Tuesday, October 31, 2017

Query to Get Request Set Details

Query to Get Concurrent Programs assigned to Request Set
  SELECT rs.user_request_set_name "Set   Name",
         rs.request_set_name "Set   code",
         rs.description "Description",
         rss.display_sequence Seq,
         cp.user_concurrent_program_name "Concurrent Program",
         ap.application_name "Application",
         e.executable_name "Executable",
         e.execution_file_name "Executable File",
         lv.meaning "Executable Type"
    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_executables E,
         apps.fnd_lookup_values lv,
         apps.fnd_application_vl ap
   WHERE     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 cp.executable_id = e.executable_id
         AND cp.executable_application_id = e.application_id
         AND e.application_id = ap.application_id
         AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
         AND lv.lookup_code = e.execution_method_code
         AND rs.user_request_set_name like 'XXAA Accounts Payable Request Set'
         AND lv.LANGUAGE = 'US'
ORDER BY rss.display_sequence


Query to Get Request Set Assigned to Request Group(Responsibility)
SELECT
  frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_request_set_name
FROM
  apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_request_Sets_tl fcpt
WHERE 1=1
  AND frt.responsibility_id = fr.responsibility_id
  AND frg.request_group_id = fr.request_group_id
  AND frgu.request_group_id = frg.request_group_id
  AND fcpt.request_set_id = frgu.request_unit_id
  AND frt.LANGUAGE = USERENV ('LANG')
  AND fcpt.LANGUAGE = USERENV ('LANG')
  AND fcpt.user_request_set_name like ' XXAA Accounts Payable Request Set '
ORDER BY 1,2,3,4



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect