Oracle Forms Personalization

Oracle Forms Personalization

With the Oracle E-Business Suite release 11.5.10, the Oracle has introduced a mechanism which revolutionizes the way the forms can be customized to fulfill the customer needs. For many years, Oracle Applications has provided a custom library using which the look and behavior of the standard forms can be altered, but the custom library modifications require extensive work on SQL and PL/SQL. In the release 11.5.10, Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.

Why Forms Personalization?

  • Oracle Supports personalization unlike customization.
  • Personalizations are stored in tables rather than files.
  • Will not have a bigger impact when you upgrade or apply patches to the environment.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Easy to disable/enable with click of a button.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.
  • Can be applied to new responsibilities/users easily.
  • Can be restricted to a function or form.

What can be implemented through Forms Personalization?

The below can be done using Personalization: 

  • Zoom from one form to another.
  • Pass data from one form to another through global variables.
  • Change LOV values dynamically.
  • Enable/Disable/Hide fields dynamically
  • Display user friendly messages when required
  • Launch URL directly from oracle form
  • Execute PL/SQL programs through FORM_DDL package
  • Call custom libraries dynamically

Personalization Tables:







Invoking the Personalization screen:

The personalization form should be used to implement the custom rules on a specific form. The specific form refers to the desired form on which you want to apply the custom business logic or modify the form behavior.

The personalization form is invoked by…

Menu Navigation: Help > Diagnostics > Custom Code > Personalize

Disable the personalization feature:

It is possible that a change you make completely breaks a form, to the point that it will not even run! Here’s how to recover:

  • On the pulldown menu, choose Help > Diagnostics > Custom Code > Off
    • This will disable all callouts to Forms Personalization
  • Run the form of interest
    • It should run now, because your changes were skipped
  • Invoke the Personalization screen and correct the problem
  • On the pulldown menu, choose Help > Diagnostics > Custom Code > Normal to re-enable processing of Personalizations


Although it is faster than a speeding bullet, it is not able to leap over tall buildings:

  • You can only change what Forms allows at runtime:
    • Cannot create new items
    • Cannot move items between canvases
    • Cannot display an item which is not on a canvas
    • Cannot set certain properties
    • Cannot change frames, graphics, boilerplate
  • You can only respond to certain Trigger Events:
    • WHEN-VALIDATE-RECORD (not in all forms)
    • Product-specific events
  • May interfere with, or be overridden by, base product code
  • Expected user is an Admin/Developer
    • Knowledge of Oracle Developer is extremely desirable
    • Knowledge of PL/SQL, Coding Standards and/or APIs required in some cases
  • Normal rules for customizations apply
    • Extensive testing in a Test environment is required!

Relationship with CUSTOM library:

  • CUSTOM is a stub library Oracle ships that receives Trigger Events. Customers are free to add any code they like to it.
  • CUSTOM and Form Personalizations drive off the same Trigger Events.
    • Form Personalizations are processed first, then the event is sent to CUSTOM
  • CUSTOM can do more because it has complete access to all PL/SQL and SQL.
  • But for most changes, Form Personalizations is adequate and is significantly simpler.

Custom.pll in Oracle Application

Custom.pll in Oracle Application

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.

CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-

1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu

Where is this located?

Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this?

Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?

 Once you make changes you need to compile the pll. Use the F60gen to compile it

f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.

For following Events call will go to CUSTOM Library:

SPECIALn (where n is a number between 1 and 45)
KEY–Fn (where n is a number between 1-8)

Custom Library contains Custom Package which is having two Functions and one procedure.


This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is

    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');


    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
      return FALSE;
    end if;

end zoom_available;

Sample code2:

function zoom_available return Boolean is

    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');


   if (form_name = 'APXINWKB' and block_name = 'INV_SUM_FOLDER')
      return TRUE;
    elsif (form_name = 'APXINWKB' and block_name = 'LINE_SUM_FOLDER')
      return TRUE;
      return FALSE;
    end if;

end zoom_available;



This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard

By default it returns custom.standard.

Sample code:
function varchar2) return integer is
     if event_name = ’MY_CUSTOM_EVENT’ then
     return custom.override;
     return custom.standard;
     end if;
end style;


3] EVENT: 
This procedure allows you to execute your code at specific events including:

  —    ZOOM






By default this routine must perform ‘null;’

Sample code:
procedure event(event_name varchar2) is

