Scenario Overview 

Imagine a mid-sized manufacturing organisation that operates multiple warehouses across different regions. The organisation utilises the Oracle Fusion SCM Inventory to manage its inventory, item costs, and transactions efficiently. One of the critical business requirements is to analyse cost fluctuations of inventory items and monitor if any transactions have been processed using varying Units of Measure (UOM). This is essential for accurate cost accounting, pricing strategies, and inventory valuation.

Challenge 

• The cost of an item can vary over time due to changes in procurement, supplier contracts, or production overheads. 
• Items might be transacted in different UOMs depending on specific use cases or regional preferences. For instance: 
• Bulk purchases might use cartons (CTN). 
• Smaller sales might use pieces (PC). 
• Internal usage may use alternative units like kilograms (KG). 

The organisation’s supply chain team needs to: 

1. Identify cost changes for each item over time. 

2. Determine if the same cost applies to multiple transactions or if there were real cost changes. 

3. Monitor UOM variations in transactions and reconcile them to the standard UOM used for costing. 

Take your expertise to the next level with our
Oracle Fusion SCM (Supply Chain Management) Training

Solution 

A custom SQL query, integrated within Oracle Fusion BI Publisher (BIP), helps to: 

1. Extract historical item cost details, effective cost dates, and corresponding UOMs from the Inventory and Costing modules. 

2. Filter records to display: 

• For items with unchanged costs, only the latest entry is based on the cost-effective date. 
• For items with cost changes, all records show the variations. 

3. Identify discrepancies between the transaction UOM and the standard costing UOM. 

Query Logic 

• The query leverages Oracle Fusion SCM tables like: 
  • CST_PERPAVG_COST: Stores perpetual average costs for items. 
  • EGP_SYSTEM_ITEMS_B: Provides master item details. 
  • INV_UNITS_OF_MEASURE_B: Maps UOM codes to their descriptions. 
  • INV_MATERIAL_TXNS: Captures transaction details. 
• Using a combination of ROW_NUMBER() for ranking and conditional filtering, the query outputs: 
• Latest costs for items with unchanged prices. 
• All historical costs for items with price variations. 
• Transaction UOM details alongside the costing UOM for reconciliation. 

Practical Example 

Items and Cost Changes

• Item 1000467 (Trash Bag 10 GAL Blue): 
• Cost remained constant (20) across several effective dates. 
• Only the latest effective cost date (12-JAN-2021) is retained. 
• Item 1000468 (Trash Bag 30 GAL Black): 
• Cost fluctuated (14.55, 14.73, 14.82) over multiple effective dates. 
• All rows are displayed to analyse the changes. 

Transaction UOM Reconciliation

• Some transactions for Item 1000467 used PKT (packets), while others used EA (each). This discrepancy is flagged for investigation. 

Functional Benefits 

1. Streamlined Cost Analysis: 

• Enables finance and supply chain teams to focus on meaningful cost changes without sifting through redundant data. 
• Provides insights into cost trends for budgeting and forecasting. 

2. Enhanced UOM Management: 

• Identifies and reconciles UOM discrepancies, ensuring accurate inventory valuation. 
• Helps to standardise UOM usage across transactions. 

3. Improved Decision-Making: 

• Facilitates timely decisions on pricing, procurement, and production strategies. 
• Supports auditing and compliance efforts by maintaining transparency in cost data. 

Solution Architecture 

1. Source Systems: 

• Oracle Fusion SCM (Inventory and Costing modules). 

2. Technology Stack: 

• Oracle SQL for data extraction and transformation. 
• Oracle BI Publisher for reporting and visualisation. 

3. Integration: 

• The query can be embedded as a BI report or scheduled to run periodically. 
• Results can be exported to Excel or integrated into dashboards for real-time monitoring. 

Query: 

