Oracle HRMS Employee Create API Script
l_eff_start_date date;
l_eff_end_date date;
l_effective_date date := SYSDATE;
l_job_id per_positions.job_id%TYPE;
l_pos_org_id per_positions.position_id%TYPE;
l_validate_mode boolean := FALSE;
l_pos_type fnd_lookup_values.lookup_code%TYPE;
l_avail_id per_shared_types.shared_type_id%TYPE;
l_fte number;
l_max_persons number;
l_probation_period number;
l_probation_unit_desc varchar2 (500);
l_business_group_id number := 82;
l_segment1 varchar2 (20);
l_orcl_pos_title_code fnd_lookup_values.lookup_code%TYPE;
l_orcl_pos_title_code_num number;
l_attribute8 hr_all_positions_f.attribute8%TYPE;
l_attribute9 hr_all_positions_f.attribute9%TYPE;
l_grade_id hr_all_positions_f.entry_grade_id%TYPE;
l_barg_unit_cd hr_all_positions_f.bargaining_unit_cd%TYPE;
l_attribute6 hr_all_positions_f.attribute6%TYPE;
l_working_hours hr_all_positions_f.working_hours%TYPE;
l_frequency hr_all_positions_f.frequency%TYPE;
l_position_id per_positions.position_id%TYPE;
l_object_version_number hr_all_positions_f.object_version_number%TYPE;
l_position_definition_id number;
l_pos_name per_positions.name%TYPE;
l_location_id number;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
CURSOR c1
IS
SELECT DISTINCT
POSITION,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'HR_POSITION_NAME'
AND LANGUAGE = 'US'
AND MEANING = POSITION)
POSITION_1,
JOB,
(SELECT pj.job_id
FROM per_jobs pj, per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND BUSINESS_GROUP_ID = 82
AND pj.NAME = JOB)
job_id,
ORGANIZATION,
(SELECT organization_id
FROM hr_all_organization_units
WHERE UPPER (NAME) LIKE UPPER (ORGANIZATION)
AND business_group_id = 82)
org_id
FROM XXHRL_MY_ASSIGNMENTS
WHERE NVL (pos_process_flag, 'N') = 'N';
BEGIN
l_orcl_pos_title_code_num := 10001;
FOR i IN c1
LOOP
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
l_pos_type := 'NONE';
l_avail_id := 1;
l_position_id := NULL;
l_object_version_number := NULL;
l_eff_start_date := NULL;
l_eff_end_date := NULL;
l_position_definition_id := NULL;
l_pos_name := NULL;
l_location_id := 145; --Location_code = XXXX
hr_position_api.create_position (
p_validate => FALSE,
p_position_id => l_position_id,
p_effective_start_date => l_eff_start_date,
p_effective_end_date => l_eff_end_date,
p_position_definition_id => l_position_definition_id,
p_name => l_pos_name,
p_object_version_number => l_object_version_number,
p_job_id => i.JOB_ID,
p_organization_id => i.ORG_ID,
p_effective_date => '01-Jan-1992',
p_date_effective => '01-Jan-1992',
p_language_code => 'US',
p_availability_status_id => l_avail_id,
p_business_group_id => 82,
p_location_id => l_location_id,
p_position_type => 'NONE',
p_segment1 => TO_CHAR (l_orcl_pos_title_code_num),
p_segment2 => i.POSITION_1
);
DBMS_OUTPUT.put_line ('l_pos_name: ' || l_pos_name);
UPDATE XXHRL_MY_ASSIGNMENTS
SET POS_PROCESS_FLAG = 'Y', POS_PROCESS_MESSAGE = 'Loaded'
-- POS_SEQ_NO = l_orcl_pos_title_code_num
WHERE POSITION = i.POSITION;
l_orcl_pos_title_code_num := l_orcl_pos_title_code_num + 1;
l_count := l_count + 1;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Exception ' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_MY_ASSIGNMENTS
SET POS_PROCESS_FLAG = 'N', POS_PROCESS_MESSAGE = l_error_msg
WHERE POSITION = i.POSITION;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments