POOrderHistory
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - POOrderHistory
Overview
This reporting view provides a comprehensive dataset of SAP purchasing documents, combining data from the PurchaseDocuments table and the ekbe table. It offers insights into various aspects of purchasing activities, including document details, item information, accounting data, and calendar dimensions.
Data Sources
| Source Table/View | Description |
|---|---|
| PurchaseDocuments | A table containing master data for purchasing documents, including vendor account numbers, document numbers, and item numbers. |
| ekbe | A table from the SAP ERP system that stores detailed information about purchasing documents, such as transaction event types, material document data, quantity and value information, and account assignment details. |
Key Use Cases
- Analyzing purchasing patterns and trends
- Monitoring the status of purchase orders and invoices
- Reconciling purchase order data with financial data
- Identifying potential cost savings and optimization opportunities
- Providing a basis for reporting and analysis on purchasing activities
Query Logic
The query combines data from the PurchaseDocuments table and the ekbe table using inner join conditions on Client_MANDT, DocumentNumber_EBELN, and Item_EBELP. It also includes left joins to the currency_decimal table for currency conversion and to the calendar_date_dim table for adding calendar dimensions to the data. The query filters the results to include only goods receipt and invoice receipt transactions (VGABE IN('1', '2')).
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| ME21N | Create Purchase Order |
| ME22N | Change Purchase Order |
| ME23N | Display Purchase Order |
| MIRO | Enter Invoice |
| MRKO | Goods Receipt for Purchase Order |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| PO.VendorAccountNumber_LIFNR | Vendor account number | |
| ekbe.MANDT | Client | |
| ekbe.EBELN | Purchasing document number | |
| ekbe.EBELP | Item number of purchasing document | |
| ekbe.ZEKKN | Sequential number of account assignment | |
| ekbe.VGABE | Transaction event type | |
| ekbe.GJAHR | Material document year | |
| ekbe.BELNR | Number of material document | |
| ekbe.BUZEI | Item in material document | |
| ekbe.BEWTP | Purchase order history category | |
| ekbe.BWART | Movement type (inventory management) | |
| ekbe.BUDAT | Posting date in the document | |
| ekbe.MENGE | Quantity | |
| ekbe.BPMNG | Quantity in purchase order price unit | |
| ekbe.WAERS | Currency key | |
| ekbe.WESBS | Goods receipt blocked stock in order unit | |
| ekbe.BPWES | Quantity in GR blocked stock in order price unit | |
| ekbe.SHKZG | Debit/credit indicator | |
| ekbe.BWTAR | Valuation type | |
| ekbe.ELIKZ | Delivery completed | |
| ekbe.XBLNR | Reference document number | |
| ekbe.LFGJA | Fiscal year of a reference document | |
| ekbe.LFBNR | Document number of a reference document | |
| ekbe.LFPOS | Item of a reference document | |
| ekbe.GRUND | Reason for movement | |
| ekbe.CPUDT | Day on which accounting document was entered | |
| ekbe.CPUTM | Time of entry | |
| ekbe.EVERE | Compliance with shipping instructions | |
| ekbe.REFWR | Invoice value in foreign currency | |
| ekbe.MATNR | Material number | |
| ekbe.WERKS | Plant | |
| ekbe.XWSBR | Reversal of GR allowed for GR-based IV despite invoice | |
| ekbe.ETENS | Sequential number of vendor confirmation | |
| ekbe.KNUMV | Number of the document condition | |
| ekbe.MWSKZ | Tax on sales/purchases code | |
| ekbe.LSMNG | Quantity in unit of measure from delivery note | |
| ekbe.LSMEH | Unit of measure from delivery note | |
| ekbe.EMATN | Material number | |
| ekbe.HSWAE | Local currency key | |
| ekbe.BAMNG | Quantity | |
| ekbe.CHARG | Batch number | |
| ekbe.BLDAT | Document date in document | |
| ekbe.XWOFF | Calculation of ValOpen | |
| ekbe.XUNPL | Unplanned account assignment from invoice verification | |
| ekbe.ERNAM | Name of person who created the object | |
| ekbe.SRVPOS | Service number | |
| ekbe.PACKNO | Package number of service | |
| ekbe.INTROW | Line number of service | |
| ekbe.BEKKN | Number of PO account assignment | |
| ekbe.LEMIN | Returns indicator | |
| ekbe.AREWB | Clearing value on GR/IR account in PO currency | |
| ekbe.REWRB | Invoice amount in PO currency | |
| ekbe.SAPRL | SAP release | |
| ekbe.MENGE_POP | Quantity | |
| ekbe.BPMNG_POP | Quantity in purchase order price unit | |
| ekbe.DMBTR_POP | Amount in local currency | |
| ekbe.WRBTR_POP | Amount in document currency | |
| ekbe.WESBB | Valuated goods receipt blocked stock in order unit | |
| ekbe.BPWEB | Quantity in valuated GR blocked stock in order price unit | |
| ekbe.WEORA | Acceptance at origin | |
| ekbe.AREWR_POP | GR/IR account clearing value in local currency | |
| ekbe.KUDIF | Exchange rate difference amount | |
| ekbe.RETAMT_FC | Retention amount in document currency | |
| ekbe.RETAMT_LC | Retention amount in company code currency | |
| ekbe.RETAMTP_FC | Posted retention amount in document currency | |
| ekbe.RETAMTP_LC | Posted security retention amount in company code currency | |
| ekbe.XMACC | Multiple account assignment | |
| ekbe.WKURS | Exchange rate | |
| ekbe.INV_ITEM_ORIGIN | Origin of an invoice item | |
| ekbe.VBELN_ST | Delivery | |
| ekbe.VBELP_ST | Delivery item | |
| ekbe.SGT_SCAT | Stock segment | |
| ekbe.ET_UPD | Procedure for updating the schedule line quantity | |
| ekbe.J_SC_DIE_COMP_F | Depreciation completion flag | |
| ekbe.WRF_CHARSTC1 | Characteristic value 1 | |
| ekbe.WRF_CHARSTC2 | Characteristic value 2 | |
| ekbe.WRF_CHARSTC3 | Characteristic value 3 | |
| CalendarDateDimension_BUDAT.CalYear | Year of posting date in the document | |
| CalendarDateDimension_BUDAT.CalMonth | Month of posting date in the document | |
| CalendarDateDimension_BUDAT.CalWeek | Week of posting date in the document | |
| CalendarDateDimension_BUDAT.CalQuarter | Quarter of posting date in the document | |
| CalendarDateDimension_BLDAT.CalYear | Year of document date in document | |
| CalendarDateDimension_BLDAT.CalMonth | Month of document date in document | |
| CalendarDateDimension_BLDAT.CalWeek | Week of document date in document | |
| CalendarDateDimension_BLDAT.CalQuarter | Quarter of document date in document | |
| currency_decimal.CURRFIX | Currency decimal | |
| AmountInLocalCurrency_DMBTR | Amount in local currency | |
| AmountInDocumentCurrency_WRBTR | Amount in document currency | |
| GrirAccountClearingValueInLocalCurrency_AREWR | GR/IR account clearing value in local currency | |
| InvoiceValueEntered__inLocalCurrency___REEWR | Invoice value entered in local currency | |
| ClearingValueOnGrirClearingAccount__transacCurrency___AREWW | Clearing value on GR/IR clearing account in transaction currency |