Wednesday, 7 December 2011

How to Find Custom Concurrent Program Name in a Single Query

SELECT a.cpname, a.UCPNAME, a.param, f.FLEX_VALUE_SET_NAME FROM
(
select V.CONCURRENT_PROGRAM_NAME CPNAME, V.USER_CONCURRENT_PROGRAM_NAME UCPNAME, nvl(VC.END_USER_COLUMN_NAME,'NULL PARAM') param,
NVL(vc.FLEX_VALUE_SET_ID,0) valueset
from FND_CONCURRENT_PROGRAMS_VL v,
FND_DESCR_FLEX_COL_USAGE_VL VC
where V.USER_CONCURRENT_PROGRAM_NAME like 'XX%' AND V.APPLICATION_ID = VC.APPLICATION_ID(+)
AND VC.DESCRIPTIVE_FLEXFIELD_NAME(+) = '$SRS$.'||V.CONCURRENT_PROGRAM_NAME
) a,
fnd_flex_value_sets f
where a.valueset = f.flex_value_set_id(+)