SQL Query to identify the change requests for IR_ISO

Select * from po_change_requests
where request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
AND DOCUMENT_TYPE= 'REQ'
AND REQUEST_LEVEL= 'LINE'
AND ACTION_TYPE = 'MODIFICATION';

SQL Query to identify duplicate distributions for an Internal Requisition.

SELECT SEGMENT1,REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL 
WHERE REQUISITION_HEADER_ID IN (select REQUISITION_HEADER_ID from PO_REQUISITION_LINES_ALL where REQUISITION_LINE_ID not in 
(select REQUISITION_LINE_ID FROM PO_REQ_DISTRIBUTIONS_ALL) AND SOURCE_TYPE_CODE='INVENTORY') 
and org_id =&orgid and authorization_status='APPROVED';

Script to identify outstanding reservation records that exist for order lines which are closed or cancelled.

This script will find outstanding reservation records that exist for order lines which are closed or cancelled. It does this for both external sales orders and internal orders.
Also, the script find reservations left after the Order line is deleted but reservations exist.
The script finds records from MTL_DEMAND which are not in sync with MTL_RESERVATIONS.

     a) 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 = ‘ ORDER ENTRY’);

    b)  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 = ‘ORDER ENTRY’);

c c) 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 = ' ORDER ENTRY’)

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');

   d) 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 = ‘ORDER ENTRY')

               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'));

e)  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 );

Script to identify old orphan move orders

This is script to identify Open move order lines which are not linked to Delivery details , Orphan suggestions in Mtl_material_transactions_temp and  reservations link to Mtl_material_transactions_temp if reservations are not existing.

a) select mmtt.*
from mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where mmtt.move_order_line_id IS NOT NULL
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status = 7
and mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = 3
and not exists (
select 'Y'
from wsh_delivery_details
where move_order_line_id = mtrl.line_id
and released_status = 'S' 

b)  Find allocation records for closed move order

 select mmtt.* 
from mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl 
where mmtt.move_order_line_id = mtrl.line_id 
and mtrl.line_status = 5 

c)  Find  allocation records with missing move order

  select mmtt.* 
from mtl_material_transactions_temp mmtt 
where move_order_line_id IS NOT NULL 
and not exists ( 
select mtrl.line_id 
from mtl_txn_request_lines mtrl 
where mtrl.line_id = mmtt.move_order_line_id) 

d)    select mtrl.*
from mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where mtrl.line_status = 7
and mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = 3
and not exists (
select 'Y'
from wsh_delivery_details
where move_order_line_id = mtrl.line_id
and released_status = 'S'