[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_APL_PKG
Source
1 PACKAGE BODY poa_dbi_apl_pkg
2 /* $Header: poadbiaplb.pls 120.5 2006/04/21 02:27:03 sdiwakar noship $ */
3
4 AS
5
6 FUNCTION get_status_sel_clause(
7 p_view_by_dim in varchar2,
8 p_view_by_col in varchar2,
9 p_url in varchar2,
10 p_sec_context in varchar2
11 ) return varchar2;
12 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2;
13 FUNCTION get_status_filter_where return VARCHAR2;
14 FUNCTION get_kpi_filter_where return VARCHAR2;
15
16 procedure status_sql(p_param in bis_pmv_page_parameter_tbl,
17 x_custom_sql out nocopy varchar2,
18 x_custom_output out nocopy bis_query_attributes_tbl)
19 is
20 l_query varchar2(10000);
21 l_view_by varchar2(120);
22 l_view_by_col varchar2(120);
23 l_as_of_date date;
24 l_prev_as_of_date date;
25 l_xtd varchar2(10);
26 l_comparison_type varchar2(1) := 'Y';
27 l_nested_pattern number;
28 l_cur_suffix varchar2(2);
29 l_url varchar2(300);
30 l_custom_sql varchar2(4000);
31 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
32 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
33 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
34 l_in_join_tbl2 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
35 l_where_clause varchar2(2000);
36 l_where_clause2 varchar2(2000);
37 l_view_by_value varchar2(100);
38 l_mv varchar2(30);
39 l_mv2 varchar2(30);
40 l_sec_context varchar2(10);
41 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
42 l_use_only_agg_mv varchar2(1);
43 err_msg varchar2(100);
44 begin
45 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
46 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
47
48 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
49 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
50 poa_dbi_sutil_pkg.process_parameters(
51 p_param => p_param,
52 p_view_by => l_view_by,
53 p_view_by_col_name => l_view_by_col,
54 p_view_by_value => l_view_by_value,
55 p_comparison_type => l_comparison_type,
56 p_xtd => l_xtd,
57 p_as_of_date => l_as_of_date,
58 p_prev_as_of_date => l_prev_as_of_date,
59 p_cur_suffix => l_cur_suffix,
60 p_nested_pattern => l_nested_pattern,
61 p_where_clause => l_where_clause,
62 p_mv => l_mv,
63 p_join_tbl => l_join_tbl,
64 p_in_join_tbl => l_in_join_tbl,
65 x_custom_output => x_custom_output,
66 p_trend => 'N',
67 p_func_area => 'AP',
68 p_version => '5.0',
69 p_role => 'VPP',
70 p_mv_set => 'IDL');
71
72 poa_dbi_util_pkg.add_column(l_col_tbl, 'leakage_amount_' || l_cur_suffix, 'leakage_amount');
73 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount');
74
75 if((l_view_by = 'HRI_PERSON+HRI_PER') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
76 l_url := null;
77 else
78 l_url := 'pFunctionName=POA_DBI_APL_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=HRI_PERSON+HRI_PER&pParamIds=Y';
79 end if;
80
81 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url, l_sec_context) ||
82 ' from '|| fnd_global.newline ||
83 poa_dbi_template_pkg.status_sql(
84 p_fact_name => l_mv,
85 p_where_clause => l_where_clause,
86 p_join_tables => l_join_tbl,
87 p_use_windowing => 'Y',
88 p_col_name => l_col_tbl,
89 p_use_grpid => 'N',
90 p_filter_where => get_status_filter_where,
91 p_in_join_tables => l_in_join_tbl);
92 elsif(l_sec_context = 'COMP') then
93 poa_dbi_sutil_pkg.process_parameters(
94 p_param => p_param,
95 p_view_by => l_view_by,
96 p_view_by_col_name => l_view_by_col,
97 p_view_by_value => l_view_by_value,
98 p_comparison_type => l_comparison_type,
99 p_xtd => l_xtd,
100 p_as_of_date => l_as_of_date,
101 p_prev_as_of_date => l_prev_as_of_date,
102 p_cur_suffix => l_cur_suffix,
103 p_nested_pattern => l_nested_pattern,
104 p_where_clause => l_where_clause,
105 p_mv => l_mv,
106 p_join_tbl => l_join_tbl,
107 p_in_join_tbl => l_in_join_tbl,
108 x_custom_output => x_custom_output,
109 p_trend => 'N',
110 p_func_area => 'AP',
111 p_version => '8.0',
112 p_role => 'VPP',
113 p_mv_set => 'IDLA');
114
115 poa_dbi_util_pkg.add_column(l_col_tbl, 'leakage_amount_' || l_cur_suffix, 'leakage_amount');
116 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount');
117
118 if((l_view_by = 'HRI_PERSON+HRI_PER') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
119 l_url := null;
120 else
121 l_url := 'pFunctionName=POA_DBI_CC_APL_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=HRI_PERSON+HRI_PER&pParamIds=Y';
122 end if;
123
124 /*check if we can get everything from aggregated mv*/
125 l_use_only_agg_mv := 'Y';
126 for i in 1..l_in_join_tbl.count loop
127 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
128 if(l_in_join_tbl(i).aggregated_flag = 'N')then
129 l_use_only_agg_mv := 'N';
130 end if;
131 end if;
132 end loop;
133
134 if(l_use_only_agg_mv = 'N') then
135 poa_dbi_sutil_pkg.process_parameters(
136 p_param => p_param,
137 p_view_by => l_view_by,
138 p_view_by_col_name => l_view_by_col,
139 p_view_by_value => l_view_by_value,
140 p_comparison_type => l_comparison_type,
141 p_xtd => l_xtd,
142 p_as_of_date => l_as_of_date,
143 p_prev_as_of_date => l_prev_as_of_date,
144 p_cur_suffix => l_cur_suffix,
145 p_nested_pattern => l_nested_pattern,
146 p_where_clause => l_where_clause2,
147 p_mv => l_mv2,
148 p_join_tbl => l_join_tbl,
149 p_in_join_tbl => l_in_join_tbl2,
150 x_custom_output => x_custom_output,
151 p_trend => 'N',
152 p_func_area => 'AP',
153 p_version => '8.0',
154 p_role => 'VPP',
155 p_mv_set => 'IDLB');
156
157 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
158 l_mv_tbl.extend;
159 l_mv_tbl(1).mv_name := l_mv;
160 l_mv_tbl(1).mv_col := l_col_tbl;
161 l_mv_tbl(1).mv_where := l_where_clause;
162 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
163 l_mv_tbl(1).use_grp_id := 'N';
164
165 l_mv_tbl.extend;
166 l_mv_tbl(2).mv_name := l_mv2;
167 l_mv_tbl(2).mv_col := l_col_tbl;
168 l_mv_tbl(2).mv_where := l_where_clause2;
169 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
170 l_mv_tbl(2).use_grp_id := 'N';
171 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url, l_sec_context) ||
172 ' from ( '||fnd_global.newline ||
173 poa_dbi_template_pkg.union_all_status_sql(
174 p_mv => l_mv_tbl,
175 p_join_tables => l_join_tbl,
176 p_use_windowing => 'Y',
177 p_paren_count => 3,
178 p_filter_where => get_status_filter_where,
179 p_generate_viewby => 'Y',
180 p_diff_measures => 'N');
181 else
182 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url, l_sec_context) ||
183 ' from '|| fnd_global.newline ||
184 poa_dbi_template_pkg.status_sql(
185 p_fact_name => l_mv,
186 p_where_clause => l_where_clause,
187 p_join_tables => l_join_tbl,
188 p_use_windowing => 'Y',
189 p_col_name => l_col_tbl,
190 p_use_grpid => 'N',
191 p_filter_where => get_status_filter_where,
192 p_in_join_tables => l_in_join_tbl);
193 end if; /* l_use_only_agg_mv = 'N' */
194 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
195 x_custom_sql := l_query;
196 end;
197
198 procedure trend_sql(p_param in bis_pmv_page_parameter_tbl,
199 x_custom_sql out nocopy varchar2,
200 x_custom_output out nocopy bis_query_attributes_tbl)
201 is
202 l_query varchar2(10000);
203 l_view_by varchar2(120);
204 l_view_by_col varchar2(120);
205 l_as_of_date date;
206 l_prev_as_of_date date;
207 l_xtd varchar2(10);
208 l_comparison_type varchar2(1) := 'Y';
209 l_nested_pattern number;
210 l_cur_suffix varchar2(2);
211 l_custom_sql varchar2(4000);
212 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
213 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
214 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
215 l_in_join_tbl2 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
216 l_mv varchar2(30);
217 l_mv2 varchar2(30);
218 l_where_clause varchar2(2000);
219 l_where_clause2 varchar2(2000);
220 l_view_by_value varchar2(100);
221 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
222 l_sec_context varchar2(10);
223 l_use_only_agg_mv varchar2(1);
224 err_msg varchar2(100);
225 begin
226 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
227 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
228
229 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
230 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
231 poa_dbi_sutil_pkg.process_parameters(
232 p_param => p_param,
233 p_view_by => l_view_by,
234 p_view_by_col_name => l_view_by_col,
235 p_view_by_value => l_view_by_value,
236 p_comparison_type => l_comparison_type,
237 p_xtd => l_xtd,
238 p_as_of_date => l_as_of_date,
239 p_prev_as_of_date => l_prev_as_of_date,
240 p_cur_suffix => l_cur_suffix,
241 p_nested_pattern => l_nested_pattern,
242 p_where_clause => l_where_clause,
243 p_mv => l_mv,
244 p_join_tbl => l_join_tbl,
245 p_in_join_tbl => l_in_join_tbl,
246 x_custom_output => x_custom_output,
247 p_trend => 'Y',
248 p_func_area => 'AP',
249 p_version => '5.0',
250 p_role => 'VPP',
251 p_mv_set => 'IDL');
252
253 poa_dbi_util_pkg.add_column(l_col_tbl, 'leakage_amount_' || l_cur_suffix, 'leakage_amount', 'N');
254
255 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount', 'N');
256
257 l_query := get_trend_sel_clause || ' from '|| fnd_global.newline ||
258 poa_dbi_template_pkg.trend_sql(
259 p_xtd => l_xtd,
260 p_comparison_type => l_comparison_type,
261 p_fact_name => l_mv,
262 p_where_clause => l_where_clause,
263 p_col_name => l_col_tbl,
264 p_use_grpid => 'N',
265 p_in_join_tables => l_in_join_tbl);
266 elsif(l_sec_context = 'COMP')then
267 poa_dbi_sutil_pkg.process_parameters(
268 p_param => p_param,
269 p_view_by => l_view_by,
270 p_view_by_col_name => l_view_by_col,
271 p_view_by_value => l_view_by_value,
272 p_comparison_type => l_comparison_type,
273 p_xtd => l_xtd,
274 p_as_of_date => l_as_of_date,
275 p_prev_as_of_date => l_prev_as_of_date,
276 p_cur_suffix => l_cur_suffix,
277 p_nested_pattern => l_nested_pattern,
278 p_where_clause => l_where_clause,
279 p_mv => l_mv,
280 p_join_tbl => l_join_tbl,
281 p_in_join_tbl => l_in_join_tbl,
282 x_custom_output => x_custom_output,
283 p_trend => 'Y',
284 p_func_area => 'AP',
285 p_version => '8.0',
286 p_role => 'VPP',
287 p_mv_set => 'IDLA');
288
289 poa_dbi_util_pkg.add_column(l_col_tbl, 'leakage_amount_' || l_cur_suffix, 'leakage_amount', 'N');
290
291 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount', 'N');
292
293 /*check if we can get everything from aggregated mv*/
294 l_use_only_agg_mv := 'Y';
295 for i in 1..l_in_join_tbl.count loop
296 if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
297 if(l_in_join_tbl(i).aggregated_flag = 'N')then
298 l_use_only_agg_mv := 'N';
299 end if;
300 end if;
301 end loop;
302
303 if(l_use_only_agg_mv = 'N') then
304 poa_dbi_sutil_pkg.process_parameters(
305 p_param => p_param,
306 p_view_by => l_view_by,
307 p_view_by_col_name => l_view_by_col,
308 p_view_by_value => l_view_by_value,
309 p_comparison_type => l_comparison_type,
310 p_xtd => l_xtd,
311 p_as_of_date => l_as_of_date,
312 p_prev_as_of_date => l_prev_as_of_date,
313 p_cur_suffix => l_cur_suffix,
314 p_nested_pattern => l_nested_pattern,
315 p_where_clause => l_where_clause2,
316 p_mv => l_mv2,
317 p_join_tbl => l_join_tbl,
318 p_in_join_tbl => l_in_join_tbl2,
319 x_custom_output => x_custom_output,
320 p_trend => 'Y',
321 p_func_area => 'AP',
322 p_version => '8.0',
323 p_role => 'VPP',
324 p_mv_set => 'IDLB');
325
326 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
327 l_mv_tbl.extend;
328 l_mv_tbl(1).mv_name := l_mv;
332 l_mv_tbl(1).use_grp_id := 'N';
329 l_mv_tbl(1).mv_col := l_col_tbl;
330 l_mv_tbl(1).mv_where := l_where_clause;
331 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
333 l_mv_tbl(1).mv_xtd := l_xtd;
334
335 l_mv_tbl.extend;
336 l_mv_tbl(2).mv_name := l_mv2;
337 l_mv_tbl(2).mv_col := l_col_tbl;
338 l_mv_tbl(2).mv_where := l_where_clause2;
339 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
340 l_mv_tbl(2).use_grp_id := 'N';
341 l_mv_tbl(2).mv_xtd := l_xtd;
342
346 p_comparison_type => l_comparison_type,
343 l_query := get_trend_sel_clause('union') || ' from ' ||fnd_global.newline ||
344 poa_dbi_template_pkg.union_all_trend_sql(
345 p_mv => l_mv_tbl,
347 p_diff_measures => 'N');
348 else
349 l_query := get_trend_sel_clause || ' from ' || fnd_global.newline ||
350 poa_dbi_template_pkg.trend_sql(
351 p_xtd => l_xtd,
352 p_comparison_type => l_comparison_type,
353 p_fact_name => l_mv,
354 p_where_clause => l_where_clause,
355 p_col_name => l_col_tbl,
356 p_use_grpid => 'N',
357 p_in_join_tables => l_in_join_tbl);
358 end if; /*l_use_only_agg_mv = 'N' */
359 end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
360
361 x_custom_sql := l_query;
362 end;
363
364
365 FUNCTION get_kpi_filter_where return VARCHAR2
366 IS
367 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
368 BEGIN
369 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
370 l_col_tbl.extend;
371 l_col_tbl(1) := 'POA_PERCENT2';
372 l_col_tbl.extend;
373 l_col_tbl(2) := 'POA_PERCENT1';
374
375
376 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
377
378 END;
379
380 procedure kpi_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(4000);
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_org_where varchar2(500);
393 l_cur_suffix varchar2(2);
394 l_custom_sql varchar2(4000);
395 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
396 l_where_clause varchar2(1000);
397 l_mv varchar2(30);
398 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
399 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
400 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
401 l_custom_rec BIS_QUERY_ATTRIBUTES;
402 l_view_by_value varchar2(100);
403 begin
404 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
405 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
406
407 poa_dbi_sutil_pkg.process_parameters(
408 p_param => p_param,
409 p_view_by => l_view_by,
410 p_view_by_col_name => l_view_by_col,
411 p_view_by_value => l_view_by_value,
412 p_comparison_type => l_comparison_type,
413 p_xtd => l_xtd,
414 p_as_of_date => l_as_of_date,
415 p_prev_as_of_date => l_prev_as_of_date,
416 p_cur_suffix => l_cur_suffix,
417 p_nested_pattern => l_nested_pattern,
418 p_where_clause => l_where_clause,
419 p_mv => l_mv,
420 p_join_tbl => l_join_tbl,
421 p_in_join_tbl => l_in_join_tbl,
422 x_custom_output => x_custom_output,
423 p_trend => 'N',
424 p_func_area => 'AP',
425 p_version => '5.0',
426 p_role => 'VPP',
427 p_mv_set => 'IDL');
428
429 poa_dbi_util_pkg.add_column(l_col_tbl, 'leakage_amount_' || l_cur_suffix, 'leakage_amount');
430 poa_dbi_util_pkg.add_column(l_col_tbl, 'amount_' || l_cur_suffix, 'amount');
431
432 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
433
434 l_join_rec.table_name :=
435 poa_dbi_sutil_pkg.get_table('ORGANIZATION+FII_OPERATING_UNITS', 'AP', '5.0');
436 l_join_rec.table_alias := 'v';
437 l_join_rec.fact_column :=
438 poa_dbi_sutil_pkg.get_col_name('ORGANIZATION+FII_OPERATING_UNITS', 'AP', '5.0', 'IDL');
439 l_join_rec.column_name := 'id';
440
441 l_join_tbl.extend;
442 l_join_tbl(l_join_tbl.count) :=l_join_rec;
443
444 l_query := 'select v.value VIEWBY,
445 oset.POA_PERCENT1 POA_PERCENT1, --Current
446 oset.POA_PERCENT2 POA_PERCENT2, --Prior
447 oset.POA_MEASURE1 POA_MEASURE1, --Current Leakage Amount
448 oset.POA_MEASURE2 POA_MEASURE2 --Current Amount
449 from
450 (select * from
451 (select org_id,
452 ' || poa_dbi_util_pkg.rate_clause('c_leakage_amount','c_amount') || ' POA_PERCENT1,
453 ' || poa_dbi_util_pkg.rate_clause('p_leakage_amount','p_amount') || ' POA_PERCENT2,
454 ' || poa_dbi_util_pkg.rate_clause('c_leakage_amount_total','c_amount_total') || ' POA_MEASURE1,
455 ' || poa_dbi_util_pkg.rate_clause('p_leakage_amount_total','p_amount_total') || ' POA_MEASURE2
456 from
457 ' || poa_dbi_template_pkg.status_sql(
458 p_fact_name => l_mv,
459 p_where_clause => l_where_clause,
460 p_join_tables => l_join_tbl,
461 p_use_windowing => 'N',
462 p_col_name => l_col_tbl,
463 p_use_grpid => 'N',
464 p_filter_where => get_kpi_filter_where,
465 p_in_join_tables => l_in_join_tbl);
466
467 x_custom_sql := l_query;
468 end;
469
470
471 FUNCTION get_status_filter_where return VARCHAR2
472 IS
473 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
474 BEGIN
475 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
476 l_col_tbl.extend;
477 l_col_tbl(1) := 'POA_MEASURE1';
478 l_col_tbl.extend;
482 l_col_tbl.extend;
479 l_col_tbl(2) := 'POA_PERCENT2';
480 l_col_tbl.extend;
481 l_col_tbl(3) := 'POA_PERCENT1';
483 l_col_tbl(4) := 'POA_MEASURE3';
484
485 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
486
487 END;
488
489
490 FUNCTION get_status_sel_clause(
491 p_view_by_dim in varchar2,
492 p_view_by_col in varchar2,
493 p_url in varchar2,
494 p_sec_context in varchar2
495 ) return varchar2
496 IS
497 l_sel_clause varchar2(4000);
498 BEGIN
499 l_sel_clause :=
500 'select ' || case p_view_by_col
501 when 'inv_d_created_by' then 'decode(v.value, null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.value) '
502 else 'v.value ' end ||
503 'VIEWBY,
504 decode(v.id, null, -1, v.id) VIEWBYID,
505 oset.POA_MEASURE1 POA_MEASURE1, --Leakage Amount
506 oset.POA_MEASURE1 POA_MEASURE2, --Leakage
507 oset.POA_PERCENT1 POA_PERCENT1, --Change
508 oset.POA_MEASURE3 POA_MEASURE3, --Invoice Amount
509 oset.POA_PERCENT2 POA_PERCENT2, --Leakage Rate
510 oset.POA_MEASURE4 POA_MEASURE4, --Total Leakage Amount
511 oset.POA_MEASURE5 POA_MEASURE5, --Total Invoice Amount
512 oset.POA_MEASURE6 POA_MEASURE6, --Total Change
513 oset.POA_MEASURE7 POA_MEASURE7, --Total Leakage Rate
514 ''' || p_url || ''' POA_MEASURE8,
515 ''' || p_url || ''' POA_MEASURE9,';
516
517 if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
518 p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
519 l_sel_clause := l_sel_clause || '
520 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_APL_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE3,
521 decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_APL_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE4,';
522 else
523 l_sel_clause := l_sel_clause || '
524 null POA_ATTRIBUTE3,
525 null POA_ATTRIBUTE4,';
526 end if;
527
528 if (p_sec_context = 'COMP') then
529 l_sel_clause := l_sel_clause || '
530 ''pFunctionName=POA_DBI_CC_APL_TREND_RPT&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=TIME+FII_TIME_ENT_YEAR'' POA_ATTRIBUTE5';
531 else
532 l_sel_clause := l_sel_clause || '
533 ''pFunctionName=POA_DBI_APL_TREND_RPT&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=TIME+FII_TIME_ENT_YEAR'' POA_ATTRIBUTE5';
534 end if;
535 l_sel_clause := l_sel_clause || '
536 from
537 (select (rank() over (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
538 || p_view_by_col || ',
539 POA_MEASURE1, POA_PERCENT1, POA_MEASURE3, POA_PERCENT2, POA_MEASURE4,
540 POA_MEASURE5, POA_MEASURE6, POA_MEASURE7 from
541 (select ' || p_view_by_col || ',
542 ' || p_view_by_col || ' VIEWBY,
543 nvl(c_leakage_amount,0) POA_MEASURE1,
544 ' || poa_dbi_util_pkg.change_clause('c_leakage_amount','p_leakage_amount') || ' POA_PERCENT1,
545 nvl(c_amount,0) POA_MEASURE3,
546 ' || poa_dbi_util_pkg.rate_clause('c_leakage_amount','c_amount') || ' POA_PERCENT2,
547 nvl(c_leakage_amount_total,0) POA_MEASURE4,
548 nvl(c_amount_total,0) POA_MEASURE5,
549 ' || poa_dbi_util_pkg.change_clause('c_leakage_amount_total','p_leakage_amount_total') || ' POA_MEASURE6,
550 ' || poa_dbi_util_pkg.rate_clause('c_leakage_amount_total','c_amount_total') || ' POA_MEASURE7';
551
552 return l_sel_clause;
553 END;
554
555 FUNCTION get_trend_sel_clause(p_type in varchar2 := 'trend') return VARCHAR2
556 IS
557
558 l_sel_clause varchar2(4000);
559
560 BEGIN
561 if (p_type = 'trend') then
562 l_sel_clause := 'select cal.name VIEWBY,'||fnd_global.newline;
563 else
564 l_sel_clause := 'select cal_name VIEWBY,'||fnd_global.newline;
565 end if;
566 l_sel_clause := l_sel_clause || 'nvl(p_leakage_amount,0) POA_MEASURE1,
567 nvl(c_leakage_amount,0) POA_MEASURE2,
568 nvl(p_leakage_amount,0) POA_PERCENT1,
569 ' || poa_dbi_util_pkg.change_clause('c_leakage_amount','p_leakage_amount') || ' POA_PERCENT3,
570 nvl(c_leakage_amount,0) POA_PERCENT2';
571
572 return l_sel_clause;
573
574 END;
575
576 end poa_dbi_apl_pkg;