[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_OBJECT_RULES_PVT
Source
1 PACKAGE BODY PA_PERF_OBJECT_RULES_PVT AS
2 /* $Header: PAPEORVB.pls 120.1 2005/08/19 16:39:14 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_perf_object_rules_pvt';
5
6 /*==================================================================
7 PROCEDURE
8 create_rule_object
9 PURPOSE
10 This procedure inserts a row into the pa_perf_object_rules table.
11 ==================================================================*/
12
13
14 PROCEDURE create_rule_object(
15 P_OBJECT_RULE_ID IN NUMBER,
16 P_OBJECT_TYPE IN VARCHAR2,
17 P_OBJECT_ID IN NUMBER,
18 P_RULE_ID IN NUMBER,
19 P_RECORD_VERSION_NUMBER IN NUMBER,
20 P_CREATION_DATE IN DATE,
21 P_CREATED_BY IN NUMBER,
22 P_LAST_UPDATE_DATE IN DATE,
23 P_LAST_UPDATED_BY IN NUMBER,
24 P_LAST_UPDATE_LOGIN IN NUMBER,
25 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
26 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
27 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
28 IS
29
30 l_msg_count NUMBER := 0;
31 l_data VARCHAR2(2000);
32 l_msg_data VARCHAR2(2000);
33 l_msg_index_out NUMBER;
34 l_debug_mode VARCHAR2(1);
35 l_rowid VARCHAR2(255);
36 l_debug_level2 CONSTANT NUMBER := 2;
37 l_debug_level3 CONSTANT NUMBER := 3;
38 l_debug_level4 CONSTANT NUMBER := 4;
39 l_debug_level5 CONSTANT NUMBER := 5;
40
41 BEGIN
42
43 -- Initialize the Error Stack
44 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PVT.create_rule_object');
45 x_msg_count := 0;
46 x_msg_data := NULL;
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
49
50 IF l_debug_mode = 'Y' THEN
51 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PVT.create_rule_object',
52 p_debug_mode => l_debug_mode );
53 END IF;
54
55 IF l_debug_mode = 'Y' THEN
56 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PVT.create_rule_object';
57 pa_debug.write(g_module_name,pa_debug.g_err_stage,
58 l_debug_level3);
59 END IF;
60
61 IF l_debug_mode = 'Y' THEN
62 pa_debug.g_err_stage:= 'Going to check if the rule has already been associated to this project';
63 pa_debug.write(g_module_name,pa_debug.g_err_stage,
64 l_debug_level3);
65 END IF;
66
67 DECLARE
68 dummy number;
69 BEGIN
70 SELECT object_rule_id
71 INTO dummy
72 FROM pa_perf_object_rules
73 WHERE object_id = P_OBJECT_ID
74 AND rule_id = P_RULE_ID;
75
76 RETURN;
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 NULL;
80 END;
81 -- See if there is a record with the same measure format and period type
82
83 -- See if there is a record with the same measure but different period type
84 /*
85 DECLARE
86 dummy number ;
87 BEGIN
88 SELECT 1
89 INTO dummy
90 FROM PA_PERF_RULES_V perfrule
91 WHERE perfrule.rule_id = P_RULE_ID
92 AND (perfrule.measure_name,perfrule.period_type) in (
93 SELECT objrule.measure_name,objrule.period_type
94 FROM PA_PERF_OBJECT_RULES_V objrule
95 WHERE objrule.object_id = P_OBJECT_ID ) ;
96 PA_UTILS.ADD_MESSAGE
97 ( p_app_short_name => 'PA',
98 p_msg_name => 'PA_EXCP_PROJ_DUP_MEASURE_CAL');
99 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
100 x_msg_count := 1;
101 return;
102 EXCEPTION
103 WHEN OTHERS THEN
104 NULL;
105 END;
106
107 DECLARE
108 dummy number ;
109 BEGIN
110 SELECT 1
111 INTO dummy
112 FROM PA_PERF_RULES_v perfrule
113 WHERE perfrule.rule_id = P_RULE_ID
114 AND perfrule.measure_name in (
115 SELECT objrule.measure_name
116 FROM PA_PERF_OBJECT_RULES_V objrule
117 WHERE objrule.object_id = P_OBJECT_ID ) ;
118 PA_UTILS.ADD_MESSAGE
119 ( p_app_short_name => 'PA',
120 p_msg_name => 'PA_EXCP_PROJ_RULE_DUP_MEASURE');
121
122 x_msg_count := 1;
123 EXCEPTION
124 WHEN OTHERS THEN
125 NULL;
126 END;
127 */
128
129 PA_PERF_OBJECT_RULES_PKG.insert_row(
130 X_ROWID => l_rowid,
131 X_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
132 X_OBJECT_TYPE => P_OBJECT_TYPE,
133 X_OBJECT_ID => P_OBJECT_ID,
134 X_RULE_ID => P_RULE_ID,
135 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
136 X_CREATION_DATE => P_CREATION_DATE,
137 X_CREATED_BY => P_CREATED_BY,
138 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
139 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
140 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
141 );
142
143 -- Check for business rules violations
144
145 IF l_debug_mode = 'Y' THEN
146 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PVT.create_rule_object';
147 pa_debug.write(g_module_name,pa_debug.g_err_stage,
148 l_debug_level3);
149 pa_debug.reset_curr_function;
150 END IF;
151
152 -- Reset the Error Stack
153 PA_DEBUG.reset_err_stack;
154
155
156 EXCEPTION
157 WHEN others THEN
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 x_msg_count := 1;
160 x_msg_data := SQLERRM;
161
162 FND_MSG_PUB.add_exc_msg(
163 p_pkg_name => 'PA_PERF_OBJECT_RULES_PVT'
164 ,p_procedure_name => 'CREATE_RULE_OBJECT'
165 ,p_error_text => x_msg_data);
166
167 IF l_debug_mode = 'Y' THEN
168 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
169 pa_debug.write(g_module_name,pa_debug.g_err_stage,
170 l_debug_level5);
171 pa_debug.reset_curr_function;
172 END IF;
173 RAISE;
174 END create_rule_object;
175
176 /*==================================================================
177 PROCEDURE
178 update_rule_object
179 PURPOSE
180 This procedure updates a row in the pa_perf_object_rules table.
181 ==================================================================*/
182
183 PROCEDURE update_rule_object(
184 P_OBJECT_RULE_ID IN NUMBER,
185 P_OBJECT_TYPE IN VARCHAR2,
186 P_OBJECT_ID IN NUMBER,
187 P_RULE_ID IN NUMBER,
188 P_RECORD_VERSION_NUMBER IN NUMBER,
189 P_CREATION_DATE IN DATE,
190 P_CREATED_BY IN NUMBER,
191 P_LAST_UPDATE_DATE IN DATE,
192 P_LAST_UPDATED_BY IN NUMBER,
193 P_LAST_UPDATE_LOGIN IN NUMBER,
194 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
195 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
196 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
197 IS
198 Invalid_Ret_Status EXCEPTION;
199 l_msg_count NUMBER := 0;
200 l_data VARCHAR2(2000);
201 l_msg_data VARCHAR2(2000);
202 l_msg_index_out NUMBER;
203 l_debug_mode VARCHAR2(1);
204 l_rowid VARCHAR2(255);
205 l_debug_level2 CONSTANT NUMBER := 2;
206 l_debug_level3 CONSTANT NUMBER := 3;
207 l_debug_level4 CONSTANT NUMBER := 4;
208 l_debug_level5 CONSTANT NUMBER := 5;
209
210 BEGIN
211
212 savepoint sp;
213 -- Initialize the Error Stack
214 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PVT.update_rule_object');
215 x_msg_count := 0;
216 x_msg_data := NULL;
217
218 -- Initialize the return status to success
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
221 IF l_debug_mode = 'Y' THEN
222 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PVT.update_rule_object',
223 p_debug_mode => l_debug_mode );
224 END IF;
225
226 IF l_debug_mode = 'Y' THEN
227 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PVT.update_rule_object';
228 pa_debug.write(g_module_name,pa_debug.g_err_stage,
229 l_debug_level3);
230
231 pa_debug.g_err_stage:= 'P_OBJECT_RULE_ID = '|| P_OBJECT_RULE_ID;
232 pa_debug.write(g_module_name,pa_debug.g_err_stage,
233 l_debug_level5);
234
235 pa_debug.g_err_stage:= 'P_OBJECT_TYPE = '|| P_OBJECT_TYPE;
236 pa_debug.write(g_module_name,pa_debug.g_err_stage,
237 l_debug_level5);
238 pa_debug.g_err_stage:= 'P_OBJECT_ID = '|| P_OBJECT_ID ;
239 pa_debug.write(g_module_name,pa_debug.g_err_stage,
240 l_debug_level5);
241 pa_debug.g_err_stage:= 'P_RULE_ID = '|| P_RULE_ID ;
242 pa_debug.write(g_module_name,pa_debug.g_err_stage,
243 l_debug_level5);
244 END IF;
245
246 IF l_debug_mode = 'Y' THEN
247 pa_debug.g_err_stage:= 'about to call lock row method';
248 pa_debug.write(g_module_name,pa_debug.g_err_stage,
249 l_debug_level3);
250 END IF;
251
252 PA_PERF_OBJECT_RULES_PKG.LOCK_ROW
253 (X_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
254 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER ) ;
255
256 IF l_debug_mode = 'Y' THEN
257 pa_debug.g_err_stage:= 'lock row method called';
258 pa_debug.write(g_module_name,pa_debug.g_err_stage,
259 l_debug_level3);
260 END IF;
261
262 l_msg_count := FND_MSG_PUB.count_msg;
263
264 if(l_msg_count<>0) then
265 Raise Invalid_Ret_Status;
266 end if;
267
268 IF l_debug_mode = 'Y' THEN
269 pa_debug.g_err_stage:= 'about to call PA_PERF_RULES_PKG.update_rule_object';
270 pa_debug.write(g_module_name,pa_debug.g_err_stage,
271 l_debug_level3);
272 END IF;
273
274 PA_PERF_OBJECT_RULES_PKG.UPDATE_ROW(
275 X_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
276 X_OBJECT_TYPE => P_OBJECT_TYPE,
277 X_OBJECT_ID => P_OBJECT_ID,
278 X_RULE_ID => P_RULE_ID,
279 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
280 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
281 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
282 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN );
283
284 IF l_debug_mode = 'Y' THEN
285 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PVT.update_rule_object';
286 pa_debug.write(g_module_name,pa_debug.g_err_stage,
287 l_debug_level3);
288 pa_debug.reset_curr_function;
289 END IF;
290
291 -- Reset the Error Stack
292 PA_DEBUG.reset_err_stack;
293
294 EXCEPTION
295 WHEN Invalid_Ret_Status THEN
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 l_msg_count := FND_MSG_PUB.count_msg;
298
299 IF l_msg_count = 1 and x_msg_data IS NULL THEN
300 PA_INTERFACE_UTILS_PUB.get_messages(
301 p_encoded => FND_API.G_TRUE
302 ,p_msg_index => 1
303 ,p_msg_count => l_msg_count
304 ,p_msg_data => l_msg_data
305 ,p_data => l_data
306 ,p_msg_index_out => l_msg_index_out);
307
311 x_msg_count := l_msg_count;
308 x_msg_data := l_data;
309 x_msg_count := l_msg_count;
310 ELSE
312 END IF;
313
314 IF l_debug_mode = 'Y' THEN
315 pa_debug.reset_curr_function;
316 END IF;
317
318 rollback to sp;
319 RETURN;
320
321 WHEN OTHERS THEN
322 x_msg_count := 1;
323 x_msg_data := substr(SQLERRM,1,240);
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
325 FND_MSG_PUB.add_exc_msg (
326 p_pkg_name => 'PA_PERF_OBJECT_RULES_PVT'
327 , p_procedure_name => PA_DEBUG.G_Err_Stack
328 , p_error_text => substr(SQLERRM,1,240));
329
330 rollback to sp;
331 RAISE;
332
333 END update_rule_object;
334
335 /*==================================================================
336 PROCEDURE
337 delete_rule_object
338 PURPOSE
339 This procedure deletes a row from the pa_perf_object_rules table.
340 ==================================================================*/
341
342 PROCEDURE delete_rule_object (
343 P_OBJECT_RULE_ID IN NUMBER,
344 P_RECORD_VERSION_NUMBER IN NUMBER,
345 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
346 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
347 X_MSG_DATA OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
348 X_RULE_NAME OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
349 )
350 IS
351 Invalid_Ret_Status EXCEPTION;
352 l_msg_count NUMBER;
353 l_msg_data VARCHAR2(2000);
354 l_data VARCHAR2(2000);
355 l_msg_index_out NUMBER;
356 l_debug_mode VARCHAR2(1);
357 l_debug_level2 CONSTANT NUMBER := 2;
358 l_debug_level3 CONSTANT NUMBER := 3;
359 l_debug_level4 CONSTANT NUMBER := 4;
360 l_debug_level5 CONSTANT NUMBER := 5;
361
362 BEGIN
363 savepoint sp;
364
365
366 -- Initialize the Error Stack
367 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PVT.Delete_Rule_object');
368 x_msg_count := 0;
369 x_msg_data := NULL;
370
371 -- Initialize the return status to success
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373
374 IF l_debug_mode = 'Y' THEN
375 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PVT.Delete_Rule_object',
376 p_debug_mode => l_debug_mode );
377 END IF;
378
379 IF l_debug_mode = 'Y' THEN
380 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PVT.Delete_Rule_object';
381 pa_debug.write(g_module_name,pa_debug.g_err_stage,
382 l_debug_level3);
383 pa_debug.g_err_stage:= 'P_OBJECT_RULE_ID = '|| P_OBJECT_RULE_ID;
384 pa_debug.write(g_module_name,pa_debug.g_err_stage,
385 l_debug_level5);
386
387 pa_debug.g_err_stage:= 'about to call lock row method';
388 pa_debug.write(g_module_name,pa_debug.g_err_stage,
389 l_debug_level3);
390 END IF;
391
392 BEGIN
393 SELECT RULE_NAME
394 INTO X_RULE_NAME
395 FROM PA_PERF_RULES
396 WHERE RULE_ID=(SELECT RULE_ID
397 FROM PA_PERF_OBJECT_RULES
398 WHERE OBJECT_RULE_ID=P_OBJECT_RULE_ID);
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 NULL;
402 END;
403
404 PA_PERF_OBJECT_RULES_PKG.LOCK_ROW
405 (
406 X_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
407 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
408 );
409
410 l_msg_count := FND_MSG_PUB.count_msg;
411 if(l_msg_count<>0) then
412 Raise Invalid_Ret_Status;
413 end if;
414
415 IF l_debug_mode = 'Y' THEN
416 pa_debug.g_err_stage:= 'about to call PA_PERF_THRESHOLDS_PKG.delete_row';
417 pa_debug.write(g_module_name,pa_debug.g_err_stage,
418 l_debug_level3);
419 END IF;
420
421 -- Delete Role
422 PA_PERF_OBJECT_RULES_PKG.DELETE_ROW
423 (X_OBJECT_RULE_ID => P_OBJECT_RULE_ID);
424
425 IF l_debug_mode = 'Y' THEN
426 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PVT.Delete_Rule_object';
427 pa_debug.write(g_module_name,pa_debug.g_err_stage,
428 l_debug_level3);
429 pa_debug.reset_curr_function;
430 END IF;
431
432 -- Reset the Error Stack
433 PA_DEBUG.reset_err_stack;
434
435 EXCEPTION
436 WHEN Invalid_Ret_Status THEN
437 x_return_status := FND_API.G_RET_STS_ERROR;
438 l_msg_count := FND_MSG_PUB.count_msg;
439
440 IF l_msg_count = 1 and x_msg_data IS NULL THEN
441 PA_INTERFACE_UTILS_PUB.get_messages
442 ( p_encoded => FND_API.G_TRUE
443 ,p_msg_index => 1
444 ,p_msg_count => l_msg_count
445 ,p_msg_data => l_msg_data
446 ,p_data => l_data
447 ,p_msg_index_out => l_msg_index_out);
448
449 x_msg_data := l_data;
450 x_msg_count := l_msg_count;
451 ELSE
452 x_msg_count := l_msg_count;
453 END IF;
454
455 IF l_debug_mode = 'Y' THEN
456 pa_debug.reset_curr_function;
457 END IF;
458
459 rollback to sp;
460 RETURN;
461
462 WHEN OTHERS THEN
463 x_msg_count := 1;
464 x_msg_data := substr(SQLERRM,1,240);
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 FND_MSG_PUB.add_exc_msg
470
467 ( p_pkg_name => 'PA_PERF_OBJECT_RULES_PVT'
468 , p_procedure_name => PA_DEBUG.G_Err_Stack
469 , p_error_text => substr(SQLERRM,1,240));
471 rollback to sp;
472 RAISE;
473
474 END Delete_Rule_object;
475
476 PROCEDURE validate_rule_object
477 (P_OBJECT_ID IN NUMBER,
478 P_OBJECT_TYPE IN VARCHAR2,
479 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
480 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
481 X_MSG_DATA OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
482 IS
483 CURSOR RULES_IN_ERROR IS
484 SELECT objrule.measure_name,objrule.period_type,count(*),
485 objrule.measure_id --Added for the bug# 3639474
486 FROM PA_PERF_OBJECT_RULES_V objrule
487 WHERE objrule.object_id = P_OBJECT_ID
488 AND objrule.rule_type='PERF_RULE'
489 GROUP BY objrule.measure_name,objrule.period_type, objrule.measure_id
490 HAVING count(*) >1;
491
492 CURSOR RULE_NAMES_IN_ERROR (l_measure_id IN NUMBER ,l_period_type IN VARCHAR2 ) IS --Changed the parameter l_measure_name VARCHAR2 to l_measure_id NUMBER for bug# 3639474
493 SELECT objrule.RULE_NAME
494 FROM PA_PERF_OBJECT_RULES_V objrule
495 WHERE objrule.object_id = P_OBJECT_ID
496 --AND objrule.measure_name = l_measure_name --commented for the bug# 3639474
497 AND objrule.measure_id = l_measure_id --Added for the bug# 3639474
498 AND objrule.period_type =l_period_type;
499
500 CURSOR RULES_IN_WARNING IS
501 SELECT objrule.measure_name,count(*),
502 objrule.measure_id ----Added for the bug# 3639474
503 FROM PA_PERF_OBJECT_RULES_V objrule
504 WHERE objrule.object_id = P_OBJECT_ID
505 AND objrule.rule_type='PERF_RULE'
506 AND EXISTS (select 1 from PA_PERF_OBJECT_RULES rule
507 where rule.record_version_number = 1 -- Changed hardcoded value from 2 to 1
508 and rule.rule_id in (select rule_id from PA_PERF_OBJECT_RULES_V objrule1
509 where objrule1.rule_id = rule.rule_id
510 and objrule1.measure_name=objrule.measure_name)
511 and rule.object_id=P_OBJECT_ID)
512 GROUP BY objrule.measure_name, objrule.measure_id
513 HAVING count(*) >1;
514
515 CURSOR RULE_NAMES_IN_WARNING (l_measure_id IN NUMBER ) IS --Changed the parameter l_measure_name VARCHAR2 to l_measure_id NUMBER for bug# 3639474
516 SELECT objrule.RULE_NAME
517 FROM PA_PERF_OBJECT_RULES_V objrule
518 WHERE objrule.object_id = P_OBJECT_ID
519 --AND objrule.measure_name = l_measure_name; --commented for the bug# 3639474
520 AND objrule.measure_id = l_measure_id; --Added for the bug# 3639474
521
522 l_rules_in_error RULES_IN_ERROR%ROWTYPE;
523 l_rules_in_warning RULES_IN_WARNING%ROWTYPE;
524
525 l_message_code VARCHAR2(2000);
526 l_rule_name VARCHAR2(80);
527 l_rule_count NUMBER;
528
529 BEGIN
530
531 x_msg_count := 0;
532 x_msg_data := NULL;
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 l_message_code := '';
536
537 FOR l_rules_in_error IN RULES_IN_ERROR LOOP
538
539 l_message_code := l_message_code||'<br>';
540
541 IF (x_msg_count = 0 ) THEN
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 x_msg_count := x_msg_count+1;
544 END IF ;
545
546 OPEN RULE_NAMES_IN_ERROR (l_rules_in_error.measure_id,l_rules_in_error.period_type) ; --Changed the parameter from l_rules_in_error.measure_name to l_rules_in_error.measure_id for the bug# 3639474
547
548 l_rule_count := 0;
549
550 LOOP
551 FETCH RULE_NAMES_IN_ERROR
552 INTO l_rule_name ;
553
554 EXIT WHEN RULE_NAMES_IN_ERROR%NOTFOUND;
555
556 IF l_rule_count<>0 THEN
557 l_message_code := l_message_code||', ';
558 END IF;
559
560 l_message_code := l_message_code||l_rule_name;
561 l_rule_count := 1;
562
563 END LOOP;
564
565 CLOSE RULE_NAMES_IN_ERROR;
566
567 l_message_code := l_message_code||'</br>';
568
569 END LOOP;
570
571 IF (x_msg_count <>0) THEN
572 ROLLBACK;
573 END IF;
574
575 PA_UTILS.ADD_MESSAGE
576 ( p_app_short_name => 'PA',
577 p_msg_name => 'PA_EXCP_PROJ_DUP_MEASURE_CAL',
578 p_token1 =>'PERF_RULE',
579 p_value1 =>l_message_code);
580
581 /* We should check for rules in warning only when there are no rules in error */
582
583 IF (x_msg_count = 0 ) THEN
584 l_message_code := '';
585
586 FOR l_rules_in_warning IN RULES_IN_WARNING LOOP
587
588 l_message_code := l_message_code||'<br>';
589
590 IF (x_msg_count = 0 ) THEN
591 x_msg_count := x_msg_count+1;
592 END IF ;
593
594 OPEN RULE_NAMES_IN_WARNING (l_rules_in_warning.measure_id); --Changed the parameter from l_rules_in_warning.measure_name to l_rules_in_warning.measure_id for the bug# 3639474
595
596 l_rule_count := 0;
597
598 LOOP
599 FETCH RULE_NAMES_IN_WARNING
600 INTO l_rule_name ;
601
602 EXIT WHEN RULE_NAMES_IN_WARNING%NOTFOUND;
603
604 IF l_rule_count<>0 THEN
605 l_message_code := l_message_code||', ';
606 END IF;
607
608 l_message_code := l_message_code||l_rule_name;
609 l_rule_count := 1;
610
611 END LOOP;
612
613 CLOSE RULE_NAMES_IN_WARNING;
614
615 l_message_code := l_message_code||'</br>';
616
617 END LOOP;
618
619 X_MSG_DATA := l_message_code ;
620 PA_UTILS.ADD_MESSAGE
621 ( p_app_short_name => 'PA',
622 p_msg_name => 'PA_EXCP_PROJ_RULE_DUP_MEASURE',
623 p_token1 =>'RULE_NAME',
624 p_value1 =>l_message_code);
625 END IF ;
626 /* New rows are created with record_version_number=2 .Update them back to 1 */
627
628 UPDATE PA_PERF_OBJECT_RULES
629 SET RECORD_VERSION_NUMBER = 1
630 WHERE RECORD_VERSION_NUMBER =2;
631
632 END VALIDATE_RULE_OBJECT;
633
634 END PA_PERF_OBJECT_RULES_PVT;