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