OrderToCash
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - OrderToCash
Overview
This view provides a comprehensive reporting dataset for SAP systems, combining data from Sales Orders, Deliveries, Billing, and master data tables. It offers insights into sales performance, order fulfillment, and customer behavior.
Data Sources
| Source Table/View | Description |
|---|---|
| SalesOrders | Sales order header data, including order numbers, dates, quantities, pricing, and customer information. |
| Deliveries | Delivery-related data, such as delivery numbers, dates, quantities, and status. |
| Billing | Billing document data, including invoice numbers, dates, amounts, and tax information. |
| CustomersMD | Customer master data, including customer numbers, names, addresses, and contact information. |
| MaterialsMD | Material master data, including material numbers, descriptions, and pricing information. |
| SalesOrganizationsMD | Sales organization master data, including sales organization names and addresses. |
| DistributionChannelsMD | Distribution channel master data, including distribution channel names and descriptions. |
| CountriesMD | Country master data, including country names and codes. |
| OneTouchOrder | One-touch order data, including order numbers and item details. |
| DivisionsMD | Division master data, including division names and descriptions. |
| DeliveryBlockingReasonsMD | Delivery blocking reasons master data, including blocking reasons and descriptions. |
| BillingBlockingReasonsMD | Billing blocking reasons master data, including blocking reasons and descriptions. |
Key Use Cases
- Sales performance analysis
- Order fulfillment tracking
- Customer relationship management
- Inventory management
- Financial reporting
Query Logic
The view combines data from multiple tables using joins based on common fields, such as sales order numbers and material numbers. It calculates various metrics and aggregates data to provide a comprehensive view of sales and delivery performance.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| VA01 | Create Sales Order |
| VA02 | Change Sales Order |
| VL01N | Create Delivery |
| VL02N | Change Delivery |
| VF01 | Create Invoice |
| VF02 | Change Invoice |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| SalesOrders.Client_MANDT | Client ID | |
| Deliveries.Delivery_VBELN | Delivery Number | |
| Deliveries.DeliveryItem_POSNR | Delivery Item Number | |
| SalesOrders.SalesDocument_VBELN | Sales Document Number | |
| SalesOrders.Item_POSNR | Sales Order Item Number | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG | Actual Quantity Delivered in Sales Units | |
| Deliveries.BaseUnitOfMeasure_MEINS | Base Unit of Measure | |
| Deliveries.NetPrice_NETPR | Net Price | |
| Deliveries.NetValueInDocumentCurrency_NETWR | Net Value in Document Currency | |
| Deliveries.SdDocumentCurrency_WAERK | Sales Document Currency | |
| Deliveries.DeliveryBlock_DocumentHeader_LIFSK | Delivery Block in Document Header | |
| Deliveries.BillingBlockInSdDocument_FAKSK | Billing Block in Sales Document | |
| Deliveries.Date__proofOfDelivery___PODAT | Date of Proof of Delivery | |
| Deliveries.BillingDateForBillingIndexAndPrintout_FKDAT | Billing Date for Billing Index and Printout | |
| Deliveries.SalesOrderNumber_VGBEL | Sales Order Number | |
| Deliveries.DeliveryDate_LFDAT | Delivery Date | |
| Deliveries.ActualGoodsMovementDate_WADAT_IST | Actual Goods Movement Date | |
| SalesOrders.ExchangeRateType_KURST | Exchange Rate Type | |
| SalesOrders.Requesteddeliverydate_VDATU | Requested Delivery Date | |
| SalesOrders.CumulativeOrderQuantity_KWMENG | Cumulative Order Quantity | |
| SalesOrders.BaseUnitofMeasure_MEINS | Sales Unit Measure | |
| SalesOrders.Netprice_NETPR | Net Price | |
| SalesOrders.Currency_WAERK | Sales Order Document Currency | |
| SalesOrders.ShippingReceivingPoint_VSTEL | Shipping Receiving Point | |
| SalesOrders.SoldToParty_KUNNR | Sold-to Party | |
| SalesOrders.SoldToPartyItem_KUNNR | Sold-to Party Item | |
| SalesOrders.SoldToPartyItemName_KUNNR | Sold-to Party Item Name | |
| SalesOrders.ShipToPartyItem_KUNNR | Ship-to Party Item | |
| SalesOrders.ShipToPartyItemName_KUNNR | Ship-to Party Item Name | |
| SalesOrders.BillToPartyItem_KUNNR | Bill-to Party Item | |
| SalesOrders.BillToPartyItemName_KUNNR | Bill-to Party Item Name | |
| SalesOrders.PayerItem_KUNNR | Payer Item | |
| SalesOrders.PayerItemName_KUNNR | Payer Item Name | |
| SalesOrders.SoldToPartyHeader_KUNNR | Sold-to Party Header | |
| SalesOrders.SoldToPartyHeaderName_KUNNR | Sold-to Party Header Name | |
| SalesOrders.ShipToPartyHeader_KUNNR | Ship-to Party Header | |
| SalesOrders.ShipToPartyHeaderName_KUNNR | Ship-to Party Header Name | |
| SalesOrders.BillToPartyHeader_KUNNR | Bill-to Party Header | |
| SalesOrders.BillToPartyHeaderName_KUNNR | Bill-to Party Header Name | |
| SalesOrders.PayerHeader_KUNNR | Payer Header | |
| SalesOrders.PayerHeaderName_KUNNR | Payer Header Name | |
| SalesOrders.SalesOrganization_VKORG | Sales Organization | |
| SalesOrders.DistributionChannel_VTWEG | Distribution Channel | |
| SalesOrders.OverallDeliveryStatus_LFGSK | Overall Delivery Status | |
| SalesOrders.ListPrice | List Price | |
| SalesOrders.AdjustedPrice | Adjusted Price | |
| SalesOrders.InterCompanyPrice | Intercompany Price | |
| SalesOrders.Discount | Discount | |
| SalesOrders.ConfirmedOrderQuantity_BMENG | Confirmed Order Quantity | |
| SalesOrders.CreationDate_ERDAT | Creation Date | |
| SalesOrders.DocumentCategory_VBTYP | Document Category | |
| SalesOrders.PrecedingDocCategory_VGTYP | Preceding Document Category | |
| SalesOrders.Documentnumberofthereferencedocument_VGBEL | Document Number of the Referenced Document | |
| SalesOrders.ReferenceItem_VGPOS | Reference Item | |
| SalesOrders.RejectionReason_ABGRU | Rejection Reason | |
| CustomersMD.CustomerNumber_KUNNR | Customer Number | |
| CustomersMD.Name1_NAME1 | Customer Name 1 | |
| CustomersMD.Name2_NAME2 | Customer Name 2 | |
| CustomersMD.City_ORT01 | City | |
| CustomersMD.CountryKey_LAND1 | Country Key | |
| CustomersMD.PostalCode_PSTLZ | Postal Code | |
| CustomersMD.CustomerRegion_REGIO | Customer Region | |
| CustomersMD.Address_ADRNR | Customer Address | |
| CustomersMD.LanguageKey_SPRAS | Customer Language | |
| MaterialsMD.MaterialNumber_MATNR | Material Number | |
| MaterialsMD.MaterialType_MTART | Material Type | |
| MaterialsMD.Division_SPART | Division | |
| MaterialsMD.MaterialCategory_ATTYP | Product Category | |
| MaterialsMD.Brand_BRAND_ID | Brand | |
| MaterialsMD.MaterialText_MAKTX | Material Description | |
| MaterialsMD.Language_SPRAS | Language | |
| Billing.ActualBilledQuantity_FKIMG | Actual Billed Quantity | |
| Billing.BillingDocument_VBELN | Billing Document Number | |
| Billing.Rebate | Rebate | |
| Billing.TaxAmount_MWSBK | Tax Amount | |
| Billing.Volume_VOLUM | Volume | |
| Billing.GrossWeight_BRGEW | Gross Weight | |
| Billing.BillingDate_FKDAT | Billing Date | |
| Billing.BillingItem_POSNR | Billing Item Number | |
| Billing.NetWeight_NTGEW | Net Weight | |
| Billing.NetValue_NETWR | Billing Net Value | |
| Billing.SdDocumentCurrency_WAERK | Billing Document Currency | |
| SalesOrganizationsMD.SalesOrgName_VTEXT | Sales Organization Name | |
| DistributionChannelMD.DistributionChannelName_VTEXT | Distribution Channel Name | |
| CountriesMD.CountryName_LANDX | Country Name | |
| OneTouchOrder.OneTouchOrderCount | One Touch Order Count | |
| OneTouchOrder.VBAPSalesDocument_VBELN | One Touch Orders | |
| DivisionsMD.DivisionName_VTEXT | Division Description | |
| SalesOrders.OverallProcessingStatus_GBSTK | Overall Processing Status | |
| TVLST.DeliveryBlockReason_VTEXT | Delivery Block Reason Description | |
| TVFST.BillingBlockReason_VTEXT | Billing Block Reason Description | |
| CASE SalesOrders.OverallProcessingStatus_GBSTK | ||
| WHEN 'A' THEN 'Not yet Processed' | ||
| WHEN 'B' THEN 'Partially Processed' | ||
| WHEN 'C' THEN 'Completely Processed' | ||
| ELSE 'Not relevant' | ||
| END | Return Order Description | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetPrice_NETPR | Delivered Value | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetValueInDocumentCurrency_NETWR | Value | |
| SalesOrders.CumulativeOrderQuantity_KWMENG * SalesOrders.Netprice_NETPR | Sales Order Net Value | |
| SUM(Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetPrice_NETPR) OVER(PARTITION BY Deliveries.DeliveryItem_POSNR, Deliveries.Delivery_VBELN) | Delivered Net Value | |
| IF(Deliveries.Date__proofOfDelivery___PODAT > Deliveries.DeliveryDate_LFDAT, | ||
| 'Delayed', | ||
| 'NotDelayed') | Late Deliveries | |
| IF(Deliveries.DeliveryBlock_documentHeader_LIFSK IS NULL | ||
| AND Deliveries.BillingBlockInSdDocument_FAKSK IS NULL, | ||
| 'NotBlocked', | ||
| 'Blocked' ) | Blocked Sales Order | |
| COUNT(DISTINCT SalesOrders.SalesDocument_VBELN) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Orders | |
| COUNT(SalesOrders.Item_POSNR) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Order Items | |
| COUNT(Deliveries.DeliveryItem_POSNR) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Deliveries | |
| SUM(SalesOrders.CumulativeOrderQuantity_KWMENG ) OVER(PARTITION BY SalesOrders.Client_MANDT, SalesOrders.SalesDocument_VBELN, SalesOrders.Item_POSNR) | SalesOrderQuantity | |
| SUM(SalesOrders.Netprice_NETPR * SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.Client_MANDT, SalesOrders.SalesDocument_VBELN, SalesOrders.Item_POSNR) | SalesOrderValue | |
| IF(SalesOrders.DocumentCategory_VBTYP = 'C', | ||
| SalesOrders.SalesDocument_VBELN, | ||
| NULL) | IncomingOrderNum | |
| IF(SalesOrders.CumulativeOrderQuantity_KWMENG = Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG, | ||
| 'DeliveredInFull', | ||
| 'NotDeliverdInFull') | InFullDelivery | |
| IF(Deliveries.Date__proofOfDelivery___PODAT <= Deliveries.DeliveryDate_LFDAT | ||
| AND SalesOrders.CumulativeOrderQuantity_KWMENG = Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG, | ||
| 'OTIF', | ||
| 'NotOTIF') | OTIF | |
| SAFE_DIVIDE(SalesOrders.ConfirmedOrderQuantity_BMENG, SalesOrders.CumulativeOrderQuantity_KWMENG ) * 100 | FillRatePercent | |
| IF(SalesOrders.CumulativeOrderQuantity_KWMENG > SalesOrders.ConfirmedOrderQuantity_BMENG, | ||
| 'BackOrder', | ||
| 'NotBackOrder') | BackOrder | |
| IF(Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG = SalesOrders.CumulativeOrderQuantity_KWMENG | ||
| AND SalesOrders.CumulativeOrderQuantity_KWMENG = Billing.ActualBilledQuantity_FKIMG, | ||
| 'NotOpenOrder', | ||
| 'OpenOrder') | OpenOrder | |
| IF( SalesOrders.DocumentCategory_VBTYP = 'H', | ||
| IF( SalesOrders.PrecedingDocCategory_VGTYP = 'C' AND SalesOrders.ReferenceDocument_VGBEL = SalesOrders.Documentnumberofthereferencedocument_VGBEL | ||
| AND SalesOrders.Item_POSNR = SalesOrders.ReferenceItem_VGPOS, | ||
| 'Returned', | ||
| 'NotReturned'), | ||
| IF( SalesOrders.PrecedingDocCategory_VGTYP = 'M' AND SalesOrders.ReferenceDocument_VGBEL = Billing.DocumentNumberOfTheReferenceDocument_VGBEL | ||
| AND SalesOrders.ReferenceItem_VGPOS = Billing.ItemNumberOfTheReferenceItem_VGPOS | ||
| AND Billing.SalesDocument_AUBEL = SalesOrders.SalesDocument_VBELN | ||
| AND Billing.SalesDocumentItem_AUPOS = SalesOrders.Item_POSNR, | ||
| 'Returned', | ||
| 'NotReturned') ) | ReturnOrder | |
| IF(SalesOrders.RejectionReason_ABGRU IS NOT NULL, | ||
| 'Canceled', | ||
| 'NotCanceled') | CanceledOrder | |
| IF(Deliveries.ActualGoodsMovementDate_WADAT_IST IS NOT NULL, | ||
| TIMESTAMP_DIFF(CAST(CONCAT(Deliveries.Date__proofOfDelivery___PODAT, ' ', Deliveries.ConfirmationTime_POTIM) AS TIMESTAMP), | ||
| CAST(CONCAT(SalesOrders.CreationDate_ERDAT, ' ', SalesOrders.CreationTime_ERZET) AS TIMESTAMP), DAY), NULL) | OrderCycleTimeInDays | |
| IF(Deliveries.Date__proofOfDelivery___PODAT <= Deliveries.DeliveryDate_LFDAT, | ||
| 'DeliveredOnTime', | ||
| 'NotDeliveredOnTime') | OnTimeDelivery |