Oracle WIP Jobs Pending Resource Transactions, Pending Transactions in WIP_COST_TXN_INTERFACE Table

By | July 6, 2011

To find out the pending transactions from the application navigate to

WIP -> Discrete -> WIP Resource Transactions -> Pending Resource Transactions   and enter WIP Job name or any required parameter.

Here in this from you can find all the Pending Resource Transactions, if you could find your transaction in this form then that means your transaction got struck in Interface table [WIP_COST_TXN_INTERFACE].

Cause of Problem:

Usually this is caused if there is an problem with  ‘Cost Manager‘ and some times with  ‘Move Transaction Manager‘ too.

Solution:

Step 1: Identify your pending transactions in WIP_COST_TXN_INTERFACE table using the following query

SELECT transaction_id,
  request_id,
  GROUP_ID,
  process_status,
  wip_entity_name
FROM wip_cost_txn_interface
WHERE process_status IN (1, 3)
AND wip_entity_name   ='&ur_wipjob_name';

here

PROCESS_STATUS = 1 means Pending and
PROCESS_STATUS = 3 means Error

Note down all the transactions_id which you need to fix.

Step 2:  Take a backup of WIP_COST_TXN_INTERFACE table

Step 3:   Stop the Cost Manager & Move Transaction Manager

to stop these programs you need to navigate to INVENTORY –> Setup –>Transactions –> Interface Managers and then select the program and go to Tools menu.

Step 4: 

  • If the PROCESS_STATUS = 1 (Pending) , run the following script:
UPDATE wip_cost_txn_interface
  SET GROUP_ID = NULL,
  transaction_id = NULL
WHERE transaction_id IN (x, y, z);

x y z = transaction_ids of stuck transactions.

If  you want to fix all records in WIP_COST_TXN_INTERFACE table then run the following script:

UPDATE wip_cost_txn_interface
SET GROUP_ID = NULL,
transaction_id = NULL,
process_status = 1;
  • If the PROCESS_STATUS = 3 (Error) , run the following script:
UPDATE wip_cost_txn_interface
SET GROUP_ID = NULL,
process_status = 1
WHERE process_status = 3;

Step 5:  Restart the Cost Manager and Move Transaction Manager.

Navigate to Pending Resource Transaction Form and now you can’t find your transaction here as they are cleared,

Source: Note ID 234027.1

WOW! Did you like this post? We'll send more interesting posts like Oracle WIP Jobs Pending Resource Transactions, Pending Transactions in WIP_COST_TXN_INTERFACE Table to you!
Enter your Email Address:
  • Kiran Reddy

    Nice article!!! Helped me solve one issue..

  • Made Leine

    WOW! Finally i got a solution to my problem…Thank you Shailender 🙂

  • Kiran, Made

    Thank you. keep visiting my blog.