[Home] [Help]
36: --
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
50: l_date_earned := null;
51: end if;
52: --
53: if (p_action_type in ('R', 'Q', 'B', 'V')) then
54: update pay_payroll_actions pac
55: set pac.action_population_status = p_action_population_status,
56: pac.last_update_date = p_last_update_date,
57: pac.last_updated_by = p_last_updated_by,
58: pac.last_update_login = p_last_update_login
57: pac.last_updated_by = p_last_updated_by,
58: pac.last_update_login = p_last_update_login
59: where pac.payroll_action_id = p_payroll_action_id;
60: elsif (p_action_type not in ('X', 'H')) then
61: update pay_payroll_actions pac
62: set pac.action_population_status = p_action_population_status,
63: pac.last_update_date = p_last_update_date,
64: pac.last_updated_by = p_last_updated_by,
65: pac.last_update_login = p_last_update_login,
65: pac.last_update_login = p_last_update_login,
66: pac.date_earned = l_date_earned
67: where pac.payroll_action_id = p_payroll_action_id;
68: else
69: update pay_payroll_actions pac
70: set pac.action_population_status = p_action_population_status,
71: pac.last_update_date = p_last_update_date,
72: pac.last_updated_by = p_last_updated_by,
73: pac.last_update_login = p_last_update_login,
120: release_on_commit => TRUE);
121: --
122: if (ret <> 0) then
123: hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
124: hr_utility.set_message_token('LOCKNAME','PAY_PAYROLL_ACTIONS_'||pactid);
125: hr_utility.set_message_token('LOCKERR',ret);
126: hr_utility.raise_error;
127: end if;
128: --
127: end if;
128: --
129: select pac.action_population_status
130: into actpopstat
131: from pay_payroll_actions pac
132: where pac.payroll_action_id = pactid;
133: --
134: -- only bother to process if status is not complete.
135: if(actpopstat <> 'C'and actpopstat <> 'A' and actpopstat <> 'E') then
239: ) is
240: l_lckhandle varchar2(128);
241: ret number;
242: chk_pop_status pay_chunk_status.population_status%type;
243: act_pop_status pay_payroll_actions.action_population_status%type;
244: begin
245: /* OK we have the next chunk lets lock it and confirm that its
246: unprocessed
247: */
272: select pcs_rand.population_status,
273: ppa.action_population_status
274: into chk_pop_status,
275: act_pop_status
276: from pay_payroll_actions ppa,
277: pay_chunk_status pcs_pop,
278: pay_chunk_status pcs_rand
279: where pcs_pop.payroll_action_id = pactid
280: and pcs_pop.chunk_number = p_next_chunk
361: next_chunk number;
362: found boolean;
363: l_lckhandle varchar2(128);
364: ret number;
365: act_pop_status pay_payroll_actions.action_population_status%type;
366: norows boolean; -- used to decide if sql stat has returned rows.
367: dummy number; -- need because must select into something.
368: begin
369: --
391: release_on_commit => TRUE);
392: --
393: if (ret <> 0) then
394: hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
395: hr_utility.set_message_token('LOCKNAME','PAY_PAYROLL_ACTIONS_'||pactid);
396: hr_utility.set_message_token('LOCKERR',ret);
397: hr_utility.raise_error;
398: end if;
399: --
400: select
401: ppa.action_population_status
402: into
403: act_pop_status
404: from pay_payroll_actions ppa
405: where ppa.payroll_action_id = pactid;
406: --
407: if ( act_pop_status <> 'C'
408: and act_pop_status <> 'A'
507: rand_chunk out nocopy number -- chunk_number.
508: ) is
509: next_chunk number;
510: found boolean;
511: pay_pop_status pay_payroll_actions.action_population_status%type;
512: chk_pop_status pay_chunk_status.population_status%type;
513: get_paused boolean;
514: begin
515: --
528: get_paused := FALSE;
529: --
530: select action_population_status
531: into pay_pop_status
532: from pay_payroll_actions
533: where payroll_action_id = pactid;
534: --
535: begin
536: --
687: is
688: --
689: next_chunk number;
690: pop_chunk_number number;
691: action_status pay_payroll_actions.action_status%type;
692: action_pop_status pay_payroll_actions.action_population_status%type;
693: l_dummy number;
694: found boolean;
695: --
688: --
689: next_chunk number;
690: pop_chunk_number number;
691: action_status pay_payroll_actions.action_status%type;
692: action_pop_status pay_payroll_actions.action_population_status%type;
693: l_dummy number;
694: found boolean;
695: --
696: begin
703: PAC.action_population_status
704: into next_chunk,
705: action_status,
706: action_pop_status
707: from pay_payroll_actions PAC
708: where PAC.payroll_action_id = pactid
709: for update of PAC.current_chunk_number;
710: --
711: if (action_status = 'C') then
738: end if;
739: --
740: if (got_chunk = TRUE) then
741: --
742: update pay_payroll_actions pac
743: set pac.current_chunk_number = next_chunk
744: where pac.payroll_action_id = pactid;
745: --
746: update pay_chunk_status
812: select action_status,
813: action_population_status
814: into pact_act_status,
815: act_pop_status
816: from pay_payrolL_actions
817: where payroll_action_id = pactid;
818: --
819: if (next_chunk is not null) then
820: --
899: curr_chunk in out nocopy number -- current chunk
900: ) is
901: next_chunk number;
902: found boolean;
903: pact_act_status pay_payroll_actions.action_status%type;
904: act_pop_status pay_payroll_actions.action_population_status%type;
905: chk_status pay_chunk_status.process_status%type;
906: begin
907: --
900: ) is
901: next_chunk number;
902: found boolean;
903: pact_act_status pay_payroll_actions.action_status%type;
904: act_pop_status pay_payroll_actions.action_population_status%type;
905: chk_status pay_chunk_status.process_status%type;
906: begin
907: --
908: found := FALSE;
917: --
918: select action_status, action_population_status
919: into pact_act_status,
920: act_pop_status
921: from pay_payroll_actions
922: where payroll_action_id = pactid;
923: --
924: begin
925: --
1149: cursor get_cost_lockers (p_cost_act number, p_run_act number)
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
1279: p_transient_action in boolean default false -- Transient Action
1280: ) is
1281: --
1282: l_transient_action boolean;
1283: l_action_type pay_payroll_actions.action_type%type;
1284: l_report_type pay_payroll_actions.report_type%type;
1285: l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286: l_report_category pay_payroll_actions.report_category%type;
1287: l_eff_date pay_payroll_actions.effective_date%type;
1280: ) is
1281: --
1282: l_transient_action boolean;
1283: l_action_type pay_payroll_actions.action_type%type;
1284: l_report_type pay_payroll_actions.report_type%type;
1285: l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286: l_report_category pay_payroll_actions.report_category%type;
1287: l_eff_date pay_payroll_actions.effective_date%type;
1288: l_temp_act_flag pay_report_format_mappings_f.temporary_action_flag%type;
1281: --
1282: l_transient_action boolean;
1283: l_action_type pay_payroll_actions.action_type%type;
1284: l_report_type pay_payroll_actions.report_type%type;
1285: l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286: l_report_category pay_payroll_actions.report_category%type;
1287: l_eff_date pay_payroll_actions.effective_date%type;
1288: l_temp_act_flag pay_report_format_mappings_f.temporary_action_flag%type;
1289: --
1282: l_transient_action boolean;
1283: l_action_type pay_payroll_actions.action_type%type;
1284: l_report_type pay_payroll_actions.report_type%type;
1285: l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286: l_report_category pay_payroll_actions.report_category%type;
1287: l_eff_date pay_payroll_actions.effective_date%type;
1288: l_temp_act_flag pay_report_format_mappings_f.temporary_action_flag%type;
1289: --
1290: begin
1283: l_action_type pay_payroll_actions.action_type%type;
1284: l_report_type pay_payroll_actions.report_type%type;
1285: l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286: l_report_category pay_payroll_actions.report_category%type;
1287: l_eff_date pay_payroll_actions.effective_date%type;
1288: l_temp_act_flag pay_report_format_mappings_f.temporary_action_flag%type;
1289: --
1290: begin
1291: --
1298: l_report_type,
1299: l_report_qualifier,
1300: l_report_category,
1301: l_eff_date
1302: from pay_payroll_actions
1303: where payroll_action_id = pactid;
1304: --
1305: l_transient_action := FALSE;
1306: --
1414: chunk number,
1415: itpflg varchar2
1416: ) is
1417: select /*+ ORDERED
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)
1425: act.assignment_action_id,
1426: act.assignment_id,
1427: act.tax_unit_id,
1428: ppp.pre_payment_id
1429: from pay_payroll_actions pa1,
1430: pay_payroll_actions pa2,
1431: pay_action_classifications pcl,
1432: pay_population_ranges pop,
1433: per_periods_of_service pos,
1426: act.assignment_id,
1427: act.tax_unit_id,
1428: ppp.pre_payment_id
1429: from pay_payroll_actions pa1,
1430: pay_payroll_actions pa2,
1431: pay_action_classifications pcl,
1432: pay_population_ranges pop,
1433: per_periods_of_service pos,
1434: per_all_assignments_f as1,
1491: endperson number,
1492: itpflg varchar2
1493: ) is
1494: select /*+ ORDERED
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)
1502: act.assignment_action_id,
1503: act.assignment_id,
1504: act.tax_unit_id,
1505: ppp.pre_payment_id
1506: from pay_payroll_actions pa1,
1507: pay_payroll_actions pa2,
1508: pay_action_classifications pcl,
1509: per_periods_of_service pos,
1510: per_all_assignments_f as1,
1503: act.assignment_id,
1504: act.tax_unit_id,
1505: ppp.pre_payment_id
1506: from pay_payroll_actions pa1,
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,
1564: chunk number,
1565: itpflg varchar2
1566: ) is
1567: select /*+ ORDERED
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)
1575: act.assignment_action_id,
1576: act.assignment_id,
1577: act.tax_unit_id,
1578: ppp.pre_payment_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,
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,
1588: pay_org_payment_methods_f opm
1705: class varchar2,
1706: itpflg varchar2
1707: ) is
1708: select /*+ ORDERED
1709: index(pa2 PAY_PAYROLL_ACTIONS_N5)
1710: index(as1 PER_ASSIGNMENTS_F_N4)
1711: USE_NL(pos as1) */
1712: act.assignment_action_id,
1713: act.assignment_id,
1711: USE_NL(pos as1) */
1712: act.assignment_action_id,
1713: act.assignment_id,
1714: act.tax_unit_id
1715: from pay_payroll_actions pa1,
1716: pay_payroll_actions pa2,
1717: pay_action_classifications pcl,
1718: per_periods_of_service pos,
1719: per_all_assignments_f as1,
1712: act.assignment_action_id,
1713: act.assignment_id,
1714: act.tax_unit_id
1715: from pay_payroll_actions pa1,
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
1768: class varchar2,
1769: itpflg varchar2
1770: ) is
1771: select /*+ ORDERED
1772: index(pa2 PAY_PAYROLL_ACTIONS_N5)
1773: index(as1 PER_ASSIGNMENTS_F_N4)
1774: USE_NL(pos as1) */
1775: act.assignment_action_id,
1776: act.assignment_id,
1774: USE_NL(pos as1) */
1775: act.assignment_action_id,
1776: act.assignment_id,
1777: act.tax_unit_id
1778: from pay_payroll_actions pa1,
1779: pay_payroll_actions pa2,
1780: pay_action_classifications pcl,
1781: pay_population_ranges pop,
1782: per_periods_of_service pos,
1775: act.assignment_action_id,
1776: act.assignment_id,
1777: act.tax_unit_id
1778: from pay_payroll_actions pa1,
1779: pay_payroll_actions pa2,
1780: pay_action_classifications pcl,
1781: pay_population_ranges pop,
1782: per_periods_of_service pos,
1783: per_all_assignments_f as1,
1911: */
1912: pcp.assignment_action_id,
1913: hou.organization_id,
1914: ppp.pre_payment_id
1915: from pay_payroll_actions pa1,
1916: pay_payroll_actions pa2,
1917: pay_action_classifications pcl,
1918: pay_population_ranges pop,
1919: hr_organization_units hou,
1912: pcp.assignment_action_id,
1913: hou.organization_id,
1914: ppp.pre_payment_id
1915: from pay_payroll_actions pa1,
1916: pay_payroll_actions pa2,
1917: pay_action_classifications pcl,
1918: pay_population_ranges pop,
1919: hr_organization_units hou,
1920: pay_pre_payments ppp,
1970: if (g_contrib_payments_exist is null) then
1971: begin
1972: select 1
1973: into l_cp
1974: from pay_payroll_actions pa1
1975: where pa1.payroll_action_id = pactid
1976: and exists
1977: (select 1
1978: from pay_payroll_actions pa2,
1974: from pay_payroll_actions pa1
1975: where pa1.payroll_action_id = pactid
1976: and exists
1977: (select 1
1978: from pay_payroll_actions pa2,
1979: pay_contributing_payments pcp
1980: where pa2.payroll_action_id = pcp.payroll_action_id
1981: and pa2.action_type = 'PRU'
1982: and pa2.business_group_id = pa1.business_group_id);
2052: ptype number,
2053: class varchar2
2054: ) is
2055: SELECT /*+ ORDERED
2056: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2057: index(as1 PER_ASSIGNMENTS_F_N4)
2058: index(as2 PER_ASSIGNMENTS_F_PK)
2059: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2060: USE_NL(pop pos as1 as2) */
2063: act.tax_unit_id,
2064: ppp.pre_payment_id,
2065: pa1.assignment_set_id,
2066: as1.payroll_id
2067: from pay_payroll_actions pa1,
2068: pay_payroll_actions pa2,
2069: pay_action_classifications pcl,
2070: pay_population_ranges pop,
2071: per_periods_of_service pos,
2064: ppp.pre_payment_id,
2065: pa1.assignment_set_id,
2066: as1.payroll_id
2067: from pay_payroll_actions pa1,
2068: pay_payroll_actions pa2,
2069: pay_action_classifications pcl,
2070: pay_population_ranges pop,
2071: per_periods_of_service pos,
2072: per_all_assignments_f as1,
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
2120: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2121: and ppa_void.action_type = 'D')
2144: ptype number,
2145: class varchar2
2146: ) is
2147: SELECT /*+ ORDERED
2148: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2149: index(as1 PER_ASSIGNMENTS_F_N4)
2150: index(as2 PER_ASSIGNMENTS_F_PK)
2151: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2152: USE_NL(pos as1 as2) */
2155: act.tax_unit_id,
2156: ppp.pre_payment_id,
2157: pa1.assignment_set_id,
2158: as1.payroll_id
2159: from pay_payroll_actions pa1,
2160: pay_payroll_actions pa2,
2161: pay_action_classifications pcl,
2162: per_periods_of_service pos,
2163: per_all_assignments_f as1,
2156: ppp.pre_payment_id,
2157: pa1.assignment_set_id,
2158: as1.payroll_id
2159: from pay_payroll_actions pa1,
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,
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
2209: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2210: and ppa_void.action_type = 'D')
2239: act.tax_unit_id,
2240: ppp.pre_payment_id,
2241: pa1.assignment_set_id,
2242: as1.payroll_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,
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,
2252: pay_org_payment_methods_f opm
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
2296: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2297: and ppa_void.action_type = 'D')
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
2351: where lck1.locked_action_id = plockedid
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
2354: and chk_ppa.action_type = 'H'
2473: itpflg varchar2,
2474: ptype number
2475: ) is
2476: select /*+ ORDERED
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)
2484: act.assignment_action_id,
2485: act.assignment_id,
2486: act.tax_unit_id,
2487: ppp.pre_payment_id
2488: from pay_payroll_actions pa1,
2489: pay_payroll_actions pa2,
2490: pay_action_classifications pcl,
2491: pay_population_ranges pop,
2492: per_periods_of_service pos,
2485: act.assignment_id,
2486: act.tax_unit_id,
2487: ppp.pre_payment_id
2488: from pay_payroll_actions pa1,
2489: pay_payroll_actions pa2,
2490: pay_action_classifications pcl,
2491: pay_population_ranges pop,
2492: per_periods_of_service pos,
2493: per_all_assignments_f as1,
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
2551: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2552: and ppa_void.action_type = 'D')
2562: itpflg varchar2,
2563: ptype number
2564: ) is
2565: select /*+ ORDERED
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)
2573: act.assignment_action_id,
2574: act.assignment_id,
2575: act.tax_unit_id,
2576: ppp.pre_payment_id
2577: from pay_payroll_actions pa1,
2578: pay_payroll_actions pa2,
2579: pay_action_classifications pcl,
2580: per_periods_of_service pos,
2581: per_all_assignments_f as1,
2574: act.assignment_id,
2575: act.tax_unit_id,
2576: ppp.pre_payment_id
2577: from pay_payroll_actions pa1,
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,
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
2637: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2638: and ppa_void.action_type = 'D')
2647: itpflg varchar2,
2648: ptype number
2649: ) is
2650: select /*+ ORDERED
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)
2658: act.assignment_action_id,
2659: act.assignment_id,
2660: act.tax_unit_id,
2661: ppp.pre_payment_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,
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,
2671: pay_org_payment_methods_f opm
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
2725: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2726: and ppa_void.action_type = 'D')
2821: class varchar2,
2822: itpflg varchar2
2823: ) is
2824: select /*+ ORDERED
2825: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2826: index(as1 PER_ASSIGNMENTS_F_N4)
2827: index(as2 PER_ASSIGNMENTS_F_PK)
2828: USE_NL(pop pos as1) */
2829: act.assignment_action_id,
2831: act.tax_unit_id,
2832: as1.person_id,
2833: as1.effective_start_date,
2834: as1.primary_flag
2835: from pay_payroll_actions pa1,
2836: pay_payroll_actions pa2,
2837: pay_action_classifications pcl,
2838: pay_population_ranges pop,
2839: per_periods_of_service pos,
2832: as1.person_id,
2833: as1.effective_start_date,
2834: as1.primary_flag
2835: from pay_payroll_actions pa1,
2836: pay_payroll_actions pa2,
2837: pay_action_classifications pcl,
2838: pay_population_ranges pop,
2839: per_periods_of_service pos,
2840: per_all_assignments_f as1,
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
2872: and pa3.payroll_action_id = ac2.payroll_action_id
2894: class varchar2,
2895: itpflg varchar2
2896: ) is
2897: select /*+ ORDERED
2898: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2899: index(as1 PER_ASSIGNMENTS_F_N4)
2900: index(as2 PER_ASSIGNMENTS_F_PK)
2901: USE_NL(pos as1) */
2902: act.assignment_action_id,
2904: act.tax_unit_id,
2905: as1.person_id,
2906: as1.effective_start_date,
2907: as1.primary_flag
2908: from pay_payroll_actions pa1,
2909: pay_payroll_actions pa2,
2910: pay_action_classifications pcl,
2911: per_periods_of_service pos,
2912: per_all_assignments_f as1,
2905: as1.person_id,
2906: as1.effective_start_date,
2907: as1.primary_flag
2908: from pay_payroll_actions pa1,
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,
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
2942: and pa3.payroll_action_id = ac2.payroll_action_id
2963: class varchar2,
2964: itpflg varchar2
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)
2975: act.tax_unit_id,
2976: as1.person_id,
2977: as1.effective_start_date,
2978: as1.primary_flag
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,
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
2988: and pa2.payroll_id = pa1.payroll_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
3016: and pa3.payroll_action_id = ac2.payroll_action_id
3137: class varchar2,
3138: itpflg varchar2
3139: ) is
3140: select /*+ ORDERED
3141: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3142: index(as1 PER_ASSIGNMENTS_F_N4)
3143: index(as2 PER_ASSIGNMENTS_F_PK)
3144: USE_NL(pos pop as1) */
3145: act.assignment_action_id,
3144: USE_NL(pos pop as1) */
3145: act.assignment_action_id,
3146: act.assignment_id,
3147: act.tax_unit_id
3148: from pay_payroll_actions pa1,
3149: pay_payroll_actions pa2,
3150: pay_action_classifications pcl,
3151: pay_population_ranges pop,
3152: per_periods_of_service pos,
3145: act.assignment_action_id,
3146: act.assignment_id,
3147: act.tax_unit_id
3148: from pay_payroll_actions pa1,
3149: pay_payroll_actions pa2,
3150: pay_action_classifications pcl,
3151: pay_population_ranges pop,
3152: per_periods_of_service pos,
3153: per_all_assignments_f as1,
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
3182: and pa3.payroll_action_id = ac2.payroll_action_id
3204: class varchar2,
3205: itpflg varchar2
3206: ) is
3207: select /*+ ORDERED
3208: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3209: index(as1 PER_ASSIGNMENTS_F_N4)
3210: index(as2 PER_ASSIGNMENTS_F_PK)
3211: USE_NL(pos as1) */
3212: act.assignment_action_id,
3211: USE_NL(pos as1) */
3212: act.assignment_action_id,
3213: act.assignment_id,
3214: act.tax_unit_id
3215: from pay_payroll_actions pa1,
3216: pay_payroll_actions pa2,
3217: pay_action_classifications pcl,
3218: per_periods_of_service pos,
3219: per_all_assignments_f as1,
3212: act.assignment_action_id,
3213: act.assignment_id,
3214: act.tax_unit_id
3215: from pay_payroll_actions pa1,
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,
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
3246: and pa3.payroll_action_id = ac2.payroll_action_id
3267: class varchar2,
3268: itpflg varchar2
3269: ) is
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,
3279: act.tax_unit_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,
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
3289: and pa2.consolidation_set_id = pa1.consolidation_set_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
3314: and pa3.payroll_action_id = ac2.payroll_action_id
3411: class varchar2,
3412: itpflg varchar2
3413: ) is
3414: select /*+ ORDERED
3415: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3416: index(as1 PER_ASSIGNMENTS_F_N4)
3417: index(as2 PER_ASSIGNMENTS_F_PK)
3418: USE_NL(pos pop as1) */
3419: act.assignment_action_id,
3419: act.assignment_action_id,
3420: act.assignment_id,
3421: act.tax_unit_id,
3422: act.payroll_action_id
3423: from pay_payroll_actions pa1,
3424: pay_payroll_actions pa2,
3425: pay_action_classifications pcl,
3426: pay_population_ranges pop,
3427: per_periods_of_service pos,
3420: act.assignment_id,
3421: act.tax_unit_id,
3422: act.payroll_action_id
3423: from pay_payroll_actions pa1,
3424: pay_payroll_actions pa2,
3425: pay_action_classifications pcl,
3426: pay_population_ranges pop,
3427: per_periods_of_service pos,
3428: per_all_assignments_f as1,
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
3457: and pa3.payroll_action_id = ac2.payroll_action_id
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
3515: where int.locking_action_id = act.assignment_action_id
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
3533: and chq.assignment_action_id = int.locked_action_id
3550: class varchar2,
3551: itpflg varchar2
3552: ) is
3553: select /*+ ORDERED
3554: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3555: index(as1 PER_ASSIGNMENTS_F_N4)
3556: index(as2 PER_ASSIGNMENTS_F_PK)
3557: USE_NL(pos as1) */
3558: act.assignment_action_id,
3558: act.assignment_action_id,
3559: act.assignment_id,
3560: act.tax_unit_id,
3561: act.payroll_action_id
3562: from pay_payroll_actions pa1,
3563: pay_payroll_actions pa2,
3564: pay_action_classifications pcl,
3565: per_periods_of_service pos,
3566: per_all_assignments_f as1,
3559: act.assignment_id,
3560: act.tax_unit_id,
3561: act.payroll_action_id
3562: from pay_payroll_actions pa1,
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,
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
3593: and pa3.payroll_action_id = ac2.payroll_action_id
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
3651: where int.locking_action_id = act.assignment_action_id
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
3669: and chq.assignment_action_id = int.locked_action_id
3685: class varchar2,
3686: itpflg varchar2
3687: ) is
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)
3695: act.assignment_action_id,
3696: act.assignment_id,
3697: act.tax_unit_id,
3698: act.payroll_action_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,
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
3708: and pa2.consolidation_set_id = pa1.consolidation_set_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
3733: and pa3.payroll_action_id = ac2.payroll_action_id
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
3791: where int.locking_action_id = act.assignment_action_id
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
3809: and chq.assignment_action_id = int.locked_action_id
3906: class varchar2,
3907: itpflg varchar2
3908: ) is
3909: select /*+ ORDERED
3910: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3911: index(as1 PER_ASSIGNMENTS_F_N4)
3912: index(as2 PER_ASSIGNMENTS_F_PK)
3913: USE_NL(pop pos as1) */
3914: act.assignment_action_id,
3913: USE_NL(pop pos as1) */
3914: act.assignment_action_id,
3915: act.assignment_id,
3916: act.tax_unit_id
3917: from pay_payroll_actions pa1,
3918: pay_all_payrolls_f pay,
3919: per_time_periods ptp,
3920: pay_payroll_actions pa2,
3921: pay_action_classifications pcl,
3916: act.tax_unit_id
3917: from pay_payroll_actions pa1,
3918: pay_all_payrolls_f pay,
3919: per_time_periods ptp,
3920: pay_payroll_actions pa2,
3921: pay_action_classifications pcl,
3922: pay_population_ranges pop,
3923: per_periods_of_service pos,
3924: per_all_assignments_f as1,
3973: class varchar2,
3974: itpflg varchar2
3975: ) is
3976: select /*+ ORDERED
3977: index(pa2 PAY_PAYROLL_ACTIONS_N5)
3978: index(as1 PER_ASSIGNMENTS_F_N4)
3979: index(as2 PER_ASSIGNMENTS_F_PK)
3980: USE_NL(pos as1) */
3981: act.assignment_action_id,
3980: USE_NL(pos as1) */
3981: act.assignment_action_id,
3982: act.assignment_id,
3983: act.tax_unit_id
3984: from pay_payroll_actions pa1,
3985: pay_all_payrolls_f pay,
3986: per_time_periods ptp,
3987: pay_payroll_actions pa2,
3988: pay_action_classifications pcl,
3983: act.tax_unit_id
3984: from pay_payroll_actions pa1,
3985: pay_all_payrolls_f pay,
3986: per_time_periods ptp,
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,
4100: pactid number,
4101: chunk number
4102: ) is
4103: select asg.assignment_id
4104: from pay_payroll_actions pac,
4105: pay_population_ranges pop,
4106: pay_batch_headers bth,
4107: pay_batch_lines btl,
4108: per_all_assignments_f asg
4125: stperson number,
4126: endperson number
4127: ) is
4128: select asg.assignment_id
4129: from pay_payroll_actions pac,
4130: pay_batch_lines btl,
4131: per_all_assignments_f asg
4132: where pac.payroll_action_id = pactid
4133: and pac.action_type = 'BEE'
4210: chunk number,
4211: itpflg varchar2
4212: ) is
4213: select /*+ ORDERED
4214: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4215: index(as1 PER_ASSIGNMENTS_F_PK)
4216: index(as2 PER_ASSIGNMENTS_F_N4)
4217: USE_NL(pop pos as1 as2) */
4218: ac2.assignment_action_id,
4218: ac2.assignment_action_id,
4219: ac2.assignment_id,
4220: ac2.tax_unit_id,
4221: pa2.action_type
4222: from pay_payroll_actions pa,
4223: pay_payroll_actions pa2,
4224: pay_action_classifications pcl,
4225: pay_population_ranges pop,
4226: per_periods_of_service pos,
4219: ac2.assignment_id,
4220: ac2.tax_unit_id,
4221: pa2.action_type
4222: from pay_payroll_actions pa,
4223: pay_payroll_actions pa2,
4224: pay_action_classifications pcl,
4225: pay_population_ranges pop,
4226: per_periods_of_service pos,
4227: per_all_assignments_f as2,
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
4258: and pa3.payroll_action_id = ac3.payroll_action_id
4279: endperson number,
4280: itpflg varchar2
4281: ) is
4282: select /*+ ORDERED
4283: index(pa2 PAY_PAYROLL_ACTIONS_N5)
4284: index(as1 PER_ASSIGNMENTS_F_PK)
4285: index(as2 PER_ASSIGNMENTS_F_N4)
4286: USE_NL(pos as1 as2) */
4287: ac2.assignment_action_id,
4287: ac2.assignment_action_id,
4288: ac2.assignment_id,
4289: ac2.tax_unit_id,
4290: pa2.action_type
4291: from pay_payroll_actions pa,
4292: pay_payroll_actions pa2,
4293: pay_action_classifications pcl,
4294: per_periods_of_service pos,
4295: per_all_assignments_f as2,
4288: ac2.assignment_id,
4289: ac2.tax_unit_id,
4290: pa2.action_type
4291: from pay_payroll_actions pa,
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,
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
4325: and pa3.payroll_action_id = ac3.payroll_action_id
4345: chunk number,
4346: itpflg varchar2
4347: ) is
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)
4355: ac2.assignment_action_id,
4356: ac2.assignment_id,
4357: ac2.tax_unit_id,
4358: pa2.action_type
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,
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
4368: and pa2.consolidation_set_id = pa.consolidation_set_id
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
4395: and pa3.payroll_action_id = ac3.payroll_action_id
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
4424: where pa.payroll_action_id = pactid
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
4424: where pa.payroll_action_id = pactid
4425: and in2.locking_action_id = costactid
4426: and ac1.assignment_action_id = in2.locked_action_id
4427: and ac1.source_action_id is null
4435: --
4436: lockingactid number;
4437: lockedactid number;
4438: assignid number;
4439: actype pay_payroll_actions.action_type%TYPE;
4440: pmnt_act_type pay_payroll_actions.action_type%TYPE;
4441: prepay_aa_id number;
4442: runactid number;
4443: greid number;
4436: lockingactid number;
4437: lockedactid number;
4438: assignid number;
4439: actype pay_payroll_actions.action_type%TYPE;
4440: pmnt_act_type pay_payroll_actions.action_type%TYPE;
4441: prepay_aa_id number;
4442: runactid number;
4443: greid number;
4444: not_paid number;
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;
4511:
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 (
4586: select 1
4592: lockingactid number;
4593: lockedactid number;
4594: assignid number;
4595: greid number;
4596: atype pay_payroll_actions.action_type%type;
4597: --
4598: begin
4599: open qpcur(pactid);
4600: fetch qpcur into lockedactid, assignid, greid, atype;
4645: begin
4646: select assignment_action_code
4647: into action_proc
4648: from pay_report_format_mappings_f prfm,
4649: pay_payroll_actions ppa
4650: where ppa.payroll_action_id = pactid
4651: and ppa.report_type = prfm.report_type
4652: and ppa.report_qualifier = prfm.report_qualifier
4653: and ppa.report_category = prfm.report_category
4709: itpflg varchar2,
4710: ptype number
4711: ) is
4712: select /*+ ORDERED
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)
4720: act.assignment_action_id,
4721: act.assignment_id,
4722: act.tax_unit_id,
4723: ppp.pre_payment_id
4724: from pay_payroll_actions pa1,
4725: pay_payroll_actions pa2,
4726: pay_action_classifications pcl,
4727: pay_population_ranges pop,
4728: per_periods_of_service pos,
4721: act.assignment_id,
4722: act.tax_unit_id,
4723: ppp.pre_payment_id
4724: from pay_payroll_actions pa1,
4725: pay_payroll_actions pa2,
4726: pay_action_classifications pcl,
4727: pay_population_ranges pop,
4728: per_periods_of_service pos,
4729: per_all_assignments_f as1,
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
4787: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4788: and ppa_void.action_type = 'D')
4798: itpflg varchar2,
4799: ptype number
4800: ) is
4801: select /*+ ORDERED
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)
4809: act.assignment_action_id,
4810: act.assignment_id,
4811: act.tax_unit_id,
4812: ppp.pre_payment_id
4813: from pay_payroll_actions pa1,
4814: pay_payroll_actions pa2,
4815: pay_action_classifications pcl,
4816: per_periods_of_service pos,
4817: per_all_assignments_f as1,
4810: act.assignment_id,
4811: act.tax_unit_id,
4812: ppp.pre_payment_id
4813: from pay_payroll_actions pa1,
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,
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
4873: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4874: and ppa_void.action_type = 'D')
4883: itpflg varchar2,
4884: ptype number
4885: ) is
4886: select /*+ ORDERED
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)
4894: act.assignment_action_id,
4895: act.assignment_id,
4896: act.tax_unit_id,
4897: ppp.pre_payment_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,
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,
4907: pay_org_payment_methods_f opm
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
4961: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4962: and ppa_void.action_type = 'D')
5072: elsif (atype = PREPAY) then
5073: select nvl(multi_assignments_flag, 'N')
5074: into multi_asg_fg
5075: from pay_all_payrolls_f prl,
5076: pay_payroll_Actions pact
5077: where pact.payroll_action_id = pactid
5078: and prl.payroll_id = pact.payroll_id
5079: and pact.effective_date between prl.effective_start_date
5080: and prl.effective_end_date;
5124: g_many_procs_in_period := 'Y';
5125: end if;
5126: --
5127: dbms_lock.allocate_unique(
5128: lockname => 'PAY_PAYROLL_ACTIONS_'||pactid,
5129: lockhandle => g_lckhandle);
5130: --
5131: loop
5132: -- start by processing the range row.
5234: is
5235: select ppa.action_status
5236: , ppa.action_type
5237: , rfm.report_name
5238: from pay_payroll_actions ppa
5239: , pay_report_format_mappings_f rfm
5240: where ppa.payroll_action_id = p_pact_id
5241: and ppa.report_type = rfm.report_type(+)
5242: and ppa.report_qualifier = rfm.report_qualifier(+)
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;
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;
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;
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: --