create or replace FUNCTION dm_convert (value_in IN VARCHAR2) 	RETURN DATE
/*
|| Summary: Validate and convert date input of most any format.
|| 		dm_convert stands for "date manager conversion". Accepts
||		a character string and returns a fully-parsed and validated
||		date. If the string does not specify a valid date, the function
||		returns NULL.
||
|| NOTE: if you get a parser stack overflow, then you need to 
||   cut down on the number of nested blocks! PL/SQL in Oracle
||   Forms seems to be able to handle more than PL/SQL V2 on
||   the server side!
*/
IS
	/* Internal, upper-cased version of date string */
	value_int CHAR(100) := UPPER (value_in);

	/* The value returned by the function */
	return_value DATE := NULL;

	/* Transfer SYSDATE to local variable to avoid repetitive calls */
	today DATE := SYSDATE;
BEGIN
	/*
	|| Handle the short-cut logic before checking for specific date 
formats.
	|| Supported short-cuts include:
	||		EW - end of week
	||		BW - beginning of week
	||		EM - end of month
	||		BM - beginning of month
	||
	|| You might want to add shortcuts for quarters specific to your site.
	*/
	IF value_int = 'EW'
	THEN 
		/* End of week in this case is Friday of the week */
		return_value := TRUNC (today, 'DAY') + 5;
	
	ELSIF value_int = 'BW'
	THEN
		/* Beginning of week in this case is Monday of the week */
		return_value := TRUNC (today, 'DAY') + 1;
	
	ELSIF value_int = 'BM' 
	THEN
	 	return_value := TRUNC (today, 'MONTH');
	
	ELSIF value_int = 'EM'
	THEN
		return_value := LAST_DAY (today);
	
	ELSIF value_int IS NOT NULL
	THEN
		/* No known short-cut. The user must have entered a date string for
		|| conversion. Now attempt to convert the value using a sequence
		|| of calls to TO_DATE. If one attempt fails, pass it to the next
		|| TO_DATE and format mask within a (very) nested exception section. 
		*/
		BEGIN 	return_value := TO_DATE (value_int, 'DD');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MM/DD');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MM/DD/YY');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MM/DD/YYYY');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'DD-MON');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'DD-MON-YY');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'DD-MON-YYYY');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MON');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MON-DD');
		EXCEPTION WHEN OTHERS THEN
		BEGIN return_value := TO_DATE (value_int, 'MON-DD-YY');
		--EXCEPTION WHEN OTHERS THEN
		--BEGIN return_value := TO_DATE (value_int, 'MON-DD-YYYY');
		--EXCEPTION WHEN OTHERS THEN
		--BEGIN return_value := TO_DATE (value_int, 'MON-YYYY');
		--EXCEPTION WHEN OTHERS THEN return_value := NULL;
		END; END; END; END; END; END; 
		END; END; --END; --END; --END; END;
	END IF;

	/* Whether NULL or a real date, return the value */
	RETURN (return_value);

END;
/