1 Run cmclean.sql Note 134007.1 Non Destructive Script to Clean
Concurrent Manager Tables
2- Review Note 304313.1 Understanding Cost Manager
3. Run and review Note 310000.1 Oracle Cost Management Interface Managers Activity Test
4. Please do the following in this test instance where the issue is raised
a. Restart Internal Manager,Inventory Manager and Inventory Remote Procedure Manager
b. Perform gather statistics for INV, WIP, FND, and BOM schemas since these are the tables involved in
the costing
c. Stop cost manager and all cost workers
Stop the Cost Manager Note 373207.1
Please query up the Cost Manager using System Administrator Responsibility/Requests/View -
Then select Specific Requests - In the Name field type --> Cost Manager (Note this is case
sensitive) and select Find. Verify that there is only one cost manager running - make sure there
is not one that is Terminating (this means it's not fully canceled) and let us know what you
find. Note this is also the Navigation to Cancel the cost manager.
d. Make the profile options are set:
2- Review Note 304313.1 Understanding Cost Manager
3. Run and review Note 310000.1 Oracle Cost Management Interface Managers Activity Test
4. Please do the following in this test instance where the issue is raised
a. Restart Internal Manager,Inventory Manager and Inventory Remote Procedure Manager
b. Perform gather statistics for INV, WIP, FND, and BOM schemas since these are the tables involved in
the costing
c. Stop cost manager and all cost workers
Stop the Cost Manager Note 373207.1
Please query up the Cost Manager using System Administrator Responsibility/Requests/View -
Then select Specific Requests - In the Name field type --> Cost Manager (Note this is case
sensitive) and select Find. Verify that there is only one cost manager running - make sure there
is not one that is Terminating (this means it's not fully canceled) and let us know what you
find. Note this is also the Navigation to Cancel the cost manager.
d. Make the profile options are set:
MRP:Debug Mode to YES
MRP: Trace Mode to Yes
CST:Cost update Debug Level to FULL
MRP: Trace Mode to Yes
CST:Cost update Debug Level to FULL
e. Relink your CMCTCM (Cost Manager), CMCMCW ( Material Cost Transaction Worker), CMCACW (Actual Cost Worker), and CMCLCW ( Layer Cost Worker) executable. Refer to Note 69798.1 for tips on relinking an executable. See
Note 99768.1 AD Utilities: Functionality in Regards to Applications Maintenance
Note.131321.1 How to Relink Oracle Database Software on UNIX
Note.69798.1 Basics of Relinking an Executable or Binary in an Oracle
Developer Environment
f. Uncosted/failed transactions can be resubmitted through the application itself.
Costs -> View Transactions -> Material Transactions.
From the menu, Tools Select All: Tools Submit All
Else, use the following script to submit the records in MMT table:
update
mtl_material_transactions MMT
set MMT.costed_flag = 'N',
MMT.error_code = NULL,
MMT.error_explanation = NULL,
MMT.transaction_group_id = NULL,
MMT.transaction_set_id = NULL
where MMT.organization_id = &organization_id
and MMT.costed_flag in ('N','E')
and NOT EXISTS ( SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID);
set MMT.costed_flag = 'N',
MMT.error_code = NULL,
MMT.error_explanation = NULL,
MMT.transaction_group_id = NULL,
MMT.transaction_set_id = NULL
where MMT.organization_id = &organization_id
and MMT.costed_flag in ('N','E')
and NOT EXISTS ( SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID);
g.
commit;
h. Re-start the cost manager.
Restart the cost manager via Inventory > Setup > Transactions >
Interface Managers > Tools > Launch Manager. Set up a periodic scheduling interval of every five
minutes.
Note -- Cost Manager should not be scheduled through request form to run Periodically.
Cost Manger should be scheduled through Interface managers only.
5. Upload and review Cost Manager and Cost worker log,Raw and Tkprof Trace files
(See Note 199081.1 for questions on TKPROF and
Note 296559.1-- FAQ: Common Tracing Techniques
6. For the Organization and period in question, upload completed spooled file CstDiag115.txt after
running CstCheck.sql and output of BDEprdcls.sql Diagnostics Scripts (see Note 246467.1) If
prompted by customer id , just enter your company name.
7. R11i: Provide the output of the Inventory Period Closing Activity Test from Note 206576.1
Note:
Restart the cost manager via Inventory > Setup > Transactions >
Interface Managers > Tools > Launch Manager. Set up a periodic scheduling interval of every five
minutes.
Note -- Cost Manager should not be scheduled through request form to run Periodically.
Cost Manger should be scheduled through Interface managers only.
5. Upload and review Cost Manager and Cost worker log,Raw and Tkprof Trace files
(See Note 199081.1 for questions on TKPROF and
Note 296559.1-- FAQ: Common Tracing Techniques
6. For the Organization and period in question, upload completed spooled file CstDiag115.txt after
running CstCheck.sql and output of BDEprdcls.sql Diagnostics Scripts (see Note 246467.1) If
prompted by customer id , just enter your company name.
7. R11i: Provide the output of the Inventory Period Closing Activity Test from Note 206576.1
Note:
Run the following sql to identify the hung process:
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'
The output of the above query would be Request Id , Time Stamp, Status Code and
Phase Code similar to the example below:
5296078 8/16/2006 10:19:02 PM P I
The Status and Phase codes are as follows:
PHASE CODE:
Value Meaning
I Inactive
P Pending
R Running
C Completed
STATUS CODE:
Value Meaning
U Disabled
W Paused
X Terminated
Z Waiting
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
A Waiting
B Resuming
C Normal
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'
The output of the above query would be Request Id , Time Stamp, Status Code and
Phase Code similar to the example below:
5296078 8/16/2006 10:19:02 PM P I
The Status and Phase codes are as follows:
PHASE CODE:
Value Meaning
I Inactive
P Pending
R Running
C Completed
STATUS CODE:
Value Meaning
U Disabled
W Paused
X Terminated
Z Waiting
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
A Waiting
B Resuming
C Normal
Troubleshooting Uncosted
Transactions:
1. Follow the steps for stuck costing provided in attached file
steps_to_be_followed_bde_stuck_cost.txt
2. Run the
following and upload in excel.
If there are any errors in wip_cost_txn_interface please take corrective
actions as given in the WIP_TXN_INTERFACE_ERRORS which is given by query (b)
a) select *
from wip_cost_txn_interface wcti
where process_status = 3
and exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.costed_flag = 'N'
and wcti.wip_entity_id = mmt.transaction_source_id
and mmt.transaction_source_type_id = 5)
b) Run the following to get any errors in WIP_TXN_INTERFACE_ERRORS for transactions given by query (a) and upload in excel.
select *
from WIP_TXN_INTERFACE_ERRORS WTIE
where WTIE.transaction_id in (
select wcti.transaction_id
from wip_cost_txn_interface wcti
where process_status = 3
and exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.costed_flag = 'N'
and wcti.wip_entity_id = mmt.transaction_source_id
and mmt.transaction_source_type_id = 5))
3. Check if there was any records stuck in MMTT? Run
select *
from mtl_material_Transactions_temp;
Must return 0 rows, if it doesn't work with Inventory support/dev to have
MMTT rows to be processed do NOT delete them.
a) Run the following and upload in excel.
This SQL will give all the completion transactions which have pending
transactions in mtl_material_transactions_temp or wip_move_txn_interface
select *
from mtl_material_transactions mmt
where mmt.transaction_action_id in (31,30)
and transaction_source_type_id = 5
and exists (
SELECT null
FROM mtl_material_transactions_temp MMTP
WHERE MMT.organization_id =MMTP.organization_id
AND (MMT.COMPLETION_TRANSACTION_ID =
MMTP.COMPLETION_TRANSACTION_ID
OR MMT.MOVE_TRANSACTION_ID =
MMTP.move_transaction_id)
UNION ALL
SELECT null
FROM wip_move_txn_interface WMTI
WHERE WMTI.TRANSACTION_ID =
MMT.MOVE_TRANSACTION_ID)
and costed_flag = 'N'
b) Run the following and upload in excel.
Please take the corrective action import these pending transactions from mtl_material_transactions_temp to
mtl_material_transactions by running inventory transaction processor.
If these are in error then please take corrective actions.
select *
FROM mtl_material_transactions_temp MMTP
where exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.transaction_source_type_id = 5
and mmt.costed_flag = 'N'
and MMT.organization_id = MMTP.organization_id
AND (MMT.COMPLETION_TRANSACTION_ID =
MMTP.COMPLETION_TRANSACTION_ID
OR MMT.MOVE_TRANSACTION_ID =
MMTP.move_transaction_id))
4. Check if there was any stuck WMTI? Run
select *
from wip_move_txn_interface;
Must return 0 rows, if it doesn't work with WIP support/dev to have WMTI
rows to be processed do NOT delete them.
If there are any errors in wip_cost_txn_interface please take corrective
actions as given in the WIP_TXN_INTERFACE_ERRORS which is given by query (b)
a) select *
from wip_cost_txn_interface wcti
where process_status = 3
and exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.costed_flag = 'N'
and wcti.wip_entity_id = mmt.transaction_source_id
and mmt.transaction_source_type_id = 5)
b) Run the following to get any errors in WIP_TXN_INTERFACE_ERRORS for transactions given by query (a) and upload in excel.
select *
from WIP_TXN_INTERFACE_ERRORS WTIE
where WTIE.transaction_id in (
select wcti.transaction_id
from wip_cost_txn_interface wcti
where process_status = 3
and exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.costed_flag = 'N'
and wcti.wip_entity_id = mmt.transaction_source_id
and mmt.transaction_source_type_id = 5))
3. Check if there was any records stuck in MMTT? Run
select *
from mtl_material_Transactions_temp;
Must return 0 rows, if it doesn't work with Inventory support/dev to have
MMTT rows to be processed do NOT delete them.
a) Run the following and upload in excel.
This SQL will give all the completion transactions which have pending
transactions in mtl_material_transactions_temp or wip_move_txn_interface
select *
from mtl_material_transactions mmt
where mmt.transaction_action_id in (31,30)
and transaction_source_type_id = 5
and exists (
SELECT null
FROM mtl_material_transactions_temp MMTP
WHERE MMT.organization_id =MMTP.organization_id
AND (MMT.COMPLETION_TRANSACTION_ID =
MMTP.COMPLETION_TRANSACTION_ID
OR MMT.MOVE_TRANSACTION_ID =
MMTP.move_transaction_id)
UNION ALL
SELECT null
FROM wip_move_txn_interface WMTI
WHERE WMTI.TRANSACTION_ID =
MMT.MOVE_TRANSACTION_ID)
and costed_flag = 'N'
b) Run the following and upload in excel.
Please take the corrective action import these pending transactions from mtl_material_transactions_temp to
mtl_material_transactions by running inventory transaction processor.
If these are in error then please take corrective actions.
select *
FROM mtl_material_transactions_temp MMTP
where exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.transaction_source_type_id = 5
and mmt.costed_flag = 'N'
and MMT.organization_id = MMTP.organization_id
AND (MMT.COMPLETION_TRANSACTION_ID =
MMTP.COMPLETION_TRANSACTION_ID
OR MMT.MOVE_TRANSACTION_ID =
MMTP.move_transaction_id))
4. Check if there was any stuck WMTI? Run
select *
from wip_move_txn_interface;
Must return 0 rows, if it doesn't work with WIP support/dev to have WMTI
rows to be processed do NOT delete them.
Please
take the corrective action import these pending transactions from
wip_move_txn_interface to
wip_move_transactions by running wip move transaction processor.
If these are in error then please take corrective actions.
select *
FROM wip_move_txn_interface WMTI
where exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.transaction_source_type_id = 5
and mmt.costed_flag = 'N'
and WMTI.TRANSACTION_ID =
MMT.MOVE_TRANSACTION_ID )
5. Check if there are any orphaned Logical transactions?
Query:
SELECT mmt_parent.transaction_id
FROM MTL_MATERIAL_TRANSACTIONS mmt_parent
WHERE mmt_parent.parent_transaction_id is not null
and mmt_parent.costed_flag is null
and mmt_parent.parent_transaction_id = mmt_parent.transaction_id
and exists ( select 'x'
from mtl_material_transactions mmt_child
where mmt_child.parent_transaction_id =
mmt_parent.parent_transaction_id
and mmt_child.costed_flag = 'N')
This must return 0 rows, if it doesn't let us know!
6. checked if you have any orphaned backflush transaction?
a)
SELECT mmt.transaction_date txn_dte, mmt.transaction_id txn_id,
mmt.transaction_action_id action,
mmt.inventory_item_id item_id, mmt.primary_quantity p_qty,
mmt.subinventory_code subinv,
mmt.transaction_source_id src,
mmt.actual_cost ac, mmt.organization_id org_id,
mmt.currency_code curr_code,
mmt.acct_period_id
FROM mtl_material_transactions mmt
WHERE mmt.costed_flag IN ('N','E')
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id NOT IN (30,31,32)
AND mmt.flow_schedule = 'Y'
AND EXISTS (SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = mmt.organization_id
AND mmt1.transaction_action_id IN (30,31,32)
AND mmt1.transaction_source_type_id = 5
AND mmt1.costed_flag IS NULL
AND mmt1.completion_transaction_id =
mmt.completion_transaction_id);
b)
SELECT *
FROM wip_cost_txn_interface wcti
WHERE entity_type = 4
AND NVL(transaction_type,-1) IN (1,2,3)
AND EXISTS
(SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = wcti.organization_id
AND mmt1.transaction_action_id IN (30,31,32)
AND mmt1.transaction_source_type_id = 5
AND mmt1.costed_flag IS NULL
AND mmt1.completion_transaction_id =
wcti.completion_transaction_id)
Both cases 4 and 5 can happen when there is a server or databsae crash which
leaves half posted data.
7. Check if there there are no transactions with costed_flag other than 'E','N' and null?
Provide the output in Excel with headers of the following queries:
a)
select organization_id, costed_flag, count(*)
from mtl_material_Transactions
where costed_flag is not null
group by organization_id, costed_flag
b.)
Select organization_id, process_status, count(*)
from wip_cost_txn_interface
group by organization_id, process_status
wip_move_transactions by running wip move transaction processor.
If these are in error then please take corrective actions.
select *
FROM wip_move_txn_interface WMTI
where exists ( select 'x'
from mtl_material_transactions mmt
where mmt.transaction_action_id in (30,31)
and mmt.transaction_source_type_id = 5
and mmt.costed_flag = 'N'
and WMTI.TRANSACTION_ID =
MMT.MOVE_TRANSACTION_ID )
5. Check if there are any orphaned Logical transactions?
Query:
SELECT mmt_parent.transaction_id
FROM MTL_MATERIAL_TRANSACTIONS mmt_parent
WHERE mmt_parent.parent_transaction_id is not null
and mmt_parent.costed_flag is null
and mmt_parent.parent_transaction_id = mmt_parent.transaction_id
and exists ( select 'x'
from mtl_material_transactions mmt_child
where mmt_child.parent_transaction_id =
mmt_parent.parent_transaction_id
and mmt_child.costed_flag = 'N')
This must return 0 rows, if it doesn't let us know!
6. checked if you have any orphaned backflush transaction?
a)
SELECT mmt.transaction_date txn_dte, mmt.transaction_id txn_id,
mmt.transaction_action_id action,
mmt.inventory_item_id item_id, mmt.primary_quantity p_qty,
mmt.subinventory_code subinv,
mmt.transaction_source_id src,
mmt.actual_cost ac, mmt.organization_id org_id,
mmt.currency_code curr_code,
mmt.acct_period_id
FROM mtl_material_transactions mmt
WHERE mmt.costed_flag IN ('N','E')
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id NOT IN (30,31,32)
AND mmt.flow_schedule = 'Y'
AND EXISTS (SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = mmt.organization_id
AND mmt1.transaction_action_id IN (30,31,32)
AND mmt1.transaction_source_type_id = 5
AND mmt1.costed_flag IS NULL
AND mmt1.completion_transaction_id =
mmt.completion_transaction_id);
b)
SELECT *
FROM wip_cost_txn_interface wcti
WHERE entity_type = 4
AND NVL(transaction_type,-1) IN (1,2,3)
AND EXISTS
(SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = wcti.organization_id
AND mmt1.transaction_action_id IN (30,31,32)
AND mmt1.transaction_source_type_id = 5
AND mmt1.costed_flag IS NULL
AND mmt1.completion_transaction_id =
wcti.completion_transaction_id)
Both cases 4 and 5 can happen when there is a server or databsae crash which
leaves half posted data.
7. Check if there there are no transactions with costed_flag other than 'E','N' and null?
Provide the output in Excel with headers of the following queries:
a)
select organization_id, costed_flag, count(*)
from mtl_material_Transactions
where costed_flag is not null
group by organization_id, costed_flag
b.)
Select organization_id, process_status, count(*)
from wip_cost_txn_interface
group by organization_id, process_status
8. For R12.x
Check if there is any stuck records in CST_LC_ADJ_INTERFACE (CLAI)table ?
Make sure there are no records stuck with error in CST_LC_ADJ_INTERFACE table.
More information about the error should be in CST_LC_ADJ_INTERFACE_ERRORS table run:
select ce.error_message,ce.error_column, c.*
from cst_lc_adj_interface c, org_acct_periods o,cst_lc_adj_interface_errors ce
where c.organization_id = &org_id
and c.transaction_id = ce.transaction_id
and c.transaction_date <= o.schedule_close_date
and c.organization_id = o.organization_id
and o.acct_period_id = 6020
and rownum < 1000
Try setting the records in CST_LC_ADJ_INTERFACE to Process = 1 and Phase = 1 (Pending),
the Cost Managed spawned the Landed Cost Adjustment Processor and a Landed Cost Adjustment Worker.
update CST_LC_ADJ_INTERFACE
set process_status = 1,
process_phase=1
where rcv_transaction_id = &rcv_txn_id;
Run Landed Cost Adujustment Processor
Check records, should not be there.
select *
from CST_LC_ADJ_INTERFACE
where rcv_transaction_id = &rcv_txn_id;
9. Stop the cost manager, let all workers finish, then Enable MRP:DEBUG as
YES and enable the trace on the cost manager, then launch the cost manager,
Let it run for 1 hour and then stop it again, let all the workers finish
once more, then provide us :
a) the trace and tkprof of the cost manager
b) the log file of the cost manager, note you can only obtain the log of the
Cost Manager when the cost manager is stopped.
10. For R12: supply the output of the collector Diagnostics script test name "Cost processing errors for
Inventory and WIP Transactions& To Check Uncosted Transactions At Period End" (see Note 1271813.1)
11. For R12 : Run, review, and upload CSTPNTXN Period Close Pending Transactions Report output (from Doc ID 1331647.1)
12. Review the paper 'Resolving Period Close Pending Transaction' from Note 242927.1&
Note 1069492.1 Also review Note 304313.1
hey can you supply the stuck_info.sql
ReplyDelete