***The following Package used to create the New employee with Assignment, Address, SIT(Special Information), EIT(Extra Information), Element Entries with Values, Jobs, Grades, Positions, Personal Payment Method, Employee Phone Details......Using API's.
Package Specification:
-----------------------------
CREATE OR REPLACE PACKAGE APPS.xxse_emp_master_cre_api
IS
-- =============================================================================
--
-- FILENAME
-- XXSE_EMP_MASTER_CRE_API.pks
--
-- DESCRIPTION
-- BLUE PRINT : EMPLOYEE Creation API
--
-- -----------------------------------------------------------------------------
--
-- CHANGE LIST
-- ===========
--
-- VERSION DATE AUTHOR COMMENT
-- -------+-----------+--------------------+------------------------------------
-- 1.0 04-JUN-2019 Subba Reddy CREATED.
--
-- =============================================================================
--
-- -----------------------------------------------------------------------------
-- -----------------First Create Employee---------------------------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE create_emp (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
pp_employee_number IN VARCHAR2 DEFAULT NULL
);
--
-- -----------------------------------------------------------------------------
-- -----------------After Create Emp,Create Address-----------------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE create_address (p_person_id NUMBER);
--
-- -----------------------------------------------------------------------------
-- -----------------After Create Emp,Correct Assg Details-----------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE correct_assg_det (p_person_id NUMBER, p_business_group_id NUMBER);
--
--Creation Speceial Information
PROCEDURE xx_create_sit (p_person_id NUMBER, p_business_group_id NUMBER);
--
--Creation Extra Information
PROCEDURE xx_create_eit (p_person_id NUMBER);
--
--Creating Employee Element Entry Values
PROCEDURE xx_emp_element_entry (p_person_id NUMBER);
--
--Creating Employee Jobs
PROCEDURE xx_emp_jobs (p_person_id NUMBER, p_business_group_id NUMBER);
--
--Creating Employee Grade
PROCEDURE xx_emp_grade (p_person_id NUMBER, p_business_group_id NUMBER);
--
-- Creating Employee Position
PROCEDURE xx_emp_position (p_person_id NUMBER, p_business_group_id NUMBER);
--
-- Creating Employee Personal Payment Method
PROCEDURE xx_emp_per_pay_mtd (
p_person_id NUMBER,
p_business_group_id NUMBER
);
--
---- Creating Phone Number at Employee Level(Others-->Phones)
PROCEDURE xx_emp_phone (p_person_id NUMBER);
END xxse_emp_master_cre_api;
/
---------------------
Package Body:
---------------------
CREATE OR REPLACE PACKAGE BODY APPS.xxse_emp_master_cre_api
IS
-- =============================================================================
--
-- FILENAME
-- XXSE_EMP_MASTER_CRE_API.pkb
--
-- DESCRIPTION
-- BLUE PRINT : EMPLOYEE Creation API
--
-- -----------------------------------------------------------------------------
--
-- CHANGE LIST
-- ===========
--
-- VERSION DATE AUTHOR COMMENT
-- -------+-----------+--------------------+------------------------------------
-- 1.0 04-JUN-2019 Subba Reddy CREATED.
--
-- =============================================================================
PROCEDURE LOG (p_message IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_message);
DBMS_OUTPUT.put_line (p_message);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error at LOG:' || SQLERRM);
DBMS_OUTPUT.put_line ('Error at LOG:' || SQLERRM);
END;
--
-- -----------------------------------------------------------------------------
-- -----------------First Create Employee---------------------------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE create_emp (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
pp_employee_number IN VARCHAR2 DEFAULT NULL
)
IS
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1
AND xxe.employee_number =
NVL (pp_employee_number, xxe.employee_number)
AND status = 'ACTIVE'
AND 0 =
(SELECT COUNT (*)
FROM per_all_people_f
WHERE 1 = 1
AND employee_number = xxe.employee_number
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date)
AND NVL (interface_status, 'NEW') IN
('NEW', 'ERROR', 'API ERROR');
l_person_id per_all_people_f.person_id%TYPE;
l_full_name per_all_people_f.full_name%TYPE;
l_assignment_id per_all_assignments_f.assignment_id%TYPE;
l_object_version_number per_all_assignments_f.object_version_number%TYPE;
l_asg_object_version_number NUMBER;
l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
l_per_comment_id per_all_people_f.comment_id%TYPE;
l_assignment_seq per_all_assignments_f.assignment_sequence%TYPE;
l_assignment_number per_all_assignments_f.assignment_number%TYPE;
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
p_email_address VARCHAR2 (500);
p_blood_type VARCHAR2 (100);
p_date_of_birth DATE;
p_country_of_birth VARCHAR2 (500);
p_title VARCHAR2 (500);
p_town_of_birth VARCHAR2 (500);
p_nationality VARCHAR2 (500);
p_region_of_birth VARCHAR2 (500);
p_office_number VARCHAR2 (500);
p_hire_date DATE;
p_business_group_id NUMBER;
p_last_name VARCHAR2 (500);
p_first_name VARCHAR2 (500);
p_middle_names VARCHAR2 (500);
p_sex VARCHAR2 (500);
p_national_identifier VARCHAR2 (500);
p_known_as VARCHAR2 (500);
p_employee_number VARCHAR2 (500);
l_nationality VARCHAR2 (1500);
l_gender VARCHAR2 (1500);
l_employee_number VARCHAR2 (1500);
p_count NUMBER;
--
l_error_flag VARCHAR2 (10);
l_error_message VARCHAR2 (10000);
--
l_hire_date DATE;
l_marital_status VARCHAR2 (10);
l_person_type_id NUMBER;
l_business_group_id NUMBER
:= fnd_profile.value_specific ('PER_BUSINESS_GROUP_ID');
BEGIN
fnd_global.apps_initialize (user_id => fnd_global.user_id,
resp_id => fnd_global.resp_id,
resp_appl_id => fnd_global.resp_appl_id
);
FOR i IN c1
LOOP
BEGIN
LOG ('##############################################');
LOG ('Employee Number:' || i.employee_number);
LOG ('###############################################');
l_error_flag := 'N';
l_person_id := NULL;
l_assignment_id := NULL;
l_nationality := NULL;
l_gender := NULL;
l_hire_date := NULL;
l_marital_status := NULL;
l_person_type_id := NULL;
BEGIN
SELECT lookup_code
INTO l_nationality
FROM fnd_lookup_values
WHERE 1 = 1
AND lookup_type = 'AE_NATIONALITY' --'NATIONALITY'
AND UPPER (meaning) = UPPER (i.nationality)
AND LANGUAGE = 'US'
AND enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message || ',' || 'Error at Nationality';
END;
--Getting Gender code
BEGIN
SELECT DECODE (UPPER (i.gender), 'MALE', 'M', 'FEMALE', 'F')
INTO l_gender
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message || ',' || 'Error at Gender';
END;
--
--getting hire date
BEGIN
SELECT date_from --TO_DATE (date_from, 'DD/MM/RRRR') dt
INTO l_hire_date
FROM per_business_groups
WHERE 1 = 1 AND business_group_id = l_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message || ',' || 'Error at Business Group ID';
END;
--getting Marital Status code
BEGIN
SELECT lookup_code
INTO l_marital_status
FROM fnd_lookup_values
WHERE lookup_type = 'PQP_GB_PENSERVER_MAR_STAT_CODE'
AND UPPER (meaning) =
DECODE (UPPER (i.marital_status),
'SINGLE', 'SINGLE (NEVER MARRIED)',
'WIDOW(ER)', 'WIDOWED',
UPPER (i.marital_status)
)
AND LANGUAGE = 'US'
AND enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_marital_status := NULL;
END;
--Geeting Person Types
BEGIN
SELECT person_type_id
INTO l_person_type_id
FROM per_person_types
WHERE 1 = 1
AND UPPER (user_person_type) =
DECODE (UPPER (i.employee_type),
'WORKERS', 'WORKER',
UPPER (i.employee_type)
);
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message || ',' || 'Error at Employee Type';
END;
IF l_error_flag = 'Y'
THEN
UPDATE xx_employee_master_stg
SET interface_status = 'ERROR',
error_messgae = l_error_message
WHERE 1 = 1
-- AND employee_number = i.employee_number
AND ROWID = i.row_id;
COMMIT;
LOG ('Error Messages:' || l_error_message);
ELSE
LOG ( 'Start Processing for the Employee First Name:'
|| i.first_name
|| ' '
|| 'and Last Name:'
|| i.last_name
);
BEGIN
hr_employee_api.create_employee
(
--INPUT Parameter
p_hire_date => i.last_hire_date,
-- l_hire_date,
p_business_group_id => l_business_group_id,
p_last_name => i.last_name,
p_first_name => i.first_name,
p_middle_names => NULL,
p_sex => l_gender,
p_person_type_id => l_person_type_id,
p_marital_status => l_marital_status,
p_national_identifier => NULL,
p_date_of_birth => i.birth_date,
p_known_as => i.first_name,
--OUTPUT Parameter
p_employee_number => i.employee_number,
p_person_id => l_person_id,
p_assignment_id => l_assignment_id,
p_per_object_version_number => l_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_seq,
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,
p_email_address => i.email_id,
p_blood_type => i.blood_group,
p_country_of_birth => i.country,
p_title => UPPER (i.title),
p_town_of_birth => i.city,
--p_nationality => 'UAE',--l_nationality,
p_per_information18 => l_nationality,
--Nationality
p_per_information12 => l_nationality,
p_per_information1 => i.father_name,
--father name
p_per_information3 => i.mother_name,
p_region_of_birth => i.religion,
p_office_number => i.sector,
p_internal_location=>i.department
);
IF l_person_id IS NOT NULL
THEN
LOG ( 'Employee with Employee Number: '
|| i.employee_number
|| ' created sucessfully'
);
COMMIT;
BEGIN
SELECT COUNT (*)
INTO p_count
FROM per_all_people_f
WHERE person_id = l_person_id;
EXCEPTION
WHEN OTHERS
THEN
p_count := NULL;
LOG ('Error at getting person_id ');
END;
IF p_count > 0
THEN
UPDATE xx_employee_master_stg
SET person_id = l_person_id,
sys_employee_num = l_employee_number,
interface_status = 'PROCESSED'
WHERE 1 = 1 AND employee_number = i.employee_number;
COMMIT;
END IF;
LOG ('------------------------------------------------');
LOG ('Calling CREATE_ADDRESS API');
BEGIN
create_address (l_person_id);
--Creating Employee Address
EXCEPTION
WHEN OTHERS
THEN
LOG ('Address is Not Created');
END;
BEGIN
xx_emp_jobs (l_person_id, l_business_group_id);
--creating Job
EXCEPTION
WHEN OTHERS
THEN
LOG ('Job is Not Created');
END;
BEGIN
xx_emp_grade (l_person_id, l_business_group_id);
--creating Grade
EXCEPTION
WHEN OTHERS
THEN
LOG ('Grade is Not Created');
END;
BEGIN
xx_emp_position (l_person_id, l_business_group_id);
--creating position
EXCEPTION
WHEN OTHERS
THEN
LOG ('Position is Not Created');
END;
BEGIN
xx_create_sit (l_person_id, l_business_group_id);
--creating SIT
EXCEPTION
WHEN OTHERS
THEN
LOG ('Special Info is Not Created');
END;
BEGIN
xx_create_eit (l_person_id); --creating EIT
EXCEPTION
WHEN OTHERS
THEN
LOG ('Extra Info is Not Created');
END;
BEGIN
xx_emp_element_entry (l_person_id);
--creaing element entries
EXCEPTION
WHEN OTHERS
THEN
LOG ('Elements are not assigned');
END;
BEGIN
xx_emp_phone (l_person_id);
EXCEPTION
WHEN OTHERS
THEN
LOG ('Phone not Created');
END;
LOG ('------------------------------------------------');
IF l_assignment_id IS NOT NULL
THEN
LOG
('------------------------------------------------'
);
LOG ('Calling CORRECT_ASSG_DET API');
correct_assg_det (l_person_id, l_business_group_id);
LOG
('------------------------------------------------');
BEGIN
xx_emp_per_pay_mtd (l_person_id,
l_business_group_id
);
--Creating personal Payment method
EXCEPTION
WHEN OTHERS
THEN
LOG ('Payment Method is Not Createed');
END;
ELSE
LOG
( 'Assignment Not Created for the Employee Number:'
|| i.employee_number
);
END IF;
ELSE
LOG ( 'Employee:'
|| i.employee_number
|| ' '
|| 'Not Processed'
|| SQLERRM
);
UPDATE xx_employee_master_stg
SET interface_status = 'API ERROR',
error_messgae = 'Error at API'
WHERE 1 = 1 AND employee_number = i.employee_number;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LOG ('Error in API:' || SQLERRM);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LOG ( 'Error in Loop for Employe:'
|| i.employee_number
|| ' '
|| SQLERRM
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
LOG ('error at ====>' || SQLERRM);
END;
--
-- -----------------------------------------------------------------------------
-- -----------------After Create Emp,Create Address-----------------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE create_address (p_person_id NUMBER)
IS
ln_address_id per_addresses.address_id%TYPE;
ln_object_version_number per_addresses.object_version_number%TYPE;
--
l_error_flag VARCHAR2 (10);
l_error_message VARCHAR2 (10000);
l_country_code VARCHAR2 (20);
l_eff_date DATE;
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
--
BEGIN
l_error_flag := 'N';
l_eff_date := NULL;
l_country_code := NULL;
--getting Details from the Per_all_peole_f Table
BEGIN
SELECT ppf.effective_start_date
INTO l_eff_date
FROM per_all_people_f ppf
WHERE 1 = 1
AND ppf.person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message
|| ','
|| 'Error at Getting Effective Date at Address API';
END;
FOR r IN c1
LOOP
--Getting Coutry Code
BEGIN
SELECT country_code
INTO l_country_code
FROM hz_geographies
WHERE 1 = 1
AND geography_type = 'COUNTRY'
AND UPPER (geography_name) = UPPER (r.nationality)
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message
|| ','
|| 'Error at Getting Country Code at Address API';
END;
IF l_error_flag = 'Y'
THEN
LOG ('Error Messagesat Address:' || l_error_message);
ELSE
BEGIN
IF r.address1 IS NOT NULL
THEN
LOG ('--Address Creating--');
hr_person_address_api.create_person_address
(p_effective_date => l_eff_date,
p_person_id => p_person_id,
p_primary_flag => 'Y',
p_style => l_country_code,
p_date_from => l_eff_date,
p_address_line1 => r.address1,
p_address_line2 => r.address2,
p_address_line3 => '1',
--p_town_or_city => 'White Plains',
--p_region_1 => 'Westchester',
--p_region_2 => 'NY',
-- p_region_3 => 'Westchester',
-- p_postal_code => 516172,
p_country => l_country_code,
--'US',
p_address_id => ln_address_id,
-- p_add_information13 => 'Test1',
-- p_add_information14 => 'Proddatur',
--p_add_information15 => 'Test3',
p_object_version_number => ln_object_version_number
);
COMMIT;
END IF;
IF ln_address_id IS NOT NULL
THEN
COMMIT;
LOG ('----Address Created Id:' || ln_address_id);
ELSE
LOG ('----Address not Created');
END IF;
EXCEPTION
WHEN OTHERS
THEN
LOG ('Error at Address API:' || SQLERRM);
END;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
LOG ('Error at Address:' || SQLERRM);
END;
--
-- -----------------------------------------------------------------------------
-- -----------------After Create Emp,Correct Assg Details-----------------------
-- -----------------------------------------------------------------------------
--
PROCEDURE correct_assg_det (p_person_id NUMBER, p_business_group_id NUMBER)
IS
v_people_group_id NUMBER; --:=hr_api.g_number;
v_object_version_number NUMBER;
v_special_ceiling_step_id NUMBER;
v_group_name VARCHAR2 (4000);
v_effective_start_date DATE;
v_effective_end_date DATE;
v_org_now_no_manager_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_spp_delete_warning BOOLEAN;
v_entries_changed_warning VARCHAR2 (4000);
v_tax_district_changed_warning BOOLEAN;
vl$fb_entra BOOLEAN := TRUE;
v_concatenated_segments VARCHAR2 (200);
l_concatenated_segments VARCHAR2 (200);
-- := '101.000.000.6111203.000.000';
v_gsp_post_process_warning VARCHAR2 (200);
v_soft_coding_keyflex_id NUMBER;
ln_comment_id per_all_assignments_f.comment_id%TYPE;
lb_no_managers_warning BOOLEAN;
lb_other_manager_warning BOOLEAN;
--
l_error_flag VARCHAR2 (10);
l_error_message VARCHAR2 (10000);
l_country_code VARCHAR2 (20);
l_eff_date DATE;
l_assign_id NUMBER;
l_location_id NUMBER;
l_org_id NUMBER;
l_obj_ver_no NUMBER;
l_position_id NUMBER;
l_job_id NUMBER;
l_grade_id NUMBER;
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
--
BEGIN
l_error_flag := 'N';
l_eff_date := NULL;
l_country_code := NULL;
l_org_id := NULL;
l_location_id := NULL;
l_assign_id := NULL;
l_obj_ver_no := NULL;
--getting Details from the Per_all_peole_f Table
BEGIN
SELECT pam.effective_start_date, pam.assignment_id,
pam.location_id, pam.organization_id,
pam.object_version_number
INTO l_eff_date, l_assign_id,
l_location_id, l_org_id,
l_obj_ver_no
FROM per_all_assignments_f pam
WHERE 1 = 1
AND pam.person_id = p_person_id
AND TRUNC (SYSDATE) BETWEEN pam.effective_start_date
AND pam.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_flag := 'Y';
l_error_message :=
l_error_message
|| ','
|| 'Error at Assign Details at Assignment API';
END;
IF l_error_flag = 'Y'
THEN
LOG ('Error Messages at Assign API:' || l_error_message);
ELSE
BEGIN
hr_assignment_api.update_emp_asg
(p_effective_date => l_eff_date,
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => l_assign_id,
--ln_assignment_id,
--p_supervisor_id => NULL,
--p_change_reason => NULL,
--p_manager_flag => 'N',
--p_bargaining_unit_code => NULL,
--p_labour_union_member_flag => NULL,
p_segment1 => 83,
--Employer in Employment Details Tab
p_segment2 => 100,
--Civil Regi Num in Employment Details Tab
p_segment3 => 100,
--Social Security Num in Employment Details Tab
p_segment4 => TO_CHAR (l_eff_date,
'RRRR/MM/DD'
),
--Contr Start Date in Employment Details Tab
p_segment7 => 'Y',
--Accomidation Flag in Employment Details Tab
p_segment8 => 'N',
--Transport Flag in Employment Details Tab
p_default_code_comb_id => 13264,
--UAT--3014,--(13264--DEV),
--Defulat Expense Account in Purchase Order Information Tab
p_set_of_books_id => 2024,
---(2024--DEV),UAT--2022,
--Ledger in Purchase Order Information Tab
-- Output data elements
-- -------------------------------
p_object_version_number => l_obj_ver_no,
--rec.object_version_number,
--ln_object_number,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments,
p_comment_id => ln_comment_id,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_no_managers_warning => lb_no_managers_warning,
p_other_manager_warning => lb_other_manager_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
LOG ('Error at UPDATE_EMP_ASG ;' || SQLERRM);
END;
FOR r IN c1
LOOP
BEGIN
SELECT position_id, job_id
INTO l_position_id, l_job_id
FROM per_all_positions
WHERE 1 = 1
AND NAME = r.POSITION
AND business_group_id = p_business_group_id
AND date_effective <= l_eff_date;
EXCEPTION
WHEN OTHERS
THEN
l_position_id := NULL;
l_job_id := NULL;
END;
BEGIN
SELECT grade_id
INTO l_grade_id
FROM per_grades
WHERE 1 = 1
AND NAME = r.grade
AND business_group_id = p_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
END;
BEGIN
v_object_version_number := l_obj_ver_no;
LOG ('--Assignment Correction--');
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => l_eff_date,
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => l_assign_id,
p_validate => FALSE,
p_called_from_mass_update => NULL,
p_grade_id => l_grade_id,
p_position_id => l_position_id,
--i.position_id,
p_job_id => l_job_id,
--i.job_id,
p_payroll_id => 61,
--i.payroll_id,-- SE Workers Payroll
p_location_id => l_location_id,
p_organization_id => l_org_id,
p_pay_basis_id => NULL, --testing
p_segment1 => 768,
--People Group Value in Assignment
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_supervisor_assignment_id => NULL,
--i.sup_assign_id,
p_object_version_number => v_object_version_number,
p_special_ceiling_step_id => v_special_ceiling_step_id,
p_people_group_id => v_people_group_id,
p_soft_coding_keyflex_id => v_soft_coding_keyflex_id,
p_group_name => v_group_name,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_org_now_no_manager_warning => v_org_now_no_manager_warning,
p_other_manager_warning => v_other_manager_warning,
p_spp_delete_warning => v_spp_delete_warning,
p_entries_changed_warning => v_entries_changed_warning,
p_tax_district_changed_warning => v_tax_district_changed_warning,
p_concatenated_segments => v_concatenated_segments,
--v_concatenated_segments,
p_gsp_post_process_warning => v_gsp_post_process_warning
);
COMMIT;
LOG ('p_entries_changed_warning:' || v_entries_changed_warning);
/* IF ln_address_id IS NOT NULL
THEN
COMMIT;
LOG ('----Address Created Id:' || ln_address_id);
ELSE
LOG ('----Address not Created');
END IF;*/
EXCEPTION
WHEN OTHERS
THEN
LOG ('Error at Assignment API:' || SQLERRM);
END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
LOG ('Error at Assignment:' || SQLERRM);
END;
-- Creating Speceial Information
PROCEDURE xx_create_sit (p_person_id NUMBER, p_business_group_id NUMBER)
AS
--cursor
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
v_person_id per_all_people_f.person_id%TYPE;
p_analysis_criteria_id NUMBER;
p_person_analysis_id NUMBER;
p_per_object_version_number NUMBER;
p_medical_id VARCHAR2 (100);
l_id_flex_number NUMBER := NULL;
BEGIN
FOR r IN c1
LOOP
--SIT 1
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.emairate_id IS NOT NULL
THEN
-- SIT- Emirate ID
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Emirate ID';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'Emirate ID Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Emirate ID Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => r.last_hire_date,
p_date_to => r.emirates_id_expiry_date,
p_segment1 => r.emairate_id,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 2
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.insurance_company_name IS NOT NULL
OR r.insurance_card_number IS NOT NULL
THEN
-- SIT- Insurance Info.
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Insurance Info.';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Insurance Info. Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Insurance Info. Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => r.last_hire_date,
p_date_to => r.insurance_expiry_date,
p_segment1 => r.insurance_card_number,
p_segment2 => r.insurance_company_name,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 3
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.cnia_pass_number IS NOT NULL
THEN
-- SIT- ICNIA Pass Number
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'CNIA Pass Number';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'CNIA Pass Number Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With CNIA Pass Number Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => NVL
(r.cnia_issue_date,
r.last_hire_date
),
p_date_to => r.cnia_expiry_date,
p_segment1 => r.cnia_pass_number,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 4
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.passport_number IS NOT NULL
THEN
-- SIT- Passport Number
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Passport Number';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Passport Number Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Passport Number Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => NVL
(r.passport_isuue_date,
r.last_hire_date
),
p_date_to => r.passport_expiry_date,
p_segment1 => r.passport_number,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 5
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.unified_no IS NOT NULL
THEN
-- SIT- Unified Number
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Unified Number';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Unified Number Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Unified Number Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => r.last_hire_date,
p_date_to => NULL,
p_segment1 => r.unified_no,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 6
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.work_permit_id IS NOT NULL
THEN
-- SIT- Work Permit ID
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Work Permit ID';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Work Permit ID Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Work Permit ID Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => NVL
(r.work_permit_issue_date,
r.last_hire_date
),
p_date_to => r.work_permit_expiry_date,
p_segment1 => r.work_permit_id,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 7
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.labor_card_no IS NOT NULL
THEN
-- SIT- Labor Card Info.
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name = 'Labor Card Info.';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Labor Card Info. Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Labor Card Info. Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => NVL
(r.labor_card_issue_date,
r.last_hire_date
),
p_date_to => r.labor_card_expiry_date,
p_segment1 => r.labor_card_id,
p_segment2 => r.labor_card_no,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
--SIT 8
l_id_flex_number := NULL;
p_analysis_criteria_id := NULL;
p_person_analysis_id := NULL;
p_per_object_version_number := 1;
IF r.health_certificate_number IS NOT NULL
THEN
-- SIT- Health Certificate Number
BEGIN
SELECT fifv.id_flex_num
INTO l_id_flex_number
FROM fnd_id_flexs fif, fnd_id_flex_structures_vl fifv
WHERE 1 = 1
AND fif.id_flex_code = 'PEA'
AND fif.id_flex_code = fifv.id_flex_code
AND fifv.id_flex_structure_name =
'Health Certificate Number';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Health Certificate Number Structure is not Defined '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Health Certificate Number Structure '
);
END;
hr_sit_api.create_sit
(p_validate => FALSE,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_id_flex_num => l_id_flex_number,
p_effective_date => r.last_hire_date,
p_date_from => NVL
(r.health_certificate_issue_date,
r.last_hire_date
),
p_date_to => r.health_certificate_expiry_date,
p_segment1 => r.health_certificate_number,
p_analysis_criteria_id => p_analysis_criteria_id,
p_person_analysis_id => p_person_analysis_id,
p_pea_object_version_number => p_per_object_version_number
);
COMMIT;
END IF;
END LOOP;
END;
--Creating Extra Information
PROCEDURE xx_create_eit (p_person_id NUMBER)
AS
ln_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE;
ln_object_version_number per_people_extra_info.object_version_number%TYPE;
l_img_count NUMBER;
l_img_no VARCHAR2 (120);
--cursor
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
FOR r IN c1
LOOP
--EIT 1
ln_person_extra_info_id := NULL;
ln_object_version_number := 1;
IF r.ticket_frequency IS NOT NULL
AND r.ticket_no IS NOT NULL
AND r.ticket_fare IS NOT NULL
THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => p_person_id,
p_information_type => 'TICKET_ELIGIBILITY',
p_pei_information_category => 'TICKET_ELIGIBILITY',
p_pei_information1 => r.ticket_frequency,
p_pei_information2 => r.ticket_no,
p_pei_information3 => r.ticket_fare,
p_person_extra_info_id => ln_person_extra_info_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
END;
END IF;
--EIT 2
ln_person_extra_info_id := NULL;
ln_object_version_number := 1;
SELECT COUNT (*)
INTO l_img_count
FROM fnd_flex_value_sets ffv, fnd_flex_values_vl fv
WHERE 1 = 1
AND ffv.flex_value_set_name = 'Immigration Designation'
AND ffv.flex_value_set_id = fv.flex_value_set_id
AND fv.flex_value = r.immigration_designation;
IF l_img_count = 0
THEN
BEGIN
-- fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
apps.fnd_flex_val_api.create_independent_vset_value
(p_flex_value_set_name => 'Immigration Designation',
p_flex_value => r.immigration_designation,
p_description => r.immigration_designation,
p_enabled_flag => 'Y',
p_start_date_active => NULL,
p_end_date_active => NULL,
p_summary_flag => 'N',
p_structured_hierarchy_level => NULL,
p_hierarchy_level => NULL,
x_storage_value => l_img_no
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Error is '
|| SUBSTR (SQLERRM, 1, 1000)
);
END;
END IF;
IF r.immigration_designation IS NOT NULL
THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => p_person_id,
p_information_type => 'Immigration_Designation',
p_pei_information_category => 'Immigration_Designation',
p_pei_information1 => r.immigration_designation,
p_person_extra_info_id => ln_person_extra_info_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
END;
END IF;
END LOOP;
END;
-- Creating Employee Element entries
PROCEDURE xx_emp_element_entry (p_person_id NUMBER)
AS
--Variables
ln_element_link_id pay_element_links_f.element_link_id%TYPE;
ld_effective_start_date DATE;
ld_effective_end_date DATE;
ln_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
ln_object_version_number pay_element_entries_f.object_version_number%TYPE;
lb_create_warning BOOLEAN;
ln_input_value_id pay_input_values_f.input_value_id%TYPE;
ln_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
ln_element_type_id pay_element_types_f.element_type_id%TYPE;
l_assignment_id NUMBER;
l_element_type_id NUMBER;
l_business_group_id NUMBER;
l_input_value_id_1 NUMBER;
l_input_value_id_2 NUMBER;
l_input_value_id_3 NUMBER;
l_input_value_id_4 NUMBER;
--Cursor
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
SELECT assignment_id, business_group_id
INTO l_assignment_id, l_business_group_id
FROM per_all_assignments_f
WHERE 1 = 1
AND person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
FOR r IN c1
LOOP
BEGIN
------------Element1------------------------------------------
------------Assign Element Entry for Basic Salary-------------------
--Getting Element type id
l_element_type_id := NULL;
ln_element_link_id := NULL;
l_input_value_id_1 := NULL;
BEGIN
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f_tl
WHERE element_name = 'Basic Salary';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'Basic Salarty Element is not Defined'
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Basic Salary'
);
END;
--Getting Input value id
BEGIN
SELECT pivt.input_value_id
INTO l_input_value_id_1
FROM pay_input_values_f piv, pay_input_values_f_tl pivt
WHERE 1 = 1
AND piv.element_type_id = l_element_type_id
AND piv.input_value_id = pivt.input_value_id
AND pivt.NAME = 'Monthly Amount';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Monthly Amount not defined under Basic Salary element'
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Basic Salary element'
);
END;
-- Get Element Link Id
-- ------------------------------
ln_element_link_id :=
hr_entry_api.get_link (p_assignment_id => l_assignment_id,
p_element_type_id => l_element_type_id,
p_session_date => r.last_hire_date
);
----
-- Create Element Entry
-- ------------------------------
pay_element_entry_api.create_element_entry
( -- Input data elements
-- -----------------------------
p_effective_date => r.last_hire_date,
p_business_group_id => l_business_group_id,
p_assignment_id => l_assignment_id,
p_element_link_id => ln_element_link_id,
p_entry_type => 'E',
p_input_value_id1 => l_input_value_id_1,
p_entry_value1 => r.basic_salary,
-- Output data elements
-- --------------------------------
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Basic Salary Element Entry not assigned'
);
END;
BEGIN
------------Element2------------------------------------------
------------Assign Element Entry for Accommodation Allowance By Client-------------------
--Getting Element type id
l_element_type_id := NULL;
ln_element_link_id := NULL;
l_input_value_id_1 := NULL;
BEGIN
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f_tl
WHERE element_name = 'Accommodation Allowance By Client';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Accommodation Allowance By Client Element is not Defined'
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Accommodation Allowance By Client'
);
END;
--Getting Input value id
BEGIN
SELECT pivt.input_value_id
INTO l_input_value_id_1
FROM pay_input_values_f piv, pay_input_values_f_tl pivt
WHERE 1 = 1
AND piv.element_type_id = l_element_type_id
AND piv.input_value_id = pivt.input_value_id
AND pivt.NAME = 'Monthly Amount';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Monthly Amount not defined under Accommodation Allowance By Client element'
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Accommodation Allowance By Client element'
);
END;
-- Get Element Link Id
-- ------------------------------
ln_element_link_id :=
hr_entry_api.get_link (p_assignment_id => l_assignment_id,
p_element_type_id => l_element_type_id,
p_session_date => r.last_hire_date
);
----
-- Create Element Entry
-- ------------------------------
pay_element_entry_api.create_element_entry
( -- Input data elements
-- -----------------------------
p_effective_date => r.last_hire_date,
p_business_group_id => l_business_group_id,
p_assignment_id => l_assignment_id,
p_element_link_id => ln_element_link_id,
p_entry_type => 'E',
p_input_value_id2 => l_input_value_id_1,
p_entry_value2 => r.accomton_allowce_by_client,
-- Output data elements
-- --------------------------------
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Accommodation Allowance By Client Element Entry not assigned'
);
END;
BEGIN
------------Element3------------------------------------------
-----------Assign Element Entry for Accomodation Allowance-------------------
--Getting Element type id
l_element_type_id := NULL;
ln_element_link_id := NULL;
BEGIN
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f_tl
WHERE element_name = 'Accomodation Allowance';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Accomodation Allowance Element is not Defined'
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Accomodation Allowance'
);
END;
BEGIN
SELECT pivt.input_value_id
INTO l_input_value_id_1
FROM pay_input_values_f piv, pay_input_values_f_tl pivt
WHERE 1 = 1
AND piv.element_type_id = l_element_type_id
AND piv.input_value_id = pivt.input_value_id
AND pivt.NAME = 'Monthly Amount';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Monthly Amount not defined under Accomodation Allowance element'
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Accomodation Allowance element'
);
END;
-- Get Element Link Id
-- ------------------------------
ln_element_link_id :=
hr_entry_api.get_link (p_assignment_id => l_assignment_id,
p_element_type_id => l_element_type_id,
p_session_date => r.last_hire_date
);
----
-- Create Element Entry
-- ------------------------------
pay_element_entry_api.create_element_entry
( -- Input data elements
-- -----------------------------
p_effective_date => r.last_hire_date,
p_business_group_id => l_business_group_id,
p_assignment_id => l_assignment_id,
p_element_link_id => ln_element_link_id,
p_entry_type => 'E',
p_input_value_id2 => l_input_value_id_1,
p_entry_value2 => r.accomodation_allowance,
-- Output data elements
-- --------------------------------
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Accomodation Allowance Element Entry not assigned'
);
END;
BEGIN
------------Element4------------------------------------------
------------Assign Element Entry for Emiratization Allowance-------------------
--Getting Element type id
l_element_type_id := NULL;
ln_element_link_id := NULL;
l_input_value_id_1 := NULL;
BEGIN
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f_tl
WHERE element_name = 'Emiratization Allowance';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Emiratization Allowance Element is not Defined'
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Problem With Emiratization Allowance'
);
END;
--Getting Input value id
BEGIN
SELECT pivt.input_value_id
INTO l_input_value_id_1
FROM pay_input_values_f piv, pay_input_values_f_tl pivt
WHERE 1 = 1
AND piv.element_type_id = l_element_type_id
AND piv.input_value_id = pivt.input_value_id
AND pivt.NAME = 'Monthly Amount';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Monthly Amount not defined under Emiratization Allowance element'
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Problem With Emiratization Allowance element'
);
END;
-- Get Element Link Id
-- ------------------------------
ln_element_link_id :=
hr_entry_api.get_link (p_assignment_id => l_assignment_id,
p_element_type_id => l_element_type_id,
p_session_date => r.last_hire_date
);
----
-- Create Element Entry
-- ------------------------------
pay_element_entry_api.create_element_entry
( -- Input data elements
-- -----------------------------
p_effective_date => r.last_hire_date,
p_business_group_id => l_business_group_id,
p_assignment_id => l_assignment_id,
p_element_link_id => ln_element_link_id,
p_entry_type => 'E',
p_input_value_id2 => l_input_value_id_1,
p_entry_value2 => r.emiratization_allowance,
-- Output data elements
-- --------------------------------
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Emiratization Allowance Element Entry not assigned'
);
END;
END LOOP;
END;
PROCEDURE xx_emp_jobs (p_person_id NUMBER, p_business_group_id NUMBER)
AS
--Local variables
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;
l_job_count NUMBER;
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
BEGIN
SELECT job_group_id
INTO l_job_group_id
FROM per_job_groups
WHERE 1 = 1
AND business_group_id = p_business_group_id
AND internal_name = 'HR_82';
EXCEPTION
WHEN OTHERS
THEN
l_job_group_id := NULL;
END;
FOR r IN c1
LOOP
SELECT COUNT (*)
INTO l_job_count
FROM per_jobs
WHERE 1 = 1
AND NAME = r.immigration_designation
AND business_group_id = p_business_group_id;
IF l_job_count = 0
THEN
IF l_job_group_id IS NOT NULL
THEN
BEGIN
hr_job_api.create_job
(p_validate => FALSE,
p_business_group_id => p_business_group_id,
p_date_from => TO_DATE
('01-JAN-2000',
'DD-MON-YYYY'
),
p_job_group_id => l_job_group_id,
p_segment1 => r.immigration_designation,
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);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);
END;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
PROCEDURE xx_emp_grade (p_person_id NUMBER, p_business_group_id NUMBER)
AS
l_grade_no VARCHAR2 (120);
l_gd_count NUMBER;
l_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
l_resp_appl_id NUMBER := 0;
l_resp_id NUMBER;
l_grd_seg NUMBER;
l_object_version_number NUMBER := NULL;
l_grade_definition_id NUMBER := NULL;
l_grade_id NUMBER;
l_name VARCHAR2 (500) := NULL;
l_emp_grd_count NUMBER;
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
BEGIN
SELECT responsibility_id
INTO l_resp_id
FROM fnd_responsibility_tl
WHERE responsibility_name = 'Application Developer';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Responsibility Not defined');
END;
FOR r IN c1
LOOP
--Inserting values into GRADE Value SET
SELECT COUNT (*)
INTO l_gd_count
FROM fnd_flex_value_sets ffv, fnd_flex_values_vl fv
WHERE 1 = 1
AND ffv.flex_value_set_name = 'GRADE'
AND ffv.flex_value_set_id = fv.flex_value_set_id
AND fv.flex_value = r.grade;
IF l_gd_count = 0
THEN
BEGIN
-- fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
apps.fnd_flex_val_api.create_independent_vset_value
(p_flex_value_set_name => 'GRADE',
p_flex_value => r.grade,
p_description => r.grade,
p_enabled_flag => 'Y',
p_start_date_active => NULL,
p_end_date_active => NULL,
p_summary_flag => 'N',
p_structured_hierarchy_level => NULL,
p_hierarchy_level => NULL,
x_storage_value => l_grade_no
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Error is '
|| SUBSTR (SQLERRM, 1, 1000)
);
END;
END IF;
--Create Emp Grade
SELECT MAX (SEQUENCE)
INTO l_grd_seg
FROM per_grades
WHERE 1 = 1;
SELECT COUNT (*)
INTO l_emp_grd_count
FROM per_grades
WHERE 1 = 1
AND NAME = r.grade
AND business_group_id = p_business_group_id;
IF l_emp_grd_count = 0
THEN
BEGIN
hr_grade_api.create_grade
(p_validate => FALSE,
p_business_group_id => p_business_group_id,
p_date_from => TO_DATE
('01-JAN-2000',
'DD-MON-YYYY'
),
p_sequence => l_grd_seg + 1,
p_segment1 => r.grade,
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
);
END;
END IF;
COMMIT;
END LOOP;
END;
PROCEDURE xx_emp_position (p_person_id NUMBER, p_business_group_id NUMBER)
AS
l_pos_name VARCHAR2 (500);
l_eff_start_date DATE;
l_eff_end_date DATE;
l_position_definition_id NUMBER;
l_object_version_number NUMBER;
l_position_id NUMBER;
l_job_id NUMBER;
l_pos_count NUMBER;
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
FOR r IN c1
LOOP
l_position_id := NULL;
l_object_version_number := NULL;
l_eff_start_date := NULL;
l_eff_end_date := NULL;
l_position_definition_id := NULL;
l_pos_name := NULL;
l_job_id := NULL;
--
BEGIN
SELECT job_id
INTO l_job_id
FROM per_jobs
WHERE 1 = 1
AND NAME = 'Employee'
--r.immigration_designation--Modified on 12-Dec-2019 as per Arun Suggestion
AND business_group_id = p_business_group_id;
END;
SELECT COUNT (*)
INTO l_pos_count
FROM per_all_positions
WHERE 1 = 1
AND NAME = r.POSITION
AND business_group_id = p_business_group_id;
IF l_pos_count = 0
THEN
---
hr_position_api.create_position
(p_job_id => l_job_id,
p_organization_id => p_business_group_id,
p_effective_date => '01-JAN-2000',
p_date_effective => '01-JAN-2000'
-- ,p_date_end => null
,
p_validate => FALSE,
p_position_type => 'SHARED',
p_availability_status_id => 1,
p_fte => 1,
p_max_persons => 1,
p_probation_period => NULL,
p_probation_period_unit_cd => NULL,
p_business_group_id => NULL,
p_segment1 => r.POSITION
-- ,p_attribute8 => null
-- ,p_attribute9 => null
-- ,p_entry_grade_id => null
,
p_bargaining_unit_cd => NULL
-- ,p_attribute6 => null
--
,
p_working_hours => NULL,
p_frequency => NULL
--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
);
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;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'hr_position_api.create_position API failed with error :'
|| SQLERRM
);
ROLLBACK;
END;
PROCEDURE xx_emp_per_pay_mtd (p_person_id NUMBER, p_business_group_id NUMBER)
AS
ln_method_id pay_personal_payment_methods_f.personal_payment_method_id%TYPE;
ln_ext_acc_id pay_external_accounts.external_account_id%TYPE;
ln_obj_ver_num pay_personal_payment_methods_f.object_version_number%TYPE;
ld_eff_start_date DATE;
ld_eff_end_date DATE;
ln_comment_id NUMBER;
l_assignment_id NUMBER;
l_personal_payment_id NUMBER;
l_per_pay_count NUMBER;
l_bank_code VARCHAR2 (60);
CURSOR c1
IS
SELECT xxe.*, xxe.ROWID row_id
FROM xx_employee_master_stg xxe
WHERE 1 = 1 AND person_id = p_person_id;
BEGIN
FOR r IN c1
LOOP
BEGIN
SELECT assignment_id
INTO l_assignment_id
FROM per_all_assignments_f
WHERE 1 = 1
AND person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Assignment is not created');
END;
BEGIN
SELECT org_payment_method_id
INTO l_personal_payment_id
FROM pay_org_payment_methods_f_tl
WHERE 1 = 1 AND org_payment_method_name = 'SW Personal Payment';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Payment method is not defined'
);
END;
SELECT COUNT (*)
INTO l_per_pay_count
FROM pay_personal_payment_methods_f
WHERE 1 = 1 AND assignment_id = l_assignment_id;
IF l_per_pay_count = 0
THEN
l_bank_code := NULL;
BEGIN
SELECT lookup_code
INTO l_bank_code
FROM hr_lookups h
WHERE 1 = 1
AND lookup_type = 'AE_BANK_NAMES'
AND lookup_code = r.bank_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Bank Name not defined in lookup'
);
l_bank_code := NULL;
END;
IF l_bank_code IS NOT NULL
THEN
-- Create Employee Payment Method
-- --------------------------------------------------
hr_personal_pay_method_api.create_personal_pay_method
( -- Input data elements
-- ------------------------------
p_effective_date => r.last_hire_date,
p_assignment_id => l_assignment_id,
p_org_payment_method_id => l_personal_payment_id,
p_priority => 50,
p_percentage => 100,
p_territory_code => 'AE',
p_segment1 => l_bank_code,
p_segment2 => r.branch,
p_segment3 => NULL,
p_segment4 => r.account_no,
p_segment5 => NULL,
p_segment7 => r.routing_code,
-- Output data elements
-- --------------------------------
p_personal_payment_method_id => ln_method_id,
p_external_account_id => ln_ext_acc_id,
p_object_version_number => ln_obj_ver_num,
p_effective_start_date => ld_eff_start_date,
p_effective_end_date => ld_eff_end_date,
p_comment_id => ln_comment_id
);
COMMIT;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
PROCEDURE xx_emp_phone (p_person_id NUMBER)
IS
ln_phone_id per_phones.phone_id%TYPE;
ln_object_version_number per_phones.object_version_number%TYPE;
l_telephone VARCHAR2 (100);
l_start_date DATE;
BEGIN
--Getting Phone number from the stage table
BEGIN
SELECT telephone, last_hire_date
INTO l_telephone, l_start_date
FROM xx_employee_master_stg
WHERE 1 = 1 AND person_id = p_person_id;
EXCEPTION
WHEN OTHERS
THEN
LOG ('Error at Phone:' || SQLERRM);
END;
-- Create or Update Employee Phone Detail
-- -----------------------------------------------------------
hr_phone_api.create_or_update_phone
( -- Input data elements
-- -----------------------------
p_date_from => l_start_date,
p_phone_type => 'M', --Mobile
p_phone_number => l_telephone,
p_parent_id => p_person_id,
p_parent_table => 'PER_ALL_PEOPLE_F',
p_effective_date => TO_DATE
('31-DEC-4712'),
-- Output data elements
-- --------------------------------
p_phone_id => ln_phone_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
-- log('Phone ID:'||ln_phone_id);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
LOG ('Erro at Phone:' || SQLERRM);
END;
END xxse_emp_master_cre_api;
/
