SQL to Get Request Groups and Some Useful Scripts

SQL query to get the Request Groups assigned to a Conc Program:
SELECT
   RG.APPLICATION_ID "Request Group Application ID",
   RG.REQUEST_GROUP_ID "Request Group - Group ID",
   RG.REQUEST_GROUP_NAME,
   RG.DESCRIPTION,
   rgu.unit_application_id,
   rgu.request_group_id "Request Group Unit - Group ID",
   rgu.request_unit_id,cp.concurrent_program_id,
   cp.concurrent_program_name,
   cpt.user_concurrent_program_name,
   DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
   fnd_request_groups rg,
   fnd_request_group_units rgu,
   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(cpt.user_concurrent_program_name) like '%UNACCOUNTED%';

Query to Check Menu and Request Group attached to a Reponsilblity:

SELECT DISTINCT
               a.responsibility_name,
               c.user_menu_name,
               (SELECT request_group_name
                 FROM apps.fnd_request_groups g
                WHERE g.application_id = b.application_id
                  AND g.request_group_id = b.request_group_id) requet_grp
FROM apps.fnd_responsibility_tl a,
            apps.fnd_responsibility b,
            apps.fnd_menus_tl c,
            apps.fnd_menus d,
            apps.fnd_application_tl e,
            apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
    --AND a.responsibility_id = '20538'
      AND b.menu_id = c.menu_id
      AND b.menu_id = d.menu_id
      AND e.application_id = f.application_id
      AND f.application_id = b.application_id
      AND a.LANGUAGE = 'US';

API to add a request group:
If you want to script your request group in sql, rather than FNDLOAD, to be more dynamic… here’s a sample of the API… the api will fail if the program is already in the group, thus the IF check so the script can be run multiple times without failing after the first. Just thought I’d suggest an alternative to FNDLOAD, as below:


BEGIN
IF (NOT FND_PROGRAM.PROGRAM_IN_GROUP('ARACCPB', 'Receivables','F5 Receivables All', 'Receivables')) THEN
   dbms_output.put_line('Assigning ARACCPB to request group');
   FND_PROGRAM.ADD_TO_GROUP(
                          program_short_name => 'ARACCPB',
                          program_application => 'Receivables',
                          request_group => 'F5 Receivables All',
                          group_application => 'Receivables');
else
    dbms_output.put_line('ARACCPB already in request group');
END IF;
END;
/
commit
/

Seeded Report Set Used to Know Full Functional Details of a Responsibility:
Function Security Reports (Report Set)
- Function Security Function Report
- Function Security Menu Report
- Function Security Navigator Report

Params to be submitted: Application and Responsibility

Some useful scripts Available with the following link; also work in R12:
http://oracle.anilpassi.com/some-scripts-contributed-by-ahmad-bilal-16.html