FND Queries

Query to find scheduled requests:

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CR.DESCRIPTION,
       CR.REQUEST_DATE,
       CR.REQUESTED_BY,
       (SELECT USER_NAME 
          FROM APPS.FND_USER 
         WHERE USER_ID = CR.REQUESTED_BY) REQUEST_BY_USER,
       (SELECT RESPONSIBILITY_NAME
          FROM FND_RESPONSIBILITY_TL
         WHERE RESPONSIBILITY_ID = CR.RESPONSIBILITY_ID
           AND LANGUAGE = 'US'
       ) RESPONSIBILITY,
       CR.RESUBMIT_INTERVAL,
       CR.PHASE_CODE,
       CR.STATUS_CODE,
       CR.HOLD_FLAG,
       CR.REQUESTED_START_DATE,
       CR.REQUEST_ID,
       CR.ARGUMENT_TEXT
  FROM FND_CONCURRENT_REQUESTS CR, 
       FND_CONCURRENT_PROGRAMS_TL CPTL
 WHERE CR.STATUS_CODE IN ('Q', 'I') AND 
       CR.REQUESTED_START_DATE > SYSDATE AND
       CPTL.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID AND
       CPTL.LANGUAGE = 'US'
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_START_DATE DESC

Concurrent Request Status and Phase Codes

Concurrent Request Status Codes:

SELECT LOOKUP_CODE STATUS_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;

STATUS_CODE	MEANING
A	Waiting
B	Resuming
C	Normal
D	Cancelled
E	Error
G	Warning
H	On Hold
I	Normal
M	No Manager
P	Scheduled
Q	Standby
R	Normal
S	Suspended
T	Terminating
U	Disabled
W	Paused
X	Terminated
Z	Waiting

Concurrent Request Phase Codes:

SELECT LOOKUP_CODE PHASE_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;

STATUS_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

Inventory Period Close

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 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 = '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