P_MfgExecEventHistOperation

@AbapCatalog.sqlViewName: 'PMPEEXECHISTOP'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #TRANSACTIONAL}
@VDM.viewType: #COMPOSITE
@VDM.private: true
@AbapCatalog.compiler.compareFilter: true
define view P_MfgExecEventHistOperation
  as
  -- OA History enhanced with operation information
  select from  I_OrderOperationBasic         as oper
    inner join I_OpActyInstceOpActyNtwkElmnt as oai on  oai.OrderInternalID          = oper.OrderInternalID
                                                    and oai.OrderOperationInternalID = oper.OrderOperationInternalID
    inner join P_MfgExecEventHistOpActy2     as h   on  h.OpActyNtwkInstance = oai.OpActyNtwkInstance
                                                    and h.OpActyNtwkElement  = oai.OpActyNtwkElement
  //association [0..1] to I_OpActyBuyOffText as _OpActyBuyOffText on  $projection.OperationActivity           = _OpActyBuyOffText.OperationActivity

  //and $projection.OperationActyVersionCounter = _OpActyBuyOffText.OperationActyVersionCounter

  //and $projection.MfgBuyOffInternalID         = _OpActyBuyOffText.MfgBuyOffInternalID

  //and _OpActyBuyOffText.Language              = $session.system_language

{
  key h.MfgExecDocumentCategory                                                              as MfgExecDocumentCategory,
  key h.ManufacturingExecutionDocument                                                       as ManufacturingExecutionDocument,
  key h.MfgExecutionDocumentItem                                                             as MfgExecutionDocumentItem,
  key h.MfgProcgExecAction                                                                   as MfgProcgExecAction,
      oai.ManufacturingOrder                                                                 as ManufacturingOrder,
      h.MfgBuyOffAction                                                                      as MfgBuyOffAction,
      h.MfgObjectLogEventType                                                                as MfgObjectLogEventType,
      h.EventDateTime                                                                        as EventDateTime,
      h.ShopFloorExecutionUser                                                               as ShopFloorExecutionUser,
      h.CreatedByUser                                                                        as CreatedByUser,
      h.CreationDateTime                                                                     as CreationDateTime,
      h.SortField                                                                            as SortField,
      oper.OrderInternalID                                                                   as OrderInternalID,
      oper.OrderOperationInternalID                                                          as OrderOperationInternalID,
      cast(oper.Sequence as manufacturingordersequence preserving type)                      as ManufacturingOrderSequence,
      cast(oper.Operation as manufacturingorderoperation preserving type)                    as ManufacturingOrderOperation,
      cast(oper.OperationText as mfgorderoperationtext preserving type)                      as MfgOrderOperationText,
      cast(oper.WorkCenterInternalID as pph_arbid preserving type)                           as WorkCenterInternalID,
      oper.WorkCenterTypeCode                                                                as WorkCenterTypeCode,
      cast( case oper.WorkCenterInternalID
        when '00000000' then ''
        else oper.Plant
      end as mpe_wrk_ctr_plant preserving type )                                             as WorkCenterPlant,
      h.ShopFloorItem                                                                        as ShopFloorItem,
      h.OperationActivitySFIGroup                                                            as OperationActivitySFIGroup,
      h.OpActyNtwkInstance                                                                   as OpActyNtwkInstance,
      h.OpActyNtwkElement                                                                    as OpActyNtwkElement,
      oai._OperationActivityText[1: Language=$session.system_language].OperationActivityName as OperationActivityName,
      oai.OpActyNtwkSegmentType                                                              as OpActyNtwkSegmentType,
      oai.OperationActivityNetwork                                                           as OperationActivityNetwork,
      oai.OpActyNtwkVersionCounter                                                           as OpActyNtwkVersionCounter,
      oai.OpActyNtwkElementExternalID                                                        as OpActyNtwkElementExternalID,
      oai.OperationActivity                                                                  as OperationActivity,
      oai.OperationActyVersionCounter                                                        as OperationActyVersionCounter,
      cast(case
        when h.MfgExecDocumentCategory = 1 and h.ActionIsSASAction = 'X' then oai.StatusAndActionSchema
        else h.StatusAndActionSchema
        end as mpe_sasname_type preserving type)                                             as StatusAndActionSchema,
      h.DefectInternalID                                                                     as DefectInternalID,
      h.Notification                                                                         as Notification,
      h.NotificationItem                                                                     as NotificationItem,
      h.MaterialComponent                                                                    as MaterialComponent,
      h.MaterialComponentName                                                                as MaterialComponentName,
      h.AssembledShopFloorItem                                                               as AssembledShopFloorItem,
      h.AssembledSerialNumber                                                                as AssembledSerialNumber,
      h.AssembledMfrSerialNumber                                                             as AssembledMfrSerialNumber,
      h.AssembledBatch                                                                       as AssembledBatch,
      h.ReferenceDesignatorValue                                                             as ReferenceDesignatorValue,
      h.StorageLocation                                                                      as StorageLocation,
      h.InventoryUsabilityCode                                                               as InventoryUsabilityCode,
      h.CompDisassemblyPermanence                                                            as CompDisassemblyPermanence,
      h.MaterialComponentHasReltdComp                                                        as MaterialComponentHasReltdComp,
      h.MfgBuyOffInternalID                                                                  as MfgBuyOffInternalID,
      h.MfgBuyoffDescription                                                                 as MfgBuyOffDescription,
      h.MfgBuyOffReferencedStep                                                              as MfgBuyOffReferencedStep,
      h.MfgBuyOffAdhocStepDescription                                                        as MfgBuyOffAdhocStepDescription,
      h.MfgBuyOffCycle                                                                       as MfgBuyOffCycle,
      h.RespyMgmtTeamID                                                                      as RespyMgmtTeamID,
      h.RespyMgmtFunction                                                                    as RespyMgmtFunction,
      h.BusinessProcessConfirmedQty                                                          as BusinessProcessConfirmedQty,
      h.ProductionUnit                                                                       as ProductionUnit,
      h.OpActyActualDurnInSeconds                                                            as OpActyActualDurnInSeconds,
      h.PostingIsRetroactive                                                                 as PostingIsRetroactive,
      h.MfgActionReasonCodeGroupCtlg                                                         as MfgActionReasonCodeGroupCtlg,
      h.MfgActionReasonCodeGroup                                                             as MfgActionReasonCodeGroup,
      h.ManufacturingActionReasonCode                                                        as ManufacturingActionReasonCode,
      h.MfgExecutionDocumentNote                                                             as MfgExecutionDocumentNote,
      h.Equipment                                                                            as Equipment,
      h.SystemMessageNumber                                                                  as SystemMessageNumber,
      h.SystemMessageIdentification                                                          as SystemMessageIdentification,
      h.MfgExecHistMsgVarblField1                                                            as MfgExecHistMsgVarblField1,
      h.MfgExecHistMsgVarblField2                                                            as MfgExecHistMsgVarblField2,
      h.MfgExecHistMsgVarblField3                                                            as MfgExecHistMsgVarblField3,
      h.MfgExecHistMsgVarblField4                                                            as MfgExecHistMsgVarblField4,
      h.Defect,
      h.MfgProcgExecAttachmentName                                                           as MfgProcgExecAttachmentName
}

