[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: to_number(nvl(pai.action_information12,0)) cash_equivalent --Modified for the bug 11727875
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: to_number(nvl(pai.action_information12,0)) cash_equivalent --Modified for the bug 11727875
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: to_number(nvl(pai.action_information12,0)) cash_equivalent --Modified for the bug 11727875
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: to_number(nvl(pai.action_information12,0)) cash_equivalent --Modified for the bug 11727875
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
953: edi_tax_qualifier139 varchar2(3);
954:
955: cursor get_data is
956: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
957: use_index(pai_person,pay_action_information_n2)
958: use_index(pai,pay_action_information_n2) */
959: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
960: from per_all_assignments_f paf,
961: pay_assignment_actions paa,
954:
955: cursor get_data is
956: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
957: use_index(pai_person,pay_action_information_n2)
958: use_index(pai,pay_action_information_n2) */
959: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
960: from per_all_assignments_f paf,
961: pay_assignment_actions paa,
962: pay_action_information pai,
958: use_index(pai,pay_action_information_n2) */
959: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
960: from per_all_assignments_f paf,
961: pay_assignment_actions paa,
962: pay_action_information pai,
963: pay_action_information pai_person
964: where paf.person_id = p_person_id
965: and paf.effective_end_date = (select max(paf2.effective_end_date)
966: from per_all_assignments_f paf2
959: sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
960: from per_all_assignments_f paf,
961: pay_assignment_actions paa,
962: pay_action_information pai,
963: pay_action_information pai_person
964: where paf.person_id = p_person_id
965: and paf.effective_end_date = (select max(paf2.effective_end_date)
966: from per_all_assignments_f paf2
967: where paf2.assignment_id = paf.assignment_id
978:
979: -- EOY 2008
980: cursor get_data2 is
981: select /*+ ORDERED use_nl(paf,paa,pai)
982: use_index(pai,pay_action_information_n2) */
983: sum (pai.action_information30) vans_fuel
984: from per_all_assignments_f paf,
985: pay_assignment_actions paa,
986: pay_action_information pai
982: use_index(pai,pay_action_information_n2) */
983: sum (pai.action_information30) vans_fuel
984: from per_all_assignments_f paf,
985: pay_assignment_actions paa,
986: pay_action_information pai
987: where paf.person_id = p_person_id
988: and paf.effective_end_date = (select max(paf2.effective_end_date)
989: from per_all_assignments_f paf2
990: where paf2.assignment_id = paf.assignment_id
1086: edi_tax_year_end varchar2(10);
1087:
1088: cursor get_loan_amount is
1089: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1090: use_index(pai_person,pay_action_information_n2)
1091: use_index(pai,pay_action_information_n2) */
1092: sum(to_number(nvl(pai.action_information7,0)))
1093: from per_all_assignments_f paf,
1094: pay_assignment_actions paa,
1087:
1088: cursor get_loan_amount is
1089: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1090: use_index(pai_person,pay_action_information_n2)
1091: use_index(pai,pay_action_information_n2) */
1092: sum(to_number(nvl(pai.action_information7,0)))
1093: from per_all_assignments_f paf,
1094: pay_assignment_actions paa,
1095: pay_action_information pai,
1091: use_index(pai,pay_action_information_n2) */
1092: sum(to_number(nvl(pai.action_information7,0)))
1093: from per_all_assignments_f paf,
1094: pay_assignment_actions paa,
1095: pay_action_information pai,
1096: pay_action_information pai_person
1097: where paf.person_id = p_person_id
1098: and paf.effective_end_date = (select max(paf2.effective_end_date)
1099: from per_all_assignments_f paf2
1092: sum(to_number(nvl(pai.action_information7,0)))
1093: from per_all_assignments_f paf,
1094: pay_assignment_actions paa,
1095: pay_action_information pai,
1096: pay_action_information pai_person
1097: where paf.person_id = p_person_id
1098: and paf.effective_end_date = (select max(paf2.effective_end_date)
1099: from per_all_assignments_f paf2
1100: where paf2.assignment_id = paf.assignment_id
1112: cursor get_data(p_benefit_number number) is
1113: select *
1114: from (
1115: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1116: use_index(pai_person,pay_action_information_n2)
1117: use_index(pai,pay_action_information_n2) */
1118: rownum as row_num,
1119: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1120: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1113: select *
1114: from (
1115: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1116: use_index(pai_person,pay_action_information_n2)
1117: use_index(pai,pay_action_information_n2) */
1118: rownum as row_num,
1119: to_number(nvl(pai.action_information5,1)) number_of_borrower,
1120: to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1121: to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
1125: to_number(nvl(pai.action_information11,1))cash_equivalent,
1126: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1127: from per_all_assignments_f paf,
1128: pay_assignment_actions paa,
1129: pay_action_information pai,
1130: pay_action_information pai_person
1131: where paf.person_id = p_person_id
1132: and paf.effective_end_date = (select max(paf2.effective_end_date)
1133: from per_all_assignments_f paf2
1126: to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1127: from per_all_assignments_f paf,
1128: pay_assignment_actions paa,
1129: pay_action_information pai,
1130: pay_action_information pai_person
1131: where paf.person_id = p_person_id
1132: and paf.effective_end_date = (select max(paf2.effective_end_date)
1133: from per_all_assignments_f paf2
1134: where paf2.assignment_id = paf.assignment_id
1266: edi_tax_qualifier13 varchar2(3);
1267:
1268: cursor get_data is
1269: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1270: use_index(pai_person,pay_action_information_n2)
1271: use_index(pai,pay_action_information_n2) */
1272: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1273: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1274: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1267:
1268: cursor get_data is
1269: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1270: use_index(pai_person,pay_action_information_n2)
1271: use_index(pai,pay_action_information_n2) */
1272: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1273: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1274: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1275: from per_all_assignments_f paf,
1273: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1274: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1275: from per_all_assignments_f paf,
1276: pay_assignment_actions paa,
1277: pay_action_information pai,
1278: pay_action_information pai_person
1279: where paf.person_id = p_person_id
1280: and paf.effective_end_date = (select max(paf2.effective_end_date)
1281: from per_all_assignments_f paf2
1274: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1275: from per_all_assignments_f paf,
1276: pay_assignment_actions paa,
1277: pay_action_information pai,
1278: pay_action_information pai_person
1279: where paf.person_id = p_person_id
1280: and paf.effective_end_date = (select max(paf2.effective_end_date)
1281: from per_all_assignments_f paf2
1282: where paf2.assignment_id = paf.assignment_id
1344: edi_tax_qualifier64 varchar2(3);
1345:
1346: cursor get_data is
1347: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1348: use_index(pai_person,pay_action_information_n2)
1349: use_index(pai,pay_action_information_n2) */
1350: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1351: from per_all_assignments_f paf,
1352: pay_assignment_actions paa,
1345:
1346: cursor get_data is
1347: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1348: use_index(pai_person,pay_action_information_n2)
1349: use_index(pai,pay_action_information_n2) */
1350: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1351: from per_all_assignments_f paf,
1352: pay_assignment_actions paa,
1353: pay_action_information pai,
1349: use_index(pai,pay_action_information_n2) */
1350: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1351: from per_all_assignments_f paf,
1352: pay_assignment_actions paa,
1353: pay_action_information pai,
1354: pay_action_information pai_person
1355: where paf.person_id = p_person_id
1356: and paf.effective_end_date = (select max(paf2.effective_end_date)
1357: from per_all_assignments_f paf2
1350: sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1351: from per_all_assignments_f paf,
1352: pay_assignment_actions paa,
1353: pay_action_information pai,
1354: pay_action_information pai_person
1355: where paf.person_id = p_person_id
1356: and paf.effective_end_date = (select max(paf2.effective_end_date)
1357: from per_all_assignments_f paf2
1358: where paf2.assignment_id = paf.assignment_id
1414: edi_tax_qualifier13 varchar2(3);
1415:
1416: cursor get_data is
1417: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1418: use_index(pai_person,pay_action_information_n2)
1419: use_index(pai,pay_action_information_n2) */
1420: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1421: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1422: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1415:
1416: cursor get_data is
1417: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1418: use_index(pai_person,pay_action_information_n2)
1419: use_index(pai,pay_action_information_n2) */
1420: sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1421: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1422: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1423: from per_all_assignments_f paf,
1421: sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1422: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1423: from per_all_assignments_f paf,
1424: pay_assignment_actions paa,
1425: pay_action_information pai,
1426: pay_action_information pai_person
1427: where paf.person_id = p_person_id
1428: and paf.effective_end_date = (select max(paf2.effective_end_date)
1429: from per_all_assignments_f paf2
1422: sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1423: from per_all_assignments_f paf,
1424: pay_assignment_actions paa,
1425: pay_action_information pai,
1426: pay_action_information pai_person
1427: where paf.person_id = p_person_id
1428: and paf.effective_end_date = (select max(paf2.effective_end_date)
1429: from per_all_assignments_f paf2
1430: where paf2.assignment_id = paf.assignment_id
1523: edi_tax_qualifier116 varchar2(3);
1524:
1525: cursor get_data is
1526: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1527: use_index(pai_person,pay_action_information_n2)
1528: use_index(pai,pay_action_information_n2) */
1529: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1530: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1531: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1524:
1525: cursor get_data is
1526: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1527: use_index(pai_person,pay_action_information_n2)
1528: use_index(pai,pay_action_information_n2) */
1529: sum(to_number(nvl(pai.action_information7,0))) annual_value,
1530: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1531: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1532: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1532: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1533: pai.action_information4) asset_type
1534: from per_all_assignments_f paf,
1535: pay_assignment_actions paa,
1536: pay_action_information pai,
1537: pay_action_information pai_person
1538: where paf.person_id = p_person_id
1539: and paf.effective_end_date = (select max(paf2.effective_end_date)
1540: from per_all_assignments_f paf2
1533: pai.action_information4) asset_type
1534: from per_all_assignments_f paf,
1535: pay_assignment_actions paa,
1536: pay_action_information pai,
1537: pay_action_information pai_person
1538: where paf.person_id = p_person_id
1539: and paf.effective_end_date = (select max(paf2.effective_end_date)
1540: from per_all_assignments_f paf2
1541: where paf2.assignment_id = paf.assignment_id
1692: edi_tax_qualifier120 varchar2(3);
1693:
1694: cursor get_data(p_category varchar2, p_lookup varchar2) is
1695: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1696: use_index(pai_person,pay_action_information_n2)
1697: use_index(pai,pay_action_information_n2) */
1698: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1699: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1700: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1693:
1694: cursor get_data(p_category varchar2, p_lookup varchar2) is
1695: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1696: use_index(pai_person,pay_action_information_n2)
1697: use_index(pai,pay_action_information_n2) */
1698: sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1699: sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1700: sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1701: pay_gb_p11d_magtape.get_description(pai.action_information5,
1701: pay_gb_p11d_magtape.get_description(pai.action_information5,
1702: p_lookup, pai.action_information4) description
1703: from per_all_assignments_f paf,
1704: pay_assignment_actions paa,
1705: pay_action_information pai,
1706: pay_action_information pai_person
1707: where paf.person_id = p_person_id
1708: and paf.effective_end_date = (select max(paf2.effective_end_date)
1709: from per_all_assignments_f paf2
1702: p_lookup, pai.action_information4) description
1703: from per_all_assignments_f paf,
1704: pay_assignment_actions paa,
1705: pay_action_information pai,
1706: pay_action_information pai_person
1707: where paf.person_id = p_person_id
1708: and paf.effective_end_date = (select max(paf2.effective_end_date)
1709: from per_all_assignments_f paf2
1710: where paf2.assignment_id = paf.assignment_id
1926:
1927: cursor get_data
1928: is
1929: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1930: use_index(pai_person,pay_action_information_n2)
1931: use_index(pai,pay_action_information_n2) */
1932: to_number(pai.action_information6) cost_to_you,
1933: to_number(pai.action_information7) amount_m_good,
1934: to_number(pai.action_information8) cash_equivalent,
1927: cursor get_data
1928: is
1929: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1930: use_index(pai_person,pay_action_information_n2)
1931: use_index(pai,pay_action_information_n2) */
1932: to_number(pai.action_information6) cost_to_you,
1933: to_number(pai.action_information7) amount_m_good,
1934: to_number(pai.action_information8) cash_equivalent,
1935: pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
1938: -- converted to upper for bug# 12715628
1939: upper(pai.action_information9) description --Added for the bug# 8708099
1940: from per_all_assignments_f paf,
1941: pay_assignment_actions paa,
1942: pay_action_information pai,
1943: pay_action_information pai_person
1944: where paf.person_id = p_person_id
1945: and paf.effective_end_date = (select max(paf2.effective_end_date)
1946: from per_all_assignments_f paf2
1939: upper(pai.action_information9) description --Added for the bug# 8708099
1940: from per_all_assignments_f paf,
1941: pay_assignment_actions paa,
1942: pay_action_information pai,
1943: pay_action_information pai_person
1944: where paf.person_id = p_person_id
1945: and paf.effective_end_date = (select max(paf2.effective_end_date)
1946: from per_all_assignments_f paf2
1947: where paf2.assignment_id = paf.assignment_id
2146: edi_tax_qualifier121 varchar2(3);
2147:
2148: cursor get_data is
2149: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2150: use_index(pai_person,pay_action_information_n2)
2151: use_index(pai,pay_action_information_n2) */
2152: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2153: from per_all_assignments_f paf,
2154: pay_assignment_actions paa,
2147:
2148: cursor get_data is
2149: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2150: use_index(pai_person,pay_action_information_n2)
2151: use_index(pai,pay_action_information_n2) */
2152: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2153: from per_all_assignments_f paf,
2154: pay_assignment_actions paa,
2155: pay_action_information pai,
2151: use_index(pai,pay_action_information_n2) */
2152: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2153: from per_all_assignments_f paf,
2154: pay_assignment_actions paa,
2155: pay_action_information pai,
2156: pay_action_information pai_person
2157: where paf.person_id = p_person_id
2158: and paf.effective_end_date = (select max(paf2.effective_end_date)
2159: from per_all_assignments_f paf2
2152: sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2153: from per_all_assignments_f paf,
2154: pay_assignment_actions paa,
2155: pay_action_information pai,
2156: pay_action_information pai_person
2157: where paf.person_id = p_person_id
2158: and paf.effective_end_date = (select max(paf2.effective_end_date)
2159: from per_all_assignments_f paf2
2160: where paf2.assignment_id = paf.assignment_id
2475:
2476: cursor get_assact_id(p_pact_id number) is
2477: select action_context_id
2478: from pay_assignment_actions paa,
2479: pay_action_information pai
2480: where paa.payroll_action_id = p_pact_id
2481: and pai.action_context_id = paa.assignment_action_id
2482: and pai.action_information_category = 'ADDRESS DETAILS'
2483: and pai.action_information14 = 'Employee Address'
2497: NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2498: NVL(UPPER(hl.meaning), ' '), -- addr line 5
2499: NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
2500: NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
2501: from pay_action_information pai_gb,
2502: pay_action_information pai_person,
2503: hr_lookups hl
2504: where pai_person.action_context_id = p_act_id
2505: and pai_person.action_information_category = 'ADDRESS DETAILS'
2498: NVL(UPPER(hl.meaning), ' '), -- addr line 5
2499: NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
2500: NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
2501: from pay_action_information pai_gb,
2502: pay_action_information pai_person,
2503: hr_lookups hl
2504: where pai_person.action_context_id = p_act_id
2505: and pai_person.action_information_category = 'ADDRESS DETAILS'
2506: and pai_person.action_information14 = 'Employee Address'
3122:
3123: cursor count_occurrence(p_pact_id number,
3124: p_benefit_type varchar2) is
3125: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
3126: use_index(pai_person,pay_action_information_n2)
3127: use_index(pai,pay_action_information_n2) */
3128: count(*)
3129: from per_all_assignments_f paf,
3130: pay_assignment_actions paa,
3123: cursor count_occurrence(p_pact_id number,
3124: p_benefit_type varchar2) is
3125: select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
3126: use_index(pai_person,pay_action_information_n2)
3127: use_index(pai,pay_action_information_n2) */
3128: count(*)
3129: from per_all_assignments_f paf,
3130: pay_assignment_actions paa,
3131: pay_action_information pai,
3127: use_index(pai,pay_action_information_n2) */
3128: count(*)
3129: from per_all_assignments_f paf,
3130: pay_assignment_actions paa,
3131: pay_action_information pai,
3132: pay_action_information pai_person
3133: where paf.person_id = p_person_id
3134: and paf.effective_end_date = (select max(paf2.effective_end_date)
3135: from per_all_assignments_f paf2
3128: count(*)
3129: from per_all_assignments_f paf,
3130: pay_assignment_actions paa,
3131: pay_action_information pai,
3132: pay_action_information pai_person
3133: where paf.person_id = p_person_id
3134: and paf.effective_end_date = (select max(paf2.effective_end_date)
3135: from per_all_assignments_f paf2
3136: where paf2.assignment_id = paf.assignment_id
3184: l_marror number;
3185:
3186: cursor csr_marror is
3187: select /*+ ORDERED use_nl(paa,pai,pai_person)
3188: use_index(pai_person,pay_action_information_n2)
3189: use_index(pai,pay_action_information_n2) */
3190: sum(pai.action_information7)
3191: from pay_assignment_actions paa,
3192: pay_action_information pai,
3185:
3186: cursor csr_marror is
3187: select /*+ ORDERED use_nl(paa,pai,pai_person)
3188: use_index(pai_person,pay_action_information_n2)
3189: use_index(pai,pay_action_information_n2) */
3190: sum(pai.action_information7)
3191: from pay_assignment_actions paa,
3192: pay_action_information pai,
3193: pay_action_information pai_person
3188: use_index(pai_person,pay_action_information_n2)
3189: use_index(pai,pay_action_information_n2) */
3190: sum(pai.action_information7)
3191: from pay_assignment_actions paa,
3192: pay_action_information pai,
3193: pay_action_information pai_person
3194: where paa.assignment_action_id = p_assact_id
3195: and pai.action_context_id = paa.assignment_action_id
3196: and pai.action_context_type = 'AAP'
3189: use_index(pai,pay_action_information_n2) */
3190: sum(pai.action_information7)
3191: from pay_assignment_actions paa,
3192: pay_action_information pai,
3193: pay_action_information pai_person
3194: where paa.assignment_action_id = p_assact_id
3195: and pai.action_context_id = paa.assignment_action_id
3196: and pai.action_context_type = 'AAP'
3197: and pai.action_information_category = 'MARORS'
3202:
3203: /* modified cursor csr_benefit for bug 7231911 */
3204: /* cursor csr_benefit is
3205: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3206: use_index(pai_person,pay_action_information_n2)
3207: use_index(pai,pay_action_information_n2)
3208: use_index(pai_a,pay_action_information_n2)*/
3209: /* sum(decode(pai.action_information_category,
3210: 'ASSETS TRANSFERRED', pai.action_information9,
3203: /* modified cursor csr_benefit for bug 7231911 */
3204: /* cursor csr_benefit is
3205: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3206: use_index(pai_person,pay_action_information_n2)
3207: use_index(pai,pay_action_information_n2)
3208: use_index(pai_a,pay_action_information_n2)*/
3209: /* sum(decode(pai.action_information_category,
3210: 'ASSETS TRANSFERRED', pai.action_information9,
3211: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3204: /* cursor csr_benefit is
3205: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3206: use_index(pai_person,pay_action_information_n2)
3207: use_index(pai,pay_action_information_n2)
3208: use_index(pai_a,pay_action_information_n2)*/
3209: /* sum(decode(pai.action_information_category,
3210: 'ASSETS TRANSFERRED', pai.action_information9,
3211: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3212: 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3224: 'OTHER ITEMS', pai.action_information9,
3225: 'OTHER ITEMS NON 1A', pai.action_information9,
3226: 'EXPENSES PAYMENTS', pai.action_information8)) total
3227: from pay_assignment_actions paa,
3228: pay_action_information pai,
3229: pay_action_information pai_a,
3230: pay_action_information pai_person
3231: where paa.assignment_action_id = p_assact_id
3232: and pai.action_context_id = paa.assignment_action_id
3225: 'OTHER ITEMS NON 1A', pai.action_information9,
3226: 'EXPENSES PAYMENTS', pai.action_information8)) total
3227: from pay_assignment_actions paa,
3228: pay_action_information pai,
3229: pay_action_information pai_a,
3230: pay_action_information pai_person
3231: where paa.assignment_action_id = p_assact_id
3232: and pai.action_context_id = paa.assignment_action_id
3233: and pai.action_context_type = 'AAP'
3226: 'EXPENSES PAYMENTS', pai.action_information8)) total
3227: from pay_assignment_actions paa,
3228: pay_action_information pai,
3229: pay_action_information pai_a,
3230: pay_action_information pai_person
3231: where paa.assignment_action_id = p_assact_id
3232: and pai.action_context_id = paa.assignment_action_id
3233: and pai.action_context_type = 'AAP'
3234: and pai.action_information_category = pai.action_information_category
3244: for him is more than 5000, instead of checking for any one Loan Benefit having more than 5000 value. */
3245:
3246: cursor csr_benefit is
3247: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3248: use_index(pai_person,pay_action_information_n2)
3249: use_index(pai,pay_action_information_n2)
3250: use_index(pai_a,pay_action_information_n2)*/
3251: decode(pai.action_information_category,
3252: 'ASSETS TRANSFERRED', pai.action_information9,
3245:
3246: cursor csr_benefit is
3247: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3248: use_index(pai_person,pay_action_information_n2)
3249: use_index(pai,pay_action_information_n2)
3250: use_index(pai_a,pay_action_information_n2)*/
3251: decode(pai.action_information_category,
3252: 'ASSETS TRANSFERRED', pai.action_information9,
3253: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3246: cursor csr_benefit is
3247: select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3248: use_index(pai_person,pay_action_information_n2)
3249: use_index(pai,pay_action_information_n2)
3250: use_index(pai_a,pay_action_information_n2)*/
3251: decode(pai.action_information_category,
3252: 'ASSETS TRANSFERRED', pai.action_information9,
3253: 'PAYMENTS MADE FOR EMP', pai.action_information7,
3254: 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3268: 'OTHER ITEMS', pai.action_information9,
3269: 'OTHER ITEMS NON 1A', pai.action_information9,
3270: 'EXPENSES PAYMENTS', pai.action_information8)total
3271: from pay_assignment_actions paa,
3272: pay_action_information pai,
3273: pay_action_information pai_a,
3274: pay_action_information pai_c,
3275: pay_action_information pai_person
3276: where paa.assignment_action_id = p_assact_id
3269: 'OTHER ITEMS NON 1A', pai.action_information9,
3270: 'EXPENSES PAYMENTS', pai.action_information8)total
3271: from pay_assignment_actions paa,
3272: pay_action_information pai,
3273: pay_action_information pai_a,
3274: pay_action_information pai_c,
3275: pay_action_information pai_person
3276: where paa.assignment_action_id = p_assact_id
3277: and pai.action_context_id = paa.assignment_action_id
3270: 'EXPENSES PAYMENTS', pai.action_information8)total
3271: from pay_assignment_actions paa,
3272: pay_action_information pai,
3273: pay_action_information pai_a,
3274: pay_action_information pai_c,
3275: pay_action_information pai_person
3276: where paa.assignment_action_id = p_assact_id
3277: and pai.action_context_id = paa.assignment_action_id
3278: and pai.action_context_type = 'AAP'
3271: from pay_assignment_actions paa,
3272: pay_action_information pai,
3273: pay_action_information pai_a,
3274: pay_action_information pai_c,
3275: pay_action_information pai_person
3276: where paa.assignment_action_id = p_assact_id
3277: and pai.action_context_id = paa.assignment_action_id
3278: and pai.action_context_type = 'AAP'
3279: and pai.action_information_category = pai.action_information_category