I_ProcgExecOperationActivity

@AbapCatalog.sqlViewName: 'IMPEOAEXEC'
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {serviceQuality: #D, sizeCategory: #XL, dataClass: #MIXED}
@VDM.viewType: #COMPOSITE
@AbapCatalog.compiler.compareFilter: true
@ObjectModel.compositionRoot: true
@ObjectModel.transactionalProcessingEnabled: true
@ObjectModel.writeActivePersistence: 'IMPEOAEXEC'
@ObjectModel.createEnabled: false
@ObjectModel.deleteEnabled: false
@ObjectModel.updateEnabled: false
@AbapCatalog.preserveKey: true -- otherwise despiting the key below all the fields can be treated as key fields -> BOPF does not work
@EndUserText.label: 'Operation Activity'

define view I_ProcgExecOperationActivity
  as select from           I_OpActyInstceOpActyNtwkElmnt  as OpActyInstceOpActyNtwkElmnt
    inner join             I_OpActyInstanceStatusBasic    as OpActyInstanceStatusBasic    on  OpActyInstanceStatusBasic.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                          and OpActyInstanceStatusBasic.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
    inner join             I_OrderOperation               as MfgOrderOperation            on  MfgOrderOperation.OrderInternalID          = OpActyInstceOpActyNtwkElmnt.OrderInternalID
                                                                                          and MfgOrderOperation.OrderOperationInternalID = OpActyInstceOpActyNtwkElmnt.OrderOperationInternalID
    inner join             I_MfgOrder                     as MfgOrder                     on MfgOrder.ManufacturingOrder = OpActyInstceOpActyNtwkElmnt.ManufacturingOrder
    inner join             I_SASStatus                    as SASStatus                    on SASStatus.StatusAndActionSchemaStatus = OpActyInstanceStatusBasic.StatusAndActionSchemaStatus
    left outer to one join I_OpActyWorkInstructionAssgmt  as OpActyWorkInstructionAssgmt  on  OpActyWorkInstructionAssgmt.OperationActivity           = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElement
                                                                                          and OpActyWorkInstructionAssgmt.OperationActyVersionCounter = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElmntVersCntr
    left outer to one join P_OpActyProdnRsceToolAssAgg    as OpActyProdnRsceToolAssAgg    on  OpActyProdnRsceToolAssAgg.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                          and OpActyProdnRsceToolAssAgg.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
    left outer to one join P_OpActyComponentAssignmentAgg as OpActyComponentAssignmentAgg on  OpActyComponentAssignmentAgg.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                          and OpActyComponentAssignmentAgg.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
    left outer to one join P_OpActyWthAssgdMfgQualifn     as OpActyWthAssgdMfgQualifn     on  OpActyWthAssgdMfgQualifn.OperationActivity           = OpActyInstceOpActyNtwkElmnt.OperationActivity
                                                                                          and OpActyWthAssgdMfgQualifn.OperationActyVersionCounter = OpActyInstceOpActyNtwkElmnt.OperationActyVersionCounter
    left outer to one join P_MaterialWthAssgdMfgQualifn   as MaterialWthAssgdMfgQualifn   on MaterialWthAssgdMfgQualifn.Material = MfgOrder.Material
    left outer to one join P_WrkCtrWthAssgdMfgQualifn     as WrkCtrWthAssgdMfgQualifn     on  WrkCtrWthAssgdMfgQualifn.WorkCenterTypeCode   = MfgOrderOperation.WorkCenterTypeCode
                                                                                          and WrkCtrWthAssgdMfgQualifn.WorkCenterInternalID = MfgOrderOperation.WorkCenterInternalID
  //    left outer to one join P_OpActyNumberOfInspCharcs     as OpActyNumberOfInspCharcs     on  OpActyNumberOfInspCharcs.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance

  //                                                                                          and OpActyNumberOfInspCharcs.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement

    left outer to one join P_OpActyInspLotNmbrInspCharcs  as OpActyInspLotNmbrInspCharcs  on  OpActyInspLotNmbrInspCharcs.InspectionLot                = MfgOrder.InspectionLot
    -- Shortcut: Join InspPlanOperationInternalID with OrderOperationInternalID (those are kept identitical)
    -- Alternative: Additional Join with table QAOP which maps InspPlanOperationInternalID (VORGLFNR) with OrderOperationInternalID (APLZL)
                                                                                          and OpActyInspLotNmbrInspCharcs.InspPlanOperationInternalID  = MfgOrderOperation.OrderOperationInternalID
                                                                                          and OpActyInspLotNmbrInspCharcs.ProductionOrderOperationActy = OpActyInstceOpActyNtwkElmnt.OperationActivity
                                                                                          and OpActyInspLotNmbrInspCharcs.ProductionOrderOpActyVers    = OpActyInstceOpActyNtwkElmnt.OperationActyVersionCounter
    left outer to one join P_OpActyOpenLaborTracking      as OpActyOpenLaborTracking      on  OpActyOpenLaborTracking.OpActyNtwkInstance     = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                          and OpActyOpenLaborTracking.OpActyNtwkElement      = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
                                                                                          and OpActyOpenLaborTracking.ShopFloorItem          = 0
                                                                                          and OpActyOpenLaborTracking.ShopFloorExecutionUser = $session.user
    left outer to one join I_OpActyNtwkGroupElement       as OpActyNtwkGroupElementRel    on  OpActyInstceOpActyNtwkElmnt.OperationActivityNetwork = OpActyNtwkGroupElementRel.OperationActivityNetwork
                                                                                          and OpActyInstceOpActyNtwkElmnt.OpActyNtwkVersionCounter = OpActyNtwkGroupElementRel.OpActyNtwkVersionCounter
                                                                                          and OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement        = OpActyNtwkGroupElementRel.OpActyNtwkGroupChildElement
    left outer to one join I_OpActyNtwkElement            as OpActyNtwkGroupElement       on  OpActyNtwkGroupElement.OperationActivityNetwork = OpActyNtwkGroupElementRel.OperationActivityNetwork
                                                                                          and OpActyNtwkGroupElement.OpActyNtwkVersionCounter = OpActyNtwkGroupElementRel.OpActyNtwkVersionCounter
                                                                                          and OpActyNtwkGroupElement.OpActyNtwkElement        = OpActyNtwkGroupElementRel.OpActyNtwkGroupParentElement
                                                                                          and OpActyNtwkGroupElement.OpActyNtwkElementType    = 5 //group

    left outer to one join P_OpActNrOfActiveSFI           as OpActNrOfActiveSFI           on  OpActNrOfActiveSFI.OpActyNtwkInstance            = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                          and OpActNrOfActiveSFI.OpActyNtwkElement             = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
                                                                                          and OpActyInstceOpActyNtwkElmnt.OpActyConfIsSFIBased = 'X'
  association [0..*] to I_MfgOrderDocInfoRecdObjLink   as _MfgOrderDocInfoRecdObjLink   on  $projection.ManufacturingOrder = _MfgOrderDocInfoRecdObjLink.ManufacturingOrder
  association [0..*] to I_MfgOrderOpDocInfoRecdObjLink as _MfgOrderOpDocInfoRecdObjLink on  $projection.ManufacturingOrder       = _MfgOrderOpDocInfoRecdObjLink.ManufacturingOrder
                                                                                        and $projection.OrderOperationInternalID = _MfgOrderOpDocInfoRecdObjLink.OrderOperationInternalID
  association [0..*] to I_OpActyDocInfoRecdObjLink     as _OpActyDocInfoRecdObjLink     on  $projection.ManufacturingOrder  =  _OpActyDocInfoRecdObjLink.ManufacturingOrder
                                                                                        and (
                                                                                           $projection.OpActyNtwkInstance   <> _OpActyDocInfoRecdObjLink.OpActyNtwkInstance
                                                                                           or $projection.OpActyNtwkElement <> _OpActyDocInfoRecdObjLink.OpActyNtwkElement
                                                                                         )
  association [0..*] to I_SFIDocInfoRecdObjLink        as _SFIDocInfoRecdObjLink        on  $projection.ManufacturingOrder  =  _SFIDocInfoRecdObjLink.ManufacturingOrder
                                                                                        and (
                                                                                           $projection.OpActyNtwkInstance   <> _SFIDocInfoRecdObjLink.OpActyNtwkInstance
                                                                                           or $projection.OpActyNtwkElement <> _SFIDocInfoRecdObjLink.OpActyNtwkElement
                                                                                         )
  association [0..1] to I_OpActyWorkInstructionAssgmt  as _OpActyWorkInstructionAssgmt  on  $projection.OpActyNtwkElementElement       = _OpActyWorkInstructionAssgmt.OperationActivity
                                                                                        and $projection.OpActyNtwkElementElmntVersCntr = _OpActyWorkInstructionAssgmt.OperationActyVersionCounter
  association [0..1] to I_WorkCenterBySemanticKey      as _WorkCenterBySemanticKey      on  $projection.workcenter      = _WorkCenterBySemanticKey.WorkCenter
                                                                                        and $projection.ProductionPlant = _WorkCenterBySemanticKey.Plant
  association [0..1] to I_OrderItem                    as _OrderItem                    on  $projection.ManufacturingOrder = _OrderItem.OrderID
                                                                                        and _OrderItem.OrderItem           = '0001'
  association [1..1] to I_Plant                        as _ProductionPlant              on  $projection.ProductionPlant = _ProductionPlant.Plant
  association [1..1] to I_Plant                        as _OrderHeaderProductionPlant   on  $projection.OrderHeaderProductionPlant = _OrderHeaderProductionPlant.Plant
  association [0..1] to I_WorkCenterType               as _WorkCenterType               on  $projection.WorkCenterTypeCode = _WorkCenterType.WorkCenterTypeCode
  association [0..1] to I_WorkCenter                   as _WorkCenter                   on  $projection.WorkCenterTypeCode   = _WorkCenter.WorkCenterTypeCode
                                                                                        and $projection.WorkCenterInternalID = _WorkCenter.WorkCenterInternalID
  association [1..1] to I_OperationControlProfile      as _OperationControlProfile      on  $projection.OperationControlProfile = _OperationControlProfile.OperationControlProfile

  association [0..1] to E_OperationActivity            as _Extension                    on  $projection.OperationActivity           = _Extension.OperationActivity
                                                                                        and $projection.OperationActyVersionCounter = _Extension.OperationActyVersionCounter
{
      @ObjectModel.foreignKey.association: '_OpActyNtwkInstance'
  key OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance,
  key OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement,
      @ObjectModel.foreignKey.association: '_OpActyNtwkInternalId'
      OpActyInstceOpActyNtwkElmnt.OperationActivityNetwork,
      @ObjectModel.foreignKey.association: '_OperationActivityNetwork'
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkVersionCounter,

      -- Confirmed Quantities as virtual elements for performance reasons
      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:CL_MPE_OAI_CONFD_QTS'
      cast( 0 as mpe_scrapped_frm_prcssg  )                                                            as ConfirmationScrapQuantity,
      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:CL_MPE_OAI_CONFD_QTS'
      cast( 0 as mpe_skipped_qty )                                                                     as ConfirmationSkippedQuantity,
      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:CL_MPE_OAI_CONFD_QTS'
      cast( 0 as lmnga )                                                                               as ConfirmationYieldQuantity,

      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      OpActyInstceOpActyNtwkElmnt.OpPlannedScrapQuantity,
      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      OpActyInstceOpActyNtwkElmnt.OpPlannedYieldQuantity,
      @Semantics.unitOfMeasure: true
      OpActyInstceOpActyNtwkElmnt.ProductionUnit,
      OpActyInstceOpActyNtwkElmnt.OpActyExpdExecDurnInSeconds,
      OpActyInstceOpActyNtwkElmnt.OpActyExpdExecLaborDurnInSecs,
      @ObjectModel.foreignKey.association: '_OrderInternalID'
      OpActyInstceOpActyNtwkElmnt.OrderInternalID,
      OpActyInstceOpActyNtwkElmnt.OrderOperationInternalID,
      @ObjectModel.foreignKey.association: '_SASSchema'
      OpActyInstceOpActyNtwkElmnt.StatusAndActionSchema,
      @ObjectModel.foreignKey.association: '_SASStatus'
      OpActyInstanceStatusBasic.StatusAndActionSchemaStatus,
      OpActyInstanceStatusBasic.OpActyIsSeldForRtactvPostg,

      cast(OpActyInstceOpActyNtwkElmnt.OpActyConfIsSFIBased as boolean preserving type)                as OpActyConfIsSFIBased,
      coalesce(OpActNrOfActiveSFI.NumberOfActiveShopFloorItems, 0)                                     as NumberOfActiveShopFloorItems,

      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementExternalID,
      OpActyInstceOpActyNtwkElmnt.OperationActivity,
      OpActyInstceOpActyNtwkElmnt.OperationActyVersionCounter,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElement,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElmntVersCntr,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkSegmentType,
      OpActyInstceOpActyNtwkElmnt.OANElementDisplaySqncNumber,
      SASStatus.SASStatusCriticality,
      SASStatus.SASStatusCategory,
      OpActyInstceOpActyNtwkElmnt._OperationActivity.AssemblySqncCheckIsRequired,

      OpActyNtwkGroupElement.OpActyNtwkElementElement                                                  as OperationActivityNetworkGroup,
      OpActyNtwkGroupElement.OpActyNtwkElementElmntVersCntr                                            as OpActyNtwkGroupVersionCounter,
      cast(OpActyNtwkGroupElement.OpActyNtwkElementExternalID as mpe_group_number_ext preserving type) as OpActyNtwkGroupExternalID,

      @ObjectModel.foreignKey.association: '_MfgOrder'
      OpActyInstceOpActyNtwkElmnt.ManufacturingOrder,
      @ObjectModel.text.element: 'MfgOrderOperationText'
      cast(MfgOrderOperation.Operation as manufacturingorderoperation preserving type)                 as ManufacturingOrderOperation,
      @Semantics.text: true
      MfgOrderOperation.OperationText                                                                  as MfgOrderOperationText,
      @ObjectModel.foreignKey.association: '_ProductionPlant'
      MfgOrderOperation.Plant                                                                          as ProductionPlant,
      @ObjectModel.foreignKey.association: '_WorkCenter'
      MfgOrderOperation.WorkCenterInternalID,
      @ObjectModel.foreignKey.association: '_WorkCenterType'
      MfgOrderOperation.WorkCenterTypeCode,
      MfgOrderOperation.OperationControlProfile,
      @ObjectModel.foreignKey.association: '_WorkCenterBySemanticKey'
      _WorkCenter.WorkCenter,
      @Semantics.quantity.unitOfMeasure: 'OperationUnit'
      MfgOrderOperation.OpPlannedTotalQuantity,
      @Semantics.unitOfMeasure: true
      MfgOrderOperation.OperationUnit,

      -- Order data
      MfgOrder.Reservation,
      MfgOrder.Material,
      @ObjectModel.foreignKey.association: '_OrderHeaderProductionPlant'
      MfgOrder.ProductionPlant                                                                         as OrderHeaderProductionPlant,
      MfgOrder.ProdnProcgIsFlexible,
      -- BOM data needed for corresponding association
      MfgOrder.BillOfMaterial,
      MfgOrder.BillOfMaterialCategory,
      MfgOrder.BillOfMaterialVariant,
      MfgOrder.BillOfMaterialVariantUsage,
      MfgOrder.BillOfMaterialVersion,
      -- BOO/Routing Data
      MfgOrder.BillOfOperationsType,
      MfgOrder.BillOfOperations,
      MfgOrder.BillOfOperationsVariant,
      MfgOrder.BillOfOperationsVersion,
      MfgOrderOperation.BOOOperationInternalID,
      -- Effectivity Parameter
      MfgOrder.EffectivityParameterVariant,
      MfgOrder.ProductConfiguration,
      -- Inspection Lot number,
      MfgOrder.InspectionLot,

      cast( case
              when OpActyComponentAssignmentAgg.OpActyNtwkInstance is null then ''
              else 'X'
            end as boolean preserving type)                                                            as OpActyHasAssignedComponents,
      cast( case
              when OpActyWorkInstructionAssgmt.OperationActivity is null then ''
              else 'X'
            end as boolean preserving type)                                                            as OpActyHasAssgdWorkInstructions,
      cast( case
              when OpActyProdnRsceToolAssAgg.OpActyNtwkInstance is null then ''
              else 'X'
            end as boolean preserving type)                                                            as OpActyHasAssgdProdnRsceTools,
      cast( case
              when OpActyInspLotNmbrInspCharcs.NumberOfInspCharcs is null
                or OpActyInspLotNmbrInspCharcs.NumberOfInspCharcs  = 0 then ''
              else 'X'
            end as boolean preserving type)                                                            as OpActyHasAssgdInspCharcs,
      cast( case
              when OpActyInspLotNmbrInspCharcs.NumberOfMandatoryInspCharcs is null
                or OpActyInspLotNmbrInspCharcs.NumberOfMandatoryInspCharcs  = 0 then ''
              else 'X'
            end as boolean preserving type)                                                            as OpActyHasMandatoryInspCharcs,
      cast( case
              when OpActyWthAssgdMfgQualifn.OperationActivity is null
              and  MaterialWthAssgdMfgQualifn.Material is null
              and  WrkCtrWthAssgdMfgQualifn.WorkCenterInternalID is null then ''
              else 'X'
            end as boolean preserving type)                                                            as MfgQualificationIsRequired,

      -- Information on Labor Tracking
      cast(case
        when OpActyOpenLaborTracking.OpActyNtwkInstance is null then ''
        else 'X'
      end as boolean preserving type)                                                                  as UserIsLaboredOn,
      cast(_WorkCenter.LaborTrackingIsRequired as boolean preserving type)                             as LaborTrackingIsRequired,

      --Ready quantity
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:CL_MPE_OA_READY_QTY'
      cast( 0 as mpe_ready_qty )                                                                       as OperationExecutionAvailableQty,

      _OpActyWorkInstructionAssgmt.WorkInstruction,
      _OpActyWorkInstructionAssgmt.WorkInstructionVersionCounter,

      -- Effectivity Parameter
      //      _OrderItem.EffectivityParameterVariant,

      //

      //      @ObjectModel: {

      //        virtualElement: true,

      //        virtualElementCalculatedBy: 'ABAP:CL_MPE_GET_EFFECTIVITY_VALUE'

      //      }

      //      cast('' as mpe_effectivity_parameter_desc)                                                                as  EffectivityParameterDesc,

      //

      //      @ObjectModel: {

      //        virtualElement: true,

      //        virtualElementCalculatedBy: 'ABAP:CL_MPE_GET_EFFECTIVITY_VALUE'

      //      }

      //      cast('' as abap.sstring( 256 ))                                                                           as  EffctyTypeCnctntdParamDesc,


      OpActyInstceOpActyNtwkElmnt._OpActyNtwkInstance,
      OpActyInstceOpActyNtwkElmnt._UnitOfMeasure,
      OpActyInstceOpActyNtwkElmnt._SASSchema,
      OpActyInstanceStatusBasic._SASStatus,
      OpActyInstceOpActyNtwkElmnt._OpActyNtwkInternalId,
      OpActyInstceOpActyNtwkElmnt._OperationActivityNetwork,
      OpActyInstceOpActyNtwkElmnt._OrderInternalID,
      OpActyInstceOpActyNtwkElmnt._MfgOrder,
      MfgOrder._BillOfMaterial,
      MfgOrder._Material,
      _ProductionPlant,
      _OrderHeaderProductionPlant,
      _WorkCenter,
      _OperationControlProfile,
      _WorkCenterType,
      _WorkCenterBySemanticKey,
      _MfgOrderDocInfoRecdObjLink,
      _MfgOrderOpDocInfoRecdObjLink,
      _OpActyDocInfoRecdObjLink,
      _SFIDocInfoRecdObjLink,
      _OrderItem
}