Pending transactions for inventory period close can be viewed from Inventory Accounting Periods form
Select Open period and click on Pending button.
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.
Nav :Accounting Close Cycle > Inventory Accounting Periods
--Unprocessed Material Transactions
SELECT COUNT(*)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = 'ORG_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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
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 = '' )
AND 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 = '' )
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
AND 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
