P_SFIAssembledComponent

@AbapCatalog.sqlViewName: 'PMPESFIACMP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {serviceQuality: #D, sizeCategory: #XL, dataClass: #MIXED}
@VDM.viewType: #CONSUMPTION
@VDM.private: true
define view P_SFIAssembledComponent
  as select from           I_SFIAtOpActyAssyByComponent as SFIAtOpActyAssyByComponent
    left outer to one join I_OpActyNtwkInstanceBasic    as OpActyNtwkInstance        on OpActyNtwkInstance.OpActyNtwkInstance = SFIAtOpActyAssyByComponent.OpActyNtwkInstance
    left outer to one join I_OpActyComponentAssignment  as OpActyComponentAssignment on  OpActyComponentAssignment.OpActyNtwkInstance = SFIAtOpActyAssyByComponent.OpActyNtwkInstance
                                                                                     and OpActyComponentAssignment.OpActyNtwkElement  = SFIAtOpActyAssyByComponent.OpActyNtwkElement
                                                                                     and OpActyComponentAssignment.Reservation        = SFIAtOpActyAssyByComponent.Reservation
                                                                                     and OpActyComponentAssignment.ReservationItem    = SFIAtOpActyAssyByComponent.ReservationItem
                                                                                     and OpActyComponentAssignment.RecordType         = SFIAtOpActyAssyByComponent.RecordType
    left outer to one join I_OrderComponent             as OrderComponent            on  OrderComponent.Reservation     = OpActyComponentAssignment.Reservation
                                                                                     and OrderComponent.ReservationItem = OpActyComponentAssignment.ReservationItem
                                                                                     and OrderComponent.RecordType      = OpActyComponentAssignment.RecordType
  association [0..1] to I_Plant                     as _Plant                     on  $projection.Plant = _Plant.Plant
  association [0..1] to I_ProductPlant              as _ProductPlant              on  $projection.Material = _ProductPlant.Product
                                                                                  and $projection.Plant    = _ProductPlant.Plant
  //  association [0..1] to I_StorageLocation           as _StorageLocation           on  $projection.Plant           = _StorageLocation.Plant

  //                                                                                  and $projection.storagelocation = _StorageLocation.StorageLocation

  association [1..1] to I_OpActyNtwkInstance        as _OpActyNtwkInstance        on  $projection.OpActyNtwkInstance = _OpActyNtwkInstance.OpActyNtwkInstance
  association [1..1] to I_OperationActivityInstance as _OperationActivityInstance on  $projection.OpActyNtwkInstance = _OperationActivityInstance.OpActyNtwkInstance
                                                                                  and $projection.OpActyNtwkElement  = _OperationActivityInstance.OpActyNtwkElement
{
  key SFIAtOpActyAssyByComponent.Reservation,
  key SFIAtOpActyAssyByComponent.ReservationItem,
  key SFIAtOpActyAssyByComponent.RecordType,
      @ObjectModel.foreignKey.association: '_OpActyNtwkInstance'
  key SFIAtOpActyAssyByComponent.OpActyNtwkInstance,
      @ObjectModel.foreignKey.association: '_OperationActivityInstance'
  key SFIAtOpActyAssyByComponent.OpActyNtwkElement,
  key SFIAtOpActyAssyByComponent.AssemblyShopFloorItem,
  key SFIAtOpActyAssyByComponent.Material,
      @ObjectModel.foreignKey.association: '_Plant'
  key SFIAtOpActyAssyByComponent.Plant,
      OrderComponent.StorageLocation,
      @Semantics.quantity.unitOfMeasure: 'EntryUnit'
      cast(round(fltp_to_dec( OpActyComponentAssignment.MfgOrderComponentUsageNetQty as abap.dec(13,4) ), 3)
        as co_menge )                                                                      as RequiredQuantity,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      SFIAtOpActyAssyByComponent.MaterialComponentQuantity,
      @Semantics.quantity.unitOfMeasure: 'EntryUnit'
      SFIAtOpActyAssyByComponent.QuantityInEntryUnit,
      @Semantics.unitOfMeasure: true
      SFIAtOpActyAssyByComponent.MaterialBaseUnit,
      @Semantics.unitOfMeasure: true
      SFIAtOpActyAssyByComponent.EntryUnit,
      OpActyNtwkInstance.ManufacturingOrder,
      OpActyNtwkInstance.OrderInternalID,
      OpActyNtwkInstance.OrderOperationInternalID,
      OpActyNtwkInstance.OperationActivityNetwork,
      OpActyNtwkInstance.OpActyNtwkVersionCounter,
      cast('' as mpe_comp_is_for_backflush preserving type)                                as MatlCompIsMarkedForBackflush,
      cast(coalesce(OrderComponent.GoodsMovementIsAllowed, 'X') as xwaok preserving type ) as GoodsMovementIsAllowed,
      OpActyComponentAssignment.MatlCompInstceAssyIsRequired,
      OpActyComponentAssignment.MfgRefStrucBOMLeafAssgmtUUID,
      OpActyComponentAssignment.MfgRefStructureItemDetailUUID,
      OpActyComponentAssignment.MfgRefStrucItmDetIsContextual,
      OpActyComponentAssignment.BOOOpBOMItemInternalID,
      OpActyComponentAssignment.LogicalComponent,
      OpActyComponentAssignment.MfgOrderComponentUsageNetQty,
      OrderComponent.BOMItemCategory,
      OrderComponent.BillOfMaterialInternalID,
      OrderComponent.BOMItem,
      OrderComponent.BOMItemInternalChangeCount,
      -- Variable-Size Item Fields
      OrderComponent.MaterialCompIsVariableSized,
      OrderComponent.FormulaKey,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension1,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension2,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension3,
      @Semantics.unitOfMeasure: true
      OrderComponent.VariableSizeDimensionUnit,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeComponentUnit'
      OrderComponent.VariableSizeComponentQuantity,
      @Semantics.unitOfMeasure: true
      OrderComponent.VariableSizeComponentUnit,

      SFIAtOpActyAssyByComponent._Material,
      _Plant,
      //      _StorageLocation,

      _ProductPlant,
      _OpActyNtwkInstance,
      _OperationActivityInstance,
      OrderComponent._BOMItemCategory

}

