I_CacheMRPMfgOrder

@AbapCatalog.sqlViewName: 'ICACHEMRPMFGORD'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Cache MRP Manufacturing Orders'
@AccessControl: {authorizationCheck: #CHECK,
                personalData.blocking: #REQUIRED
}
@ObjectModel: {
    usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #MIXED }
}
@VDM.viewType: #COMPOSITE

define view I_CacheMRPMfgOrder
  as select from    I_MRPMfgOrderItem            as MfgOrderItem

    inner join      I_MfgOrder                   as MfgOrder           on MfgOrderItem.OrderID = MfgOrder.ManufacturingOrder

    inner join      I_MRPPlanningSegment         as MRPPlanningSegment on  MfgOrderItem.MRPPlant               = MRPPlanningSegment.MRPPlant
                                                                       and MfgOrderItem.Material               = MRPPlanningSegment.Material
                                                                       and MfgOrderItem.MRPArea                = MRPPlanningSegment.MRPArea
                                                                       and MfgOrderItem.MRPPlanningSegmentType = MRPPlanningSegment.MRPPlanningSegmentType
                                                                       and MfgOrderItem.MRPPlanningSegment     = MRPPlanningSegment.MRPPlanningSegment




    left outer join I_MaterialShortageDefinition as ShortageDefinition on MRPPlanningSegment.MaterialShortageProfile = ShortageDefinition.MaterialShortageProfile

    left outer join P_MRPProdDemandAggregation   as ProdDemandAggr     on  MfgOrder.ManufacturingOrder                = ProdDemandAggr.SourceMRPElement
                                                                       and MfgOrderItem.MRPPlanningSegmentType        = ProdDemandAggr.MRPPlanningSegmentType
                                                                       and MfgOrderItem.MRPPlanningSegment            = ProdDemandAggr.MRPPlanningSegment
                                                                       and ShortageDefinition.MaterialShortageProfile = ProdDemandAggr.MaterialShortageProfile
    //                                                                       and ShortageDefinition.MaterialShortageProfileCount = ProdDemandAggr.MaterialShortageProfileCount

                                                                       and (
                                                                          ProdDemandAggr.SourceMRPElementCategory     = 'BR'
                                                                          or ProdDemandAggr.SourceMRPElementCategory  = 'FE'
                                                                        )

  association [0..1] to I_CacheSupplyDemandItem      as _CacheSupDemItem    on  $projection.MaterialShortageProfile      = _CacheSupDemItem.MaterialShortageProfile
                                                                            and $projection.MaterialShortageProfileCount = _CacheSupDemItem.NumberOfShortages
                                                                            and $projection.MRPElement                   = _CacheSupDemItem.MRPElement
                                                                            and $projection.MRPPlant                     = _CacheSupDemItem.MRPPlant
                                                                            and $projection.Material                     = _CacheSupDemItem.Material
                                                                            and $projection.MRPPlanningSegmentType       = _CacheSupDemItem.MRPPlanningSegmentType
                                                                            and $projection.MRPPlanningSegment           = _CacheSupDemItem.MRPPlanningSegment
                                                                            and (
                                                                               _CacheSupDemItem.MRPElementCategory       = 'BR'
                                                                               or _CacheSupDemItem.MRPElementCategory    = 'FE'
                                                                             )

  association [1..1] to I_CacheMaterial              as _CacheMaterial      on  $projection.MRPPlant = _CacheMaterial.MRPPlant
                                                                            and $projection.Material = _CacheMaterial.Material
                                                                            and $projection.MRPArea  = _CacheMaterial.MRPArea

  association [0..1] to I_WBSElement                 as _WBSElement         on  MfgOrderItem.WBSElementInternalID      = _WBSElement.WBSElementInternalID
                                                                            and MfgOrderItem.InventorySpecialStockType = 'Q'

  association [0..1] to I_UnitOfMeasure              as _UnitOfMeasure      on  $projection.MaterialBaseUnit = _UnitOfMeasure.UnitOfMeasure

  association [0..1] to I_MaterialShortageDefinition as _ShortageDefinition on  $projection.MaterialShortageProfile               = _ShortageDefinition.MaterialShortageProfile
                                                                            and _ShortageDefinition.HasMfgOrderComponentCheckRule = 'X'


