[Home] [Help]
8: from ame_conditions
9: where
10: condition_id = conditionIdIn and
11: sysdate between start_date and
12: nvl(end_date - ame_util.oneSecond, sysdate);
13: return(attributeId);
14: exception
15: when others then
16: rollback;
13: return(attributeId);
14: exception
15: when others then
16: rollback;
17: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
18: routineNameIn => 'getAttributeId',
19: exceptionNumberIn => sqlcode,
20: exceptionStringIn => '(condition ID ' ||
21: conditionIdIn||
35: where
36: ame_conditions.condition_id = conditionIdIn and
37: ame_conditions.attribute_id = ame_attributes.attribute_id and
38: sysdate between ame_attributes.start_date and
39: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
40: sysdate between ame_conditions.start_date and
41: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
42: return(attributeName);
43: exception
37: ame_conditions.attribute_id = ame_attributes.attribute_id and
38: sysdate between ame_attributes.start_date and
39: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
40: sysdate between ame_conditions.start_date and
41: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
42: return(attributeName);
43: exception
44: when others then
45: rollback;
42: return(attributeName);
43: exception
44: when others then
45: rollback;
46: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
47: routineNameIn => 'getAttributeName',
48: exceptionNumberIn => sqlcode,
49: exceptionStringIn => '(condition ID ' ||
50: conditionIdIn||
64: where
65: ame_conditions.condition_id = conditionIdIn and
66: ame_attributes.attribute_id = ame_conditions.attribute_id and
67: sysdate between ame_attributes.start_date and
68: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
69: sysdate between ame_conditions.start_date and
70: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
71: return(attributeType);
72: exception
66: ame_attributes.attribute_id = ame_conditions.attribute_id and
67: sysdate between ame_attributes.start_date and
68: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
69: sysdate between ame_conditions.start_date and
70: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
71: return(attributeType);
72: exception
73: when others then
74: rollback;
71: return(attributeType);
72: exception
73: when others then
74: rollback;
75: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
76: routineNameIn => 'getAttributeType',
77: exceptionNumberIn => sqlcode,
78: exceptionStringIn => '(condition ID ' ||
79: conditionIdIn||
91: ame_conditions
92: where
93: ame_conditions.condition_id = conditionIdIn and
94: sysdate between start_date and
95: nvl(end_date - ame_util.oneSecond, sysdate);
96: return(conditionType);
97: exception
98: when others then
99: rollback;
96: return(conditionType);
97: exception
98: when others then
99: rollback;
100: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
101: routineNameIn => 'getConditionType',
102: exceptionNumberIn => sqlcode,
103: exceptionStringIn => '(condition ID ' ||
104: conditionIdIn||
117: from ame_conditions
118: where
119: condition_id = conditionIdIn and
120: ((sysdate between start_date and
121: nvl(end_date - ame_util.oneSecond, sysdate)) or
122: (sysdate < start_date and
123: start_date < nvl(end_date,start_date +ame_util.oneSecond)));
124: else
125: select condition_key
119: condition_id = conditionIdIn and
120: ((sysdate between start_date and
121: nvl(end_date - ame_util.oneSecond, sysdate)) or
122: (sysdate < start_date and
123: start_date < nvl(end_date,start_date +ame_util.oneSecond)));
124: else
125: select condition_key
126: into conditionKey
127: from ame_conditions
127: from ame_conditions
128: where
129: condition_id = conditionIdIn and
130: (processingDateIn between start_date and
131: nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
132: end if;
133: return(conditionKey);
134: exception
135: when others then
133: return(conditionKey);
134: exception
135: when others then
136: rollback;
137: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
138: routineNamein => 'getConditionKey',
139: exceptionNumberIn => sqlcode,
140: exceptionStringIn => '(condition ID ' ||
141: conditionIdIn||
159: end if;
160: exception
161: when others then
162: rollback;
163: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
164: routineNamein => 'conditionKeyExists',
165: exceptionNumberIn => sqlcode,
166: exceptionStringIn => '(condition Key ' ||
167: conditionKeyIn ||
188: -- This case will never happen, since every instance must be linked to a DB
189: databaseId := NULL;
190: end if;
191: close get_dbid_cursor;
192: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
193: seededKeyPrefix := ame_util.seededKeyPrefix;
194: else
195: seededKeyPrefix := null;
196: end if;
189: databaseId := NULL;
190: end if;
191: close get_dbid_cursor;
192: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
193: seededKeyPrefix := ame_util.seededKeyPrefix;
194: else
195: seededKeyPrefix := null;
196: end if;
197: loop
209: return(newConditionKey);
210: exception
211: when others then
212: rollback;
213: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
214: routineNamein => 'getNextConditionKey',
215: exceptionNumberIn => sqlcode,
216: exceptionStringIn => '(condition Key ' ||
217: newConditionKey ||
226: from ame_string_values
227: where
228: condition_id = conditionIdIn and
229: sysdate between start_date and
230: nvl(end_date - ame_util.oneSecond, sysdate)
231: order by string_value;
232: description varchar2(500);
233: approverDescription ame_util.longStringType;
234: approverName ame_conditions.parameter_two%type;
229: sysdate between start_date and
230: nvl(end_date - ame_util.oneSecond, sysdate)
231: order by string_value;
232: description varchar2(500);
233: approverDescription ame_util.longStringType;
234: approverName ame_conditions.parameter_two%type;
235: approverType ame_conditions.parameter_one%type;
236: approverTypeId ame_attributes.approver_type_id%type;
237: attributeId ame_attributes.attribute_id%type;
255: tempValue ame_string_values.string_value%type;
256: wfRolesName wf_roles.name%type;
257: begin
258: conditionType := getConditionType(conditionIdIn => conditionIdIn);
259: if(conditionType = ame_util.listModConditionType) then
260: approverType :=
261: ame_condition_pkg.getParameterOne(conditionIdIn => conditionIdIn);
262: approverName :=
263: ame_condition_pkg.getParameterTwo(conditionIdIn => conditionIdIn);
262: approverName :=
263: ame_condition_pkg.getParameterTwo(conditionIdIn => conditionIdIn);
264: approverDescription :=
265: ame_approver_type_pkg.getApproverDescription(nameIn => approverName);
266: if(approverType = ame_util.finalApprover) then
267: return(ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') ||' '|| approverDescription);
268: else
269: return(ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') ||' '|| approverDescription);
270: end if;
263: ame_condition_pkg.getParameterTwo(conditionIdIn => conditionIdIn);
264: approverDescription :=
265: ame_approver_type_pkg.getApproverDescription(nameIn => approverName);
266: if(approverType = ame_util.finalApprover) then
267: return(ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') ||' '|| approverDescription);
268: else
269: return(ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') ||' '|| approverDescription);
270: end if;
271: end if;
265: ame_approver_type_pkg.getApproverDescription(nameIn => approverName);
266: if(approverType = ame_util.finalApprover) then
267: return(ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') ||' '|| approverDescription);
268: else
269: return(ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') ||' '|| approverDescription);
270: end if;
271: end if;
272: attributeId := getAttributeId(conditionIdIn => conditionIdIn);
273: approverTypeId := ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeId);
271: end if;
272: attributeId := getAttributeId(conditionIdIn => conditionIdIn);
273: approverTypeId := ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeId);
274: lineItem := ame_attribute_pkg.getLineItem(attributeIdIn => attributeId);
275: if lineItem = ame_util.booleanTrue then
276: lineItemLabel := ame_util.getLabel(ame_util.perFndAppId,'AME_LINE_ITEM_COLON');
277: end if;
278: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeId);
279: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
272: attributeId := getAttributeId(conditionIdIn => conditionIdIn);
273: approverTypeId := ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeId);
274: lineItem := ame_attribute_pkg.getLineItem(attributeIdIn => attributeId);
275: if lineItem = ame_util.booleanTrue then
276: lineItemLabel := ame_util.getLabel(ame_util.perFndAppId,'AME_LINE_ITEM_COLON');
277: end if;
278: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeId);
279: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
280: if(attributeType = ame_util.booleanAttributeType) then
276: lineItemLabel := ame_util.getLabel(ame_util.perFndAppId,'AME_LINE_ITEM_COLON');
277: end if;
278: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeId);
279: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
280: if(attributeType = ame_util.booleanAttributeType) then
281: select parameter_one
282: into parameterOne
283: from ame_conditions
284: where
283: from ame_conditions
284: where
285: condition_id = conditionIdIn and
286: sysdate between start_date and
287: nvl(end_date - ame_util.oneSecond, sysdate) ;
288: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IS') || ' ' ||parameterOne;
289: elsif(attributeType = ame_util.stringAttributeType) then
290: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IN') ||' {';
291: tempIndex := 1;
284: where
285: condition_id = conditionIdIn and
286: sysdate between start_date and
287: nvl(end_date - ame_util.oneSecond, sysdate) ;
288: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IS') || ' ' ||parameterOne;
289: elsif(attributeType = ame_util.stringAttributeType) then
290: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IN') ||' {';
291: tempIndex := 1;
292: for tempStringValue in stringValueCursor(conditionIdIn => conditionIdIn) loop
285: condition_id = conditionIdIn and
286: sysdate between start_date and
287: nvl(end_date - ame_util.oneSecond, sysdate) ;
288: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IS') || ' ' ||parameterOne;
289: elsif(attributeType = ame_util.stringAttributeType) then
290: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IN') ||' {';
291: tempIndex := 1;
292: for tempStringValue in stringValueCursor(conditionIdIn => conditionIdIn) loop
293: tempValue := tempStringValue.string_value;
286: sysdate between start_date and
287: nvl(end_date - ame_util.oneSecond, sysdate) ;
288: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IS') || ' ' ||parameterOne;
289: elsif(attributeType = ame_util.stringAttributeType) then
290: description := lineItemLabel||attributeName || ame_util.getLabel(ame_util.perFndAppId,'AME_IN') ||' {';
291: tempIndex := 1;
292: for tempStringValue in stringValueCursor(conditionIdIn => conditionIdIn) loop
293: tempValue := tempStringValue.string_value;
294: if(tempIndex = 4) then
301: description := description || substrb(tempValue,1,50);
302: tempIndex := tempIndex + 1;
303: end loop;
304: description := description || '}';
305: elsif (attributeType = ame_util.numberAttributeType or
306: attributeType = ame_util.dateAttributeType) then
307: select
308: condition_type,
309: parameter_one,
302: tempIndex := tempIndex + 1;
303: end loop;
304: description := description || '}';
305: elsif (attributeType = ame_util.numberAttributeType or
306: attributeType = ame_util.dateAttributeType) then
307: select
308: condition_type,
309: parameter_one,
310: parameter_two,
320: ame_conditions
321: where
322: condition_id = conditionIdIn and
323: sysdate between start_date and
324: nvl(end_date - ame_util.oneSecond, sysdate) ;
325: /* Check if attribute within the condition is associated with an
326: approver type. */
327: if(approverTypeId is not null) then
328: origSystem :=
335: description := attributeName || ' = ' || 'Invalid :' || origSystem || ':' || parameterTwo;
336: else
337: approverDescription :=
338: ame_approver_type_pkg.getApproverDescription(nameIn => wfRolesName);
339: if(origSystem = ame_util.perOrigSystem) then
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
336: else
337: approverDescription :=
338: ame_approver_type_pkg.getApproverDescription(nameIn => wfRolesName);
339: if(origSystem = ame_util.perOrigSystem) then
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
337: approverDescription :=
338: ame_approver_type_pkg.getApproverDescription(nameIn => wfRolesName);
339: if(origSystem = ame_util.perOrigSystem) then
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
338: ame_approver_type_pkg.getApproverDescription(nameIn => wfRolesName);
339: if(origSystem = ame_util.perOrigSystem) then
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
339: if(origSystem = ame_util.perOrigSystem) then
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
340: if(attributeName = ame_util.firstStartingPointAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
341: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_FIRST_START_POINT_COLON');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
342: elsif(attributeName = ame_util.jobLevelStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
343: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_JOBLVL_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
344: elsif(attributeName = ame_util.secondStartingPointAttribute) then
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
345: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SECOND_START_POINT_COLON');
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
353: description := attributeName || ' ' || approverDescription;
346: elsif(attributeName = ame_util.topSupPersonIdAttribute) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
353: description := attributeName || ' ' || approverDescription;
354: elsif(origSystem = ame_util.fndUserOrigSystem) then
347: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TOP_SUPERVISOR_COLON');
348: elsif(attributeName = ame_util.transactionRequestorAttribute) then
349: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
353: description := attributeName || ' ' || approverDescription;
354: elsif(origSystem = ame_util.fndUserOrigSystem) then
355: if(attributeName = ame_util.transactionReqUserAttribute) then
350: elsif(attributeName = ame_util.supStartingPointAttribute) then
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
353: description := attributeName || ' ' || approverDescription;
354: elsif(origSystem = ame_util.fndUserOrigSystem) then
355: if(attributeName = ame_util.transactionReqUserAttribute) then
356: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357: end if;
358: description := attributeName || ' ' || approverDescription;
351: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_SPRV_NON_DEF_START_POINT');
352: end if;
353: description := attributeName || ' ' || approverDescription;
354: elsif(origSystem = ame_util.fndUserOrigSystem) then
355: if(attributeName = ame_util.transactionReqUserAttribute) then
356: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357: end if;
358: description := attributeName || ' ' || approverDescription;
359: elsif(origSystem = ame_util.posOrigSystem) then
352: end if;
353: description := attributeName || ' ' || approverDescription;
354: elsif(origSystem = ame_util.fndUserOrigSystem) then
355: if(attributeName = ame_util.transactionReqUserAttribute) then
356: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357: end if;
358: description := attributeName || ' ' || approverDescription;
359: elsif(origSystem = ame_util.posOrigSystem) then
360: if(attributeName = ame_util.topPositionIdAttribute) then
355: if(attributeName = ame_util.transactionReqUserAttribute) then
356: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357: end if;
358: description := attributeName || ' ' || approverDescription;
359: elsif(origSystem = ame_util.posOrigSystem) then
360: if(attributeName = ame_util.topPositionIdAttribute) then
361: attributeName := 'Top position id: '; -- pa boilerplate
362: end if;
363: description := attributeName || ' ' || approverDescription;
356: attributeName := ame_util.getLabel(ame_util.perFndAppId,'AME_TRANSACTION_REQUESTOR');
357: end if;
358: description := attributeName || ' ' || approverDescription;
359: elsif(origSystem = ame_util.posOrigSystem) then
360: if(attributeName = ame_util.topPositionIdAttribute) then
361: attributeName := 'Top position id: '; -- pa boilerplate
362: end if;
363: description := attributeName || ' ' || approverDescription;
364: end if;
364: end if;
365: end if;
366: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372: calendar_aware => 1);
365: end if;
366: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372: calendar_aware => 1);
373: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
366: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372: calendar_aware => 1);
373: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
374: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372: calendar_aware => 1);
373: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
374: else
375: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
372: calendar_aware => 1);
373: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
374: else
375: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
376: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
377: end if;
378: else
379: if attributeType = ame_util.dateAttributeType then
375: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
376: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
377: end if;
378: else
379: if attributeType = ame_util.dateAttributeType then
380: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
381: calendar_aware => 1);
382: description := lineItemLabel||parameterOneDateString;
383: else
376: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
377: end if;
378: else
379: if attributeType = ame_util.dateAttributeType then
380: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
381: calendar_aware => 1);
382: description := lineItemLabel||parameterOneDateString;
383: else
384: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
380: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne),
381: calendar_aware => 1);
382: description := lineItemLabel||parameterOneDateString;
383: else
384: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
385: description := lineItemLabel||parameterOneNumberString;
386: end if;
387: if parameterOne is not null then
388: if(includeLowerLimit = ame_util.booleanTrue) then
384: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
385: description := lineItemLabel||parameterOneNumberString;
386: end if;
387: if parameterOne is not null then
388: if(includeLowerLimit = ame_util.booleanTrue) then
389: description := description || ' <= ';
390: else
391: description := description || ' < ';
392: end if;
392: end if;
393: end if;
394: description := description || attributeName;
395: if parameterTwo is not null then
396: if(includeUpperLimit = ame_util.booleanTrue) then
397: description := description || ' <= ';
398: else
399: description := description || ' < ';
400: end if;
397: description := description || ' <= ';
398: else
399: description := description || ' < ';
400: end if;
401: if attributeType = ame_util.dateAttributeType then
402: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo),
403: calendar_aware => 1);
404: description := description || parameterTwoDateString;
405: else
398: else
399: description := description || ' < ';
400: end if;
401: if attributeType = ame_util.dateAttributeType then
402: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo),
403: calendar_aware => 1);
404: description := description || parameterTwoDateString;
405: else
406: parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
402: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo),
403: calendar_aware => 1);
404: description := description || parameterTwoDateString;
405: else
406: parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
407: description := description || parameterTwoNumberString;
408: end if;
409: end if;
410: end if;
428: ame_conditions
429: where
430: condition_id = conditionIdIn and
431: sysdate between start_date and
432: nvl(end_date - ame_util.oneSecond, sysdate) ;
433: if(parameterOne = parameterTwo and
434: includeLowerLimit = ame_util.booleanTrue and
435: includeUpperLimit = ame_util.booleanTrue) then
436: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
430: condition_id = conditionIdIn and
431: sysdate between start_date and
432: nvl(end_date - ame_util.oneSecond, sysdate) ;
433: if(parameterOne = parameterTwo and
434: includeLowerLimit = ame_util.booleanTrue and
435: includeUpperLimit = ame_util.booleanTrue) then
436: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
437: else
438: description := lineItemLabel||parameterOne;
431: sysdate between start_date and
432: nvl(end_date - ame_util.oneSecond, sysdate) ;
433: if(parameterOne = parameterTwo and
434: includeLowerLimit = ame_util.booleanTrue and
435: includeUpperLimit = ame_util.booleanTrue) then
436: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
437: else
438: description := lineItemLabel||parameterOne;
439: if parameterOne is not null then
436: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
437: else
438: description := lineItemLabel||parameterOne;
439: if parameterOne is not null then
440: if(includeLowerLimit = ame_util.booleanTrue) then
441: description := description||' ' ||parameterThree|| ' <= ';
442: else
443: description := description||' ' ||parameterThree|| ' < ';
444: end if;
444: end if;
445: end if;
446: description := description || attributeName;
447: if parameterTwo is not null then
448: if(includeUpperLimit = ame_util.booleanTrue) then
449: description := description || ' <= ';
450: else
451: description := description || ' < ';
452: end if;
460: return(description);
461: exception
462: when others then
463: rollback;
464: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
465: routineNameIn => 'getDescription',
466: exceptionNumberIn => sqlcode,
467: exceptionStringIn => '(condition ID ' ||
468: conditionIdIn ||
479: from ame_conditions
480: where
481: condition_id = conditionIdIn and
482: sysdate between start_date and
483: nvl(end_date - ame_util.oneSecond, sysdate) ;
484: return(includeLowerLimit);
485: exception
486: when others then
487: rollback;
484: return(includeLowerLimit);
485: exception
486: when others then
487: rollback;
488: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
489: routineNameIn => 'getIncludeLowerLimit',
490: exceptionNumberIn => sqlcode,
491: exceptionStringIn => '(condition ID ' ||
492: conditionIdIn||
503: from ame_conditions
504: where
505: condition_id = conditionIdIn and
506: sysdate between start_date and
507: nvl(end_date - ame_util.oneSecond, sysdate) ;
508: return(includeUpperLimit);
509: exception
510: when others then
511: rollback;
508: return(includeUpperLimit);
509: exception
510: when others then
511: rollback;
512: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
513: routineNameIn => 'getIncludeUpperLimit',
514: exceptionNumberIn => sqlcode,
515: exceptionStringIn => '(condition ID ' ||
516: conditionIdIn||
530: where
531: ame_conditions.condition_id = conditionIdIn and
532: ame_attributes.attribute_id = ame_conditions.attribute_id and
533: sysdate between ame_conditions.start_date and
534: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
535: sysdate between ame_attributes.start_date and
536: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
537: if attributeType = ame_util.stringAttributeType then
538: return(true);
532: ame_attributes.attribute_id = ame_conditions.attribute_id and
533: sysdate between ame_conditions.start_date and
534: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
535: sysdate between ame_attributes.start_date and
536: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
537: if attributeType = ame_util.stringAttributeType then
538: return(true);
539: end if;
540: return(false);
533: sysdate between ame_conditions.start_date and
534: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
535: sysdate between ame_attributes.start_date and
536: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
537: if attributeType = ame_util.stringAttributeType then
538: return(true);
539: end if;
540: return(false);
541: exception
543: rollback;
544: return(false);
545: when others then
546: rollback;
547: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
548: routineNameIn => 'isStringAttributeType',
549: exceptionNumberIn => sqlcode,
550: exceptionStringIn => '(condition ID ' ||
551: conditionIdIn||
562: from ame_conditions
563: where
564: condition_id = conditionIdIn and
565: sysdate between start_date and
566: nvl(end_date - ame_util.oneSecond, sysdate) ;
567: return(parameterOne);
568: exception
569: when others then
570: rollback;
567: return(parameterOne);
568: exception
569: when others then
570: rollback;
571: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
572: routineNameIn => 'getParameterOne',
573: exceptionNumberIn => sqlcode,
574: exceptionStringIn => '(condition ID ' ||
575: conditionIdIn||
586: from ame_conditions
587: where
588: condition_id = conditionIdIn and
589: sysdate between start_date and
590: nvl(end_date - ame_util.oneSecond, sysdate) ;
591: return(parameterTwo);
592: exception
593: when others then
594: rollback;
591: return(parameterTwo);
592: exception
593: when others then
594: rollback;
595: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
596: routineNameIn => 'getParameterTwo',
597: exceptionNumberIn => sqlcode,
598: exceptionStringIn => '(condition ID ' ||
599: conditionIdIn||
610: from ame_conditions
611: where
612: condition_id = conditionIdIn and
613: sysdate between start_date and
614: nvl(end_date - ame_util.oneSecond, sysdate) ;
615: return(parameterThree);
616: exception
617: when others then
618: rollback;
615: return(parameterThree);
616: exception
617: when others then
618: rollback;
619: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
620: routineNameIn => 'getParameterThree',
621: exceptionNumberIn => sqlcode,
622: exceptionStringIn => '(condition ID ' ||
623: conditionIdIn||
634: from ame_conditions
635: where
636: condition_id = conditionIdIn and
637: sysdate between start_date and
638: nvl(end_date - ame_util.oneSecond, sysdate) ;
639: return(startDate);
640: exception
641: when others then
642: rollback;
639: return(startDate);
640: exception
641: when others then
642: rollback;
643: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
644: routineNameIn => 'getStartDate',
645: exceptionNumberIn => sqlcode,
646: exceptionStringIn => '(condition ID ' ||
647: conditionIdIn||
658: from ame_conditions
659: where
660: condition_id = conditionIdIn and
661: sysdate between start_date and
662: nvl(end_date - ame_util.oneSecond, sysdate) ;
663: return(conditionType);
664: exception
665: when others then
666: rollback;
663: return(conditionType);
664: exception
665: when others then
666: rollback;
667: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
668: routineNameIn => 'getType',
669: exceptionNumberIn => sqlcode,
670: exceptionStringIn => '(condition ID ' ||
671: conditionIdIn||
683: from ame_conditions
684: where
685: condition_id = conditionIdIn and
686: sysdate between start_date and
687: nvl(end_date - ame_util.oneSecond, sysdate) ;
688: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
689: return(stringStartDate);
690: exception
691: when others then
684: where
685: condition_id = conditionIdIn and
686: sysdate between start_date and
687: nvl(end_date - ame_util.oneSecond, sysdate) ;
688: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
689: return(stringStartDate);
690: exception
691: when others then
692: rollback;
689: return(stringStartDate);
690: exception
691: when others then
692: rollback;
693: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
694: routineNameIn => 'getVersionStartDate',
695: exceptionNumberIn => sqlcode,
696: exceptionStringIn => '(condition ID ' ||
697: conditionIdIn||
711: where
712: condition_id = conditionIdIn and
713: rule_id = ruleIdIn and
714: ((sysdate between start_date and
715: nvl(end_date - ame_util.oneSecond, sysdate)) or
716: (sysdate < start_date and
717: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
718: if(useCount > 0) then
719: return(true);
713: rule_id = ruleIdIn and
714: ((sysdate between start_date and
715: nvl(end_date - ame_util.oneSecond, sysdate)) or
716: (sysdate < start_date and
717: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
718: if(useCount > 0) then
719: return(true);
720: end if;
721: return(false);
721: return(false);
722: exception
723: when others then
724: rollback;
725: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
726: routineNameIn => 'isConditionUsage',
727: exceptionNumberIn => sqlcode,
728: exceptionStringIn => '(condition ID ' ||
729: conditionIdIn||
745: ame_rule_usages.rule_id = ame_condition_usages.rule_id and
746: ame_rule_usages.item_id <> applicationIdIn and
747: ame_condition_usages.condition_id = conditionIdIn and
748: ((sysdate between ame_rule_usages.start_date and
749: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
750: (sysdate < ame_rule_usages.start_date and
751: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
752: ame_rule_usages.start_date + ame_util.oneSecond))) and
753: ((sysdate between ame_condition_usages.start_date and
748: ((sysdate between ame_rule_usages.start_date and
749: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
750: (sysdate < ame_rule_usages.start_date and
751: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
752: ame_rule_usages.start_date + ame_util.oneSecond))) and
753: ((sysdate between ame_condition_usages.start_date and
754: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
755: (sysdate < ame_condition_usages.start_date and
756: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
750: (sysdate < ame_rule_usages.start_date and
751: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
752: ame_rule_usages.start_date + ame_util.oneSecond))) and
753: ((sysdate between ame_condition_usages.start_date and
754: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
755: (sysdate < ame_condition_usages.start_date and
756: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
757: ame_condition_usages.start_date + ame_util.oneSecond))) ;
758: if(useCount > 0) then
753: ((sysdate between ame_condition_usages.start_date and
754: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
755: (sysdate < ame_condition_usages.start_date and
756: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
757: ame_condition_usages.start_date + ame_util.oneSecond))) ;
758: if(useCount > 0) then
759: return(true);
760: end if;
761: return(false);
761: return(false);
762: exception
763: when others then
764: rollback;
765: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
766: routineNameIn => 'isInUseByOtherApps',
767: exceptionNumberIn => sqlcode,
768: exceptionStringIn => '(condition ID ' ||
769: conditionIdIn||
780: from ame_condition_usages
781: where
782: condition_id = conditionIdIn and
783: ((sysdate between start_date and
784: nvl(end_date - ame_util.oneSecond, sysdate)) or
785: (sysdate < start_date and
786: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
787: if(useCount > 0) then
788: return(true);
782: condition_id = conditionIdIn and
783: ((sysdate between start_date and
784: nvl(end_date - ame_util.oneSecond, sysdate)) or
785: (sysdate < start_date and
786: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
787: if(useCount > 0) then
788: return(true);
789: end if;
790: return(false);
790: return(false);
791: exception
792: when others then
793: rollback;
794: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
795: routineNameIn => 'isInUse',
796: exceptionNumberIn => sqlcode,
797: exceptionStringIn => '(condition ID ' ||
798: conditionIdIn||
813: ame_conditions
814: where
815: ame_attributes.attribute_id = ame_conditions.attribute_id and
816: ame_conditions.condition_type = conditionTypeIn and
817: ame_attributes.line_item = ame_util.booleanTrue and
818: ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
819: ame_attribute_usages.application_id = applicationIdIn and
820: ((sysdate between ame_attribute_usages.start_date and
821: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
817: ame_attributes.line_item = ame_util.booleanTrue and
818: ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
819: ame_attribute_usages.application_id = applicationIdIn and
820: ((sysdate between ame_attribute_usages.start_date and
821: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
822: (sysdate < ame_attribute_usages.start_date and
823: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
824: ame_attribute_usages.start_date + ame_util.oneSecond))) and
825: sysdate between ame_attributes.start_date and
820: ((sysdate between ame_attribute_usages.start_date and
821: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
822: (sysdate < ame_attribute_usages.start_date and
823: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
824: ame_attribute_usages.start_date + ame_util.oneSecond))) and
825: sysdate between ame_attributes.start_date and
826: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
827: sysdate between ame_conditions.start_date and
828: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
822: (sysdate < ame_attribute_usages.start_date and
823: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
824: ame_attribute_usages.start_date + ame_util.oneSecond))) and
825: sysdate between ame_attributes.start_date and
826: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
827: sysdate between ame_conditions.start_date and
828: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
829: if(lineItemCount > 0) then
830: return(true);
824: ame_attribute_usages.start_date + ame_util.oneSecond))) and
825: sysdate between ame_attributes.start_date and
826: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
827: sysdate between ame_conditions.start_date and
828: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
829: if(lineItemCount > 0) then
830: return(true);
831: end if;
832: return(false);
832: return(false);
833: exception
834: when others then
835: rollback;
836: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
837: routineNameIn => 'lineItemIsInUse',
838: exceptionNumberIn => sqlcode,
839: exceptionStringIn => sqlerrm);
840: raise;
848: parameterTwoIn in varchar2 default null,
849: parameterThreeIn in varchar2 default null,
850: includeLowerLimitIn in varchar2 default null,
851: includeUpperLimitIn in varchar2 default null,
852: stringValueListIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
853: newStartDateIn in date default null,
854: conditionIdIn in integer default null,
855: commitIn in boolean default true,
856: processingDateIn in date default null) return integer as
859: from ame_conditions
860: where
861: attribute_id = attributeIdIn and
862: sysdate between start_date and
863: nvl(end_date - ame_util.oneSecond, sysdate) ;
864: attributeType ame_attributes.attribute_type%type;
865: conditionCount integer;
866: conditionExistsException exception;
867: conditionId integer;
871: currencyNumberException1 exception;
872: currentUserId integer;
873: dateException exception;
874: errorCode integer;
875: errorMessage ame_util.longestStringType;
876: invalidConditionException exception;
877: parameterOneLengthException exception;
878: parameterTwoLengthException exception;
879: parameterThreeLengthException exception;
878: parameterTwoLengthException exception;
879: parameterThreeLengthException exception;
880: lowerlimitLengthException exception;
881: stringCount integer;
882: stringValueList ame_util.longestStringList;
883: tempCount integer;
884: tempCount2 integer;
885: tempIndex integer;
886: tempStringValueList ame_util.longestStringList;
882: stringValueList ame_util.longestStringList;
883: tempCount integer;
884: tempCount2 integer;
885: tempIndex integer;
886: tempStringValueList ame_util.longestStringList;
887: upperLimit integer;
888: upperlimitLengthException exception;
889: processingDate date;
890: begin
892: processingDate := sysdate;
893: else
894: processingDate := processingDateIn;
895: end if;
896: if(typeIn = ame_util.listModConditionType) then
897: attributeType := null;
898: elsif(attributeTypeIn is null) then
899: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
900: else
902: end if;
903: if(lengthb(conditionKeyIn) > 100) then
904: raise condKeyLengthException;
905: end if;
906: if(attributeType = ame_util.dateAttributeType) then
907: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
908: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909: raise dateException;
910: end if;
903: if(lengthb(conditionKeyIn) > 100) then
904: raise condKeyLengthException;
905: end if;
906: if(attributeType = ame_util.dateAttributeType) then
907: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
908: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909: raise dateException;
910: end if;
911: elsif(attributeType = ame_util.currencyAttributeType or
904: raise condKeyLengthException;
905: end if;
906: if(attributeType = ame_util.dateAttributeType) then
907: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
908: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909: raise dateException;
910: end if;
911: elsif(attributeType = ame_util.currencyAttributeType or
912: attributeType = ame_util.numberAttributeType) then
907: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
908: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909: raise dateException;
910: end if;
911: elsif(attributeType = ame_util.currencyAttributeType or
912: attributeType = ame_util.numberAttributeType) then
913: if(ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeIdIn)) is null then
914: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
915: raise currencyNumberException;
908: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
909: raise dateException;
910: end if;
911: elsif(attributeType = ame_util.currencyAttributeType or
912: attributeType = ame_util.numberAttributeType) then
913: if(ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeIdIn)) is null then
914: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
915: raise currencyNumberException;
916: end if;
914: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
915: raise currencyNumberException;
916: end if;
917: if(to_number(parameterOneIn) = to_number(parameterTwoIn) and
918: (includeLowerLimitIn = ame_util.booleanFalse or
919: includeUpperLimitIn = ame_util.booleanFalse)) then
920: raise currencyNumberException1;
921: end if;
922: end if;
915: raise currencyNumberException;
916: end if;
917: if(to_number(parameterOneIn) = to_number(parameterTwoIn) and
918: (includeLowerLimitIn = ame_util.booleanFalse or
919: includeUpperLimitIn = ame_util.booleanFalse)) then
920: raise currencyNumberException1;
921: end if;
922: end if;
923: end if;
920: raise currencyNumberException1;
921: end if;
922: end if;
923: end if;
924: if(attributeType = ame_util.stringAttributeType) then
925: stringValueList := stringValueListIn; /* necessary for in/out parameter below */
926: stringCount := stringValueList.count;
927: for i in 1 .. stringCount loop
928: if(instrb(stringValueList(i), ',') > 0) then
928: if(instrb(stringValueList(i), ',') > 0) then
929: raise invalidConditionException;
930: end if;
931: end loop;
932: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
933: for tempCondition in conditionCursor(attributeIdIn => attributeIdIn) loop
934: getStringValueList(conditionIdIn => tempCondition.condition_id,
935: stringValueListOut => tempStringValueList);
936: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
932: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
933: for tempCondition in conditionCursor(attributeIdIn => attributeIdIn) loop
934: getStringValueList(conditionIdIn => tempCondition.condition_id,
935: stringValueListOut => tempStringValueList);
936: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
937: longestStringList2InOut => tempStringValueList)) then
938: raise conditionExistsException;
939: end if;
940: end loop;
955: upper(include_lower_limit) = upper(includeLowerLimitIn)) and
956: ((include_upper_limit is null and includeUpperLimitIn is null) or
957: upper(include_upper_limit) = upper(includeUpperLimitIn)) and
958: processingDate between start_date and
959: nvl(end_date - ame_util.oneSecond, processingDate) ;
960: if tempCount > 0 then
961: raise conditionExistsException;
962: end if;
963: end if;
960: if tempCount > 0 then
961: raise conditionExistsException;
962: end if;
963: end if;
964: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
965: columnNameIn => 'parameter_one',
966: argumentIn => parameterOneIn)) then
967: raise parameterOneLengthException;
968: end if;
965: columnNameIn => 'parameter_one',
966: argumentIn => parameterOneIn)) then
967: raise parameterOneLengthException;
968: end if;
969: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
970: columnNameIn => 'parameter_two',
971: argumentIn => parameterTwoIn)) then
972: raise parameterTwoLengthException;
973: end if;
970: columnNameIn => 'parameter_two',
971: argumentIn => parameterTwoIn)) then
972: raise parameterTwoLengthException;
973: end if;
974: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
975: columnNameIn => 'parameter_three',
976: argumentIn => parameterThreeIn)) then
977: raise parameterThreeLengthException;
978: end if;
975: columnNameIn => 'parameter_three',
976: argumentIn => parameterThreeIn)) then
977: raise parameterThreeLengthException;
978: end if;
979: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
980: columnNameIn => 'include_lower_limit',
981: argumentIn => includeLowerLimitIn)) then
982: raise lowerlimitLengthException;
983: end if;
980: columnNameIn => 'include_lower_limit',
981: argumentIn => includeLowerLimitIn)) then
982: raise lowerlimitLengthException;
983: end if;
984: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
985: columnNameIn => 'include_upper_limit',
986: argumentIn => includeUpperLimitIn)) then
987: raise upperlimitLengthException;
988: end if;
991: including the new version, should. This is a failsafe way to check
992: whether previous versions of an already end-dated object had
993: created_by = 1.
994: */
995: currentUserId := ame_util.getCurrentUserId;
996: if(conditionIdIn is null) then
997: createdBy := currentUserId;
998: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
999: /* Use negative condition IDs for developer-seeded conditions. */
994: */
995: currentUserId := ame_util.getCurrentUserId;
996: if(conditionIdIn is null) then
997: createdBy := currentUserId;
998: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
999: /* Use negative condition IDs for developer-seeded conditions. */
1000: select count(*)
1001: into conditionCount
1002: from ame_conditions
1001: into conditionCount
1002: from ame_conditions
1003: where
1004: processingDate between start_date and
1005: nvl(end_date - ame_util.oneSecond, processingDate) ;
1006: if conditionCount = 0 then
1007: conditionId := -1;
1008: else
1009: select min(condition_id) - 1
1010: into conditionId
1011: from ame_conditions
1012: where
1013: processingDate between start_date and
1014: nvl(end_date - ame_util.oneSecond, processingDate);
1015: if(conditionId > -1) then
1016: conditionId := -1;
1017: end if;
1018: end if;
1025: into tempCount2
1026: from ame_conditions
1027: where
1028: condition_id = conditionId and
1029: created_by = ame_util.seededDataCreatedById;
1030: if(tempCount2 > 0) then
1031: createdBy := ame_util.seededDataCreatedById;
1032: else
1033: createdBy := currentUserId;
1027: where
1028: condition_id = conditionId and
1029: created_by = ame_util.seededDataCreatedById;
1030: if(tempCount2 > 0) then
1031: createdBy := ame_util.seededDataCreatedById;
1032: else
1033: createdBy := currentUserId;
1034: end if;
1035: end if;
1064: processingDate,
1065: currentUserId,
1066: nvl(newStartDateIn, processingDate),
1067: null);
1068: if(attributeType = ame_util.stringAttributeType) then
1069: upperLimit := stringValueList.count;
1070: for i in 1..upperLimit loop
1071: insert into ame_string_values(condition_id,
1072: string_value,
1095: exception
1096: when condKeyLengthException then
1097: rollback;
1098: errorCode := -20001;
1099: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1100: messageNameIn => 'AME_400362_COND_KEY_LONG',
1101: tokenNameOneIn => 'COLUMN_LENGTH',
1102: tokenValueOneIn => 100);
1103: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
1099: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1100: messageNameIn => 'AME_400362_COND_KEY_LONG',
1101: tokenNameOneIn => 'COLUMN_LENGTH',
1102: tokenValueOneIn => 100);
1103: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
1104: routineNamein => 'new',
1105: exceptionNumberIn => errorCode,
1106: exceptionStringIn => errorMessage);
1107: raise_application_error(errorCode,
1110: when invalidConditionException then
1111: rollback;
1112: errorCode := -20001;
1113: errorMessage :=
1114: ame_util.getMessage(applicationShortNameIn => 'PER',
1115: messageNameIn => 'AME_400182_CON_STR_VAL_COMMA');
1116: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1117: routineNameIn => 'new',
1118: exceptionNumberIn => errorCode,
1112: errorCode := -20001;
1113: errorMessage :=
1114: ame_util.getMessage(applicationShortNameIn => 'PER',
1115: messageNameIn => 'AME_400182_CON_STR_VAL_COMMA');
1116: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1117: routineNameIn => 'new',
1118: exceptionNumberIn => errorCode,
1119: exceptionStringIn => errorMessage);
1120: raise_application_error(errorCode,
1123: when conditionExistsException then
1124: rollback;
1125: errorCode := -20001;
1126: errorMessage :=
1127: ame_util.getMessage(applicationShortNameIn => 'PER',
1128: messageNameIn => 'AME_400183_CON_ALRDY_EXISTS');
1129: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1130: routineNameIn => 'new',
1131: exceptionNumberIn => errorCode,
1125: errorCode := -20001;
1126: errorMessage :=
1127: ame_util.getMessage(applicationShortNameIn => 'PER',
1128: messageNameIn => 'AME_400183_CON_ALRDY_EXISTS');
1129: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1130: routineNameIn => 'new',
1131: exceptionNumberIn => errorCode,
1132: exceptionStringIn => errorMessage);
1133: raise_application_error(errorCode,
1135: return(null);
1136: when parameterOneLengthException then
1137: rollback;
1138: errorCode := -20001;
1139: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1140: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1141: tokenNameOneIn => 'COLUMN_LENGTH',
1142: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1143: columnNameIn => 'parameter_one'));
1138: errorCode := -20001;
1139: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1140: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1141: tokenNameOneIn => 'COLUMN_LENGTH',
1142: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1143: columnNameIn => 'parameter_one'));
1144: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1145: routineNameIn => 'new',
1146: exceptionNumberIn => errorCode,
1140: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1141: tokenNameOneIn => 'COLUMN_LENGTH',
1142: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1143: columnNameIn => 'parameter_one'));
1144: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1145: routineNameIn => 'new',
1146: exceptionNumberIn => errorCode,
1147: exceptionStringIn => errorMessage);
1148: raise_application_error(errorCode,
1151: when parameterTwoLengthException then
1152: rollback;
1153: errorCode := -20001;
1154: errorMessage :=
1155: ame_util.getMessage(applicationShortNameIn => 'PER',
1156: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1157: tokenNameOneIn => 'COLUMN_LENGTH',
1158: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1159: columnNameIn => 'parameter_two'));
1154: errorMessage :=
1155: ame_util.getMessage(applicationShortNameIn => 'PER',
1156: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1157: tokenNameOneIn => 'COLUMN_LENGTH',
1158: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1159: columnNameIn => 'parameter_two'));
1160: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1161: routineNameIn => 'new',
1162: exceptionNumberIn => errorCode,
1156: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1157: tokenNameOneIn => 'COLUMN_LENGTH',
1158: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1159: columnNameIn => 'parameter_two'));
1160: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1161: routineNameIn => 'new',
1162: exceptionNumberIn => errorCode,
1163: exceptionStringIn => errorMessage);
1164: raise_application_error(errorCode,
1167: when parameterThreeLengthException then
1168: rollback;
1169: errorCode := -20001;
1170: errorMessage :=
1171: ame_util.getMessage(applicationShortNameIn => 'PER',
1172: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1173: tokenNameOneIn => 'COLUMN_LENGTH',
1174: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1175: columnNameIn => 'parameter_three'));
1170: errorMessage :=
1171: ame_util.getMessage(applicationShortNameIn => 'PER',
1172: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1173: tokenNameOneIn => 'COLUMN_LENGTH',
1174: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1175: columnNameIn => 'parameter_three'));
1176: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1177: routineNameIn => 'new',
1178: exceptionNumberIn => errorCode,
1172: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1173: tokenNameOneIn => 'COLUMN_LENGTH',
1174: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1175: columnNameIn => 'parameter_three'));
1176: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1177: routineNameIn => 'new',
1178: exceptionNumberIn => errorCode,
1179: exceptionStringIn => errorMessage);
1180: raise_application_error(errorCode,
1183: when lowerlimitLengthException then
1184: rollback;
1185: errorCode := -20001;
1186: errorMessage :=
1187: ame_util.getMessage(applicationShortNameIn => 'PER',
1188: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1189: tokenNameOneIn => 'COLUMN_LENGTH',
1190: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1191: columnNameIn => 'include_lower_limit'));
1186: errorMessage :=
1187: ame_util.getMessage(applicationShortNameIn => 'PER',
1188: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1189: tokenNameOneIn => 'COLUMN_LENGTH',
1190: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1191: columnNameIn => 'include_lower_limit'));
1192: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1193: routineNameIn => 'new',
1194: exceptionNumberIn => errorCode,
1188: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1189: tokenNameOneIn => 'COLUMN_LENGTH',
1190: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1191: columnNameIn => 'include_lower_limit'));
1192: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1193: routineNameIn => 'new',
1194: exceptionNumberIn => errorCode,
1195: exceptionStringIn => errorMessage);
1196: raise_application_error(errorCode,
1199: when upperlimitLengthException then
1200: rollback;
1201: errorCode := -20001;
1202: errorMessage :=
1203: ame_util.getMessage(applicationShortNameIn => 'PER',
1204: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1205: tokenNameOneIn => 'COLUMN_LENGTH',
1206: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1207: columnNameIn => 'include_upper_limit'));
1202: errorMessage :=
1203: ame_util.getMessage(applicationShortNameIn => 'PER',
1204: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1205: tokenNameOneIn => 'COLUMN_LENGTH',
1206: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1207: columnNameIn => 'include_upper_limit'));
1208: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1209: routineNameIn => 'new',
1210: exceptionNumberIn => errorCode,
1204: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1205: tokenNameOneIn => 'COLUMN_LENGTH',
1206: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1207: columnNameIn => 'include_upper_limit'));
1208: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1209: routineNameIn => 'new',
1210: exceptionNumberIn => errorCode,
1211: exceptionStringIn => errorMessage);
1212: raise_application_error(errorCode,
1215: when dateException then
1216: rollback;
1217: errorCode := -20001;
1218: errorMessage :=
1219: ame_util.getMessage(applicationShortNameIn => 'PER',
1220: messageNameIn => 'AME_400186_CON_START_LESS_END');
1221: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1222: routineNameIn => 'new',
1223: exceptionNumberIn => errorCode,
1217: errorCode := -20001;
1218: errorMessage :=
1219: ame_util.getMessage(applicationShortNameIn => 'PER',
1220: messageNameIn => 'AME_400186_CON_START_LESS_END');
1221: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1222: routineNameIn => 'new',
1223: exceptionNumberIn => errorCode,
1224: exceptionStringIn => errorMessage);
1225: raise_application_error(errorCode,
1228: when currencyNumberException then
1229: rollback;
1230: errorCode := -20001;
1231: errorMessage :=
1232: ame_util.getMessage(applicationShortNameIn => 'PER',
1233: messageNameIn => 'AME_400187_CON_LWR_LESS_UPP');
1234: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1235: routineNameIn => 'new',
1236: exceptionNumberIn => errorCode,
1230: errorCode := -20001;
1231: errorMessage :=
1232: ame_util.getMessage(applicationShortNameIn => 'PER',
1233: messageNameIn => 'AME_400187_CON_LWR_LESS_UPP');
1234: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1235: routineNameIn => 'new',
1236: exceptionNumberIn => errorCode,
1237: exceptionStringIn => errorMessage);
1238: raise_application_error(errorCode,
1241: when currencyNumberException1 then
1242: rollback;
1243: errorCode := -20001;
1244: errorMessage :=
1245: ame_util.getMessage(applicationShortNameIn => 'PER',
1246: messageNameIn => 'AME_400188_CON_LMT_VAL_YES');
1247: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1248: routineNameIn => 'new',
1249: exceptionNumberIn => errorCode,
1243: errorCode := -20001;
1244: errorMessage :=
1245: ame_util.getMessage(applicationShortNameIn => 'PER',
1246: messageNameIn => 'AME_400188_CON_LMT_VAL_YES');
1247: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1248: routineNameIn => 'new',
1249: exceptionNumberIn => errorCode,
1250: exceptionStringIn => errorMessage);
1251: raise_application_error(errorCode,
1252: errorMessage);
1253: return(null);
1254: when others then
1255: rollback;
1256: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1257: routineNameIn => 'new',
1258: exceptionNumberIn => sqlcode,
1259: exceptionStringIn => '(condition ID ' ||
1260: conditionIdIn||
1268: processingDateIn in date default null) return boolean as
1269: createdBy integer;
1270: currentUserId integer;
1271: errorCode integer;
1272: errorMessage ame_util.longestStringType;
1273: processingDate date;
1274: tempCount integer;
1275: usageExistsException exception;
1276: useCount integer;
1286: where
1287: condition_id = conditionIdIn and
1288: rule_id = ruleIdIn and
1289: ((sysdate between start_date and
1290: nvl(end_date - ame_util.oneSecond, sysdate)) or
1291: (sysdate < start_date and
1292: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1293: if(useCount > 0) then
1294: raise usageExistsException;
1288: rule_id = ruleIdIn and
1289: ((sysdate between start_date and
1290: nvl(end_date - ame_util.oneSecond, sysdate)) or
1291: (sysdate < start_date and
1292: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1293: if(useCount > 0) then
1294: raise usageExistsException;
1295: end if;
1296: currentUserId := ame_util.getCurrentUserId;
1292: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1293: if(useCount > 0) then
1294: raise usageExistsException;
1295: end if;
1296: currentUserId := ame_util.getCurrentUserId;
1297: select count(*)
1298: into tempCount
1299: from ame_condition_usages
1300: where
1299: from ame_condition_usages
1300: where
1301: condition_id = conditionIdIn and
1302: rule_id = ruleIdIn and
1303: created_by = ame_util.seededDataCreatedById;
1304: if(tempCount > 0) then
1305: createdBy := ame_util.seededDataCreatedById;
1306: else
1307: createdBy := currentUserId;
1301: condition_id = conditionIdIn and
1302: rule_id = ruleIdIn and
1303: created_by = ame_util.seededDataCreatedById;
1304: if(tempCount > 0) then
1305: createdBy := ame_util.seededDataCreatedById;
1306: else
1307: createdBy := currentUserId;
1308: end if;
1309: insert into ame_condition_usages(rule_id,
1330: when usageExistsException then
1331: rollback;
1332: errorCode := -20001;
1333: errorMessage :=
1334: ame_util.getMessage(applicationShortNameIn => 'PER',
1335: messageNameIn => 'AME_400189_CON_RULE_USES');
1336: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1337: routineNameIn => 'newConditionUsage',
1338: exceptionNumberIn => errorCode,
1332: errorCode := -20001;
1333: errorMessage :=
1334: ame_util.getMessage(applicationShortNameIn => 'PER',
1335: messageNameIn => 'AME_400189_CON_RULE_USES');
1336: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1337: routineNameIn => 'newConditionUsage',
1338: exceptionNumberIn => errorCode,
1339: exceptionStringIn => errorMessage);
1340: raise_application_error(errorCode,
1341: errorMessage);
1342: return(false);
1343: when others then
1344: rollback;
1345: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1346: routineNameIn => 'newConditionUsage',
1347: exceptionNumberIn => sqlcode,
1348: exceptionStringIn => '(condition ID ' ||
1349: conditionIdIn||
1356: valueIn in varchar2,
1357: processingDateIn in date default null) return boolean as
1358: currentUserId integer;
1359: errorCode integer;
1360: errorMessage ame_util.longestStringType;
1361: stringValueLengthException exception;
1362: useCount integer;
1363: processingDate date;
1364: begin
1374: condition_id = conditionIdIn and
1375: /* string values are case sensitive */
1376: string_value = valueIn and
1377: sysdate between start_date and
1378: nvl(end_date - ame_util.oneSecond, sysdate) ;
1379: if(useCount > 0) then
1380: return(false);
1381: end if;
1382: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_string_values',
1378: nvl(end_date - ame_util.oneSecond, sysdate) ;
1379: if(useCount > 0) then
1380: return(false);
1381: end if;
1382: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_string_values',
1383: columnNameIn => 'string_value',
1384: argumentIn => valueIn)) then
1385: raise stringValueLengthException;
1386: end if;
1383: columnNameIn => 'string_value',
1384: argumentIn => valueIn)) then
1385: raise stringValueLengthException;
1386: end if;
1387: currentUserId := ame_util.getCurrentUserId;
1388: insert into ame_string_values(condition_id,
1389: string_value,
1390: created_by,
1391: creation_date,
1407: exception
1408: when stringValueLengthException then
1409: rollback;
1410: errorCode := -20001;
1411: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1412: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1413: tokenNameOneIn => 'COLUMN_LENGTH',
1414: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1415: columnNameIn => 'string_value'));
1410: errorCode := -20001;
1411: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1412: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1413: tokenNameOneIn => 'COLUMN_LENGTH',
1414: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1415: columnNameIn => 'string_value'));
1416: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1417: routineNameIn => 'newStringValue',
1418: exceptionNumberIn => errorCode,
1412: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1413: tokenNameOneIn => 'COLUMN_LENGTH',
1414: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1415: columnNameIn => 'string_value'));
1416: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1417: routineNameIn => 'newStringValue',
1418: exceptionNumberIn => errorCode,
1419: exceptionStringIn => errorMessage);
1420: raise_application_error(errorCode,
1421: errorMessage);
1422: return(false);
1423: when others then
1424: rollback;
1425: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1426: routineNameIn => 'newStringValue',
1427: exceptionNumberIn => sqlcode,
1428: exceptionStringIn => '(condition ID ' ||
1429: conditionIdIn||
1432: raise;
1433: return(false);
1434: end newStringValue;
1435: procedure change(conditionIdIn in integer,
1436: stringValuesIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
1437: typeIn in varchar2 default null,
1438: attributeIdIn in integer default null,
1439: parameterOneIn in varchar2 default null,
1440: parameterTwoIn in varchar2 default null,
1465: (include_lower_limit = includeLowerLimitIn)) and
1466: ((include_upper_limit is null and includeUpperLimitIn is null) or
1467: (include_upper_limit = includeUpperLimitIn)) and
1468: sysdate between start_date and
1469: nvl(end_date - ame_util.oneSecond, sysdate) ;
1470: cursor startDateCursor is
1471: select start_date
1472: from ame_conditions
1473: where
1472: from ame_conditions
1473: where
1474: condition_id = conditionIdIn and
1475: sysdate between start_date and
1476: nvl(end_date - ame_util.oneSecond, sysdate)
1477: for update;
1478: attributeId integer;
1479: attributeType ame_attributes.attribute_type%type;
1480: conditionId integer;
1483: conditionType ame_conditions.condition_type%type;
1484: currentUserId integer;
1485: endDate date;
1486: errorCode integer;
1487: errorMessage ame_util.longestStringType;
1488: newStartDate date;
1489: objectVersionNoDataException exception;
1490: startDate date;
1491: stringValueList ame_util.longestStringList;
1487: errorMessage ame_util.longestStringType;
1488: newStartDate date;
1489: objectVersionNoDataException exception;
1490: startDate date;
1491: stringValueList ame_util.longestStringList;
1492: tempCount integer;
1493: tempIndex integer;
1494: tempStringValue ame_string_values.string_value%type;
1495: tempStringValueList ame_util.longestStringList;
1491: stringValueList ame_util.longestStringList;
1492: tempCount integer;
1493: tempIndex integer;
1494: tempStringValue ame_string_values.string_value%type;
1495: tempStringValueList ame_util.longestStringList;
1496: processingDate date;
1497: begin
1498: if processingDateIn is null then
1499: processingDate := sysdate;
1527: (include_lower_limit = includeLowerLimitIn)) and
1528: ((include_upper_limit is null and includeUpperLimitIn is null) or
1529: (include_upper_limit = includeUpperLimitIn)) and
1530: sysdate between start_date and
1531: nvl(end_date - ame_util.oneSecond, sysdate) ;
1532: if(tempCount > 0) then
1533: if(conditionType = ame_util.listModConditionType) then
1534: raise conditionsExistsException;
1535: else
1529: (include_upper_limit = includeUpperLimitIn)) and
1530: sysdate between start_date and
1531: nvl(end_date - ame_util.oneSecond, sysdate) ;
1532: if(tempCount > 0) then
1533: if(conditionType = ame_util.listModConditionType) then
1534: raise conditionsExistsException;
1535: else
1536: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1537: if(attributeType = ame_util.stringAttributeType) then
1533: if(conditionType = ame_util.listModConditionType) then
1534: raise conditionsExistsException;
1535: else
1536: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1537: if(attributeType = ame_util.stringAttributeType) then
1538: stringValueList := stringValuesIn;
1539: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
1540: for tempCondition in conditionCursor(typeIn => typeIn,
1541: attributeIdIn => attributeIdIn,
1535: else
1536: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1537: if(attributeType = ame_util.stringAttributeType) then
1538: stringValueList := stringValuesIn;
1539: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
1540: for tempCondition in conditionCursor(typeIn => typeIn,
1541: attributeIdIn => attributeIdIn,
1542: parameterOneIn => parameterOneIn,
1543: parameterTwoIn => parameterTwoIn,
1545: includeLowerLimitIn => includeLowerLimitIn,
1546: includeUpperLimitIn => includeUpperLimitIn) loop
1547: getStringValueList(conditionIdIn => tempCondition.condition_id,
1548: stringValueListOut => tempStringValueList);
1549: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
1550: longestStringList2InOut => tempStringValueList)) then
1551: raise conditionsExistsException;
1552: end if;
1553: end loop;
1560: attributeId := getAttributeId(conditionIdIn => conditionIdIn);
1561: else
1562: attributeId := attributeIdIn;
1563: end if;
1564: currentUserId := ame_util.getCurrentUserId;
1565: if versionStartDateIn = startDate then
1566: endDate := processingDate ;
1567: newStartDate := processingDate;
1568: update ame_conditions
1573: end_date = endDate
1574: where
1575: condition_id = conditionIdIn and
1576: sysdate between start_date and
1577: nvl(end_date - ame_util.oneSecond, sysdate) ;
1578: if conditionType <> ame_util.listModConditionType then
1579: if(attributeType = ame_util.stringAttributeType) then
1580: delete from ame_string_values where condition_id = conditionIdIn;
1581: end if;
1574: where
1575: condition_id = conditionIdIn and
1576: sysdate between start_date and
1577: nvl(end_date - ame_util.oneSecond, sysdate) ;
1578: if conditionType <> ame_util.listModConditionType then
1579: if(attributeType = ame_util.stringAttributeType) then
1580: delete from ame_string_values where condition_id = conditionIdIn;
1581: end if;
1582: end if;
1575: condition_id = conditionIdIn and
1576: sysdate between start_date and
1577: nvl(end_date - ame_util.oneSecond, sysdate) ;
1578: if conditionType <> ame_util.listModConditionType then
1579: if(attributeType = ame_util.stringAttributeType) then
1580: delete from ame_string_values where condition_id = conditionIdIn;
1581: end if;
1582: end if;
1583: /* (The new function does a commit.) */
1594: newStartDateIn => newStartDate,
1595: conditionIdIn => conditionIdIn);
1596: else
1597: close startDateCursor;
1598: raise ame_util.objectVersionException;
1599: end if;
1600: close startDateCursor;
1601: exception
1602: when ame_util.objectVersionException then
1598: raise ame_util.objectVersionException;
1599: end if;
1600: close startDateCursor;
1601: exception
1602: when ame_util.objectVersionException then
1603: rollback;
1604: if(startDateCursor%isOpen) then
1605: close startDateCursor;
1606: end if;
1605: close startDateCursor;
1606: end if;
1607: errorCode := -20001;
1608: errorMessage :=
1609: ame_util.getMessage(applicationShortNameIn => 'PER',
1610: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1611: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1612: routineNameIn => 'change',
1613: exceptionNumberIn => errorCode,
1607: errorCode := -20001;
1608: errorMessage :=
1609: ame_util.getMessage(applicationShortNameIn => 'PER',
1610: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1611: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1612: routineNameIn => 'change',
1613: exceptionNumberIn => errorCode,
1614: exceptionStringIn => errorMessage);
1615: raise_application_error(errorCode,
1620: close startDateCursor;
1621: end if;
1622: errorCode := -20001;
1623: errorMessage :=
1624: ame_util.getMessage(applicationShortNameIn => 'PER',
1625: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1626: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1627: routineNameIn => 'change',
1628: exceptionNumberIn => errorCode,
1622: errorCode := -20001;
1623: errorMessage :=
1624: ame_util.getMessage(applicationShortNameIn => 'PER',
1625: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1626: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1627: routineNameIn => 'change',
1628: exceptionNumberIn => errorCode,
1629: exceptionStringIn => errorMessage);
1630: raise_application_error(errorCode,
1632: when conditionsExistsException then
1633: rollback;
1634: errorCode := -20001;
1635: errorMessage :=
1636: ame_util.getMessage(applicationShortNameIn => 'PER',
1637: messageNameIn => 'AME_400191_CON_EXISTS_NEW');
1638: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1639: routineNameIn => 'change',
1640: exceptionNumberIn => errorCode,
1634: errorCode := -20001;
1635: errorMessage :=
1636: ame_util.getMessage(applicationShortNameIn => 'PER',
1637: messageNameIn => 'AME_400191_CON_EXISTS_NEW');
1638: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1639: routineNameIn => 'change',
1640: exceptionNumberIn => errorCode,
1641: exceptionStringIn => errorMessage);
1642: raise_application_error(errorCode,
1645: rollback;
1646: if(startDateCursor%isOpen) then
1647: close startDateCursor;
1648: end if;
1649: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1650: routineNameIn => 'change',
1651: exceptionNumberIn => sqlcode,
1652: exceptionStringIn => '(condition ID ' ||
1653: conditionIdIn||
1686: from ame_conditions
1687: where
1688: condition_id = conditionIdIn and
1689: sysdate between start_date and
1690: nvl(end_date - ame_util.oneSecond, sysdate) ;
1691: exception
1692: when others then
1693: rollback;
1694: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1690: nvl(end_date - ame_util.oneSecond, sysdate) ;
1691: exception
1692: when others then
1693: rollback;
1694: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1695: routineNameIn => 'getAllProperties',
1696: exceptionNumberIn => sqlcode,
1697: exceptionStringIn => '(condition ID ' ||
1698: conditionIdIn||
1709: raise;
1710: end getAllProperties;
1711: procedure getAuthPreConditions(applicationIdIn in integer,
1712: itemClassIdIn in integer,
1713: conditionIdsOut out nocopy ame_util.stringList,
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1710: end getAllProperties;
1711: procedure getAuthPreConditions(applicationIdIn in integer,
1712: itemClassIdIn in integer,
1713: conditionIdsOut out nocopy ame_util.stringList,
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1718: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1711: procedure getAuthPreConditions(applicationIdIn in integer,
1712: itemClassIdIn in integer,
1713: conditionIdsOut out nocopy ame_util.stringList,
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1718: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1719: cursor conditionCursor(applicationIdIn in integer,
1712: itemClassIdIn in integer,
1713: conditionIdsOut out nocopy ame_util.stringList,
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1718: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1719: cursor conditionCursor(applicationIdIn in integer,
1720: itemClassIdIn in integer) is
1713: conditionIdsOut out nocopy ame_util.stringList,
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1718: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1719: cursor conditionCursor(applicationIdIn in integer,
1720: itemClassIdIn in integer) is
1721: select
1714: conditionTypesOut out nocopy ame_util.stringList,
1715: attributeIdsOut out nocopy ame_util.stringList,
1716: attributeNamesOut out nocopy ame_util.stringList,
1717: attributeTypesOut out nocopy ame_util.stringList,
1718: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1719: cursor conditionCursor(applicationIdIn in integer,
1720: itemClassIdIn in integer) is
1721: select
1722: ame_conditions.condition_id id,
1734: ame_item_class_usages.item_class_id = itemClassIdIn and
1735: ame_conditions.attribute_id = ame_attribute_usages.attribute_id and
1736: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1737: ame_attributes.item_class_id = itemClassIdIn and
1738: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1739: sysdate between ame_conditions.start_date and
1740: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1741: sysdate between ame_attributes.start_date and
1742: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1736: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1737: ame_attributes.item_class_id = itemClassIdIn and
1738: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1739: sysdate between ame_conditions.start_date and
1740: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1741: sysdate between ame_attributes.start_date and
1742: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1743: sysdate between ame_attribute_usages.start_date and
1744: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1738: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1739: sysdate between ame_conditions.start_date and
1740: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1741: sysdate between ame_attributes.start_date and
1742: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1743: sysdate between ame_attribute_usages.start_date and
1744: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1745: sysdate between ame_item_class_usages.start_date and
1746: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1740: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1741: sysdate between ame_attributes.start_date and
1742: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1743: sysdate between ame_attribute_usages.start_date and
1744: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1745: sysdate between ame_item_class_usages.start_date and
1746: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1747: order by ame_conditions.condition_type,
1748: ame_attributes.attribute_type,
1742: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1743: sysdate between ame_attribute_usages.start_date and
1744: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1745: sysdate between ame_item_class_usages.start_date and
1746: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1747: order by ame_conditions.condition_type,
1748: ame_attributes.attribute_type,
1749: ame_attributes.name;
1750: tempOutputIndex integer;
1778: when others then
1779: if conditionCursor%isopen then
1780: close conditionCursor;
1781: end if;
1782: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1783: routineNameIn => 'getAuthPreConditions',
1784: exceptionNumberIn => sqlcode,
1785: exceptionStringIn => '(application ID ' ||
1786: applicationIdIn||
1785: exceptionStringIn => '(application ID ' ||
1786: applicationIdIn||
1787: ') ' ||
1788: sqlerrm);
1789: conditionIdsOut := ame_util.emptyStringList;
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1786: applicationIdIn||
1787: ') ' ||
1788: sqlerrm);
1789: conditionIdsOut := ame_util.emptyStringList;
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1787: ') ' ||
1788: sqlerrm);
1789: conditionIdsOut := ame_util.emptyStringList;
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1795: raise;
1788: sqlerrm);
1789: conditionIdsOut := ame_util.emptyStringList;
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1795: raise;
1796: end getAuthPreConditions;
1789: conditionIdsOut := ame_util.emptyStringList;
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1795: raise;
1796: end getAuthPreConditions;
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1790: conditionTypesOut := ame_util.emptyStringList;
1791: attributeIdsOut := ame_util.emptyStringList;
1792: attributeNamesOut := ame_util.emptyStringList;
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1795: raise;
1796: end getAuthPreConditions;
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798: conditionTypeIn in varchar2,
1793: attributeTypesOut := ame_util.emptyStringList;
1794: conditionDescriptionsOut := ame_util.emptyLongStringList;
1795: raise;
1796: end getAuthPreConditions;
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798: conditionTypeIn in varchar2,
1799: lineItemIn in varchar2 default ame_util.booleanFalse,
1800: conditionIdsOut out nocopy ame_util.stringList,
1801: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1795: raise;
1796: end getAuthPreConditions;
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798: conditionTypeIn in varchar2,
1799: lineItemIn in varchar2 default ame_util.booleanFalse,
1800: conditionIdsOut out nocopy ame_util.stringList,
1801: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1802: cursor conditionCursor(attributeIdIn in integer,
1803: conditionTypeIn in varchar2,
1796: end getAuthPreConditions;
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798: conditionTypeIn in varchar2,
1799: lineItemIn in varchar2 default ame_util.booleanFalse,
1800: conditionIdsOut out nocopy ame_util.stringList,
1801: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1802: cursor conditionCursor(attributeIdIn in integer,
1803: conditionTypeIn in varchar2,
1804: lineItemIn in varchar2) is
1797: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1798: conditionTypeIn in varchar2,
1799: lineItemIn in varchar2 default ame_util.booleanFalse,
1800: conditionIdsOut out nocopy ame_util.stringList,
1801: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1802: cursor conditionCursor(attributeIdIn in integer,
1803: conditionTypeIn in varchar2,
1804: lineItemIn in varchar2) is
1805: select
1808: from ame_conditions,
1809: ame_attributes
1810: where
1811: ame_conditions.attribute_id = ame_attributes.attribute_id and
1812: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1813: ame_attributes.attribute_id = attributeIdIn and
1814: condition_type = conditionTypeIn and
1815: sysdate between ame_conditions.start_date and
1816: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1812: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1813: ame_attributes.attribute_id = attributeIdIn and
1814: condition_type = conditionTypeIn and
1815: sysdate between ame_conditions.start_date and
1816: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1817: sysdate between ame_attributes.start_date and
1818: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1819: order by description;
1820: tempOutputIndex integer;
1814: condition_type = conditionTypeIn and
1815: sysdate between ame_conditions.start_date and
1816: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1817: sysdate between ame_attributes.start_date and
1818: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1819: order by description;
1820: tempOutputIndex integer;
1821: upperLimit integer;
1822: begin
1834: end loop;
1835: exception
1836: when others then
1837: rollback;
1838: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1839: routineNameIn => 'getAttributesConditions',
1840: exceptionNumberIn => sqlcode,
1841: exceptionStringIn => sqlerrm);
1842: conditionIdsOut := ame_util.emptyStringList;
1838: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1839: routineNameIn => 'getAttributesConditions',
1840: exceptionNumberIn => sqlcode,
1841: exceptionStringIn => sqlerrm);
1842: conditionIdsOut := ame_util.emptyStringList;
1843: conditionDescriptionsOut := ame_util.emptyLongStringList;
1844: raise;
1845: end getAttributesConditions;
1846: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1839: routineNameIn => 'getAttributesConditions',
1840: exceptionNumberIn => sqlcode,
1841: exceptionStringIn => sqlerrm);
1842: conditionIdsOut := ame_util.emptyStringList;
1843: conditionDescriptionsOut := ame_util.emptyLongStringList;
1844: raise;
1845: end getAttributesConditions;
1846: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1847: conditionTypeIn in varchar2,
1842: conditionIdsOut := ame_util.emptyStringList;
1843: conditionDescriptionsOut := ame_util.emptyLongStringList;
1844: raise;
1845: end getAttributesConditions;
1846: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1847: conditionTypeIn in varchar2,
1848: itemClassIdIn in integer,
1849: ruleIdIn in integer,
1850: conditionIdsOut out nocopy ame_util.stringList,
1846: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1847: conditionTypeIn in varchar2,
1848: itemClassIdIn in integer,
1849: ruleIdIn in integer,
1850: conditionIdsOut out nocopy ame_util.stringList,
1851: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1852: cursor conditionCursor(attributeIdIn in integer,
1853: conditionTypeIn in varchar2,
1854: itemClassIdIn in integer) is
1847: conditionTypeIn in varchar2,
1848: itemClassIdIn in integer,
1849: ruleIdIn in integer,
1850: conditionIdsOut out nocopy ame_util.stringList,
1851: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1852: cursor conditionCursor(attributeIdIn in integer,
1853: conditionTypeIn in varchar2,
1854: itemClassIdIn in integer) is
1855: select
1862: ame_attributes.attribute_id = attributeIdIn and
1863: condition_type = conditionTypeIn and
1864: ame_attributes.item_class_id = itemClassIdIn and
1865: sysdate between ame_conditions.start_date and
1866: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1867: sysdate between ame_attributes.start_date and
1868: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1869: order by description;
1870: tempOutputIndex integer;
1864: ame_attributes.item_class_id = itemClassIdIn and
1865: sysdate between ame_conditions.start_date and
1866: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1867: sysdate between ame_attributes.start_date and
1868: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1869: order by description;
1870: tempOutputIndex integer;
1871: upperLimit integer;
1872: begin
1887: end loop;
1888: exception
1889: when others then
1890: rollback;
1891: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1892: routineNameIn => 'getAttributesConditions1',
1893: exceptionNumberIn => sqlcode,
1894: exceptionStringIn => sqlerrm);
1895: conditionIdsOut := ame_util.emptyStringList;
1891: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1892: routineNameIn => 'getAttributesConditions1',
1893: exceptionNumberIn => sqlcode,
1894: exceptionStringIn => sqlerrm);
1895: conditionIdsOut := ame_util.emptyStringList;
1896: conditionDescriptionsOut := ame_util.emptyLongStringList;
1897: raise;
1898: end getAttributesConditions1;
1899: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1892: routineNameIn => 'getAttributesConditions1',
1893: exceptionNumberIn => sqlcode,
1894: exceptionStringIn => sqlerrm);
1895: conditionIdsOut := ame_util.emptyStringList;
1896: conditionDescriptionsOut := ame_util.emptyLongStringList;
1897: raise;
1898: end getAttributesConditions1;
1899: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1900: conditionTypeIn in varchar2,
1895: conditionIdsOut := ame_util.emptyStringList;
1896: conditionDescriptionsOut := ame_util.emptyLongStringList;
1897: raise;
1898: end getAttributesConditions1;
1899: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1900: conditionTypeIn in varchar2,
1901: itemClassIdIn in integer,
1902: lineItemIn in varchar2 default ame_util.booleanFalse,
1903: conditionIdsOut out nocopy ame_util.stringList,
1898: end getAttributesConditions1;
1899: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1900: conditionTypeIn in varchar2,
1901: itemClassIdIn in integer,
1902: lineItemIn in varchar2 default ame_util.booleanFalse,
1903: conditionIdsOut out nocopy ame_util.stringList,
1904: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1905: cursor conditionCursor(attributeIdIn in integer,
1906: conditionTypeIn in varchar2,
1899: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1900: conditionTypeIn in varchar2,
1901: itemClassIdIn in integer,
1902: lineItemIn in varchar2 default ame_util.booleanFalse,
1903: conditionIdsOut out nocopy ame_util.stringList,
1904: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1905: cursor conditionCursor(attributeIdIn in integer,
1906: conditionTypeIn in varchar2,
1907: lineItemIn in varchar2,
1900: conditionTypeIn in varchar2,
1901: itemClassIdIn in integer,
1902: lineItemIn in varchar2 default ame_util.booleanFalse,
1903: conditionIdsOut out nocopy ame_util.stringList,
1904: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1905: cursor conditionCursor(attributeIdIn in integer,
1906: conditionTypeIn in varchar2,
1907: lineItemIn in varchar2,
1908: itemClassIdIn in integer) is
1912: from ame_conditions,
1913: ame_attributes
1914: where
1915: ame_conditions.attribute_id = ame_attributes.attribute_id and
1916: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1917: ame_attributes.attribute_id = attributeIdIn and
1918: ame_attributes.item_class_id = itemClassIdIn and
1919: condition_type = conditionTypeIn and
1920: sysdate between ame_conditions.start_date and
1917: ame_attributes.attribute_id = attributeIdIn and
1918: ame_attributes.item_class_id = itemClassIdIn and
1919: condition_type = conditionTypeIn and
1920: sysdate between ame_conditions.start_date and
1921: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1922: sysdate between ame_attributes.start_date and
1923: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1924: order by description;
1925: tempOutputIndex integer;
1919: condition_type = conditionTypeIn and
1920: sysdate between ame_conditions.start_date and
1921: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1922: sysdate between ame_attributes.start_date and
1923: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1924: order by description;
1925: tempOutputIndex integer;
1926: upperLimit integer;
1927: begin
1940: end loop;
1941: exception
1942: when others then
1943: rollback;
1944: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1945: routineNameIn => 'getAttributesConditions2',
1946: exceptionNumberIn => sqlcode,
1947: exceptionStringIn => sqlerrm);
1948: conditionIdsOut := ame_util.emptyStringList;
1944: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1945: routineNameIn => 'getAttributesConditions2',
1946: exceptionNumberIn => sqlcode,
1947: exceptionStringIn => sqlerrm);
1948: conditionIdsOut := ame_util.emptyStringList;
1949: conditionDescriptionsOut := ame_util.emptyLongStringList;
1950: raise;
1951: end getAttributesConditions2;
1952: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1945: routineNameIn => 'getAttributesConditions2',
1946: exceptionNumberIn => sqlcode,
1947: exceptionStringIn => sqlerrm);
1948: conditionIdsOut := ame_util.emptyStringList;
1949: conditionDescriptionsOut := ame_util.emptyLongStringList;
1950: raise;
1951: end getAttributesConditions2;
1952: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1953: applicationIdIn in integer,
1950: raise;
1951: end getAttributesConditions2;
1952: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1953: applicationIdIn in integer,
1954: applicationNamesOut out nocopy ame_util.stringList) as
1955: upperLimit integer;
1956: cursor getApplicationsCursor(conditionIdIn in integer,
1957: applicationIdIn in integer) is
1958: select distinct ame_calling_apps.application_name
1967: ame_rule_usages.item_id = ame_calling_apps.application_id and
1968: ame_rule_usages.item_id <> applicationIdIn and
1969: ame_condition_usages.condition_id = conditionIdIn and
1970: ((sysdate between ame_rules.start_date and
1971: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
1972: (sysdate < ame_rules.start_date and
1973: ame_rules.start_date < nvl(ame_rules.end_date,
1974: ame_rules.start_date + ame_util.oneSecond))) and
1975: ((sysdate between ame_rule_usages.start_date and
1970: ((sysdate between ame_rules.start_date and
1971: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
1972: (sysdate < ame_rules.start_date and
1973: ame_rules.start_date < nvl(ame_rules.end_date,
1974: ame_rules.start_date + ame_util.oneSecond))) and
1975: ((sysdate between ame_rule_usages.start_date and
1976: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1977: (sysdate < ame_rule_usages.start_date and
1978: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1972: (sysdate < ame_rules.start_date and
1973: ame_rules.start_date < nvl(ame_rules.end_date,
1974: ame_rules.start_date + ame_util.oneSecond))) and
1975: ((sysdate between ame_rule_usages.start_date and
1976: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1977: (sysdate < ame_rule_usages.start_date and
1978: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1979: ame_rule_usages.start_date + ame_util.oneSecond))) and
1980: ((sysdate between ame_condition_usages.start_date and
1975: ((sysdate between ame_rule_usages.start_date and
1976: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1977: (sysdate < ame_rule_usages.start_date and
1978: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1979: ame_rule_usages.start_date + ame_util.oneSecond))) and
1980: ((sysdate between ame_condition_usages.start_date and
1981: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1982: (sysdate < ame_condition_usages.start_date and
1983: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1977: (sysdate < ame_rule_usages.start_date and
1978: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1979: ame_rule_usages.start_date + ame_util.oneSecond))) and
1980: ((sysdate between ame_condition_usages.start_date and
1981: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1982: (sysdate < ame_condition_usages.start_date and
1983: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1984: ame_condition_usages.start_date + ame_util.oneSecond))) and
1985: sysdate between ame_calling_apps.start_date and
1980: ((sysdate between ame_condition_usages.start_date and
1981: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1982: (sysdate < ame_condition_usages.start_date and
1983: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1984: ame_condition_usages.start_date + ame_util.oneSecond))) and
1985: sysdate between ame_calling_apps.start_date and
1986: nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
1987: order by ame_calling_apps.application_name;
1988: tempIndex integer;
1982: (sysdate < ame_condition_usages.start_date and
1983: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1984: ame_condition_usages.start_date + ame_util.oneSecond))) and
1985: sysdate between ame_calling_apps.start_date and
1986: nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
1987: order by ame_calling_apps.application_name;
1988: tempIndex integer;
1989: begin
1990: tempIndex := 1;
1995: end loop;
1996: exception
1997: when others then
1998: rollback;
1999: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2000: routineNameIn => 'getApplicationsUsingCondition',
2001: exceptionNumberIn => sqlcode,
2002: exceptionStringIn => '(condition ID ' ||
2003: conditionIdIn||
2002: exceptionStringIn => '(condition ID ' ||
2003: conditionIdIn||
2004: ') ' ||
2005: sqlerrm);
2006: applicationNamesOut := ame_util.emptyStringList;
2007: raise;
2008: end getApplicationsUsingCondition;
2009: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2010: descriptionsOut out nocopy ame_util.longStringList) as
2005: sqlerrm);
2006: applicationNamesOut := ame_util.emptyStringList;
2007: raise;
2008: end getApplicationsUsingCondition;
2009: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2010: descriptionsOut out nocopy ame_util.longStringList) as
2011: upperLimit integer;
2012: begin
2013: upperLimit := conditionIdsIn.count;
2006: applicationNamesOut := ame_util.emptyStringList;
2007: raise;
2008: end getApplicationsUsingCondition;
2009: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2010: descriptionsOut out nocopy ame_util.longStringList) as
2011: upperLimit integer;
2012: begin
2013: upperLimit := conditionIdsIn.count;
2014: for tempIndex in 1..upperLimit loop
2016: end loop;
2017: exception
2018: when others then
2019: rollback;
2020: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2021: routineNameIn => 'getDescriptions',
2022: exceptionNumberIn => sqlcode,
2023: exceptionStringIn => sqlerrm);
2024: descriptionsOut := ame_util.emptyLongStringList;
2020: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2021: routineNameIn => 'getDescriptions',
2022: exceptionNumberIn => sqlcode,
2023: exceptionStringIn => sqlerrm);
2024: descriptionsOut := ame_util.emptyLongStringList;
2025: raise;
2026: end getDescriptions;
2027: procedure getDetailUrls(applicationIdIn in integer,
2028: conditionIdsIn in ame_util.idList,
2024: descriptionsOut := ame_util.emptyLongStringList;
2025: raise;
2026: end getDescriptions;
2027: procedure getDetailUrls(applicationIdIn in integer,
2028: conditionIdsIn in ame_util.idList,
2029: detailUrlsOut out nocopy ame_util.longStringList) as
2030: conditionIdCount integer;
2031: begin
2032: conditionIdCount := conditionIdsIn.count;
2025: raise;
2026: end getDescriptions;
2027: procedure getDetailUrls(applicationIdIn in integer,
2028: conditionIdsIn in ame_util.idList,
2029: detailUrlsOut out nocopy ame_util.longStringList) as
2030: conditionIdCount integer;
2031: begin
2032: conditionIdCount := conditionIdsIn.count;
2033: for i in 1..conditionIdCount loop
2030: conditionIdCount integer;
2031: begin
2032: conditionIdCount := conditionIdsIn.count;
2033: for i in 1..conditionIdCount loop
2034: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2035: detailUrlsOut(i) := null;
2036: else
2037: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2038: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2033: for i in 1..conditionIdCount loop
2034: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2035: detailUrlsOut(i) := null;
2036: else
2037: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2038: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2039: 'ame_conditions_ui.getDetails?conditionIdIn=' ||
2040: conditionIdsIn(i) ||
2041: '&applicationIdIn=' ||
2034: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2035: detailUrlsOut(i) := null;
2036: else
2037: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2038: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2039: 'ame_conditions_ui.getDetails?conditionIdIn=' ||
2040: conditionIdsIn(i) ||
2041: '&applicationIdIn=' ||
2042: applicationIdIn);
2047: end loop;
2048: exception
2049: when others then
2050: rollback;
2051: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2052: routineNamein => 'getDetailUrls',
2053: exceptionNumberIn => sqlcode,
2054: exceptionStringIn => sqlerrm);
2055: detailUrlsOut := ame_util.emptyLongStringList;
2051: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2052: routineNamein => 'getDetailUrls',
2053: exceptionNumberIn => sqlcode,
2054: exceptionStringIn => sqlerrm);
2055: detailUrlsOut := ame_util.emptyLongStringList;
2056: raise;
2057: end getDetailUrls;
2058: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2059: parameterOneOut out nocopy ame_util.stringList,
2054: exceptionStringIn => sqlerrm);
2055: detailUrlsOut := ame_util.emptyLongStringList;
2056: raise;
2057: end getDetailUrls;
2058: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2059: parameterOneOut out nocopy ame_util.stringList,
2060: parameterTwoOut out nocopy ame_util.stringList) as
2061: cursor lMConditionCursor is
2062: select
2055: detailUrlsOut := ame_util.emptyLongStringList;
2056: raise;
2057: end getDetailUrls;
2058: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2059: parameterOneOut out nocopy ame_util.stringList,
2060: parameterTwoOut out nocopy ame_util.stringList) as
2061: cursor lMConditionCursor is
2062: select
2063: condition_id,
2056: raise;
2057: end getDetailUrls;
2058: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2059: parameterOneOut out nocopy ame_util.stringList,
2060: parameterTwoOut out nocopy ame_util.stringList) as
2061: cursor lMConditionCursor is
2062: select
2063: condition_id,
2064: parameter_one,
2064: parameter_one,
2065: parameter_two
2066: from ame_conditions
2067: where
2068: condition_type = ame_util.listModConditionType and
2069: sysdate between start_date and
2070: nvl(end_date - ame_util.oneSecond, sysdate) ;
2071: tempIndex integer;
2072: begin
2066: from ame_conditions
2067: where
2068: condition_type = ame_util.listModConditionType and
2069: sysdate between start_date and
2070: nvl(end_date - ame_util.oneSecond, sysdate) ;
2071: tempIndex integer;
2072: begin
2073: tempIndex := 1;
2074: for tempLMCondition in lMConditionCursor loop
2079: end loop;
2080: exception
2081: when others then
2082: rollback;
2083: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2084: routineNameIn => 'getLMConditions',
2085: exceptionNumberIn => sqlcode,
2086: exceptionStringIn => sqlerrm);
2087: conditionIdOut := ame_util.emptyIdList;
2083: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2084: routineNameIn => 'getLMConditions',
2085: exceptionNumberIn => sqlcode,
2086: exceptionStringIn => sqlerrm);
2087: conditionIdOut := ame_util.emptyIdList;
2088: parameterOneOut := ame_util.emptyStringList;
2089: parameterTwoOut := ame_util.emptyStringList;
2090: raise;
2091: end getLMConditions;
2084: routineNameIn => 'getLMConditions',
2085: exceptionNumberIn => sqlcode,
2086: exceptionStringIn => sqlerrm);
2087: conditionIdOut := ame_util.emptyIdList;
2088: parameterOneOut := ame_util.emptyStringList;
2089: parameterTwoOut := ame_util.emptyStringList;
2090: raise;
2091: end getLMConditions;
2092: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2085: exceptionNumberIn => sqlcode,
2086: exceptionStringIn => sqlerrm);
2087: conditionIdOut := ame_util.emptyIdList;
2088: parameterOneOut := ame_util.emptyStringList;
2089: parameterTwoOut := ame_util.emptyStringList;
2090: raise;
2091: end getLMConditions;
2092: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2093: descriptionsOut out nocopy ame_util.longStringList) as
2088: parameterOneOut := ame_util.emptyStringList;
2089: parameterTwoOut := ame_util.emptyStringList;
2090: raise;
2091: end getLMConditions;
2092: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2093: descriptionsOut out nocopy ame_util.longStringList) as
2094: cursor LMConditionCursor is
2095: select
2096: condition_id,
2089: parameterTwoOut := ame_util.emptyStringList;
2090: raise;
2091: end getLMConditions;
2092: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2093: descriptionsOut out nocopy ame_util.longStringList) as
2094: cursor LMConditionCursor is
2095: select
2096: condition_id,
2097: parameter_one,
2098: parameter_two
2099: from
2100: ame_conditions
2101: where
2102: condition_type = ame_util.listModConditionType and
2103: sysdate between start_date and
2104: nvl(end_date - ame_util.oneSecond, sysdate);
2105: tempIndex integer;
2106: approverDesc ame_util.longStringType;
2100: ame_conditions
2101: where
2102: condition_type = ame_util.listModConditionType and
2103: sysdate between start_date and
2104: nvl(end_date - ame_util.oneSecond, sysdate);
2105: tempIndex integer;
2106: approverDesc ame_util.longStringType;
2107: approverValid boolean;
2108: begin
2102: condition_type = ame_util.listModConditionType and
2103: sysdate between start_date and
2104: nvl(end_date - ame_util.oneSecond, sysdate);
2105: tempIndex integer;
2106: approverDesc ame_util.longStringType;
2107: approverValid boolean;
2108: begin
2109: tempIndex := 1;
2110: for LMConditionRec in LMConditionCursor loop
2113: descriptionOut => approverDesc,
2114: validityOut => approverValid);
2115: if(approverValid) then
2116: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2117: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2118: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2119: else
2120: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2121: end if;
2114: validityOut => approverValid);
2115: if(approverValid) then
2116: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2117: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2118: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2119: else
2120: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2121: end if;
2122: tempIndex := tempIndex + 1;
2116: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2117: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2118: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2119: else
2120: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2121: end if;
2122: tempIndex := tempIndex + 1;
2123: end if;
2124: end loop;
2124: end loop;
2125: exception
2126: when others then
2127: rollback;
2128: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2129: routineNameIn => 'getLMDescriptions',
2130: exceptionNumberIn => sqlcode,
2131: exceptionStringIn => sqlerrm);
2132: conditionIdsOut := ame_util.emptyStringList;
2128: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2129: routineNameIn => 'getLMDescriptions',
2130: exceptionNumberIn => sqlcode,
2131: exceptionStringIn => sqlerrm);
2132: conditionIdsOut := ame_util.emptyStringList;
2133: descriptionsOut := ame_util.emptyLongStringList;
2134: raise;
2135: end getLMDescriptions;
2136: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2129: routineNameIn => 'getLMDescriptions',
2130: exceptionNumberIn => sqlcode,
2131: exceptionStringIn => sqlerrm);
2132: conditionIdsOut := ame_util.emptyStringList;
2133: descriptionsOut := ame_util.emptyLongStringList;
2134: raise;
2135: end getLMDescriptions;
2136: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2137: descriptionsOut out nocopy ame_util.longStringList) as
2132: conditionIdsOut := ame_util.emptyStringList;
2133: descriptionsOut := ame_util.emptyLongStringList;
2134: raise;
2135: end getLMDescriptions;
2136: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2137: descriptionsOut out nocopy ame_util.longStringList) as
2138: cursor LMConditionCursor is
2139: select
2140: ame_conditions.condition_id condition_id,
2133: descriptionsOut := ame_util.emptyLongStringList;
2134: raise;
2135: end getLMDescriptions;
2136: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2137: descriptionsOut out nocopy ame_util.longStringList) as
2138: cursor LMConditionCursor is
2139: select
2140: ame_conditions.condition_id condition_id,
2141: ame_conditions.parameter_one parameter_one,
2142: ame_conditions.parameter_two parameter_two
2143: from
2144: ame_conditions
2145: where
2146: ame_conditions.condition_type = ame_util.listModConditionType and
2147: sysdate between ame_conditions.start_date and
2148: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2149: isApproverValid boolean;
2150: tempDescription ame_util.longStringType;
2144: ame_conditions
2145: where
2146: ame_conditions.condition_type = ame_util.listModConditionType and
2147: sysdate between ame_conditions.start_date and
2148: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2149: isApproverValid boolean;
2150: tempDescription ame_util.longStringType;
2151: tempIndex integer;
2152: begin
2146: ame_conditions.condition_type = ame_util.listModConditionType and
2147: sysdate between ame_conditions.start_date and
2148: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2149: isApproverValid boolean;
2150: tempDescription ame_util.longStringType;
2151: tempIndex integer;
2152: begin
2153: tempIndex := 1;
2154: for LMConditionRec in LMConditionCursor loop
2157: descriptionOut => tempDescription,
2158: validityOut => isApproverValid);
2159: if(isApproverValid and
2160: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2161: = ame_util.perOrigSystem) then
2162: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2163: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2164: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2165: else
2159: if(isApproverValid and
2160: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2161: = ame_util.perOrigSystem) then
2162: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2163: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2164: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2165: else
2166: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2167: end if;
2160: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2161: = ame_util.perOrigSystem) then
2162: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2163: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2164: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2165: else
2166: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2167: end if;
2168: tempIndex := tempIndex + 1;
2162: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2163: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2164: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2165: else
2166: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2167: end if;
2168: tempIndex := tempIndex + 1;
2169: end if;
2170: end loop;
2170: end loop;
2171: exception
2172: when others then
2173: rollback;
2174: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2175: routineNameIn => 'getLMDescriptions2',
2176: exceptionNumberIn => sqlcode,
2177: exceptionStringIn => sqlerrm);
2178: conditionIdsOut := ame_util.emptyStringList;
2174: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2175: routineNameIn => 'getLMDescriptions2',
2176: exceptionNumberIn => sqlcode,
2177: exceptionStringIn => sqlerrm);
2178: conditionIdsOut := ame_util.emptyStringList;
2179: descriptionsOut := ame_util.emptyLongStringList;
2180: raise;
2181: end getLMDescriptions2;
2182: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2175: routineNameIn => 'getLMDescriptions2',
2176: exceptionNumberIn => sqlcode,
2177: exceptionStringIn => sqlerrm);
2178: conditionIdsOut := ame_util.emptyStringList;
2179: descriptionsOut := ame_util.emptyLongStringList;
2180: raise;
2181: end getLMDescriptions2;
2182: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2183: conditionIdsOut out nocopy ame_util.stringList,
2179: descriptionsOut := ame_util.emptyLongStringList;
2180: raise;
2181: end getLMDescriptions2;
2182: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2183: conditionIdsOut out nocopy ame_util.stringList,
2184: descriptionsOut out nocopy ame_util.longStringList) as
2185: cursor LMConditionCursor(lmApproverTypeIn in varchar2) is
2186: select
2187: ame_conditions.condition_id condition_id,
2180: raise;
2181: end getLMDescriptions2;
2182: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2183: conditionIdsOut out nocopy ame_util.stringList,
2184: descriptionsOut out nocopy ame_util.longStringList) as
2185: cursor LMConditionCursor(lmApproverTypeIn in varchar2) is
2186: select
2187: ame_conditions.condition_id condition_id,
2188: ame_conditions.parameter_two parameter_two
2188: ame_conditions.parameter_two parameter_two
2189: from
2190: ame_conditions
2191: where
2192: ame_conditions.condition_type = ame_util.listModConditionType and
2193: ame_conditions.parameter_one = lmApproverTypeIn and
2194: sysdate between ame_conditions.start_date and
2195: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2196: isApproverValid boolean;
2191: where
2192: ame_conditions.condition_type = ame_util.listModConditionType and
2193: ame_conditions.parameter_one = lmApproverTypeIn and
2194: sysdate between ame_conditions.start_date and
2195: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2196: isApproverValid boolean;
2197: tempDescription ame_util.longStringType;
2198: tempIndex integer;
2199: begin
2193: ame_conditions.parameter_one = lmApproverTypeIn and
2194: sysdate between ame_conditions.start_date and
2195: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2196: isApproverValid boolean;
2197: tempDescription ame_util.longStringType;
2198: tempIndex integer;
2199: begin
2200: tempIndex := 1;
2201: for LMConditionRec in LMConditionCursor(lmApproverTypeIn => lmApproverTypeIn) loop
2204: descriptionOut => tempDescription,
2205: validityOut => isApproverValid);
2206: if(isApproverValid and
2207: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2208: = ame_util.perOrigSystem) then
2209: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2210: if(lmApproverTypeIn = ame_util.finalApprover) then
2211: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2212: else
2206: if(isApproverValid and
2207: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2208: = ame_util.perOrigSystem) then
2209: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2210: if(lmApproverTypeIn = ame_util.finalApprover) then
2211: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2212: else
2213: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2214: end if;
2207: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2208: = ame_util.perOrigSystem) then
2209: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2210: if(lmApproverTypeIn = ame_util.finalApprover) then
2211: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2212: else
2213: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2214: end if;
2215: tempIndex := tempIndex + 1;
2209: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2210: if(lmApproverTypeIn = ame_util.finalApprover) then
2211: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2212: else
2213: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2214: end if;
2215: tempIndex := tempIndex + 1;
2216: end if;
2217: end loop;
2217: end loop;
2218: exception
2219: when others then
2220: rollback;
2221: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2222: routineNameIn => 'getLMDescriptions3',
2223: exceptionNumberIn => sqlcode,
2224: exceptionStringIn => sqlerrm);
2225: conditionIdsOut := ame_util.emptyStringList;
2221: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2222: routineNameIn => 'getLMDescriptions3',
2223: exceptionNumberIn => sqlcode,
2224: exceptionStringIn => sqlerrm);
2225: conditionIdsOut := ame_util.emptyStringList;
2226: descriptionsOut := ame_util.emptyLongStringList;
2227: raise;
2228: end getLMDescriptions3;
2229: procedure getStringValueList(conditionIdIn in integer,
2222: routineNameIn => 'getLMDescriptions3',
2223: exceptionNumberIn => sqlcode,
2224: exceptionStringIn => sqlerrm);
2225: conditionIdsOut := ame_util.emptyStringList;
2226: descriptionsOut := ame_util.emptyLongStringList;
2227: raise;
2228: end getLMDescriptions3;
2229: procedure getStringValueList(conditionIdIn in integer,
2230: stringValueListOut out nocopy ame_util.longestStringList) as
2226: descriptionsOut := ame_util.emptyLongStringList;
2227: raise;
2228: end getLMDescriptions3;
2229: procedure getStringValueList(conditionIdIn in integer,
2230: stringValueListOut out nocopy ame_util.longestStringList) as
2231: cursor stringValueCursor(conditionIdIn in integer) is
2232: select string_value
2233: from ame_string_values
2234: where condition_id = conditionIdIn and
2232: select string_value
2233: from ame_string_values
2234: where condition_id = conditionIdIn and
2235: sysdate between start_date and
2236: nvl(end_date - ame_util.oneSecond, sysdate)
2237: order by string_value asc;
2238: attributeTypeException exception;
2239: errorCode integer;
2240: errorMessage ame_util.longestStringType;
2236: nvl(end_date - ame_util.oneSecond, sysdate)
2237: order by string_value asc;
2238: attributeTypeException exception;
2239: errorCode integer;
2240: errorMessage ame_util.longestStringType;
2241: tempIndex integer;
2242: begin
2243: if(getAttributeType(conditionIdIn => conditionIdIn) <> ame_util.stringAttributeType) then
2244: raise attributeTypeException;
2239: errorCode integer;
2240: errorMessage ame_util.longestStringType;
2241: tempIndex integer;
2242: begin
2243: if(getAttributeType(conditionIdIn => conditionIdIn) <> ame_util.stringAttributeType) then
2244: raise attributeTypeException;
2245: end if;
2246: tempIndex := 1;
2247: for tempStringValue in stringValueCursor(conditionIdIn) loop
2252: when attributeTypeException then
2253: rollback;
2254: errorCode := -20001;
2255: errorMessage :=
2256: ame_util.getMessage(applicationShortNameIn => 'PER',
2257: messageNameIn => 'AME_400192_CON_STR_VAL_NOT_DEF');
2258: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2259: routineNameIn => 'getStringValueList',
2260: exceptionNumberIn => errorCode,
2254: errorCode := -20001;
2255: errorMessage :=
2256: ame_util.getMessage(applicationShortNameIn => 'PER',
2257: messageNameIn => 'AME_400192_CON_STR_VAL_NOT_DEF');
2258: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2259: routineNameIn => 'getStringValueList',
2260: exceptionNumberIn => errorCode,
2261: exceptionStringIn => errorMessage);
2262: stringValueListOut := ame_util.emptyLongestStringList;
2258: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2259: routineNameIn => 'getStringValueList',
2260: exceptionNumberIn => errorCode,
2261: exceptionStringIn => errorMessage);
2262: stringValueListOut := ame_util.emptyLongestStringList;
2263: raise_application_error(errorCode,
2264: errorMessage);
2265: when others then
2266: rollback;
2263: raise_application_error(errorCode,
2264: errorMessage);
2265: when others then
2266: rollback;
2267: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2268: routineNameIn => 'getStringValueList',
2269: exceptionNumberIn => sqlcode,
2270: exceptionStringIn => '(condition ID ' ||
2271: conditionIdIn||
2270: exceptionStringIn => '(condition ID ' ||
2271: conditionIdIn||
2272: ') ' ||
2273: sqlerrm);
2274: stringValueListOut := ame_util.emptyLongestStringList;
2275: raise;
2276: end getStringValueList;
2277: procedure remove(conditionIdIn in integer,
2278: versionStartDateIn in date,
2282: from ame_conditions
2283: where
2284: condition_id = conditionIdIn and
2285: sysdate between start_date and
2286: nvl(end_date - ame_util.oneSecond, sysdate)
2287: for update;
2288: attributeType ame_attributes.attribute_type%type;
2289: conditionType ame_conditions.condition_type%type;
2290: currentUserId integer;
2288: attributeType ame_attributes.attribute_type%type;
2289: conditionType ame_conditions.condition_type%type;
2290: currentUserId integer;
2291: errorCode integer;
2292: errorMessage ame_util.longestStringType;
2293: inUseException exception;
2294: objectVersionNoDataException exception;
2295: startDate date;
2296: processingDate date;
2307: end if;
2308: if(isInUse(conditionIdIn)) then
2309: raise inUseException;
2310: end if;
2311: currentUserId := ame_util.getCurrentUserId;
2312: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2313: if versionStartDateIn = startDate then
2314: if conditionType <> ame_util.listModConditionType then
2315: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2310: end if;
2311: currentUserId := ame_util.getCurrentUserId;
2312: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2313: if versionStartDateIn = startDate then
2314: if conditionType <> ame_util.listModConditionType then
2315: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2316: if(attributeType = ame_util.stringAttributeType) then
2317: update ame_string_values
2318: set
2312: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2313: if versionStartDateIn = startDate then
2314: if conditionType <> ame_util.listModConditionType then
2315: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2316: if(attributeType = ame_util.stringAttributeType) then
2317: update ame_string_values
2318: set
2319: last_updated_by = currentUserId,
2320: last_update_date = processingDate,
2322: end_date = processingDate
2323: where
2324: condition_id = conditionIdIn and
2325: processingDate between start_date and
2326: nvl(end_date - ame_util.oneSecond, processingDate) ;
2327: end if;
2328: end if;
2329: update ame_conditions
2330: set
2334: end_date = processingDate
2335: where
2336: condition_id = conditionIdIn and
2337: processingDate between start_date and
2338: nvl(end_date - ame_util.oneSecond, processingDate) ;
2339: commit;
2340: else
2341: close startDateCursor;
2342: raise ame_util.objectVersionException;
2338: nvl(end_date - ame_util.oneSecond, processingDate) ;
2339: commit;
2340: else
2341: close startDateCursor;
2342: raise ame_util.objectVersionException;
2343: end if;
2344: close startDateCursor;
2345: exception
2346: when ame_util.objectVersionException then
2342: raise ame_util.objectVersionException;
2343: end if;
2344: close startDateCursor;
2345: exception
2346: when ame_util.objectVersionException then
2347: rollback;
2348: if(startDateCursor%isOpen) then
2349: close startDateCursor;
2350: end if;
2349: close startDateCursor;
2350: end if;
2351: errorCode := -20001;
2352: errorMessage :=
2353: ame_util.getMessage(applicationShortNameIn => 'PER',
2354: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2355: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2356: routineNameIn => 'remove',
2357: exceptionNumberIn => errorCode,
2351: errorCode := -20001;
2352: errorMessage :=
2353: ame_util.getMessage(applicationShortNameIn => 'PER',
2354: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2355: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2356: routineNameIn => 'remove',
2357: exceptionNumberIn => errorCode,
2358: exceptionStringIn => errorMessage);
2359: raise_application_error(errorCode,
2364: close startDateCursor;
2365: end if;
2366: errorCode := -20001;
2367: errorMessage :=
2368: ame_util.getMessage(applicationShortNameIn => 'PER',
2369: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2370: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2371: routineNameIn => 'remove',
2372: exceptionNumberIn => errorCode,
2366: errorCode := -20001;
2367: errorMessage :=
2368: ame_util.getMessage(applicationShortNameIn => 'PER',
2369: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2370: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2371: routineNameIn => 'remove',
2372: exceptionNumberIn => errorCode,
2373: exceptionStringIn => errorMessage);
2374: raise_application_error(errorCode,
2376: when inUseException then
2377: rollback;
2378: errorCode := -20001;
2379: errorMessage :=
2380: ame_util.getMessage(applicationShortNameIn => 'PER',
2381: messageNameIn => 'AME_400193_CON_IN USE');
2382: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2383: routineNameIn => 'remove',
2384: exceptionNumberIn => errorCode,
2378: errorCode := -20001;
2379: errorMessage :=
2380: ame_util.getMessage(applicationShortNameIn => 'PER',
2381: messageNameIn => 'AME_400193_CON_IN USE');
2382: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2383: routineNameIn => 'remove',
2384: exceptionNumberIn => errorCode,
2385: exceptionStringIn => errorMessage);
2386: raise_application_error(errorCode,
2389: rollback;
2390: if(startDateCursor%isOpen) then
2391: close startDateCursor;
2392: end if;
2393: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2394: routineNameIn => 'remove',
2395: exceptionNumberIn => sqlcode,
2396: exceptionStringIn => '(condition ID ' ||
2397: conditionIdIn||
2403: conditionIdIn in integer,
2404: newConditionIdIn in integer default null,
2405: finalizeIn in boolean default true,
2406: processingDateIn in date default null) as
2407: actionIdList ame_util.idList;
2408: conditionIdList ame_util.idList;
2409: currentUserId integer;
2410: errorCode integer;
2411: errorMessage ame_util.longestStringType;
2404: newConditionIdIn in integer default null,
2405: finalizeIn in boolean default true,
2406: processingDateIn in date default null) as
2407: actionIdList ame_util.idList;
2408: conditionIdList ame_util.idList;
2409: currentUserId integer;
2410: errorCode integer;
2411: errorMessage ame_util.longestStringType;
2412: inUseException exception;
2407: actionIdList ame_util.idList;
2408: conditionIdList ame_util.idList;
2409: currentUserId integer;
2410: errorCode integer;
2411: errorMessage ame_util.longestStringType;
2412: inUseException exception;
2413: processingDate date;
2414: ruleType ame_rules.rule_type%type;
2415: tempIndex integer;
2418: processingDate := sysdate;
2419: else
2420: processingDate := processingDateIn;
2421: end if;
2422: currentUserId := ame_util.getCurrentUserId;
2423: update ame_condition_usages
2424: set
2425: last_updated_by = currentUserId,
2426: last_update_date = processingDate,
2429: where
2430: condition_id = conditionIdIn and
2431: rule_id = ruleIdIn and
2432: ((processingDate between start_date and
2433: nvl(end_date - ame_util.oneSecond, processingDate)) or
2434: (processingDate < start_date and
2435: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2436: if(newConditionIdIn is not null) then
2437: /* The list modification condition has been changed. Check to see if
2431: rule_id = ruleIdIn and
2432: ((processingDate between start_date and
2433: nvl(end_date - ame_util.oneSecond, processingDate)) or
2434: (processingDate < start_date and
2435: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2436: if(newConditionIdIn is not null) then
2437: /* The list modification condition has been changed. Check to see if
2438: changing the condition resulted in a rule duplication. */
2439: ame_rule_pkg.getConditionIds(ruleIdIn => ruleIdIn,
2456: when inUseException then
2457: rollback;
2458: errorCode := -20001;
2459: errorMessage :=
2460: ame_util.getMessage(applicationShortNameIn => 'PER',
2461: messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
2462: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2463: routineNamein => 'removeConditionUsage',
2464: exceptionNumberIn => errorCode,
2458: errorCode := -20001;
2459: errorMessage :=
2460: ame_util.getMessage(applicationShortNameIn => 'PER',
2461: messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
2462: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2463: routineNamein => 'removeConditionUsage',
2464: exceptionNumberIn => errorCode,
2465: exceptionStringIn => errorMessage);
2466: raise_application_error(errorCode,
2466: raise_application_error(errorCode,
2467: errorMessage);
2468: when others then
2469: rollback;
2470: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2471: routineNameIn => 'removeConditionUsage',
2472: exceptionNumberIn => sqlcode,
2473: exceptionStringIn => '(condition ID ' ||
2474: conditionIdIn||
2477: raise;
2478: end removeConditionUsage;
2479: procedure removeStringValue(conditionIdIn in integer,
2480: versionStartDateIn in date,
2481: stringValueListIn in ame_util.longestStringList,
2482: processingDateIn in date default null) as
2483: cursor startDateCursor is
2484: select start_date
2485: from ame_conditions
2485: from ame_conditions
2486: where
2487: condition_id = conditionIdIn and
2488: sysdate between start_date and
2489: nvl(end_date - ame_util.oneSecond, sysdate)
2490: for update;
2491: attributeId ame_conditions.attribute_id%type;
2492: conditionId ame_conditions.condition_id%type;
2493: conditionKey ame_conditions.condition_key%type;
2493: conditionKey ame_conditions.condition_key%type;
2494: conditionType ame_conditions.condition_type%type;
2495: currentUserId integer;
2496: errorCode integer;
2497: errorMessage ame_util.longestStringType;
2498: includeLowerLimit ame_conditions.include_lower_limit%type;
2499: includeUpperLimit ame_conditions.include_upper_limit%type;
2500: objectVersionNoDataException exception;
2501: parameterOne ame_conditions.parameter_one%type;
2514: fetch startDateCursor into startDate;
2515: if startDateCursor%notfound then
2516: raise objectVersionNoDataException;
2517: end if;
2518: currentUserId := ame_util.getCurrentUserId;
2519: if versionStartDateIn = startDate then
2520: stringCount := stringValueListIn.count;
2521: for i in 1..stringCount loop
2522: update ame_string_values
2528: where
2529: condition_id = conditionIdIn and
2530: string_value = stringValueListIn(i) and
2531: processingDate between start_date and
2532: nvl(end_date - ame_util.oneSecond, processingDate) ;
2533: end loop;
2534: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2535: conditionKey := ame_condition_pkg.getConditionKey(conditionIdIn => conditionIdIn);
2536: attributeId := ame_condition_pkg.getAttributeId(conditionIdIn => conditionIdIn);
2547: end_date = processingDate
2548: where
2549: condition_id = conditionIdIn and
2550: processingDate between start_date and
2551: nvl(end_date - ame_util.oneSecond, processingDate) ;
2552: conditionId := new(conditionIdIn => conditionIdIn,
2553: typeIn => conditionType,
2554: attributeIdIn => attributeId,
2555: conditionKeyIn => conditionKey,
2559: includeLowerLimitIn => includeLowerLimit,
2560: includeUpperLimitIn => includeUpperLimit);
2561: else
2562: close startDateCursor;
2563: raise ame_util.objectVersionException;
2564: end if;
2565: close startDateCursor;
2566: exception
2567: when ame_util.objectVersionException then
2563: raise ame_util.objectVersionException;
2564: end if;
2565: close startDateCursor;
2566: exception
2567: when ame_util.objectVersionException then
2568: rollback;
2569: if(startDateCursor%isOpen) then
2570: close startDateCursor;
2571: end if;
2570: close startDateCursor;
2571: end if;
2572: errorCode := -20001;
2573: errorMessage :=
2574: ame_util.getMessage(applicationShortNameIn => 'PER',
2575: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2576: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2577: routineNameIn => 'removeStringValue',
2578: exceptionNumberIn => errorCode,
2572: errorCode := -20001;
2573: errorMessage :=
2574: ame_util.getMessage(applicationShortNameIn => 'PER',
2575: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2576: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2577: routineNameIn => 'removeStringValue',
2578: exceptionNumberIn => errorCode,
2579: exceptionStringIn => errorMessage);
2580: raise_application_error(errorCode,
2585: close startDateCursor;
2586: end if;
2587: errorCode := -20001;
2588: errorMessage :=
2589: ame_util.getMessage(applicationShortNameIn => 'PER',
2590: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2591: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2592: routineNameIn => 'removeStringValue',
2593: exceptionNumberIn => errorCode,
2587: errorCode := -20001;
2588: errorMessage :=
2589: ame_util.getMessage(applicationShortNameIn => 'PER',
2590: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2591: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2592: routineNameIn => 'removeStringValue',
2593: exceptionNumberIn => errorCode,
2594: exceptionStringIn => errorMessage);
2595: raise_application_error(errorCode,
2598: rollback;
2599: if(startDateCursor%isOpen) then
2600: close startDateCursor;
2601: end if;
2602: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2603: routineNameIn => 'removeStringValue',
2604: exceptionNumberIn => sqlcode,
2605: exceptionStringIn => '(condition ID ' ||
2606: conditionIdIn||