1 PACKAGE BODY pqp_gb_absence_plan_process AS
2 /* $Header: pqgbabpr.pkb 120.0 2005/05/29 01:48:18 appldev noship $ */
3 --
4 --
5 --
6 g_package_name VARCHAR2(31)
7 := 'pqp_gb_absence_plan_process.';
8 g_debug BOOLEAN := hr_utility.debug_enabled;
9
10
11 -- Cache Variables
12 -- Cache for get_absence_pay_plan_category
13
14 g_gap_primary_element_type_id pay_element_types_f.element_type_id%TYPE;
15 g_plan csr_abs_plan_category_by_eid%ROWTYPE;
16
17 --
18 --
19 --
20 PROCEDURE debug(
21 p_trace_message IN VARCHAR2
22 ,p_trace_location IN NUMBER DEFAULT NULL
23 )
24 IS
25 BEGIN
26 pqp_utilities.debug(p_trace_message, p_trace_location);
27 END debug;
28
29 --
30 --
31 --
32 PROCEDURE debug(p_trace_number IN NUMBER)
33 IS
34 BEGIN
35 pqp_utilities.debug(fnd_number.number_to_canonical(p_trace_number));
36 END debug;
37
38 --
39 --
40 --
41 PROCEDURE debug(p_trace_date IN DATE)
42 IS
43 BEGIN
44 pqp_utilities.debug(fnd_date.date_to_canonical(p_trace_date));
45 END debug;
46
47 --
48 --
49 --
50 PROCEDURE debug_enter(
51 p_proc_name IN VARCHAR2
52 ,p_trace_on IN VARCHAR2 DEFAULT NULL
53 )
54 IS
55 -- l_trace_options VARCHAR2(200);
56 BEGIN
57 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
58 END debug_enter;
59
60 --
61 --
62 --
63 PROCEDURE debug_exit(
64 p_proc_name IN VARCHAR2
65 ,p_trace_off IN VARCHAR2 DEFAULT NULL
66 )
67 IS
68 BEGIN
69 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
70 END debug_exit;
71
72 --
73 --
74 --
75 PROCEDURE debug_others(
76 p_proc_name IN VARCHAR2
77 ,p_proc_step IN NUMBER DEFAULT NULL
78 )
79 IS
80 BEGIN
81 pqp_utilities.debug_others(p_proc_name, p_proc_step);
82 END debug_others;
83
84 --
85 --
86 --
87 PROCEDURE clear_cache
88 IS
89 BEGIN
90 g_gap_primary_element_type_id := NULL;
91 g_plan := NULL;
92 END;
93
94 --
95 --
96 --
97 FUNCTION get_absence_pay_plan_category(p_element_type_id IN NUMBER)
98 RETURN VARCHAR2
99 IS
100 l_plan csr_abs_plan_category_by_eid%ROWTYPE;
101 l_proc_step NUMBER(38, 10);
102 l_proc_name VARCHAR2(61):=
103 g_package_name||'get_absence_pay_plan_category';
104 BEGIN
105 IF g_debug
106 THEN
107 debug_enter(l_proc_name);
108 debug('Caching Check:g_gap_primary_element_type_id:'||
109 fnd_number.number_to_canonical(g_gap_primary_element_type_id));
110 debug('Caching Check:p_element_type_id:'||
111 fnd_number.number_to_canonical(p_element_type_id));
112 END IF;
113
114 IF g_gap_primary_element_type_id IS NULL
115 OR
116 g_gap_primary_element_type_id <> p_element_type_id
117 THEN
118
119 IF g_debug
120 THEN
121 l_proc_step := 20;
122 debug(l_proc_name, l_proc_step);
123 END IF;
124
125 -- DO NOT CLEAR THE CACHE Look at if found logic
126 --g_gap_primary_element_type_id:= NULL;
127 --g_plan := NULL;
128
129 OPEN csr_abs_plan_category_by_eid(p_element_type_id);
130 FETCH csr_abs_plan_category_by_eid INTO l_plan;
131 IF csr_abs_plan_category_by_eid%FOUND THEN
132 -- if some information has been found then its because this element
133 -- type id is a primary OSP element type. The fact the code is here
134 -- indicates that it is not the same primary element as the last
135 -- primary osp element cached or that this is the first call for a
136 -- any primary osp element.
137 -- In this case save the elment details and its associated plan category
138 -- in the cache for possible re-use in subsequent calls.
139 IF g_debug
140 THEN
141 l_proc_step := 24;
142 debug(l_proc_name, l_proc_step);
143 END IF;
144 g_gap_primary_element_type_id := p_element_type_id;
145 g_plan := l_plan;
146
147 ELSE
148 -- if csr_abs_plan_category_by_eid was not found then its because
149 -- this element type is either not a primary OSP element type or is
150 -- not a OSP element at all in which case we could either
151 -- destroy the cache and return the null plan category or not clear
152 -- the cache but still return a NULL category
153 -- In essence the cache is refreshed when a new OSP Primary element is
154 -- processed.
155 IF g_debug
156 THEN
157 l_proc_step := 28;
158 debug(l_proc_name, l_proc_step);
159 END IF;
160 l_plan.absence_pay_plan_category:= NULL;
161 END IF;
162 CLOSE csr_abs_plan_category_by_eid;
163
164 ELSE
165 -- this element is the same primary osp element as one
166 -- which has been previously processed in which
167 -- case do not fetch that information again return from
168 -- saved global g_plan
169 IF g_debug
170 THEN
171 l_proc_step := 30;
172 debug(l_proc_name, l_proc_step);
173 END IF;
174
175 l_plan := g_plan;
176 END IF;
177
178 IF g_debug
179 THEN
180 debug(l_plan.absence_pay_plan_category);
181 debug_exit(l_proc_name);
182 END IF;
183
184 RETURN l_plan.absence_pay_plan_category;
185
186 EXCEPTION
187 WHEN OTHERS
188 THEN
189 clear_cache;
190 IF SQLCODE <> hr_utility.hr_error_number
191 THEN
192 debug_others(l_proc_name, l_proc_step);
193 IF g_debug
194 THEN
195 debug('Leaving: ' || l_proc_name, -999);
196 END IF;
197 fnd_message.raise_error;
198 ELSE
199 RAISE;
200 END IF;
201 END get_absence_pay_plan_category;
202
203 --
204 --
205 --
206 PROCEDURE create_absence_plan_details(
207 p_person_id IN NUMBER
208 ,p_assignment_id IN NUMBER
209 ,p_business_group_id IN NUMBER
210 ,p_legislation_code IN VARCHAR2
211 ,p_effective_date IN DATE
212 ,p_element_type_id IN NUMBER
213 ,p_effective_start_date IN DATE
214 ,p_effective_end_date IN DATE
215 ,p_pl_id IN NUMBER
216 ,p_pl_typ_id IN NUMBER
217 ,p_ler_id IN NUMBER
218 ,p_per_in_ler_id IN NUMBER
219 ,p_absence_attendance_id IN NUMBER
220 ,p_absence_date_start IN DATE
221 ,p_absence_date_end IN DATE
222 ,p_formula_outputs IN ff_exec.outputs_t
223 ,p_error_code OUT NOCOPY NUMBER
224 ,p_error_message OUT NOCOPY VARCHAR2
225 )
226 IS
227 l_absence_pay_plan_category pay_element_type_extra_info.eei_information30%TYPE;
228 l_error_code fnd_new_messages.message_number%TYPE;
229 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
230 l_proc_step NUMBER(38,10);
231 l_proc_name VARCHAR2(61):=
232 g_package_name || 'create_absence_plan_details';
233
234 BEGIN
235 g_debug := hr_utility.debug_enabled;
236
237 IF g_debug
238 THEN
239 debug_enter(l_proc_name);
240 END IF;
241
242 l_absence_pay_plan_category :=
243 get_absence_pay_plan_category(p_element_type_id);
244
245 IF g_debug
246 THEN
247 debug('l_absence_pay_plan_category:' || l_absence_pay_plan_category);
248 END IF;
249
250 l_absence_pay_plan_category := UPPER(l_absence_pay_plan_category);
251
252 IF l_absence_pay_plan_category IN ('SICKNESS','UNPAID')
253 THEN
254 IF g_debug
255 THEN
256 l_proc_step := 20;
257 debug(l_proc_name, l_proc_step);
258 END IF;
259
260 pqp_absval_pkg.create_absence_plan_details(
261 p_assignment_id => p_assignment_id
262 ,p_person_id => p_person_id
263 ,p_business_group_id => p_business_group_id
264 ,p_absence_id => p_absence_attendance_id
265 ,p_absence_date_start => p_absence_date_start
266 ,p_absence_date_end => p_absence_date_end
267 ,p_pl_id => p_pl_id
268 ,p_pl_typ_id => p_pl_typ_id
269 ,p_element_type_id => p_element_type_id
270 ,p_create_start_date => p_effective_start_date
271 ,p_create_end_date => p_effective_end_date
272 ,p_output_type => p_formula_outputs
273 ,p_error_code => l_error_code
274 ,p_message => l_message
275 );
276 ELSIF l_absence_pay_plan_category = 'MATERNITY'
277 THEN
278 IF g_debug
279 THEN
280 l_proc_step := 30;
281 debug(l_proc_name, l_proc_step);
282 END IF;
283
284 pqp_gb_omp_daily_absences.create_absence_plan_details(
285 p_assignment_id => p_assignment_id
286 ,p_person_id => p_person_id
287 ,p_business_group_id => p_business_group_id
288 ,p_absence_id => p_absence_attendance_id
289 ,p_absence_date_start => p_absence_date_start
290 ,p_absence_date_end => p_absence_date_end
291 ,p_pl_id => p_pl_id
292 ,p_pl_typ_id => p_pl_typ_id
293 ,p_element_type_id => p_element_type_id
294 ,p_create_start_date => p_effective_start_date
295 ,p_create_end_date => p_effective_end_date
296 ,p_output_type => p_formula_outputs
297 ,p_error_code => l_error_code
298 ,p_message => l_message
299 );
300 ELSE
301 IF g_debug
302 THEN
303 debug('!');
304 l_proc_step := 40;
305 debug(l_proc_name, l_proc_step);
306 END IF;
307 END IF;
308
309 --p_error_code := l_error_code;
310 --p_error_message := l_message;
311
312 IF g_debug
313 THEN
314 debug_exit(l_proc_name);
315 END IF;
316 EXCEPTION
317 WHEN OTHERS
318 THEN
319 clear_cache;
320 IF SQLCODE <> hr_utility.hr_error_number
321 THEN
322 debug_others(l_proc_name, l_proc_step);
323
324 IF g_debug
325 THEN
326 debug('Leaving: ' || l_proc_name, -999);
327 END IF;
328 fnd_message.raise_error;
329 ELSE
330 RAISE;
331 END IF;
332 END create_absence_plan_details;
333
334 --
335 --
336 --
337 PROCEDURE update_absence_plan_details(
338 p_person_id IN NUMBER
339 ,p_assignment_id IN NUMBER
340 ,p_business_group_id IN NUMBER
341 ,p_legislation_code IN VARCHAR2
342 ,p_effective_date IN DATE
343 ,p_element_type_id IN NUMBER
344 ,p_effective_start_date IN DATE
345 ,p_effective_end_date IN DATE
346 ,p_pl_id IN NUMBER
347 ,p_pl_typ_id IN NUMBER
348 ,p_ler_id IN NUMBER
349 ,p_per_in_ler_id IN NUMBER
350 ,p_absence_attendance_id IN NUMBER
351 ,p_absence_date_start IN DATE
352 ,p_absence_date_end IN DATE
353 ,p_formula_outputs IN ff_exec.outputs_t
354 ,p_error_code OUT NOCOPY NUMBER
355 ,p_error_message OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_absence_pay_plan_category pay_element_type_extra_info.eei_information30%TYPE;
359 l_error_code fnd_new_messages.message_number%TYPE;
360 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
361 l_proc_step NUMBER(20, 10);
362 l_proc_name VARCHAR2(61)
363 := g_package_name || 'update_absence_plan_details';
364 BEGIN
365 g_debug := hr_utility.debug_enabled;
366
367 IF g_debug
368 THEN
369 debug_enter(l_proc_name);
370 END IF;
371
372 l_absence_pay_plan_category :=
373 get_absence_pay_plan_category(p_element_type_id);
374
375 IF g_debug
376 THEN
377 debug('l_absence_pay_plan_category:' || l_absence_pay_plan_category);
378 END IF;
379
380 l_absence_pay_plan_category := UPPER(l_absence_pay_plan_category);
381
382 IF l_absence_pay_plan_category IN ('SICKNESS','UNPAID')
383 THEN
384 IF g_debug
385 THEN
386 l_proc_step := 20;
387 debug(l_proc_name, l_proc_step);
388 END IF;
389
390 pqp_absval_pkg.update_absence_plan_details(
391 p_assignment_id => p_assignment_id
392 ,p_person_id => p_person_id
393 ,p_business_group_id => p_business_group_id
394 ,p_absence_id => p_absence_attendance_id
395 ,p_absence_date_start => p_absence_date_start
396 ,p_absence_date_end => p_absence_date_end
397 ,p_pl_id => p_pl_id
398 ,p_pl_typ_id => p_pl_typ_id
399 ,p_element_type_id => p_element_type_id
400 ,p_update_start_date => p_effective_start_date
401 ,p_update_end_date => p_effective_end_date
402 ,p_output_type => p_formula_outputs
403 ,p_error_code => l_error_code
404 ,p_message => l_message
405 );
406 ELSIF l_absence_pay_plan_category = 'MATERNITY'
407 THEN
408 IF g_debug
409 THEN
410 l_proc_step := 30;
411 debug(l_proc_name, l_proc_step);
412 END IF;
413
414 pqp_gb_omp_daily_absences.update_absence_plan_details(
415 p_assignment_id => p_assignment_id
416 ,p_person_id => p_person_id
417 ,p_business_group_id => p_business_group_id
418 ,p_absence_id => p_absence_attendance_id
419 ,p_absence_date_start => p_absence_date_start
420 ,p_absence_date_end => p_absence_date_end
421 ,p_pl_id => p_pl_id
422 ,p_pl_typ_id => p_pl_typ_id
423 ,p_element_type_id => p_element_type_id
424 ,p_update_start_date => p_effective_start_date
425 ,p_update_end_date => p_effective_end_date
426 ,p_output_type => p_formula_outputs
427 ,p_error_code => l_error_code
428 ,p_message => l_message
429 );
430 ELSE
431 IF g_debug
432 THEN
433 debug('!');
434 l_proc_step := 40;
435 debug(l_proc_name, l_proc_step);
436 END IF;
437 END IF;
438
439 --p_error_code := l_error_code;
440 --p_error_message := l_message;
441
442 IF g_debug
443 THEN
444 debug_exit(l_proc_name);
445 END IF;
446 EXCEPTION
447 WHEN OTHERS
448 THEN
449 clear_cache;
450 IF SQLCODE <> hr_utility.hr_error_number
451 THEN
452 debug_others(l_proc_name, l_proc_step);
453
454 IF g_debug
455 THEN
456 debug('Leaving: ' || l_proc_name, -999);
457 END IF;
458
459 fnd_message.raise_error;
460 ELSE
461 RAISE;
462 END IF;
463 END update_absence_plan_details;
464
465 --
466 --
467 --
468 PROCEDURE delete_absence_plan_details(
469 p_assignment_id IN NUMBER
470 ,p_business_group_id IN NUMBER
471 ,p_legislation_code IN VARCHAR2
472 ,p_effective_date IN DATE
473 ,p_pl_id IN NUMBER
474 ,p_pl_typ_id IN NUMBER
475 ,p_ler_id IN NUMBER
476 ,p_per_in_ler_id IN NUMBER
477 ,p_absence_attendance_id IN NUMBER
478 ,p_effective_start_date IN DATE
479 ,p_effective_end_date IN DATE
480 ,p_formula_outputs IN ff_exec.outputs_t
481 ,p_error_code OUT NOCOPY NUMBER
482 ,p_error_message OUT NOCOPY VARCHAR2
483 ,p_element_type_id IN NUMBER DEFAULT NULL
484 )
485 IS
486 l_absence_pay_plan_category pay_element_type_extra_info.eei_information30%TYPE;
487 l_error_code fnd_new_messages.message_number%TYPE;
488 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
489 l_proc_step NUMBER(20, 10);
490 l_proc_name VARCHAR2(61)
491 := g_package_name || 'delete_absence_plan_details';
492 BEGIN
493 IF g_debug
494 THEN
495 debug_enter(l_proc_name);
496 END IF;
497
498 IF p_element_type_id IS NOT NULL THEN -- temp code remove once ben gives changes
499
500 l_absence_pay_plan_category :=
501 get_absence_pay_plan_category(p_element_type_id);
502 -- no point in checking by plan id
503 END IF;
504
505 IF g_debug
506 THEN
507 debug('l_absence_pay_plan_category:' || l_absence_pay_plan_category);
508 END IF;
509
510 l_absence_pay_plan_category := UPPER(l_absence_pay_plan_category);
511
512 IF l_absence_pay_plan_category IN('SICKNESS', 'MATERNITY','UNPAID')
513 OR
514 p_element_type_id IS NULL -- temp code till ben gives new benelmen
515 THEN
516 IF g_debug
517 THEN
518 l_proc_step := 20;
519 debug(l_proc_name, l_proc_step);
520 END IF;
521
522 pqp_absval_pkg.delete_absence_plan_details(
523 p_assignment_id => p_assignment_id
524 ,p_business_group_id => p_business_group_id
525 ,p_plan_id => p_pl_id
526 ,p_absence_id => p_absence_attendance_id
527 ,p_delete_start_date => p_effective_start_date
528 ,p_delete_end_date => p_effective_end_date
529 ,p_error_code => l_error_code
530 ,p_message => l_message
531 );
532
533 ELSE
534
535 IF g_debug
536 THEN
537 debug('!');
538 l_proc_step := 30;
539 debug(l_proc_name, l_proc_step);
540 END IF;
541
542 END IF; -- IF UPPER(l_absence_pay_plan_category) IN ('SICKNESS','MATERNITY')
543
544 p_error_code := l_error_code;
545 p_error_message := l_message;
546
547 -- Added by tmehra for nocopy changes Feb'03
548
549 IF g_debug
550 THEN
551 debug_exit(l_proc_name);
552 END IF;
553 EXCEPTION
554 WHEN OTHERS
555 THEN
556 clear_cache;
557 IF SQLCODE <> hr_utility.hr_error_number
558 THEN
559 debug_others(l_proc_name, l_proc_step);
560
561 IF g_debug
562 THEN
563 debug('Leaving: ' || l_proc_name, -999);
564 END IF;
565
566 fnd_message.raise_error;
567 ELSE
568 RAISE;
569 END IF;
570 END delete_absence_plan_details;
571 END;
[Home] [Help]