Oracle Cost Management

Costing Reports and Reconciliation

Costing Reports and Reconciliation

When closing an accounting period the ‘Close accounting period’ concurrent process (INCTPC) is kicked off as well as the ‘Transfer transactions to GL’ concurrent process (INCTGL). The ‘Close accounting period’ process summarizes the costs related inventory and manufacturing activities for a given account period. The ‘Transfer transactions to GL’ process distributes those cost to the general ledger.

Oracle provides a variety of reports for evaluating current inventory quantity and value as well as reconciling inventory and manufacturing transaction to what has been transferred to GL for a given period. The following is a list of the costing reports that should reconcile if run with the correct parameters dependent on the organization set up:

Period Close Reconciliation Report:
(Available 11.5.10, Executable CSTRPCRE) 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. The inventory value is used as the baseline for calculation for the next period summarization values. The Period Close Reconciliation report can be run at any time during the period or automatically during the Period Close Process by setting the profile option CST:Period Summary to either automatic or manual. If it is generated for an open period, you are creating a simulation, or snapshot of the period. If the program is run for an accounting period that is not in a Closed status, the report reads directly from a temporary table, The simulation status is indicated in the report title. (See Note 295182.1)

Standard Costing Organization, PJM/WMS Not enabled:
-Period Close Value Summary
-Inventory Value Report
-Subinventory Account Value Report
-Elemental Inventory Value Report
-Transaction Historical Summary report

Average Costing Organization, PJM/WMS Not enabled:-Period Close Value Summary (for organization level balance only)
-Elemental Inventory Value Report – Average Costing
-All Inventory Value Report – Average Costing
-Transaction Value Historical Summary-Average Costing

FIFO/LIFO Costing Organization, PJM/WMS Not enabled:
-Period Close Value Summary (if there are no unsummarized intransit balance or layer cost update, for organization level balance only)
-Elemental Inventory Value Report-Average Costing
-All Inventory Value Report-Average Costing
-Transaction Value Historical Summary-Average Costing

Standard Costing Organization, PJM/WMS enabled:
-Period Close Value Summary – Warehouse Management
-Inventory Value Report – Warehouse Management
-Cost Group Account Value Report
-Elemental Inventory Value Report – Warehouse Management
-Transaction Historical Summary Report (for organization level balance only) with option value ‘Roll back to first Date’ set to the last day of the current period.

Average Costing Organization, PJM/WMS enabled:
-Period Close Value Summary – Warehouse Management
-Elemental Inventory Value Report – Average Costing
-All Inventory Value Report-Average Costing
-Transaction Value Historical Summary-Average Costing (if there are no intransit transactions in another organization that affects the intransit quantity in this organization)

FIFO/LIFO Costing Organization, PJM/WMS enabled:-Period Close Value Summary-Warehouse Management (if there are no un summarized intransit balance)
-Elemental Inventory Value Report-Average Costing
-All Inventory Value Report-Average Costing
-Transaction Value Historical Summary-Average Costing (if there is no intransit transactions in another organization that affects the intransit quantity in this organization)

Reconciling Reports

-When reconciling reports insure that the correct report is being used depending on the organizational set up. Some customers do use other report combinations to reconcile and in some cases this is acceptable.

-Make sure that the reports (other than the Period Close Value Summary (-Warehouse Management), the Transaction Historical Summary Report and the Transaction Value Historical Summary-Average Costing) are run after all the transactions in the current period are costed and before transactions in the subsequent period are created.

-Back Dating Transactions: Oracle does allow customer to back date transactions for their business needs. Customers need to understand that back dating transactions can affect the balance when doing reconciliation but reconciliation issues can be avoided when back dating transactions by utilizing the Cost Cut off field in the Organization Parameter screen insuring that back dated transactions are costed in the correct sequence. The issue with back dating transactions is explained in the “PERIOD CLOSE SUMMARY ALGORITHM” document available at the url:
Please Contact Oracle Support
To verify if there have been back dated transactions, the following script can be run to check the type of transaction the dates and the periods affected:

SELECT DISTINCT
mmt1.transaction_id,
mmt1.transaction_date,
mcacd1.transaction_costed_date,
mmt2.transaction_id,
mmt2.transaction_date,
mcacd2.transaction_costed_date,
mmt1.inventory_item_id,
mmt1.cost_group_id
FROM org_acct_periods oap,
mtl_material_transactions mmt1,
mtl_material_transactions mmt2,
mtl_cst_actual_cost_details mcacd1,
mtl_cst_actual_cost_details mcacd2
WHERE oap.acct_period_id = &acct_period_id
AND mmt1.transaction_date
BETWEEN oap.period_start_date
AND oap.schedule_close_date
AND mmt1.inventory_item_id = mmt2.inventory_item_id
AND mmt1.cost_group_id = mmt2.cost_group_id
AND mmt1.transaction_date < mmt2.transaction_date
AND mcacd1.transaction_id = mmt1.transaction_id
AND mcacd2.transaction_id = mmt2.transaction_id
AND mcacd1.transaction_costed_date >
mcacd2.transaction_costed_date

(Receipt transaction may appear to be back dated transactions because they will not be time stamped and TRANSACTION_DATE will be null)

-Data corruption where onhand quantity does not match with the values in the MTL_MATERIAL_TRANSACTIONS can also cause reports not to reconcile. The following scripts can be run in an 11.5.10 or higher environment to see if this type of data corruption exists:

