[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVAL_GROUP_PKG
Source
1 package body ame_approval_group_pkg as
2 /* $Header: ameogrou.pkb 120.2 2011/09/21 14:54:08 prasashe ship $ */
3 /* forward declarations */
4 /*
5 getNestedMembers returns the membership of an approval group, including nested
6 groups, down to the first dynamic group in each nesting recursion. When a
7 dynamic group is encountered, its query string is copied into queryStringOut.
8 If the target group itself is dynamic, its query string is copied into
9 queryStringOut. If effectiveDateIn is not null, setGroupMembers uses the
10 ame_approval_group_items entries with that effective date. (This enables a bug
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 /*
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
23 group with ID groupIdIn from dependent group's item lists.
24 */
25 procedure updateDependentGroups(groupIdIn in integer,
26 deleteGroupIn in boolean default false);
27 /* functions and procedures */
28 function isSeeded(approvalGroupIdIn in integer) return boolean as
29 createdByValue integer;
30 begin
31 select created_by
32 into createdByValue
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);
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 ||
50 ') ' ||
51 sqlerrm);
52 raise;
53 return(true); /* conservative: avoids allowing deletion if might still be in use */
54 end isSeeded;
55
56 function getApprovalGroupItemMaxOrdNum(approvalGroupIdIn in integer) return integer as
57 orderNumber integer;
58 begin
59 select nvl(max(order_number), 0)
60 into orderNumber
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;
70 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
71 routineNameIn => 'getApprovalGroupItemMaxOrdNum',
72 exceptionNumberIn => sqlcode,
73 exceptionStringIn => sqlerrm);
74 raise;
75 return(null);
76 end getApprovalGroupItemMaxOrdNum;
77 function getApprovalGroupMaxOrderNumber(applicationIdIn in integer) return integer as
78 orderNumber integer;
79 begin
80 select nvl(max(order_number), 0)
81 into orderNumber
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;
91 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
92 routineNameIn => 'getApprovalGroupMaxOrderNumber',
93 exceptionNumberIn => sqlcode,
94 exceptionStringIn => sqlerrm);
95 raise;
96 return(null);
97 end getApprovalGroupMaxOrderNumber;
98 function getApprovalGroupOrderNumber(applicationIdIn in integer,
99 approvalGroupIdIn in integer) return integer as
100 orderNumber integer;
101 begin
102 select order_number
103 into orderNumber
104 from ame_approval_group_config
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;
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);
122 raise;
123 return(null);
124 end getApprovalGroupOrderNumber;
125 function getDescription(approvalGroupIdIn in integer) return varchar2 as
126 description ame_approval_groups.description%type;
127 begin
128 select description
129 into description
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;
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);
147 raise;
148 return(null);
149 end getDescription;
150 function getId(nameIn in varchar2) return integer as
151 approvalGroupId integer;
152 begin
153 select approval_group_id
154 into approvalGroupId
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;
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);
172 raise;
173 return(null);
174 end getId;
175 function getItemApprovalGroupId(approvalGroupItemIdIn in integer) return integer as
176 approvalGroupId integer;
177 begin
178 select approval_group_id
179 into approvalGroupId
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;
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);
197 raise;
198 return(null);
199 end getItemApprovalGroupId;
200 function getItemId(approvalGroupIdIn in integer,
201 parameterIn in varchar2,
202 parameterNameIn in varchar2) return integer as
203 itemId integer;
204 begin
205 select approval_group_item_id
206 into itemId
207 from ame_approval_group_items
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;
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);
225 raise;
226 return(null);
227 end getItemId;
228 function getItemParameter(approvalGroupItemIdIn in integer) return varchar2 as
229 parameter ame_approval_group_items.parameter%type;
230 begin
231 select parameter
232 into parameter
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;
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);
250 raise;
251 return(null);
252 end getItemParameter;
253 function getItemParameterName(approvalGroupItemIdIn in integer) return varchar2 as
254 parameterName ame_approval_group_items.parameter_name%type;
255 begin
256 select parameter_name
257 into parameterName
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;
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);
275 raise;
276 return(null);
277 end getItemParameterName;
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;
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);
304 raise;
305 return(null);
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;
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));
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',
345 tokenNameOneIn => 'NAME',
346 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
347 effectiveDateIn => effectiveDateIn))
348 || ' ' || sqlerrm);
349 raise;
350 return(null);
351 end getQueryString;
352 function getIsStatic(approvalGroupIdIn in integer) return varchar2 as
353 isStatic ame_approval_groups.is_static%type;
354 begin
355 select is_static
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;
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);
373 raise;
374 return(null);
375 end getIsStatic;
376 function getChildVersionStartDate(approvalGroupIdIn integer,
377 applicationIdIn in integer) return varchar2 as
378 startDate date;
379 stringStartDate varchar2(50);
380 begin
381 select start_date
382 into startDate
383 from ame_approval_group_config
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
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);
402 raise;
403 return(null);
404 end getChildVersionStartDate;
405 function getParentVersionStartDate(approvalGroupIdIn integer) return varchar2 as
406 startDate date;
407 stringStartDate varchar2(50);
408 begin
409 select start_date
410 into startDate
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
422 routineNameIn => 'getParentVersionStartDate',
419 when others then
420 rollback;
421 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
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);
429 raise;
430 return(null);
431 end getParentVersionStartDate;
432 function getItemOrderNumber(approvalGroupItemIdIn in integer) return integer as
433 orderNumber ame_approval_group_items.order_number%type;
434 begin
435 select order_number
436 into orderNumber
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;
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);
454 raise;
455 return(null);
456 end getItemOrderNumber;
457 function getOrderNumberCount(approvalGroupIdIn in integer,
458 newGroupMemberIn in boolean) return integer as
459 orderCount integer;
460 begin
461 select count(*)
462 into orderCount
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);
472 end if;
473 /* The user is editing the order number, so just return the orderCount. */
474 return(orderCount);
475 exception
476 when others then
477 rollback;
478 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
479 routineNameIn => 'getOrderNumberCount',
483 tokenNameOneIn => 'NAME',
480 exceptionNumberIn => sqlcode,
481 exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
482 messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
484 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
485 || ' ' || sqlerrm);
486 raise;
487 return(null);
488 end getOrderNumberCount;
489 function getVotingRegime(approvalGroupIdIn in integer,
490 applicationIdIn in integer) return varchar2 as
491 votingRegime ame_approval_group_config.voting_regime%type;
492 begin
493 select voting_regime
494 into votingRegime
495 from ame_approval_group_config
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;
505 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
506 routineNameIn => 'getVotingRegime',
507 exceptionNumberIn => sqlcode,
508 exceptionStringIn => sqlerrm);
509 raise;
510 return(null);
511 end getVotingRegime;
512 function groupIsInGroup(groupIdIn in integer,
513 possiblyNestedGroupIdIn in integer) return boolean as
514 cursor groupMemberCursor(approvalGroupIdIn in integer) is
515 select
516 parameter,
517 parameter_name
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
527 tempGroupId := to_number(tempGroup.parameter);
528 if(tempGroupId = possiblyNestedGroupIdIn) then
529 return(true);
530 elsif(groupIsInGroup(groupIdIn => tempGroupId,
531 possiblyNestedGroupIdIn => possiblyNestedGroupIdIn)) then
532 return(true);
533 end if;
534 end if;
535 end loop;
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',
545 tokenNameOneIn => 'NAME',
546 tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
547 || ' ' || sqlerrm);
548 raise;
549 return(true);
550 end groupIsInGroup;
551
552 function hasGroupChanged2(approvalGroupIdIn in integer,
553 nameIn in varchar2 default null,
554 descriptionIn in varchar2 default null,
555 isStaticIn in varchar2 default null,
556 queryStringIn in varchar2 default null) return boolean as
557 tempCount integer;
558 begin
559 select count(*)
560 into tempCount
561 from ame_approval_groups
562 where ame_approval_groups.approval_group_id = approvalGroupIdIn
563 and (nameIn is null or upper(name) = upper(nameIn))
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;
582 return(true);
583 end hasGroupChanged2;
584
585 function hasGroupChanged(approvalGroupIdIn in integer,
586 nameIn in varchar2 default null,
587 descriptionIn in varchar2 default null,
588 isStaticIn in varchar2 default null,
589 queryStringIn in varchar2 default null,
590 orderNumberIn in integer,
591 orderNumberUniqueIn in varchar2,
592 votingRegimeIn in varchar2,
593 applicationIdIn in integer) return boolean as
594 groupHasBeenUpdated boolean;
595 oldOrderNumberUnique boolean;
596 tempCount integer;
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
606 groupHasBeenUpdated := false;
607 end if;
608 select count(*)
609 into tempCount
610 from
611 ame_approval_groups,
612 ame_approval_group_config
613 where
614 ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
615 ame_approval_groups.approval_group_id = approvalGroupIdIn and
616 ame_approval_group_config.application_id = applicationIdIn and
617 ame_approval_group_config.voting_regime = votingRegimeIn and
618 ame_approval_group_config.order_number = orderNumberIn and
619 (nameIn is null or upper(name) = upper(nameIn)) and
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);
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;
640 return(true);
641 end hasGroupChanged;
642 function isInUse(approvalGroupIdIn in integer) return boolean as
643 useCount integer;
644 begin
645 select count(*)
646 into useCount
647 from
648 ame_actions,
649 ame_action_types,
650 ame_action_usages
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
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
668 return(true);
669 end if;
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',
679 tokenNameOneIn => 'NAME',
680 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
681 || ' ' || sqlerrm);
682 raise;
683 return(true);
684 end isInUse;
685 function isStatic(approvalGroupIdIn in integer,
686 effectiveDateIn in date default sysdate) return boolean as
687 isStatic ame_approval_groups.is_static%type;
688 begin
689 select is_static
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);
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',
707 tokenNameOneIn => 'NAME',
708 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn,
709 effectiveDateIn => effectiveDateIn))
710 || ' ' || sqlerrm);
711 raise;
712 return(false);
713 end isStatic;
714 function itemOrderNumberUnique(approvalGroupIdIn in integer,
715 orderNumberIn in integer) return boolean as
716 tempCount integer;
717 begin
718 select count(*)
719 into tempCount
720 from ame_approval_group_items
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;
739 return(false);
740 end itemOrderNumberUnique;
741 function new(nameIn in varchar2,
742 descriptionIn in varchar2,
743 isStaticIn in varchar2 default null,
744 queryStringIn in varchar2 default null,
745 newStartDateIn in date default null,
746 approvalGroupIdIn in integer default null,
747 finalizeIn in boolean default false,
748 updateActionIn in boolean default false) return integer as
749 approvalGroupId integer;
750 actionId ame_actions.action_id%type;
751 actionTypeId ame_action_types.action_type_id%type;
752 actionDescription ame_actions.description%type;
753 createdBy integer;
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;
763 tempCount integer;
764 begin
765 if(nameIn is null or descriptionIn is null) then
766 raise nullNameDescException;
767 end if;
768 processingDate := sysdate;
769 begin
770 select approval_group_id
771 into approvalGroupId
772 from ame_approval_groups
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'));
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;
791 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_groups',
792 columnNameIn => 'description',
793 argumentIn => descriptionIn)) then
794 raise descriptionLengthException;
795 end if;
796 /*
797 If any version of the object has created_by = 1, all versions,
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
807 approvalGroupId := approvalGroupIdIn;
808 select count(*)
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;
818 end if;
819 end if;
820 /* keeps this end date associated with the endDate from proc change */
821 endDate := nvl(newStartDateIn, sysdate) ;
822 /* insert into ame_actions proper values for the approval group */
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;
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 :=
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)
852 last_updated_by = currentUserId,
849 for update of end_date;
850 update ame_actions
851 set
853 last_update_date = processingDate,
854 last_update_login = currentUserId,
855 end_date = processingDate
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,
865 newStartDateIn => processingDate,
866 finalizeIn => true,
867 parentVersionStartDateIn => parentVersionStartDate,
868 actionIdIn => actionId);
869 else
870 actionId := ame_action_pkg.newAction(actionTypeIdIn => actionTypeId,
871 descriptionIn => actionDescription,
872 updateParentObjectIn => true,
873 parameterIn => approvalGroupId,
874 newStartDateIn => processingDate,
875 parentVersionStartDateIn => parentVersionStartDate,
876 finalizeIn => true);
877 end if;
878 end loop;
879 end if;
880 insert into ame_approval_groups(approval_group_id,
881 name,
882 created_by,
883 creation_date,
884 last_updated_by,
885 last_update_date,
886 last_update_login,
887 start_date,
888 end_date,
889 description,
890 query_string,
891 is_static)
892 values(approvalGroupId,
893 nameIn,
894 createdBy,
895 processingDate,
896 currentUserId,
897 processingDate,
898 currentUserId,
899 nvl(newStartDateIn, processingDate),
900 null,
901 descriptionIn,
902 queryStringIn,
903 isStaticIn);
904 updateDependentGroups(groupIdIn => approvalGroupId);
905 if(finalizeIn) then
906 commit;
907 end if;
908 return(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'));
918 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
919 routineNameIn => 'new',
920 exceptionNumberIn => errorCode,
921 exceptionStringIn => errorMessage);
922 raise_application_error(errorCode,
923 errorMessage);
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'));
933 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
934 routineNameIn => 'new',
935 exceptionNumberIn => errorCode,
936 exceptionStringIn => errorMessage);
937 raise_application_error(errorCode,
938 errorMessage);
939 return(null);
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,
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',
960 tokenNameOneIn => 'NAME',
961 tokenValueOneIn => nameIn)
962 || ' ' || sqlerrm);
963 raise;
964 return(null);
965 end new;
966 function newApprovalGroupItem(approvalGroupIdIn in integer,
967 parameterIn in varchar2,
968 parameterNameIn in varchar2,
969 approvalGroupItemIdIn in integer default null,
970 newOrderNumberIn in integer default null,
971 orderNumberUniqueIn in varchar2 default null,
972 oldOrderNumberIn in integer default null,
973 finalizeIn in boolean default false,
974 newStartDateIn in date default null,
975 newEndDateIn in date default null,
976 parentVersionStartDateIn in date) return integer as
977 cursor startDateCursor is
978 select start_date
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;
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;
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;
1001 parameterNameLengthException exception;
1002 startDate date;
1003 tempCount integer;
1004 tempCount2 integer;
1005 updateOnlyGIModified boolean;
1006 processingDate date;
1007 begin
1008 processingDate := sysdate;
1009 open startDateCursor;
1010 fetch startDateCursor into startDate;
1011 if startDateCursor%notfound then
1012 raise objectVersionNoDataException;
1013 end if;
1014 /* error checking */
1015 select count(*)
1016 into tempCount
1017 from ame_approval_group_items
1018 where
1019 (approvalGroupItemIdIn is null or approval_group_item_id <> approvalGroupItemIdIn) and
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',
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;
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;
1044 else
1045 approvalGroupItemId := approvalGroupItemIdIn;
1046 select count(*)
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;
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;
1065 if(oldOrderNumberIn is not null) then
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);
1075 else /* The order number is not unique. */
1076 raise orderNumberException;
1077 end if;
1078 else
1079 update ame_approval_group_items
1080 set
1081 last_updated_by = currentUserId,
1082 last_update_date = newEndDateIn,
1083 last_update_login = currentUserId,
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
1093 newOrderNumber := (newOrderNumberIn - 1);
1094 else
1095 newOrderNumber := newOrderNumberIn;
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;
1105 /*
1106 Check whether the group identified by approvalGroupIdIn G is nested in
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,
1116 possiblyNestedGroupIdIn => to_number(parameterIn)))) then
1120 approval_group_id,
1117 raise badNestedGroupException;
1118 end if;
1119 insert into ame_approval_group_items(approval_group_item_id,
1121 parameter_name,
1122 parameter,
1123 order_number,
1124 created_by,
1125 creation_date,
1126 last_updated_by,
1127 last_update_date,
1128 last_update_login,
1129 start_date,
1130 end_date)
1131 values(approvalGroupItemId,
1132 approvalGroupIdIn,
1133 parameterNameIn,
1134 parameterIn,
1135 newOrderNumber,
1136 createdBy,
1137 newStartDateIn,
1138 currentUserId,
1139 newStartDateIn,
1140 currentUserId,
1141 newStartDateIn,
1142 null);
1143 end if;
1144 else
1145 updateOnlyGIModified := true;
1146 end if;
1147 if(updateOnlyGIModified) then
1148 if(approvalGroupItemIdIn is not null) then
1149 update ame_approval_group_items
1150 set
1151 last_updated_by = currentUserId,
1152 last_update_date = newEndDateIn,
1153 last_update_login = currentUserId,
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
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,
1171 possiblyNestedGroupIdIn => to_number(parameterIn)))) then
1172 raise badNestedGroupException;
1173 end if;
1174 insert into ame_approval_group_items(approval_group_item_id,
1175 approval_group_id,
1176 parameter_name,
1177 parameter,
1178 order_number,
1179 created_by,
1180 creation_date,
1181 last_updated_by,
1182 last_update_date,
1183 last_update_login,
1184 start_date,
1185 end_date)
1186 values(approvalGroupItemId,
1187 approvalGroupIdIn,
1188 parameterNameIn,
1189 parameterIn,
1190 newOrderNumberIn,
1191 createdBy,
1192 nvl(newStartDateIn, processingDate),
1193 currentUserId,
1194 nvl(newStartDateIn, processingDate),
1195 currentUserId,
1196 nvl(newStartDateIn, processingDate),
1197 null);
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);
1207 end if;
1208 end if;
1209 end if;
1210 close startDateCursor;
1211 updateDependentGroups(groupIdIn => approvalGroupIdIn);
1212 if(finalizeIn) then
1213 commit;
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;
1223 if(startDateCursor%isOpen) then
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,
1233 exceptionStringIn => errorMessage);
1234 raise_application_error(errorCode,
1235 errorMessage);
1236 return(null);
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,
1246 exceptionStringIn => errorMessage);
1247 raise_application_error(errorCode,
1248 errorMessage);
1249 return(null);
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,
1259 exceptionStringIn => errorMessage);
1260 raise_application_error(errorCode,
1261 errorMessage);
1262 return(null);
1263 when objectVersionNoDataException then
1264 rollback;
1265 if(startDateCursor%isOpen) then
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,
1275 exceptionStringIn => errorMessage);
1276 raise_application_error(errorCode,
1277 errorMessage);
1278 return(null);
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'));
1288 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1289 routineNameIn => 'newApprovalGroupItem',
1290 exceptionNumberIn => errorCode,
1291 exceptionStringIn => errorMessage);
1292 raise_application_error(errorCode,
1293 errorMessage);
1294 return(null);
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,
1304 exceptionStringIn => errorMessage);
1305 raise_application_error(errorCode,
1306 errorMessage);
1307 return(null);
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'));
1317 ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
1318 routineNameIn => 'newApprovalGroupItem',
1319 exceptionNumberIn => errorCode,
1320 exceptionStringIn => errorMessage);
1321 raise_application_error(errorCode,
1322 errorMessage);
1323 return(null);
1324 when orderNumberException then
1325 rollback;
1326 errorCode := -20001;
1327 errorMessage := 'To make an approval group item''s order number non-unique, ' ||
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',
1337 exceptionNumberIn => errorCode,
1338 exceptionStringIn => errorMessage);
1339 raise_application_error(errorCode,
1340 errorMessage);
1341 when others then
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',
1351 tokenNameOneIn => 'NAME',
1352 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1353 || ' ' || sqlerrm);
1354 raise;
1355 return(null);
1356 end newApprovalGroupItem;
1357 function orderNumberUnique(applicationIdIn in integer,
1358 orderNumberIn in integer) return boolean as
1362 into tempCount
1359 tempCount integer;
1360 begin
1361 select count(*)
1363 from ame_approval_group_config
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;
1382 return(false);
1383 end orderNumberUnique;
1384 procedure change(approvalGroupIdIn in integer,
1385 nameIn in varchar2 default null,
1386 descriptionIn in varchar2 default null,
1387 isStaticIn in varchar2 default null,
1388 queryStringIn in varchar2 default null,
1389 updateActionIn in boolean,
1390 newVersionStartDateIn in date,
1391 finalizeIn in boolean default false) as
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;
1401 endDate := newVersionStartDateIn - ame_util.oneSecond;
1402 newStartDate := newVersionStartDateIn;
1403 update ame_approval_groups
1404 set
1405 last_updated_by = currentUserId,
1406 last_update_date = endDate,
1407 last_update_login = 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,
1417 newStartDateIn => newStartDate,
1418 approvalGroupIdIn => approvalGroupIdIn,
1419 updateActionIn => updateActionIn,
1420 finalizeIn => false);
1421 /* The new function calls updateDependentGroups, so we don't have to do it here. */
1422 if(finalizeIn) then
1423 commit;
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',
1433 tokenNameOneIn => 'NAME',
1434 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1435 || ' ' || sqlerrm);
1436 raise;
1437 end change;
1438 procedure changeGroupAndConfig(approvalGroupIdIn in integer,
1439 nameIn in varchar2 default null,
1440 descriptionIn in varchar2 default null,
1441 isStaticIn in varchar2 default null,
1442 queryStringIn in varchar2 default null,
1443 newVersionStartDateIn in date,
1444 parentVersionStartDateIn in date,
1445 childVersionStartDateIn in date,
1446 orderNumberUniqueIn in varchar2,
1447 orderNumberIn in integer,
1448 votingRegimeIn in varchar2,
1449 applicationIdIn in integer,
1450 finalizeIn in boolean default false) as
1451 cursor startDateCursor is
1452 select start_date
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;
1471 errorMessage ame_util.longestStringType;
1472 name ame_approval_groups.name%type;
1473 objectVersionNoDataException exception;
1474 startDate date;
1475 startDate2 date;
1476 tempCount integer;
1477 updateAction boolean;
1478 begin
1479 /* Try to get a lock on the record. */
1480 open startDateCursor;
1481 fetch startDateCursor into startDate;
1482 if startDateCursor%notfound then
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);
1501 else
1502 name := nameIn;
1503 end if;
1504 if(descriptionIn is null) then
1505 description := getDescription(approvalGroupIdIn => approvalGroupIdIn);
1506 else
1507 description := descriptionIn;
1508 end if;
1509 /* Check to see if name or description has changed. If so, need to
1510 update the ame_actions table. */
1511 if(nameIn <> getName(approvalGroupIdIn => approvalGroupIdIn)) then
1512 updateAction := true;
1513 else
1514 updateAction := false;
1515 end if;
1516
1517 -- If only config data is changed for a seeded group update config table only.Otherwise update both.
1518 if(hasGroupChanged2(approvalGroupIdIn => approvalGroupIdIn,
1519 nameIn => name,
1520 descriptionIn => description,
1521 isStaticIn => isStaticIn,
1522 queryStringIn => queryStringIn) OR (not ame_approval_group_pkg.isSeeded(approvalGroupIdIn => approvalGroupIdIn))) then
1523
1524
1525 ame_approval_group_pkg.change(approvalGroupIdIn => approvalGroupIdIn,
1526 nameIn => name,
1527 descriptionIn => description,
1528 isStaticIn => isStaticIn,
1529 queryStringIn => queryStringIn,
1530 newVersionStartDateIn => newVersionStartDateIn,
1531 updateActionIn => updateAction,
1532 finalizeIn => false);
1533 end if;
1534
1535 ame_approval_group_pkg.changeGroupConfig(approvalGroupIdIn => approvalGroupIdIn,
1536 orderNumberUniqueIn => orderNumberUniqueIn,
1537 orderNumberIn => orderNumberIn,
1538 votingRegimeIn => votingRegimeIn,
1539 applicationIdIn => applicationIdIn,
1540 newVersionStartDateIn => newVersionStartDateIn,
1541 finalizeIn => false);
1542 close startDateCursor2;
1543 close startDateCursor;
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;
1553 if(startDateCursor2%isOpen) then
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,
1563 exceptionStringIn => errorMessage);
1564 raise_application_error(errorCode,
1565 errorMessage);
1566 when objectVersionNoDataException then
1567 rollback;
1568 if(startDateCursor%isOpen) then
1569 close startDateCursor;
1570 end if;
1571 if(startDateCursor2%isOpen) then
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,
1581 exceptionStringIn => errorMessage);
1582 raise_application_error(errorCode,
1583 errorMessage);
1584 when others then
1585 rollback;
1586 if(startDateCursor%isOpen) then
1587 close startDateCursor;
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||
1597 ') ' ||
1598 sqlerrm);
1599 raise;
1600 end changeGroupAndConfig;
1601 procedure changeGroupConfig(applicationIdIn in integer,
1602 approvalGroupIdIn in integer,
1603 orderNumberUniqueIn in varchar2,
1604 orderNumberIn in integer,
1605 votingRegimeIn in varchar2,
1606 newVersionStartDateIn in date,
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;
1616 orderNumberException exception;
1617 updateOnlyAGModified boolean;
1618 begin
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;
1628 endDate := newVersionStartDateIn;
1629 newStartDate := newVersionStartDateIn;
1630 updateOnlyAGModified := false;
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);
1640
1641 else /* The order number is not unique. */
1642 raise orderNumberException;
1643 end if;
1644 else
1645 update ame_approval_group_config
1646 set
1647 last_updated_by = currentUserId,
1648 last_update_date = endDate,
1649 last_update_login = currentUserId,
1650 end_date = endDate
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
1660 newOrderNumber := (orderNumberIn - 1);
1661 else
1662 newOrderNumber := orderNumberIn;
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;
1672 insert into ame_approval_group_config(application_id,
1673 approval_group_id,
1674 voting_regime,
1675 order_number,
1676 created_by,
1677 creation_date,
1678 last_updated_by,
1679 last_update_date,
1680 last_update_login,
1681 start_date,
1682 end_date)
1683 values(applicationIdIn,
1684 approvalGroupIdIn,
1685 votingRegimeIn,
1686 newOrderNumber,
1687 currentUserId,
1688 newStartDate,
1689 currentUserId,
1690 newStartDate,
1691 currentUserId,
1692 newStartDate,
1693 null);
1694 end if;
1695 if(updateOnlyAGModified) then
1696 update ame_approval_group_config
1697 set
1698 last_updated_by = currentUserId,
1699 last_update_date = endDate,
1700 last_update_login = currentUserId,
1701 end_date = endDate
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,
1711 created_by,
1712 creation_date,
1713 last_updated_by,
1714 last_update_date,
1715 last_update_login,
1716 start_date,
1717 end_date)
1718 values(applicationIdIn,
1719 approvalGroupIdIn,
1720 votingRegimeIn,
1721 orderNumberIn,
1722 currentUserId,
1723 newStartDate,
1724 currentUserId,
1725 newStartDate,
1726 currentUserId,
1727 newStartDate,
1728 null);
1729 end if;
1730 if(finalizeIn) then
1731 commit;
1732 end if;
1733 exception
1734 when orderNumberException then
1735 rollback;
1736 errorCode := -20001;
1737 errorMessage := 'To make an approval group item''s order number non-unique, ' ||
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',
1747 exceptionNumberIn => errorCode,
1748 exceptionStringIn => errorMessage);
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',
1758 tokenNameOneIn => 'NAME',
1759 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1760 || ' ' || sqlerrm);
1761 raise;
1762 end changeGroupConfig;
1763 procedure changeApprovalGroupItem(approvalGroupIdIn in integer,
1764 itemIdIn in integer,
1765 parameterIn in varchar2 default null,
1769 parentVersionStartDateIn in date) as
1766 parameterNameIn in varchar2,
1767 newOrderNumberIn in integer,
1768 orderNumberUniqueIn in varchar2 default null,
1770 cursor startDateCursor is
1771 select start_date
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;
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;
1788 newStartDate date;
1789 objectVersionNoDataException exception;
1790 oldOrderNumber integer;
1791 parameter ame_approval_group_items.parameter%type;
1792 parameterName ame_approval_group_items.parameter_name%type;
1793 startDate date;
1794 processingDate date;
1795 tempCount integer;
1796 begin
1797 processingDate := sysdate;
1798 open startDateCursor;
1799 fetch startDateCursor into startDate;
1800 if startDateCursor%notfound then
1801 raise objectVersionNoDataException;
1802 end if;
1803 if(parameterIn is null) then
1804 parameter := getItemParameter(approvalGroupItemIdIn => itemIdIn);
1805 else
1806 parameter := parameterIn;
1807 end if;
1808 if(parameterNameIn is null) then
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;
1818 approvalGroupItemId := newApprovalGroupItem(approvalGroupIdIn => approvalGroupIdIn,
1819 parameterIn => parameter,
1820 parameterNameIn => parameterName,
1821 approvalGroupItemIdIn => itemIdIn,
1822 newOrderNumberIn => newOrderNumberIn,
1823 oldOrderNumberIn => oldOrderNumber,
1824 orderNumberUniqueIn => orderNumberUniqueIn,
1825 newStartDateIn => newStartDate,
1826 newEndDateIn => endDate,
1827 finalizeIn => false,
1828 parentVersionStartDateIn => parentVersionStartDateIn);
1829 groupName := getName(approvalGroupIdIn => approvalGroupIdIn);
1830 groupDescription := getDescription(approvalGroupIdIn => approvalGroupIdIn);
1831 groupIsStatic := getIsStatic(approvalGroupIdIn => approvalGroupIdIn);
1832 groupQueryString := getQueryString(approvalGroupIdIn => approvalGroupIdIn);
1833 update ame_approval_groups
1834 set
1835 last_updated_by = currentUserId,
1836 last_update_date = endDate,
1837 last_update_login = currentUserId,
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,
1847 newStartDateIn => newStartDate,
1848 approvalGroupIdIn => approvalGroupIdIn,
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
1858 rollback;
1859 if(startDateCursor%isOpen) 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,
1869 exceptionStringIn => errorMessage);
1870 raise_application_error(errorCode,
1871 errorMessage);
1872 when others then
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',
1882 tokenNameOneIn => 'NAME',
1883 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
1884 || ' ' || sqlerrm);
1885 raise;
1886 end changeApprovalGroupItem;
1887 procedure decrementGroupItemOrderNumbers(approvalGroupIdIn in integer,
1888 orderNumberIn in integer,
1889 finalizeIn in boolean default false) as
1890 cursor orderNumberCursor is
1891 select approval_group_item_id, order_number
1892 from ame_approval_group_items
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;
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;
1910 fetch orderNumberCursor bulk collect
1911 into approvalGroupItemIds, orderNumbers;
1912 close orderNumberCursor;
1913 for i in 1 .. approvalGroupItemIds.count loop
1914 parameter :=
1915 getItemParameter(approvalGroupItemIdIn => approvalGroupItemIds(i));
1916 parameterName :=
1917 getItemParameterName(approvalGroupItemIdIn => approvalGroupItemIds(i));
1918 update ame_approval_group_items
1919 set
1920 last_updated_by = currentUserId,
1921 last_update_date = processingDate,
1922 last_update_login = currentUserId,
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,
1932 order_number,
1933 created_by,
1934 creation_date,
1935 last_updated_by,
1936 last_update_date,
1937 last_update_login,
1938 start_date,
1939 end_date)
1940 values(approvalGroupItemIds(i),
1941 approvalGroupIdIn,
1942 parameterName,
1943 parameter,
1944 (orderNumbers(i) - 1),
1945 currentUserId,
1946 processingDate,
1947 currentUserId,
1948 processingDate,
1949 currentUserId,
1950 processingDate,
1951 null);
1952 end loop;
1953 if(finalizeIn) then
1954 commit;
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;
1964 end decrementGroupItemOrderNumbers;
1965 procedure decrementGroupOrderNumbers(applicationIdIn in integer,
1966 orderNumberIn in integer,
1967 finalizeIn in boolean default false) as
1968 cursor orderNumberCursor is
1969 select approval_group_id, order_number
1970 from ame_approval_group_config
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;
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;
1988 close orderNumberCursor;
1989 for i in 1 .. approvalGroupIds.count loop
1990 votingRegime := getVotingRegime(approvalGroupIdIn => approvalGroupIds(i),
1991 applicationIdIn => applicationIdIn);
1992 update ame_approval_group_config
1993 set
1994 last_updated_by = currentUserId,
1995 last_update_date = processingDate,
1996 last_update_login = currentUserId,
1997 end_date = processingDate
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,
2007 created_by,
2008 creation_date,
2009 last_updated_by,
2010 last_update_date,
2011 last_update_login,
2012 start_date,
2013 end_date)
2014 values(applicationIdIn,
2015 approvalGroupIds(i),
2016 votingRegime,
2017 (orderNumbers(i) - 1),
2018 currentUserId,
2019 processingDate,
2020 currentUserId,
2021 processingDate,
2022 currentUserId,
2023 processingDate,
2024 null);
2025 end loop;
2026 if(finalizeIn) then
2027 commit;
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;
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
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
2053 /*
2054 Check whether the group identified by groupIdIn G is nested in
2055 the group identified by tempGroup P. If so, we would have a loop in
2056 the groups: P contains G, and G would contain P, which would then
2057 contain G, . . . Also check whether P is already in G.
2058 */
2059 if(groupIdIn <> tempGroup.approval_group_id and
2060 not groupIsInGroup(groupIdIn => tempGroup.approval_group_id,
2061 possiblyNestedGroupIdIn => groupIdIn) and
2062 not groupIsInGroup(groupIdIn => groupIdIn,
2063 possiblyNestedGroupIdIn => tempGroup.approval_group_id)) then
2064 tempIndex := tempIndex + 1;
2065 allowedNestedGroupIdsOut(tempIndex) := to_char(tempGroup.approval_group_id);
2066 allowedNestedGroupNamesOut(tempIndex) := tempGroup.name;
2067 end if;
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',
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,
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
2094 sysdate between start_date and
2091 from ame_approval_group_items
2092 where
2093 approval_group_id = approvalGroupIdIn 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;
2100 tempItemId integer;
2101 tempLastName per_all_people_f.last_name%type;
2102 tempName ame_approval_groups.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;
2112 for tempItem in itemCursor(approvalGroupIdIn) loop
2113 tempItemId := tempItem.approval_group_item_id;
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));
2123 else
2124 ame_approver_type_pkg.getApproverDescAndValidity(
2125 nameIn => tempParameter,
2126 descriptionOut => approverDesc,
2127 validityOut => approverValid);
2128 if(not approverValid) then
2129 invalidMembersOut := true;
2130 end if;
2131 descriptionListOut(tempIndex) := orderListOut(tempIndex) || '. ' || approverDesc;
2132 end if;
2133 tempIndex := tempIndex + 1;
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',
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
2152 cursor groupCursor is
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
2162 groupListOut(tempIndex) := tempGroup.approval_group_id;
2163 tempIndex := tempIndex + 1;
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;
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;
2190 begin
2191 tempIndex := 1;
2192 for tempGroup in groupCursor loop
2193 groupListOut(tempIndex) := tempGroup.approval_group_id;
2194 tempIndex := 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;
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;
2222 recursionParameters ame_util.longStringList;
2223 recursionUpperLimit integer;
2224 tempIndex integer;
2225 begin
2226 select
2227 is_static,
2228 query_string
2229 into
2230 isStatic,
2231 queryString
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
2241 /* recursion */
2242 getGroupMembers(approvalGroupIdIn => to_number(tempMember.parameter),
2243 memberTypesOut => recursionParameterNames,
2244 memberIdsOut => recursionParameters);
2245 recursionUpperLimit := recursionParameterNames.count;
2246 for j in 1 .. recursionUpperLimit loop
2247 tempIndex := tempIndex + 1;
2248 memberTypesOut(tempIndex) := recursionParameterNames(j);
2249 memberIdsOut(tempIndex) := recursionParameters(j);
2250 end loop;
2251 else
2252 tempIndex := tempIndex + 1;
2253 memberTypesOut(tempIndex) := tempMember.parameter_name;
2254 memberIdsOut(tempIndex) := tempMember.parameter;
2255 end if;
2256 end loop;
2257 else /* The group uses its dynamic list. */
2258 memberTypesOut.delete;
2259 memberIdsOut.delete;
2260 return;
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',
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
2279 cursor itemCursor(approvalGroupIdIn in integer) is
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;
2289 tempItemId integer;
2290 tempParameter ame_approval_group_items.parameter%type;
2291 begin
2292 tempIndex := 1;
2293 for tempItem in itemCursor(approvalGroupIdIn) loop
2294 tempItemId := tempItem.approval_group_item_id;
2295 tempParameter := tempItem.parameter;
2296 if(not ame_approver_type_pkg.validateApprover(nameIn => tempParameter)) then
2297 itemListOut(tempIndex) := tempItemId;
2298 tempIndex := tempIndex + 1;
2299 end if;
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,
2310 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
2307 exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
2308 messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
2309 tokenNameOneIn => 'NAME',
2311 || ' ' || sqlerrm);
2312 itemListOut := ame_util.emptyIdList;
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,
2322 effectiveDateIn in date) is
2323 select
2324 parameter,
2325 parameter_name,
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;
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,
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);
2347 return;
2348 end if;
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;
2358 recursionOrderNumbers.delete;
2359 recursionQueries.delete;
2360 getNestedMembers(groupIdIn => to_number(tempMember.parameter),
2361 effectiveDateIn => effectiveDateIn,
2362 parameterNamesOut => recursionParameterNames,
2363 parametersOut => recursionParameters,
2364 orderNumbersOut => recursionOrderNumbers,
2365 queryStringsOut => recursionQueries);
2366 upperLimit := recursionParameters.count;
2367 for i in 1 .. upperLimit loop
2368 outputIndex := outputIndex + 1;
2369 parameterNamesOut(outputIndex) := recursionParameterNames(i);
2370 parametersOut(outputIndex) := recursionParameters(i);
2371 orderNumbersOut(outputIndex) := recursionOrderNumbers(i);
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);
2381 end if;
2382 else
2383 outputIndex := outputIndex + 1;
2384 parameterNamesOut(outputIndex) := tempMember.parameter_name;
2385 parametersOut(outputIndex) := tempMember.parameter;
2386 orderNumbersOut(outputIndex) := tempMember.order_number;
2387 queryStringsOut(outputIndex) := null;
2388 end if;
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',
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;
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
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;
2421 for getOrderNumberRec in getOrderNumbersCursor(approvalGroupIdIn => approvalGroupIdIn) loop
2422 orderNumbersOut(tempIndex) := to_char(getOrderNumberRec.order_number);
2423 tempIndex := 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',
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,
2441 approvalGroupItemIdIn in integer,
2442 orderNumberIn in integer,
2443 finalizeIn in boolean default false) as
2444 cursor orderNumberCursor(approvalGroupIdIn in integer,
2445 approvalGroupItemIdIn in integer,
2449 where
2446 orderNumberIn in integer) is
2447 select approval_group_Item_id, order_number
2448 from ame_approval_group_items
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;
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);
2468 fetch orderNumberCursor bulk collect
2469 into approvalGroupItemIds, orderNumbers;
2470 close orderNumberCursor;
2471 for i in 1 .. approvalGroupItemIds.count loop
2472 parameter := getItemParameter(approvalGroupItemIdIn => approvalGroupItemIds(i));
2473 parameterName := getItemParameterName(approvalGroupItemIdIn => approvalGroupItemIds(i));
2474 update ame_approval_group_items
2475 set
2476 last_updated_by = currentUserId,
2477 last_update_date = processingDate,
2478 last_update_login = currentUserId,
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,
2488 order_number,
2489 created_by,
2490 creation_date,
2491 last_updated_by,
2492 last_update_date,
2493 last_update_login,
2494 start_date,
2495 end_date)
2496 values(approvalGroupItemIds(i),
2497 approvalGroupIdIn,
2498 parameterName,
2499 parameter,
2500 (orderNumbers(i) + 1),
2501 currentUserId,
2502 processingDate,
2503 currentUserId,
2504 processingDate,
2505 currentUserId,
2506 processingDate,
2507 null);
2508 end loop;
2509 if(finalizeIn) then
2510 commit;
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;
2520 end incrementGroupItemOrderNumbers;
2521 procedure incrementGroupOrderNumbers(applicationIdIn in integer,
2522 approvalGroupIdIn in integer,
2523 orderNumberIn in integer,
2524 finalizeIn in boolean default false) as
2525 cursor orderNumberCursor is
2526 select approval_group_id, order_number
2527 from ame_approval_group_config
2528 where
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;
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;
2546 close orderNumberCursor;
2547 for i in 1 .. approvalGroupIds.count loop
2548 votingRegime := getVotingRegime(approvalGroupIdIn => approvalGroupIds(i),
2549 applicationIdIn => applicationIdIn);
2550 update ame_approval_group_config
2551 set
2552 last_updated_by = currentUserId,
2553 last_update_date = processingDate,
2554 last_update_login = currentUserId,
2555 end_date = processingDate
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,
2565 created_by,
2569 last_update_login,
2566 creation_date,
2567 last_updated_by,
2568 last_update_date,
2570 start_date,
2571 end_date)
2572 values(applicationIdIn,
2573 approvalGroupIds(i),
2574 votingRegime,
2575 (orderNumbers(i) + 1),
2576 currentUserId,
2577 processingDate,
2578 currentUserId,
2579 processingDate,
2580 currentUserId,
2581 processingDate,
2582 null);
2583 end loop;
2584 if(finalizeIn) then
2585 commit;
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
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;
2612 maxOrderNumber integer;
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;
2622 fetch applicationIdCursor bulk collect
2623 into
2624 applicationIds;
2625 close applicationIdCursor;
2626 for i in 1 .. applicationIds.count loop
2627 if(applicationIds(i) = applicationIdIn) then
2628 applicationId := applicationIds(i);
2629 orderNumber := orderNumberIn;
2630 else
2631 applicationId := applicationIds(i);
2632 select count(*)
2633 into tempCount
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)
2643 into orderNumber
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,
2653 voting_regime,
2654 order_number,
2655 created_by,
2656 creation_date,
2657 last_updated_by,
2658 last_update_date,
2659 last_update_login,
2660 start_date,
2661 end_date)
2662 values(applicationId,
2663 approvalGroupIdIn,
2664 votingRegimeIn,
2665 orderNumber,
2666 currentUserId,
2667 processingDate,
2668 currentUserId,
2669 processingDate,
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);
2679 end if;
2680 end if;
2681 if(finalizeIn) then
2682 commit;
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;
2695 select start_date
2692 procedure remove(approvalGroupIdIn in integer,
2693 parentVersionStartDateIn in date) as
2694 cursor startDateCursor is
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
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;
2714 objectVersionNoDataException exception;
2715 orderNumber integer;
2716 startDate date;
2717 processingDate date;
2718 begin
2719 processingDate := sysdate;
2720 open startDateCursor;
2721 fetch startDateCursor into startDate;
2722 if startDateCursor%notfound then
2723 raise objectVersionNoDataException;
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;
2733 close applicationIdCursor;
2734 for i in 1 .. applicationIds.count loop
2735 select order_number
2736 into orderNumber
2737 from ame_approval_group_config
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),
2747 orderNumberIn => orderNumber,
2748 finalizeIn => false);
2749 end if;
2750 end loop;
2751 /* End-date approval group itself.*/
2752 update ame_approval_groups
2753 set
2754 last_updated_by = currentUserId,
2755 last_update_date = processingDate,
2756 last_update_login = currentUserId,
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,
2766 last_update_date = processingDate,
2767 last_update_login = 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,
2777 last_update_date = processingDate,
2778 last_update_login = 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,
2788 last_update_date = processingDate,
2789 last_update_login = 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 */
2799 updateDependentGroups(groupIdIn => approvalGroupIdIn,
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
2809 rollback;
2810 if(startDateCursor%isOpen) then
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,
2823 when objectVersionNoDataException then
2820 exceptionStringIn => errorMessage);
2821 raise_application_error(errorCode,
2822 errorMessage);
2824 rollback;
2825 if(startDateCursor%isOpen) then
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,
2835 exceptionStringIn => errorMessage);
2836 raise_application_error(errorCode,
2837 errorMessage);
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,
2847 exceptionStringIn => errorMessage);
2848 raise_application_error(errorCode,
2849 errorMessage);
2850 when others then
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',
2860 tokenNameOneIn => 'NAME',
2861 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
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
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
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;
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;
2894 objectVersionNoDataException exception;
2895 queryString ame_approval_groups.query_string%type;
2896 startDate date;
2897 tempIndex integer;
2898 processingDate date;
2899 begin
2900 processingDate := sysdate;
2901 open startDateCursor;
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
2911 prevent unnecessary reordering in the decrementGroupItemOrderNumbers routine below. */
2912 for i in 1 .. approvalGroupItemCount loop
2913 approvalGroupItemList(i) := approvalGroupItemsIn(approvalGroupItemCount - tempIndex);
2914 tempIndex := (tempIndex + 1);
2915 end loop;
2916 for i in 1 .. approvalGroupItemCount loop
2917 itemOrderNumber :=
2918 getItemOrderNumber(approvalGroupItemIdIn => approvalGroupItemList(i));
2919 if(itemOrderNumberUnique(approvalGroupIdIn => approvalGroupIdIn,
2920 orderNumberIn => itemOrderNumber)) then
2921 /* subtract 1 from the order number for those above the one being deleted */
2922 decrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
2923 orderNumberIn => itemOrderNumber,
2924 finalizeIn => false);
2925 end if;
2926 update ame_approval_group_items
2927 set
2928 last_updated_by = currentUserId,
2929 last_update_date = processingDate,
2930 last_update_login = currentUserId,
2931 end_date = processingDate
2932 where
2933 approval_group_item_id = approvalGroupItemList(i) and
2937 groupName := getName(approvalGroupIdIn => approvalGroupIdIn);
2934 processingDate between start_date and
2935 nvl(end_date - ame_util.oneSecond, processingDate);
2936 end loop;
2938 groupDescription := getDescription(approvalGroupIdIn => approvalGroupIdIn);
2939 isStatic := getIsStatic(approvalGroupIdIn => approvalGroupIdIn);
2940 queryString := getQueryString(approvalGroupIdIn => approvalGroupIdIn);
2941 update ame_approval_groups
2942 set
2943 last_updated_by = currentUserId,
2944 last_update_date = processingDate,
2945 last_update_login = currentUserId,
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,
2955 approvalGroupIdIn => approvalGroupIdIn);
2956 close startDateCursor;
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;
2966 if(startDateCursor%isOpen) then
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,
2976 exceptionStringIn => errorMessage);
2977 raise_application_error(errorCode,
2978 errorMessage);
2979 when objectVersionNoDataException then
2980 rollback;
2981 if(startDateCursor%isOpen) then
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,
2991 exceptionStringIn => errorMessage);
2992 raise_application_error(errorCode,
2993 errorMessage);
2994 when others then
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',
3004 tokenNameOneIn => 'NAME',
3005 tokenValueOneIn => getName(approvalGroupIdIn => approvalGroupIdIn))
3006 || ' ' || sqlerrm);
3007 raise;
3008 end removeApprovalGroupItem;
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;
3018 queryStrings ame_util.longestStringList;
3019 tempCount integer;
3020 upperLimit integer;
3021 begin
3022 /* Clear the old nonrecursive membership list. */
3023 delete from ame_approval_group_members
3024 where approval_group_id = groupIdIn;
3025 /* Rebuid the nonrecursive membership list. */
3026 getNestedMembers(groupIdIn => groupIdIn,
3027 effectiveDateIn => effectiveDateIn,
3028 parameterNamesOut => parameterNames,
3029 parametersOut => parameters,
3030 orderNumbersOut => orderNumbers,
3031 queryStringsOut => queryStrings);
3032 upperLimit := parameters.count;
3033 /* Only insert members that aren't already there. */
3034 memberIndex := 0;
3035 for i in 1 .. upperLimit loop
3036 select count(*)
3037 into tempCount
3038 from ame_approval_group_members
3039 where
3040 approval_group_id = groupIdIn and
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,
3052 if not raiseError then
3049 origSystemIdOut => origSystemId);
3050 exception
3051 when others then
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',
3062 tokenNameOneIn => 'NAME',
3063 tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
3064 effectiveDateIn => effectiveDateIn))
3065 || ' ' || sqlerrm);
3066 raise;
3067 end if;
3068 end;
3069 else
3070 origSystem := null;
3071 origSystemId := null;
3072 end if;
3073 insert into ame_approval_group_members(
3074 approval_group_id,
3075 parameter_name,
3076 parameter,
3077 orig_system,
3078 orig_system_id,
3079 query_string,
3080 order_number,
3081 approval_group_members_id)
3082 values(
3083 groupIdIn,
3084 parameterNames(i),
3085 parameters(i),
3086 origSystem,
3087 origSystemId,
3088 queryStrings(i),
3089 orderNumbers(i),
3090 ame_approval_group_members_s.nextval);
3091 end if;
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',
3101 tokenNameOneIn => 'NAME',
3102 tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn,
3103 effectiveDateIn => effectiveDateIn))
3104 || ' ' || sqlerrm);
3105 raise;
3106 end setGroupMembers2;
3107 procedure setGroupMembers(groupIdIn in integer,
3108 effectiveDateIn in date default sysdate) as
3109 begin
3110 setGroupMembers2(groupIdIn => groupIdIn,
3111 effectiveDateIn => effectiveDateIn,
3112 raiseError => true
3113 );
3114 end setGroupMembers;
3115 procedure updateDependentGroups(groupIdIn in integer,
3116 deleteGroupIn in boolean default false) as
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;
3126 currentGroup integer;
3127 upperLimit integer;
3128 processingDate date;
3129 begin
3130 /*
3131 The following loop treats groupsToUpdate as a first-in, first-out queue.
3132 We enter the loop with the group identified by groupIdIn as the first
3133 (and so far only) group in the queue. The loop updates the next group
3134 in the queue and adds all of the groups that contain it to the end of
3135 the queue. In this fashion all of a given group's dependents are updated
3136 before any of their dependents are updated, etc.
3137 */
3138 processingDate := sysdate;
3139 groupsToUpdate(1) := groupIdIn;
3140 currentGroup := 1;
3141 upperLimit := 1;
3142 loop
3143 if(deleteGroupIn and currentGroup > 1) then
3144 /*
3145 Delete the target group (groupIdIn) from the item list of the current group.
3146 (Don't do it for currentGroup = 1 because the group is never a member of itself.)
3147 The call to setGroupMembers below updates ame_approval_group_members for dependent
3148 groups.
3149 */
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;
3159 if(currentGroup > 1 or
3160 not deleteGroupIn) then
3161 setGroupMembers(groupIdIn => groupsToUpdate(currentGroup));
3162 end if;
3163 for tempGroup in dependentGroupCursor(groupIdIn => groupsToUpdate(currentGroup)) loop
3164 upperLimit := upperLimit + 1;
3165 groupsToUpdate(upperLimit) := tempGroup.approval_group_id;
3166 end loop;
3167 currentGroup := currentGroup + 1;
3168 if(currentGroup > upperLimit) then
3169 exit;
3170 end if;
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',
3180 tokenNameOneIn => 'NAME',
3181 tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
3182 || ' ' || sqlerrm);
3183 raise;
3184 end updateDependentGroups;
3185 end ame_approval_group_pkg;