Oracle Technical Consultant Information: Oracle HRMS Uploading Scripts

CREATE OR REPLACE package APPS.cust_hrms_data_upload is
PROCEDURE create_hr_organization;
procedure update_hr_oranization ;
procedure delete_hr_hierarchy_element;
procedure create_hr_jobs;
procedure create_hr_grades;
procedure create_hr_positions ;
procedure update_hr_positions ;
procedure hr_delete_positions;
PROCEDURE CREATE_HR_employee ;
procedure update_hr_employee;
PROCEDURE create_emp_asg_criteria;
PROCEDURE create_emp_address;
procedure create_emp_contact ;
PROCEDURE create_emp_phone;
procedure create_exemployer_info ;
procedure create_qualification ;
procedure create_emp_aai;
procedure employee_create_adjustment;
procedure employee_leave_adjustment ;
procedure create_tk_group_criteria ;
procedure Emp_salary_create_or_update;
end;
/

CREATE OR REPLACE PACKAGE BODY APPS.cust_hrms_data_upload

/*********** Human resource structures data uploading below the following list of Scripts **************/

--1 PROCEDURE create_hr_organization ; This procedure create hr organization and organiztion hireracy

--2 procedure update_hr_oranization ;

--3 procedure delete_hr_hierarchy_element;

--4 procedure create_hr_jobs;

--5 procedure create_hr_grades;

--6 procedure create_hr_positions ;

--7 procedure update_hr_positions ;

--8 procedure hr_delete_positions;

--9 PROCEDURE CREATE_HR_employee ;

--10procedure update_hr_employee; update employee information

--11PROCEDURE create_emp_asg_criteria; update employee assignment

--12PROCEDURE create_emp_address;

--13procedure create_emp_contact ;

--14PROCEDURE create_emp_phone;

--15procedure create_exemployer_info ;

--15procedure create_qualification ;

--17procedure create_emp_aai;

--18procedure employee_create_adjustment;

--19procedure employee_leave_adjustment ;

--20procedure create_tk_group_criteria ; creat time keeper group criteria

--21procedure Emp_salary_create_or_update Create or Update Employee Salary

PROCEDURE create_hr_organization

CURSOR c_org

SELECT DISTINCT TRIM (effective_date) effective_date,

TRIM (org_name) org_name, TRIM (org_type) org_type,

TRIM (locations) locations,

TRIM (date_from) date_from,

TRIM (bussiness_group_id) bussiness_group_id,

TRIM (internal_external_flag)

internal_external_flag

-- ORG_HIERARCHY_NAME,

-- PARENT_ORG_NAME

FROM ifl_hr_organization ho

WHERE NVL (ho.status_upload, 'N') <> 'N';

-- AND ho.org_hierarchy_name NOT IN

-- ('IFL Reporting Hierarchy',

-- 'IFL Organization Hierarchy');

CURSOR c_org_hcy_elm

SELECT DISTINCT TRIM (ho.org_name) org_name,

TRIM (org_hierarchy_name) org_hierarchy_name,

TRIM (effective_date) effective_date,

TRIM (parent_org_name) parent_org_name,

TRIM (bussiness_group_id) bussiness_group_id,

ho.org_type

FROM ifl_hr_organization ho

WHERE 1 = 1

-- and ho.PARENT_ORG_NAME is not null

AND ho.org_hierarchy_name IN ('IFL Reporting Hierarchy');

lc_c_org c_org%ROWTYPE;

-- lc_c_org_hcy c_org_hcy%ROWTYPE ;

lc_c_org_hcy_elm c_org_hcy_elm%ROWTYPE;

l_organization_id NUMBER;

l_org_obj_ver_number_inf NUMBER;

l_org_obj_ver_number_org NUMBER;

l_org_information_id NUMBER;

l_org_dup_org_warning BOOLEAN;

l_org_flag CHAR (1);

l_org_error_desc VARCHAR2 (240);

l_hcy_process_flag CHAR (1);

l_hcy_error_description VARCHAR2 (1000);

l_hcy_org_structure_id NUMBER;

l_hcy_obj_ver_number NUMBER;

l_elm_process_flag CHAR (1);

l_elm_error_description VARCHAR2 (240);

l_elm_inactive_org_warning BOOLEAN;

l_elm_struc_element_id NUMBER;

l_elm_obj_ver_number NUMBER;

l_location_id VARCHAR2 (20);

l_int_ext VARCHAR2 (30);

l_type VARCHAR2 (100);

l_child_org_id VARCHAR2 (20);

l_parent_org_id VARCHAR2 (20);

l_struc_ver_id VARCHAR2 (20);

l_org_id NUMBER;

l_org_object_version_number NUMBER;

BEGIN

OPEN c_org;

LOOP

FETCH c_org

INTO lc_c_org;

EXIT WHEN c_org%NOTFOUND;

BEGIN

---------------------------

--- Type Code

---------------------------

SELECT lookup_code

INTO l_type

FROM hr_lookups

WHERE lookup_type = 'ORG_TYPE'

AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_org.org_type));

---------------------------

--- Internal - External Code

---------------------------

SELECT lookup_code

INTO l_int_ext

FROM hr_lookups

WHERE lookup_type = 'INTL_EXTL'

AND UPPER (meaning) LIKE

UPPER (TRIM (lc_c_org.internal_external_flag));

---------------------------

--- Location ID

---------------------------

IF lc_c_org.locations IS NOT NULL

THEN

SELECT location_id

INTO l_location_id

FROM hr_locations_all

WHERE UPPER (location_code) =

UPPER (TRIM (lc_c_org.locations));

ELSE

l_location_id := NULL;

END IF;

hr_organization_api.create_hr_organization

(p_validate => FALSE,

p_effective_date => lc_c_org.effective_date,

p_name => lc_c_org.org_name,

p_type => l_type,

p_location_id => l_location_id,

p_date_from => lc_c_org.date_from,

p_internal_external_flag => l_int_ext,

p_business_group_id => lc_c_org.bussiness_group_id,

p_enabled_flag => 'Y',

p_object_version_number_inf => l_org_obj_ver_number_inf,

p_object_version_number_org => l_org_obj_ver_number_org,

p_organization_id => l_organization_id,

p_org_information_id => l_org_information_id,

p_duplicate_org_warning => l_org_dup_org_warning

);

COMMIT;

IF l_organization_id IS NOT NULL

THEN

l_org_flag := 'Y';

l_org_error_desc := 'No Error';

END IF;

IF l_org_dup_org_warning = FALSE

THEN

l_org_error_desc := 'No Duplicate Warning,';

ELSE

l_org_error_desc := 'Duplicate Warning,';

END IF;

IF l_organization_id IS NOT NULL

THEN

UPDATE ifl_hr_organization

SET status_upload = 'Y'

WHERE TRIM (org_name) = lc_c_org.org_name;

ELSE

l_org_error_desc := l_org_error_desc || SQLERRM;

UPDATE ifl_hr_organization

--SET org_error_description = l_org_error_desc ,

SET status_upload = 'N'

WHERE TRIM (org_name) = lc_c_org.org_name;

END IF;

DBMS_OUTPUT.put_line ( 'Organization ID : '

|| lc_c_org.org_name

|| ': '

|| l_organization_id

);

EXCEPTION

WHEN OTHERS

THEN

l_org_error_desc := l_org_error_desc || SQLERRM;

UPDATE ifl_hr_organization

SET status_upload = 'N'

WHERE TRIM (org_name) = lc_c_org.org_name;

END;

l_organization_id := '';

l_org_obj_ver_number_inf := '';

l_org_obj_ver_number_org := '';

l_org_information_id := '';

l_org_dup_org_warning := FALSE;

l_org_flag := '';

l_org_error_desc := '';

l_location_id := '';

l_int_ext := '';

l_type := '';

l_org_id := '';

l_org_object_version_number := '';

END LOOP;

CLOSE c_org;

/* OPEN c_org_hcy;

LOOP

FETCH c_org_hcy INTO LC_c_org_hcy;

EXIT WHEN c_org_hcy%NOTFOUND;

BEGIN

Per_Organization_Structure_Api.create_org_struct_and_def_ver

(

p_validate => FALSE

, p_effective_date => LC_c_org_hcy.EFFECTIVE_DATE

, p_name => LC_c_org_hcy.org_hierarchy_name

, p_business_group_id => 0

, p_primary_structure_flag => 'Y'

, p_position_control_structure_f => 'Y'

, p_organization_structure_id => l_hcy_org_structure_id

, p_object_version_number => l_hcy_obj_ver_number

);

IF l_hcy_org_structure_id IS NOT NULL THEN

l_hcy_process_flag := 'Y';

l_hcy_error_description := 'No Error';

END IF;

IF l_hcy_process_flag = 'Y' THEN

UPDATE DEV_ORGANIZATION

SET

hcy_process_flag = l_hcy_process_flag ,

hcy_error_description = l_hcy_error_description ,

hcy_org_structure_id = l_hcy_org_structure_id ,

hcy_obj_ver_number = l_hcy_obj_ver_number

WHERE org_hierarchy_name = LC_c_org_hcy.org_hierarchy_name

AND effective_start_date = LC_c_org_hcy.effective_start_date;

ELSE

l_hcy_error_description := l_hcy_error_description ||SQLERRM;

UPDATE DEV_ORGANIZATION

SET hcy_error_description = l_hcy_error_description ,

hcy_process_flag = 'N'

WHERE org_hierarchy_name = LC_c_org_hcy.org_hierarchy_name

AND effective_start_date = LC_c_org_hcy.effective_start_date;

END IF;

DBMS_OUTPUT.PUT_LINE('Hierarchy Structure ID : '||l_hcy_org_structure_id);

EXCEPTION

WHEN OTHERS THEN

l_hcy_error_description := l_hcy_error_description ||SQLERRM;

UPDATE DEV_ORGANIZATION

SET hcy_error_description = l_hcy_error_description ,

hcy_process_flag = 'N'

WHERE org_hierarchy_name = LC_c_org_hcy.org_hierarchy_name

AND effective_start_date = LC_c_org_hcy.effective_start_date;

END;

l_hcy_process_flag := '';

l_hcy_error_description := '';

l_hcy_org_structure_id := '';

l_hcy_obj_ver_number := '';

END LOOP;

CLOSE c_org_hcy;*/

OPEN c_org_hcy_elm;

LOOP

FETCH c_org_hcy_elm

INTO lc_c_org_hcy_elm;

EXIT WHEN c_org_hcy_elm%NOTFOUND;

BEGIN

----------------------------------------

-- Child Organization ID

----------------------------------------

SELECT haou.organization_id

INTO l_child_org_id

FROM hr_all_organization_units haou

WHERE UPPER (haou.NAME) LIKE UPPER (lc_c_org_hcy_elm.org_name);

----------------------------------------

-- Parent Organization ID

----------------------------------------

SELECT haou.organization_id

INTO l_parent_org_id

FROM hr_all_organization_units haou

WHERE UPPER (haou.NAME) LIKE

UPPER (lc_c_org_hcy_elm.parent_org_name);

----------------------------------------

-- Structure Version ID

----------------------------------------

SELECT posv.org_structure_version_id

INTO l_struc_ver_id

FROM per_org_structure_versions posv

WHERE posv.organization_structure_id IN (

SELECT organization_structure_id

FROM per_organization_structures

WHERE UPPER (NAME) LIKE

UPPER (lc_c_org_hcy_elm.org_hierarchy_name));

-- AND PRIMARY_STRUCTURE_FLAG = 'Y');

hr_hierarchy_element_api.create_hierarchy_element

(p_validate => FALSE,

p_effective_date => lc_c_org_hcy_elm.effective_date,

p_organization_id_parent => l_parent_org_id,

p_org_structure_version_id => l_struc_ver_id,

p_organization_id_child => l_child_org_id,

p_business_group_id => lc_c_org_hcy_elm.bussiness_group_id,

p_pos_control_enabled_flag => 'N' --'Y'

p_inactive_org_warning => l_elm_inactive_org_warning,

p_org_structure_element_id => l_elm_struc_element_id,

p_object_version_number => l_elm_obj_ver_number

);

IF l_elm_struc_element_id IS NOT NULL

THEN

UPDATE ifl_hr_organization ho

SET ho.status_upload = 'Y'

WHERE TRIM (ho.org_name) = lc_c_org_hcy_elm.org_name;

ELSE

UPDATE ifl_hr_organization ho

