INVENTORY PERIOD CLOSE AND RECONCILIATION IN ORACLE APPLICATIONS

INVENTORY PERIOD CLOSE AND RECONCILIATION IN ORACLE APPLICATIONS (E-BUSINESS SUITE RELEASE 12) – DISCRETE MANUFACTURING ORGANIZATION

Purpose: The purpose of this article is to explain the Inventory period closure and reconciliation process in an easy step-by-step fashion in Oracle Applications (E-Business Suite Release 12). The document describes the pre-requisites, sequence of activities to be performed, important diagnostics and reconciliation procedure towards Inventory Period Closure for a Discrete Manufacturing Organization in Average Costing environment.

Download as White Paper “Inventory Period Close and Reconciliation” or read on..

Note: Inventory Period Closure needs to be performed for each Inventory Organization and once a period has been closed in Oracle inventory, it cannot be reopened.

Pre-requisites: Following prerequisite(s) should be ensured before proceeding ahead with Inventory Period Closure.

A. Payables and Purchasing periods are closed

·         Purchasing period closure is required to ensure that all PO transactions (PO Receipt, Return to Vendor, Receipt adjustment etc.) are processed for the current period

·         Payables period closure is required to ensure that all Purchase invoices are entered and accounted for current period. This will record all Invoice Price variances (IPV) and Exchange Rate Variances (ERV) which might be required to update inventory valuation.

Note: Purchasing Period Closure should be done subsequent to Payables closure if “Accrue Expense Items” setup parameter is set to “Period End” (Navigation:  Purchasing Superuser ->  Setup -> Organization -> Purchasing Options)

B. Ensure all transactions have been entered for Inventory Organization

i.  All Inventory transactions (issue, receipt, transfer and adjustment) have been entered and there are no transactions which are yet to be entered in Oracle

ii. There are no records in Transaction interface tables which are yet to be processed.

·         MTL_TRANSACTIONS_INTERFACE (Material Transactions)

·         MTL_TRANSACTIONS_LOT_INTERFACE (If Lots are used)

·         MTL_SERIAL_NUMBERS_INTERFACE (If Serial Nos. are used)

·         MTL_RESERVATIONS_INTERFACE (Material Reservations)

·         RCV_TRANSACTIONS_INTERFACE (PO Receiving transactions)

·         WIP_JOB_SCHEDULE_INTERFACE (Discrete Jobs )

·         WIP_JOB_DTLS_INTERFACE (Discrete Job Details)

·         WIP_MOVE_TXN_INTERFACE, WIP_SERIAL_MOVE_INTERFACE (WIP Move transactions interface)

·         WIP_SERIAL_MOVE_INTERFACE (If Serial Nos. are used)

·         WIP_TIME_ENTRY_INTERFACE (WIP Resource Transaction Interface)

·         MTL_TXN_COST_DET_INTERFACE (In case of Average Cost Update transactions)

iii. Invoice Price Variance (IPV) transfer to inventory (If required): Transfer recorded IPV for current Inventory Organization through “Transfer Invoice Variance” program (Navigation: Cost Management àItem Costs à Average Cost Update à Transfer Invoice Variance). IPV can be transferred for specific items or all items. The programs looks for IPV recorded against each item within the date range for current inventory organization and then posts an “average cost update” transaction to Transaction open interface. Inventory IPV account should be selected as adjustment account against which cost adjustment would be carried out.

iv. Exchange Rate Variance (ERV) transfer to inventory (If required): “Transfer Invoice Variance” process explained above is not applicable to ERV. There is an enhancement request logged with Oracle towards the same which is under development. “Average Cost Update” transaction would need to be carried out manually to load ERV to inventory valuation.

v. Average Cost Update transactions (if required): Perform “Average Cost Adjustment” transaction (Navigation: Cost Management à Item Costs à Average Cost Update à Update Costs) if average unit cost needs to be altered due to any reason.

vi. Job Closure of completed WIP Jobs: Discrete Job closure records WIP Variances in WIP Value summary and ensures that no transactions can be carried out for completed jobs. Any difference between Job’s  “Cost incurred” and “Costs Relieved” value is posted to WIP variance in WIP Accounting Class  (Navigation: Work in Processà Discrete à Close Discrete Jobs)

vii. Project Cost Transfers have been processed: There should be no transactions for which Project Cost Collection has not taken place. The best way to ensure this is to review material transactions (Navigation: Cost Management à View Transactions à Material Transactions). There should be no transactions with “Transferred to Projects” status as “No” or “Error”. This parameter is one of the filter criterions in Find Material Transactions form.

viii. Ensure that COGS Recognition events have been processed for all shipped out orders: Three programs must be run to ensure that all COGS recognition events are generated for shipped out sales orders in a period:

·         Record Order Management Transactions: This process records all sales order transaction activity (shipments / RMA returns) in Oracle Order Management

·         Collect revenue recognition Information: This process determines the percentage of recognized or earned revenue related to invoiced sales order shipment lines in Oracle Receivables

·         Generate COGS Recognition Events: This process generates COGS Recognition events for new sales order shipments/returns and changes in the percentage of earned revenue. COGS Recognition events credits/debits Deferred COGS/COGS accounts in order to maintain the ratio of deferred COGS to actual COGS in same proportion as unearned revenue to earned revenue.

Note: These three requests are run at Ledger level. It is recommended to create a Request Set of these requests and scheduled on daily basis to keep COGS Account value accurate.

Run “COGS Revenue Matching Report” to review that COGS Recognition events have been generated for all shipped out orders i.e. Ratio of   deferred COGS to actual COGS is same as the ratio of unearned revenue to earned revenue recognition.

