[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_qualifier12 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
611: cursor get_data(p_benefit_number number) is
612: select *
613: from (
614: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
615: use_index(pai_person,pay_action_information_n2)
616: use_index(pai,pay_action_information_n2) */
617: rownum as row_num,
618: pai.action_information3 benefit_start_date,
619: pai.action_information4 benefit_end_date,
612: select *
613: from (
614: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
615: use_index(pai_person,pay_action_information_n2)
616: use_index(pai,pay_action_information_n2) */
617: rownum as row_num,
618: pai.action_information3 benefit_start_date,
619: pai.action_information4 benefit_end_date,
620: upper(pai.action_information6) make_of_car,
632: pai.action_information26 date_free_fuel_withdrawn,
633: pai.action_information27 free_fuel_reinstated
634: from per_all_assignments_f paf,
635: pay_assignment_actions paa,
636: pay_action_information pai,
637: pay_action_information pai_person
638: where paf.person_id = p_person_id
639: and paf.effective_end_date = (select max(paf2.effective_end_date)
640: from per_all_assignments_f paf2
633: pai.action_information27 free_fuel_reinstated
634: from per_all_assignments_f paf,
635: pay_assignment_actions paa,
636: pay_action_information pai,
637: pay_action_information pai_person
638: where paf.person_id = p_person_id
639: and paf.effective_end_date = (select max(paf2.effective_end_date)
640: from per_all_assignments_f paf2
641: where paf2.assignment_id = paf.assignment_id
892: edi_tax_qualifier12 varchar2(3);
893:
894: cursor get_data is
895: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
896: use_index(pai_person,pay_action_information_n2)
897: use_index(pai,pay_action_information_n2) */
898: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
899: from per_all_assignments_f paf,
900: pay_assignment_actions paa,
893:
894: cursor get_data is
895: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
896: use_index(pai_person,pay_action_information_n2)
897: use_index(pai,pay_action_information_n2) */
898: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
899: from per_all_assignments_f paf,
900: pay_assignment_actions paa,
901: pay_action_information pai,
897: use_index(pai,pay_action_information_n2) */
898: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
899: from per_all_assignments_f paf,
900: pay_assignment_actions paa,
901: pay_action_information pai,
902: pay_action_information pai_person
903: where paf.person_id = p_person_id
904: and paf.effective_end_date = (select max(paf2.effective_end_date)
905: from per_all_assignments_f paf2
898: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
899: from per_all_assignments_f paf,
900: pay_assignment_actions paa,
901: pay_action_information pai,
902: pay_action_information pai_person
903: where paf.person_id = p_person_id
904: and paf.effective_end_date = (select max(paf2.effective_end_date)
905: from per_all_assignments_f paf2
906: where paf2.assignment_id = paf.assignment_id
986: edi_tax_year_end varchar2(10);
987:
988: cursor get_loan_amount is
989: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
990: use_index(pai_person,pay_action_information_n2)
991: use_index(pai,pay_action_information_n2) */
992: sum(to_number(nvl(pai.action_information7,0)))
993: from per_all_assignments_f paf,
994: pay_assignment_actions paa,
987:
988: cursor get_loan_amount is
989: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
990: use_index(pai_person,pay_action_information_n2)
991: use_index(pai,pay_action_information_n2) */
992: sum(to_number(nvl(pai.action_information7,0)))
993: from per_all_assignments_f paf,
994: pay_assignment_actions paa,
995: pay_action_information pai,
991: use_index(pai,pay_action_information_n2) */
992: sum(to_number(nvl(pai.action_information7,0)))
993: from per_all_assignments_f paf,
994: pay_assignment_actions paa,
995: pay_action_information pai,
996: pay_action_information pai_person
997: where paf.person_id = p_person_id
998: and paf.effective_end_date = (select max(paf2.effective_end_date)
999: from per_all_assignments_f paf2
992: sum(to_number(nvl(pai.action_information7,0)))
993: from per_all_assignments_f paf,
994: pay_assignment_actions paa,
995: pay_action_information pai,
996: pay_action_information pai_person
997: where paf.person_id = p_person_id
998: and paf.effective_end_date = (select max(paf2.effective_end_date)
999: from per_all_assignments_f paf2
1000: where paf2.assignment_id = paf.assignment_id
1012: cursor get_data(p_benefit_number number) is
1013: select *
1014: from (
1015: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1016: use_index(pai_person,pay_action_information_n2)
1017: use_index(pai,pay_action_information_n2) */
1018: rownum as row_num,
1019: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1020: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1013: select *
1014: from (
1015: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1016: use_index(pai_person,pay_action_information_n2)
1017: use_index(pai,pay_action_information_n2) */
1018: rownum as row_num,
1019: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1020: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1021: to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
1025: to_number(nvl(pai.action_information11,1))cash_equivalent,
1026: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1027: from per_all_assignments_f paf,
1028: pay_assignment_actions paa,
1029: pay_action_information pai,
1030: pay_action_information pai_person
1031: where paf.person_id = p_person_id
1032: and paf.effective_end_date = (select max(paf2.effective_end_date)
1033: from per_all_assignments_f paf2
1026: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1027: from per_all_assignments_f paf,
1028: pay_assignment_actions paa,
1029: pay_action_information pai,
1030: pay_action_information pai_person
1031: where paf.person_id = p_person_id
1032: and paf.effective_end_date = (select max(paf2.effective_end_date)
1033: from per_all_assignments_f paf2
1034: where paf2.assignment_id = paf.assignment_id
1164: edi_tax_qualifier13 varchar2(3);
1165:
1166: cursor get_data is
1167: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1168: use_index(pai_person,pay_action_information_n2)
1169: use_index(pai,pay_action_information_n2) */
1170: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1171: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1172: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1165:
1166: cursor get_data is
1167: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1168: use_index(pai_person,pay_action_information_n2)
1169: use_index(pai,pay_action_information_n2) */
1170: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1171: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1172: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1173: from per_all_assignments_f paf,
1171: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1172: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1173: from per_all_assignments_f paf,
1174: pay_assignment_actions paa,
1175: pay_action_information pai,
1176: pay_action_information pai_person
1177: where paf.person_id = p_person_id
1178: and paf.effective_end_date = (select max(paf2.effective_end_date)
1179: from per_all_assignments_f paf2
1172: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1173: from per_all_assignments_f paf,
1174: pay_assignment_actions paa,
1175: pay_action_information pai,
1176: pay_action_information pai_person
1177: where paf.person_id = p_person_id
1178: and paf.effective_end_date = (select max(paf2.effective_end_date)
1179: from per_all_assignments_f paf2
1180: where paf2.assignment_id = paf.assignment_id
1242: edi_tax_qualifier64 varchar2(3);
1243:
1244: cursor get_data is
1245: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1246: use_index(pai_person,pay_action_information_n2)
1247: use_index(pai,pay_action_information_n2) */
1248: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1249: from per_all_assignments_f paf,
1250: pay_assignment_actions paa,
1243:
1244: cursor get_data is
1245: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1246: use_index(pai_person,pay_action_information_n2)
1247: use_index(pai,pay_action_information_n2) */
1248: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1249: from per_all_assignments_f paf,
1250: pay_assignment_actions paa,
1251: pay_action_information pai,
1247: use_index(pai,pay_action_information_n2) */
1248: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1249: from per_all_assignments_f paf,
1250: pay_assignment_actions paa,
1251: pay_action_information pai,
1252: pay_action_information pai_person
1253: where paf.person_id = p_person_id
1254: and paf.effective_end_date = (select max(paf2.effective_end_date)
1255: from per_all_assignments_f paf2
1248: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1249: from per_all_assignments_f paf,
1250: pay_assignment_actions paa,
1251: pay_action_information pai,
1252: pay_action_information pai_person
1253: where paf.person_id = p_person_id
1254: and paf.effective_end_date = (select max(paf2.effective_end_date)
1255: from per_all_assignments_f paf2
1256: where paf2.assignment_id = paf.assignment_id
1312: edi_tax_qualifier13 varchar2(3);
1313:
1314: cursor get_data is
1315: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1316: use_index(pai_person,pay_action_information_n2)
1317: use_index(pai,pay_action_information_n2) */
1318: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1319: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1320: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1313:
1314: cursor get_data is
1315: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1316: use_index(pai_person,pay_action_information_n2)
1317: use_index(pai,pay_action_information_n2) */
1318: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1319: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1320: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1321: from per_all_assignments_f paf,
1319: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1320: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1321: from per_all_assignments_f paf,
1322: pay_assignment_actions paa,
1323: pay_action_information pai,
1324: pay_action_information pai_person
1325: where paf.person_id = p_person_id
1326: and paf.effective_end_date = (select max(paf2.effective_end_date)
1327: from per_all_assignments_f paf2
1320: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1321: from per_all_assignments_f paf,
1322: pay_assignment_actions paa,
1323: pay_action_information pai,
1324: pay_action_information pai_person
1325: where paf.person_id = p_person_id
1326: and paf.effective_end_date = (select max(paf2.effective_end_date)
1327: from per_all_assignments_f paf2
1328: where paf2.assignment_id = paf.assignment_id
1421: edi_tax_qualifier116 varchar2(3);
1422:
1423: cursor get_data is
1424: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1425: use_index(pai_person,pay_action_information_n2)
1426: use_index(pai,pay_action_information_n2) */
1427: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1428: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1429: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1422:
1423: cursor get_data is
1424: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1425: use_index(pai_person,pay_action_information_n2)
1426: use_index(pai,pay_action_information_n2) */
1427: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1428: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1429: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1430: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1430: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1431: pai.action_information4) asset_type
1432: from per_all_assignments_f paf,
1433: pay_assignment_actions paa,
1434: pay_action_information pai,
1435: pay_action_information pai_person
1436: where paf.person_id = p_person_id
1437: and paf.effective_end_date = (select max(paf2.effective_end_date)
1438: from per_all_assignments_f paf2
1431: pai.action_information4) asset_type
1432: from per_all_assignments_f paf,
1433: pay_assignment_actions paa,
1434: pay_action_information pai,
1435: pay_action_information pai_person
1436: where paf.person_id = p_person_id
1437: and paf.effective_end_date = (select max(paf2.effective_end_date)
1438: from per_all_assignments_f paf2
1439: where paf2.assignment_id = paf.assignment_id
1590: edi_tax_qualifier120 varchar2(3);
1591:
1592: cursor get_data(p_category varchar2, p_lookup varchar2) is
1593: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1594: use_index(pai_person,pay_action_information_n2)
1595: use_index(pai,pay_action_information_n2) */
1596: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1597: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1598: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1591:
1592: cursor get_data(p_category varchar2, p_lookup varchar2) is
1593: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1594: use_index(pai_person,pay_action_information_n2)
1595: use_index(pai,pay_action_information_n2) */
1596: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1597: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1598: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1599: pay_gb_p11d_magtape.get_description(pai.action_information5,
1599: pay_gb_p11d_magtape.get_description(pai.action_information5,
1600: p_lookup, pai.action_information4) description
1601: from per_all_assignments_f paf,
1602: pay_assignment_actions paa,
1603: pay_action_information pai,
1604: pay_action_information pai_person
1605: where paf.person_id = p_person_id
1606: and paf.effective_end_date = (select max(paf2.effective_end_date)
1607: from per_all_assignments_f paf2
1600: p_lookup, pai.action_information4) description
1601: from per_all_assignments_f paf,
1602: pay_assignment_actions paa,
1603: pay_action_information pai,
1604: pay_action_information pai_person
1605: where paf.person_id = p_person_id
1606: and paf.effective_end_date = (select max(paf2.effective_end_date)
1607: from per_all_assignments_f paf2
1608: where paf2.assignment_id = paf.assignment_id
1823:
1824: cursor get_data
1825: is
1826: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1827: use_index(pai_person,pay_action_information_n2)
1828: use_index(pai,pay_action_information_n2) */
1829: to_number(pai.action_information6) cost_to_you,
1830: to_number(pai.action_information7) amount_m_good,
1831: to_number(pai.action_information8) cash_equivalent,
1824: cursor get_data
1825: is
1826: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1827: use_index(pai_person,pay_action_information_n2)
1828: use_index(pai,pay_action_information_n2) */
1829: to_number(pai.action_information6) cost_to_you,
1830: to_number(pai.action_information7) amount_m_good,
1831: to_number(pai.action_information8) cash_equivalent,
1832: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
1833: pai.action_information4) expense_type,
1834: nvl(pai.action_information10,'N') trading_indicator
1835: from per_all_assignments_f paf,
1836: pay_assignment_actions paa,
1837: pay_action_information pai,
1838: pay_action_information pai_person
1839: where paf.person_id = p_person_id
1840: and paf.effective_end_date = (select max(paf2.effective_end_date)
1841: from per_all_assignments_f paf2
1834: nvl(pai.action_information10,'N') trading_indicator
1835: from per_all_assignments_f paf,
1836: pay_assignment_actions paa,
1837: pay_action_information pai,
1838: pay_action_information pai_person
1839: where paf.person_id = p_person_id
1840: and paf.effective_end_date = (select max(paf2.effective_end_date)
1841: from per_all_assignments_f paf2
1842: where paf2.assignment_id = paf.assignment_id
2001: edi_tax_qualifier121 varchar2(3);
2002:
2003: cursor get_data is
2004: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2005: use_index(pai_person,pay_action_information_n2)
2006: use_index(pai,pay_action_information_n2) */
2007: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2008: from per_all_assignments_f paf,
2009: pay_assignment_actions paa,
2002:
2003: cursor get_data is
2004: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2005: use_index(pai_person,pay_action_information_n2)
2006: use_index(pai,pay_action_information_n2) */
2007: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2008: from per_all_assignments_f paf,
2009: pay_assignment_actions paa,
2010: pay_action_information pai,
2006: use_index(pai,pay_action_information_n2) */
2007: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2008: from per_all_assignments_f paf,
2009: pay_assignment_actions paa,
2010: pay_action_information pai,
2011: pay_action_information pai_person
2012: where paf.person_id = p_person_id
2013: and paf.effective_end_date = (select max(paf2.effective_end_date)
2014: from per_all_assignments_f paf2
2007: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2008: from per_all_assignments_f paf,
2009: pay_assignment_actions paa,
2010: pay_action_information pai,
2011: pay_action_information pai_person
2012: where paf.person_id = p_person_id
2013: and paf.effective_end_date = (select max(paf2.effective_end_date)
2014: from per_all_assignments_f paf2
2015: where paf2.assignment_id = paf.assignment_id
2287:
2288: cursor get_assact_id(p_pact_id number) is
2289: select action_context_id
2290: from pay_assignment_actions paa,
2291: pay_action_information pai
2292: where paa.payroll_action_id = p_pact_id
2293: and pai.action_context_id = paa.assignment_action_id
2294: and pai.action_information_category = 'ADDRESS DETAILS'
2295: and pai.action_information14 = 'Employee Address'
2307: NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
2308: NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2309: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2310: NVL(UPPER(hl.meaning), ' ') -- addr line 5
2311: from pay_action_information pai_gb,
2312: pay_action_information pai_person,
2313: hr_lookups hl
2314: where pai_person.action_context_id = p_act_id
2315: and pai_person.action_information_category = 'ADDRESS DETAILS'
2308: NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2309: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2310: NVL(UPPER(hl.meaning), ' ') -- addr line 5
2311: from pay_action_information pai_gb,
2312: pay_action_information pai_person,
2313: hr_lookups hl
2314: where pai_person.action_context_id = p_act_id
2315: and pai_person.action_information_category = 'ADDRESS DETAILS'
2316: and pai_person.action_information14 = 'Employee Address'
2904:
2905: cursor count_occurrence(p_pact_id number,
2906: p_benefit_type varchar2) is
2907: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2908: use_index(pai_person,pay_action_information_n2)
2909: use_index(pai,pay_action_information_n2) */
2910: count(*)
2911: from per_all_assignments_f paf,
2912: pay_assignment_actions paa,
2905: cursor count_occurrence(p_pact_id number,
2906: p_benefit_type varchar2) is
2907: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2908: use_index(pai_person,pay_action_information_n2)
2909: use_index(pai,pay_action_information_n2) */
2910: count(*)
2911: from per_all_assignments_f paf,
2912: pay_assignment_actions paa,
2913: pay_action_information pai,
2909: use_index(pai,pay_action_information_n2) */
2910: count(*)
2911: from per_all_assignments_f paf,
2912: pay_assignment_actions paa,
2913: pay_action_information pai,
2914: pay_action_information pai_person
2915: where paf.person_id = p_person_id
2916: and paf.effective_end_date = (select max(paf2.effective_end_date)
2917: from per_all_assignments_f paf2
2910: count(*)
2911: from per_all_assignments_f paf,
2912: pay_assignment_actions paa,
2913: pay_action_information pai,
2914: pay_action_information pai_person
2915: where paf.person_id = p_person_id
2916: and paf.effective_end_date = (select max(paf2.effective_end_date)
2917: from per_all_assignments_f paf2
2918: where paf2.assignment_id = paf.assignment_id