SET ho.status_upload = 'N'

WHERE TRIM (ho.org_name) = lc_c_org_hcy_elm.org_name;

END IF;

COMMIT;

DBMS_OUTPUT.put_line ( 'Hierarchy Structure Element ID : '

|| lc_c_org_hcy_elm.org_name

|| ': '

|| l_elm_struc_element_id

);

EXCEPTION

WHEN OTHERS

THEN

l_elm_error_description := l_elm_error_description || SQLERRM;

/* UPDATE DEV_ORGANIZATION

SET elm_error_description = l_elm_error_description ,

elm_process_flag = 'N'

WHERE line_id = LC_c_org_hcy_elm.line_id; */

END;

l_elm_process_flag := '';

l_elm_error_description := '';

l_elm_inactive_org_warning := FALSE;

l_elm_struc_element_id := '';

l_elm_obj_ver_number := '';

l_parent_org_id := '';

l_child_org_id := '';

END LOOP;

CLOSE c_org_hcy_elm;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error');

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_hr_jobs

CURSOR hr_job

SELECT TRIM (bussiness_group_id) bussiness_group_id,

TRIM (date_from) date_from, TRIM (job_group) job_group,

TRIM (segment1) segment1, TRIM (segment2) segment2,

TRIM (segment3) segment3, TRIM (segment4) segment4

FROM ifl_hr_jobs hj

WHERE hj.status_upload IS NULL;

c_job hr_job%ROWTYPE;

l_job_id NUMBER := NULL;

l_object_version_number NUMBER := NULL;

l_err_msg VARCHAR2 (500) := NULL;

l_name VARCHAR2 (500) := NULL;

l_job_definition_id NUMBER := NULL;

l_job_group_id NUMBER;

BEGIN

OPEN hr_job;

LOOP

FETCH hr_job

INTO c_job;

EXIT WHEN hr_job%NOTFOUND;

BEGIN

-------------------- ifl job group id -------

hr_job_api.create_job

(p_validate => FALSE,

p_business_group_id => c_job.bussiness_group_id,

p_date_from => TO_DATE

('01-JAN-1950',

'DD-MON-YYYY'

p_job_group_id => c_job.job_group,

p_segment1 => c_job.segment1,

p_segment2 => c_job.segment2,

p_segment3 => c_job.segment3,

p_segment4 => c_job.segment4,

p_job_id => l_job_id,

p_object_version_number => l_object_version_number,

p_job_definition_id => l_job_definition_id,

p_name => l_name

);

COMMIT;

DBMS_OUTPUT.put_line ('Job has been created: ' || l_job_id);

IF l_job_id IS NOT NULL

THEN

UPDATE ifl_hr_jobs hj

SET hj.status_upload = 'Y'

WHERE hj.segment1 = c_job.segment1;

ELSE

UPDATE ifl_hr_jobs hj

SET hj.status_upload = ''

WHERE hj.segment1 = c_job.segment1;

END IF;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);

END;

l_object_version_number := '';

l_err_msg := '';

l_name := '';

l_job_definition_id := '';

END LOOP;

CLOSE hr_job;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END;

PROCEDURE create_hr_grades

CURSOR hr_grade

SELECT TRIM (bussiness_group_id) bussiness_group_id,

TRIM (date_from) date_from,

TRIM (grade_sequence) grade_sequence,

TRIM (segment1) segment1, TRIM (segment2) segment2,

TRIM (segment3) segment3, TRIM (segment4) segment4

FROM ifl_hr_grades hg

WHERE hg.status_upload IS NULL;

c_grade hr_grade%ROWTYPE;

l_err_msg VARCHAR2 (500);

l_grade_id NUMBER;

l_object_version_number NUMBER;

l_grade_definition_id NUMBER;

l_name VARCHAR2 (100);

BEGIN

OPEN hr_grade;

LOOP

FETCH hr_grade

INTO c_grade;

EXIT WHEN hr_grade%NOTFOUND;

BEGIN

hr_grade_api.create_grade

(p_validate => FALSE,

p_business_group_id => c_grade.bussiness_group_id,

p_date_from => c_grade.date_from,

p_sequence => c_grade.grade_sequence,

p_segment1 => c_grade.segment1,

p_segment2 => c_grade.segment2,

p_segment3 => c_grade.segment3,

p_segment4 => c_grade.segment4,

p_short_name => c_grade.segment1

|| '-'

|| c_grade.segment2,

p_grade_id => l_grade_id,

p_object_version_number => l_object_version_number,

p_grade_definition_id => l_grade_definition_id,

p_name => l_name

);

DBMS_OUTPUT.put_line ('Job has been created: ' || c_grade.segment1);

IF l_name IS NOT NULL

THEN

UPDATE ifl_hr_grades hg

SET hg.status_upload = 'Y'

WHERE hg.segment1 = c_grade.segment1;

ELSE

UPDATE ifl_hr_grades hg

SET hg.status_upload = ''

WHERE hg.segment1 = c_grade.segment1;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);

END;

END LOOP;

CLOSE hr_grade;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END;

PROCEDURE create_hr_positions

CURSOR hr_posit

SELECT TRIM (position_org_name) position_org_name, effective_date,

TRIM (job_name) job_name, UPPER (position_type)

position_type,

bussiness_group_id, TRIM (grades) grades, fte, max_person,

TRIM (segment1) segment1, TRIM (segment2) segment2,

TRIM (segment3) segment3, TRIM (segment4) segment4,

TRIM (segment5) segment5

FROM ifl_hr_positions hp

WHERE NVL (hp.status_upload, 'N') NOT LIKE 'Y';

-- AND ( trim(segment1),trim(segment2),trim(segment3),trim(segment4) , trim(segment5) ) in ( select ppd.SEGMENT1,ppd.SEGMENT2,ppd.SEGMENT3,ppd.SEGMENT4,ppd.SEGMENT30 from per_position_definitions ppd ) ;

-- AND HP.POSITION_ORG_NAME = 'Cone Winding-TP2 Unit 1' ;

c_posit hr_posit%ROWTYPE;

l_pos_org_id NUMBER;

l_date_end DATE := hr_general.end_of_time;

l_job_id NUMBER;

l_pos_type VARCHAR2 (30) := 'SHARED';

l_avail_id NUMBER := 1;

l_probation_period NUMBER := 6;

l_probation_unit_desc CHAR (2) := 'M';

l_frequency CHAR (2) := 'W';

l_working_hours NUMBER := 37;

l_grade_id NUMBER;

l_barg_unit_cd CHAR (5) := 'EWP';

l_orcl_pos_title_code VARCHAR2 (20) := 'MECH_TECH';

l_err_msg VARCHAR2 (500);

l_validate_mode BOOLEAN := FALSE;

-------- out variables

l_position_id NUMBER := NULL;

l_object_version_number NUMBER := NULL;

l_eff_start_date DATE := NULL;

l_eff_end_date DATE := NULL;

l_position_definition_id NUMBER := NULL;

l_pos_name VARCHAR2 (250) := NULL;

BEGIN

OPEN hr_posit;

LOOP

FETCH hr_posit

INTO c_posit;

EXIT WHEN hr_posit%NOTFOUND;

BEGIN

--------- find hr oranization id ---------

SELECT hou.organization_id

INTO l_pos_org_id

FROM hr_organization_units hou

WHERE UPPER (hou.NAME) LIKE UPPER (c_posit.position_org_name)

AND hou.business_group_id = c_posit.bussiness_group_id

AND hou.date_to IS NULL;

------------- find job id --------------------

SELECT pj.job_id

INTO l_job_id

FROM per_jobs pj

WHERE UPPER (pj.NAME) LIKE UPPER (c_posit.job_name)

AND pj.business_group_id = c_posit.bussiness_group_id

AND pj.date_to IS NULL;

------------- find grade id ----------------

/*SELECT pr.grade_id

INTO l_grade_id

FROM per_grades pr

WHERE UPPER (pr.NAME) LIKE UPPER (c_posit.grades)

AND pr.business_group_id = c_posit.bussiness_group_id

AND pr.date_to IS NULL;*/

hr_position_api.create_position

(p_job_id => l_job_id

--l_job_id --The job for the position

p_organization_id => l_pos_org_id,

p_effective_date => c_posit.effective_date,

p_date_effective => c_posit.effective_date

--The date on which the position becomes active

p_validate => l_validate_mode,

p_availability_status_id => l_avail_id,

p_business_group_id => c_posit.bussiness_group_id,

-- p_entry_grade_id => l_grade_id,

p_position_type => c_posit.position_type

--- ,p_fte => c_posit.fte

--- ,p_max_persons => c_posit.max_person

p_segment1 => c_posit.segment1,

p_segment2 => c_posit.segment2,

p_segment3 => c_posit.segment3,

p_segment30 => c_posit.segment4,

p_segment4 => c_posit.segment5

/*OUT*/

p_position_id => l_position_id,

p_object_version_number => l_object_version_number,

p_effective_start_date => l_eff_start_date,

p_effective_end_date => l_eff_end_date

--IN/OUT

p_position_definition_id => l_position_definition_id,

p_name => l_pos_name

);

DBMS_OUTPUT.put_line ( 'hr_position_api.create_position API: '

|| l_pos_name

);

IF l_pos_name IS NOT NULL

THEN

UPDATE ifl_hr_positions hp

SET hp.status_upload = 'Y'

WHERE

hp.SEGMENT1 = c_posit.segment1

and hp.SEGMENT2 = c_posit.segment2

and hp.SEGMENT3 = c_posit.segment3

and hp.SEGMENT4 = c_posit.segment4

and hp.SEGMENT5 = c_posit.segment5;

ELSE

UPDATE ifl_hr_positions hp

SET hp.status_upload = 'N'

WHERE hp.SEGMENT1 = c_posit.segment1

and hp.SEGMENT2 = c_posit.segment2

and hp.SEGMENT3 = c_posit.segment3

and hp.SEGMENT4 = c_posit.segment4

and hp.SEGMENT5 = c_posit.segment5;

END IF;

IF l_position_id IS NULL OR l_object_version_number IS NULL

THEN

DBMS_OUTPUT.put_line

( 'hr_position_api.create_position API Error: '

|| SQLERRM

);

ROLLBACK;

ELSE

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line

( 'hr_position_api.create_position API failed with error :'

|| SQLERRM

);

ROLLBACK;

COMMIT;

END;

l_position_id := NULL;

l_object_version_number := NULL;

l_eff_start_date := NULL;

l_eff_end_date := NULL;

--IN/OUT

l_position_definition_id := NULL;

l_pos_name := NULL;

l_job_id := NULL;

l_pos_org_id := NULL;

l_grade_id := NULL;

COMMIT;

END LOOP;

CLOSE hr_posit;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END;

PROCEDURE create_hr_employee

CURSOR c_emp

SELECT emp_no, last_name, middle_names, first_name, title,

father_name, person_type, hire_date, date_of_birth,

town_of_birth, country_of_birth, nationality, place_of_birth,

nic, gender, marital_status, office_email, eam_organization,

eam_section, rate_per_hours, eam_status, reason, designation,

vehicle_number, hcm_employee_no, cnic_expiry_date,

old_cnic_no, skill_level, ntn_no, wedding_date, religon,

sect, casts, blood_group, family_code, insurance_no,

black_listed, emp_reference, passport_no,

additional_person_detail

FROM ifl_hr_employee he

WHERE he.person_id IS NULL AND NVL (he.process_flag, 'N') <> 'Y';

lc_c_emp c_emp%ROWTYPE;

l_batch_id NUMBER;

l_per_object_version_number NUMBER;

l_asg_object_version_number NUMBER;

l_person_id NUMBER;

l_assignment_id NUMBER;

l_person_type_id NUMBER;

l_per_effective_start_date DATE;

l_per_effective_end_date DATE;

l_full_name VARCHAR2 (60);

l_per_comment_id NUMBER;

l_assignment_sequence NUMBER;

l_assignment_number VARCHAR2 (30);

l_name_combination_warning BOOLEAN;

l_assign_payroll_warning BOOLEAN;

l_emp_number VARCHAR2 (30);

l_user_key VARCHAR2 (4000);

l_assg_user_key VARCHAR2 (4000);

l_orig_hire_warning BOOLEAN;

l_person_extra_info_id NUMBER;

l_object_version_number NUMBER;

error_desc VARCHAR2 (240);

