1 PACKAGE BODY PAY_FI_EXPIRY_SUPPORT AS
2 /* $Header: pyfiepst.pkb 120.5 2006/03/14 01:13:37 dbehera noship $ */
3 --
4 --
5 -- --------------------------------------------------------------------------
6 -- Returns the start of the next calendar month span relative to the effective
7 -- date.
8 -- --------------------------------------------------------------------------
9 --
10 FUNCTION month_ec
11 (p_effective_date DATE) RETURN DATE IS
12 BEGIN
13 RETURN TRUNC(ADD_MONTHS(p_effective_date, -1), 'MM');
14 END month_ec;
15 --
16 --
17 -- --------------------------------------------------------------------------
18 -- Returns the start of the next calendar quarter span relative to the effective
19 -- date.
20 -- --------------------------------------------------------------------------
21 --
22 FUNCTION quarter_ec
23 (p_effective_date DATE) RETURN DATE IS
24 BEGIN
25 RETURN TRUNC(ADD_MONTHS(p_effective_date, -3), 'Q');
26 END quarter_ec;
27 --
28 --
29 -- --------------------------------------------------------------------------
30 -- Returns the start of the next calendar year span relative to the effective
31 -- date.
32 -- --------------------------------------------------------------------------
33 --
34 FUNCTION year_ec
35 (p_effective_date DATE) RETURN DATE IS
36 BEGIN
37 RETURN TRUNC(ADD_MONTHS(p_effective_date, -12), 'Y');
38 END year_ec;
39 --
40 --
41 -- --------------------------------------------------------------------------
42 -- Returns the start of the next calendar year span relative to the effective
43 -- date.
44 -- --------------------------------------------------------------------------
45 --
46 FUNCTION period_ec
47 (p_owner_payroll_action_id NUMBER
48 ,p_owner_effective_date DATE) RETURN DATE IS
49 --
50 --
51 -- Local variables.
52 --
53 l_period_start_date DATE;
54 BEGIN
55 --
56 --
57 -- If the time periods are not the same for the two payroll actions then we need to expire
58 -- the latest balance NB. returning an expiry date matching p_user_effective_date will
59 -- result in the expiration of the balance.
60 --
61 SELECT TP.start_date
62 INTO l_period_start_date
63 FROM per_time_periods TP
64 ,pay_payroll_actions PACT
65 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
66 AND PACT.payroll_id = TP.payroll_id
67 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
68 --
69 return l_period_start_date - 1;
70 END period_ec;
71
72 -- --------------------------------------------------------------------------
73 -- Returns the start of the next holiday year span relative to the effective
74 -- date.
75 -- --------------------------------------------------------------------------
76 --
77 FUNCTION hyear_ec
78 (p_effective_date DATE) RETURN DATE IS
79 l_date date;
80 BEGIN
81 SELECT TO_DATE(decode(sign(to_number(to_char(p_effective_date,'MM'))-3),1,TO_DATE('01/04/'||(to_char(p_effective_date,'YYYY') + 1) , 'DD/MM/YYYY') ,TO_DATE('01/04/'||(to_char(p_effective_date,'YYYY')) , 'DD/MM/YYYY') ) )
82 INTO l_date
83 FROM dual;
84
85 RETURN l_date;
86 END hyear_ec;
87 --
88 --
89 -- --------------------------------------------------------------------------
90 -- This is the procedure called by the core logic that manages the expiry of
91 -- latest balances. Its interface is fixed as it is called dynamically.
92 --
93 -- It will return the following output indicating the latest balance expiration
94 -- status ...
95 --
96 -- p_expiry_information = 1 - Expired
97 -- p_expiry_information = 0 - OK
98 -- --------------------------------------------------------------------------
99 --
100 PROCEDURE court_order_ec
101 (p_owner_payroll_action_id NUMBER
102 ,p_user_payroll_action_id NUMBER
103 ,p_owner_assignment_action_id NUMBER
104 ,p_user_assignment_action_id NUMBER
105 ,p_owner_effective_date DATE
106 ,p_user_effective_date DATE
107 ,p_dimension_name VARCHAR2
108 ,p_expiry_information OUT NOCOPY NUMBER) IS
109 --
110 --
111 -- Find the business group of the payroll action and also the period type of the
112 -- balance dimension.
113 --
114 CURSOR get_period_type(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
115 SELECT bd.period_type
116 ,pact.business_group_id
117 FROM pay_payroll_actions pact
118 ,hr_organization_information hoi
119 ,pay_balance_dimensions bd
120 WHERE pact.payroll_action_id = p_payroll_action_id
121 AND hoi.organization_id = pact.business_group_id
122 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
123 AND bd.dimension_name = p_dimension_name
124 AND bd.legislation_code = hoi.org_information9;
125
126
127 CURSOR get_element_entry_id(p_owner_assignment_action_id NUMBER, p_business_group_id NUMBER ,p_owner_effective_date DATE) is
128 select pee.element_entry_id
129 from pay_element_entries_f pee,
130 pay_element_types_f pet,
131 per_all_assignments_f paf1,
132 per_all_assignments_f paf2,
133 pay_assignment_actions paa1
134 where pet.element_name='Court Order Information'
135 and pet.legislation_code='FI'
136 and pet.element_type_id=pee.element_type_id
137 and paa1.assignment_action_id=p_owner_assignment_action_id
138 and paf1.business_group_id=p_business_group_id
139 and paf2.business_group_id=p_business_group_id
140 and paa1.assignment_id=paf1.assignment_id
141 and paf2.primary_flag='Y'
142 and paf1.person_id=paf2.person_id
143 and pee.assignment_id=paf2.assignment_id
144 and p_owner_effective_date between pee.effective_start_date
145 and pee.effective_end_date
146 and p_owner_effective_date between pet.effective_start_date
147 and pet.effective_end_date
148 and p_owner_effective_date between paf1.effective_start_date
149 and paf1.effective_end_date
150 and p_owner_effective_date between paf2.effective_start_date
151 and paf2.effective_end_date ;
152 -- Local variables.
153 --
154 l_rec get_period_type%ROWTYPE;
155 l_user_date_element_details get_element_entry_id%ROWTYPE;
156 l_owner_date_element_details get_element_entry_id%ROWTYPE;
157 l_owner_id NUMBER;
158 l_user_id NUMBER;
159
160 BEGIN
161 FND_FILE.PUT_LINE(FND_FILE.LOG, 'expdate'||to_char(p_user_effective_date)||to_char(p_owner_effective_date));
162
163 -- Find the business group and also the period type of the balance dimension.
164 --
165 OPEN get_period_type(p_owner_payroll_action_id, p_dimension_name);
166 FETCH get_period_type INTO l_rec;
167 CLOSE get_period_type;
168 --
169 --
170 -- Based on the period type of the balance dimension get the expiry date.
171 --
172 --IF l_rec.period_type = 'MONTH' THEN
173 --l_previous_period_date := month_ec(p_owner_effective_date);
174 --
175 --ELSIF l_rec.period_type = 'QUARTER' THEN
176 --l_previous_period_date := quarter_ec(p_owner_effective_date);
177 --
178 --ELSIF l_rec.period_type = 'YEAR' THEN
179 --l_previous_period_date := year_ec(p_owner_effective_date);
180 --
181 --for court it will always satisfy
182 --ELSIF l_rec.period_type = 'PERIOD' THEN
183 --l_previous_period_date := period_ec(p_owner_payroll_action_id, p_owner_effective_date);
184 --
185 --ELSIF l_rec.period_type = 'TYEAR' THEN
186 --l_previous_period_date := tyear_ec(p_owner_effective_date, l_rec.business_group_id);
187 --
188 --ELSIF l_rec.period_type = 'TQUARTER' THEN
189 --l_previous_period_date := tquarter_ec(p_owner_effective_date, l_rec.business_group_id);
190 --
191 --ELSIF l_rec.period_type = 'FYEAR' THEN
192 --l_previous_period_date := fyear_ec(p_owner_effective_date, l_rec.business_group_id);
193 --
194 --ELSIF l_rec.period_type = 'FQUARTER' THEN
195 --l_previous_period_date := fquarter_ec(p_owner_effective_date, l_rec.business_group_id);
196 --END IF;
197 --
198 --
199 --Check if previous period has the same element entry id ; If No then reset balances else do not reset
200 --
201 --
202 --previous period details
203 open get_element_entry_id(p_owner_assignment_action_id, l_rec.business_group_id ,p_user_effective_date);
204 fetch get_element_entry_id into l_user_date_element_details;
205 close get_element_entry_id;
206
207 l_user_id:=l_user_date_element_details.element_entry_id;
208 IF l_user_id is NULL THEN
209 l_user_id:=0;
210 END IF;
211 --current period details
212 open get_element_entry_id(p_owner_assignment_action_id , l_rec.business_group_id ,p_owner_effective_date);
213 fetch get_element_entry_id into l_owner_date_element_details;
214 close get_element_entry_id;
215 l_owner_id:=l_owner_date_element_details.element_entry_id;
216 IF l_owner_id is NULL THEN
217 l_owner_id:=0;
218 END IF;
219 --if there is a change in element entry id as well as the assignment is primary then balance should be reset
220 --primary flag check is present to ensure that resetting takes place for only one assignment of a person
221 IF(l_owner_id<>l_user_id) THEN --new element entry indicates new court order
222 P_expiry_information := 1; -- Expired!
223 ELSE
224 P_expiry_information := 0; -- OK!
225 END IF;
226
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 p_expiry_information := NULL;
231 END court_order_ec;
232
233 ------overloaded procedure which returns the actual expiry date
234
235 PROCEDURE court_order_ec
236 (p_owner_payroll_action_id NUMBER
237 ,p_user_payroll_action_id NUMBER
238 ,p_owner_assignment_action_id NUMBER
239 ,p_user_assignment_action_id NUMBER
240 ,p_owner_effective_date DATE
241 ,p_user_effective_date DATE
242 ,p_dimension_name VARCHAR2
243 ,p_expiry_information OUT NOCOPY DATE) IS
244 --
245 --
246 -- Find the business group of the payroll action and also the period type of the
247 -- balance dimension.
248 --
249 CURSOR get_period_type(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
250 SELECT bd.period_type
251 ,pact.business_group_id
252 FROM pay_payroll_actions pact
253 ,hr_organization_information hoi
254 ,pay_balance_dimensions bd
255 WHERE pact.payroll_action_id = p_payroll_action_id
256 AND hoi.organization_id = pact.business_group_id
257 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
258 AND bd.dimension_name = p_dimension_name
259 AND bd.legislation_code = hoi.org_information9;
260 --
261
262 --
263 CURSOR get_element_entry_id(p_owner_assignment_action_id NUMBER, p_business_group_id NUMBER ,p_owner_effective_date DATE) is
264 select pee.element_entry_id , pee.effective_end_date
265 from pay_element_entries_f pee,
266 pay_element_types_f pet,
267 per_all_assignments_f paf1,
268 per_all_assignments_f paf2,
269 pay_assignment_actions paa1
270 where pet.element_name='Court Order Information'
271 and pet.legislation_code='FI'
272 and pet.element_type_id=pee.element_type_id
273 and paa1.assignment_action_id=p_owner_assignment_action_id
274 and paf1.business_group_id=p_business_group_id
275 and paf2.business_group_id=p_business_group_id
276 and paa1.assignment_id=paf1.assignment_id
277 and paf2.primary_flag='Y'
278 and paf1.person_id=paf2.person_id
279 and pee.assignment_id=paf2.assignment_id
280 and p_owner_effective_date between pee.effective_start_date
281 and pee.effective_end_date
282 and p_owner_effective_date between pet.effective_start_date
283 and pet.effective_end_date
284 and p_owner_effective_date between paf1.effective_start_date
285 and paf1.effective_end_date
286 and p_owner_effective_date between paf2.effective_start_date
287 and paf2.effective_end_date ;
288
289
290 -- Local variables.
291 --
292 l_user_date_element_details get_element_entry_id%ROWTYPE;
293 l_owner_date_element_details get_element_entry_id%ROWTYPE;
294 l_owner_id NUMBER;
295 l_user_id NUMBER;
296 l_rec get_period_type%ROWTYPE;
297 l_expiry_date DATE;
298
299 BEGIN
300 --
301 --
302 -- Find the business group and also the period type of the balance dimension.
303 --
304 OPEN get_period_type(p_owner_payroll_action_id, p_dimension_name);
305 FETCH get_period_type INTO l_rec;
306 CLOSE get_period_type;
307
308 open get_element_entry_id(p_owner_assignment_action_id, l_rec.business_group_id , p_user_effective_date);
309 fetch get_element_entry_id into l_user_date_element_details;
310 close get_element_entry_id;
311
312 l_user_id:=l_user_date_element_details.element_entry_id;
313 IF l_user_id is NULL THEN
314 l_user_id:=0;
315 END IF;
316 --current period details
317 open get_element_entry_id(p_owner_assignment_action_id, l_rec.business_group_id , p_owner_effective_date);
318 fetch get_element_entry_id into l_owner_date_element_details;
319 close get_element_entry_id;
320 l_owner_id:=l_owner_date_element_details.element_entry_id;
321 IF l_owner_id is NULL THEN
322 l_owner_id:=0;
323 END IF;
324 --if there is a change in element entry id as well as the assignment is primary then balance should be reset
325 --primary flag check is present to ensure that resetting takes place for only one assignment of a person
326 IF(l_owner_id<>l_user_id) THEN --new element entry indicates new court order
327 P_expiry_information := l_owner_date_element_details.effective_end_date; -- Expired!
328 ELSE
329 P_expiry_information := l_user_date_element_details.effective_end_date; -- OK!
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 p_expiry_information := NULL;
335 END court_order_ec;
336
337 -- --------------------------------------------------------------------------
338 -- This is the procedure called by the core logic that manages the expiry of
339 -- latest balances. Its interface is fixed as it is called dynamically.
340 --
341 -- It will return the following output indicating the latest balance expiration
342 -- status ...
343 --
344 -- p_expiry_information = 1 - Expired
345 -- p_expiry_information = 0 - OK
346 -- --------------------------------------------------------------------------
347 --
348 PROCEDURE holiday_pay_ec
349 (p_owner_payroll_action_id NUMBER
350 ,p_user_payroll_action_id NUMBER
351 ,p_owner_assignment_action_id NUMBER
352 ,p_user_assignment_action_id NUMBER
353 ,p_owner_effective_date DATE
354 ,p_user_effective_date DATE
355 ,p_dimension_name VARCHAR2
356 ,p_expiry_information OUT NOCOPY NUMBER) IS
357 --
358 --
359 -- Find the business group of the payroll action and also the period type of the
360 -- balance dimension.
361 --
362 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
363 SELECT bd.period_type
364 ,pact.business_group_id
365 FROM pay_payroll_actions pact
366 ,hr_organization_information hoi
367 ,pay_balance_dimensions bd
368 WHERE pact.payroll_action_id = p_payroll_action_id
369 AND hoi.organization_id = pact.business_group_id
370 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
371 AND bd.dimension_name = p_dimension_name
372 AND bd.legislation_code = hoi.org_information9;
373 --
374 --
375 -- Local variables.
376 --
377 l_rec csr_info%ROWTYPE;
378 l_expiry_date DATE;
379 BEGIN
380 hr_utility.trace(' In p_owner_payroll_action_id => ' || p_owner_payroll_action_id );
381 hr_utility.trace(' In p_user_payroll_action_id => ' || p_user_payroll_action_id );
382 hr_utility.trace(' In p_owner_assignment_action_id => ' || p_owner_assignment_action_id);
383 hr_utility.trace(' In p_user_assignment_action_id => ' || p_user_assignment_action_id );
384 hr_utility.trace(' In p_owner_effective_date => ' || p_owner_effective_date);
385 hr_utility.trace(' p_user_effective_date => ' || p_user_effective_date );
386 hr_utility.trace(' In p_dimension_name => ' || p_dimension_name);
387
388 --
389 --
390 -- Find the business group and also the period type of the balance dimension.
391 --
392 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
393 FETCH csr_info INTO l_rec;
394 CLOSE csr_info;
395 --
396 --
397 -- Based on the period type of the balance dimension get the expiry date.
398 --
399 IF l_rec.period_type = 'HYEAR' THEN
400
401 l_expiry_date := hyear_ec(p_owner_effective_date);
402 --
403 END IF;
404 hr_utility.trace(' l_expiry_date => ' || l_expiry_date );
405 --
406 --
407 -- See if the current effective date is within the same span of time as the
408 -- balance's effective date. If yes then it is OK to use cached balance
409 -- otherwise the balance has expired.
410 --
411 IF p_user_effective_date >= l_expiry_date THEN
412 P_expiry_information := 1; -- Expired!
413 ELSE
414 P_expiry_information := 0; -- OK!
415 END IF;
416 /*
417 IF p_dimension_name ='Assignment Previous Holiday Year to Date' AND l_rec.period_type = 'HYEAR' THEN
418 P_expiry_information := 1; -- OK!
419 END IF;
420 */
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 p_expiry_information := NULL;
425 END holiday_pay_ec;
426 --
427 -- ----------------------------------------------------------------------------
428 -- This is the overloaded procedure which returns actual expiry date
429 -- ----------------------------------------------------------------------------
430 --
431 PROCEDURE holiday_pay_ec
432 (p_owner_payroll_action_id NUMBER
433 ,p_user_payroll_action_id NUMBER
434 ,p_owner_assignment_action_id NUMBER
435 ,p_user_assignment_action_id NUMBER
436 ,p_owner_effective_date DATE
437 ,p_user_effective_date DATE
438 ,p_dimension_name VARCHAR2
439 ,p_expiry_information OUT NOCOPY DATE) IS
440 --
441 --
442 -- Find the business group of the payroll action and also the period type of the
443 -- balance dimension.
444 --
445 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
446 SELECT bd.period_type
447 ,pact.business_group_id
448 FROM pay_payroll_actions pact
449 ,hr_organization_information hoi
450 ,pay_balance_dimensions bd
451 WHERE pact.payroll_action_id = p_payroll_action_id
452 AND hoi.organization_id = pact.business_group_id
453 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
454 AND bd.dimension_name = p_dimension_name
455 AND bd.legislation_code = hoi.org_information9;
456 --
457 --
458 -- Local variables.
459 --
460 l_rec csr_info%ROWTYPE;
461 BEGIN
462
463 --
464 --
465 hr_utility.trace(' In p_owner_payroll_action_id => ' || p_owner_payroll_action_id );
466 hr_utility.trace(' In p_user_payroll_action_id => ' || p_user_payroll_action_id );
467 hr_utility.trace(' In p_owner_assignment_action_id => ' || p_owner_assignment_action_id);
468 hr_utility.trace(' In p_user_assignment_action_id => ' || p_user_assignment_action_id );
469 hr_utility.trace(' In p_owner_effective_date => ' || p_owner_effective_date);
470 hr_utility.trace(' p_user_effective_date => ' || p_user_effective_date );
471 hr_utility.trace(' In p_dimension_name => ' || p_dimension_name);
472
473 -- Find the business group and also the period type of the balance dimension.
474 --
475 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
476 FETCH csr_info INTO l_rec;
477 CLOSE csr_info;
478 --
479 --
480 -- Based on the period type of the balance dimension get the expiry date.
481 --
482 IF l_rec.period_type = 'HYEAR' THEN
483
484 p_expiry_information := hyear_ec(p_owner_effective_date)-1;
485
486 /*
487 IF p_dimension_name ='Assignment Previous Holiday Year to Date' THEN
488
489 p_expiry_information := p_owner_effective_date - 36500;
490
494 END IF;
491 END IF;
492 */
493
495
496
497 hr_utility.trace(' p_expiry_information => ' || p_expiry_information);
498 --
499 --
500 -- See if the current effective date is within the same span of time as the
501 -- balance's effective date. If yes then it is OK to use cached balance
502 -- otherwise the balance has expired.
503 --
504 EXCEPTION
505 WHEN OTHERS THEN
506 p_expiry_information := NULL;
507 END holiday_pay_ec;
508
509 END pay_fi_expiry_support;
[Home] [Help]