The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pay_batch_balance_adj_pkg.batch_group_overall_status
(pabg.batch_id, pabg.batch_group_id) status
from pay_adjust_batch_groups pabg
where pabg.batch_id = p_batch_id
union
select batch_status status
from pay_balance_batch_headers
where batch_id = p_batch_id
union
select 'Y' status
from pay_balance_batch_headers bth
where bth.batch_id = p_batch_id
and bth.batch_status = 'T'
order by 1 desc;
select pabl.batch_line_status status
from pay_adjust_batch_lines pabl
where pabl.batch_id = p_batch_id
and pabl.batch_group_id = p_batch_group_id
union
select batch_group_status status
from pay_adjust_batch_groups
where batch_id = p_batch_id
and batch_group_id = p_batch_group_id
union
select 'Y' status
from pay_adjust_batch_groups
where batch_group_id = p_batch_group_id
and batch_id = p_batch_id
and batch_group_status = 'T'
order by 1 desc;
select pabl.batch_line_id
from pay_adjust_batch_lines pabl
where pabl.batch_id = p_batch_id
and pabl.batch_group_id = nvl(p_batch_group_id, pabl.batch_group_id) ;
select pabg.batch_group_id
from pay_adjust_batch_groups pabg
where pabg.batch_id = p_batch_id
and pabg.batch_group_id = nvl(p_batch_group_id, pabg.batch_group_id) ;
delete
from pay_message_lines
where source_type = 'I'
and source_id = cssr.batch_line_id;
delete
from pay_adjust_batch_lines
where batch_line_id = cssr.batch_line_id;
delete
from pay_message_lines
where source_type = 'G'
and source_id = cssr.batch_group_id;
delete
from pay_adjust_batch_groups
where batch_group_id = cssr.batch_group_id;
delete
from pay_message_lines
where source_type = 'H'
and source_id = p_batch_id;
delete
from pay_balance_batch_headers
where batch_id = p_batch_id;
select batch_group_id
from pay_adjust_batch_groups
where batch_id = p_batch_id
and batch_group_id = nvl(p_batch_group_id, batch_group_id)
and batch_group_status = 'T';
select pact.payroll_action_id
from pay_payroll_actions pact
where pact.batch_id = p_batch_group_id
and pact.action_type = 'B';
update pay_adjust_batch_groups
set batch_group_status = 'U'
where batch_group_id = lcsr.batch_group_id;
update pay_adjust_batch_lines
set batch_line_status = 'U'
where batch_group_id = lcsr.batch_group_id;
insert into pay_message_lines
(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text )
values
(pay_message_lines_s.nextval,
null,
l_message_level_tbl(i),
l_message_source_id_tbl(i),
l_message_source_type_tbl(i),
l_message_text_tbl(i)
);
select batch_group_id, consolidation_set_id,
payroll_id, effective_date, prepay_flag
from pay_adjust_batch_groups
where batch_id = p_batch_id
and batch_group_id = nvl(p_batch_group_id, batch_group_id)
and batch_group_status not in ('L','P','T');
select *
from pay_adjust_batch_lines
where batch_id = p_batch_id
and batch_group_id = p_arg_batch_group_id;
select 1 from pay_adjust_batch_groups
where batch_group_status = batch_status_arg
and batch_id = p_batch_id;
update pay_balance_batch_headers
set batch_status = 'P'
where batch_id = p_batch_id;
select batch_name into p_batch_name
from pay_balance_batch_headers
where batch_id = p_batch_id;
update pay_adjust_batch_lines
set batch_line_status = 'U'
where batch_group_id = group_csr.batch_group_id;
update pay_adjust_batch_groups
set batch_group_status = 'P'
where batch_group_id = group_csr.batch_group_id;
delete from pay_message_lines
where source_type = 'G'
and source_id = group_csr.batch_group_id;
delete from pay_message_lines
where source_type = 'I'
and source_id = line_csr.batch_line_id;
select pcs.consolidation_set_id into dummy_consolidation_id
from pay_consolidation_sets pcs
where group_csr.consolidation_set_id = pcs.consolidation_set_id;
select papf.payroll_id into dummy_payroll_id
from pay_all_payrolls_f papf
where group_csr.payroll_id = papf.payroll_id
AND group_csr.effective_date between
papf.effective_start_date and papf.effective_end_date;
insert into pay_message_lines
(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values
(pay_message_lines_s.nextval,
null,
'F',
group_csr.batch_group_id,
'G',
l_error_text);
update pay_payroll_actions
set batch_id = group_csr.batch_group_id
where payroll_action_id=l_payroll_action_id
and action_type ='B';
update pay_adjust_batch_lines
set batch_line_status = 'E'
where batch_line_id = l_message_source_id_tbl(i)
and batch_group_id = group_csr.batch_group_id;
Update the batch_group status as `V` or `T` if line_error is FALSE otherwise as `E`.
*/
if (l_line_error = FALSE) then
---
update pay_adjust_batch_lines
set batch_line_status = p_batch_process_mode
where batch_line_status = 'U'
and batch_group_id = group_csr.batch_group_id;
update pay_adjust_batch_groups
set batch_group_status = p_batch_process_mode
where batch_group_id = group_csr.batch_group_id;
update pay_adjust_batch_lines
set batch_line_status = 'V'
where batch_line_status = 'U'
and batch_group_id = group_csr.batch_group_id;
update pay_adjust_batch_groups
set batch_group_status = 'E'
where batch_group_id = group_csr.batch_group_id;
insert into pay_message_lines
(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values
(pay_message_lines_s.nextval,
null,
l_message_level_tbl(j),
l_message_source_id_tbl(j),
l_message_source_type_tbl(j),
l_message_text_tbl(j)
);
end loop; -- for inserting into pay_message_lines
update pay_adjust_batch_groups
set batch_group_status = 'E'
where batch_group_id = group_csr.batch_group_id;
select pml.source_id id into dummy_msg_source_id
from pay_message_lines pml
where pml.source_id = group_csr.batch_group_id
and source_type = 'G';
insert into pay_message_lines
(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values
(pay_message_lines_s.nextval,
null,
'F',
group_csr.batch_group_id,
'G',
l_error_text);
update pay_balance_batch_headers
set batch_status = 'T'
where batch_id = p_batch_id;
update pay_balance_batch_headers
set batch_status = 'E'
where batch_id = p_batch_id;
update pay_balance_batch_headers
set batch_status = 'V'
where batch_id = p_batch_id;