Friday, 26 June 2015

Useful Query in AOL

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


Run the following query to find out who are the Oracle Apps Users currently Logged into the application.
========================================================================================================

SELECT DISTINCT icx.session_id,
                  icx.user_id,
                  fu.user_name,
                  fu.description
    FROM icx_sessions icx,
         fnd_user fu
   WHERE    
             disabled_flag != 'Y'
         AND icx.pseudo_flag = 'N'
         AND (last_connect +
              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
                      NULL, limit_time,
                      0   , limit_time,
                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
         AND icx.counter < limit_connects
         AND icx.user_id = fu.user_id;
        
  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 users who were granted particular function in oracle apps R12 db
 =====================================================================================   

SELECT UNIQUE u.user_id,
         SUBSTR (u.user_name, 1, 30) user_name,
         SUBSTR (r.responsibility_name, 1, 60) responsiblity,
         SUBSTR (a.application_name, 1, 50) application
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
AND a.application_name in ('Puchasing','Payables')
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

Supplier Information Query for particular org_id
=================================================

select
        asp.vendor_name "Supplier Name" ,
        ass.vendor_site_code "site name" ,
        ass.address_line1 ,
        ass.country ,
        person.person_last_name ,
        pty_rel.primary_phone_number ,
        pty_rel.email_address
FROM  ap_suppliers asp ,
        ap_supplier_sites_all ass ,
        ap_supplier_contacts apsc ,
        hz_parties person ,
        hz_parties pty_rel,
        hr_operating_units hou
WHERE
       ass.vendor_id = asp.vendor_id
AND  apsc.per_party_id = person.party_id
AND  apsc.rel_party_id = pty_rel.party_id
AND  ass.org_id = hou.organization_id
AND  apsc.org_party_site_id = ass.party_site_id
AND  asp.vendor_name = nvl(:P_SUPPLIER_NAME,asp.VENDOR_NAME)
and   hou.organization_id = fnd_profile.value('org_id');

=========================================================================
SELECT   NAME "WF_NAME",
            DISPLAY_NAME,
            CUSTOM_LEVEL
FROM     
            WF_ITEM_TYPES_TL
WHERE   
           CUSTOM_LEVEL>0
AND      PROTECT_LEVEL>0
ORDER BY custom_level
=========================================================================



select distinct hbg.BUSINESS_GROUP_NAME "BGNAME",
       hbg.DATE_FROM "BGST.DATE",
       hbg.BUSINESS_GROUP_ID "bugid",
       hl.ADDRESS_LINE_1 "DOO RAND LINE ",
       hl.TOWN_OR_CITY "CITY",
       hou.NAME "opuname",
       hou.DATE_FROM "startdate",
       gl.NAME "glname",
       gl.CURRENCY_CODE "currency",
       gpt.USER_PERIOD_TYPE "claender",
       xep.NAME "LENAME",
       ood1.ORGANIZATION_NAME "INV Name",
       ood1.USER_DEFINITION_ENABLE_DATE "start date",
       msi.SECONDARY_INVENTORY_NAME "sub INV name",
       mil.SEGMENT1 "row",
       mil.SEGMENT2 "RACK",
       mil.SEGMENT3 "box"
from   HRFV_BUSINESS_GROUPS hbg,
       HR_LOCATIONS hl, 
       HR_OPERATING_UNITS hou,
       GL_LEDGERS gl,
       GL_PERIOD_TYPES gpt,
       XLE_ENTITY_PROFILES xep,
       MTL_PARAMETERS mp,
       org_organization_definitions ood,
       org_organization_definitions ood1,
       MTL_SECONDARY_INVENTORIES msi,
       MTL_ITEM_LOCATIONS mil
WHERE hbg.BUSINESS_GROUP_NAME=:BUSINESS_GROUP_NAME
and   hl.LOCATION_ID=hbg.LOCATION_ID
and   hou.BUSINESS_GROUP_ID=hbg.BUSINESS_GROUP_ID
and   gl.LEDGER_ID=hou.SET_OF_BOOKS_ID
and   gpt.PERIOD_TYPE=gl.ACCOUNTED_PERIOD_TYPE
and   xep.LEGAL_ENTITY_ID=hou.DEFAULT_LEGAL_CONTEXT_ID
and   ood.ORGANIZATION_ID=hbg.BUSINESS_GROUP_ID
and   mp.ORGANIZATION_ID=ood.ORGANIZATION_ID
and   ood1.ORGANIZATION_ID=mp.MASTER_ORGANIZATION_ID
and   msi.ORGANIZATION_ID=ood.ORGANIZATION_ID;
AND   mil.ORGANIZATION_ID=msi.ORGANIZATION_ID;
--and   ood.ORGANIZATION_ID=ml.ORGANIZATION_ID
--and   fifs.ID_FLEX_NUM=gl.CHART_OF_ACCOUNTS_ID
gcck.CONCATENATED_SEGMENTS "COA"
       --fifs.ID_FLEX_CODE "COA"
gl_code_combinations_kfv gcck
       --FND_ID_FLEX_STRUCTURES fifs
 gcck.CHART_OF_ACCOUNTS_ID=gl.CHART_OF_ACCOUNTS_ID;

============================================================================================

SELECT hbg.BUSINESS_GROUP_NAME "BGNAME",
       hbg.DATE_FROM "BGST.DATE",
       hl.ADDRESS_LINE_1 "DOO RAND LINE ",
       hl.TOWN_OR_CITY "CITY"
       --gl.CURRENCY_CODE "CURRENCY",
       --gl.NAME "LEDGERNAME"
FROM HRFV_BUSINESS_GROUPS hbg,
     HR_LOCATIONS hl
     --GL_LEDGERS gl,
     --HR_OPERATING_UNITS hou
WHERE hbg.BUSINESS_GROUP_NAME=:BUSINESS_GROUP_NAME
AND   hl.LOCATION_ID=hbg.LOCATION_ID;
--AND   hou.BUSINESS_GROUP_ID=hbg.BUSINESS_GROUP_ID;
--AND   gl.LEDGER_ID=hou.SET_OF_BOOKS_ID;



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;

=======================================================================================

SELECT hl.LOCATION_CODE "LOCCODE",
       hl.DESCRIPTION   "DESCN",
       ft.NLS_TERRITORY  "COUNTRY",
       hl.ADDRESS_LINE_1 "DOORNO",
       hl.ADDRESS_LINE_2 "COLONY",
       hl.LOC_INFORMATION16 "STATE",
       hl.LOC_INFORMATION15 "DISTRIC",
       hl.POSTAL_CODE        "PINCODE",
         CASE
         WHEN hl.INACTIVE_DATE is null then
         'ACTIVE'
         WHEN hl.INACTIVE_DATE > sysdate then
         'ACTIVE'
         ELSE
         'INACTIVE'
         END
         "ACTIVE STATUS"
FROM  HR_LOCATIONS hl,    
      FND_TERRITORIES ft
WHERE hl.LOCATION_CODE='HR_AP'
AND   ft.TERRITORY_CODE=hl.COUNTRY;


CASE & DECODE
================================================

 SELECT DECODE(DEPTNO,10,'TEN',20,'TWENTY','OTHERS') DEPTNO,DNAME,LOC   FROM DEPT D

SELECT
CASE
WHEN DEPTNO <=10 THEN
'TEN'
WHEN DEPTNO>= 20 THEN
'TWENTY'
ELSE
'OTHERS'
END "DEPTNO",DNAME,LOC
FROM DEPT

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 for various output of concurrent managers
============================================================
PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;


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 derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name
2. Responsibility Name

  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;




 Verifying Profile Option Values
SELECT DECODE (fpov.level_id,
               10001, 'Site',
               10002, 'Appl',
               10003, 'Resp',
               10004, 'User',
               'Unkown'
              ) "Level",
       DECODE (fpov.level_id,
               10002, fa.application_name,
               10003, fr.responsibility_name,
               10004, fu.user_name,
               '-'
              ) "Location",
       fpov.profile_option_value "Value"
  FROM apps.fnd_profile_option_values fpov,
       apps.fnd_profile_options fpo,
       apps.fnd_profile_options_tl fpot,
       apps.fnd_responsibility_tl fr,
       apps.fnd_user fu,
       apps.fnd_application_tl fa
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpo.profile_option_name = fpot.profile_option_name
   AND fpov.level_value = fr.responsibility_id(+)
   AND fpov.level_value = fu.user_id(+)
   AND fpov.level_value = fa.application_id(+)
   AND fpot.user_profile_option_name = '<Profile Option Name>';

 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
 Setting Org Context


Setting the Multi Org Context :

METHOD 1:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT(ACCESS_MODE,ORG_ID);
end;

Example:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT('S',101);
end;

S - Denotes that the current session will work for Single Org_id (101)

M - Denotes that the current session will work for Multiple Org_id


METHOD 2:

begin
mo_global.init (<APPLICATION SHORT NAME>);
end;

Example :

begin
mo_global.init ('AR');
end;

Query :

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'

-----------------------------------------------------------------

Setting the Application Context :


METHOD 1:

begin
fnd_global.apps_initialize(p_user_id, p_resp_id, p_resp_appl_id);
end;

Example :

begin
      fnd_global.APPS_INITIALIZE(200131258,20004,51710);
end;
begin
      fnd_global.APPS_INITIALIZE
      (user_id       => 200131258,
       resp_id       => 20004,
       resp_appl_id  => 51710
      );
end;

Query to find resp_is , resp_appl_id and user_id

select  responsibility_id
       ,application_id
       ,responsibility_name
from   fnd_responsibility_tl
where  upper(responsibility_name) IN ( upper('Receivables Manager'), upper('Application Developer' ) )
and    language = 'US';


select  user_id
from    fnd_user
where  upper(user_name) = 'SAIF';

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


METHOD 2 :


begin
dbms_application_info.set_client_info('<org id>');
end;

Example

begin
dbms_application_info.set_client_info('101');
end;


API
=========
DECLARE
        uid NUMBER;
        rid NUMBER;
        rad NUMBER;    
        sgid NUMBER;  
BEGIN    
SELECT
        USER_ID,RESPONSIBILITY_ID,RESPONSIBILITY_APPLICATION_ID,SECURITY_GROUP_ID
INTO
        uid, rid, rad, sgid    
FROM
        FND_USER_RESP_GROUPS    
WHERE
USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = 'SYSADMIN')    
AND RESPONSIBILITY_ID =
(SELECT RESPONSIBILITY_ID FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_KEY = 'SYSTEM_ADMINISTRATOR');           
FND_GLOBAL.apps_initialize (uid, rid, rad, sgid);     
ego_p4t_upgrade_pvt.upgrade_to_pim4telco(null);  
END;


 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.

====================================================

No comments:

Post a Comment