For any Concurrent Program date parameter in Oracle Apps, we assign FND_STANDARD_DATE Value Set having length 11. This value set always pass date in below
format to your program
YYYY/MM/DD
If the date format of your program (Report or PL/SQL Procedure) does not matches with above format, it will always throw below error in log file
ORA-01861: literal does not match format string
Below are the steps to resolve this error with different Program Type
Oracle Reports Type Program
Define user parameter for Date in RDF with below property details
Datatype- Date
Width- 20
Input Mask- RRRR/MM/DD HH24:MI:SS
PL/SQL Stored Procedure Type Program
1) Always use errbuf and retcode as first two OUT parameters in your procedure
2) Define the Date parameters with datatype VARCHAR2 in procedure
3) 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.TEST_TRANSFER
( p_errbuf OUT VARCHAR2
,p_retcode OUT VARCHAR2
,p_from_date VARCHAR2
,p_to_date VARCHAR2 )
IS
v_from_date DATE;
v_to_date DATE;
BEGIN
v_from_date := fnd_date.canonical_to_date (p_from_date);
v_to_date := fnd_date.canonical_to_date (p_to_date);
/
No comments:
Post a Comment