[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_SCM_PKG
Source
1 PACKAGE BODY OKI_DBI_SCM_PKG AS
2 /* $Header: OKIRKPIB.pls 115.36 2004/02/06 00:24:52 rpotnuru noship $ */
3
4
5 PROCEDURE GET_KPI_BALANCE_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8 l_pattern NUMBER;
9 l_period_type_id NUMBER;
10 l_SQLText varchar2(20000);
11 l_view_by varchar2(200);
12 l_as_of_date date;
13 l_period_type varchar2(50);
14 l_org varchar2(200);
15 l_xtd varchar2(10);
16 l_comparison_type varchar2(1) := 'Y';
17 l_org_where varchar2(500);
18 l_cur_suffix varchar2(2);
19 l_period_code varchar2(1);
20 l_nw_amt varchar2(20);
21 l_rn_amt varchar2(20);
22 l_x_amt varchar2(20);
23 l_t_amt varchar2(20);
24 l_bgn_k_amt varchar2(20);
25 l_cur_sql varchar2(5000);
26 l_ytd_sql varchar2(5000);
27 l_itd varchar2(5000);
28 l_pitd varchar2(5000);
29 l_custom_rec BIS_QUERY_ATTRIBUTES;
30
31 BEGIN
32
33 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
34 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
35
36 OKI_DBI_UTIL_PVT.get_parameter_values(p_page_parameter_tbl, l_view_by, l_period_type, l_org, l_comparison_type, l_xtd,l_as_of_date, l_cur_suffix,l_pattern,l_period_type_id,l_period_code);
37
38 l_nw_amt := 's_nw_amt_'|| l_cur_suffix;
39 l_rn_amt := 's_rn_amt_'|| l_cur_suffix;
40 l_x_amt := 'x_amt_'|| l_cur_suffix;
41 l_t_amt := 't_amt_'|| l_cur_suffix;
42 l_bgn_k_amt := 'bgn_k_amt_' || l_cur_suffix;
43
44 l_org_where := OKI_DBI_UTIL_PVT.get_org_where('ORGANIZATION',l_org);
45
46 -- Beginning Balance
47
48 l_ytd_sql := '( SELECT fact.authoring_org_id org_id
49 , NVL(SUM(DECODE(cal.report_date
50 ,&BIS_CURRENT_EFFECTIVE_START_DATE - 1
51 , '||l_nw_amt||' )), 0) +
52 NVL(SUM(DECODE(cal.report_date
53 , &BIS_CURRENT_EFFECTIVE_START_DATE - 1
54 , '||l_rn_amt||' )), 0) -
55 NVL(SUM(DECODE(cal.report_date
56 , &BIS_CURRENT_EFFECTIVE_START_DATE - 1
57 , '||l_x_amt||' )), 0) -
58 NVL(SUM(DECODE(cal.report_date
59 , &BIS_CURRENT_EFFECTIVE_START_DATE - 1
60 , '||l_t_amt||' )), 0) c_ytd_bal
61 , NVL(SUM(DECODE(cal.report_date
62 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1
63 , '||l_nw_amt||' )), 0) +
64 NVL(SUM(DECODE(cal.report_date
65 , &BIS_PREVIOUS_EFFECTIVE_START_DATE - 1
66 , '||l_rn_amt||' )), 0) -
67 NVL(SUM(DECODE(cal.report_date
68 , &BIS_PREVIOUS_EFFECTIVE_START_DATE - 1
69 , '||l_x_amt||' )), 0) -
70 NVL(SUM(DECODE(cal.report_date
71 , &BIS_PREVIOUS_EFFECTIVE_START_DATE - 1
72 , '||l_t_amt||' )), 0) p_ytd_bal
73 ,NULL c_xtd_bal,NULL p_xtd_bal
74 FROM oki_scm_o_2_mv fact
75 , fii_time_rpt_struct_v cal
76 WHERE fact.time_id = cal.time_id
77 ' || l_org_where || '
78 AND cal.report_date IN
79 (&BIS_CURRENT_EFFECTIVE_START_DATE - 1
80 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1
81 )
82 AND bitand(cal.record_type_id,
83 decode(cal.report_date,
84 &BIS_CURRENT_EFFECTIVE_START_DATE - 1,119
85 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1,119
86 ) ) = cal.record_type_id
87 GROUP BY fact.authoring_org_id
88 UNION ALL
89 SELECT fact.authoring_org_id org_id
90 , NULL c_ytd_bal,NULL p_ytd_bal
91 , NVL(SUM(DECODE(cal.report_date
92 , &BIS_CURRENT_ASOF_DATE
93 , '||l_nw_amt||' )), 0) +
94 NVL(SUM(DECODE(cal.report_date
95 , &BIS_CURRENT_ASOF_DATE
96 , '||l_rn_amt||' )), 0) -
97 NVL(SUM(DECODE(cal.report_date
98 , &BIS_CURRENT_ASOF_DATE
99 , '||l_x_amt||' )), 0) -
100 NVL(SUM(DECODE(cal.report_date
101 , &BIS_CURRENT_ASOF_DATE
102 , '||l_t_amt||' )), 0) c_xtd_bal
103 , NVL(SUM(DECODE(cal.report_date
104 , &BIS_PREVIOUS_ASOF_DATE
105 , '||l_nw_amt||' )), 0) +
106 NVL(SUM(DECODE(cal.report_date
107 , &BIS_PREVIOUS_ASOF_DATE
108 , '||l_rn_amt||')), 0) -
109 NVL(SUM(DECODE(cal.report_date
110 , &BIS_PREVIOUS_ASOF_DATE
111 , '||l_x_amt||')), 0) -
112 NVL(SUM(DECODE(cal.report_date
113 , &BIS_PREVIOUS_ASOF_DATE
114 , '||l_t_amt||')), 0) p_xtd_bal
115 FROM oki_scm_o_2_mv fact
116 , fii_time_rpt_struct_v cal
117 WHERE fact.time_id = cal.time_id
118 ' || l_org_where || '
119 AND cal.report_date IN
120 ( &BIS_CURRENT_ASOF_DATE
121 , &BIS_PREVIOUS_ASOF_DATE )
122 AND bitand(cal.record_type_id,
123 decode(cal.report_date
124 ,&BIS_CURRENT_ASOF_DATE,&BIS_NESTED_PATTERN
125 ,&BIS_PREVIOUS_ASOF_DATE ,&BIS_NESTED_PATTERN ) ) = cal.record_type_id
126 GROUP BY fact.authoring_org_id ) ';
127
128 l_cur_sql := ' ( Select org_id,
129 sum(c_ytd_bal) curr_cbal_ptd , sum(p_ytd_bal) curr_pbal_ptd
130 ,sum(c_xtd_bal) prev_cbal_ptd, sum(p_xtd_bal) prev_pbal_ptd
131 from ' || l_ytd_sql || '
132 GROUP by org_id ) cur ';
133
134 l_itd := '(select fact.authoring_org_id org_id,
135 NVL('||l_bgn_k_amt||', 0 ) bal_itd
136 FROM oki_scm_o_1_mv fact
137 , fii_time_ent_year t
138 WHERE fact.ent_year_id = t.ent_year_id
139 ' || l_org_where || '
140 AND &BIS_CURRENT_EFFECTIVE_START_DATE - 1 BETWEEN t.start_date
141 AND t.end_date
142
143 ) itd ';
144
145 l_pitd := '(select fact.authoring_org_id org_id,
146 NVL('||l_bgn_k_amt||', 0 ) pbal_itd
147 FROM oki_scm_o_1_mv fact
148 , fii_time_ent_year t
149 WHERE fact.ENT_YEAR_ID = t.ent_year_id
150 ' || l_org_where || '
151 AND &BIS_PREVIOUS_EFFECTIVE_START_DATE - 1 BETWEEN t.start_date
152 AND t.end_date
153
154 ) pitd ';
155
156 l_sqltext := 'select v.value VIEWBY
157 ,bbalance OKI_MEASURE_1
158 ,bbalance_prev OKI_MEASURE_2
159 ,curr_balance OKI_MEASURE_3
160 ,currbalance_prev OKI_MEASURE_4
161 , SUM(bbalance) OVER () OKI_MEASURE_5
162 , SUM(curr_balance) OVER () OKI_MEASURE_6
163 , SUM(bbalance_prev) OVER () OKI_MEASURE_7
164 , SUM(currbalance_prev) OVER () OKI_MEASURE_8
165 from
166 ( select z.org_id,
167 bbalance,
168 curr_balance,
169 p_bbalance+ NVL(pbal_itd, 0) bbalance_prev,
170 p_currbalance+ NVL(pbal_itd, 0) currbalance_prev
171 from
172 ( select itd.org_id org_id,
173 sum(nvl(cur.curr_cbal_ptd,0) +
174 NVL(itd.bal_itd, 0)) bbalance,
175 sum(nvl(cur.prev_cbal_ptd,0) +
176 nvl(cur.curr_cbal_ptd,0) +
177 NVL(itd.bal_itd, 0)) curr_balance,
178 sum(nvl(cur.curr_pbal_ptd,0) ) p_bbalance,
179 sum(nvl(cur.prev_pbal_ptd,0) +
180 nvl(cur.curr_pbal_ptd,0)) p_currbalance
181 from
182 '|| l_cur_sql ||', '|| l_itd ||'
183 where itd.org_id = cur.org_id(+)
184 group by itd.org_id
185 UNION
186 select cur.org_id org_id,
187 sum(nvl(cur.curr_cbal_ptd,0) +
188 nvl(itd.bal_itd,0)) bbalance,
189 sum(nvl(cur.prev_cbal_ptd,0) +
190 nvl(cur.curr_cbal_ptd,0) +
191 nvl(itd.bal_itd,0)) curr_balance,
192 sum(nvl(cur.curr_pbal_ptd,0) ) p_bbalance,
193 sum(nvl(cur.prev_pbal_ptd,0) +
194 nvl(cur.curr_pbal_ptd,0)) p_currbalance
195 from
196 '|| l_cur_sql ||', '|| l_itd ||'
197 where cur.org_id = itd.org_id(+)
198 group by cur.org_id
199 ) z , '|| l_pitd ||'
200 where z.org_id = pitd.org_id(+)
201 ) k , fii_operating_units_v v
202 where k.org_id = v.id
203 &ORDER_BY_CLAUSE ';
204
205
206 x_custom_sql := '/* OKI_DBI_BAL_KPI */' || l_SQLText;
207
208 l_custom_rec.attribute_name := '&SEC_ID';
209 l_custom_rec.attribute_value := oki_dbi_util_pvt.get_sec_profile;
210 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
211 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
212 x_custom_output.EXTEND;
213 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
214
215
216 END GET_KPI_BALANCE_SQL ;
217
218
219 PROCEDURE GET_KPI_OTHERS_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
220 x_custom_sql OUT NOCOPY VARCHAR2,
221 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
222 l_pattern NUMBER;
223 l_period_type_id NUMBER;
224 l_sqltext varchar2(20000);
225 l_view_by varchar2(200);
226 l_as_of_date date;
227 l_period_type varchar2(50);
228 l_org varchar2(200);
229 l_xtd varchar2(10);
230 l_comparison_type varchar2(1) := 'Y';
231 l_org_where varchar2(500);
232 l_cur_suffix varchar2(2);
233 l_period_code varchar2(1);
234 l_nw_amt varchar2(20);
235 l_rn_amt varchar2(20);
236 l_x_amt varchar2(20);
237 l_t_amt varchar2(20);
238 l_xrgr_amt varchar2(20);
239 l_xrgrn_amt varchar2(20);
240 l_sorsrn_amt varchar2(20);
241 l_srslrn_amt varchar2(20);
242 l_srsorn_amt varchar2(20);
243 l_cur_sql varchar2(20000);
244 l_custom_rec BIS_QUERY_ATTRIBUTES;
245
246 BEGIN
247
248 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
249 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
250
251 OKI_DBI_UTIL_PVT.get_parameter_values(p_page_parameter_tbl, l_view_by, l_period_type, l_org, l_comparison_type, l_xtd,l_as_of_date, l_cur_suffix,l_pattern,l_period_type_id,l_period_code);
252
253 l_nw_amt := 's_nw_amt_'|| l_cur_suffix;
254 l_rn_amt := 's_rn_amt_'|| l_cur_suffix;
255 l_x_amt := 'x_amt_'|| l_cur_suffix;
256 l_t_amt := 't_amt_'|| l_cur_suffix;
257 l_sorsrn_amt := 's_ors_rn_amt_'|| l_cur_suffix;
258 l_srslrn_amt := 's_rsl_rn_amt_'|| l_cur_suffix;
259 l_srsorn_amt := 's_rso_rn_amt_' || l_cur_suffix;
260 l_xrgr_amt := 'x_rgr_amt_'|| l_cur_suffix; -- Current ren rate
261 l_xrgrn_amt := 'x_rgr_amt_n_'|| l_period_code || '_' || l_cur_suffix;
262
263
264 l_org_where := OKI_DBI_UTIL_PVT.get_org_where('ORGANIZATION',l_org);
265
266 l_cur_sql :=
267 '(SELECT fact.authoring_org_id org_id
268 -- Current values
269 , NVL2(SUM(DECODE(cal.report_date
270 , &BIS_CURRENT_ASOF_DATE
271 , '||l_x_amt||'))
272 , (NVL(SUM(DECODE(cal.report_date
273 , &BIS_CURRENT_ASOF_DATE
274 , '||l_xrgr_amt||')), 0) -
275 NVL(SUM(DECODE(cal.report_date
276 , &BIS_CURRENT_ASOF_DATE
277 , '||l_xrgrn_amt||')), 0) )
278 , NULL) curr_renewed
279 ,SUM(DECODE(cal.report_date
280 , &BIS_CURRENT_ASOF_DATE
281 , '||l_x_amt||')) curr_expired
282 , SUM(DECODE(cal.report_date
283 , &BIS_CURRENT_ASOF_DATE
284 , '||l_t_amt||')) curr_term
285 , NVL2(COALESCE(SUM(DECODE(cal.report_date
286 , &BIS_CURRENT_ASOF_DATE
287 , '||l_nw_amt||'))
288 , SUM(DECODE(cal.report_date
289 , &BIS_CURRENT_ASOF_DATE
290 , '||l_srslrn_amt ||'))
291 , SUM(DECODE(cal.report_date
292 , &BIS_CURRENT_ASOF_DATE
293 , '||l_srsorn_amt ||'))
294 )
298 NVL(SUM(DECODE(cal.report_date
295 , NVL(SUM(DECODE(cal.report_date
296 , &BIS_CURRENT_ASOF_DATE
297 , '||l_nw_amt||')), 0) +
299 , &BIS_CURRENT_ASOF_DATE
300 , '||l_srslrn_amt||')), 0) +
301 NVL(SUM(DECODE(cal.report_date
302 , &BIS_CURRENT_ASOF_DATE
303 , '||l_srsorn_amt||')), 0)
304 , NULL
305 ) curr_active
306 , NVL2(COALESCE(SUM(DECODE(cal.report_date
307 , &BIS_CURRENT_ASOF_DATE
308 , '||l_rn_amt||'))
309 , SUM(DECODE(cal.report_date
310 , &BIS_CURRENT_ASOF_DATE
311 , '||l_sorsrn_amt||')))
312 , NVL(SUM(DECODE(cal.report_date
313 , &BIS_CURRENT_ASOF_DATE
314 , '||l_rn_amt||')), 0) -
315 NVL(SUM(DECODE(cal.report_date
316 , &BIS_CURRENT_ASOF_DATE
317 , '||l_sorsrn_amt||')), 0)
318 , NULL) curr_uplft
319 -- Prior values
320 , NVL2(SUM(DECODE(cal.report_date
321 , &BIS_PREVIOUS_ASOF_DATE
322 , '||l_x_amt||'))
323 ,(NVL(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE
324 , '||l_xrgr_amt||')), 0) -
325 NVL(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE
326 , '||l_xrgrn_amt||')), 0) )
327 , NULL) prev_renewed
328 , SUM(DECODE(cal.report_date
329 , &BIS_PREVIOUS_ASOF_DATE
330 , '||l_x_amt||')) prev_expired
331 , SUM(DECODE(cal.report_date
332 , &BIS_PREVIOUS_ASOF_DATE
333 , '||l_t_amt||')) prev_term
334 , NVL2(COALESCE(SUM(DECODE(cal.report_date
335 , &BIS_PREVIOUS_ASOF_DATE
336 , '||l_nw_amt||'))
337 , SUM(DECODE(cal.report_date
338 , &BIS_PREVIOUS_ASOF_DATE
339 , '||l_srslrn_amt ||'))
340 , SUM(DECODE(cal.report_date
341 , &BIS_CURRENT_ASOF_DATE
342 , '||l_srsorn_amt ||'))
343 )
344 , NVL(SUM(DECODE(cal.report_date
345 , &BIS_PREVIOUS_ASOF_DATE
346 , '||l_nw_amt||')), 0) +
347 NVL(SUM(DECODE(cal.report_date
348 , &BIS_PREVIOUS_ASOF_DATE
349 , '||l_srslrn_amt||')), 0) +
350 NVL(SUM(DECODE(cal.report_date
351 , &BIS_PREVIOUS_ASOF_DATE
352 , '||l_srsorn_amt||')), 0)
353 , NULL
354 ) prev_active
355 , NVL2(COALESCE(SUM(DECODE(cal.report_date
356 , &BIS_PREVIOUS_ASOF_DATE
357 , '||l_rn_amt||'))
358 , SUM(DECODE(cal.report_date
359 , &BIS_PREVIOUS_ASOF_DATE
360 , '||l_sorsrn_amt||')))
361 , NVL(SUM(DECODE(cal.report_date
362 , &BIS_PREVIOUS_ASOF_DATE
363 , '||l_rn_amt||')), 0) -
364 NVL(SUM(DECODE(cal.report_date
365 , &BIS_PREVIOUS_ASOF_DATE
366 , '||l_sorsrn_amt||')), 0)
367 , NULL) prev_uplft
368 FROM OKI_SCM_O_2_MV fact
369 , fii_time_rpt_struct_v cal
370 WHERE fact.time_id = cal.time_id
371 ' || l_org_where || '
372 AND cal.report_date IN
373 (&BIS_CURRENT_ASOF_DATE
374 , &BIS_PREVIOUS_ASOF_DATE )
375 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN ) =
376 cal.record_type_id
377 GROUP BY fact.authoring_org_id
378 ) cur,';
379
380 l_sqltext :=
381 'select v.value VIEWBY
382 , cur.curr_expired OKI_MEASURE_1 -- Expired
383 , cur.prev_expired OKI_MEASURE_2
384 , cur.curr_term OKI_MEASURE_3 -- Terminated
385 , cur.prev_term OKI_MEASURE_4
386 , cur.curr_active OKI_MEASURE_5 -- Activated
387 , cur.prev_active OKI_MEASURE_6
388 , cur.curr_uplft OKI_MEASURE_7 -- Uplift
389 , cur.prev_uplft OKI_MEASURE_8
390 , NULL OKI_CALC_ITEM1
391 , NULL OKI_CALC_ITEM2
392 , NULL OKI_MEASURE_9 , NULL OKI_MEASURE_10
393 , NULL OKI_CALC_ITEM5
394 , NULL OKI_CALC_ITEM6
395 , SUM(cur.curr_expired) OVER () OKI_CALC_ITEM4
396 , SUM(cur.curr_term) OVER () OKI_MEASURE_11
397 , SUM(cur.curr_active ) OVER () OKI_CALC_ITEM3
398 , SUM(cur.prev_expired) OVER () OKI_MEASURE_12
399 , SUM(cur.prev_term) OVER () OKI_MEASURE_13
400 , SUM(cur.prev_active) OVER () OKI_MEASURE_14
401 , SUM( cur.curr_uplft ) OVER () OKI_MEASURE_15
402 , SUM(cur.prev_uplft) OVER () OKI_PARAMETER_NUM_1
403 FROM '|| l_cur_sql || '
404 fii_operating_units_v v
408 x_custom_sql := '/* OKI_DBI_OTHERS_KPI */' || l_sqltext;
405 WHERE cur.org_id = v.id
406 &ORDER_BY_CLAUSE ';
407
409
410 l_custom_rec.attribute_name := '&SEC_ID';
411 l_custom_rec.attribute_value := oki_dbi_util_pvt.get_sec_profile;
412 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
413 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
414 x_custom_output.EXTEND;
415 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
416
417
418 END GET_KPI_OTHERS_SQL ;
419
420 -- brrao added for Past due renewal rate new definition for dbi 5.1
421
422 PROCEDURE GET_KPI_RATES_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
423 x_custom_sql OUT NOCOPY VARCHAR2,
424 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
425 l_pattern NUMBER;
426 l_period_type_id NUMBER;
427 l_sqltext varchar2(32767);
428 l_itd varchar2(2000);
429 l_view_by varchar2(200);
430 l_as_of_date date;
431 l_period_type varchar2(50);
432 l_org varchar2(200);
433 l_xtd varchar2(10);
434 l_comparison_type varchar2(1) := 'Y';
435 l_org_where varchar2(500);
436 l_cur_suffix varchar2(2);
437 l_period_code varchar2(1);
438 l_x_amt varchar2(20);
439 l_B_amt varchar2(20);
440 l_Brd_amt varchar2(20);
441 l_xrgr_amt varchar2(20);
442 l_xrgrn_amt varchar2(20);
443 l_brgr_amt varchar2(20);
444 l_cur_sql varchar2(20000);
445 l_ytd_sql varchar2(20000);
446 l_custom_rec BIS_QUERY_ATTRIBUTES;
447
448 BEGIN
449
450
451 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
452 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
453
454 OKI_DBI_UTIL_PVT.get_parameter_values(p_page_parameter_tbl, l_view_by, l_period_type, l_org, l_comparison_type, l_xtd,l_as_of_date, l_cur_suffix,l_pattern,l_period_type_id,l_period_code);
455
456 l_x_amt := 'x_amt_'|| l_cur_suffix;
457 l_xrgr_amt := 'x_rgr_amt_'|| l_cur_suffix; -- Current ren rate
458 l_xrgrn_amt := 'x_rgr_amt_n_'|| l_period_code || '_' || l_cur_suffix;
459 l_brgr_amt := 'b_rgr_amt_'|| l_cur_suffix; -- Backlog ren rate changed to b_rgr_amt_f
460 l_Brd_amt := 'B_rd_amt_'|| l_cur_suffix;
461 l_B_amt := 'B_amt_'|| l_cur_suffix;
462
463 /* DEBUG
464 --OKI_DBIDEBUG_PVT.g_portal_name := 'OKI_DBI_BAL';
465 OKI_DBIDEBUG_PVT.check_portal_param('OKI_DBI_BAL',p_page_parameter_tbl);
466 */
467
468 l_org_where := OKI_DBI_UTIL_PVT.get_org_where('ORGANIZATION',l_org);
469
470 l_itd := '(select mv.authoring_org_id org_id
471 ,SUM(DECODE (t.report_date,&BIS_CURRENT_ASOF_DATE-1,'||l_B_amt||',0)) cblog
472 ,SUM(DECODE (t.report_date,&BIS_PREVIOUS_ASOF_DATE-1,'||l_B_amt||',0)) pblog
473 from Oki_scm_blg_mv mv, fii_time_day t
474 WHERE mv.ent_year_id(+) = t.ent_year_id ' || l_org_where || '
475 AND t.report_date in (&BIS_CURRENT_ASOF_DATE-1,&BIS_PREVIOUS_ASOF_DATE-1 )
476 group by mv.authoring_org_id )itd ';
477
478 l_ytd_sql := '(SELECT fact.authoring_org_id org_id
479 , NVL2(SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_x_amt||'))
480 , (NVL(SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_xrgr_amt||')), 0) -
481 NVL(SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_xrgrn_amt||')), 0) ),
482 NULL) curr_renewed
483 , NVL2(SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_x_amt||'))
484 , NVL(SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_x_amt||')), 0),
485 NULL) curr_expired
486 , NVL(SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE-1, '||l_B_amt||' )), 0) bklg_amt
487 , NULL blg_rd_amt
488 , SUM(DECODE(cal.report_date, &BIS_CURRENT_ASOF_DATE, '||l_brgr_amt||')) curr_blogn
489 , NVL2(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_x_amt||'))
490 ,(NVL(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_xrgr_amt||')), 0) -
491 NVL(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_xrgrn_amt||')), 0))
492 ,NULL) prev_renewed
493 , NVL2(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_x_amt||'))
494 , NVL(SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_x_amt||')), 0)
495 ,NULL) prev_expired
496 , NVL(SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE-1, '||l_B_amt||' )), 0) bklg_amt_p
497 , NULL blg_rd_amt_p
498 , SUM(DECODE(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, '||l_brgr_amt||')) prev_blogn
499 FROM OKI_SCM_O_2_MV fact, fii_time_rpt_struct_v cal WHERE fact.time_id = cal.time_id ' || l_org_where || '
500 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE ,&BIS_CURRENT_ASOF_DATE-1
501 ,&BIS_PREVIOUS_ASOF_DATE
502 ,&BIS_PREVIOUS_ASOF_DATE-1)
503 AND bitand(cal.record_type_id, decode(cal.report_date
504 ,&BIS_CURRENT_ASOF_DATE,&BIS_NESTED_PATTERN
505 ,&BIS_PREVIOUS_ASOF_DATE ,&BIS_NESTED_PATTERN
506 ,&BIS_CURRENT_ASOF_DATE-1,119
507 ,&BIS_PREVIOUS_ASOF_DATE-1,119 )
508 ) = cal.record_type_id
509 GROUP BY fact.authoring_org_id
510 UNION ALL
511 SELECT fact.authoring_org_id org_id,
512 NULL curr_renewed,NULL curr_expired,NULL bklg_amt
513 ,NVL(SUM(DECODE(cal.report_date,&BIS_CURRENT_EFFECTIVE_START_DATE - 1, '||l_Brd_amt||')), 0) blg_rd_amt
514 , NULL curr_blogn, NULL prev_renewed, NULL prev_expired, NULL bklg_amt_p
515 , NVL(SUM(DECODE(cal.report_date,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1, '||l_Brd_amt||'))
516 , 0) blg_rd_amt_p, NULL prev_blogn
520 AND bitand(cal.record_type_id, decode(cal.report_date,
517 FROM OKI_SCM_O_2_MV fact, fii_time_rpt_struct_v cal WHERE fact.time_id = cal.time_id ' || l_org_where || '
518 AND cal.report_date IN (&BIS_CURRENT_EFFECTIVE_START_DATE - 1
519 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1)
521 &BIS_CURRENT_EFFECTIVE_START_DATE - 1,119
522 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE - 1,119)
523 ) = cal.record_type_id
524 GROUP BY fact.authoring_org_id ) ';
525
526 l_cur_sql := ' ( Select org_id, sum(curr_renewed) curr_renewed, sum(curr_expired) curr_expired
527 ,sum(bklg_amt) bklg_amt, sum(blg_rd_amt) blg_rd_amt, sum(curr_blogn) curr_blogn
528 ,sum(prev_renewed) prev_renewed, sum(prev_expired) prev_expired
529 ,sum(bklg_amt_p) bklg_amt_p, sum(blg_rd_amt_p) blg_rd_amt_p,sum(prev_blogn) prev_blogn
530 from '|| l_ytd_sql ||'
531 GROUP BY org_id ) cur, fii_time_day c,fii_time_day p
532 where c.report_date = &BIS_CURRENT_ASOF_DATE-1 and p.report_date = &BIS_PREVIOUS_ASOF_DATE-1 ';
533
534
535 l_sqltext :=
536 'select v.value VIEWBY
537 , NVL2(COALESCE(cur.curr_renewed, cur.curr_expired )
538 , (NVL(cur.curr_renewed, 0) / DECODE(cur.curr_expired, 0,NULL ,cur.curr_expired)) * 100
539 , NULL) OKI_CALC_ITEM1
540 , NVL2(COALESCE(cur.prev_renewed, cur.prev_expired )
541 ,(NVL(cur.prev_renewed, 0) / DECODE(cur.prev_expired,0,NULL, cur.prev_expired)) * 100
542 , NULL) OKI_CALC_ITEM2
543 , (nvl(cur.bkd_xtd,0) / Decode(cur.exp_bal,0,NULL,cur.exp_bal)) * 100 OKI_MEASURE_9
544 , (nvl(cur.bkd_xtd_p,0) / Decode(cur.exp_bal_p,0,NULL,cur.exp_bal_p)) * 100 OKI_MEASURE_10
545 , NVL2(COALESCE(SUM(cur.curr_renewed) OVER(), SUM(cur.curr_expired) OVER ())
546 , (SUM(NVL(cur.curr_renewed, 0)) OVER ()/
547 DECODE(SUM(cur.curr_expired) OVER (), 0, NULL, SUM(cur.curr_expired) OVER ())) * 100
548 , NULL) OKI_CALC_ITEM3
549 , (SUM(NVL(cur.bkd_xtd,0)) OVER ()/
550 Decode(SUM(cur.exp_bal) OVER (),0,NULL,SUM(cur.exp_bal) OVER ())) * 100 OKI_CALC_ITEM4
551 , NVL2(COALESCE(SUM(cur.prev_renewed) OVER(), SUM(cur.prev_expired) OVER ())
552 , (SUM(NVL(cur.prev_renewed, 0)) OVER ()/
553 DECODE(SUM(cur.prev_expired) OVER (), 0, NULL, SUM(cur.prev_expired) OVER ())) * 100
554 , NULL) OKI_CALC_ITEM5
555 , (SUM(NVL(cur.bkd_xtd_p,0)) OVER ()/
556 Decode(SUM(cur.exp_bal_p) OVER (),0,NULL,SUM(cur.exp_bal_p) OVER ())) * 100 OKI_CALC_ITEM6
557 ,cur.bkd_xtd OKI_MEASURE_1
558 ,cur.bkd_xtd_p OKI_MEASURE_2
559 ,cur.exp_bal OKI_MEASURE_3
560 ,cur.exp_bal_p OKI_MEASURE_4
561 FROM ( SELECT itd.org_id org_id, nvl(itd.cblog,0)+nvl(cur.bklg_amt,0)-
562 (case when &BIS_CURRENT_EFFECTIVE_START_DATE -1 > c.ent_year_start_date
563 then nvl(cur.blg_rd_amt,0) else 0 end ) exp_bal,
564 nvl(itd.pblog,0)+nvl(cur.bklg_amt_p,0)-
565 (case when &BIS_PREVIOUS_EFFECTIVE_START_DATE -1 > p.ent_year_start_date
566 then nvl(cur.blg_rd_amt_p,0) else 0 end ) exp_bal_p,
567 cur.curr_blogn bkd_xtd, cur.prev_blogn bkd_xtd_p,cur.curr_expired, cur.prev_expired
568 ,cur.curr_renewed,cur.prev_renewed
569 FROM '|| l_itd || ', '|| l_cur_sql || ' and itd.org_id = cur.org_id(+)
570 UNION
571 SELECT cur.org_id org_id, nvl(itd.cblog,0)+nvl(cur.bklg_amt,0)-
572 (case when &BIS_CURRENT_EFFECTIVE_START_DATE -1 > c.ent_year_start_date
573 then nvl(cur.blg_rd_amt,0) else 0 end ) exp_bal,
574 nvl(itd.pblog,0)+nvl(cur.bklg_amt_p,0)-
575 (case when &BIS_PREVIOUS_EFFECTIVE_START_DATE -1 > p.ent_year_start_date
576 then nvl(cur.blg_rd_amt_p,0) else 0 end ) exp_bal_p,
577 cur.curr_blogn bkd_xtd
578 ,cur.prev_blogn bkd_xtd_p,cur.curr_expired,cur.prev_expired
579 , cur.curr_renewed, cur.prev_renewed
580 FROM '|| l_itd || ', '|| l_cur_sql || ' and itd.org_id (+) = cur.org_id
581 ) cur,fii_operating_units_v v WHERE cur.org_id = v.id &ORDER_BY_CLAUSE ';
582
583 x_custom_sql := '/* OKI_DBI_RATES_KPI */' || l_sqltext;
584
585 l_custom_rec.attribute_name := '&SEC_ID';
586 l_custom_rec.attribute_value := oki_dbi_util_pvt.get_sec_profile;
587 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
588 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
589 x_custom_output.EXTEND;
590 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
591
592 /* DEBUG
593 OKI_DBIDEBUG_PVT.check_portal_value('OKI_DBI_BAL','SQL',l_SQLText);
594 COMMIT;
595 */
596
597
598 END GET_KPI_RATES_SQL ;
599
600
601 PROCEDURE GET_CBALANCE_TREND_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
602 x_custom_sql OUT NOCOPY VARCHAR2,
603 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
604 l_pattern NUMBER;
605 l_period_type_id NUMBER;
606 l_SQLText varchar2(20000);
607 l_view_by varchar2(200);
608 l_as_of_date date;
609 l_period varchar2(50);
610 l_org varchar2(200);
611 l_xtd varchar2(10);
612 l_comparison_type varchar2(1) := 'Y';
613 l_org_where varchar2(500);
614 l_cur_suffix varchar2(2);
615 l_period_code varchar2(1);
616 l_bgn_k_amt varchar2(20);
617 l_nw_amt varchar2(20);
618 l_rn_amt varchar2(20);
619 l_x_amt varchar2(20);
620 l_t_amt varchar2(20);
621 l_xtd_sql varchar2(5000);
625 l_case varchar2(3000);
622 l_bbal_cur varchar2(5000);
623 l_org_where_value varchar2(5000);
624 l_rep_sql varchar2(5000);
626 p_case varchar2(3000);
627 l_lag_id varchar2(3000);
628 l_custom_rec BIS_QUERY_ATTRIBUTES;
629
630 BEGIN
631
632 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
633 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
634
635 OKI_DBI_UTIL_PVT.get_parameter_values(p_page_parameter_tbl, l_view_by, l_period, l_org, l_comparison_type, l_xtd,l_as_of_date, l_cur_suffix,l_pattern,l_period_type_id,l_period_code);
636
637 /* DEBUG
638 OKI_DBI_DEBUG_PVT.g_portal_name := 'OKI_DBI_BAL_TRND';
639 OKI_DBI_DEBUG_PVT.check_portal_param(p_page_parameter_tbl);
640 */
641
642 l_nw_amt := 's_nw_amt_'|| l_cur_suffix;
643 l_rn_amt := 's_rn_amt_'|| l_cur_suffix;
644 l_x_amt := 'x_amt_'|| l_cur_suffix;
645 l_t_amt := 't_amt_'|| l_cur_suffix;
646 l_bgn_k_amt := 'bgn_k_amt_' || l_cur_suffix;
647
648
649 l_org_where := OKI_DBI_UTIL_PVT.get_org_where('ORGANIZATION',l_org);
650
651 /* brrao modified -- as this is failing for the join clause with fii_time */
652
653 l_org_where_value := l_org_where ;
654 IF l_org_where_value IS NOT NULL then
655 l_org_where_value :=
656 replace(l_org_where_value,
657 'AND ',
658 'AND ( fact.authoring_org_id is null or ') || ') ' ;
659 END IF ;
660
661
662
663 if(l_comparison_type = 'Y' and l_xtd <> 'YTD') then
664 l_rep_sql := ' and cal.report_date = (case when (fii.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
665 then least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE)
666 else least(fii.end_date, &BIS_CURRENT_ASOF_DATE)
667 end )';
668 else
669 l_rep_sql := ' and cal.report_date in (least(fii.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
670 and cal.report_date between fii.start_date and fii.end_date ';
671 end if;
672
673 l_case := 'CASE when (fii.start_date between &BIS_CURRENT_REPORT_START_DATE
674 and &BIS_CURRENT_ASOF_DATE
675 and cal.report_date = least(fii.end_date,&BIS_CURRENT_ASOF_DATE))';
676
677 p_case := ' CASE WHEN (fii.start_date between &BIS_PREVIOUS_REPORT_START_DATE
678 and &BIS_PREVIOUS_ASOF_DATE
679 and cal.report_date = least(fii.end_date,&BIS_PREVIOUS_ASOF_DATE ))';
680
681 l_lag_id := 'decode (&BIS_TIME_COMPARISON_TYPE, ''SEQUENTIAL'',1 ,''YEARLY'',
682 (CASE &BIS_PERIOD_TYPE WHEN ''FII_TIME_ENT_PERIOD'' THEN 12
683 WHEN ''FII_TIME_ENT_QTR'' THEN 4
684 WHEN ''FII_TIME_ENT_YEAR'' THEN 1
685 END))';
686
687
688 /* Handles the overlapping YTD/ITD problem */
689
690 /* brrao modified ITD org where clause to inline view for fact table */
691 l_bbal_cur :=
692 '(select itd.cbal + (case when (&BIS_CURRENT_REPORT_START_DATE - 1 < itd.start_date)
693 then 0
694 else nvl(ytd.cbal,0) end) curbal
695 ,itd.pbal + (case when (&BIS_PREVIOUS_REPORT_START_DATE -1 < itd.p_start_date)
696 then 0
697 else nvl(ytd.pbal,0) end) prevbal
698 from ( select cbal,pbal,start_date,p_start_date from
699 (select NVL(SUM( '||l_bgn_k_amt ||'), 0) cbal
700 ,NVL(lag(SUM( '||l_bgn_k_amt ||'),1) OVER (order by t.start_date),0) pbal
701 ,t.start_date
702 ,lag (t.start_date, 1) OVER (ORDER BY t.start_date ) p_start_date
703 from
704 (select '||l_bgn_k_amt ||', ent_year_id, authoring_org_id
705 from oki_scm_o_1_mv
706 where 1=1 ' || l_org_where || '
707 ) fact
708 , fii_time_ent_year t
709 where fact.ent_year_id (+) = t.ent_year_id
710 and ( &BIS_CURRENT_REPORT_START_DATE BETWEEN t.start_date AND t.end_date
711 OR &BIS_PREVIOUS_REPORT_START_DATE BETWEEN t.start_date AND t.end_date)
712 group by t.start_date
713 order by t.start_date desc
714 ) where rownum = 1
715 ) itd,
716 (select cbal,pbal from
717 (select SUM( NVL( '||l_nw_amt||', 0) + NVL('||l_rn_amt||', 0) -
718 NVL( '||l_x_amt||' , 0) - NVL( '||l_t_amt||' , 0)) cbal
719 ,lag(SUM( NVL( '||l_nw_amt||', 0) + NVL('||l_rn_amt||', 0) -
720 NVL( '||l_x_amt||' , 0) - NVL( '||l_t_amt||' , 0)),1)
721 OVER (order by cal.report_date) pbal
722 from
723 ( select * from oki_scm_o_2_mv
724 where 1=1 ' || l_org_where || '
725 ) fact
726 , fii_time_rpt_struct_v cal
727 where fact.time_id(+) = cal.time_id
728 and cal.report_date in ( &BIS_CURRENT_REPORT_START_DATE - 1,
729 &BIS_PREVIOUS_REPORT_START_DATE -1)
730 and bitand(cal.record_type_id,119 ) = cal.record_type_id
731 group by cal.report_date
732 ORDER BY cal.report_date DESC
733 ) where rownum=1
734 ) ytd
735 ) begbal,';
736
737
738 l_xtd_sql :=
739 '(select cur_xtd.name,cur_xtd.c_sum,cur_xtd.p_sum, f.start_date
740 from (select name
741 , sum(cur_sum) over
742 (order by start_date ROWS UNBOUNDED PRECEDING) c_sum
743 , sum(pre_sum) over (order by start_date ROWS UNBOUNDED PRECEDING) p_sum
744 , start_date
745 from (Select fii.name
746 , fii.start_date
750 NVL( '||l_t_amt||' , 0))
747 , SUM(' || l_case ||' then (NVL( '||l_nw_amt||', 0) +
748 NVL( '||l_rn_amt||' , 0) -
749 NVL( '||l_x_amt||' , 0) -
751 else 0 end ) cur_sum
752 , lag (SUM(' || p_case ||' then (NVL( '||l_nw_amt||', 0) +
753 NVL( '||l_rn_amt||' , 0) -
754 NVL( '||l_x_amt||' , 0) -
755 NVL( '||l_t_amt||' , 0))
756 else 0 end ), ' || l_lag_id || ')
757 OVER (order by fii.start_date) pre_sum
758 from ( select * from oki_scm_o_2_mv
759 where 1=1 ' || l_org_where || '
760 ) fact, '|| l_period ||' fii
761 , fii_time_rpt_struct_v cal
762 where fii.start_date
763 between &BIS_PREVIOUS_REPORT_START_DATE
764 and &BIS_CURRENT_ASOF_DATE
765 ' || l_rep_sql || '
766 and bitand(cal.record_type_id,&BIS_NESTED_PATTERN ) = cal.record_type_id
767 and fact.time_id (+) = cal.time_id
768 group by fii.name,fii.start_date
769 ) cur
770 ) cur_xtd ,' || l_period || ' f
771 WHERE cur_xtd.start_date(+) = f.start_date
772 AND f.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
773 AND &BIS_CURRENT_ASOF_DATE
774 ORDER BY f.start_date ) XTD ' ;
775 -- NVL(pbalance, 0) OKI_MEASURE_2,
776
777 l_SQLText := 'select curr_bal.name VIEWBY,
778 NVL(pbalance, 0) OKI_MEASURE_1,' ||
779 oki_dbi_util_pvt.change_clause('cbalance','pbalance') || ' OKI_MEASURE_2,
780 NVL(cbalance, 0) OKI_MEASURE_3
781 from
782 ( select xtd.name
783 , NVL(begbal.curbal, 0) + NVL(xtd.c_sum, 0) cbalance
784 , NVL(begbal.prevbal, 0) + NVL(xtd.p_sum, 0) pbalance
785 from '|| l_bbal_cur || l_xtd_sql ||'
786 ) curr_bal ';
787
788 x_custom_sql := '/* OKI_DBI_K_BALANCE_G */' || l_SQLText;
789
790 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
791 l_custom_rec.attribute_value := 'TIME+'||l_period;
792 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
793 x_custom_output.EXTEND;
794 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
795
796
797 l_custom_rec.attribute_name := '&SEC_ID';
798 l_custom_rec.attribute_value := oki_dbi_util_pvt.get_sec_profile;
799 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
800 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
801 x_custom_output.EXTEND;
802 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
803
804 --return l_SQLText ;
805
806 END GET_CBALANCE_TREND_SQL ;
807
808
809 END OKI_DBI_SCM_PKG ;
810