[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_QUERY_PVT
Source
1 package body BIS_PMV_QUERY_PVT AS
2 /* $Header: BISVQUEB.pls 120.1 2005/09/09 03:30:09 msaran noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.112=120.1):~PROD:~PATH:~FILE
5 --
6 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
7 -- directory of SQL Navigator
8 --
9 -- Purpose: Briefly explain the functionality of the package
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- nbarik 19-SEP-2002 Bug Fix 2503050 NLS Sort for VARCHAR2
14 -- nbarik 26-SEP-2002 Bug Fix 1917856 Commented the formatting of date here,
15 -- will be done in java files according to NLS Date format
16 -- nkishore 01-OCT-2002 Bug Fix 2598917 Query obtained is null
17 -- nbarik 03-OCT-2002 Bug Fix 2605121 Added where condition for cursor
18 -- --------- ------ ------------------------------------------
19 -- Enter package declarations as shown below
20 gvAll VARCHAR2(3) := 'ALL';
21 gvCode VARCHAR2(100) := '';
22
23 -- serao - 02/20/02- This is the seperator used for bind variables while constructing the query string
24 SEPERATOR VARCHAR2(1) := '~';
25 ORDER_BY_SUBST_VAR VARCHAR2(16):= '&ORDER_BY_CLAUSE';
26 START_INDEX_SUBST_VAR VARCHAR(13) := '&START_INDEX';
27 END_INDEX_SUBST_VAR VARCHAR(11) := '&END_INDEX';
28 --serao - to split the variables for the in, 'not in' etc clauses of the queryt
29 -- so 234, 235 should become in :1, :2
30 procedure splitMultipleVariables (
31 lString IN VARCHAR2,
32 x_bind_variables IN OUT NOCOPY VARCHAR2, -- will contain the bind variables in the order
33 --x_bind_indexes IN OUT NOCOPY VARCHAR2,
34 x_bind_count IN OUT NOCOPY NUMBER,
35 x_split_string OUT NOCOPY VARCHAR2 -- will contain :1, :2 etc.
36 ) IS
37 lindex NUMBER;
38 firstTime BOOLEAN := TRUE;
39 pString VARCHAR2(2000) := lString;
40 BEGIN
41
42 lindex := instr (pSTring, ','); -- index of comma
43 -- if there is a comma
44 while (lindex >0) loop
45 x_bind_variables := x_bind_variables ||SEPERATOR|| substr (pString, 1, lindex-1);
46
47 x_bind_count := x_bind_count+1;
48 if not(firstTime) THEN
49 x_split_string := x_split_string || ' ,';
50 else
51 firstTime := FALSE;
52 END IF;
53
54 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
55 x_split_string := x_split_string || ' :'||x_bind_count;
56
57 pString := substr (pString , lindex+1, length(pString));
58 lindex := instr (pString, ',');
59 end loop;
60
61 -- for the last element
62 x_bind_variables := x_bind_variables ||SEPERATOR|| pString;
63
64 x_bind_count := x_bind_count+1;
65 if not(firstTime) THEN
66 x_split_string := x_split_string || ' ,';
67 else
68 firstTime := FALSE;
69 END IF;
70
71 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
72 x_split_string := x_split_string || ' :'||x_bind_count;
73
74 END splitMultipleVariables;
75
76 /** pvt procedure which will replace the paramName with value and return the bindVariable acc to the
77 the boolean pUseBindVariable */
78 PROCEDURE replaceNameWithValue(
79 pParamName IN VARCHAR2,
80 pParamValue IN VARCHAR2,
81 pUseBindVariable IN BOOLEAN DEFAULT true,
82 p_initial_index IN NUMBER DEFAULT 0,
83 p_original_sql in varchar2,
84 xClause IN OUT NOCOPY VARCHAR2,
85 x_bind_variables IN OUT NOCOPY VARCHAR2,
86 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
87 x_bind_indexes IN OUT NOCOPY VARCHAR2,
88 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
89 x_bind_count IN OUT NOCOPY NUMBER
90 ) IS
91 BEGIN
92 IF (pUseBindVariable) THEN
93 /*
94 x_bind_variables := x_bind_variables||SEPERATOR||pParamValue;
95 if (pBindIndex > 0) then
96 x_bind_indexes := x_bind_indexes||SEPERATOR||p_initial_index;
97 else
98 x_bind_indexes := x_bind_indexes||SEPERATOR||x_bind_count;
99 end if;
100 x_bind_count := x_bind_count+1;
101 xClause := replace(xClause, pParamName, ' :'||x_bind_count);
102 */
103
104 replace_with_bind_variables
105 (p_search_string => pParamName,
106 p_bind_value => pParamValue,
107 p_initial_index => p_initial_index,
108 p_original_sql => p_original_sql,
109 x_custom_sql => xClause,
110 x_bind_variables => x_bind_variables,
111 x_plsql_bind_variables => x_plsql_bind_variables,
112 x_bind_indexes => x_bind_indexes,
113 x_bind_datatypes => x_bind_datatypes,
114 x_bind_count => x_bind_count);
115
116 ELSE
117 xClause := replace(xClause, pParamName, pParamValue);
118 END IF;
119 END replaceNameWithValue;
120
121 /** replaces the BIS_PREVIOUS_EFFECTIVE_START_DATE and BIS_PREVIOUS_EFFECTIVE_END_DATE substitution variables */
122
123 -- enh 2467584
124 -- kiprabha /jprabhud
125 -- added p_replace_mode
126 -- p_replace_mode = '1' => previous time value already known (passed as p_default_start_date,p_default_end_date)
127 -- = '2' => API to get previous time values needs to get called
128 PROCEDURE replace_prev_time_parameters(
129 p_user_session_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
130 p_time_parameter IN VARCHAR2,
131 p_asof_date IN VARCHAR2,
132 p_time_comparison_type IN VARCHAR2,
133 p_default_start_date IN VARCHAR2,
134 p_default_end_date IN VARCHAR2,
135 p_original_sql in varchar2,
136 p_replace_mode IN VARCHAR2,
137 x_custom_sql IN OUT NOCOPY VARCHAR2,
138 x_return_status out NOCOPY VARCHAR2,
139 x_msg_count out NOCOPY NUMBER,
140 x_msg_data out NOCOPY VARCHAR2,
141 x_bind_variables IN OUT NOCOPY VARCHAR2,
142 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
143 x_bind_indexes IN OUT NOCOPY VARCHAR2,
144 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
145 x_bind_count IN OUT NOCOPY NUMBER
146 ) IS
147 l_time_id varchar2(32000);
148 l_prev_start_Date date;
149 l_prev_end_Date date;
150 l_prev_start_Date_c varchar2(200);
151 l_prev_end_Date_c varchar2(200);
152 l_description varchar2(32000);
153 l_index number;
154 BEGIN
155
156 --Get the previous effetive start and end dates and replace them also.
157 if (p_replace_mode <> '1') then
158 BIS_PMV_TIME_LEVELS_PVT.GET_PREVIOUS_TIME_LEVEL_VALUE
159 (p_DimensionLevel => p_time_parameter
160 ,p_region_code => p_user_session_rec.region_code
161 ,p_responsibility_id => p_user_session_rec.responsibility_id
162 ,p_asof_date => p_asof_date
163 ,p_time_comparison_type => p_time_comparison_type
164 ,x_time_level_id => l_time_id
165 ,x_time_level_Value => l_description
166 ,x_start_date => l_prev_start_date
167 ,x_end_date => l_prev_end_date
168 ,x_return_Status => x_return_Status
169 ,x_msg_count => x_msg_count
170 ,x_msg_data => x_msg_data
171 );
172 else
173 l_prev_start_date_c := p_default_start_date ;
174 l_prev_end_date_c := p_default_end_date ;
175 end if;
176
177 l_index := instrb(p_original_sql, '&BIS_PREVIOUS_EFFECTIVE_START_DATE');
178 if (l_index > 0 ) then
179 if (l_prev_start_date is null or length(l_prev_start_date) =0) then
180 l_prev_start_date_c := p_default_start_date;
181 else
182 l_prev_start_date_c := to_char(l_prev_start_date, 'DD-MON-YYYY');
183 end if;
184
185 /*
186 x_bind_indexes := x_bind_indexes||SEPERATOR||l_index;
187 x_bind_variables := x_bind_variables||SEPERATOR||l_prev_Start_Date_c;
188 x_bind_count := x_bind_count+1;
189 x_custom_sql := replace(x_custom_sql,'&BIS_PREVIOUS_EFFECTIVE_START_DATE', 'to_Date(:'||x_bind_count||', ''DD-MON-YYYY'')');
190 */
191
192 replace_with_bind_variables
193 (p_search_string => '&BIS_PREVIOUS_EFFECTIVE_START_DATE',
194 p_bind_value => l_prev_Start_Date_c,
195 p_initial_index => l_index,
196 p_bind_to_date =>'Y',
197 p_original_sql => p_original_sql,
198 x_custom_sql => x_custom_sql,
199 x_bind_variables => x_bind_variables,
200 x_plsql_bind_variables => x_plsql_bind_variables,
201 x_bind_indexes => x_bind_indexes,
202 x_bind_datatypes => x_bind_Datatypes,
203 x_bind_count => x_bind_count);
204
205 END IF;
206
207 l_index := instrb(p_original_sql, '&BIS_PREVIOUS_EFFECTIVE_END_DATE');
208 if (l_index > 0 ) then
209 if (l_prev_end_date is null or length(l_prev_end_date) =0) then
210 l_prev_end_date_c := p_default_end_date;
211 else
212 l_prev_end_date_c := to_char(l_prev_end_date, 'DD-MON-YYYY');
213 end if;
214
215 /*
216 x_bind_indexes := x_bind_indexes||SEPERATOR||l_index;
217 x_bind_variables := x_bind_variables||SEPERATOR||l_prev_end_Date_c;
218 x_bind_count := x_bind_count+1;
219 x_custom_sql := replace(x_custom_sql,'&BIS_PREVIOUS_EFFECTIVE_END_DATE', ' to_Date(:'||x_bind_count||',''DD-MON-YYYY'')');
220 */
221
222 replace_with_bind_variables
223 (p_search_string => '&BIS_PREVIOUS_EFFECTIVE_END_DATE',
224 p_bind_value => l_prev_end_Date_c,
225 p_initial_index => l_index,
226 p_bind_to_date => 'Y',
227 p_original_sql => p_original_sql,
228 x_custom_sql => x_custom_sql,
229 x_bind_variables => x_bind_variables,
230 x_plsql_bind_variables => x_plsql_bind_variables,
231 x_bind_indexes => x_bind_indexes,
232 x_bind_datatypes => x_bind_Datatypes,
233 x_bind_count => x_bind_count);
234
235 end if;
236
237
238 END replace_prev_time_parameters;
239
240 PROCEDURE retrieve_params_from_page (
241 p_user_session_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
242 p_paramlvlparam_Tbl IN BIS_PMV_PARAMETERS_PVT.parameter_tbl_Type,
243 x_asof_date OUT NOCOPY VARCHAR2,
244 x_prev_asof_date OUT NOCOPY VARCHAR2,
245 x_time_comparison_type OUT NOCOPY VARCHAR2,
246 x_return_status out NOCOPY VARCHAR2,
247 x_msg_count out NOCOPY NUMBER,
248 x_msg_data out NOCOPY VARCHAR2
249 ) IS
250
251 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
252 BEGIN
253 -- retrieve the page level parameters
254
255 l_parameter_rec := null;
256 if (p_paramlvlparam_tbl.COUNT > 0) then
257 FOR i in p_paramlvlparam_tbl.FIRST..p_paramlvlparam_tbl.LAST loop
258
259 l_parameter_rec := p_paramlvlparam_tbl(i);
260 if (l_parameter_rec.parameter_name = 'AS_OF_DATE') then
261 x_asof_date := l_parameter_rec.
262 parameter_description;
263 end if;
264 if (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') then
265 x_prev_asof_date := l_parameter_rec.
266 parameter_description;
267 end if;
268
269 if (l_parameter_Rec.dimension = 'TIME_COMPARISON_TYPE') then
270 x_time_comparison_type := l_parameter_rec.parameter_description;
271 end if;
272
273 end loop;
274 end if;
275
276 END retrieve_params_from_page;
277
278 --replaces the ASOF_DATE substitution variables
279 PROCEDURE replace_paramLvl_parameters(
280 p_user_session_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
281 p_asof_date IN VARCHAR2,
282 p_prev_asof_date IN VARCHAR2,
283 p_time_comparison_type IN VARCHAR2,
284 p_time_parameter IN VARCHAR2,
285 p_original_sql in varchar2,
286 x_custom_sql IN OUT NOCOPY VARCHAR2,
287 x_return_status out NOCOPY VARCHAR2,
288 x_msg_count out NOCOPY NUMBER,
289 x_msg_data out NOCOPY VARCHAR2,
290 x_bind_variables IN OUT NOCOPY VARCHAR2,
291 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
292 x_bind_indexes IN OUT NOCOPY VARCHAR2,
293 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
294 x_bind_count IN OUT NOCOPY NUMBER
295 ) IS
296
297 l_index number;
298 l_bind_function varchar2(2000);
299
300 BEGIN
301
302 l_index := instrb(p_original_sql,'&BIS_CURRENT_ASOF_DATE');
303 if (l_index > 0) then
304 /*
305 x_bind_indexes := x_bind_indexes||SEPERATOR||l_index;
306 x_bind_variables := x_bind_variables||SEPERATOR||p_asof_date;
307 x_bind_count := x_bind_count+1;
308 x_custom_sql := replace(x_custom_sql,'&BIS_CURRENT_ASOF_DATE', ' to_Date(:'||x_bind_count||',''DD-MON-YYYY'')');
309 */
310 replace_with_bind_variables
311 (p_search_string => '&BIS_CURRENT_ASOF_DATE',
312 p_bind_value => p_asof_date,
313 p_initial_index => l_index,
314 p_bind_to_date => 'Y',
315 p_original_sql => p_original_sql,
316 x_custom_sql => x_custom_sql,
317 x_bind_variables => x_bind_variables,
318 x_plsql_bind_variables => x_plsql_bind_variables,
319 x_bind_indexes => x_bind_indexes,
320 x_bind_Datatypes => x_bind_datatypes,
321 x_bind_count => x_bind_count);
322 end if;
323
324 --Get the previous as of date.
325 l_index := instrb(p_original_sql, '&BIS_PREVIOUS_ASOF_DATE');
326 if (l_index > 0) then
327 replace_with_bind_variables
328 (p_search_string => '&BIS_PREVIOUS_ASOF_DATE',
329 p_bind_value => p_prev_asof_date,
330 p_initial_index => l_index,
331 p_bind_to_date => 'Y',
332 p_original_sql => p_original_sql,
333 x_custom_sql => x_custom_sql,
334 x_bind_variables => x_bind_variables,
335 x_plsql_bind_variables => x_plsql_bind_variables,
336 x_bind_indexes => x_bind_indexes,
337 x_bind_datatypes => x_bind_datatypes,
338 x_bind_count => x_bind_count);
339
340 end if;
341
342 END replace_paramLvl_parameters;
343
344
345
346 -- replaces the time parameters
347 PROCEDURE replace_report_parameters(
348 p_user_session_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE ,
349 pParameterTbl IN BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
350 pStartChar IN CHAR DEFAULT '&',
351 pEndChar IN CHAR DEFAULT '',
352 pUseBindVariable In BOOLEAN DEFAULT true,
353 pReplaceSubstVariable In BOOLEAN DEFAULT true,
354 pReplaceXTDVariable In BOOLEAN DEFAULT FALSE, -- replace xtd for custom stuff w/o binding - this is as per reqt.
355 p_original_sql in varchar2,
356 x_custom_sql IN OUT NOCOPY VARCHAR2,
357 x_temp_Start_date OUT NOCOPY VARCHAR2,
358 x_temp_end_date OUT NOCOPY VARCHAR2,
359 x_time_parameter OUT NOCOPY VARCHAR2,
360 x_asOf_date OUT NOCOPY VARCHAR2,
361 x_prev_asof_date OUT NOCOPY VARCHAR2,
362 x_time_comparison_type OUT NOCOPY VARCHAR2,
363 x_bind_variables IN OUT NOCOPY VARCHAR2,
364 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
365 x_bind_indexes IN OUT NOCOPY VARCHAR2,
366 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
367 x_bind_count IN OUT NOCOPY NUMBER
368 ) IS
369 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
370 l_lookup_type varchar2(2000) := 'BIS_TIME_LEVEL_VALUES';
371 l_Dimlevel_Acronym varchar2(2000);
372 l_index number;
373 l_param_name VARCHAR2(32000);
374 BEGIN
375
376 if (pParameterTbl.COUNT > 0) then
377 for i in pParameterTbl.FIRST..pParameterTbl.LAST loop
378 l_parameter_Rec := pParameterTbl(i);
379
380 --check if as-of-date was in the current session
381 --if (p_user_session_rec.page_id is null) then
382 if (l_parameter_rec.parameter_name = 'AS_OF_DATE') then
383 x_asof_date := l_parameter_rec.parameter_description;
384 end if;
385 if (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') then
386 x_prev_asof_date := l_parameter_rec.parameter_description;
387 end if;
388 if (l_parameter_Rec.dimension = 'TIME_COMPARISON_TYPE') then
389 x_time_comparison_type := l_parameter_rec.parameter_description;
390 end if;
391 --end if;
392
393 -- dim+dimlevel combination
394 l_index := instrb(p_original_sql, pStartChar||l_parameter_rec.parameter_name||pEndChar);
395 if (l_index > 0) then
396 --jprabhud 08/06/02 - Bug #2468074 Selectively disable drill across links
397 if(l_parameter_rec.parameter_name <> 'VIEW_BY') then
398 replaceNameWithValue(
399 pParamName =>pStartChar|| l_parameter_rec.parameter_name||pEndChar,
400 pParamValue => l_parameter_rec.parameter_value,
401 pUseBindVariable => pUseBindVariable,
402 p_initial_index => l_index,
403 p_original_sql => p_original_sql,
404 xClause => x_custom_sql,
405 x_bind_variables => x_bind_variables,
406 x_plsql_bind_variables => x_plsql_bind_variables,
407 x_bind_indexes => x_bind_indexes,
408 x_bind_datatypes => x_bind_datatypes,
409 x_bind_count => x_bind_count
410 );
411 end if;
412 end if;
413 if (l_parameter_rec.parameter_name = 'VIEW_BY') then
414 l_index := instrb(p_original_Sql,'&BIS_VIEW_BY');
415 if (l_index > 0 and (instrb(x_custom_Sql,'&BIS_VIEW_BY') > 0) ) then
416 replaceNameWithValue(
417 pParamName => '&BIS_VIEW_BY',
418 pParamValue => l_parameter_rec.parameter_value,
419 pUseBindVariable => pUseBindVariable,
420 p_initial_index => l_index,
421 p_original_sql => p_original_sql,
422 xClause => x_custom_sql,
423 x_bind_variables => x_bind_variables,
424 x_plsql_bind_variables => x_plsql_bind_variables,
425 x_bind_indexes => x_bind_indexes,
426 x_bind_datatypes => x_bind_datatypes,
427 x_bind_count => x_bind_count
428 );
429 end if;
430 end if;
431
432 if (pReplaceSubstVariable AND (l_parameter_rec.dimension = 'TIME' or l_parameter_Rec.dimension = 'EDW_TIME_M') ) then
433 -- check if the xtd is to be replaced
434 IF (pReplaceXTDVariable ) THEN
435 l_dimlevel_acronym := getParameterAcronym (l_lookup_type,l_parameter_Rec.parameter_name);
436 -- replace the lookup with the value e,g &_xtd without bind variables
437 IF (instrb(x_custom_sql, '&XTD') > 0) then
438 x_custom_Sql := replace(x_custom_sql,'&XTD', l_dimlevel_acronym);
439 END IF;
440 END IF;
441
442 --replace the start date
443 if (substr(l_parameter_rec.parameter_name, length(l_parameter_Rec.parameter_name)-4) = '_FROM') then
444
445 x_temp_Start_date := to_Char(l_parameter_rec.period_date,'DD-MON-YYYY');
446 x_time_parameter := substr(l_parameter_rec.parameter_name,1, length(l_parameter_rec.parameter_name)-5);
447 l_index := instrb(p_original_sql,'&BIS_CURRENT_EFFECTIVE_START_DATE');
448 if (l_index >0 ) then
449 IF (pUseBindVariable) THEN
450 /*
451 x_bind_indexes := x_bind_indexes||SEPERATOR||l_index;
452 x_bind_variables := x_bind_variables||SEPERATOR||x_temp_start_date;
453 x_bind_count := x_bind_count+1;
454 x_custom_sql := replace(x_custom_sql, '&BIS_CURRENT_EFFECTIVE_START_DATE','to_Date(:'||x_bind_count||', ''DD-MON-YYYY'')');
455 */
456 replace_with_bind_variables
457 (p_search_string => '&BIS_CURRENT_EFFECTIVE_START_DATE',
458 p_bind_value => x_temp_start_date,
459 p_initial_index => l_index,
460 p_bind_to_date => 'Y',
461 p_original_sql => p_original_sql,
462 x_custom_sql => x_custom_sql,
463 x_bind_variables => x_bind_variables,
464 x_plsql_bind_variables => x_plsql_bind_variables,
465 x_bind_indexes => x_bind_indexes,
466 x_bind_datatypes => x_bind_datatypes,
467 x_bind_count => x_bind_count);
468
469 ELSE
470 x_custom_sql := replace(x_custom_sql, '&BIS_CURRENT_EFFECTIVE_START_DATE', 'to_date('||x_temp_Start_date||', ''DD-MON-YYYY'')');
471 END IF;
472 end if;
473
474 end if;
475
476 --replace the end date
477 if (substr(l_parameter_rec.parameter_name, length(l_parameter_Rec.parameter_name)-2) = '_TO') then
478 x_temp_end_date := to_char(l_parameter_rec.period_date,'DD-MON-YYYY');
479
480 l_index := instrb(p_original_sql,'&BIS_CURRENT_EFFECTIVE_END_DATE');
481 if (l_index >0 ) then
482 IF (pUseBindVariable) THEN
483 /*
484 x_bind_indexes := x_bind_indexes||SEPERATOR||l_index;
485 x_bind_variables := x_bind_variables||SEPERATOR||x_temp_end_date;
486 x_bind_count := x_bind_count+1;
487 x_custom_sql := replace(x_custom_sql, '&BIS_CURRENT_EFFECTIVE_END_DATE', 'to_Date(:'||x_bind_count||', ''DD-MON-YYYY'')');
488 */
489 replace_with_bind_variables
490 (p_search_string => '&BIS_CURRENT_EFFECTIVE_END_DATE',
491 p_bind_value => x_temp_end_date,
492 p_initial_index => l_index,
493 p_bind_to_date => 'Y',
494 p_original_sql => p_original_sql,
495 x_custom_sql => x_custom_sql,
496 x_bind_variables => x_bind_variables,
497 x_plsql_bind_variables => x_plsql_bind_variables,
498 x_bind_indexes => x_bind_indexes,
499 x_bind_Datatypes => x_bind_datatypes,
500 x_bind_count => x_bind_count);
501
502 ELSE
503 x_custom_sql := replace(x_custom_sql, '&BIS_CURRENT_EFFECTIVE_END_DATE', 'to_date('||x_temp_End_date||', ''DD-MON-YYYY'')');
504 END IF;
505 end if;
506
507 end if;
508
509 end if; --TIME
510
511 end loop;
512 end if; --IF COUNT
513
514
515 END replace_report_parameters;
516
517 /** substitute each of the dim+dimLevels of the params (parameterName) present in the where clause*/
518
519 PROCEDURE replace_custom_sql(
520 p_user_session_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE ,
521 pParameterTbl IN BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE ,
522 p_original_sql in varchar2,
523 p_where IN OUT NOCOPY VARCHAR2,
524 x_bind_variables IN OUT NOCOPY VARCHAR2,
525 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
526 x_bind_indexes IN OUT NOCOPY VARCHAR2,
527 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
528 x_bind_count IN OUT NOCOPY NUMBER,
529 x_return_status out NOCOPY VARCHAR2,
530 x_msg_count out NOCOPY NUMBER,
531 x_msg_data out NOCOPY VARCHAR2
532
533 ) IS
534 l_time_parameter varchar2(2000);
535 l_asof_date varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
536 l_prev_asof_date varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
537 l_time_comparison_type varchar2(2000);
538 l_asof_date_page varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
539 l_prev_asof_date_page varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
540 l_time_comparison_type_page varchar2(2000);
541 l_temp_start_date varchar2(2000);
542 l_temp_end_date varchar2(2000);
543
544 l_paramlvlparam_Tbl BIS_PMV_PARAMETERS_PVT.parameter_tbl_Type;
545 BEGIN
546
547
548 replace_report_parameters(
549 p_user_session_rec => p_user_session_rec,
550 pParameterTbl => pParameterTbl,
551 p_original_sql => p_original_sql,
552 x_custom_sql => p_where,
553 x_temp_Start_date => l_temp_Start_date,
554 x_temp_end_date => l_temp_end_date,
555 x_time_parameter => l_time_parameter,
556 x_asOf_date => l_asof_date,
557 x_prev_asof_Date => l_prev_asof_Date,
558 x_time_comparison_type => l_time_comparison_type,
559 x_bind_variables => x_bind_variables,
560 x_plsql_bind_variables => x_plsql_bind_variables,
561 x_bind_indexes => x_bind_indexes,
562 x_bind_datatypes => x_bind_datatypes,
563 x_bind_count => x_bind_count
564 ) ;
565
566 IF (l_asof_date IS NULL OR l_time_comparison_type IS NULL) THEN
567
568 BIS_PMV_PARAMETERS_PVT.RETRIEVE_PARAMLVL_PARAMETERS
569 (p_user_session_Rec => p_user_session_rec
570 ,x_paramportlet_param_tbl => l_paramlvlparam_tbl
571 ,x_return_Status => x_return_Status
572 ,x_msg_count => x_msg_count
573 ,x_msg_data => x_msg_data
574 );
575
576 retrieve_params_from_page (
577 p_user_session_rec => p_user_session_rec,
578 p_paramlvlparam_Tbl => l_paramlvlparam_Tbl,
579 x_asof_date => l_asof_date_page,
580 x_prev_asof_date => l_prev_asof_date_page,
581 x_time_comparison_type => l_time_comparison_type_page,
582 x_return_status => x_return_status,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data
585 ) ;
586 END IF;
587
588 IF (l_asOf_Date IS NULL) THEN
589 IF (l_asof_date_page IS NOT NULL) THEN
590 l_asof_date := l_asof_date_page;
591 ELSE
592 l_asof_date := to_char(sysdate,'DD-MON-YYYY');
593 END IF;
594 END IF;
595 IF (l_prev_asOf_Date IS NULL) THEN
596 IF (l_prev_asof_date_page IS NOT NULL) THEN
597 l_prev_asof_date := l_prev_asof_date_page;
598 ELSE
599 l_prev_asof_date := to_char(sysdate,'DD-MON-YYYY');
600 END IF;
601 END IF;
602
603 IF (l_time_comparison_type IS NULL AND l_time_comparison_type_page IS NOT NULL ) THEN
604 l_time_comparison_type := l_time_comparison_type_page;
605 END IF;
606
607 replace_paramLvl_parameters(
608 p_user_session_rec => p_user_session_rec,
609 p_asof_date => l_asof_date ,
610 p_prev_asof_date => l_prev_asof_date ,
611 p_time_comparison_type => l_time_comparison_type ,
612 p_time_parameter => l_time_parameter,
613 p_original_sql => p_original_sql,
614 x_custom_sql => p_where ,
615 x_return_status => x_return_status ,
616 x_msg_count => x_msg_count ,
617 x_msg_data => x_msg_data ,
618 x_bind_variables => x_bind_variables,
619 x_plsql_bind_variables => x_plsql_bind_variables,
620 x_bind_indexes => x_bind_indexes,
621 x_bind_datatypes => x_bind_datatypes,
622 x_bind_count => x_bind_count
623 );
624
625
626 IF (instrb(p_where,'&BIS_PREVIOUS_EFFECTIVE_START_DATE') >0 OR
627 instrb(p_where,'&BIS_PREVIOUS_EFFECTIVE_END_DATE') > 0 ) THEN
628
629 -- enh 2467584
630 -- kiprabha /jprabhud
631 -- added p_replace_mode
632 -- p_replace_mode = '1' => previous time value already known (passed as p_default_start_date,p_default_end_date)
633 -- = '2' => API to get previous time values needs to get called
634 replace_prev_time_parameters(
635 p_user_session_rec => p_user_session_rec,
636 p_time_parameter => l_time_parameter,
637 p_asof_date => l_asof_date,
638 p_time_comparison_type => l_time_comparison_type,
639 p_default_start_date => l_temp_Start_date,
640 p_default_end_date => l_temp_end_date,
641 p_original_sql => p_original_sql,
642 p_replace_mode => '2',
643 x_custom_sql => p_where,
644 x_return_status => x_return_status,
645 x_msg_count =>x_msg_count,
646 x_msg_data => x_msg_data,
647 x_bind_variables => x_bind_variables,
648 x_plsql_bind_variables => x_plsql_bind_variables,
649 x_bind_indexes => x_bind_indexes,
650 x_bind_datatypes => x_bind_Datatypes,
651 x_bind_count => x_bind_count
652 );
653
654 END IF;
655
656
657 END replace_custom_sql;
658
659
660
661 procedure getQuerySQL(p_region_code in VARCHAR2,
662 p_function_name in VARCHAR2,
663 p_user_id in VARCHAR2,
664 p_session_id in VARCHAR2,
665 p_resp_id in VARCHAR2,
666 p_page_id in VARCHAR2 DEFAULT NULL,
667 p_schedule_id in VARCHAR2 DEFAULT NULL,
668 p_sort_attribute in VARCHAR2 DEFAULT NULL,
669 p_sort_direction in VARCHAR2 DEFAULT NULL,
670 p_source in varchar2 DEFAULT 'REPORT',
671 p_lower_bound IN INTEGER DEFAULT 1,
672 p_upper_bound IN INTEGER DEFAULT -1,
673 x_sql out NOCOPY VARCHAR2,
674 x_target_alias out NOCOPY VARCHAR2,
675 x_has_target out NOCOPY varchar2,
676 x_viewby_table out NOCOPY varchar2,
677 x_return_status out NOCOPY VARCHAR2,
678 x_msg_count out NOCOPY NUMBER,
679 x_msg_data out NOCOPY VARCHAR2,
680 x_bind_variables in OUT NOCOPY VARCHAR2,
681 x_plsql_bind_variables in OUT NOCOPY VARCHAR2,
682 x_bind_indexes in OUT NOCOPY VARCHAR2,
683 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
684 x_view_by_value OUT NOCOPY VARCHAR2
685 ) is
686
687 l_viewby_select VARCHAR2(32000);
688 l_column_select VARCHAR2(32000);
689 l_target_select VARCHAR2(32000);
690 l_select VARCHAR2(32000) := ' SELECT ';
691 l_from VARCHAR2(2000) := ' FROM ';
692 l_where VARCHAR2(32000) := ' WHERE ';
693 l_group_by VARCHAR2(2000);
694 l_order_by VARCHAR2(2000) := ' ';
695 l_user_groupby VARCHAR2(2000);
696 l_user_orderby VARCHAR2(2000);
697
698
699 -- parameter info from bis_user_attributes
700 l_user_session_rec BIS_PMV_SESSION_PVT.SESSION_REC_TYPE;
701 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
702 l_parameter_tbl BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE;
703
704 -- report info from ak_regions_vl
705 CURSOR ak_region_cursor (cpRegionCode VARCHAR2) IS
706 SELECT attribute1 disable_viewby,
707 attribute6 user_groupby,
708 attribute7 user_orderby,
709 database_object_name source_view,
710 region_object_type report_type,
711 attribute8 plsql_function,
712 attribute10 data_source,
713 attribute11 where_clause
714 FROM AK_REGIONS
715 WHERE region_code = cpRegionCode;
716
717 l_ak_region_rec BIS_PMV_METADATA_PVT.AK_REGION_REC;
718 l_ak_region_tbl BIS_PMV_METADATA_PVT.AK_REGION_TBL;
719
720 /*
721 l_disable_viewby VARCHAR2(10);
722 l_user_groupby VARCHAR2(2000);
723 l_user_orderby VARCHAR2(2000);
724 l_source_view VARCHAR2(2000);
725 l_report_type VARCHAR2(10);
726 */
727 -- save region items
728 CURSOR save_parameter_cursor (cpRegionCode VARCHAR2, cpParameterName VARCHAR2) IS
729 SELECT attribute2,
730 attribute3 base_column,
731 attribute4 where_clause,
732 attribute14 data_type
733 FROM AK_REGION_ITEMS
734 WHERE region_code = cpRegionCode
735 AND nested_region_code is null
736 AND (nvl(attribute2, attribute_code) = cpParameterName
737 or attribute2||'_FROM' = cpParameterName
738 or attribute2||'_TO' = cpParameterName)
739 ORDER BY display_sequence;
740
741 l_save_region_item_rec BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC;
742
743 -- report info from ak_region_items_vl
744 CURSOR ak_region_item_cursor (cpRegionCode VARCHAR2) IS
745 SELECT attribute1 attribute_type,
746 attribute_code,
747 attribute2,
748 attribute3 base_column,
749 attribute4 where_clause,
750 attribute15 lov_table,
751 attribute9 aggregate_function,
752 attribute14 data_type,
753 attribute7 data_format,
754 order_sequence,
755 order_direction,
756 node_query_flag
757 ,node_display_flag -- 2371922
758 FROM AK_REGION_ITEMS
759 WHERE region_code = cpRegionCode
760 AND nested_region_code is null
761 ORDER BY display_sequence;
762
763 l_item_count number;
764 l_ak_region_item_rec BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC;
765 l_ak_region_item_tbl BIS_PMV_METADATA_PVT.AK_REGION_ITEM_TBL;
766 l_Ak_count number :=1;
767 l_base_column_tbl BISVIEWER.t_char;
768 l_aggregation_tbl BISVIEWER.t_char;
769
770 /*
771 l_attribute_type VARCHAR2(2000);
772 l_attribute_code VARCHAR2(2000);
773 l_attribute2 VARCHAR2(2000);
774 l_base_column VARCHAR2(2000);
775 l_where_clause VARCHAR2(2000);
776 l_aggregate_function VARCHAR2(2000);
777 l_data_type VARCHAR2(2000);
778 l_data_format VARCHAR2(2000);
779 l_sort_attribute VARCHAR2(2000);
780 l_sort_direction VARCHAR2(2000);
781 */
782
783 -- report info from bis_ak_region_item_extension
784 CURSOR ak_region_item_ext_cursor (cpRegionCode VARCHAR2, cpAttributeCode VARCHAR2) IS
785 SELECT attribute16 extra_groupby
786 FROM BIS_AK_REGION_ITEM_EXTENSION
787 WHERE region_code = cpRegionCode
788 AND attribute_code = cpAttributeCode;
789
790 l_ak_region_item_ext_rec BIS_PMV_METADATA_PVT.AK_REGION_ITEM_EXT_REC;
791 l_ak_region_item_ext_tbl BIS_PMV_METADATA_PVT.AK_REGION_ITEM_EXT_TBL;
792
793 --l_extra_groupby VARCHAR2(2000);
794 CURSOR base_col_cursor IS
795 SELECT distinct attribute3 base_column, attribute9 aggregation_function
796 FROM AK_REGION_ITEMS
797 WHERE region_code = p_region_code
798 AND attribute9 is not null
799 AND nested_region_code is null
800 AND substr(attribute3,1,1) not in ('''','"');
801
802 --select variables
803 l_HR_report BOOLEAN := FALSE;
804 l_first_time BOOLEAN := TRUE;
805 l_no_target BOOLEAN := FALSE;
806
807 l_report_type VARCHAR2(10);
808 l_plan_id VARCHAR2(2000);
809 l_select_string VARCHAR2(2000);
810 l_viewby_attribute_code VARCHAR2(2000);
811 l_viewby_attribute2 VARCHAR2(2000);
812 l_viewby_datatype VARCHAR2(2000);
813 l_viewby_dimension VARCHAR2(2000);
814 l_viewby_dimension_level VARCHAR2(2000);
815 l_viewby_base_column VARCHAR2(2000);
816 l_viewby_table VARCHAR2(2000);
817 l_viewby_id_name VARCHAR2(2000);
818 l_viewby_value_name VARCHAR2(2000);
819 l_extra_groupby_label VARCHAR2(2000);
820 l_extra_groupby_name VARCHAR2(2000);
821 l_default_sort_attribute VARCHAR2(2000) := '';
822 l_def_sort_attr_tbl BISVIEWER.t_char;
823 l_Def_sort_seq_tbl BISVIEWER.t_Char;
824 l_def_sort_count NUMBER := 1;
825 l_sort_attr_code VARCHAR2(150);
826 --changed l_sort_attr_code size from 30 to 150 for bugfix 2598917
827 l_first_attr_code VARCHAR2(2000);
828 l_sort_attr_type VARCHAR2(2000);
829 l_sel_sort_attribute VARCHAR2(2000);
830
831 l_time_from_value VARCHAR2(2000);
832 l_time_from_description VARCHAR2(2000);
833 l_time_to_value VARCHAR2(2000);
834 l_time_to_description VARCHAR2(2000);
835 l_time_attribute2 VARCHAR2(2000);
836 l_time_dimension VARCHAR2(2000);
837 l_time_dimension_level VARCHAR2(2000);
838 l_time_table VARCHAR2(2000);
839 l_time_id_name VARCHAR2(2000);
840 l_time_value_name VARCHAR2(2000);
841
842 l_share_vbt_table BOOLEAN := FALSE;
843 l_TM_alias VARCHAR2(10) := 'TM';
844
845 l_OLTP_org_level VARCHAR2(2000);
846 l_OLTP_org_value VARCHAR2(2000);
847 l_OLTP_flag BOOLEAN := FALSE;
848
849 l_target_count number := 1;
850 l_target_alias VARCHAR2(2000);
851 l_target_alias_name VARCHAR2(2000);
852 l_target_alias_name_seperator VARCHAR2(1) := '#';
853 l_target_alias_group_seperator VARCHAR2(2000) := '*';
854
855 l_return_status VARCHAR2(2000);
856 l_msg_count NUMBER;
857 l_msg_data VARCHAR2(2000);
858
859 l_main_order_by VARCHAR2(100) :=NULL;
860 l_first_order_by VARCHAR2(100) := NULL;
861 l_second_order_by VARCHAR2(100) := NULL;
862 l_viewbyat varchar2(1);
863 l_bind_count NUMBER := 0;
864 l_custom_where VARCHAR2(300);
865
866 l_nls_sort_type VARCHAR2(30); --nbarik 19-SEP-2002 NLS Sort for VARCHAR2
867
868 begin
869 --set up user session info
870 l_user_session_rec.function_name := p_function_name;
871 l_user_session_rec.region_code := p_region_code;
872 l_user_session_rec.page_id := p_page_id;
873 l_user_session_rec.session_id := p_session_id;
874 l_user_session_rec.user_id := p_user_id;
875 l_user_session_rec.responsibility_id := p_resp_id;
876 l_user_session_rec.schedule_id := p_schedule_id;
877
878 --get report info from ak_regions_vl
879 if ak_region_cursor%ISOPEN then
880 close ak_region_cursor;
881 end if;
882 open ak_region_cursor(l_user_session_rec.region_code);
883 fetch ak_region_cursor into l_ak_region_rec;
884 close ak_region_cursor;
885 --l_ak_region_rec.data_source := 'PLSQL_PROCEDURE_QUERYATTRIBUTES';
886 if upper(nvl(l_ak_region_rec.report_type, 'OLTP')) = 'EDW' then
887 l_report_type := 'EDW';
888 else
889 l_report_type := 'OLTP';
890 end if;
891
892 --get parameter info from BIS_USER_ATTRIBUTES
893 if (p_schedule_id is null and p_source <> 'ACTUAL_FOR_KPI') or
894 (p_source = 'ACTUAL_FOR_KPI' and (p_page_id is null or p_page_id='')) then
895 BIS_PMV_PARAMETERS_PVT.RETRIEVE_SESSION_PARAMETERS
896 (p_user_session_rec => l_user_session_rec,
897 x_user_param_tbl => l_parameter_tbl,
898 x_return_status => l_return_status,
899 x_msg_count => l_msg_count,
900 x_msg_data => l_msg_data
901 );
902 elsif p_page_id is null or p_page_id = '' then
903
904 BIS_PMV_PARAMETERS_PVT.RETRIEVE_SCHEDULE_PARAMETERS
905 (p_schedule_id => p_schedule_id,
906 x_user_param_tbl => l_parameter_tbl,
907 x_return_status => l_return_status,
908 x_msg_count => l_msg_count,
909 x_msg_data => l_msg_data
910 );
911 elsif p_source in ('ACTUAL','ACTUAL_FOR_KPI') then
912
913 BIS_PMV_PARAMETERS_PVT.RETRIEVE_KPI_PARAMETERS
914 (p_user_session_rec => l_user_session_rec,
915 x_user_param_tbl => l_parameter_tbl,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data
919 );
920 else
921 BIS_PMV_PARAMETERS_PVT.RETRIEVE_PAGE_PARAMETERS
922 (p_schedule_id => p_schedule_id,
923 p_user_session_rec => l_user_session_rec,
924 x_user_param_tbl => l_parameter_tbl,
925 x_return_status => l_return_status,
926 x_msg_count => l_msg_count,
927 x_msg_data => l_msg_data
928 );
929 end if;
930
931 --set up parameters
932 if l_parameter_tbl.COUNT > 0 then
933 for i in l_parameter_tbl.FIRST..l_parameter_tbl.LAST loop
934 l_parameter_rec := l_parameter_tbl(i);
935 --business plan info
936 if l_parameter_rec.parameter_name = 'BUSINESS_PLAN' then
937 l_plan_id := l_parameter_rec.parameter_value;
938 end if;
939 --view by info
940 if l_parameter_rec.parameter_name = 'VIEW_BY' then
941 l_viewby_attribute2 := l_parameter_rec.parameter_value;
942 l_viewby_dimension := substr(l_viewby_attribute2, 1, instr(l_viewby_attribute2,'+')-1);
943 l_viewby_dimension_level := substr(l_viewby_attribute2, instr(l_viewby_attribute2,'+')+1);
944 end if;
945 --time info
946 if l_parameter_rec.dimension in ('TIME', 'EDW_TIME_M') then
947 if substr(l_parameter_rec.parameter_name,
948 length(l_parameter_rec.parameter_name)-length('_FROM')+1) = '_FROM' then
949 l_time_attribute2 := substr(l_parameter_rec.parameter_name,
950 1,
951 length(l_parameter_rec.parameter_name)-length('_FROM')
952 );
953 l_time_from_value := l_parameter_rec.parameter_value;
954 l_time_from_description := l_parameter_rec.parameter_description;
955 elsif substr(l_parameter_rec.parameter_name,
956 length(l_parameter_rec.parameter_name)-length('_TO')+1) = '_TO' then
957 if l_time_attribute2 is null or length(l_time_attribute2) = 0 then
958 l_time_attribute2 := substr(l_parameter_rec.parameter_name,
959 1,
960 length(l_parameter_rec.parameter_name)-length('_TO')
961 );
962 end if;
963 l_time_to_value := l_parameter_rec.parameter_value;
964 l_time_to_description := l_parameter_rec.parameter_description;
965 end if;
966 if l_time_dimension is null or length(l_time_dimension) = 0 then
967 l_time_dimension := substr(l_time_attribute2, 1, instr(l_time_attribute2,'+')-1);
968 l_time_dimension_level := substr(l_time_attribute2,instr(l_time_attribute2,'+')+1);
969 end if;
970 end if;
971 --OLTP org info
972 if l_parameter_rec.dimension = 'ORGANIZATION' then
973 l_OLTP_org_level := substr(l_parameter_rec.parameter_name,
974 instr(l_parameter_rec.parameter_name,'+')+1);
975 l_OLTP_org_value := l_parameter_rec.parameter_value;
976 l_OLTP_flag := true;
977 end if;
978 end loop;
979 end if;
980 -- If the sql is given by the product teams do not bother to construct the sql
981 -- just replace the variables with their values and return as is.
982 /*
983 if (p_region_code = 'OPI_POR_COGS_COMP3') then
984 l_ak_region_Rec.plsql_function := 'OPI_POR_MARGIN_REP_VIEW_PKG.GET_SQL';
985 end if;
986 if (p_region_code = 'OPI_POR_COGS_GRAPH_COMP3') then
987 l_ak_Region_rec.plsql_function := 'OPI_POR_MARGIN_REP_VIEW_PKG.GET_GRAPH_SQL';
988 end if;
989 if (p_region_code = 'FII_FACT1') then
990 l_ak_Region_rec.plsql_function := 'FII_MGR_PROTOTYPE.GET_MANAGER';
991 end if;
992 if (p_region_code = 'FII_FACT2') then
993 l_ak_Region_rec.plsql_function := 'FII_MGR_PROTOTYPE.GET_LOB';
994 end if;
995 if (p_region_code = 'FII_FACT2') then
996 l_ak_Region_rec.plsql_function := 'FII_MGR_PROTOTYPE.GET_LOB';
997 end if;
998 */
999 if (l_ak_region_rec.plsql_function is not null) then
1000 get_custom_sql(p_Source => p_Source,
1001 pAKRegionRec => l_ak_region_rec,
1002 pParametertbl => l_parameter_tbl,
1003 pUserSession => l_user_session_rec,
1004 p_sort_attribute => p_sort_attribute,
1005 p_sort_direction => p_sort_direction,
1006 p_viewby_attribute2 => l_viewby_attribute2,
1007 p_viewby_dimension => l_viewby_dimension,
1008 p_viewby_dimension_level => l_viewby_dimension_level,
1009 p_lower_bound => p_lower_bound,
1010 p_upper_bound => p_upper_bound,
1011 x_sql_string => x_sql,
1012 x_bind_variables => x_bind_variables,
1013 x_plsql_bind_variables => x_plsql_bind_variables,
1014 x_bind_indexes => x_bind_indexes,
1015 x_bind_datatypes => x_bind_datatypes,
1016 x_return_status => l_return_Status,
1017 x_msg_data => l_msg_data,
1018 x_msg_count => l_msg_count,
1019 x_view_by_value => x_view_by_value
1020 );
1021 x_target_alias := '';
1022 x_has_target := 'N';
1023 x_viewby_table := '';
1024
1025 return;
1026 end if;
1027
1028
1029 --get report info from ak_region_items_vl
1030 l_item_count := 1;
1031 if ak_region_item_cursor%ISOPEN then
1032 close ak_region_item_cursor;
1033 end if;
1034 --Get the base column and their aggregation function. We decided to use another query as this
1035 --might be faster than us having to manipulate the PL/SQL table to get rid of the duplicates.
1036 --Use a table of records instead
1037 if (base_col_cursor%ISOPEN) then
1038 close base_Col_cursor;
1039 end if;
1040 open base_col_cursor;
1041 fetch base_col_cursor bulk collect into l_base_Column_tbl, l_aggregation_tbl;
1042 close base_col_cursor;
1043
1044 -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
1045 l_nls_sort_type := fnd_profile.value('ICX_NLS_SORT');
1046
1047 open ak_region_item_cursor(l_user_session_rec.region_code);
1048 loop
1049 fetch ak_region_item_cursor into l_ak_region_item_rec;
1050 exit when ak_region_item_cursor%NOTFOUND;
1051 /*l_ak_region_item_tbl(l_ak_count) := l_ak_region_item_rec;
1052 if (substr(l_ak_region_item_rec.base_column,1,1) <> '''' or
1053 substr(l_ak_region_item_rec.base_column,1,1) <> '"' ) then
1054 l_base_column_tbl(l_item_count) := l_ak_region_item_rec.base_column;
1055 l_item_count := l_item_count+1;
1056 end if;
1057 l_ak_count := l_ak_count+1;
1058 end loop;
1059 close ak_region_item_cursor;
1060 if (l_ak_region_item_tbl.COUNT > 0) THEN
1061 for i in l_ak_Region_item_tbl.FIRST..l_ak_region_item_tbl.LAST loop
1062 l_ak_region_item_rec := l_ak_region_item_tbl(i);*/
1063 if (p_Source = 'ACTUAL') then
1064 l_ak_region_rec.disable_viewby := 'Y';
1065
1066 --jprabhud - 02/25/03 - Bug 2806218
1067 elsif(p_Source = 'ACTUAL_FOR_KPI' ) then
1068 if (l_viewby_dimension_level is null OR l_viewby_dimension_level ='' OR l_viewby_dimension_level='''''') then
1069 l_ak_region_rec.disable_viewby := 'Y';
1070 end if;
1071
1072 end if;
1073
1074 --set up time info
1075 if l_ak_region_item_rec.attribute2 = l_time_attribute2 then
1076 if l_viewby_attribute2 = l_time_attribute2 then
1077 l_share_vbt_table := true;
1078 l_TM_alias := 'VBT';
1079 else
1080 l_share_vbt_table := false;
1081 l_TM_alias := 'TM';
1082 l_time_table := l_ak_region_item_rec.lov_table;
1083 if l_time_table is null or length(l_time_table) = 0 then
1084 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING
1085 (p_DimLevelShortName => l_time_dimension_level,
1086 p_bis_source => l_report_type,
1087 x_select_string => l_select_string,
1088 x_table_name => l_time_table,
1089 x_id_name => l_time_id_name,
1090 x_value_name => l_time_value_name,
1091 x_return_status => l_return_status,
1092 x_msg_count => l_msg_count,
1093 x_msg_data => l_msg_data);
1094 else
1095 l_time_id_name := 'ID';
1096 l_time_value_name := 'VALUE';
1097 end if;
1098 end if;
1099 end if; -- end of set up time info
1100
1101 --Get the type for the sort attribute code
1102 if (p_Sort_Attribute is not null and p_Sort_Attribute=l_ak_region_item_Rec.attribute_code) then
1103 l_sort_attr_type := l_ak_region_item_rec.data_type;
1104 end if;
1105 --set up view by info
1106 if l_ak_region_item_rec.attribute2 = l_viewby_attribute2 then
1107 l_viewby_attribute_code := l_ak_region_item_rec.attribute_code;
1108 l_viewby_base_column := l_ak_region_item_rec.base_column;
1109 l_viewby_table := l_ak_region_item_rec.lov_table;
1110 l_viewby_datatype := l_ak_region_item_rec.data_Type;
1111 if l_viewby_table is null or length(l_viewby_table) = 0 then
1112 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING
1113 (p_DimLevelShortName => l_viewby_dimension_level,
1114 p_bis_source => l_report_type,
1115 x_select_string => l_select_string,
1116 x_table_name => l_viewby_table,
1117 x_id_name => l_viewby_id_name,
1118 x_value_name => l_viewby_value_name,
1119 x_return_status => l_return_status,
1120 x_msg_count => l_msg_count,
1121 x_msg_data => l_msg_data);
1122 else
1123 l_viewby_id_name := 'ID';
1124 l_viewby_value_name := 'VALUE';
1125 end if;
1126
1127 --get info from bis_ak_region_item_extension
1128 if ak_region_item_ext_cursor%ISOPEN then
1129 close ak_region_item_ext_cursor;
1130 end if;
1131 open ak_region_item_ext_cursor(l_user_session_rec.region_code, l_viewby_attribute_code);
1132 fetch ak_region_item_ext_cursor into l_ak_region_item_ext_rec;
1133 close ak_region_item_ext_cursor;
1134
1135 if l_ak_region_item_ext_rec.extra_groupby is not null then
1136 l_extra_groupby_label := substr(l_ak_region_item_ext_rec.extra_groupby, 1,
1137 instr(l_ak_region_item_ext_rec.extra_groupby, '=')-1);
1138 l_extra_groupby_name := rtrim(ltrim(substr(l_ak_region_item_ext_rec.extra_groupby,
1139 instr(l_ak_region_item_ext_rec.extra_groupby, '=')+1)));
1140 end if;
1141
1142 --construct viewby select string
1143 if nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y' then
1144 if (l_viewby_datatype = 'D') then
1145 l_viewby_select := 'to_char';
1146 end if;
1147 l_viewby_select := l_viewby_select || '(VBT.' || l_viewby_value_name || ') "VIEWBY", ';
1148 if (p_source = 'ACTUAL_FOR_KPI') then
1149 l_viewby_select := l_viewby_select || ' (VBT.' || l_viewby_id_name ||') "VIEWBYID", ';
1150 end if;
1151 l_viewbyat := 'Y';
1152 if gvCode is not null and length(gvCode) > 0 then
1153 BIS_PMV_QUERY_PVT.get_customized_order_by(p_viewby =>l_viewbyat,
1154 p_attribute_code =>l_viewby_attribute_code,
1155 p_region_code => p_region_code,
1156 p_user_id => p_user_id,
1157 p_customization_code =>gvCode,
1158 p_main_order_by => l_main_order_by,
1159 p_first_order_by => l_first_order_by,
1160 p_second_order_by => l_second_order_by);
1161 end if;
1162 if l_extra_groupby_name is not null then
1163 l_viewby_select := l_viewby_select || 'SV.' || l_extra_groupby_name || ' EXTRAVIEWBY, ';
1164 end if;
1165 end if;
1166
1167 end if; --end of set up view by info
1168 --Set up the order by info
1169 if (l_ak_region_item_rec.order_sequence is not null and
1170 l_ak_region_item_rec.order_Sequence < 100) and
1171 l_ak_region_item_rec.node_query_flag = 'N' then
1172 -- nvl(l_ak_region_rec.disable_viewby,'N') = 'Y' then
1173 IF (l_ak_region_item_rec.data_type = 'C') THEN -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
1174 IF l_nls_sort_type IS NOT NULL THEN
1175 l_sort_attr_code := ' NLSSORT('||l_ak_region_item_rec.attribute_code||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
1176 ELSE
1177 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
1178 END IF;
1179 ELSIF (l_ak_region_item_rec.data_type = 'D') THEN
1180 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
1181 ELSE
1182 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
1183 END IF;
1184 l_def_sort_attr_tbl(l_def_sort_count) := l_sort_attr_code||' '||
1185 l_ak_region_item_rec.order_direction;
1186 l_Def_sort_seq_tbl(l_def_sort_count) := l_ak_region_item_rec.order_sequence;
1187 l_def_sort_count := l_def_sort_count+1;
1188
1189 end if;
1190 if (l_first_time) then
1191 if nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y' then
1192 l_first_attr_code := 'VIEWBY';
1193 l_first_time := false;
1194 else
1195 -- if (l_ak_Region_item_rec.node_query_flag = 'N') then
1196 if (l_ak_Region_item_rec.node_query_flag = 'N' and l_ak_Region_item_rec.node_display_flag = 'Y') then
1197 if (l_ak_region_item_rec.data_type = 'D') then
1198 l_first_attr_code := l_ak_region_item_rec.attribute_code;
1199 ELSIF (l_ak_region_item_rec.data_type = 'C') THEN -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
1200 IF l_nls_sort_type IS NOT NULL THEN
1201 l_first_attr_code := ' NLSSORT('||l_ak_region_item_rec.attribute_code||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
1202 ELSE
1203 l_first_attr_code := l_ak_region_item_rec.attribute_code;
1204 END IF;
1205 else
1206 l_first_attr_code := l_ak_region_item_rec.attribute_code;
1207 end if;
1208 l_first_time := false;
1209 end if;
1210 end if;
1211 end if;
1212
1213 --set up table columns info
1214 if (l_ak_region_item_rec.attribute_type = 'MEASURE'
1215 or l_ak_region_item_rec.attribute_type = 'MEASURE_NOTARGET'
1216 or (l_ak_region_item_rec.attribute_type is null and l_ak_region_item_rec.node_query_flag = 'N')) then
1217
1218 if (substr(l_ak_region_item_rec.base_column, 1, 1) = ''''
1219 or substr(l_ak_region_item_rec.base_column, 1, 1) = '"') then
1220 --if instrb(l_ak_region_item_rec.base_column, '/') <= 0 then
1221 l_column_select := l_column_select || BIS_PMV_QUERY_PVT.GET_CALCULATE_SELECT(l_ak_region_item_rec, l_parameter_tbl, l_base_column_tbl,l_aggregation_tbl);
1222 --end if;
1223 else
1224 l_column_select := l_column_select || BIS_PMV_QUERY_PVT.GET_NORMAL_SELECT(l_ak_region_item_rec);
1225 end if;
1226 l_viewbyat := 'N';
1227 if gvCode is not null and length(gvCode) > 0 then
1228 BIS_PMV_QUERY_PVT.get_customized_order_by(p_viewby =>l_viewbyat,
1229 p_attribute_code =>l_ak_region_item_rec.attribute_code,
1230 p_region_code => p_region_code,
1231 p_user_id => p_user_id,
1232 p_customization_code =>gvCode,
1233 p_main_order_by => l_main_order_by,
1234 p_first_order_by => l_first_order_by,
1235 p_second_order_by => l_second_order_by);
1236 end if;
1237
1238 if l_ak_region_item_rec.attribute_type = 'MEASURE' and p_source not in ('ACTUAL','ACTUAL_FOR_KPI') then
1239 BIS_PMV_QUERY_PVT.GET_TARGET_SELECT
1240 (p_user_session_rec => l_user_session_rec,
1241 p_ak_region_item_rec => l_ak_region_item_rec,
1242 p_parameter_tbl => l_parameter_tbl,
1243 p_report_type => l_report_type,
1244 p_plan_id => l_plan_id,
1245 p_viewby_dimension => l_viewby_dimension,
1246 p_viewby_attribute2 => l_viewby_attribute2,
1247 p_viewby_id_name => l_viewby_id_name,
1248 p_time_from_description => l_time_from_description,
1249 p_time_to_description => l_time_to_description,
1250 x_target_select => l_target_select,
1251 x_no_target => l_no_target,
1252 x_bind_variables => x_bind_variables,
1253 --x_bind_indexes => x_bind_indexes,
1254 x_bind_count => l_bind_count);
1255
1256 if l_target_select is not null and length(l_target_select) > 0 then
1257 if length(l_ak_region_item_rec.attribute_code) > 23 then
1258 l_target_alias_name := 'target'||l_target_count;
1259 l_target_count := l_target_count + 1;
1260 if l_target_alias is not null and length(l_target_alias) > 0 then
1261 l_target_alias := l_target_alias || l_target_alias_group_seperator;
1262 end if;
1263 l_target_alias := l_target_alias || l_target_alias_name || l_target_alias_name_seperator
1264 || l_ak_region_item_rec.attribute_code;
1265 else
1266 l_target_alias_name := l_ak_region_item_rec.attribute_code || '_TARGET';
1267 end if;
1268 l_target_select := l_target_select || ' "'|| l_target_alias_name || '", ';
1269 end if;
1270
1271 l_column_select := l_column_select || l_target_select;
1272 end if;
1273
1274 end if; --end of set up table columns info
1275
1276 end loop;
1277 --end if;
1278 --close ak_region_item_cursor;
1279
1280 if l_share_vbt_table then
1281 l_time_id_name := l_viewby_id_name;
1282 l_time_value_name := l_viewby_value_name;
1283 end if;
1284
1285 x_target_alias := l_target_alias;
1286
1287 --set HR flag
1288 if substr(l_OLTP_org_level,1,2) = 'HR' or substr(l_time_dimension_level,1,2) = 'HR' then
1289 l_HR_report := true;
1290 end if;
1291
1292 --get user group by and user order by
1293 if l_ak_region_rec.user_groupby is not null and length(l_ak_region_rec.user_groupby) > 0 then
1294 l_user_groupby := BIS_PMV_QUERY_PVT.GET_USER_STRING(l_ak_region_rec.user_groupby);
1295 end if;
1296
1297 if l_ak_region_rec.user_orderby is not null and length(l_ak_region_rec.user_orderby) > 0 then
1298 l_user_orderby := BIS_PMV_QUERY_PVT.GET_USER_STRING(l_ak_region_rec.user_orderby);
1299 end if;
1300
1301 --construct select string
1302 l_select := l_select || l_viewby_select || l_column_select;
1303
1304 if substr(l_select, length(l_select)-1) = ', ' then
1305 l_select := substr(l_select, 1, length(l_select)-2);
1306 end if;
1307
1308 if l_ak_region_rec.user_groupby is not null and length(l_ak_region_rec.user_groupby) > 0 then
1309 l_select := l_select || ', ' || l_user_groupby || ' ';
1310 end if;
1311
1312 if l_ak_region_rec.user_orderby is not null and length(l_ak_region_rec.user_orderby) > 0 then
1313 l_select := l_select || ', ' || l_user_orderby || ' ';
1314 end if;
1315
1316 --construct from string
1317 l_from := l_from || l_ak_region_rec.source_view || ' SV';
1318 if nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y' then
1319 l_from := l_from || ', ' || l_viewby_table || ' VBT';
1320 end if;
1321
1322 if not(l_share_vbt_table) and l_time_table is not null and length(l_time_table) > 0
1323 and ((l_time_from_value is not null and length(l_time_from_value) > 0)
1324 or (l_time_to_value is not null and length(l_time_to_value) > 0)
1325 or (l_report_type <> 'EDW' and not (l_HR_report))) then
1326 if (l_OLTP_flag)
1327 or nvl(l_time_to_value, 'All') <> 'All'
1328 or nvl(l_time_from_value,'All') <> 'All' then
1329 l_from := l_from || ', ' || l_time_table || ' TM';
1330 end if;
1331 end if;
1332
1333 --construct where string
1334 if nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y' then
1335 l_where := l_where || ' SV.' || l_viewby_base_column || '= VBT.' || l_viewby_id_name || ' ';
1336 if l_report_type <> 'EDW' and l_viewby_dimension = 'ORGANIZATION'
1337 and l_viewby_dimension_level in ('LEGAL ENTITY','OPERATING UNIT','HR ORGANIZATION','ORGANIZATION',
1338 'SET OF BOOKS','BUSINESS GROUP','HRI_ORG_HRCY_BX','HRI_ORG_HRCYVRSN_BX','HRI_ORG_HR_HX','HRI_ORG_INHV_H',
1339 'HRI_ORG_SSUP_H','HRI_ORG_BGR_HX','HRI_ORG_HR_H','HRI_ORG_SRHL') then
1340 x_bind_variables := x_bind_variables || SEPERATOR||l_user_session_rec.responsibility_id ;
1341 l_bind_count := l_bind_count +1;
1342 --x_bind_indexes := x_bind_indexes || SEPERATOR|| l_bind_count;
1343 l_where := l_where || 'and VBT.responsibility_id = :'||l_bind_count;
1344 --l_where := l_where || 'and VBT.responsibility_id = '|| l_user_session_rec.responsibility_id || ' ';
1345 end if;
1346 else
1347 l_where := l_where || ' 1=1 ';
1348 end if;
1349
1350 if l_parameter_tbl.COUNT > 0 then
1351 for i in l_parameter_tbl.FIRST..l_parameter_tbl.LAST loop
1352 l_parameter_rec := l_parameter_tbl(i);
1353 if (l_parameter_rec.parameter_name <> 'VIEW_BY' AND
1354 l_parameter_rec.parameter_name <> 'BUSINESS_PLAN' AND
1355 substr(l_parameter_rec.parameter_name, length(l_parameter_rec.parameter_name)-length
1356 ('_HIERARCHY')+1) <> '_HIERARCHY') THEN
1357 --construct where string
1358 if save_parameter_cursor%ISOPEN then
1359 close save_parameter_cursor;
1360 end if;
1361 l_save_region_item_rec := NULL;
1362 open save_parameter_cursor(l_user_session_rec.region_code, l_parameter_rec.parameter_name);
1363 fetch save_parameter_cursor into l_save_region_item_rec;
1364 close save_parameter_cursor;
1365 -- continue only if there is a match
1366 IF (l_save_region_item_rec.base_column IS NOT NULL) THEN
1367 if (substr(l_parameter_rec.parameter_name,
1368 length(l_parameter_rec.parameter_name)-length('_HIERARCHY')+1) <> '_HIERARCHY') THEN
1369
1370 if l_parameter_rec.dimension is not null and length(l_parameter_rec.dimension) > 0 then
1371 --serao - if rolling dimension - just append the base column name to the param value
1372 IF (l_parameter_rec.parameter_description = BIS_PMV_PARAMETERS_PVT.ROLLING_DIMENSION_DESCRIPTION AND l_parameter_rec.parameter_value is not null) THEN
1373 l_where := l_where || ' and SV.'||l_save_region_item_rec.base_column || ' '||l_parameter_rec.parameter_value||' ';
1374 ELSE
1375 --Fix for 2435613 and 2435528
1376 if l_parameter_rec.dimension in ('TIME', 'EDW_TIME_M') then
1377 -- Fix for bug 2763337
1378 -- Introduce checks similar to the ones for constructing
1379 -- the TM from clause
1380 -- Note however there is a slight difference - the call
1381 -- to get_time_where also has to deal with l_tm_alias 'VBT'
1382 if (
1383 (not(l_share_vbt_table)
1384 and l_time_table is not null and length(l_time_table) > 0
1385 and ((l_time_from_value is not null
1386 and length(l_time_from_value) > 0)
1387 or (l_time_to_value is not null
1388 and length(l_time_to_value) > 0)
1389 or (l_report_type <> 'EDW'
1390 and not (l_HR_report))))
1391 or
1392 l_tm_alias = 'VBT'
1393 )
1394 then
1395
1396 if (l_OLTP_flag)
1397 or nvl(l_time_to_value, 'All') <> 'All'
1398 or nvl(l_time_from_value,'All') <> 'All' then
1399 l_where := l_where
1400 || BIS_PMV_QUERY_PVT.GET_TIME_WHERE
1401 (p_parameter_rec => l_parameter_rec,
1402 p_save_region_item_rec => l_save_region_item_rec,
1403 p_ak_region_rec => l_ak_region_rec,
1404 p_org_dimension_level => l_OLTP_org_level,
1405 p_org_dimension_level_value => l_OLTP_org_value,
1406 p_viewby_dimension => l_viewby_dimension,
1407 p_time_id_name => l_time_id_name,
1408 p_time_value_name => l_time_value_name,
1409 p_region_code => l_user_session_rec.region_code,
1410 p_TM_alias => l_TM_alias,
1411 x_bind_variables => x_bind_variables,
1412 --x_bind_indexes => x_bind_indexes,
1413 x_bind_count => l_bind_count);
1414 end if;
1415 end if ; -- if not(l_share_vbt_table)
1416 else
1417 l_where := l_where
1418 || BIS_PMV_QUERY_PVT.GET_NON_TIME_WHERE(l_parameter_rec,l_save_region_item_rec
1419 ,null,x_bind_variables,l_bind_count);
1420 end if;
1421 END IF ; -- not rolling dimension
1422 else
1423 l_where := l_where ||
1424 BIS_PMV_QUERY_PVT.GET_NON_DIMENSION_WHERE(l_parameter_rec,l_save_region_item_rec,
1425 x_bind_variables,l_bind_count);
1426 end if;
1427 end if;
1428
1429 if l_save_region_item_rec.where_clause is not null and length(l_save_region_item_rec.where_clause) > 0 then
1430 if nvl(l_ak_region_rec.disable_viewby,'N') = 'Y' or
1431 (nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y' and l_viewby_attribute2 = l_parameter_rec.parameter_name) then
1432 l_where := l_where || BIS_PMV_QUERY_PVT.GET_LOV_WHERE(l_parameter_tbl,
1433 l_save_region_item_rec.where_clause,
1434 l_user_session_rec.region_code
1435 );
1436
1437 end if;
1438 end if;
1439
1440 END IF ; -- if l_save_region_item_rec.base column
1441 end if;
1442 end loop;
1443 end if;
1444
1445
1446 IF l_ak_region_rec.where_clause IS NOT NULL THEN
1447 l_custom_where := l_ak_region_rec.where_clause;
1448 replace_custom_sql( p_user_session_rec => l_user_session_rec,
1449 pParameterTbl => l_parameter_tbl ,
1450 p_original_sql => l_custom_where,
1451 p_where => l_custom_where,
1452 x_bind_variables => x_bind_variables,
1453 x_plsql_bind_variables => x_plsql_bind_variables,
1454 x_bind_indexes => x_bind_indexes,
1455 x_bind_datatypes => x_bind_datatypes,
1456 x_bind_count => l_bind_count,
1457 x_return_status => l_return_status,
1458 x_msg_count => l_msg_count,
1459 x_msg_data => l_msg_data
1460 );
1461
1462 -- Fix for bug 2763337
1463 -- The Region-level where clause needs to have a leading space embedded
1464 -- l_where := l_where || l_custom_where;
1465 l_where := l_where || ' ' || l_custom_where;
1466 END IF;
1467
1468 --construct group by string
1469 if (p_source = 'ACTUAL_FOR_KPI') then
1470 l_no_target := true;
1471 end if;
1472 l_group_by := BIS_PMV_QUERY_PVT.GET_GROUP_BY
1473 (p_disable_viewby => nvl(l_ak_region_rec.disable_viewby,'N'),
1474 p_viewby_id_name => l_viewby_id_name,
1475 p_viewby_value_name => l_viewby_value_name,
1476 p_viewby_dimension => l_viewby_dimension,
1477 p_viewby_dimension_level => l_viewby_dimension_level,
1478 p_extra_groupby => l_extra_groupby_name,
1479 p_user_groupby => l_user_groupby,
1480 p_user_orderby => l_user_orderby,
1481 p_no_target => l_no_target);
1482 if l_group_by is not null and length(l_group_by) > 0 then
1483 l_group_by := ' GROUP BY ' || l_group_by;
1484 end if;
1485 if (p_source = 'ACTUAL_FOR_KPI') and
1486 ( nvl(l_ak_region_rec.disable_viewby,'N') <> 'Y') then
1487 l_group_by := l_group_by || ' ,VBT.'|| l_viewby_id_name ||' ';
1488 end if;
1489
1490 if (l_def_sort_attr_tbl.COUNT > 0) THEN
1491 --Sort the specified order
1492 if (l_def_sort_attr_tbl.COUNT > 1) then
1493 BIS_PMV_QUERY_PVT.sort(l_def_sort_seq_tbl, l_def_sort_attr_tbl);
1494 end if;
1495 for i in l_def_sort_seq_tbl.FIRST..l_def_sort_seq_tbl.LAST loop
1496 l_default_sort_attribute := ' '|| l_default_sort_attribute || l_def_sort_attr_tbl(i)||',';
1497 end loop;
1498 l_default_sort_attribute := substr(l_default_sort_attribute, 1, length(l_default_sort_attribute)-1);
1499 else
1500 l_default_sort_attribute := l_first_attr_code;
1501 end if;
1502
1503 --construct order by string
1504 --if (p_sort_attribute is not null and length(p_sort_attribute) >0 ) then
1505 if (p_source <> 'ACTUAL') then
1506 IF (l_sort_attr_type = 'C' and p_sort_attribute IS NOT NULL) THEN -- nbarik 27-AUG-2002 NLS Sort for VARCHAR2
1507 IF l_nls_sort_type IS NOT NULL THEN
1508 l_sel_sort_attribute := ' NLSSORT('||p_sort_Attribute||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
1509 ELSE
1510 l_sel_sort_attribute := p_sort_attribute;
1511 END IF;
1512 ELSIF (l_sort_attr_type = 'D' and p_sort_attribute is not null) then
1513 l_sel_sort_attribute := p_sort_Attribute;
1514 else
1515 l_sel_sort_attribute := p_sort_attribute;
1516 end if;
1517 l_order_by := ' ORDER BY ';
1518 l_order_by := l_order_by
1519 || BIS_PMV_QUERY_PVT.GET_ORDER_BY
1520 (p_disable_viewby => nvl(l_ak_region_rec.disable_viewby,'N'),
1521 p_sort_attribute => l_sel_sort_attribute,
1522 p_sort_direction => p_sort_direction,
1523 p_viewby_dimension => l_viewby_dimension,
1524 p_viewby_dimension_level => l_viewby_dimension_level,
1525 p_default_sort_attribute => l_default_sort_attribute,
1526 p_user_orderby => l_user_orderby);
1527 --end if;
1528 end if;
1529
1530 if l_main_order_by is not null then
1531 l_main_order_by := ' order by '||l_main_order_by||l_first_order_by||l_second_order_by;
1532 x_sql := l_select || l_from || l_where || l_group_by || l_main_order_by;
1533 else
1534 x_sql := l_select || l_from || l_where || l_group_by || l_order_by;
1535 end if;
1536
1537 if (l_no_target) then
1538 x_has_target := 'N';
1539 else
1540 x_has_target := 'Y';
1541 end if;
1542 x_viewby_table := l_viewby_table;
1543 x_plsql_bind_variables := x_bind_variables;
1544 end getQuerySQL;
1545
1546 procedure getQuery(p_region_code in VARCHAR2,
1547 p_function_name in VARCHAR2,
1548 p_user_id in VARCHAR2,
1549 p_session_id in VARCHAR2,
1550 p_resp_id in VARCHAR2,
1551 p_page_id in VARCHAR2 DEFAULT NULL,
1552 p_schedule_id in VARCHAR2 DEFAULT NULL,
1553 p_sort_attribute in VARCHAR2 DEFAULT NULL,
1554 p_sort_direction in VARCHAR2 DEFAULT NULL,
1555 p_source in varchar2 DEFAULT 'REPORT',
1556 p_customization_code in varchar2 DEFAULT NULL,
1557 p_lower_bound IN INTEGER DEFAULT 1,
1558 p_upper_bound IN INTEGER DEFAULT -1,
1559 x_sql out NOCOPY VARCHAR2,
1560 x_target_alias out NOCOPY VARCHAR2,
1561 x_has_target out NOCOPY varchar2,
1562 x_viewby_table out NOCOPY varchar2,
1563 x_return_status out NOCOPY VARCHAR2,
1564 x_msg_count out NOCOPY NUMBER,
1565 x_msg_data out NOCOPY VARCHAR2,
1566 x_bind_variables out NOCOPY VARCHAR2,
1567 x_plsql_bind_variables out NOCOPY VARCHAR2,
1568 x_bind_indexes out NOCOPY VARCHAR2,
1569 x_bind_datatypes OUT NOCOPY VARCHAR2,
1570 x_view_by_value OUT NOCOPY VARCHAR2) is
1571
1572 begin
1573
1574 gvCode := p_customization_code;
1575 getQuerySQL(p_region_code => p_region_code,
1576 p_function_name => p_function_name,
1577 p_user_id => p_user_id,
1578 p_session_id => p_session_id,
1579 p_resp_id => p_resp_id,
1580 p_page_id => p_page_id,
1581 p_schedule_id => p_schedule_id,
1582 p_sort_attribute => p_sort_attribute,
1583 p_sort_direction => p_sort_direction,
1584 p_source => p_source,
1585 p_lower_bound => p_lower_bound,
1586 p_upper_bound => p_upper_bound,
1587 x_sql => x_sql,
1588 x_target_alias => x_target_alias,
1589 x_has_target => x_has_target,
1590 x_viewby_table => x_viewby_table,
1591 x_return_status => x_return_status,
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 x_bind_variables => x_bind_variables,
1595 x_plsql_bind_variables => x_plsql_bind_variables,
1596 x_bind_indexes => x_bind_indexes,
1597 x_bind_datatypes => x_bind_datatypes,
1598 x_view_by_value => x_view_by_value );
1599 end getQuery;
1600
1601 function GET_NORMAL_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC)
1602 return varchar2 is
1603 l_select_string varchar2(2000);
1604 begin
1605 l_select_string := p_ak_region_item_rec.aggregate_function || '('
1606 || BIS_PMV_QUERY_PVT.APPLY_DATA_FORMAT(p_ak_region_item_rec) || ') "'
1607 || p_ak_region_item_rec.attribute_code || '", ';
1608 return l_select_string;
1609 end GET_NORMAL_SELECT;
1610
1611 function APPLY_DATA_FORMAT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC)
1612 return varchar2 is
1613 l_format_string varchar2(2000);
1614 l_default_date_format VARCHAR2(15) := 'DD-MON-RR';
1615 begin
1616 if p_ak_region_item_rec.data_type = 'D' then
1617 --Bug Fix 1917856 Don't do the date formatting here, it will be done in Java files
1618 l_format_string := 'TO_CHAR(SV.'||p_ak_region_item_rec.base_column||','''|| l_default_date_format||''') ';
1619 /*
1620 if (p_ak_region_item_rec.data_format is null or length(p_ak_region_item_rec.data_format) = 0) then
1621 l_format_string := ' to_char(SV.'||p_ak_region_item_rec.base_column||' )';
1622 else
1623 l_format_string := 'TO_CHAR(SV.'||p_ak_region_item_rec.base_column||','''||
1624 p_ak_region_item_rec.data_format||''') ';
1625 end if;
1626 */
1627 else
1628 l_format_string := ' SV.'||p_ak_region_item_rec.base_column||' ';
1629 end if;
1630 return l_format_string;
1631 end APPLY_DATA_FORMAT;
1632
1633 function GET_CALCULATE_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC
1634 ,p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE
1635 ,p_base_column_tbl in out NOCOPY BISVIEWER.t_char
1636 ,p_aggregation_tbl in out NOCOPY BISVIEWER.t_char)
1637 return varchar2 is
1638 l_calculate_select varchar2(2000);
1639 begin
1640 if substr(p_ak_region_item_rec.base_column, 1, 1) = '''' then
1641 l_calculate_select := substr(p_ak_region_item_rec.base_column,2,length(p_ak_region_item_rec.base_column)-2)
1642 || ' "'||p_ak_region_item_rec.attribute_code||'", ';
1643 end if;
1644 if substr(p_ak_region_item_rec.base_column, 1, 1) = '"'
1645 and instrb(p_ak_region_item_rec.base_column, '/') <= 0 then
1646 l_calculate_select := BIS_PMV_QUERY_PVT.REPLACE_FORMULA(p_ak_region_item_rec,
1647 p_parameter_tbl,
1648 p_base_column_tbl,
1649 p_aggregation_Tbl)
1650 || ' "'||p_ak_region_item_rec.attribute_code||'", ';
1651 end if;
1652 return l_calculate_select;
1653 end GET_CALCULATE_SELECT;
1654
1655 --not implemented yet!!
1656 function REPLACE_FORMULA(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC
1657 ,p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE
1658 ,p_base_column_tbl in out NOCOPY BISVIEWER.t_char
1659 ,p_aggregation_tbl in out NOCOPY BISVIEWER.t_char)
1660 return varchar2 is
1661 l_formula varchar2(2000);
1662 l_param_name_tbl BISVIEWER.t_char;
1663 l_param_value_tbl BISVIEWER.t_char;
1664 l_return_Status VARCHAR2(1);
1665 l_msg_count NUMBER;
1666 l_msg_data VARCHAR2(2000);
1667 l_base_column VARCHAR2(150);
1668 x number; --added
1669 begin
1670 --Sort the parameters in the decreasing order of their length and then substitute them
1671 -- in the formula. This is for conversion attributes and stuff...
1672 x := p_parameter_tbl.COUNT;
1673 IF p_parameter_tbl.COUNT > 0 THEN
1674 for i in p_parameter_tbl.FIRST..p_parameter_tbl.LAST LOOP
1675 l_param_name_tbl(i) := p_parameter_tbl(i).parameter_name;
1676 l_param_value_tbl(i) := p_parameter_tbl(i).parameter_description;
1677 end loop;
1678 END IF;
1679 x := l_param_value_tbl.count;
1680 --Now sort these parameters
1681 if x>0 then
1682 BIS_PMV_UTIL.sortAttributeCode
1683 (p_attributecode_tbl => l_param_name_tbl
1684 ,p_attributevalue_tbl => l_param_value_tbl
1685 ,x_return_status => l_return_status
1686 ,x_msg_count => l_msg_count
1687 ,x_msg_data => l_msg_data
1688 );
1689 end if;
1690 l_base_column := p_ak_region_item_rec.base_column;
1691 IF (l_param_name_tbl.COUNT > 0) THEN
1692 FOR i in l_param_name_tbl.FIRST..l_param_name_tbl.LAST loop
1693 if (instrb(p_ak_region_item_rec.base_column, l_param_name_tbl(i)) > 0) THEN
1694 l_base_column := replace(l_base_column, l_param_name_tbl(i), l_param_value_tbl(i));
1695 end if;
1696 end loop;
1697 end if;
1698 l_formula := substr(l_base_column,2,length(ltrim(rtrim(l_base_column)))-2);
1699 --Now append the aggregation function to each base column.
1700 x := p_aggregation_tbl.COUNT;
1701 if x > 0 then
1702 BIS_PMV_UTIL.sortAttributeCode
1703 (p_attributecode_tbl => p_base_column_tbl
1704 ,p_attributevalue_tbl => p_aggregation_tbl
1705 ,x_return_Status => l_return_status
1706 ,x_msg_count => l_msg_count
1707 ,x_msg_data => l_msg_data
1708 );
1709 end if;
1710 /*for i in p_base_column_tbl.FIRST..p_base_column_tbl.LAST
1711 loop
1712 l_base_column := p_base_column_tbl(i);
1713 end loop;*/
1714 if (p_base_column_tbl.COUNT > 0) then
1715 FOR i in p_base_column_tbl.FIRST..p_base_column_tbl.LAST LOOP
1716 if (instrb(l_formula, p_base_column_tbl(i)) > 0 and
1717 p_base_column_tbl(i) <> l_base_column) THEN
1718 --l_formula := replace(l_formula, p_base_Column_tbl(i), p_aggregation_tbl(i)||'(SV.'||
1719 --p_base_column_tbl(i)||' )');
1720 l_formula := replace(l_formula, p_base_column_tbl(i), ':'||i||':');
1721 end if;
1722 end loop;
1723 for i in p_base_column_tbl.FIRST..p_base_column_tbl.LAST LOOP
1724 l_Formula := replace (l_formula, ':'||i||':', p_aggregation_tbl(i)||'(SV.'||
1725 p_base_column_tbl(i)||' )');
1726 end loop;
1727 end if;
1728 return l_formula;
1729 end REPLACE_FORMULA;
1730
1731 -- added by serao -02/11/02 - orders dimensions one time while constructing the query
1732
1733 PROCEDURE order_Dimensions(
1734 pSource In VARCHAR2,
1735 p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
1736 p_time_to_description in VARCHAR2,
1737 p_time_from_description in VARCHAR2,
1738 p_viewby_id_name IN VARCHAR2,
1739 p_viewby_dimension in VARCHAR2,
1740 p_viewby_attribute2 IN VARCHAR2,
1741 pMeasure_short_name In VARCHAR2,
1742 x_Ordered_Dimension_Select OUT NOCOPY VARCHAR2,
1743 x_target_level_id OUT NOCOPY NUMBER,
1744 x_no_target OUT NOCOPY BOOLEAN,
1745 x_bind_variables In OUT NOCOPY VARCHAR2,
1746 --x_bind_indexes In OUT NOCOPY VARCHAR2,
1747 x_bind_count in out NOCOPY NUMBER
1748 )
1749 IS
1750
1751 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
1752
1753 TYPE Dimension_Array IS TABLE OF VARCHAR2(80);
1754 l_dim_arr Dimension_Array := DImension_Array();
1755 l_dimLevel_arr Dimension_Array := DImension_Array();
1756 l_dimLevelValue_arr Dimension_Array := DImension_Array();
1757
1758
1759 l_Ordered_Dimension_Select VARCHAR2(2000) := '';
1760 l_dimension_level VARCHAR2(80);
1761 l_dimension_level_short_name VARCHAR2(80);
1762 l_dimension_level_id NUMBER;
1763 l_dim_level_rec BIS_DIMENSION_LEVEL_PUB.DIMENSION_LEVEL_REC_TYPE;
1764 l_return_status VARCHAR2(2000);
1765 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1766 l_measure_rec BIS_MEASURE_PUB.MEASURE_REC_TYPE;
1767 l_count NUMBER;
1768 l_no_target boolean := false;
1769
1770 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
1771
1772 l_dim1 VARCHAR2(80);
1773 l_dim2 VARCHAR2(80);
1774 l_dim3 VARCHAR2(80);
1775
1776 lDimension1 VARCHAR2(80);
1777 lDim1Level VARCHAR2(80);
1778 lDim1LevelValue VARCHAR2(80);
1779 lDimension2 VARCHAR2(80);
1780 lDim2Level VARCHAR2(80);
1781 lDim2LevelValue VARCHAR2(80);
1782 lDimension3 VARCHAR2(80);
1783 lDim3Level VARCHAR2(80);
1784 lDim3LevelValue VARCHAR2(80);
1785 lDimension4 VARCHAR2(80);
1786 lDim4Level VARCHAR2(80);
1787 lDim4LevelValue VARCHAR2(80);
1788 lDimension5 VARCHAR2(80);
1789 lDim5Level VARCHAR2(80);
1790 lDim5LevelValue VARCHAR2(80);
1791 lDimension6 VARCHAR2(80);
1792 lDim6Level VARCHAR2(80);
1793 lDim6LevelValue VARCHAR2(80);
1794 lDimension7 VARCHAR2(80);
1795 lDim7Level VARCHAR2(80);
1796 lDim7LevelValue VARCHAR2(80);
1797
1798 lDim1_level_id NUMBER;
1799 lDim2_level_id NUMBER;
1800 lDim3_level_id NUMBER;
1801 lDim4_level_id NUMBER;
1802 lDim5_level_id NUMBER;
1803 lDim6_level_id NUMBER;
1804 lDim7_level_id NUMBER;
1805
1806
1807 CURSOR c_dim_lvl(p_dim_level_short_name in varchar2) IS
1808 SELECT level_id
1809 FROM bis_levels_vl
1810 WHERE short_name=p_dim_level_short_name;
1811
1812
1813 BEGIN
1814
1815 -------------------SETTIN THE TARGET_LEVEL TO GET THE TARGET_LEVEL_ID (from ShNms pvt)--------------------
1816 l_target_level_rec.measure_short_name := pMeasure_short_name;
1817
1818 -------------------------GET THE DIMENSIONS IN THE ORDER THEY ARE ---------------------------
1819 l_count := 0;
1820
1821 if p_parameter_tbl.COUNT > 0 then
1822 for i in p_parameter_tbl.FIRST..p_parameter_tbl.LAST loop
1823 l_parameter_rec := p_parameter_tbl(i);
1824 if (l_parameter_rec.parameter_name <> 'VIEW_BY' AND
1825 l_parameter_rec.parameter_name <> 'BUSINESS_PLAN' AND
1826 substr(l_parameter_rec.parameter_name, length(l_parameter_rec.parameter_name)-9) <> '_HIERARCHY' ) THEN
1827 if l_parameter_rec.dimension is not null and length(l_parameter_rec.dimension) > 0 then
1828 l_dimension_level := substr(l_parameter_rec.parameter_name, instr(l_parameter_rec.parameter_name,'+')+1);
1829 if (l_parameter_rec.dimension in ('TIME', 'EDW_TIME_M')
1830 and nvl(p_time_from_description,'All') <> nvl(p_time_to_description,'All')
1831 and l_parameter_rec.parameter_name not in (p_viewby_attribute2||'_FROM', p_viewby_attribute2||'_TO'))
1832 or (instrb(l_parameter_rec.parameter_value, ''',''') > 0
1833 and l_parameter_rec.parameter_name <> p_viewby_attribute2) then
1834 l_no_target := true;
1835 exit;
1836 end if;
1837 if l_parameter_rec.dimension in ('TIME','EDW_TIME_M') then
1838 if (substr(l_dimension_level,length(l_dimension_level)-2) = '_TO') THEN
1839 goto skip_loop;
1840 else
1841 l_dimension_level := substr(l_dimension_level, 1, length(l_dimension_level)-5);
1842 end if;
1843 end if;
1844 /* l_dimension_select := l_dimension_select || '''' || l_parameter_rec.dimension || ''','''
1845 || l_dimension_level || ''',';*/
1846 l_dim1 := l_parameter_rec.dimension ; -- quote here when appending
1847 l_dim2 := l_dimension_level ; -- quote here
1848 if l_parameter_rec.dimension = p_viewby_dimension then
1849 -- l_dimension_select := l_dimension_select || 'VBT.' || p_viewby_id_name;
1850 l_dim3 := 'VBT.' || p_viewby_id_name;
1851 else
1852 if (substr(l_parameter_rec.parameter_value,1,1)='''') then
1853 --l_Dimension_Select := l_dimension_select || l_parameter_Rec.parameter_value;
1854 l_dim3 := l_parameter_Rec.parameter_value;
1855 else
1856 -- l_dimension_select := l_dimension_select || ''''||l_parameter_rec.parameter_value||'''';
1857 l_dim3 :=''''|| l_parameter_rec.parameter_value||''''; -- quote here
1858 end if;
1859 end if;
1860 --l_dimension_select := l_dimension_select || ',';
1861 l_dim_arr.EXTEND();
1862 l_dim_arr(l_dim_arr.COUNT) := l_dim1;
1863
1864 l_dimlevel_arr.EXTEND();
1865 l_dimlevel_arr(l_dimlevel_arr.COUNT) := l_dim2;
1866
1867 l_dimlevelValue_arr.EXTEND();
1868 l_dimlevelValue_arr(l_dimlevelValue_arr.COUNT) := l_dim3;
1869
1870 l_count := l_count + 1;
1871 end if;
1872 end if;
1873 <<skip_loop>>
1874 null;
1875 end loop;
1876 end if;
1877
1878 x_no_target := l_no_target;
1879
1880 IF NOT(l_no_target) THEN --- do this only if there is a target dimension
1881 ----------------------------------------------------------------------------------------------------------
1882 -- make sure that all the 7 are present cos the code will ask for the 7th element of the array
1883 if l_count < 7 then
1884 for i in l_count+1..7 loop
1885 l_dim_arr.EXTEND();
1886 l_dim_arr(l_dim_arr.COUNT) := NULL;
1887
1888 l_dimlevel_arr.EXTEND();
1889 l_dimlevel_arr(l_dimlevel_arr.COUNT) := NULL;
1890
1891 l_dimlevelValue_arr.EXTEND();
1892 l_dimlevelValue_arr(l_dimlevelValue_arr.COUNT) := NULL;
1893 end loop;
1894 end if;
1895
1896 ----------------RETRIEVE THE MEASURE TO ORDER THE DIMENSION--------------------------------------
1897
1898 l_measure_rec.measure_short_name := pMeasure_short_name;
1899 --l_measure_rec.measure_id := p_target_level_rec.measure_id;
1900 BIS_MEASURE_PUB.RETRIEVE_MEASURE( p_api_version => 1.0
1901 ,p_measure_rec => l_measure_rec
1902 ,p_all_info =>FND_API.G_TRUE
1903 ,x_measure_rec => l_measure_rec
1904 ,x_return_status => l_return_status
1905 ,x_error_tbl => l_error_tbl
1906 );
1907
1908 -- should prob continue only if there is a valid measure id .
1909
1910 ------------------------DIMENSION1 , will be repeated for all the dimensions-------------------
1911
1912 l_dimension_level_short_name := l_dimLevel_arr(1);
1913 l_dimension_level_id := null;
1914
1915 -- Step 1 - get the short name
1916 if (upper(l_dimLevelValue_arr(1)) = '''ALL''' OR
1917 l_dimLevelValue_arr(1) = '''''' OR
1918 (l_dimLevel_arr(1) is not null and l_dimLevelValue_arr(1) is null))
1919 then
1920 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(1),pSource);
1921 l_dimLevelValue_arr(1) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(1)
1922 , l_dimension_level_short_name );
1923 end if;
1924
1925 --Step 2 , get the dimension level id
1926 IF (l_dimension_level_short_name IS NOT NULL
1927 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
1928 OPEN c_dim_lvl(l_dimension_level_short_name);
1929 FETCH c_dim_lvl INTO l_dimension_level_id;
1930 CLOSE c_dim_lvl;
1931 END IF;
1932
1933 --STEP 3
1934 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
1935 IF (l_dimension_level_id IS NOT NULL
1936 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
1937 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
1938 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
1939 ,p_Dimension_Level_Rec => l_dim_level_rec
1940 ,x_Dimension_Level_Rec => l_dim_level_rec
1941 ,x_return_status => l_return_status
1942 ,x_error_Tbl => l_error_tbl
1943 );
1944
1945 --STEP 4 - assign the correct variable
1946 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
1947 lDimension1 := l_dim_arr(1);
1948 lDim1Level := l_dimension_level_short_name;
1949 lDim1LevelValue := l_dimLevelValue_arr(1);
1950 lDim1_level_id := l_dimension_level_id;
1951
1952 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
1953 lDimension2 := l_dim_arr(1);
1954 lDim2Level := l_dimension_level_short_name;
1955 lDim2LevelValue := l_dimLevelValue_arr(1);
1956 lDim2_level_id := l_dimension_level_id;
1957
1958 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
1959 lDimension3 := l_dim_arr(1);
1960 lDim3Level := l_dimension_level_short_name;
1961 lDim3LevelValue := l_dimLevelValue_arr(1);
1962 lDim3_level_id := l_dimension_level_id;
1963
1964 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
1965 lDimension4 := l_dim_arr(1);
1966 lDim4Level := l_dimension_level_short_name;
1967 lDim4LevelValue := l_dimLevelValue_arr(1);
1968 lDim4_level_id := l_dimension_level_id;
1969
1970 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
1971 lDimension5 := l_dim_arr(1);
1972 lDim5Level := l_dimension_level_short_name;
1973 lDim5LevelValue := l_dimLevelValue_arr(1);
1974 lDim5_level_id := l_dimension_level_id;
1975
1976 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
1977 lDimension6 := l_dim_arr(1);
1978 lDim6Level := l_dimension_level_short_name;
1979 lDim6LevelValue := l_dimLevelValue_arr(1);
1980 lDim6_level_id := l_dimension_level_id;
1981
1982 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
1983 lDimension7 := l_dim_arr(1);
1984 lDim7Level := l_dimension_level_short_name;
1985 lDim7LevelValue := l_dimLevelValue_arr(1);
1986 lDim7_level_id := l_dimension_level_id;
1987
1988 END IF;
1989 END IF;
1990
1991 ---------------------------DIMENSION 2 -------------------------
1992 l_dimension_level_short_name := l_dimLevel_arr(2);
1993 l_dimension_level_id := null;
1994
1995 -- Step 1 - get the short name
1996 if (upper(l_dimLevelValue_arr(2)) = '''ALL''' OR
1997 l_dimLevelValue_arr(2) = '''''' OR
1998 (l_dimLevel_arr(2) is not null and l_dimLevelValue_arr(2) is null))
1999 then
2000 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(2),pSource);
2001 l_dimLevelValue_arr(2) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(2)
2002 , l_dimension_level_short_name );
2003
2004 end if;
2005
2006
2007 --Step 2 , get the dimension level id
2008 IF (l_dimension_level_short_name IS NOT NULL
2009 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2010 OPEN c_dim_lvl(l_dimension_level_short_name);
2011 FETCH c_dim_lvl INTO l_dimension_level_id;
2012 CLOSE c_dim_lvl;
2013 END IF;
2014
2015 --STEP 3
2016 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2017 IF (l_dimension_level_id IS NOT NULL
2018 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2019 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2020 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2021 ,p_Dimension_Level_Rec => l_dim_level_rec
2022 ,x_Dimension_Level_Rec => l_dim_level_rec
2023 ,x_return_status => l_return_status
2024 ,x_error_Tbl => l_error_tbl
2025 );
2026
2027 --STEP 4 - assign the correct variable
2028 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2029 lDimension1 := l_dim_arr(2);
2030 lDim1Level := l_dimension_level_short_name;
2031 lDim1LevelValue := l_dimLevelValue_arr(2);
2032 lDim1_level_id := l_dimension_level_id;
2033
2034 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2035 lDimension2 := l_dim_arr(2);
2036 lDim2Level := l_dimension_level_short_name;
2037 lDim2LevelValue := l_dimLevelValue_arr(2);
2038 lDim2_level_id := l_dimension_level_id;
2039
2040 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2041 lDimension3 := l_dim_arr(2);
2042 lDim3Level := l_dimension_level_short_name;
2043 lDim3LevelValue := l_dimLevelValue_arr(2);
2044 lDim3_level_id := l_dimension_level_id;
2045
2046 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2047 lDimension4 := l_dim_arr(2);
2048 lDim4Level := l_dimension_level_short_name;
2049 lDim4LevelValue := l_dimLevelValue_arr(2);
2050 lDim4_level_id := l_dimension_level_id;
2051
2052 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2053 lDimension5 := l_dim_arr(2);
2054 lDim5Level := l_dimension_level_short_name;
2055 lDim5LevelValue := l_dimLevelValue_arr(2);
2056 lDim5_level_id := l_dimension_level_id;
2057
2058 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2059 lDimension6 := l_dim_arr(2);
2060 lDim6Level := l_dimension_level_short_name;
2061 lDim6LevelValue := l_dimLevelValue_arr(2);
2062 lDim6_level_id := l_dimension_level_id;
2063
2064 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2065 lDimension7 := l_dim_arr(2);
2066 lDim7Level := l_dimension_level_short_name;
2067 lDim7LevelValue := l_dimLevelValue_arr(2);
2068 lDim7_level_id := l_dimension_level_id;
2069
2070 END IF;
2071
2072 END IF;
2073
2074 ------------------------DIMENSION 3 ------------------------------------------
2075 l_dimension_level_short_name := l_dimLevel_arr(3);
2076 l_dimension_level_id := null;
2077
2078 -- Step 1 - get the short name
2079 if (upper(l_dimLevelValue_arr(3)) = '''ALL''' OR
2080 l_dimLevelValue_arr(3) = '''''' OR
2081 (l_dimLevel_arr(3) is not null and l_dimLevelValue_arr(3) is null))
2082 then
2083 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(3),pSource);
2084 l_dimLevelValue_arr(3) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(3)
2085 , l_dimension_level_short_name );
2086
2087 end if;
2088
2089 --Step 2 , get the dimension level id
2090 IF (l_dimension_level_short_name IS NOT NULL
2091 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2092 OPEN c_dim_lvl(l_dimension_level_short_name);
2093 FETCH c_dim_lvl INTO l_dimension_level_id;
2094 CLOSE c_dim_lvl;
2095 END IF;
2096
2097 --STEP 3
2098 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2099 IF (l_dimension_level_id IS NOT NULL
2100 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2101 --SetNULL(l_dim_level_rec,l_dim_level_rec);
2102 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2103 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2104 ,p_Dimension_Level_Rec => l_dim_level_rec
2105 ,x_Dimension_Level_Rec => l_dim_level_rec
2106 ,x_return_status => l_return_status
2107 ,x_error_Tbl => l_error_tbl
2108 );
2109
2110 --STEP 4 - assign the correct variable
2111 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2112 lDimension1 := l_dim_arr(3);
2113 lDim1Level := l_dimension_level_short_name;
2114 lDim1LevelValue := l_dimLevelValue_arr(3);
2115 lDim1_level_id := l_dimension_level_id;
2116
2117 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2118 lDimension2 := l_dim_arr(3);
2119 lDim2Level := l_dimension_level_short_name;
2120 lDim2LevelValue := l_dimLevelValue_arr(3);
2121 lDim2_level_id := l_dimension_level_id;
2122
2123 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2124 lDimension3 := l_dim_arr(3);
2125 lDim3Level := l_dimension_level_short_name;
2126 lDim3LevelValue := l_dimLevelValue_arr(3);
2127 lDim3_level_id := l_dimension_level_id;
2128
2129 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2130 lDimension4 := l_dim_arr(3);
2131 lDim4Level := l_dimension_level_short_name;
2132 lDim4LevelValue := l_dimLevelValue_arr(3);
2133 lDim4_level_id := l_dimension_level_id;
2134
2135 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2136 lDimension5 := l_dim_arr(3);
2137 lDim5Level := l_dimension_level_short_name;
2138 lDim5LevelValue := l_dimLevelValue_arr(3);
2139 lDim5_level_id := l_dimension_level_id;
2140
2141 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2142 lDimension6 := l_dim_arr(3);
2143 lDim6Level := l_dimension_level_short_name;
2144 lDim6LevelValue := l_dimLevelValue_arr(3);
2145 lDim6_level_id := l_dimension_level_id;
2146
2147 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2148 lDimension7 := l_dim_arr(3);
2149 lDim7Level := l_dimension_level_short_name;
2150 lDim7LevelValue := l_dimLevelValue_arr(3);
2151 lDim7_level_id := l_dimension_level_id;
2152
2153 END IF;
2154 END IF;
2155
2156 ----------------------------------------DIMENSION 4 -------------------------------
2157 l_dimension_level_short_name := l_dimLevel_arr(4);
2158 l_dimension_level_id := null;
2159
2160 -- Step 1 - get the short name
2161 if (upper(l_dimLevelValue_arr(4)) = '''ALL''' OR
2162 l_dimLevelValue_arr(4) = '''''' OR
2163 (l_dimLevel_arr(4) is not null and l_dimLevelValue_arr(4) is null))
2164 then
2165 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(4),pSource);
2166 l_dimLevelValue_arr(4) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(4)
2167 , l_dimension_level_short_name );
2168 end if;
2169
2170 --Step 2 , get the dimension level id
2171 IF (l_dimension_level_short_name IS NOT NULL
2172 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2173 OPEN c_dim_lvl(l_dimension_level_short_name);
2174 FETCH c_dim_lvl INTO l_dimension_level_id;
2175 CLOSE c_dim_lvl;
2176 END IF;
2177
2178 --STEP 3
2179 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2180 IF (l_dimension_level_id IS NOT NULL
2181 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2182 --SetNULL(l_dim_level_rec,l_dim_level_rec);
2183 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2184 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2185 ,p_Dimension_Level_Rec => l_dim_level_rec
2186 ,x_Dimension_Level_Rec => l_dim_level_rec
2187 ,x_return_status => l_return_status
2188 ,x_error_Tbl => l_error_tbl
2189 );
2190
2191 --STEP 4 - assign the correct variable
2192 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2193 lDimension1 := l_dim_arr(4);
2194 lDim1Level := l_dimension_level_short_name;
2195 lDim1LevelValue := l_dimLevelValue_arr(4);
2196 lDim1_level_id := l_dimension_level_id;
2197
2198 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2199 lDimension2 := l_dim_arr(4);
2200 lDim2Level := l_dimension_level_short_name;
2201 lDim2LevelValue := l_dimLevelValue_arr(4);
2202 lDim2_level_id := l_dimension_level_id;
2203
2204 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2205 lDimension3 := l_dim_arr(4);
2206 lDim3Level := l_dimension_level_short_name;
2207 lDim3LevelValue := l_dimLevelValue_arr(4);
2208 lDim3_level_id := l_dimension_level_id;
2209
2210 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2211 lDimension4 := l_dim_arr(4);
2212 lDim4Level := l_dimension_level_short_name;
2213 lDim4LevelValue := l_dimLevelValue_arr(4);
2214 lDim4_level_id := l_dimension_level_id;
2215
2216 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2217 lDimension5 := l_dim_arr(4);
2218 lDim5Level := l_dimension_level_short_name;
2219 lDim5LevelValue := l_dimLevelValue_arr(4);
2220 lDim5_level_id := l_dimension_level_id;
2221
2222 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2223 lDimension6 := l_dim_arr(4);
2224 lDim6Level := l_dimension_level_short_name;
2225 lDim6LevelValue := l_dimLevelValue_arr(4);
2226 lDim6_level_id := l_dimension_level_id;
2227
2228 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2229 lDimension7 := l_dim_arr(4);
2230 lDim7Level := l_dimension_level_short_name;
2231 lDim7LevelValue := l_dimLevelValue_arr(4);
2232 lDim7_level_id := l_dimension_level_id;
2233 END IF;
2234 END IF;
2235
2236 ----------------------------------------DIMENSION 5 -------------------------
2237
2238 l_dimension_level_short_name := l_dimLevel_arr(5);
2239 l_dimension_level_id := null;
2240
2241 -- Step 1 - get the short name
2242 if (upper(l_dimLevelValue_arr(5)) = '''ALL''' OR
2243 l_dimLevelValue_arr(5) = '''''' OR
2244 (l_dimLevel_arr(5) is not null and l_dimLevelValue_arr(5) is null))
2245 then
2246 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(5),pSource);
2247 l_dimLevelValue_arr(5) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(5)
2248 , l_dimension_level_short_name );
2249
2250 end if;
2251
2252 --Step 2 , get the dimension level id
2253 IF (l_dimension_level_short_name IS NOT NULL
2254 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2255 OPEN c_dim_lvl(l_dimension_level_short_name);
2256 FETCH c_dim_lvl INTO l_dimension_level_id;
2257 CLOSE c_dim_lvl;
2258 END IF;
2259
2260 --STEP 3
2261 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2262 IF (l_dimension_level_id IS NOT NULL
2263 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2264 --SetNULL(l_dim_level_rec,l_dim_level_rec);
2265 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2266 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2267 ,p_Dimension_Level_Rec => l_dim_level_rec
2268 ,x_Dimension_Level_Rec => l_dim_level_rec
2269 ,x_return_status => l_return_status
2270 ,x_error_Tbl => l_error_tbl
2271 );
2272
2273 --STEP 4 - assign the correct variable
2274 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2275 lDimension1 := l_dim_arr(5);
2276 lDim1Level := l_dimension_level_short_name;
2277 lDim1LevelValue := l_dimLevelValue_arr(5);
2278 lDim1_level_id := l_dimension_level_id;
2279
2280 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2281 lDimension2 := l_dim_arr(5);
2282 lDim2Level := l_dimension_level_short_name;
2283 lDim2LevelValue := l_dimLevelValue_arr(5);
2284 lDim2_level_id := l_dimension_level_id;
2285
2286 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2287 lDimension3 := l_dim_arr(5);
2288 lDim3Level := l_dimension_level_short_name;
2289 lDim3LevelValue := l_dimLevelValue_arr(5);
2290 lDim3_level_id := l_dimension_level_id;
2291
2292 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2293 lDimension4 := l_dim_arr(5);
2294 lDim4Level := l_dimension_level_short_name;
2295 lDim4LevelValue := l_dimLevelValue_arr(5);
2296 lDim4_level_id := l_dimension_level_id;
2297
2298 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2299 lDimension5 := l_dim_arr(5);
2300 lDim5Level := l_dimension_level_short_name;
2301 lDim5LevelValue := l_dimLevelValue_arr(5);
2302 lDim5_level_id := l_dimension_level_id;
2303
2304 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2305 lDimension6 := l_dim_arr(5);
2306 lDim6Level := l_dimension_level_short_name;
2307 lDim6LevelValue := l_dimLevelValue_arr(5);
2308 lDim6_level_id := l_dimension_level_id;
2309
2310 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2311 lDimension7 := l_dim_arr(5);
2312 lDim7Level := l_dimension_level_short_name;
2313 lDim7LevelValue := l_dimLevelValue_arr(5);
2314 lDim7_level_id := l_dimension_level_id;
2315
2316 END IF;
2317 END IF;
2318
2319 -------------------------------------------DIMENSION 6 -----------------------
2320 l_dimension_level_short_name := l_dimLevel_arr(6);
2321 l_dimension_level_id := null;
2322
2323 -- Step 1 - get the short name
2324 if (upper(l_dimLevelValue_arr(6)) = '''ALL''' OR
2325 l_dimLevelValue_arr(6) = '''''' OR
2326 (l_dimLevel_arr(6) is not null and l_dimLevelValue_arr(6) is null))
2327 then
2328 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(6),pSource);
2329 l_dimLevelValue_arr(6) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(6)
2330 , l_dimension_level_short_name );
2331
2332 end if;
2333
2334 --Step 2 , get the dimension level id
2335 IF (l_dimension_level_short_name IS NOT NULL
2336 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2337 OPEN c_dim_lvl(l_dimension_level_short_name);
2338 FETCH c_dim_lvl INTO l_dimension_level_id;
2339 CLOSE c_dim_lvl;
2340 END IF;
2341
2342 --STEP 3
2343 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2344 IF (l_dimension_level_id IS NOT NULL
2345 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2346 --SetNULL(l_dim_level_rec,l_dim_level_rec);
2347 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2348 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2349 ,p_Dimension_Level_Rec => l_dim_level_rec
2350 ,x_Dimension_Level_Rec => l_dim_level_rec
2351 ,x_return_status => l_return_status
2352 ,x_error_Tbl => l_error_tbl
2353 );
2354
2355 --STEP 4 - assign the correct variable
2356 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2357 lDimension1 := l_dim_arr(6);
2358 lDim1Level := l_dimension_level_short_name;
2359 lDim1LevelValue := l_dimLevelValue_arr(6);
2360 lDim1_level_id := l_dimension_level_id;
2361
2362 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2363 lDimension2 := l_dim_arr(6);
2364 lDim2Level := l_dimension_level_short_name;
2365 lDim2LevelValue := l_dimLevelValue_arr(6);
2366 lDim2_level_id := l_dimension_level_id;
2367
2368 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2369 lDimension3 := l_dim_arr(6);
2370 lDim3Level := l_dimension_level_short_name;
2371 lDim3LevelValue := l_dimLevelValue_arr(6);
2372 lDim3_level_id := l_dimension_level_id;
2373
2374 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2375 lDimension4 := l_dim_arr(6);
2376 lDim4Level := l_dimension_level_short_name;
2377 lDim4LevelValue := l_dimLevelValue_arr(6);
2378 lDim4_level_id := l_dimension_level_id;
2379
2380 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2381 lDimension5 := l_dim_arr(6);
2382 lDim5Level := l_dimension_level_short_name;
2383 lDim5LevelValue := l_dimLevelValue_arr(6);
2384 lDim5_level_id := l_dimension_level_id;
2385
2386 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2387 lDimension6 := l_dim_arr(6);
2388 lDim6Level := l_dimension_level_short_name;
2389 lDim6LevelValue := l_dimLevelValue_arr(6);
2390 lDim6_level_id := l_dimension_level_id;
2391
2392 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2393 lDimension7 := l_dim_arr(6);
2394 lDim7Level := l_dimension_level_short_name;
2395 lDim7LevelValue := l_dimLevelValue_arr(6);
2396 lDim7_level_id := l_dimension_level_id;
2397
2398 END IF;
2399 END IF;
2400
2401 -------------------------------------------------DIMENSION 7 -------------------
2402 l_dimension_level_short_name := l_dimLevel_arr(7);
2403 l_dimension_level_id := null;
2404
2405 -- Step 1 - get the short name
2406 if (upper(l_dimLevelValue_arr(7)) = '''ALL''' OR
2407 l_dimLevelValue_arr(7) = '''''' OR
2408 (l_dimLevel_arr(7) is not null and l_dimLevelValue_arr(7) is null))
2409 then
2410 l_dimension_level_short_name := BIS_PMV_PMF_PVT.getTotalDimLevelName(l_dim_arr(7),pSource);
2411 l_dimLevelValue_arr(7) := BIS_PMV_PMF_PVT.getTotalDimValue(pSource,l_dim_arr(7)
2412 , l_dimension_level_short_name );
2413
2414 end if;
2415
2416 --Step 2 , get the dimension level id
2417 IF (l_dimension_level_short_name IS NOT NULL
2418 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_short_name) = FND_API.G_TRUE) THEN
2419 OPEN c_dim_lvl(l_dimension_level_short_name);
2420 FETCH c_dim_lvl INTO l_dimension_level_id;
2421 CLOSE c_dim_lvl;
2422 END IF;
2423
2424 --STEP 3
2425 --Get the dimension ids for all the dimension level ids-later used to sequence the dimension levels
2426 IF (l_dimension_level_id IS NOT NULL
2427 AND BIS_UTILITIES_PUB.Value_Not_Missing(l_dimension_level_id)= FND_API.G_TRUE) THEN
2428 --SetNULL(l_dim_level_rec,l_dim_level_rec);
2429 l_dim_level_rec.dimension_level_id := l_dimension_level_id;
2430 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => 1.0
2431 ,p_Dimension_Level_Rec => l_dim_level_rec
2432 ,x_Dimension_Level_Rec => l_dim_level_rec
2433 ,x_return_status => l_return_status
2434 ,x_error_Tbl => l_error_tbl
2435 );
2436
2437 --STEP 4 - assign the correct variable
2438 IF (l_measure_rec.dimension1_id = l_dim_level_rec.dimension_id) THEN
2439 lDimension1 := l_dim_arr(7);
2440 lDim1Level := l_dimension_level_short_name;
2441 lDim1LevelValue := l_dimLevelValue_arr(7);
2442 lDim1_level_id := l_dimension_level_id;
2443
2444 ELSIF (l_measure_rec.dimension2_id = l_dim_level_rec.dimension_id) THEN
2445 lDimension2 := l_dim_arr(7);
2446 lDim2Level := l_dimension_level_short_name;
2447 lDim2LevelValue := l_dimLevelValue_arr(7);
2448 lDim2_level_id := l_dimension_level_id;
2449
2450 ELSIF (l_measure_rec.dimension3_id = l_dim_level_rec.dimension_id) THEN
2451 lDimension3 := l_dim_arr(7);
2452 lDim3Level := l_dimension_level_short_name;
2453 lDim3LevelValue := l_dimLevelValue_arr(7);
2454 lDim3_level_id := l_dimension_level_id;
2455
2456 ELSIF (l_measure_rec.dimension4_id = l_dim_level_rec.dimension_id) THEN
2457 lDimension4 := l_dim_arr(7);
2458 lDim4Level := l_dimension_level_short_name;
2459 lDim4LevelValue := l_dimLevelValue_arr(7);
2460 lDim4_level_id := l_dimension_level_id;
2461
2462 ELSIF (l_measure_rec.dimension5_id = l_dim_level_rec.dimension_id) THEN
2463 lDimension5 := l_dim_arr(7);
2464 lDim5Level := l_dimension_level_short_name;
2465 lDim5LevelValue := l_dimLevelValue_arr(7);
2466 lDim5_level_id := l_dimension_level_id;
2467
2468 ELSIF (l_measure_rec.dimension6_id = l_dim_level_rec.dimension_id) THEN
2469 lDimension6 := l_dim_arr(7);
2470 lDim6Level := l_dimension_level_short_name;
2471 lDim6LevelValue := l_dimLevelValue_arr(7);
2472 lDim6_level_id := l_dimension_level_id;
2473
2474 ELSIF (l_measure_rec.dimension7_id = l_dim_level_rec.dimension_id) THEN
2475 lDimension7 := l_dim_arr(7);
2476 lDim7Level := l_dimension_level_short_name;
2477 lDim7LevelValue := l_dimLevelValue_arr(7);
2478 lDim7_level_id := l_dimension_level_id;
2479
2480 END IF;
2481 END IF;
2482
2483 ------------------------------------DONE FOR ALL DIMENSIONS ------------------------
2484
2485 -----------------------GET THE TARGET_LEVEL_ID --------------------------------------------
2486
2487 BEGIN
2488 l_target_level_rec.measure_name := l_measure_rec.measure_name;
2489 l_target_level_Rec.measure_id := l_measure_rec.measure_id;
2490
2491 --also return to UOM
2492 l_target_level_rec.Unit_Of_Measure := l_measure_rec.Unit_Of_Measure_Class;
2493
2494 l_target_level_rec.Dimension1_Level_ID:= NVL(lDim1_level_id ,FND_API.G_MISS_NUM);
2495 l_target_level_rec.Dimension2_Level_ID:= NVL(lDim2_level_id ,FND_API.G_MISS_NUM);
2496 l_target_level_rec.Dimension3_Level_ID:= NVL(lDim3_level_id ,FND_API.G_MISS_NUM);
2497 l_target_level_rec.Dimension4_Level_ID:= NVL(lDim4_level_id ,FND_API.G_MISS_NUM);
2498 l_target_level_rec.Dimension5_Level_ID:= NVL(lDim5_level_id ,FND_API.G_MISS_NUM);
2499 l_target_level_rec.Dimension6_Level_ID:= NVL(lDim6_level_id ,FND_API.G_MISS_NUM);
2500 l_target_level_rec.Dimension7_Level_ID:= NVL(lDim7_level_id ,FND_API.G_MISS_NUM);
2501
2502 x_target_level_id := BIS_TARGET_LEVEL_PVT.Get_Level_Id_From_Dimlevels(l_target_level_rec);
2503
2504 EXCEPTION
2505 WHEN OTHERS THEN
2506 x_target_level_id := NULL;
2507 END;
2508
2509
2510 -- x_bind_variables := x_bind_variables || SEPERATOR||x_target_level_id ;
2511 -- x_Ordered_Dimension_Select := ' ?';
2512 -------------------------------------------------------------------------------------------------
2513
2514 ------------------------------------------------------------------------------------------
2515 -- Append all the dimensions to form the actual string
2516 -- The first 2 dimensions are always quoted, therfore they are always bound
2517
2518 x_bind_variables := x_bind_variables || SEPERATOR||lDimension1;
2519 x_bind_count := x_bind_count +1;
2520 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2521 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select||' :'||x_bind_count;
2522
2523 x_bind_variables := x_bind_variables || SEPERATOR||lDIm1Level;
2524 x_bind_count := x_bind_count+1;
2525 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2526 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2527
2528
2529 -- if the 3rd value is null, then append empty quotes.
2530 if (lDIm1LevelValue is null ) THEN -- put in empty quotes
2531 x_bind_variables := x_bind_variables || SEPERATOR||'';
2532 x_bind_count := x_bind_count +1;
2533 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2534 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2535 ELSE
2536 -- if single quote on both sides, then bind, else it is a column name
2537 if ( substr(lDIm1LevelValue, 1, 1)='''' OR substr (lDIm1LevelValue, 1, 4) <> 'VBT.' ) then
2538 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm1LevelValue, '''', null); --strip the quotes
2539 x_bind_count := x_bind_count+1;
2540 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2541 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2542 else
2543 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm1LevelValue;
2544 end if;
2545 END IF;
2546
2547 x_bind_variables := x_bind_variables || SEPERATOR||lDimension2;
2548 x_bind_count := x_bind_count +1;
2549 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2550 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2551
2552 x_bind_variables := x_bind_variables || SEPERATOR||lDIm2Level;
2553 x_bind_count := x_bind_count+1;
2554 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2555 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2556
2557
2558 -- if the 3rd value is null, then append empty quotes.
2559 if (lDIm2LevelValue is null ) THEN -- put in empty quotes
2560 x_bind_variables := x_bind_variables || SEPERATOR||'';
2561 x_bind_count := x_bind_count +1;
2562 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2563 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2564 ELSE
2565 -- if starts with single quote then bind, else it is a column name
2566 if ( substr(lDIm2LevelValue, 1, 1)='''' OR substr (lDIm2LevelValue, 1, 4) <> 'VBT.' ) then
2567 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm2LevelValue, '''', null) ; --strip the quotes
2568 x_bind_count := x_bind_count +1;
2569 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2570 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2571 else
2572 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm2LevelValue;
2573 end if;
2574 END IF;
2575
2576 x_bind_variables := x_bind_variables || SEPERATOR||lDimension3;
2577 x_bind_count := x_bind_count +1;
2578 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2579 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2580
2581 x_bind_variables := x_bind_variables || SEPERATOR||lDIm3Level;
2582 x_bind_count := x_bind_count +1;
2583 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2584 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2585
2586
2587 -- if the 3rd value is null, then append empty quotes.
2588 if (lDIm3LevelValue is null ) THEN -- put in empty quotes
2589 x_bind_variables := x_bind_variables || SEPERATOR||'';
2590 x_bind_count := x_bind_count +1;
2591 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2592 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2593 ELSE
2594 -- if single quote on both sides, then bind, else it is a column name
2595 if ( substr(lDIm3LevelValue, 1, 1)='''' OR substr (lDIm3LevelValue, 1, 4) <> 'VBT.' ) then
2596 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm3LevelValue, '''', null) ; --strip the quotes
2597 x_bind_count := x_bind_count +1;
2598 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2599 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2600 else
2601 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm3LevelValue;
2602 end if;
2603 END IF;
2604
2605 x_bind_variables := x_bind_variables || SEPERATOR||lDimension4;
2606 x_bind_count := x_bind_count +1;
2607 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2608 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2609
2610 x_bind_variables := x_bind_variables || SEPERATOR||lDIm4Level;
2611 x_bind_count := x_bind_count +1;
2612 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2613 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2614
2615 -- if the 3rd value is null, then append empty quotes.
2616 if (lDIm4LevelValue is null ) THEN -- put in empty quotes
2617 x_bind_variables := x_bind_variables || SEPERATOR||'';
2618 x_bind_count := x_bind_count +1;
2619 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2620 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2621 ELSE
2622 -- if single quote on both sides, then bind, else it is a column name
2623 if ( substr(lDIm4LevelValue, 1, 1)='''' OR substr (lDIm4LevelValue, 1, 4) <> 'VBT.' ) then
2624 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm4LevelValue, '''', null) ; --strip the quotes
2625 x_bind_count := x_bind_count +1;
2626 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2627 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2628 else
2629 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm4LevelValue;
2630 end if;
2631 END IF;
2632
2633 x_bind_variables := x_bind_variables || SEPERATOR||lDimension5;
2634 x_bind_count := x_bind_count +1;
2635 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2636 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2637
2638 x_bind_variables := x_bind_variables || SEPERATOR||lDIm5Level;
2639 x_bind_count := x_bind_count +1;
2640 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2641 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2642
2643 -- if the 3rd value is null, then append empty quotes.
2644 if (lDIm5LevelValue is null ) THEN -- put in empty quotes
2645 x_bind_variables := x_bind_variables || SEPERATOR||'';
2646 x_bind_count := x_bind_count +1;
2647 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2648 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2649 ELSE
2650 -- if single quote on both sides, then bind, else it is a column name
2651 if ( substr(lDIm5LevelValue, 1, 1)='''' OR substr (lDIm5LevelValue, 1, 4) <> 'VBT.' ) then
2652 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm5LevelValue, '''', null) ; --strip the quotes
2653 x_bind_count := x_bind_count +1;
2654 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2655 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2656 else
2657 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm5LevelValue;
2658 end if;
2659 END IF;
2660
2661
2662 x_bind_variables := x_bind_variables || SEPERATOR||lDimension6;
2663 x_bind_count := x_bind_count +1;
2664 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2665 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2666
2667 x_bind_variables := x_bind_variables || SEPERATOR||lDIm6Level;
2668 x_bind_count := x_bind_count +1;
2669 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2670 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2671
2672 -- if the 3rd value is null, then append empty quotes.
2673 if (lDIm6LevelValue is null ) THEN -- put in empty quotes
2674 x_bind_variables := x_bind_variables || SEPERATOR||'';
2675 x_bind_count := x_bind_count +1;
2676 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2677 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2678 ELSE
2679 -- if single quote on both sides, then bind, else it is a column name
2680 if ( substr(lDIm6LevelValue, 1, 1)='''' OR substr (lDIm6LevelValue, 1, 4) <> 'VBT.' ) then
2681 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm6LevelValue, '''', null) ; --strip the quotes
2682 x_bind_count := x_bind_count +1;
2683 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2684 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2685 else
2686 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm6LevelValue;
2687 end if;
2688 END IF;
2689
2690 x_bind_variables := x_bind_variables || SEPERATOR||lDimension7;
2691 x_bind_count := x_bind_count +1;
2692 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2693 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2694
2695 x_bind_variables := x_bind_variables || SEPERATOR||lDIm7Level;
2696 x_bind_count := x_bind_count +1;
2697 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2698 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2699
2700 -- if the 3rd value is null, then append empty quotes.
2701 if (lDIm7LevelValue is null ) THEN -- put in empty quotes
2702 x_bind_variables := x_bind_variables || SEPERATOR||'';
2703 x_bind_count := x_bind_count +1;
2704 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2705 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2706 ELSE
2707 -- if single quote on both sides, then bind, else it is a column name, or there is a dot => it is a column name
2708 -- this is cos sometimes there is a number
2709 if ( substr(lDIm7LevelValue, 1, 1)='''' OR substr (lDIm7LevelValue, 1, 4) <> 'VBT.' ) then
2710 x_bind_variables := x_bind_variables || SEPERATOR||replace (lDIm7LevelValue, '''', null) ; --strip the quotes
2711 x_bind_count := x_bind_count +1;
2712 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2713 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2714 else
2715 IF (instrb (lDIm7LevelValue, '.') >0) then
2716 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||',' ||lDIm7LevelValue;
2717 else
2718 x_bind_variables := x_bind_variables || SEPERATOR||lDIm7LevelValue ; --strip the quotes
2719 x_bind_count := x_bind_count +1;
2720 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2721 x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;
2722 end if;
2723 end if;
2724 END IF;
2725
2726 END IF; -- if not(l_no_target)
2727 EXCEPTION
2728 WHEN OTHERS THEN
2729 x_target_level_id := NULL;
2730 x_no_target := TRUE;
2731
2732 END order_Dimensions;
2733
2734
2735 procedure GET_TARGET_SELECT(p_user_session_rec in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
2736 p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC,
2737 p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
2738 p_report_type in VARCHAR2,
2739 p_plan_id in VARCHAR2,
2740 p_viewby_dimension in VARCHAR2,
2741 p_viewby_attribute2 in VARCHAR2,
2742 p_viewby_id_name in VARCHAR2,
2743 p_time_from_description in VARCHAR2,
2744 p_time_to_description in VARCHAR2,
2745 x_target_select out NOCOPY VARCHAR2,
2746 x_no_target out NOCOPY boolean,
2747 x_bind_variables IN OUT NOCOPY VARCHAR2,
2748 --x_bind_indexes IN OUT NOCOPY VARCHAR2,
2749 x_bind_count IN OUT NOCOPY NUMBER) is
2750 l_target_select varchar2(2000);
2751 l_no_target boolean := false;
2752 l_dimension_select varchar2(2000);
2753 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
2754 l_measure_short_name varchar2(2000);
2755 l_dimension_level varchar2(2000);
2756 l_count number;
2757 l_target_level_id NUMBER;
2758 l_bind_variables VARCHAR2(2000);
2759 --l_bind_indexes VARCHAR2(2000);
2760 begin
2761
2762 l_measure_short_name := p_ak_region_item_rec.attribute2;
2763 l_count := 0;
2764
2765
2766 -- SERAO1 , 02/11/02 - added pvt function to order the dimensions once only instead of every time the query is run
2767 order_Dimensions(
2768 p_report_type,
2769 p_parameter_tbl ,
2770 p_time_to_description ,
2771 p_time_from_description ,
2772 p_viewby_id_name ,
2773 p_viewby_dimension ,
2774 p_viewby_attribute2,
2775 l_measure_short_name,
2776 l_dimension_select,
2777 l_target_level_id,
2778 l_no_target,
2779 l_bind_variables,
2780 --l_bind_indexes,
2781 x_bind_count
2782 );
2783
2784 -- The x_bind_count is first sent to the order_dimensions and then constructed here
2785 -- so numbering is not in the same order, but this does not matter for binding
2786 -- also the case if there was an excpetion in some place in order_dimensions.
2787
2788 if not(l_no_target) then
2789 l_target_select := l_target_select || 'BIS_PMV_PMF_PVT.get_target_new(';
2790
2791 -- bind the first few variables
2792 x_bind_variables := x_bind_variables || SEPERATOR||p_report_type;
2793 x_bind_count := x_bind_count +1;
2794 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2795 l_target_select := l_target_select ||' :'||x_bind_count;
2796
2797 x_bind_variables := x_bind_variables || SEPERATOR||p_user_session_rec.session_id ;
2798 x_bind_count := x_bind_count +1;
2799 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2800 l_target_select := l_target_select ||', :'||x_bind_count;
2801
2802 x_bind_variables := x_bind_variables || SEPERATOR||p_user_session_rec.region_code ;
2803 x_bind_count := x_bind_count +1;
2804 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2805 l_target_select := l_target_select ||', :'||x_bind_count;
2806
2807 x_bind_variables := x_bind_variables || SEPERATOR||p_user_session_rec.function_name ;
2808 x_bind_count := x_bind_count +1;
2809 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2810 l_target_select := l_target_select ||', :'||x_bind_count;
2811
2812 x_bind_variables := x_bind_variables || SEPERATOR||l_measure_short_name ;
2813 x_bind_count := x_bind_count +1;
2814 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2815 l_target_select := l_target_select ||', :'||x_bind_count;
2816
2817 x_bind_variables := x_bind_variables || SEPERATOR||p_plan_id ;
2818 x_bind_count := x_bind_count +1;
2819 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2820 l_target_select := l_target_select ||', :'||x_bind_count;
2821
2822 x_bind_variables := x_bind_variables || SEPERATOR||l_target_level_id ;
2823 x_bind_count := x_bind_count +1;
2824 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2825 l_target_select := l_target_select ||', :'||x_bind_count ;
2826
2827 if l_dimension_select is not null and length(l_dimension_select) > 0 then
2828 l_target_select := l_target_select || ',' || l_dimension_select;
2829 x_bind_variables := x_bind_variables || l_bind_variables;
2830 --x_bind_indexes := x_bind_indexes || l_bind_indexes;
2831 end if;
2832
2833 l_target_select := l_target_select || ')';
2834
2835 end if;
2836
2837 x_target_select := l_target_select;
2838 x_no_target := l_no_target;
2839
2840 end GET_TARGET_SELECT;
2841
2842 function GET_DIMENSION_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
2843 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
2844 p_ak_region_rec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
2845 p_org_dimension_level in VARCHAR2,
2846 p_org_dimension_level_value in VARCHAR2,
2847 p_viewby_dimension in VARCHAR2,
2848 p_time_id_name in VARCHAR2,
2849 p_time_value_name in VARCHAR2,
2850 p_region_code in VARCHAR2,
2851 p_TM_alias in VARCHAR2,
2852 x_bind_variables in OUT NOCOPY VARCHAR2,
2853 --x_bind_indexes in OUT NOCOPY VARCHAR2,
2854 x_bind_count in out NOCOPY number)
2855 return varchar2 is
2856 l_dimension_where varchar2(2000);
2857 begin
2858 if p_parameter_rec.dimension in ('TIME', 'EDW_TIME_M') then
2859 l_dimension_where := l_dimension_where
2860 || BIS_PMV_QUERY_PVT.GET_TIME_WHERE(
2861 p_parameter_rec => p_parameter_rec,
2862 p_save_region_item_rec => p_save_region_item_rec,
2863 p_ak_region_rec => p_ak_region_rec,
2864 p_org_dimension_level => p_org_dimension_level,
2865 p_org_dimension_level_value => p_org_dimension_level_value,
2866 p_viewby_dimension => p_viewby_dimension,
2867 p_time_id_name => p_time_id_name,
2868 p_time_value_name => p_time_value_name,
2869 p_region_code => p_region_code,
2870 p_TM_alias => p_TM_alias,
2871 x_bind_variables => x_bind_variables,
2872 --x_bind_indexes => x_bind_indexes,
2873 x_bind_count => x_bind_count);
2874 else
2875 l_dimension_where := l_dimension_where ||
2876 BIS_PMV_QUERY_PVT.GET_NON_TIME_WHERE(p_parameter_rec,p_save_region_item_rec,null,
2877 x_bind_variables,x_bind_count);
2878 end if;
2879 return l_dimension_where;
2880 end GET_DIMENSION_WHERE;
2881
2882 function GET_TIME_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
2883 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
2884 p_ak_region_rec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
2885 p_org_dimension_level in VARCHAR2,
2886 p_org_dimension_level_value in VARCHAR2,
2887 p_viewby_dimension in VARCHAR2,
2888 p_time_id_name in VARCHAR2,
2889 p_time_value_name in VARCHAR2,
2890 p_region_code in VARCHAR2,
2891 p_TM_alias in VARCHAR2,
2892 x_bind_variables in OUT NOCOPY VARCHAR2,
2893 --x_bind_indexes in OUT NOCOPY VARCHAR2,
2894 x_bind_count in out NOCOPY number)
2895 return varchar2 is
2896 l_time_where varchar2(2000);
2897 l_org_dimension_level varchar2(2000);
2898 l_org_dimension_level_value varchar2(2000);
2899 l_time_dimension_level varchar2(2000);
2900 l_split_string VARCHAR2(80);
2901 begin
2902 --jprabhud - 11/06/02 - Bug 2657202 - Added check for single quotes
2903 if p_org_dimension_level_value is null or upper(p_org_dimension_level_value) = gvAll
2904 or p_org_dimension_level_value = '''''' then
2905 l_org_dimension_level := 'TOTAL_ORGANIZATIONS';
2906 l_org_dimension_level_value := '-1';
2907 else
2908 l_org_dimension_level := p_org_dimension_level;
2909 l_org_dimension_level_value := p_org_dimension_level_value;
2910 end if;
2911
2912 l_time_dimension_level := substr(p_save_region_item_rec.attribute2,
2913 instr(p_save_region_item_rec.attribute2,'+')+1);
2914
2915 if substr(p_parameter_rec.parameter_name, length(p_parameter_rec.parameter_name)-length('_FROM')+1) = '_FROM' then
2916 if upper(nvl(p_ak_region_rec.report_type, 'OLTP')) <> 'EDW' and substr(p_org_dimension_level,1,2) <> 'HR'
2917 and l_time_dimension_level in ('MONTH','QUARTER','YEAR','TOTAL_TIME') then
2918
2919 if l_org_dimension_level is not null and length(l_org_dimension_level) > 0
2920 and l_org_dimension_level_value is not null and length(l_org_dimension_level_value) > 0 then
2921 l_time_where := l_time_where || ' and '||p_TM_alias||'.organization_id ';
2922 if instrb(l_org_dimension_level_value, ''',''') > 0 then
2923 l_split_string := '';
2924 splitMultipleVariables(replace (l_org_dimension_level_value, '''', null), x_bind_variables,
2925 x_bind_count, l_split_string);
2926 l_time_where := l_time_where || ' in ('||l_split_string||')';
2927
2928 -- l_time_where := l_time_where || ' in (' || l_org_dimension_level_value|| ')';
2929 else
2930 x_bind_variables := x_bind_variables || SEPERATOR||l_org_dimension_level_value;
2931 x_bind_count := x_bind_count +1;
2932 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2933 l_time_where := l_time_where || ' = :'||x_bind_count ;
2934 -- l_time_where := l_time_where || ' = ' || l_org_dimension_level_value;
2935 end if;
2936 x_bind_variables := x_bind_variables || SEPERATOR|| l_org_dimension_level;
2937 x_bind_count := x_bind_count +1;
2938 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2939 l_time_where := l_time_where || ' and '||p_TM_alias||'.organization_type = :'||x_bind_count ;
2940 -- l_time_where := l_time_where || ' and '||p_TM_alias||'.organization_type = ''' || l_org_dimension_level ||''' ';
2941
2942 end if;
2943
2944 if nvl(p_ak_region_rec.disable_viewby,'N') <> 'Y' and p_TM_alias <> 'VBT' then
2945 if l_org_dimension_level_value is not null and length(l_org_dimension_level_value) > 0
2946 and p_viewby_dimension = 'TIME' then
2947 l_time_where := l_time_where || ' and VBT.organization_id';
2948 if instrb(l_org_dimension_level_value,''',''') > 0 then
2949
2950 l_split_string := '';
2951 splitMultipleVariables(replace (l_org_dimension_level_value, '''', null),
2952 x_bind_variables, x_bind_count, l_split_string);
2953 l_time_where := l_time_where || ' in ('||l_split_string||')';
2954
2955 --l_time_where := l_time_where || ' in (' || l_org_dimension_level_value || ')';
2956 else
2957 x_bind_variables := x_bind_variables || SEPERATOR||l_org_dimension_level_value;
2958 x_bind_count := x_bind_count +1;
2959 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2960 l_time_where := l_time_where || ' = :'||x_bind_count ;
2961 --l_time_where := l_time_where || ' = ' || l_org_dimension_level_value;
2962 end if;
2963 x_bind_variables := x_bind_variables || SEPERATOR||l_org_dimension_level;
2964 x_bind_count := x_bind_count +1;
2965 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2966 l_time_where := l_time_where || ' and VBT.organization_type = :'||x_bind_count;
2967 --l_time_where := l_time_where || ' and VBT.organization_type = '''|| l_org_dimension_level || '''';
2968 end if;
2969 end if;
2970 end if;
2971
2972 if p_TM_alias <> 'VBT' then
2973 l_time_where := l_time_where || ' and SV.' || p_save_region_item_rec.base_column
2974 || ' = '||p_TM_alias||'.' || p_time_id_name;
2975 end if;
2976
2977 end if;
2978
2979 if p_parameter_rec.parameter_description is not null
2980 and length(p_parameter_rec.parameter_description) > 0
2981 and upper(p_parameter_rec.parameter_description) <> gvAll then
2982
2983 if (substr(p_region_code, length(p_region_code)-length('_BALANCES')+1) = '_BALANCES'
2984 and p_viewby_dimension not in ('TIME', 'EDW_TIME_M')) then
2985 if substr(p_parameter_rec.parameter_name, length(p_parameter_rec.parameter_name)-length('_TO')+1) = '_TO' then
2986 x_bind_variables := x_bind_variables || SEPERATOR||to_char(p_parameter_rec.period_date, 'DD-MON-YYYY');
2987 x_bind_count := x_bind_count +1;
2988 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2989 l_time_where := l_time_where || ' and '||p_TM_alias||'.end_date = to_date( :'||x_bind_count||' ,''DD-MON-YYYY'') ';
2990 end if;
2991 else
2992 if substr(p_parameter_rec.parameter_name, length(p_parameter_rec.parameter_name)-length('_FROM')+1) = '_FROM' then
2993 x_bind_variables := x_bind_variables || SEPERATOR||to_char(p_parameter_rec.period_date,'DD-MON-YYYY');
2994 x_bind_count := x_bind_count +1;
2995 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
2996 l_time_where := l_time_where || ' and '||p_TM_alias||'.start_date >= to_date( :'||x_bind_count||' ,''DD-MON-YYYY'') ';
2997 elsif substr(p_parameter_rec.parameter_name, length(p_parameter_rec.parameter_name)-length('_TO')+1) = '_TO' then
2998 x_bind_variables := x_bind_variables || SEPERATOR||to_char(p_parameter_rec.period_date,'DD-MON-YYYY');
2999 x_bind_count := x_bind_count +1;
3000 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
3001 l_time_where := l_time_where || ' and '||p_TM_alias||'.end_date <= to_date( :' ||x_bind_count||' ,''DD-MON-YYYY'') ';
3002 end if;
3003 end if;
3004
3005 if instrb(p_parameter_rec.parameter_description,'(') > 0 then
3006 l_time_where := l_time_where || BIS_PMV_QUERY_PVT.GET_TIME_LABEL_WHERE(p_parameter_rec.parameter_description,
3007 p_time_value_name,
3008 p_TM_alias,
3009 x_bind_variables,
3010 --x_bind_indexes,
3011 x_bind_count);
3012 end if;
3013
3014 end if;
3015
3016 return l_time_where;
3017 end GET_TIME_WHERE;
3018
3019 function GET_TIME_LABEL_WHERE(p_parameter_description in VARCHAR2,
3020 p_time_value_name in VARCHAR2,
3021 p_TM_alias in VARCHAR2,
3022 x_bind_variables in OUT NOCOPY VARCHAR2,
3023 --x_bind_indexes in OUT NOCOPY VARCHAR2,
3024 x_bind_count in out NOCOPY number)
3025 return varchar2 is
3026 l_time_label_where varchar2(2000);
3027 l_start_pos number;
3028 l_end_pos number;
3029 l_time_label varchar2(2000);
3030 begin
3031 l_start_pos := instr(p_parameter_description,'(');
3032 l_end_pos := instr(p_parameter_description,')');
3033 l_time_label := substr(p_parameter_description, l_start_pos, l_end_pos-l_start_pos+1);
3034 if l_time_label is not null and length(l_time_label) > 0 then
3035 x_bind_variables := x_bind_variables || SEPERATOR||'%'||l_time_label||'%';
3036 x_bind_count := x_bind_count +1;
3037 --x_bind_indexes := x_bind_indexes || SEPERATOR|| x_bind_count;
3038 l_time_label_where := ' and '||p_TM_alias||'.'||p_time_value_name||' like :'||x_bind_count;
3039 end if;
3040 return l_time_label_where;
3041 end GET_TIME_LABEL_WHERE;
3042
3043 function GET_NON_TIME_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
3044 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
3045 p_source in varchar2 default null,
3046 x_bind_variables in OUT NOCOPY VARCHAR2,
3047 --x_bind_indexes in OUT NOCOPY VARCHAR2,
3048 x_bind_count in out NOCOPY number)
3049 return varchar2 is
3050 l_non_time_where varchar2(2000);
3051 l_split_string VARCHAR2(80);
3052 begin
3053 if p_parameter_rec.parameter_description is not null
3054 and length(p_parameter_rec.parameter_description) > 0
3055 and upper(p_parameter_rec.parameter_description) <> gvAll then
3056 if (p_source = 'FOR_DBC') then
3057 l_non_time_where := l_non_time_where || ' and ' || p_save_region_item_rec.base_column || ' ';
3058 else
3059 l_non_time_where := l_non_time_where || ' and SV.' || p_save_region_item_rec.base_column || ' ';
3060 end if;
3061 if p_parameter_rec.operator is not null and length(p_parameter_rec.operator) > 0 then
3062 if instrb(p_parameter_rec.parameter_value, ''',''') > 0 then
3063 if ltrim(rtrim(p_parameter_rec.operator)) = '!=' then
3064 l_non_time_where := l_non_time_where || 'not in ';
3065 elsif ltrim(rtrim(p_parameter_rec.operator)) = '=' then
3066 l_non_time_where := l_non_time_where || ' in ';
3067 else
3068 l_non_time_where := l_non_time_where || p_parameter_rec.operator || ' ';
3069 end if;
3070
3071 l_split_string := '';
3072 splitMultipleVariables(replace (p_parameter_rec.parameter_value, '''', null),
3073 x_bind_variables, x_bind_count, l_split_string);
3074 l_non_time_where := l_non_time_where || '('||l_split_string||')';
3075 else
3076 x_bind_variables := x_bind_variables || SEPERATOR||p_parameter_rec.parameter_value;
3077 x_bind_count := x_bind_count +1;
3078 --x_bind_indexes := x_bind_indexes ||SEPERATOR|| x_bind_count;
3079 l_non_time_where := l_non_time_where||p_parameter_rec.operator||' :'||x_bind_count;
3080 end if;
3081 else
3082 if instrb(p_parameter_rec.parameter_value, ''',''') > 0 then
3083 l_split_string := '';
3084 splitMultipleVariables(replace (p_parameter_rec.parameter_value, '''', null),
3085 x_bind_variables,x_bind_count, l_split_string);
3086 l_non_time_where := l_non_time_where || ' in ('||l_split_string||')';
3087 else
3088 -- replace the quotes replace
3089 -- x_bind_variables := x_bind_variables || SEPERATOR||p_parameter_rec.parameter_value;
3090 x_bind_variables := x_bind_variables || SEPERATOR|| replace (p_parameter_rec.parameter_value, '''', null); --strip the quotes
3091 x_bind_count := x_bind_count +1;
3092 --x_bind_indexes := x_bind_indexes || SEPERATOR|| x_bind_count;
3093 l_non_time_where := l_non_time_where || ' = :'||x_bind_count;
3094 end if;
3095 end if;
3096 end if;
3097 return l_non_time_where;
3098 end GET_NON_TIME_WHERE;
3099
3100 function GET_NON_DIMENSION_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
3101 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
3102 x_bind_variables in OUT NOCOPY VARCHAR2,
3103 --x_bind_indexes in OUT NOCOPY VARCHAR2,
3104 x_bind_count in out NOCOPY number)
3105 return varchar2 is
3106 l_non_dimension_where varchar2(2000);
3107 begin
3108 if p_parameter_rec.parameter_description is not null
3109 and length(p_parameter_rec.parameter_description) > 0
3110 and p_save_region_item_rec.base_column is not null
3111 and length(p_save_region_item_rec.base_column) > 0 then
3112 if p_parameter_rec.operator is not null and length(p_parameter_rec.operator) > 0 then
3113 x_bind_variables := x_bind_variables || SEPERATOR||p_parameter_rec.parameter_description;
3114 x_bind_count := x_bind_count +1;
3115 --x_bind_indexes := x_bind_indexes || SEPERATOR||x_bind_count;
3116 l_non_dimension_where := l_non_dimension_where || ' and SV.' || p_save_region_item_rec.base_column || ' '
3117 || p_parameter_rec.operator || ' :' ||x_bind_count;
3118 --|| p_parameter_rec.operator || ' ' || p_parameter_rec.parameter_description;
3119 else
3120 if p_save_region_item_rec.data_type = 'D' then
3121 x_bind_variables := x_bind_variables || SEPERATOR||p_parameter_rec.parameter_description;
3122 x_bind_count := x_bind_count +1;
3123 --x_bind_indexes := x_bind_indexes || SEPERATOR||x_bind_count;
3124 l_non_dimension_where := l_non_dimension_where || ' and upper(ltrim(rtrim(to_char(SV.'
3125 || p_save_region_item_rec.base_column || ',''DD-MON-YY'')))) = upper(ltrim(rtrim(to_char(to_'||'date(:'||x_bind_count||'), ''DD-MON-YY'')))) ';
3126 --|| p_save_region_item_rec.base_column || ',''DD-MON-YY'')))) = upper(ltrim(rtrim(to_char(to_date('|| p_parameter_rec.parameter_description || '), ''DD-MON-YY'')))) ';
3127 else
3128 x_bind_variables := x_bind_variables || SEPERATOR||p_parameter_rec.parameter_description;
3129 x_bind_count := x_bind_count +1;
3130 --x_bind_indexes := x_bind_indexes || SEPERATOR||x_bind_count;
3131 l_non_dimension_where := l_non_dimension_where || ' and upper(ltrim(rtrim(SV.'
3132 || p_save_region_item_rec.base_column || '))) like upper(ltrim(rtrim( :'||x_bind_count||' ))) ';
3133 --|| p_save_region_item_rec.base_column || '))) like upper(ltrim(rtrim('''
3134 --|| p_parameter_rec.parameter_description || '''))) ';
3135 end if;
3136 end if;
3137 end if;
3138 return l_non_dimension_where;
3139 end GET_NON_DIMENSION_WHERE;
3140
3141 function GET_LOV_WHERE(p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
3142 p_where_clause in VARCHAR2,
3143 p_region_code in VARCHAR2 )
3144 return varchar2 is
3145 l_lov_where varchar2(2000) := p_where_clause;
3146 -- Fix for bug 2763327
3147 -- Initialize l_index1 and l_index2
3148 l_index1 number := 1;
3149 l_index2 number := 1;
3150 l_attribute_code varchar2(2000);
3151 l_attribute2 varchar2(2000);
3152 l_parameter_name varchar2(2000);
3153 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
3154 l_parameter_value varchar2(2000);
3155 l_parameter_description varchar2(2000);
3156 begin
3157 -- Fix for bug 2763327
3158 -- Added white space before appending the retrieved lov where
3159 l_lov_where := ' ' || p_where_clause ;
3160
3161 loop
3162 l_index1 := instr(l_lov_where, '{', l_index1, 1);
3163 l_index2 := instr(l_lov_where, '}', l_index1+1, 1);
3164 if l_index1 = 0 or l_index2 = 0 then
3165 exit;
3166 end if;
3167 l_attribute_code := substr(l_lov_where, l_index1+1, l_index2-l_index1-1);
3168 l_attribute2 := BIS_PARAMETER_VALIDATION.getDimensionForAttribute(rtrim(ltrim(l_attribute_code)), p_region_code);
3169 l_parameter_name := nvl(l_attribute2, l_attribute_code);
3170
3171 if p_parameter_tbl.COUNT > 0 then
3172 for i in p_parameter_tbl.FIRST..p_parameter_tbl.LAST loop
3173 l_parameter_rec := p_parameter_tbl(i);
3174 if l_parameter_rec.parameter_name = l_parameter_name then
3175 l_parameter_value := l_parameter_rec.parameter_value;
3176 l_parameter_description := l_parameter_rec.parameter_description;
3177 exit;
3178 end if;
3179 end loop;
3180 end if;
3181
3182 if l_attribute2 is not null then
3183 l_lov_where := replace(l_lov_where, '{'||l_attribute_code||'}','('||l_parameter_value||')');
3184 else
3185 l_lov_where := replace(l_lov_where, '{'||l_attribute_code||'}','('||l_parameter_description||')');
3186 end if;
3187
3188 l_index1 := l_index2+1;
3189 end loop;
3190
3191 l_lov_where := replace(l_lov_where, ' and ', ' and vbt.');
3192 l_lov_where := replace(l_lov_where, ' AND ', ' and vbt.');
3193 l_lov_where := replace(l_lov_where, ' And ', ' and vbt.');
3194 l_lov_where := replace(l_lov_where, ' or ', ' or vbt.');
3195 l_lov_where := replace(l_lov_where, ' OR ', ' or vbt.');
3196 l_lov_where := replace(l_lov_where, ' Or ', ' or vbt.');
3197 l_lov_where := replace(l_lov_where, ' in (All)',' is not null');
3198 l_lov_where := replace(l_lov_where, ' IN (All)',' is not null');
3199 l_lov_where := replace(l_lov_where, ' In (All)',' is not null');
3200 l_lov_where := replace(l_lov_where, ' in ()',' is not null');
3201 l_lov_where := replace(l_lov_where, ' IN ()',' is not null');
3202 l_lov_where := replace(l_lov_where, ' In ()',' is not null');
3203
3204 return l_lov_where;
3205
3206 end GET_LOV_WHERE;
3207
3208 function GET_GROUP_BY(p_disable_viewby in VARCHAR2,
3209 p_viewby_id_name in VARCHAR2,
3210 p_viewby_value_name in VARCHAR2,
3211 p_viewby_dimension in VARCHAR2,
3212 p_viewby_dimension_level in VARCHAR2,
3213 p_extra_groupby in VARCHAR2,
3214 p_user_groupby in VARCHAR2,
3215 p_user_orderby in VARCHAR2,
3216 p_no_target in BOOLEAN DEFAULT TRUE)
3217 return varchar2 is
3218 l_group_by varchar2(2000);
3219 begin
3220
3221 if p_disable_viewby <> 'Y' then
3222 l_group_by := 'VBT.' || p_viewby_value_name;
3223
3224 if not(p_no_target) then
3225 l_group_by := l_group_by || ', VBT.' || p_viewby_id_name;
3226 end if;
3227
3228 if p_extra_groupby is not null and length(p_extra_groupby) > 0 then
3229 l_group_by := l_group_by || ', SV.' || p_extra_groupby;
3230 end if;
3231
3232 if p_viewby_dimension in ('TIME', 'EDW_TIME_M') and p_viewby_dimension_level <> 'EDW_TIME_A' then
3233 l_group_by := l_group_by || ', VBT.start_date';
3234 end if;
3235 end if;
3236
3237 if p_user_groupby is not null and length(p_user_groupby) > 0 then
3238 if p_disable_viewby <> 'Y' then
3239 l_group_by := l_group_by || ', ';
3240 end if;
3241 l_group_by := l_group_by || p_user_groupby;
3242 if p_user_orderby is not null and length(p_user_orderby) > 0 then
3243 l_group_by := l_group_by || ', ' || p_user_orderby;
3244 end if;
3245 end if;
3246
3247 return l_group_by;
3248 end GET_GROUP_BY;
3249
3250 function GET_ORDER_BY(p_disable_viewby in VARCHAR2,
3251 p_sort_attribute in VARCHAR2,
3252 p_sort_direction in VARCHAR2,
3253 p_viewby_dimension in VARCHAR2,
3254 p_viewby_dimension_level in VARCHAR2,
3255 p_default_sort_attribute in VARCHAR2,
3256 p_user_orderby in VARCHAR2)
3257 return varchar2 is
3258 l_order_by varchar2(2000);
3259 l_sort_attribute varchar2(2000) := p_sort_attribute;
3260 l_sort_direction varchar2(2000) := p_sort_direction;
3261 l_nls_sort_type VARCHAR2(30);
3262 begin
3263
3264 if p_sort_direction is null or length(p_sort_direction) = 0 then
3265 l_sort_direction := 'ASC';
3266 else
3267 l_sort_direction := p_sort_direction;
3268 end if;
3269
3270 if p_sort_attribute is null or length(p_sort_attribute) = 0 then
3271 l_sort_attribute := p_default_sort_attribute; -- cannot be null
3272 else
3273 l_sort_attribute := p_sort_attribute || ' '|| l_sort_direction;
3274 end if;
3275
3276 --serao- 07/31/2002 - bug 2460600 - check the final sort attribute for viewby
3277 if (p_disable_viewby <> 'Y' and rtrim(ltrim(nvl(p_sort_attribute, l_sort_attribute))) = 'VIEWBY') then
3278 if p_viewby_dimension in ('TIME', 'EDW_TIME_M') and p_viewby_dimension_level <> 'EDW_TIME_A' then
3279 l_sort_attribute := 'VBT.start_date '|| l_sort_direction;
3280 else --NLS Sort for VIEWBY
3281 l_nls_sort_type := fnd_profile.value('ICX_NLS_SORT');
3282 IF l_nls_sort_type IS NOT NULL THEN
3283 l_sort_attribute := ' NLSSORT('||'VIEWBY'||', ''NLS_SORT = ' || l_nls_sort_type ||''') ' || l_sort_direction;
3284 END IF;
3285 end if;
3286 end if;
3287
3288 l_order_by := l_sort_attribute;
3289
3290 if p_user_orderby is not null and length(p_user_orderby) > 0 then
3291 l_order_by := l_order_by || ', ' || p_user_orderby;
3292 end if;
3293
3294 return l_order_by;
3295
3296 end GET_ORDER_BY;
3297
3298 function GET_USER_STRING(p_user_string in VARCHAR2)
3299 return varchar2 is
3300 l_user_string varchar2(2000) := p_user_string;
3301 l_user_function varchar2(2000);
3302 begin
3303 if substr(p_user_string,1,1)='[' and substr(p_user_string,length(p_user_string),1)=']' then
3304 l_user_function := 'SELECT ' || substr(p_user_string, 2, length(p_user_string)-2) || ' FROM DUAL';
3305 execute immediate l_user_function into l_user_string;
3306 end if;
3307 l_user_string := replace(l_user_string, '<VIEW_NAME.>', 'SV.');
3308 return l_user_string;
3309 end GET_USER_STRING;
3310 procedure sort
3311 (pSortNameTbl in out NOCOPY BISVIEWER.t_char
3312 ,pSortValueTbl in out NOCOPY BISVIEWER.t_char
3313 )
3314 IS
3315 l_temp_value varchar2(2000);
3316 l_temp_name varchar2(2000);
3317 BEGIN
3318 for i in pSortNameTbl.FIRST+1..pSortNameTbl.LAST loop
3319 l_temp_name := pSortNameTbl(i);
3320 l_temp_value := pSortValueTbl(i);
3321 for j in pSortNameTbl.FIRST..(i-1) loop
3322 if (pSortNameTbl(j) > l_temp_name) then
3323 pSortNameTbl(j+1) := pSortNameTbl(j);
3324 pSortNameTbl(j) := l_temp_name;
3325 pSortValueTbl(j+1) := pSortValueTbl(j);
3326 pSortValueTbl(j) := l_temp_value;
3327 end if;
3328 end loop;
3329 end loop;
3330 END;
3331
3332 procedure get_customized_order_by(p_viewby in varchar2,
3333 p_attribute_code in varchar2,
3334 p_region_code in varchar2,
3335 p_user_id in varchar2,
3336 p_customization_code in varchar2,
3337 p_main_order_by in out NOCOPY varchar2,
3338 p_first_order_by in out NOCOPY varchar2,
3339 p_second_order_by in out NOCOPY varchar2)
3340 is
3341 cursor c_orderby(cp_cust_code varchar2, cp_attribute_code varchar2, cp_property1 varchar2, cp_property2 varchar2) is
3342 select property_varchar2_value,property_name,attribute_code
3343 from ak_custom_region_items_vl
3344 where property_name in (cp_property1,cp_property2)
3345 and region_code = p_region_code
3346 and customization_code = cp_cust_code
3347 and attribute_code = cp_attribute_code
3348 order by property_name;
3349 l_cust_code varchar2(100);
3350 main_attribute_code varchar2(100);
3351 first_attribute_code varchar2(100);
3352 second_attribute_code varchar2(100);
3353 l_property_name varchar2(80);
3354 l_property_value varchar2(80);
3355 l_attribute_code varchar2(80);
3356 l_user_id varchar2(80);
3357 begin
3358 --Performance Fix 2463060 adding order direction and initial sort sequence as bind variables
3359 for order_clause in c_orderby(p_customization_code,p_attribute_code,'ORDER_DIRECTION','INITIAL_SORT_SEQUENCE') loop
3360 l_property_name := order_clause.property_name;
3361 l_property_value := order_clause.property_varchar2_value;
3362 if l_property_name ='INITIAL_SORT_SEQUENCE' then
3363 if p_viewby = 'Y' then
3364 if l_property_value = '0' then
3365 p_main_order_by := ' '|| ' VIEWBY '||' ';
3366 main_attribute_code := p_attribute_code;
3367 elsif l_property_value = '1' then
3368 p_first_order_by := ' , '|| ' VIEWBY '||' ';
3369 first_attribute_code := p_attribute_code;
3370 elsif l_property_value = '2' then
3371 p_second_order_by := ' , '||' VIEWBY ' ||' ';
3372 second_attribute_code := p_attribute_code;
3373 end if;
3374 else
3375 if l_property_value = '0' then
3376 p_main_order_by := ' '|| p_attribute_code ||' ';
3377 main_attribute_code := p_attribute_code;
3378 elsif l_property_value = '1' then
3379 p_first_order_by := ' , '|| p_attribute_code ||' ';
3380 first_attribute_code := p_attribute_code;
3381 elsif l_property_value = '2' then
3382 p_second_order_by := ' , '|| p_attribute_code ||' ';
3383 second_attribute_code := p_attribute_code;
3384 end if;
3385 end if;
3386 end if;
3387 if l_property_name ='ORDER_DIRECTION' then
3388 if p_attribute_code = main_attribute_code and l_property_value = 'ascending' then
3389 p_main_order_by := p_main_order_by||' ASC ';
3390 elsif p_attribute_code = main_attribute_code then
3391 p_main_order_by := p_main_order_by||' DESC ';
3392 elsif p_attribute_code = first_attribute_code and l_property_value = 'ascending' then
3393 p_first_order_by := p_first_order_by||' ASC ';
3394 elsif p_attribute_code = first_attribute_code then
3395 p_first_order_by := p_first_order_by||' DESC ';
3396 elsif p_attribute_code = second_attribute_code and l_property_value = 'ascending' then
3397 p_second_order_by := p_second_order_by||' ASC ';
3398 elsif p_attribute_code = first_attribute_code then
3399 p_second_order_by := p_second_order_by||' DESC ';
3400 end if;
3401 end if;
3402 end loop;
3403
3404 end get_customized_order_by;
3405
3406 /** Procedure to retun thre order by clause for given metadata and parmeters */
3407 procedure get_order_by_clause(
3408 p_source in varchar2 DEFAULT 'REPORT',
3409 pAKRegionRec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
3410 pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
3411 p_sort_attribute in VARCHAR2 DEFAULT NULL,
3412 p_sort_direction in VARCHAR2 DEFAULT NULL,
3413 p_viewby_attribute2 IN VARCHAR2,
3414 p_viewby_dimension IN VARCHAR2,
3415 p_viewby_dimension_level In VARCHAR2,
3416 x_order_by OUT NOCOPY VARCHAR2
3417
3418 ) IS
3419 l_ak_region_item_rec BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC;
3420 l_view_by_attr2 VARCHAR2(80);
3421 l_view_by_attr_code VARCHAR2(80);
3422 l_main_order_by VARCHAR2(100) :=NULL;
3423 l_first_order_by VARCHAR2(100) := NULL;
3424 l_second_order_by VARCHAR2(100) := NULL;
3425 l_def_sort_attr_tbl BISVIEWER.t_char;
3426 l_Def_sort_seq_tbl BISVIEWER.t_Char;
3427 l_sort_attr_code VARCHAR2(150);
3428 --changed l_sort_attr_code size from 30 to 150 for bugfix 2598917
3429 l_def_sort_count NUMBER := 1;
3430 l_default_sort_attribute VARCHAR2(2000) := '';
3431 l_first_attr_code VARCHAR2(2000);
3432 l_first_time BOOLEAN := TRUE;
3433 l_sort_attr_type VARCHAR2(2000);
3434 l_sel_sort_attribute VARCHAR2(2000);
3435 l_user_order_by VARCHAR2(2000);
3436
3437 l_nls_sort_type VARCHAR2(30); -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
3438
3439
3440 -- this is for the order gy clause which is independent of the parameter data
3441 CURSOR ak_region_item_cursor (cpRegionCode VARCHAR2) IS
3442 SELECT attribute1 attribute_type,
3443 attribute_code,
3444 attribute2,
3445 attribute3 base_column,
3446 attribute4 where_clause,
3447 attribute15 lov_table,
3448 attribute9 aggregate_function,
3449 attribute14 data_type,
3450 attribute7 data_format,
3451 order_sequence,
3452 order_direction,
3453 node_query_flag
3454 ,node_display_flag --2371922
3455 FROM AK_REGION_ITEMS
3456 WHERE region_code = cpRegionCode
3457 AND nested_region_code is null
3458 AND node_query_flag = 'N'
3459 AND ( order_direction IS NOT NULL OR order_sequence IS NOT NULL ) --Bug Fix 2605121
3460 ORDER BY display_sequence;
3461
3462 --serao - 2622281 - adding a new cursor which will select if non-view-by
3463 CURSOR ak_item_non_viewby_cursor (cpRegionCode VARCHAR2) IS
3464 SELECT attribute1 attribute_type,
3465 attribute_code,
3466 attribute2,
3467 attribute3 base_column,
3468 attribute4 where_clause,
3469 attribute15 lov_table,
3470 attribute9 aggregate_function,
3471 attribute14 data_type,
3472 attribute7 data_format,
3473 order_sequence,
3474 order_direction,
3475 node_query_flag
3476 ,node_display_flag --2371922
3477 FROM AK_REGION_ITEMS
3478 WHERE region_code = cpRegionCode
3479 AND nested_region_code is null
3480 AND node_query_flag = 'N'
3481 ORDER BY display_sequence;
3482
3483 BEGIN
3484
3485 l_nls_sort_type := fnd_profile.value('ICX_NLS_SORT'); -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
3486
3487 --serao - 2622281 - adding a new cursor which will select if non-view-by
3488 IF nvl(pAKRegionRec.disable_viewby,'N') <> 'Y' THEN
3489 IF ak_region_item_cursor%ISOPEN THEN
3490 CLOSE ak_region_item_cursor;
3491 END IF;
3492 OPEN ak_region_item_cursor(pUserSession.region_code);
3493 ELSE
3494 IF ak_item_non_viewby_cursor%ISOPEN THEN
3495 CLOSE ak_item_non_viewby_cursor;
3496 END IF;
3497 OPEN ak_item_non_viewby_cursor(pUserSession.region_code);
3498 END IF;
3499
3500 LOOP
3501
3502 --serao - 2622281 - adding a new cursor which will select if non-view-by
3503 IF nvl(pAKRegionRec.disable_viewby,'N') <> 'Y' THEN
3504 FETCH ak_region_item_cursor INTO l_ak_region_item_rec;
3505 EXIT WHEN ak_region_item_cursor%NOTFOUND;
3506 ELSE
3507 FETCH ak_item_non_viewby_cursor INTO l_ak_region_item_rec;
3508 EXIT WHEN ak_item_non_viewby_cursor%NOTFOUND;
3509 END IF;
3510
3511 IF (p_Sort_Attribute IS NOT NULL AND p_Sort_Attribute=l_ak_region_item_Rec.attribute_code) THEN
3512 l_sort_attr_type := l_ak_region_item_rec.data_type;
3513 END IF;
3514
3515 IF (l_first_time) THEN
3516 IF nvl(pAKRegionRec.disable_viewby,'N') <> 'Y' THEN
3517 l_first_attr_code := 'VIEWBY';
3518 l_first_time := false;
3519 -- ELSIF (l_ak_Region_item_rec.node_query_flag = 'N') THEN
3520 ELSIF (l_ak_Region_item_rec.node_query_flag = 'N' and l_ak_Region_item_rec.node_display_flag = 'Y') THEN
3521 IF (l_ak_region_item_rec.data_type = 'D') THEN
3522 l_first_attr_code := l_ak_region_item_rec.attribute_code;
3523 ELSIF (l_ak_region_item_rec.data_type = 'C') THEN --NLS Sort for VARCHAR2
3524 IF l_nls_sort_type IS NOT NULL THEN
3525 l_first_attr_code := ' NLSSORT('||l_ak_region_item_rec.attribute_code||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
3526 ELSE
3527 l_first_attr_code := l_ak_region_item_rec.attribute_code;
3528 END IF;
3529 ELSE
3530 l_first_attr_code := l_ak_region_item_rec.attribute_code;
3531 END IF;
3532 l_first_time := false;
3533 END IF;
3534 END IF;
3535
3536 if gvCode is not null and length(gvCode) > 0 then
3537
3538 IF l_ak_region_item_rec.attribute2 = p_viewby_attribute2 then
3539 -- for the view by attribute
3540 BIS_PMV_QUERY_PVT.get_customized_order_by(p_viewby =>'Y',
3541 p_attribute_code =>l_ak_region_item_rec.attribute_code,
3542 p_region_code => pUserSession.region_code,
3543 p_user_id => pUserSession.user_id,
3544 p_customization_code =>gvCode,
3545 p_main_order_by => l_main_order_by,
3546 p_first_order_by => l_first_order_by,
3547 p_second_order_by => l_second_order_by);
3548 END IF;
3549
3550 -- for the measure
3551 IF (l_ak_region_item_rec.attribute_type = 'MEASURE'
3552 or l_ak_region_item_rec.attribute_type = 'MEASURE_NOTARGET'
3553 OR (l_ak_region_item_rec.attribute_type is null AND l_ak_region_item_rec.node_query_flag = 'N')) THEN
3554
3555 BIS_PMV_QUERY_PVT.get_customized_order_by(p_viewby =>'N',
3556 p_attribute_code =>l_ak_region_item_rec.attribute_code,
3557 p_region_code => pUserSession.region_code,
3558 p_user_id => pUserSession.user_id,
3559 p_customization_code =>gvCode,
3560 p_main_order_by => l_main_order_by,
3561 p_first_order_by => l_first_order_by,
3562 p_second_order_by => l_second_order_by);
3563 END IF;
3564
3565 end if;
3566
3567 --Set up the order by info
3568 IF (l_ak_region_item_rec.order_sequence is not null AND
3569 l_ak_region_item_rec.order_Sequence < 100) AND
3570 l_ak_region_item_rec.node_query_flag = 'N' THEN
3571
3572 IF (l_ak_region_item_rec.data_type = 'C') THEN -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
3573 IF l_nls_sort_type IS NOT NULL THEN
3574 l_sort_attr_code := ' NLSSORT('||l_ak_region_item_rec.attribute_code||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
3575 ELSE
3576 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
3577 END IF;
3578 ELSIF (l_ak_region_item_rec.data_type = 'D') THEN
3579 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
3580 ELSE
3581 l_sort_attr_code := l_ak_region_item_rec.attribute_code;
3582 END IF;
3583 l_def_sort_attr_tbl(l_def_sort_count) := l_sort_attr_code||' '||
3584 l_ak_region_item_rec.order_direction;
3585 l_Def_sort_seq_tbl(l_def_sort_count) := l_ak_region_item_rec.order_sequence;
3586 l_def_sort_count := l_def_sort_count+1;
3587 END IF;
3588
3589 END LOOP;
3590
3591 IF (l_first_attr_code IS NULL AND nvl(pAKRegionRec.disable_viewby,'N') <> 'Y' )THEN
3592 l_first_attr_code := 'VIEWBY';
3593 END IF;
3594
3595 IF l_main_order_by IS NULL THEN
3596
3597 IF (l_def_sort_attr_tbl.COUNT > 0) THEN
3598 --Sort the specified order
3599 IF (l_def_sort_attr_tbl.COUNT > 1) THEN
3600 BIS_PMV_QUERY_PVT.sort(l_def_sort_seq_tbl, l_def_sort_attr_tbl);
3601 END IF;
3602 FOR i in l_def_sort_seq_tbl.FIRST..l_def_sort_seq_tbl.LAST LOOP
3603 l_default_sort_attribute := ' '|| l_default_sort_attribute || l_def_sort_attr_tbl(i)||',';
3604 END LOOP;
3605 l_default_sort_attribute := substr(l_default_sort_attribute, 1, length(l_default_sort_attribute)-1);
3606 ELSE
3607 l_default_sort_attribute := l_first_attr_code;
3608 END IF;
3609
3610 --construct order by string
3611 IF (p_source <> 'ACTUAL') THEN
3612 IF (l_sort_attr_type = 'C' AND p_sort_attribute IS NOT NULL) THEN -- nbarik 19-SEP-2002 NLS Sort for VARCHAR2
3613 IF l_nls_sort_type IS NOT NULL THEN
3614 l_sel_sort_attribute := ' NLSSORT('||p_sort_Attribute||', ''NLS_SORT = ' || l_nls_sort_type ||''') ';
3615 ELSE
3616 l_sel_sort_attribute := p_sort_attribute;
3617 END IF;
3618 ELSIF (l_sort_attr_type = 'D' and p_sort_attribute is not null) then
3619 l_sel_sort_attribute := p_sort_Attribute;
3620 ELSE
3621 l_sel_sort_attribute := p_sort_attribute;
3622 END IF;
3623
3624 IF pAKRegionRec.user_orderby IS NOT NULL THEN
3625 l_user_order_by := BIS_PMV_QUERY_PVT.GET_USER_STRING(pAKRegionRec.user_orderby);
3626 END IF;
3627
3628 --x_order_by := ' ORDER BY ';
3629 x_order_by := x_order_by
3630 || BIS_PMV_QUERY_PVT.GET_ORDER_BY
3631 (p_disable_viewby => nvl(pAKRegionRec.disable_viewby,'N'),
3632 p_sort_attribute => l_sel_sort_attribute,
3633 p_sort_direction => p_sort_direction,
3634 p_viewby_dimension => p_viewby_dimension,
3635 p_viewby_dimension_level => p_viewby_dimension_level,
3636 p_default_sort_attribute => l_default_sort_attribute,
3637 p_user_orderby => l_user_order_by);
3638 END IF;
3639
3640 ELSE
3641 x_order_by := l_main_order_by;
3642 END IF ; -- if main_order_by
3643 EXCEPTION
3644 WHEN OTHERS THEN
3645 NULL;
3646 END get_order_by_clause;
3647
3648 /** Procedure to replace the start and end index substitution variables */
3649 --serao- bug 2642688 -10/25/02 - use bind variables
3650 procedure replaceStartEndIndex (
3651 p_custom_sql IN OUT NOCOPY VARCHAR2,
3652 p_lower_bound In INTEGER,
3653 p_upper_bound In INTEGER,
3654 p_original_sql in varchar2,
3655 x_bind_variables IN OUT NOCOPY VARCHAR2,
3656 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
3657 x_bind_indexes IN OUT NOCOPY VARCHAR2,
3658 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
3659 x_bind_count IN OUT NOCOPY NUMBER
3660 ) IS
3661 l_index NUMBER;
3662 BEGIN
3663
3664
3665 l_index := instrb(p_original_sql, START_INDEX_SUBST_VAR);
3666 if (l_index > 0) then
3667
3668 replace_with_bind_variables
3669 (p_search_string => START_INDEX_SUBST_VAR,
3670 p_bind_value => p_lower_bound,
3671 p_bind_Datatype => BIS_PMV_PARAMETERS_PUB.INTEGER_BIND,
3672 p_initial_index => l_index,
3673 p_bind_function => NULL,
3674 p_bind_to_date => 'N',
3675 p_original_sql => p_original_sql,
3676 x_custom_sql => p_custom_sql,
3677 x_bind_variables => x_bind_variables,
3678 x_plsql_bind_variables => x_plsql_bind_variables,
3679 x_bind_indexes => x_bind_indexes,
3680 x_bind_datatypes => x_bind_datatypes,
3681 x_bind_count => x_bind_count);
3682 END IF;
3683
3684
3685 l_index := instrb(p_original_sql, END_INDEX_SUBST_VAR);
3686 if (l_index > 0) then
3687
3688 replace_with_bind_variables
3689 (p_search_string => END_INDEX_SUBST_VAR,
3690 p_bind_value => p_upper_bound,
3691 p_bind_Datatype => BIS_PMV_PARAMETERS_PUB.INTEGER_BIND,
3692 p_initial_index => l_index,
3693 p_bind_function => NULL,
3694 p_bind_to_date => 'N',
3695 p_original_sql => p_original_sql,
3696 x_custom_sql => p_custom_sql,
3697 x_bind_variables => x_bind_variables,
3698 x_plsql_bind_variables => x_plsql_bind_variables,
3699 x_bind_indexes => x_bind_indexes,
3700 x_bind_datatypes => x_bind_datatypes,
3701 x_bind_count => x_bind_count);
3702 END IF;
3703
3704 END replaceStartEndIndex;
3705
3706 /** function to return the description of the param from fnd lookup */
3707 FUNCTION getParameterAcronym (
3708 p_lookup_type IN VARCHAR2,
3709 p_Parameter_name IN VARCHAR2
3710 ) RETURN VARCHAR2 IS
3711 l_Dimlevel_Acronym varchar2(2000);
3712
3713 CURSOR c_lookups IS
3714 SELECt description
3715 FROM fnd_lookup_values_vl
3716 WHERE lookup_Type = p_lookup_type and
3717 (lookup_code||'_FROM' = p_Parameter_name or
3718 lookup_code||'_TO' = p_Parameter_name);
3719 BEGIN
3720
3721 IF c_lookups%ISOPEN then
3722 CLOSE c_lookups;
3723 END IF;
3724 OPEN c_lookups;
3725 FETCH c_lookups into l_dimlevel_acronym;
3726 CLOSE c_lookups;
3727 RETURN l_dimlevel_acronym;
3728 END getParameterAcronym ;
3729
3730 /** Procedure to replace the view by parameter for a given user session rec*/
3731 PROCEDURE process_custom_view_by(
3732 pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
3733 pViewByParam IN VARCHAR2
3734 ) IS
3735 l_return_status VARCHAR2(80);
3736 l_msg_count NUMBER;
3737 l_msg_data VARCHAR2(2000);
3738 l_parameter_rec BIS_PMV_PARAMETERS_PVT.parameter_rec_type ;
3739 l_user_session_rec BIS_PMV_SESSION_PVT.SESSION_REC_TYPE := pUserSession;
3740 BEGIN
3741
3742 l_user_session_rec.page_id := null;
3743 --delete the existing view by
3744 if (l_user_session_Rec.schedule_id is not null) then
3745 BIS_PMV_PARAMETERS_PVT.DELETE_SCHEDULE_PARAMETER
3746 (p_parameter_name => 'VIEW_BY'
3747 ,p_schedule_id => l_user_session_rec.schedule_id
3748 ,x_return_status => l_return_Status
3749 ,x_msg_count => l_msg_count
3750 ,x_msg_data => l_msg_Data
3751 );
3752 else
3753 BIS_PMV_PARAMETERS_PVT.DELETE_PARAMETER( p_user_session_rec =>l_user_session_rec
3754 ,p_parameter_name => 'VIEW_BY'
3755 ,p_schedule_option =>'NULL'
3756 ,x_return_status =>l_return_status
3757 ,x_msg_count => l_msg_count
3758 ,x_msg_data => l_msg_data );
3759 end if;
3760
3761
3762 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3763 RETURN;
3764 END IF;
3765
3766 -- create the param
3767 l_parameter_rec.parameter_name := 'VIEW_BY';
3768 l_parameter_rec.parameter_description := pViewByParam;
3769 l_parameter_rec.parameter_value := pViewByParam;
3770
3771 --insert the new view-by param
3772 if (l_user_Session_Rec.schedule_id is not null) then
3773 insert into bis_user_attributes (user_id, function_name,
3774 session_id, schedule_id, attribute_name,
3775 session_value, session_description,
3776 creation_date, created_by,
3777 last_update_Date, last_updated_by)
3778 VALUES (l_user_session_rec.user_id, l_user_session_rec.function_name,
3779 l_user_session_rec.session_id, l_user_session_rec.schedule_id,
3780 l_parameter_rec.parameter_name,
3781 pViewByParam, pViewbyParam,
3782 sysdate, -1, sysdate, -1);
3783 commit;
3784 else
3785
3786 BIS_PMV_PARAMETERS_PVT.CREATE_PARAMETER(p_user_session_rec =>l_user_session_rec
3787 ,p_parameter_rec => l_parameter_rec
3788 ,x_return_status => l_return_status
3789 ,x_msg_count => l_msg_count
3790 ,x_msg_Data => l_msg_data );
3791 end if;
3792
3793 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3794 RETURN;
3795 END IF;
3796
3797 commit;
3798 END process_custom_view_by;
3799
3800 /** Procedure to process the custom output from a pl/sql procedure call*/
3801 PROCEDURE process_custom_output(
3802 pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
3803 pCustomOutput IN bis_map_tbl,
3804 xCustom_sql OUT NOCOPY VARCHAR2,
3805 x_view_by_value OUT NOCOPY VARCHAR2
3806 ) IS
3807 BEGIN
3808 IF pCustomOutput IS NOT NULL AND pCustomOutput.COUNT >0 THEN
3809 FOR i in pCustomOutput.FIRST..pCustomOutput.LAST LOOP
3810 IF pCustomOutput(i).KEY = QUERY_STR_KEY THEN
3811 xCustom_sql := pCustomOutput(i).VALUE;
3812 ELSIF pCustomOutput(i).KEY = VIEW_BY_KEY THEN
3813 x_view_by_value := pCustomOutput(i).VALUE;
3814 IF (x_view_by_value IS NOT NULL) THEN
3815 process_custom_view_by( pUserSession => pUserSession,
3816 pViewByParam =>x_view_by_value);
3817 END IF;
3818 END IF;
3819 END LOOP;
3820 END IF;
3821 END process_custom_output;
3822
3823
3824 procedure get_custom_sql (p_source in varchar2 DEFAULT 'REPORT',
3825 pAKRegionRec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
3826 pParameterTbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
3827 pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
3828 p_sort_attribute in VARCHAR2 DEFAULT NULL,
3829 p_sort_direction in VARCHAR2 DEFAULT NULL,
3830 p_viewby_attribute2 VARCHAR2,
3831 p_viewby_dimension VARCHAR2,
3832 p_viewby_dimension_level VARCHAR2,
3833 p_lower_bound IN INTEGER DEFAULT 1,
3834 p_upper_bound IN INTEGER DEFAULT -1,
3835 x_sql_string out NOCOPY VARCHAR2,
3836 x_bind_variables OUT NOCOPY VARCHAR2,
3837 x_plsql_bind_variables OUT NOCOPY VARCHAR2,
3838 x_bind_indexes OUT NOCOPY VARCHAR2,
3839 x_bind_Datatypes OUT NOCOPY VARCHAR2,
3840 x_return_Status out NOCOPY VARCHAR2,
3841 x_msg_data OUT NOCOPY varchar2,
3842 x_msg_count OUT NOCOPY NUMBER,
3843 x_view_by_value OUT NOCOPY VARCHAR2)
3844 IS
3845 l_custom_sql varchar2(32000);
3846 l_dynamic_sql_str varchar2(32000);
3847 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
3848 l_lookup_type varchar2(2000) := 'BIS_TIME_LEVEL_VALUES';
3849 l_Dimlevel_Acronym varchar2(2000);
3850 l_where_clause varchar2(2000);
3851 l_prev_start_Date date;
3852 l_prev_start_date_c varchar2(2000);
3853 l_pRev_end_Date date;
3854 l_prev_end_date_c varchar2(2000);
3855 l_temp_start_date varchar2(2000);
3856 l_temp_end_date varchar2(2000);
3857 l_description varchar2(32000);
3858 l_time_id varchar2(32000);
3859 l_save_region_item_rec BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC;
3860 l_bind_count number := 0;
3861 l_where varchar2(32000);
3862 l_bind_variables varchar2(32000);
3863 l_bind_indexes varchar2(32000);
3864 l_asof_date varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
3865 l_asof_date_page varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
3866 l_asof_date_varchar varchar2(2000);
3867 l_prev_asof_date varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
3868 l_prev_asof_date_page varchar2(2000) := to_char(sysdate,'DD-MON-YYYY');
3869 l_time_comparison_type varchar2(2000);
3870 l_time_comparison_page varchar2(2000);
3871 l_time_parameter varchar2(2000);
3872 l_paramlvlparam_Tbl BIS_PMV_PARAMETERS_PVT.parameter_tbl_Type;
3873 l_page_parameter_rec BIS_PMV_PAGE_PARAMETER_REC :=
3874 BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null); --msaran:4600562 - added null for operator
3875 l_page_parameter_tbl BIS_PMV_PAGE_PARAMETER_TBL := BIS_PMV_PAGE_PARAMETER_TBL();
3876 l_query_attributes_tbl BIS_QUERY_ATTRIBUTES_TBL := BIS_QUERY_ATTRIBUTES_TBL();
3877 l_time_comp_value VARCHAR2(2000);
3878 l_index INTEGER;
3879 l_period_type VARCHAR2(2000);
3880 l_order_by VARCHAR2(2000);
3881 l_nested_pattern NUMBER;
3882 l_viewby_attribute2 VARCHAR2(2000);
3883 l_current_report_start_date VARCHAR2(2000) := to_char(sysdate,'DD-MON-YYYY');
3884 l_previous_report_start_date VARCHAR2(2000) := to_char(sysdate,'DD-MON-YYYY');
3885 l_temp_prev_asof_Date VARCHAR2(2000) := to_char(sysdate,'DD-MON-YYYY');
3886 l_temp_date VARCHAR2(2000) := to_char(sysdate, 'DD-MON-YYYY');
3887 l_replace_mode VARCHAR2(1);
3888 CURSOR save_parameter_cursor (cpRegionCode VARCHAR2, cpParameterName VARCHAR2) IS
3889 SELECT attribute2,
3890 attribute3 base_column,
3891 attribute4 where_clause,
3892 attribute14 data_type
3893 FROM AK_REGION_ITEMS
3894 WHERE region_code = cpRegionCode
3895 AND (nvl(attribute2, attribute_code) = cpParameterName
3896 or attribute2||'_FROM' = cpParameterName
3897 or attribute2||'_TO' = cpParameterName)
3898 ORDER BY display_sequence;
3899
3900 l_custom_output bis_map_tbl;
3901
3902 l_original_sql varchar2(32000);
3903
3904 BEGIN
3905 /*
3906 --First get the special page level parameters.
3907 BIS_PMV_PARAMETERS_PVT.RETRIEVE_PARAMLVL_PARAMETERS
3908 (p_user_session_Rec => pusersession
3909 ,x_paramportlet_param_tbl => l_paramlvlparam_tbl
3910 ,x_return_Status => x_return_Status
3911 ,x_msg_count => x_msg_count
3912 ,x_msg_data => x_msg_data
3913 );
3914 */
3915 --First get the custom select
3916 l_page_parameter_tbl.delete;
3917 if (pAKRegionRec.data_source = 'PLSQL_PARAMETERS' OR pAKRegionRec.data_source = 'PLSQL_PROCEDURE_CALL'
3918 or pAKRegionRec.data_source = 'PLSQL_PROCEDURE_QUERYATTRIBUTE') then
3919 l_index := 1;
3920 -- Call the same function by passing all the parameters
3921 if (pParameterTbl.COUNT > 0) then
3922 for i in pParameterTbl.FIRST..pParameterTbl.LAST LOOP
3923 l_parameter_rec := pParameterTbl(i);
3924 if (l_parameter_rec.dimension = 'TIME_COMPARISON_TYPE') then
3925 l_page_parameter_rec.parameter_name := 'TIME_COMPARISON_TYPE';
3926 l_time_Comp_value := substr(l_parameter_rec.parameter_description
3927 ,instr(l_parameter_rec.parameter_description,'+')+1);
3928 l_page_parameter_rec.parameter_id := l_time_comp_value;
3929 l_page_parameter_rec.parameter_value := l_time_comp_value;
3930 -- enh 2467584
3931 -- kiprabha/jprabhud
3932 -- get the l_time_comparison_type
3933 l_time_comparison_type := l_parameter_rec.parameter_description;
3934 elsif (l_parameter_rec.parameter_name = 'AS_OF_DATE') THEN
3935 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
3936 l_page_parameter_rec.parameter_name := l_parameter_rec.parameter_name;
3937 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
3938 -- enh 2467584
3939 -- kiprabha /jprabhud
3940 -- get the l_asof_date
3941 l_asof_date := l_parameter_rec.parameter_description;
3942 elsif (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') THEN
3943 l_temp_prev_asof_date := l_parameter_rec.parameter_description;
3944 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
3945 l_page_parameter_rec.parameter_name := 'BIS_PREVIOUS_ASOF_DATE' ;
3946 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
3947 elsif (l_parameter_rec.parameter_name = 'BIS_CUR_REPORT_START_DATE') THEN
3948 l_page_parameter_Rec.parameter_id := l_parameter_rec.parameter_value;
3949 l_page_parameter_rec.parameter_name := 'BIS_CURRENT_REPORT_START_DATE';
3950 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
3951 l_current_report_start_date := l_parameter_rec.parameter_description;
3952 elsif (l_parameter_rec.parameter_name = 'BIS_PREV_REPORT_START_DATE') THEN
3953 l_page_parameter_Rec.parameter_id := l_parameter_rec.parameter_value;
3954 l_page_parameter_rec.parameter_name := 'BIS_PREVIOUS_REPORT_START_DATE';
3955 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
3956 l_previous_report_start_date := l_parameter_rec.parameter_description;
3957 else
3958 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
3959 l_page_parameter_rec.parameter_name := l_parameter_rec.parameter_name;
3960 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
3961 end if;
3962 l_page_parameter_rec.period_date := l_parameter_rec.period_Date;
3963 l_page_parameter_rec.dimension := l_parameter_rec.dimension;
3964 l_page_parameter_tbl.extend;
3965 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
3966 l_index := l_index+1;
3967 if (l_parameter_rec.dimension = 'TIME' and
3968 instrb(l_parameter_rec.parameter_name,'_FROM')> 0) then
3969 l_period_type := substr(l_parameter_rec.parameter_name,
3970 instr(l_parameter_rec.parameter_name,'+')+1);
3971 l_period_type := substr(l_period_type, 1, length(l_period_Type)-5);
3972 l_page_parameter_rec.parameter_name := 'PERIOD_TYPE';
3973 l_page_parameter_rec.parameter_id := l_period_type;
3974 l_page_parameter_Rec.parameter_value := l_period_type;
3975 l_page_parameter_tbl.extend;
3976 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
3977 end if;
3978
3979 -- enh 2467584
3980 -- kiprabha /jprabhud
3981 -- get the l_time_parameter
3982 if (l_parameter_rec.dimension = 'TIME' or
3983 l_parameter_Rec.dimension = 'EDW_TIME_M') then
3984
3985 if (substr(l_parameter_rec.parameter_name, length(l_parameter_Rec.parameter_name)-4) = '_FROM') then
3986 l_time_parameter := substr(l_parameter_rec.parameter_name,1, length(l_parameter_rec.parameter_name)-5);
3987 end if ;
3988 end if ;
3989
3990 end loop;
3991 end if;
3992 /*
3993 if (l_paramlvlparam_tbl.COUNT > 0) then
3994 for i in l_paramlvlparam_tbl.FIRST..l_paramlvlparam_tbl.LAST loop
3995 l_parameter_rec := l_paramlvlparam_tbl(i);
3996 if (l_parameter_rec.dimension = 'TIME_COMPARISON_TYPE') then
3997 l_page_parameter_rec.parameter_name := 'TIME_COMPARISON_TYPE';
3998 l_time_Comp_value := substr(l_parameter_rec.parameter_description
3999 ,instr(l_parameter_rec.parameter_description,'+')+1);
4000 l_page_parameter_rec.parameter_id := l_time_comp_value;
4001 l_page_parameter_rec.parameter_value := l_time_comp_value;
4002 -- enh 2467584
4003 -- kiprabha /jprabhud
4004 -- get the l_time_comparison_type
4005 if(l_parameter_rec.parameter_description is not null) then
4006 l_time_comparison_type := l_parameter_rec.parameter_description;
4007 end if ;
4008 elsif (l_parameter_rec.parameter_name = 'AS_OF_DATE') THEN
4009 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
4010 l_page_parameter_rec.parameter_name := l_parameter_rec.parameter_name;
4011 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
4012 -- enh 2467584
4013 -- kiprabha /jprabhud
4014 -- get the l_asof_date
4015 if(l_parameter_rec.parameter_description is not null) then
4016 l_asof_date := l_parameter_rec.parameter_description;
4017 end if ;
4018 elsif (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') THEN
4019 l_temp_prev_asof_date := l_parameter_rec.parameter_description;
4020 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
4021 l_page_parameter_rec.parameter_name := 'BIS_PREVIOUS_ASOF_DATE';
4022 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
4023 elsif (l_parameter_rec.parameter_name = 'BIS_CUR_REPORT_START_DATE') THEN
4024 l_page_parameter_Rec.parameter_id := l_parameter_rec.parameter_value;
4025 l_page_parameter_rec.parameter_name := 'BIS_CURRENT_REPORT_START_DATE';
4026 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
4027 l_current_report_start_date := l_parameter_rec.parameter_description;
4028 elsif (l_parameter_rec.parameter_name = 'BIS_PREV_REPORT_START_DATE') THEN
4029 l_page_parameter_Rec.parameter_id := l_parameter_rec.parameter_value;
4030 l_page_parameter_rec.parameter_name := 'BIS_PREVIOUS_REPORT_START_DATE';
4031 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
4032 l_previous_report_start_date := l_parameter_rec.parameter_description;
4033 else
4034 l_page_parameter_rec.parameter_id := l_parameter_rec.parameter_value;
4035 l_page_parameter_rec.parameter_name := l_parameter_rec.parameter_name;
4036 l_page_parameter_rec.parameter_value := l_parameter_rec.parameter_description;
4037 end if;
4038 l_page_parameter_rec.period_date := l_parameter_rec.period_Date;
4039 l_page_parameter_rec.dimension := l_parameter_rec.dimension;
4040 l_page_parameter_tbl.extend;
4041 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
4042 l_index := l_index+1;
4043 end loop;
4044 end if;
4045 */
4046 -- get the order by
4047 get_order_by_clause(
4048 p_source => p_source,
4049 pAKRegionRec => pAKRegionRec,
4050 pUserSession => pUserSession,
4051 p_sort_attribute => p_sort_attribute,
4052 p_sort_direction => p_sort_direction,
4053 p_viewby_attribute2 => p_viewby_attribute2,
4054 p_viewby_dimension => p_viewby_dimension,
4055 p_viewby_dimension_level => p_viewby_dimension_level,
4056 x_order_by => l_order_by);
4057
4058
4059 l_page_parameter_rec.parameter_name := ORDER_BY_KEY;
4060 l_page_parameter_rec.parameter_id := ORDER_BY_KEY;
4061 l_page_parameter_rec.parameter_value := l_order_by;
4062 l_page_parameter_rec.dimension := NULL;
4063 l_page_parameter_rec.period_date := NULL;
4064
4065 l_page_parameter_tbl.extend;
4066 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
4067
4068
4069 -- begin enhancement 2467584
4070
4071
4072 -- Changes for enhancement 2467584
4073 -- Get Previous Time Level Values
4074 if (l_time_comparison_type = 'TIME_COMPARISON_TYPE+SEQUENTIAL') then
4075 l_temp_date := l_asof_date;
4076 else
4077 if (l_temp_prev_asof_date is null) then
4078 l_temp_prev_asof_Date := to_char(sysdate,'DD-MON-YYYY');
4079 end if;
4080 l_temp_date := l_temp_prev_asof_date;
4081 end if;
4082 BIS_PMV_TIME_LEVELS_PVT.GET_PREVIOUS_TIME_LEVEL_VALUE
4083 (p_DimensionLevel => l_time_parameter
4084 ,p_region_code => pUserSession.region_code
4085 ,p_responsibility_id => pUserSession.responsibility_id
4086 --,p_asof_date => to_date(l_asof_date)
4087 --This should be the previous as of date.
4088 ,p_asof_Date => l_temp_Date
4089 ,p_time_comparison_type => l_time_comparison_type
4090 ,x_time_level_id => l_time_id
4091 ,x_time_level_Value => l_description
4092 ,x_start_date => l_prev_start_date
4093 ,x_end_date => l_prev_end_date
4094 ,x_return_Status => x_return_Status
4095 ,x_msg_count => x_msg_count
4096 ,x_msg_data => x_msg_data
4097 );
4098
4099 -- Changes for enhancement 2467584
4100 -- Store the Previous Time Level Values in page_parameter_table
4101
4102 l_page_parameter_rec.parameter_name := l_time_parameter || '_PFROM' ;
4103 l_page_parameter_rec.parameter_id := l_time_id;
4104 l_page_parameter_rec.parameter_value := l_description;
4105 l_page_parameter_rec.dimension :=
4106 substr(l_time_parameter,1, instr(l_time_parameter,'+' )-1);
4107 l_page_parameter_rec.period_date := l_prev_start_date;
4108
4109 l_page_parameter_tbl.extend ;
4110 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
4111
4112 l_page_parameter_rec.parameter_name := l_time_parameter || '_PTO' ;
4113 l_page_parameter_rec.parameter_id := l_time_id;
4114 l_page_parameter_rec.parameter_value := l_description;
4115 l_page_parameter_rec.dimension :=
4116 substr(l_time_parameter,1, instr(l_time_parameter,'+' )-1);
4117 l_page_parameter_rec.period_date := l_prev_end_date;
4118
4119 l_page_parameter_tbl.extend ;
4120 l_page_parameter_tbl(l_page_parameter_tbl.LAST) := l_page_parameter_rec;
4121
4122 -- end enhancement 2467584
4123
4124
4125 IF ( pAKRegionRec.data_source = 'PLSQL_PARAMETERS') THEN
4126 l_dynamic_sql_Str := 'BEGIN :1 :='|| pAKRegionRec.plsql_function ||' (:2); END;';
4127 execute immediate l_dynamic_sql_str using OUT l_custom_sql ,IN l_page_parameter_tbl ;
4128
4129 ELSIF ( pAKRegionRec.data_source = 'PLSQL_PROCEDURE_CALL') THEN
4130 l_dynamic_sql_Str := 'BEGIN '|| pAKRegionRec.plsql_function ||' (:1, :2); END;';
4131 execute immediate l_dynamic_sql_str using IN l_page_parameter_tbl, OUT l_custom_output ;
4132 process_custom_output( pUserSession => pUserSession,
4133 pCustomOutput => l_custom_output,
4134 xCustom_sql => l_custom_sql,
4135 x_view_by_value => x_view_by_value );
4136 ELSIF (pAKRegionRec.data_source = 'PLSQL_PROCEDURE_QUERYATTRIBUTE') THEN
4137 l_dynamic_sql_str := 'BEGIN '||pAKRegionRec.plsql_function ||'(:1, :2, :3); END;';
4138 BEGIN
4139 execute immediate l_dynamic_sql_Str using IN l_page_parameter_tbl,
4140 OUT l_custom_sql, OUT l_query_attributes_Tbl;
4141 EXCEPTION
4142 WHEN OTHERS THEN
4143 null;
4144 END;
4145 END IF; --if data source
4146
4147 else
4148 if (pParameterTbl.COUNT > 0) then
4149 for i in pParameterTbl.FIRST..pParameterTbl.LAST LOOP
4150 l_parameter_rec := pParameterTbl(i);
4151 if (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') THEN
4152 l_temp_prev_asof_date := l_parameter_rec.parameter_description;
4153 elsif (l_parameter_rec.parameter_name = 'BIS_CUR_REPORT_START_DATE') THEN
4154 l_current_report_start_date := l_parameter_rec.parameter_description;
4155 elsif (l_parameter_rec.parameter_name = 'BIS_PREV_REPORT_START_DATE') THEN
4156 l_previous_report_start_date := l_parameter_rec.parameter_description;
4157 elsif (l_parameter_rec.dimension = 'TIME_COMPARISON_TYPE') then
4158 if(l_parameter_rec.parameter_description is not null) then
4159 l_time_comparison_type := l_parameter_rec.parameter_description;
4160 end if;
4161 end if ;
4162 end loop;
4163 end if;
4164 /*
4165 if (l_paramlvlparam_tbl.COUNT > 0) then
4166 for i in l_paramlvlparam_tbl.FIRST..l_paramlvlparam_tbl.LAST loop
4167 l_parameter_rec := l_paramlvlparam_tbl(i);
4168 if (l_parameter_rec.parameter_name = 'BIS_P_ASOF_DATE') THEN
4169 l_temp_prev_asof_date := l_parameter_rec.parameter_description;
4170 elsif (l_parameter_rec.parameter_name = 'BIS_CUR_REPORT_START_DATE') THEN
4171 l_current_report_start_date := l_parameter_rec.parameter_description;
4172 elsif (l_parameter_rec.parameter_name = 'BIS_PREV_REPORT_START_DATE') THEN
4173 l_previous_report_start_date := l_parameter_rec.parameter_description;
4174 elsif (l_parameter_rec.dimension = 'TIME_COMPARISON_TYPE') then
4175 if(l_parameter_rec.parameter_description is not null) then
4176 l_time_comparison_type := l_parameter_rec.parameter_description;
4177 end if ;
4178 end if;
4179 end loop;
4180 end if;
4181 */
4182 --l_dynamic_sql_Str := 'SELECT '|| pAKRegionRec.plsql_function || ' FROM DUAL';
4183 l_Dynamic_sql_str := 'BEGIN :1 :='||pAKRegionRec.plsql_function||'(); END;';
4184 --l_dynamic_sql_Str := 'SELECT test_func.get_sql FROM DUAL';
4185 execute immediate l_dynamic_sql_str using OUT l_custom_Sql;
4186 end if;
4187
4188 l_original_sql := l_custom_sql;
4189 --We first need to replace the bind variables sent by the product teams queries.
4190
4191 IF ( pAKRegionRec.data_source = 'PLSQL_PROCEDURE_QUERYATTRIBUTE') THEN
4192 replace_product_binds(pUserSession => pUserSession,
4193 p_original_sql => l_original_sql,
4194 p_custom_output => l_query_attributes_tbl,
4195 x_custom_sql => l_custom_Sql,
4196 x_bind_variables => x_bind_variables,
4197 x_plsql_bind_variables => x_plsql_bind_variables,
4198 x_bind_indexes => x_bind_indexes,
4199 x_bind_Datatypes => x_bind_datatypes,
4200 x_bind_count => l_bind_count,
4201 x_view_by_value => x_view_by_value );
4202 END IF;
4203 --Replace time comparison type and period types
4204 l_index := instrb(l_original_sql,'&BIS_TIME_COMPARISON_TYPE');
4205 if (l_index > 0) then
4206 replace_with_bind_variables
4207 (p_search_string => '&BIS_TIME_COMPARISON_TYPE',
4208 p_bind_value => l_time_comp_value,
4209 p_initial_index => l_index,
4210 p_original_sql => l_original_sql,
4211 x_custom_sql => l_custom_sql,
4212 x_bind_variables => x_bind_variables,
4213 x_plsql_bind_variables => x_plsql_bind_variables,
4214 x_bind_indexes => x_bind_indexes,
4215 x_bind_datatypes => x_bind_datatypes,
4216 x_bind_count => l_bind_count);
4217 end if;
4218 l_index := instrb(l_original_sql,'&BIS_PERIOD_TYPE');
4219 if (l_index > 0) then
4220 replace_with_bind_variables
4221 (p_search_string => '&BIS_PERIOD_TYPE',
4222 p_bind_value => l_period_type,
4223 p_initial_index => l_index,
4224 p_original_sql => l_original_sql,
4225 x_custom_sql => l_custom_sql,
4226 x_bind_variables => x_bind_variables,
4227 x_plsql_bind_variables => x_plsql_bind_variables,
4228 x_bind_indexes => x_bind_indexes,
4229 x_bind_datatypes => x_bind_datatypes,
4230 x_bind_count => l_bind_count);
4231 end if;
4232 --REplace the nested bit pattern
4233 l_index := instrb(l_original_sql ,'&BIS_NESTED_PATTERN');
4234 if (l_index > 0) then
4235 --Get the nested pattern
4236 BIS_PMV_TIME_LEVELS_PVT.GET_NESTED_PATTERN
4237 (p_time_comparison_type => l_time_comparison_type
4238 ,p_time_level => l_period_type
4239 ,x_nested_pattern => l_nested_pattern
4240 ,x_return_Status => x_return_Status
4241 ,x_msg_count => x_msg_count
4242 ,x_msg_data => x_msg_data
4243 );
4244 replace_with_bind_variables
4245 (p_search_string => '&BIS_NESTED_PATTERN',
4246 p_bind_value => l_nested_pattern,
4247 p_bind_Datatype => BIS_PMV_PARAMETERS_PUB.INTEGER_BIND,
4248 p_initial_index => l_index,
4249 p_original_sql => l_original_sql,
4250 x_custom_sql => l_custom_sql,
4251 x_bind_variables => x_bind_variables,
4252 x_plsql_bind_variables => x_plsql_bind_variables,
4253 x_bind_indexes => x_bind_indexes,
4254 x_bind_datatypes => x_bind_datatypes,
4255 x_bind_count => l_bind_count);
4256 end if;
4257 l_index := instrb(l_original_sql , '&BIS_CURRENT_REPORT_START_DATE');
4258 if (l_index > 0) then
4259 if (l_current_report_start_date is null) then
4260 l_current_Report_start_date := to_char(sysdate,'DD-MON-YYYY');
4261 end if;
4262 replace_with_bind_variables
4263 (p_search_string => '&BIS_CURRENT_REPORT_START_DATE',
4264 p_bind_value => l_current_report_start_date,
4265 p_initial_index => l_index,
4266 p_bind_to_date => 'Y',
4267 p_original_sql => l_original_sql,
4268 x_custom_sql => l_custom_sql,
4269 x_bind_variables => x_bind_variables,
4270 x_plsql_bind_variables => x_plsql_bind_variables,
4271 x_bind_indexes => x_bind_indexes,
4272 x_bind_Datatypes => x_bind_datatypes,
4273 x_bind_count => l_bind_count);
4274 end if;
4275 l_index := instrb(l_original_sql , '&BIS_PREVIOUS_REPORT_START_DATE');
4276 if (l_index > 0) then
4277 if (l_previous_report_start_date is null) then
4278 l_previous_report_start_date := to_char(sysdate,'DD-MON-YYYY');
4279 end if;
4280 replace_with_bind_variables
4281 (p_search_string => '&BIS_PREVIOUS_REPORT_START_DATE',
4282 p_bind_value => l_previous_report_start_date,
4283 p_initial_index => l_index,
4284 p_bind_to_date => 'Y',
4285 p_original_sql => l_original_sql,
4286 x_custom_sql => l_custom_sql,
4287 x_bind_variables => x_bind_variables,
4288 x_plsql_bind_variables => x_plsql_bind_variables,
4289 x_bind_indexes => x_bind_indexes,
4290 x_bind_Datatypes => x_bind_datatypes,
4291 x_bind_count => l_bind_count);
4292 end if;
4293 --First search for all the parameters and replace them
4294 replace_report_parameters(
4295 p_user_session_rec => pUserSession,
4296 pParameterTbl => pParameterTbl,
4297 pStartChar => '&',
4298 pEndChar => '',
4299 pUseBindVariable => true,
4300 pReplaceSubstVariable => true,
4301 pReplaceXTDVariable => TRUE, -- replace xtd for custom stuff w/o binding
4302 p_original_sql => l_original_sql,
4303 x_custom_sql => l_custom_Sql,
4304 x_temp_Start_date => l_temp_Start_date,
4305 x_temp_end_date => l_temp_end_date,
4306 x_time_parameter => l_time_parameter,
4307 x_asOf_date => l_asOf_date,
4308 x_prev_asof_date => l_prev_asof_date,
4309 x_time_comparison_type => l_time_comparison_type,
4310 x_bind_variables => x_bind_variables,
4311 x_plsql_bind_variables => x_plsql_bind_variables,
4312 x_bind_indexes => x_bind_indexes,
4313 x_bind_Datatypes => x_bind_datatypes,
4314 x_bind_count => l_bind_count
4315 ) ;
4316
4317 /*
4318 IF (l_asof_date IS NULL OR l_time_comparison_type IS NULL) THEN
4319
4320 retrieve_params_from_page (
4321 p_user_session_rec => pUserSession,
4322 p_paramlvlparam_Tbl => l_paramlvlparam_tbl,
4323 x_asof_date => l_asof_date_page,
4324 x_prev_asof_Date => l_prev_asof_date_page,
4325 x_time_comparison_type => l_time_comparison_page,
4326 x_return_status => x_return_status,
4327 x_msg_count => x_msg_count,
4328 x_msg_data => x_msg_data
4329 ) ;
4330 END IF;
4331 */
4332
4333 IF (l_asOf_Date IS NULL) THEN
4334 IF (l_asof_date_page IS NOT NULL) THEN
4335 l_asof_date := l_asof_date_page;
4336 ELSE
4337 l_asof_date := to_char(sysdate,'DD-MON-YYYY');
4338 END IF;
4339 END IF;
4340 IF (l_prev_asOf_Date IS NULL) THEN
4341 IF (l_prev_asof_date_page IS NOT NULL) THEN
4342 l_prev_asof_date := l_prev_asof_date_page;
4343 ELSE
4344 l_prev_asof_date := to_char(sysdate,'DD-MON-YYYY');
4345 END IF;
4346 END IF;
4347
4348 IF (l_time_comparison_type IS NULL AND l_time_comparison_page IS NOT NULL ) THEN
4349 l_time_comparison_type := l_time_comparison_page;
4350 END IF;
4351
4352
4353 replace_paramLvl_parameters(
4354 p_user_session_rec => pUserSession,
4355 p_asof_date => l_asof_date,
4356 p_prev_asof_date => l_prev_asof_date,
4357 p_time_comparison_type => l_time_comparison_type,
4358 p_time_parameter => l_time_parameter,
4359 p_original_sql => l_original_sql,
4360 x_custom_sql => l_custom_sql,
4361 x_return_status => x_return_status,
4362 x_msg_count => x_msg_count,
4363 x_msg_data => x_msg_data,
4364 x_bind_variables => x_bind_variables,
4365 x_plsql_bind_variables => x_plsql_bind_variables,
4366 x_bind_indexes => x_bind_indexes,
4367 x_bind_Datatypes => x_bind_Datatypes,
4368 x_bind_count => l_bind_count
4369 ) ;
4370
4371 IF (instrb(l_custom_sql,'&BIS_PREVIOUS_EFFECTIVE_START_DATE') >0 OR
4372 instrb(l_custom_Sql,'&BIS_PREVIOUS_EFFECTIVE_END_DATE') > 0 ) THEN
4373
4374
4375 -- jprabhud enh 2467584
4376 if (pAKRegionRec.data_source = 'PLSQL_PARAMETERS' OR pAKRegionRec.data_source = 'PLSQL_PROCEDURE_CALL'
4377 or pAKRegionRec.data_source = 'PLSQL_PROCEDURE_QUERYATTRIBUTE') then
4378 l_replace_mode :='1';
4379 if(l_prev_start_date is not null AND length(l_prev_start_date)<>0) then
4380 l_temp_start_date := to_char(l_prev_start_date,'DD-MON-YYYY') ;
4381 end if;
4382 if(l_prev_end_date is not null AND length(l_prev_end_date)<>0 ) then
4383 l_temp_end_date := to_char(l_prev_end_date,'DD-MON-YYYY') ;
4384 end if;
4385
4386 else
4387 if (l_time_comparison_type = 'TIME_COMPARISON_TYPE+SEQUENTIAL') then
4388 l_temp_date := l_asof_date;
4389 else
4390 if (l_temp_prev_asof_date is null) then
4391 l_temp_prev_asof_Date := to_char(sysdate,'DD-MON-YYYY');
4392 end if;
4393 l_temp_date := l_temp_prev_asof_date;
4394 end if;
4395 l_replace_mode :='2';
4396 end if;
4397
4398 -- enh 2467584
4399 -- kiprabha /jprabhud
4400 -- added p_replace_mode
4401 -- p_replace_mode = '1' => previous time value already known (passed as p_default_start_date,p_default_end_date)
4402 -- = '2' => API to get previous time values needs to get called
4403 replace_prev_time_parameters(
4404 p_user_session_rec => pUserSession,
4405 p_time_parameter => l_time_parameter,
4406 p_asof_date => l_temp_date,
4407 p_time_comparison_type => l_time_comparison_type,
4408 p_default_start_date => l_temp_Start_date,
4409 p_default_end_date => l_temp_end_date,
4410 p_original_sql => l_original_sql,
4411 p_replace_mode => l_replace_mode,
4412 x_custom_sql => l_custom_sql,
4413 x_return_status => x_return_status,
4414 x_msg_count =>x_msg_count,
4415 x_msg_data => x_msg_data,
4416 x_bind_variables => x_bind_variables,
4417 x_plsql_bind_variables => x_plsql_bind_variables,
4418 x_bind_indexes => x_bind_indexes,
4419 x_bind_Datatypes => x_bind_Datatypes,
4420 x_bind_count => l_bind_count
4421 );
4422 END IF;
4423
4424 -- replace start and end subs variables
4425 --serao- bug 2642688 -10/25/02 - use bind variables
4426 replaceStartEndIndex (p_custom_sql => l_custom_sql,
4427 p_lower_bound => p_lower_bound,
4428 p_upper_bound => p_upper_bound+1,
4429 p_original_sql => l_original_sql,
4430 x_bind_variables => x_bind_variables,
4431 x_plsql_bind_variables => x_plsql_bind_variables,
4432 x_bind_indexes => x_bind_indexes,
4433 x_bind_Datatypes => x_bind_Datatypes,
4434 x_bind_count => l_bind_count
4435 );
4436
4437
4438 -- replace order by subs variable
4439 IF (instrb(l_custom_sql, ORDER_BY_SUBST_VAR) >0 ) THEN
4440 -- order by could have been obtained for parameters
4441 IF (l_order_by IS NULL) THEN
4442 get_order_by_clause(
4443 p_source => p_source,
4444 pAKRegionRec => pAKRegionRec,
4445 pUserSession => pUserSession,
4446 p_sort_attribute => p_sort_attribute,
4447 p_sort_direction => p_sort_direction,
4448 p_viewby_attribute2 => p_viewby_attribute2,
4449 p_viewby_dimension => p_viewby_dimension,
4450 p_viewby_dimension_level => p_viewby_dimension_level,
4451 x_order_by => l_order_by);
4452 END IF;
4453 --serao - 2622281 - add order by only if non-null
4454 IF (l_order_by IS NOT NULL) THEN
4455 l_custom_sql := replace(l_Custom_sql, ORDER_BY_SUBST_VAR, ' ORDER BY '||l_order_by);
4456 END IF;
4457 END IF;
4458
4459 x_sql_String := l_custom_sql;
4460 exception
4461 when others then
4462 null;
4463 END;
4464
4465 PROCEDURE replaceAttrCodeWithDimension(
4466 pUserSession_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
4467 x_custom_sql In OUT NOCOPY VARCHAR2
4468 ) IS
4469 vAttributeCode VARCHAR2(150);
4470 vAttribute2 VARCHAR2(150);
4471 vIndex1 NUMBER := 1;
4472 vIndex2 NUMBER;
4473 BEGIN
4474
4475 -- substitute the attribute code with the dimensions
4476 LOOP
4477 vIndex1 := instr(x_custom_sql, '{', vIndex1, 1);
4478 vIndex2 := instr(x_custom_sql, '}', vIndex1+1, 1);
4479 if vIndex1 = 0 or vIndex2 = 0 THEN
4480 EXIT;
4481 end if;
4482 vAttributeCode := substr(x_custom_sql, vIndex1+1, vIndex2-vIndex1-1);
4483 vAttribute2 := BIS_PARAMETER_VALIDATION.getDimensionForAttribute(rtrim(ltrim(vAttributeCode)), pUserSession_rec.region_Code);
4484
4485 x_custom_sql := replace( x_custom_sql, vAttributeCode, vAttribute2);
4486 vIndex1 := vIndex2+1;
4487 END LOOP;
4488
4489 END replaceAttrCodeWithDimension;
4490
4491 PROCEDURE substitute_lov_where(
4492 pUserSession_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
4493 pSchedule_id IN VARCHAR2,
4494 pSource In VARCHAR2 DEFAULT 'REPORT',
4495 x_lov_where IN OUT NOCOPY VARCHAR2,
4496 x_return_status out NOCOPY VARCHAR2,
4497 x_msg_count out NOCOPY NUMBER,
4498 x_msg_data out NOCOPY VARCHAR2
4499 ) IS
4500
4501 l_parameter_tbl BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE;
4502 l_bind_variables VARCHAR2(1000);
4503 l_plsql_bind_variables VARCHAR2(1000);
4504 l_bind_indexes VARCHAR2(1000);
4505 l_bind_datatypes VARCHAR2(32000);
4506 l_bind_count NUMBER := 0;
4507 l_temp VARCHAR2(30);
4508 l_original_sql varchar2(32000) := x_lov_where;
4509 BEGIN
4510
4511
4512 IF (x_lov_where IS NOT NULL) THEN
4513 --check if it has any subs variables
4514 IF (instrb(x_lov_where, '{' ) > 0) THEN
4515
4516 BIS_PMV_PARAMETERS_PVT.RETRIEVE_PAGE_PARAMETERS
4517 (p_schedule_id => pSchedule_id,
4518 p_user_session_rec => pUserSession_rec,
4519 x_user_param_tbl => l_parameter_tbl,
4520 x_return_status => x_return_status,
4521 x_msg_count => x_msg_count,
4522 x_msg_data => x_msg_data
4523 );
4524
4525 replaceAttrCodeWithDimension(
4526 pUserSession_rec =>pUserSession_rec,
4527 x_custom_sql =>x_lov_where
4528 );
4529
4530 replace_report_parameters(
4531 p_user_session_rec =>pUserSession_rec,
4532 pParameterTbl =>l_parameter_tbl,
4533 pStartChar =>'{',
4534 pEndChar => '}',
4535 pUseBindVariable => false,
4536 pReplaceSubstVariable => false,
4537 pReplaceXTDVariable => false,
4538 p_original_sql => l_original_sql,
4539 x_custom_sql => x_lov_where,
4540 x_temp_Start_date => l_temp,
4541 x_temp_end_date => l_temp,
4542 x_time_parameter => l_temp,
4543 x_asOf_date => l_temp,
4544 x_prev_asof_date=> l_temp,
4545 x_time_comparison_type => l_temp,
4546 x_bind_variables => l_bind_variables,
4547 x_plsql_bind_variables => l_plsql_bind_variables,
4548 x_bind_indexes => l_bind_indexes,
4549 x_bind_Datatypes => l_bind_Datatypes,
4550 x_bind_count => l_bind_count
4551 ) ;
4552
4553
4554 END IF;
4555 END IF;
4556
4557 END substitute_lov_where;
4558
4559 procedure replace_with_bind_variables
4560 (p_search_string in varchar2,
4561 p_bind_value in varchar2,
4562 p_bind_Datatype IN NUMBER DEFAULT 2,
4563 p_initial_index in number,
4564 p_bind_function in varchar2 default null,
4565 p_bind_to_date in varchar2 default 'N',
4566 p_original_sql in varchar2,
4567 x_custom_sql in out NOCOPY varchar2,
4568 x_bind_variables in out NOCOPY varchar2,
4569 x_plsql_bind_variables in out NOCOPY varchar2,
4570 x_bind_indexes in out NOCOPY varchar2,
4571 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
4572 x_bind_count in out NOCOPY number) is
4573
4574 l_count number := 0;
4575 l_index number := instrb(x_custom_sql, p_search_string);
4576 l_bind_index number := p_initial_index;
4577 l_bind_string varchar2(32000);
4578 l_split_bind_values varchar2(32000);
4579 l_split_bind_vars varchar2(32000);
4580 l_split boolean := false;
4581 l_old_bind_count number := x_bind_count;
4582 l_diff number;
4583 begin
4584
4585 if instrb(p_bind_value, ''',''') > 0 then
4586 l_split := true;
4587 splitMultipleVariables
4588 (lString => replace(p_bind_value,'''',null),
4589 x_bind_variables => l_split_bind_values,
4590 x_bind_count => x_bind_count,
4591 x_split_string => l_split_bind_vars
4592 );
4593 l_diff := x_bind_count - l_old_bind_count;
4594 x_plsql_bind_variables := x_plsql_bind_variables || l_split_bind_values;
4595 else
4596 x_bind_count := x_bind_count + 1;
4597 x_plsql_bind_variables := x_plsql_bind_variables || SEPERATOR || p_bind_value;
4598 end if;
4599
4600 while (l_bind_index > 0) and (l_count < 1000) loop
4601
4602 if l_split then
4603 for i in 1..l_diff loop
4604 x_bind_indexes := x_bind_indexes || SEPERATOR || l_bind_index;
4605 x_bind_datatypes := x_bind_datatypes || SEPERATOR || p_bind_datatype;
4606 l_bind_index := l_bind_index + 1;
4607 end loop;
4608 x_bind_variables := x_bind_variables || l_split_bind_values;
4609 l_bind_string := l_split_bind_vars;
4610 else
4611 x_bind_indexes := x_bind_indexes || SEPERATOR || l_bind_index;
4612 if (p_bind_datatype = BIS_PMV_PARAMETERS_PUB.DATE_BIND) THEN
4613 x_bind_datatypes := x_bind_datatypes || SEPERATOR|| BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4614 else
4615 x_bind_datatypes := x_bind_datatypes || SEPERATOR || p_bind_datatype;
4616 end if;
4617 x_bind_variables := x_bind_variables || SEPERATOR || p_bind_value;
4618
4619 if p_bind_to_date = 'Y' then
4620 l_bind_string := 'TO_DATE(:' || x_bind_count || ',''DD-MON-YYYY'')';
4621 elsif (p_bind_datatype = BIS_PMV_PARAMETERS_PUB.DATE_BIND) THEN
4622 l_bind_string := 'TO_DATE(:' || x_bind_count ||',''DD/MM/YYYY'')';
4623 else
4624 l_bind_string := ':' || x_bind_count;
4625 end if;
4626 end if; -- end of split case
4627 if p_bind_function is not null then
4628 l_bind_string := p_bind_function || '(' || l_bind_string || ')';
4629 end if;
4630
4631 x_custom_sql := substrb(x_custom_sql, 0, l_index-1)
4632 || l_bind_string
4633 || substrb(x_custom_sql,l_index+lengthb(p_search_string));
4634
4635 l_index := instrb(x_custom_sql, p_search_string);
4636 l_bind_index := instrb(p_original_sql, p_search_string, l_bind_index+1);
4637 l_count := l_count + 1;
4638 end loop;
4639
4640 end replace_with_bind_variables;
4641
4642 procedure replace_product_binds
4643 (pUSerSession IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
4644 p_original_sql IN VARCHAR2,
4645 p_custom_output IN BIS_QUERY_ATTRIBUTES_TBL,
4646 x_custom_sql IN OUT NOCOPY VARCHAR2,
4647 x_bind_variables IN OUT NOCOPY VARCHAR2,
4648 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
4649 x_bind_indexes IN OUT NOCOPY VARCHAR2,
4650 x_bind_Datatypes IN OUT NOCOPY VARCHAR2,
4651 x_bind_count IN OUT NOCOPY NUMBER,
4652 x_view_by_value OUT NOCOPY VARCHAR2
4653 )
4654 IS
4655 l_index number;
4656 l_bis_query_attributes BIS_QUERY_ATTRIBUTES;
4657 BEGIN
4658 if (p_custom_output is not null and p_custom_output.COUNT > 0 )
4659 then
4660
4661 for i in p_custom_output.FIRST..p_custom_output.LAST loop
4662 l_bis_Query_attributes := p_custom_output(i);
4663 if (l_bis_query_attributes.attribute_type = BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE)
4664 then
4665 if (l_bis_query_attributes.attribute_value is not null and
4666 length(l_bis_query_attributes.attribute_value) > 0) then
4667 process_custom_view_by(pusersession, l_bis_query_attributes.attribute_value);
4668 x_view_by_value := l_bis_query_attributes.attribute_value;
4669 l_index := instrb(p_original_Sql,'&BIS_VIEW_BY');
4670 if (l_index > 0) then
4671 replaceNameWithValue(
4672 pParamName => '&BIS_VIEW_BY',
4673 pParamValue => l_bis_query_attributes.attribute_value,
4674 pUseBindVariable => true ,
4675 p_initial_index => l_index,
4676 p_original_sql => p_original_sql,
4677 xClause => x_custom_sql,
4678 x_bind_variables => x_bind_variables,
4679 x_plsql_bind_variables => x_plsql_bind_variables,
4680 x_bind_indexes => x_bind_indexes,
4681 x_bind_datatypes => x_bind_datatypes,
4682 x_bind_count => x_bind_count
4683 );
4684 end if;
4685 end if;
4686 end if;
4687 if (l_bis_query_attributes.attribute_type = BIS_PMV_PARAMETERS_PUB.BIND_TYPE) THEN
4688 l_index := instrb(p_original_sql, l_bis_query_attributes.attribute_name);
4689 if (l_index > 0) then
4690 replace_with_bind_variables
4691 (p_search_string => l_bis_query_attributes.attribute_name,
4692 p_bind_value => l_bis_Query_attributes.attribute_value,
4693 p_bind_datatype => l_bis_query_attributes.attribute_data_type,
4694 p_initial_index => l_index,
4695 p_original_sql => p_original_sql,
4696 x_custom_sql => x_custom_sql,
4697 x_bind_variables => x_bind_variables,
4698 x_plsql_bind_variables => x_plsql_bind_variables,
4699 x_bind_indexes => x_bind_indexes,
4700 x_bind_datatypes => x_bind_datatypes,
4701 x_bind_count => x_bind_count);
4702 end if;
4703 end if;
4704 end loop;
4705 null;
4706 end if;
4707 END;
4708 end BIS_PMV_QUERY_PVT;