[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_NEG_PKG
Source
1 PACKAGE BODY poa_dbi_neg_pkg
2 /* $Header: poadbinegb.pls 120.12 2006/08/27 19:13:42 sriswami noship $ */
3 AS
4 --
5 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
6 FUNCTION get_awd_status_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2) return VARCHAR2;
7 FUNCTION get_avg_cycle_time_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2) return VARCHAR2;
8 FUNCTION get_realized_status_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2) return VARCHAR2;
9 FUNCTION get_awd_trend_sel_clause return VARCHAR2;
10 FUNCTION get_avg_cycle_trend_sel_clause return VARCHAR2;
11 FUNCTION get_prj_svng_trend_sel_clause return VARCHAR2;
12 FUNCTION get_prj_ln_trend_sel_clause return VARCHAR2;
13 FUNCTION get_real_svng_trend_sel_clause return VARCHAR2;
14 FUNCTION get_neg_po_trend_sel_clause return VARCHAR2;
15 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
16 FUNCTION get_awd_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
17 FUNCTION get_avg_cycle_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
18 FUNCTION get_real_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
19 FUNCTION get_awd_dtl_filter_clause return VARCHAR2;
20
21 /* Procedure Name : status_sql
22 This procedure returns the SQL query to display the measures such as Award Amount, Projected
23 Savings Amount, Average Cycle time and their corresponding Changes. This SQL is called by the
24 Sourcing Summary Report
25 */
26
27 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
28 ,x_custom_sql OUT NOCOPY VARCHAR2
29 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
30 IS
31 l_query varchar2(32000);
32 l_view_by varchar2(120);
33 l_view_by_col varchar2(120);
34 l_as_of_date date;
35 l_prev_as_of_date date;
36 l_xtd varchar2(10);
37 l_comparison_type varchar2(1) :='Y';
38 l_nested_pattern number;
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);
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
60 ,l_view_by_col
61 ,l_view_by_value
62 ,l_comparison_type
63 ,l_xtd
64 ,l_as_of_date
65 ,l_prev_as_of_date
66 ,l_cur_suffix
67 ,l_nested_pattern
68 ,l_where_clause
69 ,l_mv
70 ,l_join_tbl
71 ,l_in_join_tbl
72 , x_custom_output
73 ,'N'
74 ,'NEG'
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
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
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
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
108 ,'c_total'
109 ,'count'
110 ,p_to_date_type => l_to_date_type);
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;
120
121 l_url := 'pFunctionName=POA_DBI_NEG_SUM_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
122
123 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url) || ' from ' ||
124 poa_dbi_template_pkg.status_sql(l_mv,
125 l_where_clause,
126 l_join_tbl,
127 p_use_windowing => 'Y',
128 p_col_name => l_col_tbl,
129 p_use_grpid => 'N',
130 p_filter_where => get_status_filter_where(l_view_by),
131 p_in_join_tables => l_in_join_tbl);
132 x_custom_sql := l_query;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 ERR_MSG := SUBSTR(SQLERRM,1,400);
137 end;
138
139 /* Function Name : get_status_filter_where
140 This function is called by the status_sql to append a coalesce statement to the SQL query
141 such that if all the measures mentioned in the list have a 0 or a null value, then that row
142 will be completely filtered out of the displayed result.
143 */
144
145 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
146 IS
147 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
148 BEGIN
149 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
150 l_col_tbl.extend;
151 l_col_tbl(1) := 'POA_MEASURE2';
152 l_col_tbl.extend;
153 l_col_tbl(2) := 'POA_PERCENT1';
154 l_col_tbl.extend;
155 l_col_tbl(3) := 'POA_MEASURE3';
156 l_col_tbl.extend;
157 l_col_tbl(4) := 'POA_MEASURE4';
158 l_col_tbl.extend;
159 l_col_tbl(5) := 'POA_PERCENT2';
160 l_col_tbl.extend;
161 l_col_tbl(6) := 'POA_PERCENT3';
162 l_col_tbl.extend;
163 l_col_tbl(7) := 'POA_MEASURE5';
164 l_col_tbl.extend;
165 l_col_tbl(8) := 'POA_MEASURE6';
166
167 if(p_view_by = 'ITEM+POA_ITEMS') then
168
169 l_col_tbl.extend;
170 l_col_tbl(9) := 'POA_MEASURE1';
171 end if;
172
173
174 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
175
176 END;
177
178 /* Function Name : get_status_sel_clause
179 This function is called by the procedure, status_sql, and it has the computations to be done
180 on the columns that will be selected from the Materialized View. Also, the mapping of these
181 results to the AK Region Items is done in this function.
182 */
183
184 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
185 ,p_view_by_col in VARCHAR2
186 ,p_url in VARCHAR2) return VARCHAR2 IS
187 l_sel_clause varchar2(8000);
188 --
189 BEGIN
190 --
191 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
192 ,'PO'
193 ,'8.0');
194 --
195 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
196 l_sel_clause := l_sel_clause || '
197 v.description POA_ATTRIBUTE2, --Description
198 v2.description POA_ATTRIBUTE3, --UOM
199 oset.POA_MEASURE1 POA_MEASURE1, --Award Quantity
200 ';
201 else
202 l_sel_clause := l_sel_clause || '
203 null POA_ATTRIBUTE2, --Description
204 null POA_ATTRIBUTE3, --UOM
205 null POA_MEASURE1, --Award Quantity
206 ';
207 end if;
208
209 l_sel_clause := l_sel_clause ||
210 ' oset.POA_MEASURE2 POA_MEASURE2, --Award Amount
211 oset.POA_PERCENT1 POA_PERCENT1, --Growth Rate
212 oset.POA_MEASURE3 POA_MEASURE3, --Current Amount
213 oset.POA_MEASURE4 POA_MEASURE4, --Projected Savings Amount
214 oset.POA_PERCENT2 POA_PERCENT2, --Change
215 oset.POA_PERCENT3 POA_PERCENT3, --Rate
216 oset.POA_MEASURE5 POA_MEASURE5, --Change
217 oset.POA_MEASURE6 POA_MEASURE6, --Average Cycle Time
218 oset.POA_MEASURE7 POA_MEASURE7, --Change
219 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total Awarded Amount
220 oset.POA_PERCENT4 POA_PERCENT4, --Grand Total Growth Rate
221 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total Current Amount
222 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total Projected Savings Amount
223 oset.POA_PERCENT5 POA_PERCENT5, --Grand Total Change
224 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total Projected Savings Rate
225 oset.POA_MEASURE11 POA_MEASURE11, --Grand Total Projected Savings Rate Change
226 oset.POA_MEASURE12 POA_MEASURE12, --Grand Total Average Cycle Time
227 oset.POA_MEASURE13 POA_MEASURE13, --Grand Total Change
228 oset.POA_MEASURE14 POA_MEASURE14, --KPI - Prior Awarded Amount
229 oset.POA_MEASURE15 POA_MEASURE15, --KPI - Prior Average Cycle Time
230 oset.POA_MEASURE17 POA_MEASURE17, --KPI - Projected Savings Amount
231 oset.POA_MEASURE18 POA_MEASURE18, --KPI - Prior Projected Savings Amount
232 oset.POA_PERCENT9 POA_PERCENT9, --KPI - Projected Savings Rate
233 oset.POA_PERCENT10 POA_PERCENT10, --KPI - Prior Projected Savings Rate
234 oset.POA_MEASURE20 POA_MEASURE20, --Grand Total - KPI - Projected Savings Amount
235 oset.POA_PERCENT11 POA_PERCENT11, --Grand Total - KPI - Projected Savings Rate
236 oset.POA_MEASURE21 POA_MEASURE21, --Grand Total - KPI - Prior Awarded Amount
237 oset.POA_MEASURE22 POA_MEASURE22, --Grand Total - KPI - Prior Average Cycle Time
238 oset.POA_MEASURE23 POA_MEASURE23, --Grand Total - KPI - Prior Projected Savings Amount
239 oset.POA_PERCENT12 POA_PERCENT12 --Grand Total - KPI - Prior Projected Savings Rate
240 from
241 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
242
243 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
244 l_sel_clause := l_sel_clause || ', base_uom';
245 end if;
246
247 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
248 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
249 l_sel_clause := l_sel_clause || ',
250 base_uom,
251 POA_MEASURE1 ';
252 end if;
253
254 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_PERCENT1,
255 POA_MEASURE3,POA_MEASURE4,
256 POA_PERCENT2,POA_PERCENT3,
257 POA_MEASURE5,POA_MEASURE6,
258 POA_MEASURE7,POA_MEASURE8,
259 POA_PERCENT4,POA_MEASURE9,
260 POA_MEASURE10,POA_PERCENT5,
261 POA_PERCENT6,POA_MEASURE11,
262 POA_MEASURE12,POA_MEASURE13,
263 POA_MEASURE14,POA_MEASURE15,
264 POA_MEASURE17,POA_MEASURE18,
265 POA_PERCENT9,POA_PERCENT10,
266 POA_MEASURE20,POA_PERCENT11,
267 POA_MEASURE21,POA_MEASURE22,
268 POA_MEASURE23,POA_PERCENT12
269 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
270 --
271 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
272 l_sel_clause := l_sel_clause || ' base_uom,
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,
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,
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,
298 nvl(p_award_amt,0) POA_MEASURE14,
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,
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,
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
314 /* Procedure Name : awd_status_sql
315 This procedure returns the SQL query to display the measures such as Award Amount, Projected Savings per Line,
316 and their corresponding change measures. This SQL is called by the Award Summary Report.
317 */
318
319 PROCEDURE awd_status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
320 ,x_custom_sql OUT NOCOPY VARCHAR2
321 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
322 IS
323 l_query varchar2(32000);
324 l_view_by varchar2(120);
325 l_view_by_col varchar2(120);
326 l_as_of_date date;
327 l_prev_as_of_date date;
328 l_xtd varchar2(10);
329 l_comparison_type varchar2(1) :='Y';
330 l_nested_pattern number;
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);
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
352 ,l_view_by_col
353 ,l_view_by_value
354 ,l_comparison_type
355 ,l_xtd
356 ,l_as_of_date
357 ,l_prev_as_of_date
358 ,l_cur_suffix
359 ,l_nested_pattern
360 ,l_where_clause
361 ,l_mv
362 ,l_join_tbl
363 ,l_in_join_tbl
364 , x_custom_output
365 ,'N'
366 ,'NEG'
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
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
384 ,'proj_savings_amt_' || l_cur_suffix
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;
394
395 l_query := get_awd_status_sel_clause(l_view_by, l_view_by_col) || ' from ' ||
396 poa_dbi_template_pkg.status_sql(l_mv,
397 l_where_clause,
398 l_join_tbl,
399 p_use_windowing => 'Y',
400 p_col_name => l_col_tbl,
401 p_use_grpid => 'N',
402 p_filter_where => get_awd_status_filter_where(l_view_by),
403 p_in_join_tables => l_in_join_tbl);
407 WHEN OTHERS THEN
404 x_custom_sql := l_query;
405
406 EXCEPTION
408 ERR_MSG := SUBSTR(SQLERRM,1,400);
409 end;
410
411 /* Function Name : get_awd_status_filter_where
412 This function is called by the awd_status_sql to append a coalesce statement to the SQL query
413 such that if all the measures mentioned in the list have a 0 or a null value, then that row
414 will be completely filtered out of the displayed result.
415 */
416
417 FUNCTION get_awd_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
418 IS
419 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
420 BEGIN
421 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
422 l_col_tbl.extend;
423 l_col_tbl(1) := 'POA_MEASURE2';
424 l_col_tbl.extend;
425 l_col_tbl(2) := 'POA_PERCENT1';
426 l_col_tbl.extend;
427 l_col_tbl(3) := 'POA_MEASURE3';
428 l_col_tbl.extend;
429 l_col_tbl(4) := 'POA_PERCENT2';
430 l_col_tbl.extend;
431 l_col_tbl(5) := 'POA_MEASURE4';
432 l_col_tbl.extend;
433 l_col_tbl(6) := 'POA_PERCENT3';
434
435 if(p_view_by = 'ITEM+POA_ITEMS') then
436 l_col_tbl.extend;
437 l_col_tbl(7) := 'POA_MEASURE1';
438 end if;
439
440 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
441
442 END;
443
444
445 /* Function Name : get_awd_status_sel_clause
446 This function is called by the procedure, awd_status_sql, and it has the computations to be done
447 on the columns that will be selected from the Materialized View. Also, the mapping of these
448 results to the AK Region Items is done in this function.
449 */
450
451 FUNCTION get_awd_status_sel_clause(p_view_by_dim in VARCHAR2
452 ,p_view_by_col in VARCHAR2
453 ) return VARCHAR2 IS
454 l_sel_clause varchar2(8000);
455 --
456 BEGIN
457 --
458 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
459 ,'NEG'
460 ,'8.0');
461 --
462 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
463 l_sel_clause := l_sel_clause || '
464 v.description POA_ATTRIBUTE2, --Description
465 v2.description POA_ATTRIBUTE3, --UOM
466 oset.POA_MEASURE1 POA_MEASURE1, --Award Quantity
467 ';
468 else
469 l_sel_clause := l_sel_clause || '
470 null POA_ATTRIBUTE2, --Description
471 null POA_ATTRIBUTE3, --UOM
472 null POA_MEASURE1, --Award Quantity
473 ';
474 end if;
475
476 l_sel_clause := l_sel_clause ||
477 ' oset.POA_MEASURE2 POA_MEASURE2, --Award Amount
478 oset.POA_PERCENT1 POA_PERCENT1, --Growth Rate
479 oset.POA_MEASURE3 POA_MEASURE3, --Negotiated Lines
480 oset.POA_PERCENT2 POA_PERCENT2, --Change
481 oset.POA_MEASURE4 POA_MEASURE4, --Projected Savings per Line
482 oset.POA_PERCENT3 POA_PERCENT3, --Change
483 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total Awarded Amount
484 oset.POA_PERCENT4 POA_PERCENT4, --Grand Total Growth Rate
485 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total Negotiated Lines
486 oset.POA_PERCENT5 POA_PERCENT5, --Grand Total Change
487 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total Projected Savings per Line
488 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total Change
489 oset.POA_MEASURE8 POA_MEASURE8, --KPI - Prior Projected Savings Amount per Line
490 oset.POA_MEASURE9 POA_MEASURE9 --Grand Total - KPI - Prior Projected Savings Amount per Line
491 from
492 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
493
494 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
495 l_sel_clause := l_sel_clause || ', base_uom';
496 end if;
497
498 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
499 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
500 l_sel_clause := l_sel_clause || ',
501 base_uom,
502 POA_MEASURE1 ';
503 end if;
504
505 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_PERCENT1,
506 POA_MEASURE3,POA_PERCENT2,
507 POA_MEASURE4,POA_PERCENT3,
508 POA_MEASURE5,POA_PERCENT4,
509 POA_MEASURE6,POA_PERCENT5,
510 POA_MEASURE7,POA_PERCENT6,
511 POA_MEASURE8,POA_MEASURE9
512 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
513 --
514 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
515 l_sel_clause := l_sel_clause || ' base_uom,
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'),
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,
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
539 This procedure returns the SQL query to display the measures such as Average Cycle Time and it's
540 phases such as Preparation Time, Bidding Time and Award and Analysis Time. It also displays the
541 Negotiation Line Count.
542 */
543
544 PROCEDURE avg_cycle_time_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
545 ,x_custom_sql OUT NOCOPY VARCHAR2
546 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
547 IS
548 l_query varchar2(32000);
549 l_view_by varchar2(120);
550 l_view_by_col varchar2(120);
551 l_as_of_date date;
552 l_prev_as_of_date date;
553 l_xtd varchar2(10);
554 l_comparison_type varchar2(1) :='Y';
555 l_nested_pattern number;
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);
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
577 ,l_view_by_col
578 ,l_view_by_value
579 ,l_comparison_type
580 ,l_xtd
581 ,l_as_of_date
582 ,l_prev_as_of_date
583 ,l_cur_suffix
584 ,l_nested_pattern
585 ,l_where_clause
586 ,l_mv
587 ,l_join_tbl
588 ,l_in_join_tbl
589 , x_custom_output
590 ,'N'
591 ,'NEG'
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);
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);
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);
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);
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);
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);
627 end if;
628
629 l_query := get_avg_cycle_time_sel_clause(l_view_by, l_view_by_col) || ' from ' ||
630 poa_dbi_template_pkg.status_sql(l_mv,
631 l_where_clause,
632 l_join_tbl,
633 p_use_windowing => 'Y',
634 p_col_name => l_col_tbl,
635 p_use_grpid => 'N',
636 p_filter_where => get_avg_cycle_filter_where(l_view_by),
637 p_in_join_tables => l_in_join_tbl);
638 x_custom_sql := l_query;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 ERR_MSG := SUBSTR(SQLERRM,1,400);
643 end;
644
645 /* Function Name : get_avg_cycle_filter_where
649 */
646 This function is called by the avg_cycle_time_sql to append a coalesce statement to the SQL query
647 such that if all the measures mentioned in the list have a 0 or a null value, then that row
648 will be completely filtered out of the displayed result.
650
651 FUNCTION get_avg_cycle_filter_where(p_view_by in VARCHAR2) return VARCHAR2
652 IS
653 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
654 BEGIN
655 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
656 l_col_tbl.extend;
657 l_col_tbl(1) := 'POA_MEASURE2';
658 l_col_tbl.extend;
659 l_col_tbl(2) := 'POA_MEASURE3';
660 l_col_tbl.extend;
661 l_col_tbl(3) := 'POA_MEASURE4';
662 l_col_tbl.extend;
663 l_col_tbl(4) := 'POA_MEASURE5';
664 l_col_tbl.extend;
665 l_col_tbl(5) := 'POA_MEASURE6';
666 if(p_view_by = 'ITEM+POA_ITEMS') then
667 l_col_tbl.extend;
668 l_col_tbl(6) := 'POA_MEASURE1';
669 end if;
670
671 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
672
673 END;
674
675 /* Function Name : get_avg_cycle_time_sel_clause
676 This function is called by the procedure, avg_cycle_time_sql, and it has the computations to be done
677 on the columns that will be selected from the Materialized View. Also, the mapping of these
678 results to the AK Region Items is done in this function.
679 */
680
681 FUNCTION get_avg_cycle_time_sel_clause(p_view_by_dim in VARCHAR2
682 ,p_view_by_col in VARCHAR2
683 ) return VARCHAR2 IS
684 l_sel_clause varchar2(8000);
685 --
686 BEGIN
687 --
688 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
689 ,'NEG'
690 ,'8.0');
691 --
692 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
693 l_sel_clause := l_sel_clause || '
694 v.description POA_ATTRIBUTE2, --Description
695 v2.description POA_ATTRIBUTE3, --UOM
696 oset.POA_MEASURE1 POA_MEASURE1, --Award Quantity
697 ';
698 else
699 l_sel_clause := l_sel_clause || '
700 null POA_ATTRIBUTE2, --Description
701 null POA_ATTRIBUTE3, --UOM
702 null POA_MEASURE1, --Award Quantity
703 ';
704 end if;
705
706 l_sel_clause := l_sel_clause ||
707 ' oset.POA_MEASURE2 POA_MEASURE2, --Negotiated Lines including RFI
708 oset.POA_MEASURE3 POA_MEASURE3, --Preparation Time
709 oset.POA_MEASURE4 POA_MEASURE4, --Bidding Time
710 oset.POA_MEASURE5 POA_MEASURE5, --Analysis Award Time
711 oset.POA_MEASURE6 POA_MEASURE6, --Total Time
712 ';
713 IF (p_view_by_dim = 'SUPPLIER+POA_SUPPLIERS') THEN
714 l_sel_clause := l_sel_clause || '
715 NULL POA_MEASURE7, --Grand Total Negotiated Lines including RFI
716 NULL POA_MEASURE8, --Grand Total Preparation Time
717 NULL POA_MEASURE9, --Grand Total Bidding Time
718 NULL POA_MEASURE10, --Grand Total Analysis and Award Time
719 NULL POA_MEASURE11 --Grand Total Total Time
720 ';
721 ELSE
722 l_sel_clause := l_sel_clause || '
723 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total Negotiated Lines including RFI
724 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total Preparation Time
725 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total Bidding Time
726 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total Analysis and Award Time
727 oset.POA_MEASURE11 POA_MEASURE11 --Grand Total Total Time
728 ';
729 END IF;
730 l_sel_clause := l_sel_clause || ' from
731 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
732
733 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
734 l_sel_clause := l_sel_clause || ', base_uom';
735 end if;
736
737 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
738 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
739 l_sel_clause := l_sel_clause || ',
740 base_uom,
741 POA_MEASURE1 ';
742 end if;
743
744 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_MEASURE3,
745 POA_MEASURE4,POA_MEASURE5,
746 POA_MEASURE6,POA_MEASURE7,
747 POA_MEASURE8,POA_MEASURE9,
748 POA_MEASURE10,POA_MEASURE11
749 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
750 --
751 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
752 l_sel_clause := l_sel_clause || ' base_uom,
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,
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,
768
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;
769 /* Procedure Name : realized_status_sql
770 This procedure returns the SQL query to display the measures such as Realized Savings, Negotiated
771 Amount, Non-Negotiated Amount and Percent Purchases Negotiated.
772 */
773
774 PROCEDURE realized_status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
775 ,x_custom_sql OUT NOCOPY VARCHAR2
776 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
777 IS
778 l_query varchar2(32000);
779 l_view_by varchar2(120);
780 l_view_by_col varchar2(120);
781 l_as_of_date date;
782 l_prev_as_of_date date;
783 l_xtd varchar2(10);
784 l_comparison_type varchar2(1) :='Y';
785 l_nested_pattern number;
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);
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
807 ,l_view_by_col
808 ,l_view_by_value
809 ,l_comparison_type
810 ,l_xtd
811 ,l_as_of_date
812 ,l_prev_as_of_date
813 ,l_cur_suffix
814 ,l_nested_pattern
815 ,l_where_clause
816 ,l_mv
817 ,l_join_tbl
818 ,l_in_join_tbl
819 , x_custom_output
820 ,'N'
821 ,'PO'
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
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
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;
845
846
847 l_query := get_realized_status_sel_clause(l_view_by, l_view_by_col) || ' from ' ||
848 poa_dbi_template_pkg.status_sql(l_mv,
849 l_where_clause,
850 l_join_tbl,
851 p_use_windowing => 'Y',
852 p_col_name => l_col_tbl,
853 p_use_grpid => 'N',
854 p_filter_where => get_real_status_filter_where(l_view_by),
855 p_in_join_tables => l_in_join_tbl);
856 x_custom_sql := l_query;
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 ERR_MSG := SUBSTR(SQLERRM,1,400);
861 end;
862
863 /* Function Name : get_real_status_filter_where
864 This function is called by the realized_status_sql to append a coalesce statement to the SQL query
865 such that if all the measures mentioned in the list have a 0 or a null value, then that row
866 will be completely filtered out of the displayed result.
867 */
868
869 FUNCTION get_real_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
870 IS
871 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
872 BEGIN
873 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
874 l_col_tbl.extend;
875 l_col_tbl(1) := 'POA_MEASURE2';
876 l_col_tbl.extend;
877 l_col_tbl(2) := 'POA_PERCENT1';
878 l_col_tbl.extend;
879 l_col_tbl(3) := 'POA_PERCENT2';
880 l_col_tbl.extend;
881 l_col_tbl(4) := 'POA_MEASURE3';
882 l_col_tbl.extend;
883 l_col_tbl(5) := 'POA_PERCENT3';
884 l_col_tbl.extend;
885 l_col_tbl(6) := 'POA_MEASURE4';
886 l_col_tbl.extend;
887 l_col_tbl(7) := 'POA_PERCENT4';
888 l_col_tbl.extend;
889 l_col_tbl(8) := 'POA_PERCENT5';
890 l_col_tbl.extend;
891 l_col_tbl(9) := 'POA_MEASURE5';
892
893 if(p_view_by = 'ITEM+POA_ITEMS') then
894 l_col_tbl.extend;
895 l_col_tbl(10) := 'POA_MEASURE1';
896 end if;
897
898
899 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
900
904 This function is called by the procedure, realized_status_sql, and it has the computations to be done
901 END;
902
903 /* Function Name : get_realized_status_sel_clause
905 on the columns that will be selected from the Materialized View. Also, the mapping of these
906 results to the AK Region Items is done in this function.
907 */
908
909 FUNCTION get_realized_status_sel_clause(p_view_by_dim in VARCHAR2
910 ,p_view_by_col in VARCHAR2
911 ) return VARCHAR2 IS
912 l_sel_clause varchar2(8000);
913 --
914 BEGIN
915 --
916 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
917 ,'PO'
918 ,'8.0');
919 --
920 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
921 l_sel_clause := l_sel_clause || '
922 v.description POA_ATTRIBUTE1, --Description
923 v2.description POA_ATTRIBUTE2, --UOM
924 oset.POA_MEASURE1 POA_MEASURE1, --Award Quantity
925 ';
926 else
927 l_sel_clause := l_sel_clause || '
928 null POA_ATTRIBUTE2, --Description
929 null POA_ATTRIBUTE3, --UOM
930 null POA_MEASURE1, --Award Quantity
931 ';
932 end if;
933
934 l_sel_clause := l_sel_clause ||
935 ' oset.POA_MEASURE2 POA_MEASURE2, --Realized Savings Amount
936 oset.POA_PERCENT1 POA_PERCENT1, --Change
937 oset.POA_PERCENT2 POA_PERCENT2, --Rate
938 oset.POA_MEASURE3 POA_MEASURE3, --PO Amount
939 oset.POA_PERCENT3 POA_PERCENT3, --Change
940 oset.POA_MEASURE4 POA_MEASURE4, --Negotiated Purchases Amount
941 oset.POA_PERCENT4 POA_PERCENT4, --Change
942 oset.POA_PERCENT5 POA_PERCENT5, --Percent Purchases Negotiated
943 oset.POA_MEASURE5 POA_MEASURE5, --Change
944 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total Realized Savings Amount
945 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total Change
946 oset.POA_PERCENT7 POA_PERCENT7, --Grand Total Rate
947 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total PO Amount
948 oset.POA_PERCENT8 POA_PERCENT8, --Grand Total Change
949 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total Negotiated Purchases Amount
950 oset.POA_PERCENT9 POA_PERCENT9, --Grand Total Change
951 oset.POA_PERCENT10 POA_PERCENT10, --Grand Total Percent Purchases Negotiated
952 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total Change
953 oset.POA_PERCENT11 POA_PERCENT11, --KPI - Prior Percent Purchases Negotiated
954 oset.POA_MEASURE11 POA_MEASURE11, --KPI - Prior Total PO Amount
955 oset.POA_MEASURE12 POA_MEASURE12, --KPI - Realized Savings Amount
956 oset.POA_MEASURE13 POA_MEASURE13, --KPI - Prior Realized Savings Amount
957 oset.POA_MEASURE14 POA_MEASURE14, --Grand Total - KPI - Realized Savings Amount
958 oset.POA_PERCENT14 POA_PERCENT14, --Grand Total - KPI - Prior Percent Purchases Negotiated
959 oset.POA_MEASURE15 POA_MEASURE15, --Grand Total - KPI - Prior Total PO Amount
960 oset.POA_MEASURE16 POA_MEASURE16, --Grand Total - KPI - Prior Realized Savings Amount
961 oset.POA_MEASURE2 POA_MEASURE17, --Realized Savings Amount in Portlet
962 oset.POA_MEASURE6 POA_MEASURE18 --Grand Total - Realized Savings Amount
963 from
964 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
965
966 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
967 l_sel_clause := l_sel_clause || ', base_uom';
968 end if;
969
970 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
971 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
972 l_sel_clause := l_sel_clause || ',
973 base_uom,
974 POA_MEASURE1 ';
975 end if;
976
977 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_PERCENT1,
978 POA_PERCENT2,POA_MEASURE3,
979 POA_PERCENT3,POA_MEASURE4,
980 POA_PERCENT4,POA_PERCENT5,
981 POA_MEASURE5,POA_MEASURE6,
982 POA_PERCENT6,POA_PERCENT7,
983 POA_MEASURE7,POA_PERCENT8,
984 POA_MEASURE8,POA_PERCENT9,
985 POA_PERCENT10,POA_MEASURE9,
986 POA_PERCENT11,POA_MEASURE11,
987 POA_MEASURE12,POA_MEASURE13,
988 POA_MEASURE14,POA_PERCENT14,
989 POA_MEASURE15,POA_MEASURE16
990 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
991 --
992 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
993 l_sel_clause := l_sel_clause || ' base_uom,
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,
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,
1013 nvl(c_neg_purchase_amt_total,0) POA_MEASURE8,
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,
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,
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
1029 return l_sel_clause;
1030 END;
1031 --
1032
1033 /* Procedure Name : awd_trend_sql
1034 This procedure returns the SQL query to display the awarded amount as a trend.
1035 */
1036
1037 PROCEDURE awd_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1038 x_custom_sql OUT NOCOPY VARCHAR2,
1039 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1040 IS
1041 l_query varchar2(32000);
1042 l_view_by varchar2(120);
1043 l_view_by_col varchar2(120);
1044 l_as_of_date date;
1045 l_prev_as_of_date date;
1046 l_xtd varchar2(10);
1047 l_comparison_type varchar2(1) :='Y';
1048 l_nested_pattern number;
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);
1058 l_mv VARCHAR2(30);
1059 ERR_MSG VARCHAR2(100);
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
1069 ,l_view_by_col
1070 ,l_view_by_value
1071 ,l_comparison_type
1072 ,l_xtd
1073 ,l_as_of_date
1074 ,l_prev_as_of_date
1075 ,l_cur_suffix
1076 ,l_nested_pattern
1077 ,l_where_clause
1078 ,l_mv
1079 ,l_join_tbl
1080 ,l_in_join_tbl
1081 , x_custom_output
1082 ,'Y'
1083 ,'NEG'
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(
1093 l_xtd,
1094 l_comparison_type,
1095 l_mv,
1096 l_where_clause,
1097 l_col_tbl,
1098 p_use_grpid => 'N',
1099 p_in_join_tables => l_in_join_tbl);
1100
1101 x_custom_sql := l_query;
1102
1103 END;
1104
1105 /* Function Name : get_awd_trend_sel_clause
1106 This function is called by the procedure, awd_trend_sql, and it has the computations to be done
1107 on the columns that will be selected from the Materialized View. Also, the mapping of these
1108 results to the AK Region Items is done in this function.
1109 */
1110
1111 FUNCTION get_awd_trend_sel_clause return VARCHAR2
1112 IS
1113 l_sel_clause varchar2(4000);
1114 BEGIN
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
1124 /* Procedure Name : avg_cycle_trend_sql
1125 This procedure returns the SQL query to display the average cycle time measures as a trend.
1126 */
1127
1128 PROCEDURE avg_cycle_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1129 x_custom_sql OUT NOCOPY VARCHAR2,
1130 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1131 IS
1132 l_query varchar2(32000);
1136 l_prev_as_of_date date;
1133 l_view_by varchar2(120);
1134 l_view_by_col varchar2(120);
1135 l_as_of_date date;
1137 l_xtd varchar2(10);
1138 l_comparison_type varchar2(1) :='Y';
1139 l_nested_pattern number;
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);
1149 l_mv VARCHAR2(30);
1150 ERR_MSG VARCHAR2(100);
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
1160 ,l_view_by_col
1161 ,l_view_by_value
1162 ,l_comparison_type
1163 ,l_xtd
1164 ,l_as_of_date
1165 ,l_prev_as_of_date
1166 ,l_cur_suffix
1167 ,l_nested_pattern
1168 ,l_where_clause
1169 ,l_mv
1170 ,l_join_tbl
1171 ,l_in_join_tbl
1172 , x_custom_output
1173 ,'Y'
1174 ,'NEG'
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);
1184
1185 l_query := get_avg_cycle_trend_sel_clause || '
1186 from '
1187 || poa_dbi_template_pkg.trend_sql(
1188 l_xtd,
1189 l_comparison_type,
1190 l_mv,
1191 l_where_clause,
1192 l_col_tbl,
1193 p_use_grpid => 'N',
1194 p_in_join_tables => l_in_join_tbl);
1195
1196 x_custom_sql := l_query;
1197
1198 END;
1199
1200 /* Function Name : get_avg_cycle_trend_sel_clause
1201 This function is called by the procedure, avg_cycle_trend_sql, and it has the computations to be done
1202 on the columns that will be selected from the Materialized View. Also, the mapping of these
1203 results to the AK Region Items is done in this function.
1204 */
1205
1206 FUNCTION get_avg_cycle_trend_sel_clause return VARCHAR2
1207 IS
1208 l_sel_clause varchar2(4000);
1209 BEGIN
1210 l_sel_clause := 'select cal.name VIEWBY,';
1211 l_sel_clause := l_sel_clause || '
1212 nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count) POA_MEASURE1,
1213 ((nvl((c_preparation_time + c_bidding_time + c_analysis_time + c_award_time),0)/decode(c_count,0,null,c_count)) -
1214 (nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0)/decode(p_count,0,null,p_count))) POA_MEASURE2,
1215 nvl((p_preparation_time + p_bidding_time + p_analysis_time + p_award_time),0)/decode(p_count,0,null,p_count) POA_MEASURE3 ';
1216 return l_sel_clause;
1217 END;
1218 --
1219
1220 /* Procedure Name : prj_svng_trend_sql
1221 This procedure returns the SQL query to display the Projected Savings measures as a trend.
1222 */
1223
1224 PROCEDURE prj_svng_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1225 x_custom_sql OUT NOCOPY VARCHAR2,
1226 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1227 IS
1228 l_query varchar2(32000);
1229 l_view_by varchar2(120);
1230 l_view_by_col varchar2(120);
1231 l_as_of_date date;
1232 l_prev_as_of_date date;
1233 l_xtd varchar2(10);
1234 l_comparison_type varchar2(1) :='Y';
1235 l_nested_pattern number;
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);
1245 l_mv VARCHAR2(30);
1246 ERR_MSG VARCHAR2(100);
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';
1257 ,l_view_by_value
1254 poa_dbi_sutil_pkg.process_parameters(p_param
1255 ,l_view_by
1256 ,l_view_by_col
1258 ,l_comparison_type
1259 ,l_xtd
1260 ,l_as_of_date
1261 ,l_prev_as_of_date
1262 ,l_cur_suffix
1263 ,l_nested_pattern
1264 ,l_where_clause
1265 ,l_mv
1266 ,l_join_tbl
1267 ,l_in_join_tbl
1268 , x_custom_output
1269 ,'Y'
1270 ,'NEG'
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(
1280 l_xtd,
1281 l_comparison_type,
1282 l_mv,
1283 l_where_clause,
1284 l_col_tbl,
1285 p_use_grpid => 'N',
1286 p_in_join_tables => l_in_join_tbl);
1287
1288 x_custom_sql := l_query;
1289
1290 END;
1291
1292 /* Function Name : get_prj_svng_trend_sel_clause
1293 This function is called by the procedure, prj_svng_trend_sql, and it has the computations to be done
1294 on the columns that will be selected from the Materialized View. Also, the mapping of these
1295 results to the AK Region Items is done in this function.
1296 */
1297
1298 FUNCTION get_prj_svng_trend_sel_clause return VARCHAR2
1299 IS
1300 l_sel_clause varchar2(4000);
1301 BEGIN
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
1311 /* Procedure Name : prj_svng_ln_trend_sql
1312 This procedure returns the SQL query to display the Projected Savings per Line measures as a trend.
1313 It also displays the Negotiation Lines count used to derive at the Savings per Line measure.
1314 */
1315
1316 PROCEDURE prj_svng_ln_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1317 x_custom_sql OUT NOCOPY VARCHAR2,
1318 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1319 IS
1320 l_query varchar2(32000);
1321 l_view_by varchar2(120);
1322 l_view_by_col varchar2(120);
1323 l_as_of_date date;
1324 l_prev_as_of_date date;
1325 l_xtd varchar2(10);
1326 l_comparison_type varchar2(1) :='Y';
1327 l_nested_pattern number;
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);
1337 l_mv VARCHAR2(30);
1338 ERR_MSG VARCHAR2(100);
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
1348 ,l_view_by_col
1349 ,l_view_by_value
1350 ,l_comparison_type
1351 ,l_xtd
1352 ,l_as_of_date
1353 ,l_prev_as_of_date
1354 ,l_cur_suffix
1355 ,l_nested_pattern
1356 ,l_where_clause
1357 ,l_mv
1358 ,l_join_tbl
1359 ,l_in_join_tbl
1360 , x_custom_output
1361 ,'Y'
1362 ,'NEG'
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 '
1372 || poa_dbi_template_pkg.trend_sql(
1373 l_xtd,
1374 l_comparison_type,
1375 l_mv,
1376 l_where_clause,
1377 l_col_tbl,
1378 p_use_grpid => 'N',
1379 p_in_join_tables => l_in_join_tbl);
1380
1381 x_custom_sql := l_query;
1382
1383 END;
1384
1385 /* Function Name : get_prj_ln_trend_sel_clause
1386 This function is called by the procedure, prj_svng_ln_trend_sql, and it has the computations to be done
1387 on the columns that will be selected from the Materialized View. Also, the mapping of these
1388 results to the AK Region Items is done in this function.
1389 */
1390
1391 FUNCTION get_prj_ln_trend_sel_clause return VARCHAR2
1392 IS
1393 l_sel_clause varchar2(4000);
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,
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
1408 /* Procedure Name : real_svng_trend_sql
1409 This procedure returns the SQL query to display the Realized Savings measure as a trend
1410 */
1411
1412 PROCEDURE real_svng_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1413 x_custom_sql OUT NOCOPY VARCHAR2,
1414 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1415 IS
1416 l_query varchar2(32000);
1417 l_view_by varchar2(120);
1418 l_view_by_col varchar2(120);
1419 l_as_of_date date;
1420 l_prev_as_of_date date;
1421 l_xtd varchar2(10);
1422 l_comparison_type varchar2(1) :='Y';
1423 l_nested_pattern number;
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);
1433 l_mv VARCHAR2(30);
1434 ERR_MSG VARCHAR2(100);
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
1444 ,l_view_by_col
1445 ,l_view_by_value
1446 ,l_comparison_type
1447 ,l_xtd
1448 ,l_as_of_date
1449 ,l_prev_as_of_date
1450 ,l_cur_suffix
1451 ,l_nested_pattern
1452 ,l_where_clause
1453 ,l_mv
1454 ,l_join_tbl
1455 ,l_in_join_tbl
1456 , x_custom_output
1457 ,'Y'
1458 ,'PO'
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(
1468 l_xtd,
1469 l_comparison_type,
1470 l_mv,
1471 l_where_clause,
1472 l_col_tbl,
1473 p_use_grpid => 'N',
1474 p_in_join_tables => l_in_join_tbl);
1475
1476 x_custom_sql := l_query;
1477
1478 END;
1479
1480 /* Function Name : get_real_svng_trend_sel_clause
1484 */
1481 This function is called by the procedure, real_svng_trend_sql, and it has the computations to be done
1482 on the columns that will be selected from the Materialized View. Also, the mapping of these
1483 results to the AK Region Items is done in this function.
1485
1486 FUNCTION get_real_svng_trend_sel_clause return VARCHAR2
1487 IS
1488 l_sel_clause varchar2(4000);
1489 BEGIN
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
1499 /* Procedure Name : neg_po_trend_sql
1500 This procedure returns the SQL query to display the Negotiated and Non-Negotiated Purchases Trend
1501 */
1502
1503 PROCEDURE neg_po_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1504 x_custom_sql OUT NOCOPY VARCHAR2,
1505 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1506 IS
1507 l_query varchar2(32000);
1508 l_view_by varchar2(120);
1509 l_view_by_col varchar2(120);
1510 l_as_of_date date;
1511 l_prev_as_of_date date;
1512 l_xtd varchar2(10);
1513 l_comparison_type varchar2(1) :='Y';
1514 l_nested_pattern number;
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);
1524 l_mv VARCHAR2(30);
1525 ERR_MSG VARCHAR2(100);
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
1535 ,l_view_by_col
1536 ,l_view_by_value
1537 ,l_comparison_type
1538 ,l_xtd
1539 ,l_as_of_date
1540 ,l_prev_as_of_date
1541 ,l_cur_suffix
1542 ,l_nested_pattern
1543 ,l_where_clause
1544 ,l_mv
1545 ,l_join_tbl
1546 ,l_in_join_tbl
1547 , x_custom_output
1548 ,'Y'
1549 ,'PO'
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 '
1559 || poa_dbi_template_pkg.trend_sql(
1560 l_xtd,
1561 l_comparison_type,
1562 l_mv,
1563 l_where_clause,
1564 l_col_tbl,
1565 p_use_grpid => 'N',
1566 p_in_join_tables => l_in_join_tbl);
1567
1568 x_custom_sql := l_query;
1569
1570 END;
1571
1572 /* Function Name : get_neg_po_trend_sel_clause
1573 This function is called by the procedure, neg_po_trend_sql, and it has the computations to be done
1574 on the columns that will be selected from the Materialized View. Also, the mapping of these
1575 results to the AK Region Items is done in this function.
1576 */
1577
1578 FUNCTION get_neg_po_trend_sel_clause return VARCHAR2
1579 IS
1580 l_sel_clause varchar2(4000);
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,
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;
1597 END;
1598
1599
1600 /* Procedure Name : dtl_sql
1601 This procedure returns the SQL query to display the Awarded and Completed Negotiation Lines detail.
1602 */
1606 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1603
1604 PROCEDURE dtl_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1605 x_custom_sql OUT NOCOPY VARCHAR2,
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);
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,
1625 l_where_clause,
1626 l_in_join_tbl,
1627 'NEG',
1628 '8.0',
1629 'NEG',
1630 'NEG'
1631 );
1632
1633 FOR i IN 1..p_param.COUNT
1634 LOOP
1635 IF (p_param(i).parameter_name = 'POA_ATTRIBUTE13') THEN
1636 l_context_number := p_param(i).parameter_id;
1637 END IF;
1638 END LOOP;
1639
1640 IF (l_context_number = 1) THEN
1641 l_filter_rfi := ' AND fact.award_status not in (''NO'', ''QUALIFIED'')
1642 AND fact.award_complete_date IS NOT NULL ';
1643 ELSE
1644 l_filter_rfi := ' AND fact.award_status <> ''NO'' ';
1645 END IF;
1646
1647 l_query :=
1648 'SELECT
1649 ponh.document_number POA_ATTRIBUTE2,
1650 ponip.disp_line_number POA_ATTRIBUTE3,
1651 item.value POA_ATTRIBUTE4,
1652 negorg.name POA_ATTRIBUTE5,
1653 doctl.name POA_ATTRIBUTE6,
1654 hrv.value POA_ATTRIBUTE7,
1655 supv.value POA_ATTRIBUTE8,
1656 decode(i.contract_type, ''BLANKET'', ''Blanket Agreement'',''STANDARD'', ''Standard PO'',''CONTRACT'',''Contract Agreement'' ) POA_ATTRIBUTE9,
1657 poh.segment1 POA_ATTRIBUTE10,
1658 uom.description POA_ATTRIBUTE11,
1659 i.POA_MEASURE1 POA_MEASURE1,
1660 i.POA_MEASURE2 POA_MEASURE2,
1661 i.POA_MEASURE3 POA_MEASURE3,
1662 ''pFunctionName=POA_DBI_NEG_DRILL&AuctionId=''||i.auction_header_id||''&addBreadCrumb=Y&retainAM=Y'' POA_ATTRIBUTE14,
1663 decode(i.po_header_id, null, null,
1664 decode(poh.authorization_status,''APPROVED'',
1665 ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||i.po_header_id||''&addBreadCrumb=Y&retainAM=Y'',
1666 ''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || i.po_header_id || ''&RevisionNum=''
1667 || poh.revision_num || ''&LanguageCode='' || userenv(''LANG'') || ''&DocumentType=PO&DocumentSubtype=STANDARD&OrgId='' || poh.org_id
1668 || ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N''
1669 )) POA_ATTRIBUTE15,
1670 i.POA_ATTRIBUTE16 POA_ATTRIBUTE16,
1671 i.POA_ATTRIBUTE17 POA_ATTRIBUTE17
1672 FROM
1673 (
1674 SELECT
1675 (rank() over(&ORDER_BY_CLAUSE nulls last, auction_header_id, auction_line_number,
1676 bid_number, bid_line_number, org_id, po_item_id, base_uom)) - 1 rnk,
1677 auction_header_id,
1678 auction_line_number,
1679 bid_number,
1680 bid_line_number,
1681 doctype_id,
1682 po_item_id,
1683 org_id,
1684 negotiation_creator_id,
1685 supplier_id,
1686 POA_MEASURE1,
1687 POA_MEASURE2,
1688 POA_MEASURE3,
1689 contract_type,
1690 po_header_id,
1691 base_uom,
1692 POA_ATTRIBUTE16,
1693 POA_ATTRIBUTE17
1694 FROM
1695 (SELECT
1696 fact.auction_header_id,
1697 fact.auction_header_id POA_ATTRIBUTE2,
1698 fact.auction_line_number,
1699 fact.bid_number,
1700 fact.bid_line_number,
1701 fact.doctype_id,
1702 fact.po_item_id,
1703 fact.org_id,
1704 fact.negotiation_creator_id,
1705 fact.supplier_id,
1706 sum(fact.award_qty) POA_MEASURE1,
1707 sum(fact.award_amount_' || l_cur_suffix || ') POA_MEASURE2, -- Add currency suffix
1708 sum(sum(fact.award_amount_' || l_cur_suffix || ')) over() POA_MEASURE3,
1709 fact.contract_type,
1710 fact.po_header_id,
1711 fact.base_uom,
1712 fact.auction_creation_date POA_ATTRIBUTE16, -- Add creation date
1713 nvl(fact.rfi_complete_date,fact.award_complete_date) POA_ATTRIBUTE17 -- Add completed date
1714 from
1715 poa_dbi_neg_f_v fact
1716 where
1717 trunc(nvl(fact.rfi_complete_date, fact.award_complete_date)) between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
1718 ' || fnd_global.newline || l_filter_rfi || l_where_clause || '
1719 group by
1720 fact.auction_header_id,
1721 fact.auction_line_number,
1722 fact.bid_number,
1723 fact.bid_line_number,
1724 fact.doctype_id,
1725 fact.po_item_id,
1726 fact.org_id,
1727 fact.negotiation_creator_id,
1728 fact.supplier_id,
1729 fact.contract_type,
1730 fact.po_header_id,
1731 fact.base_uom,
1732 fact.auction_creation_date,
1733 nvl(fact.rfi_complete_date,fact.award_complete_date)
1734 )
1735 where ' || get_awd_dtl_filter_clause || '
1736 ) i ,
1737 pon_auction_headers_all ponh,
1738 pon_auction_item_prices_all ponip,
1739 pon_bid_headers ponbh,
1740 pon_bid_item_prices ponbip,
1741 poa_items_v item,
1742 poa_suppliers_v supv,
1743 hri_cl_per_v hrv,
1744 mtl_units_of_measure_vl uom,
1745 hr_all_organization_units_vl negorg,
1746 pon_auc_doctypes_tl doctl,
1747 po_headers_all poh
1748 WHERE
1749 i.auction_header_id = ponh.auction_header_id
1750 and i.auction_line_number = ponip.line_number
1751 and ponh.auction_header_id = ponip.auction_header_id
1752 and decode(ponh.award_status, ''QUALIFIED'', null, ponh.auction_header_id) = ponbh.auction_header_id(+) /* Include only the Auction Record of RFI and not the Responses */
1753 and ponbh.auction_header_id = ponbip.auction_header_id(+) /* For Bidded Transactions Only */
1754 and ponbh.bid_number = ponbip.bid_number(+)
1755 and nvl(ponbip.auction_line_number,ponip.line_number) = ponip.line_number /* Filter to give unique record */
1756 AND nvl(ponbh.bid_number,nvl(i.bid_number,-99)) = nvl(i.bid_number,-99)
1757 AND nvl(ponbip.line_number, nvl(i.bid_line_number,-99)) = nvl(i.bid_line_number,-99)
1758 and nvl(ponbh.bid_status,''ACTIVE'') = ''ACTIVE'' /* If a Supplier changes bids, they store ARCHIVED. Ignore them. */
1759 and nvl(ponbip.award_status,''-999'') <> ''REJECTED'' /* Cannot be NULL or REJECTED */
1760 and nvl(ponh.award_status,''-999'') <> ''NO''
1761 and ponh.doctype_id = doctl.doctype_id
1762 and i.po_item_id = item.id
1763 and i.supplier_id = supv.id(+)
1764 and i.negotiation_creator_id = hrv.id
1765 AND SYSDATE BETWEEN hrv.start_date AND hrv.end_date
1766 and i.base_uom = uom.unit_of_measure(+)
1767 and i.org_id = negorg.organization_id
1768 AND i.po_header_id = poh.po_header_id(+)
1769 AND i.doctype_id = doctl.doctype_id
1770 AND doctl.LANGUAGE = USERENV(''LANG'')
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
1780 This function is called by the procedure, dtl_sql, and it has the computations to be done
1781 on the columns that will be selected from the Materialized View. Also, the mapping of these
1782 results to the AK Region Items is done in this function.
1783 */
1784
1785 FUNCTION get_awd_dtl_filter_clause return VARCHAR2
1786 IS
1787 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1788 BEGIN
1789 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
1790 l_col_tbl.extend;
1791 l_col_tbl(1) := 'POA_MEASURE2';
1792 l_col_tbl.extend;
1793 l_col_tbl(2) := 'POA_ATTRIBUTE2';
1794 l_col_tbl.extend;
1795 l_col_tbl(3) := 'POA_MEASURE3';
1796 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1797 END;
1798
1799 /* Function Name : get_dtl_filter
1800 This function is called by the procedure, dtl_sql, and it has the computations to be done
1801 on the columns that will be selected from the Materialized View. Also, the mapping of these
1802 results to the AK Region Items is done in this function.
1803 */
1804
1805 FUNCTION get_dtl_filter(p_doctype_id IN VARCHAR2, show_rfi IN VARCHAR2) return VARCHAR2
1806 IS
1807 l_dtl_filter VARCHAR2(100);
1808 l_selected_doctype VARCHAR2(10);
1809 BEGIN
1810 IF (show_rfi = '1') THEN
1811 SELECT count(*) INTO l_selected_doctype FROM POA_NEG_DOCTYPES_V WHERE INTERNAL_NAME NOT IN ('REQUEST_FOR_INFORMATION')
1812 AND ID = p_doctype_id;
1813 ELSE
1814 RETURN '1';
1815 END IF;
1816 IF(l_selected_doctype > 0) THEN
1817 RETURN '1';
1818 ELSE
1819 RETURN '0';
1820 END IF;
1821 END;
1822 end poa_dbi_neg_pkg;