[Home] [Help]
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,
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;
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
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;
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;
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);
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);
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;
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
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
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;
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;
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,
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);
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;
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;
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;
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
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;
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;
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;
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;
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;
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
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,
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
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
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
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
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;
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,
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;
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;
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;
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;
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;
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;
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);
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);
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;
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);
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;
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;
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);
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
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);
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;
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;
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
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
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;
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||
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
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;
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||
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
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
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
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||
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
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);
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;
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||
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||
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;
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||
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
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;
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||
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
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
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
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||
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
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'));
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
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')));
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');
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
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'));
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
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
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')));
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
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')));
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
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);
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;
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
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
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'));
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
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'));
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
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'));
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
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'));
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
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'));
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
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'));
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
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);
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;
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
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;
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
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
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||
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
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;
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||
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);
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||
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);
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||
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',
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||
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
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
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;
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;
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;
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;
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
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;
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
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,
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
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;
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
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;
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 ||
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);
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 ||
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;
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
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 ||
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
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
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
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||
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;
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);
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||
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
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
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||
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);
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
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
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),
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)));
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)));
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)));
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)));
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)));
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)));
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);
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;
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
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
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;
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||
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));
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;
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 :=
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);
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;
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||
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',
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||
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
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;
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||
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);
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
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;
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||
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
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
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;
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,
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);
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: /*
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
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
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;
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
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);
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;
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
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);
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;
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
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
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||
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 :=
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);
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;
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
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);
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;
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
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
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
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);
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);
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;
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
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;
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));
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;
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);
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);
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||
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);
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);
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||
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
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;
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;
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);
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;
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,
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,
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;
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;
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. */
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
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;
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;
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;
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;
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;
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',
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,
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,
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,
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'));
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,
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,
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,
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,
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,
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||
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;
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(*)
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
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||
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;
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;
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;
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;
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;
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;
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;
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;
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;
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
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;
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;
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,
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
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
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;
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;
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;
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,
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,
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
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;
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
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,
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,
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;
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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||
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);
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;
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;
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);
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
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);
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;
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;
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
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,
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
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);
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;
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,
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,
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
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,
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;
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;
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;
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;
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
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;
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;
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,
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
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;
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
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;
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 ||
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
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
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;
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;
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;
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;
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
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;
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;
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
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
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;
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
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;
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 ||
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,
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));
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);
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
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
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;
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,
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)))
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,
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)))
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,
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)))
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,
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)))
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,
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)))
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,
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)))
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,
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);
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);
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;
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,
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,
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,
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;
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;
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;
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;
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;
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;
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;
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;
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);
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
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;
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;
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;
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
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,
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
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,
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,
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,
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;
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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||
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
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,
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||
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);
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
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||
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,
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;
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
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
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;
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
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;
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;
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,
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',
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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||
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||
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;
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;
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||
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,
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,
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,
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
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,
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;
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;
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
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
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,
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;
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 */
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
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||
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,
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,
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,
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,
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
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)
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;
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
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
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,
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;
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 */
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;
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||
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,
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
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
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,
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
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
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
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
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,
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;
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
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||
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
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,
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
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,
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;
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
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);
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||
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,
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
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
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;
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: /*
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;
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||
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,
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);
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;
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;
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));
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
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: ' ||
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 :=
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||
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,
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,
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
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;
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
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;
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 ||
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 ||
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)));
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
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;
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||
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
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,
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
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;
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;
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||
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
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,
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
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
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,
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
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
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||
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
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,
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||
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,
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
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
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
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
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;
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: /*
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
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
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;
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: /*
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
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);
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
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;
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,
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
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,
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
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
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;
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,
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,
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
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;
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;
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||
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,
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
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
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;
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;
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||
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,
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;
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
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,
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;
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,
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,
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||
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;
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
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;
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;
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;
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;
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;
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;
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;
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;
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,
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);
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;
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
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;
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,
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,
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,
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;
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,
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,
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,
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,
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||
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: /*
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;
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;
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;
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;
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;
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
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;
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 */
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: /*
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: /*
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||