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

Difference between Valuesets and Lookups

Difference between Valuesets and Lookups

Difference 1

Value sets can be attached to parameters of a concurrent program or to a DFF segments where as Lookups are attached only to the fields of a Form/Page

Difference 2

Lookups can be maintained by end users where as Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.

Difference 3

Lookups can have translated values in different languages but not the values in the Value Sets

Difference 4


Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic.
On the contrary, Lookup Codes are Static list of values which can only be entered through Lookups Form.

Difference 5

We have several different types of value sets but not lookups.

Query for link between OM to Receivables

SELECT ooh.order_number, ool.line_number, ool.ordered_item,
       ool.ordered_quantity * ool.unit_selling_price,
       rcta.trx_number invoice_number, rcta.trx_date, rctl.line_number,
       rctl.unit_selling_price, ooh.org_id
  FROM oe_order_headers_all ooh,
       oe_order_lines_all ool,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctl
 WHERE ooh.header_id = ool.header_id
   AND rcta.interface_header_context = 'ORDER ENTRY'
   AND rctl.interface_line_context = 'ORDER ENTRY'
   AND rctl.interface_line_attribute1 = TO_CHAR (ooh.order_number)
   AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id)
   AND rctl.customer_trx_id = rcta.customer_trx_id
   AND ooh.order_number = NVL (:p_order_number, ooh.order_number)
--AND  ooh.org_id=nvl(:p_org_id,ooh.org_id)

Create Host/Shell Script Concurrent Program in R12

Create Host/Shell Script Concurrent Program in R12

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications R12

Step 1 : Create the executable name HOST_TEST_PROGRAM and Executable File Name demo_sh

Step 2 : Register the concurrent program Demo Shell Script Concurrent Program

Options : encrypt it will avoid displaying apps password in unix command prompt
wont get the password using unix command ps -ef | grep "apps"

Step 3 : Adding two parameter in the concurrent program
             Parameter1  Parameter2

Step 4:  Add the concurrent program in to the request group & Add that request group into the Responsibility.

Step 5:

1.Create a shell script ( say demo.prog)  in appropriate $CUSTOM_TOP/bin directory
Script name : demo.prog

  #! /bin/ksh

  param1="$5"

  param2="$6"

  echo "Parameter1 : $param1"

  echo "Parameter2 : $param2"

2.Change Permissions to 755 for the demo.prog file. ($chmod 755 $CUSTOM_TOP/bin/demo.prog)

3.Create soft Link to the shell Script ( ln -s $FND_TOP/bin/fndcpesr   $CUSTOM_TOP/bin/demo_sh) to create demo_sh file.

4. First 5 params are reserved for Application, user defined params starts from 5th param

                      ORA_USER_PASS=$1

                     USERID=$2

                     USERNAME=$3

                     REQUESTID=$4

5. fndcpesr is a standard utility available in $FND_TOP directory. Its mainly used by the application to parse arguments for shell scripts.

Step 6:

Submit the concurrent program Demo Shell Script Concurrent Program

from Added responsibility

Program should be completed in normal and out put parameter values are displayed in log file.

Note : The above method should allow spaces in the user defined parameters