[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_UTIL_PKG
Source
1 PACKAGE BODY poa_dbi_util_pkg AS
2 /* $Header: poadbiutilb.pls 120.4 2006/09/04 13:21:53 sriswami noship $ */
3
4 g_employee_id NUMBER := -1;
5
6 /* used by get_bucket_outer_query */
7 function get_bucket_clause
8 ( p_col_num in number
9 , p_col_name in varchar2
10 , p_alias_name in varchar2
11 , p_prefix in varchar2
12 , p_suffix in varchar2
13 , p_total_flag in varchar2
14 )return varchar2;
15
16 /* used by get_bucket_drill_url */
17 function get_bucket_url_clause
18 ( p_col_num in number
19 , p_alias_name in varchar2
20 , p_prefix in varchar2
21 , p_suffix in varchar2
22 , p_add_bucket_num in varchar2
23 )
24 return varchar2;
25
26 FUNCTION get_filter_where(p_cols in POA_DBI_FILTER_TBL)
27 return VARCHAR2 IS
28 l_where VARCHAR2(1000);
29
30 BEGIN
31 l_where := 'coalesce(';
32 for i in 1..p_cols.COUNT LOOP
33 if(i <> 1) then
34 l_where := l_where || ',';
35 end if;
36 l_where := l_where || '
37 decode(' || p_cols(i) || ',0,null,' || p_cols(i) || ')';
38 END LOOP;
39
40 l_where := l_where || ' ) is not null ';
41
42 return l_where;
43 END;
44
45 FUNCTION get_filter_where (p_cols in POA_DBI_FLEX_FILTER_TBL)
46 return VARCHAR2 IS
47 l_where VARCHAR2(1000);
48
49 BEGIN
50 l_where := 'coalesce (';
51
52 FOR i IN 1..p_cols.COUNT LOOP
53
54 IF(i <> 1) THEN
55 l_where := l_where || ',';
56 END IF;
57
58 IF (p_cols(i).modifier IS NULL) THEN
59
60 -- No modifier
61 l_where := l_where || '
62 ' || p_cols(i).measure_name;
63
64 ELSIF (p_cols(i).modifier = 'DECODE_0') THEN
65
66 -- Decode 0's as NULLs
67 l_where := l_where || '
68 decode(' || p_cols(i).measure_name || ', 0, null,' ||
69 p_cols(i).measure_name || ')';
70 END IF;
71
72 END LOOP;
73
74 l_where := l_where || ' ) is not null ';
75
76 RETURN l_where;
77
78 END get_filter_where;
79
80 FUNCTION get_calendar_table
81 ( period_type in varchar2
82 , p_include_prior in varchar2 := 'Y'
83 , p_include_opening in varchar2 := 'N'
84 , p_called_by_union in varchar2 := 'N'
85 ) RETURN VARCHAR2 IS
86
87 l_table_name VARCHAR2(5000);
88
89 BEGIN
90 IF period_type like 'RL%'
91 THEN
92 --Begin Changes of spend trend graph
93 IF NVL(p_called_by_union,'N') = 'Y'
94 THEN
95 RETURN '( select start_date, end_date, end_date report_date, to_char(end_date,''dd-Mon-yy'') name, ordinal from rolling_cal where 1=1 ' ||
96 case
97 when p_include_prior = 'Y' and
98 p_include_opening = 'Y' then
99 ''
100 when p_include_prior = 'Y' then
101 --'and current_ind in (1,2)'
102 ' and ( bitand(current_ind_sum,1) = 1 OR
103 bitand(current_ind_sum,2) = 2) '
104 when p_include_opening = 'Y' then
105 ---'and current_ind in (1,4)'
106 ' and ( bitand(current_ind_sum,1) = 1 OR
107 bitand(current_ind_sum,4) = 4)'
108 else
109 'and bitand(current_ind_sum,1) = 1'
110 end
111 || ')' ;
112 ELSE
113 --End Changes of spend trend graph
114 return '( select start_date, end_date, end_date report_date, to_char(end_date,''dd-Mon-yy'') name, ordinal from '
115 || '( select distinct '
116 || 'decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
117 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
118 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) '
119 || '- &RLX_DAYS_TO_START start_date'
120 || ', decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
121 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
122 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) end_date'
123 || ', decode(&BIS_TIME_COMPARISON_TYPE,''SEQUENTIAL'',-1,decode(t.current_ind,4,0,2,1,2)) ordinal '
124 || 'from biv_trend_rpt t '
125 || 'where t.offset > &RLX_ROWS_OFFSET '
126 || case
127 when p_include_prior = 'Y' and
128 p_include_opening = 'Y' then
129 ''
130 when p_include_prior = 'Y' then
131 'and current_ind in (1,2)'
132 when p_include_opening = 'Y' then
133 'and current_ind in (1,4)'
134 else
135 'and current_ind = 1'
136 end
137 || ' )'
138 || ' )';
139 END IF ;---p_called_by_union
140 END IF ; --Rolling Periods
141
142 if(period_type = 'YTD') then
143 l_table_name := 'fii_time_ent_year';
144 elsif(period_type = 'QTD') then
145 l_table_name := 'fii_time_ent_qtr';
146 elsif(period_type = 'MTD') then
147 l_table_name := 'fii_time_ent_period';
148 elsif(period_type = 'WTD') then
149 l_table_name := 'fii_time_week';
150 elsif(period_type = 'DAY') then
151 l_table_name := '(select fnd_date.date_to_displaydate(report_date) name, t.report_date start_date, t.report_date end_date from fii_time_day t)';
152 end if;
153
154 RETURN l_table_name ;
155
156 END get_calendar_table ;
157
158
159 FUNCTION get_nested_pattern(period_type IN varchar2)
160 return number
161 IS
162
163 l_pattern number;
164
165 BEGIN
166
167 if(period_type = 'RLY') then
168 l_pattern := 8192;
169 elsif(period_type = 'RLQ') then
170 l_pattern := 4096;
171 elsif(period_type = 'RLM') then
172 l_pattern := 2048;
173 elsif(period_type = 'RLW') then
174 l_pattern := 1024;
175 elsif(period_type = 'YTD') then
176 l_pattern := 119;
177 elsif(period_type = 'QTD') then
178 l_pattern := 55;
179 elsif(period_type = 'MTD') then
180 l_pattern := 23;
181 elsif(period_type = 'WTD') then
182 l_pattern := 11;
183 elsif(period_type = 'DAY') then
184 l_pattern := 1; end if;
185
186 return l_pattern;
187
188 END get_nested_pattern;
189
190 FUNCTION get_nested_period_type_id(period_type IN varchar2)
191 return number
192 IS
193
194 l_period_type_id number;
195
196 BEGIN
197
198 if(period_type = 'YTD') then
199 l_period_type_id := 64;
200 elsif(period_type = 'QTD') then
201 l_period_type_id := 32;
202 elsif(period_type = 'MTD') then
203 l_period_type_id := 16;
204 elsif(period_type = 'WTD') then
205 l_period_type_id := 1;
206 end if;
207
208 return l_period_type_id;
209
210 END get_nested_period_type_id;
211
212
213 FUNCTION get_sec_profile RETURN NUMBER
214 IS
215 BEGIN
216 ---Begin MOAC change
217 ---Call to fnd_profile is made conditionally.
218 -- IF NVL(g_sec_profile_id,-1) = -1
219 -- THEN
220 -- g_sec_profile_id := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
221 -- END IF ;
222 ---End MOAC change
223
224 RETURN nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1) ;
225 END get_sec_profile;
226
227
228 Function get_fnd_user_profile RETURN NUMBER
229 IS
230
231 l_fnd_user_profile NUMBER;
232
233 BEGIN
234
235 l_fnd_user_profile := fnd_global.user_id;
236
237 return l_fnd_user_profile;
238
239 END get_fnd_user_profile;
240
241 Function get_fnd_employee_profile RETURN NUMBER
242 IS
243 BEGIN
244 return fnd_global.employee_id;
245 END get_fnd_employee_profile;
246
247
248 FUNCTION bitor(x in number,y in number) return number
249 AS
250
251 BEGIN
252
253 return x + y - bitand(x,y);
254
255 END bitor;
256
257 PROCEDURE refresh (p_mv_name IN VARCHAR2)
258 IS
259
260 l_parallel_degree NUMBER := 0;
261
262 BEGIN
263
264 l_parallel_degree := bis_common_parameters.GET_DEGREE_OF_PARALLELISM();
265 IF (l_parallel_degree = 1) THEN
266 l_parallel_degree := 0;
267 END IF;
268
269 POA_LOG.debug_line('Refreshing : '|| p_mv_name);
270
271 DBMS_MVIEW.REFRESH(list => p_mv_name,
272 method => '?',
273 parallelism => l_parallel_degree);
274 EXCEPTION
275 when others then
276 raise;
277 END;
278
279 PROCEDURE get_parameter_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
280 p_dim_map in out NOCOPY poa_dbi_dim_map,
281 p_view_by out NOCOPY VARCHAR2,
282 p_comparison_type out NOCOPY VARCHAR2,
283 p_xtd out NOCOPY VARCHAR2,
284 p_as_of_date out NOCOPY DATE,
285 p_prev_as_of_date out NOCOPY DATE,
286 p_cur_suffix out NOCOPY VARCHAR2,
287 p_nested_pattern out NOCOPY NUMBER,
288 p_dim_bmap in out NOCOPY NUMBER)
289 IS
290
291 l_currency varchar2(30);
292 l_period_type varchar2(30);
293
294 BEGIN
295
296 for i in 1..p_param.COUNT LOOP
297 if( p_param(i).parameter_name= 'VIEW_BY') then
298 p_view_by := p_param(i).parameter_value;
299 end if;
300 if(p_param(i).parameter_name = 'PERIOD_TYPE') then
301 l_period_type := p_param(i).parameter_value;
302 end if;
303 if(p_param(i).parameter_name = 'TIME_COMPARISON_TYPE') then
304 if(p_param(i).parameter_value = 'YEARLY') then
305 p_comparison_type := 'Y';
306 else
307 p_comparison_type := 'S';
308 end if;
309 end if;
310 if(p_param(i).parameter_name = 'AS_OF_DATE') then
311 p_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
312 end if;
313 if(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
314 l_currency := p_param(i).parameter_id;
315 end if;
316
317 if(p_dim_map.exists(p_param(i).parameter_name)) then
318 if(p_param(i).parameter_id = '''''' or p_param(i).parameter_id is null or p_param(i).parameter_id = '' or p_param(i).parameter_id = 'All') then
319 p_dim_map(p_param(i).parameter_name).value := 'All';
320 else
321 p_dim_map(p_param(i).parameter_name).value := 'Val';
322 end if;
323 if(p_param(i).parameter_id is not null and p_param(i).parameter_id <> '''''') then
324 p_dim_bmap := bitor(p_dim_bmap, p_dim_map(p_param(i).parameter_name).bmap);
325 end if;
326 end if;
327 END LOOP;
328
329 if(p_dim_map.exists(p_view_by)) then
330 p_dim_bmap := bitor(p_dim_bmap,p_dim_map(p_view_by).bmap);
331 end if;
332
333 if l_period_type = 'FII_ROLLING_WEEK' then
334 p_xtd := 'RLW';
335 elsif l_period_type = 'FII_ROLLING_MONTH' then
336 p_xtd := 'RLM';
337 elsif l_period_type = 'FII_ROLLING_QTR' then
338 p_xtd := 'RLQ';
339 elsif l_period_type = 'FII_ROLLING_YEAR' then
340 p_xtd := 'RLY';
341 elsif l_period_type = 'FII_TIME_ENT_YEAR' then
342 p_xtd := 'YTD';
343 elsif l_period_type = 'FII_TIME_ENT_QTR' then
344 p_xtd := 'QTD';
345 elsif l_period_type = 'FII_TIME_ENT_PERIOD' then
346 p_xtd := 'MTD';
347 elsif l_period_type = 'FII_TIME_DAY' then
348 p_xtd := 'DAY';
349 else
350 p_xtd := 'WTD';
351 end if;
352
353 if(p_as_of_date is null) then p_as_of_date := sysdate; end if;
354 p_prev_as_of_date := poa_dbi_calendar_pkg.previous_period_asof_date(p_as_of_date, p_xtd, p_comparison_type);
355 p_nested_pattern := poa_dbi_util_pkg.get_nested_pattern(p_xtd);
356
357 if(p_comparison_type is null) then p_comparison_type := 'S'; end if;
358
359 if(l_currency = '''FII_GLOBAL1''') then
360 p_cur_suffix := 'g';
361 --Added by Arun.R for secondary global currency chanegs for OKI
362 elsif(l_currency = '''FII_GLOBAL2''') then
363 p_cur_suffix := 'sg';
364 --Added by Ashok for Annualization for OKI
365 elsif (l_currency = '''FII_GLOBAL3''') then
366 p_cur_suffix := 'a';
367 elsif(l_currency is not null) then
368 p_cur_suffix := 'b';
369 end if;
370
371 if(p_cur_suffix is null) then p_cur_suffix := 'g'; end if;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 POA_LOG.debug_line('refresh_manual_dist mvs ' || Sqlerrm || sqlcode || sysdate);
376 raise;
377
378 END get_parameter_values;
379
380 PROCEDURE get_drill_param_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
381 p_dim_map in out nocopy poa_dbi_dim_map,
382 p_cur_suffix out NOCOPY VARCHAR2)
383
384 IS
385
386 l_currency varchar2(30);
387
388 BEGIN
389
390 for i in 1..p_param.COUNT LOOP
391
392 if(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
393 l_currency := p_param(i).parameter_id;
394 end if;
395
396 if(p_dim_map.exists(p_param(i).parameter_name)) then
397 if(p_param(i).parameter_id = '''''' or p_param(i).parameter_id is null or p_param(i).parameter_id = '' or p_param(i).parameter_id = 'All') then
398 p_dim_map(p_param(i).parameter_name).value := 'All';
399 else
400 p_dim_map(p_param(i).parameter_name).value := 'Val';
401 end if;
402 end if;
403 END LOOP;
404
405 if(l_currency = '''FII_GLOBAL1''') then
406 p_cur_suffix := 'g';
407 elsif(l_currency = '''FII_GLOBAL2''') then
408 p_cur_suffix := 'sg';
409 elsif(l_currency is not null) then
410 p_cur_suffix := 'b';
411 end if;
412
413 if(p_cur_suffix is null) then p_cur_suffix := 'g'; end if;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 POA_LOG.debug_line('refresh_manual_dist mvs ' || Sqlerrm || sqlcode || sysdate);
418 raise;
419
420 END get_drill_param_values;
421
422
423 FUNCTION get_where_clauses(p_dim_map poa_dbi_dim_map, p_trend IN VARCHAR2) RETURN VARCHAR2
424 IS
425
426 l_where_clause VARCHAR2(4000);
427 i VARCHAR2(100);
428
429 BEGIN
430
431 i := p_dim_map.FIRST; -- get subscript of first element
432 WHILE i IS NOT NULL LOOP
433 if(p_dim_map(i).generate_where_clause = 'Y') then
434 if(p_dim_map(i).value = 'All') then
435 null;
436 else
437 l_where_clause := l_where_clause ||
438 ' and fact.'|| p_dim_map(i).col_name || ' in (&' || i || ') ';
439 end if;
440 end if;
441 i := p_dim_map.NEXT(i);
442 END LOOP;
443 return l_where_clause;
444
445 END get_where_clauses;
446
447 /* pass in the bucket set short name in p_short_name. This procedure queries
448 up that bucket set and adds columns to p_col_tbl for each bucket range
449 defined--by calling add_column. Returns the x_bucket_rec so that it
450 can be used for the outer bucket query
451 */
452 procedure add_bucket_columns
453 (p_short_name in varchar2
454 , p_col_tbl in out nocopy poa_DBI_UTIL_PKG.poa_dbi_col_tbl
455 , p_col_name in varchar2
456 , p_alias_name in varchar2
457 , x_bucket_rec out nocopy bis_bucket_pub.bis_bucket_rec_type
458 , p_grand_total in varchar2 := 'Y'
459 , p_prior_code in varchar2 := BOTH_PRIORS
460 , p_to_date_type in varchar2 := 'XTD'
461 )
462 is
463 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
464 l_return_status varchar2(3);
465 l_error_tbl bis_utilities_pub.error_tbl_type;
466 begin
467 bis_bucket_pub.retrieve_bis_bucket
468 ( p_short_name => p_short_name
469 , x_bis_bucket_rec => l_bucket_rec
470 , x_return_status => l_return_status
471 , x_error_tbl => l_error_tbl
472 );
473 if l_return_status = 'S' then
474 if l_bucket_rec.range1_name is not null then
475 poa_DBI_UTIL_PKG.add_column
476 ( p_col_tbl => p_col_tbl
477 , p_col_name => p_col_name || '_b1'
478 , p_alias_name => p_alias_name || '_b1'
479 , p_grand_total => p_grand_total
480 , p_to_date_type => p_to_date_type
481 , p_prior_code => p_prior_code
482 );
483 end if;
484 if l_bucket_rec.range2_name is not null then
485 poa_DBI_UTIL_PKG.add_column
486 ( p_col_tbl => p_col_tbl
487 , p_col_name => p_col_name || '_b2'
488 , p_alias_name => p_alias_name || '_b2'
489 , p_grand_total => p_grand_total
490 , p_to_date_type => p_to_date_type
491 , p_prior_code => p_prior_code
492 );
493 end if;
494 if l_bucket_rec.range3_name is not null then
495 poa_DBI_UTIL_PKG.add_column
496 ( p_col_tbl => p_col_tbl
497 , p_col_name => p_col_name || '_b3'
498 , p_alias_name => p_alias_name || '_b3'
499 , p_grand_total => p_grand_total
500 , p_to_date_type => p_to_date_type
501 , p_prior_code => p_prior_code
502 );
503 end if;
504 if l_bucket_rec.range4_name is not null then
505 poa_DBI_UTIL_PKG.add_column
506 ( p_col_tbl => p_col_tbl
507 , p_col_name => p_col_name || '_b4'
508 , p_alias_name => p_alias_name || '_b4'
509 , p_grand_total => p_grand_total
510 , p_to_date_type => p_to_date_type
511 , p_prior_code => p_prior_code
512 );
513 end if;
514 if l_bucket_rec.range5_name is not null then
515 poa_DBI_UTIL_PKG.add_column
516 ( p_col_tbl => p_col_tbl
517 , p_col_name => p_col_name || '_b5'
518 , p_alias_name => p_alias_name || '_b5'
519 , p_grand_total => p_grand_total
520 , p_to_date_type => p_to_date_type
521 , p_prior_code => p_prior_code
522 );
523 end if;
524 if l_bucket_rec.range6_name is not null then
525 poa_DBI_UTIL_PKG.add_column
526 ( p_col_tbl => p_col_tbl
527 , p_col_name => p_col_name || '_b6'
528 , p_alias_name => p_alias_name || '_b6'
529 , p_grand_total => p_grand_total
530 , p_to_date_type => p_to_date_type
531 , p_prior_code => p_prior_code
532 );
533 end if;
534 if l_bucket_rec.range7_name is not null then
535 poa_DBI_UTIL_PKG.add_column
536 ( p_col_tbl => p_col_tbl
537 , p_col_name => p_col_name || '_b7'
538 , p_alias_name => p_alias_name || '_b7'
539 , p_grand_total => p_grand_total
540 , p_to_date_type => p_to_date_type
541 , p_prior_code => p_prior_code
542 );
543 end if;
544 if l_bucket_rec.range8_name is not null then
545 poa_DBI_UTIL_PKG.add_column
546 ( p_col_tbl => p_col_tbl
547 , p_col_name => p_col_name || '_b8'
548 , p_alias_name => p_alias_name || '_b8'
549 , p_grand_total => p_grand_total
550 , p_to_date_type => p_to_date_type
551 , p_prior_code => p_prior_code
552 );
553 end if;
554 if l_bucket_rec.range9_name is not null then
555 poa_DBI_UTIL_PKG.add_column
556 ( p_col_tbl => p_col_tbl
557 , p_col_name => p_col_name || '_b9'
558 , p_alias_name => p_alias_name || '_b9'
559 , p_grand_total => p_grand_total
560 , p_to_date_type => p_to_date_type
561 , p_prior_code => p_prior_code
562 );
563 end if;
564 if l_bucket_rec.range10_name is not null then
565 poa_DBI_UTIL_PKG.add_column
566 ( p_col_tbl => p_col_tbl
567 , p_col_name => p_col_name || '_b10'
568 , p_alias_name => p_alias_name || '_b10'
569 , p_grand_total => p_grand_total
570 , p_to_date_type => p_to_date_type
571 , p_prior_code => p_prior_code
572 );
573 end if;
574 end if;
575 x_bucket_rec := l_bucket_rec;
576 end add_bucket_columns;
577
578 function get_bucket_url_clause
579 ( p_col_num in number
580 , p_alias_name in varchar2
581 , p_prefix in varchar2
582 , p_suffix in varchar2
583 , p_add_bucket_num in varchar2
584 )
585 return varchar2
586 is
587 begin
588 return ', ' || p_prefix ||
589 case p_add_bucket_num
590 when 'Y' then p_col_num
591 end || p_suffix || ' ' ||
592 p_alias_name || '_B' || p_col_num;
593
594 end get_bucket_url_clause;
595
596
597 function get_bucket_clause
598 ( p_col_num in number
599 , p_col_name in varchar2
600 , p_alias_name in varchar2
601 , p_prefix in varchar2
602 , p_suffix in varchar2
603 , p_total_flag in varchar2
604 )
605 return varchar2
606 is
607 begin
608 return ', ' || p_prefix || p_col_name || '_b'
609 || p_col_num ||
610 case p_total_flag
611 when 'Y' then '_total'
612 end || p_suffix || ' ' ||
613 p_alias_name || '_B' || p_col_num;
614
615 end get_bucket_clause;
616
617 /*
618 p_bucket_rec = the bucket rec returned from add_bucket_columns
619 p_col_name = similar to 'oset.c_age_bucket' => you have columns
620 such as oset.c_age_bucket_b1, oset.c_age_bucket_b2...
621 p_alias_name = similar to 'BIV_MEASURE10' => you want to alias
622 BIV_MEASURE10_B1, BIV_MEASURE10_B2, etc..
623 */
624 function get_bucket_outer_query
625 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
626 , p_col_name in varchar2
627 , p_alias_name in varchar2
628 , p_prefix in varchar2
629 , p_suffix in varchar2
630 , p_total_flag in varchar2 default 'N'
631 )
632 return varchar2
633 is
634 l_query varchar2(10000);
635 begin
636 if p_bucket_rec.range1_name is not null then
637 l_query := get_bucket_clause(1, p_col_name, p_alias_name,
638 p_prefix, p_suffix, p_total_flag);
639 end if;
640 if p_bucket_rec.range2_name is not null then
641 l_query := l_query || fnd_global.newline ||
642 get_bucket_clause(2, p_col_name, p_alias_name,
643 p_prefix, p_suffix, p_total_flag);
644 end if;
645 if p_bucket_rec.range3_name is not null then
646 l_query := l_query || fnd_global.newline ||
647 get_bucket_clause(3, p_col_name, p_alias_name,
648 p_prefix, p_suffix, p_total_flag);
649
650 end if;
651 if p_bucket_rec.range4_name is not null then
652 l_query := l_query || fnd_global.newline ||
653 get_bucket_clause(4, p_col_name, p_alias_name,
654 p_prefix, p_suffix, p_total_flag);
655 end if;
656 if p_bucket_rec.range5_name is not null then
657 l_query := l_query || fnd_global.newline ||
658 get_bucket_clause(5, p_col_name, p_alias_name,
659 p_prefix, p_suffix, p_total_flag);
660 end if;
661 if p_bucket_rec.range6_name is not null then
662 l_query := l_query || fnd_global.newline ||
663 get_bucket_clause(6, p_col_name, p_alias_name,
664 p_prefix, p_suffix, p_total_flag);
665 end if;
666 if p_bucket_rec.range7_name is not null then
667 l_query := l_query || fnd_global.newline ||
668 get_bucket_clause(7, p_col_name, p_alias_name,
669 p_prefix, p_suffix, p_total_flag);
670 end if;
671 if p_bucket_rec.range8_name is not null then
672 l_query := l_query || fnd_global.newline ||
673 get_bucket_clause(8, p_col_name, p_alias_name,
674 p_prefix, p_suffix, p_total_flag);
675 end if;
676 if p_bucket_rec.range9_name is not null then
677 l_query := l_query || fnd_global.newline ||
678 get_bucket_clause(9, p_col_name, p_alias_name,
679 p_prefix, p_suffix, p_total_flag);
680 end if;
681 if p_bucket_rec.range10_name is not null then
682 l_query := l_query || fnd_global.newline ||
683 get_bucket_clause(10, p_col_name, p_alias_name,
684 p_prefix, p_suffix, p_total_flag);
685 end if;
686 return l_query;
687 end get_bucket_outer_query;
688
689 /*****************************************************************
690 get_bucket_drill_url
691 ----------------------------------------------------------------
692 p_bucket_rec = the bucket rec returned from add_bucket_columns
693 p_alias_name = similar to 'POA_ATTRRIBUTE10'
694
695 Returns:
696 p_prefix || bucket_num || p_suffix || ' ' || p_alias_name || bucket_num
697 for each bucket defined in p_bucket rec.
698
699 Useful when you have buckets with drills to other reports that need to preserve the context of the bucket you drilled on like.
700
701 If you pass p_add_bucket_num = 'N', then the bucket number is NOT concatenated into the url, so return value is:
702 p_prefix || p_suffix || ' ' || p_alias_name || bucket_num
703
704 ***************************************************************************/
705 function get_bucket_drill_url
706 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
707 , p_alias_name in varchar2
708 , p_prefix in varchar2
709 , p_suffix in varchar2
710 , p_add_bucket_num in varchar2
711 )
712 return varchar2
713 is
714 l_query varchar2(10000);
715 begin
716 if p_bucket_rec.range1_name is not null then
717 l_query := get_bucket_url_clause(1, p_alias_name, p_prefix,
718 p_suffix, p_add_bucket_num);
719 end if;
720 if p_bucket_rec.range2_name is not null then
721 l_query := l_query || fnd_global.newline ||
722 get_bucket_url_clause(2, p_alias_name, p_prefix,
723 p_suffix, p_add_bucket_num);
724 end if;
725 if p_bucket_rec.range3_name is not null then
726 l_query := l_query || fnd_global.newline ||
727 get_bucket_url_clause(3, p_alias_name, p_prefix,
728 p_suffix, p_add_bucket_num);
729 end if;
730 if p_bucket_rec.range4_name is not null then
731 l_query := l_query || fnd_global.newline ||
732 get_bucket_url_clause(4, p_alias_name, p_prefix,
733 p_suffix, p_add_bucket_num);
734 end if;
735 if p_bucket_rec.range5_name is not null then
736 l_query := l_query || fnd_global.newline ||
737 get_bucket_url_clause(5, p_alias_name, p_prefix,
738 p_suffix, p_add_bucket_num);
739 end if;
740 if p_bucket_rec.range6_name is not null then
741 l_query := l_query || fnd_global.newline ||
742 get_bucket_url_clause(6, p_alias_name, p_prefix,
743 p_suffix, p_add_bucket_num);
744 end if;
745 if p_bucket_rec.range7_name is not null then
746 l_query := l_query || fnd_global.newline ||
747 get_bucket_url_clause(7, p_alias_name, p_prefix,
748 p_suffix, p_add_bucket_num);
749 end if;
750 if p_bucket_rec.range8_name is not null then
751 l_query := l_query || fnd_global.newline ||
752 get_bucket_url_clause(8, p_alias_name, p_prefix,
753 p_suffix, p_add_bucket_num);
754 end if;
755 if p_bucket_rec.range9_name is not null then
756 l_query := l_query || fnd_global.newline ||
757 get_bucket_url_clause(9, p_alias_name, p_prefix,
758 p_suffix, p_add_bucket_num);
759 end if;
760 if p_bucket_rec.range10_name is not null then
761 l_query := l_query || fnd_global.newline ||
762 get_bucket_url_clause(10, p_alias_name, p_prefix,
763 p_suffix, p_add_bucket_num);
764 end if;
765 return l_query;
766 end get_bucket_drill_url;
767
768
769 /* possible values for to_date_type:
770 XTD = period to date
771 XED = period to end of period (i.e. entire period)
772 YTD = year to date
773 ITD = inception to date
774
775 Most cases in DBI will use the default XTD.
776 */
777 PROCEDURE add_column(p_col_tbl IN OUT nocopy poa_dbi_col_tbl,
778 p_col_name IN VARCHAR2,
779 p_alias_name IN VARCHAR2,
780 p_grand_total IN VARCHAR2 := 'Y',
781 p_prior_code IN NUMBER := BOTH_PRIORS,
782 p_to_date_type IN VARCHAR2 := 'XTD')
783 IS
784
785 l_col_rec poa_dbi_col_rec;
786
787 BEGIN
788
789 l_col_rec.column_name := p_col_name;
790 l_col_rec.column_alias := p_alias_name;
791 l_col_rec.grand_total := p_grand_total;
792 l_col_rec.prior_code := p_prior_code;
793 l_col_rec.to_date_type := p_to_date_type;
794
795 p_col_tbl.extend;
796 p_col_tbl(p_col_tbl.count) := l_col_rec;
797
798 END add_column;
799
800 FUNCTION change_clause(cur_col IN VARCHAR2, prior_col IN VARCHAR2, change_type IN VARCHAR2 := 'NP')
801 RETURN VARCHAR2
802 IS
803
804 BEGIN
805 if(change_type = 'NP')then
806 return '(((nvl(' || cur_col || ',0) - ' || prior_col ||
807 ')/abs(decode(' || prior_col || ',0,null,'
808 || prior_col
809 || '))) * 100)';
810 end if;
811
812 return '(' || cur_col || ' - ' || prior_col || ')';
813 END change_clause;
814
815 FUNCTION rate_clause(numerator IN VARCHAR2, denominator IN VARCHAR2, rate_type IN VARCHAR2 := 'P')
816 RETURN VARCHAR2
817 IS
818 BEGIN
819 -- if rate is a ratio
820 if(rate_type = 'NP') then
821 return '(' || numerator || '/decode(' || denominator || ',0,null,'
822 || denominator || '))';
823 end if;
824
825 -- if rate is a percent
826 return '((nvl(' || numerator || ',0)/decode(' || denominator || ',0,null,'
827 || denominator || '))*100)';
828 END rate_clause;
829
830 FUNCTION get_commodity_sec_where(p_commodity_value VARCHAR2,
831 p_trend IN VARCHAR2 :='N') return VARCHAR2
832 IS
833
834 l_sec_where_clause VARCHAR2(1000):=null;
835
836 BEGIN
837
838 if (p_commodity_value is null or
839 p_commodity_value = '' or
840 p_commodity_value = '''''' or
841 p_commodity_value = 'All') then
842
843 l_sec_where_clause :=
844 ' fact.commodity_id in (select commodity_id
845 from po_commodity_grants sec,
846 fnd_menus menu
847 where sec.person_id = &FND_EMPLOYEE_ID and
848 menu.menu_name = ''PO_COMMODITY_MANAGER'' and
849 sec.menu_id = menu.menu_id)';
850 end if;
851 return l_sec_where_clause;
852
853 END get_commodity_sec_where;
854
855 FUNCTION get_in_commodity_sec_where(p_commodity_value VARCHAR2,
856 p_trend IN VARCHAR2 :='N') return VARCHAR2 IS
857 l_in_sec_where_clause VARCHAR2(1000):=null;
858 BEGIN
859 if(p_commodity_value is null or
860 p_commodity_value = '' or
861 p_commodity_value='''''' or
862 p_commodity_value = 'All') then
863
864 /* l_in_sec_where_clause :=
865 ' and fact.commodity_id = sec.commodity_id
866 and u.user_id = &FND_USER_ID
867 and sec.person_id = u.employee_id
868 and f.function_name =''POA_DBI_COMMODITY_RPTS_VIEW''
869 and poa_me.function_id = f.function_id
870 and sec.menu_id = poa_me.menu_id '; */
871 /* Added on 18-Jul-2005 -- As per Recommendation by Performance Team */
872 l_in_sec_where_clause := '
873 and fact.commodity_id in
874 (select sec.commodity_id
875 from po_commodity_grants sec,
876 fnd_menus menu
877 where sec.person_id = &FND_EMPLOYEE_ID
878 and menu.menu_name = ''PO_COMMODITY_MANAGER''
879 and sec.menu_id = menu.menu_id) ';
880
881
882 end if;
883 return l_in_sec_where_clause;
884 end;
885
886 FUNCTION get_ou_sec_where(p_ou_value VARCHAR2, p_ou_fact_col VARCHAR2,
887 p_trend IN VARCHAR2 :='N') return VARCHAR2
888 IS
889
890 l_sec_where_clause VARCHAR2(1000) := null;
891
892 BEGIN
893
894 if (p_ou_value is null or
895 p_ou_value = '' or
896 p_ou_value = '''''' or
897 p_ou_value = 'All') then
898
899 l_sec_where_clause :=
900 ' fact.' || p_ou_fact_col || ' in
901 (select orgl.organization_id
902 from per_organization_list orgl,
903 hr_organization_information orgi
904 where orgi.org_information1 = ''OPERATING_UNIT''
905 and orgl.organization_id = orgi.organization_id
906 and orgl.security_profile_id = &SEC_ID ) ';
907 end if;
908 return l_sec_where_clause;
909
910 END get_ou_sec_where;
911
912 FUNCTION get_in_ou_sec_where(p_ou_value VARCHAR2, p_ou_fact_col VARCHAR2,
913 p_use_bind IN VARCHAR2 :='Y') return VARCHAR2 IS
914 l_in_sec_where_clause VARCHAR2(1000) := null;
915 BEGIN
916 IF(p_ou_value is null or
917 p_ou_value = '' or
918 p_ou_value = '''''' or
919 p_ou_value = 'All') THEN
920 --Begin MOAC changes
921 IF poa_dbi_util_pkg.get_sec_profile <> -1 THEN
922 --End MOAC changes
923 /* l_in_sec_where_clause :=
924 ' and fact.' || p_ou_fact_col || ' = orgl.organization_id
925 and orgl.security_profile_id = '; */
926 l_in_sec_where_clause := '
927 and exists (select 1
928 from per_organization_list orgl
929 where fact.org_id = orgl.organization_id
930 and orgl.security_profile_id = ';
931
932 if(p_use_bind = 'Y') then
933 l_in_sec_where_clause := l_in_sec_where_clause || '&SEC_ID ' || ')';
934 else
935 l_in_sec_where_clause := l_in_sec_where_clause || poa_dbi_util_pkg.get_sec_profile || ')';
936 end if;
937 --Begin MOAC changes
938 ELSE
939 IF (p_use_bind = 'Y') THEN
940 l_in_sec_where_clause := ' and fact.org_id = ' ||'&ORG_ID ';
941 ELSE
942 l_in_sec_where_clause := ' and fact.org_id = ' || poa_dbi_util_pkg.get_ou_org_id ;
943 END IF ;
944 END IF ; ---poa_dbi_util_pkg.get_sec_profile <> -1
945 --End MOAC changes
946 end if;
947 return l_in_sec_where_clause;
948
949 END;
950
951 FUNCTION get_in_supplier_sec_where(p_supplier_value VARCHAR2) return VARCHAR2 IS
952 l_in_sec_where_clause VARCHAR2(1000) := null;
953 BEGIN
954 if(p_supplier_value is null or
955 p_supplier_value = '' or
956 p_supplier_value = '''''' or
957 p_supplier_value = 'All') then
958
959 l_in_sec_where_clause :=
960 ' and exists(select 1 from ak_web_user_sec_attr_values
961 isp, fnd_application appl
962 where
963 isp.web_user_id = &FND_USER_ID
964 and isp.number_value = fact.supplier_id
965 and isp.attribute_application_id = appl.application_id
966 and appl.application_short_name = ''POS'') ';
967
968 end if;
969 return l_in_sec_where_clause;
970
971 END get_in_supplier_sec_where;
972
973
974 PROCEDURE get_custom_trend_binds
975 ( p_xtd in varchar2
976 , p_comparison_type in varchar2
977 , x_custom_output out nocopy bis_query_attributes_tbl
978 , p_opening_balance in varchar2 := 'N'
979 )
980 IS
981
982 l_custom_rec BIS_QUERY_ATTRIBUTES;
983
984 BEGIN
985 if x_custom_output is null then
986 x_custom_output := bis_query_attributes_tbl();
987 end if;
988
989 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
990
991 if p_xtd not like 'RL%' then
992 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
993 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
994
995 if(p_xtd = 'YTD') then
996 l_custom_rec.attribute_value := 'TIME+FII_TIME_ENT_YEAR';
997 elsif(p_xtd = 'QTD') then
998 l_custom_rec.attribute_value := 'TIME+FII_TIME_ENT_QTR';
999 elsif(p_xtd = 'MTD') then
1000 l_custom_rec.attribute_value := 'TIME+FII_TIME_ENT_PERIOD';
1001 elsif(p_xtd = 'WTD') then
1002 l_custom_rec.attribute_value := 'TIME+FII_TIME_WEEK';
1003 elsif(p_xtd = 'DAY') then
1004 l_custom_rec.attribute_value := 'TIME+FII_TIME_DAY';
1005 else
1006 l_custom_rec.attribute_value := 'TIME+FII_TIME_WEEK';
1007 end if;
1008 x_custom_output.EXTEND;
1009 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1010 l_custom_rec.attribute_name := '&SPAN';
1011 if(p_xtd = 'YTD') then
1012 l_custom_rec.attribute_value := 365;
1013 elsif(p_xtd = 'QTD') then
1014 l_custom_rec.attribute_value := 90;
1015 elsif(p_xtd = 'MTD') then
1016 l_custom_rec.attribute_value := 30;
1017 elsif(p_xtd = 'WTD') then
1018 l_custom_rec.attribute_value := 7;
1019 elsif(p_xtd = 'DAY') then
1020 l_custom_rec.attribute_value := 1;
1021 else
1022 l_custom_rec.attribute_value := 7;
1023 end if;
1024
1025 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1026 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1027 x_custom_output.EXTEND;
1028 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1029 end if;
1030
1031 l_custom_rec.attribute_name := '&LAG';
1032 l_custom_rec.attribute_value := get_trend_lag(p_xtd, p_comparison_type);
1033 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1034 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1035 x_custom_output.EXTEND;
1036 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1037
1038 l_custom_rec.attribute_name := '&SEC_ID';
1039 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_sec_profile;
1040 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1041 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1042 x_custom_output.EXTEND;
1043 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1044
1045 l_custom_rec.attribute_name := '&FND_USER_ID';
1046 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_fnd_user_profile;
1047 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1048 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1049 x_custom_output.EXTEND;
1050 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1051
1052 l_custom_rec.attribute_name := '&FND_EMPLOYEE_ID';
1053 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_fnd_employee_profile;
1054 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1055 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1056 x_custom_output.EXTEND;
1057 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1058
1059 ---Begin MOAC changes
1060 l_custom_rec.attribute_name := '&ORG_ID';
1061 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_ou_org_id;
1062 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1063 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1064 x_custom_output.EXTEND;
1065 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1066 ---End MOAC changes
1067
1068 if p_xtd like 'RL%' then
1069 l_custom_rec.attribute_name := '&RLX_RSD_OFFSET';
1070 -- the bind variable calculates the offset from the as of date
1071 -- (current or prior) to the start of the first rolling period
1072 -- this emulates &BIS_CURRENT_REPORT_START_DATE for XTD periods
1073 l_custom_rec.attribute_value := case p_xtd
1074 when 'RLW' then (7*13)-1
1075 when 'RLM' then (30*12)-1
1076 when 'RLQ' then
1077 case p_comparison_type
1078 when 'S' then (90*8)-1
1079 else (90*4)-1
1080 end
1081 when 'RLY' then (365*4)-1
1082 else 0
1083 end;
1084 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1085 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1086 x_custom_output.extend;
1087 x_custom_output(x_custom_output.count) := l_custom_rec;
1088
1089 l_custom_rec.attribute_name := '&RLX_ROWS_OFFSET';
1090 -- this bind variable calculates the offset for the number
1091 -- of rows the the in-line view for needs to return
1092 l_custom_rec.attribute_value := case p_xtd
1093 when 'RLW' then -13
1094 when 'RLM' then -12
1095 when 'RLQ' then
1096 case p_comparison_type
1097 when 'S' then -8
1098 else -4
1099 end
1100 when 'RLY' then -4
1101 end;
1102 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1103 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1104 x_custom_output.extend;
1105 x_custom_output(x_custom_output.count) := l_custom_rec;
1106
1107 l_custom_rec.attribute_name := '&RLX_DAYS';
1108 -- this bind variable returns the number of days in a rolling period
1109 l_custom_rec.attribute_value := case p_xtd
1110 when 'RLW' then 7
1111 when 'RLM' then 30
1112 when 'RLQ' then 90
1113 when 'RLY' then 365
1114 end;
1115 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1116 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1117 x_custom_output.extend;
1118 x_custom_output(x_custom_output.count) := l_custom_rec;
1119
1120 l_custom_rec.attribute_name := '&RLX_DAYS_TO_START';
1121 -- this bind variable returns the number of days back to the start of
1122 -- rolling period for the end of the period (&RLX_DAYS -1)
1123 l_custom_rec.attribute_value := case p_xtd
1124 when 'RLW' then 6
1125 when 'RLM' then 29
1126 when 'RLQ' then 89
1127 when 'RLY' then 364
1128 end;
1129 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1130 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1131 x_custom_output.extend;
1132 x_custom_output(x_custom_output.count) := l_custom_rec;
1133
1134 end if;
1135
1136 END get_custom_trend_binds;
1137
1138 PROCEDURE get_custom_status_binds(x_custom_output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1139 IS
1140
1141 l_custom_rec BIS_QUERY_ATTRIBUTES;
1142
1143 BEGIN
1144
1145 if x_custom_output is null then
1146 x_custom_output := bis_query_attributes_tbl();
1147 end if;
1148
1149 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1150
1151 l_custom_rec.attribute_name := '&SEC_ID';
1152 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_sec_profile;
1153 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1154 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1155 x_custom_output.EXTEND;
1156 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1157
1158 l_custom_rec.attribute_name := '&FND_USER_ID';
1159 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_fnd_user_profile;
1160 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1161 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1162 x_custom_output.EXTEND;
1163 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1164
1165 l_custom_rec.attribute_name := '&FND_EMPLOYEE_ID';
1166 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_fnd_employee_profile;
1167 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1168 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1169 x_custom_output.EXTEND;
1170 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1171
1172 ---Begin MOAC changes
1173 l_custom_rec.attribute_name := '&ORG_ID';
1174 l_custom_rec.attribute_value := poa_dbi_util_pkg.get_ou_org_id;
1175 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1176 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1177 x_custom_output.EXTEND;
1178 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1179 ---End MOAC changes
1180
1181 END get_custom_status_binds;
1182
1183
1184 FUNCTION get_trend_lag(p_xtd IN varchar2, p_comparison_type IN varchar2)
1185 return number
1186 IS
1187
1188 BEGIN
1189
1190 if(p_comparison_type = 'S') then
1191 return 1;
1192 else
1193 return case p_xtd
1194 when 'YTD' then 1
1195 when 'QTD' then 4
1196 when 'MTD' then 12
1197 when 'WTD' then 13
1198 when 'DAY' then 7
1199 when 'RLY' then 4
1200 when 'RLQ' then 4
1201 when 'RLM' then 12
1202 when 'RLW' then 13
1203 end;
1204 end if;
1205
1206 END get_trend_lag;
1207
1208 FUNCTION get_report_start_date
1209 ( p_period_type in varchar2
1210 , p_prior in varchar2 := 'N'
1211 )
1212 return varchar2
1213 IS
1214
1215 l_cur_prior varchar2(40) := '&BIS_CURRENT_EFFECTIVE_END_DATE';
1216
1217 BEGIN
1218
1219 if p_prior = 'Y' then
1220 l_cur_prior := '&BIS_PREVIOUS_EFFECTIVE_END_DATE';
1221 end if;
1222
1223 return l_cur_prior || ' - &RLX_RSD_OFFSET';
1224
1225 END get_report_start_date;
1226
1227 PROCEDURE get_custom_balance_binds
1228 ( p_custom_output in out nocopy bis_query_attributes_tbl
1229 , p_balance_fact in varchar2
1230 , p_xtd in varchar2 := null
1231 )
1232 IS
1233
1234 l_custom_rec bis_query_attributes;
1235
1236 BEGIN
1237
1238 if p_custom_output is null then
1239 p_custom_output := bis_query_attributes_tbl();
1240 end if;
1241 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
1242
1243 l_custom_rec.attribute_name := '&LAST_COLLECTION';
1244 l_custom_rec.attribute_value := to_char(fnd_date.displayDT_to_date(bis_collection_utilities.get_last_refresh_period(upper(p_balance_fact))),'dd/mm/yyyy');
1245 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1246 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1247 p_custom_output.extend;
1248 p_custom_output(p_custom_output.count) := l_custom_rec;
1249
1250 -- Balance report in XTD Model
1251 IF(p_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
1252 THEN
1253 l_custom_rec.attribute_name := '&YTD_NESTED_PATTERN';
1254 l_custom_rec.attribute_value := 1143;
1255 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1256 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1257 p_custom_output.extend;
1258 p_custom_output(p_custom_output.count) := l_custom_rec;
1259 END IF;
1260
1261
1262 END get_custom_balance_binds;
1263
1264 PROCEDURE get_custom_rolling_binds
1265 ( p_custom_output in out nocopy bis_query_attributes_tbl
1266 , p_xtd in varchar2
1267 )
1268 IS
1269
1270 l_custom_rec bis_query_attributes;
1271
1272 BEGIN
1273
1274 if p_custom_output is null then
1275 p_custom_output := bis_query_attributes_tbl();
1276 end if;
1277
1278 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
1279
1280 l_custom_rec.attribute_name := '&RLX_NESTED_PATTERN';
1281 l_custom_rec.attribute_value := case p_xtd
1282 when 'RLW' then 1024
1283 when 'RLM' then 2048
1284 when 'RLQ' then 4096
1285 when 'RLY' then 8192
1286 end;
1287 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1288 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
1289 p_custom_output.extend;
1290 p_custom_output(p_custom_output.count) := l_custom_rec;
1291
1292 END get_custom_rolling_binds;
1293
1294 procedure bind_low_high
1295 ( p_param in bis_pmv_page_parameter_tbl
1296 , p_short_name in varchar2
1297 , p_dim_level in varchar2
1298 , p_low in varchar2
1299 , p_high in varchar2
1300 , p_custom_output in out nocopy bis_query_attributes_tbl
1301 )
1302 is
1303
1304 l_range_low number;
1305 l_range_high number;
1306
1307 l_range_id number;
1308 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
1309 l_return_status varchar2(3);
1310 l_error_tbl bis_utilities_pub.error_tbl_type;
1311
1312 l_custom_rec BIS_QUERY_ATTRIBUTES;
1313
1314 begin
1315
1316 for i in 1..p_param.count loop
1317 if p_param(i).parameter_name = p_dim_level then
1318 l_range_id := replace(p_param(i).parameter_id,'''',null);
1319 end if;
1320 end loop;
1321
1322 if l_range_id is null then
1323 return;
1324 end if;
1325
1326 bis_bucket_pub.retrieve_bis_bucket
1327 ( p_short_name => p_short_name
1328 , x_bis_bucket_rec => l_bucket_rec
1329 , x_return_status => l_return_status
1330 , x_error_tbl => l_error_tbl
1331 );
1332
1333 if l_return_status = 'S' then
1334
1335 if l_range_id = 1 then
1336 l_range_low := l_bucket_rec.range1_low;
1337 l_range_high := l_bucket_rec.range1_high;
1338 elsif l_range_id = 2 then
1339 l_range_low := l_bucket_rec.range2_low;
1340 l_range_high := l_bucket_rec.range2_high;
1341 elsif l_range_id = 3 then
1342 l_range_low := l_bucket_rec.range3_low;
1343 l_range_high := l_bucket_rec.range3_high;
1344 elsif l_range_id = 4 then
1345 l_range_low := l_bucket_rec.range4_low;
1346 l_range_high := l_bucket_rec.range4_high;
1347 elsif l_range_id = 5 then
1348 l_range_low := l_bucket_rec.range5_low;
1349 l_range_high := l_bucket_rec.range5_high;
1350 elsif l_range_id = 6 then
1351 l_range_low := l_bucket_rec.range6_low;
1352 l_range_high := l_bucket_rec.range6_high;
1353 elsif l_range_id = 7 then
1354 l_range_low := l_bucket_rec.range7_low;
1355 l_range_high := l_bucket_rec.range7_high;
1356 elsif l_range_id = 8 then
1357 l_range_low := l_bucket_rec.range8_low;
1358 l_range_high := l_bucket_rec.range8_high;
1359 elsif l_range_id = 9 then
1360 l_range_low := l_bucket_rec.range9_low;
1361 l_range_high := l_bucket_rec.range9_high;
1362 elsif l_range_id = 10 then
1363 l_range_low := l_bucket_rec.range10_low;
1364 l_range_high := l_bucket_rec.range10_high;
1365 end if;
1366 end if;
1367
1368 if p_custom_output is null then
1369 p_custom_output := bis_query_attributes_tbl();
1370 end if;
1371
1372 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1373
1374 l_custom_rec.attribute_name := p_low;
1375 l_custom_rec.attribute_value := l_range_low;
1376 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1377 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1378 p_custom_output.extend;
1379 p_custom_output(p_custom_output.count) := l_custom_rec;
1380
1381 l_custom_rec.attribute_name := p_high;
1382 l_custom_rec.attribute_value := l_range_high;
1383 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1384 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1385 p_custom_output.extend;
1386 p_custom_output(p_custom_output.count) := l_custom_rec;
1387
1388 end bind_low_high;
1389
1390 PROCEDURE get_custom_day_binds(p_custom_output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
1391 p_as_of_date IN DATE,
1392 p_comparison_type IN VARCHAR2)
1393 IS
1394 l_custom_rec BIS_QUERY_ATTRIBUTES;
1395 l_prev_as_of_date DATE;
1396
1397 BEGIN
1398
1399 RETURN ;
1400
1401 if p_custom_output is null then
1402 p_custom_output := bis_query_attributes_tbl();
1403 end if;
1404
1405 IF(p_comparison_type = 'S') THEN
1406 l_prev_as_of_date := p_as_of_date - 1;
1407 ELSIF (p_comparison_type = 'Y') THEN
1408 l_prev_as_of_date := FII_TIME_API.ent_sd_lyr_end(p_as_of_date);
1409 ELSIF (p_comparison_type = 'W') THEN
1410 l_prev_as_of_date := p_as_of_date-7;
1411 END IF;
1412
1413 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1414
1415 l_custom_rec.attribute_name := '&BIS_CURRENT_REPORT_START_DATE';
1416 l_custom_rec.attribute_value := to_char(p_as_of_date-6,'dd/mm/yyyy');
1417 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1418 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1419 p_custom_output.EXTEND;
1420 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1421
1422 l_custom_rec.attribute_name := '&BIS_PREVIOUS_REPORT_START_DATE';
1423 l_custom_rec.attribute_value := to_char(l_prev_as_of_date-6,'dd/mm/yyyy');
1424 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1425 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1426 p_custom_output.EXTEND;
1427 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1428
1429 l_custom_rec.attribute_name := '&BIS_CURRENT_EFFECTIVE_END_DATE';
1430 l_custom_rec.attribute_value := to_char(p_as_of_date,'dd/mm/yyyy');
1431 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1432 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1433 p_custom_output.EXTEND;
1434 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1435
1436 l_custom_rec.attribute_name := '&BIS_PREVIOUS_EFFECTIVE_END_DATE';
1437 l_custom_rec.attribute_value := to_char(l_prev_as_of_date,'dd/mm/yyyy');
1438 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1439 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1440 p_custom_output.EXTEND;
1441 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1442
1443 END get_custom_day_binds;
1444
1445
1446 ---Begin MOAC changes
1447
1448 FUNCTION get_ou_org_id RETURN NUMBER
1449 IS
1450 BEGIN
1451 -- IF NVL(g_org_id , -1) = -1
1452 -- THEN
1453 -- g_org_id := NVL(fnd_profile.value('ORG_ID'), -1);
1454 -- END IF ;
1455 RETURN NVL(fnd_profile.value('ORG_ID'), -1);
1456 END get_ou_org_id;
1457
1458 ---End MOAC changes
1459
1460 ---Begin Changes for spend trend graph
1461 FUNCTION get_rolling_inline_view
1462 return varchar2
1463 IS
1464
1465 BEGIN
1466 return ' ( with rolling_cal as ( select start_date, end_date, end_date report_date, to_char(end_date,''dd-Mon-yy'') name, ordinal, current_ind_sum from '
1467 || '( select '
1468 || 'decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
1469 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
1470 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) '
1471 || '- &RLX_DAYS_TO_START start_date'
1472 || ', decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
1473 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
1474 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) end_date'
1475 || ', decode(&BIS_TIME_COMPARISON_TYPE,''SEQUENTIAL'',-1,decode(t.current_ind,4,0,2,1,2)) ordinal , SUM(current_ind) current_ind_sum '
1476 || 'from biv_trend_rpt t '
1477 || 'where t.offset > &RLX_ROWS_OFFSET '
1478 || 'group by decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
1479 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
1480 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) '
1481 || '- &RLX_DAYS_TO_START '
1482 || ', decode(t.current_ind, 2, &BIS_PREVIOUS_EFFECTIVE_END_DATE'
1483 || ', 4, (&BIS_CURRENT_EFFECTIVE_START_DATE - 1)'
1484 || ', &BIS_CURRENT_EFFECTIVE_END_DATE)+(t.offset*&RLX_DAYS) '
1485 || ', decode(&BIS_TIME_COMPARISON_TYPE,''SEQUENTIAL'',-1,decode(t.current_ind,4,0,2,1,2)) '
1486 || ' )'
1487 || ' )';
1488
1489 END get_rolling_inline_view ;
1490
1491 ---End Changes for spend trend graph
1492
1493
1494 END poa_dbi_util_pkg;