Application
-----------------
select * from fnd_application;
Findout Oracle Apps Version
----------------------------------
select * from fnd_product_groups;
Findout database version
----------------------------
select * from v$version;
Find Oracle Apps URL
---------------------------
select * from icx_parameters;
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE profile_option_id = (SELECT profile_option_id
FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0;
Responsibilities
---------------------
select * from fnd_responsibility_tl where responsibility_name='Application Deveoper';
Request group
----------------
select * from fnd_request_groups;
Menus
-------------
select * from fnd_menus_vl
select * from fnd_menus;
select * from fnd_menus_tl;
select * from fnd_menu_entries;
select * from fnd_menu_entries_tl;
Form
------------
select * from fnd_form;
Function
--------------
select * from fnd_form_functions_tl;
select * from fnd_form_functions;
Executable
-----------------
select * from fnd_executables;
Concurrent Programs
-------------------------
select * from fnd_concurrent_requests;
select * from fnd_concurrent_programs_vl;
select * from fnd_concurrent_programs_tl;
Request Set
----------------
select * from fnd_request_sets_vl;
select * from fnd_req_set_stages_form_v;
select * from fnd_request_set_programs;
Profile
--------------
select * from FND_PROFILE_OPTIONS;
select * from FND_PROFILE_OPTIONS_TL;
select * from FND_PROFILE_OPTIONS_VALUES;
Key FND Tables in Oracle Application
----------------------------------------
FND_LOBS:
Table contains all the attachments which were attached by users in all the modules of E-Business Suite,
since the instance was created.
FND_DOCUMENTS_LONG_RAW:
Stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.
FND_DOCUMENTS_LONG_TEXT:
Stores information about long text documents.
FND_DOCUMENTS_SHORT_TEXT:
Stores information about short text documents.
FND_APPLICATION:
Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL:
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS:
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES:
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS:
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS:
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL:
Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES:
Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE:
Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS:
Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS:
Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS:
This table stores output files created by Concurrent Request.
FND_CURRENCIES:
Stores information about currencies.
FND_DATABASES:
It tracks the databases employed by the eBusiness suite.
This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES:
Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS:
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL:
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS:
Stores language-independent information about a document.
FND_EXECUTABLES:
Stores information about concurrent program executables.
FND_FLEX_VALUES:
Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS:
Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES:
Stores information regarding languages and dialects.
FND_MENUS:
It lists the menus that appear in the Navigate Window, as determined by the System Administrator
when defining responsibilities for function security.
FND_MENUS_TL:
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES:
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS:
Stores information about user profile options.
FND_REQUEST_GROUPS:
Stores information about report security groups.
FND_REQUEST_SETS:
Stores information about report sets.
FND_RESPONSIBILITY:
Stores information about responsibilities.
Each row includes the name and description of the responsibility, the application it belongs to,
and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL:
Stores translated information about responsibilities.
FND_RESP_FUNCTIONS:
Stores security exclusion rules for function security menus.
Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS:
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES:
Stores information about the registered sequences in your applications.
FND_TABLES:
Stores information about the registered tables in your applications.
FND_VIEWS:
Stores information about the registered views in your applications.
FND_TERRITORIES:
Stores information for countries, alternatively known as territories.
FND_USER:
Stores information about application users.
====================================================
Key Metadata Tables/Views In Oracle Applications
The following are some views a PL/SQL developer is most likely to find useful:
------------------------------------------------------------------------------------
USER_DEPENDENCIES
Stores the dependencies to and from the objects a current user owns.
This view is mostly used by Oracle to mark objects INVALID when necessary,
and also by IDEs to display the dependency information in their object browsers.
USER_ERRORS
Stores the current set of errors for all stored objects a current user owns.
This view is accessed by the SHOW ERRORS SQL*Plus command.
USER_OBJECTS
Displays the objects owned by the current user.
You can, for instance, use this view to see if an object is marked INVALID,
find all the packages that have “DEPT” in their names, etc.
USER_OBJECT_SIZE
Displays the size of the objects owned by the current user.
Actually, this view will show you the source, parsed, and compile sizes for your code.
Use it to identify the large programs in your environment, good candidates for pinning into the SGA.
USER_PLSQL_OBJECT_SETTINGS
(Introduced in Oracle Database 10g Release 1) Shows Information about the characteristics of a PL/SQL object
that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.
USER_PROCEDURES
(Introduced in Oracle9i Database Release 1) Shows Information about stored programs,
such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.
USER_SOURCE
Shows the text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source).
This is a very handy view, because you can run all sorts of analysis of the source code against it using SQL and,
in particular, Oracle Text.
USER_ARGUMENTS
Shows the arguments (parameters) in all the procedures and functions in your schema.
USER_TRIGGERS and USER_TRIG_COLUMNS
Displays the database triggers owned by current user, and any columns identified with the triggers.
You can write programs against this view to enable or disable triggers for a particular table.
List of Menus Excluded from a given Responsibility:
-----------------------------------------------------
select frv.responsibility_name,
fmv.user_menu_name
from
fnd_resp_functions frf,
fnd_menus fmv,
fnd_responsibility_vl frv
where
frf.rule_type='M'
and frf.action_id= fmv.menu_id
and frf.responsibility_id = frv.responsibility_id
and frv.responsibility_name='&resp_name';
FIND API SCRIPT
--------------------
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a,
dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'HR_COM%API%'
order by
a.owner, a.name;
xml publisher
----------------------
Identifying XML Report Lobs
SELECT b.*
FROM apps.xdo_lobs l, apps.xdo_templates_b b
WHERE l.application_short_name = '<appl short name>'
AND l.lob_code IN ('<DATA DEFINITION SHORT CODE>')
AND l.application_short_name = b.application_short_name
AND l.lob_code = b.template_code;
for intilizing before calling any public or private API
----------------------------------------------------------
select fnd.user_id,
fresp.responsibility_id,
fresp.application_id
from
fnd_user fnd,
fnd_responsibility_tl fresp
where
fnd.user_name = 'OPERATIONS'
AND fresp.responsibility_name ='Payables, Vision Operations (USA)';
PARTICULAR USER INFORMATION DETAILS
-----------------------------------------
SELECT distinct fu.USER_NAME "username",
fu.DESCRIPTION "user DESCR",
TO_CHAR(fu.START_DATE,'DD-MON-YYYY') "startdate",
TO_CHAR(nvl(fu.END_DATE,'31-DEC-2099')) "ENDINGDATE",
frt.RESPONSIBILITY_NAME "res name",
fat.APPLICATION_NAME "APP NAME",
fm.MENU_NAME "menugroupname",
fdg.DATA_GROUP_NAME "datagroupname",
frg.REQUEST_GROUP_NAME "requestgroupname",
TO_CHAR(furgd.START_DATE,'DD-MON-YYYY') "userressdate",
TO_CHAR(nvl(furgd.END_DATE,'31-DEC-2099')) "userresenddate"
FROM FND_USER fu,
FND_MENUS fm,
FND_DATA_GROUPS fdg,
FND_REQUEST_GROUPS frg,
FND_USER_RESP_GROUPS_DIRECT furgd,
FND_APPLICATION_TL fat,
FND_APPLICATION fa,
FND_RESPONSIBILITY fr,
FND_RESPONSIBILITY_TL frt
WHERE fu.USER_NAME=nvl('&username',fu.USER_NAME)
AND fu.USER_ID=furgd.USER_ID
AND frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
AND fat.APPLICATION_ID=frt.APPLICATION_ID
AND fr.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
AND fm.MENU_ID=fr.MENU_ID
AND fdg.DATA_GROUP_ID=fr.DATA_GROUP_ID
AND frg.REQUEST_GROUP_ID=fr.REQUEST_GROUP_ID;
PARTICULAR USER HAVE RESPONSIBILTY ASSIGN OR NOT
------------------------------------------------------
select usr.user_id,
usr.user_name,
res.RESPONSIBILITY_ID,
res.RESPONSIBILITY_NAME
from
apps.FND_USER usr,
apps.FND_RESPONSIBILITY_TL res,
apps.FND_USER_RESP_GROUPS grp
where
upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')||'%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id
and user_name ='OPERATIONS';
Query to find Form Function attached to which Responsibility
---------------------------------------------------------------
SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE,rtl.language
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_tl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
--AND UPPER(rtl.responsibility_name) LIKE '%OM%'
and ffl.user_function_name like '%&Function_Name%'
AND r.end_date IS NULL
AND rtl.language='US'
ORDER BY rtl.responsibility_name;
the query to list concurrent program name with its parameter, values set and default value/type
-------------------------------------------------------------------------------------------------
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, 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 fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num
find the list of responsibilities to which the programs is attached to
------------------------------------------------------------------------
SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM
fnd_Responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.language = USERENV('LANG')
AND fcpt.language = USERENV('LANG')
AND fcpt.user_concurrent_program_name = :conc_prg_name
ORDER BY 1,2,3,4
Query to find Request Set
-------------------------------
SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_request_set_name
FROM
apps.FND_RESPONSIBILITY fr,
apps.FND_RESPONSIBILITY_TL frt,
apps.FND_REQUEST_GROUPS frg,
apps.FND_REQUEST_GROUP_UNITS frgu,
apps.FND_REQUEST_SETS_TL fcpt
WHERE
frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.request_set_id = frgu.request_unit_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
AND fcpt.user_request_set_name = :request_set_name
ORDER BY 1,2,3,4
Query to find application short name
----------------------------------------
select Application_short_name , application_name
from fnd_application fapp,
fnd_application_tl fappt
where fapp.APPLICATION_ID = fappt.application_id
and fappt.language = 'US'
and application_name = 'General Ledger'
Query to find all responsibilities of a user
---------------------------------------------------
SELECT fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
furg.start_date "Start Date",
furg.end_date "End Date",
fr.responsibility_key "Responsibility Key",
fa.application_short_name "Application Short Name"
FROM fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
AND UPPER(fu.user_name) = UPPER('sandeep') -- <change it>
ORDER BY frt.responsibility_name;
List of functions excluded from a given responsibility:
--------------------------------------------------------
select frv.responsibility_name,
fffv.user_function_name
from
fnd_resp_functions frf,
fnd_form_functions_vl fffv,
fnd_responsibility_vl frv
where
frf.rule_type='F'
and frf.action_id = fffv.function_id
and frf.responsibility_id = frv.responsibility_id
and frv.responsibility_name='&resp_name';
Concurrent Program Name,responsibility Name and User Name for a concurrent request_id
---------------------------------------------------------------------------------------
select fcr.request_id,
frv.responsibility_name,
fcpv.concurrent_program_name prog_short_name,
fcpv.user_concurrent_program_name,
fu.user_name requested_by
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_vl fcpv,
fnd_user fu,
fnd_responsibility_vl frv
where fcpv.concurrent_program_id =fcr.concurrent_program_id
and fu.user_id = fcr.requested_by
and frv.responsibility_id = fcr.responsibility_id
and fcr.request_id = &req_id;
Query to get Request Set Details
----------------------------------
SELECT rs.user_request_set_name “Request Set”, rss.display_sequence seq,
cp.user_concurrent_program_name “Concurrent Program”,
e.executable_name, e.execution_file_name, lv.meaning file_type,
fat.application_name “Application Name”
FROM fnd_request_sets_vl rs,
fnd_req_set_stages_form_v rss,
fnd_request_set_programs rsp,
fnd_concurrent_programs_vl cp,
fnd_executables e,
fnd_lookup_values lv,
fnd_application_tl fat
WHERE 1 = 1
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rs.user_request_set_name = :p_request_set_name
AND e.application_id = fat.application_id
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND lv.lookup_code = e.execution_method_code
AND lv.LANGUAGE = ‘US’
AND fat.LANGUAGE = ‘US’
AND rs.end_date_active IS NULL
ORDER BY 1, 2;
Starting of Script to find the request set assigned to a responsibility
--------------------------------------------------------------------------
SELECT frt.responsibility_name
,fcpt.user_request_set_name
,frst.request_set_stage_id
,frst.user_stage_name
FROM apps.fnd_Responsibility fr
,apps.fnd_responsibility_tl frt
,apps.fnd_request_groups frg
,apps.fnd_request_group_units frgu
,apps.fnd_request_Sets_tl fcpt
,fnd_request_set_stages_tl frst
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.request_set_id = frgu.request_unit_id
AND frst.request_set_id = fcpt.request_set_id
AND frst.LANGUAGE = fcpt.LANGUAGE
AND frt.LANGUAGE = USERENV(‘LANG’)
AND fcpt.LANGUAGE = USERENV(‘LANG’)
AND fcpt.user_request_set_name = :request_set_name
ORDER BY frt.responsibility_name
,frst.request_set_stage_id
,fcpt.user_request_set_name
,frst.user_stage_name;
query to find profile option values at user and responsibility level
-------------------------------------------------------------------------
SELECT po.profile_option_name "NAME",
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
pov.profile_option_value
FROM FND_PROFILE_OPTIONS po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE 1 =1
and po.profile_option_name LIKE '&PROFILE%%'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE";
SELECT fnd_profile.value('RESP_ID') FROM dual;
SELECT fnd_profile.value('USER_ID') FROM dual;
SELECT fnd_profile.value('APPLICATION_ID') FROM dual;
SELECT TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')) FROM dual;
SELECT FND_PROFILE.VALUE('ORG_ID') FROM dual;
SELECT FND_PROFILE.VALUE('SO_ORGANIZATION_ID') FROM dual;
SELECT FND_PROFILE.VALUE('USERNAME') FROM dual;
SELECT FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') FROM dual;
---------------------------------------------------------------------------
No comments:
Post a Comment