[Home] [Help]
PACKAGE BODY: APPS.CN_SYIN_RULES_PKG
Source
1 PACKAGE BODY cn_syin_rules_pkg AS
2 -- $Header: cnsyinfb.pls 120.6 2006/01/13 03:57:15 hanaraya noship $
3
4
5 -- Procedure Name
6 -- Populate_Fields
7 -- Purpose
8
9 -- History
10 -- 01/26/94 Tony Lower Created
11 -- 08-08-95 Amy Erickson Updated
12 -- 08-30-95 Amy Erickson Updated
13
14 PROCEDURE Populate_Fields (x_revenue_class_id IN OUT NOCOPY number,
15 x_revenue_class_name IN OUT NOCOPY varchar2,
16 x_org_id IN NUMBER) IS
17
18 BEGIN
19
20 IF (x_revenue_class_id IS NOT NULL) THEN
21 SELECT name
22 INTO x_revenue_class_name
23 FROM cn_revenue_classes
24 WHERE revenue_class_id = x_revenue_class_id and org_id=x_org_id;
25 ELSE
26 x_revenue_class_name := NULL ;
27 END IF;
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 x_revenue_class_id := NULL ;
32 x_revenue_class_name := NULL ;
33
34 END Populate_fields;
35
36
37
38
39 PROCEDURE Insert_Row (x_rule_id number,
40 x_name varchar2,
41 x_ruleset_id number,
42 x_revenue_class_id number,
43 x_expense_ccid NUMBER,
44 x_liability_ccid NUMBER,
45 x_parent_rule_id number,
46 x_sequence_number number,
47 x_org_id number) IS
48 l_rowid ROWID;
49 BEGIN
50 insert_row_into_cn_rules_only(
51 x_rowid => l_rowid,
52 x_rule_id => x_rule_id,
53 x_name => x_name,
54 x_ruleset_id => x_ruleset_id,
55 x_revenue_class_id => x_revenue_class_id,
56 x_expense_ccid => x_expense_ccid,
57 x_liability_ccid => x_liability_ccid,
58 x_org_id =>x_org_id);
59
60 INSERT INTO cn_rules_hierarchy
61 (rule_id, parent_rule_id, sequence_number, ruleset_id,org_id)
62 VALUES (x_rule_id, x_parent_rule_id, x_sequence_number, x_ruleset_id, x_org_id);
63
64 unsync_ruleset(x_ruleset_id,x_org_id);
65 END Insert_Row;
66
67
68 procedure insert_row_into_cn_rules_only
69 (
70 X_ROWID in out nocopy VARCHAR2,
71 X_RULE_ID in NUMBER,
72 X_RULESET_ID in NUMBER,
73 X_PACKAGE_ID in NUMBER,
74 X_REVENUE_CLASS_ID in NUMBER,
75 x_expense_ccid IN NUMBER,
76 x_liability_ccid IN NUMBER,
77 X_NAME in VARCHAR2,
78 X_CREATION_DATE in DATE,
79 X_CREATED_BY in NUMBER,
80 X_LAST_UPDATE_DATE in DATE,
81 X_LAST_UPDATED_BY in NUMBER,
82 X_LAST_UPDATE_LOGIN in NUMBER,
83 X_ORG_ID in number
84 ) IS
85
86
87
88 L_RULE_ID NUMBER;
89 L_RULESET_ID NUMBER;
90 L_PACKAGE_ID NUMBER;
91 L_REVENUE_CLASS_ID NUMBER;
92 l_expense_ccid NUMBER;
93 l_liability_ccid NUMBER;
94 L_NAME cn_rules.name%TYPE;
95 L_CREATION_DATE DATE;
96 L_CREATED_BY NUMBER;
97 L_LAST_UPDATE_DATE DATE;
98 L_LAST_UPDATED_BY NUMBER;
99 L_LAST_UPDATE_LOGIN NUMBER;
100 L_ORG_ID NUMBER;
101
102 BEGIN
103
104 SELECT Decode(x_RULE_ID, FND_API.G_MISS_NUM, NULL,
105 Ltrim(Rtrim(x_RULE_ID)))
106 INTO l_RULE_ID FROM sys.dual;
107
108 SELECT Decode(x_RULESET_ID, FND_API.G_MISS_NUM, NULL,
109 Ltrim(Rtrim(x_RULESET_ID)))
110 INTO l_RULESET_ID FROM sys.dual;
111
112 SELECT Decode(x_PACKAGE_ID, FND_API.G_MISS_NUM, NULL,
113 Ltrim(Rtrim(x_PACKAGE_ID)))
114 INTO l_PACKAGE_ID FROM sys.dual;
115
116 SELECT Decode(x_REVENUE_CLASS_ID, FND_API.G_MISS_NUM, NULL,
117 Ltrim(Rtrim(x_REVENUE_CLASS_ID)))
118 INTO l_REVENUE_CLASS_ID FROM sys.dual;
119
120 SELECT Decode(x_expense_ccid, FND_API.G_MISS_NUM, NULL,
121 Ltrim(Rtrim(x_expense_ccid)))
122 INTO l_expense_ccid FROM sys.dual;
123
124 SELECT Decode(x_liability_ccid, FND_API.G_MISS_NUM, NULL,
125 Ltrim(Rtrim(x_liability_ccid)))
126 INTO l_liability_ccid FROM sys.dual;
127
128 SELECT Decode(x_NAME, FND_API.G_MISS_CHAR, NULL,
129 Ltrim(Rtrim(x_NAME)))
130 INTO l_NAME FROM sys.dual;
131
132 SELECT Decode(x_CREATION_DATE, FND_API.G_MISS_DATE, NULL,
133 Ltrim(Rtrim(x_CREATION_DATE)))
134 INTO l_CREATION_DATE FROM sys.dual;
135
136 SELECT Decode(x_CREATED_BY, FND_API.G_MISS_NUM, NULL,
137 Ltrim(Rtrim(x_CREATED_BY)))
138 INTO l_CREATED_BY FROM sys.dual;
139
140 SELECT Decode(x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,
141 Ltrim(Rtrim(x_LAST_UPDATE_DATE)))
142 INTO l_LAST_UPDATE_DATE FROM sys.dual;
143
144 SELECT Decode(x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,
145 Ltrim(Rtrim(x_LAST_UPDATED_BY)))
146 INTO l_LAST_UPDATED_BY FROM sys.dual;
147
148 SELECT Decode(x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,
149 Ltrim(Rtrim(x_LAST_UPDATE_LOGIN)))
150 INTO l_LAST_UPDATE_LOGIN FROM sys.dual;
151
152 SELECT Decode(x_ORG_ID, FND_API.G_MISS_NUM, NULL,
153 Ltrim(Rtrim(x_ORG_ID)))
154 INTO l_ORG_ID FROM sys.dual;
155
156 insert into CN_RULES_ALL_B
157 (
158 PACKAGE_ID,
159 RULE_ID,
160 RULESET_ID,
161 REVENUE_CLASS_ID,
162 expense_ccid,
163 liability_ccid,
164 CREATION_DATE,
165 CREATED_BY,
166 LAST_UPDATE_DATE,
167 LAST_UPDATED_BY,
168 LAST_UPDATE_LOGIN,
169 ORG_ID
170 ) values
171 (
172 L_PACKAGE_ID,
173 L_RULE_ID,
174 L_RULESET_ID,
175 L_REVENUE_CLASS_ID,
176 l_expense_ccid,
177 l_liability_ccid,
178 L_CREATION_DATE,
179 L_CREATED_BY,
180 L_LAST_UPDATE_DATE,
181 L_LAST_UPDATED_BY,
182 L_LAST_UPDATE_LOGIN,
183 L_ORG_ID
184 );
185
186 insert into CN_RULES_ALL_TL (
187 NAME,
188 LAST_UPDATE_DATE,
189 LAST_UPDATED_BY,
190 LAST_UPDATE_LOGIN,
191 CREATION_DATE,
192 CREATED_BY,
193 RULESET_ID,
194 RULE_ID,
195 LANGUAGE,
196 SOURCE_LANG,
197 ORG_ID
198 ) select
199 L_NAME,
200 L_LAST_UPDATE_DATE,
201 L_LAST_UPDATED_BY,
202 L_LAST_UPDATE_LOGIN,
203 L_CREATION_DATE,
204 L_CREATED_BY,
205 L_RULESET_ID,
206 L_RULE_ID,
207 L.LANGUAGE_CODE,
208 userenv('LANG'),
209 L_ORG_ID
210 from FND_LANGUAGES L
211 where L.INSTALLED_FLAG in ('I', 'B')
212 and not exists
213 (select NULL
214 from CN_RULES_ALL_TL T
215 where T.RULE_ID = L_RULE_ID
216 and T.RULESET_ID = L_RULESET_ID --RC 06-APR-99 Added code
217 and T.LANGUAGE = L.language_code AND
218 T.ORG_ID=L_ORG_ID);
219
220
221 end INSERT_ROW_into_cn_rules_only;
222
223
224 -- Procedure Name
225 -- unsync_ruleset
226 -- History
227 -- 17-Feb-99 Renu Chintalapati Created
228
229 PROCEDURE unsync_ruleset (x_ruleset_id number, x_org_id number) IS
230 BEGIN
231 UPDATE cn_rulesets_all_b
232 SET ruleset_status = 'UNSYNC'
233 WHERE ruleset_id = x_ruleset_id
234 and ORG_ID= x_org_id ;
235 END unsync_ruleset;
236
237
238
239 procedure UPDATE_ROW
240 (
241 X_RULE_ID in NUMBER,
242 X_RULESET_ID in NUMBER,
243 X_PACKAGE_ID in NUMBER,
244 X_REVENUE_CLASS_ID in NUMBER,
245 x_expense_ccid IN NUMBER,
246 x_liability_ccid IN NUMBER,
247 X_NAME in VARCHAR2,
248 X_LAST_UPDATE_DATE in DATE,
249 X_LAST_UPDATED_BY in NUMBER,
250 X_LAST_UPDATE_LOGIN in NUMBER,
251 X_ORG_ID IN NUMBER,
252 X_OBJECT_VERSION_NO IN OUT NOCOPY NUMBER
253 ) is
254 begin
255 X_OBJECT_VERSION_NO:=X_OBJECT_VERSION_NO+1;
256 update CN_RULES_ALL_B set
257 PACKAGE_ID = X_PACKAGE_ID,
258 REVENUE_CLASS_ID = X_REVENUE_CLASS_ID,
259 expense_ccid = x_expense_ccid,
260 liability_ccid = x_liability_ccid,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
264 OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NO
265 where RULE_ID = X_RULE_ID
266 and RULESET_ID = x_ruleset_id AND
267 ORG_ID=X_ORG_ID;
268
269 if (sql%notfound) then
270 raise no_data_found;
271 end if;
272
273 update CN_RULES_ALL_TL set
274 NAME = X_NAME,
275 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
276 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
278 SOURCE_LANG = userenv('LANG')
279 where RULE_ID = X_RULE_ID
280 and RULESET_ID = X_RULESET_ID
281 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
282 ORG_ID=X_ORG_ID;
283
284 if (sql%notfound) then
285 raise no_data_found;
286 end if;
287 end UPDATE_ROW;
288
289
290 procedure DELETE_ROW (X_RULE_ID in NUMBER,
291 X_RULESET_ID in NUMBER,
292 X_ORG_ID IN NUMBER) IS --RC 2/25/99 Added ruleset id
293 Cursor Cascade IS (SELECT rule_id
294 FROM cn_rules_hierarchy
295 WHERE parent_rule_id = x_rule_id
296 AND ruleset_id = x_ruleset_id AND
297 ORG_ID=X_ORG_ID);
298 BEGIN
299
300 DELETE cn_attribute_rules
301 WHERE rule_id = x_rule_id
302 AND ruleset_id = x_ruleset_id ;
303
304 DELETE cn_rules_hierarchy
305 WHERE rule_id = x_rule_id
306 AND ruleset_id = x_ruleset_id;
307
308 delete from CN_RULES_ALL_TL
309 where RULE_ID = X_RULE_ID
310 and ruleset_id = x_ruleset_id
311 and ORG_ID=X_ORG_ID;
312
313 if (sql%notfound) then
314 raise no_data_found;
315 end if;
316
317 delete from CN_RULES_ALL_B
318 where RULE_ID = X_RULE_ID
319 and ruleset_id = x_ruleset_id AND
320 ORG_ID=X_ORG_ID;
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325
326 unsync_ruleset(x_ruleset_id,X_ORG_ID);
327
328 FOR C in Cascade LOOP
329 Delete_Row(C.rule_id, x_ruleset_id,X_ORG_ID);
330 END Loop;
331
332 DELETE cn_rules_hierarchy
333 WHERE parent_rule_id = x_rule_id
334 AND ruleset_id = x_ruleset_id AND
335 ORG_ID=X_ORG_ID;
336
337 end DELETE_ROW;
338
339
340
341 procedure ADD_LANGUAGE
342 is
343 begin
344 delete from CN_RULES_ALL_TL T
345 where not exists
346 (select NULL
347 from CN_RULES_ALL_B B
348 where B.RULE_ID = T.RULE_ID
349 and B.RULESET_ID = T.ruleset_id
350 and B.ORG_ID= T.ORG_ID
351 );
352
353 update CN_RULES_ALL_TL T set (
354 NAME
355 ) = (select
356 B.NAME
357 from CN_RULES_ALL_TL B
358 where B.RULE_ID = T.RULE_ID
359 and B.RULESET_ID = T.RULESET_ID
360 and B.LANGUAGE = T.source_lang
361 and B.ORG_ID= T.ORG_ID)
362 where (
363 T.RULE_ID,
364 T.RULESET_ID,
365 T.LANGUAGE
366 ) in (select
367 SUBT.RULE_ID,
368 SUBT.RULESET_ID,
369 SUBT.LANGUAGE
370 from CN_RULES_ALL_TL SUBB, CN_RULES_ALL_TL SUBT
371 where SUBB.RULE_ID = SUBT.RULE_ID
372 and SUBB.RULESET_ID = SUBT.RULESET_ID
373 and SUBB.LANGUAGE = SUBT.source_lang
374 and SUBB.ORG_ID=SUBT.ORG_ID
375
376 and (SUBB.NAME <> SUBT.NAME
377 or (SUBB.NAME is null and SUBT.NAME is not null)
378 or (SUBB.NAME is not null and SUBT.NAME is null)
379 ));
380
381 insert into CN_RULES_ALL_TL (
382 NAME,
383 LAST_UPDATE_DATE,
384 LAST_UPDATED_BY,
385 LAST_UPDATE_LOGIN,
386 CREATION_DATE,
387 CREATED_BY,
388 RULESET_ID,
389 RULE_ID,
390 LANGUAGE,
391 SOURCE_LANG,
392 ORG_ID
393 ) select
394 B.NAME,
395 B.LAST_UPDATE_DATE,
396 B.LAST_UPDATED_BY,
397 B.LAST_UPDATE_LOGIN,
398 B.CREATION_DATE,
399 B.CREATED_BY,
400 B.RULESET_ID,
401 B.RULE_ID,
402 L.LANGUAGE_CODE,
403 B.SOURCE_LANG,
404 B.ORG_ID
405 from CN_RULES_ALL_TL B, FND_LANGUAGES L
406 where L.INSTALLED_FLAG in ('I', 'B')
407 and B.LANGUAGE = userenv('LANG')
408 and not exists
409 (select NULL
410 from CN_RULES_ALL_TL T
411 where T.RULE_ID = B.RULE_ID
412 and T.RULESET_ID = B.RULESET_ID
413 and T.LANGUAGE = L.language_code AND
414 T.ORG_ID=B.ORG_ID);
415 end ADD_LANGUAGE;
416
417
418
419
420
421 --------------------------------------------------------------------------+
422 -- Procedure Name: download --+
423 -- Purpose --+
424 -- This procedure is used to download the required data to the inter- --+
425 -- face table for export to a different data base --+
426 --------------------------------------------------------------------------+
427 PROCEDURE download
428 (errbuf OUT NOCOPY VARCHAR2,
429 retcode OUT NOCOPY NUMBER) IS
430
431 TYPE cnclrl_api_type IS RECORD
432 (clrl_api_id NUMBER,
433 ruleset_name cn_clrl_api_v.ruleset_name%TYPE,
434 start_date cn_clrl_api_v.start_date%TYPE,
435 end_date cn_clrl_api_v.end_date%TYPE,
436 rule_name cn_clrl_api_v.rule_name%TYPE,
437 parent_rule_name cn_clrl_api_v.parent_rule_name%TYPE,
438 revenue_class_name cn_clrl_api_v.revenue_class_name%TYPE,
439 object_name cn_clrl_api_v.object_name%TYPE,
440 not_flag cn_clrl_api_v.not_flag%TYPE,
441 value_1 cn_clrl_api_v.value_1%TYPE,
442 value_2 cn_clrl_api_v.value_2%TYPE,
443 data_flag cn_clrl_api_v.data_flag%TYPE
444 );
445
446 TYPE cnclrl_tbl_type IS TABLE OF cnclrl_api_type INDEX BY BINARY_INTEGER;
447 cnclrl_tbl cnclrl_tbl_type;
448
449 CURSOR cnclrl_cur IS
450 SELECT *
451 FROM cn_clrl_api_v;
452
453 l_proc_audit_id NUMBER;
454 j NUMBER;
455 l_api_id NUMBER;
456
457 BEGIN
458
459 /* retcode := 0;
460 -- Initial message list
461 FND_MSG_PUB.initialize;
462
463 cn_message_pkg.begin_batch
464 ( x_process_type => 'CLS',
465 x_process_audit_id => l_proc_audit_id,
466 x_parent_proc_audit_id => l_proc_audit_id,
467 x_request_id => NULL);
468 cn_message_pkg.debug('***************************************************');
469 cn_message_pkg.debug('Processing Classification Rule set');
470
471 j := 0;
472
473 FOR i IN cnclrl_cur
474 LOOP
475 SELECT cn_clrl_api_s.NEXTVAL
476 INTO l_api_id
477 FROM dual;
478
479 cnclrl_tbl(j).clrl_api_id := l_api_id;
480 cnclrl_tbl(j).ruleset_name := i.ruleset_name;
481 cnclrl_tbl(j).start_date := i.start_date;
482 cnclrl_tbl(j).end_date := i.end_date;
483 cnclrl_tbl(j).rule_name := i.rule_name;
484 cnclrl_tbl(j).parent_rule_name := i.parent_rule_name;
485 cnclrl_tbl(j).revenue_class_name := i.revenue_class_name;
486 cnclrl_tbl(j).object_name := i.object_name ;
487 cnclrl_tbl(j).not_flag := i.not_flag ;
488 cnclrl_tbl(j).value_1 := i.value_1;
489 cnclrl_tbl(j).value_2 := i.value_2;
490 cnclrl_tbl(j).data_flag := i.data_flag;
491 j := j + 1;
492 END LOOP;
493
494 for i IN cnclrl_tbl.first .. cnclrl_tbl.last loop
495 INSERT INTO cn_clrl_api
496 (clrl_api_id,
497 ruleset_name,
498 start_date,
499 end_date,
500 rule_name,
501 parent_rule_name,
502 revenue_class_name,
503 object_name,
504 not_flag,
505 value_1,
506 value_2,
507 data_flag)
508 VALUES
509 (cnclrl_tbl(i).clrl_api_id,
510 cnclrl_tbl(i).ruleset_name,
511 cnclrl_tbl(i).start_date,
512 cnclrl_tbl(i).end_date,
513 cnclrl_tbl(i).rule_name,
514 cnclrl_tbl(i).parent_rule_name,
515 cnclrl_tbl(i).revenue_class_name,
516 cnclrl_tbl(i).object_name,
517 cnclrl_tbl(i).not_flag,
518 cnclrl_tbl(i).value_1,
519 cnclrl_tbl(i).value_2,
520 cnclrl_tbl(i).data_flag);
521 END LOOP;
522
523 cn_message_pkg.end_batch(l_proc_audit_id);
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 cn_message_pkg.debug('Unhandled Exception');
528 cn_message_pkg.end_batch(l_proc_audit_id);
529 retcode := 2;
530 errbuf := SQLCODE || ' ' || Sqlerrm;
531 */
532 null;
533 END download;
534
535 --------------------------------------------------------------------------+
536 -- Procedure Name: upload --+
537 -- Purpose --+
538 -- This procedure is used to upload the required data from the inter- --+
539 -- face table to the appropriate tables in the database --+
540 --------------------------------------------------------------------------+
541 PROCEDURE upload(errbuf OUT NOCOPY VARCHAR2,
542 retcode OUT NOCOPY NUMBER) IS
543
544 CURSOR rulesets
545 IS SELECT ruleset_name, start_date, end_date
546 FROM cn_clrl_api
547 WHERE loading_status <> 'CN_INSERTED' OR loading_status IS NULL
548 GROUP BY ruleset_name, start_date, end_date;
549
550 CURSOR top_level_rules
551 (p_ruleset_name cn_clrl_api.ruleset_name%TYPE,
552 p_start_date cn_clrl_api.start_date%TYPE,
553 p_end_date cn_clrl_api.end_date%TYPE)
554 IS SELECT rule_name
555 FROM cn_clrl_api cna1
556 WHERE ruleset_name = p_ruleset_name
557 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL)
558 AND parent_rule_name NOT IN
559 (SELECT rule_name
560 FROM cn_clrl_api
561 WHERE ruleset_name = p_ruleset_name
562 AND start_date = p_start_date
563 AND end_date = p_end_date
564 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL) );
565
566 CURSOR rules
567 (p_ruleset_name cn_clrl_api.ruleset_name%TYPE,
568 p_start_date cn_clrl_api.start_date%TYPE,
569 p_end_date cn_clrl_api.end_date%TYPE,
570 p_start_rule_name cn_clrl_api.rule_name%TYPE)IS
571 SELECT rule_name, parent_rule_name, revenue_class_name
572 FROM (SELECT rule_name, parent_rule_name, revenue_class_name
573 FROM cn_clrl_api
574 WHERE ruleset_name = p_ruleset_name
575 AND start_date = p_start_date
576 AND end_date = p_end_date
577 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL)
578 GROUP BY rule_name, parent_rule_name, revenue_class_name)
579 CONNECT BY PRIOR rule_name = parent_rule_name
580 START WITH rule_name = p_start_rule_name;
581
582 CURSOR rule_attributes
583 (p_ruleset_name cn_clrl_api.ruleset_name%TYPE,
584 p_start_date cn_clrl_api.start_date%TYPE,
585 p_end_date cn_clrl_api.end_date%TYPE,
586 p_rule_name cn_clrl_api.rule_name%TYPE,
587 p_parent_rule_name cn_clrl_api.parent_rule_name%TYPE) IS
588 SELECT attribute_rule_name, not_flag,
589 value_1, value_2, data_flag, object_name
590 FROM cn_clrl_api
591 WHERE loading_status <> 'CN_INSERTED' OR loading_status IS NULL
592 AND ruleset_name = p_ruleset_name
593 AND rule_name = p_rule_name
594 AND parent_rule_name = p_parent_rule_name;
595
596 l_api_version CONSTANT NUMBER := 1.0;
597 l_msg_count NUMBER;
598 l_msg_data VARCHAR2(2000);
599 l_return_status VARCHAR2(1);
600 l_loading_status VARCHAR2(30);
601 l_loaded_counter NUMBER := 0;
602 l_total_counter NUMBER := 0;
603 l_proc_audit_id NUMBER(15);
604 l_ruleset_rec cn_ruleset_pub.ruleset_rec_type;
605 l_rule_rec cn_rule_pub.rule_rec_type;
606 l_ruleattribute_rec cn_ruleattribute_pub.ruleattribute_rec_type;
607 l_count NUMBER;
608
609 BEGIN
610 /*
611 retcode := 0;
612 -- Initial message list
613 FND_MSG_PUB.initialize;
614
615 cn_message_pkg.begin_batch
616
617 ( x_process_type => 'CLS',
618 x_process_audit_id => l_proc_audit_id,
619 x_parent_proc_audit_id => l_proc_audit_id,
620 x_request_id => NULL
621 );
622 cn_message_pkg.debug('***************************************************');
623 cn_message_pkg.debug('Processing Classification Rule set');
624
625 FOR i IN rulesets
626 LOOP
627 SELECT COUNT(1)
628 INTO l_count
629 FROM cn_rulesets
630 WHERE name = i.ruleset_name
631 AND start_date = i.start_date
632 AND end_date = i.end_date;
633
634 IF l_count = 0
635 THEN
636 l_ruleset_rec.ruleset_name := i.ruleset_name;
637 l_ruleset_rec.start_date := i.start_date;
638 l_ruleset_rec.end_date := i.end_date;
639
640
641 -- Need to refactor public package
642 cn_ruleset_pub.create_ruleset
643 ( p_api_version => 1.0,
644 p_init_msg_list => fnd_api.g_true,
645 p_commit => FND_API.G_FALSE,
646 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
647 x_return_status => l_return_status,
648 x_msg_count => l_msg_count,
649 x_msg_data => l_msg_data,
650 x_loading_status => l_loading_status,
651 p_ruleset_rec => l_ruleset_rec);
652
653
654 IF(l_return_status = FND_API.g_ret_sts_success) THEN
655
656 UPDATE cn_clrl_api
657 SET loading_status = 'CN_RULESET_INSERTED'
658 WHERE ruleset_name = i.ruleset_name
659 AND start_date = i.start_date
660 AND end_date = i.end_date
661 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
662
663 ELSE
664 -- retcode 0 = success, 1 = warning, 2 = error
665 retcode := 2;
666 cn_message_pkg.debug('Error for rulesets '||i.ruleset_name);
667 cn_api.get_fnd_message(l_msg_count, l_msg_data);
668 UPDATE cn_clrl_api
669 SET loading_status = l_loading_status,
670 message_text = l_msg_data,
671 return_status = l_return_status
672 WHERE ruleset_name = i.ruleset_name
673 AND start_date = i.start_date
674 AND end_date = i.end_date
675 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
676
677 END IF;
678 ELSE
679 UPDATE cn_clrl_api
680 SET loading_status = 'CN_ALREADY_EXISTS',
681 message_text = l_msg_data,
682 return_status = l_return_status
683 WHERE ruleset_name = i.ruleset_name
684 AND start_date = i.start_date
685 AND end_date = i.end_date
686 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
687
688 END IF;
689
690
691 FOR j IN top_level_rules(i.ruleset_name, i.start_date, i.end_date)
692 LOOP
693 FOR k IN rules(i.ruleset_name, i.start_date, i.end_date, j.rule_name)
694 LOOP
695
696 l_rule_rec.ruleset_name := i.ruleset_name;
697 l_rule_rec.start_date := i.start_date;
698 l_rule_rec.end_date := i.end_date;
699 l_rule_rec.rule_name := k.rule_name;
700 l_rule_rec.parent_rule_name := k.parent_rule_name;
701 l_rule_rec.revenue_class_name := k.revenue_class_name;
702
703 cn_rule_pub.create_rule
704 ( p_api_version => 1.0,
705 p_init_msg_list => fnd_api.g_true,
706 p_commit => FND_API.G_FALSE,
707 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
708 x_return_status => l_return_status,
709 x_msg_count => l_msg_count,
710 x_msg_data => l_msg_data,
711 x_loading_status => l_loading_status,
712 p_rule_rec => l_rule_rec);
713
714
715 IF(l_return_status = FND_API.g_ret_sts_success) THEN
716
717 UPDATE cn_clrl_api
718 SET loading_status = 'CN_RULE_INSERTED'
719 WHERE ruleset_name = i.ruleset_name
720 AND start_date = i.start_date
721 AND end_date = i.end_date
722 AND rule_name = k.rule_name
723 AND parent_rule_name = k.parent_rule_name
724 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
725
726 ELSE
727 SELECT COUNT(1)
728 INTO l_count
729 FROM cn_clrl_api
730 WHERE ruleset_name = i.ruleset_name
731 AND start_date = i.start_date
732 AND end_date = i.end_date
733 AND rule_name = k.rule_name
734 AND parent_rule_name = k.parent_rule_name
735 AND attribute_rule_name IS NOT NULL
736 AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
737
738
739 IF l_count <> 0 AND l_loading_status <> 'CN_INVALID_RULE_NAME'
740 THEN
741 UPDATE cn_clrl_api
742 SET loading_status = l_loading_status,
743 return_status = l_return_status,
744 message_text = l_msg_data
745 WHERE ruleset_name = i.ruleset_name
746 AND start_date = i.start_date
747 AND end_date = i.end_date
748 AND rule_name = k.rule_name
749 AND parent_rule_name = k.parent_rule_name
750 AND (loading_status <> 'CN_INSERTED'
751 OR loading_status IS NULL);
752
753 -- retcode 0 = success, 1 = warning, 2 = error
754
755 retcode := 2;
756
757 cn_message_pkg.debug('Error for rule : '||
758 k.rule_name||
759 ' with parent rule : '||
760 k.parent_rule_name);
761
762
763 END IF;
764 END IF;
765
766 FOR l IN rule_attributes(i.ruleset_name,
767 i.start_date,
768 i.end_date,
769 k.rule_name,
770 k.parent_rule_name)
771 LOOP
772
773 l_ruleattribute_rec.ruleset_name := i.ruleset_name;
774 l_ruleattribute_rec.start_date := i.end_date;
775 l_ruleattribute_rec.end_date := i.end_date;
776 l_ruleattribute_rec.rule_name := k.rule_name;
777 l_ruleattribute_rec.object_name := l.object_name;
778 l_ruleattribute_rec.not_flag := l.not_flag;
779 l_ruleattribute_rec.value_1 := l.value_1;
780 l_ruleattribute_rec.value_2 := l.value_2;
781 l_ruleattribute_rec.data_flag := l.data_flag;
782
783 cn_ruleattribute_pub.create_ruleattribute
784 ( p_api_version => 1.0,
785 p_init_msg_list => fnd_api.g_true,
786 p_commit => FND_API.G_FALSE,
787 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
788 x_return_status => l_return_status,
789 x_msg_count => l_msg_count,
790 x_msg_data => l_msg_data,
791 x_loading_status => l_loading_status,
792 p_ruleattribute_rec=> l_ruleattribute_rec);
793
794
795 IF(l_return_status = FND_API.g_ret_sts_success) THEN
796
797 UPDATE cn_clrl_api
798 SET loading_status = 'CN_INSERTED'
799 WHERE ruleset_name = i.ruleset_name
800 AND start_date = i.start_date
801 AND end_date = i.end_date
802 AND rule_name = k.rule_name
803 AND parent_rule_name = k.parent_rule_name
804 AND object_name = l.object_name
805 AND (loading_status <> 'CN_INSERTED'
806 OR loading_status IS NULL);
807
808 ELSE
809 UPDATE cn_clrl_api
810 SET loading_status = l_loading_status,
811 return_status = l_return_status,
812 message_text = l_msg_data
813 WHERE ruleset_name = i.ruleset_name
814 AND start_date = i.start_date
815 AND end_date = i.end_date
816 AND rule_name = k.rule_name
817 AND parent_rule_name = k.parent_rule_name
818 AND object_name = l.object_name
819 AND (loading_status <> 'CN_INSERTED'
820 OR loading_status IS NULL);
821
822 -- retcode 0 = success, 1 = warning, 2 = error
823
824 retcode := 2;
825
826 cn_message_pkg.debug('Error for rule attribute : '||
827 l.object_name||
828 ' in rule : '||
829 k.rule_name);
830
831
832 END IF;
833
834 END LOOP; --rule attributes
835
836 END LOOP; --each rule
837
838 END LOOP; --top level rules
839
840 END LOOP; --rulesets loop
841
842 COMMIT;
843
844 cn_message_pkg.end_batch(l_proc_audit_id);
845
846 --enter error codes for concurrrent program
847 IF retcode = 0 THEN
848 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_OK');
849 FND_MSG_PUB.Add;
850 errbuf :=
851 FND_MSG_PUB.get
852 (p_msg_index => fnd_msg_pub.G_LAST,p_encoded => FND_API.G_FALSE);
853 ELSIF retcode = 1 THEN
854 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_WARN');
855 FND_MSG_PUB.Add;
856 errbuf :=
857 FND_MSG_PUB.get
858 (p_msg_index => fnd_msg_pub.G_LAST,p_encoded => FND_API.G_FALSE);
859 ELSE
860 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_FAIL');
861 FND_MSG_PUB.Add;
862 errbuf :=
863 FND_MSG_PUB.get
864 (p_msg_index => fnd_msg_pub.G_LAST,p_encoded => FND_API.G_FALSE);
865 END IF;
866 */
867 null;
868 END upload;
869
870 -- --------------------------------------------------------------------+
871 -- Procedure : LOAD_ROW
872 -- Description : Called by FNDLOAD to upload seed datas, this procedure
873 -- only handle seed datas. ORG_ID = -3113
874 -- --------------------------------------------------------------------+
875 PROCEDURE LOAD_ROW
876 ( x_rule_id IN NUMBER,
877 x_ruleset_id IN NUMBER,
878 x_package_id IN NUMBER,
879 x_revenue_class_id IN NUMBER,
880 x_name IN VARCHAR2,
881 x_owner IN VARCHAR2,
882 x_org_id IN NUMBER) IS
883 user_id NUMBER;
884
885 BEGIN
886 -- Validate input data
887 IF (x_ruleset_id IS NULL)
888 OR (x_name IS NULL) OR (x_rule_id IS NULL) THEN
889 GOTO end_load_row;
890 END IF;
891
892 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
893 user_id := 1;
894 ELSE
895 user_id := 0;
896 END IF;
897 -- Load The record to _B table
898 UPDATE cn_rules_all_b SET
899 ruleset_id = x_ruleset_id,
900 revenue_class_id = x_revenue_class_id,
901 package_id = x_package_id,
902 last_update_date = sysdate,
903 last_updated_by = user_id,
904 last_update_login = 0,
905 org_id=x_org_id
906 WHERE rule_id = x_rule_id AND ruleset_id = x_ruleset_id AND ORG_ID=x_org_id;
907
908 IF (SQL%NOTFOUND) THEN
909 -- Insert new record to _B table
910 INSERT INTO cn_rules_all_b
911 (rule_id,
912 ruleset_id,
913 revenue_class_id,
914 package_id,
915 creation_date,
916 created_by,
917 last_update_date,
918 last_updated_by,
919 last_update_login,
920 org_id
921 ) values
922 (x_rule_id,
923 x_ruleset_id,
924 x_revenue_class_id,
925 x_package_id,
926 sysdate,
927 user_id,
928 sysdate,
929 user_id,
930 0,
931 x_org_id
932 );
933 END IF;
934 -- Load The record to _TL table
935 UPDATE cn_rules_all_tl SET
936 ruleset_id = x_ruleset_id,
937 name = x_name,
938 last_update_date = sysdate,
939 last_updated_by = user_id,
940 last_update_login = 0,
941 org_id=x_org_id,
942 source_lang = userenv('LANG')
943 WHERE rule_id = x_rule_id AND ruleset_id = x_ruleset_id and org_id=x_org_id
944 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
945
946 IF (SQL%NOTFOUND) THEN
947 -- Insert new record to _TL table
948 INSERT INTO cn_rules_all_tl
949 (rule_id,
950 ruleset_id,
951 name,
952 creation_date,
953 created_by,
954 last_update_date,
955 last_updated_by,
956 last_update_login,
957 language,
958 source_lang,
959 org_id)
960 SELECT
961 x_rule_id,
962 x_ruleset_id,
963 x_name,
964 sysdate,
965 user_id,
966 sysdate,
967 user_id,
968 0,
969 l.language_code,
970 userenv('LANG'),
971 x_org_id
972 FROM fnd_languages l
973 WHERE l.installed_flag IN ('I', 'B')
974 AND NOT EXISTS
975 (SELECT NULL
976 FROM cn_rules_all_tl t
977 WHERE t.rule_id = x_rule_id and t.org_id=x_org_id
978 AND t.language = l.language_code);
979 END IF;
980 << end_load_row >>
981 NULL;
982 END LOAD_ROW ;
983
984 -- --------------------------------------------------------------------+
985 -- Procedure : TRANSLATE_ROW
986 -- Description : Called by FNDLOAD to translate seed datas, this procedure
987 -- only handle seed datas. ORG_ID = -3113
988 -- --------------------------------------------------------------------+
989 PROCEDURE TRANSLATE_ROW
990 ( x_rule_id IN NUMBER,
991 x_ruleset_id IN NUMBER,
992 x_name IN VARCHAR2,
993 x_owner IN VARCHAR2,
994 x_org_id IN NUMBER) IS
995 user_id NUMBER;
996 BEGIN
997 -- Validate input data
998 IF (x_ruleset_id IS NULL)
999 OR (x_name IS NULL) OR (x_rule_id IS NULL) THEN
1000 GOTO end_translate_row;
1001 END IF;
1002
1003 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
1004 user_id := 1;
1005 ELSE
1006 user_id := 0;
1007 END IF;
1008 -- Update the translation
1009 UPDATE cn_rules_all_tl SET
1010 name = x_name,
1011 last_update_date = sysdate,
1012 last_updated_by = user_id,
1013 last_update_login = 0,
1014 source_lang = userenv('LANG')
1015 WHERE rule_id = x_rule_id
1016 AND ruleset_id = x_ruleset_id and org_id=x_org_id
1017 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1018
1019 << end_translate_row >>
1020 NULL;
1021 END TRANSLATE_ROW ;
1022
1023 END cn_syin_rules_pkg;