[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED use_nl(paf,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_information9,0))) cash_equivalent,
sum(to_number(nvl(pai.action_information7,0))) cost_or_mkt_value,
sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
upper(max(pai.action_information5)) asset_description,
pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_ASSET_TYPE',
pai.action_information4) asset_type
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ASSETS TRANSFERRED'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP'
group by pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_ASSET_TYPE',
pai.action_information4);
select /*+ ORDERED use_nl(paf,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))) cash_equivalent,
sum(to_number(nvl(pai.action_information8,0))) tax_on_notional_payments,
--UPPER(pai.action_information5) payment_description,
pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
pai.action_information4) payment_type
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'PAYMENTS MADE FOR EMP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP'
group by pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
pai.action_information4);
select /*+ ORDERED use_nl(paf,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_information11,0))) cash_equivalent,
sum(to_number(nvl(pai.action_information6,0))) gross_amount,
sum(to_number(nvl(pai.action_information7,0))) amount_m_good
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'VOUCHERS OR CREDIT CARDS'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,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_information10,0))) cash_equivalent
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'LIVING ACCOMMODATION'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
to_number(nvl(pai.action_information12,0)) cash_equivalent --Modified for the bug 11727875
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select *
from (
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
rownum as row_num,
pai.action_information3 benefit_start_date,
pai.action_information4 benefit_end_date,
upper(pai.action_information6) make_of_car,
upper(pai.action_information7) model,
pai.action_information8 date_first_registered,
pai.action_information9 list_price,
pai.action_information10 cash_equivalent_for_car,
pai.action_information11 cash_equivalent_for_fuel,
upper(pai.action_information12) fuel_type,
pai.action_information13 co2_emission,
pai.action_information15 optional_accessories,
pai.action_information16 capital_contribution,
pai.action_information17 private_use_payments,
pai.action_information18 engine_cc,
pai.action_information25 valid_benefit_end_date_flag, -- EOY 2008
pai.action_information26 date_free_fuel_withdrawn,
pai.action_information27 free_fuel_reinstated
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'CAR AND CAR FUEL 2003_04'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP')
where row_num = p_benefit_number;
select decode(l_fuel_type,
'BATTERY_ELECTRIC','E',
'DIESEL','D',
'EURO_IV_DIESEL','L',
'HYBRID_ELECTRIC','H',
'LPG_CNG','B',
'LPG_CNG_PETROL','B',
'LPG_CNG_PETROL_CONV','C',
'PETROL','P',
'E85','G',
'D')
into l_fuel_type
from dual;
select /*+ ORDERED use_nl(paf,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_information14,0))) cash_equivalent -- EOY 2008
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'VANS 2007' -- EOY 2008
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,paa,pai)
use_index(pai,pay_action_information_n2) */
sum (pai.action_information30) vans_fuel
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTB' ;
select /*+ ORDERED use_nl(paf,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 per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_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'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select *
from (
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
rownum as row_num,
to_number(nvl(pai.action_information5,1)) number_of_borrower,
to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
to_number(nvl(pai.action_information8,0)) total_interest_paid,
pai.action_information9 date_loan_made,
pai.action_information10 date_loan_discharged,
to_number(nvl(pai.action_information11,1))cash_equivalent,
to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_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'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP')
where row_num = p_benefit_number;
select /*+ ORDERED use_nl(paf,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))) cash_equivalent,
sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
sum(to_number(nvl(pai.action_information6,0))) amount_m_good
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'PVT MED TREATMENT OR INSURANCE'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,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_information5,0))) cash_equivalent
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'RELOCATION EXPENSES'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,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))) cash_equivalent,
sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
sum(to_number(nvl(pai.action_information6,0))) amount_m_good
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'SERVICES SUPPLIED'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,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))) annual_value,
sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
pai.action_information4) asset_type
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ASSETS AT EMP DISPOSAL'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP'
group by pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
pai.action_information4);
select /*+ ORDERED use_nl(paf,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_information9,0))) cash_equivalent,
sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
pay_gb_p11d_magtape.get_description(pai.action_information5,
p_lookup, pai.action_information4) description
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = p_category
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP'
group by pay_gb_p11d_magtape.get_description(pai.action_information5,
p_lookup, pai.action_information4);
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
to_number(pai.action_information6) cost_to_you,
to_number(pai.action_information7) amount_m_good,
to_number(pai.action_information8) cash_equivalent,
pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
pai.action_information4) expense_type,
nvl(pai.action_information10,'N') trading_indicator,
-- converted to upper for bug# 12715628
upper(pai.action_information9) description --Added for the bug# 8708099
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'EXPENSES PAYMENTS'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,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))) mileage_allowance
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'MARORS'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_emp_ref)
and pai_person.action_context_type = 'AAP';
select action_context_id
from pay_assignment_actions paa,
pay_action_information pai
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ADDRESS DETAILS'
and pai.action_information14 = 'Employee Address'
and pai.action_information1 = p_person_id
and pai.action_context_type = 'AAP';
select NVL(SUBSTR(UPPER(pai_gb.action_information8), 1, 36), ' '), -- last name
NVL(SUBSTR(UPPER(pai_gb.action_information6), 1, 36), ' '), -- first name
NVL(SUBSTR(UPPER(pai_gb.action_information7), 1, 36), ' '), -- middle name
NVL(UPPER(pai_gb.action_information4), 'N'), -- dir flag
NVL(UPPER(pai_gb.action_information11), ' '), -- emp no
NVL(UPPER(pai_gb.action_information12), 'NONE'), -- NI
NVL(UPPER(pai_person.action_information5), ' '), -- addr line 1
NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
NVL(UPPER(hl.meaning), ' '), -- addr line 5
NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
from pay_action_information pai_gb,
pay_action_information pai_person,
hr_lookups hl
where pai_person.action_context_id = p_act_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_context_type = 'AAP'
and pai_gb.action_context_id = pai_person.action_context_id
and pai_gb.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_gb.action_context_type = 'AAP'
and hl.lookup_type(+) = 'GB_COUNTY'
and hl.lookup_code(+) = pai_person.action_information9;
select decode(p_benefit_type,
'A', 'ASSETS TRANSFERRED',
'B', 'PAYMENTS MADE FOR EMP',
'C', 'VOUCHERS OR CREDIT CARDS',
'D', 'LIVING ACCOMMODATION',
'E', 'MILEAGE ALLOWANCE AND PPAYMENT',
'F', 'CAR AND CAR FUEL 2003_04',
'G', 'VANS 2007', -- EOY 2008
'H', 'INT FREE AND LOW INT LOANS',
'I', 'PVT MED TREATMENT OR INSURANCE',
'J', 'RELOCATION EXPENSES',
'K', 'SERVICES SUPPLIED',
'L', 'ASSETS AT EMP DISPOSAL',
'M', 'OTHER ITEMS', -- 'OTHER ITEMS NON 1A'
'N', 'EXPENSES PAYMENTS',
'U', 'MARORS')
into l_benefit_name
from dual;
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
count(*)
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paf.person_id = p_person_id
and paf.effective_end_date = (select max(paf2.effective_end_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.person_id = p_person_id)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = p_benefit_type
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and upper(pai_person.action_information13) = upper(p_employer_ref)
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
sum(pai.action_information7)
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paa.assignment_action_id = p_assact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'MARORS'
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(p_employer_ref);
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)*/
/* sum(decode(pai.action_information_category,
'ASSETS TRANSFERRED', pai.action_information9,
'PAYMENTS MADE FOR EMP', pai.action_information7,
'VOUCHERS OR CREDIT CARDS', pai.action_information11,
'LIVING ACCOMMODATION', pai.action_information10 + pai.action_information17,
'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
'CAR AND CAR FUEL 2003_04', pai.action_information10 + pai.action_information11,
'VANS 2002_03',pai.action_information15,
'VANS 2005', pai.action_information15,
'VANS 2007', pai.action_information14, -- EOY 2008
'INT FREE AND LOW INT LOANS', pai.action_information11,
'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
'RELOCATION EXPENSES', pai.action_information5,
'SERVICES SUPPLIED', pai.action_information7,
'ASSETS AT EMP DISPOSAL', pai.action_information9,
'OTHER ITEMS', pai.action_information9,
'OTHER ITEMS NON 1A', pai.action_information9,
'EXPENSES PAYMENTS', pai.action_information8)) total
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_assact_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(p_employer_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'; */
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)*/
decode(pai.action_information_category,
'ASSETS TRANSFERRED', pai.action_information9,
'PAYMENTS MADE FOR EMP', pai.action_information7,
'VOUCHERS OR CREDIT CARDS', pai.action_information11,
'LIVING ACCOMMODATION', pai.action_information10 + pai.action_information17,
'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
'CAR AND CAR FUEL 2003_04', pai.action_information10 + pai.action_information11,
'VANS 2002_03',pai.action_information15,
'VANS 2005', pai.action_information15,
'VANS 2007', pai.action_information14, -- EOY 2008
'INT FREE AND LOW INT LOANS',
decode(sign(pai.action_information11 - 1), 0, 0, -1, 0, 1,
decode(sign(pai_c.action_information23 - 5000),0,0,-1,0,1,1)),
'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
'RELOCATION EXPENSES', pai.action_information5,
'SERVICES SUPPLIED', pai.action_information7,
'ASSETS AT EMP DISPOSAL', pai.action_information9,
'OTHER ITEMS', pai.action_information9,
'OTHER ITEMS NON 1A', pai.action_information9,
'EXPENSES PAYMENTS', pai.action_information8)total
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_a,
pay_action_information pai_c,
pay_action_information pai_person
where paa.assignment_action_id = p_assact_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(p_employer_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_c.action_context_id = paa.assignment_action_id
and pai_c.action_context_type = 'AAP'
and pai_c.action_information_category = 'GB P11D ASSIGNMENT RESULTC';