The following script will get rid of outstanding reservation records that exist for order lines which are closed or cancelled

Negative Qty Showing on OnHand Qty Form for Items

The following script will get rid of outstanding  reservation records that exist for order lines which are closed or cancelled. For both external sales orders and internal orders. Also script cleans reservations left after Order line is deleted
REM but reservations exist
 Script deletes from mtl_demand which are not in sync with Mtl_reservations.

If Qty is Showing Negative on OnHand QTy Form for Items than we might need to execute below script

SELECT  V.PROFILE_OPTION_VALUE OE_PROFILE

FROM    FND_PROFILE_OPTION_VALUES V

WHERE   (V.PROFILE_OPTION_ID, V.APPLICATION_ID, V.LEVEL_ID) =

                (SELECT V2.PROFILE_OPTION_ID,V2.APPLICATION_ID,MAX(V2.LEVEL_ID)

                FROM FND_PROFILE_OPTIONS OO,

                     FND_PROFILE_OPTION_VALUES V2

                WHERE ((V2.LEVEL_ID = 10001 AND V2.LEVEL_VALUE=0)

                          OR (V2.LEVEL_ID = 10002 AND V2.LEVEL_VALUE=660))

                AND   OO.PROFILE_OPTION_ID = V2.PROFILE_OPTION_ID

                AND   OO.APPLICATION_ID = V2.APPLICATION_ID

                AND   OO.APPLICATION_ID = 660

                AND   UPPER(OO.PROFILE_OPTION_NAME) = 'ONT_SOURCE_CODE'

                GROUP BY V2.PROFILE_OPTION_ID, V2.APPLICATION_ID)

AND   V.LEVEL_VALUE = DECODE(V.LEVEL_ID, 10001, 0, 10002, 660);

PROMPT ** This gets rid of extraneous demand and reservations that are left

PROMPT ** after a line has been cancelled  

SELECT L.LINE_ID, L.HEADER_ID

FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M

WHERE  M.PRIMARY_RESERVATION_QUANTITY>0

AND nvl(L.CANCELLED_FLAG,'N')='Y'

AND L.CANCELLED_QUANTITY IS NOT NULL

AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID

AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI

                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID

                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID

                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE');

UPDATE MTL_RESERVATIONS

SET    PRIMARY_RESERVATION_QUANTITY = 0

,      RESERVATION_QUANTITY = 0

,      LAST_UPDATED_BY=-2471362

WHERE  PRIMARY_RESERVATION_QUANTITY>0

AND    EXISTS (SELECT 'X'

               FROM   OE_ORDER_LINES_ALL L

               WHERE  nvl(L.CANCELLED_FLAG,'N')='Y'

               AND L.CANCELLED_QUANTITY IS NOT NULL

               AND L.LINE_ID = MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID

               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI

                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID

                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID

                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE'));

PROMPT ** This gets rid of extraneous demand and reservations that are left

PROMPT ** after a line has been closed or delivery detail is cancelled

SELECT L.LINE_ID, L.HEADER_ID

FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M

WHERE   M.PRIMARY_RESERVATION_QUANTITY>0

AND nvl(L.OPEN_FLAG,'Y')='N'

AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID

AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI

                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID

                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID

                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')

AND NOT EXISTS              (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD

                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID

                   AND WDD.SOURCE_CODE ='OE'

                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')

                   AND WDD.RELEASED_STATUS <> 'D');

UPDATE MTL_RESERVATIONS

SET    PRIMARY_RESERVATION_QUANTITY = 0

,      RESERVATION_QUANTITY = 0

,      LAST_UPDATED_BY=-2471362

WHERE  PRIMARY_RESERVATION_QUANTITY>0

AND    EXISTS (SELECT 'X'

               FROM   OE_ORDER_LINES_ALL L

               WHERE  nvl(L.OPEN_FLAG,'Y')='N'

               AND L.LINE_ID = nvl(MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID,-99)

               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI

                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID

                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID

                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')

               AND NOT EXISTS               (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD

                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID

                   AND WDD.SOURCE_CODE ='OE'

                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')

                   AND WDD.RELEASED_STATUS <> 'D'));

PROMPT ORDER DETAILS WITH ORPHAN RESERVATIONS AFTER LINE IS DELETED

SELECT MSO.SEGMENT1 ORD_NUMBER,

       MSO.SEGMENT2 ORD_TYPE,

       MSO.SALES_ORDER_ID sALES_ORDER_ID,

       MR.DEMAND_SOURCE_LINE_ID oRDER_LINE_ID,

       MR.iNVENTORY_ITEM_ID iTEM_ID,

       MR.ORGANIZATION_ID ORGANIZATION_ID,

       MR.PRIMARY_RESERVATION_QUANTITY pRSV_QTY,

       MR.RESERVATION_QUANTITY RSV_QTY

 FROM MTL_RESERVATIONS MR,

      MTL_SALES_ORDERS MSO

WHERE MSO.SALES_ORDER_ID=MR.DEMAND_SOURCE_HEADER_ID

  AND MR.DEMAND_SOURCE_TYPE_ID IN (2,8)

  AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL

                                         WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID );

