[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_TEMPLATE_PKG
Source
1 PACKAGE BODY poa_dbi_template_pkg
2 /* $Header: poadbitmplb.pls 120.3 2006/07/25 09:13:35 sdiwakar noship $ */
3
4 AS
5
6
7 FUNCTION get_paren_str(p_paren_count IN NUMBER,
8 p_filter_where IN VARCHAR2) RETURN VARCHAR2;
9
10 FUNCTION get_group_and_sel_clause(
11 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
12 , p_use_alias IN VARCHAR2
13 ) RETURN VARCHAR2;
14
15 FUNCTION get_paren_str(p_paren_count IN NUMBER,
16 p_filter_where IN VARCHAR2) RETURN VARCHAR2
17 IS
18 l_paren_str VARCHAR2 (10000);
19 BEGIN
20 IF p_paren_count = 2
21 THEN
22 l_paren_str := ' ) oset05 ' || p_filter_where || ') oset ';
23 ELSIF p_paren_count = 3
24 THEN
25 l_paren_str := ' ) ) ' || p_filter_where || ' ) oset ';
26 ELSIF p_paren_count = 5
27 THEN
28 l_paren_str := ' ) oset05) oset10) oset15) oset20 '
29 || p_filter_where || ')oset ';
30 ELSIF p_paren_count = 6
31 THEN
32 l_paren_str := ' ) oset05) oset10) oset15) oset20) oset25 '
33 || p_filter_where || ' )oset ';
34 ELSIF p_paren_count = 7
35 THEN
36 l_paren_str := ' ) oset05) oset10) oset13) oset15) oset20) '
37 || p_filter_where || ' )oset ';
38 END IF;
39
40 return l_paren_str;
41 END get_paren_str;
42
43 FUNCTION status_sql (
44 p_fact_name IN VARCHAR2
45 , p_where_clause IN VARCHAR2
46 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
47 , p_use_windowing IN VARCHAR2
48 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
49 , p_use_grpid VARCHAR2 := 'Y'
50 , p_paren_count IN NUMBER := 3
51 , p_filter_where IN VARCHAR2 := NULL
52 , p_generate_viewby IN VARCHAR2 := 'Y'
53 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL)
54 RETURN VARCHAR2
55 IS
56 l_query VARCHAR2 (10000);
57 l_col_names VARCHAR2 (10000);
58 l_group_and_sel_clause VARCHAR2 (10000);
59 l_from_clause VARCHAR2 (10000);
60 l_full_where_clause VARCHAR2 (10000);
61 l_grpid_clause VARCHAR2 (200);
62 l_compute_prior VARCHAR2 (1) := 'N';
63 l_compute_prev_prev VARCHAR2 (1) := 'N';
64 l_paren_str VARCHAR2 (2000);
65 l_compute_opening_bal VARCHAR2(1) := 'N';
66 l_inlist VARCHAR2 (300);
67 l_inlist_bmap NUMBER := 0;
68 l_total_col_names VARCHAR2 (10000);
69 l_viewby_rank_where VARCHAR2 (10000);
70 l_in_join_tables VARCHAR2 (1000) := '';
71 l_filter_where VARCHAR2 (1000);
72 l_join_tables VARCHAR2 (10000);
73 l_col_calc_tbl poa_dbi_util_pkg.poa_dbi_col_calc_tbl;
74
75 BEGIN
76
77 IF (p_use_grpid = 'Y')
78 THEN
79 l_grpid_clause := 'and fact.grp_id = decode(cal.period_type_id,1,14,16,13,32,11,64,7)';
80 ELSIF (p_use_grpid = 'R')
81 THEN
82 l_grpid_clause := 'and fact.grp_id = decode(cal.period_type_id,1,0,16,1,32,3,64,7)';
83 END IF;
84 l_group_and_sel_clause := get_group_and_sel_clause(p_join_tables, p_use_alias => 'Y');
85
86 IF(p_in_join_tables is not null) then
87
88 FOR i in 1 .. p_in_join_tables.COUNT
89 LOOP
90 l_in_join_tables := l_in_join_tables || ', ' || p_in_join_tables(i).table_name || ' ' || p_in_join_tables(i).table_alias;
91 END LOOP;
92 END IF;
93
94 get_status_col_calc( p_col_name
95 , l_col_calc_tbl
96 , l_inlist_bmap
97 , l_compute_prior
98 , l_compute_prev_prev
99 , l_compute_opening_bal);
100
101 l_col_names := '';
102
103 FOR i IN 1 .. l_col_calc_tbl.COUNT
104 LOOP
105 l_col_names := l_col_names
106 || ', '
107 || l_col_calc_tbl(i).calc_begin
108 || l_col_calc_tbl(i).date_decode_begin
109 || l_col_calc_tbl(i).column_name
110 || l_col_calc_tbl(i).date_decode_end
111 || l_col_calc_tbl(i).calc_end
112 || ' '
113 || l_col_calc_tbl(i).alias_begin
114 || l_col_calc_tbl(i).alias_end
115 || fnd_global.newline;
116 END LOOP;
117
118
119 -- 0 (0 0 0) = neither XTD or XED
120 -- 1 (0 0 1) = XED
121 -- 2 (0 1 0) = XTD
122 -- 3 (0 1 1) = both XTD and XED
123 -- 4 (1 0 0) = YTD
124 -- 5 (1 0 1) = YTD and XED
125 -- 6 (1 1 0) = YTD and XTD)
126 -- 7 (1 1 1) = YTD and XTD and XED
127
128 l_inlist :=
129 '('
130 || CASE
131 WHEN -- if one or more columns had XED
132 BITAND (l_inlist_bmap
133 , g_inlist_xed) = g_inlist_xed
134 THEN -- alway append current
135 g_c_period_end_date
136 || CASE -- append prev date if needed
137 WHEN l_compute_prior = 'Y'
138 THEN ',' || g_p_period_end_date
139 END
140 END
141 || CASE -- when XED and (XTD or YTD) exist
142 WHEN l_inlist_bmap IN (3, 5, 7)
143 THEN ','
144 END
145 || CASE -- if one or more columns had XTD
146 WHEN ( BITAND (l_inlist_bmap
147 , g_inlist_xtd) = g_inlist_xtd
148 OR BITAND (l_inlist_bmap
149 , g_inlist_ytd) = g_inlist_ytd)
150 THEN -- alway append current
151 g_c_as_of_date
152 || CASE -- append prev date if needed
153 WHEN l_compute_prior = 'Y'
154 THEN ',' || g_p_as_of_date
155 END
156 END
157 || case
158 when bitand(l_inlist_bmap, g_inlist_rlx) = g_inlist_rlx then
159 g_c_period_end_date
160 || case -- append prev date if needed
161 when l_compute_prior = 'Y' then
162 ',' || g_p_period_end_date
163 end
164 end
165 || case
166 when bitand(l_inlist_bmap, g_inlist_bal) = g_inlist_bal then
167 g_c_as_of_date_balance
168 || case -- append prev date if needed
169 when l_compute_prior = 'Y' then
170 ',' || g_p_as_of_date_balance
171 end
172 || case
173 when l_compute_opening_bal = 'Y' then
174 ',' || g_c_as_of_date_o_balance
175 end
176 end
177 || CASE
178 WHEN l_compute_prev_prev = 'Y'
179 THEN ', &PREV_PREV_DATE'
180 END
181 || ')';
182
183
184 IF p_filter_where is not null
185 THEN
186 l_filter_where := ' where ' || p_filter_where;
187 END IF;
188
189 -- Determine how many closing parens we need
190 l_paren_str := get_paren_str(p_paren_count,
191 l_filter_where);
192
193
194 if( bitand(l_inlist_bmap, g_inlist_rlx) = g_inlist_rlx) then
195 l_join_tables := ', fii_time_structures cal '|| l_in_join_tables;
196 l_full_where_clause := ' where fact.time_id = cal.time_id '
197 || 'and fact.period_type_id = cal.period_type_id '
198 || fnd_global.newline
199 || p_where_clause
200 || fnd_global.newline
201 || 'and cal.report_date in '
202 || l_inlist
203 -- &RLX_NESTED_PATTERN should be replaced with some
204 -- &BIS bind substitution when available from fii/bis team.
205 || fnd_global.newline
206 || 'and bitand(cal.record_type_id, &RLX_NESTED_PATTERN) = '
207 || '&RLX_NESTED_PATTERN ';
208 elsif( bitand(l_inlist_bmap, g_inlist_bal) = g_inlist_bal) then
209 l_join_tables := '';
210 l_full_where_clause := ' where fact.report_date in '
211 || l_inlist
212 || p_where_clause;
213 elsif( l_inlist_bmap = 0) then --for status sqls with no as-of date or compare to
214 l_join_tables := l_in_join_tables;
215 if(p_where_clause is not null) then
216 l_full_where_clause := ' where ' || p_where_clause;
217 end if;
218 else
219 l_join_tables := ', fii_time_rpt_struct_v cal'
220 || fnd_global.newline
221 || l_in_join_tables;
222 l_full_where_clause :=' where fact.time_id = cal.time_id '
223 || p_where_clause
224 || fnd_global.newline
225 || ' and cal.report_date in '
226 || l_inlist
227 || fnd_global.newline
228 || ' and bitand(cal.record_type_id, '
229 || CASE -- if one or more columns = YTD then use nested pattern
230 WHEN BITAND (l_inlist_bmap, g_inlist_ytd) = g_inlist_ytd
231 THEN '&YTD_NESTED_PATTERN'
232 ELSE '&BIS_NESTED_PATTERN'
233 END
234 || ') = cal.record_type_id ';
235 end if;
236 l_query := '(select '
237 || l_group_and_sel_clause
238 || l_col_names
239 || fnd_global.newline||' from '
240 || p_fact_name
241 || ' fact'
242 || l_join_tables
243 || l_full_where_clause
244 || l_grpid_clause
245 || fnd_global.newline||' group by '
246 || l_group_and_sel_clause
247 || l_paren_str;
248
249
250
251 IF(p_generate_viewby = 'Y')
252 THEN
253 l_viewby_rank_where :='';
254
255 if(p_use_windowing <> 'P') then
256 l_viewby_rank_where := l_viewby_rank_where ||
257 ',' || fnd_global.newline;
258 end if;
259 l_viewby_rank_where := l_viewby_rank_where ||
260 get_viewby_rank_clause (
261 p_join_tables => p_join_tables
262 , p_use_windowing => p_use_windowing);
263 END IF;
264
265 l_query := l_query || l_viewby_rank_where;
266
267 RETURN l_query;
268 END status_sql;
269
270 FUNCTION get_group_and_sel_clause(
271 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
272 , p_use_alias IN VARCHAR2
273 ) RETURN VARCHAR2
274 IS
275 l_group_and_sel_clause VARCHAR2 (500);
276 l_alias VARCHAR2 (200);
277 BEGIN
278 l_alias := '';
279 if(p_use_alias = 'Y') then
280 if(p_join_tables(1).inner_alias is not null) then
281 l_alias := p_join_tables(1).inner_alias || '.';
282 else
283 l_alias := 'fact.';
284 end if;
285 end if;
286
287 l_group_and_sel_clause := ' ' || l_alias || p_join_tables (1).fact_column;
288
289
290 FOR i IN 2 .. p_join_tables.COUNT
291 LOOP
292
293 l_alias := '';
294 if(p_use_alias = 'Y') then
295 if(p_join_tables(i).inner_alias is not null) then
296 l_alias := p_join_tables(i).inner_alias || '.';
297 else
298 l_alias := 'fact.';
299 end if;
300 end if;
301
302 l_group_and_sel_clause := l_group_and_sel_clause
303 ||', ' || l_alias
304 || p_join_tables(i).fact_column;
305 END LOOP;
306
307 return l_group_and_sel_clause;
308 END get_group_and_sel_clause;
309
310
311 PROCEDURE get_status_col_calc(
312 p_col_names IN poa_dbi_util_pkg.poa_dbi_col_tbl
313 , x_col_calc_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_calc_tbl
314 , x_inlist_bmap OUT NOCOPY NUMBER
315 , x_compute_prior OUT NOCOPY VARCHAR2
316 , x_compute_prev_prev OUT NOCOPY VARCHAR2
317 , x_compute_opening_bal OUT NOCOPY VARCHAR2) IS
318
319 l_col_calc poa_dbi_util_pkg.poa_dbi_col_calc_rec;
320 l_date_decode_begin VARCHAR2 (1000);
321 l_date_decode_end VARCHAR2(1000);
322 l_cur_date_clause VARCHAR2(500);
323 l_prev_date_clause VARCHAR2(500);
324 l_o_bal_date_clause VARCHAR2(200);
325 l_prev_prev_date_clause VARCHAR2(200);
326 BEGIN
327 x_col_calc_tbl := poa_dbi_util_pkg.POA_DBI_COL_CALC_TBL();
328
329 x_inlist_bmap :=0;
330
331 FOR i IN 1 .. p_col_names.COUNT
332 LOOP
333 IF p_col_names(i).to_date_type = 'XED'
334 THEN
335 l_cur_date_clause := g_c_period_end_date || ',';
336 l_prev_date_clause := g_p_period_end_date || ',';
337 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
338 , g_inlist_xed);
339 ELSIF p_col_names(i).to_date_type in ('XTD','YTD') then
340 l_cur_date_clause := g_c_as_of_date || ',';
341 l_prev_date_clause := g_p_as_of_date || ',';
342 l_prev_prev_date_clause := g_pp_date || ',';
343 l_o_bal_date_clause := g_c_as_of_date_o_balance || ',';
344
345 IF p_col_names(i).to_date_type = 'XTD'
346 THEN
347 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
348 , g_inlist_xtd);
349 ELSE -- YTD
350 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
351 , g_inlist_ytd);
352 END IF;
353 elsif p_col_names(i).to_date_type = 'RLX' then
354 l_cur_date_clause := g_c_period_end_date || ',';
355 l_prev_date_clause := g_p_period_end_date || ',';
356 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
357 , g_inlist_rlx);
358 elsif p_col_names(i).to_date_type = 'BAL' then
362 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
359 l_cur_date_clause := g_c_as_of_date_balance || ',';
360 l_prev_date_clause := g_p_as_of_date_balance || ',';
361 l_o_bal_date_clause := g_c_as_of_date_o_balance || ',';
363 , g_inlist_bal);
364 elsif p_col_names(i).to_date_type = 'NA' then
365 l_cur_date_clause := '';
366 l_prev_date_clause := '';
367 END IF;
368
369 if (bitand(x_inlist_bmap, g_inlist_bal) = g_inlist_bal) then
370 l_date_decode_begin := 'decode(fact.report_date, ';
371 l_date_decode_end := ', null)';
372 elsif (x_inlist_bmap = 0) then -- for query with no as-of date or perio
373 l_date_decode_begin := '';
374 l_date_decode_end := '';
375 else
376 l_date_decode_begin := 'decode(cal.report_date, ';
377 l_date_decode_end := ', null)';
378 end if;
379
380 -- Regular current column
381 l_col_calc.column_name := p_col_names(i).column_name;
382 l_col_calc.alias_begin := 'c_' || p_col_names(i).column_alias;
383 l_col_calc.alias_end := '';
384 l_col_calc.calc_begin := 'sum(';
385 l_col_calc.calc_end := ')';
386 l_col_calc.date_decode_begin := l_date_decode_begin || l_cur_date_clause;
387 l_col_calc.date_decode_end := l_date_decode_end;
388 x_col_calc_tbl.extend();
389 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
390
391 -- Prev column (based on prior_code)
392 IF (p_col_names(i).prior_code <> poa_dbi_util_pkg.no_priors)
393 THEN
394 x_compute_prior := 'Y';
395 l_col_calc.column_name := p_col_names(i).column_name;
396 l_col_calc.alias_begin := 'p_' || p_col_names(i).column_alias;
397 l_col_calc.alias_end := '';
398 l_col_calc.calc_begin := 'sum(';
399 l_col_calc.calc_end := ')';
400 l_col_calc.date_decode_begin := l_date_decode_begin || l_prev_date_clause;
401 l_col_calc.date_decode_end := l_date_decode_end;
402
403 x_col_calc_tbl.extend();
404 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
405
406 END IF;
407
408 -- Prev Prev column
409 IF (p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev)
410 THEN
411 x_compute_prev_prev := 'Y';
412 l_col_calc.column_name := p_col_names(i).column_name;
413 l_col_calc.alias_begin := 'p2_' || p_col_names(i).column_alias;
414 l_col_calc.alias_end := '';
415 l_col_calc.calc_begin := 'sum(';
416 l_col_calc.calc_end := ')';
417 l_col_calc.date_decode_begin := l_date_decode_begin
418 || l_prev_prev_date_clause;
419 l_col_calc.date_decode_end := l_date_decode_end;
420
421 x_col_calc_tbl.extend();
422 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
423
424 END IF;
425 -- Opening Balance Column
426 if p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR then
427 x_compute_opening_bal := 'Y';
428 l_col_calc.column_name := p_col_names(i).column_name;
429 l_col_calc.alias_begin := 'o_' || p_col_names(i).column_alias;
430 l_col_calc.alias_end := '';
431 l_col_calc.calc_begin := 'sum(';
432 l_col_calc.calc_end := ')';
433 l_col_calc.date_decode_begin := l_date_decode_begin || l_o_bal_date_clause;
437 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
434 l_col_calc.date_decode_end := l_date_decode_end;
435
436 x_col_calc_tbl.extend();
438
439 end if;
440 -- If grand total is flagged, do current and prior grand totals
441 IF (p_col_names(i).grand_total = 'Y')
442 THEN
443 -- grand total of current column
444 l_col_calc.column_name := p_col_names(i).column_name;
445 l_col_calc.alias_begin := 'c_' || p_col_names(i).column_alias;
446 l_col_calc.alias_end := '_total';
447 l_col_calc.calc_begin := 'sum(sum(';
448 l_col_calc.calc_end := ')) over()';
449 l_col_calc.date_decode_begin := l_date_decode_begin || l_cur_date_clause;
450 l_col_calc.date_decode_end := l_date_decode_end;
451
452 x_col_calc_tbl.extend();
453 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
454
455
456 -- grand total of prev column (based on prior_code flagging)
457 IF ( p_col_names(i).prior_code = poa_dbi_util_pkg.both_priors
458 OR p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev
459 OR p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )
460 THEN
461 l_col_calc.column_name := p_col_names(i).column_name;
462 l_col_calc.alias_begin := 'p_' || p_col_names(i).column_alias;
463 l_col_calc.alias_end := '_total';
464 l_col_calc.calc_begin := 'sum(sum(';
465 l_col_calc.calc_end := ')) over()';
466 l_col_calc.date_decode_begin := l_date_decode_begin || l_prev_date_clause;
467 l_col_calc.date_decode_end := l_date_decode_end;
468
469 x_col_calc_tbl.extend();
470 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
471
472 END IF;
473
474 -- grand total of prev prev col (based on prior_code)
475 IF (p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev)
476 THEN
477 l_col_calc.column_name := p_col_names(i).column_name;
478 l_col_calc.alias_begin := 'p2_' || p_col_names(i).column_alias;
479 l_col_calc.alias_end := '_total';
480 l_col_calc.calc_begin := 'sum(sum(';
481 l_col_calc.calc_end := ')) over()';
482 l_col_calc.date_decode_begin := l_date_decode_begin || l_prev_prev_date_clause;
483 l_col_calc.date_decode_end := l_date_decode_end;
484
485 x_col_calc_tbl.extend();
486 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
487
488 END IF;
489
490 -- grand total Opening Balance Column
491 IF (p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR) then
492 l_col_calc.column_name := p_col_names(i).column_name;
493 l_col_calc.alias_begin := 'o_' || p_col_names(i).column_alias;
494 l_col_calc.alias_end := '_total';
495 l_col_calc.calc_begin := 'sum(sum(';
496 l_col_calc.calc_end := ')) over()';
497 l_col_calc.date_decode_begin := l_date_decode_begin || l_o_bal_date_clause;
498 l_col_calc.date_decode_end := l_date_decode_end;
499
500 x_col_calc_tbl.extend();
501 x_col_calc_tbl(x_col_calc_tbl.count) := l_col_calc;
502 END IF;
503 END IF;
504 END LOOP;
505
506 END get_status_col_calc;
507
508
509 FUNCTION union_all_trend_sql(
510 p_mv IN poa_dbi_util_pkg.poa_dbi_mv_tbl
511 , p_comparison_type IN VARCHAR2
512 , p_filter_where IN VARCHAR2 := NULL
513 , p_diff_measures in varchar2 := 'Y')
514 RETURN VARCHAR2
515 IS
516 l_inlist_bmap NUMBER;
517 l_col_list poa_dbi_util_pkg.poa_dbi_col_list;
518 l_col_names VARCHAR2(10000);
519 l_inner_col_names VARCHAR2(10000);
520 l_union_sel VARCHAR2(10000);
521 l_query VARCHAR2(30000);
522 l_compute_opening_bal VARCHAR2(10);
523 l_query_rec poa_dbi_util_pkg.poa_dbi_union_query_rec;
524 l_query_tbl poa_dbi_util_pkg.poa_dbi_union_query_tbl;
525 l_called_by_union VARCHAR2(1) ;
526 BEGIN
527 l_called_by_union := 'Y' ;
528 l_query_tbl := poa_dbi_util_pkg.poa_dbi_union_query_tbl();
529 for m in 1 .. p_mv.count loop
530 l_query_tbl.extend;
534 || fnd_global.newline;
531 l_query_tbl(l_query_tbl.count).in_union_sel := 'cal.name cal_name'
532 || fnd_global.newline
533 || ', cal.start_date cal_start_date'
535 end loop;
536
537 l_union_sel := 'cal_name'
538 || fnd_global.newline
539 || ', cal_start_date'
540 || fnd_global.newline;
541
542
543 for i in 1 ..p_mv.count loop -- main loop
544 l_query_tbl(i).template_sql :=
545 trend_sql ( p_mv(i).mv_xtd
546 , p_comparison_type
547 , p_mv(i).mv_name
548 , p_mv(i).mv_where
549 , p_mv(i).mv_col
550 , p_mv(i).use_grp_id
551 , p_mv(i).in_join_tbls
552 , p_mv(i).mv_hint
553 , p_called_by_union => l_called_by_union );
554
555 l_col_list := poa_dbi_util_pkg.poa_dbi_col_list();
556
557 get_trend_col_clauses(p_mv(i).mv_col
558 , p_mv(i).mv_xtd
559 , l_inlist_bmap
560 , l_col_names
561 , l_inner_col_names
562 , l_compute_opening_bal
563 , l_col_list);
564
565 if(p_diff_measures = 'Y') then
566 for j in 1 .. l_col_list.count loop
567 for k in 1 .. p_mv.count loop
568 if(k=i) then
569 l_query_tbl(k).in_union_sel :=
570 l_query_tbl(k).in_union_sel
571 || ', '
572 || l_col_list(j)
573 || fnd_global.newline;
574 else
575 l_query_tbl(k).in_union_sel :=
576 l_query_tbl(k).in_union_sel
577 || ', 0 '
578 || l_col_list(j)
579 || fnd_global.newline;
580 end if;
581 end loop; -- k loop
582 l_union_sel := l_union_sel
583 || ', sum('
584 || l_col_list(j)
585 || ') '
586 || l_col_list(j)
587 || fnd_global.newline;
588 end loop; -- j loop
589 else
590 for j in 1 .. l_col_list.count loop
591 l_query_tbl(i).in_union_sel :=
592 l_query_tbl(i).in_union_sel
593 || ', '
594 || l_col_list(j)
595 || fnd_global.newline;
596 if(i=1) then /*outer select clause needs to be built only once */
597 l_union_sel := l_union_sel
598 || ', sum('
599 || l_col_list(j)
600 || ') '
601 || l_col_list(j)
602 || fnd_global.newline;
603 end if;
604 end loop;
605 end if;
606 end loop ; -- i loop
607
608 ---Begin Changes for spend trend graph
609 l_query := case when p_mv(1).mv_xtd like 'RL%' then
610 poa_dbi_util_pkg.get_rolling_inline_view || ' select' || fnd_global.newline
611 else ' ( select' || fnd_global.newline end
612 ---End Changes for spend trend graph
613 || l_union_sel
614 || 'from (' || fnd_global.newline;
615
616 FOR m IN 1 .. l_query_tbl.COUNT LOOP
617 l_query := l_query
618 || '(select' || fnd_global.newline
619 || l_query_tbl(m).in_union_sel
620 || 'from ' || fnd_global.newline
621 || replace(l_query_tbl(m).template_sql,'order by cal.start_date','')
622 || fnd_global.newline;
623 IF(m <> l_query_tbl.COUNT) THEN
624 l_query := l_query || ') UNION ALL ' || fnd_global.newline;
625 END IF;
626 END LOOP;
627
628 l_query := l_query
629 || ')) group by cal_name, cal_start_date '
630 || fnd_global.newline
631 || ') uset ' || fnd_global.newline
632 || 'order by cal_start_date' || fnd_global.newline;
633
634 return l_query;
635 END union_all_trend_sql;
636
637
638 function union_all_status_sql(
639 p_mv in poa_dbi_util_pkg.poa_dbi_mv_tbl
640 , p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl
641 , p_use_windowing in varchar2
642 , p_paren_count in number := 3
643 , p_filter_where in varchar2 := null
644 , p_generate_viewby in varchar2 := 'Y'
645 , p_diff_measures in varchar2 := 'Y'
646 )
647 return varchar2
648 is
649 l_mv poa_dbi_util_pkg.poa_dbi_mv_tbl;
650 l_col_calc poa_dbi_util_pkg.poa_dbi_col_calc_tbl;
651 l_inlist_bmap number;
652 l_compute_prior varchar2(1);
653 l_compute_prev_prev varchar2(1);
654 l_compute_opening_bal varchar2(1);
655 l_query_rec poa_dbi_util_pkg.poa_dbi_union_query_rec;
656 l_query_tbl poa_dbi_util_pkg.poa_dbi_union_query_tbl;
657 l_out_union_sel varchar2(2000) := '';
658 l_query varchar2(20000) := '';
659 l_sel_clause varchar2(2000) := '';
660 l_filter_where varchar2(2000) := '';
661 l_col_name poa_dbi_util_pkg.poa_dbi_col_tbl;
662 l_join_tables poa_dbi_util_pkg.poa_dbi_join_tbl;
663 begin
664
665 l_join_tables := poa_dbi_util_pkg.poa_dbi_join_tbl();
666 l_query_tbl := poa_dbi_util_pkg.poa_dbi_union_query_tbl();
667
668 for m in 1 .. p_mv.count loop
669 l_query_tbl.extend;
670 l_query_tbl(l_query_tbl.count).in_union_sel := '';
671 end loop;
672
676 get_status_col_calc(p_mv(i).mv_col
673 for i in 1 ..p_mv.count loop -- main loop
674 l_col_name := poa_dbi_util_pkg.poa_dbi_col_tbl();
675
677 , l_col_calc
678 , l_inlist_bmap
679 , l_compute_prior
680 , l_compute_prev_prev
681 , l_compute_opening_bal);
682
683 for j in 1 .. p_mv(i).mv_col.count loop -- loop 1
684 l_col_name.extend;
685 l_col_name(l_col_name.count) := p_mv(i).mv_col(j);
686 l_col_name(l_col_name.count).grand_total := 'N';
687 end loop; -- loop 1
688
689 l_query_tbl(i).template_sql := status_sql(p_mv(i).mv_name
690 , p_mv(i).mv_where
691 , p_join_tables
692 , 'N'
693 , l_col_name
694 , p_mv(i).use_grp_id
695 , 3
696 , p_filter_where => NULL
697 , p_generate_viewby => 'N'
698 , p_in_join_tables => p_mv(i).in_join_tbls);
699
700 if(p_diff_measures = 'Y') then
701 for k in 1 .. l_col_calc.count loop -- loop 2
702 if(l_col_calc(k).alias_end = '_total') then
703 null;
704 else
705 for l in 1 .. p_mv.count loop -- loop 3
706 if ( l = i ) then -- compares the index to find out the current MV in main loop
707 -- measure needs to be put down
708 l_query_tbl(l).in_union_sel := l_query_tbl(l).in_union_sel
709 || ', '
710 || l_col_calc(k).alias_begin
711 || fnd_global.newline;
712 else -- measure needs to be forced to 0
713 l_query_tbl(l).in_union_sel := l_query_tbl(l).in_union_sel
714 || ', 0 '
715 || l_col_calc(k).alias_begin
716 || fnd_global.newline;
717 end if;
718 end loop; -- loop 3
719 end if;
720
721 l_out_union_sel := l_out_union_sel
722 || ', '
723 || l_col_calc(k).calc_begin
724 || l_col_calc(k).alias_begin
725 || l_col_calc(k).calc_end
726 || ' '
727 || l_col_calc(k).alias_begin
728 || l_col_calc(k).alias_end
729 || fnd_global.newline;
730
731 end loop; -- loop 2
732 else
733 for k in 1 .. l_col_calc.count loop
734 if(l_col_calc(k).alias_end = '_total') then
735 null;
736 else
737 l_query_tbl(i).in_union_sel := l_query_tbl(i).in_union_sel
738 || ', '
739 || l_col_calc(k).alias_begin
740 || fnd_global.newline;
741 end if;
742
743 if(i = 1) then
744 /*need to calculate outer sel clause only once*/
745 l_out_union_sel := l_out_union_sel
746 || ', '
747 || l_col_calc(k).calc_begin
748 || l_col_calc(k).alias_begin
749 || l_col_calc(k).calc_end
750 || ' '
751 || l_col_calc(k).alias_begin
752 || l_col_calc(k).alias_end
753 || fnd_global.newline;
754 end if;
755 end loop;
756 end if;
757 end loop; -- main loop
758
759 l_sel_clause := get_group_and_sel_clause(p_join_tables, p_use_alias => 'N');
760
761 if (p_filter_where is not null) then
762 l_filter_where := ' where ' || p_filter_where;
763 end if;
764
765 l_query := 'select '
766 || l_sel_clause
767 || fnd_global.newline
768 || l_out_union_sel
769 || 'from (' || fnd_global.newline;
770
771 for m in 1 .. l_query_tbl.count loop
772 l_query := l_query || 'select '
773 || l_sel_clause || fnd_global.newline
774 || l_query_tbl(m).in_union_sel
775 || 'from ' || fnd_global.newline
776 || '(('
777 || l_query_tbl(m).template_sql;
778
779 if(m <> l_query_tbl.count) then
780 l_query := l_query || ' UNION ALL ' || fnd_global.newline;
781 end if;
782 end loop;
783
784 l_query := l_query
785 || ') group by '
786 || l_sel_clause || fnd_global.newline
787 || get_paren_str(p_paren_count, l_filter_where);
788
789 if(p_generate_viewby = 'Y') then
790 if(p_use_windowing <> 'P') then
791 l_query := l_query ||
792 ',' || fnd_global.newline;
793 end if;
794 l_query := l_query ||
795 get_viewby_rank_clause (
796 p_join_tables => p_join_tables
797 , p_use_windowing => p_use_windowing);
798 end if;
799
800 return l_query;
801
802 end union_all_status_sql;
803
804
805 FUNCTION trend_sql (
806 p_xtd IN VARCHAR2
807 , p_comparison_type IN VARCHAR2
808 , p_fact_name IN VARCHAR2
809 , p_where_clause IN VARCHAR2
810 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
811 , p_use_grpid IN VARCHAR2 := 'Y'
812 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL
813 , p_fact_hint IN VARCHAR2 := null
814 , p_called_by_union IN VARCHAR2 := 'N')
815 RETURN VARCHAR2
816 IS
820 l_col_alias VARCHAR2 (4000);
817 l_query VARCHAR2 (10000);
818 l_col_names VARCHAR2 (4000);
819 l_inner_col_names VARCHAR2 (4000);
821 l_total_col_alias VARCHAR2 (4000);
822 l_view_by VARCHAR2 (120);
823 l_cal_clause VARCHAR2 (1000);
824 l_time_clause VARCHAR2 (400);
825 l_grpid_clause VARCHAR2 (200);
826 l_c_report_date_str VARCHAR2 (1000);
827 l_p_report_date_str VARCHAR2 (1000);
828 l_inlist_bmap NUMBER := 0;
829 l_in_join_tables VARCHAR2 (1000) := '';
830 l_compute_opening_bal varchar2(1) := 'N';
831 l_outer_time_clause VARCHAR2 (400);
832 l_col_list poa_dbi_util_pkg.poa_dbi_col_list;
833 BEGIN
834
835 IF(p_in_join_tables is not null) then
836
837 FOR i in 1 .. p_in_join_tables.COUNT
838 LOOP
839 l_in_join_tables := l_in_join_tables || ' , ' || p_in_join_tables(i).table_name || ' ' || p_in_join_tables(i).table_alias;
840 END LOOP;
841 END IF;
842
843 get_trend_col_clauses(p_col_name
844 , p_xtd
845 , l_inlist_bmap
846 , l_col_names
847 , l_inner_col_names
848 , l_compute_opening_bal
849 , l_col_list /*not used by trend_sql*/);
850
851
852 IF ( p_xtd IN ( 'WTD','DAY')
853 AND p_comparison_type = 'Y')
854 THEN
855 l_time_clause :=
856 ' ((cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE) or (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)) ';
857 l_outer_time_clause :=
858 ' and ((n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE) or (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)) ';
859 ELSE
860 if p_xtd like 'RL%' then
861 l_time_clause := '1=1 ';
862 else
863 l_time_clause := ' cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE ';
864 l_outer_time_clause := ' and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE ';
865 end if;
866 END IF;
867
868 IF ( p_comparison_type = 'Y'
869 AND p_xtd <> 'YTD')
870 THEN
871 -- Yearly
872 l_cal_clause :=
873 CASE
874 WHEN -- (XTD or YTD) only
875 ( BITAND (l_inlist_bmap
876 , g_inlist_xtd) = g_inlist_xtd
877 OR BITAND (l_inlist_bmap
878 , g_inlist_ytd) = g_inlist_ytd)
879 AND NOT BITAND (l_inlist_bmap
880 , g_inlist_xed) = g_inlist_xed
881 THEN -- (XTD or YTD) only
882 ' and n.report_date = (case when (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
883 then least(cal.end_date, &BIS_PREVIOUS_ASOF_DATE)
884 else least(cal.end_date, &BIS_CURRENT_ASOF_DATE) end) '
885 WHEN -- (XTD or YTD) and XED
886 ( BITAND (l_inlist_bmap
887 , g_inlist_xtd) = g_inlist_xtd
888 OR BITAND (l_inlist_bmap
889 , g_inlist_ytd) = g_inlist_ytd)
890 AND BITAND (l_inlist_bmap
891 , g_inlist_xed) = g_inlist_xed
892 THEN -- (XTD or YTD) and XED
893 ' and n.report_date in ( (case when (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
894 then least(cal.end_date, &BIS_PREVIOUS_ASOF_DATE)
895 else least(cal.end_date, &BIS_CURRENT_ASOF_DATE) end)
896 , &BIS_CURRENT_EFFECTIVE_END_DATE
897 , &BIS_PREVIOUS_EFFECTIVE_END_DATE) '
898 WHEN -- XED only
899 NOT ( BITAND (l_inlist_bmap
900 , g_inlist_xtd) = g_inlist_xtd
901 OR BITAND (l_inlist_bmap
902 , g_inlist_ytd) = g_inlist_ytd)
903 AND BITAND (l_inlist_bmap
904 , g_inlist_xed) = g_inlist_xed
905 THEN -- placeholder for XED only
906 ' and n.report_date = cal.end_date '
907 when bitand(l_inlist_bmap,g_inlist_rlx) = g_inlist_rlx then
908 ' and n.report_date = cal.report_date '
909 when bitand(l_inlist_bmap,g_inlist_bal) = g_inlist_bal then
910 ' '
911 END;
912 ELSE
913 -- Sequential comparison type
914 l_cal_clause :=
915 CASE
916 WHEN -- (XTD or YTD) only
917 ( BITAND (l_inlist_bmap
918 , g_inlist_xtd) = g_inlist_xtd
919 OR BITAND (l_inlist_bmap
920 , g_inlist_ytd) = g_inlist_ytd)
921 AND NOT BITAND (l_inlist_bmap
922 , g_inlist_xed) = g_inlist_xed
923 THEN -- (XTD or YTD) only
924 ' and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
928 , g_inlist_xtd) = g_inlist_xtd
925 and n.report_date between cal.start_date and cal.end_date '
926 WHEN -- (XTD or YTD) and XED
927 ( BITAND (l_inlist_bmap
929 OR BITAND (l_inlist_bmap
930 , g_inlist_ytd) = g_inlist_ytd)
931 AND BITAND (l_inlist_bmap
932 , g_inlist_xed) = g_inlist_xed
933 THEN -- (XTD or YTD) and XED
934 ' and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE, &BIS_CURRENT_EFFECTIVE_END_DATE)
935 and n.report_date between cal.start_date and cal.end_date '
936 WHEN -- XED only
937 NOT ( BITAND (l_inlist_bmap
938 , g_inlist_xtd) = g_inlist_xtd
939 OR BITAND (l_inlist_bmap
940 , g_inlist_ytd) = g_inlist_ytd)
941 AND BITAND (l_inlist_bmap
942 , g_inlist_xed) = g_inlist_xed
943 THEN -- XED only
944 ' and n.report_date = cal.end_date '
945 when bitand(l_inlist_bmap,g_inlist_rlx) = g_inlist_rlx then
946 ' and n.report_date = cal.report_date '
947 when bitand(l_inlist_bmap,g_inlist_bal) = g_inlist_bal then
948 ' '
949 END;
950 END IF;
951
952 IF (p_use_grpid = 'Y')
953 THEN
954 l_grpid_clause := ' and fact.grp_id = decode(n.period_type_id,1,14,16,13,32,11,64,7)';
955 ELSIF (p_use_grpid = 'R')
956 THEN
957 l_grpid_clause := 'and fact.grp_id = decode(n.period_type_id,1,0,16,1,32,3,64,7)';
958 END IF;
959
960 l_query :=
961 '(select n.start_date'
962 || case when p_xtd like 'RL%' then ', n.ordinal ' end
963 || '
964 ' ||l_col_names || '
965 from (select ' || p_fact_hint || ' '
966 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
967 || 'n.start_date, n.report_date '
968 || l_inner_col_names
969 || ' from '
970 || p_fact_name
971 || ' fact,
972 '
973 || case
974 when p_xtd like 'RL%' then
975 case
976 when (BITAND (l_inlist_bmap
977 , g_inlist_bal) = g_inlist_bal) then
978 '( select /*+ NO_MERGE */ cal.ordinal,cal.start_date, cal.report_date'
979 || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal,p_called_by_union => p_called_by_union )
980 || ' cal
981 where '
982 || l_time_clause
983 || l_cal_clause
984 || ' ) n
985 where fact.report_date = least(n.report_date,&LAST_COLLECTION)
986 '
987 ELSE
988 '( select /*+ NO_MERGE */ cal.ordinal,n.time_id,n.record_type_id,n.period_type_id,n.report_date,cal.start_date,cal.end_date'
989 || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd, p_called_by_union => p_called_by_union)
990 || ' cal, fii_time_structures n
991 where '
992 || l_time_clause
993 || l_cal_clause
994 -- &RLX_NESTED_PATTERN should be replaced with
995 -- some &BIS bind substitution when available from fii/bis team.
996 || ' and bitand(n.record_type_id,&RLX_NESTED_PATTERN) = &RLX_NESTED_PATTERN ) n'
997 || l_in_join_tables
998 || '
999 where fact.time_id = n.time_id
1000 and fact.period_type_id = n.period_type_id
1001 '
1002 end
1003 else -- non RL%
1004 ' (select /*+ NO_MERGE */ n.time_id,n.record_type_id, n.period_type_id,n.report_date,cal.start_date,cal.end_date
1005 from '
1006 || poa_dbi_util_pkg.get_calendar_table (p_xtd, p_called_by_union => p_called_by_union)
1007 || ' cal, fii_time_rpt_struct_v n
1008 where '
1009 || l_time_clause
1010 || l_cal_clause
1011 || ' and bitand(n.record_type_id, '
1012 || CASE -- if one or more columns = YTD then use nested pattern
1013 WHEN BITAND (l_inlist_bmap, g_inlist_ytd) = g_inlist_ytd
1014 THEN '&YTD_NESTED_PATTERN'
1015 ELSE '&BIS_NESTED_PATTERN'
1016 END
1017 || ') = n.record_type_id ) n
1018 ' || l_in_join_tables || '
1019 where fact.time_id = n.time_id
1020 '
1021 end
1022 || l_grpid_clause || '
1023 ' || p_where_clause || '
1024 group by '
1025 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1026 || ' n.start_date, n.report_date) i, '
1027 || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal, p_called_by_union => p_called_by_union )
1028 || ' n where i.start_date (+) = n.start_date '
1029 || l_outer_time_clause
1030 || case when p_xtd like 'RL%' then 'and i.ordinal(+) = n.ordinal ' end
1031 || '
1032 group by '
1033 || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1034 || 'n.start_date) iset, '
1035 || poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N', p_called_by_union => p_called_by_union )
1036 || ' cal '
1037 || '
1038 where cal.start_date between '
1039 || case
1040 when p_xtd like 'RL%' then
1044 end
1041 poa_dbi_util_pkg.get_report_start_date(p_xtd)
1042 else
1043 '&BIS_CURRENT_REPORT_START_DATE'
1045 || ' and &BIS_CURRENT_ASOF_DATE
1046 and cal.start_date = iset.start_date(+)'
1047 || case when p_xtd like 'RL%' then ' and cal.ordinal = iset.ordinal(+)' end
1048 || '
1049 order by cal.start_date';
1050 RETURN l_query;
1051
1052 END trend_sql;
1053
1054
1055
1056 PROCEDURE get_trend_col_clauses(
1057 p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1058 , p_xtd IN VARCHAR2
1059 , x_inlist_bmap OUT NOCOPY NUMBER
1060 , x_col_names OUT NOCOPY VARCHAR2
1061 , x_inner_col_names OUT NOCOPY VARCHAR2
1062 , x_compute_opening_bal OUT NOCOPY VARCHAR2
1063 , x_col_list OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_list)
1064 IS
1065 l_c_report_date_str VARCHAR2 (1000);
1066 l_p_report_date_str VARCHAR2 (1000);
1067 l_c_start_date VARCHAR2 (1000);
1068 l_p_start_date VARCHAR2 (1000);
1069 l_c_end_date VARCHAR2 (1000);
1070 l_p_end_date VARCHAR2 (1000);
1071 l_order_by VARCHAR2 (50);
1072 BEGIN
1073 x_col_list := poa_dbi_util_pkg.POA_DBI_COL_LIST();
1074 x_inlist_bmap := 0;
1075 IF p_col_name.FIRST IS NOT NULL
1076 THEN
1077 FOR i IN p_col_name.FIRST .. p_col_name.LAST
1078 LOOP
1079 IF p_col_name (i).to_date_type = 'XED'
1080 THEN
1081 l_c_report_date_str := ' n.end_date ';
1082 l_p_report_date_str := ' n.end_date ';
1083 l_c_start_date := '&BIS_CURRENT_REPORT_START_DATE';
1084 l_c_end_date := '&BIS_CURRENT_EFFECTIVE_END_DATE';
1085 l_p_start_date := '&BIS_PREVIOUS_REPORT_START_DATE';
1086 l_p_end_date := '&BIS_PREVIOUS_EFFECTIVE_END_DATE';
1087 l_order_by := 'n.start_date';
1088 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
1089 , g_inlist_xed);
1090 elsif p_col_name(i).to_date_type = 'RLX' then
1091 l_c_report_date_str := ' n.end_date ';
1092 l_p_report_date_str := ' n.end_date ';
1093 l_c_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd);
1094 l_c_end_date := '&BIS_CURRENT_EFFECTIVE_END_DATE and n.ordinal in (-1,2)';
1095 l_p_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y');
1096 l_p_end_date := '&BIS_PREVIOUS_EFFECTIVE_END_DATE and n.ordinal in (-1,1)';
1097 l_order_by := 'n.ordinal, n.start_date';
1098 x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap
1099 , g_inlist_rlx);
1100 elsif p_col_name(i).to_date_type = 'BAL' then
1101 l_c_report_date_str := ' n.end_date ';
1102 l_p_report_date_str := ' n.end_date ';
1103 l_c_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd);
1104 l_c_end_date := '&BIS_CURRENT_EFFECTIVE_END_DATE and n.ordinal in (-1,2)';
1105 l_p_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y');
1106 l_p_end_date := '&BIS_PREVIOUS_EFFECTIVE_END_DATE and n.ordinal in (-1,1)';
1110 ELSIF p_col_name(i).to_date_type = 'XTD' THEN
1107 l_order_by := 'n.start_date';
1108 x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap
1109 , g_inlist_bal);
1111 l_c_report_date_str := ' LEAST (n.end_date, &BIS_CURRENT_ASOF_DATE) ';
1112 l_p_report_date_str := ' LEAST (n.end_date, &BIS_PREVIOUS_ASOF_DATE) ';
1113 l_c_start_date := '&BIS_CURRENT_REPORT_START_DATE';
1114 l_c_end_date := '&BIS_CURRENT_ASOF_DATE';
1115 l_p_start_date := '&BIS_PREVIOUS_REPORT_START_DATE';
1116 l_p_end_date := '&BIS_PREVIOUS_ASOF_DATE';
1117 l_order_by := 'n.start_date';
1118 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
1119 , g_inlist_xtd);
1120 ELSIF p_col_name(i).to_date_type = 'YTD' THEN
1121 l_c_report_date_str := ' LEAST (n.end_date, &BIS_CURRENT_ASOF_DATE) ';
1122 l_p_report_date_str := ' LEAST (n.end_date, &BIS_PREVIOUS_ASOF_DATE) ';
1123 l_c_start_date := '&BIS_CURRENT_REPORT_START_DATE';
1124 l_c_end_date := '&BIS_CURRENT_ASOF_DATE';
1125 l_p_start_date := '&BIS_PREVIOUS_REPORT_START_DATE';
1126 l_p_end_date := '&BIS_PREVIOUS_ASOF_DATE';
1127 l_order_by := 'n.start_date';
1128 x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
1129 , g_inlist_ytd);
1130 END IF;
1131
1132 -- current column
1133 x_col_names :=
1134 x_col_names
1135 || ', sum(case when (n.start_date between '
1136 || l_c_start_date
1137 || ' and '
1138 || l_c_end_date
1139 || ' and i.report_date = '
1140 || l_c_report_date_str
1141 || ') then '
1142 || p_col_name (i).column_alias
1143 || ' else null end) c_'
1144 || p_col_name (i).column_alias
1145 || fnd_global.newline;
1146 x_inner_col_names :=
1147 x_inner_col_names
1148 || ', sum(' || p_col_name(i).column_name || ') ' || p_col_name(i).column_alias;
1149
1150 x_col_list.extend();
1151 x_col_list(x_col_list.count) := 'c_' || p_col_name(i).column_alias;
1152
1153 -- prior column, based on prior code flagging
1154 IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
1155 THEN
1156 x_col_names :=
1157 x_col_names
1158 || ', lag(sum(case when (n.start_date between '
1159 || l_p_start_date
1160 || ' and '
1161 || l_p_end_date
1162 || ' and i.report_date = '
1163 || l_p_report_date_str
1164 || ' ) then '
1165 || p_col_name (i).column_alias
1166 || ' else null end), &LAG'
1167 || ') over (order by '
1168 || l_order_by
1169 || ') p_'
1170 || p_col_name (i).column_alias
1171 || '
1172 ';
1173
1174 x_col_list.extend();
1175 x_col_list(x_col_list.count) := 'p_' || p_col_name(i).column_alias;
1176 END IF;
1177
1178 -- Opening Balance Column
1179 if p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR
1180 -- and p_col_name(i).to_date_type = 'BAL'
1181 then
1182 x_compute_opening_bal := 'Y';
1183 if( p_col_name(i).to_date_type = 'BAL')
1184 THEN
1185 x_col_names :=
1186 x_col_names
1187 || ', lag(sum('
1188 || p_col_name(i).column_alias
1189 || '), decode(&BIS_TIME_COMPARISON_TYPE,''YEARLY'',&LAG *2,1)) over (order by n.ordinal,n.start_date) o_'
1190 || p_col_name(i).column_alias
1191 || '
1192 ';
1193 ELSE
1194 x_col_names :=
1195 x_col_names
1196 || ', lag(sum('
1197 || p_col_name(i).column_alias
1198 || '), decode(&BIS_TIME_COMPARISON_TYPE,''YEARLY'',&LAG *2,1)) over (order by n.start_date) o_'
1199 || p_col_name(i).column_alias
1200 || '
1201 ';
1202 END IF;
1203
1204 x_col_list.extend();
1205 x_col_list(x_col_list.count) := 'o_' || p_col_name(i).column_alias;
1206 end if;
1207
1208 -- Grand total for current columns
1209 -- Note: RLX and BAL not supported here
1210 --UNION ALL queries also do not support grand totals
1211 IF (p_col_name (i).grand_total = 'Y')
1212 THEN
1213 x_col_names :=
1214 x_col_names
1215 || ',
1216 sum(sum('
1217 || p_col_name (i).column_alias
1218 || ')) over () c_'
1219 || p_col_name (i).column_alias
1220 || '_total
1221 ';
1222
1223 -- Grand total for previous columns
1224 IF (p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors)
1225 THEN
1226 x_col_names :=
1227 x_col_names
1228 || ',
1229 sum(lag(sum('
1230 || p_col_name (i).column_alias
1231 || '))) over () p_'
1232 || p_col_name (i).column_alias
1233 || '_total
1234 ';
1235 END IF;
1236 END IF;
1237
1238 END LOOP;
1239
1240 END IF;
1241 END get_trend_col_clauses;
1242
1243
1244
1245 FUNCTION dtl_status_sql (
1246 p_fact_name IN VARCHAR2
1247 , p_where_clause IN VARCHAR2
1248 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1249 , p_use_windowing IN VARCHAR2
1253 , p_group_by IN VARCHAR2
1250 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1251 , p_use_grpid IN VARCHAR2 := 'Y'
1252 , p_paren_count IN NUMBER := 3
1254 , p_from_clause IN VARCHAR2)
1255 RETURN VARCHAR2
1256 IS
1257 l_from_clause VARCHAR2 (10000);
1258 l_where_clause VARCHAR2 (10000);
1259 l_group_and_sel_clause VARCHAR2 (10000);
1260 l_query VARCHAR2 (10000);
1261 l_col_names VARCHAR2 (10000);
1262 BEGIN
1263 FOR i IN 1 .. p_join_tables.COUNT
1264 LOOP
1265 l_group_and_sel_clause :=
1266 l_group_and_sel_clause || CASE
1267 WHEN i > 1
1268 THEN ', '
1269 END || ' fact.' || p_join_tables (i).fact_column;
1270 l_from_clause :=
1271 l_from_clause || CASE
1272 WHEN i > 1
1273 THEN ', '
1274 END || p_join_tables (i).table_name || ' ' || p_join_tables (i).table_alias;
1275 l_where_clause :=
1276 l_where_clause
1277 || CASE
1278 WHEN i > 1
1279 THEN ' and '
1280 END
1281 || ' fact.'
1282 || p_join_tables (i).fact_column
1283 || '='
1284 || p_join_tables (i).table_alias
1285 || '.'
1286 || p_join_tables (i).column_name;
1287
1288 IF (p_join_tables (i).dim_outer_join = 'Y')
1289 THEN
1290 l_where_clause := l_where_clause || '(+)';
1291 END IF;
1292
1293 IF (p_join_tables (i).additional_where_clause IS NOT NULL)
1294 THEN
1295 l_where_clause := l_where_clause || ' and ' || p_join_tables (i).additional_where_clause;
1296 END IF;
1297 END LOOP;
1298
1299 FOR i IN 1 .. p_col_name.COUNT
1300 LOOP
1301 l_col_names :=
1302 l_col_names || ', sum(fact.' || p_col_name (i).column_name || ' ) '
1303 || p_col_name (i).column_alias;
1304
1305 IF (p_col_name (i).grand_total = 'Y')
1306 THEN
1307 -- Sum of current column
1308 l_col_names :=
1309 l_col_names
1310 || ', sum(sum(fact.'
1311 || p_col_name (i).column_name
1312 || ')) over () '
1313 || p_col_name (i).column_alias
1314 || '_total ';
1315 END IF;
1316 END LOOP;
1317
1318 l_query :=
1319 l_col_names
1320 || p_from_clause
1321 || l_from_clause
1322 || ' WHERE '
1323 || l_where_clause
1324 || p_where_clause
1325 || p_group_by
1326 || ') oset5 ))'
1327 || ' WHERE (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)'
1328 || ' ORDER BY rnk';
1329 RETURN l_query;
1330 END dtl_status_sql;
1331
1332 FUNCTION dtl_status_sql2 (
1333 p_fact_name IN VARCHAR2
1334 , p_where_clause IN VARCHAR2
1335 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1336 , p_use_windowing IN VARCHAR2
1337 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1338 , p_use_grpid IN VARCHAR2 := 'Y'
1339 , p_filter_where IN VARCHAR2 := NULL
1340 , p_paren_count IN NUMBER := 3
1341 , p_group_by IN VARCHAR2
1342 , p_from_clause IN VARCHAR2)
1343 RETURN VARCHAR2
1344 IS
1345 l_from_clause VARCHAR2 (10000);
1346 l_where_clause VARCHAR2 (10000);
1347 l_query VARCHAR2 (10000);
1348 l_col_names VARCHAR2 (10000);
1349
1350 l_from_clause1 VARCHAR2 (10000);
1351 l_where_clause1 VARCHAR2 (10000);
1352
1353 l_filter_where VARCHAR2(10000);
1354
1355 BEGIN
1356 FOR i IN 1 .. p_join_tables.COUNT
1357 LOOP
1358 IF (p_join_tables (i).additional_where_clause IS NOT NULL)
1359 THEN
1360 l_from_clause1 :=
1361 l_from_clause1 || ','|| p_join_tables (i).table_name || ' ' || p_join_tables (i).table_alias;
1362
1363 l_where_clause1 :=
1364 l_where_clause1
1365 || ' and '
1366 || ' fact.'
1367 || p_join_tables (i).fact_column
1368 || '='
1369 || p_join_tables (i).table_alias
1370 || '.'
1371 || p_join_tables (i).column_name;
1372
1373 IF (p_join_tables (i).dim_outer_join = 'Y')
1374 THEN
1375 l_where_clause1 := l_where_clause1 || '(+)';
1376 END IF;
1377
1378 IF (p_join_tables (i).additional_where_clause IS NOT NULL)
1379 THEN
1380 l_where_clause1 := l_where_clause1 || ' and ' || p_join_tables (i).additional_where_clause;
1381 END IF;
1382 ELSE
1383 l_from_clause :=
1384 l_from_clause || ', ' || p_join_tables (i).table_name || ' ' || p_join_tables (i).table_alias;
1385 l_where_clause :=
1386 l_where_clause
1387 || ' and '
1388 || ' fact.'
1389 || p_join_tables (i).fact_column
1390 || '='
1391 || p_join_tables (i).table_alias
1392 || '.'
1393 || p_join_tables (i).column_name;
1394
1395 IF (p_join_tables (i).dim_outer_join = 'Y')
1396 THEN
1400 END LOOP;
1397 l_where_clause := l_where_clause || '(+)';
1398 END IF;
1399 END IF; -- additional_where_clause
1401
1402 FOR i IN 1 .. p_col_name.COUNT
1403 LOOP
1404 l_col_names :=
1405 l_col_names || ', sum(fact.' || p_col_name (i).column_name || ' ) '
1406 || p_col_name (i).column_alias;
1407
1408 IF (p_col_name (i).grand_total = 'Y')
1409 THEN
1410 -- Sum of current column
1411 l_col_names :=
1412 l_col_names
1413 || ', sum(sum(fact.'
1414 || p_col_name (i).column_name
1415 || ')) over () '
1416 || p_col_name (i).column_alias
1417 || '_total ';
1418 END IF;
1419 END LOOP;
1420
1421 IF (p_filter_where IS NOT NULL)
1422 THEN
1423 l_filter_where := ' WHERE ' || p_filter_where ;
1424 END IF;
1425
1426 l_query :=
1427 l_col_names
1428 || p_from_clause || l_from_clause1
1429 || ' WHERE 1 = 1'
1430 || p_where_clause || l_where_clause1
1431 || p_group_by
1432 || ') oset5 ) '|| l_filter_where || ' )'
1433 || ' WHERE (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ) fact '
1434 || l_from_clause
1435 || ' WHERE 1 = 1'
1436 || l_where_clause
1437 || ' ORDER BY rnk';
1438 RETURN l_query;
1439 END dtl_status_sql2;
1440
1441
1442 FUNCTION get_viewby_rank_clause (
1443 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1444 , p_use_windowing IN VARCHAR2)
1445 RETURN VARCHAR2 IS
1446 l_query varchar2(10000);
1447 l_from_clause VARCHAR2 (10000);
1448 l_where_clause VARCHAR2 (10000);
1449 l_last_from_alias varchar2(30);
1450 l_rownum_where varchar2(300);
1451 l_vo_max_fetch_size varchar2(100);
1452 BEGIN
1453 l_query := '';
1454
1455 IF(p_use_windowing = 'P') THEN
1456
1457 /* Determine the l_rownum_where. If VO_MAX_FETCH_SIZE is null then dont filter any rows */
1458 select fnd_profile.value('VO_MAX_FETCH_SIZE')
1459 into l_vo_max_fetch_size
1460 from dual;
1461
1462 if (l_vo_max_fetch_size is not null) then
1463 l_rownum_where := ' where rownum < '||l_vo_max_fetch_size||' + 1 ';
1464 else
1465 l_rownum_where := ' ';
1466 end if;
1467
1468 l_query := l_query ||
1469 ' where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1))'
1470 || ' oset2 '||l_rownum_where||' ) oset, '
1471 || fnd_global.newline;
1472 END IF;
1473
1474
1475 l_from_clause := p_join_tables (1).table_name || ' ' || p_join_tables (1).table_alias;
1476
1477 l_last_from_alias := p_join_tables(1).table_alias;
1478
1479 l_where_clause :=
1480 'oset.'
1481 || p_join_tables (1).fact_column
1482 || CASE WHEN p_join_tables(1).dim_outer_join = 'R' THEN '(+)' END
1483 || '='
1484 || p_join_tables (1).table_alias
1485 || '.'
1486 || p_join_tables (1).column_name;
1487
1488 IF (p_join_tables (1).dim_outer_join = 'Y')
1489 THEN
1490 l_where_clause := l_where_clause || '(+)';
1491 END IF;
1492
1493 IF (p_join_tables (1).additional_where_clause IS NOT NULL)
1494 THEN
1495 l_where_clause := l_where_clause || ' and ' || p_join_tables (1).additional_where_clause;
1496 END IF;
1497
1498 FOR i IN 2 .. p_join_tables.COUNT
1499 LOOP
1500 if p_join_tables(i).table_alias <> l_last_from_alias then
1501 l_last_from_alias := p_join_tables(i).table_alias;
1502 l_from_clause :=
1503 l_from_clause
1504 || ', '
1505 || p_join_tables (i).table_name
1506 || ' '
1507 || p_join_tables (i).table_alias;
1508 end if;
1509 l_where_clause :=
1510 l_where_clause
1511 || ' and oset.'
1512 || p_join_tables (i).fact_column
1513 || CASE WHEN p_join_tables(i).dim_outer_join = 'R' THEN '(+)' END
1514 || '='
1515 || p_join_tables (i).table_alias
1516 || '.'
1517 || p_join_tables (i).column_name;
1518
1519 IF (p_join_tables (i).dim_outer_join = 'Y')
1520 THEN
1521 l_where_clause := l_where_clause || '(+)';
1522 END IF;
1523
1524 IF (p_join_tables (i).additional_where_clause IS NOT NULL)
1525 THEN
1526 l_where_clause := l_where_clause || ' and ' || p_join_tables (i).additional_where_clause;
1527 END IF;
1528
1529 END LOOP;
1530
1531
1532 l_query := l_query ||
1533 l_from_clause
1534 || '
1535 where '
1536 || l_where_clause;
1537
1538 IF (p_use_windowing = 'Y') THEN
1539 l_query := l_query || '
1540 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)';
1541 END IF;
1542
1543 IF (p_use_windowing = 'Y' or p_use_windowing = 'P') THEN
1544 l_query := l_query || fnd_global.newline || 'ORDER BY rnk';
1545 ELSE
1546 l_query := l_query ||
1547 fnd_global.newline || '&ORDER_BY_CLAUSE nulls last';
1548 END IF;
1549
1550 RETURN l_query;
1551
1552 END;
1553
1554 END poa_dbi_template_pkg;
1555