[Home] [Help]
1: package body ame_api3 as
2: /* $Header: ameeapi3.pkb 120.20 2011/05/17 11:38:14 nchinnam ship $ */
3: ambiguousApproverException exception;
4: ambiguousApproverMessage constant ame_util.longestStringType :=
5: ame_util.getMessage(applicationShortNameIn =>'PER',
6: messageNameIn => 'AME_400812_NULL_APPR_REC_NAME');
7: /* functions */
8: function getRuleDescription(ruleIdIn in varchar2) return varchar2 as
1: package body ame_api3 as
2: /* $Header: ameeapi3.pkb 120.20 2011/05/17 11:38:14 nchinnam ship $ */
3: ambiguousApproverException exception;
4: ambiguousApproverMessage constant ame_util.longestStringType :=
5: ame_util.getMessage(applicationShortNameIn =>'PER',
6: messageNameIn => 'AME_400812_NULL_APPR_REC_NAME');
7: /* functions */
8: function getRuleDescription(ruleIdIn in varchar2) return varchar2 as
9: begin
9: begin
10: return(ame_rule_pkg.getDescription(ruleIdIn => ruleIdIn));
11: exception
12: when others then
13: ame_util.runtimeException(packageNameIn => 'ame_api3',
14: routineNameIn => 'getRuleDescription',
15: exceptionNumberIn => sqlcode,
16: exceptionStringIn => sqlerrm);
17: raise;
20: /* procedures */
21: procedure clearInsertion(applicationIdIn in number,
22: transactionTypeIn in varchar2,
23: transactionIdIn in varchar2,
24: approverIn in ame_util.approverRecord2) as
25: ameAppId integer;
26: errorCode integer;
27: errorMessage ame_util.longStringType;
28: appr_rec_params varchar2(100);
23: transactionIdIn in varchar2,
24: approverIn in ame_util.approverRecord2) as
25: ameAppId integer;
26: errorCode integer;
27: errorMessage ame_util.longStringType;
28: appr_rec_params varchar2(100);
29: cursor getAnchorInsertions (parameterIn varchar2) is
30: (select name
31: from ame_temp_insertions
31: from ame_temp_insertions
32: where application_id = ame_engine.getAmeApplicationId
33: and transaction_id = transactionIdIn
34: and parameter = parameterIn);
35: nameList ame_util.longStringList;
36: anchorsExistException exception;
37: cmpParameter ame_temp_insertions.parameter%type;
38: anchorName varchar2(320);
39: anchorList varchar2(1000);
36: anchorsExistException exception;
37: cmpParameter ame_temp_insertions.parameter%type;
38: anchorName varchar2(320);
39: anchorList varchar2(1000);
40: approvers ame_util.approversTable2;
41: nullApprRecordFieldException Exception;
42: begin
43: ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
44: transactionIdIn => transactionIdIn,
82: for i in 1 .. approvers.count loop
83: if approvers(i).name = approverIn.name and
84: approvers(i).item_class = approverIn.item_class and
85: approvers(i).item_id = approverIn.item_id and
86: approvers(i).api_insertion = ame_util.apiInsertion then
87: anchorName := approvers(i).name;
88: cmpParameter := approvers(i).name ||
89: ame_util.fieldDelimiter ||
90: approvers(i).item_class ||
85: approvers(i).item_id = approverIn.item_id and
86: approvers(i).api_insertion = ame_util.apiInsertion then
87: anchorName := approvers(i).name;
88: cmpParameter := approvers(i).name ||
89: ame_util.fieldDelimiter ||
90: approvers(i).item_class ||
91: ame_util.fieldDelimiter ||
92: approvers(i).item_id ||
93: ame_util.fieldDelimiter ||
87: anchorName := approvers(i).name;
88: cmpParameter := approvers(i).name ||
89: ame_util.fieldDelimiter ||
90: approvers(i).item_class ||
91: ame_util.fieldDelimiter ||
92: approvers(i).item_id ||
93: ame_util.fieldDelimiter ||
94: approvers(i).action_type_id ||
95: ame_util.fieldDelimiter ||
89: ame_util.fieldDelimiter ||
90: approvers(i).item_class ||
91: ame_util.fieldDelimiter ||
92: approvers(i).item_id ||
93: ame_util.fieldDelimiter ||
94: approvers(i).action_type_id ||
95: ame_util.fieldDelimiter ||
96: approvers(i).group_or_chain_id ||
97: ame_util.fieldDelimiter ||
91: ame_util.fieldDelimiter ||
92: approvers(i).item_id ||
93: ame_util.fieldDelimiter ||
94: approvers(i).action_type_id ||
95: ame_util.fieldDelimiter ||
96: approvers(i).group_or_chain_id ||
97: ame_util.fieldDelimiter ||
98: approvers(i).occurrence;
99: open getAnchorInsertions(cmpParameter);
93: ame_util.fieldDelimiter ||
94: approvers(i).action_type_id ||
95: ame_util.fieldDelimiter ||
96: approvers(i).group_or_chain_id ||
97: ame_util.fieldDelimiter ||
98: approvers(i).occurrence;
99: open getAnchorInsertions(cmpParameter);
100: fetch getAnchorInsertions bulk collect into nameList;
101: if nameList.count > 0 then
139: anchorList := anchorList || '; ';
140: end if;
141: end loop;
142: errorCode := -20001;
143: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
144: messageNameIn => 'AME_400651_ANCHORS_EXIST',
145: tokenNameOneIn => 'APPROVER',
146: tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
147: tokenNameTwoIn => 'ANCHORED_APPROVERS',
145: tokenNameOneIn => 'APPROVER',
146: tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
147: tokenNameTwoIn => 'ANCHORED_APPROVERS',
148: tokenValueTwoIn => anchorList);
149: ame_util.runtimeException(packageNameIn => 'ame_api3',
150: routineNameIn => 'clearInsertion',
151: exceptionNumberIn => errorCode,
152: exceptionStringIn => errorMessage);
153: raise_application_error(errorCode,
156: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
157: transactionIdIn => transactionIdIn,
158: transactionTypeIdIn => transactionTypeIn);
159: errorCode := -20319;
160: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
161: messageNameIn => 'AME_400813_NULL_CLINS_APPR_FLD',
162: tokenNameOneIn => 'APPROVER_REC_PARAMS',
163: tokenValueOneIn => appr_rec_params);
164: ame_util.runtimeException(packageNameIn => 'ame_api3',
160: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
161: messageNameIn => 'AME_400813_NULL_CLINS_APPR_FLD',
162: tokenNameOneIn => 'APPROVER_REC_PARAMS',
163: tokenValueOneIn => appr_rec_params);
164: ame_util.runtimeException(packageNameIn => 'ame_api3',
165: routineNameIn => 'clearInsertion',
166: exceptionNumberIn => errorCode,
167: exceptionStringIn => errorMessage);
168: raise_application_error(errorCode,
170: when others then
171: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
172: transactionIdIn => transactionIdIn,
173: transactionTypeIdIn => transactionTypeIn);
174: ame_util.runtimeException(packageNameIn => 'ame_api3',
175: routineNameIn => 'clearInsertion',
176: exceptionNumberIn => sqlcode,
177: exceptionStringIn => sqlerrm);
178: raise;
223: when others then
224: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
225: transactionIdIn => transactionIdIn,
226: transactionTypeIdIn => transactionTypeIn);
227: ame_util.runtimeException(packageNameIn => 'ame_api3',
228: routineNameIn => 'clearInsertions',
229: exceptionNumberIn => sqlcode,
230: exceptionStringIn => sqlerrm);
231: raise;
232: end clearInsertions;
233: procedure clearSuppression(applicationIdIn in number,
234: transactionTypeIn in varchar2,
235: transactionIdIn in varchar2,
236: approverIn in ame_util.approverRecord2) as
237: ameAppId integer;
238: errorCode integer;
239: errorMessage ame_util.longStringType;
240: appr_rec_params varchar2(100);
235: transactionIdIn in varchar2,
236: approverIn in ame_util.approverRecord2) as
237: ameAppId integer;
238: errorCode integer;
239: errorMessage ame_util.longStringType;
240: appr_rec_params varchar2(100);
241: nullApprRecordFieldException Exception;
242: begin
243: ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
309: when nullApprRecordFieldException then
310: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
311: transactionIdIn => transactionIdIn,
312: transactionTypeIdIn => transactionTypeIn);
313: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
314: messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
315: tokenNameOneIn => 'APPROVER_REC_PARAMS',
316: tokenvalueOneIn=>appr_rec_params);
317: errorCode := -20320;
314: messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
315: tokenNameOneIn => 'APPROVER_REC_PARAMS',
316: tokenvalueOneIn=>appr_rec_params);
317: errorCode := -20320;
318: ame_util.runtimeException(packageNameIn => 'ame_api3',
319: routineNameIn => 'clearSuppression',
320: exceptionNumberIn => errorCode,
321: exceptionStringIn => errorMessage);
322: raise_application_error(errorCode,
324: when others then
325: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
326: transactionIdIn => transactionIdIn,
327: transactionTypeIdIn => transactionTypeIn);
328: ame_util.runtimeException(packageNameIn => 'ame_api3',
329: routineNameIn => 'clearSuppression',
330: exceptionNumberIn => sqlcode,
331: exceptionStringIn => sqlerrm);
332: raise;
377: when others then
378: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
379: transactionIdIn => transactionIdIn,
380: transactionTypeIdIn => transactionTypeIn);
381: ame_util.runtimeException(packageNameIn => 'ame_api3',
382: routineNameIn => 'clearSuppressions',
383: exceptionNumberIn => sqlcode,
384: exceptionStringIn => sqlerrm);
385: raise;
383: exceptionNumberIn => sqlcode,
384: exceptionStringIn => sqlerrm);
385: raise;
386: end clearSuppressions;
387: procedure getAllApprovalGroups(groupIdsOut out nocopy ame_util.idList,
388: groupNamesOut out nocopy ame_util.stringList) as
389: cursor fetchGroupsCursor is
390: select approval_group_id, name
391: from ame_approval_groups
384: exceptionStringIn => sqlerrm);
385: raise;
386: end clearSuppressions;
387: procedure getAllApprovalGroups(groupIdsOut out nocopy ame_util.idList,
388: groupNamesOut out nocopy ame_util.stringList) as
389: cursor fetchGroupsCursor is
390: select approval_group_id, name
391: from ame_approval_groups
392: where end_date is null
399: groupNamesOut;
400: close fetchGroupsCursor;
401: exception
402: when others then
403: ame_util.runtimeException(packageNameIn => 'ame_api3',
404: routineNameIn => 'getAllApprovalGroups',
405: exceptionNumberIn => sqlcode,
406: exceptionStringIn => sqlerrm);
407: raise;
408: end getAllApprovalGroups;
409: procedure getApplicableRules1(applicationIdIn in integer,
410: transactionIdIn in varchar2,
411: transactionTypeIn in varchar2,
412: ruleIdsOut out nocopy ame_util.idList) as
413: ruleDescriptions ame_util.stringList;
414: productionFunctionality ame_util.stringType;
415: processProductionRules boolean;
416: begin
409: procedure getApplicableRules1(applicationIdIn in integer,
410: transactionIdIn in varchar2,
411: transactionTypeIn in varchar2,
412: ruleIdsOut out nocopy ame_util.idList) as
413: ruleDescriptions ame_util.stringList;
414: productionFunctionality ame_util.stringType;
415: processProductionRules boolean;
416: begin
417: productionFunctionality := ame_util.getConfigVar
410: transactionIdIn in varchar2,
411: transactionTypeIn in varchar2,
412: ruleIdsOut out nocopy ame_util.idList) as
413: ruleDescriptions ame_util.stringList;
414: productionFunctionality ame_util.stringType;
415: processProductionRules boolean;
416: begin
417: productionFunctionality := ame_util.getConfigVar
418: (variableNameIn => ame_util.productionConfigVar
413: ruleDescriptions ame_util.stringList;
414: productionFunctionality ame_util.stringType;
415: processProductionRules boolean;
416: begin
417: productionFunctionality := ame_util.getConfigVar
418: (variableNameIn => ame_util.productionConfigVar
419: ,applicationIdIn => ame_admin_pkg.getApplicationId
420: (fndAppIdIn => applicationIdIn
421: ,transactionTypeIdIn => transactionTypeIn
414: productionFunctionality ame_util.stringType;
415: processProductionRules boolean;
416: begin
417: productionFunctionality := ame_util.getConfigVar
418: (variableNameIn => ame_util.productionConfigVar
419: ,applicationIdIn => ame_admin_pkg.getApplicationId
420: (fndAppIdIn => applicationIdIn
421: ,transactionTypeIdIn => transactionTypeIn
422: )
420: (fndAppIdIn => applicationIdIn
421: ,transactionTypeIdIn => transactionTypeIn
422: )
423: );
424: if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
425: processProductionRules := false;
426: else
427: processProductionRules := true;
428: end if;
450: (ruleIdsOut => ruleIdsOut
451: ,ruleDescriptionsOut => ruleDescriptions);
452: exception
453: when others then
454: ame_util.runtimeException(packageNameIn => 'ame_api3',
455: routineNameIn => 'getApplicableRules1',
456: exceptionNumberIn => sqlcode,
457: exceptionStringIn => sqlerrm);
458: raise;
459: end getApplicableRules1;
460: procedure getApplicableRules2(applicationIdIn in integer,
461: transactionIdIn in varchar2,
462: transactionTypeIn in varchar2,
463: ruleDescriptionsOut out nocopy ame_util.stringList) as
464: ruleIds ame_util.idList;
465: productionFunctionality ame_util.stringType;
466: processProductionRules boolean;
467: begin
460: procedure getApplicableRules2(applicationIdIn in integer,
461: transactionIdIn in varchar2,
462: transactionTypeIn in varchar2,
463: ruleDescriptionsOut out nocopy ame_util.stringList) as
464: ruleIds ame_util.idList;
465: productionFunctionality ame_util.stringType;
466: processProductionRules boolean;
467: begin
468: productionFunctionality := ame_util.getConfigVar
461: transactionIdIn in varchar2,
462: transactionTypeIn in varchar2,
463: ruleDescriptionsOut out nocopy ame_util.stringList) as
464: ruleIds ame_util.idList;
465: productionFunctionality ame_util.stringType;
466: processProductionRules boolean;
467: begin
468: productionFunctionality := ame_util.getConfigVar
469: (variableNameIn => ame_util.productionConfigVar
464: ruleIds ame_util.idList;
465: productionFunctionality ame_util.stringType;
466: processProductionRules boolean;
467: begin
468: productionFunctionality := ame_util.getConfigVar
469: (variableNameIn => ame_util.productionConfigVar
470: ,applicationIdIn => ame_admin_pkg.getApplicationId
471: (fndAppIdIn => applicationIdIn
472: ,transactionTypeIdIn => transactionTypeIn
465: productionFunctionality ame_util.stringType;
466: processProductionRules boolean;
467: begin
468: productionFunctionality := ame_util.getConfigVar
469: (variableNameIn => ame_util.productionConfigVar
470: ,applicationIdIn => ame_admin_pkg.getApplicationId
471: (fndAppIdIn => applicationIdIn
472: ,transactionTypeIdIn => transactionTypeIn
473: )
471: (fndAppIdIn => applicationIdIn
472: ,transactionTypeIdIn => transactionTypeIn
473: )
474: );
475: if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
476: processProductionRules := false;
477: else
478: processProductionRules := true;
479: end if;
501: (ruleIdsOut => ruleIds
502: ,ruleDescriptionsOut => ruleDescriptionsOut);
503: exception
504: when others then
505: ame_util.runtimeException(packageNameIn => 'ame_api3',
506: routineNameIn => 'getApplicableRules2',
507: exceptionNumberIn => sqlcode,
508: exceptionStringIn => sqlerrm);
509: raise;
510: end getApplicableRules2;
511: procedure getApplicableRules3(applicationIdIn in integer,
512: transactionIdIn in varchar2,
513: transactionTypeIn in varchar2,
514: ruleIdsOut out nocopy ame_util.idList,
515: ruleDescriptionsOut out nocopy ame_util.stringList) as
516: productionFunctionality ame_util.stringType;
517: processProductionRules boolean;
518: begin
511: procedure getApplicableRules3(applicationIdIn in integer,
512: transactionIdIn in varchar2,
513: transactionTypeIn in varchar2,
514: ruleIdsOut out nocopy ame_util.idList,
515: ruleDescriptionsOut out nocopy ame_util.stringList) as
516: productionFunctionality ame_util.stringType;
517: processProductionRules boolean;
518: begin
519: productionFunctionality := ame_util.getConfigVar
512: transactionIdIn in varchar2,
513: transactionTypeIn in varchar2,
514: ruleIdsOut out nocopy ame_util.idList,
515: ruleDescriptionsOut out nocopy ame_util.stringList) as
516: productionFunctionality ame_util.stringType;
517: processProductionRules boolean;
518: begin
519: productionFunctionality := ame_util.getConfigVar
520: (variableNameIn => ame_util.productionConfigVar
515: ruleDescriptionsOut out nocopy ame_util.stringList) as
516: productionFunctionality ame_util.stringType;
517: processProductionRules boolean;
518: begin
519: productionFunctionality := ame_util.getConfigVar
520: (variableNameIn => ame_util.productionConfigVar
521: ,applicationIdIn => ame_admin_pkg.getApplicationId
522: (fndAppIdIn => applicationIdIn
523: ,transactionTypeIdIn => transactionTypeIn
516: productionFunctionality ame_util.stringType;
517: processProductionRules boolean;
518: begin
519: productionFunctionality := ame_util.getConfigVar
520: (variableNameIn => ame_util.productionConfigVar
521: ,applicationIdIn => ame_admin_pkg.getApplicationId
522: (fndAppIdIn => applicationIdIn
523: ,transactionTypeIdIn => transactionTypeIn
524: )
522: (fndAppIdIn => applicationIdIn
523: ,transactionTypeIdIn => transactionTypeIn
524: )
525: );
526: if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
527: processProductionRules := false;
528: else
529: processProductionRules := true;
530: end if;
552: (ruleIdsOut => ruleIdsOut
553: ,ruleDescriptionsOut => ruleDescriptionsOut);
554: exception
555: when others then
556: ame_util.runtimeException(packageNameIn => 'ame_api3',
557: routineNameIn => 'getApplicableRules3',
558: exceptionNumberIn => sqlcode,
559: exceptionStringIn => sqlerrm);
560: raise;
558: exceptionNumberIn => sqlcode,
559: exceptionStringIn => sqlerrm);
560: raise;
561: end getApplicableRules3;
562: procedure getApprovalGroupId(groupNameIn ame_util.stringType,
563: groupIdOut out nocopy number) as
564: errorCode integer;
565: errorMessage ame_util.longestStringType;
566: begin
561: end getApplicableRules3;
562: procedure getApprovalGroupId(groupNameIn ame_util.stringType,
563: groupIdOut out nocopy number) as
564: errorCode integer;
565: errorMessage ame_util.longestStringType;
566: begin
567: select approval_group_id
568: into groupIdOut
569: from ame_approval_groups
571: and end_date is null;
572: exception
573: when no_data_found then
574: errorCode := -20001;
575: errorMessage := ame_util.getMessage(
576: applicationShortNameIn => 'PER',
577: messageNameIn => 'AME_400453_GROUP_NOT_DEFINED',
578: tokenNameOneIn => 'APPROVAL_GROUP',
579: tokenValueOneIn => groupNameIn);
576: applicationShortNameIn => 'PER',
577: messageNameIn => 'AME_400453_GROUP_NOT_DEFINED',
578: tokenNameOneIn => 'APPROVAL_GROUP',
579: tokenValueOneIn => groupNameIn);
580: ame_util.runtimeException(packageNameIn => 'ame_api3',
581: routineNameIn => 'getApprovalGroupId',
582: exceptionNumberIn => errorCode,
583: exceptionStringIn => errorMessage);
584: raise_application_error(errorCode,
583: exceptionStringIn => errorMessage);
584: raise_application_error(errorCode,
585: errorMessage);
586: when others then
587: ame_util.runtimeException(packageNameIn => 'ame_api3',
588: routineNameIn => 'getApprovalGroupId',
589: exceptionNumberIn => sqlcode,
590: exceptionStringIn => sqlerrm);
591: raise;
597: itemIdIn in varchar2,
598: attributeValue1Out out nocopy varchar2,
599: attributeValue2Out out nocopy varchar2,
600: attributeValue3Out out nocopy varchar2) as
601: itemId ame_util.stringType;
602: begin
603: ame_engine.updateTransactionState(isTestTransactionIn => false,
604: isLocalTransactionIn => false,
605: fetchConfigVarsIn => false,
633: attributeValue2Out => attributeValue2Out,
634: attributeValue3Out => attributeValue3Out);
635: exception
636: when others then
637: ame_util.runtimeException(packageNameIn => 'ame_api3',
638: routineNameIn => 'getAttributeValue',
639: exceptionNumberIn => sqlcode,
640: exceptionStringIn => sqlerrm);
641: raise;
642: end getAttributeValue;
643: /*
644: getAvailableInsertions limits its output to insertions available for the order type orderTypeIn,
645: if this is null; otherwise getAvailableInsertions outputs insertions available for all order types.
646: Chain-of-authority insertees (those having authority = ame_util.authorityApprover and
647: api_insertion = ame_util.apiAuthorityInsertion) must have the approver_category value
648: ame_util.approvalApproverCategory. Ad-hoc insertees may be of either approver category.
649: */
650: procedure getAvailableInsertions(applicationIdIn in number,
643: /*
644: getAvailableInsertions limits its output to insertions available for the order type orderTypeIn,
645: if this is null; otherwise getAvailableInsertions outputs insertions available for all order types.
646: Chain-of-authority insertees (those having authority = ame_util.authorityApprover and
647: api_insertion = ame_util.apiAuthorityInsertion) must have the approver_category value
648: ame_util.approvalApproverCategory. Ad-hoc insertees may be of either approver category.
649: */
650: procedure getAvailableInsertions(applicationIdIn in number,
651: transactionTypeIn in varchar2,
644: getAvailableInsertions limits its output to insertions available for the order type orderTypeIn,
645: if this is null; otherwise getAvailableInsertions outputs insertions available for all order types.
646: Chain-of-authority insertees (those having authority = ame_util.authorityApprover and
647: api_insertion = ame_util.apiAuthorityInsertion) must have the approver_category value
648: ame_util.approvalApproverCategory. Ad-hoc insertees may be of either approver category.
649: */
650: procedure getAvailableInsertions(applicationIdIn in number,
651: transactionTypeIn in varchar2,
652: transactionIdIn in varchar2,
651: transactionTypeIn in varchar2,
652: transactionIdIn in varchar2,
653: positionIn in number,
654: orderTypeIn in varchar2 default null,
655: availableInsertionsOut out nocopy ame_util.insertionsTable2) as
656: begin
657: ame_engine.updateTransactionState(isTestTransactionIn => false,
658: isLocalTransactionIn => false,
659: fetchConfigVarsIn => true,
676: transactionTypeIdIn => transactionTypeIn );
677:
678: ame_engine.getInsertions
679: (positionIn => positionIn
680: ,coaInsertionsYNIn => ame_util.booleanTrue
681: ,orderTypeIn => orderTypeIn
682: ,availableInsertionsOut => availableInsertionsOut
683: );
684: exception
682: ,availableInsertionsOut => availableInsertionsOut
683: );
684: exception
685: when others then
686: ame_util.runtimeException
687: (packageNameIn => 'ame_api3'
688: ,routineNameIn => 'getAvailableInsertions'
689: ,exceptionNumberIn => sqlcode
690: ,exceptionStringIn => sqlerrm
699: upperLimitOut out nocopy varchar2,
700: includeLowerLimitOut out nocopy varchar2,
701: includeUpperLimitOut out nocopy varchar2,
702: currencyCodeOut out nocopy varchar2,
703: allowedValuesOut out nocopy ame_util.longestStringList) as
704: begin
705: select
706: ame_attributes.name,
707: ame_attributes.attribute_type,
727: ame_attributes.attribute_id = ame_conditions.attribute_id and
728: ame_conditions.condition_id = conditionIdIn and
729: sysdate between
730: ame_attributes.start_date and
731: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
732: sysdate between
733: ame_conditions.start_date and
734: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
735: if(attributeTypeOut = ame_util.stringAttributeType) then
730: ame_attributes.start_date and
731: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
732: sysdate between
733: ame_conditions.start_date and
734: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
735: if(attributeTypeOut = ame_util.stringAttributeType) then
736: ame_condition_pkg.getStringValueList(conditionIdIn => conditionIdIn,
737: stringValueListOut => allowedValuesOut);
738: end if;
731: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
732: sysdate between
733: ame_conditions.start_date and
734: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
735: if(attributeTypeOut = ame_util.stringAttributeType) then
736: ame_condition_pkg.getStringValueList(conditionIdIn => conditionIdIn,
737: stringValueListOut => allowedValuesOut);
738: end if;
739: exception
737: stringValueListOut => allowedValuesOut);
738: end if;
739: exception
740: when others then
741: ame_util.runtimeException(packageNameIn => 'ame_api3',
742: routineNameIn => 'getConditionDetails',
743: exceptionNumberIn => sqlcode,
744: exceptionStringIn => sqlerrm);
745: raise;
747: procedure getGroupMembers1(applicationIdIn in number default null,
748: transactionTypeIn in varchar2 default null,
749: transactionIdIn in varchar2 default null,
750: groupIdIn in number,
751: memberDisplayNamesOut out nocopy ame_util.longStringList)as
752: cursor groupMemberCursor(groupIdIn in integer) is
753: select
754: parameter,
755: upper(parameter_name),
764: noItemBindException exception;
765: dynamicCursor integer;
766: colonLocation1 integer;
767: colonLocation2 integer;
768: displayNames ame_util.longStringList;
769: errorCode integer;
770: errorMessage ame_util.longestStringType;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
766: colonLocation1 integer;
767: colonLocation2 integer;
768: displayNames ame_util.longStringList;
769: errorCode integer;
770: errorMessage ame_util.longestStringType;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
773: l_orig_systemList ame_util.stringList;
774: l_orig_system_id ame_util.idList;
768: displayNames ame_util.longStringList;
769: errorCode integer;
770: errorMessage ame_util.longestStringType;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
773: l_orig_systemList ame_util.stringList;
774: l_orig_system_id ame_util.idList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
769: errorCode integer;
770: errorMessage ame_util.longestStringType;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
773: l_orig_systemList ame_util.stringList;
774: l_orig_system_id ame_util.idList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
777: outputIndex integer;
770: errorMessage ame_util.longestStringType;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
773: l_orig_systemList ame_util.stringList;
774: l_orig_system_id ame_util.idList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
777: outputIndex integer;
778: parameters ame_util.longStringList;
771: noTransIdDefinedException exception;
772: orderNumbers ame_util.idList;
773: l_orig_systemList ame_util.stringList;
774: l_orig_system_id ame_util.idList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
777: outputIndex integer;
778: parameters ame_util.longStringList;
779: queryStrings ame_util.longestStringList;
774: l_orig_system_id ame_util.idList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
777: outputIndex integer;
778: parameters ame_util.longStringList;
779: queryStrings ame_util.longestStringList;
780: rowsFound integer;
781: tempGroupMembers dbms_sql.Varchar2_Table;
782: upperParameterNames ame_util.stringList;
775: memberOrigSystem ame_util.stringType;
776: memberOrigSystemId number;
777: outputIndex integer;
778: parameters ame_util.longStringList;
779: queryStrings ame_util.longestStringList;
780: rowsFound integer;
781: tempGroupMembers dbms_sql.Varchar2_Table;
782: upperParameterNames ame_util.stringList;
783: tempGroupName ame_util.stringType;
778: parameters ame_util.longStringList;
779: queryStrings ame_util.longestStringList;
780: rowsFound integer;
781: tempGroupMembers dbms_sql.Varchar2_Table;
782: upperParameterNames ame_util.stringList;
783: tempGroupName ame_util.stringType;
784: l_error_code number;
785: begin
786: open groupMemberCursor(groupIdIn => groupIdIn);
779: queryStrings ame_util.longestStringList;
780: rowsFound integer;
781: tempGroupMembers dbms_sql.Varchar2_Table;
782: upperParameterNames ame_util.stringList;
783: tempGroupName ame_util.stringType;
784: l_error_code number;
785: begin
786: open groupMemberCursor(groupIdIn => groupIdIn);
787: fetch groupMemberCursor bulk collect
793: l_orig_system_id;
794: close groupMemberCursor;
795: outputIndex := 0; /* pre-increment */
796: for i in 1 .. parameters.count loop
797: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
798: dynamicCursor := dbms_sql.open_cursor;
799: dbms_sql.parse(dynamicCursor,
800: ame_util.removeReturns(stringIn => queryStrings(i),
801: replaceWithSpaces => true),
796: for i in 1 .. parameters.count loop
797: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
798: dynamicCursor := dbms_sql.open_cursor;
799: dbms_sql.parse(dynamicCursor,
800: ame_util.removeReturns(stringIn => queryStrings(i),
801: replaceWithSpaces => true),
802: dbms_sql.native);
803: if(instrb(queryStrings(i),
804: ame_util.transactionIdPlaceholder) > 0) then
800: ame_util.removeReturns(stringIn => queryStrings(i),
801: replaceWithSpaces => true),
802: dbms_sql.native);
803: if(instrb(queryStrings(i),
804: ame_util.transactionIdPlaceholder) > 0) then
805: if transactionIdIn is null then
806: dbms_sql.close_cursor(dynamicCursor);
807: raise noTransIdDefinedException;
808: end if;
806: dbms_sql.close_cursor(dynamicCursor);
807: raise noTransIdDefinedException;
808: end if;
809: dbms_sql.bind_variable(dynamicCursor,
810: ame_util.transactionIdPlaceholder,
811: transactionIdIn,
812: 50);
813: end if;
814: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
810: ame_util.transactionIdPlaceholder,
811: transactionIdIn,
812: 50);
813: end if;
814: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
815: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
816: dbms_sql.close_cursor(dynamicCursor);
817: raise noItemBindException;
818: end if;
811: transactionIdIn,
812: 50);
813: end if;
814: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
815: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
816: dbms_sql.close_cursor(dynamicCursor);
817: raise noItemBindException;
818: end if;
819: dbms_sql.define_array(dynamicCursor,
845: if(colonLocation2 = 0) then /* first case (old style) */
846: memberOrigSystemId :=
847: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
848: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
849: upper(ame_util.approverPersonId)) then
850: memberOrigSystem := ame_util.perOrigSystem;
851: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
852: upper(ame_util.approverUserId)) then
853: memberOrigSystem := ame_util.fndUserOrigSystem;
846: memberOrigSystemId :=
847: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
848: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
849: upper(ame_util.approverPersonId)) then
850: memberOrigSystem := ame_util.perOrigSystem;
851: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
852: upper(ame_util.approverUserId)) then
853: memberOrigSystem := ame_util.fndUserOrigSystem;
854: else
848: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
849: upper(ame_util.approverPersonId)) then
850: memberOrigSystem := ame_util.perOrigSystem;
851: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
852: upper(ame_util.approverUserId)) then
853: memberOrigSystem := ame_util.fndUserOrigSystem;
854: else
855: memberOrigSystem :=
856: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
849: upper(ame_util.approverPersonId)) then
850: memberOrigSystem := ame_util.perOrigSystem;
851: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
852: upper(ame_util.approverUserId)) then
853: memberOrigSystem := ame_util.fndUserOrigSystem;
854: else
855: memberOrigSystem :=
856: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
857: memberOrigSystemId :=
873: when others then
874: l_error_code := sqlcode;
875: if l_error_code = -20213 then
876: errorCode := -20223;
877: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
878: messageNameIn => 'AME_400838_INV_APR_FOUND',
879: tokenNameOneIn => 'PROCESS_NAME',
880: tokenValueOneIn => 'ame_api3.getGroupMembers1',
881: tokenNameTwoIn => 'ORIG_SYSTEM',
895: when others then
896: l_error_code := sqlcode;
897: if l_error_code = -20213 then
898: errorCode := -20223;
899: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
900: messageNameIn => 'AME_400838_INV_APR_FOUND',
901: tokenNameOneIn => 'PROCESS_NAME',
902: tokenValueOneIn => 'ame_api3.getGroupMembers1',
903: tokenNameTwoIn => 'ORIG_SYSTEM',
917: close groupMemberCursor;
918: end if;
919: memberDisplayNamesOut.delete;
920: errorCode := -20315;
921: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
922: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
923: ame_util.runtimeException(packageNameIn => 'ame_api3',
924: routineNameIn => 'getGroupMembers1',
925: exceptionNumberIn => errorCode,
919: memberDisplayNamesOut.delete;
920: errorCode := -20315;
921: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
922: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
923: ame_util.runtimeException(packageNameIn => 'ame_api3',
924: routineNameIn => 'getGroupMembers1',
925: exceptionNumberIn => errorCode,
926: exceptionStringIn => errorMessage);
927: raise_application_error(errorCode,
933: memberDisplayNamesOut.delete;
934: errorCode := -20316;
935: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
936: ,groupNameOut => tempGroupName);
937: errorMessage := ame_util.getMessage(
938: applicationShortNameIn => 'PER',
939: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
940: tokenNameOneIn => 'APPROVER_GROUP',
941: tokenValueOneIn => tempGroupName);
938: applicationShortNameIn => 'PER',
939: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
940: tokenNameOneIn => 'APPROVER_GROUP',
941: tokenValueOneIn => tempGroupName);
942: ame_util.runtimeException(packageNameIn => 'ame_api3',
943: routineNameIn => 'getGroupMembers1',
944: exceptionNumberIn => errorCode,
945: exceptionStringIn => errorMessage);
946: raise_application_error(errorCode,
952: memberDisplayNamesOut.delete;
953: errorCode := -20001;
954: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
955: ,groupNameOut => tempGroupName);
956: errorMessage := ame_util.getMessage(
957: applicationShortNameIn => 'PER',
958: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
959: tokenNameOneIn => 'APPROVAL_GROUP',
960: tokenValueOneIn => tempGroupName);
957: applicationShortNameIn => 'PER',
958: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
959: tokenNameOneIn => 'APPROVAL_GROUP',
960: tokenValueOneIn => tempGroupName);
961: ame_util.runtimeException(packageNameIn => 'ame_api3',
962: routineNameIn => 'getGroupMembers1',
963: exceptionNumberIn => errorCode,
964: exceptionStringIn => errorMessage);
965: raise_application_error(errorCode,
968: if(groupMemberCursor%isopen) then
969: close groupMemberCursor;
970: end if;
971: memberDisplayNamesOut.delete;
972: ame_util.runtimeException(packageNameIn => 'ame_api3',
973: routineNameIn => 'getGroupMembers1',
974: exceptionNumberIn => sqlcode,
975: exceptionStringIn => sqlerrm);
976: raise;
978: procedure getGroupMembers2(applicationIdIn in number default null,
979: transactionTypeIn in varchar2 default null,
980: transactionIdIn in varchar2 default null,
981: groupIdIn in number,
982: memberNamesOut out nocopy ame_util.longStringList,
983: memberDisplayNamesOut out nocopy ame_util.longStringList)as
984: cursor groupMemberCursor(groupIdIn in integer) is
985: select
986: parameter,
979: transactionTypeIn in varchar2 default null,
980: transactionIdIn in varchar2 default null,
981: groupIdIn in number,
982: memberNamesOut out nocopy ame_util.longStringList,
983: memberDisplayNamesOut out nocopy ame_util.longStringList)as
984: cursor groupMemberCursor(groupIdIn in integer) is
985: select
986: parameter,
987: upper(parameter_name),
996: noItemBindException exception;
997: dynamicCursor integer;
998: colonLocation1 integer;
999: colonLocation2 integer;
1000: displayNames ame_util.longStringList;
1001: errorCode integer;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
998: colonLocation1 integer;
999: colonLocation2 integer;
1000: displayNames ame_util.longStringList;
1001: errorCode integer;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
999: colonLocation2 integer;
1000: displayNames ame_util.longStringList;
1001: errorCode integer;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1000: displayNames ame_util.longStringList;
1001: errorCode integer;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1001: errorCode integer;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1009: orderNumbers ame_util.idList;
1002: errorMessage ame_util.longestStringType;
1003: approverNames ame_util.longStringList;
1004: orig_systemList ame_util.stringList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1009: orderNumbers ame_util.idList;
1010: origSystemIds ame_util.idList;
1005: orig_system_idList ame_util.idList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1009: orderNumbers ame_util.idList;
1010: origSystemIds ame_util.idList;
1011: origSystems ame_util.stringList;
1012: outputIndex integer;
1013: parameters ame_util.longStringList;
1006: memberOrigSystem ame_util.stringType;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1009: orderNumbers ame_util.idList;
1010: origSystemIds ame_util.idList;
1011: origSystems ame_util.stringList;
1012: outputIndex integer;
1013: parameters ame_util.longStringList;
1014: queryStrings ame_util.longestStringList;
1007: memberOrigSystemId number;
1008: noTransIdDefinedException exception;
1009: orderNumbers ame_util.idList;
1010: origSystemIds ame_util.idList;
1011: origSystems ame_util.stringList;
1012: outputIndex integer;
1013: parameters ame_util.longStringList;
1014: queryStrings ame_util.longestStringList;
1015: rowsFound integer;
1009: orderNumbers ame_util.idList;
1010: origSystemIds ame_util.idList;
1011: origSystems ame_util.stringList;
1012: outputIndex integer;
1013: parameters ame_util.longStringList;
1014: queryStrings ame_util.longestStringList;
1015: rowsFound integer;
1016: tempGroupMembers dbms_sql.Varchar2_Table;
1017: upperParameterNames ame_util.stringList;
1010: origSystemIds ame_util.idList;
1011: origSystems ame_util.stringList;
1012: outputIndex integer;
1013: parameters ame_util.longStringList;
1014: queryStrings ame_util.longestStringList;
1015: rowsFound integer;
1016: tempGroupMembers dbms_sql.Varchar2_Table;
1017: upperParameterNames ame_util.stringList;
1018: tempGroupName ame_util.stringType;
1013: parameters ame_util.longStringList;
1014: queryStrings ame_util.longestStringList;
1015: rowsFound integer;
1016: tempGroupMembers dbms_sql.Varchar2_Table;
1017: upperParameterNames ame_util.stringList;
1018: tempGroupName ame_util.stringType;
1019: l_error_code number;
1020: begin
1021: open groupMemberCursor(groupIdIn => groupIdIn);
1014: queryStrings ame_util.longestStringList;
1015: rowsFound integer;
1016: tempGroupMembers dbms_sql.Varchar2_Table;
1017: upperParameterNames ame_util.stringList;
1018: tempGroupName ame_util.stringType;
1019: l_error_code number;
1020: begin
1021: open groupMemberCursor(groupIdIn => groupIdIn);
1022: fetch groupMemberCursor bulk collect
1028: orig_system_idList;
1029: close groupMemberCursor;
1030: outputIndex := 0; /* pre-increment */
1031: for i in 1 .. parameters.count loop
1032: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1033: dynamicCursor := dbms_sql.open_cursor;
1034: dbms_sql.parse(dynamicCursor,
1035: ame_util.removeReturns(stringIn => queryStrings(i),
1036: replaceWithSpaces => true),
1031: for i in 1 .. parameters.count loop
1032: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1033: dynamicCursor := dbms_sql.open_cursor;
1034: dbms_sql.parse(dynamicCursor,
1035: ame_util.removeReturns(stringIn => queryStrings(i),
1036: replaceWithSpaces => true),
1037: dbms_sql.native);
1038: if(instrb(queryStrings(i),
1039: ame_util.transactionIdPlaceholder) > 0) then
1035: ame_util.removeReturns(stringIn => queryStrings(i),
1036: replaceWithSpaces => true),
1037: dbms_sql.native);
1038: if(instrb(queryStrings(i),
1039: ame_util.transactionIdPlaceholder) > 0) then
1040: if transactionIdIn is null then
1041: dbms_sql.close_cursor(dynamicCursor);
1042: raise noTransIdDefinedException;
1043: end if;
1041: dbms_sql.close_cursor(dynamicCursor);
1042: raise noTransIdDefinedException;
1043: end if;
1044: dbms_sql.bind_variable(dynamicCursor,
1045: ame_util.transactionIdPlaceholder,
1046: transactionIdIn,
1047: 50);
1048: end if;
1049: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1045: ame_util.transactionIdPlaceholder,
1046: transactionIdIn,
1047: 50);
1048: end if;
1049: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1050: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1051: dbms_sql.close_cursor(dynamicCursor);
1052: raise noItemBindException;
1053: end if;
1046: transactionIdIn,
1047: 50);
1048: end if;
1049: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1050: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1051: dbms_sql.close_cursor(dynamicCursor);
1052: raise noItemBindException;
1053: end if;
1054: dbms_sql.define_array(dynamicCursor,
1080: if(colonLocation2 = 0) then /* first case (old style) */
1081: memberOrigSystemId :=
1082: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1083: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1084: upper(ame_util.approverPersonId)) then
1085: memberOrigSystem := ame_util.perOrigSystem;
1086: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1087: upper(ame_util.approverUserId)) then
1088: memberOrigSystem := ame_util.fndUserOrigSystem;
1081: memberOrigSystemId :=
1082: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1083: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1084: upper(ame_util.approverPersonId)) then
1085: memberOrigSystem := ame_util.perOrigSystem;
1086: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1087: upper(ame_util.approverUserId)) then
1088: memberOrigSystem := ame_util.fndUserOrigSystem;
1089: else
1083: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1084: upper(ame_util.approverPersonId)) then
1085: memberOrigSystem := ame_util.perOrigSystem;
1086: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1087: upper(ame_util.approverUserId)) then
1088: memberOrigSystem := ame_util.fndUserOrigSystem;
1089: else
1090: memberOrigSystem :=
1091: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1084: upper(ame_util.approverPersonId)) then
1085: memberOrigSystem := ame_util.perOrigSystem;
1086: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1087: upper(ame_util.approverUserId)) then
1088: memberOrigSystem := ame_util.fndUserOrigSystem;
1089: else
1090: memberOrigSystem :=
1091: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1092: memberOrigSystemId :=
1110: when others then
1111: l_error_code := sqlcode;
1112: if l_error_code = -20213 then
1113: errorCode := -20223;
1114: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1115: messageNameIn => 'AME_400838_INV_APR_FOUND',
1116: tokenNameOneIn => 'PROCESS_NAME',
1117: tokenValueOneIn => 'ame_api3.getGroupMembers2',
1118: tokenNameTwoIn => 'ORIG_SYSTEM',
1136: when others then
1137: l_error_code := sqlcode;
1138: if l_error_code = -20213 then
1139: errorCode := -20223;
1140: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1141: messageNameIn => 'AME_400838_INV_APR_FOUND',
1142: tokenNameOneIn => 'PROCESS_NAME',
1143: tokenValueOneIn => 'ame_api3.getGroupMembers2',
1144: tokenNameTwoIn => 'ORIG_SYSTEM',
1156: if(groupMemberCursor%isopen) then
1157: close groupMemberCursor;
1158: end if;
1159: errorCode := -20315;
1160: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1161: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1162: ame_util.runtimeException(packageNameIn => 'ame_api3',
1163: routineNameIn => 'getGroupMembers2',
1164: exceptionNumberIn => errorCode,
1158: end if;
1159: errorCode := -20315;
1160: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1161: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1162: ame_util.runtimeException(packageNameIn => 'ame_api3',
1163: routineNameIn => 'getGroupMembers2',
1164: exceptionNumberIn => errorCode,
1165: exceptionStringIn => errorMessage);
1166: raise_application_error(errorCode,
1171: end if;
1172: errorCode := -20001;
1173: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1174: ,groupNameOut => tempGroupName);
1175: errorMessage := ame_util.getMessage(
1176: applicationShortNameIn => 'PER',
1177: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1178: tokenNameOneIn => 'APPROVAL_GROUP',
1179: tokenValueOneIn => tempGroupName);
1176: applicationShortNameIn => 'PER',
1177: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1178: tokenNameOneIn => 'APPROVAL_GROUP',
1179: tokenValueOneIn => tempGroupName);
1180: ame_util.runtimeException(packageNameIn => 'ame_api3',
1181: routineNameIn => 'getGroupMembers2',
1182: exceptionNumberIn => errorCode,
1183: exceptionStringIn => errorMessage);
1184: raise_application_error(errorCode,
1190: memberDisplayNamesOut.delete;
1191: errorCode := -20316;
1192: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1193: ,groupNameOut => tempGroupName);
1194: errorMessage := ame_util.getMessage(
1195: applicationShortNameIn => 'PER',
1196: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1197: tokenNameOneIn => 'APPROVER_GROUP',
1198: tokenValueOneIn => tempGroupName);
1195: applicationShortNameIn => 'PER',
1196: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1197: tokenNameOneIn => 'APPROVER_GROUP',
1198: tokenValueOneIn => tempGroupName);
1199: ame_util.runtimeException(packageNameIn => 'ame_api3',
1200: routineNameIn => 'getGroupMembers2',
1201: exceptionNumberIn => errorCode,
1202: exceptionStringIn => errorMessage);
1203: raise_application_error(errorCode,
1205: when others then
1206: if(groupMemberCursor%isopen) then
1207: close groupMemberCursor;
1208: end if;
1209: ame_util.runtimeException(packageNameIn => 'ame_api3',
1210: routineNameIn => 'getGroupMembers2',
1211: exceptionNumberIn => sqlcode,
1212: exceptionStringIn => sqlerrm);
1213: raise;
1215: procedure getGroupMembers3(applicationIdIn in number default null,
1216: transactionTypeIn in varchar2 default null,
1217: transactionIdIn in varchar2 default null,
1218: groupIdIn in number,
1219: memberNamesOut out nocopy ame_util.longStringList,
1220: memberOrderNumbersOut out nocopy ame_util.idList,
1221: memberDisplayNamesOut out nocopy ame_util.longStringList)as
1222: cursor groupMemberCursor(groupIdIn in integer) is
1223: select
1216: transactionTypeIn in varchar2 default null,
1217: transactionIdIn in varchar2 default null,
1218: groupIdIn in number,
1219: memberNamesOut out nocopy ame_util.longStringList,
1220: memberOrderNumbersOut out nocopy ame_util.idList,
1221: memberDisplayNamesOut out nocopy ame_util.longStringList)as
1222: cursor groupMemberCursor(groupIdIn in integer) is
1223: select
1224: parameter,
1217: transactionIdIn in varchar2 default null,
1218: groupIdIn in number,
1219: memberNamesOut out nocopy ame_util.longStringList,
1220: memberOrderNumbersOut out nocopy ame_util.idList,
1221: memberDisplayNamesOut out nocopy ame_util.longStringList)as
1222: cursor groupMemberCursor(groupIdIn in integer) is
1223: select
1224: parameter,
1225: upper(parameter_name),
1235: noItemBindException exception;
1236: dynamicCursor integer;
1237: colonLocation1 integer;
1238: colonLocation2 integer;
1239: displayNames ame_util.longStringList;
1240: errorCode integer;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1237: colonLocation1 integer;
1238: colonLocation2 integer;
1239: displayNames ame_util.longStringList;
1240: errorCode integer;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1238: colonLocation2 integer;
1239: displayNames ame_util.longStringList;
1240: errorCode integer;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1239: displayNames ame_util.longStringList;
1240: errorCode integer;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1240: errorCode integer;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1248: orderNumbers ame_util.idList;
1241: errorMessage ame_util.longestStringType;
1242: approverNames ame_util.longStringList;
1243: orig_systemList ame_util.stringList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1248: orderNumbers ame_util.idList;
1249: origSystemIds ame_util.idList;
1244: orig_systemIdList ame_util.idList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1248: orderNumbers ame_util.idList;
1249: origSystemIds ame_util.idList;
1250: origSystems ame_util.stringList;
1251: outputIndex integer;
1252: parameters ame_util.longStringList;
1245: memberOrigSystem ame_util.stringType;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1248: orderNumbers ame_util.idList;
1249: origSystemIds ame_util.idList;
1250: origSystems ame_util.stringList;
1251: outputIndex integer;
1252: parameters ame_util.longStringList;
1253: queryStrings ame_util.longestStringList;
1246: memberOrigSystemId number;
1247: noTransIdDefinedException exception;
1248: orderNumbers ame_util.idList;
1249: origSystemIds ame_util.idList;
1250: origSystems ame_util.stringList;
1251: outputIndex integer;
1252: parameters ame_util.longStringList;
1253: queryStrings ame_util.longestStringList;
1254: rowsFound integer;
1248: orderNumbers ame_util.idList;
1249: origSystemIds ame_util.idList;
1250: origSystems ame_util.stringList;
1251: outputIndex integer;
1252: parameters ame_util.longStringList;
1253: queryStrings ame_util.longestStringList;
1254: rowsFound integer;
1255: tempGroupMembers dbms_sql.Varchar2_Table;
1256: upperParameterNames ame_util.stringList;
1249: origSystemIds ame_util.idList;
1250: origSystems ame_util.stringList;
1251: outputIndex integer;
1252: parameters ame_util.longStringList;
1253: queryStrings ame_util.longestStringList;
1254: rowsFound integer;
1255: tempGroupMembers dbms_sql.Varchar2_Table;
1256: upperParameterNames ame_util.stringList;
1257: tempGroupName ame_util.stringType;
1252: parameters ame_util.longStringList;
1253: queryStrings ame_util.longestStringList;
1254: rowsFound integer;
1255: tempGroupMembers dbms_sql.Varchar2_Table;
1256: upperParameterNames ame_util.stringList;
1257: tempGroupName ame_util.stringType;
1258: l_error_code number;
1259: begin
1260: open groupMemberCursor(groupIdIn => groupIdIn);
1253: queryStrings ame_util.longestStringList;
1254: rowsFound integer;
1255: tempGroupMembers dbms_sql.Varchar2_Table;
1256: upperParameterNames ame_util.stringList;
1257: tempGroupName ame_util.stringType;
1258: l_error_code number;
1259: begin
1260: open groupMemberCursor(groupIdIn => groupIdIn);
1261: fetch groupMemberCursor bulk collect
1268: orig_systemIdList;
1269: close groupMemberCursor;
1270: outputIndex := 0; /* pre-increment */
1271: for i in 1 .. parameters.count loop
1272: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1273: dynamicCursor := dbms_sql.open_cursor;
1274: dbms_sql.parse(dynamicCursor,
1275: ame_util.removeReturns(stringIn => queryStrings(i),
1276: replaceWithSpaces => true),
1271: for i in 1 .. parameters.count loop
1272: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1273: dynamicCursor := dbms_sql.open_cursor;
1274: dbms_sql.parse(dynamicCursor,
1275: ame_util.removeReturns(stringIn => queryStrings(i),
1276: replaceWithSpaces => true),
1277: dbms_sql.native);
1278: if(instrb(queryStrings(i),
1279: ame_util.transactionIdPlaceholder) > 0) then
1275: ame_util.removeReturns(stringIn => queryStrings(i),
1276: replaceWithSpaces => true),
1277: dbms_sql.native);
1278: if(instrb(queryStrings(i),
1279: ame_util.transactionIdPlaceholder) > 0) then
1280: if transactionIdIn is null then
1281: dbms_sql.close_cursor(dynamicCursor);
1282: raise noTransIdDefinedException;
1283: end if;
1281: dbms_sql.close_cursor(dynamicCursor);
1282: raise noTransIdDefinedException;
1283: end if;
1284: dbms_sql.bind_variable(dynamicCursor,
1285: ame_util.transactionIdPlaceholder,
1286: transactionIdIn,
1287: 50);
1288: end if;
1289: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1285: ame_util.transactionIdPlaceholder,
1286: transactionIdIn,
1287: 50);
1288: end if;
1289: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1290: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1291: dbms_sql.close_cursor(dynamicCursor);
1292: raise noItemBindException;
1293: end if;
1286: transactionIdIn,
1287: 50);
1288: end if;
1289: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1290: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1291: dbms_sql.close_cursor(dynamicCursor);
1292: raise noItemBindException;
1293: end if;
1294: dbms_sql.define_array(dynamicCursor,
1321: if(colonLocation2 = 0) then /* first case (old style) */
1322: memberOrigSystemId :=
1323: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1324: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1325: upper(ame_util.approverPersonId)) then
1326: memberOrigSystem := ame_util.perOrigSystem;
1327: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1328: upper(ame_util.approverUserId)) then
1329: memberOrigSystem := ame_util.fndUserOrigSystem;
1322: memberOrigSystemId :=
1323: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1324: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1325: upper(ame_util.approverPersonId)) then
1326: memberOrigSystem := ame_util.perOrigSystem;
1327: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1328: upper(ame_util.approverUserId)) then
1329: memberOrigSystem := ame_util.fndUserOrigSystem;
1330: else
1324: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1325: upper(ame_util.approverPersonId)) then
1326: memberOrigSystem := ame_util.perOrigSystem;
1327: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1328: upper(ame_util.approverUserId)) then
1329: memberOrigSystem := ame_util.fndUserOrigSystem;
1330: else
1331: memberOrigSystem :=
1332: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1325: upper(ame_util.approverPersonId)) then
1326: memberOrigSystem := ame_util.perOrigSystem;
1327: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1328: upper(ame_util.approverUserId)) then
1329: memberOrigSystem := ame_util.fndUserOrigSystem;
1330: else
1331: memberOrigSystem :=
1332: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1333: memberOrigSystemId :=
1350: when others then
1351: l_error_code := sqlcode;
1352: if l_error_code = -20213 then
1353: errorCode := -20223;
1354: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1355: messageNameIn => 'AME_400838_INV_APR_FOUND',
1356: tokenNameOneIn => 'PROCESS_NAME',
1357: tokenValueOneIn => 'ame_api3.getGroupMembers3',
1358: tokenNameTwoIn => 'ORIG_SYSTEM',
1377: when others then
1378: l_error_code := sqlcode;
1379: if l_error_code = -20213 then
1380: errorCode := -20223;
1381: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1382: messageNameIn => 'AME_400838_INV_APR_FOUND',
1383: tokenNameOneIn => 'PROCESS_NAME',
1384: tokenValueOneIn => 'ame_api3.getGroupMembers3',
1385: tokenNameTwoIn => 'ORIG_SYSTEM',
1397: if(groupMemberCursor%isopen) then
1398: close groupMemberCursor;
1399: end if;
1400: errorCode := -20315;
1401: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1402: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1403: ame_util.runtimeException(packageNameIn => 'ame_api3',
1404: routineNameIn => 'getGroupMembers3',
1405: exceptionNumberIn => errorCode,
1399: end if;
1400: errorCode := -20315;
1401: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1402: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1403: ame_util.runtimeException(packageNameIn => 'ame_api3',
1404: routineNameIn => 'getGroupMembers3',
1405: exceptionNumberIn => errorCode,
1406: exceptionStringIn => errorMessage);
1407: raise_application_error(errorCode,
1412: end if;
1413: errorCode := -20001;
1414: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1415: ,groupNameOut => tempGroupName);
1416: errorMessage := ame_util.getMessage(
1417: applicationShortNameIn => 'PER',
1418: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1419: tokenNameOneIn => 'APPROVAL_GROUP',
1420: tokenValueOneIn => tempGroupName);
1417: applicationShortNameIn => 'PER',
1418: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1419: tokenNameOneIn => 'APPROVAL_GROUP',
1420: tokenValueOneIn => tempGroupName);
1421: ame_util.runtimeException(packageNameIn => 'ame_api3',
1422: routineNameIn => 'getGroupMembers3',
1423: exceptionNumberIn => errorCode,
1424: exceptionStringIn => errorMessage);
1425: raise_application_error(errorCode,
1431: memberDisplayNamesOut.delete;
1432: errorCode := -20316;
1433: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1434: ,groupNameOut => tempGroupName);
1435: errorMessage := ame_util.getMessage(
1436: applicationShortNameIn => 'PER',
1437: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1438: tokenNameOneIn => 'APPROVER_GROUP',
1439: tokenValueOneIn => tempGroupName);
1436: applicationShortNameIn => 'PER',
1437: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1438: tokenNameOneIn => 'APPROVER_GROUP',
1439: tokenValueOneIn => tempGroupName);
1440: ame_util.runtimeException(packageNameIn => 'ame_api3',
1441: routineNameIn => 'getGroupMembers3',
1442: exceptionNumberIn => errorCode,
1443: exceptionStringIn => errorMessage);
1444: raise_application_error(errorCode,
1446: when others then
1447: if(groupMemberCursor%isopen) then
1448: close groupMemberCursor;
1449: end if;
1450: ame_util.runtimeException(packageNameIn => 'ame_api3',
1451: routineNameIn => 'getGroupMembers3',
1452: exceptionNumberIn => sqlcode,
1453: exceptionStringIn => sqlerrm);
1454: raise;
1456: procedure getGroupMembers4(applicationIdIn in number default null,
1457: transactionTypeIn in varchar2 default null,
1458: transactionIdIn in varchar2 default null,
1459: groupIdIn in number,
1460: memberNamesOut out nocopy ame_util.longStringList,
1461: memberOrderNumbersOut out nocopy ame_util.idList,
1462: memberDisplayNamesOut out nocopy ame_util.longStringList,
1463: memberOrigSystemIdsOut out nocopy ame_util.idList,
1464: memberOrigSystemsOut out nocopy ame_util.stringList)as
1457: transactionTypeIn in varchar2 default null,
1458: transactionIdIn in varchar2 default null,
1459: groupIdIn in number,
1460: memberNamesOut out nocopy ame_util.longStringList,
1461: memberOrderNumbersOut out nocopy ame_util.idList,
1462: memberDisplayNamesOut out nocopy ame_util.longStringList,
1463: memberOrigSystemIdsOut out nocopy ame_util.idList,
1464: memberOrigSystemsOut out nocopy ame_util.stringList)as
1465: cursor groupMemberCursor(groupIdIn in integer) is
1458: transactionIdIn in varchar2 default null,
1459: groupIdIn in number,
1460: memberNamesOut out nocopy ame_util.longStringList,
1461: memberOrderNumbersOut out nocopy ame_util.idList,
1462: memberDisplayNamesOut out nocopy ame_util.longStringList,
1463: memberOrigSystemIdsOut out nocopy ame_util.idList,
1464: memberOrigSystemsOut out nocopy ame_util.stringList)as
1465: cursor groupMemberCursor(groupIdIn in integer) is
1466: select
1459: groupIdIn in number,
1460: memberNamesOut out nocopy ame_util.longStringList,
1461: memberOrderNumbersOut out nocopy ame_util.idList,
1462: memberDisplayNamesOut out nocopy ame_util.longStringList,
1463: memberOrigSystemIdsOut out nocopy ame_util.idList,
1464: memberOrigSystemsOut out nocopy ame_util.stringList)as
1465: cursor groupMemberCursor(groupIdIn in integer) is
1466: select
1467: orig_system,
1460: memberNamesOut out nocopy ame_util.longStringList,
1461: memberOrderNumbersOut out nocopy ame_util.idList,
1462: memberDisplayNamesOut out nocopy ame_util.longStringList,
1463: memberOrigSystemIdsOut out nocopy ame_util.idList,
1464: memberOrigSystemsOut out nocopy ame_util.stringList)as
1465: cursor groupMemberCursor(groupIdIn in integer) is
1466: select
1467: orig_system,
1468: orig_system_id,
1478: noItemBindException exception;
1479: dynamicCursor integer;
1480: colonLocation1 integer;
1481: colonLocation2 integer;
1482: displayNames ame_util.longStringList;
1483: errorCode integer;
1484: errorMessage ame_util.longestStringType;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1480: colonLocation1 integer;
1481: colonLocation2 integer;
1482: displayNames ame_util.longStringList;
1483: errorCode integer;
1484: errorMessage ame_util.longestStringType;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1481: colonLocation2 integer;
1482: displayNames ame_util.longStringList;
1483: errorCode integer;
1484: errorMessage ame_util.longestStringType;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1483: errorCode integer;
1484: errorMessage ame_util.longestStringType;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1490: outputIndex integer;
1491: parameters ame_util.longStringList;
1484: errorMessage ame_util.longestStringType;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1490: outputIndex integer;
1491: parameters ame_util.longStringList;
1492: queryStrings ame_util.longestStringList;
1485: approverNames ame_util.longStringList;
1486: noTransIdDefinedException exception;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1490: outputIndex integer;
1491: parameters ame_util.longStringList;
1492: queryStrings ame_util.longestStringList;
1493: rowsFound integer;
1487: orderNumbers ame_util.idList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1490: outputIndex integer;
1491: parameters ame_util.longStringList;
1492: queryStrings ame_util.longestStringList;
1493: rowsFound integer;
1494: tempGroupMembers dbms_sql.Varchar2_Table;
1495: upperParameterNames ame_util.stringList;
1488: origSystemIds ame_util.idList;
1489: origSystems ame_util.stringList;
1490: outputIndex integer;
1491: parameters ame_util.longStringList;
1492: queryStrings ame_util.longestStringList;
1493: rowsFound integer;
1494: tempGroupMembers dbms_sql.Varchar2_Table;
1495: upperParameterNames ame_util.stringList;
1496: tempGroupName ame_util.stringType;
1491: parameters ame_util.longStringList;
1492: queryStrings ame_util.longestStringList;
1493: rowsFound integer;
1494: tempGroupMembers dbms_sql.Varchar2_Table;
1495: upperParameterNames ame_util.stringList;
1496: tempGroupName ame_util.stringType;
1497: l_error_code number;
1498: begin
1499: open groupMemberCursor(groupIdIn => groupIdIn);
1492: queryStrings ame_util.longestStringList;
1493: rowsFound integer;
1494: tempGroupMembers dbms_sql.Varchar2_Table;
1495: upperParameterNames ame_util.stringList;
1496: tempGroupName ame_util.stringType;
1497: l_error_code number;
1498: begin
1499: open groupMemberCursor(groupIdIn => groupIdIn);
1500: fetch groupMemberCursor bulk collect
1507: orderNumbers;
1508: close groupMemberCursor;
1509: outputIndex := 0; /* pre-increment */
1510: for i in 1 .. parameters.count loop
1511: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1512: dynamicCursor := dbms_sql.open_cursor;
1513: dbms_sql.parse(dynamicCursor,
1514: ame_util.removeReturns(stringIn => queryStrings(i),
1515: replaceWithSpaces => true),
1510: for i in 1 .. parameters.count loop
1511: if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1512: dynamicCursor := dbms_sql.open_cursor;
1513: dbms_sql.parse(dynamicCursor,
1514: ame_util.removeReturns(stringIn => queryStrings(i),
1515: replaceWithSpaces => true),
1516: dbms_sql.native);
1517: if(instrb(queryStrings(i),
1518: ame_util.transactionIdPlaceholder) > 0) then
1514: ame_util.removeReturns(stringIn => queryStrings(i),
1515: replaceWithSpaces => true),
1516: dbms_sql.native);
1517: if(instrb(queryStrings(i),
1518: ame_util.transactionIdPlaceholder) > 0) then
1519: if transactionIdIn is null then
1520: dbms_sql.close_cursor(dynamicCursor);
1521: raise noTransIdDefinedException;
1522: end if;
1520: dbms_sql.close_cursor(dynamicCursor);
1521: raise noTransIdDefinedException;
1522: end if;
1523: dbms_sql.bind_variable(dynamicCursor,
1524: ame_util.transactionIdPlaceholder,
1525: transactionIdIn,
1526: 50);
1527: end if;
1528: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1524: ame_util.transactionIdPlaceholder,
1525: transactionIdIn,
1526: 50);
1527: end if;
1528: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1529: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1530: dbms_sql.close_cursor(dynamicCursor);
1531: raise noItemBindException;
1532: end if;
1525: transactionIdIn,
1526: 50);
1527: end if;
1528: if(instrb(queryStrings(i),ame_util2.itemClassPlaceHolder) > 0) or
1529: (instrb(queryStrings(i),ame_util2.itemIdPlaceHolder) > 0) then
1530: dbms_sql.close_cursor(dynamicCursor);
1531: raise noItemBindException;
1532: end if;
1533: dbms_sql.define_array(dynamicCursor,
1560: if(colonLocation2 = 0) then /* first case (old style) */
1561: memberOrigSystemIdsOut(outputIndex) :=
1562: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1563: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1564: upper(ame_util.approverPersonId)) then
1565: memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
1566: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1567: upper(ame_util.approverUserId)) then
1568: memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
1561: memberOrigSystemIdsOut(outputIndex) :=
1562: substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1563: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1564: upper(ame_util.approverPersonId)) then
1565: memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
1566: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1567: upper(ame_util.approverUserId)) then
1568: memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
1569: else
1563: if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1564: upper(ame_util.approverPersonId)) then
1565: memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
1566: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1567: upper(ame_util.approverUserId)) then
1568: memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
1569: else
1570: memberOrigSystemsOut(outputIndex) :=
1571: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1564: upper(ame_util.approverPersonId)) then
1565: memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
1566: elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1567: upper(ame_util.approverUserId)) then
1568: memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
1569: else
1570: memberOrigSystemsOut(outputIndex) :=
1571: substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1572: memberOrigSystemIdsOut(outputIndex) :=
1589: when others then
1590: l_error_code := sqlcode;
1591: if l_error_code = -20213 then
1592: errorCode := -20223;
1593: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1594: messageNameIn => 'AME_400838_INV_APR_FOUND',
1595: tokenNameOneIn => 'PROCESS_NAME',
1596: tokenValueOneIn => 'ame_api3.getGroupMembers4',
1597: tokenNameTwoIn => 'ORIG_SYSTEM',
1616: when others then
1617: l_error_code := sqlcode;
1618: if l_error_code = -20213 then
1619: errorCode := -20223;
1620: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1621: messageNameIn => 'AME_400838_INV_APR_FOUND',
1622: tokenNameOneIn => 'PROCESS_NAME',
1623: tokenValueOneIn => 'ame_api3.getGroupMembers4',
1624: tokenNameTwoIn => 'ORIG_SYSTEM',
1638: if(groupMemberCursor%isopen) then
1639: close groupMemberCursor;
1640: end if;
1641: errorCode := -20315;
1642: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1643: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1644: ame_util.runtimeException(packageNameIn => 'ame_api3',
1645: routineNameIn => 'getGroupMembers4',
1646: exceptionNumberIn => errorCode,
1640: end if;
1641: errorCode := -20315;
1642: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1643: messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1644: ame_util.runtimeException(packageNameIn => 'ame_api3',
1645: routineNameIn => 'getGroupMembers4',
1646: exceptionNumberIn => errorCode,
1647: exceptionStringIn => errorMessage);
1648: raise_application_error(errorCode,
1653: end if;
1654: errorCode := -20001;
1655: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1656: ,groupNameOut => tempGroupName);
1657: errorMessage := ame_util.getMessage(
1658: applicationShortNameIn => 'PER',
1659: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1660: tokenNameOneIn => 'APPROVAL_GROUP',
1661: tokenValueOneIn => tempGroupName);
1658: applicationShortNameIn => 'PER',
1659: messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1660: tokenNameOneIn => 'APPROVAL_GROUP',
1661: tokenValueOneIn => tempGroupName);
1662: ame_util.runtimeException(packageNameIn => 'ame_api3',
1663: routineNameIn => 'getGroupMembers4',
1664: exceptionNumberIn => errorCode,
1665: exceptionStringIn => errorMessage);
1666: raise_application_error(errorCode,
1672: memberDisplayNamesOut.delete;
1673: errorCode := -20316;
1674: ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1675: ,groupNameOut => tempGroupName);
1676: errorMessage := ame_util.getMessage(
1677: applicationShortNameIn => 'PER',
1678: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1679: tokenNameOneIn => 'APPROVER_GROUP',
1680: tokenValueOneIn => tempGroupName);
1677: applicationShortNameIn => 'PER',
1678: messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1679: tokenNameOneIn => 'APPROVER_GROUP',
1680: tokenValueOneIn => tempGroupName);
1681: ame_util.runtimeException(packageNameIn => 'ame_api3',
1682: routineNameIn => 'getGroupMembers4',
1683: exceptionNumberIn => errorCode,
1684: exceptionStringIn => errorMessage);
1685: raise_application_error(errorCode,
1687: when others then
1688: if(groupMemberCursor%isopen) then
1689: close groupMemberCursor;
1690: end if;
1691: ame_util.runtimeException(packageNameIn => 'ame_api3',
1692: routineNameIn => 'getGroupMembers4',
1693: exceptionNumberIn => sqlcode,
1694: exceptionStringIn => sqlerrm);
1695: raise;
1695: raise;
1696: end getGroupMembers4;
1697: procedure getItemClasses( applicationIdIn in number,
1698: transactionTypeIn in varchar2,
1699: itemClassIdsOut out nocopy ame_util.idList,
1700: itemClassNamesOut out nocopy ame_util.stringList) as
1701: ameAppId integer;
1702: begin
1703: ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
1696: end getGroupMembers4;
1697: procedure getItemClasses( applicationIdIn in number,
1698: transactionTypeIn in varchar2,
1699: itemClassIdsOut out nocopy ame_util.idList,
1700: itemClassNamesOut out nocopy ame_util.stringList) as
1701: ameAppId integer;
1702: begin
1703: ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
1704: transactionTypeIdIn => transactionTypeIn);
1706: itemClassIdsOut => itemClassIdsOut,
1707: itemClassNamesOut => itemClassNamesOut);
1708: exception
1709: when others then
1710: ame_util.runtimeException(packageNameIn => 'ame_api3',
1711: routineNameIn => 'getItemClasses',
1712: exceptionNumberIn => sqlcode,
1713: exceptionStringIn => sqlerrm);
1714: raise;
1719: begin
1720: itemClassIdOut := ame_admin_pkg.getItemClassIdByName(itemClassNameIn => itemClassNameIn);
1721: exception
1722: when others then
1723: ame_util.runtimeException(packageNameIn => 'ame_api3',
1724: routineNameIn => 'getItemClassId',
1725: exceptionNumberIn => sqlcode,
1726: exceptionStringIn => sqlerrm);
1727: raise;
1732: begin
1733: itemClassNameOut := ame_admin_pkg.getItemClassName(itemClassIdIn => itemClassIdIn);
1734: exception
1735: when others then
1736: ame_util.runtimeException(packageNameIn => 'ame_api3',
1737: routineNameIn => 'getItemClassName',
1738: exceptionNumberIn => sqlcode,
1739: exceptionStringIn => sqlerrm);
1740: raise;
1786: end getApproverDetails;
1787: procedure getOldApprovers( applicationIdIn in number,
1788: transactionTypeIn in varchar2,
1789: transactionIdIn in varchar2,
1790: oldApproversOut out nocopy ame_util.approversTable2) as
1791: ameAppId integer;
1792: cursor oldApproverCursor(applicationIdIn in integer,
1793: transactionIdIn in varchar2) is
1794: select
1851: oldApproversOut(tempIndex).source := null;
1852: end loop;
1853: exception
1854: when others then
1855: ame_util.runtimeException(packageNameIn => 'ame_api3',
1856: routineNameIn => 'getOldApprovers',
1857: exceptionNumberIn => sqlcode,
1858: exceptionStringIn => sqlerrm);
1859: oldApproversOut.delete;
1864: end getOldApprovers;
1865: procedure getRuleDetails1( ruleIdIn in number,
1866: ruleTypeOut out nocopy varchar2,
1867: ruleDescriptionOut out nocopy varchar2,
1868: conditionIdsOut out nocopy ame_util.idList,
1869: actionTypeNamesOut out nocopy ame_util.stringList,
1870: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1871: actionDescriptionsOut out nocopy ame_util.stringList) as
1872: cursor actionCursor is
1865: procedure getRuleDetails1( ruleIdIn in number,
1866: ruleTypeOut out nocopy varchar2,
1867: ruleDescriptionOut out nocopy varchar2,
1868: conditionIdsOut out nocopy ame_util.idList,
1869: actionTypeNamesOut out nocopy ame_util.stringList,
1870: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1871: actionDescriptionsOut out nocopy ame_util.stringList) as
1872: cursor actionCursor is
1873: select
1866: ruleTypeOut out nocopy varchar2,
1867: ruleDescriptionOut out nocopy varchar2,
1868: conditionIdsOut out nocopy ame_util.idList,
1869: actionTypeNamesOut out nocopy ame_util.stringList,
1870: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1871: actionDescriptionsOut out nocopy ame_util.stringList) as
1872: cursor actionCursor is
1873: select
1874: ame_action_types.name,
1867: ruleDescriptionOut out nocopy varchar2,
1868: conditionIdsOut out nocopy ame_util.idList,
1869: actionTypeNamesOut out nocopy ame_util.stringList,
1870: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1871: actionDescriptionsOut out nocopy ame_util.stringList) as
1872: cursor actionCursor is
1873: select
1874: ame_action_types.name,
1875: ame_action_types.description,
1883: ame_actions.action_id = ame_action_usages.action_id and
1884: ame_action_types.action_type_id = ame_actions.action_type_id and
1885: sysdate between
1886: ame_action_usages.start_date and
1887: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1888: sysdate between
1889: ame_action_types.start_date and
1890: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1891: sysdate between
1886: ame_action_usages.start_date and
1887: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1888: sysdate between
1889: ame_action_types.start_date and
1890: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1891: sysdate between
1892: ame_actions.start_date and
1893: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1894: cursor conditionCursor(ruleIdIn in integer) is
1889: ame_action_types.start_date and
1890: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1891: sysdate between
1892: ame_actions.start_date and
1893: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1894: cursor conditionCursor(ruleIdIn in integer) is
1895: select condition_id
1896: from ame_condition_usages
1897: where
1897: where
1898: ame_condition_usages.rule_id = ruleIdIn and
1899: sysdate between
1900: start_date and
1901: nvl(end_date - ame_util.oneSecond, sysdate);
1902: cursor ruleCursor is
1903: select
1904: ame_rules.rule_type,
1905: ame_rules.description
1908: where
1909: ame_rules.rule_id = ruleIdIn and
1910: sysdate between
1911: ame_rules.start_date and
1912: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
1913: begin
1914: open ruleCursor;
1915: fetch ruleCursor into
1916: ruleTypeOut,
1927: into conditionIdsOut;
1928: close conditionCursor;
1929: exception
1930: when others then
1931: ame_util.runtimeException(packageNameIn => 'ame_api3',
1932: routineNameIn => 'getRuleDetails1',
1933: exceptionNumberIn => sqlcode,
1934: exceptionStringIn => sqlerrm);
1935: conditionIdsOut.delete;
1937: end getRuleDetails1;
1938: procedure getRuleDetails2( ruleIdIn in number,
1939: ruleTypeOut out nocopy varchar2,
1940: ruleDescriptionOut out nocopy varchar2,
1941: conditionDescriptionsOut out nocopy ame_util.longestStringList,
1942: actionTypeNamesOut out nocopy ame_util.stringList,
1943: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1944: actionDescriptionsOut out nocopy ame_util.stringList) as
1945: cursor actionCursor is
1938: procedure getRuleDetails2( ruleIdIn in number,
1939: ruleTypeOut out nocopy varchar2,
1940: ruleDescriptionOut out nocopy varchar2,
1941: conditionDescriptionsOut out nocopy ame_util.longestStringList,
1942: actionTypeNamesOut out nocopy ame_util.stringList,
1943: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1944: actionDescriptionsOut out nocopy ame_util.stringList) as
1945: cursor actionCursor is
1946: select
1939: ruleTypeOut out nocopy varchar2,
1940: ruleDescriptionOut out nocopy varchar2,
1941: conditionDescriptionsOut out nocopy ame_util.longestStringList,
1942: actionTypeNamesOut out nocopy ame_util.stringList,
1943: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1944: actionDescriptionsOut out nocopy ame_util.stringList) as
1945: cursor actionCursor is
1946: select
1947: ame_action_types.name,
1940: ruleDescriptionOut out nocopy varchar2,
1941: conditionDescriptionsOut out nocopy ame_util.longestStringList,
1942: actionTypeNamesOut out nocopy ame_util.stringList,
1943: actionTypeDescriptionsOut out nocopy ame_util.stringList,
1944: actionDescriptionsOut out nocopy ame_util.stringList) as
1945: cursor actionCursor is
1946: select
1947: ame_action_types.name,
1948: ame_action_types.description,
1956: ame_actions.action_id = ame_action_usages.action_id and
1957: ame_action_types.action_type_id = ame_actions.action_type_id and
1958: sysdate between
1959: ame_action_usages.start_date and
1960: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1961: sysdate between
1962: ame_action_types.start_date and
1963: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1964: sysdate between
1959: ame_action_usages.start_date and
1960: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1961: sysdate between
1962: ame_action_types.start_date and
1963: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1964: sysdate between
1965: ame_actions.start_date and
1966: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1967: cursor conditionCursor(ruleIdIn in integer) is
1962: ame_action_types.start_date and
1963: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1964: sysdate between
1965: ame_actions.start_date and
1966: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1967: cursor conditionCursor(ruleIdIn in integer) is
1968: select ame_condition_pkg.getDescription(ame_condition_usages.condition_id)
1969: from ame_condition_usages
1970: where
1970: where
1971: ame_condition_usages.rule_id = ruleIdIn and
1972: sysdate between
1973: ame_condition_usages.start_date and
1974: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate);
1975: cursor ruleCursor is
1976: select
1977: ame_rules.rule_type,
1978: ame_rules.description
1981: where
1982: ame_rules.rule_id = ruleIdIn and
1983: sysdate between
1984: ame_rules.start_date and
1985: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
1986: begin
1987: open ruleCursor;
1988: fetch ruleCursor into
1989: ruleTypeOut,
2000: into conditionDescriptionsOut;
2001: close conditionCursor;
2002: exception
2003: when others then
2004: ame_util.runtimeException(packageNameIn => 'ame_api3',
2005: routineNameIn => 'getRuleDetails2',
2006: exceptionNumberIn => sqlcode,
2007: exceptionStringIn => sqlerrm);
2008: conditionDescriptionsOut.delete;
2010: end getRuleDetails2;
2011: procedure getRuleDetails3( ruleIdIn in number,
2012: ruleTypeOut out nocopy varchar2,
2013: ruleDescriptionOut out nocopy varchar2,
2014: conditionIdsOut out nocopy ame_util.idList,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2011: procedure getRuleDetails3( ruleIdIn in number,
2012: ruleTypeOut out nocopy varchar2,
2013: ruleDescriptionOut out nocopy varchar2,
2014: conditionIdsOut out nocopy ame_util.idList,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2019: actionDescriptionsOut out nocopy ame_util.stringList) as
2012: ruleTypeOut out nocopy varchar2,
2013: ruleDescriptionOut out nocopy varchar2,
2014: conditionIdsOut out nocopy ame_util.idList,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2019: actionDescriptionsOut out nocopy ame_util.stringList) as
2020: cursor actionCursor is
2013: ruleDescriptionOut out nocopy varchar2,
2014: conditionIdsOut out nocopy ame_util.idList,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2019: actionDescriptionsOut out nocopy ame_util.stringList) as
2020: cursor actionCursor is
2021: select
2014: conditionIdsOut out nocopy ame_util.idList,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2019: actionDescriptionsOut out nocopy ame_util.stringList) as
2020: cursor actionCursor is
2021: select
2022: ame_action_types.name,
2015: conditionDescriptionsOut out nocopy ame_util.longestStringList,
2016: conditionHasLOVsOut out nocopy ame_util.charList,
2017: actionTypeNamesOut out nocopy ame_util.stringList,
2018: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2019: actionDescriptionsOut out nocopy ame_util.stringList) as
2020: cursor actionCursor is
2021: select
2022: ame_action_types.name,
2023: ame_action_types.description,
2031: ame_actions.action_id = ame_action_usages.action_id and
2032: ame_action_types.action_type_id = ame_actions.action_type_id and
2033: sysdate between
2034: ame_action_usages.start_date and
2035: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
2036: sysdate between
2037: ame_action_types.start_date and
2038: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2039: sysdate between
2034: ame_action_usages.start_date and
2035: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
2036: sysdate between
2037: ame_action_types.start_date and
2038: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2039: sysdate between
2040: ame_actions.start_date and
2041: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
2042: cursor conditionCursor(ruleIdIn in integer) is
2037: ame_action_types.start_date and
2038: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2039: sysdate between
2040: ame_actions.start_date and
2041: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
2042: cursor conditionCursor(ruleIdIn in integer) is
2043: select
2044: ame_conditions.condition_id,
2045: ame_condition_pkg.getDescription(ame_conditions.condition_id),
2043: select
2044: ame_conditions.condition_id,
2045: ame_condition_pkg.getDescription(ame_conditions.condition_id),
2046: decode(ame_attributes.attribute_type,
2047: ame_util.stringAttributeType, ame_util.booleanTrue,
2048: /* default */ ame_util.booleanFalse)
2049: from
2050: ame_conditions,
2051: ame_condition_usages,
2044: ame_conditions.condition_id,
2045: ame_condition_pkg.getDescription(ame_conditions.condition_id),
2046: decode(ame_attributes.attribute_type,
2047: ame_util.stringAttributeType, ame_util.booleanTrue,
2048: /* default */ ame_util.booleanFalse)
2049: from
2050: ame_conditions,
2051: ame_condition_usages,
2052: ame_attributes
2055: ame_conditions.condition_id = ame_condition_usages.condition_id and
2056: ame_attributes.attribute_id = ame_conditions.attribute_id and
2057: sysdate between
2058: ame_condition_usages.start_date and
2059: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate) and
2060: sysdate between
2061: ame_conditions.start_date and
2062: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
2063: sysdate between
2058: ame_condition_usages.start_date and
2059: nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate) and
2060: sysdate between
2061: ame_conditions.start_date and
2062: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
2063: sysdate between
2064: ame_attributes.start_date and
2065: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
2066: cursor ruleCursor is
2061: ame_conditions.start_date and
2062: nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
2063: sysdate between
2064: ame_attributes.start_date and
2065: nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
2066: cursor ruleCursor is
2067: select
2068: ame_rules.rule_type,
2069: ame_rules.description
2072: where
2073: ame_rules.rule_id = ruleIdIn and
2074: sysdate between
2075: ame_rules.start_date and
2076: nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
2077: begin
2078: open ruleCursor;
2079: fetch ruleCursor into
2080: ruleTypeOut,
2094: conditionHasLOVsOut;
2095: close conditionCursor;
2096: exception
2097: when others then
2098: ame_util.runtimeException(packageNameIn => 'ame_api3',
2099: routineNameIn => 'getRuleDetails3',
2100: exceptionNumberIn => sqlcode,
2101: exceptionStringIn => sqlerrm);
2102: conditionDescriptionsOut.delete;
2106: end getRuleDetails3;
2107: procedure insertApprover( applicationIdIn in number,
2108: transactionTypeIn in varchar2,
2109: transactionIdIn in varchar2,
2110: approverIn in ame_util.approverRecord2,
2111: positionIn in number,
2112: insertionIn in ame_util.insertionRecord2) as
2113: ameApplicationId integer;
2114: absoluteOrder integer;
2108: transactionTypeIn in varchar2,
2109: transactionIdIn in varchar2,
2110: approverIn in ame_util.approverRecord2,
2111: positionIn in number,
2112: insertionIn in ame_util.insertionRecord2) as
2113: ameApplicationId integer;
2114: absoluteOrder integer;
2115: availableInsertions ame_util.insertionsTable2;
2116: badInsertionTypeException exception;
2111: positionIn in number,
2112: insertionIn in ame_util.insertionRecord2) as
2113: ameApplicationId integer;
2114: absoluteOrder integer;
2115: availableInsertions ame_util.insertionsTable2;
2116: badInsertionTypeException exception;
2117: badStatusException exception;
2118: disallowedAuthException exception;
2119: errorCode integer;
2116: badInsertionTypeException exception;
2117: badStatusException exception;
2118: disallowedAuthException exception;
2119: errorCode integer;
2120: errorMessage ame_util.longStringType;
2121: incompatibleApproverException exception;
2122: insertionOrder integer;
2123: insertionTypeNotFound boolean;
2124: noApproverCategoryException exception;
2142: raise incompatibleApproverException;
2143: end if;
2144: /* Make sure that the approver catgeory is defined correctly for approver */
2145: if (( approverIn.approver_category is null ) or
2146: ( approverIn.approver_category <> ame_util.approvalApproverCategory and
2147: approverIn.approver_category <> ame_util.fyiApproverCategory)) then
2148: raise noApproverCategoryException;
2149: end if;
2150: /* Handler ame_util.firstApprover order types specially. */
2143: end if;
2144: /* Make sure that the approver catgeory is defined correctly for approver */
2145: if (( approverIn.approver_category is null ) or
2146: ( approverIn.approver_category <> ame_util.approvalApproverCategory and
2147: approverIn.approver_category <> ame_util.fyiApproverCategory)) then
2148: raise noApproverCategoryException;
2149: end if;
2150: /* Handler ame_util.firstApprover order types specially. */
2151: if(insertionIn.order_type = ame_util.firstAuthority) then
2146: ( approverIn.approver_category <> ame_util.approvalApproverCategory and
2147: approverIn.approver_category <> ame_util.fyiApproverCategory)) then
2148: raise noApproverCategoryException;
2149: end if;
2150: /* Handler ame_util.firstApprover order types specially. */
2151: if(insertionIn.order_type = ame_util.firstAuthority) then
2152: ame_api2.setFirstAuthorityApprover(applicationIdIn => applicationIdIn,
2153: transactionTypeIn => transactionTypeIn,
2154: transactionIdIn => transactionIdIn,
2147: approverIn.approver_category <> ame_util.fyiApproverCategory)) then
2148: raise noApproverCategoryException;
2149: end if;
2150: /* Handler ame_util.firstApprover order types specially. */
2151: if(insertionIn.order_type = ame_util.firstAuthority) then
2152: ame_api2.setFirstAuthorityApprover(applicationIdIn => applicationIdIn,
2153: transactionTypeIn => transactionTypeIn,
2154: transactionIdIn => transactionIdIn,
2155: approverIn => approverIn,
2152: ame_api2.setFirstAuthorityApprover(applicationIdIn => applicationIdIn,
2153: transactionTypeIn => transactionTypeIn,
2154: transactionIdIn => transactionIdIn,
2155: approverIn => approverIn,
2156: clearChainStatusYNIn => ame_util.booleanFalse);
2157: return;
2158: end if;
2159: /* Make sure approverIn.approval_status is null. */
2160: if(approverIn.approval_status is not null) then
2160: if(approverIn.approval_status is not null) then
2161: raise badStatusException;
2162: end if;
2163: /* Make sure approverIn.api_insertion is of the right type. */
2164: if(approverIn.api_insertion = ame_util.oamGenerated) then
2165: raise badInsertionTypeException;
2166: end if;
2167: /* Lock Transactions */
2168: ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
2256: transactionIdIn => transactionIdIn,
2257: transactionTypeIdIn => transactionTypeIn);
2258: errorCode := -20001;
2259: errorMessage := ambiguousApproverMessage;
2260: ame_util.runtimeException(packageNameIn => 'ame_api3',
2261: routineNameIn => 'insertApprover',
2262: exceptionNumberIn => errorCode,
2263: exceptionStringIn => errorMessage);
2264: raise_application_error(errorCode,
2267: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2268: transactionIdIn => transactionIdIn,
2269: transactionTypeIdIn => transactionTypeIn);
2270: errorCode := -20304;
2271: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2272: messageNameIn => 'AME_400240_API_INV_ORDER_TYPE');
2273: ame_util.runtimeException(packageNameIn => 'ame_api3',
2274: routineNameIn => 'insertApprover',
2275: exceptionNumberIn => errorCode,
2269: transactionTypeIdIn => transactionTypeIn);
2270: errorCode := -20304;
2271: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2272: messageNameIn => 'AME_400240_API_INV_ORDER_TYPE');
2273: ame_util.runtimeException(packageNameIn => 'ame_api3',
2274: routineNameIn => 'insertApprover',
2275: exceptionNumberIn => errorCode,
2276: exceptionStringIn => errorMessage);
2277: raise_application_error(errorCode,
2280: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2281: transactionIdIn => transactionIdIn,
2282: transactionTypeIdIn => transactionTypeIn);
2283: errorCode := -20305;
2284: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2285: messageNameIn => 'AME_400241_API_NON_NULL_INSAPP');
2286: ame_util.runtimeException(packageNameIn => 'ame_api3',
2287: routineNameIn => 'insertApprover',
2288: exceptionNumberIn => errorCode,
2282: transactionTypeIdIn => transactionTypeIn);
2283: errorCode := -20305;
2284: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2285: messageNameIn => 'AME_400241_API_NON_NULL_INSAPP');
2286: ame_util.runtimeException(packageNameIn => 'ame_api3',
2287: routineNameIn => 'insertApprover',
2288: exceptionNumberIn => errorCode,
2289: exceptionStringIn => errorMessage);
2290: raise_application_error(errorCode,
2293: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2294: transactionIdIn => transactionIdIn,
2295: transactionTypeIdIn => transactionTypeIn);
2296: errorCode := -20313;
2297: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2298: messageNameIn => 'AME_400446_INCMPTBLE_APPR_INS');
2299: ame_util.runtimeException(packageNameIn => 'ame_api3',
2300: routineNameIn => 'insertApprover',
2301: exceptionNumberIn => errorCode,
2295: transactionTypeIdIn => transactionTypeIn);
2296: errorCode := -20313;
2297: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2298: messageNameIn => 'AME_400446_INCMPTBLE_APPR_INS');
2299: ame_util.runtimeException(packageNameIn => 'ame_api3',
2300: routineNameIn => 'insertApprover',
2301: exceptionNumberIn => errorCode,
2302: exceptionStringIn => errorMessage);
2303: raise_application_error(errorCode,
2306: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2307: transactionIdIn => transactionIdIn,
2308: transactionTypeIdIn => transactionTypeIn);
2309: errorCode := -20314;
2310: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2311: messageNameIn => 'AME_400447_INVALID_APPR_CATG');
2312: ame_util.runtimeException(packageNameIn => 'ame_api3',
2313: routineNameIn => 'insertApprover',
2314: exceptionNumberIn => errorCode,
2308: transactionTypeIdIn => transactionTypeIn);
2309: errorCode := -20314;
2310: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2311: messageNameIn => 'AME_400447_INVALID_APPR_CATG');
2312: ame_util.runtimeException(packageNameIn => 'ame_api3',
2313: routineNameIn => 'insertApprover',
2314: exceptionNumberIn => errorCode,
2315: exceptionStringIn => errorMessage);
2316: raise_application_error(errorCode,
2318: when others then
2319: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2320: transactionIdIn => transactionIdIn,
2321: transactionTypeIdIn => transactionTypeIn);
2322: ame_util.runtimeException(packageNameIn => 'ame_api3',
2323: routineNameIn => 'insertApprover',
2324: exceptionNumberIn => sqlcode,
2325: exceptionStringIn => sqlerrm);
2326: raise;
2326: raise;
2327: end insertApprover;
2328: procedure parseApproverSource(approverSourceIn in varchar2,
2329: sourceDescriptionOut out nocopy varchar2,
2330: ruleIdListOut out nocopy ame_util.idList) as
2331: begin
2332: ame_util.parseSourceValue(sourceValueIn => approverSourceIn,
2333: sourceDescriptionOut => sourceDescriptionOut,
2334: ruleIdListOut => ruleIdListOut);
2328: procedure parseApproverSource(approverSourceIn in varchar2,
2329: sourceDescriptionOut out nocopy varchar2,
2330: ruleIdListOut out nocopy ame_util.idList) as
2331: begin
2332: ame_util.parseSourceValue(sourceValueIn => approverSourceIn,
2333: sourceDescriptionOut => sourceDescriptionOut,
2334: ruleIdListOut => ruleIdListOut);
2335: end parseApproverSource;
2336:
2336:
2337: procedure migrateAnchors(p_applicationIdIn in number,
2338: p_transactionTypeIn in varchar2,
2339: p_transactionIdIn in varchar2,
2340: p_approverIn in ame_util.approverRecord2) is
2341: l_firstAName varchar2(100);
2342: l_order_type varchar2(50);
2343: l_parameter varchar2(3000);
2344: l_parameter_new varchar2(3000);
2423: procedure removeRecursive(nameIn in varchar2
2424: ,transactionIdIn in varchar2
2425: ,transactionTypeIn in varchar2
2426: ,applicationIdIn in number) is
2427: nameList ame_util.stringList;
2428: c_aplId number;
2429: c_transactionType varchar2(100);
2430: c_transactionId varchar2(100);
2431: begin
2463: end removeRecursive;
2464: procedure suppressApprover(applicationIdIn in number,
2465: transactionTypeIn in varchar2,
2466: transactionIdIn in varchar2,
2467: approverIn in ame_util.approverRecord2) as
2468: ameApplicationId integer;
2469: approverIndex integer;
2470: approvers ame_util.approversTable2;
2471: allowDeletingOamApprovers ame_util.attributeValueType;
2466: transactionIdIn in varchar2,
2467: approverIn in ame_util.approverRecord2) as
2468: ameApplicationId integer;
2469: approverIndex integer;
2470: approvers ame_util.approversTable2;
2471: allowDeletingOamApprovers ame_util.attributeValueType;
2472: badDeletionException exception;
2473: errorCode integer;
2474: errorMessage ame_util.longStringType;
2467: approverIn in ame_util.approverRecord2) as
2468: ameApplicationId integer;
2469: approverIndex integer;
2470: approvers ame_util.approversTable2;
2471: allowDeletingOamApprovers ame_util.attributeValueType;
2472: badDeletionException exception;
2473: errorCode integer;
2474: errorMessage ame_util.longStringType;
2475: noMatchException exception;
2470: approvers ame_util.approversTable2;
2471: allowDeletingOamApprovers ame_util.attributeValueType;
2472: badDeletionException exception;
2473: errorCode integer;
2474: errorMessage ame_util.longStringType;
2475: noMatchException exception;
2476: orderTypeLocation integer;
2477: parameterLocation integer;
2478: ruleIdList ame_util.idList;
2474: errorMessage ame_util.longStringType;
2475: noMatchException exception;
2476: orderTypeLocation integer;
2477: parameterLocation integer;
2478: ruleIdList ame_util.idList;
2479: sourceDescription ame_util.stringType;
2480: sourceLength integer;
2481: tempOrderType ame_temp_insertions.order_type%type;
2482: tempParameter ame_temp_insertions.parameter%type;
2475: noMatchException exception;
2476: orderTypeLocation integer;
2477: parameterLocation integer;
2478: ruleIdList ame_util.idList;
2479: sourceDescription ame_util.stringType;
2480: sourceLength integer;
2481: tempOrderType ame_temp_insertions.order_type%type;
2482: tempParameter ame_temp_insertions.parameter%type;
2483: upperLimit integer;
2486: from ame_temp_insertions
2487: where application_id = ame_engine.getAmeApplicationId
2488: and transaction_id = transactionIdIn
2489: and parameter = parameterIn);
2490: nameList ame_util.longStringList;
2491: anchorsExistException exception;
2492: cmpParameter ame_temp_insertions.parameter%type;
2493: anchorName varchar2(320);
2494: anchorList varchar2(1000);
2528: where sysdate between start_date and nvl(end_Date,sysdate)
2529: and transaction_type_id = transactionTypeIn
2530: and fnd_application_id = applicationIdIn)
2531: and name = approverIn.name;
2532: if l_orderType = ame_util.firstAuthority then
2533: removeRecursive(nameIn => approverIn.name
2534: ,transactionIdIn => transactionIdIn
2535: ,transactionTypeIn => transactionTypeIn
2536: ,applicationIdIn => applicationIdIn);
2596: approverIndex := null;
2597: for i in 1 .. upperLimit loop
2598: if((approvers(i).approval_status is null or
2599: approvers(i).approval_status in
2600: (ame_util.exceptionStatus,
2601: ame_util.noResponseStatus,
2602: ame_util.notifiedStatus,
2603: ame_util.rejectStatus)) and
2604: ame_engine.approversMatch(approverRecord1In => approverIn,
2597: for i in 1 .. upperLimit loop
2598: if((approvers(i).approval_status is null or
2599: approvers(i).approval_status in
2600: (ame_util.exceptionStatus,
2601: ame_util.noResponseStatus,
2602: ame_util.notifiedStatus,
2603: ame_util.rejectStatus)) and
2604: ame_engine.approversMatch(approverRecord1In => approverIn,
2605: approverRecord2In => approvers(i))) then
2598: if((approvers(i).approval_status is null or
2599: approvers(i).approval_status in
2600: (ame_util.exceptionStatus,
2601: ame_util.noResponseStatus,
2602: ame_util.notifiedStatus,
2603: ame_util.rejectStatus)) and
2604: ame_engine.approversMatch(approverRecord1In => approverIn,
2605: approverRecord2In => approvers(i))) then
2606: approverIndex := i;
2599: approvers(i).approval_status in
2600: (ame_util.exceptionStatus,
2601: ame_util.noResponseStatus,
2602: ame_util.notifiedStatus,
2603: ame_util.rejectStatus)) and
2604: ame_engine.approversMatch(approverRecord1In => approverIn,
2605: approverRecord2In => approvers(i))) then
2606: approverIndex := i;
2607: if approvers(approverIndex).api_insertion = ame_util.apiInsertion then
2603: ame_util.rejectStatus)) and
2604: ame_engine.approversMatch(approverRecord1In => approverIn,
2605: approverRecord2In => approvers(i))) then
2606: approverIndex := i;
2607: if approvers(approverIndex).api_insertion = ame_util.apiInsertion then
2608: anchorName := approvers(i).name;
2609: cmpParameter := approvers(i).name ||
2610: ame_util.fieldDelimiter ||
2611: approvers(i).item_class ||
2606: approverIndex := i;
2607: if approvers(approverIndex).api_insertion = ame_util.apiInsertion then
2608: anchorName := approvers(i).name;
2609: cmpParameter := approvers(i).name ||
2610: ame_util.fieldDelimiter ||
2611: approvers(i).item_class ||
2612: ame_util.fieldDelimiter ||
2613: approvers(i).item_id ||
2614: ame_util.fieldDelimiter ||
2608: anchorName := approvers(i).name;
2609: cmpParameter := approvers(i).name ||
2610: ame_util.fieldDelimiter ||
2611: approvers(i).item_class ||
2612: ame_util.fieldDelimiter ||
2613: approvers(i).item_id ||
2614: ame_util.fieldDelimiter ||
2615: approvers(i).action_type_id ||
2616: ame_util.fieldDelimiter ||
2610: ame_util.fieldDelimiter ||
2611: approvers(i).item_class ||
2612: ame_util.fieldDelimiter ||
2613: approvers(i).item_id ||
2614: ame_util.fieldDelimiter ||
2615: approvers(i).action_type_id ||
2616: ame_util.fieldDelimiter ||
2617: approvers(i).group_or_chain_id ||
2618: ame_util.fieldDelimiter ||
2612: ame_util.fieldDelimiter ||
2613: approvers(i).item_id ||
2614: ame_util.fieldDelimiter ||
2615: approvers(i).action_type_id ||
2616: ame_util.fieldDelimiter ||
2617: approvers(i).group_or_chain_id ||
2618: ame_util.fieldDelimiter ||
2619: approvers(i).occurrence;
2620: open getAnchorInsertions(cmpParameter);
2614: ame_util.fieldDelimiter ||
2615: approvers(i).action_type_id ||
2616: ame_util.fieldDelimiter ||
2617: approvers(i).group_or_chain_id ||
2618: ame_util.fieldDelimiter ||
2619: approvers(i).occurrence;
2620: open getAnchorInsertions(cmpParameter);
2621: fetch getAnchorInsertions bulk collect into nameList;
2622: if nameList.count > 0 then
2633: raise noMatchException;
2634: end if;
2635: ameApplicationId := ame_engine.getAmeApplicationId;
2636: -- parse the source to see if the approver was inserted.
2637: ame_util.parseSourceValue(sourceValueIn => approvers(approverIndex).source,
2638: sourceDescriptionOut => sourceDescription,
2639: ruleIdListOut => ruleIdList);
2640: -- If the approver was inserted, delete the approver from ame_temp_insertions.
2641: -- If the approver was OAM generated, check whether deleting OAM-generated approvers
2640: -- If the approver was inserted, delete the approver from ame_temp_insertions.
2641: -- If the approver was OAM generated, check whether deleting OAM-generated approvers
2642: -- is allowed. If so, record the deletion.
2643: --
2644: if(approvers(approverIndex).api_insertion = ame_util.oamGenerated or
2645: sourceDescription = ame_util.ruleGeneratedSource or
2646: sourceDescription = ame_util.surrogateSource) then
2647: allowDeletingOamApprovers :=
2648: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2641: -- If the approver was OAM generated, check whether deleting OAM-generated approvers
2642: -- is allowed. If so, record the deletion.
2643: --
2644: if(approvers(approverIndex).api_insertion = ame_util.oamGenerated or
2645: sourceDescription = ame_util.ruleGeneratedSource or
2646: sourceDescription = ame_util.surrogateSource) then
2647: allowDeletingOamApprovers :=
2648: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2649: if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
2642: -- is allowed. If so, record the deletion.
2643: --
2644: if(approvers(approverIndex).api_insertion = ame_util.oamGenerated or
2645: sourceDescription = ame_util.ruleGeneratedSource or
2646: sourceDescription = ame_util.surrogateSource) then
2647: allowDeletingOamApprovers :=
2648: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2649: if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
2650: raise badDeletionException;
2644: if(approvers(approverIndex).api_insertion = ame_util.oamGenerated or
2645: sourceDescription = ame_util.ruleGeneratedSource or
2646: sourceDescription = ame_util.surrogateSource) then
2647: allowDeletingOamApprovers :=
2648: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2649: if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
2650: raise badDeletionException;
2651: end if;
2652: insert into ame_temp_deletions(
2645: sourceDescription = ame_util.ruleGeneratedSource or
2646: sourceDescription = ame_util.surrogateSource) then
2647: allowDeletingOamApprovers :=
2648: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2649: if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
2650: raise badDeletionException;
2651: end if;
2652: insert into ame_temp_deletions(
2653: transaction_id,
2725: anchorList := anchorList || '; ';
2726: end if;
2727: end loop;
2728: errorCode := -20001;
2729: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2730: messageNameIn => 'AME_400651_ANCHORS_EXIST',
2731: tokenNameOneIn => 'APPROVER',
2732: tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
2733: tokenNameTwoIn => 'ANCHORED_APPROVERS',
2731: tokenNameOneIn => 'APPROVER',
2732: tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
2733: tokenNameTwoIn => 'ANCHORED_APPROVERS',
2734: tokenValueTwoIn => anchorList);
2735: ame_util.runtimeException(packageNameIn => 'ame_api3',
2736: routineNameIn => 'suppressApprover',
2737: exceptionNumberIn => errorCode,
2738: exceptionStringIn => errorMessage);
2739: raise_application_error(errorCode,
2742: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2743: transactionIdIn => transactionIdIn,
2744: transactionTypeIdIn => transactionTypeIn);
2745: errorCode := -20320;
2746: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2747: messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
2748: tokenNameOneIn => 'APPROVER_REC_PARAMS',
2749: tokenValueOneIn=>appr_rec_params);
2750: ame_util.runtimeException(packageNameIn => 'ame_api3',
2746: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2747: messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
2748: tokenNameOneIn => 'APPROVER_REC_PARAMS',
2749: tokenValueOneIn=>appr_rec_params);
2750: ame_util.runtimeException(packageNameIn => 'ame_api3',
2751: routineNameIn => 'suppressApprover',
2752: exceptionNumberIn => errorCode,
2753: exceptionStringIn => errorMessage);
2754: raise_application_error(errorCode,
2757: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2758: transactionIdIn => transactionIdIn,
2759: transactionTypeIdIn => transactionTypeIn);
2760: errorCode := -20301;
2761: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2762: messageNameIn => 'AME_400236_API_ADRGA_TRUE');
2763: ame_util.runtimeException(packageNameIn => 'ame_api3',
2764: routineNameIn => 'suppressApprover',
2765: exceptionNumberIn => errorCode,
2759: transactionTypeIdIn => transactionTypeIn);
2760: errorCode := -20301;
2761: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2762: messageNameIn => 'AME_400236_API_ADRGA_TRUE');
2763: ame_util.runtimeException(packageNameIn => 'ame_api3',
2764: routineNameIn => 'suppressApprover',
2765: exceptionNumberIn => errorCode,
2766: exceptionStringIn => errorMessage);
2767: raise_application_error(errorCode,
2770: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2771: transactionIdIn => transactionIdIn,
2772: transactionTypeIdIn => transactionTypeIn);
2773: errorCode := -20302;
2774: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2775: messageNameIn => 'AME_400237_API_NO MATCH_APPR');
2776: ame_util.runtimeException(packageNameIn => 'ame_api3',
2777: routineNameIn => 'suppressApprover',
2778: exceptionNumberIn => errorCode,
2772: transactionTypeIdIn => transactionTypeIn);
2773: errorCode := -20302;
2774: errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2775: messageNameIn => 'AME_400237_API_NO MATCH_APPR');
2776: ame_util.runtimeException(packageNameIn => 'ame_api3',
2777: routineNameIn => 'suppressApprover',
2778: exceptionNumberIn => errorCode,
2779: exceptionStringIn => errorMessage);
2780: raise_application_error(errorCode,
2782: when others then
2783: ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2784: transactionIdIn => transactionIdIn,
2785: transactionTypeIdIn => transactionTypeIn);
2786: ame_util.runtimeException(packageNameIn => 'ame_api3',
2787: routineNameIn => 'suppressApprover',
2788: exceptionNumberIn => sqlcode,
2789: exceptionStringIn => sqlerrm);
2790: raise;
2791: end suppressApprover;
2792: procedure suppressApprovers(applicationIdIn in integer,
2793: transactionTypeIn in varchar2,
2794: transactionIdIn in varchar2,
2795: approversIn in ame_util.approversTable2) as
2796: currentApproverInIndex integer;
2797: lastApproverInIndex integer;
2798: nextApproverInIndex integer;
2799: begin
2814: currentApproverInIndex := approversIn.next(currentApproverInIndex);
2815: end loop;
2816: exception
2817: when others then
2818: ame_util.runtimeException(packageNameIn => 'ame_api3',
2819: routineNameIn => 'suppressApprovers',
2820: exceptionNumberIn => sqlcode,
2821: exceptionStringIn => sqlerrm);
2822: raise;