Oracle canonical formats for flexfields

Flexfields are varchar2 datatype, but will handle number and dates.

So to store a char value – no action is required – but for dates and numbers, you must use the canonical functions:

To store a number into a flexfield use:

attribute1 := fnd_number.number_to_canonical(p_number);

To get a number from a flexfield use

p_number := fnd_number.canonical_to_number(attribute1)

To store a date into a flexfield use:

attribute1 := fnd_date.date_to_canonical(p_date);

To get a date from a flexfield use

p_date := fnd_date.canonical_to_date(attribute1);

Here is some examples:

DEV> select fnd_date.date_to_canonical(sysdate) from dual;
2010/02/11 16:42:18
DEV>

DEV> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh24:mi:ss';
DEV> 
DEV> select fnd_date.canonical_to_date('2010/02/11 16:42:18') d from dual;
11-feb-2010 16:42:18
DEV>

DEV> col n format 999g999d99

DEV> alter session set NLS_NUMERIC_CHARACTERS = ',.';
DEV> select fnd_number.canonical_to_number('3333.33') n from dual;
   3.333,33
DEV> 
DEV> alter session set NLS_NUMERIC_CHARACTERS = '.,';
DEV> select fnd_number.canonical_to_number('3333.33') n from dual;
   3,333.33
DEV>