My Oracle World: How to upload Positions in Oracle HRMS

NOTE: Kindly update the number of segments of position and Business Group.

TABLE:

CREATE TABLE DEV_POSITION

(

LINE_ID NUMBER(15),

POS_DEFINITION_ID NUMBER(15),

EFFECTIVE_START_DATE DATE,

SEGMENT1 VARCHAR2(150 BYTE),

SEGMENT2 VARCHAR2(150 BYTE),

SEGMENT3 VARCHAR2(150 BYTE),

SEGMENT4 VARCHAR2(150 BYTE),

POSITION_TYPE VARCHAR2(150 BYTE),

POSITION_NATURE VARCHAR2(150 BYTE),

ORG_NAME VARCHAR2(150 BYTE),

JOB_CONCAT VARCHAR2(150 BYTE),

LOC_NAME VARCHAR2(150 BYTE),

HCY_NAME VARCHAR2(150 BYTE),

REP_TO_CONCAT VARCHAR2(150 BYTE),

STATUS VARCHAR2(150 BYTE),

FTE VARCHAR2(150 BYTE),

APPROVE_HEAD_COUNT VARCHAR2(150 BYTE),

PROBATION_DURATION VARCHAR2(150 BYTE),

PROBATION_UNIT VARCHAR2(150 BYTE),

POS_PROCESS_FLAG VARCHAR2(150 BYTE),

HCY_ERROR_DESCRIPTION VARCHAR2(150 BYTE),

POS_ERROR_DESCRIPTION VARCHAR2(150 BYTE),

ELM_ERROR_DESCRIPTION VARCHAR2(150 BYTE),

POSITION_ID NUMBER(15),

OUT_EFF_START_DATE DATE,

OUT_EFF_END_DATE DATE,

POS_DEF_ID NUMBER(15),

POS_NAME VARCHAR2(150 BYTE),

POS_OBJ_VER_NUMBER NUMBER(15),

HCY_OBJ_VER_NUMBER NUMBER(15),

LEM_OBJ_VER_NUMBER NUMBER(15),

HCY_POS_STRUCTURE_ID NUMBER(15),

ELM_STRUC_ELEMENT_ID NUMBER(15),

ELM_OBJ_VER_NUMBER NUMBER(15),

HCY_PROCESS_FLAG VARCHAR2(150 BYTE),

ELM_PROCESS_FLAG VARCHAR2(150 BYTE),

POS_UPD_FLAG VARCHAR2(150 BYTE)

)

PROCEDURE

CREATE OR REPLACE PROCEDURE apps.dev_create_position

CURSOR c_pos

SELECT line_id, effective_start_date, segment1, segment2, segment3,

segment4, position_type, position_nature, org_name, job_concat,

loc_name, status, fte, approve_head_count, probation_duration,

probation_unit, pos_process_flag, pos_error_description,

position_id, out_eff_start_date, out_eff_end_date, pos_def_id,

pos_name, pos_obj_ver_number

FROM dev_position;

--WHERE NVL (pos_process_flag, 'N') <> 'Y' AND pos_error_description IS NULL;

CURSOR c_pos_hcy

SELECT DISTINCT hcy_name, effective_start_date

FROM dev_position;

-- WHERE NVL (hcy_process_flag, 'N') <> 'Y'

-- AND hcy_error_description IS NULL;

CURSOR c_pos_upd

SELECT line_id, position_id, pos_definition_id, effective_start_date

FROM dev_position

WHERE position_id IS NOT NULL

-- AND NVL (pos_process_flag, 'N') = 'Y'

-- AND NVL (pos_upd_flag, 'N') <> 'Y'

ORDER BY line_id;

CURSOR c_pos_hcy_elm

SELECT line_id, effective_start_date, segment1, segment2, segment3,

segment4, rep_to_concat, hcy_name

FROM dev_position

WHERE rep_to_concat IS NOT NULL

--AND NVL (elm_process_flag, 'N') <> 'Y'

AND segment1 IS NOT NULL

