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';
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';
No comments:
Post a Comment