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