[Home] [Help]
407: function periods_since_hire_date
408: (
409: p_payroll_id in number,
410: p_assignment_id in per_all_assignments_f.assignment_id%type,
411: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
412: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
413: p_period_num in number,
414: p_period_start in date,
415: p_emp_hire_date in date,
408: (
409: p_payroll_id in number,
410: p_assignment_id in per_all_assignments_f.assignment_id%type,
411: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
412: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
413: p_period_num in number,
414: p_period_start in date,
415: p_emp_hire_date in date,
416: p_use_tax_flag IN VARCHAR2 --2610141
506: IS
507: select ppa.date_earned,
508: ppa.effective_date
509: from pay_payroll_actions ppa,
510: pay_assignment_actions paa
511: where ppa.payroll_action_id = paa.payroll_action_id
512: and paa.assignment_action_id = p_assignment_action_id;
513:
514:
700:
701:
702: -- Variable to store the maximum previous year assignment action id and its corresponding
703: -- tax_unit_id (legal Employer).
704: l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
705: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
706:
707:
708: -- Total Earnings variable
701:
702: -- Variable to store the maximum previous year assignment action id and its corresponding
703: -- tax_unit_id (legal Employer).
704: l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
705: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
706:
707:
708: -- Total Earnings variable
709: --
727: c_fin_start_date in date,
728: c_fin_end_date in date)
729: IS
730: SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
731: FROM pay_assignment_actions paa
732: ,pay_payroll_actions ppa
733: WHERE paa.assignment_id = c_assignment_id
734: and ppa.payroll_action_id = paa.payroll_action_id
735: and ppa.business_group_id = c_business_group_id
735: and ppa.business_group_id = c_business_group_id
736: AND paa.action_sequence in
737: (
738: SELECT MAX(paa.action_sequence)
739: FROM pay_assignment_actions paa,
740: pay_payroll_actions ppa,
741: per_all_assignments_f paaf
742: WHERE ppa.business_group_id = c_business_group_id
743: AND paaf.assignment_id = c_assignment_id
757: c_fin_start_date in date,
758: c_fin_end_date in date)
759: IS
760: SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
761: FROM pay_assignment_actions paa
762: ,pay_payroll_actions ppa
763: WHERE paa.assignment_id = c_assignment_id
764: and ppa.payroll_action_id = paa.payroll_action_id
765: and ppa.business_group_id = c_business_group_id
765: and ppa.business_group_id = c_business_group_id
766: AND paa.action_sequence in
767: (
768: SELECT MAX(paa.action_sequence)
769: FROM pay_assignment_actions paa,
770: pay_payroll_actions ppa,
771: per_all_assignments_f paaf
772: WHERE ppa.business_group_id = c_business_group_id
773: AND paaf.assignment_id = c_assignment_id
867: CURSOR c_get_payroll_effective_date
868: IS
869: SELECT ppa.effective_date
870: FROM pay_payroll_actions ppa,
871: pay_assignment_actions paa
872: WHERE paa.assignment_action_id = p_assignment_action_id
873: AND ppa.payroll_action_id = paa.payroll_action_id;
874:
875: /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
879: c_start_date in date,
880: c_end_date in date)
881: IS
882: SELECT count(paa.assignment_action_id)
883: FROM pay_assignment_actions paa,
884: pay_payroll_actions ppa,
885: per_assignments_f paf
886: WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
887: AND ppa.business_group_id = c_business_group_id
1239: from per_time_periods ptp
1240: where exists (select 'EXISTS' from
1241: per_assignments_f paf,
1242: pay_payroll_actions ppa,
1243: pay_assignment_actions paa
1244: where ppa.payroll_id = v_payroll_id
1245: and ppa.action_type in ('R','Q')
1246: and paa.action_status = 'C'
1247: and ppa.payroll_action_id = paa.payroll_action_id
1261: CURSOR c_get_payroll_effective_date
1262: IS
1263: SELECT ppa.effective_date
1264: FROM pay_payroll_actions ppa,
1265: pay_assignment_actions paa
1266: WHERE paa.assignment_action_id = p_assignment_action_id
1267: AND ppa.payroll_action_id = paa.payroll_action_id;
1268:
1269:
1473: function get_retro_period
1474: (
1475: p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1476: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1477: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,/*Bug 4418107*/
1478: p_retro_start_date out NOCOPY date,
1479: p_retro_end_date out NOCOPY date
1480: )return number is
1481:
1486: ) is
1487: SELECT ptp.start_date,ptp.end_date
1488: FROM per_time_periods ptp,
1489: pay_payroll_actions ppa,
1490: pay_assignment_actions paa,
1491: pay_run_results prr,
1492: pay_element_entries_f pee
1493: WHERE pee.element_entry_id=c_element_entry_id
1494: and prr.run_result_id = pee.source_id
1508: ) is
1509: SELECT ptp.start_date,ptp.end_date
1510: FROM per_time_periods ptp,
1511: pay_payroll_actions ppa,
1512: pay_assignment_actions paa,
1513: pay_element_entries_f pee
1514: WHERE pee.element_entry_id=c_element_entry_id
1515: and paa.assignment_action_id=pee.source_asg_action_id
1516: AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1596: FUNCTION get_enhanced_retro_period
1597: (
1598: p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1599: p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1600: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1601: p_retro_start_date OUT NOCOPY date,
1602: p_retro_end_date OUT NOCOPY date,
1603: p_orig_effective_date OUT NOCOPY date,
1604: p_retro_effective_date OUT NOCOPY date,
1614: IS
1615: SELECT pee.creator_type,
1616: ppa.effective_date
1617: FROM pay_payroll_actions ppa,
1618: pay_assignment_actions paa,
1619: pay_element_entries_f pee
1620: WHERE ppa.payroll_action_id = paa.payroll_action_id
1621: AND paa.assignment_action_id = pee.creator_id
1622: AND pee.element_entry_id = c_element_entry_id
1631: ,ptp.end_date
1632: ,ppa.effective_date
1633: FROM per_time_periods ptp,
1634: pay_payroll_actions ppa,
1635: pay_assignment_actions paa,
1636: pay_run_results prr,
1637: pay_element_entries_f pee
1638: WHERE pee.element_entry_id=c_element_entry_id
1639: AND prr.run_result_id = pee.source_id
1655: ,ptp.end_date
1656: ,ppa.effective_date
1657: FROM per_time_periods ptp,
1658: pay_payroll_actions ppa,
1659: pay_assignment_actions paa,
1660: pay_element_entries_f pee
1661: WHERE pee.element_entry_id=c_element_entry_id
1662: AND paa.assignment_action_id=pee.source_asg_action_id
1663: AND paa.tax_unit_id = p_tax_unit_id
1826: FUNCTION get_retro_time_span
1827: (
1828: p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1829: p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1830: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1831: p_retro_start_date OUT NOCOPY date,
1832: p_retro_end_date OUT NOCOPY date,
1833: p_orig_effective_date OUT NOCOPY date,
1834: p_retro_effective_date OUT NOCOPY date,
1844: SELECT pee.element_entry_id
1845: ,ppa.retro_definition_id
1846: ,pepd.retro_component_id
1847: FROM pay_element_entries_f pee
1848: ,pay_assignment_actions paa
1849: ,pay_payroll_actions ppa
1850: ,pay_entry_process_details pepd
1851: WHERE pee.element_entry_id = c_element_entry_id
1852: AND pee.element_entry_id = pepd.element_entry_id
1955: END get_retro_time_span;
1956:
1957: function count_retro_periods
1958: (
1959: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1960: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1961: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1962: p_use_tax_flag IN VARCHAR2, --4415795
1963: p_mode IN VARCHAR2 DEFAULT 'E' --7665727
1957: function count_retro_periods
1958: (
1959: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1960: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1961: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1962: p_use_tax_flag IN VARCHAR2, --4415795
1963: p_mode IN VARCHAR2 DEFAULT 'E' --7665727
1964: )return number
1965: is
1968: /*
1969: cursor get_element_entries
1970: is
1971: select pee.element_entry_id from pay_element_entries_f pee,
1972: pay_assignment_actions paa
1973: where paa.assignment_action_id=p_assignment_action_id
1974: and pee.assignment_id=paa.assignment_id
1975: and pee.creator_type IN ('EE','RR')
1976: and p_date_earned between pee.effective_start_date and pee.effective_end_date;
1985: pay_element_links_f pelf,
1986: pay_element_types_f pet,
1987: pay_element_classifications pec,
1988: pay_input_values_f piv,
1989: pay_assignment_actions paa
1990: where paa.assignment_action_id = p_assignment_action_id
1991: and pee.assignment_id = paa.assignment_id
1992: and pee.creator_type IN ('EE','RR')
1993: and p_date_earned between pee.effective_start_date and pee.effective_end_date
2013: WHERE plr.legislation_code = 'AU'
2014: AND plr.rule_type ='ADVANCED_RETRO';
2015:
2016: /* Bug 6139035 - Cursor to fetch Effective Date */
2017: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2018: IS
2019: SELECT ppa.effective_date
2020: FROM pay_payroll_actions ppa
2021: ,pay_assignment_actions paa
2017: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2018: IS
2019: SELECT ppa.effective_date
2020: FROM pay_payroll_actions ppa
2021: ,pay_assignment_actions paa
2022: WHERE paa.assignment_action_id = c_assignment_action_id
2023: AND paa.payroll_action_id = ppa.payroll_action_id;
2024:
2025: l_count number;
2267: end count_retro_periods;
2268:
2269: function count_retro_periods_2009
2270: (
2271: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
2272: p_date_earned in pay_payroll_actions.date_earned%TYPE,
2273: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
2274: p_use_tax_flag IN VARCHAR2,
2275: p_mode IN VARCHAR2 --7665727
2269: function count_retro_periods_2009
2270: (
2271: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
2272: p_date_earned in pay_payroll_actions.date_earned%TYPE,
2273: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
2274: p_use_tax_flag IN VARCHAR2,
2275: p_mode IN VARCHAR2 --7665727
2276: )return number
2277: is
2290: pay_element_links_f pelf,
2291: pay_element_types_f pet,
2292: pay_element_classifications pec,
2293: pay_input_values_f piv,
2294: pay_assignment_actions paa,
2295: pay_sub_classification_rules_f psc,
2296: pay_element_classifications pec2 /* Bug 9922156 */
2297: where paa.assignment_action_id = p_assignment_action_id
2298: and pee.assignment_id = paa.assignment_id
2326: pay_element_links_f pelf,
2327: pay_element_types_f pet,
2328: pay_element_classifications pec,
2329: pay_input_values_f piv,
2330: pay_assignment_actions paa,
2331: pay_sub_classification_rules_f psc,
2332: pay_element_classifications pec2
2333: where paa.assignment_action_id = p_assignment_action_id
2334: and pee.assignment_id = paa.assignment_id
2361: pay_element_links_f pelf,
2362: pay_element_types_f pet,
2363: pay_element_classifications pec,
2364: pay_input_values_f piv,
2365: pay_assignment_actions paa
2366: where paa.assignment_action_id = p_assignment_action_id
2367: and pee.assignment_id = paa.assignment_id
2368: and pee.creator_type IN ('EE','RR')
2369: and p_date_earned between pee.effective_start_date and pee.effective_end_date
2388: WHERE plr.legislation_code = 'AU'
2389: AND plr.rule_type ='ADVANCED_RETRO';
2390:
2391:
2392: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2393: IS
2394: SELECT ppa.effective_date
2395: FROM pay_payroll_actions ppa
2396: ,pay_assignment_actions paa
2392: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2393: IS
2394: SELECT ppa.effective_date
2395: FROM pay_payroll_actions ppa
2396: ,pay_assignment_actions paa
2397: WHERE paa.assignment_action_id = c_assignment_action_id
2398: AND paa.payroll_action_id = ppa.payroll_action_id;
2399:
2400: l_count number;
2808: pay_element_types_f pet,
2809: pay_input_values_f piv,
2810: pay_run_result_values prrv,
2811: pay_run_results prr,
2812: pay_assignment_actions paa,
2813: pay_payroll_actions ppa,
2814: per_time_periods ptp,
2815: per_all_assignments_f paaf
2816: where pet.element_name = 'Extra Withholding Payments'
2835: /* Bug - 2491328 Join added for improving the performance */
2836: /* Bug 4374115 - Start */
2837: and not exists(
2838: select pai.locking_action_id
2839: from pay_assignment_actions paa1,
2840: pay_payroll_actions ppa1,
2841: pay_action_interlocks pai
2842: where ppa1.payroll_action_id = paa1.payroll_action_id
2843: and ppa1.action_type = 'V'
3050: It return value of Hours in case the element_id passed is attached to the Salary Basis
3051: */
3052: FUNCTION get_salary_basis_hours
3053: (
3054: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
3055: p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
3056: p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
3057: )
3058: RETURN NUMBER IS
3121: is
3122: CURSOR Cr_action IS
3123: select INTLK.locking_action_id
3124: from pay_action_interlocks INTLK,
3125: pay_assignment_actions paa,
3126: pay_payroll_actions ppa
3127: where INTLK.locked_action_id = p_action_id
3128: and INTLK.locking_action_id = paa.assignment_action_id
3129: and paa.payroll_action_id = ppa.payroll_action_id
3181:
3182: CURSOR c_get_master_tax_unit_id
3183: IS
3184: SELECT paa_master.tax_unit_id
3185: FROM pay_assignment_actions paa_child,
3186: pay_assignment_actions paa_master
3187: WHERE paa_child.assignment_action_id = p_assignment_action_id
3188: AND paa_master.assignment_action_id = paa_child.source_action_id;
3189:
3182: CURSOR c_get_master_tax_unit_id
3183: IS
3184: SELECT paa_master.tax_unit_id
3185: FROM pay_assignment_actions paa_child,
3186: pay_assignment_actions paa_master
3187: WHERE paa_child.assignment_action_id = p_assignment_action_id
3188: AND paa_master.assignment_action_id = paa_child.source_action_id;
3189:
3190: l_flag VARCHAR2(10);
3187: WHERE paa_child.assignment_action_id = p_assignment_action_id
3188: AND paa_master.assignment_action_id = paa_child.source_action_id;
3189:
3190: l_flag VARCHAR2(10);
3191: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
3192: l_procedure varchar2(80);
3193:
3194: BEGIN
3195: g_debug := hr_utility.debug_enabled;
3239: formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION
3240: */
3241:
3242: function get_spread_earning
3243: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3244: p_date_paid in date,
3245: p_pre_tax in number,
3246: p_spread_earning in number) return number is
3247:
3387: end;
3388:
3389: /* new function for bug#6669058 */
3390: function get_retro_spread_earning
3391: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3392: p_date_paid in date,
3393: p_pre_tax in number,
3394: p_spread_earning in number) return number is
3395:
3488: end;
3489:
3490: /*bug8406009 - calc_average_earnings function to calculate the average earnings.*/
3491: FUNCTION calc_average_earnings
3492: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3493: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3494: ,p_payroll_id IN pay_payroll_actions.payroll_id%TYPE
3495: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3496: ,p_business_group_id IN per_business_groups.business_group_id%TYPE
3489:
3490: /*bug8406009 - calc_average_earnings function to calculate the average earnings.*/
3491: FUNCTION calc_average_earnings
3492: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3493: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3494: ,p_payroll_id IN pay_payroll_actions.payroll_id%TYPE
3495: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3496: ,p_business_group_id IN per_business_groups.business_group_id%TYPE
3497: ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3491: FUNCTION calc_average_earnings
3492: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3493: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3494: ,p_payroll_id IN pay_payroll_actions.payroll_id%TYPE
3495: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3496: ,p_business_group_id IN per_business_groups.business_group_id%TYPE
3497: ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3498: ,p_period_start_date IN DATE
3499: ,p_emp_hire_date IN DATE
3522: l_dummy NUMBER;
3523:
3524: /* bug11681524 retrieving original average earnings used for retro spread calculation */
3525:
3526: CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3527: IS
3528: SELECT COUNT(*)
3529: FROM pay_assignment_actions paa
3530: WHERE paa.assignment_action_id = c_assignment_action_id
3525:
3526: CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3527: IS
3528: SELECT COUNT(*)
3529: FROM pay_assignment_actions paa
3530: WHERE paa.assignment_action_id = c_assignment_action_id
3531: AND paa.action_status = 'B';
3532:
3533:
3530: WHERE paa.assignment_action_id = c_assignment_action_id
3531: AND paa.action_status = 'B';
3532:
3533:
3534: CURSOR check_avg_earnings(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3535: IS
3536: select prv.result_value
3537: from pay_run_results prr,
3538: pay_run_result_values prv,
3547: and piv.input_value_id = prv.input_value_id
3548: and prv.run_result_id = prr.run_result_id
3549: and prr.status = 'B';
3550:
3551: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3552: IS
3553: SELECT ppa.effective_date
3554: FROM pay_payroll_actions ppa
3555: ,pay_assignment_actions paa
3551: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3552: IS
3553: SELECT ppa.effective_date
3554: FROM pay_payroll_actions ppa
3555: ,pay_assignment_actions paa
3556: WHERE paa.assignment_action_id = c_assignment_action_id
3557: AND paa.payroll_action_id = ppa.payroll_action_id;
3558:
3559: l_retro_exists NUMBER;
3692: END calc_average_earnings;
3693:
3694: /*bug8406009 - calc_lt12_prev_spread_tax function is added for retro spread deduction in previous financial year less than 12 months*/
3695: FUNCTION calc_lt12_prev_spread_tax
3696: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3697: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3698: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3699: ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3700: ,p_business_group_id IN pay_payroll_actions.business_group_id%TYPE
3693:
3694: /*bug8406009 - calc_lt12_prev_spread_tax function is added for retro spread deduction in previous financial year less than 12 months*/
3695: FUNCTION calc_lt12_prev_spread_tax
3696: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3697: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3698: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3699: ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3700: ,p_business_group_id IN pay_payroll_actions.business_group_id%TYPE
3701: ,p_average_earnings IN NUMBER
3694: /*bug8406009 - calc_lt12_prev_spread_tax function is added for retro spread deduction in previous financial year less than 12 months*/
3695: FUNCTION calc_lt12_prev_spread_tax
3696: (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3697: ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3698: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3699: ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3700: ,p_business_group_id IN pay_payroll_actions.business_group_id%TYPE
3701: ,p_average_earnings IN NUMBER
3702: ,p_tax_scale IN VARCHAR2
3707: )
3708: RETURN VARCHAR2
3709: IS
3710: CURSOR get_retro_spread_entries
3711: (c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3712: ,c_date_earned IN pay_payroll_actions.date_earned%TYPE)
3713: IS
3714: SELECT pee.element_entry_id,
3715: peev.screen_entry_value retro_amount,
3742:
3743:
3744: CURSOR get_spread_over_ee
3745: (c_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
3746: ,c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3747: ,c_date_earned IN DATE )
3748: IS
3749: SELECT peev.screen_entry_value
3750: FROM pay_element_entries_f pee
3762: AND c_date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
3763:
3764: CURSOR get_spread_over_rr
3765: (c_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
3766: ,c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3767: ,c_date_earned IN DATE )
3768: IS
3769: SELECT prrv.result_value
3770: FROM pay_run_results prr
3788: WHERE plr.legislation_code = 'AU'
3789: AND plr.rule_type ='ADVANCED_RETRO';
3790:
3791:
3792: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3793: IS
3794: SELECT ppa.effective_date
3795: FROM pay_payroll_actions ppa
3796: ,pay_assignment_actions paa
3792: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3793: IS
3794: SELECT ppa.effective_date
3795: FROM pay_payroll_actions ppa
3796: ,pay_assignment_actions paa
3797: WHERE paa.assignment_action_id = c_assignment_action_id
3798: AND paa.payroll_action_id = ppa.payroll_action_id;
3799:
3800:
4104: ,p_retro_adj_leave_load OUT NOCOPY NUMBER)
4105: RETURN NUMBER
4106: AS
4107: /* Cursor replicates RR Route for _ASG_LE_YTD with status = 'B' check added */
4108: CURSOR get_retro_ytd_results(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
4109: ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
4110: ,c_balance_type_id pay_balance_types.balance_type_id%TYPE
4111: )
4112: IS
4105: RETURN NUMBER
4106: AS
4107: /* Cursor replicates RR Route for _ASG_LE_YTD with status = 'B' check added */
4108: CURSOR get_retro_ytd_results(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
4109: ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
4110: ,c_balance_type_id pay_balance_types.balance_type_id%TYPE
4111: )
4112: IS
4113: SELECT SUM(NVL(TARGET.result_value,0))
4115: pay_balance_feeds_f FEED
4116: , pay_run_result_values TARGET
4117: , pay_run_results prr
4118: , pay_payroll_actions ppa
4119: , pay_assignment_actions paa
4120: , pay_payroll_actions ppas
4121: , pay_assignment_actions paas
4122: where paas.assignment_action_id = c_assignment_action_id
4123: and paas.payroll_action_id = ppas.payroll_action_id
4117: , pay_run_results prr
4118: , pay_payroll_actions ppa
4119: , pay_assignment_actions paa
4120: , pay_payroll_actions ppas
4121: , pay_assignment_actions paas
4122: where paas.assignment_action_id = c_assignment_action_id
4123: and paas.payroll_action_id = ppas.payroll_action_id
4124: and FEED.input_value_id = TARGET.input_value_id
4125: and TARGET.run_result_id = prr.run_result_id
4142: WHERE PBT.balance_name IN ('Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
4143: ,'Retro Earnings Leave Loading GT 12 Mths Amount')
4144: AND PBT.legislation_code ='AU';
4145:
4146: CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
4147: IS
4148: SELECT COUNT(*)
4149: FROM pay_assignment_actions paa
4150: WHERE paa.assignment_action_id = c_assignment_action_id
4145:
4146: CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
4147: IS
4148: SELECT COUNT(*)
4149: FROM pay_assignment_actions paa
4150: WHERE paa.assignment_action_id = c_assignment_action_id
4151: AND paa.action_status = 'B';
4152:
4153: l_adj_retro_value NUMBER;
4237:
4238: CURSOR check_retro_run
4239: IS
4240: SELECT COUNT(*)
4241: FROM pay_assignment_actions paa
4242: WHERE paa.assignment_action_id = p_assignment_action_id
4243: AND paa.action_status = 'B';
4244:
4245: cursor get_retro_total is
4329:
4330: g_context_table pay_balance_pkg.t_context_tab;
4331: g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
4332: g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
4333: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
4334: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
4335: l_assignment_id pay_assignment_actions.assignment_id%TYPE;
4336: l_earnings NUMBER;
4337:
4330: g_context_table pay_balance_pkg.t_context_tab;
4331: g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
4332: g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
4333: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
4334: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
4335: l_assignment_id pay_assignment_actions.assignment_id%TYPE;
4336: l_earnings NUMBER;
4337:
4338: CURSOR csr_max_assgt_action(p_assignment_id IN pay_assignment_actions.assignment_id%TYPE)
4331: g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
4332: g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
4333: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
4334: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
4335: l_assignment_id pay_assignment_actions.assignment_id%TYPE;
4336: l_earnings NUMBER;
4337:
4338: CURSOR csr_max_assgt_action(p_assignment_id IN pay_assignment_actions.assignment_id%TYPE)
4339: IS
4334: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
4335: l_assignment_id pay_assignment_actions.assignment_id%TYPE;
4336: l_earnings NUMBER;
4337:
4338: CURSOR csr_max_assgt_action(p_assignment_id IN pay_assignment_actions.assignment_id%TYPE)
4339: IS
4340: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
4341: from pay_assignment_actions paa
4342: , pay_payroll_actions ppa
4337:
4338: CURSOR csr_max_assgt_action(p_assignment_id IN pay_assignment_actions.assignment_id%TYPE)
4339: IS
4340: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
4341: from pay_assignment_actions paa
4342: , pay_payroll_actions ppa
4343: , per_assignments_f paf
4344: , per_time_periods ptp
4345: where paa.assignment_id = paf.assignment_id