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_CUT_PKG dependencies on POA_DBI_UTIL_PKG

Line 48: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

44: l_comparison_type varchar2(1) := 'Y';
45: l_nested_pattern number;
46: l_cur_suffix varchar2(2);
47: l_custom_sql varchar2(10000);
48: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
49: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
50: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
51: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
52: l_view_by_value VARCHAR2(100);

Line 49: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

45: l_nested_pattern number;
46: l_cur_suffix varchar2(2);
47: l_custom_sql varchar2(10000);
48: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
49: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
50: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
51: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
52: l_view_by_value VARCHAR2(100);
53: l_where_clause VARCHAR2(2000);

Line 50: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

46: l_cur_suffix varchar2(2);
47: l_custom_sql varchar2(10000);
48: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
49: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
50: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
51: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
52: l_view_by_value VARCHAR2(100);
53: l_where_clause VARCHAR2(2000);
54: l_where_clause2 VARCHAR2(2000);

Line 51: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

47: l_custom_sql varchar2(10000);
48: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
49: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
50: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
51: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
52: l_view_by_value VARCHAR2(100);
53: l_where_clause VARCHAR2(2000);
54: l_where_clause2 VARCHAR2(2000);
55: l_mv VARCHAR2(30);

Line 59: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

55: l_mv VARCHAR2(30);
56: l_mv2 VARCHAR2(30);
57: l_sec_context varchar2(10);
58: l_use_only_agg_mv varchar2(1);
59: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
60:
61: BEGIN
62: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
63: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

Line 62: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

58: l_use_only_agg_mv varchar2(1);
59: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
60:
61: BEGIN
62: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
63: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
64:
65: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
66: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 63: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

59: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
60:
61: BEGIN
62: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
63: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
64:
65: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
66: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
67: poa_dbi_sutil_pkg.process_parameters(

Line 89: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

85: p_version => '6.0',
86: p_role => 'COM',
87: p_mv_set => 'PODCUT');
88:
89: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
90: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93:

Line 90: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

86: p_role => 'COM',
87: p_mv_set => 'PODCUT');
88:
89: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
90: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93:
94: if(l_view_by = 'ITEM+POA_ITEMS') then

Line 91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');

87: p_mv_set => 'PODCUT');
88:
89: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
90: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93:
94: if(l_view_by = 'ITEM+POA_ITEMS') then
95: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

88:
89: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
90: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93:
94: if(l_view_by = 'ITEM+POA_ITEMS') then
95: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
96: end if;

Line 95: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

91: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93:
94: if(l_view_by = 'ITEM+POA_ITEMS') then
95: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
96: end if;
97:
98: l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
99: ' || poa_dbi_template_pkg.status_sql(

Line 132: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

128: p_version => '8.0',
129: p_role => 'COM',
130: p_mv_set => 'PODCUTA');
131:
132: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
133: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136:

Line 133: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

129: p_role => 'COM',
130: p_mv_set => 'PODCUTA');
131:
132: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
133: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136:
137: if(l_view_by = 'ITEM+POA_ITEMS') then

Line 134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');

130: p_mv_set => 'PODCUTA');
131:
132: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
133: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136:
137: if(l_view_by = 'ITEM+POA_ITEMS') then
138: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

131:
132: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
133: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136:
137: if(l_view_by = 'ITEM+POA_ITEMS') then
138: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
139: end if;

Line 138: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

134: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136:
137: if(l_view_by = 'ITEM+POA_ITEMS') then
138: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
139: end if;
140:
141: /*check if we can get everything from aggregated mv*/
142: l_use_only_agg_mv := 'Y';

Line 174: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

170: p_version => '8.0',
171: p_role => 'COM',
172: p_mv_set => 'PODCUTB');
173:
174: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
175: l_mv_tbl.extend;
176: l_mv_tbl(1).mv_name := l_mv;
177: l_mv_tbl(1).mv_col := l_col_tbl;
178: l_mv_tbl(1).mv_where := l_where_clause;

Line 332: poa_dbi_util_pkg.rate_clause('c_contract_amt', 'c_purchase_amt')

328:
329: l_sel_clause := l_sel_clause || '
330: nvl(c_purchase_amt,0) POA_MEASURE1,
331: ' ||
332: poa_dbi_util_pkg.rate_clause('c_contract_amt', 'c_purchase_amt')
333: || ' POA_PERCENT1,
334: ' ||
335: poa_dbi_util_pkg.change_clause(
336: poa_dbi_util_pkg.rate_clause( 'c_contract_amt',

Line 335: poa_dbi_util_pkg.change_clause(

331: ' ||
332: poa_dbi_util_pkg.rate_clause('c_contract_amt', 'c_purchase_amt')
333: || ' POA_PERCENT1,
334: ' ||
335: poa_dbi_util_pkg.change_clause(
336: poa_dbi_util_pkg.rate_clause( 'c_contract_amt',
337: 'c_purchase_amt'),
338: poa_dbi_util_pkg.rate_clause( 'p_contract_amt',
339: 'p_purchase_amt'),

Line 336: poa_dbi_util_pkg.rate_clause( 'c_contract_amt',

332: poa_dbi_util_pkg.rate_clause('c_contract_amt', 'c_purchase_amt')
333: || ' POA_PERCENT1,
334: ' ||
335: poa_dbi_util_pkg.change_clause(
336: poa_dbi_util_pkg.rate_clause( 'c_contract_amt',
337: 'c_purchase_amt'),
338: poa_dbi_util_pkg.rate_clause( 'p_contract_amt',
339: 'p_purchase_amt'),
340: 'P')

Line 338: poa_dbi_util_pkg.rate_clause( 'p_contract_amt',

334: ' ||
335: poa_dbi_util_pkg.change_clause(
336: poa_dbi_util_pkg.rate_clause( 'c_contract_amt',
337: 'c_purchase_amt'),
338: poa_dbi_util_pkg.rate_clause( 'p_contract_amt',
339: 'p_purchase_amt'),
340: 'P')
341: || ' POA_MEASURE2,
342: ' ||

Line 343: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')

339: 'p_purchase_amt'),
340: 'P')
341: || ' POA_MEASURE2,
342: ' ||
343: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
344: || ' POA_PERCENT2,
345: ' ||
346: poa_dbi_util_pkg.change_clause(
347: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),

Line 346: poa_dbi_util_pkg.change_clause(

342: ' ||
343: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
344: || ' POA_PERCENT2,
345: ' ||
346: poa_dbi_util_pkg.change_clause(
347: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),
348: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt'),
349: 'P')
350: || ' POA_MEASURE3,

Line 347: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),

343: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
344: || ' POA_PERCENT2,
345: ' ||
346: poa_dbi_util_pkg.change_clause(
347: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),
348: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt'),
349: 'P')
350: || ' POA_MEASURE3,
351: nvl(c_purchase_amt_total,0) POA_MEASURE5,

Line 348: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt'),

344: || ' POA_PERCENT2,
345: ' ||
346: poa_dbi_util_pkg.change_clause(
347: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),
348: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt'),
349: 'P')
350: || ' POA_MEASURE3,
351: nvl(c_purchase_amt_total,0) POA_MEASURE5,
352: ' ||

Line 353: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',

349: 'P')
350: || ' POA_MEASURE3,
351: nvl(c_purchase_amt_total,0) POA_MEASURE5,
352: ' ||
353: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
354: 'c_purchase_amt_total')
355: || ' POA_MEASURE6,
356: ' ||
357: poa_dbi_util_pkg.change_clause(

Line 357: poa_dbi_util_pkg.change_clause(

353: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
354: 'c_purchase_amt_total')
355: || ' POA_MEASURE6,
356: ' ||
357: poa_dbi_util_pkg.change_clause(
358: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
359: 'c_purchase_amt_total'),
360: poa_dbi_util_pkg.rate_clause( 'p_contract_amt_total',
361: 'p_purchase_amt_total'),

Line 358: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',

354: 'c_purchase_amt_total')
355: || ' POA_MEASURE6,
356: ' ||
357: poa_dbi_util_pkg.change_clause(
358: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
359: 'c_purchase_amt_total'),
360: poa_dbi_util_pkg.rate_clause( 'p_contract_amt_total',
361: 'p_purchase_amt_total'),
362: 'P')

