HRMS Assignment Migration – API

DECLARE
p_effective_date                 DATE;
p_datetrack_update_mode          VARCHAR2 (200);
p_assignment_id                  NUMBER;
p_validate                       BOOLEAN;
p_called_from_mass_update        BOOLEAN;
p_grade_id                       NUMBER;
p_position_id                    NUMBER;
p_job_id                         NUMBER  := 62;
p_payroll_id                     NUMBER  :=null;
p_location_id                    NUMBER  :=143;
p_organization_id                NUMBER  := 81;
p_pay_basis_id                   NUMBER  := 61;
p_segment1                       VARCHAR2 (200);
p_segment2                       VARCHAR2 (200);
p_segment3                       VARCHAR2 (200);
p_segment4                       VARCHAR2 (200);
p_segment5                       VARCHAR2 (200);
p_segment6                       VARCHAR2 (200);
p_segment7                       VARCHAR2 (200);
p_segment8                       VARCHAR2 (200);
p_segment9                       VARCHAR2 (200);
p_segment10                      VARCHAR2 (200);
p_segment11                      VARCHAR2 (200);
p_segment12                      VARCHAR2 (200);
p_segment13                      VARCHAR2 (200);
p_segment14                      VARCHAR2 (200);
p_segment15                      VARCHAR2 (200);
p_segment16                      VARCHAR2 (200);
p_segment17                      VARCHAR2 (200);
p_segment18                      VARCHAR2 (200);
p_segment19                      VARCHAR2 (200);
p_segment20                      VARCHAR2 (200);
p_segment21                      VARCHAR2 (200);
p_segment22                      VARCHAR2 (200);
p_segment23                      VARCHAR2 (200);
p_segment24                      VARCHAR2 (200);
p_segment25                      VARCHAR2 (200);
p_segment26                      VARCHAR2 (200);
p_segment27                      VARCHAR2 (200);
p_segment28                      VARCHAR2 (200);
p_segment29                      VARCHAR2 (200);
p_segment30                      VARCHAR2 (200);
p_employment_category            VARCHAR2 (200);
p_concat_segments                VARCHAR2 (200);
p_grade_ladder_pgm_id            NUMBER;
p_supervisor_assignment_id       NUMBER;
p_people_group_id                NUMBER    ;
p_object_version_number          NUMBER;
p_special_ceiling_step_id        NUMBER;
p_group_name                     VARCHAR2 (200);
p_effective_start_date           DATE;
p_effective_end_date             DATE;
p_org_now_no_manager_warning     BOOLEAN;
p_other_manager_warning          BOOLEAN;
p_spp_delete_warning             BOOLEAN;
p_entries_changed_warning        VARCHAR2 (200);
p_tax_district_changed_warning   BOOLEAN;
p_person_ids                     NUMBER;
v_api_error                      VARCHAR (500);
V_ERRCODE                        NUMBER      ;

CURSOR c1
IS
SELECT *
FROM ass_interface v
-- WHERE v.ERROR_MSG IS  NULL
where person_id = 14462 ;

BEGIN
FOR c1_rec IN c1
LOOP

SELECT effective_start_date
INTO p_effective_date
FROM per_all_assignments_f
WHERE person_id = c1_rec.person_id;

p_datetrack_update_mode := 'CORRECTION';

p_validate                := FALSE;
p_called_from_mass_update := NULL;

p_segment1 := NULL;--81;
p_segment2 := NULL;--1;
p_segment3 := NULL;--'Y';
p_segment4 := NULL;--to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS');
p_segment5 := NULL;--'Y';
p_segment6 := NULL;--to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS');
p_segment7 := NULL;
p_segment8 := NULL;
p_segment9 := NULL;
p_segment10 := NULL;
p_segment11 := NULL;
p_segment12 := NULL;
p_segment13 := NULL;
p_segment14 := NULL;
p_segment15 := NULL;
p_segment16 := NULL;
p_segment17 := NULL;
p_segment18 := NULL;
p_segment19 := NULL;
p_segment20 := NULL;
p_segment21 := NULL;
p_segment22 := NULL;
p_segment23 := NULL;
p_segment24 := NULL;
p_segment25 := NULL;
p_segment26 := NULL;
p_segment27 := NULL;
p_segment28 := NULL;
p_segment29 := NULL;
p_segment30 := NULL;
p_concat_segments := NULL;
p_grade_ladder_pgm_id := NULL;
p_supervisor_assignment_id := NULL;

/*SELECT people_group_id
INTO p_people_group_id
FROM pay_people_groups
WHERE UPPER (group_name) = UPPER (c1_rec.department);*/

SELECT object_version_number
INTO p_object_version_number
FROM per_all_assignments_f
WHERE person_id = c1_rec.person_id;
p_group_name := NULL;
p_effective_start_date := NULL;
p_effective_end_date := NULL;
p_org_now_no_manager_warning := NULL;
p_other_manager_warning := NULL;
p_spp_delete_warning := NULL;
p_entries_changed_warning := NULL;
p_tax_district_changed_warning := NULL;

p_assignment_id := c1_rec.ass_id;
p_grade_id := c1_rec.grade_id;
p_position_id := c1_rec.position_id;
--p_employment_category
p_people_group_id := c1_rec.group_id;
p_special_ceiling_step_id := c1_rec.celling_id;

BEGIN
hr_assignment_api.update_emp_asg_criteria
(p_effective_date,
p_datetrack_update_mode,
p_assignment_id,
p_validate,
p_called_from_mass_update,
p_grade_id,
p_position_id,
p_job_id,
p_payroll_id,
p_location_id,
p_organization_id,
p_pay_basis_id,
p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8,
p_segment9,
p_segment10,
p_segment11,
p_segment12,
p_segment13,
p_segment14,
p_segment15,
p_segment16,
p_segment17,
p_segment18,
p_segment19,
p_segment20,
p_segment21,
p_segment22,
p_segment23,
p_segment24,
p_segment25,
p_segment26,
p_segment27,
p_segment28,
p_segment29,
p_segment30,

p_employment_category,
p_concat_segments,
p_grade_ladder_pgm_id,
p_supervisor_assignment_id,
p_people_group_id,
p_object_version_number,
p_special_ceiling_step_id,
p_group_name,
p_effective_start_date,
p_effective_end_date,
p_org_now_no_manager_warning,
p_other_manager_warning,
p_spp_delete_warning,
p_entries_changed_warning,
p_tax_district_changed_warning
);
COMMIT;

UPDATE ass_interface
SET valid_flag = 'Done'
WHERE person_id = c1_rec.person_id ;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_api_error  := SQLERRM;
V_ERRCODE    := SQLCODE;

UPDATE ass_interface
SET error_msg = v_api_error||' '||V_ERRCODE , valid_flag = 'Error'
WHERE person_id = c1_rec.person_id;

COMMIT;
END;
END LOOP;
END;