Categories
Add Actual OnHand Amount to Facts - Analytics Currency
Currently the Facts - Analytics Currency → Unit Cost is Avg(Global Puom Unit Cost). We really need the non average cost or actual cost of the items on hand which would be PRIMARY_ONHAND_QUANTITY * GLOBAL_PUOM_UNIT_COST or just having the GLOBAL_ONHAND_VALUE available would work as well.
In this example these are the parameters, I XX out the org parameters:
1. Fiscal Calendar Name = ‘XX’
2. Fiscal Year = ‘2024’
3. Fiscal Quarter = ‘202Q34’
4. Fiscal Period = ‘Jul-24’
5. Business Unit Name = ‘XX’
6. Inventory Organization = ‘XXXX’
7. Item Primary UOM = ‘KT’
The issue this is causing is this:
SNAPSHOT_PERIOD_END_DATE | PRIMARY_UOM_CODE | PRIMARY_ONHAND_QUANTITY | PRIMARY_SALES_QUANTITY | GLOBAL_ONHAND_VALUE | GLOBAL_SALES_VALUE | GLOBAL_PUOM_UNIT_COST |
|---|---|---|---|---|---|---|
31-Jul-24 | KT | 0 | 0 | 0 | 0 | 24.57 |
31-Jul-24 | KT | 0 | 0 | 0 | 0 | 47.07 |
31-Jul-24 | KT | 2 | 1 | 10.38 | 5.19 | 5.19 |
31-Jul-24 | KT | 2 | 0 | 50 | 0 | 25 |
31-Jul-24 | KT | 274 | 55 | 3294.028 | 661.21 | 12.022 |
278 | 3354.408 | 22.7704 |
Taken from the 25R1_SCM_Metric_Calculation_Logic excel:
SCM - Inventory Turns | Unit Cost | Avg(Global Puom Unit Cost) |
|---|---|---|
SCM - Inventory Turns | Inventory Onhand Value | "Inventory Onhand Quantity" * "Unit Cost" |
This is taken from our production data. The way this is currently working is taking the:
Avg(Global Puom Unit Cost) * Inventory Onhand Value
22.7704 * 278 = 6330.1712
Whereas the actual onhand value is 3354.408, as you can see this is causing a discrepancy for our actual onhand values. When this is done across all of our items in inventory it is making our on hand value way off.
Comments
-
Hi Chris,
Thank you for sharing details. For this example, please let me know the inventory value shown in the Fusion Inventory valuation UI and it should match.
As per design, Onhand amount in FDI will match with Fusion Inventory valuation UI. If it not matching, please log SR and we will look into this.
Regards,
Ramamurthy
0 -
Ramamurthy,
The inventory value shown in the Fusion UI inventory valuation is correct. The problem is the design of the FDI Inventory valuation is using average(Unit Cost) across the unit of measure. I logged an SR and was told this was by design.
But that is the problem, Fusion does not match FDI because of how FDI was designed. SR 4-0000375316
0 -
The document I am referencing for the calculations is from here
Metric Calculation Spreadsheet - 25R1_SCM_Metric_Calculation_Logic
in this spreadsheet row 283 the calculation is:
SCM - Inventory Turns
Unit Cost
Avg(Global Puom Unit Cost)
This premise is the wrong approach to take for getting a total unit of cost on hand valuation. We would not take an average of unit costs because an item shares a UOM. I hope this makes sense?
1 -
Apologies for the small font. This is the SQL for the component out of FDI
- count(nvl(T1149.GLOBAL_PUOM_UNIT_COST , 0.0)) as c6
- sum(nvl(T1149.GLOBAL_PUOM_UNIT_COST , 0.0)) as c7
- sum(D1.c7) over () / sum(D1.c6) over () as c1
So, counting the individual rows, regardless if there is even stock on hand for that item. Summing up the unit cost and then dividing unit cost by number of items with that UOM. This is giving the average that is shown in the output here as C1 and also I am showing it in the excel data in my comment above, that is detail data out of the table OAX$OAC.DW_INV_TURNS_CF.
In OAX$OAC.DW_INV_TURNS_CF there is a field GLOBAL_ONHAND_VALUE that is already doing the correct calculations for us. Which would be PRIMARY_ONHAND_QUANTITY * GLOBAL_PUOM_UNIT_COST. This would also, essentially ignore the two items that have 0 on hand, since it would be multiplying by 0. Which is already handled in the GLOBAL_ONHAND_VALUE field.
0 -
Hi Chris, Thanks for your inputs and feedback.
0 -
We are actively working on your inputs and feedback to develop a solution that incorporates your suggestions while ensuring it does not impact any custom reports and dashboards created by other customers within this subject area.
0 -
You will hear back from us or from our CSMs very shortly. Thanks again for your support!
0
