SQL Queries to find Latest Collection date and Plan Run date in ASCP/Demantra

Here is the query to find Latest Plan Run date
Concurrent Program name: 'Launch Supply Chain Planning Process'

SELECT

    MAX(ACTUAL_COMPLETION_DATE) LAST_PLAN_RUN_DT,

    ARGUMENT1 INSTANCE_CODE,

    ARGUMENT2 PLAN_ID

FROM  FND_CONCURRENT_REQUESTS A,

      FND_CONCURRENT_PROGRAMS_TL B

WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID

      AND B.USER_CONCURRENT_PROGRAM_NAME = 'Launch Supply Chain Planning Process'    

      AND PHASE_CODE='C' AND STATUS_CODE='C'

GROUP BY ARGUMENT1,

         ARGUMENT2 ;

This has to be joined with ebs table FND_APPS_SYSTEM.NAME to get exact instance code



Query to find Latest Collection Date.

There are 2 concurrent programs in the ASCP to collect the data from ebs.

In first stage we have to run concurrent program 'Planning Data Pull' which loads the data into staging tables.
Second stage we have to  run concurrent program 'Planning ODS Load' which loads all planning final tables.

Hence we can consider concurrent program 'Planning ODS Load'  latest date as the recent collection date in the ascp system

Here is the query.

SELECT

      MAX(ACTUAL_COMPLETION_DATE) LAST_COLLECTION_DT,

      C.INSTANCE_CODE

FROM  FND_CONCURRENT_REQUESTS A,

      FND_CONCURRENT_PROGRAMS_TL B,

      MSC_APPS_INSTANCES C

WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID

      AND A.ARGUMENT1=TO_CHAR(C.INSTANCE_ID)

      AND B.USER_CONCURRENT_PROGRAM_NAME = 'Planning ODS Load'

      AND PHASE_CODE='C' AND STATUS_CODE='C'

GROUP BY C.INSTANCE_CODE;

This has to be joined with ebs table FND_APPS_SYSTEM.NAME to get exact instance code