SRAVAN ORACLE APPS HRMS: API'S

Showing posts with label API'S. Show all posts

Friday, July 24, 2015

Terminate Employee Oracle EBS (Calling API)

Employee data could be terminated from back end using oracle HRMS API (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP).

Declare
CURSOR c_emp_cur
IS
SELECT ppos.period_of_service_id, ppos.object_version_number,
papf.person_type_id, yte.termination_date end_date, yte.ID
FROM per_all_people_f papf,
per_periods_of_service ppos,
ynppo_temp_employee yte
WHERE papf.person_id = ppos.person_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN ppos.date_start
AND COALESCE (ppos.projected_termination_date,
actual_termination_date,
SYSDATE
)
AND yte.employee_code = papf.employee_number
AND yte.flag = 'T'
AND yte.status IS NULL;

l_validate BOOLEAN := FALSE;
l_period_of_service_id NUMBER;
l_object_version_number NUMBER;
l_actual_notice_period_date DATE;
l_effective_date DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_f_asg_future_changes_warning BOOLEAN;
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_final_process_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (255);
l_f_entries_changed_warning VARCHAR2 (255);
l_alu_change_warning VARCHAR2 (255);
l_person_type_id NUMBER;
l_last_std_process_date_out DATE;
BEGIN
FOR c_emp_rec IN c_emp_cur
LOOP
l_period_of_service_id := c_emp_rec.period_of_service_id;
l_object_version_number := c_emp_rec.object_version_number;
l_actual_notice_period_date := c_emp_rec.end_date;
l_effective_date := c_emp_rec.end_date;
l_person_type_id := c_emp_rec.person_type_id;

BEGIN
hr_ex_employee_api.actual_termination_emp
(p_validate => l_validate,
p_effective_date => l_effective_date,
p_period_of_service_id => l_period_of_service_id,
p_object_version_number => l_object_version_number,
p_actual_termination_date => l_actual_notice_period_date,
p_last_standard_process_date => l_actual_notice_period_date,
--p_person_type_id => l_person_type_id,
--,p_leaving_reason => 'RESS'
p_last_std_process_date_out => l_last_std_process_date_out,
p_supervisor_warning => l_supervisor_warning,
p_event_warning => l_event_warning,
p_interview_warning => l_interview_warning,
p_review_warning => l_review_warning,
p_recruiter_warning => l_recruiter_warning,
p_asg_future_changes_warning => l_asg_future_changes_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_pay_proposal_warning => l_pay_proposal_warning,
p_dod_warning => l_dod_warning,
p_alu_change_warning => l_alu_change_warning
);

UPDATE ynppo_temp_employee
SET status = 'Terminated'
WHERE ID = c_emp_rec.ID;

EXCEPTION
WHEN OTHERS
THEN
UPDATE ynppo_temp_employee
SET status = 'Not Terminated'
WHERE ID = c_emp_rec.ID;
dbms_output.put_line(SQLERRM);
END;
END LOOP;
END;

The YNPPO_TEMP_EMPLOYEE is staging table to store employee data which will be terminated using API.
Diposkan oleh Imam Tri Harsono di 22:40
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest

Label: Human Resource API
Reaksi:
Update Employee Oracle EBS (Calling API)

Employee data could be inserted from back end using oracle HRMS API (HR_PERSON_API.UPDATE_PERSON).

Declare
CURSOR c_emp
IS
SELECT *
FROM ynppo_temp_employee yte
WHERE yte.status IS NULL AND yte.flag = 'U';

ln_object_version_number per_all_people_f.object_version_number%TYPE;
lc_dt_ud_mode VARCHAR2 (100) := NULL;
ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
lc_employee_number per_all_people_f.employee_number%TYPE;

lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;

ld_effective_start_date DATE;
ld_effective_end_date DATE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_comment_id per_all_people_f.comment_id%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
v_person_id NUMBER;
v_object_version_number NUMBER;
v_effective_start_date DATE;
v_assignment_id NUMBER;
BEGIN
FOR r_emp IN c_emp
LOOP
lc_employee_number := r_emp.employee_code;

BEGIN
SELECT papf.person_id, papf.object_version_number,
papf.effective_start_date, paaf.assignment_id
INTO v_person_id, v_object_version_number,
v_effective_start_date, v_assignment_id
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE papf.employee_number = r_emp.employee_code
AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.effective_start_date)
AND TRUNC (paaf.effective_end_date)
AND papf.person_id = paaf.person_id;
EXCEPTION
WHEN OTHERS
THEN
v_person_id := 0;
v_person_id := NULL;
END;

