[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_THRESHOLDS_PUB
Source
1 PACKAGE BODY PA_PERF_THRESHOLDS_PUB AS
2 /* $Header: PAPETHPB.pls 120.1 2005/08/19 16:39:51 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_perf_thresholds_pub';
5
6 /*==================================================================
7 PROCEDURE
8 create_rule_det
9 PURPOSE
10 This procedure inserts a row into the pa_perf_thresholds table.
11 ==================================================================*/
12
13
14 PROCEDURE create_rule_det(
15 P_THRESHOLD_ID IN NUMBER,
16 P_RULE_TYPE IN VARCHAR2,
17 P_THRES_OBJ_ID IN NUMBER,
18 P_FROM_VALUE IN NUMBER,
19 P_TO_VALUE IN NUMBER,
20 P_INDICATOR_CODE IN VARCHAR2,
21 P_EXCEPTION_FLAG IN VARCHAR2,
22 P_WEIGHTING IN NUMBER,
23 P_ACCESS_LIST_ID IN NUMBER,
24 P_RECORD_VERSION_NUMBER IN NUMBER,
25 P_CREATION_DATE IN DATE,
26 P_CREATED_BY IN NUMBER,
27 P_LAST_UPDATE_DATE IN DATE,
28 P_LAST_UPDATED_BY IN NUMBER,
29 P_LAST_UPDATE_LOGIN IN NUMBER,
30 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
33 IS
34 Invalid_Ret_Status EXCEPTION;
35 l_msg_count NUMBER := 0;
36 l_data VARCHAR2(2000);
37 l_msg_data VARCHAR2(2000);
38 l_msg_index_out NUMBER;
39 l_return_status VARCHAR2(1);
40 l_debug_mode VARCHAR2(1);
41 l_debug_level2 CONSTANT NUMBER := 2;
42 l_debug_level3 CONSTANT NUMBER := 3;
43 l_debug_level4 CONSTANT NUMBER := 4;
44 l_debug_level5 CONSTANT NUMBER := 5;
45
46 BEGIN
47
48 -- Initialize the Error Stack
49 PA_DEBUG.init_err_stack('PA_PERF_THRESHOLDS_PUB.create_rule_det');
50 x_msg_count := 0;
51 x_msg_data := NULL;
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
54
55 IF l_debug_mode = 'Y' THEN
56 pa_debug.set_curr_function( p_function => 'PA_PERF_THRESHOLDS_PUB.create_rule_det',
57 p_debug_mode => l_debug_mode );
58 END IF;
59
60 IF l_debug_mode = 'Y' THEN
61 pa_debug.g_err_stage:= 'Entered PA_PERF_THRESHOLDS_PUB.create_rule_det';
62 pa_debug.write(g_module_name,pa_debug.g_err_stage,
63 l_debug_level3);
64 END IF;
65
66
67 PA_PERF_THRESHOLDS_PVT.create_rule_det(
68 P_THRESHOLD_ID => P_THRESHOLD_ID,
69 P_RULE_TYPE => P_RULE_TYPE,
70 P_THRES_OBJ_ID => P_THRES_OBJ_ID,
71 P_FROM_VALUE => P_FROM_VALUE,
72 P_TO_VALUE => P_TO_VALUE,
73 P_INDICATOR_CODE => P_INDICATOR_CODE,
74 P_EXCEPTION_FLAG => P_EXCEPTION_FLAG,
75 P_WEIGHTING => P_WEIGHTING,
76 P_ACCESS_LIST_ID => P_ACCESS_LIST_ID,
77 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
78 P_CREATION_DATE => P_CREATION_DATE,
79 P_CREATED_BY => P_CREATED_BY,
80 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
81 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
82 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
83 X_RETURN_STATUS => l_return_status,
84 X_MSG_COUNT => l_msg_count,
85 X_MSG_DATA => l_msg_data );
86
87
88 if(l_msg_count<>0) then
89 Raise Invalid_Ret_Status;
90 end if;
91
92
93 IF l_debug_mode = 'Y' THEN
94 pa_debug.g_err_stage:= 'Exiting PA_PERF_THRESHOLDS_PUB.create_rule_det';
95 pa_debug.write(g_module_name,pa_debug.g_err_stage,
96 l_debug_level3);
97 pa_debug.reset_curr_function;
98 END IF;
99
100 -- Reset the Error Stack
101 PA_DEBUG.reset_err_stack;
102
103
104 EXCEPTION
105 WHEN Invalid_Ret_Status THEN
106 x_return_status := FND_API.G_RET_STS_ERROR;
107 l_msg_count := FND_MSG_PUB.count_msg;
108
109 IF l_msg_count = 1 and x_msg_data IS NULL THEN
110 PA_INTERFACE_UTILS_PUB.get_messages(
111 p_encoded => FND_API.G_TRUE
112 ,p_msg_index => 1
113 ,p_msg_count => l_msg_count
114 ,p_msg_data => l_msg_data
115 ,p_data => l_data
116 ,p_msg_index_out => l_msg_index_out);
117
118 x_msg_data := l_data;
119 x_msg_count := l_msg_count;
120 ELSE
121 x_msg_count := l_msg_count;
122 END IF;
123
124 IF l_debug_mode = 'Y' THEN
125 pa_debug.reset_curr_function;
126 END IF;
127
128 rollback to sp;
129 RETURN;
130
131 WHEN others THEN
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 x_msg_count := 1;
134 x_msg_data := SQLERRM;
135
136 FND_MSG_PUB.add_exc_msg(
137 p_pkg_name => 'PA_PERF_THRESHOLDS_PUB'
138 ,p_procedure_name => 'CREATE_RULE_DET'
139 ,p_error_text => x_msg_data);
140
141 IF l_debug_mode = 'Y' THEN
142 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
143 pa_debug.write(g_module_name,pa_debug.g_err_stage,
144 l_debug_level5);
145 pa_debug.reset_curr_function;
146 END IF;
147 RAISE;
148 END create_rule_det;
149
150 /*==================================================================
151 PROCEDURE
152 update_rule_det
153 PURPOSE
154 This procedure updates a row into the pa_perf_thresholds table.
155 ==================================================================*/
156
157 PROCEDURE update_rule_det(
158 P_THRESHOLD_ID IN NUMBER,
159 P_RULE_TYPE IN VARCHAR2,
160 P_THRES_OBJ_ID IN NUMBER,
161 P_FROM_VALUE IN NUMBER,
162 P_TO_VALUE IN NUMBER,
163 P_INDICATOR_CODE IN VARCHAR2,
164 P_EXCEPTION_FLAG IN VARCHAR2,
165 P_WEIGHTING IN NUMBER,
166 P_ACCESS_LIST_ID IN NUMBER,
167 P_RECORD_VERSION_NUMBER IN NUMBER,
168 P_LAST_UPDATE_DATE IN DATE,
169 P_LAST_UPDATED_BY IN NUMBER,
170 P_LAST_UPDATE_LOGIN IN NUMBER,
171 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
172 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
173 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
174 IS
175 Invalid_Ret_Status EXCEPTION;
176 l_msg_count NUMBER := 0;
177 l_data VARCHAR2(2000);
178 l_msg_data VARCHAR2(2000);
179 l_msg_index_out NUMBER;
180 l_return_status VARCHAR2(1);
181 l_debug_mode VARCHAR2(1);
182 l_rowid VARCHAR2(255);
183 l_debug_level2 CONSTANT NUMBER := 2;
184 l_debug_level3 CONSTANT NUMBER := 3;
185 l_debug_level4 CONSTANT NUMBER := 4;
186 l_debug_level5 CONSTANT NUMBER := 5;
187
188 BEGIN
189
190 savepoint sp;
191 -- Initialize the Error Stack
192 PA_DEBUG.init_err_stack('PA_PERF_THRESHOLDS_PUB.Update_Rule_Det');
193 x_msg_count := 0;
194 x_msg_data := NULL;
195
196 -- Initialize the return status to success
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
199 IF l_debug_mode = 'Y' THEN
200 pa_debug.set_curr_function( p_function => 'PA_PERF_THRESHOLDS_PUB.update_rule_det',
201 p_debug_mode => l_debug_mode );
202 END IF;
203
204 IF l_debug_mode = 'Y' THEN
205 pa_debug.g_err_stage:= 'Entered PA_PERF_THRESHOLDS_PUB.update_rule';
206 pa_debug.write(g_module_name,pa_debug.g_err_stage,
207 l_debug_level3);
208
209 pa_debug.g_err_stage:= 'P_THRESHOLD_ID = '|| P_THRESHOLD_ID;
210 pa_debug.write(g_module_name,pa_debug.g_err_stage,
211 l_debug_level5);
212
213 pa_debug.g_err_stage:= 'P_RULE_TYPE = '|| P_RULE_TYPE;
214 pa_debug.write(g_module_name,pa_debug.g_err_stage,
215 l_debug_level5);
216 END IF;
217
218
219 IF l_debug_mode = 'Y' THEN
220 pa_debug.g_err_stage:= 'about to call PA_PERF_THRESHOLDS_PVT.UPDATE_RULE_DET';
221 pa_debug.write(g_module_name,pa_debug.g_err_stage,
222 l_debug_level3);
223 END IF;
224
225 PA_PERF_THRESHOLDS_PVT.update_rule_det(
226 P_THRESHOLD_ID => P_THRESHOLD_ID,
227 P_RULE_TYPE => P_RULE_TYPE,
228 P_THRES_OBJ_ID => P_THRES_OBJ_ID,
229 P_FROM_VALUE => P_FROM_VALUE,
230 P_TO_VALUE => P_TO_VALUE,
231 P_INDICATOR_CODE => P_INDICATOR_CODE,
232 P_EXCEPTION_FLAG => P_EXCEPTION_FLAG,
233 P_WEIGHTING => P_WEIGHTING,
234 P_ACCESS_LIST_ID => P_ACCESS_LIST_ID,
235 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
236 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
237 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
238 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
239 X_RETURN_STATUS => l_return_status,
240 X_MSG_COUNT => l_msg_count,
241 X_MSG_DATA => l_msg_data );
242
243 if(l_msg_count<>0) then
244 Raise Invalid_Ret_Status;
245 end if;
246
247 IF l_debug_mode = 'Y' THEN
248 pa_debug.g_err_stage:= 'Exiting PA_PERF_THRESHOLDS_PUB.update_rule_det';
249 pa_debug.write(g_module_name,pa_debug.g_err_stage,
250 l_debug_level3);
251 pa_debug.reset_curr_function;
252 END IF;
253
254 -- Reset the Error Stack
255 PA_DEBUG.reset_err_stack;
256
257 EXCEPTION
258 WHEN Invalid_Ret_Status THEN
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 l_msg_count := FND_MSG_PUB.count_msg;
261
262 IF l_msg_count = 1 and x_msg_data IS NULL THEN
263 PA_INTERFACE_UTILS_PUB.get_messages(
264 p_encoded => FND_API.G_TRUE
265 ,p_msg_index => 1
266 ,p_msg_count => l_msg_count
267 ,p_msg_data => l_msg_data
268 ,p_data => l_data
269 ,p_msg_index_out => l_msg_index_out);
270
271 x_msg_data := l_data;
272 x_msg_count := l_msg_count;
273 ELSE
274 x_msg_count := l_msg_count;
275 END IF;
276
277 IF l_debug_mode = 'Y' THEN
278 pa_debug.reset_curr_function;
279 END IF;
280
281 rollback to sp;
282 RETURN;
283
284 WHEN OTHERS THEN
285 x_msg_count := 1;
286 x_msg_data := substr(SQLERRM,1,240);
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 FND_MSG_PUB.add_exc_msg (
289 p_pkg_name => 'PA_PERF_THRESHOLDS_PUB'
290 , p_procedure_name => PA_DEBUG.G_Err_Stack
291 , p_error_text => substr(SQLERRM,1,240));
292
293 rollback to sp;
294 RAISE;
295
296 END update_rule_det;
297
298 /*==================================================================
299 PROCEDURE
300 delete_rule_det
301 PURPOSE
302 This procedure deletes a row from the pa_perf_thresholds table.
303 ==================================================================*/
304
305 PROCEDURE delete_rule_det (
306 P_THRESHOLD_ID IN NUMBER,
307 P_RECORD_VERSION_NUMBER IN NUMBER,
308 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
309 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
310 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
311 )
312 IS
313 Invalid_Ret_Status EXCEPTION;
314 l_msg_count NUMBER;
315 l_msg_data VARCHAR2(2000);
316 l_data VARCHAR2(2000);
317 l_msg_index_out NUMBER;
318 l_return_status VARCHAR2(1);
319 l_debug_mode VARCHAR2(1);
320 l_debug_level2 CONSTANT NUMBER := 2;
321 l_debug_level3 CONSTANT NUMBER := 3;
322 l_debug_level4 CONSTANT NUMBER := 4;
323 l_debug_level5 CONSTANT NUMBER := 5;
324
325 BEGIN
326 savepoint sp;
327
328
329 -- Initialize the Error Stack
330 PA_DEBUG.init_err_stack('PA_PERF_THRESHOLDS_PUB.Delete_Rule_Det');
331 x_msg_count := 0;
332 x_msg_data := NULL;
333
334 -- Initialize the return status to success
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336
337 IF l_debug_mode = 'Y' THEN
338 pa_debug.set_curr_function( p_function => 'PA_PERF_THRESHOLDS_PUB.delete_rule_det',
339 p_debug_mode => l_debug_mode );
340 END IF;
341
342 IF l_debug_mode = 'Y' THEN
343 pa_debug.g_err_stage:= 'Entered PA_PERF_THRESHOLDS_PUB.delete_rule_det';
344 pa_debug.write(g_module_name,pa_debug.g_err_stage,
345 l_debug_level3);
346 pa_debug.g_err_stage:= 'P_THRESHOLD_ID = '|| P_THRESHOLD_ID;
347 pa_debug.write(g_module_name,pa_debug.g_err_stage,
348 l_debug_level5);
349 END IF;
350
351
352 IF l_debug_mode = 'Y' THEN
353 pa_debug.g_err_stage:= 'about to call PA_PERF_THRESHOLDS_PVT.delete_rule_det';
354 pa_debug.write(g_module_name,pa_debug.g_err_stage,
355 l_debug_level3);
356 END IF;
357
358 -- Delete Role
359 PA_PERF_THRESHOLDS_PVT.delete_rule_det
360 ( P_THRESHOLD_ID => P_THRESHOLD_ID,
361 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
362 X_RETURN_STATUS => l_return_status,
363 X_MSG_COUNT => l_msg_count,
364 X_MSG_DATA => l_msg_data );
365
366 if(l_msg_count<>0) then
367 Raise Invalid_Ret_Status;
368 end if;
369
370 IF l_debug_mode = 'Y' THEN
371 pa_debug.g_err_stage:= 'Exiting PA_PERF_THRESHOLDS_PUB.delete_rule_det';
372 pa_debug.write(g_module_name,pa_debug.g_err_stage,
373 l_debug_level3);
374 pa_debug.reset_curr_function;
375 END IF;
376
377 -- Reset the Error Stack
378 PA_DEBUG.reset_err_stack;
379
380 EXCEPTION
381 WHEN Invalid_Ret_Status THEN
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 l_msg_count := FND_MSG_PUB.count_msg;
384
385 IF l_msg_count = 1 and x_msg_data IS NULL THEN
386 PA_INTERFACE_UTILS_PUB.get_messages
387 ( p_encoded => FND_API.G_TRUE
388 ,p_msg_index => 1
389 ,p_msg_count => l_msg_count
390 ,p_msg_data => l_msg_data
391 ,p_data => l_data
392 ,p_msg_index_out => l_msg_index_out);
393
394 x_msg_data := l_data;
395 x_msg_count := l_msg_count;
396 ELSE
397 x_msg_count := l_msg_count;
398 END IF;
399
400 IF l_debug_mode = 'Y' THEN
401 pa_debug.reset_curr_function;
402 END IF;
403
404 rollback to sp;
405 RETURN;
406
407 WHEN OTHERS THEN
408 x_msg_count := 1;
409 x_msg_data := substr(SQLERRM,1,240);
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 FND_MSG_PUB.add_exc_msg
412 ( p_pkg_name => 'PA_PERF_THRESHOLDS_PUB'
413 , p_procedure_name => PA_DEBUG.G_Err_Stack
414 , p_error_text => substr(SQLERRM,1,240));
415
416 rollback to sp;
417 RAISE;
418
419 END delete_rule_det;
420
421
422
423 END PA_PERF_THRESHOLDS_PUB;