[Home] [Help]
PACKAGE BODY: APPS.AME_CONDITION_PKG
Source
1 package body ame_condition_pkg as
2 /* $Header: ameocond.pkb 120.7 2011/05/18 11:27:40 nchinnam ship $ */
3 function getAttributeId(conditionIdIn in integer) return integer as
4 attributeId integer;
5 begin
6 select attribute_id
7 into attributeId
8 from ame_conditions
9 where
10 condition_id = conditionIdIn and
11 sysdate between start_date and
12 nvl(end_date - ame_util.oneSecond, sysdate);
13 return(attributeId);
14 exception
15 when others then
16 rollback;
17 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
18 routineNameIn => 'getAttributeId',
19 exceptionNumberIn => sqlcode,
20 exceptionStringIn => '(condition ID ' ||
21 conditionIdIn||
22 ') ' ||
23 sqlerrm);
24 raise;
25 return(null);
26 end getAttributeId;
27 function getAttributeName(conditionIdIn in integer) return varchar2 as
28 attributeName ame_attributes.name%type;
29 begin
30 select ame_attributes.name
31 into attributeName
32 from
33 ame_attributes,
34 ame_conditions
35 where
36 ame_conditions.condition_id = conditionIdIn and
37 ame_conditions.attribute_id = ame_attributes.attribute_id and
38 sysdate between ame_attributes.start_date and
39 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
40 sysdate between ame_conditions.start_date and
41 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
42 return(attributeName);
43 exception
44 when others then
45 rollback;
46 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
47 routineNameIn => 'getAttributeName',
48 exceptionNumberIn => sqlcode,
49 exceptionStringIn => '(condition ID ' ||
50 conditionIdIn||
51 ') ' ||
52 sqlerrm);
53 raise;
54 return(null);
55 end getAttributeName;
56 function getAttributeType(conditionIdIn in integer) return varchar2 as
57 attributeType ame_attributes.attribute_type%type;
58 begin
59 select attribute_type
60 into attributeType
61 from
62 ame_attributes,
63 ame_conditions
64 where
65 ame_conditions.condition_id = conditionIdIn and
66 ame_attributes.attribute_id = ame_conditions.attribute_id and
67 sysdate between ame_attributes.start_date and
68 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
69 sysdate between ame_conditions.start_date and
70 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
71 return(attributeType);
72 exception
73 when others then
74 rollback;
75 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
76 routineNameIn => 'getAttributeType',
77 exceptionNumberIn => sqlcode,
78 exceptionStringIn => '(condition ID ' ||
79 conditionIdIn||
80 ') ' ||
81 sqlerrm);
82 raise;
83 return(null);
84 end getAttributeType;
85 function getConditionType(conditionIdIn in integer) return varchar2 as
86 conditionType ame_conditions.condition_type%type;
87 begin
88 select condition_type
89 into conditionType
90 from
91 ame_conditions
92 where
93 ame_conditions.condition_id = conditionIdIn and
94 sysdate between start_date and
95 nvl(end_date - ame_util.oneSecond, sysdate);
96 return(conditionType);
97 exception
98 when others then
99 rollback;
100 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
101 routineNameIn => 'getConditionType',
102 exceptionNumberIn => sqlcode,
103 exceptionStringIn => '(condition ID ' ||
104 conditionIdIn||
105 ') ' ||
106 sqlerrm);
107 raise;
108 return(null);
109 end getConditionType;
110 function getConditionKey(conditionIdIn in integer,
111 processingDateIn in date default null) return varchar2 as
112 conditionKey ame_conditions.condition_key%type;
113 begin
114 if processingDateIn is null then
115 select condition_key
116 into conditionKey
117 from ame_conditions
118 where
119 condition_id = conditionIdIn and
120 ((sysdate between start_date and
121 nvl(end_date - ame_util.oneSecond, sysdate)) or
122 (sysdate < start_date and
123 start_date < nvl(end_date,start_date +ame_util.oneSecond)));
124 else
125 select condition_key
126 into conditionKey
127 from ame_conditions
128 where
129 condition_id = conditionIdIn and
130 (processingDateIn between start_date and
131 nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
132 end if;
133 return(conditionKey);
134 exception
135 when others then
136 rollback;
137 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
138 routineNamein => 'getConditionKey',
139 exceptionNumberIn => sqlcode,
140 exceptionStringIn => '(condition ID ' ||
141 conditionIdIn||
142 ') ' ||
143 sqlerrm);
144 raise;
145 return(null);
146 end getConditionKey;
147 function conditionKeyExists(conditionKeyIn in varchar2) return boolean as
148 conditionCount integer;
149 begin
150 select count(*)
151 into conditionCount
152 from ame_conditions
153 where upper(condition_key) = upper(conditionKeyIn) and
154 rownum < 2;
155 if conditionCount > 0 then
156 return(true);
157 else
158 return(false);
159 end if;
160 exception
161 when others then
162 rollback;
163 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
164 routineNamein => 'conditionKeyExists',
165 exceptionNumberIn => sqlcode,
166 exceptionStringIn => '(condition Key ' ||
167 conditionKeyIn ||
168 ') ' ||
169 sqlerrm);
170 raise;
171 return(true);
172 end conditionKeyExists;
173 function getNextConditionKey return varchar2 as
174 cursor get_dbid_cursor is
175 select to_char(db.dbid)
176 from v$database db, v$instance instance
177 where upper(db.name) = upper(instance.instance_name);
178 databaseId varchar2(50);
179 newConditionKey ame_conditions.condition_key%type;
180 newConditionKey1 ame_conditions.condition_key%type;
181 conditionKeyId number;
182 seededKeyPrefix varchar2(4);
183 begin
184 open get_dbid_cursor;
185 fetch get_dbid_cursor
186 into databaseId;
187 if get_dbid_cursor%notfound then
188 -- This case will never happen, since every instance must be linked to a DB
189 databaseId := NULL;
190 end if;
191 close get_dbid_cursor;
192 if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
193 seededKeyPrefix := ame_util.seededKeyPrefix;
194 else
195 seededKeyPrefix := null;
196 end if;
197 loop
198 select ame_condition_keys_s.nextval into conditionKeyId from dual;
199 newConditionKey := databaseId||':'||conditionKeyId;
200 if seededKeyPrefix is not null then
201 newConditionKey1 := seededKeyPrefix||'-' || newConditionKey;
202 else
203 newConditionKey1 := newConditionKey;
204 end if;
205 if not conditionKeyExists(newConditionKey1) then
206 exit;
207 end if;
208 end loop;
209 return(newConditionKey);
210 exception
211 when others then
212 rollback;
213 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
214 routineNamein => 'getNextConditionKey',
215 exceptionNumberIn => sqlcode,
216 exceptionStringIn => '(condition Key ' ||
217 newConditionKey ||
218 ') ' ||
219 sqlerrm);
220 raise;
221 return(null);
222 end getNextConditionKey;
223 function getDescription(conditionIdIn in integer) return varchar2 as
224 cursor stringValueCursor(conditionIdIn in integer) is
225 select string_value
226 from ame_string_values
227 where
228 condition_id = conditionIdIn and
229 sysdate between start_date and
230 nvl(end_date - ame_util.oneSecond, sysdate)
231 order by string_value;
232 description varchar2(500);
233 approverDescription ame_util.longStringType;
234 approverName ame_conditions.parameter_two%type;
235 approverType ame_conditions.parameter_one%type;
236 approverTypeId ame_attributes.approver_type_id%type;
237 attributeId ame_attributes.attribute_id%type;
238 attributeName ame_attributes.name%type;
239 attributeType ame_attributes.attribute_type%type;
240 conditionType ame_conditions.condition_type%type;
241 includeLowerLimit ame_conditions.include_lower_limit%type;
242 includeUpperLimit ame_conditions.include_upper_limit%type;
243 lastName per_all_people_f.last_name%type;
244 lineItem ame_attributes.line_item%type;
245 lineItemLabel varchar2(15);
246 origSystem wf_roles.orig_system%type;
247 parameterOne ame_conditions.parameter_one%type;
248 parameterOneDateString varchar2(500);
249 parameterOneNumberString varchar2(500);
250 parameterTwo ame_conditions.parameter_two%type;
251 parameterTwoDateString varchar2(500);
252 parameterTwoNumberString varchar2(500);
253 parameterThree ame_conditions.parameter_three%type;
254 tempIndex integer;
255 tempValue ame_string_values.string_value%type;
256 wfRolesName wf_roles.name%type;
257 begin
258 conditionType := getConditionType(conditionIdIn => conditionIdIn);
259 if(conditionType = ame_util.listModConditionType) then
260 approverType :=
261 ame_condition_pkg.getParameterOne(conditionIdIn => conditionIdIn);
262 approverName :=
263 ame_condition_pkg.getParameterTwo(conditionIdIn => conditionIdIn);
264 approverDescription :=
265 ame_approver_type_pkg.getApproverDescription(nameIn => approverName);
266 if(approverType = ame_util.finalApprover) then
267 return(ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') ||' '|| approverDescription);
268 else
269 return(ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') ||' '|| approverDescription);
270 end if;
271 end if;
272 attributeId := getAttributeId(conditionIdIn => conditionIdIn);
273 approverTypeId := ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeId);
274 lineItem := ame_attribute_pkg.getLineItem(attributeIdIn => attributeId);
275 if lineItem = ame_util.booleanTrue then
276 lineItemLabel := ame_util.getLabel(ame_util.perFndAppId,'AME_LINE_ITEM_COLON');
277 end if;
278 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeId);
279 attributeType := getAttributeType(conditionIdIn => conditionIdIn);
280 if(attributeType = ame_util.booleanAttributeType) then
281 select parameter_one
282 into parameterOne
283 from ame_conditions
284 where
285 condition_id = conditionIdIn and
286 sysdate between start_date and
287 nvl(end_date - ame_util.oneSecond, sysdate) ;
288 description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IS') || ' ' ||parameterOne;
289 elsif(attributeType = ame_util.stringAttributeType) then
290 description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IN') ||' {';
291 tempIndex := 1;
292 for tempStringValue in stringValueCursor(conditionIdIn => conditionIdIn) loop
293 tempValue := tempStringValue.string_value;
294 if(tempIndex = 4) then
295 description := description || ', . . .';
296 exit;
297 end if;
298 if(tempIndex > 1) then
299 description := description || ', ';
300 end if;
301 description := description || substrb(tempValue,1,50);
302 tempIndex := tempIndex + 1;
303 end loop;
304 description := description || '}';
305 elsif (attributeType = ame_util.numberAttributeType or
306 attributeType = ame_util.dateAttributeType) then
307 select
308 condition_type,
309 parameter_one,
310 parameter_two,
311 include_lower_limit,
312 include_upper_limit
313 into
314 conditionType,
315 parameterOne,
316 parameterTwo,
317 includeLowerLimit,
318 includeUpperLimit
319 from
320 ame_conditions
321 where
322 condition_id = conditionIdIn and
323 sysdate between start_date and
324 nvl(end_date - ame_util.oneSecond, sysdate) ;
325 /* Check if attribute within the condition is associated with an
326 approver type. */
327 if(approverTypeId is not null) then
328 origSystem :=
329 ame_approver_type_pkg.getApproverTypeOrigSystem(approverTypeIdIn => approverTypeId);
330 wfRolesName :=
331 ame_approver_type_pkg.getWfRolesName(origSystemIn => origSystem,
332 origSystemIdIn => to_number(parameterTwo),
333 raiseNoDataFoundIn => 'false');
334 if wfRolesName is null then
335 description := attributeName || ' = ' || 'Invalid :' || origSystem || ':' || parameterTwo;
336 else
337 approverDescription :=
338 ame_approver_type_pkg.getApproverDescription(nameIn => wfRolesName);
339 if(origSystem = ame_util.perOrigSystem) then
340 if(attributeName = ame_util.firstStartingPointAttribute) then
341 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342 elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344 elsif(attributeName = ame_util.secondStartingPointAttribute) then
345 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346 elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348 elsif(attributeName = ame_util.transactionRequestorAttribute) then
349 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350 elsif(attributeName = ame_util.supStartingPointAttribute) then
351 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352 end if;
353 description := attributeName || ' ' || approverDescription;
354 elsif(origSystem = ame_util.fndUserOrigSystem) then
355 if(attributeName = ame_util.transactionReqUserAttribute) then
356 attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357 end if;
358 description := attributeName || ' ' || approverDescription;
359 elsif(origSystem = ame_util.posOrigSystem) then
360 if(attributeName = ame_util.topPositionIdAttribute) then
361 attributeName := 'Top position id: '; -- pa boilerplate
362 end if;
363 description := attributeName || ' ' || approverDescription;
364 end if;
365 end if;
366 else
367 if(parameterOne = parameterTwo and
368 includeLowerLimit = ame_util.booleanTrue and
369 includeUpperLimit = ame_util.booleanTrue) then
370 if attributeType = ame_util.dateAttributeType then
371 parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372 calendar_aware => 1);
373 description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
374 else
375 parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
376 description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
377 end if;
378 else
379 if attributeType = ame_util.dateAttributeType then
380 parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
381 calendar_aware => 1);
382 description := lineItemLabel||parameterOneDateString;
383 else
384 parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
385 description := lineItemLabel||parameterOneNumberString;
386 end if;
387 if parameterOne is not null then
388 if(includeLowerLimit = ame_util.booleanTrue) then
389 description := description || ' <= ';
390 else
391 description := description || ' < ';
392 end if;
393 end if;
394 description := description || attributeName;
395 if parameterTwo is not null then
396 if(includeUpperLimit = ame_util.booleanTrue) then
397 description := description || ' <= ';
398 else
399 description := description || ' < ';
400 end if;
401 if attributeType = ame_util.dateAttributeType then
402 parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo),
403 calendar_aware => 1);
404 description := description || parameterTwoDateString;
405 else
406 parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
407 description := description || parameterTwoNumberString;
408 end if;
409 end if;
410 end if;
411 end if;
412 else -- currency attribute
413 select
414 condition_type,
415 parameter_one,
416 parameter_two,
417 parameter_three,
418 include_lower_limit,
419 include_upper_limit
420 into
421 conditionType,
422 parameterOne,
423 parameterTwo,
424 parameterThree,
425 includeLowerLimit,
426 includeUpperLimit
427 from
428 ame_conditions
429 where
430 condition_id = conditionIdIn and
431 sysdate between start_date and
432 nvl(end_date - ame_util.oneSecond, sysdate) ;
433 if(parameterOne = parameterTwo and
434 includeLowerLimit = ame_util.booleanTrue and
435 includeUpperLimit = ame_util.booleanTrue) then
436 description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
437 else
438 description := lineItemLabel||parameterOne;
439 if parameterOne is not null then
440 if(includeLowerLimit = ame_util.booleanTrue) then
441 description := description||' ' ||parameterThree|| ' <= ';
442 else
443 description := description||' ' ||parameterThree|| ' < ';
444 end if;
445 end if;
446 description := description || attributeName;
447 if parameterTwo is not null then
448 if(includeUpperLimit = ame_util.booleanTrue) then
449 description := description || ' <= ';
450 else
451 description := description || ' < ';
452 end if;
453 description := description || parameterTwo || ' ' || parameterThree;
454 end if;
455 end if;
456 end if;
457 if(lengthb(description) > 100) then
458 description := substrb(description, 1, 93) || ' . . .}';
459 end if;
460 return(description);
461 exception
462 when others then
463 rollback;
464 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
465 routineNameIn => 'getDescription',
466 exceptionNumberIn => sqlcode,
467 exceptionStringIn => '(condition ID ' ||
468 conditionIdIn ||
469 ') ' ||
470 sqlerrm);
471 raise;
472 return(null);
473 end getDescription;
474 function getIncludeLowerLimit(conditionIdIn in integer) return varchar as
475 includeLowerLimit ame_conditions.include_lower_limit%type;
476 begin
477 select include_lower_limit
478 into includeLowerLimit
479 from ame_conditions
480 where
481 condition_id = conditionIdIn and
482 sysdate between start_date and
483 nvl(end_date - ame_util.oneSecond, sysdate) ;
484 return(includeLowerLimit);
485 exception
486 when others then
487 rollback;
488 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
489 routineNameIn => 'getIncludeLowerLimit',
490 exceptionNumberIn => sqlcode,
491 exceptionStringIn => '(condition ID ' ||
492 conditionIdIn||
493 ') ' ||
494 sqlerrm);
495 raise;
496 return(null);
497 end getIncludeLowerLimit;
498 function getIncludeUpperLimit(conditionIdIn in integer) return varchar as
499 includeUpperLimit ame_conditions.include_upper_limit%type;
500 begin
501 select include_upper_limit
502 into includeUpperLimit
503 from ame_conditions
504 where
505 condition_id = conditionIdIn and
506 sysdate between start_date and
507 nvl(end_date - ame_util.oneSecond, sysdate) ;
508 return(includeUpperLimit);
509 exception
510 when others then
511 rollback;
512 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
513 routineNameIn => 'getIncludeUpperLimit',
514 exceptionNumberIn => sqlcode,
515 exceptionStringIn => '(condition ID ' ||
516 conditionIdIn||
517 ') ' ||
518 sqlerrm);
519 raise;
520 return(null);
521 end getIncludeUpperLimit;
522 function isStringAttributeType(conditionIdIn in integer) return boolean as
523 attributeType ame_attributes.attribute_type%type;
524 begin
525 select attribute_type
526 into attributeType
527 from
528 ame_attributes,
529 ame_conditions
530 where
531 ame_conditions.condition_id = conditionIdIn and
532 ame_attributes.attribute_id = ame_conditions.attribute_id and
533 sysdate between ame_conditions.start_date and
534 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
535 sysdate between ame_attributes.start_date and
536 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
537 if attributeType = ame_util.stringAttributeType then
538 return(true);
539 end if;
540 return(false);
541 exception
542 when no_data_found then
543 rollback;
544 return(false);
545 when others then
546 rollback;
547 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
548 routineNameIn => 'isStringAttributeType',
549 exceptionNumberIn => sqlcode,
550 exceptionStringIn => '(condition ID ' ||
551 conditionIdIn||
552 ') ' ||
553 sqlerrm);
554 raise;
555 return(false);
556 end isStringAttributeType;
557 function getParameterOne(conditionIdIn in integer) return varchar as
558 parameterOne ame_conditions.parameter_one%type;
559 begin
560 select parameter_one
561 into parameterOne
562 from ame_conditions
563 where
564 condition_id = conditionIdIn and
565 sysdate between start_date and
566 nvl(end_date - ame_util.oneSecond, sysdate) ;
567 return(parameterOne);
568 exception
569 when others then
570 rollback;
571 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
572 routineNameIn => 'getParameterOne',
573 exceptionNumberIn => sqlcode,
574 exceptionStringIn => '(condition ID ' ||
575 conditionIdIn||
576 ') ' ||
577 sqlerrm);
578 raise;
579 return(null);
580 end getParameterOne;
581 function getParameterTwo(conditionIdIn in integer) return varchar as
582 parameterTwo ame_conditions.parameter_two%type;
583 begin
584 select parameter_two
585 into parameterTwo
586 from ame_conditions
587 where
588 condition_id = conditionIdIn and
589 sysdate between start_date and
590 nvl(end_date - ame_util.oneSecond, sysdate) ;
591 return(parameterTwo);
592 exception
593 when others then
594 rollback;
595 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
596 routineNameIn => 'getParameterTwo',
597 exceptionNumberIn => sqlcode,
598 exceptionStringIn => '(condition ID ' ||
599 conditionIdIn||
600 ') ' ||
601 sqlerrm);
602 raise;
603 return(null);
604 end getParameterTwo;
605 function getParameterThree(conditionIdIn in integer) return varchar as
606 parameterThree ame_conditions.parameter_three%type;
607 begin
608 select parameter_three
609 into parameterThree
610 from ame_conditions
611 where
612 condition_id = conditionIdIn and
613 sysdate between start_date and
614 nvl(end_date - ame_util.oneSecond, sysdate) ;
615 return(parameterThree);
616 exception
617 when others then
618 rollback;
619 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
620 routineNameIn => 'getParameterThree',
621 exceptionNumberIn => sqlcode,
622 exceptionStringIn => '(condition ID ' ||
623 conditionIdIn||
624 ') ' ||
625 sqlerrm);
626 raise;
627 return(null);
628 end getParameterThree;
629 function getStartDate(conditionIdIn in integer) return date as
630 startDate date;
631 begin
632 select start_date
633 into startDate
634 from ame_conditions
635 where
636 condition_id = conditionIdIn and
637 sysdate between start_date and
638 nvl(end_date - ame_util.oneSecond, sysdate) ;
639 return(startDate);
640 exception
641 when others then
642 rollback;
643 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
644 routineNameIn => 'getStartDate',
645 exceptionNumberIn => sqlcode,
646 exceptionStringIn => '(condition ID ' ||
647 conditionIdIn||
648 ') ' ||
649 sqlerrm);
650 raise;
651 return(null);
652 end getStartDate;
653 function getType(conditionIdIn in integer) return varchar2 as
654 conditionType ame_conditions.condition_type%type;
655 begin
656 select condition_type
657 into conditionType
658 from ame_conditions
659 where
660 condition_id = conditionIdIn and
661 sysdate between start_date and
662 nvl(end_date - ame_util.oneSecond, sysdate) ;
663 return(conditionType);
664 exception
665 when others then
666 rollback;
667 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
668 routineNameIn => 'getType',
669 exceptionNumberIn => sqlcode,
670 exceptionStringIn => '(condition ID ' ||
671 conditionIdIn||
672 ') ' ||
673 sqlerrm);
674 raise;
675 return(null);
676 end getType;
677 function getVersionStartDate(conditionIdin integer) return varchar2 as
678 startDate date;
679 stringStartDate varchar2(50);
680 begin
681 select start_date
682 into startDate
683 from ame_conditions
684 where
685 condition_id = conditionIdIn and
686 sysdate between start_date and
687 nvl(end_date - ame_util.oneSecond, sysdate) ;
688 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
689 return(stringStartDate);
690 exception
691 when others then
692 rollback;
693 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
694 routineNameIn => 'getVersionStartDate',
695 exceptionNumberIn => sqlcode,
696 exceptionStringIn => '(condition ID ' ||
697 conditionIdIn||
698 ') ' ||
699 sqlerrm);
700 raise;
701 return(null);
702 end getVersionStartDate;
703 function isConditionUsage(ruleIdIn in integer,
704 conditionIdIn in integer) return boolean as
705 useCount integer;
706 begin
707 select count(*)
708 into useCount
709 from
710 ame_condition_usages
711 where
712 condition_id = conditionIdIn and
713 rule_id = ruleIdIn and
714 ((sysdate between start_date and
715 nvl(end_date - ame_util.oneSecond, sysdate)) or
716 (sysdate < start_date and
717 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
718 if(useCount > 0) then
719 return(true);
720 end if;
721 return(false);
722 exception
723 when others then
724 rollback;
725 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
726 routineNameIn => 'isConditionUsage',
727 exceptionNumberIn => sqlcode,
728 exceptionStringIn => '(condition ID ' ||
729 conditionIdIn||
730 ') ' ||
731 sqlerrm);
732 raise;
733 return(true);
734 end isConditionUsage;
735 function isInUseByOtherApps(conditionIdIn in integer,
736 applicationIdIn in integer) return boolean as
737 useCount integer;
738 begin
739 select count(*)
740 into useCount
741 from
742 ame_rule_usages,
743 ame_condition_usages
744 where
745 ame_rule_usages.rule_id = ame_condition_usages.rule_id and
746 ame_rule_usages.item_id <> applicationIdIn and
747 ame_condition_usages.condition_id = conditionIdIn and
748 ((sysdate between ame_rule_usages.start_date and
749 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
750 (sysdate < ame_rule_usages.start_date and
751 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
752 ame_rule_usages.start_date + ame_util.oneSecond))) and
753 ((sysdate between ame_condition_usages.start_date and
754 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
755 (sysdate < ame_condition_usages.start_date and
756 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
757 ame_condition_usages.start_date + ame_util.oneSecond))) ;
758 if(useCount > 0) then
759 return(true);
760 end if;
761 return(false);
762 exception
763 when others then
764 rollback;
765 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
766 routineNameIn => 'isInUseByOtherApps',
767 exceptionNumberIn => sqlcode,
768 exceptionStringIn => '(condition ID ' ||
769 conditionIdIn||
770 ') ' ||
771 sqlerrm);
772 raise;
773 return(true);
774 end isInUseByOtherApps;
775 function isInUse(conditionIdIn in integer) return boolean as
776 useCount integer;
777 begin
778 select count(*)
779 into useCount
780 from ame_condition_usages
781 where
782 condition_id = conditionIdIn and
783 ((sysdate between start_date and
784 nvl(end_date - ame_util.oneSecond, sysdate)) or
785 (sysdate < start_date and
786 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
787 if(useCount > 0) then
788 return(true);
789 end if;
790 return(false);
791 exception
792 when others then
793 rollback;
794 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
795 routineNameIn => 'isInUse',
796 exceptionNumberIn => sqlcode,
797 exceptionStringIn => '(condition ID ' ||
798 conditionIdIn||
799 ') ' ||
800 sqlerrm);
801 raise;
802 return(true);
803 end isInUse;
804 function lineItemIsInUse(applicationIdIn in integer,
805 conditionTypeIn in varchar2) return boolean as
806 lineItemCount integer;
807 begin
808 select count(*)
809 into lineItemCount
810 from
811 ame_attribute_usages,
812 ame_attributes,
813 ame_conditions
814 where
815 ame_attributes.attribute_id = ame_conditions.attribute_id and
816 ame_conditions.condition_type = conditionTypeIn and
817 ame_attributes.line_item = ame_util.booleanTrue and
818 ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
819 ame_attribute_usages.application_id = applicationIdIn and
820 ((sysdate between ame_attribute_usages.start_date and
821 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
822 (sysdate < ame_attribute_usages.start_date and
823 ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
824 ame_attribute_usages.start_date + ame_util.oneSecond))) and
825 sysdate between ame_attributes.start_date and
826 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
827 sysdate between ame_conditions.start_date and
828 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
829 if(lineItemCount > 0) then
830 return(true);
831 end if;
832 return(false);
833 exception
834 when others then
835 rollback;
836 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
837 routineNameIn => 'lineItemIsInUse',
838 exceptionNumberIn => sqlcode,
839 exceptionStringIn => sqlerrm);
840 raise;
841 return(true);
842 end lineItemIsInUse;
843 function new(typeIn in varchar2,
844 attributeIdIn in integer,
845 conditionKeyIn in varchar2,
846 attributeTypeIn in varchar2 default null,
847 parameterOneIn in varchar2 default null,
848 parameterTwoIn in varchar2 default null,
849 parameterThreeIn in varchar2 default null,
850 includeLowerLimitIn in varchar2 default null,
851 includeUpperLimitIn in varchar2 default null,
852 stringValueListIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
853 newStartDateIn in date default null,
854 conditionIdIn in integer default null,
855 commitIn in boolean default true,
856 processingDateIn in date default null) return integer as
857 cursor conditionCursor(attributeIdIn in integer) is
858 select condition_id
859 from ame_conditions
860 where
861 attribute_id = attributeIdIn and
862 sysdate between start_date and
863 nvl(end_date - ame_util.oneSecond, sysdate) ;
864 attributeType ame_attributes.attribute_type%type;
865 conditionCount integer;
866 conditionExistsException exception;
867 conditionId integer;
868 condKeyLengthException exception;
869 createdBy integer;
870 currencyNumberException exception;
871 currencyNumberException1 exception;
872 currentUserId integer;
873 dateException exception;
874 errorCode integer;
875 errorMessage ame_util.longestStringType;
876 invalidConditionException exception;
877 parameterOneLengthException exception;
878 parameterTwoLengthException exception;
879 parameterThreeLengthException exception;
880 lowerlimitLengthException exception;
881 stringCount integer;
882 stringValueList ame_util.longestStringList;
883 tempCount integer;
884 tempCount2 integer;
885 tempIndex integer;
886 tempStringValueList ame_util.longestStringList;
887 upperLimit integer;
888 upperlimitLengthException exception;
889 processingDate date;
890 begin
891 if processingDateIn is null then
892 processingDate := sysdate;
893 else
894 processingDate := processingDateIn;
895 end if;
896 if(typeIn = ame_util.listModConditionType) then
897 attributeType := null;
898 elsif(attributeTypeIn is null) then
899 attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
900 else
901 attributeType := attributeTypeIn;
902 end if;
903 if(lengthb(conditionKeyIn) > 100) then
904 raise condKeyLengthException;
905 end if;
906 if(attributeType = ame_util.dateAttributeType) then
907 if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
908 > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909 raise dateException;
910 end if;
911 elsif(attributeType = ame_util.currencyAttributeType or
912 attributeType = ame_util.numberAttributeType) then
913 if(ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeIdIn)) is null then
914 if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
915 raise currencyNumberException;
916 end if;
917 if(to_number(parameterOneIn) = to_number(parameterTwoIn) and
918 (includeLowerLimitIn = ame_util.booleanFalse or
919 includeUpperLimitIn = ame_util.booleanFalse)) then
920 raise currencyNumberException1;
921 end if;
922 end if;
923 end if;
924 if(attributeType = ame_util.stringAttributeType) then
925 stringValueList := stringValueListIn; /* necessary for in/out parameter below */
926 stringCount := stringValueList.count;
927 for i in 1 .. stringCount loop
928 if(instrb(stringValueList(i), ',') > 0) then
929 raise invalidConditionException;
930 end if;
931 end loop;
932 ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
933 for tempCondition in conditionCursor(attributeIdIn => attributeIdIn) loop
934 getStringValueList(conditionIdIn => tempCondition.condition_id,
935 stringValueListOut => tempStringValueList);
936 if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
937 longestStringList2InOut => tempStringValueList)) then
938 raise conditionExistsException;
939 end if;
940 end loop;
941 else
942 select count(*)
943 into tempCount
944 from ame_conditions
945 where
946 condition_type = typeIn and
947 attribute_id = attributeIdIn and
948 ((parameterOneIn is null and parameter_one is null) or
949 upper(parameter_one) = upper(parameterOneIn)) and
950 ((parameterTwoIn is null and parameter_two is null) or
951 upper(parameter_two) = upper(parameterTwoIn)) and
952 ((parameterThreeIn is null and parameter_three is null) or
953 upper(parameter_three) = upper(parameterThreeIn)) and
954 ((include_lower_limit is null and includeLowerLimitIn is null) or
955 upper(include_lower_limit) = upper(includeLowerLimitIn)) and
956 ((include_upper_limit is null and includeUpperLimitIn is null) or
957 upper(include_upper_limit) = upper(includeUpperLimitIn)) and
958 processingDate between start_date and
959 nvl(end_date - ame_util.oneSecond, processingDate) ;
960 if tempCount > 0 then
961 raise conditionExistsException;
962 end if;
963 end if;
964 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
965 columnNameIn => 'parameter_one',
966 argumentIn => parameterOneIn)) then
967 raise parameterOneLengthException;
968 end if;
969 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
970 columnNameIn => 'parameter_two',
971 argumentIn => parameterTwoIn)) then
972 raise parameterTwoLengthException;
973 end if;
974 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
975 columnNameIn => 'parameter_three',
976 argumentIn => parameterThreeIn)) then
977 raise parameterThreeLengthException;
978 end if;
979 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
980 columnNameIn => 'include_lower_limit',
981 argumentIn => includeLowerLimitIn)) then
982 raise lowerlimitLengthException;
983 end if;
984 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
985 columnNameIn => 'include_upper_limit',
986 argumentIn => includeUpperLimitIn)) then
987 raise upperlimitLengthException;
988 end if;
989 /*
990 If any version of the object has created_by = 1, all versions,
991 including the new version, should. This is a failsafe way to check
992 whether previous versions of an already end-dated object had
993 created_by = 1.
994 */
995 currentUserId := ame_util.getCurrentUserId;
996 if(conditionIdIn is null) then
997 createdBy := currentUserId;
998 if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
999 /* Use negative condition IDs for developer-seeded conditions. */
1000 select count(*)
1001 into conditionCount
1002 from ame_conditions
1003 where
1004 processingDate between start_date and
1005 nvl(end_date - ame_util.oneSecond, processingDate) ;
1006 if conditionCount = 0 then
1007 conditionId := -1;
1008 else
1009 select min(condition_id) - 1
1010 into conditionId
1011 from ame_conditions
1012 where
1013 processingDate between start_date and
1014 nvl(end_date - ame_util.oneSecond, processingDate);
1015 if(conditionId > -1) then
1016 conditionId := -1;
1017 end if;
1018 end if;
1019 else
1020 select ame_conditions_s.nextval into conditionId from dual;
1021 end if;
1022 else
1023 conditionId := conditionIdIn;
1024 select count(*)
1025 into tempCount2
1026 from ame_conditions
1027 where
1028 condition_id = conditionId and
1029 created_by = ame_util.seededDataCreatedById;
1030 if(tempCount2 > 0) then
1031 createdBy := ame_util.seededDataCreatedById;
1032 else
1033 createdBy := currentUserId;
1034 end if;
1035 end if;
1036 insert into ame_conditions(condition_id,
1037 condition_key,
1038 condition_type,
1039 attribute_id,
1040 parameter_one,
1041 parameter_two,
1042 parameter_three,
1043 include_lower_limit,
1044 include_upper_limit,
1045 created_by,
1046 creation_date,
1047 last_updated_by,
1048 last_update_date,
1049 last_update_login,
1050 start_date,
1051 end_date)
1052 values(conditionId,
1053 conditionKeyIn,
1054 typeIn,
1055 attributeIdIn,
1056 parameterOneIn,
1057 parameterTwoIn,
1058 parameterThreeIn,
1059 includeLowerLimitIn,
1060 includeUpperLimitIn,
1061 createdBy,
1062 processingDate,
1063 currentUserId,
1064 processingDate,
1065 currentUserId,
1066 nvl(newStartDateIn, processingDate),
1067 null);
1068 if(attributeType = ame_util.stringAttributeType) then
1069 upperLimit := stringValueList.count;
1070 for i in 1..upperLimit loop
1071 insert into ame_string_values(condition_id,
1072 string_value,
1073 created_by,
1074 creation_date,
1075 last_updated_by,
1076 last_update_date,
1077 last_update_login,
1078 start_date,
1079 end_date)
1080 values(conditionId,
1081 stringValueList(i),
1082 createdBy,
1083 processingDate,
1084 currentUserId,
1085 processingDate,
1086 currentUserId,
1087 nvl(newStartDateIn, processingDate),
1088 null);
1089 end loop;
1090 end if;
1091 if(commitIn) then
1092 commit;
1093 end if;
1094 return(conditionId);
1095 exception
1096 when condKeyLengthException then
1097 rollback;
1098 errorCode := -20001;
1099 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1100 messageNameIn => 'AME_400362_COND_KEY_LONG',
1101 tokenNameOneIn => 'COLUMN_LENGTH',
1102 tokenValueOneIn => 100);
1103 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
1104 routineNamein => 'new',
1105 exceptionNumberIn => errorCode,
1106 exceptionStringIn => errorMessage);
1107 raise_application_error(errorCode,
1108 errorMessage);
1109 return(null);
1110 when invalidConditionException then
1111 rollback;
1112 errorCode := -20001;
1113 errorMessage :=
1114 ame_util.getMessage(applicationShortNameIn => 'PER',
1115 messageNameIn => 'AME_400182_CON_STR_VAL_COMMA');
1116 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1117 routineNameIn => 'new',
1118 exceptionNumberIn => errorCode,
1119 exceptionStringIn => errorMessage);
1120 raise_application_error(errorCode,
1121 errorMessage);
1122 return(null);
1123 when conditionExistsException then
1124 rollback;
1125 errorCode := -20001;
1126 errorMessage :=
1127 ame_util.getMessage(applicationShortNameIn => 'PER',
1128 messageNameIn => 'AME_400183_CON_ALRDY_EXISTS');
1129 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1130 routineNameIn => 'new',
1131 exceptionNumberIn => errorCode,
1132 exceptionStringIn => errorMessage);
1133 raise_application_error(errorCode,
1134 errorMessage);
1135 return(null);
1136 when parameterOneLengthException then
1137 rollback;
1138 errorCode := -20001;
1139 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1140 messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1141 tokenNameOneIn => 'COLUMN_LENGTH',
1142 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1143 columnNameIn => 'parameter_one'));
1144 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1145 routineNameIn => 'new',
1146 exceptionNumberIn => errorCode,
1147 exceptionStringIn => errorMessage);
1148 raise_application_error(errorCode,
1149 errorMessage);
1150 return(null);
1151 when parameterTwoLengthException then
1152 rollback;
1153 errorCode := -20001;
1154 errorMessage :=
1155 ame_util.getMessage(applicationShortNameIn => 'PER',
1156 messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1157 tokenNameOneIn => 'COLUMN_LENGTH',
1158 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1159 columnNameIn => 'parameter_two'));
1160 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1161 routineNameIn => 'new',
1162 exceptionNumberIn => errorCode,
1163 exceptionStringIn => errorMessage);
1164 raise_application_error(errorCode,
1165 errorMessage);
1166 return(null);
1167 when parameterThreeLengthException then
1168 rollback;
1169 errorCode := -20001;
1170 errorMessage :=
1171 ame_util.getMessage(applicationShortNameIn => 'PER',
1172 messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1173 tokenNameOneIn => 'COLUMN_LENGTH',
1174 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1175 columnNameIn => 'parameter_three'));
1176 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1177 routineNameIn => 'new',
1178 exceptionNumberIn => errorCode,
1179 exceptionStringIn => errorMessage);
1180 raise_application_error(errorCode,
1181 errorMessage);
1182 return(null);
1183 when lowerlimitLengthException then
1184 rollback;
1185 errorCode := -20001;
1186 errorMessage :=
1187 ame_util.getMessage(applicationShortNameIn => 'PER',
1188 messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1189 tokenNameOneIn => 'COLUMN_LENGTH',
1190 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1191 columnNameIn => 'include_lower_limit'));
1192 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1193 routineNameIn => 'new',
1194 exceptionNumberIn => errorCode,
1195 exceptionStringIn => errorMessage);
1196 raise_application_error(errorCode,
1197 errorMessage);
1198 return(null);
1199 when upperlimitLengthException then
1200 rollback;
1201 errorCode := -20001;
1202 errorMessage :=
1203 ame_util.getMessage(applicationShortNameIn => 'PER',
1204 messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1205 tokenNameOneIn => 'COLUMN_LENGTH',
1206 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1207 columnNameIn => 'include_upper_limit'));
1208 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1209 routineNameIn => 'new',
1210 exceptionNumberIn => errorCode,
1211 exceptionStringIn => errorMessage);
1212 raise_application_error(errorCode,
1213 errorMessage);
1214 return(null);
1215 when dateException then
1216 rollback;
1217 errorCode := -20001;
1218 errorMessage :=
1219 ame_util.getMessage(applicationShortNameIn => 'PER',
1220 messageNameIn => 'AME_400186_CON_START_LESS_END');
1221 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1222 routineNameIn => 'new',
1223 exceptionNumberIn => errorCode,
1224 exceptionStringIn => errorMessage);
1225 raise_application_error(errorCode,
1226 errorMessage);
1227 return(null);
1228 when currencyNumberException then
1229 rollback;
1230 errorCode := -20001;
1231 errorMessage :=
1232 ame_util.getMessage(applicationShortNameIn => 'PER',
1233 messageNameIn => 'AME_400187_CON_LWR_LESS_UPP');
1234 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1235 routineNameIn => 'new',
1236 exceptionNumberIn => errorCode,
1237 exceptionStringIn => errorMessage);
1238 raise_application_error(errorCode,
1239 errorMessage);
1240 return(null);
1241 when currencyNumberException1 then
1242 rollback;
1243 errorCode := -20001;
1244 errorMessage :=
1245 ame_util.getMessage(applicationShortNameIn => 'PER',
1246 messageNameIn => 'AME_400188_CON_LMT_VAL_YES');
1247 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1248 routineNameIn => 'new',
1249 exceptionNumberIn => errorCode,
1250 exceptionStringIn => errorMessage);
1251 raise_application_error(errorCode,
1252 errorMessage);
1253 return(null);
1254 when others then
1255 rollback;
1256 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1257 routineNameIn => 'new',
1258 exceptionNumberIn => sqlcode,
1259 exceptionStringIn => '(condition ID ' ||
1260 conditionIdIn||
1261 ') ' ||
1262 sqlerrm);
1263 raise;
1264 return(null);
1265 end new;
1266 function newConditionUsage(ruleIdIn in integer,
1267 conditionIdIn in integer,
1268 processingDateIn in date default null) return boolean as
1269 createdBy integer;
1270 currentUserId integer;
1271 errorCode integer;
1272 errorMessage ame_util.longestStringType;
1273 processingDate date;
1274 tempCount integer;
1275 usageExistsException exception;
1276 useCount integer;
1277 begin
1278 if processingDateIn is null then
1279 processingDate := sysdate;
1280 else
1281 processingDate := processingDateIn;
1282 end if;
1283 select count(*)
1284 into useCount
1285 from ame_condition_usages
1286 where
1287 condition_id = conditionIdIn and
1288 rule_id = ruleIdIn and
1289 ((sysdate between start_date and
1290 nvl(end_date - ame_util.oneSecond, sysdate)) or
1291 (sysdate < start_date and
1292 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1293 if(useCount > 0) then
1294 raise usageExistsException;
1295 end if;
1296 currentUserId := ame_util.getCurrentUserId;
1297 select count(*)
1298 into tempCount
1299 from ame_condition_usages
1300 where
1301 condition_id = conditionIdIn and
1302 rule_id = ruleIdIn and
1303 created_by = ame_util.seededDataCreatedById;
1304 if(tempCount > 0) then
1305 createdBy := ame_util.seededDataCreatedById;
1306 else
1307 createdBy := currentUserId;
1308 end if;
1309 insert into ame_condition_usages(rule_id,
1310 condition_id,
1311 created_by,
1312 creation_date,
1313 last_updated_by,
1314 last_update_date,
1315 last_update_login,
1316 start_date,
1317 end_date)
1318 values(ruleIdIn,
1319 conditionIdIn,
1320 createdBy,
1321 processingDate,
1322 currentUserId,
1323 processingDate,
1324 currentUserId,
1325 processingDate,
1326 null);
1327 commit;
1328 return(true);
1329 exception
1330 when usageExistsException then
1331 rollback;
1332 errorCode := -20001;
1333 errorMessage :=
1334 ame_util.getMessage(applicationShortNameIn => 'PER',
1335 messageNameIn => 'AME_400189_CON_RULE_USES');
1336 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1337 routineNameIn => 'newConditionUsage',
1338 exceptionNumberIn => errorCode,
1339 exceptionStringIn => errorMessage);
1340 raise_application_error(errorCode,
1341 errorMessage);
1342 return(false);
1343 when others then
1344 rollback;
1345 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1346 routineNameIn => 'newConditionUsage',
1347 exceptionNumberIn => sqlcode,
1348 exceptionStringIn => '(condition ID ' ||
1349 conditionIdIn||
1350 ') ' ||
1351 sqlerrm);
1352 raise;
1353 return(true);
1354 end newConditionUsage;
1355 function newStringValue(conditionIdIn in integer,
1356 valueIn in varchar2,
1357 processingDateIn in date default null) return boolean as
1358 currentUserId integer;
1359 errorCode integer;
1360 errorMessage ame_util.longestStringType;
1361 stringValueLengthException exception;
1362 useCount integer;
1363 processingDate date;
1364 begin
1365 if processingDateIn is null then
1366 processingDate := sysdate;
1367 else
1368 processingDate := processingDateIn;
1369 end if;
1370 select count(*)
1371 into useCount
1372 from ame_string_values
1373 where
1374 condition_id = conditionIdIn and
1375 /* string values are case sensitive */
1376 string_value = valueIn and
1377 sysdate between start_date and
1378 nvl(end_date - ame_util.oneSecond, sysdate) ;
1379 if(useCount > 0) then
1380 return(false);
1381 end if;
1382 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_string_values',
1383 columnNameIn => 'string_value',
1384 argumentIn => valueIn)) then
1385 raise stringValueLengthException;
1386 end if;
1387 currentUserId := ame_util.getCurrentUserId;
1388 insert into ame_string_values(condition_id,
1389 string_value,
1390 created_by,
1391 creation_date,
1392 last_updated_by,
1393 last_update_date,
1394 last_update_login,
1395 start_date,
1396 end_date)
1397 values(conditionIdIn,
1398 valueIn,
1399 currentUserId,
1400 processingDate,
1401 currentUserId,
1402 processingDate,
1403 currentUserId,
1404 processingDate,
1405 null);
1406 commit;
1407 exception
1408 when stringValueLengthException then
1409 rollback;
1410 errorCode := -20001;
1411 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1412 messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1413 tokenNameOneIn => 'COLUMN_LENGTH',
1414 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1415 columnNameIn => 'string_value'));
1416 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1417 routineNameIn => 'newStringValue',
1418 exceptionNumberIn => errorCode,
1419 exceptionStringIn => errorMessage);
1420 raise_application_error(errorCode,
1421 errorMessage);
1422 return(false);
1423 when others then
1424 rollback;
1425 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1426 routineNameIn => 'newStringValue',
1427 exceptionNumberIn => sqlcode,
1428 exceptionStringIn => '(condition ID ' ||
1429 conditionIdIn||
1430 ') ' ||
1431 sqlerrm);
1432 raise;
1433 return(false);
1434 end newStringValue;
1435 procedure change(conditionIdIn in integer,
1436 stringValuesIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
1437 typeIn in varchar2 default null,
1438 attributeIdIn in integer default null,
1439 parameterOneIn in varchar2 default null,
1440 parameterTwoIn in varchar2 default null,
1441 parameterThreeIn in varchar2 default null,
1442 includeLowerLimitIn in varchar2 default null,
1443 includeUpperLimitIn in varchar2 default null,
1444 versionStartDateIn in date,
1445 processingDateIn in date default null) as
1446 cursor conditionCursor(typeIn in varchar2,
1447 attributeIdIn in integer,
1448 parameterOneIn in varchar2,
1449 parameterTwoIn in varchar2,
1450 parameterThreeIn in varchar2,
1451 includeLowerLimitIn in varchar2,
1452 includeUpperLimitIn in varchar2) is
1453 select condition_id
1454 from ame_conditions
1455 where
1456 attribute_id = attributeIdIn and
1457 condition_type = typeIn and
1458 ((parameter_one is null and parameterOneIn is null) or
1459 (parameter_one = parameterOneIn)) and
1460 ((parameter_two is null and parameterTwoIn is null) or
1461 (parameter_two = parameterTwoIn)) and
1462 ((parameter_three is null and parameterThreeIn is null) or
1463 (parameter_three = parameterThreeIn)) and
1464 ((include_lower_limit is null and includeLowerLimitIn is null) or
1465 (include_lower_limit = includeLowerLimitIn)) and
1466 ((include_upper_limit is null and includeUpperLimitIn is null) or
1467 (include_upper_limit = includeUpperLimitIn)) and
1468 sysdate between start_date and
1469 nvl(end_date - ame_util.oneSecond, sysdate) ;
1470 cursor startDateCursor is
1471 select start_date
1472 from ame_conditions
1473 where
1474 condition_id = conditionIdIn and
1475 sysdate between start_date and
1476 nvl(end_date - ame_util.oneSecond, sysdate)
1477 for update;
1478 attributeId integer;
1479 attributeType ame_attributes.attribute_type%type;
1480 conditionId integer;
1481 conditionsExistsException exception;
1482 conditionKey ame_conditions.condition_key%type;
1483 conditionType ame_conditions.condition_type%type;
1484 currentUserId integer;
1485 endDate date;
1486 errorCode integer;
1487 errorMessage ame_util.longestStringType;
1488 newStartDate date;
1489 objectVersionNoDataException exception;
1490 startDate date;
1491 stringValueList ame_util.longestStringList;
1492 tempCount integer;
1493 tempIndex integer;
1494 tempStringValue ame_string_values.string_value%type;
1495 tempStringValueList ame_util.longestStringList;
1496 processingDate date;
1497 begin
1498 if processingDateIn is null then
1499 processingDate := sysdate;
1500 else
1501 processingDate := processingDateIn;
1502 end if;
1503 open startDateCursor;
1504 fetch startDateCursor into startDate;
1505 if startDateCursor%notfound then
1506 raise objectVersionNoDataException;
1507 end if;
1508 if(typeIn is null) then
1509 conditionType := getConditionType(conditionIdIn => conditionIdIn);
1510 else
1511 conditionType := typeIn;
1512 end if;
1513 conditionKey := getConditionKey(conditionIdIn => conditionIdIn);
1514 select count(*)
1515 into tempCount
1516 from ame_conditions
1517 where
1518 condition_type = typeIn and
1519 attribute_id = attributeIdIn and
1520 ((parameter_one is null and parameterOneIn is null) or
1521 (parameter_one = parameterOneIn)) and
1522 ((parameter_two is null and parameterTwoIn is null) or
1523 (parameter_two = parameterTwoIn)) and
1524 ((parameter_three is null and parameterThreeIn is null) or
1525 (parameter_three = parameterThreeIn)) and
1526 ((include_lower_limit is null and includeLowerLimitIn is null) or
1527 (include_lower_limit = includeLowerLimitIn)) and
1528 ((include_upper_limit is null and includeUpperLimitIn is null) or
1529 (include_upper_limit = includeUpperLimitIn)) and
1530 sysdate between start_date and
1531 nvl(end_date - ame_util.oneSecond, sysdate) ;
1532 if(tempCount > 0) then
1533 if(conditionType = ame_util.listModConditionType) then
1534 raise conditionsExistsException;
1535 else
1536 attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1537 if(attributeType = ame_util.stringAttributeType) then
1538 stringValueList := stringValuesIn;
1539 ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
1540 for tempCondition in conditionCursor(typeIn => typeIn,
1541 attributeIdIn => attributeIdIn,
1542 parameterOneIn => parameterOneIn,
1543 parameterTwoIn => parameterTwoIn,
1544 parameterThreeIn => parameterThreeIn,
1545 includeLowerLimitIn => includeLowerLimitIn,
1546 includeUpperLimitIn => includeUpperLimitIn) loop
1547 getStringValueList(conditionIdIn => tempCondition.condition_id,
1548 stringValueListOut => tempStringValueList);
1549 if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
1550 longestStringList2InOut => tempStringValueList)) then
1551 raise conditionsExistsException;
1552 end if;
1553 end loop;
1554 else
1555 raise conditionsExistsException;
1556 end if;
1557 end if;
1558 end if;
1559 if(attributeIdIn is null) then
1560 attributeId := getAttributeId(conditionIdIn => conditionIdIn);
1561 else
1562 attributeId := attributeIdIn;
1563 end if;
1564 currentUserId := ame_util.getCurrentUserId;
1565 if versionStartDateIn = startDate then
1566 endDate := processingDate ;
1567 newStartDate := processingDate;
1568 update ame_conditions
1569 set
1570 last_updated_by = currentUserId,
1571 last_update_date = endDate,
1572 last_update_login = currentUserId,
1573 end_date = endDate
1574 where
1575 condition_id = conditionIdIn and
1576 sysdate between start_date and
1577 nvl(end_date - ame_util.oneSecond, sysdate) ;
1578 if conditionType <> ame_util.listModConditionType then
1579 if(attributeType = ame_util.stringAttributeType) then
1580 delete from ame_string_values where condition_id = conditionIdIn;
1581 end if;
1582 end if;
1583 /* (The new function does a commit.) */
1584 conditionId := new(typeIn => conditionType,
1585 attributeIdIn => attributeId,
1586 conditionKeyIn => conditionKey,
1587 attributeTypeIn => attributeType,
1588 parameterOneIn => parameterOneIn,
1589 parameterTwoIn => parameterTwoIn,
1590 parameterThreeIn => parameterThreeIn,
1591 includeLowerLimitIn => includeLowerLimitIn,
1592 includeUpperLimitIn => includeUpperLimitIn,
1593 stringValueListIn => stringValuesIn,
1594 newStartDateIn => newStartDate,
1595 conditionIdIn => conditionIdIn);
1596 else
1597 close startDateCursor;
1598 raise ame_util.objectVersionException;
1599 end if;
1600 close startDateCursor;
1601 exception
1602 when ame_util.objectVersionException then
1603 rollback;
1604 if(startDateCursor%isOpen) then
1605 close startDateCursor;
1606 end if;
1607 errorCode := -20001;
1608 errorMessage :=
1609 ame_util.getMessage(applicationShortNameIn => 'PER',
1610 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1611 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1612 routineNameIn => 'change',
1613 exceptionNumberIn => errorCode,
1614 exceptionStringIn => errorMessage);
1615 raise_application_error(errorCode,
1616 errorMessage);
1617 when objectVersionNoDataException then
1618 rollback;
1619 if(startDateCursor%isOpen) then
1620 close startDateCursor;
1621 end if;
1622 errorCode := -20001;
1623 errorMessage :=
1624 ame_util.getMessage(applicationShortNameIn => 'PER',
1625 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1626 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1627 routineNameIn => 'change',
1628 exceptionNumberIn => errorCode,
1629 exceptionStringIn => errorMessage);
1630 raise_application_error(errorCode,
1631 errorMessage);
1632 when conditionsExistsException then
1633 rollback;
1634 errorCode := -20001;
1635 errorMessage :=
1636 ame_util.getMessage(applicationShortNameIn => 'PER',
1637 messageNameIn => 'AME_400191_CON_EXISTS_NEW');
1638 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1639 routineNameIn => 'change',
1640 exceptionNumberIn => errorCode,
1641 exceptionStringIn => errorMessage);
1642 raise_application_error(errorCode,
1643 errorMessage);
1644 when others then
1645 rollback;
1646 if(startDateCursor%isOpen) then
1647 close startDateCursor;
1648 end if;
1649 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1650 routineNameIn => 'change',
1651 exceptionNumberIn => sqlcode,
1652 exceptionStringIn => '(condition ID ' ||
1653 conditionIdIn||
1654 ') ' ||
1655 sqlerrm);
1656 raise;
1657 end change;
1658 procedure getAllProperties(conditionIdIn in integer,
1659 conditionTypeOut out nocopy varchar2,
1660 conditionKeyOut out nocopy varchar2,
1661 attributeIdOut out nocopy integer,
1662 parameterOneOut out nocopy varchar2,
1663 parameterTwoOut out nocopy varchar2,
1664 parameterThreeOut out nocopy varchar2,
1665 includeLowerLimitOut out nocopy varchar2,
1666 includeUpperLimitOut out nocopy varchar2) as
1667 begin
1668 select
1669 condition_type,
1670 condition_key,
1671 attribute_id,
1672 parameter_one,
1673 parameter_two,
1674 parameter_three,
1675 include_lower_limit,
1676 include_upper_limit
1677 into
1678 conditionTypeOut,
1679 conditionKeyOut,
1680 attributeIdOut,
1681 parameterOneOut,
1682 parameterTwoOut,
1683 parameterThreeOut,
1684 includeLowerLimitOut,
1685 includeUpperLimitOut
1686 from ame_conditions
1687 where
1688 condition_id = conditionIdIn and
1689 sysdate between start_date and
1690 nvl(end_date - ame_util.oneSecond, sysdate) ;
1691 exception
1692 when others then
1693 rollback;
1694 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1695 routineNameIn => 'getAllProperties',
1696 exceptionNumberIn => sqlcode,
1697 exceptionStringIn => '(condition ID ' ||
1698 conditionIdIn||
1699 ') ' ||
1700 sqlerrm);
1701 conditionTypeOut := null;
1702 conditionKeyOut := null;
1703 attributeIdOut := null;
1704 parameterOneOut := null;
1705 parameterTwoOut := null;
1706 parameterThreeOut := null;
1707 includeLowerLimitOut := null;
1708 includeUpperLimitOut := null;
1709 raise;
1710 end getAllProperties;
1711 procedure getAuthPreConditions(applicationIdIn in integer,
1712 itemClassIdIn in integer,
1713 conditionIdsOut out nocopy ame_util.stringList,
1714 conditionTypesOut out nocopy ame_util.stringList,
1715 attributeIdsOut out nocopy ame_util.stringList,
1716 attributeNamesOut out nocopy ame_util.stringList,
1717 attributeTypesOut out nocopy ame_util.stringList,
1718 conditionDescriptionsOut out nocopy ame_util.longStringList) as
1719 cursor conditionCursor(applicationIdIn in integer,
1720 itemClassIdIn in integer) is
1721 select
1722 ame_conditions.condition_id id,
1723 ame_conditions.condition_type,
1724 ame_attributes.attribute_id,
1725 ame_attributes.name,
1726 ame_attributes.attribute_type
1727 from ame_conditions,
1728 ame_attributes,
1729 ame_attribute_usages,
1730 ame_item_class_usages
1731 where
1732 ame_attribute_usages.application_id = ame_item_class_usages.application_id and
1733 ame_item_class_usages.application_id = applicationIdIn and
1734 ame_item_class_usages.item_class_id = itemClassIdIn and
1735 ame_conditions.attribute_id = ame_attribute_usages.attribute_id and
1736 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1737 ame_attributes.item_class_id = itemClassIdIn and
1738 ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1739 sysdate between ame_conditions.start_date and
1740 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1741 sysdate between ame_attributes.start_date and
1742 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1743 sysdate between ame_attribute_usages.start_date and
1744 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1745 sysdate between ame_item_class_usages.start_date and
1746 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1747 order by ame_conditions.condition_type,
1748 ame_attributes.attribute_type,
1749 ame_attributes.name;
1750 tempOutputIndex integer;
1751 conditionId integer;
1752 conditionType ame_conditions.condition_type%type;
1753 attributeId integer;
1754 attributeName ame_attributes.name%type;
1755 attributeType ame_attributes.attribute_type%type;
1756 begin
1757 tempOutputIndex := 1;
1758 open conditionCursor(applicationIdIn => applicationIdIn,
1759 itemClassIdIn => itemClassIdIn);
1760 loop
1761 fetch conditionCursor into conditionId,
1762 conditionType,
1763 attributeId,
1764 attributeName,
1765 attributeType;
1766 exit when conditionCursor%notfound;
1767 /* The explicit conversions below lets nocopy work. */
1768 conditionIdsOut(tempOutputIndex) := to_char(conditionId);
1769 conditionTypesOut(tempOutputIndex) := conditionType;
1770 attributeIdsOut(tempOutputIndex) := to_char(attributeId);
1771 attributeNamesOut(tempOutputIndex) := attributeName;
1772 attributeTypesOut(tempOutputIndex) := attributeType;
1773 conditionDescriptionsOut(tempOutputIndex) := ame_condition_pkg.getDescription(conditionId);
1774 tempOutputIndex := tempOutputIndex + 1;
1775 end loop;
1776 close conditionCursor;
1777 exception
1778 when others then
1779 if conditionCursor%isopen then
1780 close conditionCursor;
1781 end if;
1782 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1783 routineNameIn => 'getAuthPreConditions',
1784 exceptionNumberIn => sqlcode,
1785 exceptionStringIn => '(application ID ' ||
1786 applicationIdIn||
1787 ') ' ||
1788 sqlerrm);
1789 conditionIdsOut := ame_util.emptyStringList;
1790 conditionTypesOut := ame_util.emptyStringList;
1791 attributeIdsOut := ame_util.emptyStringList;
1792 attributeNamesOut := ame_util.emptyStringList;
1793 attributeTypesOut := ame_util.emptyStringList;
1794 conditionDescriptionsOut := ame_util.emptyLongStringList;
1795 raise;
1796 end getAuthPreConditions;
1797 procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798 conditionTypeIn in varchar2,
1799 lineItemIn in varchar2 default ame_util.booleanFalse,
1800 conditionIdsOut out nocopy ame_util.stringList,
1801 conditionDescriptionsOut out nocopy ame_util.longStringList) as
1802 cursor conditionCursor(attributeIdIn in integer,
1803 conditionTypeIn in varchar2,
1804 lineItemIn in varchar2) is
1805 select
1806 condition_id id,
1807 ame_condition_pkg.getDescription(condition_id) description
1808 from ame_conditions,
1809 ame_attributes
1810 where
1811 ame_conditions.attribute_id = ame_attributes.attribute_id and
1812 nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1813 ame_attributes.attribute_id = attributeIdIn and
1814 condition_type = conditionTypeIn and
1815 sysdate between ame_conditions.start_date and
1816 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1817 sysdate between ame_attributes.start_date and
1818 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1819 order by description;
1820 tempOutputIndex integer;
1821 upperLimit integer;
1822 begin
1823 upperLimit := attributeIdsIn.count;
1824 tempOutputIndex := 1;
1825 for tempInputIndex in 1..upperLimit loop /* ignore first value */
1826 for tempCondition in conditionCursor(attributeIdIn => attributeIdsIn(tempInputIndex),
1827 conditionTypeIn => conditionTypeIn,
1828 lineItemIn => lineItemIn) loop
1829 /* The explicit conversion below lets nocopy work. */
1830 conditionIdsOut(tempOutputIndex) := to_char(tempCondition.id);
1831 conditionDescriptionsOut(tempOutputIndex) := tempCondition.description;
1832 tempOutputIndex := tempOutputIndex + 1;
1833 end loop;
1834 end loop;
1835 exception
1836 when others then
1837 rollback;
1838 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1839 routineNameIn => 'getAttributesConditions',
1840 exceptionNumberIn => sqlcode,
1841 exceptionStringIn => sqlerrm);
1842 conditionIdsOut := ame_util.emptyStringList;
1843 conditionDescriptionsOut := ame_util.emptyLongStringList;
1844 raise;
1845 end getAttributesConditions;
1846 procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1847 conditionTypeIn in varchar2,
1848 itemClassIdIn in integer,
1849 ruleIdIn in integer,
1850 conditionIdsOut out nocopy ame_util.stringList,
1851 conditionDescriptionsOut out nocopy ame_util.longStringList) as
1852 cursor conditionCursor(attributeIdIn in integer,
1853 conditionTypeIn in varchar2,
1854 itemClassIdIn in integer) is
1855 select
1856 ame_conditions.condition_id id,
1857 ame_condition_pkg.getDescription(ame_conditions.condition_id) description
1858 from ame_conditions,
1859 ame_attributes
1860 where
1861 ame_conditions.attribute_id = ame_attributes.attribute_id and
1862 ame_attributes.attribute_id = attributeIdIn and
1863 condition_type = conditionTypeIn and
1864 ame_attributes.item_class_id = itemClassIdIn and
1865 sysdate between ame_conditions.start_date and
1866 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1867 sysdate between ame_attributes.start_date and
1868 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1869 order by description;
1870 tempOutputIndex integer;
1871 upperLimit integer;
1872 begin
1873 upperLimit := attributeIdsIn.count;
1874 tempOutputIndex := 1;
1875 for tempInputIndex in 1..upperLimit loop /* ignore first value */
1876 for tempCondition in conditionCursor(attributeIdIn => attributeIdsIn(tempInputIndex),
1877 conditionTypeIn => conditionTypeIn,
1878 itemClassIdIn => itemClassIdIn) loop
1879 /* The explicit conversion below lets nocopy work. */
1880 if not isConditionUsage(ruleIdIn => ruleIdIn,
1881 conditionIdIn => tempCondition.Id) then
1882 conditionIdsOut(tempOutputIndex) := to_char(tempCondition.id);
1883 conditionDescriptionsOut(tempOutputIndex) := tempCondition.description;
1884 tempOutputIndex := tempOutputIndex + 1;
1885 end if;
1886 end loop;
1887 end loop;
1888 exception
1889 when others then
1890 rollback;
1891 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1892 routineNameIn => 'getAttributesConditions1',
1893 exceptionNumberIn => sqlcode,
1894 exceptionStringIn => sqlerrm);
1895 conditionIdsOut := ame_util.emptyStringList;
1896 conditionDescriptionsOut := ame_util.emptyLongStringList;
1897 raise;
1898 end getAttributesConditions1;
1899 procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1900 conditionTypeIn in varchar2,
1901 itemClassIdIn in integer,
1902 lineItemIn in varchar2 default ame_util.booleanFalse,
1903 conditionIdsOut out nocopy ame_util.stringList,
1904 conditionDescriptionsOut out nocopy ame_util.longStringList) as
1905 cursor conditionCursor(attributeIdIn in integer,
1906 conditionTypeIn in varchar2,
1907 lineItemIn in varchar2,
1908 itemClassIdIn in integer) is
1909 select
1910 condition_id id,
1911 ame_condition_pkg.getDescription(condition_id) description
1912 from ame_conditions,
1913 ame_attributes
1914 where
1915 ame_conditions.attribute_id = ame_attributes.attribute_id and
1916 nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1917 ame_attributes.attribute_id = attributeIdIn and
1918 ame_attributes.item_class_id = itemClassIdIn and
1919 condition_type = conditionTypeIn and
1920 sysdate between ame_conditions.start_date and
1921 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1922 sysdate between ame_attributes.start_date and
1923 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1924 order by description;
1925 tempOutputIndex integer;
1926 upperLimit integer;
1927 begin
1928 upperLimit := attributeIdsIn.count;
1929 tempOutputIndex := 1;
1930 for tempInputIndex in 1..upperLimit loop /* ignore first value */
1931 for tempCondition in conditionCursor(attributeIdIn => attributeIdsIn(tempInputIndex),
1932 conditionTypeIn => conditionTypeIn,
1933 lineItemIn => lineItemIn,
1934 itemClassIdIn => itemClassIdIn) loop
1935 /* The explicit conversion below lets nocopy work. */
1936 conditionIdsOut(tempOutputIndex) := to_char(tempCondition.id);
1937 conditionDescriptionsOut(tempOutputIndex) := tempCondition.description;
1938 tempOutputIndex := tempOutputIndex + 1;
1939 end loop;
1940 end loop;
1941 exception
1942 when others then
1943 rollback;
1944 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1945 routineNameIn => 'getAttributesConditions2',
1946 exceptionNumberIn => sqlcode,
1947 exceptionStringIn => sqlerrm);
1948 conditionIdsOut := ame_util.emptyStringList;
1949 conditionDescriptionsOut := ame_util.emptyLongStringList;
1950 raise;
1951 end getAttributesConditions2;
1952 procedure getApplicationsUsingCondition(conditionIdIn in integer,
1953 applicationIdIn in integer,
1954 applicationNamesOut out nocopy ame_util.stringList) as
1955 upperLimit integer;
1956 cursor getApplicationsCursor(conditionIdIn in integer,
1957 applicationIdIn in integer) is
1958 select distinct ame_calling_apps.application_name
1959 from
1960 ame_rules,
1961 ame_rule_usages,
1962 ame_calling_apps,
1963 ame_condition_usages
1964 where
1965 ame_rules.rule_id = ame_rule_usages.rule_id and
1966 ame_rules.rule_id = ame_condition_usages.rule_id and
1967 ame_rule_usages.item_id = ame_calling_apps.application_id and
1968 ame_rule_usages.item_id <> applicationIdIn and
1969 ame_condition_usages.condition_id = conditionIdIn and
1970 ((sysdate between ame_rules.start_date and
1971 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
1972 (sysdate < ame_rules.start_date and
1973 ame_rules.start_date < nvl(ame_rules.end_date,
1974 ame_rules.start_date + ame_util.oneSecond))) and
1975 ((sysdate between ame_rule_usages.start_date and
1976 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1977 (sysdate < ame_rule_usages.start_date and
1978 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1979 ame_rule_usages.start_date + ame_util.oneSecond))) and
1980 ((sysdate between ame_condition_usages.start_date and
1981 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1982 (sysdate < ame_condition_usages.start_date and
1983 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1984 ame_condition_usages.start_date + ame_util.oneSecond))) and
1985 sysdate between ame_calling_apps.start_date and
1986 nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
1987 order by ame_calling_apps.application_name;
1988 tempIndex integer;
1989 begin
1990 tempIndex := 1;
1991 for getApplicationsRec in getApplicationsCursor(conditionIdIn => conditionIdIn,
1992 applicationIdIn => applicationIdIn) loop
1993 applicationNamesOut(tempIndex) := getApplicationsRec.application_name;
1994 tempIndex := tempIndex + 1;
1995 end loop;
1996 exception
1997 when others then
1998 rollback;
1999 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2000 routineNameIn => 'getApplicationsUsingCondition',
2001 exceptionNumberIn => sqlcode,
2002 exceptionStringIn => '(condition ID ' ||
2003 conditionIdIn||
2004 ') ' ||
2005 sqlerrm);
2006 applicationNamesOut := ame_util.emptyStringList;
2007 raise;
2008 end getApplicationsUsingCondition;
2009 procedure getDescriptions(conditionIdsIn in ame_util.idList,
2010 descriptionsOut out nocopy ame_util.longStringList) as
2011 upperLimit integer;
2012 begin
2013 upperLimit := conditionIdsIn.count;
2014 for tempIndex in 1..upperLimit loop
2015 descriptionsOut(tempIndex) := getDescription(conditionIdIn => conditionIdsIn(tempIndex));
2016 end loop;
2017 exception
2018 when others then
2019 rollback;
2020 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2021 routineNameIn => 'getDescriptions',
2022 exceptionNumberIn => sqlcode,
2023 exceptionStringIn => sqlerrm);
2024 descriptionsOut := ame_util.emptyLongStringList;
2025 raise;
2026 end getDescriptions;
2027 procedure getDetailUrls(applicationIdIn in integer,
2028 conditionIdsIn in ame_util.idList,
2029 detailUrlsOut out nocopy ame_util.longStringList) as
2030 conditionIdCount integer;
2031 begin
2032 conditionIdCount := conditionIdsIn.count;
2033 for i in 1..conditionIdCount loop
2034 if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2035 detailUrlsOut(i) := null;
2036 else
2037 if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2038 detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2039 'ame_conditions_ui.getDetails?conditionIdIn=' ||
2040 conditionIdsIn(i) ||
2041 '&applicationIdIn=' ||
2042 applicationIdIn);
2043 else
2044 detailUrlsOut(i) := null;
2045 end if;
2046 end if;
2047 end loop;
2048 exception
2049 when others then
2050 rollback;
2051 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2052 routineNamein => 'getDetailUrls',
2053 exceptionNumberIn => sqlcode,
2054 exceptionStringIn => sqlerrm);
2055 detailUrlsOut := ame_util.emptyLongStringList;
2056 raise;
2057 end getDetailUrls;
2058 procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2059 parameterOneOut out nocopy ame_util.stringList,
2060 parameterTwoOut out nocopy ame_util.stringList) as
2061 cursor lMConditionCursor is
2062 select
2063 condition_id,
2064 parameter_one,
2065 parameter_two
2066 from ame_conditions
2067 where
2068 condition_type = ame_util.listModConditionType and
2069 sysdate between start_date and
2070 nvl(end_date - ame_util.oneSecond, sysdate) ;
2071 tempIndex integer;
2072 begin
2073 tempIndex := 1;
2074 for tempLMCondition in lMConditionCursor loop
2075 conditionIdOut(tempIndex) := tempLMCondition.condition_id;
2076 parameterOneOut(tempIndex) := tempLMCondition.parameter_one;
2077 parameterTwoOut(tempIndex) := tempLMCondition.parameter_two;
2078 tempIndex := tempIndex + 1;
2079 end loop;
2080 exception
2081 when others then
2082 rollback;
2083 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2084 routineNameIn => 'getLMConditions',
2085 exceptionNumberIn => sqlcode,
2086 exceptionStringIn => sqlerrm);
2087 conditionIdOut := ame_util.emptyIdList;
2088 parameterOneOut := ame_util.emptyStringList;
2089 parameterTwoOut := ame_util.emptyStringList;
2090 raise;
2091 end getLMConditions;
2092 procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2093 descriptionsOut out nocopy ame_util.longStringList) as
2094 cursor LMConditionCursor is
2095 select
2096 condition_id,
2097 parameter_one,
2098 parameter_two
2099 from
2100 ame_conditions
2101 where
2102 condition_type = ame_util.listModConditionType and
2103 sysdate between start_date and
2104 nvl(end_date - ame_util.oneSecond, sysdate);
2105 tempIndex integer;
2106 approverDesc ame_util.longStringType;
2107 approverValid boolean;
2108 begin
2109 tempIndex := 1;
2110 for LMConditionRec in LMConditionCursor loop
2111 ame_approver_type_pkg.getApproverDescAndValidity(
2112 nameIn => lMConditionRec.parameter_two,
2113 descriptionOut => approverDesc,
2114 validityOut => approverValid);
2115 if(approverValid) then
2116 conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2117 if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2118 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2119 else
2120 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2121 end if;
2122 tempIndex := tempIndex + 1;
2123 end if;
2124 end loop;
2125 exception
2126 when others then
2127 rollback;
2128 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2129 routineNameIn => 'getLMDescriptions',
2130 exceptionNumberIn => sqlcode,
2131 exceptionStringIn => sqlerrm);
2132 conditionIdsOut := ame_util.emptyStringList;
2133 descriptionsOut := ame_util.emptyLongStringList;
2134 raise;
2135 end getLMDescriptions;
2136 procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2137 descriptionsOut out nocopy ame_util.longStringList) as
2138 cursor LMConditionCursor is
2139 select
2140 ame_conditions.condition_id condition_id,
2141 ame_conditions.parameter_one parameter_one,
2142 ame_conditions.parameter_two parameter_two
2143 from
2144 ame_conditions
2145 where
2146 ame_conditions.condition_type = ame_util.listModConditionType and
2147 sysdate between ame_conditions.start_date and
2148 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2149 isApproverValid boolean;
2150 tempDescription ame_util.longStringType;
2151 tempIndex integer;
2152 begin
2153 tempIndex := 1;
2154 for LMConditionRec in LMConditionCursor loop
2155 ame_approver_type_pkg.getApproverDescAndValidity(
2156 nameIn => lMConditionRec.parameter_two,
2157 descriptionOut => tempDescription,
2158 validityOut => isApproverValid);
2159 if(isApproverValid and
2160 ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2161 = ame_util.perOrigSystem) then
2162 conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2163 if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2164 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2165 else
2166 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2167 end if;
2168 tempIndex := tempIndex + 1;
2169 end if;
2170 end loop;
2171 exception
2172 when others then
2173 rollback;
2174 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2175 routineNameIn => 'getLMDescriptions2',
2176 exceptionNumberIn => sqlcode,
2177 exceptionStringIn => sqlerrm);
2178 conditionIdsOut := ame_util.emptyStringList;
2179 descriptionsOut := ame_util.emptyLongStringList;
2180 raise;
2181 end getLMDescriptions2;
2182 procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2183 conditionIdsOut out nocopy ame_util.stringList,
2184 descriptionsOut out nocopy ame_util.longStringList) as
2185 cursor LMConditionCursor(lmApproverTypeIn in varchar2) is
2186 select
2187 ame_conditions.condition_id condition_id,
2188 ame_conditions.parameter_two parameter_two
2189 from
2190 ame_conditions
2191 where
2192 ame_conditions.condition_type = ame_util.listModConditionType and
2193 ame_conditions.parameter_one = lmApproverTypeIn and
2194 sysdate between ame_conditions.start_date and
2195 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2196 isApproverValid boolean;
2197 tempDescription ame_util.longStringType;
2198 tempIndex integer;
2199 begin
2200 tempIndex := 1;
2201 for LMConditionRec in LMConditionCursor(lmApproverTypeIn => lmApproverTypeIn) loop
2202 ame_approver_type_pkg.getApproverDescAndValidity(
2203 nameIn => lMConditionRec.parameter_two,
2204 descriptionOut => tempDescription,
2205 validityOut => isApproverValid);
2206 if(isApproverValid and
2207 ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2208 = ame_util.perOrigSystem) then
2209 conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2210 if(lmApproverTypeIn = ame_util.finalApprover) then
2211 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2212 else
2213 descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2214 end if;
2215 tempIndex := tempIndex + 1;
2216 end if;
2217 end loop;
2218 exception
2219 when others then
2220 rollback;
2221 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2222 routineNameIn => 'getLMDescriptions3',
2223 exceptionNumberIn => sqlcode,
2224 exceptionStringIn => sqlerrm);
2225 conditionIdsOut := ame_util.emptyStringList;
2226 descriptionsOut := ame_util.emptyLongStringList;
2227 raise;
2228 end getLMDescriptions3;
2229 procedure getStringValueList(conditionIdIn in integer,
2230 stringValueListOut out nocopy ame_util.longestStringList) as
2231 cursor stringValueCursor(conditionIdIn in integer) is
2232 select string_value
2233 from ame_string_values
2234 where condition_id = conditionIdIn and
2235 sysdate between start_date and
2236 nvl(end_date - ame_util.oneSecond, sysdate)
2237 order by string_value asc;
2238 attributeTypeException exception;
2239 errorCode integer;
2240 errorMessage ame_util.longestStringType;
2241 tempIndex integer;
2242 begin
2243 if(getAttributeType(conditionIdIn => conditionIdIn) <> ame_util.stringAttributeType) then
2244 raise attributeTypeException;
2245 end if;
2246 tempIndex := 1;
2247 for tempStringValue in stringValueCursor(conditionIdIn) loop
2248 stringValueListOut(tempIndex) := tempStringValue.string_value;
2249 tempIndex := tempIndex + 1;
2250 end loop;
2251 exception
2252 when attributeTypeException then
2253 rollback;
2254 errorCode := -20001;
2255 errorMessage :=
2256 ame_util.getMessage(applicationShortNameIn => 'PER',
2257 messageNameIn => 'AME_400192_CON_STR_VAL_NOT_DEF');
2258 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2259 routineNameIn => 'getStringValueList',
2260 exceptionNumberIn => errorCode,
2261 exceptionStringIn => errorMessage);
2262 stringValueListOut := ame_util.emptyLongestStringList;
2263 raise_application_error(errorCode,
2264 errorMessage);
2265 when others then
2266 rollback;
2267 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2268 routineNameIn => 'getStringValueList',
2269 exceptionNumberIn => sqlcode,
2270 exceptionStringIn => '(condition ID ' ||
2271 conditionIdIn||
2272 ') ' ||
2273 sqlerrm);
2274 stringValueListOut := ame_util.emptyLongestStringList;
2275 raise;
2276 end getStringValueList;
2277 procedure remove(conditionIdIn in integer,
2278 versionStartDateIn in date,
2279 processingDateIn in date default null) as
2280 cursor startDateCursor is
2281 select start_date
2282 from ame_conditions
2283 where
2284 condition_id = conditionIdIn and
2285 sysdate between start_date and
2286 nvl(end_date - ame_util.oneSecond, sysdate)
2287 for update;
2288 attributeType ame_attributes.attribute_type%type;
2289 conditionType ame_conditions.condition_type%type;
2290 currentUserId integer;
2291 errorCode integer;
2292 errorMessage ame_util.longestStringType;
2293 inUseException exception;
2294 objectVersionNoDataException exception;
2295 startDate date;
2296 processingDate date;
2297 begin
2298 if processingDateIn is null then
2299 processingDate := sysdate;
2300 else
2301 processingDate := processingDateIn;
2302 end if;
2303 open startDateCursor;
2304 fetch startDateCursor into startDate;
2305 if startDateCursor%notfound then
2306 raise objectVersionNoDataException;
2307 end if;
2308 if(isInUse(conditionIdIn)) then
2309 raise inUseException;
2310 end if;
2311 currentUserId := ame_util.getCurrentUserId;
2312 conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2313 if versionStartDateIn = startDate then
2314 if conditionType <> ame_util.listModConditionType then
2315 attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2316 if(attributeType = ame_util.stringAttributeType) then
2317 update ame_string_values
2318 set
2319 last_updated_by = currentUserId,
2320 last_update_date = processingDate,
2321 last_update_login = currentUserId,
2322 end_date = processingDate
2323 where
2324 condition_id = conditionIdIn and
2325 processingDate between start_date and
2326 nvl(end_date - ame_util.oneSecond, processingDate) ;
2327 end if;
2328 end if;
2329 update ame_conditions
2330 set
2331 last_updated_by = currentUserId,
2332 last_update_date = processingDate,
2333 last_update_login = currentUserId,
2334 end_date = processingDate
2335 where
2336 condition_id = conditionIdIn and
2337 processingDate between start_date and
2338 nvl(end_date - ame_util.oneSecond, processingDate) ;
2339 commit;
2340 else
2341 close startDateCursor;
2342 raise ame_util.objectVersionException;
2343 end if;
2344 close startDateCursor;
2345 exception
2346 when ame_util.objectVersionException then
2347 rollback;
2348 if(startDateCursor%isOpen) then
2349 close startDateCursor;
2350 end if;
2351 errorCode := -20001;
2352 errorMessage :=
2353 ame_util.getMessage(applicationShortNameIn => 'PER',
2354 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2355 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2356 routineNameIn => 'remove',
2357 exceptionNumberIn => errorCode,
2358 exceptionStringIn => errorMessage);
2359 raise_application_error(errorCode,
2360 errorMessage);
2361 when objectVersionNoDataException then
2362 rollback;
2363 if(startDateCursor%isOpen) then
2364 close startDateCursor;
2365 end if;
2366 errorCode := -20001;
2367 errorMessage :=
2368 ame_util.getMessage(applicationShortNameIn => 'PER',
2369 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2370 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2371 routineNameIn => 'remove',
2372 exceptionNumberIn => errorCode,
2373 exceptionStringIn => errorMessage);
2374 raise_application_error(errorCode,
2375 errorMessage);
2376 when inUseException then
2377 rollback;
2378 errorCode := -20001;
2379 errorMessage :=
2380 ame_util.getMessage(applicationShortNameIn => 'PER',
2381 messageNameIn => 'AME_400193_CON_IN USE');
2382 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2383 routineNameIn => 'remove',
2384 exceptionNumberIn => errorCode,
2385 exceptionStringIn => errorMessage);
2386 raise_application_error(errorCode,
2387 errorMessage);
2388 when others then
2389 rollback;
2390 if(startDateCursor%isOpen) then
2391 close startDateCursor;
2392 end if;
2393 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2394 routineNameIn => 'remove',
2395 exceptionNumberIn => sqlcode,
2396 exceptionStringIn => '(condition ID ' ||
2397 conditionIdIn||
2398 ') ' ||
2399 sqlerrm);
2400 raise;
2401 end remove;
2402 procedure removeConditionUsage(ruleIdIn in integer,
2403 conditionIdIn in integer,
2404 newConditionIdIn in integer default null,
2405 finalizeIn in boolean default true,
2406 processingDateIn in date default null) as
2407 actionIdList ame_util.idList;
2408 conditionIdList ame_util.idList;
2409 currentUserId integer;
2410 errorCode integer;
2411 errorMessage ame_util.longestStringType;
2412 inUseException exception;
2413 processingDate date;
2414 ruleType ame_rules.rule_type%type;
2415 tempIndex integer;
2416 begin
2417 if processingDateIn is null then
2418 processingDate := sysdate;
2419 else
2420 processingDate := processingDateIn;
2421 end if;
2422 currentUserId := ame_util.getCurrentUserId;
2423 update ame_condition_usages
2424 set
2425 last_updated_by = currentUserId,
2426 last_update_date = processingDate,
2427 last_update_login = currentUserId,
2428 end_date = processingDate
2429 where
2430 condition_id = conditionIdIn and
2431 rule_id = ruleIdIn and
2432 ((processingDate between start_date and
2433 nvl(end_date - ame_util.oneSecond, processingDate)) or
2434 (processingDate < start_date and
2435 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2436 if(newConditionIdIn is not null) then
2437 /* The list modification condition has been changed. Check to see if
2438 changing the condition resulted in a rule duplication. */
2439 ame_rule_pkg.getConditionIds(ruleIdIn => ruleIdIn,
2440 conditionIdListOut => conditionIdList);
2441 tempIndex := (conditionIdList.count + 1);
2442 conditionIdList(tempIndex) := newConditionIdIn;
2443 ame_rule_pkg.getActionIds(ruleIdIn => ruleIdIn,
2444 actionIdListOut => actionIdList);
2445 ruleType := ame_rule_pkg.getType(ruleIdIn => ruleIdIn);
2446 if(ame_rule_pkg.ruleExists(typeIn => ruleType,
2447 conditionIdListIn => conditionIdList,
2448 actionIdListIn => actionIdList)) then
2449 raise inUseException;
2450 end if;
2451 end if;
2452 if(finalizeIn) then
2453 commit;
2454 end if;
2455 exception
2456 when inUseException then
2457 rollback;
2458 errorCode := -20001;
2459 errorMessage :=
2460 ame_util.getMessage(applicationShortNameIn => 'PER',
2461 messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
2462 ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2463 routineNamein => 'removeConditionUsage',
2464 exceptionNumberIn => errorCode,
2465 exceptionStringIn => errorMessage);
2466 raise_application_error(errorCode,
2467 errorMessage);
2468 when others then
2469 rollback;
2470 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2471 routineNameIn => 'removeConditionUsage',
2472 exceptionNumberIn => sqlcode,
2473 exceptionStringIn => '(condition ID ' ||
2474 conditionIdIn||
2475 ') ' ||
2476 sqlerrm);
2477 raise;
2478 end removeConditionUsage;
2479 procedure removeStringValue(conditionIdIn in integer,
2480 versionStartDateIn in date,
2481 stringValueListIn in ame_util.longestStringList,
2482 processingDateIn in date default null) as
2483 cursor startDateCursor is
2484 select start_date
2485 from ame_conditions
2486 where
2487 condition_id = conditionIdIn and
2488 sysdate between start_date and
2489 nvl(end_date - ame_util.oneSecond, sysdate)
2490 for update;
2491 attributeId ame_conditions.attribute_id%type;
2492 conditionId ame_conditions.condition_id%type;
2493 conditionKey ame_conditions.condition_key%type;
2494 conditionType ame_conditions.condition_type%type;
2495 currentUserId integer;
2496 errorCode integer;
2497 errorMessage ame_util.longestStringType;
2498 includeLowerLimit ame_conditions.include_lower_limit%type;
2499 includeUpperLimit ame_conditions.include_upper_limit%type;
2500 objectVersionNoDataException exception;
2501 parameterOne ame_conditions.parameter_one%type;
2502 parameterTwo ame_conditions.parameter_two%type;
2503 parameterThree ame_conditions.parameter_three%type;
2504 startDate date;
2505 stringCount integer;
2506 processingDate date;
2507 begin
2508 if processingDateIn is null then
2509 processingDate := sysdate;
2510 else
2511 processingDate := processingDateIn;
2512 end if;
2513 open startDateCursor;
2514 fetch startDateCursor into startDate;
2515 if startDateCursor%notfound then
2516 raise objectVersionNoDataException;
2517 end if;
2518 currentUserId := ame_util.getCurrentUserId;
2519 if versionStartDateIn = startDate then
2520 stringCount := stringValueListIn.count;
2521 for i in 1..stringCount loop
2522 update ame_string_values
2523 set
2524 last_updated_by = currentUserId,
2525 last_update_date = processingDate,
2526 last_update_login = currentUserId,
2527 end_date = processingDate
2528 where
2529 condition_id = conditionIdIn and
2530 string_value = stringValueListIn(i) and
2531 processingDate between start_date and
2532 nvl(end_date - ame_util.oneSecond, processingDate) ;
2533 end loop;
2534 conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2535 conditionKey := ame_condition_pkg.getConditionKey(conditionIdIn => conditionIdIn);
2536 attributeId := ame_condition_pkg.getAttributeId(conditionIdIn => conditionIdIn);
2537 parameterOne := ame_condition_pkg.getParameterOne(conditionIdIn => conditionIdIn);
2538 parameterTwo := ame_condition_pkg.getParameterTwo(conditionIdIn => conditionIdIn);
2539 parameterThree := ame_condition_pkg.getParameterThree(conditionIdIn => conditionIdIn);
2540 includeLowerLimit := ame_condition_pkg.getIncludeLowerLimit(conditionIdIn => conditionIdIn);
2541 includeUpperLimit := ame_condition_pkg.getIncludeUpperLimit(conditionIdIn => conditionIdIn);
2542 update ame_conditions
2543 set
2544 last_updated_by = currentUserId,
2545 last_update_date = processingDate,
2546 last_update_login = currentUserId,
2547 end_date = processingDate
2548 where
2549 condition_id = conditionIdIn and
2550 processingDate between start_date and
2551 nvl(end_date - ame_util.oneSecond, processingDate) ;
2552 conditionId := new(conditionIdIn => conditionIdIn,
2553 typeIn => conditionType,
2554 attributeIdIn => attributeId,
2555 conditionKeyIn => conditionKey,
2556 parameterOneIn => parameterOne,
2557 parameterTwoIn => parameterTwo,
2558 parameterThreeIn => parameterThree,
2559 includeLowerLimitIn => includeLowerLimit,
2560 includeUpperLimitIn => includeUpperLimit);
2561 else
2562 close startDateCursor;
2563 raise ame_util.objectVersionException;
2564 end if;
2565 close startDateCursor;
2566 exception
2567 when ame_util.objectVersionException then
2568 rollback;
2569 if(startDateCursor%isOpen) then
2570 close startDateCursor;
2571 end if;
2572 errorCode := -20001;
2573 errorMessage :=
2574 ame_util.getMessage(applicationShortNameIn => 'PER',
2575 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2576 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2577 routineNameIn => 'removeStringValue',
2578 exceptionNumberIn => errorCode,
2579 exceptionStringIn => errorMessage);
2580 raise_application_error(errorCode,
2581 errorMessage);
2582 when objectVersionNoDataException then
2583 rollback;
2584 if(startDateCursor%isOpen) then
2585 close startDateCursor;
2586 end if;
2587 errorCode := -20001;
2588 errorMessage :=
2589 ame_util.getMessage(applicationShortNameIn => 'PER',
2590 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2591 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2592 routineNameIn => 'removeStringValue',
2593 exceptionNumberIn => errorCode,
2594 exceptionStringIn => errorMessage);
2595 raise_application_error(errorCode,
2596 errorMessage);
2597 when others then
2598 rollback;
2599 if(startDateCursor%isOpen) then
2600 close startDateCursor;
2601 end if;
2602 ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2603 routineNameIn => 'removeStringValue',
2604 exceptionNumberIn => sqlcode,
2605 exceptionStringIn => '(condition ID ' ||
2606 conditionIdIn||
2607 ') ' ||
2608 sqlerrm);
2609 raise;
2610 end removeStringValue;
2611 end ame_condition_pkg;