Value Sets - Special Value Sets -

One of the frequent issues faced mostly in SQL/PLSQL is based on Date Formats.

Following is one of the code snippet to validate From Date/ To Date  with users using Different Date Formats.


FND PLSQL " DECLARE

l_char  varchar2(25) := :!value ;
l_count NUMBER := 0;

   l_value   DATE :=  TO_DATE(l_char,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'NLS_DATE_LANGUAGE = AMERICAN') ;
 
  BEGIN
  SELECT trunc(l_value - to_date(':$FLEX$.XX_FROM_DATE','YYYY/MM/DD HH24:MI:SS'))
  INTO l_count
  FROM dual;

  IF l_count  >  365

   THEN
fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE') ;
fnd_message.set_token( 'MESSAGE', 'Date Difference Should Not Be Greater Than 365 Days' );
fnd_message.raise_error ;
   ELSIF  l_count <1    THEN
     fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE') ;
     fnd_message.set_token( 'MESSAGE', 'To Date Should Be Greater Than From Date');
     fnd_message.raise_error ;
   END IF;

END;"