[Home] [Help]
PACKAGE BODY: APPS.FUN_RULE_CRITERIA_PUB
Source
1 PACKAGE BODY FUN_RULE_CRITERIA_PUB AS
2 /*$Header: FUNXTMRULRCTPUB.pls 120.0 2005/06/20 04:29:59 ammishra noship $ */
3
4
5 ------------------------------------
6 -- declaration of private procedures
7 ------------------------------------
8
9
10 PROCEDURE do_create_rule_criteria(
11 p_rule_criteria_rec IN OUT NOCOPY RULE_CRITERIA_REC_TYPE,
12 x_criteria_id OUT NOCOPY NUMBER,
13 x_return_status IN OUT NOCOPY VARCHAR2
14 );
15
16 PROCEDURE do_update_rule_criteria(
17 p_rule_criteria_rec IN OUT NOCOPY RULE_CRITERIA_REC_TYPE,
18 p_object_version_number IN OUT NOCOPY NUMBER,
19 x_return_status IN OUT NOCOPY VARCHAR2
20 );
21
22
23 --------------------------------------
24 -- private procedures and functions
25 --------------------------------------
26
27 /*===========================================================================+
28 | PROCEDURE
29 | do_create_rule_criteria
30 |
31 | DESCRIPTION
32 | Creates Rule criteria
33 |
34 | SCOPE - PRIVATE
35 |
36 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
37 |
38 | ARGUMENTS : IN:
39 | OUT:
40 | IN/ OUT:
41 | p_rule_criteria_rec
42 | x_return_status
43 |
44 | RETURNS : NONE
45 |
46 | NOTES
47 |
48 | MODIFICATION HISTORY
49 | 10-Sep-2004 Amulya Mishra Created.
50 +===========================================================================*/
51
52 PROCEDURE do_create_rule_criteria(
53 p_rule_criteria_rec IN OUT NOCOPY RULE_CRITERIA_REC_TYPE,
54 x_criteria_id OUT NOCOPY NUMBER,
55 x_return_status IN OUT NOCOPY VARCHAR2
56 ) IS
57
58 l_rowid rowid;
59 BEGIN
60
61 -- validate the input record
62 FUN_RULE_VALIDATE_PKG.validate_rule_criteria(
63 'C',
64 p_rule_criteria_rec,
65 l_rowid,
66 x_return_status
67 );
68
69 IF x_return_status = fnd_api.g_ret_sts_error THEN
70 RAISE fnd_api.g_exc_error;
71 END IF;
72
73 -- call to table-handler
74 FUN_RULE_CRITERIA_PKG.Insert_Row (
75 X_ROWID =>l_rowid,
76 X_CRITERIA_ID =>p_rule_criteria_rec.criteria_id,
77 X_RULE_DETAIL_ID =>p_rule_criteria_rec.rule_detail_id,
78 X_CRITERIA_PARAM_ID =>p_rule_criteria_rec.criteria_param_id,
79 X_CONDITION =>p_rule_criteria_rec.condition,
80 X_PARAM_VALUE =>p_rule_criteria_rec.param_value,
81 X_CASE_SENSITIVE_FLAG =>p_rule_criteria_rec.case_sensitive_flag,
82 X_CREATED_BY_MODULE =>p_rule_criteria_rec.created_by_module
83 );
84
85 x_criteria_id := p_rule_criteria_rec.criteria_id;
86
87 END;
88
89 /*===========================================================================+
90 | PROCEDURE
91 | do_update_rule_criteria
92 |
93 | DESCRIPTION
94 | Updates Rule Criteria
95 |
96 | SCOPE - PRIVATE
97 |
98 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
99 |
100 | ARGUMENTS : IN:
101 | OUT:
102 | IN/ OUT:
103 | p_rule_criteria_rec
104 | p_object_version_number
105 | x_return_status
106 |
107 | RETURNS : NONE
108 |
109 | NOTES
110 |
111 | MODIFICATION HISTORY
112 |
113 +===========================================================================*/
114
115 PROCEDURE do_update_rule_criteria(
116 p_rule_criteria_rec IN OUT NOCOPY RULE_CRITERIA_REC_TYPE,
117 p_object_version_number IN OUT NOCOPY NUMBER,
118 x_return_status IN OUT NOCOPY VARCHAR2
119 ) IS
120
121 l_object_version_number NUMBER;
122 l_rowid ROWID;
123
124 BEGIN
125
126 BEGIN
127 SELECT OBJECT_VERSION_NUMBER,
128 ROWID
129 INTO l_object_version_number,
130 l_rowid
131 FROM FUN_RULE_CRITERIA
132 WHERE CRITERIA_ID = p_rule_criteria_rec.criteria_id
133 FOR UPDATE NOWAIT;
134
135
136 IF NOT ((p_object_version_number is null and l_object_version_number is null)
137 OR (p_object_version_number = l_object_version_number))
138 THEN
139
140 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_RECORD_CHANGED');
141 FND_MESSAGE.SET_TOKEN('TABLE', 'FUN_RULE_OBJECTS');
142 FND_MSG_PUB.ADD;
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 p_object_version_number := nvl(l_object_version_number, 1) + 1;
147
148 EXCEPTION WHEN NO_DATA_FOUND THEN
149 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_NO_RECORD');
150 FND_MESSAGE.SET_TOKEN('RECORD', 'FUN_RULE_OBJECTS');
151 FND_MESSAGE.SET_TOKEN('VALUE', 'rule_object_name');
152 FND_MSG_PUB.ADD;
153 RAISE FND_API.G_EXC_ERROR;
154 END;
155
156
157 -- call for validations.
158 FUN_RULE_VALIDATE_PKG.validate_rule_criteria(
159 'U',
160 p_rule_criteria_rec,
161 l_rowid,
162 x_return_status
163 );
164
165 IF x_return_status = fnd_api.g_ret_sts_error THEN
166 RAISE fnd_api.g_exc_error;
167 END IF;
168
169 -- call to table-handler
170 FUN_RULE_CRITERIA_PKG.Update_Row (
171 X_CRITERIA_ID =>p_rule_criteria_rec.criteria_id,
172 X_RULE_DETAIL_ID =>p_rule_criteria_rec.rule_detail_id,
173 X_CRITERIA_PARAM_ID =>p_rule_criteria_rec.criteria_param_id,
174 X_CONDITION =>p_rule_criteria_rec.condition,
175 X_PARAM_VALUE =>p_rule_criteria_rec.param_value,
176 X_CASE_SENSITIVE_FLAG =>p_rule_criteria_rec.case_sensitive_flag,
177 X_CREATED_BY_MODULE =>p_rule_criteria_rec.created_by_module
178 );
179
180 END;
181
182 /**
183 * PROCEDURE create_rule_criteria
184 *
185 * DESCRIPTION
186 * Creates Rule criteria.
187 *
188 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
189 *
190 *
191 * ARGUMENTS
192 * IN:
193 * p_init_msg_list Initialize message stack if it is set to
194 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
195 * p_rule_criteria_rec Rule Criteria record.
196 * IN/OUT:
197 * OUT:
198 * x_return_status Return status after the call. The status can
199 * be FND_API.G_RET_STS_SUCCESS (success),
200 * FND_API.G_RET_STS_ERROR (error),
201 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
202 * x_msg_count Number of messages in message stack.
203 * x_msg_data Message text if x_msg_count is 1.
204 *
205 * NOTES
206 *
207 * MODIFICATION HISTORY
208 *
209 * 10-Sep-2004 Amulya Mishra Created.
210 *
211 */
212
213 PROCEDURE create_rule_criteria(
214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
215 p_rule_criteria_rec IN RULE_CRITERIA_REC_TYPE,
216 x_criteria_id OUT NOCOPY NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2
220 ) IS
221
222 l_rule_criteria_rec RULE_CRITERIA_REC_TYPE:= p_rule_criteria_rec;
223
224 BEGIN
225 -- standard start of API savepoint
226 SAVEPOINT create_rule_criteria;
227
228 -- initialize message list if p_init_msg_list is set to TRUE.
229 IF FND_API.to_Boolean(p_init_msg_list) THEN
230 FND_MSG_PUB.initialize;
231 END IF;
232
233 -- initialize API return status to success.
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 -- call to business logic.
237 do_create_rule_criteria(
238 l_rule_criteria_rec,
239 x_criteria_id,
240 x_return_status);
241
242 -- standard call to get message count and if count is 1, get message info.
243 FND_MSG_PUB.Count_And_Get(
244 p_encoded => FND_API.G_FALSE,
245 p_count => x_msg_count,
246 p_data => x_msg_data);
247
248 EXCEPTION
249 WHEN FND_API.G_EXC_ERROR THEN
250 ROLLBACK TO create_rule_criteria;
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 FND_MSG_PUB.Count_And_Get(
253 p_encoded => FND_API.G_FALSE,
254 p_count => x_msg_count,
255 p_data => x_msg_data);
256
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 ROLLBACK TO create_rule_criteria;
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
260 FND_MSG_PUB.Count_And_Get(
261 p_encoded => FND_API.G_FALSE,
262 p_count => x_msg_count,
263 p_data => x_msg_data);
264
265 WHEN OTHERS THEN
266 ROLLBACK TO create_rule_criteria;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
269 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
270 FND_MSG_PUB.ADD;
271 FND_MSG_PUB.Count_And_Get(
272 p_encoded => FND_API.G_FALSE,
273 p_count => x_msg_count,
274 p_data => x_msg_data);
275 END create_rule_criteria;
276
277 /**
278 * PROCEDURE update_rule_criteria
279 *
280 * DESCRIPTION
281 * Updates Rule Criteria
282 *
283 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
284 *
285 * ARGUMENTS
286 * IN:
287 * p_init_msg_list Initialize message stack if it is set to
288 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
289 * p_rule_criteria_rec Rule Criteria record.
290 * IN/OUT:
291 * p_object_version_number Used for locking the being updated record.
292 * OUT:
293 * x_return_status Return status after the call. The status can
294 * be FND_API.G_RET_STS_SUCCESS (success),
295 * FND_API.G_RET_STS_ERROR (error),
296 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
297 * x_msg_count Number of messages in message stack.
298 * x_msg_data Message text if x_msg_count is 1.
299 *
300 * NOTES
301 *
302 * MODIFICATION HISTORY
303 *
304 * 10-Sep-2004 Amulya Mishra Created.
305 *
306 */
307
308 PROCEDURE update_rule_criteria (
309 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
310 p_rule_criteria_rec IN RULE_CRITERIA_REC_TYPE,
311 p_object_version_number IN OUT NOCOPY NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2
315 ) IS
316
317 l_rule_criteria_rec RULE_CRITERIA_REC_TYPE := p_rule_criteria_rec;
318 l_old_rule_criteria_rec RULE_CRITERIA_REC_TYPE;
319
320 BEGIN
321 -- standard start of API savepoint
322 SAVEPOINT update_rule_criteria;
323
324 -- initialize message list if p_init_msg_list is set to TRUE.
325 IF FND_API.to_Boolean(p_init_msg_list) THEN
326 FND_MSG_PUB.initialize;
327 END IF;
328 -- initialize API return status to success.
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330
331
332 -- Get old records. Will be used by business event system.
333 get_rule_criteria_rec (
334 p_criteria_id => l_rule_criteria_rec.criteria_id,
335 p_rule_detail_id => l_rule_criteria_rec.rule_detail_id,
336 x_rule_criteria_rec => l_old_rule_criteria_rec,
337 x_return_status => x_return_status,
338 x_msg_count => x_msg_count,
339 x_msg_data => x_msg_data );
340
341 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
342 RAISE FND_API.G_EXC_ERROR;
343 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346
347 -- call to business logic.
348 do_update_rule_criteria(
349 l_rule_criteria_rec,
350 p_object_version_number,
351 x_return_status);
352
353 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
354
355 -- standard call to get message count and if count is 1, get message info.
356 FND_MSG_PUB.Count_And_Get(
357 p_encoded => FND_API.G_FALSE,
358 p_count => x_msg_count,
359 p_data => x_msg_data);
360 END IF;
361
362 EXCEPTION
363 WHEN FND_API.G_EXC_ERROR THEN
364 ROLLBACK TO update_rule_criteria;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 FND_MSG_PUB.Count_And_Get(
367 p_encoded => FND_API.G_FALSE,
368 p_count => x_msg_count,
369 p_data => x_msg_data);
370
371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
372 ROLLBACK TO update_rule_criteria;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 FND_MSG_PUB.Count_And_Get(
375 p_encoded => FND_API.G_FALSE,
376 p_count => x_msg_count,
377 p_data => x_msg_data);
378
379 WHEN OTHERS THEN
380 ROLLBACK TO update_rule_criteria;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
383 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
384 FND_MSG_PUB.ADD;
385 FND_MSG_PUB.Count_And_Get(
386 p_encoded => FND_API.G_FALSE,
387 p_count => x_msg_count,
388 p_data => x_msg_data);
389 END update_rule_criteria;
390
391
392 /**
393 * PROCEDURE get_rule_criteria_rec
394 *
395 * DESCRIPTION
396 * Gets Rule Criteria record.
397 *
398 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
399 * FUN_RULES_PKG.Select_Row
400 *
401 * ARGUMENTS
402 * IN:
403 * p_init_msg_list Initialize message stack if it is set to
404 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
405 * p_criteria_id Criteria Id
406 * p_RULE_DETAIL_ID Rule Id
407 * IN/OUT:
408 * OUT:
409 * x_rule_criteria_rec Returned Rule Criteria record.
410 * x_return_status Return status after the call. The status can
411 * be FND_API.G_RET_STS_SUCCESS (success),
412 * FND_API.G_RET_STS_ERROR (error),
413 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
414 * x_msg_count Number of messages in message stack.
415 * x_msg_data Message text if x_msg_count is 1.
416 *
417 * NOTES
418 *
419 * MODIFICATION HISTORY
420 *
421 * 10-Sep-2004 Amulya Mishra Created.
422 *
423 */
424
425 PROCEDURE get_rule_criteria_rec (
426 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
427 p_criteria_id IN NUMBER,
428 p_rule_detail_id IN NUMBER,
429 x_rule_criteria_rec OUT NOCOPY RULE_CRITERIA_REC_TYPE,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2
433 ) IS
434
435 BEGIN
436 --Initialize message list if p_init_msg_list is set to TRUE.
437 IF FND_API.to_Boolean(p_init_msg_list) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440
441 --Initialize API return status to success.
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443
444 --Check whether primary key has been passed in.
445 IF p_criteria_id IS NULL OR
446 p_criteria_id = FND_API.G_MISS_NUM THEN
447 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_MISSING_COLUMN' );
448 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'criteria_id' );
449 FND_MSG_PUB.ADD;
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452
453 x_rule_criteria_rec.criteria_id := p_criteria_id;
454 x_rule_criteria_rec.rule_detail_id := p_rule_detail_id;
455
456
457
458 FUN_RULE_CRITERIA_PKG.Select_Row (
459 X_CRITERIA_ID =>x_rule_criteria_rec.criteria_id,
460 X_RULE_DETAIL_ID =>x_rule_criteria_rec.rule_detail_id,
461 X_CRITERIA_PARAM_ID =>x_rule_criteria_rec.criteria_param_id,
462 X_CONDITION =>x_rule_criteria_rec.condition,
463 X_PARAM_VALUE =>x_rule_criteria_rec.param_value,
464 X_CASE_SENSITIVE_FLAG =>x_rule_criteria_rec.case_sensitive_flag,
465 X_CREATED_BY_MODULE =>x_rule_criteria_rec.created_by_module
466 );
467
468
469 --Standard call to get message count and if count is 1, get message info.
470 FND_MSG_PUB.Count_And_Get(
471 p_encoded => FND_API.G_FALSE,
472 p_count => x_msg_count,
473 p_data => x_msg_data );
474
475 EXCEPTION
476 WHEN FND_API.G_EXC_ERROR THEN
477 x_return_status := FND_API.G_RET_STS_ERROR;
478
479 FND_MSG_PUB.Count_And_Get(
480 p_encoded => FND_API.G_FALSE,
481 p_count => x_msg_count,
482 p_data => x_msg_data );
483
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 FND_MSG_PUB.Count_And_Get(
487 p_encoded => FND_API.G_FALSE,
488 p_count => x_msg_count,
489 p_data => x_msg_data );
490
491 WHEN OTHERS THEN
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_OTHERS_EXCEP' );
494 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
495 FND_MSG_PUB.ADD;
496
497 FND_MSG_PUB.Count_And_Get(
498 p_encoded => FND_API.G_FALSE,
499 p_count => x_msg_count,
500 p_data => x_msg_data );
501
502 END get_rule_criteria_rec;
503
504
505 /**
506 * PROCEDURE delete_rule_criteria
507 *
508 * DESCRIPTION
509 * Deletes Rule Criteria.
510 *
511 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
512 *
513 *
514 * ARGUMENTS
515 * IN:
516 * p_init_msg_list Initialize message stack if it is set to
517 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
518 * p_criteria_param_name Criteria Param Name.
519 * p_rule_detail_id Rule Detail Id
520 * IN/OUT:
521 * OUT:
522 * x_return_status Return status after the call. The status can
523 * be FND_API.G_RET_STS_SUCCESS (success),
524 * FND_API.G_RET_STS_ERROR (error),
525 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
526 * x_msg_count Number of messages in message stack.
527 * x_msg_data Message text if x_msg_count is 1.
528 *
529 * NOTES
530 *
531 * MODIFICATION HISTORY
532 *
533 * 10-Sep-2004 Amulya Mishra Created.
534 *
535 */
536
537 PROCEDURE delete_rule_criteria(
538 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
539 p_criteria_id IN NUMBER,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2
543 ) IS
544
545
546 BEGIN
547
548 -- standard start of API savepoint
549 SAVEPOINT delete_rule_criteria;
550
551 -- initialize message list if p_init_msg_list is set to TRUE.
552 IF FND_API.to_Boolean(p_init_msg_list) THEN
553 FND_MSG_PUB.initialize;
554 END IF;
555
556 -- initialize API return status to success.
557 x_return_status := FND_API.G_RET_STS_SUCCESS;
558
559 -- call to table-handler.
560 FUN_RULE_CRITERIA_PKG.Delete_Row (
561 X_CRITERIA_ID =>p_criteria_id
562 );
563
564
565 -- standard call to get message count and if count is 1, get message info.
566 FND_MSG_PUB.Count_And_Get(
567 p_encoded => FND_API.G_FALSE,
568 p_count => x_msg_count,
569 p_data => x_msg_data);
570
571
572 EXCEPTION
573 WHEN FND_API.G_EXC_ERROR THEN
574 ROLLBACK TO delete_rule_criteria;
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 FND_MSG_PUB.Count_And_Get(
577 p_encoded => FND_API.G_FALSE,
578 p_count => x_msg_count,
579 p_data => x_msg_data);
580
581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
582 ROLLBACK TO delete_rule_criteria;
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 FND_MSG_PUB.Count_And_Get(
585 p_encoded => FND_API.G_FALSE,
586 p_count => x_msg_count,
587 p_data => x_msg_data);
588
589 WHEN OTHERS THEN
590 ROLLBACK TO delete_rule_criteria;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
593 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
594 FND_MSG_PUB.ADD;
595 FND_MSG_PUB.Count_And_Get(
596 p_encoded => FND_API.G_FALSE,
597 p_count => x_msg_count,
598 p_data => x_msg_data);
599 END delete_rule_criteria;
600
601 END FUN_RULE_CRITERIA_PUB;