@AbapCatalog.sqlViewName: 'PDLVPROCFLOW'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Delivery Process Flow'
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
define view P_DeliveryProcFlow
// only in STO: Purchase Order (V) -> Delivery (J, 7)
// or in Returns from Customer
as select distinct from I_DeliveryDocumentItem as DeliveryItem
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key cast(DeliveryItem.ReferenceSDDocument as preced_doc ) as PrecedingDocument,
key DeliveryItem.ReferenceSDDocumentCategory as PrecedingDocumentCategory,
key DeliveryItem._DeliveryDocument.DeliveryDocument as SubsequentDocument,
key DeliveryItem._DeliveryDocument.SDDocumentCategory as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
// Sales Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast('' as vbeln_va)
else cast(DeliveryItem.ReferenceSDDocument as vbeln_va)
end as SalesDocument,
// Purchase Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast(DeliveryItem.ReferenceSDDocument as ebeln)
else cast('' as ebeln)
end 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
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
DeliveryItem._DeliveryDocument.SDDocumentCategory <> 'T'
or(
DeliveryItem.ReferenceSDDocumentCategory = 'H'
and DeliveryItem._DeliveryDocument.SDDocumentCategory = 'T'
)
// only in rSTO (1 step): Purchase Order (V) -> Returns Delivery (T)
union all select distinct from I_DeliveryDocumentItem as DeliveryItem
inner join I_PurchasingDocHistory as GoodsIssue on DeliveryItem.ReferenceSDDocument = GoodsIssue.PurchasingDocument
left outer join P_LtstGIReltdMatDoc as GoodsReceipt on GoodsReceipt.PrecedingDocument = DeliveryItem.DeliveryDocument
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key cast(DeliveryItem.ReferenceSDDocument as preced_doc ) as PrecedingDocument,
key DeliveryItem.ReferenceSDDocumentCategory as PrecedingDocumentCategory,
key DeliveryItem._DeliveryDocument.DeliveryDocument as SubsequentDocument,
key DeliveryItem._DeliveryDocument.SDDocumentCategory as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
// Sales Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast('' as vbeln_va)
else cast(DeliveryItem.ReferenceSDDocument as vbeln_va)
end as SalesDocument,
// Purchase Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast(DeliveryItem.ReferenceSDDocument as ebeln)
else cast('' as ebeln)
end 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
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
DeliveryItem._DeliveryDocument.SDDocumentCategory = 'T'
and GoodsIssue.PurchaseOrderTransactionType = '1'
and GoodsReceipt.MaterialDocument = GoodsIssue.MaterialDocument
// only rSTO (2-step): Purchase Order (V) -> Goods Issue(negative Goods Receipt) (R1)
union all select distinct from I_DeliveryDocumentItem as DeliveryItem
inner join I_PurchasingDocHistory as GoodsIssue on DeliveryItem.ReferenceSDDocument = GoodsIssue.PurchasingDocument
left outer join P_LtstGIReltdMatDoc as GoodsReceipt on GoodsReceipt.PrecedingDocument = DeliveryItem.DeliveryDocument
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key cast(DeliveryItem.ReferenceSDDocument as preced_doc ) as PrecedingDocument,
key DeliveryItem.ReferenceSDDocumentCategory as PrecedingDocumentCategory,
key GoodsIssue.MaterialDocument as SubsequentDocument,
key cast('R1' as subsequ_doc_cat) as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
// Sales Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast('' as vbeln_va)
else cast(DeliveryItem.ReferenceSDDocument as vbeln_va)
end as SalesDocument,
// Purchase Order
case DeliveryItem.ReferenceSDDocumentCategory
when 'V' then cast(DeliveryItem.ReferenceSDDocument as ebeln)
else cast('' as ebeln)
end 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
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
GoodsIssue.PurchaseOrderTransactionType = '1'
and DeliveryItem._DeliveryDocument.SDDocumentCategory = 'T'
and(
GoodsReceipt.MaterialDocument <> GoodsIssue.MaterialDocument
or(
GoodsReceipt.MaterialDocument = GoodsIssue.MaterialDocument
and GoodsReceipt.MaterialDocumentYear <> GoodsIssue.MaterialDocumentYear
)
)
// only STO (2-step): Goods Issue (R1) -> Goods Receipt (R2)
union all select distinct from I_PurchasingDocHistory as GoodsReceipt
inner join I_DeliveryDocumentItem as DeliveryItem on DeliveryItem.ReferenceSDDocument = GoodsReceipt.PurchasingDocument
inner join P_LtstGIReltdMatDoc as GoodsIssue on DeliveryItem.DeliveryDocument = GoodsIssue.PrecedingDocument
left outer join I_SDDocumentProcessFlow as InboundDelivery on InboundDelivery.PrecedingDocument = DeliveryItem.DeliveryDocument
and InboundDelivery.SubsequentDocumentCategory = '7'
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key GoodsIssue.SubsequentDocument as PrecedingDocument,
key GoodsIssue.SubsequentDocumentCategory as PrecedingDocumentCategory,
key GoodsReceipt.MaterialDocument as SubsequentDocument,
key cast('R2' as subsequ_doc_cat) as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
// Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
// Material Document
GoodsReceipt.MaterialDocument as MaterialDocument,
GoodsReceipt.MaterialDocumentYear as MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
GoodsReceipt.PurchaseOrderTransactionType = '1'
and InboundDelivery.SubsequentDocumentCategory is null
and DeliveryItem._DeliveryDocument.SDDocumentCategory = 'J'
and(
GoodsReceipt.MaterialDocument <> GoodsIssue.MaterialDocument
or GoodsReceipt.MaterialDocument = GoodsIssue.MaterialDocument
and GoodsReceipt.MaterialDocumentYear <> GoodsIssue.MaterialDocumentYear
)
// only rSTO (2-step): Goods Issue (I) -> Returns Delivery (T)
union all select distinct from I_PurchasingDocHistory as GoodsIssue
inner join I_DeliveryDocumentItem as DeliveryItem on DeliveryItem.ReferenceSDDocument = GoodsIssue.PurchasingDocument
inner join P_LtstGIReltdMatDoc as ReturnDelivery on DeliveryItem.DeliveryDocument = ReturnDelivery.PrecedingDocument
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key DeliveryItem.DeliveryDocument,
key GoodsIssue.MaterialDocument as PrecedingDocument,
key cast('R1' as subsequ_doc_cat) as PrecedingDocumentCategory,
key ReturnDelivery.PrecedingDocument as SubsequentDocument,
key ReturnDelivery.PrecedingDocumentCategory as SubsequentDocumentCategory,
DeliveryItem._DeliveryDocument.SDDocumentCategory,
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
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 as MaterialDocument,
GoodsIssue.MaterialDocumentYear as MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
GoodsIssue.PurchaseOrderTransactionType = '1'
and ReturnDelivery.PrecedingDocumentCategory = 'T'
and(
ReturnDelivery.MaterialDocument <> GoodsIssue.MaterialDocument
or(
ReturnDelivery.MaterialDocument = GoodsIssue.MaterialDocument
and ReturnDelivery.MaterialDocumentYear <> GoodsIssue.MaterialDocumentYear
)
)
// Delivery itself
union all select distinct from I_DeliveryDocument as Delivery
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key Delivery.DeliveryDocument as DeliveryDocument,
key cast(Delivery.DeliveryDocument as preced_doc) as PrecedingDocument,
key cast(Delivery.SDDocumentCategory as preced_doc_cat) as PrecedingDocumentCategory,
key cast(' ' as subsequ_doc) as SubsequentDocument,
key cast(' ' as subsequ_doc_cat) as SubsequentDocumentCategory,
Delivery.SDDocumentCategory,
//Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
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
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
union all select distinct from C_RetsDelivProcFlowFreightOrd as FreightOrder
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key FreightOrder.ReturnsDelivery as DeliveryDocument,
key _DeliveryDocument.DeliveryDocument as PrecedingDocument,
key _DeliveryDocument.SDDocumentCategory as PrecedingDocumentCategory,
key FreightOrder.FreightOrder as SubsequentDocument,
key cast('F' as subsequ_doc_cat) as SubsequentDocumentCategory,
_DeliveryDocument.SDDocumentCategory 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
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
//Freight Order
FreightOrder.FreightOrder as FreightOrder,
// Associations
_DeliveryDocument
}
// WM Transfer Order
union all select distinct from P_DeliveryProcFlowTransfOrd as WMTransferOrder
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key WMTransferOrder.DeliveryDocument,
key WMTransferOrder.PrecedingDocument,
key WMTransferOrder.PrecedingDocumentCategory,
key WMTransferOrder.SubsequentDocument,
key WMTransferOrder.SubsequentDocumentCategory,
//Document Category
WMTransferOrder.PrecedingDocumentCategory as SDDocumentCategory,
//Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
WMTransferOrder.WarehouseNumber,
WMTransferOrder.WrhsMgmtTransferOrder,
// Material Document
cast('' as mblnr ) as MaterialDocument,
cast('0000' as mjahr) as MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
// WM Transfer Order with inb deliv
union all select distinct from I_SDDocumentProcessFlow as InboundDelivery
inner join P_DeliveryProcFlowTransfOrd as WMTransferOrder on InboundDelivery.SubsequentDocument = WMTransferOrder.DeliveryDocument
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key cast(InboundDelivery.PrecedingDocument as vbeln_vl) as DeliveryDocument,
key InboundDelivery.SubsequentDocument as PrecedingDocument,
key InboundDelivery.SubsequentDocumentCategory as PrecedingDocumentCategory,
key WMTransferOrder.SubsequentDocument,
key WMTransferOrder.SubsequentDocumentCategory,
//Document Category
InboundDelivery.PrecedingDocumentCategory as SDDocumentCategory,
//Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
WMTransferOrder.WarehouseNumber,
WMTransferOrder.WrhsMgmtTransferOrder,
// Material Document
cast('' as mblnr ) as MaterialDocument,
cast('0000' as mjahr) as MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
InboundDelivery.SubsequentDocumentCategory = '7'
// Custom Returns Delivery (T) -> (Partial/ Full) Goods Receipts
union all select distinct from I_SDDocumentProcessFlow as GoodsIssue
association [0..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,
key case GoodsIssue.SubsequentDocumentCategory
when 'R' then
case when GoodsIssue.GoodsMovementType = ''
then cast ('R3' as subsequ_doc_cat)
else cast ('R2' as subsequ_doc_cat)
end
when 'h' then
case when GoodsIssue.GoodsMovementType = ''
then cast ('h3' as subsequ_doc_cat)
else cast ('h2' as subsequ_doc_cat)
end
end as SubsequentDocumentCategory,
//Document Category
GoodsIssue.PrecedingDocumentCategory as SDDocumentCategory,
//Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
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.SubsequentDocument as MaterialDocument,
GoodsIssue.MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where GoodsIssue.PrecedingDocumentCategory = 'T' and
( GoodsIssue.SubsequentDocumentCategory = 'R' or
GoodsIssue.SubsequentDocumentCategory = 'h' )
// Billing Document
union all select distinct from I_SDDocumentProcessFlow as BillingDocument
association [0..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,
//Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
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,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
BillingDocument.SubsequentDocumentCategory = 'M'
or BillingDocument.SubsequentDocumentCategory = 'O'
or BillingDocument.SubsequentDocumentCategory = 'P'
// only STO (2 Step): Inbound Delivery (7)
//union all select distinct from I_SDDocumentProcessFlow as InboundDelivery
// inner join I_LatestGIRelatedMatDoc as GoodsIssue on InboundDelivery.PrecedingDocument = GoodsIssue.PrecedingDocument
//
//{
// key cast(InboundDelivery.PrecedingDocument as vbeln_vl) as DeliveryDocument,
// key GoodsIssue.SubsequentDocument as PrecedingDocument,
// key GoodsIssue.SubsequentDocumentCategory as PrecedingDocumentCategory,
// key InboundDelivery.SubsequentDocument,
// key InboundDelivery.SubsequentDocumentCategory,
//
// //Document Category
// InboundDelivery.PrecedingDocumentCategory as SDDocumentCategory,
//
// //Sales Order
// cast('' as vbeln_va) as SalesDocument,
//
// // Purchase Order
// 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
// cast('' as vbeln) as BillingDocument,
//
// // Inbound Delivery
// InboundDelivery.SubsequentDocument as InboundDelivery
//}
//where
// InboundDelivery.SubsequentDocumentCategory = '7'
// only STO (2 Step): Inbound Delivery (7) -> Goods Receipt (GR)
union all select distinct from I_SDDocumentProcessFlow as InboundDelivery
inner join P_LtstGIReltdMatDoc as GoodsReceipt on InboundDelivery.SubsequentDocument = GoodsReceipt.PrecedingDocument
inner join I_DeliveryDocument as Delivery on InboundDelivery.PrecedingDocument = Delivery.DeliveryDocument
association [0..1] to I_DeliveryDocument as _DeliveryDocument on $projection.DeliveryDocument = _DeliveryDocument.DeliveryDocument
{
key Delivery.DeliveryDocument,
key InboundDelivery.SubsequentDocument as PrecedingDocument,
key InboundDelivery.SubsequentDocumentCategory as PrecedingDocumentCategory,
key GoodsReceipt.SubsequentDocument as SubsequentDocument,
key cast('R2' as subsequ_doc_cat) as SubsequentDocumentCategory,
Delivery.SDDocumentCategory,
// Sales Order
cast('' as vbeln_va) as SalesDocument,
// Purchase Order
cast('' as ebeln) as PurchaseOrder,
// Warehouse Management Transfer Order
cast('' as lgnum) as WarehouseNumber,
cast('0000000000' as wm_transfer_order) as WrhsMgmtTransferOrder,
// Material Document
GoodsReceipt.MaterialDocument as MaterialDocument,
GoodsReceipt.MaterialDocumentYear as MaterialDocumentYear,
// Billing Document
cast('' as vbeln) as BillingDocument,
// Inbound Delivery
cast('' as vbeln_vl) as InboundDelivery,
cast('' as /scmtms/tor_id) as FreightOrder,
// Associations
_DeliveryDocument
}
where
GoodsReceipt.SubsequentDocumentCategory = 'R'
and InboundDelivery.SubsequentDocumentCategory = '7'