Wednesday, December 1, 2010
For a long time, I am not able to understand the reason for the occurrence of the above error while using DATE parameters in concurrent programs. Finally, I found the exact reason behind it and thought to share here. Let me explain reason first the cause and then the solution…
The recent releases of Oracle applications recommend the usage of the data type “FND_STANDARD_DATE” instead of the “DATE” data type. Even if you choose the “DATE” data type, it won’t accept. Hence, while registering the concurrent program parameters we choose “FND_STANDARD_DATE” as the data type for date values. The format mask for the FND_STANDARD_DATE data type is “YYYY/MM/DD HH24:MI:SS”. As a result, PLSQL procedure will receive a value like ‘2010/12/01 12:00:00’ as the date. Generally, in ORACLE database the “DATE” data type will have the format mask like “DD-MON-YYYY HH24:MI:SS” and this mismatch in the format type is the reason for the error.
“In Simple words, Because of the usage of the data type “FND_STANDARD_DATE”, Oracle Applications send the date value in the format “YYYY/MM/DD HH24:MI:SS” and Oracle database expects the date value in the format “DD-MON-YYYY HH24:MI:SS” and this is the cause of the issue”.
Follow the below steps to resolve the issue,
1. Define the Date parameters with datatype VARCHAR2 in procedure
2. Use FND_DATE.CANONICAL_TO_DATE to convert varchar2 format to oracle date format (DD-MON-YY) and then use it anywhere in a program
Below is an example for the same
CREATE OR REPLACE PROCEDURE APPS.MY_REPORT
( p_errbuf OUT VARCHAR2
,p_retcode OUT VARCHAR2
,p_from_date IN VARCHAR2
,p_to_date IN VARCHAR2 )
v_from_date := FND_DATE.CANONICAL_TO_DATE (p_from_date);
v_to_date := FND_DATE.CANONICAL_TO_DATE (p_to_date);
/* Remaining Logic can use the variables v_from_date and v_to_date */
Do you think this Article is useful?
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.