1 PACKAGE BODY PAY_HK_AVG_PAY AS
2 /* $Header: pyhkavgpay.pkb 120.0 2007/12/14 09:04:14 vamittal noship $ */
3
4 -- Package Variable
5 g_debug boolean;
6 /* Bug 6318006 This function finds the time period start date for dimension _ASG_12MTHS_PREV */
7
8 FUNCTION TIME_START
9 (
10 p_effective_date IN DATE
11 ,p_assignment_action_id IN NUMBER
12 ) RETURN DATE IS
13
14 l_start_date DATE := NULL ;
15 l_date_earned DATE;
16 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
17
18 CURSOR get_date_earned
19 IS
20 SELECT ppa.date_earned,paa.assignment_id
21 FROM
22 pay_payroll_actions ppa,
23 pay_assignment_actions paa
24 WHERE paa.assignment_action_id = p_assignment_action_id
25 AND ppa.payroll_action_id=paa.payroll_action_id;
26
27 BEGIN
28
29 g_debug := hr_utility.debug_enabled;
30 IF g_debug THEN
31 hr_utility.set_location('Entering TIME_START',1);
32 hr_utility.set_location('In Parameter p_effective_date '||p_effective_date,1);
33 hr_utility.set_location('In Parameter p_assignment_action_id '||p_assignment_action_id ,1);
34 END if;
35
36 OPEN get_date_earned;
37 FETCH get_date_earned INTO l_date_earned,l_assignment_id;
38 CLOSE get_date_earned;
39
40 IF g_debug THEN
41 hr_utility.set_location('After Cursor get_date_earned',1);
42 hr_utility.set_location('l_date_earned '||l_date_earned,1);
43 hr_utility.set_location('l_assignment_id '||l_assignment_id ,1);
44 END if;
45
46 /* To fetch the start_date from absence element*/
47 l_start_date := specified_date_absence(l_date_earned,l_assignment_id);
48 IF l_start_date IS NULL
49 THEN
50 /* If absence is not present then fetch the start_date from Specified Date element*/
51 l_start_date := specified_date_element(l_date_earned,l_assignment_id);
52 IF l_start_date IS NULL
53 THEN
54 /* If absence is not present then consider start_date as the effectice_date */
55 l_start_date := l_date_earned;
56
57 END IF;
58 END IF;
59
60
61 l_start_date := add_months(l_start_date,-13);
62 l_start_date := last_day(l_start_date) + 1;
63
64 IF g_debug THEN
65 hr_utility.set_location('Retruned specified Date '||l_start_date,1);
66 END if;
67
68 RETURN l_start_date;
69
70 END TIME_START;
71
72 /* Bug 6318006
73 This Function will fetch the specified date from element entry of absence element for dimension _ASG_12MTHS_PREV */
74
75 Function SPECIFIED_DATE_ABSENCE
76 ( p_date_earned IN DATE
77 ,p_assignment_id IN NUMBER
78 ) RETURN DATE IS
79
80 l_start_date DATE := NULL ;
81
82 CURSOR csr_get_specified_date
83 IS
84 SELECT min(pea.date_start)
85 FROM
86 per_all_assignments_f paa,
87 per_absence_attendances pea,
88 pay_element_entries_f pee
89 WHERE pee.assignment_id=p_assignment_id
90 AND paa.assignment_id=pee.assignment_id
91 AND pea.person_id = paa.person_id
92 AND pee.creator_type='A'
93 AND pee.creator_id = pea.absence_attendance_id
94 AND p_date_earned between pee.effective_start_date and pee.effective_end_date
95 AND p_date_earned between paa.effective_start_date and paa.effective_end_date;
96
97 BEGIN
98
99 g_debug := hr_utility.debug_enabled;
100 IF g_debug THEN
101 hr_utility.set_location('Entering SPECIFIED_DATE_ABSENCE',1);
102 hr_utility.set_location('In Parameter p_date_earned '||p_date_earned,1);
103 hr_utility.set_location('In Parameter p_assignment_id '||p_assignment_id ,1);
104 END if;
105
106 OPEN csr_get_specified_date;
107 FETCH csr_get_specified_date INTO l_start_date;
108 CLOSE csr_get_specified_date;
109
110 IF g_debug THEN
111 hr_utility.set_location('Retruned specified Date '||l_start_date,1);
112 END if;
113
114 RETURN l_start_date;
115
116 END SPECIFIED_DATE_ABSENCE;
117
118 /* Bug 6318006
119 This Function will fetch the specified date from element entry of Specified Date Element for dimension _ASG_12MTHS_PREV */
120
121 Function SPECIFIED_DATE_ELEMENT
122 ( p_date_earned IN DATE
123 ,p_assignment_id IN NUMBER
124 ) RETURN DATE IS
125
126 l_start_date DATE := NULL ;
127
128 CURSOR csr_get_specified_date
129 IS
130 SELECT fnd_date.canonical_to_date(peev.screen_entry_value)
131 FROM
132 pay_element_entries_f pee,
133 pay_element_entry_values_f peev,
134 pay_element_types_f pet,
135 pay_input_values_f pivf
136 WHERE pee.assignment_id=p_assignment_id
137 AND pet.element_name='Specified Date'
138 AND pee.element_type_id=pet.element_type_id
139 AND pivf.element_type_id = pet.element_type_id
140 AND pivf.name = 'Specified Date'
141 AND pee.element_entry_id=peev.element_entry_id
142 AND peev.input_value_id=pivf.input_value_id
143 AND p_date_earned between pee.effective_start_date and pee.effective_end_date
144 AND p_date_earned between peev.effective_start_date and peev.effective_end_date
145 AND p_date_earned between pet.effective_start_date and pet.effective_end_date
146 AND p_date_earned between pivf.effective_start_date and pivf.effective_end_date;
147
148 BEGIN
149
150 g_debug := hr_utility.debug_enabled;
151 IF g_debug THEN
152 hr_utility.set_location('Entering SPECIFIED_DATE_ELEMENT',1);
153 hr_utility.set_location('In Parameter p_date_earned '||p_date_earned,1);
154 hr_utility.set_location('In Parameter p_assignment_id '||p_assignment_id ,1);
155 END if;
156
157 OPEN csr_get_specified_date;
158 FETCH csr_get_specified_date INTO l_start_date;
159 CLOSE csr_get_specified_date;
160
161 IF g_debug THEN
162 hr_utility.set_location('Retruned specified Date '||l_start_date,1);
163 END if;
164
165 RETURN l_start_date;
166
167 END SPECIFIED_DATE_ELEMENT;
168
169 /* Bug 6318006
170 This Function will give the total number of days for dimension _ASG_12MTHS_PREV */
171
172 Function NO_OF_DAYS
173 ( p_assignment_action_id IN NUMBER
174 ) RETURN NUMBER IS
175
176 l_specified_date DATE := NULL;
177 l_start_date DATE := NULL ;
178 l_end_date DATE := NULL ;
179 l_date_earned DATE;
180 l_effective_date DATE;
181 l_hire_date DATE;
182 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
183 l_no_of_days NUMBER;
184
185 CURSOR get_date_earned
186 IS
187 SELECT ppa.date_earned,paa.assignment_id,ppa.effective_date
188 FROM
189 pay_payroll_actions ppa,
190 pay_assignment_actions paa
191 WHERE paa.assignment_action_id = p_assignment_action_id
192 AND ppa.payroll_action_id=paa.payroll_action_id;
193
194 CURSOR csr_hire_date(p_assignment_id in number)
195 IS
196 SELECT pps.date_start
197 FROM per_periods_of_service pps,
198 per_people_f ppf,
199 per_assignments_f paf
200 WHERE paf.person_id = ppf.person_id
201 AND pps.person_id = paf.person_id
202 AND paf.assignment_id = p_assignment_id
203 AND paf.period_of_service_id = pps.period_of_service_id;
204
205
206 BEGIN
207
208
209 g_debug := hr_utility.debug_enabled;
210 IF g_debug THEN
211 hr_utility.set_location('Entering NO_OF_DAYS',1);
212 hr_utility.set_location('In Parameter p_assignment_action_id '||p_assignment_action_id,1);
213 END if;
214
215 OPEN get_date_earned;
216 FETCH get_date_earned INTO l_date_earned,l_assignment_id,l_effective_date;
217 CLOSE get_date_earned;
218
219
220 IF g_debug THEN
221 hr_utility.set_location('After Cursor get_date_earned',1);
222 hr_utility.set_location('l_date_earned '||l_date_earned,1);
223 hr_utility.set_location('l_assignment_id '||l_assignment_id ,1);
224 hr_utility.set_location('l_effective_date '||l_effective_date ,1);
225 END if;
226
227 /* To fetch the start_date from absence element*/
228 l_specified_date := specified_date_absence(l_date_earned,l_assignment_id);
229 IF l_specified_date IS NULL
230 THEN
231 /* If absence is not present then fetch the start_date from Specified Date element*/
232 l_specified_date := specified_date_element(l_date_earned,l_assignment_id);
233 IF l_specified_date IS NULL
234 THEN
235 /* If absence is not present then consider start_date as the effectice_date */
236 l_specified_date := l_date_earned;
237
238 END IF;
239 END IF;
240
241 /* to get the hire date */
242
243 OPEN csr_hire_date(l_assignment_id);
244 FETCH csr_hire_date into l_hire_date;
245 CLOSE csr_hire_date;
246
247 IF g_debug THEN
248 hr_utility.set_location('After Cursor csr_hire_date',1);
249 hr_utility.set_location('l_hire_date '||l_hire_date,1);
250 END if;
251
252 l_start_date := last_day(add_months(l_specified_date,-13))+1;
253 l_end_date := last_day(add_months(l_specified_date,-1));
254
255 IF g_debug THEN
256 hr_utility.set_location('l_start_date '||l_start_date,1);
257 hr_utility.set_location('l_end_date '||l_end_date,1);
258 END if;
259
260
261 /*If hire_date is more then l_start_date */
262 IF l_start_date < l_hire_date
263 THEN
264 l_start_date := l_hire_date;
265 END IF;
266
267 l_no_of_days := trunc (l_end_date - l_start_date + 1);
268
269 IF g_debug THEN
270 hr_utility.set_location('Retruned l_no_of_days '||l_no_of_days,1);
271 END if;
272
273 RETURN l_no_of_days;
274
275
276 END NO_OF_DAYS;
277
278 END PAY_HK_AVG_PAY;
[Home] [Help]