Form Personalization.

For reader not yet familiar with Form Personalization, here is some summary information:

·  SECURING ACCESS AND TESTING
When using Form Personalization, it is recommended to take some precautions when implementing it.

1.   Only trusted users should have access to this feature. Use the system profiles: 'Hide Diagnostics menu entry' and 'Utilities:Diagnostics' to control the visibility of the menu and the direct access to the feature (user requires Apps password or not?);

2.   It is a good idea to test all Form Personalizations in a DEV or TEST environment before moving them to a Production environment. Remember that changes made could impact or interfere with the base code of a form;

3.   Follow diligently the Support statements communicated by Oracle.

·  OVERVIEW AND BASIC CONCEPTS
Form Personalization is a declarative feature that alter the behavior of Oracle Forms-based screens, including changing properties, executing built-ins, displaying messages, and adding menu entries.

o   RULES
For each function, you can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

An Event is a trigger point within a form, such as a startup (WHEN-NEW-FORM-INSTANCE), or a new record (WHEN-NEW-RECORD-INSTANCE). Oracle forms sends standard and product-specific events.

A Condition is an optional SQL code fragment that is evaluated when an Event occurs. If the Condition evaluates to TRUE then the Actions are processed.

A Scope is evaluated based on the current runtime context to determine if a Rule should be processed or not. The Scope can be Site, Responsibility or User. Each Rule can have one or more Scopes associated with it. When defining new Rules in your DEV or TEST environment, I recommend setting the Scope to your User first just to be safe. If all is well with your personalization, then proceed in setting the Scope to it's intended level. If a Rule has a context of "Site", it will apply for everyone. A Rule with a context of Responsibility doesn't override Site. A Rule with a context of User doesn't override Site or Responsibility.

o   ACTIONS
Each Action consists of 'Setting a property', such as making a required field or hiding a Tab page, 'Executing a Built-in', such as GO_BLOCK, DO_KEY or END_FUNCTION.EXECUTE, 'Displaying a Message' or 'Enabling a Special menu entry', such as a zoom.Once Rules are defined, save and close the form and re-run the function (the form). Then the Rules are automatically applied as Events occur within that form.

·  EXAMPLE OF A BASIC FORM PERSONALIZATION


For this example, we will define a basic Forms Personalization by hiding the Person, Customer and Fax fields of the User form. Follow the following steps.

Responsibility: System Administrator

1 - Ensure to set system profile 'Hide Diagnostics menu entry' to 'No' and 'Utilities:Diagnostics' to 'Yes' at the user level.
2 - Open the Users form. (N) Security > User > Define.
3 - This is the basic Users form as shown here:

4 - From the menu, select (M) Help > Diagnostics > Custom Code > Personalize.

5 - If some Rules are already defined in the Users form, you will see them this window (as in this example).      6 - Add a new Rule by selecting (M) File > New. 7 - This is the Form Personalisation form and it's Rules already defined. We will be adding seq. 40.

A - Rules Seq.: 40
B - Description: Hide Person, Customer and Fax fields
C - Trigger Event: WHEN-NEW-FORM-INSTANCE
D - Context or Scope: Set at User level and enter your user in the Value field
E - Select the Actions tab. This is the Actions tab of Rule seq. 40

F - Action Seq.: 1
G - Type: Property
H - Object Type: Item
I - Select the Select By Text button

J - Select the Person (Users) item from the LOV
K - Property Name: DISPLAYED
L - Value: False
M - Save your work
N - When you are done, it should look like this:

O - Repeat Steps F to L for the Customer and Fax items. Save all of your work. It should now look like this:

P - Close the Form Personalization form, close the User form and reopen it. Your User form should now look like this: Notice that the User, Customer and Fax fields are no longer displayed.

Other basic Forms Personalization are just as easy as this example, such as changing prompts, concealing data or hiding tabs.

On my next entry, I will try to cover an intermediate Form Personalization as an example. Elements such as triggers, conditions, sequences, contexts, string evaluation, multi-lingual issues, common actions and debugging will be covered.

----------------------------------------------------------------------------------------------------

REFERENCES:

Customization and Development for the E-Business Suite

Form Personalization in Oracle Applications User Guide

Form Personalization System Administrator's Guide

***********************************************************************************************************

Oracle/PLSQL: To_Char Function

---

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9')	would return '1210.7'
to_char(1210.73, '9,999.99')	would return '1,210.73'
to_char(1210.73, '$9,999.00')	would return '$1,210.73'
to_char(21, '000099')	would return '000021'

Examples - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions

---

Question:  Why doesn't this sort the day's of the week in order?

select ename, hiredate, to_char((hiredate),'fmDay') "Day"
from emp
order by "Day";

Answer:

The fmDay parameter will return the name of the Day and not the numeric value of the day.
Try the following:

select ename, hiredate, to_char((hiredate),'fmDD') "Day"
from emp
order by "Day";