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
IS
CURSOR c_pos
IS
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
IS
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
IS
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
IS
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'
)
) = '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;
/
