[Home] [Help]
PACKAGE BODY: APPS.BIS_PMF_ALERT_REG_PVT
Source
1 PACKAGE BODY BIS_PMF_ALERT_REG_PVT AS
2 /* $Header: BISVARTB.pls 120.0 2005/06/01 15:54:40 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVARTB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Private API for managing Alert Registration Repository
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 17-May-2000 jradhakr Creation
19 REM | June 2000 irchen takeover
20 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
21 REM | parameters.
22 REM | 27-Oct-2004 aguwalan Bug#3909131, added Add_Users_To_Role |
23 REM | 21-MAR-2005 ankagarw bug#4235732 - changing count(*) to count(1) |
24 REM +=======================================================================+
25 */
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_PMF_ALERT_REG_PVT';
28 G_PERFORMANCE_ALERT_PROMPTS CONSTANT VARCHAR2(1000)
29 := 'BIS_PERFORMANCE_ALERT_PROMPTS';
30 G_AD_HOC_ROLE_DISPLAY_NAME CONSTANT VARCHAR2(1000)
31 := 'BIS_AD_HOC_ROLE_DISPLAY_NAME';
32
33 G_AMPERSAND CONSTANT VARCHAR2(1) := '&';
34
35 l_debug_text VARCHAR2(32000);
36
37
38 PROCEDURE Create_Parameter_set
39 ( p_api_version IN NUMBER
40 , p_commit IN VARCHAR2 := FND_API.G_FALSE
41 , p_Param_Set_Rec IN OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
42 , x_return_status OUT NOCOPY VARCHAR2
43 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
44 )
45 IS
46 l_user_id number;
47 l_login_id number;
48 l_registration_id number;
49 l_role_name varchar2(30);
50 l_null_role_name varchar2(30) := NULL;
51 l_role_display_name varchar2(32000);
52 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
53 l_role_name_p VARCHAR2(30);
54 l_null_role_name_p VARCHAR2(30) := NULL;
55
56 BEGIN
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 /* Commented out NOCOPY for performance reasons.
61
62 BIS_PMF_ALERT_REG_PVT.Validate_Parameter_set
63 ( p_api_version => p_api_version
64 , p_Param_Set_Rec => p_Param_Set_rec
65 , x_return_status => x_Return_status
66 , x_error_Tbl => x_error_tbl
67 );
68 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
69 RAISE FND_API.G_EXC_ERROR;
70 END IF;
71 */
72
73 l_user_id := fnd_global.USER_ID;
74 l_login_id := fnd_global.LOGIN_ID;
75
76 --
77 -- Selecting the Next Registion Id from Sequence Generator
78 --
79 select bis_alert_registration_s.NextVal into l_registration_id from dual;
80 --
81 --BIS_UTILITIES_PUB.put_line(p_text =>'creating paramter set. notifier code: '
82 --||p_Param_Set_Rec.NOTIFIERS_CODE);
83
84 IF p_Param_Set_Rec.NOTIFIERS_CODE IS NULL THEN
85 l_role_name := G_BIS_ALERT_ROLE || to_char(l_registration_id);
86 l_role_display_name := BIS_UTILITIES_PVT.getPrompt
87 ( G_PERFORMANCE_ALERT_PROMPTS
88 , G_AD_HOC_ROLE_DISPLAY_NAME
89 );
90 --BIS_UTILITIES_PUB.put_line(p_text =>'role name, display name: '||l_role_name
91 --||', '||l_role_display_name);
92
93 BEGIN
94 l_role_name_p := l_role_name;
95 wf_directory.CreateAdHocRole
96 ( role_name => l_role_name_p
97 -- , role_display_name => l_role_display_name
98 , role_display_name => l_role_name
99 , expiration_date => NULL);
100 EXCEPTION
101 WHEN OTHERS THEN
102 BIS_UTILITIES_PUB.put_line(p_text =>'1st error while creating role: '||l_role_name
103 ||'. error: '||sqlerrm);
104
105 BEGIN
106 null;
107 select '~WF_ADHOC-' ||WF_ADHOC_ROLE_S.NEXTVAL
108 into l_null_role_name
109 from dual;
110 l_null_role_name_p := l_null_role_name;
111 wf_directory.CreateAdHocRole
112 ( role_name => l_null_role_name_p
113 -- , role_display_name => l_role_display_name
114 , role_display_name => l_null_role_name
115 , expiration_date => NULL);
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 BIS_UTILITIES_PUB.put_line(p_text =>'2st error while creating role: '||l_null_role_name
120 ||'. error: '||sqlerrm);
121 END;
122
123 END;
124
125 p_Param_Set_Rec.NOTIFIERS_CODE := l_role_name;
126 end if;
127 /*
128 BIS_UTILITIES_PUB.put_line(p_text =>'ART: role: '|| p_Param_Set_Rec.NOTIFIERS_CODE);
129 BIS_UTILITIES_PUB.put_line(p_text =>'target level: '||p_Param_Set_Rec.TARGET_LEVEL_ID);
130 BIS_UTILITIES_PUB.put_line(p_text =>'time dim level: '
131 ||p_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID);
132 BIS_UTILITIES_PUB.put_line(p_text =>'time dim value: '||p_Param_Set_Rec.PARAMETER2_VALUE);
133 */
134 --
135 insert into bis_pmf_alert_parameters(
136 REGISTRATION_ID
137 , PERFORMANCE_MEASURE_ID
138 , TARGET_LEVEL_ID
139 , TIME_DIMENSION_LEVEL_ID
140 , PLAN_ID
141 , NOTIFIERS_CODE
142 , PARAMETER1_VALUE
143 , PARAMETER2_VALUE
144 , PARAMETER3_VALUE
145 , PARAMETER4_VALUE
146 , PARAMETER5_VALUE
147 , PARAMETER6_VALUE
148 , PARAMETER7_VALUE
149 , NOTIFY_OWNER_FLAG
150 , CREATION_DATE
151 , CREATED_BY
152 , LAST_UPDATE_DATE
153 , LAST_UPDATED_BY
154 , LAST_UPDATE_LOGIN
155 )
156 values
157 ( l_registration_id
158 , p_Param_Set_Rec.PERFORMANCE_MEASURE_ID
159 , p_Param_Set_Rec.TARGET_LEVEL_ID
160 , p_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID
161 , p_Param_Set_Rec.PLAN_ID
162 , p_Param_Set_Rec.NOTIFIERS_CODE
163 , p_Param_Set_Rec.PARAMETER1_VALUE
164 , p_Param_Set_Rec.PARAMETER2_VALUE
165 , p_Param_Set_Rec.PARAMETER3_VALUE
166 , p_Param_Set_Rec.PARAMETER4_VALUE
167 , p_Param_Set_Rec.PARAMETER5_VALUE
168 , p_Param_Set_Rec.PARAMETER6_VALUE
169 , p_Param_Set_Rec.PARAMETER7_VALUE
170 , p_Param_Set_Rec.NOTIFY_OWNER_FLAG
171 , SYSDATE
172 , l_user_id
173 , SYSDATE
174 , l_user_id
175 , l_login_id
176 );
177
178 if (p_commit = FND_API.G_TRUE) then
179 --BIS_UTILITIES_PUB.put_line(p_text =>'committed insert. status: '||x_return_status);
180 COMMIT;
181 end if;
182
183 EXCEPTION
184 when FND_API.G_EXC_ERROR then
185 x_return_status := FND_API.G_RET_STS_ERROR ;
186 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 in Create_Parameter_set'||sqlerrm);
187 when FND_API.G_EXC_UNEXPECTED_ERROR then
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
189 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 in Create_Parameter_set'||sqlerrm);
190 when others then
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 in Create_Parameter_set'||sqlerrm);
193 l_error_tbl := x_error_Tbl;
194 BIS_UTILITIES_PVT.Add_Error_Message
195 ( p_error_msg_id => SQLCODE
196 , p_error_description => SQLERRM
197 , p_error_proc_name => G_PKG_NAME||'.Create_Parameter_set'
198 , p_error_table => l_error_tbl
199 , x_error_table => x_error_tbl
200 );
201
202 END Create_Parameter_set;
203
204 --
205 -- Delete one parameter set.
206 --
207
208 PROCEDURE Delete_Parameter_set
209 ( p_api_version IN NUMBER
210 , p_commit IN VARCHAR2 := FND_API.G_FALSE
211 , p_Param_Set_Rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
212 , x_return_status OUT NOCOPY VARCHAR2
213 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
214 )
215 IS
216 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
217 BEGIN
218
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220
221 IF p_Param_Set_Rec.registration_id IS NOT NULL THEN
222 Delete_Parameter_Set(p_Param_Set_Rec.registration_id,x_return_status);
223 ELSE
224 BIS_UTILITIES_PUB.put_line(p_text =>'Cannot delete parameter set without Registeration ID.');
225 END IF;
226 IF p_commit = FND_API.G_TRUE THEN
227 commit;
228 END IF;
229
230 EXCEPTION
231 when FND_API.G_EXC_ERROR then
232 x_return_status := FND_API.G_RET_STS_ERROR ;
233 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 in delete parameter set: '||sqlerrm);
234 when FND_API.G_EXC_UNEXPECTED_ERROR then
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
236 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 in delete parameter set: '||sqlerrm);
237 when others then
238 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 in delete parameter set: '||sqlerrm);
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
240 l_error_tbl := x_error_Tbl;
241 BIS_UTILITIES_PVT.Add_Error_Message
242 ( p_error_msg_id => SQLCODE
243 , p_error_description => SQLERRM
244 , p_error_proc_name => G_PKG_NAME||'.Delete_Parameter_set'
245 , p_error_table => l_error_tbl
246 , x_error_table => x_error_tbl
247 );
248
249 END Delete_Parameter_set;
250
251 PROCEDURE Delete_Parameter_Set
252 ( p_registration_ID IN NUMBER
253 , x_return_status OUT NOCOPY VARCHAR2
254 )
255 IS
256
257 l_count_1 number := 0;
258 l_count_2 number := 0;
259 l_debug VARCHAR2(32000);
260
261 BEGIN
262
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 -- BIS_UTILITIES_PUB.put_line(p_text =>'Deleting parameter set: '||p_registration_ID);
266
267 -- select count(1) into l_count_1 from bis_pmf_alert_parameters;
268 -- where registration_id = p_registration_id;
269
270 delete from bis_pmf_alert_parameters
271 where registration_id = p_registration_id;
272 commit;
273
274 --select count(1) into l_count_2 from bis_pmf_alert_parameters;
275 -- where registration_id = p_registration_id;
276
277 BIS_UTILITIES_PUB.put_line(p_text =>'before delete: '||l_count_1||', after delete: '||l_count_2);
278 l_debug := 'before delete: '||l_count_1||', after delete: '||l_count_2;
279
280 x_return_status := x_return_status ||'--delete debug--'||l_debug;
281
282 EXCEPTION
283 when others then
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
285 BIS_UTILITIES_PUB.put_line(p_text =>'exception in delete parameter set: '||sqlerrm);
286 END Delete_Parameter_Set;
287
288 --
289 -- Retrieve a Table of parmeter set for the given PM and time
290 -- dimension level.
291 --
292 PROCEDURE Retrieve_Parameter_set
293 ( p_api_version IN NUMBER
294 , p_measure_id IN NUMBER
295 , p_time_dimension_level_id IN NUMBER
296 , p_current_row IN VARCHAR2 := NULL
297 , x_Param_Set_Tbl OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
298 , x_return_status OUT NOCOPY VARCHAR2
299 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
300 )
301 IS
302
303 l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
304 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
305 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
306
307 Cursor parameter_set_list is select
308 REGISTRATION_ID
309 , PERFORMANCE_MEASURE_ID
310 , TARGET_LEVEL_ID
311 , TIME_DIMENSION_LEVEL_ID
312 , PLAN_ID
313 , NOTIFIERS_CODE
314 , PARAMETER1_VALUE
315 , PARAMETER2_VALUE
316 , PARAMETER3_VALUE
317 , PARAMETER4_VALUE
318 , PARAMETER5_VALUE
319 , PARAMETER6_VALUE
320 , PARAMETER7_VALUE
321 , NOTIFY_OWNER_FLAG
322 from
323 BIS_PMF_ALERT_PARAMETERS
324 where PERFORMANCE_MEASURE_ID = p_measure_id
325 and TIME_DIMENSION_LEVEL_ID = p_time_dimension_level_id;
326
327 i number := 0;
328
329 BEGIN
330
331 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving parameter set by Measure');
332 for p_set in parameter_set_list
333 loop
334
335 l_Param_Set_rec.REGISTRATION_ID := p_set.REGISTRATION_ID;
336 l_Param_Set_rec.PERFORMANCE_MEASURE_ID := p_set.PERFORMANCE_MEASURE_ID;
337 l_Param_Set_rec.TARGET_LEVEL_ID := p_set.TARGET_LEVEL_ID;
338 l_Param_Set_rec.TIME_DIMENSION_LEVEL_ID
339 := p_set.TIME_DIMENSION_LEVEL_ID;
340 l_Param_Set_rec.PLAN_ID := p_set.PLAN_ID;
341 l_Param_Set_rec.NOTIFIERS_CODE := p_set.NOTIFIERS_CODE;
342 l_Param_Set_rec.PARAMETER1_VALUE := p_set.PARAMETER1_VALUE;
343 l_Param_Set_rec.PARAMETER2_VALUE := p_set.PARAMETER2_VALUE;
344 l_Param_Set_rec.PARAMETER3_VALUE := p_set.PARAMETER3_VALUE;
345 l_Param_Set_rec.PARAMETER4_VALUE := p_set.PARAMETER4_VALUE;
346 l_Param_Set_rec.PARAMETER5_VALUE := p_set.PARAMETER5_VALUE;
347 l_Param_Set_rec.PARAMETER6_VALUE := p_set.PARAMETER6_VALUE;
348 l_Param_Set_rec.PARAMETER7_VALUE := p_set.PARAMETER7_VALUE;
349 l_Param_Set_rec.NOTIFY_OWNER_FLAG := p_set.NOTIFY_OWNER_FLAG;
350 /*
351 BIS_UTILITIES_PUB.put_line(p_text =>'Registeration id: '||l_Param_Set_rec.REGISTRATION_ID
352 ||', Measure id: '||l_Param_Set_rec.PERFORMANCE_MEASURE_ID
353 ||', Target Level id: '||l_Param_Set_rec.target_LEVEL_ID
354 ||', Time level id: '||l_Param_Set_rec.TIME_DIMENSION_LEVEL_ID
355 ||', Notifier: '||l_Param_Set_rec.NOTIFIERS_CODE
356 );
357 */
358 x_Param_Set_Tbl(x_Param_Set_Tbl.COUNT+1) := l_Param_Set_rec;
359 end loop;
360
361 IF parameter_set_list%ISOPEN THEN close parameter_set_list; END IF;
362 BIS_UTILITIES_PUB.put_line(p_text =>'Number of Parameter_sets retrieved: '||x_Param_Set_Tbl.COUNT);
363
364 EXCEPTION
365 when FND_API.G_EXC_ERROR then
366 IF parameter_set_list%ISOPEN THEN close parameter_set_list; END IF;
367 x_return_status := FND_API.G_RET_STS_ERROR ;
368 when FND_API.G_EXC_UNEXPECTED_ERROR then
369 IF parameter_set_list%ISOPEN THEN close parameter_set_list; END IF;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
371 when others then
372 IF parameter_set_list%ISOPEN THEN close parameter_set_list; END IF;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
374 l_error_tbl := x_error_Tbl;
375 BIS_UTILITIES_PVT.Add_Error_Message
376 ( p_error_msg_id => SQLCODE
377 , p_error_description => SQLERRM
378 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Parameter_set'
379 , p_error_table => l_error_tbl
380 , x_error_table => x_error_tbl
381 );
382
383 END Retrieve_Parameter_set;
384
385 --
386 -- Retrieve a Table of parmeter set for the specified
387 -- values in the parameter set record.
388 --
389 PROCEDURE Retrieve_Parameter_set
390 ( p_api_version IN NUMBER
391 , p_Param_Set_Rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
392 , p_current_row IN VARCHAR2 := NULL
393 , x_Param_Set_Tbl OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
394 , x_return_status OUT NOCOPY VARCHAR2
395 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
396 )
397 IS
398
399 l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
400 l_Param_Set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
401 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
402 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
403
404 Cursor parameter_set_reg is
405 select
406 REGISTRATION_ID
407 , PERFORMANCE_MEASURE_ID
408 , TARGET_LEVEL_ID
409 , TIME_DIMENSION_LEVEL_ID
410 , PLAN_ID
411 , NOTIFIERS_CODE
412 , PARAMETER1_VALUE
413 , PARAMETER2_VALUE
414 , PARAMETER3_VALUE
415 , PARAMETER4_VALUE
416 , PARAMETER5_VALUE
417 , PARAMETER6_VALUE
418 , PARAMETER7_VALUE
419 , NOTIFY_OWNER_FLAG
420 from
421 BIS_PMF_ALERT_PARAMETERS
422 where REGISTRATION_ID = p_Param_Set_Rec.REGISTRATION_ID;
423
424 Cursor parameter_set_tl is
425 select
426 REGISTRATION_ID
427 , PERFORMANCE_MEASURE_ID
428 , TARGET_LEVEL_ID
429 , TIME_DIMENSION_LEVEL_ID
430 , PLAN_ID
431 , NOTIFIERS_CODE
432 , PARAMETER1_VALUE
433 , PARAMETER2_VALUE
434 , PARAMETER3_VALUE
435 , PARAMETER4_VALUE
436 , PARAMETER5_VALUE
437 , PARAMETER6_VALUE
438 , PARAMETER7_VALUE
439 , NOTIFY_OWNER_FLAG
440 from
441 BIS_PMF_ALERT_PARAMETERS
442 where TARGET_LEVEL_ID = p_Param_Set_Rec.TARGET_LEVEL_ID
443 and PLAN_ID = p_Param_Set_Rec.PLAN_ID
444 -- and ( (TIME_DIMENSION_LEVEL_ID IS NULL
445 -- and p_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID IS NULL)
446 -- or (TIME_DIMENSION_LEVEL_ID = p_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID))
447 and ( (PARAMETER1_VALUE is NULL
448 and P_Param_Set_Rec.PARAMETER1_VALUE IS NULL)
449 or (PARAMETER1_VALUE = P_Param_Set_Rec.PARAMETER1_VALUE))
450 and ((PARAMETER2_VALUE is NULL
451 and P_Param_Set_Rec.PARAMETER2_VALUE IS NULL)
452 or (PARAMETER2_VALUE = P_Param_Set_Rec.PARAMETER2_VALUE))
453 and ( (PARAMETER3_VALUE is NULL
454 and P_Param_Set_Rec.PARAMETER3_VALUE IS NULL)
455 or(PARAMETER3_VALUE = P_Param_Set_Rec.PARAMETER3_VALUE))
456 and ((PARAMETER4_VALUE is NULL
457 and P_Param_Set_Rec.PARAMETER4_VALUE IS NULL)
458 or (PARAMETER4_VALUE = P_Param_Set_Rec.PARAMETER4_VALUE))
459 and ((PARAMETER5_VALUE is NULL
460 and P_Param_Set_Rec.PARAMETER5_VALUE IS NULL)
461 or (PARAMETER5_VALUE = P_Param_Set_Rec.PARAMETER5_VALUE))
462 and ((PARAMETER6_VALUE is NULL
463 and P_Param_Set_Rec.PARAMETER6_VALUE IS NULL)
464 or (PARAMETER6_VALUE = P_Param_Set_Rec.PARAMETER6_VALUE))
465 and ((PARAMETER7_VALUE is NULL
466 and P_Param_Set_Rec.PARAMETER7_VALUE IS NULL)
467 or (PARAMETER7_VALUE = P_Param_Set_Rec.PARAMETER7_VALUE));
468
469 BEGIN
470
471 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving parameter set by parameter set record');
472 IF p_Param_Set_Rec.REGISTRATION_ID IS NOT NULL THEN
473 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving alert parameter set based on registration id');
474 FOR p_set IN parameter_set_reg
475 LOOP
476 l_Param_Set_rec.REGISTRATION_ID := p_set.REGISTRATION_ID;
477 l_Param_Set_rec.PERFORMANCE_MEASURE_ID := p_set.PERFORMANCE_MEASURE_ID;
478 l_Param_Set_rec.TARGET_LEVEL_ID := p_set.TARGET_LEVEL_ID;
479 l_Param_Set_rec.TIME_DIMENSION_LEVEL_ID
480 := p_set.TIME_DIMENSION_LEVEL_ID;
481 l_Param_Set_rec.PLAN_ID := p_set.PLAN_ID;
482 l_Param_Set_rec.NOTIFIERS_CODE := p_set.NOTIFIERS_CODE;
483 l_Param_Set_rec.PARAMETER1_VALUE := p_set.PARAMETER1_VALUE;
484 l_Param_Set_rec.PARAMETER2_VALUE := p_set.PARAMETER2_VALUE;
485 l_Param_Set_rec.PARAMETER3_VALUE := p_set.PARAMETER3_VALUE;
486 l_Param_Set_rec.PARAMETER4_VALUE := p_set.PARAMETER4_VALUE;
487 l_Param_Set_rec.PARAMETER5_VALUE := p_set.PARAMETER5_VALUE;
488 l_Param_Set_rec.PARAMETER6_VALUE := p_set.PARAMETER6_VALUE;
489 l_Param_Set_rec.PARAMETER7_VALUE := p_set.PARAMETER7_VALUE;
490 l_Param_Set_rec.NOTIFY_OWNER_FLAG := p_set.NOTIFY_OWNER_FLAG;
491 l_Param_Set_Tbl(l_Param_Set_Tbl.COUNT+1) := l_Param_Set_rec;
492 END LOOP;
493 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieved '||l_Param_Set_Tbl.COUNT||' parameter sets');
494
495 ELSIF p_Param_Set_Rec.TARGET_LEVEL_ID IS NOT NULL THEN
496 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving alert parameter set based on target level');
497 FOR P_set IN parameter_set_tl
498 LOOP
499 l_Param_Set_rec.REGISTRATION_ID := p_set.REGISTRATION_ID;
500 l_Param_Set_rec.PERFORMANCE_MEASURE_ID := p_set.PERFORMANCE_MEASURE_ID;
501 l_Param_Set_rec.TARGET_LEVEL_ID := p_set.TARGET_LEVEL_ID;
502 l_Param_Set_rec.TIME_DIMENSION_LEVEL_ID
503 := p_set.TIME_DIMENSION_LEVEL_ID;
504 l_Param_Set_rec.PLAN_ID := p_set.PLAN_ID;
505 l_Param_Set_rec.NOTIFIERS_CODE := p_set.NOTIFIERS_CODE;
506 l_Param_Set_rec.PARAMETER1_VALUE := p_set.PARAMETER1_VALUE;
507 l_Param_Set_rec.PARAMETER2_VALUE := p_set.PARAMETER2_VALUE;
508 l_Param_Set_rec.PARAMETER3_VALUE := p_set.PARAMETER3_VALUE;
509 l_Param_Set_rec.PARAMETER4_VALUE := p_set.PARAMETER4_VALUE;
510 l_Param_Set_rec.PARAMETER5_VALUE := p_set.PARAMETER5_VALUE;
511 l_Param_Set_rec.PARAMETER6_VALUE := p_set.PARAMETER6_VALUE;
512 l_Param_Set_rec.PARAMETER7_VALUE := p_set.PARAMETER7_VALUE;
513 l_Param_Set_rec.NOTIFY_OWNER_FLAG := p_set.NOTIFY_OWNER_FLAG;
514 l_Param_Set_Tbl(l_Param_Set_Tbl.COUNT+1) := l_Param_Set_rec;
515 END LOOP;
516 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieved '||l_Param_Set_Tbl.COUNT||' parameter sets');
517
518 ELSIF p_Param_Set_rec.PERFORMANCE_MEASURE_ID IS NOT NULL
519 AND p_Param_Set_rec.TIME_DIMENSION_LEVEL_ID IS NOT NULL
520 THEN
521 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving alert parameter set based on performance measure');
522 Retrieve_Parameter_set
523 ( p_api_version => 1.0
524 , p_measure_id => p_Param_Set_rec.PERFORMANCE_MEASURE_ID
525 , p_time_dimension_level_id => p_Param_Set_rec.TIME_DIMENSION_LEVEL_ID
526 , P_current_row => p_current_row
527 , x_Param_Set_tbl => l_param_set_tbl
528 , x_return_status => x_return_status
529 , x_error_Tbl => x_error_Tbl
530 );
531 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieved '||l_Param_Set_Tbl.COUNT||' parameter sets');
532 --x_param_set_tbl := l_param_set_tbl;
533 --RETURN;
534 END IF;
535
536 x_Param_Set_Tbl := l_Param_Set_tbl;
537
538 EXCEPTION
539 when FND_API.G_EXC_ERROR then
540 x_return_status := FND_API.G_RET_STS_ERROR ;
541 when FND_API.G_EXC_UNEXPECTED_ERROR then
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
543 when others then
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
545 l_error_tbl := x_error_Tbl;
546 BIS_UTILITIES_PVT.Add_Error_Message
547 ( p_error_msg_id => SQLCODE
548 , p_error_description => SQLERRM
549 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Parameter_set'
550 , p_error_table => l_error_tbl
551 , x_error_table => x_error_tbl
552 );
553
554 END Retrieve_Parameter_set;
555
556 PROCEDURE Retrieve_Notifiers_Code
557 ( p_api_version IN NUMBER
558 , p_performance_measure_id IN NUMBER := NULL
559 , p_target_level_id IN NUMBER := NULL
560 , p_time_dimension_level_id IN NUMBER := NULL
561 , p_plan_id IN NUMBER := NULL
562 , p_parameter1_value IN VARCHAR2 := NULL
563 , p_parameter2_value IN VARCHAR2 := NULL
564 , p_parameter3_value IN VARCHAR2 := NULL
565 , p_parameter4_value IN VARCHAR2 := NULL
566 , p_parameter5_value IN VARCHAR2 := NULL
567 , p_parameter6_value IN VARCHAR2 := NULL
568 , p_parameter7_value IN VARCHAR2 := NULL
569 , p_current_row IN VARCHAR2 := NULL
570 , x_Notifiers_Code OUT NOCOPY VARCHAR2
571 , x_return_status OUT NOCOPY VARCHAR2
572 )
573 IS
574
575 l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
576 l_Param_Set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
577 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
578 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
579
580 BEGIN
581
582 l_Param_Set_rec.performance_measure_id := p_performance_measure_id;
583 l_Param_Set_rec.target_level_id := p_target_level_id;
584 l_Param_Set_rec.time_dimension_level_id := p_time_dimension_level_id;
585 l_Param_Set_rec.plan_id := p_plan_id;
586 l_Param_Set_rec.parameter1_value := p_parameter1_value;
587 l_Param_Set_rec.parameter2_value := p_parameter2_value;
588 l_Param_Set_rec.parameter3_value := p_parameter3_value;
589 l_Param_Set_rec.parameter4_value := p_parameter4_value;
590 l_Param_Set_rec.parameter5_value := p_parameter5_value;
591 l_Param_Set_rec.parameter6_value := p_parameter6_value;
592 l_Param_Set_rec.parameter7_value := p_parameter7_value;
593
594 Retrieve_Notifiers_Code
595 ( p_api_version => p_api_version
596 , p_Param_Set_rec => l_Param_Set_rec
597 , x_Notifiers_Code => x_Notifiers_Code
598 , x_return_status => x_return_status
599 );
600
601 EXCEPTION
602 when FND_API.G_EXC_ERROR then
603 x_return_status := FND_API.G_RET_STS_ERROR ;
604 x_return_status := 'exception 1 at Retrieve_Notifiers_Code: '||sqlerrm;
605 when FND_API.G_EXC_UNEXPECTED_ERROR then
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
607 x_return_status := 'exception 2 at Retrieve_Notifiers_Code: '||sqlerrm;
608 when others then
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610 x_return_status := 'exception 3 at Retrieve_Notifiers_Code: '||sqlerrm;
611 l_error_tbl_p := l_error_tbl;
612 BIS_UTILITIES_PVT.Add_Error_Message
613 ( p_error_msg_id => SQLCODE
614 , p_error_description => SQLERRM
615 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Notifiers_Code'
616 , p_error_table => l_error_tbl_p
617 , x_error_table => l_error_tbl
618 );
619
620 END Retrieve_Notifiers_Code;
621
622 PROCEDURE Retrieve_Notifiers_Code
623 ( p_api_version IN NUMBER
624 , p_Param_Set_rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
625 , x_Notifiers_Code OUT NOCOPY VARCHAR2
626 , x_return_status OUT NOCOPY VARCHAR2
627 )
628 IS
629
630 l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
631 l_Param_Set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
632 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
633 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
634
635 BEGIN
636
637 Retrieve_Parameter_set
638 ( p_api_version => p_api_version
639 , p_Param_Set_Rec => p_param_set_rec
640 , x_Param_Set_Tbl => l_Param_Set_Tbl
641 , x_return_status => x_return_status
642 , x_error_Tbl => l_error_Tbl
643 );
644
645 IF l_Param_Set_tbl.COUNT >= 1 THEN
646 x_notifiers_code := l_Param_Set_tbl(1).notifiers_code;
647 BIS_UTILITIES_PUB.put_line(p_text =>'Notifier code retrieved: '||x_notifiers_code);
648 ELSE
649 BIS_UTILITIES_PUB.put_line(p_text =>'Notifier code not retrieved.');
650 x_notifiers_code := null;
651 END IF;
652
653 EXCEPTION
654 when others then
655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
656 x_return_status := 'exception 1 at Retrieve_Notifiers_Code: '||sqlerrm;
657 BIS_UTILITIES_PUB.put_line(p_text =>x_return_status);
658 l_error_tbl_p := l_error_tbl;
659 BIS_UTILITIES_PVT.Add_Error_Message
660 ( p_error_msg_id => SQLCODE
661 , p_error_description => SQLERRM
662 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Notifiers_Code'
663 , p_error_table => l_error_tbl_p
664 , x_error_table => l_error_tbl
665 );
666
667 END Retrieve_Notifiers_Code;
668
669 --
670 -- Checks if request is scheduled to run again. If not, the request
671 -- is deleted from the Registration table and the ad hoc workflow role
672 -- is removed.
673 --
674 PROCEDURE Manage_Alert_Registrations
675 ( p_Param_Set_Tbl IN BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
676 , x_request_scheduled OUT NOCOPY VARCHAR2
677 , x_return_status OUT NOCOPY VARCHAR2
678 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
679 )
680 IS
681
682 l_return_status VARCHAR2(32000);
683 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
684
685 BEGIN
686
687 FOR i IN 1..p_Param_Set_Tbl.COUNT LOOP
688 Manage_Alert_Registrations
689 ( p_Param_Set_rec => p_Param_Set_Tbl(i)
690 , x_request_scheduled => x_request_scheduled
691 , x_return_status => l_return_status
692 , x_error_Tbl => l_error_Tbl
693 );
694 END LOOP;
695
696 EXCEPTION
697 when FND_API.G_EXC_ERROR then
698 x_return_status := FND_API.G_RET_STS_ERROR ;
699 when FND_API.G_EXC_UNEXPECTED_ERROR then
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
701 when others then
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
703 l_error_tbl := x_error_tbl;
704 BIS_UTILITIES_PVT.Add_Error_Message
705 ( p_error_msg_id => SQLCODE
706 , p_error_description => SQLERRM
707 , p_error_proc_name => G_PKG_NAME||'.Manage_Alert_Registrations'
708 , p_error_table => l_error_tbl
709 , x_error_table => x_error_tbl
710 );
711
712 END Manage_Alert_Registrations;
713
714 --
715 -- Checks if request is scheduled to run again. If not, the request
716 -- is deleted from the Registration table and the ad hoc workflow role
717 -- is removed.
718 --
719 PROCEDURE Manage_Alert_Registrations
720 ( p_Param_Set_rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
721 , x_request_scheduled OUT NOCOPY VARCHAR2
722 , x_return_status OUT NOCOPY VARCHAR2
723 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
724 )
725 IS
726 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
727 BEGIN
728
729 BIS_CONCURRENT_MANAGER_PVT.Manage_Alert_Registrations
730 ( p_Param_Set_rec => p_Param_Set_rec
731 , x_request_scheduled => x_request_scheduled
732 , x_return_status => x_return_status
733 , x_error_Tbl => x_error_Tbl
734 );
735
736 EXCEPTION
737 when FND_API.G_EXC_ERROR then
738 x_return_status := FND_API.G_RET_STS_ERROR ;
739 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 in Manage_Alert_Registrations: '||sqlerrm);
740 when FND_API.G_EXC_UNEXPECTED_ERROR then
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 in Manage_Alert_Registrations: '||sqlerrm);
743 when others then
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
745 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 in Manage_Alert_Registrations: '||sqlerrm);
746 l_error_tbl := x_error_Tbl;
747 BIS_UTILITIES_PVT.Add_Error_Message
748 ( p_error_msg_id => SQLCODE
749 , p_error_description => SQLERRM
750 , p_error_proc_name => G_PKG_NAME||'.Manage_Alert_Registrations'
751 , p_error_table => l_error_tbl
752 , x_error_table => x_error_tbl
753 );
754 END Manage_Alert_Registrations;
755
756
757 PROCEDURE Manage_Alert_Registrations
758 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
759 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
760 , x_request_scheduled OUT NOCOPY VARCHAR2
761 , x_return_status OUT NOCOPY VARCHAR2
762 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
763 )
764 IS
765
766 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
767 l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
768 l_request_scheduled VARCHAR2(1000);
769
770 BEGIN
771
772 BIS_UTILITIES_PUB.put_line(p_text =>'Managing alert registrations. ');
773 BIS_UTILITIES_PUB.put_line(p_text =>'Measure: '||p_measure_instance.measure_id
774 ||', target level: '||p_measure_instance.Target_Level_ID);
775
776 Form_Param_Set_Rec
777 ( p_measure_instance => p_measure_instance
778 , p_dim_level_value_tbl => p_dim_level_value_tbl
779 , x_Param_Set_Rec => l_Param_Set_Rec
780 );
781 l_Param_Set_tbl(l_Param_Set_tbl.COUNT+1) := l_Param_Set_Rec;
782
783 Manage_Alert_Registrations
784 ( p_Param_Set_Tbl => l_Param_Set_Tbl
785 , x_request_scheduled => x_request_scheduled
786 , x_return_status => x_return_status
787 , x_error_Tbl => x_error_Tbl
788 );
789
790 END Manage_Alert_Registrations;
791
792 Procedure Form_Param_Set_Rec
793 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
794 , x_Param_Set_Rec OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
795 )
796 IS
797
798 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
799 l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
800 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
801 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
802 l_dimension_level_number NUMBER;
803 l_return_status VARCHAR2(32000);
804 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
805
806 BEGIN
807
808 l_Target_Level_Rec.target_level_id := p_measure_instance.target_level_id;
809
810 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
811 ( p_api_version => 1.0
812 , p_Target_Level_Rec => l_Target_Level_Rec
813 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
814 , x_dimension_level_number => l_dimension_level_number
815 , x_return_status => l_return_status
816 , x_error_Tbl => l_error_Tbl
817 );
818
819 l_Param_Set_Rec.PERFORMANCE_MEASURE_ID := p_measure_instance.measure_id;
820 l_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID
821 := l_Dimension_Level_Rec.Dimension_Level_id;
822
823 x_Param_Set_Rec := l_Param_Set_Rec;
824
825 END Form_Param_Set_Rec;
826
827 Procedure Form_Param_Set_Rec
828 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
829 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
830 , x_Param_Set_Rec OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
831 )
832 IS
833
834 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
835 l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
836 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
837 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
838 l_dimension_level_number NUMBER;
839 l_return_status VARCHAR2(32000);
840 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
841
842 BEGIN
843
844 l_Target_Level_Rec.target_level_id := p_measure_instance.target_level_id;
845
846 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
847 ( p_api_version => 1.0
848 , p_Target_Level_Rec => l_Target_Level_Rec
849 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
850 , x_dimension_level_number => l_dimension_level_number
851 , x_return_status => l_return_status
852 , x_error_Tbl => l_error_Tbl
853 );
854
855 l_Param_Set_Rec.PERFORMANCE_MEASURE_ID := p_measure_instance.measure_id;
856 l_Param_Set_Rec.TARGET_LEVEL_ID := p_measure_instance.target_level_id;
857 l_Param_Set_Rec.TIME_DIMENSION_LEVEL_ID
858 := l_Dimension_Level_Rec.Dimension_Level_id;
859 l_Param_Set_Rec.PLAN_ID := p_measure_instance.plan_id;
860 l_Param_Set_Rec.PARAMETER1_VALUE
861 := p_dim_level_value_tbl(1).dimension_level_value_id;
862 l_Param_Set_Rec.PARAMETER2_VALUE
863 := p_dim_level_value_tbl(2).dimension_level_value_id;
864 l_Param_Set_Rec.PARAMETER3_VALUE
865 := p_dim_level_value_tbl(3).dimension_level_value_id;
866 l_Param_Set_Rec.PARAMETER4_VALUE
867 := p_dim_level_value_tbl(4).dimension_level_value_id;
868 l_Param_Set_Rec.PARAMETER5_VALUE
869 := p_dim_level_value_tbl(5).dimension_level_value_id;
870 l_Param_Set_Rec.PARAMETER6_VALUE
871 := p_dim_level_value_tbl(6).dimension_level_value_id;
872 l_Param_Set_Rec.PARAMETER7_VALUE
873 := p_dim_level_value_tbl(7).dimension_level_value_id;
874
875 x_Param_Set_Rec := l_Param_Set_Rec;
876
877 END Form_Param_Set_Rec;
878
879 --
880 -- Function which will return a boolean varible, if parameter set exist
881 -- and will also return the notifiers_code
882 --
883 FUNCTION Parameter_set_exist
884 ( p_api_version IN NUMBER
885 , p_Param_Set_Rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
886 , x_notifiers_code OUT NOCOPY VARCHAR2
887 , x_return_status OUT NOCOPY VARCHAR2
888 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
889 ) return boolean
890 IS
891
892 l_Param_Set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
893 l_p_exist boolean;
894 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
895
896 BEGIN
897
898 Retrieve_Parameter_set
899 ( p_api_version => p_api_version
900 , p_Param_Set_Rec => p_param_set_rec
901 , x_Param_Set_Tbl => l_Param_Set_Tbl
902 , x_return_status => x_return_status
903 , x_error_Tbl => x_error_Tbl
904 );
905 IF l_Param_Set_Tbl.COUNT >= 1 THEN
906 l_p_exist := TRUE;
907 ELSE
908 l_p_exist := FALSE;
909 END IF;
910
911 return l_p_exist;
912
913 EXCEPTION
914 when FND_API.G_EXC_ERROR then
915 x_return_status := FND_API.G_RET_STS_ERROR ;
916 x_return_status := ' exception 1 at Parameter_set_exist '||sqlerrm;
917 return FALSE;
918 when FND_API.G_EXC_UNEXPECTED_ERROR then
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
920 x_return_status := ' exception 2 at Parameter_set_exist '||sqlerrm;
921 return FALSE;
922 when others then
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
924 x_return_status := ' exception 3 at Parameter_set_exist '||sqlerrm;
925 return FALSE;
926 l_error_tbl := x_error_Tbl;
927 BIS_UTILITIES_PVT.Add_Error_Message
928 ( p_error_msg_id => SQLCODE
929 , p_error_description => SQLERRM
930 , p_error_proc_name => G_PKG_NAME||'.Parameter_set_exist'
931 , p_error_table => l_error_tbl
932 , x_error_table => x_error_tbl
933 );
934
935 END Parameter_set_exist;
936
937 --
938 -- Validates target record
939 --
940 PROCEDURE Validate_Parameter_set
941 ( p_api_version IN NUMBER
942 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
943 , p_Param_Set_Rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
944 , x_return_status OUT NOCOPY VARCHAR2
945 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
946 )
947 IS
948 --
949 l_return_status VARCHAR2(10);
950 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
951 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
952 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_level_Rec_Type;
953 l_measure_rec BIS_MEASURE_PUB.measure_rec_type;
954 --
955 l_bisbv_target_levels BIS_TARGET_LEVEL_PUB.Target_level_Rec_Type;
956 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
957 l_measure_rec_p BIS_MEASURE_PUB.measure_rec_type;
958 l_Dim_Level_Value_Rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
959 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
960 --
961 BEGIN
962 --
963 x_return_status := FND_API.G_RET_STS_SUCCESS;
964
965 l_measure_rec.measure_id := p_param_Set_Rec.performance_measure_id;
966
967 -- Calling the retrieve_measure to validate the measure_id
968
969 BEGIN
970 l_measure_rec_p := l_measure_rec;
971 BIS_MEASURE_PUB.Retrieve_Measure
972 ( p_api_version => p_api_version
973 , p_measure_rec => l_measure_rec_p
974 , p_all_info => FND_API.G_FALSE
975 , x_Measure_rec => l_measure_rec
976 , x_return_status => l_return_status
977 , x_error_Tbl => x_error_tbl
978 );
979
980 EXCEPTION
981 WHEN FND_API.G_EXC_ERROR THEN
982 x_return_status:= FND_API.G_RET_STS_ERROR;
983 RAISE;
984 END;
985
986 --
987 -- Calling the retrieve_target_level to validate the target_level_id
988 -- and the out NOCOPY rec is used for validating the dimension level values
989
990 l_target_rec.Target_Level_ID := p_param_Set_Rec.target_level_id;
991 l_target_rec.Plan_ID := p_param_Set_Rec.Plan_id;
992 l_target_level_rec.Target_Level_ID := p_param_Set_Rec.target_level_id;
993
994 IF p_param_Set_Rec.Plan_id IS NOT NULL THEN
995 BEGIN
996 BIS_TARGET_VALIDATE_PVT.Validate_Plan_ID
997 ( p_api_version => p_api_version
998 , p_Target_Rec => l_Target_Rec
999 , x_return_status => l_return_status
1000 , x_error_Tbl => l_error_Tbl
1001 );
1002 --
1003 EXCEPTION
1004 WHEN FND_API.G_EXC_ERROR THEN
1005 x_return_status:= FND_API.G_RET_STS_ERROR;
1006 l_error_tbl_p := x_error_Tbl;
1007 BIS_UTILITIES_PVT.concatenateErrorTables
1008 ( p_error_Tbl1 => l_error_Tbl_p
1009 , p_error_Tbl2 => l_error_Tbl
1010 , x_error_Tbl => x_error_Tbl
1011 );
1012 RAISE;
1013 END;
1014 END IF;
1015
1016 IF p_param_Set_Rec.target_level_id IS NOT NULL THEN
1017 BEGIN
1018 BIS_Target_Level_PUB.Retrieve_Target_Level
1019 ( p_api_version => p_api_version
1020 , p_Target_Level_Rec => l_target_level_rec
1021 , p_all_info => FND_API.G_FALSE
1022 , x_Target_Level_Rec => l_bisbv_target_levels
1023 , x_return_status => l_return_status
1024 , x_error_Tbl => x_error_Tbl
1025 );
1026 --
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 x_return_status:= FND_API.G_RET_STS_ERROR;
1030 RAISE;
1031 END;
1032 --
1033
1034
1035 BEGIN
1036 IF(l_bisbv_target_levels.ORG_LEVEL_ID IS NOT NULL) THEN
1037 l_Dim_Level_Value_Rec.Dimension_Level_ID
1038 := l_bisbv_target_levels.ORG_LEVEL_ID;
1039
1040 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1041 := p_Param_Set_Rec.parameter1_value;
1042 --
1043 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1044 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1045 ( p_api_version => p_api_version
1046 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1047 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1048 , x_return_status => l_return_status
1049 , x_error_Tbl => l_error_Tbl
1050 );
1051 END IF;
1052 --
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 x_return_status:= FND_API.G_RET_STS_ERROR;
1056 l_error_tbl_p := x_error_Tbl;
1057 BIS_UTILITIES_PVT.concatenateErrorTables
1058 ( p_error_Tbl1 => l_error_Tbl_p
1059 , p_error_Tbl2 => l_error_Tbl
1060 , x_error_Tbl => x_error_Tbl
1061 );
1062 RAISE;
1063 END;
1064 --
1065 BEGIN
1066 IF(l_bisbv_target_levels.TIME_LEVEL_ID IS NOT NULL) THEN
1067 l_Dim_Level_Value_Rec.Dimension_Level_ID
1068 := l_bisbv_target_levels.TIME_LEVEL_ID;
1069 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1070 := p_Param_Set_Rec.parameter2_value;
1071
1072 --
1073 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1074 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1075 ( p_api_version => p_api_version
1076 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1077 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1078 , x_return_status => l_return_status
1079 , x_error_Tbl => l_error_Tbl
1080 );
1081 END IF;
1082 --
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 x_return_status:= FND_API.G_RET_STS_ERROR;
1086 l_error_tbl_p := x_error_Tbl;
1087 BIS_UTILITIES_PVT.concatenateErrorTables
1088 ( p_error_Tbl1 => l_error_Tbl_p
1089 , p_error_Tbl2 => l_error_Tbl
1090 , x_error_Tbl => x_error_Tbl
1091 );
1092 RAISE;
1093 END;
1094 --
1095 BEGIN
1096 IF(l_bisbv_target_levels.DIMENSION1_LEVEL_ID IS NOT NULL) THEN
1097
1098 l_Dim_Level_Value_Rec.Dimension_Level_ID
1099 := l_bisbv_target_levels.DIMENSION1_LEVEL_ID;
1100
1101 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1102 := p_Param_Set_Rec.parameter3_value;
1103 --
1104 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1105 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1106 ( p_api_version => p_api_version
1107 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1108 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1109 , x_return_status => l_return_status
1110 , x_error_Tbl => l_error_Tbl
1111 );
1112 END IF;
1113 --
1114 EXCEPTION
1115 WHEN FND_API.G_EXC_ERROR THEN
1116 x_return_status:= FND_API.G_RET_STS_ERROR;
1117 l_error_tbl_p := x_error_Tbl;
1118 BIS_UTILITIES_PVT.concatenateErrorTables
1119 ( p_error_Tbl1 => l_error_Tbl_p
1120 , p_error_Tbl2 => l_error_Tbl
1121 , x_error_Tbl => x_error_Tbl
1122 );
1123 RAISE;
1124 END;
1125 --
1126 BEGIN
1127 IF(l_bisbv_target_levels.DIMENSION2_LEVEL_ID IS NOT NULL) THEN
1128
1129 l_Dim_Level_Value_Rec.Dimension_Level_ID
1130 := l_bisbv_target_levels.DIMENSION2_LEVEL_ID;
1131
1132 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1133 := p_Param_Set_Rec.parameter4_value;
1134
1135 --
1136 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1137 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1138 ( p_api_version => p_api_version
1139 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1140 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1141 , x_return_status => l_return_status
1142 , x_error_Tbl => l_error_Tbl
1143 );
1144 END IF;
1145 --
1146 EXCEPTION
1147 WHEN FND_API.G_EXC_ERROR THEN
1148 x_return_status:= FND_API.G_RET_STS_ERROR;
1149 l_error_tbl_p := x_error_Tbl;
1150 BIS_UTILITIES_PVT.concatenateErrorTables
1151 ( p_error_Tbl1 => l_error_Tbl_p
1152 , p_error_Tbl2 => l_error_Tbl
1153 , x_error_Tbl => x_error_Tbl
1154 );
1155 RAISE;
1156 END;
1157 --
1158 BEGIN
1159 IF(l_bisbv_target_levels.DIMENSION3_LEVEL_ID IS NOT NULL) THEN
1160
1161 l_Dim_Level_Value_Rec.Dimension_Level_ID
1162 := l_bisbv_target_levels.DIMENSION3_LEVEL_ID;
1163
1164 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1165 := p_Param_Set_Rec.parameter5_value;
1166 --
1167 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1168 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1169 ( p_api_version => p_api_version
1170 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1171 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1172 , x_return_status => l_return_status
1173 , x_error_Tbl => l_error_Tbl
1174 );
1175
1176 END IF;
1177 --
1178 EXCEPTION
1179 WHEN FND_API.G_EXC_ERROR THEN
1180 x_return_status:= FND_API.G_RET_STS_ERROR;
1181 l_error_tbl_p := x_error_Tbl;
1182 BIS_UTILITIES_PVT.concatenateErrorTables
1183 ( p_error_Tbl1 => l_error_Tbl_p
1184 , p_error_Tbl2 => l_error_Tbl
1185 , x_error_Tbl => x_error_Tbl
1186 );
1187 RAISE;
1188 END;
1189 --
1190 BEGIN
1191 IF(l_bisbv_target_levels.DIMENSION4_LEVEL_ID IS NOT NULL) THEN
1192
1193 l_Dim_Level_Value_Rec.Dimension_Level_ID
1194 := l_bisbv_target_levels.DIMENSION4_LEVEL_ID;
1195
1196 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1197 := p_Param_Set_Rec.parameter6_value;
1198
1199 --
1200 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1201 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1202 ( p_api_version => p_api_version
1203 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1204 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1205 , x_return_status => l_return_status
1206 , x_error_Tbl => l_error_Tbl
1207 );
1208
1209 END IF;
1210 --
1211 EXCEPTION
1212 WHEN FND_API.G_EXC_ERROR THEN
1213 x_return_status:= FND_API.G_RET_STS_ERROR;
1214 l_error_tbl_p := x_error_Tbl;
1215 BIS_UTILITIES_PVT.concatenateErrorTables
1216 ( p_error_Tbl1 => l_error_Tbl_p
1217 , p_error_Tbl2 => l_error_Tbl
1218 , x_error_Tbl => x_error_Tbl
1219 );
1220 RAISE;
1221 END;
1222 --
1223 BEGIN
1224 IF(l_bisbv_target_levels.DIMENSION5_LEVEL_ID IS NOT NULL) THEN
1225
1226 l_Dim_Level_Value_Rec.Dimension_Level_ID
1227 := l_bisbv_target_levels.DIMENSION5_LEVEL_ID;
1228
1229 l_Dim_Level_Value_Rec.Dimension_Level_Value_ID
1230 := p_Param_Set_Rec.parameter7_value;
1231
1232 --
1233 l_Dim_Level_Value_Rec_p := l_Dim_Level_Value_Rec;
1234 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value
1235 ( p_api_version => p_api_version
1236 , p_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec_p
1237 , x_Dim_Level_Value_Rec => l_Dim_Level_Value_Rec
1238 , x_return_status => l_return_status
1239 , x_error_Tbl => l_error_Tbl
1240 );
1241 END IF;
1242 --
1243 EXCEPTION
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 x_return_status:= FND_API.G_RET_STS_ERROR;
1246 l_error_tbl_p := x_error_Tbl;
1247 BIS_UTILITIES_PVT.concatenateErrorTables
1248 ( p_error_Tbl1 => l_error_Tbl_p
1249 , p_error_Tbl2 => l_error_Tbl
1250 , x_error_Tbl => x_error_Tbl
1251 );
1252 RAISE;
1253 END;
1254
1255 end if;
1256 --
1257 x_return_status := l_return_status;
1258
1259 --
1260 EXCEPTION
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 x_return_status:= FND_API.G_RET_STS_ERROR;
1263 RAISE;
1264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1266 RAISE;
1267 WHEN OTHERS THEN
1268 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1269 l_error_tbl_p := x_error_Tbl;
1270 BIS_UTILITIES_PVT.Add_Error_Message
1271 ( p_error_table => l_error_Tbl_p
1272 , p_error_msg_id => SQLCODE
1273 , p_error_description => SQLERRM
1274 , x_error_table => x_error_Tbl
1275 );
1276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1277 END Validate_Parameter_Set;
1278 --
1279
1280 PROCEDURE BuildAlertRegistrationURL
1281 ( p_measure_id IN NUMBER
1282 , p_target_level_id IN NUMBER := NULL
1283 , p_plan_id IN VARCHAR2 := NULL
1284 , p_parameter1levelId IN NUMBER := NULL
1285 , p_parameter1ValueId IN VARCHAR2 := NULL
1286 , p_parameter2levelId IN NUMBER := NULL
1287 , p_parameter2ValueId IN VARCHAR2 := NULL
1288 , p_parameter3levelId IN NUMBER := NULL
1289 , p_parameter3ValueId IN VARCHAR2 := NULL
1290 , p_parameter4levelId IN NUMBER := NULL
1291 , p_parameter4ValueId IN VARCHAR2 := NULL
1292 , p_parameter5levelId IN NUMBER := NULL
1293 , p_parameter5ValueId IN VARCHAR2 := NULL
1294 , p_parameter6levelId IN NUMBER := NULL
1295 , p_parameter6ValueId IN VARCHAR2 := NULL
1296 , p_parameter7levelId IN NUMBER := NULL
1297 , p_parameter7ValueId IN VARCHAR2 := NULL
1298 , p_viewByLevelId IN VARCHAR2 := NULL
1299 , p_alertTip IN VARCHAR2 := NULL
1300 , p_returnPageUrl IN VARCHAR2 := NULL
1301 , x_alert_url OUT NOCOPY VARCHAR2
1302 )
1303 IS
1304 l_alert_url VARCHAR2(32000);
1305 l_dbc VARCHAR2(10000);
1306 l_servlet_agent VARCHAR2(10000);
1307 l_encrypted_session_id VARCHAR2(1000);
1308 l_session_id NUMBER;
1309
1310 BEGIN
1311
1312 l_session_id := icx_sec.getsessioncookie;
1313 l_encrypted_session_id
1314 := icx_call.encrypt3(icx_sec.getID(icx_Sec.PV_SESSION_ID));
1315 fnd_profile.get(name=>'APPS_SERVLET_AGENT',
1316 val => l_alert_url);
1317 l_alert_url := FND_WEB_CONFIG.trail_slash(l_alert_url) ||
1318 'bisalrpt.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
1319 || G_AMPERSAND ||'sessionid='|| l_encrypted_session_id;
1320
1321 IF (p_measure_id IS NOT NULL) THEN
1322 l_alert_url := l_alert_url || G_AMPERSAND || 'perfMeasureId='
1323 -- 2280993 starts
1324 -- || wfa_html.conv_special_url_chars(p_measure_id);
1325 || BIS_UTILITIES_PUB.encode(p_measure_id);
1326 -- 2280993 ends
1327 END IF;
1328 IF (p_plan_id IS NOT NULL) THEN
1329 l_alert_url := l_alert_url || G_AMPERSAND || 'planId='
1330 -- 2280993 starts
1331 -- || wfa_html.conv_special_url_chars(p_plan_id);
1332 || BIS_UTILITIES_PUB.encode(p_plan_id);
1333 -- 2280993 ends
1334 END IF;
1335 IF (p_target_level_id IS NOT NULL) THEN
1336 l_Alert_url := l_alert_url || G_AMPERSAND || 'targetLevelId='
1337 -- 2280993 starts
1338 -- ||wfa_html.conv_special_url_chars(p_target_level_id);
1339 ||BIS_UTILITIES_PUB.encode(p_target_level_id);
1340 -- 2280993 ends
1341 END IF;
1342 IF (p_parameter1levelId IS NOT NULL) THEN
1343 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter1LevelId='
1344 -- 2280993 starts
1345 -- ||wfa_html.conv_special_url_chars(p_parameter1levelId);
1346 ||BIS_UTILITIES_PUB.encode(p_parameter1levelId);
1347 -- 2280993 ends
1348 END IF;
1349 IF (p_parameter1ValueId IS NOT NULL) THEN
1350 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter1ValueId='
1351 -- 2280993 starts
1352 -- ||wfa_html.conv_special_url_chars(p_parameter1ValueId);
1353 ||BIS_UTILITIES_PUB.encode(p_parameter1ValueId);
1354 -- 2280993 ends
1355 END IF;
1356 IF (p_parameter2levelId IS NOT NULL) THEN
1357 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter2LevelId='
1358 -- 2280993 starts
1359 -- ||wfa_html.conv_special_url_chars(p_parameter2levelId);
1360 ||BIS_UTILITIES_PUB.encode(p_parameter2levelId);
1361 -- 2280993 ends
1362 END IF;
1363 IF (p_parameter2ValueId IS NOT NULL) THEN
1364 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter2ValueId='
1365 -- 2280993 starts
1366 -- ||wfa_html.conv_special_url_chars(p_parameter2ValueId);
1367 ||BIS_UTILITIES_PUB.encode(p_parameter2ValueId);
1368 -- 2280993 ends
1369 END IF;
1370 IF (p_parameter3levelId IS NOT NULL) THEN
1371 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter3LevelId='
1372 -- 2280993 starts
1373 -- ||wfa_html.conv_special_url_chars(p_parameter3levelId);
1374 ||BIS_UTILITIES_PUB.encode(p_parameter3levelId);
1375 -- 2280993 ends
1376 END IF;
1377 IF (p_parameter3ValueId IS NOT NULL) THEN
1378 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter3ValueId='
1379 -- 2280993 starts
1380 -- ||wfa_html.conv_special_url_chars(p_parameter3ValueId);
1381 ||BIS_UTILITIES_PUB.encode(p_parameter3ValueId);
1382 -- 2280993 ends
1383 END IF;
1384 IF (p_parameter4levelId IS NOT NULL) THEN
1385 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter4LevelId='
1386 -- 2280993 starts
1387 -- ||wfa_html.conv_special_url_chars(p_parameter4levelId);
1388 ||BIS_UTILITIES_PUB.encode(p_parameter4levelId);
1389 -- 2280993 ends
1390 END IF;
1391 IF (p_parameter4ValueId IS NOT NULL) THEN
1392 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter4ValueId='
1393 -- 2280993 starts
1394 -- ||wfa_html.conv_special_url_chars(p_parameter4ValueId);
1395 ||BIS_UTILITIES_PUB.encode(p_parameter4ValueId);
1396 -- 2280993 ends
1397 END IF;
1398 IF (p_parameter5levelId IS NOT NULL) THEN
1399 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter5LevelId='
1400 -- 2280993 starts
1401 -- ||wfa_html.conv_special_url_chars(p_parameter5levelId);
1402 ||BIS_UTILITIES_PUB.encode(p_parameter5levelId);
1403 -- 2280993 ends
1404 END IF;
1405 IF (p_parameter5ValueId IS NOT NULL) THEN
1406 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter5ValueId='
1407 -- 2280993 starts
1408 -- ||wfa_html.conv_special_url_chars(p_parameter5ValueId);
1409 ||BIS_UTILITIES_PUB.encode(p_parameter5ValueId);
1410 -- 2280993 ends
1411 END IF;
1412 IF (p_parameter6levelId IS NOT NULL) THEN
1413 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter6LevelId='
1414 -- 2280993 starts
1415 -- ||wfa_html.conv_special_url_chars(p_parameter6levelId);
1416 ||BIS_UTILITIES_PUB.encode(p_parameter6levelId);
1417 -- 2280993 ends
1418 END IF;
1419 IF (p_parameter6ValueId IS NOT NULL) THEN
1420 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter6ValueId='
1421 -- 2280993 starts
1422 -- ||wfa_html.conv_special_url_chars(p_parameter6ValueId);
1423 ||BIS_UTILITIES_PUB.encode(p_parameter6ValueId);
1424 -- 2280993 ends
1425 END IF;
1426 IF (p_parameter7levelId IS NOT NULL) THEN
1427 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter7LevelId='
1428 -- 2280993 starts
1429 -- ||wfa_html.conv_special_url_chars(p_parameter7levelId);
1430 ||BIS_UTILITIES_PUB.encode(p_parameter7levelId);
1431 -- 2280993 ends
1432 END IF;
1433 IF (p_parameter7ValueId IS NOT NULL) THEN
1434 l_alert_url := l_alert_url || G_AMPERSAND || 'parameter7ValueId='
1435 -- 2280993 starts
1436 -- ||wfa_html.conv_special_url_chars(p_parameter7ValueId);
1437 ||BIS_UTILITIES_PUB.encode(p_parameter7ValueId);
1438 -- 2280993 ends
1439 END IF;
1440 IF (p_viewByLevelId IS NOT NULL) THEN
1441 l_alert_url := l_alert_url || G_AMPERSAND || 'viewByLevelId='
1442 -- 2280993 starts
1443 -- ||wfa_html.conv_special_url_chars(p_viewByLevelId);
1444 ||BIS_UTILITIES_PUB.encode(p_viewByLevelId);
1445 -- 2280993 ends
1446 END IF;
1447
1448 BIS_UTILITIES_PUB.put_line(p_text =>'alert url built');
1449 x_alert_url := l_alert_url;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 BIS_UTILITIES_PUB.put_line(p_text =>'Error in BuildAlertRegistrationURL: '||SQLERRM);
1454
1455 END BuildAlertRegistrationURL;
1456
1457 PROCEDURE BuildAlertRegistrationURL
1458 ( p_measure_id IN NUMBER
1459 , p_timelevel_id IN NUMBER
1460 , p_viewByLevelId IN VARCHAR2 := NULL
1461 , p_alertTip IN VARCHAR2 := NULL
1462 , p_returnPageUrl IN VARCHAR2 := NULL
1463 , x_alert_url OUT NOCOPY VARCHAR2
1464 )
1465 IS
1466
1467 l_alert_url VARCHAR2(32000);
1468 l_dbc VARCHAR2(10000);
1469 l_servlet_agent VARCHAR2(10000);
1470 l_encrypted_session_id VARCHAR2(1000);
1471 l_session_id NUMBER;
1472
1473 BEGIN
1474
1475 l_session_id := icx_sec.getsessioncookie;
1476 l_encrypted_session_id :=
1477 icx_call.encrypt3(icx_sec.getID(icx_Sec.PV_SESSION_ID));
1478 fnd_profile.get(name => 'APPS_SERVLET_AGENT',
1479 val => l_Alert_url);
1480 l_alert_url := FND_WEB_CONFIG.trail_slash(l_alert_url) ||
1481 'bisalrsc.jsp?dbc=' ||FND_WEB_CONFIG.DATABASE_ID
1482 || G_AMPERSAND||'session_id='||l_encrypted_session_id;
1483 IF (p_measure_id IS NOT NULL) THEN
1484 l_alert_url := l_alert_url || G_AMPERSAND ||'perfMeasureId='
1485 -- 2280993 starts
1486 -- ||wfa_html.conv_special_url_chars(p_measure_id);
1487 ||BIS_UTILITIES_PUB.encode(p_measure_id);
1488 -- 2280993 ends
1489 END IF;
1490 IF (p_timelevel_id IS NOT NULL) THEN
1491 l_alert_url := l_alert_url || G_AMPERSAND||'timeDimLevelId='
1492 -- 2280993 starts
1493 -- || wfa_html.conv_special_url_chars(p_timelevel_id);
1494 || BIS_UTILITIES_PUB.encode(p_timelevel_id);
1495 -- 2280993 ends
1496 END IF;
1497 IF (p_viewByLevelId IS NOT NULL) THEN
1498 l_alert_url := l_alert_url || G_AMPERSAND || 'viewByLevelId='
1499 -- 2280993 starts
1500 -- ||wfa_html.conv_special_url_chars(p_viewByLevelId);
1501 ||BIS_UTILITIES_PUB.encode(p_viewByLevelId);
1502 -- 2280993 ends
1503 END IF;
1504
1505 x_alert_url := l_alert_url;
1506
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 BIS_UTILITIES_PUB.put_line(p_text =>'Error in BuildAlertRegistrationURL: '||SQLERRM);
1510
1511 END BuildAlertRegistrationURL;
1512
1513 PROCEDURE BuildScheduleReportURL
1514 ( p_RegionCode IN VARCHAR2
1515 , p_FunctionName IN VARCHAR2
1516 , p_ApplicationId IN VARCHAR2 := NULL
1517 , p_plan_id IN VARCHAR2 := NULL
1518 , p_parameter1levelId IN NUMBER := NULL
1519 , p_parameter1ValueId IN VARCHAR2 := NULL
1520 , p_parameter2levelId IN NUMBER := NULL
1521 , p_parameter2ValueId IN VARCHAR2 := NULL
1522 , p_parameter3levelId IN NUMBER := NULL
1523 , p_parameter3ValueId IN VARCHAR2 := NULL
1524 , p_parameter4levelId IN NUMBER := NULL
1525 , p_parameter4ValueId IN VARCHAR2 := NULL
1526 , p_parameter5levelId IN NUMBER := NULL
1527 , p_parameter5ValueId IN VARCHAR2 := NULL
1528 , p_parameter6levelId IN NUMBER := NULL
1529 , p_parameter6ValueId IN VARCHAR2 := NULL
1530 , p_parameter7levelId IN NUMBER := NULL
1531 , p_parameter7ValueId IN VARCHAR2 := NULL
1532 , p_viewByLevelId IN VARCHAR2 := NULL
1533 , p_alertTip IN VARCHAR2 := NULL
1534 , p_returnPageUrl IN VARCHAR2 := NULL
1535 , x_alert_url OUT NOCOPY VARCHAR2
1536 )
1537 IS
1538
1539 BEGIN
1540
1541 Null;
1542
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 BIS_UTILITIES_PUB.put_line(p_text =>'Error in BuildScheduleReportURL: '||SQLERRM);
1546
1547 END BuildScheduleReportURL;
1548
1549 PROCEDURE Add_Users_To_Role
1550 ( p_role_name IN VARCHAR2
1551 , p_user_names IN VARCHAR2
1552 )
1553
1554 IS
1555 c1 PLS_INTEGER;
1556 l_user_names VARCHAR2(32000);
1557
1558 BEGIN
1559 IF (p_role_name IS NOT NULL) THEN
1560 IF (p_user_names IS NOT NULL) THEN
1561 l_user_names := TRIM(',' FROM TRIM(p_user_names));
1562 <<UserLoop>>
1563 LOOP
1564 c1 := INSTR(l_user_names, ',');
1565 BEGIN
1566 IF (c1 = 0) THEN
1567 WF_LOCAL_SYNCH.propagateUserRole(P_ROLE_NAME => p_role_name, P_USER_NAME => l_user_names) ;
1568 EXIT;
1569 ELSE
1570 WF_LOCAL_SYNCH.propagateUserRole(P_ROLE_NAME => p_role_name, P_USER_NAME => substr(l_user_names, 1, c1-1) ) ;
1571 END IF;
1572 l_user_names := ltrim(substr(l_user_names, c1+1));
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Add_Users_To_Role: '||SQLERRM);
1576 IF (c1 = 0) THEN
1577 EXIT;
1578 ELSE
1579 l_user_names := ltrim(substr(l_user_names, c1+1));
1580 END IF;
1581 END;
1582 END LOOP UserLoop;
1583 END IF;
1584 END IF;
1585 END Add_Users_To_Role;
1586
1587 END BIS_PMF_ALERT_REG_PVT;