[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppf.full_name,
ppf.employee_number,
paf.assignment_number
from per_assignments_f paf,
per_people_f ppf
where ppf.person_id = paf.person_id and
sysdate between ppf.effective_start_date and
ppf.effective_end_date and
sysdate between paf.effective_start_date and
paf.effective_end_date and
paf.assignment_id = p_assignment_id;
cursor cur_assignment is select
paf.assignment_id,
to_char(paf.effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(paf.effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,to_char(paf.effective_end_date,'MM/DD/YYYY')) effective_end_date,
paf.location_id location_id,
hl.location_code location_code,
ppf.payroll_id payroll_id,
ppf.payroll_name payroll_name,
paf.pay_basis_id pay_basis_id,
ppb.name name,
ltrim(rtrim(paf.soft_coding_keyflex_id)) sfckid
from per_assignments_f paf,
per_pay_bases ppb,
pay_payrolls_f ppf,
hr_locations hl
where assignment_id = p_assignment_id
and paf.location_id = hl.location_id
and paf.pay_basis_id = ppb.pay_basis_id
and paf.payroll_id = ppf.payroll_id
and sysdate between
ppf.effective_start_date and ppf.effective_end_date;
cursor cur_person is select
person_id, full_name,
to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
from per_people_f
where person_id in (select person_id
from per_assignments_f
where assignment_id = p_assignment_id);
cursor cur_address is select
ltrim(rtrim(primary_flag)) primary_flag,
ltrim(rtrim(region_1)) region_1,
ltrim(rtrim(region_2)) region_2,
ltrim(rtrim(town_or_city)) town_or_city
from per_addresses
where person_id in (select person_id
from per_assignments_f
where assignment_id = p_assignment_id);
cursor cur_federal is select
sui_jurisdiction_code,
to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id;
cursor cur_state is select
jurisdiction_code,
to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
from pay_us_emp_state_tax_rules_f
where assignment_id = p_assignment_id;
cursor cur_county is select
jurisdiction_code,
to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
from pay_us_emp_county_tax_rules_f
where assignment_id = p_assignment_id;
cursor cur_city is select
jurisdiction_code,
to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
from pay_us_emp_city_tax_rules_f
where assignment_id = p_assignment_id;
select
pev.element_entry_id eeid,
pev.screen_entry_value sevl,
to_char(pev.effective_start_date,'MM/DD/YYYY') effective_start_date,
decode(to_char(pev.effective_end_date,'MM/DD/YYYY'),'12/31/4712',
NULL, to_char(pev.effective_end_date,'MM/DD/YYYY')) effective_end_date
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pef,
pay_element_entry_values_f pev
where pet.element_name in ('VERTEX', 'Workers Compensation')
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pef.element_link_id
and pef.element_entry_id = pev.element_entry_id
and pev.screen_entry_value is not null
and pef.effective_start_date = pev.effective_start_date
and pef.effective_end_date = pev.effective_end_date
and pef.assignment_id = p_assignment_id
order by pev.element_entry_id,
pev.effective_start_date;