[Home] [Help]
980: is
981: select 1 res
982: from sys.dual
983: where exists (
984: select /*+ index(AC2 PAY_ASSIGNMENT_ACTIONS_N51) */ null
985: from pay_action_classifications acl,
986: pay_assignment_actions ac2,
987: pay_payroll_actions pa2
988: where ac2.assignment_id = p_asg_id
982: from sys.dual
983: where exists (
984: select /*+ index(AC2 PAY_ASSIGNMENT_ACTIONS_N51) */ null
985: from pay_action_classifications acl,
986: pay_assignment_actions ac2,
987: pay_payroll_actions pa2
988: where ac2.assignment_id = p_asg_id
989: and pa2.payroll_action_id = ac2.payroll_action_id
990: and acl.classification_name = 'SEQUENCED'
1037: from sys.dual
1038: where exists (
1039: select null
1040: from pay_action_classifications acl,
1041: pay_assignment_actions ac2,
1042: pay_payroll_actions pa2
1043: where ac2.assignment_id = p_asg_id
1044: and pa2.payroll_action_id = ac2.payroll_action_id
1045: and acl.classification_name = 'SEQUENCED'
1090: from sys.dual
1091: where exists (
1092: select null
1093: from pay_action_classifications acl,
1094: pay_assignment_actions ac2,
1095: pay_payroll_actions pa2
1096: where ac2.assignment_id = p_asg_id
1097: and pa2.payroll_action_id = ac2.payroll_action_id
1098: and acl.classification_name = 'SEQUENCED'
1142: from sys.dual
1143: where exists (
1144: select null
1145: from pay_action_classifications acl,
1146: pay_assignment_actions ac2,
1147: pay_payroll_actions pa2
1148: where ac2.assignment_id = p_asg_id
1149: and pa2.payroll_action_id = ac2.payroll_action_id
1150: and acl.classification_name = 'SEQUENCED'
1160: from sys.dual
1161: where exists (
1162: select null
1163: from pay_action_classifications acl,
1164: pay_assignment_actions ac2,
1165: pay_payroll_actions pa2
1166: where ac2.assignment_id = p_asg_id
1167: and pa2.payroll_action_id = ac2.payroll_action_id
1168: and acl.classification_name = 'SEQUENCED'
1176: from sys.dual
1177: where exists (
1178: select null
1179: from pay_action_classifications acl,
1180: pay_assignment_actions ac2,
1181: pay_payroll_actions pa2
1182: where ac2.assignment_id = p_asg_id
1183: and pa2.payroll_action_id = ac2.payroll_action_id
1184: and acl.classification_name = 'SEQUENCED'
1276: index(pac PAY_PAYROLL_ACTIONS_PK)
1277: rowid(pop)
1278: index(has HR_ASSIGNMENT_SETS_PK)
1279: index(pay_asg PER_ASSIGNMENTS_F_N12)
1280: index(act PAY_ASSIGNMENT_ACTIONS_N51) */ ';
1281: else
1282: l_hints := null;
1283: end if;
1284:
1289: from hr_assignment_sets has,
1290: pay_population_ranges pop,
1291: per_all_assignments_f pay_asg,
1292: pay_payroll_actions pac,
1293: pay_assignment_actions act
1294: where pac.payroll_action_id = :pactid
1295: and act.payroll_action_id = pac.payroll_action_id
1296: and act.source_action_id is null
1297: and pay_asg.assignment_id = act.assignment_id
1315: l_hints := '/*+ use_nl(pac pop amd act pay_asg)
1316: index(pac PAY_PAYROLL_ACTIONS_PK)
1317: rowid(pop)
1318: index(amd HR_ASSIGNMENT_SET_AMENDMEN_FK2)
1319: index(act PAY_ASSIGNMENT_ACTIONS_N51)
1320: index(pay_asg PER_ASSIGNMENTS_F_PK) */ ';
1321: else
1322: l_hints := null;
1323: end if;
1328: from pay_payroll_actions pac,
1329: pay_population_ranges pop,
1330: hr_assignment_set_amendments amd,
1331: per_all_assignments_f pay_asg,
1332: pay_assignment_actions act
1333: where pac.payroll_action_id = :pactid
1334: and act.payroll_action_id = pac.payroll_action_id
1335: and act.source_action_id is null
1336: and pay_asg.assignment_id = act.assignment_id
2794: -- select list for insertion into assignment actions table.
2795: -- Now needs the dummy value for secondary_status.
2796: retpgasactsel := '
2797: select
2798: pay_assignment_actions_s.nextval,
2799: null,
2800: pay_pac.payroll_action_id,
2801: ''U'',
2802: :chunk_number,
2799: null,
2800: pay_pac.payroll_action_id,
2801: ''U'',
2802: :chunk_number,
2803: pay_assignment_actions_s.nextval,
2804: 1,
2805: null,
2806: ''U'',
2807: pay_pos.object_group_id';
2807: pay_pos.object_group_id';
2808: --
2809: retasactsel := '
2810: select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
2811: pay_assignment_actions_s.nextval,
2812: pay_asg.assignment_id,
2813: pay_pac.payroll_action_id,
2814: ''U'',
2815: :chunk_number,
2812: pay_asg.assignment_id,
2813: pay_pac.payroll_action_id,
2814: ''U'',
2815: :chunk_number,
2816: pay_assignment_actions_s.nextval,
2817: 1,
2818: hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2819: pay_pac.effective_date),
2820: ''U'',
2822: --
2823: asactsel := '
2824: select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
2825: INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
2826: pay_assignment_actions_s.nextval,
2827: pay_asg.assignment_id,
2828: pay_pac.payroll_action_id,
2829: ''U'',
2830: :chunk_number,
2827: pay_asg.assignment_id,
2828: pay_pac.payroll_action_id,
2829: ''U'',
2830: :chunk_number,
2831: pay_assignment_actions_s.nextval,
2832: 1,
2833: hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2834: pay_pac.effective_date),
2835: ''U'',
2937: from per_periods_of_service pay_pos,
2938: per_all_assignments_f pay_asg,
2939: per_all_assignments_f pay_as2,
2940: pay_payroll_actions pay_pac,
2941: pay_assignment_actions pay_paa2,
2942: pay_payroll_actions pay_pac2
2943: where pay_pac.payroll_action_id = :payroll_action_id
2944: and pay_asg.payroll_id = pay_pac.payroll_id
2945: and pay_pac.effective_date between
2988: pay_asg.effective_start_date and pay_asg.effective_end_date
2989: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2990: and exists (select null
2991: from pay_action_classifications pay_pcl,
2992: pay_assignment_actions pay_act,
2993: per_all_assignments_f pay_asg2,
2994: pay_payroll_actions pay_pac2
2995: where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
2996: and pay_pac2.effective_date between
3005: pay_asg2.effective_start_date and pay_asg2.effective_end_date
3006: and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
3007: and not exists (
3008: select null
3009: from pay_assignment_actions pay_ac2
3010: where pay_ac2.assignment_id = pay_asg.assignment_id
3011: and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
3012: --
3013: -- Advancepay assignments: criteria : An existance of
3212: per_all_assignments_f pay_asg,
3213: per_all_assignments_f pay_as2,
3214: hr_assignment_set_amendments pay_inc,
3215: pay_payroll_actions pay_pac,
3216: pay_assignment_actions pay_paa2,
3217: pay_payroll_actions pay_pac2
3218: where pay_pac.payroll_action_id = :payroll_action_id
3219: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3220: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3279: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3280: and pay_as2.rowid = pay_asg.rowid
3281: and exists (select null
3282: from pay_action_classifications pay_pcl,
3283: pay_assignment_actions pay_act,
3284: per_all_assignments_f pay_asg2,
3285: pay_payroll_actions pay_pac2
3286: where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
3287: and pay_pac2.effective_date between
3296: pay_asg2.effective_start_date and pay_asg2.effective_end_date
3297: and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
3298: and not exists (
3299: select null
3300: from pay_assignment_actions pay_ac2
3301: where pay_ac2.assignment_id = pay_asg.assignment_id
3302: and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
3303: --
3304: -- Restrict by particular range of person_id.
3333: ecsresact := '
3334: from pay_payroll_actions pay_pa1,
3335: per_time_periods pay_ptp,
3336: pay_payroll_actions pay_pa2,
3337: pay_assignment_actions pay_act,
3338: per_all_assignments_f pay_asg,
3339: per_periods_of_service pay_pos
3340: where pay_pa1.payroll_action_id = :payroll_action_id
3341: and pay_ptp.payroll_id = pay_pa1.payroll_id
3358: from pay_payroll_actions pay_pa1,
3359: pay_all_payrolls_f pay_pay,
3360: per_time_periods pay_ptp,
3361: pay_payroll_actions pay_pa2,
3362: pay_assignment_actions pay_act,
3363: per_all_assignments_f pay_asg,
3364: per_periods_of_service pay_pos
3365: where pay_pa1.payroll_action_id = :payroll_action_id
3366: and pay_pay.consolidation_set_id = pay_pa1.consolidation_set_id
3382: -- i.e. restricted by payroll_id.
3383: resact := '
3384: from pay_payroll_actions pay_pa1,
3385: pay_payroll_actions pay_pa2,
3386: pay_assignment_actions pay_act,
3387: per_all_assignments_f pay_asg,
3388: per_periods_of_service pay_pos
3389: where pay_pa1.payroll_action_id = :payroll_action_id
3390: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3401: -- i.e. not restricted by payroll_id.
3402: nonact := '
3403: from pay_payroll_actions pay_pa1,
3404: pay_payroll_actions pay_pa2,
3405: pay_assignment_actions pay_act,
3406: per_all_assignments_f pay_asg,
3407: per_periods_of_service pay_pos
3408: where pay_pa1.payroll_action_id = :payroll_action_id
3409: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3419: -- i.e. restricted by payroll_id.
3420: pruresact := '
3421: from pay_payroll_actions pay_pa1,
3422: pay_payroll_actions pay_pa2,
3423: pay_assignment_actions pay_act,
3424: per_all_assignments_f pay_asg,
3425: per_periods_of_service pay_pos
3426: where pay_pa1.payroll_action_id = :payroll_action_id
3427: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3438: -- i.e. not restricted by payroll_id.
3439: prunonact := '
3440: from pay_payroll_actions pay_pa1,
3441: pay_payroll_actions pay_pa2,
3442: pay_assignment_actions pay_act,
3443: per_all_assignments_f pay_asg,
3444: per_periods_of_service pay_pos
3445: where pay_pa1.payroll_action_id = :payroll_action_id
3446: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3467: and not exists (
3468: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3469: from pay_action_classifications pay_acl,
3470: pay_payroll_actions pay_pa2,
3471: pay_assignment_actions pay_ac2
3472: where pay_ac2.assignment_id = pay_asg.assignment_id
3473: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3474: and pay_acl.classification_name = ''SEQUENCED''
3475: and pay_pa2.action_type = pay_acl.action_type
3481: and not exists (
3482: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3483: from pay_action_classifications pay_acl,
3484: pay_payroll_actions pay_pa2,
3485: pay_assignment_actions pay_ac2
3486: where pay_ac2.assignment_id = pay_asg.assignment_id
3487: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3488: and pay_acl.classification_name = ''SEQUENCED''
3489: and pay_pa2.action_type = pay_acl.action_type
3555: --
3556: intpur := '
3557: and not exists (
3558: select null
3559: from pay_assignment_actions pay_ac2,
3560: pay_payroll_actions pay_pa2
3561: where pay_ac2.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3562: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3563: and pay_pa2.action_type = ''Z''
3564: and (pay_ac2.secondary_status <> ''C''
3565: or (pay_pa2.effective_date >= pay_pac.effective_date)))
3566: and exists (
3567: select null
3568: from pay_assignment_actions pay_ac4,
3569: pay_payroll_actions pay_pa4
3570: where pay_ac4.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3571: and pay_pa4.payroll_action_id = pay_ac4.payroll_action_id
3572: and pay_pa4.effective_date <= pay_pac.effective_date
3573: and pay_pa4.action_type <> ''Z''
3574: and pay_pa4.effective_date >=
3575: (select nvl(max(pay_pa42.effective_date)
3576: ,hr_general.start_of_time)
3577: from pay_assignment_actions pay_ac42,
3578: pay_payroll_actions pay_pa42
3579: where pay_ac42.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3580: and pay_pa42.payroll_action_id = pay_ac42.payroll_action_id
3581: and pay_pa42.action_type = ''Z''
3600: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */
3601: null
3602: from pay_action_classifications pay_acl,
3603: pay_payroll_actions pay_pa2,
3604: pay_assignment_actions pay_ac2
3605: where pay_ac2.assignment_id = pay_asg.assignment_id
3606: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3607: and pay_acl.classification_name = ''SEQUENCED''
3608: and pay_pa2.action_type = pay_acl.action_type
3697: revaa :=
3698: '
3699: and not exists
3700: (select 1
3701: from pay_assignment_actions aa9
3702: where aa9.source_action_id = pay_paa2.assignment_action_id)
3703: /* check havent done reversal before */
3704: and not exists
3705: (select 1
3703: /* check havent done reversal before */
3704: and not exists
3705: (select 1
3706: from pay_action_interlocks int,
3707: pay_assignment_actions aa9,
3708: pay_payroll_actions pay_ppa2
3709: where int.locked_action_id = pay_paa2.assignment_action_id
3710: and aa9.assignment_action_id = int.locking_action_id
3711: and pay_ppa2.payroll_action_id = aa9.payroll_action_id