[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_entries(errbuf out nocopy VARCHAR2,
retcode out nocopy NUMBER,
p_element_type_id in pay_element_types_f.element_type_id%type,
p_start_date in VARCHAR2,
p_end_date in VARCHAR2,
p_bus_grp_id in pay_element_types_f.business_group_id%type,
p_assignment_set_id in Number )
is
l_ben_start_date_id pay_input_values_f.input_value_id%type;
select element_name
from pay_element_types_f pet
where pet.element_type_id = v_element_type_id;
select input_value_id
from pay_input_values_f piv
where piv.element_type_id = v_element_type_id
and piv.NAME = v_ben_date_type;
select /*+ ordered */
pee.element_entry_id, pee.object_version_number, pee.effective_start_date
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev_sd,
pay_element_entry_values_f peev_ed
where pel.element_type_id = v_element_type_id
and pee.element_link_id = pel.element_link_id
and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
and pee.effective_end_date between pel.effective_start_date and pel.effective_end_date
and peev_sd.element_entry_id = pee.element_entry_id
and peev_ed.element_entry_id = pee.element_entry_id
and peev_sd.element_entry_id = peev_ed.element_entry_id
and peev_sd.input_value_id = v_ben_start_date_id
and peev_sd.screen_entry_value >= v_start_date
and peev_ed.input_value_id = v_ben_end_date_id
and peev_ed.screen_entry_value <= v_end_date
and exists( select /*+ no_unnest */
1
from per_all_assignments_f paa
where paa.assignment_id = pee.assignment_id
and paa.business_group_id = v_bus_grp_id);
select /*+ ordered */
pee.element_entry_id, pee.object_version_number, pee.effective_start_date
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev_sd,
pay_element_entry_values_f peev_ed
where pel.element_type_id = v_element_type_id
and pee.element_link_id = pel.element_link_id
and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
and pee.effective_end_date between pel.effective_start_date and pel.effective_end_date
and peev_sd.element_entry_id = pee.element_entry_id
and peev_ed.element_entry_id = pee.element_entry_id
and peev_sd.element_entry_id = peev_ed.element_entry_id
and peev_sd.input_value_id = v_ben_start_date_id
and peev_sd.screen_entry_value >= v_start_date
and peev_ed.input_value_id = v_ben_end_date_id
and peev_ed.screen_entry_value <= v_end_date
and exists( select /*+ no_unnest */
1
from per_all_assignments_f paa,
hr_assignment_sets has,
hr_assignment_set_amendments hasa
where paa.assignment_id = pee.assignment_id
and paa.business_group_id = v_bus_grp_id
and ( has.assignment_set_id = v_assignment_set_id
and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
and has.assignment_set_id = hasa.assignment_set_id(+)
and nvl(hasa.include_or_exclude, 'I') = 'I'
and nvl(hasa.assignment_id, paa.assignment_id) = paa.assignment_id));
hr_utility.TRACE('Delete entries for element entry id '|| to_char(del_element_entry.element_entry_id) );
pay_element_entry_api.delete_element_entry(p_datetrack_delete_mode => 'ZAP',
p_effective_date => del_element_entry.effective_start_date,
p_element_entry_id => del_element_entry.element_entry_id,
p_object_version_number => del_element_entry.object_version_number,
p_effective_start_date => l_effective_start_date_dummy,
p_effective_end_date => l_effective_end_date_dummy,
p_delete_warning => l_del_warning_dummy);
hr_utility.TRACE('Commiting delete, counter = '|| to_char(l_count) );
hr_utility.TRACE('Delete entries for element entry id '|| to_char(del_element_entry.element_entry_id) );
pay_element_entry_api.delete_element_entry(p_datetrack_delete_mode => 'ZAP',
p_effective_date => del_element_entry.effective_start_date,
p_element_entry_id => del_element_entry.element_entry_id,
p_object_version_number => del_element_entry.object_version_number,
p_effective_start_date => l_effective_start_date_dummy,
p_effective_end_date => l_effective_end_date_dummy,
p_delete_warning => l_del_warning_dummy);
hr_utility.TRACE('Commiting delete, counter = '|| to_char(l_count) );
end delete_entries;
select sum(nvl(pai.action_information7, 0) )
into l_loan_amount
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_archive_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_comp.action_information6 = p_employers_ref_no
-- AND pai_comp.action_information7 = p_employers_name
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = p_person_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'INT FREE AND LOW INT LOANS';
select global_value
from ff_globals_f
where legislation_code = 'GB'
and GLOBAL_NAME = p_global_name
and fnd_date.canonical_to_date(p_benefit_end_date)
between effective_start_date and effective_end_date;
if ((l_present_val = p_variable_value) and (PAY_GB_P11D_ARCHIVE_SS.g_updated_flag = 'Y'))
then
l_new_val := l_present_val ;
select description into l_description
from hr_lookups hlu
where hlu.lookup_type = p_lookup_type
and hlu.lookup_code = p_lookup_code
and hlu.ENABLED_FLAG = 'Y'
and fnd_date.canonical_to_date(p_effective_date) between
nvl(hlu.START_DATE_ACTIVE,fnd_date.canonical_to_date(p_effective_date))
and nvl(hlu.END_DATE_ACTIVE,fnd_date.canonical_to_date(p_effective_date));
select application_id
into l_application_id
from fnd_application
where APPLICATION_SHORT_NAME = p_application;
select assignment_id
from pay_assignment_actions
where assignment_action_id = p_action_id;
select min(paa2.assignment_id)
from pay_assignment_actions paa,
pay_assignment_actions paa2,
pay_action_information pai_comp,
pay_action_information pai_person
where paa.assignment_action_id = p_action_id
and paa2.payroll_action_id = paa.payroll_action_id
and pai_comp.action_context_id = paa2.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_person.action_context_id = paa2.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = p_person_id
and pai_comp.action_information6 = p_emp_ref;
select paa.assignment_number
from per_assignments_f paa
where paa.assignment_id = l_asg_id
and paa.effective_end_date = (select max(paa2.effective_end_date)
from per_assignments_f paa2
where paa2.assignment_id = l_asg_id);
select employee_number
from per_all_people_f
where person_id = p_person_id;
select action_information1
into g_person_id
from pay_action_information pai_person
where pai_person.action_context_id = p_assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address';
select action_information6,
action_information7
into g_emp_ref_no,
g_employer_name
from pay_action_information pai_comp
where pai_comp.action_context_id = p_assignment_action_id
and pai_comp.action_context_type = 'AAP'
and pai_comp.action_information_category = 'EMEA PAYROLL INFO';
select decode(action_information4, 'Y', 'Y', 'N'),
action_information8, -- P11D changes 07/08 last_name
action_information6, -- P11D changes 07/08 first_name
substr(action_information15,9,2) ||
substr(action_information15,6,2) ||
substr(action_information15,1,4),
action_information17
into l_p11d_fields.director_flag,
l_p11d_fields.sur_name,
l_p11d_fields.fore_name,
l_p11d_fields.date_of_birth,
l_p11d_fields.gender
from pay_action_information pai_gb
where pai_gb.action_context_id = p_assignment_action_id
and pai_gb.action_context_type = 'AAP'
and pai_gb.action_information_category = 'GB EMPLOYEE DETAILS';
select action_information1,
action_information4,
action_information10 -- emp number
-- action_information14 -- assign num
into l_p11d_fields.full_name,
l_p11d_fields.national_ins_no,
l_p11d_fields.employee_number
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'EMPLOYEE DETAILS';
select payroll_action_id
into l_pactid
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select action_information1,
decode(action_information2,'0',null,to_char(to_number(action_information2),'FM999,999,990.00')),
decode(action_information2,'0',null,to_char(to_number(action_information3),'FM999,999,990.00')),
to_char(to_number(nvl(action_information4,'0')),'FM999,999,990.00'),
action_information5,
to_char(to_number(nvl(action_information6,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information7,'0')),'FM999,999,990.00'),
decode(action_information8,'0',null,to_char(to_number(action_information8),'FM999,999,990.00')),
decode(action_information8,'0',null,to_char(to_number(action_information9),'FM999,999,990.00')),
to_char(to_number(nvl(action_information10,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information11,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information12,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information13,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information14,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information15,'0')),'FM999,999,990.00'),
decode(action_information16,'0',null,to_char(to_number(action_information16),'FM999,999,990.00')),
decode(action_information16,'0',null,to_char(to_number(action_information17),'FM999,999,990.00')),
to_char(to_number(nvl(action_information18,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information19,'0')),'FM999,999,990.00'),
decode(action_information20,'0',null,to_char(to_number(action_information20),'FM999,999,990.00')),
decode(action_information20,'0',null,to_char(to_number(action_information21),'FM999,999,990.00')),
to_char(to_number(nvl(action_information22,'0')),'FM999,999,990.00'),
action_information23,
decode(action_information24,'0',null,to_char(to_number(action_information24),'FM999,999,990.00')),
decode(action_information24,'0',null,to_char(to_number(action_information25),'FM999,999,990.00')),
to_char(to_number(nvl(action_information26,'0')),'FM999,999,990.00'),
decode(action_information27, 'Y', 'Y', 'N'),
to_char(to_number(nvl(action_information28,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information29,'0')),'FM999,999,990.00'),
action_information30
into l_p11d_fields.a_desc, l_p11d_fields.a_cost, l_p11d_fields.a_amg,
l_p11d_fields.a_ce, l_p11d_fields.b_desc, l_p11d_fields.b_ce,
l_p11d_fields.b_tnp, l_p11d_fields.c_cost, l_p11d_fields.c_amg,
l_p11d_fields.c_ce, l_p11d_fields.d_ce, l_p11d_fields.e_ce,
l_p11d_fields.f_tcce, l_p11d_fields.f_tfce, l_p11d_fields.g_ce,
l_p11d_fields.i_cost, l_p11d_fields.i_amg, l_p11d_fields.i_ce,
l_p11d_fields.j_ce, l_p11d_fields.k_cost, l_p11d_fields.k_amg,
l_p11d_fields.k_ce, l_p11d_fields.l_desc, l_p11d_fields.l_cost,
l_p11d_fields.l_amg, l_p11d_fields.l_ce, l_p11d_fields.m_shares,
l_h_ce, l_h_count, l_f_count
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
select action_information1,
decode(action_information2,'0',null,to_char(to_number(action_information2),'FM999,999,990.00')),
decode(action_information2,'0',null,to_char(to_number(action_information3),'FM999,999,990.00')),
to_char(to_number(nvl(action_information4,'0')),'FM999,999,990.00'),
action_information5,
decode(action_information6,'0',null,to_char(to_number(action_information6),'FM999,999,990.00')),
decode(action_information6,'0',null,to_char(to_number(action_information7),'FM999,999,990.00')),
to_char(to_number(nvl(action_information8,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information9,'0')),'FM999,999,990.00'),
decode(action_information10,'0',null,to_char(to_number(action_information10),'FM999,999,990.00')),
decode(action_information10,'0',null,to_char(to_number(action_information11),'FM999,999,990.00')),
to_char(to_number(nvl(action_information12,'0')),'FM999,999,990.00'),
decode(action_information13,'0',null,to_char(to_number(action_information13),'FM999,999,990.00')),
decode(action_information13,'0',null,to_char(to_number(action_information14),'FM999,999,990.00')),
to_char(to_number(nvl(action_information15,'0')),'FM999,999,990.00'),
decode(action_information16, 'Y', 'Y', 'N'),
decode(action_information17,'0',null,to_char(to_number(action_information17),'FM999,999,990.00')),
decode(action_information17,'0',null,to_char(to_number(action_information18),'FM999,999,990.00')),
to_char(to_number(nvl(action_information19,'0')),'FM999,999,990.00'),
decode(action_information20,'0',null,to_char(to_number(action_information20),'FM999,999,990.00')),
decode(action_information20,'0',null,to_char(to_number(action_information21),'FM999,999,990.00')),
to_char(to_number(nvl(action_information22,'0')),'FM999,999,990.00'),
decode(action_information23,'0',null,to_char(to_number(action_information23),'FM999,999,990.00')),
decode(action_information23,'0',null,to_char(to_number(action_information24),'FM999,999,990.00')),
to_char(to_number(nvl(action_information25,'0')),'FM999,999,990.00'),
action_information26,
decode(action_information27,'0',null,to_char(to_number(action_information27),'FM999,999,990.00')),
decode(action_information27,'0',null,to_char(to_number(action_information28),'FM999,999,990.00')),
to_char(to_number(nvl(action_information29,'0')),'FM999,999,990.00')
into l_p11d_fields.n_desc, l_p11d_fields.n_cost, l_p11d_fields.n_amg,
l_p11d_fields.n_ce, l_p11d_fields.na_desc, l_p11d_fields.na_cost,
l_p11d_fields.na_amg, l_p11d_fields.na_ce,
l_p11d_fields.n_taxpaid, l_p11d_fields.o1_cost,
l_p11d_fields.o1_amg, l_p11d_fields.o1_ce, l_p11d_fields.o2_cost,
l_p11d_fields.o2_amg, l_p11d_fields.o2_ce,
l_p11d_fields.o_toi,
l_p11d_fields.o3_cost, l_p11d_fields.o3_amg, l_p11d_fields.o3_ce,
l_p11d_fields.o4_cost, l_p11d_fields.o4_amg, l_p11d_fields.o4_ce,
l_p11d_fields.o5_cost, l_p11d_fields.o5_amg, l_p11d_fields.o5_ce,
l_p11d_fields.o6_desc, l_p11d_fields.o6_cost,
l_p11d_fields.o6_amg, l_p11d_fields.o6_ce
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTB';
select substr(action_information10,9,2) || ' ' ||
substr(action_information10,6,2) || ' ' ||
substr(action_information10,1,4) ,
decode (action_information11,null,'N','Y'),to_number(nvl(ACTION_INFORMATION23,'0'))
into l_p11d_fields.f_date_free,l_p11d_fields.f_rein_yr,
l_h_sum_max_amt_outstanding
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTC';
select action_information1,
decode(action_information2,'0',null,to_char(to_number(action_information2),'FM999,999,990.00')),
decode(action_information2,'0',null,to_char(to_number(action_information3),'FM999,999,990.00')),
to_char(to_number(nvl(action_information4,'0')),'FM999,999,990.00'),
action_information5,
to_char(to_number(nvl(action_information6,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information7,'0')),'FM999,999,990.00'),
decode(action_information8,'0',null,to_char(to_number(action_information8),'FM999,999,990.00')),
decode(action_information8,'0',null,to_char(to_number(action_information9),'FM999,999,990.00')),
to_char(to_number(nvl(action_information10,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information11,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information12,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information13,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information14,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information15,'0')),'FM999,999,990.00'),
decode(action_information16,'0',null,to_char(to_number(action_information16),'FM999,999,990.00')),
decode(action_information16,'0',null,to_char(to_number(action_information17),'FM999,999,990.00')),
to_char(to_number(nvl(action_information18,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information19,'0')),'FM999,999,990.00'),
decode(action_information20,'0',null,to_char(to_number(action_information20),'FM999,999,990.00')),
decode(action_information20,'0',null,to_char(to_number(action_information21),'FM999,999,990.00')),
to_char(to_number(nvl(action_information22,'0')),'FM999,999,990.00'),
action_information23,
decode(action_information24,'0',null,to_char(to_number(action_information26) + to_number(action_information25),'FM999,999,990.00')), --Changed for bug 8204969
decode(action_information24,'0',null,to_char(to_number(action_information25),'FM999,999,990.00')),
to_char(to_number(nvl(action_information26,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information28,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information29,'0')),'FM999,999,990.00'),
action_information30
into l_p11d_fields.a_desc, l_p11d_fields.a_cost, l_p11d_fields.a_amg,
l_p11d_fields.a_ce, l_p11d_fields.b_desc, l_p11d_fields.b_ce,
l_p11d_fields.b_tnp, l_p11d_fields.c_cost, l_p11d_fields.c_amg,
l_p11d_fields.c_ce, l_p11d_fields.d_ce, l_p11d_fields.e_ce,
l_p11d_fields.f_tcce, l_p11d_fields.f_tfce, l_p11d_fields.g_ce,
l_p11d_fields.i_cost, l_p11d_fields.i_amg, l_p11d_fields.i_ce,
l_p11d_fields.j_ce, l_p11d_fields.k_cost, l_p11d_fields.k_amg,
l_p11d_fields.k_ce, l_p11d_fields.l_desc, l_p11d_fields.l_cost,
l_p11d_fields.l_amg, l_p11d_fields.l_ce,
l_h_ce, l_h_count, l_f_count
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
select action_information1,
decode(action_information2,'0',null,to_char(to_number(action_information2),'FM999,999,990.00')),
decode(action_information2,'0',null,to_char(to_number(action_information3),'FM999,999,990.00')),
to_char(to_number(nvl(action_information4,'0')),'FM999,999,990.00'),
action_information5,
decode(action_information6,'0',null,to_char(to_number(action_information6),'FM999,999,990.00')),
decode(action_information6,'0',null,to_char(to_number(action_information7),'FM999,999,990.00')),
to_char(to_number(nvl(action_information8,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information9,'0')),'FM999,999,990.00'),
decode(action_information10,'0',null,to_char(to_number(action_information10),'FM999,999,990.00')),
decode(action_information10,'0',null,to_char(to_number(action_information11),'FM999,999,990.00')),
to_char(to_number(nvl(action_information12,'0')),'FM999,999,990.00'),
decode(action_information13,'0',null,to_char(to_number(action_information13),'FM999,999,990.00')),
decode(action_information13,'0',null,to_char(to_number(action_information14),'FM999,999,990.00')),
to_char(to_number(nvl(action_information15,'0')),'FM999,999,990.00'),
decode(action_information16, 'Y', 'Y', 'N'),
decode(action_information17,'0',null,to_char(to_number(action_information17),'FM999,999,990.00')),
decode(action_information17,'0',null,to_char(to_number(action_information18),'FM999,999,990.00')),
to_char(to_number(nvl(action_information19,'0')),'FM999,999,990.00'),
decode(action_information20,'0',null,to_char(to_number(action_information20),'FM999,999,990.00')),
decode(action_information20,'0',null,to_char(to_number(action_information21),'FM999,999,990.00')),
to_char(to_number(nvl(action_information22,'0')),'FM999,999,990.00'),
decode(action_information23,'0',null,to_char(to_number(action_information23),'FM999,999,990.00')),
decode(action_information23,'0',null,to_char(to_number(action_information24),'FM999,999,990.00')),
to_char(to_number(nvl(action_information25,'0')),'FM999,999,990.00'),
action_information26,
decode(action_information27,'0',null,to_char(to_number(action_information27),'FM999,999,990.00')),
decode(action_information27,'0',null,to_char(to_number(action_information28),'FM999,999,990.00')),
to_char(to_number(nvl(action_information29,'0')),'FM999,999,990.00'),
to_char(to_number(nvl(action_information30,'0')),'FM999,999,990.00') -- P11D changes 07/08
into l_p11d_fields.n_desc, l_p11d_fields.n_cost, l_p11d_fields.n_amg,
l_p11d_fields.n_ce, l_p11d_fields.na_desc, l_p11d_fields.na_cost,
l_p11d_fields.na_amg, l_p11d_fields.na_ce,
l_p11d_fields.n_taxpaid, l_p11d_fields.o1_cost,
l_p11d_fields.o1_amg, l_p11d_fields.o1_ce, l_p11d_fields.o2_cost,
l_p11d_fields.o2_amg, l_p11d_fields.o2_ce,
l_p11d_fields.o_toi,
l_p11d_fields.o3_cost, l_p11d_fields.o3_amg, l_p11d_fields.o3_ce,
l_p11d_fields.o4_cost, l_p11d_fields.o4_amg, l_p11d_fields.o4_ce,
l_p11d_fields.o5_cost, l_p11d_fields.o5_amg, l_p11d_fields.o5_ce,
l_p11d_fields.o6_desc, l_p11d_fields.o6_cost,
l_p11d_fields.o6_amg, l_p11d_fields.o6_ce, l_p11d_fields.g_cef
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTB';
select to_number(nvl(ACTION_INFORMATION23,'0'))
into l_h_sum_max_amt_outstanding
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTC';
select
substr(pai_emp.action_information3,9,2) || ' ' ||
substr(pai_emp.action_information3,6,2) || ' ' ||
substr(pai_emp.action_information3,1,4) f_start,
substr(pai_emp.action_information4,9,2) || ' ' ||
substr(pai_emp.action_information4,6,2) || ' ' ||
substr(pai_emp.action_information4,1,4) f_end,
pai_emp.action_information6 || ' ' ||
pai_emp.action_information7 f_make,
substr(pai_emp.action_information8,9,2) || ' ' ||
substr(pai_emp.action_information8,6,2) || ' ' ||
substr(pai_emp.action_information8,1,4) f_dreg,
to_char(to_number(pai_emp.action_information9),'FM999,999,990.00') f_lprice,
to_char(to_number(pai_emp.action_information10),'FM999,999,990.00') f_cc,
to_char(to_number(pai_emp.action_information11),'FM999,999,990.00') f_fcc,
decode( pai_emp.action_information12,'0',null,PAY_GB_P11D_MAGTAPE.get_description(
pai_emp.action_information12,'GB_FUEL_TYPE',pai_emp.action_information4)) f_fuel,
pai_emp.action_information13 f_efig,
/* bug 8277887 checking the flag if co2 emisiion is either zero or null */
decode(NVL(pai_emp.action_information13,'0'),'0','Y', 'N') f_nfig,
to_char(to_number(pai_emp.action_information15),'FM999,999,990.00') f_oprice,
to_char(to_number(pai_emp.action_information16),'FM999,999,990.00') f_cost,
to_char(to_number(pai_emp.action_information17),'FM999,999,990.00') f_amg,
substr(action_information26,9,2) || ' ' ||
substr(action_information26,6,2) || ' ' ||
substr(action_information26,1,4) f_date_free,
decode(action_information27,'Y','Y','N') f_rein_yr,
pai_emp.action_information18 f_esize
from pay_action_information pai_emp
where pai_emp.action_information_category = 'CAR AND CAR FUEL 2003_04'
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_context_id in( select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = p_person_id
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name)
order by pai_emp.action_information3 desc, -- ben st dt
pai_emp.action_information4 desc, -- ben end dt
pai_emp.action_information10 desc, -- cc for car
pai_emp.action_information11 desc, -- cc for fuel
pai_emp.action_information1 desc, -- ele entrty id
pai_emp.action_information2 desc; -- effec date
select
pai_emp.action_information5 h_njb,
to_char(to_number(pai_emp.action_information6),'FM999,999,990.00') h_ayb,
to_char(to_number(pai_emp.action_information7),'FM999,999,990.00') h_mao,
decode(pai_emp.action_information8,null,'NIL','0','NIL',to_char(to_number(pai_emp.action_information8),'FM999,999,990.00')) h_ip,
substr(pai_emp.action_information9,9,2) || decode(pai_emp.action_information9,null,null,' ') ||
substr(pai_emp.action_information9,6,2) || decode(pai_emp.action_information9,null,null,' ') ||
substr(pai_emp.action_information9,1,4) h_dlm,
substr(pai_emp.action_information10,9,2) ||decode(pai_emp.action_information10,null,null,' ') ||
substr(pai_emp.action_information10,6,2) ||decode(pai_emp.action_information10,null,null,' ') ||
substr(pai_emp.action_information10,1,4) h_dld,
to_char(to_number(nvl(pai_emp.action_information11,'0')),'FM999,999,990.00') h_ce,
to_char(to_number(nvl(pai_emp.action_information16,'0')),'FM999,999,990.00') h_aye
from pay_action_information pai_emp
where pai_emp.action_information_category = 'INT FREE AND LOW INT LOANS'
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_context_id in ( select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = p_person_id
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name)
and to_number(nvl(pai_emp.action_information11,'0')) > 0 -- report only int free lons where CE is greater than 0
order by pai_emp.action_information3, -- ben st dt
pai_emp.action_information4, -- ben end dt
pai_emp.action_information9, -- dt loan made
pai_emp.action_information10, -- dt loan disc
pai_emp.action_information11, -- cc
pai_emp.action_information1, -- ele entrty id
pai_emp.action_information2; -- effec date
select payroll_action_id
into g_payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select payroll_action_id
into l_pactid
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select /*+ ORDERED use_nl(paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
sum(to_number(nvl(pai.action_information7,0)))
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'INT FREE AND LOW INT LOANS'
and pai_person.action_context_id = paa.assignment_action_id
and paa.assignment_action_id = c_asg_act_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(c_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2)
use_index(pai_a,pay_action_information_n2)*/
pai_a.action_information12
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_a,
pay_action_information pai_person
where paa.assignment_action_id = c_asg_act_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = pai.action_information_category
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_context_type = 'AAP'
and upper(pai_person.action_information13) = upper(c_emp_ref)
and pai_a.action_context_id = paa.assignment_action_id
and pai_a.action_context_type = 'AAP'
and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and pai.action_information_category = 'MILEAGE ALLOWANCE AND PPAYMENT';
select to_number(global_value)
from ff_globals_f
where global_name = 'P11D_LOW_INT_LOAN_THRESHOLD'
and to_date('05-04-' || c_rep_run,'DD-MM-YYYY') between effective_start_date and effective_end_date;
select action_information6
from pay_action_information pai_comp
where pai_comp.action_context_id = p_assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO';
select payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select 1
from pay_action_information pai
where pai.action_context_id = c_asg_act_id
and pai.action_context_type = 'AAP'
and pai.action_information_category in ('ASSETS TRANSFERRED',
'PAYMENTS MADE FOR EMP',
'VOUCHERS OR CREDIT CARDS',
'LIVING ACCOMMODATION',
'CAR AND CAR FUEL 2003_04',
'VANS 2007',
'PVT MED TREATMENT OR INSURANCE',
'RELOCATION EXPENSES',
'SERVICES SUPPLIED',
'ASSETS AT EMP DISPOSAL',
'OTHER ITEMS',
'OTHER ITEMS NON 1A',
'EXPENSES PAYMENTS',
'MARORS');
select paa.assignment_action_id
from pay_action_information pai,
pay_assignment_actions paa,
per_all_assignments_f paaf
where paa.payroll_action_id = c_pact_id
and pai.action_context_id = paa.assignment_action_id
and paa.assignment_action_id <> c_asg_act_id
and paaf.person_id = c_person_id
and paaf.assignment_id = paa.assignment_id
and pai.action_context_type = 'AAP'
and pai.action_information_category in ('ASSETS TRANSFERRED',
'PAYMENTS MADE FOR EMP',
'VOUCHERS OR CREDIT CARDS',
'LIVING ACCOMMODATION',
'CAR AND CAR FUEL 2003_04',
'VANS 2007',
'PVT MED TREATMENT OR INSURANCE',
'RELOCATION EXPENSES',
'SERVICES SUPPLIED',
'ASSETS AT EMP DISPOSAL',
'OTHER ITEMS',
'OTHER ITEMS NON 1A',
'EXPENSES PAYMENTS',
'MARORS',
'INT FREE AND LOW INT LOANS',
'MILEAGE ALLOWANCE AND PPAYMENT');
select to_char(sysdate,'YYYY'), ses.effective_date
from fnd_sessions ses
where ses.session_id = userenv('sessionid');
procedure update_leg_process_status(errbuf out nocopy VARCHAR2,
retcode out nocopy NUMBER,
p_payroll_action_id in Number,
p_new_status in Varchar2)
is
l_param_string VARCHAR2(2000);
select legislative_parameters
into l_param_string
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
update pay_payroll_actions
set legislative_parameters =l_param_string_before_val || p_new_status ||
' Status=' || p_new_status || l_param_string_after_val
where payroll_action_id = p_payroll_action_id; --8875;
select meaning
from hr_lookups hlu
where hlu.lookup_type = p_lookup_type
and hlu.lookup_code = p_lookup_code
and hlu.enabled_flag='Y'
and p_effective_date between
nvl( hlu.START_DATE_ACTIVE,p_effective_date)
and nvl( hlu.END_DATE_ACTIVE , p_effective_date);
select PAYROLL_NAME
from pay_payrolls_f
where PAYROLL_ID = l_payroll_id
and p_effective_date between
nvl(effective_start_date,p_effective_date)
and nvl(effective_start_date,p_effective_date);
select FULL_NAME
from per_people_f
where person_ID = l_person_id
and p_effective_date between
nvl(effective_start_date,p_effective_date)
and nvl(effective_start_date,p_effective_date);
select CONSOLIDATION_SET_NAME
from PAY_CONSOLIDATION_SETS
where CONSOLIDATION_SET_ID = l_consolidation_set_id;
select ASSIGNMENT_SET_NAME
from HR_ASSIGNMENT_SETS_V
where ASSIGNMENT_SET_ID =l_assignment_set_id;
select effective_date into l_effective_date
from fnd_sessions
where SESSION_ID = userenv('sessionid');
select substr(legislative_parameters,
instr(legislative_parameters, 'NOTES=') + (length('NOTES=')))
into l_notes
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select action_information1
from pay_action_information pai_person
where pai_person.action_context_id = p_assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address';
select action_information6,
action_information7
from pay_action_information pai_comp
where pai_comp.action_context_id = p_assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO';
select action_information6,
action_information8
from pay_action_information pai_gb
where pai_gb.action_context_id = c_assignment_action_id
and pai_gb.action_context_type = 'AAP'
and pai_gb.action_information_category = 'GB EMPLOYEE DETAILS';
select action_information1,
action_information4,
action_information10
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_information_category = 'EMPLOYEE DETAILS';
select /*+ ORDERED use_nl(ppa, paa, pai_comp, pai_person, pai_car)
use_index(pai_comp,pay_action_information_n2)
use_index(pai_person,pay_action_information_n2)
use_index(pai_car,pay_action_information_n2) */
pai_car.action_context_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai_comp,
pay_action_information pai_person,
pay_action_information pai_car
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name
and pai_car.action_context_id = paa.assignment_action_id
and pai_car.action_information_category = 'LIVING ACCOMMODATION'
and pai_car.action_context_type = 'AAP'
group by pai_car.action_context_id;
select pai_ben.ACTION_INFORMATION5 address,
decode(months_between(
fnd_date.canonical_to_date(pai_ben.action_information4)+1,
fnd_date.canonical_to_date(pai_ben.action_information3)),12,'Y','N') full_year,
to_char(to_number(nvl(pai_ben.action_information6,0)),'FM999,999,990.00') rent_employer,
to_char(to_number(nvl(pai_ben.action_information7,0)),'FM999,999,990.00') annual_value,
to_char(to_number(nvl(pai_ben.action_information18,0)),'FM999,999,990.00')Basic_Charge_Cost,
to_char(to_number(nvl(pai_ben.action_information9,0)),'FM999,999,990.00') amg,
to_char(to_number(nvl(pai_ben.action_information19,0)),'FM999,999,990.00')Basic_Charge,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information8,0)),'FM999,999,990.00'),null)gross_amount,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information13,0)),'FM999,999,990.00'),null) emp_share_of_cost,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information20,0)),'FM999,999,990.00'),null) cost_of_acco,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information22,0)),'FM999,999,990.00'),null) excess_of_cost,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information14,0)),'FM999,999,990.00'),null) INTEREST_VALUE,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information21,0)),'FM999,999,990.00'),null) INTEREST_AMOUNT,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information16,0)),'FM999,999,990.00'),null) rent_employee,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information9,0)),'FM999,999,990.00'),null) RENT_IN_AMG,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information17,0)),'FM999,999,990.00'),null) ADDITIONAL_CHARGE,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information19,0)),'FM999,999,990.00'),null) BASIC_CHARGE_2,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information16,0) - nvl(pai_ben.action_information9,0)),'FM999,999,990.00'),null) ACTUAL_RENT,
decode(sign(pai_ben.action_information22),1,to_char(to_number(
nvl(pai_ben.action_information19,0)+nvl(pai_ben.action_information17,0)),'FM999,999,990.00'),null) TOTAL ,
decode(sign(pai_ben.action_information22),
1,decode(months_between(
fnd_date.canonical_to_date(pai_ben.action_information4)+1,
fnd_date.canonical_to_date(pai_ben.action_information3)),12,null,
to_char(to_number(nvl(pai_ben.action_information15,0)))),null) NUMBER_OF_DAYS
from pay_action_information pai_ben
where pai_ben.action_information_category = 'LIVING ACCOMMODATION'
and pai_ben.action_context_id = p_context_id
and pai_ben.action_context_type = 'AAP';
select *
from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
l_select varchar2(500);
select pay.effective_date
from pay_payroll_actions pay
where pay.payroll_action_id = c_payroll_action_id;
l_select := 'select asg_id
from (select asg_id, rownum as row_num
from (select /*+ ORDERED use_nl(paa,paf,emp,pai_payroll)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
paf.person_id, max(paa.assignment_action_id) as asg_id ';
l_where := l_where || ' and emp.action_information2 in(select organization_id_child
from per_org_structure_elements
where business_group_id = ' || l_org_hierarchy ||
' union
select ' || l_org_hierarchy || ' from dual)';
l_select := l_select || ',emp.action_information1 ';
l_select := l_select || ',emp.action_information10 ';
l_select := l_select || ',emp.action_information1 ';
l_select := l_select || ',emp.action_information10 ';
l_select := l_select || ',emp.action_information1 ';
l_sql := l_select || l_from || l_where || l_group || l_order;
select to_number(i.value)
from pay_user_tables t,
pay_user_rows_f r,
pay_user_columns c,
pay_user_column_instances_f i
where t.user_table_name = 'GB_CC_SCALE'
and t.user_table_id = r.user_table_id
and t.user_table_id = c.user_table_id
and c.user_column_name = 'BEFORE_JAN_1_1998'
and i.user_row_id = r.user_row_id
and i.user_column_id = c.user_column_id
and p_size between to_number(r.row_low_range_or_name) and to_number(r.row_high_range)
and p_date between r.effective_start_date and r.effective_end_date
and p_date between i.effective_start_date and i.effective_end_date;
select /*+ ORDERED use_nl(ppa, paa, pai_comp, pai_person, pai_car)
use_index(pai_comp,pay_action_information_n2)
use_index(pai_person,pay_action_information_n2)
use_index(pai_car,pay_action_information_n2) */
pai_car.action_context_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai_comp,
pay_action_information pai_person,
pay_action_information pai_car
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name
and pai_car.action_context_id = paa.assignment_action_id
and pai_car.action_information_category = p_category
and pai_car.action_context_type = 'AAP'
group by pai_car.action_context_id;
select pai_emp.action_information3 ben_start,
pai_emp.action_information4 ben_end,
decode(months_between(
fnd_date.canonical_to_date(pai_emp.action_information4)+1,
fnd_date.canonical_to_date(pai_emp.action_information3)),12,'Y','N') FULL_YR_FLAG,
-- pai_emp.ACTION_INFORMATION5,
pai_emp.action_information6 make,
pai_emp.action_information7 model,
pai_emp.action_information8 date_registered,
substr(pai_emp.action_information8,9,2) || '/' ||
substr(pai_emp.action_information8,6,2) || '/' ||
substr(pai_emp.action_information8,1,4) f_dreg,
pai_emp.action_information13 CO2,
substr(pai_emp.action_information3,9,2) || '/' ||
substr(pai_emp.action_information3,6,2) || '/' ||
substr(pai_emp.action_information3,1,4) f_start,
substr(pai_emp.action_information4,9,2) || '/' ||
substr(pai_emp.action_information4,6,2) || '/' ||
substr(pai_emp.action_information4,1,4) f_end,
to_char(to_number(nvl(pai_emp.action_information9,0)),'FM999,999,990.00') f_lprice,
to_char(to_number(nvl(pai_emp.action_information10,0)),'FM999,999,990.00') f_cc,
to_char(to_number(nvl(pai_emp.action_information11,0)),'FM999,999,990.00') f_fcc,
decode( pai_emp.action_information12,'0',null,PAY_GB_P11D_MAGTAPE.get_description(
pai_emp.action_information12,'GB_FUEL_TYPE',pai_emp.action_information4)) f_fuel,
-- DECODE(pai_emp.action_information13, NULL, 'On', 'Off') f_nfig,
to_char(to_number(nvl(pai_emp.action_information16,0)),'FM999,999,990.00') f_oprice,
to_char(to_number(nvl(pai_emp.action_information9,0) +nvl(pai_emp.action_information16,0)),
'FM999,999,990.00') TOTAL_INIT_PRICE,
to_char(to_number(nvl(pai_emp.action_information17,0)),'FM999,999,990.00') f_aprice,
to_char(to_number(nvl(pai_emp.action_information9,0) + nvl(pai_emp.action_information16,0) +
nvl(pai_emp.action_information17,0)),'FM999,999,990.00') TOTAL_PRICE,
to_char(to_number(nvl(pai_emp.action_information18,0)),'FM999,999,990.00') f_cost,
to_char(to_number(nvl(pai_emp.action_information9,0) + nvl(pai_emp.action_information16,0) +
nvl(pai_emp.action_information17,0) + nvl(pai_emp.action_information18,0)),
'FM999,999,990.00') FINAL_PRICE,
to_char(to_number(nvl(pai_emp.action_information19,0)),'FM999,999,990.00') f_amg,
pai_emp.action_information20 f_esize,
to_char(to_number(nvl(pai_emp.action_information21,0)),'FM999,999,990.00') benefit_charge,
to_char(to_number(nvl(pai_emp.action_information22,0)),'FM999,999,990') unavailable,
to_char(to_number(nvl(pai_emp.action_information23,0)),'FM999,999,990.00') unavailable_value,
to_char(to_number(nvl(pai_emp.action_information21,0) - nvl(pai_emp.action_information26,0) -
nvl(pai_emp.action_information27,0) - nvl(pai_emp.action_information23,0)),
'FM999,999,990.00') BENEFIT_AFTER_UNAVAIL,
nvl(pai_emp.action_information24,'N') FUEL_BENEFIT,
to_char(to_number(nvl(pai_emp.action_information25,0)),'FM999,999,990') BENEFIT_PERCENT,
to_char(to_number(nvl(pai_emp.action_information26,0)),'FM999,999,990.00') STD_DISC,
to_char(to_number(decode(pai_emp.action_information27,0,null,pai_emp.action_information27))
,'FM999,999,990.00') ADD_DISC,
to_char(to_number(decode(nvl(pai_emp.action_information27,0) +
nvl(pai_emp.action_information26,0),0,null, nvl(pai_emp.action_information27,0) +
nvl(pai_emp.action_information26,0))),'FM999,999,990.00') FULL_DISC,
to_char(to_number(pai_emp.action_information28),'FM999,999,990.00') FUEL_SCALE,
nvl(pai_emp.action_information21,0) ben_charge,
nvl(pai_emp.action_information26,0) stand_disc,
nvl(pai_emp.action_information27,0) additional_disc
from pay_action_information pai_emp
where pai_emp.action_information_category = 'CAR AND CAR FUEL'
and pai_emp.action_context_id = p_context_id
and pai_emp.action_context_type = 'AAP';
select pai_emp.action_information3 ben_start,
pai_emp.action_information4 ben_end,
decode(
months_between(
fnd_date.canonical_to_date(pai_emp.action_information4)+1,
fnd_date.canonical_to_date(pai_emp.action_information3)),12,'Y','N') FULL_YR_FLAG,
--pai_emp.ACTION_INFORMATION5 co2,
pai_emp.action_information6 make,
pai_emp.action_information7 model,
pai_emp.action_information8 date_registered,
substr(pai_emp.action_information8,9,2) || ' ' ||
substr(pai_emp.action_information8,6,2) || ' ' ||
substr(pai_emp.action_information8,1,4) f_dreg,
pai_emp.action_information13 co2,
substr(pai_emp.action_information3,9,2) || ' ' ||
substr(pai_emp.action_information3,6,2) || ' ' ||
substr(pai_emp.action_information3,1,4) f_start,
substr(pai_emp.action_information4,9,2) || ' ' ||
substr(pai_emp.action_information4,6,2) || ' ' ||
substr(pai_emp.action_information4,1,4) f_end,
to_char(to_number(nvl(pai_emp.action_information9,0)),'FM999,999,990.00') f_lprice,
to_char(to_number(nvl(pai_emp.action_information10,0)),'FM999,999,990.00') f_cc,
to_char(to_number(nvl(pai_emp.action_information11,0)),'FM999,999,990.00') f_fcc,
decode(pai_emp.action_information12,'0',null,PAY_GB_P11D_MAGTAPE.get_description(
pai_emp.action_information12,'GB_FUEL_TYPE',pai_emp.action_information4)) f_fuel,
to_char(to_number(nvl(pai_emp.action_information15,0)),'FM999,999,990.00') f_oprice,
to_char(to_number(nvl(pai_emp.action_information9,0) +
nvl(pai_emp.action_information15,0)),'FM999,999,990.00') TOTAL_INIT_PRICE,
to_char(to_number(nvl(pai_emp.action_information16,0)),'FM999,999,990.00') f_cost,
to_char(least(l_car_max_price,(to_number(nvl(pai_emp.action_information9,0) +
nvl(pai_emp.action_information15,0) -
nvl(pai_emp.action_information16,0)))),'FM999,999,990.00') FINAL_PRICE,
to_char(to_number(nvl(pai_emp.action_information17,0)),'FM999,999,990.00') f_amg,
pai_emp.action_information18 f_esize,
to_char(to_number(nvl(pai_emp.action_information19,0)),'FM999,999,990') car_benefit_year,
to_char(to_number(nvl(pai_emp.action_information20,0)),'FM999,999,990') unavailable,
to_char(to_number(nvl(pai_emp.action_information21,0)),'FM999,999,990') unavailable_value,
to_char(to_number(nvl(pai_emp.action_information19,0) -
nvl(pai_emp.action_information21,0)),'FM999,999,990.00') CAR_BENEFIT_AVAILABLE,
to_char(to_number(nvl(pai_emp.action_information22,0)),'FM999,999,990') BENEFIT_PERCENT,
to_char(to_number(nvl(pai_emp.action_information23,0)),'FM999,999,990') STD_DISC,
to_char(to_number(nvl(pai_emp.action_information24,0)),'FM999,999,990') ROUND_NORMAL_CO2,
-- Added substring function to get fuel benefit value from action_information25 (P11D 07/08 changes)
nvl(substr(pai_emp.action_information25,1,instr(pai_emp.action_information25,':')-1),'N') FUEL_BENEFIT,
to_char(to_number(pai_emp.action_information29),'FM999,999,990') FUEL_BENEFIT_YEAR,
to_char(to_number(nvl(pai_emp.action_information23,0) +
nvl(pai_emp.action_information24,0)),'FM999,999,990') FULL_DISC,
to_char(to_number(nvl(pai_emp.action_information22,0) -
nvl(pai_emp.action_information23,0) -
nvl(pai_emp.action_information24,0)), 'FM999,999,990') TOTAL_BENIFIT,
to_char(to_number(nvl(pai_emp.action_information22,0) -
nvl(pai_emp.action_information23,0)), 'FM999,999,990') TOTAL_BENIFIT_2,
decode (pai_emp.action_information26,null,null,
decode ( pai_emp.action_information27,'Y',null,
substr(pai_emp.action_information26,9,2) || ' ' ||
substr(pai_emp.action_information26,6,2) || ' ' ||
substr(pai_emp.action_information26,1,4))) f_withdraw,
to_char(to_number(nvl(pai_emp.action_information28,0))) additional_days,
decode (pai_emp.action_information26,null,to_char(to_number(nvl(pai_emp.action_information20,0))),
to_char(to_number(nvl(pai_emp.action_information20,0) +
nvl(pai_emp.action_information28,0)) ) ) total_days ,
to_char(to_number(nvl(pai_emp.action_information30,0)),'FM999,999,990') fuel_unavailable,
pai_emp.action_information4 full_end_date
from pay_action_information pai_emp
where pai_emp.action_information_category = 'CAR AND CAR FUEL 2003_04'
and pai_emp.action_context_id = p_context_id
and pai_emp.action_context_type = 'AAP';
select to_number(global_value)
into l_car_max_price
from ff_globals_f
where global_name = 'NI_CAR_MAX_PRICE'
and to_date('05-04-' || l_rep_run,'DD-MM-YYYY') between effective_start_date and effective_end_date;
select action_information30
into l_car_count
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
select action_information30
into l_car_count
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
select action_information30
into l_car_count
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
select *
from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
select decode(ACTION_INFORMATION6, null,'Y','Y','N','N','Y') exclusive_flag,
ACTION_INFORMATION7 dreg,
nvl(ACTION_INFORMATION18,500) standard_charge,
nvl(ACTION_INFORMATION8,0) UNAVAILABLE_1,
nvl(ACTION_INFORMATION9,0) UNAVAILABLE_2,
nvl(ACTION_INFORMATION10,0) UNAVAILABLE_3,
nvl(ACTION_INFORMATION11,0) UNAVAILABLE_4,
nvl(ACTION_INFORMATION12,0) UNAVAILABLE_VAL,
nvl(ACTION_INFORMATION13,0) NUM_SHARE,
nvl(ACTION_INFORMATION14,0) PVT_USE_PAYMENT,
nvl(ACTION_INFORMATION15,0) CASH_EQUIVALENT
from pay_action_information
where action_information_category = 'VANS 2002_03'
and action_context_id in (select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select nvl(action_information5,' ') registration_number,
action_information6 date_registered,
-- count vans --
-- number of worksheet --
nvl(action_information24,0) van_charged, -- A
action_information3 van_from_b, --
action_information4 van_to_b, --
action_information7 van_unavil_b, -- B
action_information16 van_from_c, --
action_information17 van_to_c, --
action_information18 van_unavil_c, -- C
action_information19 van_from_d, --
action_information20 van_to_d, --
action_information21 van_unavil_d, -- D
nvl(action_information8,0) van_tot_day_unavil, -- E
nvl(action_information9,0) van_unavil_value, -- F
nvl(action_information10,0) van_reduce_value, -- G
nvl(action_information11,0) van_sh_pcent_reduc, -- H
nvl(action_information12,0) van_sh_reduction, -- J
action_information13 van_explanation, --
nvl(action_information10,0) -
nvl(action_information12,0) van_reduce_share, -- K
nvl(action_information14,0) van_private_uses, -- L
nvl(action_information15,0) van_benefit_charge, -- M
action_context_id
from pay_action_information
where action_information_category = 'VANS 2005'
and action_context_type = 'AAP'
and action_context_id in (select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select count(*)
from pay_action_information
where action_information_category = 'VANS 2005'
and action_context_type = 'AAP'
and action_context_id in (select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select nvl(action_information5,' ') registration_number,
-- count vans --
-- number of worksheet --
nvl(action_information24,0) van_charged, -- A
action_information3 van_from_b, --
action_information4 van_to_b, --
action_information6 van_unavil_b, -- B
action_information15 van_from_c, --
action_information16 van_to_c, --
action_information17 van_unavil_c, -- C
action_information18 van_from_d, --
action_information19 van_to_d, --
action_information20 van_unavil_d, -- D
nvl(action_information7,0) van_tot_day_unavil, -- E
nvl(action_information8,0) van_unavil_value, -- F
nvl(action_information9,0) van_reduce_value, -- G
nvl(action_information10,0) van_sh_pcent_reduc, -- H
nvl(action_information11,0) van_sh_reduction, -- J
action_information12 van_explanation, --
nvl(action_information9,0) -
nvl(action_information11,0) van_reduce_share, -- K
nvl(action_information13,0) van_private_uses, -- L
nvl(action_information14,0) van_benefit_charge, -- M
nvl(action_information24,0) van_benefit_chare_tax, --P
action_information25 van_fuel_withdrawn, --R date
nvl(action_information26,0) van_days_after_fuel_wd, --R
nvl(action_information27,0) van_total_days_no_fuel, -- S
nvl(action_information28,0) van_reduction, --T
(nvl(action_information24,0)
- nvl(action_information28,0)) van_fuel_charge_reduction, --V
nvl(action_information29,0) van_reduction_sharing, --W
nvl(action_information30,0) van_feul_ben_charge, --X
action_context_id
from pay_action_information
where action_information_category = 'VANS 2007'
and action_context_type = 'AAP'
and action_context_id in (select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select count(*)
from pay_action_information
where action_information_category = 'VANS 2007'
and action_context_type = 'AAP'
and action_context_id in (select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select *
from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
select ACTION_INFORMATION7 Maximum_Amount_Outstanding,
' ' Currency, -- 'GBP'Currency, -- as we currently support just GBP
to_number(nvl(ACTION_INFORMATION6,'0')) Amount_Outstanding_at_5th_Apri,
to_number(nvl(ACTION_INFORMATION16,'0')) Amount_Outstanding_at_Year_End,
ACTION_INFORMATION18 Official_Rate_of_Interest,
ACTION_INFORMATION8 Total_Amount_of_Interest_Paid,
ACTION_INFORMATION11 Cash_Equivalent,
ACTION_INFORMATION19 Annual_Interest_Value,
ACTION_INFORMATION20 Interest_Value,
action_context_id
from pay_action_information
where action_information_category = 'INT FREE AND LOW INT LOANS'
and action_context_type = 'AAP'
and action_context_id in(select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name)
and to_number(nvl(ACTION_INFORMATION11,'0')) > 0;
select to_number(nvl(ACTION_INFORMATION23,'0'))
into l_h_sum_max_amt_outstanding
from pay_action_information pai_emp
where pai_emp.action_context_id = p_assignment_action_id
and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTC';
select to_number(global_value)
into l_loan_threshold
from ff_globals_f
where global_name = 'P11D_LOW_INT_LOAN_THRESHOLD'
and to_date('05-04-' || l_rep_run,'DD-MM-YYYY') between effective_start_date and effective_end_date;
select *
from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
select
to_char(to_number(nvl(pai_ben.action_information9,0)),'FM999,999,990.00') GROSS_AMOUNT,
to_char(to_number(nvl(pai_ben.action_information10,0)),'FM999,999,990.00') COST,
to_char(to_number(nvl(pai_ben.action_information11,0)),'FM999,999,990.00') PAID_BY_EMPLOYEE,
to_char(to_number(nvl(pai_ben.action_information10,0) - nvl(pai_ben.action_information11,0)),'FM999,999,990.00')QUALIFYING_BENEFITS,
to_char(to_number(nvl(pai_ben.action_information13,0)),'FM999,999,990.00') COST_OF_ACCO,
to_char(to_number(nvl(pai_ben.action_information9,0) + nvl(pai_ben.action_information13,0) +
nvl(pai_ben.action_information10,0) - nvl(pai_ben.action_information11,0)),
'FM999,999,990.00') TOTAL,
to_char(to_number(nvl(pai_ben.action_information14,0)),'FM999,999,990.00') EARLIER_YEARS,
to_char(to_number(nvl(pai_ben.action_information15,0)),'FM999,999,990.00') AMOUNT_EXEMPTED,
to_char(to_number(nvl(pai_ben.action_information5,0)),'FM999,999,990.00') FINAL_AMOUNT,
action_context_id
from pay_action_information pai_ben
where pai_ben.action_information_category = 'RELOCATION EXPENSES'
and pai_ben.action_context_type = 'AAP'
and pai_ben.action_context_id in ( select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_comp.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name);
select nvl(ACTION_INFORMATION12,0) C_MILEAGE_ALLOW_PAYMENTS,
nvl(ACTION_INFORMATION13,0) B_MILEAGE_ALLOW_PAYMENTS,
nvl(ACTION_INFORMATION14,0) M_MILEAGE_ALLOW_PAYMENTS,
nvl(ACTION_INFORMATION16,0) C_TAX_DEDUCTED_PAYMENTS,
nvl(ACTION_INFORMATION17,0) B_TAX_DEDUCTED_PAYMENTS,
nvl(ACTION_INFORMATION18,0) M_TAX_DEDUCTED_PAYMENTS,
(nvl(ACTION_INFORMATION12,0) - nvl(ACTION_INFORMATION16,0)) C_NET_ALLOWANCE,
(nvl(ACTION_INFORMATION13,0) - nvl(ACTION_INFORMATION17,0)) B_NET_ALLOWANCE,
(nvl(ACTION_INFORMATION14,0) - nvl(ACTION_INFORMATION18,0)) M_NET_ALLOWANCE,
nvl(ACTION_INFORMATION1,0) C_BUSINESS_MILES,
nvl(ACTION_INFORMATION2,0) M_BUSINESS_MILES,
nvl(ACTION_INFORMATION3,0) B_BUSINESS_MILES,
nvl(ACTION_INFORMATION4,0) c_reimbursement_rate1,
nvl(ACTION_INFORMATION6,0) m_reimbursement_rate1,
nvl(ACTION_INFORMATION8,0) b_reimbursement_rate1,
nvl(ACTION_INFORMATION5,0) c_reimbursement_rate2,
nvl(ACTION_INFORMATION7,0) m_reimbursement_rate2,
nvl(ACTION_INFORMATION9,0) b_reimbursement_rate2,
nvl(ACTION_INFORMATION19,0) PASSEN_PAYMENTS,
nvl(ACTION_INFORMATION20,0) PASSEN_BUSINESS_MILES,
nvl(ACTION_INFORMATION21,0) PASSENGER_BUS_MILES_AMOUNT
from pay_action_information
where action_information_category = 'GB P11D ASSIGNMENT RESULTC'
and action_context_type = 'AAP'
and action_context_id = p_assignment_action_id;/*Removed sub query for assignment action id*/
select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2)
use_index(pai_a,pay_action_information_n2)*/
pai_a.action_information12
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_a,
pay_action_information pai_person
where paa.assignment_action_id = p_assignment_action_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = pai.action_information_category
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_context_type = 'AAP'
and upper(pai_person.action_information13) = upper(c_emp_ref)
and pai_a.action_context_id = paa.assignment_action_id
and pai_a.action_context_type = 'AAP'
and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and pai.action_information_category = 'MILEAGE ALLOWANCE AND PPAYMENT';
select pai_a.action_information12 --Modified for the bug 9799823
from pay_action_information pai,
pay_assignment_actions paa,
pay_action_information pai_a,
per_all_assignments_f paaf
where paa.payroll_action_id = c_pact_id
and pai.action_context_id = paa.assignment_action_id
and paa.assignment_action_id <> c_asg_act_id
and paaf.person_id = c_person_id
and paaf.assignment_id = paa.assignment_id
and pai.action_context_type = 'AAP'
and pai_a.action_context_id = paa.assignment_action_id
and pai_a.action_context_type = 'AAP'
and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and pai.action_information_category = 'MILEAGE ALLOWANCE AND PPAYMENT';
select *
from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
select count(1)
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id
and action_status='C';
select decode(action_information_category,
'ASSETS TRANSFERRED','A',
'PAYMENTS MADE FOR EMP','B',
'VOUCHERS OR CREDIT CARDS','C',
'PVT MED TREATMENT OR INSURANCE','I',
'SERVICES SUPPLIED','K',
'ASSETS AT EMP DISPOSAL','L',
'P11D SHARES','M',
'OTHER ITEMS','N',
'OTHER ITEMS NON 1A','N',
'EXPENSES PAYMENTS','0') SECTION_TITLE,
decode(action_information_category,
'ASSETS TRANSFERRED','Assets Transferred',
'PAYMENTS MADE FOR EMP','Payments made on behalf of employee',
'VOUCHERS OR CREDIT CARDS','Vouchers or credit cards',
'PVT MED TREATMENT OR INSURANCE','Private medical treatment or insurance',
'SERVICES SUPPLIED','Services Supplied',
'ASSETS AT EMP DISPOSAL','Assets placed at employee''s disposal',
'P11D SHARES','Shares',
'OTHER ITEMS','Other Items',
'OTHER ITEMS NON 1A','Other Items Non 1A',
'EXPENSES PAYMENTS','Expenses') SECTION_HEADING,
decode(action_information_category,
'ASSETS TRANSFERRED',get_lookup_meaning(
'GB_ASSET_TYPE',ACTION_INFORMATION6,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION5,
'PAYMENTS MADE FOR EMP',get_lookup_meaning(
'GB_PAYMENTS_MADE',ACTION_INFORMATION6,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION5,
'VOUCHERS OR CREDIT CARDS',ACTION_INFORMATION5,
'PVT MED TREATMENT OR INSURANCE',ACTION_INFORMATION10,
'SERVICES SUPPLIED',ACTION_INFORMATION10,
'ASSETS AT EMP DISPOSAL',get_lookup_meaning(
'GB_ASSETS',ACTION_INFORMATION5,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION6,
'P11D SHARES','Share Related Benefits',
'OTHER ITEMS',replace(get_lookup_meaning(
'GB_OTHER_ITEMS',ACTION_INFORMATION5,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION6,'&','&'),
and action_context_id in ( select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name)
order by decode(action_information_category,
'ASSETS TRANSFERRED','A',
'PAYMENTS MADE FOR EMP','B',
'VOUCHERS OR CREDIT CARDS','C',
'PVT MED TREATMENT OR INSURANCE','I',
'SERVICES SUPPLIED','K',
'ASSETS AT EMP DISPOSAL','L',
'P11D SHARES','M',
'OTHER ITEMS','N',
'OTHER ITEMS NON 1A','N',
'EXPENSES PAYMENTS','O') asc,
decode(action_information_category,
'ASSETS TRANSFERRED','Assets Transferred',
'PAYMENTS MADE FOR EMP','Payments made on behalf of employee',
'VOUCHERS OR CREDIT CARDS','Vouchers or credit cards',
'PVT MED TREATMENT OR INSURANCE','Private medical treatment or insurance',
'SERVICES SUPPLIED','Services Supplied',
'ASSETS AT EMP DISPOSAL','Assets placed at employee''s disposal',
'P11D SHARES','Shares',
'OTHER ITEMS','Other Items',
'OTHER ITEMS NON 1A','Other Items Non 1A',
'EXPENSES PAYMENTS','Expenses') asc ;
select decode(action_information_category,
'ASSETS TRANSFERRED','A',
'PAYMENTS MADE FOR EMP','B',
'VOUCHERS OR CREDIT CARDS','C',
'PVT MED TREATMENT OR INSURANCE','I',
'SERVICES SUPPLIED','K',
'ASSETS AT EMP DISPOSAL','L',
'OTHER ITEMS','M',
'OTHER ITEMS NON 1A','M',
'EXPENSES PAYMENTS','N') SECTION_TITLE,
decode(action_information_category,
'ASSETS TRANSFERRED','Assets Transferred',
'PAYMENTS MADE FOR EMP','Payments made on behalf of employee',
'VOUCHERS OR CREDIT CARDS','Vouchers or credit cards',
'PVT MED TREATMENT OR INSURANCE','Private medical treatment or insurance',
'SERVICES SUPPLIED','Services Supplied',
'ASSETS AT EMP DISPOSAL','Assets placed at employee''s disposal',
'OTHER ITEMS','Other Items',
'OTHER ITEMS NON 1A','Other Items Non 1A',
'EXPENSES PAYMENTS','Expenses') SECTION_HEADING,
decode(action_information_category,
'ASSETS TRANSFERRED',get_lookup_meaning(
'GB_ASSET_TYPE', ACTION_INFORMATION6,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION5,
'PAYMENTS MADE FOR EMP',get_lookup_meaning(
'GB_PAYMENTS_MADE', ACTION_INFORMATION6,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION5,
'VOUCHERS OR CREDIT CARDS',ACTION_INFORMATION5,
'PVT MED TREATMENT OR INSURANCE',ACTION_INFORMATION10,
'SERVICES SUPPLIED',ACTION_INFORMATION10,
'ASSETS AT EMP DISPOSAL',get_lookup_meaning(
'GB_ASSETS', ACTION_INFORMATION5,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION6,
'OTHER ITEMS',replace(get_lookup_meaning(
'GB_OTHER_ITEMS',ACTION_INFORMATION5,
fnd_date.canonical_to_date(ACTION_INFORMATION4))
|| ' ' || ACTION_INFORMATION6,'&','&'),
and action_context_id in ( select paa.assignment_action_id
from pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and paa.payroll_action_id = ppa.payroll_action_id
and pai_comp.action_context_id = paa.assignment_action_id
and pai_comp.action_information_category = 'EMEA PAYROLL INFO'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_information1 = to_char(p_person_id)
and pai_comp.action_information6 = p_emp_ref
and pai_comp.action_information7 = p_emp_name)
order by
decode(action_information_category,
'ASSETS TRANSFERRED','A',
'PAYMENTS MADE FOR EMP','B',
'VOUCHERS OR CREDIT CARDS','C',
'PVT MED TREATMENT OR INSURANCE','I',
'SERVICES SUPPLIED','K',
'ASSETS AT EMP DISPOSAL','L',
'OTHER ITEMS','M',
'OTHER ITEMS NON 1A','M',
'EXPENSES PAYMENTS','N') asc,
decode(action_information_category,
'ASSETS TRANSFERRED','Assets Transferred',
'PAYMENTS MADE FOR EMP','Payments made on behalf of employee',
'VOUCHERS OR CREDIT CARDS','Vouchers or credit cards',
'PVT MED TREATMENT OR INSURANCE','Private medical treatment or insurance',
'SERVICES SUPPLIED','Services Supplied',
'ASSETS AT EMP DISPOSAL','Assets placed at employee''s disposal',
'OTHER ITEMS','Other Items',
'OTHER ITEMS NON 1A','Other Items Non 1A',
'EXPENSES PAYMENTS','Expenses') asc ;
select address_line1,
address_line2,
address_line3,
town_or_city,
postal_code,
region_1
from per_addresses
where person_id = (
select distinct person_id
from pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_action_id = p_assignment_action_id
and paf.assignment_id = paa.assignment_id)
and primary_flag = 'Y'
and sysdate between date_from and nvl(date_to, hr_general.end_of_time);
select emp.action_information1 emp_name
from pay_action_information emp
where emp.action_information_category = 'EMPLOYEE DETAILS'
and emp.action_context_id = p_assignment_action_id
and emp.action_context_type = 'AAP';
select emp.action_information1 emp_name
,adr.action_information5 adr_adress1
,adr.action_information6 adr_adress2
,adr.action_information7 adr_adress3
,adr.action_information8 adr_town
,adr.action_information12 adr_county
,adr.action_information9 adr_code
from pay_assignment_actions paa
,pay_action_information emp -- Employee Details
,pay_action_information adr -- Address Details
,pay_action_information prl -- EMEA Payroll Info
where paa.assignment_action_id = p_assignment_action_id
and emp.action_information_category = 'EMPLOYEE DETAILS'
and emp.action_context_id = paa.assignment_action_id
and emp.action_context_type = 'AAP'
--
and prl.action_information_category = 'EMEA PAYROLL INFO'
and prl.action_context_id = paa.assignment_action_id
and prl.action_context_type = 'AAP'
--
and adr.action_information_category = 'ADDRESS DETAILS'
and adr.action_context_id = paa.assignment_action_id
and adr.action_context_type = 'AAP'
and adr.action_information14 = 'Employee Address';
select hlu.meaning hlu_meaning
from hr_lookups hlu
where hlu.lookup_type='GB_COUNTY'
and hlu.lookup_code=p_code
and hlu.enabled_flag='Y';
select meaning
from hr_lookups
where lookup_type='GB_P11D_PRI_MARKINGS'
and lookup_code=p_priv_mark
and enabled_flag='Y';
SELECT /*+ ordered */
paa.assignment_action_id,
paf.ASSIGNMENT_TYPE
-- Added paf.ASSIGNMENT_TYPE as we need to write primary assign number in
-- magtape if the primary and sec both have the p11d benefits
-- In case if only secondary have benefits then we may write
-- any secondary assignment number in the magtape
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai_comp
WHERE paf.person_id = p_person_id
AND paa.assignment_id = paf.assignment_id
AND paa.payroll_action_id = p_arch_payroll_action_id
AND pai_comp.action_context_id = paa.assignment_action_id
AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
AND pai_comp.action_information6 = p_emp_ref_no
order by paf.ASSIGNMENT_TYPE, paa.assignment_action_id;
SELECT /*+ ordered */
SUM (TO_NUMBER (NVL (pai.action_information7, '0')))
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai_comp,
pay_action_information pai
WHERE paf.person_id = p_person_id
AND paa.payroll_action_id = p_arch_payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND pai_comp.action_context_id = paa.assignment_action_id
AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
AND pai_comp.action_information6 = p_emp_ref_no
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'MARORS'
AND pai.action_context_id = pai_comp.action_context_id;
SELECT *
FROM pay_action_information pai
WHERE pai.action_context_id = p_assignment_action_id
AND pai.action_information_category = p_action_info_catg;