[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_RULES_PVT
Source
1 PACKAGE BODY pa_perf_rules_pvt AS
2 /* $Header: PAPERLVB.pls 120.1 2005/08/19 16:39:43 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_perf_rules_pvt';
5
6 /*==================================================================
7 PROCEDURE
8 create_rule
9 PURPOSE
10 This procedure inserts a row into the pa_perf_rules table.
11 ==================================================================*/
12
13
14 PROCEDURE create_rule(
15 P_RULE_ID IN NUMBER,
16 P_RULE_NAME IN VARCHAR2,
17 P_RULE_DESCRIPTION IN VARCHAR2,
18 P_RULE_TYPE IN VARCHAR2,
19 P_KPA_CODE IN VARCHAR2,
20 P_MEASURE_ID IN NUMBER,
21 P_MEASURE_FORMAT IN VARCHAR2,
22 P_CURRENCY_TYPE IN VARCHAR2,
23 P_PERIOD_TYPE IN VARCHAR2,
24 P_PRECISION IN NUMBER,
25 P_START_DATE_ACTIVE IN DATE,
26 P_END_DATE_ACTIVE IN DATE,
27 P_SCORE_METHOD IN VARCHAR2,
28 P_RECORD_VERSION_NUMBER IN NUMBER,
29 P_CREATION_DATE IN DATE,
30 P_CREATED_BY IN NUMBER,
31 P_LAST_UPDATE_DATE IN DATE,
32 P_LAST_UPDATED_BY IN NUMBER,
33 P_LAST_UPDATE_LOGIN IN NUMBER,
34 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
35 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
36 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
37 IS
38
39 l_msg_count NUMBER := 0;
40 l_data VARCHAR2(2000);
41 l_msg_data VARCHAR2(2000);
42 l_msg_index_out NUMBER;
43 l_debug_mode VARCHAR2(1);
44 l_rowid VARCHAR2(255);
45 l_debug_level2 CONSTANT NUMBER := 2;
46 l_debug_level3 CONSTANT NUMBER := 3;
47 l_debug_level4 CONSTANT NUMBER := 4;
48 l_debug_level5 CONSTANT NUMBER := 5;
49 dummy NUMBER;
50 duplicate_name EXCEPTION;
51
52 CURSOR CHECK_RULE_NAME IS
53 SELECT 1
54 FROM PA_PERF_RULES --Changed to PA_PERF_RULES from PA_PERF_RULES_V for Bug# 3639469
55 WHERE RULE_NAME=P_RULE_NAME
56 AND RULE_TYPE = P_RULE_TYPE; -- Added for Bug 4199228
57
58 BEGIN
59
60 savepoint sp;
61 -- Initialize the Error Stack
62 PA_DEBUG.init_err_stack('PA_PERF_RULES_PVT.create_rule');
63 x_msg_count := 0;
64 x_msg_data := NULL;
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
67
68 IF l_debug_mode = 'Y' THEN
69 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PVT.create_rule',
70 p_debug_mode => l_debug_mode );
71 END IF;
72
73 IF l_debug_mode = 'Y' THEN
74 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PVT.create_rule';
75 pa_debug.write(g_module_name,pa_debug.g_err_stage,
76 l_debug_level3);
77 END IF;
78
79 IF l_debug_mode = 'Y' THEN
80 pa_debug.g_err_stage:= 'about to check if the name is duplicate';
81 pa_debug.write(g_module_name,pa_debug.g_err_stage,
82 l_debug_level3);
83 END IF;
84
85 OPEN CHECK_RULE_NAME;
86 FETCH CHECK_RULE_NAME INTO dummy;
87 IF CHECK_RULE_NAME % FOUND THEN
88 RAISE duplicate_name;
89 END IF;
90 CLOSE CHECK_RULE_NAME;
91
92 PA_PERF_RULES_PKG.insert_row(
93 X_ROWID => l_rowid,
94 X_RULE_ID => P_RULE_ID,
95 X_RULE_NAME => P_RULE_NAME,
96 X_RULE_DESCRIPTION => P_RULE_DESCRIPTION,
97 X_RULE_TYPE => P_RULE_TYPE,
98 X_KPA_CODE => P_KPA_CODE,
99 X_MEASURE_ID => P_MEASURE_ID,
100 X_MEASURE_FORMAT => P_MEASURE_FORMAT,
101 X_CURRENCY_TYPE => P_CURRENCY_TYPE,
102 X_PERIOD_TYPE => P_PERIOD_TYPE,
103 X_PRECISION => P_PRECISION,
104 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
105 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
106 X_SCORE_METHOD => P_SCORE_METHOD,
107 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
108 X_CREATION_DATE => P_CREATION_DATE,
109 X_CREATED_BY => P_CREATED_BY,
110 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
113 );
114
115 -- Check for business rules violations
116
117 IF l_debug_mode = 'Y' THEN
118 pa_debug.g_err_stage:= 'Validating input parameters';
119 pa_debug.write(g_module_name,pa_debug.g_err_stage,
120 l_debug_level3);
121 END IF;
122
123
124 IF l_debug_mode = 'Y' THEN
125 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PVT.create_rule';
126 pa_debug.write(g_module_name,pa_debug.g_err_stage,
127 l_debug_level3);
128 pa_debug.reset_curr_function;
129 END IF;
130
131 -- Reset the Error Stack
132 PA_DEBUG.reset_err_stack;
133
134
135 EXCEPTION
136 WHEN duplicate_name THEN
137 x_return_status := FND_API.G_RET_STS_ERROR;
138
139 PA_UTILS.ADD_MESSAGE
140 ( p_app_short_name => 'PA',
141 p_msg_name => 'PA_NAME_UNIQUE');
142
143
144 x_msg_count := FND_MSG_PUB.count_msg;
145
146 IF l_debug_mode = 'Y' THEN
147 pa_debug.reset_curr_function;
148 END IF;
149
150 rollback to sp;
151 RETURN;
152
153 WHEN others THEN
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 x_msg_count := 1;
156 x_msg_data := SQLERRM;
157
158 FND_MSG_PUB.add_exc_msg(
159 p_pkg_name => 'PA_PERF_RULES_PVT'
160 ,p_procedure_name => 'CREATE_RULE'
161 ,p_error_text => x_msg_data);
162
163 IF l_debug_mode = 'Y' THEN
164 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
165 pa_debug.write(g_module_name,pa_debug.g_err_stage,
166 l_debug_level5);
167 pa_debug.reset_curr_function;
168 END IF;
169 RAISE;
170 END CREATE_RULE;
171
172 /*==================================================================
173 PROCEDURE
174 update_rule
175 PURPOSE
176 This procedure updates a row into the pa_perf_rules table.
177 ==================================================================*/
178
179 PROCEDURE update_rule(
180 P_RULE_ID IN NUMBER,
181 P_RULE_NAME IN VARCHAR2,
182 P_RULE_DESCRIPTION IN VARCHAR2,
183 P_RULE_TYPE IN VARCHAR2,
184 P_KPA_CODE IN VARCHAR2,
185 P_MEASURE_ID IN NUMBER,
186 P_MEASURE_FORMAT IN VARCHAR2,
187 P_CURRENCY_TYPE IN VARCHAR2,
188 P_PERIOD_TYPE IN VARCHAR2,
189 P_PRECISION IN NUMBER,
190 P_START_DATE_ACTIVE IN DATE,
191 P_END_DATE_ACTIVE IN DATE,
192 P_SCORE_METHOD IN VARCHAR2,
193 P_RECORD_VERSION_NUMBER IN NUMBER,
194 P_LAST_UPDATE_DATE IN DATE,
195 P_LAST_UPDATED_BY IN NUMBER,
196 P_LAST_UPDATE_LOGIN IN NUMBER,
197 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
198 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
199 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
200 IS
201 Invalid_Ret_Status EXCEPTION;
202 l_msg_count NUMBER := 0;
203 l_data VARCHAR2(2000);
204 l_msg_data VARCHAR2(2000);
205 l_msg_index_out NUMBER;
206 l_debug_mode VARCHAR2(1);
207 l_rowid VARCHAR2(255);
208 l_debug_level2 CONSTANT NUMBER := 2;
209 l_debug_level3 CONSTANT NUMBER := 3;
210 l_debug_level4 CONSTANT NUMBER := 4;
211 l_debug_level5 CONSTANT NUMBER := 5;
212 dummy NUMBER;
213 duplicate_name EXCEPTION;
214
215 CURSOR CHECK_RULE_NAME IS
216 SELECT 1
217 FROM PA_PERF_RULES --Changed to PA_PERF_RULES from PA_PERF_RULES_V for Bug# 3639469
218 WHERE RULE_NAME=P_RULE_NAME
219 AND RULE_ID <> P_RULE_ID
220 AND RULE_TYPE = P_RULE_TYPE; -- Added for Bug 4199228
221
222 BEGIN
223
224 savepoint sp;
225 -- Initialize the Error Stack
226 PA_DEBUG.init_err_stack('PA_PERF_RULES_PVT.Update_Rule');
227 x_msg_count := 0;
228 x_msg_data := NULL;
229
230 -- Initialize the return status to success
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
233 IF l_debug_mode = 'Y' THEN
234 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PVT.update_rule',
235 p_debug_mode => l_debug_mode );
236 END IF;
237
238 IF l_debug_mode = 'Y' THEN
239 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PVT.update_rule';
240 pa_debug.write(g_module_name,pa_debug.g_err_stage,
241 l_debug_level3);
242
243 pa_debug.g_err_stage:= 'P_RULE_ID = '|| P_RULE_ID;
244 pa_debug.write(g_module_name,pa_debug.g_err_stage,
245 l_debug_level5);
246
247 pa_debug.g_err_stage:= 'P_RULE_TYPE = '|| P_RULE_TYPE;
248 pa_debug.write(g_module_name,pa_debug.g_err_stage,
249 l_debug_level5);
250 pa_debug.g_err_stage:= 'P_RULE_NAME = '|| P_RULE_NAME;
251 pa_debug.write(g_module_name,pa_debug.g_err_stage,
252 l_debug_level5);
253 END IF;
254
255 IF l_debug_mode = 'Y' THEN
256 pa_debug.g_err_stage:= 'about to check if the name is duplicate';
257 pa_debug.write(g_module_name,pa_debug.g_err_stage,
258 l_debug_level3);
259 END IF;
260
261 OPEN CHECK_RULE_NAME;
262 FETCH CHECK_RULE_NAME INTO dummy;
263 IF CHECK_RULE_NAME % FOUND THEN
264 RAISE duplicate_name;
265 END IF;
266 CLOSE CHECK_RULE_NAME;
267
268 IF l_debug_mode = 'Y' THEN
269 pa_debug.g_err_stage:= 'about to call lock row method';
270 pa_debug.write(g_module_name,pa_debug.g_err_stage,
271 l_debug_level3);
272 END IF;
273
274 PA_PERF_RULES_PKG.LOCK_ROW (
275 X_RULE_ID => P_RULE_ID,
276 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER );
277
278 IF l_debug_mode = 'Y' THEN
279 pa_debug.g_err_stage:= 'lock row method called';
280 pa_debug.write(g_module_name,pa_debug.g_err_stage,
281 l_debug_level3);
282 END IF;
283
284 l_msg_count := FND_MSG_PUB.count_msg;
285
286 if(l_msg_count<>0) then
287 Raise Invalid_Ret_Status;
288 end if;
289
290 IF l_debug_mode = 'Y' THEN
291 pa_debug.g_err_stage:= 'about to call PA_PERF_RULES_PKG.UPDATE_ROW';
292 pa_debug.write(g_module_name,pa_debug.g_err_stage,
293 l_debug_level3);
294 END IF;
295
296 PA_PERF_RULES_PKG.UPDATE_ROW(
297 X_RULE_ID => P_RULE_ID,
298 X_RULE_NAME => P_RULE_NAME,
299 X_RULE_DESCRIPTION => P_RULE_DESCRIPTION,
300 X_RULE_TYPE => P_RULE_TYPE,
301 X_KPA_CODE => P_KPA_CODE,
302 X_MEASURE_ID => P_MEASURE_ID,
303 X_MEASURE_FORMAT => P_MEASURE_FORMAT,
304 X_CURRENCY_TYPE => P_CURRENCY_TYPE,
305 X_PERIOD_TYPE => P_PERIOD_TYPE,
306 X_PRECISION => P_PRECISION,
307 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
308 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
309 X_SCORE_METHOD => P_SCORE_METHOD,
310 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
311 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
312 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
313 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN );
314
315 IF l_debug_mode = 'Y' THEN
316 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PVT.update_rule';
317 pa_debug.write(g_module_name,pa_debug.g_err_stage,
318 l_debug_level3);
319 pa_debug.reset_curr_function;
320 END IF;
321
322 -- Reset the Error Stack
323 PA_DEBUG.reset_err_stack;
324
325 EXCEPTION
326
327 WHEN duplicate_name THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329
330 PA_UTILS.ADD_MESSAGE
331 ( p_app_short_name => 'PA',
332 p_msg_name => 'PA_NAME_UNIQUE');
333
334 x_msg_count := FND_MSG_PUB.count_msg;
335
336 IF l_debug_mode = 'Y' THEN
337 pa_debug.reset_curr_function;
338 END IF;
339
340 rollback to sp;
341 RETURN;
342
343 WHEN Invalid_Ret_Status THEN
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 l_msg_count := FND_MSG_PUB.count_msg;
346
347 IF l_msg_count = 1 and x_msg_data IS NULL THEN
348 PA_INTERFACE_UTILS_PUB.get_messages(
349 p_encoded => FND_API.G_TRUE
350 ,p_msg_index => 1
351 ,p_msg_count => l_msg_count
352 ,p_msg_data => l_msg_data
353 ,p_data => l_data
354 ,p_msg_index_out => l_msg_index_out);
355
356 x_msg_data := l_data;
357 x_msg_count := l_msg_count;
358 ELSE
359 x_msg_count := l_msg_count;
360 END IF;
361
362 IF l_debug_mode = 'Y' THEN
363 pa_debug.reset_curr_function;
364 END IF;
365
366 rollback to sp;
367 RETURN;
368
369 WHEN OTHERS THEN
370 x_msg_count := 1;
371 x_msg_data := substr(SQLERRM,1,240);
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 FND_MSG_PUB.add_exc_msg (
374 p_pkg_name => 'PA_PERF_RULES_PVT'
375 , p_procedure_name => PA_DEBUG.G_Err_Stack
376 , p_error_text => substr(SQLERRM,1,240));
377
378 rollback to sp;
379 RAISE;
380
381 END update_rule;
382
383 /*==================================================================
384 PROCEDURE
385 delete_rule
386 PURPOSE
387 This procedure deletes a row from the pa_perf_rules table.
388 ==================================================================*/
389
390 PROCEDURE delete_rule (
391 P_RULE_ID IN NUMBER,
392 P_RECORD_VERSION_NUMBER IN NUMBER,
393 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
394 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
395 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
396 )
397 IS
398 Invalid_Ret_Status EXCEPTION;
399 l_msg_count NUMBER;
400 l_msg_data VARCHAR2(2000);
401 l_data VARCHAR2(2000);
402 l_return_status VARCHAR2(1);
403 l_msg_index_out NUMBER;
404 l_debug_mode VARCHAR2(1);
405 l_debug_level2 CONSTANT NUMBER := 2;
406 l_debug_level3 CONSTANT NUMBER := 3;
407 l_debug_level4 CONSTANT NUMBER := 4;
408 l_debug_level5 CONSTANT NUMBER := 5;
409 l_rule_name VARCHAR2(2000);
410
411 CURSOR cur_threshold( rule_id_par NUMBER)
412 IS
413 SELECT threshold_id, record_version_number
414 FROM pa_perf_thresholds
415 WHERE thres_obj_id= rule_id_par;
416
417 CURSOR cur_obj_rule( rule_id_par NUMBER)
418 IS
419 SELECT object_rule_id ,record_version_number
420 FROM pa_perf_object_rules
421 WHERE rule_id = rule_id_par;
422
423 BEGIN
424 savepoint sp;
425
426
427 -- Initialize the Error Stack
428 PA_DEBUG.init_err_stack('PA_PERF_RULES_PVT.Delete_Row');
429 x_msg_count := 0;
430 x_msg_data := NULL;
431
432 -- Initialize the return status to success
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434
435 IF l_debug_mode = 'Y' THEN
436 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PVT.delete_rule',
437 p_debug_mode => l_debug_mode );
438 END IF;
439
440 IF l_debug_mode = 'Y' THEN
441 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PVT.delete_rule';
442 pa_debug.write(g_module_name,pa_debug.g_err_stage,
443 l_debug_level3);
444 pa_debug.g_err_stage:= 'P_RULE_ID = '|| P_RULE_ID;
445 pa_debug.write(g_module_name,pa_debug.g_err_stage,
446 l_debug_level5);
447
448 pa_debug.g_err_stage:= 'about to call lock row method';
449 pa_debug.write(g_module_name,pa_debug.g_err_stage,
450 l_debug_level3);
451 END IF;
452
453 PA_PERF_RULES_PKG.LOCK_ROW
454 (
455 X_RULE_ID => P_RULE_ID,
456 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
457 );
458
459 l_msg_count := FND_MSG_PUB.count_msg;
460 if(l_msg_count<>0) then
461 Raise Invalid_Ret_Status;
462 end if;
463
464 IF l_debug_mode = 'Y' THEN
465 pa_debug.g_err_stage:= 'about to call PA_PERF_RULES_PKG.delete_row';
466 pa_debug.write(g_module_name,pa_debug.g_err_stage,
467 l_debug_level3);
468 END IF;
469
470
471 FOR cur_var IN cur_threshold( p_rule_id )
472 LOOP
473 l_return_status := FND_API.G_RET_STS_SUCCESS;
474 l_msg_count := 0;
475 l_msg_data := NULL;
476 PA_PERF_THRESHOLDS_PVT.delete_rule_det (
477 P_THRESHOLD_ID => cur_var.threshold_id,
478 P_RECORD_VERSION_NUMBER => cur_var.record_version_number,
479 X_RETURN_STATUS => l_return_status,
480 X_MSG_COUNT => l_msg_count,
481 X_MSG_DATA => l_msg_data );
482
483 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
484 IF l_debug_mode = 'Y' THEN
485 pa_debug.g_err_stage:= 'about to delete the Rule Threshold, Threshold_id : '|| cur_var.threshold_id;
486 pa_debug.write(g_module_name,pa_debug.g_err_stage,
487 l_debug_level3);
488 END IF;
489 Raise Invalid_Ret_Status;
490 end if;
491
492 END LOOP;
493
494 FOR cur_var IN cur_obj_rule( p_rule_id )
495 LOOP
496 l_return_status := FND_API.G_RET_STS_SUCCESS;
497 l_msg_count := 0;
498 l_msg_data := NULL;
499 PA_PERF_OBJECT_RULES_PVT.delete_rule_object (
500 P_OBJECT_RULE_ID => cur_var.object_rule_id,
501 P_RECORD_VERSION_NUMBER => cur_var.record_version_number,
502 X_RETURN_STATUS => l_return_status,
503 X_MSG_COUNT => l_msg_count,
504 X_MSG_DATA => l_msg_data,
505 X_RULE_NAME => l_rule_name );
506
507 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
508 IF l_debug_mode = 'Y' THEN
509 pa_debug.g_err_stage:= 'about to delete the Rule Object Association, object_rule_id : '|| cur_var.object_rule_id;
510 pa_debug.write(g_module_name,pa_debug.g_err_stage,
511 l_debug_level3);
512 END IF;
513 Raise Invalid_Ret_Status;
514 end if;
515
516 END LOOP;
517
518 -- Delete Rule
519 PA_PERF_RULES_PKG.DELETE_ROW
520 ( X_RULE_ID => P_RULE_ID);
521
522 IF l_debug_mode = 'Y' THEN
523 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PVT.delete_rule';
524 pa_debug.write(g_module_name,pa_debug.g_err_stage,
525 l_debug_level3);
526 pa_debug.reset_curr_function;
527 END IF;
528
529 -- Reset the Error Stack
530 PA_DEBUG.reset_err_stack;
531
532 EXCEPTION
533 WHEN Invalid_Ret_Status THEN
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 l_msg_count := FND_MSG_PUB.count_msg;
536
537 IF l_msg_count = 1 and x_msg_data IS NULL THEN
538 PA_INTERFACE_UTILS_PUB.get_messages
539 ( p_encoded => FND_API.G_TRUE
540 ,p_msg_index => 1
541 ,p_msg_count => l_msg_count
542 ,p_msg_data => l_msg_data
543 ,p_data => l_data
544 ,p_msg_index_out => l_msg_index_out);
545
546 x_msg_data := l_data;
547 x_msg_count := l_msg_count;
548 ELSE
549 x_msg_count := l_msg_count;
550 END IF;
551
552 IF l_debug_mode = 'Y' THEN
553 pa_debug.reset_curr_function;
554 END IF;
555
556 rollback to sp;
557 RETURN;
558
559 WHEN OTHERS THEN
560 x_msg_count := 1;
561 x_msg_data := substr(SQLERRM,1,240);
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 FND_MSG_PUB.add_exc_msg
564 ( p_pkg_name => 'PA_PERF_RULES_PVT'
565 , p_procedure_name => PA_DEBUG.G_Err_Stack
566 , p_error_text => substr(SQLERRM,1,240));
567
568 rollback to sp;
569 RAISE;
570
571 END delete_rule;
572
573 /*==================================================================
574 PROCEDURE
575 validate_rule
576 PURPOSE
577 This procedure validates the performance rule to be inserted .
578 ==================================================================*/
579 PROCEDURE validate_rule(
580 P_RULE_ID IN NUMBER,
581 P_RULE_NAME IN VARCHAR2,
582 P_RULE_TYPE IN VARCHAR2,
583 P_PRECISION IN NUMBER,
584 P_START_DATE_ACTIVE IN DATE,
585 P_END_DATE_ACTIVE IN DATE,
586 P_THRESHOLD_ID IN SYSTEM.PA_NUM_TBL_TYPE,
587 P_THRES_OBJ_ID IN SYSTEM.PA_NUM_TBL_TYPE,
588 P_FROM_VALUE IN SYSTEM.PA_NUM_TBL_TYPE,
589 P_TO_VALUE IN SYSTEM.PA_NUM_TBL_TYPE,
590 P_INDICATOR_CODE IN SYSTEM.pa_varchar2_30_tbl_type,
591 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
592 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
593 X_MSG_DATA OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
594 P_WEIGHTING IN SYSTEM.PA_NUM_TBL_TYPE )
595 IS
596 j NUMBER;
597 k NUMBER;
598 l_check_precission VARCHAR2(1) := 'N' ;
599 l_check_from_to_value VARCHAR2(1) := 'N' ;
600 l_indicator_code VARCHAR2(1) := 'N' ;
601 l_check_range VARCHAR2(1) := 'N' ;
602 l_check_weighting VARCHAR2(1) := 'N';
603 l_check_threshold VARCHAR2(1) := 'N';
604 l_debug_mode VARCHAR2(1);
605 BEGIN
606
607 -- Initialize the Error Stack
608 PA_DEBUG.init_err_stack('PA_PERF_RULES_PVT.validate_rule');
609 x_msg_count := 0;
610 x_msg_data := NULL;
611
612 -- Initialize the return status to success
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614
615 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
616
617 IF l_debug_mode = 'Y' THEN
618 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PVT.validate_rule',
619 p_debug_mode => l_debug_mode );
620 END IF;
621
622 k := P_THRESHOLD_ID.count; /* Get the number of lines */
623
624 IF (P_END_DATE_ACTIVE IS NOT NULL) THEN
625 IF (trunc(P_START_DATE_ACTIVE) >= trunc(P_END_DATE_ACTIVE)) THEN
626 PA_UTILS.ADD_MESSAGE
627 ( p_app_short_name => 'PA',
628 p_msg_name => 'PA_SETUP_CHK_ST_EN_DATE');
629 x_return_status := FND_API.G_RET_STS_ERROR;
630 x_msg_count := FND_MSG_PUB.count_msg;
631 END IF;
632 END IF;
633
634 FOR i in P_FROM_VALUE.FIRST..P_FROM_VALUE.LAST
635 LOOP
636
637 /* Checking the precision of the thresholds defined for this rule */
638
639 IF ((length(abs(P_FROM_VALUE(i)-trunc(P_FROM_VALUE(i)))) - length(abs(P_PRECISION-trunc(P_PRECISION)))) > 0
640 OR (length(abs(P_TO_VALUE(i)-trunc(P_TO_VALUE(i)))) - length(abs(P_PRECISION-trunc(P_PRECISION)))) > 0) then
641
642 l_check_precission := 'Y' ;
643
644 END IF;
645
646 /* Checking the FROM TO values of the thresholds defined for this rule */
647
648 IF (P_FROM_VALUE(i)>=P_TO_VALUE(i)) THEN
649 l_check_from_to_value := 'Y';
650 END IF;
651
652 /* Checking the Indicator codes of the thresholds defined for this rule */
653 IF (P_RULE_TYPE = 'SCORE_RULE' ) THEN
654 j :=i+1;
655 while(j <= k) LOOP
656 IF ( P_INDICATOR_CODE(i) = P_INDICATOR_CODE(j)) THEN
657 l_indicator_code := 'Y';
658 END IF;
659 j := j+1;
660 END LOOP;
661 END IF;
662 /* Checking that the range of the thresholds do not overlap */
663 j := i+1;
664 while (j<=k) LOOP
665 IF NOT (((P_FROM_VALUE(i) <P_FROM_VALUE(j)) AND(P_TO_VALUE(i) <P_FROM_VALUE(j)))OR ((P_FROM_VALUE(i) >P_TO_VALUE(j)) AND(P_TO_VALUE(i) >P_TO_VALUE(j)))) THEN
666 l_check_range := 'Y';
667 END IF;
668 j := j+1;
669 END LOOP;
670
671 IF (P_RULE_TYPE = 'PERF_RULE' ) THEN
672 IF (P_WEIGHTING(i) <0) THEN
673 l_check_weighting := 'Y' ;
674 END IF;
675 END IF;
676
677 IF (P_RULE_TYPE = 'SCORE_RULE' ) THEN
678 IF ((P_FROM_VALUE(i) <0) OR (P_TO_VALUE(i) <0)) THEN
679 l_check_threshold := 'Y' ;
680 END IF;
681 END IF;
682 END LOOP;
683
684 IF (l_check_precission = 'Y') THEN
685 PA_UTILS.ADD_MESSAGE
686 ( p_app_short_name => 'PA',
687 p_msg_name => 'PA_THRES_PRECISION_CHECK');
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 x_msg_count := FND_MSG_PUB.count_msg;
690 END IF ;
691
692 IF (l_check_from_to_value = 'Y') THEN
693 PA_UTILS.ADD_MESSAGE
694 ( p_app_short_name => 'PA',
695 p_msg_name => 'PA_THRESH_FROM_TO_VALUE');
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 x_msg_count := FND_MSG_PUB.count_msg;
698 END IF ;
699
700 IF (l_indicator_code = 'Y') THEN
701 PA_UTILS.ADD_MESSAGE
702 ( p_app_short_name => 'PA',
703 p_msg_name => 'PA_INDICATOR_UNIQUE');
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 x_msg_count := FND_MSG_PUB.count_msg;
706 END IF ;
707
708 IF (l_check_range = 'Y') THEN
709 PA_UTILS.ADD_MESSAGE
710 ( p_app_short_name => 'PA',
711 p_msg_name => 'PA_THRES_RANGE_CHECK');
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 x_msg_count := FND_MSG_PUB.count_msg;
714 END IF ;
715
716
717 IF (l_check_weighting = 'Y') THEN
718 PA_UTILS.ADD_MESSAGE
719 ( p_app_short_name => 'PA',
720 p_msg_name => 'PA_NEGATIVE_WEIGHTING');
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 x_msg_count := FND_MSG_PUB.count_msg;
723 END IF ;
724
725
726 IF (l_check_threshold = 'Y') THEN
727 PA_UTILS.ADD_MESSAGE
728 ( p_app_short_name => 'PA',
729 p_msg_name => 'PA_THRESHOLD_NEGATIVE');
730 x_return_status := FND_API.G_RET_STS_ERROR;
731 x_msg_count := FND_MSG_PUB.count_msg;
732 END IF ;
733
734 END validate_rule;
735
736 END PA_PERF_RULES_PVT;