IF v_person_id = 0
THEN
NULL;
ELSE

-- Update Employee API
-- ---------------------------------
IF TRUNC (r_emp.effective_start_date) <>
TRUNC (v_effective_start_date)
THEN
lc_dt_ud_mode := 'UPDATE';
ELSE
lc_dt_ud_mode := 'CORRECTION';
END IF;

BEGIN
hr_person_api.update_person
( -- Input Data Elements
-- ------------------------------
p_effective_date => TRUNC (r_emp.effective_start_date),
p_datetrack_update_mode => lc_dt_ud_mode,
p_person_id => v_person_id,
p_first_name => r_emp.first_name,
p_last_name => r_emp.last_name,
p_middle_names => r_emp.middle_names,
p_sex => r_emp.gender,
p_date_of_birth => r_emp.date_of_birth,
p_title => r_emp.title,
p_email_address => r_emp.email,
p_national_identifier => lc_employee_number,
p_attribute1 => r_emp.LEVELS,
p_attribute2 => r_emp.level_desc,
p_attribute3 => r_emp.position_code,
p_attribute4 => r_emp.position_desc,
p_attribute5 => r_emp.dept_code,
p_attribute6 => r_emp.department,
p_attribute7 => r_emp.sub_dept_code,
p_attribute8 => r_emp.sub_depat,
p_attribute9 => r_emp.plant_code,
p_attribute10 => r_emp.plant,
p_attribute11 => r_emp.supervise_emp_code,
p_attribute12 => r_emp.old_employee_code,
-- Output Data Elements
-- ----------------------------------
p_employee_number => lc_employee_number,
p_object_version_number => v_object_version_number,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_full_name => lc_full_name,
p_comment_id => ln_comment_id,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);

UPDATE ynppo_temp_employee
SET status = 'Updated'
WHERE ID = r_emp.ID;
EXCEPTION
WHEN OTHERS
THEN
--ROLLBACK;
UPDATE ynppo_temp_employee
SET status = 'Not Updated'
WHERE ID = r_emp.ID;

DBMS_OUTPUT.put_line (SQLERRM);
END;
-- COMMIT;
END IF;
END LOOP;
END;

Oracle HRMS API – Update Employee Assignment & Supervisor Adding To Update Employee

-------------------------
Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id
API -- hr_assignment_api.update_emp_asg

To Update Employee
--------------------------
Grade, Location, Job, Payroll, Organization, Employee Category, People Group
API -- hr_assignment_api.update_emp_asg_criteria

