[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_NSCM_BAL_PVT
Source
1 PACKAGE BODY OKI_DBI_NSCM_BAL_PVT AS
2 /* $Header: OKIPNKPB.pls 120.4 2006/02/06 00:45:35 pubalasu noship $ */
3
4
5 FUNCTION get_bal_sel_clause (
6 p_view_by_dim IN VARCHAR2
7 , p_view_by_col IN VARCHAR2
8 , p_cur_suffix IN VARCHAR2)
9 RETURN VARCHAR2;
10
11 FUNCTION get_bal_trend_sel_clause (p_cur_suffix IN VARCHAR2)
12 RETURN VARCHAR2;
13
14 FUNCTION get_bal_detail_sel_clause (
15 p_cur_suffix IN VARCHAR2
16 , p_period_type_code IN VARCHAR2
17 , p_exp_type IN VARCHAR2)
18 RETURN VARCHAR2;
19
20 FUNCTION get_bal_itd_sql (
21 p_param IN bis_pmv_page_parameter_tbl
22 , p_trend_flag in VARCHAR2)
23 RETURN VARCHAR2;
24
25 FUNCTION get_bal_ytd_sql (
26 p_param IN bis_pmv_page_parameter_tbl
27 , p_trend_flag in VARCHAR2)
28 RETURN VARCHAR2;
29
30 FUNCTION get_trend_query (
31 p_itd IN VARCHAR2
32 ,p_ytd IN VARCHAR2
33 , p_xtd in VARCHAR2
34 ,p_cur_suffix in VARCHAR2)
35 RETURN VARCHAR2;
36 FUNCTION get_xtd_sql ( p_param IN bis_pmv_page_parameter_tbl
37 ,p_ptd in VARCHAR2
38 , p_trend_flag in VARCHAR2)
39 RETURN VARCHAR2;
40 Function get_xtd_sel_clause (p_ptd in VARCHAR2
41 ,p_view_by_col in VARCHAR2
42 , p_trend_flag in VARCHAR2 )
43 RETURN VARCHAR2;
44 --------------------------------------------------------------------
45 PROCEDURE get_balance_sql (
46 p_param IN bis_pmv_page_parameter_tbl
47 , x_custom_sql OUT NOCOPY VARCHAR2
48 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
49 IS
50
51 l_query VARCHAR2 (32767);
52 l_view_by VARCHAR2 (120);
53 l_view_by_col VARCHAR2 (120);
54 l_as_of_date DATE;
55 l_prev_as_of_date DATE;
56 l_xtd VARCHAR2 (10);
57 l_comparison_type VARCHAR2 (1);
58 l_period_type VARCHAR2(10);
59 l_nested_pattern NUMBER;
60 l_cur_suffix VARCHAR2 (2);
61 l_where_clause VARCHAR2 (2000);
62 l_filter_where VARCHAR2 (240);
63 l_mv VARCHAR2 (2000);
64 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
65 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
66 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
67 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
68 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
69 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
70 l_ytd_sql VARCHAR2(32767);
71 l_itd_sql VARCHAR2(32767);
72
73 l_sql VARCHAR2(32767);
74
75 BEGIN
76
77 l_comparison_type := 'Y';
78 l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl ();
79
80
81 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
82 , p_view_by => l_view_by
83 , p_view_by_col_name => l_view_by_col
84 , p_comparison_type => l_comparison_type
85 , p_xtd => l_xtd
86 , p_as_of_date => l_as_of_date
87 , p_prev_as_of_date => l_prev_as_of_date
88 , p_cur_suffix => l_cur_suffix
89 , p_nested_pattern => l_nested_pattern
90 , p_where_clause => l_where_clause
91 , p_mv => l_mv
92 , p_join_tbl => l_join_tbl
93 , p_period_type => l_period_type
94 , p_trend => 'N'
95 , p_func_area => 'OKI'
96 , p_version => '6.0'
97 , p_role => NULL
98 , p_mv_set => 'SRM_ST_71'
99 , p_rg_where => 'Y');
100
101
102 l_sql := get_xtd_sql (p_param,'XTD','N');
103 l_ytd_sql := get_xtd_sql (p_param,'YTD','N');
104
105 /* Additional filter needed to avoid displaying records queried due to total values at node */
106 l_filter_where := ' WHERE ( ABS(oki_pmeasure_1) + ABS(oki_measure_1) + ABS(oki_pmeasure_2) + ABS(oki_measure_2) ) <> 0 ';
107
108 -- Generate sql query
109 l_query :=
110 get_bal_sel_clause (l_view_by
111 , l_view_by_col
112 , l_cur_suffix)
113 || ' from ('
114 || l_sql
115 ||' UNION ALL ' || get_bal_itd_sql(p_param ,'N')
116 ||' UNION ALL ' || l_ytd_sql
117 ||' ) oset05 GROUP BY '|| l_view_by_col ||
118 ') oset10) oset13) oset15) oset20 ) ' || l_filter_where || ')oset , '
119 || poa_dbi_template_pkg.get_viewby_rank_clause ( p_join_tables => l_join_tbl
120 , p_use_windowing => 'Y' );
121
122 x_custom_sql := '/* OKI_DBI_SCM_BAL_SUM_RPT */ '||l_query;
123 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
124
125 END get_balance_sql;
126 --------------------------------------------------------------------
127
128 FUNCTION get_xtd_sql ( p_param IN bis_pmv_page_parameter_tbl
129 ,p_ptd in VARCHAR2
130 , p_trend_flag in VARCHAR2)
131 RETURN VARCHAR2 IS
132
133 l_view_by VARCHAR2 (120);
134 l_view_by_col VARCHAR2 (120);
135 l_as_of_date DATE;
136 l_prev_as_of_date DATE;
137 l_xtd VARCHAR2 (10);
138 l_comparison_type VARCHAR2 (1);
139 l_period_type VARCHAR2(10);
140 l_nested_pattern NUMBER;
141 l_cur_suffix VARCHAR2 (2);
142 l_where_clause1 VARCHAR2 (2000);
143 l_where_clause2 VARCHAR2 (2000);
144 l_where_clause3 VARCHAR2 (2000);
145 l_where_clause4 VARCHAR2 (2000);
146 l_filter_where VARCHAR2 (240);
147 l_mv VARCHAR2 (2000);
148 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
149 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
150 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
151 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
152 l_sql VARCHAR2(32767);
153 l_mv1 VARCHAR2(100);
154 l_mv2 VARCHAR2(100);
155 l_mv3 VARCHAR2(100);
156 l_mv4 VARCHAR2(100);
157 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
158 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
159 l_col_tbl3 poa_dbi_util_pkg.poa_dbi_col_tbl;
160 l_col_tbl4 poa_dbi_util_pkg.poa_dbi_col_tbl;
161 l_xtd1 VARCHAR2(10);
162 l_xtd2 VARCHAR2(10);
163 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
164 l_sql1 VARCHAR2(32767);
165 l_sql2 VARCHAR2(32767);
166 l_sql3 VARCHAR2(32767);
167 l_sql4 VARCHAR2(32767);
168 l_balance_logic VARCHAR2(10);
169
170 BEGIN
171
172 l_comparison_type := 'Y';
173 l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl ();
174 l_col_tbl1 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
175 l_col_tbl2 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
176 l_col_tbl3 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
177 l_col_tbl4 := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl ();
178 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
179
180 /* Balance logic for OI */
181 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
182
183 IF (l_balance_logic = 'CONTRDATE') THEN
184 --{
185 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
186 , p_view_by => l_view_by
187 , p_view_by_col_name => l_view_by_col
188 , p_comparison_type => l_comparison_type
189 , p_xtd => l_xtd1
190 , p_as_of_date => l_as_of_date
191 , p_prev_as_of_date => l_prev_as_of_date
192 , p_cur_suffix => l_cur_suffix
193 , p_nested_pattern => l_nested_pattern
194 , p_where_clause => l_where_clause1
195 , p_mv => l_mv1
196 , p_join_tbl => l_join_tbl
197 , p_period_type => l_period_type
198 , p_trend => p_trend_flag
199 , p_func_area => 'OKI'
200 , p_version => '6.0'
201 , p_role => NULL
202 , p_mv_set => 'SRM_ST_71'
203 , p_rg_where => 'Y');
204
205 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
206 , p_col_name => 's_g_o_amt_' || l_cur_suffix
207 , p_alias_name => 'xtd1'
208 , p_grand_total => 'N'
209 , p_to_date_type => p_ptd);
210
211 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
212 , p_col_name => 's_r_o_amt_' || l_cur_suffix
213 , p_alias_name => 'xtd11'
214 , p_grand_total => 'N'
215 , p_to_date_type => p_ptd);
216
217
218 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
219 , p_view_by => l_view_by
220 , p_view_by_col_name => l_view_by_col
221 , p_comparison_type => l_comparison_type
222 , p_xtd => l_xtd1
223 , p_as_of_date => l_as_of_date
224 , p_prev_as_of_date => l_prev_as_of_date
225 , p_cur_suffix => l_cur_suffix
226 , p_nested_pattern => l_nested_pattern
227 , p_where_clause => l_where_clause2
228 , p_mv => l_mv2
229 , p_join_tbl => l_join_tbl
230 , p_period_type => l_period_type
231 , p_trend => p_trend_flag
232 , p_func_area => 'OKI'
233 , p_version => '6.0'
234 , p_role => NULL
235 , p_mv_set => 'SRM_EN_71'
236 , p_rg_where => 'Y');
237
238
239 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
240 , p_col_name => 'bal_k_amt_' || l_cur_suffix
241 , p_alias_name => 'xtd2'
242 , p_grand_total => 'N'
243 , p_to_date_type => p_ptd);
244
245 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
246 , p_view_by => l_view_by
247 , p_view_by_col_name => l_view_by_col
248 , p_comparison_type => l_comparison_type
249 , p_xtd => l_xtd1
250 , p_as_of_date => l_as_of_date
251 , p_prev_as_of_date => l_prev_as_of_date
252 , p_cur_suffix => l_cur_suffix
253 , p_nested_pattern => l_nested_pattern
254 , p_where_clause => l_where_clause3
255 , p_mv => l_mv3
256 , p_join_tbl => l_join_tbl
257 , p_period_type => l_period_type
258 , p_trend => p_trend_flag
259 , p_func_area => 'OKI'
260 , p_version => '6.0'
261 , p_role => NULL
262 , p_mv_set => 'SRM_TM_71'
263 , p_rg_where => 'Y');
264
265 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl3
266 , p_col_name => 'bal_k_amt_' || l_cur_suffix
267 , p_alias_name => 'xtd3'
268 , p_grand_total => 'N'
269 , p_to_date_type => p_ptd);
270
271 l_mv_tbl.extend;
272 l_mv_tbl(1).mv_name := l_mv1;
273 l_mv_tbl(1).mv_col := l_col_tbl1;
274 l_mv_tbl(1).mv_where := l_where_clause1;
275 l_mv_tbl(1).in_join_tbls := NULL;
276 l_mv_tbl(1).use_grp_id := 'N';
277
278 l_mv_tbl.extend;
279 l_mv_tbl(2).mv_name := l_mv2;
280 l_mv_tbl(2).mv_col := l_col_tbl2;
281 l_mv_tbl(2).mv_where := l_where_clause2;
282 l_mv_tbl(2).in_join_tbls := NULL;
283 l_mv_tbl(2).use_grp_id := 'N';
284
285 l_mv_tbl.extend;
286 l_mv_tbl(3).mv_name := l_mv3;
287 l_mv_tbl(3).mv_col := l_col_tbl3;
288 l_mv_tbl(3).mv_where := l_where_clause3;
289 l_mv_tbl(3).in_join_tbls := NULL;
290 l_mv_tbl(3).use_grp_id := 'N';
291
292 --}
293
294 ELSE
295
296 --{
297
298 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
299 , p_view_by => l_view_by
300 , p_view_by_col_name => l_view_by_col
301 , p_comparison_type => l_comparison_type
302 , p_xtd => l_xtd1
303 , p_as_of_date => l_as_of_date
304 , p_prev_as_of_date => l_prev_as_of_date
305 , p_cur_suffix => l_cur_suffix
306 , p_nested_pattern => l_nested_pattern
307 , p_where_clause => l_where_clause1
308 , p_mv => l_mv1
309 , p_join_tbl => l_join_tbl
310 , p_period_type => l_period_type
311 , p_trend => p_trend_flag
312 , p_func_area => 'OKI'
316 , p_rg_where => 'Y');
313 , p_version => '6.0'
314 , p_role => NULL
315 , p_mv_set => 'SRM_ST_71'
317
318 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
319 , p_col_name => 's_g_amt_' || l_cur_suffix
320 , p_alias_name => 'xtd1'
321 , p_grand_total => 'N'
322 , p_to_date_type => p_ptd);
323
324 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
325 , p_col_name => 's_rg_amt_' || l_cur_suffix
326 , p_alias_name => 'xtd11'
327 , p_grand_total => 'N'
328 , p_to_date_type => p_ptd);
329
330
331 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
332 , p_view_by => l_view_by
333 , p_view_by_col_name => l_view_by_col
334 , p_comparison_type => l_comparison_type
335 , p_xtd => l_xtd1
336 , p_as_of_date => l_as_of_date
337 , p_prev_as_of_date => l_prev_as_of_date
338 , p_cur_suffix => l_cur_suffix
339 , p_nested_pattern => l_nested_pattern
340 , p_where_clause => l_where_clause2
341 , p_mv => l_mv2
342 , p_join_tbl => l_join_tbl
343 , p_period_type => l_period_type
344 , p_trend => p_trend_flag
345 , p_func_area => 'OKI'
346 , p_version => '6.0'
347 , p_role => NULL
348 , p_mv_set => 'SRM_EN_71'
349 , p_rg_where => 'Y');
350
351
352 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
353 , p_col_name => 'bal_k_amt_' || l_cur_suffix
354 , p_alias_name => 'xtd2'
355 , p_grand_total => 'N'
356 , p_to_date_type => p_ptd);
357
358 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
359 , p_view_by => l_view_by
360 , p_view_by_col_name => l_view_by_col
361 , p_comparison_type => l_comparison_type
362 , p_xtd => l_xtd1
363 , p_as_of_date => l_as_of_date
364 , p_prev_as_of_date => l_prev_as_of_date
365 , p_cur_suffix => l_cur_suffix
366 , p_nested_pattern => l_nested_pattern
367 , p_where_clause => l_where_clause3
368 , p_mv => l_mv3
369 , p_join_tbl => l_join_tbl
370 , p_period_type => l_period_type
371 , p_trend => p_trend_flag
372 , p_func_area => 'OKI'
373 , p_version => '6.0'
374 , p_role => NULL
375 , p_mv_set => 'SRM_TM_71'
376 , p_rg_where => 'Y');
377
378 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl3
379 , p_col_name => 'bal_k_amt_' || l_cur_suffix
380 , p_alias_name => 'xtd3'
381 , p_grand_total => 'N'
382 , p_to_date_type => p_ptd);
383
384 l_mv_tbl.extend;
385 l_mv_tbl(1).mv_name := l_mv1;
386 l_mv_tbl(1).mv_col := l_col_tbl1;
387 l_mv_tbl(1).mv_where := l_where_clause1;
388 l_mv_tbl(1).in_join_tbls := NULL;
389 l_mv_tbl(1).use_grp_id := 'N';
390
391 l_mv_tbl.extend;
392 l_mv_tbl(2).mv_name := l_mv2;
393 l_mv_tbl(2).mv_col := l_col_tbl2;
394 l_mv_tbl(2).mv_where := l_where_clause2;
395 l_mv_tbl(2).in_join_tbls := NULL;
396 l_mv_tbl(2).use_grp_id := 'N';
397
398 l_mv_tbl.extend;
399 l_mv_tbl(3).mv_name := l_mv3;
400 l_mv_tbl(3).mv_col := l_col_tbl3;
401 l_mv_tbl(3).mv_where := l_where_clause3;
402 l_mv_tbl(3).in_join_tbls := NULL;
403 l_mv_tbl(3).use_grp_id := 'N';
404
405 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
406 , p_view_by => l_view_by
407 , p_view_by_col_name => l_view_by_col
411 , p_prev_as_of_date => l_prev_as_of_date
408 , p_comparison_type => l_comparison_type
409 , p_xtd => l_xtd1
410 , p_as_of_date => l_as_of_date
412 , p_cur_suffix => l_cur_suffix
413 , p_nested_pattern => l_nested_pattern
414 , p_where_clause => l_where_clause4
415 , p_mv => l_mv4
416 , p_join_tbl => l_join_tbl
417 , p_period_type => l_period_type
418 , p_trend => p_trend_flag
419 , p_func_area => 'OKI'
420 , p_version => '6.0'
421 , p_role => NULL
422 , p_mv_set => 'SRM_SG_71'
423 , p_rg_where => 'Y');
424
425 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl4
426 , p_col_name => 'gl_s_amt_' || l_cur_suffix
427 , p_alias_name => 'xtd4'
428 , p_grand_total => 'N'
429 , p_to_date_type => p_ptd);
430
431 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl4
432 , p_col_name => 'gl_r_amt_' || l_cur_suffix
433 , p_alias_name => 'xtd44'
434 , p_grand_total => 'N'
435 , p_to_date_type => p_ptd);
436
437
438 l_mv_tbl.extend;
439 l_mv_tbl(4).mv_name := l_mv4;
440 l_mv_tbl(4).mv_col := l_col_tbl4;
441 l_mv_tbl(4).mv_where := l_where_clause4;
442 l_mv_tbl(4).in_join_tbls := NULL;
443 l_mv_tbl(4).use_grp_id := 'N';
444 l_mv_tbl(4).mv_xtd := l_xtd1;
445
446 --}
447 END IF;
448
449 IF (p_trend_flag = 'N') THEN
450 l_sql := get_xtd_sel_clause (p_ptd, l_view_by_col, 'N') || '('
451 ||poa_dbi_template_pkg.union_all_status_sql
452 (p_mv => l_mv_tbl,
453 p_join_tables => l_join_tbl,
454 p_use_windowing => 'Y',
455 p_paren_count => 1,
456 p_filter_where => NULL,
457 p_generate_viewby => 'N') || ')';
458
459 ELSE -- trend sql
460
461 l_mv_tbl(1).mv_xtd := l_xtd1;
462 l_mv_tbl(2).mv_xtd := l_xtd1;
463 l_mv_tbl(3).mv_xtd := l_xtd1;
464
465
466 IF (p_ptd = 'YTD' ) THEN
467 l_sql1 := 'Select 1'
468 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl1,'YTD',p_trend_flag)
469 || oki_dbi_util_pvt.get_xtd_where(l_mv1,'N','YTD','119')
470 || l_where_clause1;
471
472
473 l_sql2 := 'Select 1'
474 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl2,'YTD',p_trend_flag)
475 || oki_dbi_util_pvt.get_xtd_where(l_mv2,'N','YTD','119')
476 || l_where_clause2;
477
478 l_sql3 := 'Select 1'
479 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl3,'YTD',p_trend_flag)
480 || oki_dbi_util_pvt.get_xtd_where(l_mv3,'N','YTD','119')
481 || l_where_clause3;
482
483 l_sql := get_xtd_sel_clause (p_ptd, l_view_by_col, 'Y') || '('
484 || l_sql1
485 || ') a, ( '
486 || l_sql2
487 || ' ) b, ( '
488 || l_sql3 || ') c';
489
490 IF (l_balance_logic = 'EVENTDATE') THEN
491 --{
492 l_sql4 := 'Select 1'
493 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl4,'YTD',p_trend_flag)
494 || oki_dbi_util_pvt.get_xtd_where(l_mv4,'N','YTD','119')
495 || l_where_clause4;
496
497 l_sql := get_xtd_sel_clause (p_ptd, l_view_by_col, 'Y') || '('
498 || l_sql1
499 || ') a, ( '
500 || l_sql2
501 || ' ) b, ( '
502 || l_sql3
503 || ') c, ('
504 || l_sql4
505 || ') d';
506 --}
507 END IF;
508
509 ELSE
510 l_sql := get_xtd_sel_clause (p_ptd, l_view_by_col, 'Y')
511 || poa_dbi_template_pkg.union_all_trend_sql
512 (p_mv => l_mv_tbl,
513 p_comparison_type => l_comparison_type,
514 p_filter_where => NULL);
515
516 END IF;
517
518 END IF; -- trend end
519
520 return l_sql;
521
522 END get_xtd_sql;
523
524 -------------------------------------------------------
525 Function get_xtd_sel_clause (p_ptd in VARCHAR2
526 ,p_view_by_col in VARCHAR2
527 , p_trend_flag in VARCHAR2 )
528 RETURN VARCHAR2 IS
529 l_sql VARCHAR2(5000);
530 l_alias VARCHAR2(300);
534 BEGIN
531 l_viewby VARCHAR2(300);
532 l_balance_logic VARCHAR2(10);
533
535
536 /* Balance logic for OI */
537 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
538
539 IF (p_trend_flag = 'N') THEN -- Status SQL
540 l_viewby := p_view_by_col;
541 ELSE -- trend sql
542 If (p_ptd = 'YTD') then
543 l_viewby := ' 1 ';
544 Else
545 l_viewby := ' cal_name VIEWBY, cal_start_date';
546 END IF;
547 END IF;
548
549
550 IF (l_balance_logic = 'EVENTDATE') THEN
551 --{
552 IF (p_ptd = 'YTD') THEN
553 --{
554 l_sql := 'Select ' || l_viewby || ' , 0 c_xtd, 0 p_xtd
555 , nvl(c_xtd1,0)+ nvl(c_xtd11,0) + nvl(c_xtd4,0)+ nvl(c_xtd44,0) - nvl(c_xtd2,0)- nvl(c_xtd3,0) c_ytd
556 , nvl(p_xtd1,0)+ nvl(p_xtd11,0) + nvl(p_xtd4,0)+ nvl(p_xtd44,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_ytd
557 , 0 c_itd, 0 p_itd from ';
558 --}
559 ELSE
560 --{
561 l_sql := 'Select ' || l_viewby || '
562 , nvl(c_xtd1,0)+ nvl(c_xtd11,0) + nvl(c_xtd4,0)+ nvl(c_xtd44,0) - nvl(c_xtd2,0)- nvl(c_xtd3,0) c_xtd
563 , nvl(p_xtd1,0)+ nvl(p_xtd11,0) + nvl(p_xtd4,0)+ nvl(p_xtd44,0) - nvl(p_xtd2,0)- nvl(p_xtd3,0) p_xtd
564 , 0 c_ytd, 0 p_ytd, 0 c_itd, 0 p_itd from ';
565 --}
566 END IF;
567 ELSE
568 --{
569 IF (p_ptd = 'YTD') THEN
570 --{
571 l_sql := 'Select ' || l_viewby || ' , 0 c_xtd, 0 p_xtd
572 , nvl(c_xtd1,0)+ nvl(c_xtd11,0)- nvl(c_xtd2,0)- nvl(c_xtd3,0) c_ytd
573 , nvl(p_xtd1,0)+ nvl(p_xtd11,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_ytd
574 , 0 c_itd, 0 p_itd from ';
575 --}
576 ELSE
577 --{
578 l_sql := 'Select ' || l_viewby || '
579 , nvl(c_xtd1,0)+ nvl(c_xtd11,0)- nvl(c_xtd2,0)- nvl(c_xtd3,0) c_xtd
580 , nvl(p_xtd1,0)+ nvl(p_xtd11,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_xtd
581 , 0 c_ytd, 0 p_ytd, 0 c_itd, 0 p_itd from ';
582 --}
583 END IF;
584 --}
585 END IF;
586
587 return l_sql;
588
589 END get_xtd_sel_clause;
590
591 -------------------------------------------------------
592 /*
593 Balance Summary Select clause
594 */
595 FUNCTION get_bal_sel_clause (
596 p_view_by_dim IN VARCHAR2
597 , p_view_by_col IN VARCHAR2
598 , p_cur_suffix IN VARCHAR2)
599 RETURN VARCHAR2
600 IS
601 l_sel_clause VARCHAR2 (32767);
602
603 l_Cbal_url VARCHAR2(300);
604
605 l_viewby_select VARCHAR2(32767);
606 l_url_select VARCHAR2(32767);
607 BEGIN
608
609 l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
610 -- Drill Across URL when view by is Salesrep and Product
611 l_Cbal_url := '''pFunctionName=OKI_DBI_SCM_BAL_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
612
613 l_viewby_select := l_viewby_select ||
614 ', OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2
615 ,OKI_PMEASURE_1,OKI_MEASURE_1,OKI_TMEASURE_1,OKI_CHANGE_1,OKI_TCHANGE_1
616 ,OKI_KPI_MEASURE_1,OKI_PKPI_MEASURE_1,OKI_TKPI_MEASURE_1,OKI_PTKPI_MEASURE_1
617 ,OKI_PERCENT_1,OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1
618 ,OKI_PMEASURE_2,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_CHANGE_2,OKI_TCHANGE_2
619 ,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2
620 ,OKI_PERCENT_2,OKI_TPERCENT_2,OKI_PERCENT_CHANGE_2
621 ,OKI_CHANGE_3, OKI_TCHANGE_3
622 FROM (SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
623 , OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2
624 ,OKI_PMEASURE_1,OKI_MEASURE_1,OKI_TMEASURE_1,OKI_CHANGE_1,OKI_TCHANGE_1
625 ,OKI_KPI_MEASURE_1,OKI_PKPI_MEASURE_1,OKI_TKPI_MEASURE_1,OKI_PTKPI_MEASURE_1
626 ,OKI_PERCENT_1,SUM(OKI_PERCENT_1) over() OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1
627 ,OKI_PMEASURE_2,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_CHANGE_2,OKI_TCHANGE_2
628 ,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2
629 ,OKI_PERCENT_2,SUM(OKI_PERCENT_2) over() OKI_TPERCENT_2,OKI_PERCENT_CHANGE_2
630 ,OKI_CHANGE_3, OKI_TCHANGE_3
631 FROM ( ';
632
633 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
634 THEN
635 l_url_select :=
636 'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SCM_BAL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_SALES_GROUP_URL '||
637 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_Cbal_url||')) OKI_DYNAMIC_URL_2 ';
638 ELSIF(p_view_by_dim = 'ITEM+ENI_ITEM')
639 THEN
640 l_url_select :=
641 'SELECT '''' OKI_SALES_GROUP_URL '||
642 ' , '||l_cBal_url||' OKI_DYNAMIC_URL_2 ';
643 ELSE
644 l_url_select :=
645 'SELECT '''' OKI_SALES_GROUP_URL '||
646 ' , '''' OKI_DYNAMIC_URL_2 ';
647 END IF;
648
649
650 --cur current balance
651 -- p_cur
652 -- p_cur_tot
653 -- c_cur
654 -- c_cur_tot
655 --beg Beginning balance
656 -- p_beg
657 -- p_beg_tot
658 -- c_beg
659 -- c_beg_tot
660
661 l_sel_clause := l_viewby_select || l_url_select ||
662 ' ,'|| p_view_by_col ||
663 ' , oset20.p_beg OKI_PMEASURE_1, oset20.c_beg OKI_MEASURE_1 '||
664 ' , oset20.c_beg_tot OKI_TMEASURE_1, oset20.beg_chg OKI_CHANGE_1 '||
665 ' , oset20.beg_chg_tot OKI_TCHANGE_1, oset20.c_beg OKI_KPI_MEASURE_1 '||
666 ' , oset20.p_beg OKI_PKPI_MEASURE_1, oset20.c_beg_tot OKI_TKPI_MEASURE_1 '||
667 ' , oset20.p_beg_tot OKI_PTKPI_MEASURE_1, oset20.c_beg_per OKI_PERCENT_1 '||
668 ' , oset20.beg_per_chg OKI_PERCENT_CHANGE_1, oset20.p_cur OKI_PMEASURE_2 '||
669 ' , oset20.c_cur OKI_MEASURE_2, oset20.c_cur_tot OKI_TMEASURE_2 '||
670 ' , oset20.cur_chg OKI_CHANGE_2, oset20.cur_chg_tot OKI_TCHANGE_2 '||
671 ' , oset20.c_cur OKI_KPI_MEASURE_2, oset20.p_cur OKI_PKPI_MEASURE_2 '||
672 ' , oset20.c_cur_tot OKI_TKPI_MEASURE_2, oset20.p_cur_tot OKI_PTKPI_MEASURE_2 '||
673 ' , oset20.c_cur_per OKI_PERCENT_2, oset20.cur_per_chg OKI_PERCENT_CHANGE_2 '||
674 ' , oset20.ptd_chg OKI_CHANGE_3, oset20.ptd_chg_tot OKI_TCHANGE_3 '||
675 ' from ( select '|| p_view_by_col ||', oset15.c_cur c_cur '||
676 ' , oset15.p_cur p_cur, oset15.c_cur_tot c_cur_tot '||
677 ' , oset15.p_cur_tot p_cur_tot '||
678 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur','oset15.p_cur','NP') || ' cur_chg '||
679 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_tot','oset15.p_cur_tot','NP') || ' cur_chg_tot '||
680 ' , oset15.c_cur_per, oset15.p_cur_per '||
681 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_per','oset15.p_cur_per','P') || ' cur_per_chg '||
682 ' , oset15.c_beg c_beg, oset15.p_beg p_beg, oset15.c_beg_tot c_beg_tot '||
683 ' , oset15.p_beg_tot p_beg_tot '||
687 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg_per','oset15.p_beg_per','P') || ' beg_per_chg '||
684 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg','oset15.p_beg','NP') || ' beg_chg '||
685 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg_tot','oset15.p_beg_tot','NP') || ' beg_chg_tot '||
686 ' , oset15.c_beg_per, oset15.p_beg_per '||
688 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur','oset15.c_beg','NP') || ' ptd_chg '||
689 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_tot','oset15.c_beg_tot','NP') || ' ptd_chg_tot '||
690 ' from (select '|| p_view_by_col ||', oset13.c_cur , oset13.c_cur_tot '||
691 ' , oset13.p_cur , oset13.p_cur_tot '||
692 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_cur','oset13.c_cur_tot') || ' c_cur_per '||
693 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_cur','oset13.p_cur_tot') || ' p_cur_per '||
694 ' , oset13.c_beg , oset13.c_beg_tot, oset13.p_beg , oset13.p_beg_tot '||
695 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_beg','oset13.c_beg_tot') || ' c_beg_per '||
696 ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_beg','oset13.p_beg_tot') || ' p_beg_per '||
697 ' from (select '|| p_view_by_col ||
698 ' , (oset10.c_itd + oset10.c_ytd) c_cur '||
699 ' , (oset10.c_itd_tot + oset10.c_ytd_tot) c_cur_tot '||
700 ' , (oset10.p_itd + oset10.p_ytd) p_cur '||
701 ' , (oset10.p_itd_tot + oset10.p_ytd_tot) p_cur_tot '||
702 ' , (oset10.c_itd + oset10.c_ytd - oset10.c_xtd) c_beg '||
703 ' , (oset10.c_itd_tot + oset10.c_ytd_tot - oset10.c_xtd_tot) c_beg_tot '||
704 ' , (oset10.p_itd + oset10.p_ytd - oset10.p_xtd) p_beg '||
705 ' , (oset10.p_itd_tot + oset10.p_ytd_tot - oset10.p_xtd_tot) p_beg_tot '||
706 ' from ( select oset05.'||p_view_by_col ||
707 ' , SUM(NVL(oset05.c_ytd,0)) c_ytd '||
708 ' , SUM(NVL(oset05.p_ytd,0)) p_ytd '||
709 ' , SUM(SUM(NVL(oset05.c_ytd,0))) over () c_ytd_tot '||
710 ' , SUM(SUM(NVL(oset05.p_ytd,0))) over () p_ytd_tot '||
711 ' , SUM(NVL(oset05.c_xtd,0)) c_xtd '||
712 ' , SUM(NVL(oset05.p_xtd,0)) p_xtd '||
713 ' , SUM(SUM(NVL(oset05.c_xtd,0))) over () c_xtd_tot '||
714 ' , SUM(SUM(NVL(oset05.p_xtd,0))) over () p_xtd_tot '||
715 ' , SUM(NVL(oset05.c_itd,0)) c_itd '||
716 ' , SUM(SUM(NVL(oset05.c_itd,0))) over () c_itd_tot '||
717 ' , SUM(NVL(oset05.p_itd,0)) p_itd '||
718 ' , SUM(SUM(NVL(oset05.p_itd,0))) over () p_itd_tot ';
719
720 RETURN l_sel_clause;
721 END get_bal_sel_clause;
722
723 FUNCTION get_bal_itd_sql (
724 p_param IN bis_pmv_page_parameter_tbl
725 , p_trend_flag in VARCHAR2)
726 RETURN VARCHAR2 IS
727 l_view_by VARCHAR2 (120);
728 l_view_by_col VARCHAR2 (120);
729 l_as_of_date DATE;
730 l_prev_as_of_date DATE;
731 l_xtd VARCHAR2 (10);
732 l_comparison_type VARCHAR2 (1);
733 l_period_type VARCHAR2(10);
734 l_nested_pattern NUMBER;
735 l_cur_suffix VARCHAR2 (2);
736 l_where_clause VARCHAR2 (2000);
737 l_filter_where VARCHAR2 (240);
738 l_mv VARCHAR2 (2000);
739 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
740 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
741 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
742 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
743 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
744 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
745
746
747 l_sql VARCHAR2(32767);
748
749 BEGIN
750
751 l_comparison_type := 'Y';
752 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
753 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
754
755 oki_dbi_util_pvt.process_parameters (p_param => p_param
756 , p_view_by => l_view_by
757 , p_view_by_col_name => l_view_by_col
758 , p_comparison_type => l_comparison_type
759 , p_xtd => l_xtd
760 , p_as_of_date => l_as_of_date
761 , p_prev_as_of_date => l_prev_as_of_date
762 , p_cur_suffix => l_cur_suffix
763 , p_nested_pattern => l_nested_pattern
764 , p_where_clause => l_where_clause
765 , p_mv => l_mv
766 , p_join_tbl => l_join_tbl
767 , p_period_type => l_period_type
768 , p_trend => p_trend_flag
769 , p_func_area => 'OKI'
770 , p_version => '6.0'
771 , p_role => NULL
772 , p_mv_set => 'SRM_BAL'
773 , p_rg_where => 'Y');
774
775 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
776 , p_col_name => 'NULL'
777 , p_alias_name => 'xtd'
778 , p_grand_total => 'N'
779 , p_to_date_type => l_to_date_xtd);
783 , p_alias_name => 'itd'
780
781 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
782 , p_col_name => 'bal_amt_' || l_cur_suffix
784 , p_grand_total => 'N'
785 , p_to_date_type => l_to_date_itd);
786
787 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
788 , p_col_name => 'NULL'
789 , p_alias_name => 'ytd'
790 , p_grand_total => 'N'
791 , p_to_date_type => l_to_date_ytd);
792
793 if (p_trend_flag = 'Y') then
794 l_sql := 'Select 1'
795 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'ITD',p_trend_flag)
796 || oki_dbi_util_pvt.get_itd_where(l_mv,p_trend_flag)
797 || l_where_clause;
798 else
799 l_sql := 'Select ' || l_view_by_col
800 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'ITD',p_trend_flag)
801 || oki_dbi_util_pvt.get_itd_where(l_mv,p_trend_flag)
802 || l_where_clause
803 || ' GROUP BY ' || l_view_by_col;
804 END IF;
805
806 RETURN l_sql;
807
808 END get_bal_itd_sql;
809
810
811 FUNCTION Get_bal_ytd_sql (
812 p_param IN bis_pmv_page_parameter_tbl
813 , p_trend_flag in VARCHAR2)
814 RETURN VARCHAR2 IS
815 l_view_by VARCHAR2 (120);
816 l_view_by_col VARCHAR2 (120);
817 l_as_of_date DATE;
818 l_prev_as_of_date DATE;
819 l_xtd VARCHAR2 (10);
820 l_comparison_type VARCHAR2 (1);
821 l_period_type VARCHAR2(10);
822 l_nested_pattern NUMBER;
823 l_cur_suffix VARCHAR2 (2);
824 l_where_clause VARCHAR2 (2000);
825 l_filter_where VARCHAR2 (240);
826 l_mv VARCHAR2 (2000);
827 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
828 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
829 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
830 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
831 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
832 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
833
834
835 l_sql VARCHAR2(32767);
836
837 BEGIN
838
839 l_comparison_type := 'Y';
840 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
841 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
842
843 oki_dbi_util_pvt.process_parameters (p_param => p_param
844 , p_view_by => l_view_by
845 , p_view_by_col_name => l_view_by_col
846 , p_comparison_type => l_comparison_type
847 , p_xtd => l_xtd
848 , p_as_of_date => l_as_of_date
849 , p_prev_as_of_date => l_prev_as_of_date
850 , p_cur_suffix => l_cur_suffix
851 , p_nested_pattern => l_nested_pattern
852 , p_where_clause => l_where_clause
853 , p_mv => l_mv
854 , p_join_tbl => l_join_tbl
855 , p_period_type => l_period_type
856 , p_trend => p_trend_flag
857 , p_func_area => 'OKI'
858 , p_version => '6.0'
859 , p_role => NULL
860 , p_mv_set => 'SRM'
861 , p_rg_where => 'Y');
862
863
864 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
865 , p_col_name => 'NULL'
866 , p_alias_name => 'xtd'
867 , p_grand_total => 'N'
868 , p_to_date_type => l_to_date_xtd);
869
870 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
871 , p_col_name => 'NULL'
872 , p_alias_name => 'itd'
873 , p_grand_total => 'N'
874 , p_to_date_type => l_to_date_itd);
875
876 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
877 , p_col_name => 'bal_k_amt_' || l_cur_suffix
878 , p_alias_name => 'ytd'
879 , p_grand_total => 'N'
880 , p_to_date_type => l_to_date_ytd);
881
882 if (p_trend_flag = 'Y') then
883 -- NOte: Currently chnaged implementation to use same dates for trend and status.
884 -- hence get_xtd_where trend flag = N for both cases.
888 || l_where_clause;
885 l_sql := 'Select 1'
886 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'YTD',p_trend_flag)
887 || oki_dbi_util_pvt.get_xtd_where(l_mv,'N','YTD','119')
889 else
890 l_sql := 'Select ' || l_view_by_col
891 || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'YTD',p_trend_flag)
892 || oki_dbi_util_pvt.get_xtd_where(l_mv,'N','YTD','119')
893 || l_where_clause
894 || ' GROUP BY ' || l_view_by_col;
895 END IF;
896
897
898 RETURN l_sql;
899
900 END get_bal_ytd_sql;
901
902
903 /******************************************************************************
904 * Procedure to return the query for Ending Balance TRend graph
905 * get_balance_trend_sql
906 -- brrao added
907 *******************************************************************************/
908
909 PROCEDURE get_balance_trend_sql (
910 p_param IN bis_pmv_page_parameter_tbl
911 , x_custom_sql OUT NOCOPY VARCHAR2
912 , x_custom_output OUT NOCOPY bis_query_attributes_tbl) IS
913
914 -- Variables associated with the parameter portlet
915 l_query VARCHAR2 (32767);
916 l_view_by VARCHAR2 (120);
917 l_view_by_col VARCHAR2 (120);
918 l_as_of_date DATE;
919 l_prev_as_of_date DATE;
920 l_xtd VARCHAR2 (10);
921 l_comparison_type VARCHAR2 (1);
922 l_period_type VARCHAR2(10);
923 l_nested_pattern NUMBER;
924 l_dim_bmap NUMBER;
925 l_cur_suffix VARCHAR2 (2);
926 l_custom_sql VARCHAR2 (10000);
927
928 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
929 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
930 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
931 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
932 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
933 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
934
935 l_period_code varchar2(1);
936 l_where_clause VARCHAR2 (2000);
937 l_mv VARCHAR2 (2000);
938
939 l_ytd_sql VARCHAR2(32767);
940 l_itd_sql VARCHAR2(10000);
941 l_xtd_sql VARCHAR2(32767);
942
943 BEGIN
944
945 l_comparison_type := 'Y';
946 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
947 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
948
949 OKI_DBI_UTIL_PVT.Process_Parameters (p_param => p_param
950 , p_view_by => l_view_by
951 , p_view_by_col_name => l_view_by_col
952 , p_comparison_type => l_comparison_type
953 , p_xtd => l_xtd
954 , p_as_of_date => l_as_of_date
955 , p_prev_as_of_date => l_prev_as_of_date
956 , p_cur_suffix => l_cur_suffix
957 , p_nested_pattern => l_nested_pattern
958 , p_where_clause => l_where_clause
959 , p_mv => l_mv
960 , p_join_tbl => l_join_tbl
961 , p_period_type => l_period_type
962 , p_trend => 'Y'
963 , p_func_area => 'OKI'
964 , p_version => '6.0'
965 , p_role => NULL
966 , p_mv_set => 'SRM_ST_71'
967 , p_rg_where => 'Y');
968
969 l_itd_sql := get_bal_itd_sql(p_param,'Y');
970 l_xtd_sql := get_bal_trend_sel_clause(l_cur_suffix) || ' from ( ' ||get_xtd_sql (p_param,'XTD','Y');
971 l_ytd_sql := get_xtd_sql (p_param,'YTD','Y');
972
973 l_query := get_trend_query(l_itd_sql,l_ytd_sql,l_xtd_sql,l_cur_suffix);
974
975
976 /*
977 l_itd_sql := get_bal_itd_sql(p_param,'Y');
978
979 l_ytd_sql := get_bal_ytd_sql(p_param ,'Y');
980
981 l_xtd_sql := get_bal_trend_sel_clause(l_cur_suffix)
982 || ' from '
983 || poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd
984 , p_comparison_type => l_comparison_type
985 , p_fact_name => l_mv
986 , p_where_clause => l_where_clause
987 , p_col_name => l_col_tbl
988 , p_use_grpid => 'R');
989
990 l_query := get_trend_query(l_itd_sql,l_ytd_sql,l_xtd_sql,l_cur_suffix);
991 */
992 x_custom_sql := '/* OKI_DBI_SCM_BAL_GPH_RPT */ '||l_query;
993 oki_dbi_util_pvt.get_custom_trend_binds (l_xtd
994 , l_comparison_type
995 , x_custom_output);
996
997 END get_balance_trend_sql ;
998
999 /*************************************************************/
1000
1001
1002
1003 /*************************************************************
1004 * Current Balance top SQL sel clause for TREND
1008 RETURN VARCHAR2
1005 ************************************************************/
1006
1007 FUNCTION get_bal_trend_sel_clause (p_cur_suffix IN VARCHAR2)
1009 IS
1010 l_sel_clause VARCHAR2 (10000);
1011 BEGIN
1012
1013 l_sel_clause := ' Select viewby, cal_start_date, c_ytd,p_ytd,
1014 lead(c_xtd,1) over(order by cal_start_date) c_xtd,
1015 lead(p_xtd,1) over(order by cal_start_date) p_xtd
1016 from ( Select VIEWBY '||
1017 ' ,cal_start_date , c_ytd '||
1018 ' ,p_ytd '||
1019 ' ,SUM(c_xtd) OVER( ORDER BY cal_start_date DESC ROWS UNBOUNDED PRECEDING) c_xtd '||
1020 ' ,SUM(p_xtd) OVER( ORDER BY cal_start_date DESC ROWS UNBOUNDED PRECEDING) p_xtd ';
1021
1022 RETURN l_sel_clause;
1023 END get_bal_trend_sel_clause;
1024
1025
1026 /*****************************************************
1027 * Function to get the ITD Trend SQL
1028 ******************************************************/
1029
1030 FUNCTION get_trend_query (
1031 p_itd IN VARCHAR2
1032 ,p_ytd IN VARCHAR2
1033 ,p_xtd IN VARCHAR2
1034 ,p_cur_suffix IN VARCHAR2)
1035
1036 RETURN VARCHAR2 IS
1037 l_sql VARCHAR2(32767);
1038
1039 BEGIN
1040
1041 l_sql := 'Select VIEWBY, '||
1042 ' curr_bal OKI_MEASURE_1, '||
1043 ' p_curr_bal OKI_PMEASURE_1' ||
1044 ' ,'||OKI_DBI_UTIL_PVT.change_clause('curr_bal','p_curr_bal','NP') || ' OKI_CHANGE_1 '||
1045 ' FROM ( ' ||
1046 'Select cal_start_date, VIEWBY, '||
1047 ' nvl(bal.c_bal,0) - nvl(xtd.c_xtd,0) curr_bal '||
1048 ' ,nvl(bal.p_bal,0) - nvl(xtd.p_xtd,0) p_curr_bal '||
1049 ' FROM ( '||
1050 ' Select ' || OKI_DBI_UTIL_PVT.add_measures('itd.c_itd','ytd.c_ytd') ||' c_bal '||
1051 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('itd.p_itd','ytd.p_ytd') ||' p_bal '||
1052 ' FROM ( '|| p_itd ||') itd, ( '|| p_ytd || ') ytd ) bal , ('|| p_xtd || '))) xtd' ||
1053 ' ) ' ;
1054 -- ORDER BY cal_start_date
1055
1056 return l_sql;
1057
1058 END get_trend_query;
1059
1060
1061 /*
1062 Balance Detail Select clause
1063 */
1064
1065 PROCEDURE get_balance_detail_sql (
1066 p_param IN bis_pmv_page_parameter_tbl
1067 , x_custom_sql OUT NOCOPY VARCHAR2
1068 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
1069 IS
1070
1071 l_query VARCHAR2 (32767);
1072 l_view_by VARCHAR2 (120);
1073 l_view_by_col VARCHAR2 (120);
1074 l_as_of_date DATE;
1075 l_prev_as_of_date DATE;
1076 l_xtd VARCHAR2 (10);
1077 l_comparison_type VARCHAR2 (1);
1078 l_period_type VARCHAR2(10);
1079 l_nested_pattern NUMBER;
1080 l_cur_suffix VARCHAR2 (2);
1081 l_where_clause VARCHAR2 (2000);
1082 l_mv VARCHAR2 (2000);
1083 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1084 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1085 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
1086 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
1087
1088 l_rpt_specific_where VARCHAR2 (1000);
1089 l_join_where VARCHAR2 (1000);
1090 l_group_by VARCHAR2 (1000);
1091 l_exp_type VARCHAR2 (100);
1092
1093 l_filter_where VARCHAR2 (240);
1094 l_additional_where VARCHAR2 (2000);
1095 l_additional_mv VARCHAR2 (1000);
1096 l_columns VARCHAR (5000);
1097
1098 BEGIN
1099
1100
1101 l_comparison_type := 'Y';
1102 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1103 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
1104
1105
1106 OKI_DBI_UTIL_PVT.process_parameters ( p_param => p_param
1107 , p_view_by => l_view_by
1108 , p_view_by_col_name => l_view_by_col
1109 , p_comparison_type => l_comparison_type
1110 , p_xtd => l_xtd
1111 , p_as_of_date => l_as_of_date
1112 , p_prev_as_of_date => l_prev_as_of_date
1113 , p_cur_suffix => l_cur_suffix
1114 , p_nested_pattern => l_nested_pattern
1115 , p_where_clause => l_where_clause
1116 , p_mv => l_mv
1117 , p_join_tbl => l_join_tbl
1118 , p_period_type => l_period_type
1119 , p_trend => 'N'
1120 , p_func_area => 'OKI'
1121 , p_version => '7.0'
1122 , p_role => NULL
1123 , p_mv_set => 'SRM_CDTL_RPT'
1124 , p_rg_where => 'Y');
1125
1126
1127 -- modified for OKI 8.0
1128 l_rpt_specific_where :=
1129 ' AND fact.effective_start_date <= &BIS_CURRENT_ASOF_DATE
1130 AND fact.date_signed is not null
1131 AND fact.effective_end_date > &BIS_CURRENT_ASOF_DATE';
1132
1136 , p_col_name => 'price_negotiated_' || l_cur_suffix
1133 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id,fact.date_signed';
1134
1135 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
1137 , p_alias_name => 'Bal'
1138 , p_prior_code => poa_dbi_util_pkg.no_priors);
1139
1140 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1141
1142 OKI_DBI_UTIL_PVT.join_rpt_where (p_join_tbl => l_join_tbl
1143 , p_func_area => 'SRM'
1144 , p_version => '6.0'
1145 , p_role => NULL
1146 , p_mv_set => 'SRM_CDTL_RPT');
1147
1148
1149 /* Additional filter needed to avoid displaying records queried due to total values at node */
1150 l_filter_where := ' ( ABS(oki_measure_2) ) <> 0 ';
1151 l_additional_mv := ' ) fact
1152 , OKI_SCM_OCR_MV k
1153 WHERE fact.chr_id = k.chr_id) ';
1154
1155 l_query := get_bal_detail_sel_clause(l_cur_suffix, l_period_type, '1')
1156 || poa_dbi_template_pkg.dtl_status_sql2 (
1157 p_fact_name => l_mv
1158 , p_where_clause => l_where_clause || l_rpt_specific_where
1159 , p_join_tables => l_join_tbl
1160 , p_use_windowing => 'Y'
1161 , p_col_name => l_col_tbl
1162 , p_use_grpid => 'N'
1163 , p_filter_where => l_filter_where || l_additional_mv
1164 , p_paren_count => 5
1165 , p_group_by => l_group_by
1166 , p_from_clause => 'from '||l_mv ||' fact ');
1167
1168
1169 x_custom_sql := '/* OKI_DBI_SCM_BAL_DTL_RPT */'||l_query;
1170 oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
1171
1172 END get_balance_detail_sql;
1173
1174 FUNCTION get_bal_detail_sel_clause (
1175 p_cur_suffix IN VARCHAR2
1176 , p_period_type_code IN VARCHAR2
1177 , p_exp_type IN VARCHAR2)
1178 RETURN VARCHAR2
1179 IS
1180 l_query VARCHAR2 (10000);
1181
1182
1183 BEGIN
1184
1185 -- Generate sql query
1186 l_query :=
1187 '
1188 SELECT
1189 OKI_ATTRIBUTE_1,
1190 cust.value OKI_ATTRIBUTE_2,
1191 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
1192 OKI_DATE_3,
1193 OKI_DATE_1,
1194 OKI_DATE_2,
1195 OKI_MEASURE_1,
1196 OKI_TMEASURE_1,
1197 OKI_MEASURE_2,
1198 OKI_TMEASURE_2,
1199 fact.chr_id OKI_ATTRIBUTE_5
1200 FROM(
1201 SELECT *
1202 FROM (
1203 SELECT
1204 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
1205 customer_party_id,
1206 resource_id,
1207 oki_measure_2,
1208 oki_tmeasure_2,
1209 oki_date_3,
1210 oki_date_1,
1211 oki_date_2,
1212 oki_attribute_1,
1213 oki_measure_1,
1214 oki_tmeasure_1,
1215 chr_id
1216 FROM (SELECT fact.*
1217 , to_char(k.start_date) OKI_DATE_1
1218 , to_char(k.end_date) OKI_DATE_2
1219 , k.COMPLETE_k_number oki_attribute_1
1220 , k.price_nego_' ||p_cur_suffix ||' OKI_MEASURE_1
1221 , SUM(k.price_nego_' ||p_cur_suffix ||') over () OKI_TMEASURE_1
1222 FROM (SELECT *
1223 FROM (
1224 SELECT oset5.chr_id ,
1225 oset5.customer_party_id ,
1226 oset5.resource_id ,
1227 nvl(oset5.Bal,0) OKI_MEASURE_2,
1228 SUM(nvl(oset5.Bal,0)) over () OKI_TMEASURE_2,
1229 to_char(oset5.date_signed) OKI_DATE_3
1230 FROM
1231 (SELECT
1232 fact.chr_id,
1233 fact.customer_party_id,
1234 fact.resource_id,
1235 fact.date_signed
1236 ';
1237 RETURN l_query;
1238 END get_bal_detail_sel_clause;
1239
1240 END OKI_DBI_NSCM_BAL_PVT;