HRMS Positions Migration

Declare

l_rows_processed NUMBER := 0;
l_commit_point NUMBER := 250;
l_batch_run_number hr_api_batch_message_lines.batch_run_number%TYPE;
l_dummy_line_id hr_api_batch_message_lines.line_id%TYPE;
l_sequence NUMBER := 0;
V_job_id number := 62;
--------------------
-- Out Parameters --
--------------------
l_position_id NUMBER;
l_object_version_number NUMBER;
l_position_definition_id NUMBER;
l_name VARCHAR2(1000);
V_Error varchar2(3000);
--
Cursor Pos is
select * from temp_positions
where upload_status = 'N';

 

BEGIN

FOR i IN pos LOOP

BEGIN
l_position_id := NULL;
l_object_version_number := NULL;
l_position_definition_id := NULL;
l_name := NULL;

--
hr_position_api.create_position(p_validate => FALSE
,p_job_id => 62
,p_organization_id => 81
,p_date_effective => to_date(sysdate,'DD/MM/RRRR')
,p_segment1 => i.position_grade
,p_segment2 => i.position_name
,p_segment3 => i.position_code
,p_segment4 => i.ORGANIZARION
,p_segment5 => i.position_number
,p_location_id => 143
,p_concat_segments =>i.position_grade||'-'||i.position_name||'-'||i.position_code||'-'||i.ORGANIZARION||'-'||i.position_number
,p_position_id => l_position_id
,p_status => i.position_status
,p_object_version_number => l_object_version_number
,p_position_definition_id => l_position_definition_id
,p_name => l_name);

Commit;
update temp_positions
set upload_status = 'Y'
where serial = i.serial;
Commit;
dbms_output.put_line('Done');

EXCEPTION
WHEN OTHERS THEN

V_Error := SQLERRM;

Commit;
update temp_positions
set upload_status = 'N' , error_mess = V_Error
where serial = i.serial;
Commit;
dbms_output.put_line(v_error);

END;
END LOOP;
END ;