Parameters of a Scheduled Request Set

At my site, few users have scheduled a request set with different parameters. If support staff needs to hold a particular instance of that request set then there is no ways to identify that request. Oracle does not provide a mechanism to view parameters to a scheduled request set. Many times support staff guess based on "Requested By" of request set. So I wrote following query that can give me parameters to all scheduled instances of a request set.

In the query below, I have provision for 20 parameters only. there could be up to 100 parameters. If there are more parameters to any concurrent program of a request set, then you need to modify DECODE and add sql statements for remaining parameters.

SELECT "Request Set Name" , Request_id "Request ID" , "Stage Seq" , "Program Seq" , "Concurrent Program Name" , "Parameter Name" , DECODE(num, 1, (SELECT argument1 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 2, (SELECT argument2 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 3, (SELECT argument3 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 4, (SELECT argument4 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 5, (SELECT argument5 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 6, (SELECT argument6 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 7, (SELECT argument7 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 8, (SELECT argument8 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 9, (SELECT argument9 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 10, (SELECT argument10 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 11, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 12, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 13, (SELECT argument13 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 14, (SELECT argument14 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 15, (SELECT argument15 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 16, (SELECT argument16 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 17, (SELECT argument17 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 18, (SELECT argument18 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 19, (SELECT argument19 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 20, (SELECT argument20 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 'Add Additional Sql to get parametervalues' ) "Parameter Value" FROM (SELECT rs.user_request_set_name "Request Set Name" , req.request_id Request_ID , rss.display_sequence "Stage Seq" , rsp.sequence "Program Seq" , cp.user_concurrent_program_name "Concurrent Program Name" , cp_param.column_seq_num "Param Seq" , form_left_prompt "Parameter Name" , rs.REQUEST_SET_ID , cp.concurrent_program_id , row_number () over (partition by req.request_id, CP.CONCURRENT_PROGRAM_ID order by cp_param.column_seq_num )num FROM apps.fnd_request_sets_vl rs , apps.fnd_req_set_stages_form_v rss , applsys.fnd_request_set_programs rsp , apps.fnd_concurrent_programs_vl cp , apps.fnd_descr_flex_col_usage_vl cp_param , fnd_concurrent_requests req , fnd_concurrent_programs rset_prog WHERE rs.user_request_set_name LIKE '<User Request Set Name>' AND rs.application_id = rss.set_application_id AND rs.request_set_id = rss.request_set_id AND rss.set_application_id = rsp.set_application_id AND rss.request_set_id = rsp.request_set_id AND rss.request_set_stage_id = rsp.request_set_stage_id AND rsp.program_application_id = cp.application_id AND rsp.concurrent_program_id = cp.concurrent_program_id AND rs.end_date_active IS NULL AND cp_param.descriptive_flexfield_name (+) = '$SRS$.' || cp.concurrent_program_name AND cp_param.application_id (+) = cp.application_id AND cp_param.enabled_flag (+) = 'Y' AND req.concurrent_program_id = rset_prog.concurrent_program_id AND req.program_application_id = rset_prog.application_id AND req.phase_code = 'P' AND rset_prog.concurrent_program_name = 'FNDRSSUB' AND req.argument1 = to_char(rs.application_id) AND req.argument2 = to_char(rs.request_set_id) ORDER BY req.request_id , rss.display_sequence , rsp.sequence , cp_param.column_seq_num ) a;