lv_per_flag CHAR (1);

lv_info_flag CHAR (1);

l_gender VARCHAR2 (1);

l_country_code VARCHAR2 (5);

l_marital_status VARCHAR2 (15);

l_nationality VARCHAR2 (20);

l_title VARCHAR2 (20);

l_religion VARCHAR2 (100);

l_blood_group VARCHAR2 (100);

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp

INTO lc_c_emp;

EXIT WHEN c_emp%NOTFOUND;

----------------------

-- Title Lookup Value

----------------------

SELECT lookup_code

INTO l_title

FROM hr_lookups

WHERE lookup_type = 'TITLE'

AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_emp.title));

----------------------

-- Gender Lookup Value

----------------------

SELECT lookup_code

INTO l_gender

FROM fnd_lookups

WHERE lookup_type = 'IGW_SUBJECT_TYPE'

AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_emp.gender));

BEGIN

----------------------

-- Country Lookup Value

----------------------

SELECT lookup_code

INTO l_country_code

FROM fnd_lookups

WHERE lookup_type = 'JEES_EURO_COUNTRY_CODES'

AND UPPER (meaning) LIKE

UPPER (TRIM (lc_c_emp.country_of_birth));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Country Lookup error';

END;

IF lc_c_emp.marital_status IS NOT NULL

THEN

BEGIN

----------------------

-- Marital Status Lookup Value

----------------------

SELECT lookup_code

INTO l_marital_status

FROM hr_lookups

WHERE lookup_type = 'MAR_STATUS'

AND UPPER (meaning) = UPPER (TRIM (lc_c_emp.marital_status));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Marital Lookup error';

END;

END IF;

BEGIN

----------------------

-- Nationality Lookup Value

----------------------

SELECT lookup_code

INTO l_nationality

FROM hr_lookups

WHERE lookup_type = 'NATIONALITY'

AND UPPER (meaning) = UPPER (TRIM (lc_c_emp.nationality));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Nationality Lookup error';

END;

BEGIN

----------------------

-- person_type_id

----------------------

SELECT ppt.person_type_id

INTO l_person_type_id

FROM per_person_types ppt

WHERE UPPER (ppt.user_person_type) =

UPPER (TRIM (lc_c_emp.person_type));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'PERSON TYPE ERROR';

END;

IF LC_C_EMP.religion IS NOT NULL THEN

BEGIN

----------------------

-- Religion Lookup Value

----------------------

SELECT lookup_code INTO l_religion

FROM HR_LOOKUPS

WHERE lookup_type = 'PK_RELIGIONS'

AND UPPER(MEANING) = UPPER(trim(LC_C_EMP.religion));

EXCEPTION

WHEN NO_DATA_FOUND THEN

ERROR_DESC := ERROR_DESC ||'Religion Lookup error';

END;

END IF;

IF LC_C_EMP.blood_group IS NOT NULL THEN

BEGIN

----------------------

-- Blood Group Lookup Value

----------------------

SELECT lookup_code INTO l_blood_group

FROM HR_LOOKUPS

WHERE lookup_type = 'PK_BLOOD_GROUPS'

AND UPPER(MEANING) = UPPER(trim(LC_C_EMP.blood_group));

EXCEPTION

WHEN NO_DATA_FOUND THEN

ERROR_DESC := ERROR_DESC ||'Blood Group Lookup error';

END;

END IF;

BEGIN

hr_employee_api.create_employee

(p_validate => FALSE,

p_business_group_id => 0,

p_last_name => lc_c_emp.last_name,

p_middle_names => lc_c_emp.middle_names,

p_first_name => lc_c_emp.first_name,

p_suffix => lc_c_emp.father_name,

-- p_pre_name_adjunct => lc_c_emp.husband_name,

p_title => l_title,

p_sex => l_gender,

p_person_type_id => l_person_type_id,

p_national_identifier => lc_c_emp.nic,

p_hire_date => lc_c_emp.hire_date,

p_date_of_birth => lc_c_emp.date_of_birth,

p_town_of_birth => lc_c_emp.town_of_birth,

p_country_of_birth => l_country_code,

p_marital_status => l_marital_status,

p_nationality => l_nationality,

p_region_of_birth => lc_c_emp.place_of_birth,

p_email_address => lc_c_emp.office_email,

p_attribute1 => lc_c_emp.eam_organization,

p_attribute2 => lc_c_emp.rate_per_hours,

---to_char(LC_C_EMP.NIC_ISSUE_DATE,'YYYY/MM/DD HH:MI:SS') ,

p_attribute3 => lc_c_emp.eam_section,

p_attribute4 => lc_c_emp.eam_status,

p_attribute5 => lc_c_emp.reason,

p_attribute10 => lc_c_emp.designation,

p_attribute11 => lc_c_emp.vehicle_number,

p_attribute13 => lc_c_emp.office_email,

p_attribute14 => lc_c_emp.hcm_employee_no,

p_attribute15 => lc_c_emp.old_cnic_no,

p_attribute16 => lc_c_emp.cnic_expiry_date,

p_attribute17 => lc_c_emp.skill_level,

p_attribute18 => lc_c_emp.ntn_no,

p_attribute19 => lc_c_emp.wedding_date,

p_attribute20 => lc_c_emp.religon,

p_attribute21 => lc_c_emp.sect,

p_attribute22 => lc_c_emp.casts,

p_attribute23 => lc_c_emp.blood_group,

p_attribute24 => lc_c_emp.family_code,

p_attribute25 => lc_c_emp.insurance_no,

p_attribute26 => lc_c_emp.black_listed,

p_attribute27 => lc_c_emp.emp_reference,

p_attribute28 => lc_c_emp.passport_no,

p_person_id => l_person_id,

p_employee_number => lc_c_emp.emp_no,

p_assignment_id => l_assignment_id,

p_per_object_version_number => l_per_object_version_number,

p_asg_object_version_number => l_asg_object_version_number,

p_per_effective_start_date => l_per_effective_start_date,

p_per_effective_end_date => l_per_effective_end_date,

p_full_name => l_full_name,

p_per_comment_id => l_per_comment_id,

p_assignment_sequence => l_assignment_sequence,

p_assignment_number => l_assignment_number,

p_name_combination_warning => l_name_combination_warning,

p_assign_payroll_warning => l_assign_payroll_warning,

p_orig_hire_warning => l_orig_hire_warning

);

IF l_person_id IS NOT NULL

THEN

lv_per_flag := 'Y';

END IF;

IF l_name_combination_warning = FALSE

THEN

error_desc := 'No Name Warning,';

ELSE

error_desc := 'Name Combination Warning,';

END IF;

IF l_assign_payroll_warning = FALSE

THEN

error_desc := error_desc || ' No payroll Warning,';

ELSE

error_desc := error_desc || ' payroll birth_date warning,';

END IF;

IF l_orig_hire_warning = FALSE

THEN

error_desc := error_desc || ' No hire_date Warning,';

ELSE

error_desc := error_desc || ' hire_date warning,';

END IF;

IF lv_per_flag = 'Y'

THEN

UPDATE ifl_hr_employee

SET error_description = error_desc,

process_flag = lv_per_flag,

person_id = l_person_id

WHERE emp_no = lc_c_emp.emp_no;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE ifl_hr_employee

SET error_description = error_desc,

process_flag = lv_per_flag

WHERE emp_no = lc_c_emp.emp_no;

DBMS_OUTPUT.put_line

( 'Employee is Created without any Warning'

|| lc_c_emp.emp_no

);

END IF;

EXCEPTION

WHEN OTHERS

THEN

lv_per_flag := 'N';

error_desc := error_desc || SQLERRM;

UPDATE ifl_hr_employee

SET error_description = error_desc,

process_flag = lv_per_flag

WHERE emp_no = lc_c_emp.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

DBMS_OUTPUT.put_line ('Person ID : ' || l_person_id);

lv_per_flag := '';

lv_info_flag := '';

error_desc := '';

l_person_id := '';

l_emp_number := '';

l_assignment_id := '';

l_asg_object_version_number := '';

l_per_effective_start_date := '';

l_per_effective_end_date := '';

l_full_name := '';

l_per_comment_id := '';

l_assignment_sequence := '';

l_assignment_number := '';

l_name_combination_warning := FALSE;

l_assign_payroll_warning := FALSE;

l_orig_hire_warning := FALSE;

l_person_extra_info_id := '';

l_object_version_number := '';

COMMIT;

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_emp_asg_criteria

CURSOR c_emp_assg

IS --- cursor used in update assignment criteria ---

SELECT rfid_card_no, eobi_member, eobi_no, eobi_effective_date,

sessi_pessi_member, sessi_pessi_no,

sessi_pessi_effective_date, current_assign_start_date,

assg_status, assg_category, organization_name, locations,

payroll_name, job_name, position_name, grade_name,

salary_basis, probation_period_unit, end_date_probation,

supervisor_empno, supervisor_name, grade_ladder_name,

group_segment1, group_segment2, group_segment3,

group_segment4, group_segment5, group_segment6, he.person_id,

pax.assignment_id, emp_category, hire_date, emp_no

FROM ifl_hr_employee he, per_assignments_x pax

WHERE NVL (assg_process_flag, 'N') <> 'Y'

AND pax.person_id = he.person_id

AND he.person_id IS NOT NULL;

CURSOR c_assg

SELECT rfid_card_no, --- cursor used in update assignment dff ---

eobi_member, eobi_no, eobi_effective_date,

sessi_pessi_member, sessi_pessi_no,

sessi_pessi_effective_date, current_assign_start_date,

probation_period_unit, 8 normal_hours, end_date_probation,

he.person_id, pax.assignment_id, emp_no,

pax.object_version_number

FROM ifl_hr_employee he, per_assignments_x pax

WHERE NVL (assg_process_flag, 'N') <> 'Y'

AND pax.person_id = he.person_id

AND he.person_id IS NOT NULL;

lc_c_emp_assg c_emp_assg%ROWTYPE;

---------- emp assignmnet dff update ---

lc_c_assg c_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_sup_person_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_no_managers_warning BOOLEAN;

l_other_manager_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);

error_desc VARCHAR2 (240);

lv_assg_flag CHAR (1);

l_job_id VARCHAR2 (20);

l_position_id VARCHAR2 (20);

l_payroll_id NUMBER;

l_pay_basis_id NUMBER;

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_comment_id NUMBER;

l_pf_flag CHAR (1);

BEGIN

OPEN c_emp_assg;

LOOP

FETCH c_emp_assg

INTO lc_c_emp_assg;

EXIT WHEN c_emp_assg%NOTFOUND;

BEGIN

-- GET 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 = lc_c_emp_assg.job_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_job_id := NULL;

END;

-- get 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.NAME = lc_c_emp_assg.position_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_position_id := NULL;

END;

-- Get 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 pg.NAME = lc_c_emp_assg.grade_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_grade_id := NULL;

END;

-- Get Organization ID --

BEGIN

SELECT organization_id

INTO l_org_id

FROM hr_all_organization_units

WHERE UPPER (NAME) LIKE

UPPER (lc_c_emp_assg.organization_name);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_org_id := NULL;

END;

-- Get Location ID --

BEGIN

SELECT location_id

INTO l_loc_id

FROM hr_locations_all

WHERE UPPER (location_code) LIKE

UPPER (lc_c_emp_assg.locations);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_loc_id := NULL;

END;

-- Get emp category --

SELECT lookup_code

INTO l_assg_cat

FROM fnd_lookup_values_vl

WHERE lookup_type = 'EMP_CAT'

AND UPPER (lookup_code) LIKE

UPPER (lc_c_emp_assg.assg_category);

--- Get pay basis ---

BEGIN

SELECT pay_basis_id

INTO l_pay_basis_id

FROM per_pay_bases

WHERE 1 = 1

AND UPPER (NAME) LIKE UPPER (lc_c_emp_assg.salary_basis);

EXCEPTION

WHEN OTHERS

THEN

l_pay_basis_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 (lc_c_emp_assg.payroll_name)

AND TRUNC (SYSDATE) BETWEEN effective_start_date

AND effective_end_date;

EXCEPTION

WHEN OTHERS

THEN

l_payroll_id := NULL;

END;

-- Object Version Number --

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.person_id = lc_c_emp_assg.person_id;

IF lc_c_emp_assg.current_assign_start_date IS NOT NULL

THEN

l_assg_start_date := lc_c_emp_assg.current_assign_start_date;

