1 PACKAGE BODY pqp_gb_manage_life_events AS
2 /* $Header: pqpgbmle.pkb 120.4 2010/01/08 17:44:01 vaibgupt ship $ */
3 --
4 --
5 --
6 g_package_name VARCHAR2(31):= 'pqp_gb_manage_life_events.';
7 g_debug BOOLEAN:= hr_utility.debug_enabled;
8 g_nested_level NUMBER:= 0;
9
10 hr_application_error EXCEPTION;
11 PRAGMA EXCEPTION_INIT(hr_application_error, -20001);
12
13
14 PROCEDURE debug
15 (p_trace_message IN VARCHAR2
16 ,p_trace_location IN NUMBER DEFAULT NULL
17 )
18 IS
19 l_padding VARCHAR2(12);
20 l_MAX_MESSAGE_LENGTH NUMBER:= 72;
21 BEGIN
22
23 IF p_trace_location IS NOT NULL THEN
24
25 -- l_padding := SUBSTR
26 -- (RPAD(' ',LEAST(g_nested_level,5)*2,' ')
27 -- ,1,l_MAX_MESSAGE_LENGTH
28 -- - LEAST(LENGTH(p_trace_message)
29 -- ,l_MAX_MESSAGE_LENGTH)
30 -- );
31
32 hr_utility.set_location
33 (--l_padding||
34 SUBSTR(p_trace_message
35 ,GREATEST(-LENGTH(p_trace_message),-l_MAX_MESSAGE_LENGTH))
36 ,p_trace_location);
37
38 ELSE
39
40 hr_utility.trace(SUBSTR(p_trace_message,1,250));
41
42 END IF;
43
44 END debug;
45 --
46 --
47 --
48 PROCEDURE debug
49 (p_trace_number IN NUMBER )
50 IS
51 BEGIN
52 debug(fnd_number.number_to_canonical(p_trace_number));
53 END debug;
54 --
55 --
56 --
57 PROCEDURE debug
58 (p_trace_date IN DATE )
59 IS
60 BEGIN
61 debug(fnd_date.date_to_canonical(p_trace_date));
62 END debug;
63 --
64 --
65 --
66 PROCEDURE debug_enter
67 (p_proc_name IN VARCHAR2
68 ,p_trace_on IN VARCHAR2 DEFAULT NULL
69 )
70 IS
71
72 BEGIN
73
74 g_nested_level := g_nested_level + 1;
75 debug('Entering: '||NVL(p_proc_name,g_package_name),g_nested_level*100);
76
77 END debug_enter;
78 --
79 --
80 --
81 PROCEDURE debug_exit
82 (p_proc_name IN VARCHAR2
83 ,p_trace_off IN VARCHAR2 DEFAULT NULL
84 )
85 IS
86 BEGIN
87
88 debug('Leaving: '||NVL(p_proc_name,g_package_name),-g_nested_level*100);
89 g_nested_level := g_nested_level - 1;
90
91 END debug_exit;
92 --
93 --
94 --
95 PROCEDURE abse_process
96 (p_business_group_id IN NUMBER
97 ,p_person_id IN NUMBER
98 ,p_effective_date IN DATE
99 ,p_absence_attendance_id IN NUMBER -- DEFAULT NULL
100 ,p_absence_start_date IN DATE -- DEFAULT NULL
101 ,p_absence_end_date IN DATE -- DEFAULT NULL
102 ,p_errbuf OUT NOCOPY VARCHAR2
103 ,p_retcode OUT NOCOPY NUMBER
104 )
105 IS
106
107 l_after_run_last_ben_report csr_last_ben_report%ROWTYPE;
108 l_before_run_last_ben_report csr_last_ben_report%ROWTYPE;
109
110 l_benmngle_batch_parameter csr_benmngle_batch_parameter%ROWTYPE;
111 l_ben_batch_parameter_exists BOOLEAN;
112
113 l_proc_step NUMBER(38,10):= 0;
114 l_proc_name VARCHAR2(61):= g_package_name||'abse_process';
115
116 l_plsql_block VARCHAR2(5000);
117
118 l_commit_data VARCHAR2(10) := 'Y';
119 l_mode VARCHAR2(10) := 'M'; -- Absences, Lookup Type BEN_BENMNGLE_MD
120
121 l_audit_log_flag VARCHAR2(10) := 'Y';
122
123 l_effective_date_canonical VARCHAR2(30);
124 l_effective_date DATE;
125
126
127 l_error_code fnd_new_messages.message_number%TYPE;
128 l_error_message ben_reporting.text%TYPE;
129
130 PROCEDURE del_or_upd_ben_batch_parameter
131 (p_ben_batch_parameter_exists IN BOOLEAN
132 ,p_batch_parameter_id IN NUMBER
133 ,p_max_err_num IN NUMBER
134 )
135 IS
136
137 l_proc_step NUMBER(38,10);
138 l_proc_name VARCHAR2(61):=
139 g_package_name||'del_or_upd_ben_batch_parameter';
140
141 BEGIN
142
143 IF g_debug THEN
144 debug_enter(l_proc_name);
145 END IF;
146
147 /* --vaibgupt 8299459
148 IF p_ben_batch_parameter_exists
149 THEN -- update max_err_num to what it was before the run
150
151 IF g_debug THEN
152 l_proc_step := 10;
153 debug(l_proc_name,l_proc_step);
154 END IF;
155
156 UPDATE ben_batch_parameter
157 SET max_err_num = p_max_err_num
158 WHERE batch_parameter_id = p_batch_parameter_id;
159
160 IF g_debug THEN
161 debug(SQL%ROWCOUNT||' rows updated.');
162 l_proc_step := 15;
163 debug(l_proc_name,l_proc_step);
164 END IF;
165
166 ELSE -- did not exist before run so delete the one which was inserted
167
168 IF g_debug THEN
169 l_proc_step := 20;
170 debug(l_proc_name,l_proc_step);
171 debug('p_batch_parameter_id:'||p_batch_parameter_id);
172 END IF;
173
174 DELETE FROM ben_batch_parameter WHERE batch_parameter_id = p_batch_parameter_id;
175
176 IF g_debug THEN
177 debug(SQL%ROWCOUNT||' rows deleted.');
178 l_proc_step := 25;
179 debug(l_proc_name,l_proc_step);
180 END IF;
181
182
183 END IF;
184
185 IF g_debug THEN
186 debug_exit(l_proc_name);
187 END IF;
188
189 */
190 END del_or_upd_ben_batch_parameter;
191
192
193
194 PROCEDURE get_last_ben_report
195 (p_person_id IN NUMBER
196 ,p_process_date IN DATE
197 ,p_last_ben_report OUT NOCOPY csr_last_ben_report%ROWTYPE
198 )
199 IS
200
201 l_last_ben_report csr_last_ben_report%ROWTYPE;
202
203 l_proc_step NUMBER(38,10);
204 l_proc_name VARCHAR2(61):=
205 g_package_name||'get_last_ben_report';
206
207 BEGIN
208
209 IF g_debug THEN
210 debug_enter(l_proc_name);
211 END IF;
212
213 OPEN csr_last_ben_report
214 (p_person_id
215 ,p_process_date
216 );
217 FETCH csr_last_ben_report INTO p_last_ben_report;
218 CLOSE csr_last_ben_report;
219
220 --p_last_ben_report := l_last_ben_report;
221
222 IF g_debug THEN
223 debug_exit(l_proc_name);
224 END IF;
225
226 END get_last_ben_report;
227
228 BEGIN -- main()
229
230 g_debug := hr_utility.debug_enabled;
231
232 IF g_debug THEN
233 debug_enter(l_proc_name);
234 END IF;
235
236 -- error handling improvements
237 /*
238 1. retrieve ben_batch_parameter for BENMNGLE
239 2. set max error count to 1, insert if a row did not exist in ben_batch_param
240 3. count the number of ben_reporting entries for this person
241 -- to count try join with ben_benefit_actions
242 4. set audit flag to Y
243 5. do the usual call
244 6. count the number of ben_erporting_entries for this person
245 7. If it differs from the count before execution then raise exception
246 8. or if an exception has occured and we are in the expcetion block
247 then
248 9. check for the latest ben_reporting entry
249 10. if there is one retrive the text set that token for pqp dummy message
250 11. and fnd raise error.
251 */
252
253
254 /* -- vaibgupt 8299459
255 OPEN csr_benmngle_batch_parameter(p_business_group_id => p_business_group_id);
256 FETCH csr_benmngle_batch_parameter INTO l_benmngle_batch_parameter;
257 CLOSE csr_benmngle_batch_parameter;
258
259 IF l_benmngle_batch_parameter.batch_parameter_id IS NOT NULL
260 THEN
261
262 IF g_debug THEN
263 l_proc_step := 10;
264 debug(l_proc_name,l_proc_step);
265 END IF;
266
267 l_ben_batch_parameter_exists := TRUE;
268
269 -- Begin Changes by Vaibgupt (Vaibhav Gupta) bug 8299459 (Commenting the Update statement )
270 -- UPDATE ben_batch_parameter
271 -- SET max_err_num = 1
272 -- WHERE batch_parameter_id = l_benmngle_batch_parameter.batch_parameter_id;
273 --End Changes by Vaibgupt (Vaibhav Gupta) bug 8299459
274
275
276 ELSE
277
278 IF g_debug THEN
279 l_proc_step := 20;
280 debug(l_proc_name,l_proc_step);
281 END IF;
282
283 l_ben_batch_parameter_exists := FALSE;
284 INSERT INTO ben_batch_parameter
285 (batch_parameter_id -- NOT NULL NUMBER(15)
286 ,batch_exe_cd -- VARCHAR2(30)
287 ,business_group_id -- NOT NULL NUMBER(15)
288 ,thread_cnt_num -- NUMBER(15)
289 ,max_err_num -- NUMBER(15)
290 ,chunk_size -- NUMBER(15)
291 ,last_update_date -- DATE
292 ,last_updated_by -- NUMBER(15)
293 ,last_update_login -- NUMBER(15)
294 ,created_by -- NUMBER(15)
295 ,creation_date -- DATE
296 ,object_version_number -- NUMBER(9)
297 )
298 SELECT ben_batch_parameter_s.NEXTVAL --batch_parameter_id
299 ,'BENMNGLE' --batch_exe_cd
300 ,p_business_group_id --business_group_id
301 ,NULL --thread_cnt_num
302 ,1 --max_err_num
303 ,NULL --chunk_size
304 ,SYSDATE --last_update_date
305 ,-1 --last_updated_by
306 ,-1 --last_update_login
307 ,-1 --created_by
308 ,SYSDATE --creation_date
309 ,1 --object_version_number
310 FROM DUAL;
311
312 OPEN csr_benmngle_batch_parameter(p_business_group_id => p_business_group_id);
313 FETCH csr_benmngle_batch_parameter INTO l_benmngle_batch_parameter;
314 CLOSE csr_benmngle_batch_parameter;
315
316 END IF;
317
318 */
319
320
321 l_audit_log_flag := 'Y';
322
323 l_plsql_block :=
324 'BEGIN
325 ben_manage_life_events.abse_process
326 (errbuf => :Out1
327 ,retcode => :Out2
328 ,p_effective_date => :In1
329 ,p_mode => :In2
330 ,p_person_id => :In3
331 ,p_business_group_id => :In4
332 ,p_commit_data => :In5
333 ,p_audit_log_flag => :In6
334 );
335 END;
336 ';
337
338 IF g_debug THEN
339 l_proc_step := 30;
340 debug(l_proc_name,l_proc_step);
341 END IF;
342
343
344 IF p_absence_end_date IS NOT NULL
345 THEN
346
347 IF g_debug THEN
348 l_proc_step := 35;
349 debug(l_proc_name,l_proc_step);
350 END IF;
351
352 l_effective_date_canonical := fnd_date.date_to_canonical(p_absence_end_date);
353
354 ELSIF p_absence_start_date IS NOT NULL
355 THEN
356
357 IF g_debug THEN
358 l_proc_step := 35;
359 debug(l_proc_name,l_proc_step);
360 END IF;
361
362 l_effective_date_canonical := fnd_date.date_to_canonical(p_absence_start_date);
363
364 ELSE
365
366 IF g_debug THEN
367 l_proc_step := 40;
368 debug(l_proc_name,l_proc_step);
369 END IF;
370
371 -- Start and end date are NULL, either
372 -- a) Absence has been deleted OR
373 -- b) User pressed button on Absence in an empty new record.
374 --
375 -- setting p_effective_date as effective date
376 l_effective_date_canonical := fnd_date.date_to_canonical(p_effective_date);
377
378 END IF;
379
380 IF g_debug THEN
381 l_proc_step := 45;
382 debug(l_proc_name,l_proc_step);
383 END IF;
384
385 l_effective_date :=
386 fnd_date.canonical_to_date(l_effective_date_canonical);
387
388 get_last_ben_report -- before run
389 (p_person_id => p_person_id
390 ,p_process_date => l_effective_date
391 ,p_last_ben_report => l_before_run_last_ben_report
392 );
393
394 IF g_debug THEN
395 l_proc_step := 50;
396 debug(l_proc_name,l_proc_step);
397 debug('l_effective_date_canonical:'||l_effective_date_canonical);
398 END IF;
399
400 EXECUTE IMMEDIATE l_plsql_block
401 USING OUT p_errbuf
402 ,OUT p_retcode
403 ,l_effective_date_canonical
404 ,l_mode
405 ,p_person_id
406 ,p_business_group_id
407 ,l_commit_data
408 ,l_audit_log_flag;
409
410 IF g_debug THEN
411 l_proc_step := 60;
412 debug(l_proc_name,l_proc_step);
413 END IF;
414
415 -- Note a commit has been issue at this point
416
417 get_last_ben_report -- after run
418 (p_person_id => p_person_id
419 ,p_process_date => l_effective_date
420 ,p_last_ben_report => l_after_run_last_ben_report
421 );
422
423 IF g_debug THEN
424 l_proc_step := 70;
425 debug(l_proc_name,l_proc_step);
426 END IF;
427
428 IF ( NVL(l_after_run_last_ben_report.reporting_id,-1)
429 <>
430 NVL(l_before_run_last_ben_report.reporting_id,-1)
431 )
432 AND
433 ( l_after_run_last_ben_report.rep_typ_cd IN ('FATAL')
434 OR
435 l_after_run_last_ben_report.rep_typ_cd LIKE 'ERROR%'
436 )
437 THEN
438 -- there has been an error recorded
439 -- but some how no exception was raised
440 -- do so now.
441 IF g_debug THEN
442 l_proc_step := 75;
443 debug(l_proc_name,l_proc_step);
444 END IF;
445
446 RAISE hr_application_error;
447 -- it will be handled as WHEN OTHERS
448
449 END IF; -- IF ( NVL(l_after_run_last_ben_report.reporting_id,-1)
450
451 IF g_debug THEN
452 l_proc_step := 80;
453 debug(l_proc_name,l_proc_step);
454 END IF;
455
456 /* --vaibgupt 8299459
457 del_or_upd_ben_batch_parameter
458 (l_ben_batch_parameter_exists
459 ,l_benmngle_batch_parameter.batch_parameter_id
460 ,l_benmngle_batch_parameter.max_err_num
461 );
462 */
463 -- restore the effective date to what it was before the run
464 -- as benmngle changes fnd_sessions row
465 UPDATE fnd_sessions
466 SET effective_date = p_effective_date
467 WHERE session_id = USERENV('sessionid');
468
469 COMMIT;
470
471 IF g_debug THEN
472 debug_exit(l_proc_name);
473 END IF;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 -- Note a commit has been issue at this point whether or not
478 -- benmgle raises an error. However just to be sure
479 -- issue a ROLLBACK and then COMMIT any changes in this handler
480 -- before issuing a fnd_message.raise_error
481
482 l_error_code := SQLCODE;
483 l_error_message := --l_proc_name||'{'||l_proc_step||'}:'||SUBSTR(SQLERRM,1,1930);
484 SUBSTR(SQLERRM,1,2000);
485
486 IF g_debug THEN
487 debug(l_proc_name,-999);
488 debug(l_proc_name||'{'||l_proc_step||'}:');
489 debug(l_error_code);
490 debug(l_error_message);
491 END IF;
492
493 IF l_error_code = hr_utility.hr_error_number
494 THEN
495
496 IF g_debug THEN
497 l_proc_step := -998;
498 debug(l_proc_name,l_proc_step);
499 END IF;
500
501 -- is an app exception
502 -- dirty code need to examine if ben is passing back
503 -- an exception name or an exception text
504 -- if its the name get the string note we cannot
505 -- report tokens like this
506 --
507 IF l_error_message LIKE 'ORA-20001: BEN_9%'
508 OR
509 l_error_message LIKE 'ORA-20001: PQP_2%'
510 THEN
511
512 IF g_debug THEN
513 l_proc_step := -997;
514 debug(l_proc_name,l_proc_step);
515 END IF;
516
517 l_error_message := -- extract the msg short name
518 rtrim(ltrim(substr(l_error_message, instr(l_error_message,':')+1)),':');
519
520 l_error_message :=
521 fnd_message.get_string(SUBSTR(l_error_message,1,3),SUBSTR(l_error_message,1,30));
522
523 ELSE
524 IF g_debug THEN
525 l_proc_step := -996;
526 debug(l_proc_name,l_proc_step);
527 END IF;
528
529 l_error_message :=
530 l_proc_name||'{'||l_proc_step||'}:'||SUBSTR(SQLERRM,1,1930);
531 END IF;
532
533 ELSE
534 IF g_debug THEN
535 l_proc_step := -995;
536 debug(l_proc_name,l_proc_step);
537 END IF;
538 l_error_message := l_proc_name||'{'||l_proc_step||'}:'||SUBSTR(SQLERRM,1,1930);
539 END IF;
540
541
542 ROLLBACK;
543
544 p_errbuf := SUBSTR(l_error_message,1,1000);
545 -- as the receiving size is max 1000
546 p_retcode := l_error_code;
547
548 IF g_debug THEN
549 debug(l_proc_name,-990);
550 debug('l_batch_parameter_id:'||l_benmngle_batch_parameter.batch_parameter_id);
551 END IF;
552
553 del_or_upd_ben_batch_parameter
554 (l_ben_batch_parameter_exists
555 ,l_benmngle_batch_parameter.batch_parameter_id
556 ,l_benmngle_batch_parameter.max_err_num
557 );
558
559 IF g_debug THEN
560 debug(l_proc_name,-980);
561 END IF;
562
563 IF l_after_run_last_ben_report.reporting_id IS NULL
564 THEN
565 -- exception raised from within BENMGLE
566
567 IF g_debug THEN
568 debug(l_proc_name,-970);
569 END IF;
570
571 get_last_ben_report
572 (p_person_id => p_person_id
573 ,p_process_date => l_effective_date
574 ,p_last_ben_report => l_after_run_last_ben_report
575 );
576
577 IF g_debug THEN
578 debug(l_proc_name,-960);
579 END IF;
580
581 END IF; -- IF l_after_run_last_ben_report.reporting_id IS NULL
582
583 IF l_after_run_last_ben_report.reporting_id IS NOT NULL
584 AND
585 ( l_after_run_last_ben_report.reporting_id
586 <> -- need to check again since this could be app an
587 -- exception from within benmgle or one we raised
588 NVL(l_before_run_last_ben_report.reporting_id,-1)
589 )
590 AND
591 l_after_run_last_ben_report.text IS NOT NULL
592 THEN
593 -- if a after run ben report was found
594 -- which was not the same as the one before the run
595 -- and has a not null text then set the error message
596 -- to be reported with that new text
597
598 IF g_debug THEN
599 debug(l_proc_name,-950);
600 END IF;
601
602 l_error_message := l_after_run_last_ben_report.text;
603
604 END IF; --IF l_after_run_last_ben_report.reporting_id IS NOT NULL
605
606 IF g_debug THEN
607 debug(l_proc_name,-940);
608 END IF;
609
610 -- restore the effective date to what it was before the run
611 UPDATE fnd_sessions
612 SET effective_date = p_effective_date
613 WHERE session_id = USERENV('sessionid');
614
615 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
616 fnd_message.set_token('TOKEN', l_error_message);
617 IF g_debug THEN
618 debug_exit(l_proc_name);
619 END IF;
620
621 COMMIT; -- why ??
622 -- to ensure that the changes to ben_batch_parameters are applied.
623
624 fnd_message.raise_error;
625
626 END abse_process;
627 --
628 --
629 --
630 END pqp_gb_manage_life_events;
[Home] [Help]