3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 1/6 OPM Inventory Balance Reconciliation (Doc ID 1510357.1) Modified: 22-Feb-2014 Type: TROUBLESHOOTING In this Document Purpose Troubleshooting Steps NOTE: The scripts mentioned in this note are applicable only for OPM Financials Users of R12.1 or higher. 1. Steps for Inventory Valuation Report Vs OPM Accounting Pre-Processor 2. Steps for Inventory Valuation Report Vs OPM Sub-Ledger (Create Accounting) 3. Steps for OPM Sub-Ledger (Create Accounting) Vs General Ledger (GL) 4. Steps for Item-wise Difference - Analysis References APPLIES TO: Oracle Process Manufacturing Financials - Version 12.1.1 to 12.1.3 [Release 12.1] Information in this document applies to any platform. PURPOSE The purpose of this note is to explain the approach and techniques to be followed/used for reconciliation betweenItem wise inventory valuations with Inventory account balances for the period in Trial balance. This document has procedures and steps to be followed to do reconciliation at various stages of OPM period end processes. TROUBLESHOOTING STEPS NOTE: The scripts mentioned in this note are applicable only for OPM Financials Users of R12.1 or higher. Please choose the reconciliation stage which is applicable to your situation. 1. Steps for Inventory Valuation Report Vs OPM Accounting Pre- Processor NOTE: We advise you to complete the OPM Accounting Pre-Processor program for all OPM process categories/ sources and proceed with the following action before processing create accounting in draft mode. A. Match Inventory valuation report value with OPM sub ledger staging table (ie gmf_xla_extract_headers and gmf_xla_extrat_lines tables) after completing OPM Accounting Pre-Processor Program. B. Make sure that you run Inventory Valuation Report with parameter of âCurrent balanceâ set to âNoâ. Once you choose this, fields of period Year and period number gets active. C. How to do reconciliation? Run Inventory valuation report for the prior period â Note down the grand total Run Inventory valuation report for the current period â Note down the grand total Difference between prior period total value and current period total value is net movement of inventory value for the month Tally the movement of inventory value with OPM accounting Pre-Processor table value for the journal line types âINVâ, âPPVâ. Run the following script to find out the journal line type wise amounts from OPM Accounting Pre-Processor table. 1.1 select el.journal_line_type,sum(el.base_amount)from gmf_xla_extract_lines el,gmf_xla_extract_headers eh where el.header_id = eh.header_id and eh.transaction_date >= to_date('01-mar-2009 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to first date of the current period and eh.transaction_date 3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 2/6 and eh.organization_id = group by el.journal_line_type Check and confirm the net net movement of inventory value for the month is tally ing with the net difference of (entered_dr / entered_cr) and accounted_dr / accounted_cr) data for the journal line type 'INV' and 'PPV' for ISO transactions. The total of Journal line type âINVâ + âPPVâ should tally with net movement of inventory value for the month. If there is a difference, proceed with opmrecon.sql and validation steps available in this document. If there is No difference, proceed further with processing create accounting in draft mode. 2. Steps for Inventory Valuation Report Vs OPM Sub-Ledger (Create Accounting) The difference between prior period total value and current period total value of inventory valuation Reports (i.e.) net movement of inventory value for the month should tally with the OPM Sub- Ledger tables(xla_ae_headers and xla_ae_lines)for the account classes âInventory Valuationâ and âPurchase Price Varianceâ How to do reconciliation? Note down the difference between prior period total value and current period total value (i.e.)Net movement of inventory value for the month Run the following script to note down the amount accounted for the accounting class âInventory Valuationâ and âPurchase Price Varianceâ 2.1 select xle.code_combination_id,xle.accounting_class_code, sum(xle.entered_dr), sum(xle.entered_cr), sum(xle.accounted_dr), sum(xle.accounted_cr) from xla_ae_lines xle, xla_ae_headers xlh, gmf_xla_extract_headers eh where xle.ae_header_id = xlh.ae_header_id and xlh.event_id = eh.event_id and xle.application_id = 555 and xle.accounting_class_code in ('INVENTORY_VALUATION', 'PURCHASE_PRICE_VARIANCE') and eh.transaction_date >= TO_DATE('01/01/13 00:00:00','dd/mm/yy hh24:mi:ss')-- Change to first date of the current period and eh.transaction_date 3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 3/6 where xle.ae_header_id = xlh.ae_header_id and xlh.event_id = eh.event_id and xle.application_id = 555 and xle.accounting_class_code in ('INVENTORY_VALUATION', 'PURCHASE_PRICE_VARIANCE') and eh.transaction_date >= TO_DATE('01/01/10 00:00:00','dd/mm/yy hh24:mi:ss') -- Change to first date of the current period and eh.transaction_date = TO_DATE('01/01/13 00:00:00','dd/mm/yy hh24:mi:ss') -- Change to first date of the current period and eh.transaction_date = TO_DATE('01/01/13 00:00:00','dd/mm/yy hh24:mi:ss') -- Change to first date of the current period and eh.transaction_date 3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 4/6 NOTE: After performing all the above actions, if still difference is observed, proceed further with analyzing item-wise difference using âOPMRecon.sqlâ available in this note. How to do reconciliation? Step 1: From the output of inv_val_cur, sum of column TOTALCOSTCF should match with Inventory valuation report Value of Current period. (INVCurrent period ) Step 2: From the output of inv_val_pri, sum of column TOTALCOSTCF should match with Inventory valuation report Value of Prior period. (INVPrior period) Step 3: The Difference between the above two values should match with the Value of INVNet. ( INVCurrent period - INVPrior period = INVNet.) Step 4: From the output of subledger_values_cur, sum of column sub_val_cur should match with Subledger value of current period (ie) from OPM sub-ledger staging tables â gmf_xla_extract_headers and gmf_xla_extract_lines. (Subledger ValueCurrent Period) Step 5: From the output of problematic_items, sum of column item_value_diff should match with ReconDifferenceINVOPM =Subledger ValueCurrent Period - INVNet. Step 6:If the number of items returned in the table problematic_items are abnormally high then it is advisable to undo and redo of create accounting. Had you done posting to GL already those opm related GL batches needs to be reversed as well. For the guidance on undoing and redoing make sure that you involve Oracle Support/Development. Step 7: Pick up an item from the output of OPMrecon.sql (problematic_items tab)having a huge value as difference and do further analysis through the following scripts / action plans: Item wise analysis - Scripts 4.1: SELECT SUM(a.transaction_quantity), SUM(a.primary_quantity) FROM mtl_material_transactions a, MTL_SECONDARY_INVENTORIES b WHERE b.organization_id = a.organization_id AND b.secondary_inventory_name = a.subinventory_code AND a.inventory_item_id = AND a.organization_id in () AND NVL(a.logical_transaction,-1) 1 AND NVL(a.owning_tp_type,2) = 2 AND NVL(b.quantity_tracked,1) = 1 AND a.transaction_date >= TO_DATE('01-Jan-2000 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to very first date of the mtl_material_transaction(MMT) AND a.transaction_date 3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 5/6 4.4: SELECT SUM(a.transaction_quantity), SUM(a.primary_quantity) FROM mtl_material_transactions a, MTL_SECONDARY_INVENTORIES b WHERE b.organization_id = a.organization_id AND b.secondary_inventory_name = a.subinventory_code AND a.inventory_item_id = AND a.organization_id in () AND NVL(a.logical_transaction,-1) 1 AND NVL(a.owning_tp_type,2) = 2 AND NVL(b.quantity_tracked,1) = 1 AND a.transaction_date >= TO_DATE('01-Jan-2000 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to very first date of the mtl_material_transaction(MMT) and transaction_date = TO_DATE('01-Jan-2013 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to first date of the current period and transaction_date 3/26/2014 Document Display https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=13qc0h96qt_9 6/6 where header_id in (select header_id from gmf_xla_extract_headers where inventory_item_id = and organization_id in () and transaction_date >= TO_DATE('01-Jan-2013 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to first date of the current period and transaction_date = TO_DATE('01-Jan-2013 00:00:00','dd-mon-yyyy hh24:mi:ss') -- Change to first date of the current period and transaction_date