HRMS（Human Resource Management System）
--人员
per_people_f
--人员分配
per_all_assignments_f
--要素
DECLARE
l_element_name VARCHAR2(240);
l_element_id NUMBER;
l_period_name VARCHAR2(240);
l_period_date DATE;
x_element_type_id NUMBER;
x_effective_start_date DATE;
x_effective_end_date DATE;
x_object_version_number NUMBER;
x_comment_id NUMBER;
x_processing_priority_warning BOOLEAN;
BEGIN
fnd_global.apps_initialize(user_id => 1510,
resp_id => 23991,
resp_appl_id => 800); l_element_name := 'BS交通补贴12';
l_period_name := '9 2016 日历月份'; BEGIN
SELECT ptp.end_date
INTO l_period_date
FROM per_time_periods ptp
WHERE ptp.period_name = l_period_name;
EXCEPTION
WHEN OTHERS THEN
l_period_date := NULL;
END; BEGIN
SELECT petf.element_type_id
INTO l_element_id
FROM pay_element_types_f petf
WHERE petf.element_name = l_element_name
AND l_period_date BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_element_id := NULL;
END; dbms_output.put_line('l_period_date:=' || l_period_date);
dbms_output.put_line('l_element_id:=' || l_element_id); IF (l_element_id IS NULL) THEN
dbms_output.put_line('Create'); pay_element_types_api.create_element_type(p_validate => FALSE,
p_effective_date => l_period_date,
p_classification_id => 118,
p_element_name => l_element_name,
p_input_currency_code => 'CNY',
p_output_currency_code => 'CNY',
p_multiple_entries_allowed_fla => 'Y',
p_processing_type => 'N',
p_business_group_id => 81,
p_element_type_id => x_element_type_id,
p_effective_start_date => x_effective_start_date,
p_effective_end_date => x_effective_end_date,
p_object_version_number => x_object_version_number,
p_comment_id => x_comment_id,
p_processing_priority_warning => x_processing_priority_warning);
-- ELSE
-- pay_element_types_api.update_element_type();
END IF; IF x_processing_priority_warning THEN
dbms_output.put_line('x_processing_priority_warning');
END IF; dbms_output.put_line('x_element_type_id:=' || x_element_type_id);
dbms_output.put_line('x_effective_start_date:=' || x_effective_start_date);
dbms_output.put_line('x_effective_end_date:=' || x_effective_end_date);
dbms_output.put_line('x_element_type_id:=' || x_object_version_number);
dbms_output.put_line('x_comment_id:=' || x_comment_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('导入要素异常！');
END;
创建要素
--链接
/* IF (l_element_link_id IS NULL) THEN
pay_element_link_api.create_element_link(p_validate => FALSE,
p_effective_date => l_period_date,
p_element_type_id => l_element_id,
p_business_group_id => 81,
p_costable_type => 'F',
p_payroll_id => l_payroll_id,
p_people_group_id => l_people_group_id,
p_cost_concat_segments => '01.000000.66010101.0000.00000000.0000.000000.00',
p_balance_concat_segments => '01.000000.22110101.0000.00000000.0000.000000.00',
p_cost_allocation_keyflex_id => 63,
p_balancing_keyflex_id => 76,
p_standard_link_flag => 'N',
p_element_link_id => x_element_link_id,
p_comment_id => x_comment_id,
p_object_version_number => x_object_version_number,
p_effective_start_date => x_effective_start_date,
p_effective_end_date => x_effective_end_date);
dbms_output.put_line('x_element_link_id:=' || x_element_link_id);
dbms_output.put_line('x_comment_id:=' || x_comment_id);
dbms_output.put_line('x_element_type_id:=' || x_object_version_number);
dbms_output.put_line('x_effective_start_date:=' || x_effective_start_date);
dbms_output.put_line('x_effective_end_date:=' || x_effective_end_date);
END IF;*/创建链接
--要素集
DECLARE
l_element_name VARCHAR2(240);
l_element_id NUMBER;
l_period_name VARCHAR2(240);
l_period_date DATE;
l_assign_number VARCHAR2(240);
l_assignment_id NUMBER;
l_payroll_id NUMBER;
l_payroll VARCHAR2(240);
l_element_link_id NUMBER;
l_element_entry_id NUMBER;
l_people_group_id NUMBER;
x_element_link_id NUMBER;
x_effective_start_date DATE;
x_effective_end_date DATE;
x_object_version_number NUMBER;
x_comment_id NUMBER;
x_element_entry_id NUMBER;
x_create_warning BOOLEAN;
BEGIN fnd_global.apps_initialize(user_id => 1510,
resp_id => 23991,
resp_appl_id => 800); l_element_name := 'BS_FJJ_非计件日工';
l_period_name := '9 2016 日历月份';
l_assign_number := ''; BEGIN
SELECT ptp.end_date
INTO l_period_date
FROM per_time_periods ptp
WHERE ptp.period_name = l_period_name;
EXCEPTION
WHEN OTHERS THEN
l_period_date := NULL;
END; BEGIN
SELECT petf.element_type_id
INTO l_element_id
FROM pay_element_types_f petf
WHERE petf.element_name = l_element_name
AND l_period_date BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_element_id := NULL;
END; dbms_output.put_line('l_period_date:=' || l_period_date);
dbms_output.put_line('l_element_id:=' || l_element_id); BEGIN
SELECT paaf.payroll_id,
paaf.assignment_id,
paaf.people_group_id
INTO l_payroll_id,
l_assignment_id,
l_people_group_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_number = l_assign_number
AND l_period_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有分配信息');
WHEN too_many_rows THEN
dbms_output.put_line('存在多条分配信息');
END; dbms_output.put_line('l_payroll_id:=' || l_payroll_id);
dbms_output.put_line('l_assignment_id:=' || l_assignment_id);
dbms_output.put_line('l_people_group_id:=' || l_people_group_id); IF l_payroll_id IS NOT NULL THEN
BEGIN
SELECT pap.payroll_name
INTO l_payroll
FROM pay_all_payrolls_f pap
WHERE pap.payroll_id = l_payroll_id
AND l_period_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有有效工资单');
WHEN too_many_rows THEN
dbms_output.put_line('存在多工资单');
END; dbms_output.put_line('l_payroll:=' || l_payroll);
END IF; --IF l_payroll_id IS NULL THEN IF (l_element_id IS NOT NULL) THEN l_element_link_id := NULL;
BEGIN
SELECT pelf.element_link_id
INTO l_element_link_id
FROM pay_element_links_f pelf
WHERE pelf.element_type_id = l_element_id
AND pelf.payroll_id = l_payroll_id
--AND pelf.people_group_id = l_people_group_id
AND l_period_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
AND cuxhrpkg.show_record('PAY_PEOPLE_GROUPS',
NULL,
pelf.business_group_id,
pelf.people_group_id,
to_char(l_period_date,
'YYYYMMDD'),
'') = 'TRUE'
AND cuxhrpkg.show_ass(l_assignment_id,
pelf.people_group_id,
l_period_date) = 'TRUE'; EXCEPTION
WHEN OTHERS THEN
l_element_link_id := NULL;
END; dbms_output.put_line('l_element_link_id:=' || l_element_link_id); IF (l_element_link_id IS NOT NULL) THEN
BEGIN
SELECT ee.element_entry_id
--ee.object_version_number
INTO l_element_entry_id
-- l_object_version_number
FROM pay_element_entries_f ee,
pay_element_types_f et,
pay_element_links_f el
WHERE el.element_link_id = ee.element_link_id
AND el.element_link_id = l_element_link_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = l_assignment_id
AND l_period_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND l_period_date BETWEEN el.effective_start_date AND el.effective_end_date
AND l_period_date BETWEEN et.effective_start_date AND et.effective_end_date
AND et.multiple_entries_allowed_flag = 'N'
AND ee.entry_type = 'E';
EXCEPTION
WHEN OTHERS THEN
l_element_entry_id := NULL;
END; dbms_output.put_line('l_element_entry_id:=' || l_element_entry_id);
END IF;
END IF; -- IF (l_element_id IS NOT NULL) THEN
IF (l_element_entry_id IS NULL) THEN
pay_element_entry_api.create_element_entry(p_effective_date => l_period_date,
p_business_group_id => 81,
p_assignment_id => l_assignment_id,
p_element_link_id => l_element_link_id,
p_entry_type => 'E',
p_creator_type => 'F',
p_cost_allocation_keyflex_id => NULL,
-- p_input_value_id1 => l_input_id, p_effective_start_date => x_effective_start_date,
p_effective_end_date => x_effective_end_date,
p_element_entry_id => x_element_entry_id,
p_object_version_number => x_object_version_number,
p_create_warning => x_create_warning); dbms_output.put_line('x_effective_start_date:=' || x_effective_start_date);
dbms_output.put_line('x_effective_end_date:=' || x_effective_end_date);
dbms_output.put_line('x_element_entry_id:=' || x_element_entry_id);
dbms_output.put_line('x_object_version_number:=' || x_object_version_number); END IF;
--dbms_output.put_line('l_payroll:=' || l_payroll);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('导入要素异常！');
END;
创建要素集