[Home] [Help]
246: function periods_since_hire_date
247: (
248: p_payroll_id in number,
249: p_assignment_id in per_all_assignments_f.assignment_id%type,
250: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
251: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
252: p_period_num in number,
253: p_period_start in date,
254: p_emp_hire_date in date,
247: (
248: p_payroll_id in number,
249: p_assignment_id in per_all_assignments_f.assignment_id%type,
250: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
251: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
252: p_period_num in number,
253: p_period_start in date,
254: p_emp_hire_date in date,
255: p_use_tax_flag IN VARCHAR2 --2610141
344: IS
345: select ppa.date_earned,
346: ppa.effective_date
347: from pay_payroll_actions ppa,
348: pay_assignment_actions paa
349: where ppa.payroll_action_id = paa.payroll_action_id
350: and paa.assignment_action_id = p_assignment_action_id;
351:
352:
530:
531:
532: -- Variable to store the maximum previous year assignment action id and its corresponding
533: -- tax_unit_id (legal Employer).
534: l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
535: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
536:
537:
538: -- Total Earnings variable
531:
532: -- Variable to store the maximum previous year assignment action id and its corresponding
533: -- tax_unit_id (legal Employer).
534: l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
535: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
536:
537:
538: -- Total Earnings variable
539: --
557: c_fin_start_date in date,
558: c_fin_end_date in date)
559: IS
560: SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
561: FROM pay_assignment_actions paa
562: ,pay_payroll_actions ppa
563: WHERE paa.assignment_id = c_assignment_id
564: and ppa.payroll_action_id = paa.payroll_action_id
565: and ppa.business_group_id = c_business_group_id
565: and ppa.business_group_id = c_business_group_id
566: AND paa.action_sequence in
567: (
568: SELECT MAX(paa.action_sequence)
569: FROM pay_assignment_actions paa,
570: pay_payroll_actions ppa,
571: per_all_assignments_f paaf
572: WHERE ppa.business_group_id = c_business_group_id
573: AND paaf.assignment_id = c_assignment_id
587: c_fin_start_date in date,
588: c_fin_end_date in date)
589: IS
590: SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
591: FROM pay_assignment_actions paa
592: ,pay_payroll_actions ppa
593: WHERE paa.assignment_id = c_assignment_id
594: and ppa.payroll_action_id = paa.payroll_action_id
595: and ppa.business_group_id = c_business_group_id
595: and ppa.business_group_id = c_business_group_id
596: AND paa.action_sequence in
597: (
598: SELECT MAX(paa.action_sequence)
599: FROM pay_assignment_actions paa,
600: pay_payroll_actions ppa,
601: per_all_assignments_f paaf
602: WHERE ppa.business_group_id = c_business_group_id
603: AND paaf.assignment_id = c_assignment_id
652: CURSOR c_get_payroll_effective_date
653: IS
654: SELECT ppa.effective_date
655: FROM pay_payroll_actions ppa,
656: pay_assignment_actions paa
657: WHERE paa.assignment_action_id = p_assignment_action_id
658: AND ppa.payroll_action_id = paa.payroll_action_id;
659:
660: /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
664: c_start_date in date,
665: c_end_date in date)
666: IS
667: SELECT count(paa.assignment_action_id)
668: FROM pay_assignment_actions paa,
669: pay_payroll_actions ppa,
670: per_assignments_f paf
671: WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
672: AND ppa.business_group_id = c_business_group_id
988: from per_time_periods ptp
989: where exists (select 'EXISTS' from
990: per_assignments_f paf,
991: pay_payroll_actions ppa,
992: pay_assignment_actions paa
993: where ppa.payroll_id = v_payroll_id
994: and ppa.action_type in ('R','Q')
995: and paa.action_status = 'C'
996: and ppa.payroll_action_id = paa.payroll_action_id
1010: CURSOR c_get_payroll_effective_date
1011: IS
1012: SELECT ppa.effective_date
1013: FROM pay_payroll_actions ppa,
1014: pay_assignment_actions paa
1015: WHERE paa.assignment_action_id = p_assignment_action_id
1016: AND ppa.payroll_action_id = paa.payroll_action_id;
1017:
1018:
1222: function get_retro_period
1223: (
1224: p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1225: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1226: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,/*Bug 4418107*/
1227: p_retro_start_date out NOCOPY date,
1228: p_retro_end_date out NOCOPY date
1229: )return number is
1230:
1235: ) is
1236: SELECT ptp.start_date,ptp.end_date
1237: FROM per_time_periods ptp,
1238: pay_payroll_actions ppa,
1239: pay_assignment_actions paa,
1240: pay_run_results prr,
1241: pay_element_entries_f pee
1242: WHERE pee.element_entry_id=c_element_entry_id
1243: and prr.run_result_id = pee.source_id
1257: ) is
1258: SELECT ptp.start_date,ptp.end_date
1259: FROM per_time_periods ptp,
1260: pay_payroll_actions ppa,
1261: pay_assignment_actions paa,
1262: pay_element_entries_f pee
1263: WHERE pee.element_entry_id=c_element_entry_id
1264: and paa.assignment_action_id=pee.source_asg_action_id
1265: AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1345: FUNCTION get_enhanced_retro_period
1346: (
1347: p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1348: p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1349: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1350: p_retro_start_date OUT NOCOPY date,
1351: p_retro_end_date OUT NOCOPY date,
1352: p_orig_effective_date OUT NOCOPY date,
1353: p_retro_effective_date OUT NOCOPY date,
1363: IS
1364: SELECT pee.creator_type,
1365: ppa.effective_date
1366: FROM pay_payroll_actions ppa,
1367: pay_assignment_actions paa,
1368: pay_element_entries_f pee
1369: WHERE ppa.payroll_action_id = paa.payroll_action_id
1370: AND paa.assignment_action_id = pee.creator_id
1371: AND pee.element_entry_id = c_element_entry_id
1380: ,ptp.end_date
1381: ,ppa.effective_date
1382: FROM per_time_periods ptp,
1383: pay_payroll_actions ppa,
1384: pay_assignment_actions paa,
1385: pay_run_results prr,
1386: pay_element_entries_f pee
1387: WHERE pee.element_entry_id=c_element_entry_id
1388: AND prr.run_result_id = pee.source_id
1404: ,ptp.end_date
1405: ,ppa.effective_date
1406: FROM per_time_periods ptp,
1407: pay_payroll_actions ppa,
1408: pay_assignment_actions paa,
1409: pay_element_entries_f pee
1410: WHERE pee.element_entry_id=c_element_entry_id
1411: AND paa.assignment_action_id=pee.source_asg_action_id
1412: AND paa.tax_unit_id = p_tax_unit_id
1575: FUNCTION get_retro_time_span
1576: (
1577: p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1578: p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1579: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1580: p_retro_start_date OUT NOCOPY date,
1581: p_retro_end_date OUT NOCOPY date,
1582: p_orig_effective_date OUT NOCOPY date,
1583: p_retro_effective_date OUT NOCOPY date,
1593: SELECT pee.element_entry_id
1594: ,ppa.retro_definition_id
1595: ,pepd.retro_component_id
1596: FROM pay_element_entries_f pee
1597: ,pay_assignment_actions paa
1598: ,pay_payroll_actions ppa
1599: ,pay_entry_process_details pepd
1600: WHERE pee.element_entry_id = c_element_entry_id
1601: AND pee.element_entry_id = pepd.element_entry_id
1705:
1706:
1707: function count_retro_periods
1708: (
1709: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1710: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1711: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1712: p_use_tax_flag IN VARCHAR2 --4415795
1713: )return number
1707: function count_retro_periods
1708: (
1709: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1710: p_date_earned in pay_payroll_actions.date_earned%TYPE,
1711: p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1712: p_use_tax_flag IN VARCHAR2 --4415795
1713: )return number
1714: is
1715:
1717: /*
1718: cursor get_element_entries
1719: is
1720: select pee.element_entry_id from pay_element_entries_f pee,
1721: pay_assignment_actions paa
1722: where paa.assignment_action_id=p_assignment_action_id
1723: and pee.assignment_id=paa.assignment_id
1724: and pee.creator_type IN ('EE','RR')
1725: and p_date_earned between pee.effective_start_date and pee.effective_end_date;
1734: pay_element_links_f pelf,
1735: pay_element_types_f pet,
1736: pay_element_classifications pec,
1737: pay_input_values_f piv,
1738: pay_assignment_actions paa
1739: where paa.assignment_action_id = p_assignment_action_id
1740: and pee.assignment_id = paa.assignment_id
1741: and pee.creator_type IN ('EE','RR')
1742: and p_date_earned between pee.effective_start_date and pee.effective_end_date
1762: WHERE plr.legislation_code = 'AU'
1763: AND plr.rule_type ='ADVANCED_RETRO';
1764:
1765: /* Bug 6139035 - Cursor to fetch Effective Date */
1766: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1767: IS
1768: SELECT ppa.effective_date
1769: FROM pay_payroll_actions ppa
1770: ,pay_assignment_actions paa
1766: CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1767: IS
1768: SELECT ppa.effective_date
1769: FROM pay_payroll_actions ppa
1770: ,pay_assignment_actions paa
1771: WHERE paa.assignment_action_id = c_assignment_action_id
1772: AND paa.payroll_action_id = ppa.payroll_action_id;
1773:
1774: l_count number;
2060: pay_element_types_f pet,
2061: pay_input_values_f piv,
2062: pay_run_result_values prrv,
2063: pay_run_results prr,
2064: pay_assignment_actions paa,
2065: pay_payroll_actions ppa,
2066: per_time_periods ptp,
2067: per_all_assignments_f paaf
2068: where pet.element_name = 'Extra Withholding Payments'
2086: /* Bug - 2491328 Join added for improving the performance */
2087: /* Bug 4374115 - Start */
2088: and not exists(
2089: select pai.locking_action_id
2090: from pay_assignment_actions paa1,
2091: pay_payroll_actions ppa1,
2092: pay_action_interlocks pai
2093: where ppa1.payroll_action_id = paa1.payroll_action_id
2094: and ppa1.action_type = 'V'
2301: It return value of Hours in case the element_id passed is attached to the Salary Basis
2302: */
2303: FUNCTION get_salary_basis_hours
2304: (
2305: p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
2306: p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
2307: p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
2308: )
2309: RETURN NUMBER IS
2372: is
2373: CURSOR Cr_action IS
2374: select INTLK.locking_action_id
2375: from pay_action_interlocks INTLK,
2376: pay_assignment_actions paa,
2377: pay_payroll_actions ppa
2378: where INTLK.locked_action_id = p_action_id
2379: and INTLK.locking_action_id = paa.assignment_action_id
2380: and paa.payroll_action_id = ppa.payroll_action_id
2432:
2433: CURSOR c_get_master_tax_unit_id
2434: IS
2435: SELECT paa_master.tax_unit_id
2436: FROM pay_assignment_actions paa_child,
2437: pay_assignment_actions paa_master
2438: WHERE paa_child.assignment_action_id = p_assignment_action_id
2439: AND paa_master.assignment_action_id = paa_child.source_action_id;
2440:
2433: CURSOR c_get_master_tax_unit_id
2434: IS
2435: SELECT paa_master.tax_unit_id
2436: FROM pay_assignment_actions paa_child,
2437: pay_assignment_actions paa_master
2438: WHERE paa_child.assignment_action_id = p_assignment_action_id
2439: AND paa_master.assignment_action_id = paa_child.source_action_id;
2440:
2441: l_flag VARCHAR2(10);
2438: WHERE paa_child.assignment_action_id = p_assignment_action_id
2439: AND paa_master.assignment_action_id = paa_child.source_action_id;
2440:
2441: l_flag VARCHAR2(10);
2442: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
2443: l_procedure varchar2(80);
2444:
2445: BEGIN
2446: g_debug := hr_utility.debug_enabled;
2490: formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION
2491: */
2492:
2493: function get_spread_earning
2494: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2495: p_date_paid in date,
2496: p_pre_tax in number,
2497: p_spread_earning in number) return number is
2498:
2612: end;
2613:
2614: /* new function for bug#6669058 */
2615: function get_retro_spread_earning
2616: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2617: p_date_paid in date,
2618: p_pre_tax in number,
2619: p_spread_earning in number) return number is
2620: