[Home] [Help]
8: from ame_attributes
9: where
10: attribute_id = attributeIdIn and
11: sysdate between start_date and
12: nvl(end_date - ame_util.oneSecond, sysdate) ;
13: if attributeCount > 0 then
14: return(true);
15: end if;
16: return(false);
16: return(false);
17: exception
18: when others then
19: rollback;
20: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
21: routineNameIn => 'attributeExists',
22: exceptionNumberIn => sqlcode,
23: exceptionStringIn => '(attribute ID ' ||
24: attributeIdIn||
36: from ame_attributes
37: where
38: name = attributeNameIn and
39: sysdate between start_date and
40: nvl(end_date - ame_util.oneSecond, sysdate);
41: if(itemClassId <> itemClassIdIn) then
42: return(true);
43: end if;
44: return(false);
44: return(false);
45: exception
46: when others then
47: rollback;
48: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
49: routineNameIn => 'attributeExistsForDiffIC',
50: exceptionNumberIn => sqlcode,
51: exceptionStringIn => sqlerrm);
52: raise;
55: /*
56: AME_STRIPING
57: function calculateUseCount(attributeIdIn in integer,
58: applicationIdIn in integer,
59: isStripingAttributeChangeIn in varchar2 default ame_util.booleanFalse,
60: isBecomingStripingAttributeIn in varchar2 default ame_util.booleanFalse) return integer as
61: */
62: function calculateUseCount(attributeIdIn in integer,
63: applicationIdIn in integer) return integer as
56: AME_STRIPING
57: function calculateUseCount(attributeIdIn in integer,
58: applicationIdIn in integer,
59: isStripingAttributeChangeIn in varchar2 default ame_util.booleanFalse,
60: isBecomingStripingAttributeIn in varchar2 default ame_util.booleanFalse) return integer as
61: */
62: function calculateUseCount(attributeIdIn in integer,
63: applicationIdIn in integer) return integer as
64: cursor ruleCursor(applicationIdIn in integer) is
66: from ame_rule_usages
67: where
68: ame_rule_usages.item_id = applicationIdIn and
69: ((sysdate between ame_rule_usages.start_date and
70: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
71: (sysdate < ame_rule_usages.start_date and
72: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
73: ame_rule_usages.start_date + ame_util.oneSecond)));
74: ruleCount integer;
69: ((sysdate between ame_rule_usages.start_date and
70: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
71: (sysdate < ame_rule_usages.start_date and
72: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
73: ame_rule_usages.start_date + ame_util.oneSecond)));
74: ruleCount integer;
75: tempCount integer;
76: useCount integer;
77: begin
85: /*
86: if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn) and
87: (isStripingAttribute(applicationIdIn => applicationIdIn,
88: attributeIdIn => attributeIdIn) or
89: isBecomingStripingAttributeIn = ame_util.booleanTrue) and
90: isStripingAttributeChangeIn = ame_util.booleanFalse) then
91: select count(*)
92: into ruleCount
93: from ame_rule_usages
86: if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn) and
87: (isStripingAttribute(applicationIdIn => applicationIdIn,
88: attributeIdIn => attributeIdIn) or
89: isBecomingStripingAttributeIn = ame_util.booleanTrue) and
90: isStripingAttributeChangeIn = ame_util.booleanFalse) then
91: select count(*)
92: into ruleCount
93: from ame_rule_usages
94: where
109: ame_conditions.attribute_id = attributeIdIn and
110: ame_conditions.condition_id = ame_condition_usages.condition_id and
111: ame_condition_usages.rule_id = tempRule.rule_id and
112: sysdate between ame_conditions.start_date and
113: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
114: ((sysdate between ame_condition_usages.start_date and
115: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
116: (sysdate < ame_condition_usages.start_date and
117: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
111: ame_condition_usages.rule_id = tempRule.rule_id and
112: sysdate between ame_conditions.start_date and
113: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
114: ((sysdate between ame_condition_usages.start_date and
115: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
116: (sysdate < ame_condition_usages.start_date and
117: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
118: ame_condition_usages.start_date + ame_util.oneSecond)));
119: if(tempCount > 0) then
114: ((sysdate between ame_condition_usages.start_date and
115: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
116: (sysdate < ame_condition_usages.start_date and
117: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
118: ame_condition_usages.start_date + ame_util.oneSecond)));
119: if(tempCount > 0) then
120: useCount := useCount + 1;
121: else
122: select count(*)
130: ame_mandatory_attributes.action_type_id = ame_actions.action_type_id and
131: ame_actions.action_id = ame_action_usages.action_id and
132: ame_action_usages.rule_id = tempRule.rule_id and
133: sysdate between ame_mandatory_attributes.start_date and
134: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
135: sysdate between ame_actions.start_date and
136: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
137: ((sysdate between ame_action_usages.start_date and
138: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
132: ame_action_usages.rule_id = tempRule.rule_id and
133: sysdate between ame_mandatory_attributes.start_date and
134: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
135: sysdate between ame_actions.start_date and
136: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
137: ((sysdate between ame_action_usages.start_date and
138: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
139: (sysdate < ame_action_usages.start_date and
140: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
134: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
135: sysdate between ame_actions.start_date and
136: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
137: ((sysdate between ame_action_usages.start_date and
138: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
139: (sysdate < ame_action_usages.start_date and
140: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
141: ame_action_usages.start_date + ame_util.oneSecond)));
142: if(tempCount > 0) then
137: ((sysdate between ame_action_usages.start_date and
138: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
139: (sysdate < ame_action_usages.start_date and
140: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
141: ame_action_usages.start_date + ame_util.oneSecond)));
142: if(tempCount > 0) then
143: useCount := useCount + 1;
144: end if;
145: end if;
147: return(useCount);
148: exception
149: when others then
150: rollback;
151: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
152: routineNameIn => 'calculateUseCount',
153: exceptionNumberIn => sqlcode,
154: exceptionStringIn => '(Attribute ID ' ||
155: attributeIdIn||
168: where
169: ame_action_types.action_type_id = ame_mandatory_attributes.action_type_id and
170: ame_mandatory_attributes.attribute_id = attributeIdIn and
171: sysdate between ame_action_types.start_date and
172: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
173: sysdate between ame_mandatory_attributes.start_date and
174: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
175: order by name;
176: tempCount integer;
170: ame_mandatory_attributes.attribute_id = attributeIdIn and
171: sysdate between ame_action_types.start_date and
172: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
173: sysdate between ame_mandatory_attributes.start_date and
174: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
175: order by name;
176: tempCount integer;
177: approvalTypeNames varchar2(500);
178: begin
189: return(approvalTypeNames);
190: exception
191: when others then
192: rollback;
193: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
194: routineNameIn => 'getApprovalTypeNames',
195: exceptionNumberIn => sqlcode,
196: exceptionStringIn => '(attribute ID ' ||
197: attributeIdIn ||
208: from ame_attributes
209: where
210: attribute_id = attributeIdIn and
211: sysdate between ame_attributes.start_date and
212: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
213: return(approverTypeId);
214: exception
215: when others then
216: rollback;
213: return(approverTypeId);
214: exception
215: when others then
216: rollback;
217: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
218: routineNameIn => 'getApproverTypeId',
219: exceptionNumberIn => sqlcode,
220: exceptionStringIn => '(attribute ID ' ||
221: attributeIdIn ||
236: ame_attributes.attribute_id = ame_conditions.attribute_id and
237: ame_attributes.attribute_id = attributeIdIn and
238: ame_conditions.condition_type = conditionTypeIn and
239: sysdate between ame_attributes.start_date and
240: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
241: sysdate between ame_conditions.start_date and
242: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
243: return(attributeConditionCnt);
244: exception
238: ame_conditions.condition_type = conditionTypeIn and
239: sysdate between ame_attributes.start_date and
240: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
241: sysdate between ame_conditions.start_date and
242: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
243: return(attributeConditionCnt);
244: exception
245: when others then
246: rollback;
243: return(attributeConditionCnt);
244: exception
245: when others then
246: rollback;
247: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
248: routineNameIn => 'getAttributeConditionCnt',
249: exceptionNumberIn => sqlcode,
250: exceptionStringIn => '(attribute ID ' ||
251: attributeIdIn ||
272: ame_rules.rule_id = ruleIdIn and
273: ame_attributes.attribute_id = attributeIdIn and
274: ame_conditions.condition_type = conditionTypeIn and
275: sysdate between ame_attributes.start_date and
276: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
277: sysdate between ame_conditions.start_date and
278: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
279: ((sysdate between ame_condition_usages.start_date and
280: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
274: ame_conditions.condition_type = conditionTypeIn and
275: sysdate between ame_attributes.start_date and
276: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
277: sysdate between ame_conditions.start_date and
278: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
279: ((sysdate between ame_condition_usages.start_date and
280: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
281: (sysdate < ame_condition_usages.start_date and
282: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
276: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
277: sysdate between ame_conditions.start_date and
278: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
279: ((sysdate between ame_condition_usages.start_date and
280: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
281: (sysdate < ame_condition_usages.start_date and
282: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
283: ame_condition_usages.start_date + ame_util.oneSecond))) and
284: ((sysdate between ame_rules.start_date and
279: ((sysdate between ame_condition_usages.start_date and
280: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
281: (sysdate < ame_condition_usages.start_date and
282: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
283: ame_condition_usages.start_date + ame_util.oneSecond))) and
284: ((sysdate between ame_rules.start_date and
285: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
286: (sysdate < ame_rules.start_date and
287: ame_rules.start_date < nvl(ame_rules.end_date,
281: (sysdate < ame_condition_usages.start_date and
282: ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
283: ame_condition_usages.start_date + ame_util.oneSecond))) and
284: ((sysdate between ame_rules.start_date and
285: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
286: (sysdate < ame_rules.start_date and
287: ame_rules.start_date < nvl(ame_rules.end_date,
288: ame_rules.start_date + ame_util.oneSecond)));
289: return(attributeConditionInUseCnt);
284: ((sysdate between ame_rules.start_date and
285: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
286: (sysdate < ame_rules.start_date and
287: ame_rules.start_date < nvl(ame_rules.end_date,
288: ame_rules.start_date + ame_util.oneSecond)));
289: return(attributeConditionInUseCnt);
290: exception
291: when others then
292: rollback;
289: return(attributeConditionInUseCnt);
290: exception
291: when others then
292: rollback;
293: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
294: routineNameIn => 'getAttributeConditionInUseCnt',
295: exceptionNumberIn => sqlcode,
296: exceptionStringIn => '(attribute ID ' ||
297: attributeIdIn ||
308: where
309: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
310: ame_mandatory_attributes.action_type_id = actionTypeIdIn and
311: sysdate between ame_attributes.start_date and
312: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
313: sysdate between ame_mandatory_attributes.start_date and
314: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
315: order by ame_attributes.name;
316: tempCount integer;
310: ame_mandatory_attributes.action_type_id = actionTypeIdIn and
311: sysdate between ame_attributes.start_date and
312: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
313: sysdate between ame_mandatory_attributes.start_date and
314: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
315: order by ame_attributes.name;
316: tempCount integer;
317: attributeNames varchar2(500);
318: begin
329: return(attributeNames);
330: exception
331: when others then
332: rollback;
333: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
334: routineNameIn => 'getAttributeNames',
335: exceptionNumberIn => sqlcode,
336: exceptionStringIn => '(action type ID ' ||
337: actionTypeIdIn ||
348: from ame_attributes
349: where
350: attribute_id = attributeIdIn and
351: sysdate between start_date and
352: nvl(end_date - ame_util.oneSecond, sysdate) ;
353: return(description);
354: exception
355: when others then
356: rollback;
353: return(description);
354: exception
355: when others then
356: rollback;
357: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
358: routineNameIn => 'getDescription',
359: exceptionNumberIn => sqlcode,
360: exceptionStringIn => '(attribute ID ' ||
361: attributeIdIn||
372: from ame_attributes
373: where
374: name = upper(attributeNameIn) and
375: sysdate between start_date and
376: nvl(end_date - ame_util.oneSecond, sysdate) ;
377: return(attributeId);
378: exception
379: when others then
380: rollback;
377: return(attributeId);
378: exception
379: when others then
380: rollback;
381: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
382: routineNameIn => 'getIdByName',
383: exceptionNumberIn => sqlcode,
384: exceptionStringIn => '(attribute name ' ||
385: attributeNameIn||
396: from ame_attributes
397: where
398: attribute_id = attributeIdIn and
399: sysdate between start_date and
400: nvl(end_date - ame_util.oneSecond, sysdate) ;
401: return(itemClassId);
402: exception
403: when others then
404: rollback;
401: return(itemClassId);
402: exception
403: when others then
404: rollback;
405: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
406: routineNameIn => 'getItemClassId',
407: exceptionNumberIn => sqlcode,
408: exceptionStringIn => '(attribute ID ' ||
409: attributeIdIn||
420: from ame_attributes
421: where
422: attribute_id = attributeIdIn and
423: sysdate between start_date and
424: nvl(end_date - ame_util.oneSecond, sysdate) ;
425: return(lineItem);
426: exception
427: when others then
428: rollback;
425: return(lineItem);
426: exception
427: when others then
428: rollback;
429: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
430: routineNameIn => 'getLineItem',
431: exceptionNumberIn => sqlcode,
432: exceptionStringIn => '(attribute ID ' ||
433: attributeIdIn||
433: attributeIdIn||
434: ') ' ||
435: sqlerrm);
436: raise;
437: return(ame_util.booleanFalse);
438: end getLineItem;
439: function getName(attributeIdIn in integer) return varchar2 as
440: name ame_attributes.name%type;
441: begin
444: from ame_attributes
445: where
446: attribute_id = attributeIdIn and
447: sysdate between start_date and
448: nvl(end_date - ame_util.oneSecond, sysdate) ;
449: return(name);
450: exception
451: when others then
452: rollback;
449: return(name);
450: exception
451: when others then
452: rollback;
453: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
454: routineNameIn => 'getName',
455: exceptionNumberIn => sqlcode,
456: exceptionStringIn => '(attribute ID ' ||
457: attributeIdIn||
462: end getName;
463: function getQueryString(attributeIdIn in integer,
464: applicationIdIn in integer) return varchar2 as
465: errorCode integer;
466: errorMessage ame_util.longestStringType;
467: queryString ame_attribute_usages.query_string%type;
468: begin
469: select query_string
470: into queryString
472: where
473: attribute_id = attributeIdIn and
474: application_id = applicationIdIn and
475: sysdate between start_date and
476: nvl(end_date - ame_util.oneSecond, sysdate) ;
477: return(queryString);
478: exception
479: when no_data_found then
480: rollback;
479: when no_data_found then
480: rollback;
481: errorCode := -20001;
482: errorMessage :=
483: ame_util.getMessage(applicationShortNameIn => 'PER',
484: messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
485: tokenNameOneIn => 'ATTRIBUTE',
486: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
487: tokenNameTwoIn => 'APPLICATION',
485: tokenNameOneIn => 'ATTRIBUTE',
486: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
487: tokenNameTwoIn => 'APPLICATION',
488: tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
489: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
490: routineNameIn => 'getQueryString',
491: exceptionNumberIn => errorCode,
492: exceptionStringIn => errorMessage);
493: raise_application_error(errorCode,
494: errorMessage);
495: return(null);
496: when others then
497: rollback;
498: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
499: routineNameIn => 'getQueryString',
500: exceptionNumberIn => sqlcode,
501: exceptionStringIn => '(attribute ID ' ||
502: attributeIdIn||
513: from ame_attributes
514: where
515: attribute_id = attributeIdIn and
516: sysdate between start_date and
517: nvl(end_date - ame_util.oneSecond, sysdate) ;
518: return(startDate);
519: exception
520: when others then
521: rollback;
518: return(startDate);
519: exception
520: when others then
521: rollback;
522: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
523: routineNameIn => 'getStartDate',
524: exceptionNumberIn => sqlcode,
525: exceptionStringIn => '(attribute ID ' ||
526: attributeIdIn||
538: from ame_attribute_usages
539: where attribute_id = attributeIdIn and
540: application_id = applicationIdIn and
541: sysdate between start_date and
542: nvl(end_date - ame_util.oneSecond, sysdate) ;
543: return(staticUsage);
544: exception
545: when others then
546: rollback;
543: return(staticUsage);
544: exception
545: when others then
546: rollback;
547: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
548: routineNameIn => 'getStaticUsage',
549: exceptionNumberIn => sqlcode,
550: exceptionStringIn => '(attribute ID ' ||
551: attributeIdIn||
556: end getStaticUsage;
557: function getUseCount(attributeIdIn in integer,
558: applicationIdIn in integer) return varchar2 as
559: errorCode integer;
560: errorMessage ame_util.longestStringType;
561: useCount ame_attribute_usages.use_count%type;
562: begin
563: select use_count
564: into useCount
566: where
567: attribute_id = attributeIdIn and
568: application_id = applicationIdIn and
569: sysdate between start_date and
570: nvl(end_date - ame_util.oneSecond, sysdate) ;
571: return(useCount);
572: exception
573: when no_data_found then
574: rollback;
573: when no_data_found then
574: rollback;
575: errorCode := -20001;
576: errorMessage :=
577: ame_util.getMessage(applicationShortNameIn => 'PER',
578: messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
579: tokenNameOneIn => 'ATTRIBUTE',
580: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
581: tokenNameTwoIn => 'APPLICATION',
579: tokenNameOneIn => 'ATTRIBUTE',
580: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
581: tokenNameTwoIn => 'APPLICATION',
582: tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
583: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
584: routineNameIn => 'getUseCount',
585: exceptionNumberIn => errorCode,
586: exceptionStringIn => errorMessage);
587: raise_application_error(errorCode,
588: errorMessage);
589: return(null);
590: when others then
591: rollback;
592: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
593: routineNameIn => 'getUseCount',
594: exceptionNumberIn => sqlcode,
595: exceptionStringIn => '(attribute ID ' ||
596: attributeIdIn||
601: end getUseCount;
602: function getUserEditable(attributeIdIn in integer,
603: applicationIdIn in integer) return varchar2 as
604: errorCode integer;
605: errorMessage ame_util.longestStringType;
606: userEditable ame_attribute_usages.user_editable%type;
607: begin
608: select user_editable
609: into userEditable
611: where
612: attribute_id = attributeIdIn and
613: application_id = applicationIdIn and
614: sysdate between start_date and
615: nvl(end_date - ame_util.oneSecond, sysdate) ;
616: return(userEditable);
617: exception
618: when no_data_found then
619: rollback;
618: when no_data_found then
619: rollback;
620: errorCode := -20001;
621: errorMessage :=
622: ame_util.getMessage(applicationShortNameIn => 'PER',
623: messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
624: tokenNameOneIn => 'ATTRIBUTE',
625: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
626: tokenNameTwoIn => 'APPLICATION',
624: tokenNameOneIn => 'ATTRIBUTE',
625: tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
626: tokenNameTwoIn => 'APPLICATION',
627: tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
628: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
629: routineNameIn => 'getUserEditable',
630: exceptionNumberIn => errorCode,
631: exceptionStringIn => errorMessage);
632: raise_application_error(errorCode,
633: errorMessage);
634: return(null);
635: when others then
636: rollback;
637: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
638: routineNameIn => 'getUserEditable',
639: exceptionNumberIn => sqlcode,
640: exceptionStringIn => '(attribute ID ' ||
641: attributeIdIn||
655: where
656: attribute_id = attributeIdIn and
657: application_id = applicationIdIn and
658: sysdate between start_date and
659: nvl(end_date - ame_util.oneSecond, sysdate) ;
660: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
661: return(stringStartDate);
662: exception
663: when others then
656: attribute_id = attributeIdIn and
657: application_id = applicationIdIn and
658: sysdate between start_date and
659: nvl(end_date - ame_util.oneSecond, sysdate) ;
660: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
661: return(stringStartDate);
662: exception
663: when others then
664: rollback;
661: return(stringStartDate);
662: exception
663: when others then
664: rollback;
665: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
666: routineNameIn => 'getChildVersionStartDate',
667: exceptionNumberIn => sqlcode,
668: exceptionStringIn => '(attribute ID ' ||
669: attributeIdIn||
681: from ame_attributes
682: where
683: attribute_id = attributeIdIn and
684: sysdate between start_date and
685: nvl(end_date - ame_util.oneSecond, sysdate) ;
686: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
687: return(stringStartDate);
688: exception
689: when others then
682: where
683: attribute_id = attributeIdIn and
684: sysdate between start_date and
685: nvl(end_date - ame_util.oneSecond, sysdate) ;
686: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
687: return(stringStartDate);
688: exception
689: when others then
690: rollback;
687: return(stringStartDate);
688: exception
689: when others then
690: rollback;
691: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
692: routineNameIn => 'getParentVersionStartDate',
693: exceptionNumberIn => sqlcode,
694: exceptionStringIn => '(attribute ID ' ||
695: attributeIdIn||
706: from ame_attributes
707: where
708: attribute_id = attributeIdIn and
709: sysdate between start_date and
710: nvl(end_date - ame_util.oneSecond, sysdate) ;
711: return(attributeType);
712: exception
713: when others then
714: rollback;
711: return(attributeType);
712: exception
713: when others then
714: rollback;
715: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
716: routineNameIn => 'getType',
717: exceptionNumberIn => sqlcode,
718: exceptionStringIn => '(attribute ID ' ||
719: attributeIdIn||
724: end getType;
725: function inputToCanonStaticCurUsage(attributeIdIn in integer,
726: applicationIdIn in integer,
727: queryStringIn varchar2) return varchar2 as
728: amount ame_util.attributeValueType;
729: conversionType ame_util.attributeValueType;
730: convTypeException exception;
731: curCodeException exception;
732: currencyCode ame_util.attributeValueType;
725: function inputToCanonStaticCurUsage(attributeIdIn in integer,
726: applicationIdIn in integer,
727: queryStringIn varchar2) return varchar2 as
728: amount ame_util.attributeValueType;
729: conversionType ame_util.attributeValueType;
730: convTypeException exception;
731: curCodeException exception;
732: currencyCode ame_util.attributeValueType;
733: errorCode integer;
728: amount ame_util.attributeValueType;
729: conversionType ame_util.attributeValueType;
730: convTypeException exception;
731: curCodeException exception;
732: currencyCode ame_util.attributeValueType;
733: errorCode integer;
734: errorMessage ame_util.longestStringType;
735: begin
736: /*
730: convTypeException exception;
731: curCodeException exception;
732: currencyCode ame_util.attributeValueType;
733: errorCode integer;
734: errorMessage ame_util.longestStringType;
735: begin
736: /*
737: The ame_util.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
738: but it doesn't validate the individual values, or convert the amount to canonical format.
733: errorCode integer;
734: errorMessage ame_util.longestStringType;
735: begin
736: /*
737: The ame_util.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
738: but it doesn't validate the individual values, or convert the amount to canonical format.
739: */
740: ame_util.parseStaticCurAttValue(applicationIdIn => applicationIdIn,
741: attributeIdIn => attributeIdIn,
736: /*
737: The ame_util.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
738: but it doesn't validate the individual values, or convert the amount to canonical format.
739: */
740: ame_util.parseStaticCurAttValue(applicationIdIn => applicationIdIn,
741: attributeIdIn => attributeIdIn,
742: attributeValueIn => queryStringIn,
743: amountOut => amount,
744: localErrorIn => true,
743: amountOut => amount,
744: localErrorIn => true,
745: currencyOut => currencyCode,
746: conversionTypeOut => conversionType);
747: /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
748: amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
749: currencyCodeIn => currencyCode);
750: if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
751: raise curCodeException;
744: localErrorIn => true,
745: currencyOut => currencyCode,
746: conversionTypeOut => conversionType);
747: /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
748: amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
749: currencyCodeIn => currencyCode);
750: if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
751: raise curCodeException;
752: end if;
746: conversionTypeOut => conversionType);
747: /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
748: amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
749: currencyCodeIn => currencyCode);
750: if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
751: raise curCodeException;
752: end if;
753: if not ame_util.isConversionTypeValid(conversionTypeIn => conversionType) then
754: raise convTypeException;
749: currencyCodeIn => currencyCode);
750: if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
751: raise curCodeException;
752: end if;
753: if not ame_util.isConversionTypeValid(conversionTypeIn => conversionType) then
754: raise convTypeException;
755: end if;
756: return(amount || ',' || currencyCode || ',' || conversionType);
757: exception
758: when convTypeException then
759: rollback;
760: errorCode := -20001;
761: errorMessage :=
762: ame_util.getMessage(applicationShortNameIn => 'PER',
763: messageNameIn => 'AME_400150_ATT_STA_CONV_INV');
764: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
765: routineNameIn => 'inputToCanonStaticCurUsage',
766: exceptionNumberIn => errorCode,
760: errorCode := -20001;
761: errorMessage :=
762: ame_util.getMessage(applicationShortNameIn => 'PER',
763: messageNameIn => 'AME_400150_ATT_STA_CONV_INV');
764: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
765: routineNameIn => 'inputToCanonStaticCurUsage',
766: exceptionNumberIn => errorCode,
767: exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
768: raise_application_error(errorCode,
771: when curCodeException then
772: rollback;
773: errorCode := -20001;
774: errorMessage :=
775: ame_util.getMessage(applicationShortNameIn => 'PER',
776: messageNameIn => 'AME_400151_ATT_STA_CURR_INV');
777: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
778: routineNameIn => 'inputToCanonStaticCurUsage',
779: exceptionNumberIn => errorCode,
773: errorCode := -20001;
774: errorMessage :=
775: ame_util.getMessage(applicationShortNameIn => 'PER',
776: messageNameIn => 'AME_400151_ATT_STA_CURR_INV');
777: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
778: routineNameIn => 'inputToCanonStaticCurUsage',
779: exceptionNumberIn => errorCode,
780: exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
781: raise_application_error(errorCode,
782: errorMessage);
783: return(null);
784: when others then
785: rollback;
786: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
787: routineNameIn => 'inputToCanonStaticCurUsage',
788: exceptionNumberIn => sqlcode,
789: exceptionStringIn => '(attribute ID ' ||
790: attributeIdIn||
803: where
804: attribute_id = attributeIdIn and
805: application_id <> applicationIdIn and
806: sysdate between start_date and
807: nvl(end_date - ame_util.oneSecond, sysdate) ;
808: if(attributeCount > 0) then
809: return(true);
810: end if;
811: return(false);
811: return(false);
812: exception
813: when others then
814: rollback;
815: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
816: routineNameIn => 'hasUsage',
817: exceptionNumberIn => sqlcode,
818: exceptionStringIn => '(attribute ID ' ||
819: attributeIdIn||
825: /*
826: AME_STRIPING
827: function isAStripingAttribute(applicationIdIn in integer,
828: attributeIdIn in integer) return boolean as
829: stripingAttributeIds ame_util.idList;
830: useCount integer;
831: begin
832: select
833: to_number(value_1),
845: where
846: application_id = applicationIdIn and
847: stripe_set_id = 0 and
848: sysdate between start_date and
849: nvl(end_date - ame_util.oneSecond, sysdate) ;
850: for i in 1..5 loop
851: if(stripingAttributeIds(i) = attributeIdIn) then
852: return(true);
853: end if;
855: return(false);
856: exception
857: when others then
858: rollback;
859: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
860: routineNameIn => 'isAStripingAttribute',
861: exceptionNumberIn => sqlcode,
862: exceptionStringIn => '(attribute ID ' ||
863: attributeIdIn||
876: ame_conditions
877: where
878: attribute_id = attributeIdIn and
879: sysdate between start_date and
880: nvl(end_date - ame_util.oneSecond, sysdate) ;
881: if(useCount > 0) then
882: return(true);
883: end if;
884: return(false);
884: return(false);
885: exception
886: when others then
887: rollback;
888: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
889: routineNameIn => 'isInUse',
890: exceptionNumberIn => sqlcode,
891: exceptionStringIn => '(attribute ID ' ||
892: attributeIdIn||
905: where
906: attribute_id = attributeIdIn and
907: application_id = applicationIdIn and
908: sysdate between start_date and
909: nvl(end_date - ame_util.oneSecond, sysdate) ;
910: if(useCount > 0) then
911: return(true);
912: end if;
913: return(false);
916: rollback;
917: return(false);
918: when others then
919: rollback;
920: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
921: routineNameIn => 'isInUseByApplication',
922: exceptionNumberIn => sqlcode,
923: exceptionStringIn => '(attribute ID ' ||
924: attributeIdIn||
934: into lineItemCount
935: from ame_attributes
936: where
937: attribute_id = attributeIdIn and
938: line_item = ame_util.booleanTrue and
939: sysdate between start_date and
940: nvl(end_date - ame_util.oneSecond, sysdate) ;
941: if lineItemCount > 0 then
942: return(true);
936: where
937: attribute_id = attributeIdIn and
938: line_item = ame_util.booleanTrue and
939: sysdate between start_date and
940: nvl(end_date - ame_util.oneSecond, sysdate) ;
941: if lineItemCount > 0 then
942: return(true);
943: end if;
944: return(false);
944: return(false);
945: exception
946: when others then
947: rollback;
948: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
949: routineNameIn => 'isLineItem',
950: exceptionNumberIn => sqlcode,
951: exceptionStringIn => '(attribute ID ' ||
952: attributeIdIn||
963: from ame_mandatory_attributes
964: where action_type_id = -1 and
965: attribute_id = attributeIdIn and
966: sysdate between start_date and
967: nvl(end_date - ame_util.oneSecond, sysdate) ;
968: if mandatoryCount > 0 then
969: return(true);
970: end if;
971: return(false);
971: return(false);
972: exception
973: when others then
974: rollback;
975: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
976: routineNameIn => 'isMandatory',
977: exceptionNumberIn => sqlcode,
978: exceptionStringIn => '(attribute ID ' ||
979: attributeIdIn||
990: from ame_attributes
991: where
992: attribute_id = attributeIdIn and
993: sysdate between start_date and
994: nvl(end_date - ame_util.oneSecond, sysdate);
995: if(itemClassId <> ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
996: ame_util.headerItemClassName)) then
997: return(true);
998: end if;
992: attribute_id = attributeIdIn and
993: sysdate between start_date and
994: nvl(end_date - ame_util.oneSecond, sysdate);
995: if(itemClassId <> ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
996: ame_util.headerItemClassName)) then
997: return(true);
998: end if;
999: return(false);
1000: exception
999: return(false);
1000: exception
1001: when others then
1002: rollback;
1003: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1004: routineNameIn => 'isNonHeaderAttributeItem',
1005: exceptionNumberIn => sqlcode,
1006: exceptionStringIn => '(attribute ID ' ||
1007: attributeIdIn||
1019: where
1020: action_type_id <> -1 and
1021: attribute_id = attributeIdIn and
1022: sysdate between start_date and
1023: nvl(end_date - ame_util.oneSecond, sysdate) ;
1024: if requiredCount > 0 then
1025: return(true);
1026: end if;
1027: return(false);
1027: return(false);
1028: exception
1029: when others then
1030: rollback;
1031: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1032: routineNameIn => 'isRequired',
1033: exceptionNumberIn => sqlcode,
1034: exceptionStringIn => '(attribute ID ' ||
1035: attributeIdIn||
1046: from ame_attributes
1047: where
1048: attribute_id = attributeIdIn and
1049: sysdate between start_date and
1050: nvl(end_date - ame_util.oneSecond, sysdate) ;
1051: if(createdByValue = 1) then
1052: return(true);
1053: end if;
1054: return(false);
1054: return(false);
1055: exception
1056: when others then
1057: rollback;
1058: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1059: routineNameIn => 'isSeeded',
1060: exceptionNumberIn => sqlcode,
1061: exceptionStringIn => '(attribute ID ' ||
1062: attributeIdIn||
1078: attribute_id = attributeIdIn and
1079: application_id = applicationIdIn and
1080: (start_date <= sysdate and
1081: (end_date is null or sysdate < end_date));
1082: if(isStripingAttribute = ame_util.booleanTrue) then
1083: return(true);
1084: end if;
1085: return(false);
1086: exception
1088: rollback;
1089: return(false);
1090: when others then
1091: rollback;
1092: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1093: routineNameIn => 'isStripingAttribute',
1094: exceptionNumberIn => sqlcode,
1095: exceptionStringIn => '(attribute ID ' ||
1096: attributeIdIn||
1108: from ame_attributes
1109: where
1110: name = upper(nameIn) and
1111: sysdate between start_date and
1112: nvl(end_date - ame_util.oneSecond, sysdate) ;
1113: if(tempCount > 0) then
1114: return(true);
1115: end if;
1116: return(false);
1116: return(false);
1117: exception
1118: when others then
1119: rollback;
1120: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1121: routineNameIn => 'nameExists',
1122: exceptionNumberIn => sqlcode,
1123: exceptionStringIn => sqlerrm);
1124: raise;
1139: createdBy integer;
1140: currentUserId integer;
1141: descriptionLengthException exception;
1142: errorCode integer;
1143: errorMessage ame_util.longestStringType;
1144: lineItem varchar2(1);
1145: nameLengthException exception;
1146: typeLengthException exception;
1147: processingDate date;
1156: where
1157: (attributeIdIn is null or attribute_id <> attributeIdIn) and
1158: name = attributeName and
1159: sysdate between start_date and
1160: nvl(end_date - ame_util.oneSecond, sysdate) ;
1161: if attributeId is not null then
1162: raise attributeExistsException;
1163: end if;
1164: exception
1163: end if;
1164: exception
1165: when no_data_found then null;
1166: end;
1167: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1168: columnNameIn => 'name',
1169: argumentIn => attributeName)) then
1170: raise nameLengthException;
1171: end if;
1168: columnNameIn => 'name',
1169: argumentIn => attributeName)) then
1170: raise nameLengthException;
1171: end if;
1172: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1173: columnNameIn => 'attribute_type',
1174: argumentIn => typeIn)) then
1175: raise typeLengthException;
1176: end if;
1173: columnNameIn => 'attribute_type',
1174: argumentIn => typeIn)) then
1175: raise typeLengthException;
1176: end if;
1177: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1178: columnNameIn => 'description',
1179: argumentIn => descriptionIn)) then
1180: raise descriptionLengthException;
1181: end if;
1184: including the new version, should. This is a failsafe way to check
1185: whether previous versions of an already end-dated object had
1186: created_by = 1.
1187: */
1188: currentUserId := ame_util.getCurrentUserId;
1189: if(attributeIdIn is null) then
1190: if(createdByIn is null) then
1191: createdBy := currentUserId;
1192: else
1199: into tempCount
1200: from ame_attributes
1201: where
1202: attribute_id = attributeId and
1203: created_by = ame_util.seededDataCreatedById;
1204: if(tempCount > 0) then
1205: createdBy := ame_util.seededDataCreatedById;
1206: elsif(createdByIn is null) then
1207: createdBy := currentUserId;
1201: where
1202: attribute_id = attributeId and
1203: created_by = ame_util.seededDataCreatedById;
1204: if(tempCount > 0) then
1205: createdBy := ame_util.seededDataCreatedById;
1206: elsif(createdByIn is null) then
1207: createdBy := currentUserId;
1208: else
1209: createdBy := createdByIn;
1245: when attributeExistsException then
1246: rollback;
1247: errorCode := -20001;
1248: errorMessage :=
1249: ame_util.getMessage(applicationShortNameIn => 'PER',
1250: messageNameIn => 'AME_400152_ATT_NAME_EXISTS');
1251: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1252: routineNameIn => 'new',
1253: exceptionNumberIn => errorCode,
1247: errorCode := -20001;
1248: errorMessage :=
1249: ame_util.getMessage(applicationShortNameIn => 'PER',
1250: messageNameIn => 'AME_400152_ATT_NAME_EXISTS');
1251: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1252: routineNameIn => 'new',
1253: exceptionNumberIn => errorCode,
1254: exceptionStringIn => errorMessage);
1255: raise_application_error(errorCode,
1258: when nameLengthException then
1259: rollback;
1260: errorCode := -20001;
1261: errorMessage :=
1262: ame_util.getMessage(applicationShortNameIn => 'PER',
1263: messageNameIn => 'AME_400153_ATT_NAME_LONG',
1264: tokenNameOneIn => 'COLUMN_LENGTH',
1265: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1266: columnNameIn => 'name'));
1261: errorMessage :=
1262: ame_util.getMessage(applicationShortNameIn => 'PER',
1263: messageNameIn => 'AME_400153_ATT_NAME_LONG',
1264: tokenNameOneIn => 'COLUMN_LENGTH',
1265: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1266: columnNameIn => 'name'));
1267: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1268: routineNameIn => 'new',
1269: exceptionNumberIn => errorCode,
1263: messageNameIn => 'AME_400153_ATT_NAME_LONG',
1264: tokenNameOneIn => 'COLUMN_LENGTH',
1265: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1266: columnNameIn => 'name'));
1267: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1268: routineNameIn => 'new',
1269: exceptionNumberIn => errorCode,
1270: exceptionStringIn => errorMessage);
1271: raise_application_error(errorCode,
1274: when typeLengthException then
1275: rollback;
1276: errorCode := -20001;
1277: errorMessage :=
1278: ame_util.getMessage(applicationShortNameIn => 'PER',
1279: messageNameIn => 'AME_400154_ATT_TYPE_LONG',
1280: tokenNameOneIn => 'COLUMN_LENGTH',
1281: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1282: columnNameIn => 'attribute_type'));
1277: errorMessage :=
1278: ame_util.getMessage(applicationShortNameIn => 'PER',
1279: messageNameIn => 'AME_400154_ATT_TYPE_LONG',
1280: tokenNameOneIn => 'COLUMN_LENGTH',
1281: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1282: columnNameIn => 'attribute_type'));
1283: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1284: routineNameIn => 'new',
1285: exceptionNumberIn => errorCode,
1279: messageNameIn => 'AME_400154_ATT_TYPE_LONG',
1280: tokenNameOneIn => 'COLUMN_LENGTH',
1281: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1282: columnNameIn => 'attribute_type'));
1283: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1284: routineNameIn => 'new',
1285: exceptionNumberIn => errorCode,
1286: exceptionStringIn => errorMessage);
1287: raise_application_error(errorCode,
1290: when descriptionLengthException then
1291: rollback;
1292: errorCode := -20001;
1293: errorMessage :=
1294: ame_util.getMessage(applicationShortNameIn => 'PER',
1295: messageNameIn => 'AME_400155_ATT_DESC_LONG',
1296: tokenNameOneIn => 'COLUMN_LENGTH',
1297: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1298: columnNameIn => 'description'));
1293: errorMessage :=
1294: ame_util.getMessage(applicationShortNameIn => 'PER',
1295: messageNameIn => 'AME_400155_ATT_DESC_LONG',
1296: tokenNameOneIn => 'COLUMN_LENGTH',
1297: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1298: columnNameIn => 'description'));
1299: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1300: routineNameIn => 'new',
1301: exceptionNumberIn => errorCode,
1295: messageNameIn => 'AME_400155_ATT_DESC_LONG',
1296: tokenNameOneIn => 'COLUMN_LENGTH',
1297: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1298: columnNameIn => 'description'));
1299: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1300: routineNameIn => 'new',
1301: exceptionNumberIn => errorCode,
1302: exceptionStringIn => errorMessage);
1303: raise_application_error(errorCode,
1304: errorMessage);
1305: return(null);
1306: when others then
1307: rollback;
1308: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1309: routineNameIn => 'new',
1310: exceptionNumberIn => sqlcode,
1311: exceptionStringIn => '(attribute ID ' ||
1312: attributeIdIn||
1327: from ame_attribute_usages
1328: where attribute_id = attributeIdIn and
1329: application_id = applicationIdIn and
1330: sysdate between start_date and
1331: nvl(end_date - ame_util.oneSecond, sysdate) ;
1332: if isEditable = ame_util.booleanTrue then
1333: return(true);
1334: end if;
1335: return(false);
1328: where attribute_id = attributeIdIn and
1329: application_id = applicationIdIn and
1330: sysdate between start_date and
1331: nvl(end_date - ame_util.oneSecond, sysdate) ;
1332: if isEditable = ame_util.booleanTrue then
1333: return(true);
1334: end if;
1335: return(false);
1336: exception
1335: return(false);
1336: exception
1337: when others then
1338: rollback;
1339: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1340: routineNameIn => 'usageIsUserEditable',
1341: exceptionNumberIn => sqlcode,
1342: exceptionStringIn => '(attribute ID ' ||
1343: attributeIdIn||
1358: approverTypeId integer;
1359: attributeId integer;
1360: currentUserId integer;
1361: begin
1362: currentUserId := ame_util.getCurrentUserId;
1363: approverTypeId := getApproverTypeId(attributeIdIn => attributeIdIn);
1364: update ame_attributes
1365: set
1366: last_updated_by = currentUserId,
1369: end_date = endDateIn
1370: where
1371: attribute_id = attributeIdIn and
1372: sysdate between start_date and
1373: nvl(end_date - ame_util.oneSecond, sysdate);
1374: attributeId := new(nameIn => nameIn,
1375: typeIn => typeIn,
1376: descriptionIn => descriptionIn,
1377: attributeIdIn => attributeIdIn,
1384: end if;
1385: exception
1386: when others then
1387: rollback;
1388: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1389: routineNameIn => 'change',
1390: exceptionNumberIn => sqlcode,
1391: exceptionStringIn => '(attribute ID ' ||
1392: attributeIdIn||
1409: from ame_attributes
1410: where
1411: attribute_id = attributeIdIn and
1412: sysdate between start_date and
1413: nvl(end_date - ame_util.oneSecond, sysdate)
1414: for update;
1415: cursor startDateCursor2 is
1416: select start_date
1417: from ame_attribute_usages
1418: where
1419: attribute_id = attributeIdIn and
1420: application_id = applicationIdIn and
1421: sysdate between start_date and
1422: nvl(end_date - ame_util.oneSecond, sysdate)
1423: for update;
1424: attributeId integer;
1425: currentUserId integer;
1426: errorCode integer;
1423: for update;
1424: attributeId integer;
1425: currentUserId integer;
1426: errorCode integer;
1427: errorMessage ame_util.longestStringType;
1428: name ame_attributes.name%type;
1429: attributeType ame_attributes.attribute_type%type;
1430: description ame_attributes.description%type;
1431: invalidReferenceException exception;
1443: raise objectVersionNoDataException;
1444: end if;
1445: if(parentVersionStartDateIn <> startDate) then
1446: close startDateCursor;
1447: raise ame_util.objectVersionException;
1448: end if;
1449: open startDateCursor2;
1450: fetch startDateCursor2 into startDate2;
1451: if startDateCursor2%notfound then
1452: raise objectVersionNoDataException;
1453: end if;
1454: if(childVersionStartDateIn <> startDate2) then
1455: close startDateCursor2;
1456: raise ame_util.objectVersionException;
1457: end if;
1458: attributeType := getType(attributeIdIn => attributeIdIn);
1459: if(staticUsageIn = ame_util.booleanTrue) then
1460: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1455: close startDateCursor2;
1456: raise ame_util.objectVersionException;
1457: end if;
1458: attributeType := getType(attributeIdIn => attributeIdIn);
1459: if(staticUsageIn = ame_util.booleanTrue) then
1460: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1461: replaceWithSpaces => false);
1462: else
1463: queryString := queryStringIn;
1456: raise ame_util.objectVersionException;
1457: end if;
1458: attributeType := getType(attributeIdIn => attributeIdIn);
1459: if(staticUsageIn = ame_util.booleanTrue) then
1460: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1461: replaceWithSpaces => false);
1462: else
1463: queryString := queryStringIn;
1464: end if;
1477: (nameIn is null or name = upper(nameIn)) and
1478: (attributeType is null or upper(attribute_type) = upper(attributeType)) and
1479: (descriptionIn is null or description = descriptionIn) and
1480: sysdate between ame_attributes.start_date and
1481: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1482: sysdate between ame_attribute_usages.start_date and
1483: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) ;
1484: if(tempCount > 0) then
1485: return;
1479: (descriptionIn is null or description = descriptionIn) and
1480: sysdate between ame_attributes.start_date and
1481: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1482: sysdate between ame_attribute_usages.start_date and
1483: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) ;
1484: if(tempCount > 0) then
1485: return;
1486: end if;
1487: /* Get current values as necessary for update. */
1517: if(finalizeIn) then
1518: commit;
1519: end if;
1520: exception
1521: when ame_util.objectVersionException then
1522: rollback;
1523: if(startDateCursor%isOpen) then
1524: close startDateCursor;
1525: end if;
1527: close startDateCursor2;
1528: end if;
1529: errorCode := -20001;
1530: errorMessage :=
1531: ame_util.getMessage(applicationShortNameIn => 'PER',
1532: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1533: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1534: routineNameIn => 'changeAttributeAndUsage',
1535: exceptionNumberIn => errorCode,
1529: errorCode := -20001;
1530: errorMessage :=
1531: ame_util.getMessage(applicationShortNameIn => 'PER',
1532: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1533: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1534: routineNameIn => 'changeAttributeAndUsage',
1535: exceptionNumberIn => errorCode,
1536: exceptionStringIn => errorMessage);
1537: raise_application_error(errorCode,
1545: close startDateCursor2;
1546: end if;
1547: errorCode := -20001;
1548: errorMessage :=
1549: ame_util.getMessage(applicationShortNameIn => 'PER',
1550: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1551: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1552: routineNameIn => 'changeAttributeAndUsage',
1553: exceptionNumberIn => errorCode,
1547: errorCode := -20001;
1548: errorMessage :=
1549: ame_util.getMessage(applicationShortNameIn => 'PER',
1550: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1551: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1552: routineNameIn => 'changeAttributeAndUsage',
1553: exceptionNumberIn => errorCode,
1554: exceptionStringIn => errorMessage);
1555: raise_application_error(errorCode,
1563: close startDateCursor2;
1564: end if;
1565: errorCode := -20001;
1566: errorMessage :=
1567: ame_util.getMessage(applicationShortNameIn => 'PER',
1568: messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
1569: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1570: routineNameIn => 'changeAttributeAndUsage',
1571: exceptionNumberIn => errorCode,
1565: errorCode := -20001;
1566: errorMessage :=
1567: ame_util.getMessage(applicationShortNameIn => 'PER',
1568: messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
1569: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1570: routineNameIn => 'changeAttributeAndUsage',
1571: exceptionNumberIn => errorCode,
1572: exceptionStringIn => errorMessage);
1573: raise_application_error(errorCode,
1579: end if;
1580: if(startDateCursor2%isOpen) then
1581: close startDateCursor2;
1582: end if;
1583: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1584: routineNameIn => 'changeAttributeAndUsage',
1585: exceptionNumberIn => sqlcode,
1586: exceptionStringIn => '(attribute ID ' ||
1587: attributeIdIn||
1613: comma1Location integer;
1614: comma2Location integer;
1615: currentUserId integer;
1616: errorCode integer;
1617: errorMessage ame_util.longestStringType;
1618: firstReturnLocation integer;
1619: loweredQueryString varchar2(4000);
1620: queryString ame_attribute_usages.query_string%type;
1621: queryStringColumnException exception;
1622: tempCount integer;
1623: transactionType ame_calling_apps.application_name%type;
1624: begin
1625: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
1626: if(staticUsageIn = ame_util.booleanTrue) then
1627: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1628: replaceWithSpaces => false);
1629: if(attributeType = ame_util.numberAttributeType) then
1630: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1623: transactionType ame_calling_apps.application_name%type;
1624: begin
1625: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
1626: if(staticUsageIn = ame_util.booleanTrue) then
1627: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1628: replaceWithSpaces => false);
1629: if(attributeType = ame_util.numberAttributeType) then
1630: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1631: end if;
1625: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
1626: if(staticUsageIn = ame_util.booleanTrue) then
1627: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1628: replaceWithSpaces => false);
1629: if(attributeType = ame_util.numberAttributeType) then
1630: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1631: end if;
1632: else
1633: queryString := queryStringIn;
1626: if(staticUsageIn = ame_util.booleanTrue) then
1627: queryString := ame_util.removeReturns(stringIn => queryStringIn,
1628: replaceWithSpaces => false);
1629: if(attributeType = ame_util.numberAttributeType) then
1630: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1631: end if;
1632: else
1633: queryString := queryStringIn;
1634: if(attributeType = ame_util.currencyAttributeType) then
1630: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1631: end if;
1632: else
1633: queryString := queryStringIn;
1634: if(attributeType = ame_util.currencyAttributeType) then
1635: loweredQueryString := lower(queryString);
1636: if(instrb(loweredQueryString, ',', 1, 2) = 0 or
1637: instrb(loweredQueryString, ',', 1, 2) > instrb(loweredQueryString, 'from', 1, 1)) then
1638: raise queryStringColumnException;
1638: raise queryStringColumnException;
1639: end if;
1640: end if;
1641: end if;
1642: currentUserId := ame_util.getCurrentUserId;
1643: update ame_attribute_usages
1644: set
1645: last_updated_by = currentUserId,
1646: last_update_date = endDateIn,
1649: where
1650: attribute_id = attributeIdIn and
1651: application_id = applicationIdIn and
1652: sysdate between start_date and
1653: nvl(end_date - ame_util.oneSecond, sysdate) ;
1654: /*
1655: AME_STRIPING
1656: newAttributeUsage(attributeIdIn => attributeIdIn,
1657: applicationIdIn => applicationIdIn,
1675: errorCode := -20001;
1676: errorMessage := 'The select clause of a currency attribute''s ' ||
1677: 'usage must select three values: ' ||
1678: 'amount, currency code, and conversion-type code ';
1679: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1680: routineNameIn => 'changeUsage',
1681: exceptionNumberIn => errorCode,
1682: exceptionStringIn => errorMessage);
1683: raise_application_error(errorCode,
1683: raise_application_error(errorCode,
1684: errorMessage);
1685: when others then
1686: rollback;
1687: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1688: routineNameIn => 'changeUsage',
1689: exceptionNumberIn => sqlcode,
1690: exceptionStringIn => '(attribute ID ' ||
1691: attributeIdIn||
1693: sqlerrm);
1694: raise;
1695: end changeUsage;
1696: procedure getActiveAttributes(applicationIdIn in integer,
1697: attributeIdsOut out nocopy ame_util.idList,
1698: attributeNamesOut out nocopy ame_util.stringList) as
1699: cursor activeAttributeCursor(applicationIdIn in integer) is
1700: select
1701: ame_attributes.attribute_id attribute_id,
1694: raise;
1695: end changeUsage;
1696: procedure getActiveAttributes(applicationIdIn in integer,
1697: attributeIdsOut out nocopy ame_util.idList,
1698: attributeNamesOut out nocopy ame_util.stringList) as
1699: cursor activeAttributeCursor(applicationIdIn in integer) is
1700: select
1701: ame_attributes.attribute_id attribute_id,
1702: ame_attributes.name name
1707: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1708: ame_attribute_usages.application_id = applicationIdIn and
1709: use_count > 0 and
1710: sysdate between ame_attributes.start_date and
1711: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1712: sysdate between ame_attribute_usages.start_date and
1713: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1714: order by name;
1715: cursor mandatoryAttributeCursor(applicationIdIn in integer) is
1709: use_count > 0 and
1710: sysdate between ame_attributes.start_date and
1711: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1712: sysdate between ame_attribute_usages.start_date and
1713: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1714: order by name;
1715: cursor mandatoryAttributeCursor(applicationIdIn in integer) is
1716: select
1717: ame_attributes.attribute_id attribute_id,
1722: where
1723: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
1724: ame_mandatory_attributes.action_type_id = -1 and
1725: sysdate between ame_attributes.start_date and
1726: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1727: sysdate between ame_mandatory_attributes.start_date and
1728: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1729: order by name;
1730: tempIndex integer;
1724: ame_mandatory_attributes.action_type_id = -1 and
1725: sysdate between ame_attributes.start_date and
1726: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1727: sysdate between ame_mandatory_attributes.start_date and
1728: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1729: order by name;
1730: tempIndex integer;
1731: begin
1732: tempIndex := 1;
1742: end loop;
1743: exception
1744: when others then
1745: rollback;
1746: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1747: routineNameIn => 'getActiveAttributes',
1748: exceptionNumberIn => sqlcode,
1749: exceptionStringIn => '(application ID ' ||
1750: applicationIdIn||
1752: sqlerrm);
1753: raise;
1754: end getActiveAttributes;
1755: procedure getActiveHeaderAttributes(applicationIdIn in integer,
1756: attributeIdsOut out nocopy ame_util.idList,
1757: attributeNamesOut out nocopy ame_util.stringList) as
1758: cursor activeAttributeCursor(applicationIdIn in integer,
1759: itemClassIdIn in integer) is
1760: select
1753: raise;
1754: end getActiveAttributes;
1755: procedure getActiveHeaderAttributes(applicationIdIn in integer,
1756: attributeIdsOut out nocopy ame_util.idList,
1757: attributeNamesOut out nocopy ame_util.stringList) as
1758: cursor activeAttributeCursor(applicationIdIn in integer,
1759: itemClassIdIn in integer) is
1760: select
1761: ame_attributes.attribute_id attribute_id,
1768: ame_attribute_usages.application_id = applicationIdIn and
1769: ame_attributes.item_class_id = itemClassIdIn and
1770: use_count > 0 and
1771: sysdate between ame_attributes.start_date and
1772: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1773: sysdate between ame_attribute_usages.start_date and
1774: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1775: order by name;
1776: cursor mandatoryAttributeCursor(applicationIdIn in integer,
1770: use_count > 0 and
1771: sysdate between ame_attributes.start_date and
1772: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1773: sysdate between ame_attribute_usages.start_date and
1774: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1775: order by name;
1776: cursor mandatoryAttributeCursor(applicationIdIn in integer,
1777: itemClassIdIn in integer) is
1778: select
1785: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
1786: ame_attributes.item_class_id = itemClassIdIn and
1787: ame_mandatory_attributes.action_type_id = -1 and
1788: sysdate between ame_attributes.start_date and
1789: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1790: sysdate between ame_mandatory_attributes.start_date and
1791: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1792: order by name;
1793: itemClassId integer;
1787: ame_mandatory_attributes.action_type_id = -1 and
1788: sysdate between ame_attributes.start_date and
1789: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1790: sysdate between ame_mandatory_attributes.start_date and
1791: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1792: order by name;
1793: itemClassId integer;
1794: tempIndex integer;
1795: begin
1794: tempIndex integer;
1795: begin
1796: tempIndex := 1;
1797: itemClassId :=
1798: ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerITemClassName);
1799: for tempAttribute in mandatoryAttributeCursor(applicationIdIn => applicationIdIn,
1800: itemClassIdIn => itemClassId) loop
1801: attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
1802: attributeNamesOut(tempIndex) := tempAttribute.name;
1810: end loop;
1811: exception
1812: when others then
1813: rollback;
1814: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1815: routineNameIn => 'getActiveHeaderAttributes',
1816: exceptionNumberIn => sqlcode,
1817: exceptionStringIn => '(application ID ' ||
1818: applicationIdIn||
1819: ') ' ||
1820: sqlerrm);
1821: raise;
1822: end getActiveHeaderAttributes;
1823: procedure getAllAttributes(attributeIdsOut out nocopy ame_util.stringList,
1824: attributeNamesOut out nocopy ame_util.stringList) as
1825: cursor attributeCursor is
1826: select attribute_id, name
1827: from ame_attributes
1820: sqlerrm);
1821: raise;
1822: end getActiveHeaderAttributes;
1823: procedure getAllAttributes(attributeIdsOut out nocopy ame_util.stringList,
1824: attributeNamesOut out nocopy ame_util.stringList) as
1825: cursor attributeCursor is
1826: select attribute_id, name
1827: from ame_attributes
1828: where
1826: select attribute_id, name
1827: from ame_attributes
1828: where
1829: sysdate between start_date and
1830: nvl(end_date - ame_util.oneSecond, sysdate)
1831: order by name;
1832: tempIndex integer;
1833: begin
1834: tempIndex := 1;
1840: end loop;
1841: exception
1842: when others then
1843: rollback;
1844: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1845: routineNameIn => 'getAllAttributes',
1846: exceptionNumberIn => sqlcode,
1847: exceptionStringIn => sqlerrm);
1848: raise;
1847: exceptionStringIn => sqlerrm);
1848: raise;
1849: end getAllAttributes;
1850: procedure getApplicationAttributes(applicationIdIn in integer,
1851: attributeIdOut out nocopy ame_util.idList) as
1852: cursor attributeCursor(applicationIdIn in integer) is
1853: select attribute_id
1854: from ame_attribute_usages
1855: where
1854: from ame_attribute_usages
1855: where
1856: application_id = applicationIdIn and
1857: sysdate between start_date and
1858: nvl(end_date - ame_util.oneSecond, sysdate)
1859: order by attribute_id;
1860: tempIndex integer;
1861: begin
1862: tempIndex := 1;
1866: end loop;
1867: exception
1868: when others then
1869: rollback;
1870: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1871: routineNameIn => 'getApplicationAttributes',
1872: exceptionNumberIn => sqlcode,
1873: exceptionStringIn => '(applicationID ' ||
1874: applicationIdIn||
1877: raise;
1878: end getApplicationAttributes;
1879: procedure getApplicationAttributes2(applicationIdIn in integer,
1880: itemClassIdIn in integer,
1881: attributeIdOut out nocopy ame_util.stringList,
1882: attributeNameOut out nocopy ame_util.stringList) as
1883: cursor attributeCursor(applicationIdIn in integer) is
1884: select
1885: ame_attributes.attribute_id id,
1878: end getApplicationAttributes;
1879: procedure getApplicationAttributes2(applicationIdIn in integer,
1880: itemClassIdIn in integer,
1881: attributeIdOut out nocopy ame_util.stringList,
1882: attributeNameOut out nocopy ame_util.stringList) as
1883: cursor attributeCursor(applicationIdIn in integer) is
1884: select
1885: ame_attributes.attribute_id id,
1886: ame_attributes.name name
1892: ame_attributes.item_class_id = itemClassIdIn and
1893: ame_attribute_usages.application_id = applicationIdIn and
1894: ame_attributes.attribute_id not in (select attribute_id
1895: from ame_mandatory_attributes
1896: where action_type_id = ame_util.mandAttActionTypeId and
1897: sysdate between start_date and
1898: nvl(end_date - ame_util.oneSecond, sysdate)) and
1899: sysdate between ame_attributes.start_date and
1900: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1894: ame_attributes.attribute_id not in (select attribute_id
1895: from ame_mandatory_attributes
1896: where action_type_id = ame_util.mandAttActionTypeId and
1897: sysdate between start_date and
1898: nvl(end_date - ame_util.oneSecond, sysdate)) and
1899: sysdate between ame_attributes.start_date and
1900: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1901: sysdate between ame_attribute_usages.start_date and
1902: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1896: where action_type_id = ame_util.mandAttActionTypeId and
1897: sysdate between start_date and
1898: nvl(end_date - ame_util.oneSecond, sysdate)) and
1899: sysdate between ame_attributes.start_date and
1900: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1901: sysdate between ame_attribute_usages.start_date and
1902: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1903: order by name;
1904: tempIndex integer;
1898: nvl(end_date - ame_util.oneSecond, sysdate)) and
1899: sysdate between ame_attributes.start_date and
1900: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1901: sysdate between ame_attribute_usages.start_date and
1902: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1903: order by name;
1904: tempIndex integer;
1905: begin
1906: tempIndex := 1;
1912: end loop;
1913: exception
1914: when others then
1915: rollback;
1916: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1917: routineNameIn => 'getApplicationAttributes2',
1918: exceptionNumberIn => sqlcode,
1919: exceptionStringIn => '(application ID ' ||
1920: applicationIdIn||
1924: end getApplicationAttributes2;
1925: /*
1926: AME_STRIPING
1927: procedure getApplicationAttributes3(applicationIdIn in integer,
1928: attributeIdsOut out nocopy ame_util.stringList,
1929: attributeNamesOut out nocopy ame_util.stringList) as
1930: cursor applicationAttributeCursor(applicationIdIn in integer) is
1931: select
1932: ame_attributes.attribute_id attribute_id,
1925: /*
1926: AME_STRIPING
1927: procedure getApplicationAttributes3(applicationIdIn in integer,
1928: attributeIdsOut out nocopy ame_util.stringList,
1929: attributeNamesOut out nocopy ame_util.stringList) as
1930: cursor applicationAttributeCursor(applicationIdIn in integer) is
1931: select
1932: ame_attributes.attribute_id attribute_id,
1933: ame_attributes.name name
1936: ame_attribute_usages
1937: where
1938: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1939: ame_attribute_usages.application_id = applicationIdIn and
1940: ame_attributes.attribute_type = ame_util.stringAttributeType and
1941: sysdate between ame_attributes.start_date and
1942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1943: sysdate between ame_attribute_usages.start_date and
1944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1938: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1939: ame_attribute_usages.application_id = applicationIdIn and
1940: ame_attributes.attribute_type = ame_util.stringAttributeType and
1941: sysdate between ame_attributes.start_date and
1942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1943: sysdate between ame_attribute_usages.start_date and
1944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1945: order by name;
1946: attributeIdList ame_util.idList;
1940: ame_attributes.attribute_type = ame_util.stringAttributeType and
1941: sysdate between ame_attributes.start_date and
1942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1943: sysdate between ame_attribute_usages.start_date and
1944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1945: order by name;
1946: attributeIdList ame_util.idList;
1947: tempCount integer;
1948: tempIndex integer;
1942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1943: sysdate between ame_attribute_usages.start_date and
1944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1945: order by name;
1946: attributeIdList ame_util.idList;
1947: tempCount integer;
1948: tempIndex integer;
1949: begin
1950: begin
1963: where
1964: application_id = applicationIdIn and
1965: stripe_set_id = 0 and
1966: sysdate between start_date and
1967: nvl(end_date - ame_util.oneSecond, sysdate) ;
1968: exception
1969: when no_data_found then */ /* striping is not on, user needs to select
1970: from entire string attribute list */
1971: /*
2003: end loop;
2004: exception
2005: when others then
2006: rollback;
2007: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2008: routineNameIn => 'getApplicationAttributes3',
2009: exceptionNumberIn => sqlcode,
2010: exceptionStringIn => '(application ID ' ||
2011: applicationIdIn||
2010: exceptionStringIn => '(application ID ' ||
2011: applicationIdIn||
2012: ') ' ||
2013: sqlerrm);
2014: attributeIdsOut := ame_util.emptyStringList;
2015: attributeNamesOut := ame_util.emptyStringList;
2016: raise;
2017: end getApplicationAttributes3;
2018: */
2011: applicationIdIn||
2012: ') ' ||
2013: sqlerrm);
2014: attributeIdsOut := ame_util.emptyStringList;
2015: attributeNamesOut := ame_util.emptyStringList;
2016: raise;
2017: end getApplicationAttributes3;
2018: */
2019: procedure getAttributes(applicationIdIn in integer,
2017: end getApplicationAttributes3;
2018: */
2019: procedure getAttributes(applicationIdIn in integer,
2020: ruleTypeIn in integer,
2021: lineItemIn in varchar2 default ame_util.booleanFalse,
2022: attributeIdOut out nocopy ame_util.stringList,
2023: attributeNameOut out nocopy ame_util.stringList) as
2024: cursor attributeCursor(applicationIdIn in integer,
2025: ruleTypeIn in integer,
2018: */
2019: procedure getAttributes(applicationIdIn in integer,
2020: ruleTypeIn in integer,
2021: lineItemIn in varchar2 default ame_util.booleanFalse,
2022: attributeIdOut out nocopy ame_util.stringList,
2023: attributeNameOut out nocopy ame_util.stringList) as
2024: cursor attributeCursor(applicationIdIn in integer,
2025: ruleTypeIn in integer,
2026: lineItemIn in varchar2) is
2019: procedure getAttributes(applicationIdIn in integer,
2020: ruleTypeIn in integer,
2021: lineItemIn in varchar2 default ame_util.booleanFalse,
2022: attributeIdOut out nocopy ame_util.stringList,
2023: attributeNameOut out nocopy ame_util.stringList) as
2024: cursor attributeCursor(applicationIdIn in integer,
2025: ruleTypeIn in integer,
2026: lineItemIn in varchar2) is
2027: /* the distinct below is necessary to select a distinct list of attribute
2036: where
2037: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2038: ame_conditions.attribute_id = ame_attributes.attribute_id and
2039: ame_attribute_usages.application_id = applicationIdIn and
2040: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2041: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2042: sysdate between ame_attributes.start_date and
2043: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044: sysdate between ame_attribute_usages.start_date and
2037: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2038: ame_conditions.attribute_id = ame_attributes.attribute_id and
2039: ame_attribute_usages.application_id = applicationIdIn and
2040: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2041: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2042: sysdate between ame_attributes.start_date and
2043: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044: sysdate between ame_attribute_usages.start_date and
2045: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2039: ame_attribute_usages.application_id = applicationIdIn and
2040: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2041: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2042: sysdate between ame_attributes.start_date and
2043: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044: sysdate between ame_attribute_usages.start_date and
2045: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2046: sysdate between ame_conditions.start_date and
2047: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2041: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2042: sysdate between ame_attributes.start_date and
2043: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044: sysdate between ame_attribute_usages.start_date and
2045: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2046: sysdate between ame_conditions.start_date and
2047: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2048: order by name;
2049: tempIndex integer;
2043: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044: sysdate between ame_attribute_usages.start_date and
2045: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2046: sysdate between ame_conditions.start_date and
2047: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2048: order by name;
2049: tempIndex integer;
2050: begin
2051: tempIndex := 1;
2059: end loop;
2060: exception
2061: when others then
2062: rollback;
2063: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2064: routineNameIn => 'getAttributes',
2065: exceptionNumberIn => sqlcode,
2066: exceptionStringIn => '(application ID ' ||
2067: applicationIdIn||
2071: end getAttributes;
2072: procedure getAttributes2(applicationIdIn in integer,
2073: itemClassIdIn in integer,
2074: ruleTypeIn in integer,
2075: lineItemIn in varchar2 default ame_util.booleanFalse,
2076: attributeIdOut out nocopy ame_util.stringList,
2077: attributeNameOut out nocopy ame_util.stringList) as
2078: cursor attributeCursor(applicationIdIn in integer,
2079: itemClassIdIn in integer,
2072: procedure getAttributes2(applicationIdIn in integer,
2073: itemClassIdIn in integer,
2074: ruleTypeIn in integer,
2075: lineItemIn in varchar2 default ame_util.booleanFalse,
2076: attributeIdOut out nocopy ame_util.stringList,
2077: attributeNameOut out nocopy ame_util.stringList) as
2078: cursor attributeCursor(applicationIdIn in integer,
2079: itemClassIdIn in integer,
2080: ruleTypeIn in integer,
2073: itemClassIdIn in integer,
2074: ruleTypeIn in integer,
2075: lineItemIn in varchar2 default ame_util.booleanFalse,
2076: attributeIdOut out nocopy ame_util.stringList,
2077: attributeNameOut out nocopy ame_util.stringList) as
2078: cursor attributeCursor(applicationIdIn in integer,
2079: itemClassIdIn in integer,
2080: ruleTypeIn in integer,
2081: lineItemIn in varchar2) is
2092: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2093: ame_conditions.attribute_id = ame_attributes.attribute_id and
2094: ame_attribute_usages.application_id = applicationIdIn and
2095: ame_attributes.item_class_id = itemClassIdIn and
2096: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2097: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2098: sysdate between ame_attributes.start_date and
2099: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100: sysdate between ame_attribute_usages.start_date and
2093: ame_conditions.attribute_id = ame_attributes.attribute_id and
2094: ame_attribute_usages.application_id = applicationIdIn and
2095: ame_attributes.item_class_id = itemClassIdIn and
2096: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2097: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2098: sysdate between ame_attributes.start_date and
2099: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100: sysdate between ame_attribute_usages.start_date and
2101: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2095: ame_attributes.item_class_id = itemClassIdIn and
2096: nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2097: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2098: sysdate between ame_attributes.start_date and
2099: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100: sysdate between ame_attribute_usages.start_date and
2101: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2102: sysdate between ame_conditions.start_date and
2103: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2097: ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2098: sysdate between ame_attributes.start_date and
2099: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100: sysdate between ame_attribute_usages.start_date and
2101: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2102: sysdate between ame_conditions.start_date and
2103: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2104: order by name;
2105: tempIndex integer;
2099: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100: sysdate between ame_attribute_usages.start_date and
2101: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2102: sysdate between ame_conditions.start_date and
2103: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2104: order by name;
2105: tempIndex integer;
2106: begin
2107: tempIndex := 1;
2116: end loop;
2117: exception
2118: when others then
2119: rollback;
2120: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2121: routineNameIn => 'getAttributes2',
2122: exceptionNumberIn => sqlcode,
2123: exceptionStringIn => '(application ID ' ||
2124: applicationIdIn||
2130: ruleIdIn in integer,
2131: itemClassIdIn in integer,
2132: conditionTypeIn in varchar2,
2133: ruleTypeIn in integer,
2134: attributeIdOut out nocopy ame_util.stringList,
2135: attributeNameOut out nocopy ame_util.stringList) as
2136: cursor attributeCursor(applicationIdIn in integer,
2137: itemClassIdIn in integer,
2138: conditionTypeIn in varchar2,
2131: itemClassIdIn in integer,
2132: conditionTypeIn in varchar2,
2133: ruleTypeIn in integer,
2134: attributeIdOut out nocopy ame_util.stringList,
2135: attributeNameOut out nocopy ame_util.stringList) as
2136: cursor attributeCursor(applicationIdIn in integer,
2137: itemClassIdIn in integer,
2138: conditionTypeIn in varchar2,
2139: ruleTypeIn in integer) is
2152: ame_attribute_usages.application_id = applicationIdIn and
2153: ame_attributes.item_class_id = itemClassIdIn and
2154: ame_conditions.condition_type = conditionTypeIn and
2155: sysdate between ame_attributes.start_date and
2156: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2157: sysdate between ame_attribute_usages.start_date and
2158: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2159: sysdate between ame_conditions.start_date and
2160: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2154: ame_conditions.condition_type = conditionTypeIn and
2155: sysdate between ame_attributes.start_date and
2156: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2157: sysdate between ame_attribute_usages.start_date and
2158: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2159: sysdate between ame_conditions.start_date and
2160: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2161: order by name;
2162: attributeConditionCount integer;
2156: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2157: sysdate between ame_attribute_usages.start_date and
2158: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2159: sysdate between ame_conditions.start_date and
2160: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2161: order by name;
2162: attributeConditionCount integer;
2163: attributeConditionsInUseCount integer;
2164: tempIndex integer;
2185: end loop;
2186: exception
2187: when others then
2188: rollback;
2189: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2190: routineNameIn => 'getAttributes3',
2191: exceptionNumberIn => sqlcode,
2192: exceptionStringIn => '(application ID ' ||
2193: applicationIdIn||
2195: sqlerrm);
2196: raise;
2197: end getAttributes3;
2198: procedure getAttributeConditions(attributeIdIn in integer,
2199: conditionIdListOut out nocopy ame_util.idList) as
2200: cursor getConditionsCursor(attributeIdIn in integer) is
2201: select condition_id
2202: from ame_conditions
2203: where
2202: from ame_conditions
2203: where
2204: attribute_id = attributeIdIn and
2205: sysdate between start_date and
2206: nvl(end_date - ame_util.oneSecond, sysdate);
2207: tempIndex integer;
2208: begin
2209: tempIndex := 1;
2210: for getConditionsRec in getConditionsCursor(attributeIdIn => attributeIdIn) loop
2214: end loop;
2215: exception
2216: when others then
2217: rollback;
2218: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2219: routineNameIn => 'getAttributeConditions',
2220: exceptionNumberIn => sqlcode,
2221: exceptionStringIn => sqlerrm);
2222: raise;
2221: exceptionStringIn => sqlerrm);
2222: raise;
2223: end getAttributeConditions;
2224: procedure getAvailReqAttributes(actionTypeIdIn in integer,
2225: attributeIdOut out nocopy ame_util.stringList,
2226: attributeNameOut out nocopy ame_util.stringList) as
2227: cursor attributeCursor(actionTypeIdIn in integer) is
2228: select
2229: ame_attributes.attribute_id,
2222: raise;
2223: end getAttributeConditions;
2224: procedure getAvailReqAttributes(actionTypeIdIn in integer,
2225: attributeIdOut out nocopy ame_util.stringList,
2226: attributeNameOut out nocopy ame_util.stringList) as
2227: cursor attributeCursor(actionTypeIdIn in integer) is
2228: select
2229: ame_attributes.attribute_id,
2230: ame_attributes.name
2234: ame_attributes.attribute_id not in
2235: (select attribute_id from ame_mandatory_attributes
2236: where
2237: (action_type_id = actionTypeIdIn or
2238: action_type_id = ame_util.mandAttActionTypeId) and
2239: sysdate between ame_mandatory_attributes.start_date and
2240: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2241: sysdate between ame_attributes.start_date and
2242: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
2236: where
2237: (action_type_id = actionTypeIdIn or
2238: action_type_id = ame_util.mandAttActionTypeId) and
2239: sysdate between ame_mandatory_attributes.start_date and
2240: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2241: sysdate between ame_attributes.start_date and
2242: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
2243: order by name;
2244: tempIndex integer;
2238: action_type_id = ame_util.mandAttActionTypeId) and
2239: sysdate between ame_mandatory_attributes.start_date and
2240: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2241: sysdate between ame_attributes.start_date and
2242: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
2243: order by name;
2244: tempIndex integer;
2245: begin
2246: tempIndex := 1;
2252: end loop;
2253: exception
2254: when others then
2255: rollback;
2256: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2257: routineNameIn => 'getAvailReqAttributes',
2258: exceptionNumberIn => sqlcode,
2259: exceptionStringIn => '(action type ID ' ||
2260: actionTypeIdIn||
2262: sqlerrm);
2263: raise;
2264: end getAvailReqAttributes;
2265: procedure getHeaderICAttributes(applicationIdIn in integer,
2266: attributeIdsOut out nocopy ame_util.stringList,
2267: attributeNamesOut out nocopy ame_util.stringList) as
2268: cursor getHeaderICAttributesCursor(applicationIdIn in integer,
2269: headerItemClassIdIn in integer) is
2270: select distinct(ame_attributes.attribute_id),
2263: raise;
2264: end getAvailReqAttributes;
2265: procedure getHeaderICAttributes(applicationIdIn in integer,
2266: attributeIdsOut out nocopy ame_util.stringList,
2267: attributeNamesOut out nocopy ame_util.stringList) as
2268: cursor getHeaderICAttributesCursor(applicationIdIn in integer,
2269: headerItemClassIdIn in integer) is
2270: select distinct(ame_attributes.attribute_id),
2271: ame_attributes.name
2280: (select attribute_id from ame_mandatory_attributes
2281: where
2282: action_type_id = -1 and
2283: sysdate between ame_mandatory_attributes.start_date and
2284: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2285: sysdate between ame_attributes.start_date and
2286: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2287: sysdate between ame_attribute_usages.start_date and
2288: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2282: action_type_id = -1 and
2283: sysdate between ame_mandatory_attributes.start_date and
2284: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2285: sysdate between ame_attributes.start_date and
2286: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2287: sysdate between ame_attribute_usages.start_date and
2288: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2289: order by name;
2290: headerItemClassId integer;
2284: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2285: sysdate between ame_attributes.start_date and
2286: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2287: sysdate between ame_attribute_usages.start_date and
2288: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2289: order by name;
2290: headerItemClassId integer;
2291: tempIndex integer;
2292: begin
2291: tempIndex integer;
2292: begin
2293: headerItemClassId :=
2294: ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
2295: ame_util.headerItemClassName);
2296: open getHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2297: headerItemClassIdIn => headerItemClassId);
2298: fetch getHeaderICAttributesCursor bulk collect
2299: into attributeIdsOut,
2301: close getHeaderICAttributesCursor;
2302: exception
2303: when others then
2304: rollback;
2305: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2306: routineNameIn => 'getHeaderICAttributes',
2307: exceptionNumberIn => sqlcode,
2308: exceptionStringIn => '(application ID ' ||
2309: applicationIdIn||
2310: ') ' ||
2311: sqlerrm);
2312: raise;
2313: end getHeaderICAttributes;
2314: procedure getMandatoryAttributes(attributeIdOut out nocopy ame_util.stringList,
2315: attributeNameOut out nocopy ame_util.stringList,
2316: attributeTypeOut out nocopy ame_util.stringList,
2317: attributeStartDateOut out nocopy ame_util.stringList) as
2318: cursor attributeCursor is
2311: sqlerrm);
2312: raise;
2313: end getHeaderICAttributes;
2314: procedure getMandatoryAttributes(attributeIdOut out nocopy ame_util.stringList,
2315: attributeNameOut out nocopy ame_util.stringList,
2316: attributeTypeOut out nocopy ame_util.stringList,
2317: attributeStartDateOut out nocopy ame_util.stringList) as
2318: cursor attributeCursor is
2319: select
2312: raise;
2313: end getHeaderICAttributes;
2314: procedure getMandatoryAttributes(attributeIdOut out nocopy ame_util.stringList,
2315: attributeNameOut out nocopy ame_util.stringList,
2316: attributeTypeOut out nocopy ame_util.stringList,
2317: attributeStartDateOut out nocopy ame_util.stringList) as
2318: cursor attributeCursor is
2319: select
2320: ame_attributes.attribute_id,
2313: end getHeaderICAttributes;
2314: procedure getMandatoryAttributes(attributeIdOut out nocopy ame_util.stringList,
2315: attributeNameOut out nocopy ame_util.stringList,
2316: attributeTypeOut out nocopy ame_util.stringList,
2317: attributeStartDateOut out nocopy ame_util.stringList) as
2318: cursor attributeCursor is
2319: select
2320: ame_attributes.attribute_id,
2321: ame_attributes.attribute_type,
2325: ame_attributes,
2326: ame_mandatory_attributes
2327: where
2328: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2329: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2330: sysdate between ame_attributes.start_date and
2331: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2332: sysdate between ame_mandatory_attributes.start_date and
2333: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2327: where
2328: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2329: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2330: sysdate between ame_attributes.start_date and
2331: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2332: sysdate between ame_mandatory_attributes.start_date and
2333: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2334: order by name;
2335: tempIndex integer;
2329: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2330: sysdate between ame_attributes.start_date and
2331: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2332: sysdate between ame_mandatory_attributes.start_date and
2333: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2334: order by name;
2335: tempIndex integer;
2336: begin
2337: tempIndex := 1;
2345: end loop;
2346: exception
2347: when others then
2348: rollback;
2349: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2350: routineNameIn => 'getMandatoryAttributes',
2351: exceptionNumberIn => sqlcode,
2352: exceptionStringIn => sqlerrm);
2353: raise;
2352: exceptionStringIn => sqlerrm);
2353: raise;
2354: end getMandatoryAttributes;
2355: procedure getMandatoryAttributes2(applicationIdIn in integer,
2356: attributeIdOut out nocopy ame_util.stringList,
2357: attributeNameOut out nocopy ame_util.stringList,
2358: attributeTypeOut out nocopy ame_util.stringList) as
2359: cursor attributeCursor(applicationIdIn in integer) is
2360: select
2353: raise;
2354: end getMandatoryAttributes;
2355: procedure getMandatoryAttributes2(applicationIdIn in integer,
2356: attributeIdOut out nocopy ame_util.stringList,
2357: attributeNameOut out nocopy ame_util.stringList,
2358: attributeTypeOut out nocopy ame_util.stringList) as
2359: cursor attributeCursor(applicationIdIn in integer) is
2360: select
2361: ame_attributes.attribute_id,
2354: end getMandatoryAttributes;
2355: procedure getMandatoryAttributes2(applicationIdIn in integer,
2356: attributeIdOut out nocopy ame_util.stringList,
2357: attributeNameOut out nocopy ame_util.stringList,
2358: attributeTypeOut out nocopy ame_util.stringList) as
2359: cursor attributeCursor(applicationIdIn in integer) is
2360: select
2361: ame_attributes.attribute_id,
2362: ame_attributes.attribute_type,
2368: where
2369: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2370: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2371: ame_attribute_usages.application_id = applicationIdIn and
2372: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2373: sysdate between ame_attributes.start_date and
2374: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2375: sysdate between ame_attribute_usages.start_date and
2376: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2370: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2371: ame_attribute_usages.application_id = applicationIdIn and
2372: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2373: sysdate between ame_attributes.start_date and
2374: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2375: sysdate between ame_attribute_usages.start_date and
2376: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2377: sysdate between ame_mandatory_attributes.start_date and
2378: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2372: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2373: sysdate between ame_attributes.start_date and
2374: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2375: sysdate between ame_attribute_usages.start_date and
2376: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2377: sysdate between ame_mandatory_attributes.start_date and
2378: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2379: order by name;
2380: tempIndex integer;
2374: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2375: sysdate between ame_attribute_usages.start_date and
2376: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2377: sysdate between ame_mandatory_attributes.start_date and
2378: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2379: order by name;
2380: tempIndex integer;
2381: begin
2382: tempIndex := 1;
2389: end loop;
2390: exception
2391: when others then
2392: rollback;
2393: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2394: routineNameIn => 'getMandatoryAttributes2',
2395: exceptionNumberIn => sqlcode,
2396: exceptionStringIn => sqlerrm);
2397: raise;
2395: exceptionNumberIn => sqlcode,
2396: exceptionStringIn => sqlerrm);
2397: raise;
2398: end getMandatoryAttributes2;
2399: procedure getMandatoryAttributes3(attributeIdOut out nocopy ame_util.stringList,
2400: attributeNameOut out nocopy ame_util.stringList,
2401: attributeTypeOut out nocopy ame_util.stringList,
2402: attributeStartDateOut out nocopy ame_util.stringList) as
2403: cursor attributeCursor is
2396: exceptionStringIn => sqlerrm);
2397: raise;
2398: end getMandatoryAttributes2;
2399: procedure getMandatoryAttributes3(attributeIdOut out nocopy ame_util.stringList,
2400: attributeNameOut out nocopy ame_util.stringList,
2401: attributeTypeOut out nocopy ame_util.stringList,
2402: attributeStartDateOut out nocopy ame_util.stringList) as
2403: cursor attributeCursor is
2404: select
2397: raise;
2398: end getMandatoryAttributes2;
2399: procedure getMandatoryAttributes3(attributeIdOut out nocopy ame_util.stringList,
2400: attributeNameOut out nocopy ame_util.stringList,
2401: attributeTypeOut out nocopy ame_util.stringList,
2402: attributeStartDateOut out nocopy ame_util.stringList) as
2403: cursor attributeCursor is
2404: select
2405: ame_attributes.attribute_id,
2398: end getMandatoryAttributes2;
2399: procedure getMandatoryAttributes3(attributeIdOut out nocopy ame_util.stringList,
2400: attributeNameOut out nocopy ame_util.stringList,
2401: attributeTypeOut out nocopy ame_util.stringList,
2402: attributeStartDateOut out nocopy ame_util.stringList) as
2403: cursor attributeCursor is
2404: select
2405: ame_attributes.attribute_id,
2406: ame_attributes.attribute_type,
2410: ame_attributes,
2411: ame_mandatory_attributes
2412: where
2413: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2414: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2415: ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
2416: ame_util.restrictiveItemEvalAttribute) and
2417: sysdate between ame_attributes.start_date and
2418: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2411: ame_mandatory_attributes
2412: where
2413: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2414: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2415: ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
2416: ame_util.restrictiveItemEvalAttribute) and
2417: sysdate between ame_attributes.start_date and
2418: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2419: sysdate between ame_mandatory_attributes.start_date and
2412: where
2413: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2414: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2415: ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
2416: ame_util.restrictiveItemEvalAttribute) and
2417: sysdate between ame_attributes.start_date and
2418: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2419: sysdate between ame_mandatory_attributes.start_date and
2420: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2414: ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2415: ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
2416: ame_util.restrictiveItemEvalAttribute) and
2417: sysdate between ame_attributes.start_date and
2418: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2419: sysdate between ame_mandatory_attributes.start_date and
2420: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2421: order by name;
2422: tempIndex integer;
2416: ame_util.restrictiveItemEvalAttribute) and
2417: sysdate between ame_attributes.start_date and
2418: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2419: sysdate between ame_mandatory_attributes.start_date and
2420: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2421: order by name;
2422: tempIndex integer;
2423: begin
2424: tempIndex := 1;
2432: end loop;
2433: exception
2434: when others then
2435: rollback;
2436: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2437: routineNameIn => 'getMandatoryAttributes3',
2438: exceptionNumberIn => sqlcode,
2439: exceptionStringIn => sqlerrm);
2440: raise;
2440: raise;
2441: end getMandatoryAttributes3;
2442: procedure getNonHeaderICAttributes(applicationIdIn in integer,
2443: itemClassIdIn in integer,
2444: attributeIdsOut out nocopy ame_util.stringList,
2445: attributeNamesOut out nocopy ame_util.stringList) as
2446: cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2447: itemClassIdIn in integer) is
2448: select
2441: end getMandatoryAttributes3;
2442: procedure getNonHeaderICAttributes(applicationIdIn in integer,
2443: itemClassIdIn in integer,
2444: attributeIdsOut out nocopy ame_util.stringList,
2445: attributeNamesOut out nocopy ame_util.stringList) as
2446: cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2447: itemClassIdIn in integer) is
2448: select
2449: ame_attributes.attribute_id,
2455: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2456: ame_attributes.item_class_id = itemClassIdIn and
2457: ame_attribute_usages.application_id = applicationIdIn and
2458: sysdate between ame_attributes.start_date and
2459: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2460: sysdate between ame_attribute_usages.start_date and
2461: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2462: order by name;
2463: tempIndex integer;
2457: ame_attribute_usages.application_id = applicationIdIn and
2458: sysdate between ame_attributes.start_date and
2459: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2460: sysdate between ame_attribute_usages.start_date and
2461: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2462: order by name;
2463: tempIndex integer;
2464: begin
2465: open getNonHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2470: close getNonHeaderICAttributesCursor;
2471: exception
2472: when others then
2473: rollback;
2474: attributeIdsOut := ame_util.emptyStringList;
2475: attributeNamesOut := ame_util.emptyStringList;
2476: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2477: routineNameIn => 'getNonHeaderICAttributes',
2478: exceptionNumberIn => sqlcode,
2471: exception
2472: when others then
2473: rollback;
2474: attributeIdsOut := ame_util.emptyStringList;
2475: attributeNamesOut := ame_util.emptyStringList;
2476: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2477: routineNameIn => 'getNonHeaderICAttributes',
2478: exceptionNumberIn => sqlcode,
2479: exceptionStringIn => '(application ID ' ||
2472: when others then
2473: rollback;
2474: attributeIdsOut := ame_util.emptyStringList;
2475: attributeNamesOut := ame_util.emptyStringList;
2476: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2477: routineNameIn => 'getNonHeaderICAttributes',
2478: exceptionNumberIn => sqlcode,
2479: exceptionStringIn => '(application ID ' ||
2480: applicationIdIn||
2483: raise;
2484: end getNonHeaderICAttributes;
2485: procedure getNonHeaderICAttributes2(applicationIdIn in integer,
2486: itemClassIdIn in integer,
2487: attributeIdsOut out nocopy ame_util.idList,
2488: attributeNamesOut out nocopy ame_util.stringList) as
2489: cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2490: itemClassIdIn in integer) is
2491: select
2484: end getNonHeaderICAttributes;
2485: procedure getNonHeaderICAttributes2(applicationIdIn in integer,
2486: itemClassIdIn in integer,
2487: attributeIdsOut out nocopy ame_util.idList,
2488: attributeNamesOut out nocopy ame_util.stringList) as
2489: cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2490: itemClassIdIn in integer) is
2491: select
2492: ame_attributes.attribute_id,
2498: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2499: ame_attributes.item_class_id = itemClassIdIn and
2500: ame_attribute_usages.application_id = applicationIdIn and
2501: sysdate between ame_attributes.start_date and
2502: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2503: sysdate between ame_attribute_usages.start_date and
2504: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2505: order by name;
2506: tempIndex integer;
2500: ame_attribute_usages.application_id = applicationIdIn and
2501: sysdate between ame_attributes.start_date and
2502: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2503: sysdate between ame_attribute_usages.start_date and
2504: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2505: order by name;
2506: tempIndex integer;
2507: begin
2508: open getNonHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2513: close getNonHeaderICAttributesCursor;
2514: exception
2515: when others then
2516: rollback;
2517: attributeIdsOut := ame_util.emptyIdList;
2518: attributeNamesOut := ame_util.emptyStringList;
2519: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2520: routineNameIn => 'getNonHeaderICAttributes2',
2521: exceptionNumberIn => sqlcode,
2514: exception
2515: when others then
2516: rollback;
2517: attributeIdsOut := ame_util.emptyIdList;
2518: attributeNamesOut := ame_util.emptyStringList;
2519: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2520: routineNameIn => 'getNonHeaderICAttributes2',
2521: exceptionNumberIn => sqlcode,
2522: exceptionStringIn => '(application ID ' ||
2515: when others then
2516: rollback;
2517: attributeIdsOut := ame_util.emptyIdList;
2518: attributeNamesOut := ame_util.emptyStringList;
2519: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2520: routineNameIn => 'getNonHeaderICAttributes2',
2521: exceptionNumberIn => sqlcode,
2522: exceptionStringIn => '(application ID ' ||
2523: applicationIdIn||
2525: sqlerrm);
2526: raise;
2527: end getNonHeaderICAttributes2;
2528: procedure getRequiredAttributes(actionTypeIdIn in integer,
2529: attributeIdOut out nocopy ame_util.stringList,
2530: attributeNameOut out nocopy ame_util.stringList) as
2531: cursor attributeCursor(actionTypeIdIn in integer) is
2532: select ame_attributes.attribute_id,
2533: ame_attributes.name
2526: raise;
2527: end getNonHeaderICAttributes2;
2528: procedure getRequiredAttributes(actionTypeIdIn in integer,
2529: attributeIdOut out nocopy ame_util.stringList,
2530: attributeNameOut out nocopy ame_util.stringList) as
2531: cursor attributeCursor(actionTypeIdIn in integer) is
2532: select ame_attributes.attribute_id,
2533: ame_attributes.name
2534: from ame_attributes,
2536: where
2537: ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2538: ame_mandatory_attributes.action_type_id = actionTypeIdIn and
2539: sysdate between ame_attributes.start_date and
2540: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2541: sysdate between ame_mandatory_attributes.start_date and
2542: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2543: order by name;
2544: tempIndex integer;
2538: ame_mandatory_attributes.action_type_id = actionTypeIdIn and
2539: sysdate between ame_attributes.start_date and
2540: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2541: sysdate between ame_mandatory_attributes.start_date and
2542: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2543: order by name;
2544: tempIndex integer;
2545: begin
2546: tempIndex := 1;
2552: end loop;
2553: exception
2554: when others then
2555: rollback;
2556: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2557: routineNameIn => 'getRequiredAttributes',
2558: exceptionNumberIn => sqlcode,
2559: exceptionStringIn => '(action type ID ' ||
2560: actionTypeIdIn||
2564: end getRequiredAttributes;
2565: /*
2566: AME_STRIPING
2567: procedure getLineItemAttributes(applicationIdIn in integer,
2568: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2569: attributeIdOut out nocopy ame_util.stringList,
2570: attributeNameOut out nocopy ame_util.stringList) as
2571: cursor attributeCursor(applicationIdIn in integer,
2572: isStripingAttributeIn in varchar2) is
2565: /*
2566: AME_STRIPING
2567: procedure getLineItemAttributes(applicationIdIn in integer,
2568: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2569: attributeIdOut out nocopy ame_util.stringList,
2570: attributeNameOut out nocopy ame_util.stringList) as
2571: cursor attributeCursor(applicationIdIn in integer,
2572: isStripingAttributeIn in varchar2) is
2573: select
2566: AME_STRIPING
2567: procedure getLineItemAttributes(applicationIdIn in integer,
2568: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2569: attributeIdOut out nocopy ame_util.stringList,
2570: attributeNameOut out nocopy ame_util.stringList) as
2571: cursor attributeCursor(applicationIdIn in integer,
2572: isStripingAttributeIn in varchar2) is
2573: select
2574: ame_attributes.attribute_id,
2578: ame_attribute_usages
2579: where
2580: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2581: ame_attribute_usages.application_id = applicationIdIn and
2582: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2583: nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2584: (ame_attributes.start_date <= sysdate and
2585: (ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
2586: (ame_attribute_usages.start_date <= sysdate and
2579: where
2580: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2581: ame_attribute_usages.application_id = applicationIdIn and
2582: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2583: nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2584: (ame_attributes.start_date <= sysdate and
2585: (ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
2586: (ame_attribute_usages.start_date <= sysdate and
2587: (ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date))
2600: end loop;
2601: exception
2602: when others then
2603: rollback;
2604: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2605: routineNameIn => 'getLineItemAttributes',
2606: exceptionNumberIn => sqlcode,
2607: exceptionStringIn => '(application ID ' ||
2608: applicationIdIn||
2611: raise;
2612: end getLineItemAttributes;
2613: */
2614: procedure getLineItemAttributes(applicationIdIn in integer,
2615: attributeIdOut out nocopy ame_util.stringList,
2616: attributeNameOut out nocopy ame_util.stringList) as
2617: cursor attributeCursor(applicationIdIn in integer) is
2618: select
2619: ame_attributes.attribute_id,
2612: end getLineItemAttributes;
2613: */
2614: procedure getLineItemAttributes(applicationIdIn in integer,
2615: attributeIdOut out nocopy ame_util.stringList,
2616: attributeNameOut out nocopy ame_util.stringList) as
2617: cursor attributeCursor(applicationIdIn in integer) is
2618: select
2619: ame_attributes.attribute_id,
2620: ame_attributes.name
2623: ame_attribute_usages
2624: where
2625: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2626: ame_attribute_usages.application_id = applicationIdIn and
2627: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2628: sysdate between ame_attributes.start_date and
2629: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2630: sysdate between ame_attribute_usages.start_date and
2631: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2625: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2626: ame_attribute_usages.application_id = applicationIdIn and
2627: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2628: sysdate between ame_attributes.start_date and
2629: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2630: sysdate between ame_attribute_usages.start_date and
2631: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2632: order by name;
2633: tempIndex integer;
2627: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2628: sysdate between ame_attributes.start_date and
2629: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2630: sysdate between ame_attribute_usages.start_date and
2631: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2632: order by name;
2633: tempIndex integer;
2634: begin
2635: tempIndex := 1;
2641: end loop;
2642: exception
2643: when others then
2644: rollback;
2645: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2646: routineNameIn => 'getLineItemAttributes',
2647: exceptionNumberIn => sqlcode,
2648: exceptionStringIn => '(application ID ' ||
2649: applicationIdIn||
2651: sqlerrm);
2652: raise;
2653: end getLineItemAttributes;
2654: procedure getLineItemAttributes2(applicationIdIn in integer,
2655: attributeIdOut out nocopy ame_util.stringList,
2656: attributeNameOut out nocopy ame_util.stringList) as
2657: cursor attributeCursor(applicationIdIn in integer) is
2658: select
2659: ame_attributes.attribute_id,
2652: raise;
2653: end getLineItemAttributes;
2654: procedure getLineItemAttributes2(applicationIdIn in integer,
2655: attributeIdOut out nocopy ame_util.stringList,
2656: attributeNameOut out nocopy ame_util.stringList) as
2657: cursor attributeCursor(applicationIdIn in integer) is
2658: select
2659: ame_attributes.attribute_id,
2660: ame_attributes.name
2663: ame_attribute_usages
2664: where
2665: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2666: ame_attribute_usages.application_id = applicationIdIn and
2667: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2668: sysdate between ame_attributes.start_date and
2669: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2670: sysdate between ame_attribute_usages.start_date and
2671: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2665: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2666: ame_attribute_usages.application_id = applicationIdIn and
2667: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2668: sysdate between ame_attributes.start_date and
2669: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2670: sysdate between ame_attribute_usages.start_date and
2671: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2672: order by name;
2673: tempIndex integer;
2667: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2668: sysdate between ame_attributes.start_date and
2669: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2670: sysdate between ame_attribute_usages.start_date and
2671: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2672: order by name;
2673: tempIndex integer;
2674: begin
2675: tempIndex := 1;
2681: end loop;
2682: exception
2683: when others then
2684: rollback;
2685: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2686: routineNameIn => 'getLineItemAttributes2',
2687: exceptionNumberIn => sqlcode,
2688: exceptionStringIn => '(application ID ' ||
2689: applicationIdIn||
2693: end getLineItemAttributes2;
2694: /*
2695: AME_STRIPING
2696: procedure getNonMandatoryAttributes(applicationIdIn in integer,
2697: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2698: attributeIdOut out nocopy ame_util.stringList,
2699: attributeNameOut out nocopy ame_util.stringList) as
2700: cursor attributeCursor(applicationIdIn in integer,
2701: isStripingAttributeIn in varchar2) is
2694: /*
2695: AME_STRIPING
2696: procedure getNonMandatoryAttributes(applicationIdIn in integer,
2697: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2698: attributeIdOut out nocopy ame_util.stringList,
2699: attributeNameOut out nocopy ame_util.stringList) as
2700: cursor attributeCursor(applicationIdIn in integer,
2701: isStripingAttributeIn in varchar2) is
2702: select
2695: AME_STRIPING
2696: procedure getNonMandatoryAttributes(applicationIdIn in integer,
2697: isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2698: attributeIdOut out nocopy ame_util.stringList,
2699: attributeNameOut out nocopy ame_util.stringList) as
2700: cursor attributeCursor(applicationIdIn in integer,
2701: isStripingAttributeIn in varchar2) is
2702: select
2703: ame_attributes.attribute_id,
2707: ame_attribute_usages
2708: where
2709: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2710: ame_attribute_usages.application_id = applicationIdIn and
2711: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2712: nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2713: ame_attributes.attribute_id not in
2714: (select attribute_id from ame_mandatory_attributes
2715: where action_type_id = -1 and
2708: where
2709: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2710: ame_attribute_usages.application_id = applicationIdIn and
2711: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2712: nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2713: ame_attributes.attribute_id not in
2714: (select attribute_id from ame_mandatory_attributes
2715: where action_type_id = -1 and
2716: (ame_mandatory_attributes.start_date <= sysdate and
2734: end loop;
2735: exception
2736: when others then
2737: rollback;
2738: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2739: routineNameIn => 'getNonMandatoryAttributes',
2740: exceptionNumberIn => sqlcode,
2741: exceptionStringIn => '(application ID ' ||
2742: applicationIdIn||
2745: raise;
2746: end getNonMandatoryAttributes;
2747: */
2748: procedure getNonMandatoryAttributes(applicationIdIn in integer,
2749: attributeIdOut out nocopy ame_util.stringList,
2750: attributeNameOut out nocopy ame_util.stringList) as
2751: cursor attributeCursor(applicationIdIn in integer) is
2752: select
2753: ame_attributes.attribute_id,
2746: end getNonMandatoryAttributes;
2747: */
2748: procedure getNonMandatoryAttributes(applicationIdIn in integer,
2749: attributeIdOut out nocopy ame_util.stringList,
2750: attributeNameOut out nocopy ame_util.stringList) as
2751: cursor attributeCursor(applicationIdIn in integer) is
2752: select
2753: ame_attributes.attribute_id,
2754: ame_attributes.name
2757: ame_attribute_usages
2758: where
2759: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2760: ame_attribute_usages.application_id = applicationIdIn and
2761: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2762: ame_attributes.attribute_id not in
2763: (select attribute_id from ame_mandatory_attributes
2764: where action_type_id = -1 and
2765: sysdate between ame_mandatory_attributes.start_date and
2762: ame_attributes.attribute_id not in
2763: (select attribute_id from ame_mandatory_attributes
2764: where action_type_id = -1 and
2765: sysdate between ame_mandatory_attributes.start_date and
2766: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2767: sysdate between ame_attributes.start_date and
2768: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2769: sysdate between ame_attribute_usages.start_date and
2770: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2764: where action_type_id = -1 and
2765: sysdate between ame_mandatory_attributes.start_date and
2766: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2767: sysdate between ame_attributes.start_date and
2768: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2769: sysdate between ame_attribute_usages.start_date and
2770: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2771: order by name;
2772: tempIndex integer;
2766: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2767: sysdate between ame_attributes.start_date and
2768: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2769: sysdate between ame_attribute_usages.start_date and
2770: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2771: order by name;
2772: tempIndex integer;
2773: begin
2774: tempIndex := 1;
2780: end loop;
2781: exception
2782: when others then
2783: rollback;
2784: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2785: routineNameIn => 'getNonMandatoryAttributes',
2786: exceptionNumberIn => sqlcode,
2787: exceptionStringIn => '(application ID ' ||
2788: applicationIdIn||
2790: sqlerrm);
2791: raise;
2792: end getNonMandatoryAttributes;
2793: procedure getNonMandHeaderAttributes(applicationIdIn in integer,
2794: attributeIdOut out nocopy ame_util.stringList,
2795: attributeNameOut out nocopy ame_util.stringList) as
2796: cursor attributeCursor(applicationIdIn in integer,
2797: headerItemClassIdIn in integer) is
2798: select
2791: raise;
2792: end getNonMandatoryAttributes;
2793: procedure getNonMandHeaderAttributes(applicationIdIn in integer,
2794: attributeIdOut out nocopy ame_util.stringList,
2795: attributeNameOut out nocopy ame_util.stringList) as
2796: cursor attributeCursor(applicationIdIn in integer,
2797: headerItemClassIdIn in integer) is
2798: select
2799: ame_attributes.attribute_id,
2806: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2807: ame_attributes.item_class_id = ame_item_classes.item_class_id and
2808: ame_item_classes.item_class_id = headerItemClassIdIn and
2809: ame_attribute_usages.application_id = applicationIdIn and
2810: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2811: ame_attributes.attribute_id not in
2812: (select attribute_id from ame_mandatory_attributes
2813: where action_type_id = -1 and
2814: sysdate between ame_mandatory_attributes.start_date and
2811: ame_attributes.attribute_id not in
2812: (select attribute_id from ame_mandatory_attributes
2813: where action_type_id = -1 and
2814: sysdate between ame_mandatory_attributes.start_date and
2815: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2816: sysdate between ame_attributes.start_date and
2817: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2818: sysdate between ame_attribute_usages.start_date and
2819: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2813: where action_type_id = -1 and
2814: sysdate between ame_mandatory_attributes.start_date and
2815: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2816: sysdate between ame_attributes.start_date and
2817: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2818: sysdate between ame_attribute_usages.start_date and
2819: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2820: sysdate between ame_item_classes.start_date and
2821: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
2815: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2816: sysdate between ame_attributes.start_date and
2817: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2818: sysdate between ame_attribute_usages.start_date and
2819: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2820: sysdate between ame_item_classes.start_date and
2821: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
2822: order by ame_attributes.name;
2823: headerItemClassId integer;
2817: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2818: sysdate between ame_attribute_usages.start_date and
2819: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2820: sysdate between ame_item_classes.start_date and
2821: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
2822: order by ame_attributes.name;
2823: headerItemClassId integer;
2824: tempIndex integer;
2825: begin
2824: tempIndex integer;
2825: begin
2826: tempIndex := 1;
2827: headerItemClassId :=
2828: ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerItemClassName);
2829: for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
2830: headerItemClassIdIn => headerItemClassId) loop
2831: /* The explicit conversion below lets nocopy work. */
2832: attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2835: end loop;
2836: exception
2837: when others then
2838: rollback;
2839: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2840: routineNameIn => 'getNonMandHeaderAttributes',
2841: exceptionNumberIn => sqlcode,
2842: exceptionStringIn => '(application ID ' ||
2843: applicationIdIn||
2847: end getNonMandHeaderAttributes;
2848: /*
2849: AME_STRIPING
2850: procedure getRuleStripingAttributes(applicationIdIn in integer,
2851: attributeIdsOut out nocopy ame_util.stringList) as
2852: cursor ruleStripingAttributeCursor(applicationIdIn in integer) is
2853: select
2854: ame_attributes.attribute_id attribute_id
2855: from
2857: ame_attribute_usages
2858: where
2859: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2860: ame_attribute_usages.application_id = applicationIdIn and
2861: ame_attributes.attribute_type = ame_util.stringAttributeType and
2862: ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
2863: sysdate between ame_attributes.start_date and
2864: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2865: sysdate between ame_attribute_usages.start_date and
2858: where
2859: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2860: ame_attribute_usages.application_id = applicationIdIn and
2861: ame_attributes.attribute_type = ame_util.stringAttributeType and
2862: ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
2863: sysdate between ame_attributes.start_date and
2864: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2865: sysdate between ame_attribute_usages.start_date and
2866: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2860: ame_attribute_usages.application_id = applicationIdIn and
2861: ame_attributes.attribute_type = ame_util.stringAttributeType and
2862: ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
2863: sysdate between ame_attributes.start_date and
2864: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2865: sysdate between ame_attribute_usages.start_date and
2866: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2867: order by ame_attributes.attribute_id;
2868: tempIndex integer;
2862: ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
2863: sysdate between ame_attributes.start_date and
2864: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2865: sysdate between ame_attribute_usages.start_date and
2866: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2867: order by ame_attributes.attribute_id;
2868: tempIndex integer;
2869: begin
2870: tempIndex := 1;
2874: end loop;
2875: exception
2876: when others then
2877: rollback;
2878: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2879: routineNameIn => 'getRuleStripingAttributes',
2880: exceptionNumberIn => sqlcode,
2881: exceptionStringIn => '(application ID ' ||
2882: applicationIdIn||
2886: end getRuleStripingAttributes;
2887: */
2888: procedure getExistingShareableAttNames(applicationIdIn in integer,
2889: itemClassIdIn in integer,
2890: attributeIdsOut out nocopy ame_util.stringList,
2891: attributeNamesOut out nocopy ame_util.stringList) as
2892: cursor unusedAttributeCursor(applicationIdIn in integer,
2893: itemClassIdIn in integer) is
2894: select
2887: */
2888: procedure getExistingShareableAttNames(applicationIdIn in integer,
2889: itemClassIdIn in integer,
2890: attributeIdsOut out nocopy ame_util.stringList,
2891: attributeNamesOut out nocopy ame_util.stringList) as
2892: cursor unusedAttributeCursor(applicationIdIn in integer,
2893: itemClassIdIn in integer) is
2894: select
2895: ame_attributes.attribute_id attribute_id,
2910: where
2911: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2912: ame_attribute_usages.application_id = applicationIdIn and
2913: sysdate between ame_attributes.start_date and
2914: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2915: sysdate between ame_attribute_usages.start_date and
2916: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2917: order by name;
2918: cursor unusedAttributeCursor2(applicationIdIn in integer,
2912: ame_attribute_usages.application_id = applicationIdIn and
2913: sysdate between ame_attributes.start_date and
2914: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2915: sysdate between ame_attribute_usages.start_date and
2916: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2917: order by name;
2918: cursor unusedAttributeCursor2(applicationIdIn in integer,
2919: itemClassIdIn in integer,
2920: perApproverTypeIdIn in integer,
2938: where
2939: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2940: ame_attribute_usages.application_id = applicationIdIn and
2941: sysdate between ame_attributes.start_date and
2942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2943: sysdate between ame_attribute_usages.start_date and
2944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2945: minus
2946: select
2940: ame_attribute_usages.application_id = applicationIdIn and
2941: sysdate between ame_attributes.start_date and
2942: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2943: sysdate between ame_attribute_usages.start_date and
2944: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2945: minus
2946: select
2947: ame_attributes.attribute_id attribute_id,
2948: ame_attributes.name name
2952: approver_type_id not in (perApproverTypeIdIn, fndUserApproverTypeIdIn) and
2953: (ame_attributes.start_date <= sysdate and
2954: (ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
2955: order by name;
2956: allowAllApproverTypes ame_util.stringType;
2957: fndUserApproverTypeId integer;
2958: perApproverTypeId integer;
2959: tempIndex integer;
2960: begin
2958: perApproverTypeId integer;
2959: tempIndex integer;
2960: begin
2961: perApproverTypeId :=
2962: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.perOrigSystem);
2963: fndUserApproverTypeId :=
2964: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.fndUserOrigSystem);
2965: allowAllApproverTypes :=
2966: ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2960: begin
2961: perApproverTypeId :=
2962: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.perOrigSystem);
2963: fndUserApproverTypeId :=
2964: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.fndUserOrigSystem);
2965: allowAllApproverTypes :=
2966: ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2967: applicationIdIn => applicationIdIn);
2968: tempIndex := 1;
2962: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.perOrigSystem);
2963: fndUserApproverTypeId :=
2964: ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.fndUserOrigSystem);
2965: allowAllApproverTypes :=
2966: ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2967: applicationIdIn => applicationIdIn);
2968: tempIndex := 1;
2969: if(allowAllApproverTypes = ame_util.yes) then
2970: for tempAttribute in unusedAttributeCursor(applicationIdIn => applicationIdIn,
2965: allowAllApproverTypes :=
2966: ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2967: applicationIdIn => applicationIdIn);
2968: tempIndex := 1;
2969: if(allowAllApproverTypes = ame_util.yes) then
2970: for tempAttribute in unusedAttributeCursor(applicationIdIn => applicationIdIn,
2971: itemClassIdIn => itemClassIdIn) loop
2972: /* The explicit conversion below lets nocopy work. */
2973: attributeIdsOut(tempIndex) := to_char(tempAttribute.attribute_id);
2987: end if;
2988: exception
2989: when others then
2990: rollback;
2991: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2992: routineNameIn => 'getExistingShareableAttNames',
2993: exceptionNumberIn => sqlcode,
2994: exceptionStringIn => '(application ID ' ||
2995: applicationIdIn||
2998: raise;
2999: end getExistingShareableAttNames;
3000: procedure getSubordinateICAttributes(applicationIdIn in integer,
3001: itemClassIdIn in integer,
3002: attributeIdsOut out nocopy ame_util.stringList,
3003: attributeNamesOut out nocopy ame_util.stringList) as
3004: cursor attributeCursor(applicationIdIn in integer,
3005: itemClassIdIn in integer) is
3006: select
2999: end getExistingShareableAttNames;
3000: procedure getSubordinateICAttributes(applicationIdIn in integer,
3001: itemClassIdIn in integer,
3002: attributeIdsOut out nocopy ame_util.stringList,
3003: attributeNamesOut out nocopy ame_util.stringList) as
3004: cursor attributeCursor(applicationIdIn in integer,
3005: itemClassIdIn in integer) is
3006: select
3007: ame_attributes.attribute_id,
3013: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
3014: ame_attribute_usages.application_id = applicationIdIn and
3015: ame_attributes.item_class_id = itemClassIdIn and
3016: sysdate between ame_attributes.start_date and
3017: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3018: sysdate between ame_attribute_usages.start_date and
3019: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3020: order by name;
3021: tempIndex integer;
3015: ame_attributes.item_class_id = itemClassIdIn and
3016: sysdate between ame_attributes.start_date and
3017: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3018: sysdate between ame_attribute_usages.start_date and
3019: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3020: order by name;
3021: tempIndex integer;
3022: begin
3023: tempIndex := 1;
3030: end loop;
3031: exception
3032: when others then
3033: rollback;
3034: attributeIdsOut := ame_util.emptyStringList;
3035: attributeNamesOut := ame_util.emptyStringList;
3036: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3037: routineNameIn => 'getSubordinateICAttributes',
3038: exceptionNumberIn => sqlcode,
3031: exception
3032: when others then
3033: rollback;
3034: attributeIdsOut := ame_util.emptyStringList;
3035: attributeNamesOut := ame_util.emptyStringList;
3036: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3037: routineNameIn => 'getSubordinateICAttributes',
3038: exceptionNumberIn => sqlcode,
3039: exceptionStringIn => '(application ID ' ||
3032: when others then
3033: rollback;
3034: attributeIdsOut := ame_util.emptyStringList;
3035: attributeNamesOut := ame_util.emptyStringList;
3036: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3037: routineNameIn => 'getSubordinateICAttributes',
3038: exceptionNumberIn => sqlcode,
3039: exceptionStringIn => '(application ID ' ||
3040: applicationIdIn||
3043: raise;
3044: end getSubordinateICAttributes;
3045: procedure getSubordinateICAttributes2(applicationIdIn in integer,
3046: itemClassIdIn in integer,
3047: attributeIdsOut out nocopy ame_util.idList,
3048: attributeNamesOut out nocopy ame_util.stringList,
3049: attributeTypesOut out nocopy ame_util.stringList) as
3050: cursor attributeCursor(applicationIdIn in integer,
3051: itemClassIdIn in integer) is
3044: end getSubordinateICAttributes;
3045: procedure getSubordinateICAttributes2(applicationIdIn in integer,
3046: itemClassIdIn in integer,
3047: attributeIdsOut out nocopy ame_util.idList,
3048: attributeNamesOut out nocopy ame_util.stringList,
3049: attributeTypesOut out nocopy ame_util.stringList) as
3050: cursor attributeCursor(applicationIdIn in integer,
3051: itemClassIdIn in integer) is
3052: select
3045: procedure getSubordinateICAttributes2(applicationIdIn in integer,
3046: itemClassIdIn in integer,
3047: attributeIdsOut out nocopy ame_util.idList,
3048: attributeNamesOut out nocopy ame_util.stringList,
3049: attributeTypesOut out nocopy ame_util.stringList) as
3050: cursor attributeCursor(applicationIdIn in integer,
3051: itemClassIdIn in integer) is
3052: select
3053: ame_attributes.attribute_id,
3060: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
3061: ame_attribute_usages.application_id = applicationIdIn and
3062: ame_attributes.item_class_id = itemClassIdIn and
3063: sysdate between ame_attributes.start_date and
3064: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3065: sysdate between ame_attribute_usages.start_date and
3066: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3067: order by name;
3068: tempIndex integer;
3062: ame_attributes.item_class_id = itemClassIdIn and
3063: sysdate between ame_attributes.start_date and
3064: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3065: sysdate between ame_attribute_usages.start_date and
3066: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3067: order by name;
3068: tempIndex integer;
3069: begin
3070: tempIndex := 1;
3078: end loop;
3079: exception
3080: when others then
3081: rollback;
3082: attributeIdsOut := ame_util.emptyIdList;
3083: attributeNamesOut := ame_util.emptyStringList;
3084: attributeTypesOut := ame_util.emptyStringList;
3085: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3086: routineNameIn => 'getSubordinateICAttributes2',
3079: exception
3080: when others then
3081: rollback;
3082: attributeIdsOut := ame_util.emptyIdList;
3083: attributeNamesOut := ame_util.emptyStringList;
3084: attributeTypesOut := ame_util.emptyStringList;
3085: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3086: routineNameIn => 'getSubordinateICAttributes2',
3087: exceptionNumberIn => sqlcode,
3080: when others then
3081: rollback;
3082: attributeIdsOut := ame_util.emptyIdList;
3083: attributeNamesOut := ame_util.emptyStringList;
3084: attributeTypesOut := ame_util.emptyStringList;
3085: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3086: routineNameIn => 'getSubordinateICAttributes2',
3087: exceptionNumberIn => sqlcode,
3088: exceptionStringIn => '(application ID ' ||
3081: rollback;
3082: attributeIdsOut := ame_util.emptyIdList;
3083: attributeNamesOut := ame_util.emptyStringList;
3084: attributeTypesOut := ame_util.emptyStringList;
3085: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3086: routineNameIn => 'getSubordinateICAttributes2',
3087: exceptionNumberIn => sqlcode,
3088: exceptionStringIn => '(application ID ' ||
3089: applicationIdIn||
3127: attributeType ame_attributes.attribute_type%type;
3128: badCurUsageException exception;
3129: badStaticDateUsageException exception;
3130: booleanException exception;
3131: charMonths ame_util.stringList;
3132: comma1Location integer;
3133: comma2Location integer;
3134: createdBy integer;
3135: currentUserId integer;
3135: currentUserId integer;
3136: dateAttribute date;
3137: dynamicUsageException exception;
3138: errorCode integer;
3139: errorMessage ame_util.longestStringType;
3140: firstReturnLocation integer;
3141: invalidBooleanValueException exception;
3142: invalidReferenceException exception;
3143: itemClassId integer;
3148: */
3149: lineItemIdPlaceholderPosition integer;
3150: lineItemIdPlaceholderPosition2 integer;
3151: loweredQueryString varchar2(4000);
3152: numMonths ame_util.stringList;
3153: nullQueryStringException exception;
3154: objectVersionNoDataException exception;
3155: placeholderException exception;
3156: queryString ame_attribute_usages.query_string%type;
3160: startDate date;
3161: startDate2 date;
3162: stringDynamicException exception;
3163: stringStaticUsageException exception;
3164: substitutionString ame_util.stringType;
3165: tempCount integer;
3166: tempCount2 integer;
3167: tempInt integer;
3168: transactionType ame_calling_apps.application_name%type;
3183: raise objectVersionNoDataException;
3184: end if;
3185: if(parentVersionStartDateIn <> startDate) then
3186: close startDateCursor;
3187: raise ame_util.objectVersionException;
3188: end if;
3189: end if;
3190: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3191: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
3189: end if;
3190: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3191: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
3192: itemClassId := ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdIn);
3193: if(staticUsageIn = ame_util.booleanTrue) then /* static usage */
3194: queryString := ame_util.removeReturns(stringIn => queryStringIn,
3195: replaceWithSpaces => false);
3196: if(instrb(upper(queryString), upper(ame_util.transactionIdPlaceholder))) > 0 then
3197: raise placeholderException;
3190: attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3191: attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
3192: itemClassId := ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdIn);
3193: if(staticUsageIn = ame_util.booleanTrue) then /* static usage */
3194: queryString := ame_util.removeReturns(stringIn => queryStringIn,
3195: replaceWithSpaces => false);
3196: if(instrb(upper(queryString), upper(ame_util.transactionIdPlaceholder))) > 0 then
3197: raise placeholderException;
3198: end if;
3192: itemClassId := ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdIn);
3193: if(staticUsageIn = ame_util.booleanTrue) then /* static usage */
3194: queryString := ame_util.removeReturns(stringIn => queryStringIn,
3195: replaceWithSpaces => false);
3196: if(instrb(upper(queryString), upper(ame_util.transactionIdPlaceholder))) > 0 then
3197: raise placeholderException;
3198: end if;
3199: /* Format the static usage correctly. */
3200: if(attributeType = ame_util.currencyAttributeType) then
3196: if(instrb(upper(queryString), upper(ame_util.transactionIdPlaceholder))) > 0 then
3197: raise placeholderException;
3198: end if;
3199: /* Format the static usage correctly. */
3200: if(attributeType = ame_util.currencyAttributeType) then
3201: queryString := inputToCanonStaticCurUsage(attributeIdIn => attributeIdIn,
3202: applicationIdIn => applicationIdIn,
3203: queryStringIn => queryString);
3204: elsif(attributeType = ame_util.numberAttributeType) then
3200: if(attributeType = ame_util.currencyAttributeType) then
3201: queryString := inputToCanonStaticCurUsage(attributeIdIn => attributeIdIn,
3202: applicationIdIn => applicationIdIn,
3203: queryStringIn => queryString);
3204: elsif(attributeType = ame_util.numberAttributeType) then
3205: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
3206: elsif(attributeType = ame_util.stringAttributeType) then
3207: if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208: raise stringStaticUsageException;
3201: queryString := inputToCanonStaticCurUsage(attributeIdIn => attributeIdIn,
3202: applicationIdIn => applicationIdIn,
3203: queryStringIn => queryString);
3204: elsif(attributeType = ame_util.numberAttributeType) then
3205: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
3206: elsif(attributeType = ame_util.stringAttributeType) then
3207: if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208: raise stringStaticUsageException;
3209: end if;
3202: applicationIdIn => applicationIdIn,
3203: queryStringIn => queryString);
3204: elsif(attributeType = ame_util.numberAttributeType) then
3205: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
3206: elsif(attributeType = ame_util.stringAttributeType) then
3207: if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208: raise stringStaticUsageException;
3209: end if;
3210: elsif(attributeType = ame_util.booleanAttributeType) then
3203: queryStringIn => queryString);
3204: elsif(attributeType = ame_util.numberAttributeType) then
3205: queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
3206: elsif(attributeType = ame_util.stringAttributeType) then
3207: if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208: raise stringStaticUsageException;
3209: end if;
3210: elsif(attributeType = ame_util.booleanAttributeType) then
3211: if(instrb(upper(queryStringIn),'TRUE') > 0) then
3206: elsif(attributeType = ame_util.stringAttributeType) then
3207: if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208: raise stringStaticUsageException;
3209: end if;
3210: elsif(attributeType = ame_util.booleanAttributeType) then
3211: if(instrb(upper(queryStringIn),'TRUE') > 0) then
3212: queryString := 'true';
3213: elsif(instrb(upper(queryStringIn), 'FALSE') > 0) then
3214: queryString := 'false';
3214: queryString := 'false';
3215: else
3216: raise booleanException;
3217: end if;
3218: if(attributeName = ame_util.evalPrioritiesPerItemAttribute) then
3219: if(queryString = 'true') then
3220: attributeId := ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.restrictiveItemEvalAttribute);
3221: queryString1 := ame_attribute_pkg.getQueryString(attributeIdIn => attributeId,
3222: applicationIdIn => applicationIdIn);
3216: raise booleanException;
3217: end if;
3218: if(attributeName = ame_util.evalPrioritiesPerItemAttribute) then
3219: if(queryString = 'true') then
3220: attributeId := ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.restrictiveItemEvalAttribute);
3221: queryString1 := ame_attribute_pkg.getQueryString(attributeIdIn => attributeId,
3222: applicationIdIn => applicationIdIn);
3223: if(queryString1 is null or queryString1 = 'false') then
3224: raise invalidBooleanValueException;
3224: raise invalidBooleanValueException;
3225: end if;
3226: end if;
3227: end if;
3228: elsif(attributeType = ame_util.dateAttributeType) then
3229: /* check to make sure the user entered the date in the correct format */
3230: begin
3231: if(queryString is not null) then
3232: numMonths(1) := '01';
3252: charMonths(9) := 'SEP';
3253: charMonths(10) := 'OCT';
3254: charMonths(11) := 'NOV';
3255: charMonths(12) := 'DEC';
3256: ame_util.substituteStrings(stringIn => queryString,
3257: targetStringsIn => charMonths,
3258: substitutionStringsIn => numMonths,
3259: stringOut => substitutionString);
3260: queryString := substitutionString;
3279: raise stringDynamicException;
3280: end if;
3281: tempInt := 1;
3282: upperQueryString := upper(queryStringIn);
3283: upperTransIdPlaceholder := upper(ame_util.transactionIdPlaceholder);
3284: loop
3285: transIdPlaceholderPosition :=
3286: instrb(upperQueryString, upperTransIdPlaceholder, 1, tempInt);
3287: if(transIdPlaceholderPosition = 0) then
3287: if(transIdPlaceholderPosition = 0) then
3288: exit;
3289: end if;
3290: transIdPlaceholderPosition2 :=
3291: instrb(queryStringIn, ame_util.transactionIdPlaceholder, 1, tempInt);
3292: if(transIdPlaceholderPosition <> transIdPlaceholderPosition2) then
3293: raise dynamicUsageException;
3294: end if;
3295: tempInt := tempInt + 1;
3293: raise dynamicUsageException;
3294: end if;
3295: tempInt := tempInt + 1;
3296: end loop;
3297: if(attributeType = ame_util.currencyAttributeType) then
3298: comma1Location := instrb(queryString, ',', -1, 2);
3299: comma2Location := instrb(queryString, ',', -1, 1);
3300: if(comma1Location = 0 or
3301: comma2Location = 0 or
3307: raise badCurUsageException;
3308: end if;
3309: end if;
3310: end if;
3311: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attribute_usages',
3312: columnNameIn => 'query_string',
3313: argumentIn => queryString)) then
3314: raise queryStringLengthException;
3315: end if;
3335: attributeIdIn => attributeIdIn) and
3336: isStripingAttributeIn is null) then */ /* existing striping attribute
3337: has been set to null */
3338: /*
3339: isStripingAttributeChange := ame_util.booleanTrue;
3340: isBecomingStripingAttribute := ame_util.booleanFalse;
3341: elsif(not isStripingAttribute(applicationIdIn => applicationIdIn,
3342: attributeIdIn => attributeIdIn) and
3343: isStripingAttributeIn is not null) then */ /* attribute is becoming a
3336: isStripingAttributeIn is null) then */ /* existing striping attribute
3337: has been set to null */
3338: /*
3339: isStripingAttributeChange := ame_util.booleanTrue;
3340: isBecomingStripingAttribute := ame_util.booleanFalse;
3341: elsif(not isStripingAttribute(applicationIdIn => applicationIdIn,
3342: attributeIdIn => attributeIdIn) and
3343: isStripingAttributeIn is not null) then */ /* attribute is becoming a
3344: striping attribute */
3342: attributeIdIn => attributeIdIn) and
3343: isStripingAttributeIn is not null) then */ /* attribute is becoming a
3344: striping attribute */
3345: /*
3346: isStripingAttributeChange := ame_util.booleanFalse;
3347: isBecomingStripingAttribute := ame_util.booleanTrue;
3348: else
3349: isStripingAttributeChange := ame_util.booleanFalse;
3350: isBecomingStripingAttribute := ame_util.booleanFalse;
3343: isStripingAttributeIn is not null) then */ /* attribute is becoming a
3344: striping attribute */
3345: /*
3346: isStripingAttributeChange := ame_util.booleanFalse;
3347: isBecomingStripingAttribute := ame_util.booleanTrue;
3348: else
3349: isStripingAttributeChange := ame_util.booleanFalse;
3350: isBecomingStripingAttribute := ame_util.booleanFalse;
3351: end if;
3345: /*
3346: isStripingAttributeChange := ame_util.booleanFalse;
3347: isBecomingStripingAttribute := ame_util.booleanTrue;
3348: else
3349: isStripingAttributeChange := ame_util.booleanFalse;
3350: isBecomingStripingAttribute := ame_util.booleanFalse;
3351: end if;
3352: useCount := calculateUseCount(attributeIdIn => attributeIdIn,
3353: applicationIdIn => applicationIdIn,
3346: isStripingAttributeChange := ame_util.booleanFalse;
3347: isBecomingStripingAttribute := ame_util.booleanTrue;
3348: else
3349: isStripingAttributeChange := ame_util.booleanFalse;
3350: isBecomingStripingAttribute := ame_util.booleanFalse;
3351: end if;
3352: useCount := calculateUseCount(attributeIdIn => attributeIdIn,
3353: applicationIdIn => applicationIdIn,
3354: isStripingAttributeChangeIn => isStripingAttributeChange,
3358: from ame_attribute_usages
3359: where
3360: attribute_id = attributeIdIn and
3361: applicationI_id = applicationIdIn and
3362: created_by = ame_util.seededDataCreatedById;
3363: if(tempCount2 > 0) then
3364: createdBy := ame_util.seededDataCreatedById;
3365: else
3366: createdBy := currentUserId;
3360: attribute_id = attributeIdIn and
3361: applicationI_id = applicationIdIn and
3362: created_by = ame_util.seededDataCreatedById;
3363: if(tempCount2 > 0) then
3364: createdBy := ame_util.seededDataCreatedById;
3365: else
3366: createdBy := currentUserId;
3367: end if;
3368: insert into ame_attribute_usages(attribute_id,
3390: sysdate,
3391: currentUserId,
3392: nvl(newStartDateIn, sysdate),
3393: null,
3394: ame_util.booleanTrue,
3395: isStripingAttributeIn);
3396: */
3397: useCount := calculateUseCount(attributeIdIn => attributeIdIn,
3398: applicationIdIn => applicationIdIn);
3405: including the new version, should. This is a failsafe way to check
3406: whether previous versions of an already end-dated object had
3407: created_by = 1.
3408: */
3409: currentUserId := ame_util.getCurrentUserId;
3410: select count(*)
3411: into tempCount2
3412: from ame_attribute_usages
3413: where
3412: from ame_attribute_usages
3413: where
3414: attribute_id = attributeIdIn and
3415: application_id = applicationIdIn and
3416: created_by = ame_util.seededDataCreatedById;
3417: if(tempCount2 > 0) then
3418: createdBy := ame_util.seededDataCreatedById;
3419: elsif(createdByIn is null) then
3420: createdBy := currentUserId;
3414: attribute_id = attributeIdIn and
3415: application_id = applicationIdIn and
3416: created_by = ame_util.seededDataCreatedById;
3417: if(tempCount2 > 0) then
3418: createdBy := ame_util.seededDataCreatedById;
3419: elsif(createdByIn is null) then
3420: createdBy := currentUserId;
3421: else
3422: createdBy := createdByIn;
3445: processingDate,
3446: currentUserId,
3447: startDate2,
3448: null,
3449: ame_util.booleanTrue);
3450: if(finalizeIn) then
3451: if(updateParentObjectIn) then
3452: attributeDescription := getDescription(attributeIdIn => attributeIdIn);
3453: approverTypeId := getApproverTypeId(attributeIdIn => attributeIdIn);
3459: end_date = startDate2
3460: where
3461: attribute_id = attributeIdIn and
3462: sysdate between start_date and
3463: nvl(end_date - ame_util.oneSecond, sysdate);
3464: attributeId := new(nameIn => attributeName,
3465: typeIn => attributeType,
3466: descriptionIn => attributeDescription,
3467: attributeIdIn => attributeIdIn,
3474: end if;
3475: commit;
3476: end if;
3477: exception
3478: when ame_util.objectVersionException then
3479: rollback;
3480: if(startDateCursor%isOpen) then
3481: close startDateCursor;
3482: end if;
3481: close startDateCursor;
3482: end if;
3483: errorCode := -20001;
3484: errorMessage :=
3485: ame_util.getMessage(applicationShortNameIn => 'PER',
3486: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3487: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3488: routineNameIn => 'newAttributeUsage',
3489: exceptionNumberIn => errorCode,
3483: errorCode := -20001;
3484: errorMessage :=
3485: ame_util.getMessage(applicationShortNameIn => 'PER',
3486: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3487: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3488: routineNameIn => 'newAttributeUsage',
3489: exceptionNumberIn => errorCode,
3490: exceptionStringIn => errorMessage);
3491: raise_application_error(errorCode,
3496: close startDateCursor;
3497: end if;
3498: errorCode := -20001;
3499: errorMessage :=
3500: ame_util.getMessage(applicationShortNameIn => 'PER',
3501: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3502: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3503: routineNameIn => 'newAttributeUsage',
3504: exceptionNumberIn => errorCode,
3498: errorCode := -20001;
3499: errorMessage :=
3500: ame_util.getMessage(applicationShortNameIn => 'PER',
3501: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3502: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3503: routineNameIn => 'newAttributeUsage',
3504: exceptionNumberIn => errorCode,
3505: exceptionStringIn => errorMessage);
3506: raise_application_error(errorCode,
3508: when placeholderException then
3509: rollback;
3510: errorCode := -20001;
3511: errorMessage :=
3512: ame_util.getMessage(applicationShortNameIn => 'PER',
3513: messageNameIn => 'AME_400159_ATT_STAT_NOT_PLC');
3514: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3515: routineNameIn => 'newAttributeUsage',
3516: exceptionNumberIn => errorCode,
3510: errorCode := -20001;
3511: errorMessage :=
3512: ame_util.getMessage(applicationShortNameIn => 'PER',
3513: messageNameIn => 'AME_400159_ATT_STAT_NOT_PLC');
3514: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3515: routineNameIn => 'newAttributeUsage',
3516: exceptionNumberIn => errorCode,
3517: exceptionStringIn => errorMessage);
3518: raise_application_error(errorCode,
3520: when dynamicUsageException then
3521: rollback;
3522: errorCode := -20001;
3523: errorMessage :=
3524: ame_util.getMessage(applicationShortNameIn => 'PER',
3525: messageNameIn => 'AME_400414_DYNAMIC_ATTR_USAGES');
3526: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3527: routineNameIn => 'newAttributeUsage',
3528: exceptionNumberIn => errorCode,
3522: errorCode := -20001;
3523: errorMessage :=
3524: ame_util.getMessage(applicationShortNameIn => 'PER',
3525: messageNameIn => 'AME_400414_DYNAMIC_ATTR_USAGES');
3526: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3527: routineNameIn => 'newAttributeUsage',
3528: exceptionNumberIn => errorCode,
3529: exceptionStringIn => errorMessage);
3530: raise_application_error(errorCode,
3532: when nullQueryStringException then
3533: rollback;
3534: errorCode := -20001;
3535: errorMessage :=
3536: ame_util.getMessage(applicationShortNameIn => 'PER',
3537: messageNameIn => 'AME_400161_ATT_EMPTY_USAGE');
3538: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3539: routineNameIn => 'newAttributeUsage',
3540: exceptionNumberIn => errorCode,
3534: errorCode := -20001;
3535: errorMessage :=
3536: ame_util.getMessage(applicationShortNameIn => 'PER',
3537: messageNameIn => 'AME_400161_ATT_EMPTY_USAGE');
3538: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3539: routineNameIn => 'newAttributeUsage',
3540: exceptionNumberIn => errorCode,
3541: exceptionStringIn => errorMessage);
3542: raise_application_error(errorCode,
3544: when usageExistsException then
3545: rollback;
3546: errorCode := -20001;
3547: errorMessage :=
3548: ame_util.getMessage(applicationShortNameIn => 'PER',
3549: messageNameIn => 'AME_400162_ATT_USAGE_EXISTS');
3550: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3551: routineNameIn => 'newAttributeUsage',
3552: exceptionNumberIn => errorCode,
3546: errorCode := -20001;
3547: errorMessage :=
3548: ame_util.getMessage(applicationShortNameIn => 'PER',
3549: messageNameIn => 'AME_400162_ATT_USAGE_EXISTS');
3550: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3551: routineNameIn => 'newAttributeUsage',
3552: exceptionNumberIn => errorCode,
3553: exceptionStringIn => errorMessage);
3554: raise_application_error(errorCode,
3556: when queryStringColumnException then
3557: rollback;
3558: errorCode := -20001;
3559: errorMessage :=
3560: ame_util.getMessage(applicationShortNameIn => 'PER',
3561: messageNameIn => 'AME_400158_ATT_THREE_VALUES');
3562: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3563: routineNameIn => 'newAttributeUsage',
3564: exceptionNumberIn => errorCode,
3558: errorCode := -20001;
3559: errorMessage :=
3560: ame_util.getMessage(applicationShortNameIn => 'PER',
3561: messageNameIn => 'AME_400158_ATT_THREE_VALUES');
3562: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3563: routineNameIn => 'newAttributeUsage',
3564: exceptionNumberIn => errorCode,
3565: exceptionStringIn => errorMessage);
3566: raise_application_error(errorCode,
3568: when queryStringLengthException then
3569: rollback;
3570: errorCode := -20001;
3571: errorMessage :=
3572: ame_util.getMessage(applicationShortNameIn => 'PER',
3573: messageNameIn => 'AME_400163_ATT_USAGE_LONG',
3574: tokenNameOneIn => 'COLUMN_LENGTH',
3575: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attribute_usages',
3576: columnNameIn => 'query_string'));
3571: errorMessage :=
3572: ame_util.getMessage(applicationShortNameIn => 'PER',
3573: messageNameIn => 'AME_400163_ATT_USAGE_LONG',
3574: tokenNameOneIn => 'COLUMN_LENGTH',
3575: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attribute_usages',
3576: columnNameIn => 'query_string'));
3577: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3578: routineNameIn => 'newAttributeUsage',
3579: exceptionNumberIn => errorCode,
3573: messageNameIn => 'AME_400163_ATT_USAGE_LONG',
3574: tokenNameOneIn => 'COLUMN_LENGTH',
3575: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attribute_usages',
3576: columnNameIn => 'query_string'));
3577: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3578: routineNameIn => 'newAttributeUsage',
3579: exceptionNumberIn => errorCode,
3580: exceptionStringIn => errorMessage);
3581: raise_application_error(errorCode,
3583: when invalidBooleanValueException then
3584: rollback;
3585: errorCode := -20001;
3586: errorMessage :=
3587: ame_util.getMessage(applicationShortNameIn => 'PER',
3588: messageNameIn => 'AME_400322_LIN_ITEM_TRUE_SET');
3589: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3590: routineNameIn => 'newAttributeUsage',
3591: exceptionNumberIn => errorCode,
3585: errorCode := -20001;
3586: errorMessage :=
3587: ame_util.getMessage(applicationShortNameIn => 'PER',
3588: messageNameIn => 'AME_400322_LIN_ITEM_TRUE_SET');
3589: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3590: routineNameIn => 'newAttributeUsage',
3591: exceptionNumberIn => errorCode,
3592: exceptionStringIn => errorMessage);
3593: raise_application_error(errorCode,
3595: when invalidReferenceException then
3596: rollback;
3597: errorCode := -20001;
3598: errorMessage :=
3599: ame_util.getMessage(applicationShortNameIn => 'PER',
3600: messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
3601: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3602: routineNameIn => 'newAttributeUsage',
3603: exceptionNumberIn => errorCode,
3597: errorCode := -20001;
3598: errorMessage :=
3599: ame_util.getMessage(applicationShortNameIn => 'PER',
3600: messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
3601: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3602: routineNameIn => 'newAttributeUsage',
3603: exceptionNumberIn => errorCode,
3604: exceptionStringIn => errorMessage);
3605: raise_application_error(errorCode,
3608: rollback;
3609: transactionType := ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn);
3610: errorCode := -20001;
3611: errorMessage :=
3612: ame_util.getMessage(applicationShortNameIn => 'PER',
3613: messageNameIn => 'AME_400164_ATT_BAD_STAT_USG',
3614: tokenNameOneIn => 'TRANSACTION_TYPE',
3615: tokenValueOneIn => transactionType,
3616: tokenNameTwoIn => 'ATTRIBUTE',
3614: tokenNameOneIn => 'TRANSACTION_TYPE',
3615: tokenValueOneIn => transactionType,
3616: tokenNameTwoIn => 'ATTRIBUTE',
3617: tokenValueTwoIn => attributeName);
3618: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3619: routineNameIn => 'newAttributeUsage',
3620: exceptionNumberIn => errorCode,
3621: exceptionStringIn => errorMessage);
3622: raise_application_error(errorCode,
3624: when stringDynamicException then
3625: rollback;
3626: errorCode := -20001;
3627: errorMessage :=
3628: ame_util.getMessage(applicationShortNameIn => 'PER',
3629: messageNameIn => 'AME_400165_ATT_DYN_USG_COMM');
3630: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3631: routineNameIn => 'newAttributeUsage',
3632: exceptionNumberIn => errorCode,
3626: errorCode := -20001;
3627: errorMessage :=
3628: ame_util.getMessage(applicationShortNameIn => 'PER',
3629: messageNameIn => 'AME_400165_ATT_DYN_USG_COMM');
3630: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3631: routineNameIn => 'newAttributeUsage',
3632: exceptionNumberIn => errorCode,
3633: exceptionStringIn => errorMessage);
3634: raise_application_error(errorCode,
3636: when stringStaticUsageException then
3637: rollback;
3638: errorCode := -20001;
3639: errorMessage :=
3640: ame_util.getMessage(applicationShortNameIn => 'PER',
3641: messageNameIn => 'AME_400166_ATT_STAT_USG_STRING');
3642: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3643: routineNameIn => 'newAttributeUsage',
3644: exceptionNumberIn => errorCode,
3638: errorCode := -20001;
3639: errorMessage :=
3640: ame_util.getMessage(applicationShortNameIn => 'PER',
3641: messageNameIn => 'AME_400166_ATT_STAT_USG_STRING');
3642: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3643: routineNameIn => 'newAttributeUsage',
3644: exceptionNumberIn => errorCode,
3645: exceptionStringIn => errorMessage);
3646: raise_application_error(errorCode,
3648: when booleanException then
3649: rollback;
3650: errorCode := -20001;
3651: errorMessage :=
3652: ame_util.getMessage(applicationShortNameIn => 'PER',
3653: messageNameIn => 'AME_400167_ATT_STAT_USG_BOOL');
3654: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3655: routineNameIn => 'newAttributeUsage',
3656: exceptionNumberIn => errorCode,
3650: errorCode := -20001;
3651: errorMessage :=
3652: ame_util.getMessage(applicationShortNameIn => 'PER',
3653: messageNameIn => 'AME_400167_ATT_STAT_USG_BOOL');
3654: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3655: routineNameIn => 'newAttributeUsage',
3656: exceptionNumberIn => errorCode,
3657: exceptionStringIn => errorMessage);
3658: raise_application_error(errorCode,
3660: when badStaticDateUsageException then
3661: rollback;
3662: errorCode := -20001;
3663: errorMessage :=
3664: ame_util.getMessage(applicationShortNameIn => 'PER',
3665: messageNameIn => 'AME_400168_ATT_STAT_USG_DATE');
3666: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3667: routineNameIn => 'newAttributeUsage',
3668: exceptionNumberIn => errorCode,
3662: errorCode := -20001;
3663: errorMessage :=
3664: ame_util.getMessage(applicationShortNameIn => 'PER',
3665: messageNameIn => 'AME_400168_ATT_STAT_USG_DATE');
3666: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3667: routineNameIn => 'newAttributeUsage',
3668: exceptionNumberIn => errorCode,
3669: exceptionStringIn => errorMessage);
3670: raise_application_error(errorCode,
3670: raise_application_error(errorCode,
3671: errorMessage);
3672: when others then
3673: rollback;
3674: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3675: routineNameIn => 'newAttributeUsage',
3676: exceptionNumberIn => sqlcode,
3677: exceptionStringIn => '(attribute ID ' ||
3678: attributeIdIn||
3695: ame_action_usages.action_id = ame_actions.action_id and
3696: ame_action_usages.rule_id = ame_rule_usages.rule_id and
3697: ame_actions.action_type_id = actionTypeIdIn and
3698: ((sysdate between ame_action_usages.start_date and
3699: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3700: (sysdate < ame_action_usages.start_date and
3701: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3702: ame_action_usages.start_date + ame_util.oneSecond))) and
3703: ( sysdate between ame_actions.start_date and
3698: ((sysdate between ame_action_usages.start_date and
3699: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3700: (sysdate < ame_action_usages.start_date and
3701: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3702: ame_action_usages.start_date + ame_util.oneSecond))) and
3703: ( sysdate between ame_actions.start_date and
3704: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) ) and
3705: ((sysdate between ame_rule_usages.start_date and
3706: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
3700: (sysdate < ame_action_usages.start_date and
3701: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3702: ame_action_usages.start_date + ame_util.oneSecond))) and
3703: ( sysdate between ame_actions.start_date and
3704: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) ) and
3705: ((sysdate between ame_rule_usages.start_date and
3706: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
3707: (sysdate < ame_rule_usages.start_date and
3708: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
3702: ame_action_usages.start_date + ame_util.oneSecond))) and
3703: ( sysdate between ame_actions.start_date and
3704: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) ) and
3705: ((sysdate between ame_rule_usages.start_date and
3706: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
3707: (sysdate < ame_rule_usages.start_date and
3708: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
3709: ame_rule_usages.start_date + ame_util.oneSecond)));
3710: cursor applicationCursor(attributeIdIn in integer) is
3705: ((sysdate between ame_rule_usages.start_date and
3706: nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
3707: (sysdate < ame_rule_usages.start_date and
3708: ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
3709: ame_rule_usages.start_date + ame_util.oneSecond)));
3710: cursor applicationCursor(attributeIdIn in integer) is
3711: select application_id
3712: from ame_attribute_usages
3713: where
3712: from ame_attribute_usages
3713: where
3714: attribute_id = attributeIdIn and
3715: sysdate between start_date and
3716: nvl(end_date - ame_util.oneSecond, sysdate) ;
3717: applicationCount integer;
3718: commitData boolean;
3719: currentUserId integer;
3720: createdBy integer;
3726: into applicationCount
3727: from ame_attribute_usages
3728: where attribute_id = attributeIdIn and
3729: sysdate between start_date and
3730: nvl(end_date - ame_util.oneSecond, sysdate);
3731: for getAppRec in getApplicationId(actionTypeIdIn => actionTypeIdIn) loop
3732: /* for every application that uses the approval type, make sure an
3733: attribute usage exists for the required attribute */
3734: select count(*)
3737: where
3738: attribute_id = attributeIdIn and
3739: application_id = getAppRec.item_id and
3740: sysdate between start_date and
3741: nvl(end_date - ame_util.oneSecond, sysdate) ;
3742: if tempCount1 = 0 then
3743: raise_application_error(-20001,
3744: ame_util.getMessage(applicationShortNameIn => 'PER',
3745: messageNameIn => 'AME_400169_ATT_LACK_QRY_STRING'));
3740: sysdate between start_date and
3741: nvl(end_date - ame_util.oneSecond, sysdate) ;
3742: if tempCount1 = 0 then
3743: raise_application_error(-20001,
3744: ame_util.getMessage(applicationShortNameIn => 'PER',
3745: messageNameIn => 'AME_400169_ATT_LACK_QRY_STRING'));
3746: end if;
3747: end loop;
3748: select count(*)
3751: where
3752: attribute_id = attributeIdIn and
3753: action_type_id = actionTypeIdIn and
3754: sysdate between start_date and
3755: nvl(end_date - ame_util.oneSecond, sysdate) ;
3756: if tempCount > 0 then
3757: return;
3758: end if;
3759: currentUserId := ame_util.getCurrentUserId;
3755: nvl(end_date - ame_util.oneSecond, sysdate) ;
3756: if tempCount > 0 then
3757: return;
3758: end if;
3759: currentUserId := ame_util.getCurrentUserId;
3760: if(createdByIn is null) then
3761: createdBy := currentUserId;
3762: else
3763: createdBy := createdByIn;
3797: end loop;
3798: exception
3799: when others then
3800: rollback;
3801: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3802: routineNameIn => 'newMandatoryAttributes',
3803: exceptionNumberIn => sqlcode,
3804: exceptionStringIn => '(attribute ID ' ||
3805: attributeIdIn||
3810: procedure remove(attributeIdIn in integer,
3811: finalizeIn in boolean default false) as
3812: currentUserId integer;
3813: errorCode integer;
3814: errorMessage ame_util.longestStringType;
3815: inUseException exception;
3816: processingDate date;
3817: attributeType ame_attributes.attribute_type%type;
3818: begin
3816: processingDate date;
3817: attributeType ame_attributes.attribute_type%type;
3818: begin
3819: processingDate := sysdate;
3820: currentUserId := ame_util.getCurrentUserId;
3821: attributeType := getType(attributeIdIn => attributeIdIn);
3822: if attributeType = ame_util.stringAttributeType then
3823: update ame_string_values
3824: set
3818: begin
3819: processingDate := sysdate;
3820: currentUserId := ame_util.getCurrentUserId;
3821: attributeType := getType(attributeIdIn => attributeIdIn);
3822: if attributeType = ame_util.stringAttributeType then
3823: update ame_string_values
3824: set
3825: last_updated_by = currentUserId,
3826: last_update_date = processingDate,
3829: where
3830: condition_id in (select condition_id
3831: from ame_conditions
3832: where processingDate between start_date and
3833: nvl(end_date - ame_util.oneSecond, processingDate) and
3834: attribute_id = attributeIdIn) and
3835: processingDate between start_date and
3836: nvl(end_date - ame_util.oneSecond, processingDate) ;
3837: end if;
3832: where processingDate between start_date and
3833: nvl(end_date - ame_util.oneSecond, processingDate) and
3834: attribute_id = attributeIdIn) and
3835: processingDate between start_date and
3836: nvl(end_date - ame_util.oneSecond, processingDate) ;
3837: end if;
3838: update ame_conditions
3839: set
3840: last_updated_by = currentUserId,
3843: end_date = processingDate
3844: where
3845: attribute_id = attributeIdIn and
3846: processingDate between start_date and
3847: nvl(end_date - ame_util.oneSecond, processingDate) ;
3848: update ame_attributes
3849: set
3850: last_updated_by = currentUserId,
3851: last_update_date = processingDate,
3853: end_date = processingDate
3854: where
3855: attribute_id = attributeIdIn and
3856: processingDate between start_date and
3857: nvl(end_date - ame_util.oneSecond, processingDate) ;
3858: if(finalizeIn) then
3859: commit;
3860: end if;
3861: exception
3860: end if;
3861: exception
3862: when others then
3863: rollback;
3864: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3865: routineNameIn => 'remove',
3866: exceptionNumberIn => sqlcode,
3867: exceptionStringIn => '(attribute ID ' ||
3868: attributeIdIn||
3878: from ame_attribute_usages
3879: where
3880: attribute_id = attributeIdIn and
3881: sysdate between start_date and
3882: nvl(end_date - ame_util.oneSecond, sysdate) ;
3883: currentUserId integer;
3884: processingDate date;
3885: begin
3886: processingDate := sysdate;
3883: currentUserId integer;
3884: processingDate date;
3885: begin
3886: processingDate := sysdate;
3887: currentUserId := ame_util.getCurrentUserId;
3888: update ame_mandatory_attributes
3889: set
3890: last_updated_by = currentUserId,
3891: last_update_date = processingDate,
3894: where
3895: attribute_id = attributeIdIn and
3896: action_type_id = actionTypeIdIn and
3897: processingDate between start_date and
3898: nvl(end_date - ame_util.oneSecond, processingDate) ;
3899: /* Call updateUseCount after removing the attribute requirements. */
3900: for tempApplication in applicationCursor(attributeIdIn => attributeIdIn) loop
3901: updateUseCount(attributeIdIn => attributeIdIn,
3902: applicationIdIn => tempApplication.application_id,
3904: end loop;
3905: exception
3906: when others then
3907: rollback;
3908: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3909: routineNameIn => 'removeMandatoryAttributes',
3910: exceptionNumberIn => sqlcode,
3911: exceptionStringIn => '(attribute ID ' ||
3912: attributeIdIn||
3927: from ame_attributes
3928: where
3929: attribute_id = attributeIdIn and
3930: sysdate between start_date and
3931: nvl(end_date - ame_util.oneSecond, sysdate)
3932: for update;
3933: cursor startDateCursor2 is
3934: select start_date
3935: from ame_attribute_usages
3936: where
3937: attribute_id = attributeIdIn and
3938: application_id = applicationIdIn and
3939: sysdate between start_date and
3940: nvl(end_date - ame_util.oneSecond, sysdate)
3941: for update;
3942: approverTypeId integer;
3943: attributeId ame_attributes.attribute_id%type;
3944: attributeDescription ame_attributes.description%type;
3943: attributeId ame_attributes.attribute_id%type;
3944: attributeDescription ame_attributes.description%type;
3945: attributeName ame_attributes.name%type;
3946: attributeType ame_attributes.attribute_type%type;
3947: conditionIdList ame_util.idList;
3948: conditionVersionStartDate date;
3949: currentUserId integer;
3950: errorCode integer;
3951: errorMessage ame_util.longestStringType;
3947: conditionIdList ame_util.idList;
3948: conditionVersionStartDate date;
3949: currentUserId integer;
3950: errorCode integer;
3951: errorMessage ame_util.longestStringType;
3952: inUseException exception;
3953: isMandatoryException exception;
3954: lineItem ame_attributes.line_item%type;
3955: objectVersionNoDataException exception;
3969: raise objectVersionNoDataException;
3970: end if;
3971: if(parentVersionStartDateIn <> startDate) then
3972: close startDateCursor;
3973: raise ame_util.objectVersionException;
3974: end if;
3975: open startDateCursor2;
3976: fetch startDateCursor2 into startDate2;
3977: if startDateCursor2%notfound then
3978: raise objectVersionNoDataException;
3979: end if;
3980: if(childVersionStartDateIn <> startDate2) then
3981: close startDateCursor2;
3982: raise ame_util.objectVersionException;
3983: end if;
3984: /* Don't allow deleting usages for mandatory attributes. */
3985: if not allowAttributeUsageDeleteIn then
3986: if(isMandatory(attributeIdIn => attributeIdIn)) then
3998: attributeIdIn => attributeIdIn)) then
3999: raise stripingAttributeException;
4000: end if;
4001: */
4002: currentUserId := ame_util.getCurrentUserId;
4003: /* Not active, either not mandatory or allowed to delete, so delete the usage. */
4004: update ame_attribute_usages
4005: set
4006: last_updated_by = currentUserId,
4010: where
4011: attribute_id = attributeIdIn and
4012: application_id = applicationIdIn and
4013: processingDate between start_date and
4014: nvl(end_date - ame_util.oneSecond, processingDate);
4015: /*
4016: If the attribute name is
4017: (1) not used by any conditions in any rules used by the transaction type
4018: (2) is not required by any approval type (whether or not a rule uses the approval type, at present)
4035: end_date = processingDate
4036: where
4037: attribute_id = attributeIdIn and
4038: processingDate between start_date and
4039: nvl(end_date - ame_util.oneSecond, processingDate) ;
4040: attributeId := new(nameIn => attributeName,
4041: typeIn => attributeType,
4042: descriptionIn => attributeDescription,
4043: attributeIdIn => attributeIdIn,
4061: close startDateCursor;
4062: end if;
4063: attributeName := getName(attributeIdIn => attributeIdIn);
4064: errorCode := -20001;
4065: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4066: messageNameIn => 'AME_400309_ATT_NAME_STRP_ATTR',
4067: tokenNameOneIn => 'ATTRIBUTE_NAME',
4068: tokenValueOneIn => attributeName);
4069: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4065: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4066: messageNameIn => 'AME_400309_ATT_NAME_STRP_ATTR',
4067: tokenNameOneIn => 'ATTRIBUTE_NAME',
4068: tokenValueOneIn => attributeName);
4069: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4070: routineNameIn => 'removeUsage',
4071: exceptionNumberIn => errorCode,
4072: exceptionStringIn => errorMessage);
4073: raise_application_error(errorCode,
4082: close startDateCursor2;
4083: end if;
4084: errorCode := -20001;
4085: errorMessage :=
4086: ame_util.getMessage(applicationShortNameIn => 'PER',
4087: messageNameIn => 'AME_400170_ATT_MAND_CANT_DEL');
4088: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4089: routineNameIn => 'removeUsage',
4090: exceptionNumberIn => errorCode,
4084: errorCode := -20001;
4085: errorMessage :=
4086: ame_util.getMessage(applicationShortNameIn => 'PER',
4087: messageNameIn => 'AME_400170_ATT_MAND_CANT_DEL');
4088: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4089: routineNameIn => 'removeUsage',
4090: exceptionNumberIn => errorCode,
4091: exceptionStringIn => errorMessage);
4092: raise_application_error(errorCode,
4100: close startDateCursor2;
4101: end if;
4102: errorCode := -20001;
4103: errorMessage :=
4104: ame_util.getMessage(applicationShortNameIn => 'PER',
4105: messageNameIn => 'AME_400171_ATT_IS_IN_USE');
4106: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4107: routineNameIn => 'removeUsage',
4108: exceptionNumberIn => errorCode,
4102: errorCode := -20001;
4103: errorMessage :=
4104: ame_util.getMessage(applicationShortNameIn => 'PER',
4105: messageNameIn => 'AME_400171_ATT_IS_IN_USE');
4106: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4107: routineNameIn => 'removeUsage',
4108: exceptionNumberIn => errorCode,
4109: exceptionStringIn => errorMessage);
4110: raise_application_error(errorCode,
4108: exceptionNumberIn => errorCode,
4109: exceptionStringIn => errorMessage);
4110: raise_application_error(errorCode,
4111: errorMessage);
4112: when ame_util.objectVersionException then
4113: rollback;
4114: if(startDateCursor%isOpen) then
4115: close startDateCursor;
4116: end if;
4118: close startDateCursor2;
4119: end if;
4120: errorCode := -20001;
4121: errorMessage :=
4122: ame_util.getMessage(applicationShortNameIn => 'PER',
4123: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4124: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4125: routineNameIn => 'removeUsage',
4126: exceptionNumberIn => errorCode,
4120: errorCode := -20001;
4121: errorMessage :=
4122: ame_util.getMessage(applicationShortNameIn => 'PER',
4123: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4124: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4125: routineNameIn => 'removeUsage',
4126: exceptionNumberIn => errorCode,
4127: exceptionStringIn => errorMessage);
4128: raise_application_error(errorCode,
4136: close startDateCursor2;
4137: end if;
4138: errorCode := -20001;
4139: errorMessage :=
4140: ame_util.getMessage(applicationShortNameIn => 'PER',
4141: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
4142: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4143: routineNameIn => 'removeUsage',
4144: exceptionNumberIn => errorCode,
4138: errorCode := -20001;
4139: errorMessage :=
4140: ame_util.getMessage(applicationShortNameIn => 'PER',
4141: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
4142: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4143: routineNameIn => 'removeUsage',
4144: exceptionNumberIn => errorCode,
4145: exceptionStringIn => errorMessage);
4146: raise_application_error(errorCode,
4152: end if;
4153: if(startDateCursor2%isOpen) then
4154: close startDateCursor2;
4155: end if;
4156: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4157: routineNameIn => 'removeUsage',
4158: exceptionNumberIn => sqlcode,
4159: exceptionStringIn => '(attribute ID ' ||
4160: attributeIdIn||
4164: end removeUsage;
4165: /*
4166: AME_STRIPING
4167: procedure setStripingAttributesToNull(applicationIdIn in integer,
4168: oldStripedAttributesIn in ame_util.idList default ame_util.emptyIdList,
4169: lastStripingAttributeIn in boolean default false) as
4170: cursor getStripingAttributesCursor(applicationIdIn in integer) is
4171: select ame_attribute_usages.attribute_id,
4172: ame_attribute_usages.is_static,
4177: ame_attribute_usages
4178: where
4179: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
4180: ame_attribute_usages.application_id = applicationIdIn and
4181: ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
4182: (ame_attribute_usages.start_date <= sysdate and
4183: (ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date)) and
4184: (ame_attributes.start_date <= sysdate and
4185: (ame_attributes.end_date is null or sysdate < ame_attributes.end_date));
4187: oldAttributeCount integer;
4188: startDate varchar2(50);
4189: stripeSetCount integer;
4190: stripeSetId ame_stripe_sets.stripe_set_id%type;
4191: stripeSetIds ame_util.idList;
4192: versionStartDate date;
4193: begin
4194: oldAttributeCount := oldStripedAttributesIn.count;
4195: for getStripingAttributesRec in
4199: /* check to see if the attribute is no longer in the new set */
4200: /*
4201: for i in 1..oldAttributeCount loop
4202: if(getStripingAttributesRec.attribute_id = oldStripedAttributesIn(i)) then
4203: endDate := sysdate - ame_util.oneSecond;
4204: changeUsage(attributeIdIn => getStripingAttributesRec.attribute_id,
4205: applicationIdIn => applicationIdIn,
4206: staticUsageIn => getStripingAttributesRec.is_static,
4207: queryStringIn => getStripingAttributesRec.query_string,
4207: queryStringIn => getStripingAttributesRec.query_string,
4208: endDateIn => endDate,
4209: newStartDateIn => sysdate,
4210: lineItemAttributeIn => getStripingAttributesRec.line_item,
4211: isStripingAttributeIn => ame_util.booleanFalse);
4212: ame_admin_pkg.removeStripeSetAttributes(applicationIdIn => applicationIdIn,
4213: attributeIdIn => getStripingAttributesRec.attribute_id);
4214: end if;
4215: end loop;
4220: stripeSetIdsOut => stripeSetIds);
4221: ame_admin_pkg.removeAllStripeSets(applicationIdIn => applicationIdIn,
4222: deleteStripeSetIdZeroIn => lastStripingAttributeIn);
4223: ame_rule_pkg.removeRuleStripeSet(stripeSetIdsIn => stripeSetIds);
4224: stripeSetId := ame_util.getCurrentStripeSetId(applicationIdIn => applicationIdIn);
4225: owa_util.mime_header('text/html', FALSE);
4226: owa_cookie.remove(name => ame_util.getStripeSetCookieName(applicationIdIn => applicationIdIn),
4227: val => to_char(stripeSetId));
4228: owa_util.http_header_close;
4222: deleteStripeSetIdZeroIn => lastStripingAttributeIn);
4223: ame_rule_pkg.removeRuleStripeSet(stripeSetIdsIn => stripeSetIds);
4224: stripeSetId := ame_util.getCurrentStripeSetId(applicationIdIn => applicationIdIn);
4225: owa_util.mime_header('text/html', FALSE);
4226: owa_cookie.remove(name => ame_util.getStripeSetCookieName(applicationIdIn => applicationIdIn),
4227: val => to_char(stripeSetId));
4228: owa_util.http_header_close;
4229: end if;
4230: commit;
4230: commit;
4231: exception
4232: when others then
4233: rollback;
4234: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4235: routineNameIn => 'setStripingAttributesToNull',
4236: exceptionNumberIn => sqlcode,
4237: exceptionStringIn => sqlerrm);
4238: raise;
4242: applicationIdIn in integer,
4243: finalizeIn in boolean default true) as
4244: currentUserId integer;
4245: errorCode integer;
4246: errorMessage ame_util.longestStringType;
4247: /*
4248: AME_STRIPING
4249: stripingAttribute ame_attribute_usages.is_striping_attribute%type;
4250: */
4264: /*
4265: AME_STRIPING
4266: if(isStripingAttribute(attributeIdIn => attributeIdIn,
4267: applicationIdIn => applicationIdIn)) then
4268: stripingAttribute := ame_util.booleanTrue;
4269: else
4270: stripingAttribute := ame_util.booleanFalse;
4271: end if;
4272: */
4266: if(isStripingAttribute(attributeIdIn => attributeIdIn,
4267: applicationIdIn => applicationIdIn)) then
4268: stripingAttribute := ame_util.booleanTrue;
4269: else
4270: stripingAttribute := ame_util.booleanFalse;
4271: end if;
4272: */
4273: if(queryString is null and
4274: staticUsage = ame_util.booleanFalse) then
4270: stripingAttribute := ame_util.booleanFalse;
4271: end if;
4272: */
4273: if(queryString is null and
4274: staticUsage = ame_util.booleanFalse) then
4275: raise nullQueryException;
4276: end if;
4277: currentUserId := ame_util.getCurrentUserId;
4278: useCount := calculateUseCount(attributeIdIn => attributeIdIn,
4273: if(queryString is null and
4274: staticUsage = ame_util.booleanFalse) then
4275: raise nullQueryException;
4276: end if;
4277: currentUserId := ame_util.getCurrentUserId;
4278: useCount := calculateUseCount(attributeIdIn => attributeIdIn,
4279: applicationIdIn => applicationIdIn);
4280: update ame_attribute_usages
4281: set use_count = useCount
4282: where
4283: application_id = applicationIdIn and
4284: attribute_id = attributeIdIn and
4285: processingDate between start_date and
4286: nvl(end_date - ame_util.oneSecond, processingDate);
4287: /* newAttributeUsage calls calculateUseCount to get the new use_count value. */
4288: /*
4289: AME_STRIPING
4290: newAttributeUsage(attributeIdIn => attributeIdIn,
4299: when nullQueryException then
4300: rollback;
4301: errorCode := -20001;
4302: errorMessage :=
4303: ame_util.getMessage(applicationShortNameIn => 'PER',
4304: messageNameIn => 'AME_400172_ATT_NULL_ATT_USAGE');
4305: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4306: routineNameIn => 'updateUseCount',
4307: exceptionNumberIn => errorCode,
4301: errorCode := -20001;
4302: errorMessage :=
4303: ame_util.getMessage(applicationShortNameIn => 'PER',
4304: messageNameIn => 'AME_400172_ATT_NULL_ATT_USAGE');
4305: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4306: routineNameIn => 'updateUseCount',
4307: exceptionNumberIn => errorCode,
4308: exceptionStringIn => errorMessage);
4309: raise_application_error(errorCode,
4309: raise_application_error(errorCode,
4310: errorMessage);
4311: when others then
4312: rollback;
4313: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4314: routineNameIn => 'updateUseCount',
4315: exceptionNumberIn => sqlcode,
4316: exceptionStringIn => '(attribute ID ' ||
4317: attributeIdIn||
4320: raise;
4321: end updateUseCount;
4322: procedure updateUseCount2(ruleIdIn in integer,
4323: applicationIdIn in integer) as
4324: attributeIds ame_util.idList;
4325: upperLimit integer;
4326: useCount integer;
4327: processingDate date;
4328: begin
4338: where
4339: application_id = applicationIdIn and
4340: attribute_id = attributeIds(i) and
4341: processingDate between start_date and
4342: nvl(end_date - ame_util.oneSecond, processingDate) ;
4343: end loop;
4344: commit;
4345: exception
4346: when others then
4344: commit;
4345: exception
4346: when others then
4347: rollback;
4348: ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4349: routineNameIn => 'updateUseCount2',
4350: exceptionNumberIn => sqlcode,
4351: exceptionStringIn => '(rule ID ' ||
4352: ruleIdIn||