Form Personalization, Call a Function, And Return Value To Screen

In from personalization it is possible to call a database function that retrieves values based on other data entered on the screen by the user. The function then returns these values to a field on the screen. We can use a SELECT statement to call the function, put the data entered by user in some fields as required parameters to the function and finally assign the value returned by the function to a field in the form.
Before doing the exercise, please note the below points:
1] Every property that takes a string can either be processed literally or evaluated at runtime.
2] If you type a string in that does not start with ‘=’, then the exact value you type in will be used at runtime.
3] If the string you type starts with ‘=’, then the text immediately after that character will be evaluated at runtime. This allows you to write complex logic that can include references such as:

• SQL operators, such as ||, TO_CHAR, DECODE, and NVL
• Bind variables (:block.field), including: system: global and: parameter values. Use the ‘Add Item…’ button to assist with item names.
• Calls to server-side functions that do not have OUT parameters.

4] To use SELECT statement, you must follow these rules:

• The text must start with ‘=SELECT’
• The column being selected must evaluate to a CHAR, with a length no longer than 2000 bytes.
• Your SELECT statement should only return one row, but if more than one is returned only the value of the first row will be used.

Here is a simple example using a function in the database that queries values, then a personalization that sets the value queried. This example is defined in the miscellaneous transactions form (INVTTMTX.fmb) and passes the Project Information (Project Number, Task Number, Expenditure Type and Expenditure Org) to the function.

Oracle PL/SQL

=select xx_getaccount_pkg.main(''||${item.MTL_TRX_LINE.ITEM.value}||'', ''||${item.MTL_TRX_LINE.SOURCE_PROJECT_NUMBER.value}||'', ''||${item.MTL_TRX_LINE.SOURCE_TASK_NUMBER.value}||'', ''||${item.MTL_TRX_LINE.EXPENDITURE_TYPE.value}||'', ''||${item.MTL_TRX_LINE.EXPENDITURE_ORG.value}||'') from dual

1 2 3 4 5

=select xx_getaccount_pkg.main(''||${item.MTL_TRX_LINE.ITEM.value}||'', ''||${item.MTL_TRX_LINE.SOURCE_PROJECT_NUMBER.value}||'', ''||${item.MTL_TRX_LINE.SOURCE_TASK_NUMBER.value}||'', ''||${item.MTL_TRX_LINE.EXPENDITURE_TYPE.value}||'', ''||${item.MTL_TRX_LINE.EXPENDITURE_ORG.value}||'') from dual

Note that the variable in this case is written as “${item.MTL_TRX_LINE.ITEM.value}”. The text “MTL_TRX_LINE.ITEM.” is the Block and Field where the item number is written. The syntax around the block and field name ensures that the callout is made to replace the value of the item before passing the text to the function. You can use similar naming for your own fields finding the name of the Block and Field using Help > Diagnostics > Examine. Also don’t put semicolon (;) after the statement.
Open the form that you want to personalize, then choose Help > Diagnostics > Custom Code > Personalize. In this case, the miscellaneous transactions form is opened.

Enter the main information about when this personalization will be active.
Use the ‘Validate’ button to test if the syntax of your string is valid. If the evaluation fails, the processing engine will return an ORA error as if the string had been part of a SQL expression. Otherwise, it will display the text exactly as it would appear at runtime in the current context.
Enter the action information detailing what the personalization will do. Here select the ‘Property Name’ as VALUE and then put the above sql statement.

Save the changes and test the personalization.
In this case, the Project Information (Project Number, Task Number, Expenditure Type and Expenditure Org) is passed from the screen to the database function. The function then derives the GL Account from the above parameters. The value is then replaced for the Account field on the screen.