Queries Related to Responsibilities

Operating Units and Inventory Orgs

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
   
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Concurrent Program/Set attached to Request Groups

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
  apps.fnd_request_groups rg,
  apps.fnd_request_group_units rgu,
  apps.fnd_concurrent_programs cp,
  apps.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 cpt.user_concurrent_program_name = 'JDSU CM Pack Slip - US'


Menu attached to Responsibilities:

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_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';

Form Function attached to Responsibilities:

List of Responsibilities associated with Form Functions

SELECT DISTINCT responsibility_id
FROM apps.fnd_responsibility_vl a
WHERE a.end_date IS NULL
AND a.menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (SELECT menu_id FROM apps.fnd_menu_entries_vl
WHERE function_id IN (SELECT function_id FROM applsys.fnd_form_functions a
WHERE function_name = pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)
AND a.responsibility_id NOT IN (SELECT responsibility_id FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (SELECT responsibility_id FROM applsys.fnd_resp_functions resp
WHERE action_id IN (SELECT function_id FROM applsys.fnd_form_functions a
WHERE function_name = pc_function_name)))
AND a.responsibility_id NOT IN (SELECT responsibility_id FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (SELECT responsibility_id FROM applsys.fnd_resp_functions resp
WHERE action_id IN (SELECT menu_id FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (SELECT menu_id FROM apps.fnd_menu_entries_vl
WHERE function_id IN (SELECT function_id FROM applsys.fnd_form_functions a
WHERE function_name = pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)))
ORDER BY responsibility_id



Ur's
AmarAlam