API with Example to Update Basic Salary

Posted on by

API with Example to Update Basic Salary.

CREATE OR REPLACE PROCEDURE APPS.xxdm_basic_sal_proc (

errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_emp_no IN VARCHAR2
)
IS
p_business_group_id NUMBER := 81;

—- XXDM_BASIC_SAL –> Initial Load table
—- XXDM_BASIC_SAL_DEC08 –> Second Load table
CURSOR c1
IS
SELECT *
FROM xxdm_basic_sal xxt
WHERE xxt.emp_no = NVL (p_emp_no, xxt.emp_no)
AND (status IS NULL OR status <> ‘S’)
ORDER BY 1, 3;

————————————————————————————————-
————————————————————————————————-
————————————————————————————————-
————————————————————————————————-
ln_objct_sal_version_number NUMBER;
ln_comment_id NUMBER;
ln_element_entry_id NUMBER;
ln_proposal_id NUMBER;
v_error VARCHAR2 (3000);
v_status VARCHAR2 (10);
ld_effective_date DATE;
ld_effective_end_date DATE;
lb_inv_next_sal_date_warning BOOLEAN;
lb_proposed_salary_warning BOOLEAN;
lb_approved_warning BOOLEAN;
lb_payroll_warning BOOLEAN;
ln_assignment_id NUMBER;
ld_hire_date DATE;
ld_assign_start_date DATE;
ld_sal_change_date DATE;
ld_ch_date DATE;
BEGIN
FOR a IN c1
LOOP
ld_ch_date := NULL;
ln_objct_sal_version_number := NULL;
ln_comment_id := NULL;
ln_element_entry_id := NULL;
ln_proposal_id := NULL;
ld_effective_date := NULL;
ld_effective_end_date := NULL;
ln_assignment_id := NULL;
ld_hire_date := NULL;
ld_assign_start_date := NULL;
v_status := NULL;
v_error := NULL;

BEGIN
DBMS_OUTPUT.put_line (‘Start of execution’);

SELECT paaf.assignment_id, paaf.object_version_number,
ppos.date_start hire_date,
MAX (TRUNC (paaf.effective_start_date)) assign_start_date
INTO ln_assignment_id, ln_objct_sal_version_number,
ld_hire_date,
ld_assign_start_date
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_periods_of_service ppos
WHERE TRIM (papf.employee_number) = TRIM (a.emp_no)
AND a.start_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND paaf.period_of_service_id = ppos.period_of_service_id
AND a.start_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.business_group_id = paaf.business_group_id
AND ppos.person_id = papf.person_id
GROUP BY paaf.assignment_id,
paaf.object_version_number,
ppos.date_start;

IF ln_assignment_id IS NULL
THEN
v_error := ‘Null Assignment’;
v_status := ‘E’;
ELSE
v_status := ‘S’;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_error := ‘No Assg’;
v_status := ‘E’;
END;

DBMS_OUTPUT.put_line (‘Befor calling API Status :’ || v_status);

IF v_status = ‘S’
THEN
BEGIN
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => ln_proposal_id,
p_assignment_id => ln_assignment_id,
p_business_group_id => 81,
p_object_version_number => ln_objct_sal_version_number,
p_multiple_components => ‘Y’,
p_change_date => a.start_date,
p_proposed_salary_n => a.basic_salary,
p_approved => ‘Y’,
p_element_entry_id => ln_element_entry_id,
p_validate => FALSE,
p_inv_next_sal_date_warning => lb_inv_next_sal_date_warning,
p_proposed_salary_warning => lb_proposed_salary_warning,
p_approved_warning => lb_approved_warning,
p_payroll_warning => lb_payroll_warning
);
COMMIT;
v_status := ‘S’;
v_error := NULL;
EXCEPTION
WHEN OTHERS
THEN
v_error := ‘1st API Error : ‘ || SUBSTR (SQLERRM, 1, 200);
v_status := ‘E’;
END;
END IF;

DBMS_OUTPUT.put_line (‘After calling API Status :’ || v_status);

UPDATE xxdm_basic_sal
SET status = v_status,
error = v_error
WHERE emp_no = a.emp_no AND start_date = a.start_date;

COMMIT;
END LOOP;
END xxdm_basic_sal_proc;
/