How to Adjust Average Cost with Invoice Price Variances (IPV)

If you want to get your inventory cost, and ultimately your cost of goods, to reflect the actual cost you paid for your items, then you will want to interface the Invoice Price Variance (IPV) from Oracle Payables to Oracle Inventory/Cost Management.  The ability to perform this update of inventory cost is only for inventory organizations using the average cost costing method.  To understand this process, let’s look at the flow of cost from PO receipt to Transfer of Invoice Variances.  Here’s an overview of each step:

1.       Create and approve a PO

2.       Receive the item

3.       Enter and match an AP invoice (release any holds if necessary)

4.       Generate accounting for the AP invoice

5.       Transfer invoice variances to Inventory

Oracle IPV transfer flow from Payables to Inventory

Step 2 in the process (PO receipt) sets the initial average cost.  This cost will be used on all issues or shipments out of inventory.  Remember in average costing, we receive at PO price and issue out at average.

Once steps 3 (enter and match an AP invoice) and 4 (generate accounting) are complete, we are ready to run the Transfer Invoice Variance to Inventory program.  You can run the program from Cost Management for one inventory organization at a time.  This program will sum the difference between the invoice price and the PO price for each item/organization combination and then create an average cost update transaction.  This transaction will have an amount but not a quantity.  This amount is then applied to the remaining inventory on-hand.  So let’s look at a couple of examples and how your average cost will change.

Example 1:

• PO Price $10
• Receipt Quantity 100
• Invoice Price $12
• On-Hand 100
• Beginning Average Cost $10
• Ending Average Cost $12

In this example, we will apply the IPV of $2 to all 100 units in inventory.  So the average cost before the IPV transfer is $10 and the average cost after the IPV transfer is $12.  This would correctly value our inventory at actual cost.

Example 2:

