KEY FND TABLES IN ORACLE APPLICATION

KEY FND TABLES IN ORACLE APPLICATION

Here there are few key FND tables that we use in our AOL queries.

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_TERRITORIES:

Stores information for countries, alternatively known as territories.

FND_USER:

Stores information about application users.

FND_VIEWS:

Stores information about the registered views in your applications.

Query to Get the DFF details & Segment values

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

SELECT ffv.descriptive_flexfield_name DFFName,
  ffv.application_table_name TABLEName,
  ffv.title Title,
  ap.application_name Application,
  ffc.descriptive_flex_context_code ContextCode,
  ffc.descriptive_flex_context_name ContextName,
  ffc.description ContextDESC,
  ffc.enabled_flag ContextEnableFlag,
  att.column_seq_num SegmentNUMBER,
  att.form_left_prompt SegmentName,
  att.application_column_name,
  fvs.flex_value_set_name,
  att.display_flag Displayed,
  att.enabled_flag Enabled,
  att.required_flag Required
FROM apps.fnd_descriptive_flexs_vl ffv,
  apps.fnd_descr_flex_contexts_vl ffc,
  apps.fnd_descr_flex_col_usage_vl att,
  apps.fnd_flex_value_sets fvs,
  apps.fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ffv.title LIKE '%Pricing Contexts%'
and ffc.descriptive_flex_context_code='PRICING ATTRIBUTE'
--AND ffc.descriptive_flex_context_code LIKE ‘Give Context Code Value’
ORDER BY att.column_seq_num;

Profile Option in Oracle Apps

Steps to create custom Profile Option

It is very rare that you have to create your own custom profile option in oracle apps.
But in case you have a requirement that can be solved only by creating custom profile option then following are the steps to create custom profile option.

 Log in to Oracle Apps

Navigate to Application Developer Responsibility-->Profile.

Create New Profile Option

Enter all mandatory fields on Profile form to create new profile option.

Name :- Enter name of the profile option.

Application :- Enter name of the application.

User Profile Name :- Name of the profile option for users.

Description :- Description for Profile Option(Optional)

Hierarchy Type :-  Their are 4 hierarchy types available in Oracle apps which are as follows.

1. Security :- Profile Option is Visible,Updatable at Site,Application,Responsibility and User level.

2. Server    :- Profile Option is Visible,Updatable at Site,Server and User level.

3. Server-Responsibility :- Profile Option is Visible,Updatable at Site,Server+Responsibility and   User level.

4. Organization :- Profile Option is Visible,Updatable at Site,Organization and User level.

Hierarchy Type Access Level :- Hierarchy type levels are used to set the access level of the profile option i.e. at which level you can access this profile option.There are total 7 levels.Hierarchy type access levels depends on your hierarchy type i.e. which hierarchy type you have selected.

Active Dates :- Start( Start date of this profile option this field id mandatory).

                          End(End date of this profile option this field is optional).

User Access :- If you checked Visible, Updatable check-boxes under this section then user can see and also update this profile option.

You can also use SQL query to retrieve List of Values.

Create Lookup for Profile Option Values

Navigate to Application Developer Responsibility -->Application -->Lookups --> Application Object Library.

To get profile option values you have to create new Lookup or have to use any existing Lookup or you can hardcore values in SQL validation section on PROFILE form. 

Enter all the mandatory fields on LOOKUP form.

Type :- Enter Lookup type.

Meaning :- Enter meaning of the Lookup(Optional).

Application :- Enter name of the application.

Description :- Enter description of the Lookup(Optional).

Under Access Level section select one option from the following :-

User :- User can insert and update Lookup values.

Extensible :- User can add additional values and modify them but cannot modify predefined values.

System :- User cannot do anything if access level is System.

Under Line section enter the following fields :-

Code :- Code of Lookup value.

Meaning :- Meaning of Lookup value.

Description :- Enter description of  Lookup value(Optional).

