-------------------------
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;
/
