Useful query

1) To find the default org id and org_name for certain responsibility:

select name from  HR_ALL_ORGANIZATION_UNITS where organization_id = fnd_profile.value('ORG_ID')

2)   to find the default reporting level:

SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'XLA_MO_REPORTING_LEVEL' AND LOOKUP_CODE = (SELECT DECODE(NVL(MULTI_ORG_FLAG,'N'), 'N','1000', 'Y','3000') FROM FND_PRODUCT_GROUPS)

3) To find the default reporting Context:

SELECT o.name  FROM gl_sets_of_books g, hr_organization_units o,      hr_organization_information o2, hr_organization_information o3  where o.organization_id =  DECODE(:$FLEX$.XLA_MO_REPORTING_LEVEL,'2000', TO_NUMBER(o3.org_information2),'3000',o3.organization_id) and o3.org_information3=g.set_of_books_id and o2.organization_id   = o3.organization_id and o2.org_information_context || '' = 'CLASS'   and o3.org_information_context ='Operating Unit Information' and  o2.org_information1='OPERATING_UNIT' and  o2.org_information2='Y' and TO_CHAR(o2.organization_id) =  nvl(fnd_profile.value_wnps('ORG_ID'),-99)  UNION  SELECT xla.entity_name FROM xla_mo_reporting_entities_v  xla  WHERE xla.reporting_level =  :$FLEX$.XLA_MO_REPORTING_LEVEL  AND xla.reporting_level = '1000' AND xla.entity_id = :$PROFILES$.gl_set_of_bks_id

4) To find the default Set Of Book currency:

select currency_code

from gl_sets_of_books

where set_of_books_id = DECODE(:$FLEX$.XLA_MO_REPORTING_LEVEL,

'1000',:$FLEX$.XLA_MO_AR_CA_REPORTING_ENTITY,:$PROFILES$.gl_set_of_bks_id)

and not exists

(select 'x'

from gl_mc_reporting_options gmro1,

gl_mc_reporting_options gmro2

where  (gmro1.primary_currency_code = gmro2.reporting_currency_code OR

gmro1.reporting_currency_code = gmro2.reporting_currency_code)

and  gmro1.enabled_flag = gmro2.enabled_flag

and gmro1.application_id = gmro2.application_id

and gmro1.reporting_option_id <> gmro2.reporting_option_id

and gmro1.enabled_flag = 'Y'

and gmro1.application_id =222

and gmro1.primary_set_of_books_id = :$PROFILES$.gl_set_of_bks_id

and gmro1.primary_set_of_books_id = gmro2.primary_set_of_books_id

and :$FLEX$.XLA_MO_REPORTING_LEVEL <> '1000')

5) To find the default chart of accounts:

select sb.chart_of_accounts_id   from gl_sets_of_books sb, ar_system_parameters sp  where sb.set_of_books_id = sp.set_of_books_id

6) The query to find the current concurrent program:

  v_program_id := fnd_profile.value('CONC_PROGRAM_ID') ;
        select user_concurrent_program_name
        into v_program_name
        from fnd_concurrent_programs_vl
        where concurrent_program_id = v_program_id;

7) The quey to find the current responsibility:

    v_resp_id := fnd_profile.value('RESP_ID') ;
        select responsibility_name
        into v_resp_name
        from fnd_responsibility_vl
        where responsibility_id = v_resp_id;

8) To find the current environment:

select fnd_profile.value('SITENAME') from dual;

9) To find the current username:

select fnd_profile.value('USERNAME') from dual;

10) The relationship of responsibility, menus, funcitons:
 

SELECT --fu.user_name,
--fu.description,
--furg.start_date,
frvl.responsibility_name,
frvl.RESPONSIBILITY_KEY,
frvl.RESPONSIBILITY_ID,
--fff.function_name,
fff.USER_FUNCTION_NAME,
--ff.form_name,
--ff.USER_FORM_NAME,
--frvl.RESPONSIBILITY_KEY,
fa.APPLICATION_short_name,
fcmf.MENU_ID
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
fnd_form_vl ff
,FND_APPLICATION fa
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id(+)
and fcmf.GRANT_FLAG='Y'
and fa.APPLICATION_ID = frvl.application_id
--AND (frvl.responsibility_name LIKE '%Project%')
AND frvl.responsibility_name = 'Project Finance CAE Inc'
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
ORDER BY frvl.responsibility_name;