From Date :- Enter Start date of Lookup value.

To Date :- Enter End date of  Lookup value.

Enable :- Check Enable check box if you want to enable this Lookup value.

Save your work and Navigate to PROFILE form to enter SQL query to get values for Profile Option.

Set Profile Option Value

Navigate to System Administrator Responsibility --> Profile --> System.

On SYSTEM PROFILE OPTION form check the check box where you want to set the profile option that you just created i.e. on Site,Responsibility,User,Organization,Application,Server level.

Check Profile with No Values check box if you want to query those Profile options which don't have any value on the level you have selected.

From PROFILE LOV select profile option that you have created and click on FIND button.

Select Profile Option Value and save you work.

If you like this post then share this with your friends and also give your reviews about this post.

MOAC – Oracle Apps ORG_ID, Multi Org Concept

Its quite obvious that an organization may have its operations in different locations of the globe which we call as operating units in-terms of oracle ERP.

Multi-Org Architecture Prior to Release 12

• To accommodate data of different operating units in one table, Oracle has comeup with a column ORG_ID. For example PO_HEADERS_ALL.org_id is column which stores operating unit ID.
• Where as the table PO_HEADERS_ALL is stored in PO Schema and a VIEW PO_HEADERS is present in APPS Schema which would only return rows which corresponded to the current operating unit’s organization ID.
• A view in the APPS schema provides the Multi-Org filtering based on the statement below in the where clause.

  SUBSTRB(USERENV ('CLIENT_INFO'), 1, 10)

• When logging into the applications, the CLIENT_INFO value was set to the appropriate operating unit organization ID for the session based on the profile option setting for “MO: Operating Unit”.
• In SQL*Plus, CLIENT_INFO could be set to point to a particular operating unit using

  EXECUTE dbms_application_info.set_client_info('');

Multi-Org Architecture in Release 12

In release 12 a more flexible architecture has been put in place to support Multi-Org Access Control (MOAC). This architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.

To accomplish this

• Multi-org views have been removed, and replaced with synonyms. For example, PO_HEADERS would no longer be a VIEW defined on PO_HEADERS_ALL, but rather a synonym which points to PO_HEADERS_ALL
• PO_HEADERS and PO_HEADERS_ALL are both synonyms which point to the table PO.PO_HEADERS_ALL. However, the view PO_HEADERS_ALL is unrestricted, whereas, PO_HEADERS will only display data for the user’s current operating unit(s) because of the VPD policy that has been assigned to it
• The data restriction is accomplished by assigning a virtual private database (VPD) policy also known as Row Level Security (RLS) to the synonym. This policy allows the system to dynamically generate restricting conditions when queries are run against the synonym
• Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES. These policies associate a function with an object, and when the object is accessed, this function can return additional restrictions on the object to restrict the data returned. The particular policy used to implement Multi-Org in release 12 is:
  1. Policy_name: ORG_SEC
  2. Policy_group: SYS_DEFAULT
  3. Package: MO_GLOBAL
  4. Function: ORG_SECURITY
• A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY. Below query will help you to find out the security policy applied on PO_HEADERS

  SELECT * FROM dba_policies WHERE object_name LIKE 'PO_HEADERS';

• With effect of this security policy, whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

  SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id )

  MO_GLOB_ORG_ACCESS_TMP:
  MO_GLOB_ORG_ACCESS_TMP is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period. The user will be able to access data for any org_id which has been populated into mo_glob_org_access_tmp. When a session is initialized in the applications, values will be populated into mo_glob_org_access_tmp for each of the operating units the user has access to based on their “MO: Security Profile” setting.

More about ‘MO_GLOBAL.ORG_SECURITY’

The purpose of Row-Level-Security is to hide certain data [based on some conditions]. RLS does so by appending a where clause to the secured object.

1.  MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled

How to set Org Context in R12?

BEGIN EXECUTE mo_global.set_policy_context('S',&org_id); END; --'S' means Single Org Context.

