Pending transactions for inventory period close

Pending transactions for inventory period close can be viewed from Inventory Accounting Periods form

Navigation: Accounting Close Cycle > Inventory Accounting Periods

Transactions under Resolution Required and Unprocessed Shipping Transactions zones must be resolved before an accounting period can be closed.

Transactions under Resolution Recommended will not prevent the closing of a period but these transactions cannot be resolved after an accounting period is closed.

Below are the queries to check for the pending transactions.

--Unprocessed Material Transactions

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