WSM Pending Transactions

select :WIP_ENTITY_ID_1,'Pending Move Transactions' PTXN_TABLE
from dual
      WHERE exists ( 
        SELECT
          1
        FROM
          WIP_MOVE_TXN_INTERFACE
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WIP_ENTITY_ID = :WIP_ENTITY_ID_1
        UNION ALL
        SELECT
          1
        FROM
          WSM_LOT_MOVE_TXN_INTERFACE
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WIP_ENTITY_ID = :WIP_ENTITY_ID_1
          AND STATUS <> 4 )
  UNION ALL
      SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2 ,'Pending Resource Transactions' PTXN_TABLE
      FROM      DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          WIP_COST_TXN_INTERFACE
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WIP_ENTITY_ID = :WIP_ENTITY_ID_1 )
UNION ALL
      SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2  ,'Pending Material Transactions' PTXN_TABLE
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          MTL_MATERIAL_TRANSACTIONS_TEMP
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND TRANSACTION_SOURCE_ID = :WIP_ENTITY_ID_1 
          AND TRANSACTION_SOURCE_TYPE_ID = 5 )
UNION ALL
      SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2  ,'Uncosted Material Transactions' PTXN_TABLE
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND TRANSACTION_SOURCE_ID = :WIP_ENTITY_ID_1 
          AND COSTED_FLAG IN ( 'N' , 'E' ) 
          AND TRANSACTION_SOURCE_TYPE_ID = 5 )
UNION ALL
      SELECT :WIP_ENTITY_ID_1  WIP_ENTITY_ID2  ,'Pending Operation Yield'   PTXN_TABLE      
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          WIP_OPERATION_YIELDS
        WHERE ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND STATUS in ( 1 , 3 ) )
          UNION ALL
      SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2       ,'Pending WIP Lot Transactions' PTXN_TABLE
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          WSM_SM_STARTING_JOBS SJ,
          WSM_SPLIT_MERGE_TRANSACTIONS WMT
        WHERE SJ.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID 
          AND WMT.ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WMT.STATUS <> 4
        UNION
        SELECT
          1
        FROM
          WSM_SM_RESULTING_JOBS RJ,
          WSM_SPLIT_MERGE_TRANSACTIONS WMT
        WHERE RJ.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID 
          AND WMT.ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND WMT.STATUS <> 4 )
UNION ALL
    SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2    ,'Uncosted WIP Lot Transactions ' PTXN_TABLE
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          WSM_SM_STARTING_JOBS SJ,
          WSM_SPLIT_MERGE_TRANSACTIONS WMT
        WHERE SJ.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID 
          AND WMT.ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND NVL(WMT.COSTED
           ,1) <> 4
        UNION
        SELECT
          1
        FROM
          WSM_SM_RESULTING_JOBS RJ,
          WSM_SPLIT_MERGE_TRANSACTIONS WMT
        WHERE RJ.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID 
          AND WMT.ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND NVL(WMT.COSTED
           ,1) <> 4 )
UNION ALL
          SELECT :WIP_ENTITY_ID_1 WIP_ENTITY_ID2 ,'Pending PO Requisitions' PTXN_TABLE
      FROM
        DUAL
      WHERE exists ( 
        SELECT
          1
        FROM
          PO_RELEASES_ALL PR,
          PO_HEADERS_ALL PH,
          PO_DISTRIBUTIONS_ALL PD,
          PO_LINE_LOCATIONS_ALL PL
        WHERE PD.DESTINATION_ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND PD.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND PD.PO_LINE_ID is not null 
          AND PD.LINE_LOCATION_ID is not null 
          AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID 
          AND PH.PO_HEADER_ID = PD.PO_HEADER_ID 
          AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID 
          AND pr.po_release_id (+) = PD.PO_RELEASE_ID 
          AND NVL(PR.CANCEL_FLAG
           ,'N') = 'N' 
          AND ( PL.QUANTITY_RECEIVED < ( PL.QUANTITY - PL.QUANTITY_CANCELLED ) ) ) 
      OR exists ( 
        SELECT
          1
        FROM
          PO_REQUISITION_LINES_ALL PRL
        WHERE PRL.DESTINATION_ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND PRL.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 
          AND NVL(PRL.CANCEL_FLAG
           ,'N') = 'N' 
          AND PRL.LINE_LOCATION_ID is null ) 
      OR exists ( 
        SELECT
          1
        FROM
          PO_REQUISITIONS_INTERFACE_ALL PRI
        WHERE PRI.DESTINATION_ORGANIZATION_ID = :P_ORGANIZATION_ID 
          AND PRI.WIP_ENTITY_ID = :WIP_ENTITY_ID_1 )