Oracle Process Manafacturing(OPM) - Inventory Valuation Report

How is the cost calculated in Inventory Valuation Report when the Costing Method is "Lot Costing"?

Whenever an Item lot is received in the inventory, a record is inserted into GMF_LOT_COSTS table with the "ONHAND_QTY" column populated with the Onhand quantity of the Item/Lot and Inventory organization determined by the last run "Lot Actual Cost Process" and the "UNIT_COST" column populated with the "Actual Lot Cost". The actual lot cost of an Inventory Item is calculated based on the unit cost of the lot received in the inventory.

There can be situations where 2 lot cost records with the exact same cost_date. In this situation the MAX(HEADER_ID) is used to calculate the "Actual Lot Cost" .

Following function will always give the "Actual Lot Cost" for an Item/Lot, Warehouse combination when the costing method is "Lot Costing":

CREATE OR REPLACE FUNCTION xx_acc_cst (p_item_id NUMBER, p_org_id NUMBER)
   RETURN NUMBER
IS
   v_organization_id   NUMBER := p_org_id;
   v_item_id             NUMBER := p_item_id;
   v_cost_type_id     NUMBER := 1001;
   l_unit_cost           NUMBER;
BEGIN
   SELECT SUM (onhand_qty * unit_cost) / SUM (onhand_qty)
     INTO l_unit_cost
     FROM (SELECT onhand_qty, unit_cost,
                  RANK () OVER (PARTITION BY lot_number ORDER BY cost_date DESC,
                   header_id DESC) AS RANK
             FROM gmf_lot_costs
            WHERE cost_date <= (SELECT SYSDATE
                                  FROM DUAL)
              AND inventory_item_id = v_item_id
              AND organization_id = v_organization_id
              AND cost_type_id = v_cost_type_id)
    WHERE RANK = 1;
   RETURN l_unit_cost;
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;
END xx_acc_cst;

-- Actual Lost cost

Note: You can uncomment and pass the parameters in the below query if you want data for a

        specific Item,Inventory Organization, Period, Legal Entity etc.,

-- Query to derive Lost cost(Item, Inventory Organization, Lot combination)
SELECT   inv_val.item_number, inv_val.period_num, inv_val.inventory_item_id,
         inv_val.organization_code,
         SUM (inv_val.primary_quantity) primary_quantity,
         inv_val.primary_uom_code,
         SUM (inv_val.secondary_quantity) secondary_quantity,
         inv_val.secondary_uom_code, inv_val.description, inv_val.transdate,
         inv_val.organization_name, inv_val.subinventory_code,
         inv_val.subinv_description, inv_val.organization_id,
         inv_val.dual_uom_control, inv_val.actual_cost
    FROM (SELECT iim.item_number, oap.period_num, iim.inventory_item_id,
                 mp.organization_code,
                 NVL ((gpb.primary_quantity), 0) primary_quantity,
                 iim.primary_uom_code,
                 NVL ((gpb.secondary_quantity), 0) secondary_quantity,
                 iim.secondary_uom_code, iim.description,
                 (oap.schedule_close_date + (86399 / 86400)) transdate,
                                                              /* Bug 9819391*/
                 haou.NAME organization_name, gpb.subinventory_code,
                 msi.description subinv_description, mp.organization_id,
                 iim.dual_uom_control,
                 xx_acc_cst (iim.inventory_item_id,
                             iim.organization_id
                            ) actual_cost -- Function call to fetch accurate Lot Cost
            FROM mtl_item_flexfields iim,
                 mtl_parameters mp,
                 gmf_period_balances gpb,
                 org_acct_periods oap,
                 hr_all_organization_units haou,
                 hr_organization_information hoi,
                 mtl_secondary_inventories msi
           --   gmd_org_access_vw oa
          WHERE  '2' = '2'
             AND iim.inventory_item_id = gpb.inventory_item_id
             AND iim.organization_id = gpb.organization_id
             AND iim.organization_id = mp.organization_id
             AND mp.organization_id = gpb.organization_id
             AND hoi.org_information2 = :p_legal_entity_id
             AND hoi.organization_id = mp.organization_id
             AND hoi.org_information_context = 'Accounting Information'
             AND haou.organization_id = hoi.organization_id
             AND haou.organization_id = mp.organization_id
             AND mp.organization_id = oap.organization_id
             AND gpb.acct_period_id = oap.acct_period_id
             AND oap.period_year = NVL (:p_period_year, oap.period_year)
             --AND oap.period_num = :p_period_num
             AND gpb.subinventory_code(+) = msi.secondary_inventory_name
             AND msi.asset_inventory = 1                       /* B 8439947 */
             AND mp.organization_id = msi.organization_id
             --AND iim.item_number = NVL(:P_ITEM_CONCAT,iim.item_number)
             --AND mp.organization_code = NVL(:P_ORG_CODE,mp.organization_id)
             --AND iim.organization_id = oa.organization_id
         ) inv_val
   WHERE inv_val.item_number = NVL (:p_item_concat, inv_val.item_number)
     AND inv_val.organization_code = NVL (:p_org_code, inv_val.organization_code)
GROUP BY inv_val.item_number,
         inv_val.period_num,
         inv_val.inventory_item_id,
         inv_val.organization_code,
         inv_val.primary_uom_code,
         inv_val.secondary_uom_code,
         inv_val.description,
         inv_val.transdate,
         inv_val.organization_name,
         inv_val.subinventory_code,
         inv_val.subinv_description,
         inv_val.organization_id,
         inv_val.dual_uom_control,
         inv_val.acc_cost
ORDER BY 3, 1, 11;