1 PACKAGE BODY PER_PERCAEER_XMLP_PKG AS
2 /* $Header: PERCAEERB.pls 120.0 2007/12/28 06:53:38 srikrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
7 --hr_standard.event('BEFORE REPORT');
8 P_CONC_REQUEST_ID := fnd_global.CONC_REQUEST_ID;
9 declare
10
11 ret number;
12 l_gre_name per_ca_ee_extract_pkg.tab_varchar2;
13
14 begin
15
16 null;
17 c_business_group_name :=
18 hr_reports.get_business_group(p_business_group_id);
19
20
21
22 ret := per_ca_ee_extract_pkg.check_gre_without_naic(p_business_group_id,
23 l_gre_name) ;
24 if ret = -1 then
25 /*srw.message(1,'The following GRE(s) has NAIC missing: ');*/null;
26
27 for i in 1..l_gre_name.count loop
28 /*srw.message(2,l_gre_name(i));*/null;
29
30 end loop;
31
32 return (FALSE);
33 else
34
35 ret := per_ca_ee_extract_pkg.form1(p_business_group_id,
36 p_conc_request_id,
37 p_year,
38 p_naic_code,
39 fnd_date.canonical_to_date(p_date_all_emp),
40 fnd_date.canonical_to_date(p_date_tmp_emp));
41
42 ret := per_ca_ee_extract_pkg.form2n(p_business_group_id,
43 p_conc_request_id,
44 p_year,
45 fnd_date.canonical_to_date(p_date_tmp_emp));
46
47 ret := per_ca_ee_extract_pkg.form2(p_business_group_id,
48 p_conc_request_id,
49 p_year,
50 fnd_date.canonical_to_date(p_date_tmp_emp));
51
52
53
54 ret := per_ca_ee_extract_pkg.form3(p_business_group_id,
55 p_conc_request_id,
56 p_year,
57 fnd_date.canonical_to_date(p_date_tmp_emp));
58
59 ret := per_ca_ee_extract_pkg.form4(p_business_group_id,
60 p_conc_request_id,
61 p_year,
62 fnd_date.canonical_to_date(p_date_tmp_emp));
63 ret := per_ca_ee_extract_pkg.form5(p_business_group_id,
64 p_conc_request_id,
65 p_year,
66 fnd_date.canonical_to_date(p_date_tmp_emp));
67 ret := per_ca_ee_extract_pkg.form6(p_business_group_id,
68 p_conc_request_id,
69 p_year,
70 fnd_date.canonical_to_date(p_date_tmp_emp));
71 ret := per_ca_ee_extract_pkg.update_rec(p_conc_request_id);
72 return (TRUE);
73 end if;
74 end; end;
75
76 function CF_f5_n_promptFormula return Char is
77 begin
78 declare
79 cursor cur_data is select
80 'TOTAL NO OF PROMOTIONS' from dual;
81
82 l_string varchar2(30);
83 begin
84 open cur_data;
85 fetch cur_data into l_string;
86 close cur_data;
87
88 return l_string;
89
90 end;
91 end;
92
93 function cf_f5_n_totalformula(f4_n_type in varchar2) return number is
94 begin
95 declare
96 cursor cur_f5_n_total is
97 select
98 to_number(segment3) from
99 per_ca_ee_report_lines where
100 request_id = p_conc_request_id and
101 context = 'FORM5P' and
102 substr(f4_n_type,1,5) = 'FORM5' and
103 segment1 = 'NATIONAL' and
104 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
105
106 v_temp number;
107
108 begin
109
110 open cur_f5_n_total;
111 fetch cur_f5_n_total into v_temp;
112 close cur_f5_n_total;
113
114 return v_temp;
115 end;
116 end;
117
118 function cf_f5_n_totmaformula(f4_n_type in varchar2) return number is
119 begin
120 declare
121 cursor cur_f5_n_total is
122 select
123 to_number(segment4) from
124 per_ca_ee_report_lines where
125 request_id = p_conc_request_id and
126 context = 'FORM5P' and
127 substr(f4_n_type,1,5) = 'FORM5' and
128 segment1 = 'NATIONAL' and
129 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
130
131 v_temp number;
132
133 begin
134
135 open cur_f5_n_total;
136 fetch cur_f5_n_total into v_temp;
137 close cur_f5_n_total;
138
139 return v_temp;
140 end;
141 end;
142
143 function cf_f5_n_totfeformula(f4_n_type in varchar2) return number is
144 begin
145 declare
146 cursor cur_f5_n_total is
147 select
148 to_number(segment5) from
149 per_ca_ee_report_lines where
150 request_id = p_conc_request_id and
151 context = 'FORM5P' and
152 substr(f4_n_type,1,5) = 'FORM5' and
153 segment1 = 'NATIONAL' and
154 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
155
156 v_temp number;
157
158 begin
159
160 open cur_f5_n_total;
161 fetch cur_f5_n_total into v_temp;
162 close cur_f5_n_total;
163
164 return v_temp;
165 end;
166 end;
167
168 function cf_f5_n_totabformula(f4_n_type in varchar2) return number is
169 begin
170 declare
171 cursor cur_f5_n_total is
172 select
173 to_number(segment6) from
174 per_ca_ee_report_lines where
175 request_id = p_conc_request_id and
176 context = 'FORM5P' and
177 substr(f4_n_type,1,5) = 'FORM5' and
178 segment1 = 'NATIONAL' and
179 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
180
181 v_temp number;
182
183 begin
184
185 open cur_f5_n_total;
186 fetch cur_f5_n_total into v_temp;
187 close cur_f5_n_total;
188
189 return v_temp;
190 end;
191 end;
192
193 function cf_f5_n_maabformula(f4_n_type in varchar2) return number is
194 begin
195 declare
196 cursor cur_f5_n_total is
197 select
198 to_number(segment7) from
199 per_ca_ee_report_lines where
200 request_id = p_conc_request_id and
201 context = 'FORM5P' and
202 substr(f4_n_type,1,5) = 'FORM5' and
203 segment1 = 'NATIONAL' and
204 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
205
206 v_temp number;
207
208 begin
209
210 open cur_f5_n_total;
211 fetch cur_f5_n_total into v_temp;
212 close cur_f5_n_total;
213
214 return v_temp;
215 end;
216 end;
217
218 function cf_f5_n_feabformula(f4_n_type in varchar2) return number is
219 begin
220 declare
221 cursor cur_f5_n_total is
222 select
223 to_number(segment8) from
224 per_ca_ee_report_lines where
225 request_id = p_conc_request_id and
226 context = 'FORM5P' and
227 substr(f4_n_type,1,5) = 'FORM5' and
228 segment1 = 'NATIONAL' and
229 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
230
231 v_temp number;
232
233 begin
234
235 open cur_f5_n_total;
236 fetch cur_f5_n_total into v_temp;
237 close cur_f5_n_total;
238
239 return v_temp;
240 end;
241 end;
242
243 function cf_f5_n_totviformula(f4_n_type in varchar2) return number is
244 begin
245 declare
246 cursor cur_f5_n_total is
247 select
248 to_number(segment9) from
249 per_ca_ee_report_lines where
250 request_id = p_conc_request_id and
251 context = 'FORM5P' and
252 substr(f4_n_type,1,5) = 'FORM5' and
253 segment1 = 'NATIONAL' and
254 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
255
256 v_temp number;
257
258 begin
259
260 open cur_f5_n_total;
261 fetch cur_f5_n_total into v_temp;
262 close cur_f5_n_total;
263
264 return v_temp;
265 end;
266 end;
267
268 function cf_f5_n_maviformula(f4_n_type in varchar2) return number is
269 begin
270 declare
271 cursor cur_f5_n_total is
272 select
273 to_number(segment10) from
274 per_ca_ee_report_lines where
275 request_id = p_conc_request_id and
276 context = 'FORM5P' and
277 substr(f4_n_type,1,5) = 'FORM5' and
278 segment1 = 'NATIONAL' and
279 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
280
281 v_temp number;
282
283 begin
284
285 open cur_f5_n_total;
286 fetch cur_f5_n_total into v_temp;
287 close cur_f5_n_total;
288
289 return v_temp;
290 end;
291 end;
292
293 function cf_f5_n_feviformula(f4_n_type in varchar2) return number is
294 begin
295 declare
296 cursor cur_f5_n_total is
297 select
298 to_number(segment11) from
299 per_ca_ee_report_lines where
300 request_id = p_conc_request_id and
301 context = 'FORM5P' and
302 substr(f4_n_type,1,5) = 'FORM5' and
303 segment1 = 'NATIONAL' and
304 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
305
306 v_temp number;
307
308 begin
309
310 open cur_f5_n_total;
311 fetch cur_f5_n_total into v_temp;
312 close cur_f5_n_total;
313
314 return v_temp;
315 end;
316 end;
317
318 function cf_f5_n_totdiformula(f4_n_type in varchar2) return number is
319 begin
320 declare
321 cursor cur_f5_n_total is
322 select
323 to_number(segment12) from
324 per_ca_ee_report_lines where
325 request_id = p_conc_request_id and
326 context = 'FORM5P' and
327 substr(f4_n_type,1,5) = 'FORM5' and
328 segment1 = 'NATIONAL' and
329 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
330
331 v_temp number;
332
333 begin
334
335 open cur_f5_n_total;
336 fetch cur_f5_n_total into v_temp;
337 close cur_f5_n_total;
338
339 return v_temp;
340 end;
341 end;
342
343 function cf_f5_n_madiformula(f4_n_type in varchar2) return number is
344 begin
345 declare
346 cursor cur_f5_n_total is
347 select
348 to_number(segment13) from
349 per_ca_ee_report_lines where
350 request_id = p_conc_request_id and
351 context = 'FORM5P' and
352 substr(f4_n_type,1,5) = 'FORM5' and
353 segment1 = 'NATIONAL' and
354 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
355
356 v_temp number;
357
358 begin
359
360 open cur_f5_n_total;
361 fetch cur_f5_n_total into v_temp;
362 close cur_f5_n_total;
363
364 return v_temp;
365 end;
366 end;
367
368 function cf_f5_n_fediformula(f4_n_type in varchar2) return number is
369 begin
370 declare
371 cursor cur_f5_n_total is
372 select
373 to_number(segment14) from
374 per_ca_ee_report_lines where
375 request_id = p_conc_request_id and
376 context = 'FORM5P' and
377 substr(f4_n_type,1,5) = 'FORM5' and
378 segment1 = 'NATIONAL' and
379 segment2 =decode(substr(f4_n_type,6,1),'A','FR','B','PR','C','PT');
380
381 v_temp number;
382
383 begin
384
385 open cur_f5_n_total;
386 fetch cur_f5_n_total into v_temp;
387 close cur_f5_n_total;
388
389 return v_temp;
390 end;
391 end;
392
393 function cf_f5_p_totalformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
394 begin
395 declare
396 cursor cur_f5_n_total is
397 select
398 to_number(segment4) from
399 per_ca_ee_report_lines where
400 request_id = p_conc_request_id and
401 context = 'FORM5P' and
402 substr(f4_p_type,1,5) = 'FORM5' and
403 segment1 = 'PROVINCE' and
404 segment2 = f4_p_name1 and
405 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
406
407 v_temp number;
408
409 begin
410
411 open cur_f5_n_total;
412 fetch cur_f5_n_total into v_temp;
413 close cur_f5_n_total;
414
415 return v_temp;
416 end;
417 end;
418
419 function cf_f5_p_totmaformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
420 begin
421 declare
422 cursor cur_f5_n_total is
423 select
424 to_number(segment5) from
425 per_ca_ee_report_lines where
426 request_id = p_conc_request_id and
427 context = 'FORM5P' and
428 substr(f4_p_type,1,5) = 'FORM5' and
429 segment1 = 'PROVINCE' and
430 segment2 = f4_p_name1 and
431 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
432
433 v_temp number;
434
435 begin
436
437 open cur_f5_n_total;
438 fetch cur_f5_n_total into v_temp;
439 close cur_f5_n_total;
440
441 return v_temp;
442 end;
443 end;
444
445 function cf_f5_p_totfeformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
446 begin
447 declare
448 cursor cur_f5_n_total is
449 select
450 to_number(segment6) from
451 per_ca_ee_report_lines where
452 request_id = p_conc_request_id and
453 context = 'FORM5P' and
454 substr(f4_p_type,1,5) = 'FORM5' and
455 segment1 = 'PROVINCE' and
456 segment2 = f4_p_name1 and
457 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
458
459 v_temp number;
460
461 begin
462
463 open cur_f5_n_total;
464 fetch cur_f5_n_total into v_temp;
465 close cur_f5_n_total;
466
467 return v_temp;
468 end;
469 end;
470
471 function cf_f5_p_totabformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
472 begin
473 declare
474 cursor cur_f5_n_total is
475 select
476 to_number(segment7) from
477 per_ca_ee_report_lines where
478 request_id = p_conc_request_id and
479 context = 'FORM5P' and
480 substr(f4_p_type,1,5) = 'FORM5' and
481 segment1 = 'PROVINCE' and
482 segment2 = f4_p_name1 and
483 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
484
485 v_temp number;
486
487 begin
488
489 open cur_f5_n_total;
490 fetch cur_f5_n_total into v_temp;
491 close cur_f5_n_total;
492
493 return v_temp;
494 end;
495 end;
496
497 function cf_f5_p_maabformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
498 begin
499 declare
500 cursor cur_f5_n_total is
501 select
502 to_number(segment8) from
503 per_ca_ee_report_lines where
504 request_id = p_conc_request_id and
505 context = 'FORM5P' and
506 substr(f4_p_type,1,5) = 'FORM5' and
507 segment1 = 'PROVINCE' and
508 segment2 = f4_p_name1 and
509 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
510
511 v_temp number;
512
513 begin
514
515 open cur_f5_n_total;
516 fetch cur_f5_n_total into v_temp;
517 close cur_f5_n_total;
518
519 return v_temp;
520 end;
521 end;
522
523 function cf_f5_p_feabformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
524 begin
525 declare
526 cursor cur_f5_n_total is
527 select
528 to_number(segment9) from
529 per_ca_ee_report_lines where
530 request_id = p_conc_request_id and
531 context = 'FORM5P' and
532 substr(f4_p_type,1,5) = 'FORM5' and
533 segment1 = 'PROVINCE' and
534 segment2 = f4_p_name1 and
535 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
536
537 v_temp number;
538
539 begin
540
541 open cur_f5_n_total;
542 fetch cur_f5_n_total into v_temp;
543 close cur_f5_n_total;
544
545 return v_temp;
546 end;
547 end;
548
549 function cf_f5_p_totdiformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
550 begin
551 declare
552 cursor cur_f5_n_total is
553 select
554 to_number(segment13) from
555 per_ca_ee_report_lines where
556 request_id = p_conc_request_id and
557 context = 'FORM5P' and
558 substr(f4_p_type,1,5) = 'FORM5' and
559 segment1 = 'PROVINCE' and
560 segment2 = f4_p_name1 and
561 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
562
563 v_temp number;
564
565 begin
566
567 open cur_f5_n_total;
568 fetch cur_f5_n_total into v_temp;
569 close cur_f5_n_total;
570
571 return v_temp;
572 end;
573 end;
574
575 function cf_f5_p_madiformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
576 begin
577 declare
578 cursor cur_f5_n_total is
579 select
580 to_number(segment14) from
581 per_ca_ee_report_lines where
582 request_id = p_conc_request_id and
583 context = 'FORM5P' and
584 substr(f4_p_type,1,5) = 'FORM5' and
585 segment1 = 'PROVINCE' and
586 segment2 = f4_p_name1 and
587 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
588
589 v_temp number;
590
591 begin
592
593 open cur_f5_n_total;
594 fetch cur_f5_n_total into v_temp;
595 close cur_f5_n_total;
596
597 return v_temp;
598 end;
599 end;
600
601 function cf_f5_p_fediformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
602 begin
603 declare
604 cursor cur_f5_n_total is
605 select
606 to_number(segment15) from
607 per_ca_ee_report_lines where
608 request_id = p_conc_request_id and
609 context = 'FORM5P' and
610 substr(f4_p_type,1,5) = 'FORM5' and
611 segment1 = 'PROVINCE' and
612 segment2 = f4_p_name1 and
613 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
614
615 v_temp number;
616
617 begin
618
619 open cur_f5_n_total;
620 fetch cur_f5_n_total into v_temp;
621 close cur_f5_n_total;
622
623 return v_temp;
624 end;
625 end;
626
627 function cf_f5_p_totviformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
628 begin
629 declare
630 cursor cur_f5_n_total is
631 select
632 to_number(segment10) from
633 per_ca_ee_report_lines where
634 request_id = p_conc_request_id and
635 context = 'FORM5P' and
636 substr(f4_p_type,1,5) = 'FORM5' and
637 segment1 = 'PROVINCE' and
638 segment2 = f4_p_name1 and
639 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
640
641 v_temp number;
642
643 begin
644
645 open cur_f5_n_total;
646 fetch cur_f5_n_total into v_temp;
647 close cur_f5_n_total;
648
649 return v_temp;
650 end;
651 end;
652
653 function cf_f5_p_maviformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
654 begin
655 declare
656 cursor cur_f5_n_total is
657 select
658 to_number(segment11) from
659 per_ca_ee_report_lines where
660 request_id = p_conc_request_id and
661 context = 'FORM5P' and
662 substr(f4_p_type,1,5) = 'FORM5' and
663 segment1 = 'PROVINCE' and
664 segment2 = f4_p_name1 and
665 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
666
667 v_temp number;
668
669 begin
670
671 open cur_f5_n_total;
672 fetch cur_f5_n_total into v_temp;
673 close cur_f5_n_total;
674
675 return v_temp;
676 end;
677 end;
678
679 function cf_f5_p_feviformula(f4_p_type in varchar2, f4_p_name1 in varchar2) return number is
680 begin
681 declare
682 cursor cur_f5_n_total is
683 select
684 to_number(segment12) from
685 per_ca_ee_report_lines where
686 request_id = p_conc_request_id and
687 context = 'FORM5P' and
688 substr(f4_p_type,1,5) = 'FORM5' and
689 segment1 = 'PROVINCE' and
690 segment2 = f4_p_name1 and
691 segment3 =decode(substr(f4_p_type,6,1),'A','FR','B','PR','C','PT');
692
693 v_temp number;
694
695 begin
696
697 open cur_f5_n_total;
698 fetch cur_f5_n_total into v_temp;
699 close cur_f5_n_total;
700
701 return v_temp;
702 end;
703 end;
704
705 function CF_f5_p_promptFormula return Char is
706 begin
707 declare
708 cursor cur_data is select
709 'TOTAL NUMBER OF PROMOTIONS' from dual;
710
711 l_string varchar2(30);
712 begin
713 open cur_data;
714 fetch cur_data into l_string;
715 close cur_data;
716
717 return l_string;
718
719 end;
720 end;
721
722 function AfterReport return boolean is
723 begin
724
725 /*SRW.DO_SQL('DELETE per_ca_ee_report_lines
726 WHERE request_id = :p_conc_request_id');*/null;
727
728
729 --hr_standard.event('AFTER REPORT');
730 return (TRUE);
731 end;
732
733 --Functions to refer Oracle report placeholders--
734
735 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
736 Begin
737 return C_BUSINESS_GROUP_NAME;
738 END;
739 Function C_REPORT_SUBTITLE_p return varchar2 is
740 Begin
741 return C_REPORT_SUBTITLE;
742 END;
743 END PER_PERCAEER_XMLP_PKG ;
[Home] [Help]