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:

FND_FORM_CUSTOM_RULES

FND_FORM_CUSTOM_ACTIONS

FND_FORM_CUSTOM_SCOPES

FND_FORM_CUSTOM_PARAMS

FND_FORM_CUSTOM_PROP_LIST

FND_FORM_CUSTOM_PROP_VALUES

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

Limitations:

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-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-RECORD-INSTANCE, WHEN-NEW-ITEM-INSTANCE
    • 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.
Advertisements

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:

 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)

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

1] ZOOM_AVAILABLE:

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

begin

    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
    else
      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');

begin

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

end zoom_available;


 

2] STYLE:

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 custom.style(event_name varchar2) return integer is
begin
     if event_name = ’MY_CUSTOM_EVENT’ then
     return custom.override;
     else
     return custom.standard;
     end if;
end style;


 

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

  —    ZOOM

  —    WHEN-NEW-FORM-INSTANCE

  —    WHEN-NEW-BLOCK-INSTANCE

  —    WHEN-NEW-RECORD-INSTANCE

  —    WHEN-NEW-ITEM-INSTANCE

  —    WHEN-VALIDATE-RECORD

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’);

begin

     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
     else
          null
       end if;
     end if;
end;


 

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.