[Home] [Help]
397: prepayments.
398: 05-SEP-1997 KKAWOL 40.61 #547578 Period dates fix. Set the
399: date_earned column as
400: appropriate on the
401: pay_payroll_actions table.
402: 19-JUN-1997 ALOGUE 40.60 #507602 Reversals run results: source_id
403: = run_result_id of the run
404: result being reversed.
405: 04-APR-1997 NBRISTOW 40.59 #473685 Segment1 was not being decoded
527: Disabled all uses of the
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
622: assignment_number per_all_assignments_f.assignment_number%type,
623: payroll_id per_all_assignments_f.payroll_id%type);
624: --
625: type pact_details is record
626: (pact_id pay_payroll_actions.payroll_action_id%type,
627: action_name hr_lookups.meaning%type,
628: action_type pay_payroll_actions.action_type%type,
629: sequenced_flag boolean,
630: action_date date,
624: --
625: type pact_details is record
626: (pact_id pay_payroll_actions.payroll_action_id%type,
627: action_name hr_lookups.meaning%type,
628: action_type pay_payroll_actions.action_type%type,
629: sequenced_flag boolean,
630: action_date date,
631: current_date date, -- this is sysdate.
632: payroll_name pay_all_payrolls_f.payroll_name%type,
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
995: and ACT.action_sequence > l_action_sequence
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
1016: and ACT.action_sequence > l_action_sequence
1034: where not exists
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
1056: where not exists
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
1123: p_pact_rec.action_type,
1124: p_pact_rec.current_date,
1125: p_pact_rec.payroll_name,
1126: p_pact_rec.bg_name
1127: from pay_payroll_actions pac,
1128: pay_all_payrolls_f pay,
1129: per_business_groups_perf grp,
1130: hr_lookups hrl
1131: where pac.payroll_action_id = p_pact_rec.pact_id
2103: hr_utility.set_location(c_indent,90);
2104: end if;
2105: select ppt_tl.payment_type_name
2106: into l_pact_rec.action_name
2107: from pay_payroll_actions pac,
2108: pay_payment_types_tl ppt_tl,
2109: pay_payment_types ppt
2110: where pac.payroll_action_id = l_pact_rec.pact_id
2111: and ppt.payment_type_id = pac.payment_type_id
2152: 'I', -- information.
2153: pac.business_group_id,
2154: 'B',
2155: mesg_text
2156: from pay_payroll_actions pac
2157: where pac.payroll_action_id = l_pact_rec.pact_id;
2158:
2159: end;
2160: --
2160: --
2161: if g_debug then
2162: hr_utility.set_location(c_indent,80);
2163: end if;
2164: delete from pay_payroll_actions
2165: where payroll_action_id = p_payroll_action_id;
2166: --
2167: if not p_all_or_nothing then
2168: if g_debug then
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
2917: and asg.person_id = p_person_id
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
2943: and plb.defined_balance_id = pdb.defined_balance_id
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
2962: where act.assignment_id = alb.assignment_id
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
2993: and plb.defined_balance_id = pdb.defined_balance_id
3168: cursor cnkasg is
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
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
3222: and ac2.assignment_id = paa.assignment_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
3222: and ac2.assignment_id = paa.assignment_id
3223: and pa2.payroll_action_id = ac2.payroll_action_id
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
3248: where act.assignment_action_id = aaid
3249: and pac.payroll_action_id = act.payroll_action_id
3250: and asg.assignment_id = act.assignment_id
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
3248: where act.assignment_action_id = aaid
3249: and pac.payroll_action_id = act.payroll_action_id
3250: and asg.assignment_id = act.assignment_id
3251: and pac.effective_date between
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,
3289: pay_object_groups pog4,
3290: pay_assignment_actions ac3,
3291: pay_payroll_actions pa3,
3292: pay_action_classifications acl
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
3295: and pog.source_id = act.assignment_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,
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
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
3320: and pog.source_type = 'PAF'
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'
3335: and ac2.source_action_id is null)
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,
3358: pay_assignment_actions ac3,
3359: pay_payroll_actions pa3,
3360: pay_action_classifications acl
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
3363: and pog.object_group_id = act.object_id
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,
3381: pay_payroll_actions pa3
3382: where act.assignment_action_id = aaid
3383: and pac.payroll_action_id = act.payroll_action_id
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
3385: and pog.source_type = 'PPF'
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'
3398: and ac2.source_action_id is null)
3511: --
3512: cursor seqasg (
3513: asgid number,
3514: actseq number) is
3515: select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK) */
3516: ac2.rowid,
3517: ac2.assignment_action_id,
3518: ac2.source_action_id,
3519: ac2.object_type
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'
3526: and pa2.action_type = acl.action_type
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
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,
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
3556: and act.assignment_id = asgid
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,
3593: ac2.object_type
3594: from
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,
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
3609: and act.assignment_id = asgid
3625: and acl.classification_name = 'SEQUENCED'
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,
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
3639: and act.source_action_id is null
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
3658: or
4087: ) return number
4088: is
4089: rmode pay_legislation_rules.rule_mode%type;
4090: posid per_periods_of_service.period_of_service_id%type;
4091: actyp pay_payroll_actions.action_type%type;
4092: bgid pay_payroll_actions.business_group_id%type;
4093: l_action_population_status pay_payroll_actions.action_population_status%type;
4094: aa_id number;
4095: --
4088: is
4089: rmode pay_legislation_rules.rule_mode%type;
4090: posid per_periods_of_service.period_of_service_id%type;
4091: actyp pay_payroll_actions.action_type%type;
4092: bgid pay_payroll_actions.business_group_id%type;
4093: l_action_population_status pay_payroll_actions.action_population_status%type;
4094: aa_id number;
4095: --
4096: --------------------------- irbaact ------------------------------
4089: rmode pay_legislation_rules.rule_mode%type;
4090: posid per_periods_of_service.period_of_service_id%type;
4091: actyp pay_payroll_actions.action_type%type;
4092: bgid pay_payroll_actions.business_group_id%type;
4093: l_action_population_status pay_payroll_actions.action_population_status%type;
4094: aa_id number;
4095: --
4096: --------------------------- irbaact ------------------------------
4097: /*
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
4140: and act.assignment_id = asgid
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
4140: and act.assignment_id = asgid
4141: and ac2.assignment_id = act.assignment_id
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,
4166: per_all_assignments_f asg,
4167: per_all_assignments_f as2,
4168: pay_assignment_actions ac2,
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
4173: and act.assignment_id = asgid
4205: from
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,
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
4220: and act.assignment_id = asgid
4233: and acl.classification_name = 'SEQUENCED'
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,
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
4247: and pog.source_id = act.assignment_id
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
4263: for update of ac2.assignment_action_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
4285: taxunt2 number; -- The tax unit id if legislation is US
4313: into effdate, actype, lgcode, persid
4314: from
4315: per_business_groups_perf pbg,
4316: per_all_assignments_f asg,
4317: pay_payroll_actions pac
4318: where pac.payroll_action_id = pactid
4319: and pbg.business_group_id = asg.business_group_id
4320: and asg.assignment_id = asgid
4321: and pac.effective_date between
4397: select 1
4398: into aa_exists
4399: from dual
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
4399: from dual
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'
4418: select 1
4419: into aa_exists
4420: from sys.dual
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
4420: from sys.dual
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'
4443: select 1
4444: into aa_exists
4445: from sys.dual
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
4445: from sys.dual
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'
4497: l_run_type_id
4498: from sys.dual
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
4498: from sys.dual
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'
4587: pay_assignment_actions_s.nextval
4588: into actyp,
4589: bgid,
4590: aa_id
4591: from pay_payroll_actions pac
4592: where pac.payroll_action_id = pactid;
4593: --
4594: -- Most of the action types require that we look for the legislation
4595: -- rule, i.e. time period dependent (i.e. UK) or time period
4612: USE_NL(asg) */
4613: asg.business_group_id,
4614: asg.period_of_service_id
4615: into bgid, posid
4616: from pay_payroll_actions pac,
4617: per_all_assignments_f asg
4618: where pac.payroll_action_id = pactid
4619: and asg.assignment_id = asgid
4620: and pac.effective_date between
4692: from
4693: per_business_groups_perf pbg,
4694: per_all_assignments_f asg,
4695: per_all_assignments_f as2,
4696: pay_payroll_actions pac
4697: where pac.payroll_action_id = pactid
4698: and asg.payroll_id = pac.payroll_id
4699: and asg.assignment_id = asgid
4700: and pac.effective_date between
4705: and pbg.business_group_id = pac.business_group_id
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'
4741: from per_business_groups_perf pbg,
4742: per_all_assignments_f asg,
4743: per_all_assignments_f as2,
4744: per_periods_of_service pos,
4745: pay_payroll_actions pac
4746: where pac.payroll_action_id = pactid
4747: and asg.payroll_id = pac.payroll_id
4748: and asg.assignment_id = asgid
4749: and pos.period_of_service_id = asg.period_of_service_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
4764: and pa2.payroll_action_id = ac2.payroll_action_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
4829: and pactv.action_type = 'D'
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
4875: where pac.payroll_action_id = pactid
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
4875: where pac.payroll_action_id = pactid
4876: and pa2.payroll_action_id = pac.target_payroll_action_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
4891: and pa3.payroll_action_id = ac2.payroll_action_id
4917:
4918: begin
4919: select pac.action_population_status
4920: into l_action_population_status
4921: from pay_payroll_actions pac
4922: where pac.payroll_action_id = pactid;
4923: exception
4924: when others then
4925: l_action_population_status := 'U';
4925: l_action_population_status := 'U';
4926: end;
4927:
4928: if l_action_population_status <> 'C' then
4929: update pay_payroll_actions pac
4930: set pac.action_population_status = 'C'
4931: where pac.payroll_action_id = pactid;
4932: end if;
4933: --
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;
5014: --
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,
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;
5048: -- appropriate date_earned value.
5049: if g_debug then
5050: hr_utility.set_location('hrassact.qpppassact',50);
5051: end if;
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
5051: end if;
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:
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.
5143: taxunt number; -- tax unit id if legislation is US
5144: run_type_id number;
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
5195: and pbg.business_group_id = pac.business_group_id
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
5195: and pbg.business_group_id = pac.business_group_id
5196: and ac2.assignment_action_id = assactid
5197: and pa2.payroll_action_id = ac2.payroll_action_id
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
5253: and pa2.payroll_action_id = ac2.payroll_action_id
5383: hr_utility.set_location('hrassact.reversal',25);
5384: end if;
5385: select 1
5386: into dummy
5387: from pay_payroll_actions pac,
5388: pay_element_types_f pet
5389: where pac.payroll_action_id = pactid
5390: and pet.element_type_id = etypid
5391: and pac.effective_date between
5520: * to_number(rrv.result_value, mask_38_dec_places)
5521: , mask_38_dec_places), '.'),
5522: 'ND' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_38_dec_places), mask_38_dec_places), '.'),
5523: rrv.result_value)
5524: from pay_payroll_actions pac,
5525: pay_run_result_values rrv,
5526: pay_input_values_f piv
5527: where pac.payroll_action_id = pactid
5528: and rrv.run_result_id = oldrrid
5558: --
5559: -- To signal that processing has been successfully
5560: -- completed, update the action_status to (C)omplete.
5561: hr_utility.set_location('hrassact.reversal',40);
5562: update pay_payroll_actions pac
5563: set pac.action_status = 'C'
5564: where pac.payroll_action_id = pactid;
5565: end;
5566: --
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:
5590: else
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:
5603: end if;
5662: pet.element_type_id,
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,
5733: result_value)
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
5772: l_business_group_id number;
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,
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: --
5785: begin
5799: -- insert the payroll action row
5800: if g_debug then
5801: hr_utility.set_location(c_indent,10);
5802: end if;
5803: insert into PAY_PAYROLL_ACTIONS
5804: (PAYROLL_ACTION_ID,
5805: ACTION_TYPE,
5806: BUSINESS_GROUP_ID,
5807: CONSOLIDATION_SET_ID,
5850: -- the appropriate date earned value.
5851: if g_debug then
5852: hr_utility.set_location(c_indent,40);
5853: end if;
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
5853: end if;
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: )
5987: pay_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;
6448: select /*+ ordered use_nl(pee asg ptp pbg)
6449: index(pee PAY_ELEMENT_ENTRIES_F_PK)
6450: index(asg PER_ASSIGNMENTS_F_PK)
6451: index(ptp PER_TIME_PERIODS_N50) */
6452: pay_payroll_actions_s.nextval,
6453: asg.business_group_id,
6454: pbg.legislation_code,
6455: asg.assignment_id,
6456: asg.payroll_id,
6504: -- insert payroll action row.
6505: if g_debug then
6506: hr_utility.set_location(c_indent,10);
6507: end if;
6508: insert into pay_payroll_actions (
6509: payroll_action_id,
6510: action_type,
6511: business_group_id,
6512: consolidation_set_id,
6529: 'C',
6530: 'C',
6531: effdate,
6532: dtearned,
6533: pay_payroll_actions_s.nextval,
6534: tperiod,
6535: prepay_flag,
6536: creatby,
6537: creatdate,
6862:
6863: bal_dims bal_dims_cache;
6864:
6865: c_indent constant varchar2(30) := 'hrassact.maintain_lat_bal';
6866: pactid pay_payroll_actions.payroll_action_id%TYPE;
6867: asgid per_all_assignments_f.assignment_id%TYPE;
6868: bus_grp_id per_all_assignments_f.business_group_id%TYPE;
6869: l_person_id per_all_assignments_f.person_id%TYPE;
6870: cxt_id number;
7592: ) return date is
7593: l_expiry_date date;
7594: --
7595: c_indent constant varchar2(35) := 'hrassact.get_expiry_date';
7596: l_payroll_action pay_payroll_actions.payroll_action_id%type;
7597: l_effective_date pay_payroll_actions.effective_date%type;
7598: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
7599: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
7600: sql_cursor integer;
7593: l_expiry_date date;
7594: --
7595: c_indent constant varchar2(35) := 'hrassact.get_expiry_date';
7596: l_payroll_action pay_payroll_actions.payroll_action_id%type;
7597: l_effective_date pay_payroll_actions.effective_date%type;
7598: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
7599: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
7600: sql_cursor integer;
7601: l_rows integer;
7594: --
7595: c_indent constant varchar2(35) := 'hrassact.get_expiry_date';
7596: l_payroll_action pay_payroll_actions.payroll_action_id%type;
7597: l_effective_date pay_payroll_actions.effective_date%type;
7598: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
7599: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
7600: sql_cursor integer;
7601: l_rows integer;
7602: --
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: --
7633: -- Get the payroll_action_id and eff date for the adjustment if
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
7649: l_payroll_action := l_bal_owner_pay_action;
7806: l_change_flag boolean;
7807: --
7808: c_indent constant varchar2(35) := 'hrassact.feed_balance';
7809: lat_bal_exp_date date;
7810: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7811: bal_adj_exp_date date;
7812: bal_adj_eff_date pay_payroll_actions.effective_date%type;
7813: prev_bal_exp_date date;
7814: p_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7808: c_indent constant varchar2(35) := 'hrassact.feed_balance';
7809: lat_bal_exp_date date;
7810: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7811: bal_adj_exp_date date;
7812: bal_adj_eff_date pay_payroll_actions.effective_date%type;
7813: prev_bal_exp_date date;
7814: p_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7815: exp_bal_exp_date date;
7816: e_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7810: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7811: bal_adj_exp_date date;
7812: bal_adj_eff_date pay_payroll_actions.effective_date%type;
7813: prev_bal_exp_date date;
7814: p_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7815: exp_bal_exp_date date;
7816: e_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7817: --
7818: begin
7812: bal_adj_eff_date pay_payroll_actions.effective_date%type;
7813: prev_bal_exp_date date;
7814: p_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7815: exp_bal_exp_date date;
7816: e_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7817: --
7818: begin
7819: --
7820: if g_debug then