1 PACKAGE BODY AME_CONDITIONS_API2 AS
2 /* $Header: amecnapi.pkb 120.2 2006/07/12 07:24:01 pvelugul noship $ */
3 duplicateCondKeyException exception;
4 X_AME_INSTALLATION_LEVEL varchar2(255);
5 procedure OWNER_TO_WHO (
6 X_OWNER in VARCHAR2,
7 X_CREATED_BY out nocopy NUMBER,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
14 X_LAST_UPDATE_LOGIN := 0;
15 end OWNER_TO_WHO;
16
17 procedure KEY_TO_IDS (
18 X_CONDITION_KEY in VARCHAR2,
19 X_ATTRIBUTE_NAME in VARCHAR2,
20 X_CONDITION_ID out nocopy NUMBER,
21 X_ATTRIBUTE_ID out nocopy NUMBER,
22 X_CUSTOM_DETECT out nocopy BOOLEAN
23 ) is
24
25 cursor CSR_GET_ATTRIBUTE
26 (
27 X_ATTRIBUTE_NAME in VARCHAR2
28 ) is
29 select ATTRIBUTE_ID
30 from AME_ATTRIBUTES
31 where NAME = X_ATTRIBUTE_NAME
32 and sysdate between START_DATE
33 and nvl(END_DATE-(1/86400), sysdate);
34 cursor CSR_GET_CONDITION_ID
35 (
36 X_CONDITION_KEY in VARCHAR2
37 ) is
38 select CONDITION_ID, CREATED_BY
39 from AME_CONDITIONS
40 where CONDITION_KEY = X_CONDITION_KEY;
41
42 cursor CSR_IS_ATTRIBUTE_SEED
43 (
44 X_ATTRIBUTE_ID in VARCHAR2
45 ) is
46 select C.CREATED_BY
47 from AME_ATTRIBUTES C
48 where C.START_DATE =
49 (select min(A.START_DATE) from AME_ATTRIBUTES A
50 where A.ATTRIBUTE_ID = C.ATTRIBUTE_ID)
51 and C.ATTRIBUTE_ID = X_ATTRIBUTE_ID;
52
53 X_ATTR_CREATED_BY number;
54 X_COND_CREATED_BY number;
55 X_CONDITION_ID_2 number;
56 begin
57 open CSR_GET_ATTRIBUTE(
58 X_ATTRIBUTE_NAME
59 );
60 fetch CSR_GET_ATTRIBUTE into X_ATTRIBUTE_ID;
61 if (CSR_GET_ATTRIBUTE%notfound) then
62 X_ATTRIBUTE_ID := null;
63 end if;
64 close CSR_GET_ATTRIBUTE;
65
66 if X_ATTRIBUTE_ID is not null then
67 open CSR_IS_ATTRIBUTE_SEED(
68 X_ATTRIBUTE_ID
69 );
70 fetch CSR_IS_ATTRIBUTE_SEED into X_ATTR_CREATED_BY;
71 if (CSR_IS_ATTRIBUTE_SEED%notfound) OR (X_ATTR_CREATED_BY <> 1)
72 then
73 X_ATTR_CREATED_BY := null;
74 end if;
75 close CSR_IS_ATTRIBUTE_SEED;
76 end if;
77
78 if X_ATTRIBUTE_ID is not null
79 then
80 open CSR_GET_CONDITION_ID (
81 X_CONDITION_KEY
82 );
83 -- fetch CONDITION if there is a match on CONDITION_KEY
84 fetch CSR_GET_CONDITION_ID into X_CONDITION_ID,
85 X_COND_CREATED_BY;
86 if (CSR_GET_CONDITION_ID%notfound) then
87 X_CONDITION_ID := null;
88 else
89 loop
90 -- detect for custom data only
91 if X_COND_CREATED_BY = 1 then
92 X_CUSTOM_DETECT := false;
93 exit;
94 else
95 X_CUSTOM_DETECT := true;
96 end if;
97 fetch CSR_GET_CONDITION_ID into X_CONDITION_ID_2,
98 X_COND_CREATED_BY;
99 if (CSR_GET_CONDITION_ID%notfound) then
100 exit;
101 end if;
102 end loop;
103 end if;
104 close CSR_GET_CONDITION_ID;
105 end if;
106 end KEY_TO_IDS;
107
108 procedure KEY_TO_IDS_2 (
109 X_CONDITION_ID in NUMBER,
110 X_CONDITION_KEY in VARCHAR2,
111 X_ATTRIBUTE_NAME in VARCHAR2,
112 X_EXISTING_CONDITION_KEY out nocopy VARCHAR2,
113 X_CONDITION_COUNT out nocopy NUMBER,
114 X_ATTRIBUTE_ID out nocopy NUMBER
115 ) is
116 cursor CSR_CHECK_CONDITION_KEY
117 (
118 X_CONDITION_KEY in VARCHAR2
119 ) is
120 select CONDITION_ID
121 from AME_CONDITIONS
122 where CONDITION_KEY = X_CONDITION_KEY;
123 cursor CSR_CONDITION_KEY
124 (
125 X_CONDITION_ID in NUMBER
126 ) is
127 select CONDITION_KEY
128 from AME_CONDITIONS
129 where CONDITION_ID = X_CONDITION_ID;
130
131 cursor CSR_CONDITION_COUNT
132 (
133 X_CONDITION_ID in NUMBER
134 ) is
135 select count(*)
136 from AME_CONDITIONS
137 where CONDITION_ID = X_CONDITION_ID;
138 cursor CSR_IS_ATTRIBUTE_SEED
139 (
140 X_ATTRIBUTE_ID in VARCHAR2
141 ) is
142 select C.CREATED_BY
143 from AME_ATTRIBUTES C
144 where C.START_DATE =
145 (select min(A.START_DATE) from AME_ATTRIBUTES A
146 where A.ATTRIBUTE_ID = C.ATTRIBUTE_ID)
147 and C.ATTRIBUTE_ID = X_ATTRIBUTE_ID;
148
149 cursor CSR_GET_ATTRIBUTE
150 (
151 X_ATTRIBUTE_NAME in VARCHAR2
152 ) is
153 select ATTRIBUTE_ID
154 from AME_ATTRIBUTES
155 where NAME = X_ATTRIBUTE_NAME
156 and ((START_DATE - (1/86400)) <= sysdate)
157 and (((END_DATE - (1/86400)) >= sysdate)
158 or (END_DATE is null));
159
160 X_CONDITION_KEY_CHECK NUMBER;
161 X_ATTR_CREATED_BY NUMBER;
162 errorCode integer;
163 errorMessage ame_util.longestStringType;
164 begin
165 if X_AME_INSTALLATION_LEVEL is not null then
166 open CSR_CHECK_CONDITION_KEY (
167 X_CONDITION_KEY
168 );
169 -- fetch CONDITION if there is a match on CONDITION_KEY
170 fetch CSR_CHECK_CONDITION_KEY into X_CONDITION_KEY_CHECK;
171 if (CSR_CHECK_CONDITION_KEY%found) then
172 raise duplicateCondKeyException;
173 end if;
174 close CSR_CHECK_CONDITION_KEY;
175 end if;
176 -- get attribute id
177 open CSR_GET_ATTRIBUTE(
178 X_ATTRIBUTE_NAME
179 );
180 fetch CSR_GET_ATTRIBUTE into X_ATTRIBUTE_ID;
181 if (CSR_GET_ATTRIBUTE%notfound) then
182 X_ATTRIBUTE_ID := null;
183 end if;
184 close CSR_GET_ATTRIBUTE;
185
186 if X_ATTRIBUTE_ID is not null then
187 open CSR_IS_ATTRIBUTE_SEED(
188 X_ATTRIBUTE_ID
189 );
190 fetch CSR_IS_ATTRIBUTE_SEED into X_ATTR_CREATED_BY;
191 if (CSR_IS_ATTRIBUTE_SEED%notfound) OR (X_ATTR_CREATED_BY <> AME_SEED_UTILITY.SEED_USER_ID)
192 then
193 X_ATTR_CREATED_BY := null;
194 end if;
195 close CSR_IS_ATTRIBUTE_SEED;
196 end if;
197
198 if X_ATTRIBUTE_ID is not null then
199 if X_AME_INSTALLATION_LEVEL is not null then
200 open CSR_CONDITION_KEY (X_CONDITION_ID);
201 -- fetch EXISTING CONDITION KEY if there is a match on CONDITION_ID
202 fetch CSR_CONDITION_KEY into X_EXISTING_CONDITION_KEY;
203 if (CSR_CONDITION_KEY%notfound) then
204 X_EXISTING_CONDITION_KEY := null;
205 end if;
206 close CSR_CONDITION_KEY;
207 else
208 X_EXISTING_CONDITION_KEY := null;
209 end if;
210 -- Get the number of conditions existing for the particular conditionID
211 open CSR_CONDITION_COUNT(X_CONDITION_ID);
212 fetch CSR_CONDITION_COUNT
213 into X_CONDITION_COUNT;
214 close CSR_CONDITION_COUNT;
215 end if;
216 exception
217 when duplicateCondKeyException then
218 errorMessage :=
219 'OAM is attempting to upload a duplicate condition key.';
220 errorCode := -20001;
221 ame_util.runtimeException(packageNameIn => 'ame_conditions_api2',
222 routineNameIn => 'key_to_ids_2',
223 exceptionNumberIn => errorCode,
224 exceptionStringIn => errorMessage);
225 raise;
226 when others then
227 ame_util.runtimeException('ame_conditions_api2',
228 'key_to_ids_2',
229 sqlcode,
230 sqlerrm);
231 raise;
232 end KEY_TO_IDS_2;
233
234 procedure VALIDATE_CONDITION(
235 X_CONDITION_TYPE in VARCHAR2,
236 X_ATTRIBUTE_NAME in VARCHAR2,
237 X_PARAMETER_ONE in VARCHAR2,
238 X_PARAMETER_TWO in VARCHAR2,
239 X_PARAMETER_THREE in VARCHAR2,
240 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
241 X_INCLUDE_LOWER_LIMIT in VARCHAR2
242 ) is
243 invalidConditionTypeException exception;
244 invalidCondAttrTypeException exception;
245 errorCode integer;
246 errorMessage ame_util.longestStringType;
247 X_ATTRIBUTE_TYPE varchar2(20);
248 X_PARAMETER_ONE_DATE date;
249 X_PARAMETER_TWO_DATE date;
250 X_PARAMETER_ONE_NUMBER number;
251 X_PARAMETER_TWO_NUMBER number;
252 begin
253 select ATTRIBUTE_TYPE
254 into X_ATTRIBUTE_TYPE
255 from AME_ATTRIBUTES
256 where NAME = X_ATTRIBUTE_NAME
257 and sysdate between START_DATE
258 and nvl(END_DATE-(1/86400), sysdate);
259
260 if X_CONDITION_TYPE not in
261 (ame_util.ordinaryConditionType , ame_util.exceptionConditionType) then
262 raise invalidConditionTypeException;
263 end if;
264
265 if X_ATTRIBUTE_TYPE = ame_util.booleanAttributeType then
266 if (X_PARAMETER_ONE not in
267 (ame_util.booleanAttributeTrue, ame_util.booleanAttributeFalse))
268 or (X_PARAMETER_ONE is null)
269 or (X_PARAMETER_TWO is not null)
270 or (X_PARAMETER_THREE is not null)
271 or (X_INCLUDE_UPPER_LIMIT is not null)
272 or (X_INCLUDE_LOWER_LIMIT is not null) then
273 errorMessage :=
274 'OAM is attempting to upload an invalid boolean attribute condition.';
275 raise invalidCondAttrTypeException;
276 end if;
277 end if;
278
279 if X_ATTRIBUTE_TYPE = ame_util.numberAttributeType then
280 if (X_INCLUDE_LOWER_LIMIT is not null
281 and (X_INCLUDE_LOWER_LIMIT not in
282 (ame_util.booleanTrue,ame_util.booleanFalse)))
283 or (X_INCLUDE_UPPER_LIMIT is not null
284 and (X_INCLUDE_UPPER_LIMIT not in
285 (ame_util.booleanTrue,ame_util.booleanFalse))) then
286 errorMessage :=
287 'OAM is attempting to upload an invalid number attribute condition.';
288 raise invalidCondAttrTypeException;
289 end if;
290 if X_PARAMETER_ONE is not null then
291 select to_number(X_PARAMETER_ONE)
292 into X_PARAMETER_ONE_NUMBER
293 from dual;
294 end if;
295 if X_PARAMETER_TWO is not null then
296 select to_number(X_PARAMETER_TWO)
297 into X_PARAMETER_TWO_NUMBER
298 from dual;
299 end if;
300 end if;
301
302 if X_ATTRIBUTE_TYPE = ame_util.currencyAttributeType then
303 if (X_INCLUDE_LOWER_LIMIT is not null
304 and (X_INCLUDE_LOWER_LIMIT not in
305 (ame_util.booleanTrue,ame_util.booleanFalse)))
306 or (X_INCLUDE_UPPER_LIMIT is not null
307 and (X_INCLUDE_UPPER_LIMIT not in
308 (ame_util.booleanTrue,ame_util.booleanFalse)))
309 or X_PARAMETER_THREE is null then
310 errorMessage :=
311 'OAM is attempting to upload an invalid currency attribute condition.';
312 raise invalidCondAttrTypeException;
313 end if;
314 if X_PARAMETER_ONE is not null then
315 select to_number(X_PARAMETER_ONE)
316 into X_PARAMETER_ONE_NUMBER
317 from dual;
318 end if;
319 if X_PARAMETER_TWO is not null then
320 select to_number(X_PARAMETER_TWO)
321 into X_PARAMETER_TWO_NUMBER
322 from dual;
323 end if;
324 end if;
325
326 if X_ATTRIBUTE_TYPE = ame_util.dateAttributeType then
327 if (X_INCLUDE_LOWER_LIMIT is not null
328 and (X_INCLUDE_LOWER_LIMIT not in
329 (ame_util.booleanTrue,ame_util.booleanFalse)))
330 or (X_INCLUDE_UPPER_LIMIT is not null
331 and (X_INCLUDE_UPPER_LIMIT not in
332 (ame_util.booleanTrue,ame_util.booleanFalse))) then
333 errorMessage :=
334 'OAM is attempting to upload an invalid date attribute condition.';
335 raise invalidCondAttrTypeException;
336 end if;
337 if X_PARAMETER_ONE is not null then
338 select to_date(X_PARAMETER_ONE,'YYYY:MM:DD:HH24:MI:SS')
339 into X_PARAMETER_ONE_DATE
340 from dual;
341 end if;
342 if X_PARAMETER_TWO is not null then
343 select to_date(X_PARAMETER_TWO,'YYYY:MM:DD:HH24:MI:SS')
344 into X_PARAMETER_TWO_DATE
345 from dual;
346 end if;
347 end if;
348
349 if X_ATTRIBUTE_TYPE = ame_util.stringAttributeType then
350 if (X_PARAMETER_ONE is not null)
351 or (X_PARAMETER_TWO is not null)
352 or (X_PARAMETER_THREE is not null)
353 or (X_INCLUDE_LOWER_LIMIT is not null)
354 or (X_INCLUDE_UPPER_LIMIT is not null) then
355 errorMessage :=
356 'OAM is attempting to upload an invalid string attribute condition.';
357 raise invalidCondAttrTypeException;
358 end if;
359 end if;
360 exception
361 when invalidConditionTypeException then
362 errorMessage :=
363 'OAM is attempting to upload an invalid condition type.';
364 errorCode := -20001;
365 ame_util.runtimeException(packageNameIn => 'ame_conditions_api2',
366 routineNameIn => 'validate_condition',
367 exceptionNumberIn => errorCode,
368 exceptionStringIn => errorMessage);
369 raise_application_error(errorCode,
370 errorMessage);
371 when invalidCondAttrTypeException then
372 errorCode := -20001;
373 ame_util.runtimeException(packageNameIn => 'ame_conditions_api2',
374 routineNameIn => 'validate_condition',
375 exceptionNumberIn => errorCode,
376 exceptionStringIn => errorMessage);
377 raise_application_error(errorCode,
378 errorMessage);
379 when invalid_number then
380 errorMessage :=
381 'OAM is attempting to upload an invalid number or currency attribute condition.';
382 errorCode := -20001;
383 ame_util.runtimeException(packageNameIn => 'ame_conditions_api2',
384 routineNameIn => 'validate_condition',
385 exceptionNumberIn => errorCode,
386 exceptionStringIn => errorMessage);
387 raise_application_error(errorCode,
388 errorMessage);
389
390 when others then
391 ame_util.runtimeException('ame_conditions_api2',
392 'validate_condition',
393 sqlcode,
394 sqlerrm);
395 raise;
396 end VALIDATE_CONDITION;
397
398 procedure INSERT_ROW (
399 X_CONDITION_KEY in VARCHAR2,
400 X_CONDITION_TYPE in VARCHAR2,
401 X_ATTRIBUTE_ID in NUMBER,
402 X_PARAMETER_ONE in VARCHAR2,
403 X_PARAMETER_TWO in VARCHAR2,
404 X_PARAMETER_THREE in VARCHAR2,
405 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
406 X_INCLUDE_LOWER_LIMIT in VARCHAR2,
410 X_LAST_UPDATE_DATE in DATE,
407 X_CREATED_BY in NUMBER,
408 X_CREATION_DATE in DATE,
409 X_LAST_UPDATED_BY in NUMBER,
411 X_LAST_UPDATE_LOGIN in NUMBER,
412 X_START_DATE in DATE,
413 X_OBJECT_VERSION_NUMBER in NUMBER)
414 is
415 lockHandle varchar2(500);
416 returnValue integer;
417
418 begin
419
420 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_CONDITIONS.'||X_CONDITION_KEY,lockhandle => lockHandle);
421 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0,release_on_commit => true);
422 if returnValue = 0 then
423 insert into AME_CONDITIONS
424 (
425 CONDITION_ID,
426 CONDITION_KEY,
427 CONDITION_TYPE,
428 ATTRIBUTE_ID,
429 PARAMETER_ONE,
430 PARAMETER_TWO,
431 PARAMETER_THREE,
432 INCLUDE_UPPER_LIMIT,
433 INCLUDE_LOWER_LIMIT,
434 CREATED_BY,
435 CREATION_DATE,
436 LAST_UPDATED_BY,
437 LAST_UPDATE_DATE,
438 LAST_UPDATE_LOGIN,
439 START_DATE,
440 END_DATE,
441 OBJECT_VERSION_NUMBER
442 ) select
443 ame_conditions_s.nextval,
444 X_CONDITION_KEY,
445 X_CONDITION_TYPE,
446 X_ATTRIBUTE_ID,
447 X_PARAMETER_ONE,
448 X_PARAMETER_TWO,
449 X_PARAMETER_THREE,
450 X_INCLUDE_UPPER_LIMIT,
451 X_INCLUDE_LOWER_LIMIT,
452 X_CREATED_BY,
453 X_CREATION_DATE,
454 X_LAST_UPDATED_BY,
455 X_LAST_UPDATE_DATE,
456 X_LAST_UPDATE_LOGIN,
457 X_START_DATE,
458 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
459 X_OBJECT_VERSION_NUMBER
460 from sys.dual
461 where not exists (select NULL
462 from AME_CONDITIONS
463 where CONDITION_KEY = X_CONDITION_KEY);
464 end if;
465 end INSERT_ROW;
466
467 procedure INSERT_ROW_2 (
468 X_CONDITION_ID in NUMBER,
469 X_CONDITION_KEY in VARCHAR2,
470 X_CONDITION_TYPE in VARCHAR2,
471 X_ATTRIBUTE_ID in NUMBER,
472 X_PARAMETER_ONE in VARCHAR2,
473 X_PARAMETER_TWO in VARCHAR2,
474 X_PARAMETER_THREE in VARCHAR2,
475 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
476 X_INCLUDE_LOWER_LIMIT in VARCHAR2,
477 X_CREATED_BY in NUMBER,
478 X_CREATION_DATE in DATE,
479 X_LAST_UPDATED_BY in NUMBER,
480 X_LAST_UPDATE_DATE in DATE,
481 X_LAST_UPDATE_LOGIN in NUMBER,
482 X_START_DATE in DATE,
483 X_OBJECT_VERSION_NUMBER in NUMBER)
484 is
485 lockHandle varchar2(500);
486 returnValue integer;
487 begin
488
489 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_CONDITIONS.'||X_CONDITION_ID,lockhandle => lockHandle);
490 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0,release_on_commit => true);
491 insert into AME_CONDITIONS
492 (
493 CONDITION_ID,
494 CONDITION_KEY,
495 CONDITION_TYPE,
496 ATTRIBUTE_ID,
497 PARAMETER_ONE,
498 PARAMETER_TWO,
499 PARAMETER_THREE,
500 INCLUDE_UPPER_LIMIT,
501 INCLUDE_LOWER_LIMIT,
502 CREATED_BY,
503 CREATION_DATE,
504 LAST_UPDATED_BY,
505 LAST_UPDATE_DATE,
506 LAST_UPDATE_LOGIN,
507 START_DATE,
508 END_DATE,
509 OBJECT_VERSION_NUMBER
510 ) select
511 X_CONDITION_ID,
512 X_CONDITION_KEY,
513 X_CONDITION_TYPE,
514 X_ATTRIBUTE_ID,
515 X_PARAMETER_ONE,
516 X_PARAMETER_TWO,
517 X_PARAMETER_THREE,
518 X_INCLUDE_UPPER_LIMIT,
519 X_INCLUDE_LOWER_LIMIT,
520 X_CREATED_BY,
521 X_CREATION_DATE,
522 X_LAST_UPDATED_BY,
523 X_LAST_UPDATE_DATE,
524 X_LAST_UPDATE_LOGIN,
525 X_START_DATE,
526 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
527 X_OBJECT_VERSION_NUMBER
528 from sys.dual
529 where not exists (select NULL
530 from AME_CONDITIONS
531 where CONDITION_ID = X_CONDITION_ID);
532
533 end INSERT_ROW_2;
534
535 procedure DELETE_ROW (
536 X_CONDITION_KEY in VARCHAR2
537 ) is
538 begin
539 delete from AME_CONDITIONS
540 where CONDITION_KEY = X_CONDITION_KEY;
541
542 if (sql%notfound) then
543 raise no_data_found;
544 end if;
545 end DELETE_ROW;
546
547 procedure LOAD_ROW (
548 X_CONDITION_KEY in VARCHAR2,
549 X_CONDITION_ID in VARCHAR2,
550 X_CONDITION_TYPE in VARCHAR2,
551 X_ATTRIBUTE_NAME in VARCHAR2,
552 X_PARAMETER_ONE in VARCHAR2,
553 X_PARAMETER_TWO in VARCHAR2,
554 X_PARAMETER_THREE in VARCHAR2,
555 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
556 X_INCLUDE_LOWER_LIMIT in VARCHAR2,
557 X_OWNER in VARCHAR2,
558 X_LAST_UPDATE_DATE in VARCHAR2,
559 X_CUSTOM_MODE in VARCHAR2
560 )
561 is
562 X_ATTRIBUTE_ID NUMBER;
563 X_CONDITION_ID_LOAD NUMBER;
564 X_CONDITION_COUNT NUMBER:=0;
565 X_CONDITION_KEY2 VARCHAR2(100);
566 X_CUSTOM_DETECT BOOLEAN := false;
567 X_CREATED_BY NUMBER;
568 X_EXISTING_CONDITION_KEY ame_conditions.condition_key%type:=null;
569 X_LAST_UPDATED_BY NUMBER;
570 X_LAST_UPDATE_LOGIN NUMBER;
571 begin
572 X_AME_INSTALLATION_LEVEL := fnd_profile.value('AME_INSTALLATION_LEVEL');
573 VALIDATE_CONDITION(
574 X_CONDITION_TYPE,
575 X_ATTRIBUTE_NAME,
576 X_PARAMETER_ONE,
577 X_PARAMETER_TWO,
578 X_PARAMETER_THREE,
579 X_INCLUDE_UPPER_LIMIT,
580 X_INCLUDE_LOWER_LIMIT
581 );
582 OWNER_TO_WHO (
583 X_OWNER,
584 X_CREATED_BY,
585 X_LAST_UPDATED_BY,
586 X_LAST_UPDATE_LOGIN
587 );
588
589 if X_CONDITION_ID > 0 then
590 if X_AME_INSTALLATION_LEVEL is not null then
591 -- upload custom conditions when target DB is at AME11510
592 -- drive off CONDITION_KEY
593 X_CONDITION_ID_LOAD := null;
594 KEY_TO_IDS (
595 X_CONDITION_KEY,
596 X_ATTRIBUTE_NAME,
597 X_CONDITION_ID_LOAD,
598 X_ATTRIBUTE_ID,
599 X_CUSTOM_DETECT
600 );
601 -- the condition row was found matching the CONDITION_KEY
602 -- however it is custom created and will have an '@' sign prepended
603 -- insert a new row
604 if AME_SEED_UTILITY.IS_SEED_USER(X_OWNER) and
605 (X_ATTRIBUTE_ID is not null) and
606 (X_CONDITION_ID_LOAD is not null) and
607 X_CUSTOM_DETECT then
608 update AME_CONDITIONS
609 set CONDITION_KEY = '@' || X_CONDITION_KEY
610 where CONDITION_KEY = X_CONDITION_KEY;
611 INSERT_ROW (
612 X_CONDITION_KEY,
613 X_CONDITION_TYPE,
614 X_ATTRIBUTE_ID,
615 X_PARAMETER_ONE,
616 X_PARAMETER_TWO,
617 X_PARAMETER_THREE,
618 X_INCLUDE_UPPER_LIMIT,
619 X_INCLUDE_LOWER_LIMIT,
620 X_CREATED_BY,
621 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
622 X_LAST_UPDATED_BY,
623 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
624 X_LAST_UPDATE_LOGIN,
625 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
626 1);
627 end if;
628 -- the condition row was not found insert a new row
629 if (X_ATTRIBUTE_ID is not null) and
630 (X_CONDITION_ID_LOAD is null) then
631 INSERT_ROW (
632 X_CONDITION_KEY,
633 X_CONDITION_TYPE,
634 X_ATTRIBUTE_ID,
635 X_PARAMETER_ONE,
636 X_PARAMETER_TWO,
637 X_PARAMETER_THREE,
638 X_INCLUDE_UPPER_LIMIT,
639 X_INCLUDE_LOWER_LIMIT,
640 X_CREATED_BY,
641 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
642 X_LAST_UPDATED_BY,
643 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
644 X_LAST_UPDATE_LOGIN,
645 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
646 1);
647 end if;
648 end if;
649 else
650 -- drive off CONDITION_ID
651 KEY_TO_IDS_2 (
652 X_CONDITION_ID,
653 X_CONDITION_KEY,
654 X_ATTRIBUTE_NAME,
655 X_EXISTING_CONDITION_KEY,
656 X_CONDITION_COUNT,
657 X_ATTRIBUTE_ID
658 );
659 if (X_ATTRIBUTE_ID is not null) and
660 (X_CONDITION_COUNT = 0) then
661 X_CONDITION_KEY2 := X_CONDITION_KEY;
662
663 INSERT_ROW_2 (
664 X_CONDITION_ID,
665 X_CONDITION_KEY2,
666 X_CONDITION_TYPE,
667 X_ATTRIBUTE_ID,
668 X_PARAMETER_ONE,
669 X_PARAMETER_TWO,
670 X_PARAMETER_THREE,
671 X_INCLUDE_UPPER_LIMIT,
672 X_INCLUDE_LOWER_LIMIT,
673 X_CREATED_BY,
674 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
675 X_LAST_UPDATED_BY,
676 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
677 X_LAST_UPDATE_LOGIN,
678 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
679 1);
680 end if;
681 -- a current row is found.
682 -- the condition key is from an older ldt and
683 -- must be synchronized with the newer ldt extract from SEED.
684 if (X_ATTRIBUTE_ID is not null) and
685 (X_CONDITION_COUNT > 0) and
686 (X_EXISTING_CONDITION_KEY is not null) and
687 (X_EXISTING_CONDITION_KEY <> X_CONDITION_KEY) then
688 update AME_CONDITIONS
689 set CONDITION_KEY = X_CONDITION_KEY
690 where CONDITION_KEY = X_EXISTING_CONDITION_KEY
691 and CONDITION_ID = X_CONDITION_ID;
692 end if;
693 end if;
694 exception
695 when duplicateCondKeyException then
696 null;
697 when others then
698 ame_util.runtimeException('ame_conditions_api2',
699 'load_row',
700 sqlcode,
701 sqlerrm);
702 raise;
703 end LOAD_ROW;
704 --
705 END AME_CONDITIONS_API2;
[Home] [Help]