[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_INV_PKG
Source
1 PACKAGE BODY poa_dbi_inv_pkg
2 /* $Header: poadbiinvb.pls 120.5 2006/04/21 02:28:49 sdiwakar noship $ */
3 AS
4 -- -----------------------------------------------------------------------
5 -- |---------------------< get_status_sel_clause >-----------------------|
6 -- -----------------------------------------------------------------------
7 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2
8 ,p_view_by_col IN VARCHAR2
9 ,p_url IN VARCHAR2
10 ,p_to_date_type IN VARCHAR2
11 ,p_sec_context IN VARCHAR2) RETURN VARCHAR2;
12 -- -----------------------------------------------------------------------
13 -- |------------------------< get_trend_sel_clause >---------------------|
14 -- -----------------------------------------------------------------------
15 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') RETURN VARCHAR2;
16 FUNCTION get_status_filter_where return VARCHAR2;
17
18
19
20
21 -- -----------------------------------------------------------------------
22 -- |----------------------------< status_sql >---------------------------|
23 -- -----------------------------------------------------------------------
24 PROCEDURE status_sql(p_param IN BIS_PMV_PAGE_PARAMETER_TBL
25 ,x_custom_sql OUT NOCOPY VARCHAR2
26 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
27 IS
28 l_query VARCHAR2(32000);
29 l_view_by VARCHAR2(120);
30 l_view_by_col VARCHAR2(120);
31 l_as_of_date DATE;
32 l_prev_as_of_date DATE;
33 l_prev_prev_as_of_date DATE;
34 l_xtd VARCHAR2(10);
35 l_comparison_type VARCHAR2(1) :='Y';
36 l_nested_pattern NUMBER;
37 l_cur_suffix VARCHAR2(2);
38 l_url VARCHAR2(300);
39 l_view_by_value VARCHAR2(30);
40 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_COL_TBL;
41 l_join_tbl POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL;
42 l_in_join_tbl POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
43 l_in_join_tbl2 POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
44 l_where_clause VARCHAR2(2000);
45 l_where_clause2 VARCHAR2(2000);
46 l_mv VARCHAR2(30);
47 l_mv2 VARCHAR2(30);
48 l_custom_rec BIS_QUERY_ATTRIBUTES;
49 ERR_MSG VARCHAR2(100);
50 l_sec_context varchar2(10);
51 l_use_only_agg_mv varchar2(1);
52 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
53 l_to_date_type VARCHAR2(3);
54 BEGIN
55 l_join_tbl := POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL();
56 l_col_tbl := POA_DBI_UTIL_PKG.POA_DBI_COL_TBL();
57 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
58 --
59 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
60 if (l_sec_context = 'OU' or l_sec_context = 'OU/COM' or l_sec_context = 'SUPPLIER')then
61 poa_dbi_sutil_pkg.process_parameters(
62 p_param => p_param,
63 p_view_by => l_view_by,
64 p_view_by_col_name => l_view_by_col,
65 p_view_by_value => l_view_by_value,
66 p_comparison_type => l_comparison_type,
67 p_xtd => l_xtd,
68 p_as_of_date => l_as_of_date,
69 p_prev_as_of_date => l_prev_as_of_date,
70 p_cur_suffix => l_cur_suffix,
71 p_nested_pattern => l_nested_pattern,
72 p_where_clause => l_where_clause,
73 p_mv => l_mv,
74 p_join_tbl => l_join_tbl,
75 p_in_join_tbl => l_in_join_tbl,
76 x_custom_output => x_custom_output,
77 p_trend => 'N',
78 p_func_area => 'PO',
79 p_version => '6.0',
80 p_role => 'COM',
81 p_mv_set => 'API');
82
83 l_prev_prev_as_of_date := poa_dbi_calendar_pkg.previous_period_asof_date(l_prev_as_of_date, l_xtd, l_comparison_type);
84 IF(l_sec_context = 'OU' or l_sec_context = 'SUPPLIER') THEN
85 l_to_date_type := 'RLX';
86 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount','Y',p_to_date_type => l_to_date_type);
87 ELSE
88 l_to_date_type := 'XTD';
89 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount','Y',poa_dbi_util_pkg.PREV_PREV,p_to_date_type => l_to_date_type);
90 END IF;
91
92 IF(l_view_by='ITEM+ENI_ITEM_PO_CAT') THEN
93 l_url := 'pFunctionName=POA_DBI_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
94 ELSE
95 l_url := null;
96 END IF;
97
98 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url,l_to_date_type, 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,
107 p_in_join_tables => l_in_join_tbl);
108 elsif(l_sec_context = 'COMP')then
109 poa_dbi_sutil_pkg.process_parameters(
110 p_param => p_param,
111 p_view_by => l_view_by,
112 p_view_by_col_name => l_view_by_col,
113 p_view_by_value => l_view_by_value,
114 p_comparison_type => l_comparison_type,
115 p_xtd => l_xtd,
116 p_as_of_date => l_as_of_date,
117 p_prev_as_of_date => l_prev_as_of_date,
118 p_cur_suffix => l_cur_suffix,
119 p_nested_pattern => l_nested_pattern,
120 p_where_clause => l_where_clause,
121 p_mv => l_mv,
122 p_join_tbl => l_join_tbl,
123 p_in_join_tbl => l_in_join_tbl,
124 x_custom_output => x_custom_output,
125 p_trend => 'N',
126 p_func_area => 'PO',
127 p_version => '8.0',
128 p_role => 'PSM',
129 p_mv_set => 'APIA');
130
131 /*check if we can get everything from aggregated mv*/
132 l_use_only_agg_mv := 'Y';
133 for i in 1..l_in_join_tbl.count loop
134 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
135 if(l_in_join_tbl(i).aggregated_flag = 'N')then
136 l_use_only_agg_mv := 'N';
137 end if;
138 end if;
139 end loop;
140
141 if(l_use_only_agg_mv = 'N') then
142 poa_dbi_sutil_pkg.process_parameters(
143 p_param => p_param,
144 p_view_by => l_view_by,
145 p_view_by_col_name => l_view_by_col,
146 p_view_by_value => l_view_by_value,
147 p_comparison_type => l_comparison_type,
148 p_xtd => l_xtd,
149 p_as_of_date => l_as_of_date,
150 p_prev_as_of_date => l_prev_as_of_date,
151 p_cur_suffix => l_cur_suffix,
152 p_nested_pattern => l_nested_pattern,
153 p_where_clause => l_where_clause2,
154 p_mv => l_mv2,
155 p_join_tbl => l_join_tbl,
156 p_in_join_tbl => l_in_join_tbl2,
157 x_custom_output => x_custom_output,
158 p_trend => 'N',
159 p_func_area => 'PO',
160 p_version => '8.0',
161 p_role => 'PSM',
162 p_mv_set => 'APIB');
163 end if;
164
165
166 l_prev_prev_as_of_date := poa_dbi_calendar_pkg.previous_period_asof_date(l_prev_as_of_date, l_xtd, l_comparison_type);
167
168 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount','Y',poa_dbi_util_pkg.PREV_PREV);
169
170 IF(l_view_by='ITEM+ENI_ITEM_PO_CAT') THEN
171 l_url := 'pFunctionName=POA_DBI_CC_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
172 ELSE
173 l_url := null;
174 END IF;
175
176 if(l_use_only_agg_mv = 'N') then
177 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
178 l_mv_tbl.extend;
179 l_mv_tbl(1).mv_name := l_mv;
180 l_mv_tbl(1).mv_col := l_col_tbl;
181 l_mv_tbl(1).mv_where := l_where_clause;
182 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
183 l_mv_tbl(1).use_grp_id := 'N';
184
185 l_mv_tbl.extend;
186 l_mv_tbl(2).mv_name := l_mv2;
187 l_mv_tbl(2).mv_col := l_col_tbl;
188 l_mv_tbl(2).mv_where := l_where_clause2;
189 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
190 l_mv_tbl(2).use_grp_id := 'N';
191
192 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url,'XTD', l_sec_context) || ' from (' ||
193 poa_dbi_template_pkg.union_all_status_sql(
194 p_mv => l_mv_tbl,
195 p_join_tables => l_join_tbl,
196 p_use_windowing => 'Y',
197 p_paren_count => 3,
198 p_filter_where => get_status_filter_where,
199 p_generate_viewby => 'Y',
200 p_diff_measures => 'N');
201 else
202 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url,'XTD', l_sec_context) || ' from ' ||
203 poa_dbi_template_pkg.status_sql(
204 p_fact_name => l_mv,
205 p_where_clause => l_where_clause,
206 p_join_tables => l_join_tbl,
207 p_use_windowing => 'Y',
208 p_col_name => l_col_tbl,
209 p_use_grpid => 'N',
210 p_filter_where => get_status_filter_where,
211 p_in_join_tables => l_in_join_tbl);
212 end if; /* l_use_only_agg_mv = 'N' */
213 end if; /* l_sec_context = 'OU' or 'OU/COM' or 'SUPPLIER' */
214
215 x_custom_sql := l_query;
216
217 l_custom_rec.attribute_name := '&PREV_PREV_DATE';
218 l_custom_rec.attribute_value := TO_CHAR(l_prev_prev_as_of_date, 'DD/MM/YYYY');
219 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
220 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
221 x_custom_output.EXTEND;
222 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 ERR_MSG := SUBSTR(SQLERRM,1,400);
227 END;
228
229
230 FUNCTION get_status_filter_where return VARCHAR2
231 IS
232 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
233 BEGIN
234 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
235 l_col_tbl.extend;
236 l_col_tbl(1) := 'POA_MEASURE1';
237 l_col_tbl.extend;
238 l_col_tbl(2) := 'POA_PERCENT1';
239 l_col_tbl.extend;
240 l_col_tbl(3) := 'POA_MEASURE2';
241 l_col_tbl.extend;
242 l_col_tbl(4) := 'POA_PERCENT2';
243
244 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
245
246 END;
247
248
249
250 -- -----------------------------------------------------------------------
251 -- |--------------------< get_status_sel_clause >------------------------|
252 -- -----------------------------------------------------------------------
253 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2
254 ,p_view_by_col IN VARCHAR2
255 ,p_url IN VARCHAR2
256 ,p_to_date_type IN VARCHAR2
257 ,p_sec_context IN VARCHAR2) RETURN VARCHAR2 IS
258 l_sel_clause VARCHAR2(10000);
259 BEGIN
260 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
261 ,'PO'
262 ,'6.0');
263 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
264 l_sel_clause := l_sel_clause || ' v.description POA_ATTRIBUTE1, --Description
265 ';
266 ELSE
267 l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1, --Description
268 ';
269 END IF;
270 --
271 l_sel_clause := l_sel_clause || '
272 oset.POA_MEASURE1 POA_MEASURE1, --Invoice Amount
273 oset.POA_PERCENT1 POA_PERCENT1, --Growth Rate
274 oset.POA_PERCENT2 POA_PERCENT2, --Percent of Total
275 oset.POA_MEASURE2 POA_MEASURE2, --Change
276 oset.POA_MEASURE3 POA_MEASURE3, --Grand Total for Invoice Amount
277 oset.POA_MEASURE4 POA_MEASURE4, --Grand Total for Growth Rate
278 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total for Percent of Total
279 oset.POA_PERCENT3 POA_PERCENT3, --KPI Current Rate
280 oset.POA_PERCENT4 POA_PERCENT4, --KPI Previous Rate
281 oset.POA_MEASURE4 POA_MEASURE7,
282 oset.POA_MEASURE8 POA_MEASURE8,
283 ''' || p_url || ''' POA_ATTRIBUTE4,';
284
285 if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
286 p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
287 l_sel_clause := l_sel_clause || '
288 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE5,
289 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE6,';
290 else
291 l_sel_clause := l_sel_clause || '
292 null POA_ATTRIBUTE5,
293 null POA_ATTRIBUTE6,';
294 end if;
295
296 if (p_sec_context = 'COMP') then
297 l_sel_clause := l_sel_clause || '
298 ''pFunctionName=POA_DBI_CC_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE7,';
299 else
300 l_sel_clause := l_sel_clause || '
301 ''pFunctionName=POA_DBI_INV_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE7,';
302 end if;
303
304 l_sel_clause := l_sel_clause || '
305 oset.POA_MEASURE10 POA_MEASURE10,
306 oset.POA_MEASURE11 POA_MEASURE11
307 FROM
308 (SELECT (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
309
310 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
311 l_sel_clause := l_sel_clause || ', base_uom';
312 end if;
313
314 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
315 --
316 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
317 l_sel_clause := l_sel_clause || ',
318 base_uom';
319 END IF;
320 --
321 l_sel_clause := l_sel_clause || ',POA_MEASURE1,POA_PERCENT1,
322 POA_PERCENT2, POA_MEASURE2,
323 POA_MEASURE3, POA_MEASURE4,
324 POA_MEASURE5, POA_PERCENT3,
325 POA_PERCENT4, POA_MEASURE8,
326 POA_MEASURE10, POA_MEASURE11
327 FROM (SELECT ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
328 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
329 l_sel_clause := l_sel_clause || 'base_uom,';
330 END IF;
331 l_sel_clause := l_sel_clause ||
332 ' nvl(c_amount,0) POA_MEASURE1,
333 ' || poa_dbi_util_pkg.change_clause('c_amount','p_amount') || ' POA_PERCENT1,
334 ' || poa_dbi_util_pkg.rate_clause('c_amount','c_amount_total') || ' POA_PERCENT2,
335 ' || poa_dbi_util_pkg.change_clause(
336 poa_dbi_util_pkg.rate_clause('c_amount','c_amount_total'),
337 poa_dbi_util_pkg.rate_clause('p_amount','p_amount_total'),
338 'P') || ' POA_MEASURE2,
339 ' || ' nvl(c_amount_total,0) POA_MEASURE3,
340 ' || poa_dbi_util_pkg.change_clause('c_amount_total','p_amount_total') || ' POA_MEASURE4,
341 ' || poa_dbi_util_pkg.rate_clause('c_amount_total','c_amount_total') || ' POA_MEASURE5,
342 ' || poa_dbi_util_pkg.change_clause('c_amount','p_amount') || ' POA_PERCENT3,';
343 IF(p_to_date_type = 'XTD') THEN
344 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.change_clause('p_amount','p2_amount') || ' POA_PERCENT4 ,
345 ' || poa_dbi_util_pkg.change_clause('p_amount_total', 'p2_amount_total') || ' POA_MEASURE8,
346 nvl(p_amount,0) POA_MEASURE10,
347 nvl(p_amount_total,0) POA_MEASURE11 ';
348 ELSE
349 l_sel_clause := l_sel_clause || ' null POA_PERCENT4 ,
350 null POA_MEASURE8,
351 nvl(p_amount,0) POA_MEASURE10,
352 nvl(p_amount_total,0) POA_MEASURE11 ';
353 END IF;
354
355 RETURN l_sel_clause;
356 END;
357 -- -----------------------------------------------------------------------
358 -- |-----------------------------< trend_sql >---------------------------|
359 -- -----------------------------------------------------------------------
360 PROCEDURE trend_sql(p_param IN BIS_PMV_PAGE_PARAMETER_TBL
361 ,x_custom_sql OUT NOCOPY VARCHAR2
362 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
363 IS
364 l_query VARCHAR2(10000);
365 l_view_by VARCHAR2(120);
366 l_view_by_col_name VARCHAR2(120);
367 l_view_by_value VARCHAR2(30);
368 l_as_of_date DATE;
369 l_prev_as_of_date DATE;
370 l_xtd VARCHAR2(10);
371 l_comparison_type VARCHAR2(1) := 'Y';
372 l_nested_pattern NUMBER;
373 l_where_clause VARCHAR2(2000);
374 l_where_clause2 VARCHAR2(2000);
375 l_cur_suffix VARCHAR2(2);
376 l_url VARCHAR2(300);
377 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_COL_TBL;
378 l_join_tbl POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL;
379 l_in_join_tbl POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
380 l_in_join_tbl2 POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
381 l_mv VARCHAR2(30);
382 l_mv2 VARCHAR2(30);
383 ERR_MSG VARCHAR2(100);
384 l_sec_context varchar2(10);
385 l_use_only_agg_mv varchar2(1);
386 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
387 l_to_date_type varchar2(3);
388 BEGIN
389 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
390 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
391
392 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
393 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM' or l_sec_context = 'SUPPLIER' ) then
394 poa_dbi_sutil_pkg.process_parameters(
395 p_param => p_param,
396 p_view_by => l_view_by,
397 p_view_by_col_name => l_view_by_col_name,
398 p_view_by_value => l_view_by_value,
399 p_comparison_type => l_comparison_type,
400 p_xtd => l_xtd,
401 p_as_of_date => l_as_of_date,
402 p_prev_as_of_date => l_prev_as_of_date,
403 p_cur_suffix => l_cur_suffix,
404 p_nested_pattern => l_nested_pattern,
405 p_where_clause => l_where_clause,
406 p_mv => l_mv,
407 p_join_tbl => l_join_tbl,
408 p_in_join_tbl => l_in_join_tbl,
409 x_custom_output => x_custom_output,
410 p_trend => 'Y',
411 p_func_area => 'PO',
412 p_version => '6.0',
413 p_role => 'COM',
414 p_mv_set => 'API');
415
416 IF(l_sec_context = 'OU' or l_sec_context = 'SUPPLIER') THEN
417 l_to_date_type := 'RLX';
418 ELSE
419 l_to_date_type := 'XTD';
420 END IF;
421
422 poa_dbi_util_pkg.add_column(l_col_tbl,'amount_' || l_cur_suffix,'amount','N', p_to_date_type => l_to_date_type);
423
424 l_query := get_trend_sel_clause || '
425 from '
426 || poa_dbi_template_pkg.trend_sql(
427 p_xtd => l_xtd
428 ,p_comparison_type => l_comparison_type
429 ,p_fact_name => l_mv
430 ,p_where_clause => l_where_clause
431 ,p_col_name => l_col_tbl
432 ,p_use_grpid => 'N'
433 ,p_in_join_tables => l_in_join_tbl);
434
435 elsif(l_sec_context = 'COMP') then
436 poa_dbi_sutil_pkg.process_parameters(
437 p_param => p_param,
438 p_view_by => l_view_by,
439 p_view_by_col_name => l_view_by_col_name,
440 p_view_by_value => l_view_by_value,
441 p_comparison_type => l_comparison_type,
442 p_xtd => l_xtd,
443 p_as_of_date => l_as_of_date,
444 p_prev_as_of_date => l_prev_as_of_date,
445 p_cur_suffix => l_cur_suffix,
446 p_nested_pattern => l_nested_pattern,
447 p_where_clause => l_where_clause,
448 p_mv => l_mv,
449 p_join_tbl => l_join_tbl,
450 p_in_join_tbl => l_in_join_tbl,
451 x_custom_output => x_custom_output,
452 p_trend => 'Y',
453 p_func_area => 'PO',
454 p_version => '8.0',
455 p_role => 'PSM',
456 p_mv_set => 'APIA');
457 /*check if we can get everything from aggregated mv*/
458 l_use_only_agg_mv := 'Y';
459 for i in 1..l_in_join_tbl.count loop
460 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
461 if(l_in_join_tbl(i).aggregated_flag = 'N')then
462 l_use_only_agg_mv := 'N';
463 end if;
464 end if;
465 end loop;
466
467 if(l_use_only_agg_mv = 'N') then
468 poa_dbi_sutil_pkg.process_parameters(
469 p_param => p_param,
470 p_view_by => l_view_by,
471 p_view_by_col_name => l_view_by_col_name,
472 p_view_by_value => l_view_by_value,
473 p_comparison_type => l_comparison_type,
474 p_xtd => l_xtd,
475 p_as_of_date => l_as_of_date,
476 p_prev_as_of_date => l_prev_as_of_date,
477 p_cur_suffix => l_cur_suffix,
478 p_nested_pattern => l_nested_pattern,
479 p_where_clause => l_where_clause2,
480 p_mv => l_mv2,
481 p_join_tbl => l_join_tbl,
482 p_in_join_tbl => l_in_join_tbl2,
483 x_custom_output => x_custom_output,
484 p_trend => 'Y',
485 p_func_area => 'PO',
486 p_version => '8.0',
487 p_role => 'PSM',
488 p_mv_set => 'APIB');
489 end if;
490
491 poa_dbi_util_pkg.add_column(l_col_tbl,'amount_' || l_cur_suffix,'amount','N');
492
493 if(l_use_only_agg_mv = 'N') then
494 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
495 l_mv_tbl.extend;
496 l_mv_tbl(1).mv_name := l_mv;
497 l_mv_tbl(1).mv_col := l_col_tbl;
498 l_mv_tbl(1).mv_where := l_where_clause;
499 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
500 l_mv_tbl(1).use_grp_id := 'N';
501 l_mv_tbl(1).mv_xtd := l_xtd;
502
503 l_mv_tbl.extend;
504 l_mv_tbl(2).mv_name := l_mv2;
505 l_mv_tbl(2).mv_col := l_col_tbl;
506 l_mv_tbl(2).mv_where := l_where_clause2;
507 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
508 l_mv_tbl(2).use_grp_id := 'N';
509 l_mv_tbl(2).mv_xtd := l_xtd;
510
511 l_query := get_trend_sel_clause('union') || '
512 from '
513 || poa_dbi_template_pkg.union_all_trend_sql(
514 p_mv => l_mv_tbl,
515 p_comparison_type => l_comparison_type,
516 p_diff_measures => 'N');
517 else
518 l_query := get_trend_sel_clause || '
519 from '
520 || poa_dbi_template_pkg.trend_sql(
521 p_xtd => l_xtd,
522 p_comparison_type => l_comparison_type,
523 p_fact_name => l_mv,
524 p_where_clause => l_where_clause,
525 p_col_name => l_col_tbl,
526 p_use_grpid => 'N',
527 p_in_join_tables => l_in_join_tbl);
528
529 end if; /* l_use_only_agg_mv = 'N' */
530 end if; /* l_sec_context = 'OU' or 'OU/COM' or 'SUPPLIER' */
531 x_custom_sql := l_query;
532 EXCEPTION
533 WHEN OTHERS THEN
534 ERR_MSG := SUBSTR(SQLERRM,1,400);
535 END;
536 -- -----------------------------------------------------------------------
537 -- |-------------------< get_trend_sel_clause >--------------------------|
538 -- -----------------------------------------------------------------------
539 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2
540 IS
541 l_sel_clause VARCHAR2(4000);
542 BEGIN
543 if (p_type = 'trend') then
544 l_sel_clause := 'select cal.name VIEWBY,';
545 else
546 l_sel_clause := 'select cal_name VIEWBY,';
547 end if;
548 l_sel_clause := l_sel_clause || '
549 nvl(c_amount,0) POA_MEASURE2,
550 p_amount POA_MEASURE1,
551 ' || poa_dbi_util_pkg.change_clause('c_amount','p_amount') || ' POA_PERCENT1';
552 --
553 RETURN l_sel_clause;
554 END get_trend_sel_clause;
555
556 END poa_dbi_inv_pkg;