[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pet2.output_currency_code,
pet2.element_type_id,
prr2.run_result_id,
paa.assignment_id
from pay_action_interlocks pai2,
pay_run_results prr2,
pay_element_types_f pet2,
pay_payroll_actions ppa,
pay_assignment_actions paa
where pai2.locking_action_id = p_assignment_action
and pai2.locked_action_id = prr2.assignment_action_id
and paa.assignment_action_id = pai2.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr2.element_type_id = pet2.element_type_id
and prr2.source_type = 'E'
and pet2.third_party_pay_only_flag = 'Y'
and ppa.effective_date between
pet2.effective_start_date and pet2.effective_end_date
and prr2.entry_type NOT IN ('R','A');
select ppt.category category,
ppm.personal_payment_method_id personal_method,
pea.prenote_date prenote_date,
ppt.validation_days valid_days,
ppm.percentage percentage,
ppm.amount amount,
opm.org_payment_method_id org_method,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1) cash_rule,
opm.currency_code payment_currency,
ppt.pre_validation_required validation_required,
ppt.validation_value validation_value,
opm.external_account_id external_account_id
-- from hr_lookups hlu,
from pay_external_accounts pea,
pay_payment_types ppt,
pay_org_payment_methods_f opm,
pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment
and ppm.run_type_id is null
and ppm.org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and opm.defined_balance_id = p_def_balance
and ppm.external_account_id = pea.external_account_id (+)
-- and opm.pmeth_information1 = hlu.lookup_code (+)
-- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
-- and NVL(hlu.application_id, 800) = 800
and fnd_date.canonical_to_date(p_effective_date) between
ppm.effective_start_date and ppm.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between
opm.effective_start_date and opm.effective_end_date
order by ppm.person_id,ppm.priority;
select pmu.monetary_unit_id,
pmu.relative_value
from pay_monetary_units pmu,
per_business_groups_perf pbg,
pay_payroll_actions pac,
pay_assignment_actions pas
where pmu.currency_code = currency
and pbg.business_group_id = pac.business_group_id
and pac.payroll_action_id = pas.payroll_action_id
and pas.assignment_action_id = ass_act
and (pmu.business_group_id = pbg.business_group_id
or (pmu.business_group_id is null
and pmu.legislation_code = pbg.legislation_code)
or (pmu.business_group_id is null
and pmu.legislation_code is null)
)
order by pmu.relative_value desc;
select hlu.description
into cash_rule
from hr_lookups hlu
where p_seg1 = hlu.lookup_code
and hlu.lookup_type = 'CASH_ANALYSIS'
and NVL(hlu.application_id, 800) = 800 ;
This is the base unit for inserting coin anal elements for a payment.
The monetary unit is supplied and the routine pays as specified. When the
user calls it, it is possible to leave monetary unit null, and the
routine will determine the correct value. NB all operations on the
amount of cash left are protected from the user.
*/
procedure ins_coin_el(monetary_unit in number,
no_of_units in number,
factor in number) is
amount_to_pay number;
insert into pay_coin_anal_elements(
coin_anal_element_id,
pre_payment_id,
monetary_unit_id,
number_of_monetary_units)
values(
pay_coin_anal_elements_s.nextval,
cash_detail.pre_payment,
monetary_unit,
no_units);
select pmu.monetary_unit_id
into monetary_unit
from pay_monetary_units pmu,
pay_assignment_actions pas,
pay_payroll_actions pac,
per_business_groups_perf pbg
where cash_detail.a_action_id = pas.assignment_action_id
and pac.payroll_action_id = pas.payroll_action_id
and pbg.business_group_id = pac.business_group_id
and pmu.currency_code = cash_detail.currency
and (pmu.business_group_id = pbg.business_group_id
or (pmu.business_group_id is null
and pmu.legislation_code = pbg.legislation_code)
or (pmu.business_group_id is null
and pmu.legislation_code is null)
)
and pmu.relative_value = factor;
(ie use the highest denomination note possible). Note all inserts to
pay_coin_anal_elements are made through pay_coin
*/
--
procedure do_cash_analysis(payment in number,
cash_rule in varchar2,
payment_id in number,
pay_currency in varchar2,
action_id in number,
val_mode in varchar2 default 'TRANSFER',
pay_left in out nocopy number) is
monetary_unit number(16);
select cur.precision
into cash_detail.precision
from fnd_currencies cur
where cur.currency_code = pay_currency;
select pay_pre_payments_s.nextval
into payment_id
from dual;
insert into pay_pre_payments
(pre_payment_id,
personal_payment_method_id,
org_payment_method_id,
value,
base_currency_value,
assignment_action_id,
source_action_id,
prepayment_action_id,
organization_id,
payees_org_payment_method_id,
effective_date)
values (g_pre_payments(cnt).pre_payment_id,
g_pre_payments(cnt).personal_payment_method_id,
g_pre_payments(cnt).org_payment_method_id,
g_pre_payments(cnt).value,
g_pre_payments(cnt).base_currency_value,
g_pre_payments(cnt).assignment_action_id,
g_pre_payments(cnt).source_action_id,
g_pre_payments(cnt).prepayment_action_id,
g_pre_payments(cnt).organization_id,
g_pre_payments(cnt).payees_org_payment_method_id,
g_pre_payments(cnt).effective_date);
g_pre_payments.delete;
select legislation_code
into leg_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select opm.external_account_id
into l_ext_acc
from pay_org_payment_methods_f opm,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = action_id
and ppa.payroll_action_id = paa.payroll_action_id
and opm.org_payment_method_id = org_method_id
and ppa.effective_date between
opm.effective_start_date and opm.effective_end_date;
select pbg.legislation_code, ppa.effective_date
into lgcode, l_effective_date
from pay_assignment_actions asg,
per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = asg.payroll_action_id
and asg.assignment_action_id = action_id
and ppa.business_group_id = pbg.business_group_id;
select cur.precision
into cash_detail.precision
from fnd_currencies cur
where cur.currency_code = pay_currency;
pre-validation record is required, and if so insert one into
pre-payments.
*/
--
function validate_magnetic(personal_method in number,
valid_date in date,
prenote_date in date,
org_method in number,
action_id in number,
validation_value in number,
p_org_id in number default null,
p_payee_opm in number default null,
p_effdate in date default null) return boolean is
--
begin
--
hr_utility.set_location('HR_PRE_PAY.VALIDATE_MAGNETIC',1);
insert into pay_pre_payments
(pre_payment_id,
personal_payment_method_id,
org_payment_method_id,
value,
base_currency_value,
assignment_action_id,
organization_id,
payees_org_payment_method_id,
effective_date)
values (pay_pre_payments_s.nextval,
personal_method,
org_method,
validation_value,
0,
action_id,
p_org_id,
p_payee_opm,
p_effdate);
select ppt.category,
UPPER(translate(pbt.balance_name,' ','_') ||
pbd.database_item_suffix),
pbt.currency_code,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1),
opm.currency_code,
pp.default_payment_method_id,
ppa.effective_date,
pp.negative_pay_allowed_flag,
ppa.payroll_id,
ppa.business_group_id
into default_method.category,
default_method.dbase_item,
balance_currency,
default_method.cash_rule,
default_method.currency,
default_method.payment_method_id,
pre_payment_date,
negative_pay,
payroll,
pay_bg_id
from pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_payment_types ppt,
pay_all_payrolls_f pp,
pay_org_payment_methods_f opm,
pay_payroll_actions ppa
where ppa.payroll_action_id = payroll_action
and ppa.payroll_id = pp.payroll_id
and pp.default_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and opm.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.payments_flag = 'Y'
and pbt.assignment_remuneration_flag = 'Y'
-- and opm.pmeth_information1 = hlu.lookup_code (+)
-- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
-- and NVL(hlu.application_id, 800) = 800
and ppa.effective_date between
pp.effective_start_date and pp.effective_end_date
and ppa.effective_date between
opm.effective_start_date and opm.effective_end_date;
select plr.rule_mode
into g_adjust_ee_source
from pay_legislation_rules plr,
per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = payroll_action
and ppa.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
select ppt.category,
UPPER(translate(pbt.balance_name,' ','_') ||
pbd.database_item_suffix),
pbt.currency_code,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1),
opm.currency_code,
ppa.payroll_id,
pp.negative_pay_allowed_flag,
ppa.effective_date,
ppa.business_group_id
into override.category,
override.dbase_item,
balance_currency,
override.cash_rule,
override.currency,
payroll,
negative_pay,
pre_payment_date,
pay_bg_id
from pay_all_payrolls_f pp,
pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_payment_types ppt,
pay_org_pay_method_usages_f pmu,
pay_org_payment_methods_f opm,
pay_payroll_actions ppa
where ppa.payroll_action_id = payroll_action
and ppa.payroll_id = pmu.payroll_id
and ppa.payroll_id = pp.payroll_id
and pmu.org_payment_method_id = override_method
and pmu.org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and ppt.category <> 'MT'
and opm.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.payments_flag = 'Y'
and pbt.assignment_remuneration_flag = 'Y'
-- and opm.pmeth_information1 = hlu.lookup_code (+)
-- and NVL(hlu.lookup_type ,'CASH_ANALYSIS') = 'CASH_ANALYSIS'
-- and NVL(hlu.application_id, 800) = 800
and ppa.effective_date between
pp.effective_start_date and pp.effective_end_date
and ppa.effective_date between
pmu.effective_start_date and pmu.effective_end_date
and ppa.effective_date between
opm.effective_start_date and opm.effective_end_date;
select plr.rule_mode
into g_adjust_ee_source
from pay_legislation_rules plr,
per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = payroll_action
and ppa.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
select distinct pdb.defined_balance_id,
pbt.currency_code
into def_bal,
balance_currency
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.business_group_id = p_bus_grp
and pbt.assignment_remuneration_flag = 'Y'
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.payments_flag = 'Y';
select distinct legislation_code
into bus_leg
from per_business_groups_perf
where business_group_id = p_bus_grp;
select distinct pdb.defined_balance_id,
pbt.currency_code
into def_bal,
balance_currency
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.business_group_id is null
and pbt.legislation_code = bus_leg
and pbt.assignment_remuneration_flag = 'Y'
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.payments_flag = 'Y';
select org.legislation_code
into leg_code
from pay_assignment_Actions act,
pay_payroll_actions pact,
per_business_groups_perf org
where act.assignment_action_id=p_assignment_action
and pact.payroll_action_id=act.payroll_action_id
and org.business_group_id=pact.business_group_id;
select paa_chd.assignment_action_id,
paa_chd.run_type_id,
prt.run_method,paa_chd.assignment_id
from pay_assignment_actions paa_par,
pay_action_interlocks pai,
pay_assignment_actions paa_chd,
pay_payroll_actions ppa_chd,
pay_run_types_f prt
where paa_par.assignment_action_id = p_assignment_action
and paa_par.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa_chd.assignment_action_id
and paa_chd.run_type_id is not null
and paa_chd.run_type_id = prt.run_type_id
and prt.run_method in ('N','S','P')
and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
and ppa_chd.effective_date between prt.effective_start_date
and prt.effective_end_date
order by paa_chd.action_sequence;
select ppt.category category,
null personal_method,
null prenote_date,
ppt.validation_days valid_days,
rtom.percentage percentage,
rtom.amount amount,
opm.org_payment_method_id org_method,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1) cash_rule,
opm.currency_code payment_currency,
ppt.pre_validation_required validation_required,
ppt.validation_value validation_value,
opm.external_account_id external_account_id
from pay_payment_types ppt,
pay_org_payment_methods_f opm,
pay_run_type_org_methods_f rtom,
pay_org_pay_method_usages_f opmu,
pay_payroll_actions ppa,
pay_assignment_actions paa
where rtom.run_type_id = p_run_type
and rtom.org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and opm.defined_balance_id = p_def_balance
and paa.assignment_action_id = p_assignment_action
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = opmu.payroll_id
and opmu.org_payment_method_id = opm.org_payment_method_id
and fnd_date.canonical_to_date(p_effective_date) between
opmu.effective_start_date and opmu.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between
rtom.effective_start_date and rtom.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between
opm.effective_start_date and opm.effective_end_date
order by rtom.priority;
select ppt.category category,
ppm.personal_payment_method_id personal_method,
pea.prenote_date prenote_date,
ppt.validation_days valid_days,
ppm.percentage percentage,
ppm.amount amount,
opm.org_payment_method_id org_method,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1) cash_rule,
opm.currency_code payment_currency,
ppt.pre_validation_required validation_required,
ppt.validation_value validation_value,
opm.external_account_id external_account_id
from pay_external_accounts pea,
pay_payment_types ppt,
pay_org_payment_methods_f opm,
pay_personal_payment_methods_f ppm,
pay_assignment_actions act
where act.assignment_action_id=p_assignment_action
and ppm.assignment_id = act.assignment_id
and ppm.run_type_id = p_run_type
and ppm.org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and opm.defined_balance_id = p_def_balance
and ppm.external_account_id = pea.external_account_id (+)
and fnd_date.canonical_to_date(p_effective_date) between
ppm.effective_start_date and ppm.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between
opm.effective_start_date and opm.effective_end_date
order by ppm.person_id,ppm.priority;
select org.legislation_code
into leg_code
from pay_assignment_Actions act,
pay_payroll_actions pact,
per_business_groups_perf org
where act.assignment_action_id=p_assignment_Action
and pact.payroll_action_id=act.payroll_action_id
and org.business_group_id=pact.business_group_id;
g_pre_payments.delete;
select paa_chd.assignment_action_id, paa_chd.run_type_id, prt.run_method,paa_chd.assignment_id
from pay_assignment_actions paa_par,
pay_action_interlocks pai,
pay_assignment_actions paa_chd,
pay_payroll_actions ppa_chd,
pay_run_types_f prt
where paa_par.assignment_action_id = p_assignment_action
and paa_par.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa_chd.assignment_action_id
and paa_chd.run_type_id is not null
and paa_chd.run_type_id = prt.run_type_id
and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
and ppa_chd.effective_date between prt.effective_start_date
and prt.effective_end_date
order by paa_chd.action_sequence;
g_pre_payments.delete;
g_pre_payments.delete;
g_pre_payments.delete;
select prr.source_id,
fnd_number.canonical_to_number(rrv.result_value),
prr.status,
paa.assignment_id,
prr.assignment_action_id,
paa.payroll_action_id,
ppa.date_earned
into entry,
res_value,
status,
assign_id,
assign_action_id,
pay_act_id,
run_date_earned
from
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_result_values rrv,
pay_run_results prr2,
pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa
where prr.run_result_id = p_run_result
and paa.assignment_action_id= prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and pet.element_type_id = prr2.element_type_id
and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
and prr.source_id = prr2.source_id
and prr.assignment_action_id = prr2.assignment_action_id
and prr2.source_type in ('E','I')
and prr2.entry_type not in ('R','A')
and prr2.run_result_id = rrv.run_result_id
and rrv.input_value_id = piv.input_value_id
and rrv.result_value is not null
and piv.name = 'Pay Value'
and ppa.date_earned between piv.effective_start_date
and piv.effective_end_date
and ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
and (( prr.source_type = prr2.source_type and
decode (pet.proration_group_id, null, 1, prr.run_result_id) =
decode (pet.proration_group_id, null, 1, prr2.run_result_id))
or ( prr.source_type <> prr2.source_type)) ;
select fnd_number.canonical_to_number(rrv.result_value)
into adj_value
from
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_result_values rrv,
pay_run_results prr2,
pay_run_results prr
where prr.run_result_id = p_run_result
and prr.assignment_action_id = assign_action_id
and pet.element_type_id = prr2.element_type_id
and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
and prr.source_id = prr2.source_id
and prr.assignment_action_id = prr2.assignment_action_id
and prr2.source_type in ('E','I')
and prr2.entry_type in ('R','A')
and prr.entry_type not in ('R','A')
and prr2.run_result_id = rrv.run_result_id
and rrv.input_value_id = piv.input_value_id
and rrv.result_value is not null
and piv.name = 'Pay Value'
and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
piv.effective_start_date and piv.effective_end_date
and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
pet.effective_start_date and pet.effective_end_date;
select fnd_number.canonical_to_number(rrv.result_value)
into adj_value
from
pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_result_values rrv,
pay_run_results prr2,
pay_run_results prr
where pee.target_entry_id=entry
and pee.assignment_id = assign_id
and prr.source_id=pee.element_entry_id
and prr.assignment_action_id = assign_action_id
and pet.element_type_id = prr2.element_type_id
and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
and prr.source_id = prr2.source_id
and prr.assignment_action_id = prr2.assignment_action_id
and prr2.source_type in ('E','I')
and prr2.run_result_id = rrv.run_result_id
and rrv.input_value_id = piv.input_value_id
and rrv.result_value is not null
and piv.name = 'Pay Value'
and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
piv.effective_start_date and piv.effective_end_date
and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
pet.effective_start_date and pet.effective_end_date
and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
pee.effective_start_date and pee.effective_end_date;
g_pre_payments.delete;
select max(pee.effective_end_date)
into l_max_eff_end_date
from pay_run_results prr,
pay_element_entries_f pee
where prr.run_result_id = p_run_results
and prr.source_id = pee.element_entry_id ;
select ppt.category category,
ppm.personal_payment_method_id personal_method,
pea.prenote_date prenote_date,
ppt.validation_days valid_days,
ppm.percentage percentage,
ppm.amount amount,
opm.org_payment_method_id org_method,
hr_pre_pay.set_cash_rule(ppt.category,
opm.pmeth_information1) cash_rule,
opm.currency_code payment_currency,
ppt.pre_validation_required validation_required,
ppt.validation_value validation_value,
ppm.external_account_id external_account_id
into payment_details
from pay_run_results prr,
pay_element_entries_f pee,
pay_personal_payment_methods_f ppm,
pay_external_accounts pea,
pay_org_payment_methods_f opm,
pay_payment_types ppt,
pay_assignment_actions paa,
pay_payroll_actions ppa
where prr.run_result_id = p_run_results
/* Bug 12364433*/
and nvl(prr.element_entry_id,prr.source_id) = pee.element_entry_id
and paa.assignment_action_id = prr.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and pee.personal_payment_method_id = ppm.personal_payment_method_id
and ppm.org_payment_method_id = opm.org_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and ppm.external_account_id = pea.external_account_id (+)
-- and opm.pmeth_information1 = hlu.lookup_code (+)
-- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
-- and NVL(hlu.application_id, 800) = 800
and fnd_date.canonical_to_date(p_effective_date) between
ppm.effective_start_date and ppm.effective_end_date
and fnd_date.canonical_to_date(p_effective_date) between
opm.effective_start_date and opm.effective_end_date
and ( (ppa.date_earned between
pee.effective_start_date and pee.effective_end_date)
OR exists
(
SELECT 1
FROM per_time_periods ptp
WHERE ptp.time_period_id = ppa.time_period_id
AND pee.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
and pee.effective_end_date = l_max_eff_end_date
)
)
order by ppm.priority;
select nvl(prt.run_method, 'N'), paa.assignment_action_id
into rt_method,
sep_chq_aa_id
from pay_run_types_f prt,
pay_assignment_actions paa,
pay_run_results prr,
pay_payroll_actions ppa
where prr.run_result_id = p_run_results
and prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
and nvl(prt.effective_end_date, ppa.effective_date);
select count(*)
into l_paytype
from pay_org_payment_methods_f opm1
,pay_org_payment_methods_f opm2
where opm1.org_payment_method_id = p_org_meth
and opm2.org_payment_method_id = l_org_method_id
and opm1.payment_type_id = opm2.payment_type_id
and opm1.currency_code = opm2.currency_code
and p_effective_date between opm1.effective_start_date
and opm1.effective_end_date
and p_effective_date between opm2.effective_start_date
and opm2.effective_end_date;
select count(*)
into l_ext_acc
from pay_org_payment_methods_f opm
where opm.org_payment_method_id = l_org_method_id
and opm.external_account_id is not null
and p_effective_date between opm.effective_start_date
and opm.effective_end_date;
select
ppt.category category,
null personal_method,
pea.prenote_date prenote_date,
ppt.validation_days valid_days,
100 percentage,
null amount,
popm_par.org_payment_method_id org_method,
hr_pre_pay.set_cash_rule(ppt.category,
popm_par.pmeth_information1) cash_rule,
popm_par.currency_code payment_currency,
ppt.pre_validation_required validation_required,
ppt.validation_value validation_value,
popm_par.external_account_id external_account_id,
popm.defined_balance_id defined_balance_id,
popm.org_payment_method_id payee_org_method,
popm.time_definition_id time_def_id,
decode(popm.time_definition_id,
null, null,
pay_core_dates.get_time_definition_date(
popm.time_definition_id,
ppa.effective_date,
ppa.business_group_id)) payment_date
from
pay_external_accounts pea,
pay_payment_types ppt,
pay_org_payment_methods_f popm,
pay_org_payment_methods_f popm_par,
pay_org_pay_method_usages_f popmu,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_asg_action
and paa.payroll_action_id = ppa.payroll_action_id
and popm.organization_id = p_org_id
and popm.type = 'PAYEE'
and popm.parent_org_payment_method_id = popm_par.org_payment_method_id
and popm_par.org_payment_method_id = popmu.org_payment_method_id
and ppa.payroll_id = popmu.payroll_id
and popm.external_account_id = pea.external_account_id (+)
and popm_par.payment_type_id = ppt.payment_type_id
and ppa.effective_date between popmu.effective_start_date
and popmu.effective_end_date
and ppa.effective_date between popm.effective_start_date
and popm.effective_end_date
and ppa.effective_date between popm_par.effective_start_date
and popm_par.effective_end_date;
select distinct (prrv.result_value) organization_id
from pay_run_result_values prrv,
pay_input_values_f piv,
pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai
where pai.locking_action_id = p_asg_action
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_action_id = prr.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and ppa.effective_date between piv.effective_start_date
and piv.effective_end_date
and piv.name = p_org_context_name;
g_pre_payments.delete;
select chld.assignment_action_id
from pay_assignment_actions chld,
pay_assignment_actions mas
where mas.assignment_action_id = p_run_act_id
and mas.assignment_action_id = chld.source_action_id
and mas.payroll_action_id = chld.payroll_action_id
and mas.assignment_id = chld.assignment_id;
select paa.assignment_action_id,
paa.assignment_id,
paa_mas.payroll_action_id pre_payroll_action_id,
paa.tax_unit_id run_tax_unit_id,
paa_mas.chunk_number pre_chunk_number
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_assignment_actions paa_mas
where pai.locking_action_id = paa_mas.assignment_action_id
and paa_mas.assignment_action_id = p_action
and pai.locked_action_id = paa.assignment_action_id
and exists (select ''
from pay_action_interlocks pai2,
pay_assignment_actions paa2
where pai2.locking_action_id = paa_mas.assignment_action_id
and pai2.locked_action_id = paa2.assignment_action_id
and paa2.tax_unit_id <> paa.tax_unit_id)
order by paa.tax_unit_id;
select paa.assignment_action_id,
paa.assignment_id,
paa_mas.payroll_action_id pre_payroll_action_id,
paa.tax_unit_id run_tax_unit_id,
paa_mas.chunk_number pre_chunk_number,
paa.start_date start_date,
paa.end_date end_date
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_assignment_actions paa_mas
where pai.locking_action_id = paa_mas.assignment_action_id
and paa_mas.assignment_action_id = p_action
and pai.locked_action_id = paa.assignment_action_id
--
-- North America can only handle child actions on
-- multiple assignment payrolls
--
--and exists (select ''
-- from pay_action_interlocks pai2,
-- pay_assignment_actions paa2
-- where pai2.locking_action_id = paa_mas.assignment_action_id
-- and pai2.locked_action_id = paa2.assignment_action_id
-- and paa2.assignment_id <> paa.assignment_id)
order by paa.assignment_id;
select pay_assignment_actions_s.nextval
into pre_tu_actid
from dual;
select pay_assignment_actions_s.nextval
into pre_asg_actid
from dual;
select paa_chd.assignment_action_id,
paa_chd.start_date
from pay_assignment_actions paa_chd,
pay_assignment_actions paa_mas
where paa_mas.assignment_action_id = p_asg_act
and paa_mas.assignment_action_id = paa_chd.source_action_id
and paa_mas.payroll_action_id = paa_chd.payroll_action_id
and paa_mas.chunk_number = paa_chd.chunk_number;
select validation_name, rule_type
from pay_legislative_field_info
where field_name = 'MULTI_TAX_UNIT_PAYMENT'
and validation_type = 'ITEM_PROPERTY'
and target_location = 'PAYWSDOR'
and rule_mode = 'Y'
and legislation_code =
(select hr_api.return_legislation_code(ppa.business_group_id)
from pay_payroll_actions ppa, pay_assignment_actions paa
where paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = p_asg_act);
select legislation_code
into l_leg_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select
ppp.pre_payment_id,
paa_pru.assignment_action_id,
ppa_pru.payroll_action_id
from pay_payroll_actions ppa_pru,
pay_payroll_actions ppa_pre,
pay_assignment_actions paa_pru,
pay_assignment_actions paa_pre,
pay_action_interlocks pai,
pay_pre_payments ppp
where
paa_pru.assignment_action_id = p_asg_act
and ppa_pru.payroll_action_id = paa_pru.payroll_action_id
and paa_pru.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa_pre.assignment_action_id
and paa_pre.assignment_action_id = ppp.assignment_action_id
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppp.organization_id is not null
and nvl(ppp.effective_date , ppa_pre.effective_date)
<= ppa_pru.effective_date
and not exists (select ''
from pay_contributing_payments pcp
where ppp.pre_payment_id = pcp.contributing_pre_payment_id)
order by ppp.org_payment_method_id, ppp.payees_org_payment_method_id
for update of ppp.pre_payment_id;
insert into pay_contributing_payments
(assignment_action_id,
payroll_action_id,
contributing_pre_payment_id
)
values (
f_assignment_action_id(i),
f_payroll_action_id(i),
f_pre_payment_id(i)
);
select ppp.org_payment_method_id,
ppp.payees_org_payment_method_id,
ppp.organization_id,
sum(nvl(base_currency_value, 0)) base_currency_value,
sum(nvl(value,0)) value
from pay_contributing_payments pcp,
pay_pre_payments ppp
where pcp.payroll_action_id = p_pactid
and pcp.contributing_pre_payment_id = ppp.pre_payment_id
group by ppp.org_payment_method_id,
ppp.payees_org_payment_method_id,
ppp.organization_id;
select pay_pre_payments_s.nextval
into l_pre_pay_id
from dual;
insert into pay_pre_payments
(pre_payment_id,
org_payment_method_id,
value,
base_currency_value,
organization_id,
payees_org_payment_method_id,
payroll_action_id)
values
(l_pre_pay_id,
totrec.org_payment_method_id,
totrec.value,
totrec.base_currency_value,
totrec.organization_id,
totrec.payees_org_payment_method_id,
p_pactid);
update pay_contributing_payments pcp
set pre_payment_id = l_pre_pay_id
where pcp.payroll_action_id = p_pactid
and exists (select ''
from pay_pre_payments ppp
where ppp.pre_payment_id = pcp.contributing_pre_payment_id
and ppp.org_payment_method_id = totrec.org_payment_method_id
and ppp.payees_org_payment_method_id = totrec.payees_org_payment_method_id
and ppp.organization_id = totrec.organization_id
);