[Home] [Help]
PACKAGE BODY: APPS.CN_RULE_PVT
Source
1 PACKAGE BODY CN_Rule_PVT AS
2 --$Header: cnvruleb.pls 120.5 2006/03/07 04:57:39 hanaraya ship $
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Rule_PVT';
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 --| Procedure : valid_Rule
13 --| Desc : Procedure to validate Rules
14 --+==========================================================================
15 PROCEDURE valid_Rule
16 (
17 x_return_status OUT NOCOPY VARCHAR2 ,
18 x_msg_count OUT NOCOPY NUMBER ,
19 x_msg_data OUT NOCOPY VARCHAR2 ,
20 p_rule_rec IN CN_Rule_PVT.rule_rec_type,
21 p_action IN VARCHAR2,
22 p_loading_status IN VARCHAR2,
23 x_loading_status OUT NOCOPY VARCHAR2
24 )
25 IS
26 l_api_name CONSTANT VARCHAR2(30) := 'valid_Rule';
27
28 cursor get_rulesets_rec is
29 select module_type
30 from cn_rulesets
31 where ruleset_id = p_rule_rec.ruleset_id and
32 org_id=p_rule_rec.org_id;
33
34 l_ruleset_rec get_rulesets_rec%ROWTYPE;
35
36 BEGIN
37 -- Initialize API return status to success
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39 x_loading_status := p_loading_status;
40 -- API body
41
42 open get_rulesets_rec;
43 fetch get_rulesets_rec into l_ruleset_rec;
44 close get_rulesets_rec;
45
46 /* if l_ruleset_rec.module_type = 'REVCLS' THEN
47
48 IF p_rule_rec.revenue_class_id is NULL THEN
49 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
50 FND_MESSAGE.Set_Name('CN', 'CN_INVALID_REVENUE_CLASS');
51 FND_MSG_PUB.Add;
52 END IF;
53 x_loading_status := 'CN_INVALID_REVENUE_CLASS';
54 RAISE FND_API.G_EXC_ERROR ;
55 END IF;
56 null;
57 else
58 IF p_rule_rec.expense_ccid is NULL OR
59 p_rule_rec.liability_ccid IS NULL THEN
60 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
61 FND_MESSAGE.Set_Name('CN', 'CN_INVALID_ACCOUNT_CODE');
62 FND_MSG_PUB.Add;
63 END IF;
64 x_loading_status := 'CN_INVALID_ACCOUNT_CODE';
65 RAISE FND_API.G_EXC_ERROR ;
66 END IF;
67 end if;
68 */
69
70
71
72
73 -- End of API body.
74 EXCEPTION
75 WHEN FND_API.G_EXC_ERROR THEN
76 x_return_status := FND_API.G_RET_STS_ERROR ;
77 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
79 x_loading_status := 'UNEXPECTED_ERR';
80 WHEN OTHERS THEN
81 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
82 x_loading_status := 'UNEXPECTED_ERR';
83 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
84 THEN
85 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
86 END IF;
87
88 END valid_rule;
89 --=============================================================================
90 -- Procedure : validate_rule_name
91 -- Desc : Validates the rule name. The rule should be unique at
92 -- the sibling level and within the branch
93 --=============================================================================
94
95 FUNCTION validate_rule_name
96 (p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
97 p_parent_rule_id IN cn_rules.rule_id%TYPE,
98 p_rule_name IN cn_rules.name%TYPE,
99 p_org_id IN cn_rules.org_id%TYPE,
100 p_loading_status IN VARCHAR2,
101 x_loading_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
102
103 CURSOR check_siblings_cur IS
104 SELECT count(*) cnt
105 FROM cn_rules_hierarchy cnrh,
106 cn_rules cnr
107 WHERE cnrh.parent_rule_id = p_parent_rule_id
108 AND cnr.ruleset_id = p_ruleset_id
109 AND cnrh.rule_id = cnr.rule_id
110 AND cnr.name = p_rule_name
111 AND cnrh.org_id=cnr.org_id
112 AND cnr.org_id=p_org_id;
113
114 l_check_siblings_rec check_siblings_cur%ROWTYPE;
115
116
117 CURSOR check_parents_cur (p_parent_rule_id cn_rules.rule_id%TYPE)IS
118 SELECT cnrh.parent_rule_id,
119 cnr.name
120 FROM cn_rules_hierarchy cnrh,
121 cn_rules cnr
122 WHERE cnr.ruleset_id = p_ruleset_id
123 AND cnrh.rule_id = p_parent_rule_id
124 AND cnrh.rule_id = cnr.rule_id
125 AND cnrh.org_id=cnr.org_id
126 AND cnr.org_id=p_org_id;
127
128 l_check_parents_rec check_parents_cur%ROWTYPE;
129
130 l_current_parent_rule_id cn_rules_hierarchy.parent_rule_id%TYPE;
131
132 l_api_name VARCHAR2(30) := 'validate_rule_name';
133
134 BEGIN
135
136 x_loading_status := p_loading_status;
137
138 OPEN check_siblings_cur;
139 FETCH check_siblings_cur INTO l_check_siblings_rec;
140 CLOSE check_siblings_cur;
141
142 IF l_check_siblings_rec.cnt > 0
143 THEN
144 --Error condition
145 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
146 THEN
147 fnd_message.set_name('CN', 'CN_DUPLICATE_RULE_NAME');
148 fnd_msg_pub.add;
149 END IF;
150 x_loading_status := 'CN_DUPLICATE_RULE_NAME';
151 RAISE FND_API.G_EXC_ERROR;
152 ELSE
153 l_current_parent_rule_id := p_parent_rule_id;
154 WHILE l_current_parent_rule_id <> -1002
155 LOOP
156 OPEN check_parents_cur(l_current_parent_rule_id);
157 FETCH check_parents_cur INTO l_check_parents_rec;
158 CLOSE check_parents_cur;
159 l_current_parent_rule_id := l_check_parents_rec.parent_rule_id;
160 IF l_check_parents_rec.name = p_rule_name
161 THEN
162 --Error condition
163 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
164 THEN
165 fnd_message.set_name('CN', 'CN_DUPLICATE_RULE_NAME');
166 fnd_msg_pub.add;
167 END IF;
168 x_loading_status := 'CN_DUPLICATE_RULE_NAME';
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171 END LOOP;
172 END IF;
173 RETURN fnd_api.g_false;
174 EXCEPTION
175 WHEN FND_API.G_EXC_ERROR THEN
176 RETURN fnd_api.g_true;
177
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 x_loading_status := 'UNEXPECTED_ERR';
180 RETURN fnd_api.g_true;
181
182 WHEN OTHERS THEN
183 x_loading_status := 'UNEXPECTED_ERR';
184 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
185 THEN
186 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
187 END IF;
188 RETURN fnd_api.g_true;
189 END;
190 --=============================================================================
191 -- Start of comments
192 -- API name : Create_Rule
193 -- Type : Public
194 -- Function : This Public API can be used to create a rule
195 -- Pre-reqs : None.
196 -- Parameters :
197 -- IN : p_api_version IN NUMBER Required
198 -- p_init_msg_list IN VARCHAR2 Optional
199 -- Default = FND_API.G_FALSE
200 -- p_commit IN VARCHAR2 Optional
201 -- Default = FND_API.G_FALSE
202 -- p_validation_level IN NUMBER Optional
203 -- Default = FND_API.G_VALID_LEVEL_FULL
204 -- p_rule_rec IN
205 -- CN_Rule_PVT.rule_rec_type
206 --
207 -- OUT : x_return_status OUT VARCHAR2(1)
208 -- x_msg_count OUT NUMBER
209 -- x_msg_data OUT VARCHAR2(2000)
210 --
211 -- Version : Current version 1.0
212 -- 25-Mar-99 Renu Chintalapati
213 -- previous version y.y
214 -- Changed....
215 -- Initial version 1.0
216 -- 25-Mar-99 Renu Chintalapati
217 --
218 -- Notes : Note text
219 --
220 -- End of comments
221 ---=============================================================================
222 PROCEDURE Create_Rule
223 ( p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
225 p_commit IN VARCHAR2 := FND_API.G_FALSE,
226 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 x_loading_status OUT NOCOPY VARCHAR2,
231 p_rule_rec IN OUT NOCOPY CN_Rule_PVT.rule_rec_type,
232 x_rule_id OUT NOCOPY NUMBER
233 )
234 IS
235
236 l_api_name CONSTANT VARCHAR2(30) := 'Create_Rule';
237 l_api_version CONSTANT NUMBER := 1.0;
238 l_loading_status VARCHAR2(4000);
239 l_rowid VARCHAR2(4000);
240 l_sequence_number NUMBER;
241 l_count NUMBER;
242 l_rule_id NUMBER;
243
244 l_ruleset_status VARCHAR2(100);
245
246 BEGIN
247
248 -- Standard Start of API savepoint
249 SAVEPOINT Create_Rule;
250 -- Standard call to check for call compatibility.
251 IF NOT FND_API.Compatible_API_Call ( l_api_version,
252 p_api_version,
253 l_api_name,
254 G_PKG_NAME )
255 THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259 -- Initialize message list if p_init_msg_list is set to TRUE.
260 IF FND_API.to_Boolean( p_init_msg_list )
261 THEN
262 FND_MSG_PUB.initialize;
263 END IF;
264
265 -- Initialize API return status to success
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267 x_loading_status := 'CN_INSERTED';
268 -- API body
269
270 --Check for null and missing parameters in the p_rule_rec parameter
271
272 /* IF (cn_api.chk_null_num_para
273 ( p_rule_rec.ruleset_id,
274 cn_api.get_lkup_meaning('RULESET_ID', 'RULESET_TYPE'),
275 x_loading_status,
276 x_loading_status) = FND_API.G_TRUE )
277 THEN
278 RAISE fnd_api.g_exc_error;
279 END IF;
280
281 IF (cn_api.chk_null_num_para
282 ( p_rule_rec.rule_id,
283 cn_api.get_lkup_meaning('RULE_ID', 'RULESET_TYPE'),
284 x_loading_status,
285 x_loading_status) = FND_API.G_TRUE )
286 THEN
287 RAISE fnd_api.g_exc_error;
288 END IF;
289
290 */
291
292 IF (cn_api.chk_null_char_para
293 ( p_rule_rec.rule_name,
294 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
295 x_loading_status,
296 x_loading_status) = FND_API.G_TRUE )
297 THEN
298 RAISE fnd_api.g_exc_error;
299 END IF;
300 IF (cn_api.chk_null_num_para
301 ( p_rule_rec.parent_rule_id,
302 cn_api.get_lkup_meaning('PARENT_RULE_ID', 'RULESET_TYPE'),
303 x_loading_status,
304 x_loading_status) = FND_API.G_TRUE )
305 THEN
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 /* IF (cn_api.chk_null_num_para
310 ( p_rule_rec.parent_rule_id,
311 cn_api.get_lkup_meaning('SEQUENCE_NUMBER', 'RULESET_TYPE'),
312 x_loading_status,
313 x_loading_status) = FND_API.G_TRUE )
314 THEN
315 RAISE fnd_api.g_exc_error;
316 END IF;
317 */
318
319 --Now check if the ruleset exists.
320 SELECT count(1)
321 INTO l_count
322 FROM cn_rulesets
323 WHERE ruleset_id = p_rule_rec.ruleset_id
324 AND org_id= p_rule_rec.org_id;
325
326 IF l_count = 0
327 THEN
328 --Error condition
329 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
330 THEN
331 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
332 fnd_msg_pub.add;
333 END IF;
334 x_loading_status := 'CN_INVALID_RULESET';
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
338 --Validate the parent rule
339 SELECT count(1)
340 INTO l_count
341 FROM cn_rules
342 WHERE rule_id = p_rule_rec.parent_rule_id and
343 org_id=p_rule_rec.org_id;
344
345 --Fetch rule_id into l_rule_id
346 SELECT Decode(p_rule_rec.rule_id, NULL,cn_rules_s.NEXTVAL, p_rule_rec.rule_id)
347 INTO l_rule_id
348 FROM dual;
349
350 --Since this is a new rule, validate the rule name before inserting
351 IF validate_rule_name(p_rule_rec.ruleset_id,
352 p_rule_rec.parent_rule_id,
353 p_rule_rec.rule_name,
354 p_rule_rec.org_id,
355 x_loading_status,
356 x_loading_status) = fnd_api.g_true
357 THEN
358 --Error condition
359 /*IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
360 THEN
361 fnd_message.set_name('CN', 'CN_INVALID_RULE_NAME');
362 fnd_msg_pub.add;
363 END IF;
364 */
365 x_loading_status := 'CN_INVALID_RULE_NAME';
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 IF p_rule_rec.sequence_number IS NULL
370 then
371 SELECT nvl(MAX(nvl(sequence_number, 0)),0) + 1
372 INTO l_sequence_number
373 FROM cn_rules_hierarchy
374 WHERE ruleset_id = p_rule_rec.ruleset_id;
375 ELSE
376 l_sequence_number := p_rule_rec.sequence_number;
377 END IF;
378
379 --
380 -- Valid Validate Rule
381 --
382 valid_rule
383 ( x_return_status => x_return_status,
384 x_msg_count => x_msg_count,
385 x_msg_data => x_msg_data,
386 p_rule_rec => p_rule_rec,
387 p_action => 'CREATE',
388 p_loading_status => x_loading_status,
389 x_loading_status => x_loading_status
390 );
391 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
392 RAISE FND_API.G_EXC_ERROR ;
393 END IF;
394
395
396 cn_syin_rules_pkg.insert_row(l_rule_id,
397 p_rule_rec.rule_name,
398 p_rule_rec.ruleset_id,
399 p_rule_rec.revenue_class_id,
400 p_rule_rec.expense_ccid,
401 p_rule_rec.liability_ccid,
402 p_rule_rec.parent_rule_id,
403 l_sequence_number,
404 p_rule_rec.org_id);
405
406 x_rule_id := l_rule_id;
407
408 cn_rulesets_pkg.Unsync_ruleset(x_ruleset_id_in => p_rule_rec.ruleset_id,
409 x_ruleset_status_in => l_ruleset_status,
410 x_org_id => p_rule_rec.org_id);
411
412 -- End of API body.
413
414 -- Standard check of p_commit.
415 IF FND_API.To_Boolean( p_commit )
416 THEN
417 COMMIT WORK;
418 END IF;
419
420 -- Standard call to get message count and if count is 1, get message info.
421 FND_MSG_PUB.Count_And_Get
422 (p_count => x_msg_count,
423 p_data => x_msg_data
424 );
425 EXCEPTION
426 WHEN FND_API.G_EXC_ERROR THEN
427 ROLLBACK TO Create_Rule;
428 x_return_status := FND_API.G_RET_STS_ERROR ;
429 FND_MSG_PUB.Count_And_Get
430 (p_count => x_msg_count,
431 p_data => x_msg_data,
432 p_encoded => fnd_api.g_false
433 );
434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
435 ROLLBACK TO Create_Rule;
436 x_loading_status := 'UNEXPECTED_ERR';
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
438 FND_MSG_PUB.Count_And_Get
439 (p_count => x_msg_count,
440 p_data => x_msg_data,
441 p_encoded => fnd_api.g_false
442 );
443 WHEN OTHERS THEN
444 ROLLBACK TO Create_Rule;
445 x_loading_status := 'UNEXPECTED_ERR';
446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
447 IF FND_MSG_PUB.Check_Msg_Level
448 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
449 THEN
450 FND_MSG_PUB.Add_Exc_Msg
451 (G_PKG_NAME, l_api_name);
452 END IF;
453 FND_MSG_PUB.Count_And_Get
454 (p_count => x_msg_count,
455 p_data => x_msg_data,
456 p_encoded => fnd_api.g_false
457 );
458 END Create_Rule;
459 --=============================================================================
460 -- Start of comments
461 -- API name : Update_Rule
462 -- Type : Public
463 -- Function : This Public API can be used to update a rule,
464 -- a ruleset or rule attributes in Oracle Sales
465 -- Compensation.
466 -- Pre-reqs : None.
467 -- Parameters :
468 -- IN : p_api_version IN NUMBER Required
469 -- p_init_msg_list IN VARCHAR2 Optional
470 -- Default = FND_API.G_FALSE
471 -- p_commit IN VARCHAR2 Optional
472 -- Default = FND_API.G_FALSE
473 -- p_validation_level IN NUMBER Optional
474 -- Default = FND_API.G_VALID_LEVEL_FULL
475 -- p_rule_rec_type IN
476 -- CN_Rule_PVT.rule_rec_type
477 --
478 -- OUT : x_return_status OUT VARCHAR2(1)
479 -- x_msg_count OUT NUMBER
480 -- x_msg_data OUT VARCHAR2(2000)
481 --
482 -- Version : Current version 1.0
483 -- 25-Mar-99 Renu Chintalapati
484 -- previous version y.y
485 -- Changed....
486 -- Initial version 1.0
487 -- 25-Mar-99 Renu Chintalapati
488 --
489 -- Notes : Note text
490 --
491 -- End of comments
492 --=============================================================================
493
494 PROCEDURE Update_Rule
495 ( p_api_version IN NUMBER,
496 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
497 p_commit IN VARCHAR2 := FND_API.G_FALSE,
498 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
499 x_return_status OUT NOCOPY VARCHAR2,
500 x_msg_count OUT NOCOPY NUMBER,
501 x_msg_data OUT NOCOPY VARCHAR2,
502 x_loading_status OUT NOCOPY VARCHAR2,
503 p_old_rule_rec IN CN_Rule_PVT.rule_rec_type,
504 p_rule_rec IN OUT NOCOPY CN_Rule_PVT.rule_rec_type
505 ) IS
506
507 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_rowid ROWID;
510 l_sequence_number NUMBER;
511 l_count NUMBER;
512 l_ruleset_status VARCHAR2(100);
513 l_object_version_number NUMBER;
514
515 BEGIN
516
517 -- Standard Start of API savepoint
518 SAVEPOINT Update_Rule;
519 -- Standard call to check for call compatibility.
520 IF NOT FND_API.Compatible_API_Call ( l_api_version,
521 p_api_version,
522 l_api_name,
523 G_PKG_NAME )
524 THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529 IF FND_API.to_Boolean( p_init_msg_list )
530 THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533
534 -- Initialize API return status to success
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536 x_loading_status := 'CN_UPDATED';
537
538 -- API body
539
540 -- Check for object version number mismatch
541 select object_version_number into l_object_version_number
542 from cn_rules_all where rule_id = p_old_rule_rec.rule_id
543 and org_id = p_old_rule_rec.org_id;
544
545 if (l_object_version_number <> p_rule_rec.object_version_no) then
546 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
547 fnd_msg_pub.add;
548 raise fnd_api.g_exc_error;
549 end if;
550
551 -- end Check for object version number mismatch
552
553
554
555 --Check for missing parameters in the p_rule_rec parameter
556
557 IF (cn_api.chk_null_num_para
558 ( p_old_rule_rec.ruleset_id,
559 cn_api.get_lkup_meaning('RULESET_ID', 'RULESET_TYPE'),
560 x_loading_status,
561 x_loading_status) = FND_API.G_TRUE )
562 THEN
563 RAISE fnd_api.g_exc_error;
564 END IF;
565 IF (cn_api.chk_null_num_para
566 ( p_old_rule_rec.rule_id,
567 cn_api.get_lkup_meaning('RULE_ID', 'RULESET_TYPE'),
568 x_loading_status,
569 x_loading_status) = FND_API.G_TRUE )
570 THEN
571 RAISE fnd_api.g_exc_error;
572 END IF;
573 IF (cn_api.chk_null_char_para
574 ( p_old_rule_rec.rule_name,
575 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
576 x_loading_status,
577 x_loading_status) = FND_API.G_TRUE )
578 THEN
579 RAISE fnd_api.g_exc_error;
580 END IF;
581 IF (cn_api.chk_null_num_para
582 ( p_old_rule_rec.parent_rule_id,
583 cn_api.get_lkup_meaning('PARENT_RULE_ID', 'RULESET_TYPE'),
584 x_loading_status,
585 x_loading_status) = FND_API.G_TRUE )
586 THEN
587 RAISE fnd_api.g_exc_error;
588 END IF;
589 IF (cn_api.chk_null_num_para
590 ( p_old_rule_rec.parent_rule_id,
591 cn_api.get_lkup_meaning('SEQUENCE_NUMBER', 'RULESET_TYPE'),
592 x_loading_status,
593 x_loading_status) = FND_API.G_TRUE )
594 THEN
595 RAISE fnd_api.g_exc_error;
596 END IF;
597
598
599
600 IF (cn_api.chk_null_num_para
601 ( p_rule_rec.ruleset_id,
602 cn_api.get_lkup_meaning('RULESET_ID', 'RULESET_TYPE'),
603 x_loading_status,
604 x_loading_status) = FND_API.G_TRUE )
605 THEN
606 RAISE fnd_api.g_exc_error;
607 END IF;
608 IF (cn_api.chk_null_num_para
609 ( p_rule_rec.rule_id,
610 cn_api.get_lkup_meaning('RULE_ID', 'RULESET_TYPE'),
611 x_loading_status,
612 x_loading_status) = FND_API.G_TRUE )
613 THEN
614 RAISE fnd_api.g_exc_error;
615 END IF;
616 IF (cn_api.chk_null_char_para
617 ( p_rule_rec.rule_name,
618 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
619 x_loading_status,
620 x_loading_status) = FND_API.G_TRUE )
621 THEN
622 RAISE fnd_api.g_exc_error;
623 END IF;
624 IF (cn_api.chk_null_num_para
625 ( p_rule_rec.parent_rule_id,
626 cn_api.get_lkup_meaning('PARENT_RULE_ID', 'RULESET_TYPE'),
627 x_loading_status,
628 x_loading_status) = FND_API.G_TRUE )
629 THEN
630 RAISE fnd_api.g_exc_error;
631 END IF;
632 IF (cn_api.chk_null_num_para
633 ( p_rule_rec.parent_rule_id,
634 cn_api.get_lkup_meaning('SEQUENCE_NUMBER', 'RULESET_TYPE'),
635 x_loading_status,
636 x_loading_status) = FND_API.G_TRUE )
637 THEN
638 RAISE fnd_api.g_exc_error;
639 END IF;
640 IF p_rule_rec.ruleset_id <> p_old_rule_rec.ruleset_id
641 THEN
642
643 --Now check if the ruleset exists.
644 SELECT count(1)
645 INTO l_count
646 FROM cn_rulesets
647 WHERE ruleset_id = p_rule_rec.ruleset_id;
648
649 IF l_count = 0
650 THEN
651 --Error condition
652 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
653 THEN
654 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
655 fnd_msg_pub.add;
656 END IF;
657 x_loading_status := 'CN_INVALID_RULESET';
658 RAISE FND_API.G_EXC_ERROR;
659 END IF;
660
661 END IF;
662
663 IF p_rule_rec.parent_rule_id <> p_old_rule_rec.parent_rule_id
664 THEN
665
666 --Validate the parent rule
667 SELECT count(1)
668 INTO l_count
669 FROM cn_rules
670 WHERE rule_id = p_rule_rec.parent_rule_id and
671 org_id=p_rule_rec.org_id;
672 IF l_count = 0
673 THEN
674 --Error condition
675 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
676 THEN
677 fnd_message.set_name('CN', 'CN_INVALID_RULE');
678 fnd_msg_pub.add;
679 END IF;
680 x_loading_status := 'CN_INVALID_RULE';
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683 END IF;
684
685
686 SELECT COUNT(1)
687 INTO l_count
688 FROM cn_rules
689 WHERE rule_id = p_old_rule_rec.rule_id
690 and org_id=p_rule_rec.org_id;
691 IF l_count = 0
692 THEN
693 --Error condition
694 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
695 THEN
696 fnd_message.set_name('CN', 'CN_INVALID_RULE');
697 fnd_msg_pub.add;
698 END IF;
699 x_loading_status := 'CN_INVALID_RULE';
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703
704 IF p_rule_rec.rule_name <> p_old_rule_rec.rule_name
705 THEN
706 IF validate_rule_name(p_rule_rec.ruleset_id,
707 p_rule_rec.parent_rule_id,
708 p_rule_rec.rule_name,
709 p_rule_rec.org_id,
710 x_loading_status,
711 x_loading_status) = fnd_api.g_true
712 THEN
713 --Error condition
714 /* IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
715 THEN
716 fnd_message.set_name('CN', 'CN_INVALID_RULE_NAME');
717 fnd_msg_pub.add;
718 END IF;
719 */
720
721 x_loading_status := 'CN_INVALID_RULE_NAME';
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724 END IF;
725
726 --
727 -- Validate RUle
728 --
729 valid_rule
730 ( x_return_status => x_return_status,
731 x_msg_count => x_msg_count,
732 x_msg_data => x_msg_data,
733 p_rule_rec => p_rule_rec,
734 p_action => 'CREATE',
735 p_loading_status => x_loading_status,
736 x_loading_status => x_loading_status
737 );
738 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
739 RAISE FND_API.G_EXC_ERROR ;
740 END IF;
741
742
743 cn_syin_rules_pkg.update_row(p_old_rule_rec.rule_id,
744 p_rule_rec.ruleset_id,
745 null,
746 p_rule_rec.revenue_class_id,
747 p_rule_rec.expense_ccid,
748 p_rule_rec.liability_ccid,
749 p_rule_rec.rule_name,
750 Sysdate,
751 g_last_updated_by,
752 g_last_update_login,
753 p_rule_rec.org_id,
754 p_rule_rec.object_version_no);
755
756 cn_rulesets_pkg.Unsync_ruleset(x_ruleset_id_in => p_rule_rec.ruleset_id,
757 x_ruleset_status_in => l_ruleset_status,
758 x_org_id => p_rule_rec.org_id);
759
760 -- End of API body.
761
762 -- Standard check of p_commit.
763 IF FND_API.To_Boolean( p_commit )
764 THEN
765 COMMIT WORK;
766 END IF;
767
768 -- Standard call to get message count and if count is 1, get message info.
769 FND_MSG_PUB.Count_And_Get
770 (p_count => x_msg_count,
771 p_data => x_msg_data
772 );
773 EXCEPTION
774 WHEN FND_API.G_EXC_ERROR THEN
775 ROLLBACK TO Update_Rule;
776 x_return_status := FND_API.G_RET_STS_ERROR ;
777 FND_MSG_PUB.Count_And_Get
778 (p_count => x_msg_count,
779 p_data => x_msg_data,
780 p_encoded => fnd_api.g_false
781 );
782 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
783 ROLLBACK TO Update_Rule;
784 x_loading_status := 'UNEXPECTED_ERR';
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
786 FND_MSG_PUB.Count_And_Get
787 (p_count => x_msg_count,
788 p_data => x_msg_data,
789 p_encoded => fnd_api.g_false
790 );
791 WHEN OTHERS THEN
792 ROLLBACK TO Update_Rule;
793 x_loading_status := 'UNEXPECTED_ERR';
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
795 IF FND_MSG_PUB.Check_Msg_Level
796 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
797 THEN
798 FND_MSG_PUB.Add_Exc_Msg
799 (G_PKG_NAME, l_api_name);
800 END IF;
801 FND_MSG_PUB.Count_And_Get
802 (p_count => x_msg_count,
803 p_data => x_msg_data,
804 p_encoded => fnd_api.g_false
805 );
806
807 END;
808 --=============================================================================
809 -- Start of comments
810 -- API name : Delete_Rule
811 -- Type : Private
812 -- Function : This Public API can be used to delete a rule
813 -- Pre-reqs : None.
814 -- Parameters :
815 -- IN : p_api_version IN NUMBER Required
816 -- p_init_msg_list IN VARCHAR2 Optional
817 -- Default = FND_API.G_FALSE
818 -- p_commit IN VARCHAR2 Optional
819 -- Default = FND_API.G_FALSE
820 -- p_validation_level IN NUMBER Optional
821 -- Default = FND_API.G_VALID_LEVEL_FULL
822 -- p_rule_id IN NUMBER
823 --
824 -- OUT : x_return_status OUT VARCHAR2(1)
825 -- x_msg_count OUT NUMBER
826 -- x_msg_data OUT VARCHAR2(2000)
827 --
828 -- Version : Current version 1.0
829 -- 25-Mar-99 Renu Chintalapati
830 -- previous version y.y
831 -- Changed....
832 -- Initial version 1.0
833 -- 25-Mar-99 Renu Chintalapati
834 --
835 -- Notes : This can be used to delete rules (and thus
836 -- their rule attributes).
837 -- Mandatory parameter is rule id
838 --
839 -- End of comments
840 --=============================================================================
841 PROCEDURE Delete_Rule
842 ( p_api_version IN NUMBER,
843 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
844 p_commit IN VARCHAR2 := FND_API.G_FALSE,
845 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 x_loading_status OUT NOCOPY VARCHAR2,
850 p_rule_id IN cn_rules_all_b.rule_id%TYPE,
851 p_ruleset_id IN cn_rules_all_b.ruleset_id%TYPE,
852 p_org_id IN cn_rules_all_b.org_id%TYPE
853 ) IS
854
855 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
856 l_api_version CONSTANT NUMBER := 1.0;
857 l_count NUMBER;
858 l_ruleset_status Varchar2(100);
859
860
861
862
863 BEGIN
864
865
866 -- Standard Start of API savepoint
867 SAVEPOINT Delete_Rule;
868 -- Standard call to check for call compatibility.
869 IF NOT FND_API.Compatible_API_Call ( l_api_version,
870 p_api_version,
871 l_api_name,
872 G_PKG_NAME )
873 THEN
874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
875 END IF;
876
877 -- Initialize message list if p_init_msg_list is set to TRUE.
878 IF FND_API.to_Boolean( p_init_msg_list )
879 THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882
883 -- Initialize API return status to success
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885 x_loading_status := 'CN_DELETED';
886
887 -- new API body
888 SELECT COUNT(1)
889 INTO l_count
890 FROM
891 ( SELECT rule_id ,ruleset_id
892 FROM cn_rules_hierarchy
893 WHERE ruleset_id=Nvl(p_ruleset_id ,-1002) and org_id=p_org_id
894 CONNECT BY PRIOR rule_id = parent_rule_id
895 START WITH rule_id = Nvl(p_rule_id, -1002)
896 )a WHERE EXISTS
897 (SELECT 'x'
898 FROM cn_attribute_rules car
899 WHERE car.ruleset_id = a.ruleset_id
900 AND car.rule_id = a.rule_id and car.org_id=p_org_id);
901
902 IF l_count <> 0
903 THEN
904 --Error condition
905 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
906 THEN
907 fnd_message.set_name('CN', 'CN_ATTRIBUTE_RULE_EXIST');
908 fnd_msg_pub.add;
909 END IF;
910
911 x_loading_status := 'CN_ATTRIBUTE_RULE_EXIST';
912 RAISE FND_API.G_EXC_ERROR;
913
914 END IF;
915
916 SELECT COUNT(1)
917 INTO l_count
918 FROM cn_rules
919 WHERE ruleset_id = p_ruleset_id
920 AND rule_id = p_rule_id and
921 org_id=p_org_id;
922
923 IF l_count <> 1
924 THEN
925 --Error condition
926 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
927 THEN
928 fnd_message.set_name('CN', 'CN_INVALID_RULE');
929 fnd_msg_pub.add;
930 END IF;
931
932 x_loading_status := 'CN_INVALID_RULE';
933 RAISE FND_API.G_EXC_ERROR;
934
935 END IF;
936
937 cn_syin_rules_pkg.delete_row(p_rule_id, p_ruleset_id,p_org_id);
938
939 cn_rulesets_pkg.Unsync_ruleset(x_ruleset_id_in => p_ruleset_id,
940 x_ruleset_status_in => l_ruleset_status,
941 x_org_id => p_org_id);
942
943
944 -- End of API body.
945
946 -- Standard check of p_commit.
947 IF FND_API.To_Boolean( p_commit )
948 THEN
949 COMMIT WORK;
950 END IF;
951
952 -- Standard call to get message count and if count is 1, get message info.
953 FND_MSG_PUB.Count_And_Get
954 (p_count => x_msg_count,
955 p_data => x_msg_data
956 );
957 EXCEPTION
958 WHEN FND_API.G_EXC_ERROR THEN
959 ROLLBACK TO Delete_Rule;
960 x_return_status := FND_API.G_RET_STS_ERROR ;
961 FND_MSG_PUB.Count_And_Get
962 (p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => fnd_api.g_false
965 );
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 ROLLBACK TO Delete_Rule;
968 x_loading_status := 'UNEXPECTED_ERR';
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
970 FND_MSG_PUB.Count_And_Get
971 (p_count => x_msg_count,
972 p_data => x_msg_data,
973 p_encoded => fnd_api.g_false
974 );
975 WHEN OTHERS THEN
976 ROLLBACK TO Delete_Rule;
977 x_loading_status := 'UNEXPECTED_ERR';
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
979 IF FND_MSG_PUB.Check_Msg_Level
980 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
981 THEN
982 FND_MSG_PUB.Add_Exc_Msg
983 (G_PKG_NAME, l_api_name );
984 END IF;
985 FND_MSG_PUB.Count_And_Get
986 (p_count => x_msg_count,
987 p_data => x_msg_data,
988 p_encoded => fnd_api.g_false
989 );
990 END;
991 -- =======================================================================
992 -- Procedure : Get_rules
993 -- Desc : Get_rules
994 -- =======================================================================
995 PROCEDURE Get_rules
996 ( p_api_version IN NUMBER,
997 p_init_msg_list IN VARCHAR2,
998 p_commit IN VARCHAR2,
999 p_validation_level IN NUMBER,
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY NUMBER,
1002 x_msg_data OUT NOCOPY VARCHAR2,
1003 p_ruleset_name IN cn_rulesets_all_tl.name%TYPE,
1004 p_start_record IN NUMBER,
1005 p_increment_count IN NUMBER,
1006 p_order_by IN VARCHAR2,
1007 x_rule_tbl OUT NOCOPY rule_tbl_type,
1008 x_total_records OUT NOCOPY NUMBER,
1009 x_status OUT NOCOPY VARCHAR2,
1010 x_loading_status OUT NOCOPY VARCHAR2,
1011 p_org_id IN cn_rulesets_all_tl.org_id%TYPE
1012 ) IS
1013
1014 TYPE rulecurtype IS ref CURSOR;
1015 rule_cur rulecurtype;
1016
1017
1018 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rule';
1019 l_api_version CONSTANT NUMBER := 1.0;
1020
1021
1022
1023 l_ruleset_id cn_rulesets.ruleset_id%TYPE;
1024 l_rule_id cn_rules.rule_id%TYPE;
1025 l_rule_name cn_rules.name%TYPE;
1026 l_ruleset_name cn_rulesets.name%TYPE;
1027 l_revenue_class_id cn_rules.revenue_class_id%TYPE;
1028 l_expense_ccid cn_rules.expense_ccid%TYPE;
1029 l_liability_ccid cn_rules.liability_ccid%TYPE;
1030 l_org_id cn_rules.org_id%TYPE;
1031 l_expense_ccid_disp varchar2(2000);
1032 l_liability_ccid_disp varchar2(2000);
1033 l_revenue_class_name cn_revenue_classes.name%TYPE;
1034
1035 l_counter NUMBER;
1036
1037
1038 l_select varchar2(4000) :=
1039 'SELECT rset.ruleset_id ruleset_id,
1040 rset.name ruleset_name,
1041 rule.rule_id rule_id,
1042 rule.name,
1043 rule.revenue_class_id,
1044 rule.expense_ccid,
1045 rule.liability_id,
1046 rule.org_id
1047 FROM cn_rulesets rset, cn_rules rule
1048 WHERE rset.ruleset_id = rule.ruleset_id AND
1049 rset.org_id=rule.org_id AND
1050 rset.org_id=:B1 AND
1051 upper(rset.name) like upper(:B2) ';
1052
1053 BEGIN
1054
1055 --
1056 -- Standard Start of API savepoint
1057 --
1058 SAVEPOINT Get_Rules;
1059 --
1060 -- Standard call to check for call compatibility.
1061 --
1062 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1063 p_api_version ,
1064 l_api_name ,
1065 G_PKG_NAME )
1066 THEN
1067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1068 END IF;
1069
1070 --
1071 -- Initialize message list if p_init_msg_list is set to TRUE.
1072 --
1073 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1074 FND_MSG_PUB.initialize;
1075 END IF;
1076
1077 --
1078 -- Initialize API return status to success
1079 --
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081 x_loading_status := 'SELECTED';
1082 --
1083 -- API body
1084 --
1085 l_counter := 0;
1086 x_total_records := 0;
1087
1088 OPEN rule_cur FOR l_select using p_org_id,p_ruleset_name;
1089 LOOP
1090
1091 FETCH rule_cur INTO
1092 l_ruleset_id
1093 ,l_ruleset_name
1094 ,l_rule_id
1095 ,l_rule_name
1096 ,l_revenue_class_id
1097 ,l_expense_ccid
1098 ,l_liability_ccid
1099 ,l_org_id;
1100
1101 EXIT WHEN rule_cur%notfound;
1102
1103 x_total_records := x_total_records + 1;
1104
1105 IF (l_counter + 1 BETWEEN p_start_record
1106 AND (p_start_record + p_increment_count - 1))
1107 THEN
1108 x_rule_tbl(l_counter).ruleset_id
1109 := l_ruleset_id;
1110
1111 x_rule_tbl(l_counter).ruleset_name
1112 := l_ruleset_name;
1113
1114 x_rule_tbl(l_counter).rule_id
1115 := l_rule_id;
1116
1117 x_rule_tbl(l_counter).rule_name
1118 := l_rule_name;
1119
1120 x_rule_tbl(l_counter).revenue_class_id
1121 := l_revenue_class_id;
1122
1123 x_rule_tbl(l_counter).expense_ccid
1124 := l_expense_ccid;
1125
1126 x_rule_tbl(l_counter).liability_ccid
1127 := l_liability_ccid;
1128
1129 if l_revenue_class_id IS NOT NULL THEN
1130 cn_syin_rules_pkg.populate_fields
1131 (l_revenue_class_id,
1132 l_revenue_class_name,
1133 l_org_id);
1134 end if;
1135
1136 if l_liability_ccid IS NOT NULL
1137 then
1138 cn_api.get_ccid_disp(l_liability_ccid,
1139 l_liability_ccid_disp,
1140 l_org_id);
1141
1142 end if;
1143
1144 if l_expense_ccid IS NOT NULL
1145 then
1146 cn_api.get_ccid_disp(l_expense_ccid,
1147 l_expense_ccid_disp,
1148 l_org_id);
1149 end if;
1150
1151 x_rule_tbl(l_counter).expense_desc
1152 := l_expense_ccid_disp;
1153
1154 x_rule_tbl(l_counter).liability_desc
1155 := l_liability_ccid_disp;
1156
1157 x_rule_tbl(l_counter).revenue_class_name
1158 := l_revenue_class_name;
1159
1160 END IF;
1161 l_counter := l_counter + 1;
1162
1163 END LOOP;
1164 CLOSE rule_cur;
1165
1166 x_loading_status := 'SELECTED';
1167
1168 -- End of API body.
1169 -- Standard check of p_commit.
1170 IF FND_API.To_Boolean( p_commit ) THEN
1171 COMMIT WORK;
1172 END IF;
1173
1174 -- Get and Count Messages
1175 FND_MSG_PUB.Count_And_Get
1176 (
1177 p_count => x_msg_count ,
1178 p_data => x_msg_data ,
1179 p_encoded => FND_API.G_FALSE
1180 );
1181
1182 EXCEPTION
1183 WHEN FND_API.G_EXC_ERROR THEN
1184 ROLLBACK TO Get_rules;
1185 x_return_status := FND_API.G_RET_STS_ERROR ;
1186 FND_MSG_PUB.Count_And_Get
1187 (
1188 p_count => x_msg_count ,
1189 p_data => x_msg_data ,
1190 p_encoded => FND_API.G_FALSE
1191 );
1192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1193 ROLLBACK TO Get_rules;
1194 x_loading_status := 'UNEXPECTED_ERR';
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1196 FND_MSG_PUB.Count_And_Get
1197 (
1198 p_count => x_msg_count ,
1199 p_data => x_msg_data ,
1200 p_encoded => FND_API.G_FALSE
1201 );
1202 WHEN OTHERS THEN
1203 ROLLBACK TO Get_rules;
1204 x_loading_status := 'UNEXPECTED_ERR';
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1206 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1207 THEN
1208 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1209 END IF;
1210 FND_MSG_PUB.Count_And_Get (
1211 p_count => x_msg_count ,
1212 p_data => x_msg_data ,
1213 p_encoded => FND_API.G_FALSE
1214 );
1215 END Get_rules;
1216
1217 -- Function which returns the expression corresponding to a rule
1218 FUNCTION get_rule_exp (
1219 p_rule_id NUMBER ) RETURN VARCHAR2 IS
1220
1221 --cursor to get all the expressions of a rule
1222 CURSOR expr_cur(l_rule_id NUMBER) IS
1223 SELECT cnobj.user_name object_name,
1224 cnh.name hierarchy_name, cnattr.column_value column_value,
1225 cnattr.not_flag not_flag, cnattr.high_value high_value,
1226 cnattr.low_value low_value , cnattr.dimension_hierarchy_id dimension_hierarchy_id
1227 FROM cn_attribute_rules cnattr, cn_objects cnobj,
1228 cn_head_hierarchies cnh
1229 WHERE cnattr.rule_id = l_rule_id
1230 AND cnattr.column_id = cnobj.object_id (+)
1231 AND cnattr.org_id = cnobj.org_id
1232 AND cnattr.dimension_hierarchy_id = cnh.head_hierarchy_id(+)
1233 AND cnattr.org_id = cnh.org_id(+);
1234
1235
1236 rule_exp VARCHAR2(2000);
1237 l_flag NUMBER := 0;
1238 l_user_expression NUMBER := 0;
1239 l_column_value NUMBER;
1240 node_value VARCHAR2(2000);
1241
1242 -- variables for the lookups
1243 l_bet VARCHAR2(80);
1244 l_is VARCHAR2(80);
1245 l_not VARCHAR2(80);
1246 l_is_bet VARCHAR2(80);
1247 l_is_not_bet VARCHAR2(80);
1248 l_and VARCHAR2(80);
1249 l_desc VARCHAR2(80);
1250 l_not_desc VARCHAR2(80);
1251 l_hier VARCHAR2(80);
1252
1253 BEGIN
1254
1255 --get all the lookup meanings
1256 l_bet := cn_api.get_lkup_meaning('BET','Expression Messages');
1257 l_is := cn_api.get_lkup_meaning('IS','Expression Messages');
1258 l_not := cn_api.get_lkup_meaning('NOT','Expression Messages');
1259 l_is_bet := l_is || ' ' || l_bet;
1260 l_is_not_bet := l_is || ' ' || l_not || ' ' || l_bet;
1261 l_and := cn_api.get_lkup_meaning('AND','Expression Messages');
1262 l_desc := cn_api.get_lkup_meaning('DESCENDANT','Expression Messages');
1263 l_hier := cn_api.get_lkup_meaning('IIH','Expression Messages');
1264 l_not_desc := l_is || ' ' || l_not || ' ' || l_desc;
1265 l_desc := l_is || ' ' || l_desc;
1266
1267
1268 l_flag := 0;
1269 l_user_expression :=0;
1270
1271 -- first check if the user has created any expression
1272 SELECT COUNT(1)
1273 INTO l_user_expression
1274 FROM CN_RULE_ATTR_EXPRESSION
1275 WHERE RULE_ID = p_rule_id ;
1276
1277 IF (l_user_expression > 0) THEN
1278
1279 -- for user created expressions
1280
1281 SELECT DISTINCT expression
1282 INTO rule_exp
1283 FROM CN_ATTRIBUTE_RULES
1284 WHERE RULE_ID = p_rule_id ;
1285
1286 ELSE
1287 -- for expression not 'created' by the user
1288 FOR expr IN expr_cur(p_rule_id) LOOP
1289 -- first decide whether this is the first expression or not.
1290 IF l_flag = 0 THEN
1291 rule_exp := ' ';
1292 l_flag := 1;
1293 ELSE -- not first expression, need to AND with the previous expression
1294 rule_exp := rule_exp || l_and || ' ' ;
1295 END IF;
1296
1297 IF expr.dimension_hierarchy_id IS NOT NULL THEN
1298 l_column_value := expr.column_value;
1299 SELECT name INTO node_value
1300 FROM cn_hierarchy_nodes
1301 WHERE value_id=l_column_value;
1302
1303 IF expr.not_flag = 'N' THEN
1304 rule_exp := rule_exp || expr.object_name || ' ' || l_desc || ' ''' || node_value || ''' ' || l_hier || ' ''' || expr.hierarchy_name || '''' || ' ' ;
1305 ELSE
1306 rule_exp := rule_exp || expr.object_name || ' ' || l_not_desc ||' '''|| node_value || ''' ' || l_hier || ' ''' || expr.hierarchy_name || '''' || ' ' ;
1307 END IF;
1308
1309 ELSE
1310 IF expr.column_value IS NULL THEN
1311 IF expr.not_flag = 'N' THEN
1312 rule_exp := rule_exp || expr.object_name || ' ' || l_is_bet ||' '''|| expr.high_value || ''' ' || l_and ||' '''|| expr.low_value ||''''|| ' ';
1313 ELSE
1314 rule_exp := rule_exp || expr.object_name || ' ' || l_is_not_bet || ' ''' || expr.high_value || ''' ' || l_and || ' ''' || expr.low_value || '''' || ' ';
1315 END IF;
1316 ELSE
1317 IF expr.not_flag = 'N' THEN
1318 rule_exp := rule_exp || expr.object_name || ' = ' || '''' || expr.column_value || ''''|| ' ';
1319 ELSE
1320 rule_exp := rule_exp || expr.object_name || ' <> ' || '''' || expr.column_value ||''''|| ' ';
1321 END IF;
1322 END IF;
1323 END IF;
1324 END LOOP;
1325
1326 END IF; -- this is for the user created expressions
1327
1328
1329 RETURN rule_exp;
1330
1331 END get_rule_exp;
1332
1333
1334 END CN_Rule_PVT;