Line 360: poa_dbi_util_pkg.rate_clause( 'p_contract_amt_total',

356: ' ||
357: poa_dbi_util_pkg.change_clause(
358: poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
359: 'c_purchase_amt_total'),
360: poa_dbi_util_pkg.rate_clause( 'p_contract_amt_total',
361: 'p_purchase_amt_total'),
362: 'P')
363: || ' POA_MEASURE7,
364: ' ||

Line 365: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',

361: 'p_purchase_amt_total'),
362: 'P')
363: || ' POA_MEASURE7,
364: ' ||
365: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
366: 'c_purchase_amt_total')
367: || ' POA_MEASURE8,
368: ' ||
369: poa_dbi_util_pkg.change_clause(

Line 369: poa_dbi_util_pkg.change_clause(

365: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
366: 'c_purchase_amt_total')
367: || ' POA_MEASURE8,
368: ' ||
369: poa_dbi_util_pkg.change_clause(
370: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
371: 'c_purchase_amt_total'),
372: poa_dbi_util_pkg.rate_clause( 'p_n_contract_amt_total',
373: 'p_purchase_amt_total'),

Line 370: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',

366: 'c_purchase_amt_total')
367: || ' POA_MEASURE8,
368: ' ||
369: poa_dbi_util_pkg.change_clause(
370: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
371: 'c_purchase_amt_total'),
372: poa_dbi_util_pkg.rate_clause( 'p_n_contract_amt_total',
373: 'p_purchase_amt_total'),
374: 'P')

Line 372: poa_dbi_util_pkg.rate_clause( 'p_n_contract_amt_total',

368: ' ||
369: poa_dbi_util_pkg.change_clause(
370: poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
371: 'c_purchase_amt_total'),
372: poa_dbi_util_pkg.rate_clause( 'p_n_contract_amt_total',
373: 'p_purchase_amt_total'),
374: 'P')
375: || ' POA_MEASURE9 ';
376: return l_sel_clause;

Line 394: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

390: l_comparison_type varchar2(1) := 'Y';
391: l_nested_pattern number;
392: l_cur_suffix varchar2(2);
393: l_custom_sql varchar2(10000);
394: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
395: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
396: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
397: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
398: l_view_by_value VARCHAR2(100);

Line 395: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

391: l_nested_pattern number;
392: l_cur_suffix varchar2(2);
393: l_custom_sql varchar2(10000);
394: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
395: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
396: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
397: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
398: l_view_by_value VARCHAR2(100);
399: l_where_clause VARCHAR2(2000);

Line 396: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

392: l_cur_suffix varchar2(2);
393: l_custom_sql varchar2(10000);
394: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
395: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
396: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
397: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
398: l_view_by_value VARCHAR2(100);
399: l_where_clause VARCHAR2(2000);
400: l_where_clause2 VARCHAR2(2000);

Line 397: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

393: l_custom_sql varchar2(10000);
394: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
395: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
396: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
397: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
398: l_view_by_value VARCHAR2(100);
399: l_where_clause VARCHAR2(2000);
400: l_where_clause2 VARCHAR2(2000);
401: l_mv VARCHAR2(30);

Line 405: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

401: l_mv VARCHAR2(30);
402: l_mv2 VARCHAR2(30);
403: l_sec_context varchar2(10);
404: l_use_only_agg_mv varchar2(1);
405: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
406: BEGIN
407: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
408: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
409:

Line 407: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

403: l_sec_context varchar2(10);
404: l_use_only_agg_mv varchar2(1);
405: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
406: BEGIN
407: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
408: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
409:
410: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
411: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 408: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

404: l_use_only_agg_mv varchar2(1);
405: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
406: BEGIN
407: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
408: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
409:
410: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
411: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
412: poa_dbi_sutil_pkg.process_parameters(

Line 434: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

430: p_version => '6.0',
431: p_role => 'COM',
432: p_mv_set => 'PODCUT');
433:
434: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
435: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
436:
437: if(l_view_by = 'ITEM+POA_ITEMS') then
438: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 435: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

431: p_role => 'COM',
432: p_mv_set => 'PODCUT');
433:
434: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
435: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
436:
437: if(l_view_by = 'ITEM+POA_ITEMS') then
438: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
439: end if;

