[Home] [Help]
PACKAGE BODY: APPS.PQH_PROCESS_BATCH_LOG
Source
4 -- | Private Global Definitions |
1 PACKAGE BODY pqh_process_batch_log AS
2 /* $Header: pqerrlog.pkb 115.8 2004/06/15 13:51:55 rthiagar noship $ */
3 -- ----------------------------------------------------------------------------
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' pqh_process_batch_log.'; -- Global package name
8 --
9
10 --
11 /*----------------------------------------------------------------
12 ||
13 || PROCEDURE : start_log
14 ||
15 ------------------------------------------------------------------*/
16
17 PROCEDURE start_log
18 (
19 p_batch_id IN pqh_process_log.txn_id%TYPE,
20 p_module_cd IN pqh_process_log.module_cd%TYPE,
21 p_log_context IN pqh_process_log.log_context%TYPE,
22 p_information_category IN pqh_process_log.information_category%TYPE DEFAULT NULL,
23 p_information1 IN pqh_process_log.information1%TYPE DEFAULT NULL,
24 p_information2 IN pqh_process_log.information1%TYPE DEFAULT NULL,
25 p_information3 IN pqh_process_log.information1%TYPE DEFAULT NULL,
29 p_information7 IN pqh_process_log.information1%TYPE DEFAULT NULL,
26 p_information4 IN pqh_process_log.information1%TYPE DEFAULT NULL,
27 p_information5 IN pqh_process_log.information1%TYPE DEFAULT NULL,
28 p_information6 IN pqh_process_log.information1%TYPE DEFAULT NULL,
30 p_information8 IN pqh_process_log.information1%TYPE DEFAULT NULL,
31 p_information9 IN pqh_process_log.information1%TYPE DEFAULT NULL,
32 p_information10 IN pqh_process_log.information1%TYPE DEFAULT NULL,
33 p_information11 IN pqh_process_log.information1%TYPE DEFAULT NULL,
34 p_information12 IN pqh_process_log.information1%TYPE DEFAULT NULL,
35 p_information13 IN pqh_process_log.information1%TYPE DEFAULT NULL,
36 p_information14 IN pqh_process_log.information1%TYPE DEFAULT NULL,
37 p_information15 IN pqh_process_log.information1%TYPE DEFAULT NULL,
38 p_information16 IN pqh_process_log.information1%TYPE DEFAULT NULL,
39 p_information17 IN pqh_process_log.information1%TYPE DEFAULT NULL,
40 p_information18 IN pqh_process_log.information1%TYPE DEFAULT NULL,
41 p_information19 IN pqh_process_log.information1%TYPE DEFAULT NULL,
42 p_information20 IN pqh_process_log.information1%TYPE DEFAULT NULL,
43 p_information21 IN pqh_process_log.information1%TYPE DEFAULT NULL,
44 p_information22 IN pqh_process_log.information1%TYPE DEFAULT NULL,
45 p_information23 IN pqh_process_log.information1%TYPE DEFAULT NULL,
46 p_information24 IN pqh_process_log.information1%TYPE DEFAULT NULL,
47 p_information25 IN pqh_process_log.information1%TYPE DEFAULT NULL,
48 p_information26 IN pqh_process_log.information1%TYPE DEFAULT NULL,
49 p_information27 IN pqh_process_log.information1%TYPE DEFAULT NULL,
50 p_information28 IN pqh_process_log.information1%TYPE DEFAULT NULL,
51 p_information29 IN pqh_process_log.information1%TYPE DEFAULT NULL,
52 p_information30 IN pqh_process_log.information1%TYPE DEFAULT NULL
53 ) IS
54 --
55 -- local variables
56 --
57 l_proc varchar2(72) := g_package||'start_log';
58 l_process_log_id pqh_process_log.process_log_id%TYPE;
59 l_object_version_number pqh_process_log.object_version_number%TYPE;
60 PRAGMA AUTONOMOUS_TRANSACTION;
61
62
63
64 BEGIN
65
66 hr_utility.set_location('Entering: '||l_proc, 5);
67
68 -- populate the globals
69
70 g_batch_id := p_batch_id;
71 g_module_cd := p_module_cd;
72
73 hr_utility.set_location('Batch: '||g_batch_id, 10);
74 hr_utility.set_location('Module: '||g_module_cd, 15);
75 hr_utility.set_location('Context: '||p_log_context, 15);
76
77 -- initialize the pl / sql table
78
79 g_log_tab.DELETE;
80
81 -- delete all records for this batch from pqh_process_log table
82 -- this is a tree structure so delete the entire tree below the batch_id
83
84 DELETE FROM pqh_process_log
85 WHERE process_log_id IN
86 ( SELECT process_log_id
87 FROM pqh_process_log
88 START WITH master_process_log_id IS NULL
89 AND txn_id = g_batch_id
90 AND module_cd = g_module_cd
91 CONNECT BY PRIOR process_log_id = master_process_log_id
92 );
93
94
95 /*
96 insert start record in pqh_process_log with message_type_cd = 'START'
97 */
98 -- Insert API call
99 pqh_process_log_api.create_process_log
100 (
101 p_validate => false
102 ,p_process_log_id => l_process_log_id
103 ,p_module_cd => g_module_cd
104 ,p_txn_id => g_batch_id
105 ,p_master_process_log_id => null
106 ,p_message_text => 'Process Started'
107 ,p_message_type_cd => 'START'
108 ,p_batch_status => 'PENDING'
109 ,p_batch_start_date => sysdate
110 ,p_batch_end_date => null
111 ,p_txn_table_route_id => null
112 ,p_log_context => p_log_context
113 ,p_information_category => p_information_category
114 ,p_information1 => p_information1
115 ,p_information2 => p_information2
116 ,p_information3 => p_information3
117 ,p_information4 => p_information4
118 ,p_information5 => p_information5
119 ,p_information6 => p_information6
120 ,p_information7 => p_information7
121 ,p_information8 => p_information8
122 ,p_information9 => p_information9
123 ,p_information10 => p_information10
124 ,p_information11 => p_information11
125 ,p_information12 => p_information12
126 ,p_information13 => p_information13
127 ,p_information14 => p_information14
128 ,p_information15 => p_information15
129 ,p_information16 => p_information16
130 ,p_information17 => p_information17
131 ,p_information18 => p_information18
132 ,p_information19 => p_information19
133 ,p_information20 => p_information20
134 ,p_information21 => p_information21
135 ,p_information22 => p_information22
136 ,p_information23 => p_information23
137 ,p_information24 => p_information24
141 ,p_information28 => p_information28
138 ,p_information25 => p_information25
139 ,p_information26 => p_information26
140 ,p_information27 => p_information27
142 ,p_information29 => p_information29
143 ,p_information30 => p_information30
144 ,p_object_version_number => l_object_version_number
145 ,p_effective_date => sysdate
146 );
147
148
149 /*
150 For the next txn , this l_process_log_id is the master_l_process_log_id
151 we will also need this id to update in end_log
152 */
153
154 g_master_process_log_id := l_process_log_id;
155
156
157 hr_utility.set_location('Process Started ' ,29);
158 hr_utility.set_location('Process_log_id : '||l_process_log_id, 30);
159 hr_utility.set_location('OVN : '||l_object_version_number, 40);
160
161
162 /*
163 commit the autonomous transaction
164 */
165
166 commit; -- allowed only in autonomous triggers
167
168
169 hr_utility.set_location('Leaving:'||l_proc, 1000);
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
174 hr_utility.set_message_token('ROUTINE', l_proc);
175 hr_utility.set_message_token('REASON', SQLERRM);
176 hr_utility.raise_error;
177 END start_log;
178
179 /*----------------------------------------------------------------
180 ||
181 || PROCEDURE : insert_log
182 ||
183 ------------------------------------------------------------------*/
184
185 PROCEDURE insert_log
186 (
187 p_message_type_cd IN pqh_process_log.message_type_cd%TYPE,
188 p_message_text IN pqh_process_log.message_text%TYPE,
189 p_information_category IN pqh_process_log.information_category%TYPE DEFAULT NULL,
190 p_information1 IN pqh_process_log.information1%TYPE DEFAULT NULL,
191 p_information2 IN pqh_process_log.information1%TYPE DEFAULT NULL,
192 p_information3 IN pqh_process_log.information1%TYPE DEFAULT NULL,
193 p_information4 IN pqh_process_log.information1%TYPE DEFAULT NULL,
194 p_information5 IN pqh_process_log.information1%TYPE DEFAULT NULL,
195 p_information6 IN pqh_process_log.information1%TYPE DEFAULT NULL,
196 p_information7 IN pqh_process_log.information1%TYPE DEFAULT NULL,
197 p_information8 IN pqh_process_log.information1%TYPE DEFAULT NULL,
198 p_information9 IN pqh_process_log.information1%TYPE DEFAULT NULL,
199 p_information10 IN pqh_process_log.information1%TYPE DEFAULT NULL,
200 p_information11 IN pqh_process_log.information1%TYPE DEFAULT NULL,
201 p_information12 IN pqh_process_log.information1%TYPE DEFAULT NULL,
202 p_information13 IN pqh_process_log.information1%TYPE DEFAULT NULL,
203 p_information14 IN pqh_process_log.information1%TYPE DEFAULT NULL,
204 p_information15 IN pqh_process_log.information1%TYPE DEFAULT NULL,
205 p_information16 IN pqh_process_log.information1%TYPE DEFAULT NULL,
206 p_information17 IN pqh_process_log.information1%TYPE DEFAULT NULL,
207 p_information18 IN pqh_process_log.information1%TYPE DEFAULT NULL,
208 p_information19 IN pqh_process_log.information1%TYPE DEFAULT NULL,
209 p_information20 IN pqh_process_log.information1%TYPE DEFAULT NULL,
210 p_information21 IN pqh_process_log.information1%TYPE DEFAULT NULL,
211 p_information22 IN pqh_process_log.information1%TYPE DEFAULT NULL,
212 p_information23 IN pqh_process_log.information1%TYPE DEFAULT NULL,
213 p_information24 IN pqh_process_log.information1%TYPE DEFAULT NULL,
214 p_information25 IN pqh_process_log.information1%TYPE DEFAULT NULL,
215 p_information26 IN pqh_process_log.information1%TYPE DEFAULT NULL,
216 p_information27 IN pqh_process_log.information1%TYPE DEFAULT NULL,
217 p_information28 IN pqh_process_log.information1%TYPE DEFAULT NULL,
218 p_information29 IN pqh_process_log.information1%TYPE DEFAULT NULL,
219 p_information30 IN pqh_process_log.information1%TYPE DEFAULT NULL
220 ) IS
221
222 /*
223 Before inserting we check if row is already existing as the same node may have been
224 called more then once
225 */
226 --
227 -- local variables
228 --
229 l_proc varchar2(72) := g_package||'insert_log';
230 l_txn_id pqh_process_log.txn_id%TYPE;
231 l_log_context pqh_process_log.log_context%TYPE;
232 l_txn_table_route_id pqh_process_log.txn_table_route_id%TYPE;
233 l_process_log_id pqh_process_log.process_log_id%TYPE;
234 l_master_process_log_id pqh_process_log.master_process_log_id%TYPE;
235 l_object_version_number pqh_process_log.object_version_number%TYPE;
236 l_current_level NUMBER := 0;
237 l_message_text pqh_process_log.message_text%TYPE;
238 l_message_type_cd pqh_process_log.message_type_cd%TYPE;
239 l_row_exists varchar2(10) := 'N';
240 l_curr_process_log_id pqh_process_log.process_log_id%TYPE;
241 PRAGMA AUTONOMOUS_TRANSACTION;
242
243 CURSOR csr_row_exists(p_txn_id IN number) is
244 SELECT 'Y', process_log_id
245 FROM pqh_process_log
246 WHERE txn_id = p_txn_id
247 AND master_process_log_id IS NOT NULL
251 CONNECT BY prior process_log_id = master_process_log_id;
248 START WITH master_process_log_id IS NULL
249 AND txn_id = g_batch_id
250 AND module_cd = g_module_cd
252
253
254 BEGIN
255
256 hr_utility.set_location('Entering: '||l_proc, 5);
257
258 /*
259 We will insert error for the current level in g_log_tab
260 */
261
262 -- for first record in g_log_tab the master is g_master_process_log_id
263 l_master_process_log_id := g_master_process_log_id;
264
265 -- loop thru the g_log_tab table and insert
266 FOR i IN NVL(g_log_tab.FIRST,0)..NVL(g_log_tab.LAST,-999)
267 LOOP
268 /*
269 -- call insert API if insert_flag <> 'Y'
270 IF NVL(g_log_tab(i).insert_flag,'N') <> 'Y' THEN
271 */
272 -- call insert API if l_row_exists <> 'Y
273 OPEN csr_row_exists(p_txn_id => g_log_tab(i).txn_id);
274 -- initialize l_row_exists and l_curr_process_log_id
275 l_row_exists := 'N';
276 l_curr_process_log_id := '';
277 FETCH csr_row_exists INTO l_row_exists, l_curr_process_log_id;
278 CLOSE csr_row_exists;
279
280 hr_utility.set_location('Txn Id : '||g_log_tab(i).txn_id,6);
281 hr_utility.set_location('Row Exists : '||l_row_exists,7);
282 hr_utility.set_location('curr_process_log_id : '||l_curr_process_log_id,8);
283
284 IF l_row_exists <> 'Y' THEN
285 l_txn_id := g_log_tab(i).txn_id ;
286 l_txn_table_route_id := g_log_tab(i).txn_table_route_id;
287 l_log_context := g_log_tab(i).log_context;
288
289 -- determine the current context to insert the error message
290 l_current_level := NVL(g_log_tab.LAST,-999);
291
292 IF i = l_current_level THEN
293 l_message_text := p_message_text;
294 l_message_type_cd := p_message_type_cd;
295
296 -- Insert API call
297 pqh_process_log_api.create_process_log
298 (
299 p_validate => false
300 ,p_process_log_id => l_process_log_id
301 ,p_module_cd => g_module_cd
302 ,p_txn_id => l_txn_id
303 ,p_master_process_log_id => l_master_process_log_id
304 ,p_message_text => l_message_text
305 ,p_message_type_cd => l_message_type_cd
306 ,p_batch_status => null
307 ,p_batch_start_date => null
308 ,p_batch_end_date => null
309 ,p_txn_table_route_id => l_txn_table_route_id
310 ,p_log_context => l_log_context
311 ,p_information_category => p_information_category
312 ,p_information1 => p_information1
313 ,p_information2 => p_information2
314 ,p_information3 => p_information3
315 ,p_information4 => p_information4
316 ,p_information5 => p_information5
317 ,p_information6 => p_information6
318 ,p_information7 => p_information7
319 ,p_information8 => p_information8
320 ,p_information9 => p_information9
321 ,p_information10 => p_information10
322 ,p_information11 => p_information11
323 ,p_information12 => p_information12
324 ,p_information13 => p_information13
325 ,p_information14 => p_information14
326 ,p_information15 => p_information15
327 ,p_information16 => p_information16
328 ,p_information17 => p_information17
329 ,p_information18 => p_information18
330 ,p_information19 => p_information19
331 ,p_information20 => p_information20
332 ,p_information21 => p_information21
333 ,p_information22 => p_information22
334 ,p_information23 => p_information23
335 ,p_information24 => p_information24
336 ,p_information25 => p_information25
337 ,p_information26 => p_information26
338 ,p_information27 => p_information27
339 ,p_information28 => p_information28
340 ,p_information29 => p_information29
341 ,p_information30 => p_information30
342 ,p_object_version_number => l_object_version_number
343 ,p_effective_date => sysdate
344 );
345 ELSE
346 l_message_text := 'No error';
347 l_message_type_cd := 'COMPLETE';
348
349 -- Insert API call
350 pqh_process_log_api.create_process_log
351 (
352 p_validate => false
353 ,p_process_log_id => l_process_log_id
354 ,p_module_cd => g_module_cd
355 ,p_txn_id => l_txn_id
356 ,p_master_process_log_id => l_master_process_log_id
357 ,p_message_text => l_message_text
361 ,p_batch_end_date => null
358 ,p_message_type_cd => l_message_type_cd
359 ,p_batch_status => null
360 ,p_batch_start_date => null
362 ,p_txn_table_route_id => l_txn_table_route_id
363 ,p_log_context => l_log_context
364 ,p_information_category => null
365 ,p_information1 => null
366 ,p_information2 => null
367 ,p_information3 => null
368 ,p_information4 => null
369 ,p_information5 => null
370 ,p_information6 => null
371 ,p_information7 => null
372 ,p_information8 => null
373 ,p_information9 => null
374 ,p_information10 => null
375 ,p_information11 => null
376 ,p_information12 => null
377 ,p_information13 => null
378 ,p_information14 => null
379 ,p_information15 => null
380 ,p_information16 => null
381 ,p_information17 => null
382 ,p_information18 => null
383 ,p_information19 => null
384 ,p_information20 => null
385 ,p_information21 => null
386 ,p_information22 => null
387 ,p_information23 => null
388 ,p_information24 => null
389 ,p_information25 => null
390 ,p_information26 => null
391 ,p_information27 => null
392 ,p_information28 => null
393 ,p_information29 => null
394 ,p_information30 => null
395 ,p_object_version_number => l_object_version_number
396 ,p_effective_date => sysdate
397 );
398 END IF;
399
400 /*
401 -- Insert API call
402 pqh_process_log_api.create_process_log
403 (
404 p_validate => false
405 ,p_process_log_id => l_process_log_id
406 ,p_module_cd => g_module_cd
407 ,p_txn_id => l_txn_id
408 ,p_master_process_log_id => l_master_process_log_id
409 ,p_message_text => l_message_text
410 ,p_message_type_cd => l_message_type_cd
411 ,p_batch_status => null
412 ,p_batch_start_date => null
413 ,p_batch_end_date => null
414 ,p_txn_table_route_id => l_txn_table_route_id
415 ,p_log_context => l_log_context
416 ,p_information_category => p_information_category
417 ,p_information1 => p_information1
418 ,p_information2 => p_information2
419 ,p_information3 => p_information3
420 ,p_information4 => p_information4
421 ,p_information5 => p_information5
422 ,p_information6 => p_information6
423 ,p_information7 => p_information7
424 ,p_information8 => p_information8
425 ,p_information9 => p_information9
426 ,p_information10 => p_information10
427 ,p_information11 => p_information11
428 ,p_information12 => p_information12
429 ,p_information13 => p_information13
430 ,p_information14 => p_information14
431 ,p_information15 => p_information15
432 ,p_information16 => p_information16
433 ,p_information17 => p_information17
434 ,p_information18 => p_information18
435 ,p_information19 => p_information19
436 ,p_information20 => p_information20
437 ,p_information21 => p_information21
438 ,p_information22 => p_information22
439 ,p_information23 => p_information23
440 ,p_information24 => p_information24
441 ,p_information25 => p_information25
442 ,p_information26 => p_information26
443 ,p_information27 => p_information27
444 ,p_information28 => p_information28
445 ,p_information29 => p_information29
446 ,p_information30 => p_information30
447 ,p_object_version_number => l_object_version_number
448 ,p_effective_date => sysdate
449 );
450 */
451
455 -- populate the process_log_id for this record
452 -- mark the current record insert_flag = Y so that this will not be re-inserted
453 g_log_tab(i).insert_flag := 'Y';
454
456 g_log_tab(i).process_log_id := l_process_log_id;
457
458
459 hr_utility.set_location('Inserted log Process log id: '||l_process_log_id,25);
460 hr_utility.set_location('Txn_id : '||l_txn_id, 30);
461 hr_utility.set_location('Txn Route_id : '||l_txn_table_route_id, 50);
462 hr_utility.set_location('OVN : '||l_object_version_number, 100);
463
464 END IF;
465
466 -- for the next record the current process_log_id is master
467 IF l_row_exists = 'Y' THEN
468 -- as row exists pick master from table
469 l_master_process_log_id := l_curr_process_log_id;
470 ELSE
471 -- pick the new row that was created
472 l_master_process_log_id := g_log_tab(i).process_log_id;
473 END IF;
474
475
476 END LOOP;
477
478 /*
479 commit the autonomous transaction
480 */
481
482 commit;
483
484 hr_utility.set_location('Leaving:'||l_proc, 1000);
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
489 hr_utility.set_message_token('ROUTINE', l_proc);
490 hr_utility.set_message_token('REASON', SQLERRM);
491 g_Batch_Id := NULL;
492 g_Module_Cd := NULL;
493 g_Master_Process_Log_Id := NULL;
494 hr_utility.raise_error;
495 END insert_log;
496
497
498
499 /*----------------------------------------------------------------
500 ||
501 || PROCEDURE : set_context_level
502 ||
503 ------------------------------------------------------------------*/
504
505 PROCEDURE set_context_level
506 (
507 p_txn_id IN pqh_process_log.txn_id%TYPE,
508 p_txn_table_route_id IN pqh_process_log.txn_table_route_id%TYPE,
509 p_level IN NUMBER,
510 p_log_context IN pqh_process_log.log_context%TYPE DEFAULT NULL
511 ) IS
512 --
513 -- local variables
514 --
515 l_proc varchar2(72) := g_package||'set_context_level';
516 l_max_level NUMBER;
517 l_current_level NUMBER;
518
519 BEGIN
520
521 hr_utility.set_location('Entering: '||l_proc, 5);
522
523 -- determine the maximum level in the global table
524 l_max_level := NVL(g_log_tab.LAST,0);
525 l_current_level := p_level;
526
527 hr_utility.set_location('Current Level '||l_current_level,10);
528 hr_utility.set_location('Maximum Level '||l_max_level,11);
529
530 -- delete from global table all levels below the current level
531 g_log_tab.DELETE(p_level,l_max_level);
532
533 -- check if all the levels above the current level exists else error out
534 WHILE l_current_level > NVL(g_log_tab.FIRST,9999)
535 LOOP
536 IF NOT g_log_tab.EXISTS(l_current_level - 1) THEN
537 hr_utility.set_message(8302, 'PQH_INVALID_MESSAGE_LEVEL');
538 hr_utility.raise_error;
539 END IF;
540 l_current_level := l_current_level - 1;
541 END LOOP;
542
543 -- populate the global table with the values
544 g_log_tab(p_level).txn_id := p_txn_id;
545 g_log_tab(p_level).txn_table_route_id := p_txn_table_route_id;
546 g_log_tab(p_level).level := p_level;
547 g_log_tab(p_level).log_context := p_log_context;
548
549
550 hr_utility.set_location('Leaving:'||l_proc, 1000);
551
552 EXCEPTION
553 WHEN OTHERS THEN
557 g_Batch_Id := NULL;
554 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
555 hr_utility.set_message_token('ROUTINE', l_proc);
556 hr_utility.set_message_token('REASON', SQLERRM);
558 g_Module_Cd := NULL;
559 g_Master_Process_Log_Id := NULL;
560 hr_utility.raise_error;
561 END set_context_level;
562
563
564 /*----------------------------------------------------------------
565 ||
566 || PROCEDURE : end_log
567 ||
568 ------------------------------------------------------------------*/
569
570 PROCEDURE end_log
571 IS
572 --
573 -- local variables
574 --
575 l_proc varchar2(72) := g_package||'end_log';
576 l_count_error NUMBER := 0;
577 l_count_warning NUMBER := 0;
578 l_status VARCHAR2(30);
579
580 PRAGMA AUTONOMOUS_TRANSACTION;
581
582 CURSOR csr_status (p_message_type_cd IN VARCHAR2 ) IS
583 SELECT COUNT(*)
584 FROM pqh_process_log
585 WHERE message_type_cd = p_message_type_cd
586 START WITH master_process_log_id IS NULL AND txn_id = g_batch_id AND module_cd = g_module_cd
587 CONNECT BY PRIOR process_log_id = master_process_log_id;
588
589
590 BEGIN
591
592 hr_utility.set_location('Entering: '||l_proc, 5);
593
594
595 /*
596 Compute the status of the batch. If there exists any record in the batch with
597 message_type_cd = 'ERROR' then the batch_status = 'ERROR'
598 If there only exists records in the batch with message_type_cd = 'WARNING' then
599 the batch_status = 'WARNING'
600 If there are NO records in the batch with message_type_cd = 'WARNING' OR 'ERROR' then
601 the batch_status = 'SUCCESS'
602 */
603
604 OPEN csr_status(p_message_type_cd => 'ERROR');
605 FETCH csr_status INTO l_count_error;
606 CLOSE csr_status;
607
608 OPEN csr_status(p_message_type_cd => 'WARNING');
609 FETCH csr_status INTO l_count_warning;
610 CLOSE csr_status;
611
612
613 IF l_count_error <> 0 THEN
614 -- there are one or more errors
615 l_status := 'ERROR';
616 ELSE
617 -- errors are 0 , check for warnings
618 IF l_count_warning <> 0 THEN
619 -- there are one or more warnings
620 l_status := 'WARNING';
621 ELSE
622 -- no errors or warnings
623 l_status := 'SUCCESS';
624 END IF;
625
626 END IF;
627
628 hr_utility.set_location('Batch Status : '||l_status,100);
629
630 /*
631 update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
632 update the batch_end_date with current date time
633 */
634
635 UPDATE pqh_process_log
636 SET message_type_cd = 'COMPLETE',
637 message_text = fnd_message.get_string('PQH','PQH_PROCESS_COMPLETED'),
638 batch_status = l_status,
639 batch_end_date = sysdate
640 WHERE process_log_id = g_master_process_log_id;
641
642
643 hr_utility.set_location('Leaving:'||l_proc, 1000);
644
645 /*
646 commit the autonomous transaction
647 */
648
649 commit;
650
651 /* Added by vevenkat to reset the Global variables */
652
653 g_Batch_Id := NULL;
654 g_Module_Cd := NULL;
655 g_Master_Process_Log_Id := NULL;
656 EXCEPTION
657 WHEN OTHERS THEN
658 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
659 hr_utility.set_message_token('ROUTINE', l_proc);
660 hr_utility.set_message_token('REASON', SQLERRM);
661 g_Batch_id := NULL;
662 G_Module_Cd := NULL;
663 g_Master_Process_Log_Id := NULL;
664 hr_utility.raise_error;
665 END end_log;
666
667
668
669 END PQH_PROCESS_BATCH_LOG;