AOL Queries

--SQL To List All Oracle Applications With Its Version
SELECT fa.application_short_name,
  fat.application_name,
  fpi.product_version
FROM apps.fnd_application fa,
  apps.fnd_application_tl fat,
  apps.fnd_product_installations fpi
WHERE fa.application_id = fpi.application_id
AND fa.application_id   = fat.application_id
and fat.language        = userenv('LANG')
order by application_short_name;

--Query to Find Concurrent Program Responsibilities From Which It Can be Run
SELECT DISTINCT fcpt.user_concurrent_program_name,
  fcpt.description,
  frt.responsibility_name,
  request_group_name
FROM apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_responsibility fr,
  apps.fnd_responsibility_tl frt
WHERE fcpt.concurrent_program_id = frgu.request_unit_id
AND frg.request_group_id         = frgu.request_group_id
AND frg.request_group_id         = fr.request_group_id
AND fr.responsibility_id         = frt.responsibility_id
AND fcpt.application_id          = frg.application_id
AND frg.application_id           = frgu.application_id
and fr.application_id            = frt.application_id
AND fcpt.user_concurrent_program_name LIKE 'XX%'--'%&p_conc_programe_name%';

--Query to find Responsibilities attached to a User
SELECT DISTINCT fu.user_id,
 fu.user_name user_name,
 fr.responsibility_id,
 fr.responsibility_name responsiblity,
 fa.application_name application
FROM apps.fnd_user fu,
 apps.fnd_user_resp_groups furg,
 apps.fnd_application_tl fa,
 apps.fnd_responsibility_tl fr
WHERE fu.user_name LIKE UPPER('&username')
AND fu.user_id = furg.user_id
and furg.responsibility_id = fr.responsibility_id
ORDER BY 1;

--Query To Find Value Sets Details
SELECT ffvs.flex_value_set_id,
  ffvs.flex_value_set_name,
  ffvs.description set_description ,
  fl.meaning validation_type,
  ffvt.value_column_name ,
  ffvt.meaning_column_name ,
  ffvt.id_column_name ,
  ffvt.application_table_name ,
  ffvt.additional_where_clause
FROM apps.fnd_flex_value_sets ffvs ,
  apps.fnd_flex_validation_tables ffvt,
  apps.fnd_lookups fl
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id(+)
AND ffvs.flex_value_set_name LIKE NVL('&VALUE_SET_NAME_CASE_SENSITIVE','%')
and fl.lookup_type = 'SEG_VAL_TYPES'
AND fl.lookup_code = ffvs.validation_type

--Query to get Create Table Statement for an Existing Table
select dbms_metadata.get_ddl('INDEX','XX_TEMP_TABLE_N1','APPS') from dual;
or

SELECT dbms_metadata.get_ddl('TABLE','PER_ALL_PEOPLE_F','APPS') FROM dual;

--SQL to Get Values at All Levels for a Profile Option
SELECT fpot.user_profile_option_name  "Profile"
       ,fpov.profile_option_value     "Value"
       ,decode(fpov.level_id
             ,10001,'Site'
             ,10002,'Application'
             ,10003,'Responsibility'
             ,10004,'User'
             ,'UNKNOWN')              "Level"
       ,decode(fpov.level_id
             ,10002,fat.application_name
             ,10003,frt.responsibility_name
             ,10004,fu.user_name
             ,'n/a')                  "Level value"
FROM  applsys.fnd_application_tl         fat
      ,applsys.fnd_user                  fu
      ,applsys.fnd_responsibility_tl     frt
      ,applsys.fnd_profile_option_values fpov
      ,applsys.fnd_profile_options       fpo
      ,applsys.fnd_profile_options_tl    fpot
WHERE    UPPER(fpot.user_profile_option_name) LIKE '%&profile_name_case_sensitive%'
  AND    fpo.hierarchy_type = 'SECURITY'
  AND    fpot.profile_option_name   = fpo.profile_option_name
  AND    fpot.LANGUAGE              = 'US'
  AND    fpo.application_id         = fpov.application_id(+)
  AND    fpo.profile_option_id      = fpov.profile_option_id(+)
  AND    fpov.level_value           = frt.responsibility_id(+)
  AND    frt.LANGUAGE(+)            = 'US'
  AND    fpov.level_value           = fu.user_id(+)
  AND    fpov.level_value           = fat.application_id(+)
  AND    fat.LANGUAGE(+)            = 'US'
ORDER  BY "Profile"
          ,"Level"
          ,"Level value"
          ,"Value";