TYPE 1************* POPULATION***************
==========================================

 CREATE OR REPLACE PROCEDURE TYPE1
  AS
V_DEPTNO NUMBER;
V_DNAME VARCHAR2(20);
 V_LOC VARCHAR2(20);
 V1_DEPTNO NUMBER;
 V1_DNAME VARCHAR2(20);
 V1_LOC VARCHAR2(20);
 V_COUNT NUMBER;

 CURSOR C1 IS SELECT * FROM DEPT_TYPE1;

 BEGIN

 SELECT COUNT(*) INTO V_COUNT FROM DEPT_TYPE1_POP;
	 IF (V_COUNT=0) THEN
 		INSERT INTO DEPT_TYPE1_POP SELECT * FROM DEPT_TYPE1;
	 ELSE
 	OPEN C1;
	 LOOP
 		FETCH C1 INTO V_DEPTNO,V_DNAME,V_LOC;
 		EXIT WHEN C1%NOTFOUND;

		SELECT DEPTNO,DNAME,LOC INTO V1_DEPTNO,V1_DNAME,V1_LOC 
		FROM DEPT_TYPE1_POP
		WHERE DEPTNO=V_DEPTNO;

		IF (V_DEPTNO <> V1_DEPTNO OR V_DNAME <> V1_DNAME OR V_LOC <> V1_LOC )
		THEN
		UPDATE DEPT_TYPE1_POP SET DEPTNO=V_DEPTNO,
		DNAME=V_DNAME,LOC=V_LOC 
		WHERE DEPTNO=V_DEPTNO;
		END IF;
	END LOOP;
	CLOSE C1;
	END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN
		INSERT INTO DEPT_TYPE1_POP SELECT * FROM DEPT_TYPE1 
		WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT_TYPE1_POP);
END;

TYPE 2************* POPULATION***************
==========================================

CREATE OR REPLACE PROCEDURE TYPE2
AS
V_DEPTNO NUMBER;
V_DNAME VARCHAR2(20);
V_LOC VARCHAR2(20);
V1_DEPTNO NUMBER;
V1_DNAME VARCHAR2(20);
V1_LOC VARCHAR2(20);
V1_TYPE NUMBER;
V_COUNT NUMBER;

CURSOR C1 IS SELECT * FROM DEPT_TYPE1;

BEGIN

	SELECT COUNT(*) INTO V_COUNT FROM DEPT_TYPE2_POP;
	IF V_COUNT=0 THEN
		INSERT INTO DEPT_TYPE2_POP SELECT  SEQ_TYPE2.NEXTVAL,DEPTNO,DNAME,LOC  		FROM DEPT_TYPE1;
	ELSE
	OPEN C1;
	LOOP
		FETCH C1 INTO V_DEPTNO,V_DNAME,V_LOC;
		EXIT WHEN C1%NOTFOUND;

		SELECT TYPEID,DEPTNO,DNAME,LOC INTO 		V1_TYPE,V1_DEPTNO,V1_DNAME,V1_LOC 
		FROM DEPT_TYPE2_POP
		WHERE DEPTNO=V_DEPTNO
		AND TYPEID IN 
			(SELECT MAX(TYPEID) FROM DEPT_TYPE2_POP GROUP BY DEPTNO);

		IF  ( V_DEPTNO <> V1_DEPTNO OR V_DNAME <> V1_DNAME OR V_LOC <> V1_LOC )
		THEN
		INSERT INTO DEPT_TYPE2_POP 		VALUES(SEQ_TYPE2.NEXTVAL,V_DEPTNO,V_DNAME,V_LOC);
		END IF;
	END LOOP;
	CLOSE C1;
	END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

	INSERT INTO DEPT_TYPE2_POP SELECT SEQ_TYPE2.NEXTVAL,DEPTNO,DNAME,LOC 
	FROM DEPT_TYPE1
	WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT_TYPE2_POP);
 END;

TYPE 3************* POPULATION***************
==========================================

 CREATE OR REPLACE PROCEDURE TYPE3
 AS
 V_DEPTNO NUMBER;
 V_DNAME DEPT_TYPE1.DNAME%TYPE;
 V_LOC DEPT_TYPE1.LOC%TYPE;
 V1_DEPTNO NUMBER;
 V1_DNAME VARCHAR(20);
 V1_LOC VARCHAR(20);
 V1_PREV_DNAME VARCHAR(20);
 V1_PREV_LOC VARCHAR(20);
 V_COUNT NUMBER;

 CURSOR C1 IS SELECT * FROM DEPT_TYPE1;

 BEGIN

	 SELECT COUNT(*) INTO V_COUNT FROM DEPT_TYPE3_POP;

	 IF V_COUNT=0 THEN
		 INSERT INTO DEPT_TYPE3_POP
		 SELECT DEPTNO,DNAME,LOC,NULL,NULL FROM DEPT_TYPE1;
 	ELSE
	OPEN C1;
 	LOOP

		FETCH C1 INTO V_DEPTNO,V_DNAME,V_LOC;
 		EXIT WHEN C1%NOTFOUND;

		SELECT DEPTNO,DNAME,LOC,PREV_DNAME,PREV_LOC
 		INTO
 		V1_DEPTNO,V1_DNAME,V1_LOC,V1_PREV_DNAME,V1_PREV_LOC 
		FROM DEPT_TYPE3_POP
	 	WHERE DEPTNO=V_DEPTNO;

			IF (V_DNAME <> V1_DNAME OR V_LOC <> V1_LOC) THEN
 			UPDATE DEPT_TYPE3_POP SET DNAME=V_DNAME,LOC=V_LOC,
 			PREV_DNAME=V1_DNAME,PREV_LOC=V1_LOC 
			WHERE DEPTNO=V_DEPTNO;
 			END IF;
 	END LOOP;
 	CLOSE C1;
	 END IF;

 EXCEPTION

 WHEN NO_DATA_FOUND THEN

 	INSERT INTO DEPT_TYPE3_POP SELECT DEPTNO,DNAME,LOC,NULL,NULL 
	FROM DEPT_TYPE1
 	WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT_TYPE3_POP);
 END;