• PO Price $10
• Receipt Quantity 100
• Invoice Price $12
• On-Hand 10 (sold 90 units)
• Beginning Average Cost $10
• Ending Average Cost $30  (($200/10) + $10 = $30

In this example, we will apply the IPV of $2 to remaining 20 units in inventory.  So the average cost before the IPV transfer is $10 and the average cost after the IPV transfer is $30.  This would result in lower margins the next time we sell and ship this item.

Example 3:

• PO Price $10
• Receipt Quantity 100
• Invoice Price $12
• On-Hand 0 (sold 100 units)
• Beginning Average Cost $10
• Ending Average Cost $10

In this example, we wouldn’t apply the IPV of $2 because the on-hand quantity is zero.  So the average cost before the IPV transfer is $10 and the average cost after the IPV transfer would also be $10.

A guide to Inventory Period Closing activities and Inventory-SLA-GL Reconciliation

This white paper aims at providing troubleshooting guide for the Inventory period closing activities and Gl reconciliation issues which is faced after the Inventory period is closed.

Intention of the white paper is to provide the effective guidelines and some handful scripts which can be used in checking various aspects of period closing as well as Reconciliation between sub-ledger and General Ledger (GL).

Following Pre-checks are important before attempting to reconcile the value at Sub-ledger for a given account code combination with the value of that account code combination reflecting in GL.

The scripts mentioned are not applicable for Periodic Costing System (PAC).

This is applicable for R12 using Standard and/or Average Costing.

Suitable for Oracle seeded Account Derivation Rules (ADR).

NOTE : The script contained in the attachment section is being updated periodically. Please download it from Metalink every time you need to run it in order to have the latest version.

SUMMARY

 1. All Material/WIP transactions should be costed.

This is the primary step in the process of Inventory Period close and ultimately in Reconciliation. If there are any errors in the respective tables or Cost worker errors those need to be resolved. To check for these errors, diagnostics scripts are available,

Please refer to the  Note 603657.1 -Error/Uncosted/Pending Material Transactions

2. Create Accounting-Cost Management request.

After checking all the transactions for the concerned Inventory period are costed, Create Accounting-Cost Management concurrent request should be run to transfer all the transactions to GL.

This concurrent request should get successfully completed.

For request parameters and the explanations for each of the parameters and the associated script :

Please refer to the Note 755943.1 -Transfer Inventory Transaction Into General Ledger through Subledger Accounting SLA Flow

 3. Inventory Period should be closed.

Once Create Accounting-Cost Management concurrent request is completed successfully, Inventory period should be closed. While closing the Inventory Period, a report will get triggered called as "Period Close Reconciliation Report".

This concurrent program and report is used to create summarized transaction records. It displays the differences between accounted value and inventory in the Discrepancy column.

Following are key columns in Period Close Reconciliation Report:

Accounted Value: The valuation when calculated using previous adjusted summarization data + distribution information from MTA (Mtl_transaction_accounts).

On-hand Value: The valuation when calculated using current values from MOQ - quantities and costs from MMT. (Mtl_material_transactions).

Discrepancy: The difference between Account Value and On-hand Value.

Once the Period Close Reconciliation report completes normal, please check the report values and especially Discrepancy column. If there is a difference, this is the first indicator of mismatch in the Sub-ledger and Gl. In R12, you can run the Period Close reconciliation report for the closed period as well.

For more discussions on the Period Close Reconciliation Report,

Please refer to the  Note 295182.1 -Period Close Reconciliation Report (CSTRPCRE)

 4. Backdated Transactions.

Timely closing of Inventory period will prevent the backdated transactions which will have its impact on Inventory valuation at the sub-ledger level as well as in the Gl. This is the primary factor which creates a difference in Sub-ledger and Gl.

Please refer to the Note 1578694.1 -Identifying Inventory Backdated Transactions.  

 5. Mismatch like MMT-MOQD and MMT-CQL.

There is mismatch in quantities in different sub-ledger tables like Mtl_material_transactions (MMT) and Mtl_on_hand_quantities (MOQ). This can also lead to sub-ledger level difference and discrepancy in Period close reconciliation report.

For MMT-MOQ mismatch and the resolution thereof

Please refer to the Note  279205.1 -Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and MTL_ONHAND_QUANTITIES_DETAIL

 In average costing Environment, there can be MMT-CQL mismatch,  

Please refer to the Note  378348.1 which provides txn.sql to identify MMT-CQL mismatch.

 6. No negative ledger Id's exists at the legal entity level.

This is the next level check in the SLA. If there are negative ledger id’s for the gl batches then these needs to be rectified and resolved. There is a diagnostics and Data fix which is available to resolve.

Please refer to the Note 883557.1 -How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL

 7. Sub-ledger Period Close Exception Report.

From Cost Management SLA Responsibility, Sub-ledger period close exception report states the entities/events which are errored while transferring to Gl. This report is very handy so to know the first level check whether there are any transactions not transferred to Gl.

8. From General Ledger(GL) responsibility, Account Analysis Sub-Ledger report 180 Characters report will be useful to get the data for the account code combination.

9. Recon_diag.sql

After completing the above pre-checks, If there is difference in the GL and Inventory, final step is to start the reconciliation process.

Recon_diag.sql is attatched.

In Recon_diag.sql there are 5 input values which need to be provided.

1)     Org code= The inventory organization for which reconciliation activity is going on.

2)      Reference account = the account code combination in question for which Gl and sub-ledger values are not matching.

3)      from date = The inventory period, or date in which value mismatch is there.

4)     to date= The inventory period, or date in which value mismatch is there.

5)     Ledger_id = Enter the Ledger_id of the default ledger.  This can be obtained from the gl_ledgers  table.

(For 3 & 4 , pl run the script on monthly basis. Ideally if you are reconciling the April-2012 period, then condition would be
from date=01-apr-2012 to date=30-apr-2012)

After running the Recon_diag.sql two text files will be created in users default locations.
File names will be SLA_GL_RECON.TXT and INV_SLA_RECON.TXT

 The output will gather data from the Sub-ledger tables like Mtl_transaction_accounts and Wip_transaction_accounts in INV_SLA_RECON.TXT.

Data from SLA and GL tables like Xla_transaction_entities, Xla_Ae_Headers and Xla_ae_lines   and Gl_je_lines, GL_Je_batches, Gl_import_references is gathered inSLA_GL_RECON.TXT.

Error/Uncosted/Pending Material Transactions

Below mentioned set of script is specifically used to identify any pending or uncosted transactions which stops the Inventory period closing or Cost Manager activities of costing the transactions.

These scripts are best suited for any Inventory Organization which is using "Average Costing method." 
On the basis of the script output, further analysis can be made which will resolve the errors which will be output from these scripts.

