[Home] [Help]
59: edi_tax_qualifier115 varchar2(3);
60:
61: cursor get_data is
62: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
63: use_index(pai_person,pay_action_information_n2)
64: use_index(pai,pay_action_information_n2) */
65: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
66: sum(to_number(nvl(pai.action_information7,0))) cost_or_mkt_value,
67: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
60:
61: cursor get_data is
62: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
63: use_index(pai_person,pay_action_information_n2)
64: use_index(pai,pay_action_information_n2) */
65: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
66: sum(to_number(nvl(pai.action_information7,0))) cost_or_mkt_value,
67: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
68: upper(max(pai.action_information5)) asset_description,
69: pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_ASSET_TYPE',
70: pai.action_information4) asset_type
71: from per_all_assignments_f paf,
72: pay_assignment_actions paa,
73: pay_action_information pai,
74: pay_action_information pai_person
75: where paf.person_id = p_person_id
76: and paf.effective_end_date = (select max(paf2.effective_end_date)
77: from per_all_assignments_f paf2
70: pai.action_information4) asset_type
71: from per_all_assignments_f paf,
72: pay_assignment_actions paa,
73: pay_action_information pai,
74: pay_action_information pai_person
75: where paf.person_id = p_person_id
76: and paf.effective_end_date = (select max(paf2.effective_end_date)
77: from per_all_assignments_f paf2
78: where paf2.assignment_id = paf.assignment_id
222: edi_tax_qualifier117 varchar2(3);
223:
224: cursor get_data is
225: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
226: use_index(pai_person,pay_action_information_n2)
227: use_index(pai,pay_action_information_n2) */
228: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
229: sum(to_number(nvl(pai.action_information8,0))) tax_on_notional_payments,
230: --UPPER(pai.action_information5) payment_description,
223:
224: cursor get_data is
225: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
226: use_index(pai_person,pay_action_information_n2)
227: use_index(pai,pay_action_information_n2) */
228: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
229: sum(to_number(nvl(pai.action_information8,0))) tax_on_notional_payments,
230: --UPPER(pai.action_information5) payment_description,
231: pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
231: pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
232: pai.action_information4) payment_type
233: from per_all_assignments_f paf,
234: pay_assignment_actions paa,
235: pay_action_information pai,
236: pay_action_information pai_person
237: where paf.person_id = p_person_id
238: and paf.effective_end_date = (select max(paf2.effective_end_date)
239: from per_all_assignments_f paf2
232: pai.action_information4) payment_type
233: from per_all_assignments_f paf,
234: pay_assignment_actions paa,
235: pay_action_information pai,
236: pay_action_information pai_person
237: where paf.person_id = p_person_id
238: and paf.effective_end_date = (select max(paf2.effective_end_date)
239: from per_all_assignments_f paf2
240: where paf2.assignment_id = paf.assignment_id
347: edi_tax_qualifier41 varchar2(3);
348:
349: cursor get_data is
350: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
351: use_index(pai_person,pay_action_information_n2)
352: use_index(pai,pay_action_information_n2) */
353: sum(to_number(nvl(pai.action_information11,0))) cash_equivalent,
354: sum(to_number(nvl(pai.action_information6,0))) gross_amount,
355: sum(to_number(nvl(pai.action_information7,0))) amount_m_good
348:
349: cursor get_data is
350: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
351: use_index(pai_person,pay_action_information_n2)
352: use_index(pai,pay_action_information_n2) */
353: sum(to_number(nvl(pai.action_information11,0))) cash_equivalent,
354: sum(to_number(nvl(pai.action_information6,0))) gross_amount,
355: sum(to_number(nvl(pai.action_information7,0))) amount_m_good
356: from per_all_assignments_f paf,
354: sum(to_number(nvl(pai.action_information6,0))) gross_amount,
355: sum(to_number(nvl(pai.action_information7,0))) amount_m_good
356: from per_all_assignments_f paf,
357: pay_assignment_actions paa,
358: pay_action_information pai,
359: pay_action_information pai_person
360: where paf.person_id = p_person_id
361: and paf.effective_end_date = (select max(paf2.effective_end_date)
362: from per_all_assignments_f paf2
355: sum(to_number(nvl(pai.action_information7,0))) amount_m_good
356: from per_all_assignments_f paf,
357: pay_assignment_actions paa,
358: pay_action_information pai,
359: pay_action_information pai_person
360: where paf.person_id = p_person_id
361: and paf.effective_end_date = (select max(paf2.effective_end_date)
362: from per_all_assignments_f paf2
363: where paf2.assignment_id = paf.assignment_id
421: edi_tax_qualifier12 varchar2(3);
422:
423: cursor get_data is
424: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
425: use_index(pai_person,pay_action_information_n2)
426: use_index(pai,pay_action_information_n2) */
427: sum(to_number(nvl(pai.action_information10,0))) cash_equivalent
428: from per_all_assignments_f paf,
429: pay_assignment_actions paa,
422:
423: cursor get_data is
424: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
425: use_index(pai_person,pay_action_information_n2)
426: use_index(pai,pay_action_information_n2) */
427: sum(to_number(nvl(pai.action_information10,0))) cash_equivalent
428: from per_all_assignments_f paf,
429: pay_assignment_actions paa,
430: pay_action_information pai,
426: use_index(pai,pay_action_information_n2) */
427: sum(to_number(nvl(pai.action_information10,0))) cash_equivalent
428: from per_all_assignments_f paf,
429: pay_assignment_actions paa,
430: pay_action_information pai,
431: pay_action_information pai_person
432: where paf.person_id = p_person_id
433: and paf.effective_end_date = (select max(paf2.effective_end_date)
434: from per_all_assignments_f paf2
427: sum(to_number(nvl(pai.action_information10,0))) cash_equivalent
428: from per_all_assignments_f paf,
429: pay_assignment_actions paa,
430: pay_action_information pai,
431: pay_action_information pai_person
432: where paf.person_id = p_person_id
433: and paf.effective_end_date = (select max(paf2.effective_end_date)
434: from per_all_assignments_f paf2
435: where paf2.assignment_id = paf.assignment_id
483: edi_tax_qualifier71 varchar2(3);
484:
485: cursor get_data is
486: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
487: use_index(pai_person,pay_action_information_n2)
488: use_index(pai,pay_action_information_n2) */
489: sum(to_number(nvl(pai.action_information12,0))) cash_equivalent
490: from per_all_assignments_f paf,
491: pay_assignment_actions paa,
484:
485: cursor get_data is
486: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
487: use_index(pai_person,pay_action_information_n2)
488: use_index(pai,pay_action_information_n2) */
489: sum(to_number(nvl(pai.action_information12,0))) cash_equivalent
490: from per_all_assignments_f paf,
491: pay_assignment_actions paa,
492: pay_action_information pai,
488: use_index(pai,pay_action_information_n2) */
489: sum(to_number(nvl(pai.action_information12,0))) cash_equivalent
490: from per_all_assignments_f paf,
491: pay_assignment_actions paa,
492: pay_action_information pai,
493: pay_action_information pai_person
494: where paf.person_id = p_person_id
495: and paf.effective_end_date = (select max(paf2.effective_end_date)
496: from per_all_assignments_f paf2
489: sum(to_number(nvl(pai.action_information12,0))) cash_equivalent
490: from per_all_assignments_f paf,
491: pay_assignment_actions paa,
492: pay_action_information pai,
493: pay_action_information pai_person
494: where paf.person_id = p_person_id
495: and paf.effective_end_date = (select max(paf2.effective_end_date)
496: from per_all_assignments_f paf2
497: where paf2.assignment_id = paf.assignment_id
615: cursor get_data(p_benefit_number number) is
616: select *
617: from (
618: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
619: use_index(pai_person,pay_action_information_n2)
620: use_index(pai,pay_action_information_n2) */
621: rownum as row_num,
622: pai.action_information3 benefit_start_date,
623: pai.action_information4 benefit_end_date,
616: select *
617: from (
618: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
619: use_index(pai_person,pay_action_information_n2)
620: use_index(pai,pay_action_information_n2) */
621: rownum as row_num,
622: pai.action_information3 benefit_start_date,
623: pai.action_information4 benefit_end_date,
624: upper(pai.action_information6) make_of_car,
637: pai.action_information26 date_free_fuel_withdrawn,
638: pai.action_information27 free_fuel_reinstated
639: from per_all_assignments_f paf,
640: pay_assignment_actions paa,
641: pay_action_information pai,
642: pay_action_information pai_person
643: where paf.person_id = p_person_id
644: and paf.effective_end_date = (select max(paf2.effective_end_date)
645: from per_all_assignments_f paf2
638: pai.action_information27 free_fuel_reinstated
639: from per_all_assignments_f paf,
640: pay_assignment_actions paa,
641: pay_action_information pai,
642: pay_action_information pai_person
643: where paf.person_id = p_person_id
644: and paf.effective_end_date = (select max(paf2.effective_end_date)
645: from per_all_assignments_f paf2
646: where paf2.assignment_id = paf.assignment_id
931: edi_tax_qualifier139 varchar2(3);
932:
933: cursor get_data is
934: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
935: use_index(pai_person,pay_action_information_n2)
936: use_index(pai,pay_action_information_n2) */
937: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
938: from per_all_assignments_f paf,
939: pay_assignment_actions paa,
932:
933: cursor get_data is
934: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
935: use_index(pai_person,pay_action_information_n2)
936: use_index(pai,pay_action_information_n2) */
937: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
938: from per_all_assignments_f paf,
939: pay_assignment_actions paa,
940: pay_action_information pai,
936: use_index(pai,pay_action_information_n2) */
937: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
938: from per_all_assignments_f paf,
939: pay_assignment_actions paa,
940: pay_action_information pai,
941: pay_action_information pai_person
942: where paf.person_id = p_person_id
943: and paf.effective_end_date = (select max(paf2.effective_end_date)
944: from per_all_assignments_f paf2
937: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
938: from per_all_assignments_f paf,
939: pay_assignment_actions paa,
940: pay_action_information pai,
941: pay_action_information pai_person
942: where paf.person_id = p_person_id
943: and paf.effective_end_date = (select max(paf2.effective_end_date)
944: from per_all_assignments_f paf2
945: where paf2.assignment_id = paf.assignment_id
956:
957: -- EOY 2008
958: cursor get_data2 is
959: select /*+ ORDERED use_nl(paf,paa,pai)
960: use_index(pai,pay_action_information_n2) */
961: sum (pai.action_information30) vans_fuel
962: from per_all_assignments_f paf,
963: pay_assignment_actions paa,
964: pay_action_information pai
960: use_index(pai,pay_action_information_n2) */
961: sum (pai.action_information30) vans_fuel
962: from per_all_assignments_f paf,
963: pay_assignment_actions paa,
964: pay_action_information pai
965: where paf.person_id = p_person_id
966: and paf.effective_end_date = (select max(paf2.effective_end_date)
967: from per_all_assignments_f paf2
968: where paf2.assignment_id = paf.assignment_id
1064: edi_tax_year_end varchar2(10);
1065:
1066: cursor get_loan_amount is
1067: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1068: use_index(pai_person,pay_action_information_n2)
1069: use_index(pai,pay_action_information_n2) */
1070: sum(to_number(nvl(pai.action_information7,0)))
1071: from per_all_assignments_f paf,
1072: pay_assignment_actions paa,
1065:
1066: cursor get_loan_amount is
1067: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1068: use_index(pai_person,pay_action_information_n2)
1069: use_index(pai,pay_action_information_n2) */
1070: sum(to_number(nvl(pai.action_information7,0)))
1071: from per_all_assignments_f paf,
1072: pay_assignment_actions paa,
1073: pay_action_information pai,
1069: use_index(pai,pay_action_information_n2) */
1070: sum(to_number(nvl(pai.action_information7,0)))
1071: from per_all_assignments_f paf,
1072: pay_assignment_actions paa,
1073: pay_action_information pai,
1074: pay_action_information pai_person
1075: where paf.person_id = p_person_id
1076: and paf.effective_end_date = (select max(paf2.effective_end_date)
1077: from per_all_assignments_f paf2
1070: sum(to_number(nvl(pai.action_information7,0)))
1071: from per_all_assignments_f paf,
1072: pay_assignment_actions paa,
1073: pay_action_information pai,
1074: pay_action_information pai_person
1075: where paf.person_id = p_person_id
1076: and paf.effective_end_date = (select max(paf2.effective_end_date)
1077: from per_all_assignments_f paf2
1078: where paf2.assignment_id = paf.assignment_id
1090: cursor get_data(p_benefit_number number) is
1091: select *
1092: from (
1093: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1094: use_index(pai_person,pay_action_information_n2)
1095: use_index(pai,pay_action_information_n2) */
1096: rownum as row_num,
1097: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1098: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1091: select *
1092: from (
1093: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1094: use_index(pai_person,pay_action_information_n2)
1095: use_index(pai,pay_action_information_n2) */
1096: rownum as row_num,
1097: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1098: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1099: to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
1103: to_number(nvl(pai.action_information11,1))cash_equivalent,
1104: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1105: from per_all_assignments_f paf,
1106: pay_assignment_actions paa,
1107: pay_action_information pai,
1108: pay_action_information pai_person
1109: where paf.person_id = p_person_id
1110: and paf.effective_end_date = (select max(paf2.effective_end_date)
1111: from per_all_assignments_f paf2
1104: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1105: from per_all_assignments_f paf,
1106: pay_assignment_actions paa,
1107: pay_action_information pai,
1108: pay_action_information pai_person
1109: where paf.person_id = p_person_id
1110: and paf.effective_end_date = (select max(paf2.effective_end_date)
1111: from per_all_assignments_f paf2
1112: where paf2.assignment_id = paf.assignment_id
1244: edi_tax_qualifier13 varchar2(3);
1245:
1246: cursor get_data is
1247: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1248: use_index(pai_person,pay_action_information_n2)
1249: use_index(pai,pay_action_information_n2) */
1250: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1251: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1252: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1245:
1246: cursor get_data is
1247: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1248: use_index(pai_person,pay_action_information_n2)
1249: use_index(pai,pay_action_information_n2) */
1250: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1251: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1252: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1253: from per_all_assignments_f paf,
1251: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1252: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1253: from per_all_assignments_f paf,
1254: pay_assignment_actions paa,
1255: pay_action_information pai,
1256: pay_action_information pai_person
1257: where paf.person_id = p_person_id
1258: and paf.effective_end_date = (select max(paf2.effective_end_date)
1259: from per_all_assignments_f paf2
1252: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1253: from per_all_assignments_f paf,
1254: pay_assignment_actions paa,
1255: pay_action_information pai,
1256: pay_action_information pai_person
1257: where paf.person_id = p_person_id
1258: and paf.effective_end_date = (select max(paf2.effective_end_date)
1259: from per_all_assignments_f paf2
1260: where paf2.assignment_id = paf.assignment_id
1322: edi_tax_qualifier64 varchar2(3);
1323:
1324: cursor get_data is
1325: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1326: use_index(pai_person,pay_action_information_n2)
1327: use_index(pai,pay_action_information_n2) */
1328: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1329: from per_all_assignments_f paf,
1330: pay_assignment_actions paa,
1323:
1324: cursor get_data is
1325: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1326: use_index(pai_person,pay_action_information_n2)
1327: use_index(pai,pay_action_information_n2) */
1328: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1329: from per_all_assignments_f paf,
1330: pay_assignment_actions paa,
1331: pay_action_information pai,
1327: use_index(pai,pay_action_information_n2) */
1328: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1329: from per_all_assignments_f paf,
1330: pay_assignment_actions paa,
1331: pay_action_information pai,
1332: pay_action_information pai_person
1333: where paf.person_id = p_person_id
1334: and paf.effective_end_date = (select max(paf2.effective_end_date)
1335: from per_all_assignments_f paf2
1328: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1329: from per_all_assignments_f paf,
1330: pay_assignment_actions paa,
1331: pay_action_information pai,
1332: pay_action_information pai_person
1333: where paf.person_id = p_person_id
1334: and paf.effective_end_date = (select max(paf2.effective_end_date)
1335: from per_all_assignments_f paf2
1336: where paf2.assignment_id = paf.assignment_id
1392: edi_tax_qualifier13 varchar2(3);
1393:
1394: cursor get_data is
1395: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1396: use_index(pai_person,pay_action_information_n2)
1397: use_index(pai,pay_action_information_n2) */
1398: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1399: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1400: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1393:
1394: cursor get_data is
1395: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1396: use_index(pai_person,pay_action_information_n2)
1397: use_index(pai,pay_action_information_n2) */
1398: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1399: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1400: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1401: from per_all_assignments_f paf,
1399: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1400: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1401: from per_all_assignments_f paf,
1402: pay_assignment_actions paa,
1403: pay_action_information pai,
1404: pay_action_information pai_person
1405: where paf.person_id = p_person_id
1406: and paf.effective_end_date = (select max(paf2.effective_end_date)
1407: from per_all_assignments_f paf2
1400: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1401: from per_all_assignments_f paf,
1402: pay_assignment_actions paa,
1403: pay_action_information pai,
1404: pay_action_information pai_person
1405: where paf.person_id = p_person_id
1406: and paf.effective_end_date = (select max(paf2.effective_end_date)
1407: from per_all_assignments_f paf2
1408: where paf2.assignment_id = paf.assignment_id
1501: edi_tax_qualifier116 varchar2(3);
1502:
1503: cursor get_data is
1504: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1505: use_index(pai_person,pay_action_information_n2)
1506: use_index(pai,pay_action_information_n2) */
1507: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1508: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1509: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1502:
1503: cursor get_data is
1504: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1505: use_index(pai_person,pay_action_information_n2)
1506: use_index(pai,pay_action_information_n2) */
1507: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1508: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1509: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1510: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1510: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1511: pai.action_information4) asset_type
1512: from per_all_assignments_f paf,
1513: pay_assignment_actions paa,
1514: pay_action_information pai,
1515: pay_action_information pai_person
1516: where paf.person_id = p_person_id
1517: and paf.effective_end_date = (select max(paf2.effective_end_date)
1518: from per_all_assignments_f paf2
1511: pai.action_information4) asset_type
1512: from per_all_assignments_f paf,
1513: pay_assignment_actions paa,
1514: pay_action_information pai,
1515: pay_action_information pai_person
1516: where paf.person_id = p_person_id
1517: and paf.effective_end_date = (select max(paf2.effective_end_date)
1518: from per_all_assignments_f paf2
1519: where paf2.assignment_id = paf.assignment_id
1670: edi_tax_qualifier120 varchar2(3);
1671:
1672: cursor get_data(p_category varchar2, p_lookup varchar2) is
1673: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1674: use_index(pai_person,pay_action_information_n2)
1675: use_index(pai,pay_action_information_n2) */
1676: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1677: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1678: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1671:
1672: cursor get_data(p_category varchar2, p_lookup varchar2) is
1673: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1674: use_index(pai_person,pay_action_information_n2)
1675: use_index(pai,pay_action_information_n2) */
1676: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1677: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1678: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1679: pay_gb_p11d_magtape.get_description(pai.action_information5,
1679: pay_gb_p11d_magtape.get_description(pai.action_information5,
1680: p_lookup, pai.action_information4) description
1681: from per_all_assignments_f paf,
1682: pay_assignment_actions paa,
1683: pay_action_information pai,
1684: pay_action_information pai_person
1685: where paf.person_id = p_person_id
1686: and paf.effective_end_date = (select max(paf2.effective_end_date)
1687: from per_all_assignments_f paf2
1680: p_lookup, pai.action_information4) description
1681: from per_all_assignments_f paf,
1682: pay_assignment_actions paa,
1683: pay_action_information pai,
1684: pay_action_information pai_person
1685: where paf.person_id = p_person_id
1686: and paf.effective_end_date = (select max(paf2.effective_end_date)
1687: from per_all_assignments_f paf2
1688: where paf2.assignment_id = paf.assignment_id
1903:
1904: cursor get_data
1905: is
1906: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1907: use_index(pai_person,pay_action_information_n2)
1908: use_index(pai,pay_action_information_n2) */
1909: to_number(pai.action_information6) cost_to_you,
1910: to_number(pai.action_information7) amount_m_good,
1911: to_number(pai.action_information8) cash_equivalent,
1904: cursor get_data
1905: is
1906: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1907: use_index(pai_person,pay_action_information_n2)
1908: use_index(pai,pay_action_information_n2) */
1909: to_number(pai.action_information6) cost_to_you,
1910: to_number(pai.action_information7) amount_m_good,
1911: to_number(pai.action_information8) cash_equivalent,
1912: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
1913: pai.action_information4) expense_type,
1914: nvl(pai.action_information10,'N') trading_indicator
1915: from per_all_assignments_f paf,
1916: pay_assignment_actions paa,
1917: pay_action_information pai,
1918: pay_action_information pai_person
1919: where paf.person_id = p_person_id
1920: and paf.effective_end_date = (select max(paf2.effective_end_date)
1921: from per_all_assignments_f paf2
1914: nvl(pai.action_information10,'N') trading_indicator
1915: from per_all_assignments_f paf,
1916: pay_assignment_actions paa,
1917: pay_action_information pai,
1918: pay_action_information pai_person
1919: where paf.person_id = p_person_id
1920: and paf.effective_end_date = (select max(paf2.effective_end_date)
1921: from per_all_assignments_f paf2
1922: where paf2.assignment_id = paf.assignment_id
2100: edi_tax_qualifier121 varchar2(3);
2101:
2102: cursor get_data is
2103: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2104: use_index(pai_person,pay_action_information_n2)
2105: use_index(pai,pay_action_information_n2) */
2106: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2107: from per_all_assignments_f paf,
2108: pay_assignment_actions paa,
2101:
2102: cursor get_data is
2103: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2104: use_index(pai_person,pay_action_information_n2)
2105: use_index(pai,pay_action_information_n2) */
2106: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2107: from per_all_assignments_f paf,
2108: pay_assignment_actions paa,
2109: pay_action_information pai,
2105: use_index(pai,pay_action_information_n2) */
2106: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2107: from per_all_assignments_f paf,
2108: pay_assignment_actions paa,
2109: pay_action_information pai,
2110: pay_action_information pai_person
2111: where paf.person_id = p_person_id
2112: and paf.effective_end_date = (select max(paf2.effective_end_date)
2113: from per_all_assignments_f paf2
2106: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2107: from per_all_assignments_f paf,
2108: pay_assignment_actions paa,
2109: pay_action_information pai,
2110: pay_action_information pai_person
2111: where paf.person_id = p_person_id
2112: and paf.effective_end_date = (select max(paf2.effective_end_date)
2113: from per_all_assignments_f paf2
2114: where paf2.assignment_id = paf.assignment_id
2424:
2425: cursor get_assact_id(p_pact_id number) is
2426: select action_context_id
2427: from pay_assignment_actions paa,
2428: pay_action_information pai
2429: where paa.payroll_action_id = p_pact_id
2430: and pai.action_context_id = paa.assignment_action_id
2431: and pai.action_information_category = 'ADDRESS DETAILS'
2432: and pai.action_information14 = 'Employee Address'
2446: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2447: NVL(UPPER(hl.meaning), ' '), -- addr line 5
2448: NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
2449: NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
2450: from pay_action_information pai_gb,
2451: pay_action_information pai_person,
2452: hr_lookups hl
2453: where pai_person.action_context_id = p_act_id
2454: and pai_person.action_information_category = 'ADDRESS DETAILS'
2447: NVL(UPPER(hl.meaning), ' '), -- addr line 5
2448: NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
2449: NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
2450: from pay_action_information pai_gb,
2451: pay_action_information pai_person,
2452: hr_lookups hl
2453: where pai_person.action_context_id = p_act_id
2454: and pai_person.action_information_category = 'ADDRESS DETAILS'
2455: and pai_person.action_information14 = 'Employee Address'
3070:
3071: cursor count_occurrence(p_pact_id number,
3072: p_benefit_type varchar2) is
3073: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
3074: use_index(pai_person,pay_action_information_n2)
3075: use_index(pai,pay_action_information_n2) */
3076: count(*)
3077: from per_all_assignments_f paf,
3078: pay_assignment_actions paa,
3071: cursor count_occurrence(p_pact_id number,
3072: p_benefit_type varchar2) is
3073: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
3074: use_index(pai_person,pay_action_information_n2)
3075: use_index(pai,pay_action_information_n2) */
3076: count(*)
3077: from per_all_assignments_f paf,
3078: pay_assignment_actions paa,
3079: pay_action_information pai,
3075: use_index(pai,pay_action_information_n2) */
3076: count(*)
3077: from per_all_assignments_f paf,
3078: pay_assignment_actions paa,
3079: pay_action_information pai,
3080: pay_action_information pai_person
3081: where paf.person_id = p_person_id
3082: and paf.effective_end_date = (select max(paf2.effective_end_date)
3083: from per_all_assignments_f paf2
3076: count(*)
3077: from per_all_assignments_f paf,
3078: pay_assignment_actions paa,
3079: pay_action_information pai,
3080: pay_action_information pai_person
3081: where paf.person_id = p_person_id
3082: and paf.effective_end_date = (select max(paf2.effective_end_date)
3083: from per_all_assignments_f paf2
3084: where paf2.assignment_id = paf.assignment_id
3132: l_marror number;
3133:
3134: cursor csr_marror is
3135: select /*+ ORDERED use_nl(paa,pai,pai_person)
3136: use_index(pai_person,pay_action_information_n2)
3137: use_index(pai,pay_action_information_n2) */
3138: sum(pai.action_information7)
3139: from pay_assignment_actions paa,
3140: pay_action_information pai,
3133:
3134: cursor csr_marror is
3135: select /*+ ORDERED use_nl(paa,pai,pai_person)
3136: use_index(pai_person,pay_action_information_n2)
3137: use_index(pai,pay_action_information_n2) */
3138: sum(pai.action_information7)
3139: from pay_assignment_actions paa,
3140: pay_action_information pai,
3141: pay_action_information pai_person
3136: use_index(pai_person,pay_action_information_n2)
3137: use_index(pai,pay_action_information_n2) */
3138: sum(pai.action_information7)
3139: from pay_assignment_actions paa,
3140: pay_action_information pai,
3141: pay_action_information pai_person
3142: where paa.assignment_action_id = p_assact_id
3143: and pai.action_context_id = paa.assignment_action_id
3144: and pai.action_context_type = 'AAP'
3137: use_index(pai,pay_action_information_n2) */
3138: sum(pai.action_information7)
3139: from pay_assignment_actions paa,
3140: pay_action_information pai,
3141: pay_action_information pai_person
3142: where paa.assignment_action_id = p_assact_id
3143: and pai.action_context_id = paa.assignment_action_id
3144: and pai.action_context_type = 'AAP'
3145: and pai.action_information_category = 'MARORS'
3150:
3151: /* modified cursor csr_benefit for bug 7231911 */
3152: /* cursor csr_benefit is
3153: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3154: use_index(pai_person,pay_action_information_n2)
3155: use_index(pai,pay_action_information_n2)
3156: use_index(pai_a,pay_action_information_n2)*/
3157: /* sum(decode(pai.action_information_category,
3158: 'ASSETS TRANSFERRED', pai.action_information9,
3151: /* modified cursor csr_benefit for bug 7231911 */
3152: /* cursor csr_benefit is
3153: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3154: use_index(pai_person,pay_action_information_n2)
3155: use_index(pai,pay_action_information_n2)
3156: use_index(pai_a,pay_action_information_n2)*/
3157: /* sum(decode(pai.action_information_category,
3158: 'ASSETS TRANSFERRED', pai.action_information9,
3159: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3152: /* cursor csr_benefit is
3153: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3154: use_index(pai_person,pay_action_information_n2)
3155: use_index(pai,pay_action_information_n2)
3156: use_index(pai_a,pay_action_information_n2)*/
3157: /* sum(decode(pai.action_information_category,
3158: 'ASSETS TRANSFERRED', pai.action_information9,
3159: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3160: 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3172: 'OTHER ITEMS', pai.action_information9,
3173: 'OTHER ITEMS NON 1A', pai.action_information9,
3174: 'EXPENSES PAYMENTS', pai.action_information8)) total
3175: from pay_assignment_actions paa,
3176: pay_action_information pai,
3177: pay_action_information pai_a,
3178: pay_action_information pai_person
3179: where paa.assignment_action_id = p_assact_id
3180: and pai.action_context_id = paa.assignment_action_id
3173: 'OTHER ITEMS NON 1A', pai.action_information9,
3174: 'EXPENSES PAYMENTS', pai.action_information8)) total
3175: from pay_assignment_actions paa,
3176: pay_action_information pai,
3177: pay_action_information pai_a,
3178: pay_action_information pai_person
3179: where paa.assignment_action_id = p_assact_id
3180: and pai.action_context_id = paa.assignment_action_id
3181: and pai.action_context_type = 'AAP'
3174: 'EXPENSES PAYMENTS', pai.action_information8)) total
3175: from pay_assignment_actions paa,
3176: pay_action_information pai,
3177: pay_action_information pai_a,
3178: pay_action_information pai_person
3179: where paa.assignment_action_id = p_assact_id
3180: and pai.action_context_id = paa.assignment_action_id
3181: and pai.action_context_type = 'AAP'
3182: and pai.action_information_category = pai.action_information_category
3188: and pai_a.action_context_type = 'AAP'
3189: and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'; */
3190: cursor csr_benefit is
3191: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3192: use_index(pai_person,pay_action_information_n2)
3193: use_index(pai,pay_action_information_n2)
3194: use_index(pai_a,pay_action_information_n2)*/
3195: decode(pai.action_information_category,
3196: 'ASSETS TRANSFERRED', pai.action_information9,
3189: and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'; */
3190: cursor csr_benefit is
3191: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3192: use_index(pai_person,pay_action_information_n2)
3193: use_index(pai,pay_action_information_n2)
3194: use_index(pai_a,pay_action_information_n2)*/
3195: decode(pai.action_information_category,
3196: 'ASSETS TRANSFERRED', pai.action_information9,
3197: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3190: cursor csr_benefit is
3191: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3192: use_index(pai_person,pay_action_information_n2)
3193: use_index(pai,pay_action_information_n2)
3194: use_index(pai_a,pay_action_information_n2)*/
3195: decode(pai.action_information_category,
3196: 'ASSETS TRANSFERRED', pai.action_information9,
3197: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3198: 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3210: 'OTHER ITEMS', pai.action_information9,
3211: 'OTHER ITEMS NON 1A', pai.action_information9,
3212: 'EXPENSES PAYMENTS', pai.action_information8)total
3213: from pay_assignment_actions paa,
3214: pay_action_information pai,
3215: pay_action_information pai_a,
3216: pay_action_information pai_person
3217: where paa.assignment_action_id = p_assact_id
3218: and pai.action_context_id = paa.assignment_action_id
3211: 'OTHER ITEMS NON 1A', pai.action_information9,
3212: 'EXPENSES PAYMENTS', pai.action_information8)total
3213: from pay_assignment_actions paa,
3214: pay_action_information pai,
3215: pay_action_information pai_a,
3216: pay_action_information pai_person
3217: where paa.assignment_action_id = p_assact_id
3218: and pai.action_context_id = paa.assignment_action_id
3219: and pai.action_context_type = 'AAP'
3212: 'EXPENSES PAYMENTS', pai.action_information8)total
3213: from pay_assignment_actions paa,
3214: pay_action_information pai,
3215: pay_action_information pai_a,
3216: pay_action_information pai_person
3217: where paa.assignment_action_id = p_assact_id
3218: and pai.action_context_id = paa.assignment_action_id
3219: and pai.action_context_type = 'AAP'
3220: and pai.action_information_category = pai.action_information_category