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