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.

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.

Forms Personalization : How to Add a Web Link to the APPS Forms Menu

GOAL :  The purpose of this sample code is to add a Web link in the forms menu using Forms Personalization.

SOLUTION : 
Forms personalization feature may be used to add a new field in the menus of the forms in the e-business suite 11i. Below is the Forms Personalization code which will add a Web Link in the Tools menu :

1) Login to your responsibility.2) Go to the Forms you need to personalize.3) Open personalizations forms :  Help/Diagnostics/Custom Code/personalize.4) Enter sequence as '1' and in description enter 'Init Web URL menu'.5) In condition tab, enter trigger event as 'WHEN-NEW-FORM-INSTANCE'.6) Click on 'Actions' tab. Enter sequence as '1' and Type = 'Menu'.7) Choose Menu Entry (any of the special menu entries). Eg. Special 12.6) Enter menu label ( eg: Web URL ).8) In 'Enabled in Blocks' field you can choose for which blocks the special menu needs to be enabled or leave blank to be enabled for all the blocks of the forms.9) Go back to the main block where you entered 'Init Web URL menu'.10) Enter a new record with sequence as '2' and in description enter 'Launch URL'.11) In Conditions tab, enter trigger event as 'SPECIAL12'12) Click on Actions tab. Enter sequence as '1' and Type = 'Builtin'.13) Choose 'Launch a URL' for Builtin Type.13) Enter the argument as your URL ( eg : www.oracle.com ) and click 'Apply now' button.14) Save.15) Exit form , logout , login and go to your Forms again.16) Go to Tools menu. The new special menu ( Web URL ) entry will be displayed.17) Click to launch URL.

Form Personalization : How to make security in PO Forms using DEFAULT WHERE

Property Object Type - Block  
Target Object - The block from
Property Name - DEFAULT_WHERE 
Value :  PO_HEADER_ID in (1,2,3)