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

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.