Useful queries for Oracle EBS

There are many available reports in Oracle Apps so you can discuss the requirements related to these reports here.
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Useful queries for Oracle EBS

Post by ahmadbilal »

. <b>To check responsibility which contact given function</b>

1. To get form Id from database
SELECT FUNCTION_ID, USER_FUNCTION_NAME FROM FND_FORM_FUNCTIONS_TL
You can also use
SELECT FUNCTION_ID, FUNCTION_NAME FROM FND_FORM_FUNCTIONS

2. Pass The obtained Function id or function name to following query
SELECT frv.responsibility_name, frv.description
FROM fnd_responsibility_vl frv, fnd_form_functions fff
WHERE fff.function_name = 'FND_FNDATDAG' ------Pass Function name Here
--f.form_id =p_form_id
AND frv.menu_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = frv.menu_id
AND frf.rule_type = 'M')
AND frv.menu_id IN (SELECT me.menu_id
FROM fnd_menu_entries me
START WITH me.function_id = fff.function_id
CONNECT BY PRIOR me.menu_id = me.sub_menu_id)
AND fff.function_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = fff.function_id AND frf.rule_type = 'F')
? <b>To Check The Profile Options Which Are Modified</b>

SELECT t.user_profile_option_name, profile_option_value, v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "change Status",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

? <b>Query used To Check The Form personalization</b>

SELECT ffcr.function_name, ffft.user_function_name "User Form Name", ffcr.form_name "from Source File Name",
Ffcr.ID "Form ID",
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By ",
ffcr.creation_date,
ffcr.last_update_date,
ffcr.SEQUENCE,
ffcr.rule_key,
ffcr.description,
ffcr.rule_type,
ffcr.enabled,
ffcr.trigger_event,
ffcr.trigger_object,
ffcr.condition,
ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id

For More detail on form Personalization Use Following Tables (Rule_id) is reference key these tables

applsys.fnd_form_custom_actions,applsys.fnd_form_custom_scopes

By using Following query you can get application wise detail

SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = ff.application_id) application, ffcr.ID,
ffcr.SEQUENCE, ffcr.function_name,
REPLACE (ffcr.description, CHR (39), CHR (39) || CHR (39)) description,
ffcr.trigger_event, ffcr.trigger_object,
REPLACE (REPLACE (ffcr.condition, CHR (10), CHR (32)),
CHR (39),
CHR (39) || CHR (39)
) condition,
ffcr.enabled, ffcr.fire_in_enter_query, ffcr.rule_key, ffcr.form_name,
ffcr.rule_type
FROM apps.fnd_form_custom_rules ffcr, applsys.fnd_form ff
WHERE (ff.form_name) = (ffcr.form_name)

? <b>How To Check Patch Level Status</b>

select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS,PATCH_LEVEL
from APPS.fnd_application_vl a,
APPS.fnd_product_installations b
where a.application_id = b.application_id

? <b>Get Request Group name Associated With Application</b>
SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
ORDER BY c.user_concurrent_program_name,
a.application_name,
g.request_group_id
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To Check Current Status Of Request</b>

SELECT r.request_id, r.phase_code, r.status_code, r.request_date,
r.requested_start_date, r.hold_flag, r.parent_request_id,
r.last_update_date, u1.user_name updated_by_name, r.actual_start_date,
r.completion_text, r.actual_completion_date, u2.user_name requestor,
fa.application_name application_name, cp.enabled_flag enabled,
r.controlling_manager,
DECODE (r.description,
NULL, cp.user_concurrent_program_name,
r.description || ' (' || cp.user_concurrent_program_name || ')'
) program_name,
queue_control_flag, r.queue_method_code, cp.run_alone_flag,
r.single_thread_flag, r.request_limit, r.cd_id
FROM fnd_concurrent_requests r,
fnd_concurrent_programs_vl cp,
fnd_user u1,
fnd_user u2,
fnd_application_vl fa
WHERE r.program_application_id = fa.application_id
AND r.program_application_id = cp.application_id(+)
AND r.concurrent_program_id = cp.concurrent_program_id(+)
AND r.last_updated_by = u1.user_id(+)
AND r.requested_by = u2.user_id(+);


<b>Another Option</b>
select fcr.REQUEST_ID,fu.user_name as requested_by, fcpt.user_concurrent_program_name, fcr.request_date,
fcr.phase_code, fcr.requested_start_date, fcr.argument_text
from fnd_concurrent_programs_tl fcpt, fnd_concurrent_requests fcr, fnd_user fu
where fcpt.concurrent_program_id = fcr.concurrent_program_id
and fcr.requested_by = fu.user_id
and trunc(fcr.request_date) = trunc(sysdate)
order by fcr.requested_start_date
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>Get Average Runtime of A Concurrent</b>
SELECT fcp.description "Description",
TRUNC
(AVG ( ( TO_NUMBER (TO_CHAR (actual_completion_date, 'SSSSS'))
- TO_NUMBER (TO_CHAR (actual_start_date, 'SSSSS'))
)
/ 60
),
2
) "Time in Min."
FROM fnd_concurrent_programs_vl fcp, fnd_concurrent_requests fcr
WHERE fcp.application_id = fcr.program_application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND TO_CHAR (actual_completion_date, 'DD-MON-YY') =
TO_CHAR (actual_start_date, 'DD-MON-YY')
GROUP BY fcp.description
ORDER BY fcp.description;
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Check Period Status</b>
This query will help to get information of periods status (open/close) of different Oracle Apps modules.

