HRMS EX-Employee API

DECLARE
v_api_error                    VARCHAR2 (500);
v_error_msg                    VARCHAR2 (500)  := NULL;
p_object_version_number        NUMBER;
p_period_of_service_id         NUMBER;
p_last_std_process_date_out    DATE;
p_supervisor_warning           BOOLEAN;
p_event_warning                BOOLEAN;
p_interview_warning            BOOLEAN;
p_review_warning               BOOLEAN;
p_recruiter_warning            BOOLEAN;
p_asg_future_changes_warning   BOOLEAN;
p_entries_changed_warning      VARCHAR2 (4000);
p_pay_proposal_warning         BOOLEAN;
p_dod_warningt                 BOOLEAN;
p_dod_warning                  BOOLEAN;
v_p                            NUMBER;
p_leaving_reason               VARCHAR2 (200);

CURSOR mb_emp
IS
SELECT *
FROM xx_ret
WHERE valid_flag IS NULL AND employee_number = '22745';

BEGIN
FOR c1_rec IN mb_emp
LOOP
BEGIN

SELECT DISTINCT person_id
INTO v_p
FROM per_all_people_f
WHERE business_group_id = 103
AND employee_number = c1_rec.employee_number;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
SELECT period_of_service_id
INTO p_period_of_service_id
FROM per_periods_of_service
WHERE person_id = v_p;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
SELECT object_version_number
INTO p_object_version_number
FROM per_periods_of_service
WHERE business_group_id = 103
AND period_of_service_id = p_period_of_service_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;


BEGIN
SELECT lookup_code
INTO p_leaving_reason
FROM fnd_lookup_values_vl
WHERE lookup_type = 'LEAV_REAS' AND meaning = c1_rec.reason;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
hr_ex_employee_api.actual_termination_emp
(p_effective_date                  => TO_DATE(c1_rec.temp_date,'DD/MM/RRRR'),
p_period_of_service_id            => p_period_of_service_id,
p_object_version_number           => p_object_version_number,
p_actual_termination_date         => TO_DATE(c1_rec.actual_date,'DD/MM/RRRR' ),
p_last_standard_process_date      => TO_DATE (c1_rec.actual_date, 'DD/MM/RRRR'),
p_person_type_id                  => 1148,
p_assignment_status_type_id       => 3,
p_last_std_process_date_out       => p_last_std_process_date_out,
p_supervisor_warning              => p_supervisor_warning,
p_event_warning                   => p_event_warning,
p_interview_warning               => p_interview_warning,
p_review_warning                  => p_review_warning,
p_recruiter_warning               => p_recruiter_warning,
p_asg_future_changes_warning      => p_asg_future_changes_warning,
p_entries_changed_warning         => p_entries_changed_warning,
p_pay_proposal_warning            => p_pay_proposal_warning,
p_dod_warning                     => p_dod_warning ,
p_attribute1                      => C1_REC.attribute1,
p_attribute2                      => C1_REC.attribute2,
p_attribute3                      => C1_REC.attribute3,
p_attribute_category              => 'EAME_END_DETAILS' ,
p_leaving_reason                  => p_leaving_reason
);


COMMIT;

UPDATE xx_ret
SET valid_flag = 'Y'
WHERE employee_number = c1_rec.employee_number;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_api_error := SQLERRM;
v_error_msg := v_error_msg || '   ' || v_api_error;

UPDATE xx_ret
SET valid_flag = 'N',
err_msg = v_error_msg
WHERE employee_number = c1_rec.employee_number;

COMMIT;
END;
END LOOP;
END;