P_AssgblStoreForProcmtProdInt

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'PASNSTRPROCPRODI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@ObjectModel.usageType.serviceQuality: #B
@VDM.viewType: #CONSUMPTION
@VDM.private: true

define view P_AssgblStoreForProcmtProdInt
  with parameters
    P_ValidityStartDate : datum,
    P_ValidityEndDate   : datum
  -- no WRF3 checks
  -- Superset of data: logistical products in stores
  as select from    I_ActiveLogisticalProduct                                      as Product
    cross join      I_Plant                                                        as Plant

  -- Condition: If a MARC entry already exists, the respective product must be defined as 'internal procurement'
    left outer join I_ProductPlant                                                                               on  I_ProductPlant.Product = Product.Product
                                                                                                                 and I_ProductPlant.Plant   = Plant.Plant
  -- Condition: No existing or overlaping assignments
    left outer join I_LoglProductAssgmtDCToStore                                   as LoglProductAssgmtDCToStore on  Plant.Plant                                       = LoglProductAssgmtDCToStore.Store
                                                                                                                 and Product.Product                                   = LoglProductAssgmtDCToStore.Material
    -- restrict time-dependent overlaps
                                                                                                                 and (
                                                                                                                    -- Start Date is within requested period
                                                                                                                    (
                                                                                                                      LoglProductAssgmtDCToStore.ValidityStartDate     >= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProductAssgmtDCToStore.ValidityStartDate <= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                    or -- End Date is within requested period
                                                                                                                    (
                                                                                                                      LoglProductAssgmtDCToStore.ValidityEndDate       >= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProductAssgmtDCToStore.ValidityEndDate   <= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                    or -- Start Date and End Date wrap requested period
                                                                                                                    (
                                                                                                                      LoglProductAssgmtDCToStore.ValidityStartDate     <= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProductAssgmtDCToStore.ValidityEndDate   >= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                  )
    left outer join I_LoglProdAssgmtSuplrToStore                                   as LoglProdAssgmtSuplrToStore on  Plant.Plant                                       = LoglProdAssgmtSuplrToStore.Store
                                                                                                                 and Product.Product                                   = LoglProdAssgmtSuplrToStore.Material
    -- restrict time-dependent overlaps
                                                                                                                 and (
                                                                                                                    -- Start Date is within requested period
                                                                                                                    (
                                                                                                                      LoglProdAssgmtSuplrToStore.ValidityStartDate     >= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProdAssgmtSuplrToStore.ValidityStartDate <= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                    or -- End Date is within requested period
                                                                                                                    (
                                                                                                                      LoglProdAssgmtSuplrToStore.ValidityEndDate       >= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProdAssgmtSuplrToStore.ValidityEndDate   <= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                    or -- Start Date and End Date wrap requested period
                                                                                                                    (
                                                                                                                      LoglProdAssgmtSuplrToStore.ValidityStartDate     <= $parameters.P_ValidityStartDate
                                                                                                                      and LoglProdAssgmtSuplrToStore.ValidityEndDate   >= $parameters.P_ValidityEndDate
                                                                                                                    )
                                                                                                                  )

    left outer join P_DefaultSupplyingDCForMatlGrp( P_ValidityStartDate: $parameters.P_ValidityStartDate,
                                                    P_ValidityEndDate: $parameters.P_ValidityEndDate,
                                                    P_SAPClient : $session.client) as DefaultDCMatlGrp           on  DefaultDCMatlGrp.site          = Plant.Plant
                                                                                                                 and DefaultDCMatlGrp.materialgroup = Product.ProductGroup
    left outer join P_DefaultSupplyingDC( P_ValidityStartDate: $parameters.P_ValidityStartDate,
                                          P_ValidityEndDate: $parameters.P_ValidityEndDate,
                                          P_SAPClient : $session.client)           as DefaultDC                  on DefaultDC.site = Plant.Plant
    left outer join P_StoreGrpFromClfnStore( P_SAPClient : $session.client)        as _AssgdStoreGroup           on Plant.Plant = _AssgdStoreGroup.Store

    association [1..1] to I_ActiveLogisticalProduct as _ActiveLogisticalProduct on $projection.Product = _ActiveLogisticalProduct.Product

{
  key Product.Product                                                                                         as Product,
  key Plant.Plant                                                                                             as Store,
      Plant.PlantName                                                                                         as StoreName,
      Plant._Address.CityName,
      Plant._Address.StreetName,
      -- if material-group specific default DC is available use that, else the material-group independant one
      coalesce( DefaultDCMatlGrp.defaultdistributioncenter,
                DefaultDC.defaultdistributioncenter)                                                          as DefaultSupplyingDC,
      _AssgdStoreGroup.StoreGroupInternalID,

      /* Exposed associations */
      _ActiveLogisticalProduct
}
where
       Plant.PlantCategory                         = 'A' -- only store
  and  LoglProductAssgmtDCToStore.SourceListRecord is null -- no match in source list (anti-join), thus no time overlap with existing assignments
  and  LoglProdAssgmtSuplrToStore.SourceListRecord is null -- no match in source list (anti-join), thus no time overlap with existing assignments
  -- Source of Supply (MARC entry) optional, but if defined then internal procurement
  and(
       I_ProductPlant.SourceOfSupplyCategory       is null
    or I_ProductPlant.SourceOfSupplyCategory       = '2'
    or I_ProductPlant.SourceOfSupplyCategory       = '3'
    or I_ProductPlant.SourceOfSupplyCategory       = '4'
    or I_ProductPlant.SourceOfSupplyCategory       = 'D'
    or I_ProductPlant.SourceOfSupplyCategory       = 'E'
    or I_ProductPlant.SourceOfSupplyCategory       = 'F'
    or I_ProductPlant.SourceOfSupplyCategory       = 'G'
    or I_ProductPlant.SourceOfSupplyCategory       = 'H'
    or I_ProductPlant.SourceOfSupplyCategory       = 'I'
  )