VendorPerformance
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - VendorPerformance
Overview
This view provides a comprehensive analysis of purchase order data from SAP systems, including vendor performance metrics, invoice information, and detailed item-level data. It combines data from multiple tables to provide a holistic view of purchase orders, enabling users to analyze trends, identify areas for improvement, and make informed decisions.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002 |
Table containing language keys used for text translations. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CurrencyConversion |
Table containing currency conversion rates. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchaseDocuments |
Table containing header-level purchase order data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.POSchedule |
Table containing schedule line data for purchase orders. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchaseDocumentsHistory |
Table containing historical purchase order data, including goods receipt and invoice information. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchasingOrganizationsMD |
Table containing master data for purchasing organizations. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchasingGroupsMD |
Table containing master data for purchasing groups. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorsMD |
Table containing master data for vendors. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CompaniesMD |
Table containing master data for companies. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsMD |
Table containing master data for materials. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialTypesMD |
Table containing master data for material types. |
Key Use Cases
- Analyze vendor performance metrics such as cycle time, rejection rates, on-time delivery, and invoice accuracy.
- Identify trends in purchase order patterns, such as changes in order volume, pricing, and lead times.
- Monitor the status of purchase orders, including delivery status, goods receipt information, and invoice details.
- Drill down into item-level data to analyze specific purchase orders and identify areas for cost savings or process improvements.
Query Logic
The view combines data from multiple tables using a series of joins and aggregations. It first retrieves language-specific text descriptions for various fields, such as material names and vendor names. Then, it joins the purchase order header data with the schedule line data to obtain item-level details. Historical data from the purchase order history table is also joined to provide information on goods receipts and invoices. Finally, currency conversion rates are applied to convert all amounts to a consistent target currency.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| ME21N | Create Purchase Order |
| ME22N | Change Purchase Order |
| ME23N | Display Purchase Order |
| ME28 | Change Purchase Order Schedule Lines |
| MIGO | Goods Receipt for Purchase Order |
| MIRO | Invoice Receipt for Purchase Order |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client ID | |
| DocumentNumber_EBELN | Purchase Order Number | |
| Item_EBELP | Purchase Order Item Number | |
| PurchasingDocumentDate_BEDAT | Purchase Order Date | |
| NetOrderValueinPOCurrency_NETWR | Net Order Value in Purchase Order Currency | |
| CurrencyKey_WAERS | Purchase Order Currency Key | |
| ItemDeliveryDate_EINDT | Item Delivery Date | |
| OrderDateOfScheduleLine_BEDAT | Order Date of Schedule Line | |
| PostingDateInTheDocument_BUDAT | Posting Date in the Document | |
| AmountInLocalCurrency_DMBTR | Amount in Local Currency | |
| POOrderHistoryCurrencyKey_WAERS | Purchase Order History Currency Key | |
| POQuantity_MENGE | Purchase Order Quantity | |
| UoM_MEINS | Unit of Measure | |
| NetPrice_NETPR | Net Price | |
| CreatedOn_AEDAT | Created On | |
| Status_STATU | Status | |
| MaterialNumber_MATNR | Material Number | |
| MaterialType_MTART | Material Type | |
| MaterialGroup_MATKL | Material Group | |
| PurchasingOrganization_EKORG | Purchasing Organization | |
| PurchasingGroup_EKGRP | Purchasing Group | |
| VendorAccountNumber_LIFNR | Vendor Account Number | |
| Company_BUKRS | Company | |
| Plant_WERKS | Plant | |
| YearOfPurchasingDocumentDate_BEDAT | Year of Purchasing Document Date | |
| MonthOfPurchasingDocumentDate_BEDAT | Month of Purchasing Document Date | |
| WeekOfPurchasingDocumentDate_BEDAT | Week of Purchasing Document Date | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| InvoiceQuantity | Invoice Quantity | |
| InvoiceAmountInSourceCurrency | Invoice Amount in Source Currency | |
| InvoiceDate | Invoice Date | |
| YearOfInvoiceDate | Year of Invoice Date | |
| MonthOfInvoiceDate | Month of Invoice Date | |
| WeekOfInvoiceDate | Week of Invoice Date | |
| InvoiceCount | Invoice Count | |
| PurchasingOrganizationText_EKOTX | Purchasing Organization Text | |
| PurchasingGroupText_EKNAM | Purchasing Group Text | |
| CountryKey_LAND1 | Vendor Country Key | |
| NAME1 | Vendor Name | |
| CompanyText_BUTXT | Company Text | |
| FiscalyearVariant_PERIV | Fiscal Year Variant | |
| LanguageKey_SPRAS | Language Key | |
| MaterialText_MAKTX | Material Text | |
| DescriptionOfMaterialType_MTBEZ | Description of Material Type | |
| VendorCycleTimeInDays | Vendor Cycle Time in Days | |
| RejectedQuantity | Rejected Quantity | |
| GoodsReceiptQuantity | Goods Receipt Quantity | |
| GoodsReceiptAmountInSourceCurrency | Goods Receipt Amount in Source Currency | |
| ExchangeRate_UKURS | Exchange Rate | |
| TargetCurrency_TCURR | Target Currency | |
| AmountInTargetCurrency_DMBTR | Amount in Target Currency | |
| NetPriceInTargetCurrency_NETPR | Net Price in Target Currency | |
| NetOrderValueinTargetCurrency_NETWR | Net Order Value in Target Currency | |
| GoodsReceiptAmountInTargetCurrency | Goods Receipt Amount in Target Currency | |
| InvoiceAmountInTargetCurrency | Invoice Amount in Target Currency | |
| IsDelivered | Delivery Status | |
| IsRejected | Vendor Quality (Rejection) | |
| VendorOnTimeDelivery | Vendor On Time Delivery | |
| VendorInFullDelivery | Vendor InFull Delivery | |
| VendorOnTimeInFullDelivery | Vendor On Time In Full Delivery | |
| VendorInvoiceAccuracy | Vendor Invoice Accuracy | |
| PastDueOrOpenItems | Past Due and Open |