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

SQL Behind R12 Self Service Application Web (OA Framework) Page

In R12 with the Oracle’s new OA framework one can find the sql behind the html or web page. Oracle has given almost the same functionality in the “About this Page” link. If you want to find the SQL query behind any web page, you need to enable personalization and then use “About this Page” to find the view object. Below is an example for daily rates page from the GL module:

Step 1: Enable the personalization profile
   a) Under the system admin responsibility, open the system profiles form
   b) Change the “Personalize Self-Service Defn” profile to yes for the responsibility

Step 2: Perform the business process as you get into GL responsibility as below:
   a) Using the GL Super User responsibility, open the Daily Rates web page:
           >> setup >> Currencies >> Currency Rate Manager >> Daily Rates
   b) Find your daily rates for a combination of given options

Step 3: Click the About the Page link

Step 4: Click the Page tab and then click the Expand button on the business components section

Step 5: Find you view object and click the link

Step 6: Select the SQL query and copy it to your notepad (TOAD, SQL Developer,..)

Step 7: Replace any variables (anything that has a colon in front of it) with your data and test

Step 8: Modify the query and use it.