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.

How To Add Export Button In The Buyer Work Center For Requisitions?

The following personalization can be considered. 

1. Using System Administrator - Navigate - Profile - System 
 - Ensure the profile - Personalize Self Service Defn - is set to yes at the User level. 





2. Purchasing Super User - Navigate - Buyer Work Center - Requisitions



Choose the Personalize Page icon in the upper right hand corner. 






Look for the Line having Row Layout: (ActionRN) - scroll to the right and choose create item
- Choose the Expand All option - seen in the top left corner of the table - to ensure all elements are visible




For ID and Additional Text, you can enter desired value, example ExportReq.
 - Be sure to choose the Item Style as Export Button
 - The True and False decision boxes are fine as defaulted
 - Ensure Rendered decision box is True
 - For the Text box, give the desired button name
 - For view Instance - give ReqSearchVO
 - Choose Apply
 - You are returned to the personalize page screen previously seen above
 - Choose the Return to Application and you are returned to the Buyer Work Center



The button is now present and will export data in the current view that is selected. 

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.

How To Avoid The Error "java.lang.NullPointerException at oracle.apps.ap.oie.server.ExpensesAMImpl.isRejected" When Creating Expense Report

GOAL : How does one avoid the error below when trying to create an expense report?

SOLUTION : 
Ensure that there is the following:

- At least one Expense Report Template enabled for Internet Expenses with at least one Expense Item defined.
- ICX_HR_PERSON_ID is defined for the User.
- MO: Operating Unit profile option is set to appropriate value at the Expenses Responsibility level.

AP/AR Netting: Is there an API to Create an AP/AR Netting Batch ?

There is NO public Api's to create an AP/AR Netting Batch.

There are however some Receipt APIs that you may find useful:

AR_RECEIPT_API_PUB.apply_open_receipt - Creates a receipt-to-receipt application (payment netting).
AR_RECEIPT_API_PUB.unapply_open_receipt - Unapplies a receipt-to-receipt application.

SLA Data Flow and Table Links

GOAL : How does the data flow from Inventory (Material transactions coming from Inventory,WIP, and Receiving) to General Ledger(GL)? 

What are the tables that are involved?

SOLUTION : 

Important columns affected:

After a transaction is performed in forms:
mtl_material_transactions.costed_flag = 'N'

After the Cost Manager Picks up the data and processes it:
mtl_material_transactions.costed_flag is Null
xla_events.event_status_code = 'U'
xla_events.process_status_code = 'U'

After the Create Accounting - Cost Management is run:
xla_events.event_status_code = 'P'
xla_events.process_status_code = 'P'
xla_ae_headers.gl_transfer_status_code = 'N'
xla_ae_headers.gl_transfer_date is Null

After the Transfer To GL is run:
xla_ae_headers.gl_transfer_status_code = 'Y'
xla_ae_headers.gl_transfer_date is Not Null

Fore more information see : SLA Data Flow and Table Links (Doc ID 802966.1)

Oracle E-Business Suite APPS, SYSADMIN, and oracle Securing Generic Privileged Accounts

this document describe the Oracle E-Business Suite APPS, SYSADMIN, and oracle Securing Generic Privileged Accounts

for more details see :integrigy.com/oracle-security-blog
www.integrigy.com