InventoryKeyMetrics
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - InventoryKeyMetrics
Overview
This view provides a comprehensive inventory report for SAP systems. It combines data from multiple source tables to calculate various inventory metrics, including inventory value, cost of goods sold, and days of supply. The view also includes currency conversion functionality to convert inventory values to a target currency.
Data Sources
| Source Table/View | Description |
|---|---|
| StockMonthlySnapshots | Monthly snapshots of inventory data, including stock on hand, stock value, and consumption quantities. |
| MaterialLedger | Material ledger data, including standard cost and moving average price. |
| CurrencyConversion | Currency conversion rates for converting inventory values to a target currency. |
| SlowMovingThreshold | Slow-moving inventory thresholds for different material types. |
Key Use Cases
- Inventory analysis and reporting
- Slow-moving inventory identification
- Inventory valuation and cost analysis
- Supply chain optimization
Query Logic
The query first calculates various inventory metrics, such as inventory value, cost of goods sold, and days of supply, using data from the StockMonthlySnapshots and MaterialLedger tables. It then joins the results with the CurrencyConversion table to convert inventory values to a target currency. Finally, the query applies slow-moving inventory thresholds to identify slow-moving items.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MMBE | Display Material Master |
| MB51 | Stock Overview: List |
| MB52 | Stock Overview: Hierarchy |
| CO09 | Display Material Ledger |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client ID | |
| MaterialNumber_MATNR | Material Number | |
| Plant_WERKS | Plant | |
| MaterialType_MTART | Material Type | |
| DescriptionOfMaterialType_MTBEZ | Description of Material Type | |
| MaterialText_MAKTX | Material Text | |
| MaterialGroup_MATKL | Material Group | |
| MaterialGroupName_WGBEZ | Material Group Name | |
| Plant_Name2_NAME2 | Plant Name | |
| CompanyCode_BUKRS | Company Code | |
| CompanyText_BUTXT | Company Text | |
| CountryKey_LAND1 | Country Key | |
| LanguageKey_SPRAS | Language Key | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| CalYear | Calendar Year | |
| CalMonth | Calendar Month | |
| MonthEndDate | Month End Date | |
| QuantityMonthlyCumulative | Quantity Monthly Cumulative | |
| BaseUnitOfMeasure_MEINS | Base Unit of Measure | |
| AmountMonthlyCumulative | Amount Monthly Cumulative | |
| CurrencyKey_WAERS | Currency Key | |
| StockOnHand | Stock On Hand | |
| StockOnHandValue | Stock On Hand Value | |
| QuantityIssuedToDelivery | Quantity Issued To Delivery | |
| TotalConsumptionQuantity | Total Consumption Quantity | |
| ValueOfTotalValuatedStock_SALK3 | Value Of Total Valuated Stock | |
| StandardCost_STPRS | Standard Cost | |
| MovingAveragePrice_VERPR | Moving Average Price | |
| TotalConsumptionQuantityForPastYear | Total Consumption Quantity For Past Year | |
| TotalConsumptionQuantityForPastYearTillToday | Total Consumption Quantity For Past Year Till Today | |
| DemandPerDayForPastYearTillToday | Demand Per Day For Past Year Till Today | |
| CostOfGoodsSoldByMonth | Cost Of Goods Sold By Month | |
| InventoryValue | Inventory Value | |
| ThresholdValue | Threshold Value | |
| InventoryByMonth | Inventory By Month | |
| AvgInventoryByMonth | Avg Inventory By Month | |
| ExchangeRate_UKURS | Exchange Rate | |
| TargetCurrency_TCURR | Target Currency | |
| AmountMonthlyCumulativeInTargetCurrency | Amount Monthly Cumulative In Target Currency | |
| StockOnHandValueInTargetCurrency | Stock On Hand Value In Target Currency | |
| StandardCostInTargetCurrency_STPRS | Standard Cost In Target Currency | |
| MovingAveragePriceInTargetCurrency_VERPR | Moving Average Price In Target Currency | |
| CostofGoodsSoldInTargetCurrency | Costof Goods Sold In Target Currency | |
| InventoryValueInTargetCurrency | Inventory Value In Target Currency | |
| InventoryByMonthInTargetCurrency | Inventory By Month In Target Currency | |
| AvgInventoryByMonthInTargetCurrency | Avg Inventory By Month In Target Currency | |
| ValueOfTotalValuatedStockInTargetCurrency_SALK3 | Value Of Total Valuated Stock In Target Currency | |
| SlowMovingInventoryAsOfPreviousMonthInTargetCurrency | Slow Moving Inventory As Of Previous Month In Target Currency | |
| DaysOfSupplyAsOfToday | Days Of Supply As Of Today | |
| SlowMovingIndicatorAsOfPreviousMonth | Slow Moving Indicator As Of Previous Month | |
| SlowMovingInventoryAsOfPreviousMonthInSourceCurrency | Slow Moving Inventory As Of Previous Month In Source Currency | |
| InventoryTurnByMonth | Inventory Turn By Month |