-- add backflushed components

union all

select from  I_ShopFloorItemStatus       as ShopFloorItemStatus
  inner join I_OpActyNtwkInstanceBasic   as OpActyNtwkInstanceBasic   on OpActyNtwkInstanceBasic.OpActyNtwkInstance = ShopFloorItemStatus.OpActyNtwkInstance
  inner join I_OpActyComponentAssignment as OpActyComponentAssignment on  OpActyComponentAssignment.OpActyNtwkInstance = ShopFloorItemStatus.OpActyNtwkInstance
                                                                      and OpActyComponentAssignment.OpActyNtwkElement  = ShopFloorItemStatus.OpActyNtwkElement
  inner join I_OrderComponent            as OrderComponent            on  OrderComponent.Reservation                  = OpActyComponentAssignment.Reservation
                                                                      and OrderComponent.ReservationItem              = OpActyComponentAssignment.ReservationItem
                                                                      and OrderComponent.RecordType                   = OpActyComponentAssignment.RecordType
                                                                      and OrderComponent.MatlCompIsMarkedForBackflush = 'X' --backflushed
association [0..1] to I_Plant                     as _Plant                     on  $projection.Plant = _Plant.Plant
association [1..1] to I_Material                  as _Material                  on  $projection.Material = _Material.Material
association [0..1] to I_ProductPlant              as _ProductPlant              on  $projection.Material = _ProductPlant.Product
                                                                                and $projection.Plant    = _ProductPlant.Plant
