[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_DBI_UTL_PKG
Source
1 PACKAGE BODY BIX_PMV_DBI_UTL_PKG AS
2 /*$Header: bixdutlb.plb 120.1 2006/03/28 22:48:36 pubalasu noship $ */
3
4 g_dnis VARCHAR2(3000);
5 g_agent_group VARCHAR2(3000);
6
7 FUNCTION get_table (
8 dim_name VARCHAR2
9 , p_version IN VARCHAR2
10 ) RETURN VARCHAR2;
11
12 FUNCTION get_col_name (
13 dim_name IN VARCHAR2
14 , mv_set IN VARCHAR2
15 , p_version IN VARCHAR2
16 ) RETURN VARCHAR2;
17
18 PROCEDURE get_join_info (
19 p_view_by IN VARCHAR2
20 , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
21 , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
22 , p_func_area IN VARCHAR2
23 , p_version IN VARCHAR2);
24
25 PROCEDURE init_dim_map (
26 p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
27 , p_mv_set IN VARCHAR2
28 , p_version IN VARCHAR2/*keep this for extensibility */);
29
30
31 FUNCTION get_viewby_select_clause (
32 p_view_by IN VARCHAR2
33 , p_func_area IN VARCHAR2
34 , p_version IN VARCHAR2)
35 RETURN VARCHAR2 ;
36
37 FUNCTION get_row_type_where_clauses(
38 p_func_area IN VARCHAR2
39 , p_version IN VARCHAR2
40 , p_mv_set IN VARCHAR2
41 ) RETURN VARCHAR2 ;
42
43 FUNCTION get_mv (
44 p_mv_set IN VARCHAR2
45 , p_version IN VARCHAR2 /*Retained for extensibility*/) RETURN VARCHAR2;
46
47 FUNCTION get_dnis_where_clause(p_page_parameter_tbl IN bis_pmv_page_parameter_tbl) RETURN VARCHAR2;
48
49 FUNCTION get_agent_group_where_clause(p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
50 p_mv_set IN VARCHAR2
51 ) RETURN VARCHAR2;
52 FUNCTION get_outcome_filter_clause RETURN VARCHAR2;
53
54 PROCEDURE process_parameters (
55 p_param IN bis_pmv_page_parameter_tbl
56 , p_trend IN VARCHAR2
57 , p_func_area IN VARCHAR2
58 , p_version IN VARCHAR2
59 , p_mv_set IN VARCHAR2 --ITM/OTM/ECM
60 , p_where_clause OUT NOCOPY VARCHAR2
61 , p_mv OUT NOCOPY VARCHAR2
62 , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
63 , p_comp_type OUT NOCOPY VARCHAR2 --001
64 , p_xtd OUT NOCOPY VARCHAR2 --001
65 , p_view_by_select OUT NOCOPY VARCHAR2
66 , p_view_by OUT NOCOPY VARCHAR2
67 )
68 IS
69
70
71 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
72
73 -- As of now, these are not passed as out params
74 l_dim_bmap NUMBER;
75 p_comparison_type VARCHAR2(1);
76 p_as_of_date DATE;
77 p_prev_as_of_date DATE;
78 p_cur_suffix VARCHAR2 (2);
79 p_nested_pattern NUMBER;
80
81
82 BEGIN
83
84
85 l_dim_bmap := 0;
86
87 /* --
88 poa's procedure to retrieve the parameter values requires as input
89 amongst others, a table of dimension details.Init_dim_map initializes the same
90 -- */
91
92 --
93 --If mv set = SES then not all dimensions would be used to filter
94 --for example, classification and dnis would not be used to filter out the rows
95 --this is taken care of in init_dim_map
96 --
97 init_dim_map (l_dim_map, p_mv_set,p_version);
98 p_mv := get_mv (p_mv_set,p_version);
99
100
101 poa_dbi_util_pkg.get_parameter_values (
102 p_param
103 , l_dim_map
104 , p_view_by
105 , p_comparison_type
106 , p_xtd
107 , p_as_of_date
108 , p_prev_as_of_date
109 , p_cur_suffix
110 , p_nested_pattern
111 , l_dim_bmap);
112
113 p_comp_type := p_comparison_type;
114
115 /*
116 insert into bix_debug values
117 ( 'p_view_by:' || p_view_by ||
118 'p_comparison_type:' || p_comparison_type ||
119 'p_xtd:' || p_xtd ||
120 'p_as_of_date:' || p_as_of_date ||
121 'p_prev_as_of_date:' || p_prev_as_of_date ||
122 'p_nested_pattern:' || p_nested_pattern
123 );
124 */
125
126 /* Get the filter where clause [value of the dimensions selected ] and
127 concatenate it with the where clause with row_type*/
128
129 IF p_mv_set='SES' THEN
130 p_where_clause :=poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
131 ||get_agent_group_where_clause(p_param,p_mv_set)||' and application_id=696';
132 ELSIF p_func_area = 'IORRR' THEN
133 p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
134 ||get_dnis_where_clause(p_param)
135 ||get_agent_group_where_clause(p_param,p_mv_set)
136 ||get_row_type_where_clauses( p_func_area, p_version, p_mv_set)
137 ||get_outcome_filter_clause();
138 ELSE
139 p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
140 ||get_dnis_where_clause(p_param)
141 ||get_agent_group_where_clause(p_param,p_mv_set)
142 ||get_row_type_where_clauses( p_func_area, p_version, p_mv_set);
143 END IF;
144 IF p_trend <> 'Y' THEN /* For Trend portlets, get join info and view by select is not necessary */
145 get_join_info (p_view_by
146 , l_dim_map
147 , p_join_tbl
148 , p_func_area
149 , p_version);
150 p_view_by_select := get_viewby_select_clause(p_view_by,p_func_area,p_version);
151 END IF;
152
153
154 EXCEPTION WHEN OTHERS THEN
155 RAISE;
156 END process_parameters;
157
158 FUNCTION get_orr_views RETURN VARCHAR2
159 IS
160 BEGIN
161 /* pubalasu: p_insetclause can be any where condition to insert
162 before the order by clause
163 */
164 RETURN ' jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
165 WHERE oset.outcome_id = outcome.outcome_id(+)
166 AND oset.result_id = result.result_id(+)
167 AND oset.reason_id = reason.reason_id(+)
168 order by outcome.outcome_code,result.result_code,reason.reason_code
169 ' ;
170 END get_orr_views;
171
172 /* -----------------------------------------------------------------------------
173 get_row_type_where_clauses: Where clauses for row_type
174 ----------------------------------------------------------------------------- */
175 FUNCTION get_row_type_where_clauses (
176 p_func_area IN VARCHAR2
177 , p_version IN VARCHAR2
178 , p_mv_set IN VARCHAR2
179 ) RETURN VARCHAR2
180 IS
181 l_sec_where_clause VARCHAR2 (1000) := '';
182 BEGIN
183
184 l_sec_where_clause := 'and row_type=';
185 l_sec_where_clause := l_sec_where_clause ||(CASE p_mv_set
186 WHEN 'ECM'
187 THEN ''
188 WHEN 'ITM'
189 THEN
190 ( CASE p_func_area
191 WHEN 'ICSTR' THEN '''CDPR''' /* Telephony Activity by Customer Report */
192 WHEN 'ITATR' THEN '''CDPR''' /* Telephony Activity Report */
193 WHEN 'ICMTP' THEN '''CDR''' /* Calls by media type graph */
194 WHEN 'ITMAT' THEN '''CDR''' /* Abandon Rate graph */
195 WHEN 'IOUTP' THEN '''CDR''' /* Outcomes graph */
196 WHEN 'IAGTR' THEN '''CDR''' /* Inbound Telephony by agent report */
197 WHEN 'IORRR' THEN '''CORR''' /* Inbound Telephony by agent report */
198 ELSE '''C'''
199 END
200 )
201 WHEN 'SES' THEN '''CDR''' /* Inbound Telephony by agent report */
202 WHEN 'OTM'
203 THEN ''
204 ELSE ''
205 END
206 );
207
208 RETURN l_sec_where_clause;
209 END get_row_type_where_clauses;
210 /* -----------------------------------------------------------------------------
211 get_dnis_where_clause:Determines if DNIS is selected and creates a where clause
212 accordingly
213 ----------------------------------------------------------------------------- */
214 FUNCTION get_dnis_where_clause(p_page_parameter_tbl IN bis_pmv_page_parameter_tbl) RETURN VARCHAR2
215 IS
216 l_dnis varchar2(3000);
217 l_where_clause varchar2(1000);
218 BEGIN
219
220
221 IF (p_page_parameter_tbl.count > 0) THEN
222 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
223 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_DNIS'
224 THEN
225 l_dnis := p_page_parameter_tbl(i).parameter_id;
226 END IF;
227 END LOOP;
228 END IF;
229
230 IF l_dnis IS NOT NULL THEN
231 IF l_dnis = '''INBOUND''' THEN
232 l_where_clause :=' AND fact.dnis_name <> ''OUTBOUND'' ';
233 ELSIF l_dnis = '''OUTBOUND''' THEN
234 l_where_clause := ' AND fact.dnis_name = ''OUTBOUND'' ';
235 ELSE
236 l_where_clause := ' AND fact.dnis_name IN (to_char(:l_dnis)) ';
237 END IF;
238 END IF;
239 g_dnis:=l_dnis;
240 RETURN l_where_clause;
241 END get_dnis_where_clause;
242
243 /* -----------------------------------------------------------------------------
244 get_agent_group_where_clause:Determines if agent group is selected and creates a where clause
245 accordingly
246 ----------------------------------------------------------------------------- */
247 FUNCTION get_agent_group_where_clause(
248 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
249 p_mv_set IN varchar2
250 ) RETURN VARCHAR2
251 IS
252 l_agent_group varchar2(3000);
253 l_where_clause varchar2(1000);
254 BEGIN
255
256
257 IF (p_page_parameter_tbl.count > 0) THEN
258 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
259 IF p_page_parameter_tbl(i).parameter_name= 'ORGANIZATION+JTF_ORG_SUPPORT_GROUP'
260 THEN
261 l_agent_group := p_page_parameter_tbl(i).parameter_id;
262 END IF;
263 END LOOP;
264 END IF;
265
266 IF l_agent_group IS NOT NULL THEN
267 IF p_mv_set = 'SES'
268 THEN
269 l_where_clause := ' AND EXISTS (
270 SELECT 1
271 FROM jtf_rs_group_members mem
272 WHERE fact.agent_id = mem.resource_id
273 AND mem.group_id IN (:l_agent_group)
274 AND nvl(mem.delete_flag, ''N'') <> ''Y''
275 )';
276
277 ELSE
278 l_where_clause := ' AND EXISTS (
279 SELECT 1
280 FROM jtf_rs_group_members mem
281 WHERE fact.resource_id = mem.resource_id
282 AND mem.group_id IN (:l_agent_group)
283 AND nvl(mem.delete_flag, ''N'') <> ''Y''
284 )';
285 END IF;
286
287 END IF;
288
289 g_agent_group := l_agent_group;
290
291 RETURN l_where_clause;
292
293 END get_agent_group_where_clause;
294
295 /* -----------------------------------------------------------------------------
296 get_outcome_filter_clause:Simple function to return the filter clause for ORR reports
297 ----------------------------------------------------------------------------- */
298
299 FUNCTION get_outcome_filter_clause RETURN VARCHAR2
300 IS
301 BEGIN
302 RETURN 'and fact.outcome_id <> :l_outcome_filter';
303 END get_outcome_filter_clause;
304
305
306
307 /* -----------------------------------------------------------------------------
308 get_mv:Simple function to return the mv name based on the mv_set name
309 ----------------------------------------------------------------------------- */
310 FUNCTION get_mv (
311 p_mv_set IN VARCHAR2
312 , p_version IN VARCHAR2 /*Retained for extensibility*/)
313 RETURN VARCHAR2
314 IS
315 l_mv_name varchar2(100);
316 BEGIN
317
318 l_mv_name:=
319 (CASE p_mv_set
320 WHEN 'ECM'
321 THEN 'BIX_EMAIL_DETAILS_MV'
322 WHEN 'ITM'
323 THEN 'BIX_AI_CALL_DETAILS_MV'
324 WHEN 'OTM'
325 THEN 'BIX_AO_CALL_DETAILS_MV'
326 WHEN 'SES'
327 THEN 'BIX_AGENT_SESSION_F'
328 ELSE ''
329 END
330 );
331 return l_mv_name;
332 EXCEPTION
333 WHEN OTHERS THEN
334 RAISE;
335 END get_mv;
336
337
338 /* -----------------------------------------------------------------------------
339 init_dim_map: Initialize the dimension mapping for BIX.
340 The dimensions are populated depending on the mv set (ie) ECM,ITM,OTM
341 ----------------------------------------------------------------------------- */
342 PROCEDURE init_dim_map (
343 p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
344 , p_mv_set IN VARCHAR2
345 , p_version IN VARCHAR2/*keep this for extensibility */)
346 IS
347 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
348 BEGIN
349 IF P_MV_SET='ECM' THEN
350 -- Email Account Dimension
351
352 l_dim_rec.generate_where_clause := 'Y';
353 l_dim_rec.col_name := get_col_name (dim_name => g_email_accnt_dim
354 , mv_set => p_mv_set
355 , p_version => p_version);
356 l_dim_rec.view_by_table := get_table (dim_name => g_email_accnt_dim
357 , p_version => p_version);
358 l_dim_rec.bmap := 0;
359 p_dim_map (g_email_accnt_dim) := l_dim_rec;
360
361
362 -- Email Classification Dimension
363
364 l_dim_rec.generate_where_clause := 'Y';
365 l_dim_rec.col_name := get_col_name (dim_name => g_email_class_dim
366 , mv_set => p_mv_set
367 , p_version => p_version);
368 l_dim_rec.view_by_table := get_table (dim_name => g_email_class_dim
369 , p_version => p_version);
370 l_dim_rec.bmap := 0;
371 p_dim_map (g_email_class_dim) := l_dim_rec;
372 ELSIF P_MV_SET='ITM' THEN
373
374 -- Call Center Dimension
375
376 l_dim_rec.generate_where_clause := 'Y';
377 l_dim_rec.col_name := get_col_name (dim_name => g_ai_ccntr_dim
378 , mv_set => p_mv_set
379 , p_version => p_version);
380 l_dim_rec.view_by_table := get_table (dim_name => g_ai_ccntr_dim
381 , p_version => p_version);
382 l_dim_rec.bmap := 0;
383 p_dim_map (g_ai_ccntr_dim) := l_dim_rec;
384
385
386 -- Call Classification Dimension
387
388 l_dim_rec.generate_where_clause := 'Y';
389 l_dim_rec.col_name := get_col_name (dim_name => g_ai_class_dim
390 , mv_set => p_mv_set
391 , p_version => p_version);
392 l_dim_rec.view_by_table := get_table (dim_name => g_ai_class_dim
393 , p_version => p_version);
394 l_dim_rec.bmap := 0;
395 p_dim_map (g_ai_class_dim) := l_dim_rec;
396
397 -- DNIS Dimension
398 /********************
399 In DNIS Dimension ,when 'All Customer Dialed' is chosen from the dropdown, it
400 translates to dnis_name <> 'OUTBOUND' in the where clause. So, we dont
401 generate the where clause using the POA util package
402 ********************/
403
404 l_dim_rec.generate_where_clause := 'N';
405 l_dim_rec.col_name := get_col_name (dim_name => g_ai_dnis_dim
406 , mv_set => p_mv_set
407 , p_version => p_version);
408 l_dim_rec.view_by_table := get_table (dim_name => g_ai_dnis_dim
409 , p_version => p_version);
410 l_dim_rec.bmap := 0;
411 p_dim_map (g_ai_dnis_dim) := l_dim_rec;
412
413 -- Agent Group Dimension
414 /********************
415 For Agent Group dimension we need a special where clause with a EXISTS clause.
416 So, we do not generate the where clause using the POA util package
417 ********************/
418
419 l_dim_rec.generate_where_clause := 'N';
420 l_dim_rec.col_name := get_col_name (dim_name => g_agent_group_dim
421 , mv_set => p_mv_set
422 , p_version => p_version);
423 l_dim_rec.view_by_table := get_table (dim_name => g_agent_group_dim
424 , p_version => p_version);
425 l_dim_rec.bmap := 0;
426 p_dim_map (g_agent_group_dim) := l_dim_rec;
427
428 -- Customer Pseudo Dimension
429
430 l_dim_rec.generate_where_clause := 'N';
431 l_dim_rec.col_name := get_col_name (dim_name => g_ai_custm_dim
432 , mv_set => p_mv_set
433 , p_version => p_version);
434 l_dim_rec.view_by_table := get_table (dim_name => g_ai_custm_dim
435 , p_version => p_version);
436 l_dim_rec.bmap := 0;
437 p_dim_map (g_ai_custm_dim) := l_dim_rec;
438
439 ELSIF P_MV_SET='SES' THEN
440
441
442 -- Call Center Dimension
443
444 l_dim_rec.generate_where_clause := 'Y';
445 l_dim_rec.col_name := get_col_name (dim_name => g_ai_ccntr_dim
446 , mv_set => p_mv_set
447 , p_version => p_version);
448 l_dim_rec.view_by_table := get_table (dim_name => g_ai_ccntr_dim
449 , p_version => p_version);
450 l_dim_rec.bmap := 0;
451 p_dim_map (g_ai_ccntr_dim) := l_dim_rec;
452
453
454 -- Agent Group Dimension
455 /********************
456 For Agent Group dimension we need a special where clause with a EXISTS clause.
457 So, we do not generate the where clause using the POA util package
458 ********************/
459
460 l_dim_rec.generate_where_clause := 'N';
461 l_dim_rec.col_name := get_col_name (dim_name => g_agent_group_dim
462 , mv_set => p_mv_set
463 , p_version => p_version);
464 l_dim_rec.view_by_table := get_table (dim_name => g_agent_group_dim
465 , p_version => p_version);
466 l_dim_rec.bmap := 0;
467 p_dim_map (g_agent_group_dim) := l_dim_rec;
468
469 -- Call Classification Dimension
470
471 l_dim_rec.generate_where_clause := 'N';
472 l_dim_rec.col_name := get_col_name (dim_name => g_ai_class_dim
473 , mv_set => p_mv_set
474 , p_version => p_version);
475 l_dim_rec.view_by_table := get_table (dim_name => g_ai_class_dim
476 , p_version => p_version);
477 l_dim_rec.bmap := 0;
478 p_dim_map (g_ai_class_dim) := l_dim_rec;
479
480 -- DNIS Dimension
481 /********************
482 In DNIS Dimension ,when 'All Customer Dialed' is chosen from the dropdown, it
483 translates to dnis_name <> 'OUTBOUND' in the where clause. So, we dont
484 generate the where clause using the POA util package
485 ********************/
486
487 l_dim_rec.generate_where_clause := 'N';
488 l_dim_rec.col_name := get_col_name (dim_name => g_ai_dnis_dim
489 , mv_set => p_mv_set
490 , p_version => p_version);
491 l_dim_rec.view_by_table := get_table (dim_name => g_ai_dnis_dim
492 , p_version => p_version);
493 l_dim_rec.bmap := 0;
494 p_dim_map (g_ai_dnis_dim) := l_dim_rec;
495
496 END IF;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 RAISE;
501
502 END init_dim_map;
503
504 /* -----------------------------------------------------------------------------
505 get_col_name: Returns the column name in the MV that is associated with the
506 dimension.
507 ----------------------------------------------------------------------------- */
508 FUNCTION get_col_name (
509 dim_name IN VARCHAR2
510 , mv_set IN VARCHAR2
511 , p_version IN VARCHAR2
512 )
513 RETURN VARCHAR2
514 IS
515 l_col_name VARCHAR2 (100);
516 BEGIN
517 l_col_name :=
518 (CASE dim_name
519 WHEN g_email_accnt_dim
520 THEN 'email_account_id'
521 WHEN g_email_class_dim
522 THEN 'email_classification_id'
523 WHEN g_ai_ccntr_dim
524 THEN 'server_group_id'
525 WHEN g_ai_class_dim
526 THEN 'classification_value'
527 WHEN g_ai_dnis_dim
528 THEN 'dnis_name'
529 WHEN g_agent_group_dim
530 THEN
531 (CASE mv_set
532 WHEN 'SES'
533 THEN 'agent_id'
534 ELSE 'resource_id'
535 END)
536 WHEN g_ai_custm_dim
537 THEN 'party_id'
538 ELSE ''
539 END);
540
541 RETURN l_col_name;
542 END;
543
544 /* -----------------------------------------------------------------------------
545 get_table : Returns the name of the object to join to to which the MV is joined to
546 ----------------------------------------------------------------------------- */
547 FUNCTION get_table (
548 dim_name VARCHAR2
549 , p_version IN VARCHAR2)
550 RETURN VARCHAR2
551 IS
552 l_table VARCHAR2 (4000);
553 BEGIN
554 l_table :=
555 (
556 CASE dim_name
557 WHEN g_email_accnt_dim
558 THEN 'BIX_EMAIL_ACCOUNTS_V'
559 WHEN g_email_class_dim
560 THEN 'BIX_EMAIL_CLASSIFICATIONS_V'
561 WHEN g_ai_ccntr_dim
562 THEN 'IEO_SVR_GROUPS'
563 WHEN g_ai_custm_dim
564 THEN 'HZ_PARTIES'
565 WHEN g_agent_group_dim
566 THEN 'JTF_RS_RESOURCE_EXTNS_VL'
567 ELSE
568 '(Select dummy from dual)'
569 END
570 );
571
572 RETURN l_table;
573 END get_table;
574
575 /* -----------------------------------------------------------------------------
576 get_join_info : Returns the join clause to join the view by table with dimension views
577 ----------------------------------------------------------------------------- */
578
579 PROCEDURE get_join_info (
580 p_view_by IN VARCHAR2
581 , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
582 , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
583 , p_func_area IN VARCHAR2
584 , p_version IN VARCHAR2)
585 IS
586 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
587 l_view_by VARCHAR2(120);
588 BEGIN
589
590
591 x_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
592 /*
593 For some of ICI reports, there is no view by clause, but we do join to some other tables like hz_parties
594 to get customer names.
595 */
596
597 IF (p_func_area='ICSTR')
598 THEN
599 l_view_by:=g_ai_custm_dim;
600 l_join_rec.column_name := 'party_id(+) group by nvl(party_name,:l_unknown)';
601 ELSIF (p_func_area='ITATR') AND p_view_by=g_ai_ccntr_dim
602 THEN
603 l_view_by:=p_view_by;
604 l_join_rec.column_name := 'server_group_id group by v.group_name,v.server_group_id';
605 ELSIF (p_func_area='IAGTR') --agent activity report
606 THEN
607 l_view_by := g_agent_group_dim;
608 l_join_rec.column_name := 'resource_id group by resource_name ';
609 ELSE
610 l_view_by:=p_view_by;
611 l_join_rec.column_name := 'id';
612 END IF;
613
614 /* For ORR report, we cannot default view by dimension coz it is joining to 3 tables */
615
616 IF l_view_by IN (g_ai_custm_dim,g_ai_ccntr_dim,g_ai_class_dim,g_ai_dnis_dim,g_agent_group_dim)
617 AND (p_func_area<>'IORRR')
618 THEN
619
620 l_join_rec.table_name := p_dim_map (l_view_by).view_by_table;
621 l_join_rec.table_alias := 'v';
622 l_join_rec.fact_column := p_dim_map (l_view_by).col_name;
623
624 x_join_tbl.EXTEND;
625 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
626 ELSIF p_func_area='IORRR' THEN
627 l_join_rec.table_name := '';
628 l_join_rec.table_alias := '';
629 l_join_rec.fact_column := 'outcome_id,result_id,reason_id';
630 l_join_rec.column_name := '';
631
632 x_join_tbl.EXTEND;
633 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
634
635 ELSE
636
637 l_join_rec.table_name := '';
638 l_join_rec.table_alias := '';
639 l_join_rec.fact_column := 'media_item_type';
640 l_join_rec.column_name := '';
641
642 x_join_tbl.EXTEND;
643 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
644
645 END IF;
646
647
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 RAISE;
652
653 END get_join_info;
654 /* -----------------------------------------------------------------------------
655 get_viewby_select_clause:returns select clause for view by
656 ----------------------------------------------------------------------------- */
657 FUNCTION get_viewby_select_clause (
658 p_view_by IN VARCHAR2
659 , p_func_area IN VARCHAR2
660 , p_version IN VARCHAR2)
661 RETURN VARCHAR2
662 IS
663 l_view_by_select VARCHAR2(1000);
664 BEGIN
665 l_view_by_select:='SELECT '||
666 (CASE p_view_by
667 WHEN g_ai_ccntr_dim
668 THEN 'v.group_name VIEWBY,v.server_group_id VIEWBYID '
669 WHEN g_ai_dnis_dim
670 THEN 'dnis_name VIEWBY,dnis_name VIEWBYID '
671 WHEN g_ai_class_dim
672 THEN 'classification_value VIEWBY,classification_value VIEWBYID '
673 ELSE
674 'v.value VIEWBY,v.id VIEWBYID '
675 END);
676
677 RETURN l_view_by_select;
678 END;
679 /* -----------------------------------------------------------------------------
680 Status_sql_daylevel for ECM backlog measure and AI continued measure wherein
681 no join to time table is required. .Cross verify if such a thing does not exist in POA already.
682 ----------------------------------------------------------------------------- */
683 FUNCTION status_sql_daylevel (
684 p_fact_name IN VARCHAR2
685 , p_row_type_where_clause IN VARCHAR2
686 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
687 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
688 , p_time_type IN VARCHAR2 default 'ESD'
689 , p_union IN VARCHAR2 default 'ALL'
690 )
691 RETURN VARCHAR2
692 IS
693 /* Pass ESD OR AOD for p_time_type, for the where clause to choose
694 Effective Start Date or As of Date.If both of them are the same, remove this clause.Verify.
695 */
696 l_query VARCHAR2 (10000);
697 l_col_names VARCHAR2 (10000);
698 l_group_and_sel_clause VARCHAR2 (10000);
699 l_c_calc_end_date VARCHAR2 (70);
700 l_p_calc_end_date VARCHAR2 (70);
701 l_date_decode_begin VARCHAR2 (1000);
702 l_date_decode_end VARCHAR2(1000);
703 l_cur_date_clause VARCHAR2(500);
704 l_prev_date_clause VARCHAR2(500);
705 l_full_where_clause VARCHAR2 (10000);
706
707
708 BEGIN
709 --the dimension column name
710 l_group_and_sel_clause := ' fact.' || p_join_tables (1).fact_column;
711
712 IF P_TIME_TYPE='ESD' THEN
713 l_c_calc_end_date := 'TO_NUMBER(TO_CHAR('||g_c_period_start_date||',''J''))';
714 l_p_calc_end_date := 'TO_NUMBER(TO_CHAR('||g_p_period_start_date||',''J''))';
715 ELSE
716 l_c_calc_end_date := 'TO_NUMBER(TO_CHAR('||g_c_as_of_date||',''J''))';
717 l_p_calc_end_date := 'TO_NUMBER(TO_CHAR('||g_p_as_of_date||',''J''))';
718 END IF;
719
720 FOR i IN 1 .. p_col_name.COUNT
721 LOOP
722
723
724 IF p_col_name(i).to_date_type='XTD' then
725
726 IF (p_col_name(i).column_name='NULL') THEN
727 l_date_decode_begin := NULL;
728 l_cur_date_clause := NULL;
729 l_prev_date_clause := NULL;
730 l_date_decode_end := NULL;
731
732 l_col_names :=
733 l_col_names
734 || ',NULL c_'|| p_col_name (i).column_alias
735 || fnd_global.newline;
736
737 ELSE
738 l_date_decode_begin := 'decode(fact.time_id,';
739 l_cur_date_clause := l_c_calc_end_date || ',';
740 l_prev_date_clause := l_p_calc_end_date || ',';
741 l_date_decode_end := ',null)';
742
743 l_col_names :=
744 l_col_names
745 || ',sum('
746 || l_date_decode_begin
747 ||l_cur_date_clause
748 || p_col_name (i).column_name
749 || l_date_decode_end
750 || ') c_'
751 || p_col_name (i).column_alias
752 || fnd_global.newline;
753
754 END IF;
755 -- Regular current column
756 -- Prev column (based on prior_code)
757
758
759 IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
760 THEN
761
762 IF (p_col_name(i).column_name='NULL') THEN
763 l_col_names:=
764 l_col_names
765 || ',NULL p_'|| p_col_name (i).column_alias
766 || fnd_global.newline;
767
768 ELSE
769 l_col_names :=
770 l_col_names
771 || ', sum('
772 || l_date_decode_begin
773 || l_prev_date_clause
774 || p_col_name (i).column_name
775 || l_date_decode_end
776 || ') p_'
777 || p_col_name (i).column_alias
778 || fnd_global.newline;
779 END IF;
780 END IF;
781
782 -- If grand total is flagged, do current and prior grand totals
783 IF (p_col_name (i).grand_total = 'Y')
784 THEN
785 -- Sum of current column
786 l_col_names :=
787 l_col_names
788 || ', sum(sum('
789 || l_date_decode_begin
790 || l_cur_date_clause
791 || p_col_name (i).column_name
792 || l_date_decode_end
793 || ')) over () c_'
794 || p_col_name (i).column_alias
795 || '_total'
796 || fnd_global.newline;
797
798 -- Sum of prev column (based on prior_code flagging)
799 IF ( p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors
800 OR p_col_name(i).prior_code = poa_dbi_util_pkg.prev_prev
801 OR p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )
802 THEN
803 l_col_names :=
804 l_col_names
805 || ', sum(sum('
806 || l_date_decode_begin
807 || l_prev_date_clause
808 || p_col_name (i).column_name
809 || l_date_decode_end
810 || ')) over () p_'
811 || p_col_name (i).column_alias
812 || '_total'
813 || fnd_global.newline;
814 END IF;
815
816
817 END IF;
818 END IF;
819 END LOOP;
820 l_full_where_clause := ' WHERE time_id IN ('||l_c_calc_end_date||','||l_p_calc_end_date||') and period_type_id=1 '||p_row_type_where_clause;
821
822 l_query :=
823 '(select '
824 || l_group_and_sel_clause
825 || l_col_names
826 || '
827 from '
828 || p_fact_name
829 || ' fact'
830 || l_full_where_clause
831 || '
832 group by '
833 || l_group_and_sel_clause
834 || ')';
835
836 IF p_union='ALL' then
837 l_query := l_query||' UNION ALL ';
838 END IF;
839
840 RETURN l_query;
841
842 END status_sql_daylevel;
843 /* -----------------------------------------------------------------------------------------
844 Get Bind Vars. Pasees values for bind variables that are used in the front end query, back to PMV.
845 Has to be customized based on the report [p_func_area]
846 -------------------------------------------------------------------------------------------- */
847 PROCEDURE get_bind_vars (
848 x_custom_output IN OUT NOCOPY bis_query_attributes_tbl
849 , p_func_area IN VARCHAR2)
850 IS
851 l_custom_rec bis_query_attributes;
852 l_inbound VARCHAR2(50);
853 l_webcall VARCHAR2(50);
854 l_direct VARCHAR2(50);
855 l_dialed VARCHAR2(50);
856 l_unsolicited VARCHAR2(50);
857 l_unknown VARCHAR2(50);
858
859 BEGIN
860 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
861 IF p_func_area='ICSTR' THEN
862 l_custom_rec.attribute_name := ':l_unknown';
863 l_custom_rec.attribute_value := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
864 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
865 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
866 x_custom_output.EXTEND;
867 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
868 END IF;
869 IF p_func_area='IOUTP' THEN
870 l_custom_rec.attribute_name := ':l_lookup_type';
871 l_custom_rec.attribute_value := 'BIX_PMV_AI_OUTCOMES';
872 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
873 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
874 x_custom_output.EXTEND;
875 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
876 END IF;
877 IF p_func_area='IORRR' THEN
878
879 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
880
881 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
882 THEN
883 l_unknown := 'Unknown';
884 END IF;
885
886 l_custom_rec.attribute_name := ':l_outcome_filter';
887 l_custom_rec.attribute_value := -1;
888 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
889 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
890 x_custom_output.EXTEND;
891 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
892
893 l_custom_rec.attribute_name := ':l_unknown';
894 l_custom_rec.attribute_value := l_unknown;
895 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
896 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
897 x_custom_output.EXTEND;
898 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
899
900 END IF;
901
902 IF p_func_area='ICMTP' THEN
903
904 l_inbound := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_AI_INBOUND');
905
906 IF l_inbound IS NULL OR l_inbound = 'BIX_PMV_AI_INBOUND'
907 THEN
908 l_inbound := 'Inbound';
909 END IF;
910
911 l_webcall := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_AI_WEBCALL');
912
913 IF l_webcall IS NULL OR l_webcall = 'BIX_PMV_AI_WEBCALL'
914 THEN
915 l_webcall := 'Web Callback';
916 END IF;
917
918 l_dialed := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_AI_DIALED');
919
920 IF l_dialed IS NULL OR l_dialed = 'BIX_PMV_AI_DIALED'
921 THEN
922 l_dialed := 'Agent Dialed';
923 END IF;
924
925 l_direct := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_AI_DIRECT');
926
927 IF l_direct IS NULL OR l_direct = 'BIX_PMV_AI_DIRECT'
928 THEN
929 l_direct := 'Direct Dialed';
930 END IF;
931
932 l_unsolicited := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_AI_UNSOLICITED');
933
934 IF l_unsolicited IS NULL OR l_unsolicited = 'BIX_PMV_AI_UNSOLICITED'
935 THEN
936 l_unsolicited := 'Unsolicited';
937 END IF;
938
939 l_custom_rec.attribute_name := ':l_inbound';
940 l_custom_rec.attribute_value:= l_inbound;
941 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
942 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
943
944 x_custom_output.Extend();
945 x_custom_output(x_custom_output.count) := l_custom_rec;
946
947 l_custom_rec.attribute_name := ':l_direct';
948 l_custom_rec.attribute_value:= l_direct;
949 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
950 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
951
952 x_custom_output.Extend();
953 x_custom_output(x_custom_output.count) := l_custom_rec;
954
955 l_custom_rec.attribute_name := ':l_dialed';
956 l_custom_rec.attribute_value:= l_dialed;
957 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
958 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
959
960 x_custom_output.Extend();
961 x_custom_output(x_custom_output.count) := l_custom_rec;
962
963 l_custom_rec.attribute_name := ':l_webcall';
964 l_custom_rec.attribute_value:= l_webcall;
965 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
966 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
967
968 x_custom_output.Extend();
969 x_custom_output(x_custom_output.count) := l_custom_rec;
970
971 l_custom_rec.attribute_name := ':l_unsolicited';
972 l_custom_rec.attribute_value:= l_unsolicited;
973 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
974 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
975
976 x_custom_output.Extend();
977 x_custom_output(x_custom_output.count) := l_custom_rec;
978
979 END IF;
980 IF g_dnis is NOT NULL AND g_dnis NOT IN ('INBOUND','OUTBOUND') THEN
981 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
982 l_custom_rec.attribute_name := ':l_dnis';
983 l_custom_rec.attribute_value := g_dnis;
984 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
985 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
986 x_custom_output.EXTEND;
987 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
988 END IF;
989
990 IF p_func_area='IAGTR' THEN
991 l_custom_rec.attribute_name := ':l_agent_group';
992 l_custom_rec.attribute_value := g_agent_group;
993 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
994 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
995 x_custom_output.EXTEND;
996 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
997 END IF;
998
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 RAISE;
1002 END;
1003 /* -----------------------------------------------------------------------------------------
1004 Get Divided Measure ex: For a measure, if current value is a*100/(b+c)
1005 and prior value is a1*100/(b1+c1)
1006 p_num ->a
1007 p_denom ->b+c
1008 p_percentage->Y
1009 p_pnum ->a1
1010 p_pdenom->b1+c1
1011 The others are attribute names
1012
1013 -------------------------------------------------------------------------------------------- */
1014
1015 FUNCTION GET_DIVIDED_MEASURE (
1016 p_percentage VARCHAR2,
1017 p_num VARCHAR2,
1018 p_denom VARCHAR2,
1019 p_measurecol VARCHAR2,
1020 p_pnum VARCHAR2:=NULL,
1021 p_pdenom VARCHAR2:=NULL,
1022 p_totalcol VARCHAR2:=NULL,
1023 p_changecol VARCHAR2:=NULL,
1024 p_changetotalcol VARCHAR2:=NULL,
1025 p_convunitfordenom VARCHAR2:=NULL
1026 ) RETURN VARCHAR2
1027 IS
1028 l_percentage VARCHAR2(4);
1029 l_col_text VARCHAR2(2000);
1030 l_curr_text VARCHAR2(1000);
1031 l_prev_text VARCHAR2(1000);
1032 l_prev_text_denom VARCHAR2(1000);
1033 l_conv_text VARCHAR2(1000);
1034
1035 BEGIN
1036
1037 IF p_percentage='Y' THEN
1038 l_percentage:='*100';
1039 END IF;
1040
1041
1042 --This is if the measure needs to be converted between 2 time units
1043 --say secs to hours for Login Time. p_convunitfordenom contains /3600
1044
1045 l_conv_text := ' ';
1046 IF p_convunitfordenom IS NOT NULL THEN
1047 l_conv_text:=p_convunitfordenom||' ';
1048 END IF;
1049
1050 --Current value
1051 l_col_text:='SUM('||p_num||')'||l_percentage||'/DECODE(sum('||p_denom||')'||l_conv_text||',0,NULL,sum('||p_denom||')'||l_conv_text||') '||p_measurecol;
1052
1053 IF p_totalcol IS NOT NULL THEN
1054 --Total
1055 l_col_text:=l_col_text||','||'SUM(SUM('||p_num||')) over()'||l_percentage||'/DECODE(SUM(SUM('||p_denom||')) OVER()'||l_conv_text||',0,NULL,SUM(SUM('||p_denom||')) over()'||l_conv_text||') '||p_totalcol;
1056 END IF;
1057
1058
1059 IF p_changecol IS NOT NULL THEN
1060 IF p_percentage='Y' THEN
1061 --Change - absolute value ex: 80%-40%
1062 l_curr_text:='(SUM('||p_num||')'||l_percentage||'/DECODE(sum('||p_denom||'),0,NULL,sum('||p_denom||'))) ';
1063 l_prev_text:='(SUM('||p_pnum||')'||l_percentage||'/DECODE(sum('||p_pdenom||'),0,NULL,sum('||p_pdenom||'))) ';
1064 l_col_text:=l_col_text||', '||l_curr_text||'-'||l_prev_text||p_changecol;
1065
1066 --Change Total - absolute value ex: 100%-120%
1067 l_curr_text:='(SUM(SUM('||p_num||')) over()'||l_percentage||'/DECODE(SUM(SUM('||p_denom||')) OVER(),0,NULL,SUM(SUM('||p_denom||')) over())) ';
1068 l_prev_text:='(SUM(SUM('||p_pnum||')) over()'||l_percentage||'/DECODE(SUM(SUM('||p_pdenom||')) OVER(),0,NULL,SUM(SUM('||p_pdenom||')) over())) ';
1069
1070 l_col_text:=l_col_text||', '||l_curr_text||'-'||l_prev_text||p_changetotalcol;
1071 ELSE
1072
1073 --Change - percentage value ex: 4 to 5 is 25%
1074 l_curr_text:='(SUM('||p_num||')'||l_percentage||'/DECODE(sum('||p_denom||'),0,NULL,sum('||p_denom||'))) ';
1075 l_prev_text:='(SUM('||p_pnum||')'||l_percentage||'/DECODE(sum('||p_pdenom||'),0,NULL,sum('||p_pdenom||'))) ';
1076 l_prev_text_denom:='DECODE('||l_prev_text||',0,NULL,'||l_prev_text||')';
1077 l_col_text:=l_col_text||',('||l_curr_text||'-'||l_prev_text||' )*100/'||l_prev_text_denom||p_changecol;
1078 --Change Total - percentage value ex: 4 to 5 is 25%
1079 l_curr_text:='(SUM(SUM('||p_num||')) over()'||l_percentage||'/DECODE(SUM(SUM('||p_denom||')) OVER(),0,NULL,SUM(SUM('||p_denom||')) over())) ';
1080 l_prev_text:='(SUM(SUM('||p_pnum||')) over()'||l_percentage||'/DECODE(SUM(SUM('||p_pdenom||')) OVER(),0,NULL,SUM(SUM('||p_pdenom||')) over())) ';
1081 l_prev_text_denom:='DECODE('||l_prev_text||',0,NULL,'||l_prev_text||')';
1082 l_col_text:=l_col_text||',('||l_curr_text||'-'||l_prev_text||' )*100/'||l_prev_text_denom||p_changetotalcol;
1083 END IF;
1084
1085 END IF;
1086 RETURN l_col_text;
1087
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 RAISE;
1091 END get_divided_measure;
1092
1093
1094 /****START GET_PERTOTAL_MEASURE ****/
1095
1096 FUNCTION GET_PERTOTAL_MEASURE
1097 (
1098 p_num VARCHAR2,
1099 p_measurecol VARCHAR2
1100 ) RETURN VARCHAR2
1101 IS
1102 l_col_text VARCHAR2(2000);
1103
1104 BEGIN
1105
1106
1107 --Current value
1108 l_col_text:='SUM('||p_num||')*100
1109 /DECODE(sum(sum('||p_num||')) over(),0,NULL,
1110 sum(sum('||p_num||')) over()) ' || p_measurecol ;
1111
1112
1113 RETURN l_col_text;
1114
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 RAISE;
1118 END get_pertotal_measure;
1119
1120 /****END GET_PERTOTAL_MEASURE ****/
1121
1122 /**** START GET_DEVAVG_MEASURE ***/
1123
1124 FUNCTION GET_DEVAVG_MEASURE
1125 (
1126 p_percentage VARCHAR2,
1127 p_num VARCHAR2,
1128 p_denom VARCHAR2,
1129 p_col VARCHAR2,
1130 p_convunitfordenom VARCHAR2:=NULL
1131 ) RETURN VARCHAR2
1132 IS
1133 l_percentage VARCHAR2(4);
1134 l_col_text VARCHAR2(2000);
1135 l_curr_text VARCHAR2(1000);
1136 l_prev_text VARCHAR2(1000);
1137 l_conv_text VARCHAR2(1000);
1138 BEGIN
1139
1140
1141 IF p_percentage='Y' THEN
1142 l_percentage:='*100';
1143 END IF;
1144
1145 --This is if the measure needs to be converted between 2 time units
1146 --say secs to hours for Login Time. p_convunitfordenom contains /3600
1147
1148 l_conv_text := ' ';
1149 IF p_convunitfordenom IS NOT NULL THEN
1150 l_conv_text:=p_convunitfordenom||' ';
1151 END IF;
1152
1153
1154 --Current value
1155 l_col_text:='SUM('||p_num||')'||l_percentage||'/DECODE(sum('||p_denom||')'||l_conv_text||',0,NULL,sum('||p_denom||')'||l_conv_text||')
1156 -
1157 SUM(SUM('||p_num||')) over()'||l_percentage||'/DECODE(sum(sum('||p_denom||')) over()'||l_conv_text||',0,NULL,
1158 sum(sum('||p_denom||')) over()'||l_conv_text||'
1159 ) ' || p_col ;
1160
1161
1162 RETURN l_col_text;
1163
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 RAISE;
1167 END get_devavg_measure;
1168
1169 /**** END GET_DEVAVG_MEASURES ***/
1170
1171 /* -----------------------------------------------------------------------------------------
1172 Get Simple Measure ex: For a measure, if current value is a
1173 and prior value is a1
1174 p_curr ->a
1175 p_prev ->a1
1176 The others are attribute names
1177
1178
1179 -------------------------------------------------------------------------------------------- */
1180 FUNCTION GET_SIMPLE_MEASURE (
1181 p_curr VARCHAR2,p_measurecol VARCHAR2,p_prev VARCHAR2:=NULL,p_totalcol VARCHAR2:=NULL,
1182 p_changecol VARCHAR2:=NULL,p_changetotalcol VARCHAR2:=NULL,
1183 p_convertunit VARCHAR2:=NULL
1184 ) RETURN VARCHAR2
1185 IS
1186
1187 l_col_text VARCHAR2(2000);
1188 l_curr_text VARCHAR2(1000);
1189 l_prev_text VARCHAR2(1000);
1190 l_conv_text VARCHAR2(1000);
1191 BEGIN
1192
1193
1194 --This is if the measure needs to be converted between 2 time units
1195 --say secs to hours for Login Time. p_convertunit contains /3600
1196 l_conv_text := ' ';
1197 IF p_convertunit IS NOT NULL THEN
1198 l_conv_text:=p_convertunit||' ';
1199 END IF;
1200
1201
1202 --Current value
1203 l_col_text:='SUM('||p_curr||')'||l_conv_text||' '||p_measurecol;
1204
1205 IF p_totalcol IS NOT NULL THEN
1206 --Total
1207 l_col_text:=l_col_text||','||'SUM(SUM('||p_curr||')) over()'||l_conv_text||p_totalcol;
1208 END IF;
1209
1210 IF p_changecol IS NOT NULL THEN
1211 --Change - percentage value ex: 4 to 5 is 25%
1212 l_curr_text:='SUM('||p_curr||') ';
1213 l_prev_text:='SUM('||p_prev||') ';
1214 l_col_text:=l_col_text||',('||l_curr_text||'-'||l_prev_text||' )*100/DECODE('||l_prev_text||',0,NULL,'||l_prev_text||')'||p_changecol;
1215 --Change Total - percentage value ex: 4 to 5 is 25%
1216 l_curr_text:='SUM(SUM('||p_curr||')) over() ';
1217 l_prev_text:='SUM(SUM('||p_prev||')) over() ';
1218 l_col_text:=l_col_text||',('||l_curr_text||'-'||l_prev_text||' )*100/DECODE('||l_prev_text||',0,NULL,'||l_prev_text||')'||p_changetotalcol;
1219 END IF;
1220
1221
1222 RETURN l_col_text;
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 RAISE;
1226 END GET_SIMPLE_MEASURE;
1227
1228
1229 FUNCTION trend_sql (
1230 p_xtd IN VARCHAR2
1231 , p_comparison_type IN VARCHAR2
1232 , p_fact_name IN VARCHAR2
1233 , p_where_clause IN VARCHAR2
1234 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1235 , p_use_grpid IN VARCHAR2 := 'Y'
1236 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1237 , p_fact_hint IN VARCHAR2 := null
1238 , p_union_clause IN VARCHAR2 DEFAULT NULL) --This parameter is specific to ICI
1239 RETURN VARCHAR2
1240 IS
1241 l_query VARCHAR2 (10000);
1242 l_col_names VARCHAR2 (4000);
1243 l_inner_col_names VARCHAR2 (4000);
1244 l_col_alias VARCHAR2 (4000);
1245 l_total_col_alias VARCHAR2 (4000);
1246 l_view_by VARCHAR2 (120);
1247 l_cal_clause VARCHAR2 (1000);
1248 l_time_clause VARCHAR2 (400);
1249 l_grpid_clause VARCHAR2 (200);
1250 l_c_calc_end_date VARCHAR2 (1000);
1251 l_p_calc_end_date VARCHAR2 (1000);
1252 l_c_report_date_str VARCHAR2 (1000);
1253 l_p_report_date_str VARCHAR2 (1000);
1254 l_inlist_bmap NUMBER := 0;
1255 l_in_join_tables VARCHAR2 (240) := '';
1256 l_compute_opening_bal varchar2(1) := 'N';
1257 l_balance_report varchar2(1) := 'N';
1258 l_outer_time_clause VARCHAR2 (400);
1259 BEGIN
1260
1261 IF(p_in_join_tables is not null) then
1262
1263 FOR i in 1 .. p_in_join_tables.COUNT
1264 LOOP
1265 l_in_join_tables := l_in_join_tables || ' , ' || p_in_join_tables(i).table_name || ' ' || p_in_join_tables(i).table_alias;
1266 END LOOP;
1267 END IF;
1268
1269 IF p_col_name.FIRST IS NOT NULL
1270 THEN
1271 FOR i IN p_col_name.FIRST .. p_col_name.LAST
1272 LOOP
1273 IF p_col_name (i).to_date_type = 'XED'
1274 THEN
1275 l_c_calc_end_date := g_c_period_end_date;
1276 l_p_calc_end_date := g_p_period_end_date;
1277 l_c_report_date_str := ' n.end_date ';
1278 l_p_report_date_str := ' n.end_date ';
1279 l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1280 , g_inlist_xed);
1281 elsif p_col_name(i).to_date_type = 'RLX' then
1282 l_c_calc_end_date := g_c_as_of_date;
1283 l_p_calc_end_date := g_p_as_of_date;
1284 l_c_report_date_str := ' n.end_date ';
1285 l_p_report_date_str := ' n.end_date ';
1286 l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap
1287 , g_inlist_rlx);
1288 elsif p_col_name(i).to_date_type = 'BAL' then
1289 l_c_calc_end_date := g_c_as_of_date_balance;
1290 l_p_calc_end_date := g_p_as_of_date_balance;
1291 l_c_report_date_str := ' n.end_date ';
1292 l_p_report_date_str := ' n.end_date ';
1293 l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap
1294 , g_inlist_bal);
1295 l_balance_report := 'Y';
1296 ELSE -- XTD or YTD
1297 l_c_calc_end_date := g_c_as_of_date;
1298 l_p_calc_end_date := g_p_as_of_date;
1299 l_c_report_date_str := ' LEAST (n.end_date, &BIS_CURRENT_ASOF_DATE) ';
1300 l_p_report_date_str := ' LEAST (n.end_date, &BIS_PREVIOUS_ASOF_DATE) ';
1301
1302 IF p_col_name (i).to_date_type = 'XTD'
1303 THEN
1304 l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1305 , g_inlist_xtd);
1306 ELSE -- YTD
1307 l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1308 , g_inlist_ytd);
1309 END IF;
1310 END IF;
1311
1312 l_col_names :=
1313 l_col_names
1314 || ', sum(case when (n.start_date between '
1315 || case
1316 when p_col_name(i).to_date_type = 'RLX' or
1317 p_col_name(i).to_date_type = 'BAL' then
1318 poa_dbi_util_pkg.get_report_start_date(p_xtd)
1319 || ' and &BIS_CURRENT_EFFECTIVE_END_DATE and n.ordinal in (-1,2)'
1320 else
1321 '&BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE'
1322 end
1323 || ' and i.report_date = '
1324 || l_c_report_date_str
1325 || ') then '
1326 || p_col_name (i).column_alias
1327 || ' else null end) CURR_'
1328 || p_col_name (i).column_alias
1329 || '
1330 ';
1331 l_inner_col_names :=
1332 l_inner_col_names
1333 || ', sum(' || p_col_name(i).column_name || ') ' || p_col_name(i).column_alias;
1334
1335 IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
1336 THEN
1337 l_col_names :=
1338 l_col_names
1339 || ', lag(sum(case when (n.start_date between '
1340 || case
1341 when p_col_name(i).to_date_type = 'RLX' or
1342 p_col_name(i).to_date_type = 'BAL' then
1343 poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y')
1344 || ' and &BIS_PREVIOUS_EFFECTIVE_END_DATE and n.ordinal in (-1,1)'
1345 else
1346 '&BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE'
1347 end
1348 || ' and i.report_date = '
1349 || l_p_report_date_str
1350 || ' ) then '
1351 || p_col_name (i).column_alias
1352 || ' else null end), &LAG'
1353 || ') over (order by '
1354 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1355 || 'n.start_date) p_'
1356 || p_col_name (i).column_alias
1357 || '
1358 ';
1359 END IF;
1360
1361 -- Opening Balance Column
1362 if p_col_name(i).prior_code = 5 and
1363 p_col_name(i).to_date_type = 'BAL' then
1364 l_compute_opening_bal := 'Y';
1365 l_col_names :=
1366 l_col_names
1367 || ', lag(sum('
1368 || p_col_name(i).column_alias
1369 || '), decode(&BIS_TIME_COMPARISON_TYPE,''YEARLY'',&LAG *2,1)) over (order by n.ordinal,n.start_date) o_'
1370 || p_col_name(i).column_alias
1371 || ' ';
1372 end if;
1373
1374 -- Grand total for current columns
1375 -- Note: RLX and BAL not supported here
1376 IF (p_col_name (i).grand_total = 'Y')
1377 THEN
1378 l_col_names :=
1379 l_col_names
1380 || ',
1381 sum(sum('
1382 || p_col_name (i).column_alias
1383 || ')) over () CURR_'
1384 || p_col_name (i).column_alias
1385 || '_total ';
1386
1387 -- Grand total for previous columns
1388 IF (p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors)
1389 THEN
1390 l_col_names :=
1391 l_col_names
1392 || ',
1393 sum(lag(sum('
1394 || p_col_name (i).column_alias
1395 || '))) over () p_'
1396 || p_col_name (i).column_alias
1397 || '_total';
1398 END IF;
1399 END IF;
1400
1401 END LOOP;
1402
1403 END IF;
1404
1405 IF ( p_xtd = 'WTD'
1406 AND p_comparison_type = 'Y')
1407 THEN
1408 l_time_clause :=
1409 ' ((cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE) or (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)) ';
1410 l_outer_time_clause :=
1411 ' and ((n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE) or (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)) ';
1412 ELSE
1413 if p_xtd like 'RL%' then
1414 l_time_clause := '1=1 ';
1415 else
1416 l_time_clause := ' cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE ';
1417 l_outer_time_clause := ' and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE ';
1418 end if;
1419 END IF;
1420
1421 IF ( p_comparison_type = 'Y'
1422 AND p_xtd <> 'YTD')
1423 THEN
1424 -- Yearly
1425 l_cal_clause :=
1426 CASE
1427 WHEN -- (XTD or YTD) only
1428 ( BITAND (l_inlist_bmap
1429 , g_inlist_xtd) = g_inlist_xtd
1430 OR BITAND (l_inlist_bmap
1431 , g_inlist_ytd) = g_inlist_ytd)
1432 AND NOT BITAND (l_inlist_bmap
1433 , g_inlist_xed) = g_inlist_xed
1434 THEN -- (XTD or YTD) only
1435 ' and n.report_date = (case when (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
1436 then least(cal.end_date, &BIS_PREVIOUS_ASOF_DATE)
1437 else least(cal.end_date, &BIS_CURRENT_ASOF_DATE) end) '
1438 WHEN -- (XTD or YTD) and XED
1439 ( BITAND (l_inlist_bmap
1440 , g_inlist_xtd) = g_inlist_xtd
1441 OR BITAND (l_inlist_bmap
1442 , g_inlist_ytd) = g_inlist_ytd)
1443 AND BITAND (l_inlist_bmap
1444 , g_inlist_xed) = g_inlist_xed
1445 THEN -- (XTD or YTD) and XED
1446 ' and n.report_date in ( (case when (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
1447 then least(cal.end_date, &BIS_PREVIOUS_ASOF_DATE)
1448 else least(cal.end_date, &BIS_CURRENT_ASOF_DATE) end)
1449 , &BIS_CURRENT_EFFECTIVE_END_DATE
1450 , &BIS_PREVIOUS_EFFECTIVE_END_DATE) '
1451 WHEN -- XED only
1452 NOT ( BITAND (l_inlist_bmap
1453 , g_inlist_xtd) = g_inlist_xtd
1454 OR BITAND (l_inlist_bmap
1455 , g_inlist_ytd) = g_inlist_ytd)
1456 AND BITAND (l_inlist_bmap
1457 , g_inlist_xed) = g_inlist_xed
1458 THEN -- placeholder for XED only
1459 ' '
1460 when bitand(l_inlist_bmap,g_inlist_rlx) = g_inlist_rlx then
1461 ' and n.report_date = cal.report_date '
1462 when bitand(l_inlist_bmap,g_inlist_bal) = g_inlist_bal then
1463 ' '
1464 END;
1465 ELSE
1466 -- Sequential comparison type
1467 l_cal_clause :=
1468 CASE
1469 WHEN -- (XTD or YTD) only
1470 ( BITAND (l_inlist_bmap
1471 , g_inlist_xtd) = g_inlist_xtd
1472 OR BITAND (l_inlist_bmap
1473 , g_inlist_ytd) = g_inlist_ytd)
1474 AND NOT BITAND (l_inlist_bmap
1475 , g_inlist_xed) = g_inlist_xed
1476 THEN -- (XTD or YTD) only
1477 ' and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
1478 and n.report_date between cal.start_date and cal.end_date '
1479 WHEN -- (XTD or YTD) and XED
1480 ( BITAND (l_inlist_bmap
1481 , g_inlist_xtd) = g_inlist_xtd
1482 OR BITAND (l_inlist_bmap
1483 , g_inlist_ytd) = g_inlist_ytd)
1484 AND BITAND (l_inlist_bmap
1485 , g_inlist_xed) = g_inlist_xed
1486 THEN -- (XTD or YTD) and XED
1487 ' and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE, &BIS_CURRENT_EFFECTIVE_END_DATE)
1488 and n.report_date between cal.start_date and cal.end_date '
1489 WHEN -- XED only
1490 NOT ( BITAND (l_inlist_bmap
1491 , g_inlist_xtd) = g_inlist_xtd
1492 OR BITAND (l_inlist_bmap
1493 , g_inlist_ytd) = g_inlist_ytd)
1494 AND BITAND (l_inlist_bmap
1495 , g_inlist_xed) = g_inlist_xed
1496 THEN -- XED only
1497 ' and 555 = 555 /* sequential xed only */ '
1498 when bitand(l_inlist_bmap,g_inlist_rlx) = g_inlist_rlx then
1499 ' and n.report_date = cal.report_date '
1500 when bitand(l_inlist_bmap,g_inlist_bal) = g_inlist_bal then
1501 ' '
1502 END;
1503 END IF;
1504
1505 IF (p_use_grpid = 'Y')
1506 THEN
1507 l_grpid_clause := ' and fact.grp_id = decode(n.period_type_id,1,14,16,13,32,11,64,7)';
1508 ELSIF (p_use_grpid = 'R')
1509 THEN
1510 l_grpid_clause := 'and fact.grp_id = decode(cal.period_type_id,1,0,16,1,32,3,64,7)';
1511 END IF;
1512
1513 l_query :=
1514 '(select n.start_date'
1515 || case when p_xtd like 'RL%' then ', n.ordinal ' end
1516 || '
1517 ' ||l_col_names || '
1518 from (select ' || p_fact_hint || ' '
1519 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1520 || 'n.start_date, n.report_date '
1521 || l_inner_col_names
1522 || ' from '
1523 || p_fact_name
1524 || ' fact,
1525 '
1526 || case
1527 when p_xtd like 'RL%' then
1528 case
1529 when l_balance_report = 'N' then
1530 '( select /*+ NO_MERGE */ cal.ordinal,n.time_id,n.record_type_id,n.period_type_id,n.report_date,cal.start_date,cal.end_date'
1531 || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd)
1532 || ' cal, fii_time_structures n
1533 where '
1534 || l_time_clause
1535 || l_cal_clause
1536 -- &RLX_NESTED_PATTERN should be replaced with
1537 -- some &BIS bind substitution when available from fii/bis team.
1538 || ' and bitand(n.record_type_id,&RLX_NESTED_PATTERN) = &RLX_NESTED_PATTERN ) n'
1539 || l_in_join_tables
1540 || '
1541 where fact.time_id = n.time_id
1542 and fact.period_type_id = n.period_type_id
1543 '
1544 else
1545 '( select /*+ NO_MERGE */ cal.ordinal,cal.start_date, cal.report_date'
1546 || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
1547 || ' cal where '
1548 || l_time_clause
1549 || l_cal_clause
1550 || ' ) n
1551 where fact.report_date = least(n.report_date,&LAST_COLLECTION)
1552 '
1553 end
1554 else -- non RL%
1555 ' (select /*+ NO_MERGE */ n.time_id,n.record_type_id, n.period_type_id,n.report_date,cal.start_date,cal.end_date
1556 from '
1557 || poa_dbi_util_pkg.get_calendar_table (p_xtd)
1558 || ' cal, fii_time_rpt_struct_v n
1559 where '
1560 || l_time_clause
1561 || l_cal_clause
1562 || ' and bitand(n.record_type_id, '
1563 || CASE -- if one or more columns = YTD then use nested pattern
1564 WHEN BITAND (l_inlist_bmap, g_inlist_ytd) = g_inlist_ytd
1565 THEN '&YTD_NESTED_PATTERN'
1566 ELSE '&BIS_NESTED_PATTERN'
1567 END
1568 || ') = n.record_type_id ) n
1569 ' || l_in_join_tables || '
1570 where fact.time_id = n.time_id
1571 '
1572 end
1573 || l_grpid_clause || '
1574 ' || p_where_clause || '
1575 group by '
1576 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1577 || ' n.start_date, n.report_date) i, '
1578 || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
1579 || ' n where i.start_date (+) = n.start_date '
1580 || l_outer_time_clause
1581 || case when p_xtd like 'RL%' then 'and i.ordinal(+) = n.ordinal ' end
1582 || ' group by '
1583 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1584 || 'n.start_date '
1585 || p_union_clause
1586 || ') iset, '
1587 || poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N')
1588 || ' cal '
1589 || '
1590 where cal.start_date between '
1591 || case
1592 when p_xtd like 'RL%' then
1593 poa_dbi_util_pkg.get_report_start_date(p_xtd)
1594 else
1595 '&BIS_CURRENT_REPORT_START_DATE'
1596 end
1597 || ' and &BIS_CURRENT_ASOF_DATE
1598 and cal.start_date = iset.start_date(+)'
1599 || case when p_xtd like 'RL%' then ' and cal.ordinal = iset.ordinal(+)' end
1600 || '
1601 order by cal.start_date';
1602 RETURN l_query;
1603
1604 END trend_sql;
1605
1606 FUNCTION get_continued_measures(
1607 p_bix_col_tab IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_tbl,
1608 p_where_clause IN OUT NOCOPY VARCHAR2,
1609 p_xtd IN VARCHAR2,
1610 p_comparison_type IN VARCHAR2,
1611 p_mv_set IN VARCHAR2
1612 ) RETURN VARCHAR2 IS
1613
1614 l_select_list VARCHAR2(4000);
1615 l_from_list VARCHAR2(4000);
1616 l_group_by_clause VARCHAR2(4000);
1617 l_where_clause VARCHAR2(4000);
1618 l_tele_inb VARCHAR2(40) := 'TELE_INB';
1619 l_tele_direct VARCHAR2(40) := 'TELE_DIRECT';
1620
1621 BEGIN
1622
1623 l_select_list := 'SELECT fii1.START_DATE ' ;
1624
1625 FOR i IN 1..p_bix_col_tab.COUNT
1626 LOOP
1627 --Move into the IF block if its just a pseudo column value ('0')
1628 IF p_bix_col_tab(i).COLUMN_NAME = '0' THEN
1629 --If prior value for this measure is also to be calculated, then do it here.
1630 IF p_bix_col_tab(i).prior_code = 2 THEN
1631 l_select_list := l_select_list||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS
1632 ||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'p_'||p_bix_col_tab(i).COLUMN_ALIAS;
1633 ELSE--003
1634 --Form the SELECT list where prior value is not required.
1635 l_select_list := l_select_list||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS;
1636 END IF;
1637 ELSE
1638 --Get the prior values for the actual column names.
1639 IF p_bix_col_tab(i).prior_code = 2 THEN
1640 l_select_list := l_select_list||fnd_global.newline||','||
1641 'SUM(CASE when(fii1.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)
1642 THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS
1643 ||fnd_global.newline||','||'SUM(CASE when(fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
1644 THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'p_'||p_bix_col_tab(i).COLUMN_ALIAS;
1645 ELSE --003
1646 l_select_list := l_select_list||fnd_global.newline||','||
1647 'SUM(CASE when(fii1.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)
1648 THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS;
1649 END IF;
1650 END IF;
1651 END LOOP;
1652
1653 p_bix_col_tab.DELETE;
1654
1655 --Form the FROM list and the where clause for reports that display session level values.
1656 IF p_mv_set = 'SES' THEN
1657 l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'||' fii_time_rpt_struct ' ||' cal ,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1 ';
1658 l_where_clause := fnd_global.newline
1659 ||'WHERE fact.time_id = cal.time_id '
1660 ||fnd_global.newline||'AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
1661 ||fnd_global.newline||'AND cal.period_type_id = fact.period_type_id '
1662 ||fnd_global.newline||'AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE '
1663 ||p_where_clause;
1664
1665 IF (p_comparison_type = 'Y' AND p_xtd <> 'YTD') THEN
1666 l_where_clause := l_where_clause ||fnd_global.newline||'AND cal.report_date = (CASE WHEN(fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
1667 THEN least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE) ELSE least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) END) ';
1668 ELSE
1669 l_where_clause := l_where_clause || fnd_global.newline||' AND cal.report_date = least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) ';
1670 END IF;
1671
1672 ELSE
1673 l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1';
1674 l_where_clause := fnd_global.newline
1675 ||'WHERE fact.period_type_id = 1 '
1676 ||fnd_global.newline||'AND fact.time_id = TO_CHAR(fii1.start_date,''J'') '
1677 ||fnd_global.newline||'AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE '
1678 ||p_where_clause;
1679 END IF;
1680
1681
1682 l_group_by_clause := ' GROUP BY fii1.start_date order by start_date ';--002
1683
1684 l_select_list := l_select_list||l_from_list||l_where_clause||l_group_by_clause;
1685
1686 RETURN l_select_list;
1687
1688 END get_continued_measures;
1689
1690
1691
1692 /*-------------------------------------------------------------------------------------------------------
1693 ---------------------------------------------------------------------------------------------------------*/
1694
1695 PROCEDURE get_emc_page_params (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1696 l_as_of_date OUT NOCOPY DATE,
1697 l_period_type OUT NOCOPY VARCHAR2,
1698 l_record_type_id OUT NOCOPY NUMBER,
1699 l_comp_type OUT NOCOPY VARCHAR2,
1700 l_account OUT NOCOPY VARCHAR2,
1701 l_classification OUT NOCOPY VARCHAR2,
1702 l_view_by OUT NOCOPY VARCHAR2
1703 ) IS
1704 l_sql_errm VARCHAR2(32000);
1705 BEGIN
1706 IF (p_page_parameter_tbl.count > 0) THEN
1707 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1708 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
1709 l_period_type := p_page_parameter_tbl(i).parameter_value;
1710 END IF;
1711 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
1712 -- l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
1713 l_as_of_date := p_page_parameter_tbl(i).period_date;
1714 END IF;
1715 IF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
1716 l_comp_type := p_page_parameter_tbl(i).parameter_value;
1717 END IF;
1718 IF p_page_parameter_tbl(i).parameter_name= 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
1719 l_account := p_page_parameter_tbl(i).parameter_id;
1720 END IF;
1721 IF p_page_parameter_tbl(i).parameter_name= 'EMAIL CLASSIFICATION+EMAIL CLASSIFICATION' THEN
1722 l_classification := p_page_parameter_tbl(i).parameter_id;
1723 END IF;
1724 IF p_page_parameter_tbl(i).parameter_name= 'VIEW_BY' THEN
1725 l_view_by := p_page_parameter_tbl(i).parameter_id;
1726 END IF;
1727 END LOOP;
1728 END IF;
1729 --
1730 --First time page patch is applied, sometimes period type
1731 --is not being passed. Force it to MONTH if this happens.
1732 --If at all this happens, this will only happen the very first
1733 --time page patch is applied.
1734 --
1735 IF l_period_type IS NULL THEN
1736 l_period_type := 'FII_TIME_ENT_PERIOD';
1737 END IF;
1738 --
1739 --l_period_type is used to derive the table name. It can only be
1740 --the following values. If it is not, then we will fail the SQL.
1741 --
1742 IF l_period_type <> 'FII_TIME_WEEK'
1743 AND l_period_type <> 'FII_TIME_ENT_PERIOD'
1744 AND l_period_type <> 'FII_TIME_ENT_QTR'
1745 AND l_period_type <> 'FII_TIME_ENT_YEAR'
1746 THEN
1747 l_period_type := NULL;
1748 END IF;
1749 CASE l_period_type
1750 WHEN 'FII_TIME_WEEK' THEN l_record_type_id := 11;
1751 WHEN 'FII_TIME_ENT_PERIOD' THEN l_record_type_id := 23;
1752 WHEN 'FII_TIME_ENT_QTR' THEN l_record_type_id := 55;
1753 WHEN 'FII_TIME_ENT_YEAR' THEN l_record_type_id := 119;
1754 ELSE l_record_type_id := null;
1755 END CASE;
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 NULL;
1759 END get_emc_page_params;
1760
1761 FUNCTION period_start_date(l_as_of_date IN DATE,
1762 l_period_type IN VARCHAR2 ) RETURN DATE IS
1763 l_period_start_Date DATE;
1764 BEGIN
1765 CASE l_period_type
1766 WHEN 'FII_TIME_WEEK' THEN
1767 SELECT week_start_date INTO l_period_start_date
1768 FROM fii_time_day WHERE report_date = l_as_of_date;
1769 WHEN 'FII_TIME_ENT_PERIOD' THEN
1770 SELECT ent_period_start_date INTO l_period_start_date
1771 FROM fii_time_day WHERE report_date = l_as_of_date;
1772 WHEN 'FII_TIME_ENT_QTR' THEN
1773 SELECT ent_qtr_start_date INTO l_period_start_date
1774 FROM fii_time_day WHERE report_date = l_as_of_date;
1775 WHEN 'FII_TIME_ENT_YEAR' THEN
1776 SELECT ent_year_start_date INTO l_period_start_date
1777 FROM fii_time_day WHERE report_date = l_as_of_date;
1778 ELSE l_period_start_date := null;
1779 END CASE;
1780 RETURN l_period_start_date;
1781 EXCEPTION
1782 WHEN OTHERS THEN
1783 return NULL;
1784 END period_start_date;
1785
1786 FUNCTION get_default_params
1787 RETURN VARCHAR2 IS
1788 v_date VARCHAR2(11);
1789 BEGIN
1790 --SELECT to_char(add_months(to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),
1791 --'MM/DD/YYYY'),13),'DD-MON-YYYY')
1792 SELECT to_char(sysdate,'DD-MON-YYYY')
1793 INTO v_date
1794 FROM dual;
1795 RETURN 'BIX_PMV_WEEK_FROM=ALL&BIX_PMV_WEEK_TO=ALL&BIX_PMV_SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL&AS_OF_DATE='||v_date;
1796 EXCEPTION
1797 WHEN OTHERS
1798 THEN
1799 RETURN 'BIX_PMV_WEEK_FROM=ALL&BIX_PMV_WEEK_TO=ALL&BIX_PMV_SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL&AS_OF_DATE='||TO_CHAR(TRUNC(sysdate),'DD-MON-YYYY');
1800 END get_default_params;
1801
1802 PROCEDURE get_ai_page_params (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1803 l_as_of_date OUT NOCOPY DATE,
1804 l_period_type OUT NOCOPY VARCHAR2,
1805 l_record_type_id OUT NOCOPY NUMBER,
1806 l_comp_type OUT NOCOPY VARCHAR2,
1807 l_call_center OUT NOCOPY VARCHAR2,
1808 l_classification OUT NOCOPY VARCHAR2,
1809 l_dnis OUT NOCOPY VARCHAR2,
1810 l_view_by OUT NOCOPY VARCHAR2
1811 ) IS
1812 l_sql_errm VARCHAR2(32000);
1813 BEGIN
1814 IF (p_page_parameter_tbl.count > 0) THEN
1815 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1816 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
1817 l_period_type := p_page_parameter_tbl(i).parameter_value;
1818 END IF;
1819 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
1820 --l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
1821 l_as_of_date := p_page_parameter_tbl(i).period_date;
1822 END IF;
1823 IF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
1824 l_comp_type := p_page_parameter_tbl(i).parameter_value;
1825 END IF;
1826 IF p_page_parameter_tbl(i).parameter_name = 'BIX_TELEPHONY+BIX_CALL_CLASSIFICATION'
1827 THEN
1828 l_classification := p_page_parameter_tbl(i).parameter_id;
1829 END IF;
1830 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_CALL_CENTER'
1831 THEN
1832 l_call_center := p_page_parameter_tbl(i).parameter_id;
1833 END IF;
1834 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_DNIS'
1835 THEN
1836 l_dnis := p_page_parameter_tbl(i).parameter_id;
1837 END IF;
1838 IF p_page_parameter_tbl(i).parameter_name= 'VIEW_BY' THEN
1839 l_view_by := p_page_parameter_tbl(i).parameter_id;
1840 END IF;
1841 END LOOP;
1842 END IF;
1843 --
1844 --First time page patch is applied, sometimes period type
1845 --is not being passed. Force it to MONTH if this happens.
1846 --If at all this happens, this will only happen the very first
1847 --time page patch is applied.
1848 --
1849 IF l_period_type IS NULL THEN
1850 l_period_type := 'FII_TIME_ENT_PERIOD';
1851 END IF;
1852 --
1853 --l_period_type is used to derive the table name. It can only be
1854 --the following values. If it is not, then we will fail the SQL.
1855 --
1856 IF l_period_type <> 'FII_TIME_WEEK'
1857 AND l_period_type <> 'FII_TIME_ENT_PERIOD'
1858 AND l_period_type <> 'FII_TIME_ENT_QTR'
1859 AND l_period_type <> 'FII_TIME_ENT_YEAR'
1860 THEN
1861 l_period_type := NULL;
1862 END IF;
1863 CASE l_period_type
1864 WHEN 'FII_TIME_WEEK' THEN l_record_type_id := 11;
1865 WHEN 'FII_TIME_ENT_PERIOD' THEN l_record_type_id := 23;
1866 WHEN 'FII_TIME_ENT_QTR' THEN l_record_type_id := 55;
1867 WHEN 'FII_TIME_ENT_YEAR' THEN l_record_type_id := 119;
1868 ELSE l_record_type_id := null;
1869 END CASE;
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 NULL;
1873 END get_ai_page_params;
1874
1875 FUNCTION get_ai_default_page_params
1876 RETURN VARCHAR2 IS
1877 v_date VARCHAR2(11);
1878 BEGIN
1879 SELECT to_char(sysdate,'DD-MON-YYYY')
1880 INTO v_date
1881 FROM dual;
1882 RETURN 'FII_TIME_WEEK_FROM=All&FII_TIME_WEEK_TO=All&SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL&AS_OF_DATE='||v_date;
1883 EXCEPTION
1884 WHEN OTHERS
1885 THEN
1886 RETURN 'FII_TIME_WEEK_FROM=All&FII_TIME_WEEK_TO=All&SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL&AS_OF_DATE='||TO_CHAR(TRUNC(sysdate),'DD-MON-YYYY');
1887 END get_ai_default_page_params;
1888
1889 PROCEDURE GET_AO_PAGE_PARAMS
1890 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1891 l_as_of_date OUT NOCOPY DATE,
1892 l_period_type OUT NOCOPY VARCHAR2,
1893 l_record_type_id OUT NOCOPY NUMBER,
1894 l_comp_type OUT NOCOPY VARCHAR2,
1895 l_call_center OUT NOCOPY VARCHAR2,
1896 l_campaign_id OUT NOCOPY VARCHAR2,
1897 l_schedule_id OUT NOCOPY VARCHAR2,
1898 l_source_code_id OUT NOCOPY VARCHAR2,
1899 l_agent_group OUT NOCOPY VARCHAR2,
1900 l_view_by OUT NOCOPY VARCHAR2
1901
1902 ) IS
1903 l_sql_errm VARCHAR2(32000);
1904 tmp varchar2(1000);
1905 BEGIN
1906 IF (p_page_parameter_tbl.count > 0) THEN
1907 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1908 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
1909 l_period_type := p_page_parameter_tbl(i).parameter_value;
1910 END IF;
1911 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
1912 --l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
1913 l_as_of_date := p_page_parameter_tbl(i).period_date;
1914 END IF;
1915 IF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
1916 l_comp_type := p_page_parameter_tbl(i).parameter_value;
1917 END IF;
1918 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_CALL_CENTER'
1919 THEN
1920 l_call_center := p_page_parameter_tbl(i).parameter_id;
1921 END IF;
1922 IF p_page_parameter_tbl(i).parameter_name= 'CAMPAIGN+CAMPAIGN'
1923 THEN
1924 l_source_code_id := p_page_parameter_tbl(i).parameter_id;
1925 END IF;
1926 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_CAMPAIGN_SCHEDULE'
1927 THEN
1928 l_schedule_id := p_page_parameter_tbl(i).parameter_id;
1929 END IF;
1930 IF p_page_parameter_tbl(i).parameter_name= 'BIX_TELEPHONY+BIX_SOURCE_CODE'
1931 THEN
1932 l_source_code_id := p_page_parameter_tbl(i).parameter_id;
1933 END IF;
1934 IF p_page_parameter_tbl(i).parameter_name= 'ORGANIZATION+JTF_ORG_SUPPORT_GROUP' THEN
1935 l_agent_group := p_page_parameter_tbl(i).parameter_id;
1936 END IF;
1937 IF p_page_parameter_tbl(i).parameter_name= 'VIEW_BY' THEN
1938 l_view_by := p_page_parameter_tbl(i).parameter_id;
1939 END IF;
1940 END LOOP;
1941 END IF;
1942 --
1943 --First time page patch is applied, sometimes period type
1944 --is not being passed. Force it to MONTH if this happens.
1945 --If at all this happens, this will only happen the very first
1946 --time page patch is applied.
1947 --
1948 IF l_period_type IS NULL THEN
1949 ---- l_period_type := 'FII_TIME_MONTH';
1950 l_period_type := 'FII_TIME_ENT_PERIOD';
1951 END IF;
1952 --
1953 --l_period_type is used to derive the table name. It can only be
1954 --the following values. If it is not, then we will fail the SQL.
1955 --
1956
1957 IF l_period_type <> 'FII_TIME_WEEK'
1958 AND l_period_type <> 'FII_TIME_DAY'
1959 AND l_period_type <> 'FII_TIME_ENT_PERIOD'
1960 AND l_period_type <> 'FII_TIME_ENT_QTR'
1961 AND l_period_type <> 'FII_TIME_ENT_YEAR'
1962 THEN
1963 l_period_type := NULL;
1964 END IF;
1965 CASE l_period_type
1966 WHEN 'FII_TIME_WEEK' THEN l_record_type_id := 11;
1967 WHEN 'FII_TIME_ENT_PERIOD' THEN l_record_type_id := 23;
1968 WHEN 'FII_TIME_ENT_QTR' THEN l_record_type_id := 55;
1969 WHEN 'FII_TIME_ENT_YEAR' THEN l_record_type_id := 119;
1970 -- WHEN 'FII_TIME_DAY' THEN l_record_type_id := 119;
1971 ELSE l_record_type_id := null;
1972 END CASE;
1973 EXCEPTION
1974 WHEN OTHERS THEN
1975 tmp:=sqlerrm;
1976 END GET_AO_PAGE_PARAMS;
1977
1978 FUNCTION get_ao_default_page_params
1979 RETURN VARCHAR2 IS
1980 v_date VARCHAR2(11);
1981 BEGIN
1982 SELECT to_char(sysdate,'DD-MON-YYYY')
1983 INTO v_date
1984 FROM dual;
1985 RETURN '+FII_TIME_ENT_PERIOD=TIME+FII_TIME_ENT_PERIOD+SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL='||v_date;
1986 EXCEPTION
1987 WHEN OTHERS
1988 THEN
1989 RETURN '+FII_TIME_ENT_PERIOD=TIME+FII_TIME_ENT_PERIOD+
1990 SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL='||TO_CHAR(TRUNC(sysdate),'DD-MON-YYYY');
1991 END get_ao_default_page_params;
1992
1993
1994 END BIX_PMV_DBI_utl_pkg;
1995