Forms Personalization

Today’s post is about forms personalization. Forms personalization is a way to customize Oracle Applications without requiring a broad knowledge on programming languages. Although, if you want to perform more complex customizations you might require some PL/SQL skills.

I will try to explain with a very simple example the basic functionality of forms personalization and on the way I will review on how to call a stored procedure within the personalization and how to catch messages from the procedure and print them on screen (you’ll be surprised on how useful can this messages be)

As usual, here is the list of what we need, and how we will do it.

We need:

1.- Oracle Applications (obviously)
2.- The Utilities and Diagnostics profile enabled at the user level (you should be careful with this profile, since it enables the examine function for Oracle forms)
3.- A bit of knowledge on Forms Personalization. If you are new to this you can review some information here and here
And this other document contains more detailed information on the topic.
4.- SQL and PL/SQL skills will be very helpful when developing more complex personalizations. If you feel like learning PL/SQL you can download the Oracle PL/SQL bible here

How we will do it:

1.- Clearly define what your personalization needs to accomplish. This can be as simple as setting a field as required or inactivating a field, and as complex as inserting records into an interface table, calling public APIs to perform transactions, launching Oracle preseeded or customized Oracle Workflows.
2.- From Oracle EBS, identify what objects and events you need to use and set up the personalization.
3.- If you are calling a procedure, you might need to catch a message as a result. The easiest way to do this is to create a table and insert the results on this table. This way you will just need to perform a simple SQL statement to get the message.
4.- Incorporate security to your personalization if needed.
5.- Test your personalization.

Ok so let’s start.

First thing to do is to setup the Utilities and Diagnostics profile at the user level as Yes. TO do this log into System Administrator responsibility—Profiles. Now query for Utilities:Diagnostics at the user level (be careful with this you should NEVER set it up at the site level since you will grant access to every user which can lead to very harmful results).

Once this profile has been set up log into the Oracle EBS form that you which to customize. Go to Help – Diagnostics – Custom Code --- Personalize.
This will make the Forms Personalization window to prompt.

Now for the purpose of this example we will enable a menu in the form. This menu will call a procedure in the database. This procedure will perform some actions (this actions can be anything that you want) and return a message to the user. I have put all the figures of this post in a file that you can access here


First let’s create the menu. Forms Personalization works with pre-defined events and objects. WHEN-NEW-FORM-INSTANCE event as you can guess is called every time the form is loaded. This event is useful for things that need to be done once (such as adding menus and initialize variables. If you are not sure when an event is called, you can set forms to show all events by going to Help—Diagnostics—Custom Code—Show Custom Events. This will launch a message for every event that is executed.

What we will do is first create a personalization. Input a sequence and a description and set the event to be WHEN-NEW-FORM-INSTANCE. Input any condition for this personalization if you need to, leave blank if none applies. On the bottom you can set the security for this personalization. You can use industry, responsibility or user. I will set this personalization to perform just for one responsibility. Check Fig1.

Now we need to set an action for this personalization, go to the Actions tab. Input a sequence and a description for the action. Set type as Menu. On the right set a Menu entry. Be sure that you don’t override one of the existing menus. All the entries that start with menu can be found under the Actions for menu. All the ones that start with Special can be found under the Tools form Menu. For this example we will use the Tools menu. Set up menu entry as Special 15. Set the menu label as ‘Call Procedure’.
You can also set the menu to be enabled only on a certain form block. Save your work. Check Fig2.

Now go to the condition tab again. Create a new sequence and a description for the personalization. Set the description as ‘Call Procedure’. On trigger event now set the menu we just created (Special 15). Go to the actions tab.
Set a sequence for the action and choose Builtin type. Now on the right under Builtin type set Execute a procedure. In argument we need to input the call to the procedure, this can be a little tricky, so review this example carefully:

='declare v_field_value VARCHAR2(500);begin scheme.procedure('''||${item.tolines_blk.header_id.value}||''',
'''||${item.tolines_blk.line_id.value}||''','''||fnd_global.user_id||''','''||fnd_global.org_id||''');
end'

Basically what we are doing is to call the procedure and passing the necessary arguments. The procedur in the example uses four input parameters. I have put two types of arguments, ones are items within the form (item.tolines_blk.header_id.value) and the other ones are sql statements (fnd_global.user_id). You can also pass constant values to the procedure.
Your procedure structure should be like the following:

Create procedure (argument1 argument type
….)

…Validations
…Save message in a variable
…Perform actions if validations passed
…Save message in custom table
End

In order to show the output message to the user, what we do is to save it into a table and the just query it up. You can use a table that has the following structure

Field Type Description
USER_ID Number Stores the user_id for the message
MESSAGE VARCHAR2(1000) Stores the output message
STATUS Varchar2(1) Status flag for the message

You can use the following sentence to create the table:

create table table_name (user_id number, message varchar2(1000), status varchar2(1));

After you created the table, remember to grant access to the user apps so the table can be accessed from Oracle EBS.

grant all on table_name to apps


On your procedure you should be cleaning the table for the specific user_id just before inserting the new message, this way you will only have one message for every user at any time. You should do something like this at the end of your procedure for every execution:

DELETE FROM table_name where user_id = p_user_id
INSERT INTO table_name VALUES (p_user_id,p_message,p_estado);

Now save your work. Check Fig4

Create a new personalization. Set a sequence and as description set ‘OUTPUT MESSAGE’.
Input responsibility security.
You can set up a condition for this personalization, for instance since we are saving a status in the message table so we can send no message if the status is successful and we can send a message if status is unsuccessful.
Now go to the actions tab and set the action type as message.
Select Show as message type and under the message text set the following:

=(select message from my_log_table where user_id = (select fnd_global.user_id from dual))

This will bring up the message stored in the message table.

Save your work.

Now close the form and reopen it. This will make the personalization to take effect.

Go to the Tools menu and launch the procedure. Check Fig 5 and 6.

And that’s pretty much of it.

I didn’t put a sample procedure since this should be developed based on your needs. Bottom line here is to know that we are able to launch a procedure within Oracle Personalization without the need to modify Oracle Forms.

Diego