How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table

How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table

Solution
Record may be resubmitted through the Application or using SQL*Plus.

Note: Back up the data in the table prior to making any changes using SQL*Plus.

1.Resubmission through the Application
Navigation Inventory:Transactions:Transaction Open Interface
Click in the check box under the "Submit" column
then Save


2. Resubmission through SQL*Plus.

Update MTL_TRANSACTIONS_INTERFACE Set PROCESS_FLAG = 1,
LOCK_FLAG = 2, TRANSACTION_MODE = 3,
VALIDATION_REQUIRED = 1, ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL Where PROCESS_FLAG IN (1,3);

Records Stuck In Table Mtl_Transactions_Interface , Negative Balances Not Allowed

Run the following script, which will find items that do not have sufficient quantity in the STAGING subinventory.

Note: if you have given some name other than 'STAGE' to your staging subinventory, you will need to modify the lines in bold type

SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, MAX (oq.onhand_qty) onhand_qty,
SUM (wdd.requested_quantity) - MAX (oq.onhand_qty) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi,
(SELECT NVL (SUM (moq.transaction_quantity), 0) onhand_qty,
moq.organization_id, moq.inventory_item_id,
moq.subinventory_code
FROM apps.mtl_onhand_quantities moq
WHERE moq.containerized_flag = 2
AND moq.subinventory_code = 'STAGE'
GROUP BY moq.organization_id,
moq.inventory_item_id,
moq.subinventory_code) oq
WHERE wdd.released_status IN ('C', 'Y')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.inventory_item_id = oq.inventory_item_id
AND wdd.organization_id = oq.organization_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND oq.subinventory_code = 'STAGE'
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
HAVING SUM (wdd.requested_quantity) > MAX (oq.onhand_qty)
UNION ALL
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, 0,
SUM (wdd.requested_quantity) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi
WHERE wdd.released_status IN ('Y', 'C')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND NOT EXISTS (
SELECT 1
FROM mtl_onhand_quantities oq
WHERE oq.inventory_item_id = wdd.inventory_item_id
AND oq.organization_id = wdd.organization_id
AND oq.subinventory_code = 'STAGE')
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
ORDER BY 1, 2


2. Perform subinventory transfers as needed to transfer the quantity identified by the column "change_qty" into the staging subinventory

R12: Change Application user password and end date from sqlplus

R12: Change Application user password and end date from sqlplus

DECLARE
v_user_name VARCHAR2(80) :'XXABC';
v_new_password VARCHAR2(80) := 'oracle123';
v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => 'N');
-- Call the procedure
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_unencrypted_password => v_new_password
,x_end_date => SYSDATE + 10000);
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
RAISE;
END;