[Home] [Help]
17: bg_id hr_organization_units.business_group_id%type,
18: independent_periods_flag pay_legislation_rules.rule_mode%type,
19: date_earned date,
20: purge_phase pay_payroll_actions.purge_phase%type,
21: object_type pay_assignment_actions.object_type%type,
22: retro_definition_id pay_payroll_actions.retro_definition_id%type,
23: batch_id pay_payroll_actions.batch_id%type,
24: --
25: -- Assignment Action Level Details.
22: retro_definition_id pay_payroll_actions.retro_definition_id%type,
23: batch_id pay_payroll_actions.batch_id%type,
24: --
25: -- Assignment Action Level Details.
26: assact_id pay_assignment_actions.assignment_action_id%type,
27: assignment_id per_all_assignments_f.assignment_id%type,
28: full_name per_all_people_f.full_name%type,
29: assignment_number per_all_assignments_f.assignment_number%type,
30: payroll_id pay_all_payrolls_f.payroll_id%type,
233: c_indent varchar2(40);
234: cursor ceev is
235: select pee.element_entry_id
236: from pay_element_entries_f pee,
237: pay_assignment_actions paa
238: where paa.assignment_action_id = p_assact_id
239: and pee.assignment_id = paa.assignment_id
240: and pee.creator_id = paa.assignment_action_id
241: and pee.creator_type = 'P';
269: c_indent varchar2(40);
270: cursor aeev is
271: select pee.element_entry_id
272: from pay_element_entries_f pee,
273: pay_assignment_actions paa
274: where paa.assignment_action_id = p_assact_id
275: and pee.assignment_id = paa.assignment_id
276: and pee.creator_id = paa.assignment_action_id
277: and pee.creator_type = 'D';
303: c_indent varchar2(40);
304: cursor aeev is
305: select pee.element_entry_id
306: from pay_element_entries_f pee,
307: pay_assignment_actions paa
308: where paa.assignment_action_id = p_assact_id
309: and pee.assignment_id = paa.assignment_id
310: and pee.creator_id = paa.assignment_action_id
311: and pee.creator_type in ('AD', 'AE','D');
336: c_indent varchar2(40);
337: cursor raeev is
338: select pee.element_entry_id
339: from pay_element_entries_f pee,
340: pay_assignment_actions paa
341: where paa.assignment_action_id = p_assact_id
342: and pee.assignment_id = paa.assignment_id
343: and pee.creator_id = paa.assignment_action_id
344: and pee.creator_type = 'R';
370: c_indent varchar2(40);
371: cursor remove_eev is
372: select pee.element_entry_id
373: from pay_element_entries_f pee,
374: pay_assignment_actions paa
375: where paa.assignment_action_id = p_assact_id
376: and pee.creator_id = paa.assignment_action_id
377: and pee.creator_type in ('RR', 'EE', 'NR', 'PR') ;
378: --
508: pay_personal_payment_methods_f ppm,
509: pay_org_payment_methods_f opm,
510: pay_pre_payments ppp,
511: pay_payroll_actions ppa,
512: pay_assignment_actions paa
513: where paa.assignment_action_id = asgact
514: and paa.payroll_action_id = ppa.payroll_action_id
515: and paa.pre_payment_id = ppp.pre_payment_id
516: and ppp.org_payment_method_id = opm.org_payment_method_id
998: -- for an assignment action.
999: cursor c1 is
1000: select pee.element_entry_id
1001: from pay_element_entries_f pee,
1002: pay_assignment_actions paa
1003: where paa.assignment_action_id = p_assact_id
1004: and pee.assignment_id = paa.assignment_id
1005: and pee.creator_id = paa.assignment_action_id
1006: and pee.creator_type = 'B' -- (B)alance Adjustment
1150: end;
1151: --
1152: /* get the object Types for this payroll_action */
1153: begin
1154: select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N50)*/
1155: decode (ppa.action_type,
1156: 'X', paa.object_type,
1157: decode(paa.object_type,
1158: 'PAF', null,
1157: decode(paa.object_type,
1158: 'PAF', null,
1159: paa.object_type))
1160: into p_info.object_type
1161: from pay_assignment_actions paa,
1162: pay_payroll_actions ppa
1163: where paa.payroll_action_id = p_info.pact_id
1164: and p_info.pact_id = ppa.payroll_action_id
1165: and paa.source_action_id is null
1594: if g_debug then
1595: hr_utility.set_location(c_indent, 10);
1596: end if;
1597: --
1598: update pay_assignment_actions
1599: set action_status = 'M'
1600: where source_action_id = p_info.assact_id;
1601: --
1602: update pay_assignment_actions
1598: update pay_assignment_actions
1599: set action_status = 'M'
1600: where source_action_id = p_info.assact_id;
1601: --
1602: update pay_assignment_actions
1603: set action_status = 'M'
1604: where assignment_action_id = p_info.assact_id;
1605: --
1606: elsif(p_info.rollback_mode) = 'BACKPAY' then
1607: if g_debug then
1608: hr_utility.set_location(c_indent, 20);
1609: end if;
1610: --
1611: update pay_assignment_actions
1612: set action_status = 'B'
1613: where source_action_id = p_info.assact_id;
1614: --
1615: update pay_assignment_actions
1611: update pay_assignment_actions
1612: set action_status = 'B'
1613: where source_action_id = p_info.assact_id;
1614: --
1615: update pay_assignment_actions
1616: set action_status = 'B'
1617: where assignment_action_id = p_info.assact_id;
1618: --
1619: elsif(p_info.rollback_mode) = 'ROLLBACK' then
1627: --
1628: remove_archive_items(p_info, p_info.assact_id, 'AAC');
1629: remove_action_information(p_info.assact_id, 'AAC');
1630: --
1631: delete from pay_assignment_actions
1632: where source_action_id = p_info.assact_id;
1633: --
1634: delete from pay_assignment_actions
1635: where assignment_action_id = p_info.assact_id;
1630: --
1631: delete from pay_assignment_actions
1632: where source_action_id = p_info.assact_id;
1633: --
1634: delete from pay_assignment_actions
1635: where assignment_action_id = p_info.assact_id;
1636: end if;
1637: else
1638: -- In the case of rolling back (from the form), we
1642: --
1643: remove_archive_items(p_info, p_info.assact_id, 'AAC');
1644: remove_action_information(p_info.assact_id, 'AAC');
1645: --
1646: delete from pay_assignment_actions
1647: where source_action_id = p_info.assact_id;
1648: --
1649: delete from pay_action_interlocks lck
1650: where lck.locking_action_id = p_info.assact_id;
1681: v_time_period_id per_time_periods.time_period_id%TYPE;
1682: --
1683: cursor chdact (p_asgact_id in number) is
1684: select paa_chd.assignment_action_id
1685: from pay_assignment_actions paa_chd
1686: where paa_chd.source_action_id = p_asgact_id
1687: order by paa_chd.action_sequence desc;
1688: --
1689: cursor get_person_id (p_assignment_id in per_all_people_f.person_id%TYPE, p_effective_date in date) is
1735: where locking_action_id = chld_info.assact_id;
1736: if g_debug then
1737: hr_utility.set_location('About to remove assg actions' , 11);
1738: end if;
1739: delete from pay_assignment_actions
1740: where assignment_action_id = chld_info.assact_id;
1741: end if;
1742: end loop;
1743: --
1792: begin
1793: --
1794: select ppa.element_type_id
1795: into l_et_id
1796: from pay_assignment_actions paa,
1797: pay_payroll_actions ppa
1798: where ppa.payroll_action_id = paa.payroll_action_id
1799: and paa.assignment_action_id = p_info.assact_id;
1800: --
2017: * to obtain payroll action level information.
2018: */
2019: procedure val_assact_rollback(p_info in out nocopy rollback_rec)
2020: is
2021: l_action_sequence pay_assignment_actions.action_sequence%type;
2022: l_action_status pay_assignment_actions.action_status%type;
2023: l_person_id per_all_people_f.person_id%type;
2024: l_sec_status pay_assignment_actions.secondary_status%type;
2025: c_indent varchar2(40);
2018: */
2019: procedure val_assact_rollback(p_info in out nocopy rollback_rec)
2020: is
2021: l_action_sequence pay_assignment_actions.action_sequence%type;
2022: l_action_status pay_assignment_actions.action_status%type;
2023: l_person_id per_all_people_f.person_id%type;
2024: l_sec_status pay_assignment_actions.secondary_status%type;
2025: c_indent varchar2(40);
2026: begin
2020: is
2021: l_action_sequence pay_assignment_actions.action_sequence%type;
2022: l_action_status pay_assignment_actions.action_status%type;
2023: l_person_id per_all_people_f.person_id%type;
2024: l_sec_status pay_assignment_actions.secondary_status%type;
2025: c_indent varchar2(40);
2026: begin
2027: -- Obtain information about this assignment action which we will
2028: -- need later on.
2064: p_info.assignment_number
2065: from
2066: per_all_people_f PEO,
2067: pay_object_groups POG_PER,
2068: pay_assignment_actions ACT
2069: where ACT.assignment_action_id = p_info.assact_id
2070: and ACT.source_action_id is null
2071: and ACT.object_id = POG_PER.object_group_id
2072: and POG_PER.source_id = PEO.person_id
2091: p_info.payroll_id,
2092: l_person_id,
2093: p_info.full_name,
2094: p_info.assignment_number
2095: from pay_assignment_actions ACT
2096: where ACT.assignment_action_id = p_info.assact_id;
2097: --
2098: end if;
2099: --
2119: p_info.full_name,
2120: p_info.assignment_number
2121: from per_all_assignments_f ASS,
2122: per_all_people_f PEO,
2123: pay_assignment_actions ACT
2124: where ACT.assignment_action_id = p_info.assact_id
2125: and ASS.assignment_id = ACT.assignment_id
2126: and PEO.person_id = ASS.person_id
2127: and ((p_info.action_type = 'X'
2238: from dual
2239: where not exists (
2240: select null
2241: from pay_action_interlocks int,
2242: pay_assignment_actions act
2243: where int.locked_action_id = p_info.assact_id
2244: and act.assignment_action_id = int.locking_action_id
2245: and ((exists
2246: (select null
2302: select null into dummy
2303: from dual
2304: where not exists
2305: (select null
2306: from pay_assignment_actions ACT,
2307: pay_payroll_actions PACT,
2308: pay_action_classifications CLASS
2309: where ACT.assignment_id = p_info.assignment_id
2310: and ACT.action_sequence > l_action_sequence
2316: select null into dummy
2317: from dual
2318: where not exists
2319: (select null
2320: from pay_assignment_actions ACT,
2321: pay_payroll_actions PACT,
2322: pay_action_classifications CLASS
2323: where ACT.assignment_id = p_info.assignment_id
2324: and ACT.action_sequence > l_action_sequence
2346: where not exists
2347: (select null
2348: from pay_action_classifications CLASS,
2349: pay_payroll_actions PACT,
2350: pay_assignment_actions ACT,
2351: pay_object_groups POG_ASG,
2352: pay_object_groups POG_PER,
2353: pay_assignment_actions PAA_RET
2354: where PAA_RET.assignment_action_id = p_info.assact_id
2349: pay_payroll_actions PACT,
2350: pay_assignment_actions ACT,
2351: pay_object_groups POG_ASG,
2352: pay_object_groups POG_PER,
2353: pay_assignment_actions PAA_RET
2354: where PAA_RET.assignment_action_id = p_info.assact_id
2355: and POG_PER.object_group_id = PAA_RET.object_id
2356: and POG_PER.source_type = 'PPF'
2357: and POG_ASG.parent_object_group_id = POG_PER.object_group_id
2368: where not exists
2369: (select null
2370: from pay_action_classifications CLASS,
2371: pay_payroll_actions PACT,
2372: pay_assignment_actions ACT,
2373: pay_object_groups POG_ASG,
2374: pay_object_groups POG_PER,
2375: pay_assignment_actions PAA_RET
2376: where PAA_RET.assignment_action_id = p_info.assact_id
2371: pay_payroll_actions PACT,
2372: pay_assignment_actions ACT,
2373: pay_object_groups POG_ASG,
2374: pay_object_groups POG_PER,
2375: pay_assignment_actions PAA_RET
2376: where PAA_RET.assignment_action_id = p_info.assact_id
2377: and POG_PER.object_group_id = PAA_RET.object_id
2378: and POG_PER.source_type = 'PPF'
2379: and POG_ASG.parent_object_group_id = POG_PER.object_group_id
2395: where not exists
2396: (select null
2397: from pay_action_classifications CLASS,
2398: pay_payroll_actions PACT,
2399: pay_assignment_actions ACT,
2400: pay_object_groups POG_ASG,
2401: pay_object_groups POG_ASG2
2402: where POG_ASG.source_type = 'PAF'
2403: and POG_ASG2.source_type = 'PAF'
2415: where not exists
2416: (select null
2417: from pay_action_classifications CLASS,
2418: pay_payroll_actions PACT,
2419: pay_assignment_actions ACT,
2420: pay_object_groups POG_ASG,
2421: pay_object_groups POG_ASG2
2422: where POG_ASG.source_type = 'PAF'
2423: and POG_ASG2.source_type = 'PAF'
2448: where not exists
2449: (select null
2450: from pay_action_classifications CLASS,
2451: pay_payroll_actions PACT,
2452: pay_assignment_actions ACT,
2453: per_all_assignments_f ASS,
2454: per_periods_of_service POS
2455: where POS.person_id = l_person_id
2456: and ASS.period_of_service_id = POS.period_of_service_id
2466: where not exists
2467: (select null
2468: from pay_action_classifications CLASS,
2469: pay_payroll_actions PACT,
2470: pay_assignment_actions ACT,
2471: per_all_assignments_f ASS,
2472: per_periods_of_service POS
2473: where POS.person_id = l_person_id
2474: and ASS.period_of_service_id = POS.period_of_service_id
2492: into dummy
2493: from dual
2494: where not exists (select null
2495: from
2496: pay_assignment_actions paac2,
2497: pay_assignment_actions paac,
2498: pay_action_interlocks pai
2499: where pai.locking_action_id = p_info.assact_id
2500: and pai.locked_action_id = paac.assignment_action_id
2493: from dual
2494: where not exists (select null
2495: from
2496: pay_assignment_actions paac2,
2497: pay_assignment_actions paac,
2498: pay_action_interlocks pai
2499: where pai.locking_action_id = p_info.assact_id
2500: and pai.locked_action_id = paac.assignment_action_id
2501: and paac.pre_payment_id = paac2.pre_payment_id
2709: cursor c1 is
2710: select act.assignment_action_id, -- Bug 6834017 ,Removed ordered hint
2711: asg.period_of_service_id
2712: from per_all_assignments_f asg,
2713: pay_assignment_actions act
2714: where act.payroll_action_id = p_info.pact_id
2715: and asg.assignment_id = act.assignment_id
2716: and act.source_action_id is null
2717: and ((p_info.action_type = 'X'
2752: cursor c1_bee is
2753: select act.assignment_action_id, -- Bug 6834017 ,Removed ordered hint
2754: asg.period_of_service_id
2755: from per_all_assignments_f asg,
2756: pay_assignment_actions act
2757: where act.payroll_action_id = p_info.pact_id
2758: and asg.assignment_id = act.assignment_id
2759: and act.source_action_id is null
2760: and p_info.action_type = 'BEE'
2771: order by act.action_sequence desc;
2772: --
2773: cursor c2 is
2774: select act.assignment_action_id
2775: from pay_assignment_actions act
2776: where act.payroll_action_id = p_info.pact_id
2777: and act.source_action_id is null
2778: and act.object_id between
2779: range_info.starting_person and range_info.ending_person
2902: l_complete number;
2903: begin
2904: select count(*)
2905: into l_complete
2906: from pay_assignment_actions act
2907: where act.payroll_action_id = p_info.pact_id
2908: and act.secondary_status = 'C';
2909: --
2910: if(l_complete > 0) then
2925: w_payroll_id_1 number(9);
2926:
2927: cursor chk_dep is
2928: select distinct pac.payroll_action_id
2929: from pay_assignment_actions act2,
2930: pay_action_interlocks pai,
2931: pay_assignment_actions act,
2932: pay_payroll_actions pac,
2933: pay_payroll_actions pac2
2927: cursor chk_dep is
2928: select distinct pac.payroll_action_id
2929: from pay_assignment_actions act2,
2930: pay_action_interlocks pai,
2931: pay_assignment_actions act,
2932: pay_payroll_actions pac,
2933: pay_payroll_actions pac2
2934: where pac2.payroll_action_id = p_info.pact_id
2935: and act2.payroll_action_id = pac2.payroll_action_id
3070: min(asg.person_id),
3071: max(asg.person_id),
3072: 'U'
3073: from pay_payroll_actions pac,
3074: pay_assignment_actions act,
3075: per_all_assignments_f asg
3076: where pac.payroll_action_id = l_payroll_action_id
3077: and act.payroll_action_id = pac.payroll_action_id
3078: and asg.assignment_id = act.assignment_id
3089: min(act.object_id),
3090: max(act.object_id),
3091: 'U'
3092: from pay_payroll_actions pac,
3093: pay_assignment_actions act
3094: where pac.payroll_action_id = l_payroll_action_id
3095: and act.payroll_action_id = pac.payroll_action_id
3096: group by pac.payroll_action_id;
3097: end if;
3115: into dummy
3116: from sys.dual
3117: where exists (
3118: select null
3119: from pay_assignment_actions act
3120: where act.payroll_action_id = p_payroll_action_id);
3121: --
3122: -- There are still assignment actions.
3123: return(false);
3140: action_count number;
3141: begin
3142: select count(*)
3143: into action_count
3144: from pay_assignment_actions act
3145: where act.payroll_action_id = p_info.pact_id
3146: and rownum < (p_info.max_single_undo + 2);
3147: --
3148: if(action_count > p_info.max_single_undo) then
3325: if info.set_date_earned = 'Y' then
3326: select max(date_earned)
3327: into l_date_earned
3328: from pay_payroll_actions locked_pact,
3329: pay_assignment_actions locked,
3330: pay_assignment_actions locking,
3331: pay_action_interlocks locks
3332: where locking.payroll_action_id =info.pact_id
3333: and locking.assignment_action_id =locks.locking_action_id
3326: select max(date_earned)
3327: into l_date_earned
3328: from pay_payroll_actions locked_pact,
3329: pay_assignment_actions locked,
3330: pay_assignment_actions locking,
3331: pay_action_interlocks locks
3332: where locking.payroll_action_id =info.pact_id
3333: and locking.assignment_action_id =locks.locking_action_id
3334: and locked.assignment_action_id =locks.locked_action_id
3389: hr_utility.set_location(c_indent, 10);
3390: end if;
3391: select act.payroll_action_id, act.source_action_id, act.object_type
3392: into info.pact_id, src_action_id, info.object_type
3393: from pay_assignment_actions act
3394: where act.assignment_action_id = p_assignment_action_id;
3395: --
3396: -- instantiate the other parameters that are relevant.
3397: info.assact_id := p_assignment_action_id;
3430: val_assact_rr_rules(info);
3431: else
3432: select assignment_id
3433: into info.assignment_id
3434: from pay_assignment_actions
3435: where assignment_action_id = info.assact_id;
3436: end if;
3437: --
3438: -- perform the rollback/mark for retry itself.
3471: -- may have been the last one
3472: select max(locked_pact.date_earned), locking_pact.date_earned
3473: into l_date_earned, l_current_date_earned
3474: from pay_payroll_actions locked_pact,
3475: pay_assignment_actions locked,
3476: pay_assignment_actions locking,
3477: pay_action_interlocks locks,
3478: pay_payroll_actions locking_pact
3479: where locking_pact.payroll_action_id =info.pact_id
3472: select max(locked_pact.date_earned), locking_pact.date_earned
3473: into l_date_earned, l_current_date_earned
3474: from pay_payroll_actions locked_pact,
3475: pay_assignment_actions locked,
3476: pay_assignment_actions locking,
3477: pay_action_interlocks locks,
3478: pay_payroll_actions locking_pact
3479: where locking_pact.payroll_action_id =info.pact_id
3480: and locking.payroll_action_id =locking_pact.payroll_action_id