union all
-- Operation Holds
select from              I_ProductionHoldBasic  as hold
  inner join             I_LogisticsOrderBasic  as LogisticsOrderBasic on LogisticsOrderBasic.OrderInternalID = hold.OrderInternalID
  inner join             I_OrderOperationBasic  as oper                on  oper.OrderInternalID          = hold.OrderInternalID
                                                                       and oper.OrderOperationInternalID = hold.OrderOperationInternalID
  inner join             I_ProductionHoldAction as act                 on act.ProductionHold = hold.ProductionHold
  left outer to one join I_MfgExecDocumentNote  as note                on  note.MfgExecDocumentCategory = 5
                                                                       and act.ProductionHold           = note.ManufacturingExecutionDocument
                                                                       and act.ProductionHoldItem       = note.MfgExecutionDocumentItem

{
  key cast (5 as mpe_exec_doc_category preserving type)                   as MfgExecDocumentCategory,
  key act.ProductionHold                                                  as ManufacturingExecutionDocument,
  key act.ProductionHoldItem                                              as MfgExecutionDocumentItem,
  key act.MfgProcgExecAction                                              as MfgProcgExecAction,
      LogisticsOrderBasic.OrderID                                         as ManufacturingOrder,
      cast('  ' as mpe_buyoff_action preserving type)                     as MfgBuyOffAction,
      cast(0 as mpe_log_event)                                            as MfgObjectLogEventType,
      @Semantics.businessDate.at: true
      cast( case act.ProductionHoldItem
        when 1 then ProductionHoldStartDateTime
        else ProductionHoldEndDateTime
      end as mpe_perform_timestamp preserving type)                       as EventDateTime,
      cast(act.CreatedByUser as mpe_performed_by_user preserving type)    as ShopFloorExecutionUser,
      @Semantics.user.createdBy: true
      cast(act.CreatedByUser as mpe_create_user preserving type)          as CreatedByUser,
      @Semantics.systemDateTime.createdAt: true
      cast(act.CreationDateTime as mpe_create_timestampl preserving type) as CreationDateTime,
      cast(700 as rszsort)                                                as SortField,
      hold.OrderInternalID                                                as OrderInternalID,
      hold.OrderOperationInternalID                                       as OrderOperationInternalID,
      cast(oper.Sequence as manufacturingordersequence preserving type)   as ManufacturingOrderSequence,
      cast(oper.Operation as manufacturingorderoperation preserving type) as ManufacturingOrderOperation,
      cast(oper.OperationText as mfgorderoperationtext preserving type)   as MfgOrderOperationText,
      cast(oper.WorkCenterInternalID as pph_arbid preserving type)        as WorkCenterInternalID,
      oper.WorkCenterTypeCode                                             as WorkCenterTypeCode,
      cast( case oper.WorkCenterInternalID
        when '00000000' then ''
        else oper.Plant
      end as mpe_wrk_ctr_plant preserving type )                          as WorkCenterPlant,
      hold.ShopFloorItem                                                  as ShopFloorItem,
      hold.OperationActivitySFIGroup                                      as OperationActivitySFIGroup,
      hold.OpActyNtwkInstance                                             as OpActyNtwkInstance,
      hold.OpActyNtwkElement                                              as OpActyNtwkElement,
      cast('' as mpe_oa_description)                                      as OperationActivityName,
      cast('0' as mpe_oan_segment_type preserving type)                   as OpActyNtwkSegmentType,
      cast(0 as mpe_oan_id)                                               as OperationActivityNetwork,
      cast(0 as mpe_oan_version_no)                                       as OpActyNtwkVersionCounter,
      cast('' as mpe_oan_element_number_ext)                              as OpActyNtwkElementExternalID,
      cast(0 as mpe_oa_id)                                                as OperationActivity,
      cast(0 as  mpe_oa_version_no)                                       as OperationActyVersionCounter,
      cast('' as mpe_sasname_type)                                        as StatusAndActionSchema,
      hold.DefectInternalID                                               as DefectInternalID,
      hold.Notification                                                   as Notification,
      hold.NotificationItem                                               as NotificationItem,
      cast('' as mpe_component_matnr)                                     as MaterialComponent,
      cast('' as maktx)                                                   as MaterialComponentName,
      cast(0 as mpe_assembled_sfi_id)                                     as AssembledShopFloorItem,
      cast('' as mpe_assembled_sernr)                                     as AssembledSerialNumber,
      cast('' as mpe_assembled_mfr_sernr)                                 as AssembledMfrSerialNumber,
      cast('' as mpe_assembled_batch)                                     as AssembledBatch,
      cast('' as vmp_ref_des)                                             as ReferenceDesignatorValue,
      cast('    ' as lgort_d preserving type)                             as StorageLocation,
      cast('' as inventoryusabilitycode preserving type)                  as InventoryUsabilityCode,
      cast('' as mpe_disassembly_permanence preserving type )             as CompDisassemblyPermanence,
      cast('' as mpe_is_reassembly preserving type )                      as MaterialComponentHasReltdComp,
      cast(0 as mpe_buyoff_step)                                          as MfgBuyOffInternalID,
      cast('' as mpe_buyoffsqnc_desc)                                     as MfgBuyOffDescription,
      cast(0 as mpe_buyoff_step)                                          as MfgBuyOffReferencedStep,
      cast('' as mpe_buyoffsqnc_desc)                                     as MfgBuyOffAdhocStepDescription,
      cast('000000000000000' as mpe_buyoff_cycle_id preserving type)      as MfgBuyOffCycle,
      cast('00000000' as hrobjid preserving type)                         as RespyMgmtTeamID,
      cast('' as rsm_de_function)                                         as RespyMgmtFunction,
      cast(0 as co_iprzs)                                                 as BusinessProcessConfirmedQty,
      cast('' as mpe_log_unit)                                            as ProductionUnit,
      cast(0 as mpe_actl_exec_duration)                                   as OpActyActualDurnInSeconds,
      cast('' as mpe_is_posted_retroactively preserving type)             as PostingIsRetroactive,
      act.MfgActionReasonCodeGroupCtlg                                    as MfgActionReasonCodeGroupCtlg,
      act.MfgActionReasonCodeGroup                                        as MfgActionReasonCodeGroup,
      act.ManufacturingActionReasonCode                                   as ManufacturingActionReasonCode,
      note.MfgExecutionDocumentNote                                       as MfgExecutionDocumentNote,
      cast('' as equnr)                                                   as Equipment,
      cast(case act.ProductionHoldItem
           when 1 then
             case act._ProductionHoldBasic.ProdnHoldIsAppldForOrderChg
               when 'X' then '064' else '027'
             end
           else
              case act._ProductionHoldBasic.ProdnHoldIsAppldForOrderChg
                when 'X' then '065' else '030'
             end
       end as msgnr)                                                      as SystemMessageNumber,
      cast('MPE_EXEC_COMMON     ' as msgid preserving type)               as SystemMessageIdentification,
      20                                                                  as MfgExecHistMsgVarblField1,
      18                                                                  as MfgExecHistMsgVarblField2, --ManufacturingOrderOperation
      17                                                                  as MfgExecHistMsgVarblField3,
      0                                                                   as MfgExecHistMsgVarblField4,
      cast('' as qdefectid)                                               as Defect,
      cast('' as mpe_file_name)                                           as MfgProcgExecAttachmentName
}
where
  hold.ProductionHoldObjectType = '4'

