Pphmrp_Error_Mats

@AbapCatalog.sqlViewName: 'PPHMRP_ERR_MATS'
@AccessControl.authorizationCheck:#CHECK
@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #S, dataClass: #MIXED}
@EndUserText.label: 'Attributes for Displaying Materials with Issues in last MRP'
@ClientDependent: true
define view Pphmrp_Error_Mats
  with parameters
    P_Language : sylangu,
    P_Uname    : syuname
  as select from    pph_dbvm as d
    inner join      t001w    as t001w on d.werks = t001w.werks
    inner join      mara     as mara  on  d.matnr    =  mara.matnr
                                      and mara.lvorm <> 'X'
    inner join      marc     as marc  on  d.matnr    =  marc.matnr
                                      and d.werks    =  marc.werks
                                      and marc.lvorm <> 'X'
//join mdlv to get type of berid  

    inner join mdlv          as mdlv  on d.berid = mdlv.berid
//join mdma to retrieve MRP areas, since controller might only be assigned to MRP area or differ from controller at plant    

    left outer join mdma     as mdma  on  d.matnr = mdma.matnr
                                      and d.berid = mdma.berid
                                      and d.werks = mdma.werks                                       
    inner join      sdm_aor  as aor   on (( marc.werks = aor.werks and marc.dispo = aor.dispo )
                                      or (  mdma.werks = aor.werks and mdma.dispo = aor.dispo ))
                                      and aor.uname  = $parameters.P_Uname
    left outer join t438t    as t438t on  t438t.spras = $parameters.P_Language
                                      and marc.dismm  = t438t.dismm
    left outer join t439t    as t439t on  t439t.spras = $parameters.P_Language
                                      and marc.disls  = t439t.disls
    left outer join t024d    as t024d on  d.werks    = t024d.werks
                                      and marc.dispo = t024d.dispo
//MRP area specific texts

//MRP area controller

    left outer join t024d as t024d_mdma on  d.werks = t024d_mdma.werks 
                                        and mdma.dispo = t024d_mdma.dispo
//MRP area lot size

    left outer join t439t as t439t_mdma on  t439t_mdma.spras =  $parameters.P_Language 
                                        and mdma.disls = t439t_mdma.disls
//MRP area data MRP type

    left outer join t438t as t438t_mdma on  t438t_mdma.spras =  $parameters.P_Language 
                                        and mdma.dismm = t438t_mdma.dismm
                                                                                 
    left outer join makt     as makt  on  d.matnr    = makt.matnr
                                      and makt.spras = $parameters.P_Language
    left outer join dd07v    as DD07V on  DD07V.domname    = 'MSGAR'
                                      and DD07V.ddlanguage = $parameters.P_Language
                                      and DD07V.domvalue_l = d.msgty
    left outer join t100     as t100  on  t100.sprsl = $parameters.P_Language
                                      and t100.arbgb = d.msgid
                                      and t100.msgnr = d.msgno
{
  key  d.matnr                                          as MaterialID,
  key  d.werks                                          as MRPPlant,
  cast(coalesce(makt.maktx, '') as maktx)               as MaterialDescription,
  cast(max(t001w.name1) as name1)                       as MRPPlantName,
  cast(max(d.berid) as berid)                           as MRPArea,
  case mdlv.berty 
    when '01' 
    then 
     cast(max(marc.dispo) as dispo)  
    else cast(max(mdma.dispo) as dispo)                    
  end                                                   as MRPControllerID,
  
  case mdlv.berty
    when '01'
    then cast(coalesce(t024d.dsnam, '') as dsnam)  
    else cast(coalesce(t024d_mdma.dsnam, '') as dsnam)                     
  end                                                   as MRPControllerName,
 
  cast(max(d.dstmp) as dstmp)                           as LastMRPRun,
  cast(max(d.lstmp) as ustmp)                           as LastPlngFileEntryUpdate,
  
  case mdlv.berty
    when '01' 
    then cast(max(marc.dismm) as dismm) 
    else cast(max(mdma.dismm) as dismm)                       
  end                                                   as MRPType,
  
  case mdlv.berty
    when '01' 
    then cast(coalesce(t438t.dibez, '') as dibez)
    else cast(coalesce(t438t_mdma.dibez, '') as dibez)              
  end                                                   as MRPTypeDesc,
  
  case mdlv.berty
    when '01' 
    then cast(max(marc.disls) as disls)
    else cast(max(mdma.disls) as disls)
  end                                                   as MRPLotSizeProcedure,

  case mdlv.berty
    when '01' 
    then cast(coalesce(t439t.loslt, '') as loslt)
    else cast(coalesce(t439t_mdma.loslt, '') as loslt)              
  end                                                   as MRPLotSizeProcedureDesc,
  
  d.msgid                                               as MessageID,
  cast(min(d.msgty) as msgty)                           as MessageType,
  coalesce(DD07V.ddtext, '')                            as MessageTypeText,
  d.msgno                                               as MessageNumber,
  max(d.msgv1)                                          as MessageVar1,
  max(d.msgv2)                                          as MessageVar2,
  max(d.msgv3)                                          as MessageVar3,
  max(d.msgv4)                                          as MessageVar4,
  coalesce( cast(t100.text as abap.char( 100 ) ), '' )  as MessageText
}
-- Select only Material-PlanningArea-Combinations with message fields filled
-- - In active planning scenario
-- - That are not empty
-- - That are relevant for MRP --> NODISP = 'X' or message PPH_MRP 140
where
       d.plscn    =  '000'
  and  d.sc_empty =  ''
  and  d.nodisp   =  ''
  and(
       d.msgid    <> 'PPH_MRP'
    or d.msgno    <> '140'
  )
  and  d.msgid    <> ''
  and  d.msgno    <> ''
  and(
       d.msgty    =  'W'
    or d.msgty    =  'E'
    or d.msgty    =  'A'
    or d.msgty    =  'X'
  )
-- Since issues are displayed without MRP Area information, do not show duplicate messages per plant
-- Comment: Fields MSGV1 - MSGV4 are not shown on UI and are therefore not part of GROUP BY elements
group by 
    d.matnr, 
    d.werks, 
    mdlv.berty, 
    d.msgid, 
    d.msgno, 
    makt.maktx, 
    t024d.dsnam, 
    t024d_mdma.dsnam, 
    t438t.dibez, 
    t438t_mdma.dibez, 
    t439t.loslt, 
    t439t_mdma.loslt, 
    DD07V.ddtext, 
    t100.text