P_MRPProdDemandAggregation

@AbapCatalog.sqlViewName: 'PMRPPRODDEMAGGR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl: {authorizationCheck: #NOT_REQUIRED,
                personalData.blocking: #NOT_REQUIRED
}
@ObjectModel: {
    usageType: {serviceQuality: #C, sizeCategory: #XXL, dataClass: #MIXED }
}
@VDM.private: true
@VDM.viewType: #COMPOSITE

define view P_MRPProdDemandAggregation
  as select from    I_MRPPlanningSegment         as MRPPlanningSegment

    left outer join I_MaterialShortageDefinition as ShortageDefinition      on  MRPPlanningSegment.MaterialShortageProfile       = ShortageDefinition.MaterialShortageProfile
                                                                            and ShortageDefinition.HasMfgOrderComponentCheckRule = 'X'

    left outer join(
                    I_CacheSupplyDemandItem      as CacheSupDemItem
      // Exclude By Product Items from the aggregation

        inner join  I_MRPReservation             as OrderOperationComponent on  CacheSupDemItem.MRPElement              =  OrderOperationComponent.Reservation
                                                                            and CacheSupDemItem.MRPElementItem          =  OrderOperationComponent.MRPElementItem
                                                                            and OrderOperationComponent.DebitCreditCode <> 'S'
    )
    on  CacheSupDemItem.MRPPlanningSegmentType      = MRPPlanningSegment.MRPPlanningSegmentType
    and CacheSupDemItem.MRPPlanningSegment          = MRPPlanningSegment.MRPPlanningSegment
    and CacheSupDemItem.MaterialShortageProfile     = ShortageDefinition.MaterialShortageProfile
    and CacheSupDemItem.NumberOfShortages           = ShortageDefinition.MaterialShortageProfileCount
    and CacheSupDemItem.MRPPlant                    = MRPPlanningSegment.MRPPlant
    and CacheSupDemItem.Material                    = MRPPlanningSegment.Material
    and CacheSupDemItem.MRPArea                     = MRPPlanningSegment.MRPArea
    and (
       CacheSupDemItem.MRPElementCategory           = 'SB'
       or CacheSupDemItem.MRPElementCategory        = 'SM'
       or CacheSupDemItem.MRPElementCategory        = 'SU'
       or CacheSupDemItem.MRPElementCategory        = 'AR'

     )
    and (
       CacheSupDemItem.SourceMRPElementCategory     = 'BR'
       or CacheSupDemItem.SourceMRPElementCategory  = 'FE'
       or CacheSupDemItem.SourceMRPElementCategory  = 'IH'
       or CacheSupDemItem.SourceMRPElementCategory  = 'NE'
     )
    and (
       CacheSupDemItem.MRPElementUncoveredQuantity  > 0
       or CacheSupDemItem.MRPElementDelayedQuantity > 0
     )

{
  key MRPPlanningSegment.MRPPlanningSegmentType,
  key MRPPlanningSegment.MRPPlanningSegment,
  key ShortageDefinition.MaterialShortageProfile,
  key ShortageDefinition.MaterialShortageProfileCount,
  key CacheSupDemItem.SourceMRPElement,
  key CacheSupDemItem.SourceMRPElementCategory,
      max(CacheSupDemItem.MaximumDelayInDays)          as MaximumDelayInDays,
      max(CacheSupDemItem.MaximumDelayInWorkdays)      as MaximumDelayInWorkdays,
      max(CacheSupDemItem.MRPElementDelayedQuantity)   as MaximumDelayedQuantity,
      max(CacheSupDemItem.MRPElementUncoveredQuantity) as MaximumUncoveredQuantity,
      count(*)                                         as NumberOfUncoveredDemandItems
}
group by
  MRPPlanningSegment.MRPPlanningSegmentType,
  MRPPlanningSegment.MRPPlanningSegment,
  ShortageDefinition.MaterialShortageProfile,
  ShortageDefinition.MaterialShortageProfileCount,
  CacheSupDemItem.SourceMRPElementCategory,
  CacheSupDemItem.SourceMRPElement;