P_AssgblStoreForProcmtProdExt

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

define view P_AssgblStoreForProcmtProdExt
  with parameters
    P_ValidityStartDate : datum,
    P_ValidityEndDate   : datum
  -- no checks for purchasing info records (especially no time-dependency thereof)
  -- no checks for EKOrgs
  -- 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 'external 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_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,
      _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 external procurement
  and(
       I_ProductPlant.SourceOfSupplyCategory       is null
    or I_ProductPlant.SourceOfSupplyCategory       = '1'
    or I_ProductPlant.SourceOfSupplyCategory       = '3'
    or I_ProductPlant.SourceOfSupplyCategory       = '4'
    or I_ProductPlant.SourceOfSupplyCategory       = 'A'
    or I_ProductPlant.SourceOfSupplyCategory       = 'B'
    or I_ProductPlant.SourceOfSupplyCategory       = 'C'
    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'
  )