VendorPerformanceOverview
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - 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 SAP 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 valuation type. |
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 date, company, purchasing organization, purchasing group, vendor, vendor country, fiscal year variant, material, material type, plant, target currency, and language. It also calculates various metrics such as total spend, cleared invoices, and rejection rate.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| ME21N | Create Purchase Order |
| ME22N | Change Purchase Order |
| ME23N | Display Purchase Order |
| MIGO | Goods Receipt for Purchase Order |
| MIRO | Invoice Verification for Purchase Order |
| MM01 | Create Material Master |
| MM02 | Change Material Master |
| MM03 | Display Material Master |
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 |