1 PACKAGE BODY DK_CONSUMED_ENTRY as
2 /* $Header: pydkconsum.pkb 120.1 2010/08/22 15:26:21 rsahai noship $ */
3
4
5 FUNCTION consumed_entry_spl ( p_date_earned IN DATE,
6 p_payroll_id IN NUMBER,
7 p_asg_id IN NUMBER) RETURN VARCHAR2 IS
8
9 v_consumed VARCHAR2(1) := 'N';
10 v_reversed VARCHAR2(1) := 'N';
11 l_period_start DATE;
12 l_period_end DATE;
13 g_debug boolean := hr_utility.debug_enabled;
14 --
15 BEGIN
16 --
17 if g_debug then
18 hr_utility.set_location('consumed_entry spl', 10);
19 end if;
20 --
21 -- Find EARNED period dates
22 --
23 SELECT start_date,
24 end_date
25 INTO l_period_start,
26 l_period_end
27 FROM per_time_periods
28 WHERE payroll_id = p_payroll_id
29 AND p_date_earned BETWEEN start_date AND end_date;
30 --
31 if g_debug then
32 hr_utility.set_location('consumed_entry spl', 20);
33 end if;
34 --
35 SELECT DECODE(COUNT(PRR.run_result_id), 0, 'N', 'Y')
36 INTO v_consumed
37 FROM pay_run_results PRR,
38 pay_assignment_actions ASA,
39 pay_payroll_actions PPA
40 WHERE
41 ASA.assignment_id = p_asg_id
42 AND PRR.source_type IN ('E', 'I')
43 AND PRR.status in ('P', 'PA', 'R', 'O')
44 AND ASA.assignment_action_id = PRR.assignment_action_id
45 AND PPA.payroll_action_id = ASA.payroll_action_id
46 --
47 -- Check whether the run_result has been revered.
48 AND not exists (select null
49 from pay_run_results prr2
50 where prr2.source_id = PRR.run_result_id
51 and prr2.source_type in ('R', 'V'))
52 AND PPA.date_earned BETWEEN l_period_start
53 AND l_period_end;
54 --
55 if g_debug then
56 hr_utility.set_location('consumed_entry spl', 39);
57 end if;
58
59 RETURN v_consumed;
60
61 exception when NO_DATA_FOUND then
62 if g_debug then
63 hr_utility.set_location('consumed_entry spl', 40);
64 end if;
65 RETURN v_consumed;
66 --
67 END consumed_entry_spl;
68
69 FUNCTION consumed_entry_indirect ( p_date_earned IN DATE,
70 p_payroll_id IN NUMBER,
71 p_ele_entry_id IN NUMBER
72 ) RETURN VARCHAR2 IS
73
74 v_consumed VARCHAR2(1) := 'N';
75 v_reversed VARCHAR2(1) := 'N';
76 l_period_start DATE;
77 l_period_end DATE;
78 g_debug boolean := hr_utility.debug_enabled;
79 --
80 BEGIN
81 --
82 if g_debug then
83 hr_utility.set_location('consumed_entry', 10);
84 end if;
85 --
86 -- Find EARNED period dates
87 --
88 SELECT start_date,
89 end_date
90 INTO l_period_start,
91 l_period_end
92 FROM per_time_periods
93 WHERE payroll_id = p_payroll_id
94 AND p_date_earned BETWEEN start_date AND end_date;
95 --
96 if g_debug then
97 hr_utility.set_location('consumed_entry', 20);
98 end if;
99 --
100 /*
101 SELECT DECODE(COUNT(PRR.run_result_id), 0, 'N', 'Y')
102 INTO v_consumed
103 FROM pay_run_results PRR,
104 pay_assignment_actions ASA,
105 pay_payroll_actions PPA
106 WHERE PRR.source_id = p_ele_entry_id
107 AND PRR.source_type IN ('E', 'I')
108 AND PRR.status in ('P', 'PA', 'R', 'O')
109 AND ASA.assignment_action_id = PRR.assignment_action_id
110 AND PPA.payroll_action_id = ASA.payroll_action_id
111 --
112 -- Check whether the run_result has been revered.
113 AND not exists (select null
114 from pay_run_results prr2
115 where prr2.source_id = PRR.run_result_id
116 and prr2.source_type in ('R', 'V'))
117 AND PPA.date_earned BETWEEN l_period_start
118 AND l_period_end;
119 */
120 select DECODE(COUNT(PRR.run_result_id), 0, 'N', 'Y')
121 INTO v_consumed
122 FROM pay_run_results PRR,
123 pay_assignment_actions ASA,
124 pay_payroll_actions PPA,
125 pay_run_result_values PRVV
126 ,pay_input_values_f PIVF
127 ,pay_element_types_f PET
128 WHERE PRR.source_id = p_ele_entry_id
129 AND PRR.source_type IN ('E', 'I')
130 AND PRR.status in ('P', 'PA', 'R', 'O')
131 AND PRR.run_result_id=PRVV.run_result_id
132 AND ASA.assignment_action_id = PRR.assignment_action_id
133 AND PPA.payroll_action_id = ASA.payroll_action_id
134 AND pivf.name in ('Termination Accrual Days','Termination Next Entit Days',
135 'Termination Curr Entit Days','Termination Accrual Amount',
136 'Termination Next Entit Pay','Termination Curr Entit Pay')
137 AND pivf.input_Value_id=prvv.input_value_id
138 AND pivf.element_type_id=PRR.element_type_id
139 AND PET.element_name='Holiday Bank Information'
140 AND PET.element_type_id=PIVF.element_type_id
141 AND not exists (select null
142 from pay_run_results prr2
143 where prr2.source_id = PRR.run_result_id
144 and prr2.source_type in ('R', 'V'))
145 AND PPA.date_earned BETWEEN l_period_start
146 AND l_period_end;
147 --
148 if g_debug then
149 hr_utility.set_location('consumed_entry', 39);
150 end if;
151
152 RETURN v_consumed;
153
154 exception when NO_DATA_FOUND then
155 if g_debug then
156 hr_utility.set_location('consumed_entry', 40);
157 end if;
158 RETURN v_consumed;
159 --
160 END consumed_entry_indirect;
161
162 END DK_CONSUMED_ENTRY;
[Home] [Help]