1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to resubmit pending Error Transactions in WIP

Discussion in 'Oracle SCM & Manufacturing' started by erp.narendrag, May 24, 2013.

  1. erp.narendrag

    erp.narendrag Forum Expert

    Messages:
    108
    Likes Received:
    17
    Trophy Points:
    655
    Location:
    India
    How to resubmit pending/ error Transactions in WIP



    Pending WIP Material Transactions:


    First check if the Inventory Transaction Manager is running and if there are any records pending or in error in the inventory transactions interface. These can be queried via the Inventory Transactions Interface Inquiry screen. Navigate Inventory/Transactions/Transaction Open Interface. Records can be deleted and/or resubmitted with this form. Check Pending Transactions; Navigate Inventory/Transactions/Pending Transactions.
    Records can only be resubmitted via the form. If there are Pending Transactions that are stuck in the MTL_MATERIAL_TRANSACTIONS_TEMP table with transaction_type_id=5, (back flush/wip transactions) with proces_flag =’E’, they need to be submitted.


    To investigate why the Transactions are failing, run the following SQL:


    select transaction_source_id,
    inventory_item_id,
    process_flag,
    error_code,
    error_explanation,
    transaction_source_type_id,
    organization_id
    from mtl_material_transactions_temp
    where process_flag ='E' and ORGANIZATION_ID=:xx;


    Use this script to resubmit the Transactions:


    update mtl_material_transactions_temp
    set process_flag = 'Y',
    lock_flag ='N',
    transaction_mode= 3,
    error_code = NULL,
    error_explanation = NULL
    where process_flag = 'E' --and transaction_source_id=:xx;






    Pending WIP Completion Transactions:

    There are 2 ways of performing Completion Transactions.
    In the WIP Move Transactions Form:
    Navigation -> WIP/Move Transactions/Move Transactions, Set Transaction Type to Complete
    In the WIP Material Transaction Form:
    Navigation -> WIP/Material Transactions/Completion Transactions
    Records are created by doing a WIP Assembly Completion. They are stuck in
    MTL_MATERIAL_TRANSACTIONS_TEMP Table.
    Navigate –> Inventory/Transaction/Pending Transactions for the error messages:
    Or run the Following SQL script:

    To find the ORGANIZATION_ID run this script:

    select organization_id,
    organization_code
    from org_organization_definitions
    where organization_name
    like 'xxx%'; --( xxx is organization name)


    To find the WIP_Entity_Id run this script:

    select wip_entity_id
    from wip_entities
    where wip_entity_name = :x1
    and organization_id= :xxx; -- ( xxx is organization id )


    select error_code,
    error_explanation,
    lock_flag,
    inventory_item_id,
    transaction_action_id,
    transaction_source_type_id,
    process_flag,
    organization_id
    from mtl_material_transactions_temp
    where organization_id =:x1
    and transaction_source_id =:x2;
    (wip_entity_id is obtained from above SQL statement)




    Pending WIP Move Transactions:

    WIP_MOVE_TXN_INTERFACE Contains information about the shop floor move transactions that need to be processed. To View the pending move transaction:
    Navigation -> WIP/Move Transactions/Pending Move Transactions.
    Records can be updated, deleted, and resubmitted via the form.
    The Transaction Processing mode should be set to online processing.
    If there are any errored move Transactions in wip_move_txn_interface, then these Transactions must be resubmitted.
    Run the following scripts:
    To find how many rows are in Error:
    select count(*)
    from wip_move_txn_interface
    where process_status = 3;

    To find how many rows are in Pending:

    select transaction_id,
    wip_entity_id,
    process_phase,
    process_status,
    wip_entity_name
    from wip_move_txn_interface
    where process_status in (1,3)

    (Process Status = 1 indicates Pending, 3 indicates "Error")

    To find how many rows are in Error and what the messages are:
    select error_message,
    error_column
    from wip_txn_interface_errors
    where request_id=:x1 -- ( for the two that have a request id )






    Use this script to update the Error Records:

    update wip_move_txn_interface
    set group_id=null,
    request_Id = null,
    process_status=1,
    transaction_id=null
    where transaction_id=:x1;

    Restart the interface managers (Move and Cost Manager).Launch the Move Transaction Manager even if the TP: WIP Move Transaction Profile Option = on-line processing.

    Navigation -> Inventory/Setup/Transactions/Interface Managers.

    Use the “Launch Manager” button under the special option on the tool bar.
    Pending WIP Resource Transactions:

    Do the following to re-submit ERRORED RESOURCE COST records;
    1. Ensure that there is no (Resource) cost worker running.
    2. Stop the (Resource) Cost Manager.
    3. Take backup of this table WIP_COST_TXN_INTERFACE
    4. To check for pending transactions, run the following SQL;
    select transaction_id,
    request_id,
    group_id,
    process_status,
    wip_entity_name
    from wip_cost_txn_interface
    where process_status in (1, 3);
    (process_status= 1 indicates 'Pending', 3 indicates ‘Error’)
    5. Update the table.
    update wip_cost_txn_interfaceset
    group_id=NULL,
    transaction_id = NULL,
    process_status= 1 --(set it to Pending)
    where process_status = 3; --( error records)
    6. Restart the (Resource) Cost Manager.
    Pending Close Discrete Jobs:

    Go into SQL*Plus as apps user / and run the following sql script:
    wipcljob.sql
    Use this script to Report a Range of Discrete Jobs for a specific Organization that cannot be Closed Due to Pending Transactions against them. This is a sql script which queries the database and generates output which shows the Transactions that are causing the problem. This script ONLY identifies data; it does NOT update or correct any data.
    This script can also be downloaded here; Pending Jobs SQL Scripts Note 106242.1
    Make certain that no workers are running and the Cost Manager has been taken down when running the update scripts.
    In order for a job to be successfully closed, there must be NO Transactions for the job in WIP_MOVE_TXN_INTERFACE. If there are, then run the following script:
    Use this script to find Pending Rows:
    select transaction_id,
    wip_entity_id,
    process_phase,
    process_status,
    wip_entity_name
    from wip_move_txn_interface
    where process_status in (1,3);

    (process status = 1 indicates Pending, 3 indicates "Error”)


    update wip_move_txn_interface
    set group_id=null,
    process_status=1,
    transaction_id=null
    where transaction_id=:xxx1;

    There must be NO transactions for the job in WIP_COST_TXN_INTERFACE.
    WIP_COST_TXN_INTERFACE contains information about cost transactions that WIP needs to process.

    select transaction_id,
    wip_entity_id,
    process_phase,
    process _status
    from wip_cost_txn_interface
    where process_status in (1, 3);

    If there are transactions for the job, then run the following script:

    update wip_cost_txn_interface
    set group_id=null,
    process_status=1,
    transaction_id=null
    where transaction_id=;

    There must be no transactions for the job in MTL_MATERIAL_TRANSACTIONS_TEMP.
    The mtl_material_transactions_temp table is the Key for all material transactions. First make sure the Inventory Transaction Manager is running.

    select transaction_source_id ,
    inventory_item_id,
    process_flag,
    error_code,
    error_explanation,
    transaction_source_type_id,
    organization_id
    from mtl_material_transactions_temp
    where process_flag = 'E'
    and transaction_source_type_id=5; ( 5 = Job/schedule )
     
  2. dora.boyack

    dora.boyack Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    China
    Very often you can go to the forms to query the transactions in problem and find out what's wrong. For example go to Pending WIP Resource or Move Transactions there's a tab on the error and explaination.

    One more form not mentioned above sqls is the inventory transaction form, costed = Error. Again there's error explainations.
     
  3. dora.boyack

    dora.boyack Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    China
    I should have noticed the title of the discussion is on WIP.