END IF;

--- Suppervisor ---

SELECT paaf.assignment_id, papf.person_id

INTO l_supervisor_id, l_sup_person_id

FROM per_all_people_f papf, per_all_assignments_f paaf

WHERE papf.person_id = paaf.person_id

AND SYSDATE BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND SYSDATE BETWEEN papf.effective_start_date

AND papf.effective_end_date

AND papf.employee_number = lc_c_emp_assg.supervisor_empno;

hr_assignment_api.update_emp_asg_criteria

(p_validate => FALSE,

p_effective_date => lc_c_emp_assg.hire_date,

p_datetrack_update_mode => 'CORRECTION',

p_assignment_id => lc_c_emp_assg.assignment_id,

p_job_id => l_job_id,

p_position_id => l_position_id,

p_grade_id => l_grade_id,

p_location_id => l_loc_id,

p_organization_id => l_org_id,

p_employment_category => l_assg_cat,

p_payroll_id => l_payroll_id,

p_pay_basis_id => l_pay_basis_id,

p_supervisor_assignment_id => l_supervisor_id,

p_object_version_number => l_object_version_number,

p_segment1 => lc_c_emp_assg.group_segment1,

p_segment2 => lc_c_emp_assg.group_segment2,

p_segment3 => lc_c_emp_assg.group_segment3,

p_segment4 => lc_c_emp_assg.group_segment4,

p_segment5 => lc_c_emp_assg.group_segment5,

p_segment6 => lc_c_emp_assg.group_segment6,

p_effective_start_date => l_effective_start_date,

--- out parameter

p_effective_end_date => l_effective_end_date,

p_special_ceiling_step_id => l_special_ceiling_step_id,

p_people_group_id => l_people_group_id,

p_group_name => l_group_name,

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

);

IF l_people_group_id IS NOT NULL

THEN

UPDATE ifl_hr_employee he

SET assg_process_flag = 'Y'

WHERE emp_no = lc_c_emp_assg.emp_no;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE ifl_hr_employee he

SET assg_error_description = error_desc,

assg_process_flag = 'N'

WHERE emp_no = lc_c_emp_assg.emp_no;

END IF;

END;

DBMS_OUTPUT.put_line ( 'Assignment updated without any Warning'

|| lc_c_emp_assg.assignment_id

);

l_object_version_number := '';

l_special_ceiling_step_id := '';

l_people_group_id := '';

l_soft_coding_keyflex_id := '';

l_group_name := '';

l_effective_start_date := '';

l_effective_end_date := '';

l_org_now_no_manager_warning := FALSE;

l_other_manager_warning := FALSE;

l_spp_delete_warning := FALSE;

l_entries_changed_warning := '';

l_tax_district_changed_warning := FALSE;

l_concatenated_segments := '';

l_gsp_post_process_warning := '';

error_desc := '';

lv_assg_flag := '';

l_assg_cat := '';

l_job_id := '';

l_position_id := '';

l_grade_id := '';

l_org_id := '';

COMMIT;

END LOOP;

CLOSE c_emp_assg;

OPEN c_assg;

LOOP

FETCH c_assg

INTO lc_c_assg;

EXIT WHEN c_assg%NOTFOUND;

BEGIN

hr_assignment_api.update_emp_asg

(p_validate => FALSE,

p_effective_date => lc_c_assg.current_assign_start_date,

p_datetrack_update_mode => 'CORRECTION',

p_assignment_id => lc_c_assg.assignment_id,

p_object_version_number => lc_c_assg.object_version_number,

p_ass_attribute7 => lc_c_assg.rfid_card_no,

p_ass_attribute1 => lc_c_assg.eobi_member,

p_ass_attribute2 => lc_c_assg.eobi_no,

p_ass_attribute3 => lc_c_assg.eobi_effective_date,

p_ass_attribute4 => lc_c_assg.sessi_pessi_member,

p_ass_attribute5 => lc_c_assg.sessi_pessi_no,

p_ass_attribute6 => lc_c_assg.sessi_pessi_effective_date,

p_concatenated_segments => l_concatenated_segments,

--------- out parameters

p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,

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

);

COMMIT;

