How To Use Custom PL/SQL API In Forms Personalization?

GOAL : 

How to use custom developed PL/SQL procedure/ Function in Forms Personalization?

Here is an example to use custom PL/SQL API in forms personalization.
User can actually do lot of personalization using custom procedure/ function in personalization rule and logical validation based on the return value from the custom API.

In this simple example our goal is to populate the user description field based on the user name entered in the User define form. A custom API is there which will return the user group based on the user name and based on the user group the description will be populated.

SOLUTION : 

I. Create a database function as follows:
   
    create or replace function test_func(param IN varchar2) return varchar2 is
      ret_value varchar2(10);
    begin
      if param = '999' then
      ret_value := 'TEST1';
      else
      ret_value := 'TEST2';
      end if;
      return(ret_value);
    end test_func;

II. Navigate to System Administrator > security > User > Define.
III. Open the Personalization form from the pull down menu Help > Diagnostics > Custom Code
    > Personalize.

IV. Now implement any of the following personalization rule.

A] if the number of logical expression based on the database function test_func is more than 3 or so
   
    1. Create a new database function where all the logical expression based on the function test_func
    will be considered and will return the end result string as follows:
   
    create or replace function test_CallFunc(param IN varchar2) return varchar2 is
      ret_value varchar2(50);
    begin
      if test_func(param) = 'TEST1' then
      ret_value := 'Special User';
      else
      ret_value := 'Normal User';
      end if;
      return(ret_value);
    end test_CallFunc;
   
    2. Navigate to System Administrator > security > User > Define
    3. Open the Personalization form from the pull down menu Help => Diagnostics => Custom Code
    => Personalize.
   
    4. Implement the following personalization rule:
   
      Seq: 10
      Description: Assign Description using property and a calling function
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: <Blank>
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: =test_CallFunc(:USER.USER_NAME)

   
B] If the number of logical expression based on the database function test_func is less then 3 or
    so. Actually in this method for each logical expression based on the function test_func, you need to
    create a new personalization rule as below:
   
      Seq: 10
      Description: Assign Description using property and logical condition true
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)='TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Special User
   
      Seq: 20
      Description: Assign Description using property and logical condition false
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)<>'TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Normal User
   
V. Save the personalization.