Showing posts with label FND. Show all posts

FND: Query to select user and responsibility details associated to an Application(FND) user

SELECT u.user_name,

u.start_date user_start_date,

u.end_date user_end_date,

u.email_address user_email_address,

r.responsibility_name,

g.start_date resp_assign_start_date,

g.end_date resp_assign_end_date,

g.user_id,

r.responsibility_id

FROM apps.fnd_user u,

apps.fnd_user_resp_groups g,

apps.fnd_responsibility_tl r

WHERE 1 = 1

AND r.responsibility_id = g.responsibility_id

AND g.user_id = u.user_id

AND u.user_name = NVL(:p_user_name, u.user_name)

AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)

ORDER BY u.user_name, r.responsibility_name DESC;

Query to find DB_LINK in an Oracle instance

SELECT obj.object_type         "Object Type",
       obj.owner               "Object Owner",
       obj.object_name         "Object Name",
       obj.status              "Object Status",
       dbl.db_link             "DB Link",
       dbl.username            "DB Link",
       dbl.host                "DB Host"
FROM dba_objects obj,
       dba_db_links dbl
WHERE obj.object_name = dbl.db_link
   AND obj.object_type = 'DATABASE LINK';

Finding concurrent request status related information

The following query finds the concurrent process status and its related information (such as, completion phase, responsibility used, which user submitted the program, etc.).

In the following example, "Autoinvoice Import Program" as Concurrent Program Name. This is well tested in R12.1.

SELECT
       fu.user_name                           "User ID",
       frt.responsibility_name                "Responsibility Used",
       fcr.request_id                         "Request ID",
       fcpt.user_concurrent_program_name      "Concurrent Program Name",
       flv2.meaning                           "Phase",
       flv1.meaning                           "Status",
       fcr.request_date                       "Request Date",
       fcr.requested_start_date               "Request Start Date",
       fcr.parent_request_id                  "Parent Request ID"
FROM
       fnd_user                    fu,
       fnd_responsibility_tl       frt,
       fnd_concurrent_requests     fcr,
       fnd_concurrent_programs_tl fcpt,
       fnd_lookup_values           flv1,
       fnd_lookup_values           flv2
WHERE
       1=1
   AND flv1.lookup_code           = fcr.status_code
   AND flv1.lookup_type           = 'CP_STATUS_CODE'
   AND flv1.enabled_flag          = 'Y'
   AND flv1.view_application_id   <> 0
   AND flv2.lookup_code           = fcr.phase_code
   AND flv2.lookup_type           = 'CP_PHASE_CODE'
   AND flv2.enabled_flag          = 'Y'
   AND flv2.view_application_id   <> 0
   AND fu.user_id                 = fcr.requested_by
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id      = frt.responsibility_id
   AND frt.LANGUAGE               = USERENV ('LANG')
   AND fcpt.LANGUAGE              = USERENV ('LANG')
   AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'
ORDER BY fcr.request_date DESC;

FND : Query to find runtime of a concurrent program

The following query finds total run-time (in minutes) for a concurrent program. You can make a small modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
       applsys.fnd_concurrent_requests     rq
WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
    ORDER BY rq.request_id DESC;

FND : Query to find Application Short Name of a module

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
FROM fnd_application     fa,
       fnd_application_tl fat
WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
ORDER BY fat.application_name;

FND : Fetch 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;

FND : Fetch Folder Info

SELECT b.application_short_name,
d.responsibility_name,
a.OBJECT,
c.NAME Folder_Name
FROM apps.fnd_default_folders a,
apps.fnd_application b,
apps.fnd_folders c,
apps.fnd_responsibility_vl d
WHERE a.application_id = b.application_id
AND a.folder_id = c.folder_id
AND d.responsibility_id = - (a.user_id)
AND d.responsibility_name LIKE '%%'
ORDER BY b.application_short_name,
d.responsibility_name;

FND : Fetch Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of 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;

FND :Query to Fetch related to Oracle Application Messages

SELECT m.message_name,
m.message_text,
m.message_number,
a.application_short_name
FROM FND_NEW_MESSAGES M,
FND_APPLICATION a
WHERE upper(m.message_text) LIKE upper('%&Enter_Message_Text%')
AND m.language_code = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID;

SELECT m.message_name,
m.message_text,
m.message_number,
a.application_short_name
FROM FND_NEW_MESSAGES M,
FND_APPLICATION a
WHERE m.message_name LIKE '%&Enter_Message_Name%'
AND m.language_code = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID

FND :Query to find the Request Group of a Concurrent Program

Use this query to pull Request Group of a Concurrent Program. In such cases the below query will be a useful one.
SELECT
RG.APPLICATION_ID "Request Group Application ID",
RG.REQUEST_GROUP_ID "Request Group - Group ID",
RG.REQUEST_GROUP_NAME,
RG.DESCRIPTION,
rgu.unit_application_id,
rgu.request_group_id "Request Group Unit - Group ID",
rgu.request_unit_id,cp.concurrent_program_id,
cp.concurrent_program_name,
cpt.user_concurrent_program_name,
DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;

