[Home] [Help]
PACKAGE BODY: APPS.PA_FP_OF_WEBADI_PKG
Source
1 PACKAGE BODY pa_fp_of_webadi_pkg as
2 /* $Header: PAFPOFWB.pls 120.1 2005/08/19 16:27:24 mwasowic noship $ */
3
4 PROCEDURE populate_interface_table
5 ( p_session_id IN NUMBER,
6 p_budget_version_id IN NUMBER,
7 p_amount_type_code IN VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
11 )
12 IS
13
14
15 v_category_id_tab PA_FORECAST_GLOB.NumberTabTyp;
16 v_org_id_tab PA_FORECAST_GLOB.NumberTabTyp;
17 l_budget_version_id NUMBER;
18 l_amount_type_code VARCHAR2(30);
19 l_session_id NUMBER;
20 Category_id_tab PA_FORECAST_GLOB.NumberTabTyp;
21 Category_tab PA_FORECAST_GLOB.VCTabTyp;
22 OU_id_tab PA_FORECAST_GLOB.NumberTabTyp;
23 OU_name_tab PA_FORECAST_GLOB.VCTabTyp;
24 Organization_Id_tab PA_FORECAST_GLOB.NumberTabTyp;
25 Organization_Name_tab PA_FORECAST_GLOB.VCTabTyp;
26 Other_OU_Id_tab PA_FORECAST_GLOB.NumberTabTyp;
27 Other_OU_Name_tab PA_FORECAST_GLOB.VCTabTyp;
28 Other_Organization_Id_tab PA_FORECAST_GLOB.NumberTabTyp;
29 Other_Organization_Name_tab PA_FORECAST_GLOB.VCTabTyp;
30 Txn_Project_Name_tab PA_FORECAST_GLOB.VCTabTyp;
31 Resource_Name_tab PA_FORECAST_GLOB.VCTabTyp;
32 Period1_tab PA_FORECAST_GLOB.NumberTabTyp;
33 Period2_tab PA_FORECAST_GLOB.NumberTabTyp;
34 Period3_tab PA_FORECAST_GLOB.NumberTabTyp;
35 Period4_tab PA_FORECAST_GLOB.NumberTabTyp;
36 Period5_tab PA_FORECAST_GLOB.NumberTabTyp;
37 Period6_tab PA_FORECAST_GLOB.NumberTabTyp;
38 Period7_tab PA_FORECAST_GLOB.NumberTabTyp;
39 Period8_tab PA_FORECAST_GLOB.NumberTabTyp;
40 Period9_tab PA_FORECAST_GLOB.NumberTabTyp;
41 Period10_tab PA_FORECAST_GLOB.NumberTabTyp;
42 Period11_tab PA_FORECAST_GLOB.NumberTabTyp;
43 Period12_tab PA_FORECAST_GLOB.NumberTabTyp;
44 Period13_tab PA_FORECAST_GLOB.NumberTabTyp;
45 Period14_tab PA_FORECAST_GLOB.NumberTabTyp;
46 Period15_tab PA_FORECAST_GLOB.NumberTabTyp;
47 Period16_tab PA_FORECAST_GLOB.NumberTabTyp;
48 Period17_tab PA_FORECAST_GLOB.NumberTabTyp;
49 Period18_tab PA_FORECAST_GLOB.NumberTabTyp;
50 Period19_tab PA_FORECAST_GLOB.NumberTabTyp;
51 Period20_tab PA_FORECAST_GLOB.NumberTabTyp;
52 Period21_tab PA_FORECAST_GLOB.NumberTabTyp;
53 Period22_tab PA_FORECAST_GLOB.NumberTabTyp;
54 Period23_tab PA_FORECAST_GLOB.NumberTabTyp;
55 Period24_tab PA_FORECAST_GLOB.NumberTabTyp;
56 Period25_tab PA_FORECAST_GLOB.NumberTabTyp;
57 Period26_tab PA_FORECAST_GLOB.NumberTabTyp;
58 Period27_tab PA_FORECAST_GLOB.NumberTabTyp;
59 Period28_tab PA_FORECAST_GLOB.NumberTabTyp;
60 Period29_tab PA_FORECAST_GLOB.NumberTabTyp;
61 Period30_tab PA_FORECAST_GLOB.NumberTabTyp;
62 Period31_tab PA_FORECAST_GLOB.NumberTabTyp;
63 Period32_tab PA_FORECAST_GLOB.NumberTabTyp;
64 Period33_tab PA_FORECAST_GLOB.NumberTabTyp;
65 Period34_tab PA_FORECAST_GLOB.NumberTabTyp;
66 Period35_tab PA_FORECAST_GLOB.NumberTabTyp;
67 Period36_tab PA_FORECAST_GLOB.NumberTabTyp;
68 Period37_tab PA_FORECAST_GLOB.NumberTabTyp;
69 Period38_tab PA_FORECAST_GLOB.NumberTabTyp;
70 Period39_tab PA_FORECAST_GLOB.NumberTabTyp;
71 Period40_tab PA_FORECAST_GLOB.NumberTabTyp;
72 Period41_tab PA_FORECAST_GLOB.NumberTabTyp;
73 Period42_tab PA_FORECAST_GLOB.NumberTabTyp;
74 Period43_tab PA_FORECAST_GLOB.NumberTabTyp;
75 Period44_tab PA_FORECAST_GLOB.NumberTabTyp;
76 Period45_tab PA_FORECAST_GLOB.NumberTabTyp;
77 Period46_tab PA_FORECAST_GLOB.NumberTabTyp;
78 Period47_tab PA_FORECAST_GLOB.NumberTabTyp;
79 Period48_tab PA_FORECAST_GLOB.NumberTabTyp;
80 Period49_tab PA_FORECAST_GLOB.NumberTabTyp;
81 Period50_tab PA_FORECAST_GLOB.NumberTabTyp;
82 Period51_tab PA_FORECAST_GLOB.NumberTabTyp;
83 Period52_tab PA_FORECAST_GLOB.NumberTabTyp;
84
85 i NUMBER;
86 j NUMBER;
87 k NUMBER;
88 m NUMBER;
89 n NUMBER;
90 l_category_id NUMBER;
91 ll_category_id NUMBER;
92 l_other_org_id NUMBER;
93 x NUMBER;
94 l_category_name VARCHAR2(30);
95 l_org_tot_period1 NUMBER;
96 l_org_tot_period2 NUMBER;
97 l_org_tot_period3 NUMBER;
98 l_org_tot_period4 NUMBER;
99 l_org_tot_period5 NUMBER;
100 l_org_tot_period6 NUMBER;
101 l_org_tot_period7 NUMBER;
102 l_org_tot_period8 NUMBER;
103 l_org_tot_period9 NUMBER;
104 l_org_tot_period10 NUMBER;
105 l_org_tot_period11 NUMBER;
106 l_org_tot_period12 NUMBER;
107 l_org_tot_period13 NUMBER;
108 l_org_tot_period14 NUMBER;
109 l_org_tot_period15 NUMBER;
110 l_org_tot_period16 NUMBER;
111 l_org_tot_period17 NUMBER;
112 l_org_tot_period18 NUMBER;
113 l_org_tot_period19 NUMBER;
114 l_org_tot_period20 NUMBER;
115 l_org_tot_period21 NUMBER;
116 l_org_tot_period22 NUMBER;
117 l_org_tot_period23 NUMBER;
118 l_org_tot_period24 NUMBER;
119 l_org_tot_period25 NUMBER;
120 l_org_tot_period26 NUMBER;
121 l_org_tot_period27 NUMBER;
122 l_org_tot_period28 NUMBER;
123 l_org_tot_period29 NUMBER;
124 l_org_tot_period30 NUMBER;
125 l_org_tot_period31 NUMBER;
126 l_org_tot_period32 NUMBER;
127 l_org_tot_period33 NUMBER;
128 l_org_tot_period34 NUMBER;
129 l_org_tot_period35 NUMBER;
130 l_org_tot_period36 NUMBER;
131 l_org_tot_period37 NUMBER;
132 l_org_tot_period38 NUMBER;
133 l_org_tot_period39 NUMBER;
134 l_org_tot_period40 NUMBER;
135 l_org_tot_period41 NUMBER;
136 l_org_tot_period42 NUMBER;
137 l_org_tot_period43 NUMBER;
138 l_org_tot_period44 NUMBER;
139 l_org_tot_period45 NUMBER;
140 l_org_tot_period46 NUMBER;
141 l_org_tot_period47 NUMBER;
142 l_org_tot_period48 NUMBER;
143 l_org_tot_period49 NUMBER;
144 l_org_tot_period50 NUMBER;
145 l_org_tot_period51 NUMBER;
146 l_org_tot_period52 NUMBER;
147
148
149 l_cat_tot_period1 NUMBER;
150 l_cat_tot_period2 NUMBER;
151 l_cat_tot_period3 NUMBER;
152 l_cat_tot_period4 NUMBER;
153 l_cat_tot_period5 NUMBER;
154 l_cat_tot_period6 NUMBER;
155 l_cat_tot_period7 NUMBER;
156 l_cat_tot_period8 NUMBER;
157 l_cat_tot_period9 NUMBER;
158 l_cat_tot_period10 NUMBER;
159 l_cat_tot_period11 NUMBER;
160 l_cat_tot_period12 NUMBER;
161 l_cat_tot_period13 NUMBER;
162 l_cat_tot_period14 NUMBER;
163 l_cat_tot_period15 NUMBER;
164 l_cat_tot_period16 NUMBER;
165 l_cat_tot_period17 NUMBER;
166 l_cat_tot_period18 NUMBER;
167 l_cat_tot_period19 NUMBER;
168 l_cat_tot_period20 NUMBER;
169 l_cat_tot_period21 NUMBER;
170 l_cat_tot_period22 NUMBER;
171 l_cat_tot_period23 NUMBER;
172 l_cat_tot_period24 NUMBER;
173 l_cat_tot_period25 NUMBER;
174 l_cat_tot_period26 NUMBER;
175 l_cat_tot_period27 NUMBER;
176 l_cat_tot_period28 NUMBER;
177 l_cat_tot_period29 NUMBER;
178 l_cat_tot_period30 NUMBER;
179 l_cat_tot_period31 NUMBER;
180 l_cat_tot_period32 NUMBER;
181 l_cat_tot_period33 NUMBER;
182 l_cat_tot_period34 NUMBER;
183 l_cat_tot_period35 NUMBER;
184 l_cat_tot_period36 NUMBER;
185 l_cat_tot_period37 NUMBER;
186 l_cat_tot_period38 NUMBER;
187 l_cat_tot_period39 NUMBER;
188 l_cat_tot_period40 NUMBER;
189 l_cat_tot_period41 NUMBER;
190 l_cat_tot_period42 NUMBER;
191 l_cat_tot_period43 NUMBER;
192 l_cat_tot_period44 NUMBER;
193 l_cat_tot_period45 NUMBER;
194 l_cat_tot_period46 NUMBER;
195 l_cat_tot_period47 NUMBER;
196 l_cat_tot_period48 NUMBER;
197 l_cat_tot_period49 NUMBER;
198 l_cat_tot_period50 NUMBER;
199 l_cat_tot_period51 NUMBER;
200 l_cat_tot_period52 NUMBER;
201
202
203 CURSOR C1 IS
204 SELECT distinct category_id
205 FROM PA_FP_OF_WEBADI_V;
206
207 CURSOR C2(l_category_id number) IS
208 SELECT distinct Other_Organization_Id
209 FROM PA_FP_OF_WEBADI_V
210 Where category_id = l_category_id;
211
212 CURSOR C3(l_category_id number,l_other_org_id number) IS
213 SELECT
214
215 Category_id,
216 Category,
217 OU_id,
218 OU_name,
219 Organization_Id,
220 Organization_Name,
221 Other_OU_Id,
222 Other_OU_Name,
223 Other_Organization_Id,
224 Other_Organization_Name,
225 Txn_Project_Name,
226 Resource_Name,
227 Period1,
228 Period2,
229 Period3,
230 Period4,
231 Period5,
232 Period6,
233 Period7,
234 Period8,
235 Period9,
236 Period10,
237 Period11,
238 Period12,
239 Period13,
240 Period14,
241 Period15,
242 Period16,
243 Period17,
244 Period18,
245 Period19,
246 Period20,
247 Period21,
248 Period22,
249 Period23,
250 Period24,
251 Period25,
252 Period26,
253 Period27,
254 Period28,
255 Period29,
256 Period30,
257 Period31,
258 Period32,
259 Period33,
260 Period34,
261 Period35,
262 Period36,
263 Period37,
264 Period38,
265 Period39,
266 Period40,
267 Period41,
268 Period42,
269 Period43,
270 Period44,
271 Period45,
272 Period46,
273 Period47,
274 Period48,
275 Period49,
276 Period50,
277 Period51,
278 Period52
279 FROM PA_FP_OF_WEBADI_V
280 Where Category_id = l_category_id
281 and Other_Organization_Id =l_other_org_id;
282
283 BEGIN
284 i := 0;
285 j := 0;
286 m := 0;
287 n := 0;
288 k := 0;
289 l_session_id := p_session_id;
290 l_budget_version_id := p_budget_version_id;
291 l_amount_type_code := p_amount_type_code;
292
293 delete from pa_fp_of_webadi_xface
294 where session_id = l_session_id
295 and amount_type_code = l_amount_type_code;
296
297
298 OPEN C1;
299
300 LOOP
301 --dbms_output.put_line('i '||i);
302 FETCH C1 INTO v_category_id_tab(i);
303 i := i+1;
304 EXIT WHEN C1%NOTFOUND;
305 END LOOP;
306
307 CLOSE C1;
308 --dbms_output.put_line('i '||i);
309
310
311 FOR m IN 0 .. i-2 LOOP
312 --dbms_output.put_line('category id '||v_category_id_tab(m));
313 l_category_id := v_category_id_tab(m);
314
315 j:=0;
316 OPEN C2(l_category_id);
317
318 LOOP
319
320 FETCH C2 INTO v_org_id_tab(j);
321 j := j+1;
322 EXIT WHEN C2%NOTFOUND;
323
324 END LOOP;
325
326 CLOSE C2;
327 --dbms_output.put_line('j '||j);
328
329 FOR n IN 0..j-2 LOOP
330 l_other_org_id := v_org_id_tab(n);
331 --dbms_output.put_line('cat id '||l_category_id);
332 --dbms_output.put_line('org id '||l_other_org_id);
333 k := 0;
334 OPEN C3(l_category_id,l_other_org_id);
335
336 LOOP
337 k := k+1;
338 FETCH C3 INTO Category_id_tab(k),
339 Category_tab(k),
340 OU_id_tab(k),
341 OU_name_tab(k),
342 Organization_Id_tab(k),
343 Organization_Name_tab(k),
344 Other_OU_Id_tab(k),
345 Other_OU_Name_tab(k),
346 Other_Organization_Id_tab(k),
347 Other_Organization_Name_tab(k),
348 Txn_Project_Name_tab(k),
349 Resource_Name_tab(k),
350 Period1_tab(k),
351 Period2_tab(k),
352 Period3_tab(k),
353 Period4_tab(k),
354 Period5_tab(k),
355 Period6_tab(k),
356 Period7_tab(k),
357 Period8_tab(k),
358 Period9_tab(k),
359 Period10_tab(k),
360 Period11_tab(k),
361 Period12_tab(k),
362 Period13_tab(k),
363 Period14_tab(k),
364 Period15_tab(k),
365 Period16_tab(k),
366 Period17_tab(k),
367 Period18_tab(k),
368 Period19_tab(k),
369 Period20_tab(k),
370 Period21_tab(k),
371 Period22_tab(k),
372 Period23_tab(k),
373 Period24_tab(k),
374 Period25_tab(k),
375 Period26_tab(k),
376 Period27_tab(k),
377 Period28_tab(k),
378 Period29_tab(k),
379 Period30_tab(k),
380 Period31_tab(k),
381 Period32_tab(k),
382 Period33_tab(k),
383 Period34_tab(k),
384 Period35_tab(k),
385 Period36_tab(k),
386 Period37_tab(k),
387 Period38_tab(k),
388 Period39_tab(k),
389 Period40_tab(k),
390 Period41_tab(k),
391 Period42_tab(k),
392 Period43_tab(k),
393 Period44_tab(k),
394 Period45_tab(k),
395 Period46_tab(k),
396 Period47_tab(k),
397 Period48_tab(k),
398 Period49_tab(k),
399 Period50_tab(k),
400 Period51_tab(k),
401 Period52_tab(k);
402
403 EXIT WHEN C3%NOTFOUND;
404
405 END LOOP;
406
407 CLOSE C3;
408
409 FORALL x in Category_id_tab.first..Category_id_tab.last
410 INSERT INTO pa_fp_of_webadi_xface
411 (Session_id,
412 budget_version_id,
413 amount_type_code,
414 Category_id,
415 Category,
416 org_id,
417 Other_Organization_Name,
418 Project_Name,
419 Resource_Name,
420 Prd1,
421 Prd2,
422 Prd3,
423 Prd4,
424 Prd5,
425 Prd6,
426 Prd7,
427 Prd8,
428 Prd9,
429 Prd10,
430 Prd11,
431 Prd12,
432 Prd13,
433 Prd14,
434 Prd15,
435 Prd16,
436 Prd17,
437 Prd18,
438 Prd19,
439 Prd20,
440 Prd21,
441 Prd22,
442 Prd23,
443 Prd24,
444 Prd25,
445 Prd26,
446 Prd27,
447 Prd28,
448 Prd29,
449 Prd30,
450 Prd31,
451 Prd32,
452 Prd33,
453 Prd34,
454 Prd35,
455 Prd36,
456 Prd37,
457 Prd38,
458 Prd39,
459 Prd40,
460 Prd41,
461 Prd42,
462 Prd43,
463 Prd44,
464 Prd45,
465 Prd46,
466 Prd47,
467 Prd48,
468 Prd49,
469 Prd50,
470 Prd51,
471 Prd52)
472 VALUES
473 (l_session_id,
474 l_budget_version_id,
475 l_amount_type_code,
476 Category_id_tab(x),
477 Category_tab(x),
478 OU_id_tab(x),
479 Other_Organization_Name_tab(x)||'--'||Other_OU_Name_tab(x),
480 Txn_Project_Name_tab(x),
481 Resource_Name_tab(x),
482 Period1_tab(x),
483 Period2_tab(x),
484 Period3_tab(x),
485 Period4_tab(x),
486 Period5_tab(x),
487 Period6_tab(x),
488 Period7_tab(x),
489 Period8_tab(x),
490 Period9_tab(x),
491 Period10_tab(x),
492 Period11_tab(x),
493 Period12_tab(x),
494 Period13_tab(x),
495 Period14_tab(x),
496 Period15_tab(x),
497 Period16_tab(x),
498 Period17_tab(x),
499 Period18_tab(x),
500 Period19_tab(x),
501 Period20_tab(x),
502 Period21_tab(x),
503 Period22_tab(x),
504 Period23_tab(x),
505 Period24_tab(x),
506 Period25_tab(x),
507 Period26_tab(x),
508 Period27_tab(x),
509 Period28_tab(x),
510 Period29_tab(x),
511 Period30_tab(x),
512 Period31_tab(x),
513 Period32_tab(x),
514 Period33_tab(x),
515 Period34_tab(x),
516 Period35_tab(x),
517 Period36_tab(x),
518 Period37_tab(x),
519 Period38_tab(x),
520 Period39_tab(x),
521 Period40_tab(x),
522 Period41_tab(x),
523 Period42_tab(x),
524 Period43_tab(x),
525 Period44_tab(x),
526 Period45_tab(x),
527 Period46_tab(x),
528 Period47_tab(x),
529 Period48_tab(x),
530 Period49_tab(x),
531 Period50_tab(x),
532 Period51_tab(x),
533 Period52_tab(x));
534
535 --dbms_output.put_line('x: '||x);
536 --dbms_output.put_line('calculating org total');
537
538 select sum(period1),
539 sum(period2),
540 sum(period3),
541 sum(period4),
542 sum(period5),
543 sum(period6),
544 sum(period7),
545 sum(period8),
546 sum(period9),
547 sum(period10),
548 sum(period11),
549 sum(period12),
550 sum(period13),
551 sum(period14),
552 sum(period15),
553 sum(period16),
554 sum(period17),
555 sum(period18),
556 sum(period19),
557 sum(period20),
558 sum(period21),
559 sum(period22),
560 sum(period23),
561 sum(period24),
562 sum(period25),
563 sum(period26),
564 sum(period27),
565 sum(period28),
566 sum(period29),
567 sum(period30),
568 sum(period31),
569 sum(period32),
570 sum(period33),
571 sum(period34),
572 sum(period35),
573 sum(period36),
574 sum(period37),
575 sum(period38),
576 sum(period39),
577 sum(period40),
578 sum(period41),
579 sum(period42),
580 sum(period43),
581 sum(period44),
582 sum(period45),
583 sum(period46),
584 sum(period47),
585 sum(period48),
586 sum(period49),
587 sum(period50),
588 sum(period51),
589 sum(period52)
590 into l_org_tot_period1,
591 l_org_tot_period2,
592 l_org_tot_period3,
593 l_org_tot_period4,
594 l_org_tot_period5,
595 l_org_tot_period6,
596 l_org_tot_period7,
597 l_org_tot_period8,
598 l_org_tot_period9,
599 l_org_tot_period10,
600 l_org_tot_period11,
601 l_org_tot_period12,
602 l_org_tot_period13,
603 l_org_tot_period14,
604 l_org_tot_period15,
605 l_org_tot_period16,
606 l_org_tot_period17,
607 l_org_tot_period18,
608 l_org_tot_period19,
609 l_org_tot_period20,
610 l_org_tot_period21,
611 l_org_tot_period22,
612 l_org_tot_period23,
613 l_org_tot_period24,
614 l_org_tot_period25,
615 l_org_tot_period26,
616 l_org_tot_period27,
617 l_org_tot_period28,
618 l_org_tot_period29,
619 l_org_tot_period30,
620 l_org_tot_period31,
621 l_org_tot_period32,
622 l_org_tot_period33,
623 l_org_tot_period34,
624 l_org_tot_period35,
625 l_org_tot_period36,
626 l_org_tot_period37,
627 l_org_tot_period38,
628 l_org_tot_period39,
629 l_org_tot_period40,
630 l_org_tot_period41,
631 l_org_tot_period42,
632 l_org_tot_period43,
633 l_org_tot_period44,
634 l_org_tot_period45,
635 l_org_tot_period46,
636 l_org_tot_period47,
637 l_org_tot_period48,
638 l_org_tot_period49,
639 l_org_tot_period50,
640 l_org_tot_period51,
641 l_org_tot_period52
642 From PA_FP_OF_WEBADI_V
643 Where category_id = l_category_id
644 And Other_Organization_Id = l_other_org_id;
645
646
647 --dbms_output.put_line('Done calculating org total');
648
649
650 Insert INTO pa_fp_of_webadi_xface
651 (Session_id,
652 budget_version_id,
653 amount_type_code,
654 Category_id,
655 Category,
656 Other_Organization_Name,
657 Prd1,
658 Prd2,
659 Prd3,
660 Prd4,
661 Prd5,
662 Prd6,
663 Prd7,
664 Prd8,
665 Prd9,
666 Prd10,
667 Prd11,
668 Prd12,
669 Prd13,
670 Prd14,
671 Prd15,
672 Prd16,
673 Prd17,
674 Prd18,
675 Prd19,
676 Prd20,
677 Prd21,
678 Prd22,
679 Prd23,
680 Prd24,
681 Prd25,
682 Prd26,
683 Prd27,
684 Prd28,
685 Prd29,
686 Prd30,
687 Prd31,
688 Prd32,
689 Prd33,
690 Prd34,
691 Prd35,
692 Prd36,
693 Prd37,
694 Prd38,
695 Prd39,
696 Prd40,
697 Prd41,
698 Prd42,
699 Prd43,
700 Prd44,
701 Prd45,
702 Prd46,
703 Prd47,
704 Prd48,
705 Prd49,
706 Prd50,
707 Prd51,
708 Prd52 )
709 VALUES( l_session_id,
710 l_budget_version_id,
711 l_amount_type_code,
712 Category_id_tab(1),
713 Category_tab(1),
714 Other_Organization_Name_tab(1)||'--'||Other_OU_Name_tab(1)||' Total',
715 l_org_tot_period1,
716 l_org_tot_period2,
717 l_org_tot_period3,
718 l_org_tot_period4,
719 l_org_tot_period5,
720 l_org_tot_period6,
721 l_org_tot_period7,
722 l_org_tot_period8,
723 l_org_tot_period9,
724 l_org_tot_period10,
725 l_org_tot_period11,
726 l_org_tot_period12,
727 l_org_tot_period13,
728 l_org_tot_period14,
729 l_org_tot_period15,
730 l_org_tot_period16,
731 l_org_tot_period17,
732 l_org_tot_period18,
733 l_org_tot_period19,
734 l_org_tot_period20,
735 l_org_tot_period21,
736 l_org_tot_period22,
737 l_org_tot_period23,
738 l_org_tot_period24,
739 l_org_tot_period25,
740 l_org_tot_period26,
741 l_org_tot_period27,
742 l_org_tot_period28,
743 l_org_tot_period29,
744 l_org_tot_period30,
745 l_org_tot_period31,
746 l_org_tot_period32,
747 l_org_tot_period33,
748 l_org_tot_period34,
749 l_org_tot_period35,
750 l_org_tot_period36,
751 l_org_tot_period37,
752 l_org_tot_period38,
753 l_org_tot_period39,
754 l_org_tot_period40,
755 l_org_tot_period41,
756 l_org_tot_period42,
757 l_org_tot_period43,
758 l_org_tot_period44,
759 l_org_tot_period45,
760 l_org_tot_period46,
761 l_org_tot_period47,
762 l_org_tot_period48,
763 l_org_tot_period49,
764 l_org_tot_period50,
765 l_org_tot_period51,
766 l_org_tot_period52);
767
768 l_category_name := Category_tab(1);
769 ll_category_id := Category_id_tab(1);
770
771 Category_id_tab.delete;
772 Category_tab.delete;
773 OU_id_tab.delete;
774 OU_name_tab.delete;
775 Organization_Id_tab.delete;
776 Organization_Name_tab.delete;
777 Other_OU_Id_tab.delete;
778 Other_OU_Name_tab.delete;
779 Other_Organization_Id_tab.delete;
780 Other_Organization_Name_tab.delete;
781 Txn_Project_Name_tab.delete;
782 Resource_Name_tab.delete;
783 Period1_tab.delete;
784 Period2_tab.delete;
785 Period3_tab.delete;
786 Period4_tab.delete;
787 Period5_tab.delete;
788 Period6_tab.delete;
789 Period7_tab.delete;
790 Period8_tab.delete;
791 Period9_tab.delete;
792 Period10_tab.delete;
793 Period11_tab.delete;
794 Period12_tab.delete;
795 Period13_tab.delete;
796 Period14_tab.delete;
797 Period15_tab.delete;
798 Period16_tab.delete;
799 Period17_tab.delete;
800 Period18_tab.delete;
801 Period19_tab.delete;
802 Period20_tab.delete;
803 Period21_tab.delete;
804 Period22_tab.delete;
805 Period23_tab.delete;
806 Period24_tab.delete;
807 Period25_tab.delete;
808 Period26_tab.delete;
809 Period27_tab.delete;
810 Period28_tab.delete;
811 Period29_tab.delete;
812 Period30_tab.delete;
813 Period31_tab.delete;
814 Period32_tab.delete;
815 Period33_tab.delete;
816 Period34_tab.delete;
817 Period35_tab.delete;
818 Period36_tab.delete;
819 Period37_tab.delete;
820 Period38_tab.delete;
821 Period39_tab.delete;
822 Period40_tab.delete;
823 Period41_tab.delete;
824 Period42_tab.delete;
825 Period43_tab.delete;
826 Period44_tab.delete;
827 Period45_tab.delete;
828 Period46_tab.delete;
829 Period47_tab.delete;
830 Period48_tab.delete;
831 Period49_tab.delete;
832 Period50_tab.delete;
833 Period51_tab.delete;
834 Period52_tab.delete;
835
836
837
838 END LOOP; --for loop n
839
840 --dbms_output.put_line('calculating cat total');
841 select sum(period1),
842 sum(period2),
843 sum(period3),
844 sum(period4),
845 sum(period5),
846 sum(period6),
847 sum(period7),
848 sum(period8),
849 sum(period9),
850 sum(period10),
851 sum(period11),
852 sum(period12),
853 sum(period13),
854 sum(period14),
855 sum(period15),
856 sum(period16),
857 sum(period17),
858 sum(period18),
859 sum(period19),
860 sum(period20),
861 sum(period21),
862 sum(period22),
863 sum(period23),
864 sum(period24),
865 sum(period25),
866 sum(period26),
867 sum(period27),
868 sum(period28),
869 sum(period29),
870 sum(period30),
871 sum(period31),
872 sum(period32),
873 sum(period33),
874 sum(period34),
875 sum(period35),
876 sum(period36),
877 sum(period37),
878 sum(period38),
879 sum(period39),
880 sum(period40),
881 sum(period41),
882 sum(period42),
883 sum(period43),
884 sum(period44),
885 sum(period45),
886 sum(period46),
887 sum(period47),
888 sum(period48),
889 sum(period49),
890 sum(period50),
891 sum(period51),
892 sum(period52)
893 into l_cat_tot_period1,
894 l_cat_tot_period2,
895 l_cat_tot_period3,
896 l_cat_tot_period4,
897 l_cat_tot_period5,
898 l_cat_tot_period6,
899 l_cat_tot_period7,
900 l_cat_tot_period8,
901 l_cat_tot_period9,
902 l_cat_tot_period10,
903 l_cat_tot_period11,
904 l_cat_tot_period12,
905 l_cat_tot_period13,
906 l_cat_tot_period14,
907 l_cat_tot_period15,
908 l_cat_tot_period16,
909 l_cat_tot_period17,
910 l_cat_tot_period18,
911 l_cat_tot_period19,
912 l_cat_tot_period20,
913 l_cat_tot_period21,
914 l_cat_tot_period22,
915 l_cat_tot_period23,
916 l_cat_tot_period24,
917 l_cat_tot_period25,
918 l_cat_tot_period26,
919 l_cat_tot_period27,
920 l_cat_tot_period28,
921 l_cat_tot_period29,
922 l_cat_tot_period30,
923 l_cat_tot_period31,
924 l_cat_tot_period32,
925 l_cat_tot_period33,
926 l_cat_tot_period34,
927 l_cat_tot_period35,
928 l_cat_tot_period36,
929 l_cat_tot_period37,
930 l_cat_tot_period38,
931 l_cat_tot_period39,
932 l_cat_tot_period40,
933 l_cat_tot_period41,
934 l_cat_tot_period42,
935 l_cat_tot_period43,
936 l_cat_tot_period44,
937 l_cat_tot_period45,
938 l_cat_tot_period46,
939 l_cat_tot_period47,
940 l_cat_tot_period48,
941 l_cat_tot_period49,
942 l_cat_tot_period50,
943 l_cat_tot_period51,
944 l_cat_tot_period52
945 From PA_FP_OF_WEBADI_V
946 Where category_id = l_category_id;
947
948 --dbms_output.put_line('done calculating cat total');
949
950
951 Insert INTO pa_fp_of_webadi_xface
952 (Session_id,
953 budget_version_id,
954 amount_type_code,
955 category_id,
956 category,
957 Prd1,
958 Prd2,
959 Prd3,
960 Prd4,
961 Prd5,
962 Prd6,
963 Prd7,
964 Prd8,
965 Prd9,
966 Prd10,
967 Prd11,
968 Prd12,
969 Prd13,
970 Prd14,
971 Prd15,
972 Prd16,
973 Prd17,
974 Prd18,
975 Prd19,
976 Prd20,
977 Prd21,
978 Prd22,
979 Prd23,
980 Prd24,
981 Prd25,
982 Prd26,
983 Prd27,
984 Prd28,
985 Prd29,
986 Prd30,
987 Prd31,
988 Prd32,
989 Prd33,
990 Prd34,
991 Prd35,
992 Prd36,
993 Prd37,
994 Prd38,
995 Prd39,
996 Prd40,
997 Prd41,
998 Prd42,
999 Prd43,
1000 Prd44,
1001 Prd45,
1002 Prd46,
1003 Prd47,
1004 Prd48,
1005 Prd49,
1006 Prd50,
1007 Prd51,
1008 Prd52)
1009 VALUES( l_session_id,
1010 l_budget_version_id,
1011 l_amount_type_code,
1012 ll_category_id,
1013 l_category_name||' Total',
1014 l_cat_tot_period1,
1015 l_cat_tot_period2,
1016 l_cat_tot_period3,
1017 l_cat_tot_period4,
1018 l_cat_tot_period5,
1019 l_cat_tot_period6,
1020 l_cat_tot_period7,
1021 l_cat_tot_period8,
1022 l_cat_tot_period9,
1023 l_cat_tot_period10,
1024 l_cat_tot_period11,
1025 l_cat_tot_period12,
1026 l_cat_tot_period13,
1027 l_cat_tot_period14,
1028 l_cat_tot_period15,
1029 l_cat_tot_period16,
1030 l_cat_tot_period17,
1031 l_cat_tot_period18,
1032 l_cat_tot_period19,
1033 l_cat_tot_period20,
1034 l_cat_tot_period21,
1035 l_cat_tot_period22,
1036 l_cat_tot_period23,
1037 l_cat_tot_period24,
1038 l_cat_tot_period25,
1039 l_cat_tot_period26,
1040 l_cat_tot_period27,
1041 l_cat_tot_period28,
1042 l_cat_tot_period29,
1043 l_cat_tot_period30,
1044 l_cat_tot_period31,
1045 l_cat_tot_period32,
1046 l_cat_tot_period33,
1047 l_cat_tot_period34,
1048 l_cat_tot_period35,
1049 l_cat_tot_period36,
1050 l_cat_tot_period37,
1051 l_cat_tot_period38,
1052 l_cat_tot_period39,
1053 l_cat_tot_period40,
1054 l_cat_tot_period41,
1055 l_cat_tot_period42,
1056 l_cat_tot_period43,
1057 l_cat_tot_period44,
1058 l_cat_tot_period45,
1059 l_cat_tot_period46,
1060 l_cat_tot_period47,
1061 l_cat_tot_period48,
1062 l_cat_tot_period49,
1063 l_cat_tot_period50,
1064 l_cat_tot_period51,
1065 l_cat_tot_period52);
1066
1067
1068 END LOOP;--for loop m
1069
1070 commit;
1071
1072 END populate_interface_table;
1073
1074 END pa_fp_of_webadi_pkg;