InventoryByPlant
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - InventoryByPlant
Overview
This view provides a comprehensive overview of stock data for SAP systems. It combines data from multiple source tables to provide key metrics such as inventory value, obsolete stock, and obsolete inventory value, both in target and source currencies.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002 |
Table containing language keys. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CurrencyConversion |
Table containing currency conversion rates. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StockWeeklySnapshots |
Table containing weekly stock snapshots. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialLedger |
Table containing material ledger data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialPlantsMD |
Table containing material plant master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PlantsMD |
Table containing plant master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StorageLocationsMD |
Table containing storage location master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsBatchMD |
Table containing material batch master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsMD |
Table containing material master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialTypesMD |
Table containing material type master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialGroupsMD |
Table containing material group master data. |
Key Use Cases
- Inventory management and planning
- Financial reporting and analysis
- Supply chain optimization
- Obsolete stock identification and management
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 and applies currency conversion to provide the final results.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MMBE | Display Material Master |
| MB51 | Stock Overview: List |
| MB52 | Stock Overview: Hierarchy |
| CO09 | Display Production Order |
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 |