CREATE
OR REPLACE PROCEDURE xxx_create_position_p
IS
l_position_id NUMBER (10);
l_position_type VARCHAR2 (100);
l_position_definition_id NUMBER (10);
l_object_version_number NUMBER (3);
l_position VARCHAR2 (1000);
l_job_id NUMBER (10);
l_organization_id NUMBER (7);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_location_id NUMBER (10);
l_effective_date DATE
:= TO_DATE ('01-JAN-1950', 'DD-MON-YYYY');
l_name VARCHAR2 (4000);
l_err_msg VARCHAR2 (2000);
CURSOR get_details
IS
SELECT *
FROM xxx_emp_position_t
WHERE process_flag = 'N';
BEGIN
FOR fetch_details IN get_details
LOOP
--
Block to get Position Type
BEGIN
SELECT lookup_code
INTO l_position_type
FROM hr_lookups
WHERE lookup_type = 'POSITION_TYPE'
AND UPPER (meaning) = UPPER (fetch_details.position_type);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := 'Position
Type not Found !!!';
l_position_type := NULL;
END;
--
Block to Get Organization and Corresponding Location
BEGIN
SELECT haou.organization_id, location_id
INTO l_organization_id, l_location_id
FROM hr_all_organization_units haou
WHERE UPPER (NAME) = UPPER (fetch_details.organization)
AND l_effective_date BETWEEN haou.date_from
AND NVL (haou.date_to,
'31-DEC-4712')
AND haou.business_group_id = l_business_group_id_gl;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg :=
l_err_msg
|| '
- '
|| 'Organization
/ Location not Found !!!';
l_organization_id := NULL;
l_location_id := NULL;
END;
--
Block to Get Job
BEGIN
SELECT pj.job_id
INTO l_job_id
FROM per_jobs pj, per_job_definitions pjd
WHERE UPPER (pj.NAME) = UPPER (fetch_details.job)
AND pj.business_group_id = l_business_group_id_gl
AND l_effective_date BETWEEN pj.date_from
AND NVL (pj.date_to,
'31-DEC-4712')
AND pj.job_definition_id = pjd.job_definition_id;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := l_err_msg || '
- ' || 'Job
not Found !!!';
l_job_id := NULL;
END;
--
Block to Get Position
BEGIN
SELECT UPPER (flex_value)
INTO l_position
FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = 'XXX_HR_POSITION_VS'
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND UPPER (flex_value_meaning) =
UPPER (fetch_details.position);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := l_err_msg || '
- ' || 'Position
not Found !!!';
l_position := NULL;
END;
IF l_position_type IS NOT NULL
AND l_organization_id IS NOT NULL
AND l_job_id IS NOT NULL
AND l_position IS NOT NULL
THEN
BEGIN
l_err_msg := NULL;
l_position_id := NULL;
l_name := NULL;
l_object_version_number := NULL;
l_position_definition_id := NULL;
hr_position_api.
create_position (
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,
p_job_id => l_job_id,
p_organization_id => l_organization_id,
p_effective_date => l_effective_date,
p_date_effective => l_effective_date,
p_language_code => hr_api.userenv_lang,
p_validate => FALSE,
p_availability_status_id => 1,
p_business_group_id => l_business_group_id_gl,
--i.business_group_id,
p_entry_step_id => NULL,
p_entry_grade_rule_id => NULL,
p_location_id => l_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 => fetch_details.fte,
p_max_persons => fetch_details.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 => l_position_type,
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 =>lt 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 => fetch_details.seq_no,
p_segment2 => fetch_details.position,
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 => hr_security.
get_security_profile);
UPDATE xxx_emp_position_t
SET process_flag = 'Y', err_msg = NULL
WHERE UPPER (position) = UPPER (fetch_details.position);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := 'API
Exception: ' || SQLERRM;
UPDATE xxx_emp_position_t
SET process_flag = 'N', err_msg = l_err_msg
WHERE UPPER (position) = UPPER (fetch_details.position);
COMMIT;
END;
ELSE
UPDATE xxx_emp_position_t
SET process_flag = 'N', err_msg = l_err_msg
WHERE UPPER (position) = UPPER (fetch_details.position);
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END xxx_create_position_p;
No comments:
Post a Comment