P_MRPMATERIALSTATUS

@ClientHandling.algorithm: #SESSION_VARIABLE
@Search.searchable: false
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel: {
  usageType: {
    serviceQuality: #C,
    sizeCategory: #M,
    dataClass: #MIXED
  }
}
@AccessControl: {
    authorizationCheck: #NOT_REQUIRED,
    personalData.blocking: #NOT_REQUIRED
}
@AbapCatalog: {
    sqlViewName: 'PMRPMATERIALSTAT',
    compiler.compareFilter: true,
    preserveKey:true,
    dataMaintenance: #RESTRICTED
}

define view P_MRPMATERIALSTATUS
  as select from I_ProductPlantBasic as ProductPlant //dummy call to get results to be able to run the associations, because one or both material status profils could be not assigned


  association [0..1] to P_MRPMaterialPlantStatus      as _MaterialPlant             on  $projection.Material = _MaterialPlant.Material //MARC material status

                                                                                    and $projection.MRPPlant = _MaterialPlant.MRPPlant
  association [0..1] to P_MRPMaterialCrossPlantStatus as _Material                  on  $projection.Material = _Material.Material      //MARA material status


  association [0..1] to I_MRPMaterialStatusText       as _MaterialStatusText        on  $projection.MRPValidMaterialStatus = _MaterialStatusText.MRPValidMaterialStatus
                                                                                    and _MaterialStatusText.Language       = $session.system_language
  association [0..1] to I_MRPMaterialStatusCodeText   as _MRPMaterialStatusCodeText on  $projection.MRPMaterialStatus       = _MRPMaterialStatusCodeText.MRPMaterialStatus
                                                                                    and _MRPMaterialStatusCodeText.Language = $session.system_language

{
  key Product                            as Material,
  key Plant                              as MRPPlant,

      @ObjectModel.text.element:  [ 'MRPValidMaterialStatusName' ]
      cast(
            case
                when _MaterialPlant.ShiftStatusCode is not null and _MaterialPlant.ShiftStatusCode = 'B' //plant specific material status first in case of B

                     then _MaterialPlant.ShiftStatusCode
                when _Material.ShiftStatusCode is not null and _Material.ShiftStatusCode <> '' // cross plant material status A or B when plant specific status is not B

                     then _Material.ShiftStatusCode
                when _MaterialPlant.ShiftStatusCode is not null // plant specific material status A or empty and has a material status profil assigned

                     then _MaterialPlant.ShiftStatusCode
                when _Material.ShiftStatusCode is not null // cross plant material status is empty but has a material status profil assigned

                     then _Material.ShiftStatusCode
              end as mrpmaterialstatus ) as MRPValidMaterialStatus,

      @ObjectModel.text.element:  [ 'MRPMaterialStatusName' ]
      case
            when _MaterialPlant.ShiftStatusCode is not null and _MaterialPlant.ShiftStatusCode = 'B' //plant specific material status first in case of B

                  then _MaterialPlant.ProfileCode
            when _Material.ShiftStatusCode is not null and _Material.ShiftStatusCode <> '' // cross plant material status A or B when plant specific status is not B

                  then _Material.CrossPlantStatus
            when _MaterialPlant.ShiftStatusCode is not null // plant specific material status is A or empty and has a material status profil assigned

                  then _MaterialPlant.ProfileCode
            when _Material.ShiftStatusCode is not null // cross plant material status is empty but has a material status profil assigned

                  then _Material.CrossPlantStatus
       end                               as MRPMaterialStatus,

      @ObjectModel.text.element:  [ 'ManufacturingOrderHeaderStatusName' ]
      case
           when _MaterialPlant.ShiftStatusCode is not null and _MaterialPlant.ShiftStatusCode = 'B' //plant specific material status first in case of B

                 then _MaterialPlant.ManufacturingOrderHeaderStatus
           when _Material.ShiftStatusCode is not null and _Material.ShiftStatusCode <> '' // cross plant material status A or B when plant specific status is not B

                 then _Material.ManufacturingOrderHeaderStatus
           when _MaterialPlant.ShiftStatusCode is not null // plant specific material status is A or empty and has a material status profil assigned

                 then _MaterialPlant.ManufacturingOrderHeaderStatus
           when _Material.ShiftStatusCode is not null // cross plant material status is empty but has a material status profil assigned

                 then _Material.ManufacturingOrderHeaderStatus
      end                                as ManufacturingOrderHeaderStatus,


      _MaterialStatusText,
      _MRPMaterialStatusCodeText

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PRODUCTPLANTBASIC",
"P_MRPMATERIALCROSSPLANTSTATUS",
"P_MRPMATERIALPLANTSTATUS"
],
"ASSOCIATED":
[
"I_MRPMATERIALSTATUSCODETEXT",
"I_MRPMATERIALSTATUSTEXT",
"P_MRPMATERIALCROSSPLANTSTATUS",
"P_MRPMATERIALPLANTSTATUS"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/