Oracle Form Personalization - II

Continued from .... An Approach to Oracle Form Personalization

{ii} Case II : Making some item non editable in the given block of the form.

Business Requirement: The requirement was to make the Delivery name non- editable in case if

it is in open status.

Initially on Shipping Transaction Form all the Deliveries in Open Status are editable as shown below in the screenshot:

Approach : We will achieve this by using the ALTERABLE_PLUS property on trigger event

‘WHEN-NEW-RECORD-INSTANCE’.

 ALTERABLE_PLUS:

The ALTERABLE_PLUS property is intended to allow or disallow changes to all instances of an item (all rows of the block). Setting this property to false prevent the user from making a change to that item on any row, regardless of whether each record is a new or queried record.

In the given below steps we will explain the steps how to use this property to make the item non-editable on the block.

Step I : As this personalization requires every record for deliveries which are in open status to be made as non-editable, we will select ‘WHEN-NEW-RECORD-INSTANCE’ as trigger event.

Next we will press AltHDE on the delivery name to find out the trigger object name.

Trigger Event : WHEN-NEW-RECORD-INSTANCE

Trigger Object : Dlvy

Condition : $(item.dlvy.stattus_name.value) = ‘Open’

Processing Mode : Both

Step II. In second step we will display the message note which will tell user that oipen deliveries cannot be modified.

Message Type : Show

Message Text : Cannot modify open Deliveries.

Step III. Next we will set the property for the given condition. The action type property is used to set the property of objects.We will choose ALTERABLE_PLUS from the list of available properties for the target object delivery. Setting this property to false prevent the user from making a change to that item on any row, regardless of whether each record is a new or queried record.

Object Type : Item

Target Object : DLVY.NAME

Property Name : ALTERABLE_PLUS

Value : FALSE

Step IV. We will click on save button to save the current changes and reopen the same form to verify the outcome. Now we can see the Deliveries in open status are made non-editable.

Hence the personalization is done as per the requirement.

{iii} Case III : Restricting the values appearing in LOV field of a form.

BUSINESS REQUIREMENT: In standard Miscellaneous transaction form we have an LOV (List of values) named ‘Type’. It allows user to choose a particular Transaction Type amongst the list of appearing values. Our requirement is to restrict the list of transaction Types appearing in the LOV based on some condition through form personalization. The condition based on which the LOV should populate is the value of a DFF that is present in Transaction type table. The transaction types, for which this DFF is enabled, should be made available to the Miscellaneous Type form.

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

Step I: For achieving this requirement we will take the organization id ‘g97’ into consideration. Currently, if we try to select a transaction type in Miscellaneous Transaction form, standard oracle will list all the available transaction types, as shown in the below screen shot.

Navigation: Inventory  Miscellaneous TransactionType

Step II: To restrict the Transaction types LOV based on the enabled DFF value, we will navigate to form personalization page, following the navigation :

Help Diagnostics Custom Code> Personalize.

We will define a trigger for the Transaction Types LOV by going to Conditions Tab , with Trigger event as WHEN-NEW-ITEM-INSTANCE and Trigger Object as MISC_TRX.TRANSACTION_TYPE as shown in the below snapshot. The Object MISC_TRX.TRANSACTION_TYPE relates to the Transaction Type field present in Miscellaneous Transaction form.

 WHEN-NEW-ITEM-INSTANCE: This type of trigger is fired whenever a new field/item gains focus. Specifically, this event is fired when we navigate to accept an item, when Form Builder is ready to accept input in an item that is different than the item that previously had input focus.

We will now set the Trigger Object that is the field Transaction Type, for which we want to perform a specific action.

Condition:

Trigger Event: WHEN_NEW_ITEM_INTSANCE

Trigger Object: MISC_TRX.TRANSACTION_TYPE

Step III: Next, we will define the Action required to be taken in the trigger to populate the LOV

