APPLIES TO:

Oracle Cost Management - Version 11.5.10.2 to 12.2.5 [Release 11.5 to 12.2]
Information in this document applies to any platform.

PURPOSE

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.

REQUIREMENTS

These scripts can be used on SQL Developer, SQL*PLUS

CONFIGURING

These scripts can be used on any instance to check whether there are pending and uncosted transactions.

INSTRUCTIONS

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'

CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

SCRIPT

As stated, output of all these uploaded scripts will help to diagnose and analyze the issue more quickly.

REFERENCES

NOTE:1069492.1 - Resolving Period Close Pending Transaction R12
NOTE:246467.1 - BDEprdcls.sql - CstCheck.sql Diagnostics Scripts
NOTE:865438.1 - How to resolve Costing Errors & Problems