Line 438: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

434: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
435: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
436:
437: if(l_view_by = 'ITEM+POA_ITEMS') then
438: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
439: end if;
440:
441: l_query := get_con_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
442: ' ||poa_dbi_template_pkg.status_sql(

Line 475: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

471: p_version => '8.0',
472: p_role => 'COM',
473: p_mv_set => 'PODCUTA');
474:
475: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
476: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
477:
478: if(l_view_by = 'ITEM+POA_ITEMS') then
479: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 476: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

472: p_role => 'COM',
473: p_mv_set => 'PODCUTA');
474:
475: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
476: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
477:
478: if(l_view_by = 'ITEM+POA_ITEMS') then
479: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
480: end if;

Line 479: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

475: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
476: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
477:
478: if(l_view_by = 'ITEM+POA_ITEMS') then
479: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
480: end if;
481:
482: /*check if we can get everything from aggregated mv*/
483: l_use_only_agg_mv := 'Y';

Line 515: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

511: p_version => '8.0',
512: p_role => 'COM',
513: p_mv_set => 'PODCUTB');
514:
515: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
516: l_mv_tbl.extend;
517: l_mv_tbl(1).mv_name := l_mv;
518: l_mv_tbl(1).mv_col := l_col_tbl;
519: l_mv_tbl(1).mv_where := l_where_clause;

Line 664: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,

660: end if;
661:
662: l_sel_clause := l_sel_clause || '
663: nvl(c_purchase_amt,0) POA_MEASURE1,
664: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
665: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
666: nvl(c_contract_amt,0) POA_MEASURE3,
667: nvl(c_purchase_amt_total,0) POA_MEASURE5,
668: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,

Line 665: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,

661:
662: l_sel_clause := l_sel_clause || '
663: nvl(c_purchase_amt,0) POA_MEASURE1,
664: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
665: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
666: nvl(c_contract_amt,0) POA_MEASURE3,
667: nvl(c_purchase_amt_total,0) POA_MEASURE5,
668: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,
669: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total'), poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE7,

Line 668: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,

664: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
665: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
666: nvl(c_contract_amt,0) POA_MEASURE3,
667: nvl(c_purchase_amt_total,0) POA_MEASURE5,
668: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,
669: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total'), poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE7,
670: nvl(c_contract_amt_total,0) POA_MEASURE8';
671: return l_sel_clause;
672: END;

Line 669: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total'), poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE7,

665: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
666: nvl(c_contract_amt,0) POA_MEASURE3,
667: nvl(c_purchase_amt_total,0) POA_MEASURE5,
668: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,
669: ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total'), poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE7,
670: nvl(c_contract_amt_total,0) POA_MEASURE8';
671: return l_sel_clause;
672: END;
673:

Line 689: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

685: l_comparison_type varchar2(1) := 'Y';
686: l_nested_pattern number;
687: l_cur_suffix varchar2(2);
688: l_custom_sql varchar2(10000);
689: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
690: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
691: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
692: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
693: l_where_clause VARCHAR2(2000);

Line 690: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

686: l_nested_pattern number;
687: l_cur_suffix varchar2(2);
688: l_custom_sql varchar2(10000);
689: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
690: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
691: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
692: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
693: l_where_clause VARCHAR2(2000);
694: l_where_clause2 VARCHAR2(2000);

Line 691: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

687: l_cur_suffix varchar2(2);
688: l_custom_sql varchar2(10000);
689: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
690: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
691: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
692: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
693: l_where_clause VARCHAR2(2000);
694: l_where_clause2 VARCHAR2(2000);
695: l_view_by_value VARCHAR2(100);

Line 692: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

688: l_custom_sql varchar2(10000);
689: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
690: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
691: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
692: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
693: l_where_clause VARCHAR2(2000);
694: l_where_clause2 VARCHAR2(2000);
695: l_view_by_value VARCHAR2(100);
696: l_mv VARCHAR2(30);

Line 700: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

696: l_mv VARCHAR2(30);
697: l_mv2 VARCHAR2(30);
698: l_sec_context varchar2(10);
699: l_use_only_agg_mv varchar2(1);
700: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
701: BEGIN
702: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
703: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
704:

Line 702: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

698: l_sec_context varchar2(10);
699: l_use_only_agg_mv varchar2(1);
700: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
701: BEGIN
702: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
703: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
704:
705: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
706: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 703: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

699: l_use_only_agg_mv varchar2(1);
700: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
701: BEGIN
702: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
703: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
704:
705: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
706: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
707: poa_dbi_sutil_pkg.process_parameters(

Line 729: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

725: p_version => '6.0',
726: p_role => 'COM',
727: p_mv_set => 'PODCUT');
728:
729: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
731:
732: if(l_view_by = 'ITEM+POA_ITEMS') then
733: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');

726: p_role => 'COM',
727: p_mv_set => 'PODCUT');
728:
729: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
731:
732: if(l_view_by = 'ITEM+POA_ITEMS') then
733: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
734: end if;

Line 733: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

729: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
731:
732: if(l_view_by = 'ITEM+POA_ITEMS') then
733: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
734: end if;
735:
736: l_query := get_ncp_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
737: ' || poa_dbi_template_pkg.status_sql(

Line 769: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

765: p_version => '8.0',
766: p_role => 'COM',
767: p_mv_set => 'PODCUTA');
768:
769: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
770: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
771:
772: if(l_view_by = 'ITEM+POA_ITEMS') then
773: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

Line 770: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');

766: p_role => 'COM',
767: p_mv_set => 'PODCUTA');
768:
769: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
770: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
771:
772: if(l_view_by = 'ITEM+POA_ITEMS') then
773: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
774: end if;

Line 773: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

769: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
770: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
771:
772: if(l_view_by = 'ITEM+POA_ITEMS') then
773: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
774: end if;
775:
776: /*check if we can get everything from aggregated mv*/
777: l_use_only_agg_mv := 'Y';

Line 809: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

805: p_version => '8.0',
806: p_role => 'COM',
807: p_mv_set => 'PODCUTB');
808:
809: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
810: l_mv_tbl.extend;
811: l_mv_tbl(1).mv_name := l_mv;
812: l_mv_tbl(1).mv_col := l_col_tbl;
813: l_mv_tbl(1).mv_where := l_where_clause;

