--To check responsibility which contact given function
--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'
) ;
--• To Check The Profile Options Which Are Modified
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;
;
--• Query used To Check The Form personalization
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) ;
--• How To Check Patch Level Status
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 ;
--• Get Request Group name Associated With Application
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 ;
--How To Check Current Status Of Request
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(+);
;
--Another Option
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 ;
--Get Average Runtime of A Concurrent
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;
;
--To Check Period Status
--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 ;
--To Check Open inventory Periods
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 ;
--Check User --> Application and assigned responsiblity
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) ;
--To Check Application--> Responsablity and Asssigned Menu
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' ;
--To Check Module Wise Reports
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 ;
--To Count Module Wise Reports
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 ;
--To Check Profile Option value and application level
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 ;
--How To Check Error and Interface tables
SELECT
owner,
table_name
FROM
dba_tables
WHERE
table_name LIKE '%ERROR%'
ORDER BY
OWNER,
TABLE_NAME ;
SELECT
owner,
table_name
FROM
dba_tables
WHERE
table_name LIKE '%INTERFACE%'
ORDER BY
OWNER,
TABLE_NAME ;
--How To check Organization
SELECT
organization_id,
organization_code,
organization_name,
operating_unit,
legal_entity
FROM
ORG_ORGANIZATION_DEFINITIONS ;
--How To Check lookup
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 ;
--How To get Flexfilds Application wise
SELECT
(
SELECT
UNIQUE application_short_name
FROM
fnd_application
WHERE
application_id = fdfcv.application_id
)
application,
fdfcv.descriptive_flex_context_code,
fdfcv.descriptive_flex_context_name,
fdfcv.description,
fdfcv.enabled_flag,
fdfcv.application_id,
fdfcv.descriptive_flexfield_name,
fdfcv.last_update_date,
fdfcv.last_updated_by,
fdfcv.last_update_login,
fdfcv.creation_date,
fdfcv.created_by,
fdfcv.global_flag,
fdfcv.row_id
FROM
fnd_descr_flex_contexts_vl fdfcv
WHERE
(
SELECT
UNIQUE application_short_name
FROM
fnd_application
WHERE
APPLICATION_ID = FDFCV.APPLICATION_ID
)
= 'ONT' ; --ONT used For order management
--How To Check Application wise Responsiblity
SELECT
(
SELECT
application_short_name
FROM
fnd_application fa
WHERE
fa.application_id = frv.application_id
)
application,
frv.responsibility_name,
frv.description
FROM
FND_RESPONSIBILITY_VL FRV
WHERE
(
SELECT
application_short_name
FROM
fnd_application fa
WHERE
FA.APPLICATION_ID = FRV.APPLICATION_ID
)
= 'SQLAP' ; --Used For Specific Module
--how to find mapping between segment name and the value set
SELECT
FIFSV.segment_name,
FIFSV.segment_num,
FIFSV.application_column_name,
FIFSV.flex_value_set_id,
FIF.application_table_name,
FIF.id_flex_code flex_code,
FIF.id_flex_name flex_name
FROM
fnd_id_flex_segments_vl FIFSV,
fnd_id_flexs FIF
WHERE
FIFSV.ID_FLEX_CODE = FIF.ID_FLEX_CODE ;
--How to find order header information
SELECT
ooh.order_number,
ooh.header_id hdr_id,
DECODE (ooh.order_category_code, 'MIXED', 'Mixed', 'ORDER', 'Regular',
'RETURN', 'Return', ooh.order_category_code ) CATEGORY,
ott.NAME ord_typ,
ooh.ordered_date,
ooh.transactional_curr_code curr,
os2.NAME sdt,
ooh.source_document_id sdi,
os1.NAME os,
ooh.orig_sys_document_ref osdr,
ooh.sold_to_org_id sold_to,
ooh.invoice_to_org_id invoice_to,
ooh.cust_po_number cust_po,
ooh.payment_type_code pay_type,
ooh.flow_status_code hdr_flow_status,
ooh.open_flag,
ooh.booked_flag,
ooh.cancelled_flag,
NVL (ooh.upgraded_flag, 'N') upgraded_flag
FROM
oe_order_headers_all ooh,
oe_transaction_types_tl ott,
oe_order_sources os1,
oe_order_sources os2
WHERE
ooh.order_number = &order_number
AND ooh.order_type_id = ott.transaction_type_id
AND ooh.order_source_id = os1.order_source_id(+)
AND ooh.source_document_type_id = os2.order_source_id(+)
AND ott.LANGUAGE =
(
SELECT
fl.language_code
FROM
FND_LANGUAGES FL
WHERE
FL.INSTALLED_FLAG = 'B'
);
No comments:
Post a Comment