{
  key  MfgOrder.MRPPlant,
  key  MfgOrderItem.OrderID                                                                                                                               as MRPElement,
  key  cast(case MfgOrder.ManufacturingOrderCategory
      when '10' then 'FE'
      when '40' then 'BR'
      else ''
    end as mrpelementcategory preserving type)                                                                                                            as MRPElementCategory,
  key  MRPPlanningSegment.MRPPlanningSegment,
  key  MRPPlanningSegment.MRPPlanningSegmentType,
  key  ShortageDefinition.MaterialShortageProfile,
  key  ShortageDefinition.MaterialShortageProfileCount,
  key  MfgOrderItem.Material,
  key  MfgOrder.MRPController,

       cast(OrderItem as abap.char( 4 ))                                                                                                                  as MRPElementItem,
       _CacheMaterial.FactoryCalendar,
       MfgOrderItem.ProductionPlant,
       MfgOrderItem.MRPArea,
       MfgOrderItem.StorageLocation,
       MfgOrder.ManufacturingOrderType,
       _CacheMaterial.MaterialProcurementCategory,
       cast(_CacheSupDemItem.MaximumDelayInWorkdays as materialdelayinworkdays preserving type)                                                           as LateSupplyLatenessWorkdays, //=LatenessDurationInWorkDay=MaterialDelay


       cast(case coalesce(ProdDemandAggr.MaximumUncoveredQuantity, 0)
         when 0 then ProdDemandAggr.MaximumDelayInDays
         else 999
       end as maximumdelayindays preserving type)                                                                                                         as MaximumDelayInDays,

       cast(case coalesce(ProdDemandAggr.MaximumUncoveredQuantity, 0)
         when 0 then ProdDemandAggr.MaximumDelayInWorkdays
         else 999
       end as maximumdelayinworkdays preserving type)                                                                                                     as MaximumDelayInWorkdays,

       cast(case coalesce(_CacheSupDemItem.MRPElementDelayedQuantity, 0)
        when 0 then ''
         else 'X'
       end as manufacturingorderislate preserving type)                                                                                                   as ManufacturingOrderIsLate,

       ProdDemandAggr.NumberOfUncoveredDemandItems,
       @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
       @DefaultAggregation: #SUM
       cast(MfgOrderItem.ItemQuantity - MfgOrderItem.MfgOrderItemGoodsReceiptQty - MfgOrderItem.MfgOrderItemActualDeviationQty as mrpelementopenquantity) as MRPElementOpenQuantity,
       ProdDemandAggr.MaximumUncoveredQuantity,
       ProdDemandAggr.MaximumDelayedQuantity,
       MfgOrder.MfgOrderPlannedTotalQty,
       _CacheSupDemItem.MRPElementDelayedQuantity,

       MfgOrder.MfgOrderActualStartDate,
       MfgOrder.MfgOrderActualEndDate,
       MfgOrder.MfgOrderActualReleaseDate,
       MfgOrder.MfgOrderConfirmedEndDate,
       MfgOrderScheduledReleaseDate,
       MfgOrder.MfgOrderCreationDate,
       MfgOrder.MfgOrderPlannedStartDate,
       MfgOrder.MfgOrderPlannedEndDate,
       PlannedEndDate                                                                                                                                     as MRPElementAvailyOrRqmtDate,
       MfgOrder.MfgOrderPlannedReleaseDate,
       _CacheMaterial.MaterialReplnmtLeadDurnEndDate,
       _CacheSupDemItem.MaximumDelayDate,

       MfgOrder._ProductionVersion.ProductionLine,
       MfgOrder._ProductionVersion.ProductionVersion,
       MfgOrder._ProductionVersion.ProductionVersionText,
       MfgOrder.ProductionSupervisor,
       //MfgOrder._ProductionVersion.Plant                                                                                                                   as ProductionVersionPlant,

       OrderIsReleased,
       concat( _ShortageDefinition.MaterialShortageProfile, cast(_ShortageDefinition.MaterialShortageProfileCount as abap.char(3)))                       as ComponentShortageDefinition,

       case
        when MfgOrder.MfgOrderActualStartDate <> '00000000'
         then 'X'
         else ''
       end                                                                                                                                                as MfgOrderProgressHasStarted,

       case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'
         then 'X'
         else ''
       end                                                                                                                                                as MfgOrderProgressHasEnded,

       -- Progress Status
       cast(case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'//afko.gltri instead of getri

              then '4' //'none'

         when MfgOrder.MfgOrderActualStartDate <> '00000000'//afko.gstri

              then '3' //'finish'

         when MfgOrder.MfgOrderActualReleaseDate <> '00000000'//afko.ftrmi

              then '2' //'start'

         else '1' //'release'

       end as mfgorderprogressstatus)                                                                                                                     as MfgOrderProgressStatus,

       -- Next Progress Status
       case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'
              then '0' //'none'

         when MfgOrder.MfgOrderActualStartDate <> '00000000'
              then '4' //'finish'

         when MfgOrder.MfgOrderActualReleaseDate <> '00000000'
              then '3' //'start'

         else '2' //'release'

       end                                                                                                                                                as MfgOrderProgressNextStatus,

       -- Status Date
       cast(case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'
              then MfgOrder.MfgOrderConfirmedEndDate
         when MfgOrder.MfgOrderActualStartDate <> '00000000'
              then MfgOrder.MfgOrderActualStartDate
         when MfgOrder.MfgOrderActualReleaseDate <> '00000000'
              then MfgOrder.MfgOrderActualReleaseDate
         else MfgOrder.MfgOrderCreationDate
       end as mfgorderstatusactualdate preserving type)                                                                                                   as MfgOrderStatusActualDate,

       -- Next Status Date
       cast(case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'
              then '00000000' //??????

         when MfgOrder.MfgOrderActualStartDate <> '00000000'
              then MfgOrder.MfgOrderPlannedEndDate //afko.gltrp

         when MfgOrder.MfgOrderActualReleaseDate <> '00000000'
              then MfgOrder.MfgOrderPlannedStartDate //afko.gstrp

         else MfgOrder.MfgOrderScheduledReleaseDate //afko.ftrms

       end as mfgordernextstatusdate preserving type)                                                                                                     as MfgOrderNextStatusPlannedDate,

       -- Planned Status Date
       cast(case
         when MfgOrder.MfgOrderConfirmedEndDate <> '00000000'
              then MfgOrder.MfgOrderPlannedEndDate
         when MfgOrder.MfgOrderActualStartDate <> '00000000'
              then MfgOrder.MfgOrderPlannedStartDate
         when MfgOrder.MfgOrderActualReleaseDate <> '00000000'
              then MfgOrder.MfgOrderScheduledReleaseDate
         else MfgOrder.MfgOrderCreationDate //aufk.erdat

       end as mfgorderstatusplanneddate preserving type)                                                                                                  as MfgOrderStatusPlannedDate,

       -- Durations
       MaterialGoodsReceiptDuration,
       @ObjectModel: {
       virtualElement: true,
       readOnly: true,
       virtualElementCalculatedBy: 'ABAP:CL_PP_MRP_MFG_ORDER_EXIT'
       }
       cast( 0 as durnplndstatustotodayinwrkdays)                                                                                                         as DurnPlndStatusToTodayInWrkdays,


       -- Units
       cast(MfgOrderItem.BaseUnit as msehi preserving type )                                                                                              as MaterialBaseUnit,
       @Semantics.unitOfMeasure:true
       MfgOrder.ProductionUnit,
       _UnitOfMeasure.UnitOfMeasureNumberOfDecimals, //TargetQuantityUnitDcmls, for rounding

       _UnitOfMeasure.UnitOfMeasureDspNmbrOfDcmls, //TargetQuantityUnitDisplayDcmls

       MfgOrderItemQtyToBaseQtyDnmntr,
       MfgOrderItemQtyToBaseQtyNmrtr,


       -- Segment fields
       MfgOrderItem.WBSElementInternalID,
       MfgOrderItem.InventorySpecialStockType,
       MRPPlanningSegment._Reservation.MaterialCompIsProcuredDirectly,
       case
         when MRPPlanningSegment.MRPPlanningSegmentType = '24'
           then
           case
             when MRPPlanningSegment._Reservation.ReferenceOrder <> ''
              then MRPPlanningSegment._Reservation.ReferenceOrder
             else
              MRPPlanningSegment._Reservation.PlannedOrder
           end
         else
          _WBSElement.Project
       end                                                                                                                                                as ProjectNetwork,
       cast(_WBSElement.WBSElement as ps_posid_edit preserving type )                                                                                     as WBSElement,
       PlanningSegmentDocument,
       PlanningSegmentDocumentItem,



       -- Associations
       _CacheSupDemItem,
       _CacheMaterial,
       _Order,
       MfgOrder._MRPPlant,
       MfgOrderItem._Material,
       _MfgOrderType,
       _ProductionPlant,
       _StorageLocation,
       _UnitOfMeasure,
       MfgOrder._MRPController,
       _WBSElement,
       _ShortageDefinition



}
where
  (
       MfgOrderItem.OrderCategory                = '10'
    or MfgOrderItem.OrderCategory                = '40'
  )
  and  MfgOrderItem.IsCompletelyDelivered        = ''
  and  MfgOrderItem.OrderItemIsNotRelevantForMRP = ''
  and(
       MRPPlanningSegment.MRPPlanningSegmentType = '02'
    or MRPPlanningSegment.MRPPlanningSegmentType = '00'
    or MRPPlanningSegment.MRPPlanningSegmentType = '20'
    or MRPPlanningSegment.MRPPlanningSegmentType = '22'
    or MRPPlanningSegment.MRPPlanningSegmentType = '24'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CACHEMATERIAL",
"I_CACHESUPPLYDEMANDITEM",
"I_MATERIALSHORTAGEDEFINITION",
"I_MFGORDER",
"I_MRPMFGORDERITEM",
"I_MRPPLANNINGSEGMENT",
"I_PRODUCTIONVERSION",
"I_RESERVATIONITEM",
"I_UNITOFMEASURE",
"I_WBSELEMENT",
"P_MRPPRODDEMANDAGGREGATION"
],
"ASSOCIATED":
[
"I_CACHEMATERIAL",
"I_CACHESUPPLYDEMANDITEM",
"I_LOGISTICSORDER",
"I_MATERIAL",
"I_MATERIALSHORTAGEDEFINITION",
"I_MFGORDERTYPE",
"I_MRPCONTROLLER",
"I_PLANT",
"I_STORAGELOCATION",
"I_UNITOFMEASURE",
"I_WBSELEMENT"
],
"BASE":
[
"I_MFGORDER",
"I_MRPMFGORDERITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/