[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name into l_bg_name
from per_business_groups
where business_group_id = p_business_group_id;
hr_utility.trace('l_batch_name, before selecting' || l_batch_name);
select batch_name into l_batch_name
from pay_balance_batch_headers
where upper(batch_name) = upper(p_batch_name)
and business_group_id = p_business_group_id;
hr_utility.trace('l_batch_name, after selecting' || l_batch_name);
select pay_batch_headers_s.nextval into p_batch_id
from dual;
insert into pay_balance_batch_headers
(batch_id,
batch_name,
business_group_id,
batch_status,
batch_reference,
batch_source,
business_group_name,
payroll_id,
payroll_name,
upload_date,
batch_type)
values (p_batch_id, --pay_balance_batch_headers_s.nextval,
p_batch_name,
p_business_group_id,
'U', -- Unprocessed
p_batch_reference,
p_batch_source,
l_bg_name,
null,
null,
sysdate,
'A');
PROCEDURE update_batch_header(
p_batch_id in number,
p_batch_name in varchar2 default hr_api.g_varchar2,
p_batch_reference in varchar2 default hr_api.g_varchar2,
p_batch_source in varchar2 default hr_api.g_varchar2,
p_batch_status in varchar2 default hr_api.g_varchar2) is
l_batch_status pay_balance_batch_headers.batch_status%TYPE;
SELECT batch_status, business_group_name
INTO l_batch_status, l_bg_name
FROM pay_balance_batch_headers
WHERE batch_id = p_batch_id;
SELECT batch_name
INTO l_batch_name
FROM pay_balance_batch_headers
WHERE upper(batch_name) = upper(p_batch_name)
AND business_group_name = l_bg_name;
UPDATE pay_balance_batch_headers
SET batch_name = p_batch_name,
batch_reference = p_batch_reference,
batch_source = p_batch_source
WHERE batch_id = p_batch_id;
hr_utility.trace('Exception: unable to update pay_balance_batch_headers table.');
end update_batch_header;
PROCEDURE update_batch_groups_lines(
p_batch_id in number,
p_batch_name in varchar2,
p_batch_group_id in number, -- NEW
p_batch_line_id in number, -- NEW
p_effective_date in date, -- effective date
p_employee_id in varchar2, -- Employee Name
p_assignment_id in varchar2, -- assignment_number
p_element_name in varchar2,
p_element_type_id in number,
p_element_link_id in number ,
p_payroll_id in number default null,
p_business_group_id in number,
p_consolidation_set_id in number default null,
p_gre_id in number default null,
p_prepay_flag in varchar2 ,
p_costing_flag in varchar2 ,
p_cost_allocation_keyflex in number default null,
p_concatenated_segments in varchar2 default null,
segment1 in varchar2 default null,
segment2 in varchar2 default null,
segment3 in varchar2 default null,
segment4 in varchar2 default null,
segment5 in varchar2 default null,
segment6 in varchar2 default null,
segment7 in varchar2 default null,
segment8 in varchar2 default null,
segment9 in varchar2 default null,
segment10 in varchar2 default null,
segment11 in varchar2 default null,
segment12 in varchar2 default null,
segment13 in varchar2 default null,
segment14 in varchar2 default null,
segment15 in varchar2 default null,
segment16 in varchar2 default null,
segment17 in varchar2 default null,
segment18 in varchar2 default null,
segment19 in varchar2 default null,
segment20 in varchar2 default null,
segment21 in varchar2 default null,
segment22 in varchar2 default null,
segment23 in varchar2 default null,
segment24 in varchar2 default null,
segment25 in varchar2 default null,
segment26 in varchar2 default null,
segment27 in varchar2 default null,
segment28 in varchar2 default null,
segment29 in varchar2 default null,
segment30 in varchar2 default null,
p_ee_value1 in varchar2 default null,
p_ee_value2 in varchar2 default null,
p_ee_value3 in varchar2 default null,
p_ee_value4 in varchar2 default null,
p_ee_value5 in varchar2 default null,
p_ee_value6 in varchar2 default null,
p_ee_value7 in varchar2 default null,
p_ee_value8 in varchar2 default null,
p_ee_value9 in varchar2 default null,
p_ee_value10 in varchar2 default null,
p_ee_value11 in varchar2 default null,
p_ee_value12 in varchar2 default null,
p_ee_value13 in varchar2 default null,
p_ee_value14 in varchar2 default null,
p_ee_value15 in varchar2 default null,
p_col1 in number default null,
p_col2 in number default null,
p_col3 in number default null,
p_col4 in number default null,
p_col5 in number default null,
p_col_val1 in varchar2 default null,
p_col_val2 in varchar2 default null,
p_col_val3 in varchar2 default null,
p_col_val4 in varchar2 default null,
p_col_val5 in varchar2 default null) IS
l_batch_group_status pay_adjust_batch_groups.batch_group_status%TYPE;
select batch_group_status into l_batch_group_status
from pay_adjust_batch_groups
where batch_group_id = p_batch_group_id;
update pay_adjust_batch_groups
set consolidation_set_id = p_consolidation_set_id,
payroll_id = p_payroll_id,
effective_date = p_effective_date,
prepay_flag = p_prepay_flag
where batch_group_id = p_batch_group_id;
update pay_adjust_batch_lines
set assignment_id = p_assignment_id,
tax_unit_id = p_gre_id,
entry_value1 = p_ee_value1,
entry_value2 = p_ee_value2,
entry_value3 = p_ee_value3,
entry_value4 = p_ee_value4,
entry_value5 = p_ee_value5,
entry_value6 = p_ee_value6,
entry_value7 = p_ee_value7,
entry_value8 = p_ee_value8,
entry_value9 = p_ee_value9,
entry_value10 = p_ee_value10,
entry_value11 = p_ee_value11,
entry_value12 = p_ee_value12,
entry_value13 = p_ee_value13,
entry_value14 = p_ee_value14,
entry_value15 = p_ee_value15,
balance_adj_cost_flag = p_costing_flag,
cost_allocation_keyflex_id = l_cakff_id
where batch_line_id = l_batch_line_id;
hr_utility.trace('Cannot update Batch Lines');
end update_batch_groups_lines;
select batch_group_id, batch_group_status
from pay_adjust_batch_groups
where batch_id = ln_batch_id
and consolidation_set_id = ln_consolidation_set_id
and payroll_id = ln_payroll_id
and effective_date = ln_effective_date
and prepay_flag = ln_prepay_flag;
select batch_line_id, batch_line_status
from pay_adjust_batch_lines
where batch_id = ln_batch_id
and batch_group_id = ln_batch_group_id
and assignment_id = ln_assignment_id
and element_type_id = ln_element_type_id;
SELECT batch_line_status, batch_group_id, assignment_id
FROM pay_adjust_batch_lines
WHERE batch_line_id = p_batch_line_id
AND batch_id = p_batch_id;
SELECT batch_group_status, consolidation_set_id,effective_date, prepay_flag
FROM pay_adjust_batch_groups
WHERE batch_group_id = l_batch_group_id;
select input_value_id,name,rownum
from (select inv.input_value_id,inv.name name,rownum
from pay_input_values_f inv
where inv.element_type_id= cp_element_type_id
and SYSDATE between inv.effective_start_date
and inv.effective_end_date
order by inv.display_sequence,inv.name);
select paf.payroll_id
from per_assignments_f paf
where paf.assignment_number = p_assignment_id
and paf.business_group_id = p_business_group_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date;
select cost_allocation_structure
from per_business_groups
where business_group_id = cp_bg_id;
SELECT hout.organization_id
FROM hr_organization_information hoi,
hr_organization_units hou,
hr_all_organization_units_tl hout
WHERE hoi.organization_id = hou.organization_id
AND hou.organization_id = hout.organization_id
AND hoi.ORG_INFORMATION_CONTEXT = 'CLASS'
AND org_information1 = 'HR_LEGAL'
AND hou.business_group_id = cp_bg_id
AND hout.name = cp_gre_name
AND hout.language = userenv('LANG');
select pcs.consolidation_set_id
from per_assignments_f paf, pay_payrolls_f ppf, pay_consolidation_sets pcs
where paf.assignment_number = p_assignment_id
and paf.business_group_id = p_business_group_id
and sysdate between paf.effective_start_date and paf.effective_end_date
and paf.payroll_id = ppf.payroll_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and ppf.consolidation_set_id = pcs.consolidation_set_id;
select segment1
from per_all_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.business_group_id = p_business_group_id
and sysdate between paf.effective_start_date and paf.effective_end_date
and paf.assignment_number = p_assignment_id
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select element_information4 from pay_element_types_f
where element_type_id = p_element_type_id
and sysdate between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
select segment1, segment11, segment12,
nvl(segment1,nvl(segment11,segment12))
from per_all_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.business_group_id = p_business_group_id
and sysdate between paf.effective_start_date and paf.effective_end_date
and paf.assignment_number = p_assignment_id
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
select paf.assignment_id
from per_all_assignments_f paf,
per_all_people_f ppf
where ltrim(ppf.full_name) = p_employee_id
and ppf.person_id = paf.person_id
and ppf.business_group_id = p_business_group_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.assignment_number = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and paf.business_group_id = p_business_group_id;
select effective_start_date, effective_end_date
from per_all_assignments_f
where assignment_id = l_assignment_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date and effective_end_date;
ex_cannot_update_bg EXCEPTION;
ex_cannot_update_bl EXCEPTION;
SELECT name INTO l_exception_message
FROM pay_input_values_f
WHERE element_type_id= p_element_type_id
AND p_effective_date between effective_start_date and effective_end_date
AND input_value_id = l_exception_id
ORDER BY display_sequence, name;
/* If we need to raise an error if user updates the above mentioned columns we can reuse the following code.
IF l_batch_line_exists = 'Y' THEN
IF ln_old_assignment_id != ln_assignment_id THEN
hr_utility.trace('ln_old_assignment_id: ' || ln_old_assignment_id);
select pay_adjust_batch_groups_s.nextval into l_batch_group_id
from dual;
insert into pay_adjust_batch_groups
(batch_group_id,
batch_id,
batch_group_status,
consolidation_set_id,
payroll_id,
effective_date,
prepay_flag)
values (l_batch_group_id,
p_batch_id,
'U',
l_consolidation_set_id,
ln_payroll_id,
p_effective_date,
l_prepay_flag);
hr_utility.trace('Done inserting into pay_adjust_batch_groups table');
that the batch/group/line is closed for update/insert.
cannot create new batch lines if the group status is
'L' --> ??
'T' --> Transferred
'C' --> Completed
*/
hr_utility.trace('l_batch_group_status : '||l_batch_group_status);
raise ex_cannot_update_bl; -- cannot update a completed/ transferred line
hr_utility.trace('Update of pay_adjust_batch_lines ');
UPDATE pay_adjust_batch_lines
SET entry_value1 = g_ee_value1,
entry_value2 = g_ee_value2,
entry_value3 = g_ee_value3,
entry_value4 = g_ee_value4,
entry_value5 = g_ee_value5,
entry_value6 = g_ee_value6,
entry_value7 = g_ee_value7,
entry_value8 = g_ee_value8,
entry_value9 = g_ee_value9,
entry_value10 = g_ee_value10,
entry_value11 = g_ee_value11,
entry_value12 = g_ee_value12,
entry_value13 = g_ee_value13,
entry_value14 = g_ee_value14,
entry_value15 = g_ee_value15,
balance_adj_cost_flag = l_costing_flag,
cost_allocation_keyflex_id = l_cakff_id,
tax_unit_id = ln_gre_id,
batch_line_status = 'U',
batch_group_id = l_batch_group_id,
assignment_id = ln_assignment_id
WHERE batch_line_id = l_batch_line_id;
hr_utility.trace('inserting into pay_adjust_batch_lines table');
insert into pay_adjust_batch_lines(batch_line_id,
batch_id,
batch_line_status,
batch_group_id,
batch_line_sequence,
assignment_id,
element_type_id,
input_value_id1,
input_value_id2,
input_value_id3,
input_value_id4,
input_value_id5,
input_value_id6,
input_value_id7,
input_value_id8,
input_value_id9,
input_value_id10,
input_value_id11,
input_value_id12,
input_value_id13,
input_value_id14,
input_value_id15,
entry_value1,
entry_value2,
entry_value3,
entry_value4,
entry_value5,
entry_value6,
entry_value7,
entry_value8,
entry_value9,
entry_value10,
entry_value11,
entry_value12,
entry_value13,
entry_value14,
entry_value15,
balance_adj_cost_flag,
cost_allocation_keyflex_id,
tax_unit_id)
values (pay_adjust_batch_lines_s.nextval,
p_batch_id,
'U',
l_batch_group_id,
1,
ln_assignment_id,
p_element_type_id,
g_ip_id1,
g_ip_id2,
g_ip_id3,
g_ip_id4,
g_ip_id5,
g_ip_id6,
g_ip_id7,
g_ip_id8,
g_ip_id9,
g_ip_id10,
g_ip_id11,
g_ip_id12,
g_ip_id13,
g_ip_id14,
g_ip_id15,
g_ee_value1,
g_ee_value2,
g_ee_value3,
g_ee_value4,
g_ee_value5,
g_ee_value6,
g_ee_value7,
g_ee_value8,
g_ee_value9,
g_ee_value10,
g_ee_value11,
g_ee_value12,
g_ee_value13,
g_ee_value14,
g_ee_value15,
l_costing_flag,
l_cakff_id,
ln_gre_id);
raise ex_cannot_update_bg;
when ex_cannot_update_bg then
hr_utility.trace('Batch Group is either Transferred or Complete, cannot update the batch');
when ex_cannot_update_bl then
hr_utility.trace('Batch Line is either Transferred or Complete, cannot update the batch');
SELECT HL.lookup_code
FROM hr_lookups HL
WHERE HL.lookup_type = p_lookup_type
AND UPPER(HL.meaning) = UPPER(p_meaning);
SELECT HL.meaning
FROM hr_lookups HL
WHERE HL.lookup_type = p_lookup_type
AND HL.lookup_code = p_lookup_code;
select inv.UOM,
inv.LOOKUP_TYPE,
inv.VALUE_SET_ID,
etp.input_currency_code
from pay_input_values_f inv,
pay_element_types_f etp
where inv.element_type_id = p_element_type_id
and etp.element_type_id = p_element_type_id
and p_session_date between inv.effective_start_date
and inv.effective_end_date
and p_session_date between etp.effective_start_date
and etp.effective_end_date
order by inv.display_sequence,inv.name;
SELECT piv.uom
, piv.lookup_type
, piv.value_set_id
, pet.input_currency_code
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = p_element_type_id
AND pet.element_type_id = p_element_type_id
AND piv.input_value_id = p_input_value_number
AND p_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
ORDER BY piv.display_sequence,piv.name;
hr_utility.trace('Updated g_ee_value1 :' || g_ee_value1);
hr_utility.trace('Updated g_ee_value2 :' || g_ee_value2);
hr_utility.trace('Updated g_ee_value3 :' || g_ee_value3);
hr_utility.trace('Updated g_ee_value4 :' || g_ee_value4);
hr_utility.trace('Updated g_ee_value5 :' || g_ee_value5);
hr_utility.trace('Updated g_ee_value6 :' || g_ee_value6);
hr_utility.trace('Updated g_ee_value7 :' || g_ee_value7);
hr_utility.trace('Updated g_ee_value8 :' || g_ee_value8);
hr_utility.trace('Updated g_ee_value9 :' || g_ee_value9);
hr_utility.trace('Updated g_ee_value10 :' || g_ee_value10);
hr_utility.trace('Updated g_ee_value11 :' || g_ee_value11);
hr_utility.trace('Updated g_ee_value12 :' || g_ee_value12);
hr_utility.trace('Updated g_ee_value13 :' || g_ee_value13);
hr_utility.trace('Updated g_ee_value14 :' || g_ee_value14);
hr_utility.trace('Updated g_ee_value15 :' || g_ee_value15);