[Home] [Help]
PACKAGE BODY: APPS.BIS_PMF_REG_SERVICE_PVT
Source
1 package body BIS_PMF_REG_SERVICE_PVT as
2 /* $Header: BISVARSB.pls 120.0 2005/05/31 18:29:21 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'BIS_PMF_REG_SERVICE_PVT';
5
6 l_debug_text VARCHAR2(32000);
7
8 TYPE report_data_tbl_type IS TABLE OF VARCHAR2(32000)
9 INDEX BY BINARY_INTEGER;
10
11
12 --
13 -- Procedure Which Accepts the parameters Performance measure
14 -- and all dimension level ids from the Alert
15 -- Registration screen and determines which level is time.
16 -- then submits concurrent request.
17 --
18 PROCEDURE submit_parameter_set_request
19 ( p_StartTime IN varchar2 default null
20 , p_EndTime IN varchar2 default null
21 , p_frequencyInterval IN varchar2 default null
22 , p_frequencyUnits IN varchar2 default null
23 , p_request_id OUT NOCOPY varchar2
24 , p_perf_measure_id IN varchar2 default null
25 , p_time_level_id IN varchar2 default null
26 , p_parameter1_level IN varchar2 default null
27 , p_parameter2_level IN varchar2 default null
28 , p_parameter3_level IN varchar2 default null
29 , p_parameter4_level IN varchar2 default null
30 , p_parameter5_level IN varchar2 default null
31 , p_parameter6_level IN varchar2 default null
32 , p_parameter7_level IN varchar2 default null
33 , p_session_id IN varchar2 default null
34 , p_alert_type IN varchar2 default null
35 , p_notify_owners_flag IN varchar2 default null
36 , p_current_row IN VARCHAR2 := 'N'
37 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
38 ) ;
39
40 --
41 -- Procedure Which Accepts the parameters Performance measure
42 -- and time dimension level id from the Alert
43 -- Schedulling screen, processes the information
44 -- and submits the concurrent request.
45 --
46 PROCEDURE submit_parameter_set_request
47 ( p_StartTime IN varchar2 default null
48 ,p_EndTime IN varchar2 default null
49 ,p_frequencyInterval IN varchar2 default null
50 ,p_frequencyUnits IN varchar2 default null
51 ,p_request_id OUT NOCOPY varchar2
52 ,p_perf_measure_id IN varchar2 default null
53 ,p_time_dim_level_id IN varchar2 default null
54 ,p_session_id IN varchar2 default null
55 ,p_alert_type IN varchar2 default null
56 ,p_notify_owners_flag IN varchar2 default null
57 , p_current_row IN VARCHAR2 := 'N'
58 ,p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
59 );
60
61 --
62 -- Procedure Which Accepts the parameters Performance measure
63 -- and dimension level value id from the Alert
64 -- Request screen, processes the information
65 -- and adds the user to the request.
66 --
67 PROCEDURE add_parameter_request
68 ( p_perf_measure_id IN varchar2 default null
69 ,p_target_level_id IN varchar2 default null
70 ,p_time_dim_level_id IN varchar2 default null
71 ,p_notifiers IN varchar2 default null
72 ,p_plan_id IN varchar2 default null
73 ,p_parameter1_level IN varchar2 default null
74 ,p_parameter1_value IN varchar2 default null
75 ,p_parameter2_level IN varchar2 default null
76 ,p_parameter2_value IN varchar2 default null
77 ,p_parameter3_level IN varchar2 default null
78 ,p_parameter3_value IN varchar2 default null
79 ,p_parameter4_level IN varchar2 default null
80 ,p_parameter4_value IN varchar2 default null
81 ,p_parameter5_level IN varchar2 default null
82 ,p_parameter5_value IN varchar2 default null
83 ,p_parameter6_level IN varchar2 default null
84 ,p_parameter6_value IN varchar2 default null
85 ,p_parameter7_level IN varchar2 default null
86 ,p_parameter7_value IN varchar2 default null
87 ,p_alert_type IN varchar2 default null
88 ,p_session_id IN varchar2 default null
89 ,p_current_row IN VARCHAR2 := 'N'
90 ,p_notify_owner_flag IN VARCHAR2
91 ,x_request_id OUT NOCOPY varchar2
92 );
93
94 --
95 -- Procedure Which Accepts the parameters Performance measure
96 -- and time dimension level id from the BIS Report
97 -- processes the information and submits the concurrent request.
98 --
99 PROCEDURE process_report_set_request
100 ( p_StartTime IN varchar2 default null
101 ,p_EndTime IN varchar2 default null
102 ,p_frequencyInterval IN varchar2 default null
103 ,p_frequencyUnits IN varchar2 default null
104 ,p_perf_measure_id IN varchar2 default null
105 ,p_time_dim_level_id IN varchar2 default null
106 ,p_notifiers IN varchar2 default null
107 ,p_plan_id IN varchar2 default null
108 ,p_parameter1_level IN varchar2 default null
109 ,p_parameter1_value IN varchar2 default null
110 ,p_parameter2_level IN varchar2 default null
111 ,p_parameter2_value IN varchar2 default null
112 ,p_parameter3_level IN varchar2 default null
113 ,p_parameter3_value IN varchar2 default null
114 ,p_parameter4_level IN varchar2 default null
115 ,p_parameter4_value IN varchar2 default null
116 ,p_parameter5_level IN varchar2 default null
117 ,p_parameter5_value IN varchar2 default null
118 ,p_parameter6_level IN varchar2 default null
119 ,p_parameter6_value IN varchar2 default null
120 ,p_parameter7_level IN varchar2 default null
121 ,p_parameter7_value IN varchar2 default null
122 , p_viewby_level_id IN varchar2 default null
123 ,p_session_id IN varchar2 default null
124 ,p_alert_type IN varchar2 default null
125 ,p_notify_owners_flag IN varchar2 default null
126 ,p_current_row IN VARCHAR2 := 'N'
127 ,x_request_id OUT NOCOPY varchar2
128 );
129
130 --
131 -- Procedure Which Accepts the parameters Performance measure,
132 -- dimension level and value ids from the BIS Report parameter page
133 -- and requests the reports to be generated in the background.
134 --
135 PROCEDURE process_batch_report_request
136 ( p_StartTime IN varchar2 default null
137 , p_EndTime IN varchar2 default null
138 , p_frequencyInterval IN varchar2 default null
139 , p_frequencyUnits IN varchar2 default null
140 , p_perf_measure_id IN varchar2 default null
141 , p_time_dim_level_id IN varchar2 default null
142 , p_notifiers IN varchar2 default null
143 , p_plan_id IN varchar2 default null
144 , p_parameter1_level IN varchar2 default null
145 , p_parameter1_value IN varchar2 default null
146 , p_parameter2_level IN varchar2 default null
147 , p_parameter2_value IN varchar2 default null
148 , p_parameter3_level IN varchar2 default null
149 , p_parameter3_value IN varchar2 default null
150 , p_parameter4_level IN varchar2 default null
151 , p_parameter4_value IN varchar2 default null
152 , p_parameter5_level IN varchar2 default null
153 , p_parameter5_value IN varchar2 default null
154 , p_parameter6_level IN varchar2 default null
155 , p_parameter6_value IN varchar2 default null
156 , p_parameter7_level IN varchar2 default null
157 , p_parameter7_value IN varchar2 default null
158 ,p_viewby_level_id IN varchar2 default null
159 , p_session_id IN varchar2 default null
160 , p_alert_type IN varchar2 default null
161 , p_notify_owners_flag IN varchar2 default null
162 , p_current_row IN VARCHAR2 := 'N'
163 , x_request_id OUT NOCOPY varchar2
164 );
165
166 -- compares new to orig. if a row in new is not in orig, that row is
167 -- added to the diff table. all rows (including new) is put into
168 -- all table.
169 --
170 PROCEDURE Compare_param_sets
171 ( p_param_set_tbl_orig IN BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
172 , p_param_set_tbl_new IN BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
173 , x_param_set_tbl_all OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
174 , x_param_set_tbl_diff OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
175 );
176
177 Procedure Get_current_Target
178 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
179 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
180 --, p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
181 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
182 , x_return_status OUT NOCOPY VARCHAR2
183 );
184
185 Procedure Get_Previous_Target
186 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
187 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
188 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
189 , x_return_status OUT NOCOPY VARCHAR2
190 );
191
192
193 Procedure update_total_time
194 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
195 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
196 --, p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
197 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
198 , x_return_status OUT NOCOPY VARCHAR2
199 );
200
201 --
202 -- retrieves data based on BIS Report Generator reports
203 --
204 PROCEDURE Retrieve_Report_Info
205 ( p_measure_id IN NUMBER
206 , p_time_level_id IN NUMBER default null
207 ,p_plan_id IN NUMBER default null
208 ,p_parameter1_level IN varchar2 default null
209 ,p_parameter1_value IN varchar2 default null
210 ,p_parameter2_level IN varchar2 default null
211 ,p_parameter2_value IN varchar2 default null
212 ,p_parameter3_level IN varchar2 default null
213 ,p_parameter3_value IN varchar2 default null
214 ,p_parameter4_level IN varchar2 default null
215 ,p_parameter4_value IN varchar2 default null
216 ,p_parameter5_level IN varchar2 default null
217 ,p_parameter5_value IN varchar2 default null
218 ,p_parameter6_level IN varchar2 default null
219 ,p_parameter6_value IN varchar2 default null
220 ,p_parameter7_level IN varchar2 default null
221 ,p_parameter7_value IN varchar2 default null
222 , p_viewby_level_id IN varchar2 default null
223 , x_target_level_tbl OUT NOCOPY BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_type
224 , x_target_tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
225 , x_return_status OUT NOCOPY VARCHAR2
226 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
227 );
228
229 --
230 -- Helper routine which calls fnd_request APIs
231 -- For scheduled alerts
232 --
233 PROCEDURE submit_conc_request
234 ( p_request_id OUT NOCOPY varchar2
235 , p_StartTime IN varchar2 default null
236 , p_EndTime IN varchar2 default null
237 , p_frequencyInterval IN varchar2 default null
238 , p_frequencyUnits IN varchar2 default null
239 , p_performance_measure_id IN NUMBER default null
240 , p_time_dimension_level_id IN NUMBER default null
241 , p_session_id IN varchar2 default null
242 , p_current_row IN varchar2 default null
243 , p_alert_type IN varchar2 default null
244 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
245 );
246
247 --
248 -- Helper routine which calls fnd_request APIs
249 -- For scheduled reports
250 --
251 PROCEDURE submit_Concurrent_Request
252 ( p_report_data_Tbl IN report_data_tbl_type
253 ,p_performance_measure_id IN NUMBER default null
254 ,p_time_dimension_level_id IN NUMBER default null
255 ,p_session_id IN varchar2 default null
256 ,p_StartTime IN varchar2 default null
257 ,p_EndTime IN varchar2 default null
258 ,p_frequencyInterval IN varchar2 default null
259 ,p_frequencyUnits IN varchar2 default null
260 ,p_current_row IN varchar2 default null
261 , x_request_id OUT NOCOPY VARCHAR2
262 );
263
264 -- helper procedure to determine if target owners should
265 -- be notified depending on the type of alert
266 --
267 Procedure Set_Notify_Owners
268 ( p_alert_type IN VARCHAR2
269 , x_notify_owners_flag OUT NOCOPY VARCHAR2
270 );
271
272
273 PROCEDURE Add_Subscribers
274 ( p_Param_Set_rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
275 , x_request_id OUT NOCOPY varchar2
276 , x_return_status OUT NOCOPY varchar2
277 );
278
279 Procedure Form_Concurrent_Request
280 ( p_request_desc IN VARCHAR2
281 , p_Start_Time IN VARCHAR2
282 , p_measure_id IN NUMBER
283 , p_Measure_short_name IN VARCHAR2
284 , p_time_level_id IN NUMBER
285 , p_alert_type IN VARCHAR2
286 , p_current_row IN VARCHAR2
287 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
288 , x_Concurrent_Request_rec
289 OUT NOCOPY BIS_CONCURRENT_MANAGER_PVT.PMF_Request_rec_Type
290 );
291
292 PROCEDURE Verify_Target_Level
293 ( p_Target_Level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
294 , p_parameter1_level IN varchar2 default null
295 , p_parameter2_level IN varchar2 default null
296 , p_parameter3_level IN varchar2 default null
297 , p_parameter4_level IN varchar2 default null
298 , p_parameter5_level IN varchar2 default null
299 , p_parameter6_level IN varchar2 default null
300 , p_parameter7_level IN varchar2 default null
301 , p_viewby_level_id IN varchar2 default null
302 , x_Target_Level_rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
303 );
304
305 PROCEDURE Verify_Target
306 ( p_Target_rec IN BIS_Target_PUB.Target_Rec_Type
307 , p_parameter1_value IN varchar2 default null
308 , p_parameter2_value IN varchar2 default null
309 , p_parameter3_value IN varchar2 default null
310 , p_parameter4_value IN varchar2 default null
311 , p_parameter5_value IN varchar2 default null
312 , p_parameter6_value IN varchar2 default null
313 , p_parameter7_value IN varchar2 default null
314 , x_Target_rec OUT NOCOPY BIS_Target_PUB.Target_Rec_Type
315 );
316
317 FUNCTION has_Dimension_Levels
318 ( p_Target_Level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
319 , p_parameter1_level IN varchar2 default null
320 , p_parameter2_level IN varchar2 default null
321 , p_parameter3_level IN varchar2 default null
322 , p_parameter4_level IN varchar2 default null
323 , p_parameter5_level IN varchar2 default null
324 , p_parameter6_level IN varchar2 default null
325 , p_parameter7_level IN varchar2 default null
326 , p_viewby_level_id IN varchar2 default null
327 )
328 RETURN BOOLEAN;
329
330 FUNCTION has_Dimension_Level_Values
331 ( p_Target_rec IN BIS_Target_PUB.Target_Rec_Type
332 , p_parameter1_value IN varchar2 default null
333 , p_parameter2_value IN varchar2 default null
334 , p_parameter3_value IN varchar2 default null
335 , p_parameter4_value IN varchar2 default null
336 , p_parameter5_value IN varchar2 default null
337 , p_parameter6_value IN varchar2 default null
338 , p_parameter7_value IN varchar2 default null
339 )
340 RETURN BOOLEAN;
341
342 PROCEDURE check_View_by
343 ( p_parameter1_level IN varchar2 default null
344 , p_parameter2_level IN varchar2 default null
345 , p_parameter3_level IN varchar2 default null
346 , p_parameter4_level IN varchar2 default null
347 , p_parameter5_level IN varchar2 default null
348 , p_parameter6_level IN varchar2 default null
349 , p_parameter7_level IN varchar2 default null
350 , p_viewby_level_id IN varchar2 default null
351 , sameViewBy OUT NOCOPY BOOLEAN
352 , viewByLevelNum OUT NOCOPY NUMBER
353 );
354
355 FUNCTION is_time_level
356 (p_Dimension_Level_rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_rec_Type)
357 RETURN BOOLEAN;
358
359 --
360 PROCEDURE filter_duplicates (
361 p_target_tbl IN BIS_TARGET_PUB.target_tbl_type
362 ,x_target_tbl OUT NOCOPY BIS_TARGET_PUB.target_tbl_type
363 );
364
365 --
366 PROCEDURE filter_alert_based_on (
367 p_target_tbl IN BIS_TARGET_PUB.target_tbl_type
368 ,p_alert_based_on IN VARCHAR2
369 ,x_target_tbl OUT NOCOPY BIS_TARGET_PUB.target_tbl_type
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ) ;
372
373 --
374 -- Procedure Which Accepts all the parameters from the Alert
375 -- Registration screen, processes the information
376 -- and submits the request.
377 --
378 PROCEDURE process_parameter_full_set
379 ( p_request_id OUT NOCOPY varchar2
380 ,p_StartTime IN varchar2 default null
381 ,p_EndTime IN varchar2 default null
382 ,p_frequencyInterval IN varchar2 default null
383 ,p_frequencyUnits IN varchar2 default null
384 ,p_perf_measure_id IN varchar2 default null
385 ,p_target_level_id IN varchar2 default null
386 ,p_time_dim_level_id IN varchar2 default null
387 ,p_notifiers IN varchar2 default null
388 ,p_plan_id IN varchar2 default null
389 ,p_parameter1_level IN varchar2 default null
390 ,p_parameter1_value IN varchar2 default null
391 ,p_parameter2_level IN varchar2 default null
392 ,p_parameter2_value IN varchar2 default null
393 ,p_parameter3_level IN varchar2 default null
394 ,p_parameter3_value IN varchar2 default null
395 ,p_parameter4_level IN varchar2 default null
396 ,p_parameter4_value IN varchar2 default null
397 ,p_parameter5_level IN varchar2 default null
398 ,p_parameter5_value IN varchar2 default null
399 ,p_parameter6_level IN varchar2 default null
400 ,p_parameter6_value IN varchar2 default null
401 ,p_parameter7_level IN varchar2 default null
402 ,p_parameter7_value IN varchar2 default null
403 ,p_view_by_level_id IN varchar2 default null
404 ,p_alert_type IN varchar2 default null
405 ,p_session_id IN varchar2 default null
406 ,p_notify_owners_flag IN varchar2 default null
407 , p_current_row IN VARCHAR2 := 'N'
408 ,p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
409 )
410 IS
411
412 l_report_data_tbl report_data_tbl_type;
413 l_target_level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
414 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
415 l_dimension_level_number NUMBER;
416 l_param_set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
417 l_param_set_tbl_new BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
418 l_param_set_tbl_orig BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
419 l_param_set_tbl_all BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
420 l_param_set_tbl_diff BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
421
422 l_target_level_id NUMBER;
423 l_notify_owners_flag VARCHAR2(100);
424 -- l_user_id VARCHAR2(100);
425 l_return_status VARCHAR2(1000);
426 l_return_msg VARCHAR2(32000);
427 l_request_id VARCHAR2(32000);
428 l_debug VARCHAR2(32000);
429 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
430 l_api_version NUMBER := 1;
431 l_object_tbl BIS_COMPUTED_ACTUAL_PVT.object_tbl_type;
432 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
433 l_measure_short_name VARCHAR2(32000);
434 l_measure_name VARCHAR2(32000);
435 l_Measure_Rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
436 l_target_level_rec_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
437 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
438 l_alert_based_on VARCHAR2(10);
439 BEGIN
440
441 l_alert_based_on := p_alert_based_on;
442 IF (l_alert_based_on IS NULL) THEN
443 l_alert_based_on := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET;
444 END IF;
445
446 -- Debug messages should not be printed (irrespective of debug profile option).
447 bis_utilities_pvt.set_debug_log_flag ( -- 2715218
448 p_is_true => FALSE
449 , x_return_status => l_return_status
450 , x_return_msg => l_return_msg
451 ) ;
452
453
454 BIS_UTILITIES_PUB.put_line(p_text =>'alert type: '||p_alert_type);
455
456
457 IF p_perf_measure_id IS NOT NULL THEN
458 l_measure_rec.measure_id := p_perf_measure_id;
459 l_measure_rec_p := l_measure_rec;
460 BIS_Measure_PVT.Retrieve_Measure
461 ( p_api_version => l_api_version
462 , p_measure_rec => l_measure_rec_p
463 , p_all_info => FND_API.G_FALSE
464 , x_measure_rec => l_measure_rec
465 , x_return_status => l_return_status
466 , x_error_Tbl => l_error_Tbl
467 );
468 l_measure_short_name := l_measure_rec.measure_short_name;
469 l_measure_name := l_measure_rec.measure_name;
470
471 -- dbms_output.put_line('bisvarsb. process_parameter full set 0002 ');
472
473
474 ELSIF p_target_level_id IS NOT NULL THEN
475 l_Target_Level_rec.Target_Level_id := p_target_level_id;
476 l_Target_Level_rec_p := l_Target_Level_rec;
477 BIS_Target_Level_PVT.Retrieve_Target_Level
478 ( p_api_version => l_api_version
479 , p_Target_Level_rec => l_Target_Level_rec_p
480 , p_all_info => FND_API.G_TRUE
481 , x_Target_Level_rec => l_Target_Level_rec
482 , x_return_status => l_return_status
483 , x_error_Tbl => l_error_Tbl
484 );
485 l_measure_short_name := l_Target_Level_rec.measure_short_name;
486 l_measure_name := l_Target_Level_rec.measure_name;
487
488 -- dbms_output.put_line('bisvarsb. process_parameter full set 0003 ');
489
490 END IF;
491
492 -- checking if AK region for this measure is defined.
493 -- if not, simply return with message telling user the proble.
494 -- if so, continue
495 --
496 BIS_COMPUTED_ACTUAL_PVT.Get_Related_Objects
497 ( p_measure_short_name => l_measure_short_name
498 , x_object_tbl => l_object_tbl
499 , x_return_status => l_return_status
500 );
501
502 -- dbms_output.put_line('bisvarsb. process_parameter full set 0004 ');
503
504 IF l_object_tbl.COUNT < 1 THEN
505 --p_request_id := '-1';
506 p_request_id
507 := BIS_UTILITIES_PVT.Get_FND_Message
508 ( p_message_name => 'BIS_AK_NOT_SET_UP_MSG'
509 , p_msg_param1 => 'MEASURE_NAME'
510 , p_msg_param1_val => bis_utilities_pvt.escape_html(l_measure_name)
511 );
512
513 -- dbms_output.put_line('bisvarsb. process_parameter full set 0005 ');
514
515 return;
516 ELSE
517
518 -- dbms_output.put_line('bisvarsb. process_parameter full set 0002 ');
519
520 -- l_user_id := ICX_SEC.getID(ICX_SEC.PV_USER_ID, p_session_id);
521
522 -- dbms_output.put_line('bisvarsb. process_parameter full set 0006 ');
523
524 Set_Notify_Owners
525 ( p_alert_type => p_alert_type
526 , x_notify_owners_flag => l_notify_owners_flag
527 );
528
529 -- dbms_output.put_line('bisvarsb. process_parameter full set 0007 ');
530
531 IF UPPER(p_alert_type) = G_TARGET_LEVEL
532 OR p_alert_type IS NULL THEN
533
534 l_debug := l_debug ||', 1: '||p_parameter1_level
535 ||', 2: '||p_parameter2_level||' ';
536 submit_parameter_set_request
537 ( p_StartTime => p_StartTime
538 , p_EndTime => p_EndTime
539 , p_frequencyInterval => p_frequencyInterval
540 , p_frequencyUnits => p_frequencyUnits
541 , p_request_id => l_request_id
542 , p_perf_measure_id => p_perf_measure_id
543 , p_time_level_id => p_time_dim_level_id
544 , p_parameter1_level => p_parameter1_level
545 , p_parameter2_level => p_parameter2_level
546 , p_parameter3_level => p_parameter3_level
547 , p_parameter4_level => p_parameter4_level
548 , p_parameter5_level => p_parameter5_level
549 , p_parameter6_level => p_parameter6_level
550 , p_parameter7_level => p_parameter7_level
551 , p_session_id => p_session_id
552 , p_alert_type => p_alert_type
553 , p_notify_owners_flag => l_notify_owners_flag
554 , p_current_row => p_current_row
555 , p_alert_based_on => l_alert_based_on
556 );
557
558 -- dbms_output.put_line('bisvarsb. process_parameter full set 0008 ');
559
560 ELSIF UPPER(p_alert_type) = G_ALL_TARGET THEN
561
562 add_parameter_request
563 ( p_perf_measure_id => p_perf_measure_id
564 , p_target_level_id => p_target_level_id
565 , p_time_dim_level_id => p_time_dim_level_id
566 , p_notifiers => p_notifiers
567 , p_plan_id => p_plan_id
568 , p_parameter1_level => p_parameter1_level
569 , p_parameter1_value => p_parameter1_value
570 , p_parameter2_level => p_parameter2_level
571 , p_parameter2_value => p_parameter2_value
572 , p_parameter3_level => p_parameter3_level
573 , p_parameter3_value => p_parameter3_value
574 , p_parameter4_level => p_parameter4_level
575 , p_parameter4_value => p_parameter4_value
576 , p_parameter5_level => p_parameter5_level
577 , p_parameter5_value => p_parameter5_value
578 , p_parameter6_level => p_parameter6_level
579 , p_parameter6_value => p_parameter6_value
580 , p_parameter7_level => p_parameter7_level
581 , p_parameter7_value => p_parameter7_value
582 , p_alert_type => p_alert_type
583 , p_session_id => p_session_id
584 , p_current_row => p_current_row
585 , p_notify_owner_flag => l_notify_owners_flag
586 , x_request_id => l_request_id
587 );
588
589 -- dbms_output.put_line('bisvarsb. process_parameter full set 0009 ');
590
591 ELSIF UPPER(p_alert_type) = G_REPORT_GEN THEN
592
593 process_report_set_request
594 ( p_StartTime => p_StartTime
595 , p_EndTime => p_EndTime
596 , p_frequencyInterval => p_frequencyInterval
597 , p_frequencyUnits => p_frequencyUnits
598 , p_perf_measure_id => p_perf_measure_id
599 , p_time_dim_level_id => p_time_dim_level_id
600 , p_notifiers => p_notifiers
601 , p_plan_id => p_plan_id
602 , p_parameter1_level => p_parameter1_level
603 , p_parameter1_value => p_parameter1_value
604 , p_parameter2_level => p_parameter2_level
605 , p_parameter2_value => p_parameter2_value
606 , p_parameter3_level => p_parameter3_level
607 , p_parameter3_value => p_parameter3_value
608 , p_parameter4_level => p_parameter4_level
609 , p_parameter4_value => p_parameter4_value
610 , p_parameter5_level => p_parameter5_level
611 , p_parameter5_value => p_parameter5_value
612 , p_parameter6_level => p_parameter6_level
613 , p_parameter6_value => p_parameter6_value
614 , p_parameter7_level => p_parameter7_level
615 , p_parameter7_value => p_parameter7_value
616 , p_viewBy_level_id => p_view_by_level_id
617 , p_session_id => p_session_id
618 , p_alert_type => p_alert_type
619 , p_notify_owners_flag => l_notify_owners_flag
620 , p_current_row => p_current_row
621 , x_request_id => l_request_id
622 );
623
624 -- dbms_output.put_line('bisvarsb. process_parameter full set 0002 ');
625
626 ELSIF UPPER(p_alert_type) = G_REPORT_BATCH THEN
627
628 -- not done. waiting for Reports Gen team
629 --
630 process_batch_report_request
631 ( p_StartTime => p_StartTime
632 , p_EndTime => p_EndTime
633 , p_frequencyInterval => p_frequencyInterval
634 , p_frequencyUnits => p_frequencyUnits
635 , p_perf_measure_id => p_perf_measure_id
636 , p_time_dim_level_id => p_time_dim_level_id
637 , p_notifiers => p_notifiers
638 , p_plan_id => p_plan_id
639 , p_parameter1_level => p_parameter1_level
640 , p_parameter1_value => p_parameter1_value
641 , p_parameter2_level => p_parameter2_level
642 , p_parameter2_value => p_parameter2_value
643 , p_parameter3_level => p_parameter3_level
644 , p_parameter3_value => p_parameter3_value
645 , p_parameter4_level => p_parameter4_level
646 , p_parameter4_value => p_parameter4_value
647 , p_parameter5_level => p_parameter5_level
648 , p_parameter5_value => p_parameter5_value
649 , p_parameter6_level => p_parameter6_level
650 , p_parameter6_value => p_parameter6_value
651 , p_parameter7_level => p_parameter7_level
652 , p_parameter7_value => p_parameter7_value
653 , p_viewBy_level_id => p_view_by_level_id
654 , p_session_id => p_session_id
655 , p_alert_type => p_alert_type
656 , p_notify_owners_flag => l_notify_owners_flag
657 , p_current_row => p_current_row
658 , x_request_id => l_request_id
659 );
660
661 -- dbms_output.put_line('bisvarsb. process_parameter full set 0002 ');
662
663 END IF;
664
665 -- dbms_output.put_line('bisvarsb. process_parameter full set 0002 ');
666
667 --p_request_id := l_request_id ||l_debug;
668 p_request_id := l_request_id;
669
670 END IF;
671
672 EXCEPTION
673 when FND_API.G_EXC_ERROR then
674 l_return_status := FND_API.G_RET_STS_ERROR ;
675 l_debug :=l_debug||' exception 1 at process_parameter_full_set. '||sqlerrm;
676 p_request_id := l_request_id ||l_debug;
677 when FND_API.G_EXC_UNEXPECTED_ERROR then
678 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
679 l_debug :=l_debug||' exception 2 at process_parameter_full_set. '||sqlerrm;
680 p_request_id := l_request_id ||l_debug;
681 when others then
682 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 l_debug :=l_debug||' exception 3 at process_parameter_full_set. '||sqlerrm;
684 p_request_id := l_request_id ||l_debug;
685 l_error_tbl_p := l_error_tbl;
686 BIS_UTILITIES_PVT.Add_Error_Message
687 ( p_error_msg_id => SQLCODE
688 , p_error_description => SQLERRM
689 , p_error_proc_name => 'Process_Parameter_set'
690 , p_error_table => l_error_tbl_p
691 , x_error_table => l_error_tbl
692 );
693
694 end process_parameter_full_set;
695
696 --
697 -- Procedure Which Accepts the parameters Performance measure
698 -- and all dimension level ids from the Alert
699 -- Registration screen and determines which level is time.
700 -- then submits concurrent request.
701 --
702 PROCEDURE submit_parameter_set_request
703 ( p_StartTime IN varchar2 default null
704 , p_EndTime IN varchar2 default null
705 , p_frequencyInterval IN varchar2 default null
706 , p_frequencyUnits IN varchar2 default null
707 , p_request_id OUT NOCOPY varchar2
708 , p_perf_measure_id IN varchar2 default null
709 , p_time_level_id IN varchar2 default null
710 , p_parameter1_level IN varchar2 default null
711 , p_parameter2_level IN varchar2 default null
712 , p_parameter3_level IN varchar2 default null
713 , p_parameter4_level IN varchar2 default null
714 , p_parameter5_level IN varchar2 default null
715 , p_parameter6_level IN varchar2 default null
716 , p_parameter7_level IN varchar2 default null
717 , p_session_id IN varchar2 default null
718 , p_alert_type IN varchar2 default null
719 , p_notify_owners_flag IN varchar2 default null
720 , p_current_row IN VARCHAR2 := 'N'
721 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
722 )
723 IS
724
725 l_time_level_id NUMBER := NULL;
726 l_Dimension_Level_Tbl BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type;
727 l_debug VARCHAR2(32000);
728 l_request_id VARCHAR2(32000);
729
730 BEGIN
731
732 l_debug := l_debug || ', in submit_parameter_set_request2.';
733
734 IF p_parameter1_level IS NOT NULL THEN
735 l_Dimension_Level_Tbl(1).Dimension_Level_Id
736 :=TO_NUMBER(p_parameter1_level);
737 END IF;
738 IF p_parameter2_level IS NOT NULL THEN
739 l_Dimension_Level_Tbl(2).Dimension_Level_Id
740 :=TO_NUMBER(p_parameter2_level);
741 END IF;
742 IF p_parameter3_level IS NOT NULL THEN
743 l_Dimension_Level_Tbl(3).Dimension_Level_Id
744 :=TO_NUMBER(p_parameter3_level);
745 END IF;
746 IF p_parameter4_level IS NOT NULL THEN
747 l_Dimension_Level_Tbl(4).Dimension_Level_Id
748 :=TO_NUMBER(p_parameter4_level);
749 END IF;
750 IF p_parameter5_level IS NOT NULL THEN
751 l_Dimension_Level_Tbl(5).Dimension_Level_Id
752 :=TO_NUMBER(p_parameter5_level);
753 END IF;
754 IF p_parameter6_level IS NOT NULL THEN
755 l_Dimension_Level_Tbl(6).Dimension_Level_Id
756 :=TO_NUMBER(p_parameter6_level);
757 END IF;
758 IF p_parameter7_level IS NOT NULL THEN
759 l_Dimension_Level_Tbl(7).Dimension_Level_Id
760 :=TO_NUMBER(p_parameter7_level);
761 END IF;
762
763 IF p_time_level_id IS NOT NULL THEN
764 l_time_level_id := TO_NUMBER(p_time_level_id);
765 BIS_UTILITIES_PUB.put_line(p_text =>'Passed time: '||l_time_level_id);
766 ELSE
767 FOR i IN 1..l_Dimension_Level_Tbl.COUNT LOOP
768 l_debug := l_debug || ', D'||i
769 ||': '||l_Dimension_Level_Tbl(i).Dimension_Level_Id;
770 IF is_time_level(l_Dimension_Level_Tbl(i)) THEN
771 l_debug := l_debug || ', E.';
772 l_time_level_id := l_Dimension_Level_Tbl(i).Dimension_Level_Id;
773 l_debug := l_debug || ', F: '||l_time_level_id;
774 EXIT;
775 END IF;
776 END LOOP;
777 END IF;
778
779 l_debug := l_debug || ' Got time level: '||l_time_level_id;
780 submit_parameter_set_request
781 ( p_StartTime => p_StartTime
782 ,p_EndTime => p_EndTime
783 ,p_frequencyInterval => p_frequencyInterval
784 ,p_frequencyUnits => p_frequencyUnits
785 ,p_request_id => l_request_id
786 ,p_perf_measure_id => p_perf_measure_id
787 ,p_time_dim_level_id => l_time_level_id
788 ,p_session_id => p_session_id
789 ,p_alert_type => p_alert_type
790 ,p_notify_owners_flag => p_notify_owners_flag
791 , p_current_row => p_current_row
792 ,p_alert_based_on => p_alert_based_on
793 );
794 --p_request_id := l_request_id ||l_debug;
795 p_request_id := l_request_id;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 l_debug :=l_debug||' exception at submit_parameter_set_request2 '||sqlerrm;
800 p_request_id := l_request_id ||l_debug;
801
802 END submit_parameter_set_request;
803
804 --
805 -- Procedure Which Accepts the parameters Performance measure
806 -- and time dimension level id from the Alert
807 -- Registration screen and submit a concurrent request.
808 --
809 PROCEDURE submit_parameter_set_request
810 ( p_StartTime IN varchar2 default null
811 ,p_EndTime IN varchar2 default null
812 ,p_frequencyInterval IN varchar2 default null
813 ,p_frequencyUnits IN varchar2 default null
814 ,p_request_id OUT NOCOPY varchar2
815 ,p_perf_measure_id IN varchar2 default null
816 ,p_time_dim_level_id IN varchar2 default null
817 ,p_session_id IN varchar2 default null
818 ,p_alert_type IN varchar2 default null
819 ,p_notify_owners_flag IN varchar2 default null
820 , p_current_row IN VARCHAR2 := 'N'
821 ,p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
822 )
823 IS
824
825 l_param_set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
826 l_param_set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
827 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
828 l_return_status VARCHAR2(1000);
829 l_request_scheduled VARCHAR2(1000);
830 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
831 l_api_version NUMBER := 1;
832 l_Conc_exist BOOLEAN := FALSE;
833 l_request_id VARCHAR2(32000);
834 l_debug VARCHAR2(32000);
835 l_debug2 VARCHAR2(32000);
836 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
837
838 BEGIN
839
840 l_debug_text := l_debug_text||': perf measure id: '||p_perf_measure_id
841 ||', time lev id: '||p_time_dim_level_id||'-- ';
842 IF p_perf_measure_id IS NOT NULL THEN
843 l_param_set_Rec.PERFORMANCE_MEASURE_ID := to_number(p_perf_measure_id);
844 END IF;
845 IF p_time_dim_level_id IS NOT NULL THEN
846 l_param_set_Rec.TIME_DIMENSION_LEVEL_ID := to_number(p_time_dim_level_id);
847 END IF;
848
849 l_debug_text := l_debug_text||': perf measure id REC: '
850 ||l_param_set_Rec.PERFORMANCE_MEASURE_ID
851 ||', time lev id: '
852 ||l_param_set_Rec.TIME_DIMENSION_LEVEL_ID
853 ||'-- ';
854
855 BIS_UTILITIES_PUB.put_line(p_text =>'Managing measure registrations');
856 BIS_PMF_ALERT_REG_PVT.Manage_Alert_Registrations
857 ( p_Param_Set_rec => l_Param_Set_rec
858 , x_request_scheduled => l_request_scheduled
859 , x_return_status => l_return_status
860 , x_error_Tbl => l_error_Tbl
861 );
862 l_debug_text := l_debug_text||': '
863 ||'Manage_Alert_Registration Target level '||l_request_scheduled
864 ||' , '||l_return_status;
865 --BIS_UTILITIES_PUB.put_line(p_text =>'ARSB: after managing reg: '||l_debug_text);
866
867 IF l_request_scheduled = FND_API.G_TRUE THEN
868
869 --BIS_UTILITIES_PUB.put_line(p_text =>'Request exist');
870 l_debug_text := l_debug_text||': '||G_REQUEST_EXIST;
871 l_request_id := G_REQUEST_EXIST;
872
873 ELSE
874
875 --BIS_UTILITIES_PUB.put_line(p_text =>'Request does not exist');
876 process_parameter_set
877 ( p_request_id => l_request_id
878 -- , p_request_id => l_debug2
879 , p_perf_measure_id => l_param_set_Rec.performance_measure_id
880 , p_time_dim_level_id => l_param_set_Rec.time_dimension_level_id
881 , p_session_id => p_session_id
882 , p_alert_type => p_alert_type
883 , p_notify_owners_flag => p_notify_owners_flag
884 , p_current_row => p_current_row
885 );
886 l_debug_text := l_debug_text||l_debug2;
887
888 submit_conc_request
889 ( p_request_id => l_request_id
890 -- , p_request_id => l_debug2
891 , p_StartTime => p_StartTime
892 , p_EndTime => p_EndTime
893 , p_frequencyInterval => p_frequencyInterval
894 , p_frequencyUnits => p_frequencyUnits
895 , p_performance_measure_id => l_Param_Set_Rec.performance_measure_id
896 , p_time_dimension_level_id => l_Param_Set_Rec.time_dimension_level_id
897 , p_session_id => p_session_id
898 , p_current_row => p_current_row
899 , p_alert_type => p_alert_type
900 , p_alert_based_on => p_alert_based_on
901 );
902 BIS_UTILITIES_PUB.put_line(p_text =>'Detailed alert request submitted.');
903 l_debug_text := l_debug_text||l_debug2;
904
905 --p_request_id := l_request_id;
906
907 END IF;
908
909 p_request_id := l_request_id;
910 --p_request_id := l_request_id||' ..debug..'||l_debug_text;
911
912 EXCEPTION
913 when FND_API.G_EXC_ERROR then
914 l_return_status := FND_API.G_RET_STS_ERROR ;
915 p_request_id := l_request_id
916 ||' exception 1 in submit_parameter_set_request ';
917 BIS_UTILITIES_PUB.put_line(p_text =>'debug: '||l_debug_text);
918 when FND_API.G_EXC_UNEXPECTED_ERROR then
919 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
920 p_request_id := l_request_id
921 ||' exception 2 in submit_parameter_set_request ';
922 BIS_UTILITIES_PUB.put_line(p_text =>'debug: '||l_debug_text);
923 when others then
924 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
925 p_request_id := l_request_id||', debug: '||l_debug_text
926 ||' exception 3 in submit_parameter_set_request '||sqlerrm;
927 BIS_UTILITIES_PUB.put_line(p_text =>'debug: '||l_debug_text);
928 l_error_Tbl_p := l_error_Tbl;
929 BIS_UTILITIES_PVT.Add_Error_Message
930 ( p_error_msg_id => SQLCODE
931 , p_error_description => SQLERRM
932 , p_error_proc_name => 'submit_parameter_set_request'
933 , p_error_table => l_error_tbl_p
934 , x_error_table => l_error_tbl
935 );
936
937 END submit_parameter_set_request;
938
939 --
940 -- Procedure Which Accepts the parameters Performance measure
941 -- and dimension level value id from the Alert
942 -- Request screen, processes the information
943 -- and adds the user to the request.
944 --
945 PROCEDURE add_parameter_request
946 ( p_perf_measure_id IN varchar2 default null
947 ,p_target_level_id IN varchar2 default null
948 ,p_time_dim_level_id IN varchar2 default null
949 ,p_notifiers IN varchar2 default null
950 ,p_plan_id IN varchar2 default null
951 ,p_parameter1_level IN varchar2 default null
952 ,p_parameter1_value IN varchar2 default null
953 ,p_parameter2_level IN varchar2 default null
954 ,p_parameter2_value IN varchar2 default null
955 ,p_parameter3_level IN varchar2 default null
956 ,p_parameter3_value IN varchar2 default null
957 ,p_parameter4_level IN varchar2 default null
958 ,p_parameter4_value IN varchar2 default null
959 ,p_parameter5_level IN varchar2 default null
960 ,p_parameter5_value IN varchar2 default null
961 ,p_parameter6_level IN varchar2 default null
962 ,p_parameter6_value IN varchar2 default null
963 ,p_parameter7_level IN varchar2 default null
964 ,p_parameter7_value IN varchar2 default null
965 ,p_alert_type IN varchar2 default null
966 ,p_session_id IN varchar2 default null
967 ,p_current_row IN VARCHAR2 := 'N'
968 ,p_notify_owner_flag IN varchar2
969 ,x_request_id OUT NOCOPY varchar2
970 )
971 IS
972
973 l_target_level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
974 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
975 l_dimension_level_number NUMBER;
976 l_param_set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
977 l_param_set_tbl_new BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
978 l_param_set_tbl_orig BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
979 l_param_set_tbl_all BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
980 l_param_set_tbl_diff BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
981
982 l_target_level_id NUMBER;
983 l_notify_owners_flag VARCHAR2(100);
984 -- l_user_id VARCHAR2(100);
985 l_return_status VARCHAR2(1000);
986 l_request_id VARCHAR2(32000);
987 l_debug VARCHAR2(32000);
988 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
989 l_api_version NUMBER := 1;
990 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
991
992 BEGIN
993
994 IF p_target_level_id IS NOT NULL THEN
995 l_target_level_id := p_target_level_id ;
996 ELSE
997 -- Assign the appropriate values to TL table and
998 -- get the target_level_id
999 --
1000 IF p_perf_measure_id IS NOT NULL AND p_perf_measure_id <> '' THEN
1001 l_target_level_rec.Measure_ID := to_number(p_perf_measure_id);
1002 END IF;
1003
1004 IF p_parameter1_level IS NOT NULL AND p_parameter1_level <> '' THEN
1005 l_target_level_rec.Dimension1_Level_ID:= to_number(p_parameter1_level);
1006 END IF;
1007
1008 IF p_parameter2_level IS NOT NULL AND p_parameter2_level <> '' THEN
1009 l_target_level_rec.Dimension2_Level_ID:= to_number(p_parameter2_level);
1010 END IF;
1011
1012 IF p_parameter3_level IS NOT NULL AND p_parameter3_level <> '' THEN
1013 l_target_level_rec.Dimension3_Level_ID:= to_number(p_parameter3_level);
1014 END IF;
1015
1016 IF p_parameter4_level IS NOT NULL AND p_parameter4_level <> '' THEN
1017 l_target_level_rec.Dimension4_Level_ID:= to_number(p_parameter4_level);
1018 END IF;
1019
1020 IF p_parameter5_level IS NOT NULL AND p_parameter5_level <> '' THEN
1021 l_target_level_rec.Dimension5_Level_ID:= to_number(p_parameter5_level);
1022 END IF;
1023
1024 IF p_parameter6_level IS NOT NULL AND p_parameter6_level <> '' THEN
1025 l_target_level_rec.Dimension6_Level_ID:= to_number(p_parameter6_level);
1026 END IF;
1027
1028 IF p_parameter7_level IS NOT NULL AND p_parameter7_level <> '' THEN
1029 l_target_level_rec.Dimension7_Level_ID:= to_number(p_parameter7_level);
1030 END IF;
1031
1032 l_debug := l_debug||' target level rec assigned. ';
1033
1034 l_target_level_id
1035 :=BIS_TARGET_LEVEL_PVT.Get_Level_Id_From_Dimlevels(l_target_level_rec);
1036 l_debug := l_debug||' got target level id: '||l_target_level_id ||'!';
1037
1038 END IF;
1039
1040 l_Target_Level_Rec.target_level_id := l_target_level_id;
1041
1042 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
1043 ( p_api_version => 1.0
1044 , p_Target_Level_Rec => l_Target_Level_Rec
1045 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
1046 , x_dimension_level_number => l_dimension_level_number
1047 , x_return_status => l_return_status
1048 , x_error_Tbl => l_error_Tbl
1049 );
1050
1051 BIS_UTILITIES_PUB.put_line(p_text =>'Time level retrieved: '
1052 ||l_Dimension_Level_Rec.dimension_level_short_name
1053 ||', dimension level number: '||l_dimension_level_number);
1054
1055 l_debug := l_debug||' target level id: '||l_target_level_id;
1056 -- Assign all values to parameter set Rec.
1057 --
1058
1059 IF p_perf_measure_id IS NOT NULL THEN
1060 l_param_set_Rec.PERFORMANCE_MEASURE_ID := to_number(p_perf_measure_id);
1061 END IF;
1062 IF l_target_level_id IS NOT NULL THEN
1063 l_param_set_Rec.TARGET_LEVEL_ID := to_number(l_target_level_id);
1064 END IF;
1065
1066 l_param_set_Rec.TIME_DIMENSION_LEVEL_ID
1067 := l_Dimension_Level_Rec.Dimension_Level_id;
1068
1069 IF p_plan_id IS NOT NULL THEN
1070 l_param_set_Rec.PLAN_ID := to_number(p_plan_id);
1071 END IF;
1072 l_param_set_Rec.PARAMETER1_VALUE := p_parameter1_value;
1073 l_param_set_Rec.PARAMETER2_VALUE := p_parameter2_value;
1074 l_param_set_Rec.PARAMETER3_VALUE := p_parameter3_value;
1075 l_param_set_Rec.PARAMETER4_VALUE := p_parameter4_value;
1076 l_param_set_Rec.PARAMETER5_VALUE := p_parameter5_value;
1077 l_param_set_Rec.PARAMETER6_VALUE := p_parameter6_value;
1078 l_param_set_Rec.PARAMETER7_VALUE := p_parameter7_value;
1079 l_param_set_rec.notify_owner_flag := p_notify_owner_flag; --mahesh
1080 l_param_set_tbl_new(l_param_set_tbl_new.COUNT+1) := l_param_set_rec;
1081
1082 BIS_PMF_ALERT_REG_PVT.Retrieve_Parameter_set
1083 ( p_api_version => 1.0
1084 , p_measure_id => p_perf_measure_id
1085 , p_time_dimension_level_id => p_time_dim_level_id
1086 , p_current_row => p_current_row
1087 , x_Param_Set_Tbl => l_Param_Set_Tbl_orig
1088 , x_return_status => l_return_status
1089 , x_error_Tbl => l_error_Tbl
1090 );
1091 BIS_UTILITIES_PUB.put_line(p_text =>'Original number of parameter sets: '|| l_Param_Set_Tbl_orig.count);
1092
1093 -- Check if new target;
1094 -- if new, register alert into ART
1095 -- if not new, add subscribers
1096 --
1097 Compare_param_Sets
1098 ( p_param_set_tbl_orig => l_param_set_tbl_orig
1099 , p_param_set_tbl_new => l_param_set_tbl_new
1100 , x_param_set_tbl_all => l_param_set_tbl_all
1101 , x_param_set_tbl_diff => l_param_set_tbl_diff
1102 );
1103 BIS_UTILITIES_PUB.put_line(p_text =>'Parameter set comparison results: '
1104 ||' Original: '||l_param_set_tbl_orig.COUNT
1105 ||', new: '||l_param_set_tbl_new.COUNT
1106 ||', difference: '||l_param_set_tbl_diff.COUNT
1107 ||', all: '||l_param_set_tbl_all.COUNT
1108 );
1109 l_debug := l_debug
1110 ||' Original: '||l_param_set_tbl_orig.COUNT
1111 ||', new: '||l_param_set_tbl_new.COUNT
1112 ||', difference: '||l_param_set_tbl_diff.COUNT
1113 ||', all: '||l_param_set_tbl_all.COUNT;
1114
1115
1116 IF l_param_set_tbl_diff.COUNT > 0 THEN
1117 FOR i IN 1..l_param_set_tbl_diff.COUNT LOOP
1118 Register_Parameter_set
1119 ( p_api_version => l_api_version
1120 --, p_Param_Set_Rec => l_Param_Set_tbl_diff(i)
1121 , p_Param_Set_Rec => l_Param_Set_rec
1122 , p_request_id => l_request_id
1123 , x_return_status => l_return_status
1124 , x_error_Tbl => l_error_Tbl
1125 );
1126 Add_Subscribers
1127 ( p_Param_Set_Rec => l_Param_Set_rec
1128 --( p_Param_Set_rec => l_Param_Set_tbl_diff(i)
1129 , x_request_id => l_request_id
1130 , x_return_status => l_return_status
1131 );
1132 l_debug := l_debug
1133 ||' Register_Parameter_set status '||l_return_status ;
1134 l_debug := l_debug||' ---- '||l_request_id;
1135 END LOOP;
1136
1137 BIS_UTILITIES_PUB.put_line(p_text =>'New parameter sets registeration status: '||l_return_status);
1138 ELSE
1139 Add_Subscribers
1140 ( p_Param_Set_rec => l_Param_Set_rec
1141 , x_request_id => l_request_id
1142 , x_return_status => l_return_status
1143 );
1144 l_debug := l_debug||' Register_Parameter_set status '||l_return_status ;
1145 l_debug := l_debug||' ---- '||l_request_id;
1146 END IF;
1147
1148 x_request_id := l_request_id;
1149
1150 EXCEPTION
1151 when FND_API.G_EXC_ERROR then
1152 l_return_status := FND_API.G_RET_STS_ERROR ;
1153 l_debug :=l_debug||' exception 1 at add_parameter_set. '||sqlerrm;
1154 x_request_id := x_request_id ||l_debug;
1155 when FND_API.G_EXC_UNEXPECTED_ERROR then
1156 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157 l_debug :=l_debug||' exception 2 at add_parameter_set. '||sqlerrm;
1158 x_request_id := x_request_id ||l_debug;
1159 when others then
1160 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1161 l_debug :=l_debug||' exception 3 at add_parameter_set. '||sqlerrm;
1162 x_request_id := x_request_id ||l_debug;
1163 l_error_tbl_p := l_error_tbl;
1164 BIS_UTILITIES_PVT.Add_Error_Message
1165 ( p_error_msg_id => SQLCODE
1166 , p_error_description => SQLERRM
1167 , p_error_proc_name => 'Add_Parameter_set'
1168 , p_error_table => l_error_tbl_p
1169 , x_error_table => l_error_tbl
1170 );
1171
1172 END add_parameter_request;
1173
1174 --
1175 -- Procedure Which Accepts the parameters Performance measure,
1176 -- dimension level and value ids from the BIS Report
1177 -- processes the information and submits the concurrent request.
1178 --
1179 PROCEDURE process_report_set_request
1180 ( p_StartTime IN varchar2 default null
1181 , p_EndTime IN varchar2 default null
1182 , p_frequencyInterval IN varchar2 default null
1183 , p_frequencyUnits IN varchar2 default null
1184 , p_perf_measure_id IN varchar2 default null
1185 , p_time_dim_level_id IN varchar2 default null
1186 , p_notifiers IN varchar2 default null
1187 , p_plan_id IN varchar2 default null
1188 , p_parameter1_level IN varchar2 default null
1189 , p_parameter1_value IN varchar2 default null
1190 , p_parameter2_level IN varchar2 default null
1191 , p_parameter2_value IN varchar2 default null
1192 , p_parameter3_level IN varchar2 default null
1193 , p_parameter3_value IN varchar2 default null
1194 , p_parameter4_level IN varchar2 default null
1195 , p_parameter4_value IN varchar2 default null
1196 , p_parameter5_level IN varchar2 default null
1197 , p_parameter5_value IN varchar2 default null
1198 , p_parameter6_level IN varchar2 default null
1199 , p_parameter6_value IN varchar2 default null
1200 , p_parameter7_level IN varchar2 default null
1201 , p_parameter7_value IN varchar2 default null
1202 ,p_viewby_level_id IN varchar2 default null
1203 , p_session_id IN varchar2 default null
1204 , p_alert_type IN varchar2 default null
1205 , p_notify_owners_flag IN varchar2 default null
1206 , p_current_row IN VARCHAR2 := 'N'
1207 , x_request_id OUT NOCOPY varchar2
1208 )
1209 IS
1210
1211 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
1212 l_target_level_tbl BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_type;
1213 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1214 l_Alert_Request_rec BIS_ALERT_SERVICE_PVT.Alert_Request_rec_Type;
1215 l_Alert_Request_tbl BIS_ALERT_SERVICE_PVT.Alert_Request_tbl_Type;
1216 l_Concurrent_Request_Tbl BIS_CONCURRENT_MANAGER_PVT.PMF_Request_Tbl_Type;
1217 l_return_status VARCHAR2(1000);
1218 l_request_id_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1219 l_debug VARCHAR2(32000);
1220 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1221 l_errbuf VARCHAR2(32000);
1222 l_retcode VARCHAR2(32000);
1223 l_perf_measure_id NUMBER;
1224 l_time_dim_level_id NUMBER;
1225 l_plan_id NUMBER;
1226 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1227
1228 BEGIN
1229
1230 BIS_UTILITIES_PUB.put_line(p_text =>'report alert');
1231
1232 IF p_perf_measure_id IS NOT NULL THEN
1233 l_perf_measure_id := to_number(p_perf_measure_id);
1234 END IF;
1235 IF p_time_dim_level_id IS NOT NULL THEN
1236 l_time_dim_level_id := to_number(p_time_dim_level_id);
1237 END IF;
1238 IF p_plan_id IS NOT NULL THEN
1239 l_plan_id := to_number(p_plan_id);
1240 END IF;
1241
1242 Retrieve_Report_Info
1243 ( p_measure_id => l_perf_measure_id
1244 , p_time_level_id => l_time_dim_level_id
1245 , p_plan_id => l_plan_id
1246 , p_parameter1_level => p_parameter1_level
1247 , p_parameter1_value => p_parameter1_value
1248 , p_parameter2_level => p_parameter2_level
1249 , p_parameter2_value => p_parameter2_value
1250 , p_parameter3_level => p_parameter3_level
1251 , p_parameter3_value => p_parameter3_value
1252 , p_parameter4_level => p_parameter4_level
1253 , p_parameter4_value => p_parameter4_value
1254 , p_parameter5_level => p_parameter5_level
1255 , p_parameter5_value => p_parameter5_value
1256 , p_parameter6_level => p_parameter6_level
1257 , p_parameter6_value => p_parameter6_value
1258 , p_parameter7_level => p_parameter7_level
1259 , p_parameter7_value => p_parameter7_value
1260 , p_viewby_level_id => p_viewby_level_id
1261 , x_target_level_tbl => l_Target_Level_Tbl
1262 , x_target_tbl => l_target_tbl
1263 , x_return_status => l_return_status
1264 , x_error_Tbl => l_error_Tbl
1265 );
1266 BIS_UTILITIES_PUB.put_line(p_text =>'retrieved report target rows: '||l_target_tbl.count);
1267
1268 FOR i IN 1..l_target_level_tbl.COUNT LOOP
1269 --BIS_UTILITIES_PUB.put_line(p_text =>'Target level id: '||l_target_level_tbl(i).target_level_id);
1270 FOR j IN 1..l_target_tbl.COUNT LOOP
1271 IF l_target_level_tbl(i).Target_level_id=l_target_tbl(j).Target_level_id
1272 THEN
1273 BIS_ALERT_SERVICE_PVT.Form_Alert_Request_rec
1274 ( p_target_level_rec => l_target_level_tbl(i)
1275 , p_target_rec => l_target_tbl(j)
1276 , p_dimension_level_rec => l_dimension_level_rec
1277 , p_notify_set => p_notifiers
1278 , p_alert_type => p_alert_type
1279 , x_Alert_Request_rec => l_Alert_Request_rec
1280 );
1281 BIS_UTILITIES_PUB.put_line(p_text =>'Target level ID in Alert request rec: '
1282 ||l_Alert_Request_rec.target_level_id);
1283 l_Alert_Request_tbl(l_Alert_Request_tbl.COUNT+1):=l_Alert_Request_rec;
1284 END IF;
1285 END LOOP;
1286 END LOOP;
1287
1288 BIS_ALERT_SERVICE_PVT.Form_Concurrent_Request
1289 ( p_Alert_Request_Tbl => l_Alert_Request_Tbl
1290 , x_Concurrent_Request_Tbl => l_Concurrent_Request_Tbl
1291 );
1292
1293 BIS_UTILITIES_PUB.put_line(p_text =>'Submit concurrent requests for Detial Alert Service');
1294 --
1295 BIS_CONCURRENT_MANAGER_PVT.Submit_Concurrent_Request
1296 ( p_Concurrent_Request_Tbl => l_Concurrent_Request_Tbl
1297 , x_request_id_tbl => l_request_id_tbl
1298 , x_errbuf => l_errbuf
1299 , x_retcode => l_retcode
1300 );
1301
1302 BIS_UTILITIES_PUB.put_line(p_text =>'report request submitted. id: '||x_request_id);
1303 IF l_request_id_tbl.COUNT > 0 THEN
1304 x_request_id := l_request_id_tbl(l_request_id_tbl.FIRST);
1305 END IF;
1306
1307 EXCEPTION
1308 when FND_API.G_EXC_ERROR then
1309 l_return_status := FND_API.G_RET_STS_ERROR ;
1310 l_debug :=l_debug||' exception 1 at process_report_set_request. '||sqlerrm;
1311 x_request_id := x_request_id ||l_debug;
1312 when FND_API.G_EXC_UNEXPECTED_ERROR then
1313 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1314 l_debug :=l_debug||' exception 2 at process_report_set_request. '||sqlerrm;
1315 x_request_id := x_request_id ||l_debug;
1316 when others then
1317 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1318 l_debug :=l_debug||' exception 3 at process_report_set_request. '||sqlerrm;
1319 x_request_id := x_request_id ||l_debug;
1320 l_error_tbl_p := l_error_Tbl;
1321 BIS_UTILITIES_PVT.Add_Error_Message
1322 ( p_error_msg_id => SQLCODE
1323 , p_error_description => SQLERRM
1324 , p_error_proc_name => 'process_report_set_request'
1325 , p_error_table => l_error_tbl_p
1326 , x_error_table => l_error_tbl
1327 );
1328 END process_report_set_request;
1329
1330 --
1331 -- Procedure Which Accepts the parameters Performance measure,
1332 -- dimension level and value ids from the BIS Report parameter page
1333 -- and requests the reports to be generated in the background.
1334 --
1335 PROCEDURE process_batch_report_request
1336 ( p_StartTime IN varchar2 default null
1337 , p_EndTime IN varchar2 default null
1338 , p_frequencyInterval IN varchar2 default null
1339 , p_frequencyUnits IN varchar2 default null
1340 , p_perf_measure_id IN varchar2 default null
1341 , p_time_dim_level_id IN varchar2 default null
1342 , p_notifiers IN varchar2 default null
1343 , p_plan_id IN varchar2 default null
1344 , p_parameter1_level IN varchar2 default null
1345 , p_parameter1_value IN varchar2 default null
1346 , p_parameter2_level IN varchar2 default null
1347 , p_parameter2_value IN varchar2 default null
1348 , p_parameter3_level IN varchar2 default null
1349 , p_parameter3_value IN varchar2 default null
1350 , p_parameter4_level IN varchar2 default null
1351 , p_parameter4_value IN varchar2 default null
1352 , p_parameter5_level IN varchar2 default null
1353 , p_parameter5_value IN varchar2 default null
1354 , p_parameter6_level IN varchar2 default null
1355 , p_parameter6_value IN varchar2 default null
1356 , p_parameter7_level IN varchar2 default null
1357 , p_parameter7_value IN varchar2 default null
1358 ,p_viewby_level_id IN varchar2 default null
1359 , p_session_id IN varchar2 default null
1360 , p_alert_type IN varchar2 default null
1361 , p_notify_owners_flag IN varchar2 default null
1362 , p_current_row IN VARCHAR2 := 'N'
1363 , x_request_id OUT NOCOPY varchar2
1364 )
1365 IS
1366
1367 l_return_status VARCHAR2(1000);
1368 l_request_id VARCHAR2(32000);
1369 l_debug VARCHAR2(32000);
1370 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1371 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1372
1373 BEGIN
1374
1375 BIS_UTILITIES_PUB.put_line(p_text =>'IN process_batch_report_request');
1376
1377 EXCEPTION
1378 when FND_API.G_EXC_ERROR then
1379 l_return_status := FND_API.G_RET_STS_ERROR ;
1380 l_debug :=l_debug
1381 ||' exception 1 at process_batch_report_request. '||sqlerrm;
1382 x_request_id := x_request_id ||l_debug;
1383 when FND_API.G_EXC_UNEXPECTED_ERROR then
1384 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1385 l_debug :=l_debug
1386 ||' exception 2 at process_batch_report_request. '||sqlerrm;
1387 x_request_id := x_request_id ||l_debug;
1388 when others then
1389 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1390 l_debug :=l_debug
1391 ||' exception 3 at process_batch_report_request. '||sqlerrm;
1392 x_request_id := x_request_id ||l_debug;
1393 l_error_tbl_p := l_error_tbl;
1394 BIS_UTILITIES_PVT.Add_Error_Message
1395 ( p_error_msg_id => SQLCODE
1396 , p_error_description => SQLERRM
1397 , p_error_proc_name => 'process_batch_report_request'
1398 , p_error_table => l_error_tbl_p
1399 , x_error_table => l_error_tbl
1400 );
1401
1402 END process_batch_report_request;
1403
1404 --
1405 -- Procedure Which Accepts the parameters Performance measure
1406 -- and time dimension level id from the Alert
1407 -- Registration screen and submit the request for the needed once
1408 --
1409 PROCEDURE process_parameter_set
1410 (p_request_id OUT NOCOPY varchar2
1411 ,p_perf_measure_id IN varchar2 default null
1412 ,p_time_dim_level_id IN varchar2 default null
1413 ,p_session_id IN varchar2 default null
1414 ,p_alert_type IN varchar2 default null
1415 ,p_notify_owners_flag IN varchar2 default null
1416 , p_current_row IN VARCHAR2 := 'N'
1417 )
1418 IS
1419 l_notifiers_code VARCHAR2(32000);
1420 l_request_id VARCHAR2(32000);
1421 l_param_set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1422 l_param_set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1423 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
1424 l_report_data_tbl report_data_tbl_type;
1425 l_return_status VARCHAR2(1000);
1426 l_request_scheduled VARCHAR2(1000);
1427 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1428 l_api_version NUMBER := 1;
1429 l_debug VARCHAR2(32000);
1430 l_perf_measure_id NUMBER;
1431 l_time_dim_level_id NUMBER;
1432 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1433
1434 Begin
1435
1436 -- register main request
1437 --
1438 IF p_perf_measure_id IS NOT NULL THEN
1439 l_perf_measure_id := to_number(p_perf_measure_id);
1440 END IF;
1441 IF p_time_dim_level_id IS NOT NULL THEN
1442 l_time_dim_level_id := to_number(p_time_dim_level_id);
1443 END IF;
1444
1445 l_param_set_Rec.PERFORMANCE_MEASURE_ID := l_perf_measure_id;
1446 l_param_set_Rec.TIME_DIMENSION_LEVEL_ID := l_time_dim_level_id;
1447 l_param_set_rec.notify_owner_flag := p_notify_owners_flag;
1448
1449 BIS_UTILITIES_PUB.put_line(p_text =>'Processing parameter_set. measure id: '||p_perf_measure_id
1450 ||', time level id: '||p_time_dim_level_id);
1451
1452 Register_Parameter_set
1453 ( p_api_version => l_api_version
1454 , p_Param_Set_Rec => l_Param_Set_Rec
1455 , p_session_id => p_session_id
1456 , p_alert_type => p_alert_type
1457 , p_request_id => l_request_id
1458 , x_return_status => l_return_status
1459 , x_error_Tbl => l_error_Tbl
1460 );
1461 --BIS_UTILITIES_PUB.put_line(p_text =>'AFTER register param set: '||l_request_id);
1462
1463 --p_request_id := p_request_id||l_debug_text;
1464 p_request_id := l_request_id;
1465
1466 EXCEPTION
1467 when FND_API.G_EXC_ERROR then
1468 l_return_status := FND_API.G_RET_STS_ERROR ;
1469 BIS_UTILITIES_PUB.put_line(p_text =>'process_parameter_set exception1: '||sqlerrm);
1470 p_request_id := l_request_id
1471 ||' exception 1 in process_parameter_set ';
1472 when FND_API.G_EXC_UNEXPECTED_ERROR then
1473 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1474 p_request_id := l_request_id
1475 ||' exception 2 in process_parameter_set ';
1476 BIS_UTILITIES_PUB.put_line(p_text =>'process_parameter_set exception2: '||sqlerrm);
1477 when others then
1478 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1479 p_request_id := l_request_id
1480 ||' exception 3 in process_parameter_set ';
1481 BIS_UTILITIES_PUB.put_line(p_text =>'process_parameter_set exception3: '||sqlerrm);
1482 l_error_tbl_p := l_error_tbl;
1483 BIS_UTILITIES_PVT.Add_Error_Message
1484 ( p_error_msg_id => SQLCODE
1485 , p_error_description => SQLERRM
1486 , p_error_proc_name => 'Process_Parameter_set'
1487 , p_error_table => l_error_tbl_p
1488 , x_error_table => l_error_tbl
1489 );
1490
1491 end process_parameter_set;
1492
1493 --
1494 -- Procedure which insert the needed parameters to the alert
1495 -- registration repository and invoke the submit request procedure
1496 --
1497 PROCEDURE Register_Parameter_Set
1498 ( p_api_version IN NUMBER
1499 , p_Param_Set_Rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
1500 , p_session_id IN varchar2 default null
1501 , p_alert_type IN varchar2 default null
1502 , p_request_id OUT NOCOPY varchar2
1503 , x_return_status OUT NOCOPY VARCHAR2
1504 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1505 )
1506 IS
1507
1508 l_Conc_exist BOOLEAN := FALSE;
1509 l_param_exist BOOLEAN := FALSE;
1510 l_request_id VARCHAR2(32000);
1511 l_debug VARCHAR2(32000);
1512 l_notifiers_code VARCHAR2(32000);
1513 l_commit VARCHAR2(32000) := FND_API.G_TRUE;
1514 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1515 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1516
1517 BEGIN
1518 /*
1519 BIS_UTILITIES_PUB.put_line(p_text =>'VARSB: registering param set');
1520 BIS_UTILITIES_PUB.put_line(p_text =>'measure: '||p_param_set_Rec.PERFORMANCE_MEASURE_ID);
1521 BIS_UTILITIES_PUB.put_line(p_text =>'target level: '|| p_param_set_Rec.TARGET_LEVEL_ID);
1522 BIS_UTILITIES_PUB.put_line(p_text =>'time level: '|| p_param_set_Rec.TIME_DIMENSION_LEVEL_ID);
1523 BIS_UTILITIES_PUB.put_line(p_text =>'notifier: '||p_param_set_Rec.NOTIFIERS_CODE);
1524 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1: '|| p_param_set_Rec.PARAMETER1_VALUE);
1525 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2: '|| p_param_set_Rec.PARAMETER2_VALUE);
1526 BIS_UTILITIES_PUB.put_line(p_text => p_param_set_Rec.PARAMETER3_VALUE);
1527 BIS_UTILITIES_PUB.put_line(p_text => p_param_set_Rec.PARAMETER4_VALUE);
1528 BIS_UTILITIES_PUB.put_line(p_text => p_param_set_Rec.PARAMETER5_VALUE);
1529 BIS_UTILITIES_PUB.put_line(p_text => p_param_set_Rec.PARAMETER6_VALUE);
1530 BIS_UTILITIES_PUB.put_line(p_text => p_param_set_Rec.PARAMETER7_VALUE);
1531 */
1532 BIS_UTILITIES_PUB.put_line(p_text =>'Registering Parameter set' );
1533
1534 -- Check whether there is a concurrent program exist for
1535 -- the given PM and time dimension level id
1536 --
1537 l_param_exist
1538 := BIS_PMF_ALERT_REG_PUB.Parameter_set_Exist
1539 ( p_api_version => p_api_version
1540 , p_Param_Set_Rec => p_Param_Set_Rec
1541 , x_notifiers_code => l_notifiers_code
1542 , x_return_status => x_return_status
1543 , x_error_Tbl => x_error_Tbl
1544 );
1545
1546 -- Each row will have it's own adHocRole
1547 -- l_param_set_rec.notifiers_code := l_notifiers_code;
1548
1549 --BIS_UTILITIES_PUB.put_line(p_text =>'ARSB: parameter set exist notifier code: '||l_notifiers_code);
1550
1551
1552 IF l_param_exist THEN
1553 BIS_UTILITIES_PUB.put_line(p_text =>'Request has already been scheduled. Notifier code: '
1554 ||l_notifiers_code);
1555 ELSE
1556 BIS_UTILITIES_PUB.put_line(p_text =>'Request has not been scheduled. Notifier code: '
1557 ||l_notifiers_code);
1558 l_request_id := G_NOTSUBMIT;
1559 END IF;
1560
1561 IF NOT l_param_exist THEN
1562 l_Param_Set_Rec := p_Param_Set_Rec;
1563 BIS_PMF_ALERT_REG_PUB.Create_Parameter_set
1564 ( p_api_version => p_api_version
1565 , p_commit => l_commit
1566 , p_Param_Set_Rec => l_Param_Set_Rec
1567 , x_return_status => x_return_status
1568 , x_error_Tbl => x_error_Tbl
1569 );
1570 l_debug := l_debug ||' created parameter set. status: '||x_return_status;
1571 END IF;
1572
1573 /*
1574 IF p_alert_type = G_ALL_TARGET THEN
1575 Add_Subscribers
1576 ( p_Param_Set_rec => l_Param_Set_rec
1577 , x_request_id => l_request_id
1578 , x_return_status => x_return_status
1579 );
1580 BIS_UTILITIES_PUB.put_line(p_text =>'Add subscriber status: '||x_return_status);
1581 END IF;
1582 */
1583 p_request_id := l_request_id||l_debug;
1584 --p_request_id := l_request_id;
1585
1586 EXCEPTION
1587 when FND_API.G_EXC_ERROR then
1588 x_return_status := FND_API.G_RET_STS_ERROR ;
1589 x_return_status := ' exception 1 in Register_Parameter_Set '||sqlerrm;
1590 when FND_API.G_EXC_UNEXPECTED_ERROR then
1591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1592 x_return_status := ' exception 2 in Register_Parameter_Set '||sqlerrm;
1593 BIS_UTILITIES_PUB.put_line(p_text =>'register_parameter_set exception2: '||sqlerrm);
1594 when others then
1595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1596 x_return_status := ' exception 3 in Register_Parameter_Set '||sqlerrm;
1597 BIS_UTILITIES_PUB.put_line(p_text =>'register_parameter_set exception3: '||sqlerrm);
1598 l_error_tbl := x_error_Tbl;
1599 BIS_UTILITIES_PVT.Add_Error_Message
1600 ( p_error_msg_id => SQLCODE
1601 , p_error_description => SQLERRM
1602 , p_error_proc_name => 'Register_Parameter_Set'
1603 , p_error_table => l_error_tbl
1604 , x_error_table => x_error_tbl
1605 );
1606 End Register_Parameter_Set;
1607
1608 PROCEDURE Add_Subscribers
1609 ( p_Param_Set_rec IN BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type
1610 , x_request_id OUT NOCOPY varchar2
1611 , x_return_status OUT NOCOPY varchar2
1612 )
1613 IS
1614
1615 l_debug VARCHAR2(32000);
1616 l_notifiers_code VARCHAR2(32000);
1617 l_user_tbl wf_directory.UserTable;
1618 l_user VARCHAR2(32000);
1619 l_user_exist BOOLEAN := FALSE;
1620 l_request_id VARCHAR2(32000);
1621
1622 BEGIN
1623
1624 l_user := FND_GLOBAL.USER_NAME;
1625 BIS_UTILITIES_PUB.put_line(p_text =>'Adding user to subscription list: '||l_user);
1626 l_debug := l_debug ||' Adding user to subscription list: '||l_user;
1627
1628 BIS_PMF_ALERT_REG_PVT.Retrieve_Notifiers_Code
1629 ( p_api_version => 1.0
1630 , p_Param_Set_rec => p_Param_Set_rec
1631 , x_Notifiers_Code => l_Notifiers_Code
1632 , x_return_status => x_return_status
1633 );
1634 l_debug := l_debug ||' notifier code: '||l_Notifiers_Code;
1635
1636 IF l_notifiers_code IS NOT NULL THEN
1637 wf_directory.GetRoleUsers(l_notifiers_code,l_user_tbl);
1638
1639 BIS_UTILITIES_PUB.put_line(p_text =>'Number of subscribers so far: '||l_user_tbl.COUNT);
1640 l_debug := l_debug ||' Number of subscribers so far: '||l_user_tbl.COUNT;
1641
1642 FOR i IN 1..l_user_tbl.COUNT LOOP
1643 BIS_UTILITIES_PUB.put_line(p_text =>'Subscriber '||i||': '||l_user_tbl(i));
1644 IF (l_user_tbl(i) = l_user) THEN
1645 l_user_exist := TRUE;
1646 END IF;
1647 END LOOP;
1648
1649 IF l_user_exist THEN
1650 l_request_id := G_USER_EXIST;
1651 ELSE
1652 -- wf_directory.AddUsersToAdHocRole(l_notifiers_code,l_user);
1653 wf_local_synch.propagateUserRole(p_role_name => l_notifiers_code, p_user_name => l_user);
1654 l_request_id
1655 := BIS_UTILITIES_PVT.Get_FND_Message
1656 ( p_message_name => 'BIS_NOTIFIER_ADDED'
1657 , p_msg_param1 => 'USER_NAME'
1658 , p_msg_param1_val => l_user
1659 );
1660 END IF;
1661 END IF;
1662
1663 x_request_id := l_request_id;
1664 --x_request_id := l_request_id||'--'||l_debug;
1665 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1666
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 x_request_id := 'Exception while adding user. '||sqlerrm;
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1671 END Add_Subscribers;
1672
1673 --
1674 -- Retrieve a All Target information for the given performance measure
1675 -- and time dimension level.
1676 --
1677 PROCEDURE Retrieve_target_info
1678 ( p_api_version IN NUMBER
1679 , p_measure_id IN NUMBER
1680 , p_time_dimension_level_id IN NUMBER
1681 , p_current_row IN VARCHAR2 := NULL
1682 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
1683 , x_target_tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
1684 , x_return_status OUT NOCOPY VARCHAR2
1685 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1686 )
1687 IS
1688
1689 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
1690 l_Target_Level_tbl BIS_Target_Level_PUB.Target_Level_Tbl_Type;
1691 l_target_tbl_tmp BIS_TARGET_PUB.Target_Tbl_type;
1692 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
1693 l_Indicator_Region_Tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
1694
1695 l_target_count NUMBER := 1;
1696 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1697 l_dimension_level_number NUMBER;
1698 l_time_level_short_name VARCHAR2(32000);
1699 l_time_short_name VARCHAR2(32000);
1700 l_total_time_level_short VARCHAR2(32000);
1701 l_is_total_time BOOLEAN := FALSE;
1702 l_Is_Rolling_Period_Level NUMBER := 0; -- bug 2408906
1703 l_target_tbl_tmp_p BIS_TARGET_PUB.Target_Tbl_type;
1704 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1705 l_flag BOOLEAN := FALSE;
1706 c_num NUMBER := -9999;
1707 c_var VARCHAR2(5) := '-9999';
1708
1709 Begin
1710
1711
1712 BIS_UTILITIES_PUB.put_line(p_text =>' ***************************************************** ');
1713 BIS_UTILITIES_PUB.put_line(p_text => ' Start Retrieving target info ' );
1714 BIS_UTILITIES_PUB.put_line(p_text =>' ***************************************************** ');
1715
1716 BIS_UTILITIES_PUB.put_line(p_text => ' Retrieving target information ' );
1717 BIS_UTILITIES_PUB.put_line(p_text => ' Measure id = ' || p_measure_id );
1718 BIS_UTILITIES_PUB.put_line(p_text => ' Time level id = ' || p_time_dimension_level_id ) ;
1719 BIS_UTILITIES_PUB.put_line(p_text => ' Is curent time period = '|| p_current_row );
1720
1721 BIS_UTILITIES_PUB.put_line(p_text => ' --------------------------------------------- ' );
1722
1723 l_is_total_time := FALSE;
1724 l_is_total_time := IS_TOTAL_DIM_LEVEL(p_time_dimension_level_id);
1725
1726 --added to allow RSG call to let thr' for all time levels
1727 IF (p_time_dimension_level_id IS NOT NULL) THEN
1728 l_time_level_short_name := BIS_UTILITIES_PVT.GET_TIME_SHORT_NAME(p_time_dimension_level_id);
1729 END IF;
1730
1731 l_Is_Rolling_Period_Level := BIS_UTILITIES_PVT.Is_Rolling_Period_Level( -- bug 2408906
1732 p_level_short_name => l_time_level_short_name ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Time Level short Name ' || l_time_level_short_name);
1733 IF (p_time_dimension_level_id IS NOT NULL) THEN --added to allow RSG call to let thr' for all time levels
1734 l_time_short_name := BIS_UTILITIES_PVT.Get_Time_Dimension_Name (p_DimLevelId => p_time_dimension_level_id); -- BIS_UTILITIES_PUB.put_line(p_text =>'Time short Name ' || l_time_short_name);
1735 l_total_time_level_short := BIS_UTILITIES_PVT.Get_Total_Dimlevel_Name (l_time_short_name , NULL, l_time_level_short_name); -- BIS_UTILITIES_PUB.put_line(p_text =>'Total Time Level short Name ' || l_total_time_level_short);
1736 END IF;
1737
1738 BIS_UTILITIES_PUB.put_line(p_text => ' --------------------------------------------- ' );
1739
1740 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1741 l_Measure_Rec.Measure_id := p_measure_id;
1742 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Levels
1743 ( p_api_version => p_api_version
1744 , p_all_info => FND_API.G_FALSE
1745 , p_Measure_Rec => l_Measure_Rec
1746 , x_Target_Level_tbl => l_Target_Level_tbl
1747 , x_return_status => x_return_status
1748 , x_error_Tbl => x_error_Tbl
1749 );
1750
1751 BIS_UTILITIES_PUB.put_line(p_text =>'Number of target levels retrieved for Target INFO: '
1752 ||l_Target_Level_tbl.COUNT );
1753
1754 FOR i in 1..l_Target_Level_tbl.COUNT
1755 LOOP
1756 BIS_UTILITIES_PUB.put_line(p_text => ' Target level id # ' || i || ' is ' || l_Target_Level_tbl(i).target_level_id );
1757 END LOOP;
1758
1759
1760 FOR i in 1..l_Target_Level_tbl.COUNT
1761 LOOP
1762
1763 BIS_UTILITIES_PUB.put_line(p_text => ' -------***************************************---------- ' );
1764 BIS_UTILITIES_PUB.put_line(p_text => ' Start retrieving targets for # ' || i || ' tgt lvl id ' || l_Target_Level_tbl(i).target_level_id) ;
1765
1766 -- if l_is_total_time = FALSE then
1767 -- In every loop for summary levels under a measure,
1768 -- for summary level id 1793 -> finds dim levels -> finds the dimension level id corresponding to time dimension. (1219)
1769 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
1770 ( p_api_version => 1.0
1771 , p_Target_Level_Rec => l_Target_Level_tbl(i)
1772 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
1773 , x_dimension_level_number => l_dimension_level_number
1774 , x_return_status => x_return_status
1775 , x_error_Tbl => x_error_Tbl
1776 );
1777
1778 -- end if;
1779
1780 BIS_UTILITIES_PUB.put_line(p_text =>' Time level id for # ' || i || ' from target lvls table is ' || l_Dimension_Level_Rec.dimension_level_id);
1781
1782 -- Filtering only for the time dimension passed as
1783 -- parameter
1784 --
1785 -- IF p_time_dimension_level_id IS NULL -- or l_is_total_time = TRUE
1786 -- OR l_Dimension_Level_Rec.dimension_level_id = p_time_dimension_level_id
1787 -- Only if the time dimension passed in is null (no time dimension selected while adding dimensions) or
1788 -- summary levels of the measure contains a time dim level which is equal to the time dim level passed in, then
1789 -- continue with using that to spawn child requests.
1790
1791 IF p_time_dimension_level_id IS NULL or (l_is_total_time = TRUE
1792 and (BIS_UTILITIES_PUB.Value_Missing(l_Dimension_Level_Rec.dimension_level_id) = FND_API.G_TRUE
1793 or BIS_UTILITIES_PUB.Value_NULL(l_Dimension_Level_Rec.dimension_level_id)= FND_API.G_TRUE))
1794 OR l_Dimension_Level_Rec.dimension_level_id = p_time_dimension_level_id
1795 THEN
1796
1797 BIS_UTILITIES_PUB.put_line(p_text => ' --------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-------- ' );
1798 BIS_UTILITIES_PUB.put_line(p_text => ' Begin Retrieving targets from bis_target_values ' );
1799
1800 BIS_TARGET_PUB.Retrieve_targets
1801 ( p_api_version => p_api_version
1802 , p_Target_Level_Rec => l_Target_Level_tbl(i)
1803 , p_all_info => FND_API.G_FALSE
1804 , x_Target_tbl => l_Target_tbl_tmp
1805 , x_return_status => x_return_status
1806 , x_error_Tbl => x_error_Tbl
1807 );
1808
1809 BIS_UTILITIES_PUB.put_line(p_text => ' End Retrieving targets from bis_target_values ' );
1810 BIS_UTILITIES_PUB.put_line(p_text => ' Number of targets from targets = '||l_Target_tbl_tmp.COUNT);
1811 BIS_UTILITIES_PUB.put_line(p_text => ' --------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-------- ' );
1812
1813
1814 BIS_UTILITIES_PUB.put_line(p_text => ' ---------.............................-------- ' );
1815 BIS_UTILITIES_PUB.put_line(p_text => ' Begin Retrieving targets from bis_user_ind_selections ' );
1816
1817 BIS_ACTUAL_PVT.Retrieve_tl_selections
1818 ( p_Target_Level_Rec => l_Target_Level_tbl(i)
1819 , x_Indicator_Region_Tbl => l_Indicator_Region_Tbl
1820 , x_return_status => x_return_status
1821 , x_error_Tbl => x_error_Tbl
1822 );
1823
1824 l_target_count := l_Target_tbl_tmp.COUNT;
1825
1826 FOR j IN 1..l_Indicator_Region_Tbl.COUNT LOOP
1827 l_flag := FALSE;
1828 FOR k IN 1..l_Target_tbl_tmp.COUNT LOOP
1829 IF ( (NVL(l_Target_tbl_tmp(k).Target_Level_ID, c_num) = NVL(l_Indicator_Region_Tbl(j).Target_Level_ID, c_num)) AND
1830 (NVL(l_Target_tbl_tmp(k).Plan_ID, c_num) = NVL(l_Indicator_Region_Tbl(j).Plan_ID, c_num)) AND
1831 (NVL(l_Target_tbl_tmp(k).Dim1_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim1_Level_Value_ID, c_var)) AND
1832 (NVL(l_Target_tbl_tmp(k).Dim2_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim2_Level_Value_ID, c_var)) AND
1833 (NVL(l_Target_tbl_tmp(k).Dim3_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim3_Level_Value_ID, c_var)) AND
1834 (NVL(l_Target_tbl_tmp(k).Dim4_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim4_Level_Value_ID, c_var)) AND
1835 (NVL(l_Target_tbl_tmp(k).Dim5_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim5_Level_Value_ID, c_var)) AND
1836 (NVL(l_Target_tbl_tmp(k).Dim6_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim6_Level_Value_ID, c_var)) AND
1837 (NVL(l_Target_tbl_tmp(k).Dim7_Level_Value_ID, c_var) = NVL(l_Indicator_Region_Tbl(j).Dim7_Level_Value_ID, c_var))
1838 ) THEN
1839 l_flag := TRUE;
1840 EXIT;
1841 END IF;
1842 END LOOP;
1843 IF NOT (l_flag) THEN
1844 l_target_count := l_target_count + 1;
1845 l_Target_tbl_tmp(l_target_count).Target_Level_ID :=
1846 l_Indicator_Region_Tbl(j).Target_Level_ID;
1847 l_Target_tbl_tmp(l_target_count).Plan_ID :=
1848 l_Indicator_Region_Tbl(j).Plan_ID;
1849 l_Target_tbl_tmp(l_target_count).Dim1_Level_Value_ID :=
1850 l_Indicator_Region_Tbl(j).Dim1_Level_Value_ID;
1851 l_Target_tbl_tmp(l_target_count).Dim2_Level_Value_ID :=
1852 l_Indicator_Region_Tbl(j).Dim2_Level_Value_ID;
1853 l_Target_tbl_tmp(l_target_count).Dim3_Level_Value_ID :=
1854 l_Indicator_Region_Tbl(j).Dim3_Level_Value_ID;
1855 l_Target_tbl_tmp(l_target_count).Dim4_Level_Value_ID :=
1856 l_Indicator_Region_Tbl(j).Dim4_Level_Value_ID;
1857 l_Target_tbl_tmp(l_target_count).Dim5_Level_Value_ID :=
1858 l_Indicator_Region_Tbl(j).Dim5_Level_Value_ID;
1859 l_Target_tbl_tmp(l_target_count).Dim6_Level_Value_ID :=
1860 l_Indicator_Region_Tbl(j).Dim6_Level_Value_ID;
1861 l_Target_tbl_tmp(l_target_count).Dim7_Level_Value_ID :=
1862 l_Indicator_Region_Tbl(j).Dim7_Level_Value_ID;
1863 l_Target_tbl_tmp(l_target_count).is_pm_region := BIS_TARGET_PUB.G_IS_PM_REGION; -- indicates it is a PM region entry
1864 END IF;
1865 END LOOP;
1866
1867 BIS_UTILITIES_PUB.put_line(p_text => ' End Retrieving targets from bis_user_ind_selections ' );
1868 BIS_UTILITIES_PUB.put_line(p_text => ' Number of targets from user ind sel = '|| l_Indicator_Region_Tbl.COUNT );
1869 BIS_UTILITIES_PUB.put_line(p_text => ' Number of filtered targets from targets = '||l_Target_tbl_tmp.COUNT);
1870 BIS_UTILITIES_PUB.put_line(p_text => ' ---------.............................-------- ' );
1871
1872 -- If the alert is to be target based, then the "Change based" alerts should be filtered out and vice-versa
1873 l_target_tbl_tmp_p := l_target_tbl_tmp;
1874 filter_alert_based_on(
1875 p_Target_tbl => l_Target_tbl_tmp_p
1876 ,p_alert_based_on => p_alert_based_on
1877 ,x_target_tbl => l_target_tbl_tmp
1878 ,x_return_status => x_return_status
1879 );
1880
1881 -- get current targets if specified
1882 --
1883
1884 -- get current targets if specified
1885 --
1886 -- get in if it is not a rolling period or not total time (check) i.e. if the input is a rolling period/total time (check), then don't filter
1887 --added to allow RSG call to let thr' for all time levels
1888 IF ( (l_time_level_short_name IS NULL AND l_total_time_level_short IS NULL) OR ( (l_time_level_short_name <> l_total_time_level_short)
1889 and (l_Is_Rolling_Period_Level = 0)) -- bug 2408906 (dont filter out NOCOPY rolling levels)
1890 ) THEN
1891
1892 IF p_current_row = 'Y' THEN
1893 -- BIS_UTILITIES_PUB.put_line(p_text => ' call to get current target ' ) ;
1894 l_target_tbl_tmp_p := l_target_tbl_tmp;
1895 Get_Current_Target
1896 ( p_Target_tbl => l_Target_tbl_tmp_p
1897 , p_target_level_rec => l_target_level_tbl(i)
1898 -- , p_alert_based_on => p_alert_based_on
1899 , x_target_tbl => l_target_tbl_tmp
1900 , x_return_status => x_return_status
1901 );
1902 ELSIF p_current_row = 'N' THEN
1903 -- BIS_UTILITIES_PUB.put_line(p_text => ' call to get previous target ' ) ;
1904 l_target_tbl_tmp_p := l_target_tbl_tmp;
1905 Get_Previous_Target
1906 ( p_Target_tbl => l_Target_tbl_tmp_p
1907 , p_target_level_rec => l_target_level_tbl(i)
1908 , x_target_tbl => l_target_tbl_tmp
1909 , x_return_status => x_return_status
1910 );
1911 ELSE
1912 BIS_UTILITIES_PUB.put_line(p_text =>'Neither previous or current target!!');
1913 -- x_target_tbl := l_target_tbl;
1914 END IF;
1915 ELSE
1916 l_target_tbl_tmp_p := l_target_tbl_tmp;
1917 update_total_time
1918 ( p_Target_tbl => l_Target_tbl_tmp_p
1919 , p_target_level_rec => l_target_level_tbl(i)
1920 , x_target_tbl => l_target_tbl_tmp
1921 , x_return_status => x_return_status
1922 );
1923
1924 END IF;
1925
1926 -- Since time dimension level value gets added in get_current_target/get_previous_target for PM region values
1927 -- it needs to be checked for duplicates again
1928 l_target_tbl_tmp_p := l_target_tbl_tmp;
1929 filter_duplicates (
1930 p_target_tbl => l_target_tbl_tmp_p
1931 ,x_target_tbl => l_target_tbl_tmp
1932 );
1933
1934 BIS_UTILITIES_PUB.put_line(p_text => ' --------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-------- ' );
1935 BIS_UTILITIES_PUB.put_line(p_text => ' Number of filtered targets from targets = '|| l_Target_tbl_tmp.COUNT);
1936
1937 FOR j IN 1..l_Target_tbl_tmp.COUNT LOOP
1938 x_Target_tbl(x_Target_tbl.COUNT+1) := l_Target_tbl_tmp(j);
1939 END LOOP;
1940
1941 BIS_UTILITIES_PUB.put_line(p_text =>' Total number of filtered targets retrieved for this time level SO FAR.. = ' || x_target_tbl.count);
1942 BIS_UTILITIES_PUB.put_line(p_text => ' --------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-------- ' );
1943
1944 END IF;
1945
1946 END LOOP;
1947 --x_target_tbl := l_target_tbl;
1948 BIS_UTILITIES_PUB.put_line(p_text =>' Final number of targets retrieved ' || x_target_tbl.count);
1949
1950 BIS_UTILITIES_PUB.put_line(p_text =>' ***************************************************** ');
1951 BIS_UTILITIES_PUB.put_line(p_text => ' Finish Retrieving target info ' );
1952 BIS_UTILITIES_PUB.put_line(p_text =>' ***************************************************** ');
1953
1954 EXCEPTION
1955 when FND_API.G_EXC_ERROR then
1956 x_return_status := FND_API.G_RET_STS_ERROR ;
1957 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 at Retrieve_target_info: '||sqlerrm);
1958 when FND_API.G_EXC_UNEXPECTED_ERROR then
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1960 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 at Retrieve_target_info: '||sqlerrm);
1961 when others then
1962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1963 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 at Retrieve_target_info: '||sqlerrm);
1964 l_error_tbl := x_error_tbl;
1965 BIS_UTILITIES_PVT.Add_Error_Message
1966 ( p_error_msg_id => SQLCODE
1967 , p_error_description => SQLERRM
1968 , p_error_proc_name => G_PKG_NAME||'.Retrieve_target_info'
1969 , p_error_table => l_error_tbl
1970 , x_error_table => x_error_tbl
1971 );
1972
1973 End Retrieve_target_info;
1974
1975 -- Since time dimension level value gets added in get_current_target/get_previous_target for PM region values
1976 -- it needs to be checked for duplicates again
1977
1978 PROCEDURE filter_duplicates (
1979 p_target_tbl IN BIS_TARGET_PUB.target_tbl_type
1980 ,x_target_tbl OUT NOCOPY BIS_TARGET_PUB.target_tbl_type
1981 )
1982 IS
1983 l_found BOOLEAN := FALSE;
1984 l_count NUMBER := 0;
1985 c_var VARCHAR2(10) := '-99999999'; -- dummy variable.
1986 c_num NUMBER := -9999;
1987 BEGIN
1988
1989 FOR i IN p_target_tbl.FIRST .. p_target_tbl.LAST LOOP
1990 l_found := FALSE;
1991 FOR j IN i+1 .. p_target_tbl.LAST LOOP
1992
1993 IF ( (NVL(p_target_tbl(i).Target_Level_ID, c_num) = NVL(p_target_tbl(j).Target_Level_ID, c_num)) AND
1994 (NVL(p_target_tbl(i).Plan_ID, c_num) = NVL(p_target_tbl(j).Plan_ID, c_num)) AND
1995 (NVL(p_target_tbl(i).Dim1_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim1_Level_Value_ID, c_var)) AND
1996 (NVL(p_target_tbl(i).Dim2_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim2_Level_Value_ID, c_var)) AND
1997 (NVL(p_target_tbl(i).Dim3_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim3_Level_Value_ID, c_var)) AND
1998 (NVL(p_target_tbl(i).Dim4_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim4_Level_Value_ID, c_var)) AND
1999 (NVL(p_target_tbl(i).Dim5_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim5_Level_Value_ID, c_var)) AND
2000 (NVL(p_target_tbl(i).Dim6_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim6_Level_Value_ID, c_var)) AND
2001 (NVL(p_target_tbl(i).Dim7_Level_Value_ID, c_var) = NVL(p_target_tbl(j).Dim7_Level_Value_ID, c_var))
2002 ) THEN
2003 l_found := TRUE;
2004 EXIT;
2005 END IF;
2006 END LOOP;
2007
2008 IF (NOT l_found) THEN
2009 l_count := x_target_tbl.COUNT + 1;
2010 x_target_tbl(l_count) := p_target_tbl(i);
2011 END IF;
2012
2013 END LOOP;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 NULL;
2018 END;
2019
2020 -- If the alert is to be target based, then the "Change based" alerts should be filtered out
2021 -- and vice-versa, since the input table to this method contains all the targets set (change/target based) for a measure.
2022 --
2023 PROCEDURE filter_alert_based_on (
2024 p_target_tbl IN BIS_TARGET_PUB.target_tbl_type
2025 ,p_alert_based_on IN VARCHAR2
2026 ,x_target_tbl OUT NOCOPY BIS_TARGET_PUB.target_tbl_type
2027 ,x_return_status OUT NOCOPY VARCHAR2
2028 )
2029 IS
2030 l_count NUMBER := 0;
2031 BEGIN
2032
2033 FOR i IN p_target_tbl.FIRST .. p_target_tbl.LAST LOOP
2034 IF (p_target_tbl(i).is_pm_region = BIS_TARGET_PUB.G_IS_PM_REGION) THEN
2035 l_count := x_target_tbl.COUNT + 1;
2036 x_target_tbl(l_count) := p_target_tbl(i);
2037 ELSIF ( (p_alert_based_on = BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET) AND (p_target_tbl(i).target IS NOT NULL) ) THEN
2038 l_count := x_target_tbl.COUNT + 1;
2039 x_target_tbl(l_count) := p_target_tbl(i);
2040 ELSIF ( (p_alert_based_on = BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_CHANGE) AND (p_target_tbl(i).target IS NULL) ) THEN
2041 l_count := x_target_tbl.COUNT + 1;
2042 x_target_tbl(l_count) := p_target_tbl(i);
2043 END IF;
2044 END LOOP;
2045
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 NULL;
2049 END filter_alert_based_on;
2050
2051
2052 --
2053 -- Retrieve a All Target information for the given target level
2054 --
2055 PROCEDURE Retrieve_target_info
2056 ( p_api_version IN NUMBER
2057 , p_target_level_id IN NUMBER
2058 , p_time_dimension_level_id IN NUMBER := NULL
2059 , p_current_row IN VARCHAR2 := NULL
2060 --, p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
2061 , x_target_tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
2062 , x_return_status OUT NOCOPY VARCHAR2
2063 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2064 )
2065 IS
2066
2067 l_Target_Level_rec BIS_Target_Level_PUB.Target_Level_rec_Type;
2068 l_target_tbl_tmp BIS_TARGET_PUB.Target_Tbl_type;
2069 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
2070 l_target_count NUMBER := 0;
2071 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
2072 l_dimension_level_number NUMBER;
2073 l_current_row VARCHAR2(10);
2074 l_Target_Level_rec_p BIS_Target_Level_PUB.Target_Level_rec_Type;
2075 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2076 l_Target_tbl_tmp_p BIS_TARGET_PUB.Target_Tbl_type;
2077 Begin
2078
2079 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving target information 0002.'
2080 ||' , target level id: '||p_target_level_id
2081 ||', Curent row? '||p_current_row);
2082
2083 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2084
2085 l_Target_Level_Rec.Target_Level_id := p_Target_Level_id;
2086 l_Target_Level_rec_p := l_Target_Level_rec;
2087 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
2088 ( p_api_version => p_api_version
2089 , p_all_info => FND_API.G_FALSE
2090 , p_Target_Level_rec => l_Target_Level_rec_p
2091 , x_Target_Level_rec => l_Target_Level_rec
2092 , x_return_status => x_return_status
2093 , x_error_Tbl => x_error_Tbl
2094 );
2095
2096 BIS_UTILITIES_PUB.put_line(p_text =>'Target level retrieved for Target INFO 2: '
2097 ||l_Target_Level_rec.Target_Level_short_name );
2098
2099 BIS_TARGET_LEVEL_PVT.Retrieve_Time_level
2100 ( p_api_version => 1.0
2101 , p_Target_Level_Rec => l_Target_Level_rec
2102 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
2103 , x_dimension_level_number => l_dimension_level_number
2104 , x_return_status => x_return_status
2105 , x_error_Tbl => x_error_Tbl
2106 );
2107
2108 BIS_UTILITIES_PUB.put_line(p_text =>'time level id for target level: '
2109 ||l_Dimension_Level_Rec.dimension_level_id);
2110
2111 -- Filtering only for the time dimension passed as
2112 -- parameter
2113 --
2114 IF p_time_dimension_level_id IS NULL
2115 OR l_Dimension_Level_Rec.dimension_level_id = p_time_dimension_level_id
2116 THEN
2117
2118 BIS_TARGET_PUB.Retrieve_targets
2119 ( p_api_version => p_api_version
2120 , p_Target_Level_Rec => l_Target_Level_rec
2121 , p_all_info => FND_API.G_FALSE
2122 , x_Target_tbl => l_Target_tbl_tmp
2123 , x_return_status => x_return_status
2124 , x_error_Tbl => x_error_Tbl
2125 );
2126 BIS_UTILITIES_PUB.put_line(p_text =>'retriving target information for target level 2: '
2127 ||l_Target_Level_rec.target_level_short_name
2128 ||'. Number of targets ALL: '||l_Target_tbl_tmp.COUNT);
2129
2130 -- Since there is no option to choose previous or current period
2131 -- when scheduling alerts from a report, we assume Previous.
2132 --
2133 l_current_row := 'N';
2134
2135 -- get current targets if specified. this is a BIG
2136 -- performance bottleneck.
2137 --
2138 IF l_current_row = 'Y' THEN
2139 l_Target_tbl_tmp_p := l_Target_tbl_tmp;
2140 Get_Current_Target
2141 ( p_Target_tbl => l_Target_tbl_tmp_p
2142 , p_target_level_rec => l_target_level_rec
2143 -- , p_alert_based_on => p_alert_based_on
2144 , x_target_tbl => l_target_tbl_tmp
2145 , x_return_status => x_return_status
2146 );
2147 ELSIF l_current_row = 'N' THEN
2148 l_Target_tbl_tmp_p := l_Target_tbl_tmp;
2149 Get_Previous_Target
2150 ( p_Target_tbl => l_Target_tbl_tmp_p
2151 , p_target_level_rec => l_target_level_rec
2152 , x_target_tbl => l_target_tbl_tmp
2153 , x_return_status => x_return_status
2154 );
2155 ELSE
2156 BIS_UTILITIES_PUB.put_line(p_text =>'neither previous or current target!!');
2157
2158 END IF;
2159
2160 FOR j IN 1..l_Target_tbl_tmp.COUNT LOOP
2161 x_Target_tbl(x_Target_tbl.COUNT+1) := l_Target_tbl_tmp(j);
2162 END LOOP;
2163
2164 l_Target_tbl_tmp.delete;
2165
2166 END IF;
2167
2168 --x_target_tbl := l_target_tbl;
2169 BIS_UTILITIES_PUB.put_line(p_text =>'!!Number of targets retrieved: '||x_target_tbl.count);
2170
2171 EXCEPTION
2172 when FND_API.G_EXC_ERROR then
2173 x_return_status := FND_API.G_RET_STS_ERROR ;
2174 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 at Retrieve_target_info: '||sqlerrm);
2175 when FND_API.G_EXC_UNEXPECTED_ERROR then
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2177 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 at Retrieve_target_info: '||sqlerrm);
2178 when others then
2179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2180 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 at Retrieve_target_info: '||sqlerrm);
2181 l_error_tbl := x_error_Tbl;
2182 BIS_UTILITIES_PVT.Add_Error_Message
2183 ( p_error_msg_id => SQLCODE
2184 , p_error_description => SQLERRM
2185 , p_error_proc_name => G_PKG_NAME||'.Retrieve_target_info'
2186 , p_error_table => l_error_tbl
2187 , x_error_table => x_error_tbl
2188 );
2189
2190 End Retrieve_target_info;
2191
2192
2193 Procedure Get_Previous_Target
2194 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
2195 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
2196 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
2197 , x_return_status OUT NOCOPY VARCHAR2
2198 )
2199 IS
2200
2201 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
2202 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
2203 l_dimension_level_number NUMBER;
2204 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2205 l_time_id VARCHAR2(80);
2206 l_target_count NUMBER := 1;
2207 l_Org_Level_Value_ID VARCHAR2(40);
2208 l_Org_Short_Name VARCHAR2(80);
2209
2210 BEGIN
2211 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2212 --l_target_tbl := x_target_tbl ;
2213
2214 FOR j in 1..p_Target_tbl.COUNT
2215 LOOP
2216
2217 BIS_UTILITIES_PUB.put_line(p_text =>' -------- Target # ' || j || ' ---------- ' );
2218
2219 BIS_TARGET_PVT.Retrieve_Org_level_value
2220 ( p_api_version => 1.0
2221 , p_Target_Rec => p_Target_tbl(j)
2222 , x_Dim_Level_value_Rec => l_Dim_Level_value_Rec
2223 , x_dimension_level_number => l_dimension_level_number
2224 , x_return_status => x_return_status
2225 , x_error_Tbl => l_error_Tbl
2226 );
2227
2228 l_Org_Level_Value_ID := l_Dim_Level_value_Rec.Dimension_Level_Value_ID;
2229 l_Org_Short_Name := l_Dim_Level_value_Rec.Dimension_Level_Short_Name;
2230
2231 /*
2232 if (
2233 (BIS_UTILITIES_PUB.Value_Not_Missing( l_Org_Level_Value_ID ) = FND_API.G_TRUE)
2234 and (BIS_UTILITIES_PUB.Value_Not_Null( l_Org_Level_Value_ID ) = FND_API.G_TRUE)
2235 )
2236 then
2237 BIS_UTILITIES_PUB.put_line(p_text =>' Org level value id for prev tgt is: ' || l_Org_Level_Value_ID);
2238 else
2239 BIS_UTILITIES_PUB.put_line(p_text =>' Org level value id is prev tgt null/missing ' );
2240 end if;
2241
2242 if (
2243 (BIS_UTILITIES_PUB.Value_Not_Missing( l_Org_Short_Name ) = FND_API.G_TRUE)
2244 and (BIS_UTILITIES_PUB.Value_Not_Null( l_Org_Short_Name ) = FND_API.G_TRUE)
2245 )
2246 then
2247 BIS_UTILITIES_PUB.put_line(p_text =>' Org short name is prev tgt: ' || l_Org_Short_Name );
2248 else
2249 BIS_UTILITIES_PUB.put_line(p_text =>' Org level short name is prev tgt null/missing ' );
2250 end if;
2251 */
2252
2253
2254 BIS_TARGET_PVT.Retrieve_Time_level_value
2255 ( p_api_version => 1.0
2256 , p_Target_Rec => p_Target_tbl(j)
2257 , x_Dim_Level_value_Rec => l_Dim_Level_value_Rec
2258 , x_dimension_level_number => l_dimension_level_number
2259 , x_return_status => x_return_status
2260 , x_error_Tbl => l_error_Tbl
2261 );
2262
2263 -- BIS_UTILITIES_PUB.put_line(p_text =>' Time Level dimension sequence no : ' || l_dimension_level_number);
2264
2265 IF BIS_DIM_LEVEL_VALUE_PVT.Is_Previous_Time_Period
2266 ( l_Dim_Level_Value_Rec
2267 , l_Org_Level_Value_ID
2268 , l_Org_Short_Name
2269 , l_time_id)
2270 THEN
2271 /*
2272 BIS_UTILITIES_PUB.put_line(p_text => ' This time level value is PREVIOUS time period ' ) ;
2273 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieved previous period: '
2274 ||l_Dim_Level_Value_Rec.Dimension_Level_value_id );
2275 */
2276
2277 l_target_tbl(l_target_tbl.COUNT+1) := p_target_tbl(j);
2278 ELSIF (p_target_tbl(j).is_pm_region = BIS_TARGET_PUB.G_IS_PM_REGION) THEN -- see Get_Current_Target
2279
2280 -- BIS_UTILITIES_PUB.put_line(p_text => ' BISVARSB: this time level value is NOT PREVIOUS time period ' ) ;
2281
2282 if BIS_UTILITIES_PUB.Value_Missing(p_Target_tbl(j).target) = FND_API.G_TRUE
2283 or BIS_UTILITIES_PUB.Value_NULL(p_Target_tbl(j).target)= FND_API.G_TRUE
2284 then
2285 l_target_count := l_target_tbl.COUNT+1;
2286
2287 l_target_tbl(l_target_count) := p_target_tbl(j);
2288
2289 if l_dimension_level_number = 1 then
2290 l_target_tbl(l_target_count).Dim1_Level_Value_ID
2291 := l_time_id;
2292 elsif l_dimension_level_number = 2 then
2293 l_target_tbl(l_target_count).Dim2_Level_Value_ID
2294 := l_time_id;
2295 elsif l_dimension_level_number = 3 then
2296 l_target_tbl(l_target_count).Dim3_Level_Value_ID
2297 := l_time_id;
2298 elsif l_dimension_level_number = 4 then
2299 l_target_tbl(l_target_count).Dim4_Level_Value_ID
2300 := l_time_id;
2301 elsif l_dimension_level_number = 5 then
2302 l_target_tbl(l_target_count).Dim5_Level_Value_ID
2303 := l_time_id;
2304 elsif l_dimension_level_number = 6 then
2305 l_target_tbl(l_target_count).Dim6_Level_Value_ID
2306 := l_time_id;
2307 elsif l_dimension_level_number = 7 then
2308 l_target_tbl(l_target_count).Dim7_Level_Value_ID
2309 := l_time_id;
2310 end if;
2311
2312 BIS_UTILITIES_PUB.put_line(p_text =>'Time Level Replaced with the Previous Level');
2313
2314 -- else
2315 -- null;
2316 -- BIS_UTILITIES_PUB.put_line(p_text =>'Failed to retrieve previous period: ' ||l_Dim_Level_value_Rec.Dimension_Level_value_id);
2317 end if;
2318
2319 END IF;
2320 END LOOP;
2321
2322 x_target_tbl := l_target_tbl ;
2323
2324 EXCEPTION
2325 when others then
2326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2327 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_previous_Target: '||sqlerrm);
2328
2329 END Get_Previous_Target;
2330
2331
2332
2333 Procedure Get_Current_Target
2334 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
2335 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
2336 --, p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
2337 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
2338 , x_return_status OUT NOCOPY VARCHAR2
2339 )
2340 IS
2341
2342 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
2343 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
2344 l_dimension_level_number NUMBER;
2345 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2346 l_time_id VARCHAR2(80);
2347 l_target_count NUMBER := 1;
2348 l_Org_Level_Value_ID VARCHAR2(40);-- := '204';
2349 l_Org_Short_Name VARCHAR2(80);
2350
2351
2352 BEGIN
2353
2354 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2355 --l_target_tbl := x_target_tbl ;
2356 --BIS_UTILITIES_PUB.put_line(p_text =>'Retrieving current target.');
2357
2358 FOR j in 1..p_Target_tbl.COUNT
2359 LOOP
2360
2361 BIS_UTILITIES_PUB.put_line(p_text =>' -------- Target # ' || j || ' ---------- ' );
2362
2363 BIS_TARGET_PVT.Retrieve_Org_level_value
2364 ( p_api_version => 1.0
2365 , p_Target_Rec => p_Target_tbl(j)
2366 , x_Dim_Level_value_Rec => l_Dim_Level_value_Rec
2367 , x_dimension_level_number => l_dimension_level_number
2368 , x_return_status => x_return_status
2369 , x_error_Tbl => l_error_Tbl
2370 );
2371
2372
2373 l_Org_Level_Value_ID := l_Dim_Level_value_Rec.Dimension_Level_Value_ID;
2374 l_Org_Short_Name := l_Dim_Level_value_Rec.Dimension_Level_Short_Name;
2375
2376 /*
2377 if (
2378 (BIS_UTILITIES_PUB.Value_Not_Missing( l_Org_Level_Value_ID ) = FND_API.G_TRUE)
2379 and (BIS_UTILITIES_PUB.Value_Not_Null( l_Org_Level_Value_ID ) = FND_API.G_TRUE)
2380 )
2381 then
2382 BIS_UTILITIES_PUB.put_line(p_text =>' Org level value id current target is: ' || l_Org_Level_Value_ID);
2383 else
2384 BIS_UTILITIES_PUB.put_line(p_text =>' Org level value id is current target null/missing ' );
2385 end if;
2386
2387 if (
2388 (BIS_UTILITIES_PUB.Value_Not_Missing( l_Org_Short_Name ) = FND_API.G_TRUE)
2389 and (BIS_UTILITIES_PUB.Value_Not_Null( l_Org_Short_Name ) = FND_API.G_TRUE)
2390 )
2391 then
2392 BIS_UTILITIES_PUB.put_line(p_text =>' Org short name is current target : ' || l_Org_Short_Name );
2393 else
2394 BIS_UTILITIES_PUB.put_line(p_text =>' Org level short name is current target null/missing ' );
2395 end if;
2396 */
2397
2398
2399 BIS_TARGET_PVT.Retrieve_Time_level_value
2400 ( p_api_version => 1.0
2401 , p_Target_Rec => p_Target_tbl(j)
2402 , x_Dim_Level_value_Rec => l_Dim_Level_value_Rec
2403 , x_dimension_level_number => l_dimension_level_number
2404 , x_return_status => x_return_status
2405 , x_error_Tbl => l_error_Tbl
2406 );
2407 -- BIS_UTILITIES_PUB.put_line(p_text =>' Time Level value no : ' || l_dimension_level_number);
2408
2409 IF BIS_DIM_LEVEL_VALUE_PVT.Is_Current_Time_Period
2410 ( l_Dim_Level_Value_Rec
2411 , l_Org_Level_Value_ID
2412 , l_Org_Short_Name
2413 , l_time_id)
2414 THEN
2415 /*
2416 BIS_UTILITIES_PUB.put_line(p_text => ' This time period is in current period ' ) ;
2417
2418 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieved current period: '
2419 ||l_Dim_Level_Value_Rec.Dimension_Level_value_id );
2420 */
2421
2422 l_target_tbl(l_target_tbl.COUNT+1) := p_target_tbl(j);
2423
2424 -- The condition below, with targets missing, to be used to identify pm region entries will not be satisfied
2425 -- for "set targets" anymore after change based alerting (bug# 3148615) since change does not have targets-only tolerance
2426 ELSIF (p_target_tbl(j).is_pm_region = BIS_TARGET_PUB.G_IS_PM_REGION) THEN
2427
2428 -- BIS_UTILITIES_PUB.put_line(p_text => ' This time period is not in current period ' ) ;
2429
2430 -- Following step is for the records retrieved
2431 -- from user_ind_selection table
2432
2433 if BIS_UTILITIES_PUB.Value_Missing(p_Target_tbl(j).target) = FND_API.G_TRUE
2434 or BIS_UTILITIES_PUB.Value_NULL(p_Target_tbl(j).target)= FND_API.G_TRUE
2435 then
2436 l_target_count := l_target_tbl.COUNT+1;
2437
2438 l_target_tbl(l_target_count) := p_target_tbl(j);
2439
2440 if l_dimension_level_number = 1 then
2441 l_target_tbl(l_target_count).Dim1_Level_Value_ID
2442 := l_time_id;
2443 elsif l_dimension_level_number = 2 then
2444 l_target_tbl(l_target_count).Dim2_Level_Value_ID
2445 := l_time_id;
2446 elsif l_dimension_level_number = 3 then
2447 l_target_tbl(l_target_count).Dim3_Level_Value_ID
2448 := l_time_id;
2449 elsif l_dimension_level_number = 4 then
2450 l_target_tbl(l_target_count).Dim4_Level_Value_ID
2451 := l_time_id;
2452 elsif l_dimension_level_number = 5 then
2453 l_target_tbl(l_target_count).Dim5_Level_Value_ID
2454 := l_time_id;
2455 elsif l_dimension_level_number = 6 then
2456 l_target_tbl(l_target_count).Dim6_Level_Value_ID
2457 := l_time_id;
2458 elsif l_dimension_level_number = 7 then
2459 l_target_tbl(l_target_count).Dim7_Level_Value_ID
2460 := l_time_id;
2461 end if;
2462
2463 BIS_UTILITIES_PUB.put_line(p_text =>'Dim 6 Value ' || l_target_tbl(l_target_count).Dim6_Level_Value_ID);
2464
2465 BIS_UTILITIES_PUB.put_line(p_text =>'Time Level Replaced with the Current Level');
2466
2467 -- else
2468 -- null;
2469 -- BIS_UTILITIES_PUB.put_line(p_text =>'Failed to retrieve current period: ' ||l_Dim_Level_value_Rec.Dimension_Level_value_id);
2470 end if;
2471
2472 END IF;
2473 END LOOP;
2474
2475 x_target_tbl := l_target_tbl ;
2476
2477 EXCEPTION
2478 when others then
2479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2480 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_current_Target: '||sqlerrm);
2481
2482 END Get_current_Target;
2483
2484
2485
2486 Procedure update_total_time
2487 ( p_Target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
2488 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_rec_Type
2489 --, p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
2490 , x_target_tbl IN OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
2491 , x_return_status OUT NOCOPY VARCHAR2
2492 )
2493 IS
2494
2495 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
2496 l_Dim_Level_Value_Rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
2497 l_dimension_level_number NUMBER;
2498 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2499 l_time_id VARCHAR2(80);
2500 l_target_count NUMBER := 1;
2501
2502 BEGIN
2503
2504 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2505 --l_target_tbl := x_target_tbl ;
2506 BIS_UTILITIES_PUB.put_line(p_text =>'Total Record in ' || p_Target_tbl.count);
2507
2508 FOR j in 1..p_Target_tbl.COUNT
2509 LOOP
2510
2511 BIS_TARGET_PVT.Retrieve_Time_level_value
2512 ( p_api_version => 1.0
2513 , p_Target_Rec => p_Target_tbl(j)
2514 , x_Dim_Level_value_Rec => l_Dim_Level_value_Rec
2515 , x_dimension_level_number => l_dimension_level_number
2516 , x_return_status => x_return_status
2517 , x_error_Tbl => l_error_Tbl
2518 );
2519
2520 BIS_UTILITIES_PUB.put_line(p_text =>'L Total Time Level value no : ' || l_dimension_level_number);
2521 if substr(l_Dim_Level_Value_Rec.Dimension_Level_short_name,1,3) = 'EDW' then
2522 l_time_id := 1;
2523 BIS_UTILITIES_PUB.put_line(p_text =>'EDW Total Time ');
2524 else
2525 l_time_id := -1;
2526 BIS_UTILITIES_PUB.put_line(p_text =>'OLTP Total Time ');
2527 end if;
2528 -- Explained in Get_Current_Target() above.
2529 IF ( (p_Target_tbl(j).is_pm_region = BIS_TARGET_PUB.G_IS_PM_REGION) AND
2530 ((BIS_UTILITIES_PUB.Value_Missing(p_Target_tbl(j).target) = FND_API.G_TRUE) OR (BIS_UTILITIES_PUB.Value_NULL(p_Target_tbl(j).target)= FND_API.G_TRUE)) ) THEN
2531
2532 l_target_count := l_target_tbl.COUNT+1;
2533
2534 l_target_tbl(l_target_count) := p_target_tbl(j);
2535
2536 if l_dimension_level_number = 1 then
2537 l_target_tbl(l_target_count).Dim1_Level_Value_ID
2538 := l_time_id;
2539 elsif l_dimension_level_number = 2 then
2540 l_target_tbl(l_target_count).Dim2_Level_Value_ID
2541 := l_time_id;
2542 elsif l_dimension_level_number = 3 then
2543 l_target_tbl(l_target_count).Dim3_Level_Value_ID
2544 := l_time_id;
2545 elsif l_dimension_level_number = 4 then
2546 l_target_tbl(l_target_count).Dim4_Level_Value_ID
2547 := l_time_id;
2548 elsif l_dimension_level_number = 5 then
2549 l_target_tbl(l_target_count).Dim5_Level_Value_ID
2550 := l_time_id;
2551 elsif l_dimension_level_number = 6 then
2552 l_target_tbl(l_target_count).Dim6_Level_Value_ID
2553 := l_time_id;
2554 elsif l_dimension_level_number = 7 then
2555 l_target_tbl(l_target_count).Dim7_Level_Value_ID
2556 := l_time_id;
2557 end if;
2558
2559 BIS_UTILITIES_PUB.put_line(p_text =>'Dim 6 Value ' || l_target_tbl(l_target_count).Dim6_Level_Value_ID);
2560 BIS_UTILITIES_PUB.put_line(p_text =>'Time Level Replaced with the Current Level');
2561
2562 ELSE
2563 l_target_count := l_target_tbl.COUNT+1;
2564 l_target_tbl(l_target_count) := p_target_tbl(j);
2565
2566 END IF;
2567 END LOOP;
2568
2569 x_target_tbl := l_target_tbl ;
2570
2571 BIS_UTILITIES_PUB.put_line(p_text =>'Total Record out NOCOPY ' || x_Target_tbl.count);
2572
2573 EXCEPTION
2574 when others then
2575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2576 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_current_Target: '||sqlerrm);
2577
2578 END update_total_time;
2579
2580 --
2581 -- Obsolete
2582 --
2583 -- Following procedure to retrieve the repeating interval for
2584 -- the concurrent request need to be changed. This is temparory till
2585 -- we put the repeating interval option in the Alert registration
2586 -- Page.
2587 --
2588 PROCEDURE get_repeating_interval
2589 ( p_time_dimension_level_id IN varchar2
2590 , x_repeat_interval OUT NOCOPY NUMBER
2591 , x_repeat_unit OUT NOCOPY varchar2
2592 )
2593 IS
2594 l_view_name varchar2(100);
2595 l_start_date date;
2596 l_end_date date;
2597 l_sql_stmnt varchar2(32000);
2598 type c1_cursor is ref cursor;
2599 c1 c1_cursor;
2600
2601 BEGIN
2602
2603 select LEVEL_VALUES_VIEW_NAME
2604 into l_view_name
2605 from bisbv_dimension_levels
2606 where DIMENSION_LEVEL_ID = p_time_dimension_level_id;
2607
2608 l_sql_stmnt := 'select distinct start_date, end_date from '
2609 || l_view_name
2610 ||' where sysdate between nvl(start_date,sysdate) '
2611 ||' and nvl(end_date,sysdate) ';
2612
2613 open c1 for l_sql_stmnt;
2614 fetch c1 into l_start_date, l_end_date;
2615 if last_day(sysdate) = l_end_date
2616 and last_day(add_months(sysdate,-1)) + 1 = l_start_date
2617 then
2618 x_repeat_interval := 1;
2619 x_repeat_unit := 'MONTHS';
2620 elsif round(months_between(l_end_date,l_start_date),1) = 3
2621 then
2622 x_repeat_interval := 3;
2623 x_repeat_unit := 'MONTHS';
2624 elsif round(months_between(l_end_date,l_start_date),1) = 12
2625 then
2626 x_repeat_interval := 12;
2627 x_repeat_unit := 'MONTHS';
2628 else
2629 x_repeat_interval := l_end_date - l_start_date;
2630 x_repeat_unit := 'DAYS';
2631 end if;
2632
2633 EXCEPTION
2634 WHEN OTHERS THEN
2635 x_repeat_interval := 0;
2636 x_repeat_unit := SQLERRM;
2637
2638 END get_repeating_interval;
2639
2640 --
2641 -- Helper routin which calls fnd_request APIs
2642 -- For scheduled alerts
2643 --
2644 PROCEDURE submit_conc_request
2645 ( p_request_id OUT NOCOPY varchar2
2646 , p_StartTime IN varchar2 default null
2647 , p_EndTime IN varchar2 default null
2648 , p_frequencyInterval IN varchar2 default null
2649 , p_frequencyUnits IN varchar2 default null
2650 , p_performance_measure_id IN NUMBER default null
2651 , p_time_dimension_level_id IN NUMBER default null
2652 , p_session_id IN varchar2 default null
2653 , p_current_row IN varchar2 default null
2654 , p_alert_type IN varchar2 default null
2655 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
2656 )
2657 IS
2658 l_request_id VARCHAR2(32000);
2659 l_repeat_result VARCHAR2(32000);
2660 l_submit_result varchar2(32000);
2661 l_request_desc varchar2(32000);
2662 l_measure_rec BIS_MEASURE_PUB.measure_rec_type;
2663 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
2664 l_return_status VARCHAR2(1000);
2665 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2666 l_api_version NUMBER := 1;
2667 l_Concurrent_Request_rec BIS_CONCURRENT_MANAGER_PVT.PMF_Request_rec_Type;
2668 ERRBUF VARCHAR2(32000);
2669 RETCODE VARCHAR2(32000);
2670 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2671 l_measure_rec_p BIS_MEASURE_PUB.measure_rec_type;
2672 l_dimension_level_rec_p BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
2673
2674 begin
2675
2676 BIS_UTILITIES_PUB.put_line(p_text =>'Submitting detailed concurrent request');
2677 BIS_UTILITIES_PUB.put_line(p_text =>'alert type: '||p_alert_type);
2678 l_debug_text := p_request_id||l_request_id;
2679
2680 l_measure_rec.measure_id := p_performance_measure_id;
2681 l_measure_rec_p := l_measure_rec;
2682 BIS_MEASURE_PUB.Retrieve_Measure
2683 ( p_api_version => l_api_version
2684 , p_measure_rec => l_measure_rec_p
2685 , p_all_info => FND_API.G_FALSE
2686 , x_Measure_rec => l_measure_rec
2687 , x_return_status => l_return_status
2688 , x_error_Tbl => l_error_tbl
2689 );
2690
2691 l_debug_text:= l_debug_text||' retrieve measure: '||l_return_status
2692 ||', short name: '||l_measure_rec.measure_short_Name;
2693
2694
2695 l_dimension_level_rec.dimension_level_id := p_time_dimension_level_id ;
2696 l_dimension_level_rec_p := l_dimension_level_rec;
2697 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level
2698 ( p_api_version => 1.0
2699 , p_dimension_Level_Rec => l_dimension_level_rec_p
2700 , x_dimension_Level_rec => l_dimension_level_rec
2701 , x_return_status => l_return_status
2702 , x_error_tbl => l_error_tbl
2703 );
2704 l_debug_text:= l_debug_text||' retrieve dimension level: '||l_return_status
2705 ||', short name: '
2706 ||l_dimension_level_rec.dimension_level_short_Name;
2707
2708
2709 /* Obsoleted.
2710
2711 -- Following procedure to retrieve the repeating interval for
2712 -- the concurrent request need to be changed. This is temparory till
2713 -- we put the repeating interval option in the Alert registration
2714 -- Page.
2715 get_repeating_interval( p_time_dimension_level_id
2716 , l_repeat_interval
2717 , l_repeat_unit);
2718 */
2719
2720 BIS_CONCURRENT_MANAGER_PVT.Set_Repeat_Options
2721 ( p_repeat_interval => p_frequencyInterval
2722 , p_repeat_units => p_frequencyUnits
2723 , P_Start_time => p_StartTime
2724 , P_end_time => p_EndTime
2725 , x_result => l_repeat_result
2726 );
2727 commit;
2728 BIS_UTILITIES_PUB.put_line(p_text =>'Setting repeat result: '||l_repeat_result);
2729 l_debug_text:= l_debug_text||' Setting repeat result: '||l_repeat_result;
2730
2731 l_request_desc
2732 := BIS_UTILITIES_PVT.Get_FND_Message
2733 ( p_message_name => G_ALERT_CONC_REQ_NAME
2734 , p_msg_param1 => 'MEASURE_NAME'
2735 , p_msg_param1_val => l_measure_rec.MEASURE_NAME
2736 , p_msg_param2 => 'TIME_LEVEL'
2737 , p_msg_param2_val => l_dimension_level_rec.Dimension_Level_Name
2738 );
2739
2740 Form_Concurrent_Request
2741 ( p_request_desc => l_request_desc
2742 , p_Start_Time => p_startTime
2743 , p_measure_id => l_measure_rec.measure_id
2744 , p_Measure_short_name => l_measure_rec.measure_short_name
2745 , p_time_level_id => l_dimension_level_rec.Dimension_Level_id
2746 , p_alert_type => p_alert_type
2747 , p_current_row => p_current_row
2748 , p_alert_based_on => p_alert_based_on
2749 , x_Concurrent_Request_rec => l_Concurrent_Request_rec
2750 );
2751
2752 BIS_UTILITIES_PUB.put_line(p_text =>'Submit concurrent requests for Detail Alert Service');
2753 --
2754 BIS_CONCURRENT_MANAGER_PVT.Submit_Concurrent_Request
2755 ( p_Concurrent_Request_rec => l_Concurrent_Request_rec
2756 , x_request_id => l_request_id
2757 , x_errbuf => ERRBUF
2758 , x_retcode => RETCODE
2759 );
2760 l_debug_text:= l_debug_text||' Submit concurrent requests result: '
2761 ||ERRBUF||', code: '||RETCODE;
2762
2763 IF ((l_request_id = 0) OR (l_request_id IS NULL)) THEN -- 2568688
2764 l_submit_result := BIS_UTILITIES_PVT.Get_FND_Message
2765 ( p_message_name => G_FAILED_MSG)
2766 || ' ' || ERRBUF ;
2767 ELSE
2768 l_submit_result := BIS_UTILITIES_PVT.Get_FND_Message
2769 ( p_message_name => G_SUCCESS_MSG
2770 , p_msg_param1 => G_REQUEST_ID_TOK
2771 , p_msg_param1_val => l_request_id
2772 );
2773 END IF;
2774
2775 BIS_UTILITIES_PUB.put_line(p_text =>'Submission result: '||l_submit_result);
2776 --p_request_id := l_submit_result||'--submit_conc_request debug--'
2777 --||l_request_id ||' -- debug -- '||l_debug_text;
2778
2779 p_request_id := l_submit_result;
2780
2781 EXCEPTION
2782 when FND_API.G_EXC_ERROR then
2783 l_return_status := FND_API.G_RET_STS_ERROR ;
2784 p_request_id := l_request_id
2785 ||' exception 1 in submit_conc_request '||sqlerrm;
2786 when FND_API.G_EXC_UNEXPECTED_ERROR then
2787 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2788 p_request_id := l_request_id
2789 ||' exception 2 in submit_conc_request '||sqlerrm;
2790 when others then
2791 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2792 p_request_id := l_request_id
2793 ||' exception 3 in submit_conc_request '||sqlerrm;
2794 l_error_tbl_p := l_error_tbl;
2795 BIS_UTILITIES_PVT.Add_Error_Message
2796 ( p_error_msg_id => SQLCODE
2797 , p_error_description => SQLERRM
2798 , p_error_proc_name => 'submit_conc_request'
2799 , p_error_table => l_error_tbl_p
2800 , x_error_table => l_error_tbl
2801 );
2802
2803 end submit_conc_request;
2804
2805 --
2806 -- Helper routin which calls fnd_request APIs
2807 -- For scheduled alerts
2808 --
2809 PROCEDURE submit_Concurrent_Request
2810 ( p_report_data_Tbl IN report_data_tbl_type
2811 ,p_performance_measure_id IN NUMBER default null
2812 ,p_time_dimension_level_id IN NUMBER default null
2813 ,p_session_id IN varchar2 default null
2814 ,p_StartTime IN varchar2 default null
2815 ,p_EndTime IN varchar2 default null
2816 ,p_frequencyInterval IN varchar2 default null
2817 ,p_frequencyUnits IN varchar2 default null
2818 ,p_current_row IN varchar2 default null
2819 , x_request_id OUT NOCOPY VARCHAR2
2820 )
2821 IS
2822
2823 l_request_id VARCHAR2(2000);
2824 l_result BOOLEAN;
2825 l_request_desc varchar2(32000);
2826 l_user_name VARCHAR2(32000);
2827 l_measure_rec BIS_MEASURE_PUB.measure_rec_type;
2828 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
2829 l_return_status VARCHAR2(1000);
2830 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2831 l_api_version NUMBER := 1;
2832
2833 BEGIN
2834
2835 BIS_UTILITIES_PUB.put_line(p_text =>'submitting report data concurrent request');
2836
2837 EXCEPTION
2838 when FND_API.G_EXC_ERROR then
2839 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 in submit_Concurrent_Request');
2840 when FND_API.G_EXC_UNEXPECTED_ERROR then
2841 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 in submit_Concurrent_Request');
2842 when others then
2843 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 submit_Concurrent_Request: '||sqlerrm);
2844
2845 END submit_Concurrent_Request;
2846
2847 PROCEDURE Retrieve_Report_Info
2848 ( p_measure_id IN NUMBER
2849 , p_time_level_id IN NUMBER default null
2850 , p_plan_id IN NUMBER default null
2851 , p_parameter1_level IN varchar2 default null
2852 , p_parameter1_value IN varchar2 default null
2853 , p_parameter2_level IN varchar2 default null
2854 , p_parameter2_value IN varchar2 default null
2855 , p_parameter3_level IN varchar2 default null
2856 , p_parameter3_value IN varchar2 default null
2857 , p_parameter4_level IN varchar2 default null
2858 , p_parameter4_value IN varchar2 default null
2859 , p_parameter5_level IN varchar2 default null
2860 , p_parameter5_value IN varchar2 default null
2861 , p_parameter6_level IN varchar2 default null
2862 , p_parameter6_value IN varchar2 default null
2863 , p_parameter7_level IN varchar2 default null
2864 , p_parameter7_value IN varchar2 default null
2865 , p_viewby_level_id IN varchar2 default null
2866 , x_target_level_tbl OUT NOCOPY BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_type
2867 , x_target_tbl OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_type
2868 , x_return_status OUT NOCOPY VARCHAR2
2869 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2870 )
2871 IS
2872
2873 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
2874 l_Target_Level_tbl BIS_Target_Level_PUB.Target_Level_Tbl_Type;
2875 l_Target_Level_rec BIS_Target_Level_PUB.Target_Level_rec_Type;
2876 l_Target_Level_tbl_tmp BIS_Target_Level_PUB.Target_Level_Tbl_Type;
2877 l_target_tbl BIS_TARGET_PUB.Target_Tbl_type;
2878 l_target_rec BIS_TARGET_PUB.Target_rec_type;
2879 l_target_tbl_tmp BIS_TARGET_PUB.Target_Tbl_type;
2880 l_return_status VARCHAR2(32000);
2881 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2882 l_param_set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
2883
2884 BEGIN
2885
2886 BIS_UTILITIES_PUB.put_line(p_text =>'retrieving report info');
2887
2888 l_Measure_Rec.Measure_id := p_measure_id;
2889
2890 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Levels
2891 ( p_api_version => 1.0
2892 , p_all_info => FND_API.G_FALSE
2893 , p_Measure_Rec => l_Measure_Rec
2894 , x_Target_Level_tbl => l_Target_Level_tbl_tmp
2895 , x_return_status => l_return_status
2896 , x_error_Tbl => l_error_Tbl
2897 );
2898 BIS_UTILITIES_PUB.put_line(p_text =>'Number of target levels retrieved TOTAL: '
2899 ||l_Target_Level_tbl_tmp.COUNT );
2900
2901 -- Get all target levels with these dimension levels
2902 -- and this viewBy level
2903 --
2904 FOR i in 1..l_Target_Level_tbl_tmp.COUNT LOOP
2905 BIS_UTILITIES_PUB.put_line(p_text =>' l_Target_Level_tbl_TMP! target_level id: '
2906 ||l_Target_Level_tbl_tmp(i).Target_level_id);
2907 Verify_Target_Level
2908 ( p_Target_Level_rec => l_Target_Level_tbl_tmp(i)
2909 , p_parameter1_level => p_parameter1_level
2910 , p_parameter2_level => p_parameter2_level
2911 , p_parameter3_level => p_parameter3_level
2912 , p_parameter4_level => p_parameter4_level
2913 , p_parameter5_level => p_parameter5_level
2914 , p_parameter6_level => p_parameter6_level
2915 , p_parameter7_level => p_parameter7_level
2916 , p_viewby_level_id => p_viewby_level_id
2917 , x_Target_Level_rec => l_Target_Level_rec
2918 );
2919
2920 IF BIS_UTILITIES_PUB.Value_NOT_Missing(l_Target_Level_rec.Target_Level_ID)
2921 = FND_API.G_TRUE
2922 AND BIS_UTILITIES_PUB.Value_NOT_Null(l_Target_Level_rec.Target_Level_ID)
2923 = FND_API.G_TRUE
2924 THEN
2925 l_Target_Level_tbl(l_Target_Level_tbl.COUNT+1) := l_Target_Level_rec;
2926 BIS_UTILITIES_PUB.put_line(p_text =>'Verified target level: '
2927 ||l_Target_Level_tbl(l_Target_Level_tbl.COUNT).Target_level_id);
2928 END IF;
2929
2930 END LOOP;
2931 BIS_UTILITIES_PUB.put_line(p_text =>'Number of target levels retrieved for Report INFO: '
2932 ||l_Target_Level_tbl.COUNT );
2933
2934 -- Get all targets, then filter out NOCOPY only targets with passed in
2935 -- dimension level values
2936 --
2937 FOR i in 1..l_Target_Level_tbl.COUNT LOOP
2938
2939 BIS_UTILITIES_PUB.put_line(p_text =>'Getting targets for target level: '
2940 ||l_target_level_tbl(i).target_level_id);
2941
2942 Retrieve_target_info
2943 ( p_api_version => 1.0
2944 , p_target_level_id => l_target_level_tbl(i).target_level_id
2945 , p_time_dimension_level_id => NULL
2946 , p_current_row => 'Y'
2947 , x_target_tbl => l_target_tbl_tmp
2948 , x_return_status => l_return_status
2949 , x_error_Tbl => l_error_Tbl
2950 );
2951 BIS_UTILITIES_PUB.put_line(p_text =>'Number of Target retrieved TOTAL: '||l_Target_tbl_tmp.COUNT );
2952
2953 FOR j in 1..l_Target_tbl_tmp.COUNT LOOP
2954 BIS_UTILITIES_PUB.put_line(p_text =>'target for target level: '||l_Target_tbl_tmp(j).target_level_id);
2955 Verify_Target
2956 ( p_Target_rec => l_Target_tbl_tmp(j)
2957 , p_parameter1_Value => p_parameter1_Value
2958 , p_parameter2_Value => p_parameter2_Value
2959 , p_parameter3_Value => p_parameter3_Value
2960 , p_parameter4_Value => p_parameter4_Value
2961 , p_parameter5_Value => p_parameter5_Value
2962 , p_parameter6_Value => p_parameter6_Value
2963 , p_parameter7_Value => p_parameter7_Value
2964 , x_Target_rec => l_Target_rec
2965 );
2966 BIS_UTILITIES_PUB.put_line(p_text =>'AFTER Verify: '||l_Target_rec.Target_level_id);
2967
2968 IF BIS_UTILITIES_PUB.Value_NOT_Missing(l_Target_rec.Target_Level_ID)
2969 = FND_API.G_TRUE
2970 AND BIS_UTILITIES_PUB.Value_NOT_Null(l_Target_rec.Target_Level_ID)
2971 = FND_API.G_TRUE
2972 THEN
2973 l_Target_tbl(l_Target_tbl.COUNT+1) := l_Target_rec;
2974 BIS_UTILITIES_PUB.put_line(p_text =>'Verified target: '
2975 ||l_Target_tbl(l_Target_tbl.COUNT).Target_level_id);
2976 END IF;
2977
2978 END LOOP;
2979 l_target_tbl_tmp.delete;
2980
2981 BIS_UTILITIES_PUB.put_line(p_text =>'Number of targets retrieved for Report INFO: '||l_Target_tbl.COUNT);
2982
2983 END LOOP;
2984
2985 x_target_level_tbl := l_target_level_tbl;
2986 x_target_tbl := l_target_tbl;
2987
2988 EXCEPTION
2989 when FND_API.G_EXC_ERROR then
2990 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 in Retrieve_Report_Info');
2991 when FND_API.G_EXC_UNEXPECTED_ERROR then
2992 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 in Retrieve_Report_Info');
2993 when others then
2994 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 in Retrieve_Report_Info: '||sqlerrm);
2995
2996 END Retrieve_Report_Info;
2997
2998 Procedure Set_Notify_Owners
2999 ( p_alert_type IN VARCHAR2
3000 , x_notify_owners_flag OUT NOCOPY VARCHAR2
3001 )
3002 IS
3003 BEGIN
3004
3005 IF UPPER(p_alert_type) = G_TARGET_LEVEL THEN
3006 x_notify_owners_flag := 'Y';
3007 ELSIF p_alert_type = G_ALL_TARGET THEN
3008 x_notify_owners_flag := 'N';
3009 END IF;
3010
3011 EXCEPTION
3012 WHEN OTHERS THEN
3013 BIS_UTILITIES_PUB.put_line(p_text =>'exception in Set_Notify_Owners: '||sqlerrm);
3014
3015 END Set_Notify_Owners;
3016
3017 Procedure Form_Concurrent_Request
3018 ( p_request_desc IN VARCHAR2
3019 , p_Start_Time IN VARCHAR2
3020 , p_measure_id IN NUMBER
3021 , p_Measure_short_name IN VARCHAR2
3022 , p_time_level_id IN NUMBER
3023 , p_alert_type IN VARCHAR2
3024 , p_current_row IN VARCHAR2
3025 , p_alert_based_on IN VARCHAR2 := BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_TARGET
3026 , x_Concurrent_Request_rec
3027 OUT NOCOPY BIS_CONCURRENT_MANAGER_PVT.PMF_Request_rec_Type
3028 )
3029 IS
3030
3031 l_Concurrent_Request_rec
3032 BIS_CONCURRENT_MANAGER_PVT.PMF_Request_rec_Type;
3033
3034 BEGIN
3035
3036 l_Concurrent_Request_rec := x_Concurrent_Request_rec;
3037 BIS_UTILITIES_PUB.put_line(p_text =>'Forming concurrent request');
3038
3039 l_Concurrent_Request_rec.application_short_name
3040 := BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME;
3041 l_Concurrent_Request_rec.program
3042 := BIS_CONCURRENT_MANAGER_PVT.G_ALERT_PROGRAM;
3043 l_Concurrent_Request_rec.start_time := p_start_time;
3044 l_Concurrent_Request_rec.description := p_request_desc;
3045
3046 l_Concurrent_Request_rec.argument1 := p_measure_id;
3047 l_Concurrent_Request_rec.argument2 := p_measure_short_name;
3048 l_Concurrent_Request_rec.argument3 := NULL;
3049 l_Concurrent_Request_rec.argument4 := NULL;
3050 l_Concurrent_Request_rec.argument5 := NULL;
3051 l_Concurrent_Request_rec.argument6 := NULL;
3052 l_Concurrent_Request_rec.argument7 := NULL;
3053 l_Concurrent_Request_rec.argument8 := NULL;
3054 l_Concurrent_Request_rec.argument9 := p_time_level_id;
3055 l_Concurrent_Request_rec.argument10 := NULL;
3056 l_Concurrent_Request_rec.argument11 := NULL;
3057 l_Concurrent_Request_rec.argument12 := NULL;
3058 l_Concurrent_Request_rec.argument13 := NULL;
3059 l_Concurrent_Request_rec.argument14 := NULL;
3060 l_Concurrent_Request_rec.argument15 := NULL;
3061 l_Concurrent_Request_rec.argument16 := NULL;
3062 l_Concurrent_Request_rec.argument17 := NULL;
3063 l_Concurrent_Request_rec.argument18 := NULL;
3064 l_Concurrent_Request_rec.argument19 := NULL;
3065 l_Concurrent_Request_rec.argument20 := NULL;
3066 l_Concurrent_Request_rec.argument21 := NULL;
3067 l_Concurrent_Request_rec.argument22 := NULL;
3068 l_Concurrent_Request_rec.argument23 := NULL;
3069 l_Concurrent_Request_rec.argument24 := NULL;
3070 l_Concurrent_Request_rec.argument25 := NULL;
3071 l_Concurrent_Request_rec.argument26 := NULL;
3072 l_Concurrent_Request_rec.argument27 := NULL;
3073 l_Concurrent_Request_rec.argument28 := NULL;
3074 l_Concurrent_Request_rec.argument29 := NULL;
3075 l_Concurrent_Request_rec.argument30 := NULL;
3076 l_Concurrent_Request_rec.argument31 := NULL;
3077 l_Concurrent_Request_rec.argument32 := NULL;
3078 l_Concurrent_Request_rec.argument33 := NULL;
3079
3080 l_Concurrent_Request_rec.argument34 := NULL;
3081 l_Concurrent_Request_rec.argument35 := NULL;
3082 l_Concurrent_Request_rec.argument36 := NULL;
3083 l_Concurrent_Request_rec.argument37 := NULL;
3084 l_Concurrent_Request_rec.argument38 := NULL;
3085 l_Concurrent_Request_rec.argument39 := NULL;
3086
3087 l_Concurrent_Request_rec.argument40 := p_alert_type;
3088 l_Concurrent_Request_rec.argument41 := NULL;
3089 l_Concurrent_Request_rec.argument42 := p_current_row;
3090 l_Concurrent_Request_rec.argument43 := p_alert_based_on;
3091
3092 x_Concurrent_Request_rec := l_Concurrent_Request_rec;
3093 BIS_UTILITIES_PUB.put_line(p_text =>'Request successfully formed.');
3094
3095 EXCEPTION
3096 when FND_API.G_EXC_ERROR then
3097 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 1');
3098 when FND_API.G_EXC_UNEXPECTED_ERROR then
3099 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 2');
3100 when others then
3101 BIS_UTILITIES_PUB.put_line(p_text =>'form conc req 3: '||SQLERRM);
3102 BIS_UTILITIES_PVT.Add_Error_Message
3103 ( p_error_msg_id => SQLCODE
3104 , p_error_description => SQLERRM
3105 , p_error_proc_name => G_PKG_NAME||'.Form_Concurrent_Request'
3106 );
3107
3108 END Form_Concurrent_Request;
3109
3110 -- compares new to orig. if a row in new is not in orig, that row is
3111 -- added to the diff table. all rows (including new) is put into
3112 -- all table.
3113 --
3114 PROCEDURE Compare_param_sets
3115 ( p_param_set_tbl_orig IN BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
3116 , p_param_set_tbl_new IN BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
3117 , x_param_set_tbl_all OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
3118 , x_param_set_tbl_diff OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
3119 )
3120 IS
3121
3122 l_param_set_rec_new BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
3123 l_param_set_tbl_all BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
3124 l_param_set_tbl_diff BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
3125 in_orig BOOLEAN := FALSE;
3126
3127 BEGIN
3128
3129 l_param_set_tbl_all := p_param_set_tbl_orig;
3130 BIS_UTILITIES_PUB.put_line(p_text =>'Comparing new requests');
3131 IF p_param_set_tbl_orig.COUNT = 0 THEN
3132 BIS_UTILITIES_PUB.put_line(p_text =>'No original requests. All new requests.');
3133 x_param_set_tbl_diff := p_param_set_tbl_new;
3134 x_param_set_tbl_all := p_param_set_tbl_new;
3135 RETURN;
3136 END IF;
3137
3138 BIS_UTILITIES_PUB.put_line(p_text =>'p_param_set_tbl_new.COUNT: '||p_param_set_tbl_new.COUNT);
3139 BIS_UTILITIES_PUB.put_line(p_text =>'p_param_set_tbl_orig.COUNT: '||p_param_set_tbl_orig.COUNT);
3140
3141 FOR j IN 1..p_param_set_tbl_new.COUNT LOOP
3142 /*
3143 BIS_UTILITIES_PUB.put_line(p_text =>'compare_param_sets outer loop: '||j);
3144 BIS_UTILITIES_PUB.put_line(p_text =>'pm id: '||p_param_set_tbl_new(j).performance_measure_id );
3145 BIS_UTILITIES_PUB.put_line(p_text =>'tl id: '||p_param_set_tbl_new(j).target_level_id );
3146 BIS_UTILITIES_PUB.put_line(p_text =>'time id: '||p_param_set_tbl_new(j).time_dimension_level_id);
3147 BIS_UTILITIES_PUB.put_line(p_text =>'param 1 id: '||p_param_set_tbl_new(j).parameter1_value );
3148 BIS_UTILITIES_PUB.put_line(p_text =>'param 2 id: '||p_param_set_tbl_new(j).parameter2_value );
3149 BIS_UTILITIES_PUB.put_line(p_text =>'param 3 id: '||p_param_set_tbl_new(j).parameter3_value );
3150 BIS_UTILITIES_PUB.put_line(p_text =>'param 4 id: '||p_param_set_tbl_new(j).parameter4_value );
3151 BIS_UTILITIES_PUB.put_line(p_text =>'param 5 id: '||p_param_set_tbl_new(j).parameter5_value );
3152 BIS_UTILITIES_PUB.put_line(p_text =>'param 6 id: '||p_param_set_tbl_new(j).parameter6_value );
3153 BIS_UTILITIES_PUB.put_line(p_text =>'param 7 id: '||p_param_set_tbl_new(j).parameter7_value );
3154 */
3155
3156 FOR i IN 1..p_param_set_tbl_orig.COUNT LOOP
3157 /*
3158 BIS_UTILITIES_PUB.put_line(p_text =>'compare_param_sets Inner loop: '||i);
3159 BIS_UTILITIES_PUB.put_line(p_text =>'pm id: '||p_param_set_tbl_orig(i).performance_measure_id );
3160 BIS_UTILITIES_PUB.put_line(p_text =>'tl id: '||p_param_set_tbl_orig(i).target_level_id );
3161 BIS_UTILITIES_PUB.put_line(p_text =>'time id: '||p_param_set_tbl_orig(i).time_dimension_level_id);
3162 BIS_UTILITIES_PUB.put_line(p_text =>'param 1 id: '||p_param_set_tbl_orig(i).parameter1_value );
3163 BIS_UTILITIES_PUB.put_line(p_text =>'param 2 id: '||p_param_set_tbl_orig(i).parameter2_value );
3164 BIS_UTILITIES_PUB.put_line(p_text =>'param 3 id: '||p_param_set_tbl_orig(i).parameter3_value );
3165 BIS_UTILITIES_PUB.put_line(p_text =>'param 4 id: '||p_param_set_tbl_orig(i).parameter4_value );
3166 BIS_UTILITIES_PUB.put_line(p_text =>'param 5 id: '||p_param_set_tbl_orig(i).parameter5_value );
3167 BIS_UTILITIES_PUB.put_line(p_text =>'param 6 id: '||p_param_set_tbl_orig(i).parameter6_value );
3168 BIS_UTILITIES_PUB.put_line(p_text =>'param 7 id: '||p_param_set_tbl_orig(i).parameter7_value );
3169 */
3170
3171 IF ((p_param_set_tbl_orig(i).performance_measure_id IS NULL
3172 AND p_param_set_tbl_new(j).performance_measure_id IS NULL)
3173 OR (p_param_set_tbl_orig(i).performance_measure_id
3174 = p_param_set_tbl_new(j).performance_measure_id))
3175 AND ((p_param_set_tbl_orig(i).target_level_id IS NULL
3176 AND p_param_set_tbl_new(j).target_level_id IS NULL)
3177 OR (p_param_set_tbl_orig(i).target_level_id
3178 = p_param_set_tbl_new(j).target_level_id))
3179 AND ((p_param_set_tbl_orig(i).time_dimension_level_id IS NULL
3180 AND p_param_set_tbl_new(j).time_dimension_level_id IS NULL)
3181 OR (p_param_set_tbl_orig(i).time_dimension_level_id
3182 = p_param_set_tbl_new(j).time_dimension_level_id))
3183 AND ((p_param_set_tbl_orig(i).parameter1_value IS NULL
3184 AND p_param_set_tbl_new(j).parameter1_value IS NULL)
3185 OR (p_param_set_tbl_orig(i).parameter1_value
3186 = p_param_set_tbl_new(j).parameter1_value))
3187 AND ((p_param_set_tbl_orig(i).parameter2_value IS NULL
3188 AND p_param_set_tbl_new(j).parameter2_value IS NULL)
3189 OR (p_param_set_tbl_orig(i).parameter2_value
3190 = p_param_set_tbl_new(j).parameter2_value))
3191 AND ((p_param_set_tbl_orig(i).parameter3_value IS NULL
3192 AND p_param_set_tbl_new(j).parameter3_value IS NULL)
3193 OR (p_param_set_tbl_orig(i).parameter3_value
3194 = p_param_set_tbl_new(j).parameter3_value ))
3195 AND ((p_param_set_tbl_orig(i).parameter4_value IS NULL
3196 AND p_param_set_tbl_new(j).parameter4_value IS NULL)
3197 OR (p_param_set_tbl_orig(i).parameter4_value
3198 = p_param_set_tbl_new(j).parameter4_value))
3199 AND ((p_param_set_tbl_orig(i).parameter5_value IS NULL
3200 AND p_param_set_tbl_new(j).parameter5_value IS NULL)
3201 OR (p_param_set_tbl_orig(i).parameter5_value
3202 = p_param_set_tbl_new(j).parameter5_value ))
3203 AND ((p_param_set_tbl_orig(i).parameter6_value IS NULL
3204 AND p_param_set_tbl_new(j).parameter6_value IS NULL)
3205 OR (p_param_set_tbl_orig(i).parameter6_value
3206 = p_param_set_tbl_new(j).parameter6_value ))
3207 AND ((p_param_set_tbl_orig(i).parameter7_value IS NULL
3208 AND p_param_set_tbl_new(j).parameter7_value IS NULL)
3209 OR (p_param_set_tbl_orig(i).parameter7_value
3210 = p_param_set_tbl_new(j).parameter7_value))
3211 THEN
3212 BIS_UTILITIES_PUB.put_line(p_text =>'Original request');
3213 in_orig := TRUE;
3214 exit;
3215 ELSE
3216 l_param_set_rec_new := p_param_set_tbl_new(j);
3217 l_param_set_rec_new.notifiers_code
3218 := p_param_set_tbl_orig(i).notifiers_code;
3219 BIS_UTILITIES_PUB.put_line(p_text =>'New request for target level: '
3220 ||l_param_set_rec_new.target_level_id );
3221 END IF;
3222 END LOOP;
3223
3224 IF NOT in_orig THEN
3225 l_param_set_tbl_diff(l_param_set_tbl_diff.COUNT+1)
3226 := l_param_set_rec_new;
3227
3228 l_param_set_tbl_all(l_param_set_tbl_all.COUNT+1)
3229 := l_param_set_rec_new;
3230 END IF;
3231 in_orig := FALSE;
3232
3233 END LOOP;
3234
3235 BIS_UTILITIES_PUB.put_line(p_text =>'Number of new requests found: '||l_param_set_tbl_diff.COUNT);
3236
3237 x_param_set_tbl_diff := l_param_set_tbl_diff;
3238 x_param_set_tbl_all := l_param_set_tbl_all;
3239
3240 EXCEPTION
3241 WHEN OTHERS THEN
3242 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Compare_param_sets. '||sqlerrm);
3243 x_param_set_tbl_diff := l_param_set_tbl_diff;
3244 x_param_set_tbl_all := l_param_set_tbl_all;
3245
3246 END Compare_param_sets;
3247
3248 PROCEDURE Form_Parameter_Set
3249 ( p_measure_id IN NUMBER
3250 , p_time_level_id IN NUMBER := NULL
3251 , p_target_tbl IN BIS_TARGET_PUB.Target_Tbl_type
3252 , p_Notifiers_Code IN VARCHAR2 := NULL
3253 , x_parameter_set_tbl OUT NOCOPY BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type
3254 )
3255 IS
3256
3257 l_param_set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
3258 l_param_set_tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
3259 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3260 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
3261
3262 BEGIN
3263
3264 FOR i IN 1..p_Target_tbl.COUNT LOOP
3265
3266 l_param_set_Rec.PERFORMANCE_MEASURE_ID := p_measure_id;
3267 l_param_set_Rec.TARGET_LEVEL_ID := p_target_tbl(i).Target_Level_ID;
3268 IF p_time_level_id IS NOT NULL THEN
3269 l_param_set_Rec.TIME_DIMENSION_LEVEL_ID := p_time_level_id;
3270 END IF;
3271 l_param_set_Rec.PLAN_ID := p_target_tbl(i).Plan_ID;
3272 l_param_set_Rec.PARAMETER1_VALUE :=p_target_tbl(i).Dim1_Level_Value_ID;
3273 l_param_set_Rec.PARAMETER2_VALUE :=p_target_tbl(i).Dim2_Level_Value_ID;
3274 l_param_set_Rec.PARAMETER3_VALUE :=p_target_tbl(i).Dim3_Level_Value_ID;
3275 l_param_set_Rec.PARAMETER4_VALUE :=p_target_tbl(i).Dim4_Level_Value_ID;
3276 l_param_set_Rec.PARAMETER5_VALUE :=p_target_tbl(i).Dim5_Level_Value_ID;
3277 l_param_set_Rec.PARAMETER6_VALUE :=p_target_tbl(i).Dim6_Level_Value_ID;
3278 l_param_set_Rec.PARAMETER7_VALUE :=p_target_tbl(i).Dim7_Level_Value_ID;
3279 IF p_notifiers_code IS NOT NULL THEN
3280 l_param_set_rec.NOTIFIERS_CODE := p_notifiers_code;
3281 END IF;
3282 l_param_set_tbl(l_param_set_tbl.COUNT+1) := l_param_set_rec;
3283
3284 END LOOP;
3285 x_parameter_set_tbl := l_param_set_tbl;
3286
3287 EXCEPTION
3288 when FND_API.G_EXC_ERROR then
3289 BIS_UTILITIES_PUB.put_line(p_text =>'form_parameter_set exception1: '||sqlerrm);
3290 when FND_API.G_EXC_UNEXPECTED_ERROR then
3291 BIS_UTILITIES_PUB.put_line(p_text =>'form_parameter_set exception2: '||sqlerrm);
3292 when others then
3293 BIS_UTILITIES_PUB.put_line(p_text =>'form_parameter_set exception3: '||sqlerrm);
3294 l_error_tbl_p := l_error_tbl;
3295 BIS_UTILITIES_PVT.Add_Error_Message
3296 ( p_error_msg_id => SQLCODE
3297 , p_error_description => SQLERRM
3298 , p_error_proc_name => 'form_parameter_set'
3299 , p_error_table => l_error_tbl_p
3300 , x_error_table => l_error_tbl
3301 );
3302
3303 END Form_Parameter_Set;
3304
3305 PROCEDURE Verify_Target_Level
3306 ( p_Target_Level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3307 , p_parameter1_level IN varchar2 default null
3308 , p_parameter2_level IN varchar2 default null
3309 , p_parameter3_level IN varchar2 default null
3310 , p_parameter4_level IN varchar2 default null
3311 , p_parameter5_level IN varchar2 default null
3312 , p_parameter6_level IN varchar2 default null
3313 , p_parameter7_level IN varchar2 default null
3314 , p_viewby_level_id IN varchar2 default null
3315 , x_Target_Level_rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
3316 )
3317 IS
3318
3319 l_Target_Level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
3320 sameViewBy BOOLEAN;
3321 viewByLevelNum NUMBER;
3322 l_viewBy_level_id NUMBER;
3323 l_return_status VARCHAR2(32000);
3324 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3325 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
3326 l_Target_Level_rec_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
3327
3328 BEGIN
3329
3330 -- if no viewBy, check if target level's
3331 -- dimension levels = param1-7 level
3332 --
3333 -- if viewBy, find the param Level that is in the same dimension as
3334 -- the viewBy level, then check the target levels that have
3335 -- dimension levels = param1-6 + viewBy level
3336 --
3337 /*
3338 BIS_UTILITIES_PUB.put_line(p_text =>'In verify target level. Target level: '
3339 ||p_Target_Level_rec.Target_level_id
3340 ||', Viewby: '||p_viewby_level_id);
3341 */
3342 IF p_viewby_level_id IS NULL THEN
3343 IF has_Dimension_Levels
3344 ( p_Target_Level_rec => p_Target_Level_rec
3345 , p_parameter1_level => p_parameter1_level
3346 , p_parameter2_level => p_parameter2_level
3347 , p_parameter3_level => p_parameter3_level
3348 , p_parameter4_level => p_parameter4_level
3349 , p_parameter5_level => p_parameter5_level
3350 , p_parameter6_level => p_parameter6_level
3351 , p_parameter7_level => p_parameter7_level
3352 , p_viewby_level_id => p_viewby_level_id
3353 )
3354 THEN
3355 l_Target_Level_rec := p_Target_Level_rec;
3356 x_Target_Level_rec := l_Target_Level_rec;
3357
3358 BIS_UTILITIES_PUB.put_line(p_text =>'In verify target level. Target level has dim levels, no viewby: '
3359 ||l_Target_Level_rec.Target_level_id);
3360 ELSE
3361 RETURN;
3362 END IF;
3363 ELSE
3364 -- if viewBy in param Level 1-7, same as above
3365 --
3366 Check_View_By
3367 ( p_parameter1_level => p_parameter1_level
3368 , p_parameter2_level => p_parameter2_level
3369 , p_parameter3_level => p_parameter3_level
3370 , p_parameter4_level => p_parameter4_level
3371 , p_parameter5_level => p_parameter5_level
3372 , p_parameter6_level => p_parameter6_level
3373 , p_parameter7_level => p_parameter7_level
3374 , p_viewby_level_id => p_viewby_level_id
3375 , sameViewBy => sameViewBy
3376 , viewByLevelNum => viewByLevelNum
3377 );
3378 --BIS_UTILITIES_PUB.put_line(p_text =>'view by level num: '||viewByLevelNum);
3379
3380 IF sameViewBy THEN
3381 --BIS_UTILITIES_PUB.put_line(p_text =>' same view by!');
3382 IF has_Dimension_Levels
3383 ( p_Target_Level_rec => p_Target_Level_rec
3384 , p_parameter1_level => p_parameter1_level
3385 , p_parameter2_level => p_parameter2_level
3386 , p_parameter3_level => p_parameter3_level
3387 , p_parameter4_level => p_parameter4_level
3388 , p_parameter5_level => p_parameter5_level
3389 , p_parameter6_level => p_parameter6_level
3390 , p_parameter7_level => p_parameter7_level
3391 , p_viewby_level_id => p_viewby_level_id
3392 )
3393 THEN
3394 l_Target_Level_rec := p_Target_Level_rec;
3395 x_Target_Level_rec := l_Target_Level_rec;
3396 BIS_UTILITIES_PUB.put_line(p_text =>'In verify target level. DO have dimension levels: '
3397 ||l_Target_Level_rec.Target_level_id);
3398 ELSE
3399 --BIS_UTILITIES_PUB.put_line(p_text =>'In verify target level. Do NOT have dimension levels: '
3400 --||p_Target_Level_rec.Target_level_id);
3401 RETURN;
3402 END IF;
3403 ELSE
3404 BIS_UTILITIES_PUB.put_line(p_text =>' Different view by!');
3405 IF p_parameter1_level IS NOT NULL THEN
3406 l_Target_Level_rec.Dimension1_Level_Id:=TO_NUMBER(p_parameter1_level);
3407 END IF;
3408 IF p_parameter2_level IS NOT NULL THEN
3409 l_Target_Level_rec.Dimension2_Level_Id:=TO_NUMBER(p_parameter2_level);
3410 END IF;
3411 IF p_parameter3_level IS NOT NULL THEN
3412 l_Target_Level_rec.Dimension3_Level_Id:=TO_NUMBER(p_parameter3_level);
3413 END IF;
3414 IF p_parameter4_level IS NOT NULL THEN
3415 l_Target_Level_rec.Dimension4_Level_Id:=TO_NUMBER(p_parameter4_level);
3416 END IF;
3417 IF p_parameter5_level IS NOT NULL THEN
3418 l_Target_Level_rec.Dimension5_Level_Id:=TO_NUMBER(p_parameter5_level);
3419 END IF;
3420 IF p_parameter6_level IS NOT NULL THEN
3421 l_Target_Level_rec.Dimension6_Level_Id:=TO_NUMBER(p_parameter6_level);
3422 END IF;
3423 IF p_parameter7_level IS NOT NULL THEN
3424 l_Target_Level_rec.Dimension7_Level_Id:=TO_NUMBER(p_parameter7_level);
3425 END IF;
3426 IF p_viewby_level_id IS NOT NULL THEN
3427 l_viewby_level_id := TO_NUMBER(p_viewby_level_id);
3428 END IF;
3429
3430 IF viewByLevelNum = 1 THEN
3431 l_Target_Level_rec.Dimension1_Level_Id := l_viewby_level_id;
3432 l_Target_Level_rec_p := l_Target_Level_rec;
3433 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3434 ( p_api_version => 1.0
3435 , p_all_info => FND_API.G_FALSE
3436 , p_Target_Level_rec => l_Target_Level_rec_p
3437 , x_Target_Level_rec => l_Target_Level_rec
3438 , x_return_status => l_return_status
3439 , x_error_Tbl => l_error_Tbl
3440 );
3441 ELSIF viewByLevelNum = 2 THEN
3442 l_Target_Level_rec.Dimension2_Level_Id := l_viewby_level_id;
3443 l_Target_Level_rec_p := l_Target_Level_rec;
3444 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3445 ( p_api_version => 1.0
3446 , p_all_info => FND_API.G_FALSE
3447 , p_Target_Level_rec => l_Target_Level_rec_p
3448 , x_Target_Level_rec => l_Target_Level_rec
3449 , x_return_status => l_return_status
3450 , x_error_Tbl => l_error_Tbl
3451 );
3452 ELSIF viewByLevelNum = 3 THEN
3453 l_Target_Level_rec.Dimension3_Level_Id := l_viewby_level_id;
3454 l_Target_Level_rec_p := l_Target_Level_rec;
3455 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3456 ( p_api_version => 1.0
3457 , p_all_info => FND_API.G_FALSE
3458 , p_Target_Level_rec => l_Target_Level_rec_p
3459 , x_Target_Level_rec => l_Target_Level_rec
3460 , x_return_status => l_return_status
3461 , x_error_Tbl => l_error_Tbl
3462 );
3463 ELSIF viewByLevelNum = 4 THEN
3464 l_Target_Level_rec.Dimension4_Level_Id := l_viewby_level_id;
3465 l_Target_Level_rec_p := l_Target_Level_rec;
3466 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3467 ( p_api_version => 1.0
3468 , p_all_info => FND_API.G_FALSE
3469 , p_Target_Level_rec => l_Target_Level_rec_p
3470 , x_Target_Level_rec => l_Target_Level_rec
3471 , x_return_status => l_return_status
3472 , x_error_Tbl => l_error_Tbl
3473 );
3474 ELSIF viewByLevelNum = 5 THEN
3475 l_Target_Level_rec.Dimension5_Level_Id := l_viewby_level_id;
3476 l_Target_Level_rec_p := l_Target_Level_rec;
3477 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3478 ( p_api_version => 1.0
3479 , p_all_info => FND_API.G_FALSE
3480 , p_Target_Level_rec => l_Target_Level_rec_p
3481 , x_Target_Level_rec => l_Target_Level_rec
3482 , x_return_status => l_return_status
3483 , x_error_Tbl => l_error_Tbl
3484 );
3485 ELSIF viewByLevelNum = 6 THEN
3486 l_Target_Level_rec.Dimension6_Level_Id := l_viewby_level_id;
3487 l_Target_Level_rec_p := l_Target_Level_rec;
3488 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3489 ( p_api_version => 1.0
3490 , p_all_info => FND_API.G_FALSE
3491 , p_Target_Level_rec => l_Target_Level_rec_p
3492 , x_Target_Level_rec => l_Target_Level_rec
3493 , x_return_status => l_return_status
3494 , x_error_Tbl => l_error_Tbl
3495 );
3496 ELSIF viewByLevelNum = 7 THEN
3497 l_Target_Level_rec.Dimension7_Level_Id := l_viewby_level_id;
3498 l_Target_Level_rec_p := l_Target_Level_rec;
3499 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
3500 ( p_api_version => 1.0
3501 , p_all_info => FND_API.G_FALSE
3502 , p_Target_Level_rec => l_Target_Level_rec_p
3503 , x_Target_Level_rec => l_Target_Level_rec
3504 , x_return_status => l_return_status
3505 , x_error_Tbl => l_error_Tbl
3506 );
3507 ELSE
3508 RETURN;
3509 END IF;
3510 END IF;
3511 END IF;
3512
3513 x_Target_Level_rec := l_Target_Level_rec;
3514
3515 EXCEPTION
3516 when FND_API.G_EXC_ERROR then
3517 BIS_UTILITIES_PUB.put_line(p_text =>'Verify_Target_Level exception1: '||sqlerrm);
3518 when FND_API.G_EXC_UNEXPECTED_ERROR then
3519 BIS_UTILITIES_PUB.put_line(p_text =>'Verify_Target_Level exception2: '||sqlerrm);
3520 when others then
3521 BIS_UTILITIES_PUB.put_line(p_text =>'Verify_Target_Level exception3: '||sqlerrm);
3522 l_error_Tbl_p := l_error_Tbl;
3523 BIS_UTILITIES_PVT.Add_Error_Message
3524 ( p_error_msg_id => SQLCODE
3525 , p_error_description => SQLERRM
3526 , p_error_proc_name => 'Verify_Target_Level'
3527 , p_error_table => l_error_tbl_p
3528 , x_error_table => l_error_tbl
3529 );
3530
3531 END Verify_Target_Level;
3532
3533 PROCEDURE Verify_Target
3534 ( p_Target_rec IN BIS_Target_PUB.Target_Rec_Type
3535 , p_parameter1_value IN varchar2 default null
3536 , p_parameter2_value IN varchar2 default null
3537 , p_parameter3_value IN varchar2 default null
3538 , p_parameter4_value IN varchar2 default null
3539 , p_parameter5_value IN varchar2 default null
3540 , p_parameter6_value IN varchar2 default null
3541 , p_parameter7_value IN varchar2 default null
3542 , x_Target_rec OUT NOCOPY BIS_Target_PUB.Target_Rec_Type
3543 )
3544 IS
3545
3546 l_Target_rec BIS_Target_PUB.Target_Rec_Type;
3547
3548 BEGIN
3549
3550 IF has_Dimension_Level_Values
3551 ( p_Target_rec => p_Target_rec
3552 , p_parameter1_value => p_parameter1_value
3553 , p_parameter2_value => p_parameter2_value
3554 , p_parameter3_value => p_parameter3_value
3555 , p_parameter4_value => p_parameter4_value
3556 , p_parameter5_value => p_parameter5_value
3557 , p_parameter6_value => p_parameter6_value
3558 , p_parameter7_value => p_parameter7_value
3559 )
3560 THEN
3561 BIS_UTILITIES_PUB.put_line(p_text =>'has Dimension_Level_Values');
3562 l_Target_rec := p_Target_rec;
3563 END IF;
3564
3565 x_Target_rec := l_Target_rec;
3566
3567 EXCEPTION
3568 WHEN OTHERS THEN
3569 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Verify_Target: '||sqlerrm);
3570 x_Target_rec := NULL;
3571
3572 END Verify_Target;
3573
3574
3575 FUNCTION has_Dimension_Levels
3576 ( p_Target_Level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3577 , p_parameter1_level IN varchar2 default null
3578 , p_parameter2_level IN varchar2 default null
3579 , p_parameter3_level IN varchar2 default null
3580 , p_parameter4_level IN varchar2 default null
3581 , p_parameter5_level IN varchar2 default null
3582 , p_parameter6_level IN varchar2 default null
3583 , p_parameter7_level IN varchar2 default null
3584 , p_viewby_level_id IN varchar2 default null
3585 )
3586 RETURN BOOLEAN
3587 IS
3588
3589 BEGIN
3590 /*
3591 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Tdim1: '||p_Target_Level_rec.Dimension1_Level_id);
3592 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Pdim1: '||p_parameter1_level);
3593
3594 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Tdim2: '||p_Target_Level_rec.Dimension2_Level_id);
3595 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Pdim2: '||p_parameter2_level);
3596
3597 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Tdim3: '||p_Target_Level_rec.Dimension3_Level_id);
3598 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM LEV. Pdim3: '||p_parameter3_level);
3599 */
3600
3601 IF ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension1_Level_id)
3602 = FND_API.G_TRUE
3603 AND p_parameter1_level IS NULL)
3604 OR p_Target_Level_rec.Dimension1_Level_Id = TO_NUMBER(p_parameter1_level))
3605 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension2_Level_id)
3606 = FND_API.G_TRUE
3607 AND p_parameter2_level IS NULL)
3608 OR p_Target_Level_rec.Dimension2_Level_Id = TO_NUMBER(p_parameter2_level))
3609 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension3_Level_id)
3610 = FND_API.G_TRUE
3611 AND p_parameter3_level IS NULL)
3612 OR p_Target_Level_rec.Dimension3_Level_Id = TO_NUMBER(p_parameter3_level))
3613 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension4_Level_id)
3614 = FND_API.G_TRUE
3615 AND p_parameter4_level IS NULL)
3616 OR p_Target_Level_rec.Dimension4_Level_Id = TO_NUMBER(p_parameter4_level))
3617 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension5_Level_id)
3618 = FND_API.G_TRUE
3619 AND p_parameter5_level IS NULL)
3620 OR p_Target_Level_rec.Dimension5_Level_Id = TO_NUMBER(p_parameter5_level))
3621 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension6_Level_id)
3622 = FND_API.G_TRUE
3623 AND p_parameter6_level IS NULL)
3624 OR p_Target_Level_rec.Dimension6_Level_Id = TO_NUMBER(p_parameter6_level))
3625 AND ((BIS_UTILITIES_PUB.Value_Null(p_Target_Level_rec.Dimension7_Level_id)
3626 = FND_API.G_TRUE
3627 AND p_parameter7_level IS NULL)
3628 OR p_Target_Level_rec.Dimension7_Level_Id = TO_NUMBER(p_parameter7_level))
3629 THEN
3630 RETURN TRUE;
3631 ELSE
3632 RETURN FALSE;
3633 END IF;
3634
3635 EXCEPTION
3636 WHEN OTHERS THEN
3637 BIS_UTILITIES_PUB.put_line(p_text =>'Exception at has_Dimension_Levels: '||sqlerrm);
3638 RETURN FALSE;
3639
3640 END has_Dimension_Levels;
3641
3642 PROCEDURE check_View_by
3643 ( p_parameter1_level IN varchar2 default null
3644 , p_parameter2_level IN varchar2 default null
3645 , p_parameter3_level IN varchar2 default null
3646 , p_parameter4_level IN varchar2 default null
3647 , p_parameter5_level IN varchar2 default null
3648 , p_parameter6_level IN varchar2 default null
3649 , p_parameter7_level IN varchar2 default null
3650 , p_viewby_level_id IN varchar2 default null
3651 , sameViewBy OUT NOCOPY BOOLEAN
3652 , viewByLevelNum OUT NOCOPY NUMBER
3653 )
3654 IS
3655
3656 l_sameViewBy BOOLEAN := FALSE;
3657 l_viewByLevelNum NUMBER := -1;
3658
3659 BEGIN
3660
3661 IF (p_viewby_level_id = p_parameter1_level) THEN
3662 l_sameViewBy := TRUE;
3663 l_viewByLevelNum := 1;
3664 ELSIF (p_viewby_level_id = p_parameter2_level) THEN
3665 l_sameViewBy := TRUE;
3666 l_viewByLevelNum := 2;
3667 ELSIF (p_viewby_level_id = p_parameter3_level) THEN
3668 l_sameViewBy := TRUE;
3669 l_viewByLevelNum := 3;
3670 ELSIF (p_viewby_level_id = p_parameter4_level) THEN
3671 l_sameViewBy := TRUE;
3672 l_viewByLevelNum := 4;
3673 ELSIF (p_viewby_level_id = p_parameter5_level) THEN
3674 l_sameViewBy := TRUE;
3675 l_viewByLevelNum := 5;
3676 ELSIF (p_viewby_level_id = p_parameter6_level) THEN
3677 l_sameViewBy := TRUE;
3678 l_viewByLevelNum := 6;
3679 ELSIF (p_viewby_level_id = p_parameter7_level) THEN
3680 l_sameViewBy := TRUE;
3681 l_viewByLevelNum := 7;
3682 END IF;
3683
3684 sameViewBy := l_sameViewBy;
3685 viewByLevelNum := l_viewByLevelNum;
3686
3687 EXCEPTION
3688 WHEN OTHERS THEN
3689 sameViewBy := FALSE;
3690 viewByLevelNum := NULL;
3691
3692 END check_View_by;
3693
3694 FUNCTION has_Dimension_Level_Values
3695 ( p_Target_rec IN BIS_Target_PUB.Target_Rec_Type
3696 , p_parameter1_value IN varchar2 default null
3697 , p_parameter2_value IN varchar2 default null
3698 , p_parameter3_value IN varchar2 default null
3699 , p_parameter4_value IN varchar2 default null
3700 , p_parameter5_value IN varchar2 default null
3701 , p_parameter6_value IN varchar2 default null
3702 , p_parameter7_value IN varchar2 default null
3703 )
3704 RETURN BOOLEAN
3705 IS
3706
3707 BEGIN
3708
3709 /*
3710 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Tdim1: '||p_Target_rec.Dim1_level_value_id);
3711 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Pdim1: '||p_parameter1_value);
3712
3713 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Tdim2: '||p_Target_rec.Dim2_level_value_id);
3714 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Pdim2: '||p_parameter2_value);
3715
3716 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Tdim3: '||p_Target_rec.Dim3_level_value_id);
3717 BIS_UTILITIES_PUB.put_line(p_text =>'in verify DIM VAL. Pdim3: '||p_parameter3_value);
3718 */
3719
3720 IF ((p_parameter1_value IS NULL)
3721 OR p_Target_rec.Dim1_Level_Value_Id = p_parameter1_value)
3722 AND ((p_parameter2_value IS NULL)
3723 OR p_Target_rec.Dim2_Level_Value_Id = p_parameter2_value)
3724 AND ((p_parameter3_value IS NULL)
3725 OR p_Target_rec.Dim3_Level_Value_Id = p_parameter3_value)
3726 AND ((p_parameter4_value IS NULL)
3727 OR p_Target_rec.Dim4_Level_Value_Id = p_parameter4_value)
3728 AND ((p_parameter5_value IS NULL)
3729 OR p_Target_rec.Dim5_Level_Value_Id = p_parameter5_value)
3730 AND ((p_parameter6_value IS NULL)
3731 OR p_Target_rec.Dim6_Level_Value_Id = p_parameter6_value)
3732 AND ((p_parameter7_value IS NULL)
3733 OR p_Target_rec.Dim7_Level_Value_Id = p_parameter7_value)
3734 THEN
3735 RETURN TRUE;
3736 ELSE
3737 RETURN FALSE;
3738 END IF;
3739
3740 EXCEPTION
3741 WHEN OTHERS THEN
3742 BIS_UTILITIES_PUB.put_line(p_text =>'Exception at has_Dimension_Level_Values: '||sqlerrm);
3743 RETURN FALSE;
3744
3745 END has_Dimension_Level_Values;
3746
3747 FUNCTION is_time_level
3748 (p_Dimension_Level_rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_rec_Type)
3749 RETURN BOOLEAN
3750
3751 IS
3752 l_Dimension_Level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_rec_Type;
3753 l_return_status VARCHAR2(32000);
3754 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3755
3756 BEGIN
3757
3758 BIS_DIMENSION_LEVEL_PVT.Retrieve_Dimension_Level
3759 ( p_api_version => 1.0
3760 , p_Dimension_Level_Rec => p_dimension_Level_Rec
3761 , x_Dimension_Level_Rec => l_dimension_Level_Rec
3762 , x_return_status => l_return_status
3763 , x_error_Tbl => l_error_Tbl
3764 );
3765
3766 IF l_dimension_Level_Rec.dimension_short_name = BIS_UTILITIES_PVT.Get_Total_Dimlevel_Name
3767 (p_dim_short_name => BIS_UTILITIES_PVT.Get_Time_Dimension_Name
3768 (p_DimLevelId => l_dimension_level_rec.Dimension_Level_Id)) THEN
3769 RETURN TRUE;
3770 ELSE
3771 RETURN FALSE;
3772 END IF;
3773
3774 EXCEPTION
3775 WHEN OTHERS THEN
3776 RETURN FALSE;
3777
3778 END is_time_level;
3779
3780 FUNCTION IS_TOTAL_DIM_LEVEL
3781 (p_DimLevelId IN NUMBER := NULL
3782 )
3783 RETURN BOOLEAN
3784 IS
3785
3786
3787 CURSOR c_dim_id IS
3788 SELECT source, short_name,DIMENSION_ID
3789 FROM bis_levels
3790 WHERE level_id = p_DimLevelId;
3791
3792 CURSOR c_dim (p_dim_level_id in NUMBER) IS
3793 SELECT short_name
3794 FROM bis_dimensions
3795 WHERE dimension_id = p_dim_level_id;
3796
3797 l_total_name VARCHAR2(2000);
3798 l_source VARCHAR2(2000);
3799 l_length NUMBER;
3800 l_dimension_id NUMBER;
3801 l_level_short_name VARCHAR2(240);
3802 l_dim_short_name VARCHAR2(240);
3803 l_return BOOLEAN;
3804
3805 BEGIN
3806
3807 OPEN c_dim_id;
3808 FETCH c_dim_id INTO l_Source,l_level_short_name, l_dimension_id;
3809 CLOSE c_dim_id;
3810
3811 OPEN c_dim (l_dimension_id);
3812 FETCH c_dim INTO l_dim_short_name;
3813 CLOSE c_dim;
3814
3815 l_length := length(l_dim_short_name);
3816
3817 IF (l_source = 'EDW')
3818 THEN
3819 l_total_name := substr(l_dim_short_name,1,(l_length-1) );
3820 l_total_name := l_total_name || 'A';
3821 END IF;
3822 IF (l_source = 'OLTP')
3823 THEN
3824 l_total_name := 'TOTAL_'||l_dim_short_name;
3825 END IF;
3826
3827 if l_total_name = l_level_short_name then
3828 l_return := TRUE;
3829 BIS_UTILITIES_PUB.put_line(p_text =>'Is Total time');
3830 else
3831 l_return := FALSE;
3832 BIS_UTILITIES_PUB.put_line(p_text =>'Is Not Total time');
3833 end if;
3834 RETURN l_return;
3835
3836 Exception
3837 when others then
3838 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure IS_TOTAL_DIM_LEVEL : '||sqlerrm);
3839
3840 END IS_TOTAL_DIM_LEVEL;
3841
3842
3843 end BIS_PMF_REG_SERVICE_PVT;