Transactions Stuck in Shipping Transaction Form with Status next as "Interface"

If Interface Trip Stop Program got Terminated and Sales Order got closed but Transaction stuck in Shipping Transaction Form with Status next as “Interfaced” the follow below steps

1. Please execute below scripts  for  Duplicates

create or replace view mmt_mti_records_v as
select a.transaction_interface_id ,
a.picking_line_id from
mtl_material_transactions b,  mtl_transactions_interface a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.picking_line_id is not null ;

create or replace view mmtt_mti_records_v as
select a.transaction_interface_id ,
a.picking_line_id from
mtl_material_transactions_temp b,  mtl_transactions_interface a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.picking_line_id is not null ;

create or replace view mmt_mmtt_records_v as
select a.transaction_temp_id,
a.picking_line_id from
mtl_material_transactions b,  mtl_material_transactions_temp a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in ( 2,8)
and b.picking_line_id is not null;

create table mti_dup_backup as (select * from mtl_transactions_interface
where transaction_interface_id in (select transaction_interface_id from mmt_mti_records_v));

create table msni_dup_backup as (select * from mtl_serial_numbers_interface
where transaction_interface_id in (select transaction_interface_id from mmt_mti_records_v))
union
(select * from mtl_serial_numbers_interface msni where msni.transaction_interface_id in (
select mtli.serial_transaction_temp_id
from mtl_transaction_lots_interface mtli
where mtli.transaction_interface_id in (select transaction_interface_id from
mmt_mti_records_v )));

create table mtli_dup_backup as (select * from mtl_transaction_lots_interface where transaction_interface_id in (select transaction_interface_id from mmt_mti_records_v));

create table mti_dup_backup_mmtt as (select * from mtl_transactions_interface
where transaction_interface_id in (select transaction_interface_id from mmtt_mti_records_v));

create table msni_dup_backup_mmtt as (select * from mtl_serial_numbers_interface
where transaction_interface_id in (select transaction_interface_id from mmtt_mti_records_v))
union
(select * from mtl_serial_numbers_interface msni where msni.transaction_interface_id in (
select mtli.serial_transaction_temp_id
from mtl_transaction_lots_interface mtli
where mtli.transaction_interface_id in (select transaction_interface_id from
mmtt_mti_records_v )));

create table mtli_dup_backup_mmtt as (select * from mtl_transaction_lots_interface where transaction_interface_id in (select transaction_interface_id from mmtt_mti_records_v));

create table mmtt_dup_backup as (select * from mtl_material_transactions_temp
where transaction_temp_id in (select transaction_temp_id from mmt_mmtt_records_v));

create table msnt_dup_backup as
(select * from mtl_serial_numbers_temp
where transaction_temp_id in (select transaction_temp_id from mmt_mmtt_records_v))
union
(select * from mtl_serial_numbers_temp msnt where msnt.transaction_temp_id in (
select mtlt.serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id in (select transaction_temp_id from
mmt_mmtt_records_v )));

create table mtlt_dup_backup as (select * from mtl_transaction_lots_temp
 where transaction_temp_id in (select transaction_temp_id from mmt_mmtt_records_v ));

delete from mtl_serial_numbers_interface where transaction_interface_id
in (select transaction_interface_id from mmt_mti_records_v );

delete from mtl_serial_numbers_interface msni
where msni.transaction_interface_id in (
select mtli.serial_transaction_temp_id
from mtl_transaction_lots_interface mtli
where mtli.transaction_interface_id in (select transaction_interface_id from
mmt_mti_records_v ));

delete from mtl_transaction_lots_interface where transaction_interface_id in
(select transaction_interface_id from mmt_mti_records_v );

delete from mtl_transactions_interface where transaction_interface_id in
(select transaction_interface_id from
mmt_mti_records_v );

delete from mtl_serial_numbers_interface where transaction_interface_id
in (select transaction_interface_id from mmtt_mti_records_v );

delete from mtl_serial_numbers_interface msni
where msni.transaction_interface_id in (
select mtli.serial_transaction_temp_id
from mtl_transaction_lots_interface mtli
where mtli.transaction_interface_id in (select transaction_interface_id from
mmtt_mti_records_v ));

delete from mtl_transaction_lots_interface where transaction_interface_id in
(select transaction_interface_id from mmtt_mti_records_v );

delete from mtl_transactions_interface where transaction_interface_id in
(select transaction_interface_id from
mmtt_mti_records_v );

delete from mtl_serial_numbers_temp where transaction_temp_id
in (select transaction_temp_id from mmt_mmtt_records_v );

delete from mtl_serial_numbers_temp msnt
where msnt.transaction_temp_id in (
select mtlt.serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id in (select transaction_temp_id from
mmt_mmtt_records_v ));

delete from mtl_transaction_lots_temp where transaction_temp_id in
(select transaction_temp_id from mmt_mmtt_records_v );

delete from mtl_material_transactions_temp where transaction_temp_id in
(select transaction_temp_id from mmt_mmtt_records_v );

COMMIT;
 

2. Review and commit.

3. Then run the below 3 queries  -

select a.transaction_interface_id ,

a.picking_line_id from

mtl_material_transactions b,  mtl_transactions_interface a

where a.picking_line_id = b.picking_line_id

and a.trx_source_line_id = b.trx_source_line_id

and a.inventory_item_id = b.inventory_item_id

and b.transaction_type_id = a.transaction_type_id

and b.transaction_source_type_id in (2,8)

and b.picking_line_id is not null ;

select a.transaction_interface_id ,

a.picking_line_id from

mtl_material_transactions_temp b,  mtl_transactions_interface a

where a.picking_line_id = b.picking_line_id

and a.trx_source_line_id = b.trx_source_line_id

and a.inventory_item_id = b.inventory_item_id

and b.transaction_type_id = a.transaction_type_id

and b.transaction_source_type_id in (2,8)

and b.picking_line_id is not null ;

select a.transaction_temp_id,

a.picking_line_id from

mtl_material_transactions b,  mtl_material_transactions_temp a

where a.picking_line_id = b.picking_line_id

and a.trx_source_line_id = b.trx_source_line_id

and a.inventory_item_id = b.inventory_item_id

and b.transaction_type_id = a.transaction_type_id

and b.transaction_source_type_id in ( 2,8)

and b.picking_line_id is not null;

4. If none return records, follow with the below steps .

5. Run the below query to reprocess the transactions from TEMP -

update MTL_MATERIAL_TRANSACTIONS_TEMP

set TRANSACTION_MODE = 3,

LOCK_FLAG = 'N',

PROCESS_FLAG = 'Y',

ERROR_CODE = NULL,

ERROR_EXPLANATION = NULL

where transaction_mode = 8;

6. Review the updated transactions.  Then issue a commit:

commit;

7. Please resubmit the transaction from

Inventory -> Transactions -> Pending Transactions, Query for stuck transactions for this item, and resubmit by selecting Tools -> Resubmit All.

Note: Ensure that you have the transaction managers (Inventory -> Setup -> Transactions -> Interface Managers - Material Transaction Manager) up and running.

Check if all Transactions got Processed successfully