InventoryByPlant
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - InventoryByPlant
Overview
This view provides a comprehensive snapshot of stock data for SAP systems, including key metrics such as inventory value, obsolete stock, and obsolete inventory value. It combines data from multiple source tables to provide a holistic view of stock information, enabling users to analyze stock levels, values, and trends across different dimensions.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002 |
Table containing language keys and descriptions. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CurrencyConversion |
Table containing currency conversion rates. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StockWeeklySnapshots |
Table containing weekly stock snapshots, including quantity, amount, and other stock-related information. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialLedger |
Table containing material ledger data, including standard cost and moving average price. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsBatchMD |
Table containing batch-specific material data, including date of manufacture. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialPlantsMD |
Table containing plant-specific material data, including safety stock. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PlantsMD |
Table containing plant master data, including plant name and country key. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StorageLocationsMD |
Table containing storage location master data, including storage location text. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsMD |
Table containing material master data, including material text, material type, and material group. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialTypesMD |
Table containing material type descriptions. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialGroupsMD |
Table containing material group descriptions. |
Key Use Cases
- Analyzing stock levels and trends over time.
- Identifying obsolete stock and obsolete inventory value.
- Calculating inventory value in both source and target currencies.
- Understanding the impact of currency fluctuations on inventory value.
Query Logic
The query first calculates some metrics separately to avoid consuming too many resources in the next step. It then joins the results of these calculations with the main stock data table. The query also applies currency conversion rates to calculate inventory value in both source and target currencies. Finally, it calculates obsolete stock and obsolete inventory value based on the material's shelf life.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MMBE | Display Material Stock/Requirements List |
| MB51 | Stock Overview: Plant |
| MB52 | Stock Overview: Material |
| MB5B | Stock Overview: Batch |
| MM03 | Display Material Master |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client ID | |
| MaterialNumber_MATNR | Material Number | |
| BatchNumber_CHARG | Batch Number | |
| Plant_WERKS | Plant | |
| StorageLocation_LGORT | Storage Location | |
| StorageLocationText_LGOBE | Storage Location Text | |
| CompanyCode_BUKRS | Company Code | |
| CompanyText_BUTXT | Company Text | |
| BaseUnitOfMeasure_MEINS | Base Unit of Measure | |
| CurrencyKey_WAERS | Currency Key | |
| DateOfManufacture_HSDAT | Date of Manufacture | |
| MaterialText_MAKTX | Material Text | |
| LanguageKey_SPRAS | Language Key | |
| TotalShelfLife_MHDHB | Total Shelf Life | |
| MaterialType_MTART | Material Type | |
| DescriptionOfMaterialType_MTBEZ | Description of Material Type | |
| StandardCost_STPRS | Standard Cost | |
| MovingAveragePrice_VERPR | Moving Average Price | |
| MaterialGroup_MATKL | Material Group | |
| MaterialGroupName_WGBEZ | Material Group Name | |
| SafetyStock_EISBE | Safety Stock | |
| PlantName_NAME2 | Plant Name | |
| CountryKey_LAND1 | Country Key | |
| DivisionForIntercompanyBilling_SPART | Division for Intercompany Billing | |
| ValuationArea_BWKEY | Valuation Area | |
| CalYear | Calendar Year | |
| CalWeek | Calendar Week | |
| WeekEndDate | Week End Date | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| QuantityWeeklyCumulative | Quantity Weekly Cumulative | |
| AmountWeeklyCumulative | Amount Weekly Cumulative | |
| StockCharacteristic | Stock Characteristic | |
| ExchangeRate_UKURS | Exchange Rate | |
| TargetCurrency_TCURR | Target Currency | |
| AmountWeeklyCumulativeInTargetCurrency | Amount Weekly Cumulative in Target Currency | |
| StandardCostInTargetCurrency_STPRS | Standard Cost in Target Currency | |
| MovingAveragePriceInTargetCurrency_VERPR | Moving Average Price in Target Currency | |
| InventoryValueInTargetCurrency | Inventory Value in Target Currency | |
| ObsoleteInventoryValueInTargetCurrency | Obsolete Inventory Value in Target Currency | |
| InventoryValueInSourceCurrency | Inventory Value in Source Currency | |
| ObsoleteStock | Obsolete Stock | |
| ObsoleteInventoryValueInSourceCurrency | Obsolete Inventory Value in Source Currency |