P_SCMProductLocationMap

@AbapCatalog.sqlViewName: 'PSCMPRODLOCMAP'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel:{
    usageType.serviceQuality: #A,
    usageType.sizeCategory : #M,
    usageType.dataClass: #MASTER
}
@VDM.viewType: #COMPOSITE
@VDM.private: true
define view P_SCMProductLocationMap
  as select distinct from P_ProductPlantBasic      as s4productplant
    inner join            I_SCMBusinessSystemGroup as sys              on sys.BusinessSystemGroup is not null
    inner join            I_LocationMap            as ppds_plant_loc   on  ppds_plant_loc.BusinessSystemGroup = sys.BusinessSystemGroup
                                                                       and ppds_plant_loc.LocationExternalID  = s4productplant.Plant
                                                                       and ppds_plant_loc.LocationType        = '1001' //Plant type location

    inner join            I_AdvancedPlanningStatus as AdvncdPlngStatus on  AdvncdPlngStatus.LocationUUID = ppds_plant_loc.LocationUUID
                                                                       and AdvncdPlngStatus.Product      = s4productplant.Product
                                                                       and AdvncdPlngStatus.MaterialPlanningStatus != '9'
{
  key s4productplant.Product as product,
  key s4productplant.Plant   as plant,
  key s4productplant.Plant   as mrparea,
      s4productplant.Product as AdvancedPlanningProductName,
      ppds_plant_loc.Location


}
where
      s4productplant.IsAdvancedPlanningUsed = 'X'
  and s4productplant.IsMarkedForDeletion    = ' '

union all select distinct from I_ProductMRPArea         as s4productplantmrp
  inner join                   P_ProductPlantBasic      as s4productplant      on  s4productplant.Product                = s4productplantmrp.Product
                                                                               and s4productplant.Plant                  = s4productplantmrp.MRPPlant
                                                                               and s4productplant.IsAdvancedPlanningUsed = 'X'
                                                                               and s4productplant.IsMarkedForDeletion    = ' '

  inner join                   I_SCMBusinessSystemGroup as sys                 on sys.BusinessSystemGroup is not null
  inner join                   I_LocationMap            as ppds_mrp_loc        on  ppds_mrp_loc.BusinessSystemGroup = sys.BusinessSystemGroup
                                                                               and ppds_mrp_loc.LocationExternalID  = s4productplantmrp.MRPArea
                                                                               and (
                                                                                  ppds_mrp_loc.LocationType         = '1007' //Storage location mrp area

                                                                                  or ppds_mrp_loc.LocationType      = '1050' // Subcontractor mrp area

                                                                                )
  inner join                   I_AdvancedPlanningStatus as AdvncdPlngStatusMRP on  AdvncdPlngStatusMRP.LocationUUID = ppds_mrp_loc.LocationUUID
                                                                               and AdvncdPlngStatusMRP.Product      = s4productplant.Product
                                                                               and AdvncdPlngStatusMRP.MaterialPlanningStatus != '9'

{
  key s4productplant.Product     as product,
  key s4productplantmrp.MRPPlant as plant,
  key s4productplantmrp.MRPArea  as mrparea,
      s4productplant.Product     as AdvancedPlanningProductName,
      ppds_mrp_loc.Location


}
where
  s4productplantmrp.IsMarkedForDeletion = ' '