Line 962: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')

958:
959: l_sel_clause := l_sel_clause || '
960: nvl(c_purchase_amt,0) POA_MEASURE1,
961: nvl(c_n_contract_amt,0) POA_MEASURE2, ' ||
962: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
963: || ' POA_PERCENT2,
964: ' ||
965: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt') || ' - ' ||
966: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')

Line 965: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt') || ' - ' ||

961: nvl(c_n_contract_amt,0) POA_MEASURE2, ' ||
962: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
963: || ' POA_PERCENT2,
964: ' ||
965: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt') || ' - ' ||
966: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')
967: || ' POA_MEASURE3,
968: nvl(c_purchase_amt_total,0) POA_MEASURE5,
969: nvl(c_n_contract_amt_total,0) POA_MEASURE7, ' ||

Line 966: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')

962: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
963: || ' POA_PERCENT2,
964: ' ||
965: poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt') || ' - ' ||
966: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')
967: || ' POA_MEASURE3,
968: nvl(c_purchase_amt_total,0) POA_MEASURE5,
969: nvl(c_n_contract_amt_total,0) POA_MEASURE7, ' ||
970: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')

Line 970: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')

966: poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')
967: || ' POA_MEASURE3,
968: nvl(c_purchase_amt_total,0) POA_MEASURE5,
969: nvl(c_n_contract_amt_total,0) POA_MEASURE7, ' ||
970: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
971: || ' POA_MEASURE8,
972: ' ||
973:
974: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')

Line 974: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')

970: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
971: || ' POA_MEASURE8,
972: ' ||
973:
974: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
975: || ' - ' ||
976: poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total',
977: 'p_purchase_amt_total') ||
978: ' POA_MEASURE9

Line 976: poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total',

972: ' ||
973:
974: poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
975: || ' - ' ||
976: poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total',
977: 'p_purchase_amt_total') ||
978: ' POA_MEASURE9
979: ';
980:

Line 998: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

994: l_comparison_type varchar2(1) := 'Y';
995: l_nested_pattern number;
996: l_cur_suffix varchar2(2);
997: l_custom_sql varchar2(10000);
998: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
999: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1000: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1001: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1002: l_where_clause VARCHAR2(2000);

Line 999: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

995: l_nested_pattern number;
996: l_cur_suffix varchar2(2);
997: l_custom_sql varchar2(10000);
998: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
999: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1000: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1001: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1002: l_where_clause VARCHAR2(2000);
1003: l_where_clause2 VARCHAR2(2000);

Line 1000: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

996: l_cur_suffix varchar2(2);
997: l_custom_sql varchar2(10000);
998: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
999: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1000: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1001: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1002: l_where_clause VARCHAR2(2000);
1003: l_where_clause2 VARCHAR2(2000);
1004: l_mv VARCHAR2(30);

Line 1001: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

997: l_custom_sql varchar2(10000);
998: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
999: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1000: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1001: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1002: l_where_clause VARCHAR2(2000);
1003: l_where_clause2 VARCHAR2(2000);
1004: l_mv VARCHAR2(30);
1005: l_mv2 VARCHAR2(30);

Line 1009: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

1005: l_mv2 VARCHAR2(30);
1006: l_view_by_value VARCHAR2(100);
1007: l_sec_context varchar2(10);
1008: l_use_only_agg_mv varchar2(1);
1009: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1010: BEGIN
1011: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1012: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1013:

Line 1011: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

1007: l_sec_context varchar2(10);
1008: l_use_only_agg_mv varchar2(1);
1009: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1010: BEGIN
1011: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1012: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1013:
1014: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1015: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 1012: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

1008: l_use_only_agg_mv varchar2(1);
1009: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1010: BEGIN
1011: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1012: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1013:
1014: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1015: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1016: poa_dbi_sutil_pkg.process_parameters(

Line 1038: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,

1034: p_version => '6.0',
1035: p_role => 'COM',
1036: p_mv_set => 'PODCUT');
1037:
1038: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1039: 'above_contract_amt');
1040: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1041: 'below_contract_amt');
1042: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

Line 1040: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,

1036: p_mv_set => 'PODCUT');
1037:
1038: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1039: 'above_contract_amt');
1040: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1041: 'below_contract_amt');
1042: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1043:
1044: if(l_view_by = 'ITEM+POA_ITEMS') then

Line 1042: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

1038: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1039: 'above_contract_amt');
1040: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1041: 'below_contract_amt');
1042: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1043:
1044: if(l_view_by = 'ITEM+POA_ITEMS') then
1045: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1046: end if;

Line 1045: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

1041: 'below_contract_amt');
1042: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1043:
1044: if(l_view_by = 'ITEM+POA_ITEMS') then
1045: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1046: end if;
1047:
1048: l_query := get_pcl_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
1049: ' || poa_dbi_template_pkg.status_sql(

Line 1082: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,

1078: p_version => '8.0',
1079: p_role => 'COM',
1080: p_mv_set => 'PODCUTA');
1081:
1082: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1083: 'above_contract_amt');
1084: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1085: 'below_contract_amt');
1086: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

Line 1084: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,

1080: p_mv_set => 'PODCUTA');
1081:
1082: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1083: 'above_contract_amt');
1084: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1085: 'below_contract_amt');
1086: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1087:
1088: if(l_view_by = 'ITEM+POA_ITEMS') then

Line 1086: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

1082: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1083: 'above_contract_amt');
1084: poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1085: 'below_contract_amt');
1086: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1087:
1088: if(l_view_by = 'ITEM+POA_ITEMS') then
1089: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1090: end if;

Line 1089: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');

1085: 'below_contract_amt');
1086: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1087:
1088: if(l_view_by = 'ITEM+POA_ITEMS') then
1089: poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1090: end if;
1091:
1092: /*check if we can get everything from aggregated mv*/
1093: l_use_only_agg_mv := 'Y';

Line 1125: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

1121: p_version => '8.0',
1122: p_role => 'COM',
1123: p_mv_set => 'PODCUTB');
1124:
1125: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1126: l_mv_tbl.extend;
1127: l_mv_tbl(1).mv_name := l_mv;
1128: l_mv_tbl(1).mv_col := l_col_tbl;
1129: l_mv_tbl(1).mv_where := l_where_clause;

