Advanced Search

Oracle - sql to find what responsibility can run a request / report

/ Published in: SQL
Save to your folder(s)
Very useful sql to find which responsibility has access to a report or request.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /* Formatted on 8/1/2013 1:24:38 PM (QP5 v5.215.12089.38647) */
  2.   SELECT --fr.responsibility_id,
  3.          frt.responsibility_name,
  4.          fr.responsibility_key,
  5.          fr.start_date,
  6.          fr.end_date,
  7.          frt.description,
  8.          --fr.request_group_id,
  9.          --rgu.application_id,
  10.          rg.request_group_name,
  11.          --rg.description rq_desc,
  12.          --rgu.request_unit_id,
  13.          rg.request_group_code,
  14.          --cp.concurrent_program_id,
  15.          CP.CONCURRENT_PROGRAM_NAME,
  16.          cp.enabled_flag program_enabled,
  17.          cpt.user_concurrent_program_name,
  18.          cpt.description ccp_desc
  19.     FROM fnd_responsibility_tl frt,
  20.          fnd_responsibility fr,
  21.          FND_REQUEST_GROUP_UNITS rgu                --appid, rqgid, uaid,rquid
  22.                                     ,
  23.          fnd_request_groups rg                           --appid,rqgid,rqgcode
  24.                               ,
  25.          fnd_concurrent_programs cp --application_id, concurrent_program_id, concurrent_program_name, enabled_flag
  26.                                    ,
  27.          FND_CONCURRENT_PROGRAMS_TL cpt -- application_id, concurrent_program_id, user_concurrent_program_name, description
  28.    WHERE     cp.application_id = cpt.application_id
  29.          AND cp.concurrent_program_id = cpt.concurrent_program_id
  30.          AND rgu.application_id = rg.application_id
  31.          AND cp.concurrent_program_id = rgu.request_unit_id
  32.          AND rgu.request_group_id = rg.request_group_id
  33.          -- does something, sometimes null for valid items
  34.          --and request_group_code is not null
  35.          AND fr.request_group_id = rg.request_group_id
  36.          AND frt.application_id = fr.application_id
  37.          AND frt.responsibility_id = fr.responsibility_id
  38.          --frt.responsibility_name like 'Mitre General Ledger Supervisor'
  39.          --and fr.responsibility_id = '20420'
  40.          AND rgu.request_unit_type = 'P'
  41.          --REQUEST GROUP
  42.          --and upper(rg.request_group_name) like upper(:REQ_GRP_NAME)
  43.          --PROGRAM READABLE NAME
  44.          AND UPPER (cpt.user_concurrent_program_name) LIKE UPPER (:CCP_FULL_NAME)
  45.          --PROGRAM SHORT NAME
  46.          --AND upper(cp.concurrent_program_name) LIKE upper(:CCP_SHRT_NAME)
  47. ORDER BY cp.concurrent_program_name

URL: http://www.michaelcoughlin.net/blog/index.php/2011/06/how-to-find-which-responsibility-can-run-a-request-in-oracle-apps/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.