1)  This script output will give the number of error transactions that need to be resolved.  Without resolving these transactions, cost manager will not cost the transactions which are uncosted.  This list will be for all the Inventory Organizations in that legal entity.

Select * from Mtl_material_transactions where costed_flag = 'E'

2)  This script outputs the list of uncosted transactions.

Select count (*) from Mtl_material_transactions where costed_flag = 'N'

3)  This script output gives transactions which are pending for costing . These are pending resource transactions. These transactions also will not get costed unless and until ,errored transactions are costed.

Select * from wip_cost_txn_interface

4)  This script outputs the list of pending uncosted transactions.

Select count (*) from wip_cost_txn_interface

5)  This script output will generate error code and error explanations for the errored resource transactions. The same can be seen from the Pending Resource Transactions form from the WIP responsibility.

Select * from wip_txn_interface_errors

6)  This script output gives the list of transactions for the errored resource transactions which are stuck in the wip interface table.

Select *
from wip_txn_interface_errors
where transaction_id IN ( Select transaction_id from wip_cost_txn_interface)

7)  This script should give the details about the errored resource transactions . This will state the Wip_entity_id , organization_id and process_status and process_phase of the concerned transactions.

Select *
from wip_cost_txn_interface
where transaction_id in (Select transaction_id from wip_txn_interface_errors)

8)  This script output retrieves the list of errored transactions in the Inventory module at overall level.

Select *
from mtl_material_transactions_temp
where error_code is not null and error_explanation is not null

9)  This script outputs the list of pending material transactions.

Select count (*) from mtl_material_transactions_temp

10)  Output of diagnostics script, CstCheck.sql (see Note 246467.1) is for diagnosing any cost management related issue.  Gives overall setup related information.

11)  To know the Cost manager status this script can be used.  Output of this script is Request id, phase code and Status code.

SELECT request_id RequestId,
request_date RequestDt,
phase_code Phase,
status_code Status FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name = 'CMCTCM' AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702 AND fcr.phase_code <> 'C'

Transfer Inventory Transaction Into General Ledger through Subledger Accounting SLA Flow

The goal of this document is to transfer Inventory transaction distributions into the General Ledger (GL) through Subledger Accounting (SLA) in release 12.

Cost Manager Costing Inventory Transactions

After any Inventory or WIP transactions , such as Misc Receipt , Average Cost Update , or Sub inventory Transfer transaction , Cost manager should cost these transactions and distributions should be seen in the Material Distributions form. (In the MT_Transaction_Accounts table).

Launch the Cost Manager
Inventory -- Setup -- Transactions -- Interface Managers --- Tools (Menu Bar) -- Launch Manager --Submit
(No scheduling for the Cost Manager should be done in the Request Form)

Run Create Accounting Program

In R12 , you need to run Create Accounting program

Cost Management  SLA 
Run Create Accounting -Cost Management

( Pl pass the following parameters while running create accounting program..)
Ledger = Appropriate Ledger name
Process Category =
End date = &date
Mode = Final /Draft
Errors only =Yes /No
Report = Detail/Summary
Transfer to General Ledger = Yes
Post in General Ledger = Yes /No
Include User Transaction identifiers =Yes

 

Explanation for the parameters used in the Create Accounting program.

Parameter	Description
Ledger	Primary ledger for which Create Accounting program has to be run.
Process Category	This determines the Transactions of which module like Inventory distributions, WIP distributions. If we set it at NULL , then it will take all the distributions.
End Date	Create Accounting program processes only those events with event dates on or before the end date. i.e , If we set the event date as 31-Aug-2008, All the events created till 31 Aug 2008 and which are not been transferred till now , will be set to transfer by the request triggered.
Mode	Mode determines whether the sub-ledger journal entries are created in Draft or Final Mode. If Draft mode is selected, then the Transfer to General Ledger, Post in General Ledger, and General Ledger Batch Name fields are disabled. Draft entries cannot be transferred to General Ledger. If it is Draft mode then accounting distributions can be amended in SLA.(if required)
Errors Only	This parameter limits the creation of accounting to those events for which accounting has previously failed. If we set this Parameter to YES , the it will try to process all the events which were in the Error status earlier.
Report	This shows the sub-ledger accounting distributions in Detail or Summary format (Whatever the format selected)
Transfer to General Ledger	This field will be enabled only when the Mode parameter is set to Final. If this column value is set to NO, then Journal import program will not be launched. We need to POST the distributions through General Ledger.
Post in General Ledger	If Transfer to General Ledger is set to YES , then only this field will be enabled. If you set column value of Post in General Ledger as YES , posting of General Ledger batches will be done.
Include User Transaction identifiers	If this column value is set at YES , then we can track transaction_id wise distributions in SLA and in General Ledger. This we value is always recommended to set as Yes.

