Search This Blog

Wednesday, August 3, 2011

RequestSet+Stages+Programs+parameters (Oracle R12)

The query below lists Stages, programs and its parameters (only for particular type of program)

SELECT rs.request_set_id set_id,    --rsp.request_set_program_id,
  rs.user_request_set_name set_name,
  rstg.display_sequence seq, rstg.stage_name,
  rstgtl.user_stage_name,
  rsp.concurrent_program_id con_PROG_ID,
  CCP.user_concurrent_program_name,
  fccp.concurrent_program_name,
  (select Default_value from FND_REQUEST_SET_PROGRAM_ARGS x
  where x.request_set_id = rs.request_set_id
  and x.descriptive_flexfield_name = '$SRS$.XXOGL_F16_REPORT_FRAMEWORK'
  and x.application_column_name = 'ATTRIBUTE3' --the column used to store params
  and x.request_set_program_id = rsp.request_set_program_id
   ) "param: prog",
   (select Default_value from FND_REQUEST_SET_PROGRAM_ARGS x
  where x.request_set_id = rs.request_set_id
  and x.descriptive_flexfield_name = '$SRS$.XXOGL_F16_REPORT_FRAMEWORK'
  and x.application_column_name = 'ATTRIBUTE13'
  and x.request_set_program_id = rsp.request_set_program_id
   ) "param: parameter_list",
   (select Default_value from FND_REQUEST_SET_PROGRAM_ARGS x
  where x.request_set_id = rs.request_set_id
  and x.descriptive_flexfield_name = '$SRS$.XXOGL_F16_REPORT_FRAMEWORK'
  and x.application_column_name = 'ATTRIBUTE7'
  and x.request_set_program_id = rsp.request_set_program_id
   ) "param: destination"
FROM apps.fnd_request_Sets_tl rs,
  apps.FND_REQUEST_SET_STAGES RSTG,
  apps.fnd_REQUEST_SET_STAGES_tl rstgtl,
  apps.FND_REQUEST_SET_PROGRAMS RSP,
  apps.FND_CONCURRENT_PROGRAMS_TL CCP,
  apps.fnd_CONCURRENT_PROGRAMS fccp
WHERE RS.request_set_id = rstg.request_set_id
and rs.request_set_id = rstgtl.request_set_id
and rstg.request_set_id = rstgtl.request_set_id
and rstg.request_set_stage_id = rstgtl.request_set_stage_id
AND rs.request_set_id = rsp.request_set_id
AND rstg.request_set_stage_id = rsp.request_set_stage_id
AND rsp.concurrent_program_id = CCP.concurrent_program_id
and ccp.concurrent_program_id = fccp.concurrent_program_id
AND rs.user_request_set_name LIKE '<Request Set Name>' --'%F70 EOP%'
ORDER BY rstg.display_sequence

No comments:

Post a Comment