[Home] [Help]
977: from sys.dual
978: where exists (
979: select null
980: from pay_action_classifications acl,
981: pay_assignment_actions ac2,
982: pay_payroll_actions pa2
983: where ac2.assignment_id = p_asg_id
984: and pa2.payroll_action_id = ac2.payroll_action_id
985: and acl.classification_name = 'SEQUENCED'
1032: from sys.dual
1033: where exists (
1034: select null
1035: from pay_action_classifications acl,
1036: pay_assignment_actions ac2,
1037: pay_payroll_actions pa2
1038: where ac2.assignment_id = p_asg_id
1039: and pa2.payroll_action_id = ac2.payroll_action_id
1040: and acl.classification_name = 'SEQUENCED'
1085: from sys.dual
1086: where exists (
1087: select null
1088: from pay_action_classifications acl,
1089: pay_assignment_actions ac2,
1090: pay_payroll_actions pa2
1091: where ac2.assignment_id = p_asg_id
1092: and pa2.payroll_action_id = ac2.payroll_action_id
1093: and acl.classification_name = 'SEQUENCED'
1137: from sys.dual
1138: where exists (
1139: select null
1140: from pay_action_classifications acl,
1141: pay_assignment_actions ac2,
1142: pay_payroll_actions pa2
1143: where ac2.assignment_id = p_asg_id
1144: and pa2.payroll_action_id = ac2.payroll_action_id
1145: and acl.classification_name = 'SEQUENCED'
1155: from sys.dual
1156: where exists (
1157: select null
1158: from pay_action_classifications acl,
1159: pay_assignment_actions ac2,
1160: pay_payroll_actions pa2
1161: where ac2.assignment_id = p_asg_id
1162: and pa2.payroll_action_id = ac2.payroll_action_id
1163: and acl.classification_name = 'SEQUENCED'
1171: from sys.dual
1172: where exists (
1173: select null
1174: from pay_action_classifications acl,
1175: pay_assignment_actions ac2,
1176: pay_payroll_actions pa2
1177: where ac2.assignment_id = p_asg_id
1178: and pa2.payroll_action_id = ac2.payroll_action_id
1179: and acl.classification_name = 'SEQUENCED'
1266: from hr_assignment_sets has,
1267: pay_population_ranges pop,
1268: per_all_assignments_f pay_asg,
1269: pay_payroll_actions pac,
1270: pay_assignment_actions act
1271: where pac.payroll_action_id = :pactid
1272: and act.payroll_action_id = pac.payroll_action_id
1273: and act.source_action_id is null
1274: and pay_asg.assignment_id = act.assignment_id
1293: from pay_payroll_actions pac,
1294: pay_population_ranges pop,
1295: hr_assignment_set_amendments amd,
1296: per_all_assignments_f pay_asg,
1297: pay_assignment_actions act
1298: where pac.payroll_action_id = :pactid
1299: and act.payroll_action_id = pac.payroll_action_id
1300: and act.source_action_id is null
1301: and pay_asg.assignment_id = act.assignment_id
2734: -- select list for insertion into assignment actions table.
2735: -- Now needs the dummy value for secondary_status.
2736: retpgasactsel := '
2737: select
2738: pay_assignment_actions_s.nextval,
2739: null,
2740: pay_pac.payroll_action_id,
2741: ''U'',
2742: :chunk_number,
2739: null,
2740: pay_pac.payroll_action_id,
2741: ''U'',
2742: :chunk_number,
2743: pay_assignment_actions_s.nextval,
2744: 1,
2745: null,
2746: ''U'',
2747: pay_pos.object_group_id';
2747: pay_pos.object_group_id';
2748: --
2749: retasactsel := '
2750: select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
2751: pay_assignment_actions_s.nextval,
2752: pay_asg.assignment_id,
2753: pay_pac.payroll_action_id,
2754: ''U'',
2755: :chunk_number,
2752: pay_asg.assignment_id,
2753: pay_pac.payroll_action_id,
2754: ''U'',
2755: :chunk_number,
2756: pay_assignment_actions_s.nextval,
2757: 1,
2758: hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2759: pay_pac.effective_date),
2760: ''U'',
2762: --
2763: asactsel := '
2764: select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
2765: INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
2766: pay_assignment_actions_s.nextval,
2767: pay_asg.assignment_id,
2768: pay_pac.payroll_action_id,
2769: ''U'',
2770: :chunk_number,
2767: pay_asg.assignment_id,
2768: pay_pac.payroll_action_id,
2769: ''U'',
2770: :chunk_number,
2771: pay_assignment_actions_s.nextval,
2772: 1,
2773: hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2774: pay_pac.effective_date),
2775: ''U'',
2859: from per_periods_of_service pay_pos,
2860: per_all_assignments_f pay_asg,
2861: per_all_assignments_f pay_as2,
2862: pay_payroll_actions pay_pac,
2863: pay_assignment_actions pay_paa2,
2864: pay_payroll_actions pay_pac2
2865: where pay_pac.payroll_action_id = :payroll_action_id
2866: and pay_asg.payroll_id = pay_pac.payroll_id
2867: and pay_pac.effective_date between
2910: pay_asg.effective_start_date and pay_asg.effective_end_date
2911: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2912: and exists (select null
2913: from pay_action_classifications pay_pcl,
2914: pay_assignment_actions pay_act,
2915: per_all_assignments_f pay_asg2,
2916: pay_payroll_actions pay_pac2
2917: where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
2918: and pay_pac2.effective_date between
2927: pay_asg2.effective_start_date and pay_asg2.effective_end_date
2928: and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
2929: and not exists (
2930: select null
2931: from pay_assignment_actions pay_ac2
2932: where pay_ac2.assignment_id = pay_asg.assignment_id
2933: and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
2934: --
2935: -- Advancepay assignments: criteria : An existance of
3132: per_all_assignments_f pay_asg,
3133: per_all_assignments_f pay_as2,
3134: hr_assignment_set_amendments pay_inc,
3135: pay_payroll_actions pay_pac,
3136: pay_assignment_actions pay_paa2,
3137: pay_payroll_actions pay_pac2
3138: where pay_pac.payroll_action_id = :payroll_action_id
3139: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3140: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3199: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3200: and pay_as2.rowid = pay_asg.rowid
3201: and exists (select null
3202: from pay_action_classifications pay_pcl,
3203: pay_assignment_actions pay_act,
3204: per_all_assignments_f pay_asg2,
3205: pay_payroll_actions pay_pac2
3206: where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
3207: and pay_pac2.effective_date between
3216: pay_asg2.effective_start_date and pay_asg2.effective_end_date
3217: and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
3218: and not exists (
3219: select null
3220: from pay_assignment_actions pay_ac2
3221: where pay_ac2.assignment_id = pay_asg.assignment_id
3222: and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
3223: --
3224: -- Restrict by particular range of person_id.
3253: ecsresact := '
3254: from pay_payroll_actions pay_pa1,
3255: per_time_periods pay_ptp,
3256: pay_payroll_actions pay_pa2,
3257: pay_assignment_actions pay_act,
3258: per_all_assignments_f pay_asg,
3259: per_periods_of_service pay_pos
3260: where pay_pa1.payroll_action_id = :payroll_action_id
3261: and pay_ptp.payroll_id = pay_pa1.payroll_id
3278: from pay_payroll_actions pay_pa1,
3279: pay_all_payrolls_f pay_pay,
3280: per_time_periods pay_ptp,
3281: pay_payroll_actions pay_pa2,
3282: pay_assignment_actions pay_act,
3283: per_all_assignments_f pay_asg,
3284: per_periods_of_service pay_pos
3285: where pay_pa1.payroll_action_id = :payroll_action_id
3286: and pay_pay.consolidation_set_id = pay_pa1.consolidation_set_id
3302: -- i.e. restricted by payroll_id.
3303: resact := '
3304: from pay_payroll_actions pay_pa1,
3305: pay_payroll_actions pay_pa2,
3306: pay_assignment_actions pay_act,
3307: per_all_assignments_f pay_asg,
3308: per_periods_of_service pay_pos
3309: where pay_pa1.payroll_action_id = :payroll_action_id
3310: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3321: -- i.e. not restricted by payroll_id.
3322: nonact := '
3323: from pay_payroll_actions pay_pa1,
3324: pay_payroll_actions pay_pa2,
3325: pay_assignment_actions pay_act,
3326: per_all_assignments_f pay_asg,
3327: per_periods_of_service pay_pos
3328: where pay_pa1.payroll_action_id = :payroll_action_id
3329: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3339: -- i.e. restricted by payroll_id.
3340: pruresact := '
3341: from pay_payroll_actions pay_pa1,
3342: pay_payroll_actions pay_pa2,
3343: pay_assignment_actions pay_act,
3344: per_all_assignments_f pay_asg,
3345: per_periods_of_service pay_pos
3346: where pay_pa1.payroll_action_id = :payroll_action_id
3347: and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3358: -- i.e. not restricted by payroll_id.
3359: prunonact := '
3360: from pay_payroll_actions pay_pa1,
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_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3387: and not exists (
3388: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3389: from pay_action_classifications pay_acl,
3390: pay_payroll_actions pay_pa2,
3391: pay_assignment_actions pay_ac2
3392: where pay_ac2.assignment_id = pay_asg.assignment_id
3393: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3394: and pay_acl.classification_name = ''SEQUENCED''
3395: and pay_pa2.action_type = pay_acl.action_type
3401: and not exists (
3402: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3403: from pay_action_classifications pay_acl,
3404: pay_payroll_actions pay_pa2,
3405: pay_assignment_actions pay_ac2
3406: where pay_ac2.assignment_id = pay_asg.assignment_id
3407: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3408: and pay_acl.classification_name = ''SEQUENCED''
3409: and pay_pa2.action_type = pay_acl.action_type
3477: --
3478: intpur := '
3479: and not exists (
3480: select null
3481: from pay_assignment_actions pay_ac2,
3482: pay_payroll_actions pay_pa2
3483: where pay_ac2.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3484: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3485: and pay_pa2.action_type = ''Z''
3486: and (pay_ac2.secondary_status <> ''C''
3487: or (pay_pa2.effective_date >= pay_pac.effective_date)))
3488: and exists (
3489: select null
3490: from pay_assignment_actions pay_ac4,
3491: pay_payroll_actions pay_pa4
3492: where pay_ac4.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3493: and pay_pa4.payroll_action_id = pay_ac4.payroll_action_id
3494: and pay_pa4.effective_date <= pay_pac.effective_date
3495: and pay_pa4.action_type <> ''Z''
3496: and pay_pa4.effective_date >=
3497: (select nvl(max(pay_pa42.effective_date)
3498: ,hr_general.start_of_time)
3499: from pay_assignment_actions pay_ac42,
3500: pay_payroll_actions pay_pa42
3501: where pay_ac42.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3502: and pay_pa42.payroll_action_id = pay_ac42.payroll_action_id
3503: and pay_pa42.action_type = ''Z''
3522: select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */
3523: null
3524: from pay_action_classifications pay_acl,
3525: pay_payroll_actions pay_pa2,
3526: pay_assignment_actions pay_ac2
3527: where pay_ac2.assignment_id = pay_asg.assignment_id
3528: and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3529: and pay_acl.classification_name = ''SEQUENCED''
3530: and pay_pa2.action_type = pay_acl.action_type
3578: revaa :=
3579: '
3580: and not exists
3581: (select 1
3582: from pay_assignment_actions aa9
3583: where aa9.source_action_id = pay_paa2.assignment_action_id)
3584: /* check havent done reversal before */
3585: and not exists
3586: (select 1
3584: /* check havent done reversal before */
3585: and not exists
3586: (select 1
3587: from pay_action_interlocks int,
3588: pay_assignment_actions aa9,
3589: pay_payroll_actions pay_ppa2
3590: where int.locked_action_id = pay_paa2.assignment_action_id
3591: and aa9.assignment_action_id = int.locking_action_id
3592: and pay_ppa2.payroll_action_id = aa9.payroll_action_id