[Home] [Help]
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'
986: and pa2.action_type = acl.action_type
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'
1041: and pa2.action_type = acl.action_type
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'
1094: and pa2.action_type = acl.action_type
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'
1146: and pa2.action_type = acl.action_type
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'
1164: and pa2.action_type = acl.action_type
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'
1180: and pa2.action_type = acl.action_type
1265: act.assignment_action_id
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
1289: if(include) then
1290: sqlstr := '
1291: select act.assignment_id,
1292: act.assignment_action_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
1816:
1817: select range_code
1818: into range_proc
1819: from pay_report_format_mappings_f prfm,
1820: pay_payroll_actions ppa
1821: where ppa.payroll_action_id = pactid
1822: and ppa.report_type = prfm.report_type
1823: and ppa.report_qualifier = prfm.report_qualifier
1824: and ppa.report_category = prfm.report_category
2514: -- Processed - confirming its NOT formula-based.
2515: --
2516: select pac.assignment_set_id
2517: into l_asg_set_id
2518: from pay_payroll_actions pac,
2519: hr_assignment_sets has
2520: where pac.payroll_action_id = pactid
2521: and has.assignment_set_id = pac.assignment_set_id
2522: and has.formula_id is null;
2825: ''U'',
2826: null';
2827: --
2828: beeasg := '
2829: from pay_payroll_actions pay_pac,
2830: pay_batch_lines pay_btl,
2831: per_all_assignments_f pay_asg
2832: where pay_pac.payroll_action_id = :payroll_action_id
2833: and pay_pac.batch_id = pay_btl.batch_id
2842: allasg := '
2843: from per_periods_of_service pay_pos,
2844: per_all_assignments_f pay_asg,
2845: per_all_assignments_f pay_as2,
2846: pay_payroll_actions pay_pac
2847: where pay_pac.payroll_action_id = :payroll_action_id
2848: and pay_asg.payroll_id = pay_pac.payroll_id
2849: and pay_pac.effective_date between
2850: pay_asg.effective_start_date and pay_asg.effective_end_date
2858: revallasg := '
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
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
2868: pay_asg.effective_start_date and pay_asg.effective_end_date
2880: -- Purge range row select.
2881: purallasg := '
2882: from per_periods_of_service pay_pos,
2883: per_all_assignments_f pay_asg,
2884: pay_payroll_actions pay_pac
2885: where pay_pac.payroll_action_id = :pactid
2886: and pay_asg.business_group_id + 0 = pay_pac.business_group_id
2887: and pay_asg.payroll_id is not null
2888: and pay_asg.effective_start_date <= pay_pac.effective_date
2891: -- Retropay assignments
2892: allretasg := '
2893: from per_periods_of_service pay_pos,
2894: per_all_assignments_f pay_asg,
2895: pay_payroll_actions pay_pac
2896: where pay_pac.payroll_action_id = :payroll_action_id
2897: and pay_asg.payroll_id = pay_pac.payroll_id
2898: and pay_pac.effective_date between
2899: pay_asg.effective_start_date and pay_asg.effective_end_date
2902: -- RetroCost assignments
2903: allrcsasg := '
2904: from per_periods_of_service pay_pos,
2905: per_all_assignments_f pay_asg,
2906: pay_payroll_actions pay_pac
2907: where pay_pac.payroll_action_id = :pactid
2908: and (pay_asg.payroll_id = pay_pac.payroll_id or pay_pac.payroll_id is null)
2909: and pay_pac.effective_date between
2910: pay_asg.effective_start_date and pay_asg.effective_end_date
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
2919: pay_pac.start_date and pay_pac.effective_date
2920: and pay_act.payroll_action_id = pay_pac2.payroll_action_id
2939: -- IN ANY CHANGES BEING MADE.
2940: alladvasg := '
2941: from per_periods_of_service pay_pos,
2942: per_all_assignments_f pay_asg,
2943: pay_payroll_actions pay_pac
2944: where pay_pac.payroll_action_id = :payroll_action_id
2945: and pay_asg.payroll_id + 0 = pay_pac.payroll_id
2946: and pay_pac.effective_date between
2947: pay_asg.effective_start_date and pay_asg.effective_end_date
2997: -- IN ANY CHANGES BEING MADE.
2998: alladeasg := '
2999: from per_periods_of_service pay_pos,
3000: per_all_assignments_f pay_asg,
3001: pay_payroll_actions pay_pac
3002: where pay_pac.payroll_action_id = :pactid
3003: and pay_asg.payroll_id = pay_pac.payroll_id
3004: and pay_pac.effective_date between
3005: pay_asg.effective_start_date and pay_asg.effective_end_date
3043: -- IN ANY CHANGES BEING MADE.
3044: adeincspc := '
3045: from per_periods_of_service pay_pos,
3046: per_all_assignments_f pay_asg,
3047: pay_payroll_actions pay_pac,
3048: hr_assignment_set_amendments pay_inc
3049: where pay_pac.payroll_action_id = :pactid
3050: and pay_asg.payroll_id = pay_pac.payroll_id
3051: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3092: from per_periods_of_service pay_pos,
3093: per_all_assignments_f pay_asg,
3094: per_all_assignments_f pay_as2,
3095: hr_assignment_set_amendments pay_inc,
3096: pay_payroll_actions pay_pac
3097: where pay_pac.payroll_action_id = :payroll_action_id
3098: and pay_pac.assignment_set_id = pay_inc.assignment_set_id
3099: and pay_asg.assignment_id = pay_inc.assignment_id
3100: and pay_asg.payroll_id = pay_pac.payroll_id
3112: from per_periods_of_service pay_pos,
3113: per_all_assignments_f pay_asg,
3114: per_all_assignments_f pay_as2,
3115: hr_assignment_set_amendments pay_inc,
3116: pay_payroll_actions pay_pac
3117: where pay_pac.payroll_action_id = :payroll_action_id
3118: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3119: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3120: and pay_asg.payroll_id = pay_pac.payroll_id
3131: from per_periods_of_service pay_pos,
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)
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)
3141: and pay_asg.payroll_id = pay_pac.payroll_id
3157: purspcinc := '
3158: from per_periods_of_service pay_pos,
3159: per_all_assignments_f pay_asg,
3160: hr_assignment_set_amendments pay_inc,
3161: pay_payroll_actions pay_pac
3162: where pay_pac.payroll_action_id = :payroll_action_id
3163: and pay_inc.assignment_set_id = pay_pac.assignment_set_id
3164: and pay_inc.assignment_id = pay_asg.assignment_id
3165: and pay_asg.payroll_id is not null
3171: spcretinc := '
3172: from per_periods_of_service pay_pos,
3173: per_all_assignments_f pay_asg,
3174: hr_assignment_set_amendments pay_inc,
3175: pay_payroll_actions pay_pac
3176: where pay_pac.payroll_action_id = :payroll_action_id
3177: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3178: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3179: and pay_asg.payroll_id + 0 = pay_pac.payroll_id
3187: from per_periods_of_service pay_pos,
3188: hr_assignment_set_amendments pay_inc,
3189: per_all_assignments_f pay_asg,
3190: per_all_assignments_f pay_as2,
3191: pay_payroll_actions pay_pac
3192: where pay_pac.payroll_action_id = :pactid
3193: and (pay_asg.payroll_id = pay_pac.payroll_id or pay_pac.payroll_id is null)
3194: and pay_pac.effective_date between
3195: pay_asg.effective_start_date and pay_asg.effective_end_date
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
3208: pay_pac.start_date and pay_pac.effective_date
3209: and pay_act.payroll_action_id = pay_pac2.payroll_action_id
3250: -- Estimate Costing Restricted payroll action range row
3251: -- where clause. i.e. restricted by payroll_id.
3252: -- nb have to join to per_time_periods at pa.start_date
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,
3252: -- nb have to join to per_time_periods at pa.start_date
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
3274: -- Estimate Costing Unrestricted payroll action range row
3275: -- where clause. i.e. not restricted by payroll_id.
3276: -- nb have to join to per_time_periods at pa.start_date
3277: ecsnonact := '
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,
3277: ecsnonact := '
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
3300: --
3301: -- Restricted payroll action range row where clause.
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
3301: -- Restricted payroll action range row where clause.
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
3319: --
3320: -- Unrestricted payroll action range row where clause.
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
3320: -- Unrestricted payroll action range row where clause.
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
3337: --
3338: -- Restricted payroll action range row where clause.
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
3338: -- Restricted payroll action range row where clause.
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
3356: --
3357: -- Unrestricted payroll action range row where clause.
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
3357: -- Unrestricted payroll action range row where clause.
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
3384: -- and not exists clause to exclude people failing interlock rules.
3385: -- this one is for time independent legislation.
3386: intind := '
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
3386: intind := '
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''
3398: and pay_pa2.effective_date <= pay_pac.effective_date)))';
3399: --
3400: intbal := '
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
3400: intbal := '
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''
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''
3504: and pay_ac42.secondary_status = ''C''
3518: --
3519: -- Retropay sequence
3520: intretind := '
3521: and not exists (
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
3521: and not exists (
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''
3534: and pay_pa2.effective_date <= pay_pac.effective_date)))';
3535: --
3536: -- Retropay by Element assignments with retro definition
3537: retdefasg := '
3538: from pay_payroll_actions pay_pac,
3539: per_all_assignments_f pay_asg,
3540: pay_retro_assignments pay_ret_asg,
3541: per_periods_of_service pay_pos
3542: where pay_pac.payroll_action_id = :payroll_action_id
3551: pay_asg.effective_start_date and pay_asg.effective_end_date';
3552: --
3553: retdefasgpg := '
3554: from
3555: pay_payroll_actions pay_pac,
3556: pay_object_groups pay_pos
3557: where pay_pac.payroll_action_id = :payroll_action_id
3558: and pay_pos.source_type = ''PPF''
3559: and exists (select ''''
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
3593: and pay_ppa2.action_type = ''V'')';
3593: and pay_ppa2.action_type = ''V'')';
3594:
3595: orgfrom := '
3596: from
3597: pay_payroll_actions pay_pac,
3598: hr_organization_units hou,
3599: hr_organization_information hoi
3600: where pay_pac.payroll_action_id = pay_proc_environment_pkg.get_pactid()
3601: and hou.business_group_id = pay_pac.business_group_id