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