ASCP QUERY TO FETCH LATEST PLAN AND COLLECTION RUN DATES

Query to fetch Plan Latest run date:

SELECT MAX(ACTUAL_COMPLETION_DATE),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 ARGUMENT1 = '' --INTANCE_CODE AND ARGUMENT2='' -- PLAN_ID
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 and plan

-------------------------------------------------------------------------------------------------------------------------------------------------------
Query to fetch Collection Latest run date:

SELECT MAX(ACTUAL_COMPLETION_DATE),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 DATA PULL'
--AND ARGUMENT1 = '' --INTANCE_ID NO PLAN_ID ATTRIBUTE FOR THIS CONCURRENT PROGRAM
AND PHASE_CODE='C' AND STATUS_CODE='C'
GROUP BY C.INSTANCE_CODE
;

SELECT MAX(ACTUAL_COMPLETION_DATE),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 ARGUMENT1 = '' --INTANCE_ID NO PLAN_ID ATTRIBUTE FOR THIS CONCURRENT PROGRAM
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 and plan