Apps Technical

Inventory Period Close:Oracle Apps R12

By Jag - July 18, 2014
Pending transactions for inventory period close can be viewed from Inventory Accounting Periods form.

Navigation: Accounting Close Cycle > Inventory Accounting Periods

Select Open period and click on Pending button











Transactions under Resolution Required and Unprocessed Shipping Transactionszones 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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = 'ORGZ_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 = ORGZ_ID 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('PERIOD_START_DATE 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('PERIOD_END_DATE   23:59:59', 'DD-MON-YYYY HH24:MI:SS') AND
       WDL.PICK_UP_STOP_ID = WTS.STOP_ID
  • Share:

You Might Also Like

0 comments