[Home] [Help]
39: l_cur_suffix varchar2(2);
40: l_url varchar2(300);
41: l_custom_sql varchar2(10000);
42: l_view_by_value varchar2(30);
43: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
44: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
45: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
46: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
47: l_where_clause VARCHAR2(2000);
40: l_url varchar2(300);
41: l_custom_sql varchar2(10000);
42: l_view_by_value varchar2(30);
43: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
44: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
45: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
46: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
47: l_where_clause VARCHAR2(2000);
48: l_mv VARCHAR2(30);
41: l_custom_sql varchar2(10000);
42: l_view_by_value varchar2(30);
43: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
44: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
45: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
46: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
47: l_where_clause VARCHAR2(2000);
48: l_mv VARCHAR2(30);
49: ERR_MSG VARCHAR2(100);
42: l_view_by_value varchar2(30);
43: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
44: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
45: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
46: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
47: l_where_clause VARCHAR2(2000);
48: l_mv VARCHAR2(30);
49: ERR_MSG VARCHAR2(100);
50: ERR_CDE NUMBER;
51: l_context_code VARCHAR2(10);
52: l_to_date_type VARCHAR2(10);
53: BEGIN
54:
55: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
56: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
57: l_to_date_type := 'XTD';
58: poa_dbi_sutil_pkg.process_parameters(p_param
59: ,l_view_by
52: l_to_date_type VARCHAR2(10);
53: BEGIN
54:
55: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
56: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
57: l_to_date_type := 'XTD';
58: poa_dbi_sutil_pkg.process_parameters(p_param
59: ,l_view_by
60: ,l_view_by_col
75: , '8.0'
76: , 'NEG'
77: ,'NEG');
78:
79: poa_dbi_util_pkg.add_column(l_col_tbl
80: ,'award_amt_' || l_cur_suffix
81: ,'award_amt'
82: ,p_to_date_type => l_to_date_type);
83: poa_dbi_util_pkg.add_column(l_col_tbl
79: poa_dbi_util_pkg.add_column(l_col_tbl
80: ,'award_amt_' || l_cur_suffix
81: ,'award_amt'
82: ,p_to_date_type => l_to_date_type);
83: poa_dbi_util_pkg.add_column(l_col_tbl
84: ,'current_amt_' || l_cur_suffix
85: ,'current_amt'
86: ,p_to_date_type => l_to_date_type);
87: poa_dbi_util_pkg.add_column(l_col_tbl
83: poa_dbi_util_pkg.add_column(l_col_tbl
84: ,'current_amt_' || l_cur_suffix
85: ,'current_amt'
86: ,p_to_date_type => l_to_date_type);
87: poa_dbi_util_pkg.add_column(l_col_tbl
88: ,'proj_savings_amt_' || l_cur_suffix
89: ,'proj_savings_amt'
90: ,p_to_date_type => l_to_date_type);
91: poa_dbi_util_pkg.add_column(l_col_tbl
87: poa_dbi_util_pkg.add_column(l_col_tbl
88: ,'proj_savings_amt_' || l_cur_suffix
89: ,'proj_savings_amt'
90: ,p_to_date_type => l_to_date_type);
91: poa_dbi_util_pkg.add_column(l_col_tbl
92: ,'preparation_time'
93: ,'preparation_time'
94: ,p_to_date_type => l_to_date_type);
95: poa_dbi_util_pkg.add_column(l_col_tbl
91: poa_dbi_util_pkg.add_column(l_col_tbl
92: ,'preparation_time'
93: ,'preparation_time'
94: ,p_to_date_type => l_to_date_type);
95: poa_dbi_util_pkg.add_column(l_col_tbl
96: ,'bidding_time'
97: ,'bidding_time'
98: ,p_to_date_type => l_to_date_type);
99: poa_dbi_util_pkg.add_column(l_col_tbl
95: poa_dbi_util_pkg.add_column(l_col_tbl
96: ,'bidding_time'
97: ,'bidding_time'
98: ,p_to_date_type => l_to_date_type);
99: poa_dbi_util_pkg.add_column(l_col_tbl
100: ,'analysis_time'
101: ,'analysis_time'
102: ,p_to_date_type => l_to_date_type);
103: poa_dbi_util_pkg.add_column(l_col_tbl
99: poa_dbi_util_pkg.add_column(l_col_tbl
100: ,'analysis_time'
101: ,'analysis_time'
102: ,p_to_date_type => l_to_date_type);
103: poa_dbi_util_pkg.add_column(l_col_tbl
104: ,'award_time'
105: ,'award_time'
106: ,p_to_date_type => l_to_date_type);
107: poa_dbi_util_pkg.add_column(l_col_tbl
103: poa_dbi_util_pkg.add_column(l_col_tbl
104: ,'award_time'
105: ,'award_time'
106: ,p_to_date_type => l_to_date_type);
107: poa_dbi_util_pkg.add_column(l_col_tbl
108: ,'c_total'
109: ,'count'
110: ,p_to_date_type => l_to_date_type);
111:
111:
112:
113:
114: if(l_view_by = 'ITEM+POA_ITEMS') then
115: poa_dbi_util_pkg.add_column(l_col_tbl
116: ,'award_qty'
117: ,'award_qty'
118: ,p_to_date_type => l_to_date_type);
119: end if;
273: decode(base_uom,null,to_number(null),nvl(c_award_qty,0)) POA_MEASURE1, ';
274: end if;
275: --
276: l_sel_clause := l_sel_clause || ' nvl(c_award_amt,0) POA_MEASURE2,
277: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
278: c_current_amt POA_MEASURE3,
279: c_proj_savings_amt POA_MEASURE4,
280: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT2,
281: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT3,
276: l_sel_clause := l_sel_clause || ' nvl(c_award_amt,0) POA_MEASURE2,
277: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
278: c_current_amt POA_MEASURE3,
279: c_proj_savings_amt POA_MEASURE4,
280: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT2,
281: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT3,
282: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt'),
283: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt'),'P') || ' POA_MEASURE5,
284: nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
277: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
278: c_current_amt POA_MEASURE3,
279: c_proj_savings_amt POA_MEASURE4,
280: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT2,
281: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT3,
282: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt'),
283: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt'),'P') || ' POA_MEASURE5,
284: nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
285: ((nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count)) -
278: c_current_amt POA_MEASURE3,
279: c_proj_savings_amt POA_MEASURE4,
280: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT2,
281: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT3,
282: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt'),
283: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt'),'P') || ' POA_MEASURE5,
284: nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
285: ((nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count)) -
286: (nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0))/decode(p_count,0,null,p_count)) POA_MEASURE7,
279: c_proj_savings_amt POA_MEASURE4,
280: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT2,
281: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT3,
282: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt'),
283: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt'),'P') || ' POA_MEASURE5,
284: nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
285: ((nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count)) -
286: (nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0))/decode(p_count,0,null,p_count)) POA_MEASURE7,
287: nvl(c_award_amt_total,0) POA_MEASURE8,
284: nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
285: ((nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count)) -
286: (nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0))/decode(p_count,0,null,p_count)) POA_MEASURE7,
287: nvl(c_award_amt_total,0) POA_MEASURE8,
288: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
289: c_current_amt_total POA_MEASURE9,
290: c_proj_savings_amt_total POA_MEASURE10,
291: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt_total','p_proj_savings_amt_total') || ' POA_PERCENT5,
292: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT6,
287: nvl(c_award_amt_total,0) POA_MEASURE8,
288: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
289: c_current_amt_total POA_MEASURE9,
290: c_proj_savings_amt_total POA_MEASURE10,
291: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt_total','p_proj_savings_amt_total') || ' POA_PERCENT5,
292: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT6,
293: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total'),
294: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total'),'P') || ' POA_MEASURE11,
295: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE12,
288: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
289: c_current_amt_total POA_MEASURE9,
290: c_proj_savings_amt_total POA_MEASURE10,
291: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt_total','p_proj_savings_amt_total') || ' POA_PERCENT5,
292: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT6,
293: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total'),
294: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total'),'P') || ' POA_MEASURE11,
295: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE12,
296: ((nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total)) -
289: c_current_amt_total POA_MEASURE9,
290: c_proj_savings_amt_total POA_MEASURE10,
291: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt_total','p_proj_savings_amt_total') || ' POA_PERCENT5,
292: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT6,
293: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total'),
294: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total'),'P') || ' POA_MEASURE11,
295: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE12,
296: ((nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total)) -
297: (nvl((p_preparation_time_total + p_bidding_time_total + p_analysis_time_total + p_award_time_total),0))/decode(p_count_total,0,null,p_count_total)) POA_MEASURE13,
290: c_proj_savings_amt_total POA_MEASURE10,
291: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt_total','p_proj_savings_amt_total') || ' POA_PERCENT5,
292: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT6,
293: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total'),
294: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total'),'P') || ' POA_MEASURE11,
295: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE12,
296: ((nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total)) -
297: (nvl((p_preparation_time_total + p_bidding_time_total + p_analysis_time_total + p_award_time_total),0))/decode(p_count_total,0,null,p_count_total)) POA_MEASURE13,
298: nvl(p_award_amt,0) POA_MEASURE14,
298: nvl(p_award_amt,0) POA_MEASURE14,
299: nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0)/decode(p_count,0,null,p_count) POA_MEASURE15,
300: c_proj_savings_amt POA_MEASURE17,
301: p_proj_savings_amt POA_MEASURE18,
302: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT9,
303: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt') || ' POA_PERCENT10,
304: c_proj_savings_amt_total POA_MEASURE20,
305: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT11,
306: nvl(p_award_amt_total,0) POA_MEASURE21,
299: nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0)/decode(p_count,0,null,p_count) POA_MEASURE15,
300: c_proj_savings_amt POA_MEASURE17,
301: p_proj_savings_amt POA_MEASURE18,
302: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT9,
303: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt') || ' POA_PERCENT10,
304: c_proj_savings_amt_total POA_MEASURE20,
305: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT11,
306: nvl(p_award_amt_total,0) POA_MEASURE21,
307: nvl((p_preparation_time_total + p_bidding_time_total + p_analysis_time_total + p_award_time_total),0)/decode(p_count_total,0,null,p_count_total) POA_MEASURE22,
301: p_proj_savings_amt POA_MEASURE18,
302: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_current_amt') || ' POA_PERCENT9,
303: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_current_amt') || ' POA_PERCENT10,
304: c_proj_savings_amt_total POA_MEASURE20,
305: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT11,
306: nvl(p_award_amt_total,0) POA_MEASURE21,
307: nvl((p_preparation_time_total + p_bidding_time_total + p_analysis_time_total + p_award_time_total),0)/decode(p_count_total,0,null,p_count_total) POA_MEASURE22,
308: p_proj_savings_amt_total POA_MEASURE23,
309: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total') || ' POA_PERCENT12 ';
305: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_current_amt_total') || ' POA_PERCENT11,
306: nvl(p_award_amt_total,0) POA_MEASURE21,
307: nvl((p_preparation_time_total + p_bidding_time_total + p_analysis_time_total + p_award_time_total),0)/decode(p_count_total,0,null,p_count_total) POA_MEASURE22,
308: p_proj_savings_amt_total POA_MEASURE23,
309: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_current_amt_total') || ' POA_PERCENT12 ';
310: return l_sel_clause;
311: END;
312: --
313:
331: l_cur_suffix varchar2(2);
332: l_url varchar2(300);
333: l_custom_sql varchar2(10000);
334: l_view_by_value varchar2(30);
335: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
336: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
337: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
338: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
339: l_where_clause VARCHAR2(2000);
332: l_url varchar2(300);
333: l_custom_sql varchar2(10000);
334: l_view_by_value varchar2(30);
335: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
336: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
337: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
338: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
339: l_where_clause VARCHAR2(2000);
340: l_mv VARCHAR2(30);
333: l_custom_sql varchar2(10000);
334: l_view_by_value varchar2(30);
335: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
336: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
337: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
338: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
339: l_where_clause VARCHAR2(2000);
340: l_mv VARCHAR2(30);
341: ERR_MSG VARCHAR2(100);
334: l_view_by_value varchar2(30);
335: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
336: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
337: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
338: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
339: l_where_clause VARCHAR2(2000);
340: l_mv VARCHAR2(30);
341: ERR_MSG VARCHAR2(100);
342: ERR_CDE NUMBER;
343: l_context_code VARCHAR2(10);
344: l_to_date_type VARCHAR2(10);
345: BEGIN
346:
347: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
348: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
349: l_to_date_type := 'XTD';
350: poa_dbi_sutil_pkg.process_parameters(p_param
351: ,l_view_by
344: l_to_date_type VARCHAR2(10);
345: BEGIN
346:
347: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
348: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
349: l_to_date_type := 'XTD';
350: poa_dbi_sutil_pkg.process_parameters(p_param
351: ,l_view_by
352: ,l_view_by_col
367: , '8.0'
368: , 'NEG'
369: ,'NEG');
370:
371: poa_dbi_util_pkg.add_column(l_col_tbl
372: ,'award_amt_' || l_cur_suffix
373: ,'award_amt'
374: ,p_to_date_type => l_to_date_type);
375: poa_dbi_util_pkg.add_column(l_col_tbl
371: poa_dbi_util_pkg.add_column(l_col_tbl
372: ,'award_amt_' || l_cur_suffix
373: ,'award_amt'
374: ,p_to_date_type => l_to_date_type);
375: poa_dbi_util_pkg.add_column(l_col_tbl
376: ,'negotiated_lines'
377: ,'negotiated_lines'
378: ,p_to_date_type => l_to_date_type);
379: poa_dbi_util_pkg.add_column(l_col_tbl
375: poa_dbi_util_pkg.add_column(l_col_tbl
376: ,'negotiated_lines'
377: ,'negotiated_lines'
378: ,p_to_date_type => l_to_date_type);
379: poa_dbi_util_pkg.add_column(l_col_tbl
380: ,'neg_lines_with_cp'
381: ,'neg_lines_with_cp'
382: ,p_to_date_type => l_to_date_type);
383: poa_dbi_util_pkg.add_column(l_col_tbl
379: poa_dbi_util_pkg.add_column(l_col_tbl
380: ,'neg_lines_with_cp'
381: ,'neg_lines_with_cp'
382: ,p_to_date_type => l_to_date_type);
383: poa_dbi_util_pkg.add_column(l_col_tbl
384: ,'proj_savings_amt_' || l_cur_suffix
385: ,'proj_savings_amt'
386: ,p_to_date_type => l_to_date_type);
387:
385: ,'proj_savings_amt'
386: ,p_to_date_type => l_to_date_type);
387:
388: if(l_view_by = 'ITEM+POA_ITEMS') then
389: poa_dbi_util_pkg.add_column(l_col_tbl
390: ,'award_qty'
391: ,'award_qty'
392: ,p_to_date_type => l_to_date_type);
393: end if;
516: decode(base_uom,null,to_number(null),nvl(c_award_qty,0)) POA_MEASURE1, ';
517: end if;
518: --
519: l_sel_clause := l_sel_clause || ' nvl(c_award_amt,0) POA_MEASURE2,
520: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
521: nvl(c_negotiated_lines,0) POA_MEASURE3,
522: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines','p_negotiated_lines') || ' POA_PERCENT2,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
518: --
519: l_sel_clause := l_sel_clause || ' nvl(c_award_amt,0) POA_MEASURE2,
520: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
521: nvl(c_negotiated_lines,0) POA_MEASURE3,
522: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines','p_negotiated_lines') || ' POA_PERCENT2,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
519: l_sel_clause := l_sel_clause || ' nvl(c_award_amt,0) POA_MEASURE2,
520: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
521: nvl(c_negotiated_lines,0) POA_MEASURE3,
522: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines','p_negotiated_lines') || ' POA_PERCENT2,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
520: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1,
521: nvl(c_negotiated_lines,0) POA_MEASURE3,
522: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines','p_negotiated_lines') || ' POA_PERCENT2,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
521: nvl(c_negotiated_lines,0) POA_MEASURE3,
522: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines','p_negotiated_lines') || ' POA_PERCENT2,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
523: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE4,
524: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
525: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT3,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
526: nvl(c_award_amt_total,0) POA_MEASURE5,
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
534: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP') || ' POA_MEASURE9 ';
527: ' || poa_dbi_util_pkg.change_clause('c_award_amt_total','p_award_amt_total') || ' POA_PERCENT4,
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
534: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP') || ' POA_MEASURE9 ';
535: return l_sel_clause;
528: nvl(c_negotiated_lines_total,0) POA_MEASURE6,
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
534: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP') || ' POA_MEASURE9 ';
535: return l_sel_clause;
536: END;
529: ' || poa_dbi_util_pkg.change_clause('c_negotiated_lines_total','p_negotiated_lines_total') || ' POA_PERCENT5,
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
534: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP') || ' POA_MEASURE9 ';
535: return l_sel_clause;
536: END;
537:
530: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP') || ' POA_MEASURE7,
531: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt_total','c_neg_lines_with_cp_total','NP'),
532: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP')) || ' POA_PERCENT6,
533: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE8,
534: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt_total','p_neg_lines_with_cp_total','NP') || ' POA_MEASURE9 ';
535: return l_sel_clause;
536: END;
537:
538: /* Procedure Name : avg_cycle_time_sql
556: l_cur_suffix varchar2(2);
557: l_url varchar2(300);
558: l_custom_sql varchar2(10000);
559: l_view_by_value varchar2(30);
560: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
561: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
562: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
563: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
564: l_where_clause VARCHAR2(2000);
557: l_url varchar2(300);
558: l_custom_sql varchar2(10000);
559: l_view_by_value varchar2(30);
560: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
561: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
562: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
563: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
564: l_where_clause VARCHAR2(2000);
565: l_mv VARCHAR2(30);
558: l_custom_sql varchar2(10000);
559: l_view_by_value varchar2(30);
560: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
561: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
562: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
563: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
564: l_where_clause VARCHAR2(2000);
565: l_mv VARCHAR2(30);
566: ERR_MSG VARCHAR2(100);
559: l_view_by_value varchar2(30);
560: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
561: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
562: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
563: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
564: l_where_clause VARCHAR2(2000);
565: l_mv VARCHAR2(30);
566: ERR_MSG VARCHAR2(100);
567: ERR_CDE NUMBER;
568: l_context_code VARCHAR2(10);
569: l_to_date_type VARCHAR2(10);
570: BEGIN
571:
572: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
573: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
574: l_to_date_type := 'XTD';
575: poa_dbi_sutil_pkg.process_parameters(p_param
576: ,l_view_by
569: l_to_date_type VARCHAR2(10);
570: BEGIN
571:
572: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
573: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
574: l_to_date_type := 'XTD';
575: poa_dbi_sutil_pkg.process_parameters(p_param
576: ,l_view_by
577: ,l_view_by_col
592: , '8.0'
593: , 'NEG'
594: ,'NEG');
595:
596: poa_dbi_util_pkg.add_column(l_col_tbl
597: ,'c_total'
598: ,'count'
599: ,p_to_date_type => l_to_date_type
600: ,p_prior_code => 1);
597: ,'c_total'
598: ,'count'
599: ,p_to_date_type => l_to_date_type
600: ,p_prior_code => 1);
601: poa_dbi_util_pkg.add_column(l_col_tbl
602: ,'preparation_time'
603: ,'preparation_time'
604: ,p_to_date_type => l_to_date_type
605: ,p_prior_code => 1);
602: ,'preparation_time'
603: ,'preparation_time'
604: ,p_to_date_type => l_to_date_type
605: ,p_prior_code => 1);
606: poa_dbi_util_pkg.add_column(l_col_tbl
607: ,'bidding_time'
608: ,'bidding_time'
609: ,p_to_date_type => l_to_date_type
610: ,p_prior_code => 1);
607: ,'bidding_time'
608: ,'bidding_time'
609: ,p_to_date_type => l_to_date_type
610: ,p_prior_code => 1);
611: poa_dbi_util_pkg.add_column(l_col_tbl
612: ,'analysis_time'
613: ,'analysis_time'
614: ,p_to_date_type => l_to_date_type
615: ,p_prior_code => 1);
612: ,'analysis_time'
613: ,'analysis_time'
614: ,p_to_date_type => l_to_date_type
615: ,p_prior_code => 1);
616: poa_dbi_util_pkg.add_column(l_col_tbl
617: ,'award_time'
618: ,'award_time'
619: ,p_to_date_type => l_to_date_type
620: ,p_prior_code => 1);
618: ,'award_time'
619: ,p_to_date_type => l_to_date_type
620: ,p_prior_code => 1);
621: if(l_view_by = 'ITEM+POA_ITEMS') then
622: poa_dbi_util_pkg.add_column(l_col_tbl
623: ,'award_qty'
624: ,'award_qty'
625: ,p_to_date_type => l_to_date_type
626: ,p_prior_code => 1);
753: decode(base_uom,null,to_number(null),nvl(c_award_qty,0)) POA_MEASURE1, ';
754: end if;
755: --
756: l_sel_clause := l_sel_clause || ' c_count POA_MEASURE2,
757: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time','c_count','NP') || ' POA_MEASURE3,
758: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time','c_count','NP') || ' POA_MEASURE4,
759: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time + c_award_time)','c_count','NP') || ' POA_MEASURE5,
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
754: end if;
755: --
756: l_sel_clause := l_sel_clause || ' c_count POA_MEASURE2,
757: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time','c_count','NP') || ' POA_MEASURE3,
758: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time','c_count','NP') || ' POA_MEASURE4,
759: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time + c_award_time)','c_count','NP') || ' POA_MEASURE5,
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
762: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time_total','c_count_total','NP') || ' POA_MEASURE8,
755: --
756: l_sel_clause := l_sel_clause || ' c_count POA_MEASURE2,
757: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time','c_count','NP') || ' POA_MEASURE3,
758: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time','c_count','NP') || ' POA_MEASURE4,
759: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time + c_award_time)','c_count','NP') || ' POA_MEASURE5,
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
762: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time_total','c_count_total','NP') || ' POA_MEASURE8,
763: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time_total','c_count_total','NP') || ' POA_MEASURE9,
758: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time','c_count','NP') || ' POA_MEASURE4,
759: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time + c_award_time)','c_count','NP') || ' POA_MEASURE5,
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
762: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time_total','c_count_total','NP') || ' POA_MEASURE8,
763: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time_total','c_count_total','NP') || ' POA_MEASURE9,
764: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time_total + c_award_time_total)','c_count_total','NP') || ' POA_MEASURE10,
765: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE11 ';
766: return l_sel_clause;
759: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time + c_award_time)','c_count','NP') || ' POA_MEASURE5,
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
762: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time_total','c_count_total','NP') || ' POA_MEASURE8,
763: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time_total','c_count_total','NP') || ' POA_MEASURE9,
764: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time_total + c_award_time_total)','c_count_total','NP') || ' POA_MEASURE10,
765: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE11 ';
766: return l_sel_clause;
767: END;
760: nvl((c_preparation_time + c_bidding_time + c_analysis_time+ c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE6,
761: nvl(c_count_total,0) POA_MEASURE7,
762: ' || poa_dbi_util_pkg.rate_clause('c_preparation_time_total','c_count_total','NP') || ' POA_MEASURE8,
763: ' || poa_dbi_util_pkg.rate_clause('c_bidding_time_total','c_count_total','NP') || ' POA_MEASURE9,
764: ' || poa_dbi_util_pkg.rate_clause('(c_analysis_time_total + c_award_time_total)','c_count_total','NP') || ' POA_MEASURE10,
765: nvl((c_preparation_time_total + c_bidding_time_total + c_analysis_time_total + c_award_time_total),0)/decode(c_count_total,0,null,c_count_total) POA_MEASURE11 ';
766: return l_sel_clause;
767: END;
768:
786: l_cur_suffix varchar2(2);
787: l_url varchar2(300);
788: l_custom_sql varchar2(10000);
789: l_view_by_value varchar2(30);
790: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
791: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
792: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
793: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
794: l_where_clause VARCHAR2(2000);
787: l_url varchar2(300);
788: l_custom_sql varchar2(10000);
789: l_view_by_value varchar2(30);
790: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
791: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
792: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
793: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
794: l_where_clause VARCHAR2(2000);
795: l_mv VARCHAR2(30);
788: l_custom_sql varchar2(10000);
789: l_view_by_value varchar2(30);
790: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
791: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
792: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
793: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
794: l_where_clause VARCHAR2(2000);
795: l_mv VARCHAR2(30);
796: ERR_MSG VARCHAR2(100);
789: l_view_by_value varchar2(30);
790: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
791: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
792: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
793: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
794: l_where_clause VARCHAR2(2000);
795: l_mv VARCHAR2(30);
796: ERR_MSG VARCHAR2(100);
797: ERR_CDE NUMBER;
798: l_context_code VARCHAR2(10);
799: l_to_date_type VARCHAR2(10);
800: BEGIN
801:
802: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
803: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
804: l_to_date_type := 'XTD';
805: poa_dbi_sutil_pkg.process_parameters(p_param
806: ,l_view_by
799: l_to_date_type VARCHAR2(10);
800: BEGIN
801:
802: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
803: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
804: l_to_date_type := 'XTD';
805: poa_dbi_sutil_pkg.process_parameters(p_param
806: ,l_view_by
807: ,l_view_by_col
822: , '8.0'
823: , 'PO'
824: ,'POD');
825:
826: poa_dbi_util_pkg.add_column(l_col_tbl
827: ,'real_svngs_amt_' || l_cur_suffix
828: ,'real_svngs_amt'
829: ,p_to_date_type => l_to_date_type);
830: poa_dbi_util_pkg.add_column(l_col_tbl
826: poa_dbi_util_pkg.add_column(l_col_tbl
827: ,'real_svngs_amt_' || l_cur_suffix
828: ,'real_svngs_amt'
829: ,p_to_date_type => l_to_date_type);
830: poa_dbi_util_pkg.add_column(l_col_tbl
831: ,'purchase_amt_' || l_cur_suffix
832: ,'purchase_amt'
833: ,p_to_date_type => l_to_date_type);
834: poa_dbi_util_pkg.add_column(l_col_tbl
830: poa_dbi_util_pkg.add_column(l_col_tbl
831: ,'purchase_amt_' || l_cur_suffix
832: ,'purchase_amt'
833: ,p_to_date_type => l_to_date_type);
834: poa_dbi_util_pkg.add_column(l_col_tbl
835: ,'neg_purchase_amt_' || l_cur_suffix
836: ,'neg_purchase_amt'
837: ,p_to_date_type => l_to_date_type);
838:
836: ,'neg_purchase_amt'
837: ,p_to_date_type => l_to_date_type);
838:
839: if(l_view_by = 'ITEM+POA_ITEMS') then
840: poa_dbi_util_pkg.add_column(l_col_tbl
841: ,'quantity'
842: ,'quantity'
843: ,p_to_date_type => l_to_date_type);
844: end if;
994: decode(base_uom,null,to_number(null),nvl(c_quantity,0)) POA_MEASURE1, ';
995: end if;
996: --
997: l_sel_clause := l_sel_clause || ' c_real_svngs_amt POA_MEASURE2,
998: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt','p_real_svngs_amt') || ' POA_PERCENT1,
999: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt','c_purchase_amt','P') || ' POA_PERCENT2,
1000: nvl(c_purchase_amt,0) POA_MEASURE3,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
995: end if;
996: --
997: l_sel_clause := l_sel_clause || ' c_real_svngs_amt POA_MEASURE2,
998: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt','p_real_svngs_amt') || ' POA_PERCENT1,
999: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt','c_purchase_amt','P') || ' POA_PERCENT2,
1000: nvl(c_purchase_amt,0) POA_MEASURE3,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
997: l_sel_clause := l_sel_clause || ' c_real_svngs_amt POA_MEASURE2,
998: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt','p_real_svngs_amt') || ' POA_PERCENT1,
999: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt','c_purchase_amt','P') || ' POA_PERCENT2,
1000: nvl(c_purchase_amt,0) POA_MEASURE3,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
1004: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT5,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
999: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt','c_purchase_amt','P') || ' POA_PERCENT2,
1000: nvl(c_purchase_amt,0) POA_MEASURE3,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
1004: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT5,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1000: nvl(c_purchase_amt,0) POA_MEASURE3,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
1004: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT5,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1008: c_real_svngs_amt_total POA_MEASURE6,
1001: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
1004: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT5,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1008: c_real_svngs_amt_total POA_MEASURE6,
1009: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt_total','p_real_svngs_amt_total') || ' POA_PERCENT6,
1002: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1003: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT4,
1004: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT5,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1008: c_real_svngs_amt_total POA_MEASURE6,
1009: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt_total','p_real_svngs_amt_total') || ' POA_PERCENT6,
1010: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt_total','c_purchase_amt_total','P') || ' POA_PERCENT7,
1005: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1008: c_real_svngs_amt_total POA_MEASURE6,
1009: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt_total','p_real_svngs_amt_total') || ' POA_PERCENT6,
1010: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt_total','c_purchase_amt_total','P') || ' POA_PERCENT7,
1011: nvl(c_purchase_amt_total,0) POA_MEASURE7,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1006: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1007: 'P') || ' POA_MEASURE5,
1008: c_real_svngs_amt_total POA_MEASURE6,
1009: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt_total','p_real_svngs_amt_total') || ' POA_PERCENT6,
1010: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt_total','c_purchase_amt_total','P') || ' POA_PERCENT7,
1011: nvl(c_purchase_amt_total,0) POA_MEASURE7,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1008: c_real_svngs_amt_total POA_MEASURE6,
1009: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt_total','p_real_svngs_amt_total') || ' POA_PERCENT6,
1010: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt_total','c_purchase_amt_total','P') || ' POA_PERCENT7,
1011: nvl(c_purchase_amt_total,0) POA_MEASURE7,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1010: ' || poa_dbi_util_pkg.rate_clause('c_real_svngs_amt_total','c_purchase_amt_total','P') || ' POA_PERCENT7,
1011: nvl(c_purchase_amt_total,0) POA_MEASURE7,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1017: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total'),
1018: 'P') || ' POA_MEASURE9,
1011: nvl(c_purchase_amt_total,0) POA_MEASURE7,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1017: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total'),
1018: 'P') || ' POA_MEASURE9,
1019: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT11,
1012: ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT8,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1017: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total'),
1018: 'P') || ' POA_MEASURE9,
1019: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT11,
1020: nvl(p_purchase_amt,0) POA_MEASURE11,
1013: nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
1014: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt_total','p_neg_purchase_amt_total') || ' POA_PERCENT9,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1017: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total'),
1018: 'P') || ' POA_MEASURE9,
1019: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT11,
1020: nvl(p_purchase_amt,0) POA_MEASURE11,
1021: c_real_svngs_amt POA_MEASURE12,
1015: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total') || ' POA_PERCENT10,
1016: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt_total','c_purchase_amt_total'),
1017: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total'),
1018: 'P') || ' POA_MEASURE9,
1019: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT11,
1020: nvl(p_purchase_amt,0) POA_MEASURE11,
1021: c_real_svngs_amt POA_MEASURE12,
1022: p_real_svngs_amt POA_MEASURE13,
1023: c_real_svngs_amt_total POA_MEASURE14,
1020: nvl(p_purchase_amt,0) POA_MEASURE11,
1021: c_real_svngs_amt POA_MEASURE12,
1022: p_real_svngs_amt POA_MEASURE13,
1023: c_real_svngs_amt_total POA_MEASURE14,
1024: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt_total','p_purchase_amt_total') || ' POA_PERCENT14,
1025: nvl(p_purchase_amt_total,0) POA_MEASURE15,
1026: p_real_svngs_amt_total POA_MEASURE16
1027: ';
1028:
1049: l_cur_suffix varchar2(2);
1050: l_url varchar2(300);
1051: l_custom_sql varchar2(10000);
1052: l_view_by_value varchar2(30);
1053: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1054: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1055: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1056: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1057: l_where_clause VARCHAR2(2000);
1050: l_url varchar2(300);
1051: l_custom_sql varchar2(10000);
1052: l_view_by_value varchar2(30);
1053: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1054: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1055: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1056: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1057: l_where_clause VARCHAR2(2000);
1058: l_mv VARCHAR2(30);
1051: l_custom_sql varchar2(10000);
1052: l_view_by_value varchar2(30);
1053: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1054: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1055: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1056: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1057: l_where_clause VARCHAR2(2000);
1058: l_mv VARCHAR2(30);
1059: ERR_MSG VARCHAR2(100);
1052: l_view_by_value varchar2(30);
1053: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1054: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1055: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1056: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1057: l_where_clause VARCHAR2(2000);
1058: l_mv VARCHAR2(30);
1059: ERR_MSG VARCHAR2(100);
1060: ERR_CDE NUMBER;
1060: ERR_CDE NUMBER;
1061: l_context_code VARCHAR2(10);
1062: l_to_date_type VARCHAR2(10);
1063: BEGIN
1064: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1065: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1066: l_to_date_type := 'XTD';
1067: poa_dbi_sutil_pkg.process_parameters(p_param
1068: ,l_view_by
1061: l_context_code VARCHAR2(10);
1062: l_to_date_type VARCHAR2(10);
1063: BEGIN
1064: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1065: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1066: l_to_date_type := 'XTD';
1067: poa_dbi_sutil_pkg.process_parameters(p_param
1068: ,l_view_by
1069: ,l_view_by_col
1084: ,'8.0'
1085: ,'NEG'
1086: ,'NEG');
1087:
1088: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_amt_' || l_cur_suffix, 'award_amt','N',3,p_to_date_type => l_to_date_type);
1089:
1090: l_query := get_awd_trend_sel_clause || '
1091: from '
1092: || poa_dbi_template_pkg.trend_sql(
1115: l_sel_clause := 'select cal.name VIEWBY,';
1116: l_sel_clause := l_sel_clause || '
1117: nvl(c_award_amt,0) POA_MEASURE1,
1118: nvl(p_award_amt,0) POA_MEASURE2,
1119: ' || poa_dbi_util_pkg.change_clause('c_award_amt','p_award_amt') || ' POA_PERCENT1 ';
1120: return l_sel_clause;
1121: END;
1122: --
1123:
1140: l_cur_suffix varchar2(2);
1141: l_url varchar2(300);
1142: l_custom_sql varchar2(10000);
1143: l_view_by_value varchar2(30);
1144: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1145: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1146: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1147: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1148: l_where_clause VARCHAR2(2000);
1141: l_url varchar2(300);
1142: l_custom_sql varchar2(10000);
1143: l_view_by_value varchar2(30);
1144: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1145: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1146: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1147: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1148: l_where_clause VARCHAR2(2000);
1149: l_mv VARCHAR2(30);
1142: l_custom_sql varchar2(10000);
1143: l_view_by_value varchar2(30);
1144: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1145: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1146: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1147: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1148: l_where_clause VARCHAR2(2000);
1149: l_mv VARCHAR2(30);
1150: ERR_MSG VARCHAR2(100);
1143: l_view_by_value varchar2(30);
1144: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1145: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1146: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1147: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1148: l_where_clause VARCHAR2(2000);
1149: l_mv VARCHAR2(30);
1150: ERR_MSG VARCHAR2(100);
1151: ERR_CDE NUMBER;
1151: ERR_CDE NUMBER;
1152: l_context_code VARCHAR2(10);
1153: l_to_date_type VARCHAR2(10);
1154: BEGIN
1155: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1156: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1157: l_to_date_type := 'XTD';
1158: poa_dbi_sutil_pkg.process_parameters(p_param
1159: ,l_view_by
1152: l_context_code VARCHAR2(10);
1153: l_to_date_type VARCHAR2(10);
1154: BEGIN
1155: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1156: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1157: l_to_date_type := 'XTD';
1158: poa_dbi_sutil_pkg.process_parameters(p_param
1159: ,l_view_by
1160: ,l_view_by_col
1175: ,'8.0'
1176: ,'NEG'
1177: ,'NEG');
1178:
1179: poa_dbi_util_pkg.add_column(l_col_tbl, 'preparation_time', 'preparation_time','N',3,p_to_date_type => l_to_date_type);
1180: poa_dbi_util_pkg.add_column(l_col_tbl, 'bidding_time', 'bidding_time','N',3,p_to_date_type => l_to_date_type);
1181: poa_dbi_util_pkg.add_column(l_col_tbl, 'analysis_time', 'analysis_time','N',3,p_to_date_type => l_to_date_type);
1182: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_time', 'award_time','N',3,p_to_date_type => l_to_date_type);
1183: poa_dbi_util_pkg.add_column(l_col_tbl, 'c_total', 'count','N',3,p_to_date_type => l_to_date_type);
1176: ,'NEG'
1177: ,'NEG');
1178:
1179: poa_dbi_util_pkg.add_column(l_col_tbl, 'preparation_time', 'preparation_time','N',3,p_to_date_type => l_to_date_type);
1180: poa_dbi_util_pkg.add_column(l_col_tbl, 'bidding_time', 'bidding_time','N',3,p_to_date_type => l_to_date_type);
1181: poa_dbi_util_pkg.add_column(l_col_tbl, 'analysis_time', 'analysis_time','N',3,p_to_date_type => l_to_date_type);
1182: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_time', 'award_time','N',3,p_to_date_type => l_to_date_type);
1183: poa_dbi_util_pkg.add_column(l_col_tbl, 'c_total', 'count','N',3,p_to_date_type => l_to_date_type);
1184:
1177: ,'NEG');
1178:
1179: poa_dbi_util_pkg.add_column(l_col_tbl, 'preparation_time', 'preparation_time','N',3,p_to_date_type => l_to_date_type);
1180: poa_dbi_util_pkg.add_column(l_col_tbl, 'bidding_time', 'bidding_time','N',3,p_to_date_type => l_to_date_type);
1181: poa_dbi_util_pkg.add_column(l_col_tbl, 'analysis_time', 'analysis_time','N',3,p_to_date_type => l_to_date_type);
1182: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_time', 'award_time','N',3,p_to_date_type => l_to_date_type);
1183: poa_dbi_util_pkg.add_column(l_col_tbl, 'c_total', 'count','N',3,p_to_date_type => l_to_date_type);
1184:
1185: l_query := get_avg_cycle_trend_sel_clause || '
1178:
1179: poa_dbi_util_pkg.add_column(l_col_tbl, 'preparation_time', 'preparation_time','N',3,p_to_date_type => l_to_date_type);
1180: poa_dbi_util_pkg.add_column(l_col_tbl, 'bidding_time', 'bidding_time','N',3,p_to_date_type => l_to_date_type);
1181: poa_dbi_util_pkg.add_column(l_col_tbl, 'analysis_time', 'analysis_time','N',3,p_to_date_type => l_to_date_type);
1182: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_time', 'award_time','N',3,p_to_date_type => l_to_date_type);
1183: poa_dbi_util_pkg.add_column(l_col_tbl, 'c_total', 'count','N',3,p_to_date_type => l_to_date_type);
1184:
1185: l_query := get_avg_cycle_trend_sel_clause || '
1186: from '
1179: poa_dbi_util_pkg.add_column(l_col_tbl, 'preparation_time', 'preparation_time','N',3,p_to_date_type => l_to_date_type);
1180: poa_dbi_util_pkg.add_column(l_col_tbl, 'bidding_time', 'bidding_time','N',3,p_to_date_type => l_to_date_type);
1181: poa_dbi_util_pkg.add_column(l_col_tbl, 'analysis_time', 'analysis_time','N',3,p_to_date_type => l_to_date_type);
1182: poa_dbi_util_pkg.add_column(l_col_tbl, 'award_time', 'award_time','N',3,p_to_date_type => l_to_date_type);
1183: poa_dbi_util_pkg.add_column(l_col_tbl, 'c_total', 'count','N',3,p_to_date_type => l_to_date_type);
1184:
1185: l_query := get_avg_cycle_trend_sel_clause || '
1186: from '
1187: || poa_dbi_template_pkg.trend_sql(
1236: l_cur_suffix varchar2(2);
1237: l_url varchar2(300);
1238: l_custom_sql varchar2(10000);
1239: l_view_by_value varchar2(30);
1240: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1241: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1242: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1243: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1244: l_where_clause VARCHAR2(2000);
1237: l_url varchar2(300);
1238: l_custom_sql varchar2(10000);
1239: l_view_by_value varchar2(30);
1240: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1241: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1242: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1243: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1244: l_where_clause VARCHAR2(2000);
1245: l_mv VARCHAR2(30);
1238: l_custom_sql varchar2(10000);
1239: l_view_by_value varchar2(30);
1240: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1241: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1242: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1243: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1244: l_where_clause VARCHAR2(2000);
1245: l_mv VARCHAR2(30);
1246: ERR_MSG VARCHAR2(100);
1239: l_view_by_value varchar2(30);
1240: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1241: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1242: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1243: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1244: l_where_clause VARCHAR2(2000);
1245: l_mv VARCHAR2(30);
1246: ERR_MSG VARCHAR2(100);
1247: ERR_CDE NUMBER;
1247: ERR_CDE NUMBER;
1248: l_context_code VARCHAR2(10);
1249: l_to_date_type VARCHAR2(10);
1250: BEGIN
1251: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1252: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1253: l_to_date_type := 'XTD';
1254: poa_dbi_sutil_pkg.process_parameters(p_param
1255: ,l_view_by
1248: l_context_code VARCHAR2(10);
1249: l_to_date_type VARCHAR2(10);
1250: BEGIN
1251: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1252: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1253: l_to_date_type := 'XTD';
1254: poa_dbi_sutil_pkg.process_parameters(p_param
1255: ,l_view_by
1256: ,l_view_by_col
1271: ,'8.0'
1272: ,'NEG'
1273: ,'NEG');
1274:
1275: poa_dbi_util_pkg.add_column(l_col_tbl, 'proj_savings_amt_' || l_cur_suffix, 'proj_savings_amt','N',3,p_to_date_type => l_to_date_type);
1276:
1277: l_query := get_prj_svng_trend_sel_clause || '
1278: from '
1279: || poa_dbi_template_pkg.trend_sql(
1302: l_sel_clause := 'select cal.name VIEWBY,';
1303: l_sel_clause := l_sel_clause || '
1304: c_proj_savings_amt POA_MEASURE1,
1305: p_proj_savings_amt POA_MEASURE2,
1306: ' || poa_dbi_util_pkg.change_clause('c_proj_savings_amt','p_proj_savings_amt') || ' POA_PERCENT1 ';
1307: return l_sel_clause;
1308: END;
1309: --
1310:
1328: l_cur_suffix varchar2(2);
1329: l_url varchar2(300);
1330: l_custom_sql varchar2(10000);
1331: l_view_by_value varchar2(30);
1332: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1333: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1334: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1335: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1336: l_where_clause VARCHAR2(2000);
1329: l_url varchar2(300);
1330: l_custom_sql varchar2(10000);
1331: l_view_by_value varchar2(30);
1332: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1333: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1334: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1335: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1336: l_where_clause VARCHAR2(2000);
1337: l_mv VARCHAR2(30);
1330: l_custom_sql varchar2(10000);
1331: l_view_by_value varchar2(30);
1332: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1333: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1334: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1335: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1336: l_where_clause VARCHAR2(2000);
1337: l_mv VARCHAR2(30);
1338: ERR_MSG VARCHAR2(100);
1331: l_view_by_value varchar2(30);
1332: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1333: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1334: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1335: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1336: l_where_clause VARCHAR2(2000);
1337: l_mv VARCHAR2(30);
1338: ERR_MSG VARCHAR2(100);
1339: ERR_CDE NUMBER;
1339: ERR_CDE NUMBER;
1340: l_context_code VARCHAR2(10);
1341: l_to_date_type VARCHAR2(10);
1342: BEGIN
1343: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1344: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1345: l_to_date_type := 'XTD';
1346: poa_dbi_sutil_pkg.process_parameters(p_param
1347: ,l_view_by
1340: l_context_code VARCHAR2(10);
1341: l_to_date_type VARCHAR2(10);
1342: BEGIN
1343: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1344: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1345: l_to_date_type := 'XTD';
1346: poa_dbi_sutil_pkg.process_parameters(p_param
1347: ,l_view_by
1348: ,l_view_by_col
1363: ,'8.0'
1364: ,'NEG'
1365: ,'NEG');
1366:
1367: poa_dbi_util_pkg.add_column(l_col_tbl, 'proj_savings_amt_' || l_cur_suffix, 'proj_savings_amt','N',3,p_to_date_type => l_to_date_type);
1368: poa_dbi_util_pkg.add_column(l_col_tbl, 'neg_lines_with_cp', 'neg_lines_with_cp','N',3,p_to_date_type => l_to_date_type);
1369:
1370: l_query := get_prj_ln_trend_sel_clause || '
1371: from '
1364: ,'NEG'
1365: ,'NEG');
1366:
1367: poa_dbi_util_pkg.add_column(l_col_tbl, 'proj_savings_amt_' || l_cur_suffix, 'proj_savings_amt','N',3,p_to_date_type => l_to_date_type);
1368: poa_dbi_util_pkg.add_column(l_col_tbl, 'neg_lines_with_cp', 'neg_lines_with_cp','N',3,p_to_date_type => l_to_date_type);
1369:
1370: l_query := get_prj_ln_trend_sel_clause || '
1371: from '
1372: || poa_dbi_template_pkg.trend_sql(
1394: BEGIN
1395: l_sel_clause := 'select cal.name VIEWBY,';
1396: l_sel_clause := l_sel_clause || '
1397: nvl(c_neg_lines_with_cp,0) POA_MEASURE1,
1398: ' || poa_dbi_util_pkg.change_clause('c_neg_lines_with_cp','p_neg_lines_with_cp') || ' POA_PERCENT1,
1399: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE2,
1400: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
1401: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT2,
1402: nvl(p_neg_lines_with_cp,0) POA_MEASURE3,
1395: l_sel_clause := 'select cal.name VIEWBY,';
1396: l_sel_clause := l_sel_clause || '
1397: nvl(c_neg_lines_with_cp,0) POA_MEASURE1,
1398: ' || poa_dbi_util_pkg.change_clause('c_neg_lines_with_cp','p_neg_lines_with_cp') || ' POA_PERCENT1,
1399: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE2,
1400: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
1401: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT2,
1402: nvl(p_neg_lines_with_cp,0) POA_MEASURE3,
1403: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE4 ';
1396: l_sel_clause := l_sel_clause || '
1397: nvl(c_neg_lines_with_cp,0) POA_MEASURE1,
1398: ' || poa_dbi_util_pkg.change_clause('c_neg_lines_with_cp','p_neg_lines_with_cp') || ' POA_PERCENT1,
1399: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE2,
1400: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
1401: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT2,
1402: nvl(p_neg_lines_with_cp,0) POA_MEASURE3,
1403: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE4 ';
1404: return l_sel_clause;
1397: nvl(c_neg_lines_with_cp,0) POA_MEASURE1,
1398: ' || poa_dbi_util_pkg.change_clause('c_neg_lines_with_cp','p_neg_lines_with_cp') || ' POA_PERCENT1,
1399: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE2,
1400: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
1401: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT2,
1402: nvl(p_neg_lines_with_cp,0) POA_MEASURE3,
1403: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE4 ';
1404: return l_sel_clause;
1405: END;
1399: ' || poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP') || ' POA_MEASURE2,
1400: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proj_savings_amt','c_neg_lines_with_cp','NP'),
1401: poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP')) || ' POA_PERCENT2,
1402: nvl(p_neg_lines_with_cp,0) POA_MEASURE3,
1403: ' || poa_dbi_util_pkg.rate_clause('p_proj_savings_amt','p_neg_lines_with_cp','NP') || ' POA_MEASURE4 ';
1404: return l_sel_clause;
1405: END;
1406: --
1407:
1424: l_cur_suffix varchar2(2);
1425: l_url varchar2(300);
1426: l_custom_sql varchar2(10000);
1427: l_view_by_value varchar2(30);
1428: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1429: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1430: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1431: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1432: l_where_clause VARCHAR2(2000);
1425: l_url varchar2(300);
1426: l_custom_sql varchar2(10000);
1427: l_view_by_value varchar2(30);
1428: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1429: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1430: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1431: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1432: l_where_clause VARCHAR2(2000);
1433: l_mv VARCHAR2(30);
1426: l_custom_sql varchar2(10000);
1427: l_view_by_value varchar2(30);
1428: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1429: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1430: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1431: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1432: l_where_clause VARCHAR2(2000);
1433: l_mv VARCHAR2(30);
1434: ERR_MSG VARCHAR2(100);
1427: l_view_by_value varchar2(30);
1428: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1429: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1430: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1431: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1432: l_where_clause VARCHAR2(2000);
1433: l_mv VARCHAR2(30);
1434: ERR_MSG VARCHAR2(100);
1435: ERR_CDE NUMBER;
1435: ERR_CDE NUMBER;
1436: l_context_code VARCHAR2(10);
1437: l_to_date_type VARCHAR2(10);
1438: BEGIN
1439: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1440: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1441: l_to_date_type := 'XTD';
1442: poa_dbi_sutil_pkg.process_parameters(p_param
1443: ,l_view_by
1436: l_context_code VARCHAR2(10);
1437: l_to_date_type VARCHAR2(10);
1438: BEGIN
1439: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1440: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1441: l_to_date_type := 'XTD';
1442: poa_dbi_sutil_pkg.process_parameters(p_param
1443: ,l_view_by
1444: ,l_view_by_col
1459: ,'8.0'
1460: ,'PO'
1461: ,'POD');
1462:
1463: poa_dbi_util_pkg.add_column(l_col_tbl, 'real_svngs_amt_' || l_cur_suffix, 'real_svngs_amt','N',3,p_to_date_type => l_to_date_type);
1464:
1465: l_query := get_real_svng_trend_sel_clause || '
1466: from '
1467: || poa_dbi_template_pkg.trend_sql(
1490: l_sel_clause := 'select cal.name VIEWBY,';
1491: l_sel_clause := l_sel_clause || '
1492: c_real_svngs_amt POA_MEASURE1,
1493: p_real_svngs_amt POA_MEASURE2,
1494: ' || poa_dbi_util_pkg.change_clause('c_real_svngs_amt','p_real_svngs_amt') || ' POA_PERCENT1 ';
1495: return l_sel_clause;
1496: END;
1497: --
1498:
1515: l_cur_suffix varchar2(2);
1516: l_url varchar2(300);
1517: l_custom_sql varchar2(10000);
1518: l_view_by_value varchar2(30);
1519: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1520: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1521: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1522: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1523: l_where_clause VARCHAR2(2000);
1516: l_url varchar2(300);
1517: l_custom_sql varchar2(10000);
1518: l_view_by_value varchar2(30);
1519: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1520: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1521: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1522: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1523: l_where_clause VARCHAR2(2000);
1524: l_mv VARCHAR2(30);
1517: l_custom_sql varchar2(10000);
1518: l_view_by_value varchar2(30);
1519: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1520: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1521: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1522: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1523: l_where_clause VARCHAR2(2000);
1524: l_mv VARCHAR2(30);
1525: ERR_MSG VARCHAR2(100);
1518: l_view_by_value varchar2(30);
1519: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1520: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1521: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1522: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1523: l_where_clause VARCHAR2(2000);
1524: l_mv VARCHAR2(30);
1525: ERR_MSG VARCHAR2(100);
1526: ERR_CDE NUMBER;
1526: ERR_CDE NUMBER;
1527: l_context_code VARCHAR2(10);
1528: l_to_date_type VARCHAR2(10);
1529: BEGIN
1530: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1531: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1532: l_to_date_type := 'XTD';
1533: poa_dbi_sutil_pkg.process_parameters(p_param
1534: ,l_view_by
1527: l_context_code VARCHAR2(10);
1528: l_to_date_type VARCHAR2(10);
1529: BEGIN
1530: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1531: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1532: l_to_date_type := 'XTD';
1533: poa_dbi_sutil_pkg.process_parameters(p_param
1534: ,l_view_by
1535: ,l_view_by_col
1550: ,'8.0'
1551: ,'PO'
1552: ,'POD');
1553:
1554: poa_dbi_util_pkg.add_column(l_col_tbl, 'neg_purchase_amt_' || l_cur_suffix, 'neg_purchase_amt','N',3,p_to_date_type => l_to_date_type);
1555: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt','N',3,p_to_date_type => l_to_date_type);
1556:
1557: l_query := get_neg_po_trend_sel_clause || '
1558: from '
1551: ,'PO'
1552: ,'POD');
1553:
1554: poa_dbi_util_pkg.add_column(l_col_tbl, 'neg_purchase_amt_' || l_cur_suffix, 'neg_purchase_amt','N',3,p_to_date_type => l_to_date_type);
1555: poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt','N',3,p_to_date_type => l_to_date_type);
1556:
1557: l_query := get_neg_po_trend_sel_clause || '
1558: from '
1559: || poa_dbi_template_pkg.trend_sql(
1581: BEGIN
1582: l_sel_clause := 'select cal.name VIEWBY,';
1583: l_sel_clause := l_sel_clause || '
1584: nvl(c_neg_purchase_amt,0) POA_MEASURE1,
1585: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT1,
1586: (nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) POA_MEASURE2,
1587: (((nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) - (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) /
1588: decode((nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0)), 0, null, (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) * 100) POA_PERCENT2,
1589: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT4,
1585: ' || poa_dbi_util_pkg.change_clause('c_neg_purchase_amt','p_neg_purchase_amt') || ' POA_PERCENT1,
1586: (nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) POA_MEASURE2,
1587: (((nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) - (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) /
1588: decode((nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0)), 0, null, (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) * 100) POA_PERCENT2,
1589: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT4,
1590: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT3,
1591: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1592: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1593: 'P') || ' POA_MEASURE3,
1586: (nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) POA_MEASURE2,
1587: (((nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) - (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) /
1588: decode((nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0)), 0, null, (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) * 100) POA_PERCENT2,
1589: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT4,
1590: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT3,
1591: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1592: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1593: 'P') || ' POA_MEASURE3,
1594: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1587: (((nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) - (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) /
1588: decode((nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0)), 0, null, (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) * 100) POA_PERCENT2,
1589: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT4,
1590: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT3,
1591: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1592: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1593: 'P') || ' POA_MEASURE3,
1594: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1595: (nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) POA_MEASURE5 ';
1588: decode((nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0)), 0, null, (nvl(p_purchase_amt,0) - nvl(p_neg_purchase_amt,0))) * 100) POA_PERCENT2,
1589: ' || poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt') || ' POA_PERCENT4,
1590: ' || poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt') || ' POA_PERCENT3,
1591: ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_neg_purchase_amt','c_purchase_amt'),
1592: poa_dbi_util_pkg.rate_clause('p_neg_purchase_amt','p_purchase_amt'),
1593: 'P') || ' POA_MEASURE3,
1594: nvl(c_neg_purchase_amt,0) POA_MEASURE4,
1595: (nvl(c_purchase_amt,0) - nvl(c_neg_purchase_amt,0)) POA_MEASURE5 ';
1596: return l_sel_clause;
1606: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1607: IS
1608: l_query varchar2(32000);
1609: l_cur_suffix varchar2(2);
1610: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1611: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1612: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1613: l_where_clause VARCHAR2(2000);
1614: l_filter_rfi VARCHAR2(400);
1607: IS
1608: l_query varchar2(32000);
1609: l_cur_suffix varchar2(2);
1610: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1611: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1612: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1613: l_where_clause VARCHAR2(2000);
1614: l_filter_rfi VARCHAR2(400);
1615: ERR_MSG VARCHAR2(100);
1608: l_query varchar2(32000);
1609: l_cur_suffix varchar2(2);
1610: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1611: l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1612: l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1613: l_where_clause VARCHAR2(2000);
1614: l_filter_rfi VARCHAR2(400);
1615: ERR_MSG VARCHAR2(100);
1616: ERR_CDE NUMBER;
1616: ERR_CDE NUMBER;
1617: l_context_number NUMBER;
1618: l_to_date_type VARCHAR2(10);
1619: BEGIN
1620: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1621: l_to_date_type := 'XTD';
1622: poa_dbi_sutil_pkg.drill_process_parameters(
1623: p_param,
1624: l_cur_suffix,
1771: AND (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
1772: ORDER BY rnk ';
1773:
1774: x_custom_sql := l_query;
1775: poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
1776: END;
1777:
1778:
1779: /* Function Name : get_awd_dtl_filter_clause