DBMS_OUTPUT.put_line ( lc_c_assg.assignment_id

|| ' HAS BEEN UPDATED !!!!'

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ( lc_c_assg.assignment_id

|| ' HAS FAILED !!!! '

|| SQLERRM

);

END;

END LOOP;

CLOSE c_assg;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

PROCEDURE update_hr_positions

CURSOR c

SELECT hp.position_org_name, psf.position_id,

psf.object_version_number, psf.effective_start_date,

pg.grade_id, hp.grades, psf.position_definition_id

FROM ifl_hr_positions hp, hr_all_positions_f psf, per_grades pg

WHERE psf.NAME = hp.position_org_name AND pg.NAME = hp.grades;

lc_c c%ROWTYPE;

p_position_definition_id NUMBER;

p_name VARCHAR2 (500);

p_valid_grades_changed_warning BOOLEAN;

p_effective_start_date DATE;

p_effective_end_date DATE;

BEGIN

OPEN c;

LOOP

FETCH c

INTO lc_c;

EXIT WHEN c%NOTFOUND;

BEGIN

hr_position_api.update_position

(p_validate => FALSE,

p_position_id => lc_c.position_id,

p_effective_start_date => p_effective_start_date,

p_effective_end_date => p_effective_end_date,

p_object_version_number => lc_c.object_version_number,

-- p_entry_grade_id => lc_c.grade_id,

p_effective_date => lc_c.effective_start_date,

p_datetrack_mode => 'CORRECTION',

p_position_definition_id => lc_c.position_definition_id,

p_name => p_name,

p_valid_grades_changed_warning => p_valid_grades_changed_warning

);

COMMIT;

DBMS_OUTPUT.put_line ('update positons : ' || p_name);

END;

COMMIT;

END LOOP;

CLOSE c;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

PROCEDURE update_hr_employee

CURSOR c_emp

SELECT emp_no, he.last_name, he.middle_names, he.first_name,

he.title, father_name, person_type, hire_date,

he.date_of_birth, he.town_of_birth, he.country_of_birth,

he.nationality, place_of_birth, nic, gender,

he.marital_status, office_email, eam_organization,

eam_section, rate_per_hours, eam_status, reason, designation,

vehicle_number, hcm_employee_no, cnic_expiry_date,

old_cnic_no, skill_level, ntn_no, wedding_date, religon,

sect, casts, blood_group, family_code, insurance_no,

black_listed, emp_reference, passport_no,

additional_person_detail, he.person_id,

ppx.object_version_number

FROM ifl_hr_employee he, per_people_x ppx

WHERE he.person_id IS NOT NULL

AND ppx.person_id = he.person_id

AND NVL (he.process_flag, 'N') <> 'Y';

lc_c_emp c_emp%ROWTYPE;

-- Local Variables

-- -----------------------

ln_object_version_number per_all_people_f.object_version_number%TYPE

:= 7;

lc_dt_ud_mode VARCHAR2 (100) := NULL;

ln_assignment_id per_all_assignments_f.assignment_id%TYPE

:= 33564;

lc_employee_number per_all_people_f.employee_number%TYPE

:= 'PRAJ_01';

l_country_code VARCHAR2 (30);

l_marital_status VARCHAR2 (30);

l_nationality VARCHAR2 (30);

error_desc VARCHAR2 (500);

-- Out Variables for Find Date Track Mode API

-- ----------------------------------------------------------------

lb_correction BOOLEAN;

lb_update BOOLEAN;

lb_update_override BOOLEAN;

lb_update_change_insert BOOLEAN;

-- Out Variables for Update Employee API

-- -----------------------------------------------------------

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;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp

INTO lc_c_emp;

EXIT WHEN c_emp%NOTFOUND;

/* -- Find Date Track Mode

-- --------------------------------

dt_api.find_dt_upd_modes

( -- Input Data Elements

-- ------------------------------

p_effective_date => TO_DATE('29-JUN-2011'),

p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',

p_base_key_column => 'ASSIGNMENT_ID',

p_base_key_value => ln_assignment_id,

-- Output data elements

-- -------------------------------

p_correction => lb_correction,

p_update => lb_update,

p_update_override => lb_update_override,

p_update_change_insert => lb_update_change_insert

);

IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )

THEN

-- UPDATE_OVERRIDE

-- ---------------------------------

lc_dt_ud_mode := 'UPDATE_OVERRIDE';

END IF;

IF ( lb_correction = TRUE )

THEN

-- CORRECTION

-- ----------------------

lc_dt_ud_mode := 'CORRECTION';

END IF;

IF ( lb_update = TRUE )

THEN

-- UPDATE

-- --------------

lc_dt_ud_mode := 'UPDATE';

END IF; */

-- Update Employee API

-- ---------------------------------

BEGIN

----------------------

-- Country Lookup Value

----------------------

SELECT lookup_code

INTO l_country_code

FROM fnd_lookups

WHERE lookup_type = 'JEES_EURO_COUNTRY_CODES'

AND UPPER (meaning) LIKE

UPPER (TRIM (lc_c_emp.country_of_birth));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Country Lookup error';

END;

IF lc_c_emp.marital_status IS NOT NULL

THEN

BEGIN

----------------------

-- Marital Status Lookup Value

----------------------

SELECT lookup_code

INTO l_marital_status

FROM hr_lookups

WHERE lookup_type = 'MAR_STATUS'

AND UPPER (meaning) =

UPPER (TRIM (lc_c_emp.marital_status));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Marital Lookup error';

END;

END IF;

BEGIN

----------------------

-- Nationality Lookup Value

----------------------

SELECT lookup_code

INTO l_nationality

FROM hr_lookups

WHERE lookup_type = 'NATIONALITY'

AND UPPER (meaning) = UPPER (TRIM (lc_c_emp.nationality));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

error_desc := error_desc || 'Nationality Lookup error';

END;

hr_person_api.update_person

( -- Input Data Elements

-- ------------------------------

p_effective_date => lc_c_emp.hire_date,

p_datetrack_update_mode => 'CORRECTION',

p_person_id => lc_c_emp.person_id,

p_middle_names => lc_c_emp.middle_names,

p_first_name => lc_c_emp.first_name,

p_suffix => lc_c_emp.father_name,

p_national_identifier => lc_c_emp.nic,

p_date_of_birth => lc_c_emp.date_of_birth,

p_town_of_birth => lc_c_emp.town_of_birth,

p_country_of_birth => l_country_code,

p_marital_status => l_marital_status,

p_nationality => l_nationality,

p_region_of_birth => lc_c_emp.place_of_birth,

p_email_address => lc_c_emp.office_email,

p_attribute1 => lc_c_emp.eam_organization,

p_attribute2 => lc_c_emp.rate_per_hours,

---to_char(LC_C_EMP.NIC_ISSUE_DATE,'YYYY/MM/DD HH:MI:SS') ,

p_attribute3 => lc_c_emp.eam_section,

p_attribute4 => lc_c_emp.eam_status,

p_attribute5 => lc_c_emp.reason,

p_attribute10 => lc_c_emp.designation,

p_attribute11 => lc_c_emp.vehicle_number,

p_attribute13 => lc_c_emp.office_email,

p_attribute14 => lc_c_emp.hcm_employee_no,

p_attribute15 => lc_c_emp.old_cnic_no,

p_attribute16 => lc_c_emp.cnic_expiry_date,

p_attribute17 => lc_c_emp.skill_level,

p_attribute18 => lc_c_emp.ntn_no,

p_attribute19 => lc_c_emp.wedding_date,

p_attribute20 => lc_c_emp.religon,

p_attribute21 => lc_c_emp.sect,

p_attribute22 => lc_c_emp.casts,

p_attribute23 => lc_c_emp.blood_group,

p_attribute24 => lc_c_emp.family_code,

p_attribute25 => lc_c_emp.insurance_no,

p_attribute26 => lc_c_emp.black_listed,

p_attribute27 => lc_c_emp.emp_reference,

p_attribute28 => lc_c_emp.passport_no,

-- Output Data Elements --

p_employee_number => lc_employee_number,

p_object_version_number => lc_c_emp.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

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

-- ROLLBACK;

DBMS_OUTPUT.put_line (SQLERRM);

END;

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE delete_hr_hierarchy_element

CURSOR c

SELECT pose.org_structure_element_id, pose.object_version_number

FROM per_org_structure_elements pose

WHERE TRUNC (pose.creation_date) = '20-SEP-2016'

ORDER BY 1;

--and pose.ORG_STRUCTURE_ELEMENT_ID =1061;

lc_c c%ROWTYPE;

BEGIN

OPEN c;

LOOP

FETCH c

INTO lc_c;

EXIT WHEN c%NOTFOUND;

BEGIN

hr_hierarchy_element_api.delete_hierarchy_element

(p_validate => FALSE,

p_org_structure_element_id => lc_c.org_structure_element_id,

p_object_version_number => lc_c.object_version_number

);

COMMIT;

DBMS_OUTPUT.put_line

( 'Delete Hierarchy Structure Element ID : '

|| lc_c.org_structure_element_id

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

COMMIT;

END LOOP;

CLOSE c;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_emp_address

CURSOR c_emp_add

SELECT emp_no, emp_name, address, city, distance, district,

province, country, postal_code, police_station,

effective_date, ea.address_type, ea.primary_flag,

ppx.object_version_number, ppx.person_id, mailing_address,

local_flag, house_owner, living_type, living_status

FROM ifl_employee_address ea, per_people_x ppx

WHERE NVL (ea.process_flag, 'N') <> 'Y'

AND ppx.employee_number = ea.emp_no

ORDER BY ppx.person_id;

lc_c_emp_add c_emp_add%ROWTYPE;

l_address_id NUMBER;

l_object_version_number NUMBER;

error_desc VARCHAR2 (240);

lv_add_flag VARCHAR2 (1);

l_city VARCHAR2 (20);

l_country VARCHAR2 (30);

l_type VARCHAR2 (20);

l_person_id NUMBER;

l_flag VARCHAR2 (1);

BEGIN

OPEN c_emp_add;

LOOP

FETCH c_emp_add

INTO lc_c_emp_add;

EXIT WHEN c_emp_add%NOTFOUND;

BEGIN

hr_person_address_api.create_person_address

(p_validate => FALSE,

p_effective_date => lc_c_emp_add.effective_date,

p_person_id => lc_c_emp_add.person_id,

p_date_from => lc_c_emp_add.effective_date,

p_address_line1 => lc_c_emp_add.address,

p_town_or_city => lc_c_emp_add.city,

p_address_line3 => lc_c_emp_add.distance,

p_region_1 => lc_c_emp_add.district,

p_region_2 => lc_c_emp_add.province,

p_country => lc_c_emp_add.country,

p_postal_code => lc_c_emp_add.postal_code,

p_address_line2 => lc_c_emp_add.police_station,

p_primary_flag => lc_c_emp_add.primary_flag,

p_address_type => lc_c_emp_add.address_type,

p_style => 'PK',

p_addr_attribute1 => lc_c_emp_add.mailing_address,

p_addr_attribute2 => lc_c_emp_add.local_flag,

p_addr_attribute3 => lc_c_emp_add.house_owner,

p_addr_attribute4 => lc_c_emp_add.living_type,

p_addr_attribute5 => lc_c_emp_add.living_status,

--- out parametes

p_address_id => l_address_id,

p_object_version_number => l_object_version_number

);

IF l_address_id IS NOT NULL

THEN

lv_add_flag := 'Y';

error_desc := 'No Error';

END IF;

IF lv_add_flag = 'Y'

THEN

UPDATE ifl_employee_address

SET error_description = error_desc,

process_flag = lv_add_flag

WHERE TRIM (emp_no) = lc_c_emp_add.emp_no;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE ifl_employee_address

SET error_description = error_desc,

process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_emp_add.emp_no;

END IF;

DBMS_OUTPUT.put_line ('Address ID : ' || l_address_id);

EXCEPTION

WHEN OTHERS

THEN

lv_add_flag := 'N';

error_desc := error_desc || SQLERRM;

UPDATE ifl_employee_address

SET error_description = error_desc,

process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_emp_add.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

DBMS_OUTPUT.put_line ('Address ID : ' || l_address_id);

lv_add_flag := '';

l_address_id := '';

l_object_version_number := '';

error_desc := '';

COMMIT;

END LOOP;

CLOSE c_emp_add;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_emp_contact

CURSOR c_contact

SELECT emp_no, ec.full_name, contact_type, emergency_contact,

primary_contact, effective_date, cont_first_name,

cont_last_name, gender, cnic, ec.date_of_birth, relationship,

dependent, benificiary, mailing_address, phone_type,

phone_no, email, percentage, occupation, education,

date_of_death, remarks, ppx.person_id,

contact_attribute_category

FROM ifl_emp_contact_info ec, per_people_x ppx

WHERE NVL (cont_process_flag, 'N') = 'N'

AND ec.emp_no = ppx.employee_number;

lc_c_contact c_contact%ROWTYPE;

l_contact_id NUMBER;

l_person_id NUMBER;

l_contact_type VARCHAR2 (240);

l_sex_type VARCHAR2 (240);

l_title VARCHAR2 (240);

l_effective_start_date DATE;

l_effective_end_date DATE;

l_object_version_number NUMBER;

error_desc VARCHAR2 (240);

lv_contact_flag VARCHAR2 (1);

l_seq VARCHAR2 (20);

l_personal_flag VARCHAR2 (1);

------------------------------------------------------

l_contact_relationship_id NUMBER;

l_ctr_object_version_number NUMBER;

l_per_person_id NUMBER;

l_per_object_version_number NUMBER;

l_per_effective_start_date DATE;

l_per_effective_end_date DATE;

l_full_name VARCHAR2 (20);

l_per_comment_id NUMBER;

l_name_combination_warning BOOLEAN;

l_orig_hire_warning BOOLEAN;

------------------------------------------------------

BEGIN

OPEN c_contact;

LOOP

FETCH c_contact

INTO lc_c_contact;

EXIT WHEN c_contact%NOTFOUND;

BEGIN

-- Get Relationship Lookup Code

SELECT lookup_code

INTO l_contact_type

FROM hr_lookups

WHERE lookup_type = 'CONTACT'

AND UPPER (meaning) = UPPER (TRIM (lc_c_contact.relationship));

-- Get SEX Lookup Code

SELECT lookup_code

INTO l_sex_type

FROM hr_lookups

WHERE lookup_type = 'SEX'

AND UPPER (lookup_code) LIKE UPPER (TRIM (lc_c_contact.gender));

hr_contact_rel_api.create_contact

(p_validate => FALSE,

p_start_date => lc_c_contact.effective_date,

p_date_start => lc_c_contact.effective_date,

p_business_group_id => 0,

p_person_id => lc_c_contact.person_id,

p_contact_type => l_contact_type,

p_first_name => lc_c_contact.cont_first_name,

p_last_name => lc_c_contact.cont_last_name,

p_sex => l_sex_type,

p_national_identifier => lc_c_contact.cnic,

p_dependent_flag => lc_c_contact.dependent,

p_date_of_birth => lc_c_contact.date_of_birth,

p_person_type_id => 13,

p_beneficiary_flag => lc_c_contact.benificiary,

p_cont_attribute_category => 'Family Details',

-- Family Details

p_cont_attribute1 => lc_c_contact.occupation,

p_cont_attribute10 => lc_c_contact.education,

p_cont_attribute11 => lc_c_contact.date_of_death,

p_cont_attribute12 => lc_c_contact.remarks,

-- p_cont_attribute_category => 'Nominee Details', -- Nominee Details

-- p_cont_attribute1 => lc_c_contact.email

-- p_cont_attribute10 => lc_c_contact.percentage

--- out parametrs

p_contact_relationship_id => l_contact_relationship_id,

p_ctr_object_version_number => l_ctr_object_version_number,

p_per_person_id => l_per_person_id,

p_per_object_version_number => l_per_object_version_number,

p_per_effective_start_date => l_per_effective_start_date,

p_per_effective_end_date => l_per_effective_end_date,

p_full_name => l_full_name,

p_per_comment_id => l_per_comment_id,

p_name_combination_warning => l_name_combination_warning,

p_orig_hire_warning => l_orig_hire_warning

);

IF l_contact_relationship_id IS NOT NULL

THEN

lv_contact_flag := 'Y';

error_desc := 'No Error';

END IF;

IF lv_contact_flag = 'Y'

THEN

UPDATE ifl_emp_contact_info

SET cont_error_description = error_desc,

cont_process_flag = lv_contact_flag

WHERE TRIM (emp_no) = lc_c_contact.emp_no;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE ifl_emp_contact_info

SET cont_error_description = error_desc,

cont_process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_contact.emp_no;

END IF;

DBMS_OUTPUT.put_line ('Contact ID : ' || l_contact_id);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

l_contact_id := '';

l_object_version_number := '';

l_per_effective_start_date := '';

l_per_effective_end_date := '';

error_desc := '';

lv_contact_flag := '';

COMMIT;

END LOOP;

CLOSE c_contact;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_emp_phone

CURSOR c_emp_ph

SELECT emp_no, phone_number, TYPE, effective_date, pap.person_id

FROM ifl_emp_phone ep, per_people_x pap

WHERE NVL (process_flag, 'N') <> 'Y'

AND pap.employee_number = ep.emp_no;

lc_c_emp_ph c_emp_ph%ROWTYPE;

l_phone_id NUMBER;

l_object_version_number NUMBER;

error_desc VARCHAR2 (240);

lv_ph_flag CHAR (1);

l_city VARCHAR2 (20);

l_country VARCHAR2 (30);

l_type VARCHAR2 (20);

l_person_id NUMBER;

BEGIN

OPEN c_emp_ph;

LOOP

FETCH c_emp_ph

INTO lc_c_emp_ph;

EXIT WHEN c_emp_ph%NOTFOUND;

BEGIN

---------------------------

--- Type Code

---------------------------

SELECT lookup_code

INTO l_type

FROM hr_lookups

WHERE lookup_type = 'PHONE_TYPE'

AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_emp_ph.TYPE));

hr_phone_api.create_phone

(p_validate => FALSE,

p_phone_type => l_type,

p_phone_number => lc_c_emp_ph.phone_number,

p_date_from => lc_c_emp_ph.effective_date,

p_effective_date => lc_c_emp_ph.effective_date,

p_parent_id => lc_c_emp_ph.person_id,

p_parent_table => 'PER_ALL_PEOPLE_F',

p_phone_id => l_phone_id,

p_object_version_number => l_object_version_number

);

IF l_phone_id IS NOT NULL

THEN

lv_ph_flag := 'Y';

error_desc := 'No Error';

END IF;

IF lv_ph_flag = 'Y'

THEN

UPDATE ifl_emp_phone

SET error_description = error_desc,

process_flag = lv_ph_flag,

phone_id = l_phone_id,

obj_ver_number = l_object_version_number

WHERE TRIM (emp_no) = lc_c_emp_ph.emp_no;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE ifl_emp_phone

SET error_description = error_desc,

process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_emp_ph.emp_no;

END IF;

DBMS_OUTPUT.put_line ('Phone ID : ' || l_phone_id);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

lv_ph_flag := '';

l_phone_id := '';

l_object_version_number := '';

error_desc := '';

COMMIT;

END LOOP;

CLOSE c_emp_ph;

DBMS_OUTPUT.put_line ('Phone ID : ' || l_phone_id);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_exemployer_info

CURSOR c_exemp_info

SELECT emp_no, emp_name, ex_employer_name, title,

ex_employer_address, country, employee_type, job_name,

employee_category, job_start_date, job_end_date,

joining_designation, leaving_designation, joining_salary,

leaving_salary, reason_leaving, salary_period,

verification_status, verification_remarks, start_date,

end_date, employer_process_flag,

employer_error_description, previous_employer_id,

job_process_flag, job_error_description, previous_job_id,

extra_info_process_flag, extra_info_error_description,

previous_job_extra_info_id

FROM ifl_previous_employment pe

WHERE NVL (employer_process_flag, 'N') <> 'Y'

ORDER BY emp_no, start_date;

lc_c_exemp_info c_exemp_info%ROWTYPE;

l_previous_employer_id NUMBER;

l_object_version_number NUMBER;

l_previous_job_id NUMBER;

l_prev_job_obj_ver_number NUMBER;

error_desc_exemp VARCHAR2 (240);

error_desc_exjob VARCHAR2 (240);

lv_exemp_flag CHAR (1);

lv_job_flag CHAR (1);

l_effective_date DATE;

l_person_id NUMBER;

l_country_code VARCHAR2 (5);

l_category_code VARCHAR2 (50);

l_employer_type VARCHAR2 (50);

BEGIN

OPEN c_exemp_info;

LOOP

FETCH c_exemp_info

INTO lc_c_exemp_info;

EXIT WHEN c_exemp_info%NOTFOUND;

BEGIN

----------------------

-- Country Lookup Value

----------------------

BEGIN

SELECT lookup_code

INTO l_country_code

FROM fnd_lookups

WHERE lookup_type = 'JEES_EURO_COUNTRY_CODES'

AND UPPER (meaning) = UPPER (TRIM (lc_c_exemp_info.country));

EXCEPTION

WHEN OTHERS

THEN

l_country_code := '';

END;

---------------------------------

-- Employee Category Lookup Value

---------------------------------

--no need in my case

/* BEGIN

SELECT lookup_code

INTO l_category_code

FROM fnd_lookup_values_vl

WHERE lookup_type = 'EMPLOYEE_CATG'

AND UPPER (meaning) =

UPPER (TRIM (lc_c_exemp_info.employee_category));

EXCEPTION

WHEN OTHERS

THEN

l_category_code := '';

END; */

---------------------------------

-- Previous Employer Type Lookup Value

---------------------------------

BEGIN

SELECT lookup_code

INTO l_employer_type

FROM fnd_lookup_values_vl

WHERE lookup_type = 'PREV_EMP_TYPE'

AND UPPER (meaning) =

UPPER (TRIM (lc_c_exemp_info.employee_type));

EXCEPTION

WHEN OTHERS

THEN

l_employer_type := '';

END;

----------------------

-- Effective Date

----------------------

SELECT papf.effective_start_date, papf.person_id

INTO l_effective_date, l_person_id

FROM per_all_people_f papf

WHERE papf.employee_number = lc_c_exemp_info.emp_no;

hr_previous_employment_api.create_previous_employer

(p_validate => FALSE,

p_business_group_id => 0,

p_effective_date => l_effective_date,

p_person_id => l_person_id,

p_start_date => lc_c_exemp_info.start_date,

p_end_date => lc_c_exemp_info.end_date,

p_employer_name => lc_c_exemp_info.ex_employer_name,

p_employer_address => lc_c_exemp_info.ex_employer_address,

p_employer_country => l_country_code,

p_pem_attribute1 => lc_c_exemp_info.joining_designation,

p_pem_attribute10 => lc_c_exemp_info.joining_salary,

p_pem_attribute11 => lc_c_exemp_info.salary_period,

p_pem_attribute12 => lc_c_exemp_info.leaving_designation,

p_pem_attribute13 => lc_c_exemp_info.leaving_salary,

p_pem_attribute14 => lc_c_exemp_info.reason_leaving,

p_pem_attribute15 => lc_c_exemp_info.verification_remarks,

p_pem_attribute16 => lc_c_exemp_info.verification_status,

p_employer_type => l_employer_type,

p_previous_employer_id => l_previous_employer_id,

p_object_version_number => l_object_version_number

);

IF l_previous_employer_id IS NOT NULL

THEN

lv_exemp_flag := 'Y';

error_desc_exemp := 'No Error';

END IF;

IF l_previous_employer_id IS NOT NULL

THEN

BEGIN

hr_previous_employment_api.create_previous_job

(p_validate => FALSE,

p_effective_date => l_effective_date,

p_previous_employer_id => l_previous_employer_id,

p_start_date => lc_c_exemp_info.job_start_date,

p_end_date => lc_c_exemp_info.job_end_date,

p_job_name => lc_c_exemp_info.job_name,

-- p_description => lc_c_exemp_info.designation,

-- p_employment_category => l_category_code,

-- p_pjo_attribute4 => lc_c_exemp_info.REFERENCE,

-- p_pjo_attribute5 => lc_c_exemp_info.TELEPHONE,

-- p_pjo_attribute6 => lc_c_exemp_info.EMAIL,

p_previous_job_id => l_previous_job_id,

p_object_version_number => l_prev_job_obj_ver_number

);

IF l_previous_job_id IS NOT NULL

THEN

lv_job_flag := 'Y';

error_desc_exjob := 'No Error';

END IF;

EXCEPTION

WHEN OTHERS

THEN

error_desc_exjob := error_desc_exjob || SQLERRM;

lv_job_flag := 'N';

UPDATE ifl_previous_employment

SET job_error_description = error_desc_exjob,

job_process_flag = lv_job_flag

WHERE TRIM (emp_no) = lc_c_exemp_info.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

END IF;

IF lv_exemp_flag = 'Y'

THEN

UPDATE ifl_previous_employment

SET employer_error_description = error_desc_exemp,

employer_process_flag = lv_exemp_flag,

job_error_description = error_desc_exjob,

job_process_flag = DECODE (lv_job_flag, 'N', 'N', 'Y'),

previous_employer_id = l_previous_employer_id,

previous_job_id = l_previous_job_id

WHERE TRIM (emp_no) = lc_c_exemp_info.emp_no;

ELSE

error_desc_exjob := error_desc_exjob || SQLERRM;

UPDATE ifl_previous_employment

SET employer_error_description = error_desc_exemp,

employer_process_flag = 'N',

job_error_description = error_desc_exjob,

job_process_flag = DECODE (lv_job_flag, 'N', 'N', 'Y')

WHERE TRIM (emp_no) = lc_c_exemp_info.emp_no;

END IF;

EXCEPTION

WHEN OTHERS

THEN

lv_exemp_flag := 'N';

error_desc_exjob := error_desc_exjob || SQLERRM;

UPDATE ifl_previous_employment

SET employer_error_description = error_desc_exemp,

employer_process_flag = lv_exemp_flag,

job_process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_exemp_info.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

DBMS_OUTPUT.put_line ( 'Previous Employer ID : '

|| l_previous_employer_id

);

DBMS_OUTPUT.put_line ('Previous Job ID : '

|| l_previous_job_id

);

DBMS_OUTPUT.put_line ('Person_id = :' || l_person_id);

DBMS_OUTPUT.put_line ('Date = :' || l_effective_date);

DBMS_OUTPUT.put_line ('CC = :' || l_country_code);

-- DBMS_OUTPUT.put_line ( 'employer type = :'

-- || lc_c_exemp_info.employer_type

-- );

DBMS_OUTPUT.put_line ( 'add = :'

|| lc_c_exemp_info.ex_employer_name

);

lv_exemp_flag := '';

lv_job_flag := '';

error_desc_exemp := '';

error_desc_exjob := '';

l_effective_date := '';

l_person_id := '';

l_previous_employer_id := '';

l_object_version_number := '';

l_previous_job_id := '';

l_prev_job_obj_ver_number := '';

COMMIT;

END LOOP;

CLOSE c_exemp_info;

END;

PROCEDURE create_qualification

CURSOR c_emp_qual

SELECT emp_no, emp_name, qual_type, qual_title, status,

grade_attained, establishment, start_date, end_date,

awarded_date, high_qualf, education_type, marks_obtained,

total_marks, gpa, sponsored_by, post_employment, city,

verified_by, verification_by, verified_name,

verification_from, verification_remarks, subject,

subject_status, sub_start_date, sub_end_date, major, grade,

sub_marks, awarding_body, sub_total_marks

FROM ifl_emp_qualification eq

ORDER BY emp_no, start_date;

CURSOR c_emp_estab

SELECT DISTINCT emp_no, awarding_body, NULL city, start_date,

end_date, establishment, eq.awarded_date,

estab_process_flag, estab_error_description

FROM ifl_emp_qualification eq

ORDER BY emp_no, start_date;

lc_c_emp_qual c_emp_qual%ROWTYPE;

lc_c_emp_estab c_emp_estab%ROWTYPE;

l_attendance_id NUMBER;

l_attendace_obj_vno NUMBER;

l_att_effective_date DATE;

l_person_id NUMBER;

lv_estab_flag CHAR (1);

error_desc_estab VARCHAR2 (240);

l_qualification_id NUMBER;

l_object_version_number NUMBER;

l_subjects_taken_id NUMBER;

l_sub_obj_ver_number NUMBER;

error_desc_qual VARCHAR2 (240);

error_desc_sub VARCHAR2 (240);

lv_qual_flag CHAR (1);

lv_sub_flag CHAR (1);

l_effective_date DATE;

l_sub VARCHAR2 (240);

l_sub_status VARCHAR2 (240);

l_squal_status VARCHAR2 (240);

l_qual_type_id NUMBER;

BEGIN

OPEN c_emp_estab;

LOOP

FETCH c_emp_estab

INTO lc_c_emp_estab;

EXIT WHEN c_emp_estab%NOTFOUND;

BEGIN

----------------------

-- Effective Date and Person ID

----------------------

SELECT papf.effective_start_date, papf.person_id

INTO l_att_effective_date, l_person_id

FROM per_all_people_f papf

WHERE papf.employee_number = lc_c_emp_estab.emp_no

AND SYSDATE BETWEEN papf.effective_start_date

AND papf.effective_end_date;

-- AND TO_CHAR (papf.effective_end_date, 'dd/mm/yyyy') = '31/12/4712';

per_estab_attendances_api.create_attended_estab

(p_validate => FALSE,

p_effective_date => l_att_effective_date,

p_fulltime => 'N',

--p_attended_start_date => lc_c_emp_estab.AWARDED_DATE,

--p_attended_end_date =>lc_c_emp_estab.AWARDED_DATE,

p_establishment => lc_c_emp_estab.establishment,

--awarding_body,

p_address => lc_c_emp_estab.city,

p_business_group_id => 0,

p_person_id => l_person_id,

p_attendance_id => l_attendance_id,

p_object_version_number => l_attendace_obj_vno

);

IF l_attendance_id IS NOT NULL

THEN

lv_estab_flag := 'Y';

error_desc_estab := 'No Error';

END IF;

IF lv_estab_flag = 'Y'

THEN

UPDATE ifl_emp_qualification

SET estab_error_description = error_desc_estab,

estab_process_flag = lv_estab_flag,

--attendance_id = l_attendance_id,

estab_obj_ver_number = l_attendace_obj_vno

WHERE TRIM (emp_no) = lc_c_emp_estab.emp_no

AND awarding_body = lc_c_emp_estab.awarding_body

AND start_date = lc_c_emp_estab.start_date;

ELSE

error_desc_estab := error_desc_estab || SQLERRM;

UPDATE ifl_emp_qualification

SET estab_error_description = error_desc_estab,

estab_process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_emp_estab.emp_no

AND awarding_body = lc_c_emp_estab.awarding_body

AND start_date = lc_c_emp_estab.start_date;

END IF;

EXCEPTION

WHEN OTHERS

THEN

lv_estab_flag := 'N';

error_desc_estab := error_desc_estab || SQLERRM;

UPDATE ifl_emp_qualification

SET estab_error_description = error_desc_estab,

estab_process_flag = lv_estab_flag

WHERE TRIM (emp_no) = lc_c_emp_estab.emp_no

AND awarding_body = lc_c_emp_estab.awarding_body

AND start_date = lc_c_emp_estab.start_date;

DBMS_OUTPUT.put_line (SQLERRM);

END;

DBMS_OUTPUT.put_line ('Attendance ID : ' || l_attendance_id);

lv_estab_flag := '';

error_desc_estab := '';

l_att_effective_date := '';

l_person_id := '';

l_attendance_id := '';

l_attendace_obj_vno := '';

-- COMMIT;

END LOOP;

CLOSE c_emp_estab;

OPEN c_emp_qual;

LOOP

FETCH c_emp_qual

INTO lc_c_emp_qual;

EXIT WHEN c_emp_qual%NOTFOUND;

BEGIN

----------------------

-- Subject Lookup Value

----------------------

BEGIN

SELECT NVL (lookup_code, 'NONE')

INTO l_sub

FROM hr_lookups

WHERE lookup_type = 'PER_SUBJECTS'

AND UPPER (meaning) LIKE

UPPER (TRIM (lc_c_emp_qual.subject));

EXCEPTION

WHEN OTHERS

THEN

l_sub := '';

END;

----------------------

-- Subject Status Lookup Value

----------------------

SELECT lookup_code

INTO l_squal_status

FROM hr_lookups

WHERE lookup_type = 'PER_SUBJECT_STATUSES'

AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_emp_qual.status));

