[Home] [Help]
PACKAGE BODY: APPS.CN_RULESET_PVT
Source
1 PACKAGE BODY CN_Ruleset_PVT AS
2 --$Header: cnvrsetb.pls 120.7 2005/12/27 04:04:37 hanaraya ship $
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Ruleset_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 --=========================================================================
13 -- Check synchronized .
14 --=========================================================================
15
16 FUNCTION Check_Sync_Allowed (
17 p_name In VARCHAR2,
18 p_ruleset_id NUMBER,
19 p_org_id in NUMBER,
20 p_loading_status IN VARCHAR2,
21 x_loading_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
22
23 l_api_name CONSTANT VARCHAR2(30) := 'check_sync_allowed';
24
25 CURSOR no_attribute_curs IS
26 SELECT name
27 FROM cn_rules cr
28 WHERE cr.rule_id <> -1002
29 AND cr.ruleset_id = p_ruleset_id
30 AND cr.org_id=p_org_id
31 AND NOT EXISTS (SELECT 1
32 FROM cn_attribute_rules car
33 WHERE car.rule_id = cr.rule_id and
34 car.org_id=cr.org_id);
35
36 CURSOR valid_attribute_curs IS
37 SELECT attr.ruleset_id ruleset_id,
38 attr.rule_id rule_id,
39 attr.column_id column_id ,
40 attr.column_value column_value,
41 attr.high_value,
42 attr.low_value,
43 cr.name rule_name,
44 col.user_name,
45 attr.org_id
46 FROM cn_rules cr, cn_attribute_rules attr, cn_objects col
47 WHERE cr.rule_id <> -1002
48 and col.object_id = attr.column_id
49 and col.table_id = -11803
50 AND cr.ruleset_id = p_ruleset_id
51 AND attr.rule_id = cr.rule_id
52 and attr.ruleset_id = cr.ruleset_id
53 and dimension_hierarchy_id is null and
54 cr.org_id=attr.org_id and
55 attr.org_id=col.org_id;
56
57
58 l_data_flag VARCHAR2(02) := 'O';
59
60 BEGIN
61 x_loading_status := p_loading_status;
62
63 for attribute_rec in no_attribute_curs loop
64
65 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
66 fnd_message.set_name('CN', 'CN_NO_RULE_ATTRIBUTES_DEFINED');
67 fnd_message.set_token('CLASSIFICATION_RULE_NAME', p_name);
68 fnd_message.set_token('RULE_NAME', attribute_rec.name);
69 fnd_msg_pub.add;
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72
73 END LOOP;
74
75 for attribute_rec1 in valid_attribute_curs loop
76 l_data_flag := NULL;
77
78 if attribute_rec1.column_id is not null and
79 attribute_rec1.column_value is not null THEN
80 l_data_flag := 'O';
81 elsif attribute_rec1.column_id is not null and
82 attribute_rec1.high_value is not null and
83 attribute_rec1.low_value is not null then
84 l_data_flag := 'R';
85 end if;
86
87 if l_data_flag = 'O' THEN
88
89 CN_RuleAttribute_PVT.Check_Attr_types
90 (p_value_1 => attribute_rec1.column_value,
91 p_value_2 => null,
92 p_column_id => attribute_rec1.column_id,
93 p_rule_id => attribute_rec1.rule_id,
94 p_ruleset_id => attribute_rec1.ruleset_id,
95 p_org_id => attribute_rec1.org_id,
96 p_data_flag => l_data_flag,
97 p_loading_status => x_loading_status,
98 x_loading_status => x_loading_status);
99
100 if x_loading_Status = 'CN_DATATYPE_VALUE_MISMATCH' then
101 RAISE FND_API.G_EXC_ERROR;
102 END IF;
103 elsif l_data_flag = 'R' THEN
104
105
106 CN_RuleAttribute_PVT.Check_Attr_types
107 (p_value_1 => attribute_rec1.low_value,
108 p_value_2 => attribute_rec1.high_value,
109 p_column_id => attribute_rec1.column_id,
110 p_rule_id => attribute_rec1.rule_id,
111 p_ruleset_id => attribute_rec1.ruleset_id,
112 p_org_id => attribute_rec1.org_id,
113 p_data_flag => l_data_flag,
114 p_loading_status => x_loading_status,
115 x_loading_status => x_loading_status) ;
116
117 if x_loading_status = 'CN_DATATYPE_VALUE_MISMATCH' THEN
118 RAISE FND_API.G_EXC_ERROR;
119 end if;
120 end if;
121 END LOOP;
122
123 RETURN fnd_api.g_false;
124
125 EXCEPTION
126 WHEN FND_API.G_EXC_ERROR THEN
127 RETURN fnd_api.g_true;
128
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 x_loading_status := 'UNEXPECTED_ERR';
131 RETURN fnd_api.g_true;
132
133 WHEN OTHERS THEN
134 x_loading_status := 'UNEXPECTED_ERR';
135 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
136 THEN
137 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
138 END IF;
139 RETURN fnd_api.g_true;
140
141 END Check_Sync_Allowed;
142
143
144 --=========================================================================
145 -- Check Update Allowed.
146 --=========================================================================
147
148 FUNCTION check_update_allowed
149 ( p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
150 p_module_type In cn_rulesets.module_type%TYPE,
151 p_loading_status IN VARCHAR2,
152 x_loading_status OUT NOCOPY VARCHAR2,
153 x_org_id IN cn_rulesets.org_id%TYPE
154 ) RETURN VARCHAR2 IS
155
156 l_api_name CONSTANT VARCHAR2(30) := 'check_update_allowed';
157
158 l_env_org_id NUMBER;
159
160 CURSOR record_exists IS
161 SELECT count(1) cnt
162 FROM cn_rules_all_b
163 WHERE ruleset_id = p_ruleset_id
164 AND rule_id <> -1002
165 AND org_id = l_env_org_id;
166
167 CURSOR get_module_type IS
168 SELECT module_type
169 FROM cn_rulesets
170 WHERE ruleset_id = p_ruleset_id;
171
172 l_module_type cn_rulesets.module_type%TYPE;
173 l_total_record NUMBER;
174
175 BEGIN
176
177 x_loading_status := p_loading_status;
178 l_env_org_id :=x_org_id;
179
180 OPEN get_module_type;
181 fetch get_module_type into l_module_type;
182 close get_module_type;
183
184 IF l_module_type <> p_module_type THEN
185
186 open record_exists;
187 fetch record_exists into l_total_record;
188 close record_exists;
189
190 IF l_total_record > 0 THEN
191
192 --Error condition
193 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
194 THEN
195 fnd_message.set_name('CN', 'CN_CANNOT_CHANGE_TYPE');
196 fnd_msg_pub.add;
197 END IF;
198 x_loading_status := 'CN_CANNOT_TYPE_TYPE';
199 RAISE FND_API.G_EXC_ERROR;
200
201 END IF;
202
203 END IF;
204
205 RETURN fnd_api.g_false;
206
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209 RETURN fnd_api.g_true;
210
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 x_loading_status := 'UNEXPECTED_ERR';
213 RETURN fnd_api.g_true;
214
215 WHEN OTHERS THEN
216 x_loading_status := 'UNEXPECTED_ERR';
217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
218 THEN
219 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
220 END IF;
221 RETURN fnd_api.g_true;
222
223 END check_update_allowed;
224
225 --=========================================================================
226 --
227 -- Procedure : check_ruleset_dates
228 -- Desc : Validate the start and end dates for the ruleset
229 --
230 --=========================================================================
231 FUNCTION check_ruleset_dates
232 (p_start_date IN cn_rulesets.start_date%TYPE,
233 p_end_date IN cn_rulesets.end_date%TYPE,
234 p_module_type IN cn_rulesets.module_type%TYPE,
235 p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236 p_org_id IN cn_rulesets.org_id%TYPE,
237 p_loading_status IN VARCHAR2,
238 x_loading_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
239
240 l_api_name CONSTANT VARCHAR2(30) := 'check_ruleset_dates';
241
242 CURSOR overlap_check1 (p_date DATE) IS
243 SELECT count(*) cnt
244 FROM cn_rulesets cnr
245 WHERE p_date BETWEEN cnr.start_date AND cnr.end_date
246 AND nvl(module_type,'X') = nvl(p_module_type,'X')
247 AND ruleset_id <> p_ruleset_id
248 AND org_id=p_org_id;
249
250
251 CURSOR overlap_check2
252 (p_start_date DATE,
253 p_end_date DATE) IS
254 SELECT count(*) cnt
255 FROM cn_rulesets cnr
256 WHERE cnr.start_date BETWEEN p_start_date AND p_end_date
257 AND cnr.end_date BETWEEN p_start_date AND p_end_date
258 AND nvl(module_type,'X') = nvl(p_module_type,'X')
259 AND ruleset_id <> p_ruleset_id and
260 org_id=p_org_id;
261
262 l_count NUMBER;
263
264 BEGIN
265 x_loading_status := p_loading_status;
266
267 IF p_start_date > p_end_date
268 THEN
269
270 --Error condition
271 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
272 THEN
273 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
274 fnd_msg_pub.add;
275 END IF;
276
277 x_loading_status := 'CN_INVALID_DATE_RANGE';
278 RAISE FND_API.G_EXC_ERROR;
279
280 END IF;
281
282
283 OPEN overlap_check1 (p_start_date);
284 FETCH overlap_check1 INTO l_count;
285 IF l_count > 0
286 THEN
287
288 --Error condition
289 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
290 THEN
291 fnd_message.set_name('CN', 'CN_OVERLAP_RULESETS');
292 fnd_msg_pub.add;
293 END IF;
294 CLOSE overlap_check1;
295 x_loading_status := 'CN_OVERLAP_RULESETS';
296 RAISE FND_API.G_EXC_ERROR;
297
298 END IF;
299 CLOSE overlap_check1;
300
301
302 OPEN overlap_check1 (p_end_date);
303 FETCH overlap_check1 INTO l_count;
304 IF l_count > 0
305 THEN
306
307 --Error condition
308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
309 THEN
310 fnd_message.set_name('CN', 'CN_OVERLAP_RULESETS');
311 fnd_msg_pub.add;
312 END IF;
313 CLOSE overlap_check1;
314 x_loading_status := 'CN_OVERLAP_RULESETS';
315 RAISE FND_API.G_EXC_ERROR;
316
317 END IF;
318 CLOSE overlap_check1;
319
320
321 OPEN overlap_check2 (p_start_date,
322 p_end_date);
323 FETCH overlap_check2 INTO l_count;
324 IF l_count > 0
325 THEN
326
327 --Error condition
328 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
329 THEN
330 fnd_message.set_name('CN', 'CN_OVERLAP_RULESETS');
331 fnd_msg_pub.add;
332 END IF;
333 CLOSE overlap_check2;
334 x_loading_status := 'CN_OVERLAP_RULESETS';
335 RAISE FND_API.G_EXC_ERROR;
336
337 END IF;
338 CLOSE overlap_check2;
339
340 RETURN fnd_api.g_false;
341
342 EXCEPTION
343 WHEN FND_API.G_EXC_ERROR THEN
344 RETURN fnd_api.g_true;
345
346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
347 x_loading_status := 'UNEXPECTED_ERR';
348 RETURN fnd_api.g_true;
349
350 WHEN OTHERS THEN
351 x_loading_status := 'UNEXPECTED_ERR';
352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
353 THEN
354 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
355 END IF;
356 RETURN fnd_api.g_true;
357 END check_ruleset_dates;
358
359 --=========================================================================
360 -- Start of comments
361 -- API name : Create_Ruleset
362 -- Type : Private
363 -- Function : This private API can be used to create a ruleset
364 -- Pre-reqs : None.
365 -- Parameters :
366 -- IN : p_api_version IN NUMBER Required
367 -- p_init_msg_list IN VARCHAR2 Optional
368 -- Default = FND_API.G_FALSE
369 -- p_commit IN VARCHAR2 Optional
370 -- Default = FND_API.G_FALSE
371 -- p_validation_level IN NUMBER Optional
372 -- Default = FND_API.G_VALID_LEVEL_FULL
373 -- p_ruleset_rec IN
374 -- CN_Ruleset_PVT.ruleset_rec_type
375 --
376 -- OUT : x_return_status OUT VARCHAR2(1)
377 -- x_msg_count OUT NUMBER
378 -- x_msg_data OUT VARCHAR2(2000)
379 --
380 -- Version : Current version 1.0
381 -- 25-Mar-99 Renu Chintalapati
382 -- previous version y.y
383 -- Changed....
384 -- Initial version 1.0
385 -- 25-Mar-99 Renu Chintalapati
386 --
387 -- Notes : Note text
388 --
389 -- End of comments
390 --=========================================================================
391 PROCEDURE create_ruleset
392 ( p_api_version IN NUMBER,
393 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
394 p_commit IN VARCHAR2 := FND_API.G_FALSE,
395 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_msg_count OUT NOCOPY NUMBER,
398 x_msg_data OUT NOCOPY VARCHAR2,
399 x_loading_status OUT NOCOPY VARCHAR2,
400 x_ruleset_id OUT NOCOPY NUMBER,
401 p_ruleset_rec IN CN_Ruleset_PVT.ruleset_rec_type
402 )
403 IS
404
405 l_api_name CONSTANT VARCHAR2(30) := 'Create_Ruleset';
406 l_api_version CONSTANT NUMBER := 1.0;
407 l_loading_status VARCHAR2(4000);
408 l_error_status NUMBER;
409 l_error_parameter VARCHAR2(30);
410 l_rowid VARCHAR2(4000);
411 l_sequence_number NUMBER;
412 l_count NUMBER;
413 l_ruleset_id cn_rulesets.ruleset_id%TYPE;
414 l_org_id cn_rulesets.org_id%TYPE;
415
416 BEGIN
417
418 -- Standard Start of API savepoint
419 SAVEPOINT Create_Ruleset;
420 -- Standard call to check for call compatibility.
421 IF NOT FND_API.Compatible_API_Call ( l_api_version,
422 p_api_version,
423 l_api_name,
424 G_PKG_NAME )
425 THEN
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428
429 -- Initialize message list if p_init_msg_list is set to TRUE.
430 IF FND_API.to_Boolean( p_init_msg_list )
431 THEN
432 FND_MSG_PUB.initialize;
433 END IF;
434
435 -- Initialize API return status to success
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437 x_loading_status := 'CN_INSERTED';
438
439 -- API body
440
441
442 --Check for missing parameters in the p_ruleset_rec parameter
443
444 IF (cn_api.chk_miss_null_char_para
445 ( p_ruleset_rec.ruleset_name,
446 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
447 x_loading_status,
448 x_loading_status) = FND_API.G_TRUE )
449 THEN
450 RAISE fnd_api.g_exc_error;
451 END IF;
452
453 IF (cn_api.chk_miss_null_char_para
454 ( p_ruleset_rec.module_type,
455 cn_api.get_lkup_meaning('MODULE_TYPE', 'RULESET_TYPE'),
456 x_loading_status,
457 x_loading_status) = FND_API.G_TRUE )
458 THEN
459 RAISE fnd_api.g_exc_error;
460 END IF;
461
462 IF cn_api.chk_miss_null_date_para
463 ( p_ruleset_rec.end_date,
464 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
465 x_loading_status,
466 x_loading_status) = fnd_api.g_true
467 THEN
468 RAISE fnd_api.g_exc_error;
469 END IF;
470
471 IF cn_api.chk_miss_null_date_para
472 (p_ruleset_rec.start_date,
473 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
474 x_loading_status,
475 x_loading_status)= fnd_api.g_true
476 THEN
477 RAISE fnd_api.g_exc_error;
478 END IF;
479
480 --Now check if the ruleset exists.
481 --If it does, then raise error
482 --else use cn_rulesets_s.nextval
483 SELECT count(1)
484 INTO l_count
485 FROM cn_rulesets
486 WHERE name = p_ruleset_rec.ruleset_name
487 AND module_type = p_ruleset_rec.module_type
488 AND start_date = p_ruleset_rec.start_date
489 AND end_date = p_ruleset_rec.end_date and
490 ORG_ID=p_ruleset_rec.org_id;
491
492 IF l_count <> 0
493 THEN
494 --Error condition
495 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
496 THEN
497 fnd_message.set_name('CN', 'CN_RULESET_EXISTS');
498 fnd_msg_pub.add;
499 END IF;
500
501 x_loading_status := 'CN_RULESET_EXISTS';
502 RAISE FND_API.G_EXC_ERROR;
503
504 END IF;
505
506 SELECT Decode(p_ruleset_rec.ruleset_id, NULL, cn_rulesets_s.NEXTVAL,
507 p_ruleset_rec.ruleset_id)
508 INTO l_ruleset_id
509 FROM dual;
510
511 --since this is a new ruleset, validate the effectivity before inserting
512 IF check_ruleset_dates(p_ruleset_rec.start_date,
513 p_ruleset_rec.end_date,
514 p_ruleset_rec.module_type,
515 l_ruleset_id,
516 p_ruleset_rec.org_id,
517 x_loading_status,
518 x_loading_status) = fnd_api.g_true
519 THEN
520 RAISE fnd_api.g_exc_error;
521 END IF;
522
523 IF p_ruleset_rec.module_type NOT IN ('REVCLS', 'ACCGEN', 'PECLS')
524 THEN
525
526 --Error condition
527 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
528 THEN
529 fnd_message.set_name('CN', 'CN_INVALID_RULESET_TYPE');
530 fnd_msg_pub.add;
531 END IF;
532 x_loading_status := 'CN_INVALID_RULESET_TYPE';
533
534 RAISE fnd_api.g_exc_error;
535 END IF;
536
537 cn_syin_rulesets_pkg.insert_row
538 (
539 x_rowid => l_rowid,
540 x_ruleset_id => l_ruleset_id,
541 x_end_date => p_ruleset_rec.end_date,
542 x_ruleset_status => 'UNSYNC',
543 x_destination_column_id => -11980,
544 x_repository_id => 100,
545 x_start_date => p_ruleset_rec.start_date,
546 x_name => p_ruleset_rec.ruleset_name,
547 x_module_type => p_ruleset_rec.module_type,
548 x_creation_date => sysdate,
549 x_created_by => g_created_by,
550 x_last_update_date => sysdate,
551 x_last_updated_by => g_last_updated_by,
552 x_last_update_login => g_last_update_login,
553 x_org_id =>p_ruleset_rec.org_id
554 );
555
556 -- End of API body.
557
558 -- Standard check of p_commit.
559 IF FND_API.To_Boolean( p_commit )
560 THEN
561 COMMIT WORK;
562 END IF;
563
564 x_ruleset_id := l_ruleset_id;
565
566
567 FND_MSG_PUB.Count_And_Get
568 (
569 p_count => x_msg_count ,
570 p_data => x_msg_data ,
571 p_encoded => FND_API.G_FALSE
572 );
573 EXCEPTION
574 WHEN FND_API.G_EXC_ERROR THEN
575 ROLLBACK TO Create_Ruleset;
576 x_return_status := FND_API.G_RET_STS_ERROR ;
577 FND_MSG_PUB.Count_And_Get
578 (
579 p_count => x_msg_count ,
580 p_data => x_msg_data ,
581 p_encoded => FND_API.G_FALSE
582 );
583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 ROLLBACK TO Create_Ruleset;
585 x_loading_status := 'UNEXPECTED_ERR';
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
587 FND_MSG_PUB.Count_And_Get
588 (
589 p_count => x_msg_count ,
590 p_data => x_msg_data ,
591 p_encoded => FND_API.G_FALSE
592 );
593 WHEN OTHERS THEN
594 ROLLBACK TO Create_Ruleset;
595 x_loading_status := 'UNEXPECTED_ERR';
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
597 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
598 THEN
599 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
600 END IF;
601 FND_MSG_PUB.Count_And_Get
602 (
603 p_count => x_msg_count ,
604 p_data => x_msg_data ,
605 p_encoded => FND_API.G_FALSE
606 );
607 END Create_Ruleset;
608 --=========================================================================
609 -- Start of comments
610 -- API name : Update_Ruleset
611 -- Type : Public
612 -- Function : This Public API can be used to update a rule,
613 -- a ruleset or rule attributes in Oracle Sales
614 -- Compensation.
615 -- Pre-reqs : None.
616 -- Parameters :
617 -- IN : p_api_version IN NUMBER Required
618 -- p_init_msg_list IN VARCHAR2 Optional
619 -- Default = FND_API.G_FALSE
620 -- p_commit IN VARCHAR2 Optional
621 -- Default = FND_API.G_FALSE
622 -- p_validation_level IN NUMBER Optional
623 -- Default = FND_API.G_VALID_LEVEL_FULL
624 -- p_ruleset_rec_type IN
625 -- CN_Ruleset_PVT.ruleset_rec_type
626 --
627 -- OUT : x_return_status OUT VARCHAR2(1)
628 -- x_msg_count OUT NUMBER
629 -- x_msg_data OUT VARCHAR2(2000)
630 --
631 -- Version : Current version 1.0
632 -- 25-Mar-99 Renu Chintalapati
633 -- previous version y.y
634 -- Changed....
635 -- Initial version 1.0
636 -- 25-Mar-99 Renu Chintalapati
637 --
638 -- Notes : Note text
639 --
640 -- End of comments
641 --=========================================================================
642
643 PROCEDURE Update_Ruleset
644 ( p_api_version IN NUMBER,
645 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
646 p_commit IN VARCHAR2 := FND_API.G_FALSE,
647 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 x_loading_status OUT NOCOPY VARCHAR2,
652 p_old_ruleset_rec IN OUT NOCOPY CN_Ruleset_PVT.ruleset_rec_type,
653 p_ruleset_rec IN OUT NOCOPY CN_Ruleset_PVT.ruleset_rec_type
654 ) IS
655
656
657 CURSOR l_ovn_csr IS
658 SELECT nvl(object_version_number,1)
659 FROM cn_rulesets
660 WHERE ruleset_id = p_old_ruleset_rec.ruleset_id AND
661 ORG_ID=p_old_ruleset_rec.org_id;
662
663 l_env_org_id NUMBER;
664
665 CURSOR rules IS
666 SELECT count(1)
667 FROM cn_rules_all_b
668 WHERE ruleset_id = p_old_ruleset_rec.ruleset_id
669 and rule_id <> -1002 and
670 org_id = p_old_ruleset_rec.org_id;
671
672 l_api_name CONSTANT VARCHAR2(30) := 'Update_Ruleset';
673 l_api_version CONSTANT NUMBER := 1.0;
674 l_loading_st VARCHAR2(4000);
675 l_count NUMBER;
676
677 l_ruleset_status VARCHAR2(100);
678 l_request_id NUMBER;
679 l_object_version_number cn_attribute_rules.object_version_number%TYPE;
680
681 l_rules NUMBER;
682
683
684 CURSOR get_ruleset_data ( l_ruleset_id NUMBER,l_org_id Number) IS
685 SELECT *
686 FROM cn_rulesets
687 WHERE ruleset_id = l_ruleset_id and
688 org_id=l_org_id;
689
690 l_get_ruleset_data_rec get_ruleset_data%ROWTYPE;
691
692
693 BEGIN
694
695 l_env_org_id := p_ruleset_rec.org_id;
696 -- Standard Start of API savepoint
697 SAVEPOINT Update_Ruleset;
698 -- Standard call to check for call compatibility.
699 IF NOT FND_API.Compatible_API_Call ( l_api_version,
700 p_api_version,
701 l_api_name,
702 G_PKG_NAME )
703 THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 END IF;
706
707 -- Initialize message list if p_init_msg_list is set to TRUE.
708 IF FND_API.to_Boolean( p_init_msg_list )
709 THEN
710 FND_MSG_PUB.initialize;
711 END IF;
712
713 -- Initialize API return status to success
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715 x_loading_status := 'CN_UPDATED';
716
717 --
718 -- Unsync the rulesets
719 cn_syin_rules_pkg.unsync_ruleset(p_ruleset_rec.ruleset_id,p_ruleset_rec.org_id);
720
721
722
723 p_old_ruleset_rec.ruleset_id := p_ruleset_rec.ruleset_id;
724
725 select name,
726 start_date,
727 end_date,
728 module_type
729 into p_old_ruleset_rec.ruleset_name,
730 p_old_ruleset_rec.start_date,
731 p_old_ruleset_rec.end_date,
732 p_old_ruleset_rec.module_type
733 from cn_rulesets
734 where ruleset_id = p_ruleset_rec.ruleset_id and
735 org_id=p_ruleset_rec.org_id;
736
737 -- API body
738 IF p_ruleset_rec.ruleset_name <> p_old_ruleset_rec.ruleset_name
739 OR p_ruleset_rec.module_type <> p_old_ruleset_rec.module_type
740 OR p_ruleset_rec.start_date <> nvl(p_old_ruleset_rec.start_date, p_ruleset_rec.start_date + 1)
741 OR p_ruleset_rec.end_date <> nvl(p_old_ruleset_rec.end_date, p_ruleset_rec.end_date + 1)
742 THEN
743 --ruleset needs to be updated
744
745 --Validate input parameters
746 --Check for missing parameters in the p_ruleset_rec parameter
747
748 IF (cn_api.chk_miss_null_char_para
749 ( p_old_ruleset_rec.ruleset_name,
750 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
751 x_loading_status,
752 x_loading_status) = FND_API.G_TRUE )
753 THEN
754 RAISE fnd_api.g_exc_error;
755 END IF;
756
757 --Check for missing parameters in the p_ruleset_rec parameter
758
759 IF (cn_api.chk_miss_null_char_para
760 ( p_ruleset_rec.ruleset_name,
761 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
762 x_loading_status,
763 x_loading_status) = FND_API.G_TRUE )
764 THEN
765 RAISE fnd_api.g_exc_error;
766 END IF;
767
768 IF cn_api.chk_miss_null_date_para
769 ( p_ruleset_rec.end_date,
770 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
771 x_loading_status,
772 x_loading_status) = fnd_api.g_true
773 THEN
774 RAISE fnd_api.g_exc_error;
775 END IF;
776
777 IF cn_api.chk_miss_null_date_para
778 (p_ruleset_rec.start_date,
779 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
780 x_loading_status,
781 x_loading_status)= fnd_api.g_true
782 THEN
783 RAISE fnd_api.g_exc_error;
784 END IF;
785
786
787 --Now check if the ruleset exists.
788 --If it does, then raise error
789 --else use cn_rulesets_s.nextval
790 SELECT count(1)
791 INTO l_count
792 FROM cn_rulesets
793 WHERE ruleset_id = p_old_ruleset_rec.ruleset_id and
794 org_id=p_old_ruleset_rec.org_id;
795
796 IF l_count = 0
797 THEN
798 --Error condition
799 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
800 THEN
801 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
802 fnd_msg_pub.add;
803 END IF;
804
805 x_loading_status := 'CN_INVALID_RULESET';
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808
809 IF p_ruleset_rec.module_type NOT IN ('REVCLS', 'ACCGEN', 'PECLS')
810 THEN
811
812 --Error condition
813 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
814 THEN
815 fnd_message.set_name('CN', 'CN_INVALID_RULESET_TYPE');
816 fnd_msg_pub.add;
817 END IF;
818 x_loading_status := 'CN_INVALID_RULESET_TYPE';
819
820 RAISE fnd_api.g_exc_error;
821 END IF;
822
823 -- check if the object version number is the same
824 OPEN l_ovn_csr;
825 FETCH l_ovn_csr INTO l_object_version_number;
826 CLOSE l_ovn_csr;
827
828
829 IF (l_object_version_number <>
830 p_ruleset_rec.object_version_number) THEN
831
832 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
833 THEN
834 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
835 fnd_msg_pub.add;
836 END IF;
837
838 x_loading_status := 'CN_INVALID_OBJECT_VERSION';
839 RAISE FND_API.G_EXC_ERROR;
840
841 end if;
842
843
844 IF p_ruleset_rec.start_date <> p_old_ruleset_rec.start_date
845 OR p_ruleset_rec.end_date <> p_old_ruleset_rec.end_date
846 THEN
847 --validate the periods before updating
848 IF check_ruleset_dates(p_ruleset_rec.start_date,
849 p_ruleset_rec.end_date,
850 p_ruleset_rec.module_type,
851 p_old_ruleset_rec.ruleset_id,
852 p_ruleset_rec.org_id,
853 x_loading_status,
854 x_loading_status) = fnd_api.g_true
855 THEN
856 RAISE fnd_api.g_exc_error;
857 --Error condition
858 --IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
859 -- THEN
860 -- fnd_message.set_name('CN', 'CN_INVALID_RULESET');
861 -- fnd_msg_pub.add;
862 --END IF;
863 ----x_loading_status := 'CN_INVALID_RULESET';
864 ----RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 END IF;
867
868 IF check_update_allowed
869 ( p_old_ruleset_rec.ruleset_id,
870 p_ruleset_rec.module_type,
871 x_loading_status,
872 x_loading_status,
873 P_old_ruleset_rec.org_id) = fnd_api.g_true
874 THEN
875 RAISE fnd_api.g_exc_error;
876 END IF;
877
878 p_ruleset_rec.object_version_number:=p_ruleset_rec.object_version_number+1;
879 cn_syin_rulesets_pkg.update_row
880
881 (
882 x_ruleset_id => p_old_ruleset_rec.ruleset_id,
883 x_object_version_number => p_ruleset_rec.object_version_number,
884 x_end_date => p_ruleset_rec.end_date,
885 x_ruleset_status => 'UNSYNC',
886 x_destination_column_id => -11980,
887 x_repository_id => 100,
888 x_start_date => p_ruleset_rec.start_date,
889 x_name => p_ruleset_rec.ruleset_name,
890 x_module_type => p_ruleset_rec.module_type,
891 x_last_update_date => sysdate,
892 x_last_updated_by => g_last_updated_by,
893 x_last_update_login => g_last_update_login,
894 x_org_id => p_ruleset_rec.org_id
895 );
896
897 END IF;
898
899 -- sync the rulesets
900
901 IF nvl(p_ruleset_rec.sync_flag,'N') = 'Y' then
902
903 -- Check sync allowed
904
905 open rules;
906 fetch rules into l_rules;
907 close rules;
908
909 --
910 -- check sync allowed.
911 --
912 IF nvl(l_rules,0) = 0 THEN
913 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
914 THEN
915 fnd_message.set_name('CN', 'CN_NO_RULES_DEFINED');
916 fnd_msg_pub.add;
917 END IF;
918 x_loading_status := 'CN_NO_RULES_DEFINED';
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921
922
923 IF check_sync_allowed
924 ( p_old_ruleset_rec.ruleset_name,
925 p_old_ruleset_rec.ruleset_id,
926 p_old_ruleset_rec.org_id,
927 x_loading_status,
928 x_loading_status ) = fnd_api.g_true
929 THEN
930 RAISE fnd_api.g_exc_error;
931 END IF;
932
933 cn_rulesets_pkg.sync_ruleset(p_ruleset_rec.ruleset_id,l_ruleset_status,l_env_org_id);
934
935 -- Kumar
936 -- changed from GENERATED to INSTINPG ( Install in Process )
937 -- Date : 11/07/2001
938
939 IF l_ruleset_status = 'INSTINPG'
940 THEN
941 cn_classification_conc_submit.submit_request(abs(p_ruleset_rec.ruleset_id),
942 l_request_id,p_ruleset_rec.org_id);
943 p_ruleset_rec.status:='INSTINPG';
944 --
945 -- l_request_id will be null or zero only if the concurrent manager is down.
946 -- CONCFAIL Concurrent Manager Down.
947 --
948
949 IF l_request_id iS NULL or l_request_id = 0 THEN
950
951 OPEN get_ruleset_data(p_ruleset_rec.ruleset_id,p_ruleset_rec.org_id);
952 FETCH get_ruleset_data INTO l_get_ruleset_data_rec;
953 CLOSE get_ruleset_data;
954 cn_syin_rulesets_pkg.update_row(p_ruleset_rec.ruleset_id,
955 l_get_ruleset_data_rec.object_version_number,
956 'CONCFAIL',
957 l_get_ruleset_data_rec.destination_column_id,
958 l_get_ruleset_data_rec.repository_id,
959 l_get_ruleset_data_rec.start_date,
960 l_get_ruleset_data_rec.end_date,
961 l_get_ruleset_data_rec.name,
962 l_get_ruleset_data_rec.module_type,
963 null,
964 null,
965 null,
966 p_ruleset_rec.org_id);
967 p_ruleset_rec.status:='CONCFAIL';
968 END IF;
969
970
971 END IF;
972 END IF;
973
974 -- End of API body.
975 -- Standard check of p_commit.
976 IF FND_API.To_Boolean( p_commit )
977 THEN
978 COMMIT WORK;
979 END IF;
980
981
982 -- Standard call to get message count and if count is 1, get message info.
983 FND_MSG_PUB.Count_And_Get
984 (
985 p_count => x_msg_count ,
986 p_data => x_msg_data ,
987 p_encoded => FND_API.G_FALSE
988 );
989
990 EXCEPTION
991 WHEN FND_API.G_EXC_ERROR THEN
992 ROLLBACK TO Update_Ruleset;
993 x_return_status := FND_API.G_RET_STS_ERROR ;
994
995 FND_MSG_PUB.Count_And_Get
996 (
997 p_count => x_msg_count ,
998 p_data => x_msg_data ,
999 p_encoded => FND_API.G_FALSE
1000 );
1001
1002 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1003 ROLLBACK TO Update_Ruleset;
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1005 FND_MSG_PUB.Count_And_Get
1006 (
1007 p_count => x_msg_count ,
1008 p_data => x_msg_data ,
1009 p_encoded => FND_API.G_FALSE
1010 );
1011
1012 WHEN OTHERS THEN
1013 ROLLBACK TO Update_Ruleset;
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1015 IF FND_MSG_PUB.Check_Msg_Level
1016 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1017 THEN
1018 FND_MSG_PUB.Add_Exc_Msg
1019 (G_PKG_NAME,
1020 l_api_name
1021 );
1022 END IF;
1023
1024 FND_MSG_PUB.Count_And_Get
1025 (
1026 p_count => x_msg_count ,
1027 p_data => x_msg_data ,
1028 p_encoded => FND_API.G_FALSE
1029 );
1030
1031 END;
1032
1033 END CN_Ruleset_PVT;