union all

-- SFIs sent to rework operations
select from  I_SFIReworkOperation  as sfirwrk
  inner join I_OrderOperationBasic as oper                on  oper.OrderInternalID          = sfirwrk.OrderInternalID
                                                          and oper.OrderOperationInternalID = sfirwrk.OrderOperationInternalID
  inner join I_LogisticsOrderBasic as LogisticsOrderBasic on LogisticsOrderBasic.OrderInternalID = sfirwrk.OrderInternalID
{
  key cast (254 as mpe_exec_doc_category preserving type)                     as MfgExecDocumentCategory,
  key sfirwrk.ShopFloorItem                                                   as ManufacturingExecutionDocument,
  key cast(sfirwrk.OrderOperationInternalID as mpe_doc_item)                  as MfgExecutionDocumentItem,
  key cast('' as mpe_action_name_type)                                        as MfgProcgExecAction,
      LogisticsOrderBasic.OrderID                                             as ManufacturingOrder,
      cast('  ' as mpe_buyoff_action preserving type)                         as MfgBuyOffAction,
      cast(0 as mpe_log_event)                                                as MfgObjectLogEventType,
      @Semantics.businessDate.at: true
      cast(sfirwrk.CreationDateTime as mpe_perform_timestamp)                 as EventDateTime,
      cast(sfirwrk.CreatedByUser as mpe_performed_by_user preserving type)    as ShopFloorExecutionUser,
      @Semantics.user.createdBy: true
      cast(sfirwrk.CreatedByUser as mpe_create_user preserving type)          as CreatedByUser,
      @Semantics.systemDateTime.createdAt: true
      cast(sfirwrk.CreationDateTime as mpe_create_timestampl preserving type) as CreationDateTime,
      cast(900 as rszsort)                                                    as SortField,
      oper.OrderInternalID                                                    as OrderInternalID,
      oper.OrderOperationInternalID                                           as OrderOperationInternalID,
      cast(oper.Sequence as manufacturingordersequence preserving type)       as ManufacturingOrderSequence,
      cast(oper.Operation as manufacturingorderoperation preserving type)     as ManufacturingOrderOperation,
      cast(oper.OperationText as mfgorderoperationtext preserving type)       as MfgOrderOperationText,
      cast(oper.WorkCenterInternalID as pph_arbid preserving type)            as WorkCenterInternalID,
      oper.WorkCenterTypeCode                                                 as WorkCenterTypeCode,
      cast( case oper.WorkCenterInternalID
        when '00000000' then ''
        else oper.Plant
      end as mpe_wrk_ctr_plant preserving type )                              as WorkCenterPlant,
      sfirwrk.ShopFloorItem                                                   as ShopFloorItem,
      cast(0 as mpe_sfi_at_oai_group_id)                                      as OperationActivitySFIGroup,
      cast(0 as mpe_oan_instance_id)                                          as OpActyNtwkInstance,
      cast(0 as mpe_oan_element_number)                                       as OpActyNtwkElement,
      cast('' as mpe_oa_description)                                          as OperationActivityName,
      cast('0' as mpe_oan_segment_type preserving type)                       as OpActyNtwkSegmentType,
      cast(0 as mpe_oan_id)                                                   as OperationActivityNetwork,
      cast(0 as mpe_oan_version_no)                                           as OpActyNtwkVersionCounter,
      cast('' as mpe_oan_element_number_ext)                                  as OpActyNtwkElementExternalID,
      cast(0 as mpe_oa_id)                                                    as OperationActivity,
      cast(0 as  mpe_oa_version_no)                                           as OperationActyVersionCounter,
      cast('' as mpe_sasname_type)                                            as StatusAndActionSchema,
      cast('' as qdefectinternalid)                                           as DefectInternalID,
      cast('' as qmnum)                                                       as Notification,
      cast('0000' as felfd preserving type)                                   as NotificationItem,
      cast('' as mpe_component_matnr)                                         as MaterialComponent,
      cast('' as maktx)                                                       as MaterialComponentName,
      cast(0 as mpe_assembled_sfi_id)                                         as AssembledShopFloorItem,
      cast('' as mpe_assembled_sernr)                                         as AssembledSerialNumber,
      cast('' as mpe_assembled_mfr_sernr)                                     as AssembledMfrSerialNumber,
      cast('' as mpe_assembled_batch)                                         as AssembledBatch,
      cast('' as vmp_ref_des)                                                 as ReferenceDesignatorValue,
      cast('    ' as lgort_d preserving type)                                 as StorageLocation,
      cast('' as inventoryusabilitycode preserving type)                      as InventoryUsabilityCode,
      cast('' as mpe_disassembly_permanence preserving type )                 as CompDisassemblyPermanence,
      cast('' as mpe_is_reassembly preserving type )                          as MaterialComponentHasReltdComp,
      cast(0 as mpe_buyoff_step)                                              as MfgBuyOffInternalID,
      cast('' as mpe_buyoffsqnc_desc)                                         as MfgBuyOffDescription,
      cast(0 as mpe_buyoff_step)                                              as MfgBuyOffReferencedStep,
      cast('' as mpe_buyoffsqnc_desc)                                         as MfgBuyOffAdhocStepDescription,
      cast('000000000000000' as mpe_buyoff_cycle_id preserving type)          as MfgBuyOffCycle,
      cast('00000000' as hrobjid preserving type)                             as RespyMgmtTeamID,
      cast('' as rsm_de_function)                                             as RespyMgmtFunction,
      cast(0 as co_iprzs)                                                     as BusinessProcessConfirmedQty,
      cast('' as mpe_log_unit)                                                as ProductionUnit,
      cast(0 as mpe_actl_exec_duration)                                       as OpActyActualDurnInSeconds,
      cast('' as mpe_is_posted_retroactively preserving type)                 as PostingIsRetroactive,
      cast('' as qkatart)                                                     as MfgActionReasonCodeGroupCtlg,
      cast('' as qcodegrp)                                                    as MfgActionReasonCodeGroup,
      cast('' as mpe_action_reason_code)                                      as ManufacturingActionReasonCode,
      ''                                                                      as MfgExecutionDocumentNote,
      cast('' as equnr)                                                       as Equipment,
      cast('027' as msgnr)                                                    as SystemMessageNumber,
      cast('MPE_EXEC_SFI' as msgid)                                           as SystemMessageIdentification,
      7                                                                       as MfgExecHistMsgVarblField1,
      18                                                                      as MfgExecHistMsgVarblField2,
      0                                                                       as MfgExecHistMsgVarblField3,
      0                                                                       as MfgExecHistMsgVarblField4,
      cast('' as qdefectid)                                                   as Defect,
      cast('' as mpe_file_name)                                               as MfgProcgExecAttachmentName
}