P_SLSORDFLFMTPROCHIERNODE2
P_SLSORDFLFMTPROCHIERNODE2 is a CDS View in S/4HANA. 1 CDS views read from this table.
CDS Views using this table (1)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| P_SlsOrdFlfmtProcHierNode1 | view | from | CONSUMPTION |
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #CONSUMPTION
@AbapCatalog.sqlViewName: 'PSDSOFPROCHRCND2'
-- Union of Nodes for
-- Sales Order Overall Fulfillment, Initial Sales Order, Level 1 Subsequent SD Processes, Level 1 Subsequent Supply Processes, Level 2 Subsequent SD Processes
define view P_SlsOrdFlfmtProcHierNode2
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
// Node for sales order overall fulfillment
as select from P_SlsOrdWithOvrlFlfmtSts1 as SOOvrl
association [0..1] to I_SalesDocumentType as _RootNodeDocumentSalesDocType on $projection.RootNodeDocumentSalesDocType = _RootNodeDocumentSalesDocType.SalesDocumentType
association [0..1] to I_SalesDocumentType as _SalesDocumentType on $projection.SalesDocumentType = _SalesDocumentType.SalesDocumentType
association [0..1] to I_DeliveryDocumentType as _DeliveryDocumentType on $projection.DeliveryDocumentType = _DeliveryDocumentType.DeliveryDocumentType
association [0..1] to I_BillingDocumentType as _BillingDocumentType on $projection.BillingDocumentType = _BillingDocumentType.BillingDocumentType
association [0..1] to I_PurchasingDocumentType as _PurchaseOrderType on _PurchaseOrderType.PurchasingDocumentCategory = 'F'
and $projection.PurchaseOrderType = _PurchaseOrderType.PurchasingDocumentType
association [0..1] to I_MfgOrderType as _MfgOrderType on $projection.ManufacturingOrderType = _MfgOrderType.ManufacturingOrderType
association [0..1] to I_SalesDocFulfillmentStatus as _SalesDocFulfillmentStatus on $projection.SalesDocFulfillmentStatus = _SalesDocFulfillmentStatus.SalesDocFulfillmentStatus
association [0..1] to I_OutbDelivFulfillmentStatus as _OutbDelivFulfillmentStatus on $projection.OutbDelivFulfillmentStatus = _OutbDelivFulfillmentStatus.OutbDelivFulfillmentStatus
association [0..1] to I_AccountingTransferStatus as _AccountingTransferStatus on $projection.AccountingTransferStatus = _AccountingTransferStatus.AccountingTransferStatus
association [0..1] to I_PurchasingDocumentStatus as _PurchasingDocumentStatus on $projection.PurchasingDocumentStatus = _PurchasingDocumentStatus.PurchasingDocumentStatus
association [0..1] to I_SlsOrdReltdPurProcFlfmtSts as _SlsOrdReltdPurProcFlfmtSts on $projection.SlsOrdReltdPurProcFlfmtSts = _SlsOrdReltdPurProcFlfmtSts.SlsOrdReltdPurProcFlfmtSts
association [0..1] to I_SlsOrdReltdMfgProcFlfmtSts as _SlsOrdReltdMfgProcFlfmtSts on $projection.SlsOrdReltdMfgProcFlfmtSts = _SlsOrdReltdMfgProcFlfmtSts.SlsOrdReltdMfgProcFlfmtSts
{
//key
key SalesDocument,
--key cast( concat_with_space( 'R', SalesDocument, 1 ) as dme_node ) as HierarchyNode,
key cast( concat( '_', SalesDocument ) as dme_node ) as HierarchyNode,
key cast( 0 as int2 ) as HierarchyNodeLevel, -- fulfillment process on Tree Hierarchy Node Level 0
key cast( '' as parnode ) as ParentNode,
key cast( 'ROOT' as sls_ord_flfllmnt_node_type ) as NodeType,
key cast( SOOvrl.SalesDocumentCategory as vbtypl ) as DocumentCategory,
//Organization
SalesOrganization,
DistributionChannel,
OrganizationDivision,
//Category
@ObjectModel.foreignKey.association: '_RootNodeDocumentSalesDocType'
cast( SalesDocumentType as auart ) as RootNodeDocumentSalesDocType,
SalesDocumentType,
//Sales
TotalNetAmount,
TransactionCurrency,
//Subsequent Category
@ObjectModel.foreignKey.association: '_DeliveryDocumentType'
cast( '' as lfart ) as DeliveryDocumentType,
@ObjectModel.foreignKey.association: '_BillingDocumentType'
cast( '' as fkart ) as BillingDocumentType,
@ObjectModel.foreignKey.association: '_PurchaseOrderType'
cast( '' as esart ) as PurchaseOrderType,
@ObjectModel.foreignKey.association: '_MfgOrderType'
cast( '' as manufacturingordertype ) as ManufacturingOrderType,
//Status
SOOvrl.OverallSDProcessStatus,
//Sales Order Overall Delivery Fulfillment and Overall Invoicing Fulfillment Status
SalesOrdOvrlDelivFlfmtSts,
SalesOrdOvrlInvcgFlfmtSts,
//Sales Document Fulfillment Status
@ObjectModel.foreignKey.association: '_SalesDocFulfillmentStatus'
cast( '' as salesdocfulfillmentstatus ) as SalesDocFulfillmentStatus,
//Outbound Delivery Fulfillment Status
@ObjectModel.foreignKey.association: '_OutbDelivFulfillmentStatus'
cast( '' as outbdelivfulfillmentstatus ) as OutbDelivFulfillmentStatus,
//Accounting Transfer Status
@ObjectModel.foreignKey.association: '_AccountingTransferStatus'
cast( '' as rfbsk ) as AccountingTransferStatus,
//Purchasing Document Status
@ObjectModel.foreignKey.association: '_PurchasingDocumentStatus'
cast( '' as mmpur_doc_status ) as PurchasingDocumentStatus,
//Sales Order Related Purchase Process Fulfillment Status
@ObjectModel.foreignKey.association: '_SlsOrdReltdPurProcFlfmtSts'
cast( '' as slsordreltdpurprocflfmtsts ) as SlsOrdReltdPurProcFlfmtSts,
//Sales Order Related Manfacturing Process Fulfillment Status
@ObjectModel.foreignKey.association: '_SlsOrdReltdMfgProcFlfmtSts'
cast( '' as slsordreltdmfgprocflfmtsts ) as SlsOrdReltdMfgProcFlfmtSts,
//Associations
_SalesOrganization,
_DistributionChannel,
_OrganizationDivision,
_RootNodeDocumentSalesDocType,
_SalesDocumentType,
_TransactionCurrency,
_DeliveryDocumentType,
_BillingDocumentType,
_PurchaseOrderType,
_MfgOrderType,
_OverallSDProcessStatus,
_SalesOrdOvrlDelivFlfmtSts,
_SalesOrdOvrlInvcgFlfmtSts,
_SalesDocFulfillmentStatus,
_OutbDelivFulfillmentStatus,
_AccountingTransferStatus,
_PurchasingDocumentStatus,
_SlsOrdReltdPurProcFlfmtSts,
_SlsOrdReltdMfgProcFlfmtSts
}
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
// Node for initial Sales Order as level 0 process
union all
select from I_SalesDocumentBasic as Lvl0
association [0..1] to I_SalesDocumentType as _RootNodeDocumentSalesDocType on $projection.RootNodeDocumentSalesDocType = _RootNodeDocumentSalesDocType.SalesDocumentType
association [0..1] to I_DeliveryDocumentType as _DeliveryDocumentType on $projection.DeliveryDocumentType = _DeliveryDocumentType.DeliveryDocumentType
association [0..1] to I_BillingDocumentType as _BillingDocumentType on $projection.BillingDocumentType = _BillingDocumentType.BillingDocumentType
association [0..1] to I_PurchasingDocumentType as _PurchaseOrderType on _PurchaseOrderType.PurchasingDocumentCategory = 'F'
and $projection.PurchaseOrderType = _PurchaseOrderType.PurchasingDocumentType
association [0..1] to I_MfgOrderType as _MfgOrderType on $projection.ManufacturingOrderType = _MfgOrderType.ManufacturingOrderType
association [0..1] to I_SalesOrdOvrlDelivFlfmtSts as _SalesOrdOvrlDelivFlfmtSts on $projection.SalesOrdOvrlDelivFlfmtSts = _SalesOrdOvrlDelivFlfmtSts.SalesOrdOvrlDelivFlfmtSts
association [0..1] to I_SalesOrdOvrlInvcgFlfmtSts as _SalesOrdOvrlInvcgFlfmtSts on $projection.SalesOrdOvrlInvcgFlfmtSts = _SalesOrdOvrlInvcgFlfmtSts.SalesOrdOvrlInvcgFlfmtSts
association [0..1] to I_SalesDocFulfillmentStatus as _SalesDocFulfillmentStatus on $projection.SalesDocFulfillmentStatus = _SalesDocFulfillmentStatus.SalesDocFulfillmentStatus
association [0..1] to I_OutbDelivFulfillmentStatus as _OutbDelivFulfillmentStatus on $projection.OutbDelivFulfillmentStatus = _OutbDelivFulfillmentStatus.OutbDelivFulfillmentStatus
association [0..1] to I_AccountingTransferStatus as _AccountingTransferStatus on $projection.AccountingTransferStatus = _AccountingTransferStatus.AccountingTransferStatus
association [0..1] to I_PurchasingDocumentStatus as _PurchasingDocumentStatus on $projection.PurchasingDocumentStatus = _PurchasingDocumentStatus.PurchasingDocumentStatus
association [0..1] to I_SlsOrdReltdPurProcFlfmtSts as _SlsOrdReltdPurProcFlfmtSts on $projection.SlsOrdReltdPurProcFlfmtSts = _SlsOrdReltdPurProcFlfmtSts.SlsOrdReltdPurProcFlfmtSts
association [0..1] to I_SlsOrdReltdMfgProcFlfmtSts as _SlsOrdReltdMfgProcFlfmtSts on $projection.SlsOrdReltdMfgProcFlfmtSts = _SlsOrdReltdMfgProcFlfmtSts.SlsOrdReltdMfgProcFlfmtSts
{
//key
key SalesDocument,
key cast( SalesDocument as dme_node ) as HierarchyNode,
key cast( 1 as int2 ) as HierarchyNodeLevel, -- initial sales order on Hierarchy Node Level 1
key cast( concat( '_', SalesDocument ) as parnode ) as ParentNode,
key cast( 'SDOC' as sls_ord_flfllmnt_node_type ) as NodeType,
key SDDocumentCategory as DocumentCategory,
//Organization
SalesOrganization,
DistributionChannel,
OrganizationDivision,
//Category
@ObjectModel.foreignKey.association: '_RootNodeDocumentSalesDocType'
cast( SalesDocumentType as auart ) as RootNodeDocumentSalesDocType,
SalesDocumentType,
//Sales
TotalNetAmount,
TransactionCurrency,
//Subsequent Category
cast( '' as lfart ) as DeliveryDocumentType,
cast( '' as fkart ) as BillingDocumentType,
cast( '' as esart ) as PurchaseOrderType,
cast( '' as manufacturingordertype) as ManufacturingOrderType,
//Status
Lvl0.OverallSDProcessStatus,
//Sales Order Overall Delivery Fulfillment and Overall Invoicing Fulfillment Status
cast( '' as salesordovrldelivflfmtsts ) as SalesOrdOvrlDelivFlfmtSts,
cast( '' as salesordovrlinvcgflfmtsts ) as SalesOrdOvrlInvcgFlfmtSts,
//Sales Document Fulfillment Status
@ObjectModel.foreignKey.association: '_SalesDocFulfillmentStatus'
cast(
case when Lvl0.OverallSDDocumentRejectionSts = 'C'
then '0'
else
case when Lvl0.OverallTotalDeliveryStatus != ''
then case when Lvl0.OverallSDDocumentRejectionSts = 'B'
then OverallTotalDeliveryStatus
else OverallDeliveryStatus
end
else
case when Lvl0.OverallOrdReltdBillgStatus = 'A' then 'I'
when Lvl0.OverallOrdReltdBillgStatus = 'B' then 'J'
when Lvl0.OverallOrdReltdBillgStatus = 'C' then 'K'
else ''
end
end
end as salesdocfulfillmentstatus ) as SalesDocFulfillmentStatus,
//Outbound Delivery Fulfillment Status
cast( '' as outbdelivfulfillmentstatus ) as OutbDelivFulfillmentStatus,
//Accounting Transfer Status
cast( '' as rfbsk ) as AccountingTransferStatus,
//Purchasing Document Status
@ObjectModel.foreignKey.association: '_PurchasingDocumentStatus'
cast( '' as mmpur_doc_status) as PurchasingDocumentStatus,
//Sales Order Related Purchase Process Fulfillment Status
cast( '' as slsordreltdpurprocflfmtsts ) as SlsOrdReltdPurProcFlfmtSts,
//Sales Order Related Manfacturing Process Fulfillment Status
cast( '' as slsordreltdmfgprocflfmtsts ) as SlsOrdReltdMfgProcFlfmtSts,
//Associations
_SalesOrganization,
_DistributionChannel,
_OrganizationDivision,
_RootNodeDocumentSalesDocType,
_SalesDocumentType,
_TransactionCurrency,
_DeliveryDocumentType,
_BillingDocumentType,
_PurchaseOrderType,
_MfgOrderType,
_OverallSDProcessStatus,
_SalesOrdOvrlDelivFlfmtSts,
_SalesOrdOvrlInvcgFlfmtSts,
_SalesDocFulfillmentStatus,
_OutbDelivFulfillmentStatus,
_AccountingTransferStatus,
_PurchasingDocumentStatus,
_SlsOrdReltdPurProcFlfmtSts,
_SlsOrdReltdMfgProcFlfmtSts
}
------- as parent doc only SD doc categories:
where (Lvl0.SDDocumentCategory = 'C' -- C - Order
or Lvl0.SDDocumentCategory = 'I' -- I - Order Without Charge
or Lvl0.SDDocumentCategory = 'L' -- L - Debit Memo Request
)
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
// Nodes for Sales and Distribution Processes Level 1
union all
select from P_SlsOrdFlfmt1stLvlSDProcSqnc1 as Lvl1
association [0..1] to I_Currency as _TransactionCurrency on $projection.TransactionCurrency = _TransactionCurrency.Currency
association [0..1] to I_SalesDocumentType as _RootNodeDocumentSalesDocType on $projection.RootNodeDocumentSalesDocType = _RootNodeDocumentSalesDocType.SalesDocumentType
association [0..1] to I_SalesDocumentType as _SalesDocumentType on $projection.SalesDocumentType = _SalesDocumentType.SalesDocumentType
association [0..1] to I_DeliveryDocumentType as _DeliveryDocumentType on $projection.DeliveryDocumentType = _DeliveryDocumentType.DeliveryDocumentType
association [0..1] to I_BillingDocumentType as _BillingDocumentType on $projection.BillingDocumentType = _BillingDocumentType.BillingDocumentType
association [0..1] to I_PurchasingDocumentType as _PurchaseOrderType on _PurchaseOrderType.PurchasingDocumentCategory = 'F'
and $projection.PurchaseOrderType = _PurchaseOrderType.PurchasingDocumentType
association [0..1] to I_MfgOrderType as _MfgOrderType on $projection.ManufacturingOrderType = _MfgOrderType.ManufacturingOrderType
association [0..1] to I_OverallSDProcessStatus as _OverallSDProcessStatus on $projection.OverallSDProcessStatus = _OverallSDProcessStatus.OverallSDProcessStatus
association [0..1] to I_SalesOrdOvrlDelivFlfmtSts as _SalesOrdOvrlDelivFlfmtSts on $projection.SalesOrdOvrlDelivFlfmtSts = _SalesOrdOvrlDelivFlfmtSts.SalesOrdOvrlDelivFlfmtSts
association [0..1] to I_SalesOrdOvrlInvcgFlfmtSts as _SalesOrdOvrlInvcgFlfmtSts on $projection.SalesOrdOvrlInvcgFlfmtSts = _SalesOrdOvrlInvcgFlfmtSts.SalesOrdOvrlInvcgFlfmtSts
association [0..1] to I_SalesDocFulfillmentStatus as _SalesDocFulfillmentStatus on $projection.SalesDocFulfillmentStatus = _SalesDocFulfillmentStatus.SalesDocFulfillmentStatus
association [0..1] to I_OutbDelivFulfillmentStatus as _OutbDelivFulfillmentStatus on $projection.OutbDelivFulfillmentStatus = _OutbDelivFulfillmentStatus.OutbDelivFulfillmentStatus
association [0..1] to I_AccountingTransferStatus as _AccountingTransferStatus on $projection.AccountingTransferStatus = _AccountingTransferStatus.AccountingTransferStatus
association [0..1] to I_PurchasingDocumentStatus as _PurchasingDocumentStatus on $projection.PurchasingDocumentStatus = _PurchasingDocumentStatus.PurchasingDocumentStatus
association [0..1] to I_SlsOrdReltdPurProcFlfmtSts as _SlsOrdReltdPurProcFlfmtSts on $projection.SlsOrdReltdPurProcFlfmtSts = _SlsOrdReltdPurProcFlfmtSts.SlsOrdReltdPurProcFlfmtSts
association [0..1] to I_SlsOrdReltdMfgProcFlfmtSts as _SlsOrdReltdMfgProcFlfmtSts on $projection.SlsOrdReltdMfgProcFlfmtSts = _SlsOrdReltdMfgProcFlfmtSts.SlsOrdReltdMfgProcFlfmtSts
{
//key
key SalesDocument,
key L1SubsequentDoc as HierarchyNode,
key cast( 2 as int2 ) as HierarchyNodeLevel, --Lvl 1 Subsequent maps to ProcessFlowLevel '00' maps to Tree HierarchyNodeLevel 2
key SalesDocument as ParentNode,
key cast(
case
when L1SubsequentDocCat = 'K' then 'SDOC' // Credit memo request
when L1SubsequentDocCat = 'L' then 'SDOC' // Debit memo request
when L1SubsequentDocCat = 'J' then 'DDOC' // Delivery
when L1SubsequentDocCat = 'M' then 'BDOC' // Invoice
when L1SubsequentDocCat = 'O' then 'BDOC' // Credit memo
when L1SubsequentDocCat = 'P' then 'BDOC' // Debit memo
when L1SubsequentDocCat = 'U' then 'BDOC' // Pro forma invoice
else ''
end as sls_ord_flfllmnt_node_type ) as NodeType,
key L1SubsequentDocCat as DocumentCategory,
//Organization
SalesOrganization,
DistributionChannel,
OrganizationDivision,
//Category
@ObjectModel.foreignKey.association: '_RootNodeDocumentSalesDocType'
cast( Lvl1.SalesDocumentType as auart ) as RootNodeDocumentSalesDocType,
@ObjectModel.foreignKey.association: '_SalesDocumentType'
cast(
case when L1SubsequentDocCat = 'K'
or L1SubsequentDocCat = 'L'
then Lvl1.L1SalesDocumentType
else ''
end as auart ) as SalesDocumentType,
//Sales
@DefaultAggregation: #NONE
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast( 0 as netwr_ak ) as TotalNetAmount,
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_TransactionCurrency'
cast( '' as waerk ) as TransactionCurrency,
//Subsequent Category
@ObjectModel.foreignKey.association: '_DeliveryDocumentType'
cast( Lvl1.L1DeliveryDocumentType as lfart ) as DeliveryDocumentType,
@ObjectModel.foreignKey.association: '_BillingDocumentType'
cast( Lvl1.L1BillingDocumentType as fkart ) as BillingDocumentType,
cast( '' as esart ) as PurchaseOrderType,
cast( '' as manufacturingordertype ) as ManufacturingOrderType,
//Status
@ObjectModel.foreignKey.association: '_OverallSDProcessStatus'
Lvl1.L1OverallSDProcessStatus as OverallSDProcessStatus,
//Sales Order Overall Delivery Fulfillment and Overall Invoicing Fulfillment Status
cast( '' as salesordovrldelivflfmtsts ) as SalesOrdOvrlDelivFlfmtSts,
cast( '' as salesordovrlinvcgflfmtsts ) as SalesOrdOvrlInvcgFlfmtSts,
//Sales Document Fulfillment Status
--- to be calculated for L1 Debit Memo Request
@ObjectModel.foreignKey.association: '_SalesDocFulfillmentStatus'
cast(
case when Lvl1.L1OvrlSDDocumentRejectionSts = 'C'
then '0'
else
case when Lvl1.L1OverallTotalDeliveryStatus != ''
then OverallTotalDeliveryStatus
else
case when Lvl1.L1OverallOrdReltdBillgStatus = 'A' then 'I'
when Lvl1.L1OverallOrdReltdBillgStatus = 'B' then 'J'
when Lvl1.L1OverallOrdReltdBillgStatus = 'C' then 'K'
else ''
end
end
end as salesdocfulfillmentstatus ) as SalesDocFulfillmentStatus,
//Outbound Delivery Fulfillment Status
@ObjectModel.foreignKey.association: '_OutbDelivFulfillmentStatus'
cast( Lvl1.L1OverallGoodsMovementStatus as outbdelivfulfillmentstatus ) as OutbDelivFulfillmentStatus,
//Accounting Transfer Status
@ObjectModel.foreignKey.association: '_AccountingTransferStatus'
Lvl1.L1AccountingTransferStatus as AccountingTransferStatus,
//Purchasing Document Status
@ObjectModel.foreignKey.association: '_PurchasingDocumentStatus'
cast( '' as mmpur_doc_status ) as PurchasingDocumentStatus,
//Sales Order Related Purchase Process Fulfillment Status
cast( '' as slsordreltdpurprocflfmtsts ) as SlsOrdReltdPurProcFlfmtSts,
//Sales Order Related Manfacturing Process Fulfillment Status
cast( '' as slsordreltdmfgprocflfmtsts ) as SlsOrdReltdMfgProcFlfmtSts,
//Associations
_SalesOrganization,
_DistributionChannel,
_OrganizationDivision,
_RootNodeDocumentSalesDocType,
_SalesDocumentType,
_TransactionCurrency,
_DeliveryDocumentType,
_BillingDocumentType,
_PurchaseOrderType,
_MfgOrderType,
_OverallSDProcessStatus,
_SalesOrdOvrlDelivFlfmtSts,
_SalesOrdOvrlInvcgFlfmtSts,
_SalesDocFulfillmentStatus,
_OutbDelivFulfillmentStatus,
_AccountingTransferStatus,
_PurchasingDocumentStatus,
_SlsOrdReltdPurProcFlfmtSts,
_SlsOrdReltdMfgProcFlfmtSts
}
where ( L1SubsequentDocCat = 'L' // Debit memo request
or L1SubsequentDocCat = 'J' // Delivery
or
(L1SubsequentDocCat = 'M' // Invoice
or L1SubsequentDocCat = 'O' // Credit memo
or L1SubsequentDocCat = 'P' // Debit memo
or L1SubsequentDocCat = 'U' // Pro forma invoice
) and Lvl1.L1BillgDocIsCancelled != 'X' )
and Lvl1.L1DeliveryDocumentType != '?!A'
and Lvl1.L1BillingDocumentType != '?!A'
and Lvl1.L1SalesDocumentType != '?!A'
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
// Nodes for Supply Processes
union all
select from P_SlsOrdFlfmtSupProcSqnc as SupLvl1
association [0..1] to I_Currency as _TransactionCurrency on $projection.TransactionCurrency = _TransactionCurrency.Currency
association [0..1] to I_SalesDocumentType as _RootNodeDocumentSalesDocType on $projection.RootNodeDocumentSalesDocType = _RootNodeDocumentSalesDocType.SalesDocumentType
association [0..1] to I_SalesDocumentType as _SalesDocumentType on $projection.SalesDocumentType = _SalesDocumentType.SalesDocumentType
association [0..1] to I_DeliveryDocumentType as _DeliveryDocumentType on $projection.DeliveryDocumentType = _DeliveryDocumentType.DeliveryDocumentType
association [0..1] to I_BillingDocumentType as _BillingDocumentType on $projection.BillingDocumentType = _BillingDocumentType.BillingDocumentType
association [0..1] to I_OverallSDProcessStatus as _OverallSDProcessStatus on $projection.OverallSDProcessStatus = _OverallSDProcessStatus.OverallSDProcessStatus
association [0..1] to I_SalesOrdOvrlDelivFlfmtSts as _SalesOrdOvrlDelivFlfmtSts on $projection.SalesOrdOvrlDelivFlfmtSts = _SalesOrdOvrlDelivFlfmtSts.SalesOrdOvrlDelivFlfmtSts
association [0..1] to I_SalesOrdOvrlInvcgFlfmtSts as _SalesOrdOvrlInvcgFlfmtSts on $projection.SalesOrdOvrlInvcgFlfmtSts = _SalesOrdOvrlInvcgFlfmtSts.SalesOrdOvrlInvcgFlfmtSts
association [0..1] to I_SalesDocFulfillmentStatus as _SalesDocFulfillmentStatus on $projection.SalesDocFulfillmentStatus = _SalesDocFulfillmentStatus.SalesDocFulfillmentStatus
association [0..1] to I_OutbDelivFulfillmentStatus as _OutbDelivFulfillmentStatus on $projection.OutbDelivFulfillmentStatus = _OutbDelivFulfillmentStatus.OutbDelivFulfillmentStatus
association [0..1] to I_AccountingTransferStatus as _AccountingTransferStatus on $projection.AccountingTransferStatus = _AccountingTransferStatus.AccountingTransferStatus
{
//key
key SalesDocument,
key L1SubsequentDoc as HierarchyNode,
key cast( 2 as int2 ) as HierarchyNodeLevel, --Lvl 1 Subsequent maps to ProcessFlowLevel '00' maps to Tree HierarchyNodeLevel 2
key SalesDocument as ParentNode,
key cast(
case
when L1SubsequentDocCat = 'V' then 'PDOC' // Purchase Order
when L1SubsequentDocCat = '10' then 'MDOC' // Manufacturing Order
when L1SubsequentDocCat = '40' then 'MDOC' // Manufacturing Order
else ''
end
as sls_ord_flfllmnt_node_type ) as NodeType,
key L1SubsequentDocCat as DocumentCategory,
//Organization
SalesOrganization,
DistributionChannel,
OrganizationDivision,
//Category
@ObjectModel.foreignKey.association: '_RootNodeDocumentSalesDocType'
cast( SupLvl1.SalesDocumentType as auart ) as RootNodeDocumentSalesDocType,
cast( '' as auart ) as SalesDocumentType,
//Sales
@DefaultAggregation: #NONE
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( 0 as netwr_ak ) as TotalNetAmount,
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_TransactionCurrency'
cast ( '' as waerk ) as TransactionCurrency,
//Subsequent Category
cast( '' as lfart ) as DeliveryDocumentType,
cast( '' as fkart ) as BillingDocumentType,
PurchaseOrderType,
ManufacturingOrderType,
//Supply Level 1 'Pseudo Overall SD Process Status'
@ObjectModel.foreignKey.association: '_OverallSDProcessStatus'
cast(
case when L1SubsequentDocCat = 'V' // Purchase Order
then case when L1OverallSDProcessStatus = 'C'
then L1OverallSDProcessStatus
else case when (SlsOrdReltdPurProcFlfmtSts = 'B'
or SlsOrdReltdPurProcFlfmtSts = 'J')
then 'B'
else 'A'
end
end
when L1SubsequentDocCat = '10' then SlsOrdReltdMfgProcFlfmtSts // Manufacturing Order
when L1SubsequentDocCat = '40' then SlsOrdReltdMfgProcFlfmtSts // Manufacturing Order
else ''
end as gbstk ) as OverallSDProcessStatus,
//Sales Order Overall Delivery Fulfillment and Overall Invoicing Fulfillment Status
cast( '' as salesordovrldelivflfmtsts ) as SalesOrdOvrlDelivFlfmtSts,
cast( '' as salesordovrlinvcgflfmtsts ) as SalesOrdOvrlInvcgFlfmtSts,
//Sales Document Fulfillment Status
cast( '' as salesdocfulfillmentstatus ) as SalesDocFulfillmentStatus,
//Outbound Delivery Fulfillment Status
cast( '' as outbdelivfulfillmentstatus ) as OutbDelivFulfillmentStatus,
//Accounting Transfer Status
cast( '' as rfbsk ) as AccountingTransferStatus,
//Purchasing Document Status
PurchasingDocumentStatus,
//Sales Order Related Purchase Process Fulfillment Status
SlsOrdReltdPurProcFlfmtSts,
//Sales Order Related Manfacturing Process Fulfillment Status
SlsOrdReltdMfgProcFlfmtSts,
//Associations
_SalesOrganization,
_DistributionChannel,
_OrganizationDivision,
_RootNodeDocumentSalesDocType,
_SalesDocumentType,
_TransactionCurrency,
_DeliveryDocumentType,
_BillingDocumentType,
_PurchaseOrderType,
_MfgOrderType,
_OverallSDProcessStatus,
_SalesOrdOvrlDelivFlfmtSts,
_SalesOrdOvrlInvcgFlfmtSts,
_SalesDocFulfillmentStatus,
_OutbDelivFulfillmentStatus,
_AccountingTransferStatus,
_PurchasingDocumentStatus,
_SlsOrdReltdPurProcFlfmtSts,
_SlsOrdReltdMfgProcFlfmtSts
}
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
// Nodes for Sales and Distribution Processes Level 2
union all
select from P_SlsOrdFlfmt2ndLvlSDProcSqnc1 as Lvl2
association [0..1] to I_Currency as _TransactionCurrency on $projection.TransactionCurrency = _TransactionCurrency.Currency
association [0..1] to I_SalesDocumentType as _RootNodeDocumentSalesDocType on $projection.RootNodeDocumentSalesDocType = _RootNodeDocumentSalesDocType.SalesDocumentType
association [0..1] to I_SalesDocumentType as _SalesDocumentType on $projection.SalesDocumentType = _SalesDocumentType.SalesDocumentType
association [0..1] to I_DeliveryDocumentType as _DeliveryDocumentType on $projection.DeliveryDocumentType = _DeliveryDocumentType.DeliveryDocumentType
association [0..1] to I_BillingDocumentType as _BillingDocumentType on $projection.BillingDocumentType = _BillingDocumentType.BillingDocumentType
association [0..1] to I_PurchasingDocumentType as _PurchaseOrderType on _PurchaseOrderType.PurchasingDocumentCategory = 'F'
and $projection.PurchaseOrderType = _PurchaseOrderType.PurchasingDocumentType
association [0..1] to I_MfgOrderType as _MfgOrderType on $projection.ManufacturingOrderType = _MfgOrderType.ManufacturingOrderType
association [0..1] to I_OverallSDProcessStatus as _OverallSDProcessStatus on $projection.OverallSDProcessStatus = _OverallSDProcessStatus.OverallSDProcessStatus
association [0..1] to I_SalesOrdOvrlDelivFlfmtSts as _SalesOrdOvrlDelivFlfmtSts on $projection.SalesOrdOvrlDelivFlfmtSts = _SalesOrdOvrlDelivFlfmtSts.SalesOrdOvrlDelivFlfmtSts
association [0..1] to I_SalesOrdOvrlInvcgFlfmtSts as _SalesOrdOvrlInvcgFlfmtSts on $projection.SalesOrdOvrlInvcgFlfmtSts = _SalesOrdOvrlInvcgFlfmtSts.SalesOrdOvrlInvcgFlfmtSts
association [0..1] to I_SalesDocFulfillmentStatus as _SalesDocFulfillmentStatus on $projection.SalesDocFulfillmentStatus = _SalesDocFulfillmentStatus.SalesDocFulfillmentStatus
association [0..1] to I_OutbDelivFulfillmentStatus as _OutbDelivFulfillmentStatus on $projection.OutbDelivFulfillmentStatus = _OutbDelivFulfillmentStatus.OutbDelivFulfillmentStatus
association [0..1] to I_AccountingTransferStatus as _AccountingTransferStatus on $projection.AccountingTransferStatus = _AccountingTransferStatus.AccountingTransferStatus
association [0..1] to I_PurchasingDocumentStatus as _PurchasingDocumentStatus on $projection.PurchasingDocumentStatus = _PurchasingDocumentStatus.PurchasingDocumentStatus
association [0..1] to I_SlsOrdReltdPurProcFlfmtSts as _SlsOrdReltdPurProcFlfmtSts on $projection.SlsOrdReltdPurProcFlfmtSts = _SlsOrdReltdPurProcFlfmtSts.SlsOrdReltdPurProcFlfmtSts
association [0..1] to I_SlsOrdReltdMfgProcFlfmtSts as _SlsOrdReltdMfgProcFlfmtSts on $projection.SlsOrdReltdMfgProcFlfmtSts = _SlsOrdReltdMfgProcFlfmtSts.SlsOrdReltdMfgProcFlfmtSts
{
//key
key SalesDocument,
key L2SubsequentDoc as HierarchyNode,
key cast( 3 as int2 ) as HierarchyNodeLevel, --L2Subsequent maps to ProcessFlowLevel '01' maps to Tree HierarchyNodeLevel 3
key L1SubsequentDoc as ParentNode,
key cast(
case
when L2SubsequentDocCat = 'M' then 'BDOC' // Invoice
when L2SubsequentDocCat = 'O' then 'BDOC' // Credit memo
when L2SubsequentDocCat = 'P' then 'BDOC' // Debit memo
when L2SubsequentDocCat = 'U' then 'BDOC' // Pro forma invoice
else ''
end as sls_ord_flfllmnt_node_type ) as NodeType,
key L2SubsequentDocCat as DocumentCategory,
//Organization
SalesOrganization,
DistributionChannel,
OrganizationDivision,
//Category
@ObjectModel.foreignKey.association: '_RootNodeDocumentSalesDocType'
cast( Lvl2.SalesDocumentType as auart ) as RootNodeDocumentSalesDocType,
cast( '' as auart ) as SalesDocumentType,
//Sales
@DefaultAggregation: #NONE
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast( 0 as netwr_ak ) as TotalNetAmount,
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_TransactionCurrency'
cast( '' as waerk ) as TransactionCurrency,
//Subsequent Category
cast( '' as lfart ) as DeliveryDocumentType,
@ObjectModel.foreignKey.association: '_BillingDocumentType'
cast(Lvl2.L2BillingDocumentType as fkart ) as BillingDocumentType,
cast( '' as esart ) as PurchaseOrderType,
cast( '' as manufacturingordertype ) as ManufacturingOrderType,
//Status
@ObjectModel.foreignKey.association: '_OverallSDProcessStatus'
Lvl2.L2OverallSDProcessStatus as OverallSDProcessStatus,
//Sales Order Overall Delivery Fulfillment and Overall Invoicing Fulfillment Status
cast( '' as salesordovrldelivflfmtsts ) as SalesOrdOvrlDelivFlfmtSts,
cast( '' as salesordovrlinvcgflfmtsts ) as SalesOrdOvrlInvcgFlfmtSts,
//Sales Document Fulfillment Status
cast( '' as salesdocfulfillmentstatus ) as SalesDocFulfillmentStatus,
//Outbound Delivery Fulfillment Status
cast( '' as outbdelivfulfillmentstatus ) as OutbDelivFulfillmentStatus,
//Accounting Transfer Status
@ObjectModel.foreignKey.association: '_AccountingTransferStatus'
Lvl2.L2AccountingTransferStatus as AccountingTransferStatus,
//Purchasing Document Status
@ObjectModel.foreignKey.association: '_PurchasingDocumentStatus'
cast( '' as mmpur_doc_status) as PurchasingDocumentStatus,
//Sales Order Related Purchase Process Fulfillment Status
cast( '' as slsordreltdpurprocflfmtsts ) as SlsOrdReltdPurProcFlfmtSts,
//Sales Order Related Manfacturing Process Fulfillment Status
cast( '' as slsordreltdmfgprocflfmtsts ) as SlsOrdReltdMfgProcFlfmtSts,
//Associations
_SalesOrganization,
_DistributionChannel,
_OrganizationDivision,
_RootNodeDocumentSalesDocType,
_SalesDocumentType,
_TransactionCurrency,
_DeliveryDocumentType,
_BillingDocumentType,
_PurchaseOrderType,
_MfgOrderType,
_OverallSDProcessStatus,
_SalesOrdOvrlDelivFlfmtSts,
_SalesOrdOvrlInvcgFlfmtSts,
_SalesDocFulfillmentStatus,
_OutbDelivFulfillmentStatus,
_AccountingTransferStatus,
_PurchasingDocumentStatus,
_SlsOrdReltdPurProcFlfmtSts,
_SlsOrdReltdMfgProcFlfmtSts
}
where (L2SubsequentDocCat = 'M' // Invoice
or L2SubsequentDocCat = 'O' // Credit memo
or L2SubsequentDocCat = 'P' // Debit memo
or L2SubsequentDocCat = 'U' // Pro forma invoice
) and Lvl2.L2BillgDocIsCancelled != 'X'