VendorPerformanceOverview
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - VendorPerformanceOverview
Overview
The 'VendorPerformanceOverview' view is a reporting view built as a reference for details of calculations implemented in dashboards. It is not designed for extensive reporting or analytical use.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorPerformance |
This table contains the data used to calculate the metrics in the view. It is a denormalized table that includes data from multiple source tables, including purchasing documents, invoices, and material master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialLedger |
This table contains the material ledger data used to calculate the purchase price variance metrics. It includes data such as standard cost, moving average price, and posting period. |
Key Use Cases
- This view can be used to report on vendor performance metrics such as rejection rate, on-time delivery rate, and invoice accuracy.
- It can also be used to analyze purchase price variance and identify opportunities for cost savings.
Query Logic
The view is created using a series of subqueries and joins. The first subquery, VendorPerformance, groups the data by the following dimensions: Date, Company, Purchasing Organization, Purchasing Group, Vendor, Vendor Country, Fiscal Year Variant, Material, Material Type, Plant, Target Currency, and Language. It also calculates the following metrics: POQuantity_MENGE, AverageNetPrice, TotalSpend, ClearedInvoices, CountRejectedOrders, CountNotRejectedOrders, CountNotDelayedOrders, CountDelayedOrders, CountInFullOrders, CountNotInFullOrders, CountAccurateInvoices, CountInaccurateInvoices, CountPastDueOrders, and CountOpenOrders.
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 | Maintain Material Ledger |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client ID | |
| PurchasingDocumentDate_BEDAT | Purchasing Document Date | |
| YearOfPurchasingDocumentDate_BEDAT | Year of Purchasing Document Date | |
| MonthOfPurchasingDocumentDate_BEDAT | Month of Purchasing Document Date | |
| WeekOfPurchasingDocumentDate_BEDAT | Week of Purchasing Document Date | |
| Company_BUKRS | Company Code | |
| Company | Company Name | |
| PurchasingOrganization_EKORG | Purchasing Organization | |
| PurchasingOrganization | Purchasing Organization Name | |
| PurchasingGroup_EKGRP | Purchasing Group | |
| PurchasingGroup | Purchasing Group Name | |
| VendorAccountNumber_LIFNR | Vendor Account Number | |
| VendorName | Vendor Name | |
| VendorCountry | Vendor Country | |
| FiscalyearVariant_PERIV | Fiscal Year Variant | |
| MaterialNumber_MATNR | Material Number | |
| Plant_WERKS | Plant | |
| MaterialType_MTART | Material Type | |
| POQuantity_MENGE | PO Quantity | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| InvoiceDate | Invoice Date | |
| TotalSpend | Total Spend | |
| ClearedInvoices | Cleared Invoices | |
| YearOfInvoiceDate | Year of Invoice Date | |
| MonthOfInvoiceDate | Month of Invoice Date | |
| WeekOfInvoiceDate | Week of Invoice Date | |
| AverageNetPrice | Average Net Price | |
| TargetCurrency_TCURR | Target Currency | |
| AverageNetPriceInTargetCurrency | Average Net Price in Target Currency | |
| TotalSpendInTargetCurrency | Total Spend in Target Currency | |
| CountRejectedOrders | Count of Rejected Orders | |
| CountNotRejectedOrders | Count of Not Rejected Orders | |
| CountNotDelayedOrders | Count of Not Delayed Orders | |
| CountDelayedOrders | Count of Delayed Orders | |
| CountInFullOrders | Count of In Full Orders | |
| CountNotInFullOrders | Count of Not In Full Orders | |
| CountAccurateInvoices | Count of Accurate Invoices | |
| CountInaccurateInvoices | Count of Inaccurate Invoices | |
| CountPastDueOrders | Count of Past Due Orders | |
| CountOpenOrders | Count of Open Orders | |
| Material | Material Description | |
| MaterialType | Material Type Description | |
| LanguageKey_SPRAS | Language Key | |
| ValuationPrice | Valuation Price | |
| ValuationPriceInTargetCurrency | Valuation Price in Target Currency | |
| PurchasePriceVariance | Purchase Price Variance | |
| PurchasePriceVarianceInTargetCurrency | Purchase Price Variance in Target Currency |