[Home] [Help]
PACKAGE BODY: APPS.AME_RULE_PKG
Source
1 package body ame_rule_pkg as
2 /* $Header: ameorule.pkb 120.1 2006/09/07 12:59:51 pvelugul noship $ */
3 allOrgsLabel constant varchar2(50) := 'Include all organizations.';
4 allGroupsLabel constant varchar2(50) := 'Include all business groups.';
5 allSetsOfBooksLabel constant varchar2(50) := 'Include all sets of books.';
6 /* forward declarations */
7 /*
8 1. Call changeAllAttributeUseCounts after committing changes to a rule that will always
9 exist after the changes.
10 2. When you're going to call removeUsage, or do anything else that might result in a
11 rule's deletion (end dating), first fetch the list of attributes used by the rule
12 before the changes, then call removeUsage (or perform the other changes) and commit
13 them, then call changeAttributeUseCounts2, passing it the list of attributes.
14 Note that changeAttributeUseCounts gets called by changeAllAttributeUseCounts.
15 */
16 procedure changeAllAttributeUseCounts(ruleIdIn in integer,
17 finalizeIn in boolean default true);
18 procedure changeAttributeUseCounts(ruleIdIn in integer,
19 applicationIdIn in integer,
20 finalizeIn in boolean default true);
21 procedure changeAttributeUseCounts2(attributeIdsIn in ame_util.idList,
22 applicationIdIn in integer,
23 finalizeIn in boolean default true);
24 /* functions */
25 function allOrdinaryConditionsDeleted(conditionIdListIn in ame_util.idList,
26 deletedListIn in ame_util.stringList) return boolean as
27 conditionId integer;
28 deletedOrdinaryConditionCount integer;
29 ordinaryConditionCount integer;
30 begin
31 ordinaryConditionCount := 0;
32 deletedOrdinaryConditionCount := 0;
33 /* get a count of the existing ordinary conditions applied to the rule */
34 for i in 1..conditionIdListIn.count loop
35 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionIdListIn(i))
36 = ame_util.ordinaryConditionType) then
37 ordinaryConditionCount := (ordinaryConditionCount + 1);
38 end if;
39 end loop;
40 /* get a count of the deleted ordinary conditions */
41 for i in 1..deletedListIn.count loop
42 if(deletedListIn(i) like 'con%') then
43 conditionId := to_number(substrb(deletedListIn(i),4,(lengthb(deletedListIn(i)))));
44 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionId)
45 = ame_util.ordinaryConditionType) then
46 deletedOrdinaryConditionCount := (deletedOrdinaryConditionCount + 1);
47 end if;
48 end if;
49 end loop;
50 /* verify if all ordinary conditions were deleted */
51 if(ordinaryConditionCount = deletedOrdinaryConditionCount) then
52 /* all ordinary conditions were deleted */
53 return(true);
54 else
55 return(false);
56 end if;
57 exception
58 when others then
59 rollback;
60 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
61 routineNamein => 'allOrdinaryConditionsDeleted',
62 exceptionNumberIn => sqlcode,
63 exceptionStringIn => sqlerrm);
64 raise;
65 return(true);
66 end allOrdinaryConditionsDeleted;
67 function appHasRules(applicationIdIn in integer) return boolean as
68 ruleCount integer;
69 begin
70 select count(*)
71 into ruleCount
72 from ame_rule_usages
73 where
74 item_id = applicationIdIn and
75 ((sysdate between start_date and
76 nvl(end_date - ame_util.oneSecond, sysdate)) or
77 (sysdate < start_date and
78 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
79 if(ruleCount > 0) then
80 return(true);
81 end if;
82 return(false);
83 exception
84 when others then
85 rollback;
86 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
87 routineNamein => 'appHasRules',
88 exceptionNumberIn => sqlcode,
89 exceptionStringIn => sqlerrm);
90 raise;
91 return(true);
92 end appHasRules;
93 /* This function may return the following value
94 0 No overlapping Usage exists
95 1 Usage with same Lifespan and priority exists
96 2 Usage with same lifespan but different priority exists
97 3 Usage with overlapping lifespan exists
98 */
99 function bothSeededLMActionTypesChosen(actionTypeIdsIn in ame_util.idList) return boolean as
100 finalAuthActionTypeId integer;
101 nonFinalAuthActionTypeId integer;
102 tempCount integer;
103 begin
104 tempCount := 0;
105 nonFinalAuthActionTypeId :=
106 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.nonFinalAuthority);
107 finalAuthActionTypeId :=
108 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.finalAuthorityTypeName);
109 for i in 1..actionTypeIdsIn.count loop
110 if(actionTypeIdsIn(i) in (nonFinalAuthActionTypeId, finalAuthActionTypeId)) then
111 tempCount := (tempCount + 1);
112 end if;
113 end loop;
114 if(tempCount > 1) then
115 return(true);
116 end if;
117 return(false);
118 exception
119 when others then
120 rollback;
121 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
122 routineNamein => 'bothSeededLMActionTypesChosen',
123 exceptionNumberIn => sqlcode,
124 exceptionStringIn => sqlerrm);
125 raise;
126 return(true);
127 end bothSeededLMActionTypesChosen;
128 function bothSeededLMActionTypesChosen2(ruleIdIn in integer,
129 actionTypeIdsIn in ame_util.idList) return boolean as
130 cursor actionTypeIdsCursor(ruleIdIn in integer) is
131 select distinct(ame_action_types.action_type_id) action_type_id
132 from ame_action_types,
133 ame_actions,
134 ame_action_usages
135 where
136 ame_action_types.action_type_id = ame_actions.action_type_id and
137 ame_actions.action_id = ame_action_usages.action_id and
138 ame_action_usages.rule_id = ruleIdIn and
139 sysdate between ame_action_usages.start_date and
140 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
141 sysdate between ame_action_types.start_date and
142 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
143 sysdate between ame_actions.start_date and
144 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
145 actionTypeIds ame_util.idList;
146 finalAuthActionTypeId integer;
147 finalAuthority boolean;
148 nonFinalAuthActionTypeId integer;
149 nonFinalAuthority boolean;
150 tempCount integer;
151 tempCount2 integer;
152 begin
153 tempCount := (actionTypeIdsIn.count + 1);
154 actionTypeIds := actionTypeIdsIn;
155 for actionTypeIdsRec in actionTypeIdsCursor(ruleIdIn => ruleIdIn) loop
156 actionTypeIds(tempCount) := actionTypeIdsRec.action_type_id;
157 tempCount := (tempCount + 1);
158 end loop;
159 nonFinalAuthActionTypeId :=
160 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.nonFinalAuthority);
161 finalAuthActionTypeId :=
162 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.finalAuthorityTypeName);
163 /* Verify if both action types are now selected. */
164 for i in 1..actionTypeIds.count loop
165 if(actionTypeIds(i) = nonFinalAuthActionTypeId) then
166 nonFinalAuthority := true;
167 exit;
168 end if;
169 end loop;
170 for i in 1..actionTypeIds.count loop
171 if(actionTypeIds(i) = finalAuthActionTypeId) then
172 finalAuthority := true;
173 exit;
174 end if;
175 end loop;
176 if(nonFinalAuthority) then
177 if(finalAuthority) then
178 return(true);
179 end if;
180 end if;
181 return(false);
182 exception
183 when others then
184 rollback;
185 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
186 routineNamein => 'bothSeededLMActionTypesChosen2',
187 exceptionNumberIn => sqlcode,
188 exceptionStringIn => sqlerrm);
189 raise;
190 return(true);
191 end bothSeededLMActionTypesChosen2;
192 function checkRuleUsageExists(applicationIdIn in integer,
193 ruleIdIn in integer,
194 startDateIn in date,
195 endDateIn in date default null,
196 processingDateIn in date,
197 priorityIn in varchar2 default null)
198 return number as
199 cursor ruleUsageCursor(ruleIdIn in integer,
200 applicationIdIn in integer,
201 processingDateIn in date) is
202 select start_date, end_date, priority
203 from ame_rule_usages
204 where rule_id = ruleIdIn and
205 item_id = applicationIdIn and
206 ( processingDateIn between start_date and
207 nvl(end_date - ame_util.oneSecond,processingDateIn ) or
208 (processingDateIn < start_date and
209 start_date < nvl(end_date,start_date + ame_util.oneSecond)))
210 order by start_date desc;
211 usagestartDateList ame_util.dateList;
212 usageEndDateList ame_util.dateList;
213 usagePriorityList ame_util.idList;
214 errorCode integer;
215 errorMessage ame_util.longestStringType;
216 begin
217 for ruleUsage in ruleUsageCursor(ruleIdIn => ruleIdIn,
218 applicationIdIn => applicationIdIn,
219 processingDateIn => processingDateIn) loop
220 if ( trunc(startDateIn) = trunc(ruleUsage.start_date) and
221 trunc(endDateIn) = trunc(ruleUsage.end_date) and
222 priorityIn = ruleUsage.priority
223 ) then
224 return(1);
225 elsif ( trunc(startDateIn) = trunc(ruleUsage.start_date) and
226 trunc(endDateIn) = trunc(ruleUsage.end_date)
227 ) then
228 return(2);
229 elsif (ruleUsage.end_date is null and endDateIn is null) then
230 return(3);
231 elsif ((endDateIn is null and startDateIn < ruleUsage.end_date)
232 or
233 ( ruleUsage.end_date is null and
234 (startDateIn >= ruleUsage.start_date
235 or endDateIn > ruleUsage.start_date))
236 ) then
237 return(3);
238 elsif ( (startDateIn between ruleUsage.start_date and
239 ruleUsage.end_date - ame_util.oneSecond)
240 or
241 (endDateIn between ruleUsage.start_date and
242 ruleUsage.end_date - ame_util.oneSecond)
243 or
244 (ruleUsage.start_date between startDateIn and
245 endDateIn - ame_util.oneSecond )
246 or
247 (ruleUsage.end_date between startDateIn and
248 endDateIn - ame_util.oneSecond )
249 ) then
250 return(3);
251 end if;
252 end loop;
253 return(0);
254 exception
255 when others then
256 rollback;
257 errorCode := -20001;
258 errorMessage :=
259 ame_util.getMessage(applicationShortNameIn => 'PER',
260 messageNameIn => 'AME_400329_RULE_USG_OVER_LIFE');
261 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
262 routineNameIn => 'checkRuleUsageExists',
263 exceptionNumberIn => errorCode,
264 exceptionStringIn => errorMessage);
265 raise;
266 return(3);
267 end checkRuleUsageExists;
268 function deletedAllExceptionConditions(conditionIdListIn in ame_util.idList,
269 deletedListIn in ame_util.stringList) return boolean as
270 conditionId integer;
271 mandatoryConditionCount integer;
272 mandatoryConditionIdList ame_util.idList;
273 tempConditionCount integer;
274 tempIndex integer;
275 begin
276 tempIndex := 1;
277 for i in 1..conditionIdListIn.count loop
278 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionIdListIn(i))
279 = ame_util.exceptionConditionType) then
280 mandatoryConditionIdList(tempIndex) := conditionIdListIn(i);
281 tempIndex := (tempIndex + 1);
282 /* there can be multiple exception conditions so keep looping */
283 end if;
284 end loop;
285 mandatoryConditionCount := mandatoryConditionIdList.count;
286 if(mandatoryConditionCount = 0) then
287 return(false);
288 end if;
289 tempConditionCount := 0;
290 for i in 1..deletedListIn.count loop
291 if(deletedListIn(i) like 'con%') then
292 conditionId := to_number(substrb(deletedListIn(i),4,(lengthb(deletedListIn(i)))));
293 for j in 1..mandatoryConditionCount loop
294 if(mandatoryConditionIdList(j) = conditionId) then
295 tempConditionCount := (tempConditionCount + 1);
296 end if;
297 end loop;
298 end if;
299 end loop;
300 if(mandatoryConditionCount = tempConditionCount) then
301 /* all exception conditions were deleted */
302 return(true);
303 else
304 return(false);
305 end if;
306 exception
307 when others then
308 rollback;
309 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
310 routineNamein => 'deletedAllExceptionConditions',
311 exceptionNumberIn => sqlcode,
312 exceptionStringIn => sqlerrm);
313 raise;
314 return(true); /* conservative: avoids allowing deletion */
315 end deletedAllExceptionConditions;
316 function descriptionInUse(descriptionIn in varchar2) return boolean as
317 descriptionCount varchar2(500);
318 begin
319 select count(*)
320 into descriptionCount
321 from ame_rules
322 where upper(description) = upper(descriptionIn) and
323 /* allows for future start date */
324 ((sysdate between start_date and
325 nvl(end_date - ame_util.oneSecond, sysdate)) or
326 (sysdate < start_date and
327 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
328 if descriptionCount > 0 then
329 return(true);
330 end if;
331 return(false);
332 exception
333 when others then
334 rollback;
335 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
336 routineNamein => 'descriptionInUse',
337 exceptionNumberIn => sqlcode,
338 exceptionStringIn => sqlerrm);
339 raise;
340 return(true);
341 end descriptionInUse;
342 function finalAuthorityActionType(actionTypeIdsIn in ame_util.idList) return boolean as
343 finalAuthActionTypeId integer;
344 begin
345 finalAuthActionTypeId :=
346 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.finalAuthorityTypeName);
347 for i in 1..actionTypeIdsIn.count loop
348 if(actionTypeIdsIn(i) = finalAuthActionTypeId) then
349 return(true);
350 end if;
351 end loop;
352 return(false);
353 exception
354 when others then
355 rollback;
356 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
357 routineNamein => 'finalAuthorityActionType',
358 exceptionNumberIn => sqlcode,
359 exceptionStringIn => sqlerrm);
360 raise;
361 return(true);
362 end finalAuthorityActionType;
363 function finalAuthorityActionType2(ruleIdIn in integer) return boolean as
364 cursor actionTypeIdsCursor(ruleIdIn in integer) is
365 select distinct(ame_action_types.action_type_id) action_type_id
366 from ame_action_types,
367 ame_actions,
368 ame_action_usages
369 where
370 ame_action_types.action_type_id = ame_actions.action_type_id and
371 ame_actions.action_id = ame_action_usages.action_id and
372 ame_action_usages.rule_id = ruleIdIn and
373 sysdate between ame_action_usages.start_date and
374 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
375 sysdate between ame_action_types.start_date and
376 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
377 sysdate between ame_actions.start_date and
378 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
379 finalAuthActionTypeId integer;
380 begin
381 finalAuthActionTypeId :=
382 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.finalAuthorityTypeName);
383 for actionTypeIdsRec in actionTypeIdsCursor(ruleIdIn => ruleIdIn) loop
384 if(actionTypeIdsRec.action_type_id = finalAuthActionTypeId) then
385 return(true);
386 end if;
387 end loop;
388 return(false);
389 exception
390 when others then
391 rollback;
392 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
393 routineNamein => 'finalAuthorityActionType2',
394 exceptionNumberIn => sqlcode,
395 exceptionStringIn => sqlerrm);
396 raise;
397 return(true);
398 end finalAuthorityActionType2;
399 function getApproverCategory(ruleIdIn in integer,
400 applicationIdIn in integer,
401 usageStartDateIn in date) return varchar2 as
402 approverCategory ame_util.stringType;
403 begin
404 select approver_category
405 into approverCategory
406 from
407 ame_rule_usages
408 where
409 rule_id = ruleIdIn and
410 item_id = applicationIdIn and
411 start_date = usageStartDateIn and
412 ((sysdate between start_date and
413 nvl(end_date - ame_util.oneSecond, sysdate)) or
414 (sysdate < start_date and
415 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
416 return(approverCategory);
417 exception
418 when others then
419 rollback;
420 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
421 routineNamein => 'getApproverCategory',
422 exceptionNumberIn => sqlcode,
423 exceptionStringIn => '(rule ID ' ||
424 ruleIdIn||
425 ') ' ||
426 sqlerrm);
427 raise;
428 return(null);
429 end getApproverCategory;
430 function getConditionCount(ruleIdIn in integer) return integer as
431 conditionCount integer;
432 begin
433 select count(condition_id)
434 into conditionCount
435 from ame_condition_usages
436 where
437 rule_id = ruleIdIn and
438 /* allows for future start date */
439 ((sysdate between start_date and
440 nvl(end_date - ame_util.oneSecond, sysdate)) or
441 (sysdate < start_date and
442 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
443 return(conditionCount);
444 exception
445 when others then
446 rollback;
447 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
448 routineNamein => 'getConditionCount',
449 exceptionNumberIn => sqlcode,
450 exceptionStringIn => '(rule ID ' ||
451 ruleIdIn||
452 ') ' ||
453 sqlerrm);
454 raise;
455 return(null);
456 end getConditionCount;
457 function getItemClassId(ruleIdIn in integer,
458 processingDateIn in date default null) return integer as
459 itemClassId integer;
460 begin
461 if processingDateIn is null then
462 select item_class_id
463 into itemClassId
464 from ame_rules
465 where
466 rule_id = ruleIdIn and
467 ((sysdate between start_date and
468 nvl(end_date - ame_util.oneSecond, sysdate)) or
469 (sysdate < start_date and
470 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
471 return(itemClassId);
472 else
473 select item_class_id
474 into itemClassId
475 from ame_rules
476 where
477 rule_id = ruleIdIn and
478 rownum < 2 and /* for efficiency */
479 (processingDateIn between start_date and
480 nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
481 return(itemClassId);
482 end if;
483 exception
484 when others then
485 rollback;
486 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
487 routineNamein => 'getItemClassId',
488 exceptionNumberIn => sqlcode,
489 exceptionStringIn => '(rule ID ' ||
490 ruleIdIn||
491 ') ' ||
492 sqlerrm);
493 raise;
494 return(null);
495 end getItemClassId;
496 function getLMConditionId(ruleIdIn in integer) return integer as
497 conditionId integer;
498 begin
499 select ame_conditions.condition_id
500 into conditionId
501 from ame_conditions,
502 ame_condition_usages
503 where
504 ame_conditions.condition_id = ame_condition_usages.condition_id and
505 ame_conditions.condition_type = ame_util.listModConditionType and
506 ame_condition_usages.rule_id = ruleIdIn and
507 (ame_conditions.start_date <= sysdate and
508 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
509 ((sysdate between ame_condition_usages.start_date and
510 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
511 (sysdate < ame_condition_usages.start_date and
512 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
513 ame_condition_usages.start_date + ame_util.oneSecond)));
514 return(conditionId);
515 exception
516 when others then
517 rollback;
518 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
519 routineNamein => 'getLMConditionId',
520 exceptionNumberIn => sqlcode,
521 exceptionStringIn => '(rule ID ' ||
522 ruleIdIn||
523 ') ' ||
524 sqlerrm);
525 raise;
526 return(null);
527 end getLMConditionId;
528 function getOrganizationName(organizationIdIn in integer) return varchar2 as
529 organizationName hr_organization_units.name%type;
530 begin
531 select name
532 into organizationName
533 from hr_organization_units
534 where
535 sysdate >= date_from and
536 organization_id = organizationIdIn and
537 (date_to is null or sysdate < date_to);
538 return(organizationName);
539 exception
540 when others then
541 rollback;
542 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
543 routineNameIn => 'getOrganizationName',
544 exceptionNumberIn => sqlcode,
545 exceptionStringIn => '(organization ID ' ||
546 organizationIdIn||
547 ') ' ||
548 sqlerrm);
549 raise;
550 return(null);
551 end getOrganizationName;
552 function getPriority(ruleIdIn in integer,
553 applicationIdIn in integer,
554 usageStartDateIn in date) return varchar2 as
555 priority varchar2(20);
556 begin
557 select to_char(priority)
558 into priority
559 from ame_rule_usages
560 where
561 rule_id = ruleIdIn and
562 item_id = applicationIdIn and
563 usageStartDateIn between start_date and
564 nvl(end_date - ame_util.oneSecond, usageStartDateIn);
565 return(priority);
566 exception
567 when others then
568 rollback;
569 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
570 routineNamein => 'getPriority',
571 exceptionNumberIn => sqlcode,
572 exceptionStringIn => '(rule ID ' ||
573 ruleIdIn||
574 ') ' ||
575 sqlerrm);
576 raise;
577 return(null);
578 end getPriority;
579 function getItemId(ruleIdIn in integer) return integer as
580 itemId ame_rule_usages.item_id%type;
581 begin
582 select item_id
583 into itemId
584 from ame_rule_usages
585 where
586 rule_id = ruleIdIn and
587 ((sysdate between start_date and
588 nvl(end_date - ame_util.oneSecond, sysdate)) or
589 (sysdate < start_date and
590 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
591 return(itemId);
592 exception
593 when others then
594 rollback;
595 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
596 routineNamein => 'getItemId',
597 exceptionNumberIn => sqlcode,
598 exceptionStringIn => '(rule ID ' ||
599 ruleIdIn||
600 ') ' ||
601 sqlerrm);
602 raise;
603 return(null);
604 end getItemId;
605 function getRuleType(ruleIdIn in integer,
606 processingDateIn in date default null) return integer as
607 ruleType integer;
608 begin
609 if processingDateIn is null then
610 select rule_type
611 into ruleType
612 from ame_rules
613 where
614 rule_id = ruleIdIn and
615 ((sysdate between start_date and
616 nvl(end_date - ame_util.oneSecond, sysdate)) or
617 (sysdate < start_date and
618 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
619 return(ruleType);
620 else
621 select rule_type
622 into ruleType
623 from ame_rules
624 where
625 rule_id = ruleIdIn and
626 rownum < 2 and /* for efficiency */
627 (processingDateIn between start_date and
628 nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
629 return(ruleType);
630 end if;
631 exception
632 when others then
633 rollback;
634 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
635 routineNamein => 'getRuleType',
636 exceptionNumberIn => sqlcode,
637 exceptionStringIn => '(rule ID ' ||
638 ruleIdIn||
639 ') ' ||
640 sqlerrm);
641 raise;
642 return(null);
643 end getRuleType;
644 function getRuleTypeLabel(ruleTypeIn in integer) return varchar2 as
645 begin
646 if(ruleTypeIn = ame_util.authorityRuleType) then
647 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_CREATION'));
648 elsif(ruleTypeIn = ame_util.exceptionRuleType) then
649 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_CREATION_EXCEPTION'));
650 elsif(ruleTypeIn = ame_util.listModRuleType) then
651 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_MODIFICATION'));
652 elsif(ruleTypeIn = ame_util.substitutionRuleType) then
653 return(lower(ame_util.getLabel(ame_util.perFndAppId, 'AME_SUBSTITUTION')));
654 -- return('substitution');
655 elsif(ruleTypeIn = ame_util.preListGroupRuleType) then
656 return(ame_util.getLabel(ame_util.perFndAppId, 'AME_PRE_LIST_APPROVAL_GROUP'));
657 elsif(ruleTypeIn = ame_util.postListGroupRuleType) then
658 return(ame_util.getLabel(ame_util.perFndAppId, 'AME_POST_LIST_APPROVAL_GROUP'));
659 elsif(ruleTypeIn = ame_util.productionRuleType) then
660 return(lower(ame_util.getLabel(ame_util.perFndAppId, 'AME_PRODUCTION')));
661 elsif(ruleTypeIn = ame_util.combinationRuleType) then
662 return(lower(ame_util.getLabel(ame_util.perFndAppId, 'AME_COMBINATION')));
663 else
664 return(null);
665 end if;
666 exception
667 when others then
668 rollback;
669 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
670 routineNamein => 'getRuleTypeLabel',
671 exceptionNumberIn => sqlcode,
672 exceptionStringIn => sqlerrm);
673 raise;
674 return(null);
675 end getRuleTypeLabel;
676 function getRuleTypeLabel2(ruleTypeIn in integer) return varchar2 as
677 begin
678 if(ruleTypeIn = ame_util.authorityRuleType) then
679 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_CREAT_RULES'));
680 elsif(ruleTypeIn = ame_util.exceptionRuleType) then
681 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_CREAT_EXCEP_RULES'));
682 elsif(ruleTypeIn = ame_util.listModRuleType) then
683 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_MOD_RULES'));
684 elsif(ruleTypeIn = ame_util.substitutionRuleType) then
685 return(ame_util.getLabel(ame_util.perFndAppId,'AME_SUBSTITUTION_RULES'));
686 elsif(ruleTypeIn = ame_util.preListGroupRuleType) then
687 return(ame_util.getLabel(ame_util.perFndAppId,'AME_PRE_LIST_APPR_GROUP_RULES'));
688 elsif(ruleTypeIn = ame_util.postListGroupRuleType) then
689 return(ame_util.getLabel(ame_util.perFndAppId,'AME_POST_LIST_APPR_GROUP_RULES'));
690 elsif(ruleTypeIn = ame_util.productionRuleType) then
691 return(ame_util.getLabel(ame_util.perFndAppId,'AME_PRODUCTION_RULES'));
692 elsif(ruleTypeIn = ame_util.combinationRuleType) then
693 return(ame_util.getLabel(ame_util.perFndAppId,'AME_COMBINATION_RULES'));
694 else
695 return(null);
696 end if;
697 exception
698 when others then
699 rollback;
700 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
701 routineNamein => 'getRuleTypeLabel2',
702 exceptionNumberIn => sqlcode,
703 exceptionStringIn => sqlerrm);
704 raise;
705 return(null);
706 end getRuleTypeLabel2;
707 function getDescription(ruleIdIn in integer,
708 processingDateIn in date default null) return varchar2 as
709 description ame_rules.description%type;
710 begin
711 if processingDateIn is null then
712 select description
713 into description
714 from ame_rules
715 where
716 rule_id = ruleIdIn and
717 rownum < 2 and /* for efficiency */
718 ((sysdate between start_date and
719 nvl(end_date - ame_util.oneSecond, sysdate)) or
720 (sysdate < start_date and
721 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
722 else
723 select description
724 into description
725 from ame_rules
726 where
727 rule_id = ruleIdIn and
728 rownum < 2 and /* for efficiency */
729 (processingDateIn between start_date and
730 nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
731 end if;
732 return(description);
733 exception
734 when others then
735 rollback;
736 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
737 routineNamein => 'getDescription',
738 exceptionNumberIn => sqlcode,
739 exceptionStringIn => '(rule ID ' ||
740 ruleIdIn||
741 ') ' ||
742 sqlerrm);
743 raise;
744 return(null);
745 end getDescription;
746 function getEndDate(ruleIdIn in integer) return date as
747 endDate ame_rules.end_date%type;
748 begin
749 select end_date
750 into endDate
751 from ame_rules
752 where
753 rule_id = ruleIdIn and
754 ((sysdate between start_date and
755 nvl(end_date - ame_util.oneSecond, sysdate)) or
756 (sysdate < start_date and
757 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
758 return(endDate);
759 exception
760 when others then
761 rollback;
762 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
763 routineNamein => 'getEndDate',
764 exceptionNumberIn => sqlcode,
765 exceptionStringIn => '(rule ID ' ||
766 ruleIdIn||
767 ') ' ||
768 sqlerrm);
769 raise;
770 return(null);
771 end getEndDate;
772 function getEffectiveEndDateUsage(applicationIdIn in integer,
773 ruleIdIn in integer,
774 effectiveDateIn in date) return date as
775 usageEndDate date;
776 effectiveDate date;
777 begin
778 effectiveDate := effectiveDateIn;
779 if(trunc(effectiveDate) = trunc(sysdate)) then
780 effectiveDate := sysdate;
781 end if;
782 select end_date
783 into usageEndDate
784 from ame_rule_usages
785 where
786 item_id = applicationIdIn and
787 rule_id = ruleIdIn and
788 effectiveDate between start_date and
789 nvl(end_date - ame_util.oneSecond, effectiveDate);
790 return(usageEndDate);
791 exception
792 when no_data_found then
793 return(null);
794 when others then
795 rollback;
796 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
797 routineNamein => 'getEffectiveEndDateUsage',
798 exceptionNumberIn => sqlcode,
799 exceptionStringIn => '(rule ID ' ||
800 ruleIdIn||
801 ') ' ||
802 sqlerrm);
803 raise;
804 return(null);
805 end getEffectiveEndDateUsage;
806 function getEffectiveStartDateUsage(applicationIdIn in integer,
807 ruleIdIn in integer,
808 effectiveDateIn in date) return date as
809 usageStartDate date;
810 effectiveDate date;
811 begin
812 effectiveDate := effectiveDateIn;
813 if(trunc(effectiveDate) = trunc(sysdate)) then
814 effectiveDate := sysdate;
815 end if;
816 select start_date
817 into usageStartDate
818 from ame_rule_usages
819 where
820 item_id = applicationIdIn and
821 rule_id = ruleIdIn and
822 effectiveDate between start_date and
823 nvl(end_date - ame_util.oneSecond, effectiveDate);
824 return(usageStartDate);
825 exception
826 when no_data_found then
827 return(null);
828 when others then
829 rollback;
830 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
831 routineNamein => 'getEffectiveStartDateUsage',
832 exceptionNumberIn => sqlcode,
833 exceptionStringIn => '(rule ID ' ||
834 ruleIdIn||
835 ') ' ||
836 sqlerrm);
837 raise;
838 return(null);
839 end getEffectiveStartDateUsage;
840 function getUsageEndDate(ruleIdIn in integer,
841 applicationIdIn in integer,
842 processingDateIn in date) return varchar2 as
843 endDate ame_rule_usages.end_date%type;
844 begin
845 select end_date
846 into endDate
847 from ame_rule_usages
848 where
849 rule_id = ruleIdIn and
850 item_id = applicationIdIn and
851 creation_date = processingDateIn;
852 return(ame_util.versionDateToString(dateIn => endDate));
853 exception
854 when others then
855 rollback;
856 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
857 routineNamein => 'getUsageEndDate',
858 exceptionNumberIn => sqlcode,
859 exceptionStringIn => '(rule ID ' ||
860 ruleIdIn||
861 ') ' ||
862 sqlerrm);
863 raise;
864 return(null);
865 end getUsageEndDate;
866 function getId(typeIn in varchar2,
867 conditionIdListIn in ame_util.idList,
868 actionIdListIn in ame_util.idList) return integer as
869 cursor ruleIdCursor(typeIn in varchar2) is
870 select rule_id
871 from ame_rules
872 where
873 rule_type = typeIn and
874 ((sysdate between start_date and
875 nvl(end_date - ame_util.oneSecond, sysdate)) or
876 (sysdate < start_date and
877 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
878 actionIdList1 ame_util.idList;
879 actionIdList2 ame_util.idList;
880 actionIdMatch boolean;
881 conditionIdList1 ame_util.idList;
882 conditionIdList2 ame_util.idList;
883 conditionIdMatch boolean;
884 ruleId ame_rules.rule_id%type;
885 begin
886 ruleId := null;
887 conditionIdList1 := conditionIdListIn;
888 actionIdList1 := actionIdListIn;
889 ame_util.sortIdListInPlace(idListInOut => conditionIdList1);
890 ame_util.sortIdListInPlace(idListInOut => actionIdList1);
891 conditionIdMatch := false;
892 actionIdMatch := false;
893 for tempRuleId in ruleIdCursor(typeIn => typeIn) loop
894 getConditionIds(ruleIdIn => tempRuleId.rule_id,
895 conditionIdListOut => conditionIdList2);
896 ame_util.sortIdListInPlace(idListInOut => conditionIdList2);
897 if(ame_util.idListsMatch(idList1InOut => conditionIdList1,
898 idList2InOut => conditionIdList2,
899 sortList1In => false,
900 sortList2In => false)) then
901 conditionIdMatch := true;
902 end if;
903 getactionIds(ruleIdIn => tempRuleId.rule_id,
904 actionIdListOut => actionIdList2);
905 ame_util.sortIdListInPlace(idListInOut => actionIdList2);
906 if(ame_util.idListsMatch(idList1InOut => actionIdList1,
907 idList2InOut => actionIdList2,
908 sortList1In => false,
909 sortList2In => false)) then
910 actionIdMatch := true;
911 end if;
912 ruleId := tempRuleId.rule_id;
913 if(conditionIdMatch and actionIdMatch) then
914 return(ruleId);
915 end if;
916 conditionIdList2.delete;
917 actionIdList2.delete;
918 conditionIdMatch := false;
919 actionIdMatch := false;
920 end loop;
921 return(null);
922 exception
923 when others then
924 rollback;
925 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
926 routineNamein => 'getId',
927 exceptionNumberIn => sqlcode,
928 exceptionStringIn => '(rule ID ' ||
929 ruleId ||
930 ') ' ||
931 sqlerrm);
932 raise;
933 return(null);
934 end getId;
935 /*
936 AME_STRIPING
937 function getRuleStripeSetId(ruleIdIn in integer) return integer as
938 stripeSetId integer;
939 begin
940 begin
941 select stripe_set_id
942 into stripeSetId
943 from ame_rule_stripe_sets
944 where
945 rule_id = ruleIdIn and
946 (start_date <= sysdate and
947 (end_date is null or sysdate < end_date));
948 return(stripeSetId);
949 exception
950 when no_data_found then
951 return(null);
952 end;
953 exception
954 when others then
955 rollback;
956 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
957 routineNameIn => 'getRuleStripeSetId',
958 exceptionNumberIn => sqlcode,
959 exceptionStringIn => sqlerrm);
960 return(null);
961 end getRuleStripeSetId;
962 */
963 function ruleKeyExists (ruleKeyIn in varchar2) return boolean as
964 ruleCount integer;
965 begin
966 select count(*)
967 into ruleCount
968 from ame_rules
969 where upper(rule_key) = upper(ruleKeyIn) and
970 rownum < 2;
971 if ruleCount > 0 then
972 return(true);
973 else
974 return(false);
975 end if;
976 exception
977 when others then
978 rollback;
979 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
980 routineNamein => 'ruleKeyExists',
981 exceptionNumberIn => sqlcode,
982 exceptionStringIn => '(rule Key ' ||
983 ruleKeyIn ||
984 ') ' ||
985 sqlerrm);
986 raise;
987 return(true);
988 end ruleKeyExists;
989 function getNextRuleKey return varchar2 as
990 databaseId varchar2(50);
991 newRuleKey ame_rules.rule_key%type;
992 newRuleKey1 ame_rules.rule_key%type;
993 ruleKeyId number;
994 seededKeyPrefix varchar2(4);
995 begin
996 begin
997 select to_char(db.dbid)
998 into databaseId
999 from v$database db, v$instance instance
1000 where upper(db.name) = upper(instance.instance_name);
1001 exception
1002 when no_data_found then
1003 databaseId := null;
1004 end;
1005 if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
1006 seededKeyPrefix := ame_util.seededKeyPrefix;
1007 else
1008 seededKeyPrefix := null;
1009 end if;
1010 loop
1011 select ame_rule_keys_s.nextval into ruleKeyId from dual;
1012 newRuleKey := databaseId||':'||ruleKeyId;
1013 if seededKeyPrefix is not null then
1014 newRuleKey1 := seededKeyPrefix||'-' || newRuleKey;
1015 else
1016 newRuleKey1 := newRuleKey;
1017 end if;
1018 if not ruleKeyExists(newRuleKey1) then
1019 exit;
1020 end if;
1021 end loop;
1022 return(newRuleKey);
1023 exception
1024 when others then
1025 rollback;
1026 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1027 routineNamein => 'getNextRuleKey',
1028 exceptionNumberIn => sqlcode,
1029 exceptionStringIn => '(rule Key ' ||
1030 newRuleKey ||
1031 ') ' ||
1032 sqlerrm);
1033 raise;
1034 return(null);
1035 end getNextRuleKey;
1036 function getRuleKey(ruleIdIn in integer,
1037 processingDateIn in date default null) return varchar2 as
1038 ruleKey ame_rules.rule_key%type;
1039 begin
1040 if processingDateIn is null then
1041 select rule_key
1042 into ruleKey
1043 from ame_rules
1044 where
1045 rule_id = ruleIdIn and
1046 ((sysdate between start_date and
1047 nvl(end_date - ame_util.oneSecond, sysdate)) or
1048 (sysdate < start_date and
1049 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1050 else
1051 select rule_key
1052 into ruleKey
1053 from ame_rules
1054 where
1055 rule_id = ruleIdIn and
1056 (processingDateIn between start_date and
1057 nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
1058 end if;
1059 return(ruleKey);
1060 exception
1061 when others then
1062 rollback;
1063 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1064 routineNamein => 'getRuleKey',
1065 exceptionNumberIn => sqlcode,
1066 exceptionStringIn => '(rule ID ' ||
1067 ruleIdIn||
1068 ') ' ||
1069 sqlerrm);
1070 raise;
1071 return(null);
1072 end getRuleKey;
1073 function getNewRuleStartDate(ruleIdIn in integer,
1074 processingDateIn in date) return date as
1075 ruleStartDate date;
1076 newStartDate date;
1077 begin
1078 select min(start_date)
1079 into ruleStartDate
1080 from ame_rule_usages
1081 where
1082 rule_id = ruleIdIn and
1083 ((sysdate between start_date and
1084 nvl(end_date - ame_util.oneSecond, sysdate)) or
1085 (sysdate < start_date and
1086 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1087 if ruleStartDate is null then
1088 newStartDate := null;
1089 elsif trunc(ruleStartDate) > trunc(processingDateIn) then
1090 newStartDate := trunc(ruleStartDate);
1091 else
1092 newStartDate := processingDateIn;
1093 end if;
1094 return(newStartDate);
1095 exception
1096 when others then
1097 rollback;
1098 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1099 routineNamein => 'getNewRuleStartDate',
1100 exceptionNumberIn => sqlcode,
1101 exceptionStringIn => '(rule ID ' ||
1102 ruleIdIn||
1103 ') ' ||
1104 sqlerrm);
1105 raise;
1106 return(null);
1107 end getNewRuleStartDate;
1108 function getNewRuleEndDate(ruleIdIn in integer,
1109 processingDateIn in date) return date as
1110 ruleEndDate date;
1111 newEndDate date;
1112 begin
1113 select max(nvl(end_date,to_date('31/12/4712','DD/MM/YYYY')))
1114 into ruleEndDate
1115 from ame_rule_usages
1116 where
1117 rule_id = ruleIdIn and
1118 ((sysdate between start_date and
1119 nvl(end_date - ame_util.oneSecond, sysdate)) or
1120 (sysdate < start_date and
1121 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1122 if ruleEndDate is null or ruleEndDate =
1123 to_date('31/12/4712','DD/MM/YYYY') then
1124 newEndDate := null;
1125 elsif trunc(ruleEndDate) > trunc(processingDateIn) then
1126 newEndDate := trunc(ruleEndDate);
1127 else
1128 newEndDate := ruleEndDate;
1129 end if;
1130 return(newEndDate);
1131 exception
1132 when others then
1133 rollback;
1134 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1135 routineNamein => 'getNewRuleEndDate',
1136 exceptionNumberIn => sqlcode,
1137 exceptionStringIn => '(rule ID ' ||
1138 ruleIdIn||
1139 ') ' ||
1140 sqlerrm);
1141 raise;
1142 return(null);
1143 end getNewRuleEndDate;
1144 function getRulePriorityMode(applicationIdIn in integer,
1145 ruleTypeIn in varchar2) return varchar2 as
1146 priority ame_util.stringType;
1147 variableValue ame_util.longStringType;
1148 begin
1149 variableValue := ame_util.getConfigVar(variableNameIn => ame_util.rulePriorityModesConfigVar,
1150 applicationIdIn => applicationIdIn);
1151 if(ruleTypeIn = ame_util.combinationRuleType) then
1152 priority := substrb(variableValue, 1, (instr(variableValue,':',1,1) -1));
1153 elsif(ruleTypeIn = ame_util.authorityRuleType) then
1154 priority := substrb(variableValue,
1155 (instr(variableValue,':',1,1) +1),
1156 (instr(variableValue,':',1,2) -
1157 (instr(variableValue,':',1,1) +1)));
1158 elsif(ruleTypeIn = ame_util.exceptionRuleType) then
1159 priority := substrb(variableValue,
1160 (instr(variableValue,':',1,2) +1),
1161 (instr(variableValue,':',1,3) -
1162 (instr(variableValue,':',1,2) +1)));
1163 elsif(ruleTypeIn = ame_util.listModRuleType) then
1164 priority := substrb(variableValue,
1165 (instr(variableValue,':',1,3) +1),
1166 (instr(variableValue,':',1,4) -
1167 (instr(variableValue,':',1,3) +1)));
1168 elsif(ruleTypeIn = ame_util.substitutionRuleType) then
1169 priority := substrb(variableValue,
1170 (instr(variableValue,':',1,4) +1),
1171 (instr(variableValue,':',1,5) -
1172 (instr(variableValue,':',1,4) +1)));
1173 elsif(ruleTypeIn = ame_util.preListGroupRuleType) then
1174 priority := substrb(variableValue,
1175 (instr(variableValue,':',1,5) +1),
1176 (instr(variableValue,':',1,6) -
1177 (instr(variableValue,':',1,5) +1)));
1178 elsif(ruleTypeIn = ame_util.postListGroupRuleType) then
1179 priority := substrb(variableValue,
1180 (instr(variableValue,':',1,6) +1),
1181 (instr(variableValue,':',1,7) -
1182 (instr(variableValue,':',1,6) +1)));
1183 elsif(ruleTypeIn = ame_util.productionRuleType) then
1184 priority := substrb(variableValue,
1185 (instr(variableValue,':',1,7) +1));
1186 end if;
1187 return(priority);
1188 exception
1189 when others then
1190 rollback;
1191 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1192 routineNamein => 'getRulePriorityMode',
1193 exceptionNumberIn => sqlcode,
1194 exceptionStringIn => sqlerrm);
1195 raise;
1196 return(ame_util.disabledRulePriority); /* safe */
1197 end getRulePriorityMode;
1198 function getStartDate(ruleIdIn in integer) return date as
1199 startDate ame_rules.start_date%type;
1200 begin
1201 select start_date
1202 into startDate
1203 from ame_rules
1204 where
1205 rule_id = ruleIdIn and
1206 ((sysdate between start_date and
1207 nvl(end_date - ame_util.oneSecond, sysdate)) or
1208 (sysdate < start_date and
1209 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1210 return(startDate);
1211 exception
1212 when others then
1213 rollback;
1214 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1215 routineNamein => 'getStartDate',
1216 exceptionNumberIn => sqlcode,
1217 exceptionStringIn => '(rule ID ' ||
1218 ruleIdIn||
1219 ') ' ||
1220 sqlerrm);
1221 raise;
1222 return(null);
1223 end getStartDate;
1224 function getSubItemClassId(ruleIdIn in integer) return integer as
1225 cursor getSubItemClassIdCur(ruleIdIn in integer,
1226 headerItemClassIdIn in integer) is
1227 select item_class_id
1228 from ame_conditions,
1229 ame_attributes,
1230 ame_condition_usages
1231 where
1232 ame_conditions.condition_id = ame_condition_usages.condition_id and
1233 ame_conditions.attribute_id = ame_attributes.attribute_id and
1234 ame_condition_usages.rule_id = ruleIdIn and
1235 ame_attributes.item_class_id <> headerItemClassIdIn and
1236 (sysdate between ame_conditions.start_date and
1237 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)) and
1238 (sysdate between ame_condition_usages.start_date and
1239 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) and
1240 (sysdate between ame_attributes.start_date and
1241 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate));
1242 headerItemClassId integer;
1243 itemIds ame_util.idList;
1244 begin
1245 headerItemClassId :=
1246 ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
1247 ame_util.headerItemClassName);
1248 open getSubItemClassIdCur(ruleIdIn => ruleIdIn,
1249 headerItemClassIdIn => headerItemClassId);
1250 fetch getSubItemClassIdCur bulk collect
1251 into itemIds;
1252 close getSubItemClassIdCur;
1253 for i in 1..itemIds.count loop
1254 if(itemIds(i) <> headerItemClassId) then
1255 return(itemIds(i));
1256 end if;
1257 end loop;
1258 exception
1259 when others then
1260 rollback;
1261 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1262 routineNamein => 'getSubItemClassId',
1263 exceptionNumberIn => sqlcode,
1264 exceptionStringIn => '(rule ID ' ||
1265 ruleIdIn||
1266 ') ' ||
1267 sqlerrm);
1268 raise;
1269 return(null);
1270 end getSubItemClassId;
1271 function getUsageStartDate(ruleIdIn in integer,
1272 applicationIdIn in integer,
1273 processingDateIn in date) return varchar2 as
1274 startDate ame_rule_usages.start_date%type;
1275 begin
1276 select start_date
1277 into startDate
1278 from ame_rule_usages
1279 where
1280 rule_id = ruleIdIn and
1281 item_id = applicationIdIn and
1282 creation_date = processingDateIn;
1283 return(ame_util.versionDateToString(dateIn => startDate));
1284 exception
1285 when others then
1286 rollback;
1287 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1288 routineNamein => 'getUsageStartDate',
1289 exceptionNumberIn => sqlcode,
1290 exceptionStringIn => '(rule ID ' ||
1291 ruleIdIn||
1292 ') ' ||
1293 sqlerrm);
1294 raise;
1295 return(null);
1296 end getUsageStartDate;
1297 function getType(ruleIdIn in integer) return integer as
1298 ruleType ame_rules.rule_type%type;
1299 begin
1300 select rule_type
1301 into ruleType
1302 from ame_rules
1303 where
1304 rule_id = ruleIdIn and
1305 ((sysdate between start_date and
1306 nvl(end_date - ame_util.oneSecond, sysdate)) or
1307 (sysdate < start_date and
1308 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1309 return(ruleType);
1310 exception
1311 when others then
1312 rollback;
1313 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1314 routineNamein => 'getType',
1315 exceptionNumberIn => sqlcode,
1316 exceptionStringIn => '(rule ID ' ||
1317 ruleIdIn||
1318 ') ' ||
1319 sqlerrm);
1320 raise;
1321 return(null);
1322 end getType;
1323 function getVersionStartDate(ruleIdIn integer) return varchar2 as
1324 startDate date;
1325 stringStartDate varchar2(50);
1326 begin
1327 select start_date
1328 into startDate
1329 from ame_rules
1330 where
1331 rule_id = ruleIdIn and
1332 ((sysdate between start_date and
1333 nvl(end_date - ame_util.oneSecond, sysdate)) or
1334 (sysdate < start_date and
1335 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1336 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
1337 return(stringStartDate);
1338 exception
1339 when others then
1340 rollback;
1341 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1342 routineNameIn => 'getVersionStartDate',
1343 exceptionNumberIn => sqlcode,
1344 exceptionStringIn => '(rule ID ' ||
1345 ruleIdIn||
1346 ') ' ||
1347 sqlerrm);
1348 raise;
1349 return(null);
1350 end getVersionStartDate;
1351 function hasATUsageForRuleType(ruleTypeIn in integer,
1352 actionTypeIdsIn in ame_util.idList) return boolean as
1353 cursor actionTypeUsagesCur(actionTypeIdIn in integer) is
1354 select rule_type
1355 from ame_action_type_usages
1356 where
1357 action_type_id = actionTypeIdIn and
1358 sysdate between start_date and
1359 nvl(end_date - ame_util.oneSecond, sysdate);
1360 begin
1361 for i in 1..actionTypeIdsIn.count loop
1362 for actionTypeUsagesRec in actionTypeUsagesCur(actionTypeIdIn => actionTypeIdsIn(i)) loop
1363 if(ruleTypeIn = actionTypeUsagesRec.rule_type) then
1364 return(true);
1365 end if;
1366 end loop;
1367 end loop;
1368 return(false);
1369 exception
1370 when others then
1371 rollback;
1372 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1373 routineNameIn => 'hasATUsageForRuleType',
1374 exceptionNumberIn => sqlcode,
1375 exceptionStringIn => sqlerrm);
1376 raise;
1377 return(false);
1378 end hasATUsageForRuleType;
1379 function hasATUsageForRuleType2(ruleTypeIn in integer,
1380 actionIdsIn in ame_util.idList) return boolean as
1381 cursor actionTypeUsagesCur(actionIdIn in integer) is
1382 select ame_action_type_usages.rule_type
1383 from ame_action_type_usages,
1384 ame_action_types,
1385 ame_actions
1386 where
1387 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
1388 ame_action_types.action_type_id = ame_actions.action_type_id and
1389 ame_actions.action_id = actionIdIn and
1390 sysdate between ame_action_type_usages.start_date and
1391 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
1392 sysdate between ame_action_types.start_date and
1393 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1394 sysdate between ame_actions.start_date and
1395 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1396 begin
1397 /*
1398 With exception of the combination rule type, a rule must have at least
1399 one action of an action type that has an action-type usage for
1400 the rule's type.
1401 */
1402 if(ruleTypeIn = ame_util.combinationRuleType) then
1403 return(true);
1404 end if;
1405 for i in 1..actionIdsIn.count loop
1406 for actionTypeUsagesRec in actionTypeUsagesCur(actionIdIn => actionIdsIn(i)) loop
1407 if(ruleTypeIn = actionTypeUsagesRec.rule_type) then
1408 return(true);
1409 end if;
1410 end loop;
1411 end loop;
1412 return(false);
1413 exception
1414 when others then
1415 rollback;
1416 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1417 routineNameIn => 'hasATUsageForRuleType2',
1418 exceptionNumberIn => sqlcode,
1419 exceptionStringIn => sqlerrm);
1420 raise;
1421 return(false);
1422 end hasATUsageForRuleType2;
1423 function hasExceptionCondition(conditionIdsIn in ame_util.idList) return boolean as
1424 begin
1425 for i in 1..conditionIdsIn.count loop
1426 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) =
1427 ame_util.exceptionConditionType) then
1428 return(true);
1429 end if;
1430 end loop;
1431 return(false);
1432 exception
1433 when others then
1434 rollback;
1435 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1436 routineNameIn => 'hasExceptionCondition',
1437 exceptionNumberIn => sqlcode,
1438 exceptionStringIn => sqlerrm);
1439 raise;
1440 return(false);
1441 end hasExceptionCondition;
1442 function hasListModCondition(conditionIdsIn in ame_util.idList) return boolean as
1443 begin
1444 for i in 1..conditionIdsIn.count loop
1445 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) =
1446 ame_util.listMOdConditionType) then
1447 return(true);
1448 end if;
1449 end loop;
1450 return(false);
1451 exception
1452 when others then
1453 rollback;
1454 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1455 routineNameIn => 'hasListModCondition',
1456 exceptionNumberIn => sqlcode,
1457 exceptionStringIn => sqlerrm);
1458 raise;
1459 return(false);
1460 end hasListModCondition;
1461 function hasListModCondition2(ruleIdIn in integer) return boolean as
1462 conditionCount integer;
1463 begin
1464 select count(*)
1465 into conditionCount
1466 from ame_conditions,
1467 ame_condition_usages
1468 where
1469 ame_conditions.condition_id = ame_condition_usages.condition_id and
1470 ame_conditions.condition_type = ame_util.listModConditionType and
1471 ame_condition_usages.rule_id = ruleIdIn and
1472 (ame_conditions.start_date <= sysdate and
1473 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
1474 ((sysdate between ame_condition_usages.start_date and
1475 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1476 (sysdate < ame_condition_usages.start_date and
1477 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1478 ame_condition_usages.start_date + ame_util.oneSecond)));
1479 if(conditionCount > 0) then
1480 return(true);
1481 else
1482 return(false);
1483 end if;
1484 exception
1485 when others then
1486 rollback;
1487 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1488 routineNamein => 'hasListModCondition2',
1489 exceptionNumberIn => sqlcode,
1490 exceptionStringIn => '(rule ID ' ||
1491 ruleIdIn||
1492 ') ' ||
1493 sqlerrm);
1494 raise;
1495 return(null);
1496 end hasListModCondition2;
1497 function hasNonProductionActions(actionIdsIn in ame_util.idList) return boolean as
1498 actionTypeId integer;
1499 begin
1500 for i in 1 .. actionIdsIn.count loop
1501 actionTypeId :=
1502 ame_action_pkg.getActionTypeIdById(actionIdIn => actionIdsIn(i));
1503 if(ame_action_pkg.getAllowedRuleType(actionTypeIdIn => actionTypeId) <>
1504 ame_util.productionRuleType) then
1505 return(true);
1506 end if;
1507 end loop;
1508 return(false);
1509 exception
1510 when others then
1511 rollback;
1512 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1513 routineNameIn => 'hasNonProductionActions',
1514 exceptionNumberIn => sqlcode,
1515 exceptionStringIn => sqlerrm);
1516 raise;
1517 return(false);
1518 end hasNonProductionActions;
1519 function hasNonProductionActionTypes(actionTypeIdsIn in ame_util.idList) return boolean as
1520 begin
1521 for i in 1 .. actionTypeIdsIn.count loop
1522 if(ame_action_pkg.getAllowedRuleType(actionTypeIdIn => actionTypeIdsIn(i)) <>
1523 ame_util.productionRuleType) then
1524 return(true);
1525 end if;
1526 end loop;
1527 return(false);
1528 exception
1529 when others then
1530 rollback;
1531 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1532 routineNameIn => 'hasNonProductionActionTypes',
1533 exceptionNumberIn => sqlcode,
1534 exceptionStringIn => sqlerrm);
1535 raise;
1536 return(false);
1537 end hasNonProductionActionTypes;
1538 function hasSubOrListModAction(ruleIdIn in integer) return boolean as
1539 subOrListModActionCount integer;
1540 begin
1541 select count(distinct ame_action_types.action_type_id)
1542 into subOrListModActionCount
1543 from ame_action_usages,
1544 ame_actions,
1545 ame_action_types,
1546 ame_action_type_usages
1547 where ame_action_usages.rule_id = ruleIdIn and
1548 ame_action_usages.action_id = ame_actions.action_id and
1549 ame_action_types.action_type_id = ame_actions.action_type_id and
1550 ame_action_type_usages.action_type_id = ame_action_types.action_type_id and
1551 ame_action_type_usages.rule_type in (ame_util.substitutionRuleType,
1552 ame_util.listModRuleType) and
1553 sysdate between ame_action_usages.start_date and
1554 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1555 sysdate between ame_action_type_usages.start_date and
1556 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
1557 sysdate between ame_action_types.start_date and
1558 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1559 sysdate between ame_actions.start_date and
1560 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1561 if(subOrListModActionCount > 0) then
1562 return(true);
1563 else
1564 return(false);
1565 end if;
1566 exception
1567 when others then
1568 rollback;
1569 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
1570 routineNameIn => 'hasSubOrListModAction',
1571 exceptionNumberIn => sqlcode,
1572 exceptionStringIn => sqlerrm);
1573 raise;
1574 return(false);
1575 end hasSubOrListModAction;
1576 function isAtLeastOneICAttrSelected(itemClassIdIn in integer,
1577 attributeIdsIn in ame_util.idList) return boolean as
1578 begin
1579 for i in 1..attributeIdsIn.count loop
1580 if(ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdsIn(i)) =
1581 itemClassIdIn) then
1582 return(true);
1583 exit;
1584 end if;
1585 end loop;
1586 return(false);
1587 exception
1588 when others then
1589 rollback;
1590 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1591 routineNamein => 'isAtLeastOneICAttrSelected',
1592 exceptionNumberIn => sqlcode,
1593 exceptionStringIn => sqlerrm);
1594 raise;
1595 return(true); /* conservative: avoids allowing deletion if might still be in use */
1596 end isAtLeastOneICAttrSelected;
1597 function isAtLeastOneICCondSelected(itemClassIdIn in integer,
1598 conditionIdsIn in ame_util.idList) return boolean as
1599 attributeId integer;
1600 begin
1601 for i in 1..conditionIdsIn.count loop
1602 attributeId := ame_condition_pkg.getAttributeId(conditionIdIn => conditionIdsIn(i));
1603 if(ame_attribute_pkg.getItemClassId(attributeIdIn => attributeId) =
1604 itemClassIdIn) then
1605 return(true);
1606 exit;
1607 end if;
1608 end loop;
1609 return(false);
1610 exception
1611 when others then
1612 rollback;
1613 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1614 routineNamein => 'isAtLeastOneICCondSelected',
1615 exceptionNumberIn => sqlcode,
1616 exceptionStringIn => sqlerrm);
1617 raise;
1618 return(true); /* conservative: avoids allowing deletion if might still be in use */
1619 end isAtLeastOneICCondSelected;
1620 function isInUse(ruleIdIn in integer) return boolean as
1621 useCount integer;
1622 begin
1623 select count(*)
1624 into useCount
1625 from ame_rule_usages
1626 where
1627 rule_id = ruleIdIn and
1628 ((sysdate between start_date and
1629 nvl(end_date - ame_util.oneSecond, sysdate)) or
1630 (sysdate < start_date and
1631 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1632 if(useCount > 0) then
1633 return(true);
1634 end if;
1635 return(false);
1636 exception
1637 when others then
1638 rollback;
1639 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1640 routineNamein => 'isinUse',
1641 exceptionNumberIn => sqlcode,
1642 exceptionStringIn => '(rule ID ' ||
1643 ruleIdIn||
1644 ') ' ||
1645 sqlerrm);
1646 raise;
1647 return(true); /* conservative: avoids allowing deletion if might still be in use */
1648 end isinUse;
1649 function isInUseByOtherApps(ruleIdIn in integer,
1650 applicationIdIn in integer) return boolean as
1651 useCount integer;
1652 begin
1653 select count(*)
1654 into useCount
1655 from ame_rule_usages
1656 where
1657 rule_id = ruleIdIn and
1658 item_id <> applicationIdIn and
1659 ((sysdate between start_date and
1660 nvl(end_date - ame_util.oneSecond, sysdate)) or
1661 (sysdate < start_date and
1662 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1663 if(useCount > 0) then
1664 return(true);
1665 end if;
1666 return(false);
1667 exception
1668 when others then
1669 rollback;
1670 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1671 routineNamein => 'isInUseByOtherApps',
1672 exceptionNumberIn => sqlcode,
1673 exceptionStringIn => '(rule ID ' ||
1674 ruleIdIn||
1675 ') ' ||
1676 sqlerrm);
1677 raise;
1678 return(true); /* conservative: avoids allowing deletion if might still be in use */
1679 end isInUseByOtherApps;
1680 function lastConditionDeleted(conditionIdListIn in ame_util.idList,
1681 deletedListIn in ame_util.stringList) return boolean as
1682 conditionCount integer;
1683 deleteCount integer;
1684 begin
1685 conditionCount := conditionIdListIn.count;
1686 deleteCount := 0;
1687 for i in 1..deletedListIn.count loop
1688 if(deletedListIn(i)) like 'con%' then
1689 deleteCount := deleteCount + 1;
1690 end if;
1691 end loop;
1692 if(conditionCount = deleteCount) then
1693 return(true);
1694 end if;
1695 return(false);
1696 exception
1697 when others then
1698 rollback;
1699 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1700 routineNamein => 'lastConditionDeleted',
1701 exceptionNumberIn => sqlcode,
1702 exceptionStringIn => sqlerrm);
1703 raise;
1704 return(true); /* conservative: avoids allowing deletion */
1705 end lastConditionDeleted;
1706 function lineItemJobLevelChosen(actionTypeIdsIn in ame_util.idList) return boolean as
1707 lineItemJobLevelActionTypeId integer;
1708 begin
1709 lineItemJobLevelActionTypeId :=
1710 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.lineItemJobLevelTypeName);
1711 for i in 1..actionTypeIdsIn.count loop
1712 if(actionTypeIdsIn(i) = lineItemJobLevelActionTypeId) then
1713 return(true);
1714 end if;
1715 end loop;
1716 return(false);
1717 exception
1718 when others then
1719 rollback;
1720 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1721 routineNamein => 'lineItemJobLevelChosen',
1722 exceptionNumberIn => sqlcode,
1723 exceptionStringIn => sqlerrm);
1724 raise;
1725 return(true);
1726 end lineItemJobLevelChosen;
1727 function new(applicationIdIn in integer,
1728 typeIn in integer,
1729 conditionIdsIn in ame_util.idList default ame_util.emptyIdList,
1730 actionIdsIn in ame_util.idList,
1731 ruleKeyIn in varchar2,
1732 descriptionIn in varchar2,
1733 startDateIn in date,
1734 endDateIn in date default null,
1735 ruleIdIn in integer default null,
1736 itemClassIdIn in integer default null,
1737 finalizeIn in boolean default true,
1738 processingDateIn in date default null) return integer as
1739 createdBy integer;
1740 currentUserId integer;
1741 startDateToInsert date;
1742 descriptionInUseException exception;
1743 descriptionLengthException exception;
1744 endDateToInsert date;
1745 errorCode integer;
1746 errorMessage ame_util.longestStringType;
1747 lastIndex integer;
1748 processingDate date;
1749 ruleCount integer;
1750 ruleId integer;
1751 ruleKeyLengthException exception;
1752 startDateException exception;
1753 startDateException1 exception;
1754 tempCount integer;
1755 begin
1756 /* check to see if processingDate has been initialized */
1757 if processingDateIn is null then
1758 processingDate := sysdate;
1759 else
1760 processingDate := processingDateIn;
1761 end if;
1762 /* check to see if description has already been used */
1763 if ame_rule_pkg.descriptionInUse(descriptionIn => descriptionIn) then
1764 raise descriptionInUseException;
1765 end if;
1766 /* Check and set start date. */
1767 if ruleIdIn is null then
1768 if (startDateIn < trunc(processingDate)) then
1769 raise startDateException; /* Start dates should always be today or later. */
1770 elsif(trunc(startDateIn) > trunc(processingDate)) then
1771 startDateToInsert := trunc(startDateIn); /* Truncate future start dates. */
1772 else
1773 startDateToInsert := processingDate; /* Don't truncate start dates that are for today. */
1774 end if;
1775 /* Check and set end date. */
1776 if (endDateIn is null) then
1777 endDateToInsert := null;
1778 elsif(startDateIn < endDateIn) then /* Non-null end dates should follow start dates, and should be truncated. */
1779 endDateToInsert := trunc(endDateIn);
1780 else
1781 raise startDateException1;
1782 end if;
1783 else
1784 startDateToInsert := startDateIn;
1785 endDateToInsert := endDateIn;
1786 end if;
1787 /* misc preparation for inserts */
1788 if(lengthb(ruleKeyIn) > 100) then
1789 raise ruleKeyLengthException;
1790 end if;
1791 if(ame_util.isArgumentTooLong(tableNamein => 'ame_rules',
1792 columnNamein => 'description',
1793 argumentin => descriptionIn)) then
1794 raise descriptionLengthException;
1795 end if;
1796 /*
1797 If any version of the object has created_by = 1, all versions,
1798 including the new version, should. This is a failsafe way to check
1799 whether previous versions of an already end-dated object had
1800 created_by = 1.
1801 */
1802 currentUserId := ame_util.getCurrentUserId;
1803 if(ruleIdIn is null) then
1804 createdBy := currentUserId;
1805 if(ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
1806 /* Use negative rule IDs for developer-seeded rules. */
1807 select count(*)
1808 into ruleCount
1809 from ame_rules
1810 where
1811 ((sysdate between start_date and
1812 nvl(end_date - ame_util.oneSecond, sysdate)) or
1813 (sysdate < start_date and
1814 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1815 if ruleCount = 0 then
1816 ruleId := -1;
1817 else
1818 select min(rule_id) - 1
1819 into ruleId
1820 from ame_rules
1821 where
1822 ((sysdate between start_date and
1823 nvl(end_date - ame_util.oneSecond, sysdate)) or
1824 (sysdate < start_date and
1825 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1826 if(ruleId > -1) then
1827 ruleId := -1;
1828 end if;
1829 end if;
1830 else
1831 select ame_rules_s.nextval into ruleId from dual;
1832 end if;
1833 else
1834 ruleId := ruleIdIn;
1835 select count(*)
1836 into tempCount
1837 from ame_rules
1838 where
1839 rule_id = ruleId and
1840 created_by = ame_util.seededDataCreatedById;
1841 if(tempCount > 0) then
1842 createdBy := ame_util.seededDataCreatedById;
1843 else
1844 createdBy := currentUserId;
1845 end if;
1846 end if;
1847 /* inserts */
1848 insert into ame_rules(rule_id,
1849 rule_type,
1850 rule_key,
1851 action_id,
1852 created_by,
1853 creation_date,
1854 last_updated_by,
1855 last_update_date,
1856 last_update_login,
1857 start_date,
1858 end_date,
1859 description,
1860 item_class_id)
1861 values(ruleId,
1862 typeIn,
1863 ruleKeyIn,
1864 null,
1865 createdBy,
1866 processingDate,
1867 currentUserId,
1868 processingDate,
1869 currentUserId,
1870 startDateToInsert,
1871 endDateToInsert,
1872 descriptionIn,
1873 itemClassIdIn);
1874 if(conditionIdsIn.count > 0) then
1875 for tempIndex in 1 .. conditionIdsIn.count loop
1876 insert into ame_condition_usages(rule_id,
1877 condition_id,
1878 created_by,
1879 creation_date,
1880 last_updated_by,
1881 last_update_date,
1882 last_update_login,
1883 start_date,
1884 end_date)
1885 values(ruleId,
1886 conditionIdsIn(tempIndex),
1887 createdBy,
1888 processingDate,
1889 currentUserId,
1890 processingDate,
1891 currentUserId,
1892 startDateToInsert,
1893 endDateToInsert);
1894 end loop;
1895 end if;
1896 for tempIndex in 1 .. actionIdsIn.count loop
1897 insert into ame_action_usages(rule_id,
1898 action_id,
1899 created_by,
1900 creation_date,
1901 last_updated_by,
1902 last_update_date,
1903 last_update_login,
1904 start_date,
1905 end_date)
1906 values(ruleId,
1907 actionIdsIn(tempIndex),
1908 createdBy,
1909 processingDate,
1910 currentUserId,
1911 processingDate,
1912 currentUserId,
1913 startDateToInsert,
1914 endDateToInsert);
1915 end loop;
1916 /*
1917 AME_STRIPING
1918 if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn)) then
1919 updateRuleStripeSets(applicationIdIn => applicationIdIn,
1920 ruleIdIn => ruleId,
1921 conditionIdsIn => conditionIdsIn);
1922 end if;
1923 */
1924 if(ruleIdIn is null and
1925 finalizeIn) then
1926 commit;
1927 end if;
1928 return(ruleId);
1929 exception
1930 when ruleKeyLengthException then
1931 rollback;
1932 errorCode := -20001;
1933 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1934 messageNameIn => 'AME_400361_RULE_KEY_LONG',
1935 tokenNameOneIn => 'COLUMN_LENGTH',
1936 tokenValueOneIn => 100);
1937 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1938 routineNamein => 'new',
1939 exceptionNumberIn => errorCode,
1940 exceptionStringIn => errorMessage);
1941 raise_application_error(errorCode,
1942 errorMessage);
1943 return(null);
1944 when descriptionInUseException then
1945 rollback;
1946 errorCode := -20001;
1947 errorMessage :=
1948 ame_util.getMessage(applicationShortNameIn => 'PER',
1949 messageNameIn => 'AME_400206_RUL_DESC_IN_USE');
1950 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1951 routineNamein => 'new',
1952 exceptionNumberIn => errorCode,
1953 exceptionStringIn => errorMessage);
1954 raise_application_error(errorCode,
1955 errorMessage);
1956 return(null);
1957 when descriptionLengthException then
1958 rollback;
1959 errorCode := -20001;
1960 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1961 messageNameIn => 'AME_400207_RUL_DESC_LONG',
1962 tokenNameOneIn => 'COLUMN_LENGTH',
1963 tokenValueOneIn => ame_util.getColumnLength(tableNamein => 'ame_rules',
1964 columnNamein => 'description'));
1965 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1966 routineNamein => 'new',
1967 exceptionNumberIn => errorCode,
1968 exceptionStringIn => errorMessage);
1969 raise_application_error(errorCode,
1970 errorMessage);
1971 return(null);
1972 when startDateException then
1973 rollback;
1974 errorCode := -20001;
1975 errorMessage :=
1976 ame_util.getMessage(applicationShortNameIn => 'PER',
1977 messageNameIn => 'AME_400208_RUL_STRT_PREC_TDY');
1978 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1979 routineNamein => 'new',
1980 exceptionNumberIn => errorCode,
1981 exceptionStringIn => errorMessage);
1982 raise_application_error(errorCode,
1983 errorMessage);
1984 return(null);
1985 when startDateException1 then
1986 rollback;
1987 errorCode := -20001;
1988 errorMessage :=
1989 ame_util.getMessage(applicationShortNameIn => 'PER',
1990 messageNameIn => 'AME_400209_RUL_STRT_PREC_END');
1991 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
1992 routineNamein => 'new',
1993 exceptionNumberIn => errorCode,
1994 exceptionStringIn => errorMessage);
1995 raise_application_error(errorCode,
1996 errorMessage);
1997 return(null);
1998 when others then
1999 rollback;
2000 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2001 routineNamein => 'new',
2002 exceptionNumberIn => sqlcode,
2003 exceptionStringIn => '(rule ID ' ||
2004 ruleIdIn||
2005 ') ' ||
2006 sqlerrm);
2007 raise;
2008 return(null);
2009 end new;
2010 /*
2011 AME_STRIPING
2012 procedure newRuleStripeSet(applicationIdIn in integer,
2013 ruleIdIn in integer,
2014 stripeSetIdIn in integer) as
2015 attributeCount integer;
2016 currentUserId integer;
2017 endDate date;
2018 errorCode integer;
2019 errorMessage varchar2(200);
2020 lineItemAttribute ame_attributes.line_item%type;
2021 newStartDate date;
2022 queryString ame_attribute_usages.query_string%type;
2023 staticUsage ame_attribute_usages.is_static%type;
2024 stripingAttributeIds ame_util.idList;
2025 stripingAttributeNames ame_util.stringList;
2026 useCount integer;
2027 begin
2028 currentUserId := ame_util.getCurrentUserId;
2029 select count(*)
2030 into useCount
2031 from ame_rule_stripe_sets
2032 where
2033 rule_id = ruleIdIn and
2034 stripe_set_id = stripeSetIdIn and
2035 (start_date <= sysdate and
2036 (end_date is null or sysdate < end_date));
2037 if(useCount > 0) then
2038 return;
2039 end if;
2040 insert into ame_rule_stripe_sets(rule_id,
2041 stripe_set_id,
2042 created_by,
2043 creation_date,
2044 last_updated_by,
2045 last_update_date,
2046 last_update_login,
2047 security_group_id,
2048 start_date,
2049 end_date)
2050 values(ruleIdIn,
2051 stripeSetIdIn,
2052 currentUserId,
2053 sysdate,
2054 currentUserId,
2055 sysdate,
2056 currentUserId,
2057 null,
2058 sysdate,
2059 null);
2060 ame_admin_pkg.updateStripingAttUseCount(applicationIdIn => applicationIdIn);
2061 commit;
2062 exception
2063 when others then
2064 rollback;
2065 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2066 routineNamein => 'newRuleStripeSet',
2067 exceptionNumberIn => sqlcode,
2068 exceptionStringIn => '(rule ID ' ||
2069 ruleIdIn||
2070 ') ' ||
2071 sqlerrm);
2072 raise;
2073 end newRuleStripeSet;
2074 */
2075 function newRuleUsage(itemIdIn in integer,
2076 ruleIdIn in integer,
2077 startDateIn in date,
2078 endDateIn in date default null,
2079 categoryIn in varchar2 default null,
2080 priorityIn in varchar2 default null,
2081 finalizeIn in boolean default false,
2082 parentVersionStartDateIn in date,
2083 processingDateIn in date default null,
2084 updateParentObjectIn in boolean default false) return boolean as
2085 cursor startDateCursor is
2086 select start_date
2087 from ame_rules
2088 where
2089 rule_id = ruleIdIn and
2090 ((sysdate between start_date and
2091 nvl(end_date - ame_util.oneSecond, sysdate)) or
2092 (sysdate < start_date and
2093 start_date < nvl(end_date,start_date + ame_util.oneSecond)))
2094 for update;
2095 actionIdList ame_util.idList;
2096 description ame_rules.description%type;
2097 approvalCategory ame_util.stringType;
2098 conditionIdList ame_util.idList;
2099 createdBy integer;
2100 createUsage boolean;
2101 currentUserId integer;
2102 newRuleEndDate ame_rules.end_date%type;
2103 newRuleStartDate ame_rules.start_date%type;
2104 errorCode integer;
2105 errorMessage ame_util.longestStringType;
2106 invalidDateException exception;
2107 itemClassId integer;
2108 itemIdCount integer;
2109 objectVersionNoDataException exception;
2110 invalidPriorityException exception;
2111 ruleKey ame_rules.rule_key%type;
2112 ruleId ame_rules.rule_id%type;
2113 ruleType ame_rules.rule_type%type;
2114 ruleStartDate date;
2115 usageExistsException exception;
2116 startDateException exception;
2117 startDateException1 exception;
2118 tempCount integer;
2119 useCount integer;
2120 startDateToInsert date;
2121 endDateToInsert date;
2122 processingDate date;
2123 endDate date;
2124 overlappingUsage number;
2125 usageAlreadyExists exception;
2126 usageExistsWithDiffPriority exception;
2127 usageOverlaps exception;
2128 begin
2129 /* check to see if processingDate has been initialized */
2130 if processingDateIn is null then
2131 processingDate := sysdate;
2132 else
2133 processingDate := processingDateIn;
2134 end if;
2135 /* Check to make sure that there are no overlapping timespans */
2136 overlappingUsage := checkRuleUsageExists(applicationIdIn => itemIdIn,
2137 ruleIdIn => ruleIdIn,
2138 startDateIn => startDateIn,
2139 endDateIn => endDateIn,
2140 processingDateIn => processingDate,
2141 priorityIn => priorityIn ) ;
2142 if overlappingUsage = 1 then
2143 raise usageAlreadyExists;
2144 elsif overlappingUsage = 2 then
2145 raise usageExistsWithDiffPriority;
2146 elsif overlappingUsage = 3 then
2147 raise usageOverlaps;
2148 end if;
2149 if(finalizeIn) then
2150 open startDateCursor;
2151 fetch startDateCursor into ruleStartDate;
2152 if startDateCursor%notfound then
2153 raise objectVersionNoDataException;
2154 end if;
2155 if(parentVersionStartDateIn <> ruleStartDate) then
2156 close startDateCursor;
2157 raise ame_util.objectVersionException;
2158 end if;
2159 end if;
2160 ruleKey := getRuleKey(ruleIdIn => ruleIdIn);
2161 ruleType := getRuleType(ruleIdIn => ruleIdIn);
2162 itemClassId := getItemClassId(ruleIdIn => ruleIdIn);
2163 if(ame_rule_pkg.useRulePriorityMode(applicationIdIn => itemIdIn,
2164 ruleTypeIn => ruleType)) then
2165 if (priorityIn is null) or
2166 (not(ame_util.isANumber(stringIn => priorityIn,
2167 allowDecimalsIn => false,
2168 allowNegativesIn => false))) then
2169 raise invalidPriorityException;
2170 end if;
2171 end if;
2172 /* Check and set start date. for rule usage */
2173 if (startDateIn < trunc(processingDate)) then
2174 raise startDateException; /* Start dates should always be today or later. */
2175 elsif(trunc(startDateIn) > trunc(processingDate)) then
2176 startDateToInsert := trunc(startDateIn); /* Truncate future start dates. */
2177 else
2178 startDateToInsert := processingDate; /* Don't truncate start dates that are for today. */
2179 end if;
2180 /* Check and set end date for rule usage. */
2181 if(endDateIn is null) then
2182 endDateToInsert := null;
2183 elsif(trunc(endDateIn)) = trunc(processingDate) then
2184 endDateToInsert := processingDate;
2185 elsif(startDateIn < endDateIn) then /* Non-null end dates should follow start dates, and should be truncated. */
2186 endDateToInsert := trunc(endDateIn);
2187 else
2188 raise startDateException1;
2189 end if;
2190 if(endDateToInsert = startDateToInsert) then
2191 raise invalidDateException;
2192 end if;
2193 select count(*)
2194 into useCount
2195 from ame_rule_usages
2196 where
2197 rule_id = ruleIdIn and
2198 item_id = itemIdIn and
2199 trunc(start_date) = startDateIn and
2200 nvl(end_date, processingDate) = nvl(endDateIn, processingDate) and
2201 ((sysdate between start_date and
2202 nvl(end_date - ame_util.oneSecond, sysdate)) or
2203 (sysdate < start_date and
2204 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2205 if(useCount > 0) then
2206 raise usageExistsException;
2207 end if;
2208 currentUserId := ame_util.getCurrentUserId;
2209 select count(*)
2210 into tempCount
2211 from ame_rule_usages
2212 where
2213 rule_id = ruleIdIn and
2214 item_id = itemIdIn and
2215 created_by = ame_util.seededDataCreatedById;
2216 if(tempCount > 0) then
2217 createdBy := ame_util.seededDataCreatedById;
2218 else
2219 createdBy := currentUserId;
2220 end if;
2221 approvalCategory := categoryIn;
2222 /* The category should default to ame_util.approvalApproverCategory
2223 if the categoryIn value is null for rule types other than
2224 list modification and substitution */
2225 if(ruleType in (ame_util.authorityRuleType,
2226 ame_util.exceptionRuleType,
2227 ame_util.preListGroupRuleType,
2228 ame_util.postListGroupRuleType,
2229 ame_util.combinationRuleType)) then
2230 if(categoryIn is null) then
2231 approvalCategory := ame_util.approvalApproverCategory;
2232 end if;
2233 end if;
2234 insert into ame_rule_usages(item_id,
2235 rule_id,
2236 created_by,
2237 creation_date,
2238 last_updated_by,
2239 last_update_date,
2240 last_update_login,
2241 start_date,
2242 end_date,
2243 priority,
2244 approver_category)
2245 values(itemIdIn,
2246 ruleIdIn,
2247 currentUserId,
2248 processingDate,
2249 currentUserId,
2250 processingDate,
2251 currentUserId,
2252 startDateToInsert,
2253 endDateToInsert,
2254 priorityIn,
2255 approvalCategory);
2256 /* ruleType := getRuleType(ruleIdIn => ruleIdIn); */
2257 if updateParentObjectIn then
2258 endDate := processingDate;
2259 description := getDescription(ruleIdIn => ruleIdIn);
2260 newRuleStartDate := getnewRuleStartDate(ruleIdIn => ruleIdIn,
2261 processingDateIn => processingDate);
2262 newRuleEndDate := getnewRuleEndDate(ruleIdIn => ruleIdIn,
2263 processingDateIn => processingDate);
2264 getActionIds(ruleIdIn => ruleIdIn,
2265 actionIdListOut => actionIdList);
2266 for i in 1..actionIdList.count loop
2267 update ame_action_usages
2268 set
2269 last_updated_by = currentUserId,
2270 last_update_date = processingDate,
2271 last_update_login = currentUserId,
2272 end_date = endDate
2273 where
2274 rule_id = ruleIdIn and
2275 action_id = actionIdList(i) and
2276 ((processingDate between start_date and
2277 nvl(end_date - ame_util.oneSecond, processingDate)) or
2278 (processingDate < start_date and
2279 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2280 end loop;
2281 getConditionIds(ruleIdIn => ruleIdIn,
2282 conditionIdListOut => conditionIdList);
2283 if conditionIdList.count > 0 then
2284 for i in 1..conditionIdList.count loop
2285 update ame_condition_usages
2286 set
2287 last_updated_by = currentUserId,
2288 last_update_date = processingDate,
2289 last_update_login = currentUserId,
2290 end_date = endDate
2291 where
2292 rule_id = ruleIdIn and
2293 condition_id = conditionIdList(i) and
2294 ((processingDate between start_date and
2295 nvl(end_date - ame_util.oneSecond, processingDate)) or
2296 (processingDate < start_date and
2297 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2298 end loop;
2299 end if;
2300 update ame_rules
2301 set
2302 last_updated_by = currentUserId,
2303 last_update_date = processingDate,
2304 last_update_login = currentUserId,
2305 end_date = endDate
2306 where
2307 rule_id = ruleIdIn and
2308 ((processingDate between start_date and
2309 nvl(end_date - ame_util.oneSecond, processingDate)) or
2310 (processingDate < start_date and
2311 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2312 ruleId := new(applicationIdIn => itemIdIn,
2313 typeIn => ruleType,
2314 conditionIdsIn => conditionIdList,
2315 actionIdsIn => actionIdList,
2316 itemClassIdIn => itemClassId,
2317 ruleKeyIn => ruleKey,
2318 descriptionIn => description,
2319 startDateIn => newRuleStartDate,
2320 endDateIn => newRuleEndDate,
2321 ruleIdIn => ruleIdIn,
2322 finalizeIn => false,
2323 processingDateIn => processingDateIn);
2324 end if;
2325 changeAttributeUseCounts(ruleIdIn => ruleIdIn,
2326 applicationIdIn => itemIdIn,
2327 finalizeIn => false);
2328 if(finalizeIn) then
2329 commit;
2330 close startDateCursor;
2331 end if;
2332 return(true);
2333 exception
2334 when ame_util.objectVersionException then
2335 rollback;
2336 if(startDateCursor%isOpen) then
2337 close startDateCursor;
2338 end if;
2339 errorCode := -20001;
2340 errorMessage :=
2341 ame_util.getMessage(applicationShortNameIn => 'PER',
2342 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2343 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2344 routineNameIn => 'newRuleUsage',
2345 exceptionNumberIn => errorCode,
2346 exceptionStringIn => errorMessage);
2347 raise_application_error(errorCode,
2348 errorMessage);
2349 return(null);
2350 when usageAlreadyExists then
2351 rollback;
2352 errorCode := -20001;
2353 errorMessage :=
2354 ame_util.getMessage(applicationShortNameIn => 'PER',
2355 messageNameIn => 'AME_400327_RULE_USG_EXST_LIFE');
2356 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2357 routineNameIn => 'newRuleUsage',
2358 exceptionNumberIn => errorCode,
2359 exceptionStringIn => errorMessage);
2360 raise_application_error(errorCode,
2361 errorMessage);
2362 return(null);
2363 when usageExistsWithDiffPriority then
2364 rollback;
2365 errorCode := -20001;
2366 errorMessage :=
2367 ame_util.getMessage(applicationShortNameIn => 'PER',
2368 messageNameIn => 'AME_400328_RULE_USG_DIFF_PRIOR');
2369 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2370 routineNameIn => 'newRuleUsage',
2371 exceptionNumberIn => errorCode,
2372 exceptionStringIn => errorMessage);
2373 raise_application_error(errorCode,
2374 errorMessage);
2375 return(null);
2376 when usageOverlaps then
2377 rollback;
2378 errorCode := -20001;
2379 errorMessage :=
2380 ame_util.getMessage(applicationShortNameIn => 'PER',
2381 messageNameIn => 'AME_400329_RULE_USG_OVER_LIFE');
2382 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2383 routineNameIn => 'newRuleUsage',
2384 exceptionNumberIn => errorCode,
2385 exceptionStringIn => errorMessage);
2386 raise_application_error(errorCode,
2387 errorMessage);
2388 return(null);
2389 when invalidPriorityException then
2390 rollback;
2391 if(startDateCursor%isOpen) then
2392 close startDateCursor;
2393 end if;
2394 errorCode := -20001;
2395 errorMessage :=
2396 ame_util.getMessage(applicationShortNameIn => 'PER',
2397 messageNameIn => 'AME_400288_RUL_PRI_NOT_VAL');
2398 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2399 routineNameIn => 'newRuleUsage',
2400 exceptionNumberIn => errorCode,
2401 exceptionStringIn => errorMessage);
2402 raise_application_error(errorCode,
2403 errorMessage);
2404 return(null);
2405 when startDateException then
2406 rollback;
2407 errorCode := -20001;
2408 errorMessage :=
2409 ame_util.getMessage(applicationShortNameIn => 'PER',
2410 messageNameIn => 'AME_400208_RUL_STRT_PREC_TDY');
2411 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2412 routineNamein => 'newRuleUsage',
2413 exceptionNumberIn => errorCode,
2414 exceptionStringIn => errorMessage);
2415 raise_application_error(errorCode,
2416 errorMessage);
2417 return(null);
2418 when startDateException1 then
2419 rollback;
2420 errorCode := -20001;
2421 errorMessage :=
2422 ame_util.getMessage(applicationShortNameIn => 'PER',
2423 messageNameIn => 'AME_400214_RUL_STRT_LESS_END');
2424 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2425 routineNamein => 'newRuleUsage',
2426 exceptionNumberIn => errorCode,
2427 exceptionStringIn => errorMessage);
2428 raise_application_error(errorCode,
2429 errorMessage);
2430 when objectVersionNoDataException then
2431 rollback;
2432 if(startDateCursor%isOpen) then
2433 close startDateCursor;
2434 end if;
2435 errorCode := -20001;
2436 errorMessage :=
2437 ame_util.getMessage(applicationShortNameIn => 'PER',
2438 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2439 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2440 routineNameIn => 'newRuleUsage',
2441 exceptionNumberIn => errorCode,
2442 exceptionStringIn => errorMessage);
2443 raise_application_error(errorCode,
2444 errorMessage);
2445 return(null);
2446 when usageExistsException then
2447 rollback;
2448 errorCode := -20001;
2449 errorMessage :=
2450 ame_util.getMessage(applicationShortNameIn => 'PER',
2451 messageNameIn => 'AME_400210_RUL_USAGE_EXISTS');
2452 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2453 routineNamein => 'newRuleUsage',
2454 exceptionNumberIn => errorCode,
2455 exceptionStringIn => errorMessage);
2456 raise_application_error(errorCode,
2457 errorMessage);
2458 return(null);
2459 when invalidDateException then
2460 rollback;
2461 errorCode := -20001;
2462 errorMessage :=
2463 ame_util.getMessage(applicationShortNameIn => 'PER',
2464 messageNameIn => 'AME_400437_RULE_USAGE_END_DATE');
2465 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
2466 routineNameIn => 'newRuleUsage',
2467 exceptionNumberIn => errorCode,
2468 exceptionStringIn => errorMessage);
2469 raise_application_error(errorCode,
2470 errorMessage);
2471 when others then
2472 rollback;
2473 if(startDateCursor%isOpen) then
2474 close startDateCursor;
2475 end if;
2476 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2477 routineNamein => 'newRuleUsage',
2478 exceptionNumberIn => sqlcode,
2479 exceptionStringIn => '(rule ID ' ||
2480 ruleIdIn||
2481 ') ' ||
2482 sqlerrm);
2483 raise;
2484 return(null);
2485 end newRuleUsage;
2486 function nonFinalAuthorityActionType(actionTypeIdsIn in ame_util.idList) return boolean as
2487 nonFinalAuthActionTypeId integer;
2488 begin
2489 nonFinalAuthActionTypeId :=
2490 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.nonFinalAuthority);
2491 for i in 1..actionTypeIdsIn.count loop
2492 if(actionTypeIdsIn(i) = nonFinalAuthActionTypeId) then
2493 return(true);
2494 end if;
2495 end loop;
2496 return(false);
2497 exception
2498 when others then
2499 rollback;
2500 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2501 routineNamein => 'nonFinalAuthorityActionType',
2502 exceptionNumberIn => sqlcode,
2503 exceptionStringIn => sqlerrm);
2504 raise;
2505 return(true);
2506 end nonFinalAuthorityActionType;
2507 function nonFinalAuthorityActionType2(ruleIdIn in integer) return boolean as
2508 cursor actionTypeIdsCursor(ruleIdIn in integer) is
2509 select distinct(ame_action_types.action_type_id) action_type_id
2510 from ame_action_types,
2511 ame_actions,
2512 ame_action_usages
2513 where
2514 ame_action_types.action_type_id = ame_actions.action_type_id and
2515 ame_actions.action_id = ame_action_usages.action_id and
2516 ame_action_usages.rule_id = ruleIdIn and
2517 sysdate between ame_action_usages.start_date and
2518 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
2519 sysdate between ame_action_types.start_date and
2520 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2521 sysdate between ame_actions.start_date and
2522 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
2523 nonFinalAuthActionTypeId integer;
2524 begin
2525 nonFinalAuthActionTypeId :=
2526 ame_action_pkg.getActionTypeIdByName(actionTypeNameIn => ame_util.nonFinalAuthority);
2527 for actionTypeIdsRec in actionTypeIdsCursor(ruleIdIn => ruleIdIn) loop
2528 if(actionTypeIdsRec.action_type_id = nonFinalAuthActionTypeId) then
2529 return(true);
2530 end if;
2531 end loop;
2532 return(false);
2533 exception
2534 when others then
2535 rollback;
2536 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2537 routineNamein => 'nonFinalAuthorityActionType2',
2538 exceptionNumberIn => sqlcode,
2539 exceptionStringIn => sqlerrm);
2540 raise;
2541 return(true);
2542 end nonFinalAuthorityActionType2;
2543 function ordinaryConditionsExist(ruleIdIn in integer) return boolean as
2544 conditionCount integer;
2545 begin
2546 select count(*)
2547 into conditionCount
2548 from ame_conditions,
2549 ame_condition_usages
2550 where
2551 ame_conditions.condition_id = ame_condition_usages.condition_id and
2552 ame_condition_usages.rule_id = ruleIdIn and
2553 ame_conditions.condition_type = ame_util.ordinaryConditionType and
2554 sysdate between ame_conditions.start_date and
2555 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
2556 ((sysdate between ame_condition_usages.start_date and
2557 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
2558 (sysdate < ame_condition_usages.start_date and
2559 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
2560 ame_condition_usages.start_date + ame_util.oneSecond)));
2561 if(conditionCount > 0) then
2562 return(true);
2563 end if;
2564 return(false);
2565 exception
2566 when others then
2567 rollback;
2568 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2569 routineNamein => 'ordinaryConditionsExist',
2570 exceptionNumberIn => sqlcode,
2571 exceptionStringIn => sqlerrm);
2572 raise;
2573 return(true); /* conservative: avoids allowing deletion */
2574 end ordinaryConditionsExist;
2575 function ruleAlreadyExistsForTransType(typeIn in varchar2,
2576 conditionIdListIn in ame_util.idList,
2577 actionIdListIn in ame_util.idList,
2578 applicationIdIn in integer,
2579 itemClassIdIn in integer default null) return boolean as
2580 cursor ruleIdCursor(typeIn in varchar2,
2581 applicationIdIn in integer,
2582 itemClassIdIn in integer default null) is
2583 select ame_rules.rule_id
2584 from ame_rules,
2585 ame_rule_usages
2586 where
2587 item_id = applicationIdIn and
2588 rule_type = typeIn and
2589 (item_class_id is null or
2590 item_class_id = itemClassIdIn) and
2591 ame_rules.rule_id = ame_rule_usages.rule_id and
2592 ((sysdate between ame_rules.start_date and
2593 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
2594 (sysdate < ame_rules.start_date and
2595 ame_rules.start_date < nvl(ame_rules.end_date,
2596 ame_rules.start_date + ame_util.oneSecond))) and
2597 ((sysdate between ame_rule_usages.start_date and
2598 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
2599 (sysdate < ame_rule_usages.start_date and
2600 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
2601 ame_rule_usages.start_date + ame_util.oneSecond)));
2602 actionIdList1 ame_util.idList;
2603 actionIdList2 ame_util.idList;
2604 actionIdMatch boolean;
2605 conditionIdList1 ame_util.idList;
2606 conditionIdList2 ame_util.idList;
2607 conditionIdMatch boolean;
2608 ruleId ame_rules.rule_id%type;
2609 begin
2610 ruleId := null;
2611 conditionIdList1 := conditionIdListIn;
2612 ame_util.sortIdListInPlace(idListInOut => conditionIdList1);
2613 actionIdList1 := actionIdListIn;
2614 ame_util.sortIdListInPlace(idListInOut => actionIdList1);
2615 actionIdMatch := false;
2616 conditionIdMatch := false;
2617 for tempRuleId in ruleIdCursor(typeIn => typeIn,
2618 applicationIdIn => applicationIdIn,
2619 itemClassIdIn => itemClassIdIn) loop
2620 getConditionIds(ruleIdIn => tempRuleId.rule_id,
2621 conditionIdListOut => conditionIdList2);
2622 ame_util.sortIdListInPlace(idListInOut => conditionIdList2);
2623 if(ame_util.idListsMatch(idList1InOut => conditionIdList1,
2624 idList2InOut => conditionIdList2,
2625 sortList1In => false,
2626 sortList2In => false)) then
2627 conditionIdMatch := true;
2628 end if;
2629 getActionIds(ruleIdIn => tempRuleId.rule_id,
2630 actionIdListOut => actionIdList2);
2631 ame_util.sortIdListInPlace(idListInOut => actionIdList2);
2632 if(ame_util.idListsMatch(idList1InOut => actionIdList1,
2633 idList2InOut => actionIdList2,
2634 sortList1In => false,
2635 sortList2In => false)) then
2636 actionIdMatch := true;
2637 end if;
2638 if(conditionIdMatch and actionIdMatch) then
2639 return(true);
2640 end if;
2641 conditionIdList2.delete;
2642 actionIdList2.delete;
2643 conditionIdMatch := false;
2644 actionIdMatch := false;
2645 ruleId := tempRuleId.rule_id;
2646 end loop;
2647 return(false);
2648 exception
2649 when others then
2650 rollback;
2651 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2652 routineNamein => 'ruleAlreadyExistsForTransType',
2653 exceptionNumberIn => sqlcode,
2654 exceptionStringIn => '(rule ID ' ||
2655 ruleId ||
2656 ') ' ||
2657 sqlerrm);
2658 raise;
2659 return(true);
2660 end ruleAlreadyExistsForTransType;
2661 function ruleExists(typeIn in varchar2,
2662 conditionIdListIn in ame_util.idList,
2663 actionIdListIn ame_util.idList,
2664 itemClassIdIn in integer default null) return boolean as
2665 cursor ruleIdCursor(typeIn in varchar2,
2666 itemClassIdIn in integer default null) is
2667 select rule_id
2668 from ame_rules
2669 where
2670 rule_type = typeIn and
2671 (item_class_id is null or
2672 item_class_id = itemClassIdIn) and
2673 ((sysdate between start_date and
2674 nvl(end_date - ame_util.oneSecond, sysdate)) or
2675 (sysdate < start_date and
2676 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2677 actionIdList1 ame_util.idList;
2678 actionIdList2 ame_util.idList;
2679 actionIdMatch boolean;
2680 conditionIdList1 ame_util.idList;
2681 conditionIdList2 ame_util.idList;
2682 conditionIdMatch boolean;
2683 ruleId ame_rules.rule_id%type;
2684 begin
2685 ruleId := null;
2686 conditionIdList1 := conditionIdListIn;
2687 ame_util.sortIdListInPlace(idListInOut => conditionIdList1);
2688 actionIdList1 := actionIdListIn;
2689 ame_util.sortIdListInPlace(idListInOut => actionIdList1);
2690 conditionIdMatch := false;
2691 actionIdMatch := false;
2692 for tempRuleId in ruleIdCursor(typeIn => typeIn,
2693 itemClassIdIn => itemClassIdIn) loop
2694 getConditionIds(ruleIdIn => tempRuleId.rule_id,
2695 conditionIdListOut => conditionIdList2);
2696 ame_util.sortIdListInPlace(idListInOut => conditionIdList2);
2697 if(ame_util.idListsMatch(idList1InOut => conditionIdList1,
2698 idList2InOut => conditionIdList2,
2699 sortList1In => false,
2700 sortList2In => false)) then
2701 conditionIdMatch := true;
2702 end if;
2703 getActionIds(ruleIdIn => tempRuleId.rule_id,
2704 actionIdListOut => actionIdList2);
2705 ame_util.sortIdListInPlace(idListInOut => actionIdList2);
2706 if(ame_util.idListsMatch(idList1InOut => actionIdList1,
2707 idList2InOut => actionIdList2,
2708 sortList1In => false,
2709 sortList2In => false)) then
2710 actionIdMatch := true;
2711 end if;
2712 if(conditionIdMatch and actionIdMatch) then
2713 return(true);
2714 end if;
2715 ruleId := tempRuleId.rule_id;
2716 conditionIdList2.delete;
2717 actionIdList2.delete;
2718 conditionIdMatch := false;
2719 actionIdMatch := false;
2720 end loop;
2721 return(false);
2722 exception
2723 when others then
2724 rollback;
2725 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2726 routineNamein => 'ruleExists',
2727 exceptionNumberIn => sqlcode,
2728 exceptionStringIn => '(rule ID ' ||
2729 ruleId ||
2730 ') ' ||
2731 sqlerrm);
2732 raise;
2733 return(true);
2734 end ruleExists;
2735 function subordinateICCondExist(ruleIdIn in integer) return boolean as
2736 headerItemClassId integer;
2737 tempCount integer;
2738 begin
2739 headerItemClassId :=
2740 ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
2741 ame_util.headerItemClassName);
2742 select count(*)
2743 into tempCount
2744 from ame_conditions,
2745 ame_attributes,
2746 ame_condition_usages
2747 where
2748 ame_conditions.condition_id = ame_condition_usages.condition_id and
2749 ame_conditions.attribute_id = ame_attributes.attribute_id and
2750 ame_condition_usages.rule_id = ruleIdIn and
2751 ame_attributes.item_class_id <> headerItemClassId and
2752 (sysdate between ame_conditions.start_date and
2753 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)) and
2754 (sysdate between ame_condition_usages.start_date and
2755 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) and
2756 (sysdate between ame_attributes.start_date and
2757 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate));
2758 if(tempCount > 0) then
2759 return(true);
2760 else
2761 return(false);
2762 end if;
2763 exception
2764 when others then
2765 rollback;
2766 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2767 routineNamein => 'subordinateICCondExist',
2768 exceptionNumberIn => sqlcode,
2769 exceptionStringIn => sqlerrm);
2770 raise;
2771 return(false);
2772 end subordinateICCondExist;
2773 function useRulePriorityMode(applicationIdIn in integer,
2774 ruleTypeIn in varchar2) return boolean as
2775 variableValue ame_util.longStringType;
2776 begin
2777 variableValue := ame_util.getConfigVar(variableNameIn => ame_util.rulePriorityModesConfigVar,
2778 applicationIdIn => applicationIdIn);
2779 if(ruleTypeIn = ame_util.combinationRuleType) then
2780 if(substrb(variableValue, 1, (instr(variableValue,':',1,1) -1))
2781 = ame_util.disabledRulePriority) then
2782 return(false);
2783 end if;
2784 elsif(ruleTypeIn = ame_util.authorityRuleType) then
2785 if(substrb(variableValue,
2786 (instr(variableValue,':',1,1) +1),
2787 (instr(variableValue,':',1,2) -
2788 (instr(variableValue,':',1,1) +1)))
2789 = ame_util.disabledRulePriority) then
2790 return(false);
2791 end if;
2792 elsif(ruleTypeIn = ame_util.exceptionRuleType) then
2793 if(substrb(variableValue,
2794 (instr(variableValue,':',1,2) +1),
2795 (instr(variableValue,':',1,3) -
2796 (instr(variableValue,':',1,2) +1)))
2797 = ame_util.disabledRulePriority) then
2798 return(false);
2799 end if;
2800 elsif(ruleTypeIn = ame_util.listModRuleType) then
2801 if(substrb(variableValue,
2802 (instr(variableValue,':',1,3) +1),
2803 (instr(variableValue,':',1,4) -
2804 (instr(variableValue,':',1,3) +1)))
2805 = ame_util.disabledRulePriority) then
2806 return(false);
2807 end if;
2808 elsif(ruleTypeIn = ame_util.substitutionRuleType) then
2809 if(substrb(variableValue,
2810 (instr(variableValue,':',1,4) +1),
2811 (instr(variableValue,':',1,5) -
2812 (instr(variableValue,':',1,4) +1)))
2813 = ame_util.disabledRulePriority) then
2814 return(false);
2815 end if;
2816 elsif(ruleTypeIn = ame_util.preListGroupRuleType) then
2817 if(substrb(variableValue,
2818 (instr(variableValue,':',1,5) +1),
2819 (instr(variableValue,':',1,6) -
2820 (instr(variableValue,':',1,5) +1)))
2821 = ame_util.disabledRulePriority) then
2822 return(false);
2823 end if;
2824 elsif(ruleTypeIn = ame_util.postListGroupRuleType) then
2825 if(substrb(variableValue,
2826 (instr(variableValue,':',1,6) +1),
2827 (instr(variableValue,':',1,7) -
2828 (instr(variableValue,':',1,6) +1)))
2829 = ame_util.disabledRulePriority) then
2830 return(false);
2831 end if;
2832 elsif(ruleTypeIn = ame_util.productionRuleType) then
2833 if(substrb(variableValue,
2834 (instr(variableValue,':',1,7) +1))
2835 = ame_util.disabledRulePriority) then
2836 return(false);
2837 end if;
2838 end if;
2839 return(true);
2840 exception
2841 when others then
2842 rollback;
2843 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
2844 routineNamein => 'useRulePriorityMode',
2845 exceptionNumberIn => sqlcode,
2846 exceptionStringIn => sqlerrm);
2847 raise;
2848 return(false);
2849 end useRulePriorityMode;
2850 /* procedures */
2851 procedure change(ruleIdIn in integer,
2852 typeIn in integer default null,
2853 conditionIdsIn in ame_util.idList default ame_util.emptyIdList,
2854 actionIdsIn in ame_util.idList default ame_util.emptyIdList,
2855 deleteListIn in ame_util.stringList default ame_util.emptyStringList,
2856 descriptionIn in varchar2 default null,
2857 applicationIdIn in integer default null,
2858 parentVersionStartDateIn in date,
2859 finalizeIn in boolean default false,
2860 processingDateIn in date default null) as
2861 cursor ruleStartDateCursor is
2862 select start_date
2863 from ame_rules
2864 where
2865 rule_id = ruleIdIn and
2866 ((sysdate between start_date and
2867 nvl(end_date - ame_util.oneSecond, sysdate)) or
2868 (sysdate < start_date and
2869 start_date < nvl(end_date,start_date + ame_util.oneSecond)))
2870 for update;
2871 actionCount integer;
2872 actionCount2 integer;
2873 actionDeletionException exception;
2874 actionId integer;
2875 actionId1 ame_actions.action_id%type;
2876 actionIdListCopy ame_util.idList;
2877 actionTypeId1 ame_action_types.action_type_id%type;
2878 actionTypeId2 ame_action_types.action_type_id%type;
2879 actionTypeUsageException exception;
2880 conditionCount integer;
2881 applicationIds ame_util.idList;
2882 conditionId integer;
2883 conditionIdListCopy ame_util.idList;
2884 currentUserId integer;
2885 description ame_rules.description%type;
2886 endDate date;
2887 errorCode integer;
2888 errorMessage ame_util.longestStringType;
2889 exceptionConditionException exception;
2890 inUseException exception;
2891 itemClassId integer;
2892 listModConditionException exception;
2893 newActionIdList ame_util.idList;
2894 newConditionIdList ame_util.idList;
2895 newStartDate date;
2896 newEndDate date;
2897 newUsageResult boolean;
2898 nonProductionActionException exception;
2899 objectVersionNoDataException exception;
2900 ruleId integer;
2901 ruleKey ame_rules.rule_key%type;
2902 ruleType integer;
2903 ruleStartDate ame_rules.start_date%type;
2904 ruleEndDate ame_rules.end_date%type;
2905 tempCount integer;
2906 tempIndex2 integer;
2907 processingDate date;
2908 newVersionStartDate date;
2909 begin
2910 if processingDateIn is null then
2911 processingDate := sysdate;
2912 else
2913 processingDate := processingDateIn;
2914 end if;
2915 if(finalizeIn) then
2916 open ruleStartDateCursor;
2917 fetch ruleStartDateCursor into ruleStartDate;
2918 if ruleStartDateCursor%notfound then
2919 raise objectVersionNoDataException;
2920 end if;
2921 if parentVersionStartDateIn <> ruleStartDate then
2922 close ruleStartDateCursor;
2923 raise ame_util.objectVersionException;
2924 end if;
2925 end if;
2926 if(typeIn is null) then
2927 ruleType := getType(ruleIdIn => ruleIdIn);
2928 else
2929 ruleType := typeIn;
2930 end if;
2931 ruleKey := getRuleKey(ruleIdIn => ruleIdIn);
2932 if(descriptionIn is null) then
2933 description := getDescription(ruleIdIn => ruleIdIn);
2934 else
2935 description := descriptionIn;
2936 end if;
2937 currentUserId := ame_util.getCurrentUserId;
2938 /* make sure the end_date and start_date values do not overlap */
2939 endDate := processingDate ;
2940 /* To figure out the start_date for the new row in ame_rules the
2941 logic is : If no value of start date is inputed in the routine then
2942 start date is not changed, then for future dated rules, the start_date
2943 is retained otherwise it becomes the processing date */
2944 if(trunc(ruleStartDate) > trunc(processingDate)) then
2945 newStartDate := trunc(ruleStartDate);
2946 else
2947 newStartDate := processingDate;
2948 end if;
2949 /* To figure out the end date for the new row in ame_rules the logic
2950 is: If no value for end_date is inputed in the routine then end date
2951 is not changed. In this case the end_date from the old row is retained
2952 */
2953 ruleEndDate := getEndDate(ruleIdIn => ruleIdIn);
2954 if ruleEndDate is null then
2955 newEndDate := null;
2956 else
2957 newEndDate := ruleEndDate;
2958 end if;
2959 itemClassId := ame_rule_pkg.getItemClassId(ruleIdIn => ruleIdIn);
2960 newConditionIdList := conditionIdsIn;
2961 conditionCount := newConditionIdList.count;
2962 newActionIdList := actionIdsIn;
2963 actionCount := newActionIdList.count;
2964 actionCount2 := 0;
2965 /* Verify that at least on action remains for the rule. */
2966 if(deleteListIn.count > 0) then
2967 for i in 1..deleteListIn.count loop
2968 if(deleteListIn(i) like 'act%') then
2969 actionCount2 := (actionCount2 + 1);
2970 end if;
2971 end loop;
2972 /* If no actions remain, raise an exception. */
2973 if(actionCount2 = actionCount) then
2974 raise actionDeletionException;
2975 end if;
2976 for i in 1..deleteListIn.count loop
2977 if(deleteListIn(i)) like 'con%' then
2978 conditionId := to_number(substrb(deleteListIn(i),4,(lengthb(deleteListIn(i)))));
2979 for j in 1..conditionCount loop
2980 if(newConditionIdList(j) = conditionId) then
2981 /* there is a match so delete from the condition list */
2982 newConditionIdList.delete(j);
2983 for k in (j + 1) .. conditionCount loop
2984 /* reindex those conditions that fall above the deleted condition */
2985 newConditionIdList(k-1) := newConditionIdList(k);
2986 end loop;
2987 /* the last condition in the index was reset in the loop above
2988 which now leaves a duplicate so delete the duplicate */
2989 newConditionIdList.delete(conditionCount);
2990 /* get the new condition count */
2991 conditionCount := newConditionIdList.count;
2992 exit;
2993 end if;
2994 end loop;
2995 else
2996 actionId := to_number(substrb(deleteListIn(i),4,(lengthb(deleteListIn(i)))));
2997 for j in 1..actionCount loop
2998 if(newActionIdList(j) = actionId) then
2999 /* there is a match so delete from the action list */
3000 newActionIdList.delete(j);
3001 for k in (j + 1) .. actionCount loop
3002 /* reindex those actions that fall above the deleted action */
3003 newActionIdList(k-1) := newActionIdList(k);
3004 end loop;
3005 /* the last action in the index was reset in the loop above
3006 which now leaves a duplicate so delete the duplicate */
3007 newActionIdList.delete(actionCount);
3008 /* get the new action count */
3009 actionCount := newActionIdList.count;
3010 exit;
3011 end if;
3012 end loop;
3013 end if;
3014 end loop;
3015 itemClassId := ame_rule_pkg.getItemClassId(ruleIdIn => ruleIdIn);
3016 if(ruleExists(typeIn => ruleType,
3017 itemClassIdIn => itemClassId,
3018 conditionIdListIn => newConditionIdList,
3019 actionIdListIn => newActionIdList)) then
3020 raise inUseException;
3021 end if;
3022 end if;
3023 /*
3024 With exception of the combination rule type, a rule must have at least
3025 one action of an action type that has an action-type usage for
3026 the rule's type.
3027 */
3028 if(not ame_rule_pkg.hasATUsageForRuleType2(ruleTypeIn => ruleType,
3029 actionIdsIn => newActionIdList)) then
3030 raise actionTypeUsageException;
3031 end if;
3032 if(ruleType = ame_util.combinationRuleType) then
3033 if(not ame_rule_pkg.hasNonProductionActions(actionIdsIn => newActionIdList)) then
3034 raise nonProductionActionException;
3035 end if;
3036 end if;
3037 /* Exception rules must have at least one exception condition. */
3038 if(ruleType = ame_util.exceptionRuleType) then
3039 if(not ame_rule_pkg.hasExceptionCondition(conditionIdsIn => newConditionIdList)) then
3040 raise exceptionConditionException;
3041 end if;
3042 end if;
3043 /* List-modification and substitution rules must have exactly one list-modification condition. */
3044 if(ruleType in (ame_util.listModRuleType, ame_util.substitutionRuleType)) then
3045 if(not ame_rule_pkg.hasListModCondition(conditionIdsIn => newConditionIdList)) then
3046 raise listModConditionException;
3047 end if;
3048 end if;
3049 update ame_condition_usages
3050 set
3051 last_updated_by = currentUserId,
3052 last_update_date = endDate,
3053 last_update_login = currentUserId,
3054 end_date = endDate
3055 where
3056 rule_id = ruleIdIn and
3057 ((sysdate between start_date and
3058 nvl(end_date - ame_util.oneSecond, sysdate)) or
3059 (sysdate < start_date and
3060 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3061 update ame_action_usages
3062 set
3063 last_updated_by = currentUserId,
3064 last_update_date = endDate,
3065 last_update_login = currentUserId,
3066 end_date = endDate
3067 where
3068 rule_id = ruleIdIn and
3069 ((sysdate between start_date and
3070 nvl(end_date - ame_util.oneSecond, sysdate)) or
3071 (sysdate < start_date and
3072 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3073 update ame_rules
3074 set
3075 last_updated_by = currentUserId,
3076 last_update_date = endDate,
3077 last_update_login = currentUserId,
3078 end_date = endDate
3079 where
3080 rule_id = ruleIdIn and
3081 ((sysdate between start_date and
3082 nvl(end_date - ame_util.oneSecond, sysdate)) or
3083 (sysdate < start_date and
3084 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3085 ruleId := new(applicationIdIn => applicationIdIn,
3086 typeIn => ruleType,
3087 conditionIdsIn => newConditionIdList,
3088 actionIdsIn => newActionIdList,
3089 itemClassIdIn => itemClassId,
3090 ruleKeyIn => ruleKey,
3091 descriptionIn => description,
3092 startDateIn => newStartDate,
3093 endDateIn => newEndDate,
3094 ruleIdIn => ruleIdIn,
3095 finalizeIn => false,
3096 processingDateIn => processingDate);
3097 changeAllAttributeUseCounts(ruleIdIn => ruleIdIn,
3098 finalizeIn => false);
3099 if(finalizeIn) then
3100 close ruleStartDateCursor;
3101 commit;
3102 end if;
3103 exception
3104 when ame_util.objectVersionException then
3105 rollback;
3106 if(ruleStartDateCursor%isOpen) then
3107 close ruleStartDateCursor;
3108 end if;
3109 errorCode := -20001;
3110 errorMessage :=
3111 ame_util.getMessage(applicationShortNameIn => 'PER',
3112 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3113 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3114 routineNameIn => 'change',
3115 exceptionNumberIn => errorCode,
3116 exceptionStringIn => errorMessage);
3117 raise_application_error(errorCode,
3118 errorMessage);
3119 when objectVersionNoDataException then
3120 rollback;
3121 if(ruleStartDateCursor%isOpen) then
3122 close ruleStartDateCursor;
3123 end if;
3124 errorCode := -20001;
3125 errorMessage :=
3126 ame_util.getMessage(applicationShortNameIn => 'PER',
3127 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3128 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3129 routineNameIn => 'change',
3130 exceptionNumberIn => errorCode,
3131 exceptionStringIn => errorMessage);
3132 raise_application_error(errorCode,
3133 errorMessage);
3134 when inUseException then
3135 rollback;
3136 errorCode := -20001;
3137 errorMessage :=
3138 ame_util.getMessage(applicationShortNameIn => 'PER',
3139 messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
3140 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3141 routineNamein => 'change',
3142 exceptionNumberIn => errorCode,
3143 exceptionStringIn => errorMessage);
3144 raise_application_error(errorCode,
3145 errorMessage);
3146 when actionTypeUsageException then
3147 rollback;
3148 errorCode := -20001;
3149 errorMessage :=
3150 ame_util.getMessage(applicationShortNameIn => 'PER',
3151 messageNameIn => 'AME_400382_RULE_ONE_ACT_SEL');
3152 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3153 routineNamein => 'change',
3154 exceptionNumberIn => errorCode,
3155 exceptionStringIn => errorMessage);
3156 raise_application_error(errorCode,
3157 errorMessage);
3158 when actionDeletionException then
3159 rollback;
3160 errorCode := -20001;
3161 errorMessage :=
3162 ame_util.getMessage(applicationShortNameIn => 'PER',
3163 messageNameIn => 'AME_400383_RULE_ONE_ACT_SEL2');
3164 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3165 routineNamein => 'change',
3166 exceptionNumberIn => errorCode,
3167 exceptionStringIn => errorMessage);
3168 raise_application_error(errorCode,
3169 errorMessage);
3170 when nonProductionActionException then
3171 rollback;
3172 errorCode := -20001;
3173 errorMessage :=
3174 ame_util.getMessage(applicationShortNameIn => 'PER',
3175 messageNameIn => 'AME_400464_RULE_NONPROD_ACTION');
3176 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3177 routineNamein => 'change',
3178 exceptionNumberIn => errorCode,
3179 exceptionStringIn => errorMessage);
3180 raise_application_error(errorCode,
3181 errorMessage);
3182 when exceptionConditionException then
3183 rollback;
3184 errorCode := -20001;
3185 errorMessage :=
3186 ame_util.getMessage(applicationShortNameIn => 'PER',
3187 messageNameIn => 'AME_400384_RULE_ONE_EXC_COND');
3188 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3189 routineNamein => 'change',
3190 exceptionNumberIn => errorCode,
3191 exceptionStringIn => errorMessage);
3192 raise_application_error(errorCode,
3193 errorMessage);
3194 when listModConditionException then
3195 rollback;
3196 errorCode := -20001;
3197 errorMessage :=
3198 ame_util.getMessage(applicationShortNameIn => 'PER',
3199 messageNameIn => 'AME_400385_RULE_LM');
3200 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3201 routineNamein => 'change',
3202 exceptionNumberIn => errorCode,
3203 exceptionStringIn => errorMessage);
3204 raise_application_error(errorCode,
3205 errorMessage);
3206 when others then
3207 rollback;
3208 if(ruleStartDateCursor%isOpen) then
3209 close ruleStartDateCursor;
3210 end if;
3211 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3212 routineNamein => 'change',
3213 exceptionNumberIn => sqlcode,
3214 exceptionStringIn => '(rule ID ' ||
3215 ruleIdIn||
3216 ') ' ||
3217 sqlerrm);
3218 raise;
3219 end change;
3220 procedure changeAllAttributeUseCounts(ruleIdIn in integer,
3221 finalizeIn in boolean default true) as
3222 cursor applicationCursor(ruleIdIn in integer) is
3223 select item_id
3224 from ame_rule_usages
3225 where
3226 rule_id = ruleIdIn and
3227 ((sysdate between start_date and
3228 nvl(end_date - ame_util.oneSecond, sysdate)) or
3229 (sysdate < start_date and
3230 start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
3231 begin
3232 for tempApplication in applicationCursor(ruleIdIn => ruleIdIn) loop
3233 changeAttributeUseCounts(ruleIdIn => ruleIdIn,
3234 applicationIdIn => tempApplication.item_id,
3235 finalizeIn => finalizeIn);
3236 end loop;
3237 exception
3238 when others then
3239 rollback;
3240 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3241 routineNamein => 'changeAllAttributeUseCounts',
3242 exceptionNumberIn => sqlcode,
3243 exceptionStringIn => '(rule ID ' ||
3244 ruleIdIn||
3245 ') ' ||
3246 sqlerrm);
3247 raise;
3248 end changeAllAttributeUseCounts;
3249 procedure changeAttributeUseCounts(ruleIdIn in integer,
3250 applicationIdIn in integer,
3251 finalizeIn in boolean default true) as
3252 attributeIds ame_util.idList;
3253 upperLimit integer;
3254 begin
3255 getRequiredAttributes(ruleIdIn => ruleIdIn,
3256 attributeIdsOut => attributeIds);
3257 upperLimit := attributeIds.count;
3258 for i in 1 .. upperLimit loop
3259 ame_attribute_pkg.updateUseCount(attributeIdIn => attributeIds(i),
3260 applicationIdIn => applicationIdIn,
3261 finalizeIn => finalizeIn);
3262 end loop;
3263 exception
3264 when others then
3265 rollback;
3266 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3267 routineNameIn => 'changeAttributeUseCounts',
3268 exceptionNumberIn => sqlcode,
3269 exceptionStringIn => '(rule ID ' ||
3270 ruleIdIn||
3271 ') ' ||
3272 sqlerrm);
3273 raise;
3274 end changeAttributeUseCounts;
3275 procedure changeAttributeUseCounts2(attributeIdsIn in ame_util.idList,
3276 applicationIdIn in integer,
3277 finalizeIn in boolean default true) as
3278 upperLimit integer;
3279 begin
3280 upperLimit := attributeIdsIn.count;
3281 for i in 1 .. upperLimit loop
3282 ame_attribute_pkg.updateUseCount(attributeIdIn => attributeIdsIn(i),
3283 applicationIdIn => applicationIdIn,
3284 finalizeIn => finalizeIn);
3285 end loop;
3286 exception
3287 when others then
3288 rollback;
3289 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3290 routineNameIn => 'changeAttributeUseCounts2',
3291 exceptionNumberIn => sqlcode,
3292 exceptionStringIn => '(application ID ' ||
3293 applicationIdIn||
3294 ') ' ||
3295 sqlerrm);
3296 raise;
3297 end changeAttributeUseCounts2;
3298 /*
3299 AME_STRIPING
3300 procedure changeRuleStripe(ruleIdIn in integer,
3301 oldStripeSetIdIn in integer,
3302 newStripeSetIdIn in integer) as
3303 currentUserId integer;
3304 begin
3305 currentUserId := ame_util.getCurrentUserId;
3306 update ame_rule_stripe_sets
3307 set
3308 last_updated_by = currentUserId,
3309 last_update_date = sysdate,
3310 last_update_login = currentUserId,
3311 end_date = sysdate
3312 where
3313 rule_id = ruleIdIn and
3314 stripe_set_id = oldStripeSetIdIn and
3315 (start_date <= sysdate and
3316 (end_date is null or sysdate < end_date));
3317 insert into ame_rule_stripe_sets(rule_id,
3318 stripe_set_id,
3319 created_by,
3320 creation_date,
3321 last_updated_by,
3322 last_update_date,
3323 last_update_login,
3324 security_group_id,
3325 start_date,
3326 end_date)
3327 values(ruleIdIn,
3328 newStripeSetIdIn,
3329 currentUserId,
3330 sysdate,
3331 currentUserId,
3332 sysdate,
3333 currentUserId,
3334 null,
3335 sysdate,
3336 null);
3337 commit;
3338 exception
3339 when others then
3340 rollback;
3341 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3342 routineNameIn => 'changeRuleStripe',
3343 exceptionNumberIn => sqlcode,
3344 exceptionStringIn => sqlerrm);
3345 raise;
3346 end changeRuleStripe;
3347 */
3348 procedure changeUsage(ruleIdIn in integer,
3349 applicationIdIn in integer,
3350 priorityIn in varchar2,
3351 categoryIn in varchar2,
3352 parentVersionStartDateIn in date,
3353 oldStartDateIn in date,
3354 oldEndDateIn in date default null,
3355 startDateIn in date default null,
3356 endDateIn in date default null,
3357 finalizeIn in boolean default false,
3358 processingDateIn in date default null) as
3359 cursor startDateCursor is
3360 select start_date
3361 from ame_rules
3362 where
3363 rule_id = ruleIdIn and
3364 ((sysdate between start_date and
3365 nvl(end_date - ame_util.oneSecond, sysdate)) or
3366 (sysdate < start_date and
3367 start_date < nvl(end_date,start_date + ame_util.oneSecond)))
3368 for update;
3369 cursor usageDataCursor is
3370 select rule_id
3371 from ame_rule_usages
3372 where
3373 item_id = applicationIdIn and
3374 rule_id = ruleIdIn and
3375 start_date = oldStartDateIn and
3376 nvl(end_date, sysdate) = nvl(oldEndDateIn, sysdate)
3377 for update;
3378 currentUserId integer;
3379 errorCode integer;
3380 errorMessage ame_util.longestStringType;
3381 invalidDateException exception;
3382 invalidPriorityException exception;
3383 newUsageResult boolean;
3384 endDate date;
3385 objectVersionNoDataException exception;
3386 processingDate date;
3387 newStartDate date;
3388 newEndDate date;
3389 ruleId integer;
3390 startDate date;
3391 startDateException exception;
3392 startDateException1 exception;
3393 usageStartDate date;
3394 usageEndDate date;
3395 begin
3396 if(finalizeIn) then
3397 open startDateCursor;
3398 fetch startDateCursor into startDate;
3399 if startDateCursor%notfound then
3400 raise objectVersionNoDataException;
3401 end if;
3402 if(parentVersionStartDateIn <> startDate) then
3403 raise ame_util.objectVersionException;
3404 end if;
3405 open usageDataCursor;
3406 fetch usageDataCursor into ruleId;
3407 if usageDataCursor%notfound then
3408 raise ame_util.objectVersionException;
3409 end if;
3410 end if;
3411 if processingDateIn is null then
3412 processingDate := sysdate;
3413 else
3414 processingDate := processingDateIn;
3415 end if;
3416 if (trunc(startDateIn) <> trunc(oldStartDateIn) and
3417 (startDateIn) < trunc(processingDate)) then
3418 raise startDateException;
3419 end if;
3420 if (startDateIn) >= (endDateIn) then
3421 raise startDateException1;
3422 end if;
3423 if not(ame_util.isANumber(stringIn => priorityIn,
3424 allowDecimalsIn => false,
3425 allowNegativesIn => false)) then
3426 raise invalidPriorityException;
3427 end if;
3428 if(trunc(startDateIn) > trunc(processingDate)) then
3429 newStartDate := trunc(startDateIn);
3430 else
3431 newStartDate := processingDate;
3432 end if;
3433 endDate := processingDate;
3434 currentUserId := ame_util.getCurrentUserId;
3435 update ame_rule_usages
3436 set
3437 last_updated_by = currentUserId,
3438 last_update_date = endDate,
3439 last_update_login = currentUserId,
3440 end_date = endDate
3441 where
3442 rule_id = ruleIdIn and
3443 item_id = applicationIdIn and
3444 start_date = oldStartDateIn and
3445 nvl(end_date, endDate) = nvl(oldEndDateIn, endDate) ;
3446 newUsageResult := newRuleUsage(itemIdIn => applicationIdIn,
3447 ruleIdIn => ruleIdIn,
3448 startDateIn => newStartDate,
3449 endDateIn => endDateIn,
3450 priorityIn => priorityIn,
3451 categoryIn => categoryIn,
3452 finalizeIn => false,
3453 parentVersionStartDateIn => parentVersionStartDateIn,
3454 processingDateIn => processingDateIn,
3455 updateParentObjectIn => true);
3456 if(finalizeIn) then
3457 close usageDataCursor;
3458 close startDateCursor;
3459 commit;
3460 end if;
3461 exception
3462 when ame_util.objectVersionException then
3463 rollback;
3464 errorCode := -20001;
3465 errorMessage :=
3466 ame_util.getMessage(applicationShortNameIn => 'PER',
3467 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3468 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3469 routineNameIn => 'changeUsage',
3470 exceptionNumberIn => errorCode,
3471 exceptionStringIn => errorMessage);
3472 raise_application_error(errorCode,
3473 errorMessage);
3474 when objectVersionNoDataException then
3475 rollback;
3476 if(startDateCursor%isOpen) then
3477 close startDateCursor;
3478 end if;
3479 if(usageDataCursor%isOpen) then
3480 close usageDataCursor;
3481 end if;
3482 errorCode := -20001;
3483 errorMessage :=
3484 ame_util.getMessage(applicationShortNameIn => 'PER',
3485 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3486 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3487 routineNameIn => 'changeUsage',
3488 exceptionNumberIn => errorCode,
3489 exceptionStringIn => errorMessage);
3490 raise_application_error(errorCode,
3491 errorMessage);
3492 when invalidPriorityException then
3493 rollback;
3494 errorCode := -20001;
3495 errorMessage :=
3496 ame_util.getMessage(applicationShortNameIn => 'PER',
3497 messageNameIn => 'AME_400288_RUL_PRI_NOT_VAL');
3498 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3499 routineNameIn => 'changeUsage',
3500 exceptionNumberIn => errorCode,
3501 exceptionStringIn => errorMessage);
3502 raise_application_error(errorCode,
3503 errorMessage);
3504 when startDateException then
3505 rollback;
3506 errorCode := -20001;
3507 errorMessage :=
3508 ame_util.getMessage(applicationShortNameIn => 'PER',
3509 messageNameIn => 'AME_400213_RUL_STRT_GRTR_CUR');
3510 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3511 routineNamein => 'changeUsage',
3512 exceptionNumberIn => errorCode,
3513 exceptionStringIn => errorMessage);
3514 raise_application_error(errorCode,
3515 errorMessage);
3516 when startDateException1 then
3517 rollback;
3518 errorCode := -20001;
3519 errorMessage :=
3520 ame_util.getMessage(applicationShortNameIn => 'PER',
3521 messageNameIn => 'AME_400214_RUL_STRT_LESS_END');
3522 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3523 routineNamein => 'changeUsage',
3524 exceptionNumberIn => errorCode,
3525 exceptionStringIn => errorMessage);
3526 raise_application_error(errorCode,
3527 errorMessage);
3528 when others then
3529 rollback;
3530 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3531 routineNameIn => 'changeUsage',
3532 exceptionNumberIn => sqlcode,
3533 exceptionStringIn => '(rule ID ' ||
3534 ruleIdIn||
3535 ') ' ||
3536 sqlerrm);
3537 raise;
3538 end changeUsage;
3539 /*
3540 AME_STRIPING
3541 procedure dropRuleStripeSet(ruleIdIn in integer,
3542 applicationIdIn in integer,
3543 finalizeIn in boolean default false) as
3544 stripeSetId integer;
3545 begin
3546 begin
3547 select stripe_set_id
3548 into stripeSetId
3549 from ame_rule_stripe_sets
3550 where
3551 rule_id = ruleIdIn and
3552 stripe_set_id in
3553 (select stripe_set_id
3554 from ame_stripe_sets
3555 where
3556 application_id = applicationIdIn and
3557 (start_date <= sysdate and
3558 (end_date is null or sysdate < end_date))) and
3559 (start_date <= sysdate and
3560 (end_date is null or sysdate < end_date));
3561 exception
3562 when no_data_found then
3563 return;
3564 end;
3565 update ame_rule_stripe_sets
3566 set end_date = sysdate
3567 where
3568 rule_id = ruleIdIn and
3569 stripe_set_id = stripeSetId and
3570 (start_date <= sysdate and
3571 (end_date is null or sysdate < end_date));
3572 ame_admin_pkg.checkStripeSetUsage(stripeSetIdIn => stripeSetId,
3573 finalizeIn => false);
3574 if(finalizeIn) then
3575 commit;
3576 end if;
3577 exception
3578 when others then
3579 rollback;
3580 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3581 routineNameIn => 'dropRuleStripeSet',
3582 exceptionNumberIn => sqlcode,
3583 exceptionStringIn => '(rule ID ' ||
3584 ruleIdIn||
3585 ') ' ||
3586 sqlerrm);
3587 raise;
3588 end dropRuleStripeSet;
3589 */
3590 /*
3591 AME_STRIPING
3592 procedure getAppRuleList(applicationIdIn in integer,
3593 stripeSetIdIn in integer default null,
3594 isStripingIn in varchar2,
3595 ruleListOut out nocopy ame_rule_pkg.ruleActionRecordTable) as
3596 */
3597 procedure getActionIds(ruleIdIn in integer,
3598 actionIdListOut out nocopy ame_util.idList) as
3599 cursor actionCursor(ruleIdIn in integer) is
3600 select
3601 ame_action_usages.action_id
3602 from
3603 ame_action_usages
3604 where
3605 rule_id = ruleIdIn and
3606 ((sysdate between ame_action_usages.start_date and
3607 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3608 (sysdate < ame_action_usages.start_date and
3609 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3610 ame_action_usages.start_date + ame_util.oneSecond)));
3611 actionId integer;
3612 tempIndex integer;
3613 begin
3614 tempIndex := 1;
3615 for tempAction in actionCursor(ruleIdIn => ruleIdIn) loop
3616 actionIdListOut(tempIndex) := tempAction.action_id;
3617 tempIndex := tempIndex + 1;
3618 end loop;
3619 exception
3620 when others then
3621 rollback;
3622 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3623 routineNamein => 'getActionIds',
3624 exceptionNumberIn => sqlcode,
3625 exceptionStringIn => '(rule ID ' ||
3626 ruleIdIn||
3627 ') ' ||
3628 sqlerrm);
3629 actionIdListOut := ame_util.emptyIdList;
3630 raise;
3631 end getActionIds;
3632 procedure getActions(ruleIdIn in integer,
3633 actionIdsOut out nocopy ame_util.idList,
3634 actionDescriptionsOut out nocopy ame_util.longStringList) as
3635 cursor actionsCursor(ruleIdIn in integer) is
3636 select ame_actions.action_id,
3637 ame_actions.parameter,
3638 ame_actions.parameter_two,
3639 ame_action_types.name,
3640 ame_action_types.dynamic_description,
3641 ame_action_types.description_query
3642 from ame_actions,
3643 ame_action_types,
3644 ame_action_usages
3645 where
3646 ame_actions.action_type_id = ame_action_types.action_type_id and
3647 ame_actions.action_id = ame_action_usages.action_id and
3648 ame_action_usages.rule_id = ruleIdIn and
3649 sysdate between ame_actions.start_date and
3650 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
3651 sysdate between ame_action_types.start_date and
3652 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
3653 ((sysdate between ame_action_usages.start_date and
3654 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3655 (sysdate < ame_action_usages.start_date and
3656 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3657 ame_action_usages.start_date + ame_util.oneSecond)))
3658 order by ame_actions.created_by, ame_actions.description;
3659 actionId integer;
3660 tempActionDescription ame_util.stringType;
3661 tempIndex integer;
3662 begin
3663 tempIndex := 1;
3664 for tempAction in actionsCursor(ruleIdIn => ruleIdIn) loop
3665 actionIdsOut(tempIndex) := tempAction.action_id;
3666 if(tempAction.dynamic_description = ame_util.booleanTrue) then
3667 begin
3668 if(instrb(tempAction.description_query, ame_util.actionParameterOne) > 0) then
3669 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* both parameters */
3670 execute immediate tempAction.description_query
3671 into tempActionDescription using
3672 in tempAction.parameter,
3673 in tempAction.parameter_two;
3674 else /* just parameter_one */
3675 execute immediate tempAction.description_query into
3676 tempActionDescription using
3677 in tempAction.parameter;
3678 end if;
3679 else
3680 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* just paramter_two */
3681 execute immediate tempAction.description_query
3682 into tempActionDescription using
3683 in tempAction.parameter_two;
3684 else /* neither */
3685 execute immediate tempAction.description_query into
3686 tempActionDescription;
3687 end if;
3688 end if;
3689 exception when others then
3690 tempActionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_INVALID_DESCRIPTION');
3691 end;
3692 actionDescriptionsOut(tempIndex) :=
3693 tempAction.name || ': ' || tempActionDescription;
3694 else
3695 actionDescriptionsOut(tempIndex) :=
3696 ame_action_pkg.getDescription(actionIdIn => tempAction.action_id);
3697 end if;
3698 tempIndex := tempIndex + 1;
3699 end loop;
3700 exception
3701 when others then
3702 rollback;
3703 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3704 routineNameIn => 'getActions',
3705 exceptionNumberIn => sqlcode,
3706 exceptionStringIn => '(rule ID ' ||
3707 ruleIdIn||
3708 ') ' ||
3709 sqlerrm);
3710 actionIdsOut := ame_util.emptyIdList;
3711 actionDescriptionsOut := ame_util.emptyLongStringList;
3712 raise;
3713 end getActions;
3714 procedure getActions2(ruleIdIn in integer,
3715 actionTypeIdIn in integer,
3716 actionIdsOut out nocopy ame_util.stringList,
3717 actionDescriptionsOut out nocopy ame_util.stringList) as
3718 cursor actionsCursor(ruleIdIn in integer) is
3719 select ame_actions.action_id,
3720 ame_actions.parameter,
3721 ame_actions.parameter_two,
3722 ame_actions.description,
3723 ame_action_types.dynamic_description,
3724 ame_action_types.description_query
3725 from ame_actions,
3726 ame_action_types
3727 where
3728 ame_actions.action_type_id = ame_action_types.action_type_id and
3729 ame_actions.action_type_id = actionTypeIdIn and
3730 ame_actions.action_id not in
3731 (select action_id
3732 from ame_action_usages
3733 where
3734 rule_id = ruleIdIn and
3735 ((sysdate between ame_action_usages.start_date and
3736 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3737 (sysdate < ame_action_usages.start_date and
3738 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3739 ame_action_usages.start_date + ame_util.oneSecond)))) and
3740 sysdate between ame_actions.start_date and
3741 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
3742 sysdate between ame_action_types.start_date and
3743 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
3744 order by ame_actions.created_by, ame_actions.description;
3745 actionId integer;
3746 tempIndex integer;
3747 begin
3748 tempIndex := 1;
3749 for tempAction in actionsCursor(ruleIdIn => ruleIdIn) loop
3750 actionIdsOut(tempIndex) := tempAction.action_id;
3751 if(tempAction.dynamic_description = ame_util.booleanTrue) then
3752 begin
3753 if(instrb(tempAction.description_query, ame_util.actionParameterOne) > 0) then
3754 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* both parameters */
3755 execute immediate tempAction.description_query
3756 into actionDescriptionsOut(tempIndex) using
3757 in tempAction.parameter,
3758 in tempAction.parameter_two;
3759 else /* just parameter_one */
3760 execute immediate tempAction.description_query into
3761 actionDescriptionsOut(tempIndex) using
3762 in tempAction.parameter;
3763 end if;
3764 else
3765 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* just paramter_two */
3766 execute immediate tempAction.description_query
3767 into actionDescriptionsOut(tempIndex) using
3768 in tempAction.parameter_two;
3769 else /* neither */
3770 execute immediate tempAction.description_query into
3771 actionDescriptionsOut(tempIndex);
3772 end if;
3773 end if;
3774 exception when others then
3775 actionDescriptionsOut(tempIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_INVALID_DESCRIPTION');
3776 end;
3777 else
3778 actionDescriptionsOut(tempIndex) := tempAction.description;
3779 end if;
3780 tempIndex := tempIndex + 1;
3781 end loop;
3782 exception
3783 when others then
3784 rollback;
3785 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3786 routineNameIn => 'getActions2',
3787 exceptionNumberIn => sqlcode,
3788 exceptionStringIn => '(rule ID ' ||
3789 ruleIdIn||
3790 ') ' ||
3791 sqlerrm);
3792 actionIdsOut := ame_util.emptyStringList;
3793 actionDescriptionsOut := ame_util.emptyStringList;
3794 raise;
3795 end getActions2;
3796 procedure getAppRuleList(applicationIdIn in integer,
3797 ruleListOut out nocopy ame_rule_pkg.ruleActionRecordTable) as
3798 cursor ruleCursor(applicationIdIn in varchar2) is
3799 select
3800 r.rule_id rule_id,
3801 r.rule_key rule_key,
3802 r.rule_type rule_type,
3803 r.description rule_description,
3804 u.start_date usage_start_date,
3805 u.end_date usage_end_date,
3806 u.priority priority,
3807 r.item_class_id,
3808 u.approver_category
3809 from
3810 ame_rule_usages u,
3811 ame_rules r,
3812 ame_item_class_usages i
3813 where
3814 u.rule_id = r.rule_id and
3815 r.item_class_id = i.item_class_id and
3816 u.item_id = applicationIdIn and
3817 i.application_id = applicationIdIn and
3818 ((sysdate between r.start_date and
3819 nvl(r.end_date - ame_util.oneSecond, sysdate)) or
3820 (sysdate < r.start_date and
3821 r.start_date < nvl(r.end_date,
3822 r.start_date + ame_util.oneSecond))) and
3823 ((sysdate between u.start_date and
3824 nvl(u.end_date - ame_util.oneSecond, sysdate)) or
3825 (sysdate < u.start_date and
3826 u.start_date < nvl(u.end_date,
3827 u.start_date + ame_util.oneSecond))) and
3828 (i.start_date <= sysdate and
3829 (i.end_date is null or sysdate < i.end_date))
3830 order by i.item_class_order_number, rule_type, rule_description, usage_start_date;
3831 cursor ruleCursor2(applicationIdIn in varchar2) is
3832 select
3833 r.rule_id rule_id,
3834 r.rule_key rule_key,
3835 r.rule_type rule_type,
3836 r.description rule_description,
3837 u.start_date usage_start_date,
3838 u.end_date usage_end_date,
3839 u.priority priority,
3840 null item_class_id,
3841 u.approver_category
3842 from
3843 ame_rule_usages u,
3844 ame_rules r
3845 where
3846 u.rule_id = r.rule_id and
3847 r.rule_type in (ame_util.substitutionRuleType, ame_util.listModRuleType) and
3848 u.item_id = applicationIdIn and
3849 ((sysdate between r.start_date and
3850 nvl(r.end_date - ame_util.oneSecond, sysdate)) or
3851 (sysdate < r.start_date and
3852 r.start_date < nvl(r.end_date,
3853 r.start_date + ame_util.oneSecond))) and
3854 ((sysdate between u.start_date and
3855 nvl(u.end_date - ame_util.oneSecond, sysdate)) or
3856 (sysdate < u.start_date and
3857 u.start_date < nvl(u.end_date,
3858 u.start_date + ame_util.oneSecond)))
3859 order by rule_type, rule_description, usage_start_date;
3860 tempRuleActionRecord ruleActionRecord;
3861 tempIndex integer;
3862 begin
3863 tempIndex := 1;
3864 for tempRule in ruleCursor(applicationIdIn => applicationIdIn) loop
3865 tempRuleActionRecord.rule_id := tempRule.rule_id;
3866 tempRuleActionRecord.rule_key:= tempRule.rule_key;
3867 tempRuleActionRecord.rule_type := tempRule.rule_type;
3868 tempRuleActionRecord.rule_description := tempRule.rule_description;
3869 tempRuleActionRecord.usage_start_date := tempRule.usage_start_date;
3870 tempRuleActionRecord.usage_end_date := tempRule.usage_end_date;
3871 tempRuleActionRecord.priority := tempRule.priority;
3872 tempRuleActionRecord.item_class_id := tempRule.item_class_id;
3873 tempRuleActionRecord.approver_category := tempRule.approver_category;
3874 ruleListOut(tempIndex) := tempRuleActionRecord;
3875 tempIndex := tempIndex + 1;
3876 end loop;
3877 for tempRule in ruleCursor2(applicationIdIn => applicationIdIn) loop
3878 tempRuleActionRecord.rule_id := tempRule.rule_id;
3879 tempRuleActionRecord.rule_key:= tempRule.rule_key;
3880 tempRuleActionRecord.rule_type := tempRule.rule_type;
3881 tempRuleActionRecord.rule_description := tempRule.rule_description;
3882 tempRuleActionRecord.usage_start_date := tempRule.usage_start_date;
3883 tempRuleActionRecord.usage_end_date := tempRule.usage_end_date;
3884 tempRuleActionRecord.priority := tempRule.priority;
3885 tempRuleActionRecord.item_class_id := null;
3886 tempRuleActionRecord.approver_category := tempRule.approver_category;
3887 ruleListOut(tempIndex) := tempRuleActionRecord;
3888 tempIndex := tempIndex + 1;
3889 end loop;
3890 exception
3891 when others then
3892 rollback;
3893 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3894 routineNameIn => 'getAppRuleList',
3895 exceptionNumberIn => sqlcode,
3896 exceptionStringIn => '(application ID ' ||
3897 applicationIdIn||
3898 ') ' ||
3899 sqlerrm);
3900 ruleListOut := ame_rule_pkg.emptyRuleActionRecordTable;
3901 raise;
3902 end getAppRuleList;
3903 procedure getAppRuleList2(applicationIdIn in integer,
3904 applicationIdToShareIn in integer,
3905 ruleIdListOut out nocopy ame_util.stringList,
3906 ruleDescriptionListOut out nocopy ame_util.stringList) as
3907 cursor ruleCursor(applicationIdToShareIn in varchar2,
3908 itemClassIdIn in integer) is
3909 select
3910 distinct(r.rule_id) rule_id,
3911 r.description
3912 from
3913 ame_rules r,
3914 ame_rule_usages u
3915 where
3916 u.rule_id = r.rule_id and
3917 (r.item_class_id is null or
3918 r.item_class_id = itemClassIdIn) and
3919 u.item_id = applicationIdToShareIn and
3920 ((sysdate between r.start_date and
3921 nvl(r.end_date - ame_util.oneSecond, sysdate)) or
3922 (sysdate < r.start_date and
3923 r.start_date < nvl(r.end_date,
3924 r.start_date + ame_util.oneSecond))) and
3925 ((sysdate between u.start_date and
3926 nvl(u.end_date - ame_util.oneSecond, sysdate)) or
3927 (sysdate < u.start_date and
3928 u.start_date < nvl(u.end_date,
3929 u.start_date + ame_util.oneSecond)))
3930 order by r.description;
3931 itemClassIds ame_util.idList;
3932 tempIndex integer;
3933 begin
3934 ame_admin_pkg.getTransTypeItemClassIds(applicationIdIn => applicationIdIn,
3935 itemClassIdsOut => itemClassIds);
3936 tempIndex := 1;
3937 for i in 1 .. itemClassIds.count loop
3938 for tempRule in ruleCursor(applicationIdToShareIn => applicationIdToShareIn,
3939 itemClassIdIn => itemClassIds(i)) loop
3940 ruleIdListOut(tempIndex) := tempRule.rule_id;
3941 ruleDescriptionListOut(tempIndex) := tempRule.description;
3942 tempIndex := tempIndex + 1;
3943 end loop;
3944 end loop;
3945 exception
3946 when others then
3947 rollback;
3948 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
3949 routineNameIn => 'getAppRuleList2',
3950 exceptionNumberIn => sqlcode,
3951 exceptionStringIn => '(application ID ' ||
3952 applicationIdIn||
3953 ') ' ||
3954 sqlerrm);
3955 ruleIdListOut := ame_util.emptyStringList;
3956 ruleDescriptionListOut := ame_util.emptyStringList;
3957 raise;
3958 end getAppRuleList2;
3959 procedure getConditionIds(ruleIdIn in integer,
3960 conditionIdListOut out nocopy ame_util.idList) as
3961 cursor conditionCursor(ruleIdIn in integer) is
3962 select
3963 ame_conditions.condition_id condition_id,
3964 ame_conditions.condition_type condition_type
3965 from
3966 ame_conditions,
3967 ame_condition_usages
3968 where
3969 ame_conditions.condition_id = ame_condition_usages.condition_id and
3970 ame_condition_usages.rule_id = ruleIdIn and
3971 (ame_conditions.start_date <= sysdate and
3972 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
3973 ((sysdate between ame_condition_usages.start_date and
3974 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
3975 (sysdate < ame_condition_usages.start_date and
3976 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
3977 ame_condition_usages.start_date + ame_util.oneSecond)))
3978 order by condition_type;
3979 tempIndex integer;
3980 begin
3981 /*
3982 Can't do a bulk collect here because we have to order by condition_type
3983 (so that exception conditions, either pre or post, get displayed after
3984 ordinary conditions), and we don't want to output condition_type.
3985 */
3986 tempIndex := 1;
3987 for tempCondition in conditionCursor(ruleIdIn => ruleIdIn) loop
3988 conditionIdListOut(tempIndex) := tempCondition.condition_id;
3989 tempIndex := tempIndex + 1;
3990 end loop;
3991 if(tempIndex = 1) then
3992 conditionIdListOut := ame_util.emptyIdList;
3993 end if;
3994 exception
3995 when others then
3996 rollback;
3997 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
3998 routineNamein => 'getConditionIds',
3999 exceptionNumberIn => sqlcode,
4000 exceptionStringIn => '(rule ID ' ||
4001 ruleIdIn||
4002 ') ' ||
4003 sqlerrm);
4004 conditionIdListOut := ame_util.emptyIdList;
4005 raise;
4006 end getConditionIds;
4007 procedure getConditions(ruleIdIn in integer,
4008 conditionListOut out nocopy ame_util.stringList,
4009 conditionIdListOut out nocopy ame_util.idList) as
4010 conditionIdList ame_util.idList;
4011 tempConditionType ame_conditions.condition_type%type;
4012 tempDescription varchar2(200);
4013 upperLimit integer;
4014 ruleType ame_rules.rule_type%type;
4015 begin
4016 ruleType := getRuleType(ruleIdIn => ruleIdIn);
4017 getConditionIds(ruleIdIn => ruleIdIn,
4018 conditionIdListOut => conditionIdList);
4019 upperLimit := conditionIdList.count;
4020 if(upperLimit = 0) then
4021 conditionListOut := ame_util.emptyStringList;
4022 conditionIdListOut := ame_util.emptyIdList;
4023 else
4024 for tempIndex in 1..upperLimit loop
4025 tempConditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdList(tempIndex));
4026 if(tempConditionType = ame_util.exceptionConditionType) then
4027 tempDescription := 'Exception: ' ||
4028 ame_condition_pkg.getDescription(conditionIdIn => conditionIdList(tempIndex));
4029 elsif(tempConditionType = ame_util.listModConditionType) then
4030 tempDescription := 'List Modification: ' ||
4031 ame_condition_pkg.getDescription(conditionIdIn => conditionIdList(tempIndex));
4032 else
4033 tempDescription :=
4034 ame_condition_pkg.getDescription(conditionIdIn => conditionIdList(tempIndex));
4035 end if;
4036 conditionListOut(tempIndex) := substrb(tempDescription, 1, 100);
4037 conditionIdListOut(tempIndex) := conditionIdList(tempIndex);
4038 end loop;
4039 end if;
4040 exception
4041 when others then
4042 rollback;
4043 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4044 routineNamein => 'getConditions',
4045 exceptionNumberIn => sqlcode,
4046 exceptionStringIn => '(rule ID ' ||
4047 ruleIdIn||
4048 ') ' ||
4049 sqlerrm);
4050 conditionListOut := ame_util.emptyStringList;
4051 conditionIdListOut := ame_util.emptyIdList;
4052 raise;
4053 end getConditions;
4054 procedure getDetailUrls(ruleIdsIn in ame_util.idList,
4055 applicationIdIn in integer,
4056 usageEndDatesIn in ame_util.dateList default ame_util.emptyDateList,
4057 usageStartDatesIn in ame_util.dateList,
4058 detailUrlsOut out nocopy ame_util.longStringList) as
4059 ruleIdCount integer;
4060 usageStartDate ame_util.stringType;
4061 begin
4062 ruleIdCount := ruleIdsIn.count;
4063 for i in 1..ruleIdCount loop
4064 detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
4065 'ame_rules_ui.getDetails?ruleIdIn=' ||
4066 ruleIdsIn(i) ||
4067 '&applicationIdIn=' ||
4068 applicationIdIn ||
4069 '&displayUsagesIn=' ||
4070 ame_util.booleanTrue ||
4071 '&usageEndDateIn=' ||
4072 ame_util.versionDateToString(dateIn => usageEndDatesIn(i)) ||
4073 '&usageStartDateIn=' ||
4074 ame_util.versionDateToString(dateIn => usageStartDatesIn(i)));
4075 end loop;
4076 exception
4077 when others then
4078 rollback;
4079 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4080 routineNamein => 'getDetailUrls',
4081 exceptionNumberIn => sqlcode,
4082 exceptionStringIn => '(application ID ' ||
4083 applicationIdIn||
4084 ') ' ||
4085 sqlerrm);
4086 detailUrlsOut := ame_util.emptyLongStringList;
4087 raise;
4088 end getDetailUrls;
4089 procedure getOrdinaryAttributeIds(ruleIdIn in integer,
4090 attributeIdListOut out nocopy ame_util.idList) as
4091 cursor attributeCursor(ruleIdIn in integer) is
4092 select ame_conditions.attribute_id attribute_id
4093 from
4094 ame_conditions,
4095 ame_condition_usages
4096 where
4097 ame_conditions.condition_type = ame_util.ordinaryConditionType and
4098 ame_conditions.condition_id = ame_condition_usages.condition_id and
4099 ame_condition_usages.rule_id = ruleIdIn and
4100 (ame_conditions.start_date <= sysdate and
4101 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
4102 ((sysdate between ame_condition_usages.start_date and
4103 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
4104 (sysdate < ame_condition_usages.start_date and
4105 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
4106 order by attribute_id;
4107 tempIndex integer;
4108 begin
4109 tempIndex := 1;
4110 for tempAttribute in attributeCursor(ruleIdIn => ruleIdIn) loop
4111 attributeIdListOut(tempIndex) := tempAttribute.attribute_id;
4112 tempIndex := tempIndex + 1;
4113 end loop;
4114 exception
4115 when others then
4116 rollback;
4117 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4118 routineNamein => 'getOrdinaryAttributeIds',
4119 exceptionNumberIn => sqlcode,
4120 exceptionStringIn => '(rule ID ' ||
4121 ruleIdIn||
4122 ') ' ||
4123 sqlerrm);
4124 attributeIdListOut := ame_util.emptyIdList;
4125 raise;
4126 end getOrdinaryAttributeIds;
4127 procedure getRequiredAttributes(ruleIdIn in integer,
4128 attributeIdsOut out nocopy ame_util.idList) as
4129 cursor attributeCursor(ruleIdIn in integer) is
4130 select ame_conditions.attribute_id attribute_id
4131 from
4132 ame_conditions,
4133 ame_condition_usages
4134 where
4135 ame_conditions.condition_type in (ame_util.ordinaryConditionType, ame_util.exceptionConditionType) and
4136 ame_condition_usages.rule_id = ruleIdIn and
4137 ame_condition_usages.condition_id = ame_conditions.condition_id and
4138 (ame_conditions.start_date <= sysdate and
4139 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
4140 ((sysdate between ame_condition_usages.start_date and
4141 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
4142 (sysdate < ame_condition_usages.start_date and
4143 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
4144 ame_condition_usages.start_date + ame_util.oneSecond)))
4145 union
4146 select ame_mandatory_attributes.attribute_id attribute_id
4147 from
4148 ame_mandatory_attributes,
4149 ame_action_usages,
4150 ame_actions
4151 where
4152 ame_mandatory_attributes.action_type_id = ame_actions.action_type_id and
4153 ame_actions.action_id = ame_action_usages.action_id and
4154 ame_action_usages.rule_id = ruleIdIn and
4155 (ame_mandatory_attributes.start_date <= sysdate and
4156 (ame_mandatory_attributes.end_date is null or sysdate < ame_mandatory_attributes.end_date)) and
4157 ((sysdate between ame_action_usages.start_date and
4158 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
4159 (sysdate < ame_action_usages.start_date and
4160 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
4161 ame_action_usages.start_date + ame_util.oneSecond))) and
4162 (ame_actions.start_date <= sysdate and
4163 (ame_actions.end_date is null or sysdate < ame_actions.end_date));
4164 tempIndex integer;
4165 begin
4166 tempIndex := 0;
4167 for tempAttribute in attributeCursor(ruleIdIn => ruleIdIn) loop
4168 tempIndex := tempIndex + 1;
4169 attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
4170 end loop;
4171 exception
4172 when others then
4173 rollback;
4174 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4175 routineNameIn => 'getRequiredAttributes',
4176 exceptionNumberIn => sqlcode,
4177 exceptionStringIn => '(rule ID ' ||
4178 ruleIdIn||
4179 ') ' ||
4180 sqlerrm);
4181 attributeIdsOut := ame_util.emptyIdList;
4182 raise;
4183 end getRequiredAttributes;
4184 procedure getRuleAppUsages(ruleIdIn in integer,
4185 transactionTypeDescriptionsOut out nocopy ame_util.stringList) as
4186 cursor getAppUsagesCursor(ruleIdIn in integer) is
4187 select application_id,
4188 application_name
4189 from ame_rule_usages,
4190 ame_calling_apps
4191 where
4192 ame_rule_usages.item_id = ame_calling_apps.application_id and
4193 ame_rule_usages.rule_id = ruleIdIn and
4194 (ame_rule_usages.start_date <= sysdate and
4195 (ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date)) and
4196 (ame_calling_apps.start_date <= sysdate and
4197 (ame_calling_apps.end_date is null or sysdate < ame_calling_apps.end_date))
4198 order by application_name;
4199 tempIndex integer;
4200 begin
4201 tempIndex := 1;
4202 for getAppUsagesRec in getAppUsagesCursor(ruleIdIn => ruleIdIn) loop
4203 transactionTypeDescriptionsOut(tempIndex) :=
4204 getAppUsagesRec.application_name;
4205 tempIndex := tempIndex + 1;
4206 end loop;
4207 exception
4208 when others then
4209 rollback;
4210 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4211 routineNamein => 'getRuleAppUsages',
4212 exceptionNumberIn => sqlcode,
4213 exceptionStringIn => '(rule ID ' ||
4214 ruleIdIn||
4215 ') ' ||
4216 sqlerrm);
4217 transactionTypeDescriptionsOut := ame_util.emptyStringList;
4218 raise;
4219 end getRuleAppUsages;
4220 procedure getRuleUsages(ruleIdIn in integer,
4221 applicationIdsOut out nocopy ame_util.idList,
4222 prioritiesOut out nocopy ame_util.stringList) as
4223 cursor getRuleUsageCursor(ruleIdIn in integer) is
4224 select item_id,
4225 priority
4226 from ame_rule_usages
4227 where rule_id = ruleIdIn and
4228 ((sysdate between start_date and
4229 nvl(end_date - ame_util.oneSecond, sysdate)) or
4230 (sysdate < start_date and
4231 start_date < nvl(end_date, start_date + ame_util.oneSecond)));
4232 tempIndex integer;
4233 begin
4234 tempIndex := 1;
4235 for getRuleUsageRec in getRuleUsageCursor(ruleIdIn => ruleIdIn) loop
4236 applicationIdsOut(tempIndex) := getRuleUsageRec.item_id;
4237 prioritiesOut(tempIndex) := getRuleUsageRec.priority;
4238 tempIndex := tempIndex + 1;
4239 end loop;
4240 exception
4241 when others then
4242 rollback;
4243 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4244 routineNamein => 'getRuleUsages',
4245 exceptionNumberIn => sqlcode,
4246 exceptionStringIn => sqlerrm);
4247 applicationIdsOut := ame_util.emptyIdList;
4248 prioritiesOut := ame_util.emptyStringList;
4249 raise;
4250 end getRuleUsages;
4251 /*
4252 AME_STRIPING
4253 procedure getStripeSetRules(stripeSetIdIn in integer,
4254 ruleIdsOut out nocopy ame_util.idList) is
4255 cursor getRuleIdCursor(stripeSetIdIn in integer) is
4256 select rule_id
4257 from ame_rule_stripe_sets
4258 where
4259 stripe_set_id = stripeSetIdIn and
4260 (start_date <= sysdate and
4261 (end_date is null or sysdate < end_date));
4262 begin
4263 open getRuleIdCursor(stripeSetIdIn => stripeSetIdIn);
4264 fetch getRuleIdCursor bulk collect
4265 into ruleIdsOut;
4266 close getRuleIdCursor;
4267 exception
4268 when others then
4269 rollback;
4270 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4271 routineNameIn => 'getStripeSetRules',
4272 exceptionNumberIn => sqlcode,
4273 exceptionStringIn => sqlerrm);
4274 ruleIdsOut := ame_util.emptyIdList;
4275 raise;
4276 end getStripeSetRules;
4277 */
4278 /*
4279 AME_STRIPING
4280 procedure getStripeSets(ruleIdIn in integer,
4281 effectiveRuleDateIn in date default sysdate,
4282 stripeSetIdsOut out nocopy ame_util.idList) is
4283 cursor stripeSetCursor(ruleIdIn in integer,
4284 effectiveRuleDateIn in date) is
4285 select stripe_set_id
4286 from ame_rule_stripe_sets
4287 where
4288 rule_id = ruleIdIn and
4289 effectiveRuleDateIn between
4290 (start_date + ame_util.oneSecond) and
4291 nvl(end_date - ame_util.oneSecond, effectiveRuleDateIn);
4292 begin
4293 open stripeSetCursor(ruleIdIn => ruleIdIn,
4294 effectiveRuleDateIn => effectiveRuleDateIn);
4295 fetch stripeSetCursor bulk collect
4296 into stripeSetIdsOut;
4297 close stripeSetCursor;
4298 exception
4299 when others then
4300 rollback;
4301 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4302 routineNameIn => 'getStripeSets',
4303 exceptionNumberIn => sqlcode,
4304 exceptionStringIn => sqlerrm);
4305 stripeSetIdsOut := ame_util.emptyIdList;
4306 raise;
4307 end getStripeSets;
4308 */
4309 procedure getTransTypeItemClasses(applicationIdIn in integer,
4310 itemClassIdIn in integer,
4311 itemClassIdsOut out nocopy ame_util.stringList,
4312 itemClassNamesOut out nocopy ame_util.stringList) as
4313 cursor itemClassesCursor(itemClassIdIn in integer) is
4314 select to_char(ame_item_classes.item_class_id),
4315 name
4316 from ame_item_classes,
4317 ame_item_class_usages
4318 where
4319 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
4320 ame_item_class_usages.application_id = applicationIdIn and
4321 ame_item_classes.item_class_id <> itemClassIdIn and
4322 sysdate between ame_item_classes.start_date and
4323 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
4324 sysdate between ame_item_class_usages.start_date and
4325 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
4326 order by ame_item_classes.item_class_id;
4327 begin
4328 open itemClassesCursor(itemClassIdIn => itemClassIdIn);
4329 fetch itemClassesCursor bulk collect
4330 into itemClassIdsOut,
4331 itemClassNamesOut;
4332 close itemClassesCursor;
4333 exception
4334 when others then
4335 rollback;
4336 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4337 routineNamein => 'getTransTypeItemClasses',
4338 exceptionNumberIn => sqlcode,
4339 exceptionStringIn => sqlerrm);
4340 itemClassIdsOut := ame_util.emptyStringList;
4341 itemClassNamesOut := ame_util.emptyStringList;
4342 raise;
4343 end getTransTypeItemClasses;
4344 procedure getTypedConditions(ruleIdIn in integer,
4345 conditionTypeIn in varchar2,
4346 conditionIdsOut out nocopy ame_util.idList) as
4347 cursor conditionCursor(ruleIdIn in integer,
4348 conditionTypeIn in varchar2) is
4349 select
4350 ame_conditions.condition_id condition_id
4351 from
4352 ame_conditions,
4353 ame_condition_usages
4354 where
4355 ame_conditions.condition_type = conditionTypeIn and
4356 ame_condition_usages.rule_id = ruleIdIn and
4357 ame_condition_usages.condition_id = ame_conditions.condition_id and
4358 (ame_conditions.start_date <= sysdate and
4359 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
4360 ((sysdate between ame_condition_usages.start_date and
4361 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
4362 (sysdate < ame_condition_usages.start_date and
4363 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
4364 ame_condition_usages.start_date + ame_util.oneSecond)))
4365 order by condition_type;
4366 tempIndex integer;
4367 begin
4368 tempIndex := 1;
4369 for tempCondition in conditionCursor(ruleIdIn => ruleIdIn,
4370 conditionTypeIn => conditionTypeIn) loop
4371 conditionIdsOut(tempIndex) := tempCondition.condition_id;
4372 tempIndex := tempIndex + 1;
4373 end loop;
4374 exception
4375 when others then
4376 rollback;
4377 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4378 routineNamein => 'getTypedConditions',
4379 exceptionNumberIn => sqlcode,
4380 exceptionStringIn => '(rule ID ' ||
4381 ruleIdIn||
4382 ') ' ||
4383 sqlerrm);
4384 conditionIdsOut := ame_util.emptyIdList;
4385 raise;
4386 end getTypedConditions;
4387 procedure getTypedConditions2(ruleIdIn in integer,
4388 conditionTypeIn in varchar2,
4389 conditionListOut out nocopy ame_util.longStringList,
4390 conditionIdsOut out nocopy ame_util.idList) as
4391 cursor conditionCursor(ruleIdIn in integer,
4392 conditionTypeIn in varchar2) is
4393 select
4394 ame_conditions.condition_id condition_id
4395 from
4396 ame_conditions,
4397 ame_condition_usages
4398 where
4399 ame_conditions.condition_type = conditionTypeIn and
4400 ame_condition_usages.rule_id = ruleIdIn and
4401 ame_condition_usages.condition_id = ame_conditions.condition_id and
4402 (ame_conditions.start_date <= sysdate and
4403 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
4404 ((sysdate between ame_condition_usages.start_date and
4405 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
4406 (sysdate < ame_condition_usages.start_date and
4407 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
4408 ame_condition_usages.start_date + ame_util.oneSecond)))
4409 order by condition_type;
4410 tempIndex integer;
4411 begin
4412 tempIndex := 1;
4413 for tempCondition in conditionCursor(ruleIdIn => ruleIdIn,
4414 conditionTypeIn => conditionTypeIn) loop
4415 conditionIdsOut(tempIndex) := tempCondition.condition_id;
4416 conditionListOut(tempIndex) :=
4417 ame_condition_pkg.getDescription(conditionIdIn => tempCondition.condition_id);
4418 tempIndex := tempIndex + 1;
4419 end loop;
4420 exception
4421 when others then
4422 rollback;
4423 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4424 routineNamein => 'getTypedConditions2',
4425 exceptionNumberIn => sqlcode,
4426 exceptionStringIn => '(rule ID ' ||
4427 ruleIdIn||
4428 ') ' ||
4429 sqlerrm);
4430 conditionIdsOut := ame_util.emptyIdList;
4431 raise;
4432 end getTypedConditions2;
4433 procedure remove(ruleIdIn in integer,
4434 finalizeIn in boolean default true,
4435 processingDateIn in date default null) as
4436 currentUserId integer;
4437 errorCode integer;
4438 errorMessage ame_util.longestStringType;
4439 inUseException exception;
4440 conditionIdList ame_util.idList;
4441 conditionCount integer;
4442 endDate date;
4443 processingDate date;
4444 begin
4445 if(isInUse(ruleIdIn)) then
4446 raise inUseException;
4447 end if;
4448 /* check to see if processingDate has been initialized */
4449 if processingDateIn is null then
4450 processingDate := sysdate;
4451 else
4452 processingDate := processingDateIn;
4453 end if;
4454 endDate := processingDate;
4455 ame_rule_pkg.getConditionIds(ruleIdIn => ruleIdIn,
4456 conditionIdListOut => conditionIdList);
4457 conditionCount := conditionIdList.count;
4458 for tempIndex in 1..conditionCount loop
4459 ame_condition_pkg.removeConditionUsage(ruleIdIn => ruleIdIn,
4460 conditionIdIn => conditionIdList(tempIndex),
4461 finalizeIn => finalizeIn);
4462 end loop;
4463 currentUserId := ame_util.getCurrentUserId;
4464 update ame_rules
4465 set
4466 last_updated_by = currentUserId,
4467 last_update_date = sysdate,
4468 last_update_login = currentUserId,
4469 end_date = endDate
4470 where
4471 rule_id = ruleIdIn and
4472 ((sysdate between start_date and
4473 nvl(end_date - ame_util.oneSecond, sysdate)) or
4474 (sysdate < start_date and
4475 start_date < nvl(end_date, start_date + ame_util.oneSecond)));
4476 if finalizeIn then
4477 commit;
4478 end if;
4479 exception
4480 when inUseException then
4481 rollback;
4482 errorCode := -20001;
4483 errorMessage :=
4484 ame_util.getMessage(applicationShortNameIn => 'PER',
4485 messageNameIn => 'AME_400216_RUL_IN_USE');
4486 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4487 routineNamein => 'remove',
4488 exceptionNumberIn => errorCode,
4489 exceptionStringIn => errorMessage);
4490 raise_application_error(errorCode,
4491 errorMessage);
4492 when others then
4493 rollback;
4494 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4495 routineNamein => 'remove',
4496 exceptionNumberIn => sqlcode,
4497 exceptionStringIn => '(rule ID ' ||
4498 ruleIdIn||
4499 ') ' ||
4500 sqlerrm);
4501 raise;
4502 end remove;
4503 /*
4504 AME_STRIPING
4505 procedure removeRuleStripeSet(stripeSetIdsIn in ame_util.idList default ame_util.emptyIdList,
4506 ruleIdIn in integer default null,
4507 finalizeIn in boolean default false) as
4508 currentUserId integer;
4509 stripeSetCount integer;
4510 begin
4511 currentUserId := ame_util.getCurrentUserId;
4512 stripeSetCount := stripeSetIdsIn.count;
4513 if(stripeSetCount > 0) then
4514 for i in 1..stripeSetCount loop
4515 update ame_rule_stripe_sets
4516 set
4517 last_updated_by = currentUserId,
4518 last_update_date = sysdate,
4519 last_update_login = currentUserId,
4520 end_date = sysdate
4521 where
4522 stripe_set_id = stripeSetIdsIn(i) and
4523 (end_date is null or sysdate < end_date);
4524 end loop;
4525 else
4526 update ame_rule_stripe_sets
4527 set
4528 last_updated_by = currentUserId,
4529 last_update_date = sysdate,
4530 last_update_login = currentUserId,
4531 end_date = sysdate
4532 where
4533 rule_id = ruleIdIn and
4534 (end_date is null or sysdate < end_date);
4535 end if;
4536 if(finalizeIn) then
4537 commit;
4538 end if;
4539 exception
4540 when others then
4541 rollback;
4542 ame_util.runtimeException(packageNamein => 'ame_rule_pkg',
4543 routineNamein => 'removeRuleStripeSet',
4544 exceptionNumberIn => sqlcode,
4545 exceptionStringIn => sqlerrm);
4546 raise;
4547 end removeRuleStripeSet;
4548 */
4549 procedure removeUsage(ruleIdIn in integer,
4550 itemIdIn in integer,
4551 usageStartDateIn in date,
4552 parentVersionStartDateIn in date,
4553 finalizeIn in boolean default true,
4554 processingDateIn in date default null) as
4555 cursor startDateCursor is
4556 select start_date
4557 from ame_rules
4558 where
4559 rule_id = ruleIdIn and
4560 ((sysdate between start_date and
4561 nvl(end_date - ame_util.oneSecond, sysdate)) or
4562 (sysdate < start_date and
4563 start_date < nvl(end_date, start_date + ame_util.oneSecond)))
4564 for update;
4565 actionCount integer;
4566 actionIdList ame_util.idList;
4567 attributeIdList ame_util.idList;
4568 conditionCount integer;
4569 conditionIdList ame_util.idList;
4570 currentUserId integer;
4571 description ame_rules.description%type;
4572 newRuleEndDate ame_rules.end_date%type;
4573 endDate date;
4574 processingDate date;
4575 errorCode integer;
4576 errorMessage ame_util.longestStringType;
4577 itemClassId integer;
4578 objectVersionNoDataException exception;
4579 ruleId ame_rules.rule_id%type;
4580 ruleKey ame_rules.rule_key%type;
4581 ruleType ame_rules.rule_type%type;
4582 startDate date;
4583 begin
4584 /* check to see if processingDate has been initialized */
4585 if processingDateIn is null then
4586 processingDate := sysdate;
4587 else
4588 processingDate := processingDateIn;
4589 end if;
4590 endDate := processingDate;
4591 open startDateCursor;
4592 fetch startDateCursor into startDate;
4593 if startDateCursor%notfound then
4594 raise objectVersionNoDataException;
4595 end if;
4596 if parentVersionStartDateIn <> startDate then
4597 close startDateCursor;
4598 raise ame_util.objectVersionException;
4599 end if;
4600 getRequiredAttributes(ruleIdIn => ruleIdIn,
4601 attributeIdsOut => attributeIdList);
4602 currentUserId := ame_util.getCurrentUserId;
4603 update ame_rule_usages
4604 set
4605 last_updated_by = currentUserId,
4606 last_update_date = processingDate,
4607 last_update_login = currentUserId,
4608 end_date = endDate
4609 where
4610 rule_id = ruleIdIn and
4611 item_id = itemIdIn and
4612 start_date = usageStartDateIn and
4613 start_date < nvl(end_date, start_date + ame_util.onesecond);
4614 changeAttributeUseCounts2(attributeIdsIn => attributeIdList,
4615 applicationIdIn => itemIdIn,
4616 finalizeIn => false);
4617 ruleKey := getRuleKey(ruleIdIn => ruleIdIn);
4618 ruleType := getRuleType(ruleIdIn => ruleIdIn);
4619 description := getDescription(ruleIdIn => ruleIdIn);
4620 itemClassId := getItemClassId(ruleIdIn => ruleIdIn);
4621 newRuleEndDate := getNewRuleEndDate(ruleIdIn => ruleIdIn,
4622 processingDateIn => processingDate);
4623 getConditionIds(ruleIdIn => ruleIdIn,
4624 conditionIdListOut => conditionIdList);
4625 conditionCount := conditionIdList.count;
4626 getActionIds(ruleIdIn => ruleIdIn,
4627 actionIdListOut => actionIdList);
4628 actionCount := actionIdList.count;
4629 if conditionCount > 0 then
4630 for i in 1..conditionCount loop
4631 update ame_condition_usages
4632 set
4633 last_updated_by = currentUserId,
4634 last_update_date = processingDate,
4635 last_update_login = currentUserId,
4636 end_date = processingDate
4637 where
4638 rule_id = ruleIdIn and
4639 condition_id = conditionIdList(i) and
4640 ((sysdate between start_date and
4641 nvl(end_date - ame_util.oneSecond, sysdate)) or
4642 (sysdate < start_date and
4643 start_date < nvl(end_date, start_date + ame_util.oneSecond)));
4644 end loop;
4645 end if;
4646 if actionCount > 0 then
4647 for i in 1..actionCount loop
4648 update ame_action_usages
4649 set
4650 last_updated_by = currentUserId,
4651 last_update_date = processingDate,
4652 last_update_login = currentUserId,
4653 end_date = processingDate
4654 where
4655 rule_id = ruleIdIn and
4656 action_id = actionIdList(i) and
4657 ((sysdate between start_date and
4658 nvl(end_date - ame_util.oneSecond, sysdate)) or
4659 (sysdate < start_date and
4660 start_date < nvl(end_date, start_date + ame_util.oneSecond)));
4661 end loop;
4662 end if;
4663 if not isInUse(ruleIdIn) then
4664 remove(ruleIdIn => ruleIdIn,
4665 finalizeIn => false,
4666 processingDateIn => processingDateIn);
4667 /*
4668 AME_STRIPING
4669 if(ame_admin_pkg.isStripingOn(applicationIdIn => itemIdIn)) then
4670 dropRuleStripeSet(ruleIdIn => ruleIdIn,
4671 applicationIdIn => itemIdIn);
4672 ame_admin_pkg.updateStripingAttUseCount(applicationIdIn => itemIdIn);
4673 end if;
4674 */
4675 else
4676 update ame_rules
4677 set
4678 last_updated_by = currentUserId,
4679 last_update_date = processingDate,
4680 last_update_login = currentUserId,
4681 end_date = endDate
4682 where
4683 rule_id = ruleIdIn and
4684 ((processingDate between start_date and
4685 nvl(end_date - ame_util.oneSecond, processingDate)) or
4686 (processingDate < start_date and
4687 start_date < nvl(end_date, start_date + ame_util.oneSecond)));
4688 ruleId := new(applicationIdIn => itemIdIn,
4689 typeIn => ruleType,
4690 conditionIdsIn => conditionIdList,
4691 actionIdsIn => actionIdList,
4692 ruleKeyIn => ruleKey,
4693 descriptionIn => description,
4694 itemClassIdIn => itemClassId,
4695 startDateIn => processingDate,
4696 endDateIn => newRuleEndDate,
4697 ruleIdIn => ruleIdIn,
4698 finalizeIn => false,
4699 processingDateIn => processingDate);
4700 end if;
4701 close startDateCursor;
4702 if(finalizeIn) then
4703 commit;
4704 end if;
4705 exception
4706 when ame_util.objectVersionException then
4707 rollback;
4708 if(startDateCursor%isOpen) then
4709 close startDateCursor;
4710 end if;
4711 errorCode := -20001;
4712 errorMessage :=
4713 ame_util.getMessage(applicationShortNameIn => 'PER',
4714 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4715 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4716 routineNameIn => 'removeUsage',
4717 exceptionNumberIn => errorCode,
4718 exceptionStringIn => errorMessage);
4719 raise_application_error(errorCode,
4720 errorMessage);
4721 when objectVersionNoDataException then
4722 rollback;
4723 if(startDateCursor%isOpen) then
4724 close startDateCursor;
4725 end if;
4726 errorCode := -20001;
4727 errorMessage :=
4728 ame_util.getMessage(applicationShortNameIn => 'PER',
4729 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
4730 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4731 routineNameIn => 'removeUsage',
4732 exceptionNumberIn => errorCode,
4733 exceptionStringIn => errorMessage);
4734 raise_application_error(errorCode,
4735 errorMessage);
4736 when others then
4737 rollback;
4738 if(startDateCursor%isOpen) then
4739 close startDateCursor;
4740 end if;
4741 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4742 routineNameIn => 'removeUsage',
4743 exceptionNumberIn => sqlcode,
4744 exceptionStringIn => '(rule ID ' ||
4745 ruleIdIn||
4746 ') ' ||
4747 sqlerrm);
4748 raise;
4749 end removeUsage;
4750 /*
4751 AME_STRIPING
4752 procedure updateRuleStripeSets(applicationIdIn in integer,
4753 ruleIdIn in integer,
4754 conditionIdsIn in ame_util.idList,
4755 finalizeIn in boolean default false) as
4756 */
4757 /* This procedure updates the rule's stripe set in each transaction type that uses striping. */
4758 /*
4759 cursor applicationIdCursor is
4760 select application_id
4761 from ame_calling_apps
4762 where
4763 start_date <= sysdate and
4764 (end_date is null or sysdate < end_date);
4765 applicationIds ame_util.idList;
4766 attributeIds ame_util.idList;
4767 newAttributeCount integer;
4768 stringValues ame_util.longestStringList;
4769 stripingAttributeIds ame_util.idList;
4770 stripingAttributeValues ame_util.stringList;
4771 stripingAttributeCount integer;
4772 equalityCondStringValues ame_util.stringList;
4773 stripeSetIds ame_util.idList;
4774 tempIndex integer;
4775 tempStripeSetId integer;
4776 begin
4777 open applicationIdCursor;
4778 fetch applicationIdCursor bulk collect into applicationIds;
4779 close applicationIdCursor;
4780 */
4781 /*
4782 Fetch attribute IDs corresponding to the ordinary equality conditions on string attributes
4783 (possible striping conditions).
4784 */
4785 /* tempIndex := 0; pre-increment */
4786 /*
4787 for i in 1 .. conditionIdsIn.count loop
4788 if(ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.ordinaryConditionType and
4789 ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType) then
4790 ame_condition_pkg.getStringValueList(conditionIdIn => conditionIdsIn(i),
4791 stringValueListOut => stringValues);
4792 if(stringValues.count = 1) then */ /* equality condition, and so possible striping condition */
4793 /*
4794 tempIndex := tempIndex + 1;
4795 attributeIds(tempIndex) := ame_condition_pkg.getAttributeId(conditionIdIn => conditionIdsIn(i));
4796 equalityCondStringValues(tempIndex) := stringValues(1);
4797 end if;
4798 end if;
4799 end loop;
4800 for i in 1 .. applicationIds.count loop
4801 if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn)) then
4802 ame_admin_pkg.getStripingAttributeIds(applicationIdIn => applicationIdIn,
4803 stripingAttributeIdsOut => stripingAttributeIds);
4804 stripingAttributeCount := stripingAttributeIds.count; */
4805 /* Initialize the striping-attribute values to the wildcard. */
4806 /*
4807 for j in 1 .. stripingAttributeCount loop
4808 stripingAttributeValues(j) := ame_util.stripeWildcard;
4809 end loop;
4810 */
4811 /* Initialize the remaining columns to null. */
4812 /*
4813 for j in stripingAttributeCount + 1 .. 5 loop
4814 stripingAttributeValues(j) := null;
4815 end loop;
4816 */
4817 /*
4818 Find the rule's striping conditions for the ith transaction type, and populate
4819 stripingAttributeValues with the corresponding string values.
4820 */
4821 /*
4822 for j in 1 .. attributeIds.count loop
4823 for k in 1 .. stripingAttributeCount loop
4824 if(stripingAttributeIds(k) = attributeIds(j)) then */ /* striping attribute */
4825 /*
4826 stripingAttributeValues(k) := equalityCondStringValues(j);
4827 exit; */ /* at most one striping condition per striping attribute */
4828 /*
4829 end if;
4830 end loop;
4831 end loop;
4832 */
4833 /* Check to see if rule was just created. (If just created, the rule
4834 will not have a stripe set.) */
4835 /* Rule is not new, so drop the rule from its old stripe set. */
4836 /*
4837 dropRuleStripeSet(ruleIdIn => ruleIdIn,
4838 applicationIdIn => applicationIdIn);
4839 */
4840 /* Add the rule to its new stripe set, creating that stripe set if needed. */
4841 /*
4842 tempStripeSetId := ame_admin_pkg.getStripeSetId(applicationIdIn => applicationIdIn,
4843 attributeValuesIn => stripingAttributeValues);
4844 if(tempStripeSetId is null) then
4845 tempStripeSetId := ame_admin_pkg.newStripeSet(applicationIdIn => applicationIdIn,
4846 attributeValuesIn => stripingAttributeValues);
4847 end if;
4848 ame_rule_pkg.newRuleStripeSet(applicationIdIn => applicationIdIn,
4849 ruleIdIn => ruleIdIn,
4850 stripeSetIdIn => tempStripeSetId);
4851 end if;
4852 end loop;
4853 if(finalizeIn) then
4854 commit;
4855 end if;
4856 exception
4857 when others then
4858 rollback;
4859 if(applicationIdCursor%isopen) then
4860 close applicationIdCursor;
4861 end if;
4862 ame_util.runtimeException(packageNameIn => 'ame_rule_pkg',
4863 routineNameIn => 'updateRuleStripeSets',
4864 exceptionNumberIn => sqlcode,
4865 exceptionStringIn => '(rule ID ' ||
4866 ruleIdIn||
4867 ') ' ||
4868 sqlerrm);
4869 raise;
4870 end updateRuleStripeSets;
4871 */
4872 /* Get the rule id only once */
4873 end ame_rule_pkg;