form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);


     if (form_name = 'XXBI' and block_name = 'xxcc') Then
        if(event_name = 'WHEN-NEW-FORM-INSTNACE')
           --Write your code here
     elsif(event_name = 'WHEN-VALIDATE-RECORD')THEN
           --Write your code here
       end if;
     end if;


How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.

Forms Personalization: an alternative of custom.pll

In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.

Advantages of Forms Personalization over Custom.PLL:

  • Forms personalization can be used by an user with limited PL/SQL knowledge. 
  • Changes take place immediately on reopening the form.
  • Anything which can be done using Custom.PLL can be done using Forms Personalization also.
  • Personalizations are stored in base tables related to Form Personalization.
  • CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
  • Easy to disable/enable with click of a button.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

Steps To Register Unix Shell Script As A Concurrent Program

Steps To Register Unix Shell Script As A Concurrent Program

We can register an Unix shell script in our oracle application through a concurrent program. Here are the steps.

Step 1:

Copy the .prog script in ASCII mode to the bin directory of your application top directory. For example, call the script XXSHELL.prog and place it under $XXCUST_TOP/bin

step 2:

Check the file permissions. Sometimes it is required to give full permission to the script.

step 3:

Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called XXSHELL.prog , then use this:

cd $XXCUST_TOP/bin

ln -s $FND_TOP/bin/fndcpesr XXSHELL

This link should be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.

step 4:

Register the concurrent program, using an execution method of ‘Host’. Use the name of your script without the .prog extension as the name of the executable.

For the example above: Use XXSHELL as executable name.


Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher. Make sure your script returns an exit status also.

fndcpesr is a standard utility available in $FND_TOP directory. It is mainly used by the application to parse the above four arguments to the shell scripts.

Triggers in Oracle Forms

Triggers in Oracle Forms

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Block Processing Triggers:

Block processing triggers fire in response to events related to record management in a block.

  • When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
  • When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
  • When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Interface Event Triggers:

Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.

  • When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
  • When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
  • When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
  • When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
  • When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
  • When-Window-Activated Initiate an action whenever an operator or the application activates a window.
  • When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
  • When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Master/Detail Triggers:

Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.

  • On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
  • On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
  • On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

  • On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
  • On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.

  • When-Validate-Item
  • When-Validate-Record

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.

  • Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
  • Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
  • Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
  • Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
  • Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
  • Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
  • Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
  • Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.
  • When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
  • When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
  • When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
  • When-New-Item-Instance Perform an action immediately after the input focus moves to a different item. 

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.

  • On-Delete
  • On-Insert
  • On-Update
  • On-Logon
  • On-Logout
  • Post-Database-Commit
  • Post-Delete
  • Post-Insert
  • Post-Update
  • Pre-Commit
  • Pre-Delete
  • Pre-Insert
  • Pre-Update

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

  • Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
  • Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.


Modal & Modeless Windows in Oracle Forms

Modal & Modeless Windows in Oracle Forms

A window in oracle forms is a container for all visual objects that make up a Forms application. You can create two different type of windows in oracle forms. Lets have a brief comparisons between these two types of windows.

Modal Window:

A modal window is a restricted window that the user must respond to before moving the input focus to another window. Modal windows:

  • Must be dismissed before control can be returned to a modeless window
  • Become active as soon as they display
  • Require a means of exit or dismissal

Modeless Window:

A modeless window is an unrestricted window that the user can exit freely. Modeless windows:

  • Can display many at once
  • Are not necessarily active when displayed
  • Are the default window type


Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of a Concurrent Program.


        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"

        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv

        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;

SET operators in Oracle

SET operators in Oracle

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL : Combines the results of two SELECT statements into one result set.

UNION : Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS : Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

INTERSECT : Returns only those rows that are returned by each of two SELECT statements

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

  1. The result sets of both the queries must have the same number of columns.  
  2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

Rules and Restrictions on Set Operations:

  • Column names for the result set are derived from the first SELECT.
  • If we want to use ORDER BY in a query involving set operations, we must place the ORDER BY at the end of the entire statement. The ORDER BY clause can appear only once at the end of the compound query.
  • Component queries are executed from top to bottom. If we want to alter the sequence of execution, use parentheses appropriately.
  • Set operations are not permitted on columns of type BLOB, CLOB, BFILE, and VARRAY, nor are set operations permitted on nested table columns.
  • Since UNION, INTERSECT, and MINUS operators involve sort operations, they are not allowed on LONG columns. However, UNION ALL is allowed on LONG columns.

MULTI ORG in Oracle Application

MULTI ORG in Oracle Application

