[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_UTIL_PVT
Source
1 PACKAGE BODY OKI_DBI_UTIL_PVT AS
2 /* $Header: OKIRDBIB.pls 120.2 2006/02/06 00:50:15 pubalasu noship $ */
3
4 PROCEDURE populate_mv_bmap (
5 p_mv_bmap_tbl OUT NOCOPY oki_dbi_mv_bmap_tbl
6 , p_mv_set IN VARCHAR2);
7
8 -- FUNCTION get_where_clauses (
9 -- p_dim_map OUT poa_dbi_util_pkg.poa_dbi_dim_map
10 -- , p_trend IN VARCHAR2)
11 -- RETURN VARCHAR2;
12
13 PROCEDURE split_pseudo_rs_group (
14 p_param IN bis_pmv_page_parameter_tbl);
15
16 FUNCTION current_period_start_date (
17 as_of_date IN DATE
18 , period_type IN VARCHAR2)
19 RETURN DATE
20 IS
21 l_date DATE;
22 BEGIN
23 IF (period_type = 'YTD')
24 THEN
25 l_date := fii_time_api.ent_cyr_start (as_of_date);
26 ELSIF (period_type = 'QTD')
27 THEN
28 l_date := fii_time_api.ent_cqtr_start (as_of_date);
29 ELSIF (period_type = 'MTD')
30 THEN
31 l_date := fii_time_api.ent_cper_start (as_of_date);
32 ELSIF (period_type = 'WTD')
33 THEN
34 l_date := fii_time_api.cwk_start (as_of_date);
35 END IF;
36
37 RETURN l_date;
38 EXCEPTION
39 WHEN OTHERS
40 THEN
41 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
42 fnd_message.set_name (application => 'FND'
43 , NAME => 'CRM-DEBUG ERROR');
44 fnd_message.set_token (token => 'ROUTINE'
45 , VALUE => 'OKI_DBI_UTIL_PVT.current_period_start_date ');
46 bis_collection_utilities.put_line (fnd_message.get);
47 raise_application_error (-20000
48 , 'Stack Dump Follows =>'
49 , TRUE);
50 END current_period_start_date;
51
52 /******************************************************************************
53 Description: Retrieves the current period end date.
54 ******************************************************************************/
55 FUNCTION current_period_end_date (
56 as_of_date IN DATE
57 , period_type IN VARCHAR2)
58 RETURN DATE
59 IS
60 l_date DATE;
61 BEGIN
62 IF (period_type = 'YTD')
63 THEN
64 l_date := fii_time_api.ent_cyr_end (as_of_date);
65 ELSIF (period_type = 'QTD')
66 THEN
67 l_date := fii_time_api.ent_cqtr_end (as_of_date);
68 ELSIF (period_type = 'MTD')
69 THEN
70 l_date := fii_time_api.ent_cper_end (as_of_date);
71 ELSIF (period_type = 'WTD')
72 THEN
73 l_date := fii_time_api.cwk_end (as_of_date);
74 END IF;
75
76 RETURN l_date;
77 EXCEPTION
78 WHEN OTHERS
79 THEN
80 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
81 fnd_message.set_name (application => 'FND'
82 , NAME => 'CRM-DEBUG ERROR');
83 fnd_message.set_token (token => 'ROUTINE'
84 , VALUE => 'OKI_DBI_UTIL_PVT.current_period_end_date ');
85 bis_collection_utilities.put_line (fnd_message.get);
86 raise_application_error (-20000
87 , 'Stack Dump Follows =>'
88 , TRUE);
89 END current_period_end_date;
90
91 FUNCTION previous_period_start_date (
92 as_of_date IN DATE
93 , period_type IN VARCHAR2
94 , comparison_type IN VARCHAR2)
95 RETURN DATE
96 IS
97 l_prev_date DATE;
98 l_date DATE;
99 BEGIN
100 /* Temporary fix until fii fixes the problem */
101 IF (comparison_type = 'S')
102 THEN
103 IF (period_type = 'YTD')
104 THEN
105 SELECT fii.start_date
106 INTO l_date
107 FROM fii_time_ent_year fii
108 WHERE (SELECT fii.start_date - 1
109 FROM fii_time_ent_year fii
110 WHERE as_of_date BETWEEN fii.start_date AND fii.end_date) BETWEEN fii.start_date AND fii.end_date;
111 ELSE
112 IF (period_type = 'QTD')
113 THEN
114 SELECT fii.start_date
115 INTO l_date
116 FROM fii_time_ent_qtr fii
117 WHERE (SELECT fii.start_date - 1
118 FROM fii_time_ent_qtr fii
119 WHERE as_of_date BETWEEN fii.start_date AND fii.end_date) BETWEEN fii.start_date AND fii.end_date;
120 ELSE
121 SELECT fii.start_date
122 INTO l_date
123 FROM fii_time_ent_period fii
124 WHERE (SELECT fii.start_date - 1
125 FROM fii_time_ent_period fii
126 WHERE as_of_date BETWEEN fii.start_date AND fii.end_date) BETWEEN fii.start_date AND fii.end_date;
127 END IF;
128 END IF;
129 ELSE
130 l_prev_date := previous_period_asof_date (as_of_date
131 , period_type
132 , comparison_type);
133 l_date := current_period_start_date (l_prev_date
134 , period_type);
135 END IF;
136
137 RETURN l_date;
138 EXCEPTION
139 WHEN OTHERS
140 THEN
141 RETURN bis_common_parameters.get_global_start_date;
142 END previous_period_start_date;
143
144 FUNCTION current_report_start_date (
145 as_of_date IN DATE
146 , period_type IN VARCHAR2)
147 RETURN DATE
148 IS
149 l_date DATE;
150 l_curr_year NUMBER;
151 l_curr_qtr NUMBER;
152 l_curr_period NUMBER;
153 l_week_start_date DATE;
154 BEGIN
155 IF (period_type = 'YTD')
156 THEN
157 SELECT SEQUENCE
158 INTO l_curr_year
159 FROM fii_time_ent_year
160 WHERE as_of_date BETWEEN start_date AND end_date;
161
162 SELECT start_date
163 INTO l_date
164 FROM fii_time_ent_year
165 WHERE SEQUENCE = l_curr_year - 3;
166 END IF;
167
168 IF (period_type = 'QTD')
169 THEN
170 SELECT SEQUENCE
171 , ent_year_id
172 INTO l_curr_qtr
173 , l_curr_year
174 FROM fii_time_ent_qtr
175 WHERE as_of_date BETWEEN start_date AND end_date;
176
177 IF (l_curr_qtr = 4)
178 THEN
179 l_date := fii_time_api.ent_cyr_start (as_of_date);
180 ELSE
181 SELECT start_date
182 INTO l_date
183 FROM fii_time_ent_qtr
184 WHERE SEQUENCE = l_curr_qtr + 1
185 AND ent_year_id = l_curr_year - 1;
186 END IF;
187 END IF;
188
189 IF (period_type = 'MTD')
190 THEN
191 SELECT p.SEQUENCE
192 , q.ent_year_id
193 INTO l_curr_period
194 , l_curr_year
195 FROM fii_time_ent_period p
196 , fii_time_ent_qtr q
197 WHERE p.ent_qtr_id = q.ent_qtr_id
198 AND as_of_date BETWEEN p.start_date AND p.end_date;
199
200 SELECT start_date
201 INTO l_date
202 FROM (SELECT p.start_date
203 FROM fii_time_ent_period p
204 , fii_time_ent_qtr q
205 WHERE p.ent_qtr_id = q.ent_qtr_id
206 AND ( ( p.SEQUENCE = l_curr_period + 1
207 AND q.ent_year_id = l_curr_year - 1)
208 OR ( p.SEQUENCE = 1
209 AND q.ent_year_id = l_curr_year))
210 ORDER BY p.start_date)
211 WHERE ROWNUM <= 1;
212 /* select p.start_date
213 into l_date
214 from fii_time_ent_period p, fii_time_ent_qtr q
215 where p.ent_qtr_id=q.ent_qtr_id
216 and p.sequence=l_curr_period+1 -- temp fix for 12 points on graph else 13 points brrao modified
217 and q.ent_year_id=l_curr_year-1;
218 */
219 END IF;
220
221 IF (period_type = 'WTD')
222 THEN
223 SELECT start_date
224 INTO l_week_start_date
225 FROM fii_time_week
226 WHERE as_of_date BETWEEN start_date AND end_date;
227
228 SELECT start_date
229 INTO l_date
230 FROM fii_time_week
231 WHERE start_date = l_week_start_date - 7 * 12;
232 END IF;
233
234 RETURN l_date;
235 EXCEPTION
236 WHEN OTHERS
237 THEN
238 RETURN bis_common_parameters.get_global_start_date;
239 END current_report_start_date;
240
241 FUNCTION previous_report_start_date (
242 as_of_date IN DATE
243 , period_type IN VARCHAR2
244 , comparison_type IN VARCHAR2)
245 RETURN DATE
246 IS
247 l_prev_date DATE;
248 l_date DATE;
249 BEGIN
250 l_prev_date := previous_period_asof_date (as_of_date
251 , period_type
252 , comparison_type);
253 l_date := current_report_start_date (l_prev_date
254 , period_type);
255 RETURN l_date;
256 EXCEPTION
257 WHEN OTHERS
258 THEN
259 bis_collection_utilities.put_line ('Error in function previous_report_start_date : ' || SQLERRM || '' || SQLCODE);
260 raise_application_error (-20000
261 , 'Stack Dump Follows =>'
262 , TRUE);
263 END previous_report_start_date;
264
265 FUNCTION previous_period_asof_date (
266 as_of_date IN DATE
267 , period_type IN VARCHAR2
268 , comparison_type IN VARCHAR2)
269 RETURN DATE
270 IS
271 l_date DATE;
272 BEGIN
273 IF (period_type = 'YTD')
274 THEN
275 l_date := fii_time_api.ent_sd_lyr_end (as_of_date);
276 ELSIF (period_type = 'QTD')
277 THEN
278 IF (comparison_type = 'Y')
279 THEN
280 l_date := fii_time_api.ent_sd_lysqtr_end (as_of_date);
281 ELSE
282 l_date := fii_time_api.ent_sd_pqtr_end (as_of_date);
283 END IF;
284 ELSIF (period_type = 'MTD')
285 THEN
286 IF (comparison_type = 'Y')
287 THEN
288 l_date := fii_time_api.ent_sd_lysper_end (as_of_date);
289 ELSE
290 l_date := fii_time_api.ent_sd_pper_end (as_of_date);
291 END IF;
292 ELSIF (period_type = 'WTD')
293 THEN
294 IF (comparison_type = 'Y')
295 THEN
296 l_date := fii_time_api.sd_lyswk (as_of_date);
297 ELSE
298 l_date := fii_time_api.sd_pwk (as_of_date);
299 END IF;
300 END IF;
301
302 RETURN l_date;
303 EXCEPTION
304 WHEN OTHERS
305 THEN
306 RETURN bis_common_parameters.get_global_start_date - 1;
307 /* making sure it's < current_report_date */
308
309 END previous_period_asof_date;
310
311 -- -----------------------------------------------------------------------------
312 -- get_sec_profile: Get the security profile.
313 -- -----------------------------------------------------------------------------
314 FUNCTION get_sec_profile
315 RETURN NUMBER
316 IS
317 l_sec_profile NUMBER;
318 BEGIN
319 l_sec_profile := NVL (fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
320 RETURN l_sec_profile;
321 EXCEPTION
322 WHEN OTHERS
323 THEN
324 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
325 fnd_message.set_name (application => 'FND'
326 , NAME => 'CRM-DEBUG ERROR');
327 fnd_message.set_token (token => 'ROUTINE'
328 , VALUE => 'OKI_DBI_UTIL_PVT.get_sec_profile ');
329 bis_collection_utilities.put_line (fnd_message.get);
330 raise_application_error (-20000
331 , 'Stack Dump Follows =>'
332 , TRUE);
333 END get_sec_profile;
334
335 -- ---------------------------------------------
336 -- get_org_where clause funtion for OU security
337 -- --------------------------------------------
338
339 FUNCTION get_org_where (
340 p_name IN VARCHAR2
341 , p_org IN VARCHAR2)
342 RETURN VARCHAR2
343 IS
344 l_org_where VARCHAR2 (500);
345 BEGIN
346 IF (p_name = 'ORGANIZATION')
347 THEN
348 IF ( p_org IS NULL
349 OR p_org = ''
350 OR p_org = 'All')
351 THEN
352 l_org_where :=
353 ' AND authoring_org_id IN (
354 SELECT pol.organization_id
355 FROM per_organization_list pol
356 WHERE pol.security_profile_id
357 = &SEC_ID ) ';
358 ELSE
359 l_org_where := ' AND authoring_org_id = &ORGANIZATION+FII_OPERATING_UNITS';
360 END IF;
361 ELSE
362 l_org_where := '';
363 END IF;
364
365 RETURN l_org_where;
366 EXCEPTION
367 WHEN OTHERS
368 THEN
369 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
370 fnd_message.set_name (application => 'FND'
371 , NAME => 'CRM-DEBUG ERROR');
372 fnd_message.set_token (token => 'ROUTINE'
373 , VALUE => 'OKI_DBI_UTIL_PVT.get_org_where ');
374 bis_collection_utilities.put_line (fnd_message.get);
375 raise_application_error (-20000
376 , 'Stack Dump Follows =>'
377 , TRUE);
378 END get_org_where;
379
380 -- -----------------------------------------------------
381 -- get_nested_cols () clause to get upper sql conditions
382 -- brrao added
383 -- -----------------------------------------------------
384 FUNCTION get_nested_cols (
385 p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
386 ,period_type IN VARCHAR2
387 ,P_TREND in varchar2 )
388 RETURN VARCHAR2 IS
389
390 l_str VARCHAR2 (10000);
391 C_DATE varchar2(100);
392 p_date varchar2(100);
393 BEGIN
394
395 IF P_TREND = 'Y' THEN
396 p_date := '&BIS_PREVIOUS_REPORT_START_DATE -1';
397 c_date := '&BIS_CURRENT_REPORT_START_DATE -1';
398
399 IF period_type in ('ITD','YTD') THEN
400 p_date := '&BIS_PREVIOUS_ASOF_DATE';
401 c_date := '&BIS_CURRENT_ASOF_DATE ';
402 END IF;
403 /* IF period_type = 'YTD' THEN
404 p_date := '&BIS_PREVIOUS_ASOF_DATE';
405 c_date := '&BIS_CURRENT_ASOF_DATE ';
406 END IF;
407 */
408 ----
409 ELSE
410 p_date := '&BIS_PREVIOUS_ASOF_DATE';
411 c_date := '&BIS_CURRENT_ASOF_DATE ';
412 END IF;
413
414 if period_type in ('ITD','YTD') then
415 FOR i IN 1 .. p_col_name.COUNT
416 LOOP
417 IF (p_col_name(i).to_date_type IN ('ITD','YTD'))
418 THEN
419 L_str := l_str ||',SUM(decode(cal.report_date,'|| c_date ||','|| p_col_name(i).column_name||')) c_'|| p_col_name(i).column_alias;
420
421 IF (p_col_name(i).prior_code <> poa_dbi_util_pkg.no_priors)
422 THEN
423 L_str :=l_str||',SUM(decode(cal.report_date,'|| p_date ||','|| p_col_name(i).column_name||')) p_'|| p_col_name(i).column_alias;
424
425 END IF;
426 ELSE
427 IF(p_trend <> 'Y')
428 THEN
429 L_str := l_str ||',TO_NUMBER(null) c_' || p_col_name(i).column_alias;
430 -- Prev column (based on prior_code)
431 IF (p_col_name(i).prior_code <> poa_dbi_util_pkg.no_priors)
432 THEN
433 L_str := l_str ||',TO_NUMBER(null) P_' || p_col_name(i).column_alias;
434 END IF;
435 END IF;
436 END IF;
437 END LOOP;
438 ELSE -- if type = XTD for all nested cases
439 FOR i IN 1 .. p_col_name.COUNT
440 LOOP
441 -- use this only if its not a YTD measure ie only for all xtd measures
442 IF ( p_col_name(i).to_date_type <> 'YTD' ) THEN
443 L_str := l_str ||',SUM(decode(cal.report_date,'|| c_date ||','|| p_col_name(i).column_name||',null)) c_'|| p_col_name(i).column_alias
444
445 || '
446 ';
447 IF (p_col_name(i).grand_total = 'Y')
448 THEN
449 L_str := l_str ||',sum(sum(decode(cal.report_date,'|| c_date ||','|| p_col_name(i).column_name||',null))) over() c_'|| p_col_name(i).column_alias || '_total
450
451 ';
452 END IF;
453 IF (p_col_name(i).prior_code <> poa_dbi_util_pkg.no_priors)
454 THEN
455 L_str := l_str ||',SUM(decode(cal.report_date,'|| p_date ||','|| p_col_name(i).column_name||',null)) p_'|| p_col_name(i).column_alias
456
457 || '
458 ';
459 IF (p_col_name(i).grand_total = 'Y')
460 THEN
461 L_str := l_str ||',sum(sum(decode(cal.report_date,'|| p_date ||','|| p_col_name(i).column_name||',null))) over() p_'|| p_col_name(i).column_alias || '_total
462
463 ';
464 END IF;
465 END IF;
466 END IF;
467 END LOOP;
468 end IF; -- end if ITD
469
470 return l_str;
471
472 END get_nested_cols;
473
474 -- ------------------------------------------------
475 -- get_itd_where clause to get itd where conditions
476 -- brrao added
477 -- ------------------------------------------------
478 FUNCTION get_itd_where (
479 p_mv_name IN VARCHAR2
480 , p_trend IN VARCHAR2 )
481 RETURN VARCHAR2 IS
482 l_str VARCHAR2 (500);
483 C_DATE varchar2(100);
484 p_date varchar2(100);
485 BEGIN
486
487 /* IF P_TREND = 'Y' THEN
488 -- p_date := '&BIS_PREVIOUS_REPORT_START_DATE -1';
489 -- c_date := '&BIS_CURRENT_REPORT_START_DATE -1';
490 -- p_date := '&BIS_PREVIOUS_ASOF_DATE -1';
491 c_date := '&BIS_CURRENT_ASOF_DATE -1';
492
493 ELSE
494 p_date := '&BIS_PREVIOUS_ASOF_DATE';
495 c_date := '&BIS_CURRENT_ASOF_DATE ';
496 END IF;
497 */
498 p_date := '&BIS_PREVIOUS_ASOF_DATE';
499 c_date := '&BIS_CURRENT_ASOF_DATE ';
500
501 L_str := ' FROM '|| P_MV_NAME || ' fact, fii_time_day cal ' ||
502 ' WHERE 1 = 1 '||
503 ' AND fact.ent_year_id = cal.ent_year_id '||
504 ' AND cal.report_date IN ( '|| c_date ||','||p_date ||')';
505
506 RETURN L_str;
507 END get_itd_where;
508
509 -- ---------------------------------------------------
510 -- get_xtd_where () clause to get itd where conditions
511 -- brrao added
512 -- ---------------------------------------------------
513 FUNCTION get_xtd_where (
514 p_mv_name IN VARCHAR2
515 , p_trend IN VARCHAR2
516 , p_type IN VARCHAR2
517 ,p_pattern in VARCHAR2 := NULL)
518 RETURN VARCHAR2 IS
519 l_str VARCHAR2 (500);
520 C_DATE varchar2(100);
521 l_patt varchar2(50);
522 p_date varchar2(100);
523 BEGIN
524
525 IF P_TREND = 'Y' THEN
526 p_date := '&BIS_PREVIOUS_REPORT_START_DATE';
527 c_date := '&BIS_CURRENT_REPORT_START_DATE ';
528 IF ( p_type = 'YTD' ) then
529 p_date := '&BIS_PREVIOUS_REPORT_START_DATE - 1';
530 c_date := '&BIS_CURRENT_REPORT_START_DATE - 1';
531 END IF;
532 ELSE
533 p_date := '&BIS_PREVIOUS_ASOF_DATE';
534 c_date := '&BIS_CURRENT_ASOF_DATE ';
535 END IF;
536
537 IF (p_pattern is null ) then
538 l_patt := '&BIS_NESTED_PATTERN';
539 ELSE
540 l_patt := p_pattern;
541 END IF;
542 L_str := ' FROM '|| P_MV_NAME || ' fact, fii_time_rpt_struct_v cal ' ||
543 ' WHERE 1 = 1 '||
544 ' AND fact.time_id = cal.time_id '||
545 ' AND cal.report_date IN ( '|| c_date ||','||p_date ||')
546 and bitand(cal.record_type_id, '||l_patt || ') = cal.record_type_id';
547
548 RETURN L_str;
549 END get_xtd_where;
550
551 -- --------------------------------------
552 -- get_dbi_params for as_of_date format
553 -- ----------------------------------------
554
555 FUNCTION get_dbi_params (
556 region_id IN VARCHAR2)
557 RETURN VARCHAR2
558 IS
559 currency fii_currencies_v.VALUE%TYPE;
560 BEGIN
561 currency := 'FII_GLOBAL1';
562
563 /* '&'||'ORGANIZATION=All';-- ||
564 '&'||'SEC_ID=230';
565 '&'||'BIS_TIME_COMPARISON_TYPE=SEQUENTIAL'||
566 '&'||'&BIS_PERIOD_TYPE = FII_TIME_ENT_PERIOD'
567 ||
568 '&'||'VIEW_BY= ALL';
569
570 */
571
572 /* Modified by brrao test for initiailization params */
573 IF (region_id = 'OKI_DBI_SCM_OU_PARAM')
574 THEN
575 RETURN '&' || 'AS_OF_DATE=' || fnd_date.date_to_chardate (TRUNC (SYSDATE)) || '&' || 'CURRENCY=' || currency;
576 ELSIF (region_id = 'OKI_DBI_K_BALANCE_G')
577 THEN
578 RETURN '&' || 'AS_OF_DATE=' || fnd_date.date_to_chardate (TRUNC (SYSDATE)) || '&' || 'CURRENCY=' || currency;
579 ELSE
580 RETURN NULL;
581 END IF;
582 EXCEPTION
583 WHEN OTHERS
584 THEN
585 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
586 fnd_message.set_name (application => 'FND'
587 , NAME => 'CRM-DEBUG ERROR');
588 fnd_message.set_token (token => 'ROUTINE'
589 , VALUE => 'OKI_DBI_UTIL_PVT.get_dbi_params ');
590 bis_collection_utilities.put_line (fnd_message.get);
591 raise_application_error (-20000
592 , 'Stack Dump Follows =>'
593 , TRUE);
594 END get_dbi_params;
595
596 -- -------------------------------
597 -- get_global_currency
598 -- -------------------------------
599 FUNCTION get_global_currency
600 RETURN VARCHAR2
601 IS
602 BEGIN
603 RETURN bis_common_parameters.get_currency_code;
604 EXCEPTION
605 WHEN OTHERS
606 THEN
607 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
608 fnd_message.set_name (application => 'FND'
609 , NAME => 'CRM-DEBUG ERROR');
610 fnd_message.set_token (token => 'ROUTINE'
611 , VALUE => 'OKI_DBI_UTIL_PVT.get_global_currency ');
612 bis_collection_utilities.put_line (fnd_message.get);
613 raise_application_error (-20000
614 , 'Stack Dump Follows =>'
615 , TRUE);
616 END get_global_currency;
617
618 -- -------------------------------
619 -- get_display_currency
620 -- -------------------------------
621 FUNCTION get_display_currency (
622 p_currency_code IN VARCHAR2
623 , p_selected_operating_unit IN VARCHAR2)
624 RETURN VARCHAR2
625 IS
626 l_global_currency_code VARCHAR2 (3);
627 l_operating_unit VARCHAR2 (10);
628 l_functional_currency_code VARCHAR2 (3);
629 l_common_functional_currency VARCHAR2 (3);
630 l_sec_profile_id VARCHAR2 (10);
631 l_sec_profile NUMBER;
632 l_return_value VARCHAR2 (1);
633 BEGIN
634 l_sec_profile := get_sec_profile;
635 l_return_value := '0';
636 -- selected currency is the same as the global currency
637
638 IF (l_global_currency_code IS NULL)
639 THEN
640 l_global_currency_code := get_global_currency;
641 END IF;
642
643 IF (p_currency_code = 'FII_GLOBAL1')
644 THEN
645 RETURN '1'; -- always show the global currency
646 ELSE
647 -- Currency is not the global currency
648 IF (p_selected_operating_unit <> 'ALL')
649 THEN
650 IF ( p_selected_operating_unit <> l_operating_unit
651 OR l_operating_unit IS NULL)
652 THEN
653 SELECT currency_code
654 INTO l_functional_currency_code
655 FROM financials_system_params_all fsp
656 , gl_sets_of_books gsob
657 WHERE fsp.org_id = p_selected_operating_unit
658 AND fsp.set_of_books_id = gsob.set_of_books_id;
659
660 l_operating_unit := p_selected_operating_unit;
661 END IF;
662
663 IF ( (p_currency_code = l_functional_currency_code)
664 AND (l_global_currency_code <> l_functional_currency_code))
665 THEN
666 RETURN '1';
667 ELSE
668 RETURN '0';
669 END IF;
670 ELSE -- operating unit is 'All'
671 IF ( l_common_functional_currency IS NULL
672 OR NVL (l_sec_profile_id
673 , -1) <> l_sec_profile)
674 THEN
675 l_sec_profile_id := l_sec_profile;
676
677 SELECT DISTINCT currency_code
678 INTO l_common_functional_currency
679 FROM financials_system_params_all fsp
680 , gl_sets_of_books gsob
681 WHERE fsp.set_of_books_id = gsob.set_of_books_id
682 AND fsp.org_id IN (SELECT organization_id
683 FROM per_organization_list
684 WHERE security_profile_id = l_sec_profile);
685 END IF;
686
687 IF ( (p_currency_code = l_common_functional_currency)
688 AND (l_global_currency_code <> l_common_functional_currency))
689 THEN
690 RETURN '1';
691 ELSE
692 RETURN '0';
693 END IF;
694 END IF;
695 END IF;
696 EXCEPTION
697 WHEN TOO_MANY_ROWS
698 THEN
699 l_common_functional_currency := 'N/A';
700 RETURN '0';
701 WHEN OTHERS
702 THEN
703 RETURN '0';
704 END get_display_currency;
705
706 -- -----------------------------------------------------
707 -- get_parameter_values : Gets all the BIS parameters
708 -- -----------------------------------------------------
709 PROCEDURE get_parameter_values (
710 p_param IN bis_pmv_page_parameter_tbl
711 , p_view_by OUT NOCOPY VARCHAR2
712 , p_period_type OUT NOCOPY VARCHAR2
713 , p_org OUT NOCOPY VARCHAR2
714 , p_comparison_type OUT NOCOPY VARCHAR2
715 , p_xtd OUT NOCOPY VARCHAR2
716 , p_as_of_date OUT NOCOPY DATE
717 , p_cur_suffix OUT NOCOPY VARCHAR2
718 , p_pattern OUT NOCOPY NUMBER
719 , p_period_type_id OUT NOCOPY NUMBER
720 , p_period_type_code OUT NOCOPY VARCHAR2)
721 IS
722 l_currency VARCHAR2 (30);
723 BEGIN
724 FOR i IN 1 .. p_param.COUNT
725 LOOP
726 IF (p_param (i).parameter_name = 'VIEW_BY')
727 THEN
728 p_view_by := p_param (i).parameter_value;
729 END IF;
730
731 IF (p_param (i).parameter_name = 'PERIOD_TYPE')
732 THEN
733 p_period_type := p_param (i).parameter_value;
734 END IF;
735
736 IF (p_param (i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS')
737 THEN
738 p_org := p_param (i).parameter_value;
739 END IF;
740
741 IF (p_param (i).parameter_name = 'TIME_COMPARISON_TYPE')
742 THEN
743 IF (p_param (i).parameter_value = 'YEARLY')
744 THEN
745 p_comparison_type := 'Y';
746 ELSE
747 p_comparison_type := 'S';
748 END IF;
749 END IF;
750
751 IF (p_param (i).parameter_name = 'AS_OF_DATE')
752 THEN
753 p_as_of_date := TO_DATE (p_param (i).parameter_value
754 , 'DD-MM-YYYY');
755 END IF;
756
757 IF (p_param (i).parameter_name = 'CURRENCY+FII_CURRENCIES')
758 THEN
759 l_currency := p_param (i).parameter_id;
760 END IF;
761 END LOOP;
762
763 IF (p_period_type = 'FII_TIME_ENT_YEAR')
764 THEN
765 p_xtd := 'YTD';
766 p_period_type_id := 64;
767 p_period_type_code := 'y';
768 p_pattern := 119;
769 ELSIF (p_period_type = 'FII_TIME_ENT_QTR')
770 THEN
771 p_xtd := 'QTD';
772 p_period_type_id := 32;
773 p_period_type_code := 'q';
774 p_pattern := 55;
775 ELSE
776 -- Default values
777 p_period_type := 'FII_TIME_ENT_PERIOD';
778 p_xtd := 'MTD';
779 p_period_type_id := 16;
780 p_period_type_code := 'p';
781 p_pattern := 23;
782 /*
783 -- Not currently used
784 else p_xtd := 'WTD';
785 p_period_type_id := 1;
786 p_period_type_code := 'w' ;
787 p_pattern := 11;
788 */
789 END IF;
790
791 IF (p_as_of_date IS NULL)
792 THEN
793 p_as_of_date := SYSDATE;
794 END IF;
795
796 IF (p_comparison_type IS NULL)
797 THEN
798 p_comparison_type := 'S';
799 END IF;
800
801 IF (l_currency = '''FII_GLOBAL1''')
802 THEN
803 p_cur_suffix := 'g';
804 --Added by Arun.R for secondary global currency changes for OKI on Nov-05-03
805 ELSIF(l_currency = '''FII_GLOBAL2''') then
806 p_cur_suffix := 'sg';
807 ELSE
808 p_cur_suffix := 'f';
809 END IF;
810
811 IF (p_cur_suffix IS NULL)
812 THEN
813 p_cur_suffix := 'g';
814 END IF;
815 EXCEPTION
816 WHEN OTHERS
817 THEN
818 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
819 fnd_message.set_name (application => 'FND'
820 , NAME => 'CRM-DEBUG ERROR');
821 fnd_message.set_token (token => 'ROUTINE'
822 , VALUE => 'OKI_DBI_UTIL_PVT.get_parameter_values ');
823 bis_collection_utilities.put_line (fnd_message.get);
824 raise_application_error (-20000
825 , 'Stack Dump Follows =>'
826 , TRUE);
827 END get_parameter_values;
828
829 PROCEDURE get_drill_across_param_val (
830 p_param IN bis_pmv_page_parameter_tbl
831 , p_attribute_code_num1 OUT NOCOPY NUMBER
832 , p_attribute_code_num2 OUT NOCOPY NUMBER
833 , p_attribute_code_num3 OUT NOCOPY NUMBER
834 , p_attribute_code_num4 OUT NOCOPY NUMBER
835 , p_attribute_code_num5 OUT NOCOPY NUMBER
836 , p_attribute_code_char1 OUT NOCOPY VARCHAR2
837 , p_attribute_code_char2 OUT NOCOPY VARCHAR2
838 , p_attribute_code_char3 OUT NOCOPY VARCHAR2
839 , p_attribute_code_char4 OUT NOCOPY VARCHAR2
840 , p_attribute_code_char5 OUT NOCOPY VARCHAR2)
841 IS
842 BEGIN
843 FOR i IN 1 .. p_param.COUNT
844 LOOP
845 IF (p_param (i).parameter_name = 'pAttributeCodeNum1')
846 THEN
847 p_attribute_code_num1 := p_param (i).parameter_value;
848 ELSIF (p_param (i).parameter_name = 'pAttributeCodeNum2')
849 THEN
850 p_attribute_code_num2 := p_param (i).parameter_value;
851 ELSIF (p_param (i).parameter_name = 'pAttributeCodeNum3')
852 THEN
853 p_attribute_code_num3 := p_param (i).parameter_value;
854 ELSIF (p_param (i).parameter_name = 'pAttributeCodeNum4')
855 THEN
856 p_attribute_code_num4 := p_param (i).parameter_value;
857 ELSIF (p_param (i).parameter_name = 'pAttributeCodeNum5')
858 THEN
859 p_attribute_code_num5 := p_param (i).parameter_value;
860 ELSIF (p_param (i).parameter_name = 'pAttributeCodeChar1')
861 THEN
862 p_attribute_code_char1 := p_param (i).parameter_value;
863 ELSIF (p_param (i).parameter_name = 'pAttributeCodeChar2')
864 THEN
865 p_attribute_code_char2 := p_param (i).parameter_value;
866 ELSIF (p_param (i).parameter_name = 'pAttributeCodeChar3')
867 THEN
868 p_attribute_code_char3 := p_param (i).parameter_value;
869 ELSIF (p_param (i).parameter_name = 'pAttributeCodeChar4')
870 THEN
871 p_attribute_code_char4 := p_param (i).parameter_value;
872 ELSIF (p_param (i).parameter_name = 'pAttributeCodeChar5')
873 THEN
874 p_attribute_code_char5 := p_param (i).parameter_value;
875 ELSIF (p_param (i).parameter_name = 'OKI_STATUS+OKI_STATUS')
876 THEN
877 p_attribute_code_char5 := p_param (i).parameter_id;
878 ELSIF (p_param (i).parameter_name = 'OKI_STATUS+TERM_REASON')
879 THEN
880 p_attribute_code_char5 := p_param (i).parameter_id;
881 ELSIF (p_param (i).parameter_name = 'OKI_STATUS+EXP_STATUS')
882 THEN
883 p_attribute_code_num5 := TO_NUMBER (REPLACE (p_param (i).parameter_id
884 , ''''));
885 p_attribute_code_char5 := p_param (i).parameter_value;
886 ELSIF (p_param (i).parameter_name = 'OKI_STATUS+BKD_STATUS')
887 THEN
888 p_attribute_code_num5 := TO_NUMBER (REPLACE (p_param (i).parameter_id
889 , ''''));
890 --p_attribute_code_char5 := p_param(i).parameter_id ;
891 p_attribute_code_char4 := p_param (i).parameter_value;
892 END IF;
893 END LOOP;
894 EXCEPTION
895 WHEN OTHERS
896 THEN
897 bis_collection_utilities.put_line (SQLERRM || '' || SQLCODE);
898 fnd_message.set_name (application => 'FND'
899 , NAME => 'CRM-DEBUG ERROR');
900 fnd_message.set_token (token => 'ROUTINE'
901 , VALUE => 'OKI_DBI_UTIL_PVT.get_drill_across_param_val ');
902 bis_collection_utilities.put_line (fnd_message.get);
903 raise_application_error (-20000
904 , 'Stack Dump Follows =>'
905 , TRUE);
906 END get_drill_across_param_val;
907
908 PROCEDURE process_parameters (
909 p_param IN bis_pmv_page_parameter_tbl
910 , p_view_by OUT NOCOPY VARCHAR2
911 , p_view_by_col_name OUT NOCOPY VARCHAR2
912 , p_comparison_type OUT NOCOPY VARCHAR2
913 , p_xtd OUT NOCOPY VARCHAR2
914 , p_as_of_date OUT NOCOPY DATE
915 , p_prev_as_of_date OUT NOCOPY DATE
916 , p_cur_suffix OUT NOCOPY VARCHAR2
917 , p_nested_pattern OUT NOCOPY NUMBER
918 , p_where_clause OUT NOCOPY VARCHAR2
919 , p_mv OUT NOCOPY VARCHAR2
920 , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
921 , p_period_type OUT NOCOPY VARCHAR2
922 , p_trend IN VARCHAR2
923 , p_func_area IN VARCHAR2 -- Renewals?
924 , p_version IN VARCHAR2
925 , p_role IN VARCHAR2 --
926 , p_mv_set IN VARCHAR2
927 , p_rg_where IN VARCHAR2) -- SRM
928 IS
929 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
930 l_dim_bmap NUMBER;
931 l_rpt_where VARCHAR2(3000);
932 l_class VARCHAR2(3000);
933 l_eni_schema VARCHAR2(20);
934 BEGIN
935
936 g_param := p_param;
937 g_trend := p_trend;
938 g_mv_set := p_mv_set;
939 l_dim_bmap := 0;
940 l_eni_schema := 'ENI';
941
942 split_pseudo_rs_group (p_param);
943
944 init_dim_map (l_dim_map
945 , p_func_area
946 , p_version
947 , p_mv_set);
948 poa_dbi_util_pkg.get_parameter_values (p_param
949 , l_dim_map
950 , p_view_by
951 , p_comparison_type
952 , p_xtd
953 , p_as_of_date
954 , p_prev_as_of_date
955 , p_cur_suffix
956 , p_nested_pattern
957 , l_dim_bmap);
958 g_view_by := p_view_by;
959 /* add in the security dimensions that must always be present in bmap */
960 --Ravi commented
961 -- l_dim_bmap := poa_dbi_util_pkg.bitor (l_dim_bmap
962 -- , g_oper_unit_bmap);
963 /* Change the Suffix */
964 p_cur_suffix := get_cur_suffix (p_cur_suffix);
965 /* Set period type */
966 p_period_type := get_period_type_code (p_xtd);
967
968
969 --DBMS_OUTPUT.put_line ('40: ');
970
971 IF (l_dim_map.EXISTS (p_view_by))
972 THEN
973 p_view_by_col_name := l_dim_map (p_view_by).col_name;
974 END IF;
975
976 p_mv := get_mv (l_dim_bmap
977 , p_func_area
978 , p_version
979 , p_mv_set);
980
981 IF (p_mv_set IN ('SRM_DTL_RPT','SRM_CDTL_RPT')) then
982 p_where_clause := get_dtl_param_where(p_param);
983 ELSE -- summary reports and trends
984 p_where_clause :=
985 oki_dbi_util_pvt.get_where_clauses(l_dim_map, p_trend, p_view_by,p_mv_set)
986 || get_security_where_clauses (l_dim_map
987 , p_func_area
988 , p_version
989 , p_role
990 , p_view_by
991 , p_rg_where
992 , p_param);
993 get_join_info (p_view_by
994 , l_dim_map
995 , p_join_tbl
996 , p_func_area
997 , p_version);
998 END IF;
999
1000 END process_parameters;
1001
1002 -----------------------------------------
1003 FUNCTION get_dtl_param_where( p_param IN bis_pmv_page_parameter_tbl)
1004 RETURN VARCHAR2
1005 IS
1006 l_sg VARCHAR2(3200);
1007 l_org VARCHAR2(3200);
1008 l_prod VARCHAR2(3200);
1009 l_prod_cat VARCHAR2(3200);
1010 l_cust VARCHAR2(3200);
1011 l_reason VARCHAR2(3200);
1012 l_resource VARCHAR2(500);
1013 l_rgroup VARCHAR2(500);
1014 l_param_where VARCHAR2(5000);
1015 l_sep NUMBER;
1016 l_class VARCHAR2(3000);
1017 BEGIN
1018
1019 FOR i IN 1..p_param.COUNT
1020 LOOP
1021
1022 IF(p_param(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS')
1023 THEN l_org := p_param(i).parameter_value;
1024 IF ( l_org IS NULL OR l_org = '' OR l_org = 'All') then
1025 l_org :=' ';
1026 else
1027 l_org := ' AND fact.authoring_org_id in (&ORGANIZATION+FII_OPERATING_UNITS)';
1028 end if;
1029 END IF;
1030
1031 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_PROD_LEAF_CAT')
1032 THEN l_prod_cat := p_param(i).parameter_value;
1033 IF ( l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All') then
1034 l_prod_cat :=' ';
1035 else
1036 l_prod_cat :=' AND fact.service_item_category_id in (&ITEM+ENI_ITEM_PROD_LEAF_CAT)';
1037 end if;
1038 END IF;
1039
1040 IF(p_param(i).parameter_name = 'OKI_STATUS+TERM_REASON')
1041 THEN l_reason := p_param(i).parameter_value;
1042 IF ( l_reason IS NULL OR l_reason = '' OR l_reason = 'All') then
1043 l_reason :=' ';
1044 else
1045 l_reason := ' AND fact.trn_code in (&OKI_STATUS+TERM_REASON)';
1046 end if;
1047 END IF;
1048
1049 IF(p_param(i).parameter_name = 'OKI_STATUS+CNCL_REASON')
1050 THEN l_reason := p_param(i).parameter_value;
1051 IF ( l_reason IS NULL OR l_reason = '' OR l_reason = 'All') then
1052 l_reason :=' ';
1053 else
1054 l_reason := ' AND fact.sts_code in (&OKI_STATUS+CNCL_REASON)';
1055 end if;
1056 END IF;
1057
1058 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM')
1059 THEN l_prod := p_param(i).parameter_id;
1060 IF ( l_prod IS NULL OR l_prod = '' OR l_prod = 'All') then
1061 l_prod :=' ';
1062 else
1063 l_prod := ' AND fact.service_item_org_id in (&ITEM+ENI_ITEM) ';
1064 end if;
1065 END IF;
1066
1067
1068 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
1069 THEN l_sg := p_param(i).parameter_id;
1070 END IF;
1071
1072 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
1073 THEN l_class := p_param(i).parameter_value;
1074 IF ( l_class IS NULL OR l_class = '' OR l_class = 'All') then
1075 l_class :=' ';
1076 else
1077 l_class := 'AND fact.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
1078 end if;
1079 END IF;
1080
1081
1082 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
1083 THEN l_cust := p_param(i).parameter_value;
1084 IF ( l_cust IS NULL OR l_cust = '' OR l_cust = 'All') then
1085 l_cust :=' ';
1086 else
1087 l_cust :=' and fact.customer_party_id in (&CUSTOMER+FII_CUSTOMERS)';
1088 end if;
1089 END IF;
1090
1091 END LOOP;
1092
1093 IF (oki_dbi_util_pvt.g_resource_id IS NULL) THEN
1094 l_resource := ' ';
1095 l_rgroup := ' AND fact.resource_group_id = rs_grp.rg_id
1096 AND rs_grp.prg_id = &OKI_RG ';
1097 ELSE
1098 l_resource := 'AND fact.resource_id = &OKI_RS ' ;
1099 l_rgroup := ' AND fact.resource_group_id = &OKI_RG ';
1100 END IF;
1101
1102
1103 l_param_where := l_org || l_prod_cat || l_prod || l_rgroup || l_resource || l_class || l_cust || l_reason;
1104
1105 return l_param_where;
1106
1107 END get_dtl_param_where;
1108
1109
1110 /* -----------------------------------------------------------------------------
1111 get_prodcat_where: Get where clause for product category
1112 ----------------------------------------------------------------------------- */
1113
1114 FUNCTION get_prodcat_where
1115 RETURN VARCHAR2
1116 IS
1117 BEGIN
1118 /*
1119 IF( (g_trend = 'N') AND
1120 ( g_view_by = g_time_mth_dim
1121 OR g_view_by = g_time_qtr_dim
1122 OR g_view_by = g_time_year_dim) )
1123 THEN
1124 -- This is a report which goes to oki_20_J_mv
1125 IF (get_param_id (g_param
1126 , g_prod_ctgy_dim) IS NOT NULL)
1127 THEN
1128
1129 RETURN
1130 ' AND fact.service_item_category_id IN (
1131 SELECT d.child_id
1132 FROM eni_denorm_hierarchies d
1133 WHERE d.parent_id = &ITEM+ENI_ITEM_VBH_CAT
1134 AND item_assgn_flag = ''Y''
1135 AND dbi_flag = ''Y''
1136 ) ';
1137 END IF;
1138 END IF;
1139 */
1140
1141 IF (get_param_id (g_param
1142 , g_prod_ctgy_dim) IS NOT NULL)
1143 THEN
1144 -- RETURN ' and fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
1145 --RAVI FOR DBI 70
1146 RETURN ' and fact.service_item_category_id = &ITEM+ENI_ITEM_VBH_CAT ';
1147 /* ELSIF(g_view_by = g_prod_ctgy_dim) THEN
1148 -- prod_cat = All so need to get all top nodes
1149 RETURN ' and fact.parent_id IN (
1150 SELECT d.parent_id
1151 FROM eni_denorm_hierarchies d
1152 WHERE d.top_node_flag = ''Y''
1153 )'; */
1154 END IF;
1155
1156 RETURN '';
1157
1158 END get_prodcat_where;
1159 /* -----------------------------------------------------------------------------
1160 get_rg_sec_where: Get where clause for resource group
1161 ----------------------------------------------------------------------------- */
1162 FUNCTION get_rg_sec_where (
1163 p_rg_value IN VARCHAR2
1164 , p_rg_col IN VARCHAR2
1165 , p_view_by IN VARCHAR2)
1166 RETURN VARCHAR2
1167 IS
1168 l_sec_where_clause VARCHAR2 (1000) := NULL;
1169 BEGIN
1170 -- brrao modified
1171 -- for semi-detail VIEW_BY REPORTS
1172 -- used for Past Due percent, Booking to renewal ratios, Period renewal rates reports
1173 IF (g_mv_set = 'SRM_DET')
1174 THEN
1175 IF (g_resource_id IS NULL)
1176 THEN
1177 RETURN ' fact.rg_id = &OKI_RG and fact.umarker <> ''TOP GROUP'' ';
1178 ELSE
1179 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1180 END IF;
1181 -- for detail contract reports - (non-view-by)
1182 -- Used for Late renewals Aging and Cancellation Detail reports aswell.
1183 ELSIF (g_mv_set IN ( 'SRM_RPT','SRM_CUST_RPT', 'SRM_TBK_RPT','SRM_LATE_BKING') ) --Added by Arun SRM_CUST_RPT for YTD customer reports
1184 THEN
1185 IF (g_resource_id IS NULL)
1186 THEN
1187 IF g_mv_set='SRM_TBK_RPT' THEN
1188 RETURN ' fact.resource_group_id IN ( SELECT sgr2.rg_id FROM oki_rs_group_mv sgr2 WHERE sgr2.prg_id = &OKI_RG ) ';
1189 ELSE
1190 RETURN ' fact.rg_id IN ( SELECT sgr2.rg_id FROM oki_rs_group_mv sgr2 WHERE sgr2.prg_id = &OKI_RG ) ';
1191 END IF;
1192 ELSE
1193 IF g_mv_set='SRM_TBK_RPT' THEN
1194 RETURN ' fact.resource_group_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1195 ELSE
1196 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1197 END IF;
1198 END IF;
1199 -- for trends and table portlets
1200 -- Also used for Late renewal and Cancellation Summary reports
1201 ELSIF( g_mv_set IN ('SRM','SRM_BLG','SRM_OPN','SRM_BLG_CUST','SRM_BAL') ) --Added BY Arun SRM_BLG_CUST for ITD customer reports
1202
1203 THEN
1204 IF(g_trend = 'Y')
1205 THEN
1206 IF (g_resource_id IS NULL)
1207 THEN
1208 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = -999 ';
1209 ELSE
1210 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1211 END IF;
1212 ELSIF p_view_by = g_sales_grp_dim
1213 THEN
1214 IF (g_resource_id IS NULL)
1215 THEN
1216 /*
1217 RETURN ' ((fact.rg_id in (select rg_id from oki_rs_group_mv where prg_id = &OKI_RG
1218 and denorm_level = 1) and fact.resource_id = -999)
1219 or (fact.rg_id = &OKI_RG and fact.resource_id <> -999)) ';
1220 */
1221 RETURN ' ( fact.prg_id = &OKI_RG
1222 and fact.umarker <> ''TOP GROUP'' ) ';
1223 ELSE
1224 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1225 END IF;
1226 ELSE
1227 IF (g_resource_id IS NULL)
1228 THEN
1229 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = -999 ';
1230 ELSE
1231 RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1232 END IF;
1233 END IF;
1234 END IF;
1235
1236
1237 IF (g_mv_set IN ( 'SRM_CR_71','SRM_EC_71','SRM_CN_71','SRM_SG_71','SRM_ST_71','SRM_TM_71','SRM_EN_71' )) --Added by blindaue for 71 reports
1238 THEN
1239 IF (g_resource_id IS NULL)
1240 THEN RETURN ' ( fact.prg_id = &OKI_RG and fact.umarker <> ''TOP GROUP'' ) ';
1241 ELSE RETURN ' fact.rg_id = &OKI_RG AND fact.resource_id = &OKI_RS ';
1242 END IF;
1243 END IF;
1244
1245
1246 RETURN l_sec_where_clause;
1247 END get_rg_sec_where;
1248
1249 /* -----------------------------------------------------------------------------
1250 get_security_where_clauses: Where clauses for
1251 (1) Operating Unit
1252 (2) Resource Group
1253 ----------------------------------------------------------------------------- */
1254 FUNCTION get_security_where_clauses (
1255 p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map
1256 , p_func_area IN VARCHAR2
1257 , p_version IN VARCHAR2
1258 , p_role IN VARCHAR2
1259 , p_view_by IN VARCHAR2
1260 , p_rg_where IN VARCHAR2
1261 , p_param IN bis_pmv_page_parameter_tbl)
1262 RETURN VARCHAR2
1263 IS
1264 l_sec_where_clause VARCHAR2 (1000);
1265
1266 l_rg_where VARCHAR2 (1000);
1267 l_prodcat_where VARCHAR2 (1000);
1268
1269 l_ou_where VARCHAR2 (1000);
1270 l_org_col VARCHAR2 (30);
1271 l_service_cat_where VARCHAR2 (1000);
1272 BEGIN
1273 l_sec_where_clause := '';
1274 /* l_org_col := 'authoring_org_id';
1275 l_ou_where := poa_dbi_util_pkg.get_ou_sec_where (p_dim_map ('ORGANIZATION+FII_OPERATING_UNITS').VALUE
1276 , p_dim_map ('ORGANIZATION+FII_OPERATING_UNITS').col_name);
1277
1278 IF (l_ou_where IS NOT NULL)
1279 THEN
1280 l_sec_where_clause := l_sec_where_clause || ' and ' || l_ou_where;
1281 END IF;
1282 */
1283
1284 IF p_rg_where = 'Y'
1285 THEN
1286 l_rg_where := get_rg_sec_where (p_dim_map (g_sales_grp_dim).VALUE
1287 , p_dim_map (g_sales_grp_dim).col_name
1288 , p_view_by);
1289 IF (l_rg_where IS NOT NULL)
1290 THEN
1291 l_sec_where_clause := l_sec_where_clause || ' and ' || l_rg_where;
1292 END IF;
1293 END IF;
1294
1295 /*
1296 l_prodcat_where := get_prodcat_where();
1297 IF (l_prodcat_where IS NOT NULL)
1298 THEN
1299 l_sec_where_clause := l_sec_where_clause || l_prodcat_where;
1300 END IF;
1301 */
1302 RETURN l_sec_where_clause;
1303 END get_security_where_clauses;
1304
1305 /* -----------------------------------------------------------------------------
1306 get_mv:
1307 ----------------------------------------------------------------------------- */
1308 FUNCTION get_mv (
1309 p_dim_bmap IN NUMBER
1310 , p_func_area IN VARCHAR2
1311 , p_version IN VARCHAR2
1312 , p_mv_set IN VARCHAR2)
1313 RETURN VARCHAR2
1314 IS
1315 l_index NUMBER := 1;
1316 l_cost NUMBER;
1317 l_mv_bmap_tbl oki_dbi_mv_bmap_tbl;
1318 BEGIN
1319 populate_mv_bmap (l_mv_bmap_tbl
1320 , p_mv_set);
1321 l_cost := l_mv_bmap_tbl (1).mv_bmap;
1322
1323 FOR i IN l_mv_bmap_tbl.FIRST .. l_mv_bmap_tbl.LAST
1324 LOOP
1325 IF (BITAND (l_mv_bmap_tbl (i).mv_bmap
1326 , p_dim_bmap) = p_dim_bmap)
1327 THEN
1328 IF (l_mv_bmap_tbl (i).mv_bmap < l_cost)
1329 THEN
1330 l_cost := l_mv_bmap_tbl (i).mv_bmap;
1331 l_index := i;
1332 END IF;
1333 END IF;
1334 END LOOP;
1335
1336 RETURN l_mv_bmap_tbl (l_index).mv_name;
1337 END get_mv;
1338
1339 /* -----------------------------------------------------------------------------
1340 init_dim_map: Initialize the dimension mapping
1341 ----------------------------------------------------------------------------- */
1342 PROCEDURE init_dim_map (
1343 p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
1344 , p_func_area IN VARCHAR2
1345 , p_version IN VARCHAR2
1346 , p_mv_set IN VARCHAR2)
1347 IS
1348 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
1349 BEGIN
1350 -- Operating Unit
1351 l_dim_rec.generate_where_clause := 'Y';
1352 l_dim_rec.col_name := get_col_name (dim_name => g_oper_unit_dim
1353 , p_func_area => p_func_area
1354 , p_version => p_version);
1355 l_dim_rec.view_by_table := get_table (dim_name => g_oper_unit_dim
1356 , p_func_area => p_func_area
1357 , p_version => p_version);
1358 l_dim_rec.bmap := g_oper_unit_bmap;
1359 p_dim_map (g_oper_unit_dim) := l_dim_rec;
1360 -- Sales Group
1361 l_dim_rec.generate_where_clause := 'N';
1362 l_dim_rec.col_name := get_col_name (dim_name => g_sales_grp_dim
1363 , p_func_area => p_func_area
1364 , p_version => p_version);
1365 l_dim_rec.view_by_table := get_table (dim_name => g_sales_grp_dim
1366 , p_func_area => p_func_area
1367 , p_version => p_version);
1368 l_dim_rec.bmap := g_sales_grp_bmap;
1369 p_dim_map (g_sales_grp_dim) := l_dim_rec;
1370
1371 -- Service Item
1372 l_dim_rec.generate_where_clause := 'Y';
1373 l_dim_rec.col_name := get_col_name (dim_name => g_sitem_dim
1374 , p_func_area => p_func_area
1375 , p_version => p_version);
1376 l_dim_rec.view_by_table := get_table (dim_name => g_sitem_dim
1377 , p_func_area => p_func_area
1378 , p_version => p_version);
1379 l_dim_rec.bmap := g_sitem_bmap;
1380 p_dim_map (g_sitem_dim) := l_dim_rec;
1381
1382 -- Cust classification
1383 l_dim_rec.generate_where_clause := 'Y';
1384 l_dim_rec.col_name := get_col_name (dim_name => g_cust_class_dim
1385 , p_func_area => p_func_area
1386 , p_version => p_version);
1387 l_dim_rec.view_by_table := get_table (dim_name => g_cust_class_dim
1388 , p_func_area => p_func_area
1389 , p_version => p_version);
1390 l_dim_rec.bmap := g_cust_class_bmap;
1391 p_dim_map (g_cust_class_dim) := l_dim_rec;
1392
1393 -- Product Category
1394 l_dim_rec.generate_where_clause := 'Y';
1395 l_dim_rec.col_name := get_col_name (dim_name => g_prod_ctgy_dim
1396 , p_func_area => p_func_area
1397 , p_version => p_version);
1398 l_dim_rec.view_by_table := get_table (dim_name => g_prod_ctgy_dim
1399 , p_func_area => p_func_area
1400 , p_version => p_version);
1401 l_dim_rec.bmap := g_prd_ctgy_bmap;
1402 p_dim_map (g_prod_ctgy_dim) := l_dim_rec;
1403 -- Cancellation Reason
1404 l_dim_rec.generate_where_clause := 'Y';
1405 l_dim_rec.col_name := get_col_name (dim_name => g_cncl_reason_dim
1406 , p_func_area => p_func_area
1407 , p_version => p_version);
1408 l_dim_rec.view_by_table := get_table (dim_name => g_cncl_reason_dim
1409 , p_func_area => p_func_area
1410 , p_version => p_version);
1411 l_dim_rec.bmap := g_cncl_reason_bmap;
1412 p_dim_map (g_cncl_reason_dim) := l_dim_rec;
1413
1414 --Customer Added by Arun for 7.0
1415 l_dim_rec.generate_where_clause := 'Y';
1416 l_dim_rec.col_name := get_col_name (dim_name => g_customer_dim
1417 , p_func_area => p_func_area
1418 , p_version => p_version);
1419 l_dim_rec.view_by_table :='FII_CUSTOMERS_V'; -- as there is no view by customer
1420 l_dim_rec.bmap := g_customer_bmap;
1421 p_dim_map (g_customer_dim) := l_dim_rec;
1422
1423 -- Terminations Reason
1424 l_dim_rec.generate_where_clause := 'Y';
1425 l_dim_rec.col_name := get_col_name (dim_name => g_trm_reason_dim
1426 , p_func_area => p_func_area
1427 , p_version => p_version);
1428 l_dim_rec.view_by_table := get_table (dim_name => g_trm_reason_dim
1429 , p_func_area => p_func_area
1430 , p_version => p_version);
1431 l_dim_rec.bmap := g_trm_reason_bmap;
1432 p_dim_map (g_trm_reason_dim) := l_dim_rec;
1433
1434
1435 END init_dim_map;
1436
1437 /* -----------------------------------------------------------------------------
1438 get_col_name: Returns the column name in the MV that is associated with the
1439 dimension.
1440 ----------------------------------------------------------------------------- */
1441 FUNCTION get_col_name (
1442 dim_name VARCHAR2
1443 , p_func_area IN VARCHAR2
1444 , p_version IN VARCHAR2)
1445 RETURN VARCHAR2
1446 IS
1447 l_col_name VARCHAR2 (100);
1448 BEGIN
1449 l_col_name :=
1450 (CASE dim_name
1451 WHEN g_sales_grp_dim
1452 THEN 'rg_id, resource_id '
1453 WHEN g_oper_unit_dim
1454 THEN 'authoring_org_id'
1455 WHEN g_sitem_dim
1456 -- Commented by Pushkala THEN 'service_itemorg_id'
1457 THEN 'service_item_org_id'
1458 WHEN g_prod_ctgy_dim
1459 THEN 'service_item_category_id '
1460 WHEN g_sales_rep_dim
1461 THEN 'resource_id'
1462 WHEN g_cncl_reason_dim
1463 THEN 'sts_code'
1464 WHEN g_customer_dim
1465 THEN 'customer_party_id'
1466 WHEN g_trm_reason_dim
1467 THEN 'trn_code'
1468 WHEN g_cust_class_dim
1469 THEN 'class_code'
1470 ELSE ''
1471 END);
1472 /* Commented By RAVI
1473 IF ( (dim_name = g_prod_ctgy_dim)
1474 AND
1475 (get_param_id(g_param,g_prod_ctgy_dim) IS NULL ) )
1476 THEN
1477 l_col_name := 'service_item_category_id';
1478
1479 END IF;
1480 */
1481
1482 RETURN l_col_name;
1483 END;
1484
1485 /* -----------------------------------------------------------------------------
1486 get_security_where_clauses: Returns the name of the object to join to to which
1487 the MV is joined to ???????????????????????
1488 ----------------------------------------------------------------------------- */
1489 FUNCTION get_table (
1490 dim_name VARCHAR2
1491 , p_func_area IN VARCHAR2
1492 , p_version IN VARCHAR2)
1493 RETURN VARCHAR2
1494 IS
1495 l_table VARCHAR2 (4000);
1496 BEGIN
1497 l_table :=
1498 (CASE dim_name
1499 WHEN g_oper_unit_dim
1500 THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
1501
1502 WHEN g_sales_grp_dim
1503 -- THEN '(SELECT rg.group_id as id , rg.group_name value from JTF_RS_GROUPS_VL rg)'
1504 -- THEN '(SELECT rg.group_id as id , rg.group_name value from JTF_RS_GROUPS_TL rg where language = userenv(''LANG''))'
1505
1506 THEN 'OKI_DBI_SRM_GRP_RES_V'
1507 WHEN g_sitem_dim
1508 THEN 'ENI_ITEM_V'
1509 WHEN g_prod_ctgy_dim
1510 THEN 'OKI_ENI_ITEM_VBH_NODES_V'
1511 WHEN g_sales_rep_dim
1512 THEN 'OKI_DIM_SALESFORCE'
1513 WHEN g_cncl_reason_dim
1514 THEN 'OKI_CANCEL_STATUSES_V'
1515 WHEN g_trm_reason_dim
1516 THEN 'OKI_TERM_REASONS_V'
1517 WHEN g_cust_class_dim
1518 THEN 'FII_PARTNER_MKT_CLASS_V'
1519 ELSE ''
1520 END);
1521 RETURN l_table;
1522 END get_table;
1523
1524 PROCEDURE get_join_info (
1525 p_view_by IN VARCHAR2
1526 , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
1527 , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
1528 , p_func_area IN VARCHAR2
1529 , p_version IN VARCHAR2)
1530 IS
1531 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
1532 BEGIN
1533 x_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1534
1535 IF (NOT p_dim_map.EXISTS (p_view_by))
1536 THEN
1537 RETURN;
1538 END IF;
1539
1540 --DBMS_OUTPUT.put_line ('Inside join table');
1541 l_join_rec.table_name := p_dim_map (p_view_by).view_by_table;
1542 l_join_rec.table_alias := 'v';
1543 l_join_rec.fact_column := p_dim_map (p_view_by).col_name;
1544
1545 IF (p_view_by = 'OKI_RESOURCE+SALESREP')
1546 THEN
1547 l_join_rec.column_name := 'id(+)';
1548 ELSIF (p_view_by = g_sales_grp_dim)
1549 THEN
1550 -- l_join_rec.column_name := 'id(+)';
1551 l_join_rec.table_name := 'jtf_rs_groups_vl';
1552 l_join_rec.table_alias := 'g';
1553 l_join_rec.fact_column := 'rg_id';
1554 l_join_rec.column_name := 'group_id';
1555 x_join_tbl.EXTEND;
1556 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1557 l_join_rec.table_name := 'jtf_rs_resource_extns_vl';
1558 l_join_rec.table_alias := 'r';
1559 l_join_rec.fact_column := 'resource_id';
1560 l_join_rec.column_name := 'resource_id(+)';
1561 ELSE
1562 l_join_rec.column_name := 'id';
1563 END IF;
1564
1565 x_join_tbl.EXTEND;
1566 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1567 /*
1568 IF (p_view_by = 'ITEM+POA_ITEMS')
1569 THEN
1570 l_join_rec.table_name := 'mtl_units_of_measure_vl';
1571 l_join_rec.table_alias := 'v2';
1572 l_join_rec.fact_column := 'base_uom';
1573 l_join_rec.column_name := 'unit_of_measure';
1574 x_join_tbl.EXTEND;
1575 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1576 END IF;
1577 */
1578 END get_join_info;
1579
1580 /* Not currently used by OKI */
1581 /* -----------------------------------------------------------------------------
1582 get_join_info:
1583 ----------------------------------------------------------------------------- */
1584 /* PROCEDURE get_join_info (
1585 p_view_by IN VARCHAR2
1586 , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
1587 , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
1588 , p_func_area IN VARCHAR2
1589 , p_version IN VARCHAR2
1590 , p_rpt_type IN VARCHAR2 := 'SUMMARY')
1591 IS
1592 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
1593 BEGIN
1594 x_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
1595
1596 --DBMS_OUTPUT.put_line ('p_rpt_type: [' || p_rpt_type || ']');
1597
1598 IF (NOT p_dim_map.EXISTS (p_view_by))
1599 THEN
1600 IF (p_rpt_type = 'K_DTL')
1601 AND (p_version = '6.0')
1602 THEN
1603 l_join_rec.table_name := 'oki_scm_000_mv';
1604 l_join_rec.column_name := 'chr_id';
1605 l_join_rec.table_alias := 'k';
1606 l_join_rec.fact_column := 'chr_id';
1607 x_join_tbl.EXTEND;
1608 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1609 l_join_rec.table_name := 'fii_customers_v';
1610 l_join_rec.column_name := 'id';
1611 l_join_rec.table_alias := 'cust';
1612 l_join_rec.fact_column := 'customer_party_id';
1613 x_join_tbl.EXTEND;
1614 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1615 l_join_rec.table_name := 'jtf_rs_resource_extns_tl';
1616 l_join_rec.column_name := 'resource_id';
1617 l_join_rec.table_alias := 'rsex';
1618 l_join_rec.dim_outer_join := 'Y';
1619 l_join_rec.fact_column := 'resource_id';
1620 x_join_tbl.EXTEND;
1621 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1622 --DBMS_OUTPUT.put_line ('Join:');
1623 END IF;
1624 ELSE
1625 --DBMS_OUTPUT.put_line ('Else:');
1626 l_join_rec.table_name := p_dim_map (p_view_by).view_by_table;
1627 l_join_rec.table_alias := 'v';
1628 l_join_rec.fact_column := p_dim_map (p_view_by).col_name;
1629
1630 IF (p_view_by = 'ITEM+POA_COMMODITIES')
1631 THEN
1632 l_join_rec.additional_where_clause := 'language=USERENV(''LANG'')';
1633 l_join_rec.column_name := 'commodity_id';
1634 ELSE
1635 l_join_rec.column_name := 'id';
1636 END IF;
1637
1638 x_join_tbl.EXTEND;
1639 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1640
1641 IF (p_view_by = 'ITEM+POA_ITEMS')
1642 THEN
1643 l_join_rec.table_name := 'mtl_units_of_measure_vl';
1644 l_join_rec.table_alias := 'v2';
1645 l_join_rec.fact_column := 'base_uom';
1646 l_join_rec.column_name := 'unit_of_measure';
1647 x_join_tbl.EXTEND;
1648 x_join_tbl (x_join_tbl.COUNT) := l_join_rec;
1649 END IF;
1650 END IF;
1651 END get_join_info;
1652 */
1653 PROCEDURE populate_mv_bmap (
1654 p_mv_bmap_tbl OUT NOCOPY oki_dbi_mv_bmap_tbl
1655 , p_mv_set IN VARCHAR2 )
1656 IS
1657 l_rec oki_dbi_mv_bmap_rec;
1658 BEGIN
1659 p_mv_bmap_tbl := oki_dbi_mv_bmap_tbl ();
1660
1661 --Added by Arun for DBI7.0 SRM Customer reports
1662 /* Renewals by Customer,Period Renewals by Customers,Expected Bookings by Customer
1663 and Renewal Cancellation s by customer reports should use this MV-Set
1664 Also commented out all the ENI views which were used for the product category hierarchy and instead added
1665 the product category dimension to the respective MV's
1666 */
1667
1668 IF (p_mv_set = 'SRM_BAL')
1669 THEN
1670 -- Pushkala : 71 MVs for Balance
1671 p_mv_bmap_tbl.EXTEND (2);
1672 l_rec.mv_name := 'OKI_SRM_028_V';
1673 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap+g_cust_class_bmap;
1674 p_mv_bmap_tbl (1) := l_rec;
1675 l_rec.mv_name := 'OKI_SRM_038_MV';
1676 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_cust_class_bmap+g_oper_unit_bmap;
1677 p_mv_bmap_tbl (2) := l_rec;
1678 ELSIF (p_mv_set = 'SRM_BLG')
1679 THEN
1680 -- Pushkala : 71 MVs for Backlog
1681 p_mv_bmap_tbl.EXTEND (2);
1682 l_rec.mv_name := 'OKI_SRM_029_V';
1683 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_sitem_bmap+g_prd_ctgy_bmap;
1684 p_mv_bmap_tbl (1) := l_rec;
1685 l_rec.mv_name := 'OKI_SRM_039_MV';
1686 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap;
1687 p_mv_bmap_tbl (2) := l_rec;
1688
1689 ELSIF (p_mv_set = 'SRM_OPN')
1690 THEN
1691 -- Pushkala : 71 MVs - Open - Used in Backlog report
1692 p_mv_bmap_tbl.EXTEND (2);
1693 l_rec.mv_name := 'OKI_SRM_030_V';
1694 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_sitem_bmap+g_prd_ctgy_bmap;
1695 p_mv_bmap_tbl (1) := l_rec;
1696 l_rec.mv_name := 'OKI_SRM_040_MV';
1697 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap;
1698 p_mv_bmap_tbl (2) := l_rec;
1699 ELSIF (p_mv_set = 'SRM_LATE_BKING')
1700 THEN
1701 --Pushkala : 71 MV for Late Bookings
1702 p_mv_bmap_tbl.EXTEND (1);
1703 l_rec.mv_name := 'OKI_SRM_003_MV';
1704 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_sitem_bmap+g_prd_ctgy_bmap;
1705 p_mv_bmap_tbl (1) := l_rec;
1706 ELSIF (p_mv_set IN ('SRM_DTL_RPT','SRM_CDTL_RPT'))
1707 THEN
1708 --Pushkala : 71 MVs for Detail reports
1709 p_mv_bmap_tbl.EXTEND (1);
1710 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_sitem_bmap+g_prd_ctgy_bmap;
1711 IF (oki_dbi_util_pvt.g_resource_id IS NULL) then
1712 l_rec.mv_name := ' OKI_RS_GROUP_MV rs_grp, OKI_SRM_004_MV ';
1713 ELSE
1714 l_rec.mv_name := ' OKI_SRM_004_MV ';
1715 END IF;
1716 p_mv_bmap_tbl (1) := l_rec;
1717 ELSIF (p_mv_set = 'SRM_TBK_RPT')
1718 THEN
1719 -- Pushkala : 71 MV for Top Bookings
1720 p_mv_bmap_tbl.EXTEND (1);
1721 l_rec.mv_name := 'OKI_SRM_004_MV';
1722 l_rec.mv_bmap := g_oper_unit_bmap+g_sales_grp_bmap+g_customer_bmap;
1723 p_mv_bmap_tbl (1) := l_rec;
1724 ELSIF (p_mv_set = 'SRM_SG_71')
1725 THEN
1726 -- Pushkala : 71 Signed Date mvs
1727 p_mv_bmap_tbl.EXTEND (2);
1728 l_rec.mv_name := 'OKI_SRM_024_V';
1729 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap+g_cust_class_bmap;
1730 p_mv_bmap_tbl (1) := l_rec;
1731 l_rec.mv_name := 'OKI_SRM_034_MV';
1732 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_cust_class_bmap+g_oper_unit_bmap;
1733 p_mv_bmap_tbl (2) := l_rec;
1734 ELSIF (p_mv_set = 'SRM_ST_71')
1735 THEN
1736 -- Pushkala : 71 Start Date mvs
1737 p_mv_bmap_tbl.EXTEND (2);
1738 l_rec.mv_name := 'OKI_SRM_021_V';
1739 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap+g_cust_class_bmap;
1740 p_mv_bmap_tbl (1) := l_rec;
1741 l_rec.mv_name := 'OKI_SRM_031_MV';
1742 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_cust_class_bmap+g_oper_unit_bmap;
1743 p_mv_bmap_tbl (2) := l_rec;
1744 ELSIF (p_mv_set = 'SRM_CN_71')
1745 THEN
1746 -- Pushkala : 71 Cancelled Date mvs
1747 p_mv_bmap_tbl.EXTEND (2);
1748 l_rec.mv_name := 'OKI_SRM_027_V';
1749 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_cncl_reason_bmap+g_sitem_bmap;
1750 p_mv_bmap_tbl (1) := l_rec;
1751 l_rec.mv_name := 'OKI_SRM_037_MV';
1752 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_cncl_reason_bmap;
1753 p_mv_bmap_tbl (2) := l_rec;
1754 ELSIF (p_mv_set = 'SRM_EC_71')
1755 THEN
1756 -- Pushkala : 71 Expected Close Date mvs
1757 p_mv_bmap_tbl.EXTEND (2);
1758 l_rec.mv_name := 'OKI_SRM_026_V';
1759 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap;
1760 p_mv_bmap_tbl (1) := l_rec;
1761 l_rec.mv_name := 'OKI_SRM_036_MV';
1762 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap;
1763 p_mv_bmap_tbl (2) := l_rec;
1764 ELSIF (p_mv_set = 'SRM_TM_71')
1765 THEN
1766 -- Pushkala : 71 Termination Date mvs
1767 p_mv_bmap_tbl.EXTEND (2);
1768 l_rec.mv_name := 'OKI_SRM_025_V';
1769 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_trm_reason_bmap+g_sitem_bmap+g_cust_class_bmap;
1770 p_mv_bmap_tbl (1) := l_rec;
1771 l_rec.mv_name := 'OKI_SRM_035_MV';
1772 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_cust_class_bmap+g_oper_unit_bmap+g_trm_reason_bmap;
1773 p_mv_bmap_tbl (2) := l_rec;
1774 ELSIF (p_mv_set = 'SRM_CR_71')
1775 THEN
1776 -- Pushkala : 71 Creation Date mvs
1777 p_mv_bmap_tbl.EXTEND (2);
1778 l_rec.mv_name := 'OKI_SRM_022_V';
1779 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap;
1780 p_mv_bmap_tbl (1) := l_rec;
1781 l_rec.mv_name := 'OKI_SRM_032_MV';
1782 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap;
1783 p_mv_bmap_tbl (2) := l_rec;
1784 ELSIF (p_mv_set = 'SRM_EN_71')
1785 THEN
1786 -- Pushkala : 71 End Date mvs
1787 p_mv_bmap_tbl.EXTEND (2);
1788 l_rec.mv_name := 'OKI_SRM_023_V';
1789 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap+g_cust_class_bmap;
1790 p_mv_bmap_tbl (1) := l_rec;
1791 l_rec.mv_name := 'OKI_SRM_033_MV';
1792 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_cust_class_bmap+g_oper_unit_bmap;
1793 p_mv_bmap_tbl (2) := l_rec;
1794 ELSIF (p_mv_set = 'SRM_RPT')
1795 THEN
1796 --Pushkala : Not used by 71 reports
1797 p_mv_bmap_tbl.EXTEND (1);
1798 l_rec.mv_name := 'oki_20_j_mv';
1799 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_sitem_bmap+g_prd_ctgy_bmap;
1800 p_mv_bmap_tbl (1) := l_rec;
1801 ELSIF (p_mv_set = 'SRM_CUST_RPT')
1802 THEN
1803 -- Pushkala : Not used by 71 reports
1804 p_mv_bmap_tbl.EXTEND (1);
1805 l_rec.mv_name := 'oki_srm_30_mv';
1806 l_rec.mv_bmap := g_oper_unit_bmap+g_sales_grp_bmap+g_sitem_bmap+g_prd_ctgy_bmap+g_cncl_reason_bmap+g_trm_reason_bmap+g_customer_bmap;
1807
1808 p_mv_bmap_tbl (1) := l_rec;
1809 ELSIF (p_mv_set = 'SRM_BLG_CUST') --Past Due Renewals by customer should use this MV-set
1810 THEN
1811 --Pushkala : Not used by 71 reports
1812 p_mv_bmap_tbl.EXTEND (1);
1813 l_rec.mv_name := 'oki_itd_blg_45_mv';
1814 l_rec.mv_bmap := g_oper_unit_bmap + g_sales_grp_bmap + g_prd_ctgy_bmap+g_sitem_bmap+g_customer_bmap;
1815 p_mv_bmap_tbl (1) := l_rec;
1816 ELSIF (p_mv_set = 'SRM')
1817 THEN
1818 --Pushkala : Not used by 71 reports
1819 p_mv_bmap_tbl.EXTEND (5);
1820 l_rec.mv_name := 'oki_srm_45_mv';
1821 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_oper_unit_bmap+g_sitem_bmap+g_cncl_reason_bmap+g_trm_reason_bmap;
1822 p_mv_bmap_tbl (1) := l_rec;
1823 l_rec.mv_name := '(SELECT * FROM oki_srm_or_50_mv where ogrp_id=1 and lmarker= 1)';
1824 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap;
1825 p_mv_bmap_tbl (2) := l_rec;
1826 l_rec.mv_name := '(SELECT * FROM oki_srm_or_50_mv where ogrp_id=0 and lmarker= 1)';
1827 l_rec.mv_bmap := g_sales_grp_bmap+g_prd_ctgy_bmap+g_sitem_bmap;
1828 p_mv_bmap_tbl (3) := l_rec;
1829 l_rec.mv_name := '(SELECT * FROM oki_srm_or_50_mv where ogrp_id=0 and lmarker= 2)';
1830 l_rec.mv_bmap := g_sales_grp_bmap+g_oper_unit_bmap;
1831 p_mv_bmap_tbl (4) := l_rec;
1832 l_rec.mv_name := '(SELECT * FROM oki_srm_or_50_mv where ogrp_id=3 and lmarker= 1)';
1833 l_rec.mv_bmap := g_sales_grp_bmap;
1834 p_mv_bmap_tbl (5) := l_rec;
1835 END IF;
1836 EXCEPTION
1837 WHEN OTHERS
1838 THEN
1839 poa_log.debug_line ('refresh_manual_dist mvs ' || SQLERRM || SQLCODE || SYSDATE);
1840 RAISE;
1841 END populate_mv_bmap;
1842
1843 FUNCTION get_viewby_select_clause (
1844 p_viewby IN VARCHAR2
1845 , p_func_area IN VARCHAR2
1846 , p_version IN VARCHAR2)
1847 RETURN VARCHAR2
1848 IS
1849 l_directs VARCHAR2 (100);
1850 BEGIN
1851 IF (p_viewby = g_sales_grp_dim)
1852 THEN
1853 RETURN 'SELECT decode(oset.resource_id,-999,to_char(oset.rg_id),oset.resource_id||''.''||oset.rg_id) VIEWBYID ,decode(oset.resource_id,-999,g.group_name, decode(oset.resource_id, -1, &UNASSIGNED,r.resource_name)) VIEWBY ';
1854 ELSE
1855 RETURN 'select v.value VIEWBY
1856 ,v.id VIEWBYID ';
1857 END IF;
1858 END;
1859
1860 /* -----------------------------------------------------------------------------
1861 get_cur_suffix: OKI does not suffix the functional currency with "b".
1862 OKI uses "f" for the functional currency suffix.
1863 ----------------------------------------------------------------------------- */
1864 FUNCTION get_cur_suffix (
1865 p_cur_suffix IN VARCHAR2)
1866 RETURN VARCHAR2
1867 IS
1868 BEGIN
1869 IF p_cur_suffix = 'b'
1870 THEN
1871 RETURN 'f';
1872 ELSE
1873 RETURN p_cur_suffix;
1874 END IF;
1875 END get_cur_suffix;
1876
1877 /* -----------------------------------------------------------------------------
1878 get_period_type_code:
1879 ----------------------------------------------------------------------------- */
1880 FUNCTION get_period_type_code (
1881 p_xtd IN VARCHAR2)
1882 RETURN VARCHAR2
1883 IS
1884 l_period_type_code VARCHAR2 (1);
1885 BEGIN
1886 IF (p_xtd = 'MTD')
1887 THEN
1888 l_period_type_code := 'p';
1889 ELSIF (p_xtd = 'QTD')
1890 THEN
1891 l_period_type_code := 'q';
1892 ELSE
1893 l_period_type_code := 'y';
1894 END IF;
1895
1896 RETURN l_period_type_code;
1897 END get_period_type_code;
1898
1899 FUNCTION get_where_clauses (
1900 p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map
1901 , p_trend IN VARCHAR2
1902 , p_view_by IN VARCHAR2
1903 , p_mv_set IN VARCHAR2)
1904 RETURN VARCHAR2
1905 IS
1906 l_where_clause VARCHAR2 (4000);
1907 i VARCHAR2 (100);
1908 l_ou_flag NUMBER;
1909 l_cc_flag NUMBER;
1910 l_pc_flag NUMBER;
1911 l_45 BOOLEAN;
1912 cc_flag VARCHAR2(200);
1913 BEGIN
1914 l_45 := FALSE;
1915 l_ou_flag := 1;
1916 l_cc_flag := 0;
1917 l_pc_flag := 1;
1918
1919 i := p_dim_map.FIRST; -- get subscript of first element
1920 -- DBMS_OUTPUT.put_line ('i : [' || i || ']');
1921
1922 WHILE i IS NOT NULL
1923 LOOP
1924 --insert into debug values ('test 0 - '||i,sysdate);commit;
1925 -- IF ( p_dim_map (i).VALUE IS NULL
1926 -- OR p_dim_map (i).VALUE = ''
1927 -- OR p_dim_map (i).VALUE = 'All')
1928 IF (p_dim_map (i).VALUE = 'All')
1929 THEN NULL;
1930 /* Added by OKI */
1931 --------------------------------------------------------------------------------
1932 ELSIF i = g_sales_grp_dim
1933 THEN NULL;
1934 --------------------------------------------------------------------------------
1935 ELSIF (p_trend = 'Y')
1936 THEN
1937 l_where_clause := l_where_clause || ' and (fact.' || p_dim_map (i).col_name || ' is null or fact.' || p_dim_map (i).col_name || ' in (&' || i || ')) ';
1938 ELSE
1939 l_where_clause := l_where_clause || ' and fact.' || p_dim_map (i).col_name || ' in (&' || i || ') ';
1940 END IF;
1941 -- start addition by blindaue
1942 --insert into debug values ('p_dim_map(i).view_by_table: '|| p_dim_map(i).view_by_table,sysdate);commit;
1943
1944 IF ( p_view_by = 'ORGANIZATION+FII_OPERATING_UNITS'
1945 OR (p_dim_map(i).view_by_table = '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))' AND p_dim_map(i).value <> 'All'))
1946 THEN l_ou_flag := 0;
1947 ELSE NULL;
1948 END IF;
1949
1950 IF ( p_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
1951 OR (p_dim_map(i).view_by_table = 'FII_PARTNER_MKT_CLASS_V' AND p_dim_map(i).value <> 'All'))
1952 THEN l_cc_flag := 1;
1953 ELSE NULL;
1954 END IF;
1955
1956 -- IF (p_view_by = 'ITEM+ENI_ITEM'
1957 -- OR p_dim_map(i).value = 'All')
1958 -- THEN NULL;
1959 -- ELSE
1960
1961 IF (p_view_by = 'ITEM+ENI_ITEM_PROD_LEAF_CAT'
1962 OR (p_dim_map(i).view_by_table = 'OKI_ENI_ITEM_VBH_NODES_V' AND p_dim_map(i).value <> 'All'))
1963 THEN l_pc_flag := 0;
1964 ELSE NULL;
1965 END IF;
1966 -- end addition by blindaue
1967
1968 IF (p_view_by = 'ITEM+ENI_ITEM'
1969 OR (p_dim_map(i).view_by_table = 'ENI_ITEM_V' AND p_dim_map(i).value <> 'All'))
1970 THEN l_45 := true;
1971 END IF;
1972
1973 /*
1974 Commented by Pushkala - Term reason and cncl reason is available in 03*_mvs.
1975 IF (p_view_by = 'OKI_STATUS+CNCL_REASON'
1976 OR (p_dim_map(i).view_by_table = 'OKI_CANCEL_STATUSES_V' AND p_dim_map(i).value <> 'All'))
1977 THEN l_45 := true;
1978 END IF;
1979 IF (p_view_by = 'OKI_STATUS+TERM_REASON'
1980 OR (p_dim_map(i).view_by_table = 'OKI_TERM_REASONS_V' AND p_dim_map(i).value <> 'All'))
1981 THEN l_45 := true;
1982 END IF;
1983 */
1984 i := p_dim_map.NEXT (i);
1985 END LOOP;
1986
1987
1988 IF (p_mv_set in ('SRM_ST_71','SRM_TM_71','SRM_EN_71','SRM_BAL','SRM_SG_71')) then
1989 cc_flag := ' and fact.cc_flag <> '|| l_cc_flag;
1990 ELSE
1991 cc_flag := ' ';
1992 END IF;
1993
1994
1995
1996 IF p_mv_set NOT IN ('SRM_TBK_RPT','SRM_LATE_BKING') THEN
1997 /* Pushkala : Changed the section - for reports using the new views */
1998 IF not l_45
1999 THEN l_where_clause := l_where_clause
2000 || ' and fact.pc_flag = '|| l_pc_flag;
2001 ELSE
2002 l_where_clause := l_where_clause|| cc_flag;
2003 END IF;
2004 END IF;
2005
2006 RETURN l_where_clause;
2007 END get_where_clauses;
2008
2009 PROCEDURE add_join_table (
2010 p_join_tbl IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
2011 , p_column_name IN VARCHAR2
2012 , p_table_name IN VARCHAR2
2013 , p_table_alias IN VARCHAR2
2014 , p_fact_column IN VARCHAR2
2015 , p_dim_outer_join IN VARCHAR2 := 'N'
2016 , p_additional_where_clause IN VARCHAR2)
2017 IS
2018 l_join_tbl_rec poa_dbi_util_pkg.poa_dbi_join_rec;
2019 BEGIN
2020 l_join_tbl_rec.column_name := p_column_name;
2021 l_join_tbl_rec.table_name := p_table_name;
2022 l_join_tbl_rec.table_alias := p_table_alias;
2023 l_join_tbl_rec.fact_column := p_fact_column;
2024 l_join_tbl_rec.dim_outer_join := p_dim_outer_join;
2025 l_join_tbl_rec.additional_where_clause := p_additional_where_clause;
2026 p_join_tbl.EXTEND;
2027 p_join_tbl (p_join_tbl.COUNT) := l_join_tbl_rec;
2028 END;
2029
2030 PROCEDURE join_rpt_where (
2031 p_join_tbl IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
2032 , p_func_area IN VARCHAR2
2033 , p_version IN VARCHAR2
2034 , p_role IN VARCHAR2
2035 , p_mv_set IN VARCHAR2)
2036 IS
2037 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
2038 BEGIN
2039 l_join_tbl := p_join_tbl;
2040
2041 IF ( p_func_area = 'SRM_CUST' )
2042 THEN
2043 add_join_table (p_join_tbl => p_join_tbl
2044 , p_column_name => 'id'
2045 , p_table_name => 'fii_customers_v'
2046 , p_table_alias => 'cust'
2047 , p_fact_column => 'customer_party_id'
2048 , p_additional_where_clause => NULL);
2049 ELSIF ( p_mv_set IN('SRM_DTL_RPT'))
2050 THEN
2051
2052 add_join_table (p_join_tbl => p_join_tbl
2053 , p_column_name => 'chr_id'
2054 , p_table_name => 'OKI_SCM_OCR_MV'
2055 , p_table_alias => 'k'
2056 , p_fact_column => 'chr_id'
2057 , p_additional_where_clause => NULL);
2058 add_join_table (p_join_tbl => p_join_tbl
2059 , p_column_name => 'id'
2060 , p_table_name => 'fii_customers_v'
2061 , p_table_alias => 'cust'
2062 , p_fact_column => 'customer_party_id'
2063 , p_additional_where_clause => NULL);
2064 add_join_table (p_join_tbl => p_join_tbl
2065 , p_column_name => 'resource_id'
2066 , p_table_name => 'jtf_rs_resource_extns_vl'
2067 , p_table_alias => 'rsex'
2068 , p_fact_column => 'resource_id'
2069 , p_dim_outer_join => 'Y'
2070 , p_additional_where_clause => NULL);
2071 ELSIF (p_mv_set in ('SRM_TBK_RPT','SRM_CDTL_RPT')) THEN
2072 --Pushkala : 71 changes
2073 add_join_table (p_join_tbl => p_join_tbl
2074 , p_column_name => 'id'
2075 , p_table_name => 'fii_customers_v'
2076 , p_table_alias => 'cust'
2077 , p_fact_column => 'customer_party_id'
2078 , p_additional_where_clause => NULL);
2079 add_join_table (p_join_tbl => p_join_tbl
2080 , p_column_name => 'resource_id'
2081 , p_table_name => 'jtf_rs_resource_extns_vl'
2082 , p_table_alias => 'rsex'
2083 , p_fact_column => 'resource_id'
2084 , p_dim_outer_join => 'Y'
2085 , p_additional_where_clause => NULL);
2086
2087 ELSIF ( p_func_area = 'SRM'
2088 AND p_version = ('6.0'))
2089 THEN
2090 add_join_table (p_join_tbl => p_join_tbl
2091 , p_column_name => 'chr_id'
2092 , p_table_name => 'oki_scm_000_mv'
2093 , p_table_alias => 'k'
2094 , p_fact_column => 'chr_id'
2095 , p_additional_where_clause => NULL);
2096 add_join_table (p_join_tbl => p_join_tbl
2097 , p_column_name => 'id'
2098 , p_table_name => 'fii_customers_v'
2099 , p_table_alias => 'cust'
2100 , p_fact_column => 'customer_party_id'
2101 , p_additional_where_clause => NULL);
2102 add_join_table (p_join_tbl => p_join_tbl
2103 , p_column_name => 'resource_id'
2104 , p_table_name => 'jtf_rs_resource_extns_vl'
2105 , p_table_alias => 'rsex'
2106 , p_fact_column => 'resource_id'
2107 , p_dim_outer_join => 'Y'
2108 , p_additional_where_clause => NULL);
2109 END IF;
2110 END join_rpt_where;
2111
2112 FUNCTION add_measures (
2113 measure1 IN VARCHAR2
2114 , measure2 IN VARCHAR2)
2115 RETURN VARCHAR2
2116 IS
2117 BEGIN
2118 RETURN ' NVL2( COALESCE(' || measure1 || ',' || measure2 || ') ,(NVL(' || measure1 || ',0)+NVL(' || measure2 || ',0)),NULL )';
2119
2120 END add_measures;
2121
2122 FUNCTION subtract_measures (
2123 measure1 IN VARCHAR2
2124 , measure2 IN VARCHAR2)
2125 RETURN VARCHAR2
2126 IS
2127 BEGIN
2128 RETURN ' NVL2( COALESCE(' || measure1 || ',' || measure2 || ') ,(NVL(' || measure1 || ',0)-NVL(' || measure2 || ',0)),NULL )';
2129
2130 END subtract_measures;
2131
2132 PROCEDURE get_bind_vars (
2133 x_custom_output IN OUT NOCOPY bis_query_attributes_tbl)
2134 IS
2135 l_custom_rec bis_query_attributes;
2136 BEGIN
2137 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
2138 -- Unassigned bind variable
2139 fnd_message.set_name (application => 'BIS'
2140 , NAME => 'EDW_UNASSIGNED');
2141 l_custom_rec.attribute_name := '&UNASSIGNED';
2142 l_custom_rec.attribute_value := fnd_message.get;
2143 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2144 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2145 x_custom_output.EXTEND;
2146 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2147 -- Direct Report bind variable
2148 fnd_message.set_name (application => 'BIS'
2149 , NAME => 'BIS_PMF_DIRECT_REP');
2150 l_custom_rec.attribute_name := '&DIRECT_REPORT';
2151 l_custom_rec.attribute_value := fnd_message.get;
2152 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2153 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2154 x_custom_output.EXTEND;
2155 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2156
2157 l_custom_rec.attribute_name := '&YTD_NESTED_PATTERN';
2158 l_custom_rec.attribute_value := 119;
2159 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2160 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
2161 x_custom_output.EXTEND;
2162 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2163
2164 l_custom_rec.attribute_name := '&OKI_RG';
2165 l_custom_rec.attribute_value := g_rs_group_id;
2166 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2167 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
2168 x_custom_output.EXTEND;
2169 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2170 l_custom_rec.attribute_name := '&OKI_RS';
2171 l_custom_rec.attribute_value := g_resource_id;
2172 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2173 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
2174 x_custom_output.EXTEND;
2175 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2176
2177 l_custom_rec.attribute_name := '&SITEM_ID';
2178 l_custom_rec.attribute_value := g_itemid;
2179 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2180 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
2181 x_custom_output.EXTEND;
2182 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2183 l_custom_rec.attribute_name := '&INV_ORGID';
2184 l_custom_rec.attribute_value := g_invorgid ;
2185 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2186 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
2187 x_custom_output.EXTEND;
2188 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2189
2190 END get_bind_vars;
2191
2192 PROCEDURE get_custom_trend_binds (
2193 p_xtd IN VARCHAR2
2194 , p_comparison_type IN VARCHAR2
2195 , x_custom_output OUT NOCOPY bis_query_attributes_tbl)
2196 IS
2197 l_custom_rec bis_query_attributes;
2198 BEGIN
2199 -- get binds that are common across applications
2200 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => p_xtd
2201 , p_comparison_type => p_comparison_type
2202 , x_custom_output => x_custom_output);
2203 -- get binds that are specific to OKI
2204 get_bind_vars (x_custom_output => x_custom_output);
2205 END get_custom_trend_binds;
2206
2207 PROCEDURE get_custom_status_binds (
2208 x_custom_output OUT NOCOPY bis_query_attributes_tbl)
2209 IS
2210 l_custom_rec bis_query_attributes;
2211 BEGIN
2212 -- get binds that are common across applications
2213 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output => x_custom_output);
2214 -- get binds that are specific to OKI
2215 get_bind_vars (x_custom_output => x_custom_output);
2216 END get_custom_status_binds;
2217
2218
2219 PROCEDURE get_bis_bucket_binds (
2220 x_custom_output IN OUT NOCOPY bis_query_attributes_tbl,
2221 x_bis_bucket IN bis_bucket_pub.BIS_BUCKET_REC_TYPE)
2222 IS
2223 l_custom_rec bis_query_attributes;
2224 BEGIN
2225 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
2226
2227 l_custom_rec.attribute_name := '&RANGE1_NAME';
2228 l_custom_rec.attribute_value := x_bis_bucket.range1_name;
2229 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2230 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2231 x_custom_output.EXTEND;
2232 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2233
2234 l_custom_rec.attribute_name := '&RANGE2_NAME';
2235 l_custom_rec.attribute_value := x_bis_bucket.range2_name;
2236 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2237 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2238 x_custom_output.EXTEND;
2239 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2240
2241 l_custom_rec.attribute_name := '&RANGE3_NAME';
2242 l_custom_rec.attribute_value := x_bis_bucket.range3_name;
2243 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2244 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2245 x_custom_output.EXTEND;
2246 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2247
2248 l_custom_rec.attribute_name := '&RANGE4_NAME';
2249 l_custom_rec.attribute_value := x_bis_bucket.range4_name;
2250 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2251 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2252 x_custom_output.EXTEND;
2253 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2254
2255 l_custom_rec.attribute_name := '&RANGE5_NAME';
2256 l_custom_rec.attribute_value := x_bis_bucket.range5_name;
2257 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2258 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2259 x_custom_output.EXTEND;
2260 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2261
2262 l_custom_rec.attribute_name := '&RANGE6_NAME';
2263 l_custom_rec.attribute_value := x_bis_bucket.range6_name;
2264 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2265 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2266 x_custom_output.EXTEND;
2267 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2268
2269 l_custom_rec.attribute_name := '&RANGE7_NAME';
2270 l_custom_rec.attribute_value := x_bis_bucket.range7_name;
2271 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2272 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2273 x_custom_output.EXTEND;
2274 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2275
2276 l_custom_rec.attribute_name := '&RANGE8_NAME';
2277 l_custom_rec.attribute_value := x_bis_bucket.range8_name;
2278 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2279 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2280 x_custom_output.EXTEND;
2281 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2282
2283 l_custom_rec.attribute_name := '&RANGE9_NAME';
2284 l_custom_rec.attribute_value := x_bis_bucket.range9_name;
2285 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2286 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2287 x_custom_output.EXTEND;
2288 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2289
2290 l_custom_rec.attribute_name := '&RANGE10_NAME';
2291 l_custom_rec.attribute_value := x_bis_bucket.range10_name;
2292 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2293 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2294 x_custom_output.EXTEND;
2295 x_custom_output (x_custom_output.COUNT) := l_custom_rec;
2296
2297 END get_bis_bucket_binds;
2298
2299
2300 FUNCTION get_default_portlet_param (
2301 p_region_code IN VARCHAR2)
2302 RETURN VARCHAR2
2303 IS
2304 l_params VARCHAR2 (500);
2305 -- l_sg_id VARCHAR(30);
2306 BEGIN
2307
2308 -- l_sg_id := get_sg_id;
2309
2310 IF (p_region_code = 'OKI_DBI_SRG_PARAM')
2311 THEN
2312 l_params := '&SEQUENTIAL=TIME_COMPARISON_TYPE+SEQUENTIAL&FII_TIME_ENT_PERIOD_FROM=All&FII_TIME_ENT_PERIOD_TO=All&FII_CURRENCIES=FII_GLOBAL1&JTF_ORG_SALES_GROUP='||get_sg_id;
2313
2314 END IF;
2315
2316 RETURN l_params;
2317 END get_default_portlet_param;
2318
2319 FUNCTION get_view_by (
2320 p_param IN bis_pmv_page_parameter_tbl)
2321 RETURN VARCHAR2
2322 IS
2323 p_view_by VARCHAR2 (100);
2324 BEGIN
2325 FOR i IN 1 .. p_param.COUNT
2326 LOOP
2327 IF (p_param (i).parameter_name = 'VIEW_BY')
2328 THEN
2329 p_view_by := p_param (i).parameter_value;
2330 END IF;
2331 END LOOP;
2332
2333 RETURN p_view_by;
2334 END get_view_by;
2335
2336 FUNCTION get_param_id (
2337 p_param IN bis_pmv_page_parameter_tbl
2338 , p_param_name IN VARCHAR2)
2339 RETURN VARCHAR2
2340 IS
2341 p_param_id VARCHAR2 (100);
2342 BEGIN
2343 FOR i IN 1 .. p_param.COUNT
2344 LOOP
2345 IF (p_param (i).parameter_name = p_param_name)
2346 THEN
2347 p_param_id := p_param (i).parameter_id;
2348 END IF;
2349 END LOOP;
2350
2351 RETURN p_param_id;
2352 END get_param_id;
2353
2354
2355 PROCEDURE split_pseudo_rs_group (
2356 p_param IN bis_pmv_page_parameter_tbl)
2357 IS
2358 l_pseudo_rs_group VARCHAR2 (200);
2359 l_sep NUMBER;
2360 BEGIN
2361 g_rs_group_id := NULL;
2362 g_resource_id := NULL;
2363
2364 l_pseudo_rs_group := get_param_id (p_param
2365 , 'ORGANIZATION+JTF_ORG_SALES_GROUP');
2366 l_pseudo_rs_group := REPLACE (l_pseudo_rs_group
2367 , '''');
2368 COMMIT;
2369
2370 IF (l_pseudo_rs_group = '-1111')
2371 THEN
2372 g_rs_group_id := -1111;
2373 g_resource_id := NULL;
2374 ELSE
2375 l_sep := INSTR (l_pseudo_rs_group
2376 , '.');
2377 IF (l_sep > 0)
2378 THEN
2379 g_resource_id := TO_NUMBER (SUBSTR (l_pseudo_rs_group
2380 , 0
2381 , l_sep-1 ));
2382 g_rs_group_id := TO_NUMBER (SUBSTR (l_pseudo_rs_group
2383 , l_sep + 1));
2384 ELSE
2385 g_rs_group_id := TO_NUMBER (l_pseudo_rs_group);
2386 g_resource_id := NULL;
2387 END IF;
2388 END IF;
2389
2390 COMMIT;
2391 END split_pseudo_rs_group;
2392
2393 FUNCTION two_way_join ( sel_clause VARCHAR2,
2394 query1 VARCHAR2,
2395 query2 varchar2,
2396 join_column1 varchar2,
2397 join_column2 varchar2)
2398 return varchar2 IS
2399 BEGIN
2400 return
2401 'select '||join_column1||
2402 ' , '||sel_clause ||
2403 ' from ( ( '|| query1 ||' ) UNION ALL ('|| query2 ||' ) )'||
2404 ' GROUP BY '||join_column1;
2405 END two_way_join;
2406
2407 FUNCTION get_sg_id RETURN VARCHAR2 IS
2408 l_sg_id VARCHAR2(100);
2409 BEGIN
2410
2411 SELECT id
2412 INTO l_sg_id
2413 FROM
2414 (
2415 SELECT id, rank() over (order by value nulls last) rnk
2416 FROM jtf_rs_dbi_res_grp_vl
2417 WHERE usage = 'SALES'
2418 AND current_id = -1111
2419 AND denorm_level = 0
2420 )
2421 where rnk = 1;
2422
2423 RETURN l_sg_id;
2424
2425 EXCEPTION
2426 WHEN OTHERS THEN
2427 RETURN -1111;
2428 END get_sg_id;
2429
2430 FUNCTION change_clause(cur_col IN VARCHAR2, prior_col IN VARCHAR2, change_type IN VARCHAR2 := 'NP', prod in VARCHAR2 := 'OKI')
2431 RETURN VARCHAR2
2432 IS
2433
2434 BEGIN
2435
2436 if (prod = 'OKI') then
2437 if(change_type = 'NP') then -- measure is AMT
2438 return '(((' || cur_col || ' - ' || prior_col ||
2439 ')/abs(decode(' || prior_col || ',0,null,'|| prior_col
2440 || '))) * 100)';
2441 else
2442 return '(' || cur_col || ' - ' || prior_col || ')'; -- rate or ratio
2443 end if;
2444
2445 else
2446 -- old POA change code
2447 return '(((nvl(' || cur_col || ',0) - ' || prior_col ||
2448 ')/abs(decode(' || prior_col || ',0,null,'
2449 || prior_col
2450 || '))) * 100)';
2451
2452 end if;
2453
2454 -- if CHANGE IS A RATE OR RATIO then check if prior and current exists..
2455 /* return 'NVL2(coalesce(' || cur_col || ', ' || prior_col || ')' ||
2456 ',(nvl(' || cur_col || ',0) - nvl(' || prior_col || ',0) )' ||
2457 ', NULL)'; -- NEW CODE
2458 */
2459 END change_clause;
2460
2461 END oki_dbi_util_pvt;