[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
177: l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
178:
179: l_business_group_id pay_payroll_actions.business_group_id%type;
180:
181: l_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
182: l_error_mesg varchar2(100);
183: l_year number ;
184: l_report_type varchar2(30) ;
185:
260:
261: 'SELECT
262: to_number(paa_arch.serial_number)
263: FROM
264: PAY_ASSIGNMENT_ACTIONS paa_arch
265: WHERE paa_arch.payroll_action_id = ' || l_eoy_payroll_action_id ||
266: ' AND :payroll_action_id is not null
267: AND paa_arch.action_status = ''C''
268: order by to_number(paa_arch.serial_number) ';
347: paa_arch.assignment_id,
348: paa_arch.tax_unit_id,
349: paf.effective_end_date
350: FROM per_assignments_f paf,
351: pay_assignment_actions paa_arch
352: WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
353: AND paa_arch.action_status = 'C'
354: AND paf.PERSON_ID = p_per_id
355: AND paa_arch.assignment_id = paf.assignment_id
452: paa_arch.assignment_id,
453: paa_arch.tax_unit_id,
454: paf.effective_end_date
455: FROM per_assignments_f paf,
456: pay_assignment_actions paa_arch,
457: pay_population_ranges ppr ';
458:
459: c_actions_where :=
460: ' WHERE paa_arch.action_status = ''C''
500: paa_arch.assignment_id,
501: paa_arch.tax_unit_id,
502: paf.effective_end_date
503: FROM per_assignments_f paf,
504: pay_assignment_actions paa_arch ';
505:
506: c_actions_where := ' WHERE paa_arch.action_status = ''C''
507: AND paa_arch.payroll_action_id +0= ' || p_eoy_payroll_action_id || '
508: AND paa_arch.assignment_id = paf.assignment_id
554: -- for each assignment/pre-payment/reversal).
555: hr_utility.set_location(l_procedure_name, 25);
556: hr_utility.trace('Before inserting the action record');
557:
558: select pay_assignment_actions_s.nextval
559: into lockingactid
560: from dual;
561:
562: -- insert the action record.
561:
562: -- insert the action record.
563: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
564:
565: -- Update serial_numbrt of Pay_assignment_actions with the
566: -- assignment_action_id .
567: update pay_assignment_actions
568: set serial_number = lockedactid
569: where assignment_action_id = lockingactid;
563: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
564:
565: -- Update serial_numbrt of Pay_assignment_actions with the
566: -- assignment_action_id .
567: update pay_assignment_actions
568: set serial_number = lockedactid
569: where assignment_action_id = lockingactid;
570: end if;
571: end loop;
584: paa_arch.tax_unit_id,
585: paf.effective_end_date
586: FROM
587: per_assignments_f paf,
588: pay_assignment_actions paa_arch,
589: pay_population_ranges ppr ';
590: c_actions_where := '
591: /* disabling the index for performance reason */
592: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
612: paa_arch.tax_unit_id,
613: paf.effective_end_date
614: FROM
615: per_assignments_f paf,
616: pay_assignment_actions paa_arch ';
617: c_actions_where := '
618: /* disabling the index for performance reason */
619: WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
620: AND paa_arch.action_status = ''C''
668: -- for each assignment/pre-payment/reversal).
669: hr_utility.set_location(l_procedure_name, 45);
670: hr_utility.trace('Before inserting the action record');
671:
672: select pay_assignment_actions_s.nextval
673: into lockingactid
674: from dual;
675:
676: -- insert the action record.
675:
676: -- insert the action record.
677: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
678:
679: -- Update serial_numbrt of Pay_assignment_actions with the
680: -- assignment_action_id .
681: update pay_assignment_actions
682: set serial_number = lockedactid
683: where assignment_action_id = lockingactid;
677: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
678:
679: -- Update serial_numbrt of Pay_assignment_actions with the
680: -- assignment_action_id .
681: update pay_assignment_actions
682: set serial_number = lockedactid
683: where assignment_action_id = lockingactid;
684: end if;
685: end loop;
701: paa_arch.tax_unit_id,
702: paf.effective_end_date
703: FROM
704: per_assignments_f paf,
705: pay_assignment_actions paa_arch,
706: pay_population_ranges ppr ';
707: c_actions_where := '
708: /* disabling the index for performance reason */
709: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
730: paa_arch.tax_unit_id,
731: paf.effective_end_date
732: FROM
733: per_assignments_f paf,
734: pay_assignment_actions paa_arch ';
735: c_actions_where := '
736: /* disabling the index for performance reason */
737: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
738: AND paa_arch.action_status = ''C''
785: -- for each assignment/pre-payment/reversal).
786: hr_utility.set_location(l_procedure_name, 90);
787: hr_utility.trace('Before inserting the action record');
788:
789: select pay_assignment_actions_s.nextval
790: into lockingactid
791: from dual;
792:
793: -- insert the action record.
792:
793: -- insert the action record.
794: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
795:
796: -- Update serial_numbrt of Pay_assignment_actions with the
797: -- assignment_action_id .
798: update pay_assignment_actions
799: set serial_number = lockedactid
800: where assignment_action_id = lockingactid;
794: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
795:
796: -- Update serial_numbrt of Pay_assignment_actions with the
797: -- assignment_action_id .
798: update pay_assignment_actions
799: set serial_number = lockedactid
800: where assignment_action_id = lockingactid;
801: end if;
802: end loop;
814: paa_arch.assignment_id,
815: paa_arch.tax_unit_id,
816: paf.effective_end_date
817: FROM per_assignments_f paf,
818: pay_assignment_actions paa_arch';
819:
820: c_actions_where := '
821: WHERE paa_arch.payroll_action_id = ' || p_eoy_payroll_action_id ||'
822: AND paa_arch.action_status = ''C''
868: -- for each assignment/pre-payment/reversal).
869: hr_utility.set_location(l_procedure_name, 110);
870: hr_utility.trace('Before inserting the action record');
871:
872: select pay_assignment_actions_s.nextval
873: into lockingactid
874: from dual;
875:
876: -- insert the action record.
875:
876: -- insert the action record.
877: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
878:
879: -- Update serial_numbrt of Pay_assignment_actions with the
880: -- assignment_action_id .
881: update pay_assignment_actions
882: set serial_number = lockedactid
883: where assignment_action_id = lockingactid;
877: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
878:
879: -- Update serial_numbrt of Pay_assignment_actions with the
880: -- assignment_action_id .
881: update pay_assignment_actions
882: set serial_number = lockedactid
883: where assignment_action_id = lockingactid;
884: end if;
885: end loop;
909: paa_arch.assignment_id,
910: paa_arch.tax_unit_id,
911: paf.effective_end_date
912: FROM per_assignments_f paf,
913: pay_assignment_actions paa_arch,
914: pay_population_ranges ppr ';
915: c_actions_where := '
916: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
917: AND paa_arch.action_status = ''C''
933: paa_arch.assignment_id,
934: paa_arch.tax_unit_id,
935: paf.effective_end_date
936: FROM per_assignments_f paf,
937: pay_assignment_actions paa_arch ';
938: c_actions_where := '
939: WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
940: AND paa_arch.action_status = ''C''
941: AND paa_arch.assignment_id = paf.assignment_id
1185: -- for each assignment/pre-payment/reversal).
1186: hr_utility.set_location(l_procedure_name, 220);
1187: hr_utility.trace('Before inserting the action record');
1188:
1189: select pay_assignment_actions_s.nextval
1190: into lockingactid
1191: from dual;
1192:
1193: -- insert the action record.
1192:
1193: -- insert the action record.
1194: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1195:
1196: -- Update serial_numbrt of Pay_assignment_actions with the
1197: -- assignment_action_id .
1198: update pay_assignment_actions
1199: set serial_number = lockedactid
1200: where assignment_action_id = lockingactid;
1194: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1195:
1196: -- Update serial_numbrt of Pay_assignment_actions with the
1197: -- assignment_action_id .
1198: update pay_assignment_actions
1199: set serial_number = lockedactid
1200: where assignment_action_id = lockingactid;
1201: end if;
1202: end loop;
1216: paa_arch.assignment_id,
1217: paa_arch.tax_unit_id,
1218: paf.effective_end_date
1219: FROM per_assignments_f paf,
1220: pay_assignment_actions paa_arch,
1221: pay_population_ranges ppr ';
1222: c_actions_where := '
1223: WHERE paa_arch.action_status = ''C''
1224: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1244: paa_arch.assignment_id,
1245: paa_arch.tax_unit_id,
1246: paf.effective_end_date
1247: FROM per_assignments_f paf,
1248: pay_assignment_actions paa_arch ';
1249: c_actions_where := '
1250: WHERE paa_arch.action_status = ''C''
1251: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1252: AND paa_arch.assignment_id = paf.assignment_id
1302: hr_utility.trace('Before inserting the action record');
1303:
1304: hr_utility.set_location('procpyr',3);
1305:
1306: select pay_assignment_actions_s.nextval
1307: into lockingactid
1308: from dual;
1309:
1310: -- insert the action record.
1309:
1310: -- insert the action record.
1311: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1312:
1313: -- Update serial_numbrt of Pay_assignment_actions with the
1314: -- assignment_action_id .
1315: update pay_assignment_actions
1316: set serial_number = lockedactid
1317: where assignment_action_id = lockingactid;
1311: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1312:
1313: -- Update serial_numbrt of Pay_assignment_actions with the
1314: -- assignment_action_id .
1315: update pay_assignment_actions
1316: set serial_number = lockedactid
1317: where assignment_action_id = lockingactid;
1318: end if;
1319: end loop;
1386: paa_arch.assignment_id,
1387: paa_arch.tax_unit_id,
1388: paf.effective_end_date
1389: FROM per_assignments_f paf,
1390: pay_assignment_actions paa_arch
1391: WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
1392: AND paa_arch.action_status = 'C'
1393: AND paf.PERSON_ID = p_per_id
1394: AND paa_arch.assignment_id = paf.assignment_id
1503: paa_arch.assignment_id,
1504: paa_arch.tax_unit_id,
1505: paf.effective_end_date
1506: FROM per_assignments_f paf,
1507: pay_assignment_actions paa_arch,
1508: pay_population_ranges ppr ';
1509: c_actions_where :=
1510: 'WHERE paa_arch.action_status = ''C''
1511: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1562: paa_arch.assignment_id,
1563: paa_arch.tax_unit_id,
1564: paf.effective_end_date
1565: FROM per_assignments_f paf,
1566: pay_assignment_actions paa_arch ';
1567: c_actions_where :=
1568: 'WHERE paa_arch.action_status = ''C''
1569: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1570: AND paa_arch.assignment_id = paf.assignment_id
1628: hr_utility.trace('Before inserting the action record');
1629:
1630: hr_utility.set_location('procpyr',3);
1631:
1632: select pay_assignment_actions_s.nextval
1633: into lockingactid
1634: from dual;
1635:
1636: -- insert the action record.
1635:
1636: -- insert the action record.
1637: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1638:
1639: -- Update serial_numbrt of Pay_assignment_actions with the
1640: -- assignment_action_id .
1641: update pay_assignment_actions
1642: set serial_number = lockedactid
1643: where assignment_action_id = lockingactid;
1637: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1638:
1639: -- Update serial_numbrt of Pay_assignment_actions with the
1640: -- assignment_action_id .
1641: update pay_assignment_actions
1642: set serial_number = lockedactid
1643: where assignment_action_id = lockingactid;
1644: end loop;
1645: close c_actions_no_selection;
1658: paa_arch.tax_unit_id,
1659: paf.effective_end_date
1660: FROM
1661: per_assignments_f paf,
1662: pay_assignment_actions paa_arch,
1663: pay_population_ranges ppr
1664: /* disabling the index for performance reason */ ';
1665: c_actions_where :=
1666: 'WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1701: paa_arch.tax_unit_id,
1702: paf.effective_end_date
1703: FROM
1704: per_assignments_f paf,
1705: pay_assignment_actions paa_arch
1706: /* disabling the index for performance reason */ ';
1707: c_actions_where :=
1708: 'WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1709: AND paa_arch.action_status = ''C''
1769: hr_utility.trace('Before inserting the action record');
1770:
1771: hr_utility.set_location('procpyr',3);
1772:
1773: select pay_assignment_actions_s.nextval
1774: into lockingactid
1775: from dual;
1776:
1777: -- insert the action record.
1776:
1777: -- insert the action record.
1778: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1779:
1780: -- Update serial_numbrt of Pay_assignment_actions with the
1781: -- assignment_action_id .
1782: update pay_assignment_actions
1783: set serial_number = lockedactid
1784: where assignment_action_id = lockingactid;
1778: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1779:
1780: -- Update serial_numbrt of Pay_assignment_actions with the
1781: -- assignment_action_id .
1782: update pay_assignment_actions
1783: set serial_number = lockedactid
1784: where assignment_action_id = lockingactid;
1785: end loop;
1786: close c_actions_with_location;
1800: paa_arch.tax_unit_id,
1801: paf.effective_end_date
1802: FROM
1803: per_assignments_f paf,
1804: pay_assignment_actions paa_arch,
1805: pay_population_ranges ppr
1806: /* disabling the index for performance reason */ ';
1807: c_actions_where :=
1808: 'WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1843: paa_arch.tax_unit_id,
1844: paf.effective_end_date
1845: FROM
1846: per_assignments_f paf,
1847: pay_assignment_actions paa_arch
1848: /* disabling the index for performance reason */ ';
1849: c_actions_where :=
1850: 'WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1851: AND paa_arch.action_status = ''C''
1909: hr_utility.trace('Before inserting the action record');
1910:
1911: hr_utility.set_location('procpyr',3);
1912:
1913: select pay_assignment_actions_s.nextval
1914: into lockingactid
1915: from dual;
1916:
1917: -- insert the action record.
1916:
1917: -- insert the action record.
1918: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1919:
1920: -- Update serial_numbrt of Pay_assignment_actions with the
1921: -- assignment_action_id .
1922: update pay_assignment_actions
1923: set serial_number = lockedactid
1924: where assignment_action_id = lockingactid;
1918: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1919:
1920: -- Update serial_numbrt of Pay_assignment_actions with the
1921: -- assignment_action_id .
1922: update pay_assignment_actions
1923: set serial_number = lockedactid
1924: where assignment_action_id = lockingactid;
1925: end loop;
1926: close c_actions_with_org;
1937: paa_arch.tax_unit_id,
1938: paf.effective_end_date
1939: FROM
1940: per_assignments_f paf,
1941: pay_assignment_actions paa_arch
1942: /* disabling the index for performance reason */ ';
1943: c_actions_where :=
1944: 'WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1945: AND paa_arch.action_status = ''C''
2003: hr_utility.trace('Before inserting the action record');
2004:
2005: hr_utility.set_location('procpyr',3);
2006:
2007: select pay_assignment_actions_s.nextval
2008: into lockingactid
2009: from dual;
2010:
2011: -- insert the action record.
2010:
2011: -- insert the action record.
2012: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2013:
2014: -- Update serial_numbrt of Pay_assignment_actions with the
2015: -- assignment_action_id .
2016: update pay_assignment_actions
2017: set serial_number = lockedactid
2018: where assignment_action_id = lockingactid;
2012: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2013:
2014: -- Update serial_numbrt of Pay_assignment_actions with the
2015: -- assignment_action_id .
2016: update pay_assignment_actions
2017: set serial_number = lockedactid
2018: where assignment_action_id = lockingactid;
2019: end loop;
2020: close c_actions_with_person;
2032: paa_arch.assignment_id,
2033: paa_arch.tax_unit_id,
2034: paf.effective_end_date
2035: FROM per_assignments_f paf,
2036: pay_assignment_actions paa_arch,
2037: pay_population_ranges ppr ';
2038: c_actions_where :=
2039: 'WHERE paa_arch.action_status = ''C''
2040: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
2077: paa_arch.assignment_id,
2078: paa_arch.tax_unit_id,
2079: paf.effective_end_date
2080: FROM per_assignments_f paf,
2081: pay_assignment_actions paa_arch ';
2082: c_actions_where :=
2083: 'WHERE paa_arch.action_status = ''C''
2084: AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
2085: AND paa_arch.assignment_id = paf.assignment_id
2145: hr_utility.trace('Before inserting the action record');
2146:
2147: hr_utility.set_location('procpyr',3);
2148:
2149: select pay_assignment_actions_s.nextval
2150: into lockingactid
2151: from dual;
2152:
2153: -- insert the action record.
2152:
2153: -- insert the action record.
2154: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2155:
2156: -- Update serial_numbrt of Pay_assignment_actions with the
2157: -- assignment_action_id .
2158: update pay_assignment_actions
2159: set serial_number = lockedactid
2160: where assignment_action_id = lockingactid;
2154: hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2155:
2156: -- Update serial_numbrt of Pay_assignment_actions with the
2157: -- assignment_action_id .
2158: update pay_assignment_actions
2159: set serial_number = lockedactid
2160: where assignment_action_id = lockingactid;
2161: end loop;
2162: close c_actions_with_assign_set;
2181: chunk in number) is
2182:
2183: l_session_date date;
2184: l_year number ;
2185: l_gre_id pay_assignment_actions.tax_unit_id%type;
2186: l_org_id per_assignments_f.organization_id%type;
2187: l_loc_id per_assignments_f.location_id%type;
2188: l_per_id per_assignments_f.person_id%type;
2189: l_ssn per_people_f.national_identifier%type;
2308: ) is
2309:
2310: l_dt date;
2311: l_year number ;
2312: l_gre_id pay_assignment_actions.tax_unit_id%type;
2313: l_org_id per_assignments_f.organization_id%type;
2314: l_loc_id per_assignments_f.location_id%type;
2315: l_per_id per_assignments_f.person_id%type;
2316: l_ssn per_people_f.national_identifier%type;
2384:
2385: sqlstr :=
2386: 'select mt.rowid
2387: from hr_organization_units hou, hr_locations_all hl, per_periods_of_service pps,
2388: per_assignments_f paf, pay_assignment_actions mt
2389: where mt.payroll_action_id = :pactid and paf.assignment_id = mt.assignment_id
2390: and paf.effective_start_date = (select max(paf2.effective_start_date)
2391: from per_assignments_f paf2
2392: where paf2.assignment_id = paf.assignment_id