ix. “Create Accounting Program for Cost Management” must be run: It is recommended to schedule “Create Accounting” program on daily basis to keep GL account value in sync with sub-ledger values. Though this program should be run once before period closure to ensure all accounting entries are passed onto GL (in case some transaction were recorded after last run). For more information on Create Accounting program and Accounting flow refer Oracle Note 755943.1

C. Period Close Diagnostics

Perform period close diagnostics to identify if there are some transactions which might prevent inventory period closure. Diagnostics can be performed in two ways:

i. Review Pending transactions against current period in Inventory Accounting Periods form. (Navigation: Cost Management à Accounting Close Cycle à Inventory Accounting Periods). The form shows the no. of transactions under “Resolution required” and “Resolution recommended” heads. Transactions under “Resolution required” category are the transactions which should be resolved before period can be closed. Transactions under “Resolution recommended” category are the transactions which are recommended to be resolved before period closure but in would not prevent the period closure; these transactions are processed in next accounting period subsequent to current period closure. This form also shows unprocessed shipping transactions.

ii. Run “Period Close Pending Transactions Report”. This Report can be run for “Resolution required”, “Resolution recommended” or both categories.

Error Resolution will depend on the nature of error. There are several documents available on Oracle Metalink describing error resolutions for some common errors. For others, Oracle SR might be required for resolution.

Note: It is recommended to perform the period close diagnostics frequently (e.g. weekly) to identify errors at an early stage and hence ensuring smooth period closure. This activity is performed at inventory organization level. For more information on Reviewing and fixing period close diagnostic errors refer Oracle Note 603657.1

D. Reconcile Inventory balances

i. Run “Period Close Reconciliation Report” in simulation mode:this report displays the differences between accounted value and inventory value as discrepancy. Accounted value is calculated using previous adjusted summarization data and distribution information from MTA (MTL_TRANSACTION_ACCOUNTS) table. On-hand value is calculated using current values from MOQ (MTL_ONHAND_QUANTITIES) and MMT (MTL_MATERIAL_TRANSACTIONS) tables. Discrepancy is reported as difference between the two. For more information on “Period Close Reconciliation” report and resolving discrepancies referOracle Note 295182.1

ii. INV-GL Reconciliation

Inventory valuation reconciliation is a critical step to ensure that balance of valuation accounts (Navigation: Inventory Management -> Setup -> Organizations -> Parameters) is representing the accurate inventory value. There should be no difference between Inventory values (Inventory Value reports from sub-ledger and accounted value in Valuation Accounts in General Ledger). Following table can be used as reference for this reconciliation activity.

Index	Report	Inventory Organization(s)
		ORGA	ORGB
	All   Inventories Value Report – By Cost Group
A	Inventory   Stock Value	200000	100000
B	In-transit   Inventory Value	20000	10000
	“Account   Analysis Report”  in General   Ledger
C	Material   Account	140000	70000
D	Outside   Processing Account	30000	15000
E	Material   Overhead Account	10000	5000
F	Overhead   Account	0	0
G	Resource   Account	20000	10000
H	Total   Inventory Value (C+D+E+F+G)	200000	100000
I	In-transit   Inventory Account	20000	10000
J	Inventory   Value Discrepancy (H-A)	0	0
K	In-transit   Value Discrepancy (I-B)	0	0

Note(s)

1. If Inventory Organization is not reflected in one of the account segments, then report total from General Ledger should be matched with sum total of Inventory value reports for all organizations associated with the Ledger.

2.  In-transit value is shown as part of Inventory Organization’s inventory value based on FOB (Free on Board) point defined for inter-org transfer (Navigation: Inventory Management -> Setup -> Organizations ->Shipping Networks). In-transit inventory is owned by either the shipping organization, FOB receipt, or the receiving organization, FOB shipment.

3. WIP (Work in Process) Value Reconciliation: If discrete manufacturing is used,

WIP reconciliation can also be done in a similar fashion as Inventory. “WIP value Report” shows the details of cost incurred and relieved to WIP accounting classes at inventory org level and balances should be compared with account analysis report at GL as was done in case of Inventory reconciliation.

iii. Resolving INV-GL Reconciliation discrepancies (if any)

Oracle Note 1447211.1 explains and provides diagnostic scripts for easy identification of errors/issues leading to mismatch between GL and INV values. Following is a quick summary of steps to be performed for identification of reconciliation issues:

·         Check back-dated transactions: Back dated transactions can lead to discrepancies between GL and sub-ledger values.

·         Check mismatch in quantities between MMT and MOQ tables

·         Check mismatch in cost details between MMT and CQL tables

·         No Negative ledger Id’s exist at the Legal Entity level

·         Run “Sub-ledger Period Close Exception Report” for journal source as “Cost Management”: This report lists all accounting events and journal entries that fail period close validation for GL. The report displays all error/unprocessed/un-transferred recordsand can also be run at journal category level. If records exist in this report Run “Transfer Journal Entries to GL – Cost Management” to ensure that all pending journal entries are transferred to GL, then run the report again and review.

·         If differences still persist, Run “Recon_diag.sql” script (Refer Oracle Note. Script generates two outputs, “INV_SLA_RECON.TXT” and “SLA_GL_RECON.TXT” to provide the detailed information about mismatch between INV-SLA-GL.

E. Close Accounting Period

Once GL and INV sub-ledger values match, close Inventory Period (Navigation: Inventory Management à Accounting Close Cycle -> Inventory Accounting Periods -> Change Status -> “Closed”).

Stay updated with recent posts and content on my blog, follow me on Twitter: @msinghal82

Email: msinghal.iitk@gmail.com