[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: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
365: end if;
366: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
372: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
373: else
366: else
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
372: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
373: else
374: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
367: if(parameterOne = parameterTwo and
368: includeLowerLimit = ame_util.booleanTrue and
369: includeUpperLimit = ame_util.booleanTrue) then
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
372: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
373: else
374: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
375: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
370: if attributeType = ame_util.dateAttributeType then
371: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
372: description := lineItemLabel||attributeName|| ' = ' || parameterOneDateString;
373: else
374: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
375: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
376: end if;
377: else
378: if attributeType = ame_util.dateAttributeType then
374: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
375: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
376: end if;
377: else
378: if attributeType = ame_util.dateAttributeType then
379: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
380: description := lineItemLabel||parameterOneDateString;
381: else
382: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
375: description := lineItemLabel||attributeName|| ' = ' || parameterOneNumberString;
376: end if;
377: else
378: if attributeType = ame_util.dateAttributeType then
379: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
380: description := lineItemLabel||parameterOneDateString;
381: else
382: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
383: description := lineItemLabel||parameterOneNumberString;
378: if attributeType = ame_util.dateAttributeType then
379: parameterOneDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterOne));
380: description := lineItemLabel||parameterOneDateString;
381: else
382: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
383: description := lineItemLabel||parameterOneNumberString;
384: end if;
385: if parameterOne is not null then
386: if(includeLowerLimit = ame_util.booleanTrue) then
382: parameterOneNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterOne);
383: description := lineItemLabel||parameterOneNumberString;
384: end if;
385: if parameterOne is not null then
386: if(includeLowerLimit = ame_util.booleanTrue) then
387: description := description || ' <= ';
388: else
389: description := description || ' < ';
390: end if;
390: end if;
391: end if;
392: description := description || attributeName;
393: if parameterTwo is not null then
394: if(includeUpperLimit = ame_util.booleanTrue) then
395: description := description || ' <= ';
396: else
397: description := description || ' < ';
398: end if;
395: description := description || ' <= ';
396: else
397: description := description || ' < ';
398: end if;
399: if attributeType = ame_util.dateAttributeType then
400: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo));
401: description := description || parameterTwoDateString;
402: else
403: parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
396: else
397: description := description || ' < ';
398: end if;
399: if attributeType = ame_util.dateAttributeType then
400: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo));
401: description := description || parameterTwoDateString;
402: else
403: parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
404: description := description || parameterTwoNumberString;
399: if attributeType = ame_util.dateAttributeType then
400: parameterTwoDateString := fnd_date.date_to_displayDate(dateVal => ame_util.versionStringToDate(stringDateIn => parameterTwo));
401: description := description || parameterTwoDateString;
402: else
403: parameterTwoNumberString := ame_util.canonNumStringToDisplayString(canonicalNumberStringIn => parameterTwo);
404: description := description || parameterTwoNumberString;
405: end if;
406: end if;
407: end if;
425: ame_conditions
426: where
427: condition_id = conditionIdIn and
428: sysdate between start_date and
429: nvl(end_date - ame_util.oneSecond, sysdate) ;
430: if(parameterOne = parameterTwo and
431: includeLowerLimit = ame_util.booleanTrue and
432: includeUpperLimit = ame_util.booleanTrue) then
433: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
427: condition_id = conditionIdIn and
428: sysdate between start_date and
429: nvl(end_date - ame_util.oneSecond, sysdate) ;
430: if(parameterOne = parameterTwo and
431: includeLowerLimit = ame_util.booleanTrue and
432: includeUpperLimit = ame_util.booleanTrue) then
433: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
434: else
435: description := lineItemLabel||parameterOne;
428: sysdate between start_date and
429: nvl(end_date - ame_util.oneSecond, sysdate) ;
430: if(parameterOne = parameterTwo and
431: includeLowerLimit = ame_util.booleanTrue and
432: includeUpperLimit = ame_util.booleanTrue) then
433: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
434: else
435: description := lineItemLabel||parameterOne;
436: if parameterOne is not null then
433: description := lineItemLabel||attributeName || ' = ' || parameterOne || ' ' || parameterThree;
434: else
435: description := lineItemLabel||parameterOne;
436: if parameterOne is not null then
437: if(includeLowerLimit = ame_util.booleanTrue) then
438: description := description||' ' ||parameterThree|| ' <= ';
439: else
440: description := description||' ' ||parameterThree|| ' < ';
441: end if;
441: end if;
442: end if;
443: description := description || attributeName;
444: if parameterTwo is not null then
445: if(includeUpperLimit = ame_util.booleanTrue) then
446: description := description || ' <= ';
447: else
448: description := description || ' < ';
449: end if;
457: return(description);
458: exception
459: when others then
460: rollback;
461: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
462: routineNameIn => 'getDescription',
463: exceptionNumberIn => sqlcode,
464: exceptionStringIn => '(condition ID ' ||
465: conditionIdIn ||
476: from ame_conditions
477: where
478: condition_id = conditionIdIn and
479: sysdate between start_date and
480: nvl(end_date - ame_util.oneSecond, sysdate) ;
481: return(includeLowerLimit);
482: exception
483: when others then
484: rollback;
481: return(includeLowerLimit);
482: exception
483: when others then
484: rollback;
485: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
486: routineNameIn => 'getIncludeLowerLimit',
487: exceptionNumberIn => sqlcode,
488: exceptionStringIn => '(condition ID ' ||
489: conditionIdIn||
500: from ame_conditions
501: where
502: condition_id = conditionIdIn and
503: sysdate between start_date and
504: nvl(end_date - ame_util.oneSecond, sysdate) ;
505: return(includeUpperLimit);
506: exception
507: when others then
508: rollback;
505: return(includeUpperLimit);
506: exception
507: when others then
508: rollback;
509: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
510: routineNameIn => 'getIncludeUpperLimit',
511: exceptionNumberIn => sqlcode,
512: exceptionStringIn => '(condition ID ' ||
513: conditionIdIn||
527: where
528: ame_conditions.condition_id = conditionIdIn and
529: ame_attributes.attribute_id = ame_conditions.attribute_id and
530: sysdate between ame_conditions.start_date and
531: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
532: sysdate between ame_attributes.start_date and
533: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
534: if attributeType = ame_util.stringAttributeType then
535: return(true);
529: ame_attributes.attribute_id = ame_conditions.attribute_id and
530: sysdate between ame_conditions.start_date and
531: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
532: sysdate between ame_attributes.start_date and
533: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
534: if attributeType = ame_util.stringAttributeType then
535: return(true);
536: end if;
537: return(false);
530: sysdate between ame_conditions.start_date and
531: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
532: sysdate between ame_attributes.start_date and
533: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
534: if attributeType = ame_util.stringAttributeType then
535: return(true);
536: end if;
537: return(false);
538: exception
540: rollback;
541: return(false);
542: when others then
543: rollback;
544: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
545: routineNameIn => 'isStringAttributeType',
546: exceptionNumberIn => sqlcode,
547: exceptionStringIn => '(condition ID ' ||
548: conditionIdIn||
559: from ame_conditions
560: where
561: condition_id = conditionIdIn and
562: sysdate between start_date and
563: nvl(end_date - ame_util.oneSecond, sysdate) ;
564: return(parameterOne);
565: exception
566: when others then
567: rollback;
564: return(parameterOne);
565: exception
566: when others then
567: rollback;
568: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
569: routineNameIn => 'getParameterOne',
570: exceptionNumberIn => sqlcode,
571: exceptionStringIn => '(condition ID ' ||
572: conditionIdIn||
583: from ame_conditions
584: where
585: condition_id = conditionIdIn and
586: sysdate between start_date and
587: nvl(end_date - ame_util.oneSecond, sysdate) ;
588: return(parameterTwo);
589: exception
590: when others then
591: rollback;
588: return(parameterTwo);
589: exception
590: when others then
591: rollback;
592: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
593: routineNameIn => 'getParameterTwo',
594: exceptionNumberIn => sqlcode,
595: exceptionStringIn => '(condition ID ' ||
596: conditionIdIn||
607: from ame_conditions
608: where
609: condition_id = conditionIdIn and
610: sysdate between start_date and
611: nvl(end_date - ame_util.oneSecond, sysdate) ;
612: return(parameterThree);
613: exception
614: when others then
615: rollback;
612: return(parameterThree);
613: exception
614: when others then
615: rollback;
616: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
617: routineNameIn => 'getParameterThree',
618: exceptionNumberIn => sqlcode,
619: exceptionStringIn => '(condition ID ' ||
620: conditionIdIn||
631: from ame_conditions
632: where
633: condition_id = conditionIdIn and
634: sysdate between start_date and
635: nvl(end_date - ame_util.oneSecond, sysdate) ;
636: return(startDate);
637: exception
638: when others then
639: rollback;
636: return(startDate);
637: exception
638: when others then
639: rollback;
640: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
641: routineNameIn => 'getStartDate',
642: exceptionNumberIn => sqlcode,
643: exceptionStringIn => '(condition ID ' ||
644: conditionIdIn||
655: from ame_conditions
656: where
657: condition_id = conditionIdIn and
658: sysdate between start_date and
659: nvl(end_date - ame_util.oneSecond, sysdate) ;
660: return(conditionType);
661: exception
662: when others then
663: rollback;
660: return(conditionType);
661: exception
662: when others then
663: rollback;
664: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
665: routineNameIn => 'getType',
666: exceptionNumberIn => sqlcode,
667: exceptionStringIn => '(condition ID ' ||
668: conditionIdIn||
680: from ame_conditions
681: where
682: condition_id = conditionIdIn and
683: sysdate between start_date and
684: nvl(end_date - ame_util.oneSecond, sysdate) ;
685: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
686: return(stringStartDate);
687: exception
688: when others then
681: where
682: condition_id = conditionIdIn and
683: sysdate between start_date and
684: nvl(end_date - ame_util.oneSecond, sysdate) ;
685: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
686: return(stringStartDate);
687: exception
688: when others then
689: rollback;
686: return(stringStartDate);
687: exception
688: when others then
689: rollback;
690: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
691: routineNameIn => 'getVersionStartDate',
692: exceptionNumberIn => sqlcode,
693: exceptionStringIn => '(condition ID ' ||
694: conditionIdIn||
708: where
709: condition_id = conditionIdIn and
710: rule_id = ruleIdIn and
711: ((sysdate between start_date and
712: nvl(end_date - ame_util.oneSecond, sysdate)) or
713: (sysdate < start_date and
714: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
715: if(useCount > 0) then
716: return(true);
710: rule_id = ruleIdIn and
711: ((sysdate between start_date and
712: nvl(end_date - ame_util.oneSecond, sysdate)) or
713: (sysdate < start_date and
714: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
715: if(useCount > 0) then
716: return(true);
717: end if;
718: return(false);
718: return(false);
719: exception
720: when others then
721: rollback;
722: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
723: routineNameIn => 'isConditionUsage',
724: exceptionNumberIn => sqlcode,
725: exceptionStringIn => '(condition ID ' ||
726: conditionIdIn||
742: ame_rule_usages.rule_id = ame_condition_usages.rule_id and
743: ame_rule_usages.item_id <> applicationIdIn and
744: ame_condition_usages.condition_id = conditionIdIn and
745: ((sysdate between ame_rule_usages.start_date and
746: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
747: (sysdate < ame_rule_usages.start_date and
748: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
749: ame_rule_usages.start_date + ame_util.oneSecond))) and
750: ((sysdate between ame_condition_usages.start_date and
745: ((sysdate between ame_rule_usages.start_date and
746: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
747: (sysdate < ame_rule_usages.start_date and
748: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
749: ame_rule_usages.start_date + ame_util.oneSecond))) and
750: ((sysdate between ame_condition_usages.start_date and
751: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
752: (sysdate < ame_condition_usages.start_date and
753: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
747: (sysdate < ame_rule_usages.start_date and
748: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
749: ame_rule_usages.start_date + ame_util.oneSecond))) and
750: ((sysdate between ame_condition_usages.start_date and
751: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
752: (sysdate < ame_condition_usages.start_date and
753: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
754: ame_condition_usages.start_date + ame_util.oneSecond))) ;
755: if(useCount > 0) then
750: ((sysdate between ame_condition_usages.start_date and
751: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
752: (sysdate < ame_condition_usages.start_date and
753: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
754: ame_condition_usages.start_date + ame_util.oneSecond))) ;
755: if(useCount > 0) then
756: return(true);
757: end if;
758: return(false);
758: return(false);
759: exception
760: when others then
761: rollback;
762: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
763: routineNameIn => 'isInUseByOtherApps',
764: exceptionNumberIn => sqlcode,
765: exceptionStringIn => '(condition ID ' ||
766: conditionIdIn||
777: from ame_condition_usages
778: where
779: condition_id = conditionIdIn and
780: ((sysdate between start_date and
781: nvl(end_date - ame_util.oneSecond, sysdate)) or
782: (sysdate < start_date and
783: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
784: if(useCount > 0) then
785: return(true);
779: condition_id = conditionIdIn and
780: ((sysdate between start_date and
781: nvl(end_date - ame_util.oneSecond, sysdate)) or
782: (sysdate < start_date and
783: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
784: if(useCount > 0) then
785: return(true);
786: end if;
787: return(false);
787: return(false);
788: exception
789: when others then
790: rollback;
791: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
792: routineNameIn => 'isInUse',
793: exceptionNumberIn => sqlcode,
794: exceptionStringIn => '(condition ID ' ||
795: conditionIdIn||
810: ame_conditions
811: where
812: ame_attributes.attribute_id = ame_conditions.attribute_id and
813: ame_conditions.condition_type = conditionTypeIn and
814: ame_attributes.line_item = ame_util.booleanTrue and
815: ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
816: ame_attribute_usages.application_id = applicationIdIn and
817: ((sysdate between ame_attribute_usages.start_date and
818: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
814: ame_attributes.line_item = ame_util.booleanTrue and
815: ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
816: ame_attribute_usages.application_id = applicationIdIn and
817: ((sysdate between ame_attribute_usages.start_date and
818: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
819: (sysdate < ame_attribute_usages.start_date and
820: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
821: ame_attribute_usages.start_date + ame_util.oneSecond))) and
822: sysdate between ame_attributes.start_date and
817: ((sysdate between ame_attribute_usages.start_date and
818: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)) or
819: (sysdate < ame_attribute_usages.start_date and
820: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
821: ame_attribute_usages.start_date + ame_util.oneSecond))) and
822: sysdate between ame_attributes.start_date and
823: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
824: sysdate between ame_conditions.start_date and
825: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
819: (sysdate < ame_attribute_usages.start_date and
820: ame_attribute_usages.start_date < nvl(ame_attribute_usages.end_date,
821: ame_attribute_usages.start_date + ame_util.oneSecond))) and
822: sysdate between ame_attributes.start_date and
823: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
824: sysdate between ame_conditions.start_date and
825: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
826: if(lineItemCount > 0) then
827: return(true);
821: ame_attribute_usages.start_date + ame_util.oneSecond))) and
822: sysdate between ame_attributes.start_date and
823: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
824: sysdate between ame_conditions.start_date and
825: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) ;
826: if(lineItemCount > 0) then
827: return(true);
828: end if;
829: return(false);
829: return(false);
830: exception
831: when others then
832: rollback;
833: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
834: routineNameIn => 'lineItemIsInUse',
835: exceptionNumberIn => sqlcode,
836: exceptionStringIn => sqlerrm);
837: raise;
845: parameterTwoIn in varchar2 default null,
846: parameterThreeIn in varchar2 default null,
847: includeLowerLimitIn in varchar2 default null,
848: includeUpperLimitIn in varchar2 default null,
849: stringValueListIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
850: newStartDateIn in date default null,
851: conditionIdIn in integer default null,
852: commitIn in boolean default true,
853: processingDateIn in date default null) return integer as
856: from ame_conditions
857: where
858: attribute_id = attributeIdIn and
859: sysdate between start_date and
860: nvl(end_date - ame_util.oneSecond, sysdate) ;
861: attributeType ame_attributes.attribute_type%type;
862: conditionCount integer;
863: conditionExistsException exception;
864: conditionId integer;
868: currencyNumberException1 exception;
869: currentUserId integer;
870: dateException exception;
871: errorCode integer;
872: errorMessage ame_util.longestStringType;
873: invalidConditionException exception;
874: parameterOneLengthException exception;
875: parameterTwoLengthException exception;
876: parameterThreeLengthException exception;
875: parameterTwoLengthException exception;
876: parameterThreeLengthException exception;
877: lowerlimitLengthException exception;
878: stringCount integer;
879: stringValueList ame_util.longestStringList;
880: tempCount integer;
881: tempCount2 integer;
882: tempIndex integer;
883: tempStringValueList ame_util.longestStringList;
879: stringValueList ame_util.longestStringList;
880: tempCount integer;
881: tempCount2 integer;
882: tempIndex integer;
883: tempStringValueList ame_util.longestStringList;
884: upperLimit integer;
885: upperlimitLengthException exception;
886: processingDate date;
887: begin
889: processingDate := sysdate;
890: else
891: processingDate := processingDateIn;
892: end if;
893: if(typeIn = ame_util.listModConditionType) then
894: attributeType := null;
895: elsif(attributeTypeIn is null) then
896: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
897: else
899: end if;
900: if(lengthb(conditionKeyIn) > 100) then
901: raise condKeyLengthException;
902: end if;
903: if(attributeType = ame_util.dateAttributeType) then
904: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
905: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
906: raise dateException;
907: end if;
900: if(lengthb(conditionKeyIn) > 100) then
901: raise condKeyLengthException;
902: end if;
903: if(attributeType = ame_util.dateAttributeType) then
904: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
905: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
906: raise dateException;
907: end if;
908: elsif(attributeType = ame_util.currencyAttributeType or
901: raise condKeyLengthException;
902: end if;
903: if(attributeType = ame_util.dateAttributeType) then
904: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
905: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
906: raise dateException;
907: end if;
908: elsif(attributeType = ame_util.currencyAttributeType or
909: attributeType = ame_util.numberAttributeType) then
904: if ame_util.versionStringToDate(stringDateIn => parameterOneIn)
905: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
906: raise dateException;
907: end if;
908: elsif(attributeType = ame_util.currencyAttributeType or
909: attributeType = ame_util.numberAttributeType) then
910: if(ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeIdIn)) is null then
911: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
912: raise currencyNumberException;
905: > ame_util.versionStringToDate(stringDateIn => parameterTwoIn) then
906: raise dateException;
907: end if;
908: elsif(attributeType = ame_util.currencyAttributeType or
909: attributeType = ame_util.numberAttributeType) then
910: if(ame_attribute_pkg.getApproverTypeId(attributeIdIn => attributeIdIn)) is null then
911: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
912: raise currencyNumberException;
913: end if;
911: if(to_number(parameterOneIn) > to_number(parameterTwoIn)) then
912: raise currencyNumberException;
913: end if;
914: if(to_number(parameterOneIn) = to_number(parameterTwoIn) and
915: (includeLowerLimitIn = ame_util.booleanFalse or
916: includeUpperLimitIn = ame_util.booleanFalse)) then
917: raise currencyNumberException1;
918: end if;
919: end if;
912: raise currencyNumberException;
913: end if;
914: if(to_number(parameterOneIn) = to_number(parameterTwoIn) and
915: (includeLowerLimitIn = ame_util.booleanFalse or
916: includeUpperLimitIn = ame_util.booleanFalse)) then
917: raise currencyNumberException1;
918: end if;
919: end if;
920: end if;
917: raise currencyNumberException1;
918: end if;
919: end if;
920: end if;
921: if(attributeType = ame_util.stringAttributeType) then
922: stringValueList := stringValueListIn; /* necessary for in/out parameter below */
923: stringCount := stringValueList.count;
924: for i in 1 .. stringCount loop
925: if(instrb(stringValueList(i), ',') > 0) then
925: if(instrb(stringValueList(i), ',') > 0) then
926: raise invalidConditionException;
927: end if;
928: end loop;
929: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
930: for tempCondition in conditionCursor(attributeIdIn => attributeIdIn) loop
931: getStringValueList(conditionIdIn => tempCondition.condition_id,
932: stringValueListOut => tempStringValueList);
933: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
929: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
930: for tempCondition in conditionCursor(attributeIdIn => attributeIdIn) loop
931: getStringValueList(conditionIdIn => tempCondition.condition_id,
932: stringValueListOut => tempStringValueList);
933: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
934: longestStringList2InOut => tempStringValueList)) then
935: raise conditionExistsException;
936: end if;
937: end loop;
952: upper(include_lower_limit) = upper(includeLowerLimitIn)) and
953: ((include_upper_limit is null and includeUpperLimitIn is null) or
954: upper(include_upper_limit) = upper(includeUpperLimitIn)) and
955: processingDate between start_date and
956: nvl(end_date - ame_util.oneSecond, processingDate) ;
957: if tempCount > 0 then
958: raise conditionExistsException;
959: end if;
960: end if;
957: if tempCount > 0 then
958: raise conditionExistsException;
959: end if;
960: end if;
961: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
962: columnNameIn => 'parameter_one',
963: argumentIn => parameterOneIn)) then
964: raise parameterOneLengthException;
965: end if;
962: columnNameIn => 'parameter_one',
963: argumentIn => parameterOneIn)) then
964: raise parameterOneLengthException;
965: end if;
966: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
967: columnNameIn => 'parameter_two',
968: argumentIn => parameterTwoIn)) then
969: raise parameterTwoLengthException;
970: end if;
967: columnNameIn => 'parameter_two',
968: argumentIn => parameterTwoIn)) then
969: raise parameterTwoLengthException;
970: end if;
971: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
972: columnNameIn => 'parameter_three',
973: argumentIn => parameterThreeIn)) then
974: raise parameterThreeLengthException;
975: end if;
972: columnNameIn => 'parameter_three',
973: argumentIn => parameterThreeIn)) then
974: raise parameterThreeLengthException;
975: end if;
976: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
977: columnNameIn => 'include_lower_limit',
978: argumentIn => includeLowerLimitIn)) then
979: raise lowerlimitLengthException;
980: end if;
977: columnNameIn => 'include_lower_limit',
978: argumentIn => includeLowerLimitIn)) then
979: raise lowerlimitLengthException;
980: end if;
981: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_conditions',
982: columnNameIn => 'include_upper_limit',
983: argumentIn => includeUpperLimitIn)) then
984: raise upperlimitLengthException;
985: end if;
988: including the new version, should. This is a failsafe way to check
989: whether previous versions of an already end-dated object had
990: created_by = 1.
991: */
992: currentUserId := ame_util.getCurrentUserId;
993: if(conditionIdIn is null) then
994: createdBy := currentUserId;
995: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
996: /* Use negative condition IDs for developer-seeded conditions. */
991: */
992: currentUserId := ame_util.getCurrentUserId;
993: if(conditionIdIn is null) then
994: createdBy := currentUserId;
995: if (ame_util.getHighestResponsibility = ame_util.developerResponsibility) then
996: /* Use negative condition IDs for developer-seeded conditions. */
997: select count(*)
998: into conditionCount
999: from ame_conditions
998: into conditionCount
999: from ame_conditions
1000: where
1001: processingDate between start_date and
1002: nvl(end_date - ame_util.oneSecond, processingDate) ;
1003: if conditionCount = 0 then
1004: conditionId := -1;
1005: else
1006: select min(condition_id) - 1
1007: into conditionId
1008: from ame_conditions
1009: where
1010: processingDate between start_date and
1011: nvl(end_date - ame_util.oneSecond, processingDate);
1012: if(conditionId > -1) then
1013: conditionId := -1;
1014: end if;
1015: end if;
1022: into tempCount2
1023: from ame_conditions
1024: where
1025: condition_id = conditionId and
1026: created_by = ame_util.seededDataCreatedById;
1027: if(tempCount2 > 0) then
1028: createdBy := ame_util.seededDataCreatedById;
1029: else
1030: createdBy := currentUserId;
1024: where
1025: condition_id = conditionId and
1026: created_by = ame_util.seededDataCreatedById;
1027: if(tempCount2 > 0) then
1028: createdBy := ame_util.seededDataCreatedById;
1029: else
1030: createdBy := currentUserId;
1031: end if;
1032: end if;
1061: processingDate,
1062: currentUserId,
1063: nvl(newStartDateIn, processingDate),
1064: null);
1065: if(attributeType = ame_util.stringAttributeType) then
1066: upperLimit := stringValueList.count;
1067: for i in 1..upperLimit loop
1068: insert into ame_string_values(condition_id,
1069: string_value,
1092: exception
1093: when condKeyLengthException then
1094: rollback;
1095: errorCode := -20001;
1096: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1097: messageNameIn => 'AME_400362_COND_KEY_LONG',
1098: tokenNameOneIn => 'COLUMN_LENGTH',
1099: tokenValueOneIn => 100);
1100: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
1096: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1097: messageNameIn => 'AME_400362_COND_KEY_LONG',
1098: tokenNameOneIn => 'COLUMN_LENGTH',
1099: tokenValueOneIn => 100);
1100: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
1101: routineNamein => 'new',
1102: exceptionNumberIn => errorCode,
1103: exceptionStringIn => errorMessage);
1104: raise_application_error(errorCode,
1107: when invalidConditionException then
1108: rollback;
1109: errorCode := -20001;
1110: errorMessage :=
1111: ame_util.getMessage(applicationShortNameIn => 'PER',
1112: messageNameIn => 'AME_400182_CON_STR_VAL_COMMA');
1113: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1114: routineNameIn => 'new',
1115: exceptionNumberIn => errorCode,
1109: errorCode := -20001;
1110: errorMessage :=
1111: ame_util.getMessage(applicationShortNameIn => 'PER',
1112: messageNameIn => 'AME_400182_CON_STR_VAL_COMMA');
1113: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1114: routineNameIn => 'new',
1115: exceptionNumberIn => errorCode,
1116: exceptionStringIn => errorMessage);
1117: raise_application_error(errorCode,
1120: when conditionExistsException then
1121: rollback;
1122: errorCode := -20001;
1123: errorMessage :=
1124: ame_util.getMessage(applicationShortNameIn => 'PER',
1125: messageNameIn => 'AME_400183_CON_ALRDY_EXISTS');
1126: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1127: routineNameIn => 'new',
1128: exceptionNumberIn => errorCode,
1122: errorCode := -20001;
1123: errorMessage :=
1124: ame_util.getMessage(applicationShortNameIn => 'PER',
1125: messageNameIn => 'AME_400183_CON_ALRDY_EXISTS');
1126: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1127: routineNameIn => 'new',
1128: exceptionNumberIn => errorCode,
1129: exceptionStringIn => errorMessage);
1130: raise_application_error(errorCode,
1132: return(null);
1133: when parameterOneLengthException then
1134: rollback;
1135: errorCode := -20001;
1136: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1137: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1138: tokenNameOneIn => 'COLUMN_LENGTH',
1139: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1140: columnNameIn => 'parameter_one'));
1135: errorCode := -20001;
1136: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1137: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1138: tokenNameOneIn => 'COLUMN_LENGTH',
1139: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1140: columnNameIn => 'parameter_one'));
1141: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1142: routineNameIn => 'new',
1143: exceptionNumberIn => errorCode,
1137: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1138: tokenNameOneIn => 'COLUMN_LENGTH',
1139: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1140: columnNameIn => 'parameter_one'));
1141: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1142: routineNameIn => 'new',
1143: exceptionNumberIn => errorCode,
1144: exceptionStringIn => errorMessage);
1145: raise_application_error(errorCode,
1148: when parameterTwoLengthException then
1149: rollback;
1150: errorCode := -20001;
1151: errorMessage :=
1152: ame_util.getMessage(applicationShortNameIn => 'PER',
1153: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1154: tokenNameOneIn => 'COLUMN_LENGTH',
1155: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1156: columnNameIn => 'parameter_two'));
1151: errorMessage :=
1152: ame_util.getMessage(applicationShortNameIn => 'PER',
1153: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1154: tokenNameOneIn => 'COLUMN_LENGTH',
1155: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1156: columnNameIn => 'parameter_two'));
1157: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1158: routineNameIn => 'new',
1159: exceptionNumberIn => errorCode,
1153: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1154: tokenNameOneIn => 'COLUMN_LENGTH',
1155: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1156: columnNameIn => 'parameter_two'));
1157: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1158: routineNameIn => 'new',
1159: exceptionNumberIn => errorCode,
1160: exceptionStringIn => errorMessage);
1161: raise_application_error(errorCode,
1164: when parameterThreeLengthException then
1165: rollback;
1166: errorCode := -20001;
1167: errorMessage :=
1168: ame_util.getMessage(applicationShortNameIn => 'PER',
1169: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1170: tokenNameOneIn => 'COLUMN_LENGTH',
1171: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1172: columnNameIn => 'parameter_three'));
1167: errorMessage :=
1168: ame_util.getMessage(applicationShortNameIn => 'PER',
1169: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1170: tokenNameOneIn => 'COLUMN_LENGTH',
1171: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1172: columnNameIn => 'parameter_three'));
1173: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1174: routineNameIn => 'new',
1175: exceptionNumberIn => errorCode,
1169: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1170: tokenNameOneIn => 'COLUMN_LENGTH',
1171: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1172: columnNameIn => 'parameter_three'));
1173: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1174: routineNameIn => 'new',
1175: exceptionNumberIn => errorCode,
1176: exceptionStringIn => errorMessage);
1177: raise_application_error(errorCode,
1180: when lowerlimitLengthException then
1181: rollback;
1182: errorCode := -20001;
1183: errorMessage :=
1184: ame_util.getMessage(applicationShortNameIn => 'PER',
1185: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1186: tokenNameOneIn => 'COLUMN_LENGTH',
1187: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1188: columnNameIn => 'include_lower_limit'));
1183: errorMessage :=
1184: ame_util.getMessage(applicationShortNameIn => 'PER',
1185: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1186: tokenNameOneIn => 'COLUMN_LENGTH',
1187: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1188: columnNameIn => 'include_lower_limit'));
1189: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1190: routineNameIn => 'new',
1191: exceptionNumberIn => errorCode,
1185: messageNameIn => 'AME_400184_CON_LWR_LMT_LONG',
1186: tokenNameOneIn => 'COLUMN_LENGTH',
1187: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1188: columnNameIn => 'include_lower_limit'));
1189: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1190: routineNameIn => 'new',
1191: exceptionNumberIn => errorCode,
1192: exceptionStringIn => errorMessage);
1193: raise_application_error(errorCode,
1196: when upperlimitLengthException then
1197: rollback;
1198: errorCode := -20001;
1199: errorMessage :=
1200: ame_util.getMessage(applicationShortNameIn => 'PER',
1201: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1202: tokenNameOneIn => 'COLUMN_LENGTH',
1203: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1204: columnNameIn => 'include_upper_limit'));
1199: errorMessage :=
1200: ame_util.getMessage(applicationShortNameIn => 'PER',
1201: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1202: tokenNameOneIn => 'COLUMN_LENGTH',
1203: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1204: columnNameIn => 'include_upper_limit'));
1205: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1206: routineNameIn => 'new',
1207: exceptionNumberIn => errorCode,
1201: messageNameIn => 'AME_400185_CON_UPP_LMT_LONG',
1202: tokenNameOneIn => 'COLUMN_LENGTH',
1203: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_conditions',
1204: columnNameIn => 'include_upper_limit'));
1205: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1206: routineNameIn => 'new',
1207: exceptionNumberIn => errorCode,
1208: exceptionStringIn => errorMessage);
1209: raise_application_error(errorCode,
1212: when dateException then
1213: rollback;
1214: errorCode := -20001;
1215: errorMessage :=
1216: ame_util.getMessage(applicationShortNameIn => 'PER',
1217: messageNameIn => 'AME_400186_CON_START_LESS_END');
1218: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1219: routineNameIn => 'new',
1220: exceptionNumberIn => errorCode,
1214: errorCode := -20001;
1215: errorMessage :=
1216: ame_util.getMessage(applicationShortNameIn => 'PER',
1217: messageNameIn => 'AME_400186_CON_START_LESS_END');
1218: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1219: routineNameIn => 'new',
1220: exceptionNumberIn => errorCode,
1221: exceptionStringIn => errorMessage);
1222: raise_application_error(errorCode,
1225: when currencyNumberException then
1226: rollback;
1227: errorCode := -20001;
1228: errorMessage :=
1229: ame_util.getMessage(applicationShortNameIn => 'PER',
1230: messageNameIn => 'AME_400187_CON_LWR_LESS_UPP');
1231: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1232: routineNameIn => 'new',
1233: exceptionNumberIn => errorCode,
1227: errorCode := -20001;
1228: errorMessage :=
1229: ame_util.getMessage(applicationShortNameIn => 'PER',
1230: messageNameIn => 'AME_400187_CON_LWR_LESS_UPP');
1231: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1232: routineNameIn => 'new',
1233: exceptionNumberIn => errorCode,
1234: exceptionStringIn => errorMessage);
1235: raise_application_error(errorCode,
1238: when currencyNumberException1 then
1239: rollback;
1240: errorCode := -20001;
1241: errorMessage :=
1242: ame_util.getMessage(applicationShortNameIn => 'PER',
1243: messageNameIn => 'AME_400188_CON_LMT_VAL_YES');
1244: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1245: routineNameIn => 'new',
1246: exceptionNumberIn => errorCode,
1240: errorCode := -20001;
1241: errorMessage :=
1242: ame_util.getMessage(applicationShortNameIn => 'PER',
1243: messageNameIn => 'AME_400188_CON_LMT_VAL_YES');
1244: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1245: routineNameIn => 'new',
1246: exceptionNumberIn => errorCode,
1247: exceptionStringIn => errorMessage);
1248: raise_application_error(errorCode,
1249: errorMessage);
1250: return(null);
1251: when others then
1252: rollback;
1253: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1254: routineNameIn => 'new',
1255: exceptionNumberIn => sqlcode,
1256: exceptionStringIn => '(condition ID ' ||
1257: conditionIdIn||
1265: processingDateIn in date default null) return boolean as
1266: createdBy integer;
1267: currentUserId integer;
1268: errorCode integer;
1269: errorMessage ame_util.longestStringType;
1270: processingDate date;
1271: tempCount integer;
1272: usageExistsException exception;
1273: useCount integer;
1283: where
1284: condition_id = conditionIdIn and
1285: rule_id = ruleIdIn and
1286: ((sysdate between start_date and
1287: nvl(end_date - ame_util.oneSecond, sysdate)) or
1288: (sysdate < start_date and
1289: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1290: if(useCount > 0) then
1291: raise usageExistsException;
1285: rule_id = ruleIdIn and
1286: ((sysdate between start_date and
1287: nvl(end_date - ame_util.oneSecond, sysdate)) or
1288: (sysdate < start_date and
1289: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1290: if(useCount > 0) then
1291: raise usageExistsException;
1292: end if;
1293: currentUserId := ame_util.getCurrentUserId;
1289: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
1290: if(useCount > 0) then
1291: raise usageExistsException;
1292: end if;
1293: currentUserId := ame_util.getCurrentUserId;
1294: select count(*)
1295: into tempCount
1296: from ame_condition_usages
1297: where
1296: from ame_condition_usages
1297: where
1298: condition_id = conditionIdIn and
1299: rule_id = ruleIdIn and
1300: created_by = ame_util.seededDataCreatedById;
1301: if(tempCount > 0) then
1302: createdBy := ame_util.seededDataCreatedById;
1303: else
1304: createdBy := currentUserId;
1298: condition_id = conditionIdIn and
1299: rule_id = ruleIdIn and
1300: created_by = ame_util.seededDataCreatedById;
1301: if(tempCount > 0) then
1302: createdBy := ame_util.seededDataCreatedById;
1303: else
1304: createdBy := currentUserId;
1305: end if;
1306: insert into ame_condition_usages(rule_id,
1327: when usageExistsException then
1328: rollback;
1329: errorCode := -20001;
1330: errorMessage :=
1331: ame_util.getMessage(applicationShortNameIn => 'PER',
1332: messageNameIn => 'AME_400189_CON_RULE_USES');
1333: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1334: routineNameIn => 'newConditionUsage',
1335: exceptionNumberIn => errorCode,
1329: errorCode := -20001;
1330: errorMessage :=
1331: ame_util.getMessage(applicationShortNameIn => 'PER',
1332: messageNameIn => 'AME_400189_CON_RULE_USES');
1333: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1334: routineNameIn => 'newConditionUsage',
1335: exceptionNumberIn => errorCode,
1336: exceptionStringIn => errorMessage);
1337: raise_application_error(errorCode,
1338: errorMessage);
1339: return(false);
1340: when others then
1341: rollback;
1342: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1343: routineNameIn => 'newConditionUsage',
1344: exceptionNumberIn => sqlcode,
1345: exceptionStringIn => '(condition ID ' ||
1346: conditionIdIn||
1353: valueIn in varchar2,
1354: processingDateIn in date default null) return boolean as
1355: currentUserId integer;
1356: errorCode integer;
1357: errorMessage ame_util.longestStringType;
1358: stringValueLengthException exception;
1359: useCount integer;
1360: processingDate date;
1361: begin
1371: condition_id = conditionIdIn and
1372: /* string values are case sensitive */
1373: string_value = valueIn and
1374: sysdate between start_date and
1375: nvl(end_date - ame_util.oneSecond, sysdate) ;
1376: if(useCount > 0) then
1377: return(false);
1378: end if;
1379: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_string_values',
1375: nvl(end_date - ame_util.oneSecond, sysdate) ;
1376: if(useCount > 0) then
1377: return(false);
1378: end if;
1379: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_string_values',
1380: columnNameIn => 'string_value',
1381: argumentIn => valueIn)) then
1382: raise stringValueLengthException;
1383: end if;
1380: columnNameIn => 'string_value',
1381: argumentIn => valueIn)) then
1382: raise stringValueLengthException;
1383: end if;
1384: currentUserId := ame_util.getCurrentUserId;
1385: insert into ame_string_values(condition_id,
1386: string_value,
1387: created_by,
1388: creation_date,
1404: exception
1405: when stringValueLengthException then
1406: rollback;
1407: errorCode := -20001;
1408: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1409: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1410: tokenNameOneIn => 'COLUMN_LENGTH',
1411: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1412: columnNameIn => 'string_value'));
1407: errorCode := -20001;
1408: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1409: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1410: tokenNameOneIn => 'COLUMN_LENGTH',
1411: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1412: columnNameIn => 'string_value'));
1413: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1414: routineNameIn => 'newStringValue',
1415: exceptionNumberIn => errorCode,
1409: messageNameIn => 'AME_400190_CON_STR_VAL_LONG',
1410: tokenNameOneIn => 'COLUMN_LENGTH',
1411: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_string_values',
1412: columnNameIn => 'string_value'));
1413: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1414: routineNameIn => 'newStringValue',
1415: exceptionNumberIn => errorCode,
1416: exceptionStringIn => errorMessage);
1417: raise_application_error(errorCode,
1418: errorMessage);
1419: return(false);
1420: when others then
1421: rollback;
1422: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1423: routineNameIn => 'newStringValue',
1424: exceptionNumberIn => sqlcode,
1425: exceptionStringIn => '(condition ID ' ||
1426: conditionIdIn||
1429: raise;
1430: return(false);
1431: end newStringValue;
1432: procedure change(conditionIdIn in integer,
1433: stringValuesIn in ame_util.longestStringList default ame_util.emptyLongestStringList,
1434: typeIn in varchar2 default null,
1435: attributeIdIn in integer default null,
1436: parameterOneIn in varchar2 default null,
1437: parameterTwoIn in varchar2 default null,
1462: (include_lower_limit = includeLowerLimitIn)) and
1463: ((include_upper_limit is null and includeUpperLimitIn is null) or
1464: (include_upper_limit = includeUpperLimitIn)) and
1465: sysdate between start_date and
1466: nvl(end_date - ame_util.oneSecond, sysdate) ;
1467: cursor startDateCursor is
1468: select start_date
1469: from ame_conditions
1470: where
1469: from ame_conditions
1470: where
1471: condition_id = conditionIdIn and
1472: sysdate between start_date and
1473: nvl(end_date - ame_util.oneSecond, sysdate)
1474: for update;
1475: attributeId integer;
1476: attributeType ame_attributes.attribute_type%type;
1477: conditionId integer;
1480: conditionType ame_conditions.condition_type%type;
1481: currentUserId integer;
1482: endDate date;
1483: errorCode integer;
1484: errorMessage ame_util.longestStringType;
1485: newStartDate date;
1486: objectVersionNoDataException exception;
1487: startDate date;
1488: stringValueList ame_util.longestStringList;
1484: errorMessage ame_util.longestStringType;
1485: newStartDate date;
1486: objectVersionNoDataException exception;
1487: startDate date;
1488: stringValueList ame_util.longestStringList;
1489: tempCount integer;
1490: tempIndex integer;
1491: tempStringValue ame_string_values.string_value%type;
1492: tempStringValueList ame_util.longestStringList;
1488: stringValueList ame_util.longestStringList;
1489: tempCount integer;
1490: tempIndex integer;
1491: tempStringValue ame_string_values.string_value%type;
1492: tempStringValueList ame_util.longestStringList;
1493: processingDate date;
1494: begin
1495: if processingDateIn is null then
1496: processingDate := sysdate;
1524: (include_lower_limit = includeLowerLimitIn)) and
1525: ((include_upper_limit is null and includeUpperLimitIn is null) or
1526: (include_upper_limit = includeUpperLimitIn)) and
1527: sysdate between start_date and
1528: nvl(end_date - ame_util.oneSecond, sysdate) ;
1529: if(tempCount > 0) then
1530: if(conditionType = ame_util.listModConditionType) then
1531: raise conditionsExistsException;
1532: else
1526: (include_upper_limit = includeUpperLimitIn)) and
1527: sysdate between start_date and
1528: nvl(end_date - ame_util.oneSecond, sysdate) ;
1529: if(tempCount > 0) then
1530: if(conditionType = ame_util.listModConditionType) then
1531: raise conditionsExistsException;
1532: else
1533: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1534: if(attributeType = ame_util.stringAttributeType) then
1530: if(conditionType = ame_util.listModConditionType) then
1531: raise conditionsExistsException;
1532: else
1533: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1534: if(attributeType = ame_util.stringAttributeType) then
1535: stringValueList := stringValuesIn;
1536: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
1537: for tempCondition in conditionCursor(typeIn => typeIn,
1538: attributeIdIn => attributeIdIn,
1532: else
1533: attributeType := getAttributeType(conditionIdIn => conditionIdIn);
1534: if(attributeType = ame_util.stringAttributeType) then
1535: stringValueList := stringValuesIn;
1536: ame_util.sortLongestStringListInPlace(longestStringListInOut => stringValueList);
1537: for tempCondition in conditionCursor(typeIn => typeIn,
1538: attributeIdIn => attributeIdIn,
1539: parameterOneIn => parameterOneIn,
1540: parameterTwoIn => parameterTwoIn,
1542: includeLowerLimitIn => includeLowerLimitIn,
1543: includeUpperLimitIn => includeUpperLimitIn) loop
1544: getStringValueList(conditionIdIn => tempCondition.condition_id,
1545: stringValueListOut => tempStringValueList);
1546: if(ame_util.longestStringListsMatch(longestStringList1InOut => stringValueList,
1547: longestStringList2InOut => tempStringValueList)) then
1548: raise conditionsExistsException;
1549: end if;
1550: end loop;
1557: attributeId := getAttributeId(conditionIdIn => conditionIdIn);
1558: else
1559: attributeId := attributeIdIn;
1560: end if;
1561: currentUserId := ame_util.getCurrentUserId;
1562: if versionStartDateIn = startDate then
1563: endDate := processingDate ;
1564: newStartDate := processingDate;
1565: update ame_conditions
1570: end_date = endDate
1571: where
1572: condition_id = conditionIdIn and
1573: sysdate between start_date and
1574: nvl(end_date - ame_util.oneSecond, sysdate) ;
1575: if conditionType <> ame_util.listModConditionType then
1576: if(attributeType = ame_util.stringAttributeType) then
1577: delete from ame_string_values where condition_id = conditionIdIn;
1578: end if;
1571: where
1572: condition_id = conditionIdIn and
1573: sysdate between start_date and
1574: nvl(end_date - ame_util.oneSecond, sysdate) ;
1575: if conditionType <> ame_util.listModConditionType then
1576: if(attributeType = ame_util.stringAttributeType) then
1577: delete from ame_string_values where condition_id = conditionIdIn;
1578: end if;
1579: end if;
1572: condition_id = conditionIdIn and
1573: sysdate between start_date and
1574: nvl(end_date - ame_util.oneSecond, sysdate) ;
1575: if conditionType <> ame_util.listModConditionType then
1576: if(attributeType = ame_util.stringAttributeType) then
1577: delete from ame_string_values where condition_id = conditionIdIn;
1578: end if;
1579: end if;
1580: /* (The new function does a commit.) */
1591: newStartDateIn => newStartDate,
1592: conditionIdIn => conditionIdIn);
1593: else
1594: close startDateCursor;
1595: raise ame_util.objectVersionException;
1596: end if;
1597: close startDateCursor;
1598: exception
1599: when ame_util.objectVersionException then
1595: raise ame_util.objectVersionException;
1596: end if;
1597: close startDateCursor;
1598: exception
1599: when ame_util.objectVersionException then
1600: rollback;
1601: if(startDateCursor%isOpen) then
1602: close startDateCursor;
1603: end if;
1602: close startDateCursor;
1603: end if;
1604: errorCode := -20001;
1605: errorMessage :=
1606: ame_util.getMessage(applicationShortNameIn => 'PER',
1607: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1608: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1609: routineNameIn => 'change',
1610: exceptionNumberIn => errorCode,
1604: errorCode := -20001;
1605: errorMessage :=
1606: ame_util.getMessage(applicationShortNameIn => 'PER',
1607: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1608: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1609: routineNameIn => 'change',
1610: exceptionNumberIn => errorCode,
1611: exceptionStringIn => errorMessage);
1612: raise_application_error(errorCode,
1617: close startDateCursor;
1618: end if;
1619: errorCode := -20001;
1620: errorMessage :=
1621: ame_util.getMessage(applicationShortNameIn => 'PER',
1622: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1623: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1624: routineNameIn => 'change',
1625: exceptionNumberIn => errorCode,
1619: errorCode := -20001;
1620: errorMessage :=
1621: ame_util.getMessage(applicationShortNameIn => 'PER',
1622: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1623: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1624: routineNameIn => 'change',
1625: exceptionNumberIn => errorCode,
1626: exceptionStringIn => errorMessage);
1627: raise_application_error(errorCode,
1629: when conditionsExistsException then
1630: rollback;
1631: errorCode := -20001;
1632: errorMessage :=
1633: ame_util.getMessage(applicationShortNameIn => 'PER',
1634: messageNameIn => 'AME_400191_CON_EXISTS_NEW');
1635: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1636: routineNameIn => 'change',
1637: exceptionNumberIn => errorCode,
1631: errorCode := -20001;
1632: errorMessage :=
1633: ame_util.getMessage(applicationShortNameIn => 'PER',
1634: messageNameIn => 'AME_400191_CON_EXISTS_NEW');
1635: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1636: routineNameIn => 'change',
1637: exceptionNumberIn => errorCode,
1638: exceptionStringIn => errorMessage);
1639: raise_application_error(errorCode,
1642: rollback;
1643: if(startDateCursor%isOpen) then
1644: close startDateCursor;
1645: end if;
1646: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1647: routineNameIn => 'change',
1648: exceptionNumberIn => sqlcode,
1649: exceptionStringIn => '(condition ID ' ||
1650: conditionIdIn||
1683: from ame_conditions
1684: where
1685: condition_id = conditionIdIn and
1686: sysdate between start_date and
1687: nvl(end_date - ame_util.oneSecond, sysdate) ;
1688: exception
1689: when others then
1690: rollback;
1691: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1687: nvl(end_date - ame_util.oneSecond, sysdate) ;
1688: exception
1689: when others then
1690: rollback;
1691: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1692: routineNameIn => 'getAllProperties',
1693: exceptionNumberIn => sqlcode,
1694: exceptionStringIn => '(condition ID ' ||
1695: conditionIdIn||
1706: raise;
1707: end getAllProperties;
1708: procedure getAuthPreConditions(applicationIdIn in integer,
1709: itemClassIdIn in integer,
1710: conditionIdsOut out nocopy ame_util.stringList,
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1707: end getAllProperties;
1708: procedure getAuthPreConditions(applicationIdIn in integer,
1709: itemClassIdIn in integer,
1710: conditionIdsOut out nocopy ame_util.stringList,
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1715: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1708: procedure getAuthPreConditions(applicationIdIn in integer,
1709: itemClassIdIn in integer,
1710: conditionIdsOut out nocopy ame_util.stringList,
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1715: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1716: cursor conditionCursor(applicationIdIn in integer,
1709: itemClassIdIn in integer,
1710: conditionIdsOut out nocopy ame_util.stringList,
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1715: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1716: cursor conditionCursor(applicationIdIn in integer,
1717: itemClassIdIn in integer) is
1710: conditionIdsOut out nocopy ame_util.stringList,
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1715: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1716: cursor conditionCursor(applicationIdIn in integer,
1717: itemClassIdIn in integer) is
1718: select
1711: conditionTypesOut out nocopy ame_util.stringList,
1712: attributeIdsOut out nocopy ame_util.stringList,
1713: attributeNamesOut out nocopy ame_util.stringList,
1714: attributeTypesOut out nocopy ame_util.stringList,
1715: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1716: cursor conditionCursor(applicationIdIn in integer,
1717: itemClassIdIn in integer) is
1718: select
1719: ame_conditions.condition_id id,
1731: ame_item_class_usages.item_class_id = itemClassIdIn and
1732: ame_conditions.attribute_id = ame_attribute_usages.attribute_id and
1733: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1734: ame_attributes.item_class_id = itemClassIdIn and
1735: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1736: sysdate between ame_conditions.start_date and
1737: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1738: sysdate between ame_attributes.start_date and
1739: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1733: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1734: ame_attributes.item_class_id = itemClassIdIn and
1735: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1736: sysdate between ame_conditions.start_date and
1737: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1738: sysdate between ame_attributes.start_date and
1739: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1740: sysdate between ame_attribute_usages.start_date and
1741: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1735: ame_conditions.condition_type in (ame_util.ordinaryConditionType,ame_util.exceptionConditionType) and
1736: sysdate between ame_conditions.start_date and
1737: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1738: sysdate between ame_attributes.start_date and
1739: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1740: sysdate between ame_attribute_usages.start_date and
1741: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1742: sysdate between ame_item_class_usages.start_date and
1743: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1737: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1738: sysdate between ame_attributes.start_date and
1739: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1740: sysdate between ame_attribute_usages.start_date and
1741: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1742: sysdate between ame_item_class_usages.start_date and
1743: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1744: order by ame_conditions.condition_type,
1745: ame_attributes.attribute_type,
1739: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1740: sysdate between ame_attribute_usages.start_date and
1741: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
1742: sysdate between ame_item_class_usages.start_date and
1743: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
1744: order by ame_conditions.condition_type,
1745: ame_attributes.attribute_type,
1746: ame_attributes.name;
1747: tempOutputIndex integer;
1775: when others then
1776: if conditionCursor%isopen then
1777: close conditionCursor;
1778: end if;
1779: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1780: routineNameIn => 'getAuthPreConditions',
1781: exceptionNumberIn => sqlcode,
1782: exceptionStringIn => '(application ID ' ||
1783: applicationIdIn||
1782: exceptionStringIn => '(application ID ' ||
1783: applicationIdIn||
1784: ') ' ||
1785: sqlerrm);
1786: conditionIdsOut := ame_util.emptyStringList;
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1783: applicationIdIn||
1784: ') ' ||
1785: sqlerrm);
1786: conditionIdsOut := ame_util.emptyStringList;
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1784: ') ' ||
1785: sqlerrm);
1786: conditionIdsOut := ame_util.emptyStringList;
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1792: raise;
1785: sqlerrm);
1786: conditionIdsOut := ame_util.emptyStringList;
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1792: raise;
1793: end getAuthPreConditions;
1786: conditionIdsOut := ame_util.emptyStringList;
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1792: raise;
1793: end getAuthPreConditions;
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1787: conditionTypesOut := ame_util.emptyStringList;
1788: attributeIdsOut := ame_util.emptyStringList;
1789: attributeNamesOut := ame_util.emptyStringList;
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1792: raise;
1793: end getAuthPreConditions;
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1795: conditionTypeIn in varchar2,
1790: attributeTypesOut := ame_util.emptyStringList;
1791: conditionDescriptionsOut := ame_util.emptyLongStringList;
1792: raise;
1793: end getAuthPreConditions;
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1795: conditionTypeIn in varchar2,
1796: lineItemIn in varchar2 default ame_util.booleanFalse,
1797: conditionIdsOut out nocopy ame_util.stringList,
1798: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1792: raise;
1793: end getAuthPreConditions;
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1795: conditionTypeIn in varchar2,
1796: lineItemIn in varchar2 default ame_util.booleanFalse,
1797: conditionIdsOut out nocopy ame_util.stringList,
1798: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1799: cursor conditionCursor(attributeIdIn in integer,
1800: conditionTypeIn in varchar2,
1793: end getAuthPreConditions;
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1795: conditionTypeIn in varchar2,
1796: lineItemIn in varchar2 default ame_util.booleanFalse,
1797: conditionIdsOut out nocopy ame_util.stringList,
1798: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1799: cursor conditionCursor(attributeIdIn in integer,
1800: conditionTypeIn in varchar2,
1801: lineItemIn in varchar2) is
1794: procedure getAttributesConditions(attributeIdsIn in ame_util.idList,
1795: conditionTypeIn in varchar2,
1796: lineItemIn in varchar2 default ame_util.booleanFalse,
1797: conditionIdsOut out nocopy ame_util.stringList,
1798: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1799: cursor conditionCursor(attributeIdIn in integer,
1800: conditionTypeIn in varchar2,
1801: lineItemIn in varchar2) is
1802: select
1805: from ame_conditions,
1806: ame_attributes
1807: where
1808: ame_conditions.attribute_id = ame_attributes.attribute_id and
1809: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1810: ame_attributes.attribute_id = attributeIdIn and
1811: condition_type = conditionTypeIn and
1812: sysdate between ame_conditions.start_date and
1813: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1809: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1810: ame_attributes.attribute_id = attributeIdIn and
1811: condition_type = conditionTypeIn and
1812: sysdate between ame_conditions.start_date and
1813: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1814: sysdate between ame_attributes.start_date and
1815: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1816: order by description;
1817: tempOutputIndex integer;
1811: condition_type = conditionTypeIn and
1812: sysdate between ame_conditions.start_date and
1813: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1814: sysdate between ame_attributes.start_date and
1815: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1816: order by description;
1817: tempOutputIndex integer;
1818: upperLimit integer;
1819: begin
1831: end loop;
1832: exception
1833: when others then
1834: rollback;
1835: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1836: routineNameIn => 'getAttributesConditions',
1837: exceptionNumberIn => sqlcode,
1838: exceptionStringIn => sqlerrm);
1839: conditionIdsOut := ame_util.emptyStringList;
1835: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1836: routineNameIn => 'getAttributesConditions',
1837: exceptionNumberIn => sqlcode,
1838: exceptionStringIn => sqlerrm);
1839: conditionIdsOut := ame_util.emptyStringList;
1840: conditionDescriptionsOut := ame_util.emptyLongStringList;
1841: raise;
1842: end getAttributesConditions;
1843: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1836: routineNameIn => 'getAttributesConditions',
1837: exceptionNumberIn => sqlcode,
1838: exceptionStringIn => sqlerrm);
1839: conditionIdsOut := ame_util.emptyStringList;
1840: conditionDescriptionsOut := ame_util.emptyLongStringList;
1841: raise;
1842: end getAttributesConditions;
1843: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1844: conditionTypeIn in varchar2,
1839: conditionIdsOut := ame_util.emptyStringList;
1840: conditionDescriptionsOut := ame_util.emptyLongStringList;
1841: raise;
1842: end getAttributesConditions;
1843: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1844: conditionTypeIn in varchar2,
1845: itemClassIdIn in integer,
1846: ruleIdIn in integer,
1847: conditionIdsOut out nocopy ame_util.stringList,
1843: procedure getAttributesConditions1(attributeIdsIn in ame_util.idList,
1844: conditionTypeIn in varchar2,
1845: itemClassIdIn in integer,
1846: ruleIdIn in integer,
1847: conditionIdsOut out nocopy ame_util.stringList,
1848: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1849: cursor conditionCursor(attributeIdIn in integer,
1850: conditionTypeIn in varchar2,
1851: itemClassIdIn in integer) is
1844: conditionTypeIn in varchar2,
1845: itemClassIdIn in integer,
1846: ruleIdIn in integer,
1847: conditionIdsOut out nocopy ame_util.stringList,
1848: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1849: cursor conditionCursor(attributeIdIn in integer,
1850: conditionTypeIn in varchar2,
1851: itemClassIdIn in integer) is
1852: select
1859: ame_attributes.attribute_id = attributeIdIn and
1860: condition_type = conditionTypeIn and
1861: ame_attributes.item_class_id = itemClassIdIn and
1862: sysdate between ame_conditions.start_date and
1863: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1864: sysdate between ame_attributes.start_date and
1865: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1866: order by description;
1867: tempOutputIndex integer;
1861: ame_attributes.item_class_id = itemClassIdIn and
1862: sysdate between ame_conditions.start_date and
1863: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1864: sysdate between ame_attributes.start_date and
1865: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1866: order by description;
1867: tempOutputIndex integer;
1868: upperLimit integer;
1869: begin
1884: end loop;
1885: exception
1886: when others then
1887: rollback;
1888: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1889: routineNameIn => 'getAttributesConditions1',
1890: exceptionNumberIn => sqlcode,
1891: exceptionStringIn => sqlerrm);
1892: conditionIdsOut := ame_util.emptyStringList;
1888: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1889: routineNameIn => 'getAttributesConditions1',
1890: exceptionNumberIn => sqlcode,
1891: exceptionStringIn => sqlerrm);
1892: conditionIdsOut := ame_util.emptyStringList;
1893: conditionDescriptionsOut := ame_util.emptyLongStringList;
1894: raise;
1895: end getAttributesConditions1;
1896: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1889: routineNameIn => 'getAttributesConditions1',
1890: exceptionNumberIn => sqlcode,
1891: exceptionStringIn => sqlerrm);
1892: conditionIdsOut := ame_util.emptyStringList;
1893: conditionDescriptionsOut := ame_util.emptyLongStringList;
1894: raise;
1895: end getAttributesConditions1;
1896: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1897: conditionTypeIn in varchar2,
1892: conditionIdsOut := ame_util.emptyStringList;
1893: conditionDescriptionsOut := ame_util.emptyLongStringList;
1894: raise;
1895: end getAttributesConditions1;
1896: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1897: conditionTypeIn in varchar2,
1898: itemClassIdIn in integer,
1899: lineItemIn in varchar2 default ame_util.booleanFalse,
1900: conditionIdsOut out nocopy ame_util.stringList,
1895: end getAttributesConditions1;
1896: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1897: conditionTypeIn in varchar2,
1898: itemClassIdIn in integer,
1899: lineItemIn in varchar2 default ame_util.booleanFalse,
1900: conditionIdsOut out nocopy ame_util.stringList,
1901: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1902: cursor conditionCursor(attributeIdIn in integer,
1903: conditionTypeIn in varchar2,
1896: procedure getAttributesConditions2(attributeIdsIn in ame_util.idList,
1897: conditionTypeIn in varchar2,
1898: itemClassIdIn in integer,
1899: lineItemIn in varchar2 default ame_util.booleanFalse,
1900: conditionIdsOut out nocopy ame_util.stringList,
1901: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1902: cursor conditionCursor(attributeIdIn in integer,
1903: conditionTypeIn in varchar2,
1904: lineItemIn in varchar2,
1897: conditionTypeIn in varchar2,
1898: itemClassIdIn in integer,
1899: lineItemIn in varchar2 default ame_util.booleanFalse,
1900: conditionIdsOut out nocopy ame_util.stringList,
1901: conditionDescriptionsOut out nocopy ame_util.longStringList) as
1902: cursor conditionCursor(attributeIdIn in integer,
1903: conditionTypeIn in varchar2,
1904: lineItemIn in varchar2,
1905: itemClassIdIn in integer) is
1909: from ame_conditions,
1910: ame_attributes
1911: where
1912: ame_conditions.attribute_id = ame_attributes.attribute_id and
1913: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
1914: ame_attributes.attribute_id = attributeIdIn and
1915: ame_attributes.item_class_id = itemClassIdIn and
1916: condition_type = conditionTypeIn and
1917: sysdate between ame_conditions.start_date and
1914: ame_attributes.attribute_id = attributeIdIn and
1915: ame_attributes.item_class_id = itemClassIdIn and
1916: condition_type = conditionTypeIn and
1917: sysdate between ame_conditions.start_date and
1918: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1919: sysdate between ame_attributes.start_date and
1920: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1921: order by description;
1922: tempOutputIndex integer;
1916: condition_type = conditionTypeIn and
1917: sysdate between ame_conditions.start_date and
1918: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1919: sysdate between ame_attributes.start_date and
1920: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
1921: order by description;
1922: tempOutputIndex integer;
1923: upperLimit integer;
1924: begin
1937: end loop;
1938: exception
1939: when others then
1940: rollback;
1941: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1942: routineNameIn => 'getAttributesConditions2',
1943: exceptionNumberIn => sqlcode,
1944: exceptionStringIn => sqlerrm);
1945: conditionIdsOut := ame_util.emptyStringList;
1941: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1942: routineNameIn => 'getAttributesConditions2',
1943: exceptionNumberIn => sqlcode,
1944: exceptionStringIn => sqlerrm);
1945: conditionIdsOut := ame_util.emptyStringList;
1946: conditionDescriptionsOut := ame_util.emptyLongStringList;
1947: raise;
1948: end getAttributesConditions2;
1949: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1942: routineNameIn => 'getAttributesConditions2',
1943: exceptionNumberIn => sqlcode,
1944: exceptionStringIn => sqlerrm);
1945: conditionIdsOut := ame_util.emptyStringList;
1946: conditionDescriptionsOut := ame_util.emptyLongStringList;
1947: raise;
1948: end getAttributesConditions2;
1949: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1950: applicationIdIn in integer,
1947: raise;
1948: end getAttributesConditions2;
1949: procedure getApplicationsUsingCondition(conditionIdIn in integer,
1950: applicationIdIn in integer,
1951: applicationNamesOut out nocopy ame_util.stringList) as
1952: upperLimit integer;
1953: cursor getApplicationsCursor(conditionIdIn in integer,
1954: applicationIdIn in integer) is
1955: select distinct ame_calling_apps.application_name
1964: ame_rule_usages.item_id = ame_calling_apps.application_id and
1965: ame_rule_usages.item_id <> applicationIdIn and
1966: ame_condition_usages.condition_id = conditionIdIn and
1967: ((sysdate between ame_rules.start_date and
1968: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
1969: (sysdate < ame_rules.start_date and
1970: ame_rules.start_date < nvl(ame_rules.end_date,
1971: ame_rules.start_date + ame_util.oneSecond))) and
1972: ((sysdate between ame_rule_usages.start_date and
1967: ((sysdate between ame_rules.start_date and
1968: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
1969: (sysdate < ame_rules.start_date and
1970: ame_rules.start_date < nvl(ame_rules.end_date,
1971: ame_rules.start_date + ame_util.oneSecond))) and
1972: ((sysdate between ame_rule_usages.start_date and
1973: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1974: (sysdate < ame_rule_usages.start_date and
1975: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1969: (sysdate < ame_rules.start_date and
1970: ame_rules.start_date < nvl(ame_rules.end_date,
1971: ame_rules.start_date + ame_util.oneSecond))) and
1972: ((sysdate between ame_rule_usages.start_date and
1973: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1974: (sysdate < ame_rule_usages.start_date and
1975: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1976: ame_rule_usages.start_date + ame_util.oneSecond))) and
1977: ((sysdate between ame_condition_usages.start_date and
1972: ((sysdate between ame_rule_usages.start_date and
1973: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1974: (sysdate < ame_rule_usages.start_date and
1975: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1976: ame_rule_usages.start_date + ame_util.oneSecond))) and
1977: ((sysdate between ame_condition_usages.start_date and
1978: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1979: (sysdate < ame_condition_usages.start_date and
1980: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1974: (sysdate < ame_rule_usages.start_date and
1975: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1976: ame_rule_usages.start_date + ame_util.oneSecond))) and
1977: ((sysdate between ame_condition_usages.start_date and
1978: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1979: (sysdate < ame_condition_usages.start_date and
1980: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1981: ame_condition_usages.start_date + ame_util.oneSecond))) and
1982: sysdate between ame_calling_apps.start_date and
1977: ((sysdate between ame_condition_usages.start_date and
1978: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1979: (sysdate < ame_condition_usages.start_date and
1980: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1981: ame_condition_usages.start_date + ame_util.oneSecond))) and
1982: sysdate between ame_calling_apps.start_date and
1983: nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
1984: order by ame_calling_apps.application_name;
1985: tempIndex integer;
1979: (sysdate < ame_condition_usages.start_date and
1980: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1981: ame_condition_usages.start_date + ame_util.oneSecond))) and
1982: sysdate between ame_calling_apps.start_date and
1983: nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
1984: order by ame_calling_apps.application_name;
1985: tempIndex integer;
1986: begin
1987: tempIndex := 1;
1992: end loop;
1993: exception
1994: when others then
1995: rollback;
1996: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
1997: routineNameIn => 'getApplicationsUsingCondition',
1998: exceptionNumberIn => sqlcode,
1999: exceptionStringIn => '(condition ID ' ||
2000: conditionIdIn||
1999: exceptionStringIn => '(condition ID ' ||
2000: conditionIdIn||
2001: ') ' ||
2002: sqlerrm);
2003: applicationNamesOut := ame_util.emptyStringList;
2004: raise;
2005: end getApplicationsUsingCondition;
2006: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2007: descriptionsOut out nocopy ame_util.longStringList) as
2002: sqlerrm);
2003: applicationNamesOut := ame_util.emptyStringList;
2004: raise;
2005: end getApplicationsUsingCondition;
2006: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2007: descriptionsOut out nocopy ame_util.longStringList) as
2008: upperLimit integer;
2009: begin
2010: upperLimit := conditionIdsIn.count;
2003: applicationNamesOut := ame_util.emptyStringList;
2004: raise;
2005: end getApplicationsUsingCondition;
2006: procedure getDescriptions(conditionIdsIn in ame_util.idList,
2007: descriptionsOut out nocopy ame_util.longStringList) as
2008: upperLimit integer;
2009: begin
2010: upperLimit := conditionIdsIn.count;
2011: for tempIndex in 1..upperLimit loop
2013: end loop;
2014: exception
2015: when others then
2016: rollback;
2017: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2018: routineNameIn => 'getDescriptions',
2019: exceptionNumberIn => sqlcode,
2020: exceptionStringIn => sqlerrm);
2021: descriptionsOut := ame_util.emptyLongStringList;
2017: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2018: routineNameIn => 'getDescriptions',
2019: exceptionNumberIn => sqlcode,
2020: exceptionStringIn => sqlerrm);
2021: descriptionsOut := ame_util.emptyLongStringList;
2022: raise;
2023: end getDescriptions;
2024: procedure getDetailUrls(applicationIdIn in integer,
2025: conditionIdsIn in ame_util.idList,
2021: descriptionsOut := ame_util.emptyLongStringList;
2022: raise;
2023: end getDescriptions;
2024: procedure getDetailUrls(applicationIdIn in integer,
2025: conditionIdsIn in ame_util.idList,
2026: detailUrlsOut out nocopy ame_util.longStringList) as
2027: conditionIdCount integer;
2028: begin
2029: conditionIdCount := conditionIdsIn.count;
2022: raise;
2023: end getDescriptions;
2024: procedure getDetailUrls(applicationIdIn in integer,
2025: conditionIdsIn in ame_util.idList,
2026: detailUrlsOut out nocopy ame_util.longStringList) as
2027: conditionIdCount integer;
2028: begin
2029: conditionIdCount := conditionIdsIn.count;
2030: for i in 1..conditionIdCount loop
2027: conditionIdCount integer;
2028: begin
2029: conditionIdCount := conditionIdsIn.count;
2030: for i in 1..conditionIdCount loop
2031: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2032: detailUrlsOut(i) := null;
2033: else
2034: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2035: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2030: for i in 1..conditionIdCount loop
2031: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2032: detailUrlsOut(i) := null;
2033: else
2034: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2035: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2036: 'ame_conditions_ui.getDetails?conditionIdIn=' ||
2037: conditionIdsIn(i) ||
2038: '&applicationIdIn=' ||
2031: if ame_condition_pkg.getConditionType(conditionIdIn => conditionIdsIn(i)) = ame_util.listModConditionType then
2032: detailUrlsOut(i) := null;
2033: else
2034: if ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdsIn(i)) = ame_util.stringAttributeType then
2035: detailUrlsOut(i) := (ame_util.getPlsqlDadPath ||
2036: 'ame_conditions_ui.getDetails?conditionIdIn=' ||
2037: conditionIdsIn(i) ||
2038: '&applicationIdIn=' ||
2039: applicationIdIn);
2044: end loop;
2045: exception
2046: when others then
2047: rollback;
2048: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2049: routineNamein => 'getDetailUrls',
2050: exceptionNumberIn => sqlcode,
2051: exceptionStringIn => sqlerrm);
2052: detailUrlsOut := ame_util.emptyLongStringList;
2048: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2049: routineNamein => 'getDetailUrls',
2050: exceptionNumberIn => sqlcode,
2051: exceptionStringIn => sqlerrm);
2052: detailUrlsOut := ame_util.emptyLongStringList;
2053: raise;
2054: end getDetailUrls;
2055: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2056: parameterOneOut out nocopy ame_util.stringList,
2051: exceptionStringIn => sqlerrm);
2052: detailUrlsOut := ame_util.emptyLongStringList;
2053: raise;
2054: end getDetailUrls;
2055: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2056: parameterOneOut out nocopy ame_util.stringList,
2057: parameterTwoOut out nocopy ame_util.stringList) as
2058: cursor lMConditionCursor is
2059: select
2052: detailUrlsOut := ame_util.emptyLongStringList;
2053: raise;
2054: end getDetailUrls;
2055: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2056: parameterOneOut out nocopy ame_util.stringList,
2057: parameterTwoOut out nocopy ame_util.stringList) as
2058: cursor lMConditionCursor is
2059: select
2060: condition_id,
2053: raise;
2054: end getDetailUrls;
2055: procedure getLMConditions(conditionIdOut out nocopy ame_util.idList,
2056: parameterOneOut out nocopy ame_util.stringList,
2057: parameterTwoOut out nocopy ame_util.stringList) as
2058: cursor lMConditionCursor is
2059: select
2060: condition_id,
2061: parameter_one,
2061: parameter_one,
2062: parameter_two
2063: from ame_conditions
2064: where
2065: condition_type = ame_util.listModConditionType and
2066: sysdate between start_date and
2067: nvl(end_date - ame_util.oneSecond, sysdate) ;
2068: tempIndex integer;
2069: begin
2063: from ame_conditions
2064: where
2065: condition_type = ame_util.listModConditionType and
2066: sysdate between start_date and
2067: nvl(end_date - ame_util.oneSecond, sysdate) ;
2068: tempIndex integer;
2069: begin
2070: tempIndex := 1;
2071: for tempLMCondition in lMConditionCursor loop
2076: end loop;
2077: exception
2078: when others then
2079: rollback;
2080: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2081: routineNameIn => 'getLMConditions',
2082: exceptionNumberIn => sqlcode,
2083: exceptionStringIn => sqlerrm);
2084: conditionIdOut := ame_util.emptyIdList;
2080: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2081: routineNameIn => 'getLMConditions',
2082: exceptionNumberIn => sqlcode,
2083: exceptionStringIn => sqlerrm);
2084: conditionIdOut := ame_util.emptyIdList;
2085: parameterOneOut := ame_util.emptyStringList;
2086: parameterTwoOut := ame_util.emptyStringList;
2087: raise;
2088: end getLMConditions;
2081: routineNameIn => 'getLMConditions',
2082: exceptionNumberIn => sqlcode,
2083: exceptionStringIn => sqlerrm);
2084: conditionIdOut := ame_util.emptyIdList;
2085: parameterOneOut := ame_util.emptyStringList;
2086: parameterTwoOut := ame_util.emptyStringList;
2087: raise;
2088: end getLMConditions;
2089: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2082: exceptionNumberIn => sqlcode,
2083: exceptionStringIn => sqlerrm);
2084: conditionIdOut := ame_util.emptyIdList;
2085: parameterOneOut := ame_util.emptyStringList;
2086: parameterTwoOut := ame_util.emptyStringList;
2087: raise;
2088: end getLMConditions;
2089: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2090: descriptionsOut out nocopy ame_util.longStringList) as
2085: parameterOneOut := ame_util.emptyStringList;
2086: parameterTwoOut := ame_util.emptyStringList;
2087: raise;
2088: end getLMConditions;
2089: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2090: descriptionsOut out nocopy ame_util.longStringList) as
2091: cursor LMConditionCursor is
2092: select
2093: condition_id,
2086: parameterTwoOut := ame_util.emptyStringList;
2087: raise;
2088: end getLMConditions;
2089: procedure getLMDescriptions(conditionIdsOut out nocopy ame_util.stringList,
2090: descriptionsOut out nocopy ame_util.longStringList) as
2091: cursor LMConditionCursor is
2092: select
2093: condition_id,
2094: parameter_one,
2095: parameter_two
2096: from
2097: ame_conditions
2098: where
2099: condition_type = ame_util.listModConditionType and
2100: sysdate between start_date and
2101: nvl(end_date - ame_util.oneSecond, sysdate);
2102: tempIndex integer;
2103: approverDesc ame_util.longStringType;
2097: ame_conditions
2098: where
2099: condition_type = ame_util.listModConditionType and
2100: sysdate between start_date and
2101: nvl(end_date - ame_util.oneSecond, sysdate);
2102: tempIndex integer;
2103: approverDesc ame_util.longStringType;
2104: approverValid boolean;
2105: begin
2099: condition_type = ame_util.listModConditionType and
2100: sysdate between start_date and
2101: nvl(end_date - ame_util.oneSecond, sysdate);
2102: tempIndex integer;
2103: approverDesc ame_util.longStringType;
2104: approverValid boolean;
2105: begin
2106: tempIndex := 1;
2107: for LMConditionRec in LMConditionCursor loop
2110: descriptionOut => approverDesc,
2111: validityOut => approverValid);
2112: if(approverValid) then
2113: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2114: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2115: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2116: else
2117: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2118: end if;
2111: validityOut => approverValid);
2112: if(approverValid) then
2113: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2114: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2115: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2116: else
2117: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2118: end if;
2119: tempIndex := tempIndex + 1;
2113: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2114: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2115: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || approverDesc);
2116: else
2117: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || approverDesc);
2118: end if;
2119: tempIndex := tempIndex + 1;
2120: end if;
2121: end loop;
2121: end loop;
2122: exception
2123: when others then
2124: rollback;
2125: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2126: routineNameIn => 'getLMDescriptions',
2127: exceptionNumberIn => sqlcode,
2128: exceptionStringIn => sqlerrm);
2129: conditionIdsOut := ame_util.emptyStringList;
2125: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2126: routineNameIn => 'getLMDescriptions',
2127: exceptionNumberIn => sqlcode,
2128: exceptionStringIn => sqlerrm);
2129: conditionIdsOut := ame_util.emptyStringList;
2130: descriptionsOut := ame_util.emptyLongStringList;
2131: raise;
2132: end getLMDescriptions;
2133: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2126: routineNameIn => 'getLMDescriptions',
2127: exceptionNumberIn => sqlcode,
2128: exceptionStringIn => sqlerrm);
2129: conditionIdsOut := ame_util.emptyStringList;
2130: descriptionsOut := ame_util.emptyLongStringList;
2131: raise;
2132: end getLMDescriptions;
2133: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2134: descriptionsOut out nocopy ame_util.longStringList) as
2129: conditionIdsOut := ame_util.emptyStringList;
2130: descriptionsOut := ame_util.emptyLongStringList;
2131: raise;
2132: end getLMDescriptions;
2133: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2134: descriptionsOut out nocopy ame_util.longStringList) as
2135: cursor LMConditionCursor is
2136: select
2137: ame_conditions.condition_id condition_id,
2130: descriptionsOut := ame_util.emptyLongStringList;
2131: raise;
2132: end getLMDescriptions;
2133: procedure getLMDescriptions2(conditionIdsOut out nocopy ame_util.stringList,
2134: descriptionsOut out nocopy ame_util.longStringList) as
2135: cursor LMConditionCursor is
2136: select
2137: ame_conditions.condition_id condition_id,
2138: ame_conditions.parameter_one parameter_one,
2139: ame_conditions.parameter_two parameter_two
2140: from
2141: ame_conditions
2142: where
2143: ame_conditions.condition_type = ame_util.listModConditionType and
2144: sysdate between ame_conditions.start_date and
2145: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2146: isApproverValid boolean;
2147: tempDescription ame_util.longStringType;
2141: ame_conditions
2142: where
2143: ame_conditions.condition_type = ame_util.listModConditionType and
2144: sysdate between ame_conditions.start_date and
2145: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2146: isApproverValid boolean;
2147: tempDescription ame_util.longStringType;
2148: tempIndex integer;
2149: begin
2143: ame_conditions.condition_type = ame_util.listModConditionType and
2144: sysdate between ame_conditions.start_date and
2145: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2146: isApproverValid boolean;
2147: tempDescription ame_util.longStringType;
2148: tempIndex integer;
2149: begin
2150: tempIndex := 1;
2151: for LMConditionRec in LMConditionCursor loop
2154: descriptionOut => tempDescription,
2155: validityOut => isApproverValid);
2156: if(isApproverValid and
2157: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2158: = ame_util.perOrigSystem) then
2159: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2160: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2161: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2162: else
2156: if(isApproverValid and
2157: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2158: = ame_util.perOrigSystem) then
2159: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2160: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2161: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2162: else
2163: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2164: end if;
2157: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2158: = ame_util.perOrigSystem) then
2159: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2160: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2161: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2162: else
2163: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2164: end if;
2165: tempIndex := tempIndex + 1;
2159: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2160: if(LMConditionRec.parameter_one = ame_util.anyApprover) then
2161: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2162: else
2163: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2164: end if;
2165: tempIndex := tempIndex + 1;
2166: end if;
2167: end loop;
2167: end loop;
2168: exception
2169: when others then
2170: rollback;
2171: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2172: routineNameIn => 'getLMDescriptions2',
2173: exceptionNumberIn => sqlcode,
2174: exceptionStringIn => sqlerrm);
2175: conditionIdsOut := ame_util.emptyStringList;
2171: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2172: routineNameIn => 'getLMDescriptions2',
2173: exceptionNumberIn => sqlcode,
2174: exceptionStringIn => sqlerrm);
2175: conditionIdsOut := ame_util.emptyStringList;
2176: descriptionsOut := ame_util.emptyLongStringList;
2177: raise;
2178: end getLMDescriptions2;
2179: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2172: routineNameIn => 'getLMDescriptions2',
2173: exceptionNumberIn => sqlcode,
2174: exceptionStringIn => sqlerrm);
2175: conditionIdsOut := ame_util.emptyStringList;
2176: descriptionsOut := ame_util.emptyLongStringList;
2177: raise;
2178: end getLMDescriptions2;
2179: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2180: conditionIdsOut out nocopy ame_util.stringList,
2176: descriptionsOut := ame_util.emptyLongStringList;
2177: raise;
2178: end getLMDescriptions2;
2179: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2180: conditionIdsOut out nocopy ame_util.stringList,
2181: descriptionsOut out nocopy ame_util.longStringList) as
2182: cursor LMConditionCursor(lmApproverTypeIn in varchar2) is
2183: select
2184: ame_conditions.condition_id condition_id,
2177: raise;
2178: end getLMDescriptions2;
2179: procedure getLMDescriptions3(lmApproverTypeIn in varchar2,
2180: conditionIdsOut out nocopy ame_util.stringList,
2181: descriptionsOut out nocopy ame_util.longStringList) as
2182: cursor LMConditionCursor(lmApproverTypeIn in varchar2) is
2183: select
2184: ame_conditions.condition_id condition_id,
2185: ame_conditions.parameter_two parameter_two
2185: ame_conditions.parameter_two parameter_two
2186: from
2187: ame_conditions
2188: where
2189: ame_conditions.condition_type = ame_util.listModConditionType and
2190: ame_conditions.parameter_one = lmApproverTypeIn and
2191: sysdate between ame_conditions.start_date and
2192: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2193: isApproverValid boolean;
2188: where
2189: ame_conditions.condition_type = ame_util.listModConditionType and
2190: ame_conditions.parameter_one = lmApproverTypeIn and
2191: sysdate between ame_conditions.start_date and
2192: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2193: isApproverValid boolean;
2194: tempDescription ame_util.longStringType;
2195: tempIndex integer;
2196: begin
2190: ame_conditions.parameter_one = lmApproverTypeIn and
2191: sysdate between ame_conditions.start_date and
2192: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
2193: isApproverValid boolean;
2194: tempDescription ame_util.longStringType;
2195: tempIndex integer;
2196: begin
2197: tempIndex := 1;
2198: for LMConditionRec in LMConditionCursor(lmApproverTypeIn => lmApproverTypeIn) loop
2201: descriptionOut => tempDescription,
2202: validityOut => isApproverValid);
2203: if(isApproverValid and
2204: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2205: = ame_util.perOrigSystem) then
2206: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2207: if(lmApproverTypeIn = ame_util.finalApprover) then
2208: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2209: else
2203: if(isApproverValid and
2204: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2205: = ame_util.perOrigSystem) then
2206: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2207: if(lmApproverTypeIn = ame_util.finalApprover) then
2208: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2209: else
2210: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2211: end if;
2204: ame_approver_type_pkg.getApproverOrigSystem(nameIn => LMConditionRec.parameter_two)
2205: = ame_util.perOrigSystem) then
2206: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2207: if(lmApproverTypeIn = ame_util.finalApprover) then
2208: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2209: else
2210: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2211: end if;
2212: tempIndex := tempIndex + 1;
2206: conditionIdsOut(tempIndex) := to_char(LMConditionRec.condition_id);
2207: if(lmApproverTypeIn = ame_util.finalApprover) then
2208: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_FINAL_APPROVER_IS') || ' ' || tempDescription);
2209: else
2210: descriptionsOut(tempIndex) := (ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_IS') || ' ' || tempDescription);
2211: end if;
2212: tempIndex := tempIndex + 1;
2213: end if;
2214: end loop;
2214: end loop;
2215: exception
2216: when others then
2217: rollback;
2218: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2219: routineNameIn => 'getLMDescriptions3',
2220: exceptionNumberIn => sqlcode,
2221: exceptionStringIn => sqlerrm);
2222: conditionIdsOut := ame_util.emptyStringList;
2218: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2219: routineNameIn => 'getLMDescriptions3',
2220: exceptionNumberIn => sqlcode,
2221: exceptionStringIn => sqlerrm);
2222: conditionIdsOut := ame_util.emptyStringList;
2223: descriptionsOut := ame_util.emptyLongStringList;
2224: raise;
2225: end getLMDescriptions3;
2226: procedure getStringValueList(conditionIdIn in integer,
2219: routineNameIn => 'getLMDescriptions3',
2220: exceptionNumberIn => sqlcode,
2221: exceptionStringIn => sqlerrm);
2222: conditionIdsOut := ame_util.emptyStringList;
2223: descriptionsOut := ame_util.emptyLongStringList;
2224: raise;
2225: end getLMDescriptions3;
2226: procedure getStringValueList(conditionIdIn in integer,
2227: stringValueListOut out nocopy ame_util.longestStringList) as
2223: descriptionsOut := ame_util.emptyLongStringList;
2224: raise;
2225: end getLMDescriptions3;
2226: procedure getStringValueList(conditionIdIn in integer,
2227: stringValueListOut out nocopy ame_util.longestStringList) as
2228: cursor stringValueCursor(conditionIdIn in integer) is
2229: select string_value
2230: from ame_string_values
2231: where condition_id = conditionIdIn and
2229: select string_value
2230: from ame_string_values
2231: where condition_id = conditionIdIn and
2232: sysdate between start_date and
2233: nvl(end_date - ame_util.oneSecond, sysdate)
2234: order by string_value asc;
2235: attributeTypeException exception;
2236: errorCode integer;
2237: errorMessage ame_util.longestStringType;
2233: nvl(end_date - ame_util.oneSecond, sysdate)
2234: order by string_value asc;
2235: attributeTypeException exception;
2236: errorCode integer;
2237: errorMessage ame_util.longestStringType;
2238: tempIndex integer;
2239: begin
2240: if(getAttributeType(conditionIdIn => conditionIdIn) <> ame_util.stringAttributeType) then
2241: raise attributeTypeException;
2236: errorCode integer;
2237: errorMessage ame_util.longestStringType;
2238: tempIndex integer;
2239: begin
2240: if(getAttributeType(conditionIdIn => conditionIdIn) <> ame_util.stringAttributeType) then
2241: raise attributeTypeException;
2242: end if;
2243: tempIndex := 1;
2244: for tempStringValue in stringValueCursor(conditionIdIn) loop
2249: when attributeTypeException then
2250: rollback;
2251: errorCode := -20001;
2252: errorMessage :=
2253: ame_util.getMessage(applicationShortNameIn => 'PER',
2254: messageNameIn => 'AME_400192_CON_STR_VAL_NOT_DEF');
2255: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2256: routineNameIn => 'getStringValueList',
2257: exceptionNumberIn => errorCode,
2251: errorCode := -20001;
2252: errorMessage :=
2253: ame_util.getMessage(applicationShortNameIn => 'PER',
2254: messageNameIn => 'AME_400192_CON_STR_VAL_NOT_DEF');
2255: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2256: routineNameIn => 'getStringValueList',
2257: exceptionNumberIn => errorCode,
2258: exceptionStringIn => errorMessage);
2259: stringValueListOut := ame_util.emptyLongestStringList;
2255: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2256: routineNameIn => 'getStringValueList',
2257: exceptionNumberIn => errorCode,
2258: exceptionStringIn => errorMessage);
2259: stringValueListOut := ame_util.emptyLongestStringList;
2260: raise_application_error(errorCode,
2261: errorMessage);
2262: when others then
2263: rollback;
2260: raise_application_error(errorCode,
2261: errorMessage);
2262: when others then
2263: rollback;
2264: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2265: routineNameIn => 'getStringValueList',
2266: exceptionNumberIn => sqlcode,
2267: exceptionStringIn => '(condition ID ' ||
2268: conditionIdIn||
2267: exceptionStringIn => '(condition ID ' ||
2268: conditionIdIn||
2269: ') ' ||
2270: sqlerrm);
2271: stringValueListOut := ame_util.emptyLongestStringList;
2272: raise;
2273: end getStringValueList;
2274: procedure remove(conditionIdIn in integer,
2275: versionStartDateIn in date,
2279: from ame_conditions
2280: where
2281: condition_id = conditionIdIn and
2282: sysdate between start_date and
2283: nvl(end_date - ame_util.oneSecond, sysdate)
2284: for update;
2285: attributeType ame_attributes.attribute_type%type;
2286: conditionType ame_conditions.condition_type%type;
2287: currentUserId integer;
2285: attributeType ame_attributes.attribute_type%type;
2286: conditionType ame_conditions.condition_type%type;
2287: currentUserId integer;
2288: errorCode integer;
2289: errorMessage ame_util.longestStringType;
2290: inUseException exception;
2291: objectVersionNoDataException exception;
2292: startDate date;
2293: processingDate date;
2304: end if;
2305: if(isInUse(conditionIdIn)) then
2306: raise inUseException;
2307: end if;
2308: currentUserId := ame_util.getCurrentUserId;
2309: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2310: if versionStartDateIn = startDate then
2311: if conditionType <> ame_util.listModConditionType then
2312: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2307: end if;
2308: currentUserId := ame_util.getCurrentUserId;
2309: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2310: if versionStartDateIn = startDate then
2311: if conditionType <> ame_util.listModConditionType then
2312: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2313: if(attributeType = ame_util.stringAttributeType) then
2314: update ame_string_values
2315: set
2309: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2310: if versionStartDateIn = startDate then
2311: if conditionType <> ame_util.listModConditionType then
2312: attributeType := ame_condition_pkg.getAttributeType(conditionIdIn => conditionIdIn);
2313: if(attributeType = ame_util.stringAttributeType) then
2314: update ame_string_values
2315: set
2316: last_updated_by = currentUserId,
2317: last_update_date = processingDate,
2319: end_date = processingDate
2320: where
2321: condition_id = conditionIdIn and
2322: processingDate between start_date and
2323: nvl(end_date - ame_util.oneSecond, processingDate) ;
2324: end if;
2325: end if;
2326: update ame_conditions
2327: set
2331: end_date = processingDate
2332: where
2333: condition_id = conditionIdIn and
2334: processingDate between start_date and
2335: nvl(end_date - ame_util.oneSecond, processingDate) ;
2336: commit;
2337: else
2338: close startDateCursor;
2339: raise ame_util.objectVersionException;
2335: nvl(end_date - ame_util.oneSecond, processingDate) ;
2336: commit;
2337: else
2338: close startDateCursor;
2339: raise ame_util.objectVersionException;
2340: end if;
2341: close startDateCursor;
2342: exception
2343: when ame_util.objectVersionException then
2339: raise ame_util.objectVersionException;
2340: end if;
2341: close startDateCursor;
2342: exception
2343: when ame_util.objectVersionException then
2344: rollback;
2345: if(startDateCursor%isOpen) then
2346: close startDateCursor;
2347: end if;
2346: close startDateCursor;
2347: end if;
2348: errorCode := -20001;
2349: errorMessage :=
2350: ame_util.getMessage(applicationShortNameIn => 'PER',
2351: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2352: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2353: routineNameIn => 'remove',
2354: exceptionNumberIn => errorCode,
2348: errorCode := -20001;
2349: errorMessage :=
2350: ame_util.getMessage(applicationShortNameIn => 'PER',
2351: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2352: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2353: routineNameIn => 'remove',
2354: exceptionNumberIn => errorCode,
2355: exceptionStringIn => errorMessage);
2356: raise_application_error(errorCode,
2361: close startDateCursor;
2362: end if;
2363: errorCode := -20001;
2364: errorMessage :=
2365: ame_util.getMessage(applicationShortNameIn => 'PER',
2366: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2367: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2368: routineNameIn => 'remove',
2369: exceptionNumberIn => errorCode,
2363: errorCode := -20001;
2364: errorMessage :=
2365: ame_util.getMessage(applicationShortNameIn => 'PER',
2366: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2367: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2368: routineNameIn => 'remove',
2369: exceptionNumberIn => errorCode,
2370: exceptionStringIn => errorMessage);
2371: raise_application_error(errorCode,
2373: when inUseException then
2374: rollback;
2375: errorCode := -20001;
2376: errorMessage :=
2377: ame_util.getMessage(applicationShortNameIn => 'PER',
2378: messageNameIn => 'AME_400193_CON_IN USE');
2379: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2380: routineNameIn => 'remove',
2381: exceptionNumberIn => errorCode,
2375: errorCode := -20001;
2376: errorMessage :=
2377: ame_util.getMessage(applicationShortNameIn => 'PER',
2378: messageNameIn => 'AME_400193_CON_IN USE');
2379: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2380: routineNameIn => 'remove',
2381: exceptionNumberIn => errorCode,
2382: exceptionStringIn => errorMessage);
2383: raise_application_error(errorCode,
2386: rollback;
2387: if(startDateCursor%isOpen) then
2388: close startDateCursor;
2389: end if;
2390: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2391: routineNameIn => 'remove',
2392: exceptionNumberIn => sqlcode,
2393: exceptionStringIn => '(condition ID ' ||
2394: conditionIdIn||
2400: conditionIdIn in integer,
2401: newConditionIdIn in integer default null,
2402: finalizeIn in boolean default true,
2403: processingDateIn in date default null) as
2404: actionIdList ame_util.idList;
2405: conditionIdList ame_util.idList;
2406: currentUserId integer;
2407: errorCode integer;
2408: errorMessage ame_util.longestStringType;
2401: newConditionIdIn in integer default null,
2402: finalizeIn in boolean default true,
2403: processingDateIn in date default null) as
2404: actionIdList ame_util.idList;
2405: conditionIdList ame_util.idList;
2406: currentUserId integer;
2407: errorCode integer;
2408: errorMessage ame_util.longestStringType;
2409: inUseException exception;
2404: actionIdList ame_util.idList;
2405: conditionIdList ame_util.idList;
2406: currentUserId integer;
2407: errorCode integer;
2408: errorMessage ame_util.longestStringType;
2409: inUseException exception;
2410: processingDate date;
2411: ruleType ame_rules.rule_type%type;
2412: tempIndex integer;
2415: processingDate := sysdate;
2416: else
2417: processingDate := processingDateIn;
2418: end if;
2419: currentUserId := ame_util.getCurrentUserId;
2420: update ame_condition_usages
2421: set
2422: last_updated_by = currentUserId,
2423: last_update_date = processingDate,
2426: where
2427: condition_id = conditionIdIn and
2428: rule_id = ruleIdIn and
2429: ((processingDate between start_date and
2430: nvl(end_date - ame_util.oneSecond, processingDate)) or
2431: (processingDate < start_date and
2432: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2433: if(newConditionIdIn is not null) then
2434: /* The list modification condition has been changed. Check to see if
2428: rule_id = ruleIdIn and
2429: ((processingDate between start_date and
2430: nvl(end_date - ame_util.oneSecond, processingDate)) or
2431: (processingDate < start_date and
2432: start_date < nvl(end_date,start_date + ame_util.oneSecond))) ;
2433: if(newConditionIdIn is not null) then
2434: /* The list modification condition has been changed. Check to see if
2435: changing the condition resulted in a rule duplication. */
2436: ame_rule_pkg.getConditionIds(ruleIdIn => ruleIdIn,
2453: when inUseException then
2454: rollback;
2455: errorCode := -20001;
2456: errorMessage :=
2457: ame_util.getMessage(applicationShortNameIn => 'PER',
2458: messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
2459: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2460: routineNamein => 'removeConditionUsage',
2461: exceptionNumberIn => errorCode,
2455: errorCode := -20001;
2456: errorMessage :=
2457: ame_util.getMessage(applicationShortNameIn => 'PER',
2458: messageNameIn => 'AME_400212_RUL_PROP_EXISTS');
2459: ame_util.runtimeException(packageNamein => 'ame_condition_pkg',
2460: routineNamein => 'removeConditionUsage',
2461: exceptionNumberIn => errorCode,
2462: exceptionStringIn => errorMessage);
2463: raise_application_error(errorCode,
2463: raise_application_error(errorCode,
2464: errorMessage);
2465: when others then
2466: rollback;
2467: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2468: routineNameIn => 'removeConditionUsage',
2469: exceptionNumberIn => sqlcode,
2470: exceptionStringIn => '(condition ID ' ||
2471: conditionIdIn||
2474: raise;
2475: end removeConditionUsage;
2476: procedure removeStringValue(conditionIdIn in integer,
2477: versionStartDateIn in date,
2478: stringValueListIn in ame_util.longestStringList,
2479: processingDateIn in date default null) as
2480: cursor startDateCursor is
2481: select start_date
2482: from ame_conditions
2482: from ame_conditions
2483: where
2484: condition_id = conditionIdIn and
2485: sysdate between start_date and
2486: nvl(end_date - ame_util.oneSecond, sysdate)
2487: for update;
2488: attributeId ame_conditions.attribute_id%type;
2489: conditionId ame_conditions.condition_id%type;
2490: conditionKey ame_conditions.condition_key%type;
2490: conditionKey ame_conditions.condition_key%type;
2491: conditionType ame_conditions.condition_type%type;
2492: currentUserId integer;
2493: errorCode integer;
2494: errorMessage ame_util.longestStringType;
2495: includeLowerLimit ame_conditions.include_lower_limit%type;
2496: includeUpperLimit ame_conditions.include_upper_limit%type;
2497: objectVersionNoDataException exception;
2498: parameterOne ame_conditions.parameter_one%type;
2511: fetch startDateCursor into startDate;
2512: if startDateCursor%notfound then
2513: raise objectVersionNoDataException;
2514: end if;
2515: currentUserId := ame_util.getCurrentUserId;
2516: if versionStartDateIn = startDate then
2517: stringCount := stringValueListIn.count;
2518: for i in 1..stringCount loop
2519: update ame_string_values
2525: where
2526: condition_id = conditionIdIn and
2527: string_value = stringValueListIn(i) and
2528: processingDate between start_date and
2529: nvl(end_date - ame_util.oneSecond, processingDate) ;
2530: end loop;
2531: conditionType := ame_condition_pkg.getType(conditionIdIn => conditionIdIn);
2532: conditionKey := ame_condition_pkg.getConditionKey(conditionIdIn => conditionIdIn);
2533: attributeId := ame_condition_pkg.getAttributeId(conditionIdIn => conditionIdIn);
2544: end_date = processingDate
2545: where
2546: condition_id = conditionIdIn and
2547: processingDate between start_date and
2548: nvl(end_date - ame_util.oneSecond, processingDate) ;
2549: conditionId := new(conditionIdIn => conditionIdIn,
2550: typeIn => conditionType,
2551: attributeIdIn => attributeId,
2552: conditionKeyIn => conditionKey,
2556: includeLowerLimitIn => includeLowerLimit,
2557: includeUpperLimitIn => includeUpperLimit);
2558: else
2559: close startDateCursor;
2560: raise ame_util.objectVersionException;
2561: end if;
2562: close startDateCursor;
2563: exception
2564: when ame_util.objectVersionException then
2560: raise ame_util.objectVersionException;
2561: end if;
2562: close startDateCursor;
2563: exception
2564: when ame_util.objectVersionException then
2565: rollback;
2566: if(startDateCursor%isOpen) then
2567: close startDateCursor;
2568: end if;
2567: close startDateCursor;
2568: end if;
2569: errorCode := -20001;
2570: errorMessage :=
2571: ame_util.getMessage(applicationShortNameIn => 'PER',
2572: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2573: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2574: routineNameIn => 'removeStringValue',
2575: exceptionNumberIn => errorCode,
2569: errorCode := -20001;
2570: errorMessage :=
2571: ame_util.getMessage(applicationShortNameIn => 'PER',
2572: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2573: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2574: routineNameIn => 'removeStringValue',
2575: exceptionNumberIn => errorCode,
2576: exceptionStringIn => errorMessage);
2577: raise_application_error(errorCode,
2582: close startDateCursor;
2583: end if;
2584: errorCode := -20001;
2585: errorMessage :=
2586: ame_util.getMessage(applicationShortNameIn => 'PER',
2587: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2588: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2589: routineNameIn => 'removeStringValue',
2590: exceptionNumberIn => errorCode,
2584: errorCode := -20001;
2585: errorMessage :=
2586: ame_util.getMessage(applicationShortNameIn => 'PER',
2587: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2588: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2589: routineNameIn => 'removeStringValue',
2590: exceptionNumberIn => errorCode,
2591: exceptionStringIn => errorMessage);
2592: raise_application_error(errorCode,
2595: rollback;
2596: if(startDateCursor%isOpen) then
2597: close startDateCursor;
2598: end if;
2599: ame_util.runtimeException(packageNameIn => 'ame_condition_pkg',
2600: routineNameIn => 'removeStringValue',
2601: exceptionNumberIn => sqlcode,
2602: exceptionStringIn => '(condition ID ' ||
2603: conditionIdIn||