DECLARE
CURSOR c1
IS
SELECT t.ROWID,
t.*,
per.effective_start_date,
per.person_id
FROM xx_assignment_new_t t, per_people_x per
WHERE second_process_flag = 'N'
AND primary_flag = 'Secondary Assignment'
AND per.employee_number = TO_CHAR (t.employee_number);
l_business_group_id NUMBER := 10665;
l_err_msg VARCHAR2 (1000) := NULL;
l_grade_id NUMBER := NULL;
l_position_id NUMBER := NULL;
l_job_id NUMBER := NULL;
l_organization_id NUMBER := NULL;
l_location_id NUMBER := NULL;
l_people_group_id NUMBER := NULL;
l_special_ceiling_step_id NUMBER := NULL;
l_group_name VARCHAR2 (4000) := NULL;
l_tax_district_changed_warning BOOLEAN;
l_org_now_no_manager_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (4000) := NULL;
l_spp_delete_warning BOOLEAN;
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_other_manager_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (2000) := NULL;
l_employment_category VARCHAR2 (500) := NULL;
l_assignment_id NUMBER := NULL;
l_object_version_number NUMBER := NULL;
l_soft_coding_keyflex_id NUMBER := NULL;
l_assignment_number VARCHAR2 (100) := NULL;
l_assignment_sequence NUMBER := NULL;
l_comment_id NUMBER := NULL;
BEGIN
--Initialize Session
fnd_global.apps_initialize (user_id =>
49431, -- XX_MIGRATION
resp_id =>
65973, -- XX HRMS Manager ( IT )
resp_appl_id => 800 -- Human Resources
);
FOR fetch_details IN c1
LOOP
BEGIN
l_err_msg := NULL;
l_people_group_id := NULL;
l_special_ceiling_step_id := NULL;
l_grade_id := NULL;
l_position_id := NULL;
l_job_id := NULL;
l_organization_id := NULL;
l_group_name := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_org_now_no_manager_warning := NULL;
l_other_manager_warning := NULL;
l_spp_delete_warning := NULL;
l_entries_changed_warning := NULL;
l_tax_district_changed_warning := NULL;
l_concatenated_segments := NULL;
l_employment_category := NULL;
l_assignment_id := NULL;
l_object_version_number := NULL;
l_soft_coding_keyflex_id := 63067;
l_assignment_number := NULL;
l_assignment_sequence := NULL;
l_comment_id := NULL;
--Block to Get Grade
ID
IF fetch_details.grade IS NOT NULL
THEN
BEGIN
SELECT pg.grade_id
INTO l_grade_id
FROM per_grades pg
WHERE UPPER (pg.name) = UPPER (fetch_details.grade)
AND business_group_id = l_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
l_err_msg := 'Grade ID not Found !!!';
END;
END IF;
DBMS_OUTPUT.put_line ('l_grade_id: ' || l_grade_id);
-- Block to Get Job
ID from Position
BEGIN
SELECT
job_id
INTO l_job_id
FROM hr_all_positions_f hapf
WHERE
business_group_id = l_business_group_id
AND attribute1 = fetch_details.position_code
AND availability_status_id = 1
AND TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
AND hapf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_job_id := NULL;
l_err_msg := l_err_msg || ' - ' || 'Job ID not Found !!!';
END;
-- Block to Get
Position ID from Position
BEGIN
SELECT
position_id
INTO l_position_id
FROM hr_all_positions_f hapf
WHERE
business_group_id = l_business_group_id
AND attribute1 = fetch_details.position_code
AND availability_status_id = 1
AND TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
AND hapf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_position_id := NULL;
l_err_msg := l_err_msg || ' - ' || 'Position ID not Found !!!';
END;
-- Block to Get
Organization ID from Position
BEGIN
SELECT
organization_id
INTO l_organization_id
FROM hr_all_positions_f hapf
WHERE
business_group_id = l_business_group_id
AND attribute1 = fetch_details.position_code
AND availability_status_id = 1
AND TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
AND hapf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_organization_id := NULL;
l_err_msg :=
l_err_msg || ' - ' || 'Organization ID not Found !!!';
END;
-- Block to Get
Location ID
BEGIN
SELECT
location_id
INTO l_location_id
FROM hr_locations_all
WHERE UPPER (location_code) = UPPER (fetch_details.location);
EXCEPTION
WHEN OTHERS
THEN
l_location_id := NULL;
l_err_msg :=
l_err_msg || ' - ' || 'l_location_id not Found !!!';
END;
-- Block to Get
Employment Category
BEGIN
SELECT
l.lookup_code
INTO l_employment_category
FROM hr_leg_lookups l
WHERE l.lookup_type = 'EMP_CAT'
AND l.lookup_code IN
(SELECT v.lookup_code
FROM fnd_lookup_values_vl v
WHERE v.lookup_type = 'EMP_CAT'
AND v.attribute1 = 'KU'
OR v.attribute1 = 'ALL')
AND UPPER (l.meaning) =
UPPER (fetch_details.assignment_category)
AND enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_employment_category := NULL;
l_err_msg :=
l_err_msg
|| ' - '
|| ' l_employment_category not
Found !!!';
END;
hr_assignment_api.create_secondary_emp_asg (
p_validate => FALSE,
p_effective_date => fetch_details.effective_start_date,
p_person_id => fetch_details.person_id,
p_organization_id => l_organization_id,
p_grade_id =>
l_grade_id,
p_position_id => l_position_id,
p_job_id => l_job_id,
p_assignment_status_type_id =>
1,
p_payroll_id => NULL,
p_location_id => l_location_id,
p_supervisor_id => NULL,
p_assignment_number =>
l_assignment_number,
p_employment_category =>
l_employment_category,
p_pgp_segment1 => fetch_details.nationality_type, --PPG Nationality Type
p_pgp_segment2 => fetch_details.pension_status, -- PPG Pension Status
p_pgp_segment3 => fetch_details.housing_status, -- PPG Housing Status
p_pgp_segment4 => fetch_details.housing_pension_status, -- PPG Housing Pension Status
p_pgp_segment5 => fetch_details.contract_type -- PPG Contract Type
,
p_ass_attribute_category =>
10665,
p_ass_attribute2 => fetch_details.program_code,
p_ass_attribute3 => fetch_details.fund_code,
p_group_name => l_group_name,
p_concatenated_segments =>
l_concatenated_segments,
p_assignment_id => l_assignment_id,
p_soft_coding_keyflex_id =>
l_soft_coding_keyflex_id,
p_people_group_id => l_people_group_id,
p_object_version_number =>
l_object_version_number,
p_effective_start_date =>
l_effective_start_date,
p_effective_end_date =>
l_effective_end_date,
p_assignment_sequence =>
l_assignment_sequence,
p_comment_id => l_comment_id,
p_other_manager_warning =>
l_other_manager_warning);
COMMIT;
UPDATE xx_assignment_new_t aest
SET aest.second_process_flag = 'Y', second_err_msg = l_err_msg
WHERE aest.ROWID = fetch_details.ROWID;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SUBSTR (SQLERRM, 0, 1000);
UPDATE
xx_assignment_new_t aest
SET aest.second_process_flag = 'N', second_err_msg = l_err_msg
WHERE aest.ROWID = fetch_details.ROWID;
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