Staging Table
-----------------
CREATE TABLE APPS.XXAT_EMP_ASSIGNMENTS
(
EMP_NUM NUMBER,
ORG_NAME VARCHAR2(2000 BYTE),
GCC_GROUP VARCHAR2(1000 BYTE),
POSITION VARCHAR2(2000 BYTE),
JOB VARCHAR2(2000 BYTE),
PAYROLL VARCHAR2(2000 BYTE),
LOCATION VARCHAR2(2000 BYTE),
STATUS VARCHAR2(2000 BYTE),
SAL_BASIS VARCHAR2(2000 BYTE),
SUPERVISOR_NUM NUMBER,
GOSI_EMP VARCHAR2(1000 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
ERROR_MSG VARCHAR2(3000 BYTE)
);

Flat File Format
------------------------------
Emp Num Organization GCC Group Position Job Payroll Location Status Salary Basis Supervisor Number GOSI - Employer Process Flag Error Message
3 Chief Business Support Office No.No.Yes Chief Business Support Officer.Chief Business Support Office Chief Business Support Officer EATC Payroll Riyadh Head Office Active Assignment EATC Monthly Salary 911 Etihad Atheeb Telecommunication Company N

Sample Code - Example
------------------------------
Package Specification
--------------------------

CREATE OR REPLACE PACKAGE APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update;
END xxat_emp_assign_pkg;

Package Body
----------------

CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update
IS
-- lc_c_emp_assg c_emp_assg%ROWTYPE;
l_object_version_number NUMBER;
l_special_ceiling_step_id NUMBER;
l_people_group_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
l_supervisor_id NUMBER;
l_assg_id NUMBER;
l_group_name VARCHAR2 (100);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_no_managers_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (100);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (100);
l_gsp_post_process_warning VARCHAR2 (100);
l_job_id VARCHAR2 (20);
l_position_id VARCHAR2 (20);
l_grade_id VARCHAR2 (20);
l_org_id VARCHAR2 (20);
l_loc_id VARCHAR2 (20);
l_assg_cat VARCHAR2 (50);
l_assg_start_date DATE;
l_pf_flag CHAR (1);
l_segment1 VARCHAR2 (50);
l_segment2 VARCHAR2 (50);
l_segment3 VARCHAR2 (50);
l_person_id NUMBER;
--
l_error NUMBER;
l_error_msg VARCHAR2 (3000);
v_emp_num VARCHAR2 (100);
l_assign_date VARCHAR2 (100);
l_sup_hire_date DATE;
l_emp_hire_date DATE;
l_payroll_id NUMBER;
l_paybasis_id NUMBER;
l_comment_id NUMBER;

CURSOR c1
IS
SELECT *
FROM xxat_emp_assignments
WHERE process_flag = 'N'
AND emp_num = 82;
BEGIN
DBMS_OUTPUT.put_line (' ****** Process Validation Start ******');

FOR i IN c1
LOOP
l_error := 0;
l_person_id := NULL;

IF i.emp_num IS NULL
-- OR i.assign_start_date IS NULL
OR i.org_name IS NULL
OR i.gcc_group IS NULL
OR i.POSITION IS NULL
OR i.job IS NULL
OR i.payroll IS NULL
OR i.LOCATION IS NULL
OR i.status IS NULL
OR i.sal_basis IS NULL
OR i.supervisor_num IS NULL
OR i.gosi_emp IS NULL
THEN
l_error_msg :=
'All Fields are mandatory & should not be null';
l_error := 1;
-- p_retcode := 2;
--fnd_file.put_line (fnd_file.LOG,i.relationship_id || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
ELSE
l_object_version_number := NULL;
l_special_ceiling_step_id := NULL;
l_people_group_id := NULL;
l_soft_coding_keyflex_id := NULL;
l_group_name := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_org_now_no_manager_warning := FALSE;
l_other_manager_warning := FALSE;
l_spp_delete_warning := FALSE;
l_entries_changed_warning := NULL;
l_tax_district_changed_warning := FALSE;
l_concatenated_segments := NULL;
l_gsp_post_process_warning := NULL;
-- error_desc := '';
-- lv_assg_flag := '';
l_assg_cat := NULL;
l_job_id := NULL;
l_position_id := NULL;
l_grade_id := NULL;
l_org_id := NULL;
l_assign_date := NULL;
l_sup_hire_date := NULL;
l_emp_hire_date := NULL;

---------------------------
-- AssignmentID
---------------------------
BEGIN
SELECT paf.assignment_id
INTO l_assg_id
FROM per_assignments_f paf
WHERE paf.assignment_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_assg_id := NULL;
l_error_msg := 'Assignment not found';
l_error := 1;
END;

---------------------------------------
-- Employee Hire Date / Effective Start Date
---------------------------------------
BEGIN
SELECT paf.start_date
INTO l_emp_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;

SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=
'Employee start date not found';
l_error := 1;
END;

-------------------------------------------------
-- Supervisor Hire Date / Effective Start Date
--------------------------------------------------
BEGIN
SELECT paf.start_date
INTO l_sup_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.supervisor_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;

SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN

l_error_msg :=
'Employee start date not found';
l_error := 1;
END;

IF l_emp_hire_date < l_sup_hire_date
THEN
l_assign_date := l_sup_hire_date;
ELSE
l_assign_date := l_emp_hire_date;
END IF;

--dbms_output.put_line('emp date'||l_emp_hire_date||'super date'||l_sup_hire_date||'final'||l_assign_date);
----------------------------------
-- Organization ID
----------------------------------
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_all_organization_units
WHERE UPPER (NAME) LIKE UPPER (i.org_name)
AND business_group_id = 81;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Organization not found';
l_error := 1;
END;

----------------------
-- Job ID
----------------------
BEGIN
SELECT pj.job_id
INTO l_job_id
FROM per_jobs pj,
per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND pj.NAME = i.job;
EXCEPTION
WHEN OTHERS
THEN
-- l_job_id := NULL;
l_error_msg := 'Job not found';
l_error := 1;
END;

----------------------------------------
-- Position ID
----------------------------------------
BEGIN
SELECT pap.position_id
INTO l_position_id
FROM per_all_positions pap,
per_position_definitions ppd
WHERE pap.position_definition_id = ppd.position_definition_id
AND pap.status = 'VALID'
AND pap.NAME LIKE i.POSITION || '%';
EXCEPTION
WHEN OTHERS
THEN
-- l_position_id := NULL;
l_error_msg := 'Position not found';
l_error := 1;
END;

---------------------------
-- Grade ID
---------------------------
/* BEGIN
SELECT pg.grade_id
INTO l_grade_id
FROM per_grades pg,
per_grade_definitions pgd
WHERE pg.grade_definition_id = pgd.grade_definition_id
AND UPPER (pgd.segment1) =
UPPER (lc_c_emp_assg.grad_segment1)
AND UPPER (pgd.segment2) =
UPPER (lc_c_emp_assg.grad_segment2)
AND UPPER (pgd.segment3) =
UPPER (lc_c_emp_assg.grad_segment3);
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
END;*/

---------------------------------
-- Payroll ID
---------------------------------
BEGIN
SELECT payroll_id
INTO l_payroll_id
FROM pay_all_payrolls_f
WHERE 1 = 1
AND UPPER (payroll_name) LIKE UPPER (i.payroll)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_pay_id := NULL;
l_error_msg := 'Payroll ID not found';
l_error := 1;
END;

---------------------------------
-- Location ID
---------------------------------
BEGIN
SELECT location_id
INTO l_loc_id
FROM hr_locations_all
WHERE UPPER (location_code) LIKE UPPER (i.LOCATION);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Location ID not found';
l_error := 1;
END;

-----------------------------------------------
-- Salary Information (T) > Salary Basis ID
-----------------------------------------------
BEGIN
SELECT pay_basis_id
INTO l_paybasis_id
FROM per_pay_bases
WHERE 1 = 1
AND UPPER (NAME) LIKE UPPER (i.sal_basis);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Pay/Salary basis not found';
l_error := 1;
END;

---------------------------------
-- Supervisor ID
---------------------------------
BEGIN
SELECT person_id
INTO l_supervisor_id
FROM per_all_people_f ppf
WHERE employee_number = i.supervisor_num
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Supervisor ID not found';
l_error := 1;
END;

-----------------------------
-- Assignment Category
-----------------------------

-- BEGIN

-- SELECT distinct lookup_code
-- INTO l_assg_cat
-- FROM fnd_lookup_values_vl
-- WHERE lookup_type = 'EMP_CAT'
-- AND UPPER (meaning) LIKE UPPER (lc_c_emp_assg.ASSG_CATG)
-- AND DESCRIPTION IS NULL;
--
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- l_assg_cat := NULL;
-- END;

--------------------------
-- People Group ID & NAME Number
--------------------------
BEGIN
SELECT people_group_id,
group_name
INTO l_people_group_id,
l_group_name
FROM pay_people_groups a
WHERE segment1 || '.' || segment2 || '.' || segment3 IN (
SELECT segment1 || '.' || segment2 || '.' || segment3
FROM pay_people_groups a
WHERE 1 = 1
-- AND people_group_id = 'No.No.Yes'
AND group_name = i.gcc_group);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Group ID/Name not found';
l_error := 1;
END;

--------------------------
-- Object Version Number
--------------------------
BEGIN
SELECT paaf.object_version_number
INTO l_object_version_number
FROM per_all_assignments_f paaf
WHERE SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.assignment_id = l_assg_id;

-- DBMS_OUTPUT.put_line ( 'object version no.'
-- || l_object_version_number
-- );
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Object Version not found';
l_error := 1;
END;

IF l_error = 0
THEN
BEGIN

-- Update Employee Assgment Criteria
-- -----------------------------------------------------
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TO_DATE
(l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
--l_assg_start_date,
p_datetrack_update_mode => 'CORRECTION',
--'UPDATE',
p_assignment_id => l_assg_id,
p_position_id => l_position_id,
p_job_id => l_job_id,
p_payroll_id => l_payroll_id,
-- p_grade_id => l_grade_id,
p_location_id => l_loc_id,
p_organization_id => l_org_id,
p_pay_basis_id => l_paybasis_id,
--p_employment_category => l_assg_cat ,
p_segment1 => 81,
-- GOSI Tab - Emloyer Name
-- p_segment2 => lc_c_emp_assg.pg_segment2,
-- p_segment3 => lc_c_emp_assg.pg_segment3,
-- p_supervisor_assignment_id => l_supervisor_id,
--Outparatmers
p_people_group_id => l_people_group_id,
p_object_version_number => l_object_version_number,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
--lc_c_emp_assg.people_group,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
-- p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
-- p_concatenated_segments => l_concatenated_segments,
-- p_gsp_post_process_warning => l_gsp_post_process_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Api Exception ' || SQLERRM;
END;
-- dbms_output.put_line(TO_DATE (l_assign_date,'DD-MON-RRRR' ));
BEGIN

-- Update Employee Assignment
-- ---------------------------------------------
hr_assignment_api.update_emp_asg
(
-- Input data elements
p_effective_date => TO_DATE (l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
p_datetrack_update_mode => 'UPDATE',
p_assignment_id => l_assg_id,
p_supervisor_id => l_supervisor_id,
p_change_reason => NULL,
p_manager_flag => 'Y',
-- p_bargaining_unit_code => NULL,
-- p_labour_union_member_flag => NULL,
-- p_segment1 => 204,
-- p_segment3 => 'N',
-- p_normal_hours => null,
-- p_frequency => NULL,
-- Output data elements
-- -------------------------------
p_object_version_number => l_object_version_number,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Error In Supervisors API ' || SQLERRM;
END;

IF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
ELSE
UPDATE xxat_emp_assignments
SET process_flag = 'Y'
WHERE emp_num = i.emp_num;
END IF;
ELSIF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
END IF;

COMMIT;
--fnd_file.put_line (fnd_file.LOG, '****** Process Validation End ******');
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('****** Process Validation End ******');
COMMIT;
END xxxat_emp_assign_update;
END xxat_emp_assign_pkg;
/

Tuesday, March 10, 2015

HR EMPLOYEE SALARY UPDATE API

PROCEDURE PRC_EMPSAL_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
LB_INV_NEXT_SAL_DATE_WARNING BOOLEAN;
LB_PROPOSED_SALARY_WARNING BOOLEAN;
LB_APPROVED_WARNING BOOLEAN;
LB_PAYROLL_WARNING BOOLEAN;
LN_PAY_PROPOSAL_ID NUMBER;
LN_OBJECT_VERSION_NUMBER NUMBER;
L_ASSIGNMENT_NUMBER NUMBER;
L_PROPOSAL_REASON VARCHAR2(30);
L_APPROVED VARCHAR2(10);
L_DATE_TO DATE:= NULL;
TOTAL_RECORDS NUMBER;
ERROR_RECORDS NUMBER;
CURRENT_RECORDS NUMBER;
L_ERROR_MESSAGE VARCHAR2(4000);
L_PERSON_ID NUMBER;
L_EFFECTIVE_DATE_VALID NUMBER;
CURSOR STAGING_RECORDS IS
SELECT A.*,ROWID FROM HR_EMP_UPDATE_STAGING A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
BEGIN
SELECT COUNT(*)INTO CURRENT_RECORDS FROM HR_EMP_UPDATE_STAGING A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
FOR I IN STAGING_RECORDS LOOP
--INITIAL VALUES
L_ERROR_MESSAGE := NULL;
L_APPROVED := NULL;
LN_PAY_PROPOSAL_ID := NULL;
LN_OBJECT_VERSION_NUMBER := NULL;
--STATUS VALIDATIONS
IF I.STATUS = 'Approved' THEN
L_APPROVED := 'Y';
ELSIF I.STATUS = 'Proposed' THEN
L_APPROVED := 'N';
ELSE
L_APPROVED := NULL;
END IF;
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER =I.EMPLOYEE_ID -- ENTER EMPLOYEE NUMBER HERE TO FETCH PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE('L_PERSON_ID: '||L_PERSON_ID);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
BEGIN
SELECT ASSIGNMENT_ID INTO L_ASSIGNMENT_NUMBER -- TO FETCH ASSIGNMENT_ID BASED ON PERSON_ID WHICH IS GENERATED FROM ABOVE QUERY
FROM PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND EFFECTIVE_START_DATE >=SYSDATE
AND ROWNUM<2 p="">DBMS_OUTPUT.PUT_LINE('L_ASSIGNMENT_NUMBER: '||L_ASSIGNMENT_NUMBER);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
--TO GET PROPOSAL REASONS
BEGIN
SELECT LOOKUP_CODE INTO L_PROPOSAL_REASON FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PROPOSAL_REASON' AND MEANING = I.PROPOSAL_REASON;
DBMS_OUTPUT.PUT_LINE('L_PROPOSAL_REASON: '||L_PROPOSAL_REASON);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
BEGIN
SELECT 1 INTO L_EFFECTIVE_DATE_VALID -- EFFECTIVE DATE VALIDATION
FROM PER_PAY_PROPOSALS
WHERE ASSIGNMENT_ID = L_ASSIGNMENT_NUMBER
HAVING I.EFFECTIVE_DATE > MAX(CHANGE_DATE);
DBMS_OUTPUT.PUT_LINE('L_EFFECTIVE_DATE_VALID: '||L_EFFECTIVE_DATE_VALID);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;

IF L_ASSIGNMENT_NUMBER IS NOT NULL AND I.NEW_SALARY IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL AND L_APPROVED IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' NEW SALARY: '||I.NEW_SALARY);
BEGIN
HR_MAINTAIN_PROPOSAL_API.CRE_OR_UPD_SALARY_PROPOSAL
(
-- INPUT
P_BUSINESS_GROUP_ID => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
P_ASSIGNMENT_ID => L_ASSIGNMENT_NUMBER,
P_CHANGE_DATE => I.EFFECTIVE_DATE, -- EFFECTIVE FROM DATE
P_PROPOSAL_REASON => L_PROPOSAL_REASON, -- PROPOSAL REASON
P_PROPOSED_SALARY_N => I.NEW_SALARY, -- PROPOSED SALARY
P_APPROVED => L_APPROVED,
P_DATE_TO => L_DATE_TO, -- EFFECTIVE TO DATE
-- OUTPUT
P_PAY_PROPOSAL_ID => LN_PAY_PROPOSAL_ID,
P_OBJECT_VERSION_NUMBER => LN_OBJECT_VERSION_NUMBER,
P_INV_NEXT_SAL_DATE_WARNING => LB_INV_NEXT_SAL_DATE_WARNING,
P_PROPOSED_SALARY_WARNING => LB_PROPOSED_SALARY_WARNING,
P_APPROVED_WARNING => LB_APPROVED_WARNING,
P_PAYROLL_WARNING => LB_PAYROLL_WARNING
);

IF LN_PAY_PROPOSAL_ID IS NOT NULL THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(LN_PAY_PROPOSAL_ID||' FOR :'||L_ASSIGNMENT_NUMBER||' PERSON_ID');
UPDATE HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
ELSE
UPDATE HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E', ERROR_MESSAGE = 'ERROR OUT FROM API EXECUTION' WHERE ROWID = I.ROWID;
END IF;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
UPDATE HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E', ERROR_MESSAGE = L_ERROR_MESSAGE WHERE ROWID = I.ROWID;
END;

ELSE
IF L_ASSIGNMENT_NUMBER IS NULL THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'NO SUCH EMPLOYEE EXISTS *';
END IF;
IF L_EFFECTIVE_DATE_VALID IS NULL THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT EFFECTIVE DATE *';
END IF;
IF I.NEW_SALARY IS NULL THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT SALARY *';
END IF;
IF L_APPROVED IS NULL THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT APPROVED STATUS *';
END IF;

UPDATE HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E',ERROR_MESSAGE = L_ERROR_MESSAGE WHERE ROWID = I.ROWID;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR RECORD FOR EMPLOYEE ID : '||I.EMPLOYEE_ID||' '||L_ERROR_MESSAGE);
COMMIT;
END IF;
END LOOP;

SELECT COUNT(*) INTO TOTAL_RECORDS FROM HR_EMP_UPDATE_STAGING;
SELECT COUNT(*) INTO ERROR_RECORDS FROM HR_EMP_UPDATE_STAGING WHERE UPDATE_STATUS = 'E';
--VIEW LOG FOR THIS ERROR DATA
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------RECORD VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CURRENT NO. OF RECORDS TO INSERT : '||CURRENT_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UN-SUCCESSFUL');
--VIEW OUTPUT FILE FOR THIS DATA
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------RECORD VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CURRENT NO. OF RECORDS TO INSERT : '||(TOTAL_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'SUCCESSFUL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

END PRC_EMPSAL_UPDATE;
/

HR EMPLOYEE ORGANIZATION UPDATE API

PROCEDURE PRC_EMP_ORG_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS

L_EFFECTIVE_DATE DATE:= NULL;
L_ASSIGNMENT_ID NUMBER:= NULL;
L_CORRECTION BOOLEAN;
L_UPDATE BOOLEAN;
L_UPDATE_OVERRIDE BOOLEAN;
L_UPDATE_CHANGE_INSERT BOOLEAN;
LC_DT_UD_MODE VARCHAR2(100):= NULL;
L_LOCATION_ID NUMBER:= NULL;
L_GRADE_ID NUMBER:= NULL;
L_JOB_ID NUMBER:= NULL;
L_PAYROLL_ID NUMBER:= NULL;
L_ORGANIZATION_ID NUMBER:= NULL;
L_EMPLOYEE_CATEGORY VARCHAR2(100):= NULL;
L_POSITION_ID NUMBER:= NULL;
L_PEOPLE_GROUP_ID NUMBER:= NULL;
L_OBJ_VERSION_NUM NUMBER:= NULL;
L_SPECIAL_CEILING_STEP_ID PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
L_GROUP_NAME VARCHAR2(30);
L_EFFECTIVE_START_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
L_EFFECTIVE_END_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
L_ORG_NOW_NO_MANAGER_WARNING BOOLEAN;
L_OTHER_MANAGER_WARNING BOOLEAN;
L_SPP_DELETE_WARNING BOOLEAN;
L_ENTRIES_CHANGED_WARNING VARCHAR2(30);
L_TAX_DISTRICT_CHANGED_WARN BOOLEAN;
L_PERSON_ID NUMBER:= NULL;
L_EFFECTIVE_DATE_VALID NUMBER:= NULL;
ERROR_MESSAGE1 VARCHAR2(4000):=NULL;
TOTAL_RECORDS NUMBER;
CURRENT_RECORDS NUMBER;
ERROR_RECORDS NUMBER;
CURSOR STAGING_RECORDS IS
SELECT A.*,ROWID FROM EMP_ORG_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
BEGIN
SELECT COUNT(*) INTO CURRENT_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
FOR I IN STAGING_RECORDS LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID);
--INITIATION OF VARIABLES
L_PEOPLE_GROUP_ID := NULL;
L_OBJ_VERSION_NUM := NULL;
L_SPECIAL_CEILING_STEP_ID := NULL;
ERROR_MESSAGE1 := NULL;
-- TO GET PERSON_ID BASED ON EMPLOYEE_NUMBER FROM STAGING TABLE
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER =I.EMPLOYEE_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE('L_PERSON_ID:'||L_PERSON_ID);
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;

-- TO GET OBJECT_VERSION_NUMBER BASED ON PERSON_ID AND ACTIVE PERSON
BEGIN
SELECT MAX(OBJECT_VERSION_NUMBER) INTO L_OBJ_VERSION_NUM
FROM PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
IF L_OBJ_VERSION_NUM IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('L_OBJ_VERSION_NUM:'||L_OBJ_VERSION_NUM);
END IF;
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;
-- TO FETCH THE ASSIGNMENT_ID BASED ON PERSON_ID
BEGIN
SELECT ASSIGNMENT_ID,EFFECTIVE_START_DATE,LOCATION_ID,GRADE_ID,JOB_ID,PAYROLL_ID,EMPLOYMENT_CATEGORY,POSITION_ID, PEOPLE_GROUP_ID
INTO L_ASSIGNMENT_ID, L_EFFECTIVE_DATE, L_LOCATION_ID, L_GRADE_ID, L_JOB_ID, L_PAYROLL_ID, L_EMPLOYEE_CATEGORY, L_POSITION_ID, L_PEOPLE_GROUP_ID
FROM PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND OBJECT_VERSION_NUMBER = L_OBJ_VERSION_NUM;
DBMS_OUTPUT.PUT_LINE('L_ASSIGNMENT_ID:'||L_ASSIGNMENT_ID);
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;

-- TO VALIDATE EFFECTIVE_DATE
IF I.EFFECTIVE_DATE >= L_EFFECTIVE_DATE THEN
L_EFFECTIVE_DATE_VALID := 1;
ELSE
L_EFFECTIVE_DATE_VALID := NULL;
END IF;
-- TO VALIDATE THE ORGANIZATION WHICH IS IN STAGING TABLE
BEGIN
SELECT ORGANIZATION_ID INTO L_ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = I.NEW_ORGANIZATION;
DBMS_OUTPUT.PUT_LINE('L_ORGANIZATION_ID:'||L_ORGANIZATION_ID);
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;

-- AND BUSINESS_GROUP_ID = 202;
IF L_PERSON_ID IS NOT NULL AND L_OBJ_VERSION_NUM IS NOT NULL AND L_ASSIGNMENT_ID IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL AND L_ORGANIZATION_ID IS NOT NULL THEN
BEGIN
DT_API.FIND_DT_UPD_MODES
( P_EFFECTIVE_DATE => I.EFFECTIVE_DATE,
P_BASE_TABLE_NAME => 'PER_ALL_ASSIGNMENTS_F',
P_BASE_KEY_COLUMN => 'ASSIGNMENT_ID',
P_BASE_KEY_VALUE => L_ASSIGNMENT_ID,
-- OUTPUT DATA ELEMENTS
-- --------------------------------
P_CORRECTION => L_CORRECTION,
P_UPDATE => L_UPDATE,
P_UPDATE_OVERRIDE => L_UPDATE_OVERRIDE,
P_UPDATE_CHANGE_INSERT => L_UPDATE_CHANGE_INSERT
);

IF ( L_UPDATE_OVERRIDE = TRUE OR L_UPDATE_CHANGE_INSERT = TRUE )
THEN
-- UPDATE_OVERRIDE
LC_DT_UD_MODE := 'UPDATE_OVERRIDE';
END IF;
IF ( L_CORRECTION = TRUE )
THEN
-- CORRECTION
LC_DT_UD_MODE := 'CORRECTION';
END IF;
IF ( L_UPDATE = TRUE )
THEN
-- UPDATE
LC_DT_UD_MODE := 'UPDATE';
END IF;
DBMS_OUTPUT.PUT_LINE('MODE: '||LC_DT_UD_MODE);
--WE NEED TO PROVIDE COMBINATIONS OF POSITION VALUES, JOB VALUES AND ORGANIZATION VALUES WHICH ARE LOCATED IN PER_POSITIONS.
HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
(
-- INPUT DATA ELEMENTS
P_EFFECTIVE_DATE => I.EFFECTIVE_DATE,
P_DATETRACK_UPDATE_MODE => LC_DT_UD_MODE,
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_LOCATION_ID => L_LOCATION_ID,
P_GRADE_ID => L_GRADE_ID,
P_JOB_ID => L_JOB_ID,
P_PAYROLL_ID => L_PAYROLL_ID,
P_ORGANIZATION_ID => L_ORGANIZATION_ID, --NEW ORG_ID
P_EMPLOYMENT_CATEGORY => L_EMPLOYEE_CATEGORY,
P_POSITION_ID => L_POSITION_ID,
-- OUTPUT DATA ELEMENTS
P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID,
P_OBJECT_VERSION_NUMBER => L_OBJ_VERSION_NUM,
P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_STEP_ID,
P_GROUP_NAME => L_GROUP_NAME,
P_EFFECTIVE_START_DATE => L_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => L_EFFECTIVE_END_DATE,
P_ORG_NOW_NO_MANAGER_WARNING => L_ORG_NOW_NO_MANAGER_WARNING,
P_OTHER_MANAGER_WARNING => L_OTHER_MANAGER_WARNING,
P_SPP_DELETE_WARNING => L_SPP_DELETE_WARNING,
P_ENTRIES_CHANGED_WARNING => L_ENTRIES_CHANGED_WARNING,
P_TAX_DISTRICT_CHANGED_WARNING => L_TAX_DISTRICT_CHANGED_WARN
);

DBMS_OUTPUT.PUT_LINE(L_EFFECTIVE_START_DATE);
IF L_EFFECTIVE_START_DATE IS NOT NULL THEN
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
COMMIT;
ELSE
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1||' UNKNOWN ERROR FROM API *' WHERE ROWID = I.ROWID;
COMMIT;
END IF;

EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1||' *' WHERE ROWID = I.ROWID;
COMMIT;
END;
ELSE
IF L_PERSON_ID IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH EMPLOYEE EXISTS *';
END IF;
IF L_ASSIGNMENT_ID IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH ASSIGNMENT EXISTS *';
END IF;
IF I.EFFECTIVE_DATE IS NULL OR L_EFFECTIVE_DATE_VALID IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'PLEASE PROVIDE CORRECT EFFECTIVE DATE *';
END IF;
IF L_OBJ_VERSION_NUM IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO ACTIVE ASSIGNMENTS AVAILABLE FOR THIS EMPLOYEE *';
END IF;
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1 WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
SELECT COUNT(1) INTO TOTAL_RECORDS FROM EMP_ORG_UPDATE_STG;
SELECT COUNT(1) INTO ERROR_RECORDS FROM EMP_ORG_UPDATE_STG WHERE UPDATE_STATUS = 'E';
--VIEW LOG FOR THIS ERROR DATA
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------RECORD VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CURRENT NO. OF RECORDS TO INSERT : '||CURRENT_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------');

--VIEW OUTPUT FILE FOR THIS DATA
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------RECORD VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CURRENT NO. OF RECORDS TO INSERT : '||(TOTAL_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PRC_EMP_ORG_UPDATE;
/