[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from pay_element_types_f pet,
pay_element_classifications pec
where pet.element_type_id = l_batch_line.element_type_id
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and pet.PROCESSING_TYPE = 'R'
and pec.legislation_code is not null
and pec.CLASSIFICATION_name like 'EXTERNAL_REPORTING%'
and pec.legislation_code = 'GB';
select pbl.batch_line_id,
pbl.assignment_id,
pbl.assignment_number,
pbl.element_type_id,
pbl.effective_date,
pbl.effective_start_date
from pay_batch_lines pbl
where pbl.batch_id = p_batch_header_id
and pbl.batch_line_status = 'T'
and (p_assignment_id is null or pbl.assignment_id = p_assignment_id)
union all
select to_number(null) batch_line_id,
to_number(null) assignment_id,
to_char(null) assignment_number,
to_number(null) element_type_id,
to_date(null) effective_date,
to_date(null) effective_start_date
from dual
where not exists
(select null
from pay_batch_headers pbh
where pbh.batch_id = p_batch_header_id);
select pee.element_entry_id, pee.creator_type,
pee.creator_id, pee.effective_start_date
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where pee.creator_id = p_batch_header_id
and pee.creator_type = 'H'
and (pee.source_id is null or pee.source_id = p_asg_action_id)
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = l_batch_line.element_type_id
and pet.element_type_id = pel.element_type_id
and pee.assignment_id = c_assignment_id
and ((pet.processing_type = 'R'
and pee.effective_start_date = l_batch_line.effective_date)
or (pet.processing_type = 'N'
and l_batch_line.effective_date between pee.effective_start_date
and pee.effective_end_date))
and l_batch_line.effective_date between pel.effective_start_date
and pel.effective_end_date
and l_batch_line.effective_date between pet.effective_start_date
and pet.effective_end_date
and l_ele_class_chk is null
union all
select pee.element_entry_id, pee.creator_type,
pee.creator_id, pee.effective_start_date
from per_absence_attendances paa,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where paa.batch_id = p_batch_header_id
and pee.creator_id = paa.absence_attendance_id
and pee.creator_type = 'A'
and (pee.source_id is null or pee.source_id = p_asg_action_id)
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = l_batch_line.element_type_id
and pet.element_type_id = pel.element_type_id
and pee.assignment_id = c_assignment_id
and l_batch_line.effective_date between pel.effective_start_date
and pel.effective_end_date
and l_batch_line.effective_date between pet.effective_start_date
and pet.effective_end_date
and l_ele_class_chk is null
union all
select pee.element_entry_id, pee.creator_type,
pee.creator_id, pee.effective_start_date
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where pee.creator_id = p_batch_header_id
and pee.creator_type = 'H'
and (pee.source_id is null or pee.source_id = p_asg_action_id)
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = l_batch_line.element_type_id
and pet.element_type_id = pel.element_type_id
and pee.assignment_id = c_assignment_id
and pet.processing_type = 'R'
and ((l_batch_line.effective_start_date is not null and pee.effective_start_date=l_batch_line.effective_start_date)
or
(l_batch_line.effective_start_date is null and pee.effective_start_date=l_batch_line.effective_date))
and l_batch_line.effective_date between pel.effective_start_date
and pel.effective_end_date
and l_batch_line.effective_date between pet.effective_start_date
and pet.effective_end_date
and l_ele_class_chk is not null
union all
select pee.element_entry_id, pee.creator_type,
pee.creator_id, pee.effective_start_date
from pay_element_entries_f pee
where pee.creator_id = p_batch_header_id
and pee.creator_type = 'H'
and pee.source_id = p_asg_action_id
and pee.assignment_id = c_assignment_id
and pee.entry_type = 'E'
and l_batch_line.element_type_id is null
union all
select pee.element_entry_id, pee.creator_type,
pee.creator_id, pee.effective_start_date
from per_absence_attendances paa,
pay_element_entries_f pee
where paa.batch_id = p_batch_header_id
and pee.creator_id = paa.absence_attendance_id
and pee.creator_type = 'A'
and pee.source_id = p_asg_action_id
and pee.assignment_id = c_assignment_id
and pee.entry_type = 'E'
and l_batch_line.element_type_id is null;
select pct.batch_control_id
from pay_batch_control_totals pct
where pct.batch_id = p_batch_id;
select 'Y'
from pay_payroll_actions pact
where pact.batch_id = p_batch_id
and pact.action_type = 'BEE';
select pact.payroll_action_id,
pact.business_group_id
from pay_payroll_actions pact
where pact.batch_id = p_batch_header_id
and pact.action_type = 'BEE'
and pact.batch_process_mode = 'TRANSFER';
select 'Y'
from pay_element_entries_f pee
where pee.element_entry_id = p_ee_id
and p_eff_date between pee.effective_start_date
and pee.effective_end_date
and pee.creator_type in ('A','H')
and pee.creator_id is not null
and exists (select null
from pay_element_entries_f pee1
where pee.element_entry_id = pee1.element_entry_id
and (pee1.creator_type <> pee.creator_type
or pee1.creator_id <> pee.creator_id));
select 'Y'
into l_check_batch_id
from pay_batch_headers
where batch_id = p_batch_header_id
and batch_status = 'T';
select business_group_id,
nvl(REJECT_ENTRY_NOT_REMOVED,'N'),
nvl(ROLLBACK_ENTRY_UPDATES,'N'),
DATE_EFFECTIVE_CHANGES
into l_business_group_id,
l_reject_ent_not_removed,
l_allow_rollback,
l_DATE_EFFECTIVE_CHANGES
from pay_batch_headers
where batch_id = p_batch_header_id;
select assignment_id
into l_assignment_id
from per_assignments_f asg
where upper(asg.assignment_number) = upper(l_batch_line.assignment_number)
and asg.business_group_id = l_business_group_id
and ((l_batch_line.effective_start_date is not null
and l_batch_line.effective_start_date between asg.effective_start_date
and asg.effective_end_date)
or (l_batch_line.effective_start_date is null
and l_batch_line.effective_date between asg.effective_start_date
and asg.effective_end_date));
insert_rollback_message('L', l_batch_line.batch_line_id, 'F', false);
delete from per_absence_attendances
where absence_attendance_id = l_creator_id;
hr_entry_api.delete_element_entry('DELETE_NEXT_CHANGE',
l_effective_session_date-1,
l_element_entry_id);
hr_entry_api.delete_element_entry('ZAP',
l_effective_session_date,
l_element_entry_id);
update pay_batch_lines
set batch_line_status = 'U'
where batch_line_id = l_batch_line.batch_line_id;
update pay_batch_lines
set batch_line_status = 'U'
where batch_line_id = l_batch_line.batch_line_id;
update pay_batch_headers
set batch_status = 'U'
where batch_id = p_batch_header_id;
update pay_batch_control_totals
set control_status = 'U'
where batch_id = p_batch_header_id;
insert_rollback_message('H', p_batch_header_id, 'I', false);
update pay_batch_control_totals
set control_status = 'U'
where batch_id = p_batch_header_id;
insert_rollback_message('H', p_batch_header_id, 'I', false);
insert_rollback_message('H', p_batch_header_id, 'F', false);
select pbh.batch_id,
pac.assignment_id,
nvl(pbh.reject_if_results_exists,'Y') reject_if_results_exists,
nvl(pbh.purge_after_rollback,'Y') leave_batch,
pbh.batch_status
from pay_assignment_actions pac,
pay_payroll_actions ppa,
pay_batch_headers pbh
where pac.assignment_action_id = p_asg_action_id
and ppa.payroll_action_id = pac.payroll_action_id
and pbh.batch_id = ppa.batch_id
and ppa.action_type = 'BEE'
union all
select ppa.batch_id,
pac.assignment_id,
'Y' reject_if_results_exists,
'Y' leave_batch,
'T' batch_status
from pay_assignment_actions pac,
pay_payroll_actions ppa
where pac.assignment_action_id = p_asg_action_id
and ppa.payroll_action_id = pac.payroll_action_id
and ppa.action_type = 'BEE'
and not exists
(select null
from pay_batch_headers pbh1
where pbh1.batch_id = ppa.batch_id);
select 'Y'
from dual
where exists
(select null
from pay_batch_control_totals pct
where pct.batch_id = p_batch_id
and pct.control_status <> 'U')
and exists
(select null
from pay_batch_lines pbl
where pbl.batch_id = p_batch_id
and pbl.batch_line_status <> 'T')
and exists
(select null
from pay_batch_headers pbh
where pbh.batch_id = p_batch_id
and pbh.batch_status = 'T');
select pct.batch_control_id
from pay_batch_control_totals pct
where pct.batch_id = p_batch_id;
select decode(parspa.parent_request_id,-1,pa.parent_request_id,parspa.parent_request_id)
from fnd_concurrent_requests pa,
fnd_concurrent_requests parspa,
fnd_concurrent_programs fcp
where pa.request_id = p_request_id
and parspa.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'UNDO_MIX'
and pa.parent_request_id = parspa.request_id;
select argument3, argument5
into l_reject_if_run_results_exist,l_leave_batch
from fnd_concurrent_requests
where request_id = l_parent_id;
update pay_batch_control_totals
set control_status = 'U'
where batch_id = l_rec.batch_id;
select pbh.batch_id,
pbh.business_group_id,
ppa.BATCH_PROCESS_MODE,
nvl(pbh.purge_after_rollback,'N') purge_after_rollback
from pay_payroll_actions ppa,
pay_batch_headers pbh
where ppa.payroll_action_id = p_payroll_action_id
and pbh.batch_id = ppa.batch_id
and ppa.action_type = 'BEE'
and not exists
(select null
from pay_batch_lines pbl
where pbl.batch_id = pbh.batch_id
and pbl.batch_line_status = 'T');
select decode(parspa.parent_request_id,-1,pa.parent_request_id,parspa.parent_request_id)
from fnd_concurrent_requests pa,
fnd_concurrent_requests parspa,
fnd_concurrent_programs fcp
where pa.request_id = p_request_id
and parspa.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'UNDO_MIX'
and pa.parent_request_id = parspa.request_id;
select argument5
into l_leave_batch
from fnd_concurrent_requests
where request_id = l_parent_id;
update pay_batch_headers
set batch_status = 'U'
where batch_id = l_rec.batch_id;
select 'Y' into l_results_found
from pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_entries_f pee
where prr.source_type = 'E'
and pee.element_entry_id = p_element_entry_id
and p_effective_session_date between pee.effective_start_date
and pee.effective_end_date
and prr.source_id = pee.element_entry_id
and prr.status = 'P'
and prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.date_earned between pee.effective_start_date
and pee.effective_end_date ;
select 'Y' into l_results_found
from pay_run_results
where source_type = 'E'
and source_id = p_element_entry_id
and status = 'P';
procedure insert_rollback_message(
p_level in varchar2,
p_batch_id in number,
p_severity in varchar2,
p_fail in boolean
) is
l_line_text pay_message_lines.line_text%type;
hr_utility.set_location('pay_mix_rollback_pkg.insert_rollback_message',10);
end insert_rollback_message;
select par.parameter_value
into param_value
from pay_action_parameters par
where par.parameter_name = p_param_name;
insert into pay_message_lines(
line_sequence,
message_level,
source_id,
source_type,
line_text)
values(
pay_message_lines_s.nextval,
g_message_severity_tbl(i),
g_message_id_tbl(i),
g_message_level_tbl(i),
g_message_tbl(i));
delete from pay_message_lines
where source_id = p_source_id
and source_type = p_msg_type;