Inventory Position Report
Inventory Position Report
Business Schema:
Schema: SAPECC_MM_MD
Table: InventoryPosition
Go Live: April 8, 2024
Users: Gillian Thomas and Chris Stamper
Purpose: Report is used to display the amount of inventory on hand and the future needs.
Fields/Definitions:
Field Name
Definition
Formula/Table
Season
SAPECC_MM_MD.TVM3T.BEZEI
Material Number
Use formula to remove zeros SUBSTR(IV.MaterialNumber,(6 + 1), 12)
SAPECC_MM_MD.MBEW.MATNR
Material Description
SAPECC_MM_MD.MAKT.MAKTX
Retail Pack Size
SAPECC_MM_MD.T179T.VTEXT
Average Sales Qty Past 13 weeks
Average Invoice qty for the past 13 Weeks. The past 13 weeks is calculated by looking at a date range between weekstartdate -91 days and weekstartdate -1. (Billing tables: SAPECC_BL.VBRK and SAPECC_BL.VBRP)
round(SAPECC_MM_MD.InventoryPosition.Sales_Qty__Past_13_Weeks_ / 13)
Gross Inventory (no held/blocked inventory)
Valuated Quantity originate from SAPECC_MM_MD.MBEW.LBKUM
(SAPECC_MM_MD.InventoryPosition.Valuated_Qty)
Held/Blocked Inventory
Inventory that is coded as held or blocked
Weeks of Gross Inventory
# of weeks of gross inventory available. Formula is taking the average sales qty past 13 weeks column and dividing that by the gross inventory.
round(
Sum(
sum(SAPECC_MM_MD.InventoryPosition.Sales_Qty__Past_13_Weeks_ / 13)
) / SAPECC_MM_MD.InventoryPosition.Valuated_Qty
)
)
All Open Orders
Net Inventory
The calculation takes the gross inventory – open orders.
SAPECC_MM_MD.InventoryPosition.Valuated_Qty - SAPECC_MM_MD.InventoryPosition.Open_Sales_Order_Qty__Next_13_Weeks_
Weeks of Net Inventory
# of weeks of net inventory available
round(
sum(
sum(SAPECC_MM_MD.InventoryPosition.Valuated_Qty - SAPECC_MM_MD.InventoryPosition.Open_Sales_Order_Qty__Next_13_Weeks_) / (SAPECC_MM_MD.InventoryPosition.Sales_Qty__Past_13_Weeks_ / 13)
))
Average Forecast Qty
Average forecast qty comes out of GIB
SAPECC_MM_MD.InventoryPosition.ForecastQty
Weeks on Hand Based on Forecast
Gross Inventory / Forecast Inventory * 13
round(
(SAPECC_MM_MD.InventoryPosition.Valuated_Qty / SAPECC_MM_MD.InventoryPosition.ForecastQty) * 13
)
Individual Filters
Filter Fields
Table
Criteria
Custom formula
Case(
ifnull(
SAPECC_MM_MD.InventoryPosition.Valuated_Qty, 0) <> 0,1,
ifnull(
SAPECC_MM_MD.InventoryPosition.Open_Sales_Order_Qty__Next_13_Weeks_, 0) <> 0,1,
ifnull(
SAPECC_MM_MD.InventoryPosition.Sales_Qty__Past_13_Weeks_,
0) <> 0, 1,
ifnull(
SAPECC_MM_MD.InventoryPosition.ForecastQty,
0) <> 0,1,-1)
Change Control
Date
Modification Made
Version
Updated by
5/8/2024
Updated the dashboard to include the listing of all inventory and order breakout by months insight.
8/5/2024 11:46am
George Boose