Let’s first look at the various Action types that we will be using in this trigger.

 Action Type: Property: - Property action type is used to link the action with the object. The object can be Item, Window, and Block etc. In this case as we are applying the trigger on LOV so we will choose the Property type to be LOV.

 Action Type: Built-in:- Builtin are provided in both forms developer and Report developer which conatains many pl/sql constructs which we can reference while customizing or devloping forns or reports. These built-in packages are not installed as extensions to package STANDARD; therefore we must reference them with a construct of one of the package. In oracle forms personalization we can simply use them by invoking them and can add related argument to them. Depending upon the API type selected, the parameter should be entered. For Example: Bultin Type: FORMS_DDL Argument: ROLLBACK.

In the present case, we will create a Builtin Type: Create a Record Group from Query

 Create Record Group from Query It creates a record group dynamically through given Query and assigns this new Record Group to a list of values to limit data returned in the list.

To limit the values returned in the LOV, we will create a Builtin Action, for which we require to enter the query as argument and record group name. Refer to the figure below:

Builtin Type: Create Record Group from Query

Argument:

SELECT TP.TRANSACTION_TYPE_NAME "transaction_type_name" ,

TP.TRANSACTION_TYPE_ID "transaction_type_id" ,

TP.TRANSACTION_SOURCE_TYPE_ID "transaction_source_type_id" ,

TP.TRANSACTION_ACTION_ID "transaction_action_id" ,

decode(to_char(TP.type_class),'1','*', '2','', '') "project_related", Page 18 of 20

TP.DESCRIPTION "description" ,

SRC.DESCRIPTIVE_FLEX_CONTEXT_CODE "descriptive_flex_context_code",

SRC.TRANSACTION_SOURCE_TYPE_NAME "transaction_source_type_name",

Nvl(TP.TYPE_CLASS,2)

FROM MTL_TRANSACTION_TYPES TP,

MTL_TXN_SOURCE_TYPES SRC

WHERE TP.TRANSACTION_SOURCE_TYPE_ID = SRC.TRANSACTION_SOURCE_TYPE_ID

AND NVL(TP.DISABLE_DATE, SYSDATE+1) > SYSDATE

AND NVL(SRC.DISABLE_DATE, SYSDATE+1) > SYSDATE

AND NVL(TP.ATTRIBUTE10,'N') = 'Y'

AND (TP.transaction_source_type_id = 3 or TP.transaction_source_type_id = 6 or TP.transaction_source_type_id = 13 or TP.transaction_source_type_id >= 100)

AND (TP.transaction_action_id = 1 or TP.transaction_action_id = 27)

AND TP.transaction_type_id not in (66,67,68,95,96) order by TP.transaction_type_name

Group Name: XXDIS_MISC_TRX_GRP

Next, we will validate the argument by clicking on ‘Validate’ button.

Step IV: As the last step, we will create one more Action for associating the newly created Record Group to the required LOV.

An action called Property is required to refer to the LOV object. A Property action type requires Object Type, Target Object, Property Name and value.

We will select LOV as Object type, and give the LOV name as the Target Object which is ‘TRX_TYPE_MISC’ in this case.

As we need to associate the newly created Record Group with this LOV, we will select Record Group as Property Type and give the name of the created Record Group (i.e. XXDIS_MISC_TRX_GRP) as property value.

Object type: LOV

Target Object: TRX_TYPE_MISC

Property Type: Record Group

Value: XXDIS_MISC_TRX_GRP

Validating the Group Name: The Group name created in the Builtin Type, should exactly match with the Value given in the Property Type. We can validate the conformance of the value with Group Name by clicking the Validate Button present at the bottom of the form.  

On saving these personalization changes, the original Miscellaneous Transaction form will look as shown in the below screen shot. The Transaction type LOV has now been limited to show only the required value. To view these changes the user must close the existing form and reopen it.

Personalized Form: The personalized form will have only two values for transaction type as per the requirement. Hence the requirement is achieved.

                                                               Thank You !!!!.

                ** Please share your comments and inputs , there are many more to come **.

                                                                                                                

                     

      My LinkedIn                                                                                                 My Facebook