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