P_ASSGBLDCFORSTORE

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

define view P_AssgblDCForStore

  with parameters
    P_ValidityStartDate : datum,
    P_ValidityEndDate   : datum

  -- find all Distribution Centers
  as select distinct from I_Plant

  -- that are related via plant to source list items whose validity period enclose the start date parameter
    join                  I_MPPurchasingSourceItem as SourceListEncloseValStartDate on  I_Plant.Plant                                   = SourceListEncloseValStartDate.Plant
                                                                                    and SourceListEncloseValStartDate.ValidityStartDate <= $parameters.P_ValidityStartDate
                                                                                    and SourceListEncloseValStartDate.ValidityEndDate   >= $parameters.P_ValidityStartDate

  -- that are related via plant and material to source list items whose validity period enclose the end date parameter
    join                  I_MPPurchasingSourceItem as SourceListEncloseValEndDate   on  SourceListEncloseValStartDate.Plant           = SourceListEncloseValEndDate.Plant
                                                                                    and SourceListEncloseValEndDate.Material          = SourceListEncloseValStartDate.Material
                                                                                    and SourceListEncloseValEndDate.ValidityStartDate <= $parameters.P_ValidityEndDate
                                                                                    and SourceListEncloseValEndDate.ValidityEndDate   >= $parameters.P_ValidityEndDate

  association [1..1] to I_ActiveLogisticalProduct as _ActiveLogisticalProduct on $projection.Material = _ActiveLogisticalProduct.Product
  association [1..1] to I_Plant                   as _DistributionCenter      on $projection.DistributionCenter = _DistributionCenter.Plant

{
  key SourceListEncloseValStartDate.Material,

      @ObjectModel.foreignKey.association: '_DistributionCenter'
  key cast(SourceListEncloseValStartDate.Plant as distributioncenter preserving type) as DistributionCenter,
      /*  Exposed associations  */
      _ActiveLogisticalProduct,
      _DistributionCenter
}
where
  I_Plant.PlantCategory = 'B' -- Distribution Center