The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED */
pact.submission_period_status,
pact.submission_start_date,
pact.submission_end_date
from pay_jp_isdf_assact_v assact,
pay_assignment_actions paa,
pay_jp_isdf_pact_v pact
where assact.action_information_id = p_action_information_id
and paa.assignment_action_id = assact.assignment_action_id
and pact.payroll_action_id = paa.payroll_action_id;
select /*+ ORDERED */
nvl(nvl(pp.prl_information1, hoi.org_information2),'CTR_EE')
from pay_all_payrolls_f pp,
hr_organization_information hoi
where pp.payroll_id = p_payroll_id
and p_effective_date
between pp.effective_start_date and pp.effective_end_date
and hoi.organization_id(+) = pp.business_group_id
and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO';
select /* +ORDERED */
ppa.payroll_action_id,
ppa.business_group_id,
ppa.effective_date
from pay_jp_isdf_assact_v pjia,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pjia.action_information_id = p_action_information_id
and paa.assignment_action_id = pjia.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select /* +ORDERED */
put.user_table_id,
pur.user_row_id,
pur.display_sequence,
pur.row_low_range_or_name,
pur.row_high_range
from pay_user_tables put,
pay_user_rows_f pur
where put.user_table_name = p_udt_name
and nvl(put.legislation_code,'X') = nvl(l_legislation_code,nvl(put.legislation_code,'X'))
and pur.user_table_id = put.user_table_id
and p_effective_date
between pur.effective_start_date and pur.effective_end_date
order by 3, fnd_number.canonical_to_number(pur.row_low_range_or_name);
select /* +ORDERED */
puc.user_column_name,
puci.value
from pay_user_columns puc,
pay_user_column_instances_f puci
where puc.user_table_id = p_user_table_id
and puci.user_column_id = puc.user_column_id
and puci.user_row_id = p_row_id
and p_effective_date
between puci.effective_start_date and puci.effective_end_date
order by 1;
select /* +ORDERED */
put.user_table_id,
pur.user_row_id,
pur.display_sequence,
pur.row_low_range_or_name,
pur.row_high_range
from pay_user_tables put,
pay_user_rows_f pur
where put.user_table_name = p_udt_name
and nvl(put.legislation_code,'X') = nvl(l_legislation_code,nvl(put.legislation_code,'X'))
and pur.user_table_id = put.user_table_id
and p_effective_date
between pur.effective_start_date and pur.effective_end_date
order by 3, fnd_number.canonical_to_number(pur.row_low_range_or_name);
select /* +ORDERED */
puc.user_column_name,
puci.value
from pay_user_columns puc,
pay_user_column_instances_f puci
where puc.user_table_id = p_user_table_id
and puci.user_column_id = puc.user_column_id
and puci.user_row_id = p_row_id
and p_effective_date
between puci.effective_start_date and puci.effective_end_date
order by 1;
select paa.payroll_action_id
from pay_assignment_actions paa
where paa.assignment_action_id = l_assact_rec.assignment_action_id;
hr_utility.trace('start delete preset archive');
delete
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_ISDF_ASSACT';
hr_utility.trace('end delete preset archive');
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transaction_status = 'N'
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
select action_information_id,
action_context_id,
action_context_type,
object_version_number,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30,
effective_date,
assignment_id
from pay_action_information pai
where pai.action_context_id = p_assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category in ('JP_ISDF_LIFE_GEN',
'JP_ISDF_LIFE_PENS',
'JP_ISDF_NONLIFE',
'JP_ISDF_SOCIAL',
'JP_ISDF_MUTUAL_AID',
'JP_ISDF_SPOUSE',
'JP_ISDF_SPOUSE_INC',
'JP_ISDF_CARE_MED')
and pai.action_information1 <> 'D';
select /* +ORDERED */
ppa.payroll_action_id,
ppa.business_group_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
hr_utility.trace('end update_assact');
select *
from pay_jp_isdf_calc_dct_v
where assignment_action_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ISDF_CALC_DCT'
and status <> 'D';
select *
from pay_jp_isdf_calc_dct_v2
where assignment_action_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ISDF_CALC_DCT2'
and status <> 'D';
select *
into l_assact_rec
from pay_jp_isdf_assact_v
where action_information_id = p_action_information_id;
select pay_action_information_s.nextval
into l_action_information_id
from dual;
pay_jp_isdf_dml_pkg.update_calc_dct(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'I',
p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
p_spouse_income => l_calc_dct_rec.spouse_inc,
p_spouse_deduction => l_calc_dct_rec.spouse_deduction,
p_life_gen_new_ins_prem => l_calc_dct_rec.life_gen_new_ins_prem,
p_life_pens_new_ins_prem => l_calc_dct_rec.life_pens_new_ins_prem,
p_life_gen_new_ins_calc_prem => l_calc_dct_rec.life_gen_new_ins_calc_prem,
p_life_pens_new_ins_calc_prem => l_calc_dct_rec.life_pens_new_ins_calc_prem);
select pay_action_information_s.nextval
into l_action_information_id
from dual;
pay_jp_isdf_dml_pkg.update_calc_dct2(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'I',
p_care_med_new_ins_prem => l_calc_dct_rec.care_med_new_ins_prem,
p_care_med_new_ins_calc_prem => l_calc_dct_rec.care_med_new_ins_calc_prem);
hr_utility.trace('end update_assact');
select *
from pay_jp_isdf_calc_dct_v
where assignment_action_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ISDF_CALC_DCT'
and status <> 'D';
select *
from pay_jp_isdf_calc_dct_v2
where assignment_action_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ISDF_CALC_DCT2'
and status <> 'D';
select *
from pay_jp_isdf_entry_v
where assignment_action_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ISDF_ENTRY'
and status <> 'D';
select pay_action_information_s.nextval
into l_action_information_id
from dual;
pay_jp_isdf_dml_pkg.update_calc_dct(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'I',
p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
p_spouse_income => l_calc_dct_rec.spouse_inc,
p_spouse_deduction => l_calc_dct_rec.spouse_deduction,
p_life_gen_new_ins_prem => l_calc_dct_rec.life_gen_new_ins_prem,
p_life_pens_new_ins_prem => l_calc_dct_rec.life_pens_new_ins_prem,
p_life_gen_new_ins_calc_prem => l_calc_dct_rec.life_gen_new_ins_calc_prem,
p_life_pens_new_ins_calc_prem => l_calc_dct_rec.life_pens_new_ins_calc_prem);
select pay_action_information_s.nextval
into l_action_information_id
from dual;
pay_jp_isdf_dml_pkg.update_calc_dct2(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'I',
p_care_med_new_ins_prem => l_calc_dct_rec.care_med_new_ins_prem,
p_care_med_new_ins_calc_prem => l_calc_dct_rec.care_med_new_ins_calc_prem);
select pay_action_information_s.nextval
into l_action_information_id
from dual;
p_ins_datetrack_update_mode => null,
p_ins_element_entry_id => null,
p_ins_ee_object_version_number => null,
p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
p_life_gen_ins_prem_o => null,
p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
p_life_pens_ins_prem_o => null,
p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
p_nonlife_long_ins_prem_o => null,
p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
p_nonlife_short_ins_prem_o => null,
p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
p_earthquake_ins_prem_o => null,
p_is_datetrack_update_mode => null,
p_is_element_entry_id => null,
p_is_ee_object_version_number => null,
p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
p_social_ins_prem_o => null,
p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
p_mutual_aid_prem_o => null,
p_spouse_income => l_calc_dct_rec.spouse_inc,
p_spouse_income_o => null,
p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
p_national_pens_ins_prem_o => null,
p_life_gen_ins_prem_new => l_calc_dct_rec.life_gen_new_ins_prem,
p_life_pens_ins_prem_new => l_calc_dct_rec.life_pens_new_ins_prem,
p_care_med_ins_prem_new => l_calc_dct_rec.care_med_new_ins_prem,
p_object_version_number => l_object_version_number);
pay_jp_isdf_dml_pkg.update_entry(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'Q',
p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
p_life_gen_ins_prem_o => l_csr_entry.life_gen_ins_prem,
p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
p_life_pens_ins_prem_o => l_csr_entry.life_pens_ins_prem,
p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
p_nonlife_long_ins_prem_o => l_csr_entry.nonlife_long_ins_prem,
p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
p_nonlife_short_ins_prem_o => l_csr_entry.nonlife_short_ins_prem,
p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
p_earthquake_ins_prem_o => l_csr_entry.earthquake_ins_prem,
p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
p_social_ins_prem_o => l_csr_entry.social_ins_prem,
p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
p_mutual_aid_prem_o => l_csr_entry.mutual_aid_prem,
p_spouse_income => l_calc_dct_rec.spouse_inc,
p_spouse_income_o => l_csr_entry.spouse_income,
p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
p_national_pens_ins_prem_o => l_csr_entry.national_pens_ins_prem,
p_life_gen_ins_prem_new => l_calc_dct_rec.life_gen_new_ins_prem,
p_life_pens_ins_prem_new => l_calc_dct_rec.life_pens_new_ins_prem,
p_care_med_ins_prem_new => l_calc_dct_rec.care_med_new_ins_prem);
pay_jp_isdf_dml_pkg.update_entry(
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number,
p_status => 'I',
p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
p_life_gen_ins_prem_o => null,
p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
p_life_pens_ins_prem_o => null,
p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
p_nonlife_long_ins_prem_o => null,
p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
p_nonlife_short_ins_prem_o => null,
p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
p_earthquake_ins_prem_o => null,
p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
p_social_ins_prem_o => null,
p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
p_mutual_aid_prem_o => null,
p_spouse_income => l_calc_dct_rec.spouse_inc,
p_spouse_income_o => null,
p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
p_national_pens_ins_prem_o => null,
p_life_gen_ins_prem_new => l_calc_dct_rec.life_gen_new_ins_prem,
p_life_pens_ins_prem_new => l_calc_dct_rec.life_pens_new_ins_prem,
p_care_med_ins_prem_new => l_calc_dct_rec.care_med_new_ins_prem);
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transaction_status = 'F',
finalized_date = fnd_date.date_to_canonical(l_submission_date),
finalized_by = fnd_number.number_to_canonical(fnd_global.user_id),
user_comments = p_user_comments
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
delete
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_ISDF_ASSACT';
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transaction_status = 'U',
finalized_date = null,
finalized_by = null,
admin_comments = p_admin_comments
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transaction_status = 'N',
finalized_date = null,
finalized_by = null,
admin_comments = p_admin_comments
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
hr_utility.trace('start update_assact');
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transaction_status = 'A'
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
procedure insert_session(
p_effective_date in date)
is
--
l_rowid rowid;
select rowid
from fnd_sessions
where session_id = userenv('sessionid')
for update nowait;
insert into fnd_sessions(
session_id,
effective_date)
values(
userenv('sessionid'),
p_effective_date);
update fnd_sessions
set effective_date = p_effective_date
where rowid = l_rowid;
end insert_session;
procedure delete_session
is
begin
--
delete
from fnd_sessions
where session_id = userenv('sessionid');
end delete_session;
l_ins_datetrack_update_mode pay_jp_isdf_entry_v.ins_datetrack_update_mode%type;
l_is_datetrack_update_mode pay_jp_isdf_entry_v.is_datetrack_update_mode%type;
select /* +ORDERED */
ppa.payroll_action_id,
ppa.business_group_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_rec.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_business_group_id => g_business_group_id,
p_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
p_element_entry_id => l_entry_rec.ins_element_entry_id,
p_object_version_number => l_entry_rec.ins_ee_object_version_number,
p_input_value_id1 => c_life_gen_iv_id,
p_input_value_id2 => c_life_pens_iv_id,
p_input_value_id3 => c_nonlife_long_iv_id,
p_input_value_id4 => c_nonlife_short_iv_id,
p_input_value_id5 => c_earthquake_iv_id,
p_input_value_id6 => c_life_gen_new_iv_id,
p_input_value_id7 => c_life_pens_new_iv_id,
p_input_value_id8 => c_care_med_new_iv_id,
p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
p_entry_value6 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem_new),
p_entry_value7 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem_new),
p_entry_value8 => fnd_number.number_to_canonical(p_rec.care_med_ins_prem_new),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
l_ins_datetrack_update_mode := l_entry_rec.ins_datetrack_update_mode;
l_ins_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.ins_element_entry_id,l_esd,l_eed,l_effective_date);
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_business_group_id => g_business_group_id,
p_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
p_element_entry_id => l_entry_rec.is_element_entry_id,
p_object_version_number => l_entry_rec.is_ee_object_version_number,
p_input_value_id5 => c_social_iv_id,
p_input_value_id6 => c_mutual_aid_iv_id,
p_input_value_id7 => c_spouse_iv_id,
p_input_value_id9 => c_national_pens_iv_id,
p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
l_is_datetrack_update_mode := l_entry_rec.is_datetrack_update_mode;
l_is_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.is_element_entry_id,l_esd,l_eed,l_effective_date);
update pay_jp_isdf_entry_dml_v
set object_version_number = p_rec.object_version_number,
status = l_status,
ins_datetrack_update_mode = l_ins_datetrack_update_mode,
ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
life_gen_ins_prem_o = l_entry_rec.life_gen_ins_prem,
life_pens_ins_prem_o = l_entry_rec.life_pens_ins_prem,
nonlife_long_ins_prem_o = l_entry_rec.nonlife_long_ins_prem,
nonlife_short_ins_prem_o = l_entry_rec.nonlife_short_ins_prem,
earthquake_ins_prem_o = l_entry_rec.earthquake_ins_prem,
is_datetrack_update_mode = l_is_datetrack_update_mode,
is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
social_ins_prem_o = l_entry_rec.social_ins_prem,
mutual_aid_prem_o = l_entry_rec.mutual_aid_prem,
spouse_income_o = l_entry_rec.spouse_income,
national_pens_ins_prem_o = l_entry_rec.national_pens_ins_prem,
life_gen_ins_prem_new = l_entry_rec.life_gen_ins_prem_new,
life_pens_ins_prem_new = l_entry_rec.life_pens_ins_prem_new,
care_med_ins_prem_new = l_entry_rec.care_med_ins_prem_new
where row_id = p_rec.row_id;
update pay_jp_isdf_entry_dml_v
set object_version_number = p_rec.object_version_number,
status = l_status,
ins_datetrack_update_mode = l_ins_datetrack_update_mode,
is_datetrack_update_mode = l_is_datetrack_update_mode,
ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number)
where row_id = p_rec.row_id;
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_business_group_id => g_business_group_id,
p_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
p_element_entry_id => l_entry_rec.ins_element_entry_id,
p_object_version_number => l_entry_rec.ins_ee_object_version_number,
p_input_value_id1 => c_life_gen_iv_id,
p_input_value_id2 => c_life_pens_iv_id,
p_input_value_id3 => c_nonlife_long_iv_id,
p_input_value_id4 => c_nonlife_short_iv_id,
p_input_value_id5 => c_earthquake_iv_id,
p_input_value_id6 => c_life_gen_new_iv_id,
p_input_value_id7 => c_life_pens_new_iv_id,
p_input_value_id8 => c_care_med_new_iv_id,
p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
p_entry_value6 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem_new),
p_entry_value7 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem_new),
p_entry_value8 => fnd_number.number_to_canonical(p_rec.care_med_ins_prem_new),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
l_ins_datetrack_update_mode := l_entry_rec.ins_datetrack_update_mode;
l_ins_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.ins_element_entry_id,l_esd,l_eed,l_effective_date);
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_business_group_id => g_business_group_id,
p_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
p_element_entry_id => l_entry_rec.is_element_entry_id,
p_object_version_number => l_entry_rec.is_ee_object_version_number,
p_input_value_id5 => c_social_iv_id,
p_input_value_id6 => c_mutual_aid_iv_id,
p_input_value_id7 => c_spouse_iv_id,
p_input_value_id9 => c_national_pens_iv_id,
p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
l_is_datetrack_update_mode := l_entry_rec.is_datetrack_update_mode;
l_is_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.is_element_entry_id,l_esd,l_eed,l_effective_date);
update pay_jp_isdf_entry_dml_v
set object_version_number = p_rec.object_version_number,
status = l_status,
ins_datetrack_update_mode = l_ins_datetrack_update_mode,
ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
life_gen_ins_prem_o = l_entry_rec.life_gen_ins_prem,
life_pens_ins_prem_o = l_entry_rec.life_pens_ins_prem,
nonlife_long_ins_prem_o = l_entry_rec.nonlife_long_ins_prem,
nonlife_short_ins_prem_o = l_entry_rec.nonlife_short_ins_prem,
earthquake_ins_prem_o = l_entry_rec.earthquake_ins_prem,
is_datetrack_update_mode = l_is_datetrack_update_mode,
is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
social_ins_prem_o = l_entry_rec.social_ins_prem,
mutual_aid_prem_o = l_entry_rec.mutual_aid_prem,
spouse_income_o = l_entry_rec.spouse_income,
national_pens_ins_prem_o = l_entry_rec.national_pens_ins_prem,
life_gen_ins_prem_new = l_entry_rec.life_gen_ins_prem_new,
life_pens_ins_prem_new = l_entry_rec.life_pens_ins_prem_new,
care_med_ins_prem_new = l_entry_rec.care_med_ins_prem_new
where row_id = p_rec.row_id;
update pay_jp_isdf_entry_dml_v
set object_version_number = p_rec.object_version_number,
status = l_status,
ins_datetrack_update_mode = l_ins_datetrack_update_mode,
ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
life_gen_ins_prem_o = null,
life_pens_ins_prem_o = null,
nonlife_long_ins_prem_o = null,
nonlife_short_ins_prem_o = null,
earthquake_ins_prem_o = null,
is_datetrack_update_mode = l_is_datetrack_update_mode,
is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
social_ins_prem_o = null,
mutual_aid_prem_o = null,
spouse_income_o = null,
national_pens_ins_prem_o = null
where row_id = p_rec.row_id;
hr_utility.trace('end update_assact');
select *
from per_assignment_extra_info
where assignment_extra_info_id = p_rec.assignment_extra_info_id;
update pay_jp_isdf_life_gen_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_LIG_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => l_csr_aei.aei_information4,
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => l_csr_aei.aei_information10,
p_aei_information12 => p_rec.gen_ins_class_old_new);
update pay_jp_isdf_life_gen_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_LIG_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => l_csr_aei.aei_information10,
p_aei_information12 => p_rec.gen_ins_class_old_new);
update pay_jp_isdf_life_gen_dml_v
set object_version_number = p_rec.object_version_number,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_utility.trace('end update_assact');
select *
from per_assignment_extra_info
where assignment_extra_info_id = p_rec.assignment_extra_info_id;
update pay_jp_isdf_care_med_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_CMI_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => l_csr_aei.aei_information4,
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => l_csr_aei.aei_information10);
update pay_jp_isdf_care_med_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_CMI_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => l_csr_aei.aei_information10);
update pay_jp_isdf_care_med_dml_v
set object_version_number = p_rec.object_version_number,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_utility.trace('end update_assact');
select *
from per_assignment_extra_info
where assignment_extra_info_id = p_rec.assignment_extra_info_id;
update pay_jp_isdf_life_pens_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_LIP_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => l_csr_aei.aei_information4,
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => fnd_date.date_to_canonical(p_rec.ins_period_start_date),
p_aei_information7 => p_rec.ins_period,
p_aei_information8 => p_rec.contractor_name,
p_aei_information9 => p_rec.beneficiary_name,
p_aei_information10 => p_rec.beneficiary_relship,
p_aei_information11 => l_csr_aei.aei_information11,
p_aei_information13 => p_rec.pens_ins_class_old_new);
update pay_jp_isdf_life_pens_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_LIP_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => fnd_date.date_to_canonical(p_rec.ins_period_start_date),
p_aei_information7 => p_rec.ins_period,
p_aei_information8 => p_rec.contractor_name,
p_aei_information9 => p_rec.beneficiary_name,
p_aei_information10 => p_rec.beneficiary_relship,
p_aei_information11 => l_csr_aei.aei_information11,
p_aei_information13 => p_rec.pens_ins_class_old_new);
update pay_jp_isdf_life_pens_dml_v
set object_version_number = p_rec.object_version_number,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_utility.trace('end update_assact');
select *
from per_assignment_extra_info
where assignment_extra_info_id = p_rec.assignment_extra_info_id;
update pay_jp_isdf_nonlife_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_AI_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => l_csr_aei.aei_information4,
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => p_rec.maturity_repayment,
p_aei_information11 => l_csr_aei.aei_information11,
p_aei_information13 => l_csr_aei.aei_information13);
update pay_jp_isdf_nonlife_dml_v
set object_version_number = p_rec.object_version_number,
status = p_rec.status,
aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
where row_id = p_rec.row_id;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
p_object_version_number => l_csr_aei.object_version_number,
p_aei_information_category => 'JP_ASS_AI_INFO',
p_aei_information1 => l_csr_aei.aei_information1,
p_aei_information2 => l_csr_aei.aei_information2,
p_aei_information3 => l_csr_aei.aei_information3,
p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
p_aei_information5 => p_rec.ins_type,
p_aei_information6 => p_rec.ins_period,
p_aei_information7 => p_rec.contractor_name,
p_aei_information8 => p_rec.beneficiary_name,
p_aei_information9 => p_rec.beneficiary_relship,
p_aei_information10 => p_rec.maturity_repayment,
p_aei_information11 => l_csr_aei.aei_information11,
p_aei_information13 => l_csr_aei.aei_information13);
update pay_jp_isdf_nonlife_dml_v
set object_version_number = p_rec.object_version_number,
aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
where row_id = p_rec.row_id;
hr_utility.trace('end update_assact');
select *
from pay_jp_isdf_entry_v
where assignment_action_id = l_assact_rec.assignment_action_id
and status <> 'D'
for update nowait;
select *
from pay_jp_isdf_life_gen_v
where assignment_action_id = l_assact_rec.assignment_action_id
and gen_ins_class <> 'PC'
and status = 'D'
for update nowait;
select *
from pay_jp_isdf_life_gen_v
where assignment_action_id = l_assact_rec.assignment_action_id
and gen_ins_class <> 'PC'
and status = 'Q'
for update nowait;
select *
from pay_jp_isdf_life_gen_v
where assignment_action_id = l_assact_rec.assignment_action_id
and gen_ins_class <> 'PC'
and status = 'I'
for update nowait;
select *
from pay_jp_isdf_care_med_v
where assignment_action_id = l_assact_rec.assignment_action_id
and care_ins_class <> 'PC'
and status = 'D'
for update nowait;
select *
from pay_jp_isdf_care_med_v
where assignment_action_id = l_assact_rec.assignment_action_id
and care_ins_class <> 'PC'
and status = 'Q'
for update nowait;
select *
from pay_jp_isdf_care_med_v
where assignment_action_id = l_assact_rec.assignment_action_id
and care_ins_class <> 'PC'
and status = 'I'
for update nowait;
select *
from pay_jp_isdf_life_pens_v
where assignment_action_id = l_assact_rec.assignment_action_id
and pens_ins_class <> 'PC'
and status = 'D'
for update nowait;
select *
from pay_jp_isdf_life_pens_v
where assignment_action_id = l_assact_rec.assignment_action_id
and pens_ins_class <> 'PC'
and status = 'Q'
for update nowait;
select *
from pay_jp_isdf_life_pens_v
where assignment_action_id = l_assact_rec.assignment_action_id
and pens_ins_class <> 'PC'
and status = 'I'
for update nowait;
select *
from pay_jp_isdf_nonlife_v
where assignment_action_id = l_assact_rec.assignment_action_id
and nonlife_ins_class <> 'PC'
and status = 'D'
for update nowait;
select *
from pay_jp_isdf_nonlife_v
where assignment_action_id = l_assact_rec.assignment_action_id
and nonlife_ins_class <> 'PC'
and status = 'Q'
for update nowait;
select *
from pay_jp_isdf_nonlife_v
where assignment_action_id = l_assact_rec.assignment_action_id
and nonlife_ins_class <> 'PC'
and status = 'I'
for update nowait;
insert_session(l_effective_date);
delete_session;
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transfer_status = 'T',
transfer_date = fnd_date.date_to_canonical(l_effective_date)
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
select *
from pay_jp_isdf_entry_v
where assignment_action_id = l_assact_rec.assignment_action_id
for update nowait;
select rowid row_id,
action_information_id,
object_version_number,
action_information_category
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_ISDF_ASSACT'
and action_information1 <> 'D';
insert_session(l_effective_date);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_datetrack_delete_mode => 'DELETE',
p_element_entry_id => l_csr_entry.ins_element_entry_id,
p_object_version_number => l_csr_entry.ins_ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => l_effective_date,
p_datetrack_delete_mode => 'DELETE',
p_element_entry_id => l_csr_entry.is_element_entry_id,
p_object_version_number => l_csr_entry.is_ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
update pay_jp_isdf_entry_dml_v
set object_version_number = l_csr_entry.object_version_number + 1,
ins_ee_object_version_number = fnd_number.number_to_canonical(l_csr_entry.ins_ee_object_version_number),
is_ee_object_version_number = fnd_number.number_to_canonical(l_csr_entry.is_ee_object_version_number)
where row_id = l_csr_entry.row_id;
delete
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_ISDF_ASSACT';
elsif p_mode = 'DELETE' then
--
open csr_del;
update pay_action_information
set object_version_number = l_object_version_number,
action_information1 = 'D'
where rowid = l_csr_del.row_id;
delete_session;
update pay_jp_isdf_assact_dml_v
set object_version_number = p_object_version_number,
transfer_status = 'E',
expiry_date = fnd_date.date_to_canonical(p_expiry_date)
where row_id = l_assact_rec.row_id;
hr_utility.trace('end update_assact');
select /*+ ORDERED */
assact.action_information_id,
assact.object_version_number,
pp.full_name,
pa.assignment_number
from pay_assignment_actions paa,
pay_jp_isdf_assact_v assact,
per_all_assignments_f pa,
per_all_people_f pp
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transaction_status = 'N'
and pa.assignment_id = assact.assignment_id
and assact.effective_date
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and assact.effective_date
between pp.effective_start_date and pp.effective_end_date
order by lpad(pa.assignment_number,10,' '),
pp.full_name;
select effective_date
into l_effective_date
from pay_jp_isdf_pact_v
where payroll_action_id = p_payroll_action_id;
insert_session(l_effective_date);
delete_session;
select /*+ ORDERED */
assact.action_information_id,
assact.object_version_number,
pp.full_name,
pa.assignment_number
from pay_assignment_actions paa,
pay_jp_isdf_assact_v assact,
per_all_assignments_f pa,
per_all_people_f pp
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transaction_status = 'F'
and pa.assignment_id = assact.assignment_id
and assact.effective_date
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and assact.effective_date
between pp.effective_start_date and pp.effective_end_date
order by lpad(pa.assignment_number,10,' '),
pp.full_name;
select effective_date
into l_effective_date
from pay_jp_isdf_pact_v
where payroll_action_id = p_payroll_action_id;
insert_session(l_effective_date);
delete_session;
select /*+ ORDERED */
assact.action_information_id,
assact.object_version_number,
pp.full_name,
pa.assignment_number
from pay_assignment_actions paa,
pay_jp_isdf_assact_v assact,
per_all_assignments_f pa,
per_all_people_f pp
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transaction_status = 'A'
and assact.transfer_status = 'U'
and pa.assignment_id = assact.assignment_id
and assact.effective_date
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and assact.effective_date
between pp.effective_start_date and pp.effective_end_date
order by lpad(pa.assignment_number,10,' '),
pp.full_name;
select effective_date
into l_effective_date
from pay_jp_isdf_pact_v
where payroll_action_id = p_payroll_action_id;
insert_session(l_effective_date);
delete_session;
select /*+ ORDERED */
assact.action_information_id,
assact.object_version_number,
pp.full_name,
pa.assignment_number
from pay_assignment_actions paa,
pay_jp_isdf_assact_v assact,
per_all_assignments_f pa,
per_all_people_f pp
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transaction_status = 'A'
and assact.transfer_status = 'T'
and pa.assignment_id = assact.assignment_id
and assact.effective_date
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and assact.effective_date
between pp.effective_start_date and pp.effective_end_date
order by lpad(pa.assignment_number,10,' '),
pp.full_name;
select effective_date
into l_effective_date
from pay_jp_isdf_pact_v
where payroll_action_id = p_payroll_action_id;
insert_session(l_effective_date);
delete_session;