To Update Employee
-------------------------
Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id
API -- hr_assignment_api.update_emp_asg
To Update Employee
--------------------------
Grade, Location, Job, Payroll, Organization, Employee Category, People Group
API -- hr_assignment_api.update_emp_asg_criteria
Staging Table
-----------------
CREATE TABLE APPS.XXAT_EMP_ASSIGNMENTS
(
EMP_NUM NUMBER,
ORG_NAME VARCHAR2(2000 BYTE),
GCC_GROUP VARCHAR2(1000 BYTE),
POSITION VARCHAR2(2000 BYTE),
JOB VARCHAR2(2000 BYTE),
PAYROLL VARCHAR2(2000 BYTE),
LOCATION VARCHAR2(2000 BYTE),
STATUS VARCHAR2(2000 BYTE),
SAL_BASIS VARCHAR2(2000 BYTE),
SUPERVISOR_NUM NUMBER,
GOSI_EMP VARCHAR2(1000 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
ERROR_MSG VARCHAR2(3000 BYTE)
);
Flat File Format
------------------------------| Emp Num | Organization | GCC Group | Position | Job | Payroll | Location | Status | Salary Basis | Supervisor Number | GOSI - Employer | Process Flag | Error Message |
| 3 | Chief Business Support Office | No.No.Yes | Chief Business Support Officer.Chief Business Support Office | Chief Business Support Officer | EATC Payroll | Riyadh Head Office | Active Assignment | EATC Monthly Salary | 911 | Etihad Atheeb Telecommunication Company | N |
Sample Code - Example
------------------------------Package Specification
--------------------------
CREATE OR REPLACE PACKAGE APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update;
END xxat_emp_assign_pkg;
Package Body
----------------
CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_assign_pkg
AS
PROCEDURE xxxat_emp_assign_update
IS
-- lc_c_emp_assg c_emp_assg%ROWTYPE;
l_object_version_number NUMBER;
l_special_ceiling_step_id NUMBER;
l_people_group_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
l_supervisor_id NUMBER;
l_assg_id NUMBER;
l_group_name VARCHAR2 (100);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_no_managers_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (100);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (100);
l_gsp_post_process_warning VARCHAR2 (100);
l_job_id VARCHAR2 (20);
l_position_id VARCHAR2 (20);
l_grade_id VARCHAR2 (20);
l_org_id VARCHAR2 (20);
l_loc_id VARCHAR2 (20);
l_assg_cat VARCHAR2 (50);
l_assg_start_date DATE;
l_pf_flag CHAR (1);
l_segment1 VARCHAR2 (50);
l_segment2 VARCHAR2 (50);
l_segment3 VARCHAR2 (50);
l_person_id NUMBER;
--
l_error NUMBER;
l_error_msg VARCHAR2 (3000);
v_emp_num VARCHAR2 (100);
l_assign_date VARCHAR2 (100);
l_sup_hire_date DATE;
l_emp_hire_date DATE;
l_payroll_id NUMBER;
l_paybasis_id NUMBER;
l_comment_id NUMBER;
CURSOR c1
IS
SELECT *
FROM xxat_emp_assignments
WHERE process_flag = 'N'
AND emp_num = 82;
BEGIN
DBMS_OUTPUT.put_line (' ****** Process Validation Start ******');
FOR i IN c1
LOOP
l_error := 0;
l_person_id := NULL;
IF i.emp_num IS NULL
-- OR i.assign_start_date IS NULL
OR i.org_name IS NULL
OR i.gcc_group IS NULL
OR i.POSITION IS NULL
OR i.job IS NULL
OR i.payroll IS NULL
OR i.LOCATION IS NULL
OR i.status IS NULL
OR i.sal_basis IS NULL
OR i.supervisor_num IS NULL
OR i.gosi_emp IS NULL
THEN
l_error_msg :=
'All Fields are mandatory & should not be null';
l_error := 1;
-- p_retcode := 2;
--fnd_file.put_line (fnd_file.LOG,i.relationship_id || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
ELSE
l_object_version_number := NULL;
l_special_ceiling_step_id := NULL;
l_people_group_id := NULL;
l_soft_coding_keyflex_id := NULL;
l_group_name := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_org_now_no_manager_warning := FALSE;
l_other_manager_warning := FALSE;
l_spp_delete_warning := FALSE;
l_entries_changed_warning := NULL;
l_tax_district_changed_warning := FALSE;
l_concatenated_segments := NULL;
l_gsp_post_process_warning := NULL;
-- error_desc := '';
-- lv_assg_flag := '';
l_assg_cat := NULL;
l_job_id := NULL;
l_position_id := NULL;
l_grade_id := NULL;
l_org_id := NULL;
l_assign_date := NULL;
l_sup_hire_date := NULL;
l_emp_hire_date := NULL;
---------------------------
-- AssignmentID
---------------------------
BEGIN
SELECT paf.assignment_id
INTO l_assg_id
FROM per_assignments_f paf
WHERE paf.assignment_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_assg_id := NULL;
l_error_msg := 'Assignment not found';
l_error := 1;
END;
---------------------------------------
-- Employee Hire Date / Effective Start Date
---------------------------------------
BEGIN
SELECT paf.start_date
INTO l_emp_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.emp_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=
'Employee start date not found';
l_error := 1;
END;
-------------------------------------------------
-- Supervisor Hire Date / Effective Start Date
--------------------------------------------------
BEGIN
SELECT paf.start_date
INTO l_sup_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.supervisor_num
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT TO_DATE (TO_CHAR (l_assign_date,
'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS'
)
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=
'Employee start date not found';
l_error := 1;
END;
IF l_emp_hire_date < l_sup_hire_date
THEN
l_assign_date := l_sup_hire_date;
ELSE
l_assign_date := l_emp_hire_date;
END IF;
--dbms_output.put_line('emp date'||l_emp_hire_date||'super date'||l_sup_hire_date||'final'||l_assign_date);
----------------------------------
-- Organization ID
----------------------------------
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_all_organization_units
WHERE UPPER (NAME) LIKE UPPER (i.org_name)
AND business_group_id = 81;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Organization not found';
l_error := 1;
END;
----------------------
-- Job ID
----------------------
BEGIN
SELECT pj.job_id
INTO l_job_id
FROM per_jobs pj,
per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND pj.NAME = i.job;
EXCEPTION
WHEN OTHERS
THEN
-- l_job_id := NULL;
l_error_msg := 'Job not found';
l_error := 1;
END;
----------------------------------------
-- Position ID
----------------------------------------
BEGIN
SELECT pap.position_id
INTO l_position_id
FROM per_all_positions pap,
per_position_definitions ppd
WHERE pap.position_definition_id = ppd.position_definition_id
AND pap.status = 'VALID'
AND pap.NAME LIKE i.POSITION || '%';
EXCEPTION
WHEN OTHERS
THEN
-- l_position_id := NULL;
l_error_msg := 'Position not found';
l_error := 1;
END;
---------------------------
-- Grade ID
---------------------------
/* BEGIN
SELECT pg.grade_id
INTO l_grade_id
FROM per_grades pg,
per_grade_definitions pgd
WHERE pg.grade_definition_id = pgd.grade_definition_id
AND UPPER (pgd.segment1) =
UPPER (lc_c_emp_assg.grad_segment1)
AND UPPER (pgd.segment2) =
UPPER (lc_c_emp_assg.grad_segment2)
AND UPPER (pgd.segment3) =
UPPER (lc_c_emp_assg.grad_segment3);
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
END;*/
---------------------------------
-- Payroll ID
---------------------------------
BEGIN
SELECT payroll_id
INTO l_payroll_id
FROM pay_all_payrolls_f
WHERE 1 = 1
AND UPPER (payroll_name) LIKE UPPER (i.payroll)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_pay_id := NULL;
l_error_msg := 'Payroll ID not found';
l_error := 1;
END;
---------------------------------
-- Location ID
---------------------------------
BEGIN
SELECT location_id
INTO l_loc_id
FROM hr_locations_all
WHERE UPPER (location_code) LIKE UPPER (i.LOCATION);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Location ID not found';
l_error := 1;
END;
-----------------------------------------------
-- Salary Information (T) > Salary Basis ID
-----------------------------------------------
BEGIN
SELECT pay_basis_id
INTO l_paybasis_id
FROM per_pay_bases
WHERE 1 = 1
AND UPPER (NAME) LIKE UPPER (i.sal_basis);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Pay/Salary basis not found';
l_error := 1;
END;
---------------------------------
-- Supervisor ID
---------------------------------
BEGIN
SELECT person_id
INTO l_supervisor_id
FROM per_all_people_f ppf
WHERE employee_number = i.supervisor_num
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Supervisor ID not found';
l_error := 1;
END;
-----------------------------
-- Assignment Category
-----------------------------
-- BEGIN
-- SELECT distinct lookup_code
-- INTO l_assg_cat
-- FROM fnd_lookup_values_vl
-- WHERE lookup_type = 'EMP_CAT'
-- AND UPPER (meaning) LIKE UPPER (lc_c_emp_assg.ASSG_CATG)
-- AND DESCRIPTION IS NULL;
--
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- l_assg_cat := NULL;
-- END;
--------------------------
-- People Group ID & NAME Number
--------------------------
BEGIN
SELECT people_group_id,
group_name
INTO l_people_group_id,
l_group_name
FROM pay_people_groups a
WHERE segment1 || '.' || segment2 || '.' || segment3 IN (
SELECT segment1 || '.' || segment2 || '.' || segment3
FROM pay_people_groups a
WHERE 1 = 1
-- AND people_group_id = 'No.No.Yes'
AND group_name = i.gcc_group);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Group ID/Name not found';
l_error := 1;
END;
--------------------------
-- Object Version Number
--------------------------
BEGIN
SELECT paaf.object_version_number
INTO l_object_version_number
FROM per_all_assignments_f paaf
WHERE SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.assignment_id = l_assg_id;
-- DBMS_OUTPUT.put_line ( 'object version no.'
-- || l_object_version_number
-- );
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Object Version not found';
l_error := 1;
END;
IF l_error = 0
THEN
BEGIN
-- Update Employee Assgment Criteria
-- -----------------------------------------------------
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TO_DATE
(l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
--l_assg_start_date,
p_datetrack_update_mode => 'CORRECTION',
--'UPDATE',
p_assignment_id => l_assg_id,
p_position_id => l_position_id,
p_job_id => l_job_id,
p_payroll_id => l_payroll_id,
-- p_grade_id => l_grade_id,
p_location_id => l_loc_id,
p_organization_id => l_org_id,
p_pay_basis_id => l_paybasis_id,
--p_employment_category => l_assg_cat ,
p_segment1 => 81,
-- GOSI Tab - Emloyer Name
-- p_segment2 => lc_c_emp_assg.pg_segment2,
-- p_segment3 => lc_c_emp_assg.pg_segment3,
-- p_supervisor_assignment_id => l_supervisor_id,
--Outparatmers
p_people_group_id => l_people_group_id,
p_object_version_number => l_object_version_number,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
--lc_c_emp_assg.people_group,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
-- p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
-- p_concatenated_segments => l_concatenated_segments,
-- p_gsp_post_process_warning => l_gsp_post_process_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Api Exception ' || SQLERRM;
END;
-- dbms_output.put_line(TO_DATE (l_assign_date,'DD-MON-RRRR' ));
BEGIN
-- Update Employee Assignment
-- ---------------------------------------------
hr_assignment_api.update_emp_asg
(
-- Input data elements
p_effective_date => TO_DATE (l_assign_date,
'DD-MON-RRRR'
),
-- TRUNC (SYSDATE),
p_datetrack_update_mode => 'UPDATE',
p_assignment_id => l_assg_id,
p_supervisor_id => l_supervisor_id,
p_change_reason => NULL,
p_manager_flag => 'Y',
-- p_bargaining_unit_code => NULL,
-- p_labour_union_member_flag => NULL,
-- p_segment1 => 204,
-- p_segment3 => 'N',
-- p_normal_hours => null,
-- p_frequency => NULL,
-- Output data elements
-- -------------------------------
p_object_version_number => l_object_version_number,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg :=
'Error In Supervisors API ' || SQLERRM;
END;
IF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
ELSE
UPDATE xxat_emp_assignments
SET process_flag = 'Y'
WHERE emp_num = i.emp_num;
END IF;
ELSIF l_error = 1
THEN
UPDATE xxat_emp_assignments
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
END IF;
COMMIT;
--fnd_file.put_line (fnd_file.LOG, '****** Process Validation End ******');
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('****** Process Validation End ******');
COMMIT;
END xxxat_emp_assign_update;
END xxat_emp_assign_pkg;
/
