[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
2232: ptype number,
2233: class varchar2
2234: ) is
2235: SELECT
2236: /*+ leading(PA1 PA2 ACT) use_nl(PA1 PA2 ACT) index(ACT PAY_ASSIGNMENT_ACTIONS_N50)*/ -- Bug 6522667
2237: act.assignment_action_id,
2238: act.assignment_id,
2239: act.tax_unit_id,
2240: ppp.pre_payment_id,
2243: from pay_payroll_actions pa1,
2244: pay_population_ranges pop,
2245: per_periods_of_service pos,
2246: per_all_assignments_f as1,
2247: pay_assignment_actions act,
2248: pay_payroll_actions pa2,
2249: pay_action_classifications pcl,
2250: pay_pre_payments ppp,
2251: per_all_assignments_f as2,
2281: and not exists (
2282: select /* Bug 6522667, moved ORDERED hint */
2283: null
2284: from pay_action_interlocks int,
2285: pay_assignment_actions ac2
2286: where int.locked_action_id = act.assignment_action_id
2287: and ac2.assignment_action_id = int.locking_action_id
2288: and ac2.pre_payment_id = ppp.pre_payment_id
2289: and not exists (
2287: and ac2.assignment_action_id = int.locking_action_id
2288: and ac2.pre_payment_id = ppp.pre_payment_id
2289: and not exists (
2290: select null
2291: from pay_assignment_actions paa_void,
2292: pay_action_interlocks pai_void,
2293: pay_payroll_actions ppa_void
2294: where pai_void.locked_action_id = ac2.assignment_action_id
2295: and pai_void.locking_action_id = paa_void.assignment_action_id
2299: and not exists (
2300: select /* Bug 6522667, moved ORDERED hint */
2301: null
2302: from per_all_assignments_f as3,
2303: pay_assignment_actions ac3
2304: where itpflg = 'N'
2305: and ac3.payroll_action_id = pa2.payroll_action_id
2306: and ac3.action_status not in ('C', 'S')
2307: and as3.assignment_id = ac3.assignment_id
2342: -- Ensure there exists a voided check for this payment.
2343: and exists
2344: (select 1
2345: from pay_action_interlocks lck1,
2346: pay_assignment_actions chk_paa,
2347: pay_payroll_actions chk_ppa,
2348: pay_action_interlocks lck2,
2349: pay_assignment_actions vd_paa,
2350: pay_payroll_actions vd_ppa
2345: from pay_action_interlocks lck1,
2346: pay_assignment_actions chk_paa,
2347: pay_payroll_actions chk_ppa,
2348: pay_action_interlocks lck2,
2349: pay_assignment_actions vd_paa,
2350: pay_payroll_actions vd_ppa
2351: where lck1.locked_action_id = plockedid
2352: and lck1.locking_action_id = chk_paa.assignment_action_id
2353: and chk_paa.payroll_action_id = chk_ppa.payroll_action_id
2409: if inasgset then
2410: -- we need to insert one action for each of the
2411: -- rows that we return from the cursor (i.e. one
2412: -- for each assignment/pre-payment).
2413: select pay_assignment_actions_s.nextval
2414: into lockingactid
2415: from dual;
2416: --
2417: -- insert the action record.
2477: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2478: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2479: INDEX(as1 PER_ASSIGNMENTS_N4)
2480: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2481: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2482: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2483: USE_NL(pop pos ppp opm as1 act as2) */
2484: act.assignment_action_id,
2485: act.assignment_id,
2490: pay_action_classifications pcl,
2491: pay_population_ranges pop,
2492: per_periods_of_service pos,
2493: per_all_assignments_f as1,
2494: pay_assignment_actions act,
2495: per_all_assignments_f as2,
2496: pay_pre_payments ppp,
2497: pay_org_payment_methods_f opm
2498: where pa1.payroll_action_id = pactid
2524: or pa1.org_payment_method_id is null)
2525: and not exists (
2526: select null
2527: from per_all_assignments_f as3,
2528: pay_assignment_actions ac3
2529: where itpflg = 'N'
2530: and ac3.payroll_action_id = pa2.payroll_action_id
2531: and ac3.action_status not in ('C', 'S')
2532: and as3.assignment_id = ac3.assignment_id
2536: and not exists (
2537: select /*+ ORDERED*/
2538: null
2539: from pay_action_interlocks int,
2540: pay_assignment_actions ac2
2541: where int.locked_action_id = act.assignment_action_id
2542: and ac2.assignment_action_id = int.locking_action_id
2543: and ac2.pre_payment_id = ppp.pre_payment_id
2544: and not exists (
2542: and ac2.assignment_action_id = int.locking_action_id
2543: and ac2.pre_payment_id = ppp.pre_payment_id
2544: and not exists (
2545: select null
2546: from pay_assignment_actions paa_void,
2547: pay_action_interlocks pai_void,
2548: pay_payroll_actions ppa_void
2549: where pai_void.locked_action_id = ac2.assignment_action_id
2550: and pai_void.locking_action_id = paa_void.assignment_action_id
2566: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2567: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2568: INDEX(as1 PER_ASSIGNMENTS_N4)
2569: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2570: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2571: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2572: USE_NL(pos ppp opm as1 act as2) */
2573: act.assignment_action_id,
2574: act.assignment_id,
2578: pay_payroll_actions pa2,
2579: pay_action_classifications pcl,
2580: per_periods_of_service pos,
2581: per_all_assignments_f as1,
2582: pay_assignment_actions act,
2583: per_all_assignments_f as2,
2584: pay_pre_payments ppp,
2585: pay_org_payment_methods_f opm
2586: where pa1.payroll_action_id = pactid
2610: or pa1.org_payment_method_id is null)
2611: and not exists (
2612: select null
2613: from per_all_assignments_f as3,
2614: pay_assignment_actions ac3
2615: where itpflg = 'N'
2616: and ac3.payroll_action_id = pa2.payroll_action_id
2617: and ac3.action_status not in ('C', 'S')
2618: and as3.assignment_id = ac3.assignment_id
2622: and not exists (
2623: select /*+ ORDERED*/
2624: null
2625: from pay_action_interlocks int,
2626: pay_assignment_actions ac2
2627: where int.locked_action_id = act.assignment_action_id
2628: and ac2.assignment_action_id = int.locking_action_id
2629: and ac2.pre_payment_id = ppp.pre_payment_id
2630: and not exists (
2628: and ac2.assignment_action_id = int.locking_action_id
2629: and ac2.pre_payment_id = ppp.pre_payment_id
2630: and not exists (
2631: select null
2632: from pay_assignment_actions paa_void,
2633: pay_action_interlocks pai_void,
2634: pay_payroll_actions ppa_void
2635: where pai_void.locked_action_id = ac2.assignment_action_id
2636: and pai_void.locking_action_id = paa_void.assignment_action_id
2651: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
2652: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2653: INDEX(as1 PER_ASSIGNMENTS_N4)
2654: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2655: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2656: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2657: USE_NL(pos pop ppp opm as1 act as2) */
2658: act.assignment_action_id,
2659: act.assignment_id,
2662: from pay_payroll_actions pa1,
2663: pay_population_ranges pop,
2664: per_periods_of_service pos,
2665: per_all_assignments_f as1,
2666: pay_assignment_actions act,
2667: pay_payroll_actions pa2,
2668: pay_action_classifications pcl,
2669: per_all_assignments_f as2,
2670: pay_pre_payments ppp,
2698: or pa1.org_payment_method_id is null)
2699: and not exists (
2700: select null
2701: from per_all_assignments_f as3,
2702: pay_assignment_actions ac3
2703: where itpflg = 'N'
2704: and ac3.payroll_action_id = pa2.payroll_action_id
2705: and ac3.action_status not in ('C', 'S')
2706: and as3.assignment_id = ac3.assignment_id
2710: and not exists (
2711: select /*+ ORDERED*/
2712: null
2713: from pay_action_interlocks int,
2714: pay_assignment_actions ac2
2715: where int.locked_action_id = act.assignment_action_id
2716: and ac2.assignment_action_id = int.locking_action_id
2717: and ac2.pre_payment_id = ppp.pre_payment_id
2718: and not exists (
2716: and ac2.assignment_action_id = int.locking_action_id
2717: and ac2.pre_payment_id = ppp.pre_payment_id
2718: and not exists (
2719: select null
2720: from pay_assignment_actions paa_void,
2721: pay_action_interlocks pai_void,
2722: pay_payroll_actions ppa_void
2723: where pai_void.locked_action_id = ac2.assignment_action_id
2724: and pai_void.locking_action_id = paa_void.assignment_action_id
2759: --
2760: -- we need to insert one action for each of the
2761: -- rows that we return from the cursor (i.e. one
2762: -- for each assignment/pre-payment).
2763: select pay_assignment_actions_s.nextval
2764: into lockingactid
2765: from dual;
2766: --
2767: -- insert the action record.
2837: pay_action_classifications pcl,
2838: pay_population_ranges pop,
2839: per_periods_of_service pos,
2840: per_all_assignments_f as1,
2841: pay_assignment_actions act,
2842: per_all_assignments_f as2
2843: where pa1.payroll_action_id = pactid
2844: and pa2.payroll_id = pa1.payroll_id
2845: and pa2.effective_date between
2863: and pos.person_id = pop.person_id
2864: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2865: and not exists (
2866: select null
2867: from pay_assignment_actions ac2,
2868: pay_payroll_actions pa3,
2869: pay_action_interlocks int
2870: where int.locked_action_id = act.assignment_action_id
2871: and ac2.assignment_action_id = int.locking_action_id
2874: and not exists (
2875: select /*+ ORDERED*/
2876: null
2877: from per_all_assignments_f as3,
2878: pay_assignment_actions ac3
2879: where itpflg = 'N'
2880: and ac3.payroll_action_id = pa2.payroll_action_id
2881: and ac3.action_status not in ( 'C', 'S')
2882: and as3.assignment_id = ac3.assignment_id
2909: pay_payroll_actions pa2,
2910: pay_action_classifications pcl,
2911: per_periods_of_service pos,
2912: per_all_assignments_f as1,
2913: pay_assignment_actions act,
2914: per_all_assignments_f as2
2915: where pa1.payroll_action_id = pactid
2916: and pa2.payroll_id = pa1.payroll_id
2917: and pa2.effective_date between
2933: and pos.person_id between stperson and endperson
2934: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2935: and not exists (
2936: select null
2937: from pay_assignment_actions ac2,
2938: pay_payroll_actions pa3,
2939: pay_action_interlocks int
2940: where int.locked_action_id = act.assignment_action_id
2941: and ac2.assignment_action_id = int.locking_action_id
2944: and not exists (
2945: select /*+ ORDERED*/
2946: null
2947: from per_all_assignments_f as3,
2948: pay_assignment_actions ac3
2949: where itpflg = 'N'
2950: and ac3.payroll_action_id = pa2.payroll_action_id
2951: and ac3.action_status not in ( 'C', 'S')
2952: and as3.assignment_id = ac3.assignment_id
2965: ) is
2966: select /*+ ORDERED
2967: index(pa2 PAY_PAYROLL_ACTIONS_PK)
2968: index(pos PER_PERIODS_OF_SERVICE_N3)
2969: index(act PAY_ASSIGNMENT_ACTIONS_N51)
2970: index(as1 PER_ASSIGNMENTS_F_N4)
2971: index(as2 PER_ASSIGNMENTS_F_PK)
2972: USE_NL(pos pop act as1 as2 pa2) */
2973: act.assignment_action_id,
2979: from pay_payroll_actions pa1,
2980: pay_population_ranges pop,
2981: per_periods_of_service pos,
2982: per_all_assignments_f as1,
2983: pay_assignment_actions act,
2984: pay_payroll_actions pa2,
2985: pay_action_classifications pcl,
2986: per_all_assignments_f as2
2987: where pa1.payroll_action_id = pactid
3007: and pos.person_id = pop.person_id
3008: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3009: and not exists (
3010: select null
3011: from pay_assignment_actions ac2,
3012: pay_payroll_actions pa3,
3013: pay_action_interlocks int
3014: where int.locked_action_id = act.assignment_action_id
3015: and ac2.assignment_action_id = int.locking_action_id
3018: and not exists (
3019: select /*+ ORDERED*/
3020: null
3021: from per_all_assignments_f as3,
3022: pay_assignment_actions ac3
3023: where itpflg = 'N'
3024: and ac3.payroll_action_id = pa2.payroll_action_id
3025: and ac3.action_status not in ( 'C', 'S')
3026: and as3.assignment_id = ac3.assignment_id
3065: if (mult_asg_flag = 'Y')
3066: then
3067: -- insert master actions
3068: if (prev_person_id is null or prev_person_id <> person_id) then
3069: select pay_assignment_actions_s.nextval
3070: into lockingactid
3071: from dual;
3072:
3073: -- insert into pay_assignment_actions.
3069: select pay_assignment_actions_s.nextval
3070: into lockingactid
3071: from dual;
3072:
3073: -- insert into pay_assignment_actions.
3074: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3075:
3076: end if;
3077: -- insert interlocks
3083: /* process the insert of assignment actions */
3084: /* logic prevents more than one action per assignment */
3085: if(prev_assignid is null OR prev_assignid <> assignid) then
3086: -- get a value for the action id that is locking.
3087: select pay_assignment_actions_s.nextval
3088: into lockingactid
3089: from dual;
3090: --
3091: -- insert into pay_assignment_actions.
3087: select pay_assignment_actions_s.nextval
3088: into lockingactid
3089: from dual;
3090: --
3091: -- insert into pay_assignment_actions.
3092: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3093: end if;
3094: --
3095: -- insert into interlocks table.
3150: pay_action_classifications pcl,
3151: pay_population_ranges pop,
3152: per_periods_of_service pos,
3153: per_all_assignments_f as1,
3154: pay_assignment_actions act,
3155: per_all_assignments_f as2
3156: where pa1.payroll_action_id = pactid
3157: and pa2.consolidation_set_id = pa1.consolidation_set_id
3158: and pa2.effective_date between
3173: and pos.period_of_service_id = as1.period_of_service_id
3174: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3175: and not exists (
3176: select null
3177: from pay_assignment_actions ac2,
3178: pay_payroll_actions pa3,
3179: pay_action_interlocks int
3180: where int.locked_action_id = act.assignment_action_id
3181: and ac2.assignment_action_id = int.locking_action_id
3184: and not exists (
3185: select /*+ ORDERED*/
3186: null
3187: from per_all_assignments_f as3,
3188: pay_assignment_actions ac3
3189: where itpflg = 'N'
3190: and ac3.payroll_action_id = pa2.payroll_action_id
3191: and ac3.action_status not in ('C','S')
3192: and as3.assignment_id = ac3.assignment_id
3216: pay_payroll_actions pa2,
3217: pay_action_classifications pcl,
3218: per_periods_of_service pos,
3219: per_all_assignments_f as1,
3220: pay_assignment_actions act,
3221: per_all_assignments_f as2
3222: where pa1.payroll_action_id = pactid
3223: and pa2.consolidation_set_id = pa1.consolidation_set_id
3224: and pa2.effective_date between
3237: and pos.person_id between stperson and endperson
3238: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3239: and not exists (
3240: select null
3241: from pay_assignment_actions ac2,
3242: pay_payroll_actions pa3,
3243: pay_action_interlocks int
3244: where int.locked_action_id = act.assignment_action_id
3245: and ac2.assignment_action_id = int.locking_action_id
3248: and not exists (
3249: select /*+ ORDERED*/
3250: null
3251: from per_all_assignments_f as3,
3252: pay_assignment_actions ac3
3253: where itpflg = 'N'
3254: and ac3.payroll_action_id = pa2.payroll_action_id
3255: and ac3.action_status not in ('C','S')
3256: and as3.assignment_id = ac3.assignment_id
3270: select /*+ ORDERED
3271: index(pa2 PAY_PAYROLL_ACTIONS_PK)
3272: index(pos PER_PERIODS_OF_SERVICE_N3)
3273: index(as1 PER_ASSIGNMENTS_F_N4)
3274: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3275: index(as2 PER_ASSIGNMENTS_F_PK)
3276: USE_NL(pos pop act pa2 as2 as1) */
3277: act.assignment_action_id,
3278: act.assignment_id,
3280: from pay_payroll_actions pa1,
3281: pay_population_ranges pop,
3282: per_periods_of_service pos,
3283: per_all_assignments_f as1,
3284: pay_assignment_actions act,
3285: pay_payroll_actions pa2,
3286: pay_action_classifications pcl,
3287: per_all_assignments_f as2
3288: where pa1.payroll_action_id = pactid
3305: and pos.person_id = pop.person_id
3306: and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3307: and not exists (
3308: select null
3309: from pay_assignment_actions ac2,
3310: pay_payroll_actions pa3,
3311: pay_action_interlocks int
3312: where int.locked_action_id = act.assignment_action_id
3313: and ac2.assignment_action_id = int.locking_action_id
3316: and not exists (
3317: select /*+ ORDERED*/
3318: null
3319: from per_all_assignments_f as3,
3320: pay_assignment_actions ac3
3321: where itpflg = 'N'
3322: and ac3.payroll_action_id = pa2.payroll_action_id
3323: and ac3.action_status not in ('C','S')
3324: and as3.assignment_id = ac3.assignment_id
3358: /* process the insert of assignment actions */
3359: /* logic prevents more than one action per assignment */
3360: if(prev_assignid is null OR prev_assignid <> assignid) then
3361: -- get a value for the action id that is locking.
3362: select pay_assignment_actions_s.nextval
3363: into lockingactid
3364: from dual;
3365: --
3366: -- insert into pay_assignment_actions.
3362: select pay_assignment_actions_s.nextval
3363: into lockingactid
3364: from dual;
3365: --
3366: -- insert into pay_assignment_actions.
3367: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3368: end if;
3369: --
3370: -- insert into interlocks table.
3425: pay_action_classifications pcl,
3426: pay_population_ranges pop,
3427: per_periods_of_service pos,
3428: per_all_assignments_f as1,
3429: pay_assignment_actions act,
3430: per_all_assignments_f as2
3431: where pa1.payroll_action_id = pactid
3432: and pa2.consolidation_set_id = pa1.consolidation_set_id
3433: and pa2.effective_date between
3448: and pos.period_of_service_id = as1.period_of_service_id
3449: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3450: and not exists (
3451: select null
3452: from pay_assignment_actions ac2,
3453: pay_payroll_actions pa3,
3454: pay_action_interlocks int
3455: where int.locked_action_id = act.assignment_action_id
3456: and ac2.assignment_action_id = int.locking_action_id
3459: and not exists (
3460: select /*+ ORDERED*/
3461: null
3462: from per_all_assignments_f as3,
3463: pay_assignment_actions ac3
3464: where itpflg = 'N'
3465: and ac3.payroll_action_id = pa2.payroll_action_id
3466: and ac3.action_status not in ('C','S')
3467: and as3.assignment_id = ac3.assignment_id
3506: or (pa2.action_type = 'D'
3507: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3508: and exists (select 1
3509: from pay_action_interlocks int,
3510: pay_assignment_actions chq,
3511: pay_payroll_actions pcq,
3512: pay_pre_payments ppp,
3513: pay_org_payment_methods_f pom,
3514: pay_ce_reconciled_payments crp
3524: pom.effective_start_date and pom.effective_end_date))
3525: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3526: and exists (select 1
3527: from pay_action_interlocks int,
3528: pay_assignment_actions chq,
3529: pay_payroll_actions pcq,
3530: pay_pre_payments ppp,
3531: pay_org_payment_methods_f pom
3532: where int.locking_action_id = act.assignment_action_id
3563: pay_payroll_actions pa2,
3564: pay_action_classifications pcl,
3565: per_periods_of_service pos,
3566: per_all_assignments_f as1,
3567: pay_assignment_actions act,
3568: per_all_assignments_f as2
3569: where pa1.payroll_action_id = pactid
3570: and pa2.consolidation_set_id = pa1.consolidation_set_id
3571: and pa2.effective_date between
3584: and pos.person_id between stperson and endperson
3585: and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3586: and not exists (
3587: select null
3588: from pay_assignment_actions ac2,
3589: pay_payroll_actions pa3,
3590: pay_action_interlocks int
3591: where int.locked_action_id = act.assignment_action_id
3592: and ac2.assignment_action_id = int.locking_action_id
3595: and not exists (
3596: select /*+ ORDERED*/
3597: null
3598: from per_all_assignments_f as3,
3599: pay_assignment_actions ac3
3600: where itpflg = 'N'
3601: and ac3.payroll_action_id = pa2.payroll_action_id
3602: and ac3.action_status not in ('C','S')
3603: and as3.assignment_id = ac3.assignment_id
3642: or (pa2.action_type = 'D'
3643: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3644: and exists (select 1
3645: from pay_action_interlocks int,
3646: pay_assignment_actions chq,
3647: pay_payroll_actions pcq,
3648: pay_pre_payments ppp,
3649: pay_org_payment_methods_f pom,
3650: pay_ce_reconciled_payments crp
3660: pom.effective_start_date and pom.effective_end_date))
3661: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3662: and exists (select 1
3663: from pay_action_interlocks int,
3664: pay_assignment_actions chq,
3665: pay_payroll_actions pcq,
3666: pay_pre_payments ppp,
3667: pay_org_payment_methods_f pom
3668: where int.locking_action_id = act.assignment_action_id
3688: select /*+ ORDERED
3689: index(pa2 PAY_PAYROLL_ACTIONS_PK)
3690: index(pos PER_PERIODS_OF_SERVICE_N3)
3691: index(as1 PER_ASSIGNMENTS_F_N4)
3692: index(act PAY_ASSIGNMENT_ACTIONS_N51)
3693: index(as2 PER_ASSIGNMENTS_F_PK)
3694: USE_NL(pos pop act pa2 as2 as1) */
3695: act.assignment_action_id,
3696: act.assignment_id,
3699: from pay_payroll_actions pa1,
3700: pay_population_ranges pop,
3701: per_periods_of_service pos,
3702: per_all_assignments_f as1,
3703: pay_assignment_actions act,
3704: pay_payroll_actions pa2,
3705: pay_action_classifications pcl,
3706: per_all_assignments_f as2
3707: where pa1.payroll_action_id = pactid
3724: and pos.person_id = pop.person_id
3725: and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3726: and not exists (
3727: select null
3728: from pay_assignment_actions ac2,
3729: pay_payroll_actions pa3,
3730: pay_action_interlocks int
3731: where int.locked_action_id = act.assignment_action_id
3732: and ac2.assignment_action_id = int.locking_action_id
3735: and not exists (
3736: select /*+ ORDERED*/
3737: null
3738: from per_all_assignments_f as3,
3739: pay_assignment_actions ac3
3740: where itpflg = 'N'
3741: and ac3.payroll_action_id = pa2.payroll_action_id
3742: and ac3.action_status not in ('C','S')
3743: and as3.assignment_id = ac3.assignment_id
3782: or (pa2.action_type = 'D'
3783: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3784: and exists (select 1
3785: from pay_action_interlocks int,
3786: pay_assignment_actions chq,
3787: pay_payroll_actions pcq,
3788: pay_pre_payments ppp,
3789: pay_org_payment_methods_f pom,
3790: pay_ce_reconciled_payments crp
3800: pom.effective_start_date and pom.effective_end_date))
3801: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3802: and exists (select 1
3803: from pay_action_interlocks int,
3804: pay_assignment_actions chq,
3805: pay_payroll_actions pcq,
3806: pay_pre_payments ppp,
3807: pay_org_payment_methods_f pom
3808: where int.locking_action_id = act.assignment_action_id
3852: /* logic prevents more than one action per assignment */
3853: if(prev_assignid is null OR prev_assignid <> assignid OR
3854: prev_pactid <> lpactid) then
3855: -- get a value for the action id that is locking.
3856: select pay_assignment_actions_s.nextval
3857: into lockingactid
3858: from dual;
3859: --
3860: -- insert into pay_assignment_actions.
3856: select pay_assignment_actions_s.nextval
3857: into lockingactid
3858: from dual;
3859: --
3860: -- insert into pay_assignment_actions.
3861: insact(lockingactid,assignid,pactid,rand_chunk,greid);
3862: end if;
3863: --
3864: -- insert into interlocks table.
3921: pay_action_classifications pcl,
3922: pay_population_ranges pop,
3923: per_periods_of_service pos,
3924: per_all_assignments_f as1,
3925: pay_assignment_actions act,
3926: per_all_assignments_f as2
3927: where pa1.payroll_action_id = pactid
3928: and pay.consolidation_set_id = pa1.consolidation_set_id
3929: and pa1.effective_date between
3953: and not exists (
3954: select /*+ ORDERED*/
3955: null
3956: from per_all_assignments_f as3,
3957: pay_assignment_actions ac3
3958: where itpflg = 'N'
3959: and ac3.payroll_action_id = pa2.payroll_action_id
3960: and ac3.action_status not in ('C','S')
3961: and as3.assignment_id = ac3.assignment_id
3987: pay_payroll_actions pa2,
3988: pay_action_classifications pcl,
3989: per_periods_of_service pos,
3990: per_all_assignments_f as1,
3991: pay_assignment_actions act,
3992: per_all_assignments_f as2
3993: where pa1.payroll_action_id = pactid
3994: and pay.consolidation_set_id = pa1.consolidation_set_id
3995: and pa1.effective_date between
4017: and not exists (
4018: select /*+ ORDERED*/
4019: null
4020: from per_all_assignments_f as3,
4021: pay_assignment_actions ac3
4022: where itpflg = 'N'
4023: and ac3.payroll_action_id = pa2.payroll_action_id
4024: and ac3.action_status not in ('C','S')
4025: and as3.assignment_id = ac3.assignment_id
4054: /* process the insert of assignment actions */
4055: /* logic prevents more than one action per assignment */
4056: if(prev_assignid is null OR prev_assignid <> assignid) then
4057: -- get a value for the action id that is locking.
4058: select pay_assignment_actions_s.nextval
4059: into lockingactid
4060: from dual;
4061: --
4062: -- insert into pay_assignment_actions.
4058: select pay_assignment_actions_s.nextval
4059: into lockingactid
4060: from dual;
4061: --
4062: -- insert into pay_assignment_actions.
4063: insact(lockingactid,assignid,pactid,rand_chunk,greid);
4064: end if;
4065: --
4066: prev_assignid := assignid;
4159: exit when beecur%notfound;
4160: end if;
4161: --
4162: -- Get an assignment_action_id.
4163: select pay_assignment_actions_s.nextval
4164: into asgactid
4165: from dual;
4166: --
4167: if preasgid is null or preasgid <> assignid then
4224: pay_action_classifications pcl,
4225: pay_population_ranges pop,
4226: per_periods_of_service pos,
4227: per_all_assignments_f as2,
4228: pay_assignment_actions ac2,
4229: per_all_assignments_f as1
4230: where pa.payroll_action_id = pactid
4231: and pa2.consolidation_set_id = pa.consolidation_set_id
4232: and pa2.effective_date between
4249: and pop.chunk_number = chunk
4250: and pos.person_id = pop.person_id
4251: and not exists (
4252: select null
4253: from pay_assignment_actions ac3,
4254: pay_payroll_actions pa3,
4255: pay_action_interlocks in3
4256: where in3.locked_action_id = ac2.assignment_action_id
4257: and ac3.assignment_action_id = in3.locking_action_id
4260: and not exists (
4261: select /*+ ORDERED*/
4262: null
4263: from per_all_assignments_f as3,
4264: pay_assignment_actions ac3
4265: where itpflg = 'N'
4266: and ac3.payroll_action_id = pa2.payroll_action_id
4267: and ac3.action_status not in ('C','S')
4268: and as3.assignment_id = ac3.assignment_id
4292: pay_payroll_actions pa2,
4293: pay_action_classifications pcl,
4294: per_periods_of_service pos,
4295: per_all_assignments_f as2,
4296: pay_assignment_actions ac2,
4297: per_all_assignments_f as1
4298: where pa.payroll_action_id = pactid
4299: and pa2.consolidation_set_id = pa.consolidation_set_id
4300: and pa2.effective_date between
4316: and pos.person_id between
4317: stperson and endperson
4318: and not exists (
4319: select null
4320: from pay_assignment_actions ac3,
4321: pay_payroll_actions pa3,
4322: pay_action_interlocks in3
4323: where in3.locked_action_id = ac2.assignment_action_id
4324: and ac3.assignment_action_id = in3.locking_action_id
4327: and not exists (
4328: select /*+ ORDERED*/
4329: null
4330: from per_all_assignments_f as3,
4331: pay_assignment_actions ac3
4332: where itpflg = 'N'
4333: and ac3.payroll_action_id = pa2.payroll_action_id
4334: and ac3.action_status not in ('C','S')
4335: and as3.assignment_id = ac3.assignment_id
4348: select /*+ ORDERED
4349: index(pa2 PAY_PAYROLL_ACTIONS_PK)
4350: index(pos PER_PERIODS_OF_SERVICE_N3)
4351: index(as2 PER_ASSIGNMENTS_F_N4)
4352: index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4353: index(as1 PER_ASSIGNMENTS_F_PK)
4354: USE_NL(pos pop as1 as2) */
4355: ac2.assignment_action_id,
4356: ac2.assignment_id,
4359: from pay_payroll_actions pa,
4360: pay_population_ranges pop,
4361: per_periods_of_service pos,
4362: per_all_assignments_f as2,
4363: pay_assignment_actions ac2,
4364: pay_payroll_actions pa2,
4365: pay_action_classifications pcl,
4366: per_all_assignments_f as1
4367: where pa.payroll_action_id = pactid
4386: and pop.chunk_number = chunk
4387: and pos.person_id = pop.person_id
4388: and not exists (
4389: select null
4390: from pay_assignment_actions ac3,
4391: pay_payroll_actions pa3,
4392: pay_action_interlocks in3
4393: where in3.locked_action_id = ac2.assignment_action_id
4394: and ac3.assignment_action_id = in3.locking_action_id
4397: and not exists (
4398: select /*+ ORDERED*/
4399: null
4400: from per_all_assignments_f as3,
4401: pay_assignment_actions ac3
4402: where itpflg = 'N'
4403: and ac3.payroll_action_id = pa2.payroll_action_id
4404: and ac3.action_status not in ('C','S')
4405: and as3.assignment_id = ac3.assignment_id
4415: costactid number
4416: ) is
4417: select ac1.assignment_action_id
4418: from pay_action_interlocks in2,
4419: pay_assignment_actions ac1,
4420: pay_payroll_actions pa1,
4421: pay_action_classifications pcl1,
4422: per_all_assignments_f as1,
4423: pay_payroll_actions pa
4473: exit when costedacts%notfound;
4474: --
4475: --
4476: -- Get an assignment_action_id.
4477: select pay_assignment_actions_s.nextval
4478: into lockingactid
4479: from dual;
4480: --
4481: -- Insert an assignment action for each action.
4502:
4503: select distinct(pa.action_type)
4504: into pmnt_act_type
4505: from pay_action_interlocks int,
4506: pay_assignment_actions aa,
4507: pay_payroll_actions pa
4508: where int.locking_action_id = lockedactid
4509: and aa.assignment_action_id = int.locked_action_id
4510: and pa.payroll_action_id = aa.payroll_action_id;
4523: and opm.cost_payment = 'Y'
4524: and opm.transfer_to_gl_flag = 'Y'
4525: and not exists
4526: (select 1
4527: from pay_assignment_actions aa
4528: where aa.pre_payment_id = ppp.pre_payment_id);
4529:
4530: end if;
4531:
4533:
4534: if (not_paid = 0) then
4535:
4536: -- Get an assignment_action_id.
4537: select pay_assignment_actions_s.nextval
4538: into lockingactid
4539: from dual;
4540: --
4541: -- Insert an assignment action for each action.
4577: select ac1.assignment_action_id,
4578: ac1.assignment_id,
4579: ac1.tax_unit_id,
4580: pa1.action_type
4581: from pay_assignment_actions ac1,
4582: pay_payroll_actions pa1
4583: where pa1.payroll_action_id = pactid
4584: and pa1.target_payroll_action_id = ac1.payroll_action_id
4585: and not exists (
4583: where pa1.payroll_action_id = pactid
4584: and pa1.target_payroll_action_id = ac1.payroll_action_id
4585: and not exists (
4586: select 1
4587: from pay_assignment_actions ac2
4588: where ac2.payroll_action_id = pactid
4589: and ac2.assignment_id = ac1.assignment_id)
4590: for update of ac1.assignment_action_id ;
4591: --
4604: end if;
4605: close qpcur ;
4606: --
4607: -- Get an assignment_action_id.
4608: select pay_assignment_actions_s.nextval
4609: into lockingactid
4610: from dual;
4611: --
4612: -- Insert an assignment action for the action
4713: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4714: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4715: INDEX(as1 PER_ASSIGNMENTS_N4)
4716: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4717: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4718: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4719: USE_NL(pop pos ppp opm as1 act as2) */
4720: act.assignment_action_id,
4721: act.assignment_id,
4726: pay_action_classifications pcl,
4727: pay_population_ranges pop,
4728: per_periods_of_service pos,
4729: per_all_assignments_f as1,
4730: pay_assignment_actions act,
4731: per_all_assignments_f as2,
4732: pay_pre_payments ppp,
4733: pay_org_payment_methods_f opm
4734: where pa1.payroll_action_id = pactid
4760: or pa1.org_payment_method_id is null)
4761: and not exists (
4762: select null
4763: from per_all_assignments_f as3,
4764: pay_assignment_actions ac3
4765: where itpflg = 'N'
4766: and ac3.payroll_action_id = pa2.payroll_action_id
4767: and ac3.action_status not in ('C', 'S')
4768: and as3.assignment_id = ac3.assignment_id
4772: and not exists (
4773: select /*+ ORDERED*/
4774: null
4775: from pay_action_interlocks int,
4776: pay_assignment_actions ac2
4777: where int.locked_action_id = act.assignment_action_id
4778: and ac2.assignment_action_id = int.locking_action_id
4779: and ac2.pre_payment_id = ppp.pre_payment_id
4780: and not exists (
4778: and ac2.assignment_action_id = int.locking_action_id
4779: and ac2.pre_payment_id = ppp.pre_payment_id
4780: and not exists (
4781: select null
4782: from pay_assignment_actions paa_void,
4783: pay_action_interlocks pai_void,
4784: pay_payroll_actions ppa_void
4785: where pai_void.locked_action_id = ac2.assignment_action_id
4786: and pai_void.locking_action_id = paa_void.assignment_action_id
4802: INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4803: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4804: INDEX(as1 PER_ASSIGNMENTS_N4)
4805: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4806: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4807: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4808: USE_NL(pos ppp opm as1 act as2) */
4809: act.assignment_action_id,
4810: act.assignment_id,
4814: pay_payroll_actions pa2,
4815: pay_action_classifications pcl,
4816: per_periods_of_service pos,
4817: per_all_assignments_f as1,
4818: pay_assignment_actions act,
4819: per_all_assignments_f as2,
4820: pay_pre_payments ppp,
4821: pay_org_payment_methods_f opm
4822: where pa1.payroll_action_id = pactid
4846: or pa1.org_payment_method_id is null)
4847: and not exists (
4848: select null
4849: from per_all_assignments_f as3,
4850: pay_assignment_actions ac3
4851: where itpflg = 'N'
4852: and ac3.payroll_action_id = pa2.payroll_action_id
4853: and ac3.action_status not in ('C', 'S')
4854: and as3.assignment_id = ac3.assignment_id
4858: and not exists (
4859: select /*+ ORDERED*/
4860: null
4861: from pay_action_interlocks int,
4862: pay_assignment_actions ac2
4863: where int.locked_action_id = act.assignment_action_id
4864: and ac2.assignment_action_id = int.locking_action_id
4865: and ac2.pre_payment_id = ppp.pre_payment_id
4866: and not exists (
4864: and ac2.assignment_action_id = int.locking_action_id
4865: and ac2.pre_payment_id = ppp.pre_payment_id
4866: and not exists (
4867: select null
4868: from pay_assignment_actions paa_void,
4869: pay_action_interlocks pai_void,
4870: pay_payroll_actions ppa_void
4871: where pai_void.locked_action_id = ac2.assignment_action_id
4872: and pai_void.locking_action_id = paa_void.assignment_action_id
4887: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
4888: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4889: INDEX(as1 PER_ASSIGNMENTS_N4)
4890: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4891: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4892: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4893: USE_NL(pos pop ppp opm as1 act as2) */
4894: act.assignment_action_id,
4895: act.assignment_id,
4898: from pay_payroll_actions pa1,
4899: pay_population_ranges pop,
4900: per_periods_of_service pos,
4901: per_all_assignments_f as1,
4902: pay_assignment_actions act,
4903: pay_payroll_actions pa2,
4904: pay_action_classifications pcl,
4905: per_all_assignments_f as2,
4906: pay_pre_payments ppp,
4934: or pa1.org_payment_method_id is null)
4935: and not exists (
4936: select null
4937: from per_all_assignments_f as3,
4938: pay_assignment_actions ac3
4939: where itpflg = 'N'
4940: and ac3.payroll_action_id = pa2.payroll_action_id
4941: and ac3.action_status not in ('C', 'S')
4942: and as3.assignment_id = ac3.assignment_id
4946: and not exists (
4947: select /*+ ORDERED*/
4948: null
4949: from pay_action_interlocks int,
4950: pay_assignment_actions ac2
4951: where int.locked_action_id = act.assignment_action_id
4952: and ac2.assignment_action_id = int.locking_action_id
4953: and ac2.pre_payment_id = ppp.pre_payment_id
4954: and not exists (
4952: and ac2.assignment_action_id = int.locking_action_id
4953: and ac2.pre_payment_id = ppp.pre_payment_id
4954: and not exists (
4955: select null
4956: from pay_assignment_actions paa_void,
4957: pay_action_interlocks pai_void,
4958: pay_payroll_actions ppa_void
4959: where pai_void.locked_action_id = ac2.assignment_action_id
4960: and pai_void.locking_action_id = paa_void.assignment_action_id
4995: --
4996: -- we need to insert one action for each of the
4997: -- rows that we return from the cursor (i.e. one
4998: -- for each assignment/pre-payment).
4999: select pay_assignment_actions_s.nextval
5000: into lockingactid
5001: from dual;
5002: --
5003: -- insert the action record.
5245: cursor get_existing_person_chunk(p_ppa_id number
5246: ,p_paf_id number)
5247: is
5248: select paa.chunk_number
5249: from pay_assignment_actions paa
5250: , per_all_assignments_f paf
5251: , per_all_people_f ppf
5252: where paa.payroll_action_id = p_ppa_id
5253: and paa.assignment_id = p_paf_id
5262: cursor get_min_chunk(p_ppa_id number)
5263: is
5264: select min(chunk_number)
5265: from (select chunk_number, count(assignment_action_id) ct
5266: from pay_assignment_actions
5267: where payroll_action_id = p_ppa_id
5268: group by chunk_number) v1
5269: where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5270: from pay_assignment_actions
5266: from pay_assignment_actions
5267: where payroll_action_id = p_ppa_id
5268: group by chunk_number) v1
5269: where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5270: from pay_assignment_actions
5271: where payroll_action_id = p_ppa_id
5272: group by chunk_number) v2);
5273: --
5274: l_act_status pay_payroll_actions.action_status%type;
5273: --
5274: l_act_status pay_payroll_actions.action_status%type;
5275: l_act_type pay_payroll_actions.action_type%type;
5276: l_rep_name pay_report_format_mappings_f.report_name%type;
5277: l_chunk pay_assignment_actions.chunk_number%type;
5278: l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5279: --
5280: BEGIN
5281: --
5274: l_act_status pay_payroll_actions.action_status%type;
5275: l_act_type pay_payroll_actions.action_type%type;
5276: l_rep_name pay_report_format_mappings_f.report_name%type;
5277: l_chunk pay_assignment_actions.chunk_number%type;
5278: l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5279: --
5280: BEGIN
5281: --
5282: -- Determine whether new action can be inserted: 1. if payroll_action is
5332: end if;
5333: --
5334: end if;
5335: --
5336: select pay_assignment_actions_s.nextval
5337: into l_asg_act_id
5338: from dual;
5339: --
5340: -- insert the action
5338: from dual;
5339: --
5340: -- insert the action
5341: --
5342: insert into pay_assignment_actions
5343: (assignment_action_id
5344: ,assignment_id
5345: ,payroll_action_id
5346: ,action_status