[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Calls various validation routines on new/updated row
*/
procedure ppt_brui(allow_as_default in varchar2,
category in varchar2,
pre_validation_required in varchar2,
validation_days in number,
validation_value in varchar2) is
status boolean;
select ppt.territory_code,
exa.territory_code
into required_territory,
actual_territory
from pay_payment_types ppt,
pay_external_accounts exa
where exa.external_account_id = account
and ppt.payment_type_id = type;
select ppt.currency_code
into required_currency
from pay_payment_types ppt
where ppt.payment_type_id = type;
select pdb.balance_type_id
into bal_type
from pay_defined_balances pdb
where defined_balance_id = def_balance;
select pbt.currency_code
into bal_currency
from pay_balance_types pbt
where balance_type_id = bal_type;
defined balance id must be inserted into the defined_balance_id column
of the OPM.
--
*/
function gen_balance(leg_code in varchar2) return number is
no_payments_balance exception;
select pdb.defined_balance_id
into defined_balance
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.legislation_code = leg_code
and pdb.business_group_id is null
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbt.balance_type_id = pdb.balance_type_id
and pbd.payments_flag = 'Y'
and pbt.assignment_remuneration_flag = 'Y';
The OPM may only be updated or deleted if it has no pre-payments with an
effective date after the validation start date.
*/
function check_prepay(opm_id in number,
val_start_date in varchar2) return boolean is
dummy varchar2(2);
select 1
into dummy
from dual
where not exists(
select 1
from pay_pre_payments pp
where pp.org_payment_method_id = opm_id
and exists(
select 1
from pay_assignment_actions aa,
pay_payroll_actions pa
where pp.assignment_action_id = aa.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date >=
fnd_date.canonical_to_date(val_start_date)));
On delete check no PPMs depend on the OPM
NOTES
An OPM may not be deleted when there is a PPM which is dependant upon it.
Here DE deletes are what we are talking about.
eg
|-------------PPM-------------------|
|-------------OPM---------|
^
DE Del Invalid
--
*/
function check_ppm(val_start_date in varchar2,
opm_id in varchar2) return boolean is
dummy number;
select 1
into dummy
from dual
where not exists(
select 1
from pay_personal_payment_methods_f ppm
where ppm.org_payment_method_id = opm_id
and ppm.effective_end_date > fnd_date.canonical_to_date(val_start_date));
Check that on delete there are no payrolls using this method as default.
NOTES
Each payroll must have a default payment method. If this OPM is used as
default by any payrolls (DE mode) then it cannot be deleted.
*/
function check_default(opm_id in varchar2,
val_start_date in varchar2) return boolean is
valid_del varchar2(2);
select 'Y'
into valid_del
from dual
where not exists(
select 1
from pay_payrolls_f pp
where pp.default_payment_method_id = opm_id
and pp.effective_end_date > fnd_date.canonical_to_date(val_start_date));
select exa.territory_code
into actual_territory
from pay_external_accounts exa
where exa.external_account_id = account_id;
select nvl(ppt.territory_code, 'NONE'),
ppt.category
into required_territory,
category
from pay_payment_types ppt,
pay_org_payment_methods_f opm
where opm.org_payment_method_id = opm_id
and opm.payment_type_id = ppt.payment_type_id
and fnd_date.canonical_to_date(val_start_date) between
opm.effective_start_date and opm.effective_end_date;
and as an update/insert trigger.
*/
function unique_priority(in_priority in varchar2,
val_start_date in varchar2,
val_end_date in varchar2,
assignment in varchar2) return boolean is
duplicate varchar2(2);
select 'N'
into duplicate
from sys.dual
where not exists(
select 1
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = assignment
and ppm.priority = in_priority
and fnd_date.canonical_to_date(val_start_date) < ppm.effective_end_date
and fnd_date.canonical_to_date(val_end_date) > ppm.effective_start_date);
Checks there are no outstanding pre-payments on delete
NOTES
Before deleting the PPM must make sure there are no PPs which use it after
val_start_date.
*/
function check_pp(ppm_id in varchar2,
val_start_date in varchar2) return boolean is
status varchar2(2);
select 'Y'
into status
from sys.dual
where not exists(
select 1
from pay_payroll_actions pa,
pay_assignment_actions aa,
pay_pre_payments pp
where pp.personal_payment_method_id = ppm_id
and pp.assignment_action_id = aa.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date > fnd_date.canonical_to_date(val_start_date));
Checks there are no outstanding pre-payments on update
NOTES
Before updating the PPM must make sure there are no PPs for the particular
transaction Added for bug 10392383
*/
procedure check_ppm(trans_step_id in varchar2,
status out nocopy varchar2) is
ppm_id Number(9);
SELECT personal_payment_method_id
,effective_date
INTO ppm_id, eff_date
FROM pay_pss_transaction_steps
WHERE transaction_step_id = trans_step_id;
select 'Y'
into status
from sys.dual
where not exists(
select 1
from pay_payroll_actions pa,
pay_assignment_actions aa,
pay_pre_payments pp
where pp.personal_payment_method_id = ppm_id
and pp.assignment_action_id = aa.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date > eff_date);