[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from pay_balance_types blt,
pay_defined_balances dfb,
pay_org_payment_methods_f opm
where blt.assignment_remuneration_flag = 'Y'
and blt.balance_type_id = dfb.balance_type_id
and dfb.defined_balance_id = opm.defined_balance_id
and opm.org_payment_method_id = p_org_payment_method_id
and p_effective_date between opm.effective_start_date
and opm.effective_end_date;
select 1
from pay_org_payment_methods_f opm
where opm.org_payment_method_id = p_org_payment_method_id
and p_effective_date
between opm.effective_start_date
and opm.effective_end_date;
select pa.payroll_id,
pa.effective_start_date,
pa.effective_end_date
from per_all_assignments_f pa
where pa.assignment_id = p_assignment_id
and pa.business_group_id + 0 = p_business_group_id
and pa.effective_end_date >= p_effective_date
order by pa.effective_start_date;
select min(popmu.effective_start_date),
max(popmu.effective_end_date)
from pay_org_pay_method_usages_f popmu
where popmu.payroll_id = l_payroll_id
and popmu.org_payment_method_id = p_org_payment_method_id
and popmu.effective_start_date <= l_end_range
and popmu.effective_end_date >= l_start_range;
select pp1.effective_end_date
from per_people_f pp1
where pp1.person_id = p_payee_id
and pp1.effective_start_date >= p_effective_date
and pp1.effective_end_date =
(select max(pp2.effective_end_date)
from per_people_f pp2
where pp2.person_id = p_payee_id
and pp2.effective_start_date >= p_effective_date)
for update nowait;
select ppm.effective_start_date -1
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and ppm.priority = p_priority
and nvl(ppm.run_type_id,-9999) = nvl(p_run_type_id,-9999)
and (ppm.priority <> 1
or exists
(select null
from pay_org_payment_methods_f opm
, pay_defined_balances db
, pay_balance_types bt
where opm.org_payment_method_id = ppm.org_payment_method_id
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and db.defined_balance_id = opm.defined_balance_id
and bt.balance_type_id = db.balance_type_id
and bt.assignment_remuneration_flag = 'Y'
)
)
and (ppm.personal_payment_method_id <>
p_personal_payment_method_id
or p_personal_payment_method_id is null)
and ppm.effective_start_date =
(select min(ppm2.effective_start_date)
from pay_personal_payment_methods_f ppm2
where (ppm2.personal_payment_method_id <>
p_personal_payment_method_id
or p_personal_payment_method_id is null)
and ppm2.assignment_id = p_assignment_id
and ppm2.priority = p_priority
and nvl(ppm2.run_type_id,-9999) = nvl(p_run_type_id,-9999)
and (ppm2.priority <> 1
or exists
(select null
from pay_org_payment_methods_f opm
, pay_defined_balances db
, pay_balance_types bt
where opm.org_payment_method_id = ppm2.org_payment_method_id
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and db.defined_balance_id = opm.defined_balance_id
and bt.balance_type_id = db.balance_type_id
and bt.assignment_remuneration_flag = 'Y'
)
)
and (ppm2.effective_start_date >= p_effective_date or
p_effective_date between
ppm2.effective_start_date and ppm2.effective_end_date
))
for update nowait;
select asg.assignment_type
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date;
if (p_datetrack_mode = 'INSERT') then
chk_assignment_type
(p_assignment_id => p_assignment_id
,p_effective_date => p_effective_date);
if (p_datetrack_mode = 'INSERT' or
p_datetrack_mode = 'DELETE_NEXT_CHANGE' or
p_datetrack_mode = 'FUTURE_CHANGE') then
hr_utility.set_location(' Leaving:'||l_proc, 10);
select null
from pay_org_payment_methods_f opm,
pay_payment_types ppt
where opm.org_payment_method_id = p_org_payment_method_id
and p_effective_date
between opm.effective_start_date
and opm.effective_end_date
and ppt.payment_type_id = opm.payment_type_id;
select null
from pay_org_payment_methods_f opm
where opm.org_payment_method_id = p_org_payment_method_id
and p_effective_date between opm.effective_start_date
and opm.effective_end_date
and opm.defined_balance_id is null;
select pyt.category
from pay_org_payment_methods_f opm
, pay_payment_types pyt
where p_org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = pyt.payment_type_id
and p_effective_date between opm.effective_start_date
and opm.effective_end_date;
select oru.business_group_id
from hr_all_organization_units oru
where oru.organization_id = p_payee_id
and p_effective_date between oru.date_from and
nvl(oru.date_to, hr_api.g_eot);
select null
from hr_organization_information ori
where ori.organization_id = p_payee_id
and ori.org_information_context = 'CLASS'
and ori.org_information1 = 'HR_PAYEE'
and ori.org_information2 = 'Y';
select null
from per_people_f per
where per.person_id = p_payee_id
and p_effective_date between per.effective_start_date
and per.effective_end_date;
select null
from per_contact_relationships ctr,
per_all_assignments_f asg
where ctr.contact_person_id = p_payee_id
and ctr.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and ctr.third_party_pay_flag = 'Y';
select bt.currency_code
from pay_org_payment_methods_f opm
, pay_defined_balances db
, pay_balance_types bt
where org_payment_method_id = p_org_payment_method_id
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and db.defined_balance_id = opm.defined_balance_id
and bt.balance_type_id = db.balance_type_id
;
select null
from pay_org_payment_methods_f opm
, pay_payment_types pyt
where p_org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = pyt.payment_type_id
and p_effective_date between opm.effective_start_date
and opm.effective_end_date
and pyt.category = 'MT';
select null
from pay_external_accounts pea
where pea.external_account_id = p_external_account_id;
select null
from pay_external_accounts pea1,
pay_external_accounts pea2,
pay_org_payment_methods_f opm
where pea1.external_account_id = p_external_account_id
and opm.org_payment_method_id = p_org_payment_method_id
and opm.external_account_id = pea2.external_account_id
and pea1.id_flex_num = pea2.id_flex_num
and exists
(select null
from pay_legislation_rules
where to_char(pea1.id_flex_num) = rule_mode
and rule_type ='E');
select null
from pay_personal_payment_methods_f ppm
where ppm.priority = p_priority
and ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id,-9999) = nvl(p_run_type_id,-9999)
and (ppm.personal_payment_method_id <> p_personal_payment_method_id
or p_personal_payment_method_id is null)
and (ppm.priority <> 1
or exists
(select null
from pay_org_payment_methods_f opm
, pay_defined_balances db
, pay_balance_types bt
where opm.org_payment_method_id = ppm.org_payment_method_id
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and db.defined_balance_id = opm.defined_balance_id
and bt.balance_type_id = db.balance_type_id
and bt.assignment_remuneration_flag = 'Y'
)
)
and ppm.effective_start_date <= p_validation_end_date
and ppm.effective_end_date >= p_validation_start_date;
procedure chk_delete
(p_personal_payment_method_id in
pay_personal_payment_methods_f.personal_payment_method_id%TYPE
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date) is
--
l_exists varchar2(1);
l_proc varchar2(72) := g_package||'chk_delete';
select null
from pay_pre_payments ppy
, pay_assignment_actions asa
, pay_payroll_actions pra
where p_personal_payment_method_id = ppy.personal_payment_method_id
and ppy.assignment_action_id = asa.assignment_action_id
and asa.payroll_action_id = pra.payroll_action_id
and pra.effective_date > p_effective_date;
select null
from pay_pre_payments ppy
where p_personal_payment_method_id = ppy.personal_payment_method_id;
procedure check_garnishment_delete is
l_proc varchar2(72) := g_package||'check_garnishment_delete';
select null
from pay_personal_payment_methods_f ppm,
pay_element_entries_f ele,
pay_org_payment_methods_f opm
where ppm.personal_payment_method_id = p_personal_payment_method_id
and p_effective_date between ppm.effective_start_date
and ppm.effective_end_date
and ppm.org_payment_method_id = opm.org_payment_method_id
and p_effective_date between opm.effective_start_date
and opm.effective_end_date
and opm.defined_balance_id is null
and ele.personal_payment_method_id = ppm.personal_payment_method_id
and ele.effective_start_date <= p_validation_start_date
and ele.effective_end_date >= p_validation_end_date;
hr_utility.set_message(801, 'HR_7849_PPM_ELE_DELETE');
end check_garnishment_delete;
if p_datetrack_mode = 'DELETE' then
open csr_date_eff;
check_garnishment_delete;
check_garnishment_delete;
end chk_delete;
Procedure check_non_updateable_args(p_rec in pay_ppm_shd.g_rec_type
,p_effective_date in date) is
--
l_proc varchar2(72) := g_package||'check_non_updateable_args';
end check_non_updateable_args;
Procedure dt_update_validate
(p_org_payment_method_id in number default hr_api.g_number,
p_assignment_id in number default hr_api.g_number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_personal_payment_method_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
End dt_delete_validate;
Procedure insert_validate
(p_rec in pay_ppm_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in pay_ppm_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'update_validate';
check_non_updateable_args(p_rec => p_rec
,p_effective_date => p_effective_date);
dt_update_validate
(p_org_payment_method_id => p_rec.org_payment_method_id,
p_assignment_id => p_rec.assignment_id,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End update_validate;
Procedure delete_validate
(p_rec in pay_ppm_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'delete_validate';
chk_delete
(p_personal_payment_method_id => p_rec.personal_payment_method_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
);
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_personal_payment_method_id => p_rec.personal_payment_method_id);
End delete_validate;
select pbg.legislation_code
from per_business_groups pbg
, pay_personal_payment_methods_f ppm
where ppm.personal_payment_method_id = p_personal_payment_method_id
and pbg.business_group_id = ppm.business_group_id;