[Home] [Help]
11: fix in amem0015.sql.)
12: */
13: procedure getNestedMembers(groupIdIn in integer,
14: effectiveDateIn in date default null,
15: parameterNamesOut out nocopy ame_util.stringList,
16: parametersOut out nocopy ame_util.stringList,
17: orderNumbersOut out nocopy ame_util.idList,
18: queryStringsOut out nocopy ame_util.longestStringList);
19: /*
12: */
13: procedure getNestedMembers(groupIdIn in integer,
14: effectiveDateIn in date default null,
15: parameterNamesOut out nocopy ame_util.stringList,
16: parametersOut out nocopy ame_util.stringList,
17: orderNumbersOut out nocopy ame_util.idList,
18: queryStringsOut out nocopy ame_util.longestStringList);
19: /*
20: updateDependentGroups updates ame_approval_group_members for the group with
13: procedure getNestedMembers(groupIdIn in integer,
14: effectiveDateIn in date default null,
15: parameterNamesOut out nocopy ame_util.stringList,
16: parametersOut out nocopy ame_util.stringList,
17: orderNumbersOut out nocopy ame_util.idList,
18: queryStringsOut out nocopy ame_util.longestStringList);
19: /*
20: updateDependentGroups updates ame_approval_group_members for the group with
21: group ID groupIdIn, and all groups depending on it (explicitly or implicitly).
14: effectiveDateIn in date default null,
15: parameterNamesOut out nocopy ame_util.stringList,
16: parametersOut out nocopy ame_util.stringList,
17: orderNumbersOut out nocopy ame_util.idList,
18: queryStringsOut out nocopy ame_util.longestStringList);
19: /*
20: updateDependentGroups updates ame_approval_group_members for the group with
21: group ID groupIdIn, and all groups depending on it (explicitly or implicitly).
22: If deleteGroupIn is true, updateDependentGroups also removes (end-dates) the
33: from ame_approval_groups
34: where
35: approval_group_id = approvalGroupIdIn and
36: sysdate between start_date and
37: nvl(end_date - ame_util.oneSecond, sysdate) ;
38: if(createdByValue = 1) then
39: return(true);
40: end if;
41: return(false);
41: return(false);
42: exception
43: when others then
44: rollback;
45: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
46: routineNameIn => 'isSeeded',
47: exceptionNumberIn => sqlcode,
48: exceptionStringIn => '(Approval Group ' ||
49: approvalGroupIdIn ||
61: from ame_approval_group_items
62: where
63: approval_group_id = approvalGroupIdIn and
64: sysdate between start_date and
65: nvl(end_date - ame_util.oneSecond, sysdate);
66: return(orderNumber);
67: exception
68: when others then
69: rollback;
66: return(orderNumber);
67: exception
68: when others then
69: rollback;
70: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
71: routineNameIn => 'getApprovalGroupItemMaxOrdNum',
72: exceptionNumberIn => sqlcode,
73: exceptionStringIn => sqlerrm);
74: raise;
82: from ame_approval_group_config
83: where
84: application_id = applicationIdIn and
85: sysdate between start_date and
86: nvl(end_date - ame_util.oneSecond, sysdate) ;
87: return(orderNumber);
88: exception
89: when others then
90: rollback;
87: return(orderNumber);
88: exception
89: when others then
90: rollback;
91: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
92: routineNameIn => 'getApprovalGroupMaxOrderNumber',
93: exceptionNumberIn => sqlcode,
94: exceptionStringIn => sqlerrm);
95: raise;
105: where
106: approval_group_id = approvalGroupIdIn and
107: application_id = applicationIdIn and
108: sysdate between start_date and
109: nvl(end_date - ame_util.oneSecond, sysdate) ;
110: return(orderNumber);
111: exception
112: when others then
113: rollback;
110: return(orderNumber);
111: exception
112: when others then
113: rollback;
114: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
115: routineNameIn => 'getApprovalGroupOrderNumber',
116: exceptionNumberIn => sqlcode,
117: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
118: messageNameIn => 'AME_400356_APPR_GROUP_ID_ERR',
113: rollback;
114: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
115: routineNameIn => 'getApprovalGroupOrderNumber',
116: exceptionNumberIn => sqlcode,
117: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
118: messageNameIn => 'AME_400356_APPR_GROUP_ID_ERR',
119: tokenNameOneIn => 'GROUPID',
120: tokenValueOneIn => to_char(approvalGroupIdIn))
121: || ' ' || sqlerrm);
130: from ame_approval_groups
131: where
132: approval_group_id = approvalGroupIdIn and
133: sysdate between start_date and
134: nvl(end_date - ame_util.oneSecond, sysdate) ;
135: return(description);
136: exception
137: when others then
138: rollback;
135: return(description);
136: exception
137: when others then
138: rollback;
139: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
140: routineNameIn => 'getDescription',
141: exceptionNumberIn => sqlcode,
142: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
143: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
138: rollback;
139: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
140: routineNameIn => 'getDescription',
141: exceptionNumberIn => sqlcode,
142: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
143: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
144: tokenNameOneIn => 'NAME',
145: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
146: ||' '|| sqlerrm);
155: from ame_approval_groups
156: where
157: upper(name) = upper(nameIn) and
158: sysdate between start_date and
159: nvl(end_date - ame_util.oneSecond, sysdate) ;
160: return(approvalGroupId);
161: exception
162: when others then
163: rollback;
160: return(approvalGroupId);
161: exception
162: when others then
163: rollback;
164: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
165: routineNameIn => 'getId',
166: exceptionNumberIn => sqlcode,
167: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
168: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
163: rollback;
164: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
165: routineNameIn => 'getId',
166: exceptionNumberIn => sqlcode,
167: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
168: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
169: tokenNameOneIn => 'NAME',
170: tokenValueOneIn => nameIn)
171: || ' ' || sqlerrm);
180: from ame_approval_group_items
181: where
182: approval_group_item_id = approvalGroupItemIdIn and
183: sysdate between start_date and
184: nvl(end_date - ame_util.oneSecond, sysdate) ;
185: return(approvalGroupId);
186: exception
187: when others then
188: rollback;
185: return(approvalGroupId);
186: exception
187: when others then
188: rollback;
189: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
190: routineNameIn => 'getItemApprovalGroupId',
191: exceptionNumberIn => sqlcode,
192: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
193: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
188: rollback;
189: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
190: routineNameIn => 'getItemApprovalGroupId',
191: exceptionNumberIn => sqlcode,
192: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
193: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
194: tokenNameOneIn => 'GROUPITEMID',
195: tokenValueOneIn => approvalGroupItemIdIn)
196: || ' ' || sqlerrm);
208: where
209: upper(parameter) = upper(parameterIn) and
210: (upper(parameter_name) = upper(parameterNameIn)) and
211: sysdate between start_date and
212: nvl(end_date - ame_util.oneSecond, sysdate) ;
213: return(itemId);
214: exception
215: when others then
216: rollback;
213: return(itemId);
214: exception
215: when others then
216: rollback;
217: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
218: routineNameIn => 'getItemId',
219: exceptionNumberIn => sqlcode,
220: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
221: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
216: rollback;
217: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
218: routineNameIn => 'getItemId',
219: exceptionNumberIn => sqlcode,
220: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
221: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
222: tokenNameOneIn => 'NAME',
223: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
224: || ' ' || sqlerrm);
233: from ame_approval_group_items
234: where
235: approval_group_item_id = approvalGroupItemIdIn and
236: sysdate between start_date and
237: nvl(end_date - ame_util.oneSecond, sysdate) ;
238: return(parameter);
239: exception
240: when others then
241: rollback;
238: return(parameter);
239: exception
240: when others then
241: rollback;
242: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
243: routineNameIn => 'getItemParameter',
244: exceptionNumberIn => sqlcode,
245: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
246: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
241: rollback;
242: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
243: routineNameIn => 'getItemParameter',
244: exceptionNumberIn => sqlcode,
245: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
246: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
247: tokenNameOneIn => 'GROUPITEMID',
248: tokenValueOneIn => approvalGroupItemIdIn)
249: || ' ' || sqlerrm);
258: from ame_approval_group_items
259: where
260: approval_group_item_id = approvalGroupItemIdIn and
261: sysdate between start_date and
262: nvl(end_date - ame_util.oneSecond, sysdate);
263: return(parameterName);
264: exception
265: when others then
266: rollback;
263: return(parameterName);
264: exception
265: when others then
266: rollback;
267: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
268: routineNameIn => 'getItemParameterName',
269: exceptionNumberIn => sqlcode,
270: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
271: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
266: rollback;
267: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
268: routineNameIn => 'getItemParameterName',
269: exceptionNumberIn => sqlcode,
270: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
271: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
272: tokenNameOneIn => 'GROUPITEMID',
273: tokenValueOneIn => approvalGroupItemIdIn)
274: || ' ' || sqlerrm);
278: function getName(approvalGroupIdIn in integer,
279: effectiveDateIn in date default sysdate) return varchar2 as
280: name ame_approval_groups.name%type;
281: begin
282: if(approvalGroupIdIn = ame_util.nullInsertionGroupOrChainId) then
283: return('''no approval group'' and ''no chain of authority''');
284: end if;
285: select name
286: into name
287: from ame_approval_groups
288: where
289: approval_group_id = approvalGroupIdIn and
290: nvl(effectiveDateIn, sysdate) between start_date and
291: nvl(end_date - ame_util.oneSecond, sysdate) ;
292: return(name);
293: exception
294: when others then
295: rollback;
292: return(name);
293: exception
294: when others then
295: rollback;
296: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
297: routineNameIn => 'getName',
298: exceptionNumberIn => sqlcode,
299: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
300: messageNameIn => 'AME_400356_APPR_GROUP_ID_ERR',
295: rollback;
296: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
297: routineNameIn => 'getName',
298: exceptionNumberIn => sqlcode,
299: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
300: messageNameIn => 'AME_400356_APPR_GROUP_ID_ERR',
301: tokenNameOneIn => 'GROUPID',
302: tokenValueOneIn => to_char(approvalGroupIdIn))
303: || ' ' || sqlerrm);
306: end getName;
307: function getQueryString(approvalGroupIdIn in integer,
308: effectiveDateIn in date default sysdate) return varchar2 as
309: errorCode integer;
310: errorMessage ame_util.longestStringType;
311: queryString ame_approval_groups.query_string%type;
312: begin
313: select query_string
314: into queryString
315: from ame_approval_groups
316: where
317: approval_group_id = approvalGroupIdIn and
318: nvl(effectiveDateIn, sysdate) between start_date and
319: nvl(end_date - ame_util.oneSecond, sysdate) ;
320: return(queryString);
321: exception
322: when no_data_found then
323: rollback;
322: when no_data_found then
323: rollback;
324: errorCode := -20001;
325: errorMessage :=
326: ame_util.getMessage(applicationShortNameIn => 'PER',
327: messageNameIn => 'AME_400194_APG_NO_USAGE',
328: tokenNameOneIn => 'APPROVAL_GROUP',
329: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
330: effectiveDateIn => effectiveDateIn));
327: messageNameIn => 'AME_400194_APG_NO_USAGE',
328: tokenNameOneIn => 'APPROVAL_GROUP',
329: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
330: effectiveDateIn => effectiveDateIn));
331: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
332: routineNameIn => 'getQueryString',
333: exceptionNumberIn => errorCode,
334: exceptionStringIn => errorMessage);
335: raise_application_error(errorCode,
336: errorMessage);
337: return(null);
338: when others then
339: rollback;
340: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
341: routineNameIn => 'getQueryString',
342: exceptionNumberIn => sqlcode,
343: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
344: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
339: rollback;
340: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
341: routineNameIn => 'getQueryString',
342: exceptionNumberIn => sqlcode,
343: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
344: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
345: tokenNameOneIn => 'NAME',
346: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
347: effectiveDateIn => effectiveDateIn))
356: into isStatic
357: from ame_approval_groups
358: where approval_group_id = approvalGroupIdIn and
359: sysdate between start_date and
360: nvl(end_date - ame_util.oneSecond, sysdate) ;
361: return(isStatic);
362: exception
363: when others then
364: rollback;
361: return(isStatic);
362: exception
363: when others then
364: rollback;
365: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
366: routineNameIn => 'getIsStatic',
367: exceptionNumberIn => sqlcode,
368: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
369: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
364: rollback;
365: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
366: routineNameIn => 'getIsStatic',
367: exceptionNumberIn => sqlcode,
368: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
369: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
370: tokenNameOneIn => 'NAME',
371: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
372: || ' ' || sqlerrm);
384: where
385: approval_group_id = approvalGroupIdIn and
386: application_id = applicationIdIn and
387: sysdate between start_date and
388: nvl(end_date - ame_util.oneSecond, sysdate) ;
389: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
390: return(stringStartDate);
391: exception
392: when others then
385: approval_group_id = approvalGroupIdIn and
386: application_id = applicationIdIn and
387: sysdate between start_date and
388: nvl(end_date - ame_util.oneSecond, sysdate) ;
389: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
390: return(stringStartDate);
391: exception
392: when others then
393: rollback;
390: return(stringStartDate);
391: exception
392: when others then
393: rollback;
394: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
395: routineNameIn => 'getChildVersionStartDate',
396: exceptionNumberIn => sqlcode,
397: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
398: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
393: rollback;
394: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
395: routineNameIn => 'getChildVersionStartDate',
396: exceptionNumberIn => sqlcode,
397: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
398: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
399: tokenNameOneIn => 'NAME',
400: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
401: || ' ' || sqlerrm);
411: from ame_approval_groups
412: where
413: approval_group_id = approvalGroupIdIn and
414: sysdate between start_date and
415: nvl(end_date - ame_util.oneSecond, sysdate) ;
416: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
417: return(stringStartDate);
418: exception
419: when others then
412: where
413: approval_group_id = approvalGroupIdIn and
414: sysdate between start_date and
415: nvl(end_date - ame_util.oneSecond, sysdate) ;
416: stringStartDate := ame_util.versionDateToString(dateIn => startDate);
417: return(stringStartDate);
418: exception
419: when others then
420: rollback;
417: return(stringStartDate);
418: exception
419: when others then
420: rollback;
421: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
422: routineNameIn => 'getParentVersionStartDate',
423: exceptionNumberIn => sqlcode,
424: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
425: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
420: rollback;
421: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
422: routineNameIn => 'getParentVersionStartDate',
423: exceptionNumberIn => sqlcode,
424: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
425: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
426: tokenNameOneIn => 'NAME',
427: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
428: || ' ' || sqlerrm);
437: from ame_approval_group_items
438: where
439: approval_group_item_id = approvalGroupItemIdIn and
440: sysdate between start_date and
441: nvl(end_date - ame_util.oneSecond, sysdate) ;
442: return(orderNumber);
443: exception
444: when others then
445: rollback;
442: return(orderNumber);
443: exception
444: when others then
445: rollback;
446: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
447: routineNameIn => 'getItemOrderNumber',
448: exceptionNumberIn => sqlcode,
449: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
450: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
445: rollback;
446: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
447: routineNameIn => 'getItemOrderNumber',
448: exceptionNumberIn => sqlcode,
449: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
450: messageNameIn => 'AME_400355_APPR_GRPITEMID_ERR',
451: tokenNameOneIn => 'GROUPITEMID',
452: tokenValueOneIn => approvalGroupItemIdIn)
453: || ' ' || sqlerrm);
463: from ame_approval_group_items
464: where
465: approval_group_id = approvalGroupIdIn and
466: sysdate between start_date and
467: nvl(end_date - ame_util.oneSecond, sysdate);
468: /* If new group member, need to include an additional order number
469: within the select list. */
470: if(newGroupMemberIn) then
471: return(orderCount + 1);
474: return(orderCount);
475: exception
476: when others then
477: rollback;
478: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
479: routineNameIn => 'getOrderNumberCount',
480: exceptionNumberIn => sqlcode,
481: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
482: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
477: rollback;
478: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
479: routineNameIn => 'getOrderNumberCount',
480: exceptionNumberIn => sqlcode,
481: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
482: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
483: tokenNameOneIn => 'NAME',
484: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
485: || ' ' || sqlerrm);
496: where
497: approval_group_id = approvalGroupIdIn and
498: application_id = applicationIdIn and
499: sysdate between start_date and
500: nvl(end_date - ame_util.oneSecond, sysdate) ;
501: return(votingRegime);
502: exception
503: when others then
504: rollback;
501: return(votingRegime);
502: exception
503: when others then
504: rollback;
505: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
506: routineNameIn => 'getVotingRegime',
507: exceptionNumberIn => sqlcode,
508: exceptionStringIn => sqlerrm);
509: raise;
518: from ame_approval_group_items
519: where
520: approval_group_id = approvalGroupIdIn and
521: sysdate between start_date and
522: nvl(end_date - ame_util.oneSecond, sysdate) ;
523: tempGroupId integer;
524: begin
525: for tempGroup in groupMemberCursor(approvalGroupIdIn => groupIdIn) loop
526: if(tempGroup.parameter_name = ame_util.approverOamGroupId) then
522: nvl(end_date - ame_util.oneSecond, sysdate) ;
523: tempGroupId integer;
524: begin
525: for tempGroup in groupMemberCursor(approvalGroupIdIn => groupIdIn) loop
526: if(tempGroup.parameter_name = ame_util.approverOamGroupId) then
527: tempGroupId := to_number(tempGroup.parameter);
528: if(tempGroupId = possiblyNestedGroupIdIn) then
529: return(true);
530: elsif(groupIsInGroup(groupIdIn => tempGroupId,
536: return(false);
537: exception
538: when others then
539: rollback;
540: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
541: routineNameIn => 'groupIsInGroup',
542: exceptionNumberIn => sqlcode,
543: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
544: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
539: rollback;
540: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
541: routineNameIn => 'groupIsInGroup',
542: exceptionNumberIn => sqlcode,
543: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
544: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
545: tokenNameOneIn => 'NAME',
546: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
547: || ' ' || sqlerrm);
564: and (descriptionIn is null or upper(description) = upper(descriptionIn))
565: and is_static = isStaticIn
566: and (queryStringIn is null or upper(query_string) = upper(queryStringIn))
567: and sysdate between ame_approval_groups.start_date
568: and nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate);
569: if(tempCount = 0) then
570: return(true);
571: else
572: return(false);
573: end if;
574: exception
575: when others then
576: rollback;
577: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
578: routineNameIn => 'hasGroupChanged2',
579: exceptionNumberIn => sqlcode,
580: exceptionStringIn => sqlerrm);
581: raise;
597: begin
598: oldOrderNumberUnique := orderNumberUnique(applicationIdIn => applicationIdIn,
599: orderNumberIn => orderNumberIn);
600:
601: /* If the old order number is not unique, orderNumberUniqueIn = ame_util.yes,
602: then group has been updated. */
603: if(not oldOrderNumberUnique and orderNumberUniqueIn = ame_util.yes) then
604: groupHasBeenUpdated := true;
605: else
599: orderNumberIn => orderNumberIn);
600:
601: /* If the old order number is not unique, orderNumberUniqueIn = ame_util.yes,
602: then group has been updated. */
603: if(not oldOrderNumberUnique and orderNumberUniqueIn = ame_util.yes) then
604: groupHasBeenUpdated := true;
605: else
606: groupHasBeenUpdated := false;
607: end if;
620: (descriptionIn is null or upper(description) = upper(descriptionIn)) and
621: is_static = isStaticIn and
622: (queryStringIn is null or upper(query_string) = upper(queryStringIn)) and
623: sysdate between ame_approval_groups.start_date and
624: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
625: sysdate between ame_approval_group_config.start_date and
626: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate);
627: if(tempCount = 0 or groupHasBeenUpdated) then
628: return(true);
622: (queryStringIn is null or upper(query_string) = upper(queryStringIn)) and
623: sysdate between ame_approval_groups.start_date and
624: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
625: sysdate between ame_approval_group_config.start_date and
626: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate);
627: if(tempCount = 0 or groupHasBeenUpdated) then
628: return(true);
629: else
630: return(false);
631: end if;
632: exception
633: when others then
634: rollback;
635: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
636: routineNameIn => 'hasGroupChanged',
637: exceptionNumberIn => sqlcode,
638: exceptionStringIn => sqlerrm);
639: raise;
651: where
652: ame_actions.parameter = to_char(approvalGroupIdIn) and
653: ame_action_usages.action_id = ame_actions.action_id and
654: ame_actions.action_type_id = ame_action_types.action_type_id and
655: ame_action_types.name in (ame_util.preApprovalTypeName,
656: ame_util.postApprovalTypeName,
657: ame_util.groupChainApprovalTypeName) and
658: sysdate between ame_actions.start_date and
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
652: ame_actions.parameter = to_char(approvalGroupIdIn) and
653: ame_action_usages.action_id = ame_actions.action_id and
654: ame_actions.action_type_id = ame_action_types.action_type_id and
655: ame_action_types.name in (ame_util.preApprovalTypeName,
656: ame_util.postApprovalTypeName,
657: ame_util.groupChainApprovalTypeName) and
658: sysdate between ame_actions.start_date and
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
660: sysdate between ame_action_types.start_date and
653: ame_action_usages.action_id = ame_actions.action_id and
654: ame_actions.action_type_id = ame_action_types.action_type_id and
655: ame_action_types.name in (ame_util.preApprovalTypeName,
656: ame_util.postApprovalTypeName,
657: ame_util.groupChainApprovalTypeName) and
658: sysdate between ame_actions.start_date and
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
660: sysdate between ame_action_types.start_date and
661: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
655: ame_action_types.name in (ame_util.preApprovalTypeName,
656: ame_util.postApprovalTypeName,
657: ame_util.groupChainApprovalTypeName) and
658: sysdate between ame_actions.start_date and
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
660: sysdate between ame_action_types.start_date and
661: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
662: ((sysdate between ame_action_usages.start_date and
663: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
657: ame_util.groupChainApprovalTypeName) and
658: sysdate between ame_actions.start_date and
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
660: sysdate between ame_action_types.start_date and
661: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
662: ((sysdate between ame_action_usages.start_date and
663: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
664: (sysdate < ame_action_usages.start_date and
665: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
659: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
660: sysdate between ame_action_types.start_date and
661: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
662: ((sysdate between ame_action_usages.start_date and
663: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
664: (sysdate < ame_action_usages.start_date and
665: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
666: ame_action_usages.start_date + ame_util.oneSecond)));
667: if(useCount > 0) then
662: ((sysdate between ame_action_usages.start_date and
663: nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
664: (sysdate < ame_action_usages.start_date and
665: ame_action_usages.start_date < nvl(ame_action_usages.end_date,
666: ame_action_usages.start_date + ame_util.oneSecond)));
667: if(useCount > 0) then
668: return(true);
669: end if;
670: return(false);
670: return(false);
671: exception
672: when others then
673: rollback;
674: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
675: routineNameIn => 'isInUse',
676: exceptionNumberIn => sqlcode,
677: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
678: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
673: rollback;
674: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
675: routineNameIn => 'isInUse',
676: exceptionNumberIn => sqlcode,
677: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
678: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
679: tokenNameOneIn => 'NAME',
680: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
681: || ' ' || sqlerrm);
690: into isStatic
691: from ame_approval_groups
692: where approval_group_id = approvalGroupIdIn and
693: nvl(effectiveDateIn, sysdate) between start_date and
694: nvl(end_date - ame_util.oneSecond, sysdate);
695: if(isStatic = ame_util.booleanTrue) then
696: return(true);
697: end if;
698: return(false);
691: from ame_approval_groups
692: where approval_group_id = approvalGroupIdIn and
693: nvl(effectiveDateIn, sysdate) between start_date and
694: nvl(end_date - ame_util.oneSecond, sysdate);
695: if(isStatic = ame_util.booleanTrue) then
696: return(true);
697: end if;
698: return(false);
699: exception
698: return(false);
699: exception
700: when others then
701: rollback;
702: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
703: routineNameIn => 'isStatic',
704: exceptionNumberIn => sqlcode,
705: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
706: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
701: rollback;
702: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
703: routineNameIn => 'isStatic',
704: exceptionNumberIn => sqlcode,
705: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
706: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
707: tokenNameOneIn => 'NAME',
708: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
709: effectiveDateIn => effectiveDateIn))
721: where
722: approval_group_id = approvalGroupIdIn and
723: order_number = orderNumberIn and
724: sysdate between start_date and
725: nvl(end_date - ame_util.oneSecond, sysdate);
726: if(tempCount > 1) then
727: return(false);
728: else
729: return(true);
730: end if;
731: exception
732: when others then
733: rollback;
734: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
735: routineNameIn => 'itemOrderNumberUnique',
736: exceptionNumberIn => sqlcode,
737: exceptionStringIn => sqlerrm);
738: raise;
754: currentUserId integer;
755: descriptionLengthException exception;
756: endDate date;
757: errorCode integer;
758: errorMessage ame_util.longestStringType;
759: nameLengthException exception;
760: nullNameDescException exception;
761: parentVersionStartDate date;
762: processingDate date;
773: where
774: (approvalGroupIdIn is null or approval_group_id <> approvalGroupIdIn) and
775: upper(name) = upper(nameIn) and
776: sysdate between start_date and
777: nvl(end_date - ame_util.oneSecond, sysdate);
778: if approvalGroupId is not null then
779: raise_application_error(-20001,
780: ame_util.getMessage(applicationShortNameIn => 'PER',
781: messageNameIn => 'AME_400195_APG_ALRDY_EXISTS'));
776: sysdate between start_date and
777: nvl(end_date - ame_util.oneSecond, sysdate);
778: if approvalGroupId is not null then
779: raise_application_error(-20001,
780: ame_util.getMessage(applicationShortNameIn => 'PER',
781: messageNameIn => 'AME_400195_APG_ALRDY_EXISTS'));
782: end if;
783: exception
784: when no_data_found then null;
782: end if;
783: exception
784: when no_data_found then null;
785: end;
786: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_groups',
787: columnNameIn => 'name',
788: argumentIn => nameIn)) then
789: raise nameLengthException;
790: end if;
787: columnNameIn => 'name',
788: argumentIn => nameIn)) then
789: raise nameLengthException;
790: end if;
791: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_groups',
792: columnNameIn => 'description',
793: argumentIn => descriptionIn)) then
794: raise descriptionLengthException;
795: end if;
798: including the new version, should. This is a failsafe way to check
799: whether previous versions of an already end-dated object had
800: created_by = 1.
801: */
802: currentUserId := ame_util.getCurrentUserId;
803: if(approvalGroupIdIn is null) then
804: createdBy := currentUserId;
805: select ame_approval_groups_s.nextval into approvalGroupId from dual;
806: else
809: into tempCount
810: from ame_approval_groups
811: where
812: approval_group_id = approvalGroupIdIn and
813: created_by = ame_util.seededDataCreatedById;
814: if(tempCount > 0) then
815: createdBy := ame_util.seededDataCreatedById;
816: else
817: createdBy := currentUserId;
811: where
812: approval_group_id = approvalGroupIdIn and
813: created_by = ame_util.seededDataCreatedById;
814: if(tempCount > 0) then
815: createdBy := ame_util.seededDataCreatedById;
816: else
817: createdBy := currentUserId;
818: end if;
819: end if;
823: if(approvalGroupIdIn is null or updateActionIn) then
824: for i in 1..3 loop
825: if i = 1 then
826: actionTypeId := ame_action_pkg.getPreApprovalActionTypeId;
827: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_PRE_APPROVAL') || ' ' ||nameIn;
828: parentVersionStartDate :=
829: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
830: elsif i = 2 then
831: actionTypeId := ame_action_pkg.getPostApprovalActionTypeId;
825: if i = 1 then
826: actionTypeId := ame_action_pkg.getPreApprovalActionTypeId;
827: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_PRE_APPROVAL') || ' ' ||nameIn;
828: parentVersionStartDate :=
829: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
830: elsif i = 2 then
831: actionTypeId := ame_action_pkg.getPostApprovalActionTypeId;
832: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_POST_APPROVAL') || ' ' ||nameIn;
833: parentVersionStartDate :=
828: parentVersionStartDate :=
829: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
830: elsif i = 2 then
831: actionTypeId := ame_action_pkg.getPostApprovalActionTypeId;
832: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_POST_APPROVAL') || ' ' ||nameIn;
833: parentVersionStartDate :=
834: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
835: else
836: actionTypeId := ame_action_pkg.getGroupChainActionTypeId;
830: elsif i = 2 then
831: actionTypeId := ame_action_pkg.getPostApprovalActionTypeId;
832: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_POST_APPROVAL') || ' ' ||nameIn;
833: parentVersionStartDate :=
834: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
835: else
836: actionTypeId := ame_action_pkg.getGroupChainActionTypeId;
837: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_APPROVAL') || ' ' ||nameIn;
838: parentVersionStartDate :=
833: parentVersionStartDate :=
834: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
835: else
836: actionTypeId := ame_action_pkg.getGroupChainActionTypeId;
837: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_APPROVAL') || ' ' ||nameIn;
838: parentVersionStartDate :=
839: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
840: end if;
841: if(updateActionIn) then
835: else
836: actionTypeId := ame_action_pkg.getGroupChainActionTypeId;
837: actionDescription := ame_util.getLabel(ame_util.perFndAppId,'AME_REQ_APPROVAL') || ' ' ||nameIn;
838: parentVersionStartDate :=
839: ame_util.versionStringToDate(stringDateIn => ame_action_pkg.getParentVersionStartDate(actionTypeIdIn => actionTypeId));
840: end if;
841: if(updateActionIn) then
842: select action_id into actionId
843: from ame_actions
844: where
845: parameter = to_char(approvalGroupId) and
846: action_type_id = actionTypeId and
847: sysdate between start_date and
848: nvl(end_date - ame_util.oneSecond, sysdate)
849: for update of end_date;
850: update ame_actions
851: set
852: last_updated_by = currentUserId,
856: where
857: parameter = to_char(approvalGroupId) and
858: action_type_id = actionTypeId and
859: processingDate between start_date and
860: nvl(end_date - ame_util.oneSecond, processingDate) ;
861: actionId := ame_action_pkg.newAction(actionTypeIdIn => actionTypeId,
862: descriptionIn => actionDescription,
863: updateParentObjectIn => true,
864: parameterIn => approvalGroupId,
909: exception
910: when nameLengthException then
911: rollback;
912: errorCode := -20001;
913: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
914: messageNameIn => 'AME_400196_APG_NAME_LONG',
915: tokenNameOneIn => 'COLUMN_LENGTH',
916: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
917: columnNameIn => 'name'));
912: errorCode := -20001;
913: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
914: messageNameIn => 'AME_400196_APG_NAME_LONG',
915: tokenNameOneIn => 'COLUMN_LENGTH',
916: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
917: columnNameIn => 'name'));
918: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
919: routineNameIn => 'new',
920: exceptionNumberIn => errorCode,
914: messageNameIn => 'AME_400196_APG_NAME_LONG',
915: tokenNameOneIn => 'COLUMN_LENGTH',
916: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
917: columnNameIn => 'name'));
918: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
919: routineNameIn => 'new',
920: exceptionNumberIn => errorCode,
921: exceptionStringIn => errorMessage);
922: raise_application_error(errorCode,
924: return(null);
925: when descriptionLengthException then
926: rollback;
927: errorCode := -20001;
928: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
929: messageNameIn => 'AME_400197_APG_DESC_LONG',
930: tokenNameOneIn => 'COLUMN_LENGTH',
931: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
932: columnNameIn => 'description'));
927: errorCode := -20001;
928: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
929: messageNameIn => 'AME_400197_APG_DESC_LONG',
930: tokenNameOneIn => 'COLUMN_LENGTH',
931: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
932: columnNameIn => 'description'));
933: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
934: routineNameIn => 'new',
935: exceptionNumberIn => errorCode,
929: messageNameIn => 'AME_400197_APG_DESC_LONG',
930: tokenNameOneIn => 'COLUMN_LENGTH',
931: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
932: columnNameIn => 'description'));
933: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
934: routineNameIn => 'new',
935: exceptionNumberIn => errorCode,
936: exceptionStringIn => errorMessage);
937: raise_application_error(errorCode,
940: when nullNameDescException then
941: rollback;
942: errorCode := -20001;
943: errorMessage :=
944: ame_util.getMessage(applicationShortNameIn => 'PER',
945: messageNameIn => 'AME_400198_APG_NAME_DESC_ENT');
946: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
947: routineNameIn => 'new',
948: exceptionNumberIn => errorCode,
942: errorCode := -20001;
943: errorMessage :=
944: ame_util.getMessage(applicationShortNameIn => 'PER',
945: messageNameIn => 'AME_400198_APG_NAME_DESC_ENT');
946: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
947: routineNameIn => 'new',
948: exceptionNumberIn => errorCode,
949: exceptionStringIn => errorMessage);
950: raise_application_error(errorCode,
951: errorMessage);
952: return(null);
953: when others then
954: rollback;
955: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
956: routineNameIn => 'new',
957: exceptionNumberIn => sqlcode,
958: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
959: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
954: rollback;
955: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
956: routineNameIn => 'new',
957: exceptionNumberIn => sqlcode,
958: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
959: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
960: tokenNameOneIn => 'NAME',
961: tokenValueOneIn => nameIn)
962: || ' ' || sqlerrm);
979: from ame_approval_groups
980: where
981: approval_group_id = approvalGroupIdIn and
982: sysdate between start_date and
983: nvl(end_date - ame_util.oneSecond, sysdate)
984: for update;
985: approvalGroupItemId integer;
986: badNestedGroupException exception;
987: badOrderNumberException exception;
987: badOrderNumberException exception;
988: createdBy integer;
989: currentUserId integer;
990: errorCode integer;
991: errorMessage ame_util.longestStringType;
992: groupExistsException exception;
993: maxOrderNumber integer;
994: newOrderNumber integer;
995: objectVersionNoDataException exception;
992: groupExistsException exception;
993: maxOrderNumber integer;
994: newOrderNumber integer;
995: objectVersionNoDataException exception;
996: oldOrderNumberUnique ame_util.stringType;
997: orderNumberException exception;
998: parameter ame_approval_group_items.parameter%type;
999: parameterLengthException exception;
1000: parameterName ame_approval_group_items.parameter_name%type;
1020: approval_group_id = approvalGroupIdIn and
1021: upper(parameter) = upper(parameterIn) and
1022: (upper(parameter_name) = upper(parameterNameIn)) and
1023: sysdate between start_date and
1024: nvl(end_date - ame_util.oneSecond, sysdate) ;
1025: if(tempCount > 0) then
1026: raise groupExistsException;
1027: end if;
1028: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_group_items',
1024: nvl(end_date - ame_util.oneSecond, sysdate) ;
1025: if(tempCount > 0) then
1026: raise groupExistsException;
1027: end if;
1028: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_group_items',
1029: columnNameIn => 'parameter',
1030: argumentIn => parameterIn)) then
1031: raise parameterLengthException;
1032: end if;
1029: columnNameIn => 'parameter',
1030: argumentIn => parameterIn)) then
1031: raise parameterLengthException;
1032: end if;
1033: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_group_items',
1034: columnNameIn => 'parameter_name',
1035: argumentIn => parameterNameIn)) then
1036: raise parameterNameLengthException;
1037: end if;
1035: argumentIn => parameterNameIn)) then
1036: raise parameterNameLengthException;
1037: end if;
1038: /* actual work */
1039: currentUserId := ame_util.getCurrentUserId;
1040: if parentVersionStartDateIn = startDate then
1041: if(approvalGroupItemIdIn is null) then
1042: createdBy := currentUserId;
1043: select ame_approval_group_items_s.nextval into approvalGroupItemId from dual;
1047: into tempCount2
1048: from ame_approval_group_items
1049: where
1050: approval_group_item_id = approvalGroupItemId and
1051: created_by = ame_util.seededDataCreatedById;
1052: if(tempCount2 > 0) then
1053: createdBy := ame_util.seededDataCreatedById;
1054: else
1055: createdBy := currentUserId;
1049: where
1050: approval_group_item_id = approvalGroupItemId and
1051: created_by = ame_util.seededDataCreatedById;
1052: if(tempCount2 > 0) then
1053: createdBy := ame_util.seededDataCreatedById;
1054: else
1055: createdBy := currentUserId;
1056: end if;
1057: end if;
1056: end if;
1057: end if;
1058: if(ame_approval_group_pkg.itemOrderNumberUnique(orderNumberIn => oldOrderNumberIn,
1059: approvalGroupIdIn => approvalGroupIdIn)) then
1060: oldOrderNumberUnique := ame_util.yes;
1061: else
1062: oldOrderNumberUnique := ame_util.no;
1063: end if;
1064: updateOnlyGIModified := false;
1058: if(ame_approval_group_pkg.itemOrderNumberUnique(orderNumberIn => oldOrderNumberIn,
1059: approvalGroupIdIn => approvalGroupIdIn)) then
1060: oldOrderNumberUnique := ame_util.yes;
1061: else
1062: oldOrderNumberUnique := ame_util.no;
1063: end if;
1064: updateOnlyGIModified := false;
1065: if(oldOrderNumberIn is not null) then
1066: /* Item order number is getting changed. */
1066: /* Item order number is getting changed. */
1067: if(oldOrderNumberIn = newOrderNumberIn) then
1068: if(orderNumberUniqueIn = oldOrderNumberUnique) then
1069: updateOnlyGIModified := true; /* Order number not modified. */
1070: elsif(orderNumberUniqueIn = ame_util.yes) then
1071: /* Need to increment the order numbers to keep them in sequence. */
1072: incrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
1073: approvalGroupItemIdIn => approvalGroupItemIdIn,
1074: orderNumberIn => newOrderNumberIn);
1084: end_date = newEndDateIn
1085: where
1086: approval_group_item_id = approvalGroupItemIdIn and
1087: sysdate between start_date and
1088: nvl(end_date - ame_util.oneSecond, sysdate);
1089: if(oldOrderNumberUnique = ame_util.yes) then
1090: decrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
1091: orderNumberIn => oldOrderNumberIn);
1092: if(newOrderNumberIn > oldOrderNumberIn)then
1085: where
1086: approval_group_item_id = approvalGroupItemIdIn and
1087: sysdate between start_date and
1088: nvl(end_date - ame_util.oneSecond, sysdate);
1089: if(oldOrderNumberUnique = ame_util.yes) then
1090: decrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
1091: orderNumberIn => oldOrderNumberIn);
1092: if(newOrderNumberIn > oldOrderNumberIn)then
1093: newOrderNumber := (newOrderNumberIn - 1);
1096: end if;
1097: else
1098: newOrderNumber := newOrderNumberIn;
1099: end if;
1100: if(orderNumberUniqueIn = ame_util.yes) then
1101: incrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
1102: approvalGroupItemIdIn => approvalGroupItemIdIn,
1103: orderNumberIn => newOrderNumber);
1104: end if;
1107: the group identified by parameterIn P. If so, we would have a loop in
1108: the groups: P contains G, and G would contain P, which would then
1109: contain G, . . . Also check whether P is already in G.
1110: */
1111: if(parameterNameIn = ame_util.approverOamGroupId and
1112: (approvalGroupIdIn = to_number(parameterIn) or
1113: groupIsInGroup(groupIdIn => to_number(parameterIn),
1114: possiblyNestedGroupIdIn => approvalGroupIdIn) or
1115: groupIsInGroup(groupIdIn => approvalGroupIdIn,
1154: end_date = newEndDateIn
1155: where
1156: approval_group_item_id = approvalGroupItemIdIn and
1157: sysdate between start_date and
1158: nvl(end_date - ame_util.oneSecond, sysdate);
1159: end if;
1160: /*
1161: Check whether the group identified by approvalGroupIdIn G is nested in
1162: the group identified by parameterIn P. If so, we would have a loop in
1162: the group identified by parameterIn P. If so, we would have a loop in
1163: the groups: P contains G, and G would contain P, which would then
1164: contain G, . . . Also check whether P is already in G.
1165: */
1166: if(parameterNameIn = ame_util.approverOamGroupId and
1167: (approvalGroupIdIn = to_number(parameterIn) or
1168: groupIsInGroup(groupIdIn => to_number(parameterIn),
1169: possiblyNestedGroupIdIn => approvalGroupIdIn) or
1170: groupIsInGroup(groupIdIn => approvalGroupIdIn,
1198: end if;
1199: maxOrderNumber :=
1200: ame_approval_group_pkg.getApprovalGroupItemMaxOrdNum(approvalGroupIdIn => approvalGroupIdIn);
1201: if(oldOrderNumberIn is null) then
1202: if(orderNumberUniqueIn = ame_util.yes) then
1203: if(newOrderNumberIn <> (maxOrderNumber + 1)) then
1204: incrementGroupItemOrderNumbers(approvalGroupItemIdIn => approvalGroupItemId,
1205: approvalGroupIdIn => approvalGroupIdIn,
1206: orderNumberIn => newOrderNumberIn);
1214: end if;
1215: return(approvalGroupItemId);
1216: else
1217: close startDateCursor;
1218: raise ame_util.objectVersionException;
1219: end if;
1220: exception
1221: when ame_util.objectVersionException then
1222: rollback;
1217: close startDateCursor;
1218: raise ame_util.objectVersionException;
1219: end if;
1220: exception
1221: when ame_util.objectVersionException then
1222: rollback;
1223: if(startDateCursor%isOpen) then
1224: close startDateCursor;
1225: end if;
1224: close startDateCursor;
1225: end if;
1226: errorCode := -20001;
1227: errorMessage :=
1228: ame_util.getMessage(applicationShortNameIn => 'PER',
1229: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1230: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1231: routineNameIn => 'newApprovalGroupItem',
1232: exceptionNumberIn => errorCode,
1226: errorCode := -20001;
1227: errorMessage :=
1228: ame_util.getMessage(applicationShortNameIn => 'PER',
1229: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1230: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1231: routineNameIn => 'newApprovalGroupItem',
1232: exceptionNumberIn => errorCode,
1233: exceptionStringIn => errorMessage);
1234: raise_application_error(errorCode,
1237: when badNestedGroupException then
1238: rollback;
1239: errorCode := -20001;
1240: errorMessage :=
1241: ame_util.getMessage(applicationShortNameIn => 'PER',
1242: messageNameIn => 'AME_400199_APG_NEST_CONTAINS');
1243: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1244: routineNameIn => 'newApprovalGroupItem',
1245: exceptionNumberIn => errorCode,
1239: errorCode := -20001;
1240: errorMessage :=
1241: ame_util.getMessage(applicationShortNameIn => 'PER',
1242: messageNameIn => 'AME_400199_APG_NEST_CONTAINS');
1243: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1244: routineNameIn => 'newApprovalGroupItem',
1245: exceptionNumberIn => errorCode,
1246: exceptionStringIn => errorMessage);
1247: raise_application_error(errorCode,
1250: when groupExistsException then
1251: rollback;
1252: errorCode := -20001;
1253: errorMessage :=
1254: ame_util.getMessage(applicationShortNameIn => 'PER',
1255: messageNameIn => 'AME_400200_APG_MEMBER_EXISTS');
1256: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1257: routineNameIn => 'newApprovalGroupItem',
1258: exceptionNumberIn => errorCode,
1252: errorCode := -20001;
1253: errorMessage :=
1254: ame_util.getMessage(applicationShortNameIn => 'PER',
1255: messageNameIn => 'AME_400200_APG_MEMBER_EXISTS');
1256: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1257: routineNameIn => 'newApprovalGroupItem',
1258: exceptionNumberIn => errorCode,
1259: exceptionStringIn => errorMessage);
1260: raise_application_error(errorCode,
1266: close startDateCursor;
1267: end if;
1268: errorCode := -20001;
1269: errorMessage :=
1270: ame_util.getMessage(applicationShortNameIn => 'PER',
1271: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1272: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1273: routineNameIn => 'newApprovalGroupItem',
1274: exceptionNumberIn => errorCode,
1268: errorCode := -20001;
1269: errorMessage :=
1270: ame_util.getMessage(applicationShortNameIn => 'PER',
1271: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1272: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1273: routineNameIn => 'newApprovalGroupItem',
1274: exceptionNumberIn => errorCode,
1275: exceptionStringIn => errorMessage);
1276: raise_application_error(errorCode,
1279: when parameterLengthException then
1280: rollback;
1281: errorCode := -20001;
1282: errorMessage :=
1283: ame_util.getMessage(applicationShortNameIn => 'PER',
1284: messageNameIn => 'AME_400201_APG_PAR_MEM_LONG',
1285: tokenNameOneIn => 'COLUMN_LENGTH',
1286: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1287: columnNameIn => 'parameter'));
1282: errorMessage :=
1283: ame_util.getMessage(applicationShortNameIn => 'PER',
1284: messageNameIn => 'AME_400201_APG_PAR_MEM_LONG',
1285: tokenNameOneIn => 'COLUMN_LENGTH',
1286: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1287: columnNameIn => 'parameter'));
1288: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1289: routineNameIn => 'newApprovalGroupItem',
1290: exceptionNumberIn => errorCode,
1284: messageNameIn => 'AME_400201_APG_PAR_MEM_LONG',
1285: tokenNameOneIn => 'COLUMN_LENGTH',
1286: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1287: columnNameIn => 'parameter'));
1288: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1289: routineNameIn => 'newApprovalGroupItem',
1290: exceptionNumberIn => errorCode,
1291: exceptionStringIn => errorMessage);
1292: raise_application_error(errorCode,
1295: when badOrderNumberException then
1296: rollback;
1297: errorCode := -20001;
1298: errorMessage :=
1299: ame_util.getMessage(applicationShortNameIn => 'PER',
1300: messageNameIn => 'AME_400202_APG_ORD_NAME_ARG');
1301: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1302: routineNameIn => 'newApprovalGroupItem',
1303: exceptionNumberIn => errorCode,
1297: errorCode := -20001;
1298: errorMessage :=
1299: ame_util.getMessage(applicationShortNameIn => 'PER',
1300: messageNameIn => 'AME_400202_APG_ORD_NAME_ARG');
1301: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1302: routineNameIn => 'newApprovalGroupItem',
1303: exceptionNumberIn => errorCode,
1304: exceptionStringIn => errorMessage);
1305: raise_application_error(errorCode,
1308: when parameterNameLengthException then
1309: rollback;
1310: errorCode := -20001;
1311: errorMessage :=
1312: ame_util.getMessage(applicationShortNameIn => 'PER',
1313: messageNameIn => 'AME_400203_APG_PAR_GRP_LONG',
1314: tokenNameOneIn => 'COLUMN_LENGTH',
1315: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1316: columnNameIn => 'parameter_name'));
1311: errorMessage :=
1312: ame_util.getMessage(applicationShortNameIn => 'PER',
1313: messageNameIn => 'AME_400203_APG_PAR_GRP_LONG',
1314: tokenNameOneIn => 'COLUMN_LENGTH',
1315: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1316: columnNameIn => 'parameter_name'));
1317: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1318: routineNameIn => 'newApprovalGroupItem',
1319: exceptionNumberIn => errorCode,
1313: messageNameIn => 'AME_400203_APG_PAR_GRP_LONG',
1314: tokenNameOneIn => 'COLUMN_LENGTH',
1315: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_group_items',
1316: columnNameIn => 'parameter_name'));
1317: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1318: routineNameIn => 'newApprovalGroupItem',
1319: exceptionNumberIn => errorCode,
1320: exceptionStringIn => errorMessage);
1321: raise_application_error(errorCode,
1328: 'you must give another approval group item the same order ' ||
1329: 'number, or give this approval group item the same order ' ||
1330: 'number as another.'; -- pa message
1331: /*
1332: ame_util.getMessage(applicationShortNameIn => 'PER',
1333: messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
1334: */
1335: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1336: routineNameIn => 'newApprovalGroupItem',
1331: /*
1332: ame_util.getMessage(applicationShortNameIn => 'PER',
1333: messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
1334: */
1335: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1336: routineNameIn => 'newApprovalGroupItem',
1337: exceptionNumberIn => errorCode,
1338: exceptionStringIn => errorMessage);
1339: raise_application_error(errorCode,
1342: rollback;
1343: if(startDateCursor%isOpen) then
1344: close startDateCursor;
1345: end if;
1346: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1347: routineNameIn => 'newApprovalGroupItem',
1348: exceptionNumberIn => sqlcode,
1349: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1350: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1345: end if;
1346: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1347: routineNameIn => 'newApprovalGroupItem',
1348: exceptionNumberIn => sqlcode,
1349: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1350: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1351: tokenNameOneIn => 'NAME',
1352: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1353: || ' ' || sqlerrm);
1364: where
1365: application_id = applicationIdIn and
1366: order_number = orderNumberIn and
1367: sysdate between start_date and
1368: nvl(end_date - ame_util.oneSecond, sysdate);
1369: if(tempCount > 1) then
1370: return(false);
1371: else
1372: return(true);
1373: end if;
1374: exception
1375: when others then
1376: rollback;
1377: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1378: routineNameIn => 'orderNumberUnique',
1379: exceptionNumberIn => sqlcode,
1380: exceptionStringIn => sqlerrm);
1381: raise;
1392: approvalGroupId integer;
1393: currentUserId integer;
1394: endDate date;
1395: errorCode integer;
1396: errorMessage ame_util.longestStringType;
1397: newStartDate date;
1398: objectVersionNoDataException exception;
1399: begin
1400: currentUserId := ame_util.getCurrentUserId;
1396: errorMessage ame_util.longestStringType;
1397: newStartDate date;
1398: objectVersionNoDataException exception;
1399: begin
1400: currentUserId := ame_util.getCurrentUserId;
1401: endDate := newVersionStartDateIn - ame_util.oneSecond;
1402: newStartDate := newVersionStartDateIn;
1403: update ame_approval_groups
1404: set
1397: newStartDate date;
1398: objectVersionNoDataException exception;
1399: begin
1400: currentUserId := ame_util.getCurrentUserId;
1401: endDate := newVersionStartDateIn - ame_util.oneSecond;
1402: newStartDate := newVersionStartDateIn;
1403: update ame_approval_groups
1404: set
1405: last_updated_by = currentUserId,
1408: end_date = endDate
1409: where
1410: approval_group_id = approvalGroupIdIn and
1411: sysdate between start_date and
1412: nvl(end_date - ame_util.oneSecond, sysdate) ;
1413: approvalGroupId := new(nameIn => nameIn,
1414: descriptionIn => descriptionIn,
1415: isStaticIn => isStaticIn,
1416: queryStringIn => queryStringIn,
1424: end if;
1425: exception
1426: when others then
1427: rollback;
1428: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1429: routineNameIn => 'change',
1430: exceptionNumberIn => sqlcode,
1431: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1432: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1427: rollback;
1428: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1429: routineNameIn => 'change',
1430: exceptionNumberIn => sqlcode,
1431: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1432: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1433: tokenNameOneIn => 'NAME',
1434: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1435: || ' ' || sqlerrm);
1453: from ame_approval_groups
1454: where
1455: approval_group_id = approvalGroupIdIn and
1456: sysdate between start_date and
1457: nvl(end_date - ame_util.oneSecond, sysdate)
1458: for update;
1459: cursor startDateCursor2 is
1460: select start_date
1461: from ame_approval_group_config
1462: where
1463: approval_group_id = approvalGroupIdIn and
1464: application_id = applicationIdIn and
1465: sysdate between start_date and
1466: nvl(end_date - ame_util.oneSecond, sysdate)
1467: for update;
1468: currentUserId integer;
1469: description ame_approval_groups.description%type;
1470: errorCode integer;
1467: for update;
1468: currentUserId integer;
1469: description ame_approval_groups.description%type;
1470: errorCode integer;
1471: errorMessage ame_util.longestStringType;
1472: name ame_approval_groups.name%type;
1473: objectVersionNoDataException exception;
1474: startDate date;
1475: startDate2 date;
1483: raise objectVersionNoDataException;
1484: end if;
1485: if(parentVersionStartDateIn <> startDate) then
1486: close startDateCursor;
1487: raise ame_util.objectVersionException;
1488: end if;
1489: open startDateCursor2;
1490: fetch startDateCursor2 into startDate2;
1491: if startDateCursor2%notfound then
1492: raise objectVersionNoDataException;
1493: end if;
1494: if(childVersionStartDateIn <> startDate2) then
1495: close startDateCursor2;
1496: raise ame_util.objectVersionException;
1497: end if;
1498: /* Get current values as necessary for update. */
1499: if(nameIn is null) then
1500: name := getName(approvalGroupIdIn => approvalGroupIdIn);
1544: if(finalizeIn) then
1545: commit;
1546: end if;
1547: exception
1548: when ame_util.objectVersionException then
1549: rollback;
1550: if(startDateCursor%isOpen) then
1551: close startDateCursor;
1552: end if;
1554: close startDateCursor2;
1555: end if;
1556: errorCode := -20001;
1557: errorMessage :=
1558: ame_util.getMessage(applicationShortNameIn => 'PER',
1559: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1560: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1561: routineNameIn => 'changeGroupAndConfig',
1562: exceptionNumberIn => errorCode,
1556: errorCode := -20001;
1557: errorMessage :=
1558: ame_util.getMessage(applicationShortNameIn => 'PER',
1559: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1560: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1561: routineNameIn => 'changeGroupAndConfig',
1562: exceptionNumberIn => errorCode,
1563: exceptionStringIn => errorMessage);
1564: raise_application_error(errorCode,
1572: close startDateCursor2;
1573: end if;
1574: errorCode := -20001;
1575: errorMessage :=
1576: ame_util.getMessage(applicationShortNameIn => 'PER',
1577: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1578: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1579: routineNameIn => 'changeGroupAndConfig',
1580: exceptionNumberIn => errorCode,
1574: errorCode := -20001;
1575: errorMessage :=
1576: ame_util.getMessage(applicationShortNameIn => 'PER',
1577: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1578: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1579: routineNameIn => 'changeGroupAndConfig',
1580: exceptionNumberIn => errorCode,
1581: exceptionStringIn => errorMessage);
1582: raise_application_error(errorCode,
1588: end if;
1589: if(startDateCursor2%isOpen) then
1590: close startDateCursor2;
1591: end if;
1592: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1593: routineNameIn => 'changeGroupAndConfig',
1594: exceptionNumberIn => sqlcode,
1595: exceptionStringIn => '(approval group ID ' ||
1596: approvalGroupIdIn||
1607: finalizeIn in boolean default false) as
1608: currentUserId integer;
1609: endDate date;
1610: errorCode integer;
1611: errorMessage ame_util.longestStringType;
1612: newOrderNumber integer;
1613: newStartDate date;
1614: oldOrderNumber integer;
1615: oldOrderNumberUnique ame_util.stringType;
1611: errorMessage ame_util.longestStringType;
1612: newOrderNumber integer;
1613: newStartDate date;
1614: oldOrderNumber integer;
1615: oldOrderNumberUnique ame_util.stringType;
1616: orderNumberException exception;
1617: updateOnlyAGModified boolean;
1618: begin
1619: oldOrderNumber := getApprovalGroupOrderNumber(applicationIdIn => applicationIdIn,
1619: oldOrderNumber := getApprovalGroupOrderNumber(applicationIdIn => applicationIdIn,
1620: approvalGroupIdIn => approvalGroupIdIn);
1621: if(ame_approval_group_pkg.orderNumberUnique(applicationIdIn => applicationIdIn,
1622: orderNumberIn => oldOrderNumber)) then
1623: oldOrderNumberUnique := ame_util.yes;
1624: else
1625: oldOrderNumberUnique := ame_util.no;
1626: end if;
1627: currentUserId := ame_util.getCurrentUserId;
1621: if(ame_approval_group_pkg.orderNumberUnique(applicationIdIn => applicationIdIn,
1622: orderNumberIn => oldOrderNumber)) then
1623: oldOrderNumberUnique := ame_util.yes;
1624: else
1625: oldOrderNumberUnique := ame_util.no;
1626: end if;
1627: currentUserId := ame_util.getCurrentUserId;
1628: endDate := newVersionStartDateIn;
1629: newStartDate := newVersionStartDateIn;
1623: oldOrderNumberUnique := ame_util.yes;
1624: else
1625: oldOrderNumberUnique := ame_util.no;
1626: end if;
1627: currentUserId := ame_util.getCurrentUserId;
1628: endDate := newVersionStartDateIn;
1629: newStartDate := newVersionStartDateIn;
1630: updateOnlyAGModified := false;
1631: /* Check if order number was modified */
1631: /* Check if order number was modified */
1632: if(oldOrderNumber = orderNumberIn) then
1633: if(orderNumberUniqueIn = oldOrderNumberUnique) then
1634: updateOnlyAGModified := true;
1635: elsif(orderNumberUniqueIn = ame_util.yes) then
1636: /* Need to adjust the order numbers to keep them in sequence. */
1637: incrementGroupOrderNumbers(applicationIdIn => applicationIdIn,
1638: approvalGroupIdIn => approvalGroupIdIn,
1639: orderNumberIn => orderNumberIn);
1651: where
1652: application_id = applicationIdIn and
1653: approval_group_id = approvalGroupIdIn and
1654: sysdate between start_date and
1655: nvl(end_date - ame_util.oneSecond, sysdate);
1656: if(oldOrderNumberUnique = ame_util.yes) then
1657: decrementGroupOrderNumbers(applicationIdIn => applicationIdIn,
1658: orderNumberIn => oldOrderNumber);
1659: if(orderNumberIn > oldOrderNumber)then
1652: application_id = applicationIdIn and
1653: approval_group_id = approvalGroupIdIn and
1654: sysdate between start_date and
1655: nvl(end_date - ame_util.oneSecond, sysdate);
1656: if(oldOrderNumberUnique = ame_util.yes) then
1657: decrementGroupOrderNumbers(applicationIdIn => applicationIdIn,
1658: orderNumberIn => oldOrderNumber);
1659: if(orderNumberIn > oldOrderNumber)then
1660: newOrderNumber := (orderNumberIn - 1);
1663: end if;
1664: else
1665: newOrderNumber := orderNumberIn;
1666: end if;
1667: if(orderNumberUniqueIn = ame_util.yes) then
1668: incrementGroupOrderNumbers(applicationIdIn => applicationIdIn,
1669: approvalGroupIdIn => approvalGroupIdIn,
1670: orderNumberIn => newOrderNumber);
1671: end if;
1702: where
1703: application_id = applicationIdIn and
1704: approval_group_id = approvalGroupIdIn and
1705: sysdate between start_date and
1706: nvl(end_date - ame_util.oneSecond, sysdate);
1707: insert into ame_approval_group_config(application_id,
1708: approval_group_id,
1709: voting_regime,
1710: order_number,
1738: 'you must give another approval group item the same order ' ||
1739: 'number, or give this approval group item the same order ' ||
1740: 'number as another.'; -- pa message
1741: /*
1742: ame_util.getMessage(applicationShortNameIn => 'PER',
1743: messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
1744: */
1745: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1746: routineNameIn => 'changeGroupConfig',
1741: /*
1742: ame_util.getMessage(applicationShortNameIn => 'PER',
1743: messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
1744: */
1745: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1746: routineNameIn => 'changeGroupConfig',
1747: exceptionNumberIn => errorCode,
1748: exceptionStringIn => errorMessage);
1749: raise_application_error(errorCode,
1749: raise_application_error(errorCode,
1750: errorMessage);
1751: when others then
1752: rollback;
1753: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1754: routineNameIn => 'changeGroupConfig',
1755: exceptionNumberIn => sqlcode,
1756: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1757: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1752: rollback;
1753: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1754: routineNameIn => 'changeGroupConfig',
1755: exceptionNumberIn => sqlcode,
1756: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1757: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1758: tokenNameOneIn => 'NAME',
1759: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1760: || ' ' || sqlerrm);
1772: from ame_approval_groups
1773: where
1774: approval_group_id = approvalGroupIdIn and
1775: sysdate between start_date and
1776: nvl(end_date - ame_util.oneSecond, sysdate)
1777: for update;
1778: approvalGroupId integer;
1779: approvalGroupItemId integer;
1780: currentUserId integer;
1779: approvalGroupItemId integer;
1780: currentUserId integer;
1781: endDate date;
1782: errorCode integer;
1783: errorMessage ame_util.longestStringType;
1784: groupDescription ame_approval_groups.description%type;
1785: groupIsStatic ame_approval_groups.is_static%type;
1786: groupName ame_approval_groups.name%type;
1787: groupQueryString ame_approval_groups.query_string%type;
1809: parameterName := getItemParameterName(approvalGroupItemIdIn => itemIdIn);
1810: else
1811: parameterName := parameterNameIn;
1812: end if;
1813: currentUserId := ame_util.getCurrentUserId;
1814: if(parentVersionStartDateIn = startDate) then
1815: oldOrderNumber := getItemOrderNumber(approvalGroupItemIdIn => itemIdIn);
1816: endDate := processingDate;
1817: newStartDate := processingDate;
1838: end_date = endDate
1839: where
1840: approval_group_id = approvalGroupIdIn and
1841: sysdate between start_date and
1842: nvl(end_date - ame_util.oneSecond, sysdate) ;
1843: approvalGroupId := new(nameIn => groupName,
1844: descriptionIn => groupDescription,
1845: isStaticIn => groupIsStatic,
1846: queryStringIn => groupQueryString,
1849: finalizeIn => true);
1850: /* function new does a commit */
1851: else
1852: close startDateCursor;
1853: raise ame_util.objectVersionException;
1854: end if;
1855: close startDateCursor;
1856: exception
1857: when objectVersionNoDataException then
1860: close startDateCursor;
1861: end if;
1862: errorCode := -20001;
1863: errorMessage :=
1864: ame_util.getMessage(applicationShortNameIn => 'PER',
1865: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1866: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1867: routineNameIn => 'changeApprovalGroupItem',
1868: exceptionNumberIn => errorCode,
1862: errorCode := -20001;
1863: errorMessage :=
1864: ame_util.getMessage(applicationShortNameIn => 'PER',
1865: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1866: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1867: routineNameIn => 'changeApprovalGroupItem',
1868: exceptionNumberIn => errorCode,
1869: exceptionStringIn => errorMessage);
1870: raise_application_error(errorCode,
1873: rollback;
1874: if(startDateCursor%isOpen) then
1875: close startDateCursor;
1876: end if;
1877: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1878: routineNameIn => 'changeApprovalGroupItem',
1879: exceptionNumberIn => sqlcode,
1880: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1881: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1876: end if;
1877: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1878: routineNameIn => 'changeApprovalGroupItem',
1879: exceptionNumberIn => sqlcode,
1880: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
1881: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
1882: tokenNameOneIn => 'NAME',
1883: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1884: || ' ' || sqlerrm);
1893: where
1894: approval_group_id = approvalGroupIdIn and
1895: order_number > orderNumberIn and
1896: sysdate between start_date and
1897: nvl(end_date - ame_util.oneSecond, sysdate)
1898: order by order_number;
1899: approvalGroupItemIds ame_util.idList;
1900: currentUserId integer;
1901: parameter ame_approval_group_items.parameter%type;
1895: order_number > orderNumberIn and
1896: sysdate between start_date and
1897: nvl(end_date - ame_util.oneSecond, sysdate)
1898: order by order_number;
1899: approvalGroupItemIds ame_util.idList;
1900: currentUserId integer;
1901: parameter ame_approval_group_items.parameter%type;
1902: parameterName ame_approval_group_items.parameter%type;
1903: processingDate date;
1900: currentUserId integer;
1901: parameter ame_approval_group_items.parameter%type;
1902: parameterName ame_approval_group_items.parameter%type;
1903: processingDate date;
1904: orderNumbers ame_util.idList;
1905: votingRegime ame_util.charType;
1906: begin
1907: currentUserId := ame_util.getCurrentUserId;
1908: processingDate := sysdate;
1901: parameter ame_approval_group_items.parameter%type;
1902: parameterName ame_approval_group_items.parameter%type;
1903: processingDate date;
1904: orderNumbers ame_util.idList;
1905: votingRegime ame_util.charType;
1906: begin
1907: currentUserId := ame_util.getCurrentUserId;
1908: processingDate := sysdate;
1909: open orderNumberCursor;
1903: processingDate date;
1904: orderNumbers ame_util.idList;
1905: votingRegime ame_util.charType;
1906: begin
1907: currentUserId := ame_util.getCurrentUserId;
1908: processingDate := sysdate;
1909: open orderNumberCursor;
1910: fetch orderNumberCursor bulk collect
1911: into approvalGroupItemIds, orderNumbers;
1923: end_date = processingDate
1924: where
1925: approval_group_item_id = approvalGroupItemIds(i) and
1926: sysdate between start_date and
1927: nvl(end_date - ame_util.oneSecond, sysdate);
1928: insert into ame_approval_group_items(approval_group_item_id,
1929: approval_group_id,
1930: parameter_name,
1931: parameter,
1955: end if;
1956: exception
1957: when others then
1958: rollback;
1959: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1960: routineNameIn => 'decrementGroupItemOrderNumbers',
1961: exceptionNumberIn => sqlcode,
1962: exceptionStringIn => sqlerrm);
1963: raise;
1971: where
1972: application_id = applicationIdIn and
1973: order_number > orderNumberIn and
1974: sysdate between start_date and
1975: nvl(end_date - ame_util.oneSecond, sysdate)
1976: order by order_number;
1977: approvalGroupIds ame_util.idList;
1978: currentUserId integer;
1979: orderNumbers ame_util.idList;
1973: order_number > orderNumberIn and
1974: sysdate between start_date and
1975: nvl(end_date - ame_util.oneSecond, sysdate)
1976: order by order_number;
1977: approvalGroupIds ame_util.idList;
1978: currentUserId integer;
1979: orderNumbers ame_util.idList;
1980: processingDate date;
1981: votingRegime ame_util.charType;
1975: nvl(end_date - ame_util.oneSecond, sysdate)
1976: order by order_number;
1977: approvalGroupIds ame_util.idList;
1978: currentUserId integer;
1979: orderNumbers ame_util.idList;
1980: processingDate date;
1981: votingRegime ame_util.charType;
1982: begin
1983: currentUserId := ame_util.getCurrentUserId;
1977: approvalGroupIds ame_util.idList;
1978: currentUserId integer;
1979: orderNumbers ame_util.idList;
1980: processingDate date;
1981: votingRegime ame_util.charType;
1982: begin
1983: currentUserId := ame_util.getCurrentUserId;
1984: processingDate := sysdate;
1985: open orderNumberCursor;
1979: orderNumbers ame_util.idList;
1980: processingDate date;
1981: votingRegime ame_util.charType;
1982: begin
1983: currentUserId := ame_util.getCurrentUserId;
1984: processingDate := sysdate;
1985: open orderNumberCursor;
1986: fetch orderNumberCursor bulk collect
1987: into approvalGroupIds, orderNumbers;
1998: where
1999: application_id = applicationIdIn and
2000: approval_group_id = approvalGroupIds(i) and
2001: sysdate between start_date and
2002: nvl(end_date - ame_util.oneSecond, sysdate);
2003: insert into ame_approval_group_config(application_id,
2004: approval_group_id,
2005: voting_regime,
2006: order_number,
2028: end if;
2029: exception
2030: when others then
2031: rollback;
2032: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2033: routineNameIn => 'decrementGroupOrderNumbers',
2034: exceptionNumberIn => sqlcode,
2035: exceptionStringIn => sqlerrm);
2036: raise;
2035: exceptionStringIn => sqlerrm);
2036: raise;
2037: end decrementGroupOrderNumbers;
2038: procedure getAllowedNestedGroups(groupIdIn in integer,
2039: allowedNestedGroupIdsOut out nocopy ame_util.stringList,
2040: allowedNestedGroupNamesOut out nocopy ame_util.stringList) as
2041: cursor groupCursor is
2042: select
2043: approval_group_id,
2036: raise;
2037: end decrementGroupOrderNumbers;
2038: procedure getAllowedNestedGroups(groupIdIn in integer,
2039: allowedNestedGroupIdsOut out nocopy ame_util.stringList,
2040: allowedNestedGroupNamesOut out nocopy ame_util.stringList) as
2041: cursor groupCursor is
2042: select
2043: approval_group_id,
2044: name
2044: name
2045: from ame_approval_groups
2046: where
2047: sysdate between start_date and
2048: nvl(end_date - ame_util.oneSecond, sysdate) ;
2049: tempIndex integer;
2050: begin
2051: tempIndex := 0; /* pre-increment */
2052: for tempGroup in groupCursor loop
2068: end loop;
2069: exception
2070: when others then
2071: rollback;
2072: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2073: routineNameIn => 'getAllowedNestedGroups',
2074: exceptionNumberIn => sqlcode,
2075: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2076: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2071: rollback;
2072: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2073: routineNameIn => 'getAllowedNestedGroups',
2074: exceptionNumberIn => sqlcode,
2075: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2076: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2077: tokenNameOneIn => 'NAME',
2078: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
2079: || ' ' || sqlerrm);
2076: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2077: tokenNameOneIn => 'NAME',
2078: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
2079: || ' ' || sqlerrm);
2080: allowedNestedGroupIdsOut := ame_util.emptyStringList;
2081: allowedNestedGroupNamesOut := ame_util.emptyStringList;
2082: raise;
2083: end getAllowedNestedGroups;
2084: procedure getApprovalGroupItemList(approvalGroupIdIn in integer,
2077: tokenNameOneIn => 'NAME',
2078: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
2079: || ' ' || sqlerrm);
2080: allowedNestedGroupIdsOut := ame_util.emptyStringList;
2081: allowedNestedGroupNamesOut := ame_util.emptyStringList;
2082: raise;
2083: end getAllowedNestedGroups;
2084: procedure getApprovalGroupItemList(approvalGroupIdIn in integer,
2085: itemListOut out nocopy ame_util.idList,
2081: allowedNestedGroupNamesOut := ame_util.emptyStringList;
2082: raise;
2083: end getAllowedNestedGroups;
2084: procedure getApprovalGroupItemList(approvalGroupIdIn in integer,
2085: itemListOut out nocopy ame_util.idList,
2086: orderListOut out nocopy ame_util.idList,
2087: descriptionListOut out nocopy ame_util.longStringList,
2088: invalidMembersOut out nocopy boolean) as
2089: cursor itemCursor(approvalGroupIdIn in integer) is
2082: raise;
2083: end getAllowedNestedGroups;
2084: procedure getApprovalGroupItemList(approvalGroupIdIn in integer,
2085: itemListOut out nocopy ame_util.idList,
2086: orderListOut out nocopy ame_util.idList,
2087: descriptionListOut out nocopy ame_util.longStringList,
2088: invalidMembersOut out nocopy boolean) as
2089: cursor itemCursor(approvalGroupIdIn in integer) is
2090: select approval_group_item_id
2083: end getAllowedNestedGroups;
2084: procedure getApprovalGroupItemList(approvalGroupIdIn in integer,
2085: itemListOut out nocopy ame_util.idList,
2086: orderListOut out nocopy ame_util.idList,
2087: descriptionListOut out nocopy ame_util.longStringList,
2088: invalidMembersOut out nocopy boolean) as
2089: cursor itemCursor(approvalGroupIdIn in integer) is
2090: select approval_group_item_id
2091: from ame_approval_group_items
2091: from ame_approval_group_items
2092: where
2093: approval_group_id = approvalGroupIdIn and
2094: sysdate between start_date and
2095: nvl(end_date - ame_util.oneSecond, sysdate)
2096: order by order_number;
2097: tempindex integer;
2098: tempDescription ame_approval_groups.description%type;
2099: tempFirstName per_all_people_f.first_name%type;
2103: tempParameter ame_approval_group_items.parameter%type;
2104: tempParameterName ame_approval_group_items.parameter_name%type;
2105: tempUserName fnd_user.user_name%type;
2106: tempRowNumber integer;
2107: approverDesc ame_util.longStringType;
2108: approverValid boolean;
2109: begin
2110: tempIndex := 1;
2111: invalidMembersOut := false;
2114: tempParameterName := ame_approval_group_pkg.getItemParameterName(approvalGroupItemIdIn => tempItemId);
2115: tempParameter := ame_approval_group_pkg.getItemParameter(approvalGroupItemIdIn => tempItemId);
2116: itemListOut(tempIndex) := tempItemId;
2117: orderListOut(tempIndex) := getItemOrderNumber(approvalGroupItemIdIn => tempItemId);
2118: if tempParameterName = ame_util.approverOamGroupId then
2119: descriptionListOut(tempIndex) :=
2120: orderListOut(tempIndex) ||
2121: '. ' ||
2122: getName(approvalGroupIdIn => to_number(tempParameter));
2134: end loop;
2135: exception
2136: when others then
2137: rollback;
2138: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2139: routineNameIn => 'getApprovalGroupItemList',
2140: exceptionNumberIn => sqlcode,
2141: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2142: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2137: rollback;
2138: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2139: routineNameIn => 'getApprovalGroupItemList',
2140: exceptionNumberIn => sqlcode,
2141: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2142: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2143: tokenNameOneIn => 'NAME',
2144: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2145: || ' ' || sqlerrm);
2142: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2143: tokenNameOneIn => 'NAME',
2144: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2145: || ' ' || sqlerrm);
2146: itemListOut := ame_util.emptyIdList;
2147: orderListOut := ame_util.emptyIdList;
2148: descriptionListOut := ame_util.emptyLongStringList;
2149: raise;
2150: end getApprovalGroupItemList;
2143: tokenNameOneIn => 'NAME',
2144: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2145: || ' ' || sqlerrm);
2146: itemListOut := ame_util.emptyIdList;
2147: orderListOut := ame_util.emptyIdList;
2148: descriptionListOut := ame_util.emptyLongStringList;
2149: raise;
2150: end getApprovalGroupItemList;
2151: procedure getApprovalGroupList(groupListOut out nocopy ame_util.idList) as
2144: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2145: || ' ' || sqlerrm);
2146: itemListOut := ame_util.emptyIdList;
2147: orderListOut := ame_util.emptyIdList;
2148: descriptionListOut := ame_util.emptyLongStringList;
2149: raise;
2150: end getApprovalGroupItemList;
2151: procedure getApprovalGroupList(groupListOut out nocopy ame_util.idList) as
2152: cursor groupCursor is
2147: orderListOut := ame_util.emptyIdList;
2148: descriptionListOut := ame_util.emptyLongStringList;
2149: raise;
2150: end getApprovalGroupItemList;
2151: procedure getApprovalGroupList(groupListOut out nocopy ame_util.idList) as
2152: cursor groupCursor is
2153: select approval_group_id
2154: from ame_approval_groups
2155: where
2153: select approval_group_id
2154: from ame_approval_groups
2155: where
2156: sysdate between start_date and
2157: nvl(end_date - ame_util.oneSecond, sysdate);
2158: tempIndex integer;
2159: begin
2160: tempIndex := 1;
2161: for tempGroup in groupCursor loop
2164: end loop;
2165: exception
2166: when others then
2167: rollback;
2168: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2169: routineNameIn => 'getApprovalGroupList',
2170: exceptionNumberIn => sqlcode,
2171: exceptionStringIn => sqlerrm);
2172: groupListOut := ame_util.emptyIdList;
2168: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2169: routineNameIn => 'getApprovalGroupList',
2170: exceptionNumberIn => sqlcode,
2171: exceptionStringIn => sqlerrm);
2172: groupListOut := ame_util.emptyIdList;
2173: raise;
2174: end getApprovalGroupList;
2175: procedure getApprovalGroupList2(applicationIdIn in integer,
2176: groupListOut out nocopy ame_util.idList) as
2172: groupListOut := ame_util.emptyIdList;
2173: raise;
2174: end getApprovalGroupList;
2175: procedure getApprovalGroupList2(applicationIdIn in integer,
2176: groupListOut out nocopy ame_util.idList) as
2177: cursor groupCursor is
2178: select ame_approval_groups.approval_group_id
2179: from ame_approval_groups,
2180: ame_approval_group_config
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2183: ame_approval_group_config.application_id = applicationIdIn and
2184: sysdate between ame_approval_groups.start_date and
2185: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
2186: sysdate between ame_approval_group_config.start_date and
2187: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate)
2188: order by ame_approval_group_config.order_number;
2189: tempIndex integer;
2183: ame_approval_group_config.application_id = applicationIdIn and
2184: sysdate between ame_approval_groups.start_date and
2185: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
2186: sysdate between ame_approval_group_config.start_date and
2187: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate)
2188: order by ame_approval_group_config.order_number;
2189: tempIndex integer;
2190: begin
2191: tempIndex := 1;
2195: end loop;
2196: exception
2197: when others then
2198: rollback;
2199: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2200: routineNameIn => 'getApprovalGroupList2',
2201: exceptionNumberIn => sqlcode,
2202: exceptionStringIn => sqlerrm);
2203: groupListOut := ame_util.emptyIdList;
2199: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2200: routineNameIn => 'getApprovalGroupList2',
2201: exceptionNumberIn => sqlcode,
2202: exceptionStringIn => sqlerrm);
2203: groupListOut := ame_util.emptyIdList;
2204: raise;
2205: end getApprovalGroupList2;
2206: procedure getGroupMembers(approvalGroupIdIn in integer,
2207: memberIdsOut out nocopy ame_util.longStringList,
2203: groupListOut := ame_util.emptyIdList;
2204: raise;
2205: end getApprovalGroupList2;
2206: procedure getGroupMembers(approvalGroupIdIn in integer,
2207: memberIdsOut out nocopy ame_util.longStringList,
2208: memberTypesOut out nocopy ame_util.stringList) as
2209: cursor groupMemberCursor(approvalGroupIdIn in integer) is
2210: select
2211: parameter,
2204: raise;
2205: end getApprovalGroupList2;
2206: procedure getGroupMembers(approvalGroupIdIn in integer,
2207: memberIdsOut out nocopy ame_util.longStringList,
2208: memberTypesOut out nocopy ame_util.stringList) as
2209: cursor groupMemberCursor(approvalGroupIdIn in integer) is
2210: select
2211: parameter,
2212: parameter_name
2213: from ame_approval_group_items
2214: where
2215: approval_group_id = approvalGroupIdIn and
2216: sysdate between start_date and
2217: nvl(end_date - ame_util.oneSecond, sysdate)
2218: order by order_number;
2219: isStatic ame_approval_groups.is_static%type;
2220: queryString ame_util.longestStringType;
2221: recursionParameterNames ame_util.stringList;
2216: sysdate between start_date and
2217: nvl(end_date - ame_util.oneSecond, sysdate)
2218: order by order_number;
2219: isStatic ame_approval_groups.is_static%type;
2220: queryString ame_util.longestStringType;
2221: recursionParameterNames ame_util.stringList;
2222: recursionParameters ame_util.longStringList;
2223: recursionUpperLimit integer;
2224: tempIndex integer;
2217: nvl(end_date - ame_util.oneSecond, sysdate)
2218: order by order_number;
2219: isStatic ame_approval_groups.is_static%type;
2220: queryString ame_util.longestStringType;
2221: recursionParameterNames ame_util.stringList;
2222: recursionParameters ame_util.longStringList;
2223: recursionUpperLimit integer;
2224: tempIndex integer;
2225: begin
2218: order by order_number;
2219: isStatic ame_approval_groups.is_static%type;
2220: queryString ame_util.longestStringType;
2221: recursionParameterNames ame_util.stringList;
2222: recursionParameters ame_util.longStringList;
2223: recursionUpperLimit integer;
2224: tempIndex integer;
2225: begin
2226: select
2232: from ame_approval_groups
2233: where
2234: approval_group_id = approvalGroupIdIn and
2235: sysdate between start_date and
2236: nvl(end_date - ame_util.oneSecond, sysdate) ;
2237: if(isStatic = ame_util.booleanTrue) then /* Use the static membership list. */
2238: tempIndex := 0; /* pre-increment */
2239: for tempMember in groupMemberCursor(approvalGroupIdIn => approvalGroupIdIn) loop
2240: if(tempMember.parameter_name = ame_util.approverOamGroupId) then
2233: where
2234: approval_group_id = approvalGroupIdIn and
2235: sysdate between start_date and
2236: nvl(end_date - ame_util.oneSecond, sysdate) ;
2237: if(isStatic = ame_util.booleanTrue) then /* Use the static membership list. */
2238: tempIndex := 0; /* pre-increment */
2239: for tempMember in groupMemberCursor(approvalGroupIdIn => approvalGroupIdIn) loop
2240: if(tempMember.parameter_name = ame_util.approverOamGroupId) then
2241: /* recursion */
2236: nvl(end_date - ame_util.oneSecond, sysdate) ;
2237: if(isStatic = ame_util.booleanTrue) then /* Use the static membership list. */
2238: tempIndex := 0; /* pre-increment */
2239: for tempMember in groupMemberCursor(approvalGroupIdIn => approvalGroupIdIn) loop
2240: if(tempMember.parameter_name = ame_util.approverOamGroupId) then
2241: /* recursion */
2242: getGroupMembers(approvalGroupIdIn => to_number(tempMember.parameter),
2243: memberTypesOut => recursionParameterNames,
2244: memberIdsOut => recursionParameters);
2261: end if;
2262: exception
2263: when others then
2264: rollback;
2265: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2266: routineNameIn => 'getGroupMembers',
2267: exceptionNumberIn => sqlcode,
2268: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2269: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2264: rollback;
2265: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2266: routineNameIn => 'getGroupMembers',
2267: exceptionNumberIn => sqlcode,
2268: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2269: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2270: tokenNameOneIn => 'NAME',
2271: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2272: || ' ' || sqlerrm);
2269: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2270: tokenNameOneIn => 'NAME',
2271: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2272: || ' ' || sqlerrm);
2273: memberIdsOut := ame_util.emptyLongStringList;
2274: memberTypesOut := ame_util.emptyStringList;
2275: raise;
2276: end getGroupMembers;
2277: procedure getInvalidApprGroupItemList(approvalGroupIdIn in integer,
2270: tokenNameOneIn => 'NAME',
2271: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2272: || ' ' || sqlerrm);
2273: memberIdsOut := ame_util.emptyLongStringList;
2274: memberTypesOut := ame_util.emptyStringList;
2275: raise;
2276: end getGroupMembers;
2277: procedure getInvalidApprGroupItemList(approvalGroupIdIn in integer,
2278: itemListOut out nocopy ame_util.idList) as
2274: memberTypesOut := ame_util.emptyStringList;
2275: raise;
2276: end getGroupMembers;
2277: procedure getInvalidApprGroupItemList(approvalGroupIdIn in integer,
2278: itemListOut out nocopy ame_util.idList) as
2279: cursor itemCursor(approvalGroupIdIn in integer) is
2280: select approval_group_item_id, parameter
2281: from ame_approval_group_items
2282: where
2280: select approval_group_item_id, parameter
2281: from ame_approval_group_items
2282: where
2283: approval_group_id = approvalGroupIdIn and
2284: parameter_name <> ame_util.approverOamGroupId and
2285: sysdate between start_date and
2286: nvl(end_date - ame_util.oneSecond, sysdate)
2287: order by order_number;
2288: tempIndex integer;
2282: where
2283: approval_group_id = approvalGroupIdIn and
2284: parameter_name <> ame_util.approverOamGroupId and
2285: sysdate between start_date and
2286: nvl(end_date - ame_util.oneSecond, sysdate)
2287: order by order_number;
2288: tempIndex integer;
2289: tempItemId integer;
2290: tempParameter ame_approval_group_items.parameter%type;
2300: end loop;
2301: exception
2302: when others then
2303: rollback;
2304: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2305: routineNameIn => 'getInvalidApprGroupItemList',
2306: exceptionNumberIn => sqlcode,
2307: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2308: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2303: rollback;
2304: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2305: routineNameIn => 'getInvalidApprGroupItemList',
2306: exceptionNumberIn => sqlcode,
2307: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2308: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2309: tokenNameOneIn => 'NAME',
2310: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2311: || ' ' || sqlerrm);
2308: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2309: tokenNameOneIn => 'NAME',
2310: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2311: || ' ' || sqlerrm);
2312: itemListOut := ame_util.emptyIdList;
2313: raise;
2314: end getInvalidApprGroupItemList;
2315: procedure getNestedMembers(groupIdIn in integer,
2316: effectiveDateIn in date default null,
2313: raise;
2314: end getInvalidApprGroupItemList;
2315: procedure getNestedMembers(groupIdIn in integer,
2316: effectiveDateIn in date default null,
2317: parameterNamesOut out nocopy ame_util.stringList,
2318: parametersOut out nocopy ame_util.stringList,
2319: orderNumbersOut out nocopy ame_util.idList,
2320: queryStringsOut out nocopy ame_util.longestStringList) as
2321: cursor groupMemberCursor(approvalGroupIdIn in integer,
2314: end getInvalidApprGroupItemList;
2315: procedure getNestedMembers(groupIdIn in integer,
2316: effectiveDateIn in date default null,
2317: parameterNamesOut out nocopy ame_util.stringList,
2318: parametersOut out nocopy ame_util.stringList,
2319: orderNumbersOut out nocopy ame_util.idList,
2320: queryStringsOut out nocopy ame_util.longestStringList) as
2321: cursor groupMemberCursor(approvalGroupIdIn in integer,
2322: effectiveDateIn in date) is
2315: procedure getNestedMembers(groupIdIn in integer,
2316: effectiveDateIn in date default null,
2317: parameterNamesOut out nocopy ame_util.stringList,
2318: parametersOut out nocopy ame_util.stringList,
2319: orderNumbersOut out nocopy ame_util.idList,
2320: queryStringsOut out nocopy ame_util.longestStringList) as
2321: cursor groupMemberCursor(approvalGroupIdIn in integer,
2322: effectiveDateIn in date) is
2323: select
2316: effectiveDateIn in date default null,
2317: parameterNamesOut out nocopy ame_util.stringList,
2318: parametersOut out nocopy ame_util.stringList,
2319: orderNumbersOut out nocopy ame_util.idList,
2320: queryStringsOut out nocopy ame_util.longestStringList) as
2321: cursor groupMemberCursor(approvalGroupIdIn in integer,
2322: effectiveDateIn in date) is
2323: select
2324: parameter,
2326: order_number
2327: from ame_approval_group_items
2328: where
2329: approval_group_id = approvalGroupIdIn and
2330: effectiveDateIn between start_date and nvl(end_date - ame_util.oneSecond, sysdate)
2331: order by order_number;
2332: outputIndex integer;
2333: recursionParameterNames ame_util.stringList;
2334: recursionParameters ame_util.stringList;
2329: approval_group_id = approvalGroupIdIn and
2330: effectiveDateIn between start_date and nvl(end_date - ame_util.oneSecond, sysdate)
2331: order by order_number;
2332: outputIndex integer;
2333: recursionParameterNames ame_util.stringList;
2334: recursionParameters ame_util.stringList;
2335: recursionOrderNumbers ame_util.idList;
2336: recursionQueries ame_util.longestStringList;
2337: upperLimit integer;
2330: effectiveDateIn between start_date and nvl(end_date - ame_util.oneSecond, sysdate)
2331: order by order_number;
2332: outputIndex integer;
2333: recursionParameterNames ame_util.stringList;
2334: recursionParameters ame_util.stringList;
2335: recursionOrderNumbers ame_util.idList;
2336: recursionQueries ame_util.longestStringList;
2337: upperLimit integer;
2338: begin
2331: order by order_number;
2332: outputIndex integer;
2333: recursionParameterNames ame_util.stringList;
2334: recursionParameters ame_util.stringList;
2335: recursionOrderNumbers ame_util.idList;
2336: recursionQueries ame_util.longestStringList;
2337: upperLimit integer;
2338: begin
2339: /* If the target group is dynamic, just return its query string. */
2332: outputIndex integer;
2333: recursionParameterNames ame_util.stringList;
2334: recursionParameters ame_util.stringList;
2335: recursionOrderNumbers ame_util.idList;
2336: recursionQueries ame_util.longestStringList;
2337: upperLimit integer;
2338: begin
2339: /* If the target group is dynamic, just return its query string. */
2340: if(not isStatic(approvalGroupIdIn => groupIdIn,
2338: begin
2339: /* If the target group is dynamic, just return its query string. */
2340: if(not isStatic(approvalGroupIdIn => groupIdIn,
2341: effectiveDateIn => effectiveDateIn)) then
2342: parameterNamesOut(1) := ame_util.approverOamGroupId;
2343: parametersOut(1) := to_char(groupIdIn);
2344: orderNumbersOut(1) := 1;
2345: queryStringsOut(1) := getQueryString(approvalGroupIdIn => groupIdIn,
2346: effectiveDateIn => effectiveDateIn);
2349: outputIndex := 0; /* pre-increment */
2350: /* The target group is static, so loop through its members. */
2351: for tempMember in groupMemberCursor(approvalGroupIdIn => groupIdIn,
2352: effectiveDateIn => effectiveDateIn) loop
2353: if(tempMember.parameter_name = ame_util.approverOamGroupId) then
2354: if(isStatic(approvalGroupIdIn => to_number(tempMember.parameter),
2355: effectiveDateIn => effectiveDateIn)) then
2356: recursionParameterNames.delete;
2357: recursionParameters.delete;
2372: queryStringsOut(outputIndex) := recursionQueries(i);
2373: end loop;
2374: else
2375: outputIndex := outputIndex + 1;
2376: parameterNamesOut(outputIndex) := ame_util.approverOamGroupId;
2377: parametersOut(outputIndex) := tempMember.parameter;
2378: orderNumbersOut(outputIndex) := outputIndex;
2379: queryStringsOut(outputIndex) := getQueryString(approvalGroupIdIn => to_number(tempMember.parameter),
2380: effectiveDateIn => effectiveDateIn);
2389: end loop;
2390: exception
2391: when others then
2392: rollback;
2393: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2394: routineNameIn => 'getNestedMembers',
2395: exceptionNumberIn => sqlcode,
2396: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2397: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2392: rollback;
2393: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2394: routineNameIn => 'getNestedMembers',
2395: exceptionNumberIn => sqlcode,
2396: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2397: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2398: tokenNameOneIn => 'NAME',
2399: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
2400: effectiveDateIn => effectiveDateIn))
2398: tokenNameOneIn => 'NAME',
2399: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
2400: effectiveDateIn => effectiveDateIn))
2401: || ' ' || sqlerrm);
2402: parameterNamesOut := ame_util.emptyStringList;
2403: parametersOut := ame_util.emptyStringList;
2404: orderNumbersOut := ame_util.emptyIdList;
2405: queryStringsOut := ame_util.emptyLongestStringList;
2406: raise;
2399: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
2400: effectiveDateIn => effectiveDateIn))
2401: || ' ' || sqlerrm);
2402: parameterNamesOut := ame_util.emptyStringList;
2403: parametersOut := ame_util.emptyStringList;
2404: orderNumbersOut := ame_util.emptyIdList;
2405: queryStringsOut := ame_util.emptyLongestStringList;
2406: raise;
2407: end getNestedMembers;
2400: effectiveDateIn => effectiveDateIn))
2401: || ' ' || sqlerrm);
2402: parameterNamesOut := ame_util.emptyStringList;
2403: parametersOut := ame_util.emptyStringList;
2404: orderNumbersOut := ame_util.emptyIdList;
2405: queryStringsOut := ame_util.emptyLongestStringList;
2406: raise;
2407: end getNestedMembers;
2408: /*
2401: || ' ' || sqlerrm);
2402: parameterNamesOut := ame_util.emptyStringList;
2403: parametersOut := ame_util.emptyStringList;
2404: orderNumbersOut := ame_util.emptyIdList;
2405: queryStringsOut := ame_util.emptyLongestStringList;
2406: raise;
2407: end getNestedMembers;
2408: /*
2409: procedure getOrderNumbers(approvalGroupIdIn in integer,
2406: raise;
2407: end getNestedMembers;
2408: /*
2409: procedure getOrderNumbers(approvalGroupIdIn in integer,
2410: orderNumbersOut out nocopy ame_util.stringList) as
2411: cursor getOrderNumbersCursor(approvalGroupIdIn in integer) is
2412: select order_number
2413: from ame_approval_group_items
2414: where approval_group_id = approvalGroupIdIn and
2412: select order_number
2413: from ame_approval_group_items
2414: where approval_group_id = approvalGroupIdIn and
2415: sysdate between start_date and
2416: nvl(end_date - ame_util.oneSecond, sysdate)
2417: order by order_number;
2418: tempIndex integer;
2419: begin
2420: tempIndex := 1;
2424: end loop;
2425: exception
2426: when others then
2427: rollback;
2428: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2429: routineNameIn => 'getOrderNumbers',
2430: exceptionNumberIn => sqlcode,
2431: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2432: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2427: rollback;
2428: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2429: routineNameIn => 'getOrderNumbers',
2430: exceptionNumberIn => sqlcode,
2431: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2432: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2433: tokenNameOneIn => 'NAME',
2434: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2435: || ' ' || sqlerrm);
2432: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2433: tokenNameOneIn => 'NAME',
2434: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2435: || ' ' || sqlerrm);
2436: orderNumbersOut := ame_util.emptyStringList;
2437: raise;
2438: end getOrderNumbers;
2439: */
2440: procedure incrementGroupItemOrderNumbers(approvalGroupIdIn in integer,
2450: approval_group_id = approvalGroupIdIn and
2451: approval_group_item_id <> approvalGroupItemIdIn and
2452: order_number >= orderNumberIn and
2453: sysdate between start_date and
2454: nvl(end_date - ame_util.oneSecond, sysdate)
2455: order by order_number;
2456: approvalGroupItemIds ame_util.idList;
2457: currentUserId integer;
2458: orderNumbers ame_util.idList;
2452: order_number >= orderNumberIn and
2453: sysdate between start_date and
2454: nvl(end_date - ame_util.oneSecond, sysdate)
2455: order by order_number;
2456: approvalGroupItemIds ame_util.idList;
2457: currentUserId integer;
2458: orderNumbers ame_util.idList;
2459: parameter ame_approval_group_items.parameter%type;
2460: parameterName ame_approval_group_items.parameter_name%type;
2454: nvl(end_date - ame_util.oneSecond, sysdate)
2455: order by order_number;
2456: approvalGroupItemIds ame_util.idList;
2457: currentUserId integer;
2458: orderNumbers ame_util.idList;
2459: parameter ame_approval_group_items.parameter%type;
2460: parameterName ame_approval_group_items.parameter_name%type;
2461: processingDate date;
2462: begin
2459: parameter ame_approval_group_items.parameter%type;
2460: parameterName ame_approval_group_items.parameter_name%type;
2461: processingDate date;
2462: begin
2463: currentUserId := ame_util.getCurrentUserId;
2464: processingDate := sysdate;
2465: open orderNumberCursor(approvalGroupIdIn => approvalGroupIdIn,
2466: approvalGroupItemIdIn => approvalGroupItemIdIn,
2467: orderNumberIn => orderNumberIn);
2479: end_date = processingDate
2480: where
2481: approval_group_item_id = approvalGroupItemIds(i) and
2482: sysdate between start_date and
2483: nvl(end_date - ame_util.oneSecond, sysdate);
2484: insert into ame_approval_group_items(approval_group_item_id,
2485: approval_group_id,
2486: parameter_name,
2487: parameter,
2511: end if;
2512: exception
2513: when others then
2514: rollback;
2515: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2516: routineNameIn => 'incrementGroupItemOrderNumbers',
2517: exceptionNumberIn => sqlcode,
2518: exceptionStringIn => sqlerrm);
2519: raise;
2529: application_id = applicationIdIn and
2530: approval_group_id <> approvalGroupIdIn and
2531: order_number >= orderNumberIn and
2532: sysdate between start_date and
2533: nvl(end_date - ame_util.oneSecond, sysdate)
2534: order by order_number;
2535: approvalGroupIds ame_util.idList;
2536: currentUserId integer;
2537: orderNumbers ame_util.idList;
2531: order_number >= orderNumberIn and
2532: sysdate between start_date and
2533: nvl(end_date - ame_util.oneSecond, sysdate)
2534: order by order_number;
2535: approvalGroupIds ame_util.idList;
2536: currentUserId integer;
2537: orderNumbers ame_util.idList;
2538: processingDate date;
2539: votingRegime ame_util.charType;
2533: nvl(end_date - ame_util.oneSecond, sysdate)
2534: order by order_number;
2535: approvalGroupIds ame_util.idList;
2536: currentUserId integer;
2537: orderNumbers ame_util.idList;
2538: processingDate date;
2539: votingRegime ame_util.charType;
2540: begin
2541: currentUserId := ame_util.getCurrentUserId;
2535: approvalGroupIds ame_util.idList;
2536: currentUserId integer;
2537: orderNumbers ame_util.idList;
2538: processingDate date;
2539: votingRegime ame_util.charType;
2540: begin
2541: currentUserId := ame_util.getCurrentUserId;
2542: processingDate := sysdate;
2543: open orderNumberCursor;
2537: orderNumbers ame_util.idList;
2538: processingDate date;
2539: votingRegime ame_util.charType;
2540: begin
2541: currentUserId := ame_util.getCurrentUserId;
2542: processingDate := sysdate;
2543: open orderNumberCursor;
2544: fetch orderNumberCursor bulk collect
2545: into approvalGroupIds, orderNumbers;
2556: where
2557: application_id = applicationIdIn and
2558: approval_group_id = approvalGroupIds(i) and
2559: sysdate between start_date and
2560: nvl(end_date - ame_util.oneSecond, sysdate);
2561: insert into ame_approval_group_config(application_id,
2562: approval_group_id,
2563: voting_regime,
2564: order_number,
2586: end if;
2587: exception
2588: when others then
2589: rollback;
2590: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2591: routineNameIn => 'incrementGroupOrderNumbers',
2592: exceptionNumberIn => sqlcode,
2593: exceptionStringIn => sqlerrm);
2594: raise;
2595: end incrementGroupOrderNumbers;
2596: procedure newApprovalGroupConfig(approvalGroupIdIn in integer,
2597: applicationIdIn in integer default null,
2598: orderNumberIn in integer default null,
2599: orderNumberUniqueIn in varchar2 default ame_util.yes,
2600: votingRegimeIn in varchar2 default ame_util.serializedVoting,
2601: finalizeIn in boolean default false) as
2602: cursor applicationIdCursor is
2603: select application_id
2596: procedure newApprovalGroupConfig(approvalGroupIdIn in integer,
2597: applicationIdIn in integer default null,
2598: orderNumberIn in integer default null,
2599: orderNumberUniqueIn in varchar2 default ame_util.yes,
2600: votingRegimeIn in varchar2 default ame_util.serializedVoting,
2601: finalizeIn in boolean default false) as
2602: cursor applicationIdCursor is
2603: select application_id
2604: from ame_calling_apps
2603: select application_id
2604: from ame_calling_apps
2605: where
2606: sysdate between start_date and
2607: nvl(end_date - ame_util.oneSecond, sysdate)
2608: order by application_id;
2609: applicationId integer;
2610: applicationIds ame_util.idList;
2611: currentUserId integer;
2606: sysdate between start_date and
2607: nvl(end_date - ame_util.oneSecond, sysdate)
2608: order by application_id;
2609: applicationId integer;
2610: applicationIds ame_util.idList;
2611: currentUserId integer;
2612: maxOrderNumber integer;
2613: orderNumber ame_approval_group_config.order_number%type;
2614: processingDate date;
2613: orderNumber ame_approval_group_config.order_number%type;
2614: processingDate date;
2615: tempCount integer;
2616: begin
2617: currentUserId := ame_util.getCurrentUserId;
2618: processingDate := sysdate;
2619: maxOrderNumber :=
2620: ame_approval_group_pkg.getApprovalGroupMaxOrderNumber(applicationIdIn => applicationIdIn);
2621: open applicationIdCursor;
2634: from ame_approval_group_config
2635: where
2636: application_id = applicationIds(i) and
2637: sysdate between start_date and
2638: nvl(end_date - ame_util.oneSecond, sysdate);
2639: if(tempCount = 0) then
2640: orderNumber := 1;
2641: else
2642: select (nvl(max(order_number), 0) + 1)
2644: from ame_approval_group_config
2645: where
2646: application_id = applicationIds(i) and
2647: sysdate between start_date and
2648: nvl(end_date - ame_util.oneSecond, sysdate);
2649: end if;
2650: end if;
2651: insert into ame_approval_group_config(application_id,
2652: approval_group_id,
2670: currentUserId,
2671: processingDate,
2672: null);
2673: end loop;
2674: if(orderNumberUniqueIn = ame_util.yes) then
2675: if(orderNumberIn <> (maxOrderNumber + 1)) then
2676: incrementGroupOrderNumbers(applicationIdIn => applicationIdIn,
2677: approvalGroupIdIn => approvalGroupIdIn,
2678: orderNumberIn => orderNumberIn);
2683: end if;
2684: exception
2685: when others then
2686: rollback;
2687: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2688: routineNameIn => 'newApprovalGroupConfig',
2689: exceptionNumberIn => sqlcode,
2690: exceptionStringIn => sqlerrm);
2691: end newApprovalGroupConfig;
2696: from ame_approval_groups
2697: where
2698: approval_group_id = approvalGroupIdIn and
2699: sysdate between start_date and
2700: nvl(end_date - ame_util.oneSecond, sysdate)
2701: for update;
2702: cursor applicationIdCursor is
2703: select application_id
2704: from ame_calling_apps
2703: select application_id
2704: from ame_calling_apps
2705: where
2706: sysdate between start_date and
2707: nvl(end_date - ame_util.oneSecond, sysdate)
2708: order by application_id;
2709: applicationIds ame_util.idList;
2710: currentUserId integer;
2711: errorCode integer;
2705: where
2706: sysdate between start_date and
2707: nvl(end_date - ame_util.oneSecond, sysdate)
2708: order by application_id;
2709: applicationIds ame_util.idList;
2710: currentUserId integer;
2711: errorCode integer;
2712: errorMessage ame_util.longestStringType;
2713: inUseException exception;
2708: order by application_id;
2709: applicationIds ame_util.idList;
2710: currentUserId integer;
2711: errorCode integer;
2712: errorMessage ame_util.longestStringType;
2713: inUseException exception;
2714: objectVersionNoDataException exception;
2715: orderNumber integer;
2716: startDate date;
2724: end if;
2725: if(isInUse(approvalGroupIdIn => approvalGroupIdIn)) then
2726: raise inUseException;
2727: end if;
2728: currentUserId := ame_util.getCurrentUserId;
2729: if parentVersionStartDateIn = startDate then
2730: open applicationIdCursor;
2731: fetch applicationIdCursor bulk collect
2732: into applicationIds;
2738: where
2739: application_id = applicationIds(i) and
2740: approval_group_id = approvalGroupIdIn and
2741: sysdate between start_date and
2742: nvl(end_date - ame_util.oneSecond, sysdate);
2743: if(orderNumberUnique(applicationIdIn => applicationIds(i),
2744: orderNumberIn => orderNumber)) then
2745: /* subtract 1 from the order number for those above the one being deleted */
2746: decrementGroupOrderNumbers(applicationIdIn => applicationIds(i),
2757: end_date = processingDate
2758: where
2759: approval_group_id = approvalGroupIdIn and
2760: processingDate between start_date and
2761: nvl(end_date - ame_util.oneSecond, processingDate) ;
2762: /* End-date approval-group items. */
2763: update ame_approval_group_items
2764: set
2765: last_updated_by = currentUserId,
2768: end_date = processingDate
2769: where
2770: approval_group_id = approvalGroupIdIn and
2771: processingDate between start_date and
2772: nvl(end_date - ame_util.oneSecond, processingDate);
2773: /* End-date the approval group configs */
2774: update ame_approval_group_config
2775: set
2776: last_updated_by = currentUserId,
2779: end_date = processingDate
2780: where
2781: approval_group_id = approvalGroupIdIn and
2782: processingDate between start_date and
2783: nvl(end_date - ame_util.oneSecond, processingDate) ;
2784: /* End-date any related actions. */
2785: update ame_actions
2786: set
2787: last_updated_by = currentUserId,
2790: end_date = processingDate
2791: where
2792: parameter = to_char(approvalGroupIdIn) and
2793: processingDate between start_date and
2794: nvl(end_date - ame_util.oneSecond, processingDate) ;
2795: /*
2796: Remove the group from any groups containing it, and
2797: update those groups in ame_approval_group_members.
2798: */
2800: deleteGroupIn => true);
2801: commit;
2802: else
2803: close startDateCursor;
2804: raise ame_util.objectVersionException;
2805: end if;
2806: close startDateCursor;
2807: exception
2808: when ame_util.objectVersionException then
2804: raise ame_util.objectVersionException;
2805: end if;
2806: close startDateCursor;
2807: exception
2808: when ame_util.objectVersionException then
2809: rollback;
2810: if(startDateCursor%isOpen) then
2811: close startDateCursor;
2812: end if;
2811: close startDateCursor;
2812: end if;
2813: errorCode := -20001;
2814: errorMessage :=
2815: ame_util.getMessage(applicationShortNameIn => 'PER',
2816: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2817: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2818: routineNameIn => 'remove',
2819: exceptionNumberIn => errorCode,
2813: errorCode := -20001;
2814: errorMessage :=
2815: ame_util.getMessage(applicationShortNameIn => 'PER',
2816: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2817: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2818: routineNameIn => 'remove',
2819: exceptionNumberIn => errorCode,
2820: exceptionStringIn => errorMessage);
2821: raise_application_error(errorCode,
2826: close startDateCursor;
2827: end if;
2828: errorCode := -20001;
2829: errorMessage :=
2830: ame_util.getMessage(applicationShortNameIn => 'PER',
2831: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2832: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2833: routineNameIn => 'remove',
2834: exceptionNumberIn => errorCode,
2828: errorCode := -20001;
2829: errorMessage :=
2830: ame_util.getMessage(applicationShortNameIn => 'PER',
2831: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2832: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2833: routineNameIn => 'remove',
2834: exceptionNumberIn => errorCode,
2835: exceptionStringIn => errorMessage);
2836: raise_application_error(errorCode,
2838: when inUseException then
2839: rollback;
2840: errorCode := -20001;
2841: errorMessage :=
2842: ame_util.getMessage(applicationShortNameIn => 'PER',
2843: messageNameIn => 'AME_400205_APG_IN_USE');
2844: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2845: routineNameIn => 'remove',
2846: exceptionNumberIn => errorCode,
2840: errorCode := -20001;
2841: errorMessage :=
2842: ame_util.getMessage(applicationShortNameIn => 'PER',
2843: messageNameIn => 'AME_400205_APG_IN_USE');
2844: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2845: routineNameIn => 'remove',
2846: exceptionNumberIn => errorCode,
2847: exceptionStringIn => errorMessage);
2848: raise_application_error(errorCode,
2851: rollback;
2852: if(startDateCursor%isOpen) then
2853: close startDateCursor;
2854: end if;
2855: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2856: routineNameIn => 'remove',
2857: exceptionNumberIn => sqlcode,
2858: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2859: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2854: end if;
2855: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2856: routineNameIn => 'remove',
2857: exceptionNumberIn => sqlcode,
2858: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2859: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2860: tokenNameOneIn => 'NAME',
2861: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2862: || ' ' || sqlerrm);
2862: || ' ' || sqlerrm);
2863: raise;
2864: end remove;
2865: procedure removeApprovalGroupItem(approvalGroupIdIn in integer,
2866: approvalGroupItemsIn in ame_util.idList,
2867: parentVersionStartDateIn in date) as
2868: cursor startDateCursor is
2869: select start_date
2870: from ame_approval_groups
2870: from ame_approval_groups
2871: where
2872: approval_group_id = approvalGroupIdIn and
2873: sysdate between start_date and
2874: nvl(end_date - ame_util.oneSecond, sysdate)
2875: for update;
2876: cursor orderCursor(approvalGroupIdIn in integer) is
2877: select order_number, approval_group_item_id
2878: from ame_approval_group_items
2877: select order_number, approval_group_item_id
2878: from ame_approval_group_items
2879: where approval_group_id = approvalGroupIdIn and
2880: sysdate between start_date and
2881: nvl(end_date - ame_util.oneSecond, sysdate)
2882: for update of order_number
2883: order by order_number;
2884: approvalGroupId ame_approval_groups.approval_group_id%type;
2885: approvalGroupItemCount integer;
2882: for update of order_number
2883: order by order_number;
2884: approvalGroupId ame_approval_groups.approval_group_id%type;
2885: approvalGroupItemCount integer;
2886: approvalGroupItemList ame_util.idList;
2887: currentUserId integer;
2888: errorCode integer;
2889: errorMessage ame_util.longestStringType;
2890: groupDescription ame_approval_groups.description%type;
2885: approvalGroupItemCount integer;
2886: approvalGroupItemList ame_util.idList;
2887: currentUserId integer;
2888: errorCode integer;
2889: errorMessage ame_util.longestStringType;
2890: groupDescription ame_approval_groups.description%type;
2891: groupName ame_approval_groups.name%type;
2892: isStatic ame_approval_groups.is_static%type;
2893: itemOrderNumber integer;
2902: fetch startDateCursor into startDate;
2903: if startDateCursor%notfound then
2904: raise objectVersionNoDataException;
2905: end if;
2906: currentUserId := ame_util.getCurrentUserId;
2907: if parentVersionStartDateIn = startDate then
2908: approvalGroupItemCount := approvalGroupItemsIn.count;
2909: tempIndex := 0;
2910: /* Reindex to set approval group item order numbers in descending order. This will
2931: end_date = processingDate
2932: where
2933: approval_group_item_id = approvalGroupItemList(i) and
2934: processingDate between start_date and
2935: nvl(end_date - ame_util.oneSecond, processingDate);
2936: end loop;
2937: groupName := getName(approvalGroupIdIn => approvalGroupIdIn);
2938: groupDescription := getDescription(approvalGroupIdIn => approvalGroupIdIn);
2939: isStatic := getIsStatic(approvalGroupIdIn => approvalGroupIdIn);
2946: end_date = processingDate
2947: where
2948: approval_group_id = approvalGroupIdIn and
2949: processingDate between start_date and
2950: nvl(end_date - ame_util.oneSecond, processingDate);
2951: approvalGroupId := new(nameIn => groupName,
2952: descriptionIn => groupDescription,
2953: isStaticIn => isStatic,
2954: queryStringIn => queryString,
2957: /* new calls updateDependentGroups, so don't do it here. */
2958: commit;
2959: else
2960: close startDateCursor;
2961: raise ame_util.objectVersionException;
2962: end if;
2963: exception
2964: when ame_util.objectVersionException then
2965: rollback;
2960: close startDateCursor;
2961: raise ame_util.objectVersionException;
2962: end if;
2963: exception
2964: when ame_util.objectVersionException then
2965: rollback;
2966: if(startDateCursor%isOpen) then
2967: close startDateCursor;
2968: end if;
2967: close startDateCursor;
2968: end if;
2969: errorCode := -20001;
2970: errorMessage :=
2971: ame_util.getMessage(applicationShortNameIn => 'PER',
2972: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2973: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2974: routineNameIn => 'removeApprovalGroupItem',
2975: exceptionNumberIn => errorCode,
2969: errorCode := -20001;
2970: errorMessage :=
2971: ame_util.getMessage(applicationShortNameIn => 'PER',
2972: messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2973: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2974: routineNameIn => 'removeApprovalGroupItem',
2975: exceptionNumberIn => errorCode,
2976: exceptionStringIn => errorMessage);
2977: raise_application_error(errorCode,
2982: close startDateCursor;
2983: end if;
2984: errorCode := -20001;
2985: errorMessage :=
2986: ame_util.getMessage(applicationShortNameIn => 'PER',
2987: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2988: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2989: routineNameIn => 'removeApprovalGroupItem',
2990: exceptionNumberIn => errorCode,
2984: errorCode := -20001;
2985: errorMessage :=
2986: ame_util.getMessage(applicationShortNameIn => 'PER',
2987: messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2988: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
2989: routineNameIn => 'removeApprovalGroupItem',
2990: exceptionNumberIn => errorCode,
2991: exceptionStringIn => errorMessage);
2992: raise_application_error(errorCode,
2995: rollback;
2996: if(startDateCursor%isOpen) then
2997: close startDateCursor;
2998: end if;
2999: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3000: routineNameIn => 'removeApprovalGroupItem',
3001: exceptionNumberIn => sqlcode,
3002: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3003: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2998: end if;
2999: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3000: routineNameIn => 'removeApprovalGroupItem',
3001: exceptionNumberIn => sqlcode,
3002: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3003: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3004: tokenNameOneIn => 'NAME',
3005: tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
3006: || ' ' || sqlerrm);
3009: procedure setGroupMembers2(groupIdIn in integer,
3010: effectiveDateIn in date default sysdate,
3011: raiseError in boolean) as
3012: memberIndex integer;
3013: orderNumbers ame_util.idList;
3014: origSystem ame_util.stringType;
3015: origSystemId integer;
3016: parameterNames ame_util.stringList;
3017: parameters ame_util.stringList;
3010: effectiveDateIn in date default sysdate,
3011: raiseError in boolean) as
3012: memberIndex integer;
3013: orderNumbers ame_util.idList;
3014: origSystem ame_util.stringType;
3015: origSystemId integer;
3016: parameterNames ame_util.stringList;
3017: parameters ame_util.stringList;
3018: queryStrings ame_util.longestStringList;
3012: memberIndex integer;
3013: orderNumbers ame_util.idList;
3014: origSystem ame_util.stringType;
3015: origSystemId integer;
3016: parameterNames ame_util.stringList;
3017: parameters ame_util.stringList;
3018: queryStrings ame_util.longestStringList;
3019: tempCount integer;
3020: upperLimit integer;
3013: orderNumbers ame_util.idList;
3014: origSystem ame_util.stringType;
3015: origSystemId integer;
3016: parameterNames ame_util.stringList;
3017: parameters ame_util.stringList;
3018: queryStrings ame_util.longestStringList;
3019: tempCount integer;
3020: upperLimit integer;
3021: begin
3014: origSystem ame_util.stringType;
3015: origSystemId integer;
3016: parameterNames ame_util.stringList;
3017: parameters ame_util.stringList;
3018: queryStrings ame_util.longestStringList;
3019: tempCount integer;
3020: upperLimit integer;
3021: begin
3022: /* Clear the old nonrecursive membership list. */
3041: parameter_name = parameterNames(i) and
3042: parameter = parameters(i);
3043: if(tempCount = 0) then
3044: memberIndex := memberIndex + 1;
3045: if parameterNames(i) = ame_util.approverWfRolesName then
3046: begin
3047: ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => parameters(i),
3048: origSystemOut => origSystem,
3049: origSystemIdOut => origSystemId);
3053: origSystem := null;
3054: origSystemId := null;
3055: else
3056: rollback;
3057: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3058: routineNameIn => 'setGroupMembers2',
3059: exceptionNumberIn => sqlcode,
3060: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3061: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3056: rollback;
3057: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3058: routineNameIn => 'setGroupMembers2',
3059: exceptionNumberIn => sqlcode,
3060: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3061: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3062: tokenNameOneIn => 'NAME',
3063: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
3064: effectiveDateIn => effectiveDateIn))
3092: end loop;
3093: exception
3094: when others then
3095: rollback;
3096: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3097: routineNameIn => 'setGroupMembers2',
3098: exceptionNumberIn => sqlcode,
3099: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3100: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3095: rollback;
3096: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3097: routineNameIn => 'setGroupMembers2',
3098: exceptionNumberIn => sqlcode,
3099: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3100: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3101: tokenNameOneIn => 'NAME',
3102: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
3103: effectiveDateIn => effectiveDateIn))
3117: cursor dependentGroupCursor(groupIdIn in integer) is
3118: select distinct approval_group_id
3119: from ame_approval_group_items
3120: where
3121: parameter_name = ame_util.approverOamGroupId and
3122: parameter = to_char(groupIdIn) and
3123: sysdate between start_date and
3124: nvl(end_date - ame_util.oneSecond, sysdate) ;
3125: groupsToUpdate ame_util.idList;
3120: where
3121: parameter_name = ame_util.approverOamGroupId and
3122: parameter = to_char(groupIdIn) and
3123: sysdate between start_date and
3124: nvl(end_date - ame_util.oneSecond, sysdate) ;
3125: groupsToUpdate ame_util.idList;
3126: currentGroup integer;
3127: upperLimit integer;
3128: processingDate date;
3121: parameter_name = ame_util.approverOamGroupId and
3122: parameter = to_char(groupIdIn) and
3123: sysdate between start_date and
3124: nvl(end_date - ame_util.oneSecond, sysdate) ;
3125: groupsToUpdate ame_util.idList;
3126: currentGroup integer;
3127: upperLimit integer;
3128: processingDate date;
3129: begin
3150: update ame_approval_group_items
3151: set end_date = processingDate
3152: where
3153: approval_group_id = groupsToUpdate(currentGroup) and
3154: parameter_name = ame_util.approverOamGroupId and
3155: parameter = to_char(groupIdIn) and
3156: processingDate between start_date and
3157: nvl(end_date - ame_util.oneSecond, processingDate) ;
3158: end if;
3153: approval_group_id = groupsToUpdate(currentGroup) and
3154: parameter_name = ame_util.approverOamGroupId and
3155: parameter = to_char(groupIdIn) and
3156: processingDate between start_date and
3157: nvl(end_date - ame_util.oneSecond, processingDate) ;
3158: end if;
3159: if(currentGroup > 1 or
3160: not deleteGroupIn) then
3161: setGroupMembers(groupIdIn => groupsToUpdate(currentGroup));
3171: end loop;
3172: exception
3173: when others then
3174: rollback;
3175: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3176: routineNameIn => 'updateDependentGroups',
3177: exceptionNumberIn => sqlcode,
3178: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3179: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3174: rollback;
3175: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
3176: routineNameIn => 'updateDependentGroups',
3177: exceptionNumberIn => sqlcode,
3178: exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
3179: messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
3180: tokenNameOneIn => 'NAME',
3181: tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
3182: || ' ' || sqlerrm);