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