Oracle Pending Transaction Queries

Oracle Pending Transaction Queries
Unprocessed Material:
SELECT *
  FROM mtl_material_transactions_temp
 WHERE     organization_id = :p_org_id
       AND transaction_date < TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND NVL (transaction_status, 0) <> 2 -- 2 indicates a save-only status;

Uncosted Material/WSM:
SELECT  /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */     *
  FROM mtl_material_transactions MMT
 WHERE     organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND costed_flag IS NOT NULL;

Pending WIP Costing:
SELECT *
  FROM wip_cost_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending WSM Interface:
SELECT *
  FROM wsm_split_merge_txn_interface
 WHERE     organization_id = :p_org_id
       AND process_status <> wip_constants.completed
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending LCM Interface:
/* Support for Landed Cost Management: Pending landed cost adjustment transactions */
SELECT *
  FROM cst_lc_adj_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending Receiving:
SELECT *
  FROM rcv_transactions_interface
 WHERE     to_organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND destination_type_code IN ('INVENTORY', 'SHOP FLOOR');

Pending Material:
SELECT *
  FROM mtl_transactions_interface
 WHERE     organization_id = 102
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND process_flag <> 9;

Pending Shop Floor Move:
SELECT *
  FROM wip_move_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr');

Incomplete Work Orders : If the maintenance is enabled in mtl parameters.eam_enabled_flag = ‘Y’

SELECT *
  FROM wip_discrete_jobs WDJ, wip_entities WE
 WHERE WDJ.organization_id = :p_org_id
       AND WDJ.scheduled_completion_date <=TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND WDJ.status_type = 3                                     -- Released
       AND WDJ.wip_entity_id = WE.wip_entity_id
       AND WDJ.organization_id = WE.organization_id
       AND WE.entity_type = 6                       -- Maintenance Work Order;

==============================================================================================================
Unprocessed Material:
SELECT COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>' AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
  AND NVL(TRANSACTION_STATUS, 0) <> 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
 COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Transactions
   
SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Interface

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Pending Receiving Transactions

SELECT COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND PROCESS_FLAG <> 9


--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Unprocessed Shipping Transactions


SELECT COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
 WHERE WDD.SOURCE_CODE = 'OE'
   AND WDD.RELEASED_STATUS = 'C'
   AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')
   AND WDD.ORGANIZATION_ID = 86
   AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
   AND WND.DELIVERY_ID = WDA.DELIVERY_ID
   AND WND.STATUS_CODE IN ('CL', 'IT')
   AND WDL.DELIVERY_ID = WND.DELIVERY_ID
   AND WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P')
   AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('27-OCT-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('23-NOV-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID