[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_RULES_PKG
Source
1 package body ams_list_rules_pkg as
2 /* $Header: amsllrub.pls 120.1 2005/10/19 03:34:41 batoleti noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_LIST_RULE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_LIST_RULE_NAME in VARCHAR2,
8 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
9 X_ACTIVE_FROM_DATE in DATE,
10 X_ACTIVE_TO_DATE in DATE,
11 X_LIST_RULE_TYPE in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER,
18 x_LIST_SOURCE_TYPE in VARCHAR2,
19 x_ENABLED_FLAG in VARCHAR2,
20 x_SEEDED_FLAG in VARCHAR2
21 ) is
22 cursor C is select ROWID from AMS_LIST_RULES_ALL
23 where LIST_RULE_ID = X_LIST_RULE_ID
24 ;
25 begin
26 insert into AMS_LIST_RULES_ALL (
27 LIST_RULE_ID,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_LOGIN,
33 OBJECT_VERSION_NUMBER,
34 LIST_RULE_NAME,
35 WEIGHTAGE_FOR_DEDUPE,
36 ACTIVE_FROM_DATE,
37 ACTIVE_TO_DATE,
38 DESCRIPTION,
39 LIST_RULE_TYPE,
40 LIST_SOURCE_TYPE,
41 ENABLED_FLAG,
42 SEEDED_FLAG
43 ) values (
44 X_LIST_RULE_ID,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_LOGIN,
50 X_OBJECT_VERSION_NUMBER,
51 X_LIST_RULE_NAME,
52 X_WEIGHTAGE_FOR_DEDUPE,
53 X_ACTIVE_FROM_DATE,
54 X_ACTIVE_TO_DATE,
55 X_DESCRIPTION,
56 X_LIST_RULE_TYPE,
57 x_LIST_SOURCE_TYPE,
58 x_ENABLED_FLAG,
59 x_SEEDED_FLAG
60 );
61
62 insert into AMS_LIST_RULES_ALL_TL (
63 LIST_RULE_ID,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_LOGIN,
69 LIST_RULE_NAME,
70 DESCRIPTION,
71 LANGUAGE,
72 SOURCE_LANG
73 ) select
74 X_LIST_RULE_ID,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATED_BY,
77 X_CREATION_DATE,
78 X_CREATED_BY,
79 X_LAST_UPDATE_LOGIN,
80 X_LIST_RULE_NAME,
81 X_DESCRIPTION,
82 L.LANGUAGE_CODE,
83 userenv('LANG')
84 from FND_LANGUAGES L
85 where L.INSTALLED_FLAG in ('I', 'B')
86 and not exists
87 (select NULL
88 from AMS_LIST_RULES_ALL_TL T
89 where T.LIST_RULE_ID = X_LIST_RULE_ID
90 and T.LANGUAGE = L.LANGUAGE_CODE);
91
92 open c;
93 fetch c into X_ROWID;
94 if (c%notfound) then
95 close c;
96 raise no_data_found;
97 end if;
98 close c;
99
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_LIST_RULE_ID in NUMBER,
104 X_OBJECT_VERSION_NUMBER in NUMBER,
105 X_LIST_RULE_NAME in VARCHAR2,
106 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
107 X_ACTIVE_FROM_DATE in DATE,
108 X_ACTIVE_TO_DATE in DATE,
109 X_LIST_RULE_TYPE in VARCHAR2,
110 X_DESCRIPTION in VARCHAR2
111 ) is
112 cursor c1 is select
113 OBJECT_VERSION_NUMBER,
114 LIST_RULE_NAME,
115 WEIGHTAGE_FOR_DEDUPE,
116 ACTIVE_FROM_DATE,
117 ACTIVE_TO_DATE,
118 LIST_RULE_TYPE,
119 DESCRIPTION
120 from AMS_LIST_RULES_ALL
121 where LIST_RULE_ID = X_LIST_RULE_ID
122 for update of LIST_RULE_ID nowait;
123 begin
124 for tlinfo in c1 loop
125 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
126 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
127 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
128 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
129 AND (tlinfo.LIST_RULE_NAME = X_LIST_RULE_NAME)
130 AND (tlinfo.WEIGHTAGE_FOR_DEDUPE = X_WEIGHTAGE_FOR_DEDUPE)
131 AND (tlinfo.ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE)
132 AND ((tlinfo.ACTIVE_TO_DATE = X_ACTIVE_TO_DATE)
133 OR ((tlinfo.ACTIVE_TO_DATE is null) AND (X_ACTIVE_TO_DATE is null)))
134 AND ((tlinfo.LIST_RULE_TYPE = X_LIST_RULE_TYPE)
135 OR ((tlinfo.LIST_RULE_TYPE is null) AND (X_LIST_RULE_TYPE is null)))
136 ) then
137 null;
138 else
139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
140 app_exception.raise_exception;
141 end if;
142 end loop;
143 return;
144 end LOCK_ROW;
145
146 procedure UPDATE_ROW (
147 X_LIST_RULE_ID in NUMBER,
148 X_OBJECT_VERSION_NUMBER in NUMBER,
149 X_LIST_RULE_NAME in VARCHAR2,
150 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
151 X_ACTIVE_FROM_DATE in DATE,
152 X_ACTIVE_TO_DATE in DATE,
153 X_LIST_RULE_TYPE in VARCHAR2,
154 X_DESCRIPTION in VARCHAR2,
155 X_LAST_UPDATE_DATE in DATE,
156 X_LAST_UPDATED_BY in NUMBER,
157 X_LAST_UPDATE_LOGIN in NUMBER,
158 x_LIST_SOURCE_TYPE in VARCHAR2,
159 x_ENABLED_FLAG in VARCHAR2,
160 x_SEEDED_FLAG in VARCHAR2
161 ) is
162 begin
163 update AMS_LIST_RULES_ALL set
164 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
165 LIST_RULE_NAME = X_LIST_RULE_NAME,
166 WEIGHTAGE_FOR_DEDUPE = X_WEIGHTAGE_FOR_DEDUPE,
167 ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE,
168 ACTIVE_TO_DATE = X_ACTIVE_TO_DATE,
169 LIST_RULE_TYPE = X_LIST_RULE_TYPE,
170 DESCRIPTION = X_DESCRIPTION,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
174 LIST_SOURCE_TYPE = x_LIST_SOURCE_TYPE,
175 ENABLED_FLAG = x_ENABLED_FLAG,
176 SEEDED_FLAG = x_SEEDED_FLAG
177 where LIST_RULE_ID = X_LIST_RULE_ID;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 update AMS_LIST_RULES_ALL_TL set
183 LIST_RULE_NAME = X_LIST_RULE_NAME,
184 DESCRIPTION = X_DESCRIPTION,
185 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
186 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
187 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
188 SOURCE_LANG = userenv('LANG')
189 where LIST_RULE_ID = X_LIST_RULE_ID
190 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195 end UPDATE_ROW;
196
197 procedure DELETE_ROW (
198 X_LIST_RULE_ID in NUMBER
199 ) is
200 begin
201 delete from AMS_LIST_RULES_ALL_TL
202 where LIST_RULE_ID = X_LIST_RULE_ID;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207
208 delete from AMS_LIST_RULES_ALL
209 where LIST_RULE_ID = X_LIST_RULE_ID;
210
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 end DELETE_ROW;
216
217 PROCEDURE load_row (
218 X_LIST_RULE_ID in NUMBER,
219 X_LIST_RULE_NAME in VARCHAR2,
220 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
221 X_ACTIVE_FROM_DATE in VARCHAR2,
222 X_ACTIVE_TO_DATE in VARCHAR2,
223 X_LIST_RULE_TYPE in VARCHAR2,
224 X_DESCRIPTION in VARCHAR2,
225 x_owner IN VARCHAR2,
226 x_LIST_SOURCE_TYPE in VARCHAR2,
227 x_ENABLED_FLAG in VARCHAR2,
228 x_SEEDED_FLAG in VARCHAR2,
229 x_custom_mode IN VARCHAR2
230
231 )
232 IS
233 l_user_id number := 0;
234 l_obj_verno number;
235 l_dummy_char varchar2(1);
236 l_row_id varchar2(100);
237 l_list_rule_id number;
238 l_last_updated_by number;
239
240 CURSOR c_obj_verno IS
241 SELECT object_version_number, last_updated_by
242 FROM ams_list_rules_all
243 WHERE list_rule_id = x_list_rule_id;
244
245 CURSOR c_chk_exists is
246 SELECT 'x'
247 FROM ams_list_rules_all
248 WHERE list_rule_id = x_list_rule_id;
249
250 CURSOR c_get_id is
251 SELECT ams_list_rules_all_s.NEXTVAL
252 FROM DUAL;
253 BEGIN
254 if X_OWNER = 'SEED' then
255 l_user_id := 1;
256 elsif X_OWNER = 'ORACLE' then
257 l_user_id := 2;
258 elsif X_OWNER = 'SYSADMIN' THEN
259 l_user_id := 0;
260 end if;
261
262 OPEN c_chk_exists;
263 FETCH c_chk_exists INTO l_dummy_char;
264 IF c_chk_exists%notfound THEN
265 CLOSE c_chk_exists;
266
267 IF x_list_rule_id IS NULL THEN
268 OPEN c_get_id;
269 FETCH c_get_id INTO l_list_rule_id;
270 CLOSE c_get_id;
271 ELSE
272 l_list_rule_id := x_list_rule_id;
273 END IF;
274 l_obj_verno := 1;
275
276 ams_list_rules_pkg.Insert_Row (
277 X_ROWID => l_row_id,
278 X_LIST_RULE_ID => l_list_rule_id,
279 X_OBJECT_VERSION_NUMBER => l_obj_verno,
280 X_LIST_RULE_NAME => x_list_rule_name,
281 X_WEIGHTAGE_FOR_DEDUPE => x_weightage_for_dedupe,
282 X_ACTIVE_FROM_DATE => SYSDATE,
283 X_ACTIVE_TO_DATE => SYSDATE,
284 X_LIST_RULE_TYPE => x_list_rule_type,
285 X_DESCRIPTION => x_description,
286 X_CREATION_DATE => SYSDATE,
287 X_CREATED_BY => l_user_id,
288 X_LAST_UPDATE_DATE => SYSDATE,
289 X_LAST_UPDATED_BY => l_user_id,
290 X_LAST_UPDATE_LOGIN => 0,
291 x_LIST_SOURCE_TYPE => x_LIST_SOURCE_TYPE,
292 x_ENABLED_FLAG => x_ENABLED_FLAG,
293 x_SEEDED_FLAG => x_SEEDED_FLAG
294 );
295 ELSE
296 CLOSE c_chk_exists;
297 OPEN c_obj_verno;
298 FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;
299 CLOSE c_obj_verno;
300
301 if (l_last_updated_by in (1,2,0) OR
302 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
303
304 ams_list_rules_pkg.Update_Row (
305 X_LIST_RULE_ID => x_list_rule_id,
306 X_OBJECT_VERSION_NUMBER => l_obj_verno,
307 X_LIST_RULE_NAME => x_list_rule_name,
308 X_WEIGHTAGE_FOR_DEDUPE => x_weightage_for_dedupe,
309 X_ACTIVE_FROM_DATE => TO_DATE (x_active_from_date, 'YYYY/MM/DD'),
310 X_ACTIVE_TO_DATE => TO_DATE (x_active_to_date, 'YYYY/MM/DD'),
311 X_LIST_RULE_TYPE => x_list_rule_type,
312 X_DESCRIPTION => x_description,
313 X_LAST_UPDATE_DATE => SYSDATE,
314 X_LAST_UPDATED_BY => l_user_id,
315 X_LAST_UPDATE_LOGIN => 0,
316 x_LIST_SOURCE_TYPE => x_LIST_SOURCE_TYPE,
317 x_ENABLED_FLAG => x_ENABLED_FLAG,
318 x_SEEDED_FLAG => x_SEEDED_FLAG
319 );
320
321 end if;
322 END IF;
323 END load_row;
324
325 PROCEDURE TRANSLATE_ROW (
326 X_LIST_RULE_ID IN NUMBER,
327 X_LIST_RULE_NAME IN VARCHAR2,
328 X_DESCRIPTION IN VARCHAR2,
329 X_OWNER IN VARCHAR2,
330 x_custom_mode IN VARCHAR2
331
332 ) IS
333
334 cursor c_last_updated_by is
335 select last_updated_by
336 FROM AMS_LIST_RULES_ALL_TL
337 where LIST_RULE_ID = X_LIST_RULE_ID
338 and USERENV('LANG') = LANGUAGE;
339
340 l_last_updated_by number;
341
342 BEGIN
343
344 open c_last_updated_by;
345 fetch c_last_updated_by into l_last_updated_by;
346 close c_last_updated_by;
347
348 if (l_last_updated_by in (1,2,0) OR
349 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
350
351
352 -- only UPDATE rows that have not been altered by user
353 UPDATE AMS_LIST_RULES_ALL_TL
354 SET
355 LIST_RULE_NAME = NVL(X_LIST_RULE_NAME, LIST_RULE_NAME),
356 DESCRIPTION = NVL(X_DESCRIPTION, DESCRIPTION),
357 SOURCE_LANG = userenv('LANG'),
358 LAST_UPDATE_DATE = SYSDATE,
359 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
360 LAST_UPDATE_LOGIN = 0
361 WHERE LIST_RULE_ID = X_LIST_RULE_ID
362 AND userenv('LANG') IN (language, source_lang);
363
364 end if;
365 END TRANSLATE_ROW;
366
367
368 ------------------ AMS_LIST_RULE_FIELDS -------------------------------
369 procedure INSERT_FIELD (
370 X_ROWID in OUT NOCOPY VARCHAR2,
371 X_LIST_RULE_FIELD_ID in NUMBER,
372 X_FIELD_COLUMN_NAME in VARCHAR2,
373 X_OBJECT_VERSION_NUMBER in NUMBER,
374 X_SUBSTRING_LENGTH in NUMBER,
375 X_WEIGHTAGE in NUMBER,
376 X_SEQUENCE_NUMBER in NUMBER,
377 X_LIST_RULE_ID in NUMBER,
378 X_FIELD_TABLE_NAME in VARCHAR2,
379 X_CREATION_DATE in DATE,
380 X_CREATED_BY in NUMBER,
381 X_LAST_UPDATE_DATE in DATE,
382 X_LAST_UPDATED_BY in NUMBER,
383 X_LAST_UPDATE_LOGIN in NUMBER,
384 x_WORD_REPLACEMENT_CODE in VARCHAR2,
385 x_LIST_SOURCE_FIELD_ID in NUMBER
386 ) is
387 cursor C is select ROWID from AMS_LIST_RULE_FIELDS
388 where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID
389 ;
390 begin
391 insert into AMS_LIST_RULE_FIELDS (
392 FIELD_COLUMN_NAME,
393 OBJECT_VERSION_NUMBER,
394 SUBSTRING_LENGTH,
395 WEIGHTAGE,
396 SEQUENCE_NUMBER,
397 LIST_RULE_FIELD_ID,
398 LAST_UPDATE_DATE,
399 LAST_UPDATED_BY,
400 CREATION_DATE,
401 CREATED_BY,
402 LAST_UPDATE_LOGIN,
403 LIST_RULE_ID,
404 FIELD_TABLE_NAME,
405 WORD_REPLACEMENT_CODE,
406 LIST_SOURCE_FIELD_ID
407 ) values (
408 X_FIELD_COLUMN_NAME,
409 X_OBJECT_VERSION_NUMBER,
410 X_SUBSTRING_LENGTH,
411 X_WEIGHTAGE,
412 X_SEQUENCE_NUMBER,
413 X_LIST_RULE_FIELD_ID,
414 X_LAST_UPDATE_DATE,
415 X_LAST_UPDATED_BY,
416 X_CREATION_DATE,
417 X_CREATED_BY,
418 X_LAST_UPDATE_LOGIN,
419 X_LIST_RULE_ID,
420 X_FIELD_TABLE_NAME,
421 x_WORD_REPLACEMENT_CODE,
422 x_LIST_SOURCE_FIELD_ID
423 );
424
425 open c;
426 fetch c into X_ROWID;
427 if (c%notfound) then
428 close c;
429 raise no_data_found;
430 end if;
431 close c;
432
433 end INSERT_FIELD;
434
435 procedure LOCK_FIELD (
436 X_LIST_RULE_FIELD_ID in NUMBER,
437 X_FIELD_COLUMN_NAME in VARCHAR2,
438 X_OBJECT_VERSION_NUMBER in NUMBER,
439 X_SUBSTRING_LENGTH in NUMBER,
440 X_WEIGHTAGE in NUMBER,
441 X_SEQUENCE_NUMBER in NUMBER,
442 X_LIST_RULE_ID in NUMBER,
443 X_FIELD_TABLE_NAME in VARCHAR2
444 ) is
445 cursor c1 is select
446 FIELD_COLUMN_NAME,
447 OBJECT_VERSION_NUMBER,
448 SUBSTRING_LENGTH,
449 WEIGHTAGE,
450 SEQUENCE_NUMBER,
451 LIST_RULE_ID,
452 FIELD_TABLE_NAME
453 from AMS_LIST_RULE_FIELDS
454 where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID
455 for update of LIST_RULE_FIELD_ID nowait;
456 begin
457 for tlinfo in c1 loop
458 if ( (tlinfo.FIELD_TABLE_NAME = X_FIELD_TABLE_NAME)
459 AND (tlinfo.FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME)
460 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
461 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
462 AND ((tlinfo.SUBSTRING_LENGTH = X_SUBSTRING_LENGTH)
463 OR ((tlinfo.SUBSTRING_LENGTH is null) AND (X_SUBSTRING_LENGTH is null)))
464 AND ((tlinfo.WEIGHTAGE = X_WEIGHTAGE)
465 OR ((tlinfo.WEIGHTAGE is null) AND (X_WEIGHTAGE is null)))
466 AND ((tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
467 OR ((tlinfo.SEQUENCE_NUMBER is null) AND (X_SEQUENCE_NUMBER is null)))
468 AND (tlinfo.LIST_RULE_ID = X_LIST_RULE_ID)
469 ) then
470 null;
471 else
472 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
473 app_exception.raise_exception;
474 end if;
475 end loop;
476 return;
477 end LOCK_FIELD;
478
479 procedure UPDATE_FIELD (
480 X_LIST_RULE_FIELD_ID in NUMBER,
481 X_FIELD_COLUMN_NAME in VARCHAR2,
482 X_OBJECT_VERSION_NUMBER in NUMBER,
483 X_SUBSTRING_LENGTH in NUMBER,
484 X_WEIGHTAGE in NUMBER,
485 X_SEQUENCE_NUMBER in NUMBER,
486 X_LIST_RULE_ID in NUMBER,
487 X_FIELD_TABLE_NAME in VARCHAR2,
488 X_LAST_UPDATE_DATE in DATE,
489 X_LAST_UPDATED_BY in NUMBER,
490 X_LAST_UPDATE_LOGIN in NUMBER,
491 x_WORD_REPLACEMENT_CODE in VARCHAR2,
492 x_LIST_SOURCE_FIELD_ID in NUMBER
493 ) is
494 begin
495 update AMS_LIST_RULE_FIELDS set
496 FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME,
497 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
498 SUBSTRING_LENGTH = X_SUBSTRING_LENGTH,
499 WEIGHTAGE = X_WEIGHTAGE,
500 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
501 LIST_RULE_ID = X_LIST_RULE_ID,
502 FIELD_TABLE_NAME = X_FIELD_TABLE_NAME,
503 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
504 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
505 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
506 WORD_REPLACEMENT_CODE = x_WORD_REPLACEMENT_CODE,
507 LIST_SOURCE_FIELD_ID = x_LIST_SOURCE_FIELD_ID
508 where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID;
509
510 if (sql%notfound) then
511 raise no_data_found;
512 end if;
513 end UPDATE_FIELD;
514
515 procedure DELETE_FIELD (
516 X_LIST_RULE_FIELD_ID in NUMBER
517 ) is
518 begin
519 delete from AMS_LIST_RULE_FIELDS
520 where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID;
521
522 if (sql%notfound) then
523 raise no_data_found;
524 end if;
525
526 end DELETE_FIELD;
527
528 PROCEDURE load_field (
529 X_LIST_RULE_FIELD_ID in NUMBER,
530 X_FIELD_COLUMN_NAME in VARCHAR2,
531 X_SUBSTRING_LENGTH in NUMBER,
532 X_WEIGHTAGE in NUMBER,
533 X_SEQUENCE_NUMBER in NUMBER,
534 X_LIST_RULE_ID in NUMBER,
535 X_FIELD_TABLE_NAME in VARCHAR2,
536 x_owner IN VARCHAR2,
537 x_WORD_REPLACEMENT_CODE in VARCHAR2,
538 x_LIST_SOURCE_FIELD_ID in NUMBER,
539 x_custom_mode IN VARCHAR2
540
541 )
542 IS
543 l_user_id number := 0;
544 l_obj_verno number;
545 l_dummy_char varchar2(1);
546 l_row_id varchar2(100);
547 l_list_rule_field_id number;
548 l_last_updated_by number;
549
550 CURSOR c_obj_verno IS
551 SELECT object_version_number, last_updated_by
552 FROM ams_list_rule_fields
553 WHERE list_rule_field_id = x_list_rule_field_id;
554
555 CURSOR c_chk_exists is
556 SELECT 'x'
557 FROM ams_list_rule_fields
558 WHERE list_rule_field_id = x_list_rule_field_id;
559
560 CURSOR c_mod_rule is
561 SELECT 'x'
562 FROM ams_list_rule_fields
563 WHERE LIST_RULE_ID = X_LIST_RULE_ID
564 AND LAST_UPDATED_BY <> 1 and last_updated_by <> 2 and last_updated_by <> 0;
565
566 CURSOR c_get_id is
567 SELECT ams_list_rule_fields_s.NEXTVAL
568 FROM DUAL;
569 l_dummy_rule_char varchar2(1);
570 BEGIN
571 if X_OWNER = 'SEED' then
572 l_user_id := 1;
573 elsif X_OWNER = 'ORACLE' then
574 l_user_id := 2;
575 elsif X_OWNER = 'SYSADMIN' THEN
576 l_user_id := 0;
577
578 end if;
579
580 OPEN c_mod_rule ;
581 FETCH c_mod_rule INTO l_dummy_rule_char;
582 IF c_mod_rule%notfound THEN
583 CLOSE c_mod_rule;
584 OPEN c_chk_exists;
585 FETCH c_chk_exists INTO l_dummy_char;
586 IF c_chk_exists%notfound THEN
587 CLOSE c_chk_exists;
588
589 IF x_list_rule_id IS NULL THEN
590 OPEN c_get_id;
591 FETCH c_get_id INTO l_list_rule_field_id;
592 CLOSE c_get_id;
593 ELSE
594 l_list_rule_field_id := x_list_rule_field_id;
595 END IF;
596 l_obj_verno := 1;
597
598 ams_list_rules_pkg.Insert_Field (
599 X_ROWID => l_row_id,
600 X_LIST_RULE_FIELD_ID => l_list_rule_field_id,
601 X_FIELD_COLUMN_NAME => x_field_column_name,
602 X_OBJECT_VERSION_NUMBER => l_obj_verno,
603 X_SUBSTRING_LENGTH => x_substring_length,
604 X_WEIGHTAGE => x_weightage,
605 X_SEQUENCE_NUMBER => x_sequence_number,
606 X_LIST_RULE_ID => x_list_rule_id,
607 X_FIELD_TABLE_NAME => x_field_table_name,
608 X_CREATION_DATE => SYSDATE,
609 X_CREATED_BY => l_user_id,
610 X_LAST_UPDATE_DATE => SYSDATE,
611 X_LAST_UPDATED_BY => l_user_id,
612 X_LAST_UPDATE_LOGIN => 0,
613 x_WORD_REPLACEMENT_CODE => x_WORD_REPLACEMENT_CODE,
614 x_LIST_SOURCE_FIELD_ID => x_LIST_SOURCE_FIELD_ID
615 );
616 ELSE
617 CLOSE c_chk_exists;
618 OPEN c_obj_verno;
619 FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;
620 CLOSE c_obj_verno;
621
622 if (l_last_updated_by in (1,2,0) OR
623 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
624
625 ams_list_rules_pkg.Update_Field (
626 X_LIST_RULE_FIELD_ID => x_list_rule_field_id,
627 X_FIELD_COLUMN_NAME => x_field_column_name,
628 X_OBJECT_VERSION_NUMBER => l_obj_verno,
629 X_SUBSTRING_LENGTH => x_substring_length,
630 X_WEIGHTAGE => x_weightage,
631 X_SEQUENCE_NUMBER => x_sequence_number,
632 X_LIST_RULE_ID => x_list_rule_id,
633 X_FIELD_TABLE_NAME => x_field_table_name,
634 X_LAST_UPDATE_DATE => SYSDATE,
635 X_LAST_UPDATED_BY => l_user_id,
636 X_LAST_UPDATE_LOGIN => 0,
637 x_WORD_REPLACEMENT_CODE => x_WORD_REPLACEMENT_CODE,
638 x_LIST_SOURCE_FIELD_ID => x_LIST_SOURCE_FIELD_ID
639 );
640 END IF;
641
642 end if;
643 ELSE
644 CLOSE c_mod_rule;
645 END IF;
646
647 END load_field;
648
649
650 PROCEDURE TRANSLATE_FIELD (
651 X_LIST_RULE_FIELD_ID IN NUMBER,
652 X_OWNER IN VARCHAR2,
653 x_custom_mode IN VARCHAR2
654
655 ) IS
656 BEGIN
657 -- There is no _TL table, so nothing to translate
658 NULL;
659 END TRANSLATE_FIELD;
660
661
662
663 end ams_list_rules_pkg;