FND :Fetching Oracle AOL Descriptive Flexfield Segment(DFF) Details

SELECT fat.application_name
       , fdfv.title
       , fdfcv.descriptive_flex_context_code context
       , fdfcuv.column_seq_num num
       , fdfcuv.end_user_column_name name
       , fdfcuv.application_column_name column_
       , ffvs.flex_value_set_name value_set
       , ffvs.description value_set_description
       , fdfcuv.required_flag
       , fdfcuv.display_flag
       , fdfcuv.enabled_flag
       , fdfcuv.security_enabled_flag
       , fdfcuv.default_value
    FROM apps.fnd_descriptive_flexs_vl fdfv
       , applsys.fnd_application_tl fat
       , apps.fnd_descr_flex_contexts_vl fdfcv
       , apps.fnd_descr_flex_col_usage_vl fdfcuv
       , applsys.fnd_flex_value_sets ffvs
   WHERE fdfv.application_id = fat.application_id
     AND fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
     AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
     AND fdfcv.descriptive_flex_context_code =
                                       fdfcuv.descriptive_flex_context_code
     AND fdfcuv.flex_value_set_id = ffvs.flex_value_set_id(+)
     AND fdfv.title = NVL(:P_TITLE,fdfv.title)
     AND fat.application_name = NVL(:P_APPLICATION,fat.application_name)
     AND fdfcv.descriptive_flex_context_code =
                         NVL(:P_DESC_FLEX_CD,fdfcv.descriptive_flex_context_code)

ORDER BY fat.application_name

, fdfv.title

, fdfcv.descriptive_flex_context_code

, fdfcuv.column_seq_num;

you can validate at this navigation
Application Developer > Navigate > Flexfields > Descriptive > Segments

FND :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 ;

FND : Query to find Request Group Details

select rga.application_short_name "Req Grp App",
rg.request_group_name "Request Group Name",
rg.request_group_code "Request Group Code",
rg.description "Request Group Desc",
cp.concurrent_program_name "Concurrent Program",
ea.application_short_name "Exec App",
e.executable_name "Executable",
rga.application_id "Req Grp App ID",
rg.request_group_id "Req Grp ID",
e.application_id "Exec App ID"
from applsys.fnd_executables e,
applsys.fnd_concurrent_programs cp,
applsys.fnd_request_group_units rgu,
applsys.fnd_request_groups rg,
applsys.fnd_application rga,
applsys.fnd_application ea
where e.application_id like '%' -- Enter Application ID
and e.executable_name like '%' -- Enter Executable Name
and cp.executable_application_id = e.application_id
and cp.executable_id = e.executable_id
and cp.enabled_flag = 'Y'
and rgu.request_unit_id = cp.concurrent_program_id
and rgu.unit_application_id = cp.application_id
and rgu.request_unit_type = 'P'
and rg.application_id = rgu.application_id
and rg.request_group_id = rgu.request_group_id
and rga.application_id = rgu.application_id
and ea.application_id = e.application_id

FND : Fetch OU (Org id) from Responsibility Name

SELECT   frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.hr_organization_units hou,
         apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv
   WHERE frv.responsibility_name = <Responsibility Name>
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

FND : Fetch Concurrent Program parameters

select cp.CONCURRENT_PROGRAM_NAME, cu.*, vs.FLEX_VALUE_SET_NAME
from applsys.fnd_concurrent_programs cp,
applsys.fnd_descr_flex_column_usages cu,
applsys.fnd_flex_value_sets vs
where cp.CREATION_DATE > to_date('17-MAY-1995') --Enter Creation Date
AND cu.application_id = cp.application_id
AND cu.descriptive_flexfield_name = '$SRS$.' || cp.concurrent_program_name
AND cu.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_ID
ANd cp.CONCURRENT_PROGRAM_ID=:CONCURRENT_PROGRAM_ID --(Enter CONCURRENT_PROGRAM_ID of CONCURRENT_PROGRAM)
order by cp.CONCURRENT_PROGRAM_NAME, cu.COLUMN_SEQ_NUM

FND: Oracle Application URL from the Apps database

SELECT profile_option_value
FROM apps.fnd_profile_option_values
WHERE profile_option_id =
                         (SELECT profile_option_id
                            FROM apps.fnd_profile_options
                           WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
   AND level_value = 0

FND : QUERY to Find Locked Objects

select oracle_username, os_user_name, session_id, process,
locked_mode, o.owner, o.object_type, o.object_name
from v$locked_object l,
sys.dba_objects o
where l.object_id = o.object_id
order by o.owner, o.object_name, session_id

FND : Fetch Independent Value set Details:

This query gives details of independent FND Value sets.

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');

EBS Queryboard