AND segment2 IS NOT NULL

AND segment3 IS NOT NULL

AND segment4 IS NOT NULL

--AND segment5 IS NOT NULL

--AND segment6 IS NOT NULL

AND hcy_name IS NOT NULL

AND elm_error_description IS NULL;

lc_c_pos c_pos%ROWTYPE;

lc_c_pos_upd c_pos_upd%ROWTYPE;

lc_c_pos_hcy c_pos_hcy%ROWTYPE;

lc_c_pos_hcy_elm c_pos_hcy_elm%ROWTYPE;

l_position_id NUMBER;

l_effective_start_date DATE;

l_effective_end_date DATE;

l_position_definition_id NUMBER;

l_name VARCHAR2 (240);

l_object_version_number NUMBER;

error_desc VARCHAR2 (240);

lv_pos_flag CHAR (1);

l_pos_upd_flag CHAR (1);

l_supervisor_id VARCHAR2 (20);

l_relief_id VARCHAR2 (20);

l_successor_id VARCHAR2 (20);

l_hcy_process_flag CHAR (1);

l_hcy_error_description VARCHAR2 (1000);

l_hcy_pos_structure_id NUMBER;

l_hcy_obj_ver_number NUMBER;

l_elm_process_flag CHAR (1);

l_elm_error_description VARCHAR2 (240);

l_elm_inactive_org_warning BOOLEAN;

l_elm_struc_element_id NUMBER;

l_elm_obj_ver_number NUMBER;

l_pos_type VARCHAR2 (100);

l_org_id VARCHAR2 (10);

l_job_id VARCHAR2 (10);

l_loc_id VARCHAR2 (10);

l_avail_id VARCHAR2 (10);

l_prob_unit VARCHAR2 (20);

l_child_pos_id VARCHAR2 (20);

l_parent_pos_id VARCHAR2 (20);

l_struc_ver_id VARCHAR2 (20);

l_vgrade_warning BOOLEAN;

l_parent_esd DATE;

l_child_esd DATE;

BEGIN

OPEN c_pos;

LOOP

FETCH c_pos

INTO lc_c_pos;

EXIT WHEN c_pos%NOTFOUND;

BEGIN

---------------------------------------------------------------

-- Position Type

---------------------------------------------------------------

SELECT lookup_code

INTO l_pos_type

FROM hr_lookups

WHERE lookup_type = 'POSITION_TYPE'

AND UPPER (meaning) LIKE UPPER (lc_c_pos.position_type);

---------------------------------------------------------------

-- Organization ID

---------------------------------------------------------------

SELECT organization_id

INTO l_org_id

FROM hr_all_organization_units

WHERE UPPER (NAME) LIKE UPPER (lc_c_pos.org_name);

---------------------------------------------------------------

-- Job ID

---------------------------------------------------------------

SELECT pj.job_id

INTO l_job_id

FROM per_jobs pj

WHERE UPPER (pj.NAME) = UPPER (lc_c_pos.job_concat);

-------------------------------------------------------------

-- Location ID

-------------------------------------------------------------

SELECT location_id

INTO l_loc_id

FROM hr_locations_all

WHERE UPPER (location_code) LIKE UPPER (lc_c_pos.loc_name);

---------------------------------------------------------------

-- Availability ID

---------------------------------------------------------------

SELECT shared_type_id

INTO l_avail_id

FROM per_shared_types_vl

WHERE DECODE

