[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_TP_EXT_PROCESS
Source
1 PACKAGE BODY pqp_gb_tp_ext_process AS
2 -- /* $Header: pqpgbtpext.pkb 120.1 2010/09/15 12:40:23 krreddy ship $ */
3 --
4 --
5
6 -- ----------------------------------------------------------------------------
7 -- |--------------------------------< debug >---------------------------------|
8 -- ----------------------------------------------------------------------------
9
10 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
11 IS
12
13 --
14 BEGIN
15 --
16 pqp_utilities.DEBUG (
17 p_trace_message => p_trace_message
18 ,p_trace_location => p_trace_location
19 );
20 --
21 END DEBUG;
22
23
24 --
25 -- ----------------------------------------------------------------------------
26 -- |--------------------------------< debug >---------------------------------|
27 -- ----------------------------------------------------------------------------
28
29 PROCEDURE DEBUG (p_trace_number IN NUMBER)
30 IS
31
32 --
33 BEGIN
34 --
35 DEBUG (fnd_number.number_to_canonical (p_trace_number));
36 --
37
38 END DEBUG;
39
40 --
41 -- ----------------------------------------------------------------------------
42 -- |--------------------------------< debug >---------------------------------|
43 -- ----------------------------------------------------------------------------
44
45 PROCEDURE DEBUG (p_trace_date IN DATE)
46 IS
47
48 --
49 BEGIN
50 --
51 DEBUG (fnd_date.date_to_canonical (p_trace_date));
52 --
53
54 END DEBUG;
55
56
57 -- This procedure is used for debug purposes
58 -- debug_enter checks the debug flag and sets the trace on/off
59 --
60 -- ----------------------------------------------------------------------------
61 -- |----------------------------< debug_enter >-------------------------------|
62 -- ----------------------------------------------------------------------------
63
64 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
65 IS
66 BEGIN
67 --
68 pqp_utilities.debug_enter (
69 p_proc_name => p_proc_name
70 ,p_trace_on => p_trace_on
71 );
72 --
73 END debug_enter;
74
75
76 -- ----------------------------------------------------------------------------
77 -- |----------------------------< debug_exit >--------------------------------|
78 -- ----------------------------------------------------------------------------
79
80 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
81 IS
82 BEGIN
83 --
84 pqp_utilities.debug_exit (
85 p_proc_name => p_proc_name
86 ,p_trace_off => p_trace_off
87 );
88 --
89 END debug_exit;
90
91
92 -- This procedure is used for debug purposes
93 --
94 -- ----------------------------------------------------------------------------
95 -- |----------------------------< debug_others >--------------------------------|
96 -- ----------------------------------------------------------------------------
97
98 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
99 IS
100 BEGIN
101 --
102 pqp_utilities.debug_others (
103 p_proc_name => p_proc_name
104 ,p_proc_step => p_proc_step
105 );
106 --
107 END debug_others;
108
109 -- Function returns extract result id for a given request id
110 --
111 -- ----------------------------------------------------------------------------
112 -- |----------------------------< get_ext_rslt_frm_req >----------------------|
113 -- ----------------------------------------------------------------------------
114
115 FUNCTION get_ext_rslt_frm_req (p_request_id IN NUMBER
116 ,p_ext_dfn_id IN NUMBER
117 )
118 RETURN NUMBER IS
119 --
120 CURSOR csr_get_ext_rslt_id
121 IS
122 SELECT ext_rslt_id
123 FROM ben_ext_rslt
124 WHERE request_id = p_request_id
125 AND ext_dfn_id = p_ext_dfn_id;
126
127 l_ext_rslt_id NUMBER;
128 l_proc_name VARCHAR2 (80) := g_proc_name
129 || 'get_ext_rslt_frm_req';
130 l_proc_step NUMBER;
131
132 --
133 BEGIN
134 --
135 IF g_debug
136 THEN
137 l_proc_step := 10;
138 DEBUG ( 'Entering: '
139 || l_proc_name, l_proc_step);
140 END IF;
141
142 OPEN csr_get_ext_rslt_id;
143 FETCH csr_get_ext_rslt_id INTO l_ext_rslt_id;
144
145 IF csr_get_ext_rslt_id%NOTFOUND THEN
146 fnd_message.set_name ('BEN', 'BEN_91873_EXT_NOT_FOUND');
147 fnd_file.put_line(fnd_file.log, 'Error: '
148 || fnd_message.get);
149 fnd_file.put_line(fnd_file.log, ' ');
150 CLOSE csr_get_ext_rslt_id;
151 fnd_message.raise_error;
152 END IF; -- End if of row not found check ...
153 CLOSE csr_get_ext_rslt_id;
154
155 IF g_debug
156 THEN
157 DEBUG ( 'Extract Result ID: '
158 || TO_CHAR(l_ext_rslt_id));
159 l_proc_step := 20;
160 DEBUG ( 'Leaving: '
161 || l_proc_name, l_proc_step);
162 END IF;
163
164 RETURN l_ext_rslt_id;
165
166 END get_ext_rslt_frm_req;
167 --
168
169 -- Procedure gets extract result count for a given ext result id
170 --
171 -- ----------------------------------------------------------------------------
172 -- |----------------------------< get_ext_rslt_count >------------------------|
173 -- ----------------------------------------------------------------------------
174
175 PROCEDURE get_ext_rslt_count (p_ext_rslt_id IN NUMBER
176 ,p_ext_file_id IN NUMBER
177 ,p_hdr_count OUT NOCOPY NUMBER
178 ,p_dtl_count OUT NOCOPY NUMBER
179 ,p_trl_count OUT NOCOPY NUMBER
180 ,p_per_count OUT NOCOPY NUMBER
181 ,p_err_count OUT NOCOPY NUMBER
182 ,p_tot_count OUT NOCOPY NUMBER
183 )
184 IS
185 --
186 -- Cursor to get header record count
187 --
188 CURSOR csr_get_hdr_cnt
189 IS
190 SELECT COUNT(*)
191 FROM ben_ext_rcd_in_file fil
192 ,ben_ext_rcd rcd
193 WHERE fil.ext_rcd_id = rcd.ext_rcd_id
194 AND fil.ext_file_id = p_ext_file_id
195 AND rcd.rcd_type_cd = 'H';
196
197 --
198 -- Cursor to get trailer record count
199 --
200 CURSOR csr_get_trl_cnt
201 IS
202 SELECT COUNT(*)
203 FROM ben_ext_rcd_in_file fil
204 ,ben_ext_rcd rcd
205 WHERE fil.ext_rcd_id = rcd.ext_rcd_id
206 AND fil.ext_file_id = p_ext_file_id
207 AND rcd.rcd_type_cd = 'T';
208 --
209 -- Cursor to get detail record count
210 --
211 CURSOR csr_get_dtl_cnt
212 IS
213 SELECT COUNT(*)
214 FROM ben_ext_rslt_dtl xrd
215 WHERE xrd.ext_rslt_id = p_ext_rslt_id;
216 --
217 -- Cursor to get person record count
218 --
219 CURSOR csr_get_per_cnt
220 IS
221 SELECT COUNT(DISTINCT person_id)
222 FROM ben_ext_rslt_dtl xrd
223 WHERE xrd.ext_rslt_id = p_ext_rslt_id
224 AND person_id not in (0, 999999999999);
225 --
226 -- Cursor to get error record count
227 --
228 CURSOR csr_get_err_cnt
229 IS
230 SELECT COUNT(*)
231 FROM ben_ext_rslt_err err
232 WHERE err.ext_rslt_id = p_ext_rslt_id;
233
234 l_hdr_count NUMBER;
235 l_dtl_count NUMBER;
236 l_trl_count NUMBER;
237 l_per_count NUMBER;
238 l_err_count NUMBER;
239 l_tot_count NUMBER;
240 l_proc_name VARCHAR2 (80) := g_proc_name
241 || 'get_ext_rslt_count';
242 l_proc_step NUMBER;
243
244 --
245 BEGIN
246
247 IF g_debug
248 THEN
249 l_proc_step := 10;
250 DEBUG ( 'Entering: '
251 || l_proc_name, l_proc_step);
252 END IF;
253
254 --
255 -- Get header count
256 --
257 OPEN csr_get_hdr_cnt;
258 FETCH csr_get_hdr_cnt INTO l_hdr_count;
259 CLOSE csr_get_hdr_cnt;
260
261 IF g_debug
262 THEN
263 l_proc_step := 20;
264 DEBUG (l_proc_name, l_proc_step);
265 END IF;
266
267 --
268 -- Get detail count
269 --
270 OPEN csr_get_dtl_cnt;
271 FETCH csr_get_dtl_cnt INTO l_dtl_count;
272 CLOSE csr_get_dtl_cnt;
273
274 IF g_debug
275 THEN
276 l_proc_step := 30;
277 DEBUG (l_proc_name, l_proc_step);
278 END IF;
279
280 --
281 -- Get trailer count
282 --
283 OPEN csr_get_trl_cnt;
284 FETCH csr_get_trl_cnt INTO l_trl_count;
285 CLOSE csr_get_trl_cnt;
286
287 IF g_debug
288 THEN
289 l_proc_step := 40;
290 DEBUG (l_proc_name, l_proc_step);
291 END IF;
292
293 --
294 -- Get person count
295 --
296 OPEN csr_get_per_cnt;
297 FETCH csr_get_per_cnt INTO l_per_count;
298 CLOSE csr_get_per_cnt;
299
300 IF g_debug
301 THEN
302 l_proc_step := 50;
303 DEBUG (l_proc_name, l_proc_step);
304 END IF;
305
306 --
307 -- Get error count
308 --
309 OPEN csr_get_err_cnt;
310 FETCH csr_get_err_cnt INTO l_err_count;
311 CLOSE csr_get_err_cnt;
312
313 l_tot_count := l_hdr_count + l_dtl_count + l_trl_count;
314
315 IF g_debug
316 THEN
317 DEBUG ('Header Count : ' || TO_CHAR(l_hdr_count));
318 DEBUG ('Detail Count : ' || TO_CHAR(l_dtl_count));
319 DEBUG ('Trailer Count : ' || TO_CHAR(l_trl_count));
320 DEBUG ('Person Count : ' || TO_CHAR(l_per_count));
321 DEBUG ('Error Count : ' || TO_CHAR(l_err_count));
322 DEBUG ('Total Detail Count : ' || TO_CHAR(l_tot_count));
323 END IF;
324
325
326 IF g_debug
327 THEN
328 l_proc_step := 60;
329 DEBUG (l_proc_name, l_proc_step);
330 END IF;
331
332 fnd_file.put_line(fnd_file.log, 'Total Count for extract result ID: '
333 || TO_CHAR(p_ext_rslt_id));
334 fnd_file.put_line(fnd_file.log, ' ');
335 fnd_file.put_line(fnd_file.log, 'Header Record Count: '
336 || TO_CHAR(l_hdr_count));
337 fnd_file.put_line(fnd_file.log, ' ');
338 fnd_file.put_line(fnd_file.log, 'Detail Record Count: '
339 || TO_CHAR(l_dtl_count));
340 fnd_file.put_line(fnd_file.log, ' ');
341 fnd_file.put_line(fnd_file.log, 'Trailer Record Count: '
342 || TO_CHAR(l_trl_count));
343 fnd_file.put_line(fnd_file.log, ' ');
344 fnd_file.put_line(fnd_file.log, 'Person Record Count: '
345 || TO_CHAR(l_per_count));
346 fnd_file.put_line(fnd_file.log, ' ');
347 fnd_file.put_line(fnd_file.log, 'Error Record Count: '
348 || TO_CHAR(l_err_count));
349 fnd_file.put_line(fnd_file.log, ' ');
350 fnd_file.put_line(fnd_file.log, 'Total Detail Record Count: '
351 || TO_CHAR(l_tot_count));
352 fnd_file.new_line(fnd_file.log, 1);
353
354
355 p_hdr_count := l_hdr_count;
356 p_dtl_count := l_dtl_count;
357 p_trl_count := l_trl_count;
358 p_per_count := l_per_count;
359 p_err_count := l_err_count;
360
361 IF g_debug
362 THEN
363 l_proc_step := 70;
364 DEBUG ( 'Leaving: '
365 || l_proc_name, l_proc_step);
366 END IF;
367
368 EXCEPTION
369 WHEN others THEN
370 p_hdr_count := NULL;
371 p_dtl_count := NULL;
372 p_trl_count := NULL;
373 p_per_count := NULL;
374 p_err_count := NULL;
375 IF SQLCODE <> hr_utility.hr_error_number
376 THEN
377 debug_others (l_proc_name, l_proc_step);
378 IF g_debug
379 THEN
380 DEBUG ( 'Leaving: '
381 || l_proc_name, -999);
382 END IF;
383 fnd_message.raise_error;
384 ELSE
385 RAISE;
386 END IF;
387
388 END get_ext_rslt_count;
389 --
390
391 -- Procedure creates extract results for a given master ext result id
392 --
393 -- ----------------------------------------------------------------------------
394 -- |----------------------------< create_extract_results >--------------------|
395 -- ----------------------------------------------------------------------------
396
397 PROCEDURE create_extract_results (p_master_ext_rslt_id IN NUMBER
398 ,p_master_request_id IN NUMBER
399 ,p_ext_dfn_id IN NUMBER
400 ,p_request_id IN NUMBER
401 ,p_business_group_id IN NUMBER
402 ,p_program_application_id IN NUMBER
403 ,p_program_id IN NUMBER
404 ,p_effective_date IN DATE
405 )
406 IS
407 --
408 --
409 -- Cursor to fetch extract result details info
410 --
411 CURSOR csr_get_ext_rslt_dtl (c_ext_rslt_id NUMBER)
412 IS
413 SELECT
414 prmy_sort_val,
415 scnd_sort_val,
416 thrd_sort_val,
417 trans_seq_num,
418 rcrd_seq_num,
419 ext_rcd_id,
420 person_id,
421 val_01,
422 val_02,
423 val_03,
424 val_04,
425 val_05,
426 val_06,
427 val_07,
428 val_08,
429 val_09,
430 val_10,
431 val_11,
432 val_12,
433 val_13,
434 val_14,
435 val_15,
436 val_16,
437 val_17,
438 val_19,
439 val_18,
440 val_20,
441 val_21,
442 val_22,
443 val_23,
444 val_24,
445 val_25,
446 val_26,
447 val_27,
448 val_28,
449 val_29,
450 val_30,
451 val_31,
452 val_32,
453 val_33,
454 val_34,
455 val_35,
456 val_36,
457 val_37,
458 val_38,
459 val_39,
460 val_40,
461 val_41,
462 val_42,
463 val_43,
464 val_44,
465 val_45,
466 val_46,
467 val_47,
468 val_48,
469 val_49,
470 val_50,
471 val_51,
472 val_52,
473 val_53,
474 val_54,
475 val_55,
476 val_56,
477 val_57,
478 val_58,
479 val_59,
480 val_60,
481 val_61,
482 val_62,
483 val_63,
484 val_64,
485 val_65,
486 val_66,
487 val_67,
488 val_68,
489 val_69,
490 val_70,
491 val_71,
492 val_72,
493 val_73,
494 val_74,
495 val_75,
496 business_group_id
497 FROM ben_ext_rslt_dtl
498 WHERE ext_rslt_id = c_ext_rslt_id;
499
500 l_ext_rslt_dtl_rec csr_get_ext_rslt_dtl%ROWTYPE;
501
502 --
503 -- Cursor to get record type information
504 --
505
506 CURSOR csr_get_ext_rcd_ht (c_ext_rcd_id NUMBER)
507 IS
508 SELECT rcd_type_cd
509 FROM ben_ext_rcd
510 WHERE ext_rcd_id = c_ext_rcd_id;
511
512 l_ext_rcd_type_cd ben_ext_rcd.rcd_type_cd%TYPE;
513
514 --
515 -- Cursor to get extract result information
516 --
517 CURSOR csr_get_ext_rslt_err (c_ext_rslt_id NUMBER)
518 IS
519 SELECT ext_rslt_err_id,
520 err_num,
521 err_txt,
522 typ_cd,
523 person_id,
524 business_group_id,
525 object_version_number,
526 request_id,
527 program_application_id,
528 program_id,
529 program_update_date,
530 ext_rslt_id
531 FROM ben_ext_rslt_err
532 WHERE ext_rslt_id = c_ext_rslt_id;
533
534 l_ext_rslt_err_rec csr_get_ext_rslt_err%ROWTYPE;
535
536 l_ext_rslt_dtl_id NUMBER;
537 l_ext_rslt_err_id NUMBER;
538 l_ext_rslt_id NUMBER;
539 l_object_version_number NUMBER;
540 l_count_dtl NUMBER := 0;
541 l_count_err NUMBER := 0;
542 l_proc_name VARCHAR2 (80) := g_proc_name
543 || 'create_extract_results';
544 l_proc_step NUMBER;
545 --
546 BEGIN
547 --
548
549 IF g_debug
550 THEN
551 l_proc_step := 10;
552 DEBUG ( 'Entering: '
553 || l_proc_name, l_proc_step);
554 END IF;
555
556 fnd_file.put_line(fnd_file.log, 'Processing Request ID: '
557 || TO_CHAR(p_request_id));
558 fnd_file.put_line(fnd_file.log, ' ');
559
560 l_ext_rslt_id := get_ext_rslt_frm_req
561 (p_request_id => p_request_id
562 ,p_ext_dfn_id => p_ext_dfn_id
563 );
564
565 IF l_ext_rslt_id IS NOT NULL THEN
566
567 -- Create copy of extract detail
568 -- Get the record type code as we do not want
569 -- to create copies of header / trailer unless it is
570 -- of master business group
571
572 IF g_debug
573 THEN
574 l_proc_step := 20;
575 DEBUG (l_proc_name, l_proc_step);
576 END IF;
577
578 fnd_file.put_line(fnd_file.log, 'Extract Result ID: '
579 || TO_CHAR(l_ext_rslt_id));
580 fnd_file.put_line(fnd_file.log, ' ');
581
582 OPEN csr_get_ext_rslt_dtl (l_ext_rslt_id);
583 LOOP
584
585 FETCH csr_get_ext_rslt_dtl INTO l_ext_rslt_dtl_rec;
586 EXIT WHEN csr_get_ext_rslt_dtl%NOTFOUND;
587
588 IF g_debug
589 THEN
590 l_proc_step := 30;
591 DEBUG (l_proc_name, l_proc_step);
592 END IF;
593
594 OPEN csr_get_ext_rcd_ht (l_ext_rslt_dtl_rec.ext_rcd_id);
595 FETCH csr_get_ext_rcd_ht INTO l_ext_rcd_type_cd;
596 CLOSE csr_get_ext_rcd_ht;
597
598 IF g_debug
599 THEN
600 DEBUG ('Record Type CD: '
601 || l_ext_rcd_type_cd);
602 END IF;
603
604 IF (
605 l_ext_rcd_type_cd = 'D'
606 )
607 OR
608 (
609 l_ext_rcd_type_cd IN ('H', 'T') AND
610 l_ext_rslt_dtl_rec.business_group_id = p_business_group_id
611 )
612 THEN
613
614 IF g_debug
615 THEN
616 l_proc_step := 40;
617 DEBUG (l_proc_name, l_proc_step);
618 END IF;
619
620 -- Create a copy
621 ben_ext_rslt_dtl_api.create_ext_rslt_dtl
622 (p_validate => FALSE
623 ,p_ext_rslt_dtl_id => l_ext_rslt_dtl_id
624 ,p_prmy_sort_val => l_ext_rslt_dtl_rec.prmy_sort_val
625 ,p_scnd_sort_val => l_ext_rslt_dtl_rec.scnd_sort_val
626 ,p_thrd_sort_val => l_ext_rslt_dtl_rec.thrd_sort_val
627 ,p_trans_seq_num => l_ext_rslt_dtl_rec.trans_seq_num
628 ,p_rcrd_seq_num => l_ext_rslt_dtl_rec.rcrd_seq_num
629 ,p_ext_rslt_id => p_master_ext_rslt_id
630 ,p_ext_rcd_id => l_ext_rslt_dtl_rec.ext_rcd_id
631 ,p_person_id => l_ext_rslt_dtl_rec.person_id
632 ,p_business_group_id => p_business_group_id
633 ,p_val_01 => l_ext_rslt_dtl_rec.val_01
634 ,p_val_02 => l_ext_rslt_dtl_rec.val_02
635 ,p_val_03 => l_ext_rslt_dtl_rec.val_03
636 ,p_val_04 => l_ext_rslt_dtl_rec.val_04
637 ,p_val_05 => l_ext_rslt_dtl_rec.val_05
638 ,p_val_06 => l_ext_rslt_dtl_rec.val_06
639 ,p_val_07 => l_ext_rslt_dtl_rec.val_07
640 ,p_val_08 => l_ext_rslt_dtl_rec.val_08
641 ,p_val_09 => l_ext_rslt_dtl_rec.val_09
642 ,p_val_10 => l_ext_rslt_dtl_rec.val_10
643 ,p_val_11 => l_ext_rslt_dtl_rec.val_11
644 ,p_val_12 => l_ext_rslt_dtl_rec.val_12
645 ,p_val_13 => l_ext_rslt_dtl_rec.val_13
646 ,p_val_14 => l_ext_rslt_dtl_rec.val_14
647 ,p_val_15 => l_ext_rslt_dtl_rec.val_15
648 ,p_val_16 => l_ext_rslt_dtl_rec.val_16
649 ,p_val_17 => l_ext_rslt_dtl_rec.val_17
650 ,p_val_19 => l_ext_rslt_dtl_rec.val_19
651 ,p_val_18 => l_ext_rslt_dtl_rec.val_18
652 ,p_val_20 => l_ext_rslt_dtl_rec.val_20
653 ,p_val_21 => l_ext_rslt_dtl_rec.val_21
654 ,p_val_22 => l_ext_rslt_dtl_rec.val_22
655 ,p_val_23 => l_ext_rslt_dtl_rec.val_23
656 ,p_val_24 => l_ext_rslt_dtl_rec.val_24
657 ,p_val_25 => l_ext_rslt_dtl_rec.val_25
658 ,p_val_26 => l_ext_rslt_dtl_rec.val_26
659 ,p_val_27 => l_ext_rslt_dtl_rec.val_27
660 ,p_val_28 => l_ext_rslt_dtl_rec.val_28
661 ,p_val_29 => l_ext_rslt_dtl_rec.val_29
662 ,p_val_30 => l_ext_rslt_dtl_rec.val_30
663 ,p_val_31 => l_ext_rslt_dtl_rec.val_31
664 ,p_val_32 => l_ext_rslt_dtl_rec.val_32
665 ,p_val_33 => l_ext_rslt_dtl_rec.val_33
666 ,p_val_34 => l_ext_rslt_dtl_rec.val_34
667 ,p_val_35 => l_ext_rslt_dtl_rec.val_35
668 ,p_val_36 => l_ext_rslt_dtl_rec.val_36
669 ,p_val_37 => l_ext_rslt_dtl_rec.val_37
670 ,p_val_38 => l_ext_rslt_dtl_rec.val_38
671 ,p_val_39 => l_ext_rslt_dtl_rec.val_39
672 ,p_val_40 => l_ext_rslt_dtl_rec.val_40
673 ,p_val_41 => l_ext_rslt_dtl_rec.val_41
674 ,p_val_42 => l_ext_rslt_dtl_rec.val_42
675 ,p_val_43 => l_ext_rslt_dtl_rec.val_43
676 ,p_val_44 => l_ext_rslt_dtl_rec.val_44
677 ,p_val_45 => l_ext_rslt_dtl_rec.val_45
678 ,p_val_46 => l_ext_rslt_dtl_rec.val_46
679 ,p_val_47 => l_ext_rslt_dtl_rec.val_47
680 ,p_val_48 => l_ext_rslt_dtl_rec.val_48
681 ,p_val_49 => l_ext_rslt_dtl_rec.val_49
682 ,p_val_50 => l_ext_rslt_dtl_rec.val_50
683 ,p_val_51 => l_ext_rslt_dtl_rec.val_51
684 ,p_val_52 => l_ext_rslt_dtl_rec.val_52
685 ,p_val_53 => l_ext_rslt_dtl_rec.val_53
686 ,p_val_54 => l_ext_rslt_dtl_rec.val_54
687 ,p_val_55 => l_ext_rslt_dtl_rec.val_55
688 ,p_val_56 => l_ext_rslt_dtl_rec.val_56
689 ,p_val_57 => l_ext_rslt_dtl_rec.val_57
690 ,p_val_58 => l_ext_rslt_dtl_rec.val_58
691 ,p_val_59 => l_ext_rslt_dtl_rec.val_59
692 ,p_val_60 => l_ext_rslt_dtl_rec.val_60
693 ,p_val_61 => l_ext_rslt_dtl_rec.val_61
694 ,p_val_62 => l_ext_rslt_dtl_rec.val_62
695 ,p_val_63 => l_ext_rslt_dtl_rec.val_63
696 ,p_val_64 => l_ext_rslt_dtl_rec.val_64
697 ,p_val_65 => l_ext_rslt_dtl_rec.val_65
698 ,p_val_66 => l_ext_rslt_dtl_rec.val_66
699 ,p_val_67 => l_ext_rslt_dtl_rec.val_67
700 ,p_val_68 => l_ext_rslt_dtl_rec.val_68
701 ,p_val_69 => l_ext_rslt_dtl_rec.val_69
702 ,p_val_70 => l_ext_rslt_dtl_rec.val_70
703 ,p_val_71 => l_ext_rslt_dtl_rec.val_71
704 ,p_val_72 => l_ext_rslt_dtl_rec.val_72
705 ,p_val_73 => l_ext_rslt_dtl_rec.val_73
706 ,p_val_74 => l_ext_rslt_dtl_rec.val_74
707 ,p_val_75 => l_ext_rslt_dtl_rec.val_75
708 ,p_program_application_id => p_program_application_id
709 ,p_program_id => p_program_id
710 ,p_program_update_date => SYSDATE
711 ,p_request_id => p_master_request_id
712 ,p_object_version_number => l_object_version_number
713 );
714 l_count_dtl := l_count_dtl + 1;
715
716
717 END IF; -- End if of record type check ...
718
719 END LOOP;
720 CLOSE csr_get_ext_rslt_dtl;
721
722 --
723 -- Create copy of extract error records if one exist
724 --
725
726 IF g_debug
727 THEN
728 l_proc_step := 50;
729 DEBUG (l_proc_name, l_proc_step);
730 END IF;
731
732 OPEN csr_get_ext_rslt_err (l_ext_rslt_id);
733 LOOP
734
735 FETCH csr_get_ext_rslt_err INTO l_ext_rslt_err_rec;
736 EXIT WHEN csr_get_ext_rslt_err%NOTFOUND;
737
738 -- create a copy of extract result error
739
740 IF g_debug
741 THEN
742 l_proc_step := 60;
743 DEBUG (l_proc_name, l_proc_step);
744 END IF;
745
746 ben_ext_rslt_err_api.create_ext_rslt_err
747 (p_validate => FALSE
748 ,p_ext_rslt_err_id => l_ext_rslt_err_id
749 ,p_err_num => l_ext_rslt_err_rec.err_num
750 ,p_err_txt => l_ext_rslt_err_rec.err_txt
751 ,p_typ_cd => l_ext_rslt_err_rec.typ_cd
752 ,p_person_id => l_ext_rslt_err_rec.person_id
753 ,p_business_group_id => p_business_group_id
754 ,p_object_version_number => l_object_version_number
755 ,p_request_id => p_master_request_id
756 ,p_program_application_id => p_program_application_id
757 ,p_program_id => p_program_id
758 ,p_program_update_date => SYSDATE
759 ,p_ext_rslt_id => p_master_ext_rslt_id
760 ,p_effective_date => p_effective_date
761 );
762
763 l_count_err := l_count_err + 1;
764
765 END LOOP;
766 CLOSE csr_get_ext_rslt_err;
767 END IF; -- End if of ext result id is not null check ...
768
769 IF g_debug
770 THEN
771 DEBUG ('Total Detail Records Created: '
772 || TO_CHAR(l_count_dtl));
773 DEBUG ('Total Error Records Created: '
774 || TO_CHAR(l_count_err));
775 END IF;
776
777 fnd_file.put_line(fnd_file.log, 'Total Detail Records Created: '
778 || TO_CHAR(l_count_dtl));
779 fnd_file.put_line(fnd_file.log, ' ');
780 fnd_file.put_line(fnd_file.log, 'Total Error Records Created: '
781 || TO_CHAR(l_count_err));
782 fnd_file.put_line(fnd_file.log, ' ');
783 fnd_file.put_line(fnd_file.log, 'Finished Processing Request ID: '
784 || TO_CHAR(p_request_id));
785 fnd_file.new_line(fnd_file.log, 1);
786
787 IF g_debug
788 THEN
789 l_proc_step := 70;
790 DEBUG ( 'Leaving: '
791 || l_proc_name, l_proc_step);
792 END IF;
793
794 END create_extract_results;
795 --
796 -- Procedure copy_extract_results for a given set of request ids
797 --
798 -- ----------------------------------------------------------------------------
799 -- |----------------------------< copy_extract_results >----------------------|
800 -- ----------------------------------------------------------------------------
801
802 PROCEDURE copy_extract_results
803 (p_tab_request_ids IN pqp_gb_tp_ext_process.t_request_ids_type
804 ,p_ext_dfn_id IN NUMBER
805 ,p_master_business_group IN NUMBER
806 )
807 IS
808
809 CURSOR csr_get_ext_rslt_info (c_request_id NUMBER)
810 IS
811 SELECT ext_rslt_id
812 ,eff_dt
813 ,ext_strt_dt
814 ,ext_end_dt
815 ,drctry_name
816 ,output_name
817 FROM ben_ext_rslt
818 WHERE request_id = c_request_id
819 AND ext_dfn_id = p_ext_dfn_id
820 AND business_group_id = p_master_business_group;
821
822 --
823 -- Cursor to get file id
824 --
825 CURSOR csr_get_ext_param
826 IS
827 SELECT ext_file_id
828 ,output_name
829 ,apnd_rqst_id_flag
830 ,kickoff_wrt_prc_flag
831 FROM ben_ext_dfn
832 WHERE ext_dfn_id = p_ext_dfn_id;
833
834 l_ext_param_rec csr_get_ext_param%ROWTYPE;
835 l_ext_rslt_info_rec csr_get_ext_rslt_info%ROWTYPE;
836 l_master_ext_rslt_id NUMBER;
837 l_ext_rslt_id NUMBER;
838 l_master_request_id NUMBER := fnd_global.conc_request_id;
839 l_master_prog_appl_id NUMBER := fnd_global.prog_appl_id;
840 l_master_program_id NUMBER := fnd_global.conc_program_id;
841 l_tab_request_ids pqp_gb_tp_ext_process.t_request_ids_type
842 := p_tab_request_ids;
843 i NUMBER;
844 l_effective_date DATE;
845 l_object_version_number NUMBER;
846 l_hdr_count NUMBER := 0;
847 l_dtl_count NUMBER := 0;
848 l_trl_count NUMBER := 0;
849 l_per_count NUMBER := 0;
850 l_err_count NUMBER := 0;
851 l_tot_count NUMBER := 0;
852 l_output_name VARCHAR2(200); -- Do not use type
853 l_request_id NUMBER;
854 l_val_02 ben_ext_rslt_dtl.val_02%TYPE;
855 l_val_03 ben_ext_rslt_dtl.val_03%TYPE;
856 l_proc_name VARCHAR2 (80) := g_proc_name
857 || 'copy_extract_results';
858 l_proc_step NUMBER;
859
860 BEGIN
861
862 IF g_debug
863 THEN
864 l_proc_step := 10;
865 DEBUG ( 'Entering: '
866 || l_proc_name, l_proc_step);
867 END IF;
868 --
869 -- Create an extract result first for this request id
870 --
871 i := l_tab_request_ids.FIRST;
872
873 WHILE i IS NOT NULL
874 LOOP
875
876 IF g_debug
877 THEN
878 l_proc_step := 20;
879 DEBUG (l_proc_name, l_proc_step);
880 END IF;
881
882 OPEN csr_get_ext_rslt_info (l_tab_request_ids(i));
883 FETCH csr_get_ext_rslt_info INTO l_ext_rslt_info_rec;
884 IF csr_get_ext_rslt_info%FOUND THEN
885
886 IF g_debug
887 THEN
888 DEBUG ('Master Business Group Request ID: '
889 || TO_CHAR(l_tab_request_ids(i)));
890 END IF;
891
892 fnd_file.put_line(fnd_file.log, 'Master Business Group Request ID: '
893 || TO_CHAR(l_tab_request_ids(i)));
894 fnd_file.put_line(fnd_file.log, ' ');
895 CLOSE csr_get_ext_rslt_info;
896 EXIT;
897 END IF; -- End if of ext result found check ...
898 CLOSE csr_get_ext_rslt_info;
899
900 i := l_tab_request_ids.NEXT(i);
901 END LOOP;
902
903 IF l_ext_rslt_info_rec.ext_rslt_id IS NULL THEN
904 -- Error
905 -- There is no result for master business group
906 fnd_message.set_name ('BEN', 'BEN_91873_EXT_NOT_FOUND');
907 fnd_file.put_line(fnd_file.log, 'Error: '
908 || fnd_message.get);
909 fnd_file.put_line(fnd_file.log, ' ');
910 fnd_message.raise_error;
911 END IF; -- End if of ext rslt id null check ...
912
913 --
914 -- Fetch the file id for the extract
915 --
916 IF g_debug
917 THEN
918 l_proc_step := 30;
919 DEBUG (l_proc_name, l_proc_step);
920 END IF;
921
922 OPEN csr_get_ext_param;
923 FETCH csr_get_ext_param INTO l_ext_param_rec;
924
925 IF csr_get_ext_param%NOTFOUND THEN
926 fnd_message.set_name ('BEN', 'BEN_91873_EXT_NOT_FOUND');
927 fnd_file.put_line(fnd_file.log, 'Error: '
928 || fnd_message.get);
929 fnd_file.put_line(fnd_file.log, ' ');
930 CLOSE csr_get_ext_param;
931 fnd_message.raise_error;
932 END IF; -- End if of row not found check ...
933 CLOSE csr_get_ext_param;
934
935 IF g_debug
936 THEN
937 l_proc_step := 40;
938 DEBUG (l_proc_name, l_proc_step);
939 END IF;
940
941 IF l_ext_param_rec.output_name IS NOT NULL AND
942 l_ext_param_rec.apnd_rqst_id_flag = 'Y'
943 THEN
944 --
945 l_output_name := l_ext_param_rec.output_name || '.' ||
946 TO_CHAR(l_master_request_id);
947 --
948 ELSE
949 l_output_name := 'outfile';
950 END IF; -- End if of output name not null check ...
951
952 IF g_debug
953 THEN
954 DEBUG ('Master Request ID: '
955 || TO_CHAR(l_master_request_id));
956 DEBUG ('Output File Name: '
957 || l_output_name);
958 l_proc_step := 40;
959 DEBUG (l_proc_name, l_proc_step);
960 END IF;
961
962 fnd_file.put_line(fnd_file.log, 'Master Request ID: '
963 || TO_CHAR(l_master_request_id));
964 fnd_file.put_line(fnd_file.log, ' ');
965
966 ben_ext_rslt_api.create_ext_rslt
967 (p_validate => FALSE
968 ,p_ext_rslt_id => l_master_ext_rslt_id
969 ,p_run_strt_dt => SYSDATE
970 ,p_run_end_dt => NULL
971 ,p_ext_stat_cd => 'X'
972 ,p_eff_dt => l_ext_rslt_info_rec.eff_dt
973 ,p_ext_strt_dt => l_ext_rslt_info_rec.ext_strt_dt
974 ,p_ext_end_dt => l_ext_rslt_info_rec.ext_end_dt
975 ,p_output_name => l_output_name
976 ,p_drctry_name => l_ext_rslt_info_rec.drctry_name
977 ,p_ext_dfn_id => p_ext_dfn_id
978 ,p_business_group_id => p_master_business_group
979 ,p_program_application_id => l_master_prog_appl_id
980 ,p_program_id => l_master_program_id
981 ,p_program_update_date => SYSDATE
982 ,p_request_id => l_master_request_id
983 ,p_object_version_number => l_object_version_number
984 ,p_effective_date => l_ext_rslt_info_rec.eff_dt);
985
986 IF g_debug
987 THEN
988 DEBUG ('Master Result ID: '
989 || TO_CHAR(l_master_ext_rslt_id));
990 l_proc_step := 50;
991 DEBUG (l_proc_name, l_proc_step);
992 END IF;
993
994 fnd_file.put_line(fnd_file.log, 'Master Result ID: '
995 || TO_CHAR(l_master_ext_rslt_id));
996 fnd_file.put_line(fnd_file.log, ' ');
997
998 i := l_tab_request_ids.FIRST;
999
1000 WHILE i IS NOT NULL
1001 LOOP
1002
1003 IF g_debug
1004 THEN
1005 DEBUG ('Request ID: '
1006 || TO_CHAR(l_tab_request_ids(i)));
1007 l_proc_step := 60;
1008 DEBUG (l_proc_name, l_proc_step);
1009 END IF;
1010
1011 create_extract_results
1012 (p_master_ext_rslt_id => l_master_ext_rslt_id
1013 ,p_master_request_id => l_master_request_id
1014 ,p_ext_dfn_id => p_ext_dfn_id
1015 ,p_request_id => l_tab_request_ids(i)
1016 ,p_business_group_id => p_master_business_group
1017 ,p_program_application_id => l_master_prog_appl_id
1018 ,p_program_id => l_master_program_id
1019 ,p_effective_date => l_ext_rslt_info_rec.eff_dt
1020 );
1021
1022 i := l_tab_request_ids.NEXT(i);
1023 END LOOP;
1024
1025 --
1026 -- Get extract result count
1027 --
1028
1029 IF g_debug
1030 THEN
1031 l_proc_step := 70;
1032 DEBUG (l_proc_name, l_proc_step);
1033 END IF;
1034
1035 get_ext_rslt_count
1036 (p_ext_rslt_id => l_master_ext_rslt_id
1037 ,p_ext_file_id => l_ext_param_rec.ext_file_id
1038 ,p_hdr_count => l_hdr_count
1039 ,p_dtl_count => l_dtl_count
1040 ,p_trl_count => l_trl_count
1041 ,p_per_count => l_per_count
1042 ,p_err_count => l_err_count
1043 ,p_tot_count => l_tot_count
1044 );
1045
1046 IF g_debug
1047 THEN
1048 l_proc_step := 80;
1049 DEBUG (l_proc_name, l_proc_step);
1050 END IF;
1051
1052 --
1053 -- Update trailer data element with the correct value
1054 --
1055 ben_ext_thread.g_ext_rslt_id := l_master_ext_rslt_id;
1056 l_val_02 := pqp_gb_tp_pension_extracts.get_total_number_data_records(' ');
1057 l_val_03 := pqp_gb_tp_pension_extracts.get_total_number_data_records('1');
1058
1059 IF g_debug
1060 THEN
1061 DEBUG ('Total Number of Data Records: '
1062 || l_val_02);
1063 DEBUG ('Total Number of Type 1 Data Records: '
1064 || l_val_03);
1065 l_proc_step := 90;
1066 DEBUG (l_proc_name, l_proc_step);
1067 END IF;
1068
1069 UPDATE ben_ext_rslt_dtl rslt
1070 SET val_02 = l_val_02
1071 ,val_03 = l_val_03
1072 WHERE ext_rslt_id = l_master_ext_rslt_id
1073 AND EXISTS ( SELECT 1 FROM ben_ext_rcd rcd
1074 WHERE rcd.ext_rcd_id = rslt.ext_rcd_id
1075 AND rcd.rcd_type_cd = 'T'
1076 );
1077 --
1078 -- update master ext rslt with the count details
1079 --
1080 IF l_err_count > 0 THEN
1081 --
1082 -- Call update API to update Extract Run Rslts row here
1083 -- Extract status - Completed with Errors
1084 --
1085
1086 IF g_debug
1087 THEN
1088 l_proc_step := 100;
1089 DEBUG (l_proc_name, l_proc_step);
1090 END IF;
1091
1092 ben_ext_rslt_api.update_ext_rslt
1093 (p_validate => FALSE
1094 ,p_ext_rslt_id => l_master_ext_rslt_id
1095 ,p_run_end_dt => SYSDATE
1096 ,p_ext_stat_cd => 'E'
1097 ,p_tot_rec_num => l_tot_count
1098 ,p_tot_per_num => l_per_count
1099 ,p_tot_err_num => l_err_count
1100 ,p_program_application_id => l_master_prog_appl_id
1101 ,p_program_id => l_master_program_id
1102 ,p_program_update_date => SYSDATE
1103 ,p_request_id => l_master_request_id
1104 ,p_object_version_number => l_object_version_number
1105 ,p_effective_date => l_ext_rslt_info_rec.eff_dt);
1106 --
1107
1108 ELSE
1109 --
1110 IF g_debug
1111 THEN
1112 l_proc_step := 110;
1113 DEBUG (l_proc_name, l_proc_step);
1114 END IF;
1115
1116 ben_ext_rslt_api.update_ext_rslt
1117 (p_validate => FALSE
1118 ,p_ext_rslt_id => l_master_ext_rslt_id
1119 ,p_run_end_dt => SYSDATE
1120 ,p_ext_stat_cd => 'S'
1121 ,p_tot_rec_num => l_tot_count
1122 ,p_tot_per_num => l_per_count
1123 ,p_tot_err_num => l_err_count
1124 ,p_program_application_id => l_master_prog_appl_id
1125 ,p_program_id => l_master_program_id
1126 ,p_program_update_date => SYSDATE
1127 ,p_request_id => l_master_request_id
1128 ,p_object_version_number => l_object_version_number
1129 ,p_effective_date => l_ext_rslt_info_rec.eff_dt);
1130
1131 END IF; -- End if of err count > 0 check ...
1132
1133 IF l_master_request_id IS NOT NULL THEN
1134
1135
1136 IF l_ext_param_rec.kickoff_wrt_prc_flag = 'Y' THEN
1137 --
1138 IF g_debug
1139 THEN
1140 l_proc_step := 120;
1141 DEBUG (l_proc_name, l_proc_step);
1142 END IF;
1143
1144 l_request_id := fnd_request.submit_request
1145 (application => 'BEN',
1146 program => 'BENXWRIT',
1147 description => NULL,
1148 sub_request => FALSE,
1149 argument1 => l_master_ext_rslt_id);
1150 --
1151 END IF; -- End if of kick off write process flag check ...
1152 END IF; -- End if of master request id not null check ...
1153
1154 IF g_debug
1155 THEN
1156 l_proc_step := 130;
1157 DEBUG ('Leaving: '
1158 || l_proc_name, l_proc_step);
1159 END IF;
1160
1161 END copy_extract_results;
1162 --
1163 -- Procedure copy_extract_process is a wrapper for copy_extract_results
1164 -- so that it can be used as a concurrent program
1165 --
1166 -- ----------------------------------------------------------------------------
1167 -- |----------------------------< copy_extract_process >----------------------|
1168 -- ----------------------------------------------------------------------------
1169 PROCEDURE copy_extract_process (errbuf OUT NOCOPY VARCHAR2
1170 ,retcode OUT NOCOPY NUMBER
1171 ,p_ext_dfn_id IN NUMBER
1172 ,p_business_group_id IN NUMBER
1173 ,p_request_id_1 IN NUMBER
1174 ,p_request_id_2 IN NUMBER
1175 ,p_request_id_3 IN NUMBER
1176 ,p_request_id_4 IN NUMBER
1177 ,p_request_id_5 IN NUMBER
1178 )
1179 IS
1180 --
1181 l_tab_request_ids pqp_gb_tp_ext_process.t_request_ids_type;
1182 i NUMBER;
1183 j NUMBER;
1184 l_request_id NUMBER;
1185 l_proc_name VARCHAR2 (80) := g_proc_name
1186 || 'copy_extract_results';
1187 l_proc_step NUMBER;
1188
1189 --
1190 BEGIN
1191 --
1192 IF g_debug
1193 THEN
1194 l_proc_step := 10;
1195 DEBUG ('Entering: '
1196 || l_proc_name, l_proc_step);
1197 END IF;
1198
1199 i := 0;
1200 j := 0;
1201
1202 LOOP
1203 i := i + 1;
1204 SELECT DECODE
1205 (i
1206 ,1, p_request_id_1
1207 ,2, p_request_id_2
1208 ,3, p_request_id_3
1209 ,4, p_request_id_4
1210 ,5, p_request_id_5
1211 ,NULL
1212 )
1213 INTO l_request_id
1214 FROM dual;
1215
1216 IF l_request_id IS NOT NULL THEN
1217
1218 IF g_debug
1219 THEN
1220 DEBUG ('Include Request ID: '
1221 || TO_CHAR(l_request_id));
1222 END IF;
1223
1224 j := j + 1;
1225 l_tab_request_ids(j) := l_request_id;
1226 END IF; -- End if of request id not null check ...
1227
1228 EXIT WHEN i >= 5;
1229 END LOOP;
1230
1231 IF l_tab_request_ids.COUNT > 1 THEN
1232
1233 IF g_debug
1234 THEN
1235 l_proc_step := 20;
1236 DEBUG (l_proc_name, l_proc_step);
1237 END IF;
1238
1239 copy_extract_results (p_tab_request_ids => l_tab_request_ids
1240 ,p_ext_dfn_id => p_ext_dfn_id
1241 ,p_master_business_group => p_business_group_id
1242 );
1243 END IF;
1244 IF g_debug
1245 THEN
1246 l_proc_step := 30;
1247 DEBUG ('Leaving: '
1248 || l_proc_name, l_proc_step);
1249 END IF;
1250
1251 END copy_extract_process;
1252
1253 --
1254 -- set_location_code
1255 --
1256 PROCEDURE set_location_code
1257 (p_udt_id IN NUMBER
1258 ,p_value IN VARCHAR2
1259 ,p_business_group_id IN NUMBER
1260 ) IS
1261
1262 c_effective_date CONSTANT DATE := to_date('01/01/1951','dd/mm/yyyy');
1263
1264 CURSOR csr_user_col IS
1265 SELECT user_column_id
1266 FROM pay_user_columns
1267 WHERE user_table_id = p_udt_id
1268 AND user_column_name = 'Location Code'
1269 -- AND legislation_code = 'GB' -- Commented out for the bug 9963239
1270 -- AND business_group_id IS NULL; -- Commented out for the bug 9963239
1271 AND (legislation_code = 'GB' -- Added for the bug 9963239
1272 OR business_group_id = p_business_group_id); -- Added for the bug 9963239
1273
1274
1275
1276 CURSOR csr_user_row IS
1277 SELECT user_row_id
1278 FROM pay_user_rows_f
1279 WHERE user_table_id = p_udt_id
1280 AND row_low_range_or_name = 'Criteria'
1281 AND c_effective_date BETWEEN effective_start_date
1282 AND effective_end_date
1283 -- AND legislation_code = 'GB' -- Commented out for the bug 9963239
1284 -- AND business_group_id IS NULL; -- Commented out for the bug 9963239
1285 AND (legislation_code = 'GB' -- Added for the bug 9963239
1286 OR business_group_id = p_business_group_id); -- Added for the bug 9963239
1287
1288
1289 CURSOR csr_udt_location_code IS
1290 SELECT uci.rowid, uci.*
1291 FROM pay_user_columns puc
1292 ,pay_user_rows_f pur
1293 ,pay_user_column_instances_f uci
1294 WHERE -- User Column
1295 puc.user_table_id = p_udt_id
1296 AND puc.user_column_name = 'Location Code'
1297 -- AND puc.legislation_code = 'GB' -- Commented out for the bug 9963239
1298 -- AND puc.business_group_id IS NULL -- Commented out for the bug 9963239
1299 AND (puc.legislation_code = 'GB' -- Added for the bug 9963239
1300 OR puc.business_group_id = p_business_group_id) -- Added for the bug 9963239
1301 -- User Row
1302 AND pur.row_low_range_or_name = 'Criteria'
1303 AND c_effective_date BETWEEN pur.effective_start_date
1304 AND pur.effective_end_date
1305 -- AND pur.legislation_code = 'GB' -- Commented out for the bug 9963239
1306 -- AND pur.business_group_id IS NULL -- Commented out for the bug 9963239
1307 AND (pur.legislation_code = 'GB' -- Added for the bug 9963239
1308 OR pur.business_group_id = p_business_group_id) -- Added for the bug 9963239
1309 -- Join column and Col Instance
1310 AND uci.user_column_id = puc.user_column_id
1311 -- join row and Col Instance
1312 AND uci.user_row_id = pur.user_row_id
1313 -- Filter instance on date and BG
1314 AND uci.business_group_id = p_business_group_id
1315 AND ((c_effective_date BETWEEN uci.effective_start_date
1316 AND uci.effective_end_date
1317 )
1318 OR
1319 (uci.effective_start_date > c_effective_date
1320 )
1321 )
1322 ORDER BY uci.effective_start_date ASC;
1323
1324 l_udt_row csr_udt_location_code%ROWTYPE;
1325
1326 l_proc_name VARCHAR2 (80) := g_proc_name
1327 || 'set_location_code';
1328
1329
1330 BEGIN
1331
1332 --hr_utility.trace_on(NULL, 'REQID');
1333 --g_debug := hr_utility.debug_enabled;
1334
1335 IF g_debug THEN
1336 debug('Entering :'||l_proc_name, 10);
1337 debug('p_udt_id :'||to_char(p_udt_id), 20);
1338 debug('p_business_group_id :'||to_char(p_business_group_id), 30);
1339 debug('p_value :'||p_value, 40);
1340 END IF;
1341
1342 FOR l_udt_row IN csr_udt_location_code
1343 LOOP
1344
1345 PAY_USER_COLUMN_INSTANCES_PKG.delete_row(l_udt_row.rowid);
1346
1347 IF g_debug THEN
1348 debug('Deleted row in loop', 50);
1349 END IF;
1350 /* Use the new API when made available, currently only in NOV03 FP
1351 pay_user_column_instance_api.delete_user_column_instance
1352 (p_validate => FALSE
1353 ,p_effective_date => l_udt_row.effective_start_date
1354 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1355 ,p_datetrack_update_mode => hr_api.g_zap
1356 ,p_object_version_number => l_udt_row.object_version_number
1357 ,p_effective_start_date => l_udt_row.effective_start_date
1358 ,p_effective_end_date => l_udt_row.effective_start_date
1359 );
1360 */
1361
1362 END LOOP;
1363
1364 l_udt_row := NULL;
1365
1366 OPEN csr_user_col;
1367 FETCH csr_user_col INTO l_udt_row.user_column_id;
1368 CLOSE csr_user_col;
1369
1370 OPEN csr_user_row;
1371 FETCH csr_user_row INTO l_udt_row.user_row_id;
1372 CLOSE csr_user_row;
1373
1374 IF g_debug THEN
1375 debug('User Row Id :'||to_char(l_udt_row.user_row_id), 60);
1376 debug('User Col Id :'||to_char(l_udt_row.user_column_id), 70);
1377 END IF;
1378
1379 -- Now insert a new row with the correct location code
1380 PAY_USER_COLUMN_INSTANCES_PKG.insert_row
1381 (p_rowid => l_udt_row.rowid
1382 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1383 ,p_effective_start_date => c_effective_date
1384 ,p_effective_end_date => hr_api.g_eot
1385 ,p_user_row_id => l_udt_row.user_row_id
1386 ,p_user_column_id => l_udt_row.user_column_id
1387 ,p_business_group_id => p_business_group_id
1388 ,p_legislation_code => NULL
1389 ,p_legislation_subgroup => NULL
1390 ,p_value => p_value
1391 );
1392
1393 /* Use the new API when made available, currently only in NOV03 FP
1394 pay_user_column_instance_api.create_user_column_instance
1395 (p_validate => FALSE
1396 ,p_effective_date => c_effective_date
1397 ,p_user_row_id => l_udt_row.user_row_id
1398 ,p_user_column_id => l_udt_row.user_column_id
1399 ,p_value => p_value
1400 ,p_business_group_id => p_business_group_id
1401 ,p_legislation_code => NULL
1402 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1403 ,p_object_version_number => l_udt_row.object_version_number
1404 ,p_effective_start_date => l_udt_row.effective_start_date
1405 ,p_effective_end_date => l_udt_row.effective_end_date
1406 );
1407 */
1408
1409 IF g_debug THEN
1410 debug('Leaving :'||l_proc_name, 90);
1411 END IF;
1412 --hr_utility.trace_off;
1413
1414 EXCEPTION
1415 WHEN OTHERS THEN
1416 --hr_utility.trace_off;
1417 RAISE;
1418 END set_location_code;
1419
1420 --
1421 -- set_cross_person_records
1422 --
1423 PROCEDURE set_cross_person_records
1424 (p_business_group_id IN NUMBER
1425 ,p_effective_date IN DATE
1426 ,p_master_request_id IN NUMBER
1427 -- Bugfix 3671727:ENH2 :Added new param
1428 ,p_ext_dfn_id IN VARCHAR2
1429 ) IS
1430
1431 PRAGMA AUTONOMOUS_TRANSACTION;
1432
1433 l_wait_success BOOLEAN := FALSE;
1434
1435 l_phase VARCHAR2(80);
1436 l_status VARCHAR2(80);
1437 l_dev_phase VARCHAR2(80);
1438 l_dev_status VARCHAR2(80);
1439 l_message VARCHAR2(80);
1440
1441 l_multiproc_data pqp_gb_t1_pension_extracts.csr_multiproc_data%ROWTYPE;
1442
1443 l_proc_name VARCHAR2 (80) := g_proc_name
1444 || 'set_cross_person_records';
1445
1446
1447 BEGIN -- set_cross_person_records
1448
1449 IF g_debug THEN
1450 debug('Entering :'||l_proc_name, 10);
1451 END IF;
1452
1453 -- Get the master row data
1454 OPEN pqp_gb_t1_pension_extracts.csr_multiproc_data
1455 (p_record_type => 'M'
1456 ,p_national_identifier => NULL
1457 -- Bugfix 3671727:ENH1: Now passing lea number
1458 ,p_lea_number => g_lea_number
1459 ,p_ext_dfn_id => p_ext_dfn_id
1460 );
1461 FETCH pqp_gb_t1_pension_extracts.csr_multiproc_data INTO l_multiproc_data;
1462
1463 IF pqp_gb_t1_pension_extracts.csr_multiproc_data%FOUND THEN
1464
1465 IF g_debug THEN
1466 debug('Found row in csr_multiproc_data', 20);
1467 END IF;
1468
1469 -- Found, now chk for status
1470 IF NVL(l_multiproc_data.processing_status, 'U') <> 'P' THEN
1471
1472 l_wait_success := TRUE;
1473
1474 ELSE -- Another request might be running currently
1475
1476 IF g_debug THEN
1477 debug(l_proc_name, 30);
1478 END IF;
1479
1480 -- Verify by chking the status of the request id
1481 -- stored in the master bg row
1482 l_wait_success := fnd_concurrent.get_request_status
1483 (request_id => l_multiproc_data.request_id
1484 ,phase => l_phase -- OUT
1485 ,status => l_status -- OUT
1486 ,dev_phase => l_dev_phase -- OUT
1487 ,dev_status => l_dev_status -- OUT
1488 ,message => l_message -- OUT
1489 );
1490
1491 IF l_wait_success
1492 AND
1493 l_dev_phase = 'COMPLETE' THEN
1494
1495 l_wait_success := TRUE;
1496 ELSE
1497 l_wait_success := FALSE;
1498 END IF;
1499
1500 END IF; -- NVL(l_multiproc_data.processing_status, 'U') <> 'P' THEN
1501
1502 IF l_wait_success THEN
1503 UPDATE pqp_ext_cross_person_records
1504 SET business_group_id = p_business_group_id
1505 ,effective_start_date = p_effective_date
1506 ,request_id = nvl(p_master_request_id, g_master_request_id)
1507 ,processing_status = 'P' -- Processing
1508 ,last_updated_by = fnd_global.user_id
1509 ,last_update_date = SYSDATE
1510 ,last_update_login = fnd_global.login_id
1511 ,object_version_number = (object_version_number + 1)
1512 WHERE record_type = 'M'
1513 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
1514 AND ext_dfn_id = p_ext_dfn_id
1515 AND lea_number = g_lea_number;
1516
1517 IF g_debug THEN
1518 debug('No of rows updated :'||to_char(SQL%ROWCOUNT), 40);
1519 debug('Updated master BG row with :'||to_char(p_business_group_id), 50);
1520 END IF;
1521
1522 ELSE
1523 -- Raise error with message that there is already
1524 -- another TPA Master Extract Process running,
1525 -- can not submit a second one
1526 CLOSE pqp_gb_t1_pension_extracts.csr_multiproc_data;
1527
1528 -- Exiting because another process is running and you can not submit twice
1529 fnd_message.set_name('PQP', 'PQP_230036_MULTIPLE_TP_EXT_ERR');
1530 fnd_file.put_line(fnd_file.log, fnd_message.get);
1531 fnd_message.raise_error;
1532 RETURN;
1533
1534 END IF;
1535
1536 l_wait_success := NULL;
1537
1538 ELSE -- NOTFOUND
1539
1540 -- Not found, insert a new master bg row
1541 -- Bugfix 3671727:ENH1:ENH2 : Added ext_dfn_id and lea_number
1542 INSERT INTO pqp_ext_cross_person_records
1543 (record_type
1544 ,ext_dfn_id
1545 ,lea_number
1546 ,business_group_id
1547 ,effective_start_date
1548 ,request_id
1549 ,processing_status
1550 ,created_by
1551 ,creation_date
1552 ,object_version_number
1553 )
1554 VALUES
1555 ('M' -- Master BG row
1556 ,p_ext_dfn_id
1557 ,g_lea_number
1558 ,p_business_group_id
1559 ,p_effective_date
1560 ,nvl(p_master_request_id, g_master_request_id)
1561 ,'P' -- Processing
1562 ,fnd_global.user_id
1563 ,SYSDATE
1564 ,1
1565 );
1566
1567 IF g_debug THEN
1568 debug('Inserted master BG row with :'||to_char(p_business_group_id), 60);
1569 END IF;
1570
1571 END IF; -- pqp_gb_t1_pension_extracts.csr_multiproc_data%FOUND THEN
1572
1573 -- Close the cursor if its still open
1574 IF pqp_gb_t1_pension_extracts.csr_multiproc_data%ISOPEN THEN
1575 CLOSE pqp_gb_t1_pension_extracts.csr_multiproc_data;
1576 END IF;
1577
1578 -- Step 3.2) Updating multiproc data
1579 UPDATE pqp_ext_cross_person_records
1580 SET processing_status = 'U'
1581 ,last_updated_by = fnd_global.user_id
1582 ,last_update_date = SYSDATE
1583 ,last_update_login = fnd_global.login_id
1584 ,object_version_number = (object_version_number + 1)
1585 WHERE record_type = 'X'
1586 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
1587 AND ext_dfn_id = p_ext_dfn_id
1588 AND lea_number = g_lea_number;
1589
1590 -- Commiting here before any further processing
1591 -- coz we have updated data in the multiproc table
1592 -- and this will be used by all the child processes
1593 COMMIT;
1594
1595 IF g_debug THEN
1596 debug('Leaving :'||l_proc_name, 90);
1597 END IF;
1598 RETURN;
1599
1600 END set_cross_person_records;
1601
1602 --
1603 -- fail_current_extract_run
1604 --
1605 PROCEDURE fail_current_extract_run
1606 (p_ext_dfn_id IN NUMBER
1607 ) IS
1608
1609 l_proc_name VARCHAR2 (80) := g_proc_name
1610 || 'fail_current_extract_run';
1611
1612 BEGIN
1613
1614 IF g_debug THEN
1615 debug('Entering :'||l_proc_name, 10);
1616 END IF;
1617
1618 -- Error out as the current BG is not an LEA
1619 -- Also, we need to reset the master BG row in multiproc data
1620 -- table to 'E'
1621 UPDATE pqp_ext_cross_person_records
1622 SET processing_status = 'E' -- Error
1623 ,last_updated_by = fnd_global.user_id
1624 ,last_update_date = SYSDATE
1625 ,last_update_login = fnd_global.login_id
1626 ,object_version_number = (object_version_number + 1)
1627 WHERE record_type = 'M'
1628 -- Bugfix 3671727:ENH1 : Added these AND clauses
1629 AND ext_dfn_id = p_ext_dfn_id
1630 AND lea_number = g_lea_number;
1631
1632 COMMIT;
1633
1634 IF g_debug THEN
1635 debug('Leaving :'||l_proc_name, 90);
1636 END IF;
1637
1638 RETURN;
1639
1640 END fail_current_extract_run;
1641 --
1642 -- tpa_extract_process
1643 --
1644 PROCEDURE tpa_extract_process
1645 (errbuf OUT NOCOPY VARCHAR2
1646 ,retcode OUT NOCOPY NUMBER
1647 ,p_ext_dfn_id IN NUMBER
1648 ,p_effective_date IN VARCHAR2
1649 ,p_business_group_id IN NUMBER
1650 ,p_lea_yn IN VARCHAR2
1651 ,p_argument1 IN VARCHAR2
1652 ,p_organization_id IN NUMBER
1653 -- Bugfix 3671727:ENH1 : Added new param
1654 ,p_argument2 IN VARCHAR2
1655 ,p_lea_number IN VARCHAR2
1656 ) IS
1657
1658 CURSOR csr_location_code IS
1659 SELECT loc.location_code
1660 ,loc.location_id
1661 ,lei.lei_information6 lea_number
1662 FROM hr_organization_units_v org
1663 ,hr_locations_all loc
1664 ,hr_location_extra_info lei
1665 WHERE org.organization_id = p_organization_id
1666 AND loc.location_id = org.location_id
1667 AND lei.location_id(+) = loc.location_id
1668 AND nvl(lei.information_type,'PQP_GB_EDU_ESTB_INFO') = 'PQP_GB_EDU_ESTB_INFO';
1669
1670 CURSOR csr_bg_name(p_business_group_id IN NUMBER) IS
1671 SELECT name
1672 FROM per_business_groups_perf
1673 WHERE business_group_id = p_business_group_id;
1674
1675 PROGRAM_FAILURE CONSTANT NUMBER := 2 ;
1676 PROGRAM_SUCCESS CONSTANT NUMBER := 0 ;
1677
1678 l_location_code hr_locations_all.location_code%TYPE := NULL;
1679 l_location_id hr_locations_all.location_id%TYPE := NULL;
1680 l_curr_bg_id per_all_people_f.business_group_id%TYPE;
1681 l_request_id fnd_concurrent_requests.request_id%TYPE;
1682 l_retcode NUMBER := PROGRAM_SUCCESS;
1683 l_wait_success BOOLEAN := FALSE;
1684 l_effective_date DATE;
1685
1686 l_phase VARCHAR2(80);
1687 l_status VARCHAR2(80);
1688 l_dev_phase VARCHAR2(80);
1689 l_dev_status VARCHAR2(80);
1690 l_message VARCHAR2(80);
1691 l_err_msg fnd_new_messages.message_text%TYPE;
1692
1693 l_lea_details pqp_gb_tp_pension_extracts.csr_lea_details%ROWTYPE;
1694 l_lea_dets_frm_bg pqp_gb_tp_pension_extracts.csr_lea_details%ROWTYPE;
1695 l_multiproc_data pqp_gb_t1_pension_extracts.csr_multiproc_data%ROWTYPE;
1696 l_lea_dets_by_loc pqp_gb_tp_pension_extracts.csr_lea_details_by_loc%ROWTYPE;
1697
1698 l_ext_udt_id pay_user_tables.user_table_id%TYPE;
1699 l_bg_name per_business_groups_perf.name%TYPE := NULL;
1700
1701 l_proc_name VARCHAR2 (80) := g_proc_name
1702 || 'tpa_extract_process';
1703
1704 BEGIN -- tpa_extract_process
1705
1706 --hr_utility.trace_on(NULL, 'REQID');
1707 --g_debug := hr_utility.debug_enabled;
1708
1709 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1710
1711 -- Step 1) Get the master request id
1712 g_master_request_id := fnd_global.conc_request_id;
1713
1714 IF g_debug THEN
1715 debug('Entering :'||l_proc_name, 10);
1716 debug('g_master_request_id :'||to_char(g_master_request_id), 20);
1717 END IF;
1718
1719 -- Step 2) Set report type, LEA or Non-LEA
1720 -- If its the LEA report then we set the location
1721 -- code as NULL in UDT, otherwise, we find the
1722 -- location code, find the UDT name and set the
1723 -- location code in the UDT for the Non-LEA report.
1724
1725 -- Step 2.1) Check report type
1726
1727 -- Bugifix in 115.5
1728 -- Checking if its an LEA report using the p_lea_yn flag instead of
1729 -- comparing the org id and bg id coz for non lea report
1730 -- we might have a situation where the location has been
1731 -- linked to the BG org for using with non lea report
1732 IF p_lea_yn = 'Y' THEN
1733
1734 g_report_type := 'LEA';
1735
1736 -- Clear the location code from the UDT as we're
1737 -- running the LEA report
1738 l_location_code := NULL;
1739 l_location_id := NULL;
1740
1741 -- Bugfix 3671727:ENH1 :Setting the LEA number
1742 g_lea_number := p_lea_number;
1743
1744 ELSE -- Check report type
1745
1746 IF g_debug THEN
1747 debug(l_proc_name, 30);
1748 END IF;
1749
1750 g_report_type := 'NONLEA';
1751
1752 -- Step 2.2) Get the location id for this organization
1753 -- and set the location code in the UDT
1754 -- so the non-Lea report gets executed
1755 -- Bugfix 3671727:ENH1 : Now getting the location id and LEA number
1756 OPEN csr_location_code;
1757 FETCH csr_location_code INTO l_location_code, l_location_id, g_lea_number;
1758 CLOSE csr_location_code;
1759
1760 -- Bugfix 3671727:ENH1 : If the LEA number was NULL on location EIT then
1761 -- get it from the following in that order
1762 -- 1) Org linked to that location
1763 -- 2) The BG
1764 IF g_lea_number IS NULL THEN
1765
1766 IF g_debug THEN
1767 debug(l_proc_name, 40);
1768 END IF;
1769
1770 -- Step 1) Getting LEA Number from Org linked to the location
1771 OPEN pqp_gb_tp_pension_extracts.csr_lea_details_by_loc(l_location_id);
1772 FETCH pqp_gb_tp_pension_extracts.csr_lea_details_by_loc INTO l_lea_dets_by_loc;
1773
1774 IF (pqp_gb_tp_pension_extracts.csr_lea_details_by_loc%FOUND
1775 AND
1776 l_lea_dets_by_loc.lea_number IS NOT NULL
1777 ) THEN
1778
1779 g_lea_number := l_lea_dets_by_loc.lea_number;
1780
1781 ELSE
1782 -- LEA Number is not present on org linked to location
1783 IF g_debug THEN
1784 debug(l_proc_name, 45);
1785 END IF;
1786
1787 -- Step 2) Look for LEA Number at BG level
1788 OPEN pqp_gb_tp_pension_extracts.csr_lea_details
1789 (p_organization_id => p_business_group_id
1790 ,p_lea_number => NULL
1791 );
1792 FETCH pqp_gb_tp_pension_extracts.csr_lea_details INTO l_lea_dets_frm_bg;
1793
1794 IF (pqp_gb_tp_pension_extracts.csr_lea_details%FOUND
1795 AND
1796 l_lea_dets_frm_bg.lea_number IS NOT NULL
1797 ) THEN
1798
1799 g_lea_number := l_lea_dets_frm_bg.lea_number;
1800
1801 ELSE -- NOT FOUND or LEA Number is NULL
1802
1803 -- Close both cursors
1804 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details_by_loc;
1805 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1806
1807 -- Error out as the current BG is not set up as an LEA
1808 fail_current_extract_run(p_ext_dfn_id => p_ext_dfn_id);
1809
1810 fnd_message.set_name('PQP', 'PQP_230037_CURR_BG_NOT_LEA_ERR');
1811 l_err_msg := fnd_message.get;
1812 errbuf := l_err_msg;
1813 retcode := PROGRAM_FAILURE;
1814 fnd_file.put_line(fnd_file.log, l_err_msg);
1815
1816 fnd_message.raise_error;
1817
1818 RETURN;
1819
1820 END IF; -- Chk Lea number from BG Level
1821
1822 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1823
1824 END IF; -- Step 1) Getting LEA Number from Org linked to the location
1825
1826 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details_by_loc;
1827
1828 END IF; -- g_lea_number IS NULL
1829 --
1830 END IF; -- Check report type
1831
1832 IF g_debug THEN
1833 debug('g_report_type :'||g_report_type, 50);
1834 debug('g_lea_number :'||g_lea_number, 60);
1835 debug('l_location_code :'||nvl(l_location_code,'NULL'), 70);
1836 END IF;
1837
1838 -- Step 2.3) Get the UDT name using p_ext_dfn_id
1839 OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes(p_ext_dfn_id);
1840 FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes
1841 INTO g_extract_type, g_extract_udt_name, l_ext_udt_id;
1842 CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
1843
1844 -- Step 2.4) Update the UDT in the current Master BG
1845 set_location_code
1846 (p_udt_id => l_ext_udt_id
1847 ,p_value => l_location_code
1848 ,p_business_group_id => p_business_group_id
1849 );
1850
1851 -- Step 3) Set the master Bg in the multiproc data table
1852 -- and also update multiproc data with status of
1853 -- U for unprocessed
1854 -- This step has now been moved into the procedure set_cross_person_records
1855 -- Bugfix 3671727:ENH2 :Passing new param p_ext_dfn_id
1856 set_cross_person_records
1857 (p_business_group_id => p_business_group_id
1858 ,p_effective_date => l_effective_date
1859 ,p_ext_dfn_id => p_ext_dfn_id
1860 );
1861
1862 -- Step 4) Find the details of this BG / organization
1863 -- Also find any other LEA BGs enabled for cross BG
1864 -- reporting and store them all for processing
1865
1866 -- Step 4.1) Get the LEA details of the chosen LEA in the current BG
1867 g_lea_business_groups.DELETE;
1868
1869 OPEN pqp_gb_tp_pension_extracts.csr_lea_details
1870 (p_organization_id => p_business_group_id
1871 -- Bugfix 3671727:ENH1 Now fetching for chosen LEA
1872 ,p_lea_number => g_lea_number
1873 );
1874 FETCH pqp_gb_tp_pension_extracts.csr_lea_details INTO l_lea_details;
1875
1876 IF pqp_gb_tp_pension_extracts.csr_lea_details%NOTFOUND THEN
1877
1878 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1879
1880 -- Error out as the current BG does not hv this LEA
1881 -- Bugfix 3671727:ENH1:ENH2 :Moved code from here into new proc
1882 -- as we need to call it from more than one places
1883 fail_current_extract_run(p_ext_dfn_id => p_ext_dfn_id);
1884
1885 fnd_message.set_name('PQP', 'PQP_230037_CURR_BG_NOT_LEA_ERR');
1886 l_err_msg := fnd_message.get;
1887 errbuf := l_err_msg;
1888 retcode := PROGRAM_FAILURE;
1889 fnd_file.put_line(fnd_file.log, l_err_msg);
1890
1891 fnd_message.raise_error;
1892
1893 RETURN;
1894
1895 END IF;
1896
1897 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1898
1899 -- Step 4.2) Store the current master BG in the list of BGs to process
1900 g_lea_business_groups(p_business_group_id).business_group_id := p_business_group_id;
1901 -- Bugfix 3671727:ENH1 Commented out as these cols are not available in the collection
1902 -- g_lea_business_groups(p_business_group_id).lea_number := l_lea_details.lea_number;
1903 -- g_lea_business_groups(p_business_group_id).lea_name := l_lea_details.lea_name;
1904 g_lea_business_groups(p_business_group_id).CrossBG_Enabled := l_lea_details.crossbg_enabled;
1905
1906 IF g_debug THEN
1907 debug('Count in BGs collection :'||to_char(g_lea_business_groups.COUNT), 80);
1908 debug('LEA Number :'||l_lea_details.lea_number, 90);
1909 END IF;
1910
1911 -- Step 4.3) If its the LEA report, then we need to find,
1912 -- store and process other LEA BGs enabled for Cross BG reporting.
1913 -- For Non-LEA report, we just run for current BG and location code
1914 IF g_report_type = 'LEA'
1915 AND
1916 l_lea_details.crossbg_enabled = 'Y' THEN
1917
1918 -- Loop thru all the LEA BGs enabled for Cross BG reporting
1919 FOR l_BG_dets IN pqp_gb_t1_pension_extracts.csr_all_business_groups
1920 (l_lea_details.lea_number
1921 ,p_business_group_id
1922 )
1923 LOOP
1924
1925 -- Update the location code in the UDT for this BG
1926 set_location_code
1927 (p_udt_id => l_ext_udt_id
1928 ,p_value => l_location_code
1929 ,p_business_group_id => l_BG_dets.business_group_id
1930 );
1931
1932 -- Store all LEA BGs enabled for bross BG reporting
1933 g_lea_business_groups(l_BG_dets.business_group_id) := l_BG_dets;
1934
1935 IF g_debug THEN
1936 debug('Added to collection BGId :'||to_char(l_BG_dets.business_group_id), 100);
1937 END IF;
1938
1939 END LOOP;
1940
1941 IF g_debug THEN
1942 debug('Count in BGs collection :'||to_char(g_lea_business_groups.COUNT), 110);
1943 END IF;
1944
1945 END IF;
1946
1947 -- Commit here as we hv set location code in one or more UDTs
1948 COMMIT;
1949
1950 -- Step 5) For each stored LEA Bg, submit an extract process
1951 l_curr_bg_id := g_lea_business_groups.FIRST;
1952
1953 WHILE l_curr_bg_id IS NOT NULL
1954 LOOP
1955
1956 IF g_debug THEN
1957 debug('Submitting Request', 120);
1958 END IF;
1959
1960 -- Submit the extract process request
1961 l_request_id := fnd_request.submit_request
1962 (application => 'BEN'
1963 ,program => 'BENXTRCT'
1964 ,description => to_char(l_curr_bg_id)
1965 ,sub_request => FALSE -- TRUE, still not decide on this one
1966 ,argument1 => NULL -- benefit_action_id
1967 ,argument2 => fnd_number.number_to_canonical(p_ext_dfn_id)
1968 ,argument3 => p_effective_date -- is already canonical
1969 ,argument4 => fnd_number.number_to_canonical(l_curr_bg_id)
1970 );
1971
1972 IF l_request_id = 0 THEN
1973
1974 OPEN csr_bg_name(l_curr_bg_id);
1975 FETCH csr_bg_name INTO l_bg_name;
1976 CLOSE csr_bg_name;
1977
1978 fnd_message.set_name('PQP', 'PQP_230038_EXT_PROC_SUBMIT_ERR');
1979 fnd_message.set_token('BGNAME', l_bg_name);
1980 l_err_msg := fnd_message.get;
1981 errbuf := l_err_msg;
1982 fnd_file.put_line(fnd_file.log, l_err_msg);
1983 l_retcode := PROGRAM_FAILURE ;
1984 l_err_msg := NULL;
1985 EXIT;
1986 END IF;
1987
1988 COMMIT;
1989
1990 IF g_debug THEN
1991 debug('BGId :'||to_char(l_curr_bg_id)||' Request ID :'||to_char(l_request_id), 130);
1992 END IF;
1993
1994 -- If the execution mode is serial then
1995 -- we must wait for this request to complete
1996 -- before submitting the next one.
1997 IF g_execution_mode = 'SERIAL' THEN
1998
1999 l_wait_success := fnd_concurrent.wait_for_request
2000 (request_id => l_request_id
2001 ,interval => g_wait_interval
2002 ,max_wait => g_max_wait
2003 ,phase => l_phase -- OUT
2004 ,status => l_status -- OUT
2005 ,dev_phase => l_dev_phase -- OUT
2006 ,dev_status => l_dev_status -- OUT
2007 ,message => l_message -- OUT
2008 );
2009
2010 -- Do some error checking here
2011 IF (NOT l_wait_success
2012 )
2013 OR
2014 (l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL'
2015 ) THEN
2016
2017 fnd_file.put_line(fnd_file.log, l_message);
2018
2019 l_bg_name := NULL;
2020 OPEN csr_bg_name(l_curr_bg_id);
2021 FETCH csr_bg_name INTO l_bg_name;
2022 CLOSE csr_bg_name;
2023
2024 fnd_message.set_name('PQP', 'PQP_230039_EXT_PROC_EXEC_ERR');
2025 fnd_message.set_token('BGNAME', l_bg_name);
2026 l_err_msg := fnd_message.get;
2027 errbuf := l_err_msg;
2028 fnd_file.put_line(fnd_file.log, l_err_msg);
2029 l_retcode := PROGRAM_FAILURE ;
2030 l_err_msg := NULL;
2031 EXIT;
2032
2033 ELSE -- Completed successfully
2034
2035 IF g_debug THEN
2036 debug('SERIAL: Completed Successfully Request ID :'||to_char(l_request_id), 140);
2037 END IF;
2038
2039 -- Store the request id in BG collection
2040 g_lea_business_groups(l_curr_bg_id).request_id := l_request_id;
2041 g_request_ids(l_curr_bg_id) := l_request_id;
2042
2043 END IF;
2044
2045 ELSE -- PARALLEL, store request id for chking later
2046 g_lea_business_groups(l_curr_bg_id).request_id := l_request_id;
2047 g_request_ids(l_curr_bg_id) := l_request_id;
2048 END IF; -- g_execution_mode = 'SERIAL' THEN
2049
2050 -- Get the next BG
2051 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2052
2053 END LOOP; -- l_curr_bg_id IS NOT NULL
2054
2055 -- Step 5.2) Check the return code for any failure
2056 IF l_retcode = PROGRAM_FAILURE THEN
2057
2058 IF g_debug THEN
2059 debug('SERIAL:Program Failure, erroring.', 150);
2060 END IF;
2061
2062 -- First reset the status on multiproc data for master bg row
2063 UPDATE pqp_ext_cross_person_records
2064 SET processing_status = 'E' -- Error
2065 ,last_updated_by = fnd_global.user_id
2066 ,last_update_date = SYSDATE
2067 ,last_update_login = fnd_global.login_id
2068 ,object_version_number = (object_version_number + 1)
2069 WHERE record_type = 'M'
2070 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2071 AND ext_dfn_id = p_ext_dfn_id
2072 AND lea_number = g_lea_number;
2073
2074 COMMIT;
2075
2076 retcode := l_retcode;
2077 fnd_message.raise_error;
2078 RETURN;
2079
2080 END IF;
2081
2082 -- Step 6) Wait till all extract processes finish
2083 -- If the execution mode is parallel then we
2084 -- must wait for all the requests to complete
2085 -- before proceeding.
2086 IF g_execution_mode = 'PARALLEL' THEN
2087
2088 l_curr_bg_id := g_lea_business_groups.FIRST;
2089
2090 WHILE l_curr_bg_id IS NOT NULL
2091 LOOP
2092
2093 l_wait_success := fnd_concurrent.wait_for_request
2094 (request_id => g_lea_business_groups(l_curr_bg_id).request_id
2095 ,interval => g_wait_interval
2096 ,max_wait => g_max_wait
2097 ,phase => l_phase -- OUT
2098 ,status => l_status -- OUT
2099 ,dev_phase => l_dev_phase -- OUT
2100 ,dev_status => l_dev_status -- OUT
2101 ,message => l_message -- OUT
2102 );
2103
2104 -- Do some error checking here
2105 IF (NOT l_wait_success
2106 )
2107 OR
2108 (l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL'
2109 ) THEN
2110
2111 fnd_file.put_line(fnd_file.log, l_message);
2112
2113 l_bg_name := NULL;
2114 OPEN csr_bg_name(l_curr_bg_id);
2115 FETCH csr_bg_name INTO l_bg_name;
2116 CLOSE csr_bg_name;
2117
2118 fnd_message.set_name('PQP', 'PQP_230039_EXT_PROC_EXEC_ERR');
2119 fnd_message.set_token('BGNAME', l_bg_name);
2120 l_err_msg := fnd_message.get;
2121 errbuf := l_err_msg;
2122 fnd_file.put_line(fnd_file.log, l_err_msg);
2123 l_retcode := PROGRAM_FAILURE ;
2124 l_err_msg:= NULL;
2125 EXIT;
2126
2127 END IF; -- (l_dev_phase = 'COMPLETE'
2128
2129 IF g_debug THEN
2130 debug('PARALLEL:Completed Request ID :'||
2131 to_char(g_lea_business_groups(l_curr_bg_id).request_id), 160);
2132 END IF;
2133
2134 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2135
2136 END LOOP; -- l_curr_bg_id IS NOT NULL
2137
2138 -- Step 6.2) Check the return code for any failure
2139 IF l_retcode = PROGRAM_FAILURE THEN
2140
2141 IF g_debug THEN
2142 debug('PARALLEL:Program Failure, erroring.', 170);
2143 END IF;
2144
2145 -- First reset the status on multiproc data for master bg row
2146 UPDATE pqp_ext_cross_person_records
2147 SET processing_status = 'E' -- Error
2148 ,last_updated_by = fnd_global.user_id
2149 ,last_update_date = SYSDATE
2150 ,last_update_login = fnd_global.login_id
2151 ,object_version_number = (object_version_number + 1)
2152 WHERE record_type = 'M'
2153 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2154 AND ext_dfn_id = p_ext_dfn_id
2155 AND lea_number = g_lea_number;
2156
2157 COMMIT;
2158
2159 retcode := l_retcode;
2160 fnd_message.raise_error;
2161 RETURN;
2162 END IF;
2163
2164 END IF; -- g_execution_mode = 'PARALLEL' THEN
2165
2166 -- Step 7) Call the extract results merge/copy process
2167 -- only if there are more than one request ids
2168 -- in the collection
2169
2170 IF g_request_ids.COUNT > 1 THEN
2171
2172 copy_extract_results
2173 (p_tab_request_ids => g_request_ids
2174 ,p_ext_dfn_id => p_ext_dfn_id
2175 ,p_master_business_group => p_business_group_id
2176 );
2177
2178 END IF; -- End if of collection count > 1 check ...
2179
2180 -- Step 8) Reset the processing status in master Bg
2181 -- and multiproc rows
2182 UPDATE pqp_ext_cross_person_records
2183 SET processing_status = 'C' -- Completed
2184 ,last_updated_by = fnd_global.user_id
2185 ,last_update_date = SYSDATE
2186 ,last_update_login = fnd_global.login_id
2187 ,object_version_number = (object_version_number + 1)
2188 WHERE record_type = 'M'
2189 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2190 AND ext_dfn_id = p_ext_dfn_id
2191 AND lea_number = g_lea_number;
2192
2193 UPDATE pqp_ext_cross_person_records
2194 SET processing_status = 'U' -- Back to Unprocessed
2195 ,last_updated_by = fnd_global.user_id
2196 ,last_update_date = SYSDATE
2197 ,last_update_login = fnd_global.login_id
2198 ,object_version_number = (object_version_number + 1)
2199 WHERE record_type = 'X'
2200 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2201 AND ext_dfn_id = p_ext_dfn_id
2202 AND lea_number = g_lea_number;
2203
2204 COMMIT;
2205
2206 -- Write a summary in the log file
2207 fnd_file.put_line(fnd_file.log, 'Teachers Pension Extract Process completed successfully.');
2208 fnd_file.put_line(fnd_file.log, ' ');
2209 fnd_file.put_line(fnd_file.log, 'Total business groups processed :'
2210 ||to_char(g_lea_business_groups.COUNT));
2211 fnd_file.put_line(fnd_file.log, ' ');
2212 fnd_file.put_line(fnd_file.log, 'Business Group Id Request Id ');
2213 fnd_file.put_line(fnd_file.log, '----------------- ---------------');
2214
2215 l_curr_bg_id := g_lea_business_groups.FIRST;
2216
2217 WHILE l_curr_bg_id IS NOT NULL
2218 LOOP
2219
2220 fnd_file.put(fnd_file.log, rpad(to_char(l_curr_bg_id), 20));
2221 fnd_file.put_line
2222 (fnd_file.log
2223 ,rpad(to_char(g_lea_business_groups(l_curr_bg_id).request_id), 15)
2224 );
2225
2226 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2227
2228 END LOOP;
2229
2230 IF g_debug THEN
2231 debug('Completed master process.', 180);
2232 debug('Leaving :'||l_proc_name, 190);
2233 END IF;
2234
2235 --hr_utility.trace_off;
2236
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239 IF g_debug THEN
2240 debug('Other in :'||l_proc_name, 200);
2241 END IF;
2242 -- First reset the status on multiproc data for master bg row
2243 UPDATE pqp_ext_cross_person_records
2244 SET processing_status = 'E' -- Error
2245 ,last_updated_by = fnd_global.user_id
2246 ,last_update_date = SYSDATE
2247 ,last_update_login = fnd_global.login_id
2248 ,object_version_number = (object_version_number + 1)
2249 WHERE record_type = 'M'
2250 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2251 AND ext_dfn_id = p_ext_dfn_id
2252 AND lea_number = g_lea_number;
2253
2254
2255 COMMIT;
2256 RAISE;
2257 END tpa_extract_process;
2258
2259 --
2260 --
2261 --
2262
2263 END pqp_gb_tp_ext_process;