----------------------

-- Subject Status Lookup Value

----------------------

SELECT lookup_code

INTO l_sub_status

FROM hr_lookups

WHERE lookup_type = 'PER_SUBJECT_STATUSES'

AND UPPER (meaning) LIKE

UPPER (TRIM (lc_c_emp_qual.subject_status));

----------------------

-- Effective Date

----------------------

SELECT papf.effective_start_date, papf.person_id

INTO l_effective_date, l_person_id

FROM per_all_people_f papf

WHERE papf.employee_number = lc_c_emp_qual.emp_no

AND SYSDATE BETWEEN papf.effective_start_date

AND papf.effective_end_date;

-- AND TO_CHAR (papf.effective_end_date, 'dd/mm/yyyy') = '31/12/4712';

----------------------

-- Qualification Type ID

----------------------

SELECT pqt.qualification_type_id

INTO l_qual_type_id

FROM per_qualification_types pqt

WHERE UPPER (pqt.NAME) LIKE

UPPER (TRIM (lc_c_emp_qual.qual_type));

per_qualifications_api.create_qualification

(p_validate => FALSE,

p_business_group_id => 0,

p_effective_date => l_effective_date,

p_qualification_type_id => l_qual_type_id,

p_person_id => l_person_id,

p_title => lc_c_emp_qual.qual_title,

p_status => l_squal_status,

p_grade_attained => lc_c_emp_qual.grade_attained,

p_awarding_body => lc_c_emp_qual.awarding_body,

p_awarded_date => lc_c_emp_qual.awarded_date,

p_start_date => lc_c_emp_qual.start_date,

p_end_date => lc_c_emp_qual.start_date,

p_attribute2 => lc_c_emp_qual.total_marks,

p_attribute3 => lc_c_emp_qual.marks_obtained,

p_attribute4 => lc_c_emp_qual.grade_attained,

p_attribute5 => lc_c_emp_qual.gpa,

p_attribute6 => lc_c_emp_qual.high_qualf,

p_attribute7 => lc_c_emp_qual.education_type,

p_attribute8 => lc_c_emp_qual.sponsored_by,

p_attribute9 => lc_c_emp_qual.post_employment,

p_attribute10 => lc_c_emp_qual.city,

p_attribute11 => lc_c_emp_qual.verified_by,

p_attribute12 => lc_c_emp_qual.verification_from,

p_attribute13 => lc_c_emp_qual.verification_remarks,

p_attribute14 => lc_c_emp_qual.verification_by,

p_attribute15 => lc_c_emp_qual.verified_name,

-- out parameters

p_qualification_id => l_qualification_id,

p_object_version_number => l_object_version_number

);

