[Home] [Help]
116: 29-APR-2005 SuSivasu 115.110 Added p_reason parameter for
117: ext_man_payment.
118: 29-APR-2005 alogue 115.109 4337565 Support of ENABLE_RR_SPARSE
119: upgrade.
120: 14-APR-2005 alogue 115.108 3465844 Use PAY_ASSIGNMENT_ACTIONS_N51
121: instead of PAY_ASSIGNMENT_ACTIONS_N1
122: in index hints.
123: 04-APR-2005 alogue 115.107 Latest Balance Maintenance by
124: Reversals.
117: ext_man_payment.
118: 29-APR-2005 alogue 115.109 4337565 Support of ENABLE_RR_SPARSE
119: upgrade.
120: 14-APR-2005 alogue 115.108 3465844 Use PAY_ASSIGNMENT_ACTIONS_N51
121: instead of PAY_ASSIGNMENT_ACTIONS_N1
122: in index hints.
123: 04-APR-2005 alogue 115.107 Latest Balance Maintenance by
124: Reversals.
125: 23-FEB-2005 nbristow 115.106 Changes for Period Allocation.
528: business_group_id index.
529: 05-OCT-1994 DKERR 40.20 Set the OBJECT_VERSION_NUMBER
530: to 1 for inserts into
531: PAY_PAYROLL_ACTIONS and
532: PAY_ASSIGNMENT_ACTIONS for
533: all action types.
534: 28-JUN-1994 DSAXBY 40.19 Added line to decode statement
535: in reversal routine, to
536: prevent invalid number errors.
615: sz number
616: );
617: --
618: type assact_details is record
619: (assact_id pay_assignment_actions.assignment_action_id%type,
620: assignment_id pay_assignment_actions.assignment_id%type,
621: full_name per_all_people_f.full_name%type,
622: assignment_number per_all_assignments_f.assignment_number%type,
623: payroll_id per_all_assignments_f.payroll_id%type);
616: );
617: --
618: type assact_details is record
619: (assact_id pay_assignment_actions.assignment_action_id%type,
620: assignment_id pay_assignment_actions.assignment_id%type,
621: full_name per_all_people_f.full_name%type,
622: assignment_number per_all_assignments_f.assignment_number%type,
623: payroll_id per_all_assignments_f.payroll_id%type);
624: --
820: select null
821: from dual
822: where exists
823: (select null -- check for any ASSACTs
824: from pay_assignment_actions
825: where payroll_action_id = p_pact_id)
826: or exists
827: (select null -- check for any MESSAGEs
828: from pay_message_lines
860: p_assact_rec in out nocopy assact_details,
861: p_rollback_mode in varchar2)
862: return boolean is
863: --
864: l_action_sequence pay_assignment_actions.action_sequence%type;
865: l_action_status pay_assignment_actions.action_status%type;
866: l_person_id per_all_people_f.person_id%type;
867: begin
868: --
861: p_rollback_mode in varchar2)
862: return boolean is
863: --
864: l_action_sequence pay_assignment_actions.action_sequence%type;
865: l_action_status pay_assignment_actions.action_status%type;
866: l_person_id per_all_people_f.person_id%type;
867: begin
868: --
869: g_debug := hr_utility.debug_enabled;
889: p_assact_rec.assignment_number
890: from per_periods_of_service POS,
891: per_all_assignments_f ASS,
892: per_all_people_f PEO,
893: pay_assignment_actions ACT
894: where ACT.assignment_action_id = p_assact_rec.assact_id
895: and ASS.assignment_id = ACT.assignment_id
896: and p_pact_rec.action_date between
897: ASS.effective_start_date and ASS.effective_end_date
940: from dual
941: where not exists (
942: select null
943: from pay_action_interlocks int,
944: pay_assignment_actions act
945: where int.locked_action_id = p_assact_rec.assact_id
946: and act.assignment_action_id = int.locking_action_id
947: and act.action_status <> 'M');
948: else
986: select null into dummy
987: from dual
988: where not exists
989: (select null
990: from pay_assignment_actions ACT,
991: pay_payroll_actions PACT,
992: pay_action_classifications CLASS,
993: pay_action_classifications CLAS2
994: where ACT.assignment_id = p_assact_rec.assignment_id
1007: select null into dummy
1008: from dual
1009: where not exists
1010: (select null
1011: from pay_assignment_actions ACT,
1012: pay_payroll_actions PACT,
1013: pay_action_classifications CLASS,
1014: pay_action_classifications CLAS2
1015: where ACT.assignment_id = p_assact_rec.assignment_id
1035: (select null
1036: from pay_action_classifications CLASS,
1037: pay_action_classifications CLAS2,
1038: pay_payroll_actions PACT,
1039: pay_assignment_actions ACT,
1040: per_all_assignments_f ASS,
1041: per_periods_of_service POS
1042: where POS.person_id = l_person_id
1043: and ASS.period_of_service_id = POS.period_of_service_id
1057: (select null
1058: from pay_action_classifications CLASS,
1059: pay_action_classifications CLAS2,
1060: pay_payroll_actions PACT,
1061: pay_assignment_actions ACT,
1062: per_all_assignments_f ASS,
1063: per_periods_of_service POS
1064: where POS.person_id = l_person_id
1065: and ASS.period_of_service_id = POS.period_of_service_id
1276: procedure trash_quickpay (p_action_id number) is
1277: cursor c1 is
1278: select pee.element_entry_id
1279: from pay_element_entries_f pee,
1280: pay_assignment_actions paa
1281: where pee.creator_type = 'Q'
1282: and pee.creator_id = paa.assignment_action_id
1283: and pee.assignment_id = paa.assignment_id
1284: and paa.assignment_action_id = p_action_id
1871: if p_rollback_mode = 'RETRY' then
1872: if g_debug then
1873: hr_utility.set_location('hrassact.do_assact_rollback',170);
1874: end if;
1875: update pay_assignment_actions
1876: set action_status = 'M'
1877: where assignment_action_id = p_assact_rec.assact_id;
1878: --
1879: elsif p_rollback_mode = 'BACKPAY' then
1879: elsif p_rollback_mode = 'BACKPAY' then
1880: if g_debug then
1881: hr_utility.set_location('hrassact.do_assact_rollback',180);
1882: end if;
1883: update pay_assignment_actions
1884: set action_status = 'B'
1885: where assignment_action_id = p_assact_rec.assact_id;
1886: --
1887: elsif p_rollback_mode = 'ROLLBACK' then
1895: --
1896: if g_debug then
1897: hr_utility.set_location('hrassact.do_assact_rollback',200);
1898: end if;
1899: delete from pay_assignment_actions
1900: where assignment_action_id = p_assact_rec.assact_id;
1901: end if;
1902: else
1903: -- In the case of rolling back (from the form), we
1971: hr_utility.set_location(c_indent, 20);
1972: end if;
1973: select max(act.action_sequence) + 1
1974: into l_cur_aseq
1975: from pay_assignment_actions act
1976: where act.payroll_action_id = p_payroll_action_id;
1977: --
1978: -- now roll back each assignment action in turn (lock as we go)
1979: declare
1987: -- the first chunk.
1988: cursor c1 is
1989: select act.assignment_action_id,
1990: act.action_sequence
1991: from pay_assignment_actions act
1992: where act.payroll_action_id = p_payroll_action_id
1993: and act.action_sequence < l_cur_aseq
1994: order by act.action_sequence desc
1995: for update of act.action_status;
2271: l_pact_rec pact_details; -- payroll action details
2272: l_assact_rec assact_details; -- assignment action details
2273: cursor c1 is
2274: select payroll_action_id
2275: from pay_assignment_actions
2276: where assignment_action_id = p_assignment_action_id
2277: for update of action_status;
2278: --
2279: --
2908: cursor albc (p_person_id number) is
2909: select /*+ ORDERED */ alb.latest_balance_id
2910: from per_periods_of_service pos,
2911: per_all_assignments_f asg,
2912: pay_assignment_actions act,
2913: pay_payroll_actions pac,
2914: pay_assignment_latest_balances alb
2915: where pos.person_id = p_person_id
2916: and asg.period_of_service_id = pos.period_of_service_id
2934: pay_input_values_f piv,
2935: pay_balance_feeds_f pbf,
2936: pay_defined_balances pdb,
2937: pay_person_latest_balances plb,
2938: pay_assignment_actions paa,
2939: pay_payroll_actions ppa
2940: where plb.person_id = p_person_id
2941: and piv.element_type_id = p_eletyp_id
2942: and piv.input_value_id = pbf.input_value_id
2953: p_eletyp_id number) is
2954: select /*+ ORDERED */
2955: alb.latest_balance_id
2956: from pay_assignment_latest_balances alb,
2957: pay_assignment_actions act,
2958: pay_payroll_actions pac,
2959: pay_defined_balances pdb,
2960: pay_balance_feeds_f pbf,
2961: pay_input_values_f piv
2984: pay_input_values_f piv,
2985: pay_balance_feeds_f pbf,
2986: pay_defined_balances pdb,
2987: pay_latest_balances plb,
2988: pay_assignment_actions paa,
2989: pay_payroll_actions ppa
2990: where plb.person_id = p_person_id
2991: and piv.element_type_id = p_eletyp_id
2992: and piv.input_value_id = pbf.input_value_id
3128: begin
3129: select payroll_action_id,
3130: action_sequence,
3131: assignment_id,
3132: pay_assignment_actions_s.nextval
3133: into pact,
3134: actseq,
3135: asgid,
3136: newseq
3133: into pact,
3134: actseq,
3135: asgid,
3136: newseq
3137: from pay_assignment_actions
3138: where assignment_action_id = p_assact;
3139: --
3140: resequence_actions(pact, asgid, actseq, rmode);
3141: --
3138: where assignment_action_id = p_assact;
3139: --
3140: resequence_actions(pact, asgid, actseq, rmode);
3141: --
3142: update pay_assignment_actions
3143: set action_sequence = newseq
3144: where assignment_action_id = p_assact;
3145: --
3146: update pay_run_balances
3169: select ppa.effective_date,
3170: paa.assignment_action_id,
3171: ppa.action_type
3172: from pay_payroll_actions ppa,
3173: pay_assignment_actions paa
3174: where ppa.payroll_action_id = pactid
3175: and paa.payroll_action_id = ppa.payroll_action_id
3176: and paa.source_action_id is null
3177: and paa.chunk_number = cnkno;
3212: ) is
3213: select ac2.rowid,
3214: ac2.assignment_action_id
3215: from pay_action_classifications acl,
3216: pay_assignment_actions ac2,
3217: pay_assignment_actions paa,
3218: pay_payroll_actions ppa,
3219: pay_payroll_actions pa2
3220: where paa.assignment_action_id = aaid
3213: select ac2.rowid,
3214: ac2.assignment_action_id
3215: from pay_action_classifications acl,
3216: pay_assignment_actions ac2,
3217: pay_assignment_actions paa,
3218: pay_payroll_actions ppa,
3219: pay_payroll_actions pa2
3220: where paa.assignment_action_id = aaid
3221: and ppa.payroll_action_id = paa.payroll_action_id
3238: ) is
3239: select ac2.rowid,
3240: ac2.assignment_action_id
3241: from pay_action_classifications acl,
3242: pay_assignment_actions ac2,
3243: pay_assignment_actions act,
3244: per_all_assignments_f as2,
3245: per_all_assignments_f asg,
3246: pay_payroll_actions pa2,
3239: select ac2.rowid,
3240: ac2.assignment_action_id
3241: from pay_action_classifications acl,
3242: pay_assignment_actions ac2,
3243: pay_assignment_actions act,
3244: per_all_assignments_f as2,
3245: per_all_assignments_f asg,
3246: pay_payroll_actions pa2,
3247: pay_payroll_actions pac
3280: ac2.assignment_action_id
3281: from
3282: (
3283: select distinct ac3.assignment_action_id
3284: from pay_assignment_actions act,
3285: pay_object_groups pog,
3286: pay_object_groups pog2,
3287: pay_object_groups pog3,
3288: pay_payroll_actions pac,
3286: pay_object_groups pog2,
3287: pay_object_groups pog3,
3288: pay_payroll_actions pac,
3289: pay_object_groups pog4,
3290: pay_assignment_actions ac3,
3291: pay_payroll_actions pa3,
3292: pay_action_classifications acl
3293: where act.assignment_action_id = aaid
3294: and pac.payroll_action_id = act.payroll_action_id
3306: and pa3.action_type = acl.action_type
3307: and acl.classification_name = 'SEQUENCED'
3308: union all
3309: select distinct ac3.assignment_action_id
3310: from pay_assignment_actions act,
3311: pay_object_groups pog,
3312: pay_object_groups pog2,
3313: pay_object_groups pog3,
3314: pay_payroll_actions pac,
3311: pay_object_groups pog,
3312: pay_object_groups pog2,
3313: pay_object_groups pog3,
3314: pay_payroll_actions pac,
3315: pay_assignment_actions ac3,
3316: pay_payroll_actions pa3
3317: where act.assignment_action_id = aaid
3318: and pac.payroll_action_id = act.payroll_action_id
3319: and pog.source_id = act.assignment_id
3326: and ac3.object_type = 'POG'
3327: and pa3.payroll_action_id = ac3.payroll_action_id
3328: and pa3.effective_date > pac.effective_date
3329: and pa3.action_type = 'L') V,
3330: pay_assignment_actions ac2,
3331: pay_payroll_actions pa2
3332: where ac2.assignment_action_id = V.assignment_action_id
3333: and pa2.payroll_action_id = ac2.payroll_action_id
3334: and (( chldact = 'N'
3349: ac2.assignment_action_id
3350: from
3351: (
3352: select distinct ac3.assignment_action_id
3353: from pay_assignment_actions act,
3354: pay_object_groups pog,
3355: pay_object_groups pog2,
3356: pay_payroll_actions pac,
3357: pay_object_groups pog3,
3354: pay_object_groups pog,
3355: pay_object_groups pog2,
3356: pay_payroll_actions pac,
3357: pay_object_groups pog3,
3358: pay_assignment_actions ac3,
3359: pay_payroll_actions pa3,
3360: pay_action_classifications acl
3361: where act.assignment_action_id = aaid
3362: and pac.payroll_action_id = act.payroll_action_id
3372: and pa3.action_type = acl.action_type
3373: and acl.classification_name = 'SEQUENCED'
3374: union all
3375: select distinct ac3.assignment_action_id
3376: from pay_assignment_actions act,
3377: pay_object_groups pog,
3378: pay_object_groups pog2,
3379: pay_payroll_actions pac,
3380: pay_assignment_actions ac3,
3376: from pay_assignment_actions act,
3377: pay_object_groups pog,
3378: pay_object_groups pog2,
3379: pay_payroll_actions pac,
3380: pay_assignment_actions ac3,
3381: pay_payroll_actions pa3
3382: where act.assignment_action_id = aaid
3383: and pac.payroll_action_id = act.payroll_action_id
3384: and pog.object_group_id = act.object_id
3389: and ac3.object_type = 'POG'
3390: and pa3.payroll_action_id = ac3.payroll_action_id
3391: and pa3.effective_date > pac.effective_date
3392: and pa3.action_type = 'L') V,
3393: pay_assignment_actions ac2,
3394: pay_payroll_actions pa2
3395: where ac2.assignment_action_id = V.assignment_action_id
3396: and pa2.payroll_action_id = ac2.payroll_action_id
3397: and (( chldact = 'N'
3402: order by pa2.effective_date, ac2.action_sequence
3403: for update of ac2.assignment_action_id;
3404: --
3405: my_rowid rowid;
3406: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3407: --
3408: begin
3409: --
3410: g_debug := hr_utility.debug_enabled;
3445: end if;
3446: --
3447: --
3448: -- Now, update with new action_sequence.
3449: update pay_assignment_actions act
3450: set act.action_sequence = pay_assignment_actions_s.nextval
3451: where act.rowid = my_rowid;
3452: --
3453: update pay_run_balances
3446: --
3447: --
3448: -- Now, update with new action_sequence.
3449: update pay_assignment_actions act
3450: set act.action_sequence = pay_assignment_actions_s.nextval
3451: where act.rowid = my_rowid;
3452: --
3453: update pay_run_balances
3454: set action_sequence = pay_assignment_actions_s.currval
3450: set act.action_sequence = pay_assignment_actions_s.nextval
3451: where act.rowid = my_rowid;
3452: --
3453: update pay_run_balances
3454: set action_sequence = pay_assignment_actions_s.currval
3455: where assignment_action_id = upd_aa_id;
3456: --
3457: end loop;
3458: --
3481: --
3482: cursor get_chld (p_aa_id number)
3483: is
3484: select assignment_action_id
3485: from pay_assignment_actions
3486: where source_action_id = p_aa_id
3487: order by action_sequence;
3488: --
3489: begin
3491: for chdrec in get_chld(p_asg_action) loop
3492: --
3493: resequence_children(chdrec.assignment_action_id);
3494: --
3495: update pay_assignment_actions
3496: set action_sequence = pay_assignment_actions_s.nextval
3497: where assignment_action_id = chdrec.assignment_action_id;
3498: --
3499: end loop;
3492: --
3493: resequence_children(chdrec.assignment_action_id);
3494: --
3495: update pay_assignment_actions
3496: set action_sequence = pay_assignment_actions_s.nextval
3497: where assignment_action_id = chdrec.assignment_action_id;
3498: --
3499: end loop;
3500: --
3517: ac2.assignment_action_id,
3518: ac2.source_action_id,
3519: ac2.object_type
3520: from pay_action_classifications acl,
3521: pay_assignment_actions ac2,
3522: pay_payroll_actions pa2
3523: where ac2.assignment_id = asgid
3524: and pa2.payroll_action_id = ac2.payroll_action_id
3525: and acl.classification_name = 'SEQUENCED'
3537: asgid number,
3538: actseq number,
3539: rmode varchar2) is
3540: select /*+ ORDERED
3541: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N51)
3542: (pa2 PAY_PAYROLL_ACTIONS_PK) */
3543: ac2.rowid,
3544: ac2.assignment_action_id,
3545: ac2.source_action_id,
3546: ac2.object_type
3547: from pay_payroll_actions pac,
3548: per_all_assignments_f asg,
3549: per_all_assignments_f as2,
3550: pay_assignment_actions act,
3551: pay_assignment_actions ac2,
3552: pay_payroll_actions pa2,
3553: pay_action_classifications acl
3554: where pac.payroll_action_id = pactid
3547: from pay_payroll_actions pac,
3548: per_all_assignments_f asg,
3549: per_all_assignments_f as2,
3550: pay_assignment_actions act,
3551: pay_assignment_actions ac2,
3552: pay_payroll_actions pa2,
3553: pay_action_classifications acl
3554: where pac.payroll_action_id = pactid
3555: and act.payroll_action_id = pac.payroll_action_id
3584: asgid number,
3585: actseq number,
3586: rmode varchar2) is
3587: select /*+ ORDERED
3588: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_PK)
3589: (pa2 PAY_PAYROLL_ACTIONS_PK) */
3590: ac2.rowid,
3591: ac2.assignment_action_id,
3592: ac2.source_action_id,
3595: (
3596: select /*+ ORDERED */
3597: distinct ac3.assignment_action_id
3598: from pay_payroll_actions pac,
3599: pay_assignment_actions act,
3600: pay_object_groups pog,
3601: pay_object_groups pog2,
3602: pay_object_groups pog3,
3603: pay_object_groups pog4,
3600: pay_object_groups pog,
3601: pay_object_groups pog2,
3602: pay_object_groups pog3,
3603: pay_object_groups pog4,
3604: pay_assignment_actions ac3,
3605: pay_payroll_actions pa3,
3606: pay_action_classifications acl
3607: where pac.payroll_action_id = pactid
3608: and act.payroll_action_id = pac.payroll_action_id
3626: union all
3627: select /*+ ORDERED */
3628: distinct ac3.assignment_action_id
3629: from pay_payroll_actions pac,
3630: pay_assignment_actions act,
3631: pay_object_groups pog,
3632: pay_object_groups pog2,
3633: pay_object_groups pog3,
3634: pay_assignment_actions ac3,
3630: pay_assignment_actions act,
3631: pay_object_groups pog,
3632: pay_object_groups pog2,
3633: pay_object_groups pog3,
3634: pay_assignment_actions ac3,
3635: pay_payroll_actions pa3
3636: where pac.payroll_action_id = pactid
3637: and act.payroll_action_id = pac.payroll_action_id
3638: and act.assignment_id = asgid
3649: and ac3.object_type = 'POG'
3650: and ac3.action_sequence > actseq
3651: and pa3.payroll_action_id = ac3.payroll_action_id
3652: and pa3.action_type = 'L') V,
3653: pay_assignment_actions ac2,
3654: pay_payroll_actions pa2
3655: where ac2.assignment_action_id = V.assignment_action_id
3656: and pa2.payroll_action_id = ac2.payroll_action_id
3657: and (ac2.source_action_id is null
3663: for update of ac2.assignment_action_id;
3664:
3665: --
3666: my_rowid rowid;
3667: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3668: src_aa_id pay_assignment_actions.source_action_id%type;
3669: obj_type pay_assignment_actions.object_type%type;
3670: --
3671: begin
3664:
3665: --
3666: my_rowid rowid;
3667: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3668: src_aa_id pay_assignment_actions.source_action_id%type;
3669: obj_type pay_assignment_actions.object_type%type;
3670: --
3671: begin
3672: --
3665: --
3666: my_rowid rowid;
3667: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3668: src_aa_id pay_assignment_actions.source_action_id%type;
3669: obj_type pay_assignment_actions.object_type%type;
3670: --
3671: begin
3672: --
3673: g_debug := hr_utility.debug_enabled;
3704: resequence_children(upd_aa_id);
3705: --
3706: --
3707: -- Now, update with new action_sequence.
3708: update pay_assignment_actions act
3709: set act.action_sequence = pay_assignment_actions_s.nextval
3710: where act.rowid = my_rowid;
3711: --
3712: update pay_run_balances
3705: --
3706: --
3707: -- Now, update with new action_sequence.
3708: update pay_assignment_actions act
3709: set act.action_sequence = pay_assignment_actions_s.nextval
3710: where act.rowid = my_rowid;
3711: --
3712: update pay_run_balances
3713: set action_sequence = pay_assignment_actions_s.currval
3709: set act.action_sequence = pay_assignment_actions_s.nextval
3710: where act.rowid = my_rowid;
3711: --
3712: update pay_run_balances
3713: set action_sequence = pay_assignment_actions_s.currval
3714: where assignment_action_id = upd_aa_id;
3715: --
3716: /* If it's an assignment action thats created due to a
3717: POG then we need to up the POGs action sequence
3719: if (obj_type is not null
3720: and obj_type = 'POG'
3721: and src_aa_id is not null) then
3722: --
3723: update pay_assignment_actions act
3724: set act.action_sequence = pay_assignment_actions_s.nextval
3725: where act.assignment_action_id = src_aa_id;
3726: --
3727: end if;
3720: and obj_type = 'POG'
3721: and src_aa_id is not null) then
3722: --
3723: update pay_assignment_actions act
3724: set act.action_sequence = pay_assignment_actions_s.nextval
3725: where act.assignment_action_id = src_aa_id;
3726: --
3727: end if;
3728: --
4130: asgid number) is
4131: select ac2.rowid,
4132: ac2.assignment_action_id
4133: from pay_action_classifications acl,
4134: pay_assignment_actions ac2,
4135: pay_assignment_actions act,
4136: pay_payroll_actions pa2,
4137: pay_payroll_actions pac
4138: where pac.payroll_action_id = pactid
4131: select ac2.rowid,
4132: ac2.assignment_action_id
4133: from pay_action_classifications acl,
4134: pay_assignment_actions ac2,
4135: pay_assignment_actions act,
4136: pay_payroll_actions pa2,
4137: pay_payroll_actions pac
4138: where pac.payroll_action_id = pactid
4139: and act.payroll_action_id = pac.payroll_action_id
4157: --
4158: cursor seqper (pactid number,
4159: asgid number) is
4160: select /*+ ORDERED
4161: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N51) */
4162: ac2.rowid,
4163: ac2.assignment_action_id
4164: from pay_payroll_actions pac,
4165: pay_assignment_actions act,
4161: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N51) */
4162: ac2.rowid,
4163: ac2.assignment_action_id
4164: from pay_payroll_actions pac,
4165: pay_assignment_actions act,
4166: per_all_assignments_f asg,
4167: per_all_assignments_f as2,
4168: pay_assignment_actions ac2,
4169: pay_payroll_actions pa2,
4164: from pay_payroll_actions pac,
4165: pay_assignment_actions act,
4166: per_all_assignments_f asg,
4167: per_all_assignments_f as2,
4168: pay_assignment_actions ac2,
4169: pay_payroll_actions pa2,
4170: pay_action_classifications acl
4171: where pac.payroll_action_id = pactid
4172: and act.payroll_action_id = pac.payroll_action_id
4198: --
4199: cursor seqgrp (pactid number,
4200: asgid number) is
4201: select /*+ ORDERED
4202: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_PK) */
4203: ac2.rowid,
4204: ac2.assignment_action_id
4205: from
4206: (
4206: (
4207: select /*+ ORDERED */
4208: distinct ac3.assignment_action_id
4209: from pay_payroll_actions pac,
4210: pay_assignment_actions act,
4211: pay_object_groups pog,
4212: pay_object_groups pog2,
4213: pay_object_groups pog3,
4214: pay_object_groups pog4,
4211: pay_object_groups pog,
4212: pay_object_groups pog2,
4213: pay_object_groups pog3,
4214: pay_object_groups pog4,
4215: pay_assignment_actions ac3,
4216: pay_payroll_actions pa3,
4217: pay_action_classifications acl
4218: where pac.payroll_action_id = pactid
4219: and act.payroll_action_id = pac.payroll_action_id
4234: union all
4235: select /*+ ORDERED */
4236: distinct ac3.assignment_action_id
4237: from pay_payroll_actions pac,
4238: pay_assignment_actions act,
4239: pay_object_groups pog,
4240: pay_object_groups pog2,
4241: pay_object_groups pog3,
4242: pay_assignment_actions ac3,
4238: pay_assignment_actions act,
4239: pay_object_groups pog,
4240: pay_object_groups pog2,
4241: pay_object_groups pog3,
4242: pay_assignment_actions ac3,
4243: pay_payroll_actions pa3
4244: where pac.payroll_action_id = pactid
4245: and act.payroll_action_id = pac.payroll_action_id
4246: and act.assignment_id = asgid
4254: and ac3.object_type = 'POG'
4255: and pa3.payroll_action_id = ac3.payroll_action_id
4256: and pa3.effective_date > pac.effective_date
4257: and pa3.action_type = 'L') V,
4258: pay_assignment_actions ac2,
4259: pay_payroll_actions pa2
4260: where ac2.assignment_action_id = V.assignment_action_id
4261: and pa2.payroll_action_id = ac2.payroll_action_id
4262: order by pa2.effective_date, ac2.action_sequence
4276: and pog_act.parent_object_group_id = pog_grp.parent_object_group_id
4277: and pog_grp.source_type = 'PAF';
4278: --
4279: my_rowid rowid;
4280: upd_aa_id pay_assignment_actions.assignment_action_id%type;
4281: actype pay_payroll_actions.action_type%type;
4282: effdate date; -- the effective date of the payroll action.
4283: lgcode varchar2(30); -- Used to check if the US legislation.
4284: persid number; -- assignments person_id
4400: where exists (
4401: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4402: from pay_action_classifications pay_acl,
4403: pay_payroll_actions pay_pa2,
4404: pay_assignment_actions pay_ac2
4405: where pay_ac2.assignment_id = asgid
4406: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4407: and pay_acl.classification_name = 'SEQUENCED'
4408: and pay_pa2.action_type = pay_acl.action_type
4421: where exists (
4422: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4423: from pay_action_classifications pay_acl,
4424: pay_payroll_actions pay_pa2,
4425: pay_assignment_actions pay_ac2
4426: where pay_ac2.assignment_id = dprec.assignment_id
4427: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4428: and pay_acl.classification_name = 'SEQUENCED'
4429: and pay_pa2.action_type = pay_acl.action_type
4446: where exists (
4447: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4448: from pay_action_classifications pay_acl,
4449: pay_payroll_actions pay_pa2,
4450: pay_assignment_actions pay_ac2
4451: where pay_ac2.assignment_id = dprec.assignment_id
4452: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4453: and pay_acl.classification_name = 'SEQUENCED'
4454: and pay_pa2.action_type = pay_acl.action_type
4475: -- initialisation to perform balance rollup.
4476: if g_debug then
4477: hr_utility.set_location('hrassact.irbaact',25);
4478: end if;
4479: insert into pay_assignment_actions (
4480: assignment_action_id,
4481: assignment_id,
4482: payroll_action_id,
4483: action_status,
4499: where not exists (
4500: select null
4501: from pay_payroll_actions pac,
4502: pay_payroll_actions pa2,
4503: pay_assignment_actions act,
4504: pay_assignment_actions ac2
4505: where pac.payroll_action_id = pactid
4506: and pac.action_type <> 'I'
4507: and act.payroll_action_id = pac.payroll_action_id
4500: select null
4501: from pay_payroll_actions pac,
4502: pay_payroll_actions pa2,
4503: pay_assignment_actions act,
4504: pay_assignment_actions ac2
4505: where pac.payroll_action_id = pactid
4506: and pac.action_type <> 'I'
4507: and act.payroll_action_id = pac.payroll_action_id
4508: and act.assignment_id = asgid
4556: end if;
4557: --
4558: --
4559: -- Now, update with new action_sequence.
4560: update pay_assignment_actions act
4561: set act.action_sequence = pay_assignment_actions_s.nextval
4562: where act.rowid = my_rowid;
4563: --
4564: update pay_run_balances
4557: --
4558: --
4559: -- Now, update with new action_sequence.
4560: update pay_assignment_actions act
4561: set act.action_sequence = pay_assignment_actions_s.nextval
4562: where act.rowid = my_rowid;
4563: --
4564: update pay_run_balances
4565: set action_sequence = pay_assignment_actions_s.currval
4561: set act.action_sequence = pay_assignment_actions_s.nextval
4562: where act.rowid = my_rowid;
4563: --
4564: update pay_run_balances
4565: set action_sequence = pay_assignment_actions_s.currval
4566: where assignment_action_id = upd_aa_id;
4567: end loop;
4568: --
4569: --
4583: --
4584: -- Start by grabbing the type of the action we are dealing with.
4585: select pac.action_type,
4586: pac.business_group_id,
4587: pay_assignment_actions_s.nextval
4588: into actyp,
4589: bgid,
4590: aa_id
4591: from pay_payroll_actions pac
4670: -- time period independent legislation.
4671: if g_debug then
4672: hr_utility.set_location('hrassact.inassact_main',20);
4673: end if;
4674: insert into pay_assignment_actions (
4675: assignment_action_id,
4676: assignment_id,
4677: payroll_action_id,
4678: action_status,
4706: and not exists (
4707: select null
4708: from pay_action_classifications acl,
4709: pay_payroll_actions pa2,
4710: pay_assignment_actions ac2
4711: where ac2.assignment_id = asg.assignment_id
4712: and pa2.payroll_action_id = ac2.payroll_action_id
4713: and acl.classification_name = 'SEQUENCED'
4714: and pa2.action_type = acl.action_type
4719: -- time period dependent legislation.
4720: if g_debug then
4721: hr_utility.set_location('hrassact.inassact_main',30);
4722: end if;
4723: insert into pay_assignment_actions (
4724: assignment_action_id,
4725: assignment_id,
4726: payroll_action_id,
4727: action_status,
4755: and pbg.business_group_id = pac.business_group_id
4756: and not exists (
4757: select null
4758: from pay_action_classifications acl,
4759: pay_assignment_actions ac2,
4760: pay_payroll_actions pa2,
4761: per_all_assignments_f as2
4762: where as2.period_of_service_id = pos.period_of_service_id
4763: and ac2.assignment_id = as2.assignment_id
4786: irbaact(pactid,asgid,rmode,actyp, p_element_entry, taxunt,
4787: p_purge_mode,p_run_type_id,aa_id, p_mode);
4788: elsif (actyp = 'E') then
4789: --
4790: -- Insert a pay_assignment_actions record for Pre-payment
4791: -- External/Manual payments.
4792: -- Only do this if the pre-payment has not already
4793: -- been paid.
4794: -- This is detected if there is any assignment action
4793: -- been paid.
4794: -- This is detected if there is any assignment action
4795: -- with a pre_payment_id the same as that we are attempting
4796: -- to process which is not locked by a void action.
4797: insert into PAY_ASSIGNMENT_ACTIONS (
4798: ASSIGNMENT_ACTION_ID,
4799: ASSIGNMENT_ID,
4800: PAYROLL_ACTION_ID,
4801: ACTION_STATUS,
4808: asgid,
4809: pactid,
4810: 'C',
4811: 1,
4812: PAY_ASSIGNMENT_ACTIONS_S.nextval,
4813: p_pre_payment_id,
4814: p_serial_number,
4815: 1
4816: from sys.dual
4815: 1
4816: from sys.dual
4817: where not exists (
4818: select null
4819: from pay_assignment_actions act
4820: where act.pre_payment_id = p_pre_payment_id
4821: and not exists
4822: ( select null
4823: from pay_action_interlocks loc1,
4820: where act.pre_payment_id = p_pre_payment_id
4821: and not exists
4822: ( select null
4823: from pay_action_interlocks loc1,
4824: pay_assignment_actions actv,
4825: pay_payroll_actions pactv
4826: where loc1.locked_action_id = act.assignment_action_id
4827: and loc1.locking_action_id = actv.assignment_action_id
4828: and pactv.payroll_action_id = actv.payroll_action_id
4847: --
4848: if g_debug then
4849: hr_utility.set_location('hrassact.inassact_main',45);
4850: end if;
4851: insert into PAY_ASSIGNMENT_ACTIONS (
4852: ASSIGNMENT_ACTION_ID,
4853: ASSIGNMENT_ID,
4854: PAYROLL_ACTION_ID,
4855: ACTION_STATUS,
4866: 1,
4867: hr_dynsql.get_tax_unit(asg.assignment_id,
4868: pac.effective_date)
4869: from per_business_groups_perf pbg,
4870: pay_assignment_actions act,
4871: pay_payroll_actions pac, -- prepay action.
4872: pay_payroll_actions pa2, -- the QuickPay action.
4873: per_periods_of_service pos,
4874: per_all_assignments_f asg
4882: and pos.period_of_service_id = asg.period_of_service_id
4883: and pbg.business_group_id = pac.business_group_id
4884: and not exists (
4885: select null
4886: from pay_assignment_actions ac2,
4887: pay_payroll_actions pa3,
4888: pay_action_interlocks int
4889: where int.locked_action_id = act.assignment_action_id
4890: and ac2.assignment_action_id = int.locking_action_id
4892: and pa3.action_type in ('P', 'U'))
4893: and not exists (
4894: select null
4895: from per_all_assignments_f as3,
4896: pay_assignment_actions ac3
4897: where rmode <> 'Y'
4898: and ac3.payroll_action_id = pa2.payroll_action_id
4899: and ac3.action_status not in ('C', 'S')
4900: and as3.assignment_id = ac3.assignment_id
4964: inassact(p_payroll_action_id, p_assignment_id, null, null, null);
4965: --
4966: -- Get the assignment_action_id
4967: -- that has just been inserted
4968: select pay_assignment_actions_s.currval
4969: into assactid
4970: from sys.dual;
4971: --
4972: -- Return information.
5005: hr_utility.set_location('hrassact.qpppassact',10);
5006: end if;
5007: select act.assignment_id
5008: into l_assignment_id
5009: from pay_assignment_actions act,
5010: pay_payroll_actions pac
5011: where pac.payroll_action_id = p_payroll_action_id
5012: and act.payroll_action_id = pac.target_payroll_action_id
5013: and act.source_action_id is null;
5023: --
5024: if g_debug then
5025: hr_utility.set_location('hrassact.qpppassact',30);
5026: end if;
5027: select pay_assignment_actions_s.currval,
5028: act.assignment_action_id
5029: into l_locking_action_id,
5030: l_locked_action_id
5031: from pay_payroll_actions pac,
5028: act.assignment_action_id
5029: into l_locking_action_id,
5030: l_locked_action_id
5031: from pay_payroll_actions pac,
5032: pay_assignment_actions act
5033: where pac.payroll_action_id = p_payroll_action_id
5034: and act.payroll_action_id = pac.target_payroll_action_id
5035: and act.source_action_id is null;
5036: --
5052: update pay_payroll_actions pac
5053: set pac.date_earned = (
5054: select pa2.date_earned
5055: from pay_payroll_actions pa2,
5056: pay_assignment_actions act
5057: where act.assignment_action_id = l_locked_action_id
5058: and pa2.payroll_action_id = act.payroll_action_id)
5059:
5060: where pac.payroll_action_id =p_payroll_action_id;
5125: decode(p_org_needed,
5126: 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
5127: null) organization_id,
5128: ppa.effective_date
5129: from pay_assignment_actions paa,
5130: pay_run_results prr,
5131: pay_payroll_actions ppa
5132: where paa.assignment_action_id = revassactid
5133: and ppa.payroll_action_id = paa.payroll_action_id
5134: and paa.assignment_action_id = prr.assignment_action_id;
5135: --
5136: -- cursor and variables used in reversal of pay_run_balances
5137: --
5138: rev_asgact_id pay_assignment_actions.assignment_action_id%type;
5139: rev_act_seq pay_assignment_actions.action_sequence%type;
5140: rev_eff_date pay_payroll_actions.effective_date%type;
5141: --
5142: asgid number; -- assignment_id.
5135: --
5136: -- cursor and variables used in reversal of pay_run_balances
5137: --
5138: rev_asgact_id pay_assignment_actions.assignment_action_id%type;
5139: rev_act_seq pay_assignment_actions.action_sequence%type;
5140: rev_eff_date pay_payroll_actions.effective_date%type;
5141: --
5142: asgid number; -- assignment_id.
5143: taxunt number; -- tax unit id if legislation is US
5147:
5148: found number;
5149: rev_assact number; -- reversal assignment_action_id.
5150: leg_code per_business_groups_perf.legislation_code%type;
5151: l_process_path pay_assignment_actions.process_path%type;
5152: l_found boolean;
5153: l_inp_val_name pay_input_values_f.name%type;
5154: l_rev_lat_bal_maintenance boolean;
5155: l_rule_mode pay_legislation_rules.rule_mode%type;
5186: ac2.run_type_id,
5187: pbg.legislation_code
5188: into asgid, taxunt,run_type_id, leg_code
5189: from pay_action_classifications acl,
5190: pay_assignment_actions ac2,
5191: pay_payroll_actions pa2,
5192: per_business_groups_perf pbg,
5193: pay_payroll_actions pac
5194: where pac.payroll_action_id = pactid
5216: hr_utility.set_location('hrassact.reversal',10);
5217: -- Return the Reversal's assignment_action_id
5218: select act.assignment_action_id
5219: into rev_assact
5220: from pay_assignment_actions act
5221: where act.payroll_action_id = pactid
5222: and act.assignment_id = asgid;
5223: --
5224: select pay_core_utils.get_process_path(assactid)
5224: select pay_core_utils.get_process_path(assactid)
5225: into l_process_path
5226: from dual;
5227: -- need to set process path for reversals
5228: update pay_assignment_actions
5229: set process_path =l_process_path
5230: where assignment_action_id=rev_assact;
5231:
5232: -- insert an interlock row.
5244: assactid
5245: from dual
5246: where not exists (
5247: select null
5248: from pay_assignment_actions ac2,
5249: pay_payroll_actions pa2,
5250: pay_action_interlocks pai
5251: where pai.locked_action_id = assactid
5252: and ac2.assignment_action_id = pai.locking_action_id
5264: insert into pay_action_interlocks (
5265: locking_action_id,
5266: locked_action_id)
5267: select rev_assact, paa1.assignment_action_id
5268: from pay_assignment_actions paa,pay_assignment_actions paa1
5269: where paa.assignment_action_id=assactid
5270: and paa1.payroll_action_id=paa.payroll_action_id
5271: and paa1.source_action_id is null
5272: and paa1.assignment_id=paa.assignment_id
5276: else
5277: -- Return the Reversal's assignment_action_id
5278: select act.assignment_action_id
5279: into rev_assact
5280: from pay_assignment_actions act
5281: where act.payroll_action_id = pactid
5282: and act.assignment_id = asgid;
5283:
5284: --create new interlock if needed
5581: , ppa.effective_date
5582: into rev_asgact_id
5583: , rev_act_seq
5584: , rev_eff_date
5585: from pay_assignment_actions paa
5586: , pay_payroll_actions ppa
5587: where paa.payroll_action_id = pactid
5588: and paa.payroll_action_id = ppa.payroll_action_id;
5589:
5594: , ppa.effective_date
5595: into rev_asgact_id
5596: , rev_act_seq
5597: , rev_eff_date
5598: from pay_assignment_actions paa,
5599: pay_payroll_actions ppa
5600: where paa.assignment_action_id = assactid
5601: and ppa.payroll_action_id = paa.payroll_action_id;
5602:
5656: select + ORDERED
5657: INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK,
5658: PEL PAY_ELEMENT_LINKS_F_PK,
5659: PET PAY_ELEMENT_TYPES_F_PK,
5660: ACT2 PAY_ASSIGNMENT_ACTIONS_PK)
5661: USE_NL(PAC ACT PAI ACT2 RR2 PEE)
5662: pet.element_type_id,
5663: act.assignment_action_id,
5664: pee.entry_type,
5663: act.assignment_action_id,
5664: pee.entry_type,
5665: pee.element_entry_id
5666: from pay_payroll_actions pac,
5667: pay_assignment_actions act,
5668: pay_action_interlocks pai,
5669: pay_assignment_actions act2,
5670: pay_run_results rr2,
5671: pay_element_entries_f pee,
5665: pee.element_entry_id
5666: from pay_payroll_actions pac,
5667: pay_assignment_actions act,
5668: pay_action_interlocks pai,
5669: pay_assignment_actions act2,
5670: pay_run_results rr2,
5671: pay_element_entries_f pee,
5672: pay_element_links_f pel,
5673: pay_element_types_f pet
5734: select pev.input_value_id,
5735: pay_run_results_s.currval,
5736: null
5737: from pay_payroll_actions pac,
5738: pay_assignment_actions act, -- reversal
5739: pay_element_entry_values_f pev
5740: where act.assignment_action_id = c1rec.assignment_action_id --rev
5741: and act.payroll_action_id = pac.payroll_action_id
5742: and pev.element_entry_id = c1rec.element_entry_id
5773: l_consolidation_set_id number;
5774: --
5775: cursor C_CON1 is
5776: select pay_payroll_actions_s.nextval,
5777: pay_assignment_actions_s.nextval,
5778: pa.CONSOLIDATION_SET_ID,
5779: pa.BUSINESS_GROUP_ID
5780: from PAY_ASSIGNMENT_ACTIONS paa,
5781: PAY_PAYROLL_ACTIONS pa
5776: select pay_payroll_actions_s.nextval,
5777: pay_assignment_actions_s.nextval,
5778: pa.CONSOLIDATION_SET_ID,
5779: pa.BUSINESS_GROUP_ID
5780: from PAY_ASSIGNMENT_ACTIONS paa,
5781: PAY_PAYROLL_ACTIONS pa
5782: where paa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
5783: and paa.PAYROLL_ACTION_ID = pa.PAYROLL_ACTION_ID;
5784: --
5854: update pay_payroll_actions pac
5855: set pac.date_earned = (
5856: select pa2.date_earned
5857: from pay_payroll_actions pa2,
5858: pay_assignment_actions act
5859: where act.assignment_action_id = p_assignment_action_id
5860: and pa2.payroll_action_id = act.payroll_action_id
5861: )
5862: where pac.payroll_action_id = l_payroll_action_id;
5988: ,l_action_type
5989: ,l_effective_date
5990: from
5991: pay_payroll_actions pac
5992: ,pay_assignment_actions aa
5993: where
5994: aa.assignment_action_id = p_assact
5995: and pac.payroll_action_id = aa.payroll_action_id;
5996:
6274: paa.assignment_id
6275: into tax_group,
6276: asg_id
6277: from hr_organization_information hoi,
6278: pay_assignment_actions paa
6279: where UPPER(hoi.org_information_context) = 'FEDERAL TAX RULES'
6280: and hoi.organization_id = paa.tax_unit_id
6281: and paa.assignment_action_id = p_assact
6282: and hoi.org_information5 is not null;
6288: paa.assignment_id
6289: into tax_group,
6290: asg_id
6291: from hr_organization_information hoi,
6292: pay_assignment_actions paa
6293: where UPPER(hoi.org_information_context) = 'CANADA EMPLOYER IDENTIFICATION'
6294: and hoi.organization_id = paa.tax_unit_id
6295: and paa.assignment_action_id = p_assact
6296: and hoi.org_information4 is not null;
6548: hr_utility.set_location(c_indent,15);
6549: end if;
6550: select act.assignment_action_id, act.tax_unit_id
6551: into assactid, tax_unit
6552: from pay_assignment_actions act
6553: where act.payroll_action_id = pactid;
6554: --
6555: --
6556: -- we need to insert the run result row
7624: select pay.payroll_action_id,
7625: pay.effective_date
7626: into l_bal_owner_pay_action,
7627: p_bal_owner_eff_date
7628: from pay_assignment_actions asg
7629: , pay_payroll_actions pay
7630: where asg.assignment_action_id = p_bal_owner_asg_actid
7631: and pay.payroll_action_id = asg.payroll_action_id;
7632: --
7640: select pay.payroll_action_id,
7641: pay.effective_date
7642: into l_payroll_action,
7643: l_effective_date
7644: from pay_assignment_actions asg
7645: , pay_payroll_actions pay
7646: where asg.assignment_action_id = p_ass_action_id
7647: and pay.payroll_action_id = asg.payroll_action_id;
7648: else
8148: l_person_id,
8149: pactid,
8150: tax_unit_id,
8151: bus_grp_id
8152: from pay_assignment_actions paa,
8153: per_all_assignments_f pera
8154: where paa.assignment_action_id = assactid
8155: and pera.assignment_id = paa.assignment_id
8156: and effdate between pera.effective_start_date
8346:
8347: -- determine the master action
8348: SELECT pai.locked_action_id
8349: INTO l_mst_id
8350: FROM pay_action_interlocks pai,pay_assignment_actions paa
8351: WHERE pai.locking_action_id = p_act_id
8352: AND paa.assignment_action_id = pai.locked_action_id
8353: AND paa.source_action_id is null;
8354:
8354:
8355: select 1
8356: into found
8357: from dual
8358: where exists (select 1 from pay_assignment_actions paa
8359: where paa.source_action_id=l_mst_id);
8360:
8361: begin
8362: -- if there is no run for that interlocok cacluate
8363:
8364: -- what the new assignment action should be
8365: select child_asg.assignment_action_id
8366: into new_assactid
8367: from pay_assignment_actions mast_asg,
8368: pay_assignment_actions child_asg,
8369: pay_assignment_actions rev_asg
8370: where l_mst_id=mast_asg.assignment_action_id
8371: and child_asg.source_action_id is not null
8364: -- what the new assignment action should be
8365: select child_asg.assignment_action_id
8366: into new_assactid
8367: from pay_assignment_actions mast_asg,
8368: pay_assignment_actions child_asg,
8369: pay_assignment_actions rev_asg
8370: where l_mst_id=mast_asg.assignment_action_id
8371: and child_asg.source_action_id is not null
8372: and child_asg.assignment_id=mast_asg.assignment_id
8365: select child_asg.assignment_action_id
8366: into new_assactid
8367: from pay_assignment_actions mast_asg,
8368: pay_assignment_actions child_asg,
8369: pay_assignment_actions rev_asg
8370: where l_mst_id=mast_asg.assignment_action_id
8371: and child_asg.source_action_id is not null
8372: and child_asg.assignment_id=mast_asg.assignment_id
8373: and child_asg.payroll_action_id=mast_asg.payroll_action_id