1 package body pay_consumed_entry as
2 /* $Header: pyconsum.pkb 120.2.12010000.1 2008/07/27 22:22:34 appldev ship $ */
3 --
4 --
5 FUNCTION consumed_entry ( p_date_earned IN DATE,
6 p_payroll_id IN NUMBER,
7 p_ele_entry_id IN NUMBER) RETURN VARCHAR2 IS
8 --
9 -- Checks for "consumed" element entry (context) in skip rule for earnings
10 -- and deductions. Payroll Action's DATE_EARNED must be between the
11 -- EARNED period dates. Have PAY_EARNED_PERIOD_START_DATE and
12 -- PAY_EARNED_PERIOD_END_DATE been requested from core?!? For now we'll
13 -- derive these period dates in the UDF based on the "date earned" context.
14 --
15 v_consumed VARCHAR2(1) := 'N';
16 v_reversed VARCHAR2(1) := 'N';
17 l_period_start DATE;
18 l_period_end DATE;
19 g_debug boolean := hr_utility.debug_enabled;
20 --
21 BEGIN
22 --
23 if g_debug then
24 hr_utility.set_location('consumed_entry', 10);
25 end if;
26 --
27 -- Find EARNED period dates
28 --
29 SELECT start_date,
30 end_date
31 INTO l_period_start,
32 l_period_end
33 FROM per_time_periods
34 WHERE payroll_id = p_payroll_id
35 AND p_date_earned BETWEEN start_date AND end_date;
36 --
37 if g_debug then
38 hr_utility.set_location('consumed_entry', 20);
39 end if;
40 --
41 SELECT /*+ INDEX(PPA PAY_PAYROLL_ACTIONS_PK)*/
42 DECODE(COUNT(PRR.run_result_id), 0, 'N', 'Y')
43 INTO v_consumed
44 FROM pay_run_results PRR,
45 pay_assignment_actions ASA,
46 pay_payroll_actions PPA
47 WHERE PRR.source_id = p_ele_entry_id
48 AND PRR.source_type IN ('E', 'I')
49 AND PRR.status in ('P', 'PA', 'R', 'O')
50 AND ASA.assignment_action_id = PRR.assignment_action_id
51 AND PPA.payroll_action_id = ASA.payroll_action_id
52 --
53 -- Check whether the run_result has been revered.
54 AND not exists (select null
55 from pay_run_results prr2
56 where prr2.source_id = PRR.run_result_id
57 and prr2.source_type in ('R', 'V'))
58 AND PPA.date_earned BETWEEN l_period_start
59 AND l_period_end;
60 --
61 if g_debug then
62 hr_utility.set_location('consumed_entry', 39);
63 end if;
64
65 RETURN v_consumed;
66
67 exception when NO_DATA_FOUND then
68 if g_debug then
69 hr_utility.set_location('consumed_entry', 40);
70 end if;
71 RETURN v_consumed;
72 --
73 END consumed_entry;
74
75 end pay_consumed_entry;
[Home] [Help]