Line 1286: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

1282: l_sel_clause := l_sel_clause || '
1283: nvl(c_purchase_amt,0) POA_MEASURE1,
1284: nvl(c_above_contract_amt, 0) + nvl(c_below_contract_amt, 0) POA_MEASURE2,
1285: nvl(c_below_contract_amt,0) POA_MEASURE3,
1286: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1287: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE4,
1288: nvl(c_purchase_amt_total,0) POA_MEASURE5,
1289: nvl(c_above_contract_amt,0) POA_MEASURE6,
1290: nvl(c_above_contract_amt_total,0) + nvl(c_below_contract_amt_total,0) POA_MEASURE7,

Line 1287: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE4,

1283: nvl(c_purchase_amt,0) POA_MEASURE1,
1284: nvl(c_above_contract_amt, 0) + nvl(c_below_contract_amt, 0) POA_MEASURE2,
1285: nvl(c_below_contract_amt,0) POA_MEASURE3,
1286: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1287: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE4,
1288: nvl(c_purchase_amt_total,0) POA_MEASURE5,
1289: nvl(c_above_contract_amt,0) POA_MEASURE6,
1290: nvl(c_above_contract_amt_total,0) + nvl(c_below_contract_amt_total,0) POA_MEASURE7,
1291: nvl(c_below_contract_amt_total,0) POA_MEASURE8,

Line 1293: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE10,

1289: nvl(c_above_contract_amt,0) POA_MEASURE6,
1290: nvl(c_above_contract_amt_total,0) + nvl(c_below_contract_amt_total,0) POA_MEASURE7,
1291: nvl(c_below_contract_amt_total,0) POA_MEASURE8,
1292: nvl(c_above_contract_amt_total,0) POA_MEASURE9,
1293: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE10,
1294: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE11,
1295: nvl(c_p_contract_amt,0) POA_MEASURE13,
1296: nvl(c_p_contract_amt_total,0) POA_MEASURE14';
1297: return l_sel_clause;

Line 1294: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE11,

1290: nvl(c_above_contract_amt_total,0) + nvl(c_below_contract_amt_total,0) POA_MEASURE7,
1291: nvl(c_below_contract_amt_total,0) POA_MEASURE8,
1292: nvl(c_above_contract_amt_total,0) POA_MEASURE9,
1293: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE10,
1294: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE11,
1295: nvl(c_p_contract_amt,0) POA_MEASURE13,
1296: nvl(c_p_contract_amt_total,0) POA_MEASURE14';
1297: return l_sel_clause;
1298: END;

Line 1315: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

1311: l_comparison_type varchar2(1) := 'Y';
1312: l_nested_pattern number;
1313: l_cur_suffix varchar2(2);
1314: l_custom_sql varchar2(4000);
1315: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1316: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1317: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1318: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1319: l_mv VARCHAR2(30);

Line 1316: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

1312: l_nested_pattern number;
1313: l_cur_suffix varchar2(2);
1314: l_custom_sql varchar2(4000);
1315: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1316: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1317: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1318: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1319: l_mv VARCHAR2(30);
1320: l_mv2 VARCHAR2(30);

Line 1317: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1313: l_cur_suffix varchar2(2);
1314: l_custom_sql varchar2(4000);
1315: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1316: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1317: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1318: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1319: l_mv VARCHAR2(30);
1320: l_mv2 VARCHAR2(30);
1321: l_where_clause VARCHAR2(2000);

Line 1318: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1314: l_custom_sql varchar2(4000);
1315: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1316: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1317: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1318: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1319: l_mv VARCHAR2(30);
1320: l_mv2 VARCHAR2(30);
1321: l_where_clause VARCHAR2(2000);
1322: l_where_clause2 VARCHAR2(2000);

Line 1326: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

1322: l_where_clause2 VARCHAR2(2000);
1323: l_view_by_value VARCHAR2(100);
1324: l_sec_context varchar2(10);
1325: l_use_only_agg_mv varchar2(1);
1326: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1327: BEGIN
1328: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1329: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1330:

Line 1328: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

1324: l_sec_context varchar2(10);
1325: l_use_only_agg_mv varchar2(1);
1326: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1327: BEGIN
1328: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1329: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1330:
1331: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1332: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 1329: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

1325: l_use_only_agg_mv varchar2(1);
1326: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1327: BEGIN
1328: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1329: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1330:
1331: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1332: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1333: poa_dbi_sutil_pkg.process_parameters(

Line 1355: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');

1351: p_version => '6.0',
1352: p_role => 'COM',
1353: p_mv_set => 'PODCUT');
1354:
1355: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1356:
1357: l_query := get_pop_trend_sel_clause || ' from
1358: '|| poa_dbi_template_pkg.trend_sql(
1359: p_xtd => l_xtd,

Line 1390: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');

1386: p_version => '8.0',
1387: p_role => 'COM',
1388: p_mv_set => 'PODCUTA');
1389:
1390: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1391:
1392: /*check if we can get everything from aggregated mv*/
1393: l_use_only_agg_mv := 'Y';
1394: for i in 1..l_in_join_tbl.count loop

Line 1425: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

1421: p_version => '8.0',
1422: p_role => 'COM',
1423: p_mv_set => 'PODCUTB');
1424:
1425: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1426: l_mv_tbl.extend;
1427: l_mv_tbl(1).mv_name := l_mv;
1428: l_mv_tbl(1).mv_col := l_col_tbl;
1429: l_mv_tbl(1).mv_where := l_where_clause;

Line 1476: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1';

1472: end if;
1473: l_sel_clause := l_sel_clause ||
1474: 'nvl(c_purchase_amt,0) POA_MEASURE1,
1475: p_purchase_amt POA_MEASURE2,
1476: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1';
1477:
1478: return l_sel_clause;
1479: end;
1480:

Line 1495: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

1491: l_comparison_type varchar2(1) := 'Y';
1492: l_nested_pattern number;
1493: l_cur_suffix varchar2(2);
1494: l_custom_sql varchar2(10000);
1495: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1496: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1497: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1498: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1499: l_mv VARCHAR2(30);

Line 1496: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