IF l_qualification_id IS NOT NULL

THEN

lv_qual_flag := 'Y';

error_desc_qual := 'No Error';

END IF;

IF (l_qualification_id IS NOT NULL AND l_sub IS NOT NULL)

THEN

BEGIN

per_sub_ins.ins

(p_validate => FALSE,

p_effective_date => l_effective_date,

p_start_date => lc_c_emp_qual.sub_start_date,

p_end_date => lc_c_emp_qual.sub_end_date,

p_major => lc_c_emp_qual.major,

p_subject_status => l_sub_status,

p_subject => l_sub,

p_grade_attained => lc_c_emp_qual.grade,

-- out parameters

p_qualification_id => l_qualification_id,

p_subjects_taken_id => l_subjects_taken_id,

p_object_version_number => l_sub_obj_ver_number

);

IF l_subjects_taken_id IS NOT NULL

THEN

INSERT INTO per_subjects_taken_tl

(subjects_taken_id, LANGUAGE, source_lang,

grade_attained, created_by, creation_date,

last_updated_by, last_update_date,

last_update_login

)

VALUES (l_subjects_taken_id, 'US', 'US',

NULL, -1, SYSDATE,

-- lc_c_emp_qual.grade, -1, SYSDATE,

-1, SYSDATE,

-1

);

lv_sub_flag := 'Y';

error_desc_sub := 'No Error';

END IF;

EXCEPTION

WHEN OTHERS

THEN

error_desc_sub := error_desc_sub || SQLERRM;

lv_sub_flag := 'N';

UPDATE ifl_emp_qualification

SET sub_error_description = error_desc_sub,

sub_process_flag = lv_sub_flag

WHERE TRIM (emp_no) = lc_c_emp_qual.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

END IF;

IF lv_qual_flag = 'Y'

THEN

UPDATE ifl_emp_qualification

SET qual_error_description = error_desc_qual,

qual_process_flag = lv_qual_flag,

sub_error_description = error_desc_sub,

sub_process_flag = DECODE (lv_sub_flag, 'N', 'N', 'Y'),

qualification_id = l_qualification_id,

qual_obj_ver_number = l_object_version_number,

subjects_taken_id = l_subjects_taken_id,

sub_obj_ver_number = l_sub_obj_ver_number

WHERE TRIM (emp_no) = lc_c_emp_qual.emp_no;

ELSE

error_desc_qual := error_desc_qual || SQLERRM;

UPDATE ifl_emp_qualification

SET qual_error_description = error_desc_qual,

qual_process_flag = 'N',

sub_error_description = error_desc_sub,

sub_process_flag = DECODE (lv_sub_flag, 'N', 'N', 'Y')

WHERE TRIM (emp_no) = lc_c_emp_qual.emp_no;

DBMS_OUTPUT.put_line

('Employee qualification is Created without any Warning');

END IF;

EXCEPTION

WHEN OTHERS

THEN

lv_qual_flag := 'N';

error_desc_qual := error_desc_qual || SQLERRM;

UPDATE ifl_emp_qualification

SET qual_error_description = error_desc_qual,

qual_process_flag = lv_qual_flag,

sub_process_flag = 'N'

WHERE TRIM (emp_no) = lc_c_emp_qual.emp_no;

DBMS_OUTPUT.put_line (SQLERRM);

END;

DBMS_OUTPUT.put_line ( 'Qualification ID : '

|| l_qualification_id

);

DBMS_OUTPUT.put_line ( 'Subject Taken ID : '

|| l_subjects_taken_id

);

lv_qual_flag := '';

lv_sub_flag := '';

error_desc_qual := '';

error_desc_sub := '';

l_effective_date := '';

l_person_id := '';

l_qual_type_id := '';

l_qualification_id := '';

l_object_version_number := '';

l_subjects_taken_id := '';

l_sub_obj_ver_number := '';

-- COMMIT;

END LOOP;

CLOSE c_emp_qual;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE create_emp_aai

CURSOR aai

SELECT emp_no, effective_start_date, autogen_hours_yn,

rotation_plan_name, earning_policy_name, shift, rest_01,

rest_02

FROM ifl_emp_aai ea

WHERE NVL (ea.status_upload, 'N') = 'N';

c_aai aai%ROWTYPE;

p_created_by NUMBER := 1070;

p_creation_date DATE := SYSDATE;

p_last_updated_by NUMBER := 1070;

p_last_update_date DATE := SYSDATE;

p_last_update_login NUMBER := 0;

p_assignment_id NUMBER;

p_rotation NUMBER;

p_policy NUMBER;

aai_id NUMBER;

BEGIN

OPEN aai;

LOOP

FETCH aai

INTO c_aai;

EXIT WHEN aai%NOTFOUND;

BEGIN

----- get assignment id --

SELECT pax.assignment_id

INTO p_assignment_id

FROM per_people_x ppx, per_assignments_x pax

WHERE pax.person_id = ppx.person_id

AND ppx.employee_number = c_aai.emp_no;

------ get rotation plan id --

SELECT rp.ID

INTO p_rotation

FROM hxt_rotation_plans rp

WHERE rp.NAME = c_aai.rotation_plan_name;

------ get earning policy id --

SELECT ep.ID

INTO p_policy

FROM hxt_earning_policies ep

WHERE ep.NAME = c_aai.earning_policy_name;

SELECT hxt_seqno.NEXTVAL

INTO aai_id

FROM DUAL;

hxt_gen_aai.create_otlr_add_assign_info

(p_id => aai_id,

p_effective_start_date => c_aai.effective_start_date,

p_assignment_id => p_assignment_id,

p_autogen_hours_yn => c_aai.autogen_hours_yn,

p_rotation_plan => p_rotation,

p_earning_policy => p_policy,

p_created_by => 1070,

p_creation_date => SYSDATE,

p_last_updated_by => 1070,

p_last_update_date => SYSDATE,

p_last_update_login => 0,

p_attribute1 => c_aai.shift,

p_attribute2 => c_aai.rest_01,

p_attribute3 => c_aai.rest_02

);

