18: end if;
19: return '';
20: exception
21: when others then
22: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
23: routineNameIn => 'isValidApprover',
24: exceptionNumberIn => sqlcode,
25: exceptionStringIn => sqlerrm);
26: return sqlerrm || ' ';
29: function getActionTypeName(actionTypeIdIn in integer) return varchar2 as
30: actionTypeName ame_action_types_vl.user_action_type_name%type;
31: begin
32: if (actionTypeIdIn = -1) then
33: return ame_util.getMessage('PER','AME_400637_TEXT_NONE');
34: end if;
35: select user_action_type_name
36: into actionTypeName
37: from ame_action_types_vl
36: into actionTypeName
37: from ame_action_types_vl
38: where action_type_id = actionTypeIdIn
39: and sysdate between start_date
40: and nvl(end_date - ame_util.oneSecond, sysdate);
41: return actionTypeName;
42: exception
43: when others then
44: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
40: and nvl(end_date - ame_util.oneSecond, sysdate);
41: return actionTypeName;
42: exception
43: when others then
44: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
45: routineNameIn => 'getActionTypeName',
46: exceptionNumberIn => sqlcode,
47: exceptionStringIn => sqlerrm);
48: raise;
55: select user_approval_group_name
56: into actionTypeName
57: from ame_approval_groups_vl
58: where approval_group_id = groupIdIn
59: and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
60: return actionTypeName;
61: exception
62: when others then
63: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
59: and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
60: return actionTypeName;
61: exception
62: when others then
63: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
64: routineNameIn => 'getApproverGroupName',
65: exceptionNumberIn => sqlcode,
66: exceptionStringIn => sqlerrm);
67: raise;
73: begin
74: return statusIn;
75: exception
76: when others then
77: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
78: routineNameIn => 'getApprovalStatusDesc',
79: exceptionNumberIn => sqlcode,
80: exceptionStringIn => sqlerrm);
81: raise;
97: exception
98: when no_data_found then
99: return null;
100: when others then
101: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
102: routineNameIn => 'getApprovalCategoryDesc',
103: exceptionNumberIn => sqlcode,
104: exceptionStringIn => sqlerrm);
105: raise;
119: and nvl(end_date_active, trunc(sysdate));
120: return(authorityDesc);
121: exception
122: when others then
123: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
124: routineNameIn => 'getAuthorityDesc',
125: exceptionNumberIn => sqlcode,
126: exceptionStringIn => sqlerrm);
127: raise;
133: begin
134: return apiInsertionIn;
135: exception
136: when others then
137: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
138: routineNameIn => 'getApiInsertionDesc',
139: exceptionNumberIn => sqlcode,
140: exceptionStringIn => sqlerrm);
141: raise;
154: and nvl(end_date_active, trunc(sysdate));
155: return origSystemDesc;
156: exception
157: when others then
158: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
159: routineNameIn => 'getOrigSystemDesc',
160: exceptionNumberIn => sqlcode,
161: exceptionStringIn => sqlerrm);
162: raise;
176: and nvl(end_date_active, trunc(sysdate));
177: return ruleTypeDesc;
178: exception
179: when others then
180: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
181: routineNameIn => 'getRuleTypeDesc',
182: exceptionNumberIn => sqlcode,
183: exceptionStringIn => sqlerrm);
184: raise;
194: into ruleTypeCode
195: from ame_rules
196: where rule_id = ruleIdIn
197: and effectiveDateIn between start_date
198: and nvl(end_date - ame_util.oneSecond, sysdate);
199: return getApprovalCategoryDesc(ruleTypeCode);
200: exception
201: when others then
202: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
198: and nvl(end_date - ame_util.oneSecond, sysdate);
199: return getApprovalCategoryDesc(ruleTypeCode);
200: exception
201: when others then
202: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
203: routineNameIn => 'getRuleType',
204: exceptionNumberIn => sqlcode,
205: exceptionStringIn => sqlerrm);
206: raise;
219: from ame_rule_usages
220: where rule_id = ruleIdIn
221: and item_id = applicationIdIn
222: and effectiveDateIn between start_date
223: and nvl(end_date - ame_util.oneSecond, sysdate);
224: exception
225: when no_data_found then
226: categoryCode := 'A';
227: end;
227: end;
228: return getApprovalCategoryDesc(categoryCode);
229: exception
230: when others then
231: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
232: routineNameIn => 'getRuleCategory',
233: exceptionNumberIn => sqlcode,
234: exceptionStringIn => sqlerrm);
235: raise;
243: select condition_id
244: from ame_condition_usages
245: where rule_id = ruleIdIn
246: and effectiveDateIn between start_date
247: and nvl(end_date - ame_util.oneSecond, sysdate);
248: conditionsList ame_util.longestStringType;
249: conditionIdList ame_util.idList;
250: begin
251: open getCondtitionsCursor(ruleIdIn => ruleIdIn
244: from ame_condition_usages
245: where rule_id = ruleIdIn
246: and effectiveDateIn between start_date
247: and nvl(end_date - ame_util.oneSecond, sysdate);
248: conditionsList ame_util.longestStringType;
249: conditionIdList ame_util.idList;
250: begin
251: open getCondtitionsCursor(ruleIdIn => ruleIdIn
252: ,effectiveDateIn => effectiveDateIn);
245: where rule_id = ruleIdIn
246: and effectiveDateIn between start_date
247: and nvl(end_date - ame_util.oneSecond, sysdate);
248: conditionsList ame_util.longestStringType;
249: conditionIdList ame_util.idList;
250: begin
251: open getCondtitionsCursor(ruleIdIn => ruleIdIn
252: ,effectiveDateIn => effectiveDateIn);
253: fetch getCondtitionsCursor bulk collect
257: if x > 1 then
258: conditionsList := conditionsList || fnd_global.local_chr(ascii_chr => 13);
259: end if;
260: conditionsList := conditionsList
261: || ame_utility_pkg.get_condition_description(p_condition_id => conditionIdList(x));
262: end loop;
263: return conditionsList;
264: exception
265: when others then
262: end loop;
263: return conditionsList;
264: exception
265: when others then
266: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
267: routineNameIn => 'getConditionsList',
268: exceptionNumberIn => sqlcode,
269: exceptionStringIn => sqlerrm);
270: raise;
278: into itemName
279: from ame_item_classes_vl
280: where item_class_id = itemClassIdIn and
281: sysdate between start_date and
282: nvl(end_date - ame_util.oneSecond, sysdate) ;
283: return(itemName);
284: exception
285: when others then
286: rollback;
283: return(itemName);
284: exception
285: when others then
286: rollback;
287: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
288: routineNameIn => 'getItemClassNameById',
289: exceptionNumberIn => sqlcode,
290: exceptionStringIn => sqlerrm);
291: raise;
299: into itemName
300: from ame_item_classes_vl
301: where trim(name) = trim(itemClassNameIn) and
302: sysdate between start_date and
303: nvl(end_date - ame_util.oneSecond, sysdate) ;
304: return(itemName);
305: exception
306: when others then
307: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
303: nvl(end_date - ame_util.oneSecond, sysdate) ;
304: return(itemName);
305: exception
306: when others then
307: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
308: routineNameIn => 'getItemClassName',
309: exceptionNumberIn => sqlcode,
310: exceptionStringIn => sqlerrm);
311: raise;
319: select action_id
320: from ame_action_usages
321: where rule_id = ruleIdIn
322: and effectiveDateIn between start_date
323: and nvl(end_date - ame_util.oneSecond, sysdate);
324: actionsList ame_util.longestStringType;
325: actionIdList ame_util.idList;
326: begin
327: open getActionsCursor(ruleIdIn => ruleIdIn
320: from ame_action_usages
321: where rule_id = ruleIdIn
322: and effectiveDateIn between start_date
323: and nvl(end_date - ame_util.oneSecond, sysdate);
324: actionsList ame_util.longestStringType;
325: actionIdList ame_util.idList;
326: begin
327: open getActionsCursor(ruleIdIn => ruleIdIn
328: ,effectiveDateIn => effectiveDateIn);
321: where rule_id = ruleIdIn
322: and effectiveDateIn between start_date
323: and nvl(end_date - ame_util.oneSecond, sysdate);
324: actionsList ame_util.longestStringType;
325: actionIdList ame_util.idList;
326: begin
327: open getActionsCursor(ruleIdIn => ruleIdIn
328: ,effectiveDateIn => effectiveDateIn);
329: fetch getActionsCursor bulk collect
333: if x <> 1 then
334: actionsList := actionsList || fnd_global.local_chr(ascii_chr => 13);
335: end if;
336: actionsList := actionsList
337: || ame_utility_pkg.get_action_description(p_action_id => actionIdList(x));
338: end loop;
339: return actionsList;
340: exception
341: when others then
338: end loop;
339: return actionsList;
340: exception
341: when others then
342: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
343: routineNameIn => 'getActionsList',
344: exceptionNumberIn => sqlcode,
345: exceptionStringIn => sqlerrm);
346: raise;
354: into ruleDesc
355: from ame_rules_vl
356: where rule_id = ruleIdIn
357: and effectiveRuleDate between start_date
358: and nvl(end_date - ame_util.oneSecond, sysdate);
359: return ruleDesc;
360: exception
361: when others then
362: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
358: and nvl(end_date - ame_util.oneSecond, sysdate);
359: return ruleDesc;
360: exception
361: when others then
362: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
363: routineNameIn => 'getRuleDescription',
364: exceptionNumberIn => sqlcode,
365: exceptionStringIn => sqlerrm);
366: raise;
367: return null;
368: end getRuleDescription;
369: --+
370: function getSourceDesc(sourceIn in varchar2, effectiveRuleDate in date) return varchar2 as
371: sourceDesc ame_util.longestStringType;
372: ruleIdList ame_util.idList;
373: sourceDescription ame_util.stringType;
374: begin
375: ame_util.parseSourceValue(sourceValueIn => sourceIn
368: end getRuleDescription;
369: --+
370: function getSourceDesc(sourceIn in varchar2, effectiveRuleDate in date) return varchar2 as
371: sourceDesc ame_util.longestStringType;
372: ruleIdList ame_util.idList;
373: sourceDescription ame_util.stringType;
374: begin
375: ame_util.parseSourceValue(sourceValueIn => sourceIn
376: ,sourceDescriptionOut => sourceDescription
369: --+
370: function getSourceDesc(sourceIn in varchar2, effectiveRuleDate in date) return varchar2 as
371: sourceDesc ame_util.longestStringType;
372: ruleIdList ame_util.idList;
373: sourceDescription ame_util.stringType;
374: begin
375: ame_util.parseSourceValue(sourceValueIn => sourceIn
376: ,sourceDescriptionOut => sourceDescription
377: ,ruleIdListOut => ruleIdList);
371: sourceDesc ame_util.longestStringType;
372: ruleIdList ame_util.idList;
373: sourceDescription ame_util.stringType;
374: begin
375: ame_util.parseSourceValue(sourceValueIn => sourceIn
376: ,sourceDescriptionOut => sourceDescription
377: ,ruleIdListOut => ruleIdList);
378: for x in 1 .. ruleIdList.count loop
379: if x > 1 then
383: end loop;
384: return sourceDesc;
385: exception
386: when others then
387: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
388: routineNameIn => 'getSourceDesc',
389: exceptionNumberIn => sqlcode,
390: exceptionStringIn => sqlerrm);
391: raise;
405: where ame_attribute_usages.attribute_id = ame_attributes.attribute_id
406: and ame_attribute_usages.application_id = applicationIdIn
407: and ame_attributes.item_class_id = itemClassIdIn
408: and sysdate between ame_attribute_usages.start_date and
409: nvl(ame_attribute_usages.end_date - ame_util.oneSecond,sysdate)
410: and sysdate between ame_attributes.start_date and
411: nvl(ame_attributes.end_date - ame_util.oneSecond,sysdate);
412: if(atrCount > 0) then
413: retValue := 'Enabled';
407: and ame_attributes.item_class_id = itemClassIdIn
408: and sysdate between ame_attribute_usages.start_date and
409: nvl(ame_attribute_usages.end_date - ame_util.oneSecond,sysdate)
410: and sysdate between ame_attributes.start_date and
411: nvl(ame_attributes.end_date - ame_util.oneSecond,sysdate);
412: if(atrCount > 0) then
413: retValue := 'Enabled';
414: end if;
415: return retValue;
414: end if;
415: return retValue;
416: exception
417: When others then
418: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
419: routineNameIn => 'isAttributesExist',
420: exceptionNumberIn => sqlcode,
421: exceptionStringIn => sqlerrm);
422: raise;
483: dummy_date date;
484: dummy_curr_code boolean := true;
485: dummy_conv_type boolean := true ;
486: begin
487: if attributeTypeIn = ame_util.numberAttributeType then
488: -- number attribute validation
489: begin
490: dummy_num := to_number(attributeValue1In);
491: exception
515: );
516: return 'ERROR_EXIST';
517: end;
518: end;
519: elsif attributeTypeIn = ame_util.dateAttributeType then
520: --date validation
521: begin
522: dummy_date := to_date(attributeValue1In, ame_util.versionDateFormatModel);
523: exception
518: end;
519: elsif attributeTypeIn = ame_util.dateAttributeType then
520: --date validation
521: begin
522: dummy_date := to_date(attributeValue1In, ame_util.versionDateFormatModel);
523: exception
524: when others then
525: setErrorMessage(attributeNameIn => attributeNameIn
526: ,attributeItemTypeIn => attributeItemTypeIn
528: ,itemIdIn => itemIdIn
529: );
530: return 'ERROR_EXIST';
531: end;
532: elsif attributeTypeIn = ame_util.booleanAttributeType then
533: --boolean attribute validation
534: if trim(attributeValue1In) <> ame_util.booleanAttributeTrue and
535: trim(attributeValue1In) <> ame_util.booleanAttributeFalse then
536: setErrorMessage(attributeNameIn => attributeNameIn
530: return 'ERROR_EXIST';
531: end;
532: elsif attributeTypeIn = ame_util.booleanAttributeType then
533: --boolean attribute validation
534: if trim(attributeValue1In) <> ame_util.booleanAttributeTrue and
535: trim(attributeValue1In) <> ame_util.booleanAttributeFalse then
536: setErrorMessage(attributeNameIn => attributeNameIn
537: ,attributeItemTypeIn => attributeItemTypeIn
538: ,itemClassIdIn => itemClassIdIn
531: end;
532: elsif attributeTypeIn = ame_util.booleanAttributeType then
533: --boolean attribute validation
534: if trim(attributeValue1In) <> ame_util.booleanAttributeTrue and
535: trim(attributeValue1In) <> ame_util.booleanAttributeFalse then
536: setErrorMessage(attributeNameIn => attributeNameIn
537: ,attributeItemTypeIn => attributeItemTypeIn
538: ,itemClassIdIn => itemClassIdIn
539: ,itemIdIn => itemIdIn
539: ,itemIdIn => itemIdIn
540: );
541: return 'ERROR_EXIST';
542: end if;
543: elsif attributeTypeIn = ame_util.currencyAttributeType then
544: -- Currency attr validation
545: begin
546: dummy_num := to_number(attributeValue1In);
547: dummy_curr_code := ame_util.isCurrencyCodeValid(currencyCodeIn => attributeValue2In);
543: elsif attributeTypeIn = ame_util.currencyAttributeType then
544: -- Currency attr validation
545: begin
546: dummy_num := to_number(attributeValue1In);
547: dummy_curr_code := ame_util.isCurrencyCodeValid(currencyCodeIn => attributeValue2In);
548: dummy_conv_type := ame_util.isConversionTypeValid(conversionTypeIn => attributeValue3In);
549: if (dummy_curr_code is not null and dummy_curr_code = false) or
550: (dummy_curr_code is not null and dummy_conv_type = false )then
551: setErrorMessage(attributeNameIn => attributeNameIn
544: -- Currency attr validation
545: begin
546: dummy_num := to_number(attributeValue1In);
547: dummy_curr_code := ame_util.isCurrencyCodeValid(currencyCodeIn => attributeValue2In);
548: dummy_conv_type := ame_util.isConversionTypeValid(conversionTypeIn => attributeValue3In);
549: if (dummy_curr_code is not null and dummy_curr_code = false) or
550: (dummy_curr_code is not null and dummy_conv_type = false )then
551: setErrorMessage(attributeNameIn => attributeNameIn
552: ,attributeItemTypeIn => attributeItemTypeIn
569: end checkConversionError;
570:
571: procedure getNonMandAttributes(applicationIdIn in integer,
572: itemClassIdIn in integer,
573: attributeIdOut out nocopy ame_util.stringList,
574: attributeNameOut out nocopy ame_util.stringList,
575: attributeTypeOut out nocopy ame_util.stringList,
576: approverTypeIdOut out nocopy ame_util.idList) as
577: cursor attributeCursor(applicationIdIn in integer,
570:
571: procedure getNonMandAttributes(applicationIdIn in integer,
572: itemClassIdIn in integer,
573: attributeIdOut out nocopy ame_util.stringList,
574: attributeNameOut out nocopy ame_util.stringList,
575: attributeTypeOut out nocopy ame_util.stringList,
576: approverTypeIdOut out nocopy ame_util.idList) as
577: cursor attributeCursor(applicationIdIn in integer,
578: headerItemClassIdIn in integer) is
571: procedure getNonMandAttributes(applicationIdIn in integer,
572: itemClassIdIn in integer,
573: attributeIdOut out nocopy ame_util.stringList,
574: attributeNameOut out nocopy ame_util.stringList,
575: attributeTypeOut out nocopy ame_util.stringList,
576: approverTypeIdOut out nocopy ame_util.idList) as
577: cursor attributeCursor(applicationIdIn in integer,
578: headerItemClassIdIn in integer) is
579: select
572: itemClassIdIn in integer,
573: attributeIdOut out nocopy ame_util.stringList,
574: attributeNameOut out nocopy ame_util.stringList,
575: attributeTypeOut out nocopy ame_util.stringList,
576: approverTypeIdOut out nocopy ame_util.idList) as
577: cursor attributeCursor(applicationIdIn in integer,
578: headerItemClassIdIn in integer) is
579: select
580: ame_attributes.attribute_id,
589: ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
590: ame_attributes.item_class_id = ame_item_classes.item_class_id and
591: ame_item_classes.item_class_id = headerItemClassIdIn and
592: ame_attribute_usages.application_id = applicationIdIn and
593: nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
594: ame_attributes.attribute_id not in
595: (select attribute_id from ame_mandatory_attributes
596: where action_type_id = -1 and
597: sysdate between ame_mandatory_attributes.start_date and
594: ame_attributes.attribute_id not in
595: (select attribute_id from ame_mandatory_attributes
596: where action_type_id = -1 and
597: sysdate between ame_mandatory_attributes.start_date and
598: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
599: sysdate between ame_attributes.start_date and
600: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
601: sysdate between ame_attribute_usages.start_date and
602: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
596: where action_type_id = -1 and
597: sysdate between ame_mandatory_attributes.start_date and
598: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
599: sysdate between ame_attributes.start_date and
600: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
601: sysdate between ame_attribute_usages.start_date and
602: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
603: sysdate between ame_item_classes.start_date and
604: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
598: nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
599: sysdate between ame_attributes.start_date and
600: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
601: sysdate between ame_attribute_usages.start_date and
602: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
603: sysdate between ame_item_classes.start_date and
604: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
605: order by ame_attributes.name;
606: tempIndex integer;
600: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
601: sysdate between ame_attribute_usages.start_date and
602: nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
603: sysdate between ame_item_classes.start_date and
604: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
605: order by ame_attributes.name;
606: tempIndex integer;
607: begin
608: tempIndex := 1;
617: end loop;
618: exception
619: when others then
620: rollback;
621: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
622: routineNameIn => 'getNonMandAttributes',
623: exceptionNumberIn => sqlcode,
624: exceptionStringIn => '(application ID ' ||
625: applicationIdIn||
638: ,atr.attribute_type
639: ,atr.approver_type_id
640: from ame_attributes atr
641: ,ame_attribute_usages atu
642: where atr.name in (ame_util.jobLevelStartingPointAttribute
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
639: ,atr.approver_type_id
640: from ame_attributes atr
641: ,ame_attribute_usages atu
642: where atr.name in (ame_util.jobLevelStartingPointAttribute
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
647: ,ame_util.secondStartingPointAttribute
640: from ame_attributes atr
641: ,ame_attribute_usages atu
642: where atr.name in (ame_util.jobLevelStartingPointAttribute
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
647: ,ame_util.secondStartingPointAttribute
648: )
641: ,ame_attribute_usages atu
642: where atr.name in (ame_util.jobLevelStartingPointAttribute
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
647: ,ame_util.secondStartingPointAttribute
648: )
649: and atr.attribute_id = atu.attribute_id
642: where atr.name in (ame_util.jobLevelStartingPointAttribute
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
647: ,ame_util.secondStartingPointAttribute
648: )
649: and atr.attribute_id = atu.attribute_id
650: and atu.application_id = applicationIdIn
643: ,ame_util.nonDefStartingPointPosAttr
644: ,ame_util.nonDefPosStructureAttr
645: ,ame_util.supStartingPointAttribute
646: ,ame_util.firstStartingPointAttribute
647: ,ame_util.secondStartingPointAttribute
648: )
649: and atr.attribute_id = atu.attribute_id
650: and atu.application_id = applicationIdIn
651: and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
650: and atu.application_id = applicationIdIn
651: and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
652: and sysdate between atu.start_date and nvl(atu.end_date - (1/86400), sysdate);
653:
654: attributeIds ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
651: and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
652: and sysdate between atu.start_date and nvl(atu.end_date - (1/86400), sysdate);
653:
654: attributeIds ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
652: and sysdate between atu.start_date and nvl(atu.end_date - (1/86400), sysdate);
653:
654: attributeIds ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
653:
654: attributeIds ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
654: attributeIds ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
655: attributeIds2 ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
663: itemIds ame_util.stringList;
656: attributeNames ame_util.stringList;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
663: itemIds ame_util.stringList;
664: upperLimit integer;
657: attributeTypes ame_util.stringList;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
663: itemIds ame_util.stringList;
664: upperLimit integer;
665: workflowItemKey wf_item_activity_statuses.item_key%type;
658: attributeValue1 ame_util.attributeValueType;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
663: itemIds ame_util.stringList;
664: upperLimit integer;
665: workflowItemKey wf_item_activity_statuses.item_key%type;
666: workflowItemType wf_item_activity_statuses.item_type%type;
659: attributeValue2 ame_util.attributeValueType;
660: attributeValue3 ame_util.attributeValueType;
661: itemClassIdsList ame_util.stringList;
662: itemClassNamesList ame_util.stringList;
663: itemIds ame_util.stringList;
664: upperLimit integer;
665: workflowItemKey wf_item_activity_statuses.item_key%type;
666: workflowItemType wf_item_activity_statuses.item_type%type;
667: variantAttributeIds ame_util.idList;
663: itemIds ame_util.stringList;
664: upperLimit integer;
665: workflowItemKey wf_item_activity_statuses.item_key%type;
666: workflowItemType wf_item_activity_statuses.item_type%type;
667: variantAttributeIds ame_util.idList;
668: variantAttributeNames ame_util.stringList;
669: variantAttributeTypes ame_util.stringList;
670: variantAttributeApprType ame_util.idList;
671: approverTypeIdList ame_util.idList;
664: upperLimit integer;
665: workflowItemKey wf_item_activity_statuses.item_key%type;
666: workflowItemType wf_item_activity_statuses.item_type%type;
667: variantAttributeIds ame_util.idList;
668: variantAttributeNames ame_util.stringList;
669: variantAttributeTypes ame_util.stringList;
670: variantAttributeApprType ame_util.idList;
671: approverTypeIdList ame_util.idList;
672: itemClassName varchar2(100);
665: workflowItemKey wf_item_activity_statuses.item_key%type;
666: workflowItemType wf_item_activity_statuses.item_type%type;
667: variantAttributeIds ame_util.idList;
668: variantAttributeNames ame_util.stringList;
669: variantAttributeTypes ame_util.stringList;
670: variantAttributeApprType ame_util.idList;
671: approverTypeIdList ame_util.idList;
672: itemClassName varchar2(100);
673: convErrYes boolean;
666: workflowItemType wf_item_activity_statuses.item_type%type;
667: variantAttributeIds ame_util.idList;
668: variantAttributeNames ame_util.stringList;
669: variantAttributeTypes ame_util.stringList;
670: variantAttributeApprType ame_util.idList;
671: approverTypeIdList ame_util.idList;
672: itemClassName varchar2(100);
673: convErrYes boolean;
674: purgeValueYes boolean;
667: variantAttributeIds ame_util.idList;
668: variantAttributeNames ame_util.stringList;
669: variantAttributeTypes ame_util.stringList;
670: variantAttributeApprType ame_util.idList;
671: approverTypeIdList ame_util.idList;
672: itemClassName varchar2(100);
673: convErrYes boolean;
674: purgeValueYes boolean;
675: begin
717: ,attributeIdOut => attributeIds
718: ,attributeNameOut => attributeNames
719: ,attributeTypeOut => attributeTypes);
720: upperLimit := attributeIds.count;
721: ame_util.getWorkflowAttributeValues(applicationIdIn => applicationIdIn,
722: transactionIdIn => transactionIdIn,
723: workflowItemKeyOut => workflowItemKey,
724: workflowItemTypeOut => workflowItemType);
725: --+
724: workflowItemTypeOut => workflowItemType);
725: --+
726: for i in 1 .. upperLimit loop
727: --+
728: if attributeNames(i) = ame_util.workflowItemKeyAttribute then
729: attributeValue1 := workflowItemKey;
730: attributeValue2 := null;
731: attributeValue3 := null;
732: elsif attributeNames(i) = ame_util.workflowItemTypeAttribute then
728: if attributeNames(i) = ame_util.workflowItemKeyAttribute then
729: attributeValue1 := workflowItemKey;
730: attributeValue2 := null;
731: attributeValue3 := null;
732: elsif attributeNames(i) = ame_util.workflowItemTypeAttribute then
733: attributeValue1 := workflowItemType;
734: attributeValue2 := null;
735: attributeValue3 := null;
736: else
781: --+
782: --+ non mandatory header attributes
783: --+
784: getNonMandAttributes(applicationIdIn => applicationIdIn
785: ,itemClassIdIn => ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerItemClassName)
786: ,attributeIdOut => attributeIds
787: ,attributeNameOut => attributeNames
788: ,attributeTypeOut => attributeTypes
789: ,approverTypeIdOut => approverTypeIdList);
983: return;
984: end if;
985: exception
986: When others then
987: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
988: routineNameIn => 'populateRealTransAttributes',
989: exceptionNumberIn => sqlcode,
990: exceptionStringIn => sqlerrm);
991: if sqlcode < -20001 then
1004: -- 'N' for a test transaction
1005: ,processPriorities in varchar2
1006: ,rulesOut out nocopy ame_rules_list
1007: ,errString out nocopy varchar2) as
1008: ruleItemClassIds ame_util.idList;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1005: ,processPriorities in varchar2
1006: ,rulesOut out nocopy ame_rules_list
1007: ,errString out nocopy varchar2) as
1008: ruleItemClassIds ame_util.idList;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1013: ruleIds ame_util.idList;
1006: ,rulesOut out nocopy ame_rules_list
1007: ,errString out nocopy varchar2) as
1008: ruleItemClassIds ame_util.idList;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1013: ruleIds ame_util.idList;
1014: rulesList ame_rules_list := ame_rules_list();
1007: ,errString out nocopy varchar2) as
1008: ruleItemClassIds ame_util.idList;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1013: ruleIds ame_util.idList;
1014: rulesList ame_rules_list := ame_rules_list();
1015: ruleObject ame_rule;
1008: ruleItemClassIds ame_util.idList;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1013: ruleIds ame_util.idList;
1014: rulesList ame_rules_list := ame_rules_list();
1015: ruleObject ame_rule;
1016: effectiveRuleDate date;
1009: itemClassIds ame_util.idList;
1010: itemIds ame_util.stringList;
1011: ruleTypes ame_util.idList;
1012: ruleDescriptions ame_util.stringList;
1013: ruleIds ame_util.idList;
1014: rulesList ame_rules_list := ame_rules_list();
1015: ruleObject ame_rule;
1016: effectiveRuleDate date;
1017: effecitveRuleDateString ame_util.stringType;
1013: ruleIds ame_util.idList;
1014: rulesList ame_rules_list := ame_rules_list();
1015: ruleObject ame_rule;
1016: effectiveRuleDate date;
1017: effecitveRuleDateString ame_util.stringType;
1018: itemClassName varchar2(100);
1019: begin
1020: errString := 'NO_ERROR';
1021: ame_engine.updateTransactionState(isTestTransactionIn => isRealTransaction = 'N'
1076: end loop;
1077: rulesOut := rulesList;
1078: exception
1079: When others then
1080: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
1081: routineNameIn => 'getApplicableRules',
1082: exceptionNumberIn => sqlcode,
1083: exceptionStringIn => sqlerrm);
1084: if sqlcode < -20001 then
1096: -- 'N' for a test transaction
1097: ,approverListStageIn in integer
1098: ,approversOut out nocopy ame_approvers_list
1099: ,errString out nocopy varchar2) as
1100: approverList ame_util.approversTable2;
1101: productionIndexes ame_util.idList;
1102: variableNames ame_util.stringList;
1103: variableValues ame_util.stringList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1097: ,approverListStageIn in integer
1098: ,approversOut out nocopy ame_approvers_list
1099: ,errString out nocopy varchar2) as
1100: approverList ame_util.approversTable2;
1101: productionIndexes ame_util.idList;
1102: variableNames ame_util.stringList;
1103: variableValues ame_util.stringList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1105: approver ame_approver;
1098: ,approversOut out nocopy ame_approvers_list
1099: ,errString out nocopy varchar2) as
1100: approverList ame_util.approversTable2;
1101: productionIndexes ame_util.idList;
1102: variableNames ame_util.stringList;
1103: variableValues ame_util.stringList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1105: approver ame_approver;
1106: effectiveRuleDate date;
1099: ,errString out nocopy varchar2) as
1100: approverList ame_util.approversTable2;
1101: productionIndexes ame_util.idList;
1102: variableNames ame_util.stringList;
1103: variableValues ame_util.stringList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1105: approver ame_approver;
1106: effectiveRuleDate date;
1107: tempActionTypeName ame_util.stringType;
1103: variableValues ame_util.stringList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1105: approver ame_approver;
1106: effectiveRuleDate date;
1107: tempActionTypeName ame_util.stringType;
1108: tempGroupName ame_util.stringType;
1109: approverSource varchar2(500);
1110: productionsList varchar2(4000);
1111: ruleIdList ame_util.idList;
1104: approverObjList ame_approvers_list := ame_approvers_list();
1105: approver ame_approver;
1106: effectiveRuleDate date;
1107: tempActionTypeName ame_util.stringType;
1108: tempGroupName ame_util.stringType;
1109: approverSource varchar2(500);
1110: productionsList varchar2(4000);
1111: ruleIdList ame_util.idList;
1112: l_app_count number;
1107: tempActionTypeName ame_util.stringType;
1108: tempGroupName ame_util.stringType;
1109: approverSource varchar2(500);
1110: productionsList varchar2(4000);
1111: ruleIdList ame_util.idList;
1112: l_app_count number;
1113: begin
1114: l_app_count :=0;
1115: errString:= 'NO_ERROR';
1125: effectiveRuleDate := ame_engine.getEffectiveRuleDate;
1126: --+
1127: for i in 1 .. approverList.count loop
1128: tempActionTypeName := getActionTypeName(approverList(i).action_type_id);
1129: if(tempActionTypeName in (ame_util.groupChainApprovalTypeName
1130: ,ame_util.preApprovalTypeName
1131: ,ame_util.postApprovalTypeName))then
1132: tempGroupName := getApproverGroupName(approverList(i).group_or_chain_id);
1133: else
1126: --+
1127: for i in 1 .. approverList.count loop
1128: tempActionTypeName := getActionTypeName(approverList(i).action_type_id);
1129: if(tempActionTypeName in (ame_util.groupChainApprovalTypeName
1130: ,ame_util.preApprovalTypeName
1131: ,ame_util.postApprovalTypeName))then
1132: tempGroupName := getApproverGroupName(approverList(i).group_or_chain_id);
1133: else
1134: tempGroupName := 'Chain '||approverList(i).group_or_chain_id;
1127: for i in 1 .. approverList.count loop
1128: tempActionTypeName := getActionTypeName(approverList(i).action_type_id);
1129: if(tempActionTypeName in (ame_util.groupChainApprovalTypeName
1130: ,ame_util.preApprovalTypeName
1131: ,ame_util.postApprovalTypeName))then
1132: tempGroupName := getApproverGroupName(approverList(i).group_or_chain_id);
1133: else
1134: tempGroupName := 'Chain '||approverList(i).group_or_chain_id;
1135: end if;
1132: tempGroupName := getApproverGroupName(approverList(i).group_or_chain_id);
1133: else
1134: tempGroupName := 'Chain '||approverList(i).group_or_chain_id;
1135: end if;
1136: ame_util.parseSourceValue(sourceValueIn => approverList(i).source
1137: ,sourceDescriptionOut => approverSource
1138: ,ruleIdListOut => ruleIdList);
1139: productionsList := '';
1140: for j in 1 .. productionIndexes.count
1148: variableValues(j);
1149: end if;
1150: end loop;
1151: if ((approverList(i).approval_status is null) or
1152: (approverList(i).approval_status not like '%'||ame_util.repeatedStatus) or
1153: (approverListStageIn < 6)) then
1154: if approverListStageIn = 1 and
1155: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1156: approverList(i).approval_status :=null;
1151: if ((approverList(i).approval_status is null) or
1152: (approverList(i).approval_status not like '%'||ame_util.repeatedStatus) or
1153: (approverListStageIn < 6)) then
1154: if approverListStageIn = 1 and
1155: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1156: approverList(i).approval_status :=null;
1157: end if;
1158: if approverListStageIn = 5 and
1159: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1155: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1156: approverList(i).approval_status :=null;
1157: end if;
1158: if approverListStageIn = 5 and
1159: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1160: approverList(i).approval_status :=ame_util.repeatedStatus;
1161: end if;
1162: approver := ame_approver(approverList(i).name
1163: ,approverList(i).orig_system
1156: approverList(i).approval_status :=null;
1157: end if;
1158: if approverListStageIn = 5 and
1159: approverList(i).approval_status like '%'||ame_util.repeatedStatus then
1160: approverList(i).approval_status :=ame_util.repeatedStatus;
1161: end if;
1162: approver := ame_approver(approverList(i).name
1163: ,approverList(i).orig_system
1164: ,approverList(i).orig_system_id
1196: end loop;
1197: approversOut := approverObjList;
1198: exception
1199: When others then
1200: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
1201: routineNameIn => 'getApprovers',
1202: exceptionNumberIn => sqlcode,
1203: exceptionStringIn => sqlerrm);
1204:
1218: -- 'N' for a test transaction
1219: ,processPriorities in varchar2
1220: ,productionsOut out nocopy ame_productions_list
1221: ,errString out nocopy varchar2) as
1222: variableNames ame_util.stringList;
1223: variableValues ame_util.stringList;
1224: productionNVPair ame_production_name_value_pair;
1225: productionsList ame_util2.productionsTable ;--:= ame_util2.productionsTable();
1226:
1219: ,processPriorities in varchar2
1220: ,productionsOut out nocopy ame_productions_list
1221: ,errString out nocopy varchar2) as
1222: variableNames ame_util.stringList;
1223: variableValues ame_util.stringList;
1224: productionNVPair ame_production_name_value_pair;
1225: productionsList ame_util2.productionsTable ;--:= ame_util2.productionsTable();
1226:
1227: begin
1221: ,errString out nocopy varchar2) as
1222: variableNames ame_util.stringList;
1223: variableValues ame_util.stringList;
1224: productionNVPair ame_production_name_value_pair;
1225: productionsList ame_util2.productionsTable ;--:= ame_util2.productionsTable();
1226:
1227: begin
1228: errString := 'NO_ERROR';
1229: ame_engine.updateTransactionState(isTestTransactionIn => isRealTransaction = 'N',
1258:
1259: end loop;
1260: exception
1261: When others then
1262: ame_util.runtimeException(packageNameIn => 'ame_test_utility_pkg',
1263: routineNameIn => 'getTransactionProductions',
1264: exceptionNumberIn => sqlcode,
1265: exceptionStringIn => sqlerrm);
1266: if sqlcode < -20001 then