@AbapCatalog.sqlViewName: 'PMPEEXECHISTOA2'
@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_MfgExecEventHistOpActy2
as
-- OA History of events using I_MfgExecDocumentNote
select from P_MfgExecEventHistOpActy as u
left outer to one join I_MfgExecDocumentNote as note on u.MfgExecDocumentCategory = note.MfgExecDocumentCategory
and u.ManufacturingExecutionDocument = note.ManufacturingExecutionDocument
and u.MfgExecutionDocumentItem = note.MfgExecutionDocumentItem
{
key u.MfgExecDocumentCategory as MfgExecDocumentCategory,
key u.ManufacturingExecutionDocument as ManufacturingExecutionDocument,
key u.MfgExecutionDocumentItem as MfgExecutionDocumentItem,
key u.MfgProcgExecAction as MfgProcgExecAction,
u.ActionIsSASAction as ActionIsSASAction,
cast(' ' as mpe_buyoff_action preserving type) as MfgBuyOffAction,
u.MfgObjectLogEventType as MfgObjectLogEventType,
u.EventDateTime as EventDateTime,
u.ShopFloorExecutionUser as ShopFloorExecutionUser,
u.CreatedByUser as CreatedByUser,
u.CreationDateTime as CreationDateTime,
u.SortField as SortField,
u.ShopFloorItem as ShopFloorItem,
u.OperationActivitySFIGroup as OperationActivitySFIGroup,
u.OpActyNtwkInstance as OpActyNtwkInstance,
u.OpActyNtwkElement as OpActyNtwkElement,
u.StatusAndActionSchema as StatusAndActionSchema,
u.DefectInternalID as DefectInternalID,
u.Notification as Notification,
u.NotificationItem as NotificationItem,
u.MaterialComponent as MaterialComponent,
u.MaterialComponentName as MaterialComponentName,
u.AssembledShopFloorItem as AssembledShopFloorItem,
u.AssembledSerialNumber as AssembledSerialNumber,
u.AssembledMfrSerialNumber as AssembledMfrSerialNumber,
u.AssembledBatch as AssembledBatch,
u.ReferenceDesignatorValue as ReferenceDesignatorValue,
u.StorageLocation as StorageLocation,
u.InventoryUsabilityCode as InventoryUsabilityCode,
u.CompDisassemblyPermanence as CompDisassemblyPermanence,
u.MaterialComponentHasReltdComp as MaterialComponentHasReltdComp,
cast(0 as mpe_buyoff_step) as MfgBuyOffInternalID,
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,
u.BusinessProcessConfirmedQty as BusinessProcessConfirmedQty,
u.ProductionUnit as ProductionUnit,
u.OpActyActualDurnInSeconds as OpActyActualDurnInSeconds,
u.PostingIsRetroactive as PostingIsRetroactive,
u.MfgActionReasonCodeGroupCtlg as MfgActionReasonCodeGroupCtlg,
u.MfgActionReasonCodeGroup as MfgActionReasonCodeGroup,
u.ManufacturingActionReasonCode as ManufacturingActionReasonCode,
note.MfgExecutionDocumentNote as MfgExecutionDocumentNote,
u.Equipment as Equipment,
u.SystemMessageNumber as SystemMessageNumber,
u.SystemMessageIdentification as SystemMessageIdentification,
u.MfgExecHistMsgVarblField1 as MfgExecHistMsgVarblField1,
u.MfgExecHistMsgVarblField2 as MfgExecHistMsgVarblField2,
u.MfgExecHistMsgVarblField3 as MfgExecHistMsgVarblField3,
u.MfgExecHistMsgVarblField4 as MfgExecHistMsgVarblField4,
cast('' as qdefectid) as Defect,
cast('' as mpe_file_name) as MfgProcgExecAttachmentName,
cast('' as mpe_buyoffsqnc_desc ) as MfgBuyoffDescription
}
union all
select from I_MfgBuyOffActionHistory as buyoff
{
key cast (4 as mpe_exec_doc_category preserving type) as MfgExecDocumentCategory,
key cast(buyoff.ManufacturingExecutionDocument as mpe_doc_nmbr ) as ManufacturingExecutionDocument,
key buyoff.MfgExecutionDocumentItem as MfgExecutionDocumentItem,
key buyoff.MfgProcgExecAction as MfgProcgExecAction,
cast('' as xfeld preserving type) as ActionIsSASAction,
buyoff.MfgBuyOffAction as MfgBuyOffAction,
cast(0 as mpe_log_event) as MfgObjectLogEventType,
@Semantics.businessDate.at: true
cast(buyoff.CreationDateTime as mpe_perform_timestamp) as EventDateTime,
cast(buyoff.CreationUserName as mpe_performed_by_user preserving type) as ShopFloorExecutionUser,
@Semantics.user.createdBy: true
cast(buyoff.CreationUserName as mpe_create_user preserving type) as CreatedByUser,
@Semantics.systemDateTime.createdAt: true
cast(buyoff.CreationDateTime as mpe_create_timestampl preserving type) as CreationDateTime,
cast(250 as rszsort) as SortField,
buyoff.ShopFloorItem as ShopFloorItem,
cast(0 as mpe_sfi_at_oai_group_id) as OperationActivitySFIGroup,
buyoff.OpActyNtwkInstance as OpActyNtwkInstance,
buyoff.OpActyNtwkElement as OpActyNtwkElement,
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,
buyoff.MfgBuyOffInternalID as MfgBuyOffInternalID,
buyoff.MfgBuyOffReferencedStep as MfgBuyOffReferencedStep,
buyoff.MfgBuyOffAdhocStepDescription as MfgBuyOffAdhocStepDescription,
buyoff.ManufacturingExecutionDocument as MfgBuyOffCycle,
buyoff.RespyMgmtTeamID as RespyMgmtTeamID,
buyoff.RespyMgmtFunction 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,
buyoff.MfgActionReasonCodeGroupCtlg as MfgActionReasonCodeGroupCtlg,
buyoff.MfgActionReasonCodeGroup as MfgActionReasonCodeGroup,
buyoff.ManufacturingActionReasonCode as ManufacturingActionReasonCode,
buyoff.MfgBuyOffComment as MfgExecutionDocumentNote,
cast('' as equnr) as Equipment,
buyoff.SystemMessageNumber as SystemMessageNumber,
buyoff.SystemMessageIdentification as SystemMessageIdentification,
buyoff.MfgExecHistMsgVarblField1 as MfgExecHistMsgVarblField1,
buyoff.MfgExecHistMsgVarblField2 as MfgExecHistMsgVarblField2,
buyoff.MfgExecHistMsgVarblField3 as MfgExecHistMsgVarblField3,
buyoff.MfgExecHistMsgVarblField4 as MfgExecHistMsgVarblField4,
cast('' as qdefectid) as Defect,
cast('' as mpe_file_name) as MfgProcgExecAttachmentName,
buyoff.MfgBuyOffDescription as MfgBuyoffDescription
}
union all
-- Defects
select from I_MfgDefectHistory as Defect
{
key cast (255 as mpe_exec_doc_category preserving type) as MfgExecDocumentCategory,
key cast(cast(substring(Defect.Notification,3,10) as abap.numc( 19 )) as mpe_doc_nmbr ) as ManufacturingExecutionDocument,
// key cast(cast(substring(Defect.ManufacturingExecutionDocument,3,10) as abap.numc( 19 )) as mpe_doc_nmbr ) as ManufacturingExecutionDocument,
// key Defect.ManufacturingExecutionDocument as ManufacturingExecutionDocument,
key cast(Defect.NotificationItem as mpe_doc_item ) as MfgExecutionDocumentItem,
// key cast(0000 as mpe_doc_item ) as MfgExecutionDocumentItem,
-- For the time being there is just one action
key Defect.MfgProcgExecAction as MfgProcgExecAction,
cast('' as xfeld preserving type) as ActionIsSASAction,
cast(' ' as mpe_buyoff_action preserving type) as MfgBuyOffAction,
cast(0 as mpe_log_event) as MfgObjectLogEventType,
dats_tims_to_tstmp(Defect.CreationDate,Defect.CreationTime,abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as EventDateTime,
cast(Defect.CreatedByUser as mpe_performed_by_user preserving type) as ShopFloorExecutionUser,
@Semantics.user.createdBy: true
cast(Defect.CreatedByUser as mpe_create_user preserving type) as CreatedByUser,
@Semantics.systemDateTime.createdAt: true
dats_tims_to_tstmp(Defect.CreationDate,Defect.CreationTime,abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as CreationDateTime,
cast(800 as rszsort) as SortField,
Defect.ShopFloorItem as ShopFloorItem,
cast(0 as mpe_sfi_at_oai_group_id) as OperationActivitySFIGroup,
Defect.OpActyNtwkInstance as OpActyNtwkInstance,
Defect.OpActyNtwkElement as OpActyNtwkElement,
Defect.StatusAndActionSchema as StatusAndActionSchema,
cast('' as qdefectinternalid) as DefectInternalID,
Defect.Notification as Notification,
Defect.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(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,
Defect.Equipment as Equipment,
// cast(case Defect.Equipment
// when '' then
// '080'
// else
// '082' end as msgnr) as SystemMessageNumber,
cast(case
when Defect.Defect is not initial and Defect.DefectCategory is not initial and Defect.ShopFloorItem is null
then '080'
// when Defect.Notification like '$%' and Defect.Equipment is initial
// then '080'
when Defect.Defect is not initial and Defect.DefectCategory is not initial and Defect.ShopFloorItem is not initial
then '082'
// when Defect.Notification like '$%' and Defect.Equipment is not initial
// then '082'
when Defect.Defect is not initial and Defect.DefectCategory is initial
then '165'
// when Defect.Notification not like '$%' and Defect.Defect is not initial
// then '165'
when Defect.DefectCategory is initial and Defect.Defect is initial and Defect.ShopFloorItem is not initial
then '210'
when Defect.DefectCategory is initial and Defect.Defect is initial
then '166'
// when Defect.Notification not like '$%' and Defect.Defect is initial
// then '166'
end as msgnr) as SystemMessageNumber,
'MPE_EXEC_SFO' as SystemMessageIdentification,
27 as MfgExecHistMsgVarblField1,
case
// Defect.Equipment
when Defect.Defect is not initial and Defect.DefectCategory is not initial and Defect.ShopFloorItem is not initial
then 7
when Defect.Defect is not initial and Defect.DefectCategory is initial
then 21
when Defect.Defect is initial
then 21
else
0
end as MfgExecHistMsgVarblField2,
20 as MfgExecHistMsgVarblField3,
case
when Defect.Defect is not initial and Defect.DefectCategory is initial
then 33
when Defect.DefectCategory is initial and Defect.Defect is initial and Defect.ShopFloorItem is not initial
then 7
else 0
end as MfgExecHistMsgVarblField4,
Defect.Defect,
cast('' as mpe_file_name) as MfgProcgExecAttachmentName,
cast('' as mpe_buyoffsqnc_desc ) as MfgBuyoffDescription
}
union all
-- Events from Order Log
select from I_ManufacturingObjectLog as log
{
key cast (9 as mpe_exec_doc_category preserving type) as MfgExecDocumentCategory,
key log.ManufacturingObjectLog as ManufacturingExecutionDocument,
key cast(0 as mpe_doc_item) as MfgExecutionDocumentItem,
key cast('' as mpe_action_name_type) as MfgProcgExecAction,
cast('' as xfeld preserving type) as ActionIsSASAction,
cast(' ' as mpe_buyoff_action preserving type) as MfgBuyOffAction,
log.MfgObjectLogEventType as MfgObjectLogEventType,
@Semantics.businessDate.at: true
cast(log.CreationDateTime as mpe_perform_timestamp) as EventDateTime,
cast(log.CreatedByUser as mpe_performed_by_user preserving type) as ShopFloorExecutionUser,
@Semantics.user.createdBy: true
cast(log.CreatedByUser as mpe_create_user preserving type) as CreatedByUser,
@Semantics.systemDateTime.createdAt: true
cast(log.CreationDateTime as mpe_create_timestampl preserving type) as CreationDateTime,
case log.MfgObjectLogEventType
when 5 then cast(100 as rszsort) -- Create
when 1 then cast(101 as rszsort) -- Handover to Production
when 3 then cast(102 as rszsort) -- Release
else cast(103 as rszsort)
end as SortField,
log.ShopFloorItem as ShopFloorItem,
cast(0 as mpe_sfi_at_oai_group_id) as OperationActivitySFIGroup,
log.OpActyNtwkInstance as OpActyNtwkInstance,
log.OpActyNtwkElement as OpActyNtwkElement,
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(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,
log.MfgActionReasonCodeGroupCtlg as MfgActionReasonCodeGroupCtlg,
log.MfgActionReasonCodeGroup as MfgActionReasonCodeGroup,
log.ManufacturingActionReasonCode as ManufacturingActionReasonCode,
'' as MfgExecutionDocumentNote,
cast('' as equnr) as Equipment,
log.SystemMessageNumber as SystemMessageNumber,
log.SystemMessageIdentification as SystemMessageIdentification,
log.MfgExecHistMsgVarblField1 as MfgExecHistMsgVarblField1,
log.MfgExecHistMsgVarblField2 as MfgExecHistMsgVarblField2,
log.MfgExecHistMsgVarblField3 as MfgExecHistMsgVarblField3,
log.MfgExecHistMsgVarblField4 as MfgExecHistMsgVarblField4,
cast('' as qdefectid ) as Defect,
log.MfgProcgExecAttachmentName as MfgProcgExecAttachmentName,
cast('' as mpe_buyoffsqnc_desc ) as MfgBuyoffDescription
}
where
log.ManufacturingOrder <> ''
and log.MfgObjectLogObjectType = 'OA'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MANUFACTURINGOBJECTLOG",
"I_MFGBUYOFFACTIONHISTORY",
"I_MFGDEFECTHISTORY",
"I_MFGEXECDOCUMENTNOTE",
"P_MFGEXECEVENTHISTOPACTY"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/