Oracle HRMS/Payroll element link 要素链接批量设置导入脚本，晒晒太阳
想了想，要素链接设置也挺多的，挺麻烦的。也写了个脚本。分享一下＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝>
-- Created on 2008-7-23 by Mark Gao
DECLARE
-- Local variables here
i INTEGER;
-- Out
o_element_link_id NUMBER;
o_comment_id NUMBER;
o_object_version_number NUMBER;
o_effective_start_date DATE;
o_effective_end_date DATE;
-- In
p_element_type_id NUMBER;
p_payroll_id NUMBER;
CURSOR c_link IS
SELECT *
FROM xxsg_hr_link_imp_temp hl
WHERE hl.p_msg <> 'P'
OR hl.p_msg IS Null;
--And hl.p_link_imp_id>=19;
BEGIN
FOR c1 IN c_link LOOP
-- Get element_type_id
BEGIN
SELECT pet.element_type_id
INTO p_element_type_id
FROM pay_element_types_f_tl pet
WHERE pet.element_name = c1.p_element_name
AND pet.LANGUAGE = 'ZHS';
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
-- Get Payroll_id
BEGIN
SELECT pr.payroll_id
INTO p_payroll_id
FROM pay_all_payrolls_f pr
WHERE pr.payroll_name = c1.p_payroll_name
AND c1.p_effective_date BETWEEN pr.effective_start_date AND
pr.effective_end_date
AND pr.business_group_id = 0;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
-- Test statements here
pay_element_link_api.create_element_link(p_validate => FALSE,
p_effective_date => c1.p_effective_date,
p_element_type_id => p_element_type_id,
p_business_group_id => 0,
p_costable_type => 'N',
p_payroll_id => p_payroll_id,
p_job_id => NULL,
p_position_id => NULL,
p_people_group_id => NULL,
p_cost_allocation_keyflex_id => NULL,
p_organization_id => NULL,
p_location_id => NULL,
p_grade_id => NULL,
p_balancing_keyflex_id => NULL,
p_element_set_id => NULL,
p_pay_basis_id => NULL,
p_link_to_all_payrolls_flag => 'N',
p_standard_link_flag => NULL,
p_transfer_to_gl_flag => NULL,
p_comments => NULL,
p_employment_category => NULL,
p_qualifying_age => NULL,
p_qualifying_length_of_service => NULL,
p_qualifying_units => NULL,
p_attribute_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_cost_segment1 => NULL,
p_cost_segment2 => NULL,
p_cost_segment3 => NULL,
p_cost_segment4 => NULL,
p_cost_segment5 => NULL,
p_cost_segment6 => NULL,
p_cost_segment7 => NULL,
p_cost_segment8 => NULL,
p_cost_segment9 => NULL,
p_cost_segment10 => NULL,
p_cost_segment11 => NULL,
p_cost_segment12 => NULL,
p_cost_segment13 => NULL,
p_cost_segment14 => NULL,
p_cost_segment15 => NULL,
p_cost_segment16 => NULL,
p_cost_segment17 => NULL,
p_cost_segment18 => NULL,
p_cost_segment19 => NULL,
p_cost_segment20 => NULL,
p_cost_segment21 => NULL,
p_cost_segment22 => NULL,
p_cost_segment23 => NULL,
p_cost_segment24 => NULL,
p_cost_segment25 => NULL,
p_cost_segment26 => NULL,
p_cost_segment27 => NULL,
p_cost_segment28 => NULL,
p_cost_segment29 => NULL,
p_cost_segment30 => NULL,
p_balance_segment1 => NULL,
p_balance_segment2 => NULL,
p_balance_segment3 => NULL,
p_balance_segment4 => NULL,
p_balance_segment5 => NULL,
p_balance_segment6 => NULL,
p_balance_segment7 => NULL,
p_balance_segment8 => NULL,
p_balance_segment9 => NULL,
p_balance_segment10 => NULL,
p_balance_segment11 => NULL,
p_balance_segment12 => NULL,
p_balance_segment13 => NULL,
p_balance_segment14 => NULL,
p_balance_segment15 => NULL,
p_balance_segment16 => NULL,
p_balance_segment17 => NULL,
p_balance_segment18 => NULL,
p_balance_segment19 => NULL,
p_balance_segment20 => NULL,
p_balance_segment21 => NULL,
p_balance_segment22 => NULL,
p_balance_segment23 => NULL,
p_balance_segment24 => NULL,
p_balance_segment25 => NULL,
p_balance_segment26 => NULL,
p_balance_segment27 => NULL,
p_balance_segment28 => NULL,
p_balance_segment29 => NULL,
p_balance_segment30 => NULL,
p_cost_concat_segments => NULL,
p_balance_concat_segments => NULL,
p_element_link_id => o_element_link_id,
p_comment_id => o_comment_id,
p_object_version_number => o_object_version_number,
p_effective_start_date => o_effective_start_date,
p_effective_end_date => o_effective_end_date);
dbms_output.put_line('p_link_imp_id=> '||c1.p_link_imp_id);
BEGIN
UPDATE xxsg_hr_link_imp_temp hl
SET hl.p_payroll_id = p_payroll_id,
hl.p_element_type_id = p_element_type_id,
hl.p_element_link_id = o_element_link_id,
hl.p_object_version_number = o_object_version_number,
hl.p_effective_start_date = o_effective_start_date,
hl.p_effective_end_date = o_effective_end_date,
hl.p_msg = 'P'
WHERE hl.p_link_imp_id = c1.p_link_imp_id;
EXCEPTION
WHEN OTHERS THEN
UPDATE xxsg_hr_link_imp_temp hl
SET hl.p_msg = 'Error!'
WHERE hl.p_link_imp_id = c1.p_link_imp_id;
-- COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Null;
-- COMMIT;
页:
[1]