[FUNCTION]GET_PROJ_END

CREATE OR REPLACE FUNCTION SAPHEE.GET_PROJ_END (IN_MANDT VARCHAR2, IN_POSID VARCHAR2 )

RETURN  VARCHAR2

IS
 RT VARCHAR(100) ;
   P_MANDT VARCHAR(72) DEFAULT '';
  P_POSID VARCHAR(72) DEFAULT '';
  P_WBS VARCHAR(72) DEFAULT '';

   P_LN INTEGER DEFAULT 0;
   P_LR INTEGER DEFAULT 0;

BEGIN

    P_MANDT := RTRIM(IN_MANDT);
    P_POSID := RTRIM(IN_POSID);
    P_LN := INSTR(P_POSID, '-P');
    P_LR := INSTR(P_POSID, '-R');

   IF (P_LR > 0 ) THEN RETURN '';
   END IF;
   IF (P_LN = 0) THEN P_WBS := P_POSID;
   ELSE  P_WBS := SUBSTR(P_POSID,1,P_LN-1);
   END IF;

    SELECT ( CASE WHEN TXT04 > '' THEN 'CODE : '||TXT04 ||'  사유: ' || TXT30 ELSE '' END ) INTO RT
    FROM SAPHEE.ZMASTER02
    WHERE MANDT = P_MANDT
    AND POSID = SUBSTR(P_WBS,1,6)
    AND POSID_1 = P_WBS
    AND SUBSTR(TXT04,1,1) <> 'I'
    AND RTRIM(TXT04) NOT IN ('P','R4','R5','R6');

    RETURN RT;
    END;
/