PROMPT UPDATING MTL_RESERVATIONS

UPDATE MTL_RESERVATIONS MR

SET PRIMARY_RESERVATION_QUANTITY=0,

    RESERVATION_QUANTITY=0,

    LAST_UPDATED_BY=-2471362

   WHERE MR.DEMAND_SOURCE_TYPE_ID IN (2,8)

     AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL

                                            WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID);

COMMIT;

PROMPT **Updating records for closed /cancelled lines in mtl_demand where records are not in sync

UPDATE MTL_DEMAND D

SET    PRIMARY_UOM_QUANTITY = 0

,      LINE_ITEM_QUANTITY   = 0

,      COMPLETED_QUANTITY   = 0

,      LINE_ITEM_RESERVATION_QTY = 0

,      RESERVATION_QUANTITY = 0

,      LAST_UPDATED_BY=-2471362

WHERE  DEMAND_SOURCE_TYPE IN (2,8)

AND RESERVATION_TYPE = 2

AND    DEMAND_SOURCE_LINE = ( SELECT O.LINE_ID

                                FROM OE_ORDER_LINES_ALL O

                                WHERE  ( NVL(o.OPEN_FLAG,'Y') = 'N'

                                         OR NVL(O.CANCELLED_FLAG,'N') ='Y')

                                AND  O.LINE_ID =D.DEMAND_SOURCE_LINE )

AND   DEMAND_SOURCE_LINE NOT IN  ( select TRX_SOURCE_LINE_ID

                    FROM  MTL_TRANSACTIONS_INTERFACE MTI

                    WHERE  MTI.SOURCE_LINE_ID = D.DEMAND_SOURCE_LINE)

AND DEMAND_SOURCE_LINE NOT IN (SELECT SOURCE_LINE_ID

                   FROM WSH_DELIVERY_DETAILS WDD

                 WHERE WDD.SOURCE_LINE_ID=D.DEMAND_SOURCE_LINE

                   AND WDD.SOURCE_CODE='OE'

                   AND WDD.INV_INTERFACED_FLAG IN ('N','P'));

PROMPT FINAL UPDATING MTL_DEMAND

UPDATE MTL_DEMAND SET PRIMARY_UOM_QUANTITY=0,

                      COMPLETED_QUANTITY=0,

                      RESERVATION_QUANTITY=0,

                      LAST_UPDATED_BY=-2471362

       WHERE DEMAND_SOURCE_TYPE IN (2,8)

         AND RESERVATION_TYPE=2

         AND DEMAND_ID IN (SELECT N_COLUMN1 FROM MTL_RESERVATIONS WHERE

                                    DEMAND_SOURCE_TYPE_ID IN (2,8)

                                AND PRIMARY_RESERVATION_QUANTITY=0

                                AND RESERVATION_QUANTITY=0

                                AND LAST_UPDATED_BY=-2471362);

PROMPT **Deleting reservations which are complete

COMMIT;

DELETE FROM MTL_RESERVATIONS WHERE

       DEMAND_SOURCE_TYPE_ID IN (2,8)

 AND   LAST_UPDATED_BY=-2471362;

PROMPT **Deleting records from mtl_demand where reservations are complete

DELETE FROM MTL_DEMAND WHERE

    DEMAND_SOURCE_TYPE IN (2,8)

AND RESERVATION_TYPE=2     

AND LAST_UPDATED_BY=-2471362;

Email Notifications are not getting received to company Emails from Oracle Workflow

Issue Might be with 'Workflow Notification Mailer was hung and stopped processing.'  If we did stop/start the Workflow Notification mailer and lo and behold which resolved the issue, after this Emails will Flow to the Company Emails successfully 

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