[Home] [Help]
64:
65: 115.15 10-Jan-02 kthirmiy For TAR 1874418.995 to improve performance changed in
66: sort_action function . Removed the tables
67: pay_payroll_actions ppa_arch and
68: pay_assignment_actions and to go directly to
69: pay_assignment_actions mt table.
70: 115.16 18-JAN-02 meshah changed the sort cursor again. Need to fetch
71: zip code for the live address.
72: 115.20 12-FEB-02 meshah changed the action_creation cursor. Now seperate
65: 115.15 10-Jan-02 kthirmiy For TAR 1874418.995 to improve performance changed in
66: sort_action function . Removed the tables
67: pay_payroll_actions ppa_arch and
68: pay_assignment_actions and to go directly to
69: pay_assignment_actions mt table.
70: 115.16 18-JAN-02 meshah changed the sort cursor again. Need to fetch
71: zip code for the live address.
72: 115.20 12-FEB-02 meshah changed the action_creation cursor. Now seperate
73: procedures are called for Employee and Employer
168: l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
169:
170: l_business_group_id pay_payroll_actions.business_group_id%type;
171:
172: l_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
173: l_error_mesg varchar2(100);
174: l_year number ;
175: l_report_type varchar2(30) ;
176:
251:
252: 'SELECT
253: to_number(paa_arch.serial_number)
254: FROM
255: PAY_ASSIGNMENT_ACTIONS paa_arch
256: WHERE paa_arch.payroll_action_id = ' || l_eoy_payroll_action_id ||
257: ' AND :payroll_action_id is not null
258: AND paa_arch.action_status = ''C''
259: order by to_number(paa_arch.serial_number) ';
338: paa_arch.assignment_id,
339: paa_arch.tax_unit_id,
340: paf.effective_end_date
341: FROM per_assignments_f paf,
342: pay_assignment_actions paa_arch
343: WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
344: AND paa_arch.action_status = 'C'
345: AND paf.PERSON_ID = p_per_id
346: AND paa_arch.assignment_id = paf.assignment_id
443: paa_arch.assignment_id,
444: paa_arch.tax_unit_id,
445: paf.effective_end_date
446: FROM per_assignments_f paf,
447: pay_assignment_actions paa_arch,
448: pay_population_ranges ppr ';
449:
450: c_actions_where :=
451: ' WHERE paa_arch.action_status = ''C''
491: paa_arch.assignment_id,
492: paa_arch.tax_unit_id,
493: paf.effective_end_date
494: FROM per_assignments_f paf,
495: pay_assignment_actions paa_arch ';
496:
497: c_actions_where := ' WHERE paa_arch.action_status = ''C''
498: AND paa_arch.payroll_action_id +0= ' || p_eoy_payroll_action_id || '
499: AND paa_arch.assignment_id = paf.assignment_id
545: -- for each assignment/pre-payment/reversal).
546: hr_utility.set_location(l_procedure_name, 25);
547: hr_utility.trace('Before inserting the action record');
548:
549: select pay_assignment_actions_s.nextval
550: into lockingactid
551: from dual;
552:
553: -- insert the action record.
552:
553: -- insert the action record.
554: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
555:
556: -- Update serial_numbrt of Pay_assignment_actions with the
557: -- assignment_action_id .
558: update pay_assignment_actions
559: set serial_number = lockedactid
560: where assignment_action_id = lockingactid;
554: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
555:
556: -- Update serial_numbrt of Pay_assignment_actions with the
557: -- assignment_action_id .
558: update pay_assignment_actions
559: set serial_number = lockedactid
560: where assignment_action_id = lockingactid;
561: end if;
562: end loop;
575: paa_arch.tax_unit_id,
576: paf.effective_end_date
577: FROM per_periods_of_service pps,
578: per_assignments_f paf,
579: pay_assignment_actions paa_arch,
580: pay_population_ranges ppr ';
581: c_actions_where := '
582: /* disabling the index for performance reason */
583: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
607: paa_arch.tax_unit_id,
608: paf.effective_end_date
609: FROM per_periods_of_service pps,
610: per_assignments_f paf,
611: pay_assignment_actions paa_arch ';
612: c_actions_where := '
613: /* disabling the index for performance reason */
614: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
615: AND paa_arch.action_status = ''C''
667: -- for each assignment/pre-payment/reversal).
668: hr_utility.set_location(l_procedure_name, 45);
669: hr_utility.trace('Before inserting the action record');
670:
671: select pay_assignment_actions_s.nextval
672: into lockingactid
673: from dual;
674:
675: -- insert the action record.
674:
675: -- insert the action record.
676: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
677:
678: -- Update serial_numbrt of Pay_assignment_actions with the
679: -- assignment_action_id .
680: update pay_assignment_actions
681: set serial_number = lockedactid
682: where assignment_action_id = lockingactid;
676: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
677:
678: -- Update serial_numbrt of Pay_assignment_actions with the
679: -- assignment_action_id .
680: update pay_assignment_actions
681: set serial_number = lockedactid
682: where assignment_action_id = lockingactid;
683: end if;
684: end loop;
700: paa_arch.tax_unit_id,
701: paf.effective_end_date
702: FROM per_periods_of_service pps,
703: per_assignments_f paf,
704: pay_assignment_actions paa_arch,
705: pay_population_ranges ppr ';
706: c_actions_where := '
707: /* disabling the index for performance reason */
708: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
733: paa_arch.tax_unit_id,
734: paf.effective_end_date
735: FROM per_periods_of_service pps,
736: per_assignments_f paf,
737: pay_assignment_actions paa_arch ';
738: c_actions_where := '
739: /* disabling the index for performance reason */
740: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
741: AND paa_arch.action_status = ''C''
792: -- for each assignment/pre-payment/reversal).
793: hr_utility.set_location(l_procedure_name, 90);
794: hr_utility.trace('Before inserting the action record');
795:
796: select pay_assignment_actions_s.nextval
797: into lockingactid
798: from dual;
799:
800: -- insert the action record.
799:
800: -- insert the action record.
801: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
802:
803: -- Update serial_numbrt of Pay_assignment_actions with the
804: -- assignment_action_id .
805: update pay_assignment_actions
806: set serial_number = lockedactid
807: where assignment_action_id = lockingactid;
801: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
802:
803: -- Update serial_numbrt of Pay_assignment_actions with the
804: -- assignment_action_id .
805: update pay_assignment_actions
806: set serial_number = lockedactid
807: where assignment_action_id = lockingactid;
808: end if;
809: end loop;
821: paa_arch.assignment_id,
822: paa_arch.tax_unit_id,
823: paf.effective_end_date
824: FROM per_assignments_f paf,
825: pay_assignment_actions paa_arch';
826:
827: c_actions_where := '
828: WHERE paa_arch.payroll_action_id = ' || p_eoy_payroll_action_id ||'
829: AND paa_arch.action_status = ''C''
875: -- for each assignment/pre-payment/reversal).
876: hr_utility.set_location(l_procedure_name, 110);
877: hr_utility.trace('Before inserting the action record');
878:
879: select pay_assignment_actions_s.nextval
880: into lockingactid
881: from dual;
882:
883: -- insert the action record.
882:
883: -- insert the action record.
884: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
885:
886: -- Update serial_numbrt of Pay_assignment_actions with the
887: -- assignment_action_id .
888: update pay_assignment_actions
889: set serial_number = lockedactid
890: where assignment_action_id = lockingactid;
884: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
885:
886: -- Update serial_numbrt of Pay_assignment_actions with the
887: -- assignment_action_id .
888: update pay_assignment_actions
889: set serial_number = lockedactid
890: where assignment_action_id = lockingactid;
891: end if;
892: end loop;
916: paa_arch.assignment_id,
917: paa_arch.tax_unit_id,
918: paf.effective_end_date
919: FROM per_assignments_f paf,
920: pay_assignment_actions paa_arch,
921: pay_population_ranges ppr ';
922: c_actions_where := '
923: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
924: AND paa_arch.action_status = ''C''
940: paa_arch.assignment_id,
941: paa_arch.tax_unit_id,
942: paf.effective_end_date
943: FROM per_assignments_f paf,
944: pay_assignment_actions paa_arch ';
945: c_actions_where := '
946: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
947: AND paa_arch.action_status = ''C''
948: AND paa_arch.assignment_id = paf.assignment_id
1192: -- for each assignment/pre-payment/reversal).
1193: hr_utility.set_location(l_procedure_name, 220);
1194: hr_utility.trace('Before inserting the action record');
1195:
1196: select pay_assignment_actions_s.nextval
1197: into lockingactid
1198: from dual;
1199:
1200: -- insert the action record.
1199:
1200: -- insert the action record.
1201: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1202:
1203: -- Update serial_numbrt of Pay_assignment_actions with the
1204: -- assignment_action_id .
1205: update pay_assignment_actions
1206: set serial_number = lockedactid
1207: where assignment_action_id = lockingactid;
1201: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1202:
1203: -- Update serial_numbrt of Pay_assignment_actions with the
1204: -- assignment_action_id .
1205: update pay_assignment_actions
1206: set serial_number = lockedactid
1207: where assignment_action_id = lockingactid;
1208: end if;
1209: end loop;
1223: paa_arch.assignment_id,
1224: paa_arch.tax_unit_id,
1225: paf.effective_end_date
1226: FROM per_assignments_f paf,
1227: pay_assignment_actions paa_arch,
1228: pay_population_ranges ppr ';
1229: c_actions_where := '
1230: WHERE paa_arch.action_status = ''C''
1231: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1251: paa_arch.assignment_id,
1252: paa_arch.tax_unit_id,
1253: paf.effective_end_date
1254: FROM per_assignments_f paf,
1255: pay_assignment_actions paa_arch ';
1256: c_actions_where := '
1257: WHERE paa_arch.action_status = ''C''
1258: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1259: AND paa_arch.assignment_id = paf.assignment_id
1309: hr_utility.trace('Before inserting the action record');
1310:
1311: hr_utility.set_location('procpyr',3);
1312:
1313: select pay_assignment_actions_s.nextval
1314: into lockingactid
1315: from dual;
1316:
1317: -- insert the action record.
1316:
1317: -- insert the action record.
1318: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1319:
1320: -- Update serial_numbrt of Pay_assignment_actions with the
1321: -- assignment_action_id .
1322: update pay_assignment_actions
1323: set serial_number = lockedactid
1324: where assignment_action_id = lockingactid;
1318: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1319:
1320: -- Update serial_numbrt of Pay_assignment_actions with the
1321: -- assignment_action_id .
1322: update pay_assignment_actions
1323: set serial_number = lockedactid
1324: where assignment_action_id = lockingactid;
1325: end if;
1326: end loop;
1392: paa_arch.assignment_id,
1393: paa_arch.tax_unit_id,
1394: paf.effective_end_date
1395: FROM per_assignments_f paf,
1396: pay_assignment_actions paa_arch
1397: WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
1398: AND paa_arch.action_status = 'C'
1399: AND paf.PERSON_ID = p_per_id
1400: AND paa_arch.assignment_id = paf.assignment_id
1506: paa_arch.assignment_id,
1507: paa_arch.tax_unit_id,
1508: paf.effective_end_date
1509: FROM per_assignments_f paf,
1510: pay_assignment_actions paa_arch,
1511: pay_population_ranges ppr
1512: WHERE paa_arch.action_status = ''C''
1513: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1514: AND paa_arch.assignment_id = paf.assignment_id
1547: paa_arch.assignment_id,
1548: paa_arch.tax_unit_id,
1549: paf.effective_end_date
1550: FROM per_assignments_f paf,
1551: pay_assignment_actions paa_arch
1552: WHERE paa_arch.action_status = ''C''
1553: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1554: AND paa_arch.assignment_id = paf.assignment_id
1555: AND paf.effective_start_date = (select max(paf2.effective_start_date)
1600: hr_utility.trace('Before inserting the action record');
1601:
1602: hr_utility.set_location('procpyr',3);
1603:
1604: select pay_assignment_actions_s.nextval
1605: into lockingactid
1606: from dual;
1607:
1608: -- insert the action record.
1607:
1608: -- insert the action record.
1609: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1610:
1611: -- Update serial_numbrt of Pay_assignment_actions with the
1612: -- assignment_action_id .
1613: update pay_assignment_actions
1614: set serial_number = lockedactid
1615: where assignment_action_id = lockingactid;
1609: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1610:
1611: -- Update serial_numbrt of Pay_assignment_actions with the
1612: -- assignment_action_id .
1613: update pay_assignment_actions
1614: set serial_number = lockedactid
1615: where assignment_action_id = lockingactid;
1616: end loop;
1617: close c_actions_no_selection;
1630: paa_arch.tax_unit_id,
1631: paf.effective_end_date
1632: FROM per_periods_of_service pps,
1633: per_assignments_f paf,
1634: pay_assignment_actions paa_arch,
1635: pay_population_ranges ppr
1636: /* disabling the index for performance reason */
1637: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1638: AND paa_arch.action_status = ''C''
1676: paa_arch.tax_unit_id,
1677: paf.effective_end_date
1678: FROM per_periods_of_service pps,
1679: per_assignments_f paf,
1680: pay_assignment_actions paa_arch
1681: /* disabling the index for performance reason */
1682: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1683: AND paa_arch.action_status = ''C''
1684: AND nvl(final_process_date,''' || p_session_date || ''')
1735: hr_utility.trace('Before inserting the action record');
1736:
1737: hr_utility.set_location('procpyr',3);
1738:
1739: select pay_assignment_actions_s.nextval
1740: into lockingactid
1741: from dual;
1742:
1743: -- insert the action record.
1742:
1743: -- insert the action record.
1744: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1745:
1746: -- Update serial_numbrt of Pay_assignment_actions with the
1747: -- assignment_action_id .
1748: update pay_assignment_actions
1749: set serial_number = lockedactid
1750: where assignment_action_id = lockingactid;
1744: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1745:
1746: -- Update serial_numbrt of Pay_assignment_actions with the
1747: -- assignment_action_id .
1748: update pay_assignment_actions
1749: set serial_number = lockedactid
1750: where assignment_action_id = lockingactid;
1751: end loop;
1752: close c_actions_with_location;
1766: paa_arch.tax_unit_id,
1767: paf.effective_end_date
1768: FROM per_periods_of_service pps,
1769: per_assignments_f paf,
1770: pay_assignment_actions paa_arch,
1771: pay_population_ranges ppr
1772: /* disabling the index for performance reason */
1773: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1774: AND paa_arch.action_status = ''C''
1812: paa_arch.tax_unit_id,
1813: paf.effective_end_date
1814: FROM per_periods_of_service pps,
1815: per_assignments_f paf,
1816: pay_assignment_actions paa_arch
1817: /* disabling the index for performance reason */
1818: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1819: AND paa_arch.action_status = ''C''
1820: AND nvl(final_process_date,''' || p_session_date || ''')
1871: hr_utility.trace('Before inserting the action record');
1872:
1873: hr_utility.set_location('procpyr',3);
1874:
1875: select pay_assignment_actions_s.nextval
1876: into lockingactid
1877: from dual;
1878:
1879: -- insert the action record.
1878:
1879: -- insert the action record.
1880: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1881:
1882: -- Update serial_numbrt of Pay_assignment_actions with the
1883: -- assignment_action_id .
1884: update pay_assignment_actions
1885: set serial_number = lockedactid
1886: where assignment_action_id = lockingactid;
1880: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1881:
1882: -- Update serial_numbrt of Pay_assignment_actions with the
1883: -- assignment_action_id .
1884: update pay_assignment_actions
1885: set serial_number = lockedactid
1886: where assignment_action_id = lockingactid;
1887: end loop;
1888: close c_actions_with_org;
1914: hr_utility.trace('Before inserting the action record');
1915:
1916: hr_utility.set_location('procpyr',3);
1917:
1918: select pay_assignment_actions_s.nextval
1919: into lockingactid
1920: from dual;
1921:
1922: -- insert the action record.
1921:
1922: -- insert the action record.
1923: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1924:
1925: -- Update serial_numbrt of Pay_assignment_actions with the
1926: -- assignment_action_id .
1927: update pay_assignment_actions
1928: set serial_number = lockedactid
1929: where assignment_action_id = lockingactid;
1923: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1924:
1925: -- Update serial_numbrt of Pay_assignment_actions with the
1926: -- assignment_action_id .
1927: update pay_assignment_actions
1928: set serial_number = lockedactid
1929: where assignment_action_id = lockingactid;
1930: end loop;
1931: close c_actions_with_person;
1943: paa_arch.assignment_id,
1944: paa_arch.tax_unit_id,
1945: paf.effective_end_date
1946: FROM per_assignments_f paf,
1947: pay_assignment_actions paa_arch,
1948: pay_population_ranges ppr
1949: WHERE paa_arch.action_status = ''C''
1950: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1951: AND paa_arch.assignment_id = paf.assignment_id
1987: paa_arch.assignment_id,
1988: paa_arch.tax_unit_id,
1989: paf.effective_end_date
1990: FROM per_assignments_f paf,
1991: pay_assignment_actions paa_arch
1992: WHERE paa_arch.action_status = ''C''
1993: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1994: AND paa_arch.assignment_id = paf.assignment_id
1995: AND paf.effective_start_date = (select max(paf2.effective_start_date)
2044: hr_utility.trace('Before inserting the action record');
2045:
2046: hr_utility.set_location('procpyr',3);
2047:
2048: select pay_assignment_actions_s.nextval
2049: into lockingactid
2050: from dual;
2051:
2052: -- insert the action record.
2051:
2052: -- insert the action record.
2053: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2054:
2055: -- Update serial_numbrt of Pay_assignment_actions with the
2056: -- assignment_action_id .
2057: update pay_assignment_actions
2058: set serial_number = lockedactid
2059: where assignment_action_id = lockingactid;
2053: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2054:
2055: -- Update serial_numbrt of Pay_assignment_actions with the
2056: -- assignment_action_id .
2057: update pay_assignment_actions
2058: set serial_number = lockedactid
2059: where assignment_action_id = lockingactid;
2060: end loop;
2061: close c_actions_with_assign_set;
2080: chunk in number) is
2081:
2082: l_session_date date;
2083: l_year number ;
2084: l_gre_id pay_assignment_actions.tax_unit_id%type;
2085: l_org_id per_assignments_f.organization_id%type;
2086: l_loc_id per_assignments_f.location_id%type;
2087: l_per_id per_assignments_f.person_id%type;
2088: l_ssn per_people_f.national_identifier%type;
2207: ) is
2208:
2209: l_dt date;
2210: l_year number ;
2211: l_gre_id pay_assignment_actions.tax_unit_id%type;
2212: l_org_id per_assignments_f.organization_id%type;
2213: l_loc_id per_assignments_f.location_id%type;
2214: l_per_id per_assignments_f.person_id%type;
2215: l_ssn per_people_f.national_identifier%type;
2283:
2284: sqlstr :=
2285: 'select mt.rowid
2286: from hr_organization_units hou, hr_locations_all hl, per_periods_of_service pps, per_assignments_f paf,
2287: pay_assignment_actions mt where mt.payroll_action_id = :pactid and
2288: paf.assignment_id = mt.assignment_id and paf.effective_start_date = (select max(paf2.effective_start_date)
2289: from per_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
2290: and paf2.effective_start_date <= to_date(''31-DEC-''||'''||l_year||''',''DD/MM/YYYY''))
2291: and paf.effective_end_date >= to_date(''01-JAN-''||'''||l_year||''',''DD/MM/YYYY'')