ORA-01861: literal does not match format string

Cause:  FDPSTP failed due to ORA-01861: literal does not match format string
ORA-06512: at line 1

Starting with Oracle Application Version 11.5.10.2, Oracle does not allow you to use FND_DATE4 parameters and instead recommends FND_STANDARD_DATE data type. Thus, concurrent program using date parameters would be assigned value set FND_STANDARD_DATE.

The point to be noted here is, format mask for the FND_STANDARD_DATE data type is "YYYY/MM/DD HH24:MI:SS". However, in most of the date format in Oracle Database will have format mask "DD-MON-YYYY HH24:MI:SS". Because of difference in above two format masks, you get error "ORA-01861: literal does not match format string".

Following is the workaround to avoid such errors:
1. Define the date parameter as VARCHAR2 in Oracle Procedure.
2. Register Date Parameter in Concurrent Program with Value Set as "FND_STANDARD_DATE".
3. Use FND_DATE.CANONICAL_TO_DATE function to convert varchar2 value to oracle date format & then use this value in your oracle procedure.

Example:


CREATE PROCEDURE XX_TEMP (errbuf      OUT  VARCHAR2
                         ,retcode     OUT  VARCHAR2
                         ,i_from_date  IN  VARCHAR2
                         ,i_to_date    IN  VARCHAR2
                         )
IS
   ld_from_date DATE := FND_DATE.CANONICAL_TO_DATE (i_from_date);
   ld_to_date   DATE := FND_DATE.CANONICAL_TO_DATE (i_to_date);

   /* Use  ld_from_date and ld_to_date instead of i_from_date and i_to_date in procedure */
END XX_TEMP;
/