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;
Mastering database architecture, complex SQL queries, and PL/SQL blocks can get incredibly intense, especially when you are trying to configure database instances and ensure relational integrity all at the same time. When the pressure of university deadlines starts building up alongside tricky compiler errors, finding reliable Oracle Assignment Help in UK services is a complete game-changer. Partnering with experienced coding experts who understand both database optimization and strict UK academic grading criteria is easily the smartest way to keep your technical documentation organized, reduce debugging stress, and secure top marks. Thanks for sharing this helpful post!
ReplyDelete