AOL Queries

How to find the Responsibilities of a particular Concurrent Program

SELECT DISTINCT frg.request_group_id,
                frg.request_group_name,
                frgu.REQUEST_UNIT_ID,
                fcp.USER_CONCURRENT_PROGRAM_NAME,
                fcp.CONCURRENT_PROGRAM_NAME,
                fcp.CONCURRENT_PROGRAM_ID,
                frvl.RESPONSIBILITY_NAME
  FROM FND_REQUEST_GROUPS frg,
       FND_REQUEST_GROUP_UNITS frgu,
       FND_CONCURRENT_PROGRAMS_VL fcp,
       FND_RESPONSIBILITY_VL frvl
 WHERE     frg.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
       AND frg.REQUEST_GROUP_ID = frvl.REQUEST_GROUP_ID
       AND frgu.request_unit_type = 'P'         --P for Program, S for Set
       AND fcp.CONCURRENT_PROGRAM_ID = frgu.REQUEST_unit_ID
       AND fcp.CONCURRENT_PROGRAM_NAME LIKE :cpn --Prorgaram Short Name
       AND fcp.USER_CONCURRENT_PROGRAM_NAME like :ucpn --Conc Program Name
--//--------------------------------------------------------------------------------------------------------
--//Query To Fetch the Forms And Functions along with Prompt Names
--//--------------------------------------------------------------------------------------------------------
select distinct Prompt,
function_name,
user_function_name,
form_name,
user_form_name 
from FND_FORM_FUNCTIONS_VL ffv,
fnd_form_vl fv,
 fnd_menu_entries_vl fm
where fv.form_id = ffv.form_id  
and fm.function_id = ffv.function_id
AND prompt is not null
and ffv.application_id = 20003


--//---------------------------------------------------------------------------------------------------------------------- 
--// To Search the Form Names and Menu Names for a perticular Form/Function/Menu
--//----------------------------------------------------------------------------------------------------------------------
select distinct Prompt,Menu_name,
function_name,
user_function_name,
form_name,
user_form_name 
from FND_FORM_FUNCTIONS_VL ffv,
fnd_form_vl fv,
 fnd_menu_entries_vl fm,
 fnd_menus fmn,
 fnd_application fa
where fv.form_id = ffv.form_id  
and fm.function_id = ffv.function_id
AND fmn.menu_id = fm.menu_id
AND prompt is not null
AND form_name = NVL(:form_name,form_name)
AND function_name = NVL(:function_name,form_name)
AND user_form_name = NVL(:user_form_name,user_form_name)
AND user_function_name = NVL(:user_function_name,user_form_name)
AND prompt = NVL(:prompt,prompt)
AND menu_name = NVL(:menu_name,menu_name)
and ffv.application_id = fa.application_id--20003
AND fa.application_short_name = 'XXARS'

--//----------------------------------------------------------------------------   
--//To Find the Responsibility of a concurrent Program
--//----------------------------------------------------------------------------
select
frt.* from fnd_request_group_units frgu, 
fnd_concurrent_programs_tl fcp
, fnd_request_groups frg
, fnd_Responsibility_tl frt
, fnd_Responsibility fr
where frgu.request_unit_id = fcp.concurrent_program_id 
and frg.request_group_id = frgu.request_group_id 
and fr.responsibility_id = frt.responsibility_id 
and fr.request_group_id = frgu.request_group_id 
and user_concurrent_program_name like :cp_name 
and frt.language = fcp.language 
and fcp.language = 'US'

--//-----------Using the Program Short Name--------------------

select
frt.* from fnd_request_group_units frgu, 
fnd_concurrent_programs fcp
, fnd_request_groups frg
, fnd_Responsibility_tl frt
, fnd_Responsibility fr
where frgu.request_unit_id = fcp.concurrent_program_id 
and frg.request_group_id = frgu.request_group_id 
and fr.responsibility_id = frt.responsibility_id 
and FR.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID 
and CONCURRENT_PROGRAM_NAME like :CP_SHORT_NAME
--and FRT.LANGUAGE = FCP.LANGUAGE 

and frt.language = 'US';

--//---------------------------------------------------------------------
--//To find the Responsibility of Request Set----------
--//---------------------------------------------------------------------
SELECT UNIQUE frt.responsibility_name,
  frg.request_group_name,
  frsv.user_request_set_name
