[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_SRM_PRNWL_PVT
Source
1 PACKAGE BODY OKI_DBI_SRM_PRNWL_PVT AS
2 /* $Header: OKIIPRNB.pls 120.3 2006/05/18 04:26:55 asparama noship $ */
3
4 FUNCTION get_table_sel_clause (
5 p_view_by_dim IN VARCHAR2
6 , p_view_by_col IN VARCHAR2)
7 RETURN VARCHAR2;
8
9 FUNCTION get_bookings_sel_clause (
10 p_cur_suffix IN VARCHAR2
11 , p_period_type_code IN VARCHAR2)
12 RETURN VARCHAR2;
13
14
15 --dbi7.0
16 /* FUNCTION get_bkngs_by_cust_sel_clause
17 RETURN VARCHAR2;
18 */
19
20 /*******************************************************************************
21 Function: get_table_sql
22 Description: Function to get Period Renewals Summary Report DBI 6.0
23 *******************************************************************************/
24
25
26 PROCEDURE get_table_sql (
27
28 p_param IN bis_pmv_page_parameter_tbl
29 , x_custom_sql OUT NOCOPY VARCHAR2
30 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
31 IS
32
33 l_query VARCHAR2(32767);
34 l_view_by VARCHAR2(120);
35 l_view_by_col VARCHAR2(120);
36 l_xtd1 VARCHAR2(10);
37 l_xtd2 VARCHAR2(10);
38 l_as_of_date DATE;
39 l_prev_as_of_date DATE;
40 l_comparison_type VARCHAR2(1);
41
42 l_period_type VARCHAR2(10);
43 l_nested_pattern NUMBER;
44 l_cur_suffix VARCHAR2(2);
45 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
46 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
47 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
48 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
49
50 l_where_clause1 VARCHAR2(2000);
51 l_where_clause2 VARCHAR2(2000);
52 l_filter_where VARCHAR2(240);
53 l_mv1 VARCHAR2(2000);
54 l_mv2 VARCHAR2(2000);
55
56
57 l_to_date_xed VARCHAR2(3);
58 l_to_date_xtd VARCHAR2(3);
59 l_to_date_ytd VARCHAR2(3);
60 l_to_date_itd VARCHAR2(3);
61
62 l_period_ytd_sql VARCHAR2(32767);
63 l_viewby_rank_where VARCHAR2(32767);
64 l_ytd_sel_clause VARCHAR2(32767);
65
66 l_mv_1 VARCHAR2(100);
67 l_mv_2 VARCHAR2(100);
68
69 l_ytd_sel1 VARCHAR2(5000);
70
71 l_ytd_sel2 VARCHAR2(5000);
72
73 l_pcflag VARCHAR2(500);
74 l_ouflag VARCHAR2(500);
75 l_ccflag VARCHAR2(500);
76 l_umark VARCHAR2(500);
77
78 l_sg VARCHAR2(32000);
79 l_org VARCHAR2(32000);
80 l_prod VARCHAR2(32000);
81 l_prod_cat VARCHAR2(32000);
82
83 BEGIN
84
85 l_to_date_xed := 'XED';
86 l_to_date_xtd := 'XTD';
87 l_comparison_type := 'Y';
88 l_to_date_ytd := 'YTD';
89 l_to_date_itd := 'ITD';
90 l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl ();
91 l_col_tbl1 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
92 l_col_tbl2 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
93 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
94 l_ytd_sel_clause := '';
95
96 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
97 , p_view_by => l_view_by
98 , p_view_by_col_name => l_view_by_col
99 , p_comparison_type => l_comparison_type
100 , p_xtd => l_xtd1
101 , p_as_of_date => l_as_of_date
102 , p_prev_as_of_date => l_prev_as_of_date
103 , p_cur_suffix => l_cur_suffix
104 , p_nested_pattern => l_nested_pattern
105 , p_where_clause => l_where_clause1
106 , p_mv => l_mv1
107 , p_join_tbl => l_join_tbl
108 , p_period_type => l_period_type
109 , p_trend => 'N'
110 , p_func_area => 'OKI'
111 , p_version => '6.0'
112 , p_role => NULL
113 , p_mv_set => 'SRM_ST_71'
114 , p_rg_where => 'Y');
115
116 -- Populate col table with regular columns
117
118 -- Period Renewal node
119
120 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
121 , p_col_name => 's_r_amt_' || l_cur_suffix
122 , p_alias_name => 'Scr'
123 , p_to_date_type => l_to_date_xed);
124
125
126
127 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
128 , p_col_name => 'c_scr_amt_' || l_cur_suffix
129 , p_alias_name => 'CScr'
130 , p_to_date_type => l_to_date_xed);
131
132
133 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
134 , p_col_name => 's_gpr_amt_'||l_period_type||'_' || l_cur_suffix
135 , p_alias_name => 'ScGpr'
136 , p_to_date_type => l_to_date_xed);
137
138
139
140 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
141 , p_col_name => 's_gpo_amt_'||l_period_type||'_' || l_cur_suffix
142 , p_alias_name => 'ScGpo'
143 , p_to_date_type => l_to_date_xed);
144
145
146
147 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
148 , p_view_by => l_view_by
149 , p_view_by_col_name => l_view_by_col
150 , p_comparison_type => l_comparison_type
151 , p_xtd => l_xtd1
152
153 , p_as_of_date => l_as_of_date
154 , p_prev_as_of_date => l_prev_as_of_date
155 , p_cur_suffix => l_cur_suffix
156 , p_nested_pattern => l_nested_pattern
157 , p_where_clause => l_where_clause2
158 , p_mv => l_mv2
159 , p_join_tbl => l_join_tbl
160 , p_period_type => l_period_type
161 , p_trend => 'N'
162 , p_func_area => 'OKI'
163 , p_version => '6.0'
164 , p_role => NULL
165 , p_mv_set => 'SRM_SG_71'
166
167 , p_rg_where => 'Y');
168
169 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
170 , p_col_name => 'g_scr_amt_'||l_period_type||'_' || l_cur_suffix
171 , p_alias_name => 'ScGr'
172 , p_to_date_type => l_to_date_xtd);
173
174
175 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
176 , p_col_name => 'g_sco_amt_'||l_period_type||'_' || l_cur_suffix
177 , p_alias_name => 'ScGo'
178 , p_to_date_type => l_to_date_xtd);
179
180
181 l_mv_tbl.extend;
182 l_mv_tbl(1).mv_name := l_mv1;
183 l_mv_tbl(1).mv_col := l_col_tbl1;
184 l_mv_tbl(1).mv_where := l_where_clause1;
185 l_mv_tbl(1).in_join_tbls := NULL;
186 l_mv_tbl(1).use_grp_id := 'N';
187
188 l_mv_tbl.extend;
189 l_mv_tbl(2).mv_name := l_mv2;
190 l_mv_tbl(2).mv_col := l_col_tbl2;
191 l_mv_tbl(2).mv_where := l_where_clause2;
192 l_mv_tbl(2).in_join_tbls := NULL;
193 l_mv_tbl(1).use_grp_id := 'N';
194
195
196
197 /* Additional filter needed to avoid displaying records queried due to total values at node */
198
199 l_filter_where := ' ( ABS(oki_measure_1) + ABS(oki_measure_2) + ABS(oki_measure_5) ) <> 0 ';
200 -- l_filter_where := ' 1=1 ';
201
202
203 /* Building the query */
204
205 -- l_query := get_table_sel_clause (l_view_by, l_view_by_col)
206 -- || ' FROM '
207 -- || l_period_ytd_sql;
208
209 l_query := get_table_sel_clause (l_view_by, l_view_by_col) -- in poa : l_join_tbl
210
211 || ' from (
212 ' || poa_dbi_template_pkg.union_all_status_sql
213 (p_mv => l_mv_tbl,
214 p_join_tables => l_join_tbl,
215 p_use_windowing => 'Y',
216 p_paren_count => 7,
217 p_filter_where => l_filter_where );
218
219
220
221
222 x_custom_sql := '/* New OKI_DBI_SRM_PRNWL_SUM_RPT */' || l_query;
223 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
224
225 END get_table_sql;
226
227 /*
228
229
230 Period Renewal Summary Select clause
231
232 */
233
234 FUNCTION get_table_sel_clause (
235 p_view_by_dim IN VARCHAR2
236 , p_view_by_col IN VARCHAR2)
237 RETURN VARCHAR2
238
239 IS
240
241 l_sel_clause VARCHAR2 (32767);
242 l_bookings_url VARCHAR2(300);
243
244 l_prodcat_url VARCHAR2(300);
245 -- l_rrate_url VARCHAR2(300);
246 l_viewby_select VARCHAR2(32767);
247 l_url_select VARCHAR2(32767);
248
249 BEGIN
250
251
252 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
253
254 -- Bookings URL when view by is Salesrep
255
256 l_bookings_url := '''pFunctionName=OKI_DBI_SRM_PRNWL_BKING_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY='||p_view_by_dim||'''';
257
258
259 --l_bookings_url := '''pFunctionName=OKI_DBI_SRM_PRNWL_BKING_RPT''';
260
261 -- l_rrate_url := '''pFunctionName=OKI_DBI_SRM_PRNWL_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY='||p_view_by_dim||'''';
262
263 --l_rrate_url :='''OKI_DBI_SRM_PRNWL_RATE_DRPT''';
264
265 IF(p_view_by_dim = 'ITEM+ENI_ITEM_PROD_LEAF_CAT')
266 THEN
267 l_prodcat_url :=
268 ' decode(leaf_node_flag,''Y''
269 , ''pFunctionName=OKI_DBI_SRM_PRNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM''
270 ,''pFunctionName=OKI_DBI_SRM_PRNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_4 ';
271
272 ELSE
273 l_prodcat_url := ''''' OKI_DYNAMIC_URL_4 ';
274 END IF;
275
276
277
278 l_viewby_select := l_viewby_select ||
279
280 ',OKI_DYNAMIC_URL_1
281 ,OKI_DYNAMIC_URL_2
282 ,' ||l_prodcat_url || '
283 ,oki_measure_1
284 ,oki_measure_2
285
286 ,oki_measure_3
287 ,oki_measure_4
288 ,oki_measure_5
289 ,oki_measure_6
290 ,oki_measure_7
291 ,oki_measure_8
292 ,oki_measure_11
293 ,oki_measure_12
294 ,oki_measure_13
295 ,oki_measure_14
296 ,oki_measure_15
297 ,oki_measure_16
298 ,oki_measure_17
299
300 ,oki_measure_18
301 ,oki_measure_23
302 ,oki_measure_26
303 ,oki_calc_item1
304 ,oki_calc_item2
305 ,oki_calc_item3
306 ,oki_calc_item4
307 ,oki_calc_item5
308 ,oki_calc_item6
309 ,oki_calc_item7
310 ,oki_calc_item8
311 ,oki_calc_item11
312 ,oki_calc_item12
313
314 ,oki_calc_item13
315 ,oki_calc_item14
316 ,oki_calc_item15
317 ,oki_calc_item16
318 ,oki_calc_item17
319 ,oki_calc_item18
320
321 FROM (
322 SELECT
323 rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk
324 ,'||p_view_by_col||'
325 ,OKI_DYNAMIC_URL_1
326 ,OKI_DYNAMIC_URL_2
327
328 ,oki_measure_1
329 ,oki_measure_2
330 ,oki_measure_3
331 ,oki_measure_4
332 ,oki_measure_5
333 ,oki_measure_6
334 ,oki_measure_7
335 ,oki_measure_8
336 ,oki_measure_11
337 ,oki_measure_12
338 ,oki_measure_13
339 ,oki_measure_14
340 ,oki_measure_15
341
342 ,oki_measure_16
343 ,oki_measure_17
344 ,sum(oki_measure_8) over() oki_measure_18
345 ,oki_measure_23
346 ,oki_measure_26
347 ,oki_calc_item1
348 ,oki_calc_item2
349 ,oki_calc_item3
350 ,oki_calc_item4
351 ,oki_calc_item5
352 ,oki_calc_item6
353 ,oki_calc_item7
354 ,oki_calc_item8
355
356 ,oki_calc_item11
357 ,oki_calc_item12
358 ,oki_calc_item13
359 ,oki_calc_item14
360 ,oki_calc_item15
361 ,oki_calc_item16
362 ,oki_calc_item17
363 ,oki_calc_item18
364
365 FROM ( ';
366
367 -- ' , decode(resource_id,-999,'''','||l_bookings_url||') OKI_DYNAMIC_URL_2 '||
368 -- ' , decode(resource_id,-999,'||l_rrate_url||','||l_rrate_url||') OKI_DYNAMIC_URL_3 ';
369
370
371
372 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
373 THEN
374 l_url_select :=
375 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_PRNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 '||
379 l_url_select :=
376 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_bookings_url||')) OKI_DYNAMIC_URL_2 ';
377 ELSIF(p_view_by_dim = 'ITEM+ENI_ITEM')
378 THEN
380 'SELECT '''' OKI_DYNAMIC_URL_1 '||
381 ' , '||l_bookings_url||' OKI_DYNAMIC_URL_2 ';
382
383 ELSE
384 l_url_select :=
385 'SELECT '''' OKI_DYNAMIC_URL_1 '||
386 ' , '''' OKI_DYNAMIC_URL_2 ';
387 END IF;
388
389
390 l_sel_clause := l_viewby_select || l_url_select ||
391
392 ' ,'|| p_view_by_col ||
393
394 ' , oset20.c_Rnw oki_measure_1 '||
395
396
397 ' , oset20.c_Bkg oki_measure_2 '||
398
399 ' , oset20.c_rnwl_rate oki_measure_3 '||
400
401 ' , oset20.rnwl_rate_chg oki_measure_4 '||
402
403 ' , oset20.Can oki_measure_5 '||
404
405 ' , oset20.C_Upl oki_measure_6 '||
406
407 ' , oset20.Upl_chg oki_measure_7 '||
408
409 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset20.c_Rnw','oset20.C_Rnw_tot') || ' oki_measure_8 '||
410
411
412 ' , oset20.C_Rnw_tot oki_measure_11 '||
413
414 ' , oset20.C_Bkg_tot oki_measure_12 '||
415
416 ' , oset20.C_rnwl_rate_tot oki_measure_13 '||
417
418 ' , oset20.rnwl_rate_chg_tot oki_measure_14 '||
419
420 ' , oset20.Can_tot oki_measure_15 '||
421
422 ' , oset20.C_Upl_tot oki_measure_16 '||
423
424
425 ' , oset20.Upl_chg_tot oki_measure_17 '||
426
427 ' , oset20.p_rnwl_rate oki_measure_23 '||
428
429 ' , oset20.P_Upl oki_measure_26 '||
430
431 ' , oset20.c_Rnw oki_calc_item1 '||
432
433 ' , oset20.c_Rnw_tot oki_calc_item11 '||
434
435 ' , oset20.P_Rnw oki_calc_item2 '||
436
437 ' , oset20.P_Rnw_tot oki_calc_item12 '||
438
439
440 ' , oset20.C_Bkg oki_calc_item3 '||
441
442 ' , oset20.C_Bkg_tot oki_calc_item13 '||
443
444 ' , oset20.P_Bkg oki_calc_item4 '||
445
446 ' , oset20.P_Bkg_tot oki_calc_item14 '||
447
448 ' , oset20.C_rnwl_rate oki_calc_item5 '||
449
450 ' , oset20.C_rnwl_rate_tot oki_calc_item15 '||
451
452
453 ' , oset20.P_rnwl_rate oki_calc_item6 '||
454
455 ' , oset20.P_rnwl_rate_tot oki_calc_item16 '||
456
457 ' , oset20.C_Upl oki_calc_item7 '||
458
459 ' , oset20.C_Upl_tot oki_calc_item17 '||
460
461 ' , oset20.P_Upl oki_calc_item8 '||
462
463 ' , oset20.P_Upl_tot oki_calc_item18 '||
464
465 ' from '||
466
467
468 ' ( select '||
469
470 ' '|| p_view_by_col ||
471
472 ' , oset15.c_Scr c_Rnw '||
473
474 ' , oset15.p_Scr p_Rnw '||
475
476 ' , oset15.c_ScGpGr c_Bkg '||
477
478 ' , oset15.p_ScGpGr p_Bkg '||
479
480
481 ' , oset15.c_rnwl_Rate c_rnwl_rate '||
482
483 ' , oset15.p_rnwl_Rate p_rnwl_rate '||
484
485 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_rnwl_Rate','oset15.p_rnwl_Rate','P') || ' rnwl_rate_chg '||
486
487 ' , oset15.CScr Can '||
488
489 ' , oset15.c_Upl c_Upl '||
490
491 ' , oset15.p_Upl P_Upl '||
492
493 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_Upl','oset15.p_Upl','NP') || ' Upl_chg '||
494
495
496 ' , oset15.c_Scr_tot C_Rnw_tot '||
497
498 ' , oset15.p_Scr_tot P_Rnw_tot '||
499
503
500 ' , oset15.c_ScGpGr_tot C_Bkg_tot '||
501
502 ' , oset15.p_ScGpGr_tot P_Bkg_tot '||
504 ' , oset15.c_rnwl_Rate_tot C_rnwl_rate_tot '||
505
506 ' , oset15.p_rnwl_Rate_tot P_rnwl_rate_tot '||
507
508
509 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_rnwl_Rate_tot','oset15.p_rnwl_Rate_tot','P') || ' rnwl_rate_chg_tot '||
510
511 ' , oset15.CScr_tot Can_tot '||
512
513 ' , oset15.c_Upl_tot C_Upl_tot '||
514
515 ' , oset15.p_Upl_tot P_Upl_tot '||
516
517 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_Upl_tot','oset15.p_Upl_tot','NP') || ' Upl_chg_tot '||
518
519 ' from '||
520
521 ' (select '||
522
523
524 p_view_by_col ||
525
526 ' , oset13.c_Scr '||
527
528 ' , oset13.p_Scr '||
529
530 ' , oset13.c_ScGpGr '||
531
532 ' , oset13.c_ScGpGo '||
533
534 ' , oset13.p_ScGpGr '||
535
536
537 ' , oset13.p_ScGpGo '||
538
539 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_ScGpGr','oset13.c_Scr') || 'c_rnwl_Rate '||
540
541 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_ScGpGr','oset13.p_Scr') || 'p_rnwl_Rate '||
542
543 ' , oset13.CScr '||
544
545 ' , oset13.c_Scr_tot '||
546
547 ' , oset13.p_Scr_tot '||
548
549 ' , oset13.c_ScGpGr_tot '||
550
551
552 ' , oset13.c_ScGpGo_tot '||
553
554 ' , oset13.p_ScGpGr_tot '||
555
556 ' , oset13.p_ScGpGo_tot '||
557
558 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_ScGpGr_tot','oset13.c_Scr_tot') || ' c_rnwl_Rate_tot '||
559
560 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_ScGpGr_tot','oset13.p_Scr_tot') || ' p_rnwl_Rate_tot '||
561
562 ' , oset13.CScr_tot '||
563
564
565 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.c_ScGpGr','oset13.c_ScGpGo') ||' c_Upl '||
566
567 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.p_ScGpGr','oset13.p_ScGpGo') ||' p_Upl '||
568
569 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.c_ScGpGr_tot','oset13.c_ScGpGo_tot') ||' c_Upl_tot '||
570
571 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.p_ScGpGr_tot','oset13.p_ScGpGo_tot') ||' p_Upl_tot '||
572
573 ' from '||
574
575 ' (select '||
576
577 -- For use in where condition to join to the dimension table
578
579
580 p_view_by_col ||
581
582 ' , oset10.c_Scr c_Scr '||
583
584 ' , oset10.p_Scr p_Scr '||
585
586 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_ScGpr','oset10.c_ScGr') ||' c_ScGpGr '||
587
588 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_ScGpo','oset10.c_ScGo') ||' c_ScGpGo '||
589
590 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_ScGpr','oset10.p_ScGr') ||' p_ScGpGr '||
591
592
593 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_ScGpo','oset10.p_ScGo') ||' p_ScGpGo '||
594
595 ' , oset10.c_CScr CScr '||
596
597 ' , oset10.c_Scr_tot c_Scr_tot '||
598
599 ' , oset10.p_Scr_tot p_Scr_tot '||
600
601 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_ScGpr_tot','oset10.c_ScGr_tot') ||' c_ScGpGr_tot '||
602
603 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_ScGpo_tot','oset10.c_ScGo_tot') ||' c_ScGpGo_tot '||
604
605 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_ScGpr_tot','oset10.p_ScGr_tot') ||' p_ScGpGr_tot '||
606
607
608 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_ScGpo_tot','oset10.p_ScGo_tot') ||' p_ScGpGo_tot '||
609
610 ' , oset10.c_CScr_tot CScr_tot '||
611
612 ' from '||
613
614 ' ( select '||
615
616 ' oset05.'||p_view_by_col ||
617
618 ' , nvl(oset05.c_Scr,0) c_Scr '||
619
620
621 ' , nvl(oset05.c_CScr,0) c_CScr '||
622
623 ' , nvl(oset05.c_ScGpr,0) c_ScGpr '||
624
625 ' , nvl(oset05.c_ScGpo,0) c_ScGpo '||
626
627 ' , nvl(oset05.c_ScGr,0) c_ScGr '||
628
629 ' , nvl(oset05.c_ScGo,0) c_ScGo '||
630
631 ' , nvl(oset05.p_Scr,0) p_Scr '||
632
633 ' , nvl(oset05.p_CScr,0) p_CScr '||
634
635
636 ' , nvl(oset05.p_ScGpr,0) p_ScGpr '||
637
638 ' , nvl(oset05.p_ScGpo,0) p_ScGpo '||
639
640 ' , nvl(oset05.p_ScGr,0) p_ScGr '||
641
642 ' , nvl(oset05.p_ScGo,0) p_ScGo '||
643
644 ' , nvl(oset05.c_Scr_total,0) c_Scr_tot '||
645
646 ' , nvl(oset05.c_CScr_total,0) c_CScr_tot '||
647
648
649 ' , nvl(oset05.c_ScGpr_total,0) c_ScGpr_tot '||
650
651 ' , nvl(oset05.c_ScGpo_total,0) c_ScGpo_tot '||
652
656
653 ' , nvl(oset05.c_ScGr_total,0) c_ScGr_tot '||
654
655 ' , nvl(oset05.c_ScGo_total,0) c_ScGo_tot '||
657 ' , nvl(oset05.p_Scr_total,0) p_Scr_tot '||
658
659 ' , nvl(oset05.p_CScr_total,0) p_CScr_tot '||
660
661 ' , nvl(oset05.p_ScGpr_total,0) p_ScGpr_tot '||
662
663
664 ' , nvl(oset05.p_ScGpo_total,0) p_ScGpo_tot '||
665
666 ' , nvl(oset05.p_ScGr_total,0) p_ScGr_tot '||
667
668 ' , nvl(oset05.p_ScGo_total,0) p_ScGo_tot ';
669
670
671
672
673 RETURN l_sel_clause;
674
675 END get_table_sel_clause;
676
677
678
679 PROCEDURE get_bookings_sql (
680
681 p_param IN bis_pmv_page_parameter_tbl
682 , x_custom_sql OUT NOCOPY VARCHAR2
683 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
684
685 IS
686
687 l_query VARCHAR2 (32767);
688 l_view_by VARCHAR2 (120);
689 l_view_by_col VARCHAR2 (120);
690 l_as_of_date DATE;
691 l_prev_as_of_date DATE;
692 l_xtd VARCHAR2 (10);
693 l_comparison_type VARCHAR2 (1);
694 l_period_type VARCHAR2(10);
695 l_nested_pattern NUMBER;
696 l_curr_suffix VARCHAR2 (2);
697 l_where_clause VARCHAR2 (2000);
698 l_mv VARCHAR2 (2000);
699 l_additional_mv VARCHAR2 (2000);
700 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
701 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
702 l_to_date_xed VARCHAR2 (3) ;
703 l_to_date_xtd VARCHAR2 (3);
704
705 l_rpt_specific_where VARCHAR2 (1000);
706 l_join_where VARCHAR2 (1000);
707 l_group_by VARCHAR2 (1000);
708
709 l_filter_where VARCHAR2 (240);
710
711 BEGIN
712
713 l_to_date_xed := 'XED';
714 l_to_date_xtd := 'XTD';
715 l_comparison_type := 'Y';
716 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
717 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
718
719 oki_dbi_util_pvt.process_parameters ( p_param => p_param
720 , p_view_by => l_view_by
721 , p_view_by_col_name => l_view_by_col
722 , p_comparison_type => l_comparison_type
723 , p_xtd => l_xtd
724 , p_as_of_date => l_as_of_date
725 , p_prev_as_of_date => l_prev_as_of_date
726 , p_cur_suffix => l_curr_suffix
727 , p_nested_pattern => l_nested_pattern
728 , p_where_clause => l_where_clause
729 , p_mv => l_mv
730 , p_join_tbl => l_join_tbl
731 , p_period_type => l_period_type
732 , p_trend => 'N'
733 , p_func_area => 'OKI'
734 , p_version => '6.0'
735 , p_role => NULL
736 , p_mv_set => 'SRM_CDTL_RPT'
737 , p_rg_where => 'Y');
738
739 l_rpt_specific_where :=
740 ' AND fact.renewal_flag in (1,3)
741 AND fact.start_date between &BIS_CURRENT_EFFECTIVE_START_DATE
742 and &BIS_CURRENT_EFFECTIVE_END_DATE
743 AND fact.date_signed <= &BIS_CURRENT_ASOF_DATE';
744
745 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id,fact.date_signed';
746
747 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
748 , p_col_name => 'price_negotiated_' || l_curr_suffix
749 , p_alias_name => 'affected_value'
750 , p_prior_code => poa_dbi_util_pkg.no_priors);
751
752 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
753
754 oki_dbi_util_pvt.join_rpt_where (p_join_tbl => l_join_tbl
755 , p_func_area => 'SRM'
756 , p_version => '6.0'
757 , p_role => NULL
758 , p_mv_set => 'SRM_CDTL_RPT');
759
760 /* Additional filter needed to avoid displaying records queried due to total values at node */
761 l_filter_where := ' ( ABS(oki_measure_1) ) <> 0 ';
762 l_additional_mv := ' ) fact
763 , OKI_SCM_OCR_MV k
764 WHERE fact.chr_id = k.chr_id) ';
768 , p_where_clause => l_where_clause || l_rpt_specific_where
765 l_query := get_bookings_sel_clause (l_curr_suffix, l_period_type )
766 || poa_dbi_template_pkg.dtl_status_sql2 (
767 p_fact_name => l_mv
769 , p_join_tables => l_join_tbl
770 , p_use_windowing => 'Y'
771 , p_col_name => l_col_tbl
772 , p_use_grpid => 'N'
773 , p_filter_where => l_filter_where || l_additional_mv
774 , p_paren_count => 5
775 , p_group_by => l_group_by
776 , p_from_clause => ' from '||l_mv ||' fact ');
777
778 x_custom_sql := '/* OKI_DBI_SRM_PRNWL_BKING_RPT */' || l_query;
779
780
781
782 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
783
784 END get_bookings_sql;
785
786
787 FUNCTION get_bookings_sel_clause (
788 p_cur_suffix IN VARCHAR2
789 , p_period_type_code IN VARCHAR2)
790 RETURN VARCHAR2
791 IS
792 l_query VARCHAR2 (10000);
793
794 BEGIN
795
796 -- Generate sql query
797 l_query :=
798 'SELECT
799 complete_k_number oki_attribute_1
800 , cust.value oki_attribute_2
801 , DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3
802 , to_char(date_signed) OKI_DATE_1
803 , to_char(start_date) OKI_DATE_2
804 , to_char(end_date) OKI_DATE_3
805 , oki_measure_1
806 , OKI_MEASURE_2
807 , oki_measure_11
808 , OKI_MEASURE_12
809 , fact.chr_id OKI_ATTRIBUTE_5
810 FROM
811 (
812 SELECT * FROM
813 ((
814 SELECT
815 fact.*
816 , k.complete_k_number
817 , k.start_date start_date
818 , k.end_date end_date
819 , NVL(k.price_nego_' ||p_cur_suffix ||',0) OKI_MEASURE_2
820 , NVL(SUM(k.price_nego_' ||p_cur_suffix ||') over (),0) OKI_MEASURE_12
821 FROM
822 (
823 SELECT rank() over (ORDER BY OKI_MEASURE_1 DESC nulls last) - 1 rnk
824 , chr_id
825 , customer_party_id
826 , resource_id
827 , oki_measure_1
828 , oki_measure_11
829 , date_signed
830 FROM
831 (
832 SELECT oset5.chr_id
833 , oset5.customer_party_id
834 , oset5.resource_id
835 , nvl(oset5.affected_value,0) OKI_MEASURE_1
836 , nvl(oset5.affected_value_total,0) OKI_MEASURE_11
837 , oset5.date_signed
838 FROM
839 (
840 SELECT fact.chr_id, fact.customer_party_id , fact.resource_id,fact.date_signed';
841 RETURN l_query;
842 END get_bookings_sel_clause;
843
844
845 /* This procedure generates the entire SQL query that is required for the report
846 * Period Renewal Bookings By Customer.
847 *
848 * p_param -->a table populated by PMV which contains all the parameters that
849 * the user selects in the report
850 * x_custom_sql -->the final SQL query that is generated
851 * x_custom_output -->contains the bind variables
852 */
853
854
855 /*******************************************************************************
856 * FUNCTION get_bkngs_by_cust_sql () returns the select clause containing
857 the measures for the report
858 ********************************************************************************/
859 /*
860 FUNCTION get_bkngs_by_cust_sel_clause
861 RETURN VARCHAR2
862 IS
863 l_query VARCHAR2(10000);
864 BEGIN
865
866 l_query :='';
867 RETURN l_query;
868
869 END get_bkngs_by_cust_sel_clause;
870 */
871
872
873 /* This procedure generates the entire SQL query that is required for the report
874 * Period Renewal Bookings By Customer.
875 *
876 * p_param -->a table populated by PMV which contains all the parameters that
877 * the user selects in the report
878 * x_custom_sql -->the final SQL query that is generated
879 * x_custom_output -->contains the bind variables
880 */
881
882
883 PROCEDURE get_bkngs_by_cust_sql (
884
885 p_param IN bis_pmv_page_parameter_tbl
886 , x_custom_sql OUT NOCOPY VARCHAR2
887 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
888
889 IS
890 l_query VARCHAR2 (32767);
891
892 BEGIN
893
894 l_query := 'Hello';
895
896
897 x_custom_sql := '/* OKI_DBI_SRM_PRNWL_SUM_CRPT */' || l_query;
898
899 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
900
901 END get_bkngs_by_cust_sql;
902 /*******************************************************************************
903 * FUNCTION get_bkngs_by_cust_sql () returns the select clause containing
904 the measures for the report
905 ********************************************************************************/
906 /*
910
907 FUNCTION get_bkngs_by_cust_sel_clause
908 RETURN VARCHAR2
909 IS
911 l_query VARCHAR2(10000);
912 BEGIN
913
914 l_query :='';
915 RETURN l_query;
916
917 END get_bkngs_by_cust_sel_clause;
918 */
919
920 PROCEDURE get_rrate_sql (
921 p_param IN bis_pmv_page_parameter_tbl
922 , x_custom_sql OUT NOCOPY VARCHAR2
923 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
924
925 IS
926
927 l_query VARCHAR2 (32767);
928 l_view_by VARCHAR2 (12000);
929 l_view_by_col VARCHAR2 (12000);
930 l_as_of_date DATE;
931 l_prev_as_of_date DATE;
932 l_xtd VARCHAR2 (10);
933 l_comparison_type VARCHAR2 (1) ;
934 l_period_type VARCHAR2(10);
935 l_nested_pattern NUMBER;
936 l_cur_suffix VARCHAR2 (22);
937 l_where_clause VARCHAR2 (20000);
938
939 l_mv VARCHAR2 (2000);
940 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
941 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
942 l_to_date_xed VARCHAR2 (3);
943 l_to_date_xtd VARCHAR2 (3);
944 l_view_by_table VARCHAR2(10000);
945
946 l_viewby_select VARCHAR2(10000);
947 l_url_select VARCHAR2(20000);
948 l_FROM_WHERE VARCHAR2(20000);
949 l_viewby_col_special VARCHAR2(1160); -- Needed when the view by is resource group id
950 l_filter_where VARCHAR2(20000);
951 l_VIEWBY_RANK_ORDER VARCHAR2(20000);
952
953 l_prodcat_url VARCHAR2(1300);
954
955
956 BEGIN
957
958 l_to_date_xed := 'XED';
959 l_to_date_xtd := 'XTD';
960 l_comparison_type := 'Y';
961 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
962 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
963
964
965 oki_dbi_util_pvt.process_parameters ( p_param => p_param
966
967 , p_view_by => l_view_by
968 , p_view_by_col_name => l_view_by_col
969 , p_comparison_type => l_comparison_type
970 , p_xtd => l_xtd
971 , p_as_of_date => l_as_of_date
972 , p_prev_as_of_date => l_prev_as_of_date
973 , p_cur_suffix => l_cur_suffix
974 , p_nested_pattern => l_nested_pattern
975 , p_where_clause => l_where_clause
976 , p_mv => l_mv
977 , p_join_tbl => l_join_tbl
978 , p_period_type => l_period_type
979 , p_trend => 'N'
980
981 , p_func_area => 'OKI'
982 , p_version => '6.0'
983 , p_role => NULL
984 , p_mv_set => 'SRM_DET'
985 , p_rg_where => 'Y');
986
987
988 l_view_by_table := oki_dbi_util_pvt.get_table(dim_name => l_view_by
989 ,p_func_area => 'SRM'
990 ,p_version => '6.0' );
991 IF(l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
992 THEN
993 l_prodcat_url :=
994
995 ' decode(leaf_node_flag,''Y''
996 , ''pFunctionName=OKI_DBI_SRM_PRNWL_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM''
997 ,''pFunctionName=OKI_DBI_SRM_PRNWL_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_2 ';
998 ELSE
999 l_prodcat_url := ''''' OKI_DYNAMIC_URL_2 ';
1000 END IF;
1001
1002 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(l_view_by, 'SRM', '6.0');
1003
1004
1005 /* Additional filter needed to avoid displaying records queried due to total values at node */
1006
1007 l_filter_where := ' AND ( ABS(oki_measure_1) + ABS(oki_measure_2) + ABS(oki_measure_4) + ABS(oki_measure_5) + ABS(oki_measure_7) + ABS(oki_measure_8) ) <> 0 ';
1008
1009
1010 l_viewby_select := l_viewby_select ||
1011 ', OKI_DYNAMIC_URL_1 ,'|| l_prodcat_url || ' ,oki_measure_1 ,oki_measure_2 ,oki_measure_3 ,oki_measure_4
1012 ,oki_measure_5 ,oki_measure_6 , oki_measure_6 oki_calc_item4,oki_measure_7,oki_measure_8, oki_measure_9 ,
1013 oki_measure_9 oki_calc_item5, oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14 ,
1014 oki_measure_15,oki_measure_16 ,oki_measure_17, oki_measure_18, oki_measure_19
1015 FROM (SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||l_view_by_col||') - 1 rnk ,'||l_view_by_col||'
1016 ,OKI_DYNAMIC_URL_1 ,oki_measure_1 ,oki_measure_2 ,oki_measure_3 ,oki_measure_4 ,oki_measure_5 ,oki_measure_6
1020
1017 ,oki_measure_7,oki_measure_8,oki_measure_9 ,oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
1018 ,oki_measure_15 ,oki_measure_16,oki_measure_17,oki_measure_18, oki_measure_19 FROM ( ';
1019
1021 /* Dynamic URL's */
1022
1023
1024 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
1025 THEN
1026 l_url_select :=
1027 'SELECT DECODE(resource_id,-999, ''pFunctionName=OKI_DBI_SRM_PRNWL_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'', '''') OKI_DYNAMIC_URL_1 ';
1028 l_viewby_col_special := ' imm_child_rg_id ';
1029 ELSE
1030 l_url_select :=
1031 'SELECT NULL OKI_DYNAMIC_URL_1 ';
1032 l_viewby_col_special := NULL ;
1033 END IF;
1034
1035
1036
1037 /* From and Joins */
1038 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
1039 THEN
1040 l_FROM_WHERE := '
1041 FROM '||l_mv ||' fact
1042 WHERE fact.mx_id = 5
1043 AND fact.renewal_flag IN (1,3)
1044 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
1045 AND &BIS_CURRENT_EFFECTIVE_END_DATE '
1046 || l_where_clause || '
1047 GROUP BY imm_child_rg_id, resource_id ';
1048
1049 l_VIEWBY_RANK_ORDER :='
1050
1051 )oset05)oset10))oset ,'
1052 || ' jtf_rs_groups_vl g, jtf_rs_resource_extns_vl r
1053 where oset.rg_id=g.group_id and oset.resource_id=r.resource_id(+) '
1054 || l_filter_where || '
1055 AND (rnk BETWEEN &START_INDEX and &END_INDEX or &END_INDEX = -1)
1056 &ORDER_BY_CLAUSE nulls last ';
1057
1058 ELSE
1059 l_FROM_WHERE := '
1060 FROM '||l_mv ||' fact
1061 WHERE fact.mx_id = 5
1062 AND fact.renewal_flag IN (1,3)
1063 AND fact.activity_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
1064
1065 AND &BIS_CURRENT_EFFECTIVE_END_DATE '
1066 || l_where_clause || '
1067 GROUP BY ' ||l_view_by_col ;
1068
1069 l_VIEWBY_RANK_ORDER :='
1070 )oset05)oset10))oset ,'
1071 || l_view_by_table || ' v
1072 WHERE v.id = oset.'||l_view_by_col|| l_filter_where || '
1073 AND (rnk BETWEEN &START_INDEX and &END_INDEX or &END_INDEX = -1)
1074 &ORDER_BY_CLAUSE nulls last ';
1075 END IF;
1076
1077 l_query := l_viewby_select || l_url_select || ' ,'||
1078
1079 l_view_by_col || ',' ||'
1080 oset10.booked_val OKI_MEASURE_1,
1081 oset10.start_val OKI_MEASURE_2,
1082 oset10.val_rate OKI_MEASURE_3,
1083 oset10.booked_lcount OKI_MEASURE_4,
1084 oset10.start_lcount OKI_MEASURE_5,
1085 oset10.lcount_rate OKI_MEASURE_6,
1086 oset10.booked_hcount OKI_MEASURE_7,
1087 oset10.start_hcount OKI_MEASURE_8,
1088 oset10.hcount_rate OKI_MEASURE_9,
1089 oset10.booked_val_tot OKI_MEASURE_11,
1090 oset10.start_val_tot OKI_MEASURE_12,
1091 oset10.val_rate_tot OKI_MEASURE_13,
1092
1093 oset10.booked_lcount_tot OKI_MEASURE_14,
1094 oset10.start_lcount_tot OKI_MEASURE_15,
1095 oset10.lcount_rate_tot OKI_MEASURE_16,
1096 oset10.booked_hcount_tot OKI_MEASURE_17,
1097 oset10.start_hcount_tot OKI_MEASURE_18,
1098 oset10.hcount_rate_tot OKI_MEASURE_19
1099 FROM
1100 (
1101 SELECT '|| l_view_by_col || ',
1102 oset05.booked_val booked_val,
1103 oset05.starting_val start_val,
1104 oset05.booked_val/decode(oset05.starting_val,0,NULL,oset05.starting_val)*100 val_rate,
1105 oset05.booked_lcount booked_lcount,
1106
1107 oset05.starting_lcount start_lcount,
1108 oset05.booked_lcount /decode( oset05.starting_lcount,0,NULL,oset05.starting_lcount)*100 lcount_rate,
1109 oset05.booked_hcount booked_hcount,
1110 oset05.starting_hcount start_hcount,
1111 oset05.booked_hcount /decode( oset05.starting_hcount,0,NULL,oset05.starting_hcount)*100 hcount_rate,
1112 oset05.booked_val_tot booked_val_tot,
1113 oset05.starting_val_tot start_val_tot,
1114 oset05.booked_val_tot/decode(oset05.starting_val_tot,0,NULL,oset05.starting_val_tot)*100 val_rate_tot,
1115 oset05.booked_lcount_tot booked_lcount_tot,
1116 oset05.starting_lcount_tot start_lcount_tot,
1117 oset05.booked_lcount_tot /decode( oset05.starting_lcount_tot,0,NULL,oset05.starting_lcount_tot)*100 lcount_rate_tot,
1118 oset05.booked_hcount_tot booked_hcount_tot,
1119 oset05.starting_hcount_tot start_hcount_tot,
1120
1121 oset05.booked_hcount_tot /decode( oset05.starting_hcount_tot,0,NULL,oset05.starting_hcount_tot)*100 hcount_rate_tot
1122 FROM
1123 (SELECT '|| l_viewby_col_special ||l_view_by_col ||',
1124 NVL(SUM(fact.price_nego_'||l_cur_suffix||'),0) starting_val,
1125 NVL(COUNT(distinct(fact.chr_id)),0) starting_hcount,
1126 NVL(COUNT(distinct(fact.cle_id)),0) starting_lcount,
1127 NVL(SUM(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.price_nego_'||l_cur_suffix||' else null end),0) booked_val,
1128 NVL(COUNT(distinct(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.chr_id else null end)),0) booked_hcount,
1129 NVL(COUNT(distinct(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.cle_id else null end)),0) booked_lcount ,
1130 NVL(SUM(SUM(fact.price_nego_'||l_cur_suffix||')) over (),0) starting_val_tot,
1131 NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) starting_hcount_tot,
1132 NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) starting_lcount_tot,
1133 NVL(SUM(SUM(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.price_nego_'||l_cur_suffix||' else null end)) over (),0) booked_val_tot,
1134
1135 NVL(SUM(COUNT(distinct(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.chr_id else null end))) over(),0) booked_hcount_tot,
1136 NVL(SUM(COUNT(distinct(case when date_signed <= &BIS_CURRENT_ASOF_DATE then fact.cle_id else null end))) over(),0) booked_lcount_tot '||
1137 l_FROM_WHERE || l_VIEWBY_RANK_ORDER ;
1138 x_custom_sql := '/* OKI_DBI_SRM_PRNWL_RATE_DRPT */ ' || l_query;
1142
1139 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1140
1141 END get_rrate_sql;
1143
1144 /*******************************************************************************
1145 Function: get_pr_trend_sql
1146 Description: Function to retrieve the sql statement for the period renewals
1147 TREND portlet
1148
1149 *******************************************************************************/
1150
1151 PROCEDURE get_pr_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1152 x_custom_sql OUT NOCOPY VARCHAR2,
1153 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1154
1155 -- Variables associated with the parameter portlet
1156 l_query VARCHAR2 (32767);
1157 l_view_by VARCHAR2 (120);
1158 l_view_by_col VARCHAR2 (120);
1159 l_as_of_date DATE;
1160 l_prev_as_of_date DATE;
1161 l_xtd VARCHAR2 (10);
1162
1163 l_comparison_type VARCHAR2 (1);
1164 l_nested_pattern NUMBER;
1165 l_dim_bmap NUMBER;
1166 l_cur_suffix VARCHAR2 (2);
1167 l_custom_sql VARCHAR2 (10000);
1168
1169 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
1170 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
1171 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1172
1173 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
1174
1175 l_period_code varchar2(1);
1176 l_where_clause1 VARCHAR2 (2000);
1177 l_where_clause2 VARCHAR2 (2000);
1178 l_mv VARCHAR2 (2000);
1179 l_to_date_xtd VARCHAR2 (3);
1180
1181 l_to_date_xed VARCHAR2 (3);
1182 l_mv1 VARCHAR2 (2000);
1183 l_mv2 VARCHAR2 (2000);
1184
1185 l_xtd1 VARCHAR2 (10);
1186 l_xtd2 VARCHAR2 (10);
1187 BEGIN
1188
1189 l_to_date_xed := 'XED';
1190 l_to_date_xtd := 'XTD';
1191 l_comparison_type := 'Y';
1192 /* DEBUG
1193 OKI_DBIDEBUG_PVT.check_portal_param('OKI_DBI_SRG',p_param);
1194 */
1195 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1196 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1197
1198 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1199
1200 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
1201
1202 oki_dbi_util_pvt.process_parameters (p_param => p_param
1203 , p_view_by => l_view_by
1204 , p_view_by_col_name => l_view_by_col
1205 , p_comparison_type => l_comparison_type
1206 , p_xtd => l_xtd1
1207 , p_as_of_date => l_as_of_date
1208 , p_prev_as_of_date => l_prev_as_of_date
1209 , p_cur_suffix => l_cur_suffix
1210 , p_nested_pattern => l_nested_pattern
1211 , p_where_clause => l_where_clause1
1212 , p_mv => l_mv1
1213 , p_join_tbl => l_join_tbl
1214 ,p_period_type => l_period_code
1215 , p_trend => 'Y'
1216 , p_func_area => 'OKI'
1217 , p_version => '6.0'
1218 , p_role => NULL
1219 , p_mv_set => 'SRM_ST_71'
1220 , p_rg_where => 'Y');
1221 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
1222 , p_col_name => 's_r_amt_' || l_cur_suffix
1223 , p_alias_name => 's_r_amt_xed'
1224 , p_grand_total => 'N'
1225 , p_to_date_type => 'XED');
1226 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
1227 , p_col_name => 's_gpr_amt_' || l_period_code || '_' || l_cur_suffix
1228 , p_alias_name => 's_gpr_amt_xed'
1229
1230 , p_grand_total => 'N'
1231 , p_to_date_type => 'XED');
1232 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
1233 , p_col_name => 's_gpo_amt_'|| l_period_code || '_' || l_cur_suffix
1234 , p_alias_name => 's_gpo_amt_xed'
1235 , p_grand_total => 'N'
1236 , p_to_date_type => 'XED');
1237
1238 oki_dbi_util_pvt.process_parameters (p_param => p_param
1239 , p_view_by => l_view_by
1240 , p_view_by_col_name => l_view_by_col
1241 , p_comparison_type => l_comparison_type
1242 , p_xtd => l_xtd2
1243 , p_as_of_date => l_as_of_date
1244 , p_prev_as_of_date => l_prev_as_of_date
1245 , p_cur_suffix => l_cur_suffix
1246 , p_nested_pattern => l_nested_pattern
1247 , p_where_clause => l_where_clause2
1248 , p_mv => l_mv2
1249 , p_join_tbl => l_join_tbl
1253 , p_version => '6.0'
1250 ,p_period_type => l_period_code
1251 , p_trend => 'Y'
1252 , p_func_area => 'OKI'
1254 , p_role => NULL
1255 , p_mv_set => 'SRM_SG_71'
1256 , p_rg_where => 'Y');
1257
1258
1259 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
1260 , p_col_name => 'g_scr_amt_' || l_period_code || '_' || l_cur_suffix
1261 , p_alias_name => 'g_scr_amt_xtd'
1262 , p_grand_total => 'N'
1263 , p_to_date_type => 'XTD');
1264 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
1265 , p_col_name => 'g_sco_amt_' || l_period_code || '_' || l_cur_suffix
1266 , p_alias_name => 'g_sco_amt_xtd'
1267 , p_grand_total => 'N'
1268 , p_to_date_type => 'XTD');
1269
1270
1271 l_mv_tbl.extend;
1272 l_mv_tbl(1).mv_name := l_mv1;
1273 l_mv_tbl(1).mv_col := l_col_tbl1;
1274 l_mv_tbl(1).mv_where := l_where_clause1;
1275 l_mv_tbl(1).in_join_tbls := NULL;
1276 l_mv_tbl(1).use_grp_id := 'N';
1277 l_mv_tbl(1).mv_xtd := l_xtd1;
1278
1279
1280 l_mv_tbl.extend;
1281
1282 l_mv_tbl(2).mv_name := l_mv2;
1283 l_mv_tbl(2).mv_col := l_col_tbl2;
1284 l_mv_tbl(2).mv_where := l_where_clause2;
1285 l_mv_tbl(2).in_join_tbls := NULL;
1286 l_mv_tbl(2).use_grp_id := 'N';
1287 l_mv_tbl(2).mv_xtd := l_xtd2;
1288
1289
1290 l_query :=
1291 get_trend_sel_clause
1292 || ' from '
1293 ||poa_dbi_template_pkg.union_all_trend_sql
1294 (p_mv => l_mv_tbl,
1295 p_comparison_type => 'R',
1296 p_filter_where => NULL);
1297
1298
1299 x_custom_sql := '/* OKI_DBI_SRM_PR_G */ ' || l_query;
1300 oki_dbi_util_pvt.get_custom_trend_binds (l_xtd1
1301 , l_comparison_type
1302 , x_custom_output);
1303
1304 /* DEBUG
1305 OKI_DBIDEBUG_PVT.check_portal_value('OKI_DBI_SRG','SQL',x_custom_sql);
1306 COMMIT;
1307 */
1308
1309 END get_pr_trend_sql ;
1310
1311
1312 --- ******************************************
1313 FUNCTION get_trend_sel_clause
1314 RETURN VARCHAR2
1315 IS
1316 l_sel_clause VARCHAR2 (10000);
1317 BEGIN
1318
1319 -- OKI_MEASURE_1 : expiring value
1320 -- OKI_MEASURE_2 : prior exp. value
1321 -- OKI_MEASURE_3 : Booked value
1322 -- OKI_MEASURE_4 : prior booked value
1323 -- OKI_MEASURE_5 : Ren rate value
1324 -- OKI_MEASURE_6 : Change
1325
1326 -- OKI_MEASURE_9 : prior Ren rate value
1327 -- OKI_MEASURE_7 : Uplift value
1328 -- OKI_MEASURE_8 : Change Uplift
1329 -- OKI_MEASURE_10 : prior Uplift value
1330
1331 /*-------------
1332 ,('||oki_dbi_util_pvt.add_measures('iset.c_s_gpr_amt_xed','iset.c_g_scr_amt_xtd') || '
1333 /decode(iset.c_s_r_amt_xed,0,NULL,iset.c_s_r_amt_xed)*100) -
1334 ('||oki_dbi_util_pvt.add_measures('iset.p_s_gpr_amt_xed','iset.p_g_scr_amt_xtd') || '
1335 /decode(iset.p_s_r_amt_xed,0,NULL,iset.p_s_r_amt_xed)*100) OKI_MEASURE_6
1336 */
1337
1338 l_sel_clause :=
1339 'SELECT cal_NAME AS VIEWBY
1340 , nvl(uset.c_s_r_amt_xed,0) OKI_MEASURE_1
1341 , nvl(uset.p_s_r_amt_xed,0) OKI_MEASURE_2
1342 , nvl(uset.c_s_gpr_amt_xed,0) + nvl(uset.c_g_scr_amt_xtd,0) OKI_MEASURE_3
1343 , nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0) OKI_MEASURE_4
1344 ,( (nvl(uset.c_s_gpr_amt_xed,0) + nvl(uset.c_g_scr_amt_xtd,0))
1345 /decode(uset.c_s_r_amt_xed,0,NULL,uset.c_s_r_amt_xed))*100 OKI_MEASURE_5
1346 ,(((nvl(uset.c_s_gpr_amt_xed,0) + nvl(uset.c_g_scr_amt_xtd,0))
1347 /decode(uset.c_s_r_amt_xed,0,NULL,uset.c_s_r_amt_xed)*100) -
1348 ((nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0))
1349 /decode(uset.p_s_r_amt_xed,0,NULL,uset.p_s_r_amt_xed)*100)) OKI_MEASURE_6
1350 ,((nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0))
1351 /decode(uset.p_s_r_amt_xed,0,NULL,uset.p_s_r_amt_xed))*100 OKI_MEASURE_9
1352
1353 ,nvl(uset.c_s_gpr_amt_xed,0) + nvl(uset.c_g_scr_amt_xtd,0) -
1354 (nvl(uset.c_s_gpo_amt_xed,0)+nvl(uset.c_g_sco_amt_xtd,0)) OKI_MEASURE_7
1355 ,nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0) -
1356 (nvl(uset.p_s_gpo_amt_xed,0)+nvl(uset.p_g_sco_amt_xtd,0)) OKI_MEASURE_10
1357 , ((nvl(uset.c_s_gpr_amt_xed,0) + nvl(uset.c_g_scr_amt_xtd,0) -
1358 (nvl(uset.c_s_gpo_amt_xed,0) + nvl(uset.c_g_sco_amt_xtd,0)) )
1359 - (nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0) -
1360 (nvl(uset.p_s_gpo_amt_xed,0) + nvl(uset.p_g_sco_amt_xtd,0))
1361 )
1362 ) / abs(decode(
1363 (nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0) -
1364 (nvl(uset.p_s_gpo_amt_xed,0) + nvl(uset.p_g_sco_amt_xtd,0))
1365 ),0,NULL
1366
1367 ,(nvl(uset.p_s_gpr_amt_xed,0) + nvl(uset.p_g_scr_amt_xtd,0) -
1368 (nvl(uset.p_s_gpo_amt_xed,0) + nvl(uset.p_g_sco_amt_xtd,0))
1369 )
1370 )
1371 ) *100 OKI_MEASURE_8 ';
1372
1373 RETURN l_sel_clause;
1374 END get_trend_sel_clause;
1375
1376
1377 END oki_dbi_srm_prnwl_pvt;