Selectively Enabling Fields or Descriptive Flexfields on a Query Only Form

We have recently worked for a client who wanted a form within Oracle E-Business Suite to be read-only on a particular responsibility, except for the descriptive flexfield. I’ve also by chance come across a couple of posts on the Oracle OTN Forums recently too that are asking for very similar requirements, so I thought I would post a simple example here to follow. When faced with such a requirement the following options may immediately come to mind.

• Forms Personalizations
• Custom Library
• Customization to the standard form

Each of the above has its own advantages and disadvantages which I won’t go into here, other than to say the way we chose, and the way I will be demonstrating, is to use Forms Personalizations with a tiny hint of forms customisation.

The form we were asked to work on is PERWSEAD, or to normal people who don’t recognise forms by their names [ 🙂 ], the HRMS Absence screen. The form is launched from the Person maintenance screen, and in our situation we were asked to only make certain records read-only, based upon the absence type. So rather than the standard method of making a form query only (by adding a QUERY_ONLY=YES parameter in the function definition), which would affect ALL records, we instead personalized the form to make it read-only only where the absence category is ‘S’. However for the purposes of this example, we will not complicate things, and just assume it is read-only for all records. So we can create a single personalization which fires on the WHEN-NEW-FORM-INSTANCE trigger to set the block(s) we are considering to disabled.

Once that is done we then must set the descriptive flexfield as read-only. The DFF item is unfortunately part of the ABA block, so even if we could enable the fields on the form, the block property would prevent us from updating any values. This can be proved by using the Help > Diagnostics > Examine menu to manually set the values and attempting to save. So instead we must ensure that the block is enabled at the point the flexfield is opened. Luckily for us, the flexfield window is modal meaning that you can’t modify the values on the form until you close the window. So we can create a process as follows.

It is at this point we hit a small problem with the PERWSEAD form (or at least our version from an R12.1.3 instance) – the trigger style of the WHEN-NEW-ITEM-INSTANCE trigger on the DF item is set to Override. That trigger contains a USER EXIT which calls the flexfield window, meaning that the form level trigger never actually fires. And unfortunately for us, that is what personalizations hook into! So we instead have to have a small change to the standard form to change that trigger style to either Override or After. Obviously changing standard objects is not ideal and in this case we thoroughly checked that this modification would not have any impact on existing functionality.

Furthermore we also flagged the module as having been modified in Oracle Application Manager Dashboard so that any patches that may touch that file will warn us that it contains modifications. Make the change, upload the file to your EBS instance and compile into the $PER_TOP/forms/US/ directory.

Now we can start to implement our process map. So the first process we create sets the block to be updateable when the flexfield is entered.

Now we need to disable it again once the user leaves. By looking in the form definition, we know what item is the next item that is focussed after the flexfield. So on that item, we hook into the WHEN-VALIDATE-ITEM trigger and say “if the block is updateable then set it to be non-updateable”.

The final problem we hit is that the form block reverts to non-updateable once the DFF is exited. There are different options here, however given the nature of the change we were doing, we decided to add in a secondary action which commits the form upon leaving the block. Obviously the only changes that can be made are those when the form is enabled, which is the DFF.

Save all that, exit the form and go back in again. Now we can see when I enter the flexfield the block becomes enabled and I can make changes. Once I leave the flexfield, the block is disabled. However I can click Save and the record is committed to the database, but then reverts back to being disabled.

Obviously take appropriate care when modifying standard objects. Ideally you would “XX” the form up as a copy and modify that version, however this can become difficult if the form is launched from within other forms. As always ensure your Oracle licence permits you to make such modifications and always take backups.