1 PACKAGE BODY FUN_RULE_OBJECTS_PUB AS
2 /*$Header: FUNXTMRULROBPUB.pls 120.8.12010000.2 2008/08/06 07:44:30 makansal ship $ */
3
4
5 ------------------------------------
6 -- declaration of private procedures
7 ------------------------------------
8
9
10 PROCEDURE do_create_rule_object(
11 p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
12 x_rule_object_id OUT NOCOPY NUMBER,
13 x_return_status IN OUT NOCOPY VARCHAR2
14 );
15
16 PROCEDURE do_create_rule_object_instance(
17 p_application_id IN NUMBER,
18 p_rule_object_name IN VARCHAR2,
19 p_instance_label IN VARCHAR2,
20 p_org_id IN NUMBER,
21 x_rule_object_id OUT NOCOPY NUMBER,
22 x_return_status IN OUT NOCOPY VARCHAR2
23 );
24
25 PROCEDURE do_update_rule_object(
26 p_update_instance IN VARCHAR2 DEFAULT 'N',
27 p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
28 p_object_version_number IN OUT NOCOPY NUMBER,
29 x_return_status IN OUT NOCOPY VARCHAR2
30 );
31
32 --------------------------------------
33 -- private procedures and functions
34 --------------------------------------
35
36 /*===========================================================================+
37 | PROCEDURE
38 | do_create_rule_object
39 |
40 | DESCRIPTION
41 | Creates user customizable objects
42 |
43 | SCOPE - PRIVATE
44 |
45 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
46 |
47 | ARGUMENTS : IN:
48 | OUT:
49 | IN/ OUT:
50 | p_rule_object_rec
51 | x_return_status
52 |
53 | RETURNS : NONE
54 |
55 | NOTES
56 |
57 | MODIFICATION HISTORY
58 | 10-Sep-2004 Amulya Mishra Created.
59 +===========================================================================*/
60
61 PROCEDURE do_create_rule_object(
62 p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
63 x_rule_object_id OUT NOCOPY NUMBER,
64 x_return_status IN OUT NOCOPY VARCHAR2
65 ) IS
66
67 l_rowid rowid:= NULL;
68
69 BEGIN
70 -- validate the input record
71 FUN_RULE_VALIDATE_PKG.validate_rule_objects(
72 'C',
73 p_rule_object_rec,
74 l_rowid,
75 x_return_status
76 );
77
78 IF x_return_status = fnd_api.g_ret_sts_error THEN
79 RAISE fnd_api.g_exc_error;
80 END IF;
81
82 /********************************************************************************
83 Dont pass X_INSTANCE_LABEL,X_PARENT_RULE_OBJECT_ID,X_ORG_ID
84 on in this API the Rule Object Instance Information. Because, create_rule_object
85 API will not be used to create the Rule Object Instance.
86 *********************************************************************************/
87
88 FUN_RULE_OBJECTS_PKG.Insert_Row (
89 X_ROWID =>l_rowid,
90 X_RULE_OBJECT_ID =>p_rule_object_rec.rule_object_id,
91 X_APPLICATION_ID =>p_rule_object_rec.application_id,
92 X_RULE_OBJECT_NAME =>p_rule_object_rec.rule_object_name,
93 X_RESULT_TYPE =>p_rule_object_rec.result_type,
94 X_REQUIRED_FLAG =>p_rule_object_rec.required_flag,
95 X_USE_DEFAULT_VALUE_FLAG =>p_rule_object_rec.use_default_value_flag,
96 X_DEFAULT_APPLICATION_ID =>p_rule_object_rec.default_application_id,
97 X_DEFAULT_VALUE =>p_rule_object_rec.default_value,
98 X_FLEX_VALUE_SET_ID =>p_rule_object_rec.flex_value_set_id,
99 X_FLEXFIELD_NAME =>p_rule_object_rec.flexfield_name,
100 X_FLEXFIELD_APP_SHORT_NAME =>p_rule_object_rec.flexfield_app_short_name,
101 X_MULTI_RULE_RESULT_FLAG =>p_rule_object_rec.multi_rule_result_flag,
102 X_CREATED_BY_MODULE =>p_rule_object_rec.created_by_module,
103 X_USER_RULE_OBJECT_NAME =>p_rule_object_rec.user_rule_object_name,
104 X_DESCRIPTION =>p_rule_object_rec.description,
105 X_USE_INSTANCE_FLAG =>p_rule_object_rec.use_instance_flag
106 );
107
108 END;
109
110 /*===========================================================================+
111 | PROCEDURE
112 | do_create_rule_object_instance
113 |
114 | DESCRIPTION
115 | Creates Rule object Instance
116 |
117 | SCOPE - PRIVATE
118 |
119 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
120 |
121 | ARGUMENTS : IN:
122 | OUT:
123 | IN/ OUT:
124 | p_rule_object_rec
125 | x_return_status
126 |
127 | RETURNS : NONE
128 |
129 | NOTES
130 |
131 | MODIFICATION HISTORY
132 | 27-DEC-2005 Amulya Mishra Created.
133 +===========================================================================*/
134
135 PROCEDURE do_create_rule_object_instance(
136 p_application_id IN NUMBER,
137 p_rule_object_name IN VARCHAR2,
138 p_instance_label IN VARCHAR2,
139 p_org_id IN NUMBER,
140 x_rule_object_id OUT NOCOPY NUMBER,
141 x_return_status IN OUT NOCOPY VARCHAR2
142 ) IS
143
144 l_rowid rowid:= NULL;
145 l_orig_rule_object_rec RULE_OBJECTS_REC_TYPE;
146 l_rule_object_instance_rec RULE_OBJECTS_REC_TYPE;
147 l_rule_object_id FUN_RULE_OBJECTS_B.RULE_OBJECT_ID%TYPE;
148
149 x_msg_count number;
150 x_msg_data varchar2(2000);
151
152 BEGIN
153
154 -- Get old records. Will be used by logic to create Rule Object Instance.
155 -- Before creating the Rule Object Instance for a Rule Object Name, we
156 -- need to check if the original rule object instance exists or not.
157 -- So explicitly pass p_instance_label as NULL.
158
159
160 get_rule_object_rec (
161 p_rule_object_name => p_rule_object_name,
162 p_application_id => P_application_id,
163 p_instance_label => NULL,
164 p_org_id => NULL,
165 x_rule_object_rec => l_orig_rule_object_rec,
166 x_return_status => x_return_status,
167 x_msg_count => x_msg_count,
168 x_msg_data => x_msg_data );
169
170
171 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
172 RAISE FND_API.G_EXC_ERROR;
173 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 --Validate if the parent rule object has use_instance_flag as Y or not.
178 --If not, then we will raise user defined exception and come out.
179
180 IF (NOT UPPER(l_orig_rule_object_rec.use_instance_flag) = 'Y') THEN
181 fnd_message.set_name('FUN', 'FUN_RULE_NO_CREATE_ROB_INST');
182 fnd_msg_pub.add;
183 x_return_status := fnd_api.g_ret_sts_error;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187
188 --If we have reached here, that means we have got the Rule Object Name for which
189 --we want to create Rule Object Instance.
190
191 --Assign related values for the instance.
192 l_rule_object_instance_rec := l_orig_rule_object_rec;
193
194 l_rule_object_instance_rec.instance_label := p_instance_label;
195
196 --Derive the Rule_Object_Id of original Rule Object and set it as
197 --parent_rule_object_id of the instance.
198
199 l_rule_object_instance_rec.parent_rule_object_id := l_orig_rule_object_rec.rule_object_id;
200 l_rule_object_instance_rec.org_id := p_org_id;
201
202
203 -- validate the input record
204 FUN_RULE_VALIDATE_PKG.validate_rule_object_instance(
205 'C',
206 l_rule_object_instance_rec,
207 l_rowid,
208 x_return_status
209 );
210
211 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
212 RAISE FND_API.G_EXC_ERROR;
213 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END IF;
216
217 FUN_RULE_OBJECTS_PKG.Insert_Row (
218 X_ROWID =>l_rowid,
219 X_RULE_OBJECT_ID =>NULL,
220 X_APPLICATION_ID =>l_rule_object_instance_rec.application_id,
221 X_RULE_OBJECT_NAME =>l_rule_object_instance_rec.rule_object_name,
222 X_RESULT_TYPE =>l_rule_object_instance_rec.result_type,
223 X_REQUIRED_FLAG =>l_rule_object_instance_rec.required_flag,
224 X_USE_DEFAULT_VALUE_FLAG =>l_rule_object_instance_rec.use_default_value_flag,
225 X_DEFAULT_APPLICATION_ID =>l_rule_object_instance_rec.default_application_id,
226 X_DEFAULT_VALUE =>l_rule_object_instance_rec.default_value,
227 X_FLEX_VALUE_SET_ID =>l_rule_object_instance_rec.flex_value_set_id,
228 X_FLEXFIELD_NAME =>l_rule_object_instance_rec.flexfield_name,
229 X_FLEXFIELD_APP_SHORT_NAME =>l_rule_object_instance_rec.flexfield_app_short_name,
230 X_MULTI_RULE_RESULT_FLAG =>l_rule_object_instance_rec.multi_rule_result_flag,
231 X_CREATED_BY_MODULE =>l_rule_object_instance_rec.created_by_module,
232 X_USER_RULE_OBJECT_NAME =>l_rule_object_instance_rec.user_rule_object_name,
233 X_DESCRIPTION =>l_rule_object_instance_rec.description,
234 X_USE_INSTANCE_FLAG =>l_rule_object_instance_rec.use_instance_flag,
235 X_INSTANCE_LABEL =>l_rule_object_instance_rec.instance_label,
236 X_PARENT_RULE_OBJECT_ID =>l_rule_object_instance_rec.parent_rule_object_id,
237 X_ORG_ID =>l_rule_object_instance_rec.org_id
238 );
239 --Make sure the Rule Object Instance gets created and then select the Rule Object Id
240 --of the new Rule Object Instance.
241
242 BEGIN
243 SELECT RULE_OBJECT_ID INTO X_RULE_OBJECT_ID
244 FROM FUN_RULE_OBJECTS_B WHERE RULE_OBJECT_NAME = p_rule_object_name
245 AND APPLICATION_ID = p_application_id
246 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
247 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
248 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
249 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
250 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 fnd_message.set_name('FUN', 'FUN_RULE_API_INVALID_ROB');
255 fnd_msg_pub.add;
256 x_return_status := fnd_api.g_ret_sts_error;
257 END;
258
259 /**************************************************************************************
260 Since the newly created Rule Object Instance will have the same criteria parameters
261 in FUN_RULE_CRIT_PARAMS_B/_TL table, we need to
262 Create the Criteria Parameters for the Rule Object Instance with Rule_Object_id
263 as X_RULE_OBJECT_ID.
264 **************************************************************************************/
265 FUN_RULE_CRIT_PARAMS_PUB.CREATE_DUP_RULE_CRIT_PARAMS(
266 p_init_msg_list => FND_API.G_FALSE,
267 p_from_rule_object_id => l_orig_rule_object_rec.rule_object_id,
268 p_to_rule_object_id => X_RULE_OBJECT_ID,
269 x_return_status => x_return_status,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data);
272
273 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
274 RAISE FND_API.G_EXC_ERROR;
275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278
279
280 END do_create_rule_object_instance;
281
282
283 /*===========================================================================+
284 | PROCEDURE
285 | do_update_rule_object
286 |
287 | DESCRIPTION
288 | Updates user customizable objects
289 |
290 | SCOPE - PRIVATE
291 |
292 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
293 |
294 | ARGUMENTS : IN:
295 | OUT:
296 | IN/ OUT:
297 | p_rule_object_rec
298 | p_object_version_number
299 | x_return_status
300 |
301 | RETURNS : NONE
302 |
303 | NOTES
304 |
305 | MODIFICATION HISTORY
306 |
307 +===========================================================================*/
308
309 PROCEDURE do_update_rule_object(
310 p_update_instance IN VARCHAR2 DEFAULT 'N',
311 p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
312 p_object_version_number IN OUT NOCOPY NUMBER,
313 x_return_status IN OUT NOCOPY VARCHAR2
314 ) IS
315
316 l_object_version_number NUMBER;
317 l_rowid ROWID;
318 BEGIN
319
320 -- check whether record has been updated by another user. If not, lock it.
321 BEGIN
322 SELECT OBJECT_VERSION_NUMBER,
323 ROWID
324 INTO l_object_version_number,
325 l_rowid
326 FROM FUN_RULE_OBJECTS_B
327 WHERE RULE_OBJECT_ID = p_rule_object_rec.rule_object_id
328 FOR UPDATE OF rule_object_id NOWAIT;
329
330
331 IF NOT ((p_object_version_number is null and l_object_version_number is null)
332 OR (p_object_version_number = l_object_version_number))
333 THEN
334
335 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_RECORD_CHANGED');
336 FND_MESSAGE.SET_TOKEN('TABLE', 'FUN_RULE_OBJECTS');
337 FND_MSG_PUB.ADD;
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340
341 p_object_version_number := nvl(l_object_version_number, 1) + 1;
342
343 EXCEPTION WHEN NO_DATA_FOUND THEN
344 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_NO_RECORD');
345 FND_MESSAGE.SET_TOKEN('RECORD', 'FUN_RULE_OBJECTS');
346 FND_MESSAGE.SET_TOKEN('VALUE', 'rule_object_id'||to_char(p_rule_object_rec.rule_object_id));
347 FND_MSG_PUB.ADD;
348 RAISE FND_API.G_EXC_ERROR;
349 END;
350
351 -- call for validations.
352 IF( p_update_instance = 'Y' ) THEN
353 -- call for validations of Rule Object Instance.
354 FUN_RULE_VALIDATE_PKG.validate_rule_object_instance(
355 'U',
356 p_rule_object_rec,
357 l_rowid,
358 x_return_status
359 );
360 ELSE
361 -- call for validations of Rule Object Record
362 FUN_RULE_VALIDATE_PKG.validate_rule_objects(
363 'U',
364 p_rule_object_rec,
365 l_rowid,
366 x_return_status
367 );
368 END IF;
369
370 IF x_return_status = fnd_api.g_ret_sts_error THEN
371 RAISE fnd_api.g_exc_error;
372 END IF;
373 -- call to table-handler.
374 FUN_RULE_OBJECTS_PKG.Update_Row (
375 X_RULE_OBJECT_ID =>p_rule_object_rec.rule_object_id,
376 X_APPLICATION_ID =>p_rule_object_rec.application_id,
377 X_RULE_OBJECT_NAME =>p_rule_object_rec.rule_object_name,
378 X_RESULT_TYPE =>p_rule_object_rec.result_type,
379 X_REQUIRED_FLAG =>p_rule_object_rec.required_flag,
380 X_USE_DEFAULT_VALUE_FLAG =>p_rule_object_rec.use_default_value_flag,
381 X_DEFAULT_APPLICATION_ID =>p_rule_object_rec.default_application_id,
382 X_DEFAULT_VALUE =>p_rule_object_rec.default_value,
383 X_FLEX_VALUE_SET_ID =>p_rule_object_rec.flex_value_set_id,
384 X_FLEXFIELD_NAME =>p_rule_object_rec.flexfield_name,
385 X_FLEXFIELD_APP_SHORT_NAME =>p_rule_object_rec.flexfield_app_short_name,
386 X_MULTI_RULE_RESULT_FLAG =>p_rule_object_rec.multi_rule_result_flag,
387 X_OBJECT_VERSION_NUMBER =>p_object_version_number,
388 X_CREATED_BY_MODULE =>p_rule_object_rec.created_by_module,
389 X_USER_RULE_OBJECT_NAME =>p_rule_object_rec.user_rule_object_name,
390 X_DESCRIPTION =>p_rule_object_rec.description,
391 X_USE_INSTANCE_FLAG =>p_rule_object_rec.use_instance_flag,
392 X_INSTANCE_LABEL =>p_rule_object_rec.instance_label,
393 X_PARENT_RULE_OBJECT_ID =>p_rule_object_rec.parent_rule_object_id,
394 X_ORG_ID =>p_rule_object_rec.org_id
395 );
396 END;
397
398
399 /**
400 * PROCEDURE create_rule_object
401 *
402 * DESCRIPTION
403 * Creates User customizable objects.
404 *
405 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
406 *
407 *
408 * ARGUMENTS
409 * IN:
410 * p_init_msg_list Initialize message stack if it is set to
411 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
412 * p_rule_object_rec User customizable object record.
413 * IN/OUT:
414 * OUT:
415 * x_return_status Return status after the call. The status can
416 * be FND_API.G_RET_STS_SUCCESS (success),
417 * FND_API.G_RET_STS_ERROR (error),
418 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
419 * x_msg_count Number of messages in message stack.
420 * x_msg_data Message text if x_msg_count is 1.
421 *
422 * NOTES
423 *
424 * MODIFICATION HISTORY
425 *
426 * 10-Sep-2004 Amulya Mishra Created.
427 *
428 */
429
430 PROCEDURE create_rule_object(
431 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
432 p_rule_object_rec IN RULE_OBJECTS_REC_TYPE,
433 x_rule_object_id OUT NOCOPY NUMBER,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2
437 ) IS
438
439 l_rule_object_rec RULE_OBJECTS_REC_TYPE:= p_rule_object_rec;
440
441 BEGIN
442
443 -- standard start of API savepoint
444 SAVEPOINT create_rule_objects;
445
446 -- initialize message list if p_init_msg_list is set to TRUE.
447 IF FND_API.to_Boolean(p_init_msg_list) THEN
448 FND_MSG_PUB.initialize;
449 END IF;
450
451 -- initialize API return status to success.
452 x_return_status := FND_API.G_RET_STS_SUCCESS;
453
454
455 -- call to business logic.
456 do_create_rule_object(
457 l_rule_object_rec,
458 l_rule_object_rec.rule_object_id,
459 x_return_status);
460
461 -- standard call to get message count and if count is 1, get message info.
462 FND_MSG_PUB.Count_And_Get(
463 p_encoded => FND_API.G_FALSE,
464 p_count => x_msg_count,
465 p_data => x_msg_data);
466
467 EXCEPTION
468 WHEN FND_API.G_EXC_ERROR THEN
469 ROLLBACK TO create_rule_objects;
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 FND_MSG_PUB.Count_And_Get(
472 p_encoded => FND_API.G_FALSE,
473 p_count => x_msg_count,
474 p_data => x_msg_data);
475 x_msg_data := FND_MSG_PUB.Get_Detail;
476
477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
478 ROLLBACK TO create_rule_objects;
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480 FND_MSG_PUB.Count_And_Get(
481 p_encoded => FND_API.G_FALSE,
482 p_count => x_msg_count,
483 p_data => x_msg_data);
484 x_msg_data := FND_MSG_PUB.Get_Detail;
485 WHEN OTHERS THEN
486 ROLLBACK TO create_rule_objects;
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
489 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
490 FND_MSG_PUB.ADD;
491 FND_MSG_PUB.Count_And_Get(
492 p_encoded => FND_API.G_FALSE,
493 p_count => x_msg_count,
494 p_data => x_msg_data);
495 x_msg_data := FND_MSG_PUB.Get_Detail;
496 END create_rule_object;
497
498 /**
499 * Use this routine to create a rule object instance to be used for rule partitioning.
500 * With this API you can create a record in the FUN_RULE_OBJECTS_B ,
501 * table provided the flag USE_INSTANCE_FLAG is set to 'Y'. This API will return
502 * the rule_object_id of the newly created rule object instance record.
503 *
504 * p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
505 * p_application_id Application identifier
506 * p_rule_object_name Name of Rule Object
507 * p_instance_label Name of the Instance.
508 * p_rule_object_id Internal identifier for the Rule Object
509 * x_return_status Return status after the call.
510 * x_msg_count Number of messages in message stack.
511 * x_msg_data Message text if x_msg_count is 1.
512 *
513 * 27-Dec-2005 Amulya Mishra Created
514 */
515
516 PROCEDURE create_rule_object_instance(
517 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
518 p_application_id IN NUMBER,
519 p_rule_object_name IN VARCHAR2,
520 p_instance_label IN VARCHAR2,
521 p_org_id IN NUMBER,
522 x_rule_object_id OUT NOCOPY NUMBER,
523 x_return_status OUT NOCOPY VARCHAR2,
524 x_msg_count OUT NOCOPY NUMBER,
525 x_msg_data OUT NOCOPY VARCHAR2
526 )
527 IS
528
529 BEGIN
530 -- standard start of API savepoint
531 SAVEPOINT create_rule_object_instance;
532
533 -- initialize message list if p_init_msg_list is set to TRUE.
534 IF FND_API.to_Boolean(p_init_msg_list) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537
538 -- initialize API return status to success.
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 --Dont create the Rule Object Intance, if it already exists. Simply return to
542 --calling procedure.
543
544 IF (rule_object_instance_exists(p_application_id , p_rule_object_name , p_instance_label , p_org_id)) THEN
545 BEGIN
546 --This should infact never raise exception if the code flow has reached here.
547
548 SELECT RULE_OBJECT_ID INTO X_RULE_OBJECT_ID
549 FROM FUN_RULE_OBJECTS_B
550 WHERE RULE_OBJECT_NAME = p_rule_object_name
551 AND APPLICATION_ID = p_application_id
552 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
553 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
554 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
555 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
556 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
557
558 RETURN;
559
560 EXCEPTION
561 WHEN NO_DATA_FOUND THEN
562 RETURN;
563
564 END;
565 -- call to business logic.
566 ELSE
567 do_create_rule_object_instance(
568 p_application_id,
569 p_rule_object_name,
570 p_instance_label,
571 p_org_id,
572 x_rule_object_id,
573 x_return_status);
574 END IF;
575 -- standard call to get message count and if count is 1, get message info.
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 EXCEPTION
581 WHEN FND_API.G_EXC_ERROR THEN
582 ROLLBACK TO create_rule_object_instance;
583 x_return_status := FND_API.G_RET_STS_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 x_msg_data := FND_MSG_PUB.Get_Detail;
589
590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
591 ROLLBACK TO create_rule_object_instance;
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 FND_MSG_PUB.Count_And_Get(
594 p_encoded => FND_API.G_FALSE,
595 p_count => x_msg_count,
596 p_data => x_msg_data);
597 x_msg_data := FND_MSG_PUB.Get_Detail;
598 WHEN OTHERS THEN
599 ROLLBACK TO create_rule_object_instance;
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
602 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
603 FND_MSG_PUB.ADD;
604 FND_MSG_PUB.Count_And_Get(
605 p_encoded => FND_API.G_FALSE,
606 p_count => x_msg_count,
607 p_data => x_msg_data);
608 x_msg_data := FND_MSG_PUB.Get_Detail;
609
610 END create_rule_object_instance;
611
612 /**
613 * PROCEDURE update_rule_object
614 *
615 * DESCRIPTION
616 * Updates User customizable objects
617 *
618 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
619 *
620 * ARGUMENTS
621 * IN:
622 * p_init_msg_list Initialize message stack if it is set to
623 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
624 * p_rules_object_rec User Customizable Object record.
625 * IN/OUT:
626 * p_object_version_number Used for locking the being updated record.
627 * OUT:
628 * x_return_status Return status after the call. The status can
629 * be FND_API.G_RET_STS_SUCCESS (success),
630 * FND_API.G_RET_STS_ERROR (error),
631 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
632 * x_msg_count Number of messages in message stack.
633 * x_msg_data Message text if x_msg_count is 1.
634 *
635 * NOTES
636 *
637 * MODIFICATION HISTORY
638 *
639 * 10-Sep-2004 Amulya Mishra Created.
640 *
641 */
642
643 PROCEDURE update_rule_object (
644 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
645 p_rule_object_rec IN RULE_OBJECTS_REC_TYPE,
646 p_object_version_number IN OUT NOCOPY NUMBER,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2
650 ) IS
651
652 l_rule_object_rec RULE_OBJECTS_REC_TYPE := p_rule_object_rec;
653 l_old_rule_object_rec RULE_OBJECTS_REC_TYPE;
654 l_rule_object_id number;
655
656 CURSOR FUN_RULE_OBJECTS_CUR(p_rule_object_id NUMBER) IS
657 SELECT
658 B.RULE_OBJECT_ID,
659 B.APPLICATION_ID,
660 B.RULE_OBJECT_NAME,
661 B.RESULT_TYPE,
662 B.REQUIRED_FLAG,
663 B.USE_DEFAULT_VALUE_FLAG,
664 ATTR.DEFAULT_APPLICATION_ID,
665 ATTR.DEFAULT_VALUE,
666 B.FLEX_VALUE_SET_ID,
667 B.FLEXFIELD_NAME,
668 B.FLEXFIELD_APP_SHORT_NAME,
669 B.MULTI_RULE_RESULT_FLAG,
670 B.CREATED_BY_MODULE,
671 B.OBJECT_VERSION_NUMBER,
672 TL.USER_RULE_OBJECT_NAME,
673 TL.DESCRIPTION,
674 B.USE_INSTANCE_FLAG,
675 B.INSTANCE_LABEL,
676 B.PARENT_RULE_OBJECT_ID,
677 B.ORG_ID
678 FROM FUN_RULE_OBJECTS_B B, FUN_RULE_OBJECTS_TL TL, FUN_RULE_OBJ_ATTRIBUTES ATTR
679 WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id
680 AND B.RULE_OBJECT_ID = ATTR.RULE_OBJECT_ID
681 AND B.RULE_OBJECT_ID = TL.RULE_OBJECT_ID
682 AND TL.LANGUAGE = 'US';
683
684 BEGIN
685 -- standard start of API savepoint
686 SAVEPOINT update_rule_objects;
687
688 -- initialize message list if p_init_msg_list is set to TRUE.
689 IF FND_API.to_Boolean(p_init_msg_list) THEN
690 FND_MSG_PUB.initialize;
691 END IF;
692
693 -- initialize API return status to success.
694 x_return_status := FND_API.G_RET_STS_SUCCESS;
695
696 -- Get old records. Will be used by business event system.
697 get_rule_object_rec (
698 p_rule_object_id => l_rule_object_rec.rule_object_id,
699 x_rule_object_rec => l_old_rule_object_rec,
700 x_return_status => x_return_status,
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data );
703
704 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
705 RAISE FND_API.G_EXC_ERROR;
706 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710
711 --Make sure to make the fields related to Rule Object Instance as NULL.
712 l_rule_object_rec.instance_label := null;
713 l_rule_object_rec.parent_rule_object_id := null;
714 l_rule_object_rec.org_id := null;
715
716
717 -- call to business logic.
718 do_update_rule_object(
719 'N', --Update for Rule Object Instance Parameter
720 l_rule_object_rec,
721 p_object_version_number,
722 x_return_status);
723
724 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
725
726 -- standard call to get message count and if count is 1, get message info.
727 FND_MSG_PUB.Count_And_Get(
728 p_encoded => FND_API.G_FALSE,
729 p_count => x_msg_count,
730 p_data => x_msg_data);
731 END IF;
732
733
734 --If USE_INSTANCE_FLAG is Y, then make the update in all instances.
735 --Loop through the cursor and update the Rule Object tables for each instances.
736
737 IF( UPPER(l_rule_object_rec.use_instance_flag) = 'Y') THEN
738 FOR C_REC IN FUN_RULE_OBJECTS_CUR(l_rule_object_rec.rule_object_id) LOOP
739
740
741 -- Get old records. Will be used by business event system.
742
743 get_rule_object_rec (
744 p_rule_object_id => C_REC.rule_object_id,
745 x_rule_object_rec => l_old_rule_object_rec,
746 x_return_status => x_return_status,
747 x_msg_count => x_msg_count,
748 x_msg_data => x_msg_data );
749
750 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
751 RAISE FND_API.G_EXC_ERROR;
752 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 END IF;
755
756 -- initialize API return status to success.
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 --Only propagate the changes of parent rule object record to the instance records.
760
761 l_rule_object_rec.rule_object_id := c_rec.rule_object_id;
762 l_rule_object_rec.use_instance_flag := c_rec.use_instance_flag;
763 l_rule_object_rec.instance_label := c_rec.instance_label;
764
765 --parent_rule_object_id should not be NULL for a Rule Object Instance.
766 --This validation is done in do_update_rule_object().
767
768 l_rule_object_rec.parent_rule_object_id := C_REC.parent_rule_object_id;
769 l_rule_object_rec.org_id := C_REC.org_id;
770
771
772 -- call to business logic.
773 do_update_rule_object(
774 'Y', --Update for Rule Object Instance Parameter
775 l_rule_object_rec,
776 C_REC.OBJECT_VERSION_NUMBER,
777 x_return_status);
778
779 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
780
781 -- standard call to get message count and if count is 1, get message info.
782 FND_MSG_PUB.Count_And_Get(
783 p_encoded => FND_API.G_FALSE,
784 p_count => x_msg_count,
785 p_data => x_msg_data);
786 END IF;
787
788 END LOOP;
789 END IF;
790
791
792 EXCEPTION
793 WHEN FND_API.G_EXC_ERROR THEN
794 ROLLBACK TO update_rule_objects;
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 FND_MSG_PUB.Count_And_Get(
797 p_encoded => FND_API.G_FALSE,
798 p_count => x_msg_count,
799 p_data => x_msg_data);
800
801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
802 ROLLBACK TO update_rule_objects;
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 FND_MSG_PUB.Count_And_Get(
805 p_encoded => FND_API.G_FALSE,
806 p_count => x_msg_count,
807 p_data => x_msg_data);
808
809 WHEN OTHERS THEN
810 ROLLBACK TO update_rule_objects;
811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
813 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
814 FND_MSG_PUB.ADD;
815 FND_MSG_PUB.Count_And_Get(
816 p_encoded => FND_API.G_FALSE,
817 p_count => x_msg_count,
818 p_data => x_msg_data);
819 END update_rule_object;
820
821
822 /**
823 * PROCEDURE get_rule_object_rec
824 *
825 * DESCRIPTION
826 * Gets user customizable objects record.
827 *
828 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
829 * FUN_RULES_PKG.Select_Row
830 *
831 * ARGUMENTS
832 * IN:
833 * p_init_msg_list Initialize message stack if it is set to
834 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
835 * p_rule_objects User customizable objects name.
836 * IN/OUT:
837 * OUT:
838 * x_rule_object_rec Returned class category record.
839 * x_return_status Return status after the call. The status can
840 * be FND_API.G_RET_STS_SUCCESS (success),
841 * FND_API.G_RET_STS_ERROR (error),
842 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
843 * x_msg_count Number of messages in message stack.
844 * x_msg_data Message text if x_msg_count is 1.
845 *
846 * NOTES
847 *
848 * MODIFICATION HISTORY
849 *
850 * 10-Sep-2004 Amulya Mishra Created.
851 *
852 */
853
854 PROCEDURE get_rule_object_rec (
855 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
856 p_rule_object_name IN VARCHAR2,
857 p_application_id IN NUMBER,
858 p_instance_label IN VARCHAR2,
859 p_org_id IN NUMBER,
860 x_rule_object_rec OUT NOCOPY RULE_OBJECTS_REC_TYPE,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2
864 ) IS
865
866 BEGIN
867
868 --Initialize message list if p_init_msg_list is set to TRUE.
869 IF FND_API.to_Boolean(p_init_msg_list) THEN
870 FND_MSG_PUB.initialize;
871 END IF;
872
873 --Initialize API return status to success.
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875
876 --Check whether primary key has been passed in.
877 IF p_rule_object_name IS NULL OR
878 p_rule_object_name = FND_API.G_MISS_CHAR THEN
879 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_MISSING_COLUMN' );
880 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'rule_object_name' );
881 FND_MSG_PUB.ADD;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884
885
886 x_rule_object_rec.rule_object_name := p_rule_object_name;
887 x_rule_object_rec.application_id := p_application_id;
888 x_rule_object_rec.instance_label := p_instance_label;
889 x_rule_object_rec.org_id := p_org_id;
890
891 FUN_RULE_OBJECTS_PKG.Select_Row (
892 X_RULE_OBJECT_NAME =>x_rule_object_rec.rule_object_name,
893 X_RULE_OBJECT_ID =>x_rule_object_rec.rule_object_id,
894 X_APPLICATION_ID =>x_rule_object_rec.application_id,
895 X_USER_RULE_OBJECT_NAME =>x_rule_object_rec.user_rule_object_name,
896 X_DESCRIPTION =>x_rule_object_rec.description,
897 X_RESULT_TYPE =>x_rule_object_rec.result_type,
898 X_REQUIRED_FLAG =>x_rule_object_rec.required_flag,
899 X_USE_DEFAULT_VALUE_FLAG =>x_rule_object_rec.use_default_value_flag,
900 X_DEFAULT_APPLICATION_ID =>x_rule_object_rec.default_application_id,
901 X_DEFAULT_VALUE =>x_rule_object_rec.default_value,
902 X_FLEX_VALUE_SET_ID =>x_rule_object_rec.flex_value_set_id,
903 X_FLEXFIELD_NAME =>x_rule_object_rec.flexfield_name,
904 X_FLEXFIELD_APP_SHORT_NAME =>x_rule_object_rec.flexfield_app_short_name,
905 X_MULTI_RULE_RESULT_FLAG =>x_rule_object_rec.multi_rule_result_flag,
906 X_CREATED_BY_MODULE =>x_rule_object_rec.created_by_module,
907 X_USE_INSTANCE_FLAG =>x_rule_object_rec.use_instance_flag,
908 X_INSTANCE_LABEL =>x_rule_object_rec.instance_label,
909 X_PARENT_RULE_OBJECT_ID =>x_rule_object_rec.parent_rule_object_id,
910 X_ORG_ID =>x_rule_object_rec.org_id
911 );
912
913
914 --Standard call to get message count and if count is 1, get message info.
915 FND_MSG_PUB.Count_And_Get(
916 p_encoded => FND_API.G_FALSE,
917 p_count => x_msg_count,
918 p_data => x_msg_data );
919
920 EXCEPTION
921 WHEN FND_API.G_EXC_ERROR THEN
922 x_return_status := FND_API.G_RET_STS_ERROR;
923
924 FND_MSG_PUB.Count_And_Get(
925 p_encoded => FND_API.G_FALSE,
926 p_count => x_msg_count,
927 p_data => x_msg_data );
928
929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
931
932 FND_MSG_PUB.Count_And_Get(
933 p_encoded => FND_API.G_FALSE,
934 p_count => x_msg_count,
935 p_data => x_msg_data );
936
937 WHEN OTHERS THEN
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939
940 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_OTHERS_EXCEP' );
941 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
942 FND_MSG_PUB.ADD;
943
944 FND_MSG_PUB.Count_And_Get(
945 p_encoded => FND_API.G_FALSE,
946 p_count => x_msg_count,
947 p_data => x_msg_data );
948
949 END get_rule_object_rec;
950
951 /**
952 * PROCEDURE get_rule_object_rec
953 *
954 * DESCRIPTION
955 * Gets Rule objects record for a passed Rule_Object_id
956 *
957 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
958 * FUN_RULES_PKG.Select_Row
959 *
960 * ARGUMENTS
961 * IN:
962 * p_init_msg_list Initialize message stack if it is set to
963 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
964 * p_rule_objects User customizable objects name.
965 * IN/OUT:
966 * OUT:
967 * x_rule_object_rec Returned class category record.
968 * x_return_status Return status after the call. The status can
969 * be FND_API.G_RET_STS_SUCCESS (success),
970 * FND_API.G_RET_STS_ERROR (error),
971 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
972 * x_msg_count Number of messages in message stack.
973 * x_msg_data Message text if x_msg_count is 1.
974 *
975 * NOTES
976 *
977 * MODIFICATION HISTORY
978 *
979 * 10-Sep-2004 Amulya Mishra Created.
980 *
981 */
982
983 PROCEDURE get_rule_object_rec (
984 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
985 p_rule_object_id IN NUMBER,
986 x_rule_object_rec OUT NOCOPY RULE_OBJECTS_REC_TYPE,
987 x_return_status OUT NOCOPY VARCHAR2,
988 x_msg_count OUT NOCOPY NUMBER,
989 x_msg_data OUT NOCOPY VARCHAR2
990 ) IS
991
992 BEGIN
993
994 --Initialize message list if p_init_msg_list is set to TRUE.
995 IF FND_API.to_Boolean(p_init_msg_list) THEN
996 FND_MSG_PUB.initialize;
997 END IF;
998
999 --Initialize API return status to success.
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001
1002 --Check whether primary key has been passed in.
1003 IF p_rule_object_id IS NULL OR
1004 p_rule_object_id = FND_API.G_MISS_NUM THEN
1005 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_MISSING_COLUMN' );
1006 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'rule_object_id' );
1007 FND_MSG_PUB.ADD;
1008 RAISE FND_API.G_EXC_ERROR;
1009 END IF;
1010
1011
1012 x_rule_object_rec.rule_object_id := p_rule_object_id;
1013
1014 FUN_RULE_OBJECTS_PKG.Select_Row_Rob_Id (
1015 X_RULE_OBJECT_NAME =>x_rule_object_rec.rule_object_name,
1016 X_RULE_OBJECT_ID =>x_rule_object_rec.rule_object_id,
1017 X_APPLICATION_ID =>x_rule_object_rec.application_id,
1018 X_USER_RULE_OBJECT_NAME =>x_rule_object_rec.user_rule_object_name,
1019 X_DESCRIPTION =>x_rule_object_rec.description,
1020 X_RESULT_TYPE =>x_rule_object_rec.result_type,
1021 X_REQUIRED_FLAG =>x_rule_object_rec.required_flag,
1022 X_USE_DEFAULT_VALUE_FLAG =>x_rule_object_rec.use_default_value_flag,
1023 X_DEFAULT_APPLICATION_ID =>x_rule_object_rec.default_application_id,
1024 X_DEFAULT_VALUE =>x_rule_object_rec.default_value,
1025 X_FLEX_VALUE_SET_ID =>x_rule_object_rec.flex_value_set_id,
1026 X_FLEXFIELD_NAME =>x_rule_object_rec.flexfield_name,
1027 X_FLEXFIELD_APP_SHORT_NAME =>x_rule_object_rec.flexfield_app_short_name,
1028 X_MULTI_RULE_RESULT_FLAG =>x_rule_object_rec.multi_rule_result_flag,
1029 X_CREATED_BY_MODULE =>x_rule_object_rec.created_by_module,
1030 X_USE_INSTANCE_FLAG =>x_rule_object_rec.use_instance_flag,
1031 X_INSTANCE_LABEL =>x_rule_object_rec.instance_label,
1032 X_PARENT_RULE_OBJECT_ID =>x_rule_object_rec.parent_rule_object_id,
1033 X_ORG_ID =>x_rule_object_rec.org_id
1034 );
1035
1036
1037 --Standard call to get message count and if count is 1, get message info.
1038 FND_MSG_PUB.Count_And_Get(
1039 p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data );
1042
1043 EXCEPTION
1044 WHEN FND_API.G_EXC_ERROR THEN
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046
1047 FND_MSG_PUB.Count_And_Get(
1048 p_encoded => FND_API.G_FALSE,
1049 p_count => x_msg_count,
1050 p_data => x_msg_data );
1051
1052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054
1055 FND_MSG_PUB.Count_And_Get(
1056 p_encoded => FND_API.G_FALSE,
1057 p_count => x_msg_count,
1058 p_data => x_msg_data );
1059
1060 WHEN OTHERS THEN
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062
1063 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_OTHERS_EXCEP' );
1064 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1065 FND_MSG_PUB.ADD;
1066
1067 FND_MSG_PUB.Count_And_Get(
1068 p_encoded => FND_API.G_FALSE,
1069 p_count => x_msg_count,
1070 p_data => x_msg_data );
1071
1072 END get_rule_object_rec;
1073
1074 /**
1075 * PROCEDURE delete_rule_object
1076 *
1077 * DESCRIPTION
1078 * Deletes User customizable objects.
1079 *
1080 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1081 *
1082 *
1083 * ARGUMENTS
1084 * IN:
1085 * p_init_msg_list Initialize message stack if it is set to
1086 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1087 * p_rule_object_name User customizable object Name.
1088 * p_application_id Application Id
1089 * IN/OUT:
1090 * OUT:
1091 * x_return_status Return status after the call. The status can
1092 * be FND_API.G_RET_STS_SUCCESS (success),
1093 * FND_API.G_RET_STS_ERROR (error),
1094 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1095 * x_msg_count Number of messages in message stack.
1096 * x_msg_data Message text if x_msg_count is 1.
1097 *
1098 * NOTES
1099 *
1100 * MODIFICATION HISTORY
1101 *
1102 * 10-Sep-2004 Amulya Mishra Created.
1103 *
1104 */
1105
1106 PROCEDURE delete_rule_object(
1107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1108 p_rule_object_name IN VARCHAR2,
1109 p_application_id IN NUMBER,
1110 x_return_status OUT NOCOPY VARCHAR2,
1111 x_msg_count OUT NOCOPY NUMBER,
1112 x_msg_data OUT NOCOPY VARCHAR2
1113 ) IS
1114
1115 l_rule_object_id number;
1116
1117 CURSOR FUN_RULE_OBJECTS_CUR(p_rule_object_id NUMBER) IS
1118 SELECT
1119 B.APPLICATION_ID,
1120 B.RULE_OBJECT_NAME,
1121 B.INSTANCE_LABEL,
1122 B.ORG_ID
1123 FROM FUN_RULE_OBJECTS_B B, FUN_RULE_OBJECTS_TL TL, FUN_RULE_OBJ_ATTRIBUTES ATTR
1124 WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id
1125 AND B.RULE_OBJECT_ID = ATTR.RULE_OBJECT_ID
1126 AND B.RULE_OBJECT_ID = TL.RULE_OBJECT_ID
1127 AND TL.LANGUAGE = 'US';
1128
1129 BEGIN
1130
1131 -- standard start of API savepoint
1132 SAVEPOINT delete_rule_object;
1133
1134 --Store the rule_object_id first so that it will be used
1135 --to delete the instances later.
1136
1137 BEGIN
1138 SELECT RULE_OBJECT_ID INTO l_rule_object_id FROM FUN_RULE_OBJECTS_B
1139 WHERE RULE_OBJECT_NAME = p_rule_object_name
1140 AND APPLICATION_ID = p_application_id
1141 AND INSTANCE_LABEL IS NULL
1142 AND ORG_ID IS NULL
1143 AND PARENT_RULE_OBJECT_ID IS NULL;
1144
1145 EXCEPTION
1146 WHEN NO_DATA_FOUND THEN
1147 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_INVALID_ROB');
1148 FND_MSG_PUB.ADD;
1149 RAISE FND_API.G_EXC_ERROR;
1150 END;
1151
1152 --Instead of calling the delete apis of the tables for each record, from performance point of view
1153 --its better to issue direct delete statement.
1154
1155 DELETE FROM FUN_RULE_PARAM_VALUES FRPV
1156 WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
1157 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
1158 WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
1159 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1160
1161
1162 DELETE FROM FUN_RULE_CRIT_PARAMS_TL FRCP
1163 WHERE CRITERIA_PARAM_ID IN (SELECT CRITERIA_PARAM_ID
1164 FROM FUN_RULE_OBJECTS_B FROB, FUN_RULE_CRIT_PARAMS_B FRCPB
1165 WHERE FROB.RULE_OBJECT_ID = FRCPB.RULE_OBJECT_ID
1166 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1167
1168 DELETE FROM FUN_RULE_CRIT_PARAMS_B FRCP
1169 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1170 FROM FUN_RULE_OBJECTS_B WHERE
1171 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1172
1173 DELETE FROM FUN_RULE_DETAILS FRD
1174 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1175 FROM FUN_RULE_OBJECTS_B WHERE
1176 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1177 DELETE FROM FUN_RULE_CRITERIA FRC
1178 WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
1179 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
1180 WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
1181 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1182
1183 --TEST Related tables deletion starts from here
1184
1185 DELETE FROM FUN_RULE_TEST_MULTIRULE FRTP
1186 WHERE TEST_ID IN (SELECT TEST_ID
1187 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1188 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1189 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1190
1191 DELETE FROM FUN_RULE_TEST_RESULTS FRTR
1192 WHERE TEST_ID IN (SELECT TEST_ID
1193 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1194 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1195 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1196
1197 DELETE FROM FUN_RULE_TEST_MVAL_RES FRTMR
1198 WHERE TEST_ID IN (SELECT TEST_ID
1199 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1200 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1201 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1202
1203 DELETE FROM FUN_RULE_TEST_PARAMS FRTP
1204 WHERE TEST_ID IN (SELECT TEST_ID
1205 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1206 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1207 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1208
1209 DELETE FROM FUN_RULE_TESTS FRT
1210 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1211 FROM FUN_RULE_OBJECTS_B WHERE
1212 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
1213
1214 -- initialize message list if p_init_msg_list is set to TRUE.
1215 IF FND_API.to_Boolean(p_init_msg_list) THEN
1216 FND_MSG_PUB.initialize;
1217 END IF;
1218
1219 -- initialize API return status to success.
1220 x_return_status := FND_API.G_RET_STS_SUCCESS;
1221
1222 -- call to table-handler.
1223 FUN_RULE_OBJECTS_PKG.Delete_Row (
1224 X_RULE_OBJECT_NAME =>p_rule_object_name,
1225 X_APPLICATION_ID =>p_application_id
1226 );
1227
1228
1229 --If USE_INSTANCE_FLAG is Y, then DELETE all instances.
1230 --Loop through the cursor and update the Rule Object tables for each instances.
1231
1232
1233 FOR C_REC IN FUN_RULE_OBJECTS_CUR(l_rule_object_id) LOOP
1234
1235 -- initialize API return status to success.
1236 x_return_status := FND_API.G_RET_STS_SUCCESS;
1237
1238 delete_rule_object_instance(
1239 p_application_id => C_REC.application_id,
1240 p_rule_object_name => C_REC.rule_object_name,
1241 p_instance_label => C_REC.instance_label,
1242 p_org_id => C_REC.org_id,
1243 x_return_status => x_return_status,
1244 x_msg_count => x_msg_count,
1245 x_msg_data => x_msg_data);
1246
1247
1248 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1249
1250 -- standard call to get message count and if count is 1, get message info.
1251 FND_MSG_PUB.Count_And_Get(
1252 p_encoded => FND_API.G_FALSE,
1253 p_count => x_msg_count,
1254 p_data => x_msg_data);
1255 END IF;
1256
1257 END LOOP;
1258
1259 -- standard call to get message count and if count is 1, get message info.
1260 FND_MSG_PUB.Count_And_Get(
1261 p_encoded => FND_API.G_FALSE,
1262 p_count => x_msg_count,
1263 p_data => x_msg_data);
1264
1265 EXCEPTION
1266 WHEN FND_API.G_EXC_ERROR THEN
1267 ROLLBACK TO delete_rule_object;
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 FND_MSG_PUB.Count_And_Get(
1270 p_encoded => FND_API.G_FALSE,
1271 p_count => x_msg_count,
1272 p_data => x_msg_data);
1273
1274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1275 ROLLBACK TO delete_rule_object;
1276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277 FND_MSG_PUB.Count_And_Get(
1278 p_encoded => FND_API.G_FALSE,
1279 p_count => x_msg_count,
1280 p_data => x_msg_data);
1281
1282 WHEN OTHERS THEN
1283 ROLLBACK TO delete_rule_object;
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
1286 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1287 FND_MSG_PUB.ADD;
1288 FND_MSG_PUB.Count_And_Get(
1289 p_encoded => FND_API.G_FALSE,
1290 p_count => x_msg_count,
1291 p_data => x_msg_data);
1292 END delete_rule_object;
1293
1294
1295
1296 /**
1297 * Use this routine to delete a rule object instance. The API deletes a record in the
1298 * FUN_RULE_OBJECTS_B table after checking if the USE_INSTANCE_FLAG is Y or not.
1299 *
1300 *
1301 * p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
1302 * p_application_id Application identifier
1303 * p_rule_object_name Name of Rule Object
1304 * p_instance_label Name of the Instance.
1305 * x_return_status Return status after the call.
1306 * x_msg_count Number of messages in message stack.
1307 * x_msg_data Message text if x_msg_count is 1.
1308 *
1309 * 27-Dec-2005 Amulya Mishra Created
1310 */
1311
1312 PROCEDURE delete_rule_object_instance(
1313 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1314 p_application_id IN NUMBER,
1315 p_rule_object_name IN VARCHAR2,
1316 p_instance_label IN VARCHAR2,
1317 p_org_id IN NUMBER,
1318 x_return_status OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER,
1320 x_msg_data OUT NOCOPY VARCHAR2
1321 ) IS
1322
1323 l_rule_object_id NUMBER;
1324
1325 BEGIN
1326
1327 -- standard start of API savepoint
1328 SAVEPOINT delete_rule_object_instance;
1329
1330 --Get the RULE_OBJECT_ID from FUN_RULE_OBJECTS_B for the Rule Instance.
1331 --Use the RULE_OBJECT_ID to delete the records.
1332
1333 BEGIN
1334 SELECT RULE_OBJECT_ID INTO l_rule_object_id FROM FUN_RULE_OBJECTS_B
1335 WHERE RULE_OBJECT_NAME = p_rule_object_name
1336 AND APPLICATION_ID = p_application_id
1337 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1338 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1339 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1340 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1341 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
1342
1343 EXCEPTION
1344 WHEN NO_DATA_FOUND THEN
1345 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_NO_INSTANCE');
1346 FND_MESSAGE.SET_TOKEN('INSTANCE_LABEL' ,p_instance_label);
1347 FND_MSG_PUB.ADD;
1348 RAISE FND_API.G_EXC_ERROR;
1349 END;
1350
1351 --Instead of calling the delete apis of the tables for each record, from performance point of view
1352 --its better to issue direct delete statement.
1353
1354 DELETE FROM FUN_RULE_PARAM_VALUES FRPV
1355 WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
1356 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
1357 WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
1358 AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1359 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1360 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1361 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1362 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1363 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1364 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1365
1366
1367 DELETE FROM FUN_RULE_CRIT_PARAMS_TL FRCP
1368 WHERE CRITERIA_PARAM_ID IN (SELECT CRITERIA_PARAM_ID
1369 FROM FUN_RULE_OBJECTS_B FROB, FUN_RULE_CRIT_PARAMS_B FRCPB
1370 WHERE FROB.RULE_OBJECT_ID = FRCPB.RULE_OBJECT_ID
1371 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1372 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1373 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1374 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1375 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1376 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1377 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1378
1379
1380
1381 DELETE FROM FUN_RULE_CRIT_PARAMS_B FRCP
1382 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1383 FROM FUN_RULE_OBJECTS_B WHERE
1384 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1385 AND APPLICATION_ID = P_APPLICATION_ID
1386 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1387 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1388 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1389 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1390 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1391
1392 DELETE FROM FUN_RULE_DETAILS FRD
1393 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1394 FROM FUN_RULE_OBJECTS_B WHERE
1395 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1396 AND APPLICATION_ID = P_APPLICATION_ID
1397 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1398 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1399 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1400 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1401 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1402
1403 DELETE FROM FUN_RULE_CRITERIA FRC
1404 WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
1405 FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
1406 WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
1407 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1408 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1409 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1410 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1411 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1412 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1413 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1414
1415
1416 --TEST Related tables deletion starts from here
1417
1418 DELETE FROM FUN_RULE_TEST_MULTIRULE FRTP
1419 WHERE TEST_ID IN (SELECT TEST_ID
1420 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1421 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1422 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1423 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1424 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1425 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1426 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1427 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1428 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1429
1430
1431 DELETE FROM FUN_RULE_TEST_RESULTS FRTR
1432 WHERE TEST_ID IN (SELECT TEST_ID
1433 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1434 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1435 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1436 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1437 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1438 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1439 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1440 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1441 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1442
1443
1444 DELETE FROM FUN_RULE_TEST_MVAL_RES FRTMR
1445 WHERE TEST_ID IN (SELECT TEST_ID
1446 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1447 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1448 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1449 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1450 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1451 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1452 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1453 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1454 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1455
1456
1457 DELETE FROM FUN_RULE_TEST_PARAMS FRTP
1458 WHERE TEST_ID IN (SELECT TEST_ID
1459 FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
1460 WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
1461 AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1462 AND FROB.APPLICATION_ID = P_APPLICATION_ID
1463 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1464 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1465 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1466 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1467 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1468
1469
1470 DELETE FROM FUN_RULE_TESTS FRT
1471 WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
1472 FROM FUN_RULE_OBJECTS_B WHERE
1473 RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
1474 AND APPLICATION_ID = P_APPLICATION_ID
1475 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1476 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1477 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1478 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1479 AND PARENT_RULE_OBJECT_ID IS NOT NULL);
1480
1481
1482 -- initialize message list if p_init_msg_list is set to TRUE.
1483 IF FND_API.to_Boolean(p_init_msg_list) THEN
1484 FND_MSG_PUB.initialize;
1485 END IF;
1486
1487 -- initialize API return status to success.
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489
1490 -- call to table-handler.
1491 FUN_RULE_OBJECTS_PKG.Delete_Row (
1492 X_RULE_OBJECT_ID =>l_rule_object_id
1493 );
1494
1495 -- standard call to get message count and if count is 1, get message info.
1496 FND_MSG_PUB.Count_And_Get(
1497 p_encoded => FND_API.G_FALSE,
1498 p_count => x_msg_count,
1499 p_data => x_msg_data);
1500
1501 EXCEPTION
1502 WHEN FND_API.G_EXC_ERROR THEN
1503 ROLLBACK TO delete_rule_object_instance;
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 FND_MSG_PUB.Count_And_Get(
1506 p_encoded => FND_API.G_FALSE,
1507 p_count => x_msg_count,
1508 p_data => x_msg_data);
1509
1510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1511 ROLLBACK TO delete_rule_object_instance;
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1513 FND_MSG_PUB.Count_And_Get(
1514 p_encoded => FND_API.G_FALSE,
1515 p_count => x_msg_count,
1516 p_data => x_msg_data);
1517
1518 WHEN OTHERS THEN
1519 ROLLBACK TO delete_rule_object_instance;
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
1522 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1523 FND_MSG_PUB.ADD;
1524 FND_MSG_PUB.Count_And_Get(
1525 p_encoded => FND_API.G_FALSE,
1526 p_count => x_msg_count,
1527 p_data => x_msg_data);
1528 END delete_rule_object_instance;
1529
1530
1531 FUNCTION RULE_OBJECT_USES_PARAMETER(p_rule_object_name IN VARCHAR2,
1532 p_parameter_name IN VARCHAR2)
1533 RETURN BOOLEAN
1534 IS
1535 l_dummy VARCHAR2(1);
1536 BEGIN
1537
1538 SELECT 'Y' INTO l_dummy
1539 FROM FUN_RULE_CRIT_PARAMS_B FRCP, FUN_RULE_OBJECTS_B FROB
1540 WHERE FROB.RULE_OBJECT_ID = FRCP.RULE_OBJECT_ID
1541 AND FROB.RULE_OBJECT_NAME = p_rule_object_name
1542 AND FRCP.PARAM_NAME = p_parameter_name;
1543
1544 return TRUE;
1545 EXCEPTION
1546 WHEN NO_DATA_FOUND THEN
1547 return FALSE;
1548 WHEN OTHERS THEN
1549 return FALSE;
1550 END;
1551
1552 /**
1553 * PROCEDURE rule_object_instance_exists
1554 *
1555 * DESCRIPTION
1556 * Returns Boolean value based on a Rule Object Instance exists in the
1557 * FUN_RULE_OBJECTS_B table or not.
1558 *
1559 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1560 *
1561 *
1562 * ARGUMENTS
1563 * IN:
1564 * p_application_id Application Id
1565 * p_rule_object_name Rule object Name.
1566 * p_instance_label Rule Object Instance Label name
1567 * IN/OUT:
1568 * OUT:
1569 * True , if the rule object instance exists in the table, else returns False.
1570 *
1571 * NOTES
1572 *
1573 * MODIFICATION HISTORY
1574 *
1575 * 27-Dec-2005 Amulya Mishra Created.
1576 *
1577 */
1578
1579 FUNCTION rule_object_instance_exists(
1580 p_application_id IN NUMBER,
1581 p_rule_object_name IN VARCHAR2,
1582 p_instance_label IN VARCHAR2,
1583 p_org_id IN NUMBER
1584 ) RETURN BOOLEAN
1585 IS
1586
1587 l_param_value VARCHAR2(1);
1588
1589
1590 BEGIN
1591 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1592 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start FUN_RULE_PUB.rule_object_instance_exists', FALSE);
1593 end if;
1594
1595 /*
1596 Bug 7040957 - re_written the routine to avoid high parsing as dynamic code is not required under
1597 current case.
1598 */
1599
1600
1601 SELECT 'Y' INTO l_param_value
1602 FROM FUN_RULE_OBJECTS_B
1603 WHERE RULE_OBJECT_NAME = p_rule_object_name
1604 AND APPLICATION_ID = p_application_id
1605 AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
1606 (INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
1607 AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
1608 ( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
1609 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
1610
1611 if (l_param_value = 'Y') then
1612 return TRUE;
1613 else
1614 return FALSE;
1615 end if;
1616
1617 EXCEPTION
1618
1619 WHEN TOO_MANY_ROWS THEN
1620 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1621 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.rule_object_instance_exists:->TO_MANY_ROWS', FALSE);
1622 END IF;
1623 return TRUE;
1624
1625 WHEN NO_DATA_FOUND THEN
1626 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1627 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.rule_object_instance_exists:->NO_DATA_FOUND', FALSE);
1628 END IF;
1629 return FALSE;
1630
1631 WHEN OTHERS THEN
1632
1633 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.rule_object_instance_exists:->'||SQLERRM, FALSE);
1635 END IF;
1636 RAISE;
1637 END rule_object_instance_exists;
1638
1639 /**
1640 * FUNCTION rule_object_instance_exists
1641 *
1642 * DESCRIPTION
1643 * Returns ('Y'/'N') value based on a Rule Object Instance exists in the
1644 * FUN_RULE_OBJECTS_B table or not.
1645 *
1646 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1647 *
1648 *
1649 * ARGUMENTS
1650 * IN:
1651 * p_application_id Application Id
1652 * p_rule_object_name Rule object Name.
1653 * p_instance_label Rule Object Instance Label name
1654 * p_org_id Operating Unit
1655 * IN/OUT:
1656 * OUT:
1657 * 'Y' , if the rule object instance exists in the table, else returns 'N'.
1658 *
1659 * NOTES
1660 *
1661 * MODIFICATION HISTORY
1662 *
1663 * 18-JUL-2006 A.Hari Krishna Created.
1664 *
1665 */
1666 FUNCTION rule_object_instance_exists_vc(
1667 p_application_id IN NUMBER,
1668 p_rule_object_name IN VARCHAR2,
1669 p_instance_label IN VARCHAR2,
1670 p_org_id IN NUMBER
1671
1672 ) RETURN VARCHAR2
1673
1674 IS
1675
1676 return_value VARCHAR2(1);
1677
1678 BEGIN
1679
1680 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1681 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'Start of FUN_RULE_PUB.rule_object_instance_exists', FALSE);
1682 end if;
1683
1684 if(rule_object_instance_exists(p_application_id,p_rule_object_name,p_instance_label,p_org_id) = TRUE) then
1685 return_value:='Y';
1686 else
1687 return_value:='N';
1688 end if;
1689
1690 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1691 fnd_log.message(FND_LOG.LEVEL_STATEMENT, 'End of FUN_RULE_PUB.rule_object_instance_exists', FALSE);
1692 end if;
1693
1694 return return_value;
1695
1696 END rule_object_instance_exists_vc;
1697
1698 /**
1699 * PROCEDURE convert_use_instance
1700 *
1701 * DESCRIPTION
1702 * convert the Rule Object to make it enabling Instance
1703 * and vice versa.
1704 *
1705 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1706 *
1707 *
1708 * ARGUMENTS
1709 * IN:
1710 * p_init_msg_list Initialize message stack if it is set to
1711 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1712 * p_rule_object_id Internal identifier for the Rule Object
1713 * p_instance_flag Use Instance Flag value for the Rule Object
1714 * p_instance_label Instance Label to be associated with the Rule Object Instances.
1715 * p_org_id Internal identifier for the organization id to be
1716 * associated with the Rule Object Instances
1717 * IN/OUT:
1718 * OUT:
1719 * x_return_status Return status after the call. The status can
1720 * be FND_API.G_RET_STS_SUCCESS (success),
1721 * FND_API.G_RET_STS_ERROR (error),
1722 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1723 * x_msg_count Number of messages in message stack.
1724 * x_msg_data Message text if x_msg_count is 1.
1725 *
1726 * NOTES
1727 *
1728 * MODIFICATION HISTORY
1729 *
1730 * 14-Feb-2006 Amulya Mishra Created.
1731 *
1732 */
1733
1734 /*Algorithm for converting the Rule Object Into Instance Anabled and vice-versa.
1735
1736 If the RULE_OBJECT_ID is for an object instance, error out.
1737 Compare the USE_INSTANCE_FLAG on the rule object with P_INSTANCE_FLAG.
1738 If they are the same, do nothing and exit out.
1739 If P_INSTANCE_FLAG = 'Y' then
1740 Set USE_INSTANCE_FLAG to 'Y' for the rule object.
1741 IF P_INSTANCE_LABEL and/or P_ORG_ID is not null THEN
1742 Create a new instance.
1743 Move all of the original rules and test cases to this new instance.
1744 Else
1745 Delete all of the original rules and test cases.
1746 End if
1747 Else if P_INSTANCE_FLAG = 'N' then
1748 Set USE_INSTANCE_FLAG to 'N' for the rule object
1749 Delete all object instances
1750 Else
1751 Invalid P_INSTANCE_FLAG -- error
1752 End if
1753
1754 */
1755
1756
1757 PROCEDURE convert_use_instance(
1758 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1759 p_rule_object_id IN NUMBER,
1760 p_use_instance_flag IN VARCHAR2,
1761 p_instance_label IN VARCHAR2 DEFAULT NULL,
1762 P_ORG_ID IN NUMBER DEFAULT NULL,
1763 x_return_status OUT NOCOPY VARCHAR2,
1764 x_msg_count OUT NOCOPY NUMBER,
1765 x_msg_data OUT NOCOPY VARCHAR2
1766 )
1767 IS
1768 CURSOR C_RULE_OBJECT_INSTANCES(p_rule_object_id NUMBER) IS
1769 SELECT APPLICATION_ID,RULE_OBJECT_NAME,
1770 RULE_OBJECT_ID, INSTANCE_LABEL, ORG_ID
1771 FROM FUN_RULE_OBJECTS_B
1772 WHERE PARENT_RULE_OBJECT_ID = p_rule_object_id;
1773
1774 x_rule_object_id FUN_RULE_OBJECTS_B.RULE_OBJECT_ID%TYPE;
1775 l_application_id FUN_RULE_OBJECTS_B.APPLICATION_ID%TYPE;
1776 l_rule_object_name FUN_RULE_OBJECTS_B.RULE_OBJECT_NAME%TYPE;
1777 l_parent_rule_object_id FUN_RULE_OBJECTS_B.PARENT_RULE_OBJECT_ID%TYPE;
1778 l_use_instance_flag FUN_RULE_OBJECTS_B.USE_INSTANCE_FLAG%TYPE;
1779
1780 BEGIN
1781 -- standard start of API savepoint
1782 SAVEPOINT convert_use_instance;
1783
1784 -- initialize message list if p_init_msg_list is set to TRUE.
1785 IF FND_API.to_Boolean(p_init_msg_list) THEN
1786 FND_MSG_PUB.initialize;
1787 END IF;
1788
1789 -- initialize API return status to success.
1790 x_return_status := FND_API.G_RET_STS_SUCCESS;
1791
1792 /*The Rule Object Id passed is for an Instance which cannt be processed.
1793 *Only parent Rule Objects should be processed through this method
1794 */
1795
1796 IF (FUN_RULE_UTILITY_PKG.IS_USE_INSTANCE(p_rule_object_id)) THEN
1797 fnd_message.set_name('FUN', 'FUN_RULE_NO_CONV_ROB_INST');
1798 fnd_msg_pub.add;
1799 x_return_status := fnd_api.g_ret_sts_error;
1800 RAISE FND_API.G_EXC_ERROR;
1801 END IF;
1802
1803 /*Compare the USE_INSTANCE_FLAG from Database for the Rule Object Id and compare
1804 *with the passed value i.e p_use_instance_flag. If same, then simply exit.
1805 */
1806 --Since we have come here, that means the Rule Object Id record exists in the database.
1807 --So No need to handling exception.
1808
1809 BEGIN
1810 SELECT USE_INSTANCE_FLAG , APPLICATION_ID, RULE_OBJECT_NAME
1811 INTO l_use_instance_flag, l_application_id, l_rule_object_name
1812 FROM FUN_RULE_OBJECTS_B
1813 WHERE RULE_OBJECT_ID = p_rule_object_id;
1814 END;
1815
1816 IF (NVL(l_use_instance_flag,'N') = NVL(p_use_instance_flag,'N')) THEN
1817 RETURN;
1818 END IF;
1819
1820
1821 IF (UPPER(p_use_instance_flag) = 'Y') THEN
1822
1823 --Set USE_INSTANCE_FLAG to 'Y' for the rule object.
1824 BEGIN
1825 UPDATE FUN_RULE_OBJECTS_B
1826 SET USE_INSTANCE_FLAG ='Y'
1827 WHERE RULE_OBJECT_ID = p_rule_object_id;
1828 END;
1829
1830 if ( p_instance_label IS NOT NULL OR
1831 p_org_id IS NOT NULL) then
1832
1833 --Create a new instance.
1834
1835 create_rule_object_instance(
1836 p_application_id => l_application_id,
1837 p_rule_object_name => l_rule_object_name,
1838 p_instance_label => p_instance_label,
1839 p_org_id => p_org_id,
1840 x_rule_object_id => x_rule_object_id,
1841 x_return_status => x_return_status,
1842 x_msg_count => x_msg_count ,
1843 x_msg_data => x_msg_data
1844 );
1845
1846 -- Move all of the original rules and test cases to this new instance.
1847 BEGIN
1848 UPDATE FUN_RULE_DETAILS
1849 SET RULE_OBJECT_ID = x_rule_object_id
1850 WHERE RULE_OBJECT_ID = p_rule_object_id;
1851 END;
1852
1853 BEGIN
1854 UPDATE FUN_RULE_TESTS
1855 SET RULE_OBJECT_ID = x_rule_object_id
1856 WHERE RULE_OBJECT_ID = p_rule_object_id;
1857 END;
1858 else
1859 --Delete all of the original rules and test cases.
1860
1861 delete_rule_object(
1862 p_rule_object_name => l_rule_object_name,
1863 p_application_id => l_application_id,
1864 x_return_status => x_return_status,
1865 x_msg_count => x_msg_count ,
1866 x_msg_data => x_msg_data
1867 );
1868
1869 end if;
1870
1871 ELSIF (UPPER(p_use_instance_flag) = 'N') THEN
1872
1873 --Select the parent rule object id for this instance.
1874 BEGIN
1875 SELECT PARENT_RULE_OBJECT_ID
1876 INTO l_parent_rule_object_id
1877 FROM FUN_RULE_OBJECTS_B
1878 WHERE RULE_OBJECT_ID = p_rule_object_id;
1879 END;
1880
1881 --Set USE_INSTANCE_FLAG to 'N' for the rule object
1882 BEGIN
1883 UPDATE FUN_RULE_OBJECTS_B
1884 SET USE_INSTANCE_FLAG ='N',
1885 INSTANCE_LABEL = null,
1886 ORG_ID = null,
1887 PARENT_RULE_OBJECT_ID = null
1888 WHERE RULE_OBJECT_ID = p_rule_object_id;
1889 END;
1890
1891 --Delete all object instances
1892
1893 FOR c_rec IN C_RULE_OBJECT_INSTANCES(p_rule_object_id)
1894 LOOP
1895 delete_rule_object_instance(
1896 p_application_id => c_rec.application_id,
1897 p_rule_object_name => c_rec.rule_object_name,
1898 p_instance_label => c_rec.instance_label,
1899 p_org_id => c_rec.org_id,
1900 x_return_status => x_return_status,
1901 x_msg_count => x_msg_count,
1902 x_msg_data => x_msg_data
1903 );
1904 END LOOP;
1905
1906 ELSE
1907 --Invalid P_USE_INSTANCE_FLAG
1908
1909 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_INST_FLAG');
1910 fnd_msg_pub.add;
1911 x_return_status := fnd_api.g_ret_sts_error;
1912 RAISE FND_API.G_EXC_ERROR;
1913 END IF;
1914
1915 EXCEPTION
1916 WHEN FND_API.G_EXC_ERROR THEN
1917 ROLLBACK TO convert_use_instance;
1918 x_return_status := FND_API.G_RET_STS_ERROR;
1919 FND_MSG_PUB.Count_And_Get(
1920 p_encoded => FND_API.G_FALSE,
1921 p_count => x_msg_count,
1922 p_data => x_msg_data);
1923 x_msg_data := FND_MSG_PUB.Get_Detail;
1924
1925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1926 ROLLBACK TO convert_use_instance;
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1928 FND_MSG_PUB.Count_And_Get(
1929 p_encoded => FND_API.G_FALSE,
1930 p_count => x_msg_count,
1931 p_data => x_msg_data);
1932 x_msg_data := FND_MSG_PUB.Get_Detail;
1933 WHEN OTHERS THEN
1934 ROLLBACK TO convert_use_instance;
1935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1936 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
1937 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1938 FND_MSG_PUB.ADD;
1939 FND_MSG_PUB.Count_And_Get(
1940 p_encoded => FND_API.G_FALSE,
1941 p_count => x_msg_count,
1942 p_data => x_msg_data);
1943 x_msg_data := FND_MSG_PUB.Get_Detail;
1944 END convert_use_instance;
1945
1946 END FUN_RULE_OBJECTS_PUB;
[Home] [Help]