[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_BAK_PKG
Source
1 package body biv_dbi_bak_pkg
2 /* $Header: bivsrvrbakb.pls 120.0 2005/05/25 10:58:32 appldev noship $ */
3 as
4
5 g_backlog_rep_func varchar2(50) := 'BIV_DBI_BAK_TBL_REP';
6 g_backlog_dbn_rep_func varchar2(50) := 'BIV_DBI_BAK_DBN_TBL_REP';
7 g_backlog_age_rep_func varchar2(50) := 'BIV_DBI_BAK_AGE_TBL_REP';
8 g_backlog_age_dbn_rep_func varchar2(50) := 'BIV_DBI_BAK_AGE_DBN_TBL_REP';
9
10 function drill_across
11 ( p_distribution in varchar2
12 , p_backlog_type in varchar2
13 , p_col_name in varchar2
14 , p_alias in varchar2
15 , p_view_by in varchar2
16 )
17 return varchar2
18 is
19
20 begin
21
22 if p_view_by = biv_dbi_tmpl_util.g_AGING then
23 return 'null ' || p_alias;
24 end if;
25
26 return 'case when &BIS_CURRENT_ASOF_DATE >= &LAST_COLLECTION then ' ||
27 '''pFunctionName=' || case p_distribution
28 when 'Y' then
29 g_backlog_age_dbn_rep_func
30 else
31 g_backlog_age_rep_func
32 end
33 || case p_backlog_type
34 when 'ESCALATED' then
35 '&BACKLOG_TYPE=ESCALATED'
36 when 'UNOWNED' then
37 '&BACKLOG_TYPE=UNOWNED'
38 end
39 || '&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY='
40 || case p_view_by
41 when biv_dbi_tmpl_util.g_SEVERITY then
42 biv_dbi_tmpl_util.g_STATUS
43 else
44 biv_dbi_tmpl_util.g_SEVERITY
45 end
46 || '&pParamIds=Y'' else null end '
47 || p_alias;
48 end drill_across;
49
50 function unr_drill_across
51 ( p_distribution in varchar2
52 , p_backlog_type in varchar2
53 , p_col_name in varchar2
54 , p_alias in varchar2
55 , p_view_by in varchar2
56 )
57 return varchar2
58 is
59
60 begin
61
62 if p_view_by = biv_dbi_tmpl_util.g_AGING then
63 return 'null ' || p_alias;
64 end if;
65
66 return 'case when &BIS_CURRENT_ASOF_DATE >= &LAST_COLLECTION then ' ||
67 '''pFunctionName=' || case p_distribution
68 when 'Y' then
69 g_backlog_age_dbn_rep_func
70 else
71 g_backlog_age_rep_func
72 end
73 || case p_backlog_type
74 when 'ESCALATED' then
75 '&BACKLOG_TYPE=ESCALATED'
76 when 'UNOWNED' then
77 '&BACKLOG_TYPE=UNOWNED'
78 end
79 || '&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY='
80 || case p_view_by
81 when biv_dbi_tmpl_util.g_SEVERITY then
82 biv_dbi_tmpl_util.g_STATUS
83 else
84 biv_dbi_tmpl_util.g_SEVERITY
85 end
86 || '&BIV_RES_STATUS=N&pParamIds=Y'' else null end '
87 || p_alias;
88 end unr_drill_across;
89
90 /*
91 -- Last refresh date checks
92 procedure set_last_collection
93 is
94 begin
95 poa_dbi_template_pkg.g_c_as_of_date := 'least(&BIS_CURRENT_ASOF_DATE,&LAST_COLLECTION)';
96 poa_dbi_template_pkg.g_p_as_of_date := 'least(&BIS_PREVIOUS_ASOF_DATE,&LAST_COLLECTION)';
97 end set_last_collection;
98
99 -- Last refresh date checks
100 procedure unset_last_collection
101 is
102 begin
103 poa_dbi_template_pkg.g_c_as_of_date := '&BIS_CURRENT_ASOF_DATE';
104 poa_dbi_template_pkg.g_p_as_of_date := '&BIS_PREVIOUS_ASOF_DATE';
105 end unset_last_collection;
106 */
107
108 procedure get_tbl_sql
109 ( p_param in bis_pmv_page_parameter_tbl
110 , x_custom_sql out nocopy varchar2
111 , x_custom_output out nocopy bis_query_attributes_tbl
112 )
113 is
114
115 l_view_by varchar2(200);
116 l_view_by_col_name varchar2(200);
117 l_comparison_type varchar2(200);
118 l_xtd varchar2(200);
119 l_where_clause varchar2(10000);
120 l_mv varchar2(10000);
121 l_stmt varchar2(32767);
122
123 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
124 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
125
126 l_custom_output bis_query_attributes_tbl;
127
128 l_to_date_type VARCHAR2 (3) ;
129 l_as_of_date date;
130
131 begin
132
133 -- clear out the tables.
134 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
135 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
136
137
138 biv_dbi_tmpl_util.process_parameters
139 ( p_param => p_param
140 , p_report_type => 'BACKLOG'
141 , p_trend => 'N'
142 , x_view_by => l_view_by
143 , x_view_by_col_name => l_view_by_col_name
144 , x_comparison_type => l_comparison_type
145 , x_xtd => l_xtd
146 , x_where_clause => l_where_clause
147 , x_mv => l_mv
148 , x_join_tbl => l_join_tbl
149 , x_as_of_date => l_as_of_date
150 );
151
152
153 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
154 THEN
155 l_to_date_type := 'YTD';
156 -- set_last_collection;
157 ELSE
158 l_to_date_type := 'BAL';
159 END IF;
160
161
162 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
163 , p_col_name => 'backlog_count'
164 , p_alias_name => 'backlog'
165 , p_to_date_type => l_to_date_type
166 );
167
168 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
169 , p_col_name => 'escalated_count'
170 , p_alias_name => 'escalated'
171 , p_to_date_type => l_to_date_type
172 );
173
174 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
175 , p_col_name => 'unowned_count'
176 , p_alias_name => 'unowned'
177 , p_to_date_type => l_to_date_type
178 );
179
180 l_stmt := 'select
181 ' ||
182 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
183 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
184 '
185 ' ||
186 case
187 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
188 ', v.description'
189 else
190 ', null'
191 end
192 || ' BIV_ATTRIBUTE5
193 /* Backlog Prior */
194 , nvl(oset.p_backlog,0) BIV_MEASURE1
195 /* Backlog Current */
196 , nvl(oset.c_backlog,0) BIV_MEASURE2
197 /* Backlog Change */
198 , ' ||
199 biv_dbi_tmpl_util.change_column('oset.c_backlog'
200 ,'oset.p_backlog'
201 ,'BIV_MEASURE4') ||
202 '
203 /* Escalated Prior */
204 , nvl(oset.p_escalated,0) BIV_MEASURE9
205 /* Escalated Current */
206 , nvl(oset.c_escalated,0) BIV_MEASURE10
207 /* Escalated Change */
208 , ' ||
209 biv_dbi_tmpl_util.change_column('oset.c_escalated'
210 ,'oset.p_escalated'
211 ,'BIV_MEASURE12') ||
212 '
213 /* Unowned Prior */
214 , nvl(oset.p_unowned,0) BIV_MEASURE13
215 /* Unowned Current */
216 , nvl(oset.c_unowned,0) BIV_MEASURE14
217 /* Unowned Change */
218 , ' ||
219 biv_dbi_tmpl_util.change_column('oset.c_unowned'
220 ,'oset.p_unowned'
221 ,'BIV_MEASURE16') ||
222 '
223 /* GT Backlog Current */
224 , nvl(oset.c_backlog_total,0) BIV_MEASURE17
225 /* GT Backlog Change */
226 , ' ||
227 biv_dbi_tmpl_util.change_column('oset.c_backlog_total'
228 ,'oset.p_backlog_total'
229 ,'BIV_MEASURE18') ||
230 '
231 /* GT Escalated Current */
232 , nvl(oset.c_escalated_total,0) BIV_MEASURE21
233 /* GT Escalated Change */
234 , ' ||
235 biv_dbi_tmpl_util.change_column('oset.c_escalated_total'
236 ,'oset.p_escalated_total'
237 ,'BIV_MEASURE22') ||
238 '
239 /* GT Unowned Current */
240 , nvl(oset.c_unowned_total,0) BIV_MEASURE23
241 /* GT Unowned Change */
242 , ' ||
243 biv_dbi_tmpl_util.change_column('oset.c_unowned_total'
244 ,'oset.p_unowned_total'
245 ,'BIV_MEASURE24') ||
246 '
247 , ' ||
248 biv_dbi_tmpl_util.get_category_drill_down( l_view_by, g_backlog_rep_func ) ||
249 '
250 , ' ||
251 drill_across('N','BACKLOG', 'oset.c_backlog', 'BIV_ATTRIBUTE6',l_view_by) ||
252 '
253 , ' ||
254 drill_across('N','ESCALATED', 'oset.c_escalated', 'BIV_ATTRIBUTE7',l_view_by) ||
255 '
256 , ' ||
257 drill_across('N','UNOWNED', 'oset.c_unowned', 'BIV_ATTRIBUTE8',l_view_by) ||
258 '
259 from
260 ( select * from ( ' || poa_dbi_template_pkg.status_sql
261 ( p_fact_name => l_mv
262 , p_where_clause => l_where_clause
263 , p_join_tables => l_join_tbl
264 , p_use_windowing => 'N'
265 , p_col_name => l_col_tbl
266 , p_use_grpid => 'N'
267 , p_paren_count => 3
268 , p_filter_where => '(c_backlog > 0 or p_backlog > 0)'
269 , p_generate_viewby => 'Y'
270 );
271
272 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
273
274 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
275 l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
276
277
278 x_custom_sql := l_stmt;
279 -- unset_last_collection;
280
281 poa_dbi_util_pkg.get_custom_balance_binds
282 ( p_custom_output => l_custom_output
283 , p_balance_fact => biv_dbi_tmpl_util.get_balance_fact(l_mv)
284 , p_xtd => l_xtd
285 );
286
287 x_custom_output := l_custom_output;
288
289 l_stmt := biv_dbi_tmpl_util.dump_binds(l_custom_output);
290
291 end get_tbl_sql;
292
293 procedure get_dbn_tbl_sql
294 ( p_param in bis_pmv_page_parameter_tbl
295 , x_custom_sql out nocopy varchar2
296 , x_custom_output out nocopy bis_query_attributes_tbl
297 )
298 is
299
300 l_view_by varchar2(200);
301 l_view_by_col_name varchar2(200);
302 l_comparison_type varchar2(200);
303 l_xtd varchar2(200);
304 l_where_clause varchar2(10000);
305 l_mv varchar2(10000);
306 l_stmt varchar2(32767);
307
308 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
309 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
310
311 l_custom_output bis_query_attributes_tbl;
312
313 l_to_date_type VARCHAR2 (3) ;
314 l_as_of_date date;
315
316 begin
317
318 -- clear out the tables.
319 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
320 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
321
322 biv_dbi_tmpl_util.process_parameters
323 ( p_param => p_param
324 , p_report_type => 'BACKLOG'
325 , p_trend => 'N'
326 , x_view_by => l_view_by
327 , x_view_by_col_name => l_view_by_col_name
328 , x_comparison_type => l_comparison_type
329 , x_xtd => l_xtd
330 , x_where_clause => l_where_clause
331 , x_mv => l_mv
332 , x_join_tbl => l_join_tbl
333 , x_as_of_date => l_as_of_date
334 );
335
336 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
337 THEN
338 l_to_date_type := 'YTD';
339 -- set_last_collection;
340 ELSE
341 l_to_date_type := 'BAL';
342 END IF;
343
344 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
345 , p_col_name => 'backlog_count'
346 , p_alias_name => 'backlog'
347 , p_to_date_type => l_to_date_type
348 );
349
350 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
351 , p_col_name => 'escalated_count'
352 , p_alias_name => 'escalated'
353 , p_to_date_type => l_to_date_type
354 );
355
356 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
357 , p_col_name => 'unowned_count'
358 , p_alias_name => 'unowned'
359 , p_to_date_type => l_to_date_type
360 );
361
362 l_stmt := 'select
363 ' ||
364 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
365 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
366 '
367 ' ||
368 case
369 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
370 ', v.description'
371 else
372 ', null'
373 end
374 || ' BIV_ATTRIBUTE5
375 /* Backlog Prior */
376 , nvl(oset.p_backlog,0) BIV_MEASURE1
377 /* Backlog Current */
378 , nvl(oset.c_backlog,0) BIV_MEASURE2
379 /* Backlog Change */
380 , ' ||
381 biv_dbi_tmpl_util.change_column('oset.c_backlog'
382 ,'oset.p_backlog'
383 ,'BIV_MEASURE4') ||
384 '
385 /* Percent of Total Prior */
386 , ' ||
387 biv_dbi_tmpl_util.rate_column('oset.p_backlog'
388 ,'oset.p_backlog_total'
389 ,'BIV_MEASURE5') ||
390 '
391 /* Percent of Total Current */
392 , ' ||
393 biv_dbi_tmpl_util.rate_column('oset.c_backlog'
394 ,'oset.c_backlog_total'
395 ,'BIV_MEASURE6') ||
396 '
397 /* Percent of Total Change */
398 , ' ||
399 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog'
400 ,'oset.c_backlog_total'
401 ,null)
402 ,biv_dbi_tmpl_util.rate_column('oset.p_backlog'
403 ,'oset.p_backlog_total'
404 ,null)
405 ,'BIV_MEASURE8'
406 ,'N') ||
407 '
408 /* Escalated Percent Prior */
409 , ' ||
410 biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated,0)'
411 ,'oset.p_backlog'
412 ,'BIV_MEASURE9') ||
413 '
414 /* Escalated Percent Current */
415 , ' ||
416 biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated,0)'
417 ,'oset.c_backlog'
418 ,'BIV_MEASURE10') ||
419 '
420 /* Escalated Percent Change */
421 , ' ||
422 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated,0)'
423 ,'oset.c_backlog'
424 ,null)
425 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated,0)'
426 ,'oset.p_backlog'
427 ,null)
428 ,'BIV_MEASURE12'
429 ,'N') ||
430 '
431 /* Unowned Percent Prior */
432 , ' ||
433 biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned,0)'
434 ,'oset.p_backlog'
435 ,'BIV_MEASURE13') ||
436 '
437 /* Unowned Percent Current */
438 , ' ||
439 biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned,0)'
440 ,'oset.c_backlog'
441 ,'BIV_MEASURE14') ||
442 '
443 /* Unowned Percent Change */
444 , ' ||
445 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned,0)'
446 ,'oset.c_backlog'
447 ,null)
448 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned,0)'
449 ,'oset.p_backlog'
450 ,null)
451 ,'BIV_MEASURE16'
452 ,'N') ||
453 '
454 /* GT Backlog Current */
455 , nvl(oset.c_backlog_total,0) BIV_MEASURE17
456 /* GT Backlog Change */
457 , ' ||
458 biv_dbi_tmpl_util.change_column('oset.c_backlog_total'
459 ,'oset.p_backlog_total'
460 ,'BIV_MEASURE18') ||
461 '
462 /* GT Percent of Total Current */
463 , 100 BIV_MEASURE19
464 /* GT Escalated Percent Current */
465 , ' ||
466 biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated_total,0)'
467 ,'oset.c_backlog_total'
468 ,'BIV_MEASURE21') ||
469 '
470 /* GT Escalated Percent Change */
471 , ' ||
472 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated_total,0)'
473 ,'oset.c_backlog_total'
474 ,null)
475 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated_total,0)'
476 ,'oset.p_backlog_total'
477 ,null)
478 ,'BIV_MEASURE22'
479 ,'N') ||
480 '
481 /* GT Unowned Percent Current */
482 , ' ||
483 biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned_total,0)'
484 ,'oset.c_backlog_total'
485 ,'BIV_MEASURE23') ||
486 '
487 /* GT Unowned Percent Change */
488 , ' ||
489 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned_total,0)'
490 ,'oset.c_backlog_total'
491 ,null)
492 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned_total,0)'
493 ,'oset.p_backlog_total'
494 ,null)
495 ,'BIV_MEASURE24'
496 ,'N') ||
497 '
498 /* KPI GT Backlog Prior */
499 , nvl(oset.p_backlog_total,0) BIV_MEASURE31
500 /* KPI GT Escalated Percent Prior */
501 , ' ||
502 biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated_total,0)'
503 ,'oset.p_backlog_total'
504 ,'BIV_MEASURE32') ||
505 '
506 /* KPI GT Unowned Percent Prior */
507 , ' ||
508 biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned_total,0)'
509 ,'oset.p_backlog_total'
510 ,'BIV_MEASURE33') ||
511 '
512 , ' ||
513 biv_dbi_tmpl_util.get_category_drill_down( l_view_by, g_backlog_dbn_rep_func ) ||
514 '
515 , ' ||
516 drill_across('Y','BACKLOG', 'oset.c_backlog', 'BIV_ATTRIBUTE6',l_view_by) ||
517 '
518 from
519 ( select * from ( ' || poa_dbi_template_pkg.status_sql
520 ( p_fact_name => l_mv
521 , p_where_clause => l_where_clause
522 , p_join_tables => l_join_tbl
523 , p_use_windowing => 'N'
524 , p_col_name => l_col_tbl
525 , p_use_grpid => 'N'
526 , p_paren_count => 3
527 , p_filter_where => '(c_backlog > 0 or p_backlog > 0)'
528 , p_generate_viewby => 'Y'
529 );
530
531 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
532
533 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
534 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
535
536 x_custom_sql := l_stmt;
537 -- unset_last_collection;
538
539
540 poa_dbi_util_pkg.get_custom_balance_binds
541 ( p_custom_output => l_custom_output
542 , p_balance_fact => biv_dbi_tmpl_util.get_balance_fact(l_mv)
543 , p_xtd => l_xtd
544 );
545
546 x_custom_output := l_custom_output;
547
548 end get_dbn_tbl_sql;
549
550
551 procedure get_unr_dbn_tbl_sql
552 ( p_param in bis_pmv_page_parameter_tbl
553 , x_custom_sql out nocopy varchar2
554 , x_custom_output out nocopy bis_query_attributes_tbl
555 )
556 is
557
558 l_view_by varchar2(200);
559 l_view_by_col_name varchar2(200);
560 l_comparison_type varchar2(200);
561 l_xtd varchar2(200);
562 l_where_clause varchar2(10000);
563 l_mv varchar2(10000);
564 l_stmt varchar2(32767);
565
566 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
567 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
568
569 l_custom_output bis_query_attributes_tbl;
570
571 l_to_date_type VARCHAR2 (3) ;
572 l_as_of_date date;
573
574 begin
575
576 -- clear out the tables.
577 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
578 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
579
580 biv_dbi_tmpl_util.process_parameters
581 ( p_param => p_param
582 , p_report_type => 'BACKLOG'
583 , p_trend => 'N'
584 , x_view_by => l_view_by
585 , x_view_by_col_name => l_view_by_col_name
586 , x_comparison_type => l_comparison_type
587 , x_xtd => l_xtd
588 , x_where_clause => l_where_clause
589 , x_mv => l_mv
590 , x_join_tbl => l_join_tbl
591 , x_as_of_date => l_as_of_date
592 );
593
594 l_where_clause := l_where_clause || 'and resolved_flag = ''N''';
595
596 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
597 THEN
598 l_to_date_type := 'YTD';
599 -- set_last_collection;
600 ELSE
601 l_to_date_type := 'BAL';
602 END IF;
603
604 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
605 , p_col_name => 'backlog_count'
606 , p_alias_name => 'backlog'
607 , p_to_date_type => l_to_date_type
608 );
609
610 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
611 , p_col_name => 'escalated_count'
612 , p_alias_name => 'escalated'
613 , p_to_date_type => l_to_date_type
614 );
615
616 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
617 , p_col_name => 'unowned_count'
618 , p_alias_name => 'unowned'
619 , p_to_date_type => l_to_date_type
620 );
621
622 l_stmt := 'select
623 ' ||
624 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
625 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
626 '
627 ' ||
628 case
629 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
630 ', v.description'
631 else
632 ', null'
633 end
634 || ' BIV_ATTRIBUTE5
635 /* Backlog Prior */
636 , nvl(oset.p_backlog,0) BIV_MEASURE1
637 /* Backlog Current */
638 , nvl(oset.c_backlog,0) BIV_MEASURE2
639 /* Backlog Change */
640 , ' ||
641 biv_dbi_tmpl_util.change_column('oset.c_backlog'
642 ,'oset.p_backlog'
643 ,'BIV_MEASURE4') ||
644 '
645 /* Percent of Total Prior */
646 , ' ||
647 biv_dbi_tmpl_util.rate_column('oset.p_backlog'
648 ,'oset.p_backlog_total'
649 ,'BIV_MEASURE5') ||
650 '
651 /* Percent of Total Current */
652 , ' ||
653 biv_dbi_tmpl_util.rate_column('oset.c_backlog'
654 ,'oset.c_backlog_total'
655 ,'BIV_MEASURE6') ||
656 '
657 /* Percent of Total Change */
658 , ' ||
659 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog'
660 ,'oset.c_backlog_total'
661 ,null)
662 ,biv_dbi_tmpl_util.rate_column('oset.p_backlog'
663 ,'oset.p_backlog_total'
664 ,null)
665 ,'BIV_MEASURE8'
666 ,'N') ||
667 '
668 /* Escalated Percent Prior */
669 , ' ||
670 biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated,0)'
671 ,'oset.p_backlog'
672 ,'BIV_MEASURE9') ||
673 '
674 /* Escalated Percent Current */
675 , ' ||
676 biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated,0)'
677 ,'oset.c_backlog'
678 ,'BIV_MEASURE10') ||
679 '
680 /* Escalated Percent Change */
681 , ' ||
682 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated,0)'
683 ,'oset.c_backlog'
684 ,null)
685 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated,0)'
686 ,'oset.p_backlog'
687 ,null)
688 ,'BIV_MEASURE12'
689 ,'N') ||
690 '
691 /* Unowned Percent Prior */
692 , ' ||
693 biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned,0)'
694 ,'oset.p_backlog'
695 ,'BIV_MEASURE13') ||
696 '
697 /* Unowned Percent Current */
698 , ' ||
699 biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned,0)'
700 ,'oset.c_backlog'
701 ,'BIV_MEASURE14') ||
702 '
703 /* Unowned Percent Change */
704 , ' ||
705 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned,0)'
706 ,'oset.c_backlog'
707 ,null)
708 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned,0)'
709 ,'oset.p_backlog'
710 ,null)
711 ,'BIV_MEASURE16'
712 ,'N') ||
713 '
714 /* GT Backlog Current */
715 , nvl(oset.c_backlog_total,0) BIV_MEASURE17
716 /* GT Backlog Change */
717 , ' ||
718 biv_dbi_tmpl_util.change_column('oset.c_backlog_total'
719 ,'oset.p_backlog_total'
720 ,'BIV_MEASURE18') ||
721 '
722 /* GT Percent of Total Current */
723 , 100 BIV_MEASURE19
724 /* GT Escalated Percent Current */
725 , ' ||
726 biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated_total,0)'
727 ,'oset.c_backlog_total'
728 ,'BIV_MEASURE21') ||
729 '
730 /* GT Escalated Percent Change */
731 , ' ||
732 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_escalated_total,0)'
733 ,'oset.c_backlog_total'
734 ,null)
735 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated_total,0)'
736 ,'oset.p_backlog_total'
737 ,null)
738 ,'BIV_MEASURE22'
739 ,'N') ||
740 '
741 /* GT Unowned Percent Current */
742 , ' ||
743 biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned_total,0)'
744 ,'oset.c_backlog_total'
745 ,'BIV_MEASURE23') ||
746 '
747 /* GT Unowned Percent Change */
748 , ' ||
749 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(oset.c_unowned_total,0)'
750 ,'oset.c_backlog_total'
751 ,null)
752 ,biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned_total,0)'
753 ,'oset.p_backlog_total'
754 ,null)
755 ,'BIV_MEASURE24'
756 ,'N') ||
757 '
758 /* KPI GT Backlog Prior */
759 , nvl(oset.p_backlog_total,0) BIV_MEASURE31
760 /* KPI GT Escalated Percent Prior */
761 , ' ||
762 biv_dbi_tmpl_util.rate_column('nvl(oset.p_escalated_total,0)'
763 ,'oset.p_backlog_total'
764 ,'BIV_MEASURE32') ||
765 '
766 /* KPI GT Unowned Percent Prior */
767 , ' ||
768 biv_dbi_tmpl_util.rate_column('nvl(oset.p_unowned_total,0)'
769 ,'oset.p_backlog_total'
770 ,'BIV_MEASURE33') ||
771 '
772 , ' ||
773 biv_dbi_tmpl_util.get_category_drill_down( l_view_by, g_backlog_dbn_rep_func||'@BIV_RES_STATUS=N' ) ||
774 '
775 , ' ||
776 unr_drill_across('Y','BACKLOG', 'oset.c_backlog', 'BIV_ATTRIBUTE6',l_view_by) ||
777 '
778 from
779 ( select * from ( ' || poa_dbi_template_pkg.status_sql
780 ( p_fact_name => l_mv
781 , p_where_clause => l_where_clause
782 , p_join_tables => l_join_tbl
783 , p_use_windowing => 'N'
784 , p_col_name => l_col_tbl
785 , p_use_grpid => 'N'
786 , p_paren_count => 3
787 , p_filter_where => '(c_backlog > 0 or p_backlog > 0)'
788 , p_generate_viewby => 'Y'
789 );
790
791 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
792
793 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
794 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
795
796 x_custom_sql := l_stmt;
797 -- unset_last_collection;
798
799
800 poa_dbi_util_pkg.get_custom_balance_binds
801 ( p_custom_output => l_custom_output
802 , p_balance_fact => biv_dbi_tmpl_util.get_balance_fact(l_mv)
803 , p_xtd => l_xtd
804 );
805
806 x_custom_output := l_custom_output;
807
808 end get_unr_dbn_tbl_sql;
809
810 procedure get_trd_sql
811 ( p_param in bis_pmv_page_parameter_tbl
812 , x_custom_sql out nocopy varchar2
813 , x_custom_output out nocopy bis_query_attributes_tbl
814 , p_distribution in varchar2 := 'N'
815 )
816 is
817
818 l_view_by varchar2(200);
819 l_view_by_col_name varchar2(200);
820 l_comparison_type varchar2(200);
821 l_xtd varchar2(200);
822 l_where_clause varchar2(10000);
823 l_mv varchar2(10000);
824 l_stmt varchar2(32767);
825
826 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
827 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
828
829 l_custom_output bis_query_attributes_tbl;
830
831 l_to_date_type VARCHAR2 (3) ;
832 l_as_of_date date;
833
834 begin
835
836 -- clear out the tables.
837 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
838 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
839
840
841 biv_dbi_tmpl_util.process_parameters
842 ( p_param => p_param
843 , p_report_type => 'BACKLOG'
844 , p_trend => 'Y'
845 , x_view_by => l_view_by
846 , x_view_by_col_name => l_view_by_col_name
847 , x_comparison_type => l_comparison_type
848 , x_xtd => l_xtd
849 , x_where_clause => l_where_clause
850 , x_mv => l_mv
851 , x_join_tbl => l_join_tbl
852 , x_as_of_date => l_as_of_date
853 );
854
855 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
856 THEN
857 l_to_date_type := 'YTD';
858 -- set_last_collection;
859 ELSE
860 l_to_date_type := 'BAL';
861 END IF;
862
863
864 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
865 , p_col_name => 'backlog_count'
866 , p_alias_name => 'backlog'
867 , p_to_date_type => l_to_date_type
868 , p_grand_total => 'N'
869 );
870
871 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
872 , p_col_name => 'escalated_count'
873 , p_alias_name => 'escalated'
874 , p_to_date_type => l_to_date_type
875 , p_grand_total => 'N'
876 );
877
878 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
879 , p_col_name => 'unowned_count'
880 , p_alias_name => 'unowned'
881 , p_to_date_type => l_to_date_type
882 , p_grand_total => 'N'
883 );
884
885 l_stmt := 'select
886 cal.name VIEWBY
887 /* End date of the period */
888 , cal.end_date VIEWBYID
889 /* Backlog Prior */
890 , nvl(iset.p_backlog,0) BIV_MEASURE1
891 /* Backlog Current */
892 , nvl(iset.c_backlog,0) BIV_MEASURE2
893 /* Backlog Change */
894 , ' ||
895 biv_dbi_tmpl_util.change_column('iset.c_backlog'
896 ,'iset.p_backlog'
897 ,'BIV_MEASURE4') ||
898 case
899 when p_distribution = 'N' then
900 '
901 /* Escalated Prior */
902 , nvl(iset.p_escalated,0) BIV_MEASURE9
903 /* Escalated Current */
904 , nvl(iset.c_escalated,0) BIV_MEASURE10
905 /* Escalated Change */
906 , ' ||
907 biv_dbi_tmpl_util.change_column('iset.c_escalated'
908 ,'iset.p_escalated'
909 ,'BIV_MEASURE12') ||
910 '
911 /* Unowned Prior */
912 , nvl(iset.p_unowned,0) BIV_MEASURE13
913 /* Unowned Current */
914 , nvl(iset.c_unowned,0) BIV_MEASURE14
915 /* Unowned Change */
916 , ' ||
917 biv_dbi_tmpl_util.change_column('iset.c_unowned'
918 ,'iset.p_unowned'
919 ,'BIV_MEASURE16')
920 else
921 '
922 /* Escalated Percent Prior */
923 , ' ||
924 biv_dbi_tmpl_util.rate_column('nvl(iset.p_escalated,0)'
925 ,'iset.p_backlog'
926 ,'BIV_MEASURE9') ||
927 '
928 /* Escalated Percent Current */
929 , ' ||
930 biv_dbi_tmpl_util.rate_column('nvl(iset.c_escalated,0)'
931 ,'iset.c_backlog'
932 ,'BIV_MEASURE10') ||
933 '
934 /* Escalated Percent Change */
935 , ' ||
936 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(iset.c_escalated,0)'
937 ,'iset.c_backlog'
938 ,null)
939 ,biv_dbi_tmpl_util.rate_column('nvl(iset.p_escalated,0)'
940 ,'iset.p_backlog'
941 ,null)
942 ,'BIV_MEASURE12'
943 ,'N') ||
944 '
945 /* Unowned Percent Prior */
946 , ' ||
947 biv_dbi_tmpl_util.rate_column('nvl(iset.p_unowned,0)'
948 ,'iset.p_backlog'
949 ,'BIV_MEASURE13') ||
950 '
951 /* Unowned Percent Current */
952 , ' ||
953 biv_dbi_tmpl_util.rate_column('nvl(iset.c_unowned,0)'
954 ,'iset.c_backlog'
955 ,'BIV_MEASURE14') ||
956 '
957 /* Unowned Percent Change */
958 , ' ||
959 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('nvl(iset.c_unowned,0)'
960 ,'iset.c_backlog'
961 ,null)
962 ,biv_dbi_tmpl_util.rate_column('nvl(iset.p_unowned,0)'
963 ,'iset.p_backlog'
964 ,null)
965 ,'BIV_MEASURE16'
966 ,'N')
967 end;
968
969 IF (l_xtd = 'WTD')
970 THEN
971 l_stmt := l_stmt ||','||'''AS_OF_DATE=''||to_char(cal.end_date,''dd/mm/yyyy'')||''&pFunctionName=BIV_DBI_BAK_TRD_REP&pParamIds=Y&VIEW_BY=TIME+FII_TIME_DAY&FII_TIME_DAY=FII_TIME_DAY'' BIV_DYNAMIC_URL1' || ',NULL BIV_DYNAMIC_URL2';
972 ELSIF (l_xtd = 'RLW') THEN
973 l_stmt := l_stmt ||',NULL BIV_DYNAMIC_URL1 ,'||'''AS_OF_DATE=''||to_char(cal.end_date,''dd/mm/yyyy'')||''&pFunctionName=BIV_DBI_BAK_TRD_REP&pParamIds=Y&VIEW_BY=TIME+FII_TIME_DAY&FII_TIME_DAY=FII_TIME_DAY'' BIV_DYNAMIC_URL2';
974 ELSE
975 l_stmt:= l_stmt || ', NULL BIV_DYNAMIC_URL1, NULL BIV_DYNAMIC_URL2';
976 END IF;
977
978 l_stmt := l_stmt ||'
979
980 from
981 ' || poa_dbi_template_pkg.trend_sql
982 ( p_xtd => l_xtd
983 , p_comparison_type => l_comparison_type
984 , p_fact_name => l_mv
985 , p_where_clause => l_where_clause
986 , p_col_name => l_col_tbl
987 , p_use_grpid => 'N'
988 );
989
990 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
991 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
992 -- l_stmt := l_stmt || biv_dbi_tmpl_util.get_trace_file_name;
993
994 x_custom_sql := l_stmt;
995 -- unset_last_collection;
996
997 poa_dbi_util_pkg.get_custom_trend_binds
998 ( x_custom_output => l_custom_output
999 , p_xtd => l_xtd
1000 , p_comparison_type => l_comparison_type
1001 );
1002
1003 poa_dbi_util_pkg.get_custom_balance_binds
1004 ( p_custom_output => l_custom_output
1005 , p_balance_fact => biv_dbi_tmpl_util.get_balance_fact(l_mv)
1006 , p_xtd => l_xtd
1007 );
1008
1009
1010 IF(l_xtd = 'DAY')
1011 THEN
1012 poa_dbi_util_pkg.get_custom_day_binds(p_custom_output => l_custom_output,
1013 p_as_of_date => l_as_of_date,
1014 p_comparison_type => l_comparison_type);
1015 null;
1016 END IF;
1017
1018 x_custom_output := l_custom_output;
1019
1020 l_stmt := biv_dbi_tmpl_util.dump_binds(l_custom_output);
1021
1022 end get_trd_sql;
1023
1024 procedure get_dbn_trd_sql
1025 ( p_param in bis_pmv_page_parameter_tbl
1026 , x_custom_sql out nocopy varchar2
1027 , x_custom_output out nocopy bis_query_attributes_tbl
1028 )
1029 is
1030 begin
1031 get_trd_sql
1032 ( p_param => p_param
1033 , x_custom_sql => x_custom_sql
1034 , x_custom_output => x_custom_output
1035 , p_distribution => 'Y'
1036 );
1037 end get_dbn_trd_sql;
1038
1039 end biv_dbi_bak_pkg;