[Home] [Help]
PACKAGE BODY: APPS.JTFB_PICASSO_DEMO
Source
1 PACKAGE BODY jtfb_picasso_demo AS
2 /* $Header: jtfbdemb.pls 120.2 2005/10/25 05:25:58 psanyal ship $ */
3
4 /* $Header: jtfbdemb.pls 120.2 2005/10/25 05:25:58 psanyal ship $ */
5 --
6 --
7 /*****************************************************************************/
8 -- Start Of Comments
9 --
10 -- Name
11 -- jtfb_picasso_demo
12 --
13 -- Purpose
14 -- This package consists of procedures and functions to populate Demo data.
15 --
16 -- Private functions
17 -- None
18 --
19 -- Private Procedures
20 -- None
21 --
22 -- Notes
23 --
24 -- History
25 -- 15-MAY-2001, Pandian Athimoolam, Created the functions to return
26 -- the graph xaxis and yaxis label name
27 -- 09-MAY-2001, Pandian Athimoolam, Created.
28 --
29 -- end of Comments
30
31
32 /*****************************************************************************/
33 -- Start of Package Globals
34 --
35 -- end of Global Package Globals
36 --
37 --
38 /*****************************************************************************/
39 -- Start of Private Methods Specification
40 --
41 --
42 /*****************************************************************************/
43 -- Start Of Comments
44 --
45 -- Name : write_message
46 -- Type : Private
47 -- Function:
48 -- This procedure writes message (typically debug)
49 --
50 -- Pre-Reqs:
51 --
52 -- Parameters:
53 -- p_message_text
54 -- in varchar2
55 -- This is the descriptive part of the message.
56 --
57 -- Notes:
58 --
59 -- End Of Comments
60 procedure write_message(
61 p_message_text in varchar2
62 );
63 --
64 -- End of Private Methods Specification
65 --
66 --
67 procedure write_message(
68 p_message_text in varchar2
69 ) is
70 begin
71 null;
72 --dbms_output.put_line(p_message_text);
73 exception
74 when others then
75 null;
76 --dbms_output.put_line(p_message_text);
77 end write_message;
78 --
79 --
80 procedure load_jtfb_demo_bin(
81 p_context in varchar2 default null
82 ) is
83
84 l_method_name varchar2(80) := g_pkg_name || '.load_jtfb_demo_bin: ';
85 l_bin_code varchar2(20) := 'JTFB_DEMO_BIN';
86 l_rowid varchar2(256);
87 l_p_objects varchar2(50); -- the objects param value
88 l_object varchar2(50);
89 begin
90 IF p_context IS NOT NULL THEN
91 -- parse p_context and populate the relevant rows into the temp table
92 l_p_objects := jtfb_dcf.get_parameter_value(p_context,'JTFB_P_OBJECTS');
93 -- this is a multi select parameter which returs a value like 'CAMPAIGNS~~LEADS'
94
95 FOR i IN 1..jtfb_dcf.get_multiselect_count(l_p_objects) LOOP
96 l_object := jtfb_dcf.get_multiselect_value(l_p_objects,i);
97
98 IF l_object = 'CAMPAIGNS' THEN
99 INSERT INTO jtfb_temp_bin(bin_code, col1, col2, col4, col6) VALUES(l_bin_code, 'CAMPAIGNS', 'Campaigns', '67', '4.0');
100 END IF;
101
102 IF l_object = 'LEADS' THEN
103 INSERT INTO jtfb_temp_bin(bin_code, col1, col2, col4, col6) VALUES(l_bin_code, 'LEADS', 'Leads', '2,523', '-5.1');
104 END IF;
105
106 IF l_object = 'OPPORTUNITIES' THEN
107 INSERT INTO jtfb_temp_bin(bin_code, col1, col2, col4, col6) VALUES(l_bin_code, 'OPPORTUNITIES', 'Opportunities', '1522', '4.1');
108 END IF;
109
110 IF l_object = 'QUOTES' THEN
111 INSERT INTO jtfb_temp_bin(bin_code, col1, col2, col4, col6) VALUES(l_bin_code, 'QUOTES', 'Quotes', '954', '3.19');
112 END IF;
113
114 IF l_object = 'CUSTOMERS' THEN
115 INSERT INTO jtfb_temp_bin(bin_code, col1, col2, col4, col6) VALUES(l_bin_code, 'CUSTOMERS', 'Customers', '3,154', '6.5');
116 END IF;
117
118 END LOOP;
119
120 ELSE
121 jtfb_temp_bin_pkg.insert_row(
122 x_rowid => l_rowid
123 , x_bin_code => l_bin_code
124 , x_col1 => 'CAMPAIGNS'
125 , x_col2 => 'Campaigns'
126 , x_col4 => '67'
127 , x_col6 => '4.0'
128 );
129
130 jtfb_temp_bin_pkg.insert_row(
131 x_rowid => l_rowid
132 , x_bin_code => l_bin_code
133 , x_col1 => 'LEADS'
134 , x_col2 => 'Leads'
135 , x_col4 => '2523'
136 , x_col6 => '-5.1'
137 );
138
139 jtfb_temp_bin_pkg.insert_row(
140 x_rowid => l_rowid
141 , x_bin_code => l_bin_code
142 , x_col1 => 'OPPORTUNITIES'
143 , x_col2 => 'Opportunities'
144 , x_col4 => '1522'
145 , x_col6 => '4.1'
146 );
147
148 jtfb_temp_bin_pkg.insert_row(
149 x_rowid => l_rowid
150 , x_bin_code => l_bin_code
151 , x_col1 => 'QUOTES'
152 , x_col2 => 'Quotes'
153 , x_col4 => '954'
154 , x_col6 => '3.19'
155 );
156
157 jtfb_temp_bin_pkg.insert_row(
158 x_rowid => l_rowid
159 , x_bin_code => l_bin_code
160 , x_col1 => 'CUSTOMERS'
161 , x_col2 => 'Customers'
162 , x_col4 => '3154'
163 , x_col6 => '6.5'
164 );
165 END IF;
166 exception
167 when others then
168 write_message(l_method_name || sqlerrm);
169 end load_jtfb_demo_bin;
170 --
171 --
172 procedure load_jtfb_demo_bin1(
173 p_context in varchar2 default null
174 ) is
175
176 l_method_name varchar2(80) := g_pkg_name || '.load_jtfb_demo_bin1: ';
177 l_bin_code varchar2(20) := 'JTFB_DEMO_BIN1';
178
179 begin
180 insert into jtfb_temp_bin(bin_code, col1, col2, col4, col6)
181 values(l_bin_code, 'APR-01', 'APR-01', '6', '4.0');
182 insert into jtfb_temp_bin(bin_code, col1, col2, col4, col6)
183 values(l_bin_code, 'MAR-01', 'MAR-01', '4', '2.1');
184 insert into jtfb_temp_bin(bin_code, col1, col2, col4, col6)
185 values(l_bin_code, 'FEB-01', 'FEB-01', '2', '5.0');
186 insert into jtfb_temp_bin(bin_code, col1, col2, col4, col6)
187 values(l_bin_code, 'JAN-01', 'JAN-01', '12', '8.0');
188 insert into jtfb_temp_bin(bin_code, col1, col2, col4, col6)
189 values(l_bin_code, 'DEC-00', 'DEC-00', '0', '0.0');
190
191 exception
192 when others then
193 write_message(l_method_name || sqlerrm);
194 end load_jtfb_demo_bin1;
195 --
196 --
197 procedure load_jtfb_demo_report(
198 p_context in varchar2 default null
199 ) is
200
201 l_method_name varchar2(80) := g_pkg_name || '.load_jtfb_demo_report: ';
202 l_report_code varchar2(20) := 'JTFB_DEMO_REPORT';
203 l_rowid varchar2(256);
204 l_object varchar2(256);
205
206 begin
207
208 l_object := jtfb_dcf.get_parameter_value(p_context,'JTFB_P_OBJECTS');
209
210 if (l_object = 'CAMPAIGNS')
211 then
212 jtfb_temp_report_pkg.insert_row(
213 x_rowid => l_rowid
214 , x_report_code => l_report_code
215 , x_col1 => 'TOTAL'
216 , x_col2 => 'Total'
217 , x_col4 => '514'
218 , x_col6 => '648'
219 , x_col8 => '484'
220 , x_col10 => '500'
221 , x_col12 => '30.37'
222 );
223
224 jtfb_temp_report_pkg.insert_row(
225 x_rowid => l_rowid
226 , x_report_code => l_report_code
227 , x_col1 => '10'
228 , x_col2 => 'Think Customer'
229 , x_col4 => '200'
230 , x_col6 => '327'
231 , x_col8 => '168'
232 , x_col10 => '250'
233 , x_col12 => '35.97'
234 );
235
236 jtfb_temp_report_pkg.insert_row(
237 x_rowid => l_rowid
238 , x_report_code => l_report_code
239 , x_col1 => '20'
240 , x_col2 => 'CRM in 90 Days'
241 , x_col4 => '150'
242 , x_col6 => '260'
243 , x_col8 => '130'
244 , x_col10 => '150'
245 , x_col12 => '27.77'
246 );
247
248 jtfb_temp_report_pkg.insert_row(
249 x_rowid => l_rowid
250 , x_report_code => l_report_code
251 , x_col1 => '30'
252 , x_col2 => 'Interaction Center Push'
253 , x_col4 => '400'
254 , x_col6 => '345'
255 , x_col8 => '140'
256 , x_col10 => '365'
257 , x_col12 => '41.24'
258 );
259
260 elsif (l_object = 'LEADS')
261 then
262 insert into jtfb_temp_report(report_code, col1, col2
263 , col4, col6, col8, col10, col12)
264 values(l_report_code, 'TOTAL', 'Total'
265 , '466', '44','574','33','1117');
266 insert into jtfb_temp_report(report_code, col1, col2
267 , col4, col6, col8, col10, col12)
268 values(l_report_code, '10', 'France'
269 , '116','11','123','4','254');
270 insert into jtfb_temp_report(report_code, col1, col2
271 , col4, col6, col8, col10, col12)
272 values(l_report_code, '20', 'Canada'
273 , '120','10','151','6','287');
274 insert into jtfb_temp_report(report_code, col1, col2
275 , col4, col6, col8, col10, col12)
276 values(l_report_code, '30', 'UK'
277 , '90','8','140','8','246');
278 insert into jtfb_temp_report(report_code, col1, col2
279 , col4, col6, col8, col10, col12)
280 values(l_report_code, '40', 'US'
281 , '140','15','160','15','330');
282
283 elsif (l_object = 'OPPORTUNITIES')
284 then
285 insert into jtfb_temp_report(report_code, col1, col2
286 , col4, col6, col8, col10, col12)
287 values(l_report_code, 'TOTAL', 'Total', '20'
288 , '21','49','59','149');
289 insert into jtfb_temp_report(report_code, col1, col2
290 , col4, col6, col8, col10, col12)
291 values(l_report_code, '10', 'US Defense'
292 , '9','10','22','24','65');
293 insert into jtfb_temp_report(report_code, col1, col2
294 , col4, col6, col8, col10, col12)
295 values(l_report_code, '20', 'Drainage Canal'
296 , '5','3','12','14','34');
297 insert into jtfb_temp_report(report_code, col1, col2
298 , col4, col6, col8, col10, col12)
299 values(l_report_code, '30', 'Water and Sanitary'
300 , '6','8','15','21','50');
301
302 elsif (l_object = 'QUOTES')
303 then
304 insert into jtfb_temp_report(report_code, col1, col2
305 , col4, col6, col8, col10, col12)
306 values(l_report_code, 'TOTAL', 'Total'
307 , '42', '72','107','121','343');
308 insert into jtfb_temp_report(report_code, col1, col2
309 , col4, col6, col8, col10, col12)
310 values(l_report_code, '10', 'Oracle Australia'
311 , '15','20','40','43','118');
312 insert into jtfb_temp_report(report_code, col1, col2
313 , col4, col6, col8, col10, col12)
314 values(l_report_code, '20', 'Oracle Argentina'
315 , '5','10','12','16','43');
316 insert into jtfb_temp_report(report_code, col1, col2
317 , col4, col6, col8, col10, col12)
318 values(l_report_code, '30', 'Oracle Canada'
319 , '22','42','55','62','182');
320
321 elsif (l_object = 'CUSTOMERS')
322 then
323 insert into jtfb_temp_report(report_code, col1, col2
324 , col4, col6, col8, col10, col12)
325 values(l_report_code, 'TOTAL', 'Total'
326 , '448', '520','200','363','30.37');
327 insert into jtfb_temp_report(report_code, col1, col2
328 , col4, col6, col8, col10, col12)
329 values(l_report_code, '10', 'Compaq'
330 ,'50', '60','110','150','370');
331 insert into jtfb_temp_report(report_code, col1, col2
332 , col4, col6, col8, col10, col12)
333 values(l_report_code, '20', 'Xerox'
334 ,'70', '50','130','150','400');
335 insert into jtfb_temp_report(report_code, col1, col2
336 , col4, col6, col8, col10, col12)
337 values(l_report_code, '30', 'GE Medical'
338 ,'45', '80','140','240','505');
339 insert into jtfb_temp_report(report_code, col1, col2
340 , col4, col6, col8, col10, col12)
341 values(l_report_code, '30', 'HP'
342 ,'45', '80','140','140','405');
343 insert into jtfb_temp_report(report_code, col1, col2
344 , col4, col6, col8, col10, col12)
345 values(l_report_code, '30', 'Papa Jones'
346 ,'70', '80','140','280','570');
347
348 else
349 insert into jtfb_temp_report(report_code, col1, col2
350 , col4, col6, col8, col10, col12)
351 values(l_report_code, 'TOTAL', 'Total'
352 , '514', '648','484','500','30.37');
353 insert into jtfb_temp_report(report_code, col1, col2
354 , col4, col6, col8, col10, col12)
355 values(l_report_code, '10', 'Think Customer'
356 , '200','327','168','250','35.97');
357 insert into jtfb_temp_report(report_code, col1, col2
358 , col4, col6, col8, col10, col12)
359 values(l_report_code, '20', 'CRM in 90 Days'
360 , '150','260','130','150','27.77');
361 insert into jtfb_temp_report(report_code, col1, col2
362 , col4, col6, col8, col10, col12)
363 values(l_report_code, '30', 'Interaction Center Push'
364 , '400','345','140','365','41.24');
365 end if;
366
367 exception
368 when others then
369 write_message(l_method_name || sqlerrm);
370 end load_jtfb_demo_report;
371 --
372 --
373 function get_dynamic_footer(
374 p_context in varchar2 default null
375 ) return varchar2 is
376
377 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_footer: ';
378 l_footer varchar2(100) := 'Amount is scaled as: in Hundred Thousands';
379
380 begin
381
382 return l_footer;
383
384 exception
385 when others then
386 write_message(l_method_name || sqlerrm);
387 end get_dynamic_footer;
388 --
389 --
390 function get_dynamic_name(
391 p_context in varchar2 default null
392 ) return varchar2 is
393
394 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_name: ';
395 l_report_name varchar2(100);
396
397 begin
398 if (p_context = 'CAMPAIGNS')
399 then
400 l_report_name := 'Campaigns Lead Quality';
401
402 elsif (p_context = 'LEADS')
403 then
404 l_report_name := 'Lead Activity Analysis';
405
406 elsif (p_context = 'OPPORTUNITIES')
407 then
408 l_report_name := 'Opportunity Analysis';
409
410 elsif (p_context = 'QUOTES')
411 then
412 l_report_name := 'Quote Analysis';
413
414 elsif (p_context = 'CUSTOMERS')
415 then
416 l_report_name := 'Customer Analysis';
417
418 elsif (p_context is null)
419 then
420 l_report_name := 'Activity Analysis';
421 end if;
422
423 return l_report_name;
424
425 exception
426 when others then
427 write_message(l_method_name || sqlerrm);
428 end get_dynamic_name;
429 --
430 --
434
431 function get_dynamic_report_col2(
432 p_context in varchar2 default null
433 ) return varchar2 is
435 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col2: ';
436 l_col_name varchar2(100);
437
438 begin
439 if (p_context = 'CAMPAIGNS')
440 then
441 l_col_name := 'Campaigns';
442
443 elsif (p_context = 'LEADS')
444 then
445 l_col_name := 'Country';
446
447 elsif (p_context = 'OPPORTUNITIES')
448 then
449 l_col_name := 'Organization Name';
450
451 elsif (p_context = 'QUOTES')
452 then
453 l_col_name := 'Country Name';
454
455 elsif (p_context = 'CUSTOMERS')
456 then
457 l_col_name := 'Customer Name';
458
459 elsif (p_context is null)
460 then
461 l_col_name := 'Col Header2';
462 end if;
463
464 return l_col_name;
465
466 exception
467 when others then
468 write_message(l_method_name || sqlerrm);
469 end get_dynamic_report_col2;
470 --
471 --
472 function get_dynamic_report_col4(
473 p_context in varchar2 default null
474 ) return varchar2 is
475
476 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col4: ';
477 l_col_name varchar2(100);
478
479 begin
480 if (p_context = 'CAMPAIGNS')
481 then
482 l_col_name := 'Hot Leads';
483
484 elsif (p_context = 'LEADS')
485 then
486 l_col_name := 'Prior Week';
487
488 elsif (p_context = 'OPPORTUNITIES')
489 then
490 l_col_name := '01-05-2001';
491
492 elsif (p_context = 'QUOTES')
493 then
494 l_col_name := '01-05-2001';
495
496 elsif (p_context = 'CUSTOMERS')
497 then
498 l_col_name := 'Prior Week';
499
500 elsif (p_context is null)
501 then
502 l_col_name := 'Col Header4';
503 end if;
504
505 return l_col_name;
506
507 exception
508 when others then
509 write_message(l_method_name || sqlerrm);
510 end get_dynamic_report_col4;
511 --
512 --
513 function get_dynamic_report_col6(
514 p_context in varchar2 default null
515 ) return varchar2 is
516
517 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col6: ';
518 l_col_name varchar2(100);
519
520 begin
521 if (p_context = 'CAMPAIGNS')
522 then
523 l_col_name := 'Medium Leads';
524
525 elsif (p_context = 'LEADS')
526 then
527 l_col_name := 'Current Week';
528
529 elsif (p_context = 'OPPORTUNITIES') then
530 l_col_name := '02-05-2001';
531
532 elsif (p_context = 'QUOTES')
533 then
534 l_col_name := '02-05-2001';
535
536 elsif (p_context = 'CUSTOMERS')
537 then
538 l_col_name := 'Current Week';
539
540 elsif (p_context is null)
541 then
542 l_col_name := 'Col Header6';
543 end if;
544
545 return l_col_name;
546
547 exception
548 when others then
549 write_message(l_method_name || sqlerrm);
550 end get_dynamic_report_col6;
551 --
552 --
553 function get_dynamic_report_col8(
554 p_context in varchar2 default null
555 ) return varchar2 is
556
557 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col8: ';
558 l_col_name varchar2(100);
559
560 begin
561 if (p_context = 'CAMPAIGNS')
562 then
563 l_col_name := 'Cold Leads';
564
565 elsif (p_context = 'LEADS')
566 then
567 l_col_name := 'Prior Month';
568
569 elsif (p_context = 'OPPORTUNITIES')
570 then
571 l_col_name := '03-05-2001';
572
573 elsif (p_context = 'QUOTES')
574 then
575 l_col_name := '03-05-2001';
576
577 elsif (p_context = 'CUSTOMERS')
578 then
579 l_col_name := 'Prior Month';
580
581 elsif (p_context is null)
582 then
583 l_col_name := 'Col Header8';
584 end if;
585
586 return l_col_name;
587
588 exception
589 when others then
590 write_message(l_method_name || sqlerrm);
591 end get_dynamic_report_col8;
592 --
593 --
594 function get_dynamic_report_col10(
595 p_context in varchar2 default null
596 ) return varchar2 is
597
598 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col10: ';
599 l_col_name varchar2(100);
600
601 begin
602 if (p_context = 'CAMPAIGNS')
603 then
604 l_col_name := 'Unranked Leads';
605
606 elsif (p_context = 'LEADS')
607 then
608 l_col_name := 'Current Month';
609
610 elsif (p_context = 'OPPORTUNITIES')
611 then
612 l_col_name := '04-05-2001';
613
614 elsif (p_context = 'QUOTES')
615 then
616 l_col_name := '04-05-2001';
617
618 elsif (p_context = 'CUSTOMERS')
622 elsif (p_context is null)
619 then
620 l_col_name := 'Current Month';
621
623 then
624 l_col_name := 'Col Header10';
625 end if;
626
627 return l_col_name;
628
629 exception
630 when others then
631 write_message(l_method_name || sqlerrm);
632 end get_dynamic_report_col10;
633 --
634 --
635 function get_dynamic_report_col12(
636 p_context in varchar2 default null
637 ) return varchar2 is
638
639 l_method_name varchar2(80) := g_pkg_name || '.get_dynamic_report_col12: ';
640 l_col_name varchar2(100);
641
642 begin
643 if (p_context = 'CAMPAIGNS')
644 then
645 l_col_name := '% Ranked';
646
647 elsif (p_context = 'LEADS')
648 then
649 l_col_name := 'Total';
650
651 elsif (p_context = 'OPPORTUNITIES')
652 then
653 l_col_name := 'Total';
654
655 elsif (p_context = 'QUOTES')
656 then
657 l_col_name := 'Total';
658
659 elsif (p_context = 'CUSTOMERS')
660 then
661 l_col_name := 'Total';
662
663 elsif (p_context is null)
664 then
665 l_col_name := 'Col Header12';
666 end if;
667
668 return l_col_name;
669
670 exception
671 when others then
672 write_message(l_method_name || sqlerrm);
673 end get_dynamic_report_col12;
674 --
675 --
676 function get_xaxis_label_name(
677 p_context in varchar2 default null
678 ) return varchar2 is
679
680 l_method_name varchar2(80) := g_pkg_name || '.get_xaxis_label_name: ';
681 l_col_name varchar2(100);
682
683 begin
684 if (p_context = 'CAMPAIGNS')
685 then
686 l_col_name := 'Campaigns';
687
688 elsif (p_context = 'LEADS')
689 then
690 l_col_name := 'Countries';
691
692 elsif (p_context = 'OPPORTUNITIES')
693 then
694 l_col_name := 'Organizations';
695
696 elsif (p_context = 'QUOTES')
697 then
698 l_col_name := 'Countries';
699
700 elsif (p_context = 'CUSTOMERS')
701 then
702 l_col_name := 'Customers';
703
704 elsif (p_context is null)
705 then
706 l_col_name := ' ';
707 end if;
708
709 return l_col_name;
710
711 exception
712 when others then
713 write_message(l_method_name || sqlerrm);
714 end get_xaxis_label_name;
715 --
716 --
717 function get_yaxis_label_name(
718 p_context in varchar2 default null
719 ) return varchar2 is
720
721 l_method_name varchar2(80) := g_pkg_name || '.get_yaxis_label_name: ';
722 l_col_name varchar2(100);
723
724 begin
725 if (p_context = 'CAMPAIGNS')
726 then
727 l_col_name := 'Leads';
728
729 elsif (p_context = 'LEADS')
730 then
731 l_col_name := 'Leads';
732
733 elsif (p_context = 'OPPORTUNITIES')
734 then
735 l_col_name := 'Opportunities';
736
737 elsif (p_context = 'QUOTES')
738 then
739 l_col_name := 'Quotes';
740
741 elsif (p_context = 'CUSTOMERS')
742 then
743 l_col_name := 'Leads';
744
745 elsif (p_context is null)
746 then
747 l_col_name := ' ';
748 end if;
749
750 return l_col_name;
751
752 exception
753 when others then
754 write_message(l_method_name || sqlerrm);
755 end get_yaxis_label_name;
756 --
757 --
758 end jtfb_picasso_demo;
759