[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_OBJECT_RULES_PUB
Source
1 PACKAGE BODY PA_PERF_OBJECT_RULES_PUB AS
2 /* $Header: PAPEORPB.pls 120.1 2005/08/19 16:39:00 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_perf_object_rules_pub';
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 Invalid_Ret_Status EXCEPTION;
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_return_status VARCHAR2(1);
35 l_debug_mode VARCHAR2(1);
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 -- set the savepoint
43 savepoint sp;
44 FND_MSG_PUB.initialize;
45 -- Initialize the Error Stack
46 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PUB.create_rule_object');
47 x_msg_count := 0;
48 x_msg_data := NULL;
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
51
52 IF l_debug_mode = 'Y' THEN
53 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PUB.create_rule_object',
54 p_debug_mode => l_debug_mode );
55 END IF;
56
57 IF l_debug_mode = 'Y' THEN
58 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PUB.create_rule_object';
59 pa_debug.write(g_module_name,pa_debug.g_err_stage,
60 l_debug_level3);
61 END IF;
62
63
64 PA_PERF_OBJECT_RULES_PVT.create_rule_object(
65 P_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
66 P_OBJECT_TYPE => P_OBJECT_TYPE,
67 P_OBJECT_ID => P_OBJECT_ID,
68 P_RULE_ID => P_RULE_ID,
69 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
70 P_CREATION_DATE => P_CREATION_DATE,
71 P_CREATED_BY => P_CREATED_BY,
72 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
73 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
74 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
75 X_RETURN_STATUS => l_return_status,
76 X_MSG_COUNT => l_msg_count,
77 X_MSG_DATA => l_msg_data );
78
79 if(l_return_status<>'S') then
80 Raise Invalid_Ret_Status;
81 else
82 X_RETURN_STATUS :=l_return_status;
83 X_MSG_COUNT := l_msg_count;
84 end if;
85 /* The message count will be <> 0 when there is amother rule with the same measure and different calendar .
86 But we should not raise error in that case .So checking for the return status instead . */
87 /* if(l_msg_count<>0) then
88 Raise Invalid_Ret_Status;
89 end if; */
90
91
92 IF l_debug_mode = 'Y' THEN
93 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PUB.create_rule_object';
94 pa_debug.write(g_module_name,pa_debug.g_err_stage,
95 l_debug_level3);
96 pa_debug.reset_curr_function;
97 END IF;
98
99 -- Reset the Error Stack
100 PA_DEBUG.reset_err_stack;
101
102
103 EXCEPTION
104 WHEN Invalid_Ret_Status THEN
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 l_msg_count := FND_MSG_PUB.count_msg;
107
108 IF l_msg_count = 1 and x_msg_data IS NULL THEN
109 PA_INTERFACE_UTILS_PUB.get_messages(
110 p_encoded => FND_API.G_TRUE
111 ,p_msg_index => 1
112 ,p_msg_count => l_msg_count
113 ,p_msg_data => l_msg_data
114 ,p_data => l_data
115 ,p_msg_index_out => l_msg_index_out);
116
117 x_msg_data := l_data;
118 x_msg_count := l_msg_count;
119 ELSE
120 x_msg_count := l_msg_count;
121 END IF;
122
123 IF l_debug_mode = 'Y' THEN
124 pa_debug.reset_curr_function;
125 END IF;
126
127 rollback to sp;
128 RETURN;
129
130 WHEN others THEN
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132 x_msg_count := 1;
133 x_msg_data := SQLERRM;
134
135 FND_MSG_PUB.add_exc_msg(
136 p_pkg_name => 'PA_PERF_OBJECT_RULES_PUB'
137 ,p_procedure_name => 'CREATE_RULE_OBJECT'
138 ,p_error_text => x_msg_data);
139
140 IF l_debug_mode = 'Y' THEN
141 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
142 pa_debug.write(g_module_name,pa_debug.g_err_stage,
143 l_debug_level5);
144 pa_debug.reset_curr_function;
145 END IF;
146 RAISE;
147 END create_rule_object;
148
149 /*==================================================================
150 PROCEDURE
151 update_rule_object
152 PURPOSE
153 This procedure updates a row in the pa_perf_object_rules table.
154 ==================================================================*/
155
156
157 PROCEDURE update_rule_object(
158 P_OBJECT_RULE_ID IN NUMBER,
159 P_OBJECT_TYPE IN VARCHAR2,
160 P_OBJECT_ID IN NUMBER,
161 P_RULE_ID IN NUMBER,
162 P_RECORD_VERSION_NUMBER IN NUMBER,
163 P_CREATION_DATE IN DATE,
164 P_CREATED_BY IN NUMBER,
165 P_LAST_UPDATE_DATE IN DATE,
166 P_LAST_UPDATED_BY IN NUMBER,
167 P_LAST_UPDATE_LOGIN IN NUMBER,
168 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
169 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
170 X_MSG_DATA OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
171 IS
172 Invalid_Ret_Status EXCEPTION;
173 l_msg_count NUMBER := 0;
174 l_data VARCHAR2(2000);
175 l_msg_data VARCHAR2(2000);
176 l_msg_index_out NUMBER;
177 l_return_status VARCHAR2(1);
178 l_debug_mode VARCHAR2(1);
179 l_debug_level2 CONSTANT NUMBER := 2;
180 l_debug_level3 CONSTANT NUMBER := 3;
181 l_debug_level4 CONSTANT NUMBER := 4;
182 l_debug_level5 CONSTANT NUMBER := 5;
183
184 BEGIN
185 -- set the savepoint
186 savepoint sp;
187 FND_MSG_PUB.initialize;
188 -- Initialize the Error Stack
189 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PUB.update_rule_object');
190 x_msg_count := 0;
191 x_msg_data := NULL;
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
194
195 IF l_debug_mode = 'Y' THEN
196 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PUB.update_rule_object',
197 p_debug_mode => l_debug_mode );
198 END IF;
199
200 IF l_debug_mode = 'Y' THEN
201 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PUB.update_rule_object';
202 pa_debug.write(g_module_name,pa_debug.g_err_stage,
203 l_debug_level3);
204 END IF;
205
206
207 PA_PERF_OBJECT_RULES_PVT.update_rule_object(
208 P_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
209 P_OBJECT_TYPE => P_OBJECT_TYPE,
210 P_OBJECT_ID => P_OBJECT_ID,
211 P_RULE_ID => P_RULE_ID,
212 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
213 P_CREATION_DATE => P_CREATION_DATE,
214 P_CREATED_BY => P_CREATED_BY,
215 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
216 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
217 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
218 X_RETURN_STATUS => l_return_status,
219 X_MSG_COUNT => l_msg_count,
220 X_MSG_DATA => l_msg_data );
221
222 if(l_return_status<>'S') then
223 Raise Invalid_Ret_Status;
224 else
225 X_RETURN_STATUS :=l_return_status;
226 X_MSG_COUNT := l_msg_count;
227 end if;
228 /* The message count will be <> 0 when there is amother rule with the same measure and different calendar .
229 But we should not raise error in that case .So checking for the return status instead . */
230 /* if(l_msg_count<>0) then
231 Raise Invalid_Ret_Status;
232 end if; */
233
234
235 IF l_debug_mode = 'Y' THEN
236 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PUB.update_rule_object';
237 pa_debug.write(g_module_name,pa_debug.g_err_stage,
238 l_debug_level3);
239 pa_debug.reset_curr_function;
240 END IF;
241
242 -- Reset the Error Stack
243 PA_DEBUG.reset_err_stack;
244
245
246 EXCEPTION
247 WHEN Invalid_Ret_Status THEN
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 l_msg_count := FND_MSG_PUB.count_msg;
250
251 IF l_msg_count = 1 and x_msg_data IS NULL THEN
252 PA_INTERFACE_UTILS_PUB.get_messages(
253 p_encoded => FND_API.G_TRUE
254 ,p_msg_index => 1
255 ,p_msg_count => l_msg_count
256 ,p_msg_data => l_msg_data
257 ,p_data => l_data
258 ,p_msg_index_out => l_msg_index_out);
259
260 x_msg_data := l_data;
261 x_msg_count := l_msg_count;
262 ELSE
263 x_msg_count := l_msg_count;
264 END IF;
265
266 IF l_debug_mode = 'Y' THEN
267 pa_debug.reset_curr_function;
268 END IF;
269
270 rollback to sp;
271 RETURN;
272
273 WHEN others THEN
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 x_msg_count := 1;
276 x_msg_data := SQLERRM;
277
278 FND_MSG_PUB.add_exc_msg(
279 p_pkg_name => 'PA_PERF_OBJECT_RULES_PUB'
280 ,p_procedure_name => 'UPDATE_RULE_OBJECT'
281 ,p_error_text => x_msg_data);
282
283 IF l_debug_mode = 'Y' THEN
284 pa_debug.g_err_stage:= 'Unexpected Error: '||x_msg_data;
285 pa_debug.write(g_module_name,pa_debug.g_err_stage,
286 l_debug_level5);
287 pa_debug.reset_curr_function;
288 END IF;
289 RAISE;
290 END update_rule_object;
291
292 /*==================================================================
293 PROCEDURE
294 delete_rule_object
295 PURPOSE
296 This procedure deletes a row from the pa_perf_object_rules table.
297 ==================================================================*/
298
299 PROCEDURE delete_rule_object (
300 P_OBJECT_RULE_ID IN NUMBER,
301 P_RECORD_VERSION_NUMBER IN NUMBER,
302 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
303 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
304 X_MSG_DATA OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
305 X_RULE_NAME OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
306 )
307 IS
308 Invalid_Ret_Status EXCEPTION;
309 l_msg_count NUMBER;
310 l_msg_data VARCHAR2(2000);
311 l_data VARCHAR2(2000);
312 l_msg_index_out NUMBER;
313 l_return_status VARCHAR2(1);
314 l_debug_mode VARCHAR2(1);
315 l_debug_level2 CONSTANT NUMBER := 2;
316 l_debug_level3 CONSTANT NUMBER := 3;
317 l_debug_level4 CONSTANT NUMBER := 4;
318 l_debug_level5 CONSTANT NUMBER := 5;
319
320 BEGIN
321 savepoint sp;
322
323
324 FND_MSG_PUB.initialize;
325 -- Initialize the Error Stack
326 PA_DEBUG.init_err_stack('PA_PERF_OBJECT_RULES_PUB.delete_rule_object');
327 x_msg_count := 0;
328 x_msg_data := NULL;
329
330 -- Initialize the return status to success
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 IF l_debug_mode = 'Y' THEN
334 pa_debug.set_curr_function( p_function => 'PA_PERF_OBJECT_RULES_PUB.delete_rule_object',
335 p_debug_mode => l_debug_mode );
336 END IF;
337
338 IF l_debug_mode = 'Y' THEN
339 pa_debug.g_err_stage:= 'Entered PA_PERF_OBJECT_RULES_PUB.delete_rule_object';
340 pa_debug.write(g_module_name,pa_debug.g_err_stage,
341 l_debug_level3);
342 pa_debug.g_err_stage:= 'P_OBJECT_RULE_ID = '|| P_OBJECT_RULE_ID;
343 pa_debug.write(g_module_name,pa_debug.g_err_stage,
344 l_debug_level5);
345 END IF;
346
347
348 IF l_debug_mode = 'Y' THEN
349 pa_debug.g_err_stage:= 'about to call PA_PERF_OBJECT_RULES_PVT.delete_rule_object';
350 pa_debug.write(g_module_name,pa_debug.g_err_stage,
351 l_debug_level3);
352 END IF;
353
354 -- Delete Role
355 PA_PERF_OBJECT_RULES_PVT.delete_rule_object
356 ( P_OBJECT_RULE_ID => P_OBJECT_RULE_ID,
357 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
358 X_RETURN_STATUS => l_return_status,
359 X_MSG_COUNT => l_msg_count,
360 X_MSG_DATA => l_msg_data,
361 X_RULE_NAME => X_RULE_NAME);
362
363 if(l_msg_count<>0) then
364 Raise Invalid_Ret_Status;
365 end if;
366
367 IF l_debug_mode = 'Y' THEN
368 pa_debug.g_err_stage:= 'Exiting PA_PERF_OBJECT_RULES_PUB.delete_rule_object';
369 pa_debug.write(g_module_name,pa_debug.g_err_stage,
370 l_debug_level3);
371 pa_debug.reset_curr_function;
372 END IF;
373
374 -- Reset the Error Stack
375 PA_DEBUG.reset_err_stack;
376
377 EXCEPTION
378 WHEN Invalid_Ret_Status THEN
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 l_msg_count := FND_MSG_PUB.count_msg;
381
382 IF l_msg_count = 1 and x_msg_data IS NULL THEN
383 PA_INTERFACE_UTILS_PUB.get_messages
384 ( p_encoded => FND_API.G_TRUE
385 ,p_msg_index => 1
386 ,p_msg_count => l_msg_count
387 ,p_msg_data => l_msg_data
388 ,p_data => l_data
389 ,p_msg_index_out => l_msg_index_out);
390
391 x_msg_data := l_data;
392 x_msg_count := l_msg_count;
393 ELSE
394 x_msg_count := l_msg_count;
395 END IF;
396
397 IF l_debug_mode = 'Y' THEN
398 pa_debug.reset_curr_function;
399 END IF;
400
401 rollback to sp;
402 RETURN;
403
404 WHEN OTHERS THEN
405 x_msg_count := 1;
406 x_msg_data := substr(SQLERRM,1,240);
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 FND_MSG_PUB.add_exc_msg
409 ( p_pkg_name => 'PA_PERF_OBJECT_RULES_PUB'
410 , p_procedure_name => PA_DEBUG.G_Err_Stack
411 , p_error_text => substr(SQLERRM,1,240));
412
413 rollback to sp;
414 RAISE;
415
416 END delete_rule_object;
417
418 /*==================================================================
419 PROCEDURE
420 validate_rule_object
421 PURPOSE
422 This procedure Checks the rules associated with a project .
423 ==================================================================*/
424
425 PROCEDURE validate_rule_object
426 (P_OBJECT_ID IN NUMBER,
427 P_OBJECT_TYPE IN VARCHAR2,
428 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
429 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
430 X_MSG_DATA OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
431 IS
432
433 BEGIN
434
435 x_msg_count := 0;
436 x_msg_data := NULL;
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 PA_PERF_OBJECT_RULES_PVT.validate_rule_object
440 (P_OBJECT_ID => P_OBJECT_ID ,
441 P_OBJECT_TYPE => P_OBJECT_TYPE,
442 X_RETURN_STATUS => X_RETURN_STATUS ,
443 X_MSG_COUNT => X_MSG_COUNT ,
444 X_MSG_DATA => X_MSG_DATA );
445
446 END VALIDATE_RULE_OBJECT;
447
448 END PA_PERF_OBJECT_RULES_PUB;