StockMonthlySnapshots
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - StockMonthlySnapshots
Overview
This view provides a monthly snapshot of stock data for materials in SAP systems. It includes key information such as material number, plant, storage location, batch number, company code, stock characteristic, and quantity and value on hand. The view also provides calculated metrics such as quantity issued to delivery and total consumption quantity.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.stock_monthly_snapshots |
Table containing the monthly stock snapshot data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StockCharacteristicsConfig |
Table containing the stock characteristic configuration. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.currency_decimal |
Table containing the currency decimal conversion rates. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StorageLocationsMD |
Table containing the storage location master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CompaniesMD |
Table containing the company master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PlantsMD |
Table containing the plant master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002 |
Table containing the language master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsMD |
Table containing the material master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialTypesMD |
Table containing the material type master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialGroupsMD |
Table containing the material group master data. |
Key Use Cases
- Reporting on stock levels and movements over time.
- Analysis of stock trends and patterns.
- Identification of slow-moving or obsolete stock.
- Planning for future stock requirements.
Query Logic
The query first joins the stock_monthly_snapshots table with the StockCharacteristicsConfig table to filter out stock with the 'BlockedReturns' characteristic. It then joins the resulting table with the other master data tables to enrich the data with additional information. The query then calculates the stock on hand, stock on hand value, quantity issued to delivery, and total consumption quantity metrics. Finally, the query groups the data by the key fields and aggregates the metrics.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MMBE | Display Material Master |
| MB51 | Stock Overview: Plant |
| MB52 | Stock Overview: Material |
| MM03 | Create/Change Material Master |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client | |
| MaterialNumber_MATNR | Material Number | |
| MaterialType_MTART | Material Type | |
| DescriptionOfMaterialType_MTBEZ | Description of Material Type | |
| MaterialText_MAKTX | Material Text | |
| MaterialGroup_MATKL | Material Group | |
| MaterialGroupName_WGBEZ | Material Group Name | |
| Plant_WERKS | Plant | |
| Plant_Name2_NAME2 | Plant Name | |
| StorageLocation_LGORT | Storage Location | |
| StorageLocationText_LGOBE | Storage Location Text | |
| BatchNumber_CHARG | Batch Number | |
| CompanyCode_BUKRS | Company Code | |
| CompanyText_BUTXT | Company Text | |
| CountryKey_LAND1 | Country Key | |
| LanguageKey_SPRAS | Language Key | |
| BaseUnitOfMeasure_MEINS | Base Unit of Measure | |
| StockCharacteristic | Stock Characteristic | |
| CurrencyKey_WAERS | Currency Key | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| CalYear | Calendar Year | |
| CalMonth | Calendar Month | |
| MonthEndDate | Month End Date | |
| QuantityMonthlyCumulative | Quantity Monthly Cumulative | |
| AmountMonthlyCumulative | Amount Monthly Cumulative | |
| StockOnHand | Stock On Hand | |
| StockOnHandValue | Stock On Hand Value | |
| QuantityIssuedToDelivery | Quantity Issued To Delivery | |
| TotalConsumptionQuantity | Total Consumption Quantity |