The ability to define multiple organizations and the relationships among them within a single installation of Oracle Applications is called multi organization or Multi-org. Multi Org is the future used to store the data of multiple organizations in a single Database instance.

Basic Business Needs:

  • Use a single installation of any Oracle Applications product to support any number of organizations, even if those organizations use different sets of books.
  • Define different organization models.
  • Support any number of legal entities within a single installation of Oracle Applications.
  • Secure access to data so that users can access only the information that is relevant to them.
  • Sell products from a legal entity that uses one set of books and ship them from another legal entity using a different set of books, and automatically record the appropriate intercompany sales by posting intercompany accounts payable and accounts receivable invoices.
  • Purchase products through one legal entity and receive them in another legal entity.

Basically the different entities in multi-org are:

  • Business Group (BG)
  • Sets of Books (SOB)
  • Legal entities (LE)
  • Operating units (OU)
  • Inventory organizations (IO)

Organization Structure Example:

Business Group (BG):

The business group represents the highest level in the organization structure, such as the consolidated enterprise, a major division, or an Operation Company. A BG is used to secure human resources information like generation of employee numbers, generation of applicants, position flex fields, Job flexfields, Grade Flex field, Fiscal year, etc.

Set of Books (SOB):

A SOB is a collection of Currency, Calendar and Chart of Accounts (COA). Oracle General Ledger is used to secure Journal transactions (such as journal entries and balances) of a company by set of books. For each organization of the Business Group we need to define a set of Book. A company which operates in separate cities or separate line of businesses may separate their accounting transactions across units through separate Set of Books. A Business Group can have one or more set of Books.

Legal entities (LE):

A legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other legal entity information to these types of organizations. Separate Legal Entities may share same set of Books.

Operation Unit (OU):

An operating unit is a division or a Business unit of the legal entity. At this level we are going to maintain the information of sub‐ledgers. We are going to maintain the ledgers at Legal Entity level. Receivable, Payables, Assets, etc. are comes under Operation Unit level. Each user sees information only for their operating unit. Responsibilities are linked to a specific operating unit by the MO: Operating Unit profile option.

Inventory organizations (IO):

An inventory organization represents an organization for which you track inventory transactions and balances, and manufactures or distributes products. Examples include manufacturing plants, warehouses, distribution centers, and sales offices. The following products and functions secure information by inventory organization: Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and purchasing receiving functions. To run any of these products or functions, you must choose an organization that is classified as an inventory organization.

Find out the URL of the Application in the database

Find out the URL of the Application in the database:

Option 1:

select HOME_URL from icx_parameters;

Option 2:

        AND LEVEL_VALUE=0;

Summary column vs. Formula Column vs. Placeholder Column

Summary column vs. Formula Column vs. Placeholder Column

We use these columns in our oracle reports, but always have few doubts about which column to use for what purposes. Here are few brief differences among these columns and the purpose of their use in oracle reports.

Summary column:

It summarizes another column and can recalculate for each record in a specified group. The following properties apply specifically to summary columns:

  • Function: The calculation to be performed on the values of the column specified in Source.
  • Source: The name of the column whose values are to be summarized.
  • Reset At: The group at which the summary column value resets to zero.
  • Compute At: The group for which a % of Total summary column is computed.

The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes. The Report Wizard does not support page summaries. If you select a page summary in the Field tab of the Report Wizard, an error message appears.

Formula Column:

A formula column performs a user-defined computation on the data of one or more other columns. A formula column executes a PL/SQL function and must return a value. The value can be Character, Number, or Date and returned value must match data type.

Placeholder Column:

A placeholder column is an empty container at design time. The placeholder can hold a value at run time that has been calculated and placed into it by PL/SQL code from another object.Using placeholder columns, you can:

  • Populate multiple columns from one piece of code. You can calculate several values in one block of PL/SQL code in a formula column and assign each value to a different placeholder column. Thus, you create and maintain only one program unit instead of many.
  • Store a temporary value for future reference. For example, store the current maximum salary as records are retrieved.

A Scenario:

The goal is to design a salary report of all employees. The aim of the report is to:

  • Calculate and temporarily store the name of the employee who earns the highest salary in the company.
  • Display the highest earner and the maximum salary once at the beginning of the report.

For this report, you need to create the following columns:

  • A summary to show the maximum salary for the company.
  • A placeholder to contain the highest earner’s name at run time.
  • A formula to:
    • Compare each employee salary with the maximum salary.
    • Populate the placeholder with the employee name if salary equals maximum salary.