POOrderHistory
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - POOrderHistory
Overview
This reporting view provides detailed information about purchase documents, including goods receipt and invoice receipt data, from SAP systems. It combines data from the PurchaseDocuments table in the target dataset with the ekbe table from the source dataset, and includes additional data from currency_decimal, calendar_date_dim, and currency_conversion tables.
Data Sources
| Source Table/View | Description |
|---|---|
| PurchaseDocuments | A table in the target dataset that contains information about purchase documents, including vendor account numbers, document numbers, and item numbers. |
| ekbe | A table from the source SAP system that contains detailed data about purchase documents, including transaction event types, material document numbers, and quantity information. |
| currency_decimal | A table in the target dataset that contains currency decimal information. |
| calendar_date_dim | A table in the source SAP system that contains calendar date dimension information. |
| currency_conversion | A table in the target dataset that contains currency conversion information. |
Key Use Cases
- This view can be used to analyze purchase document data, including goods receipt and invoice receipt data, for reporting and analysis purposes.
- It can be used to track the status of purchase orders, monitor inventory levels, and analyze purchasing trends.
Query Logic
The query logic joins the PurchaseDocuments table with the ekbe table on the Client_MANDT, DocumentNumber_EBELN, and Item_EBELP fields. It then left joins the currency_decimal table on the CurrencyKey_WAERS field to get currency decimal information. It also left joins the calendar_date_dim table on the BUDAT and BLDAT fields to get calendar date dimension information. Finally, it left joins the currency_conversion table on the Client_MANDT, CurrencyKey_WAERS, ChangeDate_AEDAT, and TCURR fields to get currency conversion information.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| ME21N | Create Purchase Order |
| ME22N | Change Purchase Order |
| ME23N | Display Purchase Order |
| MIGO | Goods Receipt |
| MIRO | Invoice Receipt |
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 | |
| ekbe.DMBTR * currency_decimal.CURRFIX | Amount in local currency | |
| ekbe.WRBTR * currency_decimal.CURRFIX | Amount in document currency | |
| ekbe.AREWR * currency_decimal.CURRFIX | GR/IR account clearing value in local currency | |
| ekbe.REEWR * currency_decimal.CURRFIX | Invoice value entered in local currency | |
| ekbe.AREWW * currency_decimal.CURRFIX | Clearing value on GR/IR clearing account in transac currency |