eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.POA_DBI_TEMPLATE_PKG dependencies on POA_DBI_UTIL_PKG

Line 11: p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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,

Line 46: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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

Line 48: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

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'

Line 53: , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL)

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);

Line 73: l_col_calc_tbl poa_dbi_util_pkg.poa_dbi_col_calc_tbl;

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')

Line 271: p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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);

Line 312: p_col_names IN poa_dbi_util_pkg.poa_dbi_col_tbl

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

Line 313: , x_col_calc_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_calc_tbl

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

Line 319: l_col_calc poa_dbi_util_pkg.poa_dbi_col_calc_rec;

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);

Line 327: x_col_calc_tbl := poa_dbi_util_pkg.POA_DBI_COL_CALC_TBL();

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

Line 337: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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 || ',';

Line 347: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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);

Line 350: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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 || ',';

Line 356: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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
359: l_cur_date_clause := g_c_as_of_date_balance || ',';
360: l_prev_date_clause := g_p_as_of_date_balance || ',';

Line 362: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

358: elsif p_col_names(i).to_date_type = 'BAL' then
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 || ',';
362: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap
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 := '';

Line 392: IF (p_col_names(i).prior_code <> poa_dbi_util_pkg.no_priors)

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;

Line 409: IF (p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev)

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;

Line 426: if p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR then

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 := '';

Line 457: IF ( p_col_names(i).prior_code = poa_dbi_util_pkg.both_priors

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;

Line 458: OR p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev

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;

Line 459: OR p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )

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';

Line 475: IF (p_col_names(i).prior_code = poa_dbi_util_pkg.prev_prev)

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';

Line 491: IF (p_col_names(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR) then

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(';

Line 510: p_mv IN poa_dbi_util_pkg.poa_dbi_mv_tbl

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

Line 517: l_col_list poa_dbi_util_pkg.poa_dbi_col_list;

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);

Line 523: l_query_rec poa_dbi_util_pkg.poa_dbi_union_query_rec;

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' ;

Line 524: l_query_tbl poa_dbi_util_pkg.poa_dbi_union_query_tbl;

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();

Line 528: l_query_tbl := poa_dbi_util_pkg.poa_dbi_union_query_tbl();

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;
531: l_query_tbl(l_query_tbl.count).in_union_sel := 'cal.name cal_name'
532: || fnd_global.newline

Line 555: l_col_list := poa_dbi_util_pkg.poa_dbi_col_list();

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

Line 610: poa_dbi_util_pkg.get_rolling_inline_view || ' select' || fnd_global.newline

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;

Line 639: p_mv in poa_dbi_util_pkg.poa_dbi_mv_tbl

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

Line 640: , p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl

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'

Line 649: l_mv poa_dbi_util_pkg.poa_dbi_mv_tbl;

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);

Line 650: l_col_calc poa_dbi_util_pkg.poa_dbi_col_calc_tbl;

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);

Line 655: l_query_rec poa_dbi_util_pkg.poa_dbi_union_query_rec;

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) := '';

Line 656: l_query_tbl poa_dbi_util_pkg.poa_dbi_union_query_tbl;

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) := '';

Line 661: l_col_name poa_dbi_util_pkg.poa_dbi_col_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();

Line 662: l_join_tables poa_dbi_util_pkg.poa_dbi_join_tbl;

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();

Line 665: l_join_tables := poa_dbi_util_pkg.poa_dbi_join_tbl();

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;

Line 666: l_query_tbl := poa_dbi_util_pkg.poa_dbi_union_query_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 := '';

Line 674: l_col_name := poa_dbi_util_pkg.poa_dbi_col_tbl();

670: l_query_tbl(l_query_tbl.count).in_union_sel := '';
671: end loop;
672:
673: for i in 1 ..p_mv.count loop -- main loop
674: l_col_name := poa_dbi_util_pkg.poa_dbi_col_tbl();
675:
676: get_status_col_calc(p_mv(i).mv_col
677: , l_col_calc
678: , l_inlist_bmap

Line 810: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

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')

Line 812: , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL

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

Line 832: l_col_list poa_dbi_util_pkg.poa_dbi_col_list;

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:

Line 979: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal,p_called_by_union => p_called_by_union )

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

Line 989: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd, p_called_by_union => p_called_by_union)

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

Line 1006: || poa_dbi_util_pkg.get_calendar_table (p_xtd, p_called_by_union => p_called_by_union)

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

Line 1027: || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal, p_called_by_union => p_called_by_union )

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: || '

Line 1035: || poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N', p_called_by_union => p_called_by_union )

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

Line 1041: poa_dbi_util_pkg.get_report_start_date(p_xtd)

1037: || '
1038: where cal.start_date between '
1039: || case
1040: when p_xtd like 'RL%' then
1041: poa_dbi_util_pkg.get_report_start_date(p_xtd)
1042: else
1043: '&BIS_CURRENT_REPORT_START_DATE'
1044: end
1045: || ' and &BIS_CURRENT_ASOF_DATE

Line 1057: p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

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

Line 1063: , x_col_list OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_list)

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);

Line 1073: x_col_list := poa_dbi_util_pkg.POA_DBI_COL_LIST();

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

Line 1088: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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 ';

Line 1093: l_c_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd);

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';

Line 1095: l_p_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y');

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);

Line 1098: x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap

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 ';

Line 1103: l_c_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd);

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)';
1107: l_order_by := 'n.start_date';

Line 1105: l_p_start_date := poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y');

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)';
1107: l_order_by := 'n.start_date';
1108: x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap
1109: , g_inlist_bal);

Line 1108: x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap

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)';
1107: l_order_by := 'n.start_date';
1108: x_inlist_bmap := poa_dbi_util_pkg.bitor( x_inlist_bmap
1109: , g_inlist_bal);
1110: ELSIF p_col_name(i).to_date_type = 'XTD' THEN
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) ';

Line 1118: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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) ';

Line 1128: x_inlist_bmap := poa_dbi_util_pkg.bitor (x_inlist_bmap

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

Line 1154: IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)

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 '

Line 1179: if p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR

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')

Line 1224: IF (p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors)

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: || ',

Line 1248: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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
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

Line 1250: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

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
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
1253: , p_group_by IN VARCHAR2
1254: , p_from_clause IN VARCHAR2)

Line 1335: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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

Line 1337: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

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

Line 1443: p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

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);