Setting Universal Date Mask for All Languages in 11i and R12

Once upon a time I was asked to see whether it was possible to set all the dates in a form of RRRR-MM-DD, regardless which NLS language was using.  The one who made this suggestion explained to me that this was the best format for data input (using least amount of keystrokes), and it has no language barrier in date value for all the stuff around the globe.  Since it was impossible to say "No" to this person's "suggestion" so I had to make it possible.

I was so embarrassed to create a TAR and ask Oracle how to do this configuration, and I didn't think Oracle would tell me how to do it ("it is not recommended and this configuration will void the support contract, blah, blah, blah, etc"), I used a couple of hours to figure out what's supposed to do.  And here are the steps:

(1) Change the database NLS setting to use this date mask

• SQL*Plus: create pfile from spfile
• Change file init[SID].ora: fine the line nls_date_format and set it to RRRR-MM-DD
• SQL*Plus: startup mount, create spfile from pfile, shutdown immediate, startup
• Make sure it is set: 
• select value from v$parameter where name='nls_date_format'

(2) Change values in FND_LANGUAGES table

• The character set of my instance is UTF8 so the column to be changed is UTF8_DATE_LANGUAGE.  For non-UTF8 instance, LOCAL_DATA_LANGUAGE column should be changed.
• For US language code, the original value should be American (i.e. 01-JAN-2003).  Issue an DML to update and change it to NUMERIC DATE LANGUAGE.  Set this value to other NLS languages if your instance supported these languages.

(3) Change profile option

• Change the site level profile option "ICX: Date format mask" to "1999-12-31"

These three settings should make the instance to use the date format og "RRRR-MM-DD" in both Form and OA Framework pages.

Other Issues:

(1) Unable to start Form with error APP-FND-000125

• If you run adconfig or apply some patches (which run adconfig at the end), the FND_LANGUAGES value is reset.  Perform step (2) and (3) again.
• DO NOT change the APPL_TOP context parameter FND_DATE_MASK (which is DD-MON-RR by default).  Remember we should not change settings to start Apache JServ or Form (11i) / opmn (R12).  For APPL_TOP, the date mask is changed in session level, rather than in client settings.

(2) Flexfield Values

If you have flexfield segment value using valueset FND_STANDARD_DATE and it has a default value (i.e. an attribute uses a date value in the Form as default), the original setting should look like:

• Default Type = Field
• Default Value = BLOCK.FIELD

After applying this universal date mask, this segment value should be:

• Default Type = SQL Statement
• Default Value = SELECT TO_CHAR(TO_DATE(:BLOCK.FIELD,'DD-MM-RRRR'), 'RRRR-MM-DD') FROM DUAL