1 PACKAGE BODY BIS_PMV_PMF_PVT as
2 /* $Header: BISVPMPB.pls 120.2 2005/09/23 03:58:26 msaran noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.31=120.2):~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: BIS_PMV_PMF_PVT
7 -- --
8 -- DESCRIPTION: Target related APIs for PMV
9 -- --
10 -- MODIFICATIONS --
11 -- Date User Modification
12 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
13 -- list bug number, if fixing a bug. --
14 -- --
15 -- 10/17/00 amkulkar Initial creation --
16 -- 02/04/03 nkishore BugFix 2762795 --
17 -- 4/16/03 nkishore Added pRespId to get_notify_rpt_url bug 2833251 --
18 -- 6/27/03 rcmuthuk Added p_UserId to get_notify_rpt_url bug 2810397--
19 -- 12/19/03 nkishore BugFix 3280466 for SONAR --
20 -- 1/28/2004 nkishore BugFix 3075441 --
21 -- 5/20/2004 ksadagop BugFix 3635714 --
22 -- 5/26/2004 jprabhud BugFix 3649405 for SONAR --
23 ----------------------------------------------------------------------------
24 FUNCTION GET_TARGET
25 (pSource IN VARCHAR2
26 ,pSessionId IN VARCHAR2
27 ,pRegionCode IN VARCHAR2
28 ,pFunctionName IN VARCHAR2
29 ,pMeasureShortName IN VARCHAR2 DEFAULT NULL
30 ,pPlanId IN VARCHAR2 DEFAULT NULL
31 ,pDimension1 IN VARCHAR2 DEFAULT NULL
32 ,pDim1Level IN VARCHAR2 DEFAULT NULL
33 ,pDim1LevelValue IN VARCHAR2 DEFAULT NULL
34 ,pDimension2 IN VARCHAR2 DEFAULT NULL
35 ,pDim2Level IN VARCHAR2 DEFAULT NULL
36 ,pDim2LevelValue IN VARCHAR2 DEFAULT NULL
37 ,pDimension3 IN VARCHAR2 DEFAULT NULL
38 ,pDim3Level IN VARCHAR2 DEFAULT NULL
39 ,pDim3LevelValue IN VARCHAR2 DEFAULT NULL
40 ,pDimension4 IN VARCHAR2 DEFAULT NULL
41 ,pDim4Level IN VARCHAR2 DEFAULT NULL
42 ,pDim4LevelValue IN VARCHAR2 DEFAULT NULL
43 ,pDimension5 IN VARCHAR2 DEFAULT NULL
44 ,pDim5Level IN VARCHAR2 DEFAULT NULL
45 ,pDim5LevelValue IN VARCHAR2 DEFAULT NULL
46 ,pDimension6 IN VARCHAR2 DEFAULT NULL
47 ,pDim6Level IN VARCHAR2 DEFAULT NULL
48 ,pDim6LevelValue IN VARCHAR2 DEFAULT NULL
49 ,pDimension7 IN VARCHAR2 DEFAULT NULL
50 ,pDim7Level IN VARCHAR2 DEFAULT NULL
51 ,pDim7LevelValue IN VARCHAR2 DEFAULT NULL
52 )
53 RETURN VARCHAR2
54 IS
55 vTarget VARCHAR2(1000);
56 l_return_Status VARCHAR2(32000);
57 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
58 l_target_rec BIS_TARGET_PUB.TARGET_REC_TYPE;
59 l_target_level_rec_p BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
60 l_target_rec_p BIS_TARGET_PUB.TARGET_REC_TYPE;
61 l_error_tbl BIS_UTILITIES_PUB.ERROR_TBL_TYPE;
62 v_range1_low varchar2(200);
63 v_range1_high varchar2(200);
64 l_target VARCHAR2(32000);
65 l_target_url VARCHAR2(32000);
66 l_dim1_level_value_id varchar2(32000);
67 l_dim2_level_value_id varchar2(32000);
68 l_dim3_level_value_id varchar2(32000);
69 l_dim4_level_value_id varchar2(32000);
70 l_dim5_level_value_id varchar2(32000);
71 l_dim6_level_value_id varchar2(32000);
72 l_dim7_level_value_id varchar2(32000);
73 l_dimension1_level_short_name varchar2(80);
74 l_dimension2_level_short_name varchar2(80);
75 l_dimension3_level_short_name varchar2(80);
76 l_dimension4_level_short_name varchar2(80);
77 l_dimension5_level_short_name varchar2(80);
78 l_dimension6_level_short_name varchar2(80);
79 l_dimension7_level_short_name varchar2(80);
80 BEGIN
81 l_target_level_rec.measure_short_name := pMeasureShortName;
82 l_dimension1_level_short_name := pDim1Level;
83 l_dimension2_level_short_name := pDim2Level;
84 l_dimension3_level_short_name := pDim3Level;
85 l_dimension4_level_short_name := pDim4Level;
86 l_dimension5_level_short_name := pDim5Level;
87 l_dimension6_level_short_name := pDim6Level;
88 l_dimension7_level_short_name := pDim7Level;
89 l_target_rec.plan_id := pPlanId;
90 if (upper(pDim1LevelValue) = 'ALL' OR
91 pDim1LevelValue = '' OR
92 (pDim1Level is not null and pDim1LevelValue is null))
93 then
94 l_dimension1_level_short_name := getTotalDimLevelName(pDimension1,pSource);
95 l_dim1_level_value_id := getTotalDimValue(pSource,pDimension1
96 ,l_dimension1_level_short_name);
97 else
98 l_dim1_level_value_id := pDim1LevelValue;
99 end if;
100 if (upper(pDim2LevelValue) = 'ALL' OR
101 pDim2LevelValue = '' OR
102 (pDim2Level is not null and pDim2LevelValue is null))
103 then
104 l_dimension2_level_short_name := getTotalDimLevelName(pDimension2,pSource);
105 l_dim2_level_value_id := getTotalDimValue(pSource,pDimension2
106 ,l_dimension2_level_short_name);
107 else
108 l_dim2_level_value_id := pDim2LevelValue;
109 end if;
110 if (upper(pDim3LevelValue) = 'ALL' OR
111 pDim3LevelValue = '' OR
112 (pDim3Level is not null and pDim3LevelValue is null))
113 then
114 l_dimension3_level_short_name := getTotalDimLevelName(pDimension3,pSource);
115 l_dim3_level_value_id := getTotalDimValue(pSource,pDimension3
116 ,l_dimension3_level_short_name);
117 else
118 l_dim3_level_value_id := pDim3LevelValue;
119 end if;
120 if (upper(pDim4LevelValue) = 'ALL' OR
121 pDim4LevelValue = '' OR
122 (pDim4Level is not null and pDim4LevelValue is null))
123 then
124 l_dimension4_level_short_name := getTotalDimLevelName(pDimension4,pSource);
125 l_dim4_level_value_id := getTotalDimValue(pSource,pDimension4
126 ,l_dimension4_level_short_name);
127 else
128 l_dim4_level_value_id := pDim4LevelValue;
129 end if;
130 if (upper(pDim5LevelValue) = 'ALL' OR
131 pDim5LevelValue = '' OR
132 (pDim5Level is not null and pDim5LevelValue is null))
133 then
134 l_dimension5_level_short_name := getTotalDimLevelName(pDimension5,pSource);
135 l_dim5_level_value_id := getTotalDimValue(pSource,pDimension5
136 ,l_dimension5_level_short_name);
137 else
138 l_dim5_level_value_id := pDim5LevelValue;
139 end if;
140 if (upper(pDim6LevelValue) = 'ALL' OR
141 pDim6LevelValue = '' OR
142 (pDim6Level is not null and pDim6LevelValue is null))
143 then
144 l_dimension6_level_short_name := getTotalDimLevelName(pDimension6,pSource);
145 l_dim6_level_value_id := getTotalDimValue(pSource,pDimension6
146 ,l_dimension6_level_short_name);
147 else
148 l_dim6_level_value_id := pDim6LevelValue;
149 end if;
150 if (upper(pDim7LevelValue) = 'ALL' OR
151 pDim7LevelValue = '' OR
152 (pDim7Level is not null and pDim7LevelValue is null))
153 then
154 l_dimension7_level_short_name := getTotalDimLevelName(pDimension7,pSource);
155 l_dim7_level_value_id := getTotalDimValue(pSource,pDimension7
156 ,l_dimension7_level_short_name);
157 else
158 l_dim7_level_value_id := pDim7LevelValue;
159 end if;
160
161 l_target_rec.dim1_level_Value_id := l_dim1_level_Value_id;
162 l_target_rec.dim2_level_Value_id := l_dim2_level_Value_id;
163 l_target_rec.dim3_level_Value_id := l_dim3_level_Value_id;
164 l_target_rec.dim4_level_Value_id := l_dim4_level_Value_id;
165 l_target_rec.dim5_level_Value_id := l_dim5_level_Value_id;
166 l_target_rec.dim6_level_Value_id := l_dim6_level_Value_id;
167 l_target_rec.dim7_level_Value_id := l_dim7_level_Value_id;
168 l_Target_level_Rec.dimension1_level_short_name := l_dimension1_level_short_name;
169 l_Target_level_Rec.dimension2_level_short_name := l_dimension2_level_short_name;
170 l_Target_level_Rec.dimension3_level_short_name := l_dimension3_level_short_name;
171 l_Target_level_Rec.dimension4_level_short_name := l_dimension4_level_short_name;
172 l_Target_level_Rec.dimension5_level_short_name := l_dimension5_level_short_name;
173 l_Target_level_Rec.dimension6_level_short_name := l_dimension6_level_short_name;
174 l_Target_level_Rec.dimension7_level_short_name := l_dimension7_level_short_name;
175 l_target_level_rec_p := l_target_level_rec;
176 l_target_rec_p := l_target_rec;
177 --BugFix 2762795
178 BIS_TARGET_PUB.RETRIEVE_TARGET_FROM_SHNMS
179 (p_api_version => 1.0
180 ,p_target_level_rec => l_target_level_rec_p
181 ,p_Target_Rec => l_target_rec_p
182 ,x_Target_Level_Rec => l_target_level_rec
183 ,x_Target_Rec => l_target_rec
184 ,x_return_status => l_return_status
185 ,x_error_Tbl => l_error_tbl
186 );
187 IF (l_return_Status = FND_API.G_RET_STS_ERROR) THEN
188 vTarget := 'NONE';
189 v_range1_high := 'NONE';
190 v_range1_low := 'NONE';
191 else
192 vTarget := l_target_rec.target;
193 v_range1_low := l_target_rec.range1_low;
194 v_range1_high := l_target_rec.range1_high;
195 if (l_target_rec.target_id is null) or (l_target_rec.target_id = FND_API.G_MISS_NUM) then
196 vTarget := 'NONE';
197 v_range1_low := 'NONE';
198 v_Range1_high := 'NONE';
199 else
200 if (v_range1_low = FND_API.G_MISS_NUM) or (v_range1_low is null) then
201 v_range1_low := 'NONE';
202 end if;
203 if (v_range1_high = FND_API.G_MISS_NUM) or (v_range1_high is null) then
204 v_range1_high := 'NONE';
205 end if;
206 end if;
207 END IF;
208
209 l_Target_url := FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.WEB_SERVER)||
210 'OA_HTML/bistared.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
211 ||'&sessionid='||pSessionId
212 ||'&RegionCode='||bis_pmv_util.encode(pRegionCode)
213 ||'&FunctionName='||bis_pmv_util.encode(pFunctionName)
214 ||'&SortInfo='||bis_pmv_util.encode('Sortcolumn2Asc')
215 ||'&Measure='||pMeasureShortName||'&PlanId='||pPlanId
216 ||'&Dim1Level='|| BIS_PMV_UTIL.encode(l_dimension1_level_short_name)
217 ||'&Dim2Level='|| BIS_PMV_UTIL.encode(l_dimension2_level_short_name)
218 ||'&Dim3Level='|| BIS_PMV_UTIL.encode(l_Dimension3_level_short_name)
219 ||'&Dim4Level='|| BIS_PMV_UTIL.encode(l_Dimension4_level_short_name)
220 ||'&Dim5Level='|| BIS_PMV_UTIL.encode(l_dimension5_level_short_name)
221 ||'&Dim6Level='|| BIS_PMV_UTIL.encode(l_Dimension6_level_short_name)
222 ||'&Dim7Level='|| BIS_PMV_UTIL.encode(l_dimension7_level_short_name)
223 ||'&Dim1LevelValue='||BIS_PMV_UTIL.encode(l_dim1_level_Value_id)
224 ||'&Dim2LevelValue='||BIS_PMV_UTIL.encode(l_dim2_level_Value_id)
225 ||'&Dim3LevelValue='||BIS_PMV_UTIL.encode(l_dim3_level_Value_id)
226 ||'&Dim4LevelValue='||BIS_PMV_UTIL.encode(l_dim4_level_value_id)
227 ||'&Dim5LevelValue='||BIS_PMV_UTIL.encode(l_dim5_level_value_id)
228 ||'&Dim6LevelValue='||BIS_PMV_UTIL.encode(l_dim6_level_value_id)
229 ||'&Dim7LevelValue='||BIS_PMV_UTIL.encode(l_dim7_level_value_id);
230 l_target := l_target_url||'*'||vTarget||'**'||v_range1_low||'***'||v_range1_high;
231 return l_target;
232 --return 'T_' || vTarget || '_' || v_range1_low || '_' || v_range1_high;
233 END GET_TARGET;
234 FUNCTION getTotalDimValue
235 (pDimSource IN VARCHAR2,
236 pDimension IN VARCHAR2 DEFAULT NULL,
237 pDimensionLevel IN VARCHAR2
238 )
239 RETURN VARCHAR2
240 IS
241 v_sql_stmnt VARCHAR2(2000);
242 v_table varchar2(80);
243 v_id_name VARCHAR2(80):='ID';
244 v_value_name VARCHAR2(80):='VALUE';
245 v_return_status VARCHAR2(2000);
246 v_msg_count NUMBER;
247 v_msg_data VARCHAR2(2000);
248 vsql varchar2(1000);
249 type c1CurType is ref cursor;
250 c1 c1CurType;
251 vtotallevelvalue varchar2(32000);
252 BEGIN
253 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
254 p_DimLevelShortName => pDimensionLevel
255 ,p_bis_source => pDimSource
256 ,x_Select_String => v_sql_stmnt
257 ,x_table_name=> v_table
258 ,x_id_name=> v_id_name
259 ,x_value_name=> v_value_name
260 ,x_return_status=> v_return_status
261 ,x_msg_count=> v_msg_count
262 ,x_msg_data=> v_msg_data
263 );
264
265 /*if v_return_status = FND_API.G_RET_STS_ERROR then
266 for i in 1..v_msg_count loop
267 htp.print(fnd_msg_pub.get(p_msg_index=>i, p_encoded=>FND_API.G_FALSE));
268 htp.br;
269 end loop;
270 end if;*/
271
272 vSql := 'select '||v_id_name||' from '||v_table;
273
274 begin
275 open c1 for vSql;
276 loop
277 exit when c1%notfound;
278 fetch c1 into vTotalLevelValue;
279 end loop;
280 exception
281 when others then
282 null;
283 end;
284 RETURN (VTotalLevelValue);
285 END;
286 Function getTotalDimLevelName
287 (pDimShortName IN VARCHAR2
288 ,pSource IN VARCHAR2)
289 RETURN VARCHAR2
290 IS
291 CURSOR c_dims IS
292 SELECT dimension_id
293 FROM bis_dimensions
294 WHERE short_name = pDimShortName;
295
296 CURSOR c_dimlvls(p_dim_id IN NUMBER, p_search_string IN VARCHAR2) IS
297 SELECT short_name
298 FROM bis_levels
299 where short_name like p_Search_string AND
300 dimension_id= p_dim_id;
301 l_dim_id NUMBER;
302 l_search_string VARCHAR2(32000);
303 l_total_shortname VARCHAR2(32000);
304 BEGIN
305 OPEN c_dims;
306 FETCH c_dims INTO l_dim_id;
307 CLOSE c_dims;
308 IF (pSource = 'EDW') THEN
309 l_search_string := '%_A';
310 ELSE
311 l_search_string := 'TOTAL%';
312 END IF;
313 OPEN c_dimlvls(l_dim_id, l_search_string);
314 FETCH c_dimlvls INTO l_total_shortname ;
315 CLOSE c_dimlvls;
316 RETURN l_total_shortname;
317 END;
318 PROCEDURE TOLERANCE_TEST
319 (p_target_value IN VARCHAR2
320 ,p_actual_value IN VARCHAR2
321 ,p_range1_high IN VARCHAR2
322 ,p_range1_low IN VARCHAR2
323 ,x_tolerance OUT NOCOPY VARCHAR2
324 )
325 IS
326 pToleranceFlag VARCHAR2(32000);
327 v_target_rec BIS_TARGET_PUB.Target_Rec_Type;
328 v_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
329 v_comparison_result varchar2(1000);
330 BEGIN
331 pToleranceFlag :='ON';
332 v_target_rec.Target := p_target_value;
333 v_target_rec.Range1_high := p_range1_high ;
334 v_target_rec.Range1_low := p_range1_low;
335 v_actual_rec.Actual := p_actual_value;
336
337 BIS_GENERIC_PLANNER_PVT.Compare_Values
338 ( p_target_rec => v_target_rec
339 , p_actual_rec => v_actual_rec
340 , x_comparison_result => v_comparison_result
341 );
342
343 if v_comparison_result <> BIS_GENERIC_PLANNER_PVT.G_COMP_RESULT_NORMAL then
344 pToleranceFlag := 'OFF';
345 end if;
346 x_tolerance := pToleranceFlag;
347 END;
348
349 --BugFix 3075441, add p_NlsLangCode
350 FUNCTION GET_NOTIFY_RPT_URL(
351 p_measure_id IN VARCHAR2
352 ,p_region_code in varchar2 default null
353 ,p_function_name in varchar2 default null
354 ,p_bplan_name IN VARCHAR2 default null
355 ,p_viewby_level_short_name IN VARCHAR2 default null
356 ,p_Parm1Level_short_name IN VARCHAR2 default null
357 ,p_Parm1Value_name IN VARCHAR2 default null
358 ,p_Parm2Level_short_name IN VARCHAR2 default null
359 ,p_Parm2Value_name IN VARCHAR2 default null
360 ,p_Parm3Level_short_name IN VARCHAR2 default null
361 ,p_Parm3Value_name IN VARCHAR2 default null
362 ,p_Parm4Level_short_name IN VARCHAR2 default null
363 ,p_Parm4Value_name IN VARCHAR2 default null
364 ,p_Parm5Level_short_name IN VARCHAR2 default null
365 ,p_Parm5Value_name IN VARCHAR2 default null
366 ,p_Parm6Level_short_name IN VARCHAR2 default null
367 ,p_Parm6Value_name IN VARCHAR2 default null
368 ,p_Parm7Level_short_name IN VARCHAR2 default null
369 ,p_Parm7Value_name IN VARCHAR2 default null
370 ,p_Parm8Level_short_name IN VARCHAR2 default null
371 ,p_Parm8Value_name IN VARCHAR2 default null
372 ,p_Parm9Level_short_name IN VARCHAR2 default null
373 ,p_Parm9Value_name IN VARCHAR2 default null
374 ,p_Parm10Level_short_name IN VARCHAR2 default null
375 ,p_Parm10Value_name IN VARCHAR2 default null
376 ,p_Parm11Level_short_name IN VARCHAR2 default null
377 ,p_Parm11Value_name IN VARCHAR2 default null
378 ,p_Parm12Level_short_name IN VARCHAR2 default null
379 ,p_Parm12Value_name IN VARCHAR2 default null
380 ,p_Parm13Level_short_name IN VARCHAR2 default null
381 ,p_Parm13Value_name IN VARCHAR2 default null
382 ,p_TimeParmLevel_short_name in varchar2 default null
383 ,p_TimeFromParmValue_name in varchar2 default null
384 ,p_TimeToParmValue_name in varchar2 default null
385 ,p_resp_id in varchar2 default null
386 ,p_UserId IN VARCHAR2 default null
387 ,p_NlsLangCode IN VARCHAR2 default null)
388 RETURN VARCHAR2
389 IS
390 --jprabhud defaulted to NULL enhancement#2184054
391 vURL varchar2(2000) := NULL;
392 vFileId number := 0;
393 vSessionId varchar2(32000);
394 --vUserId varchar2(20) := 'Notification'; --Bug Fix 2165959
395 --vRespId varchar2(10) := 'NULL';
396 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
397 --vUserId varchar2(20) := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
398 --vRespId varchar2(10) := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
399 vUserId varchar2(20);
400 vRespId varchar2(10);
401 vNotifId varchar2 (32000);
402
403 vParameter1 varchar2(100);
404 vParameter2 varchar2(100);
405 vParameter3 varchar2(100);
406 vParameter4 varchar2(100);
407 vParameter5 varchar2(100);
408 vParameter6 varchar2(100);
409 vParameter7 varchar2(100);
410 vParameter8 varchar2(100);
411 vParameter9 varchar2(100);
412 vParameter10 varchar2(100);
413 vParameter11 varchar2(100);
414 vParameter12 varchar2(100);
415 vParameter13 varchar2(100);
416 vParameter14 varchar2(100);
417 vParameter15 varchar2(100);
418
419 vParm14Value_name varchar2(100);
420 vParm15Value_name varchar2(100);
421
422 vTimeParameter VARCHAR2(100);
423 vTimeFromParameter VARCHAR2(100);
424 vTimeToParameter VARCHAR2(100);
425
426 vReturnStatus varchar2(2000);
427 vMsgData varchar2(2000);
428 vMsgCount number;
429
430 vReportURL varchar2(2000);
431 vHTMLPieces utl_http.html_pieces;
432
433
434 --jprabhud added for enhancement#2184054
435 l_err varchar2(2000);
436 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
437
438 l_nested_region_code varchar2(100);
439
440 lAsOfDateValue varchar2(100);
441
442
443 ---jprabhud added Cursor, also added VIEWBY PARAMETER in where clause for enhancement#2184054
444
445 CURSOR c_dimlvl(p_search_string IN VARCHAR2, p_view_by_level IN VARCHAR2 ) IS
446 select attribute2
447 FROM ak_region_items ak
448 where ak.region_code = p_region_code
449 AND ak.attribute1 in (G_DIMENSION_LEVEL, G_DIM_LEVEL_SINGLE_VALUE,G_VIEWBY_PARAMETER)
450 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1)
451 = nvl(p_view_by_level, substr(ak.attribute2, instr(ak.attribute2, '+')+1))
452 AND substr(ak.attribute2, 1,instr(ak.attribute2, '+')-1)
453 like nvl(p_search_string, substr(ak.attribute2, 1,instr(ak.attribute2, '+')-1))
454 order by display_sequence;
455 --BugFix 3280466
456 CURSOR get_nested_region IS
457 select nested_region_code from ak_region_items
458 where region_code = p_region_code
459 and nested_region_code is not null;
460
461
462 BEGIN
463
464 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
465 /*
466 --BugFix 2833251 Added p_resp_id
467 if (p_resp_id is not null) then
468 vRespId := p_resp_id;
469 end if;
470 -- rcmuthuk BugFix:2810397 added p_UserId
471 if (p_UserId is not null) then
472 vUserId := p_UserId;
473 end if;
474 */
475
476 --BugFix 3280466
477 open get_nested_region;
478 fetch get_nested_region into l_nested_region_code;
479 close get_nested_region;
480
481
482 if p_region_code is not null and p_function_name is not null then
483 --jprabhud added BEGIN enhancement#2184054
484 BEGIN
485 IF ( p_parm1Level_short_name IS NOT NULL) THEN
486 begin
487 --BugFix 3280466 Changed all the sqls to also check from Nested Region
488 select DISTINCT nvl(attribute2,attribute_code)
489 into vParameter1
490 from ak_region_items_vl AK
491 where ak.region_code in ( p_region_code, l_nested_region_code)
492 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm1Level_short_name
493 AND ak.node_query_flag = 'Y';
494 IF ( p_parm1Level_short_name ='AS_OF_DATE') THEN
495 lAsOfDateValue := p_Parm1Value_name;
496 END IF;
497 exception
498 --jprabhud added l_return_status for enhancement#2184054
499 when NO_DATA_FOUND then
500 l_return_status := FND_API.G_RET_STS_ERROR;
501 htp.print(p_parm1Level_short_name ||' does not match the level short name defined in AK .');
502 when others then
503 l_return_status := FND_API.G_RET_STS_ERROR;
504 htp.print('cannot obtain correct info for level short name: '||p_parm1Level_short_name);
505 end;
506 END IF;
507
508 IF ( p_parm2Level_short_name IS NOT NULL) THEN
509 begin
510
511 select DISTINCT nvl(attribute2,attribute_code)
512 into vParameter2
513 from ak_region_items_vl AK
514 where ak.region_code in ( p_region_code, l_nested_region_code)
515 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm2Level_short_name
516 AND ak.node_query_flag = 'Y';
517 IF ( p_parm2Level_short_name ='AS_OF_DATE') THEN
518 lAsOfDateValue := p_Parm2Value_name;
519 END IF;
520 exception
521 --jprabhud added l_return_status for enhancement#2184054
522 when NO_DATA_FOUND then
523 l_return_status := FND_API.G_RET_STS_ERROR;
524 htp.print(p_parm2Level_short_name ||' does not match the level short name defined in AK .');
525 when others then
526 l_return_status := FND_API.G_RET_STS_ERROR;
527 htp.print('cannot obtain correct info for level short name: '||p_parm2Level_short_name);
528 end;
529
530 END IF;
531
532 IF ( p_parm3Level_short_name IS NOT NULL) THEN
533 begin
534 select DISTINCT nvl(attribute2,attribute_code)
535 into vParameter3
536 from ak_region_items_vl AK
537 where ak.region_code in ( p_region_code, l_nested_region_code)
538 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm3Level_short_name
539 AND ak.node_query_flag = 'Y';
540 IF ( p_parm3Level_short_name ='AS_OF_DATE') THEN
541 lAsOfDateValue := p_Parm3Value_name;
542 END IF;
543
544 exception
545 --jprabhud added l_return_status for enhancement#2184054
546 when NO_DATA_FOUND then
547 l_return_status := FND_API.G_RET_STS_ERROR;
548 htp.print(p_parm3Level_short_name ||' does not match the level short name defined in AK .');
549 when others then
550 l_return_status := FND_API.G_RET_STS_ERROR;
551 htp.print('cannot obtain correct info for level short name: '||p_parm3Level_short_name);
552 end;
553
554 END IF;
555
556 IF ( p_parm4Level_short_name IS NOT NULL) THEN
557 begin
558 select DISTINCT nvl(attribute2,attribute_code)
559 into vParameter4
560 from ak_region_items_vl AK
561 where ak.region_code in ( p_region_code, l_nested_region_code)
562 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm4Level_short_name
563 AND ak.node_query_flag = 'Y';
564 IF ( p_parm4Level_short_name ='AS_OF_DATE') THEN
565 lAsOfDateValue := p_Parm4Value_name;
566 END IF;
567 exception
568 --jprabhud added l_return_status for enhancement#2184054
569 when NO_DATA_FOUND then
570 l_return_status := FND_API.G_RET_STS_ERROR;
571 htp.print(p_parm4Level_short_name ||' does not match the level short name defined in AK .');
572 when others then
573 l_return_status := FND_API.G_RET_STS_ERROR;
574 htp.print('cannot obtain correct info for level short name: '||p_parm4Level_short_name);
575 end;
576
577 END IF;
578
579 IF ( p_parm5Level_short_name IS NOT NULL) THEN
580 begin
581 select DISTINCT nvl(attribute2,attribute_code)
582 into vParameter5
583 from ak_region_items_vl AK
584 where ak.region_code in ( p_region_code, l_nested_region_code)
585 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm5Level_short_name
586 AND ak.node_query_flag = 'Y';
587 IF ( p_parm5Level_short_name ='AS_OF_DATE') THEN
588 lAsOfDateValue := p_Parm5Value_name;
589 END IF;
590 exception
591 --jprabhud added l_return_status for enhancement#2184054
592 when NO_DATA_FOUND then
593 l_return_status := FND_API.G_RET_STS_ERROR;
594 htp.print(p_parm5Level_short_name ||' does not match the level short name defined in AK .');
595 when others then
596 l_return_status := FND_API.G_RET_STS_ERROR;
597 htp.print('cannot obtain correct info for level short name: '||p_parm5Level_short_name);
598 end;
599
600 END IF;
601
602 IF ( p_parm6Level_short_name IS NOT NULL) THEN
603 begin
604 select DISTINCT nvl(attribute2,attribute_code)
605 into vParameter6
606 from ak_region_items_vl AK
607 where ak.region_code in ( p_region_code, l_nested_region_code)
608 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm6Level_short_name
609 AND ak.node_query_flag = 'Y';
610 IF ( p_parm6Level_short_name ='AS_OF_DATE') THEN
611 lAsOfDateValue := p_Parm6Value_name;
612 END IF;
613 exception
614 --jprabhud added l_return_status for enhancement#2184054
615 when NO_DATA_FOUND then
616 l_return_status := FND_API.G_RET_STS_ERROR;
617 htp.print(p_parm6Level_short_name ||' does not match the level short name defined in AK .');
618 when others then
619 l_return_status := FND_API.G_RET_STS_ERROR;
620 htp.print('cannot obtain correct info for level short name: '||p_parm6Level_short_name);
621 end;
622
623 END IF;
624
625 IF ( p_parm7Level_short_name IS NOT NULL) THEN
626 begin
627 select DISTINCT nvl(attribute2,attribute_code)
628 into vParameter7
629 from ak_region_items_vl AK
630 where ak.region_code = p_region_code
631 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_parm7Level_short_name
632 AND ak.node_query_flag = 'Y';
633 exception
634 --jprabhud added l_return_status for enhancement#2184054
635 when NO_DATA_FOUND then
636 l_return_status := FND_API.G_RET_STS_ERROR;
637 htp.print(p_parm7Level_short_name ||' does not match the level short name defined in AK .');
638 when others then
639 l_return_status := FND_API.G_RET_STS_ERROR;
640 htp.print('cannot obtain correct info for level short name: '||p_parm7Level_short_name);
641 end;
642
643 END IF;
644
645 IF ( p_parm8Level_short_name IS NOT NULL) THEN
646 begin
647 select DISTINCT nvl(attribute2,attribute_code)
648 into vParameter8
649 from ak_region_items_vl AK
650 where ak.region_code in ( p_region_code, l_nested_region_code)
651 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm8Level_short_name
652 AND ak.node_query_flag = 'Y';
653 IF ( p_parm8Level_short_name ='AS_OF_DATE') THEN
654 lAsOfDateValue := p_Parm8Value_name;
655 END IF;
656 exception
657 --jprabhud added l_return_status for enhancement#2184054
658 when NO_DATA_FOUND then
659 l_return_status := FND_API.G_RET_STS_ERROR;
660 htp.print(p_parm8Level_short_name ||' does not match the level short name defined in AK .');
661 when others then
662 l_return_status := FND_API.G_RET_STS_ERROR;
663 htp.print('cannot obtain correct info for level short name: '||p_parm8Level_short_name);
664 end;
665
666 END IF;
667
668 IF ( p_parm9Level_short_name IS NOT NULL) THEN
669 begin
670 select DISTINCT nvl(attribute2,attribute_code)
671 into vParameter9
672 from ak_region_items_vl AK
673 where ak.region_code in ( p_region_code, l_nested_region_code)
674 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm9Level_short_name
675 AND ak.node_query_flag = 'Y';
676 IF ( p_parm9Level_short_name ='AS_OF_DATE') THEN
677 lAsOfDateValue := p_Parm9Value_name;
678 END IF;
679 exception
680 --jprabhud added l_return_status for enhancement#2184054
681 when NO_DATA_FOUND then
682 l_return_status := FND_API.G_RET_STS_ERROR;
683 htp.print(p_parm9Level_short_name ||' does not match the level short name defined in AK .');
684 when others then
685 l_return_status := FND_API.G_RET_STS_ERROR;
686 htp.print('cannot obtain correct info for level short name: '||p_parm9Level_short_name);
687 end;
688
689 END IF;
690
691 IF ( p_parm10Level_short_name IS NOT NULL) THEN
692 begin
693 select DISTINCT nvl(attribute2,attribute_code)
694 into vParameter10
695 from ak_region_items_vl AK
696 where ak.region_code in ( p_region_code, l_nested_region_code)
697 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm10Level_short_name
698 AND ak.node_query_flag = 'Y';
699 IF ( p_parm10Level_short_name ='AS_OF_DATE') THEN
700 lAsOfDateValue := p_Parm10Value_name;
701 END IF;
702 exception
703 --jprabhud added l_return_status for enhancement#2184054
704 when NO_DATA_FOUND then
705 l_return_status := FND_API.G_RET_STS_ERROR;
706 htp.print(p_parm10Level_short_name ||' does not match the level short name defined in AK .');
707 when others then
708 l_return_status := FND_API.G_RET_STS_ERROR;
709 htp.print('cannot obtain correct info for level short name: '||p_parm10Level_short_name);
710 end;
711
712 END IF;
713
714 IF ( p_parm11Level_short_name IS NOT NULL) THEN
715 begin
716 select DISTINCT nvl(attribute2,attribute_code)
717 into vParameter11
718 from ak_region_items_vl AK
719 where ak.region_code in ( p_region_code, l_nested_region_code)
720 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm11Level_short_name
721 AND ak.node_query_flag = 'Y';
722 IF ( p_parm11Level_short_name ='AS_OF_DATE') THEN
723 lAsOfDateValue := p_Parm11Value_name;
724 END IF;
725 exception
726 --jprabhud added l_return_status for enhancement#2184054
727 when NO_DATA_FOUND then
728 l_return_status := FND_API.G_RET_STS_ERROR;
729 htp.print(p_parm11Level_short_name ||' does not match the level short name defined in AK .');
730 when others then
731 l_return_status := FND_API.G_RET_STS_ERROR;
732 htp.print('cannot obtain correct info for level short name: '||p_parm11Level_short_name);
733 end;
734
735 END IF;
736
737 IF ( p_parm12Level_short_name IS NOT NULL) THEN
738 begin
739 select DISTINCT nvl(attribute2,attribute_code)
740 into vParameter12
741 from ak_region_items_vl AK
742 where ak.region_code in ( p_region_code, l_nested_region_code)
743 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm12Level_short_name
744 AND ak.node_query_flag = 'Y';
745 IF ( p_parm12Level_short_name ='AS_OF_DATE') THEN
746 lAsOfDateValue := p_Parm12Value_name;
747 END IF;
748 exception
749 --jprabhud added l_return_status for enhancement#2184054
750 when NO_DATA_FOUND then
751 l_return_status := FND_API.G_RET_STS_ERROR;
752 htp.print(p_parm12Level_short_name ||' does not match the level short name defined in AK .');
753 when others then
754 l_return_status := FND_API.G_RET_STS_ERROR;
755 htp.print('cannot obtain correct info for level short name: '||p_parm12Level_short_name);
756 end;
757
758 END IF;
759
760 IF ( p_parm13Level_short_name IS NOT NULL) THEN
761 begin
762 select DISTINCT nvl(attribute2,attribute_code)
763 into vParameter13
764 from ak_region_items_vl AK
765 where ak.region_code in ( p_region_code, l_nested_region_code)
766 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm13Level_short_name
767 AND ak.node_query_flag = 'Y';
768 IF ( p_parm13Level_short_name ='AS_OF_DATE') THEN
769 lAsOfDateValue := p_Parm13Value_name;
770 END IF;
771 exception
772 --jprabhud added l_return_status for enhancement#2184054
773 when NO_DATA_FOUND then
774 l_return_status := FND_API.G_RET_STS_ERROR;
775 htp.print(p_parm13Level_short_name ||' does not match the level short name defined in AK .');
776 when others then
777 l_return_status := FND_API.G_RET_STS_ERROR;
778 htp.print('cannot obtain correct info for level short name: '||p_parm13Level_short_name);
779 end;
780
781 END IF;
782
783 IF (p_bplan_name IS NOT NULL) THEN
784 vParameter14:='BUSINESS_PLAN';
785 vParm14Value_name:= p_bplan_name;
786 END IF;
787 /*
788 IF (p_viewby_level_short_name IS NOT NULL) THEN
789
790 vParameter15 := 'VIEW_BY';
791 begin
792 Select attribute2
793 into vParm15Value_name
794 from ak_region_items_vl AK
795 where ak.region_code = p_region_code
796 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_viewby_level_short_name
797 AND ak.attribute1 in ('DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE');
798 exception
799 when NO_DATA_FOUND then
800 l_return_status := FND_API.G_RET_STS_ERROR;
801 htp.print(p_viewby_level_short_name ||' does not match the level short name defined in AK.');
802 when others then
803 l_return_status := FND_API.G_RET_STS_ERROR;
804 htp.print('cannot obtain correct info for level short name: '||p_viewby_level_short_name);
805 end;
806
807 END IF;
808 */
809
810 --jprabhud enhancement#2184054*/
811 IF (p_viewby_level_short_name IS NOT NULL) THEN
812 begin
813 if c_dimlvl%ISOPEN then
814 CLOSE c_dimlvl;
815 end if;
816 vParameter15 := 'VIEW_BY';
817 if(BIS_UTILITIES_PVT.is_total_dimlevel(p_viewby_level_short_name,l_err) = FALSE)
818 then
819 OPEN c_dimlvl(NULL, p_viewby_level_short_name);
820 FETCH c_dimlvl INTO vParm15Value_name ;
821 if c_dimlvl%NOTFOUND then
822 CLOSE c_dimlvl;
823 RAISE NO_DATA_FOUND;
824 end if;
825 CLOSE c_dimlvl;
826 else
827 if(p_TimeParmLevel_short_name IS NOT NULL)
828 then
829 OPEN c_dimlvl('%TIME%', NULL);
830 LOOP
831 FETCH c_dimlvl INTO vParm15Value_name ;
832 if c_dimlvl%NOTFOUND then
833 CLOSE c_dimlvl;
834 RAISE NO_DATA_FOUND;
835 end if;
836 EXIT WHEN BIS_UTILITIES_PVT.is_total_dimlevel(substr(vParm15Value_name,
837 instr(vParm15Value_name, '+')+1),l_err) = FALSE;
838 END LOOP;
839 CLOSE c_dimlvl;
840 else
841 OPEN c_dimlvl(NULL, NULL);
842 LOOP
843 FETCH c_dimlvl INTO vParm15Value_name ;
844 if c_dimlvl%NOTFOUND then
845 CLOSE c_dimlvl;
846 RAISE NO_DATA_FOUND;
847 end if;
848 EXIT WHEN BIS_UTILITIES_PVT.is_total_dimlevel(substr(vParm15Value_name,
849 instr(vParm15Value_name, '+')+1),l_err) = FALSE;
850 END LOOP;
851 CLOSE c_dimlvl;
852 end if;
853 end if;
854 EXCEPTION
855 --jprabhud added l_return_status enhancement#2184054
856 WHEN NO_DATA_FOUND then
857 l_return_status := FND_API.G_RET_STS_ERROR;
858 htp.print(p_viewby_level_short_name ||' does not match the level short name defined in AK .');
859 WHEN others then
860 l_return_status := FND_API.G_RET_STS_ERROR;
861 htp.print('cannot obtain correct info for level short name: '||p_viewby_level_short_name);
862 end;
863 END IF;
864
865 IF (p_TimeParmLevel_short_name IS NOT NULL) THEN
866
867 IF (p_TimeFromparmValue_name IS NOT NULL) THEN
868 begin
869 Select nvl(attribute2,attribute_code)
870 into vTimeParameter
871 from ak_region_items_vl AK
872 where ak.region_code = p_region_code
873 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_TimeparmLevel_short_name
874 AND ak.attribute1 in ('DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE', 'HIDE VIEW BY DIMENSION');
875 exception
876 --jprabhud added l_return_status for enhancement#2184054
877 when NO_DATA_FOUND then
878 l_return_status := FND_API.G_RET_STS_ERROR;
879 htp.print(p_TimeparmLevel_short_name ||' does not match the level short name defined in AK .');
880 when others then
881 l_return_status := FND_API.G_RET_STS_ERROR;
882 htp.print('cannot obtain correct info for level short name: '||p_TimeparmLevel_short_name);
883 end;
884
885 vTimeFromParameter := p_TimeFromparmValue_name;
886 END IF;
887
888 IF (p_TimetoparmValue_name IS NOT NULL) THEN
889 vTimeToParameter := p_TimeToparmValue_name;
890 END IF;
891 --BugFix 3280466
892 IF (lAsOfDateValue is not null and l_nested_region_code is not null ) THEN
893 vTimeFromParameter := 'DBC_TIME';
894 vTimeToParameter := 'DBC_TIME';
895 END IF;
896 END IF;
897
898 --Create an entry in FND_LOBs and get the corresponding file id
899 vFileId := BIS_SAVE_REPORT.createEntry('BIS Notification', 'text/html', null, null);
900
901 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
902 --select 'Notice_'||bis_notification_id_s.nextval into vSessionId from dual;
903 select bis_notification_id_s.nextval into vNotifId from dual;
904 vSessionId := 'Notice_'|| vNotifId;
905
906 --BugFix 2833251 Added p_resp_id
907 if (p_resp_id is not null) then
908 vRespId := p_resp_id;
909 else
910 begin
911 vRespId := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
912 exception
913 when others then
914 vRespId := vNotifId;
915 end;
916 end if;
917 -- rcmuthuk BugFix:2810397 added p_UserId
918 if (p_UserId is not null) then
919 vUserId := p_UserId;
920 else
921 begin
922 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
923 exception
924 when others then
925 vUserId := vNotifId;
926 end;
927 end if;
928
929 BIS_PMV_PARAMETERS_PVT.saveParameters
930 (pRegionCode => p_region_code,
931 pFunctionName => p_function_name,
932 pSessionId => vSessionId,
933 pUserId => vUserId,
934 pResponsibilityId => vRespId,
935 pParameter1 => vParameter1,
936 pParameterValue1 => p_Parm1Value_name,
937 pParameter2 => vParameter2,
938 pParameterValue2 => p_Parm2Value_name,
939 pParameter3 => vParameter3,
940 pParameterValue3 => p_Parm3Value_name,
941 pParameter4 => vParameter4,
942 pParameterValue4 => p_Parm4Value_name,
943 pParameter5 => vParameter5,
944 pParameterValue5 => p_Parm5Value_name,
945 pParameter6 => vParameter6,
946 pParameterValue6 => p_Parm6Value_name,
947 pParameter7 => vParameter7,
948 pParameterValue7 => p_Parm7Value_name,
949 pParameter8 => vParameter8,
950 pParameterValue8 => p_Parm8Value_name,
951 pParameter9 => vParameter9,
952 pParameterValue9 => p_Parm9Value_name,
953 pParameter10 => vParameter10,
954 pParameterValue10 => p_Parm10Value_name,
955 pParameter11 => vParameter11,
956 pParameterValue11 => p_Parm11Value_name,
957 pParameter12 => vParameter12,
958 pParameterValue12 => p_Parm12Value_name,
959 pParameter13 => vParameter13,
960 pParameterValue13 => p_Parm13Value_name,
961 pParameter14 => vParameter14,
962 pParameterValue14 => vParm14Value_name,
963 pTimeParameter => vTimeParameter,
964 pTimeFromParameter=> vTimeFromParameter,
965 pTimeToParameter => vTimeToParameter,
966 pViewByValue => vParm15Value_name,
967 pAddToDefault => 'N',
968 pAsOfDateValue => lAsOfDateValue,
969 pAsOfDateMode => 'CURRENT',
970 x_return_status => vReturnStatus,
971 x_msg_count => vMsgCount,
972 x_msg_data => vMsgData
973 );
974
975
976 --jprabhud added enhancement#2184054
977 if(l_return_status <> FND_API.G_RET_STS_ERROR) then
978 l_return_status := nvl(vReturnStatus,FND_API.G_RET_STS_SUCCESS);
979 end if;
980
981
982 /*
983 vReportURL := FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.PLSQL_AGENT)
984 || 'bisviewer.showReport?pRegionCode='||p_region_code||'&pFunctionName='||p_function_name
985 ||'&pSessionId='||vSessionId||'&pUserId='||vUserId||'&pResponsibilityId='||vRespId
986 ||'&pFileId='||vFileId||'&pFirstTime=0&pMode=SONAR';
987 */
988 --jprabhud added enhancement#2184054
989 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
990 vReportURL := FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.WEB_SERVER)||
991 'OA_HTML/bisviewm.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
992 ||'®ionCode='||BIS_PMV_UTIL.encode(p_region_code)||'&functionName='||BIS_PMV_UTIL.encode(p_function_name)
993 ||'&pSessionId='||vSessionId||'&pUserId='||vUserId||'&pResponsibilityId='||vRespId
994 ||'&fileId='||vFileId||'&pFirstTime=0&pMode=SONAR&nlsLangCode='||p_NlsLangCode;
995
996 vHTMLPieces := utl_http.request_pieces(url => vReportURL,
997 max_pieces => 32000);
998 --ksadagop BugFix#3635714
999 --msaran:4589071 - eliminate mod_plsql
1000 -- vURL := fnd_Web_config.trail_slash(fnd_web_Config.gfm_Agent)||'bis_save_report.retrieve_for_php?file_id='||icx_call.encrypt(vFileId);
1001 vURL := fnd_Web_config.trail_slash(fnd_web_Config.jsp_agent)||'bissched.jsp?pStream=Y&file_id='||icx_call.encrypt(vFileId);
1002 else
1003 vURL := NULL;
1004 end if;
1005
1006 --jprabhud added exception block enhancement#2184054
1007 EXCEPTION WHEN OTHERS THEN
1008 vURL := NULL;
1009 END;
1010
1011 end if;
1012
1013 RETURN vURL;
1014 END GET_NOTIFY_RPT_URL;
1015
1016
1017 PROCEDURE GET_NOTIFY_RPT_RUN_URL(
1018 p_measure_id IN VARCHAR2
1019 ,p_region_code in varchar2 default null
1020 ,p_function_name in varchar2 default null
1021 ,p_bplan_name IN VARCHAR2 default null
1022 ,p_viewby_level_short_name IN VARCHAR2 default null
1023 ,p_Parm1Level_short_name IN VARCHAR2 default null
1024 ,p_Parm1Value_name IN VARCHAR2 default null
1025 ,p_Parm2Level_short_name IN VARCHAR2 default null
1026 ,p_Parm2Value_name IN VARCHAR2 default null
1027 ,p_Parm3Level_short_name IN VARCHAR2 default null
1028 ,p_Parm3Value_name IN VARCHAR2 default null
1029 ,p_Parm4Level_short_name IN VARCHAR2 default null
1030 ,p_Parm4Value_name IN VARCHAR2 default null
1031 ,p_Parm5Level_short_name IN VARCHAR2 default null
1032 ,p_Parm5Value_name IN VARCHAR2 default null
1033 ,p_Parm6Level_short_name IN VARCHAR2 default null
1034 ,p_Parm6Value_name IN VARCHAR2 default null
1035 ,p_Parm7Level_short_name IN VARCHAR2 default null
1036 ,p_Parm7Value_name IN VARCHAR2 default null
1037 ,p_Parm8Level_short_name IN VARCHAR2 default null
1038 ,p_Parm8Value_name IN VARCHAR2 default null
1039 ,p_Parm9Level_short_name IN VARCHAR2 default null
1040 ,p_Parm9Value_name IN VARCHAR2 default null
1041 ,p_Parm10Level_short_name IN VARCHAR2 default null
1042 ,p_Parm10Value_name IN VARCHAR2 default null
1043 ,p_Parm11Level_short_name IN VARCHAR2 default null
1044 ,p_Parm11Value_name IN VARCHAR2 default null
1045 ,p_Parm12Level_short_name IN VARCHAR2 default null
1046 ,p_Parm12Value_name IN VARCHAR2 default null
1047 ,p_Parm13Level_short_name IN VARCHAR2 default null
1048 ,p_Parm13Value_name IN VARCHAR2 default null
1049 ,p_TimeParmLevel_short_name in varchar2 default null
1050 ,p_TimeFromParmValue_name in varchar2 default null
1051 ,p_TimeToParmValue_name in varchar2 default null
1052 ,p_resp_id in varchar2 default null
1053 ,p_UserId IN VARCHAR2 default null
1054 ,p_NlsLangCode IN VARCHAR2 default null
1055 --msaran:4415814 - added out params from fileId and reportURL
1056 ,vFileId OUT NOCOPY NUMBER
1057 ,vReportURL OUT NOCOPY VARCHAR2
1058 )
1059 IS
1060 --jprabhud defaulted to NULL enhancement#2184054
1061 vURL varchar2(2000) := NULL;
1062 -- vFileId number := 0;
1063 vSessionId varchar2(32000);
1064 --vUserId varchar2(20) := 'Notification'; --Bug Fix 2165959
1065 --vRespId varchar2(10) := 'NULL';
1066 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
1067 --vUserId varchar2(20) := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1068 --vRespId varchar2(10) := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1069 vUserId varchar2(20);
1070 vRespId varchar2(10);
1071 vNotifId varchar2 (32000);
1072
1073 vParameter1 varchar2(100);
1074 vParameter2 varchar2(100);
1075 vParameter3 varchar2(100);
1076 vParameter4 varchar2(100);
1077 vParameter5 varchar2(100);
1078 vParameter6 varchar2(100);
1079 vParameter7 varchar2(100);
1080 vParameter8 varchar2(100);
1081 vParameter9 varchar2(100);
1082 vParameter10 varchar2(100);
1083 vParameter11 varchar2(100);
1084 vParameter12 varchar2(100);
1085 vParameter13 varchar2(100);
1086 vParameter14 varchar2(100);
1087 vParameter15 varchar2(100);
1088
1089 vParm14Value_name varchar2(100);
1090 vParm15Value_name varchar2(100);
1091
1092 vTimeParameter VARCHAR2(100);
1093 vTimeFromParameter VARCHAR2(100);
1094 vTimeToParameter VARCHAR2(100);
1095
1096 vReturnStatus varchar2(2000);
1097 vMsgData varchar2(2000);
1098 vMsgCount number;
1099
1100 -- vReportURL varchar2(2000);
1101 -- vHTMLPieces utl_http.html_pieces;
1102
1103
1104 --jprabhud added for enhancement#2184054
1105 l_err varchar2(2000);
1106 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1107
1108 l_nested_region_code varchar2(100);
1109
1110 lAsOfDateValue varchar2(100);
1111
1112
1113 ---jprabhud added Cursor, also added VIEWBY PARAMETER in where clause for enhancement#2184054
1114
1115 CURSOR c_dimlvl(p_search_string IN VARCHAR2, p_view_by_level IN VARCHAR2 ) IS
1116 select attribute2
1117 FROM ak_region_items ak
1118 where ak.region_code = p_region_code
1119 AND ak.attribute1 in (G_DIMENSION_LEVEL, G_DIM_LEVEL_SINGLE_VALUE,G_VIEWBY_PARAMETER)
1120 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1)
1121 = nvl(p_view_by_level, substr(ak.attribute2, instr(ak.attribute2, '+')+1))
1122 AND substr(ak.attribute2, 1,instr(ak.attribute2, '+')-1)
1123 like nvl(p_search_string, substr(ak.attribute2, 1,instr(ak.attribute2, '+')-1))
1124 order by display_sequence;
1125 --BugFix 3280466
1126 CURSOR get_nested_region IS
1127 select nested_region_code from ak_region_items
1128 where region_code = p_region_code
1129 and nested_region_code is not null;
1130
1131
1132 BEGIN
1133
1134 vFileId := 0;
1135
1136 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
1137 /*
1138 --BugFix 2833251 Added p_resp_id
1139 if (p_resp_id is not null) then
1140 vRespId := p_resp_id;
1141 end if;
1142 -- rcmuthuk BugFix:2810397 added p_UserId
1143 if (p_UserId is not null) then
1144 vUserId := p_UserId;
1145 end if;
1146 */
1147
1148 --BugFix 3280466
1149 open get_nested_region;
1150 fetch get_nested_region into l_nested_region_code;
1151 close get_nested_region;
1152
1153
1154 if p_region_code is not null and p_function_name is not null then
1155 --jprabhud added BEGIN enhancement#2184054
1156 BEGIN
1157 IF ( p_parm1Level_short_name IS NOT NULL) THEN
1158 begin
1159 --BugFix 3280466 Changed all the sqls to also check from Nested Region
1160 select DISTINCT nvl(attribute2,attribute_code)
1161 into vParameter1
1162 from ak_region_items_vl AK
1163 where ak.region_code in ( p_region_code, l_nested_region_code)
1164 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm1Level_short_name
1165 AND ak.node_query_flag = 'Y';
1166 IF ( p_parm1Level_short_name ='AS_OF_DATE') THEN
1167 lAsOfDateValue := p_Parm1Value_name;
1168 END IF;
1169 exception
1170 --jprabhud added l_return_status for enhancement#2184054
1171 when NO_DATA_FOUND then
1172 l_return_status := FND_API.G_RET_STS_ERROR;
1173 htp.print(p_parm1Level_short_name ||' does not match the level short name defined in AK .');
1174 when others then
1175 l_return_status := FND_API.G_RET_STS_ERROR;
1176 htp.print('cannot obtain correct info for level short name: '||p_parm1Level_short_name);
1177 end;
1178 END IF;
1179
1180 IF ( p_parm2Level_short_name IS NOT NULL) THEN
1181 begin
1182
1183 select DISTINCT nvl(attribute2,attribute_code)
1184 into vParameter2
1185 from ak_region_items_vl AK
1186 where ak.region_code in ( p_region_code, l_nested_region_code)
1187 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm2Level_short_name
1188 AND ak.node_query_flag = 'Y';
1189 IF ( p_parm2Level_short_name ='AS_OF_DATE') THEN
1190 lAsOfDateValue := p_Parm2Value_name;
1191 END IF;
1192 exception
1193 --jprabhud added l_return_status for enhancement#2184054
1194 when NO_DATA_FOUND then
1195 l_return_status := FND_API.G_RET_STS_ERROR;
1196 htp.print(p_parm2Level_short_name ||' does not match the level short name defined in AK .');
1197 when others then
1198 l_return_status := FND_API.G_RET_STS_ERROR;
1199 htp.print('cannot obtain correct info for level short name: '||p_parm2Level_short_name);
1200 end;
1201
1202 END IF;
1203
1204 IF ( p_parm3Level_short_name IS NOT NULL) THEN
1205 begin
1206 select DISTINCT nvl(attribute2,attribute_code)
1207 into vParameter3
1208 from ak_region_items_vl AK
1209 where ak.region_code in ( p_region_code, l_nested_region_code)
1210 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm3Level_short_name
1211 AND ak.node_query_flag = 'Y';
1212 IF ( p_parm3Level_short_name ='AS_OF_DATE') THEN
1213 lAsOfDateValue := p_Parm3Value_name;
1214 END IF;
1215
1216 exception
1217 --jprabhud added l_return_status for enhancement#2184054
1218 when NO_DATA_FOUND then
1219 l_return_status := FND_API.G_RET_STS_ERROR;
1220 htp.print(p_parm3Level_short_name ||' does not match the level short name defined in AK .');
1221 when others then
1222 l_return_status := FND_API.G_RET_STS_ERROR;
1223 htp.print('cannot obtain correct info for level short name: '||p_parm3Level_short_name);
1224 end;
1225
1226 END IF;
1227
1228 IF ( p_parm4Level_short_name IS NOT NULL) THEN
1229 begin
1230 select DISTINCT nvl(attribute2,attribute_code)
1231 into vParameter4
1232 from ak_region_items_vl AK
1233 where ak.region_code in ( p_region_code, l_nested_region_code)
1234 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm4Level_short_name
1235 AND ak.node_query_flag = 'Y';
1236 IF ( p_parm4Level_short_name ='AS_OF_DATE') THEN
1237 lAsOfDateValue := p_Parm4Value_name;
1238 END IF;
1239 exception
1240 --jprabhud added l_return_status for enhancement#2184054
1241 when NO_DATA_FOUND then
1242 l_return_status := FND_API.G_RET_STS_ERROR;
1243 htp.print(p_parm4Level_short_name ||' does not match the level short name defined in AK .');
1244 when others then
1245 l_return_status := FND_API.G_RET_STS_ERROR;
1246 htp.print('cannot obtain correct info for level short name: '||p_parm4Level_short_name);
1247 end;
1248
1249 END IF;
1250
1251 IF ( p_parm5Level_short_name IS NOT NULL) THEN
1252 begin
1253 select DISTINCT nvl(attribute2,attribute_code)
1254 into vParameter5
1255 from ak_region_items_vl AK
1256 where ak.region_code in ( p_region_code, l_nested_region_code)
1257 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm5Level_short_name
1258 AND ak.node_query_flag = 'Y';
1259 IF ( p_parm5Level_short_name ='AS_OF_DATE') THEN
1260 lAsOfDateValue := p_Parm5Value_name;
1261 END IF;
1262 exception
1263 --jprabhud added l_return_status for enhancement#2184054
1264 when NO_DATA_FOUND then
1265 l_return_status := FND_API.G_RET_STS_ERROR;
1266 htp.print(p_parm5Level_short_name ||' does not match the level short name defined in AK .');
1267 when others then
1268 l_return_status := FND_API.G_RET_STS_ERROR;
1269 htp.print('cannot obtain correct info for level short name: '||p_parm5Level_short_name);
1270 end;
1271
1272 END IF;
1273
1274 IF ( p_parm6Level_short_name IS NOT NULL) THEN
1275 begin
1276 select DISTINCT nvl(attribute2,attribute_code)
1277 into vParameter6
1278 from ak_region_items_vl AK
1279 where ak.region_code in ( p_region_code, l_nested_region_code)
1280 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm6Level_short_name
1281 AND ak.node_query_flag = 'Y';
1282 IF ( p_parm6Level_short_name ='AS_OF_DATE') THEN
1283 lAsOfDateValue := p_Parm6Value_name;
1284 END IF;
1285 exception
1286 --jprabhud added l_return_status for enhancement#2184054
1287 when NO_DATA_FOUND then
1288 l_return_status := FND_API.G_RET_STS_ERROR;
1289 htp.print(p_parm6Level_short_name ||' does not match the level short name defined in AK .');
1290 when others then
1291 l_return_status := FND_API.G_RET_STS_ERROR;
1292 htp.print('cannot obtain correct info for level short name: '||p_parm6Level_short_name);
1293 end;
1294
1295 END IF;
1296
1297 IF ( p_parm7Level_short_name IS NOT NULL) THEN
1298 begin
1299 select DISTINCT nvl(attribute2,attribute_code)
1300 into vParameter7
1301 from ak_region_items_vl AK
1302 where ak.region_code = p_region_code
1303 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_parm7Level_short_name
1304 AND ak.node_query_flag = 'Y';
1305 exception
1306 --jprabhud added l_return_status for enhancement#2184054
1307 when NO_DATA_FOUND then
1308 l_return_status := FND_API.G_RET_STS_ERROR;
1309 htp.print(p_parm7Level_short_name ||' does not match the level short name defined in AK .');
1310 when others then
1311 l_return_status := FND_API.G_RET_STS_ERROR;
1312 htp.print('cannot obtain correct info for level short name: '||p_parm7Level_short_name);
1313 end;
1314
1315 END IF;
1316
1317 IF ( p_parm8Level_short_name IS NOT NULL) THEN
1318 begin
1319 select DISTINCT nvl(attribute2,attribute_code)
1320 into vParameter8
1321 from ak_region_items_vl AK
1322 where ak.region_code in ( p_region_code, l_nested_region_code)
1323 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm8Level_short_name
1324 AND ak.node_query_flag = 'Y';
1325 IF ( p_parm8Level_short_name ='AS_OF_DATE') THEN
1326 lAsOfDateValue := p_Parm8Value_name;
1327 END IF;
1328 exception
1329 --jprabhud added l_return_status for enhancement#2184054
1330 when NO_DATA_FOUND then
1331 l_return_status := FND_API.G_RET_STS_ERROR;
1332 htp.print(p_parm8Level_short_name ||' does not match the level short name defined in AK .');
1333 when others then
1334 l_return_status := FND_API.G_RET_STS_ERROR;
1335 htp.print('cannot obtain correct info for level short name: '||p_parm8Level_short_name);
1336 end;
1337
1338 END IF;
1339
1340 IF ( p_parm9Level_short_name IS NOT NULL) THEN
1341 begin
1342 select DISTINCT nvl(attribute2,attribute_code)
1343 into vParameter9
1344 from ak_region_items_vl AK
1345 where ak.region_code in ( p_region_code, l_nested_region_code)
1346 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm9Level_short_name
1347 AND ak.node_query_flag = 'Y';
1348 IF ( p_parm9Level_short_name ='AS_OF_DATE') THEN
1349 lAsOfDateValue := p_Parm9Value_name;
1350 END IF;
1351 exception
1352 --jprabhud added l_return_status for enhancement#2184054
1353 when NO_DATA_FOUND then
1354 l_return_status := FND_API.G_RET_STS_ERROR;
1355 htp.print(p_parm9Level_short_name ||' does not match the level short name defined in AK .');
1356 when others then
1357 l_return_status := FND_API.G_RET_STS_ERROR;
1358 htp.print('cannot obtain correct info for level short name: '||p_parm9Level_short_name);
1359 end;
1360
1361 END IF;
1362
1363 IF ( p_parm10Level_short_name IS NOT NULL) THEN
1364 begin
1365 select DISTINCT nvl(attribute2,attribute_code)
1366 into vParameter10
1367 from ak_region_items_vl AK
1368 where ak.region_code in ( p_region_code, l_nested_region_code)
1369 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm10Level_short_name
1370 AND ak.node_query_flag = 'Y';
1371 IF ( p_parm10Level_short_name ='AS_OF_DATE') THEN
1372 lAsOfDateValue := p_Parm10Value_name;
1373 END IF;
1374 exception
1375 --jprabhud added l_return_status for enhancement#2184054
1376 when NO_DATA_FOUND then
1377 l_return_status := FND_API.G_RET_STS_ERROR;
1378 htp.print(p_parm10Level_short_name ||' does not match the level short name defined in AK .');
1379 when others then
1380 l_return_status := FND_API.G_RET_STS_ERROR;
1381 htp.print('cannot obtain correct info for level short name: '||p_parm10Level_short_name);
1382 end;
1383
1384 END IF;
1385
1386 IF ( p_parm11Level_short_name IS NOT NULL) THEN
1387 begin
1388 select DISTINCT nvl(attribute2,attribute_code)
1389 into vParameter11
1390 from ak_region_items_vl AK
1391 where ak.region_code in ( p_region_code, l_nested_region_code)
1392 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm11Level_short_name
1393 AND ak.node_query_flag = 'Y';
1394 IF ( p_parm11Level_short_name ='AS_OF_DATE') THEN
1395 lAsOfDateValue := p_Parm11Value_name;
1396 END IF;
1397 exception
1398 --jprabhud added l_return_status for enhancement#2184054
1399 when NO_DATA_FOUND then
1400 l_return_status := FND_API.G_RET_STS_ERROR;
1401 htp.print(p_parm11Level_short_name ||' does not match the level short name defined in AK .');
1402 when others then
1403 l_return_status := FND_API.G_RET_STS_ERROR;
1404 htp.print('cannot obtain correct info for level short name: '||p_parm11Level_short_name);
1405 end;
1406
1407 END IF;
1408
1409 IF ( p_parm12Level_short_name IS NOT NULL) THEN
1410 begin
1411 select DISTINCT nvl(attribute2,attribute_code)
1412 into vParameter12
1413 from ak_region_items_vl AK
1414 where ak.region_code in ( p_region_code, l_nested_region_code)
1415 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm12Level_short_name
1416 AND ak.node_query_flag = 'Y';
1417 IF ( p_parm12Level_short_name ='AS_OF_DATE') THEN
1418 lAsOfDateValue := p_Parm12Value_name;
1419 END IF;
1420 exception
1421 --jprabhud added l_return_status for enhancement#2184054
1422 when NO_DATA_FOUND then
1423 l_return_status := FND_API.G_RET_STS_ERROR;
1424 htp.print(p_parm12Level_short_name ||' does not match the level short name defined in AK .');
1425 when others then
1426 l_return_status := FND_API.G_RET_STS_ERROR;
1427 htp.print('cannot obtain correct info for level short name: '||p_parm12Level_short_name);
1428 end;
1429
1430 END IF;
1431
1432 IF ( p_parm13Level_short_name IS NOT NULL) THEN
1433 begin
1434 select DISTINCT nvl(attribute2,attribute_code)
1435 into vParameter13
1436 from ak_region_items_vl AK
1437 where ak.region_code in ( p_region_code, l_nested_region_code)
1438 AND nvl(substr(ak.attribute2, instr(ak.attribute2, '+')+1), attribute_code) = p_parm13Level_short_name
1439 AND ak.node_query_flag = 'Y';
1440 IF ( p_parm13Level_short_name ='AS_OF_DATE') THEN
1441 lAsOfDateValue := p_Parm13Value_name;
1442 END IF;
1443 exception
1444 --jprabhud added l_return_status for enhancement#2184054
1445 when NO_DATA_FOUND then
1446 l_return_status := FND_API.G_RET_STS_ERROR;
1447 htp.print(p_parm13Level_short_name ||' does not match the level short name defined in AK .');
1448 when others then
1449 l_return_status := FND_API.G_RET_STS_ERROR;
1450 htp.print('cannot obtain correct info for level short name: '||p_parm13Level_short_name);
1451 end;
1452
1453 END IF;
1454
1455 IF (p_bplan_name IS NOT NULL) THEN
1456 vParameter14:='BUSINESS_PLAN';
1457 vParm14Value_name:= p_bplan_name;
1458 END IF;
1459 /*
1460 IF (p_viewby_level_short_name IS NOT NULL) THEN
1461
1462 vParameter15 := 'VIEW_BY';
1463 begin
1464 Select attribute2
1465 into vParm15Value_name
1466 from ak_region_items_vl AK
1467 where ak.region_code = p_region_code
1468 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_viewby_level_short_name
1469 AND ak.attribute1 in ('DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE');
1470 exception
1471 when NO_DATA_FOUND then
1472 l_return_status := FND_API.G_RET_STS_ERROR;
1473 htp.print(p_viewby_level_short_name ||' does not match the level short name defined in AK.');
1474 when others then
1475 l_return_status := FND_API.G_RET_STS_ERROR;
1476 htp.print('cannot obtain correct info for level short name: '||p_viewby_level_short_name);
1477 end;
1478
1479 END IF;
1480 */
1481
1482 --jprabhud enhancement#2184054*/
1483 IF (p_viewby_level_short_name IS NOT NULL) THEN
1484 begin
1485 if c_dimlvl%ISOPEN then
1486 CLOSE c_dimlvl;
1487 end if;
1488 vParameter15 := 'VIEW_BY';
1489 if(BIS_UTILITIES_PVT.is_total_dimlevel(p_viewby_level_short_name,l_err) = FALSE)
1490 then
1491 OPEN c_dimlvl(NULL, p_viewby_level_short_name);
1492 FETCH c_dimlvl INTO vParm15Value_name ;
1493 if c_dimlvl%NOTFOUND then
1494 CLOSE c_dimlvl;
1495 RAISE NO_DATA_FOUND;
1496 end if;
1497 CLOSE c_dimlvl;
1498 else
1499 if(p_TimeParmLevel_short_name IS NOT NULL)
1500 then
1501 OPEN c_dimlvl('%TIME%', NULL);
1502 LOOP
1503 FETCH c_dimlvl INTO vParm15Value_name ;
1504 if c_dimlvl%NOTFOUND then
1505 CLOSE c_dimlvl;
1506 RAISE NO_DATA_FOUND;
1507 end if;
1508 EXIT WHEN BIS_UTILITIES_PVT.is_total_dimlevel(substr(vParm15Value_name,
1509 instr(vParm15Value_name, '+')+1),l_err) = FALSE;
1510 END LOOP;
1511 CLOSE c_dimlvl;
1512 else
1513 OPEN c_dimlvl(NULL, NULL);
1514 LOOP
1515 FETCH c_dimlvl INTO vParm15Value_name ;
1516 if c_dimlvl%NOTFOUND then
1517 CLOSE c_dimlvl;
1518 RAISE NO_DATA_FOUND;
1519 end if;
1520 EXIT WHEN BIS_UTILITIES_PVT.is_total_dimlevel(substr(vParm15Value_name,
1521 instr(vParm15Value_name, '+')+1),l_err) = FALSE;
1522 END LOOP;
1523 CLOSE c_dimlvl;
1524 end if;
1525 end if;
1526 EXCEPTION
1527 --jprabhud added l_return_status enhancement#2184054
1528 WHEN NO_DATA_FOUND then
1529 l_return_status := FND_API.G_RET_STS_ERROR;
1530 htp.print(p_viewby_level_short_name ||' does not match the level short name defined in AK .');
1531 WHEN others then
1532 l_return_status := FND_API.G_RET_STS_ERROR;
1533 htp.print('cannot obtain correct info for level short name: '||p_viewby_level_short_name);
1534 end;
1535 END IF;
1536
1537 IF (p_TimeParmLevel_short_name IS NOT NULL) THEN
1538
1539 IF (p_TimeFromparmValue_name IS NOT NULL) THEN
1540 begin
1541 Select nvl(attribute2,attribute_code)
1542 into vTimeParameter
1543 from ak_region_items_vl AK
1544 where ak.region_code = p_region_code
1545 AND substr(ak.attribute2, instr(ak.attribute2, '+')+1) = p_TimeparmLevel_short_name
1546 AND ak.attribute1 in ('DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE', 'HIDE VIEW BY DIMENSION');
1547 exception
1548 --jprabhud added l_return_status for enhancement#2184054
1549 when NO_DATA_FOUND then
1550 l_return_status := FND_API.G_RET_STS_ERROR;
1551 htp.print(p_TimeparmLevel_short_name ||' does not match the level short name defined in AK .');
1552 when others then
1553 l_return_status := FND_API.G_RET_STS_ERROR;
1554 htp.print('cannot obtain correct info for level short name: '||p_TimeparmLevel_short_name);
1555 end;
1556
1557 vTimeFromParameter := p_TimeFromparmValue_name;
1558 END IF;
1559
1560 IF (p_TimetoparmValue_name IS NOT NULL) THEN
1561 vTimeToParameter := p_TimeToparmValue_name;
1562 END IF;
1563 --BugFix 3280466
1564 IF (lAsOfDateValue is not null and l_nested_region_code is not null ) THEN
1565 vTimeFromParameter := 'DBC_TIME';
1566 vTimeToParameter := 'DBC_TIME';
1567 END IF;
1568 END IF;
1569
1570 --Create an entry in FND_LOBs and get the corresponding file id
1571 vFileId := BIS_SAVE_REPORT.createEntry('BIS Notification', 'text/html', null, null);
1572
1573 --jprabhud - 5/26/2004 - BugFix 3649405 for SONAR
1574 --select 'Notice_'||bis_notification_id_s.nextval into vSessionId from dual;
1575 select bis_notification_id_s.nextval into vNotifId from dual;
1576 vSessionId := 'Notice_'|| vNotifId;
1577
1578 --BugFix 2833251 Added p_resp_id
1579 if (p_resp_id is not null) then
1580 vRespId := p_resp_id;
1581 else
1582 begin
1583 vRespId := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1584 exception
1585 when others then
1586 vRespId := vNotifId;
1587 end;
1588 end if;
1589 -- rcmuthuk BugFix:2810397 added p_UserId
1590 if (p_UserId is not null) then
1591 vUserId := p_UserId;
1592 else
1593 begin
1594 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1595 exception
1596 when others then
1597 vUserId := vNotifId;
1598 end;
1599 end if;
1600
1601 BIS_PMV_PARAMETERS_PVT.saveParameters
1602 (pRegionCode => p_region_code,
1603 pFunctionName => p_function_name,
1604 pSessionId => vSessionId,
1605 pUserId => vUserId,
1606 pResponsibilityId => vRespId,
1607 pParameter1 => vParameter1,
1608 pParameterValue1 => p_Parm1Value_name,
1609 pParameter2 => vParameter2,
1610 pParameterValue2 => p_Parm2Value_name,
1611 pParameter3 => vParameter3,
1612 pParameterValue3 => p_Parm3Value_name,
1613 pParameter4 => vParameter4,
1614 pParameterValue4 => p_Parm4Value_name,
1615 pParameter5 => vParameter5,
1616 pParameterValue5 => p_Parm5Value_name,
1617 pParameter6 => vParameter6,
1618 pParameterValue6 => p_Parm6Value_name,
1619 pParameter7 => vParameter7,
1620 pParameterValue7 => p_Parm7Value_name,
1621 pParameter8 => vParameter8,
1622 pParameterValue8 => p_Parm8Value_name,
1623 pParameter9 => vParameter9,
1624 pParameterValue9 => p_Parm9Value_name,
1625 pParameter10 => vParameter10,
1626 pParameterValue10 => p_Parm10Value_name,
1627 pParameter11 => vParameter11,
1628 pParameterValue11 => p_Parm11Value_name,
1629 pParameter12 => vParameter12,
1630 pParameterValue12 => p_Parm12Value_name,
1631 pParameter13 => vParameter13,
1632 pParameterValue13 => p_Parm13Value_name,
1633 pParameter14 => vParameter14,
1634 pParameterValue14 => vParm14Value_name,
1635 pTimeParameter => vTimeParameter,
1636 pTimeFromParameter=> vTimeFromParameter,
1637 pTimeToParameter => vTimeToParameter,
1638 pViewByValue => vParm15Value_name,
1639 pAddToDefault => 'N',
1640 pAsOfDateValue => lAsOfDateValue,
1641 pAsOfDateMode => 'CURRENT',
1642 x_return_status => vReturnStatus,
1643 x_msg_count => vMsgCount,
1644 x_msg_data => vMsgData
1645 );
1646
1647 --jprabhud added enhancement#2184054
1648 if(l_return_status <> FND_API.G_RET_STS_ERROR) then
1649 l_return_status := nvl(vReturnStatus,FND_API.G_RET_STS_SUCCESS);
1650 end if;
1651
1652
1653 --jprabhud added enhancement#2184054
1654 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
1655 vReportURL := FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.WEB_SERVER)||
1656 'OA_HTML/bisviewm.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
1657 ||'®ionCode='||BIS_PMV_UTIL.encode(p_region_code)||'&functionName='||BIS_PMV_UTIL.encode(p_function_name)
1658 ||'&pSessionId='||vSessionId||'&pUserId='||vUserId||'&pResponsibilityId='||vRespId
1659 ||'&fileId='||vFileId||'&pFirstTime=0&pMode=SONAR&nlsLangCode='||p_NlsLangCode;
1660
1661 else
1662 vReportURL := NULL;
1663 end if;
1664
1665 --jprabhud added exception block enhancement#2184054
1666 EXCEPTION WHEN OTHERS THEN
1667 vReportURL := NULL;
1668 END;
1669
1670 end if;
1671
1672 END GET_NOTIFY_RPT_RUN_URL;
1673
1674
1675
1676 --serao -02/10/02 - added for performance of the get_targe, to be owned by the pmf team later
1677 PROCEDURE GET_TARGET_RANGE
1678 (pSource IN VARCHAR2
1679 ,pSessionId IN VARCHAR2
1680 ,pRegionCode IN VARCHAR2
1681 ,pFunctionName IN VARCHAR2
1682 ,pMeasureShortName IN VARCHAR2 DEFAULT NULL
1683 ,pPlanId IN VARCHAR2 DEFAULT NULL
1684 ,pTarget_level_id IN NUMBER DEFAULT NULL
1685 ,pDimension1 IN VARCHAR2 DEFAULT NULL
1686 ,pDim1Level IN VARCHAR2 DEFAULT NULL
1687 ,pDim1LevelValue IN VARCHAR2 DEFAULT NULL
1688 ,pDimension2 IN VARCHAR2 DEFAULT NULL
1689 ,pDim2Level IN VARCHAR2 DEFAULT NULL
1690 ,pDim2LevelValue IN VARCHAR2 DEFAULT NULL
1691 ,pDimension3 IN VARCHAR2 DEFAULT NULL
1692 ,pDim3Level IN VARCHAR2 DEFAULT NULL
1693 ,pDim3LevelValue IN VARCHAR2 DEFAULT NULL
1694 ,pDimension4 IN VARCHAR2 DEFAULT NULL
1695 ,pDim4Level IN VARCHAR2 DEFAULT NULL
1696 ,pDim4LevelValue IN VARCHAR2 DEFAULT NULL
1697 ,pDimension5 IN VARCHAR2 DEFAULT NULL
1698 ,pDim5Level IN VARCHAR2 DEFAULT NULL
1699 ,pDim5LevelValue IN VARCHAR2 DEFAULT NULL
1700 ,pDimension6 IN VARCHAR2 DEFAULT NULL
1701 ,pDim6Level IN VARCHAR2 DEFAULT NULL
1702 ,pDim6LevelValue IN VARCHAR2 DEFAULT NULL
1703 ,pDimension7 IN VARCHAR2 DEFAULT NULL
1704 ,pDim7Level IN VARCHAR2 DEFAULT NULL
1705 ,pDim7LevelValue IN VARCHAR2 DEFAULT NULL
1706 ,xTarget OUT NOCOPY VARCHAR2
1707 ,x_Range1_low OUT NOCOPY VARCHAR2
1708 ,x_Range1_high OUT NOCOPY VARCHAR2
1709 ) IS
1710
1711 l_return_Status VARCHAR2(32000);
1712 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
1713 l_target_rec BIS_TARGET_PUB.TARGET_REC_TYPE;
1714 l_target_rec_p BIS_TARGET_PUB.TARGET_REC_TYPE;
1715 l_error_tbl BIS_UTILITIES_PUB.ERROR_TBL_TYPE;
1716 l_range1_low varchar2(200);
1717 l_range1_high varchar2(200);
1718 l_target VARCHAR2(32000);
1719
1720 -- pvt retrieve------------------------------
1721
1722 l_bisfv_targets_rec bisfv_targets%ROWTYPE;
1723 l_bisbv_target_levels_rec bisbv_target_levels%ROWTYPE;
1724 l_plan_id NUMBER;
1725 l_Business_Plan_Rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
1726 l_Business_Plan_Rec_p BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
1727 l_org_level_value_id VARCHAR2(250);
1728 l_time_level_value_id VARCHAR2(250);
1729
1730 ----------------
1731 lcomputing_function_id NUMBER;
1732 lTarget VARCHAR2(1000);
1733
1734 BEGIN
1735
1736 l_target_rec.plan_id := pPlanId;
1737 l_target_rec.target_level_id := pTarget_level_id;
1738 -- the calculation using the short name has been done in order_dimensions
1739 l_target_rec.dim1_level_Value_id := pDim1LevelValue;
1740 l_target_rec.dim2_level_Value_id := pDim2LevelValue;
1741 l_target_rec.dim3_level_Value_id := pDim3LevelValue;
1742 l_target_rec.dim4_level_Value_id := pDim4LevelValue;
1743 l_target_rec.dim5_level_Value_id := pDim5LevelValue;
1744 l_target_rec.dim6_level_Value_id := pDim6LevelValue;
1745 l_target_rec.dim7_level_Value_id := pDim7LevelValue;
1746
1747 ---- from retrieve_target_pub----------------------
1748 -- do value - id conversions
1749 l_target_rec_p := l_target_rec;
1750 --BugFix 2762795
1751 BIS_TARGET_PVT.Value_ID_Conversion
1752 ( p_api_version => 1.0
1753 , p_Target_Rec => l_Target_Rec_p
1754 , x_Target_Rec => l_Target_Rec
1755 , x_return_status => l_return_status
1756 , x_error_Tbl => l_error_Tbl
1757 );
1758
1759
1760 --Resequence the dimensions. This is for backward compatibility for product teams
1761 l_target_rec_p := l_target_rec;
1762 BIS_UTILITIES_PVT.resequence_dim_level_values
1763 (l_target_rec_p
1764 ,'N'
1765 ,l_target_rec
1766 ,l_Error_tbl
1767 );
1768
1769 -- from retrieve_target_pvt---------------------------------------------
1770
1771 IF( BIS_UTILITIES_PUB.Value_Not_Missing(l_Target_Rec.Target_ID)
1772 = FND_API.G_TRUE
1773 AND l_Target_Rec.Target_ID IS NOT NULL
1774 ) THEN
1775 SELECT *
1776 INTO l_bisfv_targets_rec
1777 FROM bisfv_targets bisfv_targets
1778 WHERE bisfv_targets.TARGET_ID = l_Target_Rec.Target_ID;
1779
1780
1781 ELSIF( BIS_UTILITIES_PUB.Value_Not_Missing(l_Target_Rec.Target_Level_ID)
1782 = FND_API.G_TRUE
1783 AND l_Target_Rec.Target_Level_ID IS NOT NULL
1784 ) THEN
1785 SELECT *
1786 INTO l_bisbv_target_levels_rec
1787 FROM bisbv_target_levels bisbv_target_levels
1788 WHERE bisbv_target_levels.TARGET_LEVEL_ID
1789 = l_Target_Rec.Target_Level_ID;
1790
1791 ---If Plan Id is not given, get Plan Id from Short name
1792
1793 if (BIS_UTILITIES_PUB.Value_Missing(l_Target_Rec.Plan_ID)
1794 = FND_API.G_TRUE) then
1795 if (BIS_UTILITIES_PUB.Value_Not_Missing(l_Target_Rec.Plan_Short_Name)
1796 = FND_API.G_TRUE) then
1797 l_Business_Plan_Rec.Business_Plan_Short_Name := l_Target_Rec.Plan_Short_Name;
1798 l_Business_Plan_Rec_p := l_Business_Plan_Rec;
1799 -- BugFix 2762795
1800 BIS_BUSINESS_PLAN_PVT.Value_ID_Conversion
1801 ( p_api_version => 1.0
1802 , p_Business_Plan_Rec => l_Business_Plan_Rec_p
1803 , x_Business_Plan_Rec => l_Business_Plan_Rec
1804 , x_return_status => l_return_status
1805 , x_error_Tbl => l_error_tbl
1806 );
1807 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
1808 l_plan_id := l_Business_Plan_Rec.Business_Plan_ID;
1809 end if;
1810 end if;
1811 else
1812 l_plan_id := l_Target_Rec.Plan_ID ;
1813 end if;
1814
1815 if(BIS_UTILITIES_PUB.Value_Missing(l_Target_Rec.Org_Level_Value_ID) = FND_API.G_TRUE)
1816 then l_org_level_value_id := NULL;
1817 else
1818 l_org_level_value_id := l_Target_Rec.Org_Level_Value_ID;
1819 end if;
1820
1821
1822 if(BIS_UTILITIES_PUB.Value_Missing(l_Target_Rec.Time_Level_Value_ID) = FND_API.G_TRUE)
1823 then l_time_level_value_id := NULL;
1824 else
1825 l_time_level_value_id := l_Target_Rec.Time_Level_Value_ID;
1826 end if;
1827 --------------------------------------------
1828
1829 SELECT *
1830 INTO l_bisfv_targets_rec
1831 FROM bisfv_targets bisfv_targets
1832 WHERE bisfv_targets.TARGET_LEVEL_ID = pTarget_Level_ID
1833 -- used to be p_Target_Rec.Plan_ID
1834 AND bisfv_targets.PLAN_ID = l_plan_id
1835
1836 ---changed org and time logic
1837 AND (l_org_level_value_id IS NULL
1838 OR NVL(bisfv_targets.ORG_LEVEL_VALUE_ID,'T') = NVL(l_org_level_value_id, 'T'))
1839
1840 AND (l_time_level_value_id IS NULL
1841 OR NVL(bisfv_targets.TIME_LEVEL_VALUE_ID,'T') = NVL(l_time_level_value_id, 'T'))
1842
1843 AND NVL(bisfv_targets.DIM1_LEVEL_VALUE_ID, 'T')
1844 = DECODE( l_Target_Rec.Dim1_Level_Value_ID
1845 , FND_API.G_MISS_CHAR
1846 , 'T'
1847 , NVL(l_Target_Rec.Dim1_Level_Value_ID, 'T')
1848 )
1849 AND NVL(bisfv_targets.DIM2_LEVEL_VALUE_ID, 'T')
1850 = DECODE( l_Target_Rec.Dim2_Level_Value_ID
1851 , FND_API.G_MISS_CHAR
1852 , 'T'
1853 , NVL(l_Target_Rec.Dim2_Level_Value_ID, 'T')
1854 )
1855 AND NVL(bisfv_targets.DIM3_LEVEL_VALUE_ID, 'T')
1856 = DECODE( l_Target_Rec.Dim3_Level_Value_ID
1857 , FND_API.G_MISS_CHAR
1858 , 'T'
1859 , NVL(l_Target_Rec.Dim3_Level_Value_ID, 'T')
1860 )
1861 AND NVL(bisfv_targets.DIM4_LEVEL_VALUE_ID, 'T')
1862 = DECODE( l_Target_Rec.Dim4_Level_Value_ID
1863 , FND_API.G_MISS_CHAR
1864 , 'T'
1865 , NVL(l_Target_Rec.Dim4_Level_Value_ID, 'T')
1866 )
1867 AND NVL(bisfv_targets.DIM5_LEVEL_VALUE_ID, 'T')
1868 = DECODE( l_Target_Rec.Dim5_Level_Value_ID
1869 , FND_API.G_MISS_CHAR
1870 , 'T'
1871 , NVL(l_Target_Rec.Dim5_Level_Value_ID, 'T')
1872 )
1873 AND NVL(bisfv_targets.DIM6_LEVEL_VALUE_ID, 'T')
1874 = DECODE( l_Target_Rec.Dim6_Level_Value_ID
1875 , FND_API.G_MISS_CHAR
1876 , 'T'
1877 , NVL(l_Target_Rec.Dim6_Level_Value_ID, 'T')
1878 )
1879 AND NVL(bisfv_targets.DIM7_LEVEL_VALUE_ID, 'T')
1880 = DECODE( l_Target_Rec.Dim7_Level_Value_ID
1881 , FND_API.G_MISS_CHAR
1882 , 'T'
1883 , NVL(l_Target_Rec.Dim7_Level_Value_ID, 'T')
1884 )
1885 ;
1886 END IF;
1887 -----------------------------------------------------------------
1888 lTarget := l_bisfv_targets_rec.target;
1889
1890 IF ((BIS_UTILITIES_PUB.Value_Missing(l_bisfv_targets_rec.Target) = FND_API.G_TRUE)
1891 OR (BIS_UTILITIES_PUB.Value_Null(l_bisfv_targets_rec.Target) = FND_API.G_TRUE))
1892 THEN
1893 IF ((BIS_UTILITIES_PUB.Value_Not_Missing
1894 (l_bisfv_targets_rec.target_level_ID) = FND_API.G_TRUE)
1895 AND (BIS_UTILITIES_PUB.Value_Not_Null
1896 (l_bisfv_targets_rec.target_level_ID) = FND_API.G_TRUE))
1897 THEN
1898 Select
1899 COMPUTING_FUNCTION_ID
1900 into
1901 lComputing_Function_Id
1902 from bisbv_target_levels
1903 where target_level_ID =l_bisfv_targets_rec.Target_Level_ID;
1904
1905 END IF;
1906
1907 -- only compute target if found computing fn id
1908 --
1909 IF ((BIS_UTILITIES_PUB.Value_Not_Missing
1910 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE)
1911 AND (BIS_UTILITIES_PUB.Value_Not_Null
1912 (l_Target_Level_Rec.COMPUTING_FUNCTION_ID) = FND_API.G_TRUE))
1913 THEN
1914 l_Target_Rec.Target_ID := l_bisfv_targets_rec.Target_ID;
1915 l_Target_Rec.Target_Level_ID := l_bisfv_targets_rec.Target_Level_ID;
1916 l_Target_Rec.Target_Level_Short_Name
1917 := l_bisfv_targets_rec.Target_Level_Short_Name;
1918 l_Target_Rec.Target_Level_Name
1919 := l_bisfv_targets_rec.Target_Level_Name;
1920 l_Target_Rec.Plan_ID := l_bisfv_targets_rec.Plan_ID;
1921 l_Target_Rec.Plan_Short_Name := l_bisfv_targets_rec.Plan_Short_Name;
1922 l_Target_Rec.Plan_Name := l_bisfv_targets_rec.Plan_Name;
1923 l_Target_Rec.Org_Level_Value_ID
1924 := l_bisfv_targets_rec.Org_Level_Value_ID;
1925 l_Target_Rec.Time_Level_Value_ID
1926 := l_bisfv_targets_rec.Time_Level_Value_ID;
1927 l_Target_Rec.Dim1_Level_Value_ID
1928 := l_bisfv_targets_rec.Dim1_Level_Value_ID;
1929 l_Target_Rec.Dim2_Level_Value_ID
1930 := l_bisfv_targets_rec.Dim2_Level_Value_ID;
1931 l_Target_Rec.Dim3_Level_Value_ID
1932 := l_bisfv_targets_rec.Dim3_Level_Value_ID;
1933 l_Target_Rec.Dim4_Level_Value_ID
1934 := l_bisfv_targets_rec.Dim4_Level_Value_ID;
1935 l_Target_Rec.Dim5_Level_Value_ID
1936 := l_bisfv_targets_rec.Dim5_Level_Value_ID;
1937 l_Target_Rec.Dim6_Level_Value_ID
1938 := l_bisfv_targets_rec.Dim6_Level_Value_ID;
1939 l_Target_Rec.Dim7_Level_Value_ID
1940 := l_bisfv_targets_rec.Dim7_Level_Value_ID;
1941 l_Target_Rec.Target := l_bisfv_targets_rec.Target;
1942 l_Target_Rec.Range1_low := l_bisfv_targets_rec.Range1_low;
1943 l_Target_Rec.Range1_high := l_bisfv_targets_rec.Range1_high;
1944 l_Target_Rec.Range2_low := l_bisfv_targets_rec.Range2_low;
1945 l_Target_Rec.Range2_high := l_bisfv_targets_rec.Range2_high;
1946 l_Target_Rec.Range3_low := l_bisfv_targets_rec.Range3_low;
1947 l_Target_Rec.Range3_high := l_bisfv_targets_rec.Range3_high;
1948 l_Target_Rec.Notify_Resp1_ID := l_bisfv_targets_rec.Notify_Resp1_ID;
1949 l_Target_Rec.Notify_Resp1_Short_Name
1950 := l_bisfv_targets_rec.Notify_Resp1_Short_Name;
1951 l_Target_Rec.Notify_Resp1_Name := l_bisfv_targets_rec.Notify_Resp1_Name;
1952 l_Target_Rec.Notify_Resp2_ID := l_bisfv_targets_rec.Notify_Resp2_ID;
1953 l_Target_Rec.Notify_Resp2_Short_Name
1954 := l_bisfv_targets_rec.Notify_Resp2_Short_Name;
1955 l_Target_Rec.Notify_Resp2_Name := l_bisfv_targets_rec.Notify_Resp2_Name;
1956 l_Target_Rec.Notify_Resp3_ID := l_bisfv_targets_rec.Notify_Resp3_ID;
1957 l_Target_Rec.Notify_Resp3_Short_Name
1958 := l_bisfv_targets_rec.Notify_Resp3_Short_Name;
1959 l_Target_Rec.Notify_Resp3_Name := l_bisfv_targets_rec.Notify_Resp3_Name;
1960
1961 lTarget :=
1962 BIS_TARGET_PVT.Get_Target
1963 ( p_computing_function_id => lcomputing_function_id
1964 , p_target_rec => l_target_rec
1965 );
1966 END IF;
1967 END IF;
1968
1969
1970 xTarget := lTarget;
1971 x_range1_low := l_bisfv_targets_rec.Range1_low;
1972 x_range1_high := l_bisfv_targets_rec.Range1_high;
1973 if (l_bisfv_targets_rec.Target_ID is null) or (l_bisfv_targets_rec.Target_ID = FND_API.G_MISS_NUM) then
1974 xTarget := 'NONE';
1975 x_range1_low := 'NONE';
1976 x_Range1_high := 'NONE';
1977 else
1978 if (x_range1_low = FND_API.G_MISS_NUM) or (x_range1_low is null) then
1979 x_range1_low := 'NONE';
1980 end if;
1981 if (x_range1_high = FND_API.G_MISS_NUM) or (x_range1_high is null) then
1982 x_range1_high := 'NONE';
1983 end if;
1984 end if;
1985
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 xTarget := 'NONE';
1989 x_range1_low := 'NONE';
1990 x_Range1_high := 'NONE';
1991 END GET_TARGET_RANGE;
1992
1993
1994 --serao -02/10/02 - added for performance of the get_target
1995 FUNCTION GET_TARGET_NEW
1996 (pSource IN VARCHAR2
1997 ,pSessionId IN VARCHAR2
1998 ,pRegionCode IN VARCHAR2
1999 ,pFunctionName IN VARCHAR2
2000 ,pMeasureShortName IN VARCHAR2 DEFAULT NULL
2001 ,pPlanId IN VARCHAR2 DEFAULT NULL
2002 ,pTarget_level_id IN NUMBER DEFAULT NULL
2003 ,pDimension1 IN VARCHAR2 DEFAULT NULL
2004 ,pDim1Level IN VARCHAR2 DEFAULT NULL
2005 ,pDim1LevelValue IN VARCHAR2 DEFAULT NULL
2006 ,pDimension2 IN VARCHAR2 DEFAULT NULL
2007 ,pDim2Level IN VARCHAR2 DEFAULT NULL
2008 ,pDim2LevelValue IN VARCHAR2 DEFAULT NULL
2009 ,pDimension3 IN VARCHAR2 DEFAULT NULL
2010 ,pDim3Level IN VARCHAR2 DEFAULT NULL
2011 ,pDim3LevelValue IN VARCHAR2 DEFAULT NULL
2012 ,pDimension4 IN VARCHAR2 DEFAULT NULL
2013 ,pDim4Level IN VARCHAR2 DEFAULT NULL
2014 ,pDim4LevelValue IN VARCHAR2 DEFAULT NULL
2015 ,pDimension5 IN VARCHAR2 DEFAULT NULL
2016 ,pDim5Level IN VARCHAR2 DEFAULT NULL
2017 ,pDim5LevelValue IN VARCHAR2 DEFAULT NULL
2018 ,pDimension6 IN VARCHAR2 DEFAULT NULL
2019 ,pDim6Level IN VARCHAR2 DEFAULT NULL
2020 ,pDim6LevelValue IN VARCHAR2 DEFAULT NULL
2021 ,pDimension7 IN VARCHAR2 DEFAULT NULL
2022 ,pDim7Level IN VARCHAR2 DEFAULT NULL
2023 ,pDim7LevelValue IN VARCHAR2 DEFAULT NULL
2024 ) RETURN VARCHAR2
2025 IS
2026 l_target_url VARCHAR2(32000);
2027 lTarget VARCHAR2(2000);
2028 l_range1_low varchar2(200);
2029 l_range1_high varchar2(200);
2030
2031 BEGIN
2032 get_target_range(
2033 pSource
2034 ,pSessionId
2035 ,pRegionCode
2036 ,pFunctionName
2037 ,pMeasureShortName
2038 ,pPlanId
2039 ,pTarget_level_id
2040 ,pDimension1
2041 ,pDim1Level
2042 ,pDim1LevelValue
2043 ,pDimension2
2044 ,pDim2Level
2045 ,pDim2LevelValue
2046 ,pDimension3
2047 ,pDim3Level
2048 ,pDim3LevelValue
2049 ,pDimension4
2050 ,pDim4Level
2051 ,pDim4LevelValue
2052 ,pDimension5
2053 ,pDim5Level
2054 ,pDim5LevelValue
2055 ,pDimension6
2056 ,pDim6Level
2057 ,pDim6LevelValue
2058 ,pDimension7
2059 ,pDim7Level
2060 ,pDim7LevelValue
2061 ,lTarget
2062 ,l_range1_low
2063 ,l_range1_high );
2064
2065 l_Target_url := FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.WEB_SERVER)||
2066 'OA_HTML/bistared.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
2067 ||'&sessionid='||pSessionId
2068 ||'&RegionCode='||bis_pmv_util.encode(pRegionCode)
2069 ||'&FunctionName='||bis_pmv_util.encode(pFunctionName)
2070 ||'&SortInfo='||bis_pmv_util.encode('Sortcolumn2Asc')
2071 ||'&Measure='||pMeasureShortName||'&PlanId='||pPlanId
2072 ||'&Dim1Level='|| BIS_PMV_UTIL.encode(pDim1Level)
2073 ||'&Dim2Level='|| BIS_PMV_UTIL.encode(pDim2Level)
2074 ||'&Dim3Level='|| BIS_PMV_UTIL.encode(pDim3Level)
2075 ||'&Dim4Level='|| BIS_PMV_UTIL.encode(pDim4Level)
2076 ||'&Dim5Level='|| BIS_PMV_UTIL.encode(pDim5Level)
2077 ||'&Dim6Level='|| BIS_PMV_UTIL.encode(pDim6Level)
2078 ||'&Dim7Level='|| BIS_PMV_UTIL.encode(pDim7Level)
2079 ||'&Dim1LevelValue='||BIS_PMV_UTIL.encode(pDim1LevelValue)
2080 ||'&Dim2LevelValue='||BIS_PMV_UTIL.encode(pDim2LevelValue)
2081 ||'&Dim3LevelValue='||BIS_PMV_UTIL.encode(pDim3LevelValue)
2082 ||'&Dim4LevelValue='||BIS_PMV_UTIL.encode(pDim4LevelValue)
2083 ||'&Dim5LevelValue='||BIS_PMV_UTIL.encode(pDim5LevelValue)
2084 ||'&Dim6LevelValue='||BIS_PMV_UTIL.encode(pDim6LevelValue)
2085 ||'&Dim7LevelValue='||BIS_PMV_UTIL.encode(pDim7LevelValue);
2086 l_target_url := l_target_url||'*'||lTarget||'**'||l_range1_low||'***'||l_Range1_high;
2087 return l_target_url;
2088
2089 END GET_TARGET_NEW;
2090
2091 END BIS_PMV_PMF_PVT;
[Home] [Help]