[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_DRILL_PVT
Source
1 Package Body BIS_PMV_DRILL_PVT AS
2 /* $Header: BISVDRIB.pls 120.1 2006/02/13 02:48:56 nbarik noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.90=120.1):~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: BIS_PMV_DRILL_PVT --
7 -- --
8 -- DESCRIPTION: This package contains all the procedures used to --
9 -- validate the Report Generator parameters. --
10 -- --
11 -- MODIFICATIONS --
12 -- Date User Modification --
13 -- 10/24/01 aleung initial creation --
14 -- 09/30/02 nkishore Fix for 2594996 --
15 -- 11/12/02 nkishore Fix for 2616798 commented overRideViewBy --
16 -- 04/22/03 ansingh BugFix#2887200 --
17 -- 06/07/03 gsanap Added p_NextExtraViewBy to drilldown bug 3007145--
18 -- 18-JUL-03 ansingh Bug3056835: enable drill/pivot from webportlet --
19 -- 14-AUG-03 ansingh Bug3024649: drilldown changes for related links --
20 -- 19-AUG-03 nkishore BugFix 3099789 add copy_time_params --
21 -- 10/21/03 nbarik Bug Fix 3201277 - Change in overRideViewBy --
22 -- 01/30/04 ksadagop Bug Fix 3409904 - Added CustomView for drilldown--
23 -- 02/19/04 nbarik Bug Fix 3441967 --
24 -- 03/31/04 nbarik Bug Fix 3510716 --
25 -- 06/04/04 ashgarg Bug Fix 3665085 --
26 ----------------------------------------------------------------------------
27
28 --Fix for 2594996
29 PROCEDURE overRideViewBy (
30 pFunctionName in varchar2,
31 pSessionId in varchar2,
32 pUserId in varchar2,
33 pTimeAttribute IN VARCHAR2
34 ) IS
35
36 --nbarik - 10/21/03 - bug fix 3201277 - Added function name here
37 cursor getViewBy (cpUserId varchar2, cpSessionId varchar2) is
38 select session_value
39 from bis_user_attributes
40 where user_id = cpUserId
41 and session_id = cpSessionId
42 and function_name = pFunctionName
43 and attribute_name = 'VIEW_BY';
44
45 --nbarik - 10/21/03 - bug fix 3201277
46 /*
47 cursor getTimeAttribute (cpUserId varchar2, cpSessionId varchar2) is
48 select attribute_name
49 from bis_user_attributes
50 where user_id = cpUserId
51 and session_id = cpSessionId
52 and ( attribute_name like 'TIME%' or attribute_name like 'EDW_TIME_M%' );
53 */
54
55 lViewByValue VARCHAR2(200);
56 lViewByDimension VARCHAR2(100);
57 lTimeDimLevel VARCHAR2(100);
58 lTimeDimLevels VARCHAR2(100);
59
60 BEGIN
61
62 IF getViewBy%ISOPEN THEN
63 close getViewBy;
64 END IF;
65 OPEN getViewBy(pUserId, pSessionId);
66 FETCH getViewBy INTO lViewByValue;
67 CLOSE getViewBy;
68
69 IF (lViewByValue is not null) THEN
70 lViewByDimension := substr(lViewByValue ,1,instr(lViewByValue ,'+')-1) ;
71
72 IF (lViewByDimension = 'TIME' OR lViewByDimension = 'EDW_TIME_M') THEN
73 /*
74 IF getTimeAttribute%ISOPEN THEN
75 close getTimeAttribute;
76 END IF;
77
78 OPEN getTimeAttribute(pUserId, pSessionId);
79 FETCH getTimeAttribute into lTimeDimLevel;
80 CLOSE getTimeAttribute;
81 */
82 --nbarik - 10/21/03 - bug fix 3201277
83 lTimeDimLevel := pTimeAttribute;
84 IF ( lTimeDimLevel is not null ) THEN
85
86 IF ( instr(lTimeDimLevel,'_FROM') > 1 ) THEN
87 lTimeDimLevels := substr( lTimeDimLevel ,1,instr( lTimeDimLevel ,'_FROM')-1);
88 ELSIF ( instr(lTimeDimLevel,'_TO') > 1 ) THEN
89 lTimeDimLevels := substr( lTimeDimLevel ,1,instr( lTimeDimLevel ,'_TO')-1);
90 END IF;
91
92 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
93 AND schedule_id is null
94 AND user_id=pUserId
95 AND attribute_name = 'VIEW_BY';
96
97 INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
98 session_id, attribute_name,
99 session_value,
100 dimension,
101 creation_date, created_by,
102 last_update_Date, last_updated_by)
103 VALUES (pUserId, pFunctionName,
104 pSessionId, 'VIEW_BY' ,
105 lTimeDimLevels,
106 lViewByDimension,
107 sysdate, -1, sysdate, -1);
108 END IF;
109
110 END IF;
111 END IF;
112 --End of Fix for 2594996
113
114 END overRideViewBy;
115
116 /* Procedure to copy the same function parameters - overide from schedule and page only */
117 PROCEDURE copySameFunctionParameters(pSessionId in varchar2,
118 pUserId in varchar2,
119 pFunctionName in varchar2,
120 pRegionCode in varchar2,
121 pScheduleId in varchar2 default null,
122 pPageId in varchar2 default null,
123 pRespId in varchar2 default null,
124 pParameterGroup IN BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type
125 )IS
126
127 BEGIN
128
129 IF (pScheduleId IS NOT NULL) THEN
130 BIS_PMV_PARAMETERS_PVT.overRideFromSchedule(
131 pSessionId => pSessionId,
132 pUserId =>pUserId,
133 pFunctionName =>pFunctionName,
134 pRegionCode => pRegionCode,
135 pScheduleId => pScheduleId,
136 pRespId =>pRespId,
137 pParameterGroup =>pParameterGroup
138 );
139 END IF;
140
141 IF (pPageId IS NOT NULL) THEN
142 BIS_PMV_PARAMETERS_PVT.overRideFromPage(
143 pSessionId => pSessionId,
144 pUserId =>pUserId,
145 pFunctionName =>pFunctionName,
146 pRegionCode => pRegionCode,
147 pPageId => pPageId,
148 pRespId =>pRespId,
149 pParameterGroup =>pParameterGroup
150 );
151 END IF;
152
153 END copySameFunctionParameters;
154
155
156 PROCEDURE copyGroupedParameters(pSessionId in varchar2,
157 pUserId in varchar2,
158 pPreFunctionName in varchar2,
159 pFunctionName in varchar2,
160 pRegionCode in varchar2,
161 pScheduleId in varchar2 default null,
162 pPageId in varchar2 default null,
163 pRespId in varchar2 default null,
164 pParameterGroup IN BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type,
165 pTCTExists in boolean default false,
166 pNestedRegionCode in varchar2 default null,
167 pAsofdateExists in boolean default false,
168 xTimeAttribute out NOCOPY varchar2
169 ) IS
170 l_return_status varchar2(32000);
171 l_msg_count NUMBER;
172 l_msg_data varchar2(32000);
173 l_time_attribute varchar2(2000);
174 -- nbarik - 02/19/04 - BugFix 3441967
175 l_IsPreFuncTCTExists BOOLEAN := FALSE;
176 l_IsPreFuncCalcDatesExists BOOLEAN := FALSE;
177
178 BEGIN
179
180 if (pFunctionName = pPreFunctionName) THEN
181
182 copySameFunctionParameters(pSessionId => pSessionId,
183 pUserId => pUserId,
184 pFunctionName => pFunctionName,
185 pRegionCode => pRegionCode,
186 pScheduleId => pScheduleId,
187 pPageId => pPageId,
188 pRespId => pRespId,
189 pParameterGroup => pParameterGroup
190 );
191 ELSE
192
193 --0) delete all the existing session parameters
194 DELETE FROM bis_user_attributes
195 WHERE function_name=pFunctionName
196 AND user_id=pUserId
197 AND session_id=pSessionId
198 AND schedule_id IS NULL;
199
200 --1) copy all the fnd form function default parameters
201 BIS_PMV_PARAMETERS_PVT.COPY_FORM_FUNCTION_PARAMETERS
202 (pRegionCode => pRegionCode
203 ,pFunctionName => pFunctionName
204 ,pUserId => pUserId
205 ,pSessionId => pSessionId
206 ,pResponsibilityId => pRespId
207 ,pNestedRegionCode => pNestedRegionCode
208 ,pAsofdateExists => pAsofdateExists
209 ,x_return_status => l_return_status
210 ,x_msg_count => l_msg_count
211 ,x_msg_data => l_msg_data
212 ) ;
213
214
215 --2) use the saved default parameters
216 BIS_PMV_PARAMETERS_PVT.overRideFromSavedDefault(
217 pSessionId => pSessionId,
218 pUserId =>pUserId,
219 pFunctionName =>pFunctionName,
220 pRegionCode => pRegionCode,
221 pRespId =>pRespId,
222 pParameterGroup =>pParameterGroup
223 );
224
225 -- 3) schedule or preFunction
226 IF (pScheduleId IS NOT NULL) THEN
227 BIS_PMV_PARAMETERS_PVT.overRideFromSchedule(
228 pSessionId => pSessionId,
229 pUserId =>pUserId,
230 pFunctionName =>pFunctionName,
231 pRegionCode => pRegionCode,
232 pScheduleId => pScheduleId,
233 pRespId =>pRespId,
234 pParameterGroup =>pParameterGroup
235
236 );
237 ELSIF (pPreFunctionName IS NOT NULL) THEN
238 -- nbarik - 02/19/04 - BugFix 3441967
239 BIS_PMV_PARAMETERS_PVT.overRideFromPreFunction(
240 pSessionId => pSessionId,
241 pUserId =>pUserId,
242 pFunctionName =>pFunctionName,
243 pRegionCode => pRegionCode,
244 pPreFunctionName => pPreFunctionName,
245 pRespId =>pRespId,
246 pParameterGroup =>pParameterGroup,
247 pTCTExists => pTCTExists
248 , x_IsPreFuncTCTExists => l_IsPreFuncTCTExists
249 , x_IsPreFuncCalcDatesExists => l_IsPreFuncCalcDatesExists
250 );
251 END IF;
252
253
254 --4) page level
255 IF (pPageId IS NOT NULL) THEN
256 BIS_PMV_PARAMETERS_PVT.overRideFromPage(
257 pSessionId => pSessionId,
258 pUserId =>pUserId,
259 pFunctionName =>pFunctionName,
260 pRegionCode => pRegionCode,
261 pPageId => pPageId,
262 pRespId =>pRespId,
263 pParameterGroup =>pParameterGroup
264 );
265 END IF;
266 --BugFix 3099789 add copy_time_params, Save Time Params here
267 -- nbarik - 02/19/04 - BugFix 3441967
268 BIS_PMV_PARAMETERS_PVT.COPY_TIME_PARAMS(
269 pSessionId => pSessionId,
270 pUserId =>pUserId,
271 pFunctionName =>pFunctionName,
272 pRegionCode => pRegionCode,
273 pRespId =>pRespId,
274 pParameterGroup =>pParameterGroup,
275 pTCTExists => pTCTExists,
276 p_IsPreFuncTCTExists => l_IsPreFuncTCTExists,
277 p_IsPreFuncCalcDatesExists => l_IsPreFuncCalcDatesExists,
278 x_time_attribute => l_time_attribute
279 );
280 --nbarik - 10/21/03 - bug fix 3201277 - Uncomment overRideViewBy call
281 --5 ) override the view-by in case it is time --Fix for 2594996
282 overRideViewBy (
283 pFunctionName => pFunctionName,
284 pSessionId =>pSessionId,
285 pUserId => pUserId,
286 pTimeAttribute => l_time_attribute
287 );
288
289 -- DIMENSION VALUE EXTENSION - DRILL Bug 3230530 / Bug 3004363
290 -- Return l_time_attribute to caller
291 xTimeAttribute := l_time_attribute ;
292
293 END IF; --functionName =preFunctionName
294 COMMIT;
295
296 END copyGroupedParameters;
297
298 procedure copyParameters(pSessionId in varchar2,
299 pUserId in varchar2,
300 pPreFunctionName in varchar2,
301 pFunctionName in varchar2,
302 pRegionCode in varchar2,
303 pScheduleId in varchar2 default null,
304 pPageId in varchar2 default null,
305 pRespId in varchar2 default null ) IS
306
307 vOldDimension varchar2(1000);
308 vAttributeCodeTable BISVIEWER.t_char;
309 vDimensionTable BISVIEWER.t_char;
310 vDisplaySeqTable BISVIEWER.t_char;
311
312 cursor cParameters (cpRegionCode varchar2) is
313 select nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension
314 from ak_region_items_vl
315 where region_code = rtrim(cpRegionCode)
316 and node_query_flag = 'Y'
317 order by display_sequence;
318
319 cursor cPageLessParameters (cpRegionCode varchar2) is
320 select nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension
321 from ak_region_items_vl ak
322 where ak.region_code = rtrim(cpRegionCode)
323 and ak.node_query_flag = 'Y'
324 AND nvl(substr(ak.attribute2,1,instr(ak.attribute2,'+')-1), ak.attribute_code) NOT IN (
325 select DISTINCT nvl(substr(b.attribute_name,1,instr(b.attribute_name,'+')-1), attribute_code)
326 from bis_user_attributes b
327 where b.page_id=pPageId
328 and b.user_id =pUserId
329 )
330 order by display_sequence;
331
332 -- ashgarg Bug Fix: 3665085 Changed ak_region_items_vl to ak_region_items
333 CURSOR cParametersWithNestedRegion (cpRegionCode varchar2, cpNestedRegionCode IN VARCHAR2) is
334 SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
335 FROM ak_region_items
336 WHERE region_code=cpNestedRegionCode
337 AND node_query_flag='Y'
338 UNION
339 SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
340 FROM ak_region_items
341 WHERE region_code = rtrim(cpRegionCode)
342 AND node_query_flag = 'Y'
343 AND nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code) NOT IN (
344 SELECT DISTINCT nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)
345 FROM ak_region_items
346 WHERE region_code =cpNestedRegionCode
347 )
348 ORDER BY display_sequence;
349
350 CURSOR cPageLessWithNestedRegion (cpRegionCode varchar2, cpNestedRegionCode IN VARCHAR2) is
351 SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
352 from ak_region_items
353 where region_code=cpNestedRegionCode
354 and node_query_flag='Y'
355 and nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code) not in (
356 SELECT DISTINCT nvl(substr(attribute_name ,1,instr(attribute_name ,'+')-1), attribute_code)
357 FROM bis_user_attributes
358 WHERE page_id=pPageId
359 AND user_id=pUserId
360 )
361 UNION
362 --2. default region w/o page, w/o nested
363 SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
364 FROM ak_region_items
365 WHERE region_code= rtrim(cpRegionCode)
366 AND node_query_flag='Y'
367 AND nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code) NOT IN (
368 SELECT DISTINCT nvl( substr(attribute_name ,1,instr(attribute_name ,'+')-1), attribute_code)
369 FROM bis_user_attributes
370 WHERE page_id=pPageId
371 AND user_id=pUserId
372 UNION
373 SELECT DISTINCT nvl( substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)
374 FROM ak_region_items
375 WHERE region_code =cpNestedRegionCode
376 )
377 ORDER BY display_sequence;
378
379 cursor getViewBy (cpUserId varchar2, cpSessionId varchar2) is
380 select session_value
381 from bis_user_attributes
382 where user_id = cpUserId
383 and session_id = cpSessionId
384 and attribute_name = 'VIEW_BY';
385
386 cursor getTimeAttribute (cpUserId varchar2, cpSessionId varchar2) is
387 select attribute_name
388 from bis_user_attributes
389 where user_id = cpUserId
390 and session_id = cpSessionId
391 and ( attribute_name like 'TIME%' or attribute_name like 'EDW_TIME_M%' );
392
393
394 l_return_status varchar2(32000);
395 l_msg_count NUMBER;
396 l_msg_data varchar2(32000);
397 lNestedRegionCode VARCHAR2(30);
398 lViewByValue VARCHAR2(200);
399 lViewByDimension VARCHAR2(100);
400 lTimeDimLevel VARCHAR2(100);
401 lTimeDimLevels VARCHAR2(100);
402
403 cursor getNestedRegionCode (cpRegionCode varchar2) is
404 SELECT nested_region_code
405 FROM ak_region_items
406 WHERE region_code = cpRegionCode
407 AND nested_region_code IS NOT NULL;
408
409
410 begin
411
412 --delete all the paramete rs for this session for this report only if this is not a recursive drill into the same
413 -- report, else will lose the data
414 --jprabhud added the OR clause for enhancement #2442162
415 IF (pFunctionName <> pPreFunctionName OR pPreFunctionName IS NULL) THEN
416 DELETE FROM bis_user_attributes
417 WHERE function_name=pFunctionName
418 AND user_id=pUserId
419 AND session_id=pSessionId
420 AND schedule_id IS NULL;
421 END IF;
422
423 -- get the nested region code
424 if getNestedRegionCode%ISOPEN then
425 close getNestedRegionCode;
426 end if;
427 open getNestedRegionCode(pRegionCode);
428 fetch getNestedRegionCode INTO lNestedRegionCode;
429 close getNestedRegionCode;
430
431 IF pPageId is not null then
432 -- elete the page parameters
433 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
434 AND schedule_id is null
435 AND user_id=pUserId
436 AND attribute_name in
437 (SELECT attribute_name from bis_user_attributes where user_id=pUserId and page_id=pPageId);
438
439 -- copy the page level parameters
440 insert into bis_user_attributes (USER_ID,
441 FUNCTION_NAME,
442 SESSION_ID,
443 SESSION_VALUE,
444 SESSION_DESCRIPTION,
445 ATTRIBUTE_NAME,
446 DIMENSION,
447 PERIOD_DATE)
448 SELECT pUserId,
449 pFunctionName,
450 pSessionId,
451 SESSION_VALUE,
452 SESSION_DESCRIPTION,
453 ATTRIBUTE_NAME,
454 DIMENSION,
455 PERIOD_DATE
456 FROM bis_user_attributes
457 where user_id = pUserId
458 AND page_id = pPageId;
459
460 END IF;
461
462 IF (pFunctionName = pPreFunctionName ) THEN
463
464 --If sched if is null, the rest of the procedure only copies the params from the preFunction
465 -- tothe function which is not needed for self drilling
466 IF (pScheduleId IS NULL) THEN
467 RETURN;
468 ELSE
469 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
470 and schedule_id is null AND user_id=pUserId
471 AND attribute_name in
472 (SELECT attribute_name from bis_user_attributes where schedule_id=pSCheduleId);
473
474 -- for time from
475 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
476 and schedule_id is null AND user_id=pUserId
477 AND attribute_name in
478 (SELECT attribute_name||'_FROM' from bis_user_attributes where schedule_id=pSCheduleId);
479
480 -- for time to
481 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
482 and schedule_id is null AND user_id=pUserId
483 AND attribute_name in
484 (SELECT attribute_name||'_TO' from bis_user_attributes where schedule_id=pSCheduleId);
485
486 END IF;
487
488 END IF;
489
490 IF lNestedRegionCode IS NOT NULL THEN
491
492 IF pPageId IS NOT null THEN
493
494 if cPageLessWithNestedRegion%ISOPEN then
495 close cPageLessWithNestedRegion;
496 end if;
497 open cPageLessWithNestedRegion(pRegionCode, lNestedRegionCode);
498 fetch cPageLessWithNestedRegion bulk collect into vAttributeCodeTable, vDimensionTable, vDisplaySeqTable;
499 close cPageLessWithNestedRegion;
500 ELSE
501
502 if cParametersWithNestedRegion%ISOPEN then
503 close cParametersWithNestedRegion;
504 end if;
505 open cParametersWithNestedRegion(pRegionCode, lNestedRegionCode);
506 fetch cParametersWithNestedRegion bulk collect into vAttributeCodeTable, vDimensionTable, vDisplaySeqTable;
507 close cParametersWithNestedRegion;
508 END IF; -- pageId
509
510 ELSE
511
512 IF pPageId is not null then
513 if cPageLessParameters%ISOPEN then
514 close cPageLessParameters;
515 end if;
516 open cPageLessParameters(pRegionCode);
517 fetch cPageLessParameters bulk collect into vAttributeCodeTable, vDimensionTable;
518 close cPageLessParameters;
519 else
520 -- get parameters
521 if cParameters%ISOPEN then
522 close cParameters;
523 end if;
524 open cParameters(pRegionCode);
525 fetch cParameters bulk collect into vAttributeCodeTable, vDimensionTable;
526 close cParameters;
527 end if; -- pageId
528 END IF; -- if no nested region
529
530
531 if vAttributeCodeTable.COUNT > 0 then
532 vAttributeCodeTable(vAttributeCodeTable.COUNT+1) := 'BIS_P_ASOF_DATE';
533 for i in vAttributeCodeTable.FIRST..vAttributeCodeTable.LAST loop
534
535 if pScheduleId is null then
536 insert into bis_user_attributes (USER_ID,
537 FUNCTION_NAME,
538 SESSION_ID,
539 SESSION_VALUE,
540 SESSION_DESCRIPTION,
541 ATTRIBUTE_NAME,
542 DIMENSION,
543 PERIOD_DATE)
544 select USER_ID,
545 pFunctionName,
546 SESSION_ID,
547 SESSION_VALUE,
548 SESSION_DESCRIPTION,
549 ATTRIBUTE_NAME,
550 DIMENSION,
551 PERIOD_DATE
552 from bis_user_attributes
553 where function_name = pPreFunctionName
554 and attribute_name in (vAttributeCodeTable(i),
555 vAttributeCodeTable(i)||'_FROM',
556 vAttributeCodeTable(i)||'_TO')
557 and session_id = pSessionId
558 and user_id = pUserId;
559 else
560 insert into bis_user_attributes (USER_ID,
561 FUNCTION_NAME,
562 SESSION_ID,
563 SESSION_VALUE,
564 SESSION_DESCRIPTION,
565 ATTRIBUTE_NAME,
566 DIMENSION,
567 PERIOD_DATE)
568 select pUserId,
569 pFunctionName,
570 pSessionId,
571 SESSION_VALUE,
572 SESSION_DESCRIPTION,
573 ATTRIBUTE_NAME,
574 DIMENSION,
575 PERIOD_DATE
576 from bis_user_attributes
577 where schedule_id = pScheduleId
578 and attribute_name in (vAttributeCodeTable(i),
579 vAttributeCodeTable(i)||'_FROM',
580 vAttributeCodeTable(i)||'_TO');
581
582 end if;
583 if vAttributeCodeTable(i) <> 'BIS_P_ASOF_DATE' then
584 if vDimensionTable(i) = vOldDimension then
585 goto endLoop;
586 else
587 if pScheduleId is null then
588 insert into bis_user_attributes (USER_ID,
589 FUNCTION_NAME,
590 SESSION_ID,
591 SESSION_VALUE,
592 SESSION_DESCRIPTION,
593 ATTRIBUTE_NAME,
594 DIMENSION,
595 PERIOD_DATE)
596 select USER_ID,
597 pFunctionName,
598 SESSION_ID,
599 SESSION_VALUE,
600 SESSION_DESCRIPTION,
601 ATTRIBUTE_NAME,
602 DIMENSION,
603 PERIOD_DATE
604 from bis_user_attributes
605 where function_name = pPreFunctionName
606 and attribute_name = vDimensionTable(i)||'_HIERARCHY'
607 and session_id = pSessionId
608 and user_id = pUserId;
609 else
610 -- if drilling into itself, there is a possibility that this parameter has not been
611 --deleted from the current session
612 --IF (pFunctionName == pPreFunctionName) THEN
613 DELETE FROM bis_user_attributes
614 WHERE session_id =pSessionId
615 AND function_name = pFunctionName
616 AND schedule_id IS NULL
617 AND user_id=pUserId
618 AND attribute_name = vDimensionTable(i)||'_HIERARCHY';
619 --END IF;
620
621 insert into bis_user_attributes (USER_ID,
622 FUNCTION_NAME,
623 SESSION_ID,
624 SESSION_VALUE,
625 SESSION_DESCRIPTION,
626 ATTRIBUTE_NAME,
627 DIMENSION,
628 PERIOD_DATE)
629 select pUserId,
630 pFunctionName,
631 pSessionId,
632 SESSION_VALUE,
633 SESSION_DESCRIPTION,
634 ATTRIBUTE_NAME,
635 DIMENSION,
636 PERIOD_DATE
637 from bis_user_attributes
638 where schedule_id = pScheduleId
639 and attribute_name = vDimensionTable(i)||'_HIERARCHY';
640
641 end if;
642 end if;
643 vOldDimension := vDimensionTable(i);
644 end if;
645 <<endLoop>>
646 null;
647 end loop;
648 end if;
649
650
651 -- only copy those default parameters which were not copied from the driver function
652 -- if drilling into the same report, should have all the reqd attributes.
653 IF (pFunctionName <> pPreFunctionName OR pPreFunctionName IS NULL) THEN
654 -- First copy the default parameters for this report.
655 BIS_PMV_PARAMETERS_PVT.COPY_REMAINING_DEF_PARAMETERS
656 (pFunctionName => pFunctionName
657 ,pUserId => pUserId
658 ,pSessionId => pSessionId
659 ,x_return_status => l_return_status
660 ,x_msg_count => l_msg_count
661 ,x_msg_data => l_msg_data
662 );
663
664 -- serao- 08/23/2002- bug 2514044 - copy form function parameters
665 BIS_PMV_PARAMETERS_PVT.COPY_FORM_FUNCTION_PARAMETERS
666 (pRegionCode => pRegionCode
667 ,pFunctionName => pFunctionName
668 ,pUserId => pUserId
669 ,pSessionId => pSessionId
670 ,pResponsibilityId => nvl(pRespId, icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID))
671 ,x_return_status=>l_return_status
672 ,x_msg_count=> l_msg_count
673 ,x_msg_data=>l_msg_data
674 );
675 --Fix for 2594996
676 IF getViewBy%ISOPEN THEN
677 close getViewBy;
678 END IF;
679 OPEN getViewBy(pUserId, pSessionId);
680 FETCH getViewBy INTO lViewByValue;
681 CLOSE getViewBy;
682 IF (lViewByValue is not null) THEN
683 lViewByDimension := substr(lViewByValue ,1,instr(lViewByValue ,'+')-1) ;
684 IF (lViewByDimension = 'TIME' OR lViewByDimension = 'EDW_TIME_M') THEN
685 IF getTimeAttribute%ISOPEN THEN
686 close getTimeAttribute;
687 END IF;
688 OPEN getTimeAttribute(pUserId, pSessionId);
689 FETCH getTimeAttribute into lTimeDimLevel;
690 CLOSE getTimeAttribute;
691 IF ( lTimeDimLevel is not null ) THEN
692 IF ( instr(lTimeDimLevel,'_FROM') > 1 ) THEN
693 lTimeDimLevels := substr( lTimeDimLevel ,1,instr( lTimeDimLevel ,'_FROM')-1);
694 ELSIF ( instr(lTimeDimLevel,'_TO') > 1 ) THEN
695 lTimeDimLevels := substr( lTimeDimLevel ,1,instr( lTimeDimLevel ,'_TO')-1);
696 END IF;
697
698 DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
699 AND schedule_id is null
700 AND user_id=pUserId
701 AND attribute_name = 'VIEW_BY';
702
703 INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
704 session_id, attribute_name,
705 session_value,
706 dimension,
707 creation_date, created_by,
708 last_update_Date, last_updated_by)
709 VALUES (pUserId, pFunctionName,
710 pSessionId, 'VIEW_BY' ,
711 lTimeDimLevels,
712 lViewByDimension,
713 sysdate, -1, sysdate, -1);
714 END IF;
715 END IF;
716 END IF;
717 --End of Fix for 2594996
718
719 END IF;
720
721
722 commit;
723
724 exception when others then
725 IF getViewBy%ISOPEN THEN
726 close getViewBy;
727 END IF;
728 IF getTimeAttribute%ISOPEN THEN
729 close getTimeAttribute;
730 END IF;
731 end copyParameters;
732
733 END BIS_PMV_DRILL_PVT;