Oracle R12 employee update script hr_assignment_api.update_emp_asg in HRMS

--Employee Expense Account update and Assignment

CREATE TABLE xx_emp_code_comb_tbl

(

  emp_code          VARCHAR2(30 BYTE),

  emp_full_name     VARCHAR2(240 BYTE),

  creation_date     DATE,

  ledger            VARCHAR2(240 BYTE),

  code_combination  VARCHAR2(240 BYTE),

  status            VARCHAR2(1 BYTE),

  code_comb_id      NUMBER

)

---Below is the script for update the assignment

 DECLARE

i_effective_date DATE := SYSDATE;

i_assignment_id NUMBER ;--:= 407; -- Pass assignment_id created by hr_employee_api.create_employee

i_assignment_number VARCHAR2(30) :='JG123'; -- Pass parameter assignment_number created by hr_employee_api.create_employee

l_obj NUMBER;

l_assignment_number VARCHAR2(30);

l_supervisor_id NUMBER;

lo_soft_coding_keyflex_id NUMBER;

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;

Cursor C1

IS

SELECT paaf.assignment_number,paaf.object_version_number, assignment_id,a.code_comb_id,paaf.effective_start_date

    FROM per_all_assignments_f paaf,

         xx_emp_code_comb_tbl a,

         per_assignment_status_types past

    WHERE 1=1 --assignment_id = i_assignment_id

    AND paaf.assignment_number = a.emp_code

    AND TRUNC(SYSDATE) BETWEEN TRUNC(paaf.effective_start_date)   AND TRUNC(paaf.effective_end_date)

    AND UPPER(past.user_status) = 'ACTIVE ASSIGNMENT'

    AND past.assignment_status_type_id = paaf.assignment_status_type_id;

BEGIN

 FOR i IN C1 LOOP

  hr_assignment_api.update_emp_asg

    (p_effective_date => i.effective_start_date

    ,p_datetrack_update_mode => 'CORRECTION'

    ,p_assignment_id => i.assignment_id

    ,p_assignment_number => i.assignment_number

    ,p_object_version_number => i.object_version_number

    --,p_supervisor_id => 65 --l_supervisor_id

    ,p_default_code_comb_id => i.code_comb_id

    ,p_set_of_books_id => 2041

    -- OUT Variables

    ,p_soft_coding_keyflex_id => lo_soft_coding_keyflex_id

    ,p_concatenated_segments => o_concatenated_segments

    ,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

    );

 END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Employee Supervisor is updated');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error while updating Employee Supervisor : '||SQLERRM);

END;

/