SELECT ROWID, (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = gps.application_id) application,
(SELECT NAME
FROM gl_sets_of_books gsp
WHERE gsp.set_of_books_id = gps.set_of_books_id) setofbookname,
period_name, closing_status, period_num, period_year, start_date,
end_date
FROM gl_period_statuses gps
ORDER BY period_year DESC, period_num DESC
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Check Open inventory Periods</b>
SELECT summary.organization_id organization_id,
ood.organization_code organization_code,
ood.organization_name organization_name,
periods.period_name period_name,
periods.OPEN_FLAG ,periods.PERIOD_CLOSE_DATE ,
periods.period_year period_year,
periods.period_start_date period_start_date,
periods.schedule_close_date schedule_close_date,
summary.secondary_inventory sub_inv, summary.inventory_value VALUE
FROM mtl_period_summary summary,
org_acct_periods periods,
org_organization_definitions ood
WHERE summary.organization_id = periods.organization_id
AND summary.organization_id = ood.organization_id
AND summary.acct_period_id = periods.acct_period_id
AND periods.period_close_date IS NOT NULL
AND summary.inventory_type = 1
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>Check User --> Application and assigned responsiblity</b>
select unique
u.user_id, substr(u.user_name,1,30) user_name,
SubStr(A.APPLICATION_NAME,1,50) Application,
SubStr(R.RESPONSIBILITY_NAME,1,60) Responsiblity
From
fnd_user u, FND_USER_RESP_GROUPS G, fnd_application_tl A, FND_RESPONSIBILITY_TL R
Where
G.User_Id(+) = U.User_ID
And G.RESPONSIBILITY_APPLICATION_ID = A.Application_Id
And A.Application_Id = R.Application_Id
And G.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
order by
substr(user_name,1,30),
SubStr(A.APPLICATION_NAME,1,50),
SubStr(R.RESPONSIBILITY_NAME,1,60)
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Check Application--> Responsablity and Asssigned Menu</b>
SELECT DISTINCT e.application_name, a.responsibility_name, a.LANGUAGE,
b.responsibility_key, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US'
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Check Module Wise Reports</b>

SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY 1


<b>To Count Module Wise Reports</b>
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Check Profile Option value and application level</b>

SELECT fpo.profile_option_name PROFILE, fpov.profile_option_value VALUE,
DECODE (fpov.level_id,
10001, 'SITE',
10002, 'APPLICATION',
10003, 'RESPONSIBILITY',
10004, 'USER'
) "LEVEL",
fa.application_short_name app, fr.responsibility_name responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fa.application_id(+) = fpov.level_value
AND fr.application_id(+) = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+) = fpov.level_value
AND fl.login_id(+) = fpov.last_update_login
ORDER BY 1, 3
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To Check Error and Interface tables</b>

SELECT owner, table_name
FROM dba_tables
WHERE table_name LIKE <b>'%ERROR%'</b>
ORDER BY owner, table_name


SELECT owner, table_name
FROM dba_tables
WHERE table_name LIKE <b>'%INTERFACE%'</b>
ORDER BY owner, table_name
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To check Organization</b>
SELECT organization_id, organization_code, organization_name, operating_unit,
legal_entity
FROM org_organization_definitions

Will Update With Further Information
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To Check lookup</b>

SELECT (SELECT application_short_name
FROM fnd_application
WHERE application_id = flvv.view_application_id) application,
flvv.lookup_code, flvv.meaning, flvv.description, flvv.tag,
flvv.start_date_active, flvv.end_date_active, flvv.enabled_flag,
flvv.lookup_type, flvv.security_group_id, flvv.territory_code,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = flvv.created_by) uname, flvv.creation_date,
flvv.last_update_date, flvv.last_updated_by, flvv.last_update_login
FROM fnd_lookup_values_vl flvv
WHERE (SELECT application_short_name
FROM fnd_application
WHERE application_id = flvv.view_application_id) = ///Pass Application Code e.g for order management ONT
ORDER BY 1
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How to check the Status of Deferred Activities In Workflow</b>

1-Run wfbkgchk.sql available at $FND_TOP/sql
2.For example, log on to the database by doing sqlplus username/password@database
3.Then run the sql file, wfbkgchk.sql, by doing @wfbkgchk.sql
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To get Conversion Rates</b>

SELECT from_currency, to_currency, conversion_date, conversion_type,
conversion_rate, status_code, created_by
FROM gl.gl_daily_rates
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests