Oracle Fusion HCM: Oracle HRMS Position Migration API/Code

For Example We have data in Excel File.
------------------------------------------------------------------------------------------------------------

Position Name	Organization	Job	Type
Absence Clerk	Payroll	Clerk	Single Incumbent
Accounts Assistant	Accounts	Assistant	Single Incumbent
Accounts Manager	Accounts	Manager	Single Incumbent
Accounts Supervisor	Accounting	Supervisor	Single Incumbent
Buyer	Purchasing	Buyer	None
Chairman	viON BG	Chairman	Single Incumbent
Compensation Analyst	Compensation and Benefits	Analyst	Single Incumbent
Compensation and Benefits Assistant	Compensation and Benefits	Assistant	Single Incumbent
Compensation and Benefits Manager	Compensation and Benefits	Manager	Single Incumbent

------------------------------------------------------------------------------------------------------------
Now Using the below code as i take example of "viON Consulting Organization".
------------------------------------------------------------------------------------------------------------
Create Custom table to upload the excel file data.

Script:

CREATE TABLE XXviON_HR_POSITION
(
EFFECTIVE_DATE DATE,
SEGMENT1 VARCHAR2(1000 BYTE),
SEGMENT2 VARCHAR2(1000 BYTE),
SEGMENT3 VARCHAR2(1000 BYTE),
SEGMENT4 VARCHAR2(1000 BYTE),
SEGMENT5 VARCHAR2(1000 BYTE),
SEGMENT6 VARCHAR2(1000 BYTE),
TYPE VARCHAR2(1000 BYTE),
STATUS VARCHAR2(1000 BYTE),
ORGANIZATION VARCHAR2(1000 BYTE),
JOB VARCHAR2(1000 BYTE),
LOCATION VARCHAR2(1000 BYTE),
FTE NUMBER,
MAX_PERSONS NUMBER,
PROCESS_FLAG VARCHAR2(1000 BYTE),
ERR_MESSAGE VARCHAR2(1000 BYTE),
POSITION_ID NUMBER,
POSITION_DEFINITION_ID NUMBER
);
------------------------------------------------------------------------------------------------------------
API:
------------------------------------------------------------------------------------------------------------

Create or Replace
PROCEDURE XXviON_create_position_p(errbuf VARCHAR2,retcode NUMBER)
AS
/**************************************************************************
* Developed by : Mandeep Srivastava
*
* Module : APPS.
*
* Title : Create Position
*
* Purpose : API Wrapper script - Position MIGRATION
* This will take the data residing in XXviON_HR_POSITION table and call
* the following API(s):-
*
* apps.hr_position_api.create_position
**************************************************************************/

CURSOR c_position
IS
SELECT *
FROM XXviON_HR_POSITION
WHERE process_flag ='N'
and segment1='Absence Clerk';

v_business_group_id NUMBER := 7954;--viON BG
v_organization_id NUMBER := 0;
v_err_message VARCHAR2 (1000);
v_job_id NUMBER := 0;
v_location_id NUMBER := 0;
v_available_status_type_id NUMBER := 0;
v_position_type VARCHAR2 (1000);
v_position_id NUMBER := 0;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_position_definition_id NUMBER;
v_name VARCHAR2 (1000);
v_object_version_number NUMBER := 0;
x_effective_date DATE ;

/*CREATE TABLE XXviON_HR_POSITION
(
EFFECTIVE_DATE DATE,
SEGMENT1 VARCHAR2(1000 BYTE),
SEGMENT2 VARCHAR2(1000 BYTE),
SEGMENT3 VARCHAR2(1000 BYTE),
SEGMENT4 VARCHAR2(1000 BYTE),
SEGMENT5 VARCHAR2(1000 BYTE),
SEGMENT6 VARCHAR2(1000 BYTE),
TYPE VARCHAR2(1000 BYTE),
STATUS VARCHAR2(1000 BYTE),
ORGANIZATION VARCHAR2(1000 BYTE),
JOB VARCHAR2(1000 BYTE),
LOCATION VARCHAR2(1000 BYTE),
FTE NUMBER,
MAX_PERSONS NUMBER,
PROCESS_FLAG VARCHAR2(1000 BYTE),
ERR_MESSAGE VARCHAR2(1000 BYTE),
POSITION_ID NUMBER,
POSITION_DEFINITION_ID NUMBER
);*/

BEGIN
FOR x IN c_position
LOOP
BEGIN
SELECT haou.organization_id
INTO v_organization_id
FROM apps.hr_all_organization_units haou
WHERE haou.business_group_id = v_business_group_id
AND UPPER (haou.NAME) =
UPPER (RTRIM (LTRIM (x.ORGANIZATION)))
/* AND x_effective_date BETWEEN haou.date_from
AND NVL (haou.date_to,
'31-Dec-4712')*/ ;
EXCEPTION
WHEN OTHERS
THEN
v_organization_id := NULL;
END;

BEGIN
SELECT pj.job_id
INTO v_job_id
FROM apps.per_jobs pj
WHERE UPPER (pj.NAME) = UPPER (RTRIM (LTRIM (x.job)))
AND pj.business_group_id = v_business_group_id
/* AND x_effective_date BETWEEN pj.date_from
AND NVL (pj.date_to,
'31-Dec-4712') */ ;
EXCEPTION
WHEN OTHERS
THEN
v_job_id := NULL;
END;

BEGIN
SELECT hla.location_id
INTO v_location_id
FROM apps.hr_locations_all hla
WHERE UPPER (hla.location_code) = x.LOCATION;
-- AND hla.business_group_id = v_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
v_location_id := NULL;
END;

BEGIN
SELECT pst.shared_type_id
INTO v_available_status_type_id
FROM apps.per_shared_types pst
WHERE lookup_type = 'POSITION_AVAILABILITY_STATUS'
AND UPPER (pst.shared_type_name) = UPPER (x.status);
EXCEPTION
WHEN OTHERS
THEN
v_available_status_type_id := NULL;
END;

BEGIN
SELECT lookup_code
INTO v_position_type
FROM apps.hr_lookups
WHERE lookup_type = 'POSITION_TYPE'
AND UPPER (meaning) = UPPER (x.TYPE);
EXCEPTION
WHEN OTHERS
THEN
v_position_type := NULL;
END;

BEGIN
v_position_id := NULL;
v_effective_start_date := NULL;
v_effective_end_date := NULL;
v_position_definition_id := NULL;
v_name := NULL;
v_object_version_number := NULL;
apps.hr_position_api.create_position (
p_validate => FALSE,
p_position_id => v_position_id,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date,
p_position_definition_id => v_position_definition_id,
p_name => v_name,
p_object_version_number => v_object_version_number,
p_job_id => v_job_id,
p_organization_id => v_organization_id,
p_effective_date => x.EFFECTIVE_DATE ,
p_date_effective => x.EFFECTIVE_DATE ,
p_language_code => 'US',
p_availability_status_id => v_available_status_type_id,
p_business_group_id => v_business_group_id,
p_entry_step_id => NULL,
p_entry_grade_rule_id => NULL,
p_location_id => v_location_id,
p_pay_freq_payroll_id => NULL,
p_position_transaction_id => NULL,
p_prior_position_id => NULL,
p_relief_position_id => NULL,
p_entry_grade_id => NULL,
p_successor_position_id => NULL,
p_supervisor_position_id => NULL,
p_amendment_date => NULL,
p_amendment_recommendation => NULL,
p_amendment_ref_number => NULL,
p_bargaining_unit_cd => NULL,
p_comments => NULL,
p_current_job_prop_end_date => NULL,
p_current_org_prop_end_date => NULL,
p_avail_status_prop_end_date => NULL,
p_date_end => NULL,
p_earliest_hire_date => NULL,
p_fill_by_date => NULL,
p_frequency => NULL,
p_fte => x.fte,
p_max_persons => x.fte,
p_overlap_period => NULL,
p_overlap_unit_cd => NULL,
p_pay_term_end_day_cd => NULL,
p_pay_term_end_month_cd => NULL,
p_permanent_temporary_flag => NULL,
p_permit_recruitment_flag => NULL,
p_position_type => NVL (v_position_type, 'NONE'),
p_posting_description => NULL,
p_probation_period => NULL,
p_probation_period_unit_cd => NULL,
p_replacement_required_flag => NULL,
p_review_flag => NULL,
p_seasonal_flag => NULL,
p_security_requirements => NULL,
p_status => NULL,
p_term_start_day_cd => NULL,
p_term_start_month_cd => NULL,
p_time_normal_finish => NULL,
p_time_normal_start => NULL,
p_update_source_cd => NULL,
p_working_hours => NULL,
p_works_council_approval_flag => NULL,
p_work_period_type_cd => NULL,
p_work_term_end_day_cd => NULL,
p_work_term_end_month_cd => NULL,
p_proposed_fte_for_layoff => NULL,
p_proposed_date_for_layoff => NULL,
p_pay_basis_id => NULL,
p_supervisor_id => NULL,
-- ,p_copied_to_old_table_flag in varchar2 default null ,
p_information1 => NULL,
p_information2 => NULL,
p_information3 => NULL,
p_information4 => NULL,
p_information5 => NULL,
p_information6 => NULL,
p_information7 => NULL,
p_information8 => NULL,
p_information9 => NULL,
p_information10 => NULL,
p_information11 => NULL,
p_information12 => NULL,
p_information13 => NULL,
p_information14 => NULL,
p_information15 => NULL,
p_information16 => NULL,
p_information17 => NULL,
p_information18 => NULL,
p_information19 => NULL,
p_information20 => NULL,
p_information21 => NULL,
p_information22 => NULL,
p_information23 => NULL,
p_information24 => NULL,
p_information25 => NULL,
p_information26 => NULL,
p_information27 => NULL,
p_information28 => NULL,
p_information29 => NULL,
p_information30 => NULL,
p_information_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_attribute_category => NULL,
p_segment1 => x.SEGMENT1,
-- p_segment2 => x.SEGMENT2,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => NULL,
p_request_id => NULL,
p_program_application_id => NULL,
p_program_id => NULL,
p_program_update_date => NULL,
p_security_profile_id => apps.hr_security.get_security_profile
);

UPDATE XXviON_HR_POSITION t
SET t.process_flag = 'Y',
t.position_id = v_position_id,
t.position_definition_id = v_position_definition_id
WHERE t.SEGMENT1 = x.SEGMENT1 ;
-- COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_err_message := SUBSTR (SQLERRM, 1, 1000);

UPDATE XXviON_HR_POSITION t
SET t.process_flag = 'E', t.err_message = v_err_message
WHERE t.SEGMENT1 = x.SEGMENT1 ;
-- COMMIT;
END;
END LOOP;
COMMIT;
END XXviON_create_position_p;

----------------------------------------------------------------------------------------------------
After Sucessfully execution of API now verify from front hand.

Navigation.:-
work Structure-->Position-->Description

Kindly provide your feedback.

Leave a comment for any query.

Oracle Fusion HCM

Monday, 25 February 2013

Oracle HRMS Position Migration API/Code

1 comment:

Oracle HCM Consultant