Common Queries - Responsibility

List of Responsibilities

SELECT (SELECT application_short_name
FROM   fnd_application fa
WHERE fa.application_id = frt.application_id) application,
          frt.responsibility_id, frt.responsibility_name
FROM  apps.fnd_responsibility_tl frt;

Menus associated with Responsibility

SELECT DISTINCT a.responsibility_name, c.user_menu_name
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_name p_responsbility_name
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';

Sub Menus and Function attached to this Main Menu

SELECT c.prompt, c.description
FROM    apps.fnd_menus_tl a, fnd_menu_entries_tl c
WHERE  a.menu_id             = c.menu_id
AND      a.user_menu_name = p_menu_name;

Assigned Responsibility to a User

SELECT  DISTINCT
            u.user_id ,SUBSTR (u.user_name, 1, 30)  user_name
           ,SUBSTR (r.responsibility_name, 1, 60)     responsibility
           ,SUBSTR (a.application_name, 1, 50)       application
FROM    fnd_user u,
           fnd_user_resp_groups g,
           fnd_application_tl a,
           fnd_responsibility_tl r
WHERE  g.user_id(+)                         =   u.user_id
AND      g.responsibility_application_id  =   a.application_id
AND      a.application_id                     =   r.application_id
AND      g.responsibility_id                  =   r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

Responsibility and attached Request Groups

SELECT    responsibility_name responsibility,request_group_name,frg.description
FROM       fnd_request_groups frg, fnd_responsibility_vl frv
WHERE     frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Request attached to Responsibility Listing

SELECT    responsibility_name, frg.request_group_name
             ,fcpv.user_concurrent_program_name, fcpv.description
FROM       fnd_request_groups frg, fnd_request_group_units frgu,
              fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv
WHERE     frgu.request_unit_type  =  'P'
AND         frgu.request_group_id   =  frg.request_group_id
AND         frgu.request_unit_id     =  fcpv.concurrent_program_id
AND         frv.request_group_id    =  frg.request_group_id
ORDER BY responsibility_name;

All Request with Application

SELECT  fa.application_short_name, fcpv.user_concurrent_program_name,
            description,DECODE (fcpv.execution_method_code,
            'B', 'Request Set Stage Function',
            'Q', 'SQL*Plus',
            'H', 'Host',
            'L', 'SQL*Loader',
            'A', 'Spawned',
            'I', 'PL/SQL Stored Procedure',
            'P', 'Oracle Reports',
            'S', 'Immediate',
            fcpv.execution_method_code
            ) exe_method,
            output_file_type, program_type, printer_name,minimum_width,
            minimum_length, concurrent_program_name,concurrent_program_id
FROM    fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE  fcpv.application_id = fa.application_id
ORDER BY description

Request Status Listing

SELECT  f.request_id
           ,pt.user_concurrent_program_name user_concurrent_program_name
           ,f.actual_start_date actual_start_date
           ,f.actual_completion_date actual_completion_date
           ,floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
           || ' HOURS ' ||
           floor((((f.actual_completion_date-f.actual_start_date)*24 *60*60)
           - floor(((f.actual_completion_date-f.actual_start_date)*24*60 *60)/3600)
                                                                                               *3600)/60)
           || ' MINUTES ' ||
            round((((f.actual_completion_date-f.actual_start_date)*24*60*60)
           - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/ 3600)
                                                                                                      *3600
           - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)
           - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
                                                                                     *3600)/60)*60) ))
           || ' SECS ' time_difference,
DECODE
           (p.concurrent_program_name,'ALECDC'
           ,p.concurrent_program_name||'['||f.description||']'
           ,p.concurrent_program_name) concurrent_program_name
           ,decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
           ,f.status_code
FROM    apps.fnd_concurrent_programs p
           ,apps.fnd_concurrent_programs_tl pt
           ,apps.fnd_concurrent_requests f
WHERE  f.concurrent_program_id = p.concurrent_program_id
AND      f.program_application_id = p.application_id
AND      f.concurrent_program_id = pt.concurrent_program_id
AND      f.program_application_id = pt.application_id
AND      pt.language = USERENV('Lang')
AND      f.actual_start_date IS NOT NULL;