DBMS_OUTPUT.put_line ( 'Succesfully create :'

|| c_aai.emp_no

|| 'AAI ID'

|| aai_id

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

IF aai_id IS NOT NULL

THEN

UPDATE ifl_emp_aai ea

SET ea.status_upload = 'Y'

WHERE TRIM (ea.emp_no) = c_aai.emp_no;

ELSE

UPDATE ifl_emp_aai ea

SET ea.status_upload = 'N'

WHERE TRIM (ea.emp_no) = c_aai.emp_no;

END IF;

END;

COMMIT;

aai_id := NULL;

p_assignment_id := NULL;

p_rotation := NULL;

p_policy := NULL;

END LOOP;

CLOSE aai;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE update_hr_oranization

CURSOR c_ou

SELECT hou.organization_id, hou.object_version_number,

TRIM (ho.org_type) org_type, hou.date_from, hou.TYPE,

hou.NAME

FROM ifl_hr_organization ho, hr_organization_units hou

WHERE ho.org_name = hou.NAME;

cc_ou c_ou%ROWTYPE;

p_object_version_number NUMBER;

p_duplicate_org_warning BOOLEAN;

p_end_org_warning BOOLEAN;

l_type VARCHAR2 (30);

BEGIN

OPEN c_ou;

LOOP

FETCH c_ou

INTO cc_ou;

EXIT WHEN c_ou%NOTFOUND;

BEGIN

-- get org type code -

SELECT lookup_code

INTO l_type

FROM hr_lookups

WHERE lookup_type = 'ORG_TYPE'

AND UPPER (meaning) LIKE UPPER (TRIM (cc_ou.org_type));

hr_organization_api.update_organization

(p_effective_date => cc_ou.date_from,

p_organization_id => cc_ou.organization_id,

p_type => l_type,

p_object_version_number => cc_ou.object_version_number,

p_duplicate_org_warning => p_duplicate_org_warning,

p_end_org_warning => p_end_org_warning

);

END;

COMMIT;

END LOOP;

CLOSE c_ou;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

PROCEDURE employee_create_adjustment

CURSOR c_adjust

SELECT TRIM (effective_date) effective_date, TRIM (emp_no) emp_no,

TRIM (consolidation_set) consolidation_set,

TRIM (element_name) element_name,

TRIM (input_name1) input_name1,

TRIM (input_value1) input_value1,

TRIM (input_name2) input_name2,

TRIM (input_value2) input_value2,

TRIM (input_name3) input_name3,

TRIM (input_value3) input_value3,

TRIM (input_name4) input_name4,

TRIM (input_value4) input_value4,

TRIM (input_name5) input_name5,

TRIM (input_value5) input_value5

FROM ifl_employee_adjustment ea

WHERE NVL (ea.status_upload, 'N') <> 'Y';

cc_adjust c_adjust%ROWTYPE;

--------- in parameters

p_assignment NUMBER;

elemnet_link_id NUMBER;

set_id NUMBER;

input_id1 NUMBER;

input_id2 NUMBER;

input_id3 NUMBER;

input_id4 NUMBER;

input_id5 NUMBER;

-------- out parameters

p_validate BOOLEAN;

p_element_entry_id NUMBER;

p_effective_start_date DATE;

p_effective_end_date DATE;

p_object_version_number NUMBER;

p_create_warning BOOLEAN;

BEGIN

OPEN c_adjust;

LOOP

FETCH c_adjust

INTO cc_adjust;

EXIT WHEN c_adjust%NOTFOUND;

BEGIN

-------- get CONSOLIDATION_SET_ID

SELECT pcs.consolidation_set_id

INTO set_id

FROM pay_consolidation_sets pcs

WHERE UPPER (pcs.consolidation_set_name) =

UPPER (cc_adjust.consolidation_set);

----- get assignment id

SELECT paf.assignment_id

INTO p_assignment

FROM per_people_x ppx, per_assignments_x paf

WHERE paf.person_id = ppx.person_id

AND ppx.employee_number = cc_adjust.emp_no;

----- get element link id

SELECT el.element_link_id

INTO elemnet_link_id

FROM pay_element_links_f el, pay_element_types_f et

WHERE el.element_type_id = et.element_type_id

AND et.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id1

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name1

AND ety.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id2

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name2

AND ety.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id3

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name3

AND ety.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id4

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name4

AND ety.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id5

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name5

AND ety.element_name = cc_adjust.element_name;

pay_balance_adjustment_api.create_adjustment

(p_validate => p_validate,

p_effective_date => cc_adjust.effective_date,

p_assignment_id => p_assignment,

p_consolidation_set_id => set_id,

p_element_link_id => elemnet_link_id,

p_input_value_id1 => input_id1,

p_input_value_id2 => input_id2,

p_input_value_id3 => input_id3,

p_input_value_id4 => input_id4,

p_input_value_id5 => input_id5,

p_entry_value1 => cc_adjust.input_value1,

p_entry_value2 => cc_adjust.input_value2,

p_entry_value3 => cc_adjust.input_value3,

p_entry_value4 => cc_adjust.input_value4,

p_entry_value5 => cc_adjust.input_value5,

/* p_input_value_id6 => number default null,

p_input_value_id7 => number default null,

p_input_value_id8 => number default null,

p_input_value_id9 => number default null,

p_input_value_id10 => number default null,

p_input_value_id11 => number default null,

p_input_value_id12 => number default null,

p_input_value_id13 => number default null,

p_input_value_id14 => number default null,

p_input_value_id15 => number default null,

p_entry_value1 => 1236,

p_entry_value2 => varchar2 default null,

p_entry_value3 => varchar2 default null,

p_entry_value4 => varchar2 default null,

p_entry_value5 => varchar2 default null,

p_entry_value6 => varchar2 default null,

p_entry_value7 => varchar2 default null,

p_entry_value8 => varchar2 default null,

p_entry_value9 => varchar2 default null,

p_entry_value10 => varchar2 default null,

p_entry_value11 => varchar2 default null,

p_entry_value12 => varchar2 default null,

p_entry_value13 => varchar2 default null,

p_entry_value14 => varchar2 default null,

p_entry_value15 => varchar2 default null,

p_prepay_flag => varchar2 default null,*/-- Costing information.

p_balance_adj_cost_flag => 'N',

/* p_cost_allocation_keyflex_id => number default null,

p_attribute_category => varchar2 default null,

p_attribute1 => varchar2 default null,

p_attribute2 => varchar2 default null,

p_attribute3 => varchar2 default null,

p_attribute4 => varchar2 default null,

p_attribute5 => varchar2 default null,

p_attribute6 => varchar2 default null,

p_attribute7 => varchar2 default null,

p_attribute8 => varchar2 default null,

p_attribute9 => varchar2 default null,

p_attribute10 => varchar2 default null,

p_attribute11 => varchar2 default null,

p_attribute12 => varchar2 default null,

p_attribute13 => varchar2 default null,

p_attribute14 => varchar2 default null,

p_attribute15 => varchar2 default null,

p_attribute16 => varchar2 default null,

p_attribute17 => varchar2 default null,

p_attribute18 => varchar2 default null,

p_attribute19 => varchar2 default null,

p_attribute20 => varchar2 default null,

p_run_type_id => number default null,

p_original_entry_id => number default null,*/-- Element entry information.

p_element_entry_id => p_element_entry_id,

p_effective_start_date => p_effective_start_date,

p_effective_end_date => p_effective_end_date,

p_object_version_number => p_object_version_number,

p_create_warning => p_create_warning

);

COMMIT;

DBMS_OUTPUT.put_line ( 'Element Entry ID : '

|| p_element_entry_id

|| ': '

|| p_effective_start_date

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Inner Exception' || SQLERRM);

END;

COMMIT;

END LOOP;

CLOSE c_adjust;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception' || SQLERRM);

END;

PROCEDURE employee_leave_adjustment

CURSOR c_adjust

SELECT TRIM (effective_date) effective_date, TRIM (emp_no) emp_no,

TRIM (consolidation_set) consolidation_set,

TRIM (element_name) element_name,

TRIM (input_name1) input_name1,

TRIM (input_name2) input_name2,

TRIM (input_value1) input_value1,

TRIM (input_value2) input_value2

FROM ifl_emp_leave_adjustment ea

WHERE NVL (ea.status_upload, 'N') <> 'Y';

cc_adjust c_adjust%ROWTYPE;

--------- in parameters

p_assignment NUMBER;

elemnet_link_id NUMBER;

set_id NUMBER;

input_id1 NUMBER;

input_id2 NUMBER;

input_id3 NUMBER;

input_id4 NUMBER;

input_id5 NUMBER;

-------- out parameters

p_validate BOOLEAN;

p_element_entry_id NUMBER;

p_effective_start_date DATE;

p_effective_end_date DATE;

p_object_version_number NUMBER;

p_create_warning BOOLEAN;

BEGIN

OPEN c_adjust;

LOOP

FETCH c_adjust

INTO cc_adjust;

EXIT WHEN c_adjust%NOTFOUND;

BEGIN

-------- get CONSOLIDATION_SET_ID

SELECT pcs.consolidation_set_id

INTO set_id

FROM pay_consolidation_sets pcs

WHERE UPPER (pcs.consolidation_set_name) =

UPPER (cc_adjust.consolidation_set);

----- get assignment id

SELECT paf.assignment_id

INTO p_assignment

FROM per_people_x ppx, per_assignments_x paf

WHERE paf.person_id = ppx.person_id

AND ppx.employee_number = cc_adjust.emp_no;

----- get element link id

SELECT el.element_link_id

INTO elemnet_link_id

FROM pay_element_links_f el, pay_element_types_f et

WHERE el.element_type_id = et.element_type_id

AND et.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id1

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name1

AND ety.element_name = cc_adjust.element_name;

--- get input id

SELECT piv.input_value_id

INTO input_id2

FROM pay_element_types_f ety, pay_input_values_f piv

WHERE piv.element_type_id = ety.element_type_id

AND piv.NAME = cc_adjust.input_name2

AND ety.element_name = cc_adjust.element_name;

pay_balance_adjustment_api.create_adjustment

(p_validate => p_validate,

p_effective_date => cc_adjust.effective_date,

p_assignment_id => p_assignment,

p_consolidation_set_id => set_id,

p_element_link_id => elemnet_link_id,

p_input_value_id1 => input_id1,

p_input_value_id2 => input_id2,

p_entry_value1 => cc_adjust.input_value1,

p_entry_value2 => cc_adjust.input_value2,

p_balance_adj_cost_flag => 'N',

-- Element entry information.

p_element_entry_id => p_element_entry_id,

p_effective_start_date => p_effective_start_date,

p_effective_end_date => p_effective_end_date,

p_object_version_number => p_object_version_number,

p_create_warning => p_create_warning

);

COMMIT;

DBMS_OUTPUT.put_line ( 'Element Entry ID : '

|| p_element_entry_id

|| ': '

|| p_effective_start_date

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Inner Exception' || SQLERRM);

END;

COMMIT;

END LOOP;

CLOSE c_adjust;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception' || SQLERRM);

END;

procedure hr_delete_positions is

CURSOR c

SELECT hp.position_org_name, psf.position_id,

psf.object_version_number, psf.effective_start_date,

psf.position_definition_id

FROM ifl_hr_positions hp, hr_all_positions_f psf

WHERE psf.NAME = hp.position_org_name ;

/*select *

from per_security_profiles*/

lc_c c%ROWTYPE;

p_position_definition_id NUMBER;

p_name VARCHAR2 (500);

p_valid_grades_changed_warning BOOLEAN;

p_effective_start_date DATE;

p_effective_end_date DATE;

p_validate boolean;

BEGIN

OPEN c;

LOOP

FETCH c

INTO lc_c;

EXIT WHEN c%NOTFOUND;

BEGIN

hr_position_api.delete_position

(p_validate => p_validate

,p_position_id => lc_c.POSITION_ID

,p_effective_start_date => p_effective_start_date

,p_effective_end_date => p_effective_end_date

,p_object_version_number => lc_c.OBJECT_VERSION_NUMBER

,p_effective_date => lc_c.EFFECTIVE_START_DATE

,p_datetrack_mode => 'ZAP'

,p_security_profile_id => 0

);

COMMIT;

DBMS_OUTPUT.put_line ('delete positons : ' || lc_c.POSITION_ID);

END;

COMMIT;

END LOOP;

CLOSE c;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

procedure create_tk_group_criteria

CURSOR tk_c is

select GC.TK_GROUP_NAME,

GC.EMPLYEE_NO ,

ppx.PERSON_ID,

htg.TK_GROUP_ID

from ifl_tk_group_criteria gc,

hxc_tk_groups htg ,

per_people_x ppx

where NVL(gc.STATUS_UPLOAD,'N') <>'Y'

and htg.TK_GROUP_NAME = gc.TK_GROUP_NAME

and ppx.EMPLOYEE_NUMBER = gc.EMPLYEE_NO ;

tk_cc tk_c%ROWTYPE;

p_validate BOOLEAN := FALSE;

p_tk_group_query_criteria_id number := null;

p_tk_group_query_id number := null;

p_object_version_number NUMBER := null;

BEGIN

OPEN tk_c;

LOOP

FETCH tk_c

INTO tk_cc;

EXIT WHEN tk_c%NOTFOUND;

BEGIN

HXC_TK_GRP_QUERY_CRITERIA_API.create_tk_grp_query_criteria

(p_validate => p_validate

,p_tk_group_query_criteria_id => p_tk_group_query_criteria_id

,p_tk_group_query_id => p_tk_group_query_id

,p_object_version_number => p_object_version_number

,p_tk_group_id => tk_cc.TK_GROUP_ID

,p_criteria_type => 'PERSON'

,p_criteria_id => tk_cc.PERSON_ID

);

COMMIT;

DBMS_OUTPUT.put_line ( 'Succesfully create :');

if p_tk_group_query_id is not null then

update ifl_tk_group_criteria gc

set gc.STATUS_UPLOAD = 'Y'

WHERE gc.EMPLYEE_NO = tk_cc.EMPLYEE_NO ;

else

update ifl_tk_group_criteria gc

set gc.STATUS_UPLOAD = 'N'

WHERE gc.EMPLYEE_NO = tk_cc.EMPLYEE_NO ;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

end;

end loop;

close tk_c;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

procedure Emp_salary_create_or_update is

cursor c_empsal is

select empno ,

effective_date,

salary

from ifl_emp_salary es

where nvl(es.STATUS_UPLOAD,'N') <>'Y' ;

cc_emp c_empsal%rowtype;

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_business_group_id number;

l_assignment_id number;

BEGIN

open c_empsal;

loop

fetch c_empsal into cc_emp;

exit when c_empsal%notfound;

begin

select pax.BUSINESS_GROUP_ID,pax.ASSIGNMENT_ID

into l_business_group_id,l_assignment_id

from per_people_x ppx,

per_assignments_x pax

where pax.PERSON_ID = ppx.PERSON_ID

and ppx.employee_number = cc_emp.empno ;

-- Create or Upadte Employee Salary Proposal

-- ----------------------------------------------------------------

hr_maintain_proposal_api.cre_or_upd_salary_proposal

( -- Input data elements

-- ------------------------------

p_business_group_id => l_business_group_id, --fnd_profile.value('PER_BUSINESS_GROUP_ID'),

p_assignment_id => l_assignment_id,

p_change_date => cc_emp.effective_date,

p_proposed_salary_n => cc_emp.salary,

p_approved => 'Y',

-- Output data elements

-- --------------------------------

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

dbms_output.put_line('Successfully created Employee salary'||cc_emp.empno||' '||ln_pay_proposal_id);

update ifl_emp_salary es

set es.STATUS_UPLOAD = 'Y'

where es.EMPNO = cc_emp.empno ;

else

update ifl_emp_salary es

set es.STATUS_UPLOAD = 'N'

where es.EMPNO = cc_emp.empno ;

end if;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

dbms_output.put_line('Inner Exception'||SQLERRM);

END;

end loop;

close c_empsal;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

dbms_output.put_line('Main Exception'||SQLERRM);

END;

---- END PACKAGE --

END;

Oracle Technical Consultant Information

Thursday, 13 October 2016

Oracle HRMS Uploading Scripts

1 comment:

Blog Archive