SalesOrderStatus
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - SalesOrderStatus
Overview
This reporting view provides a comprehensive overview of sales documents and their related items, deliveries, and goods movements in SAP systems. It combines data from the VBAP, LIPS, and VBAK tables to offer a holistic view of sales transactions.
Data Sources
| Source Table/View | Description |
|---|---|
| VBAP | Sales Document Item Table |
| LIPS | Delivery Item Table |
| VBAK | Sales Document Header Table |
Key Use Cases
- Analyzing sales performance by item, customer group, and delivery status
- Monitoring open quantities and delivery progress
- Tracking serial numbers and batch numbers
- Investigating discrepancies between sales orders and deliveries
- Providing insights for inventory management and logistics optimization
Query Logic
The query joins the VBAP, LIPS, and VBAK tables on the MANDT, VBELN, and POSNR fields to establish relationships between sales documents, items, deliveries, and goods movements. It calculates the open quantity as the difference between the cumulative order quantity and the actual quantity delivered. The query also includes various fields from the VBAK table to provide context about the sales document, such as document type, reason for creation, customer groups, and delivery block.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| VA01 | Create Sales Order |
| VA02 | Change Sales Order |
| VL01N | Create Delivery |
| VL02N | Change Delivery |
| MB1A | Goods Movement |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| SalesDocument_VBELN | Sales Document Number | |
| Item_POSNR | Item Number | |
| MaterialNumber_MATNR | Material Number | |
| SalesDocumentType_AUART | Sales Document Type | |
| Reason_AUGRU | Reason for Sales Document | |
| CustomerGroup1_KVGR1 | Customer Group 1 | |
| CustomerGroup2_KVGR2 | Customer Group 2 | |
| CustomerGroup3_KVGR3 | Customer Group 3 | |
| CustomerGroup4_KVGR4 | Customer Group 4 | |
| CustomerGroup5_KVGR5 | Customer Group 5 | |
| DeliveryBlock_LIFSK | Delivery Block | |
| PlanningPlant_VPWRK | Planning Plant | |
| StorageLocation_LGORT | Storage Location | |
| BatchNumber_CHARG | Batch Number | |
| CumulativeOrderQuantity_KWMENG | Cumulative Order Quantity | |
| ReferenceDocument_VGBEL | Reference Document Number | |
| ReferenceItem_VGPOS | Reference Item Number | |
| ActualQuantityDelivered_LFIMG | Actual Quantity Delivered | |
| DocumentCategory_VGTYP | Document Category | |
| SerialNumberProfile_SERAIL | Serial Number Profile | |
| NumberOfSerialNumbers_ANZSN | Number of Serial Numbers | |
| HeaderRejectionStatus_ABSTK | Header Rejection Status | |
| HeaderDeliveryStatus_LFGSK | Header Delivery Status | |
| GoodsMovementStatus_WBSTA | Goods Movement Status | |
| CreationDate_ERDAT | Creation Date | |
| CreationTime_ERZET | Creation Time | |
| RequestedDeliveryDate_VDATU | Requested Delivery Date | |
| CompleteDeliveryFlag_AUTLF | Complete Delivery Flag | |
| Delivery_VBELN | Delivery Document Number | |
| DeliveryItem_POSNR | Delivery Item Number | |
| OPENQTY | Open Quantity |