[Home] [Help]
PACKAGE BODY: APPS.CN_RULE_PUB
Source
1 PACKAGE BODY CN_Rule_PUB AS
2 --$Header: cnpruleb.pls 120.2 2005/08/25 23:37:44 rramakri noship $
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Rule_PUB';
6 G_LAST_UPDATE_DATE DATE := Sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := Sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13
14 -- Start of comments
15 -- API name : Create_Rule
16 -- Type : Public
17 -- Function : This Public API can be used to create a rule
18 -- Pre-reqs : None.
19 -- Parameters :
20 -- IN : p_api_version IN NUMBER Required
21 -- p_init_msg_list IN VARCHAR2 Optional
22 -- Default = FND_API.G_FALSE
23 -- p_commit IN VARCHAR2 Optional
24 -- Default = FND_API.G_FALSE
25 -- p_validation_level IN NUMBER Optional
26 -- Default = FND_API.G_VALID_LEVEL_FULL
27 -- p_rule_rec IN
28 -- CN_Rule_PUB.rule_rec_type
29 --
30 -- OUT : x_return_status OUT VARCHAR2(1)
31 -- x_msg_count OUT NUMBER
32 -- x_msg_data OUT VARCHAR2(2000)
33 --
34 -- Version : Current version 1.0
35 -- 25-Mar-99 Renu Chintalapati
36 -- previous version y.y
37 -- Changed....
38 -- Initial version 1.0
39 -- 25-Mar-99 Renu Chintalapati
40 --
41 -- Notes : Note text
42 --
43 -- End of comments
44
45 PROCEDURE Create_Rule
46 ( p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_commit IN VARCHAR2 := FND_API.G_FALSE,
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2,
53 x_loading_status OUT NOCOPY VARCHAR2,
54 p_rule_rec IN OUT NOCOPY CN_Rule_PUB.rule_rec_type
55 )
56 IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'Create_Rule';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_loading_status VARCHAR2(4000);
61 l_count NUMBER;
62 --SK l_rule_rec cn_rule_pvt.rule_rec_type;
63 l_rule_rec_pvt cn_rule_pvt.rule_rec_type;
64 l_bind_data_id NUMBER;
65 l_return_code VARCHAR2(1);
66
67
68 --
69 -- Declaration for user hooks
70 --
71 l_OAI_array JTF_USR_HKS.oai_data_array_type;
72 l_rule_rec CN_Rule_PUB.rule_rec_type;
73
74 l_rule_id cn_rules.rule_id%TYPE;
75
76 CURSOR parent_rules
77 (p_ruleset_id NUMBER,
78 p_rule_name cn_rules.name%TYPE,p_org_id cn_rules.org_id%TYPE )IS
79 SELECT rule_id
80 FROM cn_rules
81 WHERE ruleset_id = p_ruleset_id
82 AND name = p_rule_name
83 AND ORG_ID=p_org_id;
84
85 BEGIN
86
87 --
88 -- Standard Start of API savepoint
89 --
90 SAVEPOINT Create_Rule;
91
92 --
93 -- Standard call to check for call compatibility.
94 --
95 IF NOT FND_API.Compatible_API_Call ( l_api_version,
96 p_api_version,
97 l_api_name,
98 G_PKG_NAME )
99 THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.to_Boolean( p_init_msg_list )
105 THEN
106 FND_MSG_PUB.initialize;
107 END IF;
108
109 -- Initialize API return status to success
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 x_loading_status := 'CN_INSERTED';
112
113
114 --
115 -- Assign the parameter to a local variable
116 --
117 l_rule_rec := p_rule_rec;
118
119 --
120 -- User hooks
121 --
122
123 -- customer pre-processing section
124 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
125 'CREATE_RULE',
126 'B',
127 'C')
128 THEN
129 cn_rule_pub_cuhk.create_rule_pre
130 (p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 p_commit => p_commit,
133 p_validation_level => p_validation_level,
134 x_return_status => x_return_status,
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data,
137 x_loading_status => x_loading_status,
138 p_rule_rec => l_rule_rec);
139
140 IF x_return_status = fnd_api.g_ret_sts_error
141 THEN
142 RAISE fnd_api.g_exc_error;
143 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
144 THEN
145 RAISE fnd_api.g_exc_unexpected_error;
146 END IF;
147 END IF;
148
149 -- vertical industry pre-processing section
150 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
151 'CREATE_RULE',
152 'B',
153 'V')
154 THEN
155 cn_rule_pub_vuhk.create_rule_pre
156 (p_api_version => p_api_version,
157 p_init_msg_list => p_init_msg_list,
158 p_commit => p_commit,
159 p_validation_level => p_validation_level,
160 x_return_status => x_return_status,
161 x_msg_count => x_msg_count,
162 x_msg_data => x_msg_data,
163 x_loading_status => x_loading_status,
164 p_rule_rec => l_rule_rec);
165
166 IF x_return_status = fnd_api.g_ret_sts_error
167 THEN
168 RAISE fnd_api.g_exc_error;
169 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
170 THEN
171 RAISE fnd_api.g_exc_unexpected_error;
172 END IF;
173 END IF;
174
175
176 --
177 -- API body
178 --
179
180 --Check for null and missing parameters in the p_rule_rec parameter
181
182 IF (cn_api.chk_miss_null_char_para
183 ( l_rule_rec.ruleset_name,
184 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
185 x_loading_status,
186 x_loading_status) = FND_API.G_TRUE )
187 THEN
188 RAISE fnd_api.g_exc_error;
189 END IF;
190
191 IF cn_api.chk_miss_null_date_para
192 ( l_rule_rec.end_date,
193 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
194 x_loading_status,
195 x_loading_status) = fnd_api.g_true
196 THEN
197 RAISE fnd_api.g_exc_error;
198 END IF;
199
200 IF cn_api.chk_miss_null_date_para
201 (l_rule_rec.start_date,
202 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
203 x_loading_status,
204 x_loading_status)= fnd_api.g_true
205 THEN
206 RAISE fnd_api.g_exc_error;
207 END IF;
208
209 IF (cn_api.chk_miss_null_char_para
210 ( l_rule_rec.rule_name,
211 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
212 x_loading_status,
213 x_loading_status) = FND_API.G_TRUE )
214 THEN
215 RAISE fnd_api.g_exc_error;
216 END IF;
217 IF (cn_api.chk_miss_null_char_para
218 ( l_rule_rec.rule_name,
219 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
220 x_loading_status,
221 x_loading_status) = FND_API.G_TRUE )
222 THEN
223 RAISE fnd_api.g_exc_error;
224 END IF;
225 IF (cn_api.chk_miss_null_char_para
226 ( l_rule_rec.parent_rule_name,
227 cn_api.get_lkup_meaning('PARENT_RULE_NAME', 'RULESET_TYPE'),
228 x_loading_status,
229 x_loading_status) = FND_API.G_TRUE )
230 THEN
231 RAISE fnd_api.g_exc_error;
232 END IF;
233
234 --Now check if the ruleset exists.
235 SELECT count(1)
236 INTO l_count
237 FROM cn_rulesets
238 WHERE name = l_rule_rec.ruleset_name
239 AND start_date = l_rule_rec.start_date
240 AND end_date = l_rule_rec.end_date and
241 org_id=l_rule_rec.org_id;
242
243 IF l_count = 0
244 THEN
245 --Error condition
246 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
247 THEN
248 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
249 fnd_msg_pub.add;
250 END IF;
251 x_loading_status := 'CN_INVALID_RULESET';
252 RAISE FND_API.G_EXC_ERROR;
253 ELSE
254 SELECT ruleset_id
255 INTO l_rule_rec_pvt.ruleset_id
256 FROM cn_rulesets
257 WHERE name = l_rule_rec.ruleset_name
258 AND start_date = l_rule_rec.start_date
259 AND end_date = l_rule_rec.end_date and
260 org_id=l_rule_rec.org_id;
261 END IF;
262
263 IF l_rule_rec.revenue_class_name IS NOT NULL
264 THEN
265
266 --Now check if the revenue class exists.
267
268 SELECT count(1)
269 INTO l_count
270 FROM cn_revenue_classes
271 WHERE name = l_rule_rec.revenue_class_name;
272
273 IF l_count = 0
274 THEN
275 --Error condition
276 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
277 THEN
278 fnd_message.set_name('CN', 'CN_INVALID_REVENUE_CLASS');
279 fnd_msg_pub.add;
280 END IF;
281 x_loading_status := 'CN_INVALID_REVENUE_CLASS';
282 RAISE FND_API.G_EXC_ERROR;
283 ELSE
284 SELECT revenue_class_id
285 INTO l_rule_rec_pvt.revenue_class_id
286 FROM cn_revenue_classes
287 WHERE name = l_rule_rec.revenue_class_name;
288 END IF;
289 END IF;
290
291 -- Check if expense account is valid
292 IF l_rule_rec.expense_ccid IS NOT NULL
293 THEN
294
295 SELECT count(1)
296 INTO l_count
297 FROM gl_code_combinations
298 WHERE code_combination_id = l_rule_rec.expense_ccid;
299
300 IF l_count = 0
301 THEN
302 --Error condition
303 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
304 THEN
305 fnd_message.set_name('CN', 'CN_INVALID_EXPENSE_AC');
306 fnd_msg_pub.add;
307 END IF;
308 x_loading_status := 'CN_INVALID_EXPENSE_AC';
309 RAISE FND_API.G_EXC_ERROR;
310 ELSE
311 l_rule_rec_pvt.expense_ccid := l_rule_rec.expense_ccid;
312 END IF;
313 END IF;
314
315 -- Check if liability account is valid
316 IF l_rule_rec.liability_ccid IS NOT NULL
317 THEN
318
319 SELECT count(1)
320 INTO l_count
321 FROM gl_code_combinations
322 WHERE code_combination_id = l_rule_rec.liability_ccid;
323
324 IF l_count = 0
325 THEN
326 --Error condition
327 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
328 THEN
329 fnd_message.set_name('CN', 'CN_INVALID_LIABILITY_AC');
330 fnd_msg_pub.add;
331 END IF;
332 x_loading_status := 'CN_INVALID_LIABILITY_AC';
333 RAISE FND_API.G_EXC_ERROR;
334 ELSE
335 l_rule_rec_pvt.liability_ccid := l_rule_rec.liability_ccid;
336 END IF;
337 END IF;
338
339 l_rule_rec_pvt.ORG_ID:=l_rule_rec.ORG_ID;
340 --Validate the parent rule
341 SELECT count(1)
342 INTO l_count
343 FROM cn_rules
344 WHERE name = l_rule_rec.parent_rule_name
345 AND ruleset_id = l_rule_rec_pvt.ruleset_id
346 AND ORG_ID=l_rule_rec_pvt.ORG_ID;
347
348 IF l_count = 0
349 THEN
350 --Error condition
351 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
352 THEN
353 fnd_message.set_name('CN', 'CN_INVALID_PARENT_RULE');
354 fnd_msg_pub.add;
355 END IF;
356 x_loading_status := 'CN_INVALID_PARENT_RULE';
357 RAISE FND_API.G_EXC_ERROR;
358
359 END IF;
360
361
362 l_rule_rec_pvt.rule_name := l_rule_rec.rule_name;
363
364 FOR i IN parent_rules(l_rule_rec_pvt.ruleset_id,
365 l_rule_rec.parent_rule_name,
366 l_rule_rec.org_id)
367 LOOP
368
369 l_rule_rec_pvt.parent_rule_id := i.rule_id;
370
371 select cn_rules_s.nextval
372 into l_rule_rec_pvt.rule_id
373 from dual;
374
375 SELECT Nvl(MAX(sequence_number) + 1, 1)
376 INTO l_rule_rec_pvt.sequence_number
377 FROM cn_rules_hierarchy
378 WHERE ruleset_id = l_rule_rec_pvt.ruleset_id
379 AND parent_rule_id = l_rule_rec_pvt.parent_rule_id and
380 org_id=l_rule_rec_pvt.org_id;
381
382 cn_rule_pvt.create_rule
383 (p_api_version => p_api_version,
384 p_init_msg_list => p_init_msg_list,
385 p_commit => p_commit,
386 p_validation_level => p_validation_level,
387 x_return_status => x_return_status,
388 x_msg_count => x_msg_count,
389 x_msg_data => x_msg_data,
390 x_loading_status => x_loading_status,
391 x_rule_id => l_rule_id,
392 p_rule_rec => l_rule_rec_pvt);
393
394
395 IF x_return_status = fnd_api.g_ret_sts_error
396 THEN
397 RAISE fnd_api.g_exc_error;
398 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
399 THEN
400 RAISE fnd_api.g_exc_unexpected_error;
401 END IF;
402 END LOOP;
403
404 --
405 -- End of API body.
406 --
407
408
409 --
410 -- Post processing hooks
411 --
412
413 -- SK Start of post processing hooks
414
415 -- vertical post processing section
416 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
417 'CREATE_RULE',
418 'A',
419 'V')
420 THEN
421 cn_rule_pub_vuhk.create_rule_post
422 (p_api_version => p_api_version,
423 p_init_msg_list => p_init_msg_list,
424 p_commit => p_commit,
425 p_validation_level => p_validation_level,
426 x_return_status => x_return_status,
427 x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data,
429 x_loading_status => x_loading_status,
430 p_rule_rec => l_rule_rec);
431
432 IF x_return_status = fnd_api.g_ret_sts_error
433 THEN
434 RAISE fnd_api.g_exc_error;
435 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
436 THEN
437 RAISE fnd_api.g_exc_unexpected_error;
438 END IF;
439 END IF;
440
441 -- customer post processing section
442 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
443 'CREATE_RULE',
444 'A',
445 'C')
446 THEN
447 cn_rule_pub_cuhk.create_rule_post
448 (p_api_version => p_api_version,
449 p_init_msg_list => p_init_msg_list,
450 p_commit => p_commit,
451 p_validation_level => p_validation_level,
452 x_return_status => x_return_status,
453 x_msg_count => x_msg_count,
454 x_msg_data => x_msg_data,
455 x_loading_status => x_loading_status,
456 p_rule_rec => l_rule_rec);
457
458 IF x_return_status = fnd_api.g_ret_sts_error
459 THEN
460 RAISE fnd_api.g_exc_error;
461 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
462 THEN
463 RAISE fnd_api.g_exc_unexpected_error;
464 END IF;
465 END IF;
466 -- SK End of post processing hooks
467
468
469 -- Message generation section.
470 IF JTF_USR_HKS.Ok_to_execute('CN_RULE_PUB',
471 'CREATE_RULE',
472 'M',
473 'M')
474 THEN
475 IF cn_rule_pub_cuhk.ok_to_generate_msg
476 -- (p_rule_rec => l_rule_rec)
477 (p_rule_name => l_rule_rec.rule_name)
478
479 THEN
480 -- Clear bind variables
481 -- XMLGEN.clearBindValues;
482
483 -- Set values for bind variables,
484 -- call this for all bind variables in the business object
485 -- XMLGEN.setBindValue('RULE_NAME', l_rule_rec.rule_name);
486
487
488 -- get ID for all the bind_variables in a Business Object.
489 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
490
491 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULE_NAME', l_rule_rec.rule_name, 'S', 'T');
492
493 -- Message generation API
494 JTF_USR_HKS.generate_message
495 (p_prod_code => 'CN',
496 p_bus_obj_code => 'CRT_RULE',
497 p_action_code => 'I',
498 p_bind_data_id => l_bind_data_id,
499 x_return_code => l_return_code) ;
500
501 IF (l_return_code = FND_API.G_RET_STS_ERROR)
502 THEN
503 RAISE FND_API.G_EXC_ERROR;
504 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
505 THEN
506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508
509
510 /*
511 -- Message generation API
512 JTF_USR_HKS.generate_message
513 (p_prod_code => 'CN',
514 p_bus_obj_code => 'CRT_RULE',
515 p_bus_obj_name => 'RULE',
516 p_action_code => 'I',
517 p_oai_param => null,
518 p_oai_array => l_oai_array,
519 x_return_code => x_return_status) ;
520
521 IF (x_return_status = FND_API.G_RET_STS_ERROR)
522 THEN
523 RAISE FND_API.G_EXC_ERROR;
524 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
525 THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528
529 */
530
531
532 END IF;
533 END IF;
534
535
536 -- Standard check of p_commit.
537 IF FND_API.To_Boolean( p_commit )
538 THEN
539 COMMIT WORK;
540 END IF;
541
542 -- Standard call to get message count and if count is 1, get message info.
543 FND_MSG_PUB.Count_And_Get
544 (p_count => x_msg_count,
545 p_data => x_msg_data
546 );
547 EXCEPTION
548 WHEN FND_API.G_EXC_ERROR THEN
549 ROLLBACK TO Create_Rule;
550 x_return_status := FND_API.G_RET_STS_ERROR ;
551 FND_MSG_PUB.Count_And_Get
552 (p_count => x_msg_count,
553 p_data => x_msg_data,
554 p_encoded => fnd_api.g_false
555 );
556 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
557 ROLLBACK TO Create_Rule;
558 x_loading_status := 'UNEXPECTED_ERR';
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
560 FND_MSG_PUB.Count_And_Get
561 (p_count => x_msg_count,
562 p_data => x_msg_data,
563 p_encoded => fnd_api.g_false
564 );
565 WHEN OTHERS THEN
566 ROLLBACK TO Create_Rule;
567 x_loading_status := 'UNEXPECTED_ERR';
568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
569 IF FND_MSG_PUB.Check_Msg_Level
570 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
571 THEN
572 FND_MSG_PUB.Add_Exc_Msg
573 (G_PKG_NAME, l_api_name);
574 END IF;
575 FND_MSG_PUB.Count_And_Get
576 (p_count => x_msg_count,
577 p_data => x_msg_data,
578 p_encoded => fnd_api.g_false
579 );
580 END Create_Rule;
581
582 -- Start of comments
583 -- API name : Update_Rule
584 -- Type : Public
585 -- Function : This Public API can be used to update a rule,
586 -- a ruleset or rule attributes in Oracle Sales
587 -- Compensation.
588 -- Pre-reqs : None.
589 -- Parameters :
590 -- IN : p_api_version IN NUMBER Required
591 -- p_init_msg_list IN VARCHAR2 Optional
592 -- Default = FND_API.G_FALSE
593 -- p_commit IN VARCHAR2 Optional
594 -- Default = FND_API.G_FALSE
595 -- p_validation_level IN NUMBER Optional
596 -- Default = FND_API.G_VALID_LEVEL_FULL
597 -- p_rule_rec_type IN
598 -- CN_Rule_PUB.rule_rec_type
599 --
600 -- OUT : x_return_status OUT VARCHAR2(1)
601 -- x_msg_count OUT NUMBER
602 -- x_msg_data OUT VARCHAR2(2000)
603 --
604 -- Version : Current version 1.0
605 -- 25-Mar-99 Renu Chintalapati
606 -- previous version y.y
607 -- Changed....
608 -- Initial version 1.0
609 -- 25-Mar-99 Renu Chintalapati
610 --
611 -- Notes : Note text
612 --
613 -- End of comments
614
615
616 PROCEDURE Update_Rule
617 ( p_api_version IN NUMBER,
618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
619 p_commit IN VARCHAR2 := FND_API.G_FALSE,
620 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2,
624 x_loading_status OUT NOCOPY VARCHAR2,
625 p_old_rule_rec IN OUT NOCOPY CN_Rule_PUB.rule_rec_type,
626 p_rule_rec IN OUT NOCOPY CN_Rule_PUB.rule_rec_type
627 ) IS
628
629 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
630 l_api_version CONSTANT NUMBER := 1.0;
631 l_count NUMBER;
632 l_rule_rec_pvt cn_rule_pvt.rule_rec_type;
633 l_old_rule_rec_pvt cn_rule_pvt.rule_rec_type;
634
635
636
637 --
638 --Declaration for user hooks
639 --
640 l_OAI_array JTF_USR_HKS.oai_data_array_type;
641 l_old_rule_rec CN_Rule_PUB.rule_rec_type;
642 l_rule_rec CN_Rule_PUB.rule_rec_type;
643 l_bind_data_id NUMBER;
644 l_return_code VARCHAR2(1);
645
646
647 CURSOR get_rules(p_ruleset_id cn_rulesets.ruleset_id%TYPE,p_org_id cn_rulesets.org_id%TYPE) IS
648 SELECT cnrv.rule_id, cnrv.parent_rule_id
649 FROM cn_rules_v cnrv, cn_rules cnr1, cn_rules cnr2
650 WHERE cnr1.name = l_old_rule_rec.rule_name
651 AND cnr2.name = l_old_rule_rec.parent_rule_name
652 AND cnr1.ruleset_id = p_ruleset_id
653 AND cnr2.ruleset_id = p_ruleset_id
654 AND cnr1.rule_id = cnrv.rule_id
655 AND cnr2.rule_id = cnrv.parent_rule_id
656 and cnrv.org_id =cnr1.org_id
657 and cnr1.org_id = cnr2.org_id
658 and cnrv.org_id = p_org_id;
659
660
661 CURSOR parent_rules(p_ruleset_id cn_rulesets.ruleset_id%TYPE,p_org_id cn_rulesets.org_id%TYPE) IS
662 SELECT rule_id
663 FROM cn_rules
664 WHERE name = l_rule_rec.parent_rule_name
665 AND ruleset_id = p_ruleset_id
666 and org_id=p_org_id;
667
668
669 BEGIN
670
671 -- Standard Start of API savepoint
672 SAVEPOINT Update_Rule;
673 -- Standard call to check for call compatibility.
674 IF NOT FND_API.Compatible_API_Call ( l_api_version,
675 p_api_version,
676 l_api_name,
677 G_PKG_NAME )
678 THEN
679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
680 END IF;
681
682 -- Initialize message list if p_init_msg_list is set to TRUE.
683 IF FND_API.to_Boolean( p_init_msg_list )
684 THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688 -- Initialize API return status to success
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690 x_loading_status := 'CN_UPDATED';
691
692
693 --
694 -- Assign the parameter to a local variable
695 --
696 l_old_rule_rec := p_old_rule_rec;
697 l_rule_rec := p_rule_rec;
698
699
700 --
701 -- User hooks
702 --
703
704 -- customer pre-processing section
705 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
706 'UPDATE_RULE',
707 'B',
708 'C')
709 THEN
710 cn_rule_pub_cuhk.update_rule_pre
711 (p_api_version => p_api_version,
712 p_init_msg_list => p_init_msg_list,
713 p_commit => p_commit,
714 p_validation_level => p_validation_level,
715 x_return_status => x_return_status,
716 x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data,
718 x_loading_status => x_loading_status,
719 p_rule_rec => l_rule_rec,
720 p_old_rule_rec => l_old_rule_rec);
721
722 IF x_return_status = fnd_api.g_ret_sts_error
723 THEN
724 RAISE fnd_api.g_exc_error;
725 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
726 THEN
727 RAISE fnd_api.g_exc_unexpected_error;
728 END IF;
729 END IF;
730
731 -- vertical industry pre-processing section
732 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
733 'UPDATE_RULE',
734 'B',
735 'V')
736 THEN
737 cn_rule_pub_vuhk.update_rule_pre
738 (p_api_version => p_api_version,
739 p_init_msg_list => p_init_msg_list,
740 p_commit => p_commit,
741 p_validation_level => p_validation_level,
742 x_return_status => x_return_status,
743 x_msg_count => x_msg_count,
744 x_msg_data => x_msg_data,
745 x_loading_status => x_loading_status,
746 p_rule_rec => l_rule_rec,
747 p_old_rule_rec => l_old_rule_rec);
748
749 IF x_return_status = fnd_api.g_ret_sts_error
750 THEN
751 RAISE fnd_api.g_exc_error;
752 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
753 THEN
754 RAISE fnd_api.g_exc_unexpected_error;
755 END IF;
756 END IF;
757
758 --
759 -- API body
760 --
761
762 --Check for missing parameters in the p_rule_rec parameter
763
764 IF (cn_api.chk_miss_null_char_para
765 ( l_old_rule_rec.ruleset_name,
766 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
767 x_loading_status,
768 x_loading_status) = FND_API.G_TRUE )
769 THEN
770 RAISE fnd_api.g_exc_error;
771 END IF;
772 IF (cn_api.chk_miss_null_date_para
773 ( l_old_rule_rec.start_date,
774 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
775 x_loading_status,
776 x_loading_status) = FND_API.G_TRUE )
777 THEN
778 RAISE fnd_api.g_exc_error;
779 END IF;
780 IF (cn_api.chk_miss_null_date_para
781 ( l_old_rule_rec.end_date,
782 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
783 x_loading_status,
784 x_loading_status) = FND_API.G_TRUE )
785 THEN
786 RAISE fnd_api.g_exc_error;
787 END IF;
788 IF (cn_api.chk_miss_null_char_para
789 ( l_old_rule_rec.rule_name,
790 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
791 x_loading_status,
792 x_loading_status) = FND_API.G_TRUE )
793 THEN
794 RAISE fnd_api.g_exc_error;
795 END IF;
796 IF (cn_api.chk_miss_null_char_para
797 ( l_old_rule_rec.parent_rule_name,
798 cn_api.get_lkup_meaning('PARENT_RULE_NAME', 'RULESET_TYPE'),
799 x_loading_status,
800 x_loading_status) = FND_API.G_TRUE )
801 THEN
802 RAISE fnd_api.g_exc_error;
803 END IF;
804 IF (cn_api.chk_miss_null_char_para
805 ( l_rule_rec.ruleset_name,
806 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
807 x_loading_status,
808 x_loading_status) = FND_API.G_TRUE )
809 THEN
810 RAISE fnd_api.g_exc_error;
811 END IF;
812 IF (cn_api.chk_miss_null_date_para
813 ( l_old_rule_rec.start_date,
814 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
815 x_loading_status,
816 x_loading_status) = FND_API.G_TRUE )
817 THEN
818 RAISE fnd_api.g_exc_error;
819 END IF;
820 IF (cn_api.chk_miss_null_date_para
821 ( l_old_rule_rec.end_date,
822 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
823 x_loading_status,
824 x_loading_status) = FND_API.G_TRUE )
825 THEN
826 RAISE fnd_api.g_exc_error;
827 END IF;
828 IF (cn_api.chk_miss_null_char_para
829 ( l_rule_rec.rule_name,
830 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
831 x_loading_status,
832 x_loading_status) = FND_API.G_TRUE )
833 THEN
834 RAISE fnd_api.g_exc_error;
835 END IF;
836 IF (cn_api.chk_miss_null_char_para
837 ( l_rule_rec.parent_rule_name,
838 cn_api.get_lkup_meaning('PARENT_RULE_NAME', 'RULESET_TYPE'),
839 x_loading_status,
840 x_loading_status) = FND_API.G_TRUE )
841 THEN
842 RAISE fnd_api.g_exc_error;
843 END IF;
844
845 --Check if the old ruleset exists.
846 SELECT count(1)
847 INTO l_count
848 FROM cn_rulesets
849 WHERE name = l_old_rule_rec.ruleset_name
850 AND start_date = l_old_rule_rec.start_date
851 AND end_date = l_old_rule_rec.end_date;
852 IF l_count = 0
853 THEN
854 --Error condition
855 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
856 THEN
857 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
858 fnd_msg_pub.add;
859 END IF;
860 x_loading_status := 'CN_INVALID_RULESET';
861 RAISE FND_API.G_EXC_ERROR;
862 ELSE
863 SELECT ruleset_id,org_id
864 INTO l_old_rule_rec_pvt.ruleset_id,l_old_rule_rec_pvt.org_id
865 FROM cn_rulesets
866 WHERE name = l_old_rule_rec.ruleset_name
867 AND start_date = l_old_rule_rec.start_date
868 AND end_date = l_old_rule_rec.end_date;
869 END IF;
870
871 --Check if the new ruleset exists.
872 SELECT count(1)
873 INTO l_count
874 FROM cn_rulesets
875 WHERE name = l_rule_rec.ruleset_name
876 AND start_date = l_rule_rec.start_date
877 AND end_date = l_rule_rec.end_date;
878 IF l_count = 0
879 THEN
880 --Error condition
881 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
882 THEN
883 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
884 fnd_msg_pub.add;
885 END IF;
886 x_loading_status := 'CN_INVALID_RULESET';
887 RAISE FND_API.G_EXC_ERROR;
888 ELSE
889 SELECT ruleset_id,org_id
890 INTO l_rule_rec_pvt.ruleset_id,l_rule_rec_pvt.org_id
891 FROM cn_rulesets
892 WHERE name = l_rule_rec.ruleset_name
893 AND start_date = l_rule_rec.start_date
894 AND end_date = l_rule_rec.end_date;
895 END IF;
896
897 --Validate old parent rule
898 SELECT count(1)
899 INTO l_count
900 FROM cn_rules
901 WHERE name = l_old_rule_rec.parent_rule_name
902 AND ruleset_id = l_old_rule_rec_pvt.ruleset_id and
903 org_id=l_old_rule_rec_pvt.org_id;
904
905 IF l_count = 0
906 THEN
907 --Error condition
908 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
909 THEN
910 fnd_message.set_name('CN', 'CN_INVALID_RULE');
911 fnd_msg_pub.add;
912 END IF;
913 x_loading_status := 'CN_INVALID_RULE';
914 RAISE FND_API.G_EXC_ERROR;
915 END IF;
916
917 --Validate new parent rule
918 SELECT count(1)
919 INTO l_count
920 FROM cn_rules
921 WHERE name = l_rule_rec.parent_rule_name
922 AND ruleset_id = l_rule_rec_pvt.ruleset_id
923 and org_id=l_rule_rec_pvt.org_id;
924
925 IF l_count = 0
926 THEN
927 --Error condition
928 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
929 THEN
930 fnd_message.set_name('CN', 'CN_INVALID_RULE');
931 fnd_msg_pub.add;
932 END IF;
933 x_loading_status := 'CN_INVALID_RULE';
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 IF l_rule_rec.revenue_class_name IS NOT NULL
938 THEN
939 --Now check if the revenue class exists.
940
941 SELECT count(1)
942 INTO l_count
943 FROM cn_revenue_classes
944 WHERE name = l_rule_rec.revenue_class_name;
945
946 IF l_count = 0
947 THEN
948 --Error condition
949 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
950 THEN
951 fnd_message.set_name('CN', 'CN_INVALID_REVENUE_CLASS');
952 fnd_msg_pub.add;
953 END IF;
954 x_loading_status := 'CN_INVALID_REVENUE_CLASS';
955 RAISE FND_API.G_EXC_ERROR;
956 ELSE
957 SELECT revenue_class_id
958 INTO l_rule_rec_pvt.revenue_class_id
959 FROM cn_revenue_classes
960 WHERE name = l_rule_rec.revenue_class_name;
961 END IF;
962 END IF;
963
964 IF l_rule_rec.expense_ccid IS NOT NULL
965 THEN
966
967 SELECT count(1)
968 INTO l_count
969 FROM gl_code_combinations
970 WHERE code_combination_id = l_rule_rec.expense_ccid;
971
972 IF l_count = 0
973 THEN
974 --Error condition
975 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
976 THEN
977 fnd_message.set_name('CN', 'CN_INVALID_EXPENSE_CCID');
978 fnd_msg_pub.add;
979 END IF;
980 x_loading_status := 'CN_INVALID_EXPENSE_CCID';
981 RAISE FND_API.G_EXC_ERROR;
982 ELSE
983 l_rule_rec_pvt.expense_ccid := l_rule_rec.expense_ccid;
984 END IF;
985 END IF;
986
987 IF l_rule_rec.liability_ccid IS NOT NULL
988 THEN
989
990 SELECT count(1)
991 INTO l_count
992 FROM gl_code_combinations
993 WHERE code_combination_id = l_rule_rec.liability_ccid;
994
995 IF l_count = 0
996 THEN
997 --Error condition
998 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
999 THEN
1000 fnd_message.set_name('CN', 'CN_INVALID_LIABILITY_CCID');
1001 fnd_msg_pub.add;
1002 END IF;
1003 x_loading_status := 'CN_INVALID_LIABILITY_CCID';
1004 RAISE FND_API.G_EXC_ERROR;
1005 ELSE
1006 l_rule_rec_pvt.liability_ccid := l_rule_rec.liability_ccid;
1007 END IF;
1008 END IF;
1009
1010 l_old_rule_rec_pvt.rule_name := l_old_rule_rec.rule_name;
1011 l_rule_rec_pvt.rule_name := l_rule_rec.rule_name;
1012
1013 FOR i IN get_rules(l_old_rule_rec_pvt.ruleset_id,l_old_rule_rec_pvt.org_id)
1014 LOOP
1015 l_rule_rec_pvt.rule_id := i.rule_id;
1016 l_old_rule_rec_pvt.rule_id := i.rule_id;
1017 l_old_rule_rec_pvt.parent_rule_id := i.parent_rule_id;
1018
1019
1020 IF l_old_rule_rec.parent_rule_name <> l_rule_rec.parent_rule_name
1021 THEN
1022 FOR j IN parent_rules(l_rule_rec_pvt.ruleset_id,l_rule_rec_pvt.org_id)
1023 LOOP
1024 l_rule_rec_pvt.parent_rule_id := j.rule_id;
1025 cn_rule_pvt.update_rule
1026 (p_api_version => p_api_version,
1027 p_init_msg_list => p_init_msg_list,
1028 p_commit => p_commit,
1029 p_validation_level => p_validation_level,
1030 x_return_status => x_return_status,
1031 x_msg_count => x_msg_count,
1032 x_msg_data => x_msg_data,
1033 x_loading_status => x_loading_status,
1034 p_old_rule_rec => l_old_rule_rec_pvt,
1035 p_rule_rec => l_rule_rec_pvt);
1036 IF x_return_status = fnd_api.g_ret_sts_error
1037 THEN
1038 RAISE fnd_api.g_exc_error;
1039 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1040 THEN
1041 RAISE fnd_api.g_exc_unexpected_error;
1042 END IF;
1043 END LOOP;
1044 ELSE
1045 l_rule_rec_pvt.parent_rule_id := i.parent_rule_id;
1046 cn_rule_pvt.update_rule
1047 (p_api_version => p_api_version,
1048 p_init_msg_list => p_init_msg_list,
1049 p_commit => p_commit,
1050 p_validation_level => p_validation_level,
1051 x_return_status => x_return_status,
1052 x_msg_count => x_msg_count,
1053 x_msg_data => x_msg_data,
1054 x_loading_status => x_loading_status,
1055 p_old_rule_rec => l_old_rule_rec_pvt,
1056 p_rule_rec => l_rule_rec_pvt);
1057 IF x_return_status = fnd_api.g_ret_sts_error
1058 THEN
1059 RAISE fnd_api.g_exc_error;
1060 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1061 THEN
1062 RAISE fnd_api.g_exc_unexpected_error;
1063 END IF;
1064 END IF;
1065
1066 END LOOP;
1067
1068
1069 --
1070 -- End of API body.
1071 --
1072
1073
1074 --
1075 -- Post processing hooks
1076 --
1077
1078 -- SK Start of post processing hooks
1079
1080 -- vertical post processing section
1081 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1082 'UPDATE_RULE',
1083 'A',
1084 'V')
1085 THEN
1086 cn_rule_pub_vuhk.update_rule_post
1087 (p_api_version => p_api_version,
1088 p_init_msg_list => p_init_msg_list,
1089 p_commit => p_commit,
1090 p_validation_level => p_validation_level,
1091 x_return_status => x_return_status,
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data,
1094 x_loading_status => x_loading_status,
1095 p_rule_rec => l_rule_rec,
1096 p_old_rule_rec => l_old_rule_rec);
1097
1098 IF x_return_status = fnd_api.g_ret_sts_error
1099 THEN
1100 RAISE fnd_api.g_exc_error;
1101 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1102 THEN
1103 RAISE fnd_api.g_exc_unexpected_error;
1104 END IF;
1105 END IF;
1106
1107 -- customer post processing section
1108 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1109 'UPDATE_RULE',
1110 'A',
1111 'C')
1112 THEN
1113 cn_rule_pub_cuhk.update_rule_post
1114 (p_api_version => p_api_version,
1115 p_init_msg_list => p_init_msg_list,
1116 p_commit => p_commit,
1117 p_validation_level => p_validation_level,
1118 x_return_status => x_return_status,
1119 x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data,
1121 x_loading_status => x_loading_status,
1122 p_rule_rec => l_rule_rec,
1123 p_old_rule_rec => l_old_rule_rec);
1124
1125 IF x_return_status = fnd_api.g_ret_sts_error
1126 THEN
1127 RAISE fnd_api.g_exc_error;
1128 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1129 THEN
1130 RAISE fnd_api.g_exc_unexpected_error;
1131 END IF;
1132 END IF;
1133 -- SK End of post processing hooks
1134
1135
1136 -- Message generation section.
1137 IF JTF_USR_HKS.Ok_to_execute('CN_RULE_PUB',
1138 'UPDATE_RULE',
1139 'M',
1140 'M')
1141 THEN
1142 IF cn_rule_pub_cuhk.ok_to_generate_msg
1143 -- (p_rule_rec => l_rule_rec)
1144 (p_rule_name => l_rule_rec.rule_name)
1145 THEN
1146
1147 -- Clear bind variables
1148 -- XMLGEN.clearBindValues;
1149
1150 -- Set values for bind variables,
1151 -- call this for all bind variables in the business object
1152 -- XMLGEN.setBindValue('RULE_NAME', l_rule_rec.rule_name);
1153
1154 -- get ID for all the bind_variables in a Business Object.
1155 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1156
1157 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULE_NAME', l_rule_rec.rule_name, 'S', 'T');
1158
1159 -- Message generation API
1160 JTF_USR_HKS.generate_message
1161 (p_prod_code => 'CN',
1162 p_bus_obj_code => 'UPD_RULE',
1163 p_action_code => 'U',
1164 p_bind_data_id => l_bind_data_id,
1165 x_return_code => l_return_code) ;
1166
1167 IF (l_return_code = FND_API.G_RET_STS_ERROR)
1168 THEN
1169 RAISE FND_API.G_EXC_ERROR;
1170 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
1171 THEN
1172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1173 END IF;
1174
1175
1176 /*
1177 -- Message generation API
1178 JTF_USR_HKS.generate_message
1179 (p_prod_code => 'CN',
1180 p_bus_obj_code => 'UPD_RULE',
1181 p_bus_obj_name => 'RULE',
1182 p_action_code => 'U',
1183 p_oai_param => null,
1184 p_oai_array => l_oai_array,
1185 x_return_code => x_return_status) ;
1186
1187 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1188 THEN
1189 RAISE FND_API.G_EXC_ERROR;
1190 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1191 THEN
1192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1193 END IF;
1194 */
1195
1196 END IF;
1197 END IF;
1198
1199
1200
1201 -- Standard check of p_commit.
1202 IF FND_API.To_Boolean( p_commit )
1203 THEN
1204 COMMIT WORK;
1205 END IF;
1206
1207 -- Standard call to get message count and if count is 1, get message info.
1208 FND_MSG_PUB.Count_And_Get
1209 (p_count => x_msg_count,
1210 p_data => x_msg_data
1211 );
1212 EXCEPTION
1213 WHEN FND_API.G_EXC_ERROR THEN
1214 ROLLBACK TO Update_Rule;
1215 x_return_status := FND_API.G_RET_STS_ERROR ;
1216 FND_MSG_PUB.Count_And_Get
1217 (p_count => x_msg_count,
1218 p_data => x_msg_data,
1219 p_encoded => fnd_api.g_false
1220 );
1221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1222 ROLLBACK TO Update_Rule;
1223 x_loading_status := 'UNEXPECTED_ERR';
1224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1225 FND_MSG_PUB.Count_And_Get
1226 (p_count => x_msg_count,
1227 p_data => x_msg_data,
1228 p_encoded => fnd_api.g_false
1229 );
1230 WHEN OTHERS THEN
1231 ROLLBACK TO Update_Rule;
1232 x_loading_status := 'UNEXPECTED_ERR';
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1234 IF FND_MSG_PUB.Check_Msg_Level
1235 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1236 THEN
1237 FND_MSG_PUB.Add_Exc_Msg
1238 (G_PKG_NAME, l_api_name);
1239 END IF;
1240 FND_MSG_PUB.Count_And_Get
1241 (p_count => x_msg_count,
1242 p_data => x_msg_data,
1243 p_encoded => fnd_api.g_false
1244 );
1245
1246 END;
1247
1248 -- Start of comments
1249 -- API name : Delete_Rule
1250 -- Type : Private
1251 -- Function : This Public API can be used to delete a rule
1252 -- Pre-reqs : None.
1253 -- Parameters :
1254 -- IN : p_api_version IN NUMBER Required
1255 -- p_init_msg_list IN VARCHAR2 Optional
1256 -- Default = FND_API.G_FALSE
1257 -- p_commit IN VARCHAR2 Optional
1258 -- Default = FND_API.G_FALSE
1259 -- p_validation_level IN NUMBER Optional
1260 -- Default = FND_API.G_VALID_LEVEL_FULL
1261 -- p_rule_id IN NUMBER
1262 --
1263 -- OUT : x_return_status OUT VARCHAR2(1)
1264 -- x_msg_count OUT NUMBER
1265 -- x_msg_data OUT VARCHAR2(2000)
1266 --
1267 -- Version : Current version 1.0
1268 -- 25-Mar-99 Renu Chintalapati
1269 -- previous version y.y
1270 -- Changed....
1271 -- Initial version 1.0
1272 -- 25-Mar-99 Renu Chintalapati
1273 --
1274 -- Notes : This can be used to delete rules (and thus
1275 -- their rule attributes).
1276 -- Mandatory parameter is rule id
1277 --
1278 -- End of comments
1279
1280
1281 PROCEDURE Delete_Rule
1282 ( p_api_version IN NUMBER,
1283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1286 x_return_status OUT NOCOPY VARCHAR2,
1287 x_msg_count OUT NOCOPY NUMBER,
1288 x_msg_data OUT NOCOPY VARCHAR2,
1289 x_loading_status OUT NOCOPY VARCHAR2,
1290 p_rule_name IN cn_rules.name%TYPE,
1291 p_ruleset_name IN cn_rulesets.name%TYPE,
1292 p_ruleset_start_date IN cn_rulesets.start_date%TYPE,
1293 p_ruleset_end_date IN cn_rulesets.end_date%TYPE
1294 ) IS
1295
1296
1297 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
1298 l_api_version CONSTANT NUMBER := 1.0;
1299 l_count NUMBER;
1300 l_ruleset_id cn_rulesets.ruleset_id%TYPE;
1301 l_org_id cn_rulesets.org_id%TYPE;
1302
1303 CURSOR get_rules(p_ruleset_id cn_rulesets.ruleset_id%TYPE,p_org_id cn_rulesets.org_id%TYPE) IS
1304 SELECT rule_id
1305 FROM cn_rules
1306 WHERE ruleset_id = p_ruleset_id
1307 AND name = p_rule_name AND
1308 org_id=p_org_id;
1309
1310 --
1311 --Declaration for user hooks
1312 --
1313 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1314 l_rule_name cn_rules.name%TYPE;
1315 l_ruleset_name cn_rulesets.name%TYPE;
1316 l_ruleset_start_date cn_rulesets.start_date%TYPE;
1317 l_ruleset_end_date cn_rulesets.end_date%TYPE;
1318 l_bind_data_id NUMBER;
1319 l_return_code VARCHAR2(1);
1320
1321
1322 BEGIN
1323
1324
1325 -- Standard Start of API savepoint
1326 SAVEPOINT Delete_Rule;
1327 -- Standard call to check for call compatibility.
1328 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1329 p_api_version,
1330 l_api_name,
1331 G_PKG_NAME )
1332 THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335
1336 -- Initialize message list if p_init_msg_list is set to TRUE.
1337 IF FND_API.to_Boolean( p_init_msg_list )
1338 THEN
1339 FND_MSG_PUB.initialize;
1340 END IF;
1341
1342 -- Initialize API return status to success
1343 x_return_status := FND_API.G_RET_STS_SUCCESS;
1344 x_loading_status := 'CN_DELETED';
1345
1346
1347 --
1348 -- Assign the parameter to a local variable
1349 --
1350 l_rule_name := p_rule_name;
1351 l_ruleset_name := p_ruleset_name;
1352 l_ruleset_start_date := p_ruleset_start_date;
1353 l_ruleset_end_date := p_ruleset_end_date;
1354
1355 --
1356 -- User hooks
1357 --
1358
1359 -- customer pre-processing section
1360 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1361 'DELETE_RULE',
1362 'B',
1363 'C')
1364 THEN
1365 cn_rule_pub_cuhk.delete_rule_pre
1366 (p_api_version => p_api_version,
1367 p_init_msg_list => p_init_msg_list,
1368 p_commit => p_commit,
1369 p_validation_level => p_validation_level,
1370 x_return_status => x_return_status,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data,
1373 x_loading_status => x_loading_status,
1374 p_rule_name => l_rule_name,
1375 p_ruleset_name => l_ruleset_name,
1376 p_ruleset_start_date => l_ruleset_start_date,
1377 p_ruleset_end_date => l_ruleset_end_date);
1378
1379 IF x_return_status = fnd_api.g_ret_sts_error
1380 THEN
1381 RAISE fnd_api.g_exc_error;
1382 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1383 THEN
1384 RAISE fnd_api.g_exc_unexpected_error;
1385 END IF;
1386 END IF;
1387
1388 -- vertical industry pre-processing section
1389 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1390 'DELETE_RULE',
1391 'B',
1392 'V')
1393 THEN
1394 cn_rule_pub_vuhk.delete_rule_pre
1395 (p_api_version => p_api_version,
1396 p_init_msg_list => p_init_msg_list,
1397 p_commit => p_commit,
1398 p_validation_level => p_validation_level,
1399 x_return_status => x_return_status,
1400 x_msg_count => x_msg_count,
1401 x_msg_data => x_msg_data,
1402 x_loading_status => x_loading_status,
1403 p_rule_name => l_rule_name,
1404 p_ruleset_name => l_ruleset_name,
1405 p_ruleset_start_date => l_ruleset_start_date,
1406 p_ruleset_end_date => l_ruleset_end_date);
1407
1408 IF x_return_status = fnd_api.g_ret_sts_error
1409 THEN
1410 RAISE fnd_api.g_exc_error;
1411 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1412 THEN
1413 RAISE fnd_api.g_exc_unexpected_error;
1414 END IF;
1415 END IF;
1416
1417
1418 --
1419 -- API body
1420 --
1421 SELECT COUNT(1)
1422 INTO l_count
1423 FROM cn_rulesets
1424 WHERE name = l_ruleset_name
1425 AND start_date = l_ruleset_start_date
1426 AND end_date = l_ruleset_end_date;
1427
1428 IF l_count = 0
1429 THEN
1430 --Error condition
1431 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1432 THEN
1433 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
1434 fnd_msg_pub.add;
1435 END IF;
1436
1437 x_loading_status := 'CN_INVALID_RULESET';
1438 RAISE FND_API.G_EXC_ERROR;
1439 ELSE
1440 SELECT ruleset_id,org_id
1441 INTO l_ruleset_id,l_org_id
1442 FROM cn_rulesets
1443 WHERE name = l_ruleset_name
1444 AND start_date = l_ruleset_start_date
1445 AND end_date = l_ruleset_end_date;
1446 END IF;
1447
1448
1449 FOR i IN get_rules(l_ruleset_id,l_org_id)
1450 LOOP
1451 cn_rule_pvt.delete_rule
1452 (p_api_version => p_api_version,
1453 p_init_msg_list => p_init_msg_list,
1454 p_commit => p_commit,
1455 p_validation_level => p_validation_level,
1456 x_return_status => x_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data,
1459 x_loading_status => x_loading_status,
1460 p_ruleset_id => l_ruleset_id,
1461 p_rule_id => i.rule_id,
1462 p_org_id => l_org_id);
1463 IF x_return_status = fnd_api.g_ret_sts_error
1464 THEN
1465 RAISE fnd_api.g_exc_error;
1466 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1467 THEN
1468 RAISE fnd_api.g_exc_unexpected_error;
1469 END IF;
1470 END LOOP;
1471
1472 --
1473 -- End of API body.
1474 --
1475
1476
1477 --
1478 -- Post processing hooks
1479 --
1480
1481 -- SK Start of post processing hooks
1482
1483 -- vertical post processing section
1484 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1485 'DELETE_RULE',
1486 'A',
1487 'V')
1488 THEN
1489 cn_rule_pub_vuhk.delete_rule_post
1490 (p_api_version => p_api_version,
1491 p_init_msg_list => p_init_msg_list,
1492 p_commit => p_commit,
1493 p_validation_level => p_validation_level,
1494 x_return_status => x_return_status,
1495 x_msg_count => x_msg_count,
1496 x_msg_data => x_msg_data,
1497 x_loading_status => x_loading_status,
1498 p_rule_name => l_rule_name,
1499 p_ruleset_name => l_ruleset_name,
1500 p_ruleset_start_date => l_ruleset_start_date,
1501 p_ruleset_end_date => l_ruleset_end_date);
1502
1503 IF x_return_status = fnd_api.g_ret_sts_error
1504 THEN
1505 RAISE fnd_api.g_exc_error;
1506 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1507 THEN
1508 RAISE fnd_api.g_exc_unexpected_error;
1509 END IF;
1510 END IF;
1511
1512 -- customer post processing section
1513 IF JTF_USR_HKS.Ok_to_Execute('CN_RULE_PUB',
1514 'DELETE_RULE',
1515 'A',
1516 'C')
1517 THEN
1518 cn_rule_pub_cuhk.delete_rule_post
1519 (p_api_version => p_api_version,
1520 p_init_msg_list => p_init_msg_list,
1521 p_commit => p_commit,
1522 p_validation_level => p_validation_level,
1523 x_return_status => x_return_status,
1524 x_msg_count => x_msg_count,
1525 x_msg_data => x_msg_data,
1526 x_loading_status => x_loading_status,
1527 p_rule_name => l_rule_name,
1528 p_ruleset_name => l_ruleset_name,
1529 p_ruleset_start_date => l_ruleset_start_date,
1530 p_ruleset_end_date => l_ruleset_end_date);
1531
1532 IF x_return_status = fnd_api.g_ret_sts_error
1533 THEN
1534 RAISE fnd_api.g_exc_error;
1535 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1536 THEN
1537 RAISE fnd_api.g_exc_unexpected_error;
1538 END IF;
1539 END IF;
1540 -- SK End of post processing hooks
1541
1542
1543 -- Message generation section.
1544 IF JTF_USR_HKS.Ok_to_execute('CN_RULE_PUB',
1545 'DELETE_RULE',
1546 'M',
1547 'M')
1548 THEN
1549 IF cn_rule_pub_cuhk.ok_to_generate_msg
1550 (p_rule_name => l_rule_name)
1551 THEN
1552 -- Clear bind variables
1553 -- XMLGEN.clearBindValues;
1554
1555 -- Set values for bind variables,
1556 -- call this for all bind variables in the business object
1557 -- XMLGEN.setBindValue('RULE_NAME', l_rule_name);
1558
1559 -- get ID for all the bind_variables in a Business Object.
1560 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1561
1562 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULE_NAME', l_rule_name, 'S', 'T');
1563
1564 -- Message generation API
1565 JTF_USR_HKS.generate_message
1566 (p_prod_code => 'CN',
1567 p_bus_obj_code => 'DEL_RULE',
1568 p_action_code => 'D',
1569 p_bind_data_id => l_bind_data_id,
1570 x_return_code => l_return_code) ;
1571
1572 IF (l_return_code = FND_API.G_RET_STS_ERROR)
1573 THEN
1574 RAISE FND_API.G_EXC_ERROR;
1575 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
1576 THEN
1577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1578 END IF;
1579
1580 /*
1581 -- Message generation API
1582 JTF_USR_HKS.generate_message
1583 (p_prod_code => 'CN',
1584 p_bus_obj_code => 'DEL_RULE',
1585 p_bus_obj_name => 'RULE',
1586 p_action_code => 'D',
1587 p_oai_param => null,
1588 p_oai_array => l_oai_array,
1589 x_return_code => x_return_status) ;
1590
1591 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1592 THEN
1593 RAISE FND_API.G_EXC_ERROR;
1594 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1595 THEN
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1597 END IF;
1598 */
1599 END IF;
1600 END IF;
1601
1602
1603
1604 -- Standard check of p_commit.
1605 IF FND_API.To_Boolean( p_commit )
1606 THEN
1607 COMMIT WORK;
1608 END IF;
1609
1610 -- Standard call to get message count and if count is 1, get message info.
1611 FND_MSG_PUB.Count_And_Get
1612 (p_count => x_msg_count,
1613 p_data => x_msg_data
1614 );
1615 EXCEPTION
1616 WHEN FND_API.G_EXC_ERROR THEN
1617 ROLLBACK TO Delete_Rule;
1618 x_return_status := FND_API.G_RET_STS_ERROR ;
1619 FND_MSG_PUB.Count_And_Get
1620 (p_count => x_msg_count,
1621 p_data => x_msg_data,
1622 p_encoded => fnd_api.g_false
1623 );
1624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1625 ROLLBACK TO Delete_Rule;
1626 x_loading_status := 'UNEXPECTED_ERR';
1627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1628 FND_MSG_PUB.Count_And_Get
1629 (p_count => x_msg_count,
1630 p_data => x_msg_data,
1631 p_encoded => fnd_api.g_false
1632 );
1633 WHEN OTHERS THEN
1634 ROLLBACK TO Delete_Rule;
1635 x_loading_status := 'UNEXPECTED_ERR';
1636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1637 IF FND_MSG_PUB.Check_Msg_Level
1638 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1639 THEN
1640 FND_MSG_PUB.Add_Exc_Msg
1641 (G_PKG_NAME, l_api_name );
1642 END IF;
1643 FND_MSG_PUB.Count_And_Get
1644 (p_count => x_msg_count,
1645 p_data => x_msg_data,
1646 p_encoded => fnd_api.g_false
1647 );
1648 END;
1649
1650 ---------------------------+
1651 --
1652 -- This is called from RuleLOV.java to display the entire hierarchy for the given rule
1653 --
1654 ---------------------------+
1655
1656 function getRuleHierStr (p_rule_id NUMBER, p_ruleset_id NUMBER) RETURN VARCHAR2 IS
1657 cursor c_rules is
1658 select distinct(name) ruleName
1659 from cn_rules
1660 where rule_id in (
1661 select PARENT_RULE_ID
1662 from cn_rules_hierarchy
1663 where ruleset_id = p_ruleset_id
1664 connect by prior PARENT_RULE_ID = rule_id
1665 start with rule_id = p_rule_id);
1666
1667 cursor c_rule_name is
1668 select name
1669 from cn_rules
1670 where rule_id = p_rule_id;
1671
1672 retStr VARCHAR2(30000);
1673 ruleName cn_rules.name%TYPE;
1674 i integer := 0;
1675 begin
1676 open c_rule_name;
1677 fetch c_rule_name into ruleName;
1678 close c_rule_name;
1679
1680 retStr := ruleName || ' (';
1681 for rule in c_rules loop
1682 IF rule.ruleName <> 'BASE_RULE' THEN
1683 IF i = 0 THEN
1684 retStr := retStr || rule.ruleName;
1685 ELSE
1686 retStr := retStr || ' -> ' || rule.ruleName;
1687 END IF;
1688
1689 i := i + 1;
1690 end if;
1691 end loop;
1692
1693 retStr := retStr || ')';
1694
1695 IF i <= 0 THEN
1696 retStr := ruleName;
1697 END IF;
1698
1699 return retStr;
1700 end getRuleHierStr;
1701
1702
1703 END CN_Rule_PUB;