1) To find the default org id and org_name for certain responsibility: 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) 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 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:
select name from HR_ALL_ORGANIZATION_UNITS where organization_id = fnd_profile.value('ORG_ID')
2) to find the default reporting level:
3) To find the default reporting Context:
4) To find the default Set Of Book currency:
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:
--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;