Require comprehensive test cases for SCD Type2

Hi, I have written the following code in Oracle for SCD Type2. SCD2 table name is employee_tgt and source table is employee_src. It maintains history of column emp_name. The primary key is emp_id and the surrogate key is emp_sk. The load is assumed to run daily.

I wish to use it as template for any dimension of Type2. However, I require someone to provide me with a comprehensive test data set so that I be sure that the code is correct.

Code:


CREATE SEQUENCE seq_employee_tgt MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE TABLE employee_tgt(
emp_sk NUMBER NOT NULL,
emp_id NUMBER not null,
emp_name VARCHAR2(10) not null,
active_flag CHAR(1),
start_date DATE DEFAULT SYSDATE not null,
end_date DATE not null
);

ALTER TABLE employee_tgt ADD CONSTRAINT PK_employee_tgt PRIMARY KEY (emp_sk);
ALTER TABLE employee_tgt ADD CONSTRAINT UK_employee_tgt UNIQUE (emp_id, start_date);

CREATE TABLE employee_src(
emp_id NUMBER not null,
emp_name VARCHAR2(10) not null,
record_date DATE
);

ALTER TABLE employee_src ADD CONSTRAINT PK_employee_src PRIMARY KEY (emp_id,record_date);

INSERT INTO employee_src (emp_id, emp_name, record_date) VALUES(100,'AKS','31-DEC-2009');
INSERT INTO employee_src (emp_id, emp_name, record_date) VALUES(101,'Singh','31-DEC-2009');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(99,'Kumar','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhijit','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Singh','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(102,'Abhishek','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhijit','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Si.','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(104,'Sharma','3-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhi.','3-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Singh','3-JAN-2010');

SELECT * FROM employee_src;

SELECT * FROM employee_tgt;


CREATE OR REPLACE PROCEDURE sp_emp_scd2 (p_in_run_date IN DATE, p_out_retc_cd OUT NUMBER)
AS
BEGIN
--insert new employees and employees whose name has changed
INSERT INTO employee_tgt (emp_sk,emp_id, emp_name, active_flag, start_date, end_date)
SELECT seq_employee_tgt.nextval, emp_id, emp_name, 'Y', p_in_run_date, '9-SEP-9999'
FROM employee_src SS
WHERE ss.record_date=p_in_run_date
  AND (EXISTS (SELECT 1 FROM employee_tgt ST
                WHERE ST.emp_id=SS.emp_id
                      AND (ST.emp_name<>SS.emp_name /*OR any other field whose history is to be maintained*/)
                    AND st.active_flag='Y'
                    )
        OR NOT EXISTS (SELECT 1 FROM employee_tgt ST WHERE ST.emp_id=SS.emp_id)
      );

--old row of employees whose name has changed should be disabled
UPDATE employee_tgt ST SET active_flag = 'N', end_date = p_in_run_date - 1
WHERE EXISTS
(SELECT 1
FROM employee_src SS
WHERE ST.emp_id=SS.emp_id
AND (ST.emp_name<>SS.emp_name)
AND ss.record_date=p_in_run_date)
AND st.active_flag='Y';

COMMIT;
p_out_retc_cd := 0;

EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  p_out_retc_cd := 1;
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;