Oracle Apps SQL Scripts

Queries for Value Sets

--Value Sets based on table:
This Query gives details of value sets that are based on a oracle application tables.

select ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffvt.value_column_name ,
    ffvt.meaning_column_name ,
    ffvt.id_column_name ,
    ffvt.application_table_name ,
    ffvt.additional_where_clause
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;

Independent Value set Details:
This query gives details of independent FND Value sets i.e. Values are static and these are not derived from any application table.

SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');

Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

Get Apps Environment Details From Database

APPLSYS. FND_APPL_TOPS:
This table tracks the mount points for the APPL_TOPs in an Applications system. Each mount point has a distinct host and path.

select
        name,
        node_id,
        path,
        shared,
        created_by,
        creation_date,
        last_updated_by,
        last_update_date,
        FILE_SYSTEM_GUID,
        appl_top_guid
FROM    APPLSYS.FND_APPL_TOPS;


APPLSYS. FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.

select  server_type,
        name,
        creation_date,
        node_id
from applsys.fnd_app_servers;

APPLSYS. FND_ENV_CONTEXT:
This table stores information about environment name and value for each of the concurrent process.

select  variable_name,
        value
from   fnd_env_context
where  variable_name like '%\_TOP'
and    concurrent_process_id =
( select max(concurrent_process_id)
from fnd_env_context )
order by 1;


Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"

FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv

WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;