@AbapCatalog.sqlViewName: 'PDLVNDIBDLVPF'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #MANDATORY
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.compositionRoot:true
define view P_DlvNodesForInbDelivProcFlow
// Lane 1 (Order Creation): Sales Order/ Purchase Order -> Outbound Delivery
as select distinct from I_DeliveryDocumentItem as DeliveryItem
association [1..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key cast(DeliveryItem.ReferenceSDDocument as preced_doc ) as PrecedingDocument,
key case when DeliveryItem.ReferenceSDDocumentCategory = 'V'
then DeliveryItem.ReferenceSDDocumentCategory
when DeliveryItem.ReferenceSDDocumentCategory = 'I'
then DeliveryItem.ReferenceSDDocumentCategory
when DeliveryItem.ReferenceSDDocumentCategory = 'E' or DeliveryItem.ReferenceSDDocumentCategory = 'F'
then DeliveryItem.ReferenceSDDocumentCategory
else cast('C' as preced_doc_cat)
end as PrecedingDocumentCategory,
key DeliveryItem._DeliveryDocument.DeliveryDocument as SubsequentDocument,
key DeliveryItem._DeliveryDocument.SDDocumentCategory as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast('' as vbeln_va)
else cast(DeliveryItem.ReferenceSDDocument as vbeln_va)
end as SalesDocument,
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast(DeliveryItem.ReferenceSDDocument as ebeln)
else cast('' as ebeln)
end as PurchaseOrder,
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
cast('' as mblnr ) as MaterialDocument,
cast('0000' as mjahr) as MaterialDocumentYear,
cast('' as vbeln) as BillingDocument,
cast('' as /scmtms/tor_id) as FreightOrder,
_DeliveryDocument
}
// Lane 3 (Picking): Outbound Delivery > Non-cancelled WM Transfer Orders
union all select distinct from P_DeliveryProcFlowTransfOrd as WMTransferOrder
association [1..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key WMTransferOrder.DeliveryDocument,
key WMTransferOrder.PrecedingDocument,
key WMTransferOrder.PrecedingDocumentCategory,
key WMTransferOrder.SubsequentDocument,
key cast('Q1' as subsequ_doc_cat) as SubsequentDocumentCategory,
WMTransferOrder.PrecedingDocumentCategory as SDDocumentCategory,
cast('' as vbeln_va) as SalesDocument,
cast('' as ebeln) as PurchaseOrder,
WMTransferOrder.WarehouseNumber,
WMTransferOrder.WrhsMgmtTransferOrder,
cast('' as mblnr ) as MaterialDocument,
cast('0000' as mjahr) as MaterialDocumentYear,
cast('' as vbeln) as BillingDocument,
cast('' as /scmtms/tor_id) as FreightOrder,
_DeliveryDocument
}
// Lane 4 (Goods Movement): Outbound Delivery > Latest Material Document Goods Issue/ Cancel Goods Issue
union all select distinct from P_OutbDelivReltdToInbDeliv as OutboundDelivery
inner join P_LtstGIReltdMatDoc as GoodsIssue on GoodsIssue.PrecedingDocument = OutboundDelivery.PrecedingDocument
// left outer to many join P_DelivProcFlowLatestTransfOrd as WMTransferOrder on WMTransferOrder.DeliveryDocument = GoodsIssue.PrecedingDocument
association [1..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key cast(GoodsIssue.PrecedingDocument as vbeln_vl) as DeliveryDocument,
key GoodsIssue.PrecedingDocument,
key GoodsIssue.PrecedingDocumentCategory,
key GoodsIssue.SubsequentDocument,
// Timestamps are maintained in SubsequentDocumentItem
key
case GoodsIssue.SubsequentDocumentCategory
when 'R'
then
case when GoodsIssue.CreationTime = GoodsIssue.SubsequentDocumentItem
then
cast ('R3' as subsequ_doc_cat)
else
cast ('R1' as subsequ_doc_cat)
end
when 'h'
then
case when GoodsIssue.CreationTime = GoodsIssue.SubsequentDocumentItem
then
cast ('h3' as subsequ_doc_cat)
else
cast ('h1' as subsequ_doc_cat)
end
end as SubsequentDocumentCategory,
GoodsIssue.PrecedingDocumentCategory as SDDocumentCategory,
cast('' as vbeln_va) as SalesDocument,
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
// Material Document
GoodsIssue.MaterialDocument,
GoodsIssue.MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
//Freight Order
cast('' as /scmtms/tor_id) as FreightOrder,
_DeliveryDocument
}
// Lane 4 (Goods Movement): WM Transfer Order > Latest Material Document Goods Issue/ Cancel Goods Issue
union all select distinct from P_OutbDelivReltdToInbDeliv as OutboundDelivery
inner join P_DelivProcFlowLatestTransfOrd as LatestWMTransferOrder on LatestWMTransferOrder.DeliveryDocument = OutboundDelivery.PrecedingDocument
inner join P_LtstGIReltdMatDoc as GoodsIssue on GoodsIssue.PrecedingDocument = OutboundDelivery.PrecedingDocument
inner join P_DeliveryProcFlowTransfOrd as WMTransferOrder on WMTransferOrder.DeliveryDocument = LatestWMTransferOrder.DeliveryDocument
and WMTransferOrder.PrecedingDocument = LatestWMTransferOrder.DeliveryDocument
and WMTransferOrder.PrecedingDocumentCategory = GoodsIssue.PrecedingDocumentCategory
and WMTransferOrder.SubsequentDocument = LatestWMTransferOrder.WrhsMgmtTransferOrder
and WMTransferOrder.SubsequentDocumentCategory = 'Q'
association [1..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key cast(GoodsIssue.PrecedingDocument as vbeln_vl) as DeliveryDocument,
key cast(WMTransferOrder.SubsequentDocument as preced_doc) as PrecedingDocument,
key cast('Q1' as preced_doc_cat) as PrecedingDocumentCategory,
key GoodsIssue.SubsequentDocument,
key
case GoodsIssue.SubsequentDocumentCategory
when 'R'
then
case when GoodsIssue.CreationTime = GoodsIssue.SubsequentDocumentItem
then
cast ('R3' as subsequ_doc_cat)
else
cast ('R1' as subsequ_doc_cat)
end
when 'h'
then
case when GoodsIssue.CreationTime = GoodsIssue.SubsequentDocumentItem
then
cast ('h3' as subsequ_doc_cat)
else
cast ('h1' as subsequ_doc_cat)
end
end as SubsequentDocumentCategory,
GoodsIssue.PrecedingDocumentCategory as SDDocumentCategory,
cast('' as vbeln_va) as SalesDocument,
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
// Material Document
GoodsIssue.MaterialDocument,
GoodsIssue.MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
//Freight Order
cast('' as /scmtms/tor_id) as FreightOrder,
_DeliveryDocument
}
// Lane 5 (Invoicing): Outbound Delivery > Billing Document
union all select distinct from I_SDDocumentProcessFlow as BillingDocument
association [1..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key cast(BillingDocument.PrecedingDocument as vbeln_vl) as DeliveryDocument,
key BillingDocument.PrecedingDocument,
key BillingDocument.PrecedingDocumentCategory,
key BillingDocument.SubsequentDocument,
key BillingDocument.SubsequentDocumentCategory,
BillingDocument.PrecedingDocumentCategory as SDDocumentCategory,
cast('' as vbeln_va) as SalesDocument,
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
// Material Document
cast('' as mblnr ) as MaterialDocument,
cast('0000' as mjahr) as MaterialDocumentYear,
// Billing Document
BillingDocument.SubsequentDocument as BillingDocument,
//Freight Order
cast('' as /scmtms/tor_id) as FreightOrder,
_DeliveryDocument
}
where
BillingDocument.SubsequentDocumentCategory = 'M'
or BillingDocument.SubsequentDocumentCategory = 'O'
or BillingDocument.SubsequentDocumentCategory = 'P'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DELIVERYDOCUMENT",
"I_DELIVERYDOCUMENTITEM",
"I_SDDOCUMENTPROCESSFLOW",
"P_DELIVERYPROCFLOWTRANSFORD",
"P_DELIVPROCFLOWLATESTTRANSFORD",
"P_LTSTGIRELTDMATDOC",
"P_OUTBDELIVRELTDTOINBDELIV"
],
"ASSOCIATED":
[
"I_DELIVERYDOCUMENT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/