[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_RULES_PUB
Source
1 PACKAGE BODY pa_perf_rules_pub AS
2 /* $Header: PAPERLPB.pls 120.1 2005/08/19 16:39:26 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_perf_rules_pub';
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 Invalid_Ret_Status EXCEPTION;
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_return_status VARCHAR2(1);
44 l_debug_mode VARCHAR2(1);
45 l_rowid VARCHAR2(255);
46 l_debug_level2 CONSTANT NUMBER := 2;
47 l_debug_level3 CONSTANT NUMBER := 3;
48 l_debug_level4 CONSTANT NUMBER := 4;
49 l_debug_level5 CONSTANT NUMBER := 5;
50
51 BEGIN
52
53 -- Initialize the Error Stack
54 PA_DEBUG.init_err_stack('PA_PERF_RULES_PUB.create_rule');
55 x_msg_count := 0;
56 x_msg_data := NULL;
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
59
60 IF l_debug_mode = 'Y' THEN
61 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PUB.create_rule',
62 p_debug_mode => l_debug_mode );
63 END IF;
64
65 IF l_debug_mode = 'Y' THEN
66 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PUB.create_rule';
67 pa_debug.write(g_module_name,pa_debug.g_err_stage,
68 l_debug_level3);
69 END IF;
70
71
72 PA_PERF_RULES_PVT.create_rule(
73 P_RULE_ID => P_RULE_ID,
74 P_RULE_NAME => P_RULE_NAME,
75 P_RULE_DESCRIPTION => P_RULE_DESCRIPTION,
76 P_RULE_TYPE => P_RULE_TYPE,
77 P_KPA_CODE => P_KPA_CODE,
78 P_MEASURE_ID => P_MEASURE_ID,
79 P_MEASURE_FORMAT => P_MEASURE_FORMAT,
80 P_CURRENCY_TYPE => P_CURRENCY_TYPE,
81 P_PERIOD_TYPE => P_PERIOD_TYPE,
82 P_PRECISION => P_PRECISION,
83 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
84 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
85 P_SCORE_METHOD => P_SCORE_METHOD,
86 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
87 P_CREATION_DATE => P_CREATION_DATE,
88 P_CREATED_BY => P_CREATED_BY,
89 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
90 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
91 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
92 X_RETURN_STATUS => l_return_status,
93 X_MSG_COUNT => l_msg_count,
94 X_MSG_DATA => l_msg_data );
95
96
97 if(l_msg_count<>0) then
98 Raise Invalid_Ret_Status;
99 end if;
100
101
102 IF l_debug_mode = 'Y' THEN
103 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PUB.create_rule';
104 pa_debug.write(g_module_name,pa_debug.g_err_stage,
105 l_debug_level3);
106 pa_debug.reset_curr_function;
107 END IF;
108
109 -- Reset the Error Stack
110 PA_DEBUG.reset_err_stack;
111
112
113 EXCEPTION
114 WHEN Invalid_Ret_Status THEN
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 l_msg_count := FND_MSG_PUB.count_msg;
117
118 IF l_msg_count = 1 and x_msg_data IS NULL THEN
119 PA_INTERFACE_UTILS_PUB.get_messages(
120 p_encoded => FND_API.G_TRUE
121 ,p_msg_index => 1
122 ,p_msg_count => l_msg_count
123 ,p_msg_data => l_msg_data
124 ,p_data => l_data
125 ,p_msg_index_out => l_msg_index_out);
126
127 x_msg_data := l_data;
128 x_msg_count := l_msg_count;
129 ELSE
130 x_msg_count := l_msg_count;
131 END IF;
132
133 IF l_debug_mode = 'Y' THEN
134 pa_debug.reset_curr_function;
135 END IF;
136
137 rollback to sp;
138 RETURN;
139
140 WHEN others THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 x_msg_count := 1;
143 x_msg_data := SQLERRM;
144
145 FND_MSG_PUB.add_exc_msg(
146 p_pkg_name => 'PA_PERF_RULES_PUB'
147 ,p_procedure_name => 'CREATE_RULE'
148 ,p_error_text => x_msg_data);
149
150 IF l_debug_mode = 'Y' THEN
151 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
152 pa_debug.write(g_module_name,pa_debug.g_err_stage,
153 l_debug_level5);
154 pa_debug.reset_curr_function;
155 END IF;
156 RAISE;
157 END CREATE_RULE;
158
159 /*==================================================================
160 PROCEDURE
161 update_rule
162 PURPOSE
163 This procedure updates a row into the pa_perf_rules table.
164 ==================================================================*/
165
166 PROCEDURE update_rule(
167 P_RULE_ID IN NUMBER,
168 P_RULE_NAME IN VARCHAR2,
169 P_RULE_DESCRIPTION IN VARCHAR2,
170 P_RULE_TYPE IN VARCHAR2,
171 P_KPA_CODE IN VARCHAR2,
172 P_MEASURE_ID IN NUMBER,
173 P_MEASURE_FORMAT IN VARCHAR2,
174 P_CURRENCY_TYPE IN VARCHAR2,
175 P_PERIOD_TYPE IN VARCHAR2,
176 P_PRECISION IN NUMBER,
177 P_START_DATE_ACTIVE IN DATE,
178 P_END_DATE_ACTIVE IN DATE,
179 P_SCORE_METHOD IN VARCHAR2,
180 P_RECORD_VERSION_NUMBER IN NUMBER,
181 P_LAST_UPDATE_DATE IN DATE,
182 P_LAST_UPDATED_BY IN NUMBER,
183 P_LAST_UPDATE_LOGIN IN NUMBER,
184 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
185 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
186 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
187 IS
188 Invalid_Ret_Status EXCEPTION;
189 l_msg_count NUMBER := 0;
190 l_data VARCHAR2(2000);
191 l_msg_data VARCHAR2(2000);
192 l_msg_index_out NUMBER;
193 l_return_status VARCHAR2(1);
194 l_debug_mode VARCHAR2(1);
195 l_rowid VARCHAR2(255);
196 l_debug_level2 CONSTANT NUMBER := 2;
197 l_debug_level3 CONSTANT NUMBER := 3;
198 l_debug_level4 CONSTANT NUMBER := 4;
199 l_debug_level5 CONSTANT NUMBER := 5;
200
201 BEGIN
202
203 savepoint sp;
204 -- Initialize the Error Stack
205 PA_DEBUG.init_err_stack('PA_PERF_RULES_PUB.Update_Rule');
206 x_msg_count := 0;
207 x_msg_data := NULL;
208
209 -- Initialize the return status to success
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
212 IF l_debug_mode = 'Y' THEN
213 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PUB.update_rule',
214 p_debug_mode => l_debug_mode );
215 END IF;
216
217 IF l_debug_mode = 'Y' THEN
218 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PUB.update_rule';
219 pa_debug.write(g_module_name,pa_debug.g_err_stage,
220 l_debug_level3);
221
222 pa_debug.g_err_stage:= 'P_RULE_ID = '|| P_RULE_ID;
223 pa_debug.write(g_module_name,pa_debug.g_err_stage,
224 l_debug_level5);
225
226 pa_debug.g_err_stage:= 'P_RULE_TYPE = '|| P_RULE_TYPE;
227 pa_debug.write(g_module_name,pa_debug.g_err_stage,
228 l_debug_level5);
229 pa_debug.g_err_stage:= 'P_RULE_NAME = '|| P_RULE_NAME;
230 pa_debug.write(g_module_name,pa_debug.g_err_stage,
231 l_debug_level5);
232 END IF;
233
234
235 IF l_debug_mode = 'Y' THEN
236 pa_debug.g_err_stage:= 'about to call PA_PER_RULES_PVT.UPDATE_RULE';
237 pa_debug.write(g_module_name,pa_debug.g_err_stage,
238 l_debug_level3);
239 END IF;
240
241 PA_PERF_RULES_PVT.UPDATE_RULE(
242 P_RULE_ID => P_RULE_ID,
243 P_RULE_NAME => P_RULE_NAME,
244 P_RULE_DESCRIPTION => P_RULE_DESCRIPTION,
245 P_RULE_TYPE => P_RULE_TYPE,
246 P_KPA_CODE => P_KPA_CODE,
247 P_MEASURE_ID => P_MEASURE_ID,
248 P_MEASURE_FORMAT => P_MEASURE_FORMAT,
249 P_CURRENCY_TYPE => P_CURRENCY_TYPE,
250 P_PERIOD_TYPE => P_PERIOD_TYPE,
251 P_PRECISION => P_PRECISION,
252 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
253 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
254 P_SCORE_METHOD => P_SCORE_METHOD,
255 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
256 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
257 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
258 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
259 X_RETURN_STATUS => l_return_status,
260 X_MSG_COUNT => l_msg_count,
261 X_MSG_DATA => l_msg_data );
262
263 if(l_msg_count<>0) then
264 Raise Invalid_Ret_Status;
265 end if;
266
267 IF l_debug_mode = 'Y' THEN
268 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PUB.update_rule';
269 pa_debug.write(g_module_name,pa_debug.g_err_stage,
270 l_debug_level3);
271 pa_debug.reset_curr_function;
272 END IF;
273
274 -- Reset the Error Stack
275 PA_DEBUG.reset_err_stack;
276
277 EXCEPTION
278 WHEN Invalid_Ret_Status THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 l_msg_count := FND_MSG_PUB.count_msg;
281
282 IF l_msg_count = 1 and x_msg_data IS NULL THEN
283 PA_INTERFACE_UTILS_PUB.get_messages(
284 p_encoded => FND_API.G_TRUE
285 ,p_msg_index => 1
286 ,p_msg_count => l_msg_count
287 ,p_msg_data => l_msg_data
288 ,p_data => l_data
289 ,p_msg_index_out => l_msg_index_out);
290
291 x_msg_data := l_data;
292 x_msg_count := l_msg_count;
293 ELSE
294 x_msg_count := l_msg_count;
295 END IF;
296
297 IF l_debug_mode = 'Y' THEN
298 pa_debug.reset_curr_function;
299 END IF;
300
301 rollback to sp;
302 RETURN;
303
304 WHEN OTHERS THEN
305 x_msg_count := 1;
306 x_msg_data := substr(SQLERRM,1,240);
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 FND_MSG_PUB.add_exc_msg (
309 p_pkg_name => 'PA_PERF_RULES_PUB'
310 , p_procedure_name => PA_DEBUG.G_Err_Stack
311 , p_error_text => substr(SQLERRM,1,240));
312
313 rollback to sp;
314 RAISE;
315
316 END update_rule;
317
318 /*==================================================================
319 PROCEDURE
320 delete_rule
321 PURPOSE
322 This procedure deletes a row from the pa_perf_rules table.
323 ==================================================================*/
324
325 PROCEDURE delete_rule (
326 P_RULE_ID IN NUMBER,
327 P_RECORD_VERSION_NUMBER IN NUMBER,
328 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
329 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
330 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
331 )
332 IS
333 Invalid_Ret_Status EXCEPTION;
334 l_msg_count NUMBER;
335 l_msg_data VARCHAR2(2000);
336 l_data VARCHAR2(2000);
337 l_msg_index_out NUMBER;
338 l_return_status VARCHAR2(1);
339 l_debug_mode VARCHAR2(1);
340 l_debug_level2 CONSTANT NUMBER := 2;
341 l_debug_level3 CONSTANT NUMBER := 3;
342 l_debug_level4 CONSTANT NUMBER := 4;
343 l_debug_level5 CONSTANT NUMBER := 5;
344
345 BEGIN
346 savepoint sp;
347
348
349 -- Initialize the Error Stack
350 PA_DEBUG.init_err_stack('PA_PERF_RULES_PUB.Delete_Rule');
351 x_msg_count := 0;
352 x_msg_data := NULL;
353
354 -- Initialize the return status to success
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356
357 IF l_debug_mode = 'Y' THEN
358 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PUB.delete_rule',
359 p_debug_mode => l_debug_mode );
360 END IF;
361
362 IF l_debug_mode = 'Y' THEN
363 pa_debug.g_err_stage:= 'Entered PA_PERF_RULES_PUB.delete_rule';
364 pa_debug.write(g_module_name,pa_debug.g_err_stage,
365 l_debug_level3);
366 pa_debug.g_err_stage:= 'P_RULE_ID = '|| P_RULE_ID;
367 pa_debug.write(g_module_name,pa_debug.g_err_stage,
368 l_debug_level5);
369 END IF;
370
371
372 IF l_debug_mode = 'Y' THEN
373 pa_debug.g_err_stage:= 'about to call PA_PERF_RULES_PVT.delete_rule';
374 pa_debug.write(g_module_name,pa_debug.g_err_stage,
375 l_debug_level3);
376 END IF;
377
378 -- Delete Role
379 PA_PERF_RULES_PVT.DELETE_RULE
380 ( P_RULE_ID => P_RULE_ID,
381 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
382 X_RETURN_STATUS => l_return_status,
383 X_MSG_COUNT => l_msg_count,
384 X_MSG_DATA => l_msg_data );
385
386 if(l_msg_count<>0) then
387 Raise Invalid_Ret_Status;
388 end if;
389
390 IF l_debug_mode = 'Y' THEN
391 pa_debug.g_err_stage:= 'Exiting PA_PERF_RULES_PUB.delete_rule';
392 pa_debug.write(g_module_name,pa_debug.g_err_stage,
393 l_debug_level3);
394 pa_debug.reset_curr_function;
395 END IF;
396
397 -- Reset the Error Stack
398 PA_DEBUG.reset_err_stack;
399
400 EXCEPTION
401 WHEN Invalid_Ret_Status THEN
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 l_msg_count := FND_MSG_PUB.count_msg;
404
405 IF l_msg_count = 1 and x_msg_data IS NULL THEN
406 PA_INTERFACE_UTILS_PUB.get_messages
407 ( p_encoded => FND_API.G_TRUE
408 ,p_msg_index => 1
409 ,p_msg_count => l_msg_count
410 ,p_msg_data => l_msg_data
411 ,p_data => l_data
412 ,p_msg_index_out => l_msg_index_out);
413
414 x_msg_data := l_data;
415 x_msg_count := l_msg_count;
416 ELSE
417 x_msg_count := l_msg_count;
418 END IF;
419
420 IF l_debug_mode = 'Y' THEN
421 pa_debug.reset_curr_function;
422 END IF;
423
424 rollback to sp;
425 RETURN;
426
427 WHEN OTHERS THEN
428 x_msg_count := 1;
429 x_msg_data := substr(SQLERRM,1,240);
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431 FND_MSG_PUB.add_exc_msg
432 ( p_pkg_name => 'PA_PERF_RULES_PUB'
433 , p_procedure_name => PA_DEBUG.G_Err_Stack
434 , p_error_text => substr(SQLERRM,1,240));
435
436 rollback to sp;
437 RAISE;
438
439 END delete_rule;
440
441 /*==================================================================
442 PROCEDURE
443 validate_rule
444 PURPOSE
445 This procedure validates the performance rule to be inserted .
446 ==================================================================*/
447 PROCEDURE validate_rule(
448 P_RULE_ID IN NUMBER,
449 P_RULE_NAME IN VARCHAR2,
450 P_RULE_TYPE IN VARCHAR2,
451 P_PRECISION IN NUMBER,
452 P_START_DATE_ACTIVE IN DATE,
453 P_END_DATE_ACTIVE IN DATE,
454 P_THRESHOLD_ID IN SYSTEM.PA_NUM_TBL_TYPE,
455 P_THRES_OBJ_ID IN SYSTEM.PA_NUM_TBL_TYPE,
456 P_FROM_VALUE IN SYSTEM.PA_NUM_TBL_TYPE,
457 P_TO_VALUE IN SYSTEM.PA_NUM_TBL_TYPE,
458 P_INDICATOR_CODE IN SYSTEM.pa_varchar2_30_tbl_type,
459 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
461 X_MSG_DATA OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
462 P_WEIGHTING IN SYSTEM.PA_NUM_TBL_TYPE )
463 IS
464 l_debug_mode VARCHAR2(1);
465 l_RETURN_STATUS VARCHAR2(1);
466 l_MSG_COUNT NUMBER;
467 l_MSG_DATA VARCHAR2(2000);
468
469 BEGIN
470
471 FND_MSG_PUB.initialize;
472
473 -- Initialize the Error Stack
474 PA_DEBUG.init_err_stack('PA_PERF_RULES_PUB.validate_rule');
475 x_msg_count := 0;
476 x_msg_data := NULL;
477
478 -- Initialize the return status to success
479 x_return_status := FND_API.G_RET_STS_SUCCESS;
480
481 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
482
483 IF l_debug_mode = 'Y' THEN
484 pa_debug.set_curr_function( p_function => 'PA_PERF_RULES_PUB.validate_rule',
485 p_debug_mode => l_debug_mode );
486 END IF;
487
488 IF l_debug_mode = 'Y' THEN
489 pa_debug.g_err_stage:= 'about to call PA_PERF_RULES_PVT.validate_rule';
490 pa_debug.write(g_module_name,pa_debug.g_err_stage,
491 3);
492 END IF;
493
494 PA_PERF_RULES_PVT.validate_rule(
495 P_RULE_ID => P_RULE_ID ,
496 P_RULE_NAME => P_RULE_NAME ,
497 P_RULE_TYPE => P_RULE_TYPE ,
498 P_PRECISION => P_PRECISION ,
499 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
500 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
501 P_THRESHOLD_ID => P_THRESHOLD_ID ,
502 P_THRES_OBJ_ID => P_THRES_OBJ_ID ,
503 P_FROM_VALUE => P_FROM_VALUE ,
504 P_TO_VALUE => P_TO_VALUE ,
505 P_INDICATOR_CODE => P_INDICATOR_CODE ,
506 X_RETURN_STATUS => l_RETURN_STATUS ,
507 X_MSG_COUNT => l_MSG_COUNT ,
508 X_MSG_DATA => l_MSG_DATA ,
509 P_WEIGHTING => P_WEIGHTING );
510
511 X_RETURN_STATUS := l_RETURN_STATUS ;
512 X_MSG_COUNT := l_MSG_COUNT ;
513 X_MSG_DATA := l_MSG_DATA ;
514
515 END validate_rule;
516 END PA_PERF_RULES_PUB;