WSM Pending Transactions

Description
Categories: BI Publisher, Logistics, Manufacturing
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 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 )
Parameter Name SQL text Validation
Organization ID
 
Number
Exclude Un-Completed Jobs
 
LOV Oracle
Exclude Reserved Jobs
 
LOV Oracle
Status
 
LOV Oracle
To Job Completion Date
 
Date
From Job Completion Date
 
Date
To Job Start Date
 
Date
From Job Start Date
 
Date
To Job Release Date
 
Date
From Job Release Date
 
Date
To Job
 
LOV Oracle
From Job
 
LOV Oracle
To Class
 
LOV Oracle
From Class
 
LOV Oracle
Class Type
 
LOV Oracle