/****************************************************************************/
/* SYSTEM_ID : GE                                                           */
/* WARN ( FUNCTION )                                                        */
/* Created : 2014/12/13 13:30:48                                            */
/****************************************************************************/
CREATE FUNCTION WARN 
FUNCTION WARN(
	XDYSTART   IN VARCHAR2 ,
	XDURATION  IN NUMBER   ,
	XCOMPLETE  IN NUMBER  )
RETURN VARCHAR2
IS
    RTN     VARCHAR2(1);
	COMP	NUMBER(5);

BEGIN
	COMP := TO_DATE( SUBSTRB(XDYSTART,1,8),'YYYYMMDD' ) - TRUNC(SYSDATE,'DD') ;

	IF COMP > 0 OR XCOMPLETE = 100  THEN
		RTN := '0';
	ELSIF ( COMP + XDURATION ) >= 0 AND XCOMPLETE < 100 THEN
		RTN := '1';
	ELSIF ( COMP < 0 AND XCOMPLETE = 0 ) OR
		   (( COMP + XDURATION ) < 0 AND XCOMPLETE < 100 ) THEN
		RTN := '2';
	END IF ;

	RETURN RTN;

END WARN;
/