Table Level tracking of Sub-ledger transaction distributions.

1. To Check the Inventory transaction(s) which are been passed to General Ledger through SLA 
   Select * from mtl_transaction_accounts where transaction_id =&transaction_id
   
   
2. To know the inv_sub_ledger_id which is been populated after running the Create Accounting program.. (which is completed normal) We need pass following parameters while running Create accounting program.
   End date = &date
   Mode = Final
   Errors only =No
   Report = Detail
   Transfer to General Ledger = Yes
   Post in General Ledger = Yes
   Include User Transaction identifiers =Yes
   (The request set with these parameters will post the transactions to General Ledger. )
3. Script name : xla_distribution_links 
   Select * from xla_distribution_links
   where source_distribution_id_num_1 in
   (select Inv_sub_ledger_id from Mtl_transaction_accounts
   where transaction_id =&transc_id)
   
   
   From the above script we will come to know the ae_header_id and event_id which can be used to track into the further tables.
4. Script name : xla_transaction_entities_upg
   Select * from xla_transaction_entities_upg
   where source_id_int_1 =&transc_id)
   and application_id =707
   
   From the above script we will come to know the ENTITY_ID and LEDGER_ID
   
   Below mentioned script output will state whether the corresponding transactions from Inventory Sub ledgers have been transferred to Gl or not.
5. Script name = xla_ae_headers
   Select * from xla_ae_headers
   where ae_header_id =&ae_header_id
   (from the script no 3 you will get the Ae_header_id )
   
   
   From the above script you will come to know , Accounting date and Gl_transfer_status_code and Gl_transfer_date.
   If the Gl_transfer_status_code is "Y" , then this transaction can be further tracked to Gl_IMPORT_REFERENCES and GL_JE_lines table.
   
   From XLA_AE_LINES table we will come to know transaction and code combination id wise tracking ..
   Here GL_TRANSFER_MODE_CODE is set at "D" == Detail
6. Script Name : xla_ae_lines
   Select * from xla_ae_lines
   where ae_header_id =&ae_header_id ( From the script no. 5)
   
   From the xla_ae_lines table we will come to know the Gl_SL_LINK_ID which can be used to track in the Gl.
7. Script Name :GL_IMPORT_REFERENCES 
   Select * from gl_import_references
   where gl_sl_link_table = 'XLAJEL'
   and gl_sl_link_id in (&GL SL_LINK_ID)
   
   From the GL_IMPORT_REFERENCES table we will get je_header_id which can be used as evidence as this transaction is been posted to GL.
8. Script Name : GL_JE_LINES 
   Select * from gl_je_lines where je_header_id = &JE_HEADER_ID ( From the Gl_import_references table) 
   
   
9. Script Name : SLA-GL join
   This script will join the SLA tables and Gl tables , and it can be confirmed from the output of this script whether the concerned Gl batch is been posted to General Ledger.
   SELECT DISTINCT glb.name
   FROM xla_ae_lines xle,
   gl_import_references gli,
   gl_je_batches glb
   WHERE ae_header_id = &ae_header_id
   AND xle.gl_sl_link_id = gli.gl_sl_link_id
   AND xle.gl_sl_link_table = gli.gl_sl_link_table
   AND gli.je_batch_id = glb.je_batch_id

From  Oracle Application

• Inventory Responsibility
  > Material Transactions
  > Material Distributions
  
• Cost Management SLA SLA > Inquiry from the menu.
  Select Accounting Events, Journal Entries, or Journal Entry Lines to open the sub-ledger accounting user interface
  
  Select a transaction and choose Tools from the tool bar. Select View Accounting
  Events and the Accounting Events User Interface appears.
  
  We can also choose View Journal Entries to view accounting and journal entry details.