[Home] [Help]
PACKAGE BODY: APPS.BIS_ALERT_SERVICE_PVT
Source
1 PACKAGE BODY BIS_ALERT_SERVICE_PVT AS
2 /* $Header: BISVALRB.pls 120.1 2005/11/18 05:51:18 ankgoel noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVALRS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for Alert Services
13 REM |
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | APR-2000 irchen Creation
18 REM | 24-MAY-2000 jradhakr Added a new procedure Retrieve_All_Request_Rows
19 REM | to retrieve all rows from Alert registration
20 REM | tables
21 REM | 20-SEP-2003 mahrao Changed for bug# 2649486
22 REM | Change ensures that a new request ID is generated
23 REM | for each of the requests. This helps in decoupling
24 REM | the requestor from the requests he doesn't want.
25 REM | Got rid of the call to
26 REM | BIS_PMF_ALERT_REG_PVT.Retrieve_Notifiers_Code in
27 REM | Retrieve_All_Request_Rows as this was causing a
28 REM | single notifiers code when the requestor resquests
29 REM | for the alert.
30 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
31 REM | parameters.
32 REM | 05-APR-03 mahrao Filter criterion was missing while populating
33 REM | x_Alert_request_Tbl from l_Param_Set_tbl while
34 REM | l_param_set_tbl already contains the required record.
35 REM | 11-APR-03 mahrao Filter criterion is corrected in procedure
36 REM | Retrieve_All_Request_Rows
37 REM | 15-Dec-2003 arhegde enh# 3148615 Change/Target based alerting. |
38 REM | 18-Nov-2005 ankgoel bug# 4675515 DBI Actuals for previous time period |
39 REM +=======================================================================+
40 */
41
42 --
43 -- Constants
44 --
45
46 l_debug_text VARCHAR2(32000);
47 G_PKG_NAME CONSTANT VARCHAR2(30):= 'BIS_ALERT_SERVICE_PVT';
48
49 -----------------------------------------------------
50 -- Procedures Forward declarations
51 -----------------------------------------------------
52
53 Procedure Submit_Planning_Request
54 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
55 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
56 , p_alert_type IN VARCHAR2 := NULL
57 , p_alert_level IN VARCHAR2 := NULL
58 );
59
60 Procedure Submit_Planning_Request
61 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
62 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
63 , p_primary_dim_level_rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
64 , p_notify_set IN VARCHAR2
65 , p_alert_type IN VARCHAR2
66 , p_alert_level IN VARCHAR2
67 );
68
69 Procedure Form_Planning_Request
70 ( p_measure_id IN NUMBER := NULL
71 , p_measure_short_name IN VARCHAR2 := NULL
72 , p_target_level_id IN NUMBER
73 , p_target_level_short_name IN VARCHAR2
74 , p_plan_id IN NUMBER := NULL
75 , p_org_level_id IN NUMBER := NULL
76 , p_org_level_short_name IN VARCHAR2 := NULL
77 , p_organization_id IN VARCHAR2 := NULL
78 , p_time_level_id IN NUMBER := NULL
79 , p_time_level_short_name IN VARCHAR2 := NULL
80 , p_time_level_value_id IN VARCHAR2 := NULL
81 , p_dim1_level_id IN NUMBER := NULL
82 , p_dim1_level_short_name IN VARCHAR2 := NULL
83 , p_dim1_level_value_id IN VARCHAR2 := NULL
84 , p_dim2_level_id IN NUMBER := NULL
85 , p_dim2_level_short_name IN VARCHAR2 := NULL
86 , p_dim2_level_value_id IN VARCHAR2 := NULL
87 , p_dim3_level_id IN NUMBER := NULL
88 , p_dim3_level_short_name IN VARCHAR2 := NULL
89 , p_dim3_level_value_id IN VARCHAR2 := NULL
90 , p_dim4_level_id IN NUMBER := NULL
91 , p_dim4_level_short_name IN VARCHAR2 := NULL
92 , p_dim4_level_value_id IN VARCHAR2 := NULL
93 , p_dim5_level_id IN NUMBER := NULL
94 , p_dim5_level_short_name IN VARCHAR2 := NULL
95 , p_dim5_level_value_id IN VARCHAR2 := NULL
96 , p_dim6_level_id IN NUMBER := NULL
97 , p_dim6_level_short_name IN VARCHAR2 := NULL
98 , p_dim6_level_value_id IN VARCHAR2 := NULL
99 , p_dim7_level_id IN NUMBER := NULL
100 , p_dim7_level_short_name IN VARCHAR2 := NULL
101 , p_dim7_level_value_id IN VARCHAR2 := NULL
102 , p_target_id IN NUMBER := NULL
103 , p_target IN NUMBER := NULL
104 , p_actual_id IN NUMBER := NULL
105 , p_actual IN NUMBER := NULL
106 , p_primary_dim_level_id IN NUMBER := NULL
107 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
108 , P_notify_set IN VARCHAR2 := NULL
109 , p_alert_type IN VARCHAR2 := NULL
110 , p_alert_level IN VARCHAR2 := NULL
111 , x_measure_instance OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
112 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
113 );
114
115 -- For future enhencements
116 --
117 Procedure Form_Planning_Request
118 ( p_measure_id IN NUMBER := NULL
119 , p_measure_short_name IN VARCHAR2 := NULL
120 , p_target_level_id IN NUMBER
121 , p_target_level_short_name IN VARCHAR2
122 , p_plan_id IN NUMBER := NULL
123 , p_org_level_id IN NUMBER := NULL
124 , p_org_level_short_name IN VARCHAR2 := NULL
125 , p_organization_id IN VARCHAR2 := NULL
126 , p_time_level_id IN NUMBER := NULL
127 , p_time_level_short_name IN VARCHAR2 := NULL
128 , p_time_level_value_id IN VARCHAR2 := NULL
129 , p_dim1_level_id IN NUMBER := NULL
130 , p_dim1_level_short_name IN VARCHAR2 := NULL
131 , p_dim1_level_value_id IN VARCHAR2 := NULL
132 , p_dim2_level_id IN NUMBER := NULL
133 , p_dim2_level_short_name IN VARCHAR2 := NULL
134 , p_dim2_level_value_id IN VARCHAR2 := NULL
135 , p_dim3_level_id IN NUMBER := NULL
136 , p_dim3_level_short_name IN VARCHAR2 := NULL
137 , p_dim3_level_value_id IN VARCHAR2 := NULL
138 , p_dim4_level_id IN NUMBER := NULL
139 , p_dim4_level_short_name IN VARCHAR2 := NULL
140 , p_dim4_level_value_id IN VARCHAR2 := NULL
141 , p_dim5_level_id IN NUMBER := NULL
142 , p_dim5_level_short_name IN VARCHAR2 := NULL
143 , p_dim5_level_value_id IN VARCHAR2 := NULL
144 , p_dim6_level_id IN NUMBER := NULL
145 , p_dim6_level_short_name IN VARCHAR2 := NULL
146 , p_dim6_level_value_id IN VARCHAR2 := NULL
147 , p_dim7_level_id IN NUMBER := NULL
148 , p_dim7_level_short_name IN VARCHAR2 := NULL
149 , p_dim7_level_value_id IN VARCHAR2 := NULL
150 , p_target_id IN NUMBER := NULL
151 , p_target IN NUMBER := NULL
152 , p_actual_id IN NUMBER := NULL
153 , p_actual IN NUMBER := NULL
154 , p_primary_dim_level_id IN NUMBER := NULL
155 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
156 , P_notify_set IN VARCHAR2 := NULL
157 , p_alert_type IN VARCHAR2 := NULL
158 , p_alert_level IN VARCHAR2 := NULL
159 , x_measure_instance OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
160 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
161 , x_primary_dim_level_rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
162 , x_notify_set OUT NOCOPY VARCHAR2
163 , x_alert_type OUT NOCOPY VARCHAR2
164 , x_alert_level OUT NOCOPY VARCHAR2
165 );
166
167 -- Retrieve rows from the Alert Registration Repository
168 -- and any new targets that might have been created
169 --
170 PROCEDURE Retrieve_All_Request_Rows
171 ( p_api_version IN NUMBER
172 ,p_perf_measure_id IN NUMBER
173 ,p_time_dim_level_id IN NUMBER
174 ,p_current_row IN VARCHAR2 := NULL
175 ,p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
176 , p_alert_type IN VARCHAR2 := NULL
177 , p_alert_level IN VARCHAR2 := NULL
178 ,x_Alert_request_Tbl OUT NOCOPY BIS_ALERT_SERVICE_PVT.Alert_request_Tbl_type
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
181 );
182
183 -----------------------------------------------------
184 -- Procedures
185 -----------------------------------------------------
186
187 Procedure Service_Alert_Request
188 ( ERRBUF OUT NOCOPY VARCHAR2
189 , RETCODE OUT NOCOPY VARCHAR2
190 , p_measure_id IN NUMBER
191 , p_measure_short_name IN VARCHAR2
192 , p_plan_id IN NUMBER := NULL
193 , p_org_level_id IN NUMBER := NULL
194 , p_org_level_short_name IN VARCHAR2 := NULL
195 , p_organization_id IN VARCHAR2 := NULL
196 , p_time_level_id IN NUMBER := NULL
197 , p_time_level_short_name IN VARCHAR2 := NULL
198 , p_time_level_value_id IN VARCHAR2 := NULL
199 , p_dim1_level_id IN NUMBER := NULL
200 , p_dim1_level_short_name IN VARCHAR2 := NULL
201 , p_dim1_level_value_id IN VARCHAR2 := NULL
202 , p_dim2_level_id IN NUMBER := NULL
203 , p_dim2_level_short_name IN VARCHAR2 := NULL
204 , p_dim2_level_value_id IN VARCHAR2 := NULL
205 , p_dim3_level_id IN NUMBER := NULL
206 , p_dim3_level_short_name IN VARCHAR2 := NULL
207 , p_dim3_level_value_id IN VARCHAR2 := NULL
208 , p_dim4_level_id IN NUMBER := NULL
209 , p_dim4_level_short_name IN VARCHAR2 := NULL
210 , p_dim4_level_value_id IN VARCHAR2 := NULL
211 , p_dim5_level_id IN NUMBER := NULL
212 , p_dim5_level_short_name IN VARCHAR2 := NULL
213 , p_dim5_level_value_id IN VARCHAR2 := NULL
214 , p_dim6_level_id IN NUMBER := NULL
215 , p_dim6_level_short_name IN VARCHAR2 := NULL
216 , p_dim6_level_value_id IN VARCHAR2 := NULL
217 , p_dim7_level_id IN NUMBER := NULL
218 , p_dim7_level_short_name IN VARCHAR2 := NULL
219 , p_dim7_level_value_id IN VARCHAR2 := NULL
220 , p_primary_dim_level_id IN NUMBER := NULL
221 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
222 , P_notify_set IN VARCHAR2 := NULL
223 , p_alert_type IN VARCHAR2 := NULL
224 , p_alert_level IN VARCHAR2 := NULL
225 , p_current_row IN VARCHAR2 := 'N'
226 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
227 )
228 IS
229
230 l_alert_request_tbl BIS_ALERT_SERVICE_PVT.Alert_Request_Tbl_Type;
231 l_Concurrent_Request_Tbl BIS_CONCURRENT_MANAGER_PVT.PMF_Request_Tbl_Type;
232 l_request_id_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
233
234 l_return_status VARCHAR2(1000);
235 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
236 l_api_version NUMBER := 1;
237
238 BEGIN
239
240 BIS_UTILITIES_PUB.put_line(p_text =>'Servicing main alert request ');
241 BIS_UTILITIES_PUB.put_line(p_text =>'alert type: '||p_alert_type);
242
243 BIS_UTILITIES_PUB.put_line(p_text =>'Time level : '||p_time_level_id);
244
245 -- Retrieves all rows from ART for this request
246 --
247 Retrieve_All_Request_Rows
248 ( p_api_version => l_api_version
249 , p_perf_measure_id => p_measure_id
250 , p_time_dim_level_id => p_time_level_id
251 , p_current_row => p_current_row
252 , p_alert_based_on => p_alert_based_on
253 , p_alert_type => p_alert_type
254 , p_alert_level => p_alert_level
255 , x_Alert_Request_Tbl => l_alert_request_tbl
256 , x_return_status => l_return_status
257 , x_error_Tbl => l_error_Tbl
258 );
259
260 BIS_UTILITIES_PUB.put_line(p_text =>'Number of request rows: '||l_alert_request_tbl.count);
261
262 Form_Concurrent_Request
263 ( p_Alert_Request_Tbl => l_Alert_Request_Tbl
264 , x_Concurrent_Request_Tbl => l_Concurrent_Request_Tbl
265 );
266
267 BIS_UTILITIES_PUB.put_line(p_text =>'Submit concurrent requests for Detial Alert Service');
268 --
269 BIS_CONCURRENT_MANAGER_PVT.Submit_Concurrent_Request
270 ( p_Concurrent_Request_Tbl => l_Concurrent_Request_Tbl
271 , x_request_id_tbl => l_request_id_tbl
272 , x_errbuf => ERRBUF
273 , x_retcode => RETCODE
274 );
275
276 EXCEPTION
277 when FND_API.G_EXC_ERROR then
278 RETCODE := 1;
279 ERRBUF := SQLERRM;
280 BIS_UTILITIES_PUB.put_line(p_text =>'service alert req error 1');
281 RETURN;
282 when FND_API.G_EXC_UNEXPECTED_ERROR then
283 RETCODE := 1;
284 ERRBUF := SQLERRM;
285 BIS_UTILITIES_PUB.put_line(p_text =>'service alert req error 2');
286 RETURN;
287 when others then
288 RETCODE := 1;
289 BIS_UTILITIES_PUB.put_line(p_text =>'service alert req error 3: '||SQLERRM);
290
291 BIS_UTILITIES_PVT.Add_Error_Message
292 ( p_error_msg_id => SQLCODE
293 , p_error_description => SQLERRM
294 , p_error_proc_name => G_PKG_NAME||'.Service_Alert_Request'
295 );
296 RETURN;
297
298 END Service_Alert_Request;
299
300 Procedure Service_Alert_Request_Pvt
301 ( ERRBUF OUT NOCOPY VARCHAR2
302 , RETCODE OUT NOCOPY VARCHAR2
303 , p_measure_id IN NUMBER := NULL
304 , p_measure_short_name IN VARCHAR2 := NULL
305 , p_target_level_id IN NUMBER
306 , p_target_level_short_name IN VARCHAR2
307 , p_plan_id IN NUMBER := NULL
308 , p_org_level_id IN NUMBER := NULL
309 , p_org_level_short_name IN VARCHAR2 := NULL
310 , p_organization_id IN VARCHAR2 := NULL
311 , p_time_level_id IN NUMBER := NULL
312 , p_time_level_short_name IN VARCHAR2 := NULL
313 , p_time_level_value_id IN VARCHAR2 := NULL
314 , p_dim1_level_id IN NUMBER := NULL
315 , p_dim1_level_short_name IN VARCHAR2 := NULL
316 , p_dim1_level_value_id IN VARCHAR2 := NULL
317 , p_dim2_level_id IN NUMBER := NULL
318 , p_dim2_level_short_name IN VARCHAR2 := NULL
319 , p_dim2_level_value_id IN VARCHAR2 := NULL
320 , p_dim3_level_id IN NUMBER := NULL
321 , p_dim3_level_short_name IN VARCHAR2 := NULL
322 , p_dim3_level_value_id IN VARCHAR2 := NULL
323 , p_dim4_level_id IN NUMBER := NULL
324 , p_dim4_level_short_name IN VARCHAR2 := NULL
325 , p_dim4_level_value_id IN VARCHAR2 := NULL
326 , p_dim5_level_id IN NUMBER := NULL
327 , p_dim5_level_short_name IN VARCHAR2 := NULL
328 , p_dim5_level_value_id IN VARCHAR2 := NULL
329 , p_dim6_level_id IN NUMBER := NULL
330 , p_dim6_level_short_name IN VARCHAR2 := NULL
331 , p_dim6_level_value_id IN VARCHAR2 := NULL
332 , p_dim7_level_id IN NUMBER := NULL
333 , p_dim7_level_short_name IN VARCHAR2 := NULL
334 , p_dim7_level_value_id IN VARCHAR2 := NULL
335 , p_target_id IN NUMBER := NULL
336 , p_target IN NUMBER := NULL
337 , p_actual_id IN NUMBER := NULL
338 , p_actual IN NUMBER := NULL
339 , p_primary_dim_level_id IN NUMBER := NULL
340 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
341 , P_notify_set IN VARCHAR2 := NULL
342 , p_alert_type IN VARCHAR2 := NULL
343 , p_alert_level IN VARCHAR2 := NULL
344 , p_current_row IN VARCHAR2 := NULL
345 )
346 IS
347
348 l_Measure_Instance BIS_MEASURE_PUB.Measure_Instance_type;
349 l_Dim_Level_Value_Tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
350 l_alert_request_tbl BIS_ALERT_SERVICE_PVT.Alert_Request_Tbl_Type;
351 l_alert_request_rec BIS_ALERT_SERVICE_PVT.Alert_Request_Rec_Type;
352 l_return_status VARCHAR2(1000);
353 l_return_msg VARCHAR2(32000);
354
355 BEGIN
356
360 , x_return_status => l_return_status
357 -- Debug messages should be printed irrespective of profile option.
358 bis_utilities_pvt.set_debug_log_flag ( -- 2715218
359 p_is_true => TRUE
361 , x_return_msg => l_return_msg
362 ) ;
363
364 -- fnd_file.put_line(fnd_file.log, ' Return status is = ' || nvl(l_return_status, 'X!!' ));
365
366 BIS_UTILITIES_PUB.put_line(p_text =>' ------- Begin log file for SONAR child request. ------- ');
367
368 BIS_UTILITIES_PUB.put_line(p_text =>'Servicing detailed alert request.');
369
370 -- Form generic planning request record
371 --
372 Form_Planning_Request
373 ( p_measure_id => p_measure_id
374 , p_measure_short_name => p_measure_short_name
375 , p_target_level_id => p_target_level_id
376 , p_target_level_short_name => p_target_level_short_name
377 , p_plan_id => p_plan_id
378 , p_org_level_id => p_org_level_id
379 , p_org_level_short_name => p_org_level_short_name
380 , p_organization_id => p_organization_id
381 , p_time_level_id => p_time_level_id
382 , p_time_level_short_name => p_time_level_short_name
383 , p_time_level_value_id => p_time_level_value_id
384 , p_dim1_level_id => p_dim1_level_id
385 , p_dim1_level_short_name => p_dim1_level_short_name
386 , p_dim1_level_value_id => p_dim1_level_value_id
387 , p_dim2_level_id => p_dim2_level_id
388 , p_dim2_level_short_name => p_dim2_level_short_name
389 , p_dim2_level_value_id => p_dim2_level_value_id
390 , p_dim3_level_id => p_dim3_level_id
391 , p_dim3_level_short_name => p_dim3_level_short_name
392 , p_dim3_level_value_id => p_dim3_level_value_id
393 , p_dim4_level_id => p_dim4_level_id
394 , p_dim4_level_short_name => p_dim4_level_short_name
395 , p_dim4_level_value_id => p_dim4_level_value_id
396 , p_dim5_level_id => p_dim5_level_id
397 , p_dim5_level_short_name => p_dim5_level_short_name
398 , p_dim5_level_value_id => p_dim5_level_value_id
399 , p_dim6_level_id => p_dim6_level_id
400 , p_dim6_level_short_name => p_dim6_level_short_name
401 , p_dim6_level_value_id => p_dim6_level_value_id
402 , p_dim7_level_id => p_dim7_level_id
403 , p_dim7_level_short_name => p_dim7_level_short_name
404 , p_dim7_level_value_id => p_dim7_level_value_id
405 , p_target_id => p_target_id
406 , p_target => p_target
407 , p_actual_id => p_actual_id
408 , p_actual => p_actual
409 , p_primary_dim_level_id => p_primary_dim_level_id
410 , p_primary_dim_level_short_name => p_primary_dim_level_short_name
411 , P_notify_set => P_notify_set
412 , p_alert_type => p_alert_type
413 , p_alert_level => p_alert_level
414 , x_measure_instance => l_Measure_Instance
415 , x_Dim_Level_Value_Tbl => l_Dim_Level_Value_Tbl
416 );
417
418 BIS_UTILITIES_PUB.put_line(p_text =>'Target level id: '||l_Measure_Instance.target_level_id);
419
420 -- submit generic planning request
421 --
422 Submit_Planning_Request
423 ( p_measure_instance => l_Measure_Instance
424 , p_dim_level_value_tbl => l_Dim_Level_Value_Tbl
425 , p_alert_type => p_alert_type
426 , p_alert_level => p_alert_level
427 );
428
429 BIS_UTILITIES_PUB.put_line(p_text => ' ------- End log file for SONAR child request. ------- ');
430
431 EXCEPTION
432 when FND_API.G_EXC_ERROR then
433 RETCODE := 1;
434 ERRBUF := SQLERRM;
435 RETURN;
436 when FND_API.G_EXC_UNEXPECTED_ERROR then
437 RETCODE := 1;
438 ERRBUF := SQLERRM;
439 RETURN;
440 when others then
441 RETCODE := 1;
442 BIS_UTILITIES_PVT.Add_Error_Message
443 ( p_error_msg_id => SQLCODE
444 , p_error_description => SQLERRM
445 , p_error_proc_name => G_PKG_NAME||'.Service_Alert_Request_pvt'
446 );
447 RETURN;
448 END Service_Alert_Request_Pvt;
449
450 Procedure Submit_Planning_Request
451 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
452 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
453 , p_alert_type IN VARCHAR2 := NULL
454 , p_alert_level IN VARCHAR2 := NULL
455 )
456 IS
457
458 BEGIN
459
460 BIS_GENERIC_PLANNER_PVT.Service_Planner_Request
461 ( p_measure_instance => p_measure_instance
462 , p_dim_level_value_tbl => p_Dim_Level_Value_Tbl
463 , p_alert_type => p_alert_type
464 , p_alert_level => p_alert_level
465 );
466
467 EXCEPTION
468 when FND_API.G_EXC_ERROR then
469 RETURN;
470 when FND_API.G_EXC_UNEXPECTED_ERROR then
471 RETURN;
472 when others then
473 BIS_UTILITIES_PVT.Add_Error_Message
474 ( p_error_msg_id => SQLCODE
475 , p_error_description => SQLERRM
476 , p_error_proc_name => G_PKG_NAME||'.Service_Alert_Request'
477 );
478 RETURN;
479
480 END Submit_Planning_Request;
481
482 -- for future enhencements
483 --
484 Procedure Submit_Planning_Request
485 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
486 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
487 , p_primary_dim_level_rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
488 , P_notify_set IN VARCHAR2
492 IS
489 , p_alert_type IN VARCHAR2
490 , p_alert_level IN VARCHAR2
491 )
493
494 BEGIN
495
496 null;
497
498 EXCEPTION
499 when FND_API.G_EXC_ERROR then
500 RETURN;
501 when FND_API.G_EXC_UNEXPECTED_ERROR then
502 RETURN;
503 when others then
504 BIS_UTILITIES_PVT.Add_Error_Message
505 ( p_error_msg_id => SQLCODE
506 , p_error_description => SQLERRM
507 , p_error_proc_name => G_PKG_NAME||'.Service_Alert_Request'
508 );
509 RETURN;
510 END Submit_Planning_Request;
511
512 Procedure Form_Concurrent_Request
513 ( p_Alert_Request_Tbl IN Alert_Request_Tbl_Type
514 , x_Concurrent_Request_Tbl
515 OUT NOCOPY BIS_CONCURRENT_MANAGER_PVT.PMF_Request_Tbl_Type
516 )
517 IS
518
519 l_Concurrent_Request_Tbl
520 BIS_CONCURRENT_MANAGER_PVT.PMF_Request_Tbl_Type;
521 l_count NUMBER := 0;
522
523 BEGIN
524
525 l_Concurrent_Request_Tbl := x_Concurrent_Request_Tbl;
526 BIS_UTILITIES_PUB.put_line(p_text =>'Forming concurrent requests. Number of alert requests: '
527 ||p_Alert_Request_Tbl.COUNT );
528
529 FOR i IN 1..p_Alert_Request_Tbl.COUNT LOOP
530 --BIS_UTILITIES_PUB.put_line(p_text =>'in form concurrent request: target level id: '
531 --||p_Alert_Request_Tbl(i).target_level_id);
532
533 l_count := l_Concurrent_Request_Tbl.COUNT+1;
534
535 l_Concurrent_Request_Tbl(l_count).application_short_name
536 := BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME;
537 l_Concurrent_Request_Tbl(l_count).program
538 := BIS_CONCURRENT_MANAGER_PVT.G_ALERT_PROGRAM_PVT;
539 l_Concurrent_Request_Tbl(l_count).start_time := NULL;
540 l_Concurrent_Request_Tbl(l_count).description
541 := 'BIS Alert Detail: '||p_alert_request_tbl(i).target_level_id;
542 l_Concurrent_Request_Tbl(l_count).argument1
543 := p_alert_request_tbl(i).measure_id;
544 l_Concurrent_Request_Tbl(l_count).argument2
545 := p_alert_request_tbl(i).measure_short_name;
546 l_Concurrent_Request_Tbl(l_count).argument3
547 := p_alert_request_tbl(i).target_level_id;
548 l_Concurrent_Request_Tbl(l_count).argument4
549 := p_alert_request_tbl(i).target_level_short_name;
550 l_Concurrent_Request_Tbl(l_count).argument5
551 := p_alert_request_tbl(i).plan_id;
552
553 l_Concurrent_Request_Tbl(l_count).argument6
554 := p_alert_request_tbl(i).org_level_id;
555 l_Concurrent_Request_Tbl(l_count).argument7
556 := p_alert_request_tbl(i).org_level_short_name;
557 l_Concurrent_Request_Tbl(l_count).argument8
558 := p_alert_request_tbl(i).organization_id;
559
560 l_Concurrent_Request_Tbl(l_count).argument9
561 := p_alert_request_tbl(i).time_level_id;
562 l_Concurrent_Request_Tbl(l_count).argument10
563 := p_alert_request_tbl(i).time_level_short_name;
564 l_Concurrent_Request_Tbl(l_count).argument11
565 := p_alert_request_tbl(i).time_level_value_id;
566
567 l_Concurrent_Request_Tbl(l_count).argument12
568 := p_alert_request_tbl(i).dim1_level_id;
569 l_Concurrent_Request_Tbl(l_count).argument13
570 := p_alert_request_tbl(i).dim1_level_short_name;
571 l_Concurrent_Request_Tbl(l_count).argument14
572 := p_alert_request_tbl(i).dim1_level_value_id;
573
574 l_Concurrent_Request_Tbl(l_count).argument15
575 := p_alert_request_tbl(i).dim2_level_id;
576 l_Concurrent_Request_Tbl(l_count).argument16
577 := p_alert_request_tbl(i).dim2_level_short_name;
578 l_Concurrent_Request_Tbl(l_count).argument17
579 := p_alert_request_tbl(i).dim2_level_value_id;
580
581 l_Concurrent_Request_Tbl(l_count).argument18
582 := p_alert_request_tbl(i).dim3_level_id;
583 l_Concurrent_Request_Tbl(l_count).argument19
584 := p_alert_request_tbl(i).dim3_level_short_name;
585 l_Concurrent_Request_Tbl(l_count).argument20
586 := p_alert_request_tbl(i).dim3_level_value_id;
587
588 l_Concurrent_Request_Tbl(l_count).argument21
589 := p_alert_request_tbl(i).dim4_level_id;
590 l_Concurrent_Request_Tbl(l_count).argument22
591 := p_alert_request_tbl(i).dim4_level_short_name;
592 l_Concurrent_Request_Tbl(l_count).argument23
593 := p_alert_request_tbl(i).dim4_level_value_id;
594
595 l_Concurrent_Request_Tbl(l_count).argument24
596 := p_alert_request_tbl(i).dim5_level_id;
597 l_Concurrent_Request_Tbl(l_count).argument25
598 := p_alert_request_tbl(i).dim5_level_short_name;
599 l_Concurrent_Request_Tbl(l_count).argument26
600 := p_alert_request_tbl(i).dim5_level_value_id;
601
602 l_Concurrent_Request_Tbl(l_count).argument27
603 := p_alert_request_tbl(i).dim6_level_id;
604 l_Concurrent_Request_Tbl(l_count).argument28
605 := p_alert_request_tbl(i).dim6_level_short_name;
606 l_Concurrent_Request_Tbl(l_count).argument29
607 := p_alert_request_tbl(i).dim6_level_value_id;
608
609 l_Concurrent_Request_Tbl(l_count).argument30
610 := p_alert_request_tbl(i).dim7_level_id;
611 l_Concurrent_Request_Tbl(l_count).argument31
612 := p_alert_request_tbl(i).dim7_level_short_name;
613 l_Concurrent_Request_Tbl(l_count).argument32
614 := p_alert_request_tbl(i).dim7_level_value_id;
615
616 l_Concurrent_Request_Tbl(l_count).argument33
617 := p_alert_request_tbl(i).target_id;
621 := p_alert_request_tbl(i).actual_id;
618 l_Concurrent_Request_Tbl(l_count).argument34
619 := p_alert_request_tbl(i).target;
620 l_Concurrent_Request_Tbl(l_count).argument35
622
623 l_Concurrent_Request_Tbl(l_count).argument36
624 := p_alert_request_tbl(i).actual;
625 l_Concurrent_Request_Tbl(l_count).argument37
626 := p_alert_request_tbl(i).primary_dim_level_id;
627 l_Concurrent_Request_Tbl(l_count).argument38
628 := p_alert_request_tbl(i).primary_dim_level_short_name;
629 l_Concurrent_Request_Tbl(l_count).argument39
630 := p_alert_request_tbl(i).notify_set;
631 l_Concurrent_Request_Tbl(l_count).argument40
632 := p_alert_request_tbl(i).alert_type;
633 l_Concurrent_Request_Tbl(l_count).argument41
634 := p_alert_request_tbl(i).alert_level;
635 l_Concurrent_Request_Tbl(l_count).argument42
636 := p_alert_request_tbl(i).current_row; -- current row
637 l_Concurrent_Request_Tbl(l_count).argument43
638 := p_alert_request_tbl(i).alert_based_on;
639 END LOOP;
640
641 x_Concurrent_Request_Tbl := l_Concurrent_Request_Tbl;
642 BIS_UTILITIES_PUB.put_line(p_text =>'Number of request successfully formed: '
643 ||x_Concurrent_Request_Tbl.COUNT);
644
645 EXCEPTION
646 when FND_API.G_EXC_ERROR then
647 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 1');
648 when FND_API.G_EXC_UNEXPECTED_ERROR then
649 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 2');
650 when others then
651 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 3: '||SQLERRM);
652 BIS_UTILITIES_PVT.Add_Error_Message
653 ( p_error_msg_id => SQLCODE
654 , p_error_description => SQLERRM
655 , p_error_proc_name => G_PKG_NAME||'.Form_Concurrent_Request'
656 );
657
658 END Form_Concurrent_Request;
659
660
661 Procedure Form_Planning_Request
662 ( p_measure_id IN NUMBER := NULL
663 , p_measure_short_name IN VARCHAR2 := NULL
664 , p_target_level_id IN NUMBER
665 , p_target_level_short_name IN VARCHAR2
666 , p_plan_id IN NUMBER := NULL
667 , p_org_level_id IN NUMBER := NULL
668 , p_org_level_short_name IN VARCHAR2 := NULL
669 , p_organization_id IN VARCHAR2 := NULL
670 , p_time_level_id IN NUMBER := NULL
671 , p_time_level_short_name IN VARCHAR2 := NULL
672 , p_time_level_value_id IN VARCHAR2 := NULL
673 , p_dim1_level_id IN NUMBER := NULL
674 , p_dim1_level_short_name IN VARCHAR2 := NULL
675 , p_dim1_level_value_id IN VARCHAR2 := NULL
676 , p_dim2_level_id IN NUMBER := NULL
677 , p_dim2_level_short_name IN VARCHAR2 := NULL
678 , p_dim2_level_value_id IN VARCHAR2 := NULL
679 , p_dim3_level_id IN NUMBER := NULL
680 , p_dim3_level_short_name IN VARCHAR2 := NULL
681 , p_dim3_level_value_id IN VARCHAR2 := NULL
682 , p_dim4_level_id IN NUMBER := NULL
683 , p_dim4_level_short_name IN VARCHAR2 := NULL
684 , p_dim4_level_value_id IN VARCHAR2 := NULL
685 , p_dim5_level_id IN NUMBER := NULL
686 , p_dim5_level_short_name IN VARCHAR2 := NULL
687 , p_dim5_level_value_id IN VARCHAR2 := NULL
688 , p_dim6_level_id IN NUMBER := NULL
689 , p_dim6_level_short_name IN VARCHAR2 := NULL
690 , p_dim6_level_value_id IN VARCHAR2 := NULL
691 , p_dim7_level_id IN NUMBER := NULL
692 , p_dim7_level_short_name IN VARCHAR2 := NULL
693 , p_dim7_level_value_id IN VARCHAR2 := NULL
694 , p_target_id IN NUMBER := NULL
695 , p_target IN NUMBER := NULL
696 , p_actual_id IN NUMBER := NULL
697 , p_actual IN NUMBER := NULL
698 , p_primary_dim_level_id IN NUMBER := NULL
699 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
700 , P_notify_set IN VARCHAR2 := NULL
701 , p_alert_type IN VARCHAR2 := NULL
702 , p_alert_level IN VARCHAR2 := NULL
703 , x_measure_instance OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
704 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
705 )
706 IS
707
708 l_measure_instance BIS_MEASURE_PUB.measure_instance_Type;
709 l_Dim_Level_Value_Tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
710
711 BEGIN
712
713 l_measure_instance := x_measure_instance;
714 l_Dim_Level_Value_Tbl := x_Dim_Level_Value_Tbl;
715
716 l_measure_instance.measure_id := p_measure_id ;
717 l_measure_instance.measure_short_name := p_measure_short_name;
718 l_measure_instance.target_level_id := p_target_level_id ;
719 l_measure_instance.target_level_short_name := p_target_level_short_name;
720 l_measure_instance.plan_id := p_plan_id ;
721
722 l_Dim_Level_Value_Tbl(1).dimension_level_id := p_dim1_level_id ;
723 l_Dim_Level_Value_Tbl(1).dimension_level_short_name
724 := p_dim1_level_short_name;
725 l_Dim_Level_Value_Tbl(1).Dimension_Level_Value_ID := p_dim1_level_value_id;
726
727 l_Dim_Level_Value_Tbl(2).dimension_level_id := p_dim2_level_id ;
728 l_Dim_Level_Value_Tbl(2).dimension_level_short_name
729 := p_dim2_level_short_name;
730 l_Dim_Level_Value_Tbl(2).dimension_level_value_id := p_dim2_level_value_id;
731
732 /*
733 l_Dim_Level_Value_Tbl(1).dimension_level_id := p_org_level_id ;
734 l_Dim_Level_Value_Tbl(1).dimension_level_short_name
735 := p_org_level_short_name;
736 l_Dim_Level_Value_Tbl(1).Dimension_Level_Value_ID := p_organization_id;
737
741 l_Dim_Level_Value_Tbl(2).dimension_level_value_id := p_time_level_value_id;
738 l_Dim_Level_Value_Tbl(2).dimension_level_id := p_time_level_id ;
739 l_Dim_Level_Value_Tbl(2).dimension_level_short_name
740 := p_time_level_short_name;
742 */
743
744 l_Dim_Level_Value_Tbl(3).dimension_level_id := p_dim3_level_id ;
745 l_Dim_Level_Value_Tbl(3).dimension_level_short_name
746 := p_dim3_level_short_name;
747 l_Dim_Level_Value_Tbl(3).dimension_level_value_id := p_dim3_level_value_id;
748
749 l_Dim_Level_Value_Tbl(4).dimension_level_id := p_dim4_level_id ;
750 l_Dim_Level_Value_Tbl(4).dimension_level_short_name
751 := p_dim4_level_short_name;
752 l_Dim_Level_Value_Tbl(4).dimension_level_value_id := p_dim4_level_value_id;
753
754 l_Dim_Level_Value_Tbl(5).dimension_level_id := p_dim5_level_id ;
755 l_Dim_Level_Value_Tbl(5).dimension_level_short_name
756 := p_dim5_level_short_name;
757 l_Dim_Level_Value_Tbl(5).dimension_level_value_id := p_dim5_level_value_id;
758
759 l_Dim_Level_Value_Tbl(6).dimension_level_id := p_dim6_level_id ;
760 l_Dim_Level_Value_Tbl(6).dimension_level_short_name
761 := p_dim6_level_short_name;
762 l_Dim_Level_Value_Tbl(6).dimension_level_value_id := p_dim6_level_value_id;
763
764 l_Dim_Level_Value_Tbl(7).dimension_level_id := p_dim7_level_id ;
765 l_Dim_Level_Value_Tbl(7).dimension_level_short_name
766 := p_dim7_level_short_name;
767 l_Dim_Level_Value_Tbl(7).dimension_level_value_id := p_dim7_level_value_id;
768
769 l_measure_instance.target_id := p_target_id ;
770 l_measure_instance.target := p_target ;
771
772 l_measure_instance.actual_id := p_actual_id ;
773 l_measure_instance.actual := p_actual ;
774
775 /* not used
776 l_Dim_Level_Value_Tbl(1).primary_dim_level_id
777 := p_dimension_level_rec.dimension_level_id;
778 l_Dim_Level_Value_Tbl(1).primary_dim_level_short_name
779 := p_dimension_level_rec.dimension_level_short_name;
780
781 l_measure_instance.notify_set := p_notify_set;
782 l_measure_instance.alert_type := p_alert_type;
783 l_measure_instance.alert_level := p_alert_level;
784 */
785
786 x_measure_instance := l_measure_instance;
787 x_Dim_Level_Value_Tbl := l_Dim_Level_Value_Tbl;
788
789 EXCEPTION
790 when FND_API.G_EXC_ERROR then
791 RETURN;
792 when FND_API.G_EXC_UNEXPECTED_ERROR then
793 RETURN;
794 when others then
795 BIS_UTILITIES_PVT.Add_Error_Message
796 ( p_error_msg_id => SQLCODE
797 , p_error_description => SQLERRM
798 , p_error_proc_name => G_PKG_NAME||'.Form_Planning_Request'
799 );
800 RETURN;
801
802 END Form_Planning_Request;
803
804 -- for future enhencements
805 --
806 Procedure Form_Planning_Request
807 ( p_measure_id IN NUMBER := NULL
808 , p_measure_short_name IN VARCHAR2 := NULL
809 , p_target_level_id IN NUMBER
810 , p_target_level_short_name IN VARCHAR2
811 , p_plan_id IN NUMBER := NULL
812 , p_org_level_id IN NUMBER := NULL
813 , p_org_level_short_name IN VARCHAR2 := NULL
814 , p_organization_id IN VARCHAR2 := NULL
815 , p_time_level_id IN NUMBER := NULL
816 , p_time_level_short_name IN VARCHAR2 := NULL
817 , p_time_level_value_id IN VARCHAR2 := NULL
818 , p_dim1_level_id IN NUMBER := NULL
819 , p_dim1_level_short_name IN VARCHAR2 := NULL
820 , p_dim1_level_value_id IN VARCHAR2 := NULL
821 , p_dim2_level_id IN NUMBER := NULL
822 , p_dim2_level_short_name IN VARCHAR2 := NULL
823 , p_dim2_level_value_id IN VARCHAR2 := NULL
824 , p_dim3_level_id IN NUMBER := NULL
825 , p_dim3_level_short_name IN VARCHAR2 := NULL
826 , p_dim3_level_value_id IN VARCHAR2 := NULL
827 , p_dim4_level_id IN NUMBER := NULL
828 , p_dim4_level_short_name IN VARCHAR2 := NULL
829 , p_dim4_level_value_id IN VARCHAR2 := NULL
830 , p_dim5_level_id IN NUMBER := NULL
831 , p_dim5_level_short_name IN VARCHAR2 := NULL
832 , p_dim5_level_value_id IN VARCHAR2 := NULL
833 , p_dim6_level_id IN NUMBER := NULL
834 , p_dim6_level_short_name IN VARCHAR2 := NULL
835 , p_dim6_level_value_id IN VARCHAR2 := NULL
836 , p_dim7_level_id IN NUMBER := NULL
837 , p_dim7_level_short_name IN VARCHAR2 := NULL
838 , p_dim7_level_value_id IN VARCHAR2 := NULL
839 , p_target_id IN NUMBER := NULL
840 , p_target IN NUMBER := NULL
841 , p_actual_id IN NUMBER := NULL
842 , p_actual IN NUMBER := NULL
843 , p_primary_dim_level_id IN NUMBER := NULL
844 , p_primary_dim_level_short_name IN VARCHAR2 := NULL
845 , P_notify_set IN VARCHAR2 := NULL
846 , p_alert_type IN VARCHAR2 := NULL
847 , p_alert_level IN VARCHAR2 := NULL
848 , x_measure_instance OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
849 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
850 , x_primary_dim_level_rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
851 , x_notify_set OUT NOCOPY VARCHAR2
852 , x_alert_type OUT NOCOPY VARCHAR2
853 , x_alert_level OUT NOCOPY VARCHAR2
854 )
855 IS
856
857 BEGIN
858
859 null;
860
861 EXCEPTION
862 when FND_API.G_EXC_ERROR then
863 RETURN;
864 when FND_API.G_EXC_UNEXPECTED_ERROR then
865 RETURN;
866 when others then
867 BIS_UTILITIES_PVT.Add_Error_Message
868 ( p_error_msg_id => SQLCODE
872 RETURN;
869 , p_error_description => SQLERRM
870 , p_error_proc_name => G_PKG_NAME||'.Service_Alert_Request'
871 );
873 END Form_Planning_Request;
874
875 Procedure Form_Alert_Request_rec
876 ( p_target_level_rec IN BIS_Target_level_PUB.Target_level_Rec_Type
877 , p_target_rec IN BIS_Target_PUB.Target_rec_Type
878 , p_dimension_level_rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
879 , p_notify_set IN VARCHAR2 := NULL
880 , p_alert_type IN VARCHAR2 := NULL
881 , p_alert_level IN VARCHAR2 := NULL
882 , x_Alert_Request_rec OUT NOCOPY Alert_Request_rec_Type
883 )
884 IS
885
886 l_Alert_Request_rec BIS_ALERT_SERVICE_PVT.Alert_Request_rec_Type;
887
888 BEGIN
889 --BIS_UTILITIES_PUB.put_line(p_text =>'in form alert request: target id: '||p_target_rec.target_id);
890
891 l_Alert_Request_rec := x_Alert_Request_rec;
892
893 l_alert_request_rec.measure_id
894 := p_target_level_rec.measure_id ;
895 l_alert_request_rec.target_level_id
896 := p_target_level_rec.target_level_id ;
897 l_alert_request_rec.target_level_short_name
898 := p_target_level_rec.target_level_short_name;
899 l_alert_request_rec.plan_id
900 := p_target_rec.plan_id ;
901
902 l_alert_request_rec.org_level_id
903 := p_target_level_rec.org_level_id ;
904 l_alert_request_rec.org_level_short_name
905 := p_target_level_rec.org_level_short_name;
906 l_alert_request_rec.organization_id
907 := p_target_rec.org_level_value_id;
908 l_alert_request_rec.time_level_id
909 := p_target_level_rec.time_level_id ;
910 l_alert_request_rec.time_level_short_name
911 := p_target_level_rec.time_level_short_name;
912 l_alert_request_rec.time_level_value_id
913 := p_target_rec.time_level_value_id;
914
915 l_alert_request_rec.dim1_level_id
916 := p_target_level_rec.dimension1_level_id ;
917 l_alert_request_rec.dim1_level_short_name
918 := p_target_level_rec.dimension1_level_short_name;
919 l_alert_request_rec.dim1_level_value_id
920 := p_target_rec.dim1_level_value_id;
921
922 l_alert_request_rec.dim2_level_id
923 := p_target_level_rec.dimension2_level_id ;
924 l_alert_request_rec.dim2_level_short_name
925 := p_target_level_rec.dimension2_level_short_name;
926 l_alert_request_rec.dim2_level_value_id
927 := p_target_rec.dim2_level_value_id;
928
929 l_alert_request_rec.dim3_level_id
930 := p_target_level_rec.dimension3_level_id;
931 l_alert_request_rec.dim3_level_short_name
932 := p_target_level_rec.dimension3_level_short_name;
933 l_alert_request_rec.dim3_level_value_id
934 := p_target_rec.dim3_level_value_id;
935
936 l_alert_request_rec.dim4_level_id
937 := p_target_level_rec.dimension4_level_id ;
938 l_alert_request_rec.dim4_level_short_name
939 := p_target_level_rec.dimension4_level_short_name;
940 l_alert_request_rec.dim4_level_value_id
941 := p_target_rec.dim4_level_value_id ;
942
943 l_alert_request_rec.dim5_level_id
944 := p_target_level_rec.dimension5_level_id;
945 l_alert_request_rec.dim5_level_short_name
946 := p_target_level_rec.dimension5_level_short_name ;
947 l_alert_request_rec.dim5_level_value_id
948 := p_target_rec.dim5_level_value_id ;
949
950 l_alert_request_rec.dim6_level_id
951 := p_target_level_rec.dimension6_level_id;
952 l_alert_request_rec.dim6_level_short_name
953 := p_target_level_rec.dimension6_level_short_name;
954 l_alert_request_rec.dim6_level_value_id
955 := p_target_rec.dim6_level_value_id;
956
957 l_alert_request_rec.dim7_level_id
958 := p_target_level_rec.dimension7_level_id;
959 l_alert_request_rec.dim7_level_short_name
960 := p_target_level_rec.dimension7_level_short_name;
961 l_alert_request_rec.dim7_level_value_id
962 := p_target_rec.dim7_level_value_id;
963
964 l_alert_request_rec.target_id := p_target_rec.target_id ;
965 l_alert_request_rec.target := p_target_rec.target ;
966
967 /*
968 l_alert_request_rec.primary_dim_level_id
969 := p_dimension_level_rec.dimension_level_id;
970 l_alert_request_rec.primary_dim_level_short_name
971 := p_dimension_level_rec.dimension_level_short_name;
972 */
973
974 l_alert_request_rec.notify_set := p_notify_set;
975 l_alert_request_rec.alert_type := p_alert_type;
976 l_alert_request_rec.alert_level := p_alert_level;
977
978 x_Alert_Request_rec := l_Alert_Request_rec;
979
980 --BIS_UTILITIES_PUB.put_line(p_text =>'target reqest formed : target id : '
981 --||x_Alert_Request_rec.target_id);
982
983 EXCEPTION
984 WHEN OTHERS THEN
985 BIS_UTILITIES_PUB.put_line(p_text =>'Form alert request error: '||sqlerrm);
986 return;
987
988 END Form_Alert_Request_rec;
989
990 -- Procedure to retrieve rows from alert registration repository.
991 --
992 PROCEDURE Retrieve_All_Request_Rows
993 ( p_api_version IN NUMBER
994 ,p_perf_measure_id IN NUMBER
995 ,p_time_dim_level_id IN NUMBER
996 ,p_current_row IN VARCHAR2 := NULL
997 ,p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
998 , p_alert_type IN VARCHAR2 := NULL
999 , p_alert_level IN VARCHAR2 := NULL
1003 )
1000 ,x_Alert_request_Tbl OUT NOCOPY BIS_ALERT_SERVICE_PVT.Alert_request_Tbl_type
1001 ,x_return_status OUT NOCOPY VARCHAR2
1002 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1004 IS
1005
1006 l_param_set_tbl_orig BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1007 l_param_set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1008 l_request_scheduled VARCHAR2(1000);
1009 l_request_id VARCHAR2(32000);
1010 l_debug_text VARCHAR2(32000);
1011
1012 l_count NUMBER := 0;
1013 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
1014 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1015 c_num NUMBER := -9999;
1016 c_var VARCHAR2(5) := '-9999';
1017 l_flag BOOLEAN := FALSE;
1018 l_count_param_set_tbl NUMBER := 0;
1019 Begin
1020
1021 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving all request rows. Current row? '||p_current_row);
1022 BIS_UTILITIES_PUB.put_line(p_text =>'alert type: '||p_alert_type);
1023
1024 BIS_PMF_ALERT_REG_PUB.Retrieve_Parameter_set
1025 ( p_api_version => p_api_version
1026 , p_measure_id => p_perf_measure_id
1027 , p_time_dimension_level_id => p_time_dim_level_id
1028 , p_current_row => p_current_row
1029 , x_Param_Set_Tbl => l_Param_Set_Tbl_orig
1030 , x_return_status => x_return_status
1031 , x_error_Tbl => x_error_Tbl
1032 );
1033 BIS_UTILITIES_PUB.put_line(p_text =>'Original number of parameter sets: '|| l_Param_Set_Tbl_orig.count);
1034
1035 BIS_PMF_REG_SERVICE_PVT.Retrieve_target_info
1036 ( p_api_version => p_api_version
1037 , p_measure_id => p_perf_measure_id
1038 , p_time_dimension_level_id => p_time_dim_level_id
1039 , p_current_row => p_current_row
1040 , p_alert_based_on => p_alert_based_on
1041 , x_target_tbl => l_target_tbl
1042 , x_return_status => x_return_status
1043 , x_error_Tbl => x_error_Tbl
1044 );
1045
1046
1047 -- Assign all the target info to the Parameter Set Rec and Call
1048 -- Register_parameter_set procedure
1049
1050 FOR i IN 1..l_Target_tbl.COUNT LOOP
1051 BIS_PMF_REG_SERVICE_PVT.Form_Parameter_Set
1052 ( p_measure_id => p_perf_measure_id
1053 , p_time_level_id => p_time_dim_level_id
1054 , p_target_tbl => l_Target_tbl
1055 , p_Notifiers_Code => NULL
1056 , x_Parameter_Set_tbl => l_param_set_tbl
1057 );
1058 END LOOP;
1059
1060 FOR i IN 1..l_Param_Set_tbl.COUNT LOOP
1061 BIS_UTILITIES_PUB.put_line(p_text =>'Managing '||i||'th TARGET registrations: '
1062 ||l_Param_Set_tbl(i).Target_Level_ID);
1063 BIS_PMF_ALERT_REG_PVT.Manage_Alert_Registrations
1064 ( p_Param_Set_rec => l_Param_Set_tbl(i)
1065 , x_request_scheduled => l_request_scheduled
1066 , x_return_status => x_return_status
1067 , x_error_Tbl => x_error_Tbl
1068 );
1069 --l_debug_text := l_debug_text||': '
1070 --||'Manage_Alert_Registration Measure: '||l_request_scheduled
1071 --||' , '||l_return_status;
1072 BIS_UTILITIES_PUB.put_line(p_text =>'after manag: '||l_debug_text);
1073
1074 IF l_request_scheduled = FND_API.G_TRUE THEN
1075 BIS_UTILITIES_PUB.put_line(p_text =>'Request exist');
1076 l_debug_text := l_debug_text ||': '
1077 ||BIS_PMF_REG_SERVICE_PVT.G_REQUEST_EXIST;
1078 l_request_id := BIS_PMF_REG_SERVICE_PVT.G_REQUEST_EXIST;
1079 ELSE
1080 --l_debug_text := l_debug_text ||'--process param set debug--'
1081 --||x_return_status;
1082 BIS_UTILITIES_PUB.put_line(p_text =>'Managing alert registration by Target result: '
1083 ||x_return_status);
1084
1085 BIS_PMF_REG_SERVICE_PVT.Register_Parameter_set
1086 ( p_api_version => p_api_version
1087 , p_Param_Set_Rec => l_Param_Set_tbl(i)
1088 , p_request_id => l_request_id
1089 , x_return_status => x_return_status
1090 , x_error_Tbl => x_error_Tbl
1091 );
1092 ---_debug_text := l_debug_text ||l_request_id;
1093 BIS_UTILITIES_PUB.put_line(p_text =>'registering target row: '||i||', status: '||x_return_status);
1094 END IF;
1095 END LOOP;
1096 -- records from bis_pmf_alert_parameters are retrieved into
1097 -- l_Param_Set_tbl_orig table.
1098 FOR i IN 1..l_Param_Set_tbl_orig.COUNT LOOP
1099 BIS_UTILITIES_PUB.put_line(p_text =>'stuffing l_Param_Set_tbl. i: '||i
1100 ||', tbl count: '||l_Param_Set_tbl.COUNT);
1101 -- Filter out duplicate records while they are added to l_Param_Set_tbl table
1102 -- from l_Param_Set_tbl_Orig table
1103 -- reset the flag for every record of l_Param_Set_tbl_Orig table
1104 l_flag := FALSE;
1105 FOR k IN 1..l_Param_Set_tbl.COUNT LOOP
1106 IF ( (NVL(l_Param_Set_tbl(k).Target_Level_ID, c_num) =
1107 NVL(l_Param_Set_tbl_orig(i).Target_Level_ID, c_num)) AND
1108 (NVL(l_Param_Set_tbl(k).Plan_ID, c_num) =
1109 NVL(l_Param_Set_tbl_orig(i).Plan_ID, c_num)) AND
1110 (NVL(l_Param_Set_tbl(k).TIME_DIMENSION_LEVEL_ID, c_num) =
1111 NVL(l_Param_Set_tbl_orig(i).TIME_DIMENSION_LEVEL_ID, c_num)) AND
1112 (NVL(l_Param_Set_tbl(k).PARAMETER1_VALUE, c_var) =
1113 NVL(l_Param_Set_tbl_orig(i).PARAMETER1_VALUE, c_var)) AND
1114 (NVL(l_Param_Set_tbl(k).PARAMETER2_VALUE, c_var) =
1115 NVL(l_Param_Set_tbl_orig(i).PARAMETER2_VALUE, c_var)) AND
1116 (NVL(l_Param_Set_tbl(k).PARAMETER3_VALUE, c_var) =
1117 NVL(l_Param_Set_tbl_orig(i).PARAMETER3_VALUE, c_var)) AND
1118 (NVL(l_Param_Set_tbl(k).PARAMETER4_VALUE, c_var) =
1119 NVL(l_Param_Set_tbl_orig(i).PARAMETER4_VALUE, c_var)) AND
1120 (NVL(l_Param_Set_tbl(k).PARAMETER5_VALUE, c_var) =
1121 NVL(l_Param_Set_tbl_orig(i).PARAMETER5_VALUE, c_var)) AND
1122 (NVL(l_Param_Set_tbl(k).PARAMETER6_VALUE, c_var) =
1123 NVL(l_Param_Set_tbl_orig(i).PARAMETER6_VALUE, c_var)) AND
1124 (NVL(l_Param_Set_tbl(k).PARAMETER7_VALUE, c_var) =
1125 NVL(l_Param_Set_tbl_orig(i).PARAMETER7_VALUE, c_var))
1126 ) THEN
1127 l_flag := TRUE;
1128 EXIT;
1129 END IF;
1130 END LOOP;
1131 IF NOT (l_flag) THEN
1132 l_count_param_set_tbl := l_Param_Set_tbl.COUNT+1;
1133 l_Param_Set_tbl(l_count_param_set_tbl) := l_Param_Set_tbl_orig(i);
1134 END IF;
1135 END LOOP;
1136
1137 BIS_UTILITIES_PUB.put_line(p_text =>'l_Param_Set_tbl_orig.COUNT='||l_Param_Set_tbl_orig.COUNT);
1138 BIS_UTILITIES_PUB.put_line(p_text =>'l_Param_Set_tbl.COUNT='||l_Param_Set_tbl.COUNT);
1139 BIS_UTILITIES_PUB.put_line(p_text =>'x_Alert_request_Tbl.COUNT='||x_Alert_request_Tbl.COUNT);
1140
1141 l_count := x_Alert_request_Tbl.COUNT;
1142 FOR i IN 1..l_Param_Set_tbl.COUNT LOOP
1143 IF l_Param_Set_tbl(i).TARGET_LEVEL_ID IS NOT NULL THEN
1144 l_count := l_count+1;
1145 x_Alert_request_Tbl(l_count).measure_id := l_Param_Set_tbl(i).PERFORMANCE_MEASURE_ID;
1146 x_Alert_request_Tbl(l_count).target_level_id := l_Param_Set_tbl(i).TARGET_LEVEL_ID;
1147 x_Alert_request_Tbl(l_count).time_level_id := l_Param_Set_tbl(i).TIME_DIMENSION_LEVEL_ID;
1148 x_Alert_request_Tbl(l_count).plan_id := l_Param_Set_tbl(i).PLAN_ID;
1149 x_Alert_request_Tbl(l_count).dim1_level_value_id := l_Param_Set_tbl(i).PARAMETER1_VALUE;
1150 x_Alert_request_Tbl(l_count).dim2_level_value_id := l_Param_Set_tbl(i).PARAMETER2_VALUE;
1151 x_Alert_request_Tbl(l_count).dim3_level_value_id := l_Param_Set_tbl(i).PARAMETER3_VALUE;
1152 x_Alert_request_Tbl(l_count).dim4_level_value_id := l_Param_Set_tbl(i).PARAMETER4_VALUE;
1153 x_Alert_request_Tbl(l_count).dim5_level_value_id := l_Param_Set_tbl(i).PARAMETER5_VALUE;
1154 x_Alert_request_Tbl(l_count).dim6_level_value_id := l_Param_Set_tbl(i).PARAMETER6_VALUE;
1155 x_Alert_request_Tbl(l_count).dim7_level_value_id := l_Param_Set_tbl(i).PARAMETER7_VALUE;
1156 x_Alert_request_Tbl(l_count).alert_type := p_alert_type;
1157 x_Alert_request_Tbl(l_count).alert_level := p_alert_level;
1158 x_Alert_request_Tbl(l_count).alert_based_on := p_alert_based_on;
1159 x_Alert_request_Tbl(l_count).current_row := p_current_row;
1160 END IF;
1161 END LOOP;
1162 BIS_UTILITIES_PUB.put_line(p_text =>'Total number of requests to be submitted: '
1163 ||x_Alert_request_Tbl.count);
1164
1165 EXCEPTION
1166 when FND_API.G_EXC_ERROR then
1167 x_return_status := FND_API.G_RET_STS_ERROR ;
1168 BIS_UTILITIES_PUB.put_line(p_text =>'Exception at Retrieve_All_Request_Rows: '||sqlerrm);
1169 when FND_API.G_EXC_UNEXPECTED_ERROR then
1170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1171 BIS_UTILITIES_PUB.put_line(p_text =>'Exception at Retrieve_All_Request_Rows: '||sqlerrm);
1172 when others then
1173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1174 BIS_UTILITIES_PUB.put_line(p_text =>'Exception at Retrieve_All_Request_Rows: '||sqlerrm);
1175 l_error_tbl := x_error_tbl;
1176 BIS_UTILITIES_PVT.Add_Error_Message
1177 ( p_error_msg_id => SQLCODE
1178 , p_error_description => SQLERRM
1179 , p_error_proc_name => 'Retrieve_All_Request_Rows'
1180 , p_error_table => l_error_tbl
1181 , x_error_table => x_error_tbl
1182 );
1183 End Retrieve_All_Request_Rows;
1184
1185
1186 END BIS_ALERT_SERVICE_PVT;