======================Package Specification==================================
CREATE OR REPLACE PACKAGE xx_emp_package IS
PROCEDURE log_message (
p_procedure VARCHAR2,
p_message VARCHAR2
);
PROCEDURE xx_validate;
PROCEDURE xx_insert_valid_emp;
PROCEDURE xx_insert_in_valid_emp;
PROCEDURE xx_calling_prc;
END xx_emp_package;
=======================================================================
========================Package Body====================================
CREATE OR REPLACE PACKAGE BODY xx_emp_package IS
/*
Procedure To Print Messages
*/
PROCEDURE log_message (
p_procedure VARCHAR2,
p_message VARCHAR2
) IS
l_err_msg VARCHAR2(2000);
BEGIN
l_err_msg := '<<'
|| p_procedure
|| ' - '
|| p_message
|| '>>';
dbms_output.put_line('<<'
|| p_procedure
|| ' - '
|| p_message
|| '>>');
fnd_file.put_line(fnd_file.log, l_err_msg);
END log_message;
/*
Procedure To Validate Employee Record Columns
*/
PROCEDURE xx_validate IS
l_error_msg VARCHAR2(32000);
status VARCHAR2(20);
v_first_name VARCHAR2(200);
v_last_name VARCHAR2(200);
v_full_name VARCHAR2(200);
v_sex VARCHAR2(200);
v_party_id VARCHAR2(200);
v_title VARCHAR2(200);
CURSOR c1 IS
SELECT
*
FROM
xx_temp_t
WHERE
nvl(status, 'N') IN ( 'N', 'E' );
BEGIN
FOR i IN c1 LOOP
l_error_msg := NULL;
status := NULL;
--Validation For First Name
log_message('xx_validate', 'Validating First Name');
BEGIN
SELECT
first_name
INTO v_first_name
FROM
xx_papf
WHERE
person_id = i.person_id
AND first_name = i.firstname;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'First Name Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
--Validation For Last Name
log_message('xx_validate', 'Validating Last Name');
BEGIN
SELECT
last_name
INTO v_last_name
FROM
xx_papf
WHERE
person_id = i.person_id
AND last_name = i.lastname;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'Last Name Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
--Validation For Full Name
log_message('xx_validate', 'Validating Full Name');
BEGIN
SELECT
full_name
INTO v_full_name
FROM
xx_papf
WHERE
person_id = i.person_id
AND full_name = i.fullname;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'Full Name Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
--Validation For Sex
log_message('xx_validate', 'Validating Sex');
BEGIN
SELECT
sex
INTO v_sex
FROM
xx_papf
WHERE
person_id = i.person_id
AND sex = i.sex;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'Sex Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
--Validation For Party Id
log_message('xx_validate', 'Validating Party Id');
BEGIN
SELECT
party_id
INTO v_party_id
FROM
xx_papf
WHERE
person_id = i.person_id
AND party_id = i.party_id;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'Party Id Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
--Validation For Title
log_message('xx_validate', 'Title');
BEGIN
SELECT
title
INTO v_title
FROM
xx_papf
WHERE
person_id = i.person_id
AND title = i.title;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg
|| ' ~ '
|| 'Title Is In-valid';
WHEN OTHERS THEN
l_error_msg := l_error_msg
|| ' ~ '
|| substr(sqlerrm, 1, 250);
END;
IF l_error_msg IS NULL THEN
UPDATE xx_temp_t
SET
error_msg = l_error_msg,
status = 'V'
WHERE
person_id = i.person_id;
dbms_output.put_line('Employee Valid');
log_message('xx_validate', 'Employee Valid:' || i.person_id);
ELSE
UPDATE xx_temp_t
SET
error_msg = l_error_msg,
status = 'E'
WHERE
person_id = i.person_id;
log_message('xx_validate', 'Employee In-Valid:' || i.person_id);
END IF;
END LOOP;
COMMIT;
END xx_validate;
/*
Procedure To Insert Valid Employees In Table
*/
PROCEDURE xx_insert_valid_emp IS
v_count NUMBER;
CURSOR c1 IS
SELECT
*
FROM
xx_temp_t
WHERE
status = 'V';
BEGIN
FOR i IN c1 LOOP
log_message('xx_insert', 'Started Inserting Valid Employees In Table');
INSERT INTO xx_valid_emp (
person_id,
empno,
firstname,
lastname,
fullname,
sex,
title,
party_id
) VALUES (
i.person_id,
i.empno,
i.firstname,
i.lastname,
i.fullname,
i.sex,
i.title,
i.party_id
);
log_message('xx_insert', 'END Inserting');
END LOOP;
BEGIN
SELECT
COUNT(*)
INTO v_count
FROM
xx_valid_emp;
END;
log_message('xx_insert', 'Total Count Of Valid Employee Records Inserted:' || v_count);
COMMIT;
END xx_insert_valid_emp;
/*
Procedure To Insert In-Valid Employees In Table
*/
PROCEDURE xx_insert_in_valid_emp IS
v_count NUMBER;
CURSOR c1 IS
SELECT
*
FROM
xx_temp_t
WHERE
status = 'E';
BEGIN
FOR i IN c1 LOOP
log_message('xx_insert_in_valid_emp', 'Started Inserting In-Valid Employees In Table');
INSERT INTO XX_IN_VALID_EMP (
person_id,
empno,
firstname,
lastname,
fullname,
sex,
title,
party_id,
status,
error_msg
) VALUES (
i.person_id,
i.empno,
i.firstname,
i.lastname,
i.fullname,
i.sex,
i.title,
i.party_id,
i.status,
i.error_msg
);
log_message('xx_insert_in_valid_emp', 'END Inserting');
END LOOP;
BEGIN
SELECT
COUNT(*)
INTO v_count
FROM
xx_valid_emp;
END;
log_message('xx_insert_in_valid_emp', 'Total Count Of In-Valid Employee Records Inserted:' || v_count);
COMMIT;
END xx_insert_in_valid_emp;
/*
Procedure Call The Above Procedures In A Sequence
*/
PROCEDURE xx_calling_prc IS
BEGIN
log_message('XX_CALLING_PRC', 'Calling XX_VALIDATE Procedure');
xx_validate;
log_message('XX_CALLING_PRC', 'Calling XX_INSERT_VALID_EMP Procedure');
xx_insert_valid_emp;
log_message('XX_CALLING_PRC', 'Calling XX_INSERT_IN_VALID_EMP Procedure');
xx_insert_in_valid_emp;
END xx_calling_prc;
END xx_emp_package;
=======================================================================
=========================Testing Scripts====================================
--Similar Table Created as per_all_people_f to do comparison of data
SELECT
*
FROM
xx_papf;
--To Set STATUS and ERROR_MSG Null
UPDATE xx_temp_t
SET
status = '',
error_msg = '';
--Temporary Table created in which STATUS & ERROR_MSG is stored.
SELECT
*
FROM
xx_temp_t;
--Execution Of Calling Procudure in which all other Procedures are invoked in a sequence.
EXEC xx_emp_package.xx_calling_prc;
-- Table created to insert all the valid records in the Custom table.
SELECT
*
FROM
xx_valid_emp;
-- To delete all the records from custom table in which valid records are inserted.
TRUNCATE TABLE xx_valid_emp;
-- Table created to insert all the In-valid records in the Custom table.
SELECT
*
FROM
xx_in_valid_emp;
-- To delete all the records from custom table in which In-valid records are inserted.
TRUNCATE TABLE xx_in_valid_emp;
