[Home] [Help]
PACKAGE BODY: APPS.BISVIEWER_PMF
Source
1 package body bisviewer_pmf as
2 /* $Header: BISRGPMB.pls 115.24 2002/11/19 18:30:34 kiprabha noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile:~PROD:~PATH:~FILE
5 -----------------------------------------------------------------------------------
6 -- 13-Nov-2000 aleung change Dimension/Dimension Level separator from '.' --
7 -- to '+'. Replace instr(attribute2, '.') with --
8 -- instr(attribute2, '+') throughout the code --
9 -- 12/01/2001 dmarkman org and time are not mandatory(could be NULL) --
10 -- changed query in Get_Target_For_Level and --
11 -- in Get_Target_For_Level --
12 -- 04/10/2001 aleung add scheduleReport and getTargetParm --
13 -- 04/26/2001 aleung add procedure getTotalDimValue and function --
14 -- getTotalDimLevelName --
15 -- 05/01/2001 aleung modified scheduleReport (add form func bisschcf.jsp)--
16 -----------------------------------------------------------------------------------
17
18 gvAll constant varchar2(10) := fnd_message.get_string('BIS', 'ALL');
19
20 Function Get_Target
21 (pMeasureShortName Varchar2,
22 pDimension1Level Varchar2 default NULL,
23 pDimension2Level Varchar2 default NULL,
24 pDimension3Level Varchar2 default NULL,
25 pDimension4Level Varchar2 default NULL,
26 pDimension5Level Varchar2 default NULL,
27 pDimension6Level Varchar2 default NULL,
28 pDimension7Level Varchar2 default NULL,
29
30 pDimension1 Varchar2 default NULL,
31 pDimension2 Varchar2 default NULL,
32 pDimension3 Varchar2 default NULL,
33 pDimension4 Varchar2 default NULL,
34 pDimension5 Varchar2 default NULL,
35 pDimension6 Varchar2 default NULL,
36 pDimension7 Varchar2 default NULL,
37
38 pDimension1LevelValue Varchar2 default NULL,
39 pDimension2LevelValue Varchar2 default NULL,
40 pDimension3LevelValue Varchar2 default NULL,
41 pDimension4LevelValue Varchar2 default NULL,
42 pDimension5LevelValue Varchar2 default NULL,
43 pDimension6LevelValue Varchar2 default NULL,
44 pDimension7LevelValue Varchar2 default NULL,
45
46 pPlanId Varchar2)
47 Return VARCHAR2 is
48
49 vTarget VARCHAR2(1000);
50
51 l_return_Status VARCHAR2(32000);
52 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
53 l_target_rec BIS_TARGET_PUB.TARGET_REC_TYPE;
54 l_error_tbl BIS_UTILITIES_PUB.ERROR_TBL_TYPE;
55 v_range1_low varchar2(200);
56 v_range1_high varchar2(200);
57
58 Begin
59
60 l_target_level_rec.measure_short_name := pMeasureShortName;
61 l_target_level_rec.dimension1_level_short_name := pDimension1Level;
62 l_target_level_rec.dimension2_level_short_name := pDimension2Level;
63 l_target_level_rec.dimension3_level_short_name := pDimension3Level;
64 l_target_level_rec.dimension4_level_short_name := pDimension4Level;
65 l_target_level_rec.dimension5_level_short_name := pDimension5Level;
66 l_target_level_rec.dimension6_level_short_name := pDimension6Level;
67 l_target_level_rec.dimension7_level_short_name := pDimension7Level;
68 l_target_rec.plan_id := pPlanId;
69 l_target_rec.dim1_level_value_id := pDimension1LevelValue;
70 l_target_rec.dim2_level_value_id := pDimension2LevelValue;
71 l_target_rec.dim3_level_value_id := pDimension3LevelValue;
72 l_target_rec.dim4_level_value_id := pDimension4LevelValue;
73 l_target_rec.dim5_level_value_id := pDimension5LevelValue;
74 l_target_rec.dim6_level_value_id := pDimension6LevelValue;
75 l_target_rec.dim7_level_value_id := pDimension7LevelValue;
76
77 BIS_TARGET_PUB.RETRIEVE_TARGET_FROM_SHNMS
78 (p_api_version => 1.0
79 ,p_target_level_rec => l_target_level_rec
80 ,p_Target_Rec => l_target_rec
81 ,x_Target_Level_Rec => l_target_level_rec
82 ,x_Target_Rec => l_target_rec
83 ,x_return_status => l_return_status
84 ,x_error_Tbl => l_error_tbl
85 );
86 IF (l_return_Status = FND_API.G_RET_STS_ERROR) THEN
87 return null;
88 else
89 vTarget := l_target_rec.target;
90 v_range1_low := l_target_rec.range1_low;
91 v_range1_high := l_target_rec.range1_high;
92 if (l_target_rec.target_id is null) or (l_target_rec.target_id = FND_API.G_MISS_NUM) then
93 return null;
94 else
95 if v_range1_low = FND_API.G_MISS_NUM then
96 v_range1_low := null;
97 end if;
98 if v_range1_high = FND_API.G_MISS_NUM then
99 v_range1_high := null;
100 end if;
101
102 return 'T_' || vTarget || '_' || v_range1_low || '_' || v_range1_high;
103 end if;
104 end if;
105
106 End Get_Target;
107
108 FUNCTION Get_Target_For_Level
109 ( p_MEASURE_SHORT_NAME VARCHAR2
110 --, p_ORG_LEVEL VARCHAR2
111 --, p_TIME_LEVEL VARCHAR2
112 , p_DIMENSION1_LEVEL VARCHAR2
113 , p_DIMENSION2_LEVEL VARCHAR2
114 , p_DIMENSION3_LEVEL VARCHAR2
115 , p_DIMENSION4_LEVEL VARCHAR2
116 , p_DIMENSION5_LEVEL VARCHAR2
117 , p_DIMENSION6_LEVEL VARCHAR2
118 , p_DIMENSION7_LEVEL VARCHAR2
119 --, p_ORG_LEVEL_VALUE VARCHAR2
120 --, p_TIME_LEVEL_VALUE VARCHAR2
121 , p_DIMENSION1_LEVEL_VALUE VARCHAR2
122 , p_DIMENSION2_LEVEL_VALUE VARCHAR2
123 , p_DIMENSION3_LEVEL_VALUE VARCHAR2
124 , p_DIMENSION4_LEVEL_VALUE VARCHAR2
125 , p_DIMENSION5_LEVEL_VALUE VARCHAR2
126 , p_DIMENSION6_LEVEL_VALUE VARCHAR2
127 , p_DIMENSION7_LEVEL_VALUE VARCHAR2
128 , p_PLAN VARCHAR2
129 ) RETURN VARCHAR2 IS
130
131 l_target_level NUMBER ;
132 l_target VARCHAR2(1000) ;
133
134 --l_ORG_LEVEL_ID NUMBER;
135 --l_TIME_LEVEL_ID NUMBER;
136 l_DIMENSION1_LEVEL_ID NUMBER;
137 l_DIMENSION2_LEVEL_ID NUMBER;
138 l_DIMENSION3_LEVEL_ID NUMBER;
139 l_DIMENSION4_LEVEL_ID NUMBER;
140 l_DIMENSION5_LEVEL_ID NUMBER;
141 l_DIMENSION6_LEVEL_ID NUMBER;
142 l_DIMENSION7_LEVEL_ID NUMBER;
143
144 MEASURE_SHORT_NAME varchar2(1000):= 'none';
145 MEASURE_NAME varchar2(1000):= 'none';
146
147 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
148 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
149 l_Rstatus varchar2(1);
150 l_Error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
151
152 BEGIN
153
154 -- l_ORG_LEVEL_ID := Get_level_ID ( p_ORG_LEVEL );
155 -- l_TIME_LEVEL_ID := Get_level_ID ( p_TIME_LEVEL );
156 l_DIMENSION1_LEVEL_ID := Get_level_ID ( p_DIMENSION1_LEVEL );
157 l_DIMENSION2_LEVEL_ID := Get_level_ID ( p_DIMENSION2_LEVEL );
158 l_DIMENSION3_LEVEL_ID := Get_level_ID ( p_DIMENSION3_LEVEL );
159 l_DIMENSION4_LEVEL_ID := Get_level_ID ( p_DIMENSION4_LEVEL );
160 l_DIMENSION5_LEVEL_ID := Get_level_ID ( p_DIMENSION5_LEVEL );
161 l_DIMENSION6_LEVEL_ID := Get_level_ID ( p_DIMENSION6_LEVEL );
162 l_DIMENSION7_LEVEL_ID := Get_level_ID ( p_DIMENSION7_LEVEL );
163
164 -- Debugging messages start: --------------------------------------------------
165 /*
166
167 --htp.print('<!-- l1l_ORG_LEVEL_ID :' || l_ORG_LEVEL_ID||'-->');
168 --htp.print('<!-- l1l_TIME_LEVEL_ID :' || l_TIME_LEVEL_ID||'-->');
169 htp.print('<!-- l1l_DIMENSION1_LEVEL_ID :' || l_DIMENSION1_LEVEL_ID||'-->' );
170 htp.print('<!-- l1l_DIMENSION2_LEVEL_ID :' || l_DIMENSION2_LEVEL_ID ||'-->');
171 htp.print('<!-- l1l_DIMENSION3_LEVEL_ID :' || l_DIMENSION3_LEVEL_ID||'-->' );
172 htp.print('<!-- l1l_DIMENSION4_LEVEL_ID :' || l_DIMENSION4_LEVEL_ID ||'-->' );
173 htp.print('<!-- l1l_DIMENSION5_LEVEL_ID :' || l_DIMENSION5_LEVEL_ID||'-->' );
174 htp.print('<!-- l1l_DIMENSION6_LEVEL_ID :' || l_DIMENSION6_LEVEL_ID ||'-->' );
175 htp.print('<!-- l1l_DIMENSION7_LEVEL_ID :' || l_DIMENSION7_LEVEL_ID||'-->' );
176 htp.print('<!-- p_MEASURE_SHORT_NAME: '||p_MEASURE_SHORT_NAME||'-->');
177
178 --*/
179 -- Debugging messages end --------------------------------------------------
180
181
182 -- dmarkman 07/20/2000 do 'select' on BISBV_TARGET_LEVELS instead of BIS_TARGET_LEVELS, BIS_INDICATORS
183 -- dmarkman 12/01/2001 org and time are not mandatory(could be NULL)
184 --/*
185 begin
186 SELECT TARGET_LEVEL_ID,MEASURE_SHORT_NAME,MEASURE_NAME
187 INTO l_target_level,MEASURE_SHORT_NAME,MEASURE_NAME
188 FROM BISBV_TARGET_LEVELS L
189 , BISBV_PERFORMANCE_MEASURES M
190 WHERE M.MEASURE_ID = L.MEASURE_ID
191 AND M.MEASURE_SHORT_NAME = p_MEASURE_SHORT_NAME
192 --AND ((l_ORG_LEVEL_ID IS NOT NULL
193 --AND L.ORG_LEVEL_ID = l_ORG_LEVEL_ID)
194 --OR (l_ORG_LEVEL_ID IS NULL))
195 --AND ((l_TIME_LEVEL_ID IS NOT NULL
196 --AND L.TIME_LEVEL_ID = l_TIME_LEVEL_ID)
197 --OR (l_TIME_LEVEL_ID IS NULL))
198 AND ((l_DIMENSION1_LEVEL_ID IS NOT NULL
199 AND L.DIMENSION1_LEVEL_ID = l_DIMENSION1_LEVEL_ID)
200 OR (l_DIMENSION1_LEVEL_ID IS NULL))
201 AND ((l_DIMENSION2_LEVEL_ID IS NOT NULL
202 AND L.DIMENSION2_LEVEL_ID = l_DIMENSION2_LEVEL_ID)
203 OR (l_DIMENSION2_LEVEL_ID IS NULL))
204 AND ((l_DIMENSION3_LEVEL_ID IS NOT NULL
205 AND L.DIMENSION3_LEVEL_ID = l_DIMENSION3_LEVEL_ID)
206 OR (l_DIMENSION3_LEVEL_ID IS NULL))
207 AND ((l_DIMENSION4_LEVEL_ID IS NOT NULL
208 AND L.DIMENSION4_LEVEL_ID = l_DIMENSION4_LEVEL_ID)
209 OR (l_DIMENSION4_LEVEL_ID IS NULL))
210 AND ((l_DIMENSION5_LEVEL_ID IS NOT NULL
211 AND L.DIMENSION5_LEVEL_ID = l_DIMENSION5_LEVEL_ID)
212 OR (l_DIMENSION5_LEVEL_ID IS NULL))
213 AND ((l_DIMENSION6_LEVEL_ID IS NOT NULL
214 AND L.DIMENSION6_LEVEL_ID = l_DIMENSION6_LEVEL_ID)
215 OR (l_DIMENSION6_LEVEL_ID IS NULL))
216 AND ((l_DIMENSION7_LEVEL_ID IS NOT NULL
217 AND L.DIMENSION7_LEVEL_ID = l_DIMENSION7_LEVEL_ID)
218 OR (l_DIMENSION7_LEVEL_ID IS NULL));
219
220 exception when others then
221 --htp.print('<!-- Cannot retrieve target level!!! -->');
222 --htp.p('<!--'||SQLCODE||'-->');
223 --htp.p('<!--'||SQLERRM||'-->');
224 return null;
225 end;
226 --*/
227 --htp.print(' Get_Target_For_Level after query flag');
228
229 /*
230 l_Measure_Rec.Measure_Short_Name := p_MEASURE_SHORT_NAME;
231 BIS_MEASURE_PUB.Retrieve_Measure(p_api_version => 1.0,
232 p_Measure_Rec => l_Measure_Rec,
233 p_all_info => FND_API.G_FALSE,
234 x_Measure_Rec => l_Measure_Rec,
235 x_return_status => l_Rstatus,
236 x_error_Tbl => l_Error_Tbl);
237
238 if l_Rstatus = FND_API.G_RET_STS_ERROR then
239 htp.print('error1');
240 else
241 htp.print('m id: '||l_Measure_Rec.Measure_ID);
242 l_Target_Level_Rec.Measure_ID := l_Measure_Rec.Measure_ID;
243 l_Target_Level_Rec.Org_Level_ID := l_ORG_LEVEL_ID;
244 l_Target_Level_Rec.Time_Level_ID := l_TIME_LEVEL_ID;
245 l_Target_Level_Rec.Dimension1_Level_ID := l_DIMENSION1_LEVEL_ID;
246 l_Target_Level_Rec.Dimension2_Level_ID := l_DIMENSION2_LEVEL_ID;
247 l_Target_Level_Rec.Dimension3_Level_ID := l_DIMENSION3_LEVEL_ID;
248 l_Target_Level_Rec.Dimension4_Level_ID := l_DIMENSION4_LEVEL_ID;
249 l_Target_Level_Rec.Dimension5_Level_ID := l_DIMENSION5_LEVEL_ID;
250
251 BIS_TARGET_LEVEL_PUB.Retrieve_Target_Level(p_api_version => 1.0,
252 p_Target_Level_Rec => l_Target_Level_Rec,
253 p_all_info => FND_API.G_FALSE,
254 x_Target_Level_Rec => l_Target_Level_Rec,
255 x_return_status => l_Rstatus,
256 x_error_Tbl => l_Error_Tbl);
257 if l_Rstatus = FND_API.G_RET_STS_ERROR then
258 htp.print('error2');
259 else
260
261 l_target_level := l_Target_Level_Rec.Target_Level_ID;
262 MEASURE_SHORT_NAME := l_Measure_Rec.Measure_Short_Name;
263 MEASURE_NAME := l_Measure_Rec.Measure_Name;
264 end if;
265 end if;
266 */
267
268 -- Debugging messages: --------------------------------------------------
269 /*
270 htp.print('<!-- l_target_level: ' || l_target_level||'-->' );
271 htp.print('<!-- MEASURE_SHORT_NAME: ' || MEASURE_SHORT_NAME||'-->' );
272 htp.print('<!-- MEASURE_NAME: ' || MEASURE_NAME||'-->' );
273 --*/
274
275 l_target := Get_Target_Value( l_TARGET_LEVEL
276 --, p_ORG_LEVEL_VALUE
277 --, p_TIME_LEVEL_VALUE
278 , p_DIMENSION1_LEVEL_VALUE
279 , p_DIMENSION2_LEVEL_VALUE
280 , p_DIMENSION3_LEVEL_VALUE
281 , p_DIMENSION4_LEVEL_VALUE
282 , p_DIMENSION5_LEVEL_VALUE
283 , p_DIMENSION6_LEVEL_VALUE
284 , p_DIMENSION7_LEVEL_VALUE
285 , p_PLAN
286 ) ;
287 RETURN l_target ;
288
289 EXCEPTION
290
291 WHEN OTHERS THEN
292 RETURN NULL ;
293
294 END Get_Target_For_Level;
295
296 FUNCTION Get_Target_Value
297 ( p_TARGET_LEVEL_ID VARCHAR2
298 --, p_ORG_LEVEL_VALUE VARCHAR2
299 --, p_TIME_LEVEL_VALUE VARCHAR2
300 , p_DIMENSION1_LEVEL_VALUE VARCHAR2
301 , p_DIMENSION2_LEVEL_VALUE VARCHAR2
302 , p_DIMENSION3_LEVEL_VALUE VARCHAR2
303 , p_DIMENSION4_LEVEL_VALUE VARCHAR2
304 , p_DIMENSION5_LEVEL_VALUE VARCHAR2
305 , p_DIMENSION6_LEVEL_VALUE VARCHAR2
306 , p_DIMENSION7_LEVEL_VALUE VARCHAR2
307 , p_PLAN VARCHAR2
308 ) RETURN VARCHAR2 IS
309
310 l_target VARCHAR2(1000);
311 v_range1_low VARCHAR2(1000);
312 v_range1_high VARCHAR2(1000);
313
314 vTargetRec BIS_TARGET_PUB.Target_Rec_Type;
315 vRstatus varchar2(1);
316 vErrorTbl BIS_UTILITIES_PUB.Error_Tbl_Type;
317
318 BEGIN
319
320
321 -- Debugging messages start: --------------------------------------------------
322 /*
323
324 htp.print('<!-- ACT p_TARGET_LEVEL_ID:' || p_TARGET_LEVEL_ID||'-->' );
325 --htp.print('<!-- ACT p_ORG_LEVEL_VALUE:' || p_ORG_LEVEL_VALUE||'-->' );
326 --htp.print('<!-- ACT p_TIME_LEVEL_VALUE:' || p_TIME_LEVEL_VALUE||'-->' );
327 htp.print('<!-- ACT p_DIMENSION1_LEVEL_VALUE:' || p_DIMENSION1_LEVEL_VALUE||'-->' );
328 htp.print('<!-- ACT p_DIMENSION2_LEVEL_VALUE:' || p_DIMENSION2_LEVEL_VALUE||'-->' );
329 htp.print('<!-- ACT p_DIMENSION3_LEVEL_VALUE:' || p_DIMENSION3_LEVEL_VALUE||'-->' );
330 htp.print('<!-- ACT p_DIMENSION4_LEVEL_VALUE:' || p_DIMENSION4_LEVEL_VALUE||'-->' );
331 htp.print('<!-- ACT p_DIMENSION5_LEVEL_VALUE:' || p_DIMENSION5_LEVEL_VALUE||'-->');
332 htp.print('<!-- ACT p_DIMENSION6_LEVEL_VALUE:' || p_DIMENSION6_LEVEL_VALUE||'-->' );
333 htp.print('<!-- ACT p_DIMENSION7_LEVEL_VALUE:' || p_DIMENSION7_LEVEL_VALUE||'-->');
334 htp.print('<!-- ACT p_PLAN:' || p_PLAN||'-->' );
335 --*/
336 -- Debugging messages end ----------------------------------------------------
337
338 -- dmarkman 07/20 do 'select' on BISBV_TARGETS instead of BIS_TRAGET_VALUES
339 -- dmarkman 12/01/2001 org and time are not mandatory(could be NULL)
340 /*
341
342 SELECT TARGET, range1_low, range1_high
343 INTO l_target, v_range1_low, v_range1_high
344 FROM BISBV_TARGETS
348 --OR (P_ORG_LEVEL_VALUE IS NULL))
345 WHERE TARGET_LEVEL_ID = P_TARGET_LEVEL_ID
346 --AND (( P_ORG_LEVEL_VALUE IS NOT NULL
347 --AND ORG_LEVEL_VALUE_ID = P_ORG_LEVEL_VALUE )
349 --AND (( UPPER(TIME_LEVEL_VALUE_ID) IS NOT NULL
350 --AND UPPER(TIME_LEVEL_VALUE_ID) = UPPER(p_TIME_LEVEL_VALUE) )
351 --OR ( UPPER(TIME_LEVEL_VALUE_ID) IS NULL))
352 AND ((p_DIMENSION1_LEVEL_VALUE IS NOT NULL
353 AND DIM1_LEVEL_VALUE_ID = p_DIMENSION1_LEVEL_VALUE )
354 OR (p_DIMENSION1_LEVEL_VALUE IS NULL))
355 AND ((p_DIMENSION2_LEVEL_VALUE IS NOT NULL
356 AND DIM2_LEVEL_VALUE_ID = p_DIMENSION2_LEVEL_VALUE )
357 OR (p_DIMENSION2_LEVEL_VALUE IS NULL))
358 AND ((p_DIMENSION3_LEVEL_VALUE IS NOT NULL
359 AND DIM3_LEVEL_VALUE_ID = p_DIMENSION3_LEVEL_VALUE )
360 OR (p_DIMENSION3_LEVEL_VALUE IS NULL))
361 AND ((p_DIMENSION4_LEVEL_VALUE IS NOT NULL
362 AND DIM4_LEVEL_VALUE_ID = p_DIMENSION4_LEVEL_VALUE )
363 OR (p_DIMENSION4_LEVEL_VALUE IS NULL))
364 AND ((p_DIMENSION5_LEVEL_VALUE IS NOT NULL
365 AND DIM5_LEVEL_VALUE_ID = p_DIMENSION5_LEVEL_VALUE )
366 OR (p_DIMENSION5_LEVEL_VALUE IS NULL))
367 AND ((p_DIMENSION6_LEVEL_VALUE IS NOT NULL
368 AND DIM6_LEVEL_VALUE_ID = p_DIMENSION6_LEVEL_VALUE )
369 OR (p_DIMENSION6_LEVEL_VALUE IS NULL))
370 AND ((p_DIMENSION7_LEVEL_VALUE IS NOT NULL
371 AND DIM7_LEVEL_VALUE_ID = p_DIMENSION7_LEVEL_VALUE )
372 OR (p_DIMENSION7_LEVEL_VALUE IS NULL))
373 AND PLAN_ID = p_plan;
374
375 -- */
376 --/*
377 -- aleung, 11/08/00, use API to access PMF table BISBV_TARGETS
378 vTargetRec.Target_Level_ID := P_TARGET_LEVEL_ID;
379 --vTargetRec.Org_level_value_id := P_ORG_LEVEL_VALUE;
380 vTargetRec.Plan_ID := p_plan;
381 --vTargetRec.Time_level_Value_id := p_TIME_LEVEL_VALUE;
382 vTargetRec.Dim1_Level_Value_ID := p_DIMENSION1_LEVEL_VALUE;
383 vTargetRec.Dim2_Level_Value_ID := p_DIMENSION2_LEVEL_VALUE;
384 vTargetRec.Dim3_Level_Value_ID := p_DIMENSION3_LEVEL_VALUE;
385 vTargetRec.Dim4_Level_Value_ID := p_DIMENSION4_LEVEL_VALUE;
386 vTargetRec.Dim5_Level_Value_ID := p_DIMENSION5_LEVEL_VALUE;
387 vTargetRec.Dim6_Level_Value_ID := p_DIMENSION6_LEVEL_VALUE;
388 vTargetRec.Dim7_Level_Value_ID := p_DIMENSION7_LEVEL_VALUE;
389
390 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
391 p_Target_Rec => vTargetRec,
392 p_all_info => FND_API.G_FALSE,
393 x_Target_Rec => vTargetRec,
394 x_return_status => vRstatus,
395 x_error_Tbl => vErrorTbl);
396
397 if vRstatus = FND_API.G_RET_STS_ERROR then
398 return null;
399 else
400 l_target := vTargetRec.Target;
401 v_range1_low := vTargetRec.Range1_low;
402 v_range1_high := vTargetRec.Range1_high;
403 end if;
404 --*/
405
406
407 --htp.print('<!-- T_' || l_target || '_' || v_range1_low || '_' || v_range1_high||'-->');
408
409 return 'T_' || l_target || '_' || v_range1_low || '_' || v_range1_high;
410
411 EXCEPTION
412
413 When others then
414 return NULL;
415
416 end Get_Target_Value;
417
418
419 FUNCTION Get_LEVEL_ID ( p_Level_Short_Name IN VARCHAR2 ) RETURN NUMBER IS
420 l_level_id NUMBER ;
421 l_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
422 l_Rstatus varchar2(1);
423 l_Error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
424
425 Begin
426 If p_Level_Short_Name IS NULL Then
427 Return NULL ;
428 Else
429
430 -- Debugging messages: --------------------------------------------------
431 -- htp.br;
432 -- htp.print('FUNCTION Get_LEVEL_ID p_Level_Short_Name: ' || p_Level_Short_Name);
433 -- htp.br;
434
435 /*
436 Select LEVEL_ID
437 Into l_level_id
438 From BIS_LEVELS
439 Where short_name = p_Level_Short_Name ;
440 -- */
441 --/*
442 -- aleung, 11/08/00, use API to access PMF table BIS_LEVELS
443 l_Level_Rec.Dimension_Level_Short_Name := p_Level_Short_Name;
444 /*
445 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level(p_api_version => 1.0,
446 p_Dimension_Level_Rec => l_Level_Rec,
447 x_Dimension_Level_Rec => l_Level_Rec,
448 x_return_status => l_Rstatus,
449 x_error_Tbl => l_Error_Tbl); */
450
451 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion(p_api_version => 1.0,
452 p_Dimension_Level_Rec => l_Level_Rec,
453 x_Dimension_Level_Rec => l_Level_Rec,
454 x_return_status => l_Rstatus,
455 x_error_Tbl => l_Error_Tbl);
456
457 if l_Rstatus = FND_API.G_RET_STS_ERROR then
458 return null;
459 else
460 l_level_id := l_Level_Rec.Dimension_Level_ID;
461 end if;
462 --*/
463
464 -- htp.print('l_level_id : ' || l_level_id);
465
466 Return l_level_id ;
467 End If ;
468 EXCEPTION
472 End Get_Level_ID ;
469 When Others Then
470 Return NULL ;
471
473
474
475 procedure toleranceTest(pTargetLowHigh in varchar2,
476 pValue in varchar2,
477 pTarget out NOCOPY number,
478 pToleranceFlag out NOCOPY varchar2)
479
480 -- pTargetLowHigh like 'T_100_20_30'
481
482 is
483
484 v_TargetLowHigh VARCHAR2(1000) := pTargetLowHigh;
485 v_target number;
486 v_range_low number;
487 v_range_high number;
488
489 v_brake1 number;
490 v_brake2 number;
491 v_brake3 number;
492
493 v_comparison_result varchar2(1000);
494 v_target_rec BIS_TARGET_PUB.Target_Rec_Type;
495 v_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
496
497 begin
498
499 pToleranceFlag :='ON';
500
501 v_brake1 := instr(v_TargetLowHigh,'_',1,1);
502 v_brake2 := instr(v_TargetLowHigh,'_',1,2);
503 v_brake3 := instr(v_TargetLowHigh,'_',1,3);
504
505 v_target := nvl(substr(v_TargetLowHigh,v_brake1+1,v_brake2-v_brake1-1),0);
506 v_range_low := substr(v_TargetLowHigh,v_brake2+1,v_brake3-v_brake2-1);
507 v_range_high := substr(v_TargetLowHigh,v_brake3+1);
508
509 pTarget := v_target;
510
511 v_target_rec.Target := v_target;
512 v_target_rec.Range1_high := v_range_high ;
513 v_target_rec.Range1_low := v_range_low;
514 v_actual_rec.Actual := pValue;
515
516 BIS_GENERIC_PLANNER_PVT.Compare_Values
517 ( p_target_rec => v_target_rec
518 , p_actual_rec => v_actual_rec
519 , x_comparison_result => v_comparison_result
520 );
521
522 if v_comparison_result <> BIS_GENERIC_PLANNER_PVT.G_COMP_RESULT_NORMAL then
523 pToleranceFlag := 'OFF';
524 end if;
525
526 end toleranceTest;
527
528 Function Schedule_Alert_Link
529 (pMeasureShortName Varchar2,
530 pOrgLevel Varchar2,
531 pTimeLevel Varchar2,
532 pDimension1Level Varchar2 default NULL,
533 pDimension2Level Varchar2 default NULL,
534 pDimension3Level Varchar2 default NULL,
535 pDimension4Level Varchar2 default NULL,
536 pDimension5Level Varchar2 default NULL,
537 pDimension1 Varchar2 default NULL,
538 pDimension2 Varchar2 default NULL,
539 pDimension3 Varchar2 default NULL,
540 pDimension4 Varchar2 default NULL,
541 pDimension5 Varchar2 default NULL,
542 pOrgLevelValue Varchar2 ,
543 pTimeLevelValue Varchar2 ,
544 pDimension1LevelValue Varchar2 default NULL,
545 pDimension2LevelValue Varchar2 default NULL,
546 pDimension3LevelValue Varchar2 default NULL,
547 pDimension4LevelValue Varchar2 default NULL,
548 pDimension5LevelValue Varchar2 default NULL,
549 pPlanId Varchar2)
550 Return VARCHAR2 is
551
552 vDimension1Level Varchar2(80) := pDimension1Level;
553 vDimension2Level Varchar2(80) := pDimension2Level;
554 vDimension3Level Varchar2(80) := pDimension3Level;
555 vDimension4Level Varchar2(80) := pDimension4Level;
556 vDimension5Level Varchar2(80) := pDimension5Level;
557
558 vDimension1LevelValue Varchar2(80) := pDimension1LevelValue ;
559 vDimension2LevelValue Varchar2(80) := pDimension2LevelValue ;
560 vDimension3LevelValue Varchar2(80) := pDimension3LevelValue ;
561 vDimension4LevelValue Varchar2(80) := pDimension4LevelValue ; --minleung
562 vDimension5LevelValue Varchar2(80) := pDimension5LevelValue ;
563
564
565 vGeoLevel Varchar2(80);
566 vGeoLevelValue Varchar2(80);
567 vCountryCode Varchar2(80);
568 vAreaCode Varchar2(80);
569
570 vTimeLevelValue Varchar2(80) := pTimeLevelValue;
571 v_schedule_alert_URL VARCHAR2(32000);
572
573 Begin
574 if pDimension1 is not null then
575 if pDimension1LevelValue = gvAll then
576 vDimension1Level := 'TOTAL ' || pDimension1;
577 vDimension1LevelValue := '-1';
578 end if;
579 else
580 vDimension1Level := NULL;
581 vDimension1LevelValue := NULL;
582 end if;
583
584 if pDimension2 is not null then
585 if pDimension2LevelValue = gvAll then
586 vDimension2Level := 'TOTAL ' || pDimension2 ;
587 vDimension2LevelValue := '-1';
588 end if;
589 else
590 vDimension2Level := NULL;
591 vDimension2LevelValue := NULL;
592 end if;
593
594 if pDimension3 is not null then
595 if pDimension3LevelValue = gvAll then
596 vDimension3Level := 'TOTAL ' || pDimension3;
597 vDimension3LevelValue := '-1';
598 end if;
599 else
600 vDimension3Level := NULL;
601 vDimension3LevelValue := NULL;
602 end if;
603
604 if pDimension4 is not null then
605 if pDimension4LevelValue = gvAll then
606 vDimension4Level := 'TOTAL ' || pDimension4;
607 vDimension4LevelValue := '-1';
608 end if;
609 else
610 vDimension4Level := NULL;
611 vDimension4LevelValue := NULL;
615 if pDimension5LevelValue = gvAll then
612 end if;
613
614 if pDimension5 is not null then
616 vDimension5Level := 'TOTAL ' || pDimension5;
617 vDimension5LevelValue := '-1';
618 end if;
619 else
620 vDimension5Level := NULL;
621 vDimension5LevelValue := NULL;
622 end if;
623
624
625 if (pDimension1 = 'GEOGRAPHY' or pDimension2 = 'GEOGRAPHY' or pDimension3 = 'GEOGRAPHY' or
626 pDimension4 = 'GEOGRAPHY' or pDimension5 = 'GEOGRAPHY') then
627
628 if pDimension1 = 'GEOGRAPHY' then
629 vGeoLevel := vDimension1Level;
630 vGeoLevelValue := vDimension1LevelValue;
631 elsif pDimension2 = 'GEOGRAPHY' then
632 vGeoLevel := vDimension2Level;
633 vGeoLevelValue := vDimension2LevelValue;
634 elsif pDimension3 = 'GEOGRAPHY' then
635 vGeoLevel := vDimension3Level;
636 vGeoLevelValue := vDimension3LevelValue;
637 elsif pDimension4 = 'GEOGRAPHY' then
638 vGeoLevel := vDimension4Level;
639 vGeoLevelValue := vDimension4LevelValue;
640 elsif pDimension5 = 'GEOGRAPHY' then
641 vGeoLevel := vDimension5Level;
642 vGeoLevelValue := vDimension5LevelValue;
643 end if;
644
645 if vGeoLevel = 'COUNTRY' then
646
647
648 select bth.parent_territory_code
649 into vAreaCode
650 from bis_territory_hierarchies_v bth
651 where bth.parent_territory_type = 'AREA'
652 and bth.child_territory_type = 'COUNTRY'
653 and bth.child_territory_code = vGeoLevelValue;
654
655 vGeoLevelValue := vAreaCode ||'+' || vGeoLevelValue;
656
657 elsif vGeoLevel = 'REGION' /* Region */ then
658
659 select bth.parent_territory_code
660 into vCountryCode
661 from bis_territory_hierarchies_v bth
662 where bth.parent_territory_type = 'COUNTRY'
663 and bth.child_territory_type = 'REGION'
664 and bth.child_territory_code = vGeoLevelValue;
665
666 select bth.parent_territory_name
667 into vAreaCode
668 from bis_territory_hierarchies_v bth
669 where bth.parent_territory_type = 'AREA'
670 and bth.child_territory_type = 'COUNTRY'
671 and bth.child_territory_code = vCountryCode;
672
673 vGeoLevelValue := vAreaCode ||'+' || vCountryCode || '+' || vGeoLevelValue;
674 end if;
675
676 if pDimension1 = 'GEOGRAPHY' then
677 vDimension1LevelValue := vGeoLevelValue;
678 elsif pDimension2 = 'GEOGRAPHY' then
679 vDimension2LevelValue := vGeoLevelValue;
680 elsif pDimension3 = 'GEOGRAPHY' then
681 vDimension3LevelValue := vGeoLevelValue;
682 elsif pDimension4 = 'GEOGRAPHY' then
683 vDimension4LevelValue := vGeoLevelValue;
684 elsif pDimension5 = 'GEOGRAPHY' then
685 vDimension5LevelValue := vGeoLevelValue;
686 end if;
687
688 end if;
689
690 v_schedule_alert_URL := Schedule_Alert_URL
691 (pMeasureShortName,
692 pOrgLevel,
693 pTimeLevel,
694 vDimension1Level ,
695 vDimension2Level ,
696 vDimension3Level ,
697 vDimension4Level ,
698 vDimension5Level ,
699 pOrgLevelValue ,
700 vTimeLevelValue ,
701 vDimension1LevelValue ,
702 vDimension2LevelValue ,
703 vDimension3LevelValue ,
704 vDimension4LevelValue ,
705 vDimension5LevelValue ,
706 pPlanId );
707
708
709 -----------------------------------------------------------------------
710 -- comment out ScheduleAkert link
711
712 -- upgrade text format (aleung 8/22/2000)
713 RETURN '<A HREF=' || v_schedule_alert_URL || ' TARGET= >' || '<span class=OraGlobalButtonText>'
714 || fnd_message.get_string('BIS', 'SCHEDULE_ALERT') || '</span> </A>';
715
716 --commnet out ScheduleAkert link */
717
718 ------------------------------------------------------------------------
719
720 --RETURN NULL;
721
722 End Schedule_Alert_Link;
723
724 FUNCTION Schedule_Alert_URL
725 ( p_MEASURE_SHORT_NAME VARCHAR2
726 , p_ORG_LEVEL VARCHAR2
727 , p_TIME_LEVEL VARCHAR2
728 , p_DIMENSION1_LEVEL VARCHAR2
729 , p_DIMENSION2_LEVEL VARCHAR2
730 , p_DIMENSION3_LEVEL VARCHAR2
731 , p_DIMENSION4_LEVEL VARCHAR2
732 , p_DIMENSION5_LEVEL VARCHAR2
733 , p_ORG_LEVEL_VALUE VARCHAR2
734 , p_TIME_LEVEL_VALUE VARCHAR2
735 , p_DIMENSION1_LEVEL_VALUE VARCHAR2
736 , p_DIMENSION2_LEVEL_VALUE VARCHAR2
737 , p_DIMENSION3_LEVEL_VALUE VARCHAR2
738 , p_DIMENSION4_LEVEL_VALUE VARCHAR2
739 , p_DIMENSION5_LEVEL_VALUE VARCHAR2
740 , p_PLAN VARCHAR2
741 ) RETURN VARCHAR2 IS
742
743 l_ORG_LEVEL_ID NUMBER;
747 l_DIMENSION3_LEVEL_ID NUMBER;
744 l_TIME_LEVEL_ID NUMBER;
745 l_DIMENSION1_LEVEL_ID NUMBER;
746 l_DIMENSION2_LEVEL_ID NUMBER;
748 l_DIMENSION4_LEVEL_ID NUMBER;
749 l_DIMENSION5_LEVEL_ID NUMBER;
750
751 l_target_level_id NUMBER;
752 v_schedule_alert_URL VARCHAR2(32000);
753 v_meas_id NUMBER;
754
755 BEGIN
756
757 l_ORG_LEVEL_ID := Get_level_ID ( p_ORG_LEVEL );
758 l_TIME_LEVEL_ID := Get_level_ID ( p_TIME_LEVEL );
759 l_DIMENSION1_LEVEL_ID := Get_level_ID ( p_DIMENSION1_LEVEL );
760 l_DIMENSION2_LEVEL_ID := Get_level_ID ( p_DIMENSION2_LEVEL );
761 l_DIMENSION3_LEVEL_ID := Get_level_ID ( p_DIMENSION3_LEVEL );
762 l_DIMENSION4_LEVEL_ID := Get_level_ID ( p_DIMENSION4_LEVEL );
763 l_DIMENSION5_LEVEL_ID := Get_level_ID ( p_DIMENSION5_LEVEL );
764
765
766 SELECT distinct TARGET_LEVEL_ID, L.MEASURE_ID
767 INTO l_target_level_id, v_meas_id
768 FROM BISBV_TARGET_LEVELS L
769 , BISBV_PERFORMANCE_MEASURES M
770 WHERE M.MEASURE_ID = L.MEASURE_ID
771 AND M.MEASURE_SHORT_NAME = p_MEASURE_SHORT_NAME
772 AND L.ORG_LEVEL_ID = l_ORG_LEVEL_ID
773 AND L.TIME_LEVEL_ID = l_TIME_LEVEL_ID
774 AND ((l_DIMENSION1_LEVEL_ID IS NOT NULL
775 AND L.DIMENSION1_LEVEL_ID = l_DIMENSION1_LEVEL_ID)
776 OR (l_DIMENSION1_LEVEL_ID IS NULL))
777 AND ((l_DIMENSION2_LEVEL_ID IS NOT NULL
778 AND L.DIMENSION2_LEVEL_ID = l_DIMENSION2_LEVEL_ID)
779 OR (l_DIMENSION2_LEVEL_ID IS NULL))
780 AND ((l_DIMENSION3_LEVEL_ID IS NOT NULL
781 AND L.DIMENSION3_LEVEL_ID = l_DIMENSION3_LEVEL_ID)
782 OR (l_DIMENSION3_LEVEL_ID IS NULL))
783 AND ((l_DIMENSION4_LEVEL_ID IS NOT NULL
784 AND L.DIMENSION4_LEVEL_ID = l_DIMENSION4_LEVEL_ID)
785 OR (l_DIMENSION4_LEVEL_ID IS NULL))
786 AND ((l_DIMENSION5_LEVEL_ID IS NOT NULL
787 AND L.DIMENSION5_LEVEL_ID = l_DIMENSION5_LEVEL_ID)
788 OR (l_DIMENSION5_LEVEL_ID IS NULL));
789
790
791 --------------------------------------------------------------
792 /* commnet out ScheduleAlert URL
793
794 BIS_PMF_ALERT_REG_PVT.BuildAlertRegistrationURL
795 ( p_measure_id => v_meas_id
796 , p_target_level_id => l_target_level_id
797 , p_plan_id => p_plan
798 , p_parameter1levelId => l_ORG_LEVEL_ID
799 , p_parameter1ValueId => p_ORG_LEVEL_VALUE
800 , p_parameter2levelId => l_TIME_LEVEL_ID
801 , p_parameter2ValueId => p_TIME_LEVEL_VALUE
802 , p_parameter3levelId => l_DIMENSION1_LEVEL_ID
803 , p_parameter3ValueId => p_DIMENSION1_LEVEL_VALUE
804 , p_parameter4levelId => l_DIMENSION2_LEVEL_ID
805 , p_parameter4ValueId => p_DIMENSION2_LEVEL_VALUE
806 , p_parameter5levelId => l_DIMENSION3_LEVEL_ID
807 , p_parameter5ValueId => p_DIMENSION3_LEVEL_VALUE
808 , p_parameter6levelId => l_DIMENSION4_LEVEL_ID
809 , p_parameter6ValueId => p_DIMENSION4_LEVEL_VALUE
810 , p_parameter7levelId => l_DIMENSION5_LEVEL_ID
811 , p_parameter7ValueId => p_DIMENSION5_LEVEL_VALUE
812 , p_viewByLevelId => l_TIME_LEVEL_ID
813 , x_alert_url => v_schedule_alert_URL
814 );
815
816 --comment out ScheduleAlert URL */
817 -----------------------------------------------------------
818
819 RETURN v_schedule_alert_URL;
820
821
822 EXCEPTION
823
824 WHEN OTHERS THEN
825
826 RETURN NULL;
827
828 END Schedule_Alert_URL;
829
830
831 procedure scheduleReports(
832 pRegionCode in varchar2,
833 pFunctionName in varchar2,
834 pUserId in varchar2,
835 pSessionId in varchar2,
836 pResponsibilityId in varchar2,
837 pReportTitle in varchar2 default NULL,
838 pApplicationId in varchar2 default NULL,
839 pParmPrint in varchar2 default NULL,
840 pRequestType in varchar2 default 'R',
841 pPlugId in varchar2 default NULL,
842 pGraphType in varchar2 default NULL
843 )
844 is
845 vScheduleURL varchar2(5000);
846 --l_customize_URL varchar2(32000);
847 l_customize_id pls_integer;
848 l_fn_Responsibility_id number;
849 l_application_id number;
850 l_user_id number;
851 l_rowid varchar2(1000);
852
853 l_form_func_name varchar2(1000) := 'BIS_SCHEDULE_PAGE';
854 l_form_func_call varchar2(1000) := 'bissched.jsp';
855
856 vParams varchar2(2000);
857
858 CURSOR cFndResp (pRespId in varchar2) is
859 select application_id
860 from fnd_responsibility
861 where responsibility_id=pRespId;
862
863 begin
864 l_user_id := pUserId;
865
866 l_fn_responsibility_id := nvl(pResponsibilityId, icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID));
867 if pApplicationId is null then
868 if cFNDResp%ISOPEN then
869 CLOSE cFNDResp;
870 end if;
871 OPEN cFNDResp(l_fn_responsibility_id);
872 FETCH cFNDResp INTO l_application_id;
873 CLOSE cFNDResp;
874 else
875 l_application_id := pApplicationId;
876 end if;
877
878 /*
879 begin
883 end;
880 select application_id into l_application_id
881 from fnd_responsibility
882 where responsibility_id=l_fn_responsibility_id;
884
885 if pRequestType <> 'R' then
886 l_form_func_name := 'BIS_SCHEDULE_CONFIRM_PAGE';
887 l_form_func_call := 'bisschcf.jsp';
888 end if;
889 */
890
891 begin
892 select function_id
893 into l_customize_id
894 from fnd_form_functions
895 where function_name = l_form_func_name;
896 exception
897 when no_data_found then
898 l_customize_id := null;
899 end;
900
901 if l_customize_id is null then
902 begin
903 select FND_FORM_FUNCTIONS_S.NEXTVAL into l_customize_id from dual;
904
905 --aleung, 5/14/01, for gsi1av envrionment, their fnd_form_functions_pkg.insert_row has more parameters
906
907 fnd_form_functions_pkg.INSERT_ROW(
908 X_ROWID => l_rowid,
909 X_FUNCTION_ID => l_customize_id,
910 X_WEB_HOST_NAME => null,
911 X_WEB_AGENT_NAME => null,
912 X_WEB_HTML_CALL => l_form_func_call,
913 X_WEB_ENCRYPT_PARAMETERS => null,
914 X_WEB_SECURED => null,
915 X_WEB_ICON => null,
916 X_OBJECT_ID => null,
917 X_REGION_APPLICATION_ID => null,
918 X_REGION_CODE => null,
919 X_FUNCTION_NAME => l_form_func_name,
920 X_APPLICATION_ID => l_application_id,
921 X_FORM_ID => null,
922 X_PARAMETERS => null,
923 X_TYPE => 'JSP',
924 X_USER_FUNCTION_NAME => 'BIS SCHEDULE',
925 X_DESCRIPTION => null,
926 X_CREATION_DATE => sysdate,
927 X_CREATED_BY => l_user_id,
928 X_LAST_UPDATE_DATE => sysdate,
929 X_LAST_UPDATED_BY => l_user_id,
930 X_LAST_UPDATE_LOGIN => l_user_id);
931
932 /*
933 fnd_form_functions_pkg.insert_row (l_rowid,
934 l_customize_id, null,null,
935 l_form_func_call,
936 null,null,null,l_form_func_name,
937 l_application_id,null,null,'JSP','BIS SCHEDULE',
938 null,sysdate,l_user_id,sysdate,l_user_id,l_user_id);
939 */
940
941 exception
942 when others then
943 null;
944 end;
945 end if;
946
947 /*
948 vScheduleURL := 'OracleApps.RF?F='||icx_call.encrypt2(l_application_id||'*'||l_fn_responsibility_id||'*'||icx_sec.g_security_group_id||'*'||l_customize_id||'**]',
949 icx_sec.getID(icx_sec.PV_SESSION_ID))
950 ||'&P='||icx_call.encrypt2('regionCode='||bis_pmv_util.encode(pRegionCode)
951 ||'&functionName='||bis_pmv_util.encode(pFunctionName)
952 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
953 ||'&requestType='||pRequestType
954 ||'&plugId='||pPlugId
955 ||'&reportTitle='||bis_pmv_util.encode(pReportTitle)
956 ||'&graphType='||pGraphType,icx_sec.getID(icx_sec.PV_SESSION_ID));
957 */
958
959 /*fnd_profile.get(name=>'APPS_SERVLET_AGENT',
960 val => vScheduleURL);
961 vScheduleURL := FND_WEB_CONFIG.trail_slash(vScheduleURL)||
962 'bissched.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
963 ||'&sessionid='||icx_call.encrypt3(icx_sec.getID(icx_sec.PV_SESSION_ID))
964 ||'&responsibilityId='||pResponsibilityId
965 ||'®ionCode='||bis_pmv_util.encode(pRegionCode)
966 ||'&functionName='||bis_pmv_util.encode(pFunctionName)
967 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
968 ||'&requestType='||pRequestType
969 ||'&plugId='||pPlugId
970 ||'&graphType='||pGraphType;*/
971
972 -- owa_util.redirect_url(vScheduleURL);
973
974 -- mdamle 11/01/2002 - Added encode
975 vParams := 'regionCode='|| pRegionCode
976 ||'&functionName='||pFunctionName
977 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
978 ||'&requestType='||pRequestType
979 ||'&plugId='||pPlugId
980 ||'&reportTitle='||pReportTitle
981 ||'&graphType='||pGraphType;
982
983 OracleApps.runFunction(c_function_id => l_customize_id
984 ,n_session_id => icx_sec.getID(icx_sec.PV_SESSION_ID)
985 ,c_parameters => vParams
986 ,p_resp_appl_id => l_application_id
987 ,p_responsibility_id => l_fn_responsibility_id
988 ,p_Security_group_id => icx_sec.g_Security_group_id
989 );
990
991 end scheduleReports;
992
993 Function Schedule_Reports_Link
994 (pRegionCode Varchar2,
995 pFunctionName Varchar2,
999 pDimension1Level Varchar2 default NULL,
996 pApplicationId Varchar2 default NULL,
997 pOrgLevel Varchar2,
998 pTimeLevel Varchar2,
1000 pDimension2Level Varchar2 default NULL,
1001 pDimension3Level Varchar2 default NULL,
1002 pDimension4Level Varchar2 default NULL,
1003 pDimension5Level Varchar2 default NULL,
1004 pDimension1 Varchar2 default NULL,
1005 pDimension2 Varchar2 default NULL,
1006 pDimension3 Varchar2 default NULL,
1007 pDimension4 Varchar2 default NULL,
1008 pDimension5 Varchar2 default NULL,
1009 pOrgLevelValue Varchar2 ,
1010 pTimeLevelValue Varchar2 ,
1011 pDimension1LevelValue Varchar2 default NULL,
1012 pDimension2LevelValue Varchar2 default NULL,
1013 pDimension3LevelValue Varchar2 default NULL,
1014 pDimension4LevelValue Varchar2 default NULL,
1015 pDimension5LevelValue Varchar2 default NULL,
1016 pPlanId Varchar2,
1017 pViewByLevel Varchar2)
1018
1019 Return VARCHAR2 is
1020
1021 vDimension1Level Varchar2(80) := pDimension1Level;
1022 vDimension2Level Varchar2(80) := pDimension2Level;
1023 vDimension3Level Varchar2(80) := pDimension3Level;
1024 vDimension4Level Varchar2(80) := pDimension4Level;
1025 vDimension5Level Varchar2(80) := pDimension5Level;
1026
1027 vDimension1LevelValue Varchar2(80) := pDimension1LevelValue ;
1028 vDimension2LevelValue Varchar2(80) := pDimension2LevelValue ;
1029 vDimension3LevelValue Varchar2(80) := pDimension3LevelValue ;
1030 vDimension4LevelValue Varchar2(80) := pDimension4LevelValue ;
1031 vDimension5LevelValue Varchar2(80) := pDimension5LevelValue ;
1032
1033
1034 vGeoLevel Varchar2(80);
1035 vGeoLevelValue Varchar2(80);
1036 vCountryCode Varchar2(80);
1037 vAreaCode Varchar2(80);
1038
1039 vTimeLevelValue Varchar2(80) := pTimeLevelValue;
1040
1041 v_schedule_report_URL VARCHAR2(32000);
1042
1043 l_ORG_LEVEL_ID NUMBER;
1044 l_TIME_LEVEL_ID NUMBER;
1045 l_DIMENSION1_LEVEL_ID NUMBER;
1046 l_DIMENSION2_LEVEL_ID NUMBER;
1047 l_DIMENSION3_LEVEL_ID NUMBER;
1048 l_DIMENSION4_LEVEL_ID NUMBER;
1049 l_DIMENSION5_LEVEL_ID NUMBER;
1050 l_VIEWBY_LEVEL_ID NUMBER;
1051
1052 Begin
1053
1054 --htp.print ('I Value : ' || pDimension1LevelValue || pDimension1);
1055
1056 if pDimension1 is not null then
1057 if pDimension1LevelValue = gvAll then
1058 vDimension1Level := 'TOTAL ' || pDimension1;
1059 vDimension1LevelValue := '-1';
1060 end if;
1061 else
1062 vDimension1Level := NULL;
1063 vDimension1LevelValue := NULL;
1064 end if;
1065
1066 if pDimension2 is not null then
1067 if pDimension2LevelValue = gvAll then
1068 vDimension2Level := 'TOTAL ' || pDimension2 ;
1069 vDimension2LevelValue := '-1';
1070 end if;
1071 else
1072 vDimension2Level := NULL;
1073 vDimension2LevelValue := NULL;
1074 end if;
1075
1076 if pDimension3 is not null then
1077 if pDimension3LevelValue = gvAll then
1078 vDimension3Level := 'TOTAL ' || pDimension3;
1079 vDimension3LevelValue := '-1';
1080 end if;
1081 else
1082 vDimension3Level := NULL;
1083 vDimension3LevelValue := NULL;
1084 end if;
1085
1086 if pDimension4 is not null then
1087 if pDimension4LevelValue = gvAll then
1088 vDimension4Level := 'TOTAL ' || pDimension4;
1089 vDimension4LevelValue := '-1';
1090 end if;
1091 else
1092 vDimension4Level := NULL;
1093 vDimension4LevelValue := NULL;
1094 end if;
1095
1096 if pDimension5 is not null then
1097 if pDimension5LevelValue = gvAll then
1098 vDimension5Level := 'TOTAL ' || pDimension5;
1099 vDimension5LevelValue := '-1';
1100 end if;
1101 else
1102 vDimension5Level := NULL;
1103 vDimension5LevelValue := NULL;
1104 end if;
1105
1106
1107 if (pDimension1 = 'GEOGRAPHY' or pDimension2 = 'GEOGRAPHY' or pDimension3 = 'GEOGRAPHY' or
1108 pDimension4 = 'GEOGRAPHY' or pDimension5 = 'GEOGRAPHY') then
1109
1110 if pDimension1 = 'GEOGRAPHY' then
1111 vGeoLevel := vDimension1Level;
1112 vGeoLevelValue := vDimension1LevelValue;
1113 elsif pDimension2 = 'GEOGRAPHY' then
1114 vGeoLevel := vDimension2Level;
1115 vGeoLevelValue := vDimension2LevelValue;
1116 elsif pDimension3 = 'GEOGRAPHY' then
1117 vGeoLevel := vDimension3Level;
1118 vGeoLevelValue := vDimension3LevelValue;
1119 elsif pDimension4 = 'GEOGRAPHY' then
1120 vGeoLevel := vDimension4Level;
1121 vGeoLevelValue := vDimension4LevelValue;
1122 elsif pDimension5 = 'GEOGRAPHY' then
1123 vGeoLevel := vDimension5Level;
1124 vGeoLevelValue := vDimension5LevelValue;
1125 end if;
1126
1127 if vGeoLevel = 'COUNTRY' then
1128
1129
1130 select bth.parent_territory_code
1134 and bth.child_territory_type = 'COUNTRY'
1131 into vAreaCode
1132 from bis_territory_hierarchies_v bth
1133 where bth.parent_territory_type = 'AREA'
1135 and bth.child_territory_code = vGeoLevelValue;
1136
1137 vGeoLevelValue := vAreaCode ||'+' || vGeoLevelValue;
1138
1139 elsif vGeoLevel = 'REGION' /* Region */ then
1140
1141 select bth.parent_territory_code
1142 into vCountryCode
1143 from bis_territory_hierarchies_v bth
1144 where bth.parent_territory_type = 'COUNTRY'
1145 and bth.child_territory_type = 'REGION'
1146 and bth.child_territory_code = vGeoLevelValue;
1147
1148 select bth.parent_territory_name
1149 into vAreaCode
1150 from bis_territory_hierarchies_v bth
1151 where bth.parent_territory_type = 'AREA'
1152 and bth.child_territory_type = 'COUNTRY'
1153 and bth.child_territory_code = vCountryCode;
1154
1155 vGeoLevelValue := vAreaCode ||'+' || vCountryCode || '+' || vGeoLevelValue;
1156 end if;
1157
1158 if pDimension1 = 'GEOGRAPHY' then
1159 vDimension1LevelValue := vGeoLevelValue;
1160 elsif pDimension2 = 'GEOGRAPHY' then
1161 vDimension2LevelValue := vGeoLevelValue;
1162 elsif pDimension3 = 'GEOGRAPHY' then
1163 vDimension3LevelValue := vGeoLevelValue;
1164 elsif pDimension4 = 'GEOGRAPHY' then
1165 vDimension4LevelValue := vGeoLevelValue;
1166 elsif pDimension5 = 'GEOGRAPHY' then
1167 vDimension5LevelValue := vGeoLevelValue;
1168 end if;
1169
1170 end if;
1171
1172 l_ORG_LEVEL_ID := Get_level_ID ( pOrgLevel );
1173 l_TIME_LEVEL_ID := Get_level_ID ( pTimeLevel );
1174 l_DIMENSION1_LEVEL_ID := Get_level_ID ( pDimension1Level );
1175 l_DIMENSION2_LEVEL_ID := Get_level_ID ( pDimension2Level );
1176 l_DIMENSION3_LEVEL_ID := Get_level_ID ( pDimension3Level );
1177 l_DIMENSION4_LEVEL_ID := Get_level_ID ( pDimension4Level );
1178 l_DIMENSION5_LEVEL_ID := Get_level_ID ( pDimension5Level );
1179 l_VIEWBY_LEVEL_ID := Get_level_ID ( pViewByLevel );
1180
1181 -----------------------------------------------------------
1182 /* comment out 'ScheduleReport'
1183
1184 BIS_PMF_ALERT_REG_PVT.BuildScheduleReportURL
1185 ( p_RegionCode => pRegionCode
1186 , p_FunctionName => pFunctionName
1187 , p_ApplicationId => pApplicationId
1188 , p_plan_id => pPlanId
1189 , p_parameter1levelId => l_ORG_LEVEL_ID
1190 , p_parameter1ValueId => pOrgLevelValue
1191 , p_parameter2levelId => l_TIME_LEVEL_ID
1192 , p_parameter2ValueId => pTimeLevelValue
1193 , p_parameter3levelId => l_DIMENSION1_LEVEL_ID
1194 , p_parameter3ValueId => pDimension1LevelValue
1195 , p_parameter4levelId => l_DIMENSION2_LEVEL_ID
1196 , p_parameter4ValueId => pDimension2LevelValue
1197 , p_parameter5levelId => l_DIMENSION3_LEVEL_ID
1198 , p_parameter5ValueId => pDimension3LevelValue
1199 , p_parameter6levelId => l_DIMENSION4_LEVEL_ID
1200 , p_parameter6ValueId => pDimension4LevelValue
1201 , p_parameter7levelId => l_DIMENSION5_LEVEL_ID
1202 , p_parameter7ValueId => pDimension5LevelValue
1203 , p_viewByLevelId => l_VIEWBY_LEVEL_ID
1204 -- , p_returnPageUrl => 'http://www.yahoo.com'
1205 , x_alert_url => v_schedule_report_URL
1206 );
1207
1208 --comment out ScheduleReports */
1209 ------------------------------------------------------
1210
1211 RETURN v_schedule_report_URL;
1212
1213 End Schedule_Reports_Link;
1214
1215 function getTargetParm(Display in varchar2,
1216 Measure in varchar2,
1217 PlanId in varchar2,
1218 Dim1Level in varchar2 default null,
1219 Dim2Level in varchar2 default null,
1220 Dim3Level in varchar2 default null,
1221 Dim4Level in varchar2 default null,
1222 Dim5Level in varchar2 default null,
1223 Dim6Level in varchar2 default null,
1224 Dim7Level in varchar2 default null,
1225 Dim1LevelValue in varchar2 default null,
1226 Dim2LevelValue in varchar2 default null,
1227 Dim3LevelValue in varchar2 default null,
1228 Dim4LevelValue in varchar2 default null,
1229 Dim5LevelValue in varchar2 default null,
1230 Dim6LevelValue in varchar2 default null,
1231 Dim7LevelValue in varchar2 default null) return varchar2 is
1232 vTargetParm varchar2(2000);
1233
1234 begin
1235 vTargetParm := Display||'TARGET&Measure='||bis_pmv_util.encode(Measure)
1236 ||'&PlanId='||bis_pmv_util.encode(PlanId)
1237 ||'&Dim1Level='||bis_pmv_util.encode(Dim1Level)
1238 ||'&Dim2Level='||bis_pmv_util.encode(Dim2Level)
1239 ||'&Dim3Level='||bis_pmv_util.encode(Dim3Level)
1240 ||'&Dim4Level='||bis_pmv_util.encode(Dim4Level)
1241 ||'&Dim5Level='||bis_pmv_util.encode(Dim5Level)
1245 ||'&Dim2LevelValue='||bis_pmv_util.encode(Dim2LevelValue)
1242 ||'&Dim6Level='||bis_pmv_util.encode(Dim6Level)
1243 ||'&Dim7Level='||bis_pmv_util.encode(Dim7Level)
1244 ||'&Dim1LevelValue='||bis_pmv_util.encode(Dim1LevelValue)
1246 ||'&Dim3LevelValue='||bis_pmv_util.encode(Dim3LevelValue)
1247 ||'&Dim4LevelValue='||bis_pmv_util.encode(Dim4LevelValue)
1248 ||'&Dim5LevelValue='||bis_pmv_util.encode(Dim5LevelValue)
1249 ||'&Dim6LevelValue='||bis_pmv_util.encode(Dim6LevelValue)
1250 ||'&Dim7LevelValue='||bis_pmv_util.encode(Dim7LevelValue);
1251 return vTargetParm;
1252 end getTargetParm;
1253
1254 procedure getTotalDimValue(pDimSource in varchar2,
1255 pDimension in varchar2 default null,
1256 pDimensionLevel in out NOCOPY varchar2,
1257 pDimensionLevelValue out NOCOPY varchar2) is
1258
1259 vTotalLevel varchar2(80);
1260 vTotalLevelValue varchar2(80) := '-1';
1261 --/*
1262 v_sql_stmnt VARCHAR2(2000);
1263 v_table varchar2(80);
1264 v_id_name VARCHAR2(80):='ID';
1265 v_value_name VARCHAR2(80):='VALUE';
1266 v_return_status VARCHAR2(2000);
1267 v_msg_count NUMBER;
1268 v_msg_data VARCHAR2(2000);
1269
1270 vsql varchar2(1000);
1271 type c1CurType is ref cursor;
1272 c1 c1CurType;
1273 --*/
1274 begin
1275 /*
1276 vTotalLevel := bis_utilities_pvt.Get_Total_DimLevel_Name(p_dim_short_name =>pDimension,
1277 p_DimLevelName =>pDimensionLevel);
1278 */
1279 vTotalLevel := getTotalDimLevelName(pDimShortName => pDimension,
1280 pSource => pDimSource);
1281 /*
1282 if pDimSource = 'EDW' then
1283 vTotalLevelValue := '1';
1284 end if;
1285 */
1286 --/*
1287 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
1288 p_DimLevelShortName => vTotalLevel
1289 ,p_bis_source => pDimSource
1290 ,x_Select_String => v_sql_stmnt
1291 ,x_table_name=> v_table
1292 ,x_id_name=> v_id_name
1293 ,x_value_name=> v_value_name
1294 ,x_return_status=> v_return_status
1295 ,x_msg_count=> v_msg_count
1296 ,x_msg_data=> v_msg_data
1297 );
1298
1299 if v_return_status = FND_API.G_RET_STS_ERROR then
1300 for i in 1..v_msg_count loop
1301 htp.print(fnd_msg_pub.get(p_msg_index=>i, p_encoded=>FND_API.G_FALSE));
1302 htp.br;
1303 end loop;
1304 end if;
1305
1306 vSql := 'select '||v_id_name||' from '||v_table;
1307
1308 begin
1309 open c1 for vSql;
1310 loop
1311 exit when c1%notfound;
1312 fetch c1 into vTotalLevelValue;
1313 end loop;
1314 exception
1315 when others then
1316 null;
1317 end;
1318 --*/
1319
1320 pDimensionLevel := vTotalLevel;
1321 pDimensionLevelValue := vTotalLevelValue;
1322
1323 end getTotalDimValue;
1324
1325 Function getTotalDimLevelName(pDimShortName IN VARCHAR2
1326 ,pSource IN VARCHAR2)
1327 RETURN VARCHAR2
1328 IS
1329 CURSOR c_dims IS
1330 SELECT dimension_id
1331 FROM bis_dimensions
1332 WHERE short_name = pDimShortName;
1333
1334 CURSOR c_dimlvls(p_dim_id IN NUMBER, p_search_string IN VARCHAR2) IS
1335 SELECT short_name
1336 FROM bis_levels
1337 where short_name like p_Search_string AND
1338 dimension_id= p_dim_id;
1339 l_dim_id NUMBER;
1340 l_search_string VARCHAR2(32000);
1341 l_total_shortname VARCHAR2(32000);
1342 BEGIN
1343 OPEN c_dims;
1344 FETCH c_dims INTO l_dim_id;
1345 CLOSE c_dims;
1346 IF (pSource = 'EDW') THEN
1347 l_search_string := '%_A';
1348 ELSE
1349 l_search_string := 'TOTAL%';
1350 END IF;
1351 OPEN c_dimlvls(l_dim_id, l_search_string);
1352 FETCH c_dimlvls INTO l_total_shortname ;
1353 CLOSE c_dimlvls;
1354 RETURN l_total_shortname;
1355 END;
1356
1357 end bisviewer_pmf;