WSM Pending Transactions

Description
Categories: BI Publisher, Logistics, Manufacturing
Columns: :Wip Entity Id 1, Ptxn Table ...
Application: Shop Floor Management
Source: Pending Transactions Report (XML)
Short Name: WSMPNTXN_XML
DB package: WSM_WSMPNTXN_XMLP_PKG
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<