Update data from another Table

UPDATE (
SELECT b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

參考 http://blog.csdn.net/yuhua3272004/article/details/2776121

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

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

No related posts.

http://oracleappsdna.com/2011/07/oracle-wip-jobs-pending-resource-transactions-pending-transactions-in-wip_cost_txn_interface-table/


這次使用的指令

Update WIP_COST_TXN_INTERFACE
Set GROUP_ID = NULL,
TRANSACTION_ID = NULL,
REQUEST_ID = NULL,
PROCESS_STATUS = 1
Where ORGANIZATION_ID = 86
AND WIP_ENTITY_ID in (214751, 211204)