[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_SRM_BKRNWL_PVT
Source
1 PACKAGE BODY OKI_DBI_SRM_BKRNWL_PVT AS
2 /* $Header: OKIIBKGB.pls 120.1 2006/03/28 23:26:44 asparama noship $ */
3
4 FUNCTION get_book_start_2way_sql (
5 l_book_where IN VARCHAR2
6 , l_start_where IN VARCHAR2
7 , l_viewby_col_special IN VARCHAR2
8 , l_view_by_col IN VARCHAR2
9 , l_cur_suffix IN VARCHAR2)
10 RETURN VARCHAR2;
11
12 FUNCTION get_trend_sel_clause
13 RETURN VARCHAR2;
14
15 FUNCTION get_table_sel_clause (
16 p_view_by_dim IN VARCHAR2
17 , p_view_by_col IN VARCHAR2)
18 RETURN VARCHAR2;
19
20 FUNCTION get_col_name (p_dim_name VARCHAR2)
21 RETURN VARCHAR2;
22
23
24 /*******************************************************************************
25 Procedure: get_rates_table_sql
26 Description: Procedure to retrieve the sql statement for
27 the Booking to Renewal Ratios Drill Down Report
28 *******************************************************************************/
29
30 PROCEDURE get_rates_table_sql(
31 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
32 x_custom_sql OUT NOCOPY VARCHAR2,
33 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
34 IS
35
36 l_query VARCHAR2 (32767);
37 l_view_by VARCHAR2 (120);
38 l_view_by_col VARCHAR2 (120);
39 l_as_of_date DATE;
40 l_prev_as_of_date DATE;
41 l_xtd VARCHAR2 (10);
42 l_comparison_type VARCHAR2 (1) ;
43 l_period_type VARCHAR2(10);
44 l_nested_pattern NUMBER;
45 l_cur_suffix VARCHAR2 (2);
46 l_where_clause VARCHAR2 (2000);
47 l_mv VARCHAR2 (2000);
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_to_date_xed VARCHAR2 (3);
51 l_to_date_xtd VARCHAR2 (3);
52 l_view_by_table VARCHAR2(1000);
53
54
55 l_viewby_select VARCHAR2(10000);
56 l_url_select VARCHAR2(20000);
57 l_book_where VARCHAR2(10000);
58 l_start_where VARCHAR2(10000);
59 l_VIEWBY_RANK_ORDER VARCHAR2(10000);
60 l_viewby_col_special VARCHAR2(60); -- Needed when the view by is resource group id
61 l_filter_where VARCHAR2(10000);
62 l_prodcat_url VARCHAR2(300);
63
64 BEGIN
65 l_to_date_xed := 'XED';
66 l_to_date_xtd := 'XTD';
67 l_comparison_type := 'Y';
68 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
69 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
70
71
72 oki_dbi_util_pvt.process_parameters ( p_param => p_param
73 , p_view_by => l_view_by
74 , p_view_by_col_name => l_view_by_col
75 , p_comparison_type => l_comparison_type
76 , p_xtd => l_xtd
77 , p_as_of_date => l_as_of_date
78 , p_prev_as_of_date => l_prev_as_of_date
79 , p_cur_suffix => l_cur_suffix
80 , p_nested_pattern => l_nested_pattern
81 , p_where_clause => l_where_clause
82 , p_mv => l_mv
83 , p_join_tbl => l_join_tbl
84 , p_period_type => l_period_type
85 , p_trend => 'N'
86 , p_func_area => 'OKI'
87 , p_version => '6.0'
88 , p_role => NULL
89 , p_mv_set => 'SRM_DET'
90 , p_rg_where => 'Y');
91
92 l_view_by_table := oki_dbi_util_pvt.get_table(dim_name => l_view_by
93 ,p_func_area => 'SRM'
94 ,p_version => '6.0' );
95
96 IF(l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
97 THEN
98 l_prodcat_url :=
99 'decode(leaf_node_flag,''Y'',
100 ''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM'',
101 ''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_4 ';
102 ELSE
103 l_prodcat_url := ''''' OKI_DYNAMIC_URL_4 ';
104 END IF;
105
106 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(l_view_by, 'SRM', '6.0');
107
108
109 /* Additional filter needed to avoid displaying records queried due to total values at node */
110
111 l_filter_where := ' AND ( ABS(oki_measure_1) +
112 ABS(oki_measure_2) +
113 ABS(oki_measure_3) +
114 ABS(oki_measure_4) +
115 ABS(oki_measure_5) +
116 ABS(oki_measure_6) ) <> 0';
117
118 l_viewby_select := l_viewby_select ||
119 ', OKI_DYNAMIC_URL_1 ,'|| l_prodcat_url ||',oki_measure_1 ,oki_measure_2 ,oki_calc_item1, oki_measure_3
120 ,oki_measure_4, oki_calc_item2,oki_calc_item2 oki_calc_item4, oki_measure_5 ,oki_measure_6 ,oki_calc_item3
121 , oki_calc_item3 oki_calc_item5, oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
122 ,oki_measure_15,oki_measure_16 ,oki_measure_17, oki_measure_18, oki_measure_19
123 FROM (
124 SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||l_view_by_col||') - 1 rnk ,'||l_view_by_col||'
125 ,OKI_DYNAMIC_URL_1 ,oki_measure_1 ,oki_measure_2 ,oki_calc_item1 ,oki_measure_3 ,oki_measure_4 ,oki_calc_item2
126 ,oki_measure_5 ,oki_measure_6 ,oki_calc_item3 ,oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
127 ,oki_measure_15 ,oki_measure_16,oki_measure_17,oki_measure_18, oki_measure_19
128 FROM ( ';
129
130 /* Dynamic URL's */
131 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
132 THEN
133 l_url_select :=
134 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 ';
135 l_viewby_col_special := ' imm_child_rg_id ';
136 ELSE
137 l_url_select := ' SELECT NULL OKI_DYNAMIC_URL_1 ';
138 l_viewby_col_special := NULL ;
139 END IF;
140
141 /* From and Joins */
142 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
143 THEN
144 l_book_where := '
145 FROM '||l_mv ||' fact
146 WHERE fact.mx_id = 4
147 AND fact.renewal_flag IN (1,3)
148 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
149 AND &BIS_CURRENT_ASOF_DATE'
150 || l_where_clause || '
151 GROUP BY imm_child_rg_id, resource_id ';
152
153 l_start_where := '
154 FROM '||l_mv ||' fact
155 WHERE fact.mx_id = 5
156 AND fact.renewal_flag IN (1,3)
157 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
158 AND &BIS_CURRENT_ASOF_DATE'
159 || l_where_clause || '
160 GROUP BY imm_child_rg_id, resource_id ';
161 ELSE
162 l_book_where := '
163 FROM '||l_mv ||' fact
164 WHERE fact.mx_id = 4
165 AND fact.renewal_flag IN (1,3)
166 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
167 AND &BIS_CURRENT_ASOF_DATE '
168 || l_where_clause || '
169 GROUP BY ' ||l_view_by_col ;
170
171 l_start_where := '
172 FROM '||l_mv ||' fact
173 WHERE fact.mx_id = 5
174 AND fact.renewal_flag IN (1,3)
175 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
176 AND &BIS_CURRENT_ASOF_DATE '
177 || l_where_clause || '
178 GROUP BY ' ||l_view_by_col ;
179 END IF;
180
181
182 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
183 THEN
184 l_VIEWBY_RANK_ORDER :=
185 ')oset05)oset10))oset ,'
186 || 'jtf_rs_groups_vl g, jtf_rs_resource_extns_vl r '
187 || 'where oset.rg_id=g.group_id and oset.resource_id=r.resource_id(+) '
188 || l_filter_where || '
189 AND (rnk BETWEEN &START_INDEX and &END_INDEX or &END_INDEX = -1)
190 &ORDER_BY_CLAUSE nulls last ';
191 ELSE
192 l_VIEWBY_RANK_ORDER :=
193 ')oset05)oset10))oset ,'
194 || l_view_by_table || ' v
195 WHERE v.id = oset.'||l_view_by_col|| l_filter_where || '
196 AND (rnk BETWEEN &START_INDEX and &END_INDEX or &END_INDEX = -1)
197 &ORDER_BY_CLAUSE nulls last ';
198 END IF;
199 /*
200 l_VIEWBY_RANK_ORDER :=
201 ')oset05)oset10))oset ,'
202 || l_view_by_table || ' v
203 WHERE v.id = oset.'||l_view_by_col|| l_filter_where || '
204 AND (rnk BETWEEN &START_INDEX and &END_INDEX or &END_INDEX = -1)
205 &ORDER_BY_CLAUSE nulls last ';
206 */
207 l_query := l_viewby_select || l_url_select ||' ,'||
208 -- Generate sql query
209 l_view_by_col || ',' ||
210 'oset10.booked_val OKI_MEASURE_1,
211 oset10.start_val OKI_MEASURE_2,
212 oset10.val_rate OKI_CALC_ITEM1,
213 oset10.booked_lcount OKI_MEASURE_3,
214 oset10.start_lcount OKI_MEASURE_4,
215 oset10.lcount_rate OKI_CALC_ITEM2,
216 oset10.booked_hcount OKI_MEASURE_5,
217 oset10.start_hcount OKI_MEASURE_6,
218 oset10.hcount_rate OKI_CALC_ITEM3,
219 oset10.booked_val_tot OKI_MEASURE_11,
220 oset10.start_val_tot OKI_MEASURE_12,
221 oset10.val_rate_tot OKI_MEASURE_13,
222 oset10.booked_lcount_tot OKI_MEASURE_14,
223 oset10.start_lcount_tot OKI_MEASURE_15,
224 oset10.lcount_rate_tot OKI_MEASURE_16,
225 oset10.booked_hcount_tot OKI_MEASURE_17,
226 oset10.start_hcount_tot OKI_MEASURE_18,
227 oset10.hcount_rate_tot OKI_MEASURE_19
228 FROM
229 (
230 SELECT '|| l_view_by_col || ',
231 nvl(oset05.booked_val,0) booked_val,
232 nvl(oset05.starting_val,0) start_val,
233 oset05.booked_val/decode(oset05.starting_val,0,NULL,oset05.starting_val) val_rate,
234 nvl(oset05.booked_lcount,0) booked_lcount,
235 nvl(oset05.starting_lcount,0) start_lcount,
236 oset05.booked_lcount /decode( oset05.starting_lcount,0,NULL,oset05.starting_lcount) lcount_rate,
237 nvl(oset05.booked_hcount,0) booked_hcount,
238 nvl(oset05.starting_hcount,0) start_hcount,
239 oset05.booked_hcount /decode( oset05.starting_hcount,0,NULL,oset05.starting_hcount) hcount_rate,
240 nvl(oset05.booked_val_tot,0) booked_val_tot,
241 nvl(oset05.starting_val_tot,0) start_val_tot,
242 oset05.booked_val_tot/decode(oset05.starting_val_tot,0,NULL,oset05.starting_val_tot) val_rate_tot,
243 nvl(oset05.booked_lcount_tot,0) booked_lcount_tot,
244 nvl(oset05.starting_lcount_tot,0) start_lcount_tot,
245 oset05.booked_lcount_tot /decode( oset05.starting_lcount_tot,0,NULL,oset05.starting_lcount_tot) lcount_rate_tot,
246 nvl(oset05.booked_hcount_tot,0) booked_hcount_tot,
247 nvl(oset05.starting_hcount_tot,0) start_hcount_tot,
248 oset05.booked_hcount_tot /decode( oset05.starting_hcount_tot,0,NULL,oset05.starting_hcount_tot) hcount_rate_tot
249 FROM
250 ('||
251 get_book_start_2way_sql( l_book_where,
252 l_start_where,
253 l_viewby_col_special,
254 l_view_by_col,
255 l_cur_suffix )
256 || l_VIEWBY_RANK_ORDER;
257
258 x_custom_sql := '/* OKI_DBI_SRM_BTS_RATE_DRPT */'||l_query;
259 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
260
261 END get_rates_table_sql;
262 -----------------------------------------------------------
263
264 FUNCTION get_book_start_2way_sql (
265 l_book_where IN VARCHAR2
266 , l_start_where IN VARCHAR2
267 , l_viewby_col_special IN VARCHAR2
268 , l_view_by_col IN VARCHAR2
269 , l_cur_suffix IN VARCHAR2)
270 RETURN VARCHAR2
271 IS
272 l_select varchar2(32767);
273 l_query1 varchar2(32767);
274 l_query2 varchar2(32767);
275 l_join_column1 varchar2(50);
276 l_join_column2 varchar2(50);
277 l_query varchar2(32767);
278 BEGIN
279 -- l_select := l_view_by_col||',
280 l_select := 'sum(booked_val) booked_val,
281 sum(booked_hcount) booked_hcount,
282 sum(booked_lcount) booked_lcount,
283 sum(booked_val_tot) booked_val_tot,
284 sum(booked_hcount_tot) booked_hcount_tot,
285 sum(booked_lcount_tot) booked_lcount_tot,
286 sum(starting_val) starting_val,
287 sum(starting_hcount) starting_hcount,
288 sum(starting_lcount) starting_lcount,
289 sum(starting_val_tot) starting_val_tot,
290 sum(starting_hcount_tot) starting_hcount_tot,
291 sum(starting_lcount_tot) starting_lcount_tot ';
292
293 l_query1 :=
294 ' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
295 NVL(SUM(fact.price_nego_' || l_cur_suffix || '),0) booked_val,
296 NVL(COUNT(distinct(fact.chr_id)),0) booked_hcount,
297 NVL(COUNT(distinct(fact.cle_id)),0) booked_lcount,
298 NVL(SUM(SUM(fact.price_nego_' || l_cur_suffix || ')) over (),0) booked_val_tot,
299 NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) booked_hcount_tot,
300 NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) booked_lcount_tot,
301 to_number(null) starting_val,
302 to_number(null) starting_hcount,
306 to_number(null) starting_lcount_tot '||
303 to_number(null) starting_lcount,
304 to_number(null) starting_val_tot,
305 to_number(null) starting_hcount_tot,
307 l_book_where;
308
309 l_query2 :=
310 ' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
311 to_number(null) booked_val,
312 to_number(null) booked_hcount,
313 to_number(null) booked_lcount,
314 to_number(null) booked_val_tot,
315 to_number(null) booked_hcount_tot,
316 to_number(null) booked_lcount_tot,
317 NVL(SUM(fact.price_nego_' || l_cur_suffix || '),0) starting_val,
318 NVL(COUNT(distinct(fact.chr_id)),0) starting_hcount,
319 NVL(COUNT(distinct(fact.cle_id)),0) starting_lcount,
320 NVL(SUM(SUM(fact.price_nego_' || l_cur_suffix || ')) over (),0) starting_val_tot,
321 NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) starting_hcount_tot,
322 NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) starting_lcount_tot '||
323 l_start_where;
324
325 l_join_column1 := l_view_by_col;
326 l_join_column2 := l_view_by_col;
327
328 l_query := oki_dbi_util_pvt.two_way_join (l_select, l_query1, l_query2,l_join_column1, l_join_column1);
329 RETURN l_query;
330
331 END get_book_start_2way_sql;
332
333 /*******************************************************************************
334 Procedure: get_table_sql
335 Description: Procedure to retrieve the sql statement for
336 the Booking to Renewals Activity Portlet/Report
337 *******************************************************************************/
338
339 PROCEDURE get_table_sql(
340 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
341 x_custom_sql OUT NOCOPY VARCHAR2,
342 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
343 IS
344 l_query VARCHAR2 (10000);
345 l_view_by VARCHAR2 (120);
346 l_view_by_col VARCHAR2 (120);
347 l_as_of_date DATE;
348 l_prev_as_of_date DATE;
349 l_xtd1 VARCHAR2 (10);
350 l_xtd2 VARCHAR2 (10);
351 l_comparison_type VARCHAR2 (1);
352 l_period_type VARCHAR2(10);
353 l_nested_pattern NUMBER;
354 l_cur_suffix VARCHAR2 (2);
355 l_custom_sql VARCHAR2 (10000);
356 l_where_clause VARCHAR2 (2000);
357 l_filter_where VARCHAR2 (240);
358 l_mv1 VARCHAR2 (2000);
359 l_mv2 VARCHAR2 (2000);
360 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
361 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
362 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
363 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
364 l_to_date_xed VARCHAR2 (3);
365 l_to_date_xtd VARCHAR2 (3);
366 l_viewby_rank_where VARCHAR2(32767);
367 l_sql VARCHAR2(32767);
368 l_temp LONG;
369 BEGIN
370 /* x_custom_output := bis_query_attributes_tbl();
371 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
372
373 DEBUG - brrao added
374 -- OKI_DBIDEBUG_PVT.check_portal_param('OKI_DBI_SRM_KAPILT',p_param);
375
376 */
377 l_to_date_xed := 'XED';
378 l_to_date_xtd := 'XTD';
379 l_comparison_type := 'Y';
380 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
381 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
382 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
383 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
384
385 oki_dbi_util_pvt.process_parameters (p_param => p_param
386 , p_view_by => l_view_by
387 , p_view_by_col_name => l_view_by_col
391 , p_prev_as_of_date => l_prev_as_of_date
388 , p_comparison_type => l_comparison_type
389 , p_xtd => l_xtd1
390 , p_as_of_date => l_as_of_date
392 , p_cur_suffix => l_cur_suffix
393 , p_nested_pattern => l_nested_pattern
394 , p_where_clause => l_where_clause
395 , p_mv => l_mv1
396 , p_join_tbl => l_join_tbl
397 , p_period_type => l_period_type
398 , p_trend => 'N'
399 , p_func_area => 'OKI'
400 , p_version => '6.0'
401 , p_role => NULL
402 , p_mv_set => 'SRM_SG_71'
403 , p_rg_where => 'Y');
404
405 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
406 , p_col_name => 'g_r_amt_' || l_cur_suffix
407 , p_alias_name => 'booked'
408 , p_to_date_type => l_to_date_xtd);
409 l_mv_tbl.extend;
410 l_mv_tbl(1).mv_name := l_mv1;
411 l_mv_tbl(1).mv_col := l_col_tbl1;
412 l_mv_tbl(1).mv_where := l_where_clause;
413 l_mv_tbl(1).in_join_tbls := NULL;
414 l_mv_tbl(1).use_grp_id := 'N';
415
416 oki_dbi_util_pvt.process_parameters (p_param => p_param
417 , p_view_by => l_view_by
418 , p_view_by_col_name => l_view_by_col
419 , p_comparison_type => l_comparison_type
420 , p_xtd => l_xtd2
421 , p_as_of_date => l_as_of_date
422 , p_prev_as_of_date => l_prev_as_of_date
423 , p_cur_suffix => l_cur_suffix
424 , p_nested_pattern => l_nested_pattern
425 , p_where_clause => l_where_clause
426 , p_mv => l_mv2
427 , p_join_tbl => l_join_tbl
428 , p_period_type => l_period_type
429 , p_trend => 'N'
430 , p_func_area => 'OKI'
431 , p_version => '6.0'
432 , p_role => NULL
433 , p_mv_set => 'SRM_ST_71'
434 , p_rg_where => 'Y');
435
436
437 /*
438 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
439 , p_col_name => 'g_r_amt_n_' || l_cur_suffix
440 , p_alias_name => 'booked_n'
444 , p_col_name => 'g_r_amt_t_' || l_cur_suffix
441 , p_to_date_type => l_to_date_xtd);
442
443 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
445 , p_alias_name => 'booked_t'
446 , p_to_date_type => l_to_date_xtd);
447
448 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
449 , p_col_name => 's_r_amt_n_' || l_cur_suffix
450 , p_alias_name => 'started_n'
451 , p_to_date_type => l_to_date_xtd);
452
453 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
454 , p_col_name => 's_r_amt_t_' || l_cur_suffix
455 , p_alias_name => 'started_t'
456 , p_to_date_type => l_to_date_xtd);
457
458 */
459 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
460 , p_col_name => 's_r_amt_' || l_cur_suffix
461 , p_alias_name => 'started'
462 , p_to_date_type => l_to_date_xtd);
463
464 /* Additional filter needed to avoid displaying records queried due to total values at node */
465
466
467
468 l_mv_tbl.extend;
469 l_mv_tbl(2).mv_name := l_mv2;
470 l_mv_tbl(2).mv_col := l_col_tbl2;
471 l_mv_tbl(2).mv_where := l_where_clause;
472 l_mv_tbl(2).in_join_tbls := NULL;
473 l_mv_tbl(2).use_grp_id := 'N';
474
475 l_filter_where := ' ( ABS(oki_measure_1) + ABS(oki_measure_2) ) <> 0';
476
477 l_query :=
478 poa_dbi_template_pkg.union_all_status_sql
479 (p_mv => l_mv_tbl,
480 p_join_tables => l_join_tbl,
481 p_use_windowing => 'Y',
482 p_paren_count => 5,
483 p_filter_where => NULL,
484 p_generate_viewby => 'N');
485 l_viewby_rank_where := ' WHERE ' || l_filter_where || ')oset , '||
486 poa_dbi_template_pkg.get_viewby_rank_clause ( p_join_tables => l_join_tbl
487 , p_use_windowing => 'Y'
488 );
489
490 l_query :=
491 get_table_sel_clause (l_view_by, l_view_by_col) ||
492 l_query||l_viewby_rank_where;
493 /* poa_dbi_template_pkg.status_sql (
494 -- p_nested_pattern => l_nested_pattern
495 p_fact_name => l_mv
496 , p_where_clause => l_where_clause
497 , p_filter_where => l_filter_where
498 , p_join_tables => l_join_tbl
499 , p_use_windowing => 'Y'
500 , p_col_name => l_col_tbl
501 , p_use_grpid => 'N'
502 , p_paren_count => 6);
503 */
504 x_custom_sql := '/* OKI_DBI_SRM_BTS_RATIO_RPT */' || l_query;
505 oki_dbi_util_pvt.get_custom_status_binds(x_custom_output);
506 /* DEBUG - brrao added */
507 -- OKI_DBIDEBUG_PVT.check_portal_value('OKI_DBI_SRM_KAPILT','SQL',x_custom_sql);
508 -- COMMIT;
509
510 END get_table_sql;
511
512 /*******************************************************************************
513 Procedure: get_trend_sql
514 Description: Procedure to retrieve the sql statement for
518 PROCEDURE get_trend_sql (
515 the Booking to Renewals Ratio Trend Portlet/Report
516 *******************************************************************************/
517
519 p_param IN bis_pmv_page_parameter_tbl
520 , x_custom_sql OUT NOCOPY VARCHAR2
521 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
522 IS
523 l_query VARCHAR2 (10000);
524 l_view_by VARCHAR2 (120);
525 l_view_by_col VARCHAR2 (120);
526 l_as_of_date DATE;
527 l_prev_as_of_date DATE;
528 l_xtd1 VARCHAR2 (10);
529 l_xtd2 VARCHAR2 (10);
530 l_comparison_type VARCHAR2 (1);
531 l_period_type VARCHAR2(10);
532 l_nested_pattern NUMBER;
533 l_cur_suffix VARCHAR2 (2);
534 l_custom_sql VARCHAR2 (10000);
535 l_where_clause VARCHAR2 (2000);
536 l_mv1 VARCHAR2 (2000);
537 l_mv2 VARCHAR2 (2000);
538 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
539 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
540 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
541 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
542 l_to_date_xed VARCHAR2 (3);
543 l_to_date_xtd VARCHAR2 (3);
544 BEGIN
545 /*
546 x_custom_output := bis_query_attributes_tbl ();
547 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
548 */
549 l_to_date_xed := 'XED';
550 l_to_date_xtd := 'XTD';
551 l_comparison_type := 'Y';
552 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
553 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
554 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
555
556 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
557
558 oki_dbi_util_pvt.process_parameters ( p_param => p_param
559 , p_view_by => l_view_by
560 , p_view_by_col_name => l_view_by_col
561 , p_comparison_type => l_comparison_type
562 , p_xtd => l_xtd1
563 , p_as_of_date => l_as_of_date
564 , p_prev_as_of_date => l_prev_as_of_date
565 , p_cur_suffix => l_cur_suffix
566 , p_nested_pattern => l_nested_pattern
567 , p_where_clause => l_where_clause
568 , p_mv => l_mv1
569 , p_join_tbl => l_join_tbl
570 , p_period_type => l_period_type
571 , p_trend => 'Y'
572 , p_func_area => 'OKI'
573 , p_version => '6.0'
574 , p_role => NULL
575 , p_mv_set => 'SRM_SG_71'
576 , p_rg_where => 'Y');
577
578
579
580
581 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
582 , p_col_name => 'g_r_amt_' || l_cur_suffix
583 , p_alias_name => 'booked'
584 , p_grand_total => 'N'
585 , p_to_date_type => l_to_date_xtd);
586
587 l_mv_tbl.extend;
588 l_mv_tbl(1).mv_name := l_mv1;
589 l_mv_tbl(1).mv_col := l_col_tbl1;
590 l_mv_tbl(1).mv_where := l_where_clause;
591 l_mv_tbl(1).in_join_tbls := NULL;
592 l_mv_tbl(1).use_grp_id := 'N';
593 l_mv_tbl(1).mv_xtd := l_xtd1;
594
595 oki_dbi_util_pvt.process_parameters ( p_param => p_param
596 , p_view_by => l_view_by
597 , p_view_by_col_name => l_view_by_col
598 , p_comparison_type => l_comparison_type
599 , p_xtd => l_xtd2
600 , p_as_of_date => l_as_of_date
601 , p_prev_as_of_date => l_prev_as_of_date
602 , p_cur_suffix => l_cur_suffix
603 , p_nested_pattern => l_nested_pattern
604 , p_where_clause => l_where_clause
605 , p_mv => l_mv2
606 , p_join_tbl => l_join_tbl
607 , p_period_type => l_period_type
608 , p_trend => 'Y'
609 , p_func_area => 'OKI'
610 , p_version => '6.0'
611 , p_role => NULL
612 , p_mv_set => 'SRM_ST_71'
613 , p_rg_where => 'Y');
614
615 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
616 , p_col_name => 's_r_amt_' || l_cur_suffix
617 , p_alias_name => 'started'
621
618 , p_grand_total => 'N'
619 , p_to_date_type => l_to_date_xtd);
620
622 l_mv_tbl.extend;
623 l_mv_tbl(2).mv_name := l_mv2;
624 l_mv_tbl(2).mv_col := l_col_tbl2;
625 l_mv_tbl(2).mv_where := l_where_clause;
626 l_mv_tbl(2).in_join_tbls := NULL;
627 l_mv_tbl(2).use_grp_id := 'N';
628 l_mv_tbl(2).mv_xtd := l_xtd2;
629
630 l_query := get_trend_sel_clause || ' from '
631 ||poa_dbi_template_pkg.union_all_trend_sql
632 (p_mv => l_mv_tbl,
633 p_comparison_type => 'R',
634 p_filter_where => NULL);
635
636 /*poa_dbi_template_pkg.trend_sql ( l_xtd
637 , l_comparison_type
638 , l_mv
639 , l_where_clause
640 , l_col_tbl
641 , 'R');
642 /* , l_as_of_date
643 , l_prev_as_of_date
644 , l_nested_pattern);*/
645 x_custom_sql := '/* OKI_DBI_SRM_BTS_RATIO_G */'||l_query;
646 -- x_custom_sql := 'select 1 VIEWBY, 1 OKI_MEASURE_1, 2 OKI_MEASURE_2, 2 OKI_MEASURE_3 from dual';
647 oki_dbi_util_pvt.get_custom_trend_binds (l_xtd1
648 , l_comparison_type
649 , x_custom_output);
650
651 END get_trend_sql;
652
653 FUNCTION get_col_name (p_dim_name VARCHAR2)
654 RETURN VARCHAR2
655 IS
656 l_col_name VARCHAR2 (100);
657 BEGIN
658 l_col_name := (CASE p_dim_name
659 WHEN 'ORGANIZATION+JTF_ORG_SALES_GROUP'
660 THEN 'prg_id'
661 ELSE ''
662 END);
663 RETURN l_col_name;
664 END get_col_name;
665
666 FUNCTION get_trend_sel_clause
667 RETURN VARCHAR2
668 IS
669 l_sel_clause VARCHAR2 (10000);
670 BEGIN
671
672 /* Removed NVL clauses from the query because of bug 3123830--ARUN */
673
674 l_sel_clause :=
675 'SELECT cal_NAME AS VIEWBY
676 , (uset.p_booked) / DECODE (uset.p_started, 0, NULL, uset.p_started)
677 oki_measure_1
678 , (uset.c_booked) / DECODE (uset.c_started, 0, NULL, uset.c_started)
679 oki_measure_2
680 , (uset.c_booked / DECODE (uset.c_started, 0, NULL, uset.c_started))
681 -(uset.p_booked / DECODE (uset.p_started, 0, NULL, uset.p_started))
682 oki_measure_3';
683 RETURN l_sel_clause;
684 END get_trend_sel_clause;
685
686 /*****************************************************************
687 Booking to Renewal Activity SQL Select clause
688
689 *******************************************************************/
690
691 FUNCTION get_table_sel_clause (
692 p_view_by_dim IN VARCHAR2
693 , p_view_by_col IN VARCHAR2)
694 RETURN VARCHAR2
695 IS
696 l_sel_clause VARCHAR2 (10000);
697 l_bookings_url VARCHAR2(300);
698 l_prodcat_url VARCHAR2(300);
699 l_viewby_select VARCHAR2(10000);
700 l_url_select VARCHAR2(10000);
701 BEGIN
702
703 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
704 l_bookings_url := '''pFunctionName=OKI_DBI_SRM_RSBK_DTL_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP''';
705
706 -- l_rate_url := '''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP''';
707
708 IF(p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT')
709 THEN
710 l_prodcat_url :=
711 ' decode(leaf_node_flag,''Y''
712 , ''pFunctionName=OKI_DBI_SRM_BTS_RATIO_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM''
713 ,''pFunctionName=OKI_DBI_SRM_BTS_RATIO_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_4 ';
714 ELSE
715 l_prodcat_url := 'NULL OKI_DYNAMIC_URL_4 ';
716 END IF;
717
718 l_viewby_select := l_viewby_select ||
719 ', OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_3, '|| l_prodcat_url || '
720 ,OKI_MEASURE_1 ,OKI_MEASURE_2 ,OKI_CALC_ITEM1 ,OKI_CALC_ITEM2
721 ,OKI_CALC_ITEM3 ,OKI_CALC_ITEM4 ,OKI_CALC_ITEM5 ,OKI_CALC_ITEM6,oki_measure_3
722 ,OKI_MEASURE_4, oki_measure_5, OKI_CALC_ITEM7, OKI_CALC_ITEM8,OKI_CALC_ITEM17
723 ,OKI_CALC_ITEM18, oki_measure_15, OKI_MEASURE_18, OKI_MEASURE_19
724 FROM
725 (SELECT
726 rank() over (&ORDER_BY_CLAUSE nulls last,
727 '||p_view_by_col||') - 1 rnk
728 ,'||p_view_by_col||',OKI_DYNAMIC_URL_1
729 ,OKI_DYNAMIC_URL_3 ,OKI_MEASURE_1 ,OKI_MEASURE_2
730 ,OKI_CALC_ITEM1 ,OKI_CALC_ITEM2 ,OKI_CALC_ITEM3
731 ,OKI_CALC_ITEM4 ,OKI_CALC_ITEM5 ,OKI_CALC_ITEM6,oki_measure_3,OKI_MEASURE_4
732 ,'||POA_DBI_UTIL_PKG.rate_clause('oki_measure_2','oki_calc_item4') || ' oki_measure_5
733 ,OKI_CALC_ITEM7, OKI_CALC_ITEM8,OKI_CALC_ITEM17, OKI_CALC_ITEM18,
734 SUM( '||POA_DBI_UTIL_PKG.rate_clause('oki_measure_2','oki_calc_item4') || ') over() oki_measure_15
735 ,OKI_CALC_ITEM8 OKI_MEASURE_18, OKI_CALC_ITEM18 OKI_MEASURE_19
736 FROM ( ';
737
738 -- ,''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'' OKI_DYNAMIC_URL_2
739 -- , decode(resource_id,-999,'''','|| l_bookings_url || ') OKI_DYNAMIC_URL_3 ';
740
741 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
742 THEN
743 l_url_select :=
747 THEN
744 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_BTS_RATIO_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1' ||
745 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_bookings_url||')) OKI_DYNAMIC_URL_3 ';
746 ELSIF(p_view_by_dim = 'ITEM+ENI_ITEM')
748 l_url_select :=
749 ' SELECT NULL OKI_DYNAMIC_URL_1
750 , '||l_bookings_url||' OKI_DYNAMIC_URL_3 ';
751 ELSE
752
753 /* brrao added for documentation */
754 -- OKI_DYNAMIC_URL_1 (sales group URL)
755 -- OKI_DYNAMIC_URL_2 (Booking to Renewal Ratio DD URL link)
756 -- OKI_DYNAMIC_URL_3 (Bookings Column URL)
757 -- OKI_DYNAMIC_URL_4 (product Category URL if present)
758 -- OKI_MEASURE_1 (Renewals Value)
759 -- OKI_MEASURE_2 (Booked Value Column)
760 -- OKI_CALC_ITEM1 (Book to Renewal Ratio)
761 -- OKI_CALC_ITEM2 (Book to Renewal Ratio Change)
762 -- OKI_CALC_ITEM3 (Renewals Value TOTAL)
763 -- OKI_CALC_ITEM4 (Booked Value TOTAL)
764 -- OKI_CALC_ITEM5 (Book to Renewal Ratio TOTAL)
765 -- OKI_CALC_ITEM6 (Book to Renewal Ratio Change TOTAL)
766 -- OKI_MEASURE_3 (Book to Renewal Ratio - Current Graph) not used superflous column --
767 -- OKI_MEASURE_4 (Book to Renewal Ratio - Prior Graph)
768 -- OKI_CALC_ITEM7 (Book to Renewal Ratio - Current KPI)
769 -- OKI_CALC_ITEM17 (Book to Renewal Ratio - Current TOTAL KPI)
770 -- OKI_CALC_ITEM8 (Book to Renewal Ratio - Prior KPI)
771 -- OKI_CALC_ITEM18 (Book to Renewal Ratio - Prior TOTAL KPI)
772 -- OKI_MEASURE_18 (Book to Renewal Ratio - Prior KPI )
773 -- OKI_MEASURE_19 (Book to Renewal Ratio - Prior TOTAL for measure 18)
774
775 l_url_select :=
776 ' SELECT NULL OKI_DYNAMIC_URL_1
777 , '''' OKI_DYNAMIC_URL_3 ';
778 END IF;
779 l_sel_clause := l_viewby_select || l_url_select ||
780 ' ,'|| p_view_by_col ||
781 ' , oset20.rnwl_started oki_measure_1
782 , oset20.rnwl_booked oki_measure_2
783 , oset20.c_rnwl_rate oki_calc_item1
784 , oset20.rnwl_rate_chg oki_calc_item2
785 , oset20.rnwl_started_tot oki_calc_item3
786 , oset20.rnwl_booked_tot oki_calc_item4
787 , oset20.rnwl_rate_tot oki_calc_item5
788 , oset20.rnwl_rate_chg_tot oki_calc_item6
789 , oset20.c_rnwl_rate oki_measure_3
790 , oset20.p_rnwl_rate oki_measure_4
791 ,'||POA_DBI_UTIL_PKG.rate_clause('oset20.rnwl_booked','oset20.rnwl_booked_tot') || ' oki_measure_5
792 , oset20.c_rnwl_rate oki_calc_item7
793 , oset20.rnwl_rate_tot oki_calc_item17
794 , oset20.p_rnwl_rate oki_calc_item8
795 , oset20.p_rnwl_rate_tot oki_calc_item18
796 FROM
797 (SELECT '
798 || p_view_by_col ||'
799 , oset15.rnwl_booked
800 , oset15.rnwl_started
801 , oset15.c_rnwl_rate
802 , oset15.p_rnwl_rate
803 , oset15.rnwl_rate_chg
804 , oset15.rnwl_booked_tot
805 , oset15.rnwl_started_tot
806 , oset15.rnwl_rate_tot
807 , oset15.p_rnwl_rate_tot
808 , oset15.rnwl_rate_chg_tot
809 FROM
810 (SELECT '|| p_view_by_col || '
811 , oset10.c_rnwl_booked rnwl_booked
812 , oset10.c_rnwl_started rnwl_started
813 , oset10.c_rnwl_rate c_rnwl_rate
814 , oset10.p_rnwl_rate p_rnwl_rate
815 ,'||OKI_DBI_UTIL_PVT.change_clause('oset10.c_rnwl_Rate',
816 'oset10.p_rnwl_Rate','P') || 'rnwl_rate_chg
817 , oset10.c_rnwl_booked_tot rnwl_booked_tot
818 , oset10.c_rnwl_started_tot rnwl_started_tot
819 , oset10.c_rnwl_Rate_total rnwl_rate_tot
820 , oset10.p_rnwl_Rate_total p_rnwl_rate_tot
821 ,'||OKI_DBI_UTIL_PVT.change_clause('oset10.c_rnwl_Rate_total',
822 'oset10.p_rnwl_Rate_total','P') || 'rnwl_rate_chg_tot
823 FROM
824 (SELECT oset05.' || p_view_by_col || '
825 , nvl(oset05.c_started,0) c_rnwl_started
826 , nvl(oset05.c_booked,0) c_rnwl_booked
827 , nvl(oset05.p_started,0) p_rnwl_started
828 , nvl(oset05.p_booked,0) p_rnwl_booked
829 , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.c_booked,0)','oset05.c_started','NP') || 'c_rnwl_Rate
830 , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.p_booked,0)','oset05.p_started','NP') || 'p_rnwl_Rate
831 , nvl(oset05.c_booked_total,0) c_rnwl_booked_tot
832 , nvl(oset05.p_booked_total,0) p_rnwl_booked_tot
833 , nvl(oset05.c_started_total,0) c_rnwl_started_tot
834 , nvl(oset05.p_started_total,0) p_rnwl_started_tot
835 , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.c_booked_total,0)', 'oset05.c_started_total','NP') || 'c_rnwl_Rate_total
836 , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.p_booked_total,0)', 'oset05.p_started_total','NP') || 'p_rnwl_Rate_total from (';
837
838
839 RETURN l_sel_clause;
840 END get_table_sel_clause;
841 END OKI_DBI_SRM_BKRNWL_PVT;