Where

1. option ‘S’ is used to fetch records from specified org_id
2. option ‘M’ is used to fetch records from all accessible Operating Units
3. option ‘A’ is used to fetch records from all Operating Units

If you want access to multiple organization data, you can change the first parameter to ‘M’ (for multiple orgs), all organizations accessible by user:

BEGIN apps.mo_global.set_policy_context('M',''); END;

How to check if user has access to a particular organization?

SELECT organization_id, name, mo_global.check_access(ORGANIZATION_ID) accessible FROM HR_OPERATING_UNITS;

Other procedures/functions of MO_GLOBAL API are listed below:

PURPOSE	PROCEDURE/FUNCTION	DESCRIPTION
Initialize	INIT	Set up multiple organizations context.
JTT initialize	JTT_INIT	Initialize multiple organizations for JTT based application.
Is multi-org enabled	is_multi_org_enabled	Check if the multiple organizations instance is enabled.
Check access	check_access	Check if the operating unit is accessible.
Get OU name	get_ou_name	Get the operating unit name.
Check valid org	check_valid_org	Check if the organization is valid.
Set policy context	set_policy_context	Set the application policy context.
Get current org Id	get_current_org_id	Get the current organization ID in the application context.
Get access mode	get_access_mode	Get the application context mode.
Get OU count	get_ou_count	Get the operating unit count on the access control list.
Get valid org	get_valid_org	Get the current default/valid organization.
Validate org id public API	validate_orgid_pub_api	Get the default organization and check if the organization is valid.
Is MO initialization done	is_mo_init_done	Check if the multiple organization is initialized.

For more information on the above APIs and how to use them, please go through Oracle’s Integration Repository

How to check if an Organization is present is Security Profile or not?

We have another MOAC utility API ‘MO_UTILS’ which can be used in reports and extensions.

SELECT organization_id, name, mo_utils.check_org_in_sp(ORGANIZATION_ID, 'OPERATING_UNIT') accessible FROM HR_OPERATING_UNITS; -- Returns Y or N depending on whether user has access to given organization

Other procedures/functions of MO_UTILS API are listed below:

PURPOSE	PROCEDURE/FUNCTION	DESCRIPTION
Purpose	Procedure/Function	Description
Get Ledger Name	Get_Ledger_Name	Returns the ledger name.
Get ledger information	Get_Ledger_Info	Returns information about the ledger.
Get Default operating unit	get_default_ou	Gets the default operating unit from MO: Default Operating Unit profile or from current organization.
Get Default operating unit Id	get_default_org_id	Returns the organization ID of the default operating unit.
Check org in security profile	check_org_in_sp	Checks if the specified organization is present in the security profile.
Check ledger in security profile	check_ledger_in_sp	Checks if all operating units of a ledger are included in the security profile.
Get organization name	Get_Org_Name	Returns the operating unit name for an organization ID.
Get organization IDs for the ledger	get_orgid_fr_ledger	Returns the operating unit ID and the number of operating units in the given ledger.

Please drop a comment if you need any more additional information, I will do my best to help you out

Oracle Tables with Suffix _ALL, _TL, _B, _F, _V, _VL, _S, _A, _AVN and _ACN

_ALL  Table holds all the information about different operating units in a Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
Check out the article -> MOAC – Oracle Apps ORG_ID, Multi Org Concept

_TL are multi language tables tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_B these are the BASE tables.
They are very important and the data is stored in the table with all validations. It is supposed that these table will always contain the perfect format data. If anything happens to the BASE table data, then it is a data corruption issue.

_F these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.

_V tables are the views created on base tables

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(‘LANG’).
_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables

_AVN and _ACN are Audit Shadow Views (when data was changed, and with what values

_KFV is the view which contains all the information about Key Flex Fields(KFFs).

_DFV is the view which contains the information about Descriptive Flex Fields(DFFs).