[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.0 2005/05/29 02:20:51 appldev noship $ */
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'
1270 AND business_group_id IS NULL;
1271
1272 CURSOR csr_user_row IS
1273 SELECT user_row_id
1274 FROM pay_user_rows_f
1275 WHERE user_table_id = p_udt_id
1276 AND row_low_range_or_name = 'Criteria'
1277 AND c_effective_date BETWEEN effective_start_date
1278 AND effective_end_date
1279 AND legislation_code = 'GB'
1280 AND business_group_id IS NULL;
1281
1282
1283 CURSOR csr_udt_location_code IS
1284 SELECT uci.rowid, uci.*
1285 FROM pay_user_columns puc
1286 ,pay_user_rows_f pur
1287 ,pay_user_column_instances_f uci
1288 WHERE -- User Column
1289 puc.user_table_id = p_udt_id
1290 AND puc.user_column_name = 'Location Code'
1291 AND puc.legislation_code = 'GB'
1292 AND puc.business_group_id IS NULL
1293 -- User Row
1294 AND pur.row_low_range_or_name = 'Criteria'
1295 AND c_effective_date BETWEEN pur.effective_start_date
1296 AND pur.effective_end_date
1297 AND pur.legislation_code = 'GB'
1298 AND pur.business_group_id IS NULL
1299 -- Join column and Col Instance
1300 AND uci.user_column_id = puc.user_column_id
1301 -- join row and Col Instance
1302 AND uci.user_row_id = pur.user_row_id
1303 -- Filter instance on date and BG
1304 AND uci.business_group_id = p_business_group_id
1305 AND ((c_effective_date BETWEEN uci.effective_start_date
1306 AND uci.effective_end_date
1307 )
1308 OR
1309 (uci.effective_start_date > c_effective_date
1310 )
1311 )
1312 ORDER BY uci.effective_start_date ASC;
1313
1314 l_udt_row csr_udt_location_code%ROWTYPE;
1315
1316 l_proc_name VARCHAR2 (80) := g_proc_name
1317 || 'set_location_code';
1318
1319
1320 BEGIN
1321
1322 --hr_utility.trace_on(NULL, 'REQID');
1323 --g_debug := hr_utility.debug_enabled;
1324
1325 IF g_debug THEN
1326 debug('Entering :'||l_proc_name, 10);
1327 debug('p_udt_id :'||to_char(p_udt_id), 20);
1328 debug('p_business_group_id :'||to_char(p_business_group_id), 30);
1329 debug('p_value :'||p_value, 40);
1330 END IF;
1331
1332 FOR l_udt_row IN csr_udt_location_code
1333 LOOP
1334
1335 PAY_USER_COLUMN_INSTANCES_PKG.delete_row(l_udt_row.rowid);
1336
1337 IF g_debug THEN
1338 debug('Deleted row in loop', 50);
1339 END IF;
1340 /* Use the new API when made available, currently only in NOV03 FP
1341 pay_user_column_instance_api.delete_user_column_instance
1342 (p_validate => FALSE
1343 ,p_effective_date => l_udt_row.effective_start_date
1344 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1345 ,p_datetrack_update_mode => hr_api.g_zap
1346 ,p_object_version_number => l_udt_row.object_version_number
1347 ,p_effective_start_date => l_udt_row.effective_start_date
1348 ,p_effective_end_date => l_udt_row.effective_start_date
1349 );
1350 */
1351
1352 END LOOP;
1353
1354 l_udt_row := NULL;
1355
1356 OPEN csr_user_col;
1357 FETCH csr_user_col INTO l_udt_row.user_column_id;
1358 CLOSE csr_user_col;
1359
1360 OPEN csr_user_row;
1361 FETCH csr_user_row INTO l_udt_row.user_row_id;
1362 CLOSE csr_user_row;
1363
1364 IF g_debug THEN
1365 debug('User Row Id :'||to_char(l_udt_row.user_row_id), 60);
1366 debug('User Col Id :'||to_char(l_udt_row.user_column_id), 70);
1367 END IF;
1368
1369 -- Now insert a new row with the correct location code
1370 PAY_USER_COLUMN_INSTANCES_PKG.insert_row
1371 (p_rowid => l_udt_row.rowid
1372 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1373 ,p_effective_start_date => c_effective_date
1374 ,p_effective_end_date => hr_api.g_eot
1375 ,p_user_row_id => l_udt_row.user_row_id
1376 ,p_user_column_id => l_udt_row.user_column_id
1377 ,p_business_group_id => p_business_group_id
1378 ,p_legislation_code => NULL
1379 ,p_legislation_subgroup => NULL
1380 ,p_value => p_value
1381 );
1382
1383 /* Use the new API when made available, currently only in NOV03 FP
1384 pay_user_column_instance_api.create_user_column_instance
1385 (p_validate => FALSE
1386 ,p_effective_date => c_effective_date
1387 ,p_user_row_id => l_udt_row.user_row_id
1388 ,p_user_column_id => l_udt_row.user_column_id
1389 ,p_value => p_value
1390 ,p_business_group_id => p_business_group_id
1391 ,p_legislation_code => NULL
1392 ,p_user_column_instance_id => l_udt_row.user_column_instance_id
1393 ,p_object_version_number => l_udt_row.object_version_number
1394 ,p_effective_start_date => l_udt_row.effective_start_date
1395 ,p_effective_end_date => l_udt_row.effective_end_date
1396 );
1397 */
1398
1399 IF g_debug THEN
1400 debug('Leaving :'||l_proc_name, 90);
1401 END IF;
1402 --hr_utility.trace_off;
1403
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 --hr_utility.trace_off;
1407 RAISE;
1408 END set_location_code;
1409
1410 --
1411 -- set_cross_person_records
1412 --
1413 PROCEDURE set_cross_person_records
1414 (p_business_group_id IN NUMBER
1415 ,p_effective_date IN DATE
1416 ,p_master_request_id IN NUMBER
1417 -- Bugfix 3671727:ENH2 :Added new param
1418 ,p_ext_dfn_id IN VARCHAR2
1419 ) IS
1420
1421 PRAGMA AUTONOMOUS_TRANSACTION;
1422
1423 l_wait_success BOOLEAN := FALSE;
1424
1425 l_phase VARCHAR2(80);
1426 l_status VARCHAR2(80);
1427 l_dev_phase VARCHAR2(80);
1428 l_dev_status VARCHAR2(80);
1429 l_message VARCHAR2(80);
1430
1431 l_multiproc_data pqp_gb_t1_pension_extracts.csr_multiproc_data%ROWTYPE;
1432
1433 l_proc_name VARCHAR2 (80) := g_proc_name
1434 || 'set_cross_person_records';
1435
1436
1437 BEGIN -- set_cross_person_records
1438
1439 IF g_debug THEN
1440 debug('Entering :'||l_proc_name, 10);
1441 END IF;
1442
1443 -- Get the master row data
1444 OPEN pqp_gb_t1_pension_extracts.csr_multiproc_data
1445 (p_record_type => 'M'
1446 ,p_national_identifier => NULL
1447 -- Bugfix 3671727:ENH1: Now passing lea number
1448 ,p_lea_number => g_lea_number
1449 ,p_ext_dfn_id => p_ext_dfn_id
1450 );
1451 FETCH pqp_gb_t1_pension_extracts.csr_multiproc_data INTO l_multiproc_data;
1452
1453 IF pqp_gb_t1_pension_extracts.csr_multiproc_data%FOUND THEN
1454
1455 IF g_debug THEN
1456 debug('Found row in csr_multiproc_data', 20);
1457 END IF;
1458
1459 -- Found, now chk for status
1460 IF NVL(l_multiproc_data.processing_status, 'U') <> 'P' THEN
1461
1462 l_wait_success := TRUE;
1463
1464 ELSE -- Another request might be running currently
1465
1466 IF g_debug THEN
1467 debug(l_proc_name, 30);
1468 END IF;
1469
1470 -- Verify by chking the status of the request id
1471 -- stored in the master bg row
1472 l_wait_success := fnd_concurrent.get_request_status
1473 (request_id => l_multiproc_data.request_id
1474 ,phase => l_phase -- OUT
1475 ,status => l_status -- OUT
1476 ,dev_phase => l_dev_phase -- OUT
1477 ,dev_status => l_dev_status -- OUT
1478 ,message => l_message -- OUT
1479 );
1480
1481 IF l_wait_success
1482 AND
1483 l_dev_phase = 'COMPLETE' THEN
1484
1485 l_wait_success := TRUE;
1486 ELSE
1487 l_wait_success := FALSE;
1488 END IF;
1489
1490 END IF; -- NVL(l_multiproc_data.processing_status, 'U') <> 'P' THEN
1491
1492 IF l_wait_success THEN
1493 UPDATE pqp_ext_cross_person_records
1494 SET business_group_id = p_business_group_id
1495 ,effective_start_date = p_effective_date
1496 ,request_id = nvl(p_master_request_id, g_master_request_id)
1497 ,processing_status = 'P' -- Processing
1498 ,last_updated_by = fnd_global.user_id
1499 ,last_update_date = SYSDATE
1500 ,last_update_login = fnd_global.login_id
1501 ,object_version_number = (object_version_number + 1)
1502 WHERE record_type = 'M'
1503 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
1504 AND ext_dfn_id = p_ext_dfn_id
1505 AND lea_number = g_lea_number;
1506
1507 IF g_debug THEN
1508 debug('No of rows updated :'||to_char(SQL%ROWCOUNT), 40);
1509 debug('Updated master BG row with :'||to_char(p_business_group_id), 50);
1510 END IF;
1511
1512 ELSE
1513 -- Raise error with message that there is already
1514 -- another TPA Master Extract Process running,
1515 -- can not submit a second one
1516 CLOSE pqp_gb_t1_pension_extracts.csr_multiproc_data;
1517
1518 -- Exiting because another process is running and you can not submit twice
1519 fnd_message.set_name('PQP', 'PQP_230036_MULTIPLE_TP_EXT_ERR');
1520 fnd_file.put_line(fnd_file.log, fnd_message.get);
1521 fnd_message.raise_error;
1522 RETURN;
1523
1524 END IF;
1525
1526 l_wait_success := NULL;
1527
1528 ELSE -- NOTFOUND
1529
1530 -- Not found, insert a new master bg row
1531 -- Bugfix 3671727:ENH1:ENH2 : Added ext_dfn_id and lea_number
1532 INSERT INTO pqp_ext_cross_person_records
1533 (record_type
1534 ,ext_dfn_id
1535 ,lea_number
1536 ,business_group_id
1537 ,effective_start_date
1538 ,request_id
1539 ,processing_status
1540 ,created_by
1541 ,creation_date
1542 ,object_version_number
1543 )
1544 VALUES
1545 ('M' -- Master BG row
1546 ,p_ext_dfn_id
1547 ,g_lea_number
1548 ,p_business_group_id
1549 ,p_effective_date
1550 ,nvl(p_master_request_id, g_master_request_id)
1551 ,'P' -- Processing
1552 ,fnd_global.user_id
1553 ,SYSDATE
1554 ,1
1555 );
1556
1557 IF g_debug THEN
1558 debug('Inserted master BG row with :'||to_char(p_business_group_id), 60);
1559 END IF;
1560
1561 END IF; -- pqp_gb_t1_pension_extracts.csr_multiproc_data%FOUND THEN
1562
1563 -- Close the cursor if its still open
1564 IF pqp_gb_t1_pension_extracts.csr_multiproc_data%ISOPEN THEN
1565 CLOSE pqp_gb_t1_pension_extracts.csr_multiproc_data;
1566 END IF;
1567
1568 -- Step 3.2) Updating multiproc data
1569 UPDATE pqp_ext_cross_person_records
1570 SET processing_status = 'U'
1571 ,last_updated_by = fnd_global.user_id
1572 ,last_update_date = SYSDATE
1573 ,last_update_login = fnd_global.login_id
1574 ,object_version_number = (object_version_number + 1)
1575 WHERE record_type = 'X'
1576 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
1577 AND ext_dfn_id = p_ext_dfn_id
1578 AND lea_number = g_lea_number;
1579
1580 -- Commiting here before any further processing
1581 -- coz we have updated data in the multiproc table
1582 -- and this will be used by all the child processes
1583 COMMIT;
1584
1585 IF g_debug THEN
1586 debug('Leaving :'||l_proc_name, 90);
1587 END IF;
1588 RETURN;
1589
1590 END set_cross_person_records;
1591
1592 --
1593 -- fail_current_extract_run
1594 --
1595 PROCEDURE fail_current_extract_run
1596 (p_ext_dfn_id IN NUMBER
1597 ) IS
1598
1599 l_proc_name VARCHAR2 (80) := g_proc_name
1600 || 'fail_current_extract_run';
1601
1602 BEGIN
1603
1604 IF g_debug THEN
1605 debug('Entering :'||l_proc_name, 10);
1606 END IF;
1607
1608 -- Error out as the current BG is not an LEA
1609 -- Also, we need to reset the master BG row in multiproc data
1610 -- table to 'E'
1611 UPDATE pqp_ext_cross_person_records
1612 SET processing_status = 'E' -- Error
1613 ,last_updated_by = fnd_global.user_id
1614 ,last_update_date = SYSDATE
1615 ,last_update_login = fnd_global.login_id
1616 ,object_version_number = (object_version_number + 1)
1617 WHERE record_type = 'M'
1618 -- Bugfix 3671727:ENH1 : Added these AND clauses
1619 AND ext_dfn_id = p_ext_dfn_id
1620 AND lea_number = g_lea_number;
1621
1622 COMMIT;
1623
1624 IF g_debug THEN
1625 debug('Leaving :'||l_proc_name, 90);
1626 END IF;
1627
1628 RETURN;
1629
1630 END fail_current_extract_run;
1631 --
1632 -- tpa_extract_process
1633 --
1634 PROCEDURE tpa_extract_process
1635 (errbuf OUT NOCOPY VARCHAR2
1636 ,retcode OUT NOCOPY NUMBER
1637 ,p_ext_dfn_id IN NUMBER
1638 ,p_effective_date IN VARCHAR2
1639 ,p_business_group_id IN NUMBER
1640 ,p_lea_yn IN VARCHAR2
1641 ,p_argument1 IN VARCHAR2
1642 ,p_organization_id IN NUMBER
1643 -- Bugfix 3671727:ENH1 : Added new param
1644 ,p_argument2 IN VARCHAR2
1645 ,p_lea_number IN VARCHAR2
1646 ) IS
1647
1648 CURSOR csr_location_code IS
1649 SELECT loc.location_code
1650 ,loc.location_id
1651 ,lei.lei_information6 lea_number
1652 FROM hr_organization_units_v org
1653 ,hr_locations_all loc
1654 ,hr_location_extra_info lei
1655 WHERE org.organization_id = p_organization_id
1656 AND loc.location_id = org.location_id
1657 AND lei.location_id(+) = loc.location_id
1658 AND nvl(lei.information_type,'PQP_GB_EDU_ESTB_INFO') = 'PQP_GB_EDU_ESTB_INFO';
1659
1660 CURSOR csr_bg_name(p_business_group_id IN NUMBER) IS
1661 SELECT name
1662 FROM per_business_groups_perf
1663 WHERE business_group_id = p_business_group_id;
1664
1665 PROGRAM_FAILURE CONSTANT NUMBER := 2 ;
1666 PROGRAM_SUCCESS CONSTANT NUMBER := 0 ;
1667
1668 l_location_code hr_locations_all.location_code%TYPE := NULL;
1669 l_location_id hr_locations_all.location_id%TYPE := NULL;
1670 l_curr_bg_id per_all_people_f.business_group_id%TYPE;
1671 l_request_id fnd_concurrent_requests.request_id%TYPE;
1672 l_retcode NUMBER := PROGRAM_SUCCESS;
1673 l_wait_success BOOLEAN := FALSE;
1674 l_effective_date DATE;
1675
1676 l_phase VARCHAR2(80);
1677 l_status VARCHAR2(80);
1678 l_dev_phase VARCHAR2(80);
1679 l_dev_status VARCHAR2(80);
1680 l_message VARCHAR2(80);
1681 l_err_msg fnd_new_messages.message_text%TYPE;
1682
1683 l_lea_details pqp_gb_tp_pension_extracts.csr_lea_details%ROWTYPE;
1684 l_lea_dets_frm_bg pqp_gb_tp_pension_extracts.csr_lea_details%ROWTYPE;
1685 l_multiproc_data pqp_gb_t1_pension_extracts.csr_multiproc_data%ROWTYPE;
1686 l_lea_dets_by_loc pqp_gb_tp_pension_extracts.csr_lea_details_by_loc%ROWTYPE;
1687
1688 l_ext_udt_id pay_user_tables.user_table_id%TYPE;
1689 l_bg_name per_business_groups_perf.name%TYPE := NULL;
1690
1691 l_proc_name VARCHAR2 (80) := g_proc_name
1692 || 'tpa_extract_process';
1693
1694 BEGIN -- tpa_extract_process
1695
1696 --hr_utility.trace_on(NULL, 'REQID');
1697 --g_debug := hr_utility.debug_enabled;
1698
1699 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1700
1701 -- Step 1) Get the master request id
1702 g_master_request_id := fnd_global.conc_request_id;
1703
1704 IF g_debug THEN
1705 debug('Entering :'||l_proc_name, 10);
1706 debug('g_master_request_id :'||to_char(g_master_request_id), 20);
1707 END IF;
1708
1709 -- Step 2) Set report type, LEA or Non-LEA
1710 -- If its the LEA report then we set the location
1711 -- code as NULL in UDT, otherwise, we find the
1712 -- location code, find the UDT name and set the
1713 -- location code in the UDT for the Non-LEA report.
1714
1715 -- Step 2.1) Check report type
1716
1717 -- Bugifix in 115.5
1718 -- Checking if its an LEA report using the p_lea_yn flag instead of
1719 -- comparing the org id and bg id coz for non lea report
1720 -- we might have a situation where the location has been
1721 -- linked to the BG org for using with non lea report
1722 IF p_lea_yn = 'Y' THEN
1723
1724 g_report_type := 'LEA';
1725
1726 -- Clear the location code from the UDT as we're
1727 -- running the LEA report
1728 l_location_code := NULL;
1729 l_location_id := NULL;
1730
1731 -- Bugfix 3671727:ENH1 :Setting the LEA number
1732 g_lea_number := p_lea_number;
1733
1734 ELSE -- Check report type
1735
1736 IF g_debug THEN
1737 debug(l_proc_name, 30);
1738 END IF;
1739
1740 g_report_type := 'NONLEA';
1741
1742 -- Step 2.2) Get the location id for this organization
1743 -- and set the location code in the UDT
1744 -- so the non-Lea report gets executed
1745 -- Bugfix 3671727:ENH1 : Now getting the location id and LEA number
1746 OPEN csr_location_code;
1747 FETCH csr_location_code INTO l_location_code, l_location_id, g_lea_number;
1748 CLOSE csr_location_code;
1749
1750 -- Bugfix 3671727:ENH1 : If the LEA number was NULL on location EIT then
1751 -- get it from the following in that order
1752 -- 1) Org linked to that location
1753 -- 2) The BG
1754 IF g_lea_number IS NULL THEN
1755
1756 IF g_debug THEN
1757 debug(l_proc_name, 40);
1758 END IF;
1759
1760 -- Step 1) Getting LEA Number from Org linked to the location
1761 OPEN pqp_gb_tp_pension_extracts.csr_lea_details_by_loc(l_location_id);
1762 FETCH pqp_gb_tp_pension_extracts.csr_lea_details_by_loc INTO l_lea_dets_by_loc;
1763
1764 IF (pqp_gb_tp_pension_extracts.csr_lea_details_by_loc%FOUND
1765 AND
1766 l_lea_dets_by_loc.lea_number IS NOT NULL
1767 ) THEN
1768
1769 g_lea_number := l_lea_dets_by_loc.lea_number;
1770
1771 ELSE
1772 -- LEA Number is not present on org linked to location
1773 IF g_debug THEN
1774 debug(l_proc_name, 45);
1775 END IF;
1776
1777 -- Step 2) Look for LEA Number at BG level
1778 OPEN pqp_gb_tp_pension_extracts.csr_lea_details
1779 (p_organization_id => p_business_group_id
1780 ,p_lea_number => NULL
1781 );
1782 FETCH pqp_gb_tp_pension_extracts.csr_lea_details INTO l_lea_dets_frm_bg;
1783
1784 IF (pqp_gb_tp_pension_extracts.csr_lea_details%FOUND
1785 AND
1786 l_lea_dets_frm_bg.lea_number IS NOT NULL
1787 ) THEN
1788
1789 g_lea_number := l_lea_dets_frm_bg.lea_number;
1790
1791 ELSE -- NOT FOUND or LEA Number is NULL
1792
1793 -- Close both cursors
1794 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details_by_loc;
1795 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1796
1797 -- Error out as the current BG is not set up as an LEA
1798 fail_current_extract_run(p_ext_dfn_id => p_ext_dfn_id);
1799
1800 fnd_message.set_name('PQP', 'PQP_230037_CURR_BG_NOT_LEA_ERR');
1801 l_err_msg := fnd_message.get;
1802 errbuf := l_err_msg;
1803 retcode := PROGRAM_FAILURE;
1804 fnd_file.put_line(fnd_file.log, l_err_msg);
1805
1806 fnd_message.raise_error;
1807
1808 RETURN;
1809
1810 END IF; -- Chk Lea number from BG Level
1811
1812 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1813
1814 END IF; -- Step 1) Getting LEA Number from Org linked to the location
1815
1816 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details_by_loc;
1817
1818 END IF; -- g_lea_number IS NULL
1819 --
1820 END IF; -- Check report type
1821
1822 IF g_debug THEN
1823 debug('g_report_type :'||g_report_type, 50);
1824 debug('g_lea_number :'||g_lea_number, 60);
1825 debug('l_location_code :'||nvl(l_location_code,'NULL'), 70);
1826 END IF;
1827
1828 -- Step 2.3) Get the UDT name using p_ext_dfn_id
1829 OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes(p_ext_dfn_id);
1830 FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes
1831 INTO g_extract_type, g_extract_udt_name, l_ext_udt_id;
1832 CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
1833
1834 -- Step 2.4) Update the UDT in the current Master BG
1835 set_location_code
1836 (p_udt_id => l_ext_udt_id
1837 ,p_value => l_location_code
1838 ,p_business_group_id => p_business_group_id
1839 );
1840
1841 -- Step 3) Set the master Bg in the multiproc data table
1842 -- and also update multiproc data with status of
1843 -- U for unprocessed
1844 -- This step has now been moved into the procedure set_cross_person_records
1845 -- Bugfix 3671727:ENH2 :Passing new param p_ext_dfn_id
1846 set_cross_person_records
1847 (p_business_group_id => p_business_group_id
1848 ,p_effective_date => l_effective_date
1849 ,p_ext_dfn_id => p_ext_dfn_id
1850 );
1851
1852 -- Step 4) Find the details of this BG / organization
1853 -- Also find any other LEA BGs enabled for cross BG
1854 -- reporting and store them all for processing
1855
1856 -- Step 4.1) Get the LEA details of the chosen LEA in the current BG
1857 g_lea_business_groups.DELETE;
1858
1859 OPEN pqp_gb_tp_pension_extracts.csr_lea_details
1860 (p_organization_id => p_business_group_id
1861 -- Bugfix 3671727:ENH1 Now fetching for chosen LEA
1862 ,p_lea_number => g_lea_number
1863 );
1864 FETCH pqp_gb_tp_pension_extracts.csr_lea_details INTO l_lea_details;
1865
1866 IF pqp_gb_tp_pension_extracts.csr_lea_details%NOTFOUND THEN
1867
1868 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1869
1870 -- Error out as the current BG does not hv this LEA
1871 -- Bugfix 3671727:ENH1:ENH2 :Moved code from here into new proc
1872 -- as we need to call it from more than one places
1873 fail_current_extract_run(p_ext_dfn_id => p_ext_dfn_id);
1874
1875 fnd_message.set_name('PQP', 'PQP_230037_CURR_BG_NOT_LEA_ERR');
1876 l_err_msg := fnd_message.get;
1877 errbuf := l_err_msg;
1878 retcode := PROGRAM_FAILURE;
1879 fnd_file.put_line(fnd_file.log, l_err_msg);
1880
1881 fnd_message.raise_error;
1882
1883 RETURN;
1884
1885 END IF;
1886
1887 CLOSE pqp_gb_tp_pension_extracts.csr_lea_details;
1888
1889 -- Step 4.2) Store the current master BG in the list of BGs to process
1890 g_lea_business_groups(p_business_group_id).business_group_id := p_business_group_id;
1891 -- Bugfix 3671727:ENH1 Commented out as these cols are not available in the collection
1892 -- g_lea_business_groups(p_business_group_id).lea_number := l_lea_details.lea_number;
1893 -- g_lea_business_groups(p_business_group_id).lea_name := l_lea_details.lea_name;
1894 g_lea_business_groups(p_business_group_id).CrossBG_Enabled := l_lea_details.crossbg_enabled;
1895
1896 IF g_debug THEN
1897 debug('Count in BGs collection :'||to_char(g_lea_business_groups.COUNT), 80);
1898 debug('LEA Number :'||l_lea_details.lea_number, 90);
1899 END IF;
1900
1901 -- Step 4.3) If its the LEA report, then we need to find,
1902 -- store and process other LEA BGs enabled for Cross BG reporting.
1903 -- For Non-LEA report, we just run for current BG and location code
1904 IF g_report_type = 'LEA'
1905 AND
1906 l_lea_details.crossbg_enabled = 'Y' THEN
1907
1908 -- Loop thru all the LEA BGs enabled for Cross BG reporting
1909 FOR l_BG_dets IN pqp_gb_t1_pension_extracts.csr_all_business_groups
1910 (l_lea_details.lea_number
1911 ,p_business_group_id
1912 )
1913 LOOP
1914
1915 -- Update the location code in the UDT for this BG
1916 set_location_code
1917 (p_udt_id => l_ext_udt_id
1918 ,p_value => l_location_code
1919 ,p_business_group_id => l_BG_dets.business_group_id
1920 );
1921
1922 -- Store all LEA BGs enabled for bross BG reporting
1923 g_lea_business_groups(l_BG_dets.business_group_id) := l_BG_dets;
1924
1925 IF g_debug THEN
1926 debug('Added to collection BGId :'||to_char(l_BG_dets.business_group_id), 100);
1927 END IF;
1928
1929 END LOOP;
1930
1931 IF g_debug THEN
1932 debug('Count in BGs collection :'||to_char(g_lea_business_groups.COUNT), 110);
1933 END IF;
1934
1935 END IF;
1936
1937 -- Commit here as we hv set location code in one or more UDTs
1938 COMMIT;
1939
1940 -- Step 5) For each stored LEA Bg, submit an extract process
1941 l_curr_bg_id := g_lea_business_groups.FIRST;
1942
1943 WHILE l_curr_bg_id IS NOT NULL
1944 LOOP
1945
1946 IF g_debug THEN
1947 debug('Submitting Request', 120);
1948 END IF;
1949
1950 -- Submit the extract process request
1951 l_request_id := fnd_request.submit_request
1952 (application => 'BEN'
1953 ,program => 'BENXTRCT'
1954 ,description => to_char(l_curr_bg_id)
1955 ,sub_request => FALSE -- TRUE, still not decide on this one
1956 ,argument1 => NULL -- benefit_action_id
1957 ,argument2 => fnd_number.number_to_canonical(p_ext_dfn_id)
1958 ,argument3 => p_effective_date -- is already canonical
1959 ,argument4 => fnd_number.number_to_canonical(l_curr_bg_id)
1960 );
1961
1962 IF l_request_id = 0 THEN
1963
1964 OPEN csr_bg_name(l_curr_bg_id);
1965 FETCH csr_bg_name INTO l_bg_name;
1966 CLOSE csr_bg_name;
1967
1968 fnd_message.set_name('PQP', 'PQP_230038_EXT_PROC_SUBMIT_ERR');
1969 fnd_message.set_token('BGNAME', l_bg_name);
1970 l_err_msg := fnd_message.get;
1971 errbuf := l_err_msg;
1972 fnd_file.put_line(fnd_file.log, l_err_msg);
1973 l_retcode := PROGRAM_FAILURE ;
1974 l_err_msg := NULL;
1975 EXIT;
1976 END IF;
1977
1978 COMMIT;
1979
1980 IF g_debug THEN
1981 debug('BGId :'||to_char(l_curr_bg_id)||' Request ID :'||to_char(l_request_id), 130);
1982 END IF;
1983
1984 -- If the execution mode is serial then
1985 -- we must wait for this request to complete
1986 -- before submitting the next one.
1987 IF g_execution_mode = 'SERIAL' THEN
1988
1989 l_wait_success := fnd_concurrent.wait_for_request
1990 (request_id => l_request_id
1991 ,interval => g_wait_interval
1992 ,max_wait => g_max_wait
1993 ,phase => l_phase -- OUT
1994 ,status => l_status -- OUT
1995 ,dev_phase => l_dev_phase -- OUT
1996 ,dev_status => l_dev_status -- OUT
1997 ,message => l_message -- OUT
1998 );
1999
2000 -- Do some error checking here
2001 IF (NOT l_wait_success
2002 )
2003 OR
2004 (l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL'
2005 ) THEN
2006
2007 fnd_file.put_line(fnd_file.log, l_message);
2008
2009 l_bg_name := NULL;
2010 OPEN csr_bg_name(l_curr_bg_id);
2011 FETCH csr_bg_name INTO l_bg_name;
2012 CLOSE csr_bg_name;
2013
2014 fnd_message.set_name('PQP', 'PQP_230039_EXT_PROC_EXEC_ERR');
2015 fnd_message.set_token('BGNAME', l_bg_name);
2016 l_err_msg := fnd_message.get;
2017 errbuf := l_err_msg;
2018 fnd_file.put_line(fnd_file.log, l_err_msg);
2019 l_retcode := PROGRAM_FAILURE ;
2020 l_err_msg := NULL;
2021 EXIT;
2022
2023 ELSE -- Completed successfully
2024
2025 IF g_debug THEN
2026 debug('SERIAL: Completed Successfully Request ID :'||to_char(l_request_id), 140);
2027 END IF;
2028
2029 -- Store the request id in BG collection
2030 g_lea_business_groups(l_curr_bg_id).request_id := l_request_id;
2031 g_request_ids(l_curr_bg_id) := l_request_id;
2032
2033 END IF;
2034
2035 ELSE -- PARALLEL, store request id for chking later
2036 g_lea_business_groups(l_curr_bg_id).request_id := l_request_id;
2037 g_request_ids(l_curr_bg_id) := l_request_id;
2038 END IF; -- g_execution_mode = 'SERIAL' THEN
2039
2040 -- Get the next BG
2041 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2042
2043 END LOOP; -- l_curr_bg_id IS NOT NULL
2044
2045 -- Step 5.2) Check the return code for any failure
2046 IF l_retcode = PROGRAM_FAILURE THEN
2047
2048 IF g_debug THEN
2049 debug('SERIAL:Program Failure, erroring.', 150);
2050 END IF;
2051
2052 -- First reset the status on multiproc data for master bg row
2053 UPDATE pqp_ext_cross_person_records
2054 SET processing_status = 'E' -- Error
2055 ,last_updated_by = fnd_global.user_id
2056 ,last_update_date = SYSDATE
2057 ,last_update_login = fnd_global.login_id
2058 ,object_version_number = (object_version_number + 1)
2059 WHERE record_type = 'M'
2060 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2061 AND ext_dfn_id = p_ext_dfn_id
2062 AND lea_number = g_lea_number;
2063
2064 COMMIT;
2065
2066 retcode := l_retcode;
2067 fnd_message.raise_error;
2068 RETURN;
2069
2070 END IF;
2071
2072 -- Step 6) Wait till all extract processes finish
2073 -- If the execution mode is parallel then we
2074 -- must wait for all the requests to complete
2075 -- before proceeding.
2076 IF g_execution_mode = 'PARALLEL' THEN
2077
2078 l_curr_bg_id := g_lea_business_groups.FIRST;
2079
2080 WHILE l_curr_bg_id IS NOT NULL
2081 LOOP
2082
2083 l_wait_success := fnd_concurrent.wait_for_request
2084 (request_id => g_lea_business_groups(l_curr_bg_id).request_id
2085 ,interval => g_wait_interval
2086 ,max_wait => g_max_wait
2087 ,phase => l_phase -- OUT
2088 ,status => l_status -- OUT
2089 ,dev_phase => l_dev_phase -- OUT
2090 ,dev_status => l_dev_status -- OUT
2091 ,message => l_message -- OUT
2092 );
2093
2094 -- Do some error checking here
2095 IF (NOT l_wait_success
2096 )
2097 OR
2098 (l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL'
2099 ) THEN
2100
2101 fnd_file.put_line(fnd_file.log, l_message);
2102
2103 l_bg_name := NULL;
2104 OPEN csr_bg_name(l_curr_bg_id);
2105 FETCH csr_bg_name INTO l_bg_name;
2106 CLOSE csr_bg_name;
2107
2108 fnd_message.set_name('PQP', 'PQP_230039_EXT_PROC_EXEC_ERR');
2109 fnd_message.set_token('BGNAME', l_bg_name);
2110 l_err_msg := fnd_message.get;
2111 errbuf := l_err_msg;
2112 fnd_file.put_line(fnd_file.log, l_err_msg);
2113 l_retcode := PROGRAM_FAILURE ;
2114 l_err_msg:= NULL;
2115 EXIT;
2116
2117 END IF; -- (l_dev_phase = 'COMPLETE'
2118
2119 IF g_debug THEN
2120 debug('PARALLEL:Completed Request ID :'||
2121 to_char(g_lea_business_groups(l_curr_bg_id).request_id), 160);
2122 END IF;
2123
2124 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2125
2126 END LOOP; -- l_curr_bg_id IS NOT NULL
2127
2128 -- Step 6.2) Check the return code for any failure
2129 IF l_retcode = PROGRAM_FAILURE THEN
2130
2131 IF g_debug THEN
2132 debug('PARALLEL:Program Failure, erroring.', 170);
2133 END IF;
2134
2135 -- First reset the status on multiproc data for master bg row
2136 UPDATE pqp_ext_cross_person_records
2137 SET processing_status = 'E' -- Error
2138 ,last_updated_by = fnd_global.user_id
2139 ,last_update_date = SYSDATE
2140 ,last_update_login = fnd_global.login_id
2141 ,object_version_number = (object_version_number + 1)
2142 WHERE record_type = 'M'
2143 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2144 AND ext_dfn_id = p_ext_dfn_id
2145 AND lea_number = g_lea_number;
2146
2147 COMMIT;
2148
2149 retcode := l_retcode;
2150 fnd_message.raise_error;
2151 RETURN;
2152 END IF;
2153
2154 END IF; -- g_execution_mode = 'PARALLEL' THEN
2155
2156 -- Step 7) Call the extract results merge/copy process
2157 -- only if there are more than one request ids
2158 -- in the collection
2159
2160 IF g_request_ids.COUNT > 1 THEN
2161
2162 copy_extract_results
2163 (p_tab_request_ids => g_request_ids
2164 ,p_ext_dfn_id => p_ext_dfn_id
2165 ,p_master_business_group => p_business_group_id
2166 );
2167
2168 END IF; -- End if of collection count > 1 check ...
2169
2170 -- Step 8) Reset the processing status in master Bg
2171 -- and multiproc rows
2172 UPDATE pqp_ext_cross_person_records
2173 SET processing_status = 'C' -- Completed
2174 ,last_updated_by = fnd_global.user_id
2175 ,last_update_date = SYSDATE
2176 ,last_update_login = fnd_global.login_id
2177 ,object_version_number = (object_version_number + 1)
2178 WHERE record_type = 'M'
2179 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2180 AND ext_dfn_id = p_ext_dfn_id
2181 AND lea_number = g_lea_number;
2182
2183 UPDATE pqp_ext_cross_person_records
2184 SET processing_status = 'U' -- Back to Unprocessed
2185 ,last_updated_by = fnd_global.user_id
2186 ,last_update_date = SYSDATE
2187 ,last_update_login = fnd_global.login_id
2188 ,object_version_number = (object_version_number + 1)
2189 WHERE record_type = 'X'
2190 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2191 AND ext_dfn_id = p_ext_dfn_id
2192 AND lea_number = g_lea_number;
2193
2194 COMMIT;
2195
2196 -- Write a summary in the log file
2197 fnd_file.put_line(fnd_file.log, 'Teachers Pension Extract Process completed successfully.');
2198 fnd_file.put_line(fnd_file.log, ' ');
2199 fnd_file.put_line(fnd_file.log, 'Total business groups processed :'
2200 ||to_char(g_lea_business_groups.COUNT));
2201 fnd_file.put_line(fnd_file.log, ' ');
2202 fnd_file.put_line(fnd_file.log, 'Business Group Id Request Id ');
2203 fnd_file.put_line(fnd_file.log, '----------------- ---------------');
2204
2205 l_curr_bg_id := g_lea_business_groups.FIRST;
2206
2207 WHILE l_curr_bg_id IS NOT NULL
2208 LOOP
2209
2210 fnd_file.put(fnd_file.log, rpad(to_char(l_curr_bg_id), 20));
2211 fnd_file.put_line
2212 (fnd_file.log
2213 ,rpad(to_char(g_lea_business_groups(l_curr_bg_id).request_id), 15)
2214 );
2215
2216 l_curr_bg_id := g_lea_business_groups.NEXT(l_curr_bg_id);
2217
2218 END LOOP;
2219
2220 IF g_debug THEN
2221 debug('Completed master process.', 180);
2222 debug('Leaving :'||l_proc_name, 190);
2223 END IF;
2224
2225 --hr_utility.trace_off;
2226
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 IF g_debug THEN
2230 debug('Other in :'||l_proc_name, 200);
2231 END IF;
2232 -- First reset the status on multiproc data for master bg row
2233 UPDATE pqp_ext_cross_person_records
2234 SET processing_status = 'E' -- Error
2235 ,last_updated_by = fnd_global.user_id
2236 ,last_update_date = SYSDATE
2237 ,last_update_login = fnd_global.login_id
2238 ,object_version_number = (object_version_number + 1)
2239 WHERE record_type = 'M'
2240 -- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
2241 AND ext_dfn_id = p_ext_dfn_id
2242 AND lea_number = g_lea_number;
2243
2244
2245 COMMIT;
2246 RAISE;
2247 END tpa_extract_process;
2248
2249 --
2250 --
2251 --
2252
2253 END pqp_gb_tp_ext_process;