[Home] [Help]
PACKAGE BODY: APPS.EGO_RULE_SETS_PKG
Source
1 PACKAGE BODY EGO_RULE_SETS_PKG AS
2 /* $Header: EGOVRSTB.pls 120.1.12020000.5 2013/04/19 06:36:59 leizhzha ship $ */
3
4
5 --------------------------------------------------- R12.2 Rule Enhancement Data Migration begin ----------------------------------------------------
6
7 -- ruleset
8 RULESET_NAME VARCHAR2(500);
9 RULESET_ID NUMBER;
10 ITEM_CATALOG_GROUP NUMBER;
11 BUSINESS_ENTITIES VARCHAR2(500);
12 ATTR_GROUP_TYPE VARCHAR2(100);
13 ATTR_GROUP_NAME VARCHAR2(100);
14 RULESET_OK BOOLEAN;
15
16 -- rule
17 RULE_NAME VARCHAR2(500);
18 RULE_OK BOOLEAN;
19
20 -- usage attributes
21 USAGE_ATTR_TBL USAGE_ATTR_TBL_TYPE;
22 USAGE_ATTR_COUNT NUMBER;
23
24 -- log message
25 MSG_TYPE VARCHAR2(100);
26 MSG_DATA VARCHAR2(2000);
27
28 /**
29 * We just support one log file per ruleset
30 **/
31 log_path VARCHAR2(2000) DEFAULT '/usr/tmp';
32 log_name VARCHAR2(1000);
33 log_file UTL_FILE.FILE_TYPE;
34
35
36
37 procedure LOG_MSG (X_MSG_TYPE IN VARCHAR2, X_MSG_DATA IN VARCHAR2) is
38
39 begin
40 log_file := utl_file.fopen(log_path, log_name, 'A');
41 utl_file.put_line(log_file, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS ') || X_MSG_TYPE || '::' || X_MSG_DATA);
42 utl_file.fclose(log_file);
43 end LOG_MSG;
44
45 function BUSINESS_ENTITIES_TO_STRING (X_RULESET_ID in number) return varchar2
46 is
47
48 cursor c_business_entity(c_ruleset_id in number)
49 is
50 select business_entity
51 from EGO_INCLUDED_BUSINESSENTITIES
52 where ruleset_id = c_ruleset_id;
53
54 l_business_entities varchar2(500);
55 l_business_entity_tbl DATA_LEVEL_TBL_TYPE;
56 begin
57
58 open c_business_entity(X_RULESET_ID);
59 fetch c_business_entity bulk collect into l_business_entity_tbl;
60 close c_business_entity;
61
62 for i in 1.. l_business_entity_tbl.COUNT loop
63
64 if i <> 1 then
65 l_business_entities := l_business_entities || ',';
66 end if;
67
68 l_business_entities := l_business_entities || l_business_entity_tbl(i);
69 end loop;
70
71 return l_business_entities;
72
73 end BUSINESS_ENTITIES_TO_STRING;
74
75 function BUSINESS_ENTITIES_TO_ARRAY (X_BUSINESS_ENTITIES in varchar2) return DATA_LEVEL_TBL_TYPE
76 is
77
78 l_begin integer;
79 l_index integer;
80 l_value varchar2(50);
81 l_split_string varchar2(500);
82 l_business_entity_tbl DATA_LEVEL_TBL_TYPE;
83
84 begin
85
86 IF X_BUSINESS_ENTITIES IS NULL THEN
87 return l_business_entity_tbl;
88 END IF;
89
90 IF INSTR(X_BUSINESS_ENTITIES, ',') = 0 THEN
91 l_business_entity_tbl(1) := X_BUSINESS_ENTITIES;
92 return l_business_entity_tbl;
93 END IF;
94
95 l_begin := 0;
96 l_index := 0;
97 l_split_string := X_BUSINESS_ENTITIES;
98
99 LOOP
100
101 l_begin := INSTR(l_split_string, ',');
102
103 EXIT WHEN l_begin < 1;
104
105 l_value := Substr(l_split_string, 1, l_begin-1);
106 l_split_string := Substr(l_split_string, l_begin+1);
107
108 l_index := l_index+1;
109 l_business_entity_tbl(l_index) := l_value;
110 END LOOP;
111
112 IF (Length(l_split_string) > 0) THEN
113 l_index := l_index+1;
114 l_business_entity_tbl(l_index) := l_split_string;
115 END IF;
116
117 return l_business_entity_tbl;
118
119 end BUSINESS_ENTITIES_TO_ARRAY;
120
121 function ATTR_GROUP_DATA_LEVEL_OK (X_ATTR_GROUP_ID in number, X_BUSINESS_ENTITIES in varchar2, X_MISSING_ENT out nocopy varchar2) return boolean
122 is
123
124 cursor c_data_level(c_attr_group_id in number)
125 is
126 select DL_V.DATA_LEVEL_NAME
127 from EGO_ATTR_GROUP_DL AG_DL LEFT JOIN EGO_DATA_LEVEL_VL DL_V ON AG_DL.DATA_LEVEL_ID = DL_V.DATA_LEVEL_ID
128 where AG_DL.ATTR_GROUP_ID = c_attr_group_id;
129
130 l_has_found boolean;
131 l_business_entity varchar2(50);
132 l_data_level_tbl DATA_LEVEL_TBL_TYPE;
133 l_business_entity_tbl DATA_LEVEL_TBL_TYPE;
134
135 begin
136
137 open c_data_level(X_ATTR_GROUP_ID);
138 fetch c_data_level bulk collect into l_data_level_tbl;
139 close c_data_level;
140
141 l_business_entity_tbl := BUSINESS_ENTITIES_TO_ARRAY(X_BUSINESS_ENTITIES);
142
143 FOR i in 1.. l_business_entity_tbl.COUNT LOOP
144
145 l_business_entity := l_business_entity_tbl(i);
146
147 l_has_found := false;
148 FOR j in 1.. l_data_level_tbl.COUNT LOOP
149 IF l_business_entity = l_data_level_tbl(j) THEN
150 l_has_found := true;
151 END IF;
152 END LOOP;
153
154 IF l_has_found = false THEN
155 X_MISSING_ENT := l_business_entity;
156 return false;
157 END IF;
158 END LOOP;
159
160 return true;
161
162 end ATTR_GROUP_DATA_LEVEL_OK;
163
164 procedure CLEAR_RULESET is
165
166 begin
167 RULESET_NAME := null;
168 RULESET_ID := null;
169 ITEM_CATALOG_GROUP := null;
170 BUSINESS_ENTITIES := null;
171 ATTR_GROUP_TYPE := null;
172 ATTR_GROUP_NAME := null;
173 RULESET_OK := false;
174 end CLEAR_RULESET;
175
176 procedure INITIALIZE_RULESET (X_RULESET_NAME IN VARCHAR2, X_LOG_NAME IN VARCHAR2) is
177
178 begin
179
180 CLEAR_RULESET;
181
182 RULESET_NAME := X_RULESET_NAME;
183 -- initialize log file
184 if X_LOG_NAME is not null then
185 log_name := X_LOG_NAME;
186 else
187 log_name := X_RULESET_NAME || '_' || to_char(sysdate, 'yyyy-mm-dd') || '_' || to_char(sysdate, 'SSSSS') || '.txt';
188 end if;
189 select substr(value,1,instr(value||',', ',')-1) into log_path from v$parameter where name = 'utl_file_dir';
190 end INITIALIZE_RULESET;
191
192 procedure CLEAR_RULE is
193
194 begin
195 RULE_NAME := null;
196 RULE_OK := false;
197 MSG_TYPE := null;
198 MSG_DATA := null;
199 USAGE_ATTR_COUNT := 0;
200 USAGE_ATTR_TBL.DELETE;
201 end CLEAR_RULE;
202
203 procedure INITIALIZE_RULE (X_RULE_NAME IN VARCHAR2) is
204
205 begin
206 CLEAR_RULE;
207
208 RULE_NAME := X_RULE_NAME;
209 end INITIALIZE_RULE;
210
211 function IS_RULESET_OK return boolean is
212
213 begin
214 return RULESET_OK;
215 end IS_RULESET_OK;
216
217 function IS_RULE_OK return boolean is
218
219 begin
220 return RULE_OK;
221 end IS_RULE_OK;
222
223 function ATTR_GROUP_CONSISTENT(X_ATTR_GROUP_TYPE IN VARCHAR2, X_ATTR_GROUP_NAME IN VARCHAR2) return boolean is
224
225 begin
226 if (X_ATTR_GROUP_TYPE is null or X_ATTR_GROUP_NAME is null) then
227 return false;
228 end if;
229
230 if (ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE and ATTR_GROUP_NAME = X_ATTR_GROUP_NAME) then
231 return true;
232 end if;
233
234 return false;
235 end ATTR_GROUP_CONSISTENT;
236
237 function GET_RULESET_ID return NUMBER is
238
239 begin
240 return RULESET_ID;
241 end GET_RULESET_ID;
242
243 procedure SET_RULESET_ID (X_RULESET_ID IN NUMBER) is
244
245 begin
246 RULESET_ID := X_RULESET_ID;
247 end SET_RULESET_ID;
248
249 function GET_ATTR_GROUP_TYPE return VARCHAR2 is
250
251 begin
252 return ATTR_GROUP_TYPE;
253 end GET_ATTR_GROUP_TYPE;
254
255 function GET_ATTR_GROUP_NAME return VARCHAR2 is
256
257 begin
258 return ATTR_GROUP_NAME;
259 end GET_ATTR_GROUP_NAME;
260
261 procedure SET_ATTR_GROUP (X_ATTR_GROUP_TYPE IN VARCHAR2, X_ATTR_GROUP_NAME IN VARCHAR2) is
262
263 begin
264 ATTR_GROUP_TYPE := X_ATTR_GROUP_TYPE;
265 ATTR_GROUP_NAME := X_ATTR_GROUP_NAME;
266 end SET_ATTR_GROUP;
267
268 function GET_BUSINESS_ENTITIES return VARCHAR2 is
269
270 begin
271 return BUSINESS_ENTITIES;
272 end GET_BUSINESS_ENTITIES;
273
274 procedure SET_BUSINESS_ENTITIES (X_BUSINESS_ENTITIES IN VARCHAR2) is
275
276 begin
277 BUSINESS_ENTITIES := X_BUSINESS_ENTITIES;
278 end SET_BUSINESS_ENTITIES;
279
280 function GET_ITEM_CATALOG_GROUP return NUMBER is
281
282 begin
283 return ITEM_CATALOG_GROUP;
284 end GET_ITEM_CATALOG_GROUP;
285
286 procedure SET_ITEM_CATALOG_GROUP (X_ITEM_CATALOG_GROUP IN NUMBER) is
287
288 begin
289 ITEM_CATALOG_GROUP := X_ITEM_CATALOG_GROUP;
290 end SET_ITEM_CATALOG_GROUP;
291
292 procedure ADD_USAGE_ATTRIBUTE (X_USAGE_ATTRIBUTE IN USAGE_ATTR_REC_TYPE) is
293
294 begin
295 USAGE_ATTR_COUNT := USAGE_ATTR_COUNT + 1;
296 USAGE_ATTR_TBL(USAGE_ATTR_COUNT) := X_USAGE_ATTRIBUTE;
297 end ADD_USAGE_ATTRIBUTE;
298
299 function GET_USAGE_ATTRIBUTE_TBL return USAGE_ATTR_TBL_TYPE is
300
301 begin
302 return USAGE_ATTR_TBL;
303 end GET_USAGE_ATTRIBUTE_TBL;
304
305 procedure RULESET_SUCCESS is
306
307 begin
308 RULESET_OK := true;
309 end RULESET_SUCCESS;
310
311 procedure RULESET_FAIL is
312
313 begin
314 RULESET_OK := false;
315 end RULESET_FAIL;
316
317 procedure RULE_SUCCESS is
318
319 begin
320 RULE_OK := true;
321 end RULE_SUCCESS;
322
323 procedure RULE_FAIL is
324
325 begin
326 RULE_OK := false;
327 end RULE_FAIL;
328
329 procedure SET_MSG (X_MSG_TYPE IN VARCHAR2, X_MSG_DATA IN VARCHAR2) is
330
331 begin
332 MSG_TYPE := X_MSG_TYPE;
333 MSG_DATA := X_MSG_DATA;
334 end SET_MSG;
335
336 function GET_MSG_TYPE return VARCHAR2 is
337
338 begin
339 return MSG_TYPE;
340 end GET_MSG_TYPE;
341
342 function GET_MSG_DATA return VARCHAR2 is
343
344 begin
345 return MSG_DATA;
346 end GET_MSG_DATA;
347
348
349 procedure PROCESS_RULESET (
350 X_RULESET_ID out nocopy NUMBER,
351 X_RULESET_NAME in VARCHAR2,
352 X_RULESET_TYPE in VARCHAR2,
353 X_COMPOSITE in VARCHAR2,
354 X_ITEM_CATALOG_CATEGORY in NUMBER,
355 X_ATTR_GROUP_NAME in VARCHAR2,
356 X_ATTR_GROUP_TYPE in VARCHAR2,
357 X_BUSINESS_ENTITIES in VARCHAR2,
358 X_RULESET_DISPLAY_NAME in VARCHAR2,
359 X_DESCRIPTION in VARCHAR2,
360 X_CREATED_BY in NUMBER,
361 X_LAST_UPDATE_DATE in DATE,
362 X_LAST_UPDATED_BY in NUMBER,
363 X_LAST_UPDATE_LOGIN in NUMBER,
364 X_CUSTOM_MODE in VARCHAR2
365 ) is
366
367 CURSOR C_RULESET_EXISTS (C_RULESET_NAME VARCHAR2)
368 IS
369 SELECT RULESET_ID, LAST_UPDATE_DATE
370 FROM EGO_RULE_SETS_VL
371 WHERE RULESET_NAME = C_RULESET_NAME;
372
373 l_last_update_date DATE;
374 l_process BOOLEAN := true;
375 l_business_entity_tbl DATA_LEVEL_TBL_TYPE;
376 begin
377
378 savepoint process_rs;
379
380 -- process ruleset
381 OPEN C_RULESET_EXISTS(X_RULESET_NAME);
382 FETCH C_RULESET_EXISTS INTO X_RULESET_ID, l_last_update_date;
383 CLOSE C_RULESET_EXISTS;
384
385 if (X_RULESET_ID is not null and (NVL(X_CUSTOM_MODE, '*NULL*') = 'FORCE' or l_last_update_date < X_LAST_UPDATE_DATE)) then
386
387 update EGO_RULE_SETS_B set
388 RULESET_NAME = X_RULESET_NAME,
389 RULESET_TYPE = X_RULESET_TYPE,
390 COMPOSITE = X_COMPOSITE,
391 ITEM_CATALOG_CATEGORY = X_ITEM_CATALOG_CATEGORY,
392 ATTR_GROUP_NAME = X_ATTR_GROUP_NAME,
393 ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE,
394 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
395 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
396 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
397 where RULESET_ID = X_RULESET_ID;
398
399 update EGO_RULE_SETS_TL set
400 RULESET_DISPLAY_NAME = X_RULESET_DISPLAY_NAME,
401 DESCRIPTION = X_DESCRIPTION,
402 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
403 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
404 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
405 SOURCE_LANG = userenv('LANG')
406 where RULESET_ID = X_RULESET_ID
407 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
408
409 LOG_MSG('RULESET_SUCCESS', 'Ruleset: ' || X_RULESET_NAME || ' updated successfully!');
410 elsif (X_RULESET_ID is null) then
411
412 select EGO_RULE_SETS_S.nextval into X_RULESET_ID from dual;
413
414 insert into EGO_RULE_SETS_B (
415 RULESET_ID,
416 RULESET_NAME,
417 RULESET_TYPE,
418 COMPOSITE,
419 ITEM_CATALOG_CATEGORY,
420 ATTR_GROUP_NAME,
421 ATTR_GROUP_TYPE,
422 CREATION_DATE,
423 CREATED_BY,
424 LAST_UPDATE_DATE,
425 LAST_UPDATED_BY,
426 LAST_UPDATE_LOGIN
427 ) values (
428 X_RULESET_ID,
429 X_RULESET_NAME,
430 X_RULESET_TYPE,
431 X_COMPOSITE,
432 X_ITEM_CATALOG_CATEGORY,
433 X_ATTR_GROUP_NAME,
434 X_ATTR_GROUP_TYPE,
435 NVL(X_LAST_UPDATE_DATE,SYSDATE),
436 X_CREATED_BY,
437 NVL(X_LAST_UPDATE_DATE,SYSDATE),
438 X_LAST_UPDATED_BY,
439 X_LAST_UPDATE_LOGIN
440 );
441
442 insert into EGO_RULE_SETS_TL (
443 RULESET_ID,
444 RULESET_DISPLAY_NAME,
445 DESCRIPTION,
446 LAST_UPDATE_DATE,
447 LAST_UPDATED_BY,
448 CREATION_DATE,
449 CREATED_BY,
450 LAST_UPDATE_LOGIN,
451 LANGUAGE,
452 SOURCE_LANG
453 ) select
454 X_RULESET_ID,
455 X_RULESET_DISPLAY_NAME,
456 X_DESCRIPTION,
457 NVL(X_LAST_UPDATE_DATE,SYSDATE),
458 X_LAST_UPDATED_BY,
459 NVL(X_LAST_UPDATE_DATE,SYSDATE),
460 X_CREATED_BY,
461 X_LAST_UPDATE_LOGIN,
462 L.LANGUAGE_CODE,
463 userenv('LANG')
464 from FND_LANGUAGES L
465 where L.INSTALLED_FLAG in ('I', 'B')
466 and not exists
467 (select NULL
468 from EGO_RULE_SETS_TL T
469 where T.RULESET_ID = X_RULESET_ID
470 and T.LANGUAGE = L.LANGUAGE_CODE);
471
472 LOG_MSG('RULESET_SUCCESS', 'Ruleset: ' || X_RULESET_NAME || ' inserted successfully!');
473 else
474 l_process := false;
475 LOG_MSG('RULESET_SUCCESS', 'Ruleset: ' || X_RULESET_NAME || ' already existed and no need to update!');
476 end if;
477
478 -- process business entities only when ruleset be inserted/updated
479 if (l_process = true) then
480
481 delete from EGO_INCLUDED_BUSINESSENTITIES where RULESET_ID = X_RULESET_ID;
482
483 l_business_entity_tbl := BUSINESS_ENTITIES_TO_ARRAY(X_BUSINESS_ENTITIES);
484
485 FOR i in 1.. l_business_entity_tbl.COUNT LOOP
486 INSERT INTO EGO_INCLUDED_BUSINESSENTITIES
487 (
488 BUSINESSENTITY_ROW_ID
489 ,RULESET_ID
490 ,BUSINESS_ENTITY
491 ,LAST_UPDATE_DATE
492 ,LAST_UPDATED_BY
493 ,CREATION_DATE
494 ,CREATED_BY
495 ,LAST_UPDATE_LOGIN
496 )
497 VALUES
498 (
499 EGO_INCLUDED_ENTITIES_S.NEXTVAL
500 ,X_RULESET_ID
501 ,l_business_entity_tbl(i)
502 ,SYSDATE
503 ,X_LAST_UPDATED_BY
504 ,SYSDATE
505 ,X_CREATED_BY
506 ,X_LAST_UPDATE_LOGIN
507 );
508 END LOOP;
509 end if;
510
511 exception
512
513 when others then
514
515 rollback to process_rs;
516
517 IF C_RULESET_EXISTS%ISOPEN THEN
518 CLOSE C_RULESET_EXISTS;
519 END IF;
520 end PROCESS_RULESET;
521
522 --------------------------------------------------- R12.2 Rule Enhancement Data Migration end ----------------------------------------------------
523
524
525
526 procedure INSERT_ROW (
527 X_ROWID in out nocopy VARCHAR2,
528 X_RULESET_ID in NUMBER,
529 X_ATTR_GROUP_TYPE in VARCHAR2,
530 X_RULESET_NAME in VARCHAR2,
531 X_RULESET_TYPE in VARCHAR2,
532 X_COMPOSITE in VARCHAR2,
533 X_ITEM_CATALOG_CATEGORY in NUMBER,
534 X_ATTR_GROUP_NAME in VARCHAR2,
535 X_RULESET_DISPLAY_NAME in VARCHAR2,
536 X_DESCRIPTION in VARCHAR2,
537 X_CREATION_DATE in DATE,
538 X_CREATED_BY in NUMBER,
539 X_LAST_UPDATE_DATE in DATE,
540 X_LAST_UPDATED_BY in NUMBER,
541 X_LAST_UPDATE_LOGIN in NUMBER
542 ) is
543 cursor C is select ROWID from EGO_RULE_SETS_B
544 where RULESET_ID = X_RULESET_ID
545 ;
546 begin
547 insert into EGO_RULE_SETS_B (
548 ATTR_GROUP_TYPE,
549 RULESET_ID,
550 RULESET_NAME,
551 RULESET_TYPE,
552 COMPOSITE,
553 ITEM_CATALOG_CATEGORY,
554 ATTR_GROUP_NAME,
555 CREATION_DATE,
556 CREATED_BY,
557 LAST_UPDATE_DATE,
558 LAST_UPDATED_BY,
559 LAST_UPDATE_LOGIN
560 ) values (
561 X_ATTR_GROUP_TYPE,
562 X_RULESET_ID,
563 X_RULESET_NAME,
564 X_RULESET_TYPE,
565 X_COMPOSITE,
566 X_ITEM_CATALOG_CATEGORY,
567 X_ATTR_GROUP_NAME,
568 Nvl(X_CREATION_DATE,SYSDATE),
569 X_CREATED_BY,
570 Nvl(X_LAST_UPDATE_DATE, SYSDATE),
571 X_LAST_UPDATED_BY,
572 X_LAST_UPDATE_LOGIN
573 );
574
575 insert into EGO_RULE_SETS_TL (
576 RULESET_ID,
577 RULESET_DISPLAY_NAME,
578 DESCRIPTION,
579 LAST_UPDATE_DATE,
580 LAST_UPDATED_BY,
581 CREATION_DATE,
582 CREATED_BY,
583 LAST_UPDATE_LOGIN,
584 LANGUAGE,
585 SOURCE_LANG
586 ) select
587 X_RULESET_ID,
588 X_RULESET_DISPLAY_NAME,
589 X_DESCRIPTION,
590 Nvl(X_LAST_UPDATE_DATE,SYSDATE),
591 X_LAST_UPDATED_BY,
592 Nvl(X_CREATION_DATE,SYSDATE),
593 X_CREATED_BY,
594 X_LAST_UPDATE_LOGIN,
595 L.LANGUAGE_CODE,
596 userenv('LANG')
597 from FND_LANGUAGES L
598 where L.INSTALLED_FLAG in ('I', 'B')
599 and not exists
600 (select NULL
601 from EGO_RULE_SETS_TL T
602 where T.RULESET_ID = X_RULESET_ID
603 and T.LANGUAGE = L.LANGUAGE_CODE);
604
605 open c;
606 fetch c into X_ROWID;
607 if (c%notfound) then
608 close c;
609 raise no_data_found;
610 end if;
611 close c;
612
613 end INSERT_ROW;
614
615 procedure LOCK_ROW (
616 X_RULESET_ID in NUMBER,
617 X_ATTR_GROUP_TYPE in VARCHAR2,
618 X_RULESET_NAME in VARCHAR2,
619 X_RULESET_TYPE in VARCHAR2,
620 X_COMPOSITE in VARCHAR2,
621 X_ITEM_CATALOG_CATEGORY in NUMBER,
622 X_ATTR_GROUP_NAME in VARCHAR2,
623 X_RULESET_DISPLAY_NAME in VARCHAR2,
624 X_DESCRIPTION in VARCHAR2
625 ) is
626 cursor c is select
627 ATTR_GROUP_TYPE,
628 RULESET_NAME,
629 RULESET_TYPE,
630 COMPOSITE,
631 ITEM_CATALOG_CATEGORY,
632 ATTR_GROUP_NAME
633 from EGO_RULE_SETS_B
634 where RULESET_ID = X_RULESET_ID
635 for update of RULESET_ID nowait;
636 recinfo c%rowtype;
637
638 cursor c1 is select
639 RULESET_DISPLAY_NAME,
640 DESCRIPTION,
641 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
642 from EGO_RULE_SETS_TL
643 where RULESET_ID = X_RULESET_ID
644 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
645 for update of RULESET_ID nowait;
646 begin
647 open c;
648 fetch c into recinfo;
649 if (c%notfound) then
650 close c;
651 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
652 app_exception.raise_exception;
653 end if;
654 close c;
655 if ( ((recinfo.ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE)
656 OR ((recinfo.ATTR_GROUP_TYPE is null) AND (X_ATTR_GROUP_TYPE is null)))
657 AND (recinfo.RULESET_NAME = X_RULESET_NAME)
658 AND (recinfo.RULESET_TYPE = X_RULESET_TYPE)
659 AND (recinfo.COMPOSITE = X_COMPOSITE)
660 AND ((recinfo.ITEM_CATALOG_CATEGORY = X_ITEM_CATALOG_CATEGORY)
661 OR ((recinfo.ITEM_CATALOG_CATEGORY is null) AND (X_ITEM_CATALOG_CATEGORY is null)))
662 AND ((recinfo.ATTR_GROUP_NAME = X_ATTR_GROUP_NAME)
663 OR ((recinfo.ATTR_GROUP_NAME is null) AND (X_ATTR_GROUP_NAME is null)))
664 ) then
665 null;
666 else
667 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
668 app_exception.raise_exception;
669 end if;
670
671 for tlinfo in c1 loop
672 if (tlinfo.BASELANG = 'Y') then
673 if ( ((tlinfo.RULESET_DISPLAY_NAME = X_RULESET_DISPLAY_NAME)
674 OR ((tlinfo.RULESET_DISPLAY_NAME is null) AND (X_RULESET_DISPLAY_NAME is null)))
675 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
676 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
677 ) then
678 null;
679 else
680 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
681 app_exception.raise_exception;
682 end if;
683 end if;
684 end loop;
685 return;
686 end LOCK_ROW;
687
688 procedure UPDATE_ROW (
689 X_RULESET_ID in NUMBER,
690 X_ATTR_GROUP_TYPE in VARCHAR2,
691 X_RULESET_NAME in VARCHAR2,
692 X_RULESET_TYPE in VARCHAR2,
693 X_COMPOSITE in VARCHAR2,
694 X_ITEM_CATALOG_CATEGORY in NUMBER,
695 X_ATTR_GROUP_NAME in VARCHAR2,
696 X_RULESET_DISPLAY_NAME in VARCHAR2,
697 X_DESCRIPTION in VARCHAR2,
698 X_LAST_UPDATE_DATE in DATE,
699 X_LAST_UPDATED_BY in NUMBER,
700 X_LAST_UPDATE_LOGIN in NUMBER
701 ) is
702 begin
703 update EGO_RULE_SETS_B set
704 ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE,
705 RULESET_NAME = X_RULESET_NAME,
706 RULESET_TYPE = X_RULESET_TYPE,
707 COMPOSITE = X_COMPOSITE,
708 ITEM_CATALOG_CATEGORY = X_ITEM_CATALOG_CATEGORY,
709 ATTR_GROUP_NAME = X_ATTR_GROUP_NAME,
710 LAST_UPDATE_DATE = Nvl(X_LAST_UPDATE_DATE,SYSDATE),
711 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
712 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
713 where RULESET_ID = X_RULESET_ID;
714
715 if (sql%notfound) then
716 raise no_data_found;
717 end if;
718
719 update EGO_RULE_SETS_TL set
720 RULESET_DISPLAY_NAME = X_RULESET_DISPLAY_NAME,
721 DESCRIPTION = X_DESCRIPTION,
722 LAST_UPDATE_DATE = Nvl(X_LAST_UPDATE_DATE,SYSDATE),
723 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
724 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
725 SOURCE_LANG = userenv('LANG')
726 where RULESET_ID = X_RULESET_ID
727 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
728
729 if (sql%notfound) then
730 raise no_data_found;
731 end if;
732 end UPDATE_ROW;
733
734 procedure DELETE_ROW (
735 X_RULESET_ID in NUMBER
736 ) is
737 begin
738 delete from EGO_RULE_SETS_TL
739 where RULESET_ID = X_RULESET_ID;
740
741 if (sql%notfound) then
742 raise no_data_found;
743 end if;
744
745 delete from EGO_RULE_SETS_B
746 where RULESET_ID = X_RULESET_ID;
747
748 if (sql%notfound) then
749 raise no_data_found;
750 end if;
751 end DELETE_ROW;
752
753 procedure ADD_LANGUAGE
754 is
755 begin
756 delete from EGO_RULE_SETS_TL T
757 where not exists
758 (select NULL
759 from EGO_RULE_SETS_B B
760 where B.RULESET_ID = T.RULESET_ID
761 );
762
763 update EGO_RULE_SETS_TL T set (
764 RULESET_DISPLAY_NAME,
765 DESCRIPTION
766 ) = (select
767 B.RULESET_DISPLAY_NAME,
768 B.DESCRIPTION
769 from EGO_RULE_SETS_TL B
770 where B.RULESET_ID = T.RULESET_ID
771 and B.LANGUAGE = T.SOURCE_LANG)
772 where (
773 T.RULESET_ID,
774 T.LANGUAGE
775 ) in (select
776 SUBT.RULESET_ID,
777 SUBT.LANGUAGE
778 from EGO_RULE_SETS_TL SUBB, EGO_RULE_SETS_TL SUBT
779 where SUBB.RULESET_ID = SUBT.RULESET_ID
780 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
781 and (SUBB.RULESET_DISPLAY_NAME <> SUBT.RULESET_DISPLAY_NAME
782 or (SUBB.RULESET_DISPLAY_NAME is null and SUBT.RULESET_DISPLAY_NAME is not null)
783 or (SUBB.RULESET_DISPLAY_NAME is not null and SUBT.RULESET_DISPLAY_NAME is null)
784 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
785 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
786 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
787 ));
788
789 insert into EGO_RULE_SETS_TL (
790 RULESET_ID,
791 RULESET_DISPLAY_NAME,
792 DESCRIPTION,
793 LAST_UPDATE_DATE,
794 LAST_UPDATED_BY,
795 CREATION_DATE,
796 CREATED_BY,
797 LAST_UPDATE_LOGIN,
798 LANGUAGE,
799 SOURCE_LANG
800 ) select /*+ ORDERED */
801 B.RULESET_ID,
802 B.RULESET_DISPLAY_NAME,
803 B.DESCRIPTION,
804 B.LAST_UPDATE_DATE,
805 B.LAST_UPDATED_BY,
806 B.CREATION_DATE,
807 B.CREATED_BY,
808 B.LAST_UPDATE_LOGIN,
809 L.LANGUAGE_CODE,
810 B.SOURCE_LANG
811 from EGO_RULE_SETS_TL B, FND_LANGUAGES L
812 where L.INSTALLED_FLAG in ('I', 'B')
813 and B.LANGUAGE = userenv('LANG')
814 and not exists
815 (select NULL
816 from EGO_RULE_SETS_TL T
817 where T.RULESET_ID = B.RULESET_ID
818 and T.LANGUAGE = L.LANGUAGE_CODE);
819 end ADD_LANGUAGE;
820
821 procedure TRANSLATE_ROW (
822 X_RULESET_ID in NUMBER,
823 X_RULESET_DISPLAY_NAME in VARCHAR2,
824 X_DESCRIPTION in VARCHAR2,
825 X_OWNER in VARCHAR2
826 ) is
827 begin
828 update EGO_RULE_SETS_TL set
829 RULESET_DISPLAY_NAME = X_RULESET_DISPLAY_NAME,
830 DESCRIPTION = X_DESCRIPTION,
831 LAST_UPDATE_DATE = sysdate,
832 LAST_UPDATED_BY = decode(x_owner, 'ORACLE', 1, 0),
833 LAST_UPDATE_LOGIN = 0,
834 SOURCE_LANG = userenv('LANG')
835 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
836 and RULESET_ID = X_RULESET_ID;
837 end TRANSLATE_ROW;
838
839 procedure LOAD_ROW (
840 X_ROWID in out nocopy VARCHAR2,
841 X_RULESET_ID in NUMBER,
842 X_RULESET_NAME in VARCHAR2,
843 X_ATTR_GROUP_TYPE in VARCHAR2,
844 X_RULESET_TYPE in VARCHAR2,
845 X_COMPOSITE in VARCHAR2,
846 X_ITEM_CATALOG_CATEGORY in NUMBER,
847 X_ATTR_GROUP_NAME in VARCHAR2,
848 X_RULESET_DISPLAY_NAME in VARCHAR2,
849 X_DESCRIPTION in VARCHAR2,
850 X_CREATED_BY in NUMBER,
851 X_LAST_UPDATE_DATE in DATE,
852 X_LAST_UPDATED_BY in NUMBER,
853 X_LAST_UPDATE_LOGIN in NUMBER
854 ) is
855 begin
856 declare
857 l_ruleset_id number := 0;
858
859 begin
860 select RULESET_ID into l_ruleset_id
861 from EGO_RULE_SETS_B
862 where RULESET_NAME = X_RULESET_NAME;
863
864 EGO_RULE_SETS_PKG.UPDATE_ROW(
865 X_RULESET_ID => l_ruleset_id,
866 X_RULESET_NAME => X_RULESET_NAME,
867 X_ATTR_GROUP_TYPE => X_ATTR_GROUP_TYPE,
868 X_RULESET_TYPE => X_RULESET_TYPE,
869 X_COMPOSITE => X_COMPOSITE,
870 X_ITEM_CATALOG_CATEGORY => X_ITEM_CATALOG_CATEGORY,
871 X_ATTR_GROUP_NAME => X_ATTR_GROUP_NAME,
872 X_RULESET_DISPLAY_NAME => X_RULESET_DISPLAY_NAME,
873 X_DESCRIPTION => X_DESCRIPTION,
874 X_LAST_UPDATE_DATE => SYSDATE,
875 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
876 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
877 );
878
879 exception
880 when NO_DATA_FOUND then
881 select EGO_RULE_SETS_S.nextval into l_ruleset_id from dual;
882
883 EGO_RULE_SETS_PKG.INSERT_ROW(
884 X_ROWID => X_ROWID,
885 X_RULESET_ID => l_ruleset_id,
886 X_ATTR_GROUP_TYPE => X_ATTR_GROUP_TYPE,
887 X_RULESET_NAME => X_RULESET_NAME,
888 X_RULESET_TYPE => X_RULESET_TYPE,
889 X_COMPOSITE => X_COMPOSITE,
890 X_ITEM_CATALOG_CATEGORY => X_ITEM_CATALOG_CATEGORY,
891 X_ATTR_GROUP_NAME => X_ATTR_GROUP_NAME,
892 X_RULESET_DISPLAY_NAME => X_RULESET_DISPLAY_NAME,
893 X_DESCRIPTION => X_DESCRIPTION,
894 X_CREATION_DATE => sysdate,
895 X_CREATED_BY => X_CREATED_BY,
896 X_LAST_UPDATE_DATE => sysdate,
897 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
898 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
899 );
900 end;
901 END LOAD_ROW;
902
903 --UT bug 16611182: add default value for the user defined attribute group
904 PROCEDURE INSERT_UDA_DEFAULT_VALUES(p_batch_id IN NUMBER) IS
905 l_ext_id number;
906 l_top_ruleset_id NUMBER;
907 l_selected_business_entity EGO_ONDEMAND_RULES_BATCHS_B.business_entity%type;
908 l_datalevel_id NUMBER;
909 l_ag_id ego_attr_groups_v.attr_group_id%TYPE;
910 l_multi_row_code ego_attr_groups_v.multi_row_code%type;
911
912 l_revision_id NUMBER DEFAULT NULL;
913 l_supplier_id NUMBER DEFAULT NULL;
914 l_supplier_site_id number default null;
915
916 l_batch_item_rec EGO_ONDEMAND_RULES_ITEMS_B%rowtype;
917 CURSOR l_cursor_batch_items(p_batch NUMBER) IS
918 SELECT * FROM EGO_ONDEMAND_RULES_ITEMS_B WHERE batch_id = p_batch AND process_status = 1;
919
920 CURSOR c_default_ag(p_data_level VARCHAR2, p_datalevel_id NUMBER, p_top_ruleset_id NUMBER, p_org_id NUMBER, p_inventory_item_id NUMBER, p_icc_id NUMBER) IS
921 SELECT DISTINCT assoc.attr_group_id FROM EGO_OBJ_ATTR_GRP_ASSOCS_V assoc WHERE assoc.application_id = 431 AND assoc.classification_code = to_char(p_icc_id) AND assoc.data_level_int_name = p_data_level AND assoc.attr_group_id IN
922 (SELECT attr_group_id FROM ego_attr_groups_v WHERE attr_group_type = 'EGO_ITEMMGMT_GROUP' AND application_id = 431 AND attr_group_name IN (SELECT DISTINCT user_rules.attr_group_name FROM ego_user_rules_b user_rules
923 LEFT OUTER JOIN ego_rule_sets_b rule_set ON user_rules.ruleset_id = rule_set.ruleset_id
924 WHERE user_rules.severity IS NULL AND user_rules.attr_group_type = 'EGO_ITEMMGMT_GROUP' AND rule_set.composite = 'NO' AND EXISTS (SELECT '1' FROM ego_included_businessentities WHERE ruleset_id = user_rules.ruleset_id AND business_entity = p_data_level)
925 AND (rule_set.ruleset_id IN (SELECT included_ruleset FROM EGO_INCLUDED_RULESETS START WITH ruleset_id = p_top_ruleset_id CONNECT BY PRIOR included_ruleset = ruleset_id) OR rule_set.ruleset_id = p_top_ruleset_id)))
926 and not exists (select 'x' from ego_mtl_sy_items_ext_b where organization_id = p_org_id and inventory_item_id = p_inventory_item_id and item_catalog_group_id = p_icc_id and attr_group_id = assoc.attr_group_id and data_level_id = p_datalevel_id);
927 BEGIN
928 SELECT top_ruleset_id INTO l_top_ruleset_id FROM EGO_ONDEMAND_RULES_BATCHS_B WHERE batch_id = p_batch_id;
929 SELECT business_entity INTO l_selected_business_entity FROM EGO_ONDEMAND_RULES_BATCHS_B WHERE batch_id = p_batch_id;
930 SELECT decode(l_selected_business_entity, 'ITEM_LEVEL', 43101, 'ITEM_ORG', 43102, 'ITEM_SUP', 43103, 'ITEM_SUP_SITE', 43104, 'ITEM_SUP_SITE_ORG', 43105, 'ITEM_REVISION_LEVEL', 43106, -1) INTO l_datalevel_id FROM dual;
931 IF l_datalevel_id > 0 THEN
932 OPEN l_cursor_batch_items(p_batch_id);
933 loop
934 fetch l_cursor_batch_items INTO l_batch_item_rec;
935 exit WHEN l_cursor_batch_items%NOTFOUND;
936
937 IF l_datalevel_id = 43106 THEN
938 l_revision_id := l_batch_item_rec.revision_id;
939 END IF;
940 IF l_datalevel_id = 43103 OR l_datalevel_id = 43104 OR l_datalevel_id = 43105 THEN
941 l_supplier_id := l_batch_item_rec.supplier_id;
942 end if;
943 IF l_datalevel_id = 43104 OR l_datalevel_id = 43105 THEN
944 l_supplier_site_id := l_batch_item_rec.supplier_site_id;
945 END IF;
946 OPEN c_default_ag(l_selected_business_entity, l_datalevel_id, l_top_ruleset_id,l_batch_item_rec.organization_id, l_batch_item_rec.inventory_item_id, l_batch_item_rec.item_catalog_group_id);
947 loop
948 fetch c_default_ag INTO l_ag_id;
949 exit WHEN c_default_ag%NOTFOUND;
950
951 SELECT multi_row_code INTO l_multi_row_code FROM ego_attr_groups_v WHERE attr_group_id = l_ag_id;
952 IF l_multi_row_code <> 'Y' THEN
953 SELECT EGO_EXTFWK_S.NEXTVAL INTO l_ext_id FROM dual;
954 --insert ext data
955 INSERT INTO ego_mtl_sy_items_ext_b(extension_id, organization_id, inventory_item_id, item_catalog_group_id, attr_group_id, data_level_id, created_by, creation_date, last_updated_by, last_update_date, revision_id, pk1_value, pk2_value)
956 VALUES (l_ext_id, l_batch_item_rec.organization_id, l_batch_item_rec.inventory_item_id, l_batch_item_rec.item_catalog_group_id, l_ag_id, l_datalevel_id, 1, SYSDATE, 1, SYSDATE, l_revision_id, l_supplier_id, l_supplier_site_id);
957 --inert language data
958 INSERT INTO ego_mtl_sy_items_ext_tl(extension_id, organization_id, inventory_item_id, item_catalog_group_id, attr_group_id, data_level_id, source_lang, language, created_by, creation_date,
959 last_updated_by, last_update_date, revision_id, pk1_value, pk2_value)
960 SELECT EXT.EXTENSION_ID, EXT.ORGANIZATION_ID, EXT.INVENTORY_ITEM_ID, EXT.ITEM_CATALOG_GROUP_ID, EXT.ATTR_GROUP_ID, ext.data_level_id, USERENV('LANG'), L.LANGUAGE_CODE, ext.created_by,
961 ext.creation_date, ext.last_updated_by, ext.last_update_date, ext.revision_id, ext.pk1_value, ext.pk2_value
962 FROM ego_mtl_sy_items_ext_b ext, FND_LANGUAGES L
963 WHERE ext.EXTENSION_ID = l_ext_id AND l.INSTALLED_FLAG IN ('I', 'B');
964 commit;
965 END IF;
966 END loop;
967 close c_default_ag;
968 END loop;
969 CLOSE l_cursor_batch_items;
970 END IF;
971 END INSERT_UDA_DEFAULT_VALUES;
972
973 end EGO_RULE_SETS_PKG;