[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
into l_dummy
from sys.dual
where not exists
(select null
from pay_pss_transaction_steps pts,
pay_org_payment_methods_f opm,
pay_payment_types ppt
where pts.transaction_step_id = p_transaction_step_id
and opm.org_payment_method_id = pts.org_payment_method_id
and pts.effective_date
between opm.effective_start_date
and opm.effective_end_date
and opm.payment_type_id = ppt.payment_type_id
and ppt.category = 'MT'
and ppt.territory_code <> pts.territory_code);
select ppts.transaction_step_id
, ppts.state
from pay_pss_transaction_steps ppts
where ppts.transaction_id = p_transaction_id
and ppts.state <> C_STATE_FREED
;
(p_state <> C_STATE_FREED and p_state <> C_STATE_DELETED);
procedure update_remaining_pay_ppm
(p_transaction_id in number
) is
l_txstepids varchar2(2000);
select p.state
, p.amount_type
into l_state
, l_amount_type
from pay_pss_transaction_steps p
where p.transaction_step_id = l_txstepid;
l_state := C_STATE_UPDATED;
elsif l_state = C_STATE_UPDATED then
--
-- Check if this results in a net change.
--
pay_ppmv4_utils_ss.tt2ppm
(p_transaction_step_id => l_txstepid
,p_ppm => l_ppm
);
update pay_pss_transaction_steps p
set p.amount_type = C_REMAINING_PAY
, p.amount = 100
, p.state = l_state
where transaction_step_id = l_txstepid
;
end update_remaining_pay_ppm;
select count(0)
from pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and opm.org_payment_method_id = ppm.org_payment_method_id
and opm.defined_balance_id is not null
and (
(p_effective_date between ppm.effective_start_date and ppm.effective_end_date
and p_effective_date between opm.effective_start_date and opm.effective_end_date)
or
( check_future_payments(p_assignment_id ,to_char(p_effective_date,'YYYY-MM-DD'),p_run_type_id ) = 'Y'
and p_effective_date < ppm.effective_start_date )
);
select null
,null
,C_PAY_PERSONAL_PAYMENT_METHODS
,C_STATE_EXISTING
,ppm.personal_payment_method_id
,ppm.object_version_number
,ppm.object_version_number
,null
,null
,'N'
,ppm.effective_start_date
,ppm.org_payment_method_id
,p_assignment_id
,ppt.category
,pbt.currency_code
,pay_ppmv4_ss.get_ppm_country(ppm.org_payment_method_id,ppm.business_group_id)
,ppm.priority
,null
,decode(ppm.percentage, null, C_MONETARY, C_PERCENTAGE)
,decode(ppm.percentage, null, ppm.amount, ppm.percentage)
,ppm.external_account_id
,ppm.attribute_category
,ppm.attribute1
,ppm.attribute2
,ppm.attribute3
,ppm.attribute4
,ppm.attribute5
,ppm.attribute6
,ppm.attribute7
,ppm.attribute8
,ppm.attribute9
,ppm.attribute10
,ppm.attribute11
,ppm.attribute12
,ppm.attribute13
,ppm.attribute14
,ppm.attribute15
,ppm.attribute16
,ppm.attribute17
,ppm.attribute18
,ppm.attribute19
,ppm.attribute20
,ppm.priority
,null
,decode(ppm.percentage, null, C_MONETARY, C_PERCENTAGE)
,decode(ppm.percentage, null, ppm.amount, ppm.percentage)
,ppm.external_account_id
,ppm.attribute_category
,ppm.attribute1
,ppm.attribute2
,ppm.attribute3
,ppm.attribute4
,ppm.attribute5
,ppm.attribute6
,ppm.attribute7
,ppm.attribute8
,ppm.attribute9
,ppm.attribute10
,ppm.attribute11
,ppm.attribute12
,ppm.attribute13
,ppm.attribute14
,ppm.attribute15
,ppm.attribute16
,ppm.attribute17
,ppm.attribute18
,ppm.attribute19
,ppm.attribute20
,ppm.run_type_id
,ppm.ppm_information_category
,ppm.ppm_information1
,ppm.ppm_information2
,ppm.ppm_information3
,ppm.ppm_information4
,ppm.ppm_information5
,ppm.ppm_information6
,ppm.ppm_information7
,ppm.ppm_information8
,ppm.ppm_information9
,ppm.ppm_information10
,ppm.ppm_information11
,ppm.ppm_information12
,ppm.ppm_information13
,ppm.ppm_information14
,ppm.ppm_information15
,ppm.ppm_information16
,ppm.ppm_information17
,ppm.ppm_information18
,ppm.ppm_information19
,ppm.ppm_information20
,ppm.ppm_information21
,ppm.ppm_information22
,ppm.ppm_information23
,ppm.ppm_information24
,ppm.ppm_information25
,ppm.ppm_information26
,ppm.ppm_information27
,ppm.ppm_information28
,ppm.ppm_information29
,ppm.ppm_information30
,ppm.ppm_information_category
,ppm.ppm_information1
,ppm.ppm_information2
,ppm.ppm_information3
,ppm.ppm_information4
,ppm.ppm_information5
,ppm.ppm_information6
,ppm.ppm_information7
,ppm.ppm_information8
,ppm.ppm_information9
,ppm.ppm_information10
,ppm.ppm_information11
,ppm.ppm_information12
,ppm.ppm_information13
,ppm.ppm_information14
,ppm.ppm_information15
,ppm.ppm_information16
,ppm.ppm_information17
,ppm.ppm_information18
,ppm.ppm_information19
,ppm.ppm_information20
,ppm.ppm_information21
,ppm.ppm_information22
,ppm.ppm_information23
,ppm.ppm_information24
,ppm.ppm_information25
,ppm.ppm_information26
,ppm.ppm_information27
,ppm.ppm_information28
,ppm.ppm_information29
,ppm.ppm_information30
from pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
, pay_payment_types ppt
, pay_defined_balances pdb
, pay_balance_types pbt
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and opm.org_payment_method_id = ppm.org_payment_method_id
and (
(p_effective_date between ppm.effective_start_date and ppm.effective_end_date
and p_effective_date between opm.effective_start_date and opm.effective_end_date)
or
( check_future_payments(p_assignment_id ,to_char(p_effective_date,'YYYY-MM-DD'),p_run_type_id ) = 'Y'
and p_effective_date < ppm.effective_start_date )
)
and opm.defined_balance_id is not null
and pdb.defined_balance_id = opm.defined_balance_id
and pbt.balance_type_id = pdb.balance_type_id
and ppt.payment_type_id = opm.payment_type_id
order by ppm.priority
;
select 'Y'
from pay_pre_payments ppp
, pay_assignment_actions paa
, pay_payroll_actions ppa
where ppp.personal_payment_method_id = p_personal_payment_method_id
and paa.assignment_action_id = ppp.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date >= p_effective_date
;
select count(0)
from pay_personal_payment_methods_f ppm
where ppm.personal_payment_method_id = p_personal_payment_method_id
;
select pay_pss_transactions_s.nextval
into l_transaction_id
from dual;
l_ppm.delete_disabled := 'Y';
l_ppm.update_datetrack_mode := hr_api.g_correction;
l_ppm.delete_datetrack_mode := hr_api.g_delete;
select ppm.object_version_number
into l_ppm.delete_ovn
from pay_personal_payment_methods_f ppm
where ppm.personal_payment_method_id =
l_ppm.personal_payment_method_id
and ppm.effective_end_date = (l_effective_date-1);
l_ppm.delete_datetrack_mode := hr_api.g_zap;
l_ppm.update_datetrack_mode := hr_api.g_update;
l_ppm.delete_datetrack_mode := hr_api.g_delete;
select opm.org_payment_method_id opmid
from pay_org_payment_methods_f opm
, per_all_assignments_f paa
, pay_org_pay_method_usages_f popmu
, pay_payment_types ppt
where paa.assignment_id = p_assignment_id
and p_effective_date between
paa.effective_start_date and paa.effective_end_date
and opm.org_payment_method_id = p_org_payment_id
and popmu.payroll_id = paa.payroll_id
and p_effective_date between
popmu.effective_start_date and popmu.effective_end_date
and opm.org_payment_method_id = popmu.org_payment_method_id
and opm.defined_balance_id is not null
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and ppt.payment_type_id = opm.payment_type_id
and ppt.category = p_category
;
select opm.org_payment_method_id opmid
from pay_org_payment_methods_f opm
, per_all_assignments_f paa
, pay_org_pay_method_usages_f popmu
, pay_payment_types ppt
where paa.assignment_id = p_assignment_id
and p_effective_date between
paa.effective_start_date and paa.effective_end_date
and popmu.payroll_id = paa.payroll_id
and p_effective_date between
popmu.effective_start_date and popmu.effective_end_date
and opm.org_payment_method_id = popmu.org_payment_method_id
and opm.defined_balance_id is not null
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and ppt.payment_type_id = opm.payment_type_id
and ppt.category = p_category
;
l_sql_stat:='select '||p_function||'('||p_assignment_id||','''||to_char(p_effective_date)||''','''||l_category||''') from sys.dual';
select opm.org_payment_method_name name
, opm.org_payment_method_id opmid
from pay_org_payment_methods_f opm
, per_all_assignments_f paa
, pay_org_pay_method_usages_f popmu
, pay_payment_types ppt
where paa.assignment_id = p_assignment_id
and p_effective_date between
paa.effective_start_date and paa.effective_end_date
and popmu.payroll_id = paa.payroll_id
and p_effective_date between
popmu.effective_start_date and popmu.effective_end_date
and opm.org_payment_method_id = popmu.org_payment_method_id
and opm.defined_balance_id is not null
and p_effective_date between
opm.effective_start_date and opm.effective_end_date
and ppt.payment_type_id = opm.payment_type_id
and ppt.category = p_category
;
l_opms.delete;
l_opmids.delete;
select ppt.category
, nvl(ppt.territory_code, hr_api.return_legislation_code(paa.business_group_id))
, pap.prl_information3
, paa.payroll_id
from per_all_assignments_f paa
, pay_all_payrolls_f pap
, pay_org_payment_methods_f popm
, pay_payment_types ppt
where paa.assignment_id = p_assignment_id
and p_effective_date between
paa.effective_start_date and paa.effective_end_date
and pap.payroll_id = paa.payroll_id
and p_effective_date between
pap.effective_start_date and pap.effective_end_date
and popm.org_payment_method_id(+) = pap.default_payment_method_id
and p_effective_date between
popm.effective_start_date and popm.effective_end_date
and ppt.payment_type_id = popm.payment_type_id
;
select null
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and p_effective_date between
ppm.effective_start_date and ppm.effective_end_date
and ppm.amount is not null
;
select null
from pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and p_effective_date between
ppm.effective_start_date and ppm.effective_end_date
and 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 opm.defined_balance_id is not null
and ( (p_run_type_id is not null) or (ppm.priority <
(select max(priority)
from pay_personal_payment_methods_f ppm1
where ppm1.assignment_id = p_assignment_id
and nvl(ppm1.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and p_effective_date between
ppm1.effective_start_date and ppm1.effective_end_date)))
and ppm.percentage is not null
;
select null
from hr_lookups
where lookup_type = 'PAY_METHOD_AMOUNT_TYPE'
and lookup_code = p_lookup_code
and enabled_flag = 'Y'
and p_effective_date between nvl(start_date_active, p_effective_date)
and nvl(end_date_active, p_effective_date) ;
select person_id
into l_person_id
from per_all_assignments_f a
where a.assignment_id = l_assignment_id
and l_effective_date
between a.effective_start_date and a.effective_end_date;
select pbg.legislation_code
, pbg.business_group_id
into l_territory_code
, l_business_group_id
from per_business_groups pbg
, per_all_assignments_f asg
where asg.assignment_id = l_assignment_id
and l_effective_date between
asg.effective_start_date and asg.effective_end_date
and pbg.business_group_id + 0 = asg.business_group_id;
select leg.rule_mode
, flex.id_flex_structure_code
into l_option_value
, l_option_value1
from pay_legislation_rules leg
, fnd_id_flex_structures flex
where leg.legislation_code = l_territory_code
and leg.rule_type = 'E'
and to_char(flex.id_flex_num) = leg.rule_mode
and flex.id_flex_code = 'BANK';
select 'Y'
into l_option_value
from pay_legislative_field_info leg
where leg.field_name = 'CHEQUE_CHECK'
and leg.legislation_code = l_territory_code;
select transaction_step_id
, state
, logical_priority
from pay_pss_transaction_steps
where transaction_id = p_transaction_id;
p_review_page and l_state <> C_STATE_DELETED then
l_txstepid_tbl(l_priority) := rec.transaction_step_id;
select distinct ppts.run_type_id
from pay_pss_transaction_steps ppts
where ppts.transaction_id = p_transaction_id;
select p.o_real_priority
into l_o_priority
from pay_pss_transaction_steps p
where p.transaction_step_id = l_txstepid
;
update pay_pss_transaction_steps p
set p.real_priority = l_priority
where p.transaction_step_id = l_txstepid;
procedure update_logical_priority
(p_transaction_step_id in varchar2
,p_logical_priority in varchar2
,p_amount_type in varchar2
) is
l_logical_priority varchar2(2000);
l_proc varchar2(2000) := g_package||'update_logical_priority';
select ppm.AMOUNT, ppm.PERCENTAGE
from pay_personal_payment_methods_f ppm,
pay_pss_transaction_steps pps
where pps.transaction_step_id = p_transaction_step_id
and pps.PERSONAL_PAYMENT_METHOD_ID = ppm.PERSONAL_PAYMENT_METHOD_ID
and pps.ASSIGNMENT_ID= ppm.ASSIGNMENT_ID
and pps.ORG_PAYMENT_METHOD_ID = ppm.ORG_PAYMENT_METHOD_ID
and pps.effective_date between ppm.effective_start_date and ppm.effective_end_date;
select to_char(p.logical_priority)
, to_char(p.o_logical_priority)
, p.state
, p.amount_type
, p.amount
into l_logical_priority
, l_o_logical_priority
, l_state
, l_amount_type
, l_amount
from pay_pss_transaction_steps p
where p.transaction_step_id = p_transaction_step_id;
if l_state = C_STATE_UPDATED then
pay_ppmv4_utils_ss.seterrorstage(l_proc, 'TT2PPM', 20);
l_state := C_STATE_UPDATED;
update pay_pss_transaction_steps p
set p.amount = l_amount
, p.amount_type = l_amount_type
, p.logical_priority = p_logical_priority
, p.state = l_state
where p.transaction_step_id = p_transaction_step_id;
end update_logical_priority;
select ppts.transaction_step_id
, ppts.logical_priority
, ppts.amount_type
from pay_pss_transaction_steps ppts
where ppts.transaction_id = p_transaction_id
and pay_ppmv4_ss.include_in_page(C_SUMMARY_PAGE, ppts.state) = 'Y';
update_logical_priority
(p_transaction_step_id => rec.transaction_step_id
,p_amount_type => rec.amount_type
,p_logical_priority => rec.logical_priority + 1
);
l_new_ppm.update_ovn := 1;
l_new_ppm.delete_disabled := 'N';
if not l_original and l_saved_ppm.state = C_STATE_UPDATED then
--
-- The above version of changedppm ignores logical_priority so
-- do the check here.
--
if l_saved_ppm.logical_priority = l_saved_ppm.o_logical_priority then
pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXISTING', 30);
l_saved_ppm.state := C_STATE_UPDATED;
,p_object_version_number => l_saved_ppm.update_ovn
,p_update_datetrack_mode => l_saved_ppm.update_datetrack_mode
,p_effective_date => l_saved_ppm.effective_date
,p_org_payment_method_id => l_saved_ppm.org_payment_method_id
,p_assignment_id => l_saved_ppm.assignment_id
,p_run_type_id => l_saved_ppm.run_type_id
,p_payment_type => l_saved_ppm.payment_type
,p_territory_code => l_saved_ppm.territory_code
,p_amount_type => l_saved_ppm.amount_type
,p_amount => l_saved_ppm.amount
,p_external_account_id => l_saved_ppm.external_account_id
,p_attribute_category => l_saved_ppm.attribute_category
,p_attribute1 => l_saved_ppm.attribute1
,p_attribute2 => l_saved_ppm.attribute2
,p_attribute3 => l_saved_ppm.attribute3
,p_attribute4 => l_saved_ppm.attribute4
,p_attribute5 => l_saved_ppm.attribute5
,p_attribute6 => l_saved_ppm.attribute6
,p_attribute7 => l_saved_ppm.attribute7
,p_attribute8 => l_saved_ppm.attribute8
,p_attribute9 => l_saved_ppm.attribute9
,p_attribute10 => l_saved_ppm.attribute10
,p_attribute11 => l_saved_ppm.attribute11
,p_attribute12 => l_saved_ppm.attribute12
,p_attribute13 => l_saved_ppm.attribute13
,p_attribute14 => l_saved_ppm.attribute14
,p_attribute15 => l_saved_ppm.attribute15
,p_attribute16 => l_saved_ppm.attribute16
,p_attribute17 => l_saved_ppm.attribute17
,p_attribute18 => l_saved_ppm.attribute18
,p_attribute19 => l_saved_ppm.attribute19
,p_attribute20 => l_saved_ppm.attribute20
,p_ppm_information_category => l_saved_ppm.ppm_information_category
,p_ppm_information1 => l_saved_ppm.ppm_information1
,p_ppm_information2 => l_saved_ppm.ppm_information2
,p_ppm_information3 => l_saved_ppm.ppm_information3
,p_ppm_information4 => l_saved_ppm.ppm_information4
,p_ppm_information5 => l_saved_ppm.ppm_information5
,p_ppm_information6 => l_saved_ppm.ppm_information6
,p_ppm_information7 => l_saved_ppm.ppm_information7
,p_ppm_information8 => l_saved_ppm.ppm_information8
,p_ppm_information9 => l_saved_ppm.ppm_information9
,p_ppm_information10 => l_saved_ppm.ppm_information10
,p_ppm_information11 => l_saved_ppm.ppm_information11
,p_ppm_information12 => l_saved_ppm.ppm_information12
,p_ppm_information13 => l_saved_ppm.ppm_information13
,p_ppm_information14 => l_saved_ppm.ppm_information14
,p_ppm_information15 => l_saved_ppm.ppm_information15
,p_ppm_information16 => l_saved_ppm.ppm_information16
,p_ppm_information17 => l_saved_ppm.ppm_information17
,p_ppm_information18 => l_saved_ppm.ppm_information18
,p_ppm_information19 => l_saved_ppm.ppm_information19
,p_ppm_information20 => l_saved_ppm.ppm_information20
,p_ppm_information21 => l_saved_ppm.ppm_information21
,p_ppm_information22 => l_saved_ppm.ppm_information22
,p_ppm_information23 => l_saved_ppm.ppm_information23
,p_ppm_information24 => l_saved_ppm.ppm_information24
,p_ppm_information25 => l_saved_ppm.ppm_information25
,p_ppm_information26 => l_saved_ppm.ppm_information26
,p_ppm_information27 => l_saved_ppm.ppm_information27
,p_ppm_information28 => l_saved_ppm.ppm_information28
,p_ppm_information29 => l_saved_ppm.ppm_information29
,p_ppm_information30 => l_saved_ppm.ppm_information30
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_return_status => l_return_status
,p_msg_data => l_msg_data
,p_msg_count => l_msg_count
);
procedure delete_ppm
(p_transaction_step_id in varchar2
,p_return_status out nocopy varchar2
,p_msg_count out nocopy number
,p_msg_data out nocopy varchar2
) is
l_state varchar2(2000);
l_proc varchar2(2000) := g_package||'delete_ppm';
select p.state
, p.amount_type
, p.transaction_id
into l_state
, l_amount_type
, l_transaction_id
from pay_pss_transaction_steps p
where p.transaction_step_id = p_transaction_step_id;
update pay_pss_transaction_steps p
set p.state = C_STATE_FREED
where p.transaction_step_id = p_transaction_step_id;
,p_state => C_STATE_DELETED
,p_effective_date => l_ppm.effective_date
,p_personal_payment_method_id => l_ppm.personal_payment_method_id
,p_delete_datetrack_mode => l_ppm.delete_datetrack_mode
,p_delete_ovn => l_ppm.delete_ovn
);
fnd_message.set_name('PAY', 'PAY_51519_PSS_CANNOT_DELETE');
l_ppm.state := C_STATE_DELETED;
pay_ppmv4_utils_ss.seterrorstage(l_proc, 'DELETED', 40);
update_remaining_pay_ppm
(p_transaction_id => l_transaction_id
);
end delete_ppm;
select ppm.personal_payment_method_id ppmid
, ppm.object_version_number ovn
, ppm.effective_start_date esd
, ppm.priority priority
from pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and p_effective_date
between ppm.effective_start_date and ppm.effective_end_date
and 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 opm.defined_balance_id is not null
order by ppm.priority;
l_dt_mode := hr_api.g_update;
hr_utility.trace('Calling UPDATE from resequence_priorities');
hr_personal_pay_method_api.update_personal_pay_method
(p_validate => false
,p_personal_payment_method_id => ppm.ppmid
,p_object_version_number => ppm.ovn
,p_priority => l_priority
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_dt_mode
/*,p_ppm_information_category => l_ppm.ppm_information_category
,p_ppm_information1 => l_ppm.ppm_information1
,p_ppm_information2 => l_ppm.ppm_information2
,p_ppm_information3 => l_ppm.ppm_information3
,p_ppm_information4 => l_ppm.ppm_information4
,p_ppm_information5 => l_ppm.ppm_information5
,p_ppm_information6 => l_ppm.ppm_information6
,p_ppm_information7 => l_ppm.ppm_information7
,p_ppm_information8 => l_ppm.ppm_information8
,p_ppm_information9 => l_ppm.ppm_information9
,p_ppm_information10 => l_ppm.ppm_information10
,p_ppm_information11 => l_ppm.ppm_information11
,p_ppm_information12 => l_ppm.ppm_information12
,p_ppm_information13 => l_ppm.ppm_information13
,p_ppm_information14 => l_ppm.ppm_information14
,p_ppm_information15 => l_ppm.ppm_information15
,p_ppm_information16 => l_ppm.ppm_information16
,p_ppm_information17 => l_ppm.ppm_information17
,p_ppm_information18 => l_ppm.ppm_information18
,p_ppm_information19 => l_ppm.ppm_information19
,p_ppm_information20 => l_ppm.ppm_information20
,p_ppm_information21 => l_ppm.ppm_information21
,p_ppm_information22 => l_ppm.ppm_information22
,p_ppm_information23 => l_ppm.ppm_information23
,p_ppm_information24 => l_ppm.ppm_information24
,p_ppm_information25 => l_ppm.ppm_information25
,p_ppm_information26 => l_ppm.ppm_information26
,p_ppm_information27 => l_ppm.ppm_information27
,p_ppm_information28 => l_ppm.ppm_information28
,p_ppm_information29 => l_ppm.ppm_information29
,p_ppm_information30 => l_ppm.ppm_information30*/
,p_external_account_id => l_exaid
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_comment_id => l_comment_id
);
hr_utility.trace('Came out from UPDATE in resequence_priorities');
,p_object_version_number => l_ppm.update_ovn
,p_delete_ovn => l_ppm.delete_ovn
,p_update_datetrack_mode => l_ppm.update_datetrack_mode
,p_delete_datetrack_mode => l_ppm.delete_datetrack_mode
,p_effective_date => l_ppm.effective_date
,p_org_payment_method_id => l_ppm.org_payment_method_id
,p_assignment_id => l_ppm.assignment_id
,p_run_type_id => l_ppm.run_type_id
,p_territory_code => l_ppm.territory_code
,p_real_priority => l_ppm.real_priority
,p_amount_type => l_ppm.amount_type
,p_amount => l_ppm.amount
,p_attribute_category => l_ppm.attribute_category
,p_attribute1 => l_ppm.attribute1
,p_attribute2 => l_ppm.attribute2
,p_attribute3 => l_ppm.attribute3
,p_attribute4 => l_ppm.attribute4
,p_attribute5 => l_ppm.attribute5
,p_attribute6 => l_ppm.attribute6
,p_attribute7 => l_ppm.attribute7
,p_attribute8 => l_ppm.attribute8
,p_attribute9 => l_ppm.attribute9
,p_attribute10 => l_ppm.attribute10
,p_attribute11 => l_ppm.attribute11
,p_attribute12 => l_ppm.attribute12
,p_attribute13 => l_ppm.attribute13
,p_attribute14 => l_ppm.attribute14
,p_attribute15 => l_ppm.attribute15
,p_attribute16 => l_ppm.attribute16
,p_attribute17 => l_ppm.attribute17
,p_attribute18 => l_ppm.attribute18
,p_attribute19 => l_ppm.attribute19
,p_attribute20 => l_ppm.attribute20
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_o_real_priority => l_ppm.o_real_priority
,p_ppm_information_category => l_ppm.ppm_information_category
,p_ppm_information1 => l_ppm.ppm_information1
,p_ppm_information2 => l_ppm.ppm_information2
,p_ppm_information3 => l_ppm.ppm_information3
,p_ppm_information4 => l_ppm.ppm_information4
,p_ppm_information5 => l_ppm.ppm_information5
,p_ppm_information6 => l_ppm.ppm_information6
,p_ppm_information7 => l_ppm.ppm_information7
,p_ppm_information8 => l_ppm.ppm_information8
,p_ppm_information9 => l_ppm.ppm_information9
,p_ppm_information10 => l_ppm.ppm_information10
,p_ppm_information11 => l_ppm.ppm_information11
,p_ppm_information12 => l_ppm.ppm_information12
,p_ppm_information13 => l_ppm.ppm_information13
,p_ppm_information14 => l_ppm.ppm_information14
,p_ppm_information15 => l_ppm.ppm_information15
,p_ppm_information16 => l_ppm.ppm_information16
,p_ppm_information17 => l_ppm.ppm_information17
,p_ppm_information18 => l_ppm.ppm_information18
,p_ppm_information19 => l_ppm.ppm_information19
,p_ppm_information20 => l_ppm.ppm_information20
,p_ppm_information21 => l_ppm.ppm_information21
,p_ppm_information22 => l_ppm.ppm_information22
,p_ppm_information23 => l_ppm.ppm_information23
,p_ppm_information24 => l_ppm.ppm_information24
,p_ppm_information25 => l_ppm.ppm_information25
,p_ppm_information26 => l_ppm.ppm_information26
,p_ppm_information27 => l_ppm.ppm_information27
,p_ppm_information28 => l_ppm.ppm_information28
,p_ppm_information29 => l_ppm.ppm_information29
,p_ppm_information30 => l_ppm.ppm_information30
,p_validate => p_validate
);
select creator_person_id
, transaction_id
into l_hr_txpersonid
, l_hr_txid
from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id;
select count(*)
into l_unprocessed
from hr_api_transaction_values hatv
where hatv.name = pay_ppmv4_utils_ss.C_PROCESSED_FLAG_ARG
and hatv.varchar2_value = 'N'
and hatv.transaction_step_id in
(select transaction_step_id
from hr_api_transaction_steps hats
where hats.transaction_id = l_hr_txid);
procedure delete_ppm_transactions
(item_type in varchar2
,item_key in varchar2
,actid in number
,funmode in varchar2
,result out nocopy varchar2
) is
l_transaction_id varchar2(2000);
pay_pss_tx_steps_pkg.delete_rows
(p_transaction_id => to_number(l_transaction_id)
);
end delete_ppm_transactions;
SELECT ppt.territory_code,
TERR.territory_short_name
FROM pay_org_payment_methods_f pom,
pay_payment_types ppt,
FND_TERRITORIES_VL TERR
WHERE pom.org_payment_method_id = cp_org_payment_method_id
AND pom.payment_type_id = ppt.payment_type_id
AND ppt.territory_code = TERR.territory_code (+);
SELECT org_inf.org_information9,
TERR.territory_short_name
FROM hr_all_organization_units org,
hr_organization_information org_inf,
FND_TERRITORIES_VL TERR
WHERE org.ORGANIZATION_ID = cp_business_group_id
AND org.ORGANIZATION_ID = org_inf.ORGANIZATION_ID
AND ORG_INFORMATION_CONTEXT = 'Business Group Information'
AND org_inf.org_information9 = TERR.territory_code (+);
select flex.id_flex_structure_code
from pay_legislation_rules leg
, fnd_id_flex_structures flex
, PAY_ORG_PAYMENT_METHODS_F opm
, PAY_PAYMENT_TYPES PTS
where opm.ORG_PAYMENT_METHOD_ID = cp_opm_id
and opm.BUSINESS_GROUP_ID = cp_bgid
and opm.PAYMENT_TYPE_ID = PTS.PAYMENT_TYPE_ID
and leg.legislation_code = DECODE(PTS.territory_code, null, HR_API.RETURN_LEGISLATION_CODE(OPM.BUSINESS_GROUP_ID),PTS.territory_code)
and leg.rule_type = 'E'
and to_char(flex.id_flex_num) = leg.rule_mode
and flex.id_flex_code = 'BANK';
select OPMTL.ORG_PAYMENT_METHOD_NAME
from PAY_ORG_PAYMENT_METHODS_F_TL OPMTL,
PAY_ORG_PAYMENT_METHODS_F OPM
where OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPM.ORG_PAYMENT_METHOD_ID = cp_opmid
AND BUSINESS_GROUP_ID + 0 = cp_bgid
AND OPMTL.LANGUAGE = USERENV('LANG');
select popm.PMETH_INFORMATION9
INTO l_is_foreign_transaction
from pay_org_payment_methods_f popm
where popm.ORG_PAYMENT_METHOD_ID = p_opm_id
and HR_API.RETURN_LEGISLATION_CODE(POPM.BUSINESS_GROUP_ID)='US'
AND p_effective_date
BETWEEN popm.effective_start_date AND popm.effective_end_date;
SELECT PT.PAYMENT_TYPE_NAME
INTO l_payment_type_name
FROM PAY_PAYMENT_TYPES PT, PAY_ORG_PAYMENT_METHODS_F PM
WHERE PM.ORG_PAYMENT_METHOD_ID = P_OPM_ID
AND PM.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID
AND P_EFFECTIVE_DATE BETWEEN PM.EFFECTIVE_START_DATE
AND PM.EFFECTIVE_END_DATE;
select null
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
and ppm.effective_start_date > p_effective_date;