CREATE OR REPLACE PACKAGE APPROVED_PCK
IS
	FUNCTION NEXT_APPROVER
	(
		P_cUSER_ID		IN	VARCHAR2,
		P_cAPPROVE_USER_ID	IN	VARCHAR2 := NULL
	) RETURN VARCHAR2;
		
	FUNCTION SEPERATOR
	(
		P_cSTRING	IN	VARCHAR2,
		P_SEPERATOR	IN	VARCHAR2
	) RETURN T1;
	
	/*PROCEDURE MAIL
	(
		P_cERROR	OUT	VARCHAR2
	);*/
END APPROVED_PCK;
/
CREATE OR REPLACE PACKAGE BODY APPROVED_PCK
IS
	FUNCTION SEPERATOR
	(
		P_cSTRING	IN	VARCHAR2,
		P_SEPERATOR	IN	VARCHAR2
	) RETURN T1
	IS
		V_cSTRING 	VARCHAR2(32000);
		V_arrTEST	T1;
		V_cTEMP		VARCHAR2(100);
		V_nCNT		NUMBER := 0;
		
	BEGIN
		V_arrTEST := T1();
		V_cSTRING := P_cSTRING||P_SEPERATOR;
		IF LENGTH(P_SEPERATOR) > 1
		THEN
			V_nCNT  := LENGTH(P_SEPERATOR) - 1;
		END IF;
		WHILE(INSTR(V_cSTRING,P_SEPERATOR) > 0)
		LOOP
				   
			V_cTEMP := SUBSTR(V_cSTRING,1,INSTR(V_cSTRING,P_SEPERATOR)+V_nCNT - LENGTH(P_SEPERATOR));
			--V_cTEMP := SUBSTR(V_cSTRING,1,INSTR(V_cSTRING,P_SEPERATOR) - LENGTH(P_SEPERATOR));
			V_cSTRING:=SUBSTR(V_cSTRING,INSTR(V_cSTRING,P_SEPERATOR) + LENGTH(P_SEPERATOR),LENGTH(V_cSTRING));
			V_arrTEST.EXTEND;
			V_arrTEST(V_arrTEST.COUNT) := V_cTEMP;
		END LOOP;
	RETURN V_arrTEST;
	END;
	
	FUNCTION NEXT_APPROVER
	(
		P_cUSER_ID		IN	VARCHAR2,
		P_cAPPROVE_USER_ID	IN	VARCHAR2 := NULL
	) RETURN VARCHAR2
	IS
		V_cTEMP		VARCHAR2(32000) := NULL;
		V_nFLAG		NUMBER := 0;
		V_cTEMP1	VARCHAR2(32000);
		V_cTEMP2	VARCHAR2(32000);
		V_cTEMP3	VARCHAR2(32000);
		V_cTEMP4	VARCHAR2(32000);
		V_cTEMP5	VARCHAR2(32000);
		V_cORG_VAL	VARCHAR2(32000) := P_cUSER_ID;
		V_arrTEST	T1;
		V_arrTEST1	T1;
	BEGIN
		
		V_arrTEST := T1();
			
		
		IF P_cAPPROVE_USER_ID IS NULL THEN
			V_cTEMP1 := V_cORG_VAL||',';
			SELECT SUBSTR(V_cORG_VAL,1,INSTR(V_cORG_VAL,',')-1) INTO V_cTEMP FROM DUAL;
		ELSE
			V_arrTEST := SEPERATOR(V_cORG_VAL,',');
			
			FOR I IN 1..V_arrTEST.COUNT
			LOOP
				--dbms_output.put_line('1-'||V_arrTEST(I));
				IF INSTR(V_arrTEST(I),'&&') > 0 THEN
					V_arrTEST1 := T1();
					V_arrTEST1 := SEPERATOR(V_arrTEST(I),'&&');
					FOR J IN 1..V_arrTEST1.COUNT
					LOOP
						--dbms_output.put_line('2-'||V_arrTEST1(J));
						IF INSTR(V_arrTEST1(J),P_cAPPROVE_USER_ID) > 0  THEN
							--dbms_output.put_line('21-'||V_arrTEST1(J));
							IF (J + 1) < V_arrTEST1.COUNT THEN 
								V_cTEMP := V_arrTEST1(J+1);
								--dbms_output.put_line('3-'||V_cTEMP);
								EXIT;
							ELSE
								V_cTEMP := V_cTEMP||V_arrTEST1(J);
								V_cTEMP:=REPLACE(V_cTEMP,P_cAPPROVE_USER_ID,'');
								--dbms_output.put_line('31-'||V_cTEMP);
							END IF;
						ELSE
							V_cTEMP :=V_cTEMP||'&&'||V_arrTEST1(J);
						END IF;
					END LOOP;				
				END IF;
							
			END LOOP;
		END IF;
		V_cTEMP := REPLACE(V_cTEMP,')','');
		V_cTEMP := REPLACE(V_cTEMP,'(','');
		
		dbms_output.put_line('FINAL-'||V_cTEMP);
		RETURN TRIM(V_cTEMP);
		
	END NEXT_APPROVER;
	
	/*PROCEDURE MAIL
	(
			P_cERROR	OUT	VARCHAR2
	)
	IS
	BEGIN
		UTL_MAIL.send( sender => dolly_ash@rediffmail.com, 
		recipients => ashishmcajec@rediffmail, 
		subject => 'subject',
		message => utl_raw.cast_to_raw (' text with central europe signs like šcr ')|| ReturnCountDay,
		priority => 3,
		mime_type => 'text/plain; charset=windows-1250'
		);
	END MAIL; */
END APPROVED_PCK;
/
SHOW ERROR;
--DROP PACKAGE APPROVED_PCK;