SELECT TXN.inventory_item_id,
TXN.mmt_qty ,
ONHAND.qty
FROM (SELECT mmt.inventory_item_id ,
SUM(mmt.primary_quantity) mmt_qty,
mmt.organization_id
FROM mtl_material_transactions mmt,
mtl_system_items msi ,
mtl_secondary_inventories mse
WHERE msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND mse.secondary_inventory_name= mmt.subinventory_code
AND mse.asset_inventory = 1
AND mse.organization_id = mmt.organization_id
AND mmt.transaction_action_id not in (5,6,24,30,50,51,52,55,26,7,11,17,10,9,13,14)
AND nvl(mmt.logical_transaction,0) <> 1
GROUP BY mmt.inventory_item_id,
mmt.organization_id
)
TXN ,
(SELECT moq.inventory_item_id ,
SUM(moq.primary_transaction_quantity) qty,
moq.organization_id
FROM mtl_onhand_quantities_detail moq,
mtl_system_items msi ,
mtl_secondary_inventories mse
WHERE msi.organization_id = moq.organization_id
AND msi.inventory_item_id = moq.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND mse.secondary_inventory_name= moq.subinventory_code
AND mse.asset_inventory = 1
AND mse.organization_id = moq.organization_id
GROUP BY moq.inventory_item_id,
moq.organization_id
)
ONHAND,
mtl_parameters mp
WHERE TXN.inventory_item_id = ONHAND.inventory_item_id (+)
AND TXN.mmt_qty <> NVL(ONHAND.qty,0)
AND mp.organization_id = &org_id
AND TXN.organization_id = mp.organization_id
AND ONHAND.organization_id = TXN.organization_id
AND NVL(mp.wms_enabled_flag,'N') = 'N';

How Subledger Accounting Event Classes Map to Inventory, Purchasing, and Cost Management Module Transactions

In Cost Management module, we have 31 event classes that can be divided into four categories.  The categories are material related, receiving related, work-order related, and write-off related.

Material Related Events

The material related events are based on transactions that can affect one or more of the following: an inventory item’s on-hand quantity, the inventory item’s value, or the inventory item’s item accounting.  The flow from the creation of the material transaction to the Subledger accounting has four steps.  The first step is the creation of the material transactions.  This step can be accomplished by using a form, by running a program or workflow, or by running an API.  In the second step of the flow, the Oracle Inventory module records material transactions in the Material Transaction form.  After the material transaction is recorded, the Cost Manager program performs the third step and calculates the cost and assigns the default accounting.  After the material transactions is costed and is assigned default accounting, the Create Accounting program creates the Subledger accounting entries.

Subledger Event Class	Transaction Entry Form or Program
Consigned Inventory Ownership Transfer	Consigned Transactions
Direct Interorg Receipt	Inter-organiztional Transfer
Direct Interorg Shipment	Inter-organiztional Transfer
Recipient-side Intransit Interorg Receipt for FOB Receipt	Receipt (internal order when the shipping network FOB terms = receipt)
Sender-side Intransit Interorg Receipt for FOB Receipt	Receipt (internal order when the shipping network FOB terms = receipt)
Intransit Interorg Shipment for FOB Receipt	Shipping  (internal order when the shipping network FOB terms = receipt)
Intransit Interorg Receipt for FOB Shipment	Receipt (internal order when the shipping network FOB terms = shipment)
Recipient-side Intransit Interorg Shipment for FOB Shipment	Shipping (internal order when the shipping network FOB terms = shipment)
Sender-side Intransit Interorg Shipment for FOB Shipment	Shipping (internal order when the shipping network FOB terms = shipment)
Intraorg Transfer	Subinventory Transfer
Internal Order to Expense	Shipping (internal order when the item is an expense item)
Logical Intercompany	Shipping or Receipt when intercompany transfer requires accounting entry but doesn’t require physical movement
WIP Material Lot	WIP Material Transactions
Miscellaneous	Miscellaneous Transactions
Material Cost Update	Average Cost Update, Standard Cost Update
PO Delivery into Inventory	Receipt (Receiving into an Asset Subinventory)
Retroactive Price Adjustment	Retroactive Price Update on Purchasing Documents
Sales Order Issue	Shipping (Ship confirm external  sales order)
User Defined Inventory Transaction Class	Based on transaction setups
WIP Material	WIP Material Transactions

Receiving Related Events

The receiving related events are based on transactions that affect the cost of a receipt, the accounting for the accrual of the receipt, and the initial accounting of the receipt from the supplier.

Subledger Event Class	Transaction Entry Form or Program
Delivery to Expense Destination	Receipt (Receiving into Expense Subinventory)
Landed Cost Adjustment to Delivery	TBD – New Module
Landed Cost Adjustment to Receipt	TBD – New Module
Period End Accrual	Period End Close Process
Receipt into Receiving Inspection	Receipt
Retroactive Price Adjustment to Delivery	Retroactive Price Update on Purchasing Documents
Retroactive Price Adjustment to Receipt	Retroactive Price Update on Purchasing Documents

Work-in-Process Related Events

The work-in-process related events are based on transactions that affect the value of the assembly as it moves through production.

Subledger Event Class	Transaction Entry Form or Program
WIP Absorption	Move Transactions
Outside Processing	Receipt (Outside Processing PO)
WIP Variance	Close Discrete Jobs, Period End Close for non-standard jobs
WIP Cost Update	Average Cost Update, Standard Cost Update
WIP Lot	Completion Transactions

Write-Off Events

The work-in-process related event uses the transactions generated from the Accrual Write-Off process.

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

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.