[Home] [Help]
158: ee_effstart date;
159: -- bug 6655722
160: -- max_effend date;
161: val_date date;
162: asgno per_all_assignments_f.assignment_number%type;
163: link_id number;
164: lookup_type hr_lookups.lookup_type%type;
165: -- Bugfix 2827092
166: --value_set_id pay_input_values_f.value_set_id%type;
208: from pay_element_entries_f pee,
209: pay_element_links_f pel,
210: pay_element_types_f pet,
211: pay_input_values_f piv,
212: per_all_assignments_f asg
213: where asg.assignment_id = p_assignment_id
214: and p_effective_date between
215: asg.effective_start_date and asg.effective_end_date
216: and pel.element_type_id = p_element_type_id
759: pee.effective_start_date
760: into stop_ee_id, link_id, stop_ee_start_date
761: from pay_element_entries_f pee,
762: pay_element_links_f pel,
763: per_all_assignments_f asg
764: where asg.assignment_id = p_assignment_id
765: and p_date_earned between
766: asg.effective_start_date and asg.effective_end_date
767: and pel.element_type_id = p_element_type_id
966: is
967: --
968: cursor dp (p_per_of_serv in number) is
969: select distinct paf.assignment_id
970: from per_all_assignments_f paf
971: where paf.period_of_service_id = p_per_of_serv;
972: --
973: cursor csr_locker (p_asg_id in number,
974: p_eff_date in date)
1021: is
1022: --
1023: cursor dp (p_per_of_serv in number) is
1024: select distinct paf.assignment_id
1025: from per_all_assignments_f paf
1026: where paf.period_of_service_id = p_per_of_serv;
1027: --
1028: cursor csr_locker (p_asg_id in number,
1029: p_eff_date in date)
1074: is
1075: --
1076: cursor dp (p_per_of_serv in number) is
1077: select distinct paf.assignment_id
1078: from per_all_assignments_f paf
1079: where paf.period_of_service_id = p_per_of_serv;
1080: --
1081: cursor csr_locker (p_asg_id in number,
1082: p_eff_date in date)
1264: select act.assignment_id,
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
1274: and pay_asg.assignment_id = act.assignment_id
1275: and ((pac.action_type = ''BEE''
1276: and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
1277: from per_all_assignments_f asg2
1278: where asg2.assignment_id =
1279: pay_asg.assignment_id))
1280: or
1281: (pac.action_type <> ''BEE''
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
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
1300: and act.source_action_id is null
1301: and pay_asg.assignment_id = act.assignment_id
1302: and ((pac.action_type = ''BEE''
1303: and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
1304: from per_all_assignments_f asg2
1305: where asg2.assignment_id =
1306: pay_asg.assignment_id))
1307: or
1308: (pac.action_type <> ''BEE''
1369: -- Use information to build sql statements.
1370: if(everyone) then
1371: sqlstr := '
1372: select pay_asg.assignment_id
1373: from per_all_assignments_f pay_asg,
1374: hr_assignment_sets has
1375: where has.assignment_set_id = :v_asg_set
1376: and pay_asg.payroll_id = has.payroll_id
1377: and fnd_date.canonical_to_date(:v_effective_date) between
1380: --
1381: if(include) then
1382: sqlstr := '
1383: select pay_asg.assignment_id
1384: from per_all_assignments_f pay_asg,
1385: hr_assignment_sets has,
1386: hr_assignment_set_amendments amd
1387: where has.assignment_set_id = :asetid
1388: and amd.assignment_set_id = has.assignment_set_id
1395: --
1396: if(exclude) then
1397: sqlstr := '
1398: select pay_asg.assignment_id
1399: from per_all_assignments_f pay_asg,
1400: hr_assignment_sets has
1401: where has.assignment_set_id = :asetid
1402: and pay_asg.payroll_id = has.payroll_id
1403: and fnd_date.canonical_to_date(:v_effective_date) between
1467: --
1468: sqlstr := '
1469: select pay_asg.assignment_id, pay_asg.assignment_number,
1470: pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1471: from per_all_assignments_f pay_asg,
1472: hr_assignment_sets has,
1473: PAY_ELEMENT_SET_MEMBERS pesm,
1474: pay_element_types_f petf
1475: where pay_asg.business_group_id = :p_bgid
1501: --
1502: sqlstr := '
1503: select pay_asg.assignment_id, pay_asg.assignment_number,
1504: pay_asg.payroll_id,petf.element_type_id, petf.element_name
1505: from per_all_assignments_f pay_asg,
1506: hr_assignment_sets has,
1507: pay_element_types_f petf
1508: where pay_asg.business_group_id = :p_bgid
1509: and has.assignment_set_id = :pasetid
1523: --
1524: sqlstr := '
1525: select pay_asg.assignment_id, pay_asg.assignment_number,
1526: pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1527: from per_all_assignments_f pay_asg,
1528: hr_assignment_sets has,
1529: hr_assignment_set_amendments amd,
1530: PAY_ELEMENT_SET_MEMBERS pesm,
1531: pay_element_types_f petf
1560: --
1561: sqlstr := '
1562: select pay_asg.assignment_id, pay_asg.assignment_number,
1563: pay_asg.payroll_id,petf.element_type_id, petf.element_name
1564: from per_all_assignments_f pay_asg,
1565: hr_assignment_sets has,
1566: hr_assignment_set_amendments amd,
1567: pay_element_types_f petf
1568: where pay_asg.business_group_id = :p_bgid
1585: --
1586: sqlstr := '
1587: select pay_asg.assignment_id, pay_asg.assignment_number,
1588: pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1589: from per_all_assignments_f pay_asg,
1590: hr_assignment_sets has,
1591: PAY_ELEMENT_SET_MEMBERS pesm,
1592: pay_element_types_f petf
1593: where pay_asg.business_group_id = :p_bgid
1624: --
1625: sqlstr := '
1626: select pay_asg.assignment_id, pay_asg.assignment_number,
1627: pay_asg.payroll_id,petf.element_type_id, petf.element_name
1628: from per_all_assignments_f pay_asg,
1629: hr_assignment_sets has,
1630: pay_element_types_f petf
1631: where pay_asg.business_group_id = :p_bgid
1632: and has.assignment_set_id = :pasetid
1660: --
1661: sqlstr := '
1662: select pay_asg.assignment_id, pay_asg.assignment_number,
1663: pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1664: from per_all_assignments_f pay_asg,
1665: PAY_ELEMENT_SET_MEMBERS pesm,
1666: pay_element_types_f petf
1667: where pay_asg.business_group_id = :p_bgid
1668: and pay_asg.assignment_type = ''E''
1691: --
1692: sqlstr := '
1693: select pay_asg.assignment_id, pay_asg.assignment_number,
1694: pay_asg.payroll_id,petf.element_type_id, petf.element_name
1695: from per_all_assignments_f pay_asg,
1696: pay_element_types_f petf
1697: where pay_asg.business_group_id = :p_bgid
1698: and pay_asg.assignment_type = ''E''
1699: and fnd_date.canonical_to_date(:p_effective_date)
1744: if(everyone and (not include)) then
1745: sqlstr := '
1746: select pay_asg.assignment_id,
1747: pay_asg.payroll_id
1748: from per_all_assignments_f pay_asg,
1749: hr_assignment_sets has,
1750: per_quickpaint_invocations inv
1751: where inv.qp_invocation_id = :qp_invocation_id
1752: and has.assignment_set_id = inv.invocation_context
1759: if(include) then
1760: sqlstr := '
1761: select pay_asg.assignment_id,
1762: pay_asg.payroll_id
1763: from per_all_assignments_f pay_asg,
1764: hr_assignment_sets has,
1765: hr_assignment_set_amendments amd,
1766: per_quickpaint_invocations inv
1767: where inv.qp_invocation_id = :qp_invocation_id
1886:
1887: select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
1888: pbg.legislation_code
1889: into l_legislation
1890: from per_all_assignments_f paf,
1891: per_business_groups_perf pbg
1892: where paf.assignment_id = p_assignment_id
1893: and p_effective_date between paf.effective_start_date
1894: and paf.effective_end_date
1928: pbg.legislation_code,
1929: pbg.business_group_id
1930: into l_legislation,
1931: l_business_group_id
1932: from per_all_assignments_f paf,
1933: per_business_groups_perf pbg
1934: where paf.assignment_id = p_assignment_id
1935: and p_effective_date between paf.effective_start_date
1936: and paf.effective_end_date
2010: p_assignment_id number
2011: ,p_effective_date date
2012: ) is
2013: select to_number(SCL.segment1) tax_unit_id
2014: from per_all_assignments_f ASG
2015: ,hr_soft_coding_keyflex SCL
2016: where ASG.assignment_id = p_assignment_id
2017: and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2018: and p_effective_date between ASG.effective_start_date
2025: p_assignment_id number
2026: ,p_effective_date date
2027: ) is
2028: select establishment_id
2029: from per_all_assignments_f ASG
2030: where ASG.assignment_id = p_assignment_id
2031: and p_effective_date between ASG.effective_start_date
2032: and ASG.effective_end_date;
2033: --
2048:
2049: select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
2050: pbg.legislation_code
2051: into l_legislation
2052: from per_all_assignments_f paf,
2053: per_business_groups_perf pbg
2054: where paf.assignment_id = p_assignment_id
2055: and p_effective_date between paf.effective_start_date
2056: and paf.effective_end_date
2099: --
2100: select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
2101: pbg.legislation_code
2102: into l_legislation
2103: from per_all_assignments_f paf,
2104: per_business_groups_perf pbg
2105: where paf.assignment_id = p_assignment_id
2106: and p_effective_date between paf.effective_start_date
2107: and paf.effective_end_date
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
2834: and pay_btl.assignment_id = pay_asg.assignment_id
2835: and pay_btl.effective_date between pay_asg.effective_start_date
2840: -- Note, assignments must be effective
2841: -- at both date paid and date earned.
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
2841: -- at both date paid and date earned.
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
2856: --
2857: -- Reversal range row select
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
2857: -- Reversal range row select
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
2879: --
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
2890: --
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
2901: --
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
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
2919: pay_pac.start_date and pay_pac.effective_date
2938: -- limit of overall statement returned ... SO CARE MUST BE TAKEN
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
2996: -- limit of overall statement returned ... SO CARE MUST BE TAKEN
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
3042: -- limit of overall statement returned ... SO CARE MUST BE TAKEN
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
3089: --
3090: -- Specific inclusions in range creation assignment action phase
3091: rspcinc := '
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
3090: -- Specific inclusions in range creation assignment action phase
3091: rspcinc := '
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
3109: --
3110: -- Specific inclusions in assignment action phase
3111: spcinc := '
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
3110: -- Specific inclusions in assignment action phase
3111: spcinc := '
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)
3128: and pay_as2.period_of_service_id = pay_asg.period_of_service_id';
3129: --
3130: revspcinc := '
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,
3129: --
3130: revspcinc := '
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
3155: --
3156: -- Purge Specific inclusions.
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
3169: --
3170: -- Retropay inclusions
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)
3185: -- RetroCosting inclusions
3186: spcrcsinc := '
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)
3186: spcrcsinc := '
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
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
3208: pay_pac.start_date and pay_pac.effective_date
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
3262: and pay_pa1.start_date between
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
3287: and pay_pa1.effective_date between
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
3311: and pay_pa2.payroll_id = pay_pa1.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
3330: and pay_pa2.effective_date between
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
3348: and pay_pa2.payroll_id = pay_pa1.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
3367: and pay_pa2.effective_date between
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
3543: and pay_asg.payroll_id = pay_pac.payroll_id
3557: where pay_pac.payroll_action_id = :payroll_action_id
3558: and pay_pos.source_type = ''PPF''
3559: and exists (select ''''
3560: from pay_retro_assignments pay_ret_asg,
3561: per_all_assignments_f pay_asg2,
3562: pay_object_groups pay_pog_asg2
3563: where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
3564: and pay_pog_asg2.source_type = ''PAF''
3565: and pay_asg2.assignment_id = pay_pog_asg2.source_id