nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Add Actual OnHand Amount to Facts - Analytics Currency

Chris Haase

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.

Find more posts tagged with

Needs Votes

Status: Needs Votes

Comments

Ramamurthy Shankarachetty-Oracle

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

Chris Haase

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

Chris Haase

The document I am referencing for the calculations is from here

https://docs.oracle.com/en/cloud/saas/analytics/25r1/fascm/chapter-data-lineage.html#GUID-1E0A8BAA-568F-4DA0-A91A-C867F787C29D

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?

Chris Haase

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.

[Deleted User]

Hi Chris, Thanks for your inputs and feedback.

[Deleted User]

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.

[Deleted User]

You will hear back from us or from our CSMs very shortly. Thanks again for your support!