1492: l_nested_pattern number;
1493: l_cur_suffix varchar2(2);
1494: l_custom_sql varchar2(10000);
1495: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1496: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1497: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1498: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1499: l_mv VARCHAR2(30);
1500: l_mv2 VARCHAR2(30);

Line 1497: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1493: l_cur_suffix varchar2(2);
1494: l_custom_sql varchar2(10000);
1495: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1496: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1497: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1498: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1499: l_mv VARCHAR2(30);
1500: l_mv2 VARCHAR2(30);
1501: l_where_clause VARCHAR2(2000);

Line 1498: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1494: l_custom_sql varchar2(10000);
1495: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1496: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1497: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1498: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1499: l_mv VARCHAR2(30);
1500: l_mv2 VARCHAR2(30);
1501: l_where_clause VARCHAR2(2000);
1502: l_where_clause2 VARCHAR2(2000);

Line 1506: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

1502: l_where_clause2 VARCHAR2(2000);
1503: l_view_by_value VARCHAR2(100);
1504: l_sec_context varchar2(10);
1505: l_use_only_agg_mv varchar2(1);
1506: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1507: BEGIN
1508: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1509: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1510:

Line 1508: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

1504: l_sec_context varchar2(10);
1505: l_use_only_agg_mv varchar2(1);
1506: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1507: BEGIN
1508: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1509: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1510:
1511: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1512: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 1509: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

1505: l_use_only_agg_mv varchar2(1);
1506: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1507: BEGIN
1508: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1509: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1510:
1511: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1512: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1513: poa_dbi_sutil_pkg.process_parameters(

Line 1535: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');

1531: p_version => '6.0',
1532: p_role => 'COM',
1533: p_mv_set => 'PODCUT');
1534:
1535: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1536: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1537: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1538:
1539: l_query := get_trend_sel_clause || ' from

Line 1536: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');

1532: p_role => 'COM',
1533: p_mv_set => 'PODCUT');
1534:
1535: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1536: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1537: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1538:
1539: l_query := get_trend_sel_clause || ' from
1540: '|| poa_dbi_template_pkg.trend_sql(

Line 1537: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');

1533: p_mv_set => 'PODCUT');
1534:
1535: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1536: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1537: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1538:
1539: l_query := get_trend_sel_clause || ' from
1540: '|| poa_dbi_template_pkg.trend_sql(
1541: p_xtd => l_xtd,

Line 1572: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');

1568: p_version => '8.0',
1569: p_role => 'COM',
1570: p_mv_set => 'PODCUTA');
1571:
1572: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1573: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1574: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
1575: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1576:

Line 1573: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');

1569: p_role => 'COM',
1570: p_mv_set => 'PODCUTA');
1571:
1572: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1573: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1574: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
1575: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1576:
1577: /*check if we can get everything from aggregated mv*/

Line 1574: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');

1570: p_mv_set => 'PODCUTA');
1571:
1572: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1573: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1574: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
1575: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1576:
1577: /*check if we can get everything from aggregated mv*/
1578: l_use_only_agg_mv := 'Y';

Line 1575: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');

1571:
1572: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1573: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1574: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
1575: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1576:
1577: /*check if we can get everything from aggregated mv*/
1578: l_use_only_agg_mv := 'Y';
1579: for i in 1..l_in_join_tbl.count loop

Line 1610: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

1606: p_version => '8.0',
1607: p_role => 'COM',
1608: p_mv_set => 'PODCUTB');
1609:
1610: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1611: l_mv_tbl.extend;
1612: l_mv_tbl(1).mv_name := l_mv;
1613: l_mv_tbl(1).mv_col := l_col_tbl;
1614: l_mv_tbl(1).mv_where := l_where_clause;

Line 1660: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,

1656: l_sel_clause := 'select cal_name VIEWBY,';
1657: end if;
1658:
1659: l_sel_clause := l_sel_clause || '
1660: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1661: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
1662: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1663: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,
1664: null POA_PERCENT3, -- (Obsoleted)Contract Leakage Rate

Line 1661: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,

1657: end if;
1658:
1659: l_sel_clause := l_sel_clause || '
1660: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1661: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
1662: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1663: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,
1664: null POA_PERCENT3, -- (Obsoleted)Contract Leakage Rate
1665: null POA_MEASURE4 -- (Obsoleted)Change '||fnd_global.newline;

Line 1662: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,

1658:
1659: l_sel_clause := l_sel_clause || '
1660: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1661: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
1662: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1663: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,
1664: null POA_PERCENT3, -- (Obsoleted)Contract Leakage Rate
1665: null POA_MEASURE4 -- (Obsoleted)Change '||fnd_global.newline;
1666: return l_sel_clause;

Line 1663: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,

1659: l_sel_clause := l_sel_clause || '
1660: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1661: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
1662: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1663: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,
1664: null POA_PERCENT3, -- (Obsoleted)Contract Leakage Rate
1665: null POA_MEASURE4 -- (Obsoleted)Change '||fnd_global.newline;
1666: return l_sel_clause;
1667: END;

Line 1684: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

1680: l_comparison_type varchar2(1) := 'Y';
1681: l_nested_pattern number;
1682: l_cur_suffix varchar2(2);
1683: l_custom_sql varchar2(10000);
1684: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1685: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1686: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1687: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1688: l_where_clause varchar2(2000);

Line 1685: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

1681: l_nested_pattern number;
1682: l_cur_suffix varchar2(2);
1683: l_custom_sql varchar2(10000);
1684: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1685: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1686: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1687: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1688: l_where_clause varchar2(2000);
1689: l_where_clause2 varchar2(2000);

Line 1686: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1682: l_cur_suffix varchar2(2);
1683: l_custom_sql varchar2(10000);
1684: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1685: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1686: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1687: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1688: l_where_clause varchar2(2000);
1689: l_where_clause2 varchar2(2000);
1690: l_mv varchar2(30);

Line 1687: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1683: l_custom_sql varchar2(10000);
1684: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1685: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1686: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1687: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1688: l_where_clause varchar2(2000);
1689: l_where_clause2 varchar2(2000);
1690: l_mv varchar2(30);
1691: l_mv2 varchar2(30);

Line 1695: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;

1691: l_mv2 varchar2(30);
1692: l_org_where varchar2(500);
1693: l_commodity_where varchar2(500);
1694: l_view_by_value varchar2(100);
1695: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1696: l_sec_context varchar2(10);
1697: l_use_only_agg_mv varchar2(1);
1698: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1699: BEGIN

