ALTER SESSION SET nls_language = 'AMERICAN';
set serveroutput on;
DECLARE
i_employee_number VARCHAR2(100) := '123456';
i_effective_date DATE := sysdate;
i_datetrack_update_mode VARCHAR2(30) := 'CORRECTION';
i_assignment_id NUMBER;
l_obj NUMBER;
io_cagr_grade_def_id NUMBER;
o_cagr_concatenated_segments VARCHAR2(240);
o_concatenated_segments VARCHAR2(240);
o_comment_id NUMBER;
o_effective_start_date DATE;
o_effective_end_date DATE;
o_no_managers_warning BOOLEAN;
o_other_manager_warning BOOLEAN;
o_hourly_salaried_warning BOOLEAN;
o_gsp_post_process_warning VARCHAR2(240);
l_people_group_id NUMBER;
l_object_version_number NUMBER;
l_special_ceiling_step_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
l_group_name VARCHAR2(240);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2(240);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2(240);
l_projected_assignment_end DATE;
CURSOR c1 IS
SELECT
papf.employee_number,
paaf.*
FROM
per_all_assignments_f paaf,
per_all_people_f papf,
per_periods_of_service ppos
WHERE
papf.person_id = paaf.person_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND ppos.period_of_service_id = paaf.period_of_service_id
AND ppos.actual_termination_date IS NULL
AND papf.employee_number = nvl(
i_employee_number, papf.employee_number
)
ORDER BY
paaf.effective_start_date;
BEGIN
/*fetch the soft_coding_key_flex_id. The query can be designed based on the requirement.
For below example, the soft coded keyflex structure name is "AE Statutory Info" with segment1 as not NULL*/
SELECT
hscf.soft_coding_keyflex_id
INTO l_soft_coding_keyflex_id
FROM
fnd_id_flex_structures_vl fifsv,
hr_soft_coding_keyflex hscf
WHERE
fifsv.id_flex_structure_name = 'AE Statutory Info'
AND hscf.segment1 IS NOT NULL
AND fifsv.id_flex_num = hscf.id_flex_num;
FOR rec IN c1 LOOP
SELECT
assignment_id,
( object_version_number )
INTO
i_assignment_id,
l_obj
FROM
per_all_assignments_f
WHERE
assignment_id = rec.assignment_id
AND effective_start_date = rec.effective_start_date;
i_effective_date := rec.effective_start_date;
BEGIN
hr_assignment_api.update_emp_asg(
p_effective_date => i_effective_date,
p_datetrack_update_mode => i_datetrack_update_mode,
p_assignment_id => i_assignment_id,
p_object_version_number => l_obj,
p_cagr_grade_def_id => io_cagr_grade_def_id,
p_cagr_concatenated_segments => o_cagr_concatenated_segments,
p_concatenated_segments => o_concatenated_segments,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_comment_id => o_comment_id,
p_effective_start_date => o_effective_start_date,
p_effective_end_date => o_effective_end_date,
p_no_managers_warning => o_no_managers_warning,
p_other_manager_warning => o_other_manager_warning,
p_hourly_salaried_warning => o_hourly_salaried_warning,
p_gsp_post_process_warning => o_gsp_post_process_warning,
p_assignment_number => rec.assignment_number
);
dbms_output.put_line('asg_number '
|| rec.assignment_number
|| ' with eff_st_date '
|| rec.effective_start_date
|| ' updated');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('asg_number '
|| rec.assignment_number
|| ' with eff_st_date '
|| rec.effective_start_date
|| ' err: '
|| sqlerrm);
ROLLBACK;
END;
END LOOP;
END;