FROM fnd_responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_request_sets_vl frsv
WHERE fr.request_group_id = frg.request_group_id
AND fr.responsibility_id = frt.responsibility_id
AND frg.request_group_id = frgu.request_group_id
AND frgu.request_unit_id = frsv.request_set_id
AND frsv.user_request_set_name LIKE 'Aleris OMP to Oracle Batch Creation Request Set';



How to find  the Responsibility and User to which it is assigned

SELECT DISTINCT u.user_name,
rtl.responsibility_name,
  ff.function_name,
ffl.user_function_name
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_tl rtl,
fnd_user_resp_groups urg,
fnd_user u
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND r.application_id = urg.responsibility_application_id
AND u.user_id = urg.user_id
AND UPPER (ffl.user_function_name) = UPPER ('Batch Progression Sales Order') --XXARS_BATCH_PRO_SO
AND ff.function_id = ffl.function_id
ORDER BY u.user_name
       
How to find  the Responsibilities

SELECT d.responsibility_name
FROM fnd_menu_entries a,
fnd_menu_entries_vl b,
fnd_form_functions c,
fnd_responsibility_vl d
WHERE a.menu_id = b.menu_id
AND a.function_id = b.function_id
AND b.function_id = c.function_id
AND a.menu_id = d.menu_id
AND c.function_name = :function_name


How to find the Responsibilities for a given request set

SELECT DISTINCT frg.request_group_id,
                frg.request_group_name,
                frgu.REQUEST_UNIT_ID,
                fcp.REQUEST_SET_NAME,
                fcp.REQUEST_SET_ID,
                frvl.RESPONSIBILITY_NAME
  FROM FND_REQUEST_GROUPS frg,
       FND_REQUEST_GROUP_UNITS frgu,
       FND_REQUEST_SETS fcp,
       FND_RESPONSIBILITY_VL frvl
 WHERE     frg.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
       AND frg.REQUEST_GROUP_ID = frvl.REQUEST_GROUP_ID
       AND frgu.request_unit_type = 'S'             --P for Program, S for Set
       AND fcp.REQUEST_SET_ID = frgu.REQUEST_unit_ID
       AND fcp.REQUEST_SET_NAME LIKE 'XX%'

How to find the Assigned Users for a given Responsibility

SELECT DISTINCT fu.user_name,
                fu.email_address,
                papf.full_name,
                papf.employee_number,
                fr.responsibility_key
  FROM fnd_logins fl,
       fnd_login_responsibilities flr,
       fnd_responsibility fr,
       fnd_responsibility_tl frt,
       fnd_user fu,
       per_all_people_f papf
 WHERE     flr.responsibility_id = fr.responsibility_id
       AND flr.login_id = fl.login_id
       AND fu.user_id = fl.user_id
       AND fr.responsibility_id = frt.responsibility_id
       AND papf.person_id = fu.employee_id
       AND frt.responsibility_name LIKE 'XX%AP%SUPER%'

Query to Get all the Responsibilities of a Function

SELECT frt.responsibility_name,
       fm.menu_id,
       fm.menu_name,
       ffv.function_name,
       fmv.prompt
  FROM fnd_form_functions_vl ffv, 
       fnd_menu_entries_vl fmv, 
       fnd_menus fm,
       fnd_responsibility fr,
       fnd_responsibility_tl frt
 WHERE     ffv.function_name = 'XXD2DEXT221GBL'
       AND ffv.function_id = fmv.function_id
       AND frt.responsibility_id = fr.responsibility_id
       AND fr.menu_id = fm.menu_id
       AND fm.menu_id = fmv.menu_id

       AND frt.language = 'US';

Deleting Concurrent Programs

  BEGIN
       fnd_program.delete_program (program_short_name => 'XXD2DITI714AA', -- Conc. Executable Short Name
                                                  application => 'XXAMW' -- Program Application Short Name
                                                           );
    COMMIT;

END;

BEGIN
  fnd_program.delete_executable (executable_short_name => 'XXD2DITI714AA', -- Conc. Executable Short Name
                                                 application => 'XXAMW' -- Program Application Short Name
                                                    );
   COMMIT;
END;