The personalization is the feature using which we can customize the behavior of Oracle Form based screens. This is the feature which is present in the Oracle 11.5.10 onwards. Earlier for making changes based on screen we need to go to ‘Custom.pll’ and make the changes and follow the lengthy process of the compilation and its migration. But with Personalization we can do customization in the form screen in a quicker and smarter way. There are many options available on form personalization screen like changing properties, executing built-ins, displaying messages, adding menu entries, adding rules, conditions and actions. Hence, the form personalization is effective immediately.

A simplest approach for customizing any form could be introducing the desired condition in the given form and based on the condition defining the action as per the requirement. In the below mentioned cases we have done the same and will now discuss each of them in detail with the approach followed and screen shots.

the main purpose of writing this blog is to highlight the validation to be done in the oracle form buttons. As this is a common belief that we cannot do any validations on standard oracle buttons. But this is not the case always; we can do validations on oracle form buttons if the button click opens a new form. We can achieve these validations by using block level triggers which allows us to go to the desired block.

Here in every case we will define one requirement and figure out the way to fulfill them in their approach. Every case will contain some steps, and between these steps we will discuss the underlining principles of Oracle forms which prompted us to choose the mentioned approach.

Before I begin with my cases I would like to mention personalization form is invoked by :

Menu Navigation: Help à Diagnostics à Custom Code à Personalize.

Case I : Doing validation on some button of the form.

BUSINESS REQUIREMENT : In standard oracle shipping transaction form we have button named ‘Ship Confirm’. When user clicks on this button it opens a new form which is nothing but ship confirm form which is used to ship confirm the given delivery. Now our requirement was related to deliveries, it says On same screen, when user clicks ship confirm, we need to stop the standard oracle ship confirm if any of the delivery detail lines is not in Staged/Pick Confirmed status (wdd.released_Status = Y), we should show a message ‘Cannot do Ship Confirm as one or more lines for given Delivery is not picked. Please transact Move Order before Ship Confirm.’.

Approach: We will try to fulfill this requirement in the below mentioned steps.

Step I : For achieving this requirement we are taking the delivery 49107 into consideration. The delivery 49107 is in open status and more than one line for this delivery are not in Staged/Pick Confirm status. Initially if we will try to Ship Confirm delivery 49107, standard oracle will allow us to do so just by clicking on Ship Confirm button as shown in the below screen shot.

Step II: Now first of all we will have to find the block where the personalization should take place. We will press AltàHàDàE to know the block name, it has been shown in the below screen shot.

v Before we move on to the next step we will be discussing some fundamentals of Oracle Forms which will help beginners to understand the approach in the better manner. Next we will explain ‘WHEN-NEW-RECORD-INSTANCE’ trigger event which is used for the above requirement. Forms mainly consists of four sections i.e.;

Ø Rules: Rules are kind of controllers which determine the sequence of implementations to be done on the given form. Each Rule is associated with the sequence and description.

Ø Conditions: A Condition is an optional SQL code fragment that is evaluated when an Event occurs. If the Condition evaluates to TRUE then the Actions are processed.

Ø Context: Context denotes whom does the rules apply. It simply defines the scope of the given rule and determines the level at which rule will apply. It can be Site, Responsibility, User or Industry.

Ø Action: Actions are the operations which are to be performed when the condition and context holds true.

v WHEN-NEW-RECORD-INSTANCE: This trigger is fired each time the cursor moves on to new record. The trigger object must contain the block name of interest.

Our motive for selecting this trigger for defining the Condition and the corresponding Action was because our personalization was delivery specific and every delivery has its own set of records. So, every time when there is some new delivery in open status which has one or more line in Staged/Pick confirm status, our personalization will impact that delivery and do not allow to Ship Confirm when Ship Confirm button is clicked by the user.

Step III. Now we will define a function with description TF48.08 on the form with following attributes under the Condition tab.

Function Name : WSHFSTRX

Description : TF48.08 : Restricting the Ship Confirm if one or more line is not picked.

Condition :

Trigger Event: WHEN-NEW-RECORD-INSTANCE

Trigger Object: SHIP_CONFIRM

Condition: (select count(1) from wsh_delivery_details wdd, wsh_delivery_assignments wda

where wdd.delivery_detail_id = wda.delivery_detail_id

and wda.delivery_id = ${item.dlvy.delivery_id.value}

and wdd.released_status not in ('Y','C') ) > 0

Step IV .Now based on the above mentioned condition we will perform four different actions on this form to achieve our requirement.

v Action I : First of all we will set up the restriction message for display on the form when user clicks on the standard oracle ship confirm button this message will be displayed and it will suggest user Transact the given Move Order before making Ship Confirm.

Message Type : Show

Message Text : Cannot do Ship Confirm as one or more lines for given Delivery is not

picked.Please transact Move Order before Ship Confirm.

v Action II. Next we will go to Builtin type Action for ROLLBACK.

This Rollback is performed on built-in type FORMS_DDL to bring back our database to the previous state. It will help us to avoid the risk of locking of records in the some other dependent form.

As in this case Transact Move Order is dependent on Ship Confirm and performing ROLLBACK will simply terminates the probable locking of records held up by Ship Confirm Form for processing.

Buitin Type : FORMS_DDL

Argument : ROLLBACK

Action III. Now our next motive is to bring back our current session to the Delivery Block instead of Confirm Delivery Form. Ideally if Delivery is in open state and there is no delivery lines available yet to be staged/pick confirm, standard Oracle open Confirm Delivery Form as shown below :

So , just to avoid the opening of this form we will perform Buitin Type GO_BLOCK for Argument as DELIVERY. This will bring us back to the Delivery block of Shipping Transaction form. The action performed here are given as below and shown in screenshot:

Builtin Type : GO_BLOCK

Argument : DLVY

v Action IV: At last we will use RAISE FORM_TRIGGER_FAILURE to stop the processing.

When raised it will cause the currently active trigger to stop and return control to the entity

which called it.

Builtin Type : RAISE FORM_TRIGGER_FAILURE

Step V. Once when all the above four steps are done we will go to Shipping Transaction Form and query the same delivery 49107. We will try to ship confirm this delivery. Now when user clicks on

‘Ship Confirm’ button the application will show the note as shown below in the screen shot.

As this delivery 49107 has one or more than one line which not yet picked, therefore our personalization will not allow user to ship confirm this delivery.

Hence the personalization is done as per the requirement.

** There are many more to come . Pleae provide your inputs.**

Continued..... Oracle Form Personalization- I

Thank YoU !!!!!

Linked In

Oracle Form Personalization

An Approach to Oracle Forms Personalization.