I_MRPMasterDataIssueCatAssgmt

@EndUserText.label: 'Assignment of a Message to an Issue'
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck:#NOT_REQUIRED
@AbapCatalog.sqlViewName: 'IMRPMDISSCATA'
@ObjectModel.usageType: {serviceQuality: #A, sizeCategory: #S, dataClass: #META}
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.representativeKey: 'SystemMessageNumber'
define view I_MRPMasterDataIssueCatAssgmt 
  as select from t100 
{
//  key sprsl as Language, -- no need  to be included, is always == sy-langu by where condition

  key arbgb as SystemMessageIdentification, -- nothing found --> last time planned successfully
  key msgnr as SystemMessageNumber,
      -- categorize messages
      cast(case
      -- category I = Info that successfully planned (or never or marked for deletion)
        when    arbgb = 'PPH_MRP' and msgnr = '130'
             or arbgb = 'PPH_MRP' and msgnr = '131'        
             or arbgb = 'PPH_MRP' and msgnr = '149'        
             or arbgb = '/SAPAPO/RRP_HEUR' and msgnr = '030'
             or arbgb = '/SAPAPO/RRP_HEUR' and msgnr = '30'         
           then 'I'
      -- category R = restriction
        when    arbgb = 'PPH_MRP' and msgnr = '140'
             or arbgb = 'PPH_MRP' and msgnr = '143'
             or arbgb = 'PPH_MRP' and msgnr = '145'
             or arbgb = 'PPH_MRP' and msgnr = '146'
             or arbgb = 'PPH_MRP' and msgnr = '154'
//             or arbgb = 'PPH_MRP' and msgnr = '156'

             or arbgb = 'PPH_MRP' and msgnr = '157'
             or arbgb = 'PPH_MRP' and msgnr = '161'
             or arbgb = 'PPH_MRP' and msgnr = '163'
             or arbgb = 'PPH_MRP' and msgnr = '165'
             or arbgb = 'PPH_MRP' and msgnr = '178'
             or arbgb = 'PPH_MRP' and msgnr = '180'
             or arbgb = 'PPH_MRP' and msgnr = '181'
//             or arbgb = 'PPH_MRP' and msgnr = '204'

             or arbgb = 'PPH_MRP' and msgnr = '214'
             or arbgb = 'PPH_MRP' and msgnr = '215'           
             or arbgb = 'PPH_MRP' and msgnr = '218'
             or arbgb = 'PPH_MRP' and msgnr = '220'
//             or arbgb = 'PPH_MRP' and msgnr = '221'

             or arbgb = 'PPH_MRP' and msgnr = '224'
             or arbgb = 'PPH_MRP' and msgnr = '231'
             or arbgb = 'PPH_MRP' and msgnr = '240'
             or arbgb = 'PPH_MRP' and msgnr = '241'
//             or arbgb = 'PPH_MRP' and msgnr = '242'

//             or arbgb = 'PPH_MRP' and msgnr = '243'

             or arbgb = 'PPH_MRP' and msgnr = '251'
             or arbgb = 'PPH_MRP' and msgnr = '253'
             or arbgb = 'PPH_MRP' and msgnr = '413'
             or arbgb = 'PPH_MRP' and msgnr = '414'
             or arbgb = 'PPH_MRP' and msgnr = '415'

           then 'R'
      -- category B = Problems with BOM explosion
        when    arbgb = 'PPH_MRP' and msgnr = '226'
             or arbgb = 'MD'      and msgnr = '510'
             or arbgb = 'MD'      and msgnr = '512'
             or arbgb = 'MD'      and msgnr = '513'
             or arbgb = '61'      and msgnr = '551'
             or arbgb = '61'      and msgnr = '336'
             or arbgb = 'PPH_MRP' and msgnr = '183'
             or arbgb = 'PPH_MRP' and msgnr = '184'
             or arbgb = 'PPH_MRP' and msgnr = '188'
             or arbgb = 'PPH_MRP' and msgnr = '189'
             or arbgb = 'PPH_MRP' and msgnr = '190'
             or arbgb = 'PPH_MRP' and msgnr = '191'
             or arbgb = 'PPH_MRP' and msgnr = '193'
             or arbgb = 'PPH_MRP' and msgnr = '211'
           then 'B'
      -- category T = Problems with Scheduling
        when    arbgb = '61' and msgnr = '124'
             or arbgb = '61' and msgnr = '884'
             or arbgb = 'PPH_MRP' and msgnr = '233'
             or arbgb = 'PPH_MRP' and msgnr = '252'
             or arbgb = 'C2' 
             or arbgb = 'CO' 
             or arbgb = 'CP' 
           then 'T'
      -- category S = Problems with Sourcing
        when    arbgb = 'PPH_MRP' and msgnr = '205'
             or arbgb = 'PPH_MRP' and msgnr = '232'
             or arbgb = 'PPH_MRP' and msgnr = '235'
             or arbgb = '06'      and msgnr = '859'
           then 'S'
      -- category M = material status
        when  arbgb = 'PPH_MRP' and 
               ( msgnr = '147' or
                 msgnr = '148' or
                 msgnr = '149' or
                 msgnr = '246' )        
           or arbgb = '61' and 
               ( msgnr = '130' or       
                 msgnr = '403' )       
           then 'M'
      -- category D = master data
        when  arbgb = 'PPH_MRP' and 
               ( msgnr = '150' or
                 msgnr = '151' or
                 msgnr = '152' or
                 msgnr = '153' or
                 msgnr = '225' or
                 msgnr = '229' or
                 msgnr = '230' )
          
           or arbgb = '61' and 
               ( msgnr = '118' or       
                 msgnr = '123' )  
       
           or arbgb = 'MD' and 
               ( msgnr = '701' or      
                 msgnr = '704' or    
                 msgnr = '705' or    
                 msgnr = '137' )       
           then 'D'
        -- category A = manually set to plan in ABAP
        when    arbgb = 'PPH_MRP' and msgnr = '164'
           then 'A'
      -- default category ''
        else ' '
      end  as pph_issue_category preserving type ) as MRPMasterDataIssueCategory,
      text as MRPMasterDataIssueText
}
where sprsl = $session.system_language
and -- as HANA makes a very bad execution plan involving a complete materialization of the table we include the relevant ARBGB here
  (    arbgb = 'PPH_MRP' or arbgb = 'MD' or arbgb = '06' or arbgb = '61' 
    or arbgb = 'C2' or arbgb = 'CO' or arbgb = '/SAPAPO/RRP_HEUR' or arbgb = '/SAPAPO/RRP' or arbgb = 'AUTO_CIF'
    or arbgb = 'SFW' 
    or arbgb = 'CP' 
    or arbgb = 'CR' 
  )