Introduction:
This query is used to fetch the list of concurrent program names which are having the changes at XML Publisher level (Data_Definition or Template Level) which also includes the LOB’s changes such as XML_Schema or Data Template or Bursting File or RTF_Template. In this query we will be getting the results for changes in last 60 days from sysdate or If we want we can set the date range or we can get to that specific user concurrent program by passing it in the query.
SQL Query:
SELECT fcp.user_concurrent_program_name “User_Conc_Program Name”,
fcp.last_update_date cp_last_udpate, fcp.enabled_flag “Enabled”,
fcp.concurrent_program_name “CP_Short_Name”,
fcp.description “Description”, fa.application_name “Application”,
fcp.output_file_type “Output_Format”,
fef.executable_name “Executable_Short_Name”,
flv.meaning “Executable_Method”,
fdfcu.last_update_date parameter_last_update,
fdfcu.column_seq_num “Sequence_Number”,
fdfcu.end_user_column_name “Parameter”,
fdfcu.description “Description”,
fdfcu.enabled_flag “Parameter_Status”,
fdfcu.required_flag “Parameter_Required”,
fdfcu.security_enabled_flag “Security_Enabled”,
fdfcu.display_flag “Parameter_Display”,
fvs.flex_value_set_name “Value_Set_Name”,
fdfcu.DEFAULT_VALUE “Default_Value”,
fdfcu.default_type “Default_Type”
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_executables_form_v fef,
apps.fnd_application_vl fa,
apps.fnd_descr_flex_col_usage_vl fdfcu,
apps.fnd_flex_value_sets fvs,
apps.fnd_lookup_values flv
WHERE fcp.executable_id = fef.executable_id
AND fcp.application_id = fa.application_id
AND flv.lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND fvs.flex_value_set_id = fdfcu.flex_value_set_id
AND fdfcu.descriptive_flexfield_name =
‘$SRS$.’ || fcp.concurrent_program_name
AND flv.lookup_code = fef.execution_method_code
–AND fcp.user_concurrent_program_name = :p
AND flv.LANGUAGE = ‘US’
–AND ( trunc (fdfcu.last_update_date) >= trunc(sysdate-60) OR trunc (fcp.last_update_date) >= trunc(sysdate-60) )
AND ( TRUNC (fdfcu.last_update_date) > ’29-AUG-2023′
OR TRUNC (fcp.last_update_date) > ’29-AUG-2023′
)
ORDER BY fdfcu.last_update_date DESC
Parameter:
User Concurrent Program Name Or Data Range Or Days (like 30,40, 45,)
