@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Material Documents Document Flow'
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass:#MIXED
@DataAging.noAgingRestriction:true
//////////////////////////////////////////////////////////////////////////
//
// Defined document categories are (historical emerged):
// PR -> PuRchase order
// D -> inbound Delivery
// RD -> Returns Delivery for Order
// OD -> Delivery
// PO -> Production Order
// MO -> Maintenance Order
// PC -> ProCess order
// M -> Material document
// R -> Reversal (canceled) material document
// R2 -> Reversal of a transfer material document
// M2 -> Inner plant trfansfer document
// IP -> cross plant transfer Issuing Plant
// RP -> cross plant transfer Receiving Plant
// A -> Accounting document
// PI -> Physical inventory document
// RE -> Reservation
// WT -> Warehouse Task
// PQ -> Purchase ReQuisition
//
//////////////////////////////////////////////////////////////////////////
define view entity Mmim_MatDocDocFlow
as
select distinct from I_MaterialDocumentRecord
inner join ekko
on I_MaterialDocumentRecord.PurchaseOrder = ekko.ebeln
// PRECEDING DOCUMENT: PuRchase order (PR)
// SUBSEQUENT DOCUMENT: Material document (M)
{
key cast(I_MaterialDocumentRecord.PurchaseOrder as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(I_MaterialDocumentRecord.MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(I_MaterialDocumentRecord.PurchaseOrderItem as char20) as PrecedingDocumentItem,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as SubsequentDocumentItem,
key I_MaterialDocumentRecord.MaterialDocument as MaterialDocument,
key I_MaterialDocumentRecord.MaterialDocumentYear as MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
case ekko.bstyp
when 'L' then cast ('SA' as char2)
else cast ('PR' as char2)
end as PrecedingDocumentCategory,
cast('M ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
I_MaterialDocumentRecord.IsAutomaticallyCreated = ''
and I_MaterialDocumentRecord.PurchaseOrder <> ''
and I_MaterialDocumentRecord.MaterialDocument <> ''
and I_MaterialDocumentRecord.DeliveryDocument = ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
and I_MaterialDocumentRecord.MaterialDocumentItem = I_MaterialDocumentRecord.OriginalMaterialDocumentItem
union all
select distinct from I_MaterialDocumentRecord
inner join likp
on I_MaterialDocumentRecord.DeliveryDocument = likp.vbeln
inner join tvlk
on likp.lfart = tvlk.lfart
// PRECEDING DOCUMENT: Inbound Delivery (D), Outbound Delivery (OD)
// SUBSEQUENT DOCUMENT: Material document (M)
{
key cast(I_MaterialDocumentRecord.DeliveryDocument as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(I_MaterialDocumentRecord.MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(I_MaterialDocumentRecord.DeliveryDocumentItem as char20) as PrecedingDocumentItem,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as SubsequentDocumentItem,
key I_MaterialDocumentRecord.MaterialDocument as MaterialDocument,
key I_MaterialDocumentRecord.MaterialDocumentYear as MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
case tvlk.vbtyp
when '7' then 'D'
when 'J' then 'OD'
when 'T' then 'RD'
else '7'
end as PrecedingDocumentCategory,
// cast('D' as char2) as PrecedingDocumentCategory,
cast('M ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
I_MaterialDocumentRecord.IsAutomaticallyCreated = ''
and I_MaterialDocumentRecord.DeliveryDocument <> ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
union all
select distinct from I_MaterialDocumentRecord as m
inner join aufk as o
on m.ManufacturingOrder = o.aufnr
// PRECEDING DOCUMENT: Production Order (PO), ProCess order (PC)
// SUBSEQUENT DOCUMENT: Material document (M)
{
key cast(m.ManufacturingOrder as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(m.MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(m.MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(m.ManufacturingOrderItem as char20) as PrecedingDocumentItem,
key cast(m.MaterialDocumentItem as char20) as SubsequentDocumentItem,
key m.MaterialDocument as MaterialDocument,
key m.MaterialDocumentYear as MaterialDocumentYear,
key cast(m.MaterialDocumentItem as char20) as MaterialDocumentItem,
key m.GoodsMovementType as GoodsMovementType,
key m.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key m.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
case
when o.autyp = '10' then 'PO'
// when o.autyp = '30' then 'MO' "mowed to separate union
when o.autyp = '40' then 'PC'
when o.autyp = '05' then 'CC'
else '00' //UNDEFINED: If that case occurs, this part has to be adjusted
end as PrecedingDocumentCategory,
cast('M ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
m.IsAutomaticallyCreated = ''
and m.ManufacturingOrder <> ''
and m.ManufacturingOrderItem <> '0000'
and m.MaterialDocument <> ''
and m.ReversedMaterialDocument = ''
and m.MaterialDocumentRecordType = 'MDOC'
union all
select distinct from Mmim_MatDocAccounting as macc
inner join I_MaterialDocumentRecord on macc.MaterialDocument = I_MaterialDocumentRecord.MaterialDocument
and macc.MaterialDocumentYear = I_MaterialDocumentRecord.MaterialDocumentYear
and macc.PrecedingDocumentItem = I_MaterialDocumentRecord.MaterialDocumentItem
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
// PRECEDING DOCUMENT: Material document (M), Reversal of a transfer posting (R2)
// SUBSEQUENT DOCUMENT: Accounting document (A)
{
key macc.PrecedingDocument,
key macc.PrecedingDocumentYear,
key macc.SubsequentDocument,
key macc.SubsequentDocumentYear,
key macc.PrecedingDocumentItem,
key macc.SubsequentDocumentItem,
key macc.MaterialDocument,
key macc.MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key macc.FiscalYear,
case
when I_MaterialDocumentRecord.IssuingOrReceivingStockType <> ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.DeliveryDocument = ''
and I_MaterialDocumentRecord.ManufacturingOrder = ''
and I_MaterialDocumentRecord.PurchaseOrder = ''
then 'R2' //RP
else macc.PrecedingDocumentCategory
end as PrecedingDocumentCategory,
macc.SubsequentDocumentCategory,
macc.CompanyCode
}
// Transfer material documents (LBBSA_CID <> '')
union all
select distinct from I_MaterialDocumentRecord
// PRECEDING DOCUMENT: One step transfer, Inner plant transfer document (M2)
// SUBSEQUENT DOCUMENT: Reversal of a transfer material document (R2)
{
key cast(MaterialDocument as mmim_preced_doc) as PrecedingDocument,
key cast(MaterialDocumentYear as char4) as PrecedingDocumentYear,
key cast(MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(MaterialDocumentItem as char20) as PrecedingDocumentItem,
key cast(MaterialDocumentItem as char20) as SubsequentDocumentItem,
key MaterialDocument as MaterialDocument,
key MaterialDocumentYear as MaterialDocumentYear,
key cast(MaterialDocumentItem as char20) as MaterialDocumentItem,
key GoodsMovementType as GoodsMovementType,
key IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast('M2' as char2) as PrecedingDocumentCategory,
cast('R2' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
IsAutomaticallyCreated = ''
and IssuingOrReceivingStockType <> ''
and MaterialDocumentRecordType = 'MDOC'
and ReversedMaterialDocument = ''
and DeliveryDocument = ''
and ManufacturingOrder = ''
and PurchaseOrder = ''
union all
select distinct from I_MaterialDocumentRecord
// PRECEDING DOCUMENT: Cross-plant transfer, issuing plant (IP)
// SUBSEQUENT DOCUMENT: Cross-plant transfer, receiving plant (RP)
{
key cast(concat(MaterialDocument, cast(cast(MaterialDocumentItem as abap.int2) as char10)) as mmim_preced_doc) as PrecedingDocument,
key cast(MaterialDocumentYear as char4) as PrecedingDocumentYear,
key cast(concat(MaterialDocument, cast((cast(MaterialDocumentItem as abap.int2) + 1) as char20)) as mmim_subsequ_doc) as SubsequentDocument,
key cast(MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(MaterialDocumentItem as char20) as PrecedingDocumentItem,
key cast(MaterialDocumentItem as char20) as SubsequentDocumentItem,
key MaterialDocument as MaterialDocument,
key MaterialDocumentYear as MaterialDocumentYear,
key cast(MaterialDocumentItem as char20) as MaterialDocumentItem,
key GoodsMovementType as GoodsMovementType,
key IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast('IP' as char2) as PrecedingDocumentCategory,
cast('RP' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
IsAutomaticallyCreated = ''
and IssuingOrReceivingStockType <> ''
and MaterialDocumentRecordType = 'MDOC'
and ReversedMaterialDocument = ''
and DeliveryDocument = ''
and ManufacturingOrder = ''
and PurchaseOrder = ''
// case: 4710 was created, cancelled by 4711.
// 4710 is selected as MaterialDocument and 4711 is the subsequent document
union all
select distinct from I_MaterialDocumentRecord // association between mblnr and smbln
// PRECEDING DOCUMENT: Material document (M), Inner plant transfer document (M2)
// SUBSEQUENT DOCUMENT: Canceled material document (R), Reversal of a transfer material document
{
key cast(ReversedMaterialDocument as mmim_preced_doc) as PrecedingDocument,
key cast(ReversedMaterialDocumentYear as char4) as PrecedingDocumentYear,
key cast(MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(ReversedMaterialDocumentItem as char20) as PrecedingDocumentItem,
key cast(MaterialDocumentItem as char20) as SubsequentDocumentItem,
key ReversedMaterialDocument as MaterialDocument,
key ReversedMaterialDocumentYear as MaterialDocumentYear,
key cast(ReversedMaterialDocumentItem as char20) as MaterialDocumentItem,
key GoodsMovementType as GoodsMovementType,
key IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key Plant as Plant,
key cast('0000' as char4) as FiscalYear,
case
when InventoryStockType <> ''
and ReversedMaterialDocument <> ''
and DeliveryDocument = ''
and ManufacturingOrder = ''
and PurchaseOrder = ''
then 'M2'
else 'M'
end as PrecedingDocumentCategory,
case
when InventoryStockType <> ''
and ReversedMaterialDocument <> ''
and DeliveryDocument = ''
and ManufacturingOrder = ''
and PurchaseOrder = ''
then 'R2'
else 'R'
end as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
IsAutomaticallyCreated = ''
and MaterialDocument <> ''
and ReversedMaterialDocument <> ''
and MaterialDocumentRecordType = 'MDOC'
// case: 4710 was created, cancelled by 4711.
// 4711 is selected as MaterialDocument and 4710 is the precending document
union all
select distinct from I_MaterialDocumentRecord
// PRECEDING DOCUMENT: Canceled material document (R)
// SUBSEQUENT DOCUMENT: Material document (M)
{
key cast(ReversedMaterialDocument as mmim_preced_doc) as PrecedingDocument,
key cast(ReversedMaterialDocumentYear as char4) as PrecedingDocumentYear,
key cast(MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(ReversedMaterialDocumentItem as char20) as PrecedingDocumentItem,
key cast(MaterialDocumentItem as char20) as SubsequentDocumentItem,
key MaterialDocument as MaterialDocument,
key MaterialDocumentYear as MaterialDocumentYear,
key cast(MaterialDocumentItem as char20) as MaterialDocumentItem,
key GoodsMovementType as GoodsMovementType,
key IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast('R' as char2) as PrecedingDocumentCategory,
cast('M ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
IsAutomaticallyCreated = ''
and MaterialDocument <> ''
and ReversedMaterialDocument <> ''
and MaterialDocumentRecordType = 'MDOC'
union all
select distinct from I_MaterialDocumentRecord
inner join iseg as pi_document_item on I_MaterialDocumentRecord.MaterialDocument = pi_document_item.mblnr
and I_MaterialDocumentRecord.MaterialDocumentYear = pi_document_item.mjahr
and I_MaterialDocumentRecord.MaterialDocumentItem = pi_document_item.zeile
// PRECEDING DOCUMENT: Physical inventory Document (PI)
// SUBSEQUENT DOCUMENT: Material document (M)
{
key cast(pi_document_item.iblnr as mmim_preced_doc) as PrecedingDocument,
key cast(pi_document_item.gjahr as char4) as PrecedingDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(I_MaterialDocumentRecord.MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(pi_document_item.zeili as char20) as PrecedingDocumentItem,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as SubsequentDocumentItem,
key I_MaterialDocumentRecord.MaterialDocument as MaterialDocument,
key I_MaterialDocumentRecord.MaterialDocumentYear as MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key cast(pi_document_item.gjahr as char4) as FiscalYear,
cast('PI' as char2) as PrecedingDocumentCategory,
cast('M ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
I_MaterialDocumentRecord.IsAutomaticallyCreated = ''
and I_MaterialDocumentRecord.MaterialDocument <> ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
union all
select distinct from I_MaterialDocumentRecord as m
inner join I_ReservationHeader as r
on m.Reservation = r.Reservation
// PRECEDING DOCUMENT: Reservation (RE)
// SUBSEQUENT DOCUMENT: Material document (M) / Inner plant transfer document (M2)
{
key cast(m.Reservation as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(m.MaterialDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast(m.MaterialDocumentYear as char4) as SubsequentDocumentYear,
key cast(m.ReservationItem as char20) as PrecedingDocumentItem,
key cast(m.MaterialDocumentItem as char20) as SubsequentDocumentItem,
key m.MaterialDocument as MaterialDocument,
key m.MaterialDocumentYear as MaterialDocumentYear,
key cast(m.MaterialDocumentItem as char20) as MaterialDocumentItem,
key m.GoodsMovementType as GoodsMovementType,
key m.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key m.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast('RE' as char2) as PrecedingDocumentCategory,
case
when m.InventoryStockType <> ''
and m.ReversedMaterialDocument = ''
and m.DeliveryDocument = ''
and m.ManufacturingOrder = ''
and m.PurchaseOrder = ''
and m.IssgOrRcvgStkIdfgPlant <> ''
then 'M2'
else cast('M ' as char2) end as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
m.IsAutomaticallyCreated = ''
and m.Reservation <> '0000000000'
and m.MaterialDocument <> ''
and m.MaterialDocumentRecordType = 'MDOC'
union all
select distinct from I_MaterialDocumentRecord as m
inner join I_ReservationHeader as r
on m.Reservation = r.Reservation
and r.OrderID <> '000000000000'
inner join afih as mo
on mo.aufnr = r.OrderID
// PRECEDING DOCUMENT: Maintenance Order (MO)
// SUBSEQUENT DOCUMENT: Reservation (RE)
{
key cast(mo.aufnr as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(m.Reservation as mmim_subsequ_doc) as SubsequentDocument,
key cast('0000' as char4) as SubsequentDocumentYear,
key cast('' as char20) as PrecedingDocumentItem,
key cast('' as char20) as SubsequentDocumentItem,
key m.MaterialDocument as MaterialDocument,
key m.MaterialDocumentYear as MaterialDocumentYear,
key cast(m.MaterialDocumentItem as char20) as MaterialDocumentItem,
key m.GoodsMovementType as GoodsMovementType,
key m.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key m.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast('MO' as char2) as PrecedingDocumentCategory,
cast('RE' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
m.IsAutomaticallyCreated = ''
and m.Reservation <> '0000000000'
and m.MaterialDocument <> ''
and m.MaterialDocumentRecordType = 'MDOC'
union all
select distinct from I_MaterialDocumentRecord
inner join I_PurchasingDocumentItem
on I_MaterialDocumentRecord.PurchaseOrder = I_PurchasingDocumentItem.PurchasingDocument
and I_MaterialDocumentRecord.PurchaseOrderItem = I_PurchasingDocumentItem.PurchasingDocumentItem
// PRECEDING DOCUMENT: Purchase Requisition (PQ)
// SUBSEQUENT DOCUMENT: PuRchase order (PR)/ Scedule Agreement (SA)
{
key cast(I_PurchasingDocumentItem.PurchaseRequisition as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(I_PurchasingDocumentItem.PurchasingDocument as mmim_subsequ_doc) as SubsequentDocument,
key cast('0000' as char4) as SubsequentDocumentYear,
key cast(I_PurchasingDocumentItem.PurchaseRequisitionItem as char20) as PrecedingDocumentItem,
key cast(I_PurchasingDocumentItem.PurchasingDocumentItem as char20) as SubsequentDocumentItem,
key I_MaterialDocumentRecord.MaterialDocument as MaterialDocument,
key I_MaterialDocumentRecord.MaterialDocumentYear as MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast ('PQ' as char2) as PrecedingDocumentCategory,
case I_PurchasingDocumentItem.PurchasingDocumentCategory
when 'L' then cast ('SA' as char2)
else cast ('PR' as char2)
end as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
I_MaterialDocumentRecord.IsAutomaticallyCreated = ''
and I_MaterialDocumentRecord.PurchaseOrder <> ''
and I_MaterialDocumentRecord.DeliveryDocument = ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
and I_MaterialDocumentRecord.MaterialDocumentItem = I_MaterialDocumentRecord.OriginalMaterialDocumentItem
and I_PurchasingDocumentItem.PurchasingDocument <> ''
and I_PurchasingDocumentItem.PurchaseRequisition <> ''
union all
select distinct from I_MaterialDocumentRecord
inner join I_PurchasingDocumentItem
on I_MaterialDocumentRecord.PurchaseOrder = I_PurchasingDocumentItem.PurchasingDocument
and I_MaterialDocumentRecord.PurchaseOrderItem = I_PurchasingDocumentItem.PurchasingDocumentItem
inner join I_MaintOrdCompPurOrderC
on I_MaterialDocumentRecord.PurchaseOrder = I_MaintOrdCompPurOrderC.PurchaseOrder
and I_MaterialDocumentRecord.PurchaseOrderItem = I_MaintOrdCompPurOrderC.PurchaseOrderItem
and I_MaintOrdCompPurOrderC.MaintenanceOrder <> ''
// PRECEDING DOCUMENT: MaintenanceOrder (MO)
// SUBSEQUENT DOCUMENT: Purchase Requisition (PQ)
{
key cast(I_MaintOrdCompPurOrderC.MaintenanceOrder as mmim_preced_doc) as PrecedingDocument,
key cast('0000' as char4) as PrecedingDocumentYear,
key cast(I_PurchasingDocumentItem.PurchaseRequisition as mmim_subsequ_doc) as SubsequentDocument,
key cast('0000' as char4) as SubsequentDocumentYear,
key cast('' as char20) as PrecedingDocumentItem,
key cast('' as char20) as SubsequentDocumentItem,
key I_MaterialDocumentRecord.MaterialDocument as MaterialDocument,
key I_MaterialDocumentRecord.MaterialDocumentYear as MaterialDocumentYear,
key cast(I_MaterialDocumentRecord.MaterialDocumentItem as char20) as MaterialDocumentItem,
key I_MaterialDocumentRecord.GoodsMovementType as GoodsMovementType,
key I_MaterialDocumentRecord.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key I_MaterialDocumentRecord.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
cast ('MO' as char2) as PrecedingDocumentCategory,
cast ('PQ' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where
I_MaterialDocumentRecord.IsAutomaticallyCreated = ''
and I_MaterialDocumentRecord.PurchaseOrder <> ''
and I_MaterialDocumentRecord.DeliveryDocument = ''
and I_MaterialDocumentRecord.ReversedMaterialDocument = ''
and I_MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
and I_MaterialDocumentRecord.MaterialDocumentItem = I_MaterialDocumentRecord.OriginalMaterialDocumentItem
and I_PurchasingDocumentItem.PurchasingDocument <> ''
and I_PurchasingDocumentItem.PurchaseRequisition <> ''
and I_MaintOrdCompPurOrderC.MaintenanceOrder <> ''
union all
select distinct from I_EWM_ConfirmedWarehouseTask_2 as wt
inner join I_MaterialDocumentRecord as m
on wt.MaterialDocument <> ''
and wt.MaterialDocument = m.MaterialDocument
// PRECEDING DOCUMENT: Material document (M)
// SUBSEQUENT DOCUMENT: Warehouse Task (WT)
{
key cast(m.MaterialDocument as mmim_preced_doc) as PrecedingDocument,
key cast(m.MaterialDocumentYear as char4) as PrecedingDocumentYear,
key cast(wt.WarehouseTask as mmim_subsequ_doc) as SubsequentDocument,
key cast('0000' as char4) as SubsequentDocumentYear,
key cast(m.MaterialDocumentItem as char20) as PrecedingDocumentItem,
key cast(wt.WarehouseTaskItem as char20) as SubsequentDocumentItem,
key m.MaterialDocument as MaterialDocument,
key m.MaterialDocumentYear as MaterialDocumentYear,
key cast(m.MaterialDocumentItem as char20) as MaterialDocumentItem,
key m.GoodsMovementType as GoodsMovementType,
key m.IssuingOrReceivingPlant as IssuingOrReceivingPlant,
key m.Plant as Plant,
key cast('0000' as char4) as FiscalYear,
case
when m.InventoryStockType <> ''
and m.ReversedMaterialDocument = ''
and m.DeliveryDocument = ''
and m.ManufacturingOrder = ''
and m.PurchaseOrder = ''
and m.IssgOrRcvgStkIdfgPlant <> ''
then 'R2'
else cast('M ' as char2) end as PrecedingDocumentCategory,
cast('WT' as char2) as SubsequentDocumentCategory,
cast('' as bukrs) as CompanyCode
}
where m.IsAutomaticallyCreated = ''