[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_SRM_RNWL_PVT
Source
1 PACKAGE BODY OKI_DBI_SRM_RNWL_PVT AS
2 /* $Header: OKIIRNWB.pls 120.7 2006/05/18 01:23:54 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_top_bookings_sel_clause (
10 p_cur_suffix IN VARCHAR2
11 , p_period_type_code IN VARCHAR2)
12 RETURN VARCHAR2;
13
14 FUNCTION get_bookings_sel_clause (
15 p_cur_suffix IN VARCHAR2
16 , p_period_type_code IN VARCHAR2)
17 RETURN VARCHAR2;
18
19 FUNCTION get_renwlforecast_sel_clause (
20 p_cur_suffix IN VARCHAR2
21 , p_period_type_code IN VARCHAR2)
22 RETURN VARCHAR2;
23
24 FUNCTION get_late_rnwl_table_sel_clause (
25 p_view_by_dim IN VARCHAR2
26 , p_view_by_col IN VARCHAR2)
27 RETURN VARCHAR2;
28
29 FUNCTION get_cncl_table_sel_clause (
30 p_view_by_dim IN VARCHAR2
31 , p_view_by_col IN VARCHAR2)
32 RETURN VARCHAR2;
33
34
35 FUNCTION get_cancellations_sel_clause (
36 p_cur_suffix IN VARCHAR2
37 , p_period_type_code IN VARCHAR2)
38 RETURN VARCHAR2;
39
40 --DBI7.0
41 /* FUNCTION get_bkngs_by_cust_sel_clause
42 RETURN VARCHAR2;
43
44 FUNCTION get_exp_bkngs_cust_sel_clause
45 RETURN VARCHAR2;
46
47 FUNCTION get_cancln_by_cust_sel_clause
48 RETURN VARCHAR2;
49 */
50
51 PROCEDURE get_table_sql (
52 p_param IN bis_pmv_page_parameter_tbl
53 , x_custom_sql OUT NOCOPY VARCHAR2
54 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
55 IS
56
57 l_query VARCHAR2 (32767);
58 l_view_by VARCHAR2 (120);
59 l_view_by_col VARCHAR2 (120);
60 l_as_of_date DATE;
61 l_prev_as_of_date DATE;
62 -- l_xtd VARCHAR2 (10);
63 l_comparison_type VARCHAR2 (1);
64 l_period_type VARCHAR2(10);
65 l_nested_pattern NUMBER;
66 l_cur_suffix VARCHAR2 (2);
67 l_where_clause1 VARCHAR2 (2000);
68 l_where_clause2 VARCHAR2 (2000);
69 l_filter_where VARCHAR2 (340);
70 l_mv VARCHAR2 (2000);
71 -- l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
72 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
73 l_to_date_xed VARCHAR2 (3) ;
74 l_to_date_xtd VARCHAR2 (3);
75 l_to_date_ytd VARCHAR2 (3);
76 l_to_date_itd VARCHAR2 (3);
77 l_mv1 VARCHAR2(100);
78 l_mv2 VARCHAR2(100);
79 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
80 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
81 l_xtd1 VARCHAR2(10);
82 l_xtd2 VARCHAR2(10);
83 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
84
85 BEGIN
86 l_to_date_xed := 'XED';
87 l_to_date_xtd := 'XTD';
88 l_to_date_ytd := 'YTD';
89 l_to_date_itd := 'ITD';
90 l_comparison_type := 'Y';
91
92 l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl ();
93 l_col_tbl1 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
94 l_col_tbl2 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
95 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
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_SG_71'
114 , p_rg_where => 'Y');
115
116
117 -- Populate col table with regular columns
118 -- Period Renewal node
119 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
120 , p_col_name => 'g_r_amt_' || l_cur_suffix
121 , p_alias_name => 'Gr'
122 , p_to_date_type => l_to_date_xtd);
123
124 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
125 , p_col_name => 'g_o_amt_' || l_cur_suffix
126 , p_alias_name => 'Go'
127 , p_to_date_type => l_to_date_xtd);
128
129
130 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
131 , p_view_by => l_view_by
132 , p_view_by_col_name => l_view_by_col
133 , p_comparison_type => l_comparison_type
134 , p_xtd => l_xtd1
135 , p_as_of_date => l_as_of_date
136 , p_prev_as_of_date => l_prev_as_of_date
137 , p_cur_suffix => l_cur_suffix
138 , p_nested_pattern => l_nested_pattern
139 , p_where_clause => l_where_clause2
140 , p_mv => l_mv2
141 , p_join_tbl => l_join_tbl
142 , p_period_type => l_period_type
143 , p_trend => 'N'
144 , p_func_area => 'OKI'
145 , p_version => '6.0'
146 , p_role => NULL
147 , p_mv_set => 'SRM_EC_71'
148 , p_rg_where => 'Y');
149 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
150 , p_col_name => 'f_f_amt_' || l_cur_suffix
151 , p_alias_name => 'Fcf'
152 , p_to_date_type => l_to_date_xed
153 , p_prior_code => poa_dbi_util_pkg.no_priors);
154
155 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
156 , p_col_name => 'f_r_amt_' || l_cur_suffix
157 , p_alias_name => 'Fcr'
158 , p_to_date_type => l_to_date_xed
159 , p_prior_code => poa_dbi_util_pkg.no_priors);
160
161 l_mv_tbl.extend;
162 l_mv_tbl(1).mv_name := l_mv1;
163 l_mv_tbl(1).mv_col := l_col_tbl1;
164 l_mv_tbl(1).mv_where := l_where_clause1;
165 l_mv_tbl(1).in_join_tbls := NULL;
166 l_mv_tbl(1).use_grp_id := 'N';
167 l_mv_tbl.extend;
168 l_mv_tbl(2).mv_name := l_mv2;
169 l_mv_tbl(2).mv_col := l_col_tbl2;
170 l_mv_tbl(2).mv_where := l_where_clause2;
171 l_mv_tbl(2).in_join_tbls := NULL;
172 l_mv_tbl(2).use_grp_id := 'N';
173 /* Additional filter needed to avoid displaying records queried due to total values at node */
174
175 l_filter_where := ' ( ABS(oki_measure_1) +
176 ABS(oki_measure_3) +
177 ABS(oki_measure_4) +
178 ABS(oki_measure_7) +
179 ABS(oki_measure_8) ) <> 0 ' ;
180
181 -- oki_measure_21 + --commented for bug 3503029
182 -- oki_measure_25) <> 0 ';
183
184 -- Generate sql query
185
186
187 l_query := get_table_sel_clause (l_view_by, l_view_by_col)
188 || ' from (
189 ' || poa_dbi_template_pkg.union_all_status_sql
190 (p_mv => l_mv_tbl,
191 p_join_tables => l_join_tbl,
192 p_use_windowing => 'Y',
193 p_paren_count => 7,
194 p_filter_where => l_filter_where );
195
196 -- insert into brrao_temp values ( l_query);
197 -- commit;
198
199 x_custom_sql := '/* OKI_DBI_SRM_RNWL_SUM_RPT */' ||l_query;
200 --oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
201 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
202
203 END get_table_sql;
204
205
206 /*
207 Renewal Bookings Summary Select clause
208 */
209 FUNCTION get_table_sel_clause (
210 p_view_by_dim IN VARCHAR2
211 , p_view_by_col IN VARCHAR2)
212 RETURN VARCHAR2
213 IS
214 l_sel_clause VARCHAR2 (32767);
215 l_bookings_url VARCHAR2(300);
216 l_forecast_url VARCHAR2(300);
217 l_prodcat_url VARCHAR2(300);
218 l_viewby_select VARCHAR2(32767);
219 l_url_select VARCHAR2(32767);
220 BEGIN
221
222
223 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
224
225 -- Bookings URL when view by is Salesrep
226 l_bookings_url := '''pFunctionName=OKI_DBI_SRM_RSBK_DTL_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
227 --l_bookings_url := '''pFunctionName=OKI_DBI_SRM_RSBK_DTL_DRPT''';
228
229 l_forecast_url := '''pFunctionName=OKI_DBI_SRM_FCST_DTL_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
230 --l_forecast_url := '''pFunctionName=OKI_DBI_SRM_FCST_DTL_DRPT''';
231
232 IF(p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT')
233 THEN
234 l_prodcat_url :=
235 ' decode(leaf_node_flag,''Y''
236 , ''pFunctionName=OKI_DBI_SRM_RNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM''
237 ,''pFunctionName=OKI_DBI_SRM_RNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_4 ';
238 ELSE
239 l_prodcat_url := ''''' OKI_DYNAMIC_URL_4 ';
240 END IF;
241
242
243 l_viewby_select := l_viewby_select ||
244 ', OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_2 ,OKI_DYNAMIC_URL_3, '|| l_prodcat_url || ', oki_measure_1 ,oki_measure_2
245 ,oki_measure_3 ,oki_measure_4 ,oki_measure_5 ,oki_measure_6,oki_measure_7,oki_measure_8 ,oki_measure_11
246 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14 ,oki_measure_15 ,oki_measure_16, oki_measure_17,oki_measure_18
247 , oki_measure_21, oki_measure_25 ,oki_calc_item1 ,oki_calc_item2
248 ,oki_calc_item3 ,oki_calc_item4,oki_calc_item5, oki_calc_item6, oki_calc_item11 ,oki_calc_item12 ,oki_calc_item13
249 ,oki_calc_item14, oki_calc_item15, oki_calc_item16
250 FROM (SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
251 ,OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_2 ,OKI_DYNAMIC_URL_3 ,oki_measure_1 ,oki_measure_2 ,oki_measure_3
252 ,oki_measure_4 ,oki_measure_5 ,oki_measure_6, oki_measure_7, oki_measure_8 ,oki_measure_11 ,oki_measure_12
253 ,oki_measure_13 ,oki_measure_14 ,oki_measure_15 ,oki_measure_16, oki_measure_17, oki_measure_18 ,oki_measure_21
254 , oki_measure_25 ,oki_calc_item1 ,oki_calc_item2 ,oki_calc_item3
255 ,oki_calc_item4, oki_calc_item5, oki_calc_item6, oki_calc_item11 ,oki_calc_item12 ,oki_calc_item13
256 ,oki_calc_item14, oki_calc_item15, oki_calc_item16
257 FROM ( ';
258
259 --' , decode(resource_id,-999,'''','||l_bookings_url||') OKI_DYNAMIC_URL_2 '||
260 -- ' , decode(resource_id,-999,'''','||l_forecast_url||') OKI_DYNAMIC_URL_3 ';
261
262 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
263 THEN
264 l_url_select :=
265 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_RNWL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 '||
266 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_bookings_url||')) OKI_DYNAMIC_URL_2 '||
267 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_forecast_url||')) OKI_DYNAMIC_URL_3 ';
268 ELSIF(p_view_by_dim = 'ITEM+ENI_ITEM')
269 THEN
270 l_url_select :=
271 'SELECT '''' OKI_DYNAMIC_URL_1 '||
272 ' , '||l_bookings_url||' OKI_DYNAMIC_URL_2 '||
273 ' , '||l_forecast_url||' OKI_DYNAMIC_URL_3 ';
274 ELSE
275 l_url_select :=
276 'SELECT '''' OKI_DYNAMIC_URL_1 '||
277 ' , '''' OKI_DYNAMIC_URL_2 '||
278 ' , '''' OKI_DYNAMIC_URL_3 ';
279
280 END IF;
281
282 l_sel_clause := l_viewby_select || l_url_select ||
283 -- AK Attribute naming
284 ' ,'|| p_view_by_col ||
285 ' , oset20.C_bkg oki_measure_1 '||
286 ' , oset20.bkg_chg oki_measure_2 '||
287 ' , oset20.fcst oki_measure_3 '||
288 ' , oset20.exp_bkg oki_measure_4 '||
289 ' , oset20.C_upl oki_measure_5 '||
290 ' , oset20.upl_chg oki_measure_6 '||
291 ' , oset20.fcst_full oki_measure_7 '||
292 ' , oset20.exp_bkg_full oki_measure_8 '||
293 ' , oset20.C_bkg_tot oki_measure_11 '||
294 ' , oset20.bkg_chg_tot oki_measure_12 '||
295 ' , oset20.fcst_tot oki_measure_13 '||
296 ' , oset20.exp_bkg_tot oki_measure_14 '||
297 ' , oset20.C_upl_tot oki_measure_15 '||
298 ' , oset20.upl_chg_tot oki_measure_16 '||
299 ' , oset20.fcst_full_tot oki_measure_17 '||
300 ' , oset20.exp_bkg_full_tot oki_measure_18 '||
301 ' , oset20.p_bkg oki_measure_21 '||
302 ' , oset20.P_upl oki_measure_25 '||
303 ' , oset20.C_bkg oki_calc_item1 '||
304 ' , oset20.C_bkg_tot oki_calc_item11 '||
305 ' , oset20.P_bkg oki_calc_item2 '||
306 ' , oset20.P_bkg_tot oki_calc_item12 '||
307 ' , oset20.exp_bkg oki_calc_item3 '||
308 ' , oset20.exp_bkg_tot oki_calc_item13 '||
309 ' , NULL oki_calc_item4 '||
310 ' , NULL oki_calc_item14 '||
311 ' , oset20.C_upl oki_calc_item5 '||
312 ' , oset20.C_upl_tot oki_calc_item15 '||
313 ' , oset20.P_upl oki_calc_item6 '||
314 ' , oset20.P_upl_tot oki_calc_item16 '||
315 ' from '||
316 ' ( select '||
317 -- Change Calculation
318 ' '|| p_view_by_col ||
319 ' , oset15.C_Gr C_bkg '||
320 ' , oset15.P_Gr P_bkg '||
321 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_Gr','oset15.P_Gr','NP') || ' bkg_chg '||
322 ' , oset15.C_Fcf fcst '||
323 ' , oset15.C_Fcr fcst_full '||
324 ' , oset15.C_GrFcf exp_bkg '||
325 ' , oset15.C_GrFcr exp_bkg_full '||
326 ' , oset15.C_upl c_upl '||
327 ' , oset15.P_upl p_upl '||
328 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_upl','oset15.P_upl','NP') || ' upl_chg '||
329 ' , oset15.C_Gr_tot C_bkg_tot '||
330 ' , oset15.P_Gr_tot P_bkg_tot '||
331 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_Gr_tot','oset15.P_Gr_tot','NP') || ' bkg_chg_tot '||
332 ' , oset15.C_Fcf_tot fcst_tot '||
333 ' , oset15.C_Fcr_tot fcst_full_tot '||
334 ' , oset15.C_GrFcf_tot exp_bkg_tot '||
335 ' , oset15.C_GrFcr_tot exp_bkg_full_tot '||
339 ' from '||
336 ' , oset15.C_upl_tot C_upl_tot '||
337 ' , oset15.P_upl_tot P_upl_tot '||
338 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_upl_tot','oset15.P_upl_tot','NP') || ' upl_chg_tot '||
340 ' (select '||
341 -- Calculated Measures
342 p_view_by_col ||
343 ' , oset13.c_Gr '||
344 ' , oset13.p_Gr '||
345 ' , oset13.c_Fcf '||
346 ' , oset13.c_Fcr '||
347 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset13.c_Gr','oset13.c_Fcf') ||' c_GrFcf '||
348 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset13.c_Gr','oset13.c_Fcr') ||' c_GrFcr '||
349 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.c_Gr','oset13.c_Go') ||' c_Upl '||
350 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.p_Gr','oset13.p_Go') ||' p_Upl '||
351 ' , oset13.c_Gr_tot '||
352 ' , oset13.p_Gr_tot '||
353 ' , oset13.c_Fcf_tot '||
354 ' , oset13.c_Fcr_tot '||
355 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset13.c_Gr_tot','oset13.c_Fcf_tot') ||' c_GrFcf_tot '||
356 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset13.c_Gr_tot','oset13.c_Fcr_tot') ||' c_GrFcr_tot '||
357 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.c_Gr_tot','oset13.c_Go_tot') ||' c_Upl_tot '||
358 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset13.p_Gr_tot','oset13.p_Go_tot') ||' p_Upl_tot '||
359 ' from '||
360 ' (select '||
361 -- Measures Based on a formula
362 p_view_by_col ||
363 ' , oset10.c_Gr c_Gr '||
364 ' , oset10.c_Go c_Go '||
365 ' , oset10.p_Gr p_Gr '||
366 ' , oset10.p_Go p_Go '||
367 ' , oset10.c_Fcf c_Fcf '||
368 ' , oset10.c_Fcr c_Fcr '||
369 ' , oset10.c_Gr_tot c_Gr_tot '||
370 ' , oset10.c_Go_tot c_Go_tot '||
371 ' , oset10.p_Gr_tot p_Gr_tot '||
372 ' , oset10.p_Go_tot p_Go_tot '||
373 ' , oset10.c_Fcf_tot c_Fcf_tot '||
374 ' , oset10.c_Fcr_tot c_Fcr_tot '||
375 ' from '||
376 ' ( select '||
377 ' oset05.'||p_view_by_col ||
378 ' , nvl(oset05.c_Gr,0) c_Gr '||
379 ' , nvl(oset05.c_Go,0) c_Go '||
380 ' , nvl(oset05.c_Fcf,0) c_Fcf '||
381 ' , nvl(oset05.c_Fcr,0) c_Fcr '||
382 ' , nvl(oset05.p_Gr,0) p_Gr '||
383 ' , nvl(oset05.p_Go,0) p_Go '||
384 ' , nvl(oset05.c_Gr_total,0) c_Gr_tot '||
385 ' , nvl(oset05.c_Go_total,0) c_Go_tot '||
386 ' , nvl(oset05.c_Fcf_total,0) c_Fcf_tot '||
387 ' , nvl(oset05.c_Fcr_total,0) c_Fcr_tot '||
388 ' , nvl(oset05.p_Gr_total,0) p_Gr_tot '||
389 ' , nvl(oset05.p_Go_total,0) p_Go_tot ';
390
391 RETURN l_sel_clause;
392 END get_table_sel_clause;
393
394 PROCEDURE get_bookings_sql (
395 p_param IN bis_pmv_page_parameter_tbl
396 , x_custom_sql OUT NOCOPY VARCHAR2
397 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
398 IS
399
400 l_query VARCHAR2 (32767);
401 l_view_by VARCHAR2 (120);
402 l_view_by_col VARCHAR2 (120);
403 l_as_of_date DATE;
404 l_prev_as_of_date DATE;
405 l_xtd VARCHAR2 (10);
406 l_comparison_type VARCHAR2 (1);
407 l_period_type VARCHAR2(10);
408 l_nested_pattern NUMBER;
409 l_cur_suffix VARCHAR2 (2);
410 l_where_clause VARCHAR2 (2000);
411 l_mv VARCHAR2 (2000);
412 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
413 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
414 l_to_date_xed VARCHAR2 (3);
415 l_to_date_xtd VARCHAR2 (3);
416
417 l_rpt_specific_where VARCHAR2 (1000);
418 l_join_where VARCHAR2 (1000);
419 l_group_by VARCHAR2 (1000);
420 l_filter_where VARCHAR2 (240);
421 l_additional_where VARCHAR2 (2000);
422 l_columns VARCHAR (5000);
423
424 BEGIN
425
426 l_to_date_xed := 'XED';
427 l_to_date_xtd := 'XTD';
428 l_comparison_type := 'Y';
429 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
430 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
431
432 oki_dbi_util_pvt.process_parameters ( p_param => p_param
433 , p_view_by => l_view_by
434 , p_view_by_col_name => l_view_by_col
435 , p_comparison_type => l_comparison_type
436 , p_xtd => l_xtd
437 , p_as_of_date => l_as_of_date
438 , p_prev_as_of_date => l_prev_as_of_date
439 , p_cur_suffix => l_cur_suffix
440 , p_nested_pattern => l_nested_pattern
441 , p_where_clause => l_where_clause
442 , p_mv => l_mv
446 , p_func_area => 'OKI'
443 , p_join_tbl => l_join_tbl
444 , p_period_type => l_period_type
445 , p_trend => 'N'
447 , p_version => '6.0'
448 , p_role => NULL
449 , p_mv_set => 'SRM_DTL_RPT'
450 , p_rg_where => 'Y');
451
452 l_rpt_specific_where :=
453 ' AND fact.renewal_flag in (1,3)
454 AND fact.date_signed between &BIS_CURRENT_EFFECTIVE_START_DATE
455 and &BIS_CURRENT_ASOF_DATE';
456
457 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id , fact.date_signed';
458
459 poa_dbi_util_pkg.add_column ( p_col_tbl => l_col_tbl
460 , p_col_name => 'price_negotiated_' || l_cur_suffix
461 , p_alias_name => 'affected_value'
462 , p_prior_code => poa_dbi_util_pkg.no_priors);
463
464 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
465
466 oki_dbi_util_pvt.join_rpt_where ( p_join_tbl => l_join_tbl
467 , p_func_area => 'SRM'
468 , p_version => '6.0'
469 , p_role => NULL
470 , p_mv_set => 'SRM_DTL_RPT');
471
472 /* Additional filter needed to avoid displaying records queried due to total values at node */
473 l_filter_where := ' ( ABS(oki_measure_1) ) <> 0 ';
474
475 l_query := get_bookings_sel_clause (l_cur_suffix, l_period_type ) ||
476 poa_dbi_template_pkg.dtl_status_sql2 (
477 p_fact_name => l_mv
478 , p_where_clause => l_where_clause || l_rpt_specific_where
479 , p_join_tables => l_join_tbl
480 , p_use_windowing => 'Y'
481 , p_col_name => l_col_tbl
482 , p_use_grpid => 'N'
483 , p_filter_where => l_filter_where
484 , p_paren_count => 5
485 , p_group_by => l_group_by
486 , p_from_clause => ' from '||l_mv ||' fact ');
487
488 x_custom_sql := '/* OKI_DBI_SRM_RSBK_DTL_DRP */' || l_query;
489
490
491 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
492
493
494 END get_bookings_sql;
495
496 PROCEDURE get_top_bookings_sql (
497 p_param IN bis_pmv_page_parameter_tbl
498 , x_custom_sql OUT NOCOPY VARCHAR2
499 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
500 IS
501
502 l_query VARCHAR2 (32767);
503 l_view_by VARCHAR2 (120);
504 l_view_by_col VARCHAR2 (120);
505 l_as_of_date DATE;
506 l_prev_as_of_date DATE;
507 l_xtd VARCHAR2 (10);
508 l_comparison_type VARCHAR2 (1);
509 l_period_type VARCHAR2(10);
510 l_nested_pattern NUMBER;
511 l_curr_suffix VARCHAR2 (2);
512 l_where_clause VARCHAR2 (2000);
513 l_mv VARCHAR2 (2000);
514 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
515 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
516 l_to_date_xed VARCHAR2 (3);
517 l_to_date_xtd VARCHAR2 (3);
518
519 l_filter_where varchar2(1000);
520 l_rpt_specific_where VARCHAR2 (1000);
521 l_join_where VARCHAR2 (1000);
522 l_group_by VARCHAR2 (1000);
523
524 g_resource_id NUMBER;
525 g_rs_group_id NUMBER;
526 l_pseudo_rs_group VARCHAR2(1000);
527 l_sep NUMBER;
528 BEGIN
529
530 l_to_date_xed := 'XED';
531 l_to_date_xtd := 'XTD';
532 l_comparison_type := 'Y';
533 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
534 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
535 oki_dbi_util_pvt.process_parameters ( p_param => p_param
536 , p_view_by => l_view_by
537 , p_view_by_col_name => l_view_by_col
538 , p_comparison_type => l_comparison_type
539 , p_xtd => l_xtd
540 , p_as_of_date => l_as_of_date
541 , p_prev_as_of_date => l_prev_as_of_date
542 , p_cur_suffix => l_curr_suffix
543 , p_nested_pattern => l_nested_pattern
544 , p_where_clause => l_where_clause
548 , p_trend => 'N'
545 , p_mv => l_mv
546 , p_join_tbl => l_join_tbl
547 , p_period_type => l_period_type
549 , p_func_area => 'OKI'
550 , p_version => '6.0'
551 , p_role => NULL
552 , p_mv_set => 'SRM_TBK_RPT'
553 , p_rg_where => 'Y');
554
555 l_rpt_specific_where :=
556 ' AND fact.renewal_flag in (1,3)
557 AND fact.date_signed between &BIS_CURRENT_EFFECTIVE_START_DATE
558 and &BIS_CURRENT_ASOF_DATE ';
559 l_group_by := ' GROUP BY chr_id,customer_party_id,resource_id,complete_k_number,hstart_date,hend_date ';
560
561 /* Additional filter needed to avoid displaying records queried due to total values at node */
562 l_filter_where := ' ( ABS(oki_measure_1) ) <> 0 ';
563
564
565 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
566
567 oki_dbi_util_pvt.join_rpt_where (p_join_tbl => l_join_tbl
568 , p_func_area => 'SRM'
569 , p_version => '6.0'
570 , p_role => NULL
571 , p_mv_set => 'SRM_TBK_RPT');
572
573 l_query := get_top_bookings_sel_clause (l_curr_suffix, l_period_type )
574 || poa_dbi_template_pkg.dtl_status_sql2 (p_fact_name => l_mv
575 , p_where_clause => l_where_clause || l_rpt_specific_where
576 , p_join_tables => l_join_tbl
577 , p_use_windowing => 'Y'
578 , p_col_name => l_col_tbl
579 , p_use_grpid => 'N'
580 , p_filter_where => l_filter_where
581 , p_paren_count => 5
582 , p_group_by => l_group_by
583 , p_from_clause => ' from '||l_mv ||' fact ');
584 x_custom_sql := '/* OKI_DBI_SRM_RSBK_DTL_DRPT */ ' || l_query;
585
586 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
587
588 END get_top_bookings_sql;
589
590
591
592 FUNCTION get_top_bookings_sel_clause (
593 p_cur_suffix IN VARCHAR2
594 , p_period_type_code IN VARCHAR2)
595 RETURN VARCHAR2
596 IS
597 l_query VARCHAR2 (10000);
598
599
600 BEGIN
601
602 -- Generate sql query
603 l_query :=
604 '
605 SELECT
606 oki_attribute_1,
607 cust.value oki_attribute_2,
608 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
609 OKI_DATE_1,
610 OKI_DATE_2,
611 OKI_DATE_3,
612 oki_measure_1,
613 oki_measure_11,
614 fact.chr_id OKI_ATTRIBUTE_5
615 FROM(
616 SELECT *
617 FROM (
618 SELECT
619 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
620 oki_attribute_1,
621 oki_date_1,
622 oki_date_2,
623 oki_date_3,
624 customer_party_id,
625 resource_id,
626 oki_measure_1,
627 oki_measure_11,
628 chr_id
629 FROM (
630 SELECT oset5.complete_k_number oki_attribute_1,
631 oset5.customer_party_id ,
632 oset5.resource_id ,
633 oset5.chr_id,
634 to_char(oset5.date_signed) OKI_DATE_1,
635 oset5.start_date OKI_DATE_2,
636 oset5.end_date OKI_DATE_3,
637 nvl(oset5.affected_value,0) OKI_MEASURE_1,
638 nvl(oset5.affected_value_total,0) OKI_MEASURE_11
639 FROM
640 ( SELECT
641 fact.customer_party_id,
642 fact.resource_id,
643 fact.chr_id,
644 fact.complete_k_number,
645 min(fact.date_signed) date_signed,
646 to_char(fact.hstart_date) start_date,
647 to_char(fact.hend_date) end_date,
648 sum(fact.price_negotiated_'|| p_cur_suffix || ') affected_value,
649 sum(sum(fact.price_negotiated_'|| p_cur_suffix || ')) over() affected_value_total ';
650 RETURN l_query;
651 END get_top_bookings_sel_clause;
652
653 FUNCTION get_bookings_sel_clause (
654 p_cur_suffix IN VARCHAR2
655 , p_period_type_code IN VARCHAR2)
656 RETURN VARCHAR2
657 IS
658 l_query VARCHAR2 (10000);
659
660
661 BEGIN
662
663 -- Generate sql query
664 l_query :=
665 '
666 SELECT
667 k.complete_k_number oki_attribute_1,
668 cust.value oki_attribute_2,
672 to_char(k.end_date) OKI_DATE_3,
669 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
670 OKI_DATE_1,
671 to_char(k.start_date) OKI_DATE_2,
673 oki_measure_1,
674 oki_measure_11,
675 fact.chr_id OKI_ATTRIBUTE_5
676 FROM(
677 SELECT *
678 FROM (
679 SELECT
680 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
681 chr_id,
682 customer_party_id,
683 resource_id,
684 oki_measure_1,
685 oki_measure_11,
686 date_signed OKI_DATE_1
687 FROM (
688 SELECT oset5.chr_id ,
689 oset5.customer_party_id ,
690 oset5.resource_id ,
691 nvl(oset5.affected_value,0) OKI_MEASURE_1,
692 nvl(oset5.affected_value_total,0) OKI_MEASURE_11,
693 date_signed
694 FROM
695 (SELECT
696 fact.chr_id,
697 fact.customer_party_id,
698 fact.resource_id,
699 to_char(fact.date_signed) date_signed';
700 RETURN l_query;
701 END get_bookings_sel_clause;
702
703
704 /* This procedure generates the entire SQL query that is required for the report
705 * Renewal Bookings By Customer.
706 *
707 * p_param -->a table populated by PMV which contains all the parameters that
708 * the user selects in the report
709 * x_custom_sql -->the final SQL query that is generated
710 * x_custom_output -->contains the bind variables
711 */
712
713 PROCEDURE get_bkngs_by_cust_sql (
714 p_param IN bis_pmv_page_parameter_tbl
715 , x_custom_sql OUT NOCOPY VARCHAR2
716 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
717 IS
718 l_query VARCHAR2 (32767);
719 BEGIN
720 l_query := 'Hello';
721 x_custom_sql := '/* OKI_DBI_SRM_RSBK_DTL_CRPT */ ' || l_query;
722 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
723 END get_bkngs_by_cust_sql;
724
725 /*******************************************************************************/
726 /* get_bkngs_by_cust_sel_clause returns the top most select statement of the query
727 /********************************************************************************/
728 /*
729 FUNCTION get_bkngs_by_cust_sel_clause
730 RETURN VARCHAR2
731 IS
732 l_query VARCHAR2 (32767);
733
734
735 BEGIN
736
737 RETURN l_query;
738
739 END get_bkngs_by_cust_sel_clause;
740 */
741
742
743 PROCEDURE get_renewal_forecast_sql (
744 p_param IN bis_pmv_page_parameter_tbl
745 , x_custom_sql OUT NOCOPY VARCHAR2
746 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
747 IS
748 l_query VARCHAR2 (32767);
749 l_view_by VARCHAR2 (120);
750 l_view_by_col VARCHAR2 (120);
751 l_as_of_date DATE;
752 l_prev_as_of_date DATE;
753 l_xtd VARCHAR2 (10);
754 l_comparison_type VARCHAR2 (1);
755 l_period_type VARCHAR2(10);
756 l_nested_pattern NUMBER;
757 l_cur_suffix VARCHAR2 (2);
758 l_where_clause VARCHAR2 (2000);
759 l_mv VARCHAR2 (2000);
760 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
761 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
762 l_to_date_xed VARCHAR2 (3);
763 l_to_date_xtd VARCHAR2 (3);
764
765 l_rpt_specific_where VARCHAR2 (1000);
766 l_join_where VARCHAR2 (1000);
767 l_group_by VARCHAR2 (1000);
768 l_filter_where VARCHAR2 (240);
769
770 BEGIN
771
772 l_to_date_xed := 'XED';
773 l_to_date_xtd := 'XTD';
774 l_comparison_type := 'Y';
775 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
776 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
777 oki_dbi_util_pvt.process_parameters ( p_param => p_param
778 , p_view_by => l_view_by
779 , p_view_by_col_name => l_view_by_col
780 , p_comparison_type => l_comparison_type
781 , p_xtd => l_xtd
782 , p_as_of_date => l_as_of_date
783 , p_prev_as_of_date => l_prev_as_of_date
784 , p_cur_suffix => l_cur_suffix
785 , p_nested_pattern => l_nested_pattern
786 , p_where_clause => l_where_clause
787 , p_mv => l_mv
788 , p_join_tbl => l_join_tbl
789 , p_period_type => l_period_type
793 , p_role => NULL
790 , p_trend => 'N'
791 , p_func_area => 'OKI'
792 , p_version => '6.0'
794 , p_mv_set => 'SRM_DTL_RPT'
795 , p_rg_where => 'Y');
796
797
798 l_rpt_specific_where :=
799 ' AND fact.renewal_flag in (1,3)
800 AND fact.past_due_date = TO_DATE(''01-01-4712'',''DD-MM-YYYY'')
801 AND fact.expected_close_date between &BIS_CURRENT_EFFECTIVE_START_DATE
802 and &BIS_CURRENT_EFFECTIVE_END_DATE ' ;
803
804 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id';
805
806
807 poa_dbi_util_pkg.add_column
808 ( p_col_tbl => l_col_tbl
809 , p_col_name => 'price_negotiated_' || l_cur_suffix
810 , p_alias_name => 'affected_full_value'
811 , p_prior_code => poa_dbi_util_pkg.no_priors);
812
813 poa_dbi_util_pkg.add_column
814 ( p_col_tbl => l_col_tbl
815 , p_col_name => 'win_percent *.01 *price_negotiated_' || l_cur_suffix
816 , p_alias_name => 'affected_forecast_value'
817 , p_prior_code => poa_dbi_util_pkg.no_priors);
818
819 /* Additional filter needed to avoid displaying records queried due to total values at node */
820 l_filter_where := ' ( ABS(oki_measure_1) + ABS(oki_measure_3) ) <> 0 ';
821
822 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
823 oki_dbi_util_pvt.join_rpt_where (p_join_tbl => l_join_tbl
824 , p_func_area => 'SRM'
825 , p_version => '6.0'
826 , p_role => NULL
827 , p_mv_set => 'SRM_DTL_RPT');
828
829 l_query := get_renwlforecast_sel_clause (l_cur_suffix, l_period_type )
830
831 || poa_dbi_template_pkg.dtl_status_sql2 (
832 p_fact_name => l_mv
833 , p_where_clause => l_where_clause || l_rpt_specific_where
834 , p_join_tables => l_join_tbl
835 , p_use_windowing => 'Y'
836 , p_col_name => l_col_tbl
837 , p_use_grpid => 'N'
838 , p_filter_where => l_filter_where
839 , p_paren_count => 5
840 , p_group_by => l_group_by
841 , p_from_clause => ' from '||l_mv ||' fact ');
842
843 x_custom_sql := '/* OKI_DBI_SRM_FCST_DTL_DRPT */' || l_query;
844 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
845
846 END get_renewal_forecast_sql;
847
848 FUNCTION get_renwlforecast_sel_clause (
849 p_cur_suffix IN VARCHAR2
850 , p_period_type_code IN VARCHAR2)
851 RETURN VARCHAR2
852 IS
853 l_query VARCHAR2 (10000);
854
855 BEGIN
856
857 -- Generate sql query
861 cust.value oki_attribute_2,
858 l_query :=
859 'SELECT
860 k.complete_k_number oki_attribute_1,
862 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
863 to_char(k.start_date) OKI_DATE_1,
864 to_char(k.expected_close_date) OKI_DATE_2,
865 oki_measure_1,
866 k.win_percent OKI_MEASURE_2,
867 oki_measure_3,
868 oki_measure_11,
869 oki_measure_13,
870 fact.chr_id OKI_ATTRIBUTE_5
871 FROM (SELECT *
872 FROM (
873 SELECT
874 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
875 chr_id,
876 customer_party_id,
877 resource_id,
878 oki_measure_1,
879 oki_measure_3,
880 oki_measure_11,
881 oki_measure_13
882 FROM (
883 SELECT
884 oset5.chr_id ,
885 oset5.customer_party_id ,
886 oset5.resource_id ,
887 nvl(oset5.affected_full_value,0) OKI_MEASURE_1,
888 nvl(oset5.affected_forecast_value,0) OKI_MEASURE_3,
889 nvl(oset5.affected_full_value_total,0) OKI_MEASURE_11,
890 nvl(oset5.affected_forecast_value_total,0) OKI_MEASURE_13
891 FROM
892 (SELECT
893 fact.chr_id,
894 fact.customer_party_id,
895 fact.resource_id';
896 RETURN l_query;
897
898 END get_renwlforecast_sel_clause;
899
900
901 /* This procedure generates the entire SQL query that is required for the report
902 * Renewal Expected Bookings By Customer.
903 *
904 * p_param -->a table populated by PMV which contains all the parameters that
905 * the user selects in the report
906 * x_custom_sql -->the final SQL query that is generated
907 * x_custom_output -->contains the bind variables
908 */
909
910 PROCEDURE get_exp_bkngs_by_cust_sql (
911 p_param IN bis_pmv_page_parameter_tbl
912 , x_custom_sql OUT NOCOPY VARCHAR2
913 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
914 IS
915 l_query VARCHAR2 (32767);
916 BEGIN
917 l_query := 'Hello';
918 x_custom_sql := '/* OKI_DBI_SRM_RSBK_DTL_CRPT */ ' || l_query;
919 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
920 END get_exp_bkngs_by_cust_sql;
921
922 /*******************************************************************************
923 Function: get_exp_bkngs_cust_sel_clause
924 Description: Function to get the top most select statement of the query
925
926 *******************************************************************************/
927 /*
928 FUNCTION get_exp_bkngs_cust_sel_clause
929 RETURN VARCHAR2
930 IS
931 l_query VARCHAR2(10000);
932 BEGIN
933
934 RETURN l_query;
935 END get_exp_bkngs_cust_sel_clause;
936 */
937
938
939 /*******************************************************************************
940 Function: get_late_rnwl_table_sql
941 Description: Function to get the Late Renewals Booking Report DBI 6.0
942
943 *******************************************************************************/
944
945 PROCEDURE get_late_rnwl_table_sql (
946 p_param IN bis_pmv_page_parameter_tbl
947 , x_custom_sql OUT NOCOPY VARCHAR2
948 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
949 IS
950
951 l_query VARCHAR2 (32767);
952 l_view_by VARCHAR2 (120);
953 l_view_by_col VARCHAR2 (120);
954 l_as_of_date DATE;
955 l_prev_as_of_date DATE;
956 l_xtd VARCHAR2 (10);
957 l_comparison_type VARCHAR2 (1) ;
958 l_period_type VARCHAR2(10);
959 l_nested_pattern NUMBER;
960 l_cur_suffix VARCHAR2 (2);
961 l_where_clause VARCHAR2 (2000);
962
963 l_filter_where VARCHAR2 (1000);
964
965 l_mv VARCHAR2 (2000);
966 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
967 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
968 l_to_date_xed VARCHAR2 (3) ;
972
969 l_to_date_xtd VARCHAR2 (3) ;
970 l_to_date_ytd VARCHAR2 (3) ;
971 l_to_date_itd VARCHAR2 (3) ;
973 l_group_by VARCHAR2(32000);
974
975
976 l_custom_rec BIS_QUERY_ATTRIBUTES ;
977
978 BEGIN
979 l_to_date_xed := 'XED';
980 l_to_date_xtd := 'XTD';
981 l_comparison_type := 'Y';
982 l_to_date_ytd := 'YTD';
983 l_to_date_itd := 'ITD';
984 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
985 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
986 oki_dbi_util_pvt.process_parameters (p_param => p_param
987 , p_view_by => l_view_by
988 , p_view_by_col_name => l_view_by_col
989 , p_comparison_type => l_comparison_type
990 , p_xtd => l_xtd
991 , p_as_of_date => l_as_of_date
992 , p_prev_as_of_date => l_prev_as_of_date
993 , p_cur_suffix => l_cur_suffix
994 , p_nested_pattern => l_nested_pattern
995 , p_where_clause => l_where_clause
996 , p_mv => l_mv
997 , p_join_tbl => l_join_tbl
998 , p_period_type => l_period_type
999 , p_trend => 'N'
1000 , p_func_area => 'OKI'
1001 , p_version => '6.0'
1002 , p_role => NULL
1003 , p_mv_set => 'SRM_SG_71'
1004 , p_rg_where => 'Y');
1005
1006
1007
1008 -- Populate col table with regular columns
1009
1010 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1011 , p_col_name => 'g_r_amt_' || l_cur_suffix
1012 , p_alias_name => 'Gr'
1013 , p_to_date_type => l_to_date_xtd);
1014
1015
1016 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1017 , p_col_name => 'gl_r_amt_' || l_cur_suffix
1018 , p_alias_name => 'Glr'
1019 , p_to_date_type => l_to_date_xtd);
1020
1021
1022 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1023 , p_col_name => 'gr_r_amt_' || l_cur_suffix
1024 , p_alias_name => 'Grr'
1025 , p_to_date_type => l_to_date_xtd);
1026
1027
1028 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1029 , p_col_name => 'gl_days'
1030 , p_alias_name => 'Gld'
1031 , p_to_date_type => l_to_date_xtd
1032 , p_prior_code => poa_dbi_util_pkg.no_priors);
1033
1034
1035 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1036 , p_col_name => 'gl_days_count'
1037 , p_alias_name => 'Gld_count'
1038 , p_to_date_type => l_to_date_xtd
1039 , p_prior_code => poa_dbi_util_pkg.no_priors);
1040
1041
1042 /* Additional filter needed to avoid displaying records queried due to total values at node */
1043 l_filter_where := ' ( ABS(oki_measure_1) + ABS(oki_measure_2) + ABS(oki_measure_5) ) <> 0 ';
1044 /* l_filter_where := ' ( oki_measure_1 +
1045 oki_measure_2 +
1046 oki_measure_3 +
1047 oki_measure_5 +
1048 oki_measure_6 +
1049 oki_measure_8
1050 ) <> 0 '; */
1051
1052 -- Generate sql query
1053
1054 l_query :=
1055 get_late_rnwl_table_sel_clause (l_view_by
1056 , l_view_by_col)
1057 || ' from '
1058 || poa_dbi_template_pkg.status_sql (
1059 p_fact_name => l_mv
1060 , p_where_clause => l_where_clause
1061 , p_filter_where => l_filter_where
1062 , p_join_tables => l_join_tbl
1063 , p_use_windowing => 'Y'
1064 , p_col_name => l_col_tbl
1065 , p_use_grpid => 'N'
1066 , p_paren_count => 7);
1067
1068 x_custom_sql := '/* OKI_DBI_SRM_LATE_BKNG_LRPT */ ' || l_query;
1069
1070 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1071
1072 END get_late_rnwl_table_sql;
1073
1074 /*
1075 Late Renewal Bookings table Select clause
1076 */
1077 FUNCTION get_late_rnwl_table_sel_clause (
1078 p_view_by_dim IN VARCHAR2
1079 , p_view_by_col IN VARCHAR2)
1080 RETURN VARCHAR2
1081 IS
1085 l_bookings_url VARCHAR2(300);
1082 l_sel_clause VARCHAR2 (32767);
1083 l_viewby_select VARCHAR2(32767);
1084 l_url_select VARCHAR2(32767);
1086 l_late_rnwl_booking_url VARCHAR2(300);
1087 BEGIN
1088
1089
1090 l_viewby_select:= oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
1091
1092 -- Bookings URL when view by is Salesrep
1093 l_bookings_url := '''pFunctionName=OKI_DBI_SRM_RSBK_DTL_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
1094
1095 --l_bookings_url := '''pFunctionName=OKI_DBI_SRM_RSBK_DTL_DRPT''';
1096 l_late_rnwl_booking_url := '''pFunctionName=OKI_DBI_SRM_LATE_AGNG_LRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
1097 --l_late_rnwl_booking_url := '''pFunctionName=OKI_DBI_SRM_LATE_AGNG_LRPT''';
1098
1099 l_viewby_select:= l_viewby_select ||
1100 ', OKI_DYNAMIC_URL_1
1101 , OKI_DYNAMIC_URL_2
1102 , OKI_DYNAMIC_URL_3
1103 , oki_measure_1
1104 , oki_measure_2
1105 , oki_measure_3
1106 , oki_measure_4
1107 , oki_measure_5
1108 , oki_measure_6
1109 , oki_measure_7
1110 , oki_measure_8
1111 , oki_measure_11
1112 , oki_measure_12
1113 , oki_measure_13
1114 , oki_measure_14
1115 , oki_measure_15
1116 , oki_measure_16
1117 , oki_measure_17
1118 , oki_measure_18
1119 , oki_measure_23
1120 , oki_measure_26
1121 FROM (
1122 SELECT
1123 rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
1124 , OKI_DYNAMIC_URL_1
1125 , OKI_DYNAMIC_URL_2
1126 , OKI_DYNAMIC_URL_3
1127 , oki_measure_1
1128 , oki_measure_2
1129 , oki_measure_3
1130 , oki_measure_4
1131 , oki_measure_5
1132 , oki_measure_6
1133 , oki_measure_7
1134 , oki_measure_8
1135 , oki_measure_11
1136 , oki_measure_12
1137 , oki_measure_13
1138 , oki_measure_14
1139 , oki_measure_15
1140 , oki_measure_16
1141 , oki_measure_17
1142 , oki_measure_18
1143 , oki_measure_23
1144 , oki_measure_26
1145 FROM ( ';
1146
1147 -- disabling links for unassigned group
1148 -- ' , decode(resource_id,-999,'''','||l_late_rnwl_booking_url||') OKI_DYNAMIC_URL_2 '||
1149 -- ' , decode(resource_id,-999,'''','||l_bookings_url||') OKI_DYNAMIC_URL_3 ';
1150
1151
1152 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
1153 THEN
1154 l_url_select :=
1155 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_LATE_BKNG_LRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 '||
1156 ' , decode(resource_id,-999,'||l_late_rnwl_booking_url||',decode(rg_id,-1,'''','||l_late_rnwl_booking_url||')) OKI_DYNAMIC_URL_2 '||
1157 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_bookings_url||')) OKI_DYNAMIC_URL_3 ';
1158
1159
1160 ELSE
1161 l_url_select :=
1162 'SELECT '''' OKI_DYNAMIC_URL_1 '||
1163 ' , '''' OKI_DYNAMIC_URL_2 '||
1164 ' , '''' OKI_DYNAMIC_URL_3 ';
1165
1166 END IF;
1167
1168
1169
1170 l_sel_clause := l_viewby_select || l_url_select ||
1171 -- AK Attribute naming
1172 ' ,'|| p_view_by_col ||
1173 ' , oset20.C_Gr oki_measure_1 '||
1174 ' , oset20.c_Glr oki_measure_2 '||
1175 ' , oset20.c_late_rate oki_measure_3 '||
1176 ' , oset20.late_chg oki_measure_4 '||
1177 ' , oset20.c_Grr oki_measure_5 '||
1178 ' , oset20.c_grace_rate oki_measure_6 '||
1179 ' , oset20.grace_chg oki_measure_7 '||
1180 ' , oset20.c_avg_late oki_measure_8 '||
1181 ' , oset20.C_Gr_tot oki_measure_11 '||
1182 ' , oset20.c_Glr_tot oki_measure_12 '||
1183 ' , oset20.c_late_rate_tot oki_measure_13 '||
1184 ' , oset20.late_chg_tot oki_measure_14 '||
1185 ' , oset20.c_Grr_tot oki_measure_15 '||
1186 ' , oset20.c_grace_rate_tot oki_measure_16 '||
1187 ' , oset20.grace_chg_tot oki_measure_17 '||
1188 ' , oset20.c_avg_late_tot oki_measure_18 '||
1189 ' , oset20.p_late_rate oki_measure_23 '||
1190 ' , oset20.p_grace_rate oki_measure_26 '||
1191 ' from '||
1192 ' ( select '||
1193 -- Change Calculation
1194 ' '|| p_view_by_col ||
1195 ' , oset15.C_Gr '||
1196 ' , oset15.C_Gr_tot '||
1197 ' , oset15.c_Glr '||
1198 ' , oset15.c_Glr_tot '||
1199 ' , oset15.c_late_rate '||
1200 ' , oset15.p_late_rate '||
1201 ' , oset15.c_late_rate_tot '||
1202 ' ,'||oki_dbi_util_pvt.change_clause('oset15.c_late_rate','oset15.p_late_rate','P') || ' late_chg '||
1203 ' ,'||oki_dbi_util_pvt.change_clause('oset15.c_late_rate_tot','oset15.p_late_rate_tot','P') || ' late_chg_tot '||
1207 ' , oset15.p_grace_rate '||
1204 ' , oset15.c_Grr '||
1205 ' , oset15.c_Grr_tot '||
1206 ' , oset15.c_grace_rate '||
1208 ' , oset15.c_grace_rate_tot '||
1209 ' ,'||oki_dbi_util_pvt.change_clause('oset15.c_grace_rate','oset15.p_grace_rate','P') || ' grace_chg '||
1210 ' ,'||oki_dbi_util_pvt.change_clause('oset15.c_grace_rate_tot','oset15.p_grace_rate_tot','P') || ' grace_chg_tot '||
1211 ' , oset15.c_avg_late '||
1212 ' , oset15.c_avg_late_tot '||
1213 ' from '||
1214 ' (select '||
1215 -- Calculated Measures
1216 p_view_by_col ||
1217 ' , oset13.c_Gr '||
1218 ' , oset13.c_Gr_tot '||
1219 ' , oset13.c_Glr '||
1220 ' , oset13.c_Glr_tot '||
1221 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_Glr','oset13.c_Gr') || 'c_late_Rate '||
1222 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_Glr','oset13.p_Gr') || 'p_late_Rate '||
1223 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_Glr_tot','oset13.c_Gr_tot') || 'c_late_Rate_tot '||
1224 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_Glr_tot','oset13.p_Gr_tot') || 'p_late_Rate_tot '||
1225 ' , oset13.c_Grr '||
1226 ' , oset13.c_Grr_tot '||
1227 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_Grr','oset13.c_Gr') || 'c_grace_Rate '||
1228 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_Grr','oset13.p_Gr') || 'p_grace_Rate '||
1229 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_Grr_tot','oset13.c_Gr_tot') || 'c_grace_Rate_tot '||
1230 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_Grr_tot','oset13.p_Gr_tot') || 'p_grace_Rate_tot '||
1231 ' , NVL(oset13.c_avg_late,0) c_avg_late '||
1232 ' , NVL(oset13.c_avg_late_tot,0) c_avg_late_tot '||
1233 ' from '||
1234 ' (select '||
1235 -- Measures Based on a formula
1236 p_view_by_col ||
1237 ' , oset10.c_Gr '||
1238 ' , oset10.p_Gr '||
1239 ' , oset10.c_Gr_tot '||
1240 ' , oset10.p_Gr_tot '||
1241 ' , oset10.c_Glr '||
1242 ' , oset10.p_Glr '||
1243 ' , oset10.c_Glr_tot '||
1244 ' , oset10.p_Glr_tot '||
1245 ' , oset10.c_Grr '||
1246 ' , oset10.p_Grr '||
1247 ' , oset10.c_Grr_tot '||
1248 ' , oset10.p_Grr_tot '||
1249 ' ,'||POA_DBI_UTIL_PKG.rate_clause('NVL(oset10.c_Gld,0)','oset10.c_Gld_count','NP') || 'c_avg_late '||
1250 ' ,'||POA_DBI_UTIL_PKG.rate_clause('NVL(oset10.c_Gld_tot,0)','oset10.c_Gld_count_tot','NP') || 'c_avg_late_tot '||
1251 ' from '||
1252 ' ( select '||
1253 ' oset05.'||p_view_by_col ||
1254 ' , nvl(oset05.c_Gr,0) c_Gr '||
1255 ' , nvl(oset05.p_Gr,0) p_Gr '||
1256 ' , nvl(oset05.c_Gr_total,0) c_Gr_tot '||
1257 ' , nvl(oset05.p_Gr_total,0) p_Gr_tot '||
1258 ' , nvl(oset05.c_Glr,0) c_Glr '||
1259 ' , nvl(oset05.p_Glr,0) p_Glr '||
1260 ' , nvl(oset05.c_Glr_total,0) c_Glr_tot '||
1261 ' , nvl(oset05.p_Glr_total,0) p_Glr_tot '||
1262 ' , nvl(oset05.c_Grr,0) c_Grr '||
1263 ' , nvl(oset05.p_Grr,0) p_Grr '||
1264 ' , nvl(oset05.c_Grr_total,0) c_Grr_tot '||
1265 ' , nvl(oset05.p_Grr_total,0) p_Grr_tot '||
1266 ' , nvl(oset05.c_Gld,0) c_Gld '||
1267 ' , nvl(oset05.c_Gld_total,0) c_Gld_tot '||
1268 ' , nvl(oset05.c_Gld_count,0) c_Gld_count '||
1269 ' , nvl(oset05.c_Gld_count_total,0) c_Gld_count_tot ';
1270
1271
1272 RETURN l_sel_clause;
1273
1274 END get_late_rnwl_table_sel_clause;
1275
1276 /*******************************************************************************
1277 Function: get_cncl_table_sql
1278 Description: Function to get Renewals Cancellations Summary Report DBI 6.0
1279 *******************************************************************************/
1280
1281 PROCEDURE get_cncl_table_sql (
1282 p_param IN bis_pmv_page_parameter_tbl
1283 , x_custom_sql OUT NOCOPY VARCHAR2
1284 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
1285 IS
1286
1287 l_query VARCHAR2 (32767);
1288 l_view_by VARCHAR2 (120);
1289 l_view_by_col VARCHAR2 (120);
1290 l_as_of_date DATE;
1291 l_prev_as_of_date DATE;
1292 l_xtd VARCHAR2 (10);
1293 l_comparison_type VARCHAR2 (1);
1294 l_period_type VARCHAR2(10);
1295 l_nested_pattern NUMBER;
1296 l_cur_suffix VARCHAR2 (2);
1297 l_where_clause VARCHAR2 (2000);
1298
1299 l_filter_where VARCHAR2 (1000);
1300
1301 l_mv VARCHAR2 (2000);
1302 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1303 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1304 l_to_date_xed VARCHAR2 (3) ;
1305 l_to_date_xtd VARCHAR2 (3);
1306 l_to_date_ytd VARCHAR2 (3);
1307 l_to_date_itd VARCHAR2 (3);
1311 l_mv_1 VARCHAR2(100);
1308 g_rs_group_id number;
1309 g_resource_id number;
1310
1312 l_mv_2 VARCHAR2(100);
1313
1314 l_url_1 VARCHAR2(32000);
1315 l_url_2 VARCHAR2(32000);
1316
1317 l_dim_where VARCHAR2(32000);
1318 -- l_inner_view_by_id VARCHAR2(32000);
1319 -- l_inner_group_by VARCHAR2(32000);
1320 l_group_by VARCHAR2(32000);
1321
1322 l_pc_flag VARCHAR2(100);
1323 l_pc_flag_where VARCHAR2(32000);
1324 l_ou_flag VARCHAR2(100);
1325 l_ou_flag_where VARCHAR2(32000);
1326 l_sg VARCHAR2(32000);
1327 l_sg_where VARCHAR2(32000);
1328 l_sg_select_cust VARCHAR2(32000);
1329 l_sg_groupby_cust VARCHAR2(32000);
1330
1331 l_pseudo_rs_group VARCHAR2 (200);
1332 l_sep NUMBER;
1333
1334 l_org VARCHAR2(32000);
1335 l_org_where VARCHAR2(32000);
1336 l_org_where2 VARCHAR2(32000);
1337
1338 l_prod VARCHAR2(32000);
1339 l_prod_where VARCHAR2(32000);
1340
1341 l_cancel VARCHAR2(32000);
1342 l_cancel_where VARCHAR2(32000);
1343 l_prod_cat VARCHAR2(32000);
1344 l_prod_cat_where VARCHAR2(32000);
1345
1346 l_cust VARCHAR2(32000);
1347 l_cust_where VARCHAR2(32000);
1348
1349 l_curr VARCHAR2(50);
1350
1351 l_lang VARCHAR2(10);
1352 l_custom_rec BIS_QUERY_ATTRIBUTES ;
1353
1354 BEGIN
1355 l_to_date_xed := 'XED';
1356 l_to_date_xtd := 'XTD';
1357 l_comparison_type := 'Y';
1358 l_to_date_ytd := 'YTD';
1359 l_to_date_itd := 'ITD';
1360 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1361 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1362 oki_dbi_util_pvt.process_parameters ( p_param => p_param
1363 , p_view_by => l_view_by
1364 , p_view_by_col_name => l_view_by_col
1365 , p_comparison_type => l_comparison_type
1366 , p_xtd => l_xtd
1367 , p_as_of_date => l_as_of_date
1368 , p_prev_as_of_date => l_prev_as_of_date
1369 , p_cur_suffix => l_cur_suffix
1370 , p_nested_pattern => l_nested_pattern
1371 , p_where_clause => l_where_clause
1372 , p_mv => l_mv
1373 , p_join_tbl => l_join_tbl
1374 , p_period_type => l_period_type
1375 , p_trend => 'N'
1376 , p_func_area => 'OKI'
1377 , p_version => '6.0'
1378 , p_role => NULL
1379 , p_mv_set => 'SRM_CN_71'
1380 , p_rg_where => 'Y');
1381 --, p_rpt_type => 'SUMMARY'
1382
1383 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1384 , p_col_name => 'c_r_amt_' || l_cur_suffix
1385 , p_alias_name => 'Cr'
1386 , p_to_date_type => l_to_date_xtd);
1387
1388 /* Additional filter needed to avoid displaying records queried due to total values at node */
1389 l_filter_where := ' ( ABS(oki_measure_1) + ABS(oki_measure_21) ) <> 0 ';
1390 -- Generate sql query
1391 l_query :=
1392 get_cncl_table_sel_clause ( l_view_by
1393 , l_view_by_col)
1394 || ' from '
1395 || poa_dbi_template_pkg.status_sql (
1396 p_fact_name => l_mv
1397 , p_where_clause => l_where_clause
1398 , p_filter_where => l_filter_where
1399 , p_join_tables => l_join_tbl
1400 , p_use_windowing => 'Y'
1401 , p_col_name => l_col_tbl
1402 , p_use_grpid => 'N'
1403 , p_paren_count => 7);
1404 x_custom_sql := '/* OKI_DBI_SRM_CNCL_SUM_RPT */' || l_query;
1405 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1406
1407 END get_cncl_table_sql;
1408
1409
1410 FUNCTION get_cncl_table_sel_clause (
1411 p_view_by_dim IN VARCHAR2
1412 , p_view_by_col IN VARCHAR2)
1413 RETURN VARCHAR2
1414 IS
1415 l_sel_clause VARCHAR2 (32767);
1416 l_viewby_select VARCHAR2(32767);
1417 l_url_select VARCHAR2(32767);
1418 l_cancelled_value_url VARCHAR2(300);
1419 l_cancelled_value_reason_url VARCHAR2(300);
1420 l_prodcat_url VARCHAR2(300);
1421
1422 BEGIN
1423
1424
1425 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
1426
1430 IF(p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT')
1427 l_cancelled_value_url := '''pFunctionName=OKI_DBI_SRM_CNCL_DTL_LRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
1428 l_cancelled_value_reason_url:= '''pFunctionName=OKI_DBI_SRM_CNCL_DTL_LRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
1429
1431 THEN
1432 l_prodcat_url :=
1433 ' decode(leaf_node_flag,''Y''
1434 , ''pFunctionName=OKI_DBI_SRM_CNCL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM''
1435 ,''pFunctionName=OKI_DBI_SRM_CNCL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT'' ) OKI_DYNAMIC_URL_3 ';
1436 ELSE
1437 l_prodcat_url := ''''' OKI_DYNAMIC_URL_3 ';
1438 END IF;
1439
1440 l_viewby_select := l_viewby_select ||
1441 ', OKI_DYNAMIC_URL_1
1442 , OKI_DYNAMIC_URL_2
1443 ,' ||l_prodcat_url || '
1444 , oki_measure_1
1445 , oki_measure_2
1446 , oki_measure_3
1447 , oki_measure_11
1448 , oki_measure_12
1449 , oki_measure_13
1450 , oki_measure_21
1451 FROM (SELECT
1452 rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
1453 ,OKI_DYNAMIC_URL_1
1454 ,OKI_DYNAMIC_URL_2
1455 ,oki_measure_1
1456 ,oki_measure_2
1457 ,oki_measure_3
1458 ,oki_measure_11
1459 ,oki_measure_12
1460 ,sum(oki_measure_3) over() oki_measure_13
1461 ,oki_measure_21
1462 FROM ( ';
1463
1464
1465 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
1466 THEN
1467 l_url_select :=
1468 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_CNCL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 '||
1469 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_cancelled_value_url||')) OKI_DYNAMIC_URL_2 ';
1470
1471 ELSIF (p_view_by_dim IN ('OKI_STATUS+CNCL_REASON','ITEM+ENI_ITEM'))
1472 THEN
1473 l_url_select :=
1474 'SELECT '''' OKI_DYNAMIC_URL_1 '||
1475 ' , '||l_cancelled_value_reason_url ||' OKI_DYNAMIC_URL_2 ';
1476 ELSE
1477 l_url_select :=
1478 'SELECT '''' OKI_DYNAMIC_URL_1 '||
1479 ' , '''' OKI_DYNAMIC_URL_2 ';
1480 END IF;
1481
1482 l_sel_clause := l_viewby_select || l_url_select ||
1483 -- AK Attribute naming
1484 ' ,'|| p_view_by_col ||
1485 ' , oset20.C_cncl oki_measure_1 '||
1486 ' , oset20.cncl_chg oki_measure_2 '||
1487 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset20.C_cncl','oset20.C_cncl_tot') || ' oki_measure_3 '||
1488 ' , oset20.C_cncl_tot oki_measure_11 '||
1489 ' , oset20.cncl_chg_tot oki_measure_12 '||
1490 ' , oset20.p_cncl oki_measure_21 '||
1491 ' from '||
1492 ' ( select '||
1493 -- Change Calculation
1494 ' '|| p_view_by_col ||
1495 ' , oset15.C_Cr C_cncl '||
1496 ' , oset15.P_Cr P_cncl '||
1497 ' ,'||oki_dbi_util_pvt.change_clause('oset15.C_Cr','oset15.P_Cr','NP') || ' cncl_chg '||
1498 ' , oset15.C_Cr_tot C_cncl_tot '||
1499 ' , oset15.P_Cr_tot P_cncl_tot '||
1500 ' ,'||oki_dbi_util_pvt.change_clause('oset15.C_Cr_tot','oset15.P_Cr_tot','NP') || ' cncl_chg_tot '||
1501 ' from '||
1502 ' (select '||
1503 -- Calculated Measures
1504 p_view_by_col ||
1505 ' , oset13.c_Cr '||
1506 ' , oset13.p_Cr '||
1507 ' , oset13.c_Cr_tot '||
1508 ' , oset13.p_Cr_tot '||
1509 ' from '||
1510 ' (select '||
1511 -- Measures Based on a formula
1512 p_view_by_col ||
1513 ' , oset10.c_Cr c_Cr '||
1514 ' , oset10.p_Cr p_Cr '||
1515 ' , oset10.c_Cr_tot c_Cr_tot '||
1516 ' , oset10.p_Cr_tot p_Cr_tot '||
1517 ' from '||
1518 ' ( select '||
1519 ' oset05.'||p_view_by_col ||
1520 ' , nvl(oset05.c_Cr,0) c_Cr '||
1521 ' , nvl(oset05.p_Cr,0) p_Cr '||
1522 ' , nvl(oset05.c_Cr_total,0) c_Cr_tot '||
1523 ' , nvl(oset05.p_Cr_total,0) p_Cr_tot ';
1524
1525
1526 RETURN l_sel_clause;
1527 END get_cncl_table_sel_clause;
1528
1529 /*******************************************************************************
1530 Function: get_cancellations_sql
1531 Description: Function to get Renewals Cancellations Summary Detail Report DBI 6.0
1532 *******************************************************************************/
1533
1534 PROCEDURE get_cancellations_sql (
1535 p_param IN bis_pmv_page_parameter_tbl
1536 , x_custom_sql OUT NOCOPY VARCHAR2
1537 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
1538 IS
1539
1540 l_query VARCHAR2 (32767);
1541 l_view_by VARCHAR2 (120);
1542 l_view_by_col VARCHAR2 (120);
1543 l_as_of_date DATE;
1544 l_prev_as_of_date DATE;
1545 l_xtd VARCHAR2 (10);
1546 l_comparison_type VARCHAR2 (1);
1547 l_period_type VARCHAR2(10);
1548 l_nested_pattern NUMBER;
1552 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1549 l_curr_suffix VARCHAR2 (2);
1550 l_where_clause VARCHAR2 (2000);
1551 l_mv VARCHAR2 (2000);
1553 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1554 l_to_date_xed VARCHAR2 (3);
1555 l_to_date_xtd VARCHAR2 (3);
1556
1557 l_rpt_specific_where VARCHAR2 (1000);
1558 l_join_where VARCHAR2 (1000);
1559 l_group_by VARCHAR2 (1000);
1560 l_filter_where VARCHAR2 (240);
1561
1562 BEGIN
1563
1564 l_to_date_xed := 'XED';
1565 l_to_date_xtd := 'XTD';
1566 l_comparison_type := 'Y';
1567 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1568 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1569 oki_dbi_util_pvt.process_parameters ( p_param => p_param
1570 , p_view_by => l_view_by
1571 , p_view_by_col_name => l_view_by_col
1572 , p_comparison_type => l_comparison_type
1573 , p_xtd => l_xtd
1574 , p_as_of_date => l_as_of_date
1575 , p_prev_as_of_date => l_prev_as_of_date
1576 , p_cur_suffix => l_curr_suffix
1577 , p_nested_pattern => l_nested_pattern
1578 , p_where_clause => l_where_clause
1579 , p_mv => l_mv
1580 , p_join_tbl => l_join_tbl
1581 , p_period_type => l_period_type
1582 , p_trend => 'N'
1583 , p_func_area => 'OKI'
1584 , p_version => '6.0'
1585 , p_role => NULL
1586 , p_mv_set => 'SRM_DTL_RPT'
1587 , p_rg_where => 'Y');
1588
1589 l_rpt_specific_where :=
1590 ' AND fact.renewal_flag in (1,3)
1591 AND fact.date_cancelled between &BIS_CURRENT_EFFECTIVE_START_DATE
1592 and &BIS_CURRENT_ASOF_DATE';
1593
1594 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id, fact.sts_code ';
1595
1596 poa_dbi_util_pkg.add_column ( p_col_tbl => l_col_tbl
1597 , p_col_name => 'price_negotiated_' || l_curr_suffix
1598 , p_alias_name => 'cancelled_value'
1599 , p_prior_code => poa_dbi_util_pkg.no_priors);
1600
1601 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1602
1603 oki_dbi_util_pvt.join_rpt_where ( p_join_tbl => l_join_tbl
1604 , p_func_area => 'SRM'
1605 , p_version => '6.0'
1606 , p_role => NULL
1607 , p_mv_set => 'SRM_DTL_RPT');
1608
1609 /* Additional filter needed to avoid displaying records queried due to total values at node */
1610 l_filter_where := ' ( ABS(oki_measure_1) ) <> 0 ';
1611
1612 oki_dbi_util_pvt.add_join_table (p_join_tbl => l_join_tbl
1613 , p_column_name => 'id'
1614 , p_table_name => 'OKI_CANCEL_STATUSES_V'
1615 , p_table_alias => 'v'
1616 , p_fact_column => 'sts_code'
1617 , p_additional_where_clause => NULL);
1618
1619
1620 l_query := get_cancellations_sel_clause (l_curr_suffix, l_period_type )
1621
1622 || poa_dbi_template_pkg.dtl_status_sql2 (
1623 p_fact_name => l_mv
1624 , p_where_clause => l_where_clause || l_rpt_specific_where
1625 , p_join_tables => l_join_tbl
1626 , p_use_windowing => 'Y'
1627 , p_col_name => l_col_tbl
1628 , p_use_grpid => 'N'
1629 , p_filter_where => l_filter_where
1630 , p_paren_count => 5
1631 , p_group_by => l_group_by
1632 , p_from_clause => ' from '||l_mv ||' fact ');
1633 x_custom_sql := '/* OKI_DBI_SRM_CNCL_DTL_LRPT */' || l_query;
1634
1635
1636 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1637
1638 END get_cancellations_sql;
1639
1640 FUNCTION get_cancellations_sel_clause (
1641 p_cur_suffix IN VARCHAR2
1642 , p_period_type_code IN VARCHAR2)
1643 RETURN VARCHAR2
1644 IS
1645 l_query VARCHAR2 (10000);
1646
1647
1648 BEGIN
1649
1650 -- Generate sql query
1651 l_query :=
1652 '
1653 SELECT
1657 v.value oki_attribute_4,
1654 k.complete_k_number oki_attribute_1,
1655 cust.value oki_attribute_2,
1656 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
1658 to_char(k.start_date) OKI_DATE_1,
1659 to_char(fact.date_cancelled) OKI_DATE_2,
1660 k. price_nego_g oki_measure_2,
1661 oki_measure_1,
1662 oki_measure_11,
1663 fact.chr_id OKI_ATTRIBUTE_5
1664 FROM (select *
1665 FROM (
1666 SELECT
1667 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
1668 chr_id,
1669 customer_party_id,
1670 resource_id,
1671 date_cancelled,
1672 oki_measure_1,
1673 oki_measure_11,
1674 oki_attribute_4 sts_code
1675 FROM (
1676 SELECT oset5.chr_id ,
1677 oset5.customer_party_id ,
1678 oset5.resource_id ,
1679 oset5.date_cancelled,
1680 nvl(oset5.cancelled_value,0) OKI_MEASURE_1,
1681 nvl(oset5.cancelled_value_total,0) OKI_MEASURE_11,
1682 oset5.sts_code oki_attribute_4
1683 FROM
1684 (SELECT
1685 fact.chr_id,
1686 fact.customer_party_id,
1687 fact.resource_id,
1688 min(fact.date_cancelled) date_cancelled,
1689 fact.sts_code';
1690 RETURN l_query;
1691 END get_cancellations_sel_clause;
1692
1693 /* This procedure generates the entire SQL query that is required for the report
1694 * Renewal Cancellations By Customer.
1695 *
1696 * p_param -->a table populated by PMV which contains all the parameters that
1697 * the user selects in the report
1698 * x_custom_sql -->the final SQL query that is generated
1699 * x_custom_output -->contains the bind variables
1700 */
1701
1702 PROCEDURE get_cancln_by_cust_sql (
1703 p_param IN bis_pmv_page_parameter_tbl
1704 , x_custom_sql OUT NOCOPY VARCHAR2
1705 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
1706 IS
1707 l_query VARCHAR2 (32767);
1708 BEGIN
1709 l_query := 'Hello';
1710 x_custom_sql := '/* OKI_DBI_SRM_RSBK_DTL_CRPT */ ' || l_query;
1711 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1712 END get_cancln_by_cust_sql;
1713
1714
1715
1716 /*******************************************************************************
1717 Function: get_cancln_by_cust_sel_clause
1718 Description: Function to get top most select portion of the SQL statment
1719 *******************************************************************************/
1720
1721 /*
1722 FUNCTION get_cancln_by_cust_sel_clause
1723 RETURN VARCHAR2
1724 IS
1725 l_query VARCHAR2(10000);
1726
1727 BEGIN
1728
1729 RETURN l_query;
1730 END get_cancln_by_cust_sel_clause;
1731 */
1732
1733
1734
1735 /*******************************************************************************
1736 Function: get_bucket_sql
1737 Description: Function to get Late Renewal Bookings Aging Report DBI 6.0
1738 *******************************************************************************/
1739
1740 PROCEDURE get_bucket_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1741 x_custom_sql OUT NOCOPY VARCHAR2,
1742 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1743
1744 l_query VARCHAR2 (32767);
1745 l_view_by VARCHAR2 (120);
1746 l_view_by_col VARCHAR2 (120);
1747 l_as_of_date DATE;
1748 l_prev_as_of_date DATE;
1749 l_xtd VARCHAR2 (10);
1750 l_comparison_type VARCHAR2 (1) ;
1751 l_period_type VARCHAR2(10);
1752 l_nested_pattern NUMBER;
1753 l_dim_bmap NUMBER;
1754 l_cur_suffix VARCHAR2 (2);
1755 l_custom_sql VARCHAR2 (32767);
1756 l_custom_rec bis_query_attributes;
1757 l_where_clause VARCHAR2 (2000);
1758 l_mv VARCHAR2 (2000);
1759 l_col_rec poa_dbi_util_pkg.poa_dbi_col_rec;
1760 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1761 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1762 l_url VARCHAR2 (500);
1763 l_to_date_xed VARCHAR2 (3) ;
1764 l_to_date_xtd VARCHAR2 (3);
1765
1766 l_curr_sql VARCHAR2(32767) ;
1767 l_rep_sql VARCHAR2(32767) ;
1768 l_bucket_rec bis_bucket_pub.BIS_BUCKET_REC_TYPE;
1769 l_error_tbl bis_utilities_pub.ERROR_TBL_TYPE;
1770 l_status VARCHAR2(10000);
1771
1772 --Amount columns
1773 l_b1_amt VARCHAR2(20) ;
1774 l_b2_amt VARCHAR2(20) ;
1775 l_b3_amt VARCHAR2(20) ;
1776 l_b4_amt VARCHAR2(20) ;
1777 l_b5_amt VARCHAR2(20) ;
1778 l_b6_amt VARCHAR2(20) ;
1779 l_b7_amt VARCHAR2(20) ;
1780 l_b8_amt VARCHAR2(20) ;
1781 l_b9_amt VARCHAR2(20) ;
1782 l_b10_amt VARCHAR2(20) ;
1783
1784 -- Contains the query
1785
1786 BEGIN
1787
1788 l_to_date_xed := 'XED';
1792 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1789 l_to_date_xtd := 'XTD';
1790 l_comparison_type := 'Y';
1791 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1793 oki_dbi_util_pvt.process_parameters (p_param => p_param
1794 , p_view_by => l_view_by
1795 , p_view_by_col_name => l_view_by_col
1796 , p_comparison_type => l_comparison_type
1797 , p_xtd => l_xtd
1798 , p_as_of_date => l_as_of_date
1799 , p_prev_as_of_date => l_prev_as_of_date
1800 , p_cur_suffix => l_cur_suffix
1801 , p_nested_pattern => l_nested_pattern
1802 , p_where_clause => l_where_clause
1803 , p_mv => l_mv
1804 , p_join_tbl => l_join_tbl
1805 , p_period_type => l_period_type
1806 , p_trend => 'N'
1807 , p_func_area => 'OKI'
1808 , p_version => '6.0'
1809 , p_role => NULL
1810 , p_mv_set => 'SRM_LATE_BKING'
1811 , p_rg_where => 'Y');
1812
1813 l_b1_amt := 'bucket1_amt_' || l_cur_suffix ;
1814 l_b2_amt := 'bucket2_amt_' || l_cur_suffix ;
1815 l_b3_amt := 'bucket3_amt_' || l_cur_suffix ;
1816 l_b4_amt := 'bucket4_amt_' || l_cur_suffix ;
1817 l_b5_amt := 'bucket5_amt_' || l_cur_suffix ;
1818 l_b6_amt := 'bucket6_amt_' || l_cur_suffix ;
1819 l_b7_amt := 'bucket7_amt_' || l_cur_suffix ;
1820 l_b8_amt := 'bucket8_amt_' || l_cur_suffix ;
1821 l_b9_amt := 'bucket9_amt_' || l_cur_suffix ;
1822 l_b10_amt := 'bucket10_amt_' || l_cur_suffix ;
1823
1824 -- Retrieve record to get bucket labels
1825 bis_bucket_pub.RETRIEVE_BIS_BUCKET('OKI_DBI_SRM_LATE_AGING', l_bucket_rec, l_status, l_error_tbl);
1826
1827 /* sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1828 (case when ' || l_b1_amt || '> 0
1829 then fact.bucket1_cnt_g
1830 else 0 end), 0)) B1_cnt,
1831 */
1832
1833 l_curr_sql := 'SELECT
1834 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1835 fact.bucket1_cnt_g, 0)) B1_cnt,
1836 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1837 fact.bucket2_cnt_g, 0)) b2_cnt,
1838 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1839 fact.bucket3_cnt_g, 0)) b3_cnt,
1840 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1841 fact.bucket4_cnt_g, 0)) b4_cnt,
1842 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1843 fact.bucket5_cnt_g, 0)) b5_cnt,
1844 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1845 fact.bucket6_cnt_g, 0)) b6_cnt,
1846 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1847 fact.bucket7_cnt_g, 0)) b7_cnt,
1848 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1849 fact.bucket8_cnt_g, 0)) b8_cnt,
1850 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1851 fact.bucket9_cnt_g, 0)) b9_cnt,
1852 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1853 fact.bucket10_cnt_g, 0)) b10_cnt,
1854
1855 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b1_amt || ', 0)) B1,
1856 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b2_amt || ', 0)) b2,
1857 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b3_amt || ', 0)) b3,
1858 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b4_amt || ', 0)) b4,
1859 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b5_amt || ', 0)) b5,
1860 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b6_amt || ', 0)) b6,
1861 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b7_amt || ', 0)) b7,
1862 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b8_amt || ', 0)) b8,
1863 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b9_amt || ', 0)) b9,
1864 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,' || l_b10_amt || ', 0)) b10,
1865
1866 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b1_amt || ', 0)) B1_p,
1867 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b2_amt || ', 0)) b2_p,
1868 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b3_amt || ', 0)) b3_p,
1869 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b4_amt || ', 0)) b4_p,
1870 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b5_amt || ', 0)) b5_p,
1874 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b9_amt || ', 0)) b9_p,
1871 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b6_amt || ', 0)) b6_P,
1872 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b7_amt || ', 0)) b7_p,
1873 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b8_amt || ', 0)) b8_P,
1875 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,' || l_b10_amt || ', 0)) b10_p
1876
1877 FROM '|| l_mv || ' fact,
1878 FII_TIME_RPT_STRUCT_V cal
1879 WHERE fact.time_id(+) = cal.time_id
1880 and fact.ren_type=''REN''
1881 ' || l_where_clause || '
1882 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
1883 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
1884 AND fact.grp_id = DECODE(cal.period_type_id
1885 ,1 ,14
1886 ,16 ,13
1887 ,32 ,11
1888 ,64 ,7 ) ' ;
1889
1890 l_rep_sql := 'SELECT decode(rownum,
1891 1, &RANGE1_NAME ,
1892 2, &RANGE2_NAME ,
1893 3, &RANGE3_NAME ,
1894 4, &RANGE4_NAME ,
1895 5, &RANGE5_NAME ,
1896 6, &RANGE6_NAME ,
1897 7, &RANGE7_NAME ,
1898 8, &RANGE8_NAME ,
1899 9, &RANGE9_NAME ,
1900 10,&RANGE10_NAME, null) BUCKET,
1901 rownum BUCKET_TYPE,
1902 decode(rownum,
1903 1,b1_cnt,
1904 2, b2_cnt,
1905 3, b3_cnt,
1906 4, b4_cnt,
1907 5, b5_cnt,
1908 6, b6_cnt,
1909 7, b7_cnt,
1910 8, b8_cnt,
1911 9, b9_cnt,
1912 10, b10_cnt, null) line_CNT
1913 ,decode(rownum,
1914 1, b1,
1915 2, b2,
1916 3, b3,
1917 4, b4,
1918 5, b5,
1919 6, b6,
1920 7, b7,
1921 8, b8,
1922 9, b9,
1923 10,b10, null) curr_late
1924 ,decode(rownum,
1925 1, b1_p,
1926 2, b2_p,
1927 3, b3_p,
1928 4, b4_p,
1929 5, b5_p,
1930 6, b6_p,
1931 7, b7_p,
1932 8, b8_p,
1933 9, b9_p,
1934 10,b10_p, null) prior_late
1935 FROM (' ||l_curr_sql ||'),
1936 (SELECT id from oki_dbi_multiplexer_b where id < 11)';
1937
1938 l_query := 'SELECT
1939 bucket OKI_ATTRIBUTE_3
1940 ,bucket_type OKI_MEASURE_23
1941 ,NVL(line_cnt,0) OKI_MEASURE_1
1942 ,nvl(curr_late,0) OKI_MEASURE_2
1943 ,nvl(prior_late,0) OKI_MEASURE_3
1944 ,'||oki_dbi_util_pvt.change_clause('curr_late','prior_late','NP') ||' OKI_MEASURE_4
1945 ,nvl((sum(curr_late) over ()),0) OKI_MEASURE_12
1946 ,nvl((curr_late /decode(sum(curr_late) over (),0,NULL,sum(curr_late) over ())*100),0) OKI_MEASURE_5
1947 ,nvl((curr_late /decode(sum(curr_late) over (),0,NULL,sum(curr_late) over ())*100),0) OKI_MEASURE_6
1948 ,(nvl((sum(curr_late) over ()),0) - (sum(prior_late) over ()))/ decode(sum(prior_late) over (),0,NULL,sum(prior_late) over ()) *100 OKI_MEASURE_14
1949 ,nvl((sum(line_cnt) over ()),0) OKI_MEASURE_11
1950 ,nvl((sum(curr_late) over () /decode(sum(curr_late) over (),0,NULL,sum(curr_late) over ()) *100),0) OKI_MEASURE_15
1951
1952 FROM ( ' || l_rep_sql || '
1953 )c
1954 WHERE BUCKET IS NOT NULL
1955 ORDER BY BUCKET_TYPE ';
1956
1957 x_custom_sql := '/* OKI_DBI_SRM_LATE_AGING_LRPT */' || l_query;
1958
1959 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1960 oki_dbi_util_pvt.get_bis_bucket_binds (x_custom_output, l_bucket_rec);
1961
1962 END get_bucket_sql ;
1963
1964 /*******************************************************************************
1965 Function: get_bkng_trend_sql (get_forecast_trend_sql)
1966 Description: Function for the Bookings Trend Forecast graph in DBI 6.0
1967 *******************************************************************************/
1968
1969 PROCEDURE get_bkng_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1970 x_custom_sql OUT NOCOPY VARCHAR2,
1971 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1972
1973 -- Variables associated with the parameter portlet
1974 l_query VARCHAR2 (32767);
1975 l_view_by VARCHAR2 (120);
1976 l_view_by_col VARCHAR2 (120);
1977 l_as_of_date DATE;
1978 l_prev_as_of_date DATE;
1979 l_xtd VARCHAR2 (10);
1983 l_cur_suffix VARCHAR2 (2);
1980 l_comparison_type VARCHAR2 (1) ;
1981 l_nested_pattern NUMBER;
1982 l_dim_bmap NUMBER;
1984 l_custom_sql VARCHAR2 (10000);
1985 l_period_type VARCHAR2(10);
1986 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1987 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1988
1989 l_period_code varchar2(1);
1990 l_where_clause1 VARCHAR2 (2000);
1991 l_where_clause2 VARCHAR2 (2000);
1992 l_mv VARCHAR2 (2000);
1993 l_to_date_xtd VARCHAR2 (3) ;
1994 l_to_date_xed VARCHAR2 (3) ;
1995 l_mv1 VARCHAR2(100);
1996 l_mv2 VARCHAR2(100);
1997 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
1998 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
1999 l_xtd1 VARCHAR2(10);
2000 l_xtd2 VARCHAR2(10);
2001 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
2002
2003 BEGIN
2004
2005 l_to_date_xed := 'XED';
2006 l_to_date_xtd := 'XTD';
2007 l_comparison_type := 'Y';
2008
2009 l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl ();
2010 l_col_tbl1 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
2011 l_col_tbl2 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
2012 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
2013
2014 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
2015 , p_view_by => l_view_by
2016 , p_view_by_col_name => l_view_by_col
2017 , p_comparison_type => l_comparison_type
2018 , p_xtd => l_xtd1
2019 , p_as_of_date => l_as_of_date
2020 , p_prev_as_of_date => l_prev_as_of_date
2021 , p_cur_suffix => l_cur_suffix
2022 , p_nested_pattern => l_nested_pattern
2023 , p_where_clause => l_where_clause1
2024 , p_mv => l_mv1
2025 , p_join_tbl => l_join_tbl
2026 , p_period_type => l_period_type
2027 , p_trend => 'Y'
2028 , p_func_area => 'OKI'
2029 , p_version => '6.0'
2030 , p_role => NULL
2031 , p_mv_set => 'SRM_SG_71'
2032 , p_rg_where => 'Y');
2033
2034 -- Populate col table with regular columns
2035 -- Period Renewal node
2036 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
2037 , p_col_name => 'g_r_amt_' || l_cur_suffix
2038 , p_alias_name => 'g_r_amt_xtd'
2039 , p_grand_total => 'N'
2040 , p_to_date_type => 'XTD');
2041 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
2042 , p_col_name => 'g_r_amt_' || l_cur_suffix
2043 , p_alias_name => 'g_r_amt_tot'
2044 , p_grand_total => 'N'
2045 , p_to_date_type => 'XED');
2046
2047 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
2048 , p_view_by => l_view_by
2049 , p_view_by_col_name => l_view_by_col
2050 , p_comparison_type => l_comparison_type
2051 , p_xtd => l_xtd2
2052 , p_as_of_date => l_as_of_date
2053 , p_prev_as_of_date => l_prev_as_of_date
2054 , p_cur_suffix => l_cur_suffix
2055 , p_nested_pattern => l_nested_pattern
2056 , p_where_clause => l_where_clause2
2057 , p_mv => l_mv2
2058 , p_join_tbl => l_join_tbl
2059 , p_period_type => l_period_type
2060 , p_trend => 'Y'
2061 , p_func_area => 'OKI'
2062 , p_version => '6.0'
2063 , p_role => NULL
2064 , p_mv_set => 'SRM_EC_71'
2065 , p_rg_where => 'Y');
2066 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
2067 , p_col_name => 'f_f_amt_' || l_cur_suffix
2068 , p_alias_name => 'f_f_amt_xed'
2069 , p_grand_total => 'N'
2070 , p_to_date_type => 'XED');
2071
2072 l_mv_tbl.extend;
2073 l_mv_tbl(1).mv_name := l_mv1;
2074 l_mv_tbl(1).mv_col := l_col_tbl1;
2075 l_mv_tbl(1).mv_where := l_where_clause1;
2076 l_mv_tbl(1).in_join_tbls := NULL;
2077 l_mv_tbl(1).use_grp_id := 'N';
2078 l_mv_tbl(1).mv_xtd := l_xtd1;
2079 l_mv_tbl.extend;
2080 l_mv_tbl(2).mv_name := l_mv2;
2081 l_mv_tbl(2).mv_col := l_col_tbl2;
2082 l_mv_tbl(2).mv_where := l_where_clause2;
2083 l_mv_tbl(2).in_join_tbls := NULL;
2084 l_mv_tbl(2).use_grp_id := 'N';
2085 l_mv_tbl(2).mv_xtd := l_xtd2;
2086 /* Additional filter needed to avoid displaying records queried due to total values at node */
2087 l_query := get_bkng_trend_sel_clause
2088 || ' from '
2089 || poa_dbi_template_pkg.union_all_trend_sql(
2090 p_mv => l_mv_tbl,
2091 p_comparison_type => l_comparison_type,
2092 p_filter_where => NULL);
2093 -- insert into brrao_temp values ( l_query);
2094 -- commit;
2095 /*
2096 l_query :=
2097 get_bkng_trend_sel_clause
2098 || ' from '
2099 || poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd
2100 , p_comparison_type => l_comparison_type
2101 , p_fact_name => l_mv
2102 , p_where_clause => l_where_clause
2103 , p_col_name => l_col_tbl
2104 , p_use_grpid => 'R');
2105 */
2106 x_custom_sql := '/* OKI_DBI_SRM_BKNG_FCST_G */ ' || l_query;
2107 oki_dbi_util_pvt.get_custom_trend_binds (l_xtd1
2108 , l_comparison_type
2109 , x_custom_output);
2110
2111 END get_bkng_trend_sql ;
2112
2113 /*******************************************************************************
2114 Function: get_bkng_trend_sql_clause
2115 Description: Top SQL layer function for Bookings Forecast Trend in DBI 6.0
2116 *******************************************************************************/
2117
2118 FUNCTION get_bkng_trend_sel_clause
2119 RETURN VARCHAR2
2120 IS
2121 l_sel_clause VARCHAR2 (10000);
2122 BEGIN
2123
2124 -- OKI_MEASURE_1 : Prior - shows for current period only
2125 -- OKI_MEASURE_2 : Prior Total -always show for all periods
2126 -- OKI_MEASURE_3 : Bookings - shows for current period only
2127 -- OKI_MEASURE_4 : Expected Bookings - shows for current period only
2128 -- OKI_MEASURE_5 : Current Total - shows for all prev periods only (except current)
2129 -- OKI_MEASURE_6 : Change (Bookings)
2130 /*
2131 l_sel_clause :=
2132 'Select cal.NAME AS VIEWBY
2133 ,(case when iset.start_date != &BIS_CURRENT_EFFECTIVE_START_DATE
2134 then iset.c_g_r_amt_tot else NULL END) OKI_MEASURE_5
2135 , nvl(iset.p_g_r_amt_tot,0) OKI_MEASURE_2
2136 , nvl(iset.c_g_r_amt_xtd,0) OKI_MEASURE_3
2137 , nvl(iset.p_g_r_amt_xtd,0) OKI_MEASURE_1
2138 , '||OKI_DBI_UTIL_PVT.change_clause('nvl(iset.c_g_r_amt_xtd,0)','iset.p_g_r_amt_xtd','NP')
2139 ||' OKI_MEASURE_6
2140 ,(case when iset.start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
2141 then nvl(iset.c_f_f_amt_xed,0) + nvl(iset.c_g_r_amt_xtd,0) else NULL
2142 END) OKI_MEASURE_4
2143 ';
2144 */
2145
2146 l_sel_clause :=
2147 'Select cal_name AS VIEWBY
2148 ,(case when cal_start_date != &BIS_CURRENT_EFFECTIVE_START_DATE
2149 then c_g_r_amt_tot else NULL END) OKI_MEASURE_5
2150 , nvl(p_g_r_amt_tot,0) OKI_MEASURE_2
2151 , nvl(c_g_r_amt_xtd,0) OKI_MEASURE_3
2152 , nvl(p_g_r_amt_xtd,0) OKI_MEASURE_1
2153 , '||OKI_DBI_UTIL_PVT.change_clause('nvl(c_g_r_amt_xtd,0)','p_g_r_amt_xtd','NP')
2154 ||' OKI_MEASURE_6
2155 ,(case when cal_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
2156 then nvl(c_f_f_amt_xed,0) + nvl(c_g_r_amt_xtd,0) else NULL
2157 END) OKI_MEASURE_4
2158 ';
2159
2160 RETURN l_sel_clause;
2161 END get_bkng_trend_sel_clause;
2162
2163
2164 END oki_dbi_srm_rnwl_pvt;