OrderToCash
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - 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 organization names and addresses. |
| DistributionChannelsMD | Distribution channel master data, including channel names and descriptions. |
| CountriesMD | Country master data, including country names and codes. |
| OneTouchOrder | One-touch order data, including order numbers and counts. |
| 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 Order 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 | |
| ReturnOrderDescription | Return Order Description | |
| DeliveredValue | Delivered Value | |
| Value | Value | |
| SalesOrderNetValue | Sales Order Net Value | |
| DeliveredNetValue | Delivered Net Value | |
| LateDeliveries | Late Deliveries | |
| BlockedSalesOrder | Blocked Sales Order | |
| TotalOrders | Total Orders | |
| TotalOrderItems | Total Order Items | |
| TotalDeliveries | Total Deliveries | |
| SalesOrderQuantity | Sales Order Quantity | |
| SalesOrderValue | Sales Order Value | |
| IncomingOrderNum | Incoming Order Number | |
| InFullDelivery | In Full Delivery | |
| OTIF | OTIF | |
| FillRatePercent | Fill Rate Percent | |
| BackOrder | Back Order | |
| OpenOrder | Open Order | |
| ReturnOrder | Return Order | |
| CanceledOrder | Canceled Order | |
| OrderCycleTimeInDays | Order Cycle Time In Days | |
| OnTimeDelivery | On Time Delivery |