[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_BAK_AGE_PKG
Source
1 package body biv_dbi_bak_age_pkg
2 /* $Header: bivsrvrbagb.pls 120.0 2005/05/25 10:51:27 appldev noship $ */
3 as
4
5 g_backlog_age_rep_func varchar2(50) := 'BIV_DBI_BAK_AGE_TBL_REP';
6 g_backlog_age_dbn_rep_func varchar2(50) := 'BIV_DBI_BAK_AGE_DBN_TBL_REP';
7
8 g_backlog_detail_rep_func varchar2(50) := 'BIV_DBI_BAK_DTL_REP';
9
10 -- for balance
11 g_c_aging_as_of_date_balance constant varchar2(60) := '&AGE_CURRENT_ASOF_DATE';
12 g_p_aging_as_of_date_balance constant varchar2(60) := '&AGE_PREVIOUS_ASOF_DATE';
13 g_inlist_bal constant number := 16; -- Bit 4
14 /*
15 -- Last refresh date checks
16 procedure set_last_collection
17 is
18 begin
19 poa_dbi_template_pkg.g_c_as_of_date := 'least(&BIS_CURRENT_ASOF_DATE,&LAST_COLLECTION)';
20 poa_dbi_template_pkg.g_p_as_of_date := 'least(&BIS_PREVIOUS_ASOF_DATE,&LAST_COLLECTION)';
21 end set_last_collection;
22
23 -- Last refresh date checks
24 procedure unset_last_collection
25 is
26 begin
27 poa_dbi_template_pkg.g_c_as_of_date := '&BIS_CURRENT_ASOF_DATE';
28 poa_dbi_template_pkg.g_p_as_of_date := '&BIS_PREVIOUS_ASOF_DATE';
29 end unset_last_collection;
30 */
31
32
33 FUNCTION status_sql (
34 p_fact_name IN VARCHAR2
35 , p_where_clause IN VARCHAR2
36 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
37 , p_use_windowing IN VARCHAR2
38 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
39 , p_filter_where IN VARCHAR2 := NULL
40 , p_generate_viewby IN VARCHAR2 := 'Y'
41 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL)
42 RETURN VARCHAR2
43 IS
44 l_query VARCHAR2 (10000);
45 l_col_names VARCHAR2 (10000);
46 l_group_and_sel_clause VARCHAR2 (10000);
47 l_from_clause VARCHAR2 (10000);
48 l_where_clause VARCHAR2 (10000);
49 l_c_calc_end_date VARCHAR2 (70);
50 l_p_calc_end_date VARCHAR2 (70);
51 l_inlist VARCHAR2 (300);
52 l_inlist_bmap NUMBER := 0;
53 l_viewby_rank_where VARCHAR2 (1000);
54 l_in_join_tables VARCHAR2 (240) := '';
55 l_filter_where VARCHAR2 (1000);
56
57 BEGIN
58 l_group_and_sel_clause := ' fact.' || p_join_tables (1).fact_column;
59
60 FOR i IN 2 .. p_join_tables.COUNT
61 LOOP
62 l_group_and_sel_clause := l_group_and_sel_clause || ', fact.' || p_join_tables (i).fact_column;
63 END LOOP;
64
65 IF(p_in_join_tables is not null) then
66
67 FOR i in 1 .. p_in_join_tables.COUNT
68 LOOP
69 l_in_join_tables := l_in_join_tables || ', ' || p_in_join_tables(i).table_name || ' ' || p_in_join_tables(i).table_alias;
70 END LOOP;
71
72 END IF;
73
74 -- Bind the end date variables to the BIV aging balance '&XXX' values
75 l_c_calc_end_date := g_c_aging_as_of_date_balance;
76 l_p_calc_end_date := g_p_aging_as_of_date_balance;
77 l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
78 , g_inlist_bal);
79
80 FOR i IN 1 .. p_col_name.COUNT
81 LOOP
82
83 -- Regular current column
84 l_col_names :=
85 l_col_names
86 || ', sum(decode(fact.report_date, '
87 || l_c_calc_end_date
88 || ','
89 || p_col_name (i).column_name
90 || ', null)) c_'
91 || p_col_name (i).column_alias
92 || '
93 ';
94
95 -- Prev column (based on prior_code)
96 IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
97 THEN
98 l_col_names :=
99 l_col_names
100 || ', sum(decode(fact.report_date, '
101 || l_p_calc_end_date
102 || ','
103 || p_col_name (i).column_name
104 || ', null)) p_'
105 || p_col_name (i).column_alias
106 || '
107 ';
108 END IF;
109
110 -- If grand total is flagged, do current and prior grand totals
111 IF (p_col_name (i).grand_total = 'Y')
112 THEN
113 -- Sum of current column
114 l_col_names :=
115 l_col_names
116 || ', sum(sum(decode(fact.report_date, '
117 || l_c_calc_end_date
118 || ', '
119 || p_col_name (i).column_name
120 || ', null))) over () c_'
121 || p_col_name (i).column_alias
122 || '_total
123 ';
124
125 -- Sum of prev column
126 l_col_names :=
127 l_col_names
128 || ', sum(sum(decode(fact.report_date, '
129 || l_p_calc_end_date
130 || ', '
131 || p_col_name (i).column_name
132 || ', null))) over () p_'
133 || p_col_name (i).column_alias
134 || '_total
135 ';
136
137 END IF;
138 END LOOP;
139
140 l_inlist :=
141 '('
142 || g_c_aging_as_of_date_balance
143 || ',' || g_p_aging_as_of_date_balance
144 || ')';
145
146
147 IF p_filter_where is not null
148 THEN
149 l_filter_where := ' where ' || p_filter_where;
150 END IF;
151
152 l_query :=
153 '(select '
154 || l_group_and_sel_clause
155 || l_col_names
156 || '
157 from '
158 || p_fact_name
159 || ' fact
160 where fact.report_date in '
161 || l_inlist
162 || p_where_clause
163 || '
164 group by '
165 || l_group_and_sel_clause
166 || ' ) ) ' || l_filter_where || ' ) oset ';
167
168 IF(p_generate_viewby = 'Y')
169 THEN
170 l_viewby_rank_where := ','||
171 poa_dbi_template_pkg.get_viewby_rank_clause (
172 p_join_tables => p_join_tables
173 , p_use_windowing => p_use_windowing);
174 END IF;
175
176 l_query := l_query || l_viewby_rank_where;
177
178 RETURN l_query;
179
180 END status_sql;
181
182
183
184 FUNCTION get_calendar_table (p_xtd IN VARCHAR2) return varchar2
185 IS
186 l_report_start_date date;
187 l_as_of_date date;
188 BEGIN
189
190 IF(p_xtd like 'RL%') THEN
191
192 return '( select start_date, end_date, end_date report_date, to_char(end_date,''dd-Mon-yy'') name, ordinal from '
193 || '( select distinct '
194 || 'decode(t.current_ind, 2, &BIV_PREV_EFFEC_END_DATE'
195 || ', 4, (&BIV_CURR_EFFEC_START_DATE - 1)'
196 || ', &BIV_CURR_EFFEC_END_DATE)+(t.offset*&RLX_DAYS) '
197 || '- &RLX_DAYS_TO_START start_date'
198 || ', decode(t.current_ind, 2, &BIV_PREV_EFFEC_END_DATE'
199 || ', 4, (&BIV_CURR_EFFEC_START_DATE - 1)'
200 || ', &BIV_CURR_EFFEC_END_DATE)+(t.offset*&RLX_DAYS) end_date'
201 || ', decode(&BIS_TIME_COMPARISON_TYPE,''SEQUENTIAL'',-1,decode(t.current_ind,4,0,2,1,2)) ordinal '
202 || 'from biv_trend_rpt t '
203 || 'where t.offset > &RLX_ROWS_OFFSET '
204 || 'and current_ind = 1'
205 || ' )'
206 || ' )';
207 ELSE
208
209 l_as_of_date:=get_last_refresh_date('BIV_B_AGE_H_SUM_MV');
210 l_report_start_date:= current_report_start_date(l_as_of_date,p_xtd);
211
212 return '( select start_date, end_date, end_date report_date, name
213 from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd) || '
214 where start_date between to_date('''||l_report_start_date ||''', ''dd-mon-yyyy'') and &BIV_LAST_REFRESH_DATE )';
215
216 END IF;
217 END get_calendar_table;
218
219
220
221 procedure get_age_binds
222 ( p_period_type in varchar2
223 , p_comparison_type in varchar2
224 , p_custom_output in out nocopy bis_query_attributes_tbl
225 )
226 is
227
228 l_lag number;
229 l_curr_pattern number;
230 l_prev_pattern number;
231 l_current_date date;
232 l_prior_date date;
233 l_current_start_date date;
234 last_refresh_date date;
235 l_custom_rec BIS_QUERY_ATTRIBUTES;
236
237 begin
238
239 select max(trunc(report_date))
240 into l_current_date
241 from biv_dbi_backlog_age_dates;
242
243 if(p_comparison_type = 'S') then
244 l_lag:= 1;
245 case p_period_type
246 when 'RLY' then
247 begin
248 l_curr_pattern:= 16;
249 l_prev_pattern:= 256;
250 l_current_start_date:=FII_TIME_API.ryr_start(l_current_date);
251 l_prior_date := l_current_date - 365;
252 end;
253
254 when 'YTD' then
255 begin
256 l_curr_pattern:= 131072;
257 l_prev_pattern:= 4194304;
258 l_current_start_date:=FII_TIME_API.ryr_start(l_current_date);
259 l_prior_date := l_current_date - 365;
260 end;
261
262
263 when 'RLQ' then
264 begin
265 l_curr_pattern:= 8;
266 l_prev_pattern:= 128;
267 l_current_start_date:=FII_TIME_API.rqtr_start(l_current_date);
268 l_prior_date := l_current_date - 90;
269 end;
270
271 when 'QTD' then
272 begin
273 l_curr_pattern:= 65536;
274 l_prev_pattern:= 2097152;
275 l_current_start_date:=FII_TIME_API.rqtr_start(l_current_date);
276 l_prior_date := l_current_date - 90;
277 end;
278
279 when 'RLM' then
280 begin
281 l_curr_pattern:= 4;
282 l_prev_pattern:= 64;
283 l_current_start_date:=FII_TIME_API.rmth_start(l_current_date);
284 l_prior_date := l_current_date - 30;
285 end;
286
287 when 'MTD' then
288 begin
289 l_curr_pattern:= 32768;
290 l_prev_pattern:= 1048576;
291 l_current_start_date:=FII_TIME_API.rmth_start(l_current_date);
292 l_prior_date := l_current_date - 30;
293 end;
294
295 when 'RLW' then
296 begin
297 l_curr_pattern:= 2;
298 l_prev_pattern:= 32;
299 l_current_start_date:=FII_TIME_API.rwk_start(l_current_date);
300 l_prior_date := l_current_date - 7;
301 end;
302
303 when 'WTD' then
304 begin
305 l_curr_pattern:= 16384;
306 l_prev_pattern:= 524288;
307 l_current_start_date:=FII_TIME_API.rwk_start(l_current_date);
308 l_prior_date := l_current_date - 7;
309 end;
310
311 when 'DAY' then
312 begin
313 l_curr_pattern:= 8192;
314 l_prev_pattern:= 262144;
315 l_current_start_date:=l_current_date;
316 l_prior_date := l_current_date - 1;
317 end;
318
319 end case;
320 else
321 l_prior_date := add_months(l_current_date,-12);
322 case p_period_type
323 when 'RLY' then
324 begin
325 l_lag:= 1;
326 l_curr_pattern:= 16;
327 l_prev_pattern:= 4096;
328 l_current_start_date:=FII_TIME_API.ryr_start(l_current_date);
329 end;
330
331 when 'YTD' then
332 begin
333 l_lag:= 1;
334 l_curr_pattern:= 131072;
335 l_prev_pattern:= 134217728;
336 l_current_start_date:=FII_TIME_API.ryr_start(l_current_date);
337 end;
338
339
340 when 'RLQ' then
341 begin
342 l_lag:= 8;
343 l_curr_pattern:= 8;
344 l_prev_pattern:= 2048;
345 l_current_start_date:=FII_TIME_API.rqtr_start(l_current_date);
346 end;
347
348 when 'QTD' then
349 begin
350 l_lag:= 8;
351 l_curr_pattern:= 65536;
352 l_prev_pattern:= 67108864;
353 l_current_start_date:=FII_TIME_API.rqtr_start(l_current_date);
354 end;
355
356 when 'RLM' then
357 begin
358 l_lag:= 12;
359 l_curr_pattern:= 4;
360 l_prev_pattern:= 1024;
361 l_current_start_date:=FII_TIME_API.rmth_start(l_current_date);
362 end;
363
364 when 'MTD' then
365 begin
366 l_lag:= 12;
367 l_curr_pattern:= 32768;
368 l_prev_pattern:= 33554432;
369 l_current_start_date:=FII_TIME_API.rmth_start(l_current_date);
370 end;
371
372 when 'RLW' then
373 begin
374 l_lag:= 13;
375 l_curr_pattern:= 2;
376 l_prev_pattern:= 512;
377 l_current_start_date:=FII_TIME_API.rwk_start(l_current_date);
378 end;
379
380 when 'WTD' then
381 begin
382 l_lag:= 13;
383 l_curr_pattern:= 16384;
384 l_prev_pattern:= 16777216;
385 l_current_start_date:=FII_TIME_API.rwk_start(l_current_date);
386 end;
387
388 when 'DAY' then
389 begin
390 l_lag:= 7;
391 l_curr_pattern:= 8192;
392 l_prev_pattern:= 8388608;
393 l_current_start_date:=l_current_date;
394 end;
395
396 end case;
397 end if;
398
399 last_refresh_date := get_last_refresh_date('BIV_B_AGE_H_SUM_MV');
400
401 if p_custom_output is null then
402 p_custom_output := bis_query_attributes_tbl();
403 end if;
404
405 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
406
407 l_custom_rec.attribute_name := '&BIV_AGE_LAG';
408 l_custom_rec.attribute_value := l_lag;
409 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
410 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
411 p_custom_output.extend;
412 p_custom_output(p_custom_output.count) := l_custom_rec;
413
414 l_custom_rec.attribute_name := '&BIV_CURR_PATTERN';
415 l_custom_rec.attribute_value := l_curr_pattern;
416 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
417 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
418 p_custom_output.extend;
419 p_custom_output(p_custom_output.count) := l_custom_rec;
420
421 l_custom_rec.attribute_name := '&BIV_PREV_PATTERN';
422 l_custom_rec.attribute_value := l_prev_pattern;
423 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
424 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
425 p_custom_output.extend;
426 p_custom_output(p_custom_output.count) := l_custom_rec;
427
428 l_custom_rec.attribute_name := '&BIV_CURR_EFFEC_START_DATE';
429 l_custom_rec.attribute_value := to_char(fnd_date.displayDT_to_date(l_current_start_date),'dd/mm/yyyy');
430 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
431 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
432 p_custom_output.extend;
433 p_custom_output(p_custom_output.count) := l_custom_rec;
434
435 l_custom_rec.attribute_name := '&BIV_CURR_EFFEC_END_DATE';
436 l_custom_rec.attribute_value := to_char(fnd_date.displayDT_to_date(l_current_date),'dd/mm/yyyy');
437 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
438 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
439 p_custom_output.extend;
440 p_custom_output(p_custom_output.count) := l_custom_rec;
441
442 l_custom_rec.attribute_name := '&BIV_PREV_EFFEC_END_DATE';
443 l_custom_rec.attribute_value := to_char(fnd_date.displayDT_to_date(l_prior_date),'dd/mm/yyyy');
444 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
445 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
446 p_custom_output.extend;
447 p_custom_output(p_custom_output.count) := l_custom_rec;
448
449 l_custom_rec.attribute_name := '&BIV_LAST_REFRESH_DATE';
450 l_custom_rec.attribute_value := to_char(fnd_date.displayDT_to_date(last_refresh_date),'dd/mm/yyyy');
451 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
452 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
453 p_custom_output.extend;
454 p_custom_output(p_custom_output.count) := l_custom_rec;
455
456
457 end get_age_binds;
458
459
460
461 FUNCTION trend_sql (
462 p_xtd IN VARCHAR2
463 , p_fact_name IN VARCHAR2
464 , p_where_clause IN VARCHAR2
465 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl)
466 RETURN VARCHAR2
467 IS
468 l_query VARCHAR2 (10000);
469 l_col_names VARCHAR2 (4000);
470 BEGIN
471
472
473 FOR i IN 1 .. p_col_name.COUNT
474 LOOP
475
476 -- Regular current column
477 l_col_names :=
478 l_col_names
479 || ', sum(decode(Cur, ''Y'', '
480 || p_col_name (i).column_name
481 || ', null)) c_'
482 || p_col_name (i).column_alias
483 || '
484 ';
485
486 -- Prev column (based on prior_code)
487 IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
488 THEN
489 l_col_names :=
490 l_col_names
491 || ', lag(sum(decode(Pri, ''Y'', '
492 || p_col_name (i).column_name
493 || ', null)), &BIV_AGE_LAG) over (order by cur, fact.REPORT_DATE) p_'
494 || p_col_name (i).column_alias
495 || '
496 ';
497 END IF;
498
499 END LOOP;
500
501 -- changes included for bug 4133825
502 if (p_xtd in ('YTD','QTD','MTD','WTD'))
503 then
504 l_query :=
505 '(select
506 CASE when fact.report_date = &BIV_CURR_EFFEC_START_DATE then &BIV_CURR_EFFEC_END_DATE else fact.REPORT_DATE END report_date
507 ' ||'
508 , cur, pri '
509 || l_col_names
510 || '
511 from '
512 || p_fact_name
513 || ' fact ,
514 '
515 || '( '
516 || ' select trunc(report_date) report_date'
517 || ' , decode(bitand(record_type_id, &BIV_CURR_PATTERN), &BIV_CURR_PATTERN,''Y'',''N'') Cur '
518 || ' , decode(bitand(record_type_id, &BIV_PREV_PATTERN), &BIV_PREV_PATTERN,''Y'',''N'') Pri '
519 || ' from biv_dbi_backlog_age_dates '
520 || ' where '
521 || ' bitand(record_type_id, &BIV_CURR_PATTERN)= &BIV_CURR_PATTERN /* current */ '
522 || ' or bitand(record_type_id, &BIV_PREV_PATTERN)= &BIV_PREV_PATTERN /* prior */ '
523 || ' ) join_dates '
524 || '
525 where'
526 || ' fact.report_date = join_dates.report_date
527 '
528 || p_where_clause
529 || '
530 group by '
531 || 'fact.REPORT_DATE, cur, pri '
532 || ') iset '
533 || ',
534 '
535 || get_calendar_table(p_xtd) || 'cal
536 '
537 ||'where iset.report_date BETWEEN cal.START_DATE AND cal.end_Date'
538 ||' GROUP BY cal.report_date )iset,'
539
540 || get_calendar_table(p_xtd)
541 || ' cal
542 '
543 || ' where cal.end_date = iset.report_date(+) '
544 || ' order by cal.end_date';
545 else
546 -- changes included for bug 4133825
547
548 l_query :=
549 '(select '
550 || 'fact.REPORT_DATE , cur, pri '
551 || l_col_names
552 || '
553 from '
554 || p_fact_name
555 || ' fact ,
556 '
557 || '( '
558 || ' select trunc(report_date) report_date'
559 || ' , decode(bitand(record_type_id, &BIV_CURR_PATTERN), &BIV_CURR_PATTERN,''Y'',''N'') Cur '
560 || ' , decode(bitand(record_type_id, &BIV_PREV_PATTERN), &BIV_PREV_PATTERN,''Y'',''N'') Pri '
561 || ' from biv_dbi_backlog_age_dates '
562 || ' where '
563 || ' bitand(record_type_id, &BIV_CURR_PATTERN)= &BIV_CURR_PATTERN /* current */ '
564 || ' or bitand(record_type_id, &BIV_PREV_PATTERN)= &BIV_PREV_PATTERN /* prior */ '
565 || ' ) join_dates '
566 || '
567 where'
568 || ' fact.report_date = join_dates.report_date
569 '
570 || p_where_clause
571 || '
572 group by '
573 || 'fact.REPORT_DATE, cur, pri '
574 || ') iset '
575 || ',
576 '
577 || get_calendar_table(p_xtd)
578 || ' cal
579 '
580 || ' where cal.end_date = iset.report_date(+) '
581 || ' order by cal.end_date';
582
583 end if;
584 RETURN l_query;
585
586
587 END trend_sql;
588
589
590
591 procedure get_tbl_sql
592 ( p_param in bis_pmv_page_parameter_tbl
593 , x_custom_sql out nocopy varchar2
594 , x_custom_output out nocopy bis_query_attributes_tbl
595 , p_distribution in varchar2 := 'N'
596 )
597 is
598
599 l_view_by varchar2(200);
600 l_view_by_col_name varchar2(200);
601 l_comparison_type varchar2(200);
602 l_xtd varchar2(200);
603 l_where_clause varchar2(10000);
604 l_mv varchar2(10000);
605 l_stmt varchar2(32767);
606 l_backlog_type varchar2(100);
607
608 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
609
610 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
611 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
612
613 l_custom_output bis_query_attributes_tbl;
614
615 l_to_date_type VARCHAR2 (3) ;
616 l_as_of_date date;
617
618 begin
619
620 -- clear out the tables.
621 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
622 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
623
624 biv_dbi_tmpl_util.process_parameters
625 ( p_param => p_param
626 , p_report_type => 'BACKLOG_AGE'
627 , p_trend => 'N'
628 , x_view_by => l_view_by
629 , x_view_by_col_name => l_view_by_col_name
630 , x_comparison_type => l_comparison_type
631 , x_xtd => l_xtd
632 , x_where_clause => l_where_clause
633 , x_mv => l_mv
634 , x_join_tbl => l_join_tbl
635 , x_as_of_date => l_as_of_date
636 );
637
638 l_backlog_type := biv_dbi_tmpl_util.get_backlog_type(p_param);
639
640 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
641 THEN
642 l_to_date_type := 'YTD';
643 -- set_last_collection;
644 ELSE
645 l_to_date_type := 'BAL';
646 END IF;
647
648
649 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
650 , p_col_name => case l_backlog_type
651 when 'ESCALATED' then 'escalated_count'
652 when 'UNOWNED' then 'unowned_count'
653 else 'backlog_count'
654 end
655 , p_alias_name => 'backlog'
656 , p_to_date_type => l_to_date_type
657 );
658
659 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
660 , p_col_name => case l_backlog_type
661 when 'ESCALATED' then 'total_escalated_age'
662 when 'UNOWNED' then 'total_unowned_age'
663 else 'total_backlog_age'
664 end
665 , p_alias_name => 'backlog_age'
666 , p_to_date_type => l_to_date_type
667 );
668
669 biv_dbi_tmpl_util.add_bucket_inner_query
670 ( p_short_name => 'BIV_DBI_BACKLOG_AGING'
671 , p_col_tbl => l_col_tbl
672 , p_col_name => case l_backlog_type
673 when 'ESCALATED' then 'escalated_age'
674 when 'UNOWNED' then 'unowned_age'
675 else 'backlog_age'
676 end
677 , p_alias_name => 'age_bucket'
678 , p_grand_total => 'Y'
679 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
680 , p_to_date_type => 'BAL'
681 , x_bucket_rec => l_bucket_rec
682 );
683
684 l_stmt := 'select
685 ' ||
686 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
687 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
688 case
689 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
690 ', v.description'
691 else
692 ', null'
693 end
694 || ' BIV_ATTRIBUTE5
695 /* Backlog Prior */
696 , nvl(oset.p_backlog,0) BIV_MEASURE1
697 /* Backlog Current */
698 , nvl(oset.c_backlog,0) BIV_MEASURE2
699 /* Backlog Change */
700 , ' ||
701 biv_dbi_tmpl_util.change_column('oset.c_backlog'
702 ,'oset.p_backlog'
703 ,'BIV_MEASURE4') ||
704 '
705 /* Average Age Prior */
706 , ' ||
707 biv_dbi_tmpl_util.rate_column('oset.p_backlog_age'
708 ,'oset.p_backlog'
709 ,'BIV_MEASURE5'
710 ,'N') ||
711 '
712 /* Average Age Current */
713 , ' ||
714 biv_dbi_tmpl_util.rate_column('oset.c_backlog_age'
715 ,'oset.c_backlog'
716 ,'BIV_MEASURE6'
717 ,'N') ||
718 '
719 /* Average Age Change */
720 , ' ||
721 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog_age'
722 ,'oset.c_backlog'
723 ,null
724 ,'N')
725 ,biv_dbi_tmpl_util.rate_column('oset.p_backlog_age'
726 ,'oset.p_backlog'
727 ,null
728 ,'N')
729 ,'BIV_MEASURE8'
730 ,'N') ||
731 '
732 /* Aging Buckets */
733 ' || biv_dbi_tmpl_util.get_bucket_outer_query
734 ( p_bucket_rec => l_bucket_rec
735 , p_column_name_base => 'oset.c_age_bucket'
736 , p_alias_base => 'BIV_MEASURE10'
737 , p_total_flag => 'N'
738 , p_backlog_col => case
739 when p_distribution = 'Y' then 'oset.c_backlog'
740 else null
741 end
742 ) ||
743 '
744 /* GT Backlog Current */
745 , nvl(oset.c_backlog_total,0) BIV_MEASURE11
746 /* GT Backlog Change */
747 , ' ||
748 biv_dbi_tmpl_util.change_column('oset.c_backlog_total'
749 ,'oset.p_backlog_total'
750 ,'BIV_MEASURE12') ||
751 '
752 /* GT Average Age Current */
753 , ' ||
754 biv_dbi_tmpl_util.rate_column('oset.c_backlog_age_total'
755 ,'oset.c_backlog_total'
756 ,'BIV_MEASURE13'
757 ,'N') ||
758 '
759 /* GT Average Age Change */
760 , ' ||
761 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog_age_total'
762 ,'oset.c_backlog_total'
763 ,null
764 ,'N')
765 ,biv_dbi_tmpl_util.rate_column('oset.p_backlog_age_total'
766 ,'oset.p_backlog_total'
767 ,null
768 ,'N')
769 ,'BIV_MEASURE14'
770 ,'N') ||
771 '
772 /* GT Aging Buckets */
773 ' || biv_dbi_tmpl_util.get_bucket_outer_query
774 ( p_bucket_rec => l_bucket_rec
775 , p_column_name_base => 'oset.c_age_bucket'
776 , p_alias_base => 'BIV_MEASURE15'
777 , p_total_flag => 'Y'
778 , p_backlog_col => case
779 when p_distribution = 'Y' then 'oset.c_backlog'
780 else null
781 end
782 ) ||
783 '
784 , ' ||
785 biv_dbi_tmpl_util.get_category_drill_down(l_view_by, case
786 when p_distribution = 'N' then
787 g_backlog_age_rep_func
788 else
789 g_backlog_age_dbn_rep_func
790 end) ||
791 biv_dbi_tmpl_util.drill_detail( g_backlog_detail_rep_func
792 , 0
793 , null
794 , 'BIV_ATTRIBUTE6') ||
795 case
796 when p_distribution = 'N' then
797 biv_dbi_tmpl_util.bucket_detail_drill( g_backlog_detail_rep_func
798 , l_bucket_rec
799 , 'BIV_ATTRIBUTE7' )
800 else
801 null
802 end ||
803 '
804 from
805 ( select * from ( ' || status_sql
806 ( p_fact_name => l_mv
807 , p_where_clause => l_where_clause
808 , p_join_tables => l_join_tbl
809 , p_use_windowing => 'N'
810 , p_col_name => l_col_tbl
811 , p_filter_where => '(c_backlog > 0 or p_backlog > 0)'
812 , p_generate_viewby => 'Y'
813 );
814 -- unset_last_collection;
815
816 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
817
818 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
819 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
820
821 x_custom_sql := l_stmt;
822
823 biv_dbi_tmpl_util.bind_age_dates
824 ( p_param => p_param
825 , p_current_name => '&AGE_CURRENT_ASOF_DATE' -- these will be the text you actually use in your sql stmt.
826 , p_prior_name => '&AGE_PREVIOUS_ASOF_DATE' --
827 , p_custom_output => l_custom_output );
828
829
830 x_custom_output := l_custom_output;
831
832 end get_tbl_sql;
833
834
835 procedure get_dbn_tbl_sql
836 ( p_param in bis_pmv_page_parameter_tbl
837 , x_custom_sql out nocopy varchar2
838 , x_custom_output out nocopy bis_query_attributes_tbl
839 )
840 is
841 begin
842 get_tbl_sql
843 ( p_param => p_param
844 , x_custom_sql => x_custom_sql
845 , x_custom_output => x_custom_output
846 , p_distribution => 'Y'
847 );
848 end get_dbn_tbl_sql;
849
850
851
852 procedure get_trd_sql
853 ( p_param in bis_pmv_page_parameter_tbl
854 , x_custom_sql out nocopy varchar2
855 , x_custom_output out nocopy bis_query_attributes_tbl
856 , p_distribution in varchar2 := 'N'
857 )
858 is
859
860 l_view_by varchar2(200);
861 l_view_by_col_name varchar2(200);
862 l_comparison_type varchar2(200);
863 l_xtd varchar2(200);
864 l_where_clause varchar2(10000);
865 l_mv varchar2(10000);
866 l_stmt varchar2(32767);
867 l_backlog_type varchar2(100);
868 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
869 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
870 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
871 l_custom_output bis_query_attributes_tbl;
872
873 l_to_date_type VARCHAR2 (3) ;
874 l_as_of_date date;
875
876 l_temp_xtd varchar2(200); -- RAVI Temp Sol
877 begin
878
879 -- clear out the tables.
880 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
881 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
882
883 biv_dbi_tmpl_util.process_parameters
884 ( p_param => p_param
885 , p_report_type => 'BACKLOG_AGE'
886 , p_trend => 'Y'
887 , x_view_by => l_view_by
888 , x_view_by_col_name => l_view_by_col_name
889 , x_comparison_type => l_comparison_type
890 , x_xtd => l_xtd
891 , x_where_clause => l_where_clause
892 , x_mv => l_mv
893 , x_join_tbl => l_join_tbl
894 , x_as_of_date => l_as_of_date
895 );
896
897 l_backlog_type := biv_dbi_tmpl_util.get_backlog_type(p_param);
898
899 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
900 THEN
901 l_to_date_type := 'YTD';
902 -- set_last_collection;
903 ELSE
904 l_to_date_type := 'BAL';
905 END IF;
906
907 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
908 , p_col_name => case l_backlog_type
909 when 'ESCALATED' then 'escalated_count'
910 when 'UNOWNED' then 'unowned_count'
911 else 'backlog_count'
912 end
913 , p_alias_name => 'backlog'
914 , p_to_date_type => l_to_date_type
915 , p_grand_total => 'N'
916 );
917
918 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
919 , p_col_name => case l_backlog_type
920 when 'ESCALATED' then 'total_escalated_age'
921 when 'UNOWNED' then 'total_unowned_age'
922 else 'total_backlog_age'
923 end
924 , p_alias_name => 'backlog_age'
925 , p_to_date_type => l_to_date_type
926 , p_grand_total => 'N'
927 );
928
929 biv_dbi_tmpl_util.add_bucket_inner_query
930 ( p_short_name => 'BIV_DBI_BACKLOG_AGING'
931 , p_col_tbl => l_col_tbl
932 , p_col_name => case l_backlog_type
933 when 'ESCALATED' then 'escalated_age'
934 when 'UNOWNED' then 'unowned_age'
935 else 'backlog_age'
936 end
937 , p_alias_name => 'age_bucket'
938 , p_grand_total => 'N'
939 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
940 , p_to_date_type => 'BAL'
941 , x_bucket_rec => l_bucket_rec
942 );
943
944 l_stmt := 'select
945 cal.name VIEWBY
946 /* Backlog Prior */
947 , nvl(iset.p_backlog,0) BIV_MEASURE1
948 /* Backlog Current */
949 , nvl(iset.c_backlog,0) BIV_MEASURE2
950 /* Backlog Change */
951 , ' ||
952 biv_dbi_tmpl_util.change_column('iset.c_backlog'
953 ,'iset.p_backlog'
954 ,'BIV_MEASURE4') ||
955 '
956 /* Average Age Prior */
957 , ' ||
958 biv_dbi_tmpl_util.rate_column('iset.p_backlog_age'
959 ,'iset.p_backlog'
960 ,'BIV_MEASURE5'
961 ,'N') ||
962 '
963 /* Average Age Current */
964 , ' ||
965 biv_dbi_tmpl_util.rate_column('iset.c_backlog_age'
966 ,'iset.c_backlog'
967 ,'BIV_MEASURE6'
968 ,'N') ||
969 '
970 /* Average Age Change */
971 , ' ||
972 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('iset.c_backlog_age'
973 ,'iset.c_backlog'
974 ,null
975 ,'N')
976 ,biv_dbi_tmpl_util.rate_column('iset.p_backlog_age'
977 ,'iset.p_backlog'
978 ,null
979 ,'N')
980 ,'BIV_MEASURE8'
981 ,'N') ||
982 '
983 /* Aging Buckets */
984 ' || biv_dbi_tmpl_util.get_bucket_outer_query
985 ( p_bucket_rec => l_bucket_rec
986 , p_column_name_base => 'iset.c_age_bucket'
987 , p_alias_base => 'BIV_MEASURE10'
988 , p_total_flag => 'N'
989 , p_backlog_col => case
990 when p_distribution = 'Y' then 'iset.c_backlog'
991 else null
992 end
993 )
994
995 || ', NULL BIV_DYNAMIC_URL1, NULL BIV_DYNAMIC_URL2';
996
997
998 --changes included for bug 4133825
999 IF(l_xtd IN ('WTD','MTD','QTD','YTD')) then
1000 l_stmt:= l_stmt || '
1001 FROM (
1002 SELECT cal.report_date,
1003 sum(c_backlog) c_backlog,
1004 sum(p_backlog) p_backlog,
1005 sum(c_backlog_age) c_backlog_age,
1006 sum(p_backlog_age) p_backlog_age,
1007 sum(c_age_bucket_b1) c_age_bucket_b1,
1008 sum(c_age_bucket_b2) c_age_bucket_b2,
1009 sum(c_age_bucket_b3) c_age_bucket_b3,
1010 sum(c_age_bucket_b4) c_age_bucket_b4,
1011 sum(c_age_bucket_b5) c_age_bucket_b5
1012 ';
1013 end if;
1014 --changes included for bug 4133825
1015
1016 l_stmt := l_stmt || '
1017 from
1018 ' || trend_sql
1019 ( p_xtd => l_xtd
1020 , p_fact_name => l_mv
1021 , p_where_clause => l_where_clause
1022 , p_col_name => l_col_tbl
1023 );
1024 -- unset_last_collection;
1025
1026 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1027 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
1028 -- l_stmt := l_stmt || biv_dbi_tmpl_util.get_trace_file_name;
1029
1030 x_custom_sql := l_stmt;
1031
1032 -- RAVI Temp SOlution
1033 case l_xtd
1034 when 'YTD' THEN l_temp_xtd := 'RLY';
1035 when 'QTD' THEN l_temp_xtd := 'RLQ';
1036 when 'MTD' THEN l_temp_xtd := 'RLM';
1037 when 'WTD' THEN l_temp_xtd := 'RLW';
1038 when 'DAY' THEN l_temp_xtd := 'DAY';
1039 else l_temp_xtd := l_xtd;
1040 end case;
1041
1042
1043 poa_dbi_util_pkg.get_custom_trend_binds
1044 ( x_custom_output => l_custom_output
1045 , p_xtd => l_xtd
1046 , p_comparison_type => l_comparison_type
1047 );
1048
1049 -- Gets Lag, Curr and Prev patterns and dates for current rolling calendar
1050 get_age_binds
1051 ( p_period_type => l_temp_xtd
1052 , p_comparison_type => l_comparison_type
1053 , p_custom_output => l_custom_output
1054 );
1055
1056 x_custom_output := l_custom_output;
1057
1058 end get_trd_sql;
1059
1060
1061
1062 procedure get_dbn_trd_sql
1063 ( p_param in bis_pmv_page_parameter_tbl
1064 , x_custom_sql out nocopy varchar2
1065 , x_custom_output out nocopy bis_query_attributes_tbl
1066 )
1067 is
1068 begin
1069 get_trd_sql
1070 ( p_param => p_param
1071 , x_custom_sql => x_custom_sql
1072 , x_custom_output => x_custom_output
1073 , p_distribution => 'Y'
1074 );
1075 end get_dbn_trd_sql;
1076
1077
1078
1079 procedure get_detail_sql
1080 ( p_param in bis_pmv_page_parameter_tbl
1081 , x_custom_sql out nocopy varchar2
1082 , x_custom_output out nocopy bis_query_attributes_tbl
1083 )as
1084
1085 l_where_clause varchar2(10000);
1086 l_xtd varchar2(200);
1087 l_mv varchar2(10000);
1088 l_join_from varchar2(10000);
1089 l_join_where varchar2(10000);
1090 l_order_by varchar2(100);
1091 l_backlog_type varchar2(100);
1092
1093 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1094 l_custom_output bis_query_attributes_tbl;
1095
1096 l_drill_url varchar2(500);
1097 l_sr_id varchar2(100);
1098
1099 l_to_date_type VARCHAR2 (3) ;
1100 l_as_of_date date;
1101
1102 begin
1103
1104 biv_dbi_tmpl_util.get_detail_page_function( l_drill_url, l_sr_id );
1105
1106 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1107
1108 biv_dbi_tmpl_util.process_parameters
1109 ( p_param => p_param
1110 , p_report_type => 'BACKLOG_DETAIL'
1111 , x_where_clause => l_where_clause
1112 , x_xtd => l_xtd
1113 , x_mv => l_mv
1114 , x_join_from => l_join_from
1115 , x_join_where => l_join_where
1116 , x_join_tbl => l_join_tbl
1117 , x_as_of_date => l_as_of_date
1118 );
1119
1120 if l_where_clause like '%<replace this>%' then
1121 l_where_clause := replace(l_where_clause,'fact.<replace this> in (&'||biv_dbi_tmpl_util.g_AGING||')'
1122 ,'(&RANGE_LOW is null or fact.age >= &RANGE_LOW) and (&RANGE_HIGH is null or fact.age < &RANGE_HIGH)');
1123
1124 biv_dbi_tmpl_util.bind_low_high
1125 ( p_param
1126 , 'BIV_DBI_BACKLOG_AGING'
1127 , '&RANGE_LOW'
1128 , '&RANGE_HIGH'
1129 , l_custom_output );
1130
1131 end if;
1132
1133 l_backlog_type := biv_dbi_tmpl_util.get_backlog_type(p_param);
1134
1135 if l_backlog_type = 'ESCALATED' then
1136 l_where_clause := l_where_clause || ' and fact.escalated_date is not null';
1137 elsif l_backlog_type = 'UNOWNED' then
1138 l_where_clause := l_where_clause || ' and fact.unowned_date is not null';
1139 end if;
1140
1141 l_order_by := biv_dbi_tmpl_util.get_order_by(p_param);
1142 if l_order_by like '% DESC%' then
1143 if l_order_by like '%BIV_MEASURE12%' then
1144 l_order_by := 'fact.incident_date desc, fact.incident_id desc';
1145 else
1146 l_order_by := 'fact.age desc, fact.incident_id desc';
1147 end if;
1148 else
1149 if l_order_by like '%BIV_MEASURE12%' then
1150 l_order_by := 'fact.incident_date asc, fact.incident_id asc';
1151 else
1152 l_order_by := 'fact.age asc, fact.incident_id asc';
1153 end if;
1154 end if;
1155
1156 x_custom_sql := '
1157 select
1158 i.incident_number biv_measure1
1159 , rt.value biv_measure2 -- request_type
1160 , pr.value biv_measure3 -- product
1161 , pr.description biv_measure4
1162 , cu.value biv_measure5 -- customer
1163 , sv.value biv_measure6 -- severity
1164 , ag.value biv_measure7 -- assignment_group
1165 , st.value biv_measure8 -- status
1166 , decode(fact.escalated_date,null,&NO,&YES) biv_measure9
1167 , decode(fact.unowned_date,null,&NO,&YES) biv_measure10
1168 , fact.age biv_measure11
1169 , fnd_date.date_to_displaydate(fact.incident_date) biv_measure12' ||
1170 case
1171 when l_drill_url is not null then
1172 '
1173 , ''pFunctionName=' || l_drill_url || '&' || l_sr_id || '=''||fact.incident_id biv_attribute1'
1174 else
1175 '
1176 , null biv_attribute1'
1177 end ||
1178 '
1179 from
1180 ( select
1181 fact.*
1182 , rank() over(order by ' || l_order_by || ') -1 rnk
1183 from
1184 ' || l_mv || ' fact
1185 where
1186 fact.backlog_date_to = to_date(''31-12-4712'',''DD-MM-YYYY'')
1187 ' || l_where_clause || '
1188 ) fact
1189 ' || l_join_from || '
1190 , cs_incidents_all_b i
1191 where
1192 1=1
1193 and fact.incident_id = i.incident_id' || l_join_where || '
1194 and (fact.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
1195 &ORDER_BY_CLAUSE
1196 '
1197 --|| biv_dbi_tmpl_util.dump_parameters(p_param)
1198 ;
1199
1200 poa_dbi_util_pkg.get_custom_balance_binds
1201 ( p_custom_output => l_custom_output
1202 , p_balance_fact => biv_dbi_tmpl_util.get_balance_fact(null)
1203 , p_xtd => l_xtd
1204 );
1205
1206 biv_dbi_tmpl_util.bind_age_dates
1207 ( p_param => p_param
1208 , p_current_name => '&AGE_CURRENT_ASOF_DATE' -- these will be the text you actually use in your sql stmt.
1209 , p_prior_name => '&AGE_PREVIOUS_ASOF_DATE' --
1210 , p_custom_output => l_custom_output );
1211
1212
1213 biv_dbi_tmpl_util.bind_yes_no
1214 ( '&YES'
1215 , '&NO'
1216 , l_custom_output );
1217
1218 x_custom_output := l_custom_output;
1219
1220 end get_detail_sql;
1221
1222 FUNCTION get_last_refresh_date
1223 (p_object_name IN varchar2
1224 )
1225
1226 RETURN varchar2
1227 IS
1228 last_refresh_date date;
1229
1230 BEGIN
1231 select last_update_date into last_refresh_date
1232 from bis_refresh_log
1233 where object_name = p_object_name and status='SUCCESS'
1234 and last_update_date =( select max(last_update_date)
1235 from bis_refresh_log
1236 where object_name= p_object_name and status='SUCCESS' );
1237
1238 return last_refresh_date;
1239
1240 END get_last_refresh_date;
1241
1242 FUNCTION current_report_start_date (
1243 as_of_date IN DATE
1244 , period_type IN VARCHAR2)
1245 RETURN DATE
1246 IS
1247 l_date DATE;
1248 l_curr_year NUMBER;
1249 l_curr_qtr NUMBER;
1250 l_curr_period NUMBER;
1251 l_week_start_date DATE;
1252 BEGIN
1253 IF (period_type = 'YTD')
1254 THEN
1255 SELECT SEQUENCE
1256 INTO l_curr_year
1257 FROM fii_time_ent_year
1258 WHERE as_of_date BETWEEN start_date AND end_date;
1259
1260 SELECT start_date
1261 INTO l_date
1262 FROM fii_time_ent_year
1263 WHERE SEQUENCE = l_curr_year - 3;
1264 END IF;
1265
1266 IF (period_type = 'QTD')
1267 THEN
1268 SELECT SEQUENCE
1269 , ent_year_id
1270 INTO l_curr_qtr
1271 , l_curr_year
1272 FROM fii_time_ent_qtr
1273 WHERE as_of_date BETWEEN start_date AND end_date;
1274
1275 IF (l_curr_qtr = 4)
1276 THEN
1277 l_date := fii_time_api.ent_cyr_start (as_of_date);
1278 ELSE
1279 SELECT start_date
1280 INTO l_date
1281 FROM fii_time_ent_qtr
1282 WHERE SEQUENCE = l_curr_qtr + 1
1283 AND ent_year_id = l_curr_year - 2;
1284 END IF;
1285 END IF;
1286
1287 IF (period_type = 'MTD')
1288 THEN
1289 SELECT p.SEQUENCE
1290 , q.ent_year_id
1291 INTO l_curr_period
1292 , l_curr_year
1293 FROM fii_time_ent_period p
1294 , fii_time_ent_qtr q
1295 WHERE p.ent_qtr_id = q.ent_qtr_id
1296 AND as_of_date BETWEEN p.start_date AND p.end_date;
1297
1298 SELECT start_date
1299 INTO l_date
1300 FROM (SELECT p.start_date
1301 FROM fii_time_ent_period p
1302 , fii_time_ent_qtr q
1303 WHERE p.ent_qtr_id = q.ent_qtr_id
1304 AND ( ( p.SEQUENCE = l_curr_period + 1
1305 AND q.ent_year_id = l_curr_year - 1)
1306 OR ( p.SEQUENCE = 1
1307 AND q.ent_year_id = l_curr_year))
1308 ORDER BY p.start_date)
1309 WHERE ROWNUM <= 1;
1310 /* select p.start_date
1311 into l_date
1312 from fii_time_ent_period p, fii_time_ent_qtr q
1313 where p.ent_qtr_id=q.ent_qtr_id
1314 and p.sequence=l_curr_period+1 -- temp fix for 12 points on graph else 13 points brrao modified
1315 and q.ent_year_id=l_curr_year-1;
1316 */
1317 END IF;
1318
1319 IF (period_type = 'WTD')
1320 THEN
1321 SELECT start_date
1322 INTO l_week_start_date
1323 FROM fii_time_week
1324 WHERE as_of_date BETWEEN start_date AND end_date;
1325
1326 SELECT start_date
1327 INTO l_date
1328 FROM fii_time_week
1329 WHERE start_date = l_week_start_date - 7 * 12;
1330 END IF;
1331
1332 IF (period_type = 'DAY')
1333 THEN
1334
1335 SELECT start_date
1336 INTO l_date
1337 FROM fii_time_day
1338 WHERE start_date = as_of_date - 6;
1339 END IF;
1340
1341 RETURN l_date;
1342 EXCEPTION
1343 WHEN OTHERS
1344 THEN
1345 RETURN bis_common_parameters.get_global_start_date;
1346 END current_report_start_date;
1347
1348
1349 end biv_dbi_bak_age_pkg;