[Home] [Help]
37: select /*+ USE_NL(locked_pact locked locking locks)*/
38: max(date_earned)
39: into l_date_earned
40: from pay_payroll_actions locked_pact,
41: pay_assignment_actions locked,
42: pay_assignment_actions locking,
43: pay_action_interlocks locks
44: where locking.payroll_action_id = p_payroll_action_id
45: and locking.assignment_action_id = locks.locking_action_id
38: max(date_earned)
39: into l_date_earned
40: from pay_payroll_actions locked_pact,
41: pay_assignment_actions locked,
42: pay_assignment_actions locking,
43: pay_action_interlocks locks
44: where locking.payroll_action_id = p_payroll_action_id
45: and locking.assignment_action_id = locks.locking_action_id
46: and locked.assignment_action_id = locks.locked_action_id
1135: p_asg_id number,
1136: p_pact_id number)
1137: is
1138: select paa.assignment_action_id
1139: from pay_assignment_actions paa
1140: where paa.source_action_id = p_run_act
1141: and paa.assignment_id = p_asg_id
1142: and paa.payroll_action_id = p_pact_id
1143: and not exists (select ''
1150: is
1151: select paa.assignment_action_id
1152: from pay_action_classifications pcl,
1153: pay_payroll_actions pac,
1154: pay_assignment_actions paa,
1155: pay_action_interlocks pai
1156: where pai.locked_action_id = p_run_act
1157: and pai.locking_action_id = paa.assignment_action_id
1158: and paa.assignment_action_id <> p_cost_act
1215: select paa.assignment_action_id,
1216: paa.assignment_id,
1217: paa.payroll_action_id
1218: from pay_action_interlocks pai,
1219: pay_assignment_actions paa
1220: where pai.locking_action_id = p_act
1221: and pai.locked_action_id = paa.assignment_action_id
1222: and paa.source_action_id is null;
1223: --
1257: /*
1258: NAME
1259: insact - insert assignment action row.
1260: DESCRIPTION
1261: inserts row into pay_assignment_actions. Does not commit.
1262: NOTES
1263:
1264: */
1265: procedure insact
1348: object_type,
1349: pactid,
1350: status,
1351: chunk,
1352: pay_assignment_actions_s.nextval,
1353: 1
1354: from dual;
1355: else
1356: insert into pay_assignment_actions (
1352: pay_assignment_actions_s.nextval,
1353: 1
1354: from dual;
1355: else
1356: insert into pay_assignment_actions (
1357: assignment_action_id,
1358: assignment_id,
1359: payroll_action_id,
1360: action_status,
1372: assignid,
1373: pactid,
1374: status,
1375: chunk,
1376: pay_assignment_actions_s.nextval,
1377: prepayid,
1378: 1,
1379: greid,
1380: source_act,
1418: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
1419: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1420: INDEX(as1 PER_ASSIGNMENTS_N4)
1421: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1422: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1423: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1424: USE_NL(pop pos ppp opm as1 act as2) */
1425: act.assignment_action_id,
1426: act.assignment_id,
1431: pay_action_classifications pcl,
1432: pay_population_ranges pop,
1433: per_periods_of_service pos,
1434: per_all_assignments_f as1,
1435: pay_assignment_actions act,
1436: per_all_assignments_f as2,
1437: pay_pre_payments ppp,
1438: pay_org_payment_methods_f opm
1439: where pa1.payroll_action_id = pactid
1464: and (opm.org_payment_method_id = pa1.org_payment_method_id
1465: or pa1.org_payment_method_id is null)
1466: and not exists (
1467: select null
1468: from pay_assignment_actions ac2,
1469: pay_action_interlocks int
1470: where int.locked_action_id = act.assignment_action_id
1471: and ac2.assignment_action_id = int.locking_action_id
1472: and ac2.pre_payment_id = ppp.pre_payment_id)
1472: and ac2.pre_payment_id = ppp.pre_payment_id)
1473: and not exists (
1474: select null
1475: from per_all_assignments_f as3,
1476: pay_assignment_actions ac3
1477: where itpflg = 'N'
1478: and ac3.payroll_action_id = pa2.payroll_action_id
1479: and ac3.action_status not in ('C', 'S')
1480: and as3.assignment_id = ac3.assignment_id
1495: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
1496: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1497: INDEX(as1 PER_ASSIGNMENTS_N4)
1498: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1499: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1500: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1501: USE_NL(pos ppp opm as1 act as2) */
1502: act.assignment_action_id,
1503: act.assignment_id,
1507: pay_payroll_actions pa2,
1508: pay_action_classifications pcl,
1509: per_periods_of_service pos,
1510: per_all_assignments_f as1,
1511: pay_assignment_actions act,
1512: per_all_assignments_f as2,
1513: pay_pre_payments ppp,
1514: pay_org_payment_methods_f opm
1515: where pa1.payroll_action_id = pactid
1538: and (opm.org_payment_method_id = pa1.org_payment_method_id
1539: or pa1.org_payment_method_id is null)
1540: and not exists (
1541: select null
1542: from pay_assignment_actions ac2,
1543: pay_action_interlocks int
1544: where int.locked_action_id = act.assignment_action_id
1545: and ac2.assignment_action_id = int.locking_action_id
1546: and ac2.pre_payment_id = ppp.pre_payment_id)
1546: and ac2.pre_payment_id = ppp.pre_payment_id)
1547: and not exists (
1548: select null
1549: from per_all_assignments_f as3,
1550: pay_assignment_actions ac3
1551: where itpflg = 'N'
1552: and ac3.payroll_action_id = pa2.payroll_action_id
1553: and ac3.action_status not in ('C', 'S')
1554: and as3.assignment_id = ac3.assignment_id
1568: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
1569: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1570: INDEX(as1 PER_ASSIGNMENTS_N4)
1571: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1572: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1573: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1574: USE_NL(pos pop ppp opm as1 act as2) */
1575: act.assignment_action_id,
1576: act.assignment_id,
1579: from pay_payroll_actions pa1,
1580: pay_population_ranges pop,
1581: per_periods_of_service pos,
1582: per_all_assignments_f as1,
1583: pay_assignment_actions act,
1584: pay_payroll_actions pa2,
1585: pay_action_classifications pcl,
1586: per_all_assignments_f as2,
1587: pay_pre_payments ppp,
1614: and (opm.org_payment_method_id = pa1.org_payment_method_id
1615: or pa1.org_payment_method_id is null)
1616: and not exists (
1617: select null
1618: from pay_assignment_actions ac2,
1619: pay_action_interlocks int
1620: where int.locked_action_id = act.assignment_action_id
1621: and ac2.assignment_action_id = int.locking_action_id
1622: and ac2.pre_payment_id = ppp.pre_payment_id)
1622: and ac2.pre_payment_id = ppp.pre_payment_id)
1623: and not exists (
1624: select null
1625: from per_all_assignments_f as3,
1626: pay_assignment_actions ac3
1627: where itpflg = 'N'
1628: and ac3.payroll_action_id = pa2.payroll_action_id
1629: and ac3.action_status not in ('C', 'S')
1630: and as3.assignment_id = ac3.assignment_id
1662: --
1663: -- want to insert an assignment action for each of the
1664: -- rows that we return from the cursor, i.e. one for
1665: -- each assignment/pre-payment.
1666: select pay_assignment_actions_s.nextval
1667: into lockingactid
1668: from dual;
1669: --
1670: -- insert the action record.
1716: pay_payroll_actions pa2,
1717: pay_action_classifications pcl,
1718: per_periods_of_service pos,
1719: per_all_assignments_f as1,
1720: pay_assignment_actions act
1721: where pa1.payroll_action_id = pactid
1722: and pa2.effective_date between
1723: pa1.start_date and pa1.effective_date
1724: and pa2.consolidation_set_id = pa1.consolidation_set_id
1749: and not exists (
1750: select /*+ ORDERED*/
1751: null
1752: from per_all_assignments_f as3,
1753: pay_assignment_actions ac3
1754: where itpflg = 'N'
1755: and ac3.payroll_action_id = pa2.payroll_action_id
1756: and ac3.action_status not in ( 'C', 'S')
1757: and as3.assignment_id = ac3.assignment_id
1780: pay_action_classifications pcl,
1781: pay_population_ranges pop,
1782: per_periods_of_service pos,
1783: per_all_assignments_f as1,
1784: pay_assignment_actions act
1785: where pa1.payroll_action_id = pactid
1786: and pa2.effective_date between
1787: pa1.start_date and pa1.effective_date
1788: and pa2.consolidation_set_id = pa1.consolidation_set_id
1815: and not exists (
1816: select /*+ ORDERED*/
1817: null
1818: from per_all_assignments_f as3,
1819: pay_assignment_actions ac3
1820: where itpflg = 'N'
1821: and ac3.payroll_action_id = pa2.payroll_action_id
1822: and ac3.action_status not in ( 'C', 'S')
1823: and as3.assignment_id = ac3.assignment_id
1865: /* process the insert of assignment actions */
1866: /* logic prevents more than one action per assignment */
1867: if(prev_assignid is null OR prev_assignid <> assignid) then
1868: -- get a value for the action id that is locking.
1869: select pay_assignment_actions_s.nextval
1870: into lockingactid
1871: from dual;
1872: --
1873: -- insert into pay_assignment_actions.
1869: select pay_assignment_actions_s.nextval
1870: into lockingactid
1871: from dual;
1872: --
1873: -- insert into pay_assignment_actions.
1874: insact(lockingactid,assignid,pactid,rand_chunk,greid);
1875: end if;
1876: --
1877: -- insert into interlocks table.
1946: and not exists (
1947: select /*+ ORDERED*/
1948: null
1949: from
1950: pay_assignment_actions ac2
1951: where ac2.pre_payment_id = ppp.pre_payment_id
1952: )
1953: order by hou.organization_id, ppp.pre_payment_id
1954: for update of hou.organization_id;
1954: for update of hou.organization_id;
1955: --
1956: l_prepayid pay_pre_payments.pre_payment_id%type;
1957: prev_prepayid pay_pre_payments.pre_payment_id%type;
1958: lockedactid pay_assignment_actions.assignment_action_id%type;
1959: lockingactid pay_assignment_actions.assignment_action_id%type;
1960: orgid hr_organization_units.organization_id%type;
1961: l_cp number;
1962: --
1955: --
1956: l_prepayid pay_pre_payments.pre_payment_id%type;
1957: prev_prepayid pay_pre_payments.pre_payment_id%type;
1958: lockedactid pay_assignment_actions.assignment_action_id%type;
1959: lockingactid pay_assignment_actions.assignment_action_id%type;
1960: orgid hr_organization_units.organization_id%type;
1961: l_cp number;
1962: --
1963: begin
2003: /* process the insert of assignment actions */
2004: /* logic prevents more than one action per assignment */
2005: if(prev_prepayid is null OR prev_prepayid <> l_prepayid) then
2006: -- get a value for the action id that is locking.
2007: select pay_assignment_actions_s.nextval
2008: into lockingactid
2009: from dual;
2010: --
2011: -- insert into pay_assignment_actions.
2007: select pay_assignment_actions_s.nextval
2008: into lockingactid
2009: from dual;
2010: --
2011: -- insert into pay_assignment_actions.
2012: insact(lockingactid => lockingactid,
2013: pactid => pactid,
2014: chunk => rand_chunk,
2015: prepayid => l_prepayid,
2069: pay_action_classifications pcl,
2070: pay_population_ranges pop,
2071: per_periods_of_service pos,
2072: per_all_assignments_f as1,
2073: pay_assignment_actions act,
2074: pay_pre_payments ppp,
2075: per_all_assignments_f as2,
2076: pay_org_payment_methods_f opm
2077: where pa1.payroll_action_id = pactid
2105: and not exists (
2106: select /*+ ORDERED*/
2107: null
2108: from pay_action_interlocks int,
2109: pay_assignment_actions ac2
2110: where int.locked_action_id = act.assignment_action_id
2111: and ac2.assignment_action_id = int.locking_action_id
2112: and ac2.pre_payment_id = ppp.pre_payment_id
2113: and not exists (
2111: and ac2.assignment_action_id = int.locking_action_id
2112: and ac2.pre_payment_id = ppp.pre_payment_id
2113: and not exists (
2114: select null
2115: from pay_assignment_actions paa_void,
2116: pay_action_interlocks pai_void,
2117: pay_payroll_actions ppa_void
2118: where pai_void.locked_action_id = ac2.assignment_action_id
2119: and pai_void.locking_action_id = paa_void.assignment_action_id
2123: and not exists (
2124: select /*+ ORDERED*/
2125: null
2126: from per_all_assignments_f as3,
2127: pay_assignment_actions ac3
2128: where itpflg = 'N'
2129: and ac3.payroll_action_id = pa2.payroll_action_id
2130: and ac3.action_status not in ('C', 'S')
2131: and as3.assignment_id = ac3.assignment_id
2160: pay_payroll_actions pa2,
2161: pay_action_classifications pcl,
2162: per_periods_of_service pos,
2163: per_all_assignments_f as1,
2164: pay_assignment_actions act,
2165: pay_pre_payments ppp,
2166: per_all_assignments_f as2,
2167: pay_org_payment_methods_f opm
2168: where pa1.payroll_action_id = pactid
2194: and not exists (
2195: select /*+ ORDERED*/
2196: null
2197: from pay_action_interlocks int,
2198: pay_assignment_actions ac2
2199: where int.locked_action_id = act.assignment_action_id
2200: and ac2.assignment_action_id = int.locking_action_id
2201: and ac2.pre_payment_id = ppp.pre_payment_id
2202: and not exists (
2200: and ac2.assignment_action_id = int.locking_action_id
2201: and ac2.pre_payment_id = ppp.pre_payment_id
2202: and not exists (
2203: select null
2204: from pay_assignment_actions paa_void,
2205: pay_action_interlocks pai_void,
2206: pay_payroll_actions ppa_void
2207: where pai_void.locked_action_id = ac2.assignment_action_id
2208: and pai_void.locking_action_id = paa_void.assignment_action_id
2212: and not exists (
2213: select /*+ ORDERED*/
2214: null
2215: from per_all_assignments_f as3,
2216: pay_assignment_actions ac3
2217: where itpflg = 'N'
2218: and ac3.payroll_action_id = pa2.payroll_action_id
2219: and ac3.action_status not in ('C', 'S')
2220: and as3.assignment_id = ac3.assignment_id
2235: SELECT /*+ ORDERED
2236: index(pa2 PAY_PAYROLL_ACTIONS_PK)
2237: index(pos PER_PERIODS_OF_SERVICE_N3)
2238: index(as1 PER_ASSIGNMENTS_F_N4)
2239: index(act PAY_ASSIGNMENT_ACTIONS_N51)
2240: index(as2 PER_ASSIGNMENTS_F_PK)
2241: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2242: USE_NL(pos pop act pa2 as1 as2) */
2243: act.assignment_action_id,
2249: from pay_payroll_actions pa1,
2250: pay_population_ranges pop,
2251: per_periods_of_service pos,
2252: per_all_assignments_f as1,
2253: pay_assignment_actions act,
2254: pay_payroll_actions pa2,
2255: pay_action_classifications pcl,
2256: pay_pre_payments ppp,
2257: per_all_assignments_f as2,
2287: and not exists (
2288: select /*+ ORDERED*/
2289: null
2290: from pay_action_interlocks int,
2291: pay_assignment_actions ac2
2292: where int.locked_action_id = act.assignment_action_id
2293: and ac2.assignment_action_id = int.locking_action_id
2294: and ac2.pre_payment_id = ppp.pre_payment_id
2295: and not exists (
2293: and ac2.assignment_action_id = int.locking_action_id
2294: and ac2.pre_payment_id = ppp.pre_payment_id
2295: and not exists (
2296: select null
2297: from pay_assignment_actions paa_void,
2298: pay_action_interlocks pai_void,
2299: pay_payroll_actions ppa_void
2300: where pai_void.locked_action_id = ac2.assignment_action_id
2301: and pai_void.locking_action_id = paa_void.assignment_action_id
2305: and not exists (
2306: select /*+ ORDERED*/
2307: null
2308: from per_all_assignments_f as3,
2309: pay_assignment_actions ac3
2310: where itpflg = 'N'
2311: and ac3.payroll_action_id = pa2.payroll_action_id
2312: and ac3.action_status not in ('C', 'S')
2313: and as3.assignment_id = ac3.assignment_id
2348: -- Ensure there exists a voided check for this payment.
2349: and exists
2350: (select 1
2351: from pay_action_interlocks lck1,
2352: pay_assignment_actions chk_paa,
2353: pay_payroll_actions chk_ppa,
2354: pay_action_interlocks lck2,
2355: pay_assignment_actions vd_paa,
2356: pay_payroll_actions vd_ppa
2351: from pay_action_interlocks lck1,
2352: pay_assignment_actions chk_paa,
2353: pay_payroll_actions chk_ppa,
2354: pay_action_interlocks lck2,
2355: pay_assignment_actions vd_paa,
2356: pay_payroll_actions vd_ppa
2357: where lck1.locked_action_id = plockedid
2358: and lck1.locking_action_id = chk_paa.assignment_action_id
2359: and chk_paa.payroll_action_id = chk_ppa.payroll_action_id
2415: if inasgset then
2416: -- we need to insert one action for each of the
2417: -- rows that we return from the cursor (i.e. one
2418: -- for each assignment/pre-payment).
2419: select pay_assignment_actions_s.nextval
2420: into lockingactid
2421: from dual;
2422: --
2423: -- insert the action record.
2483: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2484: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2485: INDEX(as1 PER_ASSIGNMENTS_N4)
2486: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2487: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2488: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2489: USE_NL(pop pos ppp opm as1 act as2) */
2490: act.assignment_action_id,
2491: act.assignment_id,
2496: pay_action_classifications pcl,
2497: pay_population_ranges pop,
2498: per_periods_of_service pos,
2499: per_all_assignments_f as1,
2500: pay_assignment_actions act,
2501: per_all_assignments_f as2,
2502: pay_pre_payments ppp,
2503: pay_org_payment_methods_f opm
2504: where pa1.payroll_action_id = pactid
2530: or pa1.org_payment_method_id is null)
2531: and not exists (
2532: select null
2533: from per_all_assignments_f as3,
2534: pay_assignment_actions ac3
2535: where itpflg = 'N'
2536: and ac3.payroll_action_id = pa2.payroll_action_id
2537: and ac3.action_status not in ('C', 'S')
2538: and as3.assignment_id = ac3.assignment_id
2542: and not exists (
2543: select /*+ ORDERED*/
2544: null
2545: from pay_action_interlocks int,
2546: pay_assignment_actions ac2
2547: where int.locked_action_id = act.assignment_action_id
2548: and ac2.assignment_action_id = int.locking_action_id
2549: and ac2.pre_payment_id = ppp.pre_payment_id
2550: and not exists (
2548: and ac2.assignment_action_id = int.locking_action_id
2549: and ac2.pre_payment_id = ppp.pre_payment_id
2550: and not exists (
2551: select null
2552: from pay_assignment_actions paa_void,
2553: pay_action_interlocks pai_void,
2554: pay_payroll_actions ppa_void
2555: where pai_void.locked_action_id = ac2.assignment_action_id
2556: and pai_void.locking_action_id = paa_void.assignment_action_id
2572: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2573: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2574: INDEX(as1 PER_ASSIGNMENTS_N4)
2575: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2576: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2577: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2578: USE_NL(pos ppp opm as1 act as2) */
2579: act.assignment_action_id,
2580: act.assignment_id,
2584: pay_payroll_actions pa2,
2585: pay_action_classifications pcl,
2586: per_periods_of_service pos,
2587: per_all_assignments_f as1,
2588: pay_assignment_actions act,
2589: per_all_assignments_f as2,
2590: pay_pre_payments ppp,
2591: pay_org_payment_methods_f opm
2592: where pa1.payroll_action_id = pactid
2616: or pa1.org_payment_method_id is null)
2617: and not exists (
2618: select null
2619: from per_all_assignments_f as3,
2620: pay_assignment_actions ac3
2621: where itpflg = 'N'
2622: and ac3.payroll_action_id = pa2.payroll_action_id
2623: and ac3.action_status not in ('C', 'S')
2624: and as3.assignment_id = ac3.assignment_id
2628: and not exists (
2629: select /*+ ORDERED*/
2630: null
2631: from pay_action_interlocks int,
2632: pay_assignment_actions ac2
2633: where int.locked_action_id = act.assignment_action_id
2634: and ac2.assignment_action_id = int.locking_action_id
2635: and ac2.pre_payment_id = ppp.pre_payment_id
2636: and not exists (
2634: and ac2.assignment_action_id = int.locking_action_id
2635: and ac2.pre_payment_id = ppp.pre_payment_id
2636: and not exists (
2637: select null
2638: from pay_assignment_actions paa_void,
2639: pay_action_interlocks pai_void,
2640: pay_payroll_actions ppa_void
2641: where pai_void.locked_action_id = ac2.assignment_action_id
2642: and pai_void.locking_action_id = paa_void.assignment_action_id
2657: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
2658: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2659: INDEX(as1 PER_ASSIGNMENTS_N4)
2660: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2661: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2662: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2663: USE_NL(pos pop ppp opm as1 act as2) */
2664: act.assignment_action_id,
2665: act.assignment_id,
2668: from pay_payroll_actions pa1,
2669: pay_population_ranges pop,
2670: per_periods_of_service pos,
2671: per_all_assignments_f as1,
2672: pay_assignment_actions act,
2673: pay_payroll_actions pa2,
2674: pay_action_classifications pcl,
2675: per_all_assignments_f as2,
2676: pay_pre_payments ppp,
2704: or pa1.org_payment_method_id is null)
2705: and not exists (
2706: select null
2707: from per_all_assignments_f as3,
2708: pay_assignment_actions ac3
2709: where itpflg = 'N'
2710: and ac3.payroll_action_id = pa2.payroll_action_id
2711: and ac3.action_status not in ('C', 'S')
2712: and as3.assignment_id = ac3.assignment_id
2716: and not exists (
2717: select /*+ ORDERED*/
2718: null
2719: from pay_action_interlocks int,
2720: pay_assignment_actions ac2
2721: where int.locked_action_id = act.assignment_action_id
2722: and ac2.assignment_action_id = int.locking_action_id
2723: and ac2.pre_payment_id = ppp.pre_payment_id
2724: and not exists (
2722: and ac2.assignment_action_id = int.locking_action_id
2723: and ac2.pre_payment_id = ppp.pre_payment_id
2724: and not exists (
2725: select null
2726: from pay_assignment_actions paa_void,
2727: pay_action_interlocks pai_void,
2728: pay_payroll_actions ppa_void
2729: where pai_void.locked_action_id = ac2.assignment_action_id
2730: and pai_void.locking_action_id = paa_void.assignment_action_id
2765: --
2766: -- we need to insert one action for each of the
2767: -- rows that we return from the cursor (i.e. one
2768: -- for each assignment/pre-payment).
2769: select pay_assignment_actions_s.nextval
2770: into lockingactid
2771: from dual;
2772: --
2773: -- insert the action record.
2843: pay_action_classifications pcl,
2844: pay_population_ranges pop,
2845: per_periods_of_service pos,
2846: per_all_assignments_f as1,
2847: pay_assignment_actions act,
2848: per_all_assignments_f as2
2849: where pa1.payroll_action_id = pactid
2850: and pa2.payroll_id = pa1.payroll_id
2851: and pa2.effective_date between
2869: and pos.person_id = pop.person_id
2870: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2871: and not exists (
2872: select null
2873: from pay_assignment_actions ac2,
2874: pay_payroll_actions pa3,
2875: pay_action_interlocks int
2876: where int.locked_action_id = act.assignment_action_id
2877: and ac2.assignment_action_id = int.locking_action_id
2880: and not exists (
2881: select /*+ ORDERED*/
2882: null
2883: from per_all_assignments_f as3,
2884: pay_assignment_actions ac3
2885: where itpflg = 'N'
2886: and ac3.payroll_action_id = pa2.payroll_action_id
2887: and ac3.action_status not in ( 'C', 'S')
2888: and as3.assignment_id = ac3.assignment_id
2915: pay_payroll_actions pa2,
2916: pay_action_classifications pcl,
2917: per_periods_of_service pos,
2918: per_all_assignments_f as1,
2919: pay_assignment_actions act,
2920: per_all_assignments_f as2
2921: where pa1.payroll_action_id = pactid
2922: and pa2.payroll_id = pa1.payroll_id
2923: and pa2.effective_date between
2939: and pos.person_id between stperson and endperson
2940: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2941: and not exists (
2942: select null
2943: from pay_assignment_actions ac2,
2944: pay_payroll_actions pa3,
2945: pay_action_interlocks int
2946: where int.locked_action_id = act.assignment_action_id
2947: and ac2.assignment_action_id = int.locking_action_id
2950: and not exists (
2951: select /*+ ORDERED*/
2952: null
2953: from per_all_assignments_f as3,
2954: pay_assignment_actions ac3
2955: where itpflg = 'N'
2956: and ac3.payroll_action_id = pa2.payroll_action_id
2957: and ac3.action_status not in ( 'C', 'S')
2958: and as3.assignment_id = ac3.assignment_id
2971: ) is
2972: select /*+ ORDERED
2973: index(pa2 PAY_PAYROLL_ACTIONS_PK)
2974: index(pos PER_PERIODS_OF_SERVICE_N3)
2975: index(act PAY_ASSIGNMENT_ACTIONS_N51)
2976: index(as1 PER_ASSIGNMENTS_F_N4)
2977: index(as2 PER_ASSIGNMENTS_F_PK)
2978: USE_NL(pos pop act as1 as2 pa2) */
2979: act.assignment_action_id,
2985: from pay_payroll_actions pa1,
2986: pay_population_ranges pop,
2987: per_periods_of_service pos,
2988: per_all_assignments_f as1,
2989: pay_assignment_actions act,
2990: pay_payroll_actions pa2,
2991: pay_action_classifications pcl,
2992: per_all_assignments_f as2
2993: where pa1.payroll_action_id = pactid
3013: and pos.person_id = pop.person_id
3014: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3015: and not exists (
3016: select null
3017: from pay_assignment_actions ac2,
3018: pay_payroll_actions pa3,
3019: pay_action_interlocks int
3020: where int.locked_action_id = act.assignment_action_id
3021: and ac2.assignment_action_id = int.locking_action_id
3024: and not exists (
3025: select /*+ ORDERED*/
3026: null
3027: from per_all_assignments_f as3,
3028: pay_assignment_actions ac3
3029: where itpflg = 'N'
3030: and ac3.payroll_action_id = pa2.payroll_action_id
3031: and ac3.action_status not in ( 'C', 'S')
3032: and as3.assignment_id = ac3.assignment_id
3071: if (mult_asg_flag = 'Y')
3072: then
3073: -- insert master actions
3074: if (prev_person_id is null or prev_person_id <> person_id) then
3075: select pay_assignment_actions_s.nextval
3076: into lockingactid
3077: from dual;
3078:
3079: -- insert into pay_assignment_actions.
3075: select pay_assignment_actions_s.nextval
3076: into lockingactid
3077: from dual;
3078:
3079: -- insert into pay_assignment_actions.
3080: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3081:
3082: end if;
3083: -- insert interlocks
3089: /* process the insert of assignment actions */
3090: /* logic prevents more than one action per assignment */
3091: if(prev_assignid is null OR prev_assignid <> assignid) then
3092: -- get a value for the action id that is locking.
3093: select pay_assignment_actions_s.nextval
3094: into lockingactid
3095: from dual;
3096: --
3097: -- insert into pay_assignment_actions.
3093: select pay_assignment_actions_s.nextval
3094: into lockingactid
3095: from dual;
3096: --
3097: -- insert into pay_assignment_actions.
3098: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3099: end if;
3100: --
3101: -- insert into interlocks table.
3146: select /*+ ORDERED
3147: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3148: index(as1 PER_ASSIGNMENTS_F_N4)
3149: index(as2 PER_ASSIGNMENTS_F_PK)
3150: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3151: USE_NL(pos pop as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3152: act.assignment_action_id,
3153: act.assignment_id,
3154: act.tax_unit_id
3147: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3148: index(as1 PER_ASSIGNMENTS_F_N4)
3149: index(as2 PER_ASSIGNMENTS_F_PK)
3150: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3151: USE_NL(pos pop as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3152: act.assignment_action_id,
3153: act.assignment_id,
3154: act.tax_unit_id
3155: from pay_payroll_actions pa1,
3157: pay_action_classifications pcl,
3158: pay_population_ranges pop,
3159: per_periods_of_service pos,
3160: per_all_assignments_f as1,
3161: pay_assignment_actions act,
3162: per_all_assignments_f as2
3163: where pa1.payroll_action_id = pactid
3164: and pa2.consolidation_set_id = pa1.consolidation_set_id
3165: and pa2.effective_date between
3180: and pos.period_of_service_id = as1.period_of_service_id
3181: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3182: and not exists (
3183: select null
3184: from pay_assignment_actions ac2,
3185: pay_payroll_actions pa3,
3186: pay_action_interlocks int
3187: where int.locked_action_id = act.assignment_action_id
3188: and ac2.assignment_action_id = int.locking_action_id
3191: and not exists (
3192: select /*+ ORDERED*/
3193: null
3194: from per_all_assignments_f as3,
3195: pay_assignment_actions ac3
3196: where itpflg = 'N'
3197: and ac3.payroll_action_id = pa2.payroll_action_id
3198: and ac3.action_status not in ('C','S')
3199: and as3.assignment_id = ac3.assignment_id
3214: select /*+ ORDERED
3215: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3216: index(as1 PER_ASSIGNMENTS_F_N4)
3217: index(as2 PER_ASSIGNMENTS_F_PK)
3218: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3219: USE_NL(pos as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3220: act.assignment_action_id,
3221: act.assignment_id,
3222: act.tax_unit_id
3215: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3216: index(as1 PER_ASSIGNMENTS_F_N4)
3217: index(as2 PER_ASSIGNMENTS_F_PK)
3218: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3219: USE_NL(pos as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3220: act.assignment_action_id,
3221: act.assignment_id,
3222: act.tax_unit_id
3223: from pay_payroll_actions pa1,
3224: pay_payroll_actions pa2,
3225: pay_action_classifications pcl,
3226: per_periods_of_service pos,
3227: per_all_assignments_f as1,
3228: pay_assignment_actions act,
3229: per_all_assignments_f as2
3230: where pa1.payroll_action_id = pactid
3231: and pa2.consolidation_set_id = pa1.consolidation_set_id
3232: and pa2.effective_date between
3245: and pos.person_id between stperson and endperson
3246: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3247: and not exists (
3248: select null
3249: from pay_assignment_actions ac2,
3250: pay_payroll_actions pa3,
3251: pay_action_interlocks int
3252: where int.locked_action_id = act.assignment_action_id
3253: and ac2.assignment_action_id = int.locking_action_id
3256: and not exists (
3257: select /*+ ORDERED*/
3258: null
3259: from per_all_assignments_f as3,
3260: pay_assignment_actions ac3
3261: where itpflg = 'N'
3262: and ac3.payroll_action_id = pa2.payroll_action_id
3263: and ac3.action_status not in ('C','S')
3264: and as3.assignment_id = ac3.assignment_id
3278: select /*+ ORDERED
3279: index(pa2 PAY_PAYROLL_ACTIONS_PK)
3280: index(pos PER_PERIODS_OF_SERVICE_N3)
3281: index(as1 PER_ASSIGNMENTS_F_N4)
3282: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3283: index(as2 PER_ASSIGNMENTS_F_PK)
3284: USE_NL(pos pop act pa2 as2 as1) */
3285: act.assignment_action_id,
3286: act.assignment_id,
3288: from pay_payroll_actions pa1,
3289: pay_population_ranges pop,
3290: per_periods_of_service pos,
3291: per_all_assignments_f as1,
3292: pay_assignment_actions act,
3293: pay_payroll_actions pa2,
3294: pay_action_classifications pcl,
3295: per_all_assignments_f as2
3296: where pa1.payroll_action_id = pactid
3313: and pos.person_id = pop.person_id
3314: and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3315: and not exists (
3316: select null
3317: from pay_assignment_actions ac2,
3318: pay_payroll_actions pa3,
3319: pay_action_interlocks int
3320: where int.locked_action_id = act.assignment_action_id
3321: and ac2.assignment_action_id = int.locking_action_id
3324: and not exists (
3325: select /*+ ORDERED*/
3326: null
3327: from per_all_assignments_f as3,
3328: pay_assignment_actions ac3
3329: where itpflg = 'N'
3330: and ac3.payroll_action_id = pa2.payroll_action_id
3331: and ac3.action_status not in ('C','S')
3332: and as3.assignment_id = ac3.assignment_id
3366: /* process the insert of assignment actions */
3367: /* logic prevents more than one action per assignment */
3368: if(prev_assignid is null OR prev_assignid <> assignid) then
3369: -- get a value for the action id that is locking.
3370: select pay_assignment_actions_s.nextval
3371: into lockingactid
3372: from dual;
3373: --
3374: -- insert into pay_assignment_actions.
3370: select pay_assignment_actions_s.nextval
3371: into lockingactid
3372: from dual;
3373: --
3374: -- insert into pay_assignment_actions.
3375: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3376: end if;
3377: --
3378: -- insert into interlocks table.
3433: pay_action_classifications pcl,
3434: pay_population_ranges pop,
3435: per_periods_of_service pos,
3436: per_all_assignments_f as1,
3437: pay_assignment_actions act,
3438: per_all_assignments_f as2
3439: where pa1.payroll_action_id = pactid
3440: and pa2.consolidation_set_id = pa1.consolidation_set_id
3441: and pa2.effective_date between
3456: and pos.period_of_service_id = as1.period_of_service_id
3457: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3458: and not exists (
3459: select null
3460: from pay_assignment_actions ac2,
3461: pay_payroll_actions pa3,
3462: pay_action_interlocks int
3463: where int.locked_action_id = act.assignment_action_id
3464: and ac2.assignment_action_id = int.locking_action_id
3467: and not exists (
3468: select /*+ ORDERED*/
3469: null
3470: from per_all_assignments_f as3,
3471: pay_assignment_actions ac3
3472: where itpflg = 'N'
3473: and ac3.payroll_action_id = pa2.payroll_action_id
3474: and ac3.action_status not in ('C','S')
3475: and as3.assignment_id = ac3.assignment_id
3514: or (pa2.action_type = 'D'
3515: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3516: and exists (select 1
3517: from pay_action_interlocks int,
3518: pay_assignment_actions chq,
3519: pay_payroll_actions pcq,
3520: pay_pre_payments ppp,
3521: pay_org_payment_methods_f pom,
3522: pay_ce_reconciled_payments crp
3532: pom.effective_start_date and pom.effective_end_date))
3533: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3534: and exists (select 1
3535: from pay_action_interlocks int,
3536: pay_assignment_actions chq,
3537: pay_payroll_actions pcq,
3538: pay_pre_payments ppp,
3539: pay_org_payment_methods_f pom
3540: where int.locking_action_id = act.assignment_action_id
3571: pay_payroll_actions pa2,
3572: pay_action_classifications pcl,
3573: per_periods_of_service pos,
3574: per_all_assignments_f as1,
3575: pay_assignment_actions act,
3576: per_all_assignments_f as2
3577: where pa1.payroll_action_id = pactid
3578: and pa2.consolidation_set_id = pa1.consolidation_set_id
3579: and pa2.effective_date between
3592: and pos.person_id between stperson and endperson
3593: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3594: and not exists (
3595: select null
3596: from pay_assignment_actions ac2,
3597: pay_payroll_actions pa3,
3598: pay_action_interlocks int
3599: where int.locked_action_id = act.assignment_action_id
3600: and ac2.assignment_action_id = int.locking_action_id
3603: and not exists (
3604: select /*+ ORDERED*/
3605: null
3606: from per_all_assignments_f as3,
3607: pay_assignment_actions ac3
3608: where itpflg = 'N'
3609: and ac3.payroll_action_id = pa2.payroll_action_id
3610: and ac3.action_status not in ('C','S')
3611: and as3.assignment_id = ac3.assignment_id
3650: or (pa2.action_type = 'D'
3651: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3652: and exists (select 1
3653: from pay_action_interlocks int,
3654: pay_assignment_actions chq,
3655: pay_payroll_actions pcq,
3656: pay_pre_payments ppp,
3657: pay_org_payment_methods_f pom,
3658: pay_ce_reconciled_payments crp
3668: pom.effective_start_date and pom.effective_end_date))
3669: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3670: and exists (select 1
3671: from pay_action_interlocks int,
3672: pay_assignment_actions chq,
3673: pay_payroll_actions pcq,
3674: pay_pre_payments ppp,
3675: pay_org_payment_methods_f pom
3676: where int.locking_action_id = act.assignment_action_id
3696: select /*+ ORDERED
3697: index(pa2 PAY_PAYROLL_ACTIONS_PK)
3698: index(pos PER_PERIODS_OF_SERVICE_N3)
3699: index(as1 PER_ASSIGNMENTS_F_N4)
3700: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3701: index(as2 PER_ASSIGNMENTS_F_PK)
3702: USE_NL(pos pop act pa2 as2 as1) */
3703: act.assignment_action_id,
3704: act.assignment_id,
3707: from pay_payroll_actions pa1,
3708: pay_population_ranges pop,
3709: per_periods_of_service pos,
3710: per_all_assignments_f as1,
3711: pay_assignment_actions act,
3712: pay_payroll_actions pa2,
3713: pay_action_classifications pcl,
3714: per_all_assignments_f as2
3715: where pa1.payroll_action_id = pactid
3732: and pos.person_id = pop.person_id
3733: and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3734: and not exists (
3735: select null
3736: from pay_assignment_actions ac2,
3737: pay_payroll_actions pa3,
3738: pay_action_interlocks int
3739: where int.locked_action_id = act.assignment_action_id
3740: and ac2.assignment_action_id = int.locking_action_id
3743: and not exists (
3744: select /*+ ORDERED*/
3745: null
3746: from per_all_assignments_f as3,
3747: pay_assignment_actions ac3
3748: where itpflg = 'N'
3749: and ac3.payroll_action_id = pa2.payroll_action_id
3750: and ac3.action_status not in ('C','S')
3751: and as3.assignment_id = ac3.assignment_id
3790: or (pa2.action_type = 'D'
3791: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3792: and exists (select 1
3793: from pay_action_interlocks int,
3794: pay_assignment_actions chq,
3795: pay_payroll_actions pcq,
3796: pay_pre_payments ppp,
3797: pay_org_payment_methods_f pom,
3798: pay_ce_reconciled_payments crp
3808: pom.effective_start_date and pom.effective_end_date))
3809: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3810: and exists (select 1
3811: from pay_action_interlocks int,
3812: pay_assignment_actions chq,
3813: pay_payroll_actions pcq,
3814: pay_pre_payments ppp,
3815: pay_org_payment_methods_f pom
3816: where int.locking_action_id = act.assignment_action_id
3860: /* logic prevents more than one action per assignment */
3861: if(prev_assignid is null OR prev_assignid <> assignid OR
3862: prev_pactid <> lpactid) then
3863: -- get a value for the action id that is locking.
3864: select pay_assignment_actions_s.nextval
3865: into lockingactid
3866: from dual;
3867: --
3868: -- insert into pay_assignment_actions.
3864: select pay_assignment_actions_s.nextval
3865: into lockingactid
3866: from dual;
3867: --
3868: -- insert into pay_assignment_actions.
3869: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3870: end if;
3871: --
3872: -- insert into interlocks table.
3929: pay_action_classifications pcl,
3930: pay_population_ranges pop,
3931: per_periods_of_service pos,
3932: per_all_assignments_f as1,
3933: pay_assignment_actions act,
3934: per_all_assignments_f as2
3935: where pa1.payroll_action_id = pactid
3936: and pay.consolidation_set_id = pa1.consolidation_set_id
3937: and pa1.effective_date between
3961: and not exists (
3962: select /*+ ORDERED*/
3963: null
3964: from per_all_assignments_f as3,
3965: pay_assignment_actions ac3
3966: where itpflg = 'N'
3967: and ac3.payroll_action_id = pa2.payroll_action_id
3968: and ac3.action_status not in ('C','S')
3969: and as3.assignment_id = ac3.assignment_id
3995: pay_payroll_actions pa2,
3996: pay_action_classifications pcl,
3997: per_periods_of_service pos,
3998: per_all_assignments_f as1,
3999: pay_assignment_actions act,
4000: per_all_assignments_f as2
4001: where pa1.payroll_action_id = pactid
4002: and pay.consolidation_set_id = pa1.consolidation_set_id
4003: and pa1.effective_date between
4025: and not exists (
4026: select /*+ ORDERED*/
4027: null
4028: from per_all_assignments_f as3,
4029: pay_assignment_actions ac3
4030: where itpflg = 'N'
4031: and ac3.payroll_action_id = pa2.payroll_action_id
4032: and ac3.action_status not in ('C','S')
4033: and as3.assignment_id = ac3.assignment_id
4062: /* process the insert of assignment actions */
4063: /* logic prevents more than one action per assignment */
4064: if(prev_assignid is null OR prev_assignid <> assignid) then
4065: -- get a value for the action id that is locking.
4066: select pay_assignment_actions_s.nextval
4067: into lockingactid
4068: from dual;
4069: --
4070: -- insert into pay_assignment_actions.
4066: select pay_assignment_actions_s.nextval
4067: into lockingactid
4068: from dual;
4069: --
4070: -- insert into pay_assignment_actions.
4071: insact(lockingactid,assignid,pactid,rand_chunk,greid);
4072: end if;
4073: --
4074: prev_assignid := assignid;
4167: exit when beecur%notfound;
4168: end if;
4169: --
4170: -- Get an assignment_action_id.
4171: select pay_assignment_actions_s.nextval
4172: into asgactid
4173: from dual;
4174: --
4175: if preasgid is null or preasgid <> assignid then
4221: select /*+ ORDERED
4222: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4223: index(as1 PER_ASSIGNMENTS_F_PK)
4224: index(as2 PER_ASSIGNMENTS_F_N4)
4225: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4226: USE_NL(pop pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4227: ac2.assignment_action_id,
4228: ac2.assignment_id,
4229: ac2.tax_unit_id,
4222: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4223: index(as1 PER_ASSIGNMENTS_F_PK)
4224: index(as2 PER_ASSIGNMENTS_F_N4)
4225: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4226: USE_NL(pop pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4227: ac2.assignment_action_id,
4228: ac2.assignment_id,
4229: ac2.tax_unit_id,
4230: pa2.action_type
4233: pay_action_classifications pcl,
4234: pay_population_ranges pop,
4235: per_periods_of_service pos,
4236: per_all_assignments_f as2,
4237: pay_assignment_actions ac2,
4238: per_all_assignments_f as1
4239: where pa.payroll_action_id = pactid
4240: and pa2.consolidation_set_id = pa.consolidation_set_id
4241: and pa2.effective_date between
4258: and pop.chunk_number = chunk
4259: and pos.person_id = pop.person_id
4260: and not exists (
4261: select null
4262: from pay_assignment_actions ac3,
4263: pay_payroll_actions pa3,
4264: pay_action_interlocks in3
4265: where in3.locked_action_id = ac2.assignment_action_id
4266: and ac3.assignment_action_id = in3.locking_action_id
4269: and not exists (
4270: select /*+ ORDERED*/
4271: null
4272: from per_all_assignments_f as3,
4273: pay_assignment_actions ac3
4274: where itpflg = 'N'
4275: and ac3.payroll_action_id = pa2.payroll_action_id
4276: and ac3.action_status not in ('C','S')
4277: and as3.assignment_id = ac3.assignment_id
4291: select /*+ ORDERED
4292: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4293: index(as1 PER_ASSIGNMENTS_F_PK)
4294: index(as2 PER_ASSIGNMENTS_F_N4)
4295: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4296: USE_NL(pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4297: ac2.assignment_action_id,
4298: ac2.assignment_id,
4299: ac2.tax_unit_id,
4292: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4293: index(as1 PER_ASSIGNMENTS_F_PK)
4294: index(as2 PER_ASSIGNMENTS_F_N4)
4295: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4296: USE_NL(pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4297: ac2.assignment_action_id,
4298: ac2.assignment_id,
4299: ac2.tax_unit_id,
4300: pa2.action_type
4302: pay_payroll_actions pa2,
4303: pay_action_classifications pcl,
4304: per_periods_of_service pos,
4305: per_all_assignments_f as2,
4306: pay_assignment_actions ac2,
4307: per_all_assignments_f as1
4308: where pa.payroll_action_id = pactid
4309: and pa2.consolidation_set_id = pa.consolidation_set_id
4310: and pa2.effective_date between
4326: and pos.person_id between
4327: stperson and endperson
4328: and not exists (
4329: select null
4330: from pay_assignment_actions ac3,
4331: pay_payroll_actions pa3,
4332: pay_action_interlocks in3
4333: where in3.locked_action_id = ac2.assignment_action_id
4334: and ac3.assignment_action_id = in3.locking_action_id
4337: and not exists (
4338: select /*+ ORDERED*/
4339: null
4340: from per_all_assignments_f as3,
4341: pay_assignment_actions ac3
4342: where itpflg = 'N'
4343: and ac3.payroll_action_id = pa2.payroll_action_id
4344: and ac3.action_status not in ('C','S')
4345: and as3.assignment_id = ac3.assignment_id
4358: select /*+ ORDERED
4359: index(pa2 PAY_PAYROLL_ACTIONS_PK)
4360: index(pos PER_PERIODS_OF_SERVICE_N3)
4361: index(as2 PER_ASSIGNMENTS_F_N4)
4362: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4363: index(as1 PER_ASSIGNMENTS_F_PK)
4364: USE_NL(pos pop as1 as2) */
4365: ac2.assignment_action_id,
4366: ac2.assignment_id,
4369: from pay_payroll_actions pa,
4370: pay_population_ranges pop,
4371: per_periods_of_service pos,
4372: per_all_assignments_f as2,
4373: pay_assignment_actions ac2,
4374: pay_payroll_actions pa2,
4375: pay_action_classifications pcl,
4376: per_all_assignments_f as1
4377: where pa.payroll_action_id = pactid
4396: and pop.chunk_number = chunk
4397: and pos.person_id = pop.person_id
4398: and not exists (
4399: select null
4400: from pay_assignment_actions ac3,
4401: pay_payroll_actions pa3,
4402: pay_action_interlocks in3
4403: where in3.locked_action_id = ac2.assignment_action_id
4404: and ac3.assignment_action_id = in3.locking_action_id
4407: and not exists (
4408: select /*+ ORDERED*/
4409: null
4410: from per_all_assignments_f as3,
4411: pay_assignment_actions ac3
4412: where itpflg = 'N'
4413: and ac3.payroll_action_id = pa2.payroll_action_id
4414: and ac3.action_status not in ('C','S')
4415: and as3.assignment_id = ac3.assignment_id
4425: costactid number
4426: ) is
4427: select ac1.assignment_action_id
4428: from pay_action_interlocks in2,
4429: pay_assignment_actions ac1,
4430: pay_payroll_actions pa1,
4431: pay_action_classifications pcl1,
4432: per_all_assignments_f as1,
4433: pay_payroll_actions pa
4483: exit when costedacts%notfound;
4484: --
4485: --
4486: -- Get an assignment_action_id.
4487: select pay_assignment_actions_s.nextval
4488: into lockingactid
4489: from dual;
4490: --
4491: -- Insert an assignment action for each action.
4512:
4513: select distinct(pa.action_type)
4514: into pmnt_act_type
4515: from pay_action_interlocks int,
4516: pay_assignment_actions aa,
4517: pay_payroll_actions pa
4518: where int.locking_action_id = lockedactid
4519: and aa.assignment_action_id = int.locked_action_id
4520: and pa.payroll_action_id = aa.payroll_action_id;
4526: into not_paid
4527: from pay_action_interlocks int,
4528: pay_pre_payments ppp,
4529: pay_org_payment_methods_f opm,
4530: pay_assignment_actions paa,
4531: pay_payroll_actions ppa
4532: where int.locking_action_id = lockedactid
4533: and ppp.assignment_action_id = int.locked_action_id
4534: and paa.assignment_action_id = ppp.assignment_action_id
4538: and opm.cost_payment = 'Y'
4539: and opm.transfer_to_gl_flag = 'Y'
4540: and not exists
4541: (select 1
4542: from pay_assignment_actions aa
4543: where aa.pre_payment_id = ppp.pre_payment_id);
4544:
4545: end if;
4546:
4548:
4549: if (not_paid = 0) then
4550:
4551: -- Get an assignment_action_id.
4552: select pay_assignment_actions_s.nextval
4553: into lockingactid
4554: from dual;
4555: --
4556: -- Insert an assignment action for each action.
4592: select ac1.assignment_action_id,
4593: ac1.assignment_id,
4594: ac1.tax_unit_id,
4595: pa1.action_type
4596: from pay_assignment_actions ac1,
4597: pay_payroll_actions pa1
4598: where pa1.payroll_action_id = pactid
4599: and pa1.target_payroll_action_id = ac1.payroll_action_id
4600: and not exists (
4598: where pa1.payroll_action_id = pactid
4599: and pa1.target_payroll_action_id = ac1.payroll_action_id
4600: and not exists (
4601: select 1
4602: from pay_assignment_actions ac2
4603: where ac2.payroll_action_id = pactid
4604: and ac2.assignment_id = ac1.assignment_id)
4605: for update of ac1.assignment_action_id ;
4606: --
4619: end if;
4620: close qpcur ;
4621: --
4622: -- Get an assignment_action_id.
4623: select pay_assignment_actions_s.nextval
4624: into lockingactid
4625: from dual;
4626: --
4627: -- Insert an assignment action for the action
4728: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4729: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4730: INDEX(as1 PER_ASSIGNMENTS_N4)
4731: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4732: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4733: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4734: USE_NL(pop pos ppp opm as1 act as2) */
4735: act.assignment_action_id,
4736: act.assignment_id,
4741: pay_action_classifications pcl,
4742: pay_population_ranges pop,
4743: per_periods_of_service pos,
4744: per_all_assignments_f as1,
4745: pay_assignment_actions act,
4746: per_all_assignments_f as2,
4747: pay_pre_payments ppp,
4748: pay_org_payment_methods_f opm
4749: where pa1.payroll_action_id = pactid
4775: or pa1.org_payment_method_id is null)
4776: and not exists (
4777: select null
4778: from per_all_assignments_f as3,
4779: pay_assignment_actions ac3
4780: where itpflg = 'N'
4781: and ac3.payroll_action_id = pa2.payroll_action_id
4782: and ac3.action_status not in ('C', 'S')
4783: and as3.assignment_id = ac3.assignment_id
4787: and not exists (
4788: select /*+ ORDERED*/
4789: null
4790: from pay_action_interlocks int,
4791: pay_assignment_actions ac2
4792: where int.locked_action_id = act.assignment_action_id
4793: and ac2.assignment_action_id = int.locking_action_id
4794: and ac2.pre_payment_id = ppp.pre_payment_id
4795: and not exists (
4793: and ac2.assignment_action_id = int.locking_action_id
4794: and ac2.pre_payment_id = ppp.pre_payment_id
4795: and not exists (
4796: select null
4797: from pay_assignment_actions paa_void,
4798: pay_action_interlocks pai_void,
4799: pay_payroll_actions ppa_void
4800: where pai_void.locked_action_id = ac2.assignment_action_id
4801: and pai_void.locking_action_id = paa_void.assignment_action_id
4817: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4818: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4819: INDEX(as1 PER_ASSIGNMENTS_N4)
4820: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4821: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4822: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4823: USE_NL(pos ppp opm as1 act as2) */
4824: act.assignment_action_id,
4825: act.assignment_id,
4829: pay_payroll_actions pa2,
4830: pay_action_classifications pcl,
4831: per_periods_of_service pos,
4832: per_all_assignments_f as1,
4833: pay_assignment_actions act,
4834: per_all_assignments_f as2,
4835: pay_pre_payments ppp,
4836: pay_org_payment_methods_f opm
4837: where pa1.payroll_action_id = pactid
4861: or pa1.org_payment_method_id is null)
4862: and not exists (
4863: select null
4864: from per_all_assignments_f as3,
4865: pay_assignment_actions ac3
4866: where itpflg = 'N'
4867: and ac3.payroll_action_id = pa2.payroll_action_id
4868: and ac3.action_status not in ('C', 'S')
4869: and as3.assignment_id = ac3.assignment_id
4873: and not exists (
4874: select /*+ ORDERED*/
4875: null
4876: from pay_action_interlocks int,
4877: pay_assignment_actions ac2
4878: where int.locked_action_id = act.assignment_action_id
4879: and ac2.assignment_action_id = int.locking_action_id
4880: and ac2.pre_payment_id = ppp.pre_payment_id
4881: and not exists (
4879: and ac2.assignment_action_id = int.locking_action_id
4880: and ac2.pre_payment_id = ppp.pre_payment_id
4881: and not exists (
4882: select null
4883: from pay_assignment_actions paa_void,
4884: pay_action_interlocks pai_void,
4885: pay_payroll_actions ppa_void
4886: where pai_void.locked_action_id = ac2.assignment_action_id
4887: and pai_void.locking_action_id = paa_void.assignment_action_id
4902: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
4903: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4904: INDEX(as1 PER_ASSIGNMENTS_N4)
4905: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4906: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4907: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4908: USE_NL(pos pop ppp opm as1 act as2) */
4909: act.assignment_action_id,
4910: act.assignment_id,
4913: from pay_payroll_actions pa1,
4914: pay_population_ranges pop,
4915: per_periods_of_service pos,
4916: per_all_assignments_f as1,
4917: pay_assignment_actions act,
4918: pay_payroll_actions pa2,
4919: pay_action_classifications pcl,
4920: per_all_assignments_f as2,
4921: pay_pre_payments ppp,
4949: or pa1.org_payment_method_id is null)
4950: and not exists (
4951: select null
4952: from per_all_assignments_f as3,
4953: pay_assignment_actions ac3
4954: where itpflg = 'N'
4955: and ac3.payroll_action_id = pa2.payroll_action_id
4956: and ac3.action_status not in ('C', 'S')
4957: and as3.assignment_id = ac3.assignment_id
4961: and not exists (
4962: select /*+ ORDERED*/
4963: null
4964: from pay_action_interlocks int,
4965: pay_assignment_actions ac2
4966: where int.locked_action_id = act.assignment_action_id
4967: and ac2.assignment_action_id = int.locking_action_id
4968: and ac2.pre_payment_id = ppp.pre_payment_id
4969: and not exists (
4967: and ac2.assignment_action_id = int.locking_action_id
4968: and ac2.pre_payment_id = ppp.pre_payment_id
4969: and not exists (
4970: select null
4971: from pay_assignment_actions paa_void,
4972: pay_action_interlocks pai_void,
4973: pay_payroll_actions ppa_void
4974: where pai_void.locked_action_id = ac2.assignment_action_id
4975: and pai_void.locking_action_id = paa_void.assignment_action_id
5010: --
5011: -- we need to insert one action for each of the
5012: -- rows that we return from the cursor (i.e. one
5013: -- for each assignment/pre-payment).
5014: select pay_assignment_actions_s.nextval
5015: into lockingactid
5016: from dual;
5017: --
5018: -- insert the action record.
5260: cursor get_existing_person_chunk(p_ppa_id number
5261: ,p_paf_id number)
5262: is
5263: select paa.chunk_number
5264: from pay_assignment_actions paa
5265: , per_all_assignments_f paf
5266: , per_all_people_f ppf
5267: where paa.payroll_action_id = p_ppa_id
5268: and paa.assignment_id = p_paf_id
5277: cursor get_min_chunk(p_ppa_id number)
5278: is
5279: select min(chunk_number)
5280: from (select chunk_number, count(assignment_action_id) ct
5281: from pay_assignment_actions
5282: where payroll_action_id = p_ppa_id
5283: group by chunk_number) v1
5284: where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5285: from pay_assignment_actions
5281: from pay_assignment_actions
5282: where payroll_action_id = p_ppa_id
5283: group by chunk_number) v1
5284: where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5285: from pay_assignment_actions
5286: where payroll_action_id = p_ppa_id
5287: group by chunk_number) v2);
5288: --
5289: l_act_status pay_payroll_actions.action_status%type;
5288: --
5289: l_act_status pay_payroll_actions.action_status%type;
5290: l_act_type pay_payroll_actions.action_type%type;
5291: l_rep_name pay_report_format_mappings_f.report_name%type;
5292: l_chunk pay_assignment_actions.chunk_number%type;
5293: l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5294: --
5295: BEGIN
5296: --
5289: l_act_status pay_payroll_actions.action_status%type;
5290: l_act_type pay_payroll_actions.action_type%type;
5291: l_rep_name pay_report_format_mappings_f.report_name%type;
5292: l_chunk pay_assignment_actions.chunk_number%type;
5293: l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5294: --
5295: BEGIN
5296: --
5297: -- Determine whether new action can be inserted: 1. if payroll_action is
5347: end if;
5348: --
5349: end if;
5350: --
5351: select pay_assignment_actions_s.nextval
5352: into l_asg_act_id
5353: from dual;
5354: --
5355: -- insert the action
5353: from dual;
5354: --
5355: -- insert the action
5356: --
5357: insert into pay_assignment_actions
5358: (assignment_action_id
5359: ,assignment_id
5360: ,payroll_action_id
5361: ,action_status