[Home] [Help]
51: 10-Nov-2011 pparate 115.153 13359813 For reversal changed masking from 21digits 38decimals to
52: 23digits 36decimals as result value for customer exceeded
53: 21digits before decimal.
54: 16-Jun-2011 vpallapo 115.152 12356133 Performance fix, added hints to force indexes and avoid full table scans
55: while inserting into pay_assignment_actions.
56: 22-Nov-2010 priupadh 115.151 10212578 Modified maintain_lat_bal now calling pay_core_utils.get_legislation_rule
57: for leg rule 'BAL_ADJ_LAT_BAL'
58: 22-Apr-2010 phattarg 115.150 9089219 Removed ORDERED hint from cursor albc_selective
59: in hrassact.del_latest_balances.
148: 29-APR-2005 SuSivasu 115.110 Added p_reason parameter for
149: ext_man_payment.
150: 29-APR-2005 alogue 115.109 4337565 Support of ENABLE_RR_SPARSE
151: upgrade.
152: 14-APR-2005 alogue 115.108 3465844 Use PAY_ASSIGNMENT_ACTIONS_N51
153: instead of PAY_ASSIGNMENT_ACTIONS_N1
154: in index hints.
155: 04-APR-2005 alogue 115.107 Latest Balance Maintenance by
156: Reversals.
149: ext_man_payment.
150: 29-APR-2005 alogue 115.109 4337565 Support of ENABLE_RR_SPARSE
151: upgrade.
152: 14-APR-2005 alogue 115.108 3465844 Use PAY_ASSIGNMENT_ACTIONS_N51
153: instead of PAY_ASSIGNMENT_ACTIONS_N1
154: in index hints.
155: 04-APR-2005 alogue 115.107 Latest Balance Maintenance by
156: Reversals.
157: 23-FEB-2005 nbristow 115.106 Changes for Period Allocation.
560: business_group_id index.
561: 05-OCT-1994 DKERR 40.20 Set the OBJECT_VERSION_NUMBER
562: to 1 for inserts into
563: PAY_PAYROLL_ACTIONS and
564: PAY_ASSIGNMENT_ACTIONS for
565: all action types.
566: 28-JUN-1994 DSAXBY 40.19 Added line to decode statement
567: in reversal routine, to
568: prevent invalid number errors.
647: sz number
648: );
649: --
650: type assact_details is record
651: (assact_id pay_assignment_actions.assignment_action_id%type,
652: assignment_id pay_assignment_actions.assignment_id%type,
653: full_name per_all_people_f.full_name%type,
654: assignment_number per_all_assignments_f.assignment_number%type,
655: payroll_id per_all_assignments_f.payroll_id%type);
648: );
649: --
650: type assact_details is record
651: (assact_id pay_assignment_actions.assignment_action_id%type,
652: assignment_id pay_assignment_actions.assignment_id%type,
653: full_name per_all_people_f.full_name%type,
654: assignment_number per_all_assignments_f.assignment_number%type,
655: payroll_id per_all_assignments_f.payroll_id%type);
656: --
852: select null
853: from dual
854: where exists
855: (select null -- check for any ASSACTs
856: from pay_assignment_actions
857: where payroll_action_id = p_pact_id)
858: or exists
859: (select null -- check for any MESSAGEs
860: from pay_message_lines
892: p_assact_rec in out nocopy assact_details,
893: p_rollback_mode in varchar2)
894: return boolean is
895: --
896: l_action_sequence pay_assignment_actions.action_sequence%type;
897: l_action_status pay_assignment_actions.action_status%type;
898: l_person_id per_all_people_f.person_id%type;
899: begin
900: --
893: p_rollback_mode in varchar2)
894: return boolean is
895: --
896: l_action_sequence pay_assignment_actions.action_sequence%type;
897: l_action_status pay_assignment_actions.action_status%type;
898: l_person_id per_all_people_f.person_id%type;
899: begin
900: --
901: g_debug := hr_utility.debug_enabled;
921: p_assact_rec.assignment_number
922: from per_periods_of_service POS,
923: per_all_assignments_f ASS,
924: per_all_people_f PEO,
925: pay_assignment_actions ACT
926: where ACT.assignment_action_id = p_assact_rec.assact_id
927: and ASS.assignment_id = ACT.assignment_id
928: and p_pact_rec.action_date between
929: ASS.effective_start_date and ASS.effective_end_date
972: from dual
973: where not exists (
974: select null
975: from pay_action_interlocks int,
976: pay_assignment_actions act
977: where int.locked_action_id = p_assact_rec.assact_id
978: and act.assignment_action_id = int.locking_action_id
979: and act.action_status <> 'M');
980: else
1018: select null into dummy
1019: from dual
1020: where not exists
1021: (select null
1022: from pay_assignment_actions ACT,
1023: pay_payroll_actions PACT,
1024: pay_action_classifications CLASS,
1025: pay_action_classifications CLAS2
1026: where ACT.assignment_id = p_assact_rec.assignment_id
1039: select null into dummy
1040: from dual
1041: where not exists
1042: (select null
1043: from pay_assignment_actions ACT,
1044: pay_payroll_actions PACT,
1045: pay_action_classifications CLASS,
1046: pay_action_classifications CLAS2
1047: where ACT.assignment_id = p_assact_rec.assignment_id
1067: (select null
1068: from pay_action_classifications CLASS,
1069: pay_action_classifications CLAS2,
1070: pay_payroll_actions PACT,
1071: pay_assignment_actions ACT,
1072: per_all_assignments_f ASS,
1073: per_periods_of_service POS
1074: where POS.person_id = l_person_id
1075: and ASS.period_of_service_id = POS.period_of_service_id
1089: (select null
1090: from pay_action_classifications CLASS,
1091: pay_action_classifications CLAS2,
1092: pay_payroll_actions PACT,
1093: pay_assignment_actions ACT,
1094: per_all_assignments_f ASS,
1095: per_periods_of_service POS
1096: where POS.person_id = l_person_id
1097: and ASS.period_of_service_id = POS.period_of_service_id
1308: procedure trash_quickpay (p_action_id number) is
1309: cursor c1 is
1310: select pee.element_entry_id
1311: from pay_element_entries_f pee,
1312: pay_assignment_actions paa
1313: where pee.creator_type = 'Q'
1314: and pee.creator_id = paa.assignment_action_id
1315: and pee.assignment_id = paa.assignment_id
1316: and paa.assignment_action_id = p_action_id
1903: if p_rollback_mode = 'RETRY' then
1904: if g_debug then
1905: hr_utility.set_location('hrassact.do_assact_rollback',170);
1906: end if;
1907: update pay_assignment_actions
1908: set action_status = 'M'
1909: where assignment_action_id = p_assact_rec.assact_id;
1910: --
1911: elsif p_rollback_mode = 'BACKPAY' then
1911: elsif p_rollback_mode = 'BACKPAY' then
1912: if g_debug then
1913: hr_utility.set_location('hrassact.do_assact_rollback',180);
1914: end if;
1915: update pay_assignment_actions
1916: set action_status = 'B'
1917: where assignment_action_id = p_assact_rec.assact_id;
1918: --
1919: elsif p_rollback_mode = 'ROLLBACK' then
1927: --
1928: if g_debug then
1929: hr_utility.set_location('hrassact.do_assact_rollback',200);
1930: end if;
1931: delete from pay_assignment_actions
1932: where assignment_action_id = p_assact_rec.assact_id;
1933: end if;
1934: else
1935: -- In the case of rolling back (from the form), we
2003: hr_utility.set_location(c_indent, 20);
2004: end if;
2005: select max(act.action_sequence) + 1
2006: into l_cur_aseq
2007: from pay_assignment_actions act
2008: where act.payroll_action_id = p_payroll_action_id;
2009: --
2010: -- now roll back each assignment action in turn (lock as we go)
2011: declare
2019: -- the first chunk.
2020: cursor c1 is
2021: select act.assignment_action_id,
2022: act.action_sequence
2023: from pay_assignment_actions act
2024: where act.payroll_action_id = p_payroll_action_id
2025: and act.action_sequence < l_cur_aseq
2026: order by act.action_sequence desc
2027: for update of act.action_status;
2303: l_pact_rec pact_details; -- payroll action details
2304: l_assact_rec assact_details; -- assignment action details
2305: cursor c1 is
2306: select payroll_action_id
2307: from pay_assignment_actions
2308: where assignment_action_id = p_assignment_action_id
2309: for update of action_status;
2310: --
2311: --
2925: cursor albc (p_person_id number) is
2926: select /*+ ORDERED */ alb.latest_balance_id
2927: from per_periods_of_service pos,
2928: per_all_assignments_f asg,
2929: pay_assignment_actions act,
2930: pay_payroll_actions pac,
2931: pay_assignment_latest_balances alb
2932: where pos.person_id = p_person_id
2933: and asg.period_of_service_id = pos.period_of_service_id
2951: pay_input_values_f piv,
2952: pay_balance_feeds_f pbf,
2953: pay_defined_balances pdb,
2954: pay_person_latest_balances plb,
2955: pay_assignment_actions paa,
2956: pay_payroll_actions ppa
2957: where plb.person_id = p_person_id
2958: and piv.element_type_id = p_eletyp_id
2959: and piv.input_value_id = pbf.input_value_id
2970: p_eletyp_id number) is
2971: select
2972: alb.latest_balance_id
2973: from pay_assignment_latest_balances alb,
2974: pay_assignment_actions act,
2975: pay_payroll_actions pac,
2976: pay_defined_balances pdb,
2977: pay_balance_feeds_f pbf,
2978: pay_input_values_f piv
3001: pay_input_values_f piv,
3002: pay_balance_feeds_f pbf,
3003: pay_defined_balances pdb,
3004: pay_latest_balances plb,
3005: pay_assignment_actions paa,
3006: pay_payroll_actions ppa
3007: where plb.person_id = p_person_id
3008: and piv.element_type_id = p_eletyp_id
3009: and piv.input_value_id = pbf.input_value_id
3145: begin
3146: select payroll_action_id,
3147: action_sequence,
3148: assignment_id,
3149: pay_assignment_actions_s.nextval
3150: into pact,
3151: actseq,
3152: asgid,
3153: newseq
3150: into pact,
3151: actseq,
3152: asgid,
3153: newseq
3154: from pay_assignment_actions
3155: where assignment_action_id = p_assact;
3156: --
3157: resequence_actions(pact, asgid, actseq, rmode);
3158: --
3155: where assignment_action_id = p_assact;
3156: --
3157: resequence_actions(pact, asgid, actseq, rmode);
3158: --
3159: update pay_assignment_actions
3160: set action_sequence = newseq
3161: where assignment_action_id = p_assact;
3162: --
3163: update pay_run_balances
3186: select ppa.effective_date,
3187: paa.assignment_action_id,
3188: ppa.action_type
3189: from pay_payroll_actions ppa,
3190: pay_assignment_actions paa
3191: where ppa.payroll_action_id = pactid
3192: and paa.payroll_action_id = ppa.payroll_action_id
3193: and paa.source_action_id is null
3194: and paa.chunk_number = cnkno;
3229: ) is
3230: select ac2.rowid,
3231: ac2.assignment_action_id
3232: from pay_action_classifications acl,
3233: pay_assignment_actions ac2,
3234: pay_assignment_actions paa,
3235: pay_payroll_actions ppa,
3236: pay_payroll_actions pa2
3237: where paa.assignment_action_id = aaid
3230: select ac2.rowid,
3231: ac2.assignment_action_id
3232: from pay_action_classifications acl,
3233: pay_assignment_actions ac2,
3234: pay_assignment_actions paa,
3235: pay_payroll_actions ppa,
3236: pay_payroll_actions pa2
3237: where paa.assignment_action_id = aaid
3238: and ppa.payroll_action_id = paa.payroll_action_id
3253: aaid number,
3254: chldact varchar2
3255: ) is
3256: select /*+ ORDERED USE_NL(ac2, pa2)
3257: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N1)
3258: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK) */
3259: ac2.rowid,
3260: ac2.assignment_action_id
3261: from pay_assignment_actions act,
3257: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N1)
3258: INDEX(pa2 PAY_PAYROLL_ACTIONS_PK) */
3259: ac2.rowid,
3260: ac2.assignment_action_id
3261: from pay_assignment_actions act,
3262: pay_payroll_actions pac,
3263: per_all_assignments_f asg,
3264: per_all_assignments_f as2,
3265: pay_assignment_actions ac2,
3261: from pay_assignment_actions act,
3262: pay_payroll_actions pac,
3263: per_all_assignments_f asg,
3264: per_all_assignments_f as2,
3265: pay_assignment_actions ac2,
3266: pay_payroll_actions pa2,
3267: pay_action_classifications acl
3268: where act.assignment_action_id = aaid
3269: and pac.payroll_action_id = act.payroll_action_id
3300: ac2.assignment_action_id
3301: from
3302: (
3303: select distinct ac3.assignment_action_id
3304: from pay_assignment_actions act,
3305: pay_object_groups pog,
3306: pay_object_groups pog2,
3307: pay_object_groups pog3,
3308: pay_payroll_actions pac,
3306: pay_object_groups pog2,
3307: pay_object_groups pog3,
3308: pay_payroll_actions pac,
3309: pay_object_groups pog4,
3310: pay_assignment_actions ac3,
3311: pay_payroll_actions pa3,
3312: pay_action_classifications acl
3313: where act.assignment_action_id = aaid
3314: and pac.payroll_action_id = act.payroll_action_id
3326: and pa3.action_type = acl.action_type
3327: and acl.classification_name = 'SEQUENCED'
3328: union all
3329: select distinct ac3.assignment_action_id
3330: from pay_assignment_actions act,
3331: pay_object_groups pog,
3332: pay_object_groups pog2,
3333: pay_object_groups pog3,
3334: pay_payroll_actions pac,
3331: pay_object_groups pog,
3332: pay_object_groups pog2,
3333: pay_object_groups pog3,
3334: pay_payroll_actions pac,
3335: pay_assignment_actions ac3,
3336: pay_payroll_actions pa3
3337: where act.assignment_action_id = aaid
3338: and pac.payroll_action_id = act.payroll_action_id
3339: and pog.source_id = act.assignment_id
3346: and ac3.object_type = 'POG'
3347: and pa3.payroll_action_id = ac3.payroll_action_id
3348: and pa3.effective_date > pac.effective_date
3349: and pa3.action_type = 'L') V,
3350: pay_assignment_actions ac2,
3351: pay_payroll_actions pa2
3352: where ac2.assignment_action_id = V.assignment_action_id
3353: and pa2.payroll_action_id = ac2.payroll_action_id
3354: and (( chldact = 'N'
3369: ac2.assignment_action_id
3370: from
3371: (
3372: select distinct ac3.assignment_action_id
3373: from pay_assignment_actions act,
3374: pay_object_groups pog,
3375: pay_object_groups pog2,
3376: pay_payroll_actions pac,
3377: pay_object_groups pog3,
3374: pay_object_groups pog,
3375: pay_object_groups pog2,
3376: pay_payroll_actions pac,
3377: pay_object_groups pog3,
3378: pay_assignment_actions ac3,
3379: pay_payroll_actions pa3,
3380: pay_action_classifications acl
3381: where act.assignment_action_id = aaid
3382: and pac.payroll_action_id = act.payroll_action_id
3392: and pa3.action_type = acl.action_type
3393: and acl.classification_name = 'SEQUENCED'
3394: union all
3395: select distinct ac3.assignment_action_id
3396: from pay_assignment_actions act,
3397: pay_object_groups pog,
3398: pay_object_groups pog2,
3399: pay_payroll_actions pac,
3400: pay_assignment_actions ac3,
3396: from pay_assignment_actions act,
3397: pay_object_groups pog,
3398: pay_object_groups pog2,
3399: pay_payroll_actions pac,
3400: pay_assignment_actions ac3,
3401: pay_payroll_actions pa3
3402: where act.assignment_action_id = aaid
3403: and pac.payroll_action_id = act.payroll_action_id
3404: and pog.object_group_id = act.object_id
3409: and ac3.object_type = 'POG'
3410: and pa3.payroll_action_id = ac3.payroll_action_id
3411: and pa3.effective_date > pac.effective_date
3412: and pa3.action_type = 'L') V,
3413: pay_assignment_actions ac2,
3414: pay_payroll_actions pa2
3415: where ac2.assignment_action_id = V.assignment_action_id
3416: and pa2.payroll_action_id = ac2.payroll_action_id
3417: and (( chldact = 'N'
3422: order by pa2.effective_date, ac2.action_sequence
3423: for update of ac2.assignment_action_id;
3424: --
3425: my_rowid rowid;
3426: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3427: --
3428: begin
3429: --
3430: g_debug := hr_utility.debug_enabled;
3465: end if;
3466: --
3467: --
3468: -- Now, update with new action_sequence.
3469: update pay_assignment_actions act
3470: set act.action_sequence = pay_assignment_actions_s.nextval
3471: where act.rowid = my_rowid;
3472: --
3473: update pay_run_balances
3466: --
3467: --
3468: -- Now, update with new action_sequence.
3469: update pay_assignment_actions act
3470: set act.action_sequence = pay_assignment_actions_s.nextval
3471: where act.rowid = my_rowid;
3472: --
3473: update pay_run_balances
3474: set action_sequence = pay_assignment_actions_s.currval
3470: set act.action_sequence = pay_assignment_actions_s.nextval
3471: where act.rowid = my_rowid;
3472: --
3473: update pay_run_balances
3474: set action_sequence = pay_assignment_actions_s.currval
3475: where assignment_action_id = upd_aa_id;
3476: --
3477: end loop;
3478: --
3501: --
3502: cursor get_chld (p_aa_id number)
3503: is
3504: select assignment_action_id
3505: from pay_assignment_actions
3506: where source_action_id = p_aa_id
3507: order by action_sequence;
3508: --
3509: begin
3511: for chdrec in get_chld(p_asg_action) loop
3512: --
3513: resequence_children(chdrec.assignment_action_id);
3514: --
3515: update pay_assignment_actions
3516: set action_sequence = pay_assignment_actions_s.nextval
3517: where assignment_action_id = chdrec.assignment_action_id;
3518: --
3519: end loop;
3512: --
3513: resequence_children(chdrec.assignment_action_id);
3514: --
3515: update pay_assignment_actions
3516: set action_sequence = pay_assignment_actions_s.nextval
3517: where assignment_action_id = chdrec.assignment_action_id;
3518: --
3519: end loop;
3520: --
3537: ac2.assignment_action_id,
3538: ac2.source_action_id,
3539: ac2.object_type
3540: from pay_action_classifications acl,
3541: pay_assignment_actions ac2,
3542: pay_payroll_actions pa2
3543: where ac2.assignment_id = asgid
3544: and pa2.payroll_action_id = ac2.payroll_action_id
3545: and acl.classification_name = 'SEQUENCED'
3557: asgid number,
3558: actseq number,
3559: rmode varchar2) is
3560: select /*+ ORDERED
3561: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N51)
3562: (pa2 PAY_PAYROLL_ACTIONS_PK) */
3563: ac2.rowid,
3564: ac2.assignment_action_id,
3565: ac2.source_action_id,
3566: ac2.object_type
3567: from pay_payroll_actions pac,
3568: per_all_assignments_f asg,
3569: per_all_assignments_f as2,
3570: pay_assignment_actions act,
3571: pay_assignment_actions ac2,
3572: pay_payroll_actions pa2,
3573: pay_action_classifications acl
3574: where pac.payroll_action_id = pactid
3567: from pay_payroll_actions pac,
3568: per_all_assignments_f asg,
3569: per_all_assignments_f as2,
3570: pay_assignment_actions act,
3571: pay_assignment_actions ac2,
3572: pay_payroll_actions pa2,
3573: pay_action_classifications acl
3574: where pac.payroll_action_id = pactid
3575: and act.payroll_action_id = pac.payroll_action_id
3604: asgid number,
3605: actseq number,
3606: rmode varchar2) is
3607: select /*+ ORDERED
3608: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_PK)
3609: (pa2 PAY_PAYROLL_ACTIONS_PK) */
3610: ac2.rowid,
3611: ac2.assignment_action_id,
3612: ac2.source_action_id,
3615: (
3616: select /*+ ORDERED */
3617: distinct ac3.assignment_action_id
3618: from pay_payroll_actions pac,
3619: pay_assignment_actions act,
3620: pay_object_groups pog,
3621: pay_object_groups pog2,
3622: pay_object_groups pog3,
3623: pay_object_groups pog4,
3620: pay_object_groups pog,
3621: pay_object_groups pog2,
3622: pay_object_groups pog3,
3623: pay_object_groups pog4,
3624: pay_assignment_actions ac3,
3625: pay_payroll_actions pa3,
3626: pay_action_classifications acl
3627: where pac.payroll_action_id = pactid
3628: and act.payroll_action_id = pac.payroll_action_id
3646: union all
3647: select /*+ ORDERED */
3648: distinct ac3.assignment_action_id
3649: from pay_payroll_actions pac,
3650: pay_assignment_actions act,
3651: pay_object_groups pog,
3652: pay_object_groups pog2,
3653: pay_object_groups pog3,
3654: pay_assignment_actions ac3,
3650: pay_assignment_actions act,
3651: pay_object_groups pog,
3652: pay_object_groups pog2,
3653: pay_object_groups pog3,
3654: pay_assignment_actions ac3,
3655: pay_payroll_actions pa3
3656: where pac.payroll_action_id = pactid
3657: and act.payroll_action_id = pac.payroll_action_id
3658: and act.assignment_id = asgid
3669: and ac3.object_type = 'POG'
3670: and ac3.action_sequence > actseq
3671: and pa3.payroll_action_id = ac3.payroll_action_id
3672: and pa3.action_type = 'L') V,
3673: pay_assignment_actions ac2,
3674: pay_payroll_actions pa2
3675: where ac2.assignment_action_id = V.assignment_action_id
3676: and pa2.payroll_action_id = ac2.payroll_action_id
3677: and (ac2.source_action_id is null
3683: for update of ac2.assignment_action_id;
3684:
3685: --
3686: my_rowid rowid;
3687: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3688: src_aa_id pay_assignment_actions.source_action_id%type;
3689: obj_type pay_assignment_actions.object_type%type;
3690: --
3691: begin
3684:
3685: --
3686: my_rowid rowid;
3687: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3688: src_aa_id pay_assignment_actions.source_action_id%type;
3689: obj_type pay_assignment_actions.object_type%type;
3690: --
3691: begin
3692: --
3685: --
3686: my_rowid rowid;
3687: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3688: src_aa_id pay_assignment_actions.source_action_id%type;
3689: obj_type pay_assignment_actions.object_type%type;
3690: --
3691: begin
3692: --
3693: g_debug := hr_utility.debug_enabled;
3724: resequence_children(upd_aa_id);
3725: --
3726: --
3727: -- Now, update with new action_sequence.
3728: update pay_assignment_actions act
3729: set act.action_sequence = pay_assignment_actions_s.nextval
3730: where act.rowid = my_rowid;
3731: --
3732: update pay_run_balances
3725: --
3726: --
3727: -- Now, update with new action_sequence.
3728: update pay_assignment_actions act
3729: set act.action_sequence = pay_assignment_actions_s.nextval
3730: where act.rowid = my_rowid;
3731: --
3732: update pay_run_balances
3733: set action_sequence = pay_assignment_actions_s.currval
3729: set act.action_sequence = pay_assignment_actions_s.nextval
3730: where act.rowid = my_rowid;
3731: --
3732: update pay_run_balances
3733: set action_sequence = pay_assignment_actions_s.currval
3734: where assignment_action_id = upd_aa_id;
3735: --
3736: /* If it's an assignment action thats created due to a
3737: POG then we need to up the POGs action sequence
3739: if (obj_type is not null
3740: and obj_type = 'POG'
3741: and src_aa_id is not null) then
3742: --
3743: update pay_assignment_actions act
3744: set act.action_sequence = pay_assignment_actions_s.nextval
3745: where act.assignment_action_id = src_aa_id;
3746: --
3747: end if;
3740: and obj_type = 'POG'
3741: and src_aa_id is not null) then
3742: --
3743: update pay_assignment_actions act
3744: set act.action_sequence = pay_assignment_actions_s.nextval
3745: where act.assignment_action_id = src_aa_id;
3746: --
3747: end if;
3748: --
4150: asgid number) is
4151: select ac2.rowid,
4152: ac2.assignment_action_id
4153: from pay_action_classifications acl,
4154: pay_assignment_actions ac2,
4155: pay_assignment_actions act,
4156: pay_payroll_actions pa2,
4157: pay_payroll_actions pac
4158: where pac.payroll_action_id = pactid
4151: select ac2.rowid,
4152: ac2.assignment_action_id
4153: from pay_action_classifications acl,
4154: pay_assignment_actions ac2,
4155: pay_assignment_actions act,
4156: pay_payroll_actions pa2,
4157: pay_payroll_actions pac
4158: where pac.payroll_action_id = pactid
4159: and act.payroll_action_id = pac.payroll_action_id
4177: --
4178: cursor seqper (pactid number,
4179: asgid number) is
4180: select /*+
4181: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N51) */
4182: ac2.rowid,
4183: ac2.assignment_action_id
4184: from pay_payroll_actions pac,
4185: pay_assignment_actions act,
4181: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N51) */
4182: ac2.rowid,
4183: ac2.assignment_action_id
4184: from pay_payroll_actions pac,
4185: pay_assignment_actions act,
4186: per_all_assignments_f asg,
4187: per_all_assignments_f as2,
4188: pay_assignment_actions ac2,
4189: pay_payroll_actions pa2,
4184: from pay_payroll_actions pac,
4185: pay_assignment_actions act,
4186: per_all_assignments_f asg,
4187: per_all_assignments_f as2,
4188: pay_assignment_actions ac2,
4189: pay_payroll_actions pa2,
4190: pay_action_classifications acl
4191: where pac.payroll_action_id = pactid
4192: and act.payroll_action_id = pac.payroll_action_id
4218: --
4219: cursor seqgrp (pactid number,
4220: asgid number) is
4221: select /*+ ORDERED
4222: INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_PK) */
4223: ac2.rowid,
4224: ac2.assignment_action_id
4225: from
4226: (
4226: (
4227: select /*+ ORDERED */
4228: distinct ac3.assignment_action_id
4229: from pay_payroll_actions pac,
4230: pay_assignment_actions act,
4231: pay_object_groups pog,
4232: pay_object_groups pog2,
4233: pay_object_groups pog3,
4234: pay_object_groups pog4,
4231: pay_object_groups pog,
4232: pay_object_groups pog2,
4233: pay_object_groups pog3,
4234: pay_object_groups pog4,
4235: pay_assignment_actions ac3,
4236: pay_payroll_actions pa3,
4237: pay_action_classifications acl
4238: where pac.payroll_action_id = pactid
4239: and act.payroll_action_id = pac.payroll_action_id
4254: union all
4255: select /*+ ORDERED */
4256: distinct ac3.assignment_action_id
4257: from pay_payroll_actions pac,
4258: pay_assignment_actions act,
4259: pay_object_groups pog,
4260: pay_object_groups pog2,
4261: pay_object_groups pog3,
4262: pay_assignment_actions ac3,
4258: pay_assignment_actions act,
4259: pay_object_groups pog,
4260: pay_object_groups pog2,
4261: pay_object_groups pog3,
4262: pay_assignment_actions ac3,
4263: pay_payroll_actions pa3
4264: where pac.payroll_action_id = pactid
4265: and act.payroll_action_id = pac.payroll_action_id
4266: and act.assignment_id = asgid
4274: and ac3.object_type = 'POG'
4275: and pa3.payroll_action_id = ac3.payroll_action_id
4276: and pa3.effective_date > pac.effective_date
4277: and pa3.action_type = 'L') V,
4278: pay_assignment_actions ac2,
4279: pay_payroll_actions pa2
4280: where ac2.assignment_action_id = V.assignment_action_id
4281: and pa2.payroll_action_id = ac2.payroll_action_id
4282: order by pa2.effective_date, ac2.action_sequence
4296: and pog_act.parent_object_group_id = pog_grp.parent_object_group_id
4297: and pog_grp.source_type = 'PAF';
4298: --
4299: my_rowid rowid;
4300: upd_aa_id pay_assignment_actions.assignment_action_id%type;
4301: actype pay_payroll_actions.action_type%type;
4302: effdate date; -- the effective date of the payroll action.
4303: lgcode varchar2(30); -- Used to check if the US legislation.
4304: persid number; -- assignments person_id
4420: where exists (
4421: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4422: from pay_action_classifications pay_acl,
4423: pay_payroll_actions pay_pa2,
4424: pay_assignment_actions pay_ac2
4425: where pay_ac2.assignment_id = asgid
4426: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4427: and pay_acl.classification_name = 'SEQUENCED'
4428: and pay_pa2.action_type = pay_acl.action_type
4441: where exists (
4442: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4443: from pay_action_classifications pay_acl,
4444: pay_payroll_actions pay_pa2,
4445: pay_assignment_actions pay_ac2
4446: where pay_ac2.assignment_id = dprec.assignment_id
4447: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4448: and pay_acl.classification_name = 'SEQUENCED'
4449: and pay_pa2.action_type = pay_acl.action_type
4466: where exists (
4467: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
4468: from pay_action_classifications pay_acl,
4469: pay_payroll_actions pay_pa2,
4470: pay_assignment_actions pay_ac2
4471: where pay_ac2.assignment_id = dprec.assignment_id
4472: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
4473: and pay_acl.classification_name = 'SEQUENCED'
4474: and pay_pa2.action_type = pay_acl.action_type
4495: -- initialisation to perform balance rollup.
4496: if g_debug then
4497: hr_utility.set_location('hrassact.irbaact',25);
4498: end if;
4499: insert into pay_assignment_actions (
4500: assignment_action_id,
4501: assignment_id,
4502: payroll_action_id,
4503: action_status,
4519: where not exists (
4520: select null
4521: from pay_payroll_actions pac,
4522: pay_payroll_actions pa2,
4523: pay_assignment_actions act,
4524: pay_assignment_actions ac2
4525: where pac.payroll_action_id = pactid
4526: and pac.action_type <> 'I'
4527: and act.payroll_action_id = pac.payroll_action_id
4520: select null
4521: from pay_payroll_actions pac,
4522: pay_payroll_actions pa2,
4523: pay_assignment_actions act,
4524: pay_assignment_actions ac2
4525: where pac.payroll_action_id = pactid
4526: and pac.action_type <> 'I'
4527: and act.payroll_action_id = pac.payroll_action_id
4528: and act.assignment_id = asgid
4576: end if;
4577: --
4578: --
4579: -- Now, update with new action_sequence.
4580: update pay_assignment_actions act
4581: set act.action_sequence = pay_assignment_actions_s.nextval
4582: where act.rowid = my_rowid;
4583: --
4584: update pay_run_balances
4577: --
4578: --
4579: -- Now, update with new action_sequence.
4580: update pay_assignment_actions act
4581: set act.action_sequence = pay_assignment_actions_s.nextval
4582: where act.rowid = my_rowid;
4583: --
4584: update pay_run_balances
4585: set action_sequence = pay_assignment_actions_s.currval
4581: set act.action_sequence = pay_assignment_actions_s.nextval
4582: where act.rowid = my_rowid;
4583: --
4584: update pay_run_balances
4585: set action_sequence = pay_assignment_actions_s.currval
4586: where assignment_action_id = upd_aa_id;
4587: end loop;
4588: --
4589: --
4603: --
4604: -- Start by grabbing the type of the action we are dealing with.
4605: select pac.action_type,
4606: pac.business_group_id,
4607: pay_assignment_actions_s.nextval
4608: into actyp,
4609: bgid,
4610: aa_id
4611: from pay_payroll_actions pac
4692: hr_utility.set_location('hrassact.inassact_main',20);
4693: end if;
4694: if (gv_dum_qpay) then
4695: hr_utility.set_location('hrassact.inassact_main Dummy quickpay ',20);
4696: insert into pay_assignment_actions (
4697: assignment_action_id,
4698: assignment_id,
4699: payroll_action_id,
4700: action_status,
4727: and pbg.business_group_id = pac.business_group_id
4728: and not exists (
4729: select null
4730: from pay_payroll_actions pa2,
4731: pay_assignment_actions ac2,
4732: per_time_periods ptp
4733: where ac2.assignment_id = asg.assignment_id
4734: and pa2.payroll_action_id = ac2.payroll_action_id
4735: and ptp.payroll_id = pac.payroll_id
4736: and pac.effective_date between ptp.start_date and ptp.end_date
4737: and pa2.effective_date <= ptp.end_date);
4738: else
4739: hr_utility.set_location('hrassact.inassact_main Normal quickpay ',20);
4740: insert into pay_assignment_actions (
4741: assignment_action_id,
4742: assignment_id,
4743: payroll_action_id,
4744: action_status,
4772: and not exists (
4773: select null
4774: from pay_action_classifications acl,
4775: pay_payroll_actions pa2,
4776: pay_assignment_actions ac2
4777: where ac2.assignment_id = asg.assignment_id
4778: and pa2.payroll_action_id = ac2.payroll_action_id
4779: and acl.classification_name = 'SEQUENCED'
4780: and pa2.action_type = acl.action_type
4788: hr_utility.set_location('hrassact.inassact_main',22);
4789: end if;
4790: if (gv_dum_qpay) then
4791: hr_utility.set_location('hrassact.inassact_main Dummy quickpay ',22);
4792: insert into pay_assignment_actions (
4793: assignment_action_id,
4794: assignment_id,
4795: payroll_action_id,
4796: action_status,
4826: and pog.source_type = 'PAF'
4827: and pbg.business_group_id = pac.business_group_id
4828: and not exists (
4829: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4830: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4831: from pay_assignment_actions ac2,
4832: pay_payroll_actions pa2,
4833: pay_object_groups pog2,
4834: per_time_periods ptp
4827: and pbg.business_group_id = pac.business_group_id
4828: and not exists (
4829: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4830: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4831: from pay_assignment_actions ac2,
4832: pay_payroll_actions pa2,
4833: pay_object_groups pog2,
4834: per_time_periods ptp
4835: where pog2.parent_object_group_id = pog.parent_object_group_id
4839: and pac.effective_date between ptp.start_date and ptp.end_date
4840: and pa2.effective_date <= ptp.end_date);
4841: else
4842: hr_utility.set_location('hrassact.inassact_main Normal quickpay ',22);
4843: insert into pay_assignment_actions (
4844: assignment_action_id,
4845: assignment_id,
4846: payroll_action_id,
4847: action_status,
4877: and pog.source_type = 'PAF'
4878: and pbg.business_group_id = pac.business_group_id
4879: and not exists (
4880: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4881: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4882: from pay_action_classifications acl,
4883: pay_assignment_actions ac2,
4884: pay_payroll_actions pa2,
4885: pay_object_groups pog2
4879: and not exists (
4880: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4881: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4882: from pay_action_classifications acl,
4883: pay_assignment_actions ac2,
4884: pay_payroll_actions pa2,
4885: pay_object_groups pog2
4886: where pog2.parent_object_group_id = pog.parent_object_group_id
4887: and ac2.assignment_id = pog2.source_id
4899: hr_utility.set_location('hrassact.inassact_main',30);
4900: end if;
4901: if (gv_dum_qpay) then
4902: hr_utility.set_location('hrassact.inassact_main Dummy quickpay ',30);
4903: insert into pay_assignment_actions (
4904: assignment_action_id,
4905: assignment_id,
4906: payroll_action_id,
4907: action_status,
4934: as2.effective_start_date and as2.effective_end_date
4935: and pbg.business_group_id = pac.business_group_id
4936: and not exists (
4937: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4938: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4939: from pay_assignment_actions ac2,
4940: pay_payroll_actions pa2,
4941: per_all_assignments_f as2,
4942: per_time_periods ptp
4935: and pbg.business_group_id = pac.business_group_id
4936: and not exists (
4937: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4938: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4939: from pay_assignment_actions ac2,
4940: pay_payroll_actions pa2,
4941: per_all_assignments_f as2,
4942: per_time_periods ptp
4943: where as2.period_of_service_id = pos.period_of_service_id
4948: and pa2.effective_date <= ptp.end_date);
4949: else
4950: hr_utility.set_location('hrassact.inassact_main Normal quickpay ',30);
4951: -- Changes for bug 12356133, forced indexes PAY_PAYROLL_ACTIONS_PK and
4952: -- PAY_ASSIGNMENT_ACTIONS_N1 through hints.
4953: insert into pay_assignment_actions (
4954: assignment_action_id,
4955: assignment_id,
4956: payroll_action_id,
4949: else
4950: hr_utility.set_location('hrassact.inassact_main Normal quickpay ',30);
4951: -- Changes for bug 12356133, forced indexes PAY_PAYROLL_ACTIONS_PK and
4952: -- PAY_ASSIGNMENT_ACTIONS_N1 through hints.
4953: insert into pay_assignment_actions (
4954: assignment_action_id,
4955: assignment_id,
4956: payroll_action_id,
4957: action_status,
4984: as2.effective_start_date and as2.effective_end_date
4985: and pbg.business_group_id = pac.business_group_id
4986: and not exists (
4987: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4988: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4989: from pay_action_classifications acl,
4990: pay_assignment_actions ac2,
4991: pay_payroll_actions pa2,
4992: per_all_assignments_f as2
4986: and not exists (
4987: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
4988: INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
4989: from pay_action_classifications acl,
4990: pay_assignment_actions ac2,
4991: pay_payroll_actions pa2,
4992: per_all_assignments_f as2
4993: where as2.period_of_service_id = pos.period_of_service_id
4994: and ac2.assignment_id = as2.assignment_id
5018: irbaact(pactid,asgid,rmode,actyp, p_element_entry, taxunt,
5019: p_purge_mode,p_run_type_id,aa_id, p_mode);
5020: elsif (actyp = 'E') then
5021: --
5022: -- Insert a pay_assignment_actions record for Pre-payment
5023: -- External/Manual payments.
5024: -- Only do this if the pre-payment has not already
5025: -- been paid.
5026: -- This is detected if there is any assignment action
5025: -- been paid.
5026: -- This is detected if there is any assignment action
5027: -- with a pre_payment_id the same as that we are attempting
5028: -- to process which is not locked by a void action.
5029: insert into PAY_ASSIGNMENT_ACTIONS (
5030: ASSIGNMENT_ACTION_ID,
5031: ASSIGNMENT_ID,
5032: PAYROLL_ACTION_ID,
5033: ACTION_STATUS,
5040: asgid,
5041: pactid,
5042: 'C',
5043: 1,
5044: PAY_ASSIGNMENT_ACTIONS_S.nextval,
5045: p_pre_payment_id,
5046: p_serial_number,
5047: 1
5048: from sys.dual
5047: 1
5048: from sys.dual
5049: where not exists (
5050: select null
5051: from pay_assignment_actions act
5052: where act.pre_payment_id = p_pre_payment_id
5053: and not exists
5054: ( select null
5055: from pay_action_interlocks loc1,
5052: where act.pre_payment_id = p_pre_payment_id
5053: and not exists
5054: ( select null
5055: from pay_action_interlocks loc1,
5056: pay_assignment_actions actv,
5057: pay_payroll_actions pactv
5058: where loc1.locked_action_id = act.assignment_action_id
5059: and loc1.locking_action_id = actv.assignment_action_id
5060: and pactv.payroll_action_id = actv.payroll_action_id
5079: --
5080: if g_debug then
5081: hr_utility.set_location('hrassact.inassact_main',45);
5082: end if;
5083: insert into PAY_ASSIGNMENT_ACTIONS (
5084: ASSIGNMENT_ACTION_ID,
5085: ASSIGNMENT_ID,
5086: PAYROLL_ACTION_ID,
5087: ACTION_STATUS,
5098: 1,
5099: hr_dynsql.get_tax_unit(asg.assignment_id,
5100: pac.effective_date)
5101: from per_business_groups_perf pbg,
5102: pay_assignment_actions act,
5103: pay_payroll_actions pac, -- prepay action.
5104: pay_payroll_actions pa2, -- the QuickPay action.
5105: per_periods_of_service pos,
5106: per_all_assignments_f asg
5114: and pos.period_of_service_id = asg.period_of_service_id
5115: and pbg.business_group_id = pac.business_group_id
5116: and not exists (
5117: select null
5118: from pay_assignment_actions ac2,
5119: pay_payroll_actions pa3,
5120: pay_action_interlocks int
5121: where int.locked_action_id = act.assignment_action_id
5122: and ac2.assignment_action_id = int.locking_action_id
5124: and pa3.action_type in ('P', 'U'))
5125: and not exists (
5126: select null
5127: from per_all_assignments_f as3,
5128: pay_assignment_actions ac3
5129: where rmode <> 'Y'
5130: and ac3.payroll_action_id = pa2.payroll_action_id
5131: and ac3.action_status not in ('C', 'S')
5132: and as3.assignment_id = ac3.assignment_id
5196: inassact(p_payroll_action_id, p_assignment_id, null, null, null);
5197: --
5198: -- Get the assignment_action_id
5199: -- that has just been inserted
5200: select pay_assignment_actions_s.currval
5201: into assactid
5202: from sys.dual;
5203: --
5204: -- Return information.
5237: hr_utility.set_location('hrassact.qpppassact',10);
5238: end if;
5239: select act.assignment_id
5240: into l_assignment_id
5241: from pay_assignment_actions act,
5242: pay_payroll_actions pac
5243: where pac.payroll_action_id = p_payroll_action_id
5244: and act.payroll_action_id = pac.target_payroll_action_id
5245: and act.source_action_id is null;
5255: --
5256: if g_debug then
5257: hr_utility.set_location('hrassact.qpppassact',30);
5258: end if;
5259: select pay_assignment_actions_s.currval,
5260: act.assignment_action_id
5261: into l_locking_action_id,
5262: l_locked_action_id
5263: from pay_payroll_actions pac,
5260: act.assignment_action_id
5261: into l_locking_action_id,
5262: l_locked_action_id
5263: from pay_payroll_actions pac,
5264: pay_assignment_actions act
5265: where pac.payroll_action_id = p_payroll_action_id
5266: and act.payroll_action_id = pac.target_payroll_action_id
5267: and act.source_action_id is null;
5268: --
5284: update pay_payroll_actions pac
5285: set pac.date_earned = (
5286: select pa2.date_earned
5287: from pay_payroll_actions pa2,
5288: pay_assignment_actions act
5289: where act.assignment_action_id = l_locked_action_id
5290: and pa2.payroll_action_id = act.payroll_action_id)
5291:
5292: where pac.payroll_action_id =p_payroll_action_id;
5357: decode(p_org_needed,
5358: 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
5359: null) organization_id,
5360: ppa.effective_date
5361: from pay_assignment_actions paa,
5362: pay_run_results prr,
5363: pay_payroll_actions ppa
5364: where paa.assignment_action_id = revassactid
5365: and ppa.payroll_action_id = paa.payroll_action_id
5366: and paa.assignment_action_id = prr.assignment_action_id;
5367: --
5368: -- cursor and variables used in reversal of pay_run_balances
5369: --
5370: rev_asgact_id pay_assignment_actions.assignment_action_id%type;
5371: rev_act_seq pay_assignment_actions.action_sequence%type;
5372: rev_eff_date pay_payroll_actions.effective_date%type;
5373: --
5374: asgid number; -- assignment_id.
5367: --
5368: -- cursor and variables used in reversal of pay_run_balances
5369: --
5370: rev_asgact_id pay_assignment_actions.assignment_action_id%type;
5371: rev_act_seq pay_assignment_actions.action_sequence%type;
5372: rev_eff_date pay_payroll_actions.effective_date%type;
5373: --
5374: asgid number; -- assignment_id.
5375: taxunt number; -- tax unit id if legislation is US
5379:
5380: found number;
5381: rev_assact number; -- reversal assignment_action_id.
5382: leg_code per_business_groups_perf.legislation_code%type;
5383: l_process_path pay_assignment_actions.process_path%type;
5384: l_found boolean;
5385: l_inp_val_name pay_input_values_f.name%type;
5386: l_rev_lat_bal_maintenance boolean;
5387: l_rule_mode pay_legislation_rules.rule_mode%type;
5418: ac2.run_type_id,
5419: pbg.legislation_code
5420: into asgid, taxunt,run_type_id, leg_code
5421: from pay_action_classifications acl,
5422: pay_assignment_actions ac2,
5423: pay_payroll_actions pa2,
5424: per_business_groups_perf pbg,
5425: pay_payroll_actions pac
5426: where pac.payroll_action_id = pactid
5448: hr_utility.set_location('hrassact.reversal',10);
5449: -- Return the Reversal's assignment_action_id
5450: select act.assignment_action_id
5451: into rev_assact
5452: from pay_assignment_actions act
5453: where act.payroll_action_id = pactid
5454: and act.assignment_id = asgid;
5455: --
5456: select pay_core_utils.get_process_path(assactid)
5456: select pay_core_utils.get_process_path(assactid)
5457: into l_process_path
5458: from dual;
5459: -- need to set process path for reversals
5460: update pay_assignment_actions
5461: set process_path =l_process_path
5462: where assignment_action_id=rev_assact;
5463:
5464: -- insert an interlock row.
5476: assactid
5477: from dual
5478: where not exists (
5479: select null
5480: from pay_assignment_actions ac2,
5481: pay_payroll_actions pa2,
5482: pay_action_interlocks pai
5483: where pai.locked_action_id = assactid
5484: and ac2.assignment_action_id = pai.locking_action_id
5496: insert into pay_action_interlocks (
5497: locking_action_id,
5498: locked_action_id)
5499: select rev_assact, paa1.assignment_action_id
5500: from pay_assignment_actions paa,pay_assignment_actions paa1
5501: where paa.assignment_action_id=assactid
5502: and paa1.payroll_action_id=paa.payroll_action_id
5503: and paa1.source_action_id is null
5504: and paa1.assignment_id=paa.assignment_id
5508: else
5509: -- Return the Reversal's assignment_action_id
5510: select act.assignment_action_id
5511: into rev_assact
5512: from pay_assignment_actions act
5513: where act.payroll_action_id = pactid
5514: and act.assignment_id = asgid;
5515:
5516: --create new interlock if needed
5817: , ppa.effective_date
5818: into rev_asgact_id
5819: , rev_act_seq
5820: , rev_eff_date
5821: from pay_assignment_actions paa
5822: , pay_payroll_actions ppa
5823: where paa.payroll_action_id = pactid
5824: and paa.payroll_action_id = ppa.payroll_action_id;
5825:
5830: , ppa.effective_date
5831: into rev_asgact_id
5832: , rev_act_seq
5833: , rev_eff_date
5834: from pay_assignment_actions paa,
5835: pay_payroll_actions ppa
5836: where paa.assignment_action_id = assactid
5837: and ppa.payroll_action_id = paa.payroll_action_id;
5838:
5875:
5876: SELECT count(*)
5877: INTO hrassact.gv_cnt_reversal_act_id
5878: FROM pay_action_interlocks pai,
5879: pay_assignment_actions paa
5880: WHERE paa.payroll_action_id = pactid
5881: AND paa.assignment_action_id = pai.locking_action_id;
5882: end if;
5883:
5920: select + ORDERED
5921: INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK,
5922: PEL PAY_ELEMENT_LINKS_F_PK,
5923: PET PAY_ELEMENT_TYPES_F_PK,
5924: ACT2 PAY_ASSIGNMENT_ACTIONS_PK)
5925: USE_NL(PAC ACT PAI ACT2 RR2 PEE)
5926: pet.element_type_id,
5927: act.assignment_action_id,
5928: pee.entry_type,
5927: act.assignment_action_id,
5928: pee.entry_type,
5929: pee.element_entry_id
5930: from pay_payroll_actions pac,
5931: pay_assignment_actions act,
5932: pay_action_interlocks pai,
5933: pay_assignment_actions act2,
5934: pay_run_results rr2,
5935: pay_element_entries_f pee,
5929: pee.element_entry_id
5930: from pay_payroll_actions pac,
5931: pay_assignment_actions act,
5932: pay_action_interlocks pai,
5933: pay_assignment_actions act2,
5934: pay_run_results rr2,
5935: pay_element_entries_f pee,
5936: pay_element_links_f pel,
5937: pay_element_types_f pet
5998: select pev.input_value_id,
5999: pay_run_results_s.currval,
6000: null
6001: from pay_payroll_actions pac,
6002: pay_assignment_actions act, -- reversal
6003: pay_element_entry_values_f pev
6004: where act.assignment_action_id = c1rec.assignment_action_id --rev
6005: and act.payroll_action_id = pac.payroll_action_id
6006: and pev.element_entry_id = c1rec.element_entry_id
6037: l_consolidation_set_id number;
6038: --
6039: cursor C_CON1 is
6040: select pay_payroll_actions_s.nextval,
6041: pay_assignment_actions_s.nextval,
6042: pa.CONSOLIDATION_SET_ID,
6043: pa.BUSINESS_GROUP_ID
6044: from PAY_ASSIGNMENT_ACTIONS paa,
6045: PAY_PAYROLL_ACTIONS pa
6040: select pay_payroll_actions_s.nextval,
6041: pay_assignment_actions_s.nextval,
6042: pa.CONSOLIDATION_SET_ID,
6043: pa.BUSINESS_GROUP_ID
6044: from PAY_ASSIGNMENT_ACTIONS paa,
6045: PAY_PAYROLL_ACTIONS pa
6046: where paa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
6047: and paa.PAYROLL_ACTION_ID = pa.PAYROLL_ACTION_ID;
6048: --
6118: update pay_payroll_actions pac
6119: set pac.date_earned = (
6120: select pa2.date_earned
6121: from pay_payroll_actions pa2,
6122: pay_assignment_actions act
6123: where act.assignment_action_id = p_assignment_action_id
6124: and pa2.payroll_action_id = act.payroll_action_id
6125: )
6126: where pac.payroll_action_id = l_payroll_action_id;
6252: ,l_action_type
6253: ,l_effective_date
6254: from
6255: pay_payroll_actions pac
6256: ,pay_assignment_actions aa
6257: where
6258: aa.assignment_action_id = p_assact
6259: and pac.payroll_action_id = aa.payroll_action_id;
6260:
6538: paa.assignment_id
6539: into tax_group,
6540: asg_id
6541: from hr_organization_information hoi,
6542: pay_assignment_actions paa
6543: where UPPER(hoi.org_information_context) = 'FEDERAL TAX RULES'
6544: and hoi.organization_id = paa.tax_unit_id
6545: and paa.assignment_action_id = p_assact
6546: and hoi.org_information5 is not null;
6552: paa.assignment_id
6553: into tax_group,
6554: asg_id
6555: from hr_organization_information hoi,
6556: pay_assignment_actions paa
6557: where UPPER(hoi.org_information_context) = 'CANADA EMPLOYER IDENTIFICATION'
6558: and hoi.organization_id = paa.tax_unit_id
6559: and paa.assignment_action_id = p_assact
6560: and hoi.org_information4 is not null;
6812: hr_utility.set_location(c_indent,15);
6813: end if;
6814: select act.assignment_action_id, act.tax_unit_id
6815: into assactid, tax_unit
6816: from pay_assignment_actions act
6817: where act.payroll_action_id = pactid;
6818: --
6819: --
6820: -- we need to insert the run result row
7889: select pay.payroll_action_id,
7890: pay.effective_date
7891: into l_bal_owner_pay_action,
7892: p_bal_owner_eff_date
7893: from pay_assignment_actions asg
7894: , pay_payroll_actions pay
7895: where asg.assignment_action_id = p_bal_owner_asg_actid
7896: and pay.payroll_action_id = asg.payroll_action_id;
7897: --
7905: select pay.payroll_action_id,
7906: pay.effective_date
7907: into l_payroll_action,
7908: l_effective_date
7909: from pay_assignment_actions asg
7910: , pay_payroll_actions pay
7911: where asg.assignment_action_id = p_ass_action_id
7912: and pay.payroll_action_id = asg.payroll_action_id;
7913: else
8413: l_person_id,
8414: pactid,
8415: tax_unit_id,
8416: bus_grp_id
8417: from pay_assignment_actions paa,
8418: per_all_assignments_f pera
8419: where paa.assignment_action_id = assactid
8420: and pera.assignment_id = paa.assignment_id
8421: and effdate between pera.effective_start_date
8608:
8609: -- determine the master action
8610: SELECT pai.locked_action_id
8611: INTO l_mst_id
8612: FROM pay_action_interlocks pai,pay_assignment_actions paa
8613: WHERE pai.locking_action_id = p_act_id
8614: AND paa.assignment_action_id = pai.locked_action_id
8615: AND paa.source_action_id is null;
8616:
8616:
8617: select 1
8618: into found
8619: from dual
8620: where exists (select 1 from pay_assignment_actions paa
8621: where paa.source_action_id=l_mst_id);
8622:
8623: begin
8624: -- if there is no run for that interlocok cacluate
8625:
8626: -- what the new assignment action should be
8627: select child_asg.assignment_action_id
8628: into new_assactid
8629: from pay_assignment_actions mast_asg,
8630: pay_assignment_actions child_asg,
8631: pay_assignment_actions rev_asg,
8632: pay_action_interlocks pai
8633: where l_mst_id=mast_asg.assignment_action_id
8626: -- what the new assignment action should be
8627: select child_asg.assignment_action_id
8628: into new_assactid
8629: from pay_assignment_actions mast_asg,
8630: pay_assignment_actions child_asg,
8631: pay_assignment_actions rev_asg,
8632: pay_action_interlocks pai
8633: where l_mst_id=mast_asg.assignment_action_id
8634: and child_asg.source_action_id is not null
8627: select child_asg.assignment_action_id
8628: into new_assactid
8629: from pay_assignment_actions mast_asg,
8630: pay_assignment_actions child_asg,
8631: pay_assignment_actions rev_asg,
8632: pay_action_interlocks pai
8633: where l_mst_id=mast_asg.assignment_action_id
8634: and child_asg.source_action_id is not null
8635: and child_asg.assignment_id=mast_asg.assignment_id
8759:
8760: hr_utility.set_location ('hrassact.ins_dummy_quickpay_action', 60);
8761: /*Updating the action status of assignment action to complete*/
8762:
8763: update pay_assignment_actions
8764: set ACTION_STATUS = 'C'
8765: where assignment_action_id = v_assignment_action_id;
8766:
8767: hr_utility.set_location ('hrassact.ins_dummy_quickpay_action', 70);