The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
upper(pbd.dimension_name)
,r.route_name
from
pay_balance_dimensions pbd
,ff_routes r
where
pbd.legislation_code = p_legislation_code
and pbd.route_id = r.route_id
and r.route_name in
('Global Assignment Inception To Date',
'Global Assignment Processing Period To Date',
'Global Assignment Calendar Month To Date',
'Global Assignment Calendar Quarter To Date',
'Global Assignment Calendar Year To Date',
'Global Assignment Tax Quarter To Date',
'Global Assignment Tax Year To Date',
'Global Assignment Fiscal Quarter To Date',
'Global Assignment Fiscal Year To Date',
'Global Element Entry Inception To Date',
'Global Element Entry Processing Period To Date',
'Global Element Entry Calendar Month To Date',
'Global Element Entry Calendar Quarter To Date',
'Global Element Entry Calendar Year To Date',
'Global Assignment Within Tax Unit Inception To Date',
'Global Assignment Within Tax Unit Processing Period To Date',
'Global Assignment Within Tax Unit Calendar Month To Date',
'Global Assignment Within Tax Unit Calendar Quarter To Date',
'Global Assignment Within Tax Unit Calendar Year To Date',
'Global Assignment Within Tax Unit Tax Quarter To Date',
'Global Assignment Within Tax Unit Tax Year To Date',
'Global Assignment Within Tax Unit Fiscal Quarter To Date',
'Global Assignment Within Tax Unit Fiscal Year To Date')
union
select
upper(pbd.dimension_name)
,r.route_name
from
pay_balance_dimensions pbd
,pay_dimension_routes pdr
,ff_routes r
where
pbd.legislation_code = p_legislation_code
and pbd.balance_dimension_id = pdr.balance_dimension_id
and pdr.route_id = r.route_id
and pdr.route_type = 'RR'
and r.route_name in
('Global Assignment Inception To Date',
'Global Assignment Processing Period To Date',
'Global Assignment Calendar Month To Date',
'Global Assignment Calendar Quarter To Date',
'Global Assignment Calendar Year To Date',
'Global Assignment Tax Quarter To Date',
'Global Assignment Tax Year To Date',
'Global Assignment Fiscal Quarter To Date',
'Global Assignment Fiscal Year To Date',
'Global Element Entry Inception To Date',
'Global Element Entry Processing Period To Date',
'Global Element Entry Calendar Month To Date',
'Global Element Entry Calendar Quarter To Date',
'Global Element Entry Calendar Year To Date',
'Global Assignment Within Tax Unit Inception To Date',
'Global Assignment Within Tax Unit Processing Period To Date',
'Global Assignment Within Tax Unit Calendar Month To Date',
'Global Assignment Within Tax Unit Calendar Quarter To Date',
'Global Assignment Within Tax Unit Calendar Year To Date',
'Global Assignment Within Tax Unit Tax Quarter To Date',
'Global Assignment Within Tax Unit Tax Year To Date',
'Global Assignment Within Tax Unit Fiscal Quarter To Date',
'Global Assignment Within Tax Unit Fiscal Year To Date')
;
select
min(asg.effective_start_date) start_date
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and ptp.payroll_id = asg.payroll_id
and asg.effective_start_date between ptp.start_date
and ptp.end_date
UNION ALL
--
-- Minimum period start date that is on the assignment.
--
-- Asg |----------------->
-- Prd |----->|----->|----->
--
select
min(ptp.start_date) start_date
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and ptp.payroll_id = asg.payroll_id
and ptp.start_date between asg.effective_start_date
and asg.effective_end_date
order by 1
;
SELECT nvl(GREATEST(MIN(ass.effective_start_date), MIN(ptp.start_date), p_expiry_date)
,END_OF_TIME)
FROM per_assignments_f ass
,per_time_periods ptp
WHERE ass.assignment_id = p_assignment_id
AND ass.effective_start_date <= p_upload_date
AND ass.effective_end_date >= p_expiry_date
AND ptp.payroll_id = ass.payroll_id
AND ptp.start_date BETWEEN ass.effective_start_date and p_upload_date;
SELECT ptp.start_date
FROM per_all_assignments_f ass
,per_time_periods ptp
WHERE ass.assignment_id = p_assignment_id
AND ass.effective_start_date <= p_upload_date
AND ass.effective_end_date >= p_upload_date
AND ptp.payroll_id = ass.payroll_id
AND p_upload_date BETWEEN ptp.start_date
AND ptp.end_date;
SELECT route_name
FROM ff_routes
WHERE route_id =
(SELECT route_id
FROM PAY_BALANCE_DIMENSIONS
WHERE legislation_code= p_legislation_code
AND upper(dimension_name) = upper(p_dimension_name)
AND business_group_id IS NULL);
select min(pee.effective_start_date)
from
pay_element_entries_f pee
where
pee.assignment_id = p_assignment_id
and pee.entry_type = 'E'
and ((pee.element_entry_id = p_original_entry_id
and pee.original_entry_id is null)
or (pee.original_entry_id = p_original_entry_id));
SELECT tba.original_entry_id
FROM pay_temp_balance_adjustments tba,
pay_balance_batch_lines bbl
WHERE tba.batch_line_id = p_test_batch_line_id
AND bbl.batch_line_id = p_batch_line_id
AND nvl(tba.original_entry_id,0) = nvl(bbl.original_entry_id,0);
SELECT tba.tax_unit_id
FROM pay_temp_balance_adjustments tba,
pay_balance_batch_lines bbl
WHERE tba.batch_line_id = p_test_batch_line_id
AND bbl.batch_line_id = p_batch_line_id
AND nvl(tba.tax_unit_id,0) = nvl(bbl.tax_unit_id,0);
SELECT route_name
FROM ff_routes
WHERE route_id =
(SELECT route_id
FROM PAY_BALANCE_DIMENSIONS
WHERE legislation_code= l_legislation_code
AND upper(dimension_name) = upper(l_dimension_name)
AND business_group_id IS NULL);