WITH costing_details AS (
SELECT DISTINCT  
           Trunc(cpc.unit_cost_average, 4) AS unit_cost_average, 
           NVL(UOMT_COST.UNIT_OF_MEASURE, cpc.uom_code) AS uom, 
           cpc.currency_code AS curr, 
           TO_CHAR(cpc.eff_date, 'DD-MM-YYYY') AS effective_cost_date, 
           vub.val_unit_id, 
           vub.val_unit_code, 
           cicp.inventory_item_id AS item_id, 
           ccio.inv_org_id, 
           iop.organisation_code AS org_name, 
           cco.cost_org_name, 
           esi.item_number, 
           esi.description 
    FROM    
           cst_cost_inv_orgs ccio, 
           cst_item_cost_profiles cicp, 
           cst_cost_profiles_b ccp, 
           cst_val_units_b vub, 
           cst_val_structures_b vsb, 
           cst_val_unit_details vud, 
           cst_val_unit_combinations vuc, 
           cst_perpavg_cost cpc, 
           cst_cost_orgs_v cco, 
           inv_org_parameters iop, 
           cst_transactions ct, 
           egp_system_items_vl esi, 
           INV_UNITS_OF_MEASURE_B UOMB, 
           INV_UNITS_OF_MEASURE_TL UOMT_COST 
    WHERE   
           ct.cost_org_id = cpc.cost_org_id 
           AND ct.cost_book_id = cpc.cost_book_id 
           AND ct.val_unit_id = cpc.val_unit_id 
           AND ct.transaction_id = cpc.transaction_id 
           AND ccio.cost_org_id = cicp.cost_org_id 
           AND ccp.cost_profile_id = cicp.asset_cost_profile_id 
           AND vub.cost_org_id = cicp.cost_org_id 
           AND vub.cost_book_id = cicp.cost_book_id 
           AND vub.val_structure_id = ccp.val_structure_id 
           AND vub.val_unit_id = vud.val_unit_id 
           AND vud.val_unit_combination_id = vuc.val_unit_combination_id 
           AND ccp.val_structure_id = vsb.val_structure_id 
           AND vsb.structure_instance_number = vuc.structure_instance_number 
           AND vub.cost_org_id = cco.cost_org_id 
           AND iop.organisation_id = ccio.inv_org_id 
           AND vuc.cost_org_code = cco.cost_org_code 
           AND vub.val_structure_id = ccp.val_structure_id 
           AND vub.val_unit_id = vud.val_unit_id 
           AND vub.val_structure_id = vud.val_structure_id 
           AND vub.cost_book_id = vud.cost_book_id 
           AND vud.val_unit_combination_id = vuc.val_unit_combination_id 
           AND NVL(vuc.inv_org_code, iop.organisation_code) = iop.organisation_code 
           AND cpc.cost_org_id = cicp.cost_org_id 
           AND cpc.cost_book_id = cicp.cost_book_id 
           AND cpc.inventory_item_id = cicp.inventory_item_id 
           AND cpc.val_unit_id = vub.val_unit_id 
           AND Trunc(cpc.cost_date) <= Trunc(ct.cost_date) 
           AND cpc.cost_book_id = ( 
                SELECT ccb.cost_book_id 
                FROM   cst_cost_org_books ccb 
                WHERE  cicp.cost_org_id = ccb.cost_org_id 
                AND    ccb.primary_book_flag = 'Y' 
           ) 
           AND vsb.val_structure_type_code = 'ASSET' 
           AND esi.inventory_item_id = cicp.inventory_item_id 
           AND ccio.inv_org_id = esi.organisation_id 
           AND UOMB.UOM_CODE = cpc.uom_code 
           AND UOMB.UNIT_OF_MEASURE_ID = UOMT_COST.UNIT_OF_MEASURE_ID 
), 
transaction_details AS ( 
    SELECT  
           esi.item_number, 
           UOMT.UNIT_OF_MEASURE AS transaction_uom,  
           UOMT_item.UNIT_OF_MEASURE AS uom_item 
    FROM    
           INV_MATERIAL_TXNS imt,  
           INV_UNITS_OF_MEASURE_B UOMB,  
           INV_UNITS_OF_MEASURE_B UOMB_item,  
           INV_UNITS_OF_MEASURE_TL UOMT,  
           INV_UNITS_OF_MEASURE_TL UOMT_item,  
           INV_ONHAND_QUANTITIES_DETAIL IOQD,  
           EGP_SYSTEM_ITEMS_B esi 
    WHERE   
           UOMB.UOM_CODE = imt.TRANSACTION_UOM 
           AND UOMB_item.UOM_CODE = IOQD.TRANSACTION_UOM_CODE 
           AND UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID 
           AND UOMT_item.UNIT_OF_MEASURE_ID = UOMB_item.UNIT_OF_MEASURE_ID 
           AND IOQD.INVENTORY_ITEM_ID = imt.inventory_item_id 
           AND IOQD.INVENTORY_ITEM_ID = esi.inventory_item_id 
           AND IOQD.ORGANISATION_ID = esi.organisation_id 
    GROUP BY  
           esi.item_number, 
           UOMT.UNIT_OF_MEASURE,  
           UOMT_item.UNIT_OF_MEASURE 
), 
filtered_costing_details AS ( 
    SELECT  
           cd.*, 
           ROW_NUMBER() OVER ( 
               PARTITION BY cd.item_number, cd.unit_cost_average  
               ORDER BY TO_DATE(cd.effective_cost_date, 'DD-MM-YYYY') DESC 
           ) AS rn 
    FROM    
           costing_details cd 
) 
SELECT  
    fcd.item_id, 
    fcd.item_number, 
    fcd.effective_cost_date, 
    fcd.description, 
    fcd.unit_cost_average, 
    NVL(td.transaction_uom, fcd.uom) AS transaction_uom, 
    NVL(td.uom_item, fcd.uom) AS uom_item 
FROM  
    filtered_costing_details fcd 
LEFT JOIN  
    transaction_details td  
ON  
    fcd.item_number = td.item_number 
WHERE  
    fcd.rn = 1 OR NOT EXISTS ( 
        SELECT 1  
        FROM filtered_costing_details fc 
        WHERE  
            fc.item_number = fcd.item_number  
            AND fc.unit_cost_average <> fcd.unit_cost_average 
    ) 
ORDER BY  
    fcd.item_number, TO_DATE(fcd.effective_cost_date, 'DD-MM-YYYY') 

Conclusion 

This solution demonstrates how Oracle Fusion SCM and BI Publisher can be leveraged to enhance inventory cost visibility and streamline UOM management. By addressing these challenges, the organisation can optimise its supply chain operations, improve cost control, and ensure consistent data quality across transactions. 

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices