[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_CUT_PKG
Source
1 PACKAGE BODY poa_dbi_cut_pkg
2 /* $Header: poadbicutb.pls 120.11 2006/01/09 01:48:21 sdiwakar noship $*/
3 AS
4 FUNCTION get_status_sel_clause(
5 p_view_by_dim in VARCHAR2,
6 p_view_by_col in VARCHAR2,
7 p_sec_context in VARCHAR2
8 ) return VARCHAR2;
9 FUNCTION get_con_rpt_sel_clause(
10 p_view_by_dim in VARCHAR2,
11 p_view_by_col in VARCHAR2,
12 p_sec_context in VARCHAR2
13 ) return VARCHAR2;
14 FUNCTION get_ncp_rpt_sel_clause(
15 p_view_by_dim in VARCHAR2,
16 p_view_by_col in VARCHAR2,
17 p_sec_context in VARCHAR2
18 ) return VARCHAR2;
19 FUNCTION get_pcl_rpt_sel_clause(
20 p_view_by_dim in VARCHAR2,
21 p_view_by_col in VARCHAR2,
22 p_sec_context in VARCHAR2
23 ) return VARCHAR2;
24 FUNCTION get_pop_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2;
25 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2;
26 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
27 FUNCTION get_con_rpt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
28 FUNCTION get_ncp_rpt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
29 FUNCTION get_pcl_rpt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
30 FUNCTION get_doctype_filter_where return VARCHAR2;
31 FUNCTION get_doctype_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2)
32 return VARCHAR2;
33
34 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
35 x_custom_sql OUT NOCOPY VARCHAR2,
36 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
37 IS
38 l_query varchar2(10000);
39 l_view_by varchar2(120);
40 l_view_by_col varchar2(120);
41 l_as_of_date date;
42 l_prev_as_of_date date;
43 l_xtd varchar2(10);
44 l_comparison_type varchar2(1) := 'Y';
45 l_nested_pattern number;
46 l_cur_suffix varchar2(2);
47 l_custom_sql varchar2(10000);
48 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
49 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
50 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
51 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
52 l_view_by_value VARCHAR2(100);
53 l_where_clause VARCHAR2(2000);
54 l_where_clause2 VARCHAR2(2000);
55 l_mv VARCHAR2(30);
56 l_mv2 VARCHAR2(30);
57 l_sec_context varchar2(10);
58 l_use_only_agg_mv varchar2(1);
59 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
60
61 BEGIN
62 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
63 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
64
65 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
66 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
67 poa_dbi_sutil_pkg.process_parameters(
68 p_param => p_param,
69 p_view_by => l_view_by,
70 p_view_by_col_name => l_view_by_col,
71 p_view_by_value => l_view_by_value,
72 p_comparison_type => l_comparison_type,
73 p_xtd => l_xtd,
74 p_as_of_date => l_as_of_date,
75 p_prev_as_of_date => l_prev_as_of_date,
76 p_cur_suffix => l_cur_suffix,
77 p_nested_pattern => l_nested_pattern,
78 p_where_clause => l_where_clause,
79 p_mv => l_mv,
80 p_join_tbl => l_join_tbl,
81 p_in_join_tbl => l_in_join_tbl,
82 x_custom_output => x_custom_output,
83 p_trend => 'N',
84 p_func_area => 'PO',
85 p_version => '6.0',
86 p_role => 'COM',
87 p_mv_set => 'PODCUT');
88
89 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
90 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
91 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
92 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
93
94 if(l_view_by = 'ITEM+POA_ITEMS') then
95 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
96 end if;
97
98 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
99 ' || poa_dbi_template_pkg.status_sql(
100 p_fact_name => l_mv,
101 p_where_clause => l_where_clause,
102 p_join_tables => l_join_tbl,
103 p_use_windowing => 'Y',
104 p_col_name => l_col_tbl,
105 p_use_grpid => 'N',
106 p_filter_where => get_status_filter_where(l_view_by),
107 p_in_join_tables => l_in_join_tbl);
108
109 elsif (l_sec_context = 'COMP') then
110 poa_dbi_sutil_pkg.process_parameters(
111 p_param => p_param,
112 p_view_by => l_view_by,
113 p_view_by_col_name => l_view_by_col,
114 p_view_by_value => l_view_by_value,
115 p_comparison_type => l_comparison_type,
116 p_xtd => l_xtd,
117 p_as_of_date => l_as_of_date,
118 p_prev_as_of_date => l_prev_as_of_date,
119 p_cur_suffix => l_cur_suffix,
120 p_nested_pattern => l_nested_pattern,
121 p_where_clause => l_where_clause,
122 p_mv => l_mv,
123 p_join_tbl => l_join_tbl,
124 p_in_join_tbl => l_in_join_tbl,
125 x_custom_output => x_custom_output,
126 p_trend => 'N',
127 p_func_area => 'PO',
128 p_version => '8.0',
129 p_role => 'COM',
130 p_mv_set => 'PODCUTA');
131
132 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
133 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
134 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
135 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
136
137 if(l_view_by = 'ITEM+POA_ITEMS') then
138 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
139 end if;
140
141 /*check if we can get everything from aggregated mv*/
142 l_use_only_agg_mv := 'Y';
143 for i in 1..l_in_join_tbl.count loop
144 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
145 if(l_in_join_tbl(i).aggregated_flag = 'N')then
146 l_use_only_agg_mv := 'N';
147 end if;
148 end if;
149 end loop;
150
151 if(l_use_only_agg_mv = 'N') then
152 poa_dbi_sutil_pkg.process_parameters(
153 p_param => p_param,
154 p_view_by => l_view_by,
155 p_view_by_col_name => l_view_by_col,
156 p_view_by_value => l_view_by_value,
157 p_comparison_type => l_comparison_type,
158 p_xtd => l_xtd,
159 p_as_of_date => l_as_of_date,
160 p_prev_as_of_date => l_prev_as_of_date,
161 p_cur_suffix => l_cur_suffix,
162 p_nested_pattern => l_nested_pattern,
163 p_where_clause => l_where_clause2,
164 p_mv => l_mv2,
165 p_join_tbl => l_join_tbl,
166 p_in_join_tbl => l_in_join_tbl2,
167 x_custom_output => x_custom_output,
168 p_trend => 'N',
169 p_func_area => 'PO',
170 p_version => '8.0',
171 p_role => 'COM',
172 p_mv_set => 'PODCUTB');
173
174 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
175 l_mv_tbl.extend;
176 l_mv_tbl(1).mv_name := l_mv;
177 l_mv_tbl(1).mv_col := l_col_tbl;
178 l_mv_tbl(1).mv_where := l_where_clause;
179 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
180 l_mv_tbl(1).use_grp_id := 'N';
181
182 l_mv_tbl.extend;
183 l_mv_tbl(2).mv_name := l_mv2;
184 l_mv_tbl(2).mv_col := l_col_tbl;
185 l_mv_tbl(2).mv_where := l_where_clause2;
186 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
187 l_mv_tbl(2).use_grp_id := 'N';
188
189 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from (
190 ' || poa_dbi_template_pkg.union_all_status_sql(
191 p_mv => l_mv_tbl,
192 p_join_tables => l_join_tbl,
193 p_use_windowing => 'Y',
194 p_paren_count => 3,
195 p_filter_where => get_status_filter_where(l_view_by),
196 p_generate_viewby => 'Y',
197 p_diff_measures => 'N');
198 else
199 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
200 ' || poa_dbi_template_pkg.status_sql(
201 p_fact_name => l_mv,
202 p_where_clause => l_where_clause,
203 p_join_tables => l_join_tbl,
204 p_use_windowing => 'Y',
205 p_col_name => l_col_tbl,
206 p_use_grpid => 'N',
207 p_filter_where => get_status_filter_where(l_view_by),
208 p_in_join_tables => l_in_join_tbl);
209 end if; /* l_use_only_agg_mv = 'N' */
210 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
211 x_custom_sql := l_query;
212 end;
213
214
215 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
216 IS
217 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
218 BEGIN
219 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
220 l_col_tbl.extend;
221 l_col_tbl(1) := 'POA_MEASURE1';
222 l_col_tbl.extend;
223 l_col_tbl(2) := 'POA_PERCENT1';
224 l_col_tbl.extend;
225 l_col_tbl(3) := 'POA_MEASURE2';
226 l_col_tbl.extend;
227 l_col_tbl(4) := 'POA_PERCENT2';
228 l_col_tbl.extend;
229 l_col_tbl(5) := 'POA_MEASURE3';
230
231 if(p_view_by= 'ITEM+POA_ITEMS') then
232 l_col_tbl.extend;
233 l_col_tbl(6) := 'POA_MEASURE12';
234 end if;
235
236 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
237
238 END;
239
240
241 FUNCTION get_status_sel_clause(
242 p_view_by_dim in VARCHAR2,
243 p_view_by_col in VARCHAR2,
244 p_sec_context in VARCHAR2) return VARCHAR2
245 IS
246 l_sel_clause varchar2(8000);
247 BEGIN
248 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
249
250 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
251 l_sel_clause := l_sel_clause || ' v.description POA_ATTRIBUTE1,
252 v2.description POA_ATTRIBUTE2, oset.POA_MEASURE12 POA_MEASURE12, ';
253 else
254 l_sel_clause := l_sel_clause || '
255 null POA_MEASURE12, --Quantity
256 null POA_ATTRIBUTE1, --Description
257 null POA_ATTRIBUTE2, --UOM
258 ';
259 end if;
260
261 l_sel_clause := l_sel_clause || '
262 oset.POA_MEASURE1 POA_MEASURE1, --PO Purchases Amount
263 oset.POA_PERCENT1 POA_PERCENT1, --Contract Purchases Rate
264 oset.POA_MEASURE2 POA_MEASURE2, --Change
265 oset.POA_PERCENT2 POA_PERCENT2, --Non-Contract Purchases Rate
266 oset.POA_MEASURE3 POA_MEASURE3, --Change
267 null POA_PERCENT3, --(Obsoleted)Contract Leakage Rate
268 null POA_MEASURE4, --(Obsoleted)Change
269 oset.POA_MEASURE5 POA_MEASURE5, --Total PO Purchases Amount
270 oset.POA_MEASURE6 POA_MEASURE6, --Total Contract Purchases Rate
271 oset.POA_MEASURE7 POA_MEASURE7, --Total Change
272 oset.POA_MEASURE8 POA_MEASURE8, --Total Non-Contract Purchases Rate
273 oset.POA_MEASURE9 POA_MEASURE9, --Total Change
274 null POA_PERCENT3, --(Obsoleted)Contract Leakage Rate
275 null POA_MEASURE4, --(Obsoleted)Change';
276
277 if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
278 p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
279 l_sel_clause := l_sel_clause || '
280 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE5,
281 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE6,';
282 else
283 l_sel_clause := l_sel_clause || '
284 null POA_ATTRIBUTE5,
285 null POA_ATTRIBUTE6,';
286 end if;
287
288 if (p_sec_context = 'COMP') then
289 l_sel_clause := l_sel_clause ||'
290 ''pFunctionName=POA_DBI_CC_CUT_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE7,
291 ''pFunctionName=POA_DBI_CC_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y'' POA_ATTRIBUTE8,
292 ''pFunctionName=POA_DBI_CC_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y'' POA_ATTRIBUTE9 ';
293 else
294 l_sel_clause := l_sel_clause ||'
295 ''pFunctionName=POA_DBI_CUT_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE7,
296 ''pFunctionName=POA_DBI_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y'' POA_ATTRIBUTE8,
297 ''pFunctionName=POA_DBI_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y'' POA_ATTRIBUTE9 ';
298 end if;
299
300 l_sel_clause := l_sel_clause || '
301 from
302 (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
303
304 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
305 l_sel_clause := l_sel_clause || ', base_uom';
306 end if;
307
308 l_sel_clause := l_sel_clause || ')) - 1 rnk,'
309 || p_view_by_col;
310
311 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
312 l_sel_clause := l_sel_clause || ', base_uom, POA_MEASURE12';
313 end if;
314
315 l_sel_clause := l_sel_clause || ',
316 POA_PERCENT1, POA_MEASURE1,
317 POA_PERCENT2, POA_MEASURE2,
318 POA_MEASURE3, POA_MEASURE5,
319 POA_MEASURE6, POA_MEASURE7,
320 POA_MEASURE8, POA_MEASURE9
321 from
322 (select ' || p_view_by_col || ',
323 ' || p_view_by_col || ' VIEWBY,';
324
325 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
326 l_sel_clause := l_sel_clause || ' base_uom, decode(base_uom,null,to_number(null),nvl(c_quantity,0)) POA_MEASURE12, ';
327 end if;
328
329 l_sel_clause := l_sel_clause || '
330 nvl(c_purchase_amt,0) POA_MEASURE1,
331 ' ||
332 poa_dbi_util_pkg.rate_clause('c_contract_amt', 'c_purchase_amt')
333 || ' POA_PERCENT1,
334 ' ||
335 poa_dbi_util_pkg.change_clause(
336 poa_dbi_util_pkg.rate_clause( 'c_contract_amt',
337 'c_purchase_amt'),
338 poa_dbi_util_pkg.rate_clause( 'p_contract_amt',
339 'p_purchase_amt'),
340 'P')
341 || ' POA_MEASURE2,
342 ' ||
343 poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
344 || ' POA_PERCENT2,
345 ' ||
346 poa_dbi_util_pkg.change_clause(
347 poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt'),
348 poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt'),
349 'P')
350 || ' POA_MEASURE3,
351 nvl(c_purchase_amt_total,0) POA_MEASURE5,
352 ' ||
353 poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
354 'c_purchase_amt_total')
355 || ' POA_MEASURE6,
356 ' ||
357 poa_dbi_util_pkg.change_clause(
358 poa_dbi_util_pkg.rate_clause( 'c_contract_amt_total',
359 'c_purchase_amt_total'),
363 || ' POA_MEASURE7,
360 poa_dbi_util_pkg.rate_clause( 'p_contract_amt_total',
361 'p_purchase_amt_total'),
362 'P')
364 ' ||
365 poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
366 'c_purchase_amt_total')
367 || ' POA_MEASURE8,
368 ' ||
369 poa_dbi_util_pkg.change_clause(
370 poa_dbi_util_pkg.rate_clause( 'c_n_contract_amt_total',
371 'c_purchase_amt_total'),
372 poa_dbi_util_pkg.rate_clause( 'p_n_contract_amt_total',
373 'p_purchase_amt_total'),
374 'P')
375 || ' POA_MEASURE9 ';
376 return l_sel_clause;
377 END;
378
379
380 PROCEDURE con_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
381 x_custom_sql OUT NOCOPY VARCHAR2,
382 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
383 IS
384 l_query varchar2(10000);
385 l_view_by varchar2(120);
386 l_view_by_col varchar2(120);
387 l_as_of_date date;
388 l_prev_as_of_date date;
389 l_xtd varchar2(10);
390 l_comparison_type varchar2(1) := 'Y';
391 l_nested_pattern number;
392 l_cur_suffix varchar2(2);
393 l_custom_sql varchar2(10000);
394 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
395 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
396 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
397 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
398 l_view_by_value VARCHAR2(100);
399 l_where_clause VARCHAR2(2000);
400 l_where_clause2 VARCHAR2(2000);
401 l_mv VARCHAR2(30);
402 l_mv2 VARCHAR2(30);
403 l_sec_context varchar2(10);
404 l_use_only_agg_mv varchar2(1);
405 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
406 BEGIN
407 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
408 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
409
410 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
411 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
412 poa_dbi_sutil_pkg.process_parameters(
413 p_param => p_param,
414 p_view_by => l_view_by,
415 p_view_by_col_name => l_view_by_col,
416 p_view_by_value => l_view_by_value,
417 p_comparison_type => l_comparison_type,
418 p_xtd => l_xtd,
419 p_as_of_date => l_as_of_date,
420 p_prev_as_of_date => l_prev_as_of_date,
421 p_cur_suffix => l_cur_suffix,
422 p_nested_pattern => l_nested_pattern,
423 p_where_clause => l_where_clause,
424 p_mv => l_mv,
425 p_join_tbl => l_join_tbl,
426 p_in_join_tbl => l_in_join_tbl ,
427 x_custom_output => x_custom_output,
428 p_trend => 'N',
429 p_func_area => 'PO',
430 p_version => '6.0',
431 p_role => 'COM',
432 p_mv_set => 'PODCUT');
433
434 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
435 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
436
437 if(l_view_by = 'ITEM+POA_ITEMS') then
438 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
439 end if;
440
441 l_query := get_con_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
442 ' ||poa_dbi_template_pkg.status_sql(
443 p_fact_name => l_mv,
444 p_where_clause => l_where_clause,
445 p_join_tables => l_join_tbl,
446 p_use_windowing => 'Y',
447 p_col_name => l_col_tbl,
448 p_use_grpid => 'N',
449 p_filter_where => get_con_rpt_filter_where(l_view_by),
450 p_in_join_tables => l_in_join_tbl);
451
452 elsif (l_sec_context = 'COMP') then
453 poa_dbi_sutil_pkg.process_parameters(
454 p_param => p_param,
455 p_view_by => l_view_by,
456 p_view_by_col_name => l_view_by_col,
457 p_view_by_value => l_view_by_value,
458 p_comparison_type => l_comparison_type,
459 p_xtd => l_xtd,
460 p_as_of_date => l_as_of_date,
461 p_prev_as_of_date => l_prev_as_of_date,
462 p_cur_suffix => l_cur_suffix,
463 p_nested_pattern => l_nested_pattern,
464 p_where_clause => l_where_clause,
465 p_mv => l_mv,
466 p_join_tbl => l_join_tbl,
467 p_in_join_tbl => l_in_join_tbl ,
468 x_custom_output => x_custom_output,
469 p_trend => 'N',
470 p_func_area => 'PO',
471 p_version => '8.0',
472 p_role => 'COM',
473 p_mv_set => 'PODCUTA');
474
475 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
476 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
477
478 if(l_view_by = 'ITEM+POA_ITEMS') then
482 /*check if we can get everything from aggregated mv*/
479 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
480 end if;
481
483 l_use_only_agg_mv := 'Y';
484 for i in 1..l_in_join_tbl.count loop
485 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
486 if(l_in_join_tbl(i).aggregated_flag = 'N')then
487 l_use_only_agg_mv := 'N';
488 end if;
489 end if;
490 end loop;
491
492 if(l_use_only_agg_mv = 'N') then
493 poa_dbi_sutil_pkg.process_parameters(
494 p_param => p_param,
495 p_view_by => l_view_by,
496 p_view_by_col_name => l_view_by_col,
497 p_view_by_value => l_view_by_value,
498 p_comparison_type => l_comparison_type,
499 p_xtd => l_xtd,
500 p_as_of_date => l_as_of_date,
501 p_prev_as_of_date => l_prev_as_of_date,
502 p_cur_suffix => l_cur_suffix,
503 p_nested_pattern => l_nested_pattern,
504 p_where_clause => l_where_clause2,
505 p_mv => l_mv2,
506 p_join_tbl => l_join_tbl,
507 p_in_join_tbl => l_in_join_tbl2,
508 x_custom_output => x_custom_output,
509 p_trend => 'N',
510 p_func_area => 'PO',
511 p_version => '8.0',
512 p_role => 'COM',
513 p_mv_set => 'PODCUTB');
514
515 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
516 l_mv_tbl.extend;
517 l_mv_tbl(1).mv_name := l_mv;
518 l_mv_tbl(1).mv_col := l_col_tbl;
519 l_mv_tbl(1).mv_where := l_where_clause;
520 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
521 l_mv_tbl(1).use_grp_id := 'N';
522
523 l_mv_tbl.extend;
524 l_mv_tbl(2).mv_name := l_mv2;
525 l_mv_tbl(2).mv_col := l_col_tbl;
526 l_mv_tbl(2).mv_where := l_where_clause2;
527 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
528 l_mv_tbl(2).use_grp_id := 'N';
529
530 l_query := get_con_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from (
531 ' ||poa_dbi_template_pkg.union_all_status_sql(
532 p_mv => l_mv_tbl,
533 p_join_tables => l_join_tbl,
534 p_use_windowing => 'Y',
535 p_paren_count => 3,
536 p_filter_where => get_con_rpt_filter_where(l_view_by),
537 p_generate_viewby => 'Y',
538 p_diff_measures => 'N');
539
540 else
541 l_query := get_con_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
542 ' ||poa_dbi_template_pkg.status_sql(
543 p_fact_name => l_mv,
544 p_where_clause => l_where_clause,
545 p_join_tables => l_join_tbl,
546 p_use_windowing => 'Y',
547 p_col_name => l_col_tbl,
548 p_use_grpid => 'N',
549 p_filter_where => get_con_rpt_filter_where(l_view_by),
550 p_in_join_tables => l_in_join_tbl);
551
552 end if; /* l_use_only_agg_mv = 'N' */
553 end if; /*l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
554 x_custom_sql := l_query;
555 end;
556
557
558 FUNCTION get_con_rpt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
559 IS
560 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
561 BEGIN
562 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
563 l_col_tbl.extend;
564 l_col_tbl(1) := 'POA_MEASURE1';
565 l_col_tbl.extend;
566 l_col_tbl(2) := 'POA_PERCENT1';
567 l_col_tbl.extend;
568 l_col_tbl(3) := 'POA_MEASURE2';
569 l_col_tbl.extend;
570 l_col_tbl(4) := 'POA_MEASURE3';
571 if(p_view_by= 'ITEM+POA_ITEMS') then
572 l_col_tbl.extend;
573 l_col_tbl(5) := 'POA_MEASURE12';
574 end if;
575
576 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
577
578 END;
579
580
581 FUNCTION get_con_rpt_sel_clause(
582 p_view_by_dim in VARCHAR2,
583 p_view_by_col in VARCHAR2,
584 p_sec_context in VARCHAR2) return VARCHAR2
585 IS
586 l_sel_clause varchar2(8000);
587 BEGIN
588 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
589
590 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
591 l_sel_clause := l_sel_clause || ' v.description POA_ATTRIBUTE1,
592 v2.description POA_ATTRIBUTE2, oset.POA_MEASURE12 POA_MEASURE12, ';
593 else
594 l_sel_clause := l_sel_clause || '
595 null POA_MEASURE12,
596 null POA_ATTRIBUTE1,
597 null POA_ATTRIBUTE2, ';
598 end if;
599
600 l_sel_clause := l_sel_clause || '
601 oset.POA_MEASURE1 POA_MEASURE1, --PO Purchases Amount
602 oset.POA_PERCENT1 POA_PERCENT1, --Contract Purchases Rate
603 oset.POA_MEASURE2 POA_MEASURE2, --Change for Con Purch Rate
604 oset.POA_MEASURE3 POA_MEASURE3, --Contract Purchases Amount
605 oset.POA_MEASURE5 POA_MEASURE5, --Total PO Purchases Amount
609
606 oset.POA_MEASURE6 POA_MEASURE6, --Total Contract Purchases Rate
607 oset.POA_MEASURE7 POA_MEASURE7, --Total Change
608 oset.POA_MEASURE8 POA_MEASURE8, --Total Contract Purchases Amount';
610 if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
611 p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
612 l_sel_clause := l_sel_clause || '
613 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE4,
614 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE5,';
615 else
616 l_sel_clause := l_sel_clause || '
617 null POA_ATTRIBUTE4,
618 null POA_ATTRIBUTE5,';
619 end if;
620
621 if (p_sec_context = 'COMP') then
622 l_sel_clause := l_sel_clause ||'
623 ''pFunctionName=POA_DBI_CC_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
624 ''pFunctionName=POA_DBI_CC_CUT_CDT_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=LOOKUP+CONTRACT_DOCTYPE&pParamIds=Y'' POA_ATTRIBUTE7 ';
625 else
626 l_sel_clause := l_sel_clause ||'
627 ''pFunctionName=POA_DBI_CUT_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
628 ''pFunctionName=POA_DBI_CUT_CDT_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=LOOKUP+CONTRACT_DOCTYPE&pParamIds=Y'' POA_ATTRIBUTE7 ';
629 end if;
630
631 l_sel_clause := l_sel_clause || '
632 from
633 (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
634
635 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
636 l_sel_clause := l_sel_clause || ', base_uom';
637 end if;
638
639 l_sel_clause := l_sel_clause || ')) - 1 rnk,'
640 || p_view_by_col;
641
642 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
643 l_sel_clause := l_sel_clause || ', base_uom, POA_MEASURE12';
644 end if;
645
646 l_sel_clause := l_sel_clause || ',
647 POA_PERCENT1, POA_MEASURE1,
648 POA_MEASURE2,
649 POA_MEASURE3,
650 POA_MEASURE5,
651 POA_MEASURE6, POA_MEASURE7,
652 POA_MEASURE8
653 from
654 (select ' || p_view_by_col || ',
655 ' || p_view_by_col || ' VIEWBY,';
656
657
658 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
659 l_sel_clause := l_sel_clause || ' base_uom, decode(base_uom,null,to_number(null),nvl(c_quantity,0)) POA_MEASURE12, ';
660 end if;
661
662 l_sel_clause := l_sel_clause || '
663 nvl(c_purchase_amt,0) POA_MEASURE1,
664 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
665 ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'), poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
666 nvl(c_contract_amt,0) POA_MEASURE3,
667 nvl(c_purchase_amt_total,0) POA_MEASURE5,
668 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE6,
669 ' || poa_dbi_util_pkg.change_clause( poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total'), poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE7,
670 nvl(c_contract_amt_total,0) POA_MEASURE8';
671 return l_sel_clause;
672 END;
673
674
675 PROCEDURE ncp_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
676 x_custom_sql OUT NOCOPY VARCHAR2,
677 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
678 IS
679 l_query varchar2(10000);
680 l_view_by varchar2(120);
681 l_view_by_col varchar2(120);
682 l_as_of_date date;
683 l_prev_as_of_date date;
684 l_xtd varchar2(10);
685 l_comparison_type varchar2(1) := 'Y';
686 l_nested_pattern number;
687 l_cur_suffix varchar2(2);
688 l_custom_sql varchar2(10000);
689 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
690 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
691 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
692 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
693 l_where_clause VARCHAR2(2000);
694 l_where_clause2 VARCHAR2(2000);
695 l_view_by_value VARCHAR2(100);
696 l_mv VARCHAR2(30);
697 l_mv2 VARCHAR2(30);
698 l_sec_context varchar2(10);
699 l_use_only_agg_mv varchar2(1);
700 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
701 BEGIN
702 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
703 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
704
705 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
706 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
707 poa_dbi_sutil_pkg.process_parameters(
708 p_param => p_param,
709 p_view_by => l_view_by,
710 p_view_by_col_name => l_view_by_col,
711 p_view_by_value => l_view_by_value,
712 p_comparison_type => l_comparison_type,
713 p_xtd => l_xtd,
714 p_as_of_date => l_as_of_date,
715 p_prev_as_of_date => l_prev_as_of_date,
716 p_cur_suffix => l_cur_suffix,
717 p_nested_pattern => l_nested_pattern,
718 p_where_clause => l_where_clause,
719 p_mv => l_mv,
720 p_join_tbl => l_join_tbl,
724 p_func_area => 'PO',
721 p_in_join_tbl => l_in_join_tbl ,
722 x_custom_output => x_custom_output,
723 p_trend => 'N',
725 p_version => '6.0',
726 p_role => 'COM',
727 p_mv_set => 'PODCUT');
728
729 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
730 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
731
732 if(l_view_by = 'ITEM+POA_ITEMS') then
733 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
734 end if;
735
736 l_query := get_ncp_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
737 ' || poa_dbi_template_pkg.status_sql(
738 p_fact_name => l_mv,
739 p_where_clause => l_where_clause,
740 p_join_tables => l_join_tbl,
741 p_use_windowing => 'Y',
742 p_col_name => l_col_tbl,
743 p_use_grpid => 'N',
744 p_filter_where => get_ncp_rpt_filter_where(l_view_by),
745 p_in_join_tables => l_in_join_tbl);
746 elsif(l_sec_context = 'COMP')then
747 poa_dbi_sutil_pkg.process_parameters(
748 p_param => p_param,
749 p_view_by => l_view_by,
750 p_view_by_col_name => l_view_by_col,
751 p_view_by_value => l_view_by_value,
752 p_comparison_type => l_comparison_type,
753 p_xtd => l_xtd,
754 p_as_of_date => l_as_of_date,
755 p_prev_as_of_date => l_prev_as_of_date,
756 p_cur_suffix => l_cur_suffix,
757 p_nested_pattern => l_nested_pattern,
758 p_where_clause => l_where_clause,
759 p_mv => l_mv,
760 p_join_tbl => l_join_tbl,
761 p_in_join_tbl => l_in_join_tbl ,
762 x_custom_output => x_custom_output,
763 p_trend => 'N',
764 p_func_area => 'PO',
765 p_version => '8.0',
766 p_role => 'COM',
767 p_mv_set => 'PODCUTA');
768
769 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
770 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix,'purchase_amt');
771
772 if(l_view_by = 'ITEM+POA_ITEMS') then
773 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
774 end if;
775
776 /*check if we can get everything from aggregated mv*/
777 l_use_only_agg_mv := 'Y';
778 for i in 1..l_in_join_tbl.count loop
779 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
780 if(l_in_join_tbl(i).aggregated_flag = 'N')then
781 l_use_only_agg_mv := 'N';
782 end if;
783 end if;
784 end loop;
785
786 if(l_use_only_agg_mv = 'N') then
787 poa_dbi_sutil_pkg.process_parameters(
788 p_param => p_param,
789 p_view_by => l_view_by,
790 p_view_by_col_name => l_view_by_col,
791 p_view_by_value => l_view_by_value,
792 p_comparison_type => l_comparison_type,
793 p_xtd => l_xtd,
794 p_as_of_date => l_as_of_date,
795 p_prev_as_of_date => l_prev_as_of_date,
796 p_cur_suffix => l_cur_suffix,
797 p_nested_pattern => l_nested_pattern,
798 p_where_clause => l_where_clause2,
799 p_mv => l_mv2,
800 p_join_tbl => l_join_tbl,
801 p_in_join_tbl => l_in_join_tbl2,
802 x_custom_output => x_custom_output,
803 p_trend => 'N',
804 p_func_area => 'PO',
805 p_version => '8.0',
806 p_role => 'COM',
807 p_mv_set => 'PODCUTB');
808
809 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
810 l_mv_tbl.extend;
811 l_mv_tbl(1).mv_name := l_mv;
812 l_mv_tbl(1).mv_col := l_col_tbl;
813 l_mv_tbl(1).mv_where := l_where_clause;
814 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
815 l_mv_tbl(1).use_grp_id := 'N';
816
817 l_mv_tbl.extend;
818 l_mv_tbl(2).mv_name := l_mv2;
819 l_mv_tbl(2).mv_col := l_col_tbl;
820 l_mv_tbl(2).mv_where := l_where_clause2;
821 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
822 l_mv_tbl(2).use_grp_id := 'N';
823
824 l_query := get_ncp_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from (
825 ' || poa_dbi_template_pkg.union_all_status_sql(
826 p_mv => l_mv_tbl,
827 p_join_tables => l_join_tbl,
828 p_use_windowing => 'Y',
829 p_paren_count => 3,
830 p_filter_where => get_ncp_rpt_filter_where(l_view_by),
831 p_generate_viewby => 'Y',
832 p_diff_measures => 'N');
836 p_fact_name => l_mv,
833 else
834 l_query := get_ncp_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
835 ' || poa_dbi_template_pkg.status_sql(
837 p_where_clause => l_where_clause,
838 p_join_tables => l_join_tbl,
839 p_use_windowing => 'Y',
840 p_col_name => l_col_tbl,
841 p_use_grpid => 'N',
842 p_filter_where => get_ncp_rpt_filter_where(l_view_by),
843 p_in_join_tables => l_in_join_tbl);
844
845 end if; /* l_use_only_agg_mv = 'N' */
846 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
847 x_custom_sql := l_query;
848
849 end;
850
851
852 FUNCTION get_ncp_rpt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
853 IS
854 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
855 BEGIN
856 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
857 l_col_tbl.extend;
858 l_col_tbl(1) := 'POA_MEASURE1';
859 l_col_tbl.extend;
860 l_col_tbl(2) := 'POA_MEASURE2';
861 l_col_tbl.extend;
862 l_col_tbl(3) := 'POA_PERCENT2';
863 l_col_tbl.extend;
864 l_col_tbl(4) := 'POA_MEASURE3';
865
866 if(p_view_by= 'ITEM+POA_ITEMS') then
867 l_col_tbl.extend;
868 l_col_tbl(5) := 'POA_MEASURE12';
869 end if;
870
871 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
872
873 END;
874
875
876 FUNCTION get_ncp_rpt_sel_clause(
877 p_view_by_dim in VARCHAR2,
878 p_view_by_col in VARCHAR2,
879 p_sec_context in VARCHAR2) return VARCHAR2
880 IS
881
882 l_sel_clause varchar2(8000);
883
884
885 BEGIN
886 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
887
888 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
889 l_sel_clause := l_sel_clause || ' v.description POA_ATTRIBUTE1,
890 v2.description POA_ATTRIBUTE2, oset.POA_MEASURE12 POA_MEASURE12, ';
891 else
892 l_sel_clause := l_sel_clause || '
893 null POA_MEASURE12,
894 null POA_ATTRIBUTE1,
895 null POA_ATTRIBUTE2, ';
896 end if;
897
898 l_sel_clause := l_sel_clause || '
899 oset.POA_MEASURE1 POA_MEASURE1, --PO Purchases Amount
900 oset.POA_MEASURE2 POA_MEASURE2, --Non-Contract Purchases Amount
901 oset.POA_PERCENT2 POA_PERCENT2, --Non-Contract Purchases Rate
905 oset.POA_MEASURE8 POA_MEASURE8, --Total Non-Contract Purchases Rate
902 oset.POA_MEASURE3 POA_MEASURE3, --Change
903 oset.POA_MEASURE5 POA_MEASURE5, --Total PO Purchases Amount
904 oset.POA_MEASURE7 POA_MEASURE7, --Total Non-Contract Purchases Amount
906 oset.POA_MEASURE9 POA_MEASURE9, --Total Change';
907
908 if(p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
909 l_sel_clause := l_sel_clause || fnd_global.newline ||'
910 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE4,
911 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE5,';
912 else
913 l_sel_clause := l_sel_clause || fnd_global.newline ||
914 'null POA_ATTRIBUTE4,'||fnd_global.newline||
915 'null POA_ATTRIBUTE5,'||fnd_global.newline;
916 end if;
917
918 if (p_sec_context = 'COMP') then
919 l_sel_clause := l_sel_clause ||'
920 ''pFunctionName=POA_DBI_CC_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
921 ''pFunctionName=POA_DBI_CC_CUD_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7 ';
922 else
923 l_sel_clause := l_sel_clause ||'
924 ''pFunctionName=POA_DBI_CUT_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
925 ''pFunctionName=POA_DBI_CUD_NCP_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7 ';
926 end if;
927
928 l_sel_clause := l_sel_clause || fnd_global.newline || 'from
929 (select (rank() over
930 ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
931
932 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
933 l_sel_clause := l_sel_clause || ', base_uom';
934 end if;
935
936 l_sel_clause := l_sel_clause || ')) - 1 rnk,'
937 || p_view_by_col;
938
939 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
940 l_sel_clause := l_sel_clause || ', base_uom, POA_MEASURE12';
941 end if;
942
943 l_sel_clause := l_sel_clause || ',
944 POA_MEASURE1,
945 POA_PERCENT2, POA_MEASURE2,
946 POA_MEASURE3,
947 POA_MEASURE5,
948 POA_MEASURE7,
949 POA_MEASURE8, POA_MEASURE9
950 from
951 (select ' || p_view_by_col || ',
952 ' || p_view_by_col || ' VIEWBY,';
953
954
955 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
956 l_sel_clause := l_sel_clause || ' base_uom, decode(base_uom,null,to_number(null),nvl(c_quantity,0)) POA_MEASURE12, ';
957 end if;
958
959 l_sel_clause := l_sel_clause || '
960 nvl(c_purchase_amt,0) POA_MEASURE1,
961 nvl(c_n_contract_amt,0) POA_MEASURE2, ' ||
962 poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt')
963 || ' POA_PERCENT2,
964 ' ||
965 poa_dbi_util_pkg.rate_clause('c_n_contract_amt', 'c_purchase_amt') || ' - ' ||
966 poa_dbi_util_pkg.rate_clause('p_n_contract_amt', 'p_purchase_amt')
967 || ' POA_MEASURE3,
968 nvl(c_purchase_amt_total,0) POA_MEASURE5,
969 nvl(c_n_contract_amt_total,0) POA_MEASURE7, ' ||
970 poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
971 || ' POA_MEASURE8,
972 ' ||
973
974 poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total', 'c_purchase_amt_total')
975 || ' - ' ||
976 poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total',
977 'p_purchase_amt_total') ||
978 ' POA_MEASURE9
979 ';
980
981 return l_sel_clause;
982 END;
983
984 PROCEDURE pcl_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
985 x_custom_sql OUT NOCOPY VARCHAR2,
986 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
987 IS
988 l_query varchar2(10000);
989 l_view_by varchar2(120);
990 l_view_by_col varchar2(120);
991 l_as_of_date date;
992 l_prev_as_of_date date;
993 l_xtd varchar2(10);
994 l_comparison_type varchar2(1) := 'Y';
995 l_nested_pattern number;
996 l_cur_suffix varchar2(2);
997 l_custom_sql varchar2(10000);
998 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
999 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1000 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1001 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1002 l_where_clause VARCHAR2(2000);
1003 l_where_clause2 VARCHAR2(2000);
1004 l_mv VARCHAR2(30);
1005 l_mv2 VARCHAR2(30);
1006 l_view_by_value VARCHAR2(100);
1007 l_sec_context varchar2(10);
1008 l_use_only_agg_mv varchar2(1);
1009 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1010 BEGIN
1011 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1012 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1013
1014 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1015 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1016 poa_dbi_sutil_pkg.process_parameters(
1017 p_param => p_param,
1018 p_view_by => l_view_by,
1019 p_view_by_col_name => l_view_by_col,
1020 p_view_by_value => l_view_by_value,
1021 p_comparison_type => l_comparison_type,
1022 p_xtd => l_xtd,
1023 p_as_of_date => l_as_of_date,
1024 p_prev_as_of_date => l_prev_as_of_date,
1025 p_cur_suffix => l_cur_suffix,
1026 p_nested_pattern => l_nested_pattern,
1027 p_where_clause => l_where_clause,
1028 p_mv => l_mv,
1029 p_join_tbl => l_join_tbl,
1030 p_in_join_tbl => l_in_join_tbl ,
1031 x_custom_output => x_custom_output,
1035 p_role => 'COM',
1032 p_trend => 'N',
1033 p_func_area => 'PO',
1034 p_version => '6.0',
1036 p_mv_set => 'PODCUT');
1037
1038 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1039 'above_contract_amt');
1040 poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1041 'below_contract_amt');
1042 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1043
1044 if(l_view_by = 'ITEM+POA_ITEMS') then
1045 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1046 end if;
1047
1048 l_query := get_pcl_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
1049 ' || poa_dbi_template_pkg.status_sql(
1050 p_fact_name => l_mv,
1051 p_where_clause => l_where_clause,
1052 p_join_tables => l_join_tbl,
1053 p_use_windowing => 'Y',
1054 p_col_name => l_col_tbl,
1055 p_use_grpid => 'N',
1056 p_filter_where => get_pcl_rpt_filter_where(l_view_by),
1057 p_in_join_tables => l_in_join_tbl);
1058
1059 elsif(l_sec_context = 'COMP')then
1060 poa_dbi_sutil_pkg.process_parameters(
1061 p_param => p_param,
1062 p_view_by => l_view_by,
1063 p_view_by_col_name => l_view_by_col,
1064 p_view_by_value => l_view_by_value,
1065 p_comparison_type => l_comparison_type,
1066 p_xtd => l_xtd,
1067 p_as_of_date => l_as_of_date,
1068 p_prev_as_of_date => l_prev_as_of_date,
1069 p_cur_suffix => l_cur_suffix,
1070 p_nested_pattern => l_nested_pattern,
1071 p_where_clause => l_where_clause,
1072 p_mv => l_mv,
1073 p_join_tbl => l_join_tbl,
1074 p_in_join_tbl => l_in_join_tbl,
1075 x_custom_output => x_custom_output,
1076 p_trend => 'N',
1077 p_func_area => 'PO',
1078 p_version => '8.0',
1079 p_role => 'COM',
1080 p_mv_set => 'PODCUTA');
1081
1082 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt'); poa_dbi_util_pkg.add_column(l_col_tbl, 'above_contract_amt_' || l_cur_suffix,
1083 'above_contract_amt');
1084 poa_dbi_util_pkg.add_column(l_col_tbl, 'below_contract_amt_' || l_cur_suffix,
1085 'below_contract_amt');
1086 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1087
1088 if(l_view_by = 'ITEM+POA_ITEMS') then
1089 poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
1090 end if;
1091
1092 /*check if we can get everything from aggregated mv*/
1093 l_use_only_agg_mv := 'Y';
1094 for i in 1..l_in_join_tbl.count loop
1095 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
1096 if(l_in_join_tbl(i).aggregated_flag = 'N')then
1097 l_use_only_agg_mv := 'N';
1098 end if;
1099 end if;
1100 end loop;
1101
1102 if(l_use_only_agg_mv = 'N') then
1103 poa_dbi_sutil_pkg.process_parameters(
1104 p_param => p_param,
1105 p_view_by => l_view_by,
1106 p_view_by_col_name => l_view_by_col,
1107 p_view_by_value => l_view_by_value,
1108 p_comparison_type => l_comparison_type,
1109 p_xtd => l_xtd,
1110 p_as_of_date => l_as_of_date,
1111 p_prev_as_of_date => l_prev_as_of_date,
1112 p_cur_suffix => l_cur_suffix,
1113 p_nested_pattern => l_nested_pattern,
1114 p_where_clause => l_where_clause2,
1115 p_mv => l_mv2,
1116 p_join_tbl => l_join_tbl,
1117 p_in_join_tbl => l_in_join_tbl2,
1118 x_custom_output => x_custom_output,
1119 p_trend => 'N',
1120 p_func_area => 'PO',
1121 p_version => '8.0',
1122 p_role => 'COM',
1123 p_mv_set => 'PODCUTB');
1124
1125 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1126 l_mv_tbl.extend;
1127 l_mv_tbl(1).mv_name := l_mv;
1128 l_mv_tbl(1).mv_col := l_col_tbl;
1129 l_mv_tbl(1).mv_where := l_where_clause;
1130 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
1131 l_mv_tbl(1).use_grp_id := 'N';
1132
1133 l_mv_tbl.extend;
1134 l_mv_tbl(2).mv_name := l_mv2;
1135 l_mv_tbl(2).mv_col := l_col_tbl;
1136 l_mv_tbl(2).mv_where := l_where_clause2;
1137 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
1138 l_mv_tbl(2).use_grp_id := 'N';
1139
1140 l_query := get_pcl_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from (
1141 ' || poa_dbi_template_pkg.union_all_status_sql(
1142 p_mv => l_mv_tbl,
1143 p_join_tables => l_join_tbl,
1144 p_use_windowing => 'Y',
1145 p_paren_count => 3,
1146 p_filter_where => get_pcl_rpt_filter_where(l_view_by),
1147 p_generate_viewby => 'Y',
1148 p_diff_measures => 'N');
1149 else
1150 l_query := get_pcl_rpt_sel_clause(l_view_by, l_view_by_col, l_sec_context) || ' from
1151 ' || poa_dbi_template_pkg.status_sql(
1155 p_use_windowing => 'Y',
1152 p_fact_name => l_mv,
1153 p_where_clause => l_where_clause,
1154 p_join_tables => l_join_tbl,
1156 p_col_name => l_col_tbl,
1157 p_use_grpid => 'N',
1158 p_filter_where => get_pcl_rpt_filter_where(l_view_by),
1159 p_in_join_tables => l_in_join_tbl);
1160 end if; /* l_use_only_agg_mv = 'N' */
1161 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
1162 x_custom_sql := l_query;
1163 end;
1164
1165
1166 FUNCTION get_pcl_rpt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
1167 IS
1168 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1169 BEGIN
1170 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
1171 l_col_tbl.extend;
1172 l_col_tbl(1) := 'POA_MEASURE1';
1173 l_col_tbl.extend;
1174 l_col_tbl(2) := 'POA_MEASURE2';
1175 l_col_tbl.extend;
1176 l_col_tbl(3) := 'POA_MEASURE3';
1177 l_col_tbl.extend;
1178 l_col_tbl(4) := 'POA_PERCENT3';
1179 l_col_tbl.extend;
1180 l_col_tbl(5) := 'POA_MEASURE4';
1181 l_col_tbl.extend;
1182 l_col_tbl(6) := 'POA_MEASURE6';
1183 l_col_tbl.extend;
1184 l_col_tbl(7) := 'POA_MEASURE13';
1185
1186 if(p_view_by= 'ITEM+POA_ITEMS') then
1187 l_col_tbl.extend;
1188 l_col_tbl(8) := 'POA_MEASURE12';
1189 end if;
1190 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1191
1192 END;
1193
1194
1195 FUNCTION get_pcl_rpt_sel_clause(
1196 p_view_by_dim in VARCHAR2,
1197 p_view_by_col in VARCHAR2,
1198 p_sec_context in VARCHAR2) return VARCHAR2
1199 IS
1200 l_sel_clause varchar2(8000);
1201 BEGIN
1202 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
1203
1204 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1205 l_sel_clause := l_sel_clause || ' v.description POA_ATTRIBUTE1,
1206 v2.description POA_ATTRIBUTE2, oset.POA_MEASURE12 POA_MEASURE12, ';
1207 else
1208 l_sel_clause := l_sel_clause || '
1209 null POA_MEASURE12,
1210 null POA_ATTRIBUTE1,
1211 null POA_ATTRIBUTE2, ';
1212 end if;
1213
1214 l_sel_clause := l_sel_clause || '
1215 oset.POA_MEASURE1 POA_MEASURE1, --PO Purchases Amount
1216 oset.POA_MEASURE2 POA_MEASURE2, --Leakage Impact Amount
1217 oset.POA_MEASURE3 POA_MEASURE3, --Below Contract Amount
1218 oset.POA_PERCENT3 POA_PERCENT3, --Contract Leakage Rate
1219 oset.POA_MEASURE4 POA_MEASURE4, --Change
1220 oset.POA_MEASURE5 POA_MEASURE5, --PO Purchases Amount Total
1221 oset.POA_MEASURE6 POA_MEASURE6, --Above Contract Amount
1222 oset.POA_MEASURE7 POA_MEASURE7, --Leakage Impact Amount Total
1223 oset.POA_MEASURE8 POA_MEASURE8, --Below Contract Amount Total
1224 oset.POA_MEASURE9 POA_MEASURE9, --Above Contract Amount Total
1225 oset.POA_MEASURE10 POA_MEASURE10, --Contract Leakage Rate Total
1226 oset.POA_MEASURE11 POA_MEASURE11, --Change Total
1227 oset.POA_MEASURE13 POA_MEASURE13, --Contract Leakage Amount
1228 oset.POA_MEASURE14 POA_MEASURE14, --Contract Leakage Amount Total';
1229
1230 if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
1231 p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
1232 l_sel_clause := l_sel_clause || '
1233 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE4,
1234 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_CUT_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE5,';
1235 else
1236 l_sel_clause := l_sel_clause || '
1237 null POA_ATTRIBUTE4,
1238 null POA_ATTRIBUTE5,';
1239 end if;
1240
1241 if (p_sec_context = 'COMP') then
1242 l_sel_clause := l_sel_clause ||'
1243 ''pFunctionName=POA_DBI_CC_CUT_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
1244 ''pFunctionName=POA_DBI_CC_CUD_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7 ';
1245 else
1246 l_sel_clause := l_sel_clause ||'
1247 ''pFunctionName=POA_DBI_CUT_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE6,
1248 ''pFunctionName=POA_DBI_CUD_PCL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7 ';
1249 end if;
1250
1251 l_sel_clause := l_sel_clause || '
1252 from
1253 (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
1254
1255 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1256 l_sel_clause := l_sel_clause || ', base_uom';
1257 end if;
1258
1259 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
1260
1261 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1262 l_sel_clause := l_sel_clause || ', base_uom, POA_MEASURE12';
1263 end if;
1264
1265 l_sel_clause := l_sel_clause || ',
1266 POA_MEASURE1,
1267 POA_MEASURE2,
1268 POA_PERCENT3, POA_MEASURE3,
1269 POA_MEASURE4, POA_MEASURE5,
1270 POA_MEASURE6, POA_MEASURE7,
1271 POA_MEASURE8, POA_MEASURE9,
1272 POA_MEASURE10, POA_MEASURE11,
1273 POA_MEASURE13, POA_MEASURE14
1274 from
1275 (select ' || p_view_by_col || ',
1276 ' || p_view_by_col || ' VIEWBY,';
1277
1278 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1279 l_sel_clause := l_sel_clause || ' base_uom, decode(base_uom,null,to_number(null),nvl(c_quantity,0)) POA_MEASURE12, ';
1280 end if;
1281
1282 l_sel_clause := l_sel_clause || '
1283 nvl(c_purchase_amt,0) POA_MEASURE1,
1284 nvl(c_above_contract_amt, 0) + nvl(c_below_contract_amt, 0) POA_MEASURE2,
1288 nvl(c_purchase_amt_total,0) POA_MEASURE5,
1285 nvl(c_below_contract_amt,0) POA_MEASURE3,
1286 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1287 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE4,
1289 nvl(c_above_contract_amt,0) POA_MEASURE6,
1290 nvl(c_above_contract_amt_total,0) + nvl(c_below_contract_amt_total,0) POA_MEASURE7,
1291 nvl(c_below_contract_amt_total,0) POA_MEASURE8,
1292 nvl(c_above_contract_amt_total,0) POA_MEASURE9,
1293 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE10,
1294 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total'),poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total'),'P') || ' POA_MEASURE11,
1295 nvl(c_p_contract_amt,0) POA_MEASURE13,
1296 nvl(c_p_contract_amt_total,0) POA_MEASURE14';
1297 return l_sel_clause;
1298 END;
1299
1300
1301 PROCEDURE pop_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1302 x_custom_sql OUT NOCOPY VARCHAR2,
1303 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1304 IS
1305 l_query varchar2(10000);
1306 l_view_by varchar2(120);
1307 l_view_by_col varchar2(120);
1308 l_as_of_date date;
1309 l_prev_as_of_date date;
1310 l_xtd varchar2(10);
1311 l_comparison_type varchar2(1) := 'Y';
1312 l_nested_pattern number;
1313 l_cur_suffix varchar2(2);
1314 l_custom_sql varchar2(4000);
1315 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1316 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1317 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1318 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1319 l_mv VARCHAR2(30);
1320 l_mv2 VARCHAR2(30);
1321 l_where_clause VARCHAR2(2000);
1322 l_where_clause2 VARCHAR2(2000);
1323 l_view_by_value VARCHAR2(100);
1324 l_sec_context varchar2(10);
1325 l_use_only_agg_mv varchar2(1);
1326 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1327 BEGIN
1328 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1329 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1330
1331 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1332 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1333 poa_dbi_sutil_pkg.process_parameters(
1334 p_param => p_param,
1335 p_view_by => l_view_by,
1336 p_view_by_col_name => l_view_by_col,
1337 p_view_by_value => l_view_by_value,
1338 p_comparison_type => l_comparison_type,
1339 p_xtd => l_xtd,
1340 p_as_of_date => l_as_of_date,
1341 p_prev_as_of_date => l_prev_as_of_date,
1342 p_cur_suffix => l_cur_suffix,
1343 p_nested_pattern => l_nested_pattern,
1344 p_where_clause => l_where_clause,
1345 p_mv => l_mv,
1346 p_join_tbl => l_join_tbl,
1347 p_in_join_tbl => l_in_join_tbl,
1348 x_custom_output => x_custom_output,
1349 p_trend => 'Y',
1350 p_func_area => 'PO',
1351 p_version => '6.0',
1352 p_role => 'COM',
1353 p_mv_set => 'PODCUT');
1354
1355 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1356
1357 l_query := get_pop_trend_sel_clause || ' from
1358 '|| poa_dbi_template_pkg.trend_sql(
1359 p_xtd => l_xtd,
1360 p_comparison_type => l_comparison_type,
1361 p_fact_name => l_mv,
1362 p_where_clause => l_where_clause,
1363 p_col_name => l_col_tbl,
1364 p_use_grpid => 'N',
1365 p_in_join_tables => l_in_join_tbl);
1366
1367 elsif(l_sec_context = 'COMP')then
1368 poa_dbi_sutil_pkg.process_parameters(
1369 p_param => p_param,
1370 p_view_by => l_view_by,
1371 p_view_by_col_name => l_view_by_col,
1372 p_view_by_value => l_view_by_value,
1373 p_comparison_type => l_comparison_type,
1374 p_xtd => l_xtd,
1375 p_as_of_date => l_as_of_date,
1376 p_prev_as_of_date => l_prev_as_of_date,
1377 p_cur_suffix => l_cur_suffix,
1378 p_nested_pattern => l_nested_pattern,
1379 p_where_clause => l_where_clause,
1380 p_mv => l_mv,
1381 p_join_tbl => l_join_tbl,
1382 p_in_join_tbl => l_in_join_tbl,
1383 x_custom_output => x_custom_output,
1384 p_trend => 'Y',
1385 p_func_area => 'PO',
1386 p_version => '8.0',
1387 p_role => 'COM',
1388 p_mv_set => 'PODCUTA');
1389
1390 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1391
1392 /*check if we can get everything from aggregated mv*/
1393 l_use_only_agg_mv := 'Y';
1394 for i in 1..l_in_join_tbl.count loop
1395 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
1396 if(l_in_join_tbl(i).aggregated_flag = 'N')then
1397 l_use_only_agg_mv := 'N';
1398 end if;
1399 end if;
1400 end loop;
1401
1402 if(l_use_only_agg_mv = 'N') then
1403 poa_dbi_sutil_pkg.process_parameters(
1404 p_param => p_param,
1405 p_view_by => l_view_by,
1406 p_view_by_col_name => l_view_by_col,
1410 p_as_of_date => l_as_of_date,
1407 p_view_by_value => l_view_by_value,
1408 p_comparison_type => l_comparison_type,
1409 p_xtd => l_xtd,
1411 p_prev_as_of_date => l_prev_as_of_date,
1412 p_cur_suffix => l_cur_suffix,
1413 p_nested_pattern => l_nested_pattern,
1414 p_where_clause => l_where_clause2,
1415 p_mv => l_mv2,
1416 p_join_tbl => l_join_tbl,
1417 p_in_join_tbl => l_in_join_tbl2,
1418 x_custom_output => x_custom_output,
1419 p_trend => 'Y',
1420 p_func_area => 'PO',
1421 p_version => '8.0',
1422 p_role => 'COM',
1423 p_mv_set => 'PODCUTB');
1424
1425 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1426 l_mv_tbl.extend;
1427 l_mv_tbl(1).mv_name := l_mv;
1428 l_mv_tbl(1).mv_col := l_col_tbl;
1429 l_mv_tbl(1).mv_where := l_where_clause;
1430 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
1431 l_mv_tbl(1).use_grp_id := 'N';
1432 l_mv_tbl(1).mv_xtd := l_xtd;
1433
1434 l_mv_tbl.extend;
1435 l_mv_tbl(2).mv_name := l_mv2;
1436 l_mv_tbl(2).mv_col := l_col_tbl;
1437 l_mv_tbl(2).mv_where := l_where_clause2;
1438 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
1439 l_mv_tbl(2).use_grp_id := 'N';
1440 l_mv_tbl(2).mv_xtd := l_xtd;
1441
1442 l_query := get_pop_trend_sel_clause('union') || ' from
1443 '|| poa_dbi_template_pkg.union_all_trend_sql(
1444 p_mv => l_mv_tbl
1445 ,p_comparison_type => l_comparison_type
1446 ,p_diff_measures => 'N');
1447
1448 else
1449 l_query := get_pop_trend_sel_clause || ' from
1450 '|| poa_dbi_template_pkg.trend_sql(
1451 p_xtd => l_xtd,
1452 p_comparison_type => l_comparison_type,
1453 p_fact_name => l_mv,
1454 p_where_clause => l_where_clause,
1455 p_col_name => l_col_tbl,
1456 p_use_grpid => 'N',
1457 p_in_join_tables => l_in_join_tbl);
1458 end if; /* l_use_only_agg_mv = 'N' */
1459 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
1460 x_custom_sql := l_query;
1461 end;
1462
1463
1464 function get_pop_trend_sel_clause(p_type in varchar2 := 'trend' ) return varchar2
1465 is
1466 l_sel_clause varchar2(4000);
1467 begin
1468 if (p_type = 'trend') then
1469 l_sel_clause := 'select cal.name VIEWBY,'||fnd_global.newline;
1470 else
1471 l_sel_clause := 'select cal_name VIEWBY,'||fnd_global.newline;
1472 end if;
1473 l_sel_clause := l_sel_clause ||
1474 'nvl(c_purchase_amt,0) POA_MEASURE1,
1475 p_purchase_amt POA_MEASURE2,
1476 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1';
1477
1478 return l_sel_clause;
1479 end;
1480
1481 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1482 x_custom_sql OUT NOCOPY VARCHAR2,
1483 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1484 IS
1485 l_query varchar2(10000);
1486 l_view_by varchar2(120);
1487 l_view_by_col varchar2(120);
1488 l_as_of_date date;
1489 l_prev_as_of_date date;
1490 l_xtd varchar2(10);
1491 l_comparison_type varchar2(1) := 'Y';
1492 l_nested_pattern number;
1493 l_cur_suffix varchar2(2);
1494 l_custom_sql varchar2(10000);
1495 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1496 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1497 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1498 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1499 l_mv VARCHAR2(30);
1500 l_mv2 VARCHAR2(30);
1501 l_where_clause VARCHAR2(2000);
1502 l_where_clause2 VARCHAR2(2000);
1503 l_view_by_value VARCHAR2(100);
1504 l_sec_context varchar2(10);
1505 l_use_only_agg_mv varchar2(1);
1506 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1507 BEGIN
1508 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1509 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1510
1511 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1512 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1513 poa_dbi_sutil_pkg.process_parameters(
1514 p_param => p_param,
1515 p_view_by => l_view_by,
1516 p_view_by_col_name => l_view_by_col,
1517 p_view_by_value => l_view_by_value,
1518 p_comparison_type => l_comparison_type,
1519 p_xtd => l_xtd,
1520 p_as_of_date => l_as_of_date,
1521 p_prev_as_of_date => l_prev_as_of_date,
1522 p_cur_suffix => l_cur_suffix,
1523 p_nested_pattern => l_nested_pattern,
1524 p_where_clause => l_where_clause,
1525 p_mv => l_mv,
1526 p_join_tbl => l_join_tbl,
1527 p_in_join_tbl => l_in_join_tbl,
1528 x_custom_output => x_custom_output,
1529 p_trend => 'Y',
1530 p_func_area => 'PO',
1531 p_version => '6.0',
1532 p_role => 'COM',
1533 p_mv_set => 'PODCUT');
1534
1535 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1536 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1537 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1541 p_xtd => l_xtd,
1538
1539 l_query := get_trend_sel_clause || ' from
1540 '|| poa_dbi_template_pkg.trend_sql(
1542 p_comparison_type => l_comparison_type,
1543 p_fact_name => l_mv,
1544 p_where_clause => l_where_clause,
1545 p_col_name => l_col_tbl,
1546 p_use_grpid => 'N',
1547 p_in_join_tables => l_in_join_tbl);
1548
1549 elsif(l_sec_context = 'COMP')then
1550 poa_dbi_sutil_pkg.process_parameters(
1551 p_param => p_param,
1552 p_view_by => l_view_by,
1553 p_view_by_col_name => l_view_by_col,
1554 p_view_by_value => l_view_by_value,
1555 p_comparison_type => l_comparison_type,
1556 p_xtd => l_xtd,
1557 p_as_of_date => l_as_of_date,
1558 p_prev_as_of_date => l_prev_as_of_date,
1559 p_cur_suffix => l_cur_suffix,
1560 p_nested_pattern => l_nested_pattern,
1561 p_where_clause => l_where_clause,
1562 p_mv => l_mv,
1563 p_join_tbl => l_join_tbl,
1564 p_in_join_tbl => l_in_join_tbl,
1565 x_custom_output => x_custom_output,
1566 p_trend => 'Y',
1567 p_func_area => 'PO',
1568 p_version => '8.0',
1569 p_role => 'COM',
1570 p_mv_set => 'PODCUTA');
1571
1572 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
1573 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt', 'N');
1574 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
1575 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
1576
1577 /*check if we can get everything from aggregated mv*/
1578 l_use_only_agg_mv := 'Y';
1579 for i in 1..l_in_join_tbl.count loop
1580 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
1581 if(l_in_join_tbl(i).aggregated_flag = 'N')then
1582 l_use_only_agg_mv := 'N';
1583 end if;
1584 end if;
1585 end loop;
1586
1587 if(l_use_only_agg_mv = 'N') then
1588 poa_dbi_sutil_pkg.process_parameters(
1589 p_param => p_param,
1590 p_view_by => l_view_by,
1591 p_view_by_col_name => l_view_by_col,
1592 p_view_by_value => l_view_by_value,
1593 p_comparison_type => l_comparison_type,
1594 p_xtd => l_xtd,
1595 p_as_of_date => l_as_of_date,
1596 p_prev_as_of_date => l_prev_as_of_date,
1597 p_cur_suffix => l_cur_suffix,
1598 p_nested_pattern => l_nested_pattern,
1599 p_where_clause => l_where_clause2,
1600 p_mv => l_mv2,
1601 p_join_tbl => l_join_tbl,
1602 p_in_join_tbl => l_in_join_tbl2,
1603 x_custom_output => x_custom_output,
1604 p_trend => 'Y',
1605 p_func_area => 'PO',
1606 p_version => '8.0',
1607 p_role => 'COM',
1608 p_mv_set => 'PODCUTB');
1609
1610 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1611 l_mv_tbl.extend;
1612 l_mv_tbl(1).mv_name := l_mv;
1613 l_mv_tbl(1).mv_col := l_col_tbl;
1614 l_mv_tbl(1).mv_where := l_where_clause;
1615 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
1616 l_mv_tbl(1).use_grp_id := 'N';
1617 l_mv_tbl(1).mv_xtd := l_xtd;
1618
1619 l_mv_tbl.extend;
1620 l_mv_tbl(2).mv_name := l_mv2;
1621 l_mv_tbl(2).mv_col := l_col_tbl;
1622 l_mv_tbl(2).mv_where := l_where_clause2;
1623 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
1624 l_mv_tbl(2).use_grp_id := 'N';
1625 l_mv_tbl(2).mv_xtd := l_xtd;
1626
1627 l_query := get_trend_sel_clause('union') || ' from
1628 '|| poa_dbi_template_pkg.union_all_trend_sql(
1629 p_mv => l_mv_tbl,
1630 p_comparison_type => l_comparison_type,
1631 p_diff_measures => 'N');
1632 else
1633 l_query := get_trend_sel_clause || ' from
1634 '|| poa_dbi_template_pkg.trend_sql(
1635 p_xtd => l_xtd,
1636 p_comparison_type => l_comparison_type,
1637 p_fact_name => l_mv,
1638 p_where_clause => l_where_clause,
1639 p_col_name => l_col_tbl,
1640 p_use_grpid => 'N',
1641 p_in_join_tables => l_in_join_tbl);
1642 end if; /* l_use_only_agg_mv = 'N' */
1643 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
1644 x_custom_sql := l_query;
1645 end;
1646
1647 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2
1648 IS
1649
1650 l_sel_clause varchar2(4000);
1651
1652 BEGIN
1653 if (p_type = 'trend') then
1654 l_sel_clause := 'select cal.name VIEWBY,';
1655 else
1656 l_sel_clause := 'select cal_name VIEWBY,';
1657 end if;
1658
1659 l_sel_clause := l_sel_clause || '
1660 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1661 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE2,
1662 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1666 return l_sel_clause;
1663 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt'),poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt'),'P') || ' POA_MEASURE3,
1664 null POA_PERCENT3, -- (Obsoleted)Contract Leakage Rate
1665 null POA_MEASURE4 -- (Obsoleted)Change '||fnd_global.newline;
1667 END;
1668
1669
1670 PROCEDURE kpi_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1671 x_custom_sql OUT NOCOPY VARCHAR2,
1672 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1673 IS
1674 l_query varchar2(10000);
1675 l_view_by varchar2(120);
1676 l_view_by_col varchar2(120);
1677 l_as_of_date date;
1678 l_prev_as_of_date date;
1679 l_xtd varchar2(10);
1680 l_comparison_type varchar2(1) := 'Y';
1681 l_nested_pattern number;
1682 l_cur_suffix varchar2(2);
1683 l_custom_sql varchar2(10000);
1684 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1685 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1686 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1687 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1688 l_where_clause varchar2(2000);
1689 l_where_clause2 varchar2(2000);
1690 l_mv varchar2(30);
1691 l_mv2 varchar2(30);
1692 l_org_where varchar2(500);
1693 l_commodity_where varchar2(500);
1694 l_view_by_value varchar2(100);
1695 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1696 l_sec_context varchar2(10);
1697 l_use_only_agg_mv varchar2(1);
1698 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1699 BEGIN
1700 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1701 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1702
1703 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1704 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1705 poa_dbi_sutil_pkg.process_parameters(
1706 p_param => p_param,
1707 p_view_by => l_view_by,
1708 p_view_by_col_name => l_view_by_col,
1709 p_view_by_value => l_view_by_value,
1710 p_comparison_type => l_comparison_type,
1711 p_xtd => l_xtd,
1712 p_as_of_date => l_as_of_date,
1713 p_prev_as_of_date => l_prev_as_of_date,
1714 p_cur_suffix => l_cur_suffix,
1715 p_nested_pattern => l_nested_pattern,
1716 p_where_clause => l_where_clause,
1717 p_mv => l_mv,
1718 p_join_tbl => l_join_tbl,
1719 p_in_join_tbl => l_in_join_tbl,
1720 x_custom_output => x_custom_output,
1721 p_trend => 'N',
1722 p_func_area => 'PO',
1723 p_version => '6.0',
1724 p_role => 'COM',
1725 p_mv_set => 'PODCUT');
1726
1727 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1728 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1729 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1730 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1731
1732 l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
1733
1734 l_query := l_query || '
1735 oset.POA_PERCENT1 POA_PERCENT1,
1736 oset.POA_PERCENT2 POA_PERCENT2,
1737 oset.POA_PERCENT3 POA_PERCENT3,
1738 oset.POA_MEASURE1 POA_MEASURE1,
1739 oset.POA_MEASURE2 POA_MEASURE2,
1740 oset.POA_MEASURE3 POA_MEASURE3,
1741 oset.POA_MEASURE4 POA_MEASURE4,
1742 oset.POA_MEASURE5 POA_MEASURE5,
1743 oset.POA_MEASURE6 POA_MEASURE6,
1744 oset.POA_MEASURE7 POA_MEASURE7,
1745 oset.POA_MEASURE8 POA_MEASURE8,
1746 oset.POA_MEASURE9 POA_MEASURE9
1747 from
1748 (select * from (select ' || l_view_by_col || ',
1749 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1750 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1751 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1752 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1753 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1754 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1755 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1756 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1757 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1758 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1759 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1760 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1761 from
1762 ' || poa_dbi_template_pkg.status_sql(
1763 p_fact_name => l_mv,
1764 p_where_clause => l_where_clause,
1765 p_join_tables => l_join_tbl,
1766 p_use_windowing => 'N',
1767 p_col_name => l_col_tbl,
1768 p_use_grpid => 'N',
1769 p_in_join_tables => l_in_join_tbl);
1770
1771 elsif(l_sec_context = 'COMP')then
1772 poa_dbi_sutil_pkg.process_parameters(
1773 p_param => p_param,
1774 p_view_by => l_view_by,
1775 p_view_by_col_name => l_view_by_col,
1776 p_view_by_value => l_view_by_value,
1777 p_comparison_type => l_comparison_type,
1781 p_cur_suffix => l_cur_suffix,
1778 p_xtd => l_xtd,
1779 p_as_of_date => l_as_of_date,
1780 p_prev_as_of_date => l_prev_as_of_date,
1782 p_nested_pattern => l_nested_pattern,
1783 p_where_clause => l_where_clause,
1784 p_mv => l_mv,
1785 p_join_tbl => l_join_tbl,
1786 p_in_join_tbl => l_in_join_tbl,
1787 x_custom_output => x_custom_output,
1788 p_trend => 'N',
1789 p_func_area => 'PO',
1790 p_version => '8.0',
1791 p_role => 'COM',
1792 p_mv_set => 'PODCUTA');
1793
1794 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
1795 poa_dbi_util_pkg.add_column(l_col_tbl, 'contract_amt_' || l_cur_suffix, 'contract_amt');
1796 poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
1797 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
1798
1799 /*check if we can get everything from aggregated mv*/
1800 l_use_only_agg_mv := 'Y';
1801 for i in 1..l_in_join_tbl.count loop
1802 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
1803 if(l_in_join_tbl(i).aggregated_flag = 'N')then
1804 l_use_only_agg_mv := 'N';
1805 end if;
1806 end if;
1807 end loop;
1808
1809 if(l_use_only_agg_mv = 'N') then
1810 poa_dbi_sutil_pkg.process_parameters(
1811 p_param => p_param,
1812 p_view_by => l_view_by,
1813 p_view_by_col_name => l_view_by_col,
1814 p_view_by_value => l_view_by_value,
1815 p_comparison_type => l_comparison_type,
1816 p_xtd => l_xtd,
1817 p_as_of_date => l_as_of_date,
1818 p_prev_as_of_date => l_prev_as_of_date,
1819 p_cur_suffix => l_cur_suffix,
1820 p_nested_pattern => l_nested_pattern,
1821 p_where_clause => l_where_clause2,
1822 p_mv => l_mv2,
1823 p_join_tbl => l_join_tbl,
1824 p_in_join_tbl => l_in_join_tbl2,
1825 x_custom_output => x_custom_output,
1826 p_trend => 'N',
1827 p_func_area => 'PO',
1828 p_version => '8.0',
1829 p_role => 'COM',
1830 p_mv_set => 'PODCUTB');
1831
1832 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
1833 l_mv_tbl.extend;
1834 l_mv_tbl(1).mv_name := l_mv;
1835 l_mv_tbl(1).mv_col := l_col_tbl;
1836 l_mv_tbl(1).mv_where := l_where_clause;
1837 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
1838 l_mv_tbl(1).use_grp_id := 'N';
1839
1840 l_mv_tbl.extend;
1841 l_mv_tbl(2).mv_name := l_mv2;
1842 l_mv_tbl(2).mv_col := l_col_tbl;
1843 l_mv_tbl(2).mv_where := l_where_clause2;
1844 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
1845 l_mv_tbl(2).use_grp_id := 'N';
1846
1847 l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
1848
1849 l_query := l_query || '
1850 oset.POA_PERCENT1 POA_PERCENT1,
1851 oset.POA_PERCENT2 POA_PERCENT2,
1852 oset.POA_PERCENT3 POA_PERCENT3,
1853 oset.POA_MEASURE1 POA_MEASURE1,
1854 oset.POA_MEASURE2 POA_MEASURE2,
1855 oset.POA_MEASURE3 POA_MEASURE3,
1856 oset.POA_MEASURE4 POA_MEASURE4,
1857 oset.POA_MEASURE5 POA_MEASURE5,
1858 oset.POA_MEASURE6 POA_MEASURE6,
1859 oset.POA_MEASURE7 POA_MEASURE7,
1860 oset.POA_MEASURE8 POA_MEASURE8,
1861 oset.POA_MEASURE9 POA_MEASURE9
1862 from
1863 (select * from (select company_id,
1864 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1865 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1866 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1867 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1868 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1869 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1870 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1871 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1872 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1873 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1874 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1875 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1876 from (
1877 ' || poa_dbi_template_pkg.union_all_status_sql(
1878 p_mv => l_mv_tbl,
1879 p_join_tables => l_join_tbl,
1880 p_use_windowing => 'N',
1881 p_paren_count => 3,
1882 p_generate_viewby => 'Y',
1883 p_diff_measures => 'N');
1884 else
1885 l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
1886
1887 l_query := l_query || '
1888 oset.POA_PERCENT1 POA_PERCENT1,
1889 oset.POA_PERCENT2 POA_PERCENT2,
1890 oset.POA_PERCENT3 POA_PERCENT3,
1891 oset.POA_MEASURE1 POA_MEASURE1,
1895 oset.POA_MEASURE5 POA_MEASURE5,
1892 oset.POA_MEASURE2 POA_MEASURE2,
1893 oset.POA_MEASURE3 POA_MEASURE3,
1894 oset.POA_MEASURE4 POA_MEASURE4,
1896 oset.POA_MEASURE6 POA_MEASURE6,
1897 oset.POA_MEASURE7 POA_MEASURE7,
1898 oset.POA_MEASURE8 POA_MEASURE8,
1899 oset.POA_MEASURE9 POA_MEASURE9
1900 from
1901 (select * from (select company_id,
1902 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
1903 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
1904 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
1905 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
1906 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
1907 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
1908 ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
1909 ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
1910 ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
1911 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
1912 ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
1913 ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
1914 from
1915 ' || poa_dbi_template_pkg.status_sql(
1916 p_fact_name => l_mv,
1917 p_where_clause => l_where_clause,
1918 p_join_tables => l_join_tbl,
1919 p_use_windowing => 'N',
1920 p_col_name => l_col_tbl,
1921 p_use_grpid => 'N',
1922 p_in_join_tables => l_in_join_tbl);
1923 end if; /* l_use_only_agg_mv = 'N' */
1924 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
1925 x_custom_sql := l_query;
1926 end;
1927
1928
1929 PROCEDURE pie_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1930 x_custom_sql OUT NOCOPY VARCHAR2,
1931 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1932 IS
1933 l_query varchar2(10000);
1934 l_view_by varchar2(120);
1935 l_as_of_date date;
1936 l_prev_as_of_date date;
1937 l_xtd varchar2(10);
1938 l_comparison_type varchar2(1) := 'Y';
1939 l_nested_pattern number;
1940 l_cur_suffix varchar2(2);
1941 l_custom_sql varchar2(10000);
1942 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1943 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1944 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1945 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1946 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1947 l_where_clause VARCHAR2(2000);
1948 l_where_clause2 VARCHAR2(2000);
1949 l_mv VARCHAR2(30);
1950 l_mv2 VARCHAR2(30);
1951 l_view_by_col VARCHAR2(30);
1952 l_view_by_value VARCHAR2(100);
1953 l_in_join_tables VARCHAR2(1000) := '';
1954 l_in_join_tables2 VARCHAR2(1000) := '';
1955 l_sec_context varchar2(10);
1956 l_use_only_agg_mv varchar2(1);
1957 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1958 BEGIN
1959 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1960 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1961
1962 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1963 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1964 poa_dbi_sutil_pkg.process_parameters(
1965 p_param => p_param,
1966 p_view_by => l_view_by,
1967 p_view_by_col_name => l_view_by_col,
1968 p_view_by_value => l_view_by_value,
1969 p_comparison_type => l_comparison_type,
1970 p_xtd => l_xtd,
1971 p_as_of_date => l_as_of_date,
1972 p_prev_as_of_date => l_prev_as_of_date,
1973 p_cur_suffix => l_cur_suffix,
1974 p_nested_pattern => l_nested_pattern,
1975 p_where_clause => l_where_clause,
1976 p_mv => l_mv,
1977 p_join_tbl => l_join_tbl,
1978 p_in_join_tbl => l_in_join_tbl,
1979 x_custom_output => x_custom_output,
1980 p_trend => 'N',
1981 p_func_area => 'PO',
1982 p_version => '6.0',
1983 p_role => 'COM',
1984 p_mv_set => 'PODCUT');
1985
1986 if(l_in_join_tbl is not null) then
1987 for i in 1 .. l_in_join_tbl.count loop
1988 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
1989 end loop;
1990 end if;
1991
1992 l_query := 'select description VIEWBY,
1993 nvl(c_con_type_amt_total,0) POA_MEASURE1,
1994 c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
1995 (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
1996 p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
1997 nvl(c_purchase_amt_total,0) POA_MEASURE3,
1998 c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
1999 from
2000 ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
2001 decode(lookup_code, ''1'', c_contract_amt_total, c_n_contract_amt_total) c_con_type_amt_total,
2002 decode(lookup_code, ''1'', p_contract_amt_total, p_n_contract_amt_total) p_con_type_amt_total
2003 from
2004 ( select fl.lookup_code,
2008 c_contract_amt_total,
2005 fl.meaning description,
2006 c_n_contract_amt_total,
2007 p_n_contract_amt_total,
2009 p_contract_amt_total,
2010 c_purchase_amt_total,
2011 p_purchase_amt_total
2012 from
2013 ( select
2014 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
2015 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
2016 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
2017 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
2018 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
2019 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
2020 from ' || l_mv ||' fact,
2021 fii_time_rpt_struct_v cal
2022 ' || l_in_join_tables || '
2023 where
2024 fact.time_id = cal.time_id '
2025 || l_where_clause ||
2026 'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
2027 and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
2028 ) oset,
2029 fnd_lookups fl
2030 where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
2031 and fl.enabled_flag = ''Y''
2032 )
2033 )';
2034 elsif(l_sec_context = 'COMP')then
2035 poa_dbi_sutil_pkg.process_parameters(
2036 p_param => p_param,
2037 p_view_by => l_view_by,
2038 p_view_by_col_name => l_view_by_col,
2039 p_view_by_value => l_view_by_value,
2040 p_comparison_type => l_comparison_type,
2041 p_xtd => l_xtd,
2042 p_as_of_date => l_as_of_date,
2043 p_prev_as_of_date => l_prev_as_of_date,
2044 p_cur_suffix => l_cur_suffix,
2045 p_nested_pattern => l_nested_pattern,
2046 p_where_clause => l_where_clause,
2047 p_mv => l_mv,
2048 p_join_tbl => l_join_tbl,
2049 p_in_join_tbl => l_in_join_tbl,
2050 x_custom_output => x_custom_output,
2051 p_trend => 'N',
2052 p_func_area => 'PO',
2053 p_version => '8.0',
2054 p_role => 'COM',
2055 p_mv_set => 'PODCUTA');
2056
2057 if(l_in_join_tbl is not null) then
2058 for i in 1 .. l_in_join_tbl.count loop
2059 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
2060 end loop;
2061 end if;
2062
2063 /*check if we can get everything from aggregated mv*/
2064 l_use_only_agg_mv := 'Y';
2065 for i in 1..l_in_join_tbl.count loop
2066 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
2067 if(l_in_join_tbl(i).aggregated_flag = 'N')then
2068 l_use_only_agg_mv := 'N';
2069 end if;
2070 end if;
2071 end loop;
2072
2073 if(l_use_only_agg_mv = 'N') then
2074 poa_dbi_sutil_pkg.process_parameters(
2075 p_param => p_param,
2076 p_view_by => l_view_by,
2077 p_view_by_col_name => l_view_by_col,
2078 p_view_by_value => l_view_by_value,
2079 p_comparison_type => l_comparison_type,
2080 p_xtd => l_xtd,
2081 p_as_of_date => l_as_of_date,
2082 p_prev_as_of_date => l_prev_as_of_date,
2083 p_cur_suffix => l_cur_suffix,
2084 p_nested_pattern => l_nested_pattern,
2085 p_where_clause => l_where_clause2,
2086 p_mv => l_mv2,
2087 p_join_tbl => l_join_tbl,
2088 p_in_join_tbl => l_in_join_tbl2,
2089 x_custom_output => x_custom_output,
2090 p_trend => 'N',
2091 p_func_area => 'PO',
2092 p_version => '8.0',
2093 p_role => 'COM',
2094 p_mv_set => 'PODCUTB');
2095
2096 if(l_in_join_tbl2 is not null) then
2097 for i in 1 .. l_in_join_tbl2.count loop
2098 l_in_join_tables2 := l_in_join_tables2 || ', ' || l_in_join_tbl2(i).table_name || ' ' || l_in_join_tbl2(i).table_alias;
2099 end loop;
2100 end if;
2101
2102 l_query := 'select description VIEWBY,
2103 nvl(c_con_type_amt_total,0) POA_MEASURE1,
2104 c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
2105 (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
2106 p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
2107 nvl(c_purchase_amt_total,0) POA_MEASURE3,
2108 c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
2109 from
2110 ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
2111 decode(lookup_code, ''1'', c_contract_amt_total, ''2'', c_n_contract_amt_total, c_p_contract_amt_total) c_con_type_amt_total,
2112 decode(lookup_code, ''1'', p_contract_amt_total,''2'', p_n_contract_amt_total, p_p_contract_amt_total) p_con_type_amt_total
2113 from
2114 ( select fl.lookup_code,
2115 fl.meaning description,
2119 sum(p_contract_amt_total) p_contract_amt_total,
2116 sum(c_n_contract_amt_total) c_n_contract_amt_total,
2117 sum(p_n_contract_amt_total) p_n_contract_amt_total,
2118 sum(c_contract_amt_total) c_contract_amt_total,
2120 sum(c_p_contract_amt_total) c_p_contract_amt_total,
2121 sum(p_p_contract_amt_total) p_p_contract_amt_total,
2122 sum(c_purchase_amt_total) c_purchase_amt_total,
2123 sum(p_purchase_amt_total) p_purchase_amt_total
2124 from
2125 (
2126 ( select
2127 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
2128 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
2129 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
2130 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
2131 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
2132 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
2133 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
2134 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
2135 from ' || l_mv ||' fact,
2136 fii_time_rpt_struct_v cal
2137 ' || l_in_join_tables || '
2138 where
2139 fact.time_id = cal.time_id '
2140 || l_where_clause ||
2141 'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
2142 and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
2143 )
2144 union all
2145 ( select
2146 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
2147 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
2148 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
2149 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
2150 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
2151 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
2152 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
2153 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
2154 from ' || l_mv2 ||' fact,
2155 fii_time_rpt_struct_v cal
2156 ' || l_in_join_tables2 || '
2157 where
2158 fact.time_id = cal.time_id '
2159 || l_where_clause2 ||
2160 'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
2161 and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
2162 )
2163 ) oset,
2164 fnd_lookups fl
2165 where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
2166 and fl.enabled_flag = ''Y''
2167 group by fl.lookup_code, fl.meaning
2168 )
2169 )';
2170 else
2171 l_query := 'select description VIEWBY,
2172 nvl(c_con_type_amt_total,0) POA_MEASURE1,
2173 c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
2174 (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
2175 p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
2176 nvl(c_purchase_amt_total,0) POA_MEASURE3,
2177 c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
2178 from
2179 ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
2180 decode(lookup_code, ''1'', c_contract_amt_total, ''2'', c_n_contract_amt_total, c_p_contract_amt_total) c_con_type_amt_total,
2181 decode(lookup_code, ''1'', p_contract_amt_total,''2'', p_n_contract_amt_total, p_p_contract_amt_total) p_con_type_amt_total
2182 from
2183 ( select fl.lookup_code,
2184 fl.meaning description,
2185 c_n_contract_amt_total,
2186 p_n_contract_amt_total,
2187 c_contract_amt_total,
2188 p_contract_amt_total,
2189 c_p_contract_amt_total,
2190 p_p_contract_amt_total,
2191 c_purchase_amt_total,
2192 p_purchase_amt_total
2193 from
2194 ( select
2195 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
2196 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
2197 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
2198 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
2202 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
2199 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
2200 sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
2201 sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
2203 from ' || l_mv ||' fact,
2204 fii_time_rpt_struct_v cal
2205 ' || l_in_join_tables || '
2206 where
2207 fact.time_id = cal.time_id '
2208 || l_where_clause ||
2209 'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
2210 and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
2211 ) oset,
2212 fnd_lookups fl
2213 where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
2214 and fl.enabled_flag = ''Y''
2215 )
2216 )';
2217 end if; /* l_use_only_agg_mv = 'N' */
2218 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
2219 x_custom_sql := l_query;
2220 end;
2221
2222
2223 FUNCTION get_doctype_filter_where return VARCHAR2
2224 IS
2225 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
2226 BEGIN
2227 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
2228 l_col_tbl.extend;
2229 l_col_tbl(1) := 'c_purchase_amt_total';
2230 l_col_tbl.extend;
2231 l_col_tbl(2) := 'c_purchase_amt';
2232
2233
2234 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
2235
2236 END;
2237
2238
2239 PROCEDURE doctype_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
2240 x_custom_sql OUT NOCOPY VARCHAR2,
2241 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
2242 IS
2243 l_query varchar2(10000);
2244 l_view_by varchar2(120);
2245 l_view_by_col varchar2(120);
2246 l_as_of_date date;
2247 l_prev_as_of_date date;
2248 l_xtd varchar2(10);
2249 l_comparison_type varchar2(1) := 'Y';
2250 l_nested_pattern number;
2251 l_cur_suffix varchar2(2);
2252 l_custom_sql varchar2(10000);
2253 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
2254 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
2255 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2256 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
2257 l_view_by_value VARCHAR2(100);
2258 l_where_clause VARCHAR2(2000);
2259 l_where_clause2 VARCHAR2(2000);
2260 l_mv VARCHAR2(30);
2261 l_mv2 VARCHAR2(30);
2262 l_sec_context varchar2(10);
2263 l_use_only_agg_mv varchar2(1);
2264 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
2265 BEGIN
2266 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
2267 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
2268
2269 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
2270 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
2271 poa_dbi_sutil_pkg.process_parameters(
2272 p_param => p_param,
2273 p_view_by => l_view_by,
2274 p_view_by_col_name => l_view_by_col,
2275 p_view_by_value => l_view_by_value,
2276 p_comparison_type => l_comparison_type,
2277 p_xtd => l_xtd,
2278 p_as_of_date => l_as_of_date,
2279 p_prev_as_of_date => l_prev_as_of_date,
2280 p_cur_suffix => l_cur_suffix,
2281 p_nested_pattern => l_nested_pattern,
2282 p_where_clause => l_where_clause,
2283 p_mv => l_mv,
2284 p_join_tbl => l_join_tbl,
2285 p_in_join_tbl => l_in_join_tbl ,
2286 x_custom_output => x_custom_output,
2287 p_trend => 'N',
2288 p_func_area => 'PO',
2289 p_version => '6.0',
2290 p_role => 'COM',
2291 p_mv_set => 'PODCUT');
2292
2293 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
2294
2295 l_query := get_doctype_sel_clause(l_view_by,l_view_by_col) || ' from ' ||
2296 poa_dbi_template_pkg.status_sql(
2297 p_fact_name => l_mv,
2298 p_where_clause => l_where_clause || ' and (contract_type is not null) ',
2299 p_join_tables => l_join_tbl,
2300 p_use_windowing => 'N',
2301 p_col_name => l_col_tbl,
2302 p_use_grpid => 'N',
2303 p_paren_count => 2,
2304 p_filter_where => get_doctype_filter_where,
2305 p_in_join_tables => l_in_join_tbl);
2306
2307 elsif(l_sec_context = 'COMP')then
2308 poa_dbi_sutil_pkg.process_parameters(
2309 p_param => p_param,
2310 p_view_by => l_view_by,
2311 p_view_by_col_name => l_view_by_col,
2312 p_view_by_value => l_view_by_value,
2313 p_comparison_type => l_comparison_type,
2314 p_xtd => l_xtd,
2315 p_as_of_date => l_as_of_date,
2316 p_prev_as_of_date => l_prev_as_of_date,
2317 p_cur_suffix => l_cur_suffix,
2318 p_nested_pattern => l_nested_pattern,
2319 p_where_clause => l_where_clause,
2320 p_mv => l_mv,
2321 p_join_tbl => l_join_tbl,
2322 p_in_join_tbl => l_in_join_tbl ,
2323 x_custom_output => x_custom_output,
2324 p_trend => 'N',
2328 p_mv_set => 'PODCUTA');
2325 p_func_area => 'PO',
2326 p_version => '8.0',
2327 p_role => 'COM',
2329
2330 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
2331
2332 /*check if we can get everything from aggregated mv*/
2333 l_use_only_agg_mv := 'Y';
2334 for i in 1..l_in_join_tbl.count loop
2335 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
2336 if(l_in_join_tbl(i).aggregated_flag = 'N')then
2337 l_use_only_agg_mv := 'N';
2338 end if;
2339 end if;
2340 end loop;
2341
2342 if(l_use_only_agg_mv = 'N') then
2343 poa_dbi_sutil_pkg.process_parameters(
2344 p_param => p_param,
2345 p_view_by => l_view_by,
2346 p_view_by_col_name => l_view_by_col,
2347 p_view_by_value => l_view_by_value,
2348 p_comparison_type => l_comparison_type,
2349 p_xtd => l_xtd,
2350 p_as_of_date => l_as_of_date,
2351 p_prev_as_of_date => l_prev_as_of_date,
2352 p_cur_suffix => l_cur_suffix,
2353 p_nested_pattern => l_nested_pattern,
2354 p_where_clause => l_where_clause2,
2355 p_mv => l_mv2,
2356 p_join_tbl => l_join_tbl,
2357 p_in_join_tbl => l_in_join_tbl2,
2358 x_custom_output => x_custom_output,
2359 p_trend => 'N',
2360 p_func_area => 'PO',
2361 p_version => '8.0',
2362 p_role => 'COM',
2363 p_mv_set => 'PODCUTB');
2364
2365 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
2366 l_mv_tbl.extend;
2367 l_mv_tbl(1).mv_name := l_mv;
2368 l_mv_tbl(1).mv_col := l_col_tbl;
2369 l_mv_tbl(1).mv_where := l_where_clause;
2370 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
2371 l_mv_tbl(1).use_grp_id := 'N';
2372
2373 l_mv_tbl.extend;
2374 l_mv_tbl(2).mv_name := l_mv2;
2375 l_mv_tbl(2).mv_col := l_col_tbl;
2376 l_mv_tbl(2).mv_where := l_where_clause2;
2377 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
2378 l_mv_tbl(2).use_grp_id := 'N';
2379
2380 l_query := get_doctype_sel_clause(l_view_by,l_view_by_col) || ' from (' ||
2381 poa_dbi_template_pkg.union_all_status_sql(
2382 p_mv => l_mv_tbl,
2383 p_join_tables => l_join_tbl,
2384 p_use_windowing => 'N',
2385 p_paren_count => 2,
2386 p_filter_where => get_doctype_filter_where,
2387 p_generate_viewby => 'Y',
2388 p_diff_measures => 'N');
2389 else
2390 l_query := get_doctype_sel_clause(l_view_by,l_view_by_col) || ' from ' ||
2391 poa_dbi_template_pkg.status_sql(
2392 p_fact_name => l_mv,
2393 p_where_clause => l_where_clause || ' and (contract_type is not null) ',
2394 p_join_tables => l_join_tbl,
2395 p_use_windowing => 'N',
2396 p_col_name => l_col_tbl,
2397 p_use_grpid => 'N',
2398 p_paren_count => 2,
2399 p_filter_where => get_doctype_filter_where,
2400 p_in_join_tables => l_in_join_tbl);
2401 end if;
2402 end if;
2403 x_custom_sql := l_query;
2404 end;
2405
2406 FUNCTION get_doctype_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
2407 IS
2408 l_sel_clause varchar2(4000);
2409 BEGIN
2410 l_sel_clause :=
2411 'select v.value VIEWBY, v.id VIEWBYID,
2412 oset.POA_MEASURE1 POA_MEASURE1,
2413 oset.POA_MEASURE1 POA_MEASURE3,
2414 oset.POA_PERCENT1 POA_PERCENT1,
2415 oset.POA_PERCENT2 POA_PERCENT2,
2416 oset.POA_MEASURE2 POA_MEASURE2,
2417 oset.POA_PERCENT3 POA_PERCENT3,
2418 ''' || 'pFunctionName=POA_DBI_CUD_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' || ''' POA_ATTRIBUTE1
2419 from
2420 (select ' || 'contract_type' || ',
2421 ' || 'contract_type' || ' VIEWBY,
2422 c_purchase_amt POA_MEASURE1, '
2423 || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
2424 '
2425 || poa_dbi_util_pkg.change_clause (
2426 poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
2427 poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,
2428 'P') || ' POA_PERCENT2,
2429 c_purchase_amt_total POA_MEASURE2,
2430 decode(c_purchase_amt_total, null, null, 100) POA_PERCENT3';
2431
2432 return l_sel_clause;
2433
2434 END;
2435
2436
2437 end poa_dbi_cut_pkg;