association [1..1] to I_OpActyNtwkInstance        as _OpActyNtwkInstance        on  $projection.OpActyNtwkInstance = _OpActyNtwkInstance.OpActyNtwkInstance
association [1..1] to I_OperationActivityInstance as _OperationActivityInstance on  $projection.OpActyNtwkInstance = _OperationActivityInstance.OpActyNtwkInstance
                                                                                and $projection.OpActyNtwkElement  = _OperationActivityInstance.OpActyNtwkElement
{
      @UI.hidden:true
  key OpActyComponentAssignment.Reservation,
  key OpActyComponentAssignment.ReservationItem,
  key OpActyComponentAssignment.RecordType,
      @ObjectModel.foreignKey.association: '_OpActyNtwkInstance'
  key OpActyComponentAssignment.OpActyNtwkInstance,
      @ObjectModel.foreignKey.association: '_OperationActivityInstance'
  key OpActyComponentAssignment.OpActyNtwkElement,
  key ShopFloorItemStatus.ShopFloorItem                        as AssemblyShopFloorItem,
  key OrderComponent.Material,
      @ObjectModel.foreignKey.association: '_Plant'
  key OrderComponent.Plant,
      OrderComponent.StorageLocation,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(round(fltp_to_dec(OpActyComponentAssignment.MfgOrderComponentUsageNetQty as abap.dec(13,4)), 3)
        as co_menge )                                          as RequiredQuantity,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(case OrderComponent.MaterialCompIsVariableSized
        when 'X' then round(fltp_to_dec(OpActyComponentAssignment.MfgOrderComponentUsageNetQty as abap.dec(13,4)), 3)
                      * OrderComponent.VariableSizeComponentQuantity
        else round(fltp_to_dec(OpActyComponentAssignment.MfgOrderComponentUsageNetQty as abap.dec(13,4)), 3)
      end as mpe_consumed_qty)                                 as MaterialComponentQuantity,
      @Semantics.quantity.unitOfMeasure: 'EntryUnit'
      OpActyComponentAssignment.MfgOrderComponentUsageQuantity as QuantityInEntryUnit,
      @Semantics.unitOfMeasure: true
      OrderComponent.BaseUnit                                  as MaterialBaseUnit,
      @Semantics.unitOfMeasure: true
      cast(case OrderComponent.MaterialCompIsVariableSized
        when 'X' then OrderComponent.VariableSizeComponentUnit
        else OrderComponent.BaseUnit
      end as erfme preserving type)                            as EntryUnit,
      OpActyNtwkInstanceBasic.ManufacturingOrder,
      OpActyNtwkInstanceBasic.OrderInternalID,
      OpActyNtwkInstanceBasic.OrderOperationInternalID,
      OpActyNtwkInstanceBasic.OperationActivityNetwork,
      OpActyNtwkInstanceBasic.OpActyNtwkVersionCounter,
      cast('X' as mpe_comp_is_for_backflush preserving type)   as MatlCompIsMarkedForBackflush,
      OrderComponent.GoodsMovementIsAllowed,
      OpActyComponentAssignment.MatlCompInstceAssyIsRequired,
      OpActyComponentAssignment.MfgRefStrucBOMLeafAssgmtUUID,
      OpActyComponentAssignment.MfgRefStructureItemDetailUUID,
      OpActyComponentAssignment.MfgRefStrucItmDetIsContextual,
      OpActyComponentAssignment.BOOOpBOMItemInternalID,
      OpActyComponentAssignment.LogicalComponent,
      OpActyComponentAssignment.MfgOrderComponentUsageNetQty,
      OrderComponent.BOMItemCategory,
      OrderComponent.BillOfMaterialInternalID,
      OrderComponent.BOMItem,
      OrderComponent.BOMItemInternalChangeCount,
      -- Variable-Size Item Fields
      OrderComponent.MaterialCompIsVariableSized,
      OrderComponent.FormulaKey,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension1,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension2,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeDimensionUnit'
      OrderComponent.VariableSizeDimension3,
      @Semantics.unitOfMeasure: true
      OrderComponent.VariableSizeDimensionUnit,
      @Semantics.quantity.unitOfMeasure: 'VariableSizeComponentUnit'
      OrderComponent.VariableSizeComponentQuantity,
      @Semantics.unitOfMeasure: true
      OrderComponent.VariableSizeComponentUnit,
      _Material,
      _Plant,
      //      _StorageLocation,

      _ProductPlant,
      _OpActyNtwkInstance,
      _OperationActivityInstance,
      OrderComponent._BOMItemCategory
}
where
        ShopFloorItemStatus._SASStatus.SASStatusCategory = 3 -- Completed
  or(
        ShopFloorItemStatus._SASStatus.SASStatusCategory = 7 -- Scrapped
    and ShopFloorItemStatus._SASStatus.SASStatusCategory = 2 -- from In Process
  )