(information2,

NULL, 'Y',

DECODE

(SUBSTR (information2, 1, 1),

'+', DECODE

(SIGN (INSTR (information2,

SYS_CONTEXT ('HR_SESSION_DATA',

'LEG_CODE'

)

1, 'Y',

'N'

'-', DECODE

(SIGN (INSTR (information2,

SYS_CONTEXT ('HR_SESSION_DATA',

'LEG_CODE'

)

1, 'N',

'Y'

)

) = 'Y'

AND lookup_type = 'POSITION_AVAILABILITY_STATUS'

AND UPPER (shared_type_name) LIKE UPPER (lc_c_pos.status);

---------------------------------------------------------------

-- Probation Unit

---------------------------------------------------------------

SELECT lookup_code

INTO l_prob_unit

FROM hr_lookups hr1

WHERE hr1.lookup_type = 'FREQUENCY'

AND hr1.enabled_flag = 'Y'

AND UPPER (hr1.meaning) LIKE UPPER (lc_c_pos.probation_unit);

IF (lc_c_pos.position_nature = 'S')

THEN

hr_position_api.create_position

(p_validate => FALSE,

p_effective_date => lc_c_pos.effective_start_date,

p_date_effective => lc_c_pos.effective_start_date,

p_business_group_id => 2217,

p_segment1 => lc_c_pos.segment1,

p_segment2 => lc_c_pos.segment2,

p_segment3 => lc_c_pos.segment3,

p_segment4 => lc_c_pos.segment4,

p_position_type => l_pos_type,

p_seasonal_flag => 'Y',

p_permanent_temporary_flag => 'N',

p_organization_id => l_org_id,

p_job_id => l_job_id,

p_location_id => l_loc_id,

p_availability_status_id => l_avail_id,

p_fte => lc_c_pos.fte,

p_max_persons => lc_c_pos.approve_head_count,

p_probation_period => lc_c_pos.probation_duration,

p_probation_period_unit_cd => l_prob_unit,

p_position_id => l_position_id,

p_effective_start_date => l_effective_start_date,

p_effective_end_date => l_effective_end_date,

p_position_definition_id => l_position_definition_id,

p_name => l_name,

p_object_version_number => l_object_version_number

);

ELSE

hr_position_api.create_position

(p_validate => FALSE,

p_effective_date => lc_c_pos.effective_start_date,

p_date_effective => lc_c_pos.effective_start_date,

p_business_group_id => 2217,

p_segment1 => lc_c_pos.segment1,

p_segment2 => lc_c_pos.segment2,

p_segment3 => lc_c_pos.segment3,

p_segment4 => lc_c_pos.segment4,

p_position_type => l_pos_type,

p_seasonal_flag => 'N',

p_permanent_temporary_flag => 'Y',

p_organization_id => l_org_id,

p_job_id => l_job_id,

p_location_id => l_loc_id,

p_availability_status_id => l_avail_id,

p_fte => lc_c_pos.fte,

p_max_persons => lc_c_pos.approve_head_count,

p_probation_period => lc_c_pos.probation_duration,

p_probation_period_unit_cd => l_prob_unit,

p_position_id => l_position_id,

p_effective_start_date => l_effective_start_date,

p_effective_end_date => l_effective_end_date,

p_position_definition_id => l_position_definition_id,

p_name => l_name,

p_object_version_number => l_object_version_number

);

END IF;

IF l_position_id IS NOT NULL

THEN

lv_pos_flag := 'Y';

error_desc := 'No Error';

END IF;

IF lv_pos_flag = 'Y'

THEN

UPDATE dev_position

SET pos_error_description = error_desc,

pos_process_flag = lv_pos_flag,

position_id = l_position_id,

pos_name = l_name,

pos_obj_ver_number = l_object_version_number,

out_eff_start_date = l_effective_start_date,

out_eff_end_date = l_effective_end_date,

pos_definition_id = l_position_definition_id

WHERE line_id = lc_c_pos.line_id;

ELSE

error_desc := error_desc || SQLERRM;

UPDATE dev_position

SET pos_error_description = error_desc

-- pos_process_flag = 'N'

WHERE line_id = lc_c_pos.line_id;

END IF;

DBMS_OUTPUT.put_line ('Position ID : ' || l_position_id);

EXCEPTION

WHEN OTHERS

THEN

lv_pos_flag := 'N';

error_desc := error_desc || SQLERRM;

UPDATE dev_position

SET pos_error_description = error_desc

-- pos_process_flag = lv_pos_flag

WHERE line_id = lc_c_pos.line_id;

DBMS_OUTPUT.put_line (SQLERRM);

END;

l_position_id := '';

l_effective_start_date := '';

l_effective_end_date := '';

l_position_definition_id := '';

l_name := '';

l_object_version_number := '';

error_desc := '';

lv_pos_flag := '';

l_pos_type := '';

l_org_id := '';

l_job_id := '';

l_loc_id := '';

l_avail_id := '';

l_prob_unit := '';

-- COMMIT;

END LOOP;

CLOSE c_pos;

---======================================================================

---Supervisor Update

---======================================================================

OPEN c_pos_upd;

LOOP

FETCH c_pos_upd

INTO lc_c_pos_upd;

EXIT WHEN c_pos_upd%NOTFOUND;

l_effective_start_date := '';

l_effective_end_date := '';

l_position_definition_id := '';

l_name := '';

l_object_version_number := '';

l_supervisor_id := '';

l_relief_id := '';

l_successor_id := '';

END LOOP;

CLOSE c_pos_upd;

---======================================================================

---Supervisor Update End

---======================================================================

OPEN c_pos_hcy;

LOOP

FETCH c_pos_hcy

INTO lc_c_pos_hcy;

EXIT WHEN c_pos_hcy%NOTFOUND;

BEGIN

per_position_structure_api.create_pos_struct_and_def_ver

(p_validate => FALSE,

p_effective_date => lc_c_pos_hcy.effective_start_date,

p_name => lc_c_pos_hcy.hcy_name,

p_business_group_id => 2217,

p_primary_position_flag => 'Y',

p_position_structure_id => l_hcy_pos_structure_id,

p_object_version_number => l_hcy_obj_ver_number

);

IF l_hcy_pos_structure_id IS NOT NULL

THEN

l_hcy_process_flag := 'Y';

l_hcy_error_description := 'No Error';

END IF;

IF l_hcy_process_flag = 'Y'

THEN

UPDATE dev_position

SET hcy_process_flag = l_hcy_process_flag,

hcy_error_description = l_hcy_error_description,

hcy_pos_structure_id = l_hcy_pos_structure_id,

hcy_obj_ver_number = l_hcy_obj_ver_number

WHERE hcy_name = lc_c_pos_hcy.hcy_name

AND effective_start_date = lc_c_pos_hcy.effective_start_date;

ELSE

l_hcy_error_description := l_hcy_error_description || SQLERRM;

UPDATE dev_position

SET hcy_error_description = l_hcy_error_description

-- hcy_process_flag = 'N'

WHERE hcy_name = lc_c_pos_hcy.hcy_name

AND effective_start_date = lc_c_pos_hcy.effective_start_date;

END IF;

DBMS_OUTPUT.put_line ( 'Hierarchy Structure ID : '

|| l_hcy_pos_structure_id

);

EXCEPTION

WHEN OTHERS

THEN

l_hcy_error_description := l_hcy_error_description || SQLERRM;

UPDATE dev_position

SET hcy_error_description = l_hcy_error_description

-- hcy_process_flag = 'N'

WHERE hcy_name = lc_c_pos_hcy.hcy_name

AND effective_start_date = lc_c_pos_hcy.effective_start_date;

END;

l_hcy_process_flag := '';

l_hcy_error_description := '';

l_hcy_pos_structure_id := '';

l_hcy_obj_ver_number := '';

END LOOP;

CLOSE c_pos_hcy;

OPEN c_pos_hcy_elm;

LOOP

FETCH c_pos_hcy_elm

INTO lc_c_pos_hcy_elm;

EXIT WHEN c_pos_hcy_elm%NOTFOUND;

BEGIN

l_parent_esd := '';

l_child_esd := '';

----------------------------------------

-- Child Position ID

----------------------------------------

SELECT pap.position_id

INTO l_child_pos_id

FROM per_all_positions pap, per_position_definitions ppd

WHERE pap.position_definition_id = ppd.position_definition_id

AND UPPER (ppd.segment1) = UPPER (lc_c_pos_hcy_elm.segment1)

AND UPPER (ppd.segment2) = UPPER (lc_c_pos_hcy_elm.segment2)

AND UPPER (ppd.segment3) = UPPER (lc_c_pos_hcy_elm.segment3)

AND UPPER (ppd.segment4) = UPPER (lc_c_pos_hcy_elm.segment4);

--AND UPPER (ppd.segment5) = UPPER (lc_c_pos_hcy_elm.segment5)

-- AND UPPER (ppd.segment6) = UPPER (lc_c_pos_hcy_elm.segment6);

----------------------------------------

-- Parent Position ID

----------------------------------------

SELECT pap.position_id

INTO l_parent_pos_id

FROM per_all_positions pap

WHERE UPPER (pap.NAME) = UPPER (lc_c_pos_hcy_elm.rep_to_concat);

----------------------------------------

-- Structure Version ID

----------------------------------------

SELECT ppsv.pos_structure_version_id

INTO l_struc_ver_id

FROM per_pos_structure_versions ppsv

WHERE ppsv.position_structure_id IN (

SELECT pps.position_structure_id

FROM per_position_structures pps

WHERE UPPER (pps.NAME) LIKE

UPPER (lc_c_pos_hcy_elm.hcy_name));

--------------------------------------------

-- Parent Position Effective Date

--------------------------------------------

SELECT effective_start_date

INTO l_parent_esd

FROM hr_all_positions_f hapf

WHERE hapf.position_id = l_parent_pos_id;

--------------------------------------------

-- Child Position Effective Date

--------------------------------------------

SELECT effective_start_date

INTO l_child_esd

FROM hr_all_positions_f hapf

WHERE hapf.position_id = l_child_pos_id;

IF l_parent_esd < l_child_esd

THEN

l_parent_esd := l_child_esd;

END IF;

hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele

(p_validate => FALSE,

p_effective_date => l_parent_esd,

p_parent_position_id => l_parent_pos_id,

p_pos_structure_version_id => l_struc_ver_id,

p_subordinate_position_id => l_child_pos_id,

p_business_group_id => 2217,

p_hr_installed => 'I',

p_pos_structure_element_id => l_elm_struc_element_id,

p_object_version_number => l_elm_obj_ver_number

);

IF l_elm_struc_element_id IS NOT NULL

THEN

l_elm_process_flag := 'Y';

l_elm_error_description := 'No Error';

END IF;

IF l_elm_process_flag = 'Y'

THEN

UPDATE dev_position

SET elm_process_flag = l_elm_process_flag,

elm_error_description = l_elm_error_description,

elm_struc_element_id = l_elm_struc_element_id,

elm_obj_ver_number = l_elm_obj_ver_number

WHERE line_id = lc_c_pos_hcy_elm.line_id;

ELSE

l_elm_error_description := l_elm_error_description || SQLERRM;

UPDATE dev_position

SET elm_error_description = l_elm_error_description

-- elm_process_flag = 'N'

WHERE line_id = lc_c_pos_hcy_elm.line_id;

END IF;

DBMS_OUTPUT.put_line ( 'Hierarchy Structure Element ID : '

|| l_elm_struc_element_id

);

EXCEPTION

WHEN OTHERS

THEN

l_elm_error_description := l_elm_error_description || SQLERRM;

UPDATE dev_position

SET elm_error_description = l_elm_error_description

-- elm_process_flag = 'N'

WHERE line_id = lc_c_pos_hcy_elm.line_id;

END;

l_elm_process_flag := '';

l_elm_error_description := '';

l_elm_struc_element_id := '';

l_elm_obj_ver_number := '';

l_parent_pos_id := '';

l_child_pos_id := '';

l_parent_esd := '';

l_child_esd := '';

END LOOP;

CLOSE c_pos_hcy_elm;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));

END;

My Oracle World

How to upload Positions in Oracle HRMS

1 comment:

Popular Posts

Total Pageviews

Blog Archive