[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
pbg.name,
fnd_date.canonical_to_date(p_effective_date),
decode(p_payroll_id,null, ' ',papf.payroll_id, papf.payroll_name)
from per_business_groups pbg,
pay_all_payrolls_f papf
where pbg.business_group_id = p_business_group_id
and pbg.business_group_id = papf.business_group_id
and papf.payroll_id = nvl(to_number(p_payroll_id),papf.payroll_id)
and fnd_date.canonical_to_date(p_effective_date) between
papf.effective_start_date and papf.effective_end_date;
select /*+ ORDERED
use_index(ppf, PER_PEOPLE_F_FK1)
use_index(ppt, PER_PERSON_TYPES_N1) */
paf.assignment_number,
ppf.full_name,
paf.assignment_id
-- hr_gbbal.calc_all_balances(fnd_date.canonical_to_date(p_effective_date), paf.assignment_id, p_def_bal_id ) ni_arrears
from per_all_people_f ppf,
per_person_types ppt,
per_all_assignments_f paf,
pay_all_payrolls_f papf
where ppf.business_group_id = p_business_group_id
and papf.business_group_id = p_business_group_id
and ppt.business_group_id = p_business_group_id
--and papf.payroll_id = nvl(p_payroll_id,papf.payroll_id)
and (p_payroll_id is null
or
papf.payroll_id = p_payroll_id)
and papf.payroll_id = paf.payroll_id
and ppt.system_person_type = 'EMP'
and fnd_date.canonical_to_date(p_effective_date) between ppf.effective_start_date and ppf.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between papf.effective_start_date and papf.effective_end_date
and ppf.effective_end_date between paf.effective_start_date and paf.effective_end_date
and ppt.person_type_id = ppf.person_type_id
and paf.person_id = ppf.person_id
-- and hr_gbbal.calc_all_balances(fnd_date.canonical_to_date(p_effective_date), paf.assignment_id, p_def_bal_id) > 0
order by assignment_number;
select pbg.name,
fnd_date.canonical_to_date(c_effective_date),
decode(c_payroll_id,null, ' ',papf.payroll_id, papf.payroll_name)
from per_business_groups pbg,
pay_all_payrolls_f papf
where pbg.business_group_id = c_business_group_id
and pbg.business_group_id = papf.business_group_id
and papf.payroll_id = nvl(to_number(c_payroll_id),papf.payroll_id)
and fnd_date.canonical_to_date(c_effective_date) between
papf.effective_start_date and papf.effective_end_date;
select distinct
papf.payroll_id,
papf.payroll_name
from per_business_groups pbg,
pay_all_payrolls_f papf
where pbg.business_group_id = c_business_group_id
and pbg.business_group_id = papf.business_group_id
and papf.payroll_id = nvl(to_number(c_payroll_id),papf.payroll_id)
and c_effective_date between
papf.effective_start_date and papf.effective_end_date
order by papf.payroll_name;
select paf.assignment_number,
ppf.full_name,
paf.assignment_id
from per_all_people_f ppf,
per_all_assignments_f paf,
per_assignment_status_types past
where paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and ppf.business_group_id = c_business_group_id
and paf.payroll_id = c_payroll_id
and c_effective_date between ppf.effective_start_date and ppf.effective_end_date
and c_effective_date between paf.effective_start_date and paf.effective_end_date
and paf.person_id = ppf.person_id
order by lpad(substr(assignment_number,1,decode(instr(assignment_number,'-'),0,25,instr(assignment_number,'-')-1)),25,'0') ||
lpad(nvl(substr(assignment_number,decode(instr(assignment_number,'-'),0,null,instr(assignment_number,'-')+1)),'0'),10,'0');
select assignment_number
from per_all_assignments_f
where assignment_id = c_assignment_id;
select locking_action_id, pact.effective_date
from pay_action_interlocks, pay_assignment_actions act, pay_payroll_actions pact
where locked_action_id = c_asg_action_id
and locking_action_id = act.assignment_action_id
and act.payroll_action_id = pact.payroll_action_id;