@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(case
when act.ProductionHoldItem = 1 then 0
when hold.WorkCenterBsdNetDurnInSeconds = 0 then hold.CalendarBasedNetDurnInSeconds
else hold.WorkCenterBsdNetDurnInSeconds
end 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
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_LOGISTICSORDERBASIC",
"I_MFGEXECDOCUMENTNOTE",
"I_OPACTYINSTCEOPACTYNTWKELMNT",
"I_OPERATIONACTIVITYTEXT",
"I_ORDEROPERATIONBASIC",
"I_PRODUCTIONHOLDACTION",
"I_PRODUCTIONHOLDBASIC",
"I_SFIREWORKOPERATION",
"P_MFGEXECEVENTHISTOPACTY2"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/