271:
272: function get_retro_period
273: (
274: p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
275: p_date_earned in pay_payroll_actions.date_earned%TYPE
276: )return date is
277:
278: cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
279: c_date_earned in pay_payroll_actions.date_earned%TYPE
275: p_date_earned in pay_payroll_actions.date_earned%TYPE
276: )return date is
277:
278: cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
279: c_date_earned in pay_payroll_actions.date_earned%TYPE
280: ) is
281: SELECT creator_type
282: FROM pay_element_entries_f pee
283: WHERE pee.element_entry_id=c_element_entry_id
284: and c_date_earned between pee.effective_start_date and pee.effective_end_date;
285:
286: cursor get_retro_period_rr
287: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
288: c_date_earned in pay_payroll_actions.date_earned%TYPE
289: ) is
290:
291: SELECT ptp.start_date
292: FROM per_time_periods ptp,
289: ) is
290:
291: SELECT ptp.start_date
292: FROM per_time_periods ptp,
293: pay_payroll_actions ppa,
294: pay_assignment_actions paa,
295: pay_run_results prr,
296: pay_element_entries_f pee
297: WHERE pee.element_entry_id=c_element_entry_id
304: and c_date_earned between pee.effective_start_date and pee.effective_end_date;
305:
306: cursor get_retro_period_nr
307: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
308: c_date_earned in pay_payroll_actions.date_earned%TYPE
309: ) is
310:
311: SELECT ptp.start_date
312: FROM per_time_periods ptp,
309: ) is
310:
311: SELECT ptp.start_date
312: FROM per_time_periods ptp,
313: pay_payroll_actions ppa,
314: pay_assignment_actions paa,
315: pay_run_results prr,
316: pay_element_entries_f pee
317: WHERE pee.element_entry_id=c_element_entry_id
324: and c_date_earned between pee.effective_start_date and pee.effective_end_date;
325:
326: cursor get_retro_period_pr
327: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
328: c_date_earned in pay_payroll_actions.date_earned%TYPE
329: ) is
330:
331: SELECT ptp.start_date
332: FROM per_time_periods ptp,
329: ) is
330:
331: SELECT ptp.start_date
332: FROM per_time_periods ptp,
333: pay_payroll_actions ppa,
334: pay_assignment_actions paa,
335: pay_run_results prr,
336: pay_element_entries_f pee
337: WHERE pee.element_entry_id=c_element_entry_id
344: and c_date_earned between pee.effective_start_date and pee.effective_end_date;
345:
346: cursor get_retro_period_ee
347: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
348: c_date_earned in pay_payroll_actions.date_earned%TYPE
349: ) is
350: SELECT ptp.start_date
351: FROM per_time_periods ptp,
352: pay_payroll_actions ppa,
348: c_date_earned in pay_payroll_actions.date_earned%TYPE
349: ) is
350: SELECT ptp.start_date
351: FROM per_time_periods ptp,
352: pay_payroll_actions ppa,
353: pay_assignment_actions paa,
354: pay_element_entries_f pee
355: WHERE pee.element_entry_id=c_element_entry_id
356: and paa.assignment_action_id=pee.source_asg_action_id
733: l_date_earned_ppa date;
734:
735: cursor csr_get_ppa_date is
736: select effective_date,date_earned
737: from pay_payroll_actions ppa
738: where ppa.payroll_action_id = l_payroll_action_id;
739: Begin
740:
741: open csr_get_ppa_date;
1361: RETURN NUMBER IS
1362: cursor csr_get_retro_bal_val IS
1363: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1364: from
1365: pay_payroll_actions ppa,
1366: pay_assignment_actions paa,
1367: pay_run_results prr,
1368: pay_run_result_values prv,
1369: pay_element_types_f pet,
1406: RETURN NUMBER IS
1407: CURSOR csr_retro_bal_val IS
1408: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1409: from
1410: pay_payroll_actions ppa,
1411: pay_assignment_actions paa,
1412: pay_run_results prr,
1413: pay_run_result_values prv,
1414: pay_element_types_f pet,
1462: RETURN NUMBER IS
1463: CURSOR csr_retro_element_context IS
1464: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1465: from
1466: pay_payroll_actions ppa,
1467: pay_assignment_actions paa,
1468: pay_run_results prr,
1469: pay_run_result_values prv,
1470: pay_run_result_values prv1,
1494:
1495: CURSOR csr_retro_element IS
1496: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1497: from
1498: pay_payroll_actions ppa,
1499: pay_assignment_actions paa,
1500: pay_run_results prr,
1501: pay_run_result_values prv,
1502: pay_input_values_f piv
1543: RETURN NUMBER IS
1544: cursor csr_get_elmt_bal_val IS
1545: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1546: from
1547: pay_payroll_actions ppa,
1548: pay_assignment_actions paa,
1549: pay_run_results prr,
1550: pay_run_result_values prv,
1551: pay_element_types_f pet,
1603: p_parameter_string IN VARCHAR2
1604: ,p_token IN VARCHAR2
1605: ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
1606: IS
1607: l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
1608: l_start_pos NUMBER;
1609: l_delimiter varchar2(1):=' ';
1610: l_proc VARCHAR2(60):= g_package||' get parameter ';
1611: BEGIN
1639: FUNCTION get_file_id(p_effective_date IN DATE) RETURN VARCHAR2 AS
1640: --
1641: CURSOR get_file_no_per_day IS
1642: SELECT count(*)
1643: FROM pay_payroll_actions ppa
1644: WHERE TRIM(PAY_NL_GENERAL.get_parameter(ppa.legislative_parameters
1645: ,'MAGTAPE_REPORT_ID'))= 'NL_PAYFILE'
1646: AND action_type = 'M'
1647: AND action_status = 'C'
1698: RETURN NUMBER AS
1699: cursor csr_get_elmt_bal_val is
1700: select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
1701: from
1702: pay_payroll_actions ppa,
1703: pay_assignment_actions paa,
1704: pay_run_results prr,
1705: pay_run_result_values prv,
1706: pay_element_types_f pet,
1745: l_date_earned_ppa date;
1746:
1747: cursor csr_get_ppa_date is
1748: select date_earned
1749: from pay_payroll_actions ppa
1750: where ppa.payroll_action_id = p_payroll_action_id;
1751:
1752: Begin
1753:
1777: RETURN NUMBER is
1778:
1779: cursor csr_get_num_periods(c_payroll_action_id NUMBER) is
1780: select max(TPERIOD.period_num) from
1781: pay_payroll_actions PACTION
1782: ,per_time_periods TPERIOD
1783: where PACTION.payroll_action_id = c_payroll_action_id
1784: and TPERIOD.payroll_id = PACTION.payroll_id
1785: and to_char(PACTION.date_earned,'YYYY') = to_char(TPERIOD.regular_payment_date,'YYYY');
1812: SELECT 'DP' FROM dual
1813: WHERE EXISTS (SELECT 1
1814: FROM pay_assignment_actions paa1
1815: ,pay_assignment_actions paa2
1816: ,pay_payroll_actions ppa
1817: WHERE paa1.assignment_action_id = c_assignment_action_id
1818: AND paa2.assignment_id = c_assignment_id
1819: AND paa1.tax_unit_id = paa2.tax_unit_id
1820: AND ppa.action_type IN ('Q','R')
1829: ,c_end_date DATE)IS
1830: SELECT 'DE' FROM dual
1831: WHERE EXISTS (SELECT 1
1832: FROM pay_assignment_actions paa
1833: ,pay_payroll_actions ppa
1834: WHERE paa.assignment_id = c_assignment_id
1835: AND ppa.payroll_action_id = paa.payroll_action_id
1836: AND ppa.action_type IN ('Q','R')
1837: AND ppa.action_status in ('C','P')
1845: ,ppa.action_type
1846: ,ppa.action_status
1847: ,ppa.date_earned
1848: ,ppa.business_group_id
1849: FROM pay_payroll_actions ppa
1850: ,per_time_periods ptp
1851: WHERE ppa.payroll_action_id = c_payroll_action_id
1852: AND ptp.time_period_id = ppa.time_period_id;
1853: --
1998: SELECT 'DP' FROM dual
1999: WHERE EXISTS (SELECT 1
2000: FROM pay_assignment_actions paa1
2001: ,pay_assignment_actions paa2
2002: ,pay_payroll_actions ppa
2003: WHERE paa1.assignment_action_id = c_assignment_action_id
2004: AND paa2.assignment_id = c_assignment_id
2005: AND paa1.tax_unit_id = paa2.tax_unit_id
2006: AND ppa.action_type IN ('Q','R')
2015: ,c_end_date DATE)IS
2016: SELECT 'DE' FROM dual
2017: WHERE EXISTS (SELECT 1
2018: FROM pay_assignment_actions paa
2019: ,pay_payroll_actions ppa
2020: WHERE paa.assignment_id = c_assignment_id
2021: AND ppa.payroll_action_id = paa.payroll_action_id
2022: AND ppa.action_type IN ('Q','R')
2023: AND ppa.action_status in ('C','P')
2030: ,ppa.action_type
2031: ,ppa.action_status
2032: ,ppa.date_earned
2033: ,ppa.business_group_id
2034: FROM pay_payroll_actions ppa
2035: ,per_time_periods ptp
2036: WHERE ppa.payroll_action_id = c_payroll_action_id
2037: AND ptp.time_period_id = ppa.time_period_id;
2038: --
2184: SELECT 'DP' FROM dual
2185: WHERE EXISTS (SELECT 1
2186: FROM pay_assignment_actions paa1
2187: ,pay_assignment_actions paa2
2188: ,pay_payroll_actions ppa
2189: WHERE paa1.assignment_action_id = c_assignment_action_id
2190: AND paa2.assignment_id = paa1.assignment_id
2191: AND paa1.tax_unit_id = paa2.tax_unit_id
2192: AND ppa.action_type IN ('Q','R')
2200: SELECT 'DP' FROM dual
2201: WHERE EXISTS (SELECT 1
2202: FROM pay_assignment_actions paa1
2203: ,pay_assignment_actions paa2
2204: ,pay_payroll_actions ppa
2205: WHERE paa1.assignment_action_id = c_assignment_action_id
2206: AND paa2.assignment_id = paa1.assignment_id
2207: AND paa1.tax_unit_id = paa2.tax_unit_id
2208: AND ppa.action_type IN ('Q','R')
2219: SELECT 'DE' FROM dual
2220: WHERE EXISTS (SELECT 1
2221: FROM pay_assignment_actions paa1
2222: ,pay_assignment_actions paa2
2223: ,pay_payroll_actions ppa1
2224: ,pay_payroll_actions ppa2
2225: WHERE ppa1.payroll_action_id = c_payroll_action_id
2226: AND ppa1.payroll_action_id = paa1.payroll_action_id
2227: AND paa1.assignment_action_id = c_assignment_action_id
2220: WHERE EXISTS (SELECT 1
2221: FROM pay_assignment_actions paa1
2222: ,pay_assignment_actions paa2
2223: ,pay_payroll_actions ppa1
2224: ,pay_payroll_actions ppa2
2225: WHERE ppa1.payroll_action_id = c_payroll_action_id
2226: AND ppa1.payroll_action_id = paa1.payroll_action_id
2227: AND paa1.assignment_action_id = c_assignment_action_id
2228: AND paa1.assignment_id = paa2.assignment_id
2240: SELECT 'DE' FROM dual
2241: WHERE EXISTS (SELECT 1
2242: FROM pay_assignment_actions paa1
2243: ,pay_assignment_actions paa2
2244: ,pay_payroll_actions ppa1
2245: ,pay_payroll_actions ppa2
2246: WHERE ppa1.payroll_action_id = c_payroll_action_id
2247: AND ppa1.payroll_action_id = paa1.payroll_action_id
2248: AND paa1.assignment_action_id = c_assignment_action_id
2241: WHERE EXISTS (SELECT 1
2242: FROM pay_assignment_actions paa1
2243: ,pay_assignment_actions paa2
2244: ,pay_payroll_actions ppa1
2245: ,pay_payroll_actions ppa2
2246: WHERE ppa1.payroll_action_id = c_payroll_action_id
2247: AND ppa1.payroll_action_id = paa1.payroll_action_id
2248: AND paa1.assignment_action_id = c_assignment_action_id
2249: AND paa1.assignment_id = paa2.assignment_id
2261: SELECT ptp.start_date
2262: ,ptp.end_date
2263: ,ppa.date_earned
2264: ,business_group_id
2265: FROM pay_payroll_actions ppa
2266: ,per_time_periods ptp
2267: WHERE ppa.payroll_action_id = c_payroll_action_id
2268: AND ptp.time_period_id = ppa.time_period_id;
2269: --
2423: SELECT 'DP' FROM dual
2424: WHERE EXISTS (SELECT 1
2425: FROM pay_assignment_actions paa1
2426: ,pay_assignment_actions paa2
2427: ,pay_payroll_actions ppa
2428: WHERE paa1.assignment_action_id = c_assignment_action_id
2429: AND paa2.assignment_id = paa1.assignment_id
2430: AND paa1.tax_unit_id = paa2.tax_unit_id
2431: AND ppa.action_type IN ('Q','R')
2439: SELECT 'DP' FROM dual
2440: WHERE EXISTS (SELECT 1
2441: FROM pay_assignment_actions paa1
2442: ,pay_assignment_actions paa2
2443: ,pay_payroll_actions ppa
2444: WHERE paa1.assignment_action_id = c_assignment_action_id
2445: AND paa2.assignment_id = paa1.assignment_id
2446: AND paa1.tax_unit_id = paa2.tax_unit_id
2447: AND ppa.action_type IN ('Q','R')
2458: SELECT 'DE' FROM dual
2459: WHERE EXISTS (SELECT 1
2460: FROM pay_assignment_actions paa1
2461: ,pay_assignment_actions paa2
2462: ,pay_payroll_actions ppa1
2463: ,pay_payroll_actions ppa2
2464: WHERE ppa1.payroll_action_id = c_payroll_action_id
2465: AND ppa1.payroll_action_id = paa1.payroll_action_id
2466: AND paa1.assignment_action_id = c_assignment_action_id
2459: WHERE EXISTS (SELECT 1
2460: FROM pay_assignment_actions paa1
2461: ,pay_assignment_actions paa2
2462: ,pay_payroll_actions ppa1
2463: ,pay_payroll_actions ppa2
2464: WHERE ppa1.payroll_action_id = c_payroll_action_id
2465: AND ppa1.payroll_action_id = paa1.payroll_action_id
2466: AND paa1.assignment_action_id = c_assignment_action_id
2467: AND paa1.assignment_id = paa2.assignment_id
2479: SELECT 'DE' FROM dual
2480: WHERE EXISTS (SELECT 1
2481: FROM pay_assignment_actions paa1
2482: ,pay_assignment_actions paa2
2483: ,pay_payroll_actions ppa1
2484: ,pay_payroll_actions ppa2
2485: WHERE ppa1.payroll_action_id = c_payroll_action_id
2486: AND ppa1.payroll_action_id = paa1.payroll_action_id
2487: AND paa1.assignment_action_id = c_assignment_action_id
2480: WHERE EXISTS (SELECT 1
2481: FROM pay_assignment_actions paa1
2482: ,pay_assignment_actions paa2
2483: ,pay_payroll_actions ppa1
2484: ,pay_payroll_actions ppa2
2485: WHERE ppa1.payroll_action_id = c_payroll_action_id
2486: AND ppa1.payroll_action_id = paa1.payroll_action_id
2487: AND paa1.assignment_action_id = c_assignment_action_id
2488: AND paa1.assignment_id = paa2.assignment_id
2499: SELECT ptp.start_date
2500: ,ptp.end_date
2501: ,ppa.date_earned
2502: ,business_group_id
2503: FROM pay_payroll_actions ppa
2504: ,per_time_periods ptp
2505: WHERE ppa.payroll_action_id = c_payroll_action_id
2506: AND ptp.time_period_id = ppa.time_period_id;
2507: --