Line 1698: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

1694: l_view_by_value varchar2(100);
1695: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1696: l_sec_context varchar2(10);
1697: l_use_only_agg_mv varchar2(1);
1698: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1699: BEGIN
1700: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1701: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1702:

Line 1700: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

1696: l_sec_context varchar2(10);
1697: l_use_only_agg_mv varchar2(1);
1698: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1699: BEGIN
1700: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1701: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1702:
1703: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1704: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 1701: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

1697: l_use_only_agg_mv varchar2(1);
1698: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1699: BEGIN
1700: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1701: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1702:
1703: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1704: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1705: poa_dbi_sutil_pkg.process_parameters(

Line 1727: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

1723: p_version => '6.0',
1724: p_role => 'COM',
1725: p_mv_set => 'PODCUT');
1726:
1727: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1728: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1729: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1731:

Line 1728: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

1724: p_role => 'COM',
1725: p_mv_set => 'PODCUT');
1726:
1727: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1728: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1729: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1731:
1732: l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');

Line 1729: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');

1725: p_mv_set => 'PODCUT');
1726:
1727: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1728: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1729: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1731:
1732: l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
1733:

Line 1730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

1726:
1727: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1728: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1729: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1730: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1731:
1732: l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
1733:
1734: l_query := l_query || '

Line 1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,

1745: oset.POA_MEASURE8 POA_MEASURE8,
1746: oset.POA_MEASURE9 POA_MEASURE9
1747: from
1748: (select * from (select ' || l_view_by_col || ',
1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

Line 1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,

1746: oset.POA_MEASURE9 POA_MEASURE9
1747: from
1748: (select * from (select ' || l_view_by_col || ',
1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

Line 1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,

1747: from
1748: (select * from (select ' || l_view_by_col || ',
1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

Line 1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,

1748: (select * from (select ' || l_view_by_col || ',
1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

Line 1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

1749: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

Line 1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

1750: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

Line 1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

1751: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

Line 1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

1752: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

Line 1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

1753: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1761: from

Line 1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

1754: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1761: from
1762: ' || poa_dbi_template_pkg.status_sql(

Line 1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

1755: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1761: from
1762: ' || poa_dbi_template_pkg.status_sql(
1763: p_fact_name => l_mv,

Line 1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

1756: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1761: from
1762: ' || poa_dbi_template_pkg.status_sql(
1763: p_fact_name => l_mv,
1764: p_where_clause => l_where_clause,

Line 1794: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');

1790: p_version => '8.0',
1791: p_role => 'COM',
1792: p_mv_set => 'PODCUTA');
1793:
1794: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1795: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1796: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1797: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1798:

Line 1795: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');

1791: p_role => 'COM',
1792: p_mv_set => 'PODCUTA');
1793:
1794: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1795: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1796: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1797: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1798:
1799: /*check if we can get everything from aggregated mv*/

Line 1796: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');

1792: p_mv_set => 'PODCUTA');
1793:
1794: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1795: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1796: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1797: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1798:
1799: /*check if we can get everything from aggregated mv*/
1800: l_use_only_agg_mv := 'Y';

Line 1797: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

1793:
1794: poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1795: poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1796: poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1797: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1798:
1799: /*check if we can get everything from aggregated mv*/
1800: l_use_only_agg_mv := 'Y';
1801: for i in 1..l_in_join_tbl.count loop

Line 1832: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

1828: p_version => '8.0',
1829: p_role => 'COM',
1830: p_mv_set => 'PODCUTB');
1831:
1832: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1833: l_mv_tbl.extend;
1834: l_mv_tbl(1).mv_name := l_mv;
1835: l_mv_tbl(1).mv_col := l_col_tbl;
1836: l_mv_tbl(1).mv_where := l_where_clause;

Line 1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,

1860: oset.POA_MEASURE8 POA_MEASURE8,
1861: oset.POA_MEASURE9 POA_MEASURE9
1862: from
1863: (select * from (select company_id,
1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

Line 1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,

1861: oset.POA_MEASURE9 POA_MEASURE9
1862: from
1863: (select * from (select company_id,
1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

Line 1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,

1862: from
1863: (select * from (select company_id,
1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

Line 1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,

1863: (select * from (select company_id,
1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

Line 1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

1864: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

Line 1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

1865: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

Line 1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

1866: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

Line 1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

1867: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

Line 1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

1868: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1876: from (

Line 1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

1869: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1876: from (
1877: ' || poa_dbi_template_pkg.union_all_status_sql(

Line 1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

1870: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1876: from (
1877: ' || poa_dbi_template_pkg.union_all_status_sql(
1878: p_mv => l_mv_tbl,

Line 1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

1871: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1876: from (
1877: ' || poa_dbi_template_pkg.union_all_status_sql(
1878: p_mv => l_mv_tbl,
1879: p_join_tables => l_join_tbl,

Line 1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,

1898: oset.POA_MEASURE8 POA_MEASURE8,
1899: oset.POA_MEASURE9 POA_MEASURE9
1900: from
1901: (select * from (select company_id,
1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

Line 1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,

1899: oset.POA_MEASURE9 POA_MEASURE9
1900: from
1901: (select * from (select company_id,
1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

Line 1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,

1900: from
1901: (select * from (select company_id,
1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

Line 1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,

1901: (select * from (select company_id,
1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

Line 1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,

1902: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

Line 1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,

1903: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

Line 1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,

1904: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

Line 1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,

1905: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

Line 1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,

1906: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1914: from

Line 1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,

1907: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1914: from
1915: ' || poa_dbi_template_pkg.status_sql(

Line 1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,

1908: ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1914: from
1915: ' || poa_dbi_template_pkg.status_sql(
1916: p_fact_name => l_mv,

Line 1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9

1909: ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910: ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911: ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912: ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913: ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1914: from
1915: ' || poa_dbi_template_pkg.status_sql(
1916: p_fact_name => l_mv,
1917: p_where_clause => l_where_clause,

Line 1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

1938: l_comparison_type varchar2(1) := 'Y';
1939: l_nested_pattern number;
1940: l_cur_suffix varchar2(2);
1941: l_custom_sql varchar2(10000);
1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;

Line 1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

1939: l_nested_pattern number;
1940: l_cur_suffix varchar2(2);
1941: l_custom_sql varchar2(10000);
1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1947: l_where_clause VARCHAR2(2000);

Line 1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1940: l_cur_suffix varchar2(2);
1941: l_custom_sql varchar2(10000);
1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1947: l_where_clause VARCHAR2(2000);
1948: l_where_clause2 VARCHAR2(2000);

Line 1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

1941: l_custom_sql varchar2(10000);
1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1947: l_where_clause VARCHAR2(2000);
1948: l_where_clause2 VARCHAR2(2000);
1949: l_mv VARCHAR2(30);

Line 1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;

1942: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1947: l_where_clause VARCHAR2(2000);
1948: l_where_clause2 VARCHAR2(2000);
1949: l_mv VARCHAR2(30);
1950: l_mv2 VARCHAR2(30);

Line 1957: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

1953: l_in_join_tables VARCHAR2(1000) := '';
1954: l_in_join_tables2 VARCHAR2(1000) := '';
1955: l_sec_context varchar2(10);
1956: l_use_only_agg_mv varchar2(1);
1957: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1958: BEGIN
1959: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1960: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1961:

Line 1959: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

1955: l_sec_context varchar2(10);
1956: l_use_only_agg_mv varchar2(1);
1957: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1958: BEGIN
1959: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1960: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1961:
1962: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1963: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 1960: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

1956: l_use_only_agg_mv varchar2(1);
1957: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1958: BEGIN
1959: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1960: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1961:
1962: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1963: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1964: poa_dbi_sutil_pkg.process_parameters(

Line 2253: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

2249: l_comparison_type varchar2(1) := 'Y';
2250: l_nested_pattern number;
2251: l_cur_suffix varchar2(2);
2252: l_custom_sql varchar2(10000);
2253: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
2254: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
2255: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2256: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2257: l_view_by_value VARCHAR2(100);

Line 2254: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

2250: l_nested_pattern number;
2251: l_cur_suffix varchar2(2);
2252: l_custom_sql varchar2(10000);
2253: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
2254: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
2255: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2256: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2257: l_view_by_value VARCHAR2(100);
2258: l_where_clause VARCHAR2(2000);

Line 2255: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

2251: l_cur_suffix varchar2(2);
2252: l_custom_sql varchar2(10000);
2253: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
2254: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
2255: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2256: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2257: l_view_by_value VARCHAR2(100);
2258: l_where_clause VARCHAR2(2000);
2259: l_where_clause2 VARCHAR2(2000);

Line 2256: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;

2252: l_custom_sql varchar2(10000);
2253: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
2254: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
2255: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2256: l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2257: l_view_by_value VARCHAR2(100);
2258: l_where_clause VARCHAR2(2000);
2259: l_where_clause2 VARCHAR2(2000);
2260: l_mv VARCHAR2(30);

Line 2264: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

2260: l_mv VARCHAR2(30);
2261: l_mv2 VARCHAR2(30);
2262: l_sec_context varchar2(10);
2263: l_use_only_agg_mv varchar2(1);
2264: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
2265: BEGIN
2266: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
2267: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
2268:

Line 2266: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();

2262: l_sec_context varchar2(10);
2263: l_use_only_agg_mv varchar2(1);
2264: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
2265: BEGIN
2266: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
2267: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
2268:
2269: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
2270: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then

Line 2267: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();

2263: l_use_only_agg_mv varchar2(1);
2264: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
2265: BEGIN
2266: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
2267: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
2268:
2269: l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
2270: if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
2271: poa_dbi_sutil_pkg.process_parameters(

Line 2293: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

2289: p_version => '6.0',
2290: p_role => 'COM',
2291: p_mv_set => 'PODCUT');
2292:
2293: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
2294:
2295: l_query := get_doctype_sel_clause(l_view_by,l_view_by_col) || ' from ' ||
2296: poa_dbi_template_pkg.status_sql(
2297: p_fact_name => l_mv,

Line 2330: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');

2326: p_version => '8.0',
2327: p_role => 'COM',
2328: p_mv_set => 'PODCUTA');
2329:
2330: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
2331:
2332: /*check if we can get everything from aggregated mv*/
2333: l_use_only_agg_mv := 'Y';
2334: for i in 1..l_in_join_tbl.count loop

Line 2365: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();

2361: p_version => '8.0',
2362: p_role => 'COM',
2363: p_mv_set => 'PODCUTB');
2364:
2365: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
2366: l_mv_tbl.extend;
2367: l_mv_tbl(1).mv_name := l_mv;
2368: l_mv_tbl(1).mv_col := l_col_tbl;
2369: l_mv_tbl(1).mv_where := l_where_clause;

Line 2423: || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,

2419: from
2420: (select ' || 'contract_type' || ',
2421: ' || 'contract_type' || ' VIEWBY,
2422: c_purchase_amt POA_MEASURE1, '
2423: || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
2424: '
2425: || poa_dbi_util_pkg.change_clause (
2426: poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
2427: poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,

Line 2425: || poa_dbi_util_pkg.change_clause (

2421: ' || 'contract_type' || ' VIEWBY,
2422: c_purchase_amt POA_MEASURE1, '
2423: || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
2424: '
2425: || poa_dbi_util_pkg.change_clause (
2426: poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
2427: poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,
2428: 'P') || ' POA_PERCENT2,
2429: c_purchase_amt_total POA_MEASURE2,

Line 2426: poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,

2422: c_purchase_amt POA_MEASURE1, '
2423: || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
2424: '
2425: || poa_dbi_util_pkg.change_clause (
2426: poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
2427: poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,
2428: 'P') || ' POA_PERCENT2,
2429: c_purchase_amt_total POA_MEASURE2,
2430: decode(c_purchase_amt_total, null, null, 100) POA_PERCENT3';

Line 2427: poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,

2423: || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
2424: '
2425: || poa_dbi_util_pkg.change_clause (
2426: poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
2427: poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,
2428: 'P') || ' POA_PERCENT2,
2429: c_purchase_amt_total POA_MEASURE2,
2430: decode(c_purchase_amt_total, null, null, 100) POA_PERCENT3';
2431: