[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
612: cursor get_data(p_benefit_number number) is
613: select *
614: from (
615: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
616: use_index(pai_person,pay_action_information_n2)
617: use_index(pai,pay_action_information_n2) */
618: rownum as row_num,
619: pai.action_information3 benefit_start_date,
620: pai.action_information4 benefit_end_date,
613: select *
614: from (
615: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
616: use_index(pai_person,pay_action_information_n2)
617: use_index(pai,pay_action_information_n2) */
618: rownum as row_num,
619: pai.action_information3 benefit_start_date,
620: pai.action_information4 benefit_end_date,
621: upper(pai.action_information6) make_of_car,
633: pai.action_information26 date_free_fuel_withdrawn,
634: pai.action_information27 free_fuel_reinstated
635: from per_all_assignments_f paf,
636: pay_assignment_actions paa,
637: pay_action_information pai,
638: pay_action_information pai_person
639: where paf.person_id = p_person_id
640: and paf.effective_end_date = (select max(paf2.effective_end_date)
641: from per_all_assignments_f paf2
634: pai.action_information27 free_fuel_reinstated
635: from per_all_assignments_f paf,
636: pay_assignment_actions paa,
637: pay_action_information pai,
638: pay_action_information pai_person
639: where paf.person_id = p_person_id
640: and paf.effective_end_date = (select max(paf2.effective_end_date)
641: from per_all_assignments_f paf2
642: where paf2.assignment_id = paf.assignment_id
904: edi_tax_qualifier12 varchar2(3);
905:
906: cursor get_data is
907: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
908: use_index(pai_person,pay_action_information_n2)
909: use_index(pai,pay_action_information_n2) */
910: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
911: from per_all_assignments_f paf,
912: pay_assignment_actions paa,
905:
906: cursor get_data is
907: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
908: use_index(pai_person,pay_action_information_n2)
909: use_index(pai,pay_action_information_n2) */
910: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
911: from per_all_assignments_f paf,
912: pay_assignment_actions paa,
913: pay_action_information pai,
909: use_index(pai,pay_action_information_n2) */
910: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
911: from per_all_assignments_f paf,
912: pay_assignment_actions paa,
913: pay_action_information pai,
914: pay_action_information pai_person
915: where paf.person_id = p_person_id
916: and paf.effective_end_date = (select max(paf2.effective_end_date)
917: from per_all_assignments_f paf2
910: sum(to_number(nvl(pai.action_information15,0))) cash_equivalent
911: from per_all_assignments_f paf,
912: pay_assignment_actions paa,
913: pay_action_information pai,
914: pay_action_information pai_person
915: where paf.person_id = p_person_id
916: and paf.effective_end_date = (select max(paf2.effective_end_date)
917: from per_all_assignments_f paf2
918: where paf2.assignment_id = paf.assignment_id
998: edi_tax_year_end varchar2(10);
999:
1000: cursor get_loan_amount is
1001: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1002: use_index(pai_person,pay_action_information_n2)
1003: use_index(pai,pay_action_information_n2) */
1004: sum(to_number(nvl(pai.action_information7,0)))
1005: from per_all_assignments_f paf,
1006: pay_assignment_actions paa,
999:
1000: cursor get_loan_amount is
1001: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1002: use_index(pai_person,pay_action_information_n2)
1003: use_index(pai,pay_action_information_n2) */
1004: sum(to_number(nvl(pai.action_information7,0)))
1005: from per_all_assignments_f paf,
1006: pay_assignment_actions paa,
1007: pay_action_information pai,
1003: use_index(pai,pay_action_information_n2) */
1004: sum(to_number(nvl(pai.action_information7,0)))
1005: from per_all_assignments_f paf,
1006: pay_assignment_actions paa,
1007: pay_action_information pai,
1008: pay_action_information pai_person
1009: where paf.person_id = p_person_id
1010: and paf.effective_end_date = (select max(paf2.effective_end_date)
1011: from per_all_assignments_f paf2
1004: sum(to_number(nvl(pai.action_information7,0)))
1005: from per_all_assignments_f paf,
1006: pay_assignment_actions paa,
1007: pay_action_information pai,
1008: pay_action_information pai_person
1009: where paf.person_id = p_person_id
1010: and paf.effective_end_date = (select max(paf2.effective_end_date)
1011: from per_all_assignments_f paf2
1012: where paf2.assignment_id = paf.assignment_id
1024: cursor get_data(p_benefit_number number) is
1025: select *
1026: from (
1027: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1028: use_index(pai_person,pay_action_information_n2)
1029: use_index(pai,pay_action_information_n2) */
1030: rownum as row_num,
1031: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1032: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1025: select *
1026: from (
1027: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1028: use_index(pai_person,pay_action_information_n2)
1029: use_index(pai,pay_action_information_n2) */
1030: rownum as row_num,
1031: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1032: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1033: to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
1037: to_number(nvl(pai.action_information11,1))cash_equivalent,
1038: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1039: from per_all_assignments_f paf,
1040: pay_assignment_actions paa,
1041: pay_action_information pai,
1042: pay_action_information pai_person
1043: where paf.person_id = p_person_id
1044: and paf.effective_end_date = (select max(paf2.effective_end_date)
1045: from per_all_assignments_f paf2
1038: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1039: from per_all_assignments_f paf,
1040: pay_assignment_actions paa,
1041: pay_action_information pai,
1042: pay_action_information pai_person
1043: where paf.person_id = p_person_id
1044: and paf.effective_end_date = (select max(paf2.effective_end_date)
1045: from per_all_assignments_f paf2
1046: where paf2.assignment_id = paf.assignment_id
1176: edi_tax_qualifier13 varchar2(3);
1177:
1178: cursor get_data is
1179: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1180: use_index(pai_person,pay_action_information_n2)
1181: use_index(pai,pay_action_information_n2) */
1182: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1183: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1184: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1177:
1178: cursor get_data is
1179: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1180: use_index(pai_person,pay_action_information_n2)
1181: use_index(pai,pay_action_information_n2) */
1182: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1183: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1184: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1185: from per_all_assignments_f paf,
1183: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1184: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1185: from per_all_assignments_f paf,
1186: pay_assignment_actions paa,
1187: pay_action_information pai,
1188: pay_action_information pai_person
1189: where paf.person_id = p_person_id
1190: and paf.effective_end_date = (select max(paf2.effective_end_date)
1191: from per_all_assignments_f paf2
1184: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1185: from per_all_assignments_f paf,
1186: pay_assignment_actions paa,
1187: pay_action_information pai,
1188: pay_action_information pai_person
1189: where paf.person_id = p_person_id
1190: and paf.effective_end_date = (select max(paf2.effective_end_date)
1191: from per_all_assignments_f paf2
1192: where paf2.assignment_id = paf.assignment_id
1254: edi_tax_qualifier64 varchar2(3);
1255:
1256: cursor get_data is
1257: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1258: use_index(pai_person,pay_action_information_n2)
1259: use_index(pai,pay_action_information_n2) */
1260: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1261: from per_all_assignments_f paf,
1262: pay_assignment_actions paa,
1255:
1256: cursor get_data is
1257: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1258: use_index(pai_person,pay_action_information_n2)
1259: use_index(pai,pay_action_information_n2) */
1260: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1261: from per_all_assignments_f paf,
1262: pay_assignment_actions paa,
1263: pay_action_information pai,
1259: use_index(pai,pay_action_information_n2) */
1260: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1261: from per_all_assignments_f paf,
1262: pay_assignment_actions paa,
1263: pay_action_information pai,
1264: pay_action_information pai_person
1265: where paf.person_id = p_person_id
1266: and paf.effective_end_date = (select max(paf2.effective_end_date)
1267: from per_all_assignments_f paf2
1260: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1261: from per_all_assignments_f paf,
1262: pay_assignment_actions paa,
1263: pay_action_information pai,
1264: pay_action_information pai_person
1265: where paf.person_id = p_person_id
1266: and paf.effective_end_date = (select max(paf2.effective_end_date)
1267: from per_all_assignments_f paf2
1268: where paf2.assignment_id = paf.assignment_id
1324: edi_tax_qualifier13 varchar2(3);
1325:
1326: cursor get_data is
1327: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1328: use_index(pai_person,pay_action_information_n2)
1329: use_index(pai,pay_action_information_n2) */
1330: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1331: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1332: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1325:
1326: cursor get_data is
1327: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1328: use_index(pai_person,pay_action_information_n2)
1329: use_index(pai,pay_action_information_n2) */
1330: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1331: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1332: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1333: from per_all_assignments_f paf,
1331: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1332: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1333: from per_all_assignments_f paf,
1334: pay_assignment_actions paa,
1335: pay_action_information pai,
1336: pay_action_information pai_person
1337: where paf.person_id = p_person_id
1338: and paf.effective_end_date = (select max(paf2.effective_end_date)
1339: from per_all_assignments_f paf2
1332: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1333: from per_all_assignments_f paf,
1334: pay_assignment_actions paa,
1335: pay_action_information pai,
1336: pay_action_information pai_person
1337: where paf.person_id = p_person_id
1338: and paf.effective_end_date = (select max(paf2.effective_end_date)
1339: from per_all_assignments_f paf2
1340: where paf2.assignment_id = paf.assignment_id
1433: edi_tax_qualifier116 varchar2(3);
1434:
1435: cursor get_data is
1436: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1437: use_index(pai_person,pay_action_information_n2)
1438: use_index(pai,pay_action_information_n2) */
1439: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1440: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1441: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1434:
1435: cursor get_data is
1436: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1437: use_index(pai_person,pay_action_information_n2)
1438: use_index(pai,pay_action_information_n2) */
1439: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1440: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1441: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1442: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1442: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1443: pai.action_information4) asset_type
1444: from per_all_assignments_f paf,
1445: pay_assignment_actions paa,
1446: pay_action_information pai,
1447: pay_action_information pai_person
1448: where paf.person_id = p_person_id
1449: and paf.effective_end_date = (select max(paf2.effective_end_date)
1450: from per_all_assignments_f paf2
1443: pai.action_information4) asset_type
1444: from per_all_assignments_f paf,
1445: pay_assignment_actions paa,
1446: pay_action_information pai,
1447: pay_action_information pai_person
1448: where paf.person_id = p_person_id
1449: and paf.effective_end_date = (select max(paf2.effective_end_date)
1450: from per_all_assignments_f paf2
1451: where paf2.assignment_id = paf.assignment_id
1602: edi_tax_qualifier120 varchar2(3);
1603:
1604: cursor get_data(p_category varchar2, p_lookup varchar2) is
1605: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1606: use_index(pai_person,pay_action_information_n2)
1607: use_index(pai,pay_action_information_n2) */
1608: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1609: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1610: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1603:
1604: cursor get_data(p_category varchar2, p_lookup varchar2) is
1605: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1606: use_index(pai_person,pay_action_information_n2)
1607: use_index(pai,pay_action_information_n2) */
1608: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1609: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1610: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1611: pay_gb_p11d_magtape.get_description(pai.action_information5,
1611: pay_gb_p11d_magtape.get_description(pai.action_information5,
1612: p_lookup, pai.action_information4) description
1613: from per_all_assignments_f paf,
1614: pay_assignment_actions paa,
1615: pay_action_information pai,
1616: pay_action_information pai_person
1617: where paf.person_id = p_person_id
1618: and paf.effective_end_date = (select max(paf2.effective_end_date)
1619: from per_all_assignments_f paf2
1612: p_lookup, pai.action_information4) description
1613: from per_all_assignments_f paf,
1614: pay_assignment_actions paa,
1615: pay_action_information pai,
1616: pay_action_information pai_person
1617: where paf.person_id = p_person_id
1618: and paf.effective_end_date = (select max(paf2.effective_end_date)
1619: from per_all_assignments_f paf2
1620: where paf2.assignment_id = paf.assignment_id
1835:
1836: cursor get_data
1837: is
1838: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1839: use_index(pai_person,pay_action_information_n2)
1840: use_index(pai,pay_action_information_n2) */
1841: to_number(pai.action_information6) cost_to_you,
1842: to_number(pai.action_information7) amount_m_good,
1843: to_number(pai.action_information8) cash_equivalent,
1836: cursor get_data
1837: is
1838: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1839: use_index(pai_person,pay_action_information_n2)
1840: use_index(pai,pay_action_information_n2) */
1841: to_number(pai.action_information6) cost_to_you,
1842: to_number(pai.action_information7) amount_m_good,
1843: to_number(pai.action_information8) cash_equivalent,
1844: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
1845: pai.action_information4) expense_type,
1846: nvl(pai.action_information10,'N') trading_indicator
1847: from per_all_assignments_f paf,
1848: pay_assignment_actions paa,
1849: pay_action_information pai,
1850: pay_action_information pai_person
1851: where paf.person_id = p_person_id
1852: and paf.effective_end_date = (select max(paf2.effective_end_date)
1853: from per_all_assignments_f paf2
1846: nvl(pai.action_information10,'N') trading_indicator
1847: from per_all_assignments_f paf,
1848: pay_assignment_actions paa,
1849: pay_action_information pai,
1850: pay_action_information pai_person
1851: where paf.person_id = p_person_id
1852: and paf.effective_end_date = (select max(paf2.effective_end_date)
1853: from per_all_assignments_f paf2
1854: where paf2.assignment_id = paf.assignment_id
2032: edi_tax_qualifier121 varchar2(3);
2033:
2034: cursor get_data is
2035: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2036: use_index(pai_person,pay_action_information_n2)
2037: use_index(pai,pay_action_information_n2) */
2038: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2039: from per_all_assignments_f paf,
2040: pay_assignment_actions paa,
2033:
2034: cursor get_data is
2035: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2036: use_index(pai_person,pay_action_information_n2)
2037: use_index(pai,pay_action_information_n2) */
2038: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2039: from per_all_assignments_f paf,
2040: pay_assignment_actions paa,
2041: pay_action_information pai,
2037: use_index(pai,pay_action_information_n2) */
2038: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2039: from per_all_assignments_f paf,
2040: pay_assignment_actions paa,
2041: pay_action_information pai,
2042: pay_action_information pai_person
2043: where paf.person_id = p_person_id
2044: and paf.effective_end_date = (select max(paf2.effective_end_date)
2045: from per_all_assignments_f paf2
2038: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2039: from per_all_assignments_f paf,
2040: pay_assignment_actions paa,
2041: pay_action_information pai,
2042: pay_action_information pai_person
2043: where paf.person_id = p_person_id
2044: and paf.effective_end_date = (select max(paf2.effective_end_date)
2045: from per_all_assignments_f paf2
2046: where paf2.assignment_id = paf.assignment_id
2339:
2340: cursor get_assact_id(p_pact_id number) is
2341: select action_context_id
2342: from pay_assignment_actions paa,
2343: pay_action_information pai
2344: where paa.payroll_action_id = p_pact_id
2345: and pai.action_context_id = paa.assignment_action_id
2346: and pai.action_information_category = 'ADDRESS DETAILS'
2347: and pai.action_information14 = 'Employee Address'
2359: NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
2360: NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2361: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2362: NVL(UPPER(hl.meaning), ' ') -- addr line 5
2363: from pay_action_information pai_gb,
2364: pay_action_information pai_person,
2365: hr_lookups hl
2366: where pai_person.action_context_id = p_act_id
2367: and pai_person.action_information_category = 'ADDRESS DETAILS'
2360: NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2361: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2362: NVL(UPPER(hl.meaning), ' ') -- addr line 5
2363: from pay_action_information pai_gb,
2364: pay_action_information pai_person,
2365: hr_lookups hl
2366: where pai_person.action_context_id = p_act_id
2367: and pai_person.action_information_category = 'ADDRESS DETAILS'
2368: and pai_person.action_information14 = 'Employee Address'
2955:
2956: cursor count_occurrence(p_pact_id number,
2957: p_benefit_type varchar2) is
2958: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2959: use_index(pai_person,pay_action_information_n2)
2960: use_index(pai,pay_action_information_n2) */
2961: count(*)
2962: from per_all_assignments_f paf,
2963: pay_assignment_actions paa,
2956: cursor count_occurrence(p_pact_id number,
2957: p_benefit_type varchar2) is
2958: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2959: use_index(pai_person,pay_action_information_n2)
2960: use_index(pai,pay_action_information_n2) */
2961: count(*)
2962: from per_all_assignments_f paf,
2963: pay_assignment_actions paa,
2964: pay_action_information pai,
2960: use_index(pai,pay_action_information_n2) */
2961: count(*)
2962: from per_all_assignments_f paf,
2963: pay_assignment_actions paa,
2964: pay_action_information pai,
2965: pay_action_information pai_person
2966: where paf.person_id = p_person_id
2967: and paf.effective_end_date = (select max(paf2.effective_end_date)
2968: from per_all_assignments_f paf2
2961: count(*)
2962: from per_all_assignments_f paf,
2963: pay_assignment_actions paa,
2964: pay_action_information pai,
2965: pay_action_information pai_person
2966: where paf.person_id = p_person_id
2967: and paf.effective_end_date = (select max(paf2.effective_end_date)
2968: from per_all_assignments_f paf2
2969: where paf2.assignment_id = paf.assignment_id
3017: l_marror number;
3018:
3019: cursor csr_marror is
3020: select /*+ ORDERED use_nl(paa,pai,pai_person)
3021: use_index(pai_person,pay_action_information_n2)
3022: use_index(pai,pay_action_information_n2) */
3023: sum(pai.action_information7)
3024: from pay_assignment_actions paa,
3025: pay_action_information pai,
3018:
3019: cursor csr_marror is
3020: select /*+ ORDERED use_nl(paa,pai,pai_person)
3021: use_index(pai_person,pay_action_information_n2)
3022: use_index(pai,pay_action_information_n2) */
3023: sum(pai.action_information7)
3024: from pay_assignment_actions paa,
3025: pay_action_information pai,
3026: pay_action_information pai_person
3021: use_index(pai_person,pay_action_information_n2)
3022: use_index(pai,pay_action_information_n2) */
3023: sum(pai.action_information7)
3024: from pay_assignment_actions paa,
3025: pay_action_information pai,
3026: pay_action_information pai_person
3027: where paa.assignment_action_id = p_assact_id
3028: and pai.action_context_id = paa.assignment_action_id
3029: and pai.action_context_type = 'AAP'
3022: use_index(pai,pay_action_information_n2) */
3023: sum(pai.action_information7)
3024: from pay_assignment_actions paa,
3025: pay_action_information pai,
3026: pay_action_information pai_person
3027: where paa.assignment_action_id = p_assact_id
3028: and pai.action_context_id = paa.assignment_action_id
3029: and pai.action_context_type = 'AAP'
3030: and pai.action_information_category = 'MARORS'
3034: and upper(pai_person.action_information13) = upper(p_employer_ref);
3035:
3036: cursor csr_benefit is
3037: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3038: use_index(pai_person,pay_action_information_n2)
3039: use_index(pai,pay_action_information_n2)
3040: use_index(pai_a,pay_action_information_n2)*/
3041: sum(decode(pai.action_information_category,
3042: 'ASSETS TRANSFERRED', pai.action_information9,
3035:
3036: cursor csr_benefit is
3037: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3038: use_index(pai_person,pay_action_information_n2)
3039: use_index(pai,pay_action_information_n2)
3040: use_index(pai_a,pay_action_information_n2)*/
3041: sum(decode(pai.action_information_category,
3042: 'ASSETS TRANSFERRED', pai.action_information9,
3043: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3036: cursor csr_benefit is
3037: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3038: use_index(pai_person,pay_action_information_n2)
3039: use_index(pai,pay_action_information_n2)
3040: use_index(pai_a,pay_action_information_n2)*/
3041: sum(decode(pai.action_information_category,
3042: 'ASSETS TRANSFERRED', pai.action_information9,
3043: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3044: 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3055: 'OTHER ITEMS', pai.action_information9,
3056: 'OTHER ITEMS NON 1A', pai.action_information9,
3057: 'EXPENSES PAYMENTS', pai.action_information8)) total
3058: from pay_assignment_actions paa,
3059: pay_action_information pai,
3060: pay_action_information pai_a,
3061: pay_action_information pai_person
3062: where paa.assignment_action_id = p_assact_id
3063: and pai.action_context_id = paa.assignment_action_id
3056: 'OTHER ITEMS NON 1A', pai.action_information9,
3057: 'EXPENSES PAYMENTS', pai.action_information8)) total
3058: from pay_assignment_actions paa,
3059: pay_action_information pai,
3060: pay_action_information pai_a,
3061: pay_action_information pai_person
3062: where paa.assignment_action_id = p_assact_id
3063: and pai.action_context_id = paa.assignment_action_id
3064: and pai.action_context_type = 'AAP'
3057: 'EXPENSES PAYMENTS', pai.action_information8)) total
3058: from pay_assignment_actions paa,
3059: pay_action_information pai,
3060: pay_action_information pai_a,
3061: pay_action_information pai_person
3062: where paa.assignment_action_id = p_assact_id
3063: and pai.action_context_id = paa.assignment_action_id
3064: and pai.action_context_type = 'AAP'
3065: and pai.action_information_category = pai.action_information_category