Return to Snippet

Revision: 48128
at August 2, 2013 04:17 by theonlyalterego


Updated Code
/* Formatted on 8/1/2013 1:24:38 PM (QP5 v5.215.12089.38647) */
  SELECT --fr.responsibility_id,
         frt.responsibility_name,
         fr.responsibility_key,
         fr.start_date,
         fr.end_date,
         frt.description,
         --fr.request_group_id,
         --rgu.application_id,
         rg.request_group_name,
         --rg.description rq_desc,
         --rgu.request_unit_id,
         rg.request_group_code,
         --cp.concurrent_program_id,
         CP.CONCURRENT_PROGRAM_NAME,
         cp.enabled_flag program_enabled,
         cpt.user_concurrent_program_name,
         cpt.description ccp_desc
    FROM fnd_responsibility_tl frt,
         fnd_responsibility fr,
         FND_REQUEST_GROUP_UNITS rgu                --appid, rqgid, uaid,rquid
                                    ,
         fnd_request_groups rg                           --appid,rqgid,rqgcode
                              ,
         fnd_concurrent_programs cp --application_id, concurrent_program_id, concurrent_program_name, enabled_flag
                                   ,
         FND_CONCURRENT_PROGRAMS_TL cpt -- application_id, concurrent_program_id, user_concurrent_program_name, description
   WHERE     cp.application_id = cpt.application_id
         AND cp.concurrent_program_id = cpt.concurrent_program_id
         AND rgu.application_id = rg.application_id
         AND cp.concurrent_program_id = rgu.request_unit_id
         AND rgu.request_group_id = rg.request_group_id
         -- does something, sometimes null for valid items
         --and request_group_code is not null
         AND fr.request_group_id = rg.request_group_id
         AND frt.application_id = fr.application_id
         AND frt.responsibility_id = fr.responsibility_id
         --frt.responsibility_name like 'Mitre General Ledger Supervisor'
         --and fr.responsibility_id = '20420'
         AND rgu.request_unit_type = 'P'
         --REQUEST GROUP
         --and upper(rg.request_group_name) like upper(:REQ_GRP_NAME)
         --PROGRAM READABLE NAME
         AND UPPER (cpt.user_concurrent_program_name) LIKE UPPER (:CCP_FULL_NAME)
         --PROGRAM SHORT NAME
         --AND upper(cp.concurrent_program_name) LIKE upper(:CCP_SHRT_NAME)
ORDER BY cp.concurrent_program_name

Revision: 48127
at January 14, 2012 03:13 by theonlyalterego


Updated Code
SELECT -- rg.application_id, rg.request_group_id, unit_application_id, request_unit_id, request_unit_type,
         request_group_name
--         , rg.description RG_DESC
         , cp.concurrent_program_name
         , cpt.user_concurrent_program_name
--         , cpt.description CCP_DESC
        , fr.responsibility_key
        ,frt.responsibility_name
    FROM FND_REQUEST_GROUP_UNITS rgu,
         fnd_request_groups rg,
         fnd_concurrent_programs cp,
         FND_CONCURRENT_PROGRAMS_TL cpt,
         FND_RESPONSIBILITY fr,
         FND_RESPONSIBILITY_TL frt
   WHERE     rg.request_group_id = rgu.request_group_id
         AND RGU.REQUEST_UNIT_ID = cp.concurrent_program_id
         AND cp.concurrent_program_id = cpt.concurrent_program_id
         AND rg.request_group_id = fr.request_group_id
         and frt.responsibility_id = fr.responsibility_id
         --and upper(rg.request_group_name)  like upper(:REQ_GRP_NAME)
         --and upper(cpt.user_concurrent_program_name) like upper(:CCP_FULL_NAME)
         AND upper(cp.concurrent_program_name) LIKE upper(:CCP_SHRT_NAME)
ORDER BY cp.concurrent_program_name

Revision: 48126
at January 14, 2012 03:07 by theonlyalterego


Updated Code
SELECT -- rg.application_id, rg.request_group_id, unit_application_id, request_unit_id, request_unit_type,
         request_group_name
--         , rg.description RG_DESC
         , cp.concurrent_program_name
         , cpt.user_concurrent_program_name
--         , cpt.description CCP_DESC
    FROM FND_REQUEST_GROUP_UNITS rgu,
         fnd_request_groups rg,
         fnd_concurrent_programs cp,
         FND_CONCURRENT_PROGRAMS_TL cpt
   WHERE     rg.request_group_id = rgu.request_group_id
         AND RGU.REQUEST_UNIT_ID = cp.concurrent_program_id
         AND cp.concurrent_program_id = cpt.concurrent_program_id
         --and upper(rg.request_group_name)  like upper(:REQ_GRP_NAME)
         --and upper(cpt.user_concurrent_program_name) like upper(:CCP_FULL_NAME)
         AND upper(cp.concurrent_program_name) LIKE upper(:CCP_SHRT_NAME)
ORDER BY cp.concurrent_program_name

Revision: 48125
at June 24, 2011 05:50 by theonlyalterego


Initial Code
SELECT FRT.RESPONSIBILITY_NAME, FRG.REQUEST_GROUP_NAME, FRG.DESCRIPTION
  FROM FND_REQUEST_GROUPS FRG
      ,FND_REQUEST_GROUP_UNITS FRGU
      ,FND_CONCURRENT_PROGRAMS FCP
      ,FND_CONCURRENT_PROGRAMS_TL FCPT
      ,FND_RESPONSIBILITY_TL FRT
      ,FND_RESPONSIBILITY FR
 WHERE     FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
       AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
       AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
       AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
       AND FCPT.SOURCE_LANG = USERENV('LANG')
       AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
       AND FCP.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
       AND FR.APPLICATION_ID = FRT.APPLICATION_ID
       AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
       AND FRT.SOURCE_LANG = USERENV('LANG')
       AND FR.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
       AND FR.APPLICATION_ID = FRG.APPLICATION_ID
       --   AND FCP.CONCURRENT_PROGRAM_NAME = 'OPMTMOPG'  --  YOU CAN PUT THE SHORTNAME HERE
       AND FCPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'Purge OPM Txns and Move Order Lines' --OR THE USER CONC PROGRAM NAME HERE
;

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

Initial Description
Very useful sql to find which responsibility has access to a report or request.

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

Initial Tags
sql, Oracle

Initial Language
SQL