StockMonthlySnapshots
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - 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, and stock characteristics. The view also calculates various metrics such as quantity on hand, stock value, 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 information. |
{{ 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 }}.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
- This view can be used to analyze stock data over time.
- It can be used to identify trends and patterns in stock levels.
- It can be used to compare stock levels across different plants, storage locations, and company codes.
- It can be used to track the movement of stock over time.
- It can be used to identify potential stock issues, such as shortages or overstocks.
Query Logic
The query first joins the stock_monthly_snapshots table with the StockCharacteristicsConfig table to get the stock characteristic for each stock snapshot. It then joins the result with the currency_decimal table to get the currency decimal information for each stock snapshot. The result is then joined with the StorageLocationsMD, CompaniesMD, PlantsMD, MaterialsMD, MaterialTypesMD, and MaterialGroupsMD tables to get the master data for each stock snapshot. Finally, the result is grouped by the key fields to get the final result.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MMBE | Display Material Master |
| MB51 | Stock Overview: Plant |
| MB52 | Stock Overview: Material |
| MB5B | Stock Overview: Storage Location |
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 |