[Home] [Help]
PACKAGE BODY: APPS.AME_ACTION_PKG
Source
1 package body ame_action_pkg as
2 /* $Header: ameoacti.pkb 120.1 2006/12/26 12:59:25 avarri noship $ */
3 function actionTypeIsInUse(actionTypeIdIn in integer) return boolean as
4 useCount integer;
5 begin
6 select count(*)
7 into useCount
8 from ame_actions
9 where
10 action_type_id = actionTypeIdIn and
11 sysdate between start_date and
12 nvl(end_date - ame_util.oneSecond, sysdate) ;
13 if(useCount > 0) then
14 return(true);
15 end if;
16 return(false);
17 exception
18 when others then
19 rollback;
20 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
21 routineNameIn => 'actionTypeIsInUse',
22 exceptionNumberIn => sqlcode,
23 exceptionStringIn => '(action type ID ' ||
24 actionTypeIdIn||
25 ') ' ||
26 sqlerrm);
27 raise;
28 return(true); /* conservative: avoids allowing deletion if might still be in use */
29 end actionTypeIsInUse;
30 function getActionTypeDescQuery(actionTypeIdIn in integer) return varchar2 as
31 descriptionQuery ame_action_types.description_query%type;
32 begin
33 select description_query
34 into descriptionQuery
35 from ame_action_types
36 where
37 action_type_id = actionTypeIdIn and
38 sysdate between start_date and
39 nvl(end_date - ame_util.oneSecond, sysdate) ;
40 return(descriptionQuery);
41 exception
42 when others then
43 rollback;
44 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
45 routineNameIn => 'getActionTypeDescQuery',
46 exceptionNumberIn => sqlcode,
47 exceptionStringIn => '(action type ID ' ||
48 actionTypeIdIn||
49 ') ' ||
50 sqlerrm);
51 raise;
52 return(null);
53 end getActionTypeDescQuery;
54 function getActionTypeDynamicDesc(actionTypeIdIn in integer) return varchar2 as
55 dynamicDescription ame_action_types.dynamic_description%type;
56 begin
57 select dynamic_description
58 into dynamicDescription
59 from ame_action_types
60 where
61 action_type_id = actionTypeIdIn and
62 sysdate between start_date and
63 nvl(end_date - ame_util.oneSecond, sysdate) ;
64 return(dynamicDescription);
65 exception
66 when others then
67 rollback;
68 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
69 routineNameIn => 'getActionTypeDynamicDesc',
70 exceptionNumberIn => sqlcode,
71 exceptionStringIn => '(action type ID ' ||
72 actionTypeIdIn||
73 ') ' ||
74 sqlerrm);
75 raise;
76 return(null);
77 end getActionTypeDynamicDesc;
78 function getActionTypeIdById(actionIdIn in integer) return integer as
79 actionTypeId integer;
80 begin
81 select action_type_id
82 into actionTypeId
83 from ame_actions
84 where
85 action_id = actionIdIn and
86 sysdate between start_date and
87 nvl(end_date - ame_util.oneSecond, sysdate) ;
88 return(actionTypeId);
89 exception
90 when others then
91 rollback;
92 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
93 routineNameIn => 'getActionTypeIdById',
94 exceptionNumberIn => sqlcode,
95 exceptionStringIn => '(action ID ' ||
96 actionIdIn||
97 ') ' ||
98 sqlerrm);
99 raise;
100 return(null);
101 end getActionTypeIdById;
102 function getActionTypeIdByName(actionTypeNameIn in varchar2) return integer as
103 actionTypeId integer;
104 begin
105 select action_type_id
106 into actionTypeId
107 from ame_action_types
108 where
109 upper(name) = upper(actionTypeNameIn) and
110 sysdate between start_date and
111 nvl(end_date - ame_util.oneSecond, sysdate) ;
112 return(actionTypeId);
113 exception
114 when others then
115 rollback;
116 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
117 routineNameIn => 'getActionTypeIdByName',
118 exceptionNumberIn => sqlcode,
119 exceptionStringIn => '(action name ' ||
120 actionTypeNameIn||
121 ') ' ||
122 sqlerrm);
123 raise;
124 return(null);
125 end getActionTypeIdByName;
126 function getActionTypeDescription(actionTypeIdIn in integer) return varchar2 as
127 description ame_action_types.description%type;
128 begin
129 select description
130 into description
131 from ame_action_types
132 where
133 action_type_id = actionTypeIdIn and
134 sysdate between start_date and
135 nvl(end_date - ame_util.oneSecond, sysdate) ;
136 return(description);
137 exception
138 when others then
139 rollback;
140 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
141 routineNameIn => 'getActionTypeDescription',
142 exceptionNumberIn => sqlcode,
143 exceptionStringIn => '(action type ID ' ||
144 actionTypeIdIn||
145 ') ' ||
146 sqlerrm);
147 raise;
148 return(null);
149 end getActionTypeDescription;
150 function getActionTypeMaxOrderNumber(applicationIdIn in integer,
151 ruleTypeIn in integer) return integer as
152 orderNumber integer;
153 begin
154 select max(ame_action_type_config.order_number)
155 into orderNumber
156 from ame_action_type_config,
157 ame_action_type_usages,
158 ame_action_types
159 where
160 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
161 ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
162 ame_action_type_usages.rule_type = ruleTypeIn and
163 ame_action_type_config.application_id = applicationIdIn and
164 sysdate between ame_action_type_config.start_date and
165 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
166 sysdate between ame_action_type_usages.start_date and
167 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
168 sysdate between ame_action_types.start_date and
169 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
170 return(orderNumber);
171 exception
172 when others then
173 rollback;
174 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
175 routineNameIn => 'getActionTypeMaxOrderNumber',
176 exceptionNumberIn => sqlcode,
177 exceptionStringIn => sqlerrm);
178 raise;
179 return(null);
180 end getActionTypeMaxOrderNumber;
181 function getActionTypeName(actionTypeIdIn in integer) return varchar2 as
182 name ame_action_types.name%type;
183 begin
184 if(actionTypeIdIn = ame_util.nullInsertionActionTypeId) then
185 return('no action type');
186 end if;
187 select name
188 into name
189 from ame_action_types
190 where
191 action_type_id = actionTypeIdIn and
192 sysdate between start_date and
193 nvl(end_date - ame_util.oneSecond, sysdate) ;
194 return(name);
195 exception
196 when others then
197 rollback;
198 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
199 routineNameIn => 'getActionTypeName',
200 exceptionNumberIn => sqlcode,
201 exceptionStringIn => '(action type ID ' ||
202 actionTypeIdIn||
203 ') ' ||
204 sqlerrm);
205 raise;
206 return(null);
207 end getActionTypeName;
208 function getActionTypeNameByActionId(actionIdIn in integer) return varchar2 as
209 name ame_action_types.name%type;
210 begin
211 select ame_action_types.name
212 into name
213 from ame_actions,
214 ame_action_types
215 where
216 ame_actions.action_type_id = ame_action_types.action_type_id and
217 action_id = actionIdIn and
218 sysdate between ame_actions.start_date and
219 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
220 sysdate between ame_action_types.start_date and
221 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
222 return(name);
223 exception
224 when others then
225 rollback;
226 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
227 routineNameIn => 'getActionTypeNameByActionId',
228 exceptionNumberIn => sqlcode,
229 exceptionStringIn => '(action ID ' ||
230 actionIdIn||
231 ') ' ||
232 sqlerrm);
233 raise;
234 return(null);
235 end getActionTypeNameByActionId;
236 function getActionTypeProcedureName(actionTypeIdIn in integer) return varchar2 as
237 procedureName ame_action_types.procedure_name%type;
238 begin
239 select procedure_name
240 into procedureName
241 from ame_action_types
242 where
243 action_type_id = actionTypeIdIn and
244 sysdate between start_date and
245 nvl(end_date - ame_util.oneSecond, sysdate) ;
246 return(procedureName);
247 exception
248 when others then
249 rollback;
250 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
251 routineNameIn => 'getActionTypeProcedureName',
252 exceptionNumberIn => sqlcode,
253 exceptionStringIn => '(action type ID ' ||
254 actionTypeIdIn||
255 ') ' ||
256 sqlerrm);
257 raise;
258 return(null);
259 end getActionTypeProcedureName;
260 function getActionTypeOrderNumber(applicationIdIn in integer,
261 actionTypeIdIn in integer) return integer as
262 orderNumber integer;
263 begin
264 select order_number
265 into orderNumber
266 from ame_action_type_config
267 where
268 action_type_id = actionTypeIdIn and
269 application_id = applicationIdIn and
270 sysdate between start_date and
271 nvl(end_date - ame_util.oneSecond, sysdate) ;
272 return(orderNumber);
273 exception
274 when others then
275 rollback;
276 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
277 routineNameIn => 'getActionTypeOrderNumber',
278 exceptionNumberIn => sqlcode,
279 exceptionStringIn => '(action type ID ' ||
280 actionTypeIdIn||
281 ') ' ||
282 sqlerrm);
283 raise;
284 return(null);
285 end getActionTypeOrderNumber;
286 function getActionTypeCreatedBy(actionTypeIdIn in integer) return integer as
287 createdBy integer;
288 begin
289 select created_by
290 into createdBy
291 from ame_action_types
292 where
293 action_type_id = actionTypeIdIn and
294 sysdate between start_date and
295 nvl(end_date - ame_util.oneSecond, sysdate) ;
296 return(createdBy);
297 exception
298 when others then
299 rollback;
300 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
301 routineNameIn => 'getActionTypeCreatedBy',
302 exceptionNumberIn => sqlcode,
303 exceptionStringIn => '(action type ID ' ||
307 raise;
304 actionTypeIdIn||
305 ') ' ||
306 sqlerrm);
308 return(null);
309 end getActionTypeCreatedBy;
310 function getAllowedRuleType(actionTypeIdIn in integer) return integer as
311 ruleType integer;
312 tempCount integer;
313 begin
314 select count(*)
315 into tempCount
316 from ame_action_type_usages
317 where
318 action_type_id = actionTypeIdIn and
319 sysdate between start_date and
320 nvl(end_date - ame_util.oneSecond, sysdate);
321 if(tempCount > 1) then
322 /* authority and exception rule types are mapped to the action type */
323 /* return chain of authority */
324 return(ame_util.authorityRuleType);
325 else
326 select rule_type
327 into ruleType
328 from ame_action_type_usages
329 where
330 action_type_id = actionTypeIdIn and
331 sysdate between start_date and
332 nvl(end_date - ame_util.oneSecond, sysdate);
333 return(ruleType);
334 end if;
335 exception
336 when others then
337 rollback;
338 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
339 routineNameIn => 'getAllowedRuleType',
340 exceptionNumberIn => sqlcode,
341 exceptionStringIn => sqlerrm);
342 raise;
343 return(null);
344 end getAllowedRuleType;
345 function getAllowedRuleTypeLabel(ruleTypeIn in integer) return varchar2 as
346 begin
347 if(ruleTypeIn = ame_util.preListGroupRuleType) then
348 return(ame_util.getLabel(ame_util.perFndAppId,'AME_PRE_APPROVAL'));
349 elsif(ruleTypeIn in(ame_util.authorityRuleType,
350 ame_util.exceptionRuleType)) then
351 return(ame_util.getLabel(ame_util.perFndAppId,'AME_CHAIN_OF_AUTHORITY'));
352 elsif(ruleTypeIn = ame_util.listModRuleType) then
353 return(ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_MODIFICATION2'));
354 elsif(ruleTypeIn = ame_util.substitutionRuleType) then
355 return(ame_util.getLabel(ame_util.perFndAppId,'AME_SUBSTITUTION'));
356 elsif(ruleTypeIn = ame_util.postListGroupRuleType) then
357 return(ame_util.getLabel(ame_util.perFndAppId,'AME_POST_APPROVAL'));
358 elsif(ruleTypeIn = ame_util.productionRuleType) then
359 return(ame_util.getLabel(ame_util.perFndAppId,'AME_PRODUCTION'));
360 elsif(ruleTypeIn = ame_util.combinationRuleType) then
361 return(ame_util.getLabel(ame_util.perFndAppId,'AME_COMBINATION'));
362 else
363 return(null);
364 end if;
365 exception
366 when others then
367 rollback;
368 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
369 routineNameIn => 'getAllowedRuleTypeLabel',
370 exceptionNumberIn => sqlcode,
371 exceptionStringIn => sqlerrm);
372 raise;
373 return(null);
374 end getAllowedRuleTypeLabel;
375 function getChainOrderingMode(actionTypeIdIn in integer,
376 applicationIdIn in integer) return varchar2 as
377 chainOrderingMode ame_util.charType;
378 begin
379 select chain_ordering_mode
380 into chainOrderingMode
381 from ame_action_type_config
382 where
383 action_type_id = actionTypeIdIn and
384 application_id = applicationIdIn and
385 sysdate between start_date and
386 nvl(end_date - ame_util.oneSecond, sysdate) ;
387 return(chainOrderingMode);
388 exception
389 when others then
390 rollback;
391 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
392 routineNameIn => 'getChainOrderingMode',
393 exceptionNumberIn => sqlcode,
394 exceptionStringIn => sqlerrm);
395 raise;
396 return(null);
397 end getChainOrderingMode;
398 function getChildVersionStartDate(actionIdIn in integer) return varchar2 as
399 startDate date;
400 stringStartDate varchar2(50);
401 begin
402 select start_date
403 into startDate
404 from ame_actions
405 where
406 action_id = actionIdIn and
407 sysdate between start_date and
408 nvl(end_date - ame_util.oneSecond, sysdate) ;
409 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
410 return(stringStartDate);
411 exception
412 when others then
413 rollback;
414 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
415 routineNameIn => 'getChildVersionStartDate',
416 exceptionNumberIn => sqlcode,
417 exceptionStringIn => sqlerrm);
418 raise;
419 return(null);
420 end getChildVersionStartDate;
424 stringStartDate varchar2(50);
421 function getChildVersionStartDate2(actionTypeIdIn in integer,
422 applicationIdIn in integer) return varchar2 as
423 startDate date;
425 begin
426 select start_date
427 into startDate
428 from ame_action_type_config
429 where
430 action_type_id = actionTypeIdIn and
431 application_id = applicationIdIn and
432 sysdate between start_date and
433 nvl(end_date - ame_util.oneSecond, sysdate) ;
434 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
435 return(stringStartDate);
436 exception
437 when others then
438 rollback;
439 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
440 routineNameIn => 'getChildVersionStartDate2',
441 exceptionNumberIn => sqlcode,
442 exceptionStringIn => sqlerrm);
443 raise;
444 return(null);
445 end getChildVersionStartDate2;
446 /*
447 getDescription returns a description of the form
448 <<actionTypeName: actionDescription>>, where actionDescription is
449 the action's static or dynamic description. The maximum length of the
450 string returned by getDescription is 500 bytes.
451 */
452 function getDescription(actionIdIn in integer) return varchar2 as
453 actionTypeName ame_action_types.name%type;
454 approverName ame_actions.parameter%type;
455 description ame_actions.description%type;
456 approverDesc ame_util.longStringType;
457 approverValid boolean;
458 begin
459 if(getActionTypeDynamicDesc(actionTypeIdIn =>
460 getActionTypeIdById(actionIdIn => actionIdIn)) = ame_util.booleanTrue ) then
461 return getDynamicActionDesc(actionIdIn => actionIdIn );
462 end if;
463 select description
464 into description
465 from ame_actions
466 where
467 action_id = actionIdIn and
468 sysdate between start_date and
469 nvl(end_date - ame_util.oneSecond, sysdate) ;
470 /* Verify approver is a valid approver. */
471 if(getActionTypeName(actionTypeIdIn =>
472 (getActionTypeIdById(actionIdIn => actionIdIn)))) =
473 ame_util.substitutionTypeName then
474 approverName := getParameter(actionIdIn => actionIdIn);
475 ame_approver_type_pkg.getApproverDescAndValidity(
476 nameIn => approverName,
477 descriptionOut => approverDesc,
478 validityOut => approverValid);
479 if(not approverValid) then
480 return(approverDesc);
481 end if;
482 end if;
483 actionTypeName := getActionTypeNameByActionId(actionIdIn => actionIdIn);
484 return(actionTypeName|| ': '|| description);
485 exception
486 when others then
487 rollback;
488 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
489 routineNameIn => 'getDescription',
490 exceptionNumberIn => sqlcode,
491 exceptionStringIn => '(action ID ' ||
492 actionIdIn||
493 ') ' ||
494 sqlerrm);
495 raise;
496 return(null);
497 end getDescription;
498 function getDescription2(actionIdIn in integer) return varchar2 as
499 approverName ame_actions.parameter%type;
500 description ame_actions.description%type;
501 approverDesc ame_util.longStringType;
502 approverValid boolean;
503 begin
504 if(getActionTypeDynamicDesc(actionTypeIdIn =>
505 getActionTypeIdById(actionIdIn => actionIdIn)) = ame_util.booleanTrue ) then
506 return getDynamicActionDesc(actionIdIn => actionIdIn );
507 end if;
508 select description
509 into description
510 from ame_actions
511 where
512 action_id = actionIdIn and
513 sysdate between start_date and
514 nvl(end_date - ame_util.oneSecond, sysdate) ;
515 /* Verify approver is a valid approver. */
516 if(getActionTypeName(actionTypeIdIn =>
517 (getActionTypeIdById(actionIdIn => actionIdIn)))) =
518 ame_util.substitutionTypeName then
519 approverName := getParameter(actionIdIn => actionIdIn);
520 ame_approver_type_pkg.getApproverDescAndValidity(
521 nameIn => approverName,
522 descriptionOut => approverDesc,
523 validityOut => approverValid);
524 if(not approverValid) then
525 return(approverDesc);
526 end if;
527 end if;
528 return(description);
529 exception
530 when others then
531 rollback;
532 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
533 routineNameIn => 'getDescription2',
534 exceptionNumberIn => sqlcode,
535 exceptionStringIn => '(action ID ' ||
539 raise;
536 actionIdIn||
537 ') ' ||
538 sqlerrm);
540 return(null);
541 end getDescription2;
542 function getDynamicActionDesc(actionIdIn in integer) return varchar2 as
543 actionDescription ame_util.stringType;
544 actionTypeName ame_action_types.name%type;
545 descriptionQuery ame_action_types.description_query%type;
546 parameterOne ame_actions.parameter%type;
547 parameterTwo ame_actions.parameter_two%type;
548 tempIndex integer;
549 begin
550 select description_query,
551 parameter,
552 parameter_two
553 into descriptionQuery,
554 parameterOne,
555 parameterTwo
556 from ame_actions,
557 ame_action_types
558 where
559 ame_actions.action_type_id = ame_action_types.action_type_id and
560 action_id = actionIdIn and
561 sysdate between ame_action_types.start_date and
562 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
563 sysdate between ame_actions.start_date and
564 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
565 if(instrb(descriptionQuery, ame_util.actionParameterOne) > 0) then
566 if(instrb(descriptionQuery, ame_util.actionParameterTwo) > 0) then /* both parameters */
567 execute immediate descriptionQuery
568 into actionDescription using
569 in parameterOne,
570 in parameterTwo;
571 else /* just parameter_one */
572 execute immediate descriptionQuery into
573 actionDescription using
574 in parameterOne;
575 end if;
576 else
577 if(instrb(descriptionQuery, ame_util.actionParameterTwo) > 0) then /* just paramter_two */
578 execute immediate descriptionQuery into
579 actionDescription using
580 in parameterTwo;
581 else /* neither */
582 execute immediate descriptionQuery into
583 actionDescription;
584 end if;
585 end if;
586 actionTypeName := getActionTypeNameByActionId(actionIdIn => actionIdIn);
587 return(actionTypeName || ': '|| actionDescription);
588 exception
589 when others then
590 rollback;
591 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
592 routineNameIn => 'getDynamicActionDesc',
593 exceptionNumberIn => sqlcode,
594 exceptionStringIn => '(action ID ' ||
595 actionIdIn||
596 ') ' ||
597 sqlerrm);
598 raise;
599 return(null);
600 end getDynamicActionDesc;
601 function getGroupChainActionTypeId return integer as
602 actionTypeId ame_action_types.action_type_id%type;
603 begin
604 select action_type_id
605 into actionTypeId
606 from ame_action_types
607 where name = ame_util.groupChainApprovalTypeName and
608 sysdate between start_date and
609 nvl(end_date - ame_util.oneSecond, sysdate) ;
610 return actionTypeId;
611 exception
612 when others then
613 rollback;
614 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
615 routineNameIn => 'getGroupChainActionTypeId',
616 exceptionNumberIn => sqlcode,
617 exceptionStringIn => sqlerrm);
618 raise;
619 return(null);
620 end getGroupChainActionTypeId;
621 function getId(actionTypeIdIn in integer,
622 parameterIn in varchar2 default null) return integer as
623 actionId integer;
624 begin
625 select action_id
626 into actionId
627 from ame_actions
628 where
629 action_type_id = actionTypeIdIn and
630 ((parameterIn is null and parameter is null) or parameter = parameterIn) and
631 sysdate between start_date and
632 nvl(end_date - ame_util.oneSecond, sysdate) ;
633 return(actionId);
634 exception
635 when others then
636 rollback;
637 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
638 routineNameIn => 'getId',
639 exceptionNumberIn => sqlcode,
640 exceptionStringIn => '(action type ID ' ||
641 actionTypeIdIn||
642 ') ' ||
643 sqlerrm);
644 raise;
645 return(null);
646 end getId;
647 function getParameter(actionIdIn in integer) return varchar2 as
648 parameter ame_actions.parameter%type;
649 begin
650 select parameter
651 into parameter
652 from ame_actions
653 where
654 action_id = actionIdIn and
655 sysdate between start_date and
659 when others then
656 nvl(end_date - ame_util.oneSecond, sysdate) ;
657 return(parameter);
658 exception
660 rollback;
661 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
662 routineNameIn => 'getParameter',
663 exceptionNumberIn => sqlcode,
664 exceptionStringIn => '(action ID ' ||
665 actionIdIn||
666 ') ' ||
667 sqlerrm);
668 raise;
669 return(null);
670 end getParameter;
671 function getParameter2(actionIdIn in integer) return varchar2 as
672 parameterTwo ame_actions.parameter_two%type;
673 begin
674 select parameter_two
675 into parameterTwo
676 from ame_actions
677 where
678 action_id = actionIdIn and
679 sysdate between start_date and
680 nvl(end_date - ame_util.oneSecond, sysdate) ;
681 return(parameterTwo);
682 exception
683 when others then
684 rollback;
685 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
686 routineNameIn => 'getParameter2',
687 exceptionNumberIn => sqlcode,
688 exceptionStringIn => '(action ID ' ||
689 actionIdIn||
690 ') ' ||
691 sqlerrm);
692 raise;
693 return(null);
694 end getParameter2;
695 function getParentVersionStartDate(actionTypeIdIn in integer) return varchar2 as
696 startDate date;
697 stringStartDate varchar2(50);
698 begin
699 select start_date
700 into startDate
701 from ame_action_types
702 where
703 action_type_id = actionTypeIdIn and
704 sysdate between start_date and
705 nvl(end_date - ame_util.oneSecond, sysdate) ;
706 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
707 return(stringStartDate);
708 exception
709 when others then
710 rollback;
711 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
712 routineNameIn => 'getParentVersionStartDate',
713 exceptionNumberIn => sqlcode,
714 exceptionStringIn => '(action type ID ' ||
715 actionTypeIdIn||
716 ') ' ||
717 sqlerrm);
718 raise;
719 return(null);
720 end getParentVersionStartDate;
721 function getPreApprovalActionTypeId return integer as
722 actionTypeId ame_action_types.action_type_id%type;
723 begin
724 select action_type_id
725 into actionTypeId
726 from ame_action_types
727 where name = ame_util.preApprovalTypeName and
728 sysdate between start_date and
729 nvl(end_date - ame_util.oneSecond, sysdate) ;
730 return actionTypeId;
731 exception
732 when others then
733 rollback;
734 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
735 routineNameIn => 'getPreApprovalActionTypeId',
736 exceptionNumberIn => sqlcode,
737 exceptionStringIn => sqlerrm);
738 raise;
739 return(null);
740 end getPreApprovalActionTypeId;
741 function getPostApprovalActionTypeId return integer as
742 actionTypeId ame_action_types.action_type_id%type;
743 begin
744 select action_type_id
745 into actionTypeId
746 from ame_action_types
747 where name = ame_util.postApprovalTypeName and
748 sysdate between start_date and
749 nvl(end_date - ame_util.oneSecond, sysdate) ;
750 return actionTypeId;
751 exception
752 when others then
753 rollback;
754 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
755 routineNameIn => 'getPostApprovalActionTypeId',
756 exceptionNumberIn => sqlcode,
757 exceptionStringIn => sqlerrm);
758 raise;
759 return(null);
760 end getPostApprovalActionTypeId;
761 function getVotingRegime(actionTypeIdIn in integer,
762 applicationIdIn in integer) return varchar2 as
763 votingRegime ame_approval_group_config.voting_regime%type;
764 begin
765 select voting_regime
766 into votingRegime
767 from ame_action_type_config
768 where
769 action_type_id = actionTypeIdIn and
770 application_id = applicationIdIn and
771 sysdate between start_date and
772 nvl(end_date - ame_util.oneSecond, sysdate);
773 return(votingRegime);
774 exception
775 when others then
776 rollback;
780 exceptionStringIn => sqlerrm);
777 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
778 routineNameIn => 'getVotingRegime',
779 exceptionNumberIn => sqlcode,
781 raise;
782 return(null);
783 end getVotingRegime;
784 function isInUse(actionIdIn in integer) return boolean as
785 useCount integer;
786 begin
787 /*
788 The following select checks that the rule is current, but
789 not that the action is current. This is intentional. The
790 assumption is that the rest of the application will never
791 discover a historical action and try to check whether it
792 is in use. It now does check the start date
793 of rules to capture future rule start dates.
794 */
795 select count(*)
796 into useCount
797 from ame_rules,
798 ame_action_usages
799 where
800 ame_rules.rule_id = ame_action_usages.rule_id and
801 ame_action_usages.action_id = actionIdIn and
802 ((sysdate between ame_rules.start_date and
803 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
804 (sysdate < ame_rules.start_date and
805 ame_rules.start_date < nvl(ame_rules.end_date,ame_rules.start_date + ame_util.oneSecond))) and
806 ((sysdate between ame_action_usages.start_date and
807 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
808 (sysdate < ame_action_usages.start_date and
809 ame_action_usages.start_date < nvl(ame_action_usages.end_date,ame_action_usages.start_date + ame_util.oneSecond)));
810 if(useCount > 0) then
811 return(true);
812 end if;
813 return(false);
814 exception
815 when others then
816 rollback;
817 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
818 routineNameIn => 'isInUse',
819 exceptionNumberIn => sqlcode,
820 exceptionStringIn => '(action ID ' ||
821 actionIdIn||
822 ') ' ||
823 sqlerrm);
824 raise;
825 return(true); /* conservative: avoids allowing deletion if might still be in use */
826 end isInUse;
827 function isListCreationRuleType(actionTypeIdIn in integer) return boolean as
828 tempCount integer;
829 begin
830 select count(*)
831 into tempCount
832 from ame_action_type_usages
833 where
834 action_type_id = actionTypeIdIn and
835 rule_type = ame_util.authorityRuleType and
836 sysdate between start_date and
837 nvl(end_date - ame_util.oneSecond, sysdate) ;
838 if(tempCount > 0) then
839 return(true);
840 end if;
841 return(false);
842 exception
843 when others then
844 rollback;
845 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
846 routineNameIn => 'isListCreationRuleType',
847 exceptionNumberIn => sqlcode,
848 exceptionStringIn => '(action type ID ' ||
849 actionTypeIdIn||
850 ') ' ||
851 sqlerrm);
852 raise;
853 return(true);
854 end isListCreationRuleType;
855 function isSeeded(actionTypeIdIn in integer) return boolean as
856 createdByValue integer;
857 attributeId integer;
858 begin
859 select created_by
860 into createdByValue
861 from ame_action_types
862 where
863 action_type_id = actionTypeIdIn and
864 sysdate between start_date and
865 nvl(end_date - ame_util.oneSecond, sysdate) ;
866 if(createdByValue = 1) then
867 return(true);
868 end if;
869 return(false);
870 exception
871 when others then
872 rollback;
873 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
874 routineNameIn => 'isSeeded',
875 exceptionNumberIn => sqlcode,
876 exceptionStringIn => '(action type ID ' ||
877 actionTypeIdIn||
878 ') ' ||
879 sqlerrm);
880 raise;
881 return(true); /* conservative: avoids allowing deletion if might still be in use */
882 end isSeeded;
883 function new(nameIn in varchar2,
884 procedureNameIn in varchar2,
885 dynamicDescriptionIn in varchar2,
886 descriptionIn in varchar2 default null,
887 descriptionQueryIn in varchar2 default null,
888 actionTypeIdIn in integer default null,
889 finalizeIn in boolean default false,
890 newStartDateIn in date default null,
894 currentUserId integer;
891 processingDateIn in date default null) return integer as
892 actionTypeId integer;
893 createdBy integer;
895 descriptionLengthException exception;
896 descriptionQueryException exception;
897 descriptionQueryLgthException exception;
898 errorCode integer;
899 errorMessage ame_util.longestStringType;
900 invalidDesQueryException exception;
901 invalidDesQueryException2 exception;
902 nameLengthException exception;
903 nullDescriptionQueryException exception;
904 nullException exception;
905 procedureNameLengthException exception;
906 processingDate date;
907 tempCount integer;
908 begin
909 if processingDateIn is null then
910 processingDate := sysdate;
911 else
912 processingDate := processingDateIn;
913 end if;
914 if(nameIn is null or
915 procedureNameIn is null) then
916 raise nullException;
917 end if;
918 begin
919 select action_type_id
920 into actionTypeId
921 from ame_action_types
922 where
923 (actionTypeIdIn is null or action_type_id <> actionTypeIdIn) and
924 upper(name) = upper(nameIn) and
925 sysdate between start_date and
926 nvl(end_date - ame_util.oneSecond, sysdate) ;
927 if actionTypeId is not null then
928 raise_application_error(-20001,
929 ame_util.getMessage(applicationShortNameIn => 'PER',
930 messageNameIn => 'AME_400139_ACT_APT_ALD_EXISTS'));
931 end if;
932 exception
933 when no_data_found then null;
934 end;
935 if(dynamicDescriptionIn = ame_util.booleanTrue) then
936 if(descriptionQueryIn is null) then
937 raise nullDescriptionQueryException;
938 end if;
939 if(instrb(descriptionQueryIn, ';', 1, 1) > 0) or
940 (instrb(descriptionQueryIn, '--', 1, 1) > 0) or
941 (instrb(descriptionQueryIn, '/*', 1, 1) > 0) or
942 (instrb(descriptionQueryIn, '*/', 1, 1) > 0) then
943 raise descriptionQueryException;
944 end if;
945 /* Verify that the description query includes at least one of the bind variables */
946 if(instrb(descriptionQueryIn, ame_util.actionParameterOne, 1, 1) = 0) then
947 if(instrb(descriptionQueryIn, ame_util.actionParameterTwo, 1, 1) = 0) then
948 raise invalidDesQueryException;
949 end if;
950 end if;
951 if(instrb(descriptionQueryIn, ':', 1, 1) > 0) then
952 if(instrb(descriptionQueryIn, ame_util.actionParameterOne, 1, 1) = 0) then
953 if(instrb(descriptionQueryIn, ame_util.actionParameterTwo, 1, 1) = 0) then
954 raise invalidDesQueryException2;
955 end if;
956 end if;
957 end if;
958 end if;
959 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
960 columnNameIn => 'name',
961 argumentIn => nameIn)) then
962 raise nameLengthException;
963 end if;
964 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
965 columnNameIn => 'procedure_name',
966 argumentIn => procedureNameIn)) then
967 raise procedureNameLengthException;
968 end if;
969 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
970 columnNameIn => 'description',
971 argumentIn => descriptionIn)) then
972 raise descriptionLengthException;
973 end if;
974 /*
975 If any version of the object has created_by = 1, all versions,
976 including the new version, should. This is a failsafe way to check
977 whether previous versions of an already end-dated object had
978 created_by = 1.
979 */
980 currentUserId := ame_util.getCurrentUserId;
981 if(actionTypeIdIn is null) then
982 createdBy := currentUserId;
983 select ame_action_types_s.nextval into actionTypeId from dual;
984 else
985 actionTypeId := actionTypeIdIn;
986 select count(*)
987 into tempCount
988 from ame_action_types
989 where
990 action_type_id = actionTypeId and
991 created_by = ame_util.seededDataCreatedById;
992 if(tempCount > 0) then
993 createdBy := ame_util.seededDataCreatedById;
994 else
995 createdBy := currentUserId;
996 end if;
997 end if;
998 insert into ame_action_types(action_type_id,
999 name,
1000 procedure_name,
1001 created_by,
1002 creation_date,
1003 last_updated_by,
1004 last_update_date,
1005 last_update_login,
1006 start_date,
1007 end_date,
1008 description,
1009 dynamic_description,
1010 description_query)
1011 values(actionTypeId,
1012 nameIn,
1013 procedureNameIn,
1017 processingDate,
1014 createdBy,
1015 processingDate,
1016 currentUserId,
1018 currentUserId,
1019 nvl(newStartDateIn, processingDate),
1020 null,
1021 descriptionIn,
1022 dynamicDescriptionIn,
1023 descriptionQueryIn);
1024 if(finalizeIn) then
1025 commit;
1026 end if;
1027 return(actionTypeId);
1028 exception
1029 when invalidDesQueryException then
1030 rollback;
1031 errorCode := -20001;
1032 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1033 messageNameIn => 'AME_400370_ACT_DYNAMIC_DESC');
1034 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1035 routineNameIn => 'new',
1036 exceptionNumberIn => errorCode,
1037 exceptionStringIn => errorMessage);
1038 raise_application_error(errorCode,
1039 errorMessage);
1040 return(null);
1041 when invalidDesQueryException2 then
1042 rollback;
1043 errorCode := -20001;
1044 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1045 messageNameIn => 'AME_400371_ACT_INV_BIND_VAR');
1046 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1047 routineNameIn => 'new',
1048 exceptionNumberIn => errorCode,
1049 exceptionStringIn => errorMessage);
1050 raise_application_error(errorCode,
1051 errorMessage);
1052 return(null);
1053 when nameLengthException then
1054 rollback;
1055 errorCode := -20001;
1056 errorMessage :=
1057 ame_util.getMessage(applicationShortNameIn => 'PER',
1058 messageNameIn => 'AME_400140_ACT_APT_NAME_LONG',
1059 tokenNameOneIn => 'COLUMN_LENGTH',
1060 tokenValueOneIn =>
1061 ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1062 columnNameIn => 'name'));
1063 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1064 routineNameIn => 'new',
1065 exceptionNumberIn => errorCode,
1066 exceptionStringIn => errorMessage);
1067 raise_application_error(errorCode,
1068 errorMessage);
1069 return(null);
1070 when procedureNameLengthException then
1071 rollback;
1072 errorCode := -20001;
1073 errorMessage :=
1074 ame_util.getMessage(applicationShortNameIn => 'PER',
1075 messageNameIn => 'AME_400141_ACT_APT_PRC_NAM_LNG',
1076 tokenNameOneIn => 'COLUMN_LENGTH',
1077 tokenValueOneIn =>
1078 ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1079 columnNameIn => 'procedure_name'));
1080 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1081 routineNameIn => 'new',
1082 exceptionNumberIn => errorCode,
1083 exceptionStringIn => errorMessage);
1084 raise_application_error(errorCode,
1085 errorMessage);
1086 return(null);
1087 when descriptionLengthException then
1088 rollback;
1089 errorCode := -20001;
1090 errorMessage :=
1091 ame_util.getMessage(applicationShortNameIn => 'PER',
1092 messageNameIn => 'AME_400142_ACT_APT_DESC_LONG',
1093 tokenNameOneIn => 'COLUMN_LENGTH',
1094 tokenValueOneIn =>
1095 ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1096 columnNameIn => 'description'));
1097 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1098 routineNameIn => 'new',
1099 exceptionNumberIn => errorCode,
1100 exceptionStringIn => errorMessage);
1101 raise_application_error(errorCode,
1102 errorMessage);
1103 return(null);
1104 when nullException then
1105 rollback;
1106 errorCode := -20001;
1107 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1108 messageNameIn => 'AME_400144_ACT_VALUE_APT_ENT');
1109 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1110 routineNameIn => 'new',
1111 exceptionNumberIn => errorCode,
1112 exceptionStringIn => errorMessage);
1113 raise_application_error(errorCode,
1114 errorMessage);
1115 return(null);
1116 when descriptionQueryException then
1117 rollback;
1118 errorCode := -20001;
1119 errorMessage :=
1120 ame_util.getMessage(applicationShortNameIn => 'PER',
1124 exceptionNumberIn => errorCode,
1121 messageNameIn => 'AME_400372_ACT DYNAMIC_DESC2');
1122 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1123 routineNameIn => 'new',
1125 exceptionStringIn => errorMessage);
1126 raise_application_error(errorCode,
1127 errorMessage);
1128 when nullDescriptionQueryException then
1129 rollback;
1130 errorCode := -20001;
1131 errorMessage :=
1132 ame_util.getMessage(applicationShortNameIn => 'PER',
1133 messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
1134 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1135 routineNameIn => 'new',
1136 exceptionNumberIn => errorCode,
1137 exceptionStringIn => errorMessage);
1138 raise_application_error(errorCode,
1139 errorMessage);
1140 when descriptionQueryLgthException then
1141 rollback;
1142 errorCode := -20001;
1143 errorMessage :=
1144 ame_util.getMessage(applicationShortNameIn => 'PER',
1145 messageNameIn => 'AME_400142_ACT_APT_DESC_LONG',
1146 tokenNameOneIn => 'COLUMN_LENGTH',
1147 tokenValueOneIn =>
1148 ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1149 columnNameIn => 'description_query'));
1150 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1151 routineNameIn => 'new',
1152 exceptionNumberIn => errorCode,
1153 exceptionStringIn => errorMessage);
1154 raise_application_error(errorCode,
1155 errorMessage);
1156 return(null);
1157 when others then
1158 rollback;
1159 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1160 routineNameIn => 'new',
1161 exceptionNumberIn => sqlcode,
1162 exceptionStringIn => '(action type ID ' ||
1163 actionTypeIdIn||
1164 ') ' ||
1165 sqlerrm);
1166 raise;
1167 return(null);
1168 end new;
1169 function newAction(actionTypeIdIn in integer,
1170 updateParentObjectIn in boolean,
1171 descriptionIn in varchar2 default null,
1172 parameterIn in varchar2 default null,
1173 parameterTwoIn in varchar2 default null,
1174 newStartDateIn in date default null,
1175 finalizeIn in boolean default false,
1176 parentVersionStartDateIn in date default null,
1177 actionIdIn in integer default null,
1178 processingDateIn in date default null) return integer as
1179 cursor startDateCursor is
1180 select start_date
1181 from ame_action_types
1182 where
1183 action_type_id = actionTypeIdIn and
1184 sysdate between start_date and
1185 nvl(end_date - ame_util.oneSecond, sysdate)
1186 for update;
1187 actionCount integer;
1188 actionId integer;
1189 actionTypeId integer;
1190 actionTypeDescription ame_action_types.description%type;
1191 actionTypeDescQuery ame_action_types.description_query%type;
1192 actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193 actionTypeName ame_action_types.name%type;
1194 actionTypeProcedureName ame_action_types.procedure_name%type;
1195 attributeId ame_attributes.attribute_id%type;
1196 createdBy integer;
1197 currentUserId integer;
1198 descriptionLengthException exception;
1199 duplicateActionException exception;
1200 endDate date;
1201 errorCode integer;
1202 errorMessage ame_util.longestStringType;
1203 invalidAttNameException exception;
1204 invalidParameter exception;
1205 nullDescriptionException exception;
1206 objectVersionNoDataException exception;
1207 parameterLengthException exception;
1208 startDate date;
1209 processingDate date;
1210 tempCount integer;
1211 begin
1212 if processingDateIn is null then
1213 processingDate := sysdate;
1214 else
1215 processingDate := processingDateIn;
1216 end if;
1217 if(finalizeIn) then
1218 open startDateCursor;
1219 fetch startDateCursor into startDate;
1220 if startDateCursor%notfound then
1221 raise objectVersionNoDataException;
1222 end if;
1223 if(parentVersionStartDateIn <> startDate) then
1224 close startDateCursor;
1225 raise ame_util.objectVersionException;
1226 end if;
1227 end if;
1228 if parameterIn like '%;%' then
1229 raise invalidParameter;
1230 end if;
1231 if(descriptionIn is null) and
1232 (getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn) = ame_util.booleanFalse) then
1233 raise nullDescriptionException;
1237 from ame_actions
1234 end if;
1235 select count(*)
1236 into actionCount
1238 where
1239 (actionIdIn is null or action_id <> actionIdIn) and
1240 ((parameterIn is null and parameter is null) or parameter = parameterIn) and
1241 ((parameterTwoIn is null and parameter_two is null) or parameter_two = parameterTwoIn) and
1242 action_type_id = actionTypeIdIn and
1243 sysdate between start_date and
1244 nvl(end_date - ame_util.oneSecond, sysdate);
1245 if(actionCount > 0) then
1246 raise duplicateActionException;
1247 end if;
1248 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_actions',
1249 columnNameIn => 'description',
1250 argumentIn => descriptionIn)) then
1251 raise descriptionLengthException;
1252 end if;
1253 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_actions',
1254 columnNameIn => 'parameter',
1255 argumentIn => parameterIn)) then
1256 raise parameterLengthException;
1257 end if;
1258 /*
1259 If any version of the object has created_by = 1, all versions,
1260 including the new version, should. This is a failsafe way to check
1261 whether previous versions of an already end-dated object had
1262 created_by = 1.
1263 */
1264 currentUserId := ame_util.getCurrentUserId;
1265 if(actionIdIn is null) then
1266 createdBy := currentUserId;
1267 select ame_actions_s.nextval into actionId from dual;
1268 else
1269 actionId := actionIdIn;
1270 select count(*)
1271 into tempCount
1272 from ame_actions
1273 where
1274 action_id = actionId and
1275 created_by = ame_util.seededDataCreatedById;
1276 if(tempCount > 0) then
1277 createdBy := ame_util.seededDataCreatedById;
1278 else
1279 createdBy := currentUserId;
1280 end if;
1281 end if;
1282 startDate := processingDate;
1283 insert into ame_actions(action_id,
1284 action_type_id,
1285 parameter,
1286 created_by,
1287 creation_date,
1288 last_updated_by,
1289 last_update_date,
1290 last_update_login,
1291 start_date,
1292 end_date,
1293 description,
1294 parameter_two)
1295 values(actionId,
1296 actionTypeIdIn,
1297 parameterIn,
1298 createdBy,
1299 processingDate,
1300 currentUserId,
1301 processingDate,
1302 currentUserId,
1303 nvl(newStartDateIn, startDate),
1304 null,
1305 descriptionIn,
1306 parameterTwoIn);
1307 actionTypeName := ame_action_pkg.getActionTypeName(actionTypeIdIn => actionTypeIdIn);
1308 if(updateParentObjectIn) then
1309 endDate := startDate ;
1310 actionTypeDescription := getActionTypeDescription(actionTypeIdIn => actionTypeIdIn);
1311 actionTypeProcedureName := getActionTypeProcedureName(actionTypeIdIn => actionTypeIdIn);
1312 actionTypeDynamicDesc := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
1313 actionTypeDescQuery := getActionTypeDescQuery(actionTypeIdIn => actionTypeIdIn);
1314 update ame_action_types
1315 set
1316 last_updated_by = currentUserId,
1317 last_update_date = endDate,
1318 last_update_login = currentUserId,
1319 end_date = endDate
1320 where
1321 action_type_id = actionTypeIdIn and
1322 processingDate between start_date and
1323 nvl(end_date - ame_util.oneSecond, processingDate);
1324 actionTypeId := new(nameIn => actionTypeName,
1325 procedureNameIn => actionTypeProcedureName,
1326 descriptionIn => actionTypeDescription,
1327 actionTypeIdIn => actionTypeIdIn,
1328 dynamicDescriptionIn => actionTypeDynamicDesc,
1329 descriptionQueryIn => actionTypeDescQuery,
1330 finalizeIn => false,
1331 newStartDateIn => nvl(newStartDateIn, startDate),
1332 processingDateIn => processingDate);
1333 end if;
1334 if(finalizeIn) then
1335 commit;
1336 end if;
1337 return(actionId);
1338 exception
1339 when ame_util.objectVersionException then
1340 rollback;
1341 if(startDateCursor%isOpen) then
1342 close startDateCursor;
1343 end if;
1344 errorCode := -20001;
1345 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1346 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1347 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1348 routineNameIn => 'newAction',
1349 exceptionNumberIn => errorCode,
1353 when objectVersionNoDataException then
1350 exceptionStringIn => errorMessage);
1351 raise_application_error(errorCode,
1352 errorMessage);
1354 rollback;
1355 if(startDateCursor%isOpen) then
1356 close startDateCursor;
1357 end if;
1358 errorCode := -20001;
1359 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1360 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1361 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1362 routineNameIn => 'newAction',
1363 exceptionNumberIn => errorCode,
1364 exceptionStringIn => errorMessage);
1365 raise_application_error(errorCode,
1366 errorMessage);
1367 when nullDescriptionException then
1368 rollback;
1369 if(startDateCursor%isOpen) then
1370 close startDateCursor;
1371 end if;
1372 errorCode := -20001;
1373 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1374 messageNameIn => 'AME_400137_ACT_EMPTY_DESC');
1375 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1376 routineNameIn => 'newAction',
1377 exceptionNumberIn => errorCode,
1378 exceptionStringIn => errorMessage);
1379 raise_application_error(errorCode,
1380 errorMessage);
1381 when duplicateActionException then
1382 rollback;
1383 if(startDateCursor%isOpen) then
1384 close startDateCursor;
1385 end if;
1386 errorCode := -20001;
1387 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1388 messageNameIn => 'AME_400293_ACT_APR_ALD_EXISTS');
1389 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1390 routineNameIn => 'newAction',
1391 exceptionNumberIn => errorCode,
1392 exceptionStringIn => errorMessage);
1393 raise_application_error(errorCode,
1394 errorMessage);
1395 when invalidParameter then
1396 rollback;
1397 errorCode := -20001;
1398 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1399 messageNameIn => 'AME_400135_ACT_NO_PAR_SEMI');
1400 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1401 routineNameIn => 'newAction',
1402 exceptionNumberIn => errorCode,
1403 exceptionStringIn => errorMessage);
1404 raise_application_error(errorCode,
1405 errorMessage);
1406 return(null);
1407 when descriptionLengthException then
1408 rollback;
1409 errorCode := -20001;
1410 errorMessage:= ame_util.getMessage(applicationShortNameIn => 'PER',
1411 messageNameIn => 'AME_400136_ACT_APP_DES_LNG',
1412 tokenNameOneIn => 'COLUMN_LENGTH',
1413 tokenValueOneIn =>
1414 ame_util.getColumnLength(tableNameIn => 'ame_actions',
1415 columnNameIn => 'description'));
1416 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1417 routineNameIn => 'newAction',
1418 exceptionNumberIn => errorCode,
1419 exceptionStringIn => errorMessage);
1420 raise_application_error(errorCode,
1421 errorMessage);
1422 return(null);
1423 when parameterLengthException then
1424 rollback;
1425 errorCode := -20001;
1426 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1427 messageNameIn => 'AME_400138_ACT_APPR_TOO_LONG',
1428 tokenNameOneIn => 'COLUMN_LENGTH',
1429 tokenValueOneIn =>
1430 ame_util.getColumnLength(tableNameIn => 'ame_actions',
1431 columnNameIn => 'parameter'));
1432 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1433 routineNameIn => 'newAction',
1434 exceptionNumberIn => errorCode,
1435 exceptionStringIn => errorMessage);
1436 raise_application_error(errorCode,
1437 errorMessage);
1438 return(null);
1439 when invalidAttNameException then
1440 rollback;
1441 errorCode := -20001;
1442 errorMessage :=
1443 ame_util.getMessage(applicationShortNameIn => 'PER',
1444 messageNameIn => 'AME_400323_INV_ATTRIB_ENT_PAR');
1445 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1446 routineNameIn => 'newAction',
1447 exceptionNumberIn => errorCode,
1448 exceptionStringIn => errorMessage);
1449 raise_application_error(errorCode,
1453 rollback;
1450 errorMessage);
1451 return(null);
1452 when others then
1454 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1455 routineNameIn => 'newAction',
1456 exceptionNumberIn => sqlcode,
1457 exceptionStringIn => '(action type ID ' ||
1458 actionTypeIdIn||
1459 ') ' ||
1460 sqlerrm);
1461 raise;
1462 return(null);
1463 end newAction;
1464 function orderNumberUnique(applicationIdIn in integer,
1465 orderNumberIn in integer,
1466 actionTypeIdIn in integer) return boolean as
1467 ruleType integer;
1468 tempCount integer;
1469 begin
1470 ruleType := getAllowedRuleType(actionTypeIdIn => actionTypeIdIn);
1471 select count(*)
1472 into tempCount
1473 from ame_action_type_config,
1474 ame_action_type_usages
1475 where
1476 ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
1477 ame_action_type_config.application_id = applicationIdIn and
1478 ame_action_type_config.order_number = orderNumberIn and
1479 ame_action_type_usages.rule_type = ruleType and
1480 sysdate between ame_action_type_config.start_date and
1481 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
1482 sysdate between ame_action_type_usages.start_date and
1483 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
1484 if(tempCount > 1) then
1485 return(false);
1486 else
1487 return(true);
1488 end if;
1489 exception
1490 when others then
1491 rollback;
1492 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1493 routineNameIn => 'orderNumberUnique',
1494 exceptionNumberIn => sqlcode,
1495 exceptionStringIn => sqlerrm);
1496 raise;
1497 return(false);
1498 end orderNumberUnique;
1499 function requiredAttOnApprovalTypeList(actionTypeIdIn in integer,
1500 attributeIdIn in integer) return boolean as
1501 tempCount integer;
1502 begin
1503 select count(*)
1504 into tempCount
1505 from ame_mandatory_attributes
1506 where
1507 action_type_id = actionTypeIdIn and
1508 attribute_id = attributeIdIn and
1509 sysdate between start_date and
1510 nvl(end_date - ame_util.oneSecond, sysdate) ;
1511 if(tempCount > 0) then
1512 return true;
1513 end if;
1514 return false;
1515 exception
1516 when others then
1517 rollback;
1518 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1519 routineNameIn => 'requiredAttOnApprovalTypeList',
1520 exceptionNumberIn => sqlcode,
1521 exceptionStringIn => '(action type ID ' ||
1522 actionTypeIdIn||
1523 ') ' ||
1524 sqlerrm);
1525 raise;
1526 return(true);
1527 end requiredAttOnApprovalTypeList;
1528 procedure change(actionTypeIdIn in integer,
1529 ruleTypeIn in varchar2,
1530 processingDateIn in date,
1531 descriptionQueryIn in varchar2 default null,
1532 nameIn in varchar2 default null,
1533 procedureNameIn in varchar2 default null,
1534 descriptionIn in varchar2 default null,
1535 deleteListIn in ame_util.stringList default ame_util.emptyStringList,
1536 finalizeIn in boolean default false) as
1537 cursor startDateCursor is
1538 select start_date
1539 from ame_action_types
1540 where action_type_id = actionTypeIdIn and
1541 sysdate between start_date and
1542 nvl(end_date - ame_util.oneSecond, sysdate)
1543 for update;
1544 actionIdList ame_util.idList;
1545 actionTypeId integer;
1546 actionTypeSeeded boolean;
1547 approverTypeIdList ame_util.idList;
1548 attributeId integer;
1549 childVersionStartDates ame_util.dateList;
1550 currentUserId integer;
1551 endDate date;
1552 errorCode integer;
1553 errorMessage ame_util.longestStringType;
1554 deleteCount integer;
1555 description ame_action_types.description%type;
1556 dynamicDescription ame_action_types.dynamic_description%type;
1557 name ame_action_types.name%type;
1558 newStartDate date;
1559 nullException exception;
1560 objectVersionNoDataException exception;
1561 procedureName ame_action_types.procedure_name%type;
1562 seededException exception;
1563 startDate date;
1564 tempIndex integer;
1568 currentUserId := ame_util.getCurrentUserId;
1565 tempIndex2 integer;
1566 begin
1567 actionTypeSeeded := isSeeded(actionTypeIdIn => actionTypeIdIn);
1569 if(not actionTypeSeeded) then
1570 removeActionTypeUsages(actionTypeIdIn => actionTypeIdIn,
1571 finalizeIn => false,
1572 processingDateIn => processingDateIn);
1573 newActionTypeUsage(actionTypeIdIn => actionTypeIdIn,
1574 ruleTypeIn => ruleTypeIn,
1575 finalizeIn => false,
1576 processingDateIn => processingDateIn);
1577 end if;
1578 dynamicDescription := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
1579 /* make sure the end_date and start_date values do not overlap */
1580 endDate := processingDateIn;
1581 newStartDate := processingDateIn;
1582 update ame_action_types
1583 set
1584 last_updated_by = currentUserId,
1585 last_update_date = endDate,
1586 last_update_login = currentUserId,
1587 end_date = endDate
1588 where
1589 action_type_id = actionTypeIdIn and
1590 processingDateIn between start_date and
1591 nvl(end_date - ame_util.oneSecond, processingDateIn) ;
1592 actionTypeId := new(nameIn => nameIn,
1593 procedureNameIn => procedureNameIn,
1594 descriptionIn => descriptionIn,
1595 actionTypeIdIn => actionTypeIdIn,
1596 dynamicDescriptionIn => dynamicDescription,
1597 descriptionQueryIn => descriptionQueryIn,
1598 finalizeIn => false,
1599 newStartDateIn => newStartDate,
1600 processingDateIn => processingDateIn);
1601 deleteCount := deleteListIn.count;
1602 tempIndex := 1;
1603 tempIndex2 := 1;
1604 if(deleteCount > 0) then
1605 for i in 1..deleteCount loop
1606 if(deleteListIn(i)) like 'req%' then
1607 attributeId := substrb(deleteListIn(i),4,(lengthb(deleteListIn(i))));
1608 ame_attribute_pkg.removeMandatoryAttributes(attributeIdIn => attributeId,
1609 actionTypeIdIn => actionTypeIdIn,
1610 finalizeIn => false);
1611 elsif(deleteListIn(i)) like 'appr%' then
1612 approverTypeIdList(tempIndex2) :=
1613 to_number(substrb(deleteListIn(i),5,(lengthb(deleteListIn(i)))));
1614 tempIndex2 := tempIndex2 + 1;
1615 else
1616 actionIdList(tempIndex) := deleteListIn(i);
1617 childVersionStartDates(tempIndex) :=
1618 ame_util.versionStringToDate(stringDateIn =>
1619 ame_action_pkg.getChildVersionStartDate(actionIdIn => deleteListIn(i)));
1620 tempIndex := tempIndex + 1;
1621 end if;
1622 end loop;
1623 -- Check if any approver types were selected for deletion.
1624 if(approverTypeIdList.count > 0) then
1625 ame_approver_type_pkg.removeApproverTypeUsages(actionTypeIdIn => actionTypeIdIn,
1626 approverTypeIdsIn => approverTypeIdList,
1627 finalizeIn => false,
1628 processingDateIn => processingDateIn);
1629 end if;
1630 if actionIdList.count > 0 then
1631 removeAction(actionTypeIdIn => actionTypeIdIn,
1632 actionIdIn => actionIdList,
1633 childVersionStartDatesIn => childVersionStartDates,
1634 finalizeIn => false,
1635 processingDateIn => processingDateIn);
1636 end if;
1637 end if;
1638 if(finalizeIn) then
1639 close startDateCursor;
1640 commit;
1641 end if;
1642 exception
1643 when ame_util.objectVersionException then
1644 rollback;
1645 if(startDateCursor%isOpen) then
1646 close startDateCursor;
1647 end if;
1648 errorCode := -20001;
1649 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1650 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1651 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1652 routineNameIn => 'change',
1653 exceptionNumberIn => errorCode,
1654 exceptionStringIn => errorMessage);
1655 raise_application_error(errorCode,
1656 errorMessage);
1657 when objectVersionNoDataException then
1658 rollback;
1659 if(startDateCursor%isOpen) then
1660 close startDateCursor;
1661 end if;
1662 errorCode := -20001;
1663 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1664 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1665 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1666 routineNameIn => 'change',
1667 exceptionNumberIn => errorCode,
1668 exceptionStringIn => errorMessage);
1669 raise_application_error(errorCode,
1673 if(startDateCursor%isOpen) then
1670 errorMessage);
1671 when others then
1672 rollback;
1674 close startDateCursor;
1675 end if;
1676 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1677 routineNameIn => 'change',
1678 exceptionNumberIn => sqlcode,
1679 exceptionStringIn => '(action type ID ' ||
1680 actionTypeIdIn||
1681 ') ' ||
1682 sqlerrm);
1683 raise;
1684 end change;
1685 procedure changeAction(actionIdIn in integer,
1686 actionTypeIdIn in integer default null,
1687 descriptionIn in varchar2 default null,
1688 parameterIn in varchar2 default null,
1689 parameterTwoIn in varchar2 default null,
1690 finalizeIn in boolean default false,
1691 childVersionStartDateIn in date,
1692 parentVersionStartDateIn in date,
1693 processingDateIn in date default null) as
1694 cursor startDateCursor is
1695 select start_date
1696 from ame_action_types
1697 where action_type_id = actionTypeIdIn and
1698 sysdate between start_date and
1699 nvl(end_date - ame_util.oneSecond, sysdate)
1700 for update;
1701 cursor startDateCursor2 is
1702 select start_date
1703 from ame_actions
1704 where action_id = actionIdIn and
1705 sysdate between start_date and
1706 nvl(end_date - ame_util.oneSecond, sysdate)
1707 for update;
1708 actionId ame_actions.action_id%type;
1709 actionTypeId ame_action_types.action_type_id%type;
1710 currentUserId integer;
1711 description ame_actions.description%type;
1712 endDate date;
1713 errorCode integer;
1714 errorMessage ame_util.longestStringType;
1715 newActionTypeId ame_action_types.action_type_id%type;
1716 newStartDate date;
1717 nullDescriptionException exception;
1718 objectVersionNoDataException exception;
1719 startDate date;
1720 startDate2 date;
1721 tempCount integer;
1722 processingDate date;
1723 begin
1724 if processingDateIn is null then
1725 processingDate := sysdate;
1726 else
1727 processingDate := processingDateIn;
1728 end if;
1729 if(descriptionIn is null) and
1730 (getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn) = ame_util.booleanFalse) then
1731 raise nullDescriptionException;
1732 end if;
1733 if(finalizeIn) then
1734 open startDateCursor;
1735 fetch startDateCursor into startDate;
1736 if startDateCursor%notfound then
1737 raise objectVersionNoDataException;
1738 end if;
1739 if(parentVersionStartDateIn <> startDate) then
1740 close startDateCursor;
1741 raise ame_util.objectVersionException;
1742 end if;
1743 open startDateCursor2;
1744 fetch startDateCursor2 into startDate2;
1745 if startDateCursor2%notfound then
1746 raise objectVersionNoDataException;
1747 end if;
1748 if(childVersionStartDateIn <> startDate2) then
1749 close startDateCursor2;
1750 raise ame_util.objectVersionException;
1751 end if;
1752 end if;
1753 select count(*)
1754 into tempCount
1755 from ame_actions
1756 where
1757 action_id = actionIdIn and
1758 action_type_id = actionTypeIdIn and
1759 (descriptionIn is null or description = descriptionIn) and
1760 /* parameterIn is null means "set parameter to null,"
1761 because parameter defaults to null */
1762 ((parameterIn is null and parameter is null) or upper(parameter) = upper(parameterIn)) and
1763 sysdate between start_date and
1764 nvl(end_date - ame_util.oneSecond, sysdate) ;
1765 if(tempCount > 0) then
1766 return;
1767 end if;
1768 if(actionTypeIdIn is null) then
1769 actionTypeId := getActionTypeIdById(actionIdIn);
1770 else
1771 actionTypeId := actionTypeIdIn;
1772 end if;
1773 /*
1774 Always update to parameterIn, even if it's null.
1775 */
1776 currentUserId := ame_util.getCurrentUserId;
1777 /* make sure the end_date and start_date values do not overlap */
1778 endDate := sysdate ;
1779 newStartDate := sysdate;
1780 update ame_actions
1781 set
1782 last_updated_by = currentUserId,
1783 last_update_date = endDate,
1784 last_update_login = currentUserId,
1785 end_date = endDate
1786 where
1787 action_id = actionIdIn and
1788 processingDate between start_date and
1789 nvl(end_date - ame_util.oneSecond, processingDate) ;
1790 actionId := newAction(actionTypeIdIn => actionTypeId,
1791 descriptionIn => descriptionIn,
1792 updateParentObjectIn => true,
1793 finalizeIn => false,
1797 actionIdIn => actionIdIn,
1794 parameterIn => parameterIn,
1795 parameterTwoIn => parameterTwoIn,
1796 newStartDateIn => newStartDate,
1798 processingDateIn => processingDate);
1799 if(finalizeIn) then
1800 close StartDateCursor2;
1801 close StartDateCursor;
1802 commit;
1803 end if;
1804 exception
1805 when nullDescriptionException then
1806 rollback;
1807 if(startDateCursor2%isOpen) then
1808 close startDateCursor2;
1809 end if;
1810 if(startDateCursor%isOpen) then
1811 close startDateCursor;
1812 end if;
1813 errorCode := -20001;
1814 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1815 messageNameIn => 'AME_400137_ACT_EMPTY_DESC');
1816 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1817 routineNameIn => 'changeAction',
1818 exceptionNumberIn => errorCode,
1819 exceptionStringIn => errorMessage);
1820 raise_application_error(errorCode,
1821 errorMessage);
1822 when ame_util.objectVersionException then
1823 rollback;
1824 if(startDateCursor2%isOpen) then
1825 close startDateCursor2;
1826 end if;
1827 if(startDateCursor%isOpen) then
1828 close startDateCursor;
1829 end if;
1830 errorCode := -20001;
1831 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1832 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1833 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1834 routineNameIn => 'changeAction',
1835 exceptionNumberIn => errorCode,
1836 exceptionStringIn => errorMessage);
1837 raise_application_error(errorCode,
1838 errorMessage);
1839 when objectVersionNoDataException then
1840 rollback;
1841 if(startDateCursor2%isOpen) then
1842 close startDateCursor2;
1843 end if;
1844 if(startDateCursor%isOpen) then
1845 close startDateCursor;
1846 end if;
1847 errorCode := -20001;
1848 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1849 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1850 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1851 routineNameIn => 'changeAction',
1852 exceptionNumberIn => errorCode,
1853 exceptionStringIn => errorMessage);
1854 raise_application_error(errorCode,
1855 errorMessage);
1856 when others then
1857 rollback;
1858 if(startDateCursor2%isOpen) then
1859 close startDateCursor2;
1860 end if;
1861 if(startDateCursor%isOpen) then
1862 close startDateCursor;
1863 end if;
1864 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1865 routineNameIn => 'changeAction',
1866 exceptionNumberIn => sqlcode,
1867 exceptionStringIn => '(action ID ' ||
1868 actionIdIn||
1869 ') ' ||
1870 sqlerrm);
1871 raise;
1872 end changeAction;
1873 procedure changeActionTypeAndConfig(actionTypeIdIn in integer,
1874 ruleTypeIn in varchar2,
1875 orderNumberIn in integer,
1876 orderNumberUniqueIn in varchar2,
1877 childVersionStartDate2In in date,
1878 parentVersionStartDateIn in date,
1879 applicationIdIn in integer,
1880 descriptionQueryIn in varchar2 default null,
1881 chainOrderIngModeIn in varchar2 default null,
1882 votingRegimeIn in varchar2 default null,
1883 nameIn in varchar2 default null,
1884 procedureNameIn in varchar2 default null,
1885 descriptionIn in varchar2 default null,
1886 deleteListIn in ame_util.stringList default ame_util.emptyStringList,
1887 finalizeIn in boolean default false) as
1888 cursor startDateCursor is
1889 select start_date
1890 from ame_action_types
1891 where action_type_id = actionTypeIdIn and
1892 sysdate between start_date and
1893 nvl(end_date - ame_util.oneSecond, sysdate)
1894 for update;
1895 cursor startDateCursor2 is
1896 select start_date
1897 from ame_action_type_config
1898 where
1899 action_type_id = actionTypeIdIn and
1903 for update;
1900 application_id = applicationIdIn and
1901 sysdate between start_date and
1902 nvl(end_date - ame_util.oneSecond, sysdate)
1904 currentUserId integer;
1905 description ame_approval_groups.description%type;
1906 dynamicDescription ame_action_types.dynamic_description%type;
1907 errorCode integer;
1908 errorMessage ame_util.longestStringType;
1909 nullDescriptionQueryException exception;
1910 nullException exception;
1911 objectVersionNoDataException exception;
1912 processingDate date;
1913 startDate date;
1914 startDate2 date;
1915 begin
1916 processingDate := sysdate;
1917 /* Try to get a lock on the record. */
1918 open startDateCursor;
1919 fetch startDateCursor into startDate;
1920 if startDateCursor%notfound then
1921 raise objectVersionNoDataException;
1922 end if;
1923 if(parentVersionStartDateIn <> startDate) then
1924 close startDateCursor;
1925 raise ame_util.objectVersionException;
1926 end if;
1927 open startDateCursor2;
1928 fetch startDateCursor2 into startDate2;
1929 if startDateCursor2%notfound then
1930 raise objectVersionNoDataException;
1931 end if;
1932 if(childVersionStartDate2In <> startDate2) then
1933 close startDateCursor2;
1934 raise ame_util.objectVersionException;
1935 end if;
1936 if(nameIn is null or
1937 procedureNameIn is null or
1938 descriptionIn is null) then
1939 raise nullException;
1940 end if;
1941 dynamicDescription := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
1942 if(dynamicDescription = ame_util.booleanTrue) then
1943 if(descriptionQueryIn is null) then
1944 raise nullDescriptionQueryException;
1945 end if;
1946 end if;
1947 ame_action_pkg.change(actionTypeIdIn => actionTypeIdIn,
1948 nameIn => nameIn,
1949 procedureNameIn => procedureNameIn,
1950 descriptionIn => descriptionIn,
1951 descriptionQueryIn => descriptionQueryIn,
1952 ruleTypeIn => ruleTypeIn,
1953 deleteListIn => deleteListIn,
1954 processingDateIn => processingDate,
1955 finalizeIn => false);
1956 ame_action_pkg.changeActionTypeConfig(actionTypeIdIn => actionTypeIdIn,
1957 orderNumberIn => orderNumberIn,
1958 orderNumberUniqueIn => orderNumberUniqueIn,
1959 chainOrderingModeIn => chainOrderingModeIn,
1960 votingRegimeIn => votingRegimeIn,
1961 applicationIdIn => applicationIdIn,
1962 processingDateIn => processingDate,
1963 finalizeIn => false);
1964 close startDateCursor2;
1965 close startDateCursor;
1966 if(finalizeIn) then
1967 commit;
1968 end if;
1969 exception
1970 when ame_util.objectVersionException then
1971 rollback;
1972 if(startDateCursor%isOpen) then
1973 close startDateCursor;
1974 end if;
1975 if(startDateCursor2%isOpen) then
1976 close startDateCursor2;
1977 end if;
1978 errorCode := -20001;
1979 errorMessage :=
1980 ame_util.getMessage(applicationShortNameIn => 'PER',
1981 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1982 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1983 routineNameIn => 'changeActionTypeAndConfig',
1984 exceptionNumberIn => errorCode,
1985 exceptionStringIn => errorMessage);
1986 raise_application_error(errorCode,
1987 errorMessage);
1988 when objectVersionNoDataException then
1989 rollback;
1990 if(startDateCursor%isOpen) then
1991 close startDateCursor;
1992 end if;
1993 if(startDateCursor2%isOpen) then
1994 close startDateCursor2;
1995 end if;
1996 errorCode := -20001;
1997 errorMessage :=
1998 ame_util.getMessage(applicationShortNameIn => 'PER',
1999 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2000 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2001 routineNameIn => 'changeActionTypeAndConfig',
2002 exceptionNumberIn => errorCode,
2003 exceptionStringIn => errorMessage);
2004 raise_application_error(errorCode,
2005 errorMessage);
2006 when nullException then
2007 rollback;
2008 errorCode := -20001;
2009 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2010 messageNameIn => 'AME_400144_ACT_VALUE_APT_ENT');
2011 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2012 routineNameIn => 'changeActionTypeAndConfig',
2016 errorMessage);
2013 exceptionNumberIn => errorCode,
2014 exceptionStringIn => errorMessage);
2015 raise_application_error(errorCode,
2017 when nullDescriptionQueryException then
2018 rollback;
2019 errorCode := -20001;
2020 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2021 messageNameIn => 'AME_400373_ACT DYNAMIC_DESC3');
2022 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2023 routineNameIn => 'changeActionTypeAndConfig',
2024 exceptionNumberIn => errorCode,
2025 exceptionStringIn => errorMessage);
2026 raise_application_error(errorCode,
2027 errorMessage);
2028 when others then
2029 rollback;
2030 if(startDateCursor%isOpen) then
2031 close startDateCursor;
2032 end if;
2033 if(startDateCursor2%isOpen) then
2034 close startDateCursor2;
2035 end if;
2036 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2037 routineNameIn => 'changeActionTypeAndConfig',
2038 exceptionNumberIn => sqlcode,
2039 exceptionStringIn => '(action type ID ' ||
2040 actionTypeIdIn||
2041 ') ' ||
2042 sqlerrm);
2043 raise;
2044 end changeActionTypeAndConfig;
2045 procedure changeActionTypeConfig(applicationIdIn in integer,
2046 actionTypeIdIn in integer,
2047 orderNumberIn in integer,
2048 orderNumberUniqueIn in varchar2,
2049 processingDateIn in date,
2050 votingRegimeIn in varchar2 default null,
2051 chainOrderingModeIn in varchar2 default null,
2052 finalizeIn in boolean default false) as
2053 currentUserId integer;
2054 endDate date;
2055 errorCode integer;
2056 errorMessage ame_util.longestStringType;
2057 newOrderNumber integer;
2058 newStartDate date;
2059 oldOrderNumber integer;
2060 orderNumberException exception;
2061 oldOrderNumberUnique ame_util.stringType;
2062 updateOnlyATModified boolean;
2063 begin
2064 oldOrderNumber := getActionTypeOrderNumber(applicationIdIn => applicationIdIn,
2065 actionTypeIdIn => actionTypeIdIn);
2066 if(ame_action_pkg.orderNumberUnique(applicationIdIn => applicationIdIn,
2067 orderNumberIn => oldOrderNumber,
2068 actionTypeIdIn => actionTypeIdIn)) then
2069 oldOrderNumberUnique := ame_util.yes;
2070 else
2071 oldOrderNumberUnique := ame_util.no;
2072 end if;
2073 endDate := processingDateIn;
2074 newStartDate := processingDateIn;
2075 currentUserId := ame_util.getCurrentUserId;
2076 updateOnlyATModified := false;
2077 /* Check if order number was modified */
2078 if(oldOrderNumber = orderNumberIn) then
2079 if(orderNumberUniqueIn = oldOrderNumberUnique) then
2080 updateOnlyATModified := true; /* Order number not modified. */
2081 elsif(orderNumberUniqueIn = ame_util.yes) then
2082 /* Need to increment the order numbers to keep them in sequence. */
2083 incrementActionTypeOrdNumbers(applicationIdIn => applicationIdIn,
2084 actionTypeIdIn => actionTypeIdIn,
2085 orderNumberIn => orderNumberIn);
2086
2087 else /* The order number is not unique. */
2088 raise orderNumberException;
2089 end if;
2090 else
2091 update ame_action_type_config
2092 set
2093 last_updated_by = currentUserId,
2094 last_update_date = endDate,
2095 last_update_login = currentUserId,
2096 end_date = endDate
2097 where
2098 application_id = applicationIdIn and
2099 action_type_id = actionTypeIdIn and
2100 sysdate between start_date and
2101 nvl(end_date - ame_util.oneSecond, sysdate);
2102 if(oldOrderNumberUnique = ame_util.yes) then
2103 decrementActionTypeOrdNumbers(applicationIdIn => applicationIdIn,
2104 actionTypeIdIn => actionTypeIdIn,
2105 orderNumberIn => oldOrderNumber);
2106 if(orderNumberIn > oldOrderNumber)then
2107 newOrderNumber := (orderNumberIn - 1);
2108 else
2109 newOrderNumber := orderNumberIn;
2110 end if;
2111 else
2112 newOrderNumber := orderNumberIn;
2113 end if;
2114 if(orderNumberUniqueIn = ame_util.yes) then
2118 end if;
2115 incrementActionTypeOrdNumbers(applicationIdIn => applicationIdIn,
2116 actionTypeIdIn => actionTypeIdIn,
2117 orderNumberIn => newOrderNumber);
2119 insert into ame_action_type_config(application_id,
2120 action_type_id,
2121 voting_regime,
2122 order_number,
2123 chain_ordering_mode,
2124 created_by,
2125 creation_date,
2126 last_updated_by,
2127 last_update_date,
2128 last_update_login,
2129 start_date,
2130 end_date)
2131 values(applicationIdIn,
2132 actionTypeIdIn,
2133 votingRegimeIn,
2134 newOrderNumber,
2135 chainOrderingModeIn,
2136 currentUserId,
2137 newStartDate,
2138 currentUserId,
2139 newStartDate,
2140 currentUserId,
2141 newStartDate,
2142 null);
2143 end if;
2144 if(updateOnlyATModified) then
2145 update ame_action_type_config
2146 set
2147 last_updated_by = currentUserId,
2148 last_update_date = endDate,
2149 last_update_login = currentUserId,
2150 end_date = endDate
2151 where
2152 application_id = applicationIdIn and
2153 action_type_id = actionTypeIdIn and
2154 sysdate between start_date and
2155 nvl(end_date - ame_util.oneSecond, sysdate);
2156 insert into ame_action_type_config(application_id,
2157 action_type_id,
2158 voting_regime,
2159 order_number,
2160 chain_ordering_mode,
2161 created_by,
2162 creation_date,
2163 last_updated_by,
2164 last_update_date,
2165 last_update_login,
2166 start_date,
2167 end_date)
2168 values(applicationIdIn,
2169 actionTypeIdIn,
2170 votingRegimeIn,
2171 orderNumberIn,
2172 chainOrderingModeIn,
2173 currentUserId,
2174 newStartDate,
2175 currentUserId,
2176 newStartDate,
2177 currentUserId,
2178 newStartDate,
2179 null);
2180 end if;
2181 if(finalizeIn) then
2182 commit;
2183 end if;
2184 exception
2185 when orderNumberException then
2186 rollback;
2187 errorCode := -20001;
2188 errorMessage :=
2189 ame_util.getMessage(applicationShortNameIn => 'PER',
2190 messageNameIn => 'AME_400463_ORD_NUM_UNIQUE');
2191 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2192 routineNameIn => 'changeActionTypeConfig',
2193 exceptionNumberIn => errorCode,
2194 exceptionStringIn => errorMessage);
2195 raise_application_error(errorCode,
2196 errorMessage);
2197 when others then
2198 rollback;
2199 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2200 routineNameIn => 'changeActionTypeConfig',
2201 exceptionNumberIn => sqlcode,
2202 exceptionStringIn => '(action type ID ' ||
2203 actionTypeIdIn||
2204 ') ' ||
2205 sqlerrm);
2206 raise;
2207 end changeActionTypeConfig;
2208 procedure decrementActionTypeOrdNumbers(applicationIdIn in integer,
2209 actionTypeIdIn in integer,
2210 orderNumberIn in integer,
2211 finalizeIn in boolean default false) as
2212 cursor orderNumberCursor(applicationIdIn in integer,
2213 orderNumberIn in integer,
2214 ruleTypeIn in integer) is
2215 select ame_action_type_config.action_type_id,
2216 ame_action_type_config.order_number
2217 from ame_action_type_config,
2218 ame_action_type_usages
2219 where
2220 ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
2224 sysdate between ame_action_type_config.start_date and
2221 ame_action_type_config.application_id = applicationIdIn and
2222 ame_action_type_config.order_number > orderNumberIn and
2223 ame_action_type_usages.rule_type = ruleTypeIn and
2225 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
2226 sysdate between ame_action_type_usages.start_date and
2227 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2228 order by order_number;
2229 actionTypeIds ame_util.idList;
2230 chainOrderingMode ame_util.charType;
2231 currentUserId integer;
2232 orderNumbers ame_util.idList;
2233 processingDate date;
2234 ruleType integer;
2235 votingRegime ame_util.charType;
2236 begin
2237 currentUserId := ame_util.getCurrentUserId;
2238 processingDate := sysdate;
2239 ruleType := getAllowedRuleType(actionTypeIdIn => actionTypeIdIn);
2240 open orderNumberCursor(applicationIdIn => applicationIdIn,
2241 orderNumberIn => orderNumberIn,
2242 ruleTypeIn => ruleType);
2243 fetch orderNumberCursor bulk collect
2244 into actionTypeIds, orderNumbers;
2245 close orderNumberCursor;
2246 for i in 1 .. actionTypeIds.count loop
2247 votingRegime := getVotingRegime(actionTypeIdIn => actionTypeIds(i),
2248 applicationIdIn => applicationIdIn);
2249 chainOrderingMode := getChainOrderingMode(actionTypeIdIn => actionTypeIds(i),
2250 applicationIdIn => applicationIdIn);
2251 update ame_action_type_config
2252 set
2253 last_updated_by = currentUserId,
2254 last_update_date = processingDate,
2255 last_update_login = currentUserId,
2256 end_date = processingDate
2257 where
2258 application_id = applicationIdIn and
2259 action_type_id = actionTypeIds(i) and
2260 sysdate between start_date and
2261 nvl(end_date - ame_util.oneSecond, sysdate);
2262 insert into ame_action_type_config(application_id,
2263 action_type_id,
2264 voting_regime,
2265 order_number,
2266 chain_ordering_mode,
2267 created_by,
2268 creation_date,
2269 last_updated_by,
2270 last_update_date,
2271 last_update_login,
2272 start_date,
2273 end_date)
2274 values(applicationIdIn,
2275 actionTypeIds(i),
2276 votingRegime,
2277 (orderNumbers(i) - 1),
2278 chainOrderingMode,
2279 currentUserId,
2280 processingDate,
2281 currentUserId,
2282 processingDate,
2283 currentUserId,
2284 processingDate,
2285 null);
2286 end loop;
2287 if(finalizeIn) then
2288 commit;
2289 end if;
2290 exception
2291 when others then
2292 rollback;
2293 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2294 routineNameIn => 'decrementActionTypeOrdNumbers',
2295 exceptionNumberIn => sqlcode,
2296 exceptionStringIn => sqlerrm);
2297 raise;
2298 end decrementActionTypeOrdNumbers;
2299 procedure getActions(actionTypeIdIn in integer,
2300 actionsOut out nocopy ame_util.idStringTable) as
2301 cursor actionCursor(actionTypeIdIn in integer) is
2302 select
2303 action_id,
2304 description,
2305 parameter
2306 from ame_actions
2307 where
2308 action_type_id = actionTypeIdIn and
2309 sysdate between start_date and
2310 nvl(end_date - ame_util.oneSecond, sysdate)
2311 order by description;
2312 tempIndex integer;
2313 begin
2314 tempIndex := 1;
2315 /* If substitute action, need to check if approver is valid. */
2316 if(getActionTypeName(actionTypeIdIn) = ame_util.substitutionTypeName) then
2317 for tempAction in actionCursor(actionTypeIdIn => actionTypeIdIn) loop
2318 if(ame_approver_type_pkg.validateApprover(nameIn => tempAction.parameter)) then
2319 actionsOut(tempIndex).id := tempAction.action_id;
2320 actionsOut(tempIndex).string := tempAction.description;
2321 tempIndex := tempIndex + 1;
2322 end if;
2323 end loop;
2324 else
2325 for tempAction in actionCursor(actionTypeIdIn => actionTypeIdIn) loop
2326 actionsOut(tempIndex).id := tempAction.action_id;
2327 actionsOut(tempIndex).string := tempAction.description;
2328 tempIndex := tempIndex + 1;
2332 when others then
2329 end loop;
2330 end if;
2331 exception
2333 rollback;
2334 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2335 routineNameIn => 'getActions',
2336 exceptionNumberIn => sqlcode,
2337 exceptionStringIn => '(action type ID ' ||
2338 actionTypeIdIn||
2339 ') ' ||
2340 sqlerrm);
2341 actionsOut := ame_util.emptyIdStringTable;
2342 raise;
2343 end getActions;
2344 procedure getActions2(actionTypeIdIn in integer,
2345 actionIdsOut out nocopy ame_util.stringList,
2346 actionDescriptionsOut out nocopy ame_util.longStringList) as
2347 cursor actionCursor(actionTypeIdIn in integer) is
2348 select
2349 action_id,
2350 parameter
2351 from ame_actions
2352 where
2353 action_type_id = actionTypeIdIn and
2354 sysdate between start_date and
2355 nvl(end_date - ame_util.oneSecond, sysdate)
2356 order by description;
2357 tempIndex integer;
2358 begin
2359 tempIndex := 1;
2360 /* If substitute action, need to check if approver is valid. */
2361 if(getActionTypeName(actionTypeIdIn) = ame_util.substitutionTypeName) then
2362 for tempAction in actionCursor(actionTypeIdIn => actionTypeIdIn) loop
2363 if(ame_approver_type_pkg.validateApprover(nameIn => tempAction.parameter)) then
2364 actionIdsOut(tempIndex) := tempAction.action_id;
2365 actionDescriptionsOut(tempIndex) := getDescription(actionIdIn => tempAction.action_id);
2366 tempIndex := tempIndex + 1;
2367 end if;
2368 end loop;
2369 else
2370 for tempAction in actionCursor(actionTypeIdIn => actionTypeIdIn) loop
2371 actionIdsOut(tempIndex) := tempAction.action_id;
2372 actionDescriptionsOut(tempIndex) := getDescription(actionIdIn => tempAction.action_id);
2373 tempIndex := tempIndex + 1;
2374 end loop;
2375 end if;
2376 exception
2377 when others then
2378 rollback;
2379 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2380 routineNameIn => 'getActions2',
2381 exceptionNumberIn => sqlcode,
2382 exceptionStringIn => '(action type ID ' ||
2383 actionTypeIdIn||
2384 ') ' ||
2385 sqlerrm);
2386 actionIdsOut := ame_util.emptyStringList;
2387 actionDescriptionsOut := ame_util.emptyLongStringList;
2388 raise;
2389 end getActions2;
2390 procedure getActions3(actionTypeIdIn in integer,
2391 dynamicDescriptionIn in varchar2,
2392 actionTypeNamesOut out nocopy ame_util.stringList,
2393 actionIdsOut out nocopy ame_util.idList,
2394 actionParametersOut out nocopy ame_util.stringList,
2395 actionDescriptionsOut out nocopy ame_util.stringList,
2396 actionCreatedByOut out nocopy ame_util.idList) as
2397 cursor actionCursor(actionTypeIdIn in integer) is
2398 select
2399 ame_action_types.name,
2400 ame_action_types.description_query,
2401 ame_actions.action_id,
2402 ame_actions.description,
2403 ame_actions.parameter,
2404 ame_actions.parameter_two,
2405 ame_actions.created_by
2406 from ame_action_types,
2407 ame_actions
2408 where
2409 ame_action_types.action_type_id = ame_actions.action_type_id and
2410 ame_action_types.action_type_id = actionTypeIdIn and
2411 sysdate between ame_action_types.start_date and
2412 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2413 sysdate between ame_actions.start_date and
2414 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate)
2415 order by ame_actions.created_by, ame_actions.description;
2416 parameterOne ame_actions.parameter%type;
2417 parameterTwo ame_actions.parameter_two%type;
2418 tempIndex integer;
2419 begin
2420 tempIndex := 1;
2421 for tempAction in actionCursor(actionTypeIdIn => actionTypeIdIn) loop
2422 actionTypeNamesOut(tempIndex) := tempAction.name;
2423 actionIdsOut(tempIndex) := tempAction.action_id;
2424 actionParametersOut(tempIndex) := tempAction.parameter;
2425 actionCreatedByOut(tempIndex) := tempAction.created_by;
2426 if(dynamicDescriptionIn = ame_util.booleanFalse) then
2427 actionDescriptionsOut(tempIndex) := tempAction.description;
2428 else
2429 begin
2430 if(instrb(tempAction.description_query, ame_util.actionParameterOne) > 0) then
2431 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* both parameters */
2432 execute immediate tempAction.description_query
2433 into actionDescriptionsOut(tempIndex) using
2434 in tempAction.parameter,
2435 in tempAction.parameter_two;
2436 else /* just parameter_one */
2440 end if;
2437 execute immediate tempAction.description_query into
2438 actionDescriptionsOut(tempIndex) using
2439 in tempAction.parameter;
2441 else
2442 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* just paramter_two */
2443 execute immediate tempAction.description_query
2444 into actionDescriptionsOut(tempIndex) using
2445 in tempAction.parameter_two;
2446 else /* neither */
2447 execute immediate tempAction.description_query into
2448 actionDescriptionsOut(tempIndex);
2449 end if;
2450 end if;
2451 exception when others then
2452 actionDescriptionsOut(tempIndex) := 'Invalid description';
2453 end;
2454 end if;
2455 tempIndex := tempIndex + 1;
2456 end loop;
2457 exception
2458 when others then
2459 rollback;
2460 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2461 routineNameIn => 'getActions3',
2462 exceptionNumberIn => sqlcode,
2463 exceptionStringIn => '(action type ID ' ||
2464 actionTypeIdIn||
2465 ') ' ||
2466 sqlerrm);
2467 actionTypeNamesOut := ame_util.emptyStringList;
2468 actionIdsOut := ame_util.emptyIdList;
2469 actionParametersOut := ame_util.emptyStringList;
2470 actionDescriptionsOut := ame_util.emptyStringList;
2471 actionCreatedByOut := ame_util.emptyIdList;
2472 raise;
2473 end getActions3;
2474 procedure getActions4(actionTypeIdIn in integer,
2475 actionIdsOut out nocopy ame_util.stringList,
2476 actionDescriptionsOut out nocopy ame_util.stringList) as
2477 cursor actionsCursor is
2478 select ame_actions.action_id,
2479 ame_actions.parameter,
2480 ame_actions.parameter_two,
2481 ame_actions.description,
2482 ame_action_types.dynamic_description,
2483 ame_action_types.description_query
2484 from ame_actions,
2485 ame_action_types
2486 where
2487 ame_actions.action_type_id = ame_action_types.action_type_id and
2488 ame_actions.action_type_id = actionTypeIdIn and
2489 sysdate between ame_actions.start_date and
2490 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
2491 sysdate between ame_action_types.start_date and
2492 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
2493 order by ame_actions.created_by, ame_actions.description;
2494 actionId integer;
2495 tempIndex integer;
2496 begin
2497 tempIndex := 1;
2498 for tempAction in actionsCursor loop
2499 actionIdsOut(tempIndex) := tempAction.action_id;
2500 if(tempAction.dynamic_description = ame_util.booleanTrue) then
2501 begin
2502 if(instrb(tempAction.description_query, ame_util.actionParameterOne) > 0) then
2503 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* both parameters */
2504 execute immediate tempAction.description_query
2505 into actionDescriptionsOut(tempIndex) using
2506 in tempAction.parameter,
2507 in tempAction.parameter_two;
2508 else /* just parameter_one */
2509 execute immediate tempAction.description_query into
2510 actionDescriptionsOut(tempIndex) using
2511 in tempAction.parameter;
2512 end if;
2513 else
2514 if(instrb(tempAction.description_query, ame_util.actionParameterTwo) > 0) then /* just paramter_two */
2515 execute immediate tempAction.description_query
2516 into actionDescriptionsOut(tempIndex) using
2517 in tempAction.parameter_two;
2518 else /* neither */
2519 execute immediate tempAction.description_query into
2520 actionDescriptionsOut(tempIndex);
2521 end if;
2522 end if;
2523 exception when others then
2524 actionDescriptionsOut(tempIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_INVALID_DESCRIPTION');
2525 end;
2526 else
2527 actionDescriptionsOut(tempIndex) := tempAction.description;
2528 end if;
2529 tempIndex := tempIndex + 1;
2530 end loop;
2531 exception
2532 when others then
2533 rollback;
2534 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2535 routineNameIn => 'getActions4',
2536 exceptionNumberIn => sqlcode,
2537 exceptionStringIn => sqlerrm);
2538 actionIdsOut := ame_util.emptyStringList;
2539 actionDescriptionsOut := ame_util.emptyStringList;
2540 raise;
2541 end getActions4;
2542 procedure getActionTypes(actionTypesOut out nocopy ame_util.idStringTable) as
2543 cursor actionTypeCursor is
2544 select
2545 action_type_id,
2546 name
2550 nvl(end_date - ame_util.oneSecond, sysdate)
2547 from ame_action_types
2548 where
2549 sysdate between start_date and
2551 order by name;
2552 tempIndex integer;
2553 begin
2554 tempIndex := 1;
2555 for tempActionType in actionTypeCursor loop
2556 actionTypesOut(tempIndex).id := tempActionType.action_type_id;
2557 actionTypesOut(tempIndex).string := tempActionType.name;
2558 tempIndex := tempIndex + 1;
2559 end loop;
2560 exception
2561 when others then
2562 rollback;
2563 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2564 routineNameIn => 'getActionTypes',
2565 exceptionNumberIn => sqlcode,
2566 exceptionStringIn => sqlerrm);
2567 actionTypesOut := ame_util.emptyIdStringTable;
2568 raise;
2569 end getActionTypes;
2570 procedure getActionTypes2(actionTypeIdsOut out nocopy ame_util.stringList,
2571 actionTypeNamesOut out nocopy ame_util.stringList) as
2572 cursor actionTypeCursor is
2573 select
2574 action_type_id,
2575 name
2576 from ame_action_types
2577 where
2578 sysdate between start_date and
2579 nvl(end_date - ame_util.oneSecond, sysdate)
2580 order by name;
2581 tempIndex integer;
2582 begin
2583 tempIndex := 1;
2584 for tempActionType in actionTypeCursor loop
2585 actionTypeIdsOut(tempIndex) := tempActionType.action_type_id;
2586 actionTypeNamesOut(tempIndex) := tempActionType.name;
2587 tempIndex := tempIndex + 1;
2588 end loop;
2589 exception
2590 when others then
2591 rollback;
2592 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2593 routineNameIn => 'getActionTypes2',
2594 exceptionNumberIn => sqlcode,
2595 exceptionStringIn => sqlerrm);
2596 actionTypeIdsOut := ame_util.emptyStringList;
2597 actionTypeNamesOut := ame_util.emptyStringList;
2598 raise;
2599 end getActionTypes2;
2600 procedure getActionTypes3(applicationIdIn in integer,
2601 actionTypeIdsOut out nocopy ame_util.stringList,
2602 actionTypeNamesOut out nocopy ame_util.stringList,
2603 actionTypeDescriptionsOut out nocopy ame_util.stringList,
2604 ruleTypesOut out nocopy ame_util.idList) as
2605 cursor actionTypeCursor is
2606 select ame_action_types.action_type_id,
2607 ame_action_types.name,
2608 ame_action_types.description,
2609 ame_action_type_usages.rule_type,
2610 ame_action_type_config.order_number
2611 from ame_action_types,
2612 ame_action_type_usages,
2613 ame_action_type_config
2614 where
2615 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2616 ame_action_types.action_type_id = ame_action_type_config.action_type_id and
2617 ame_action_type_config.application_id = applicationIdIn and
2618 ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
2619 sysdate between ame_action_types.start_date and
2620 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2621 sysdate between ame_action_type_usages.start_date and
2622 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
2623 sysdate between ame_action_type_config.start_date and
2624 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate)
2625 order by ame_action_type_usages.rule_type,
2626 ame_action_type_config.order_number,
2627 ame_action_types.name;
2628 tempIndex integer;
2629 begin
2630 tempIndex := 1;
2631 for tempActionType in actionTypeCursor loop
2632 actionTypeIdsOut(tempIndex) := tempActionType.action_type_id;
2633 actionTypeNamesOut(tempIndex) := tempActionType.name;
2634 actionTypeDescriptionsOut(tempIndex) := tempActionType.description;
2635 ruleTypesOut(tempIndex) := tempActionType.rule_type;
2636 tempIndex := tempIndex + 1;
2637 end loop;
2638 exception
2639 when others then
2640 rollback;
2641 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2642 routineNameIn => 'getActionTypes3',
2643 exceptionNumberIn => sqlcode,
2644 exceptionStringIn => sqlerrm);
2645 actionTypeIdsOut := ame_util.emptyStringList;
2646 actionTypeNamesOut := ame_util.emptyStringList;
2647 actionTypeDescriptionsOut := ame_util.emptyStringList;
2648 ruleTypesOut := ame_util.emptyIdList;
2649 raise;
2650 end getActionTypes3;
2651 procedure getActionTypeDescriptions(actionTypeIdsOut out nocopy ame_util.stringList,
2652 actionTypeDescriptionsOut out nocopy ame_util.stringList) as
2653 cursor actionTypeCursor is
2654 select
2655 action_type_id,
2659 sysdate between start_date and
2656 description
2657 from ame_action_types
2658 where
2660 nvl(end_date - ame_util.oneSecond, sysdate)
2661 order by description;
2662 tempIndex integer;
2663 begin
2664 tempIndex := 1;
2665 for tempActionType in actionTypeCursor loop
2666 actionTypeIdsOut(tempIndex) := tempActionType.action_type_id;
2667 actionTypeDescriptionsOut(tempIndex) := tempActionType.description;
2668 tempIndex := tempIndex + 1;
2669 end loop;
2670 exception
2671 when others then
2672 rollback;
2673 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2674 routineNameIn => 'getActionTypeDescriptions',
2675 exceptionNumberIn => sqlcode,
2676 exceptionStringIn => sqlerrm);
2677 actionTypeIdsOut := ame_util.emptyStringList;
2678 actionTypeDescriptionsOut := ame_util.emptyStringList;
2679 raise;
2680 end getActionTypeDescriptions;
2681 procedure getActionTypeUsages(actionTypeIdIn in integer,
2682 ruleTypesOut out nocopy ame_util.stringList) as
2683 cursor getRuleTypesCur(actionTypeIdIn in integer) is
2684 select rule_type
2685 from ame_action_type_usages
2686 where action_type_id = actionTypeIdIn and
2687 sysdate between start_date and
2688 nvl(end_date - ame_util.oneSecond, sysdate)
2689 order by rule_type;
2690 tempIndex integer;
2691 begin
2692 tempIndex := 1;
2693 for getRuleTypesRec in getRuleTypesCur(actionTypeIdIn) loop
2694 ruleTypesOut(tempIndex) := getRuleTypesRec.rule_type;
2695 tempIndex := tempIndex + 1;
2696 end loop;
2697 exception
2698 when others then
2699 rollback;
2700 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2701 routineNameIn => 'getActionTypeUsages',
2702 exceptionNumberIn => sqlcode,
2703 exceptionStringIn => '(action type ID ' ||
2704 actionTypeIdIn||
2705 ') ' ||
2706 sqlerrm);
2707 ruleTypesOut := ame_util.emptyStringList;
2708 raise;
2709 end getActionTypeUsages;
2710 procedure getActionTypeUsages2(actionTypeIdsOut out nocopy ame_util.idList,
2711 ruleTypesOut out nocopy ame_util.idList) as
2712 cursor getRuleTypesCur is
2713 select action_type_id,
2714 rule_type
2715 from ame_action_type_usages
2716 where
2717 /* There are two rows in ame_action_type_usages for list creation rules
2718 and list exception rules. Only grab on row, so here we're
2719 eliminating the exception rule. */
2720 rule_type <> ame_util.exceptionRuleType and
2721 sysdate between start_date and
2722 nvl(end_date - ame_util.oneSecond, sysdate)
2723 order by rule_type, action_type_id;
2724 begin
2725 open getRuleTypesCur;
2726 fetch getRuleTypesCur bulk collect
2727 into actionTypeIdsOut,
2728 ruleTypesOut;
2729 close getRuleTypesCur;
2730 exception
2731 when others then
2732 rollback;
2733 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2734 routineNameIn => 'getActionTypeUsages2',
2735 exceptionNumberIn => sqlcode,
2736 exceptionStringIn => sqlerrm);
2737 actionTypeIdsOut := ame_util.emptyIdList;
2738 ruleTypesOut := ame_util.emptyidList;
2739 raise;
2740 end getActionTypeUsages2;
2741 procedure getAllowedApproverTypes(actionTypeIdIn in integer,
2742 allowedApproverTypeIdsOut out nocopy ame_util.stringList,
2743 allowedApproverTypeNamesOut out nocopy ame_util.stringList) as
2744 cursor getApproverTypesCursor(actionTypeIdIn in integer) is
2745 select
2746 ame_approver_type_usages.approver_type_id,
2747 ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_name
2748 from ame_approver_types,
2749 ame_approver_type_usages
2750 where
2751 ame_approver_types.approver_type_id = ame_approver_type_usages.approver_type_id and
2752 ame_approver_type_usages.action_type_id = actionTypeIdIn and
2753 sysdate between
2754 ame_approver_types.start_date and
2755 nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
2756 sysdate between
2757 ame_approver_type_usages.start_date and
2758 nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
2759 order by approver_name;
2760 begin
2761 open getApproverTypesCursor(actionTypeIdIn => actionTypeIdIn);
2762 fetch getApproverTypesCursor bulk collect
2763 into allowedApproverTypeIdsOut,
2764 allowedApproverTypeNamesOut;
2765 close getApproverTypesCursor;
2766 exception
2767 when others then
2768 rollback;
2769 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2773 actionTypeIdIn||
2770 routineNameIn => 'getAllowedApproverTypes',
2771 exceptionNumberIn => sqlcode,
2772 exceptionStringIn => '(action type ID ' ||
2774 ') ' ||
2775 sqlerrm);
2776 allowedApproverTypeIdsOut := ame_util.emptyStringList;
2777 allowedApproverTypeNamesOut := ame_util.emptyStringList;
2778 raise;
2779 end getAllowedApproverTypes;
2780 procedure getAllowedRuleTypeLabels(allowedRuleTypesOut out nocopy ame_util.stringList,
2781 allowedRuleTypeLabelsOut out nocopy ame_util.stringList) as
2782 begin
2783 allowedRuleTypesOut(1) := ame_util.preListGroupRuleType;
2784 allowedRuleTypesOut(2) := ame_util.authorityRuleType; -- or ame_util.exceptionRuleType
2785 allowedRuleTypesOut(3) := ame_util.listModRuleType;
2786 allowedRuleTypesOut(4) := ame_util.substitutionRuleType;
2787 allowedRuleTypesOut(5) := ame_util.postListGroupRuleType;
2788 allowedRuleTypesOut(6) := ame_util.productionRuleType;
2789 allowedRuleTypeLabelsOut(1) := ame_util.getLabel(ame_util.perFndAppId,'AME_PRE_APPROVAL');
2790 allowedRuleTypeLabelsOut(2) := ame_util.getLabel(ame_util.perFndAppId,'AME_CHAIN_OF_AUTHORITY');
2791 allowedRuleTypeLabelsOut(3) := ame_util.getLabel(ame_util.perFndAppId,'AME_LIST_MODIFICATION2');
2792 allowedRuleTypeLabelsOut(4) := ame_util.getLabel(ame_util.perFndAppId,'AME_SUBSTITUTION');
2793 allowedRuleTypeLabelsOut(5) := ame_util.getLabel(ame_util.perFndAppId,'AME_POST_APPROVAL');
2794 allowedRuleTypeLabelsOut(6) := ame_util.getLabel(ame_util.perFndAppId,'AME_PRODUCTION');
2795 exception
2796 when others then
2797 rollback;
2798 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2799 routineNameIn => 'getAllowedRuleTypeLabels',
2800 exceptionNumberIn => sqlcode,
2801 exceptionStringIn => sqlerrm);
2802 allowedRuleTypesOut := ame_util.emptyStringList;
2803 allowedRuleTypeLabelsOut := ame_util.emptyStringList;
2804 raise;
2805 end getAllowedRuleTypeLabels;
2806 procedure getAvailableActionTypes(applicationIdIn in integer,
2807 ruleTypeIn in integer,
2808 actionTypeIdsOut out nocopy ame_util.stringList,
2809 actionTypeDescriptionsOut out nocopy ame_util.stringList) as
2810 cursor actionTypeCursor(ruleTypeIn in varchar2,
2811 allowAllApproverTypesIn in varchar2,
2812 allowProductionsIn in varchar2) is
2813 select
2814 ame_action_types.action_type_id action_type_id,
2815 ame_action_types.description description
2816 from
2817 ame_action_types,
2818 ame_action_type_usages
2819 where
2820 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2821 ((allowProductionsIn = ame_util.yes and ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2822 ame_action_type_usages.rule_type = ruleTypeIn) and
2823 (allowAllApproverTypesIn = ame_util.yes or
2824 ame_action_types.action_type_id in (
2825 select distinct action_type_id
2826 from ame_approver_type_usages
2827 where
2828 (approver_type_id = ame_util.anyApproverType or
2829 approver_type_id in
2830 (select approver_type_id
2831 from ame_approver_types
2832 where
2833 orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
2834 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2835 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2836 sysdate between
2837 ame_action_types.start_date and
2838 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2839 sysdate
2840 between ame_action_type_usages.start_date and
2841 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2842 order by upper(description);
2843 actionTypeIds ame_util.stringList;
2844 actionTypeIds2 ame_util.stringList;
2845 actionTypeDescriptions ame_util.stringList;
2846 actionTypeDescriptions2 ame_util.stringList;
2847 allowAllApproverTypes ame_util.stringType;
2848 allowProductions ame_util.stringType;
2849 lineItemActionTypeId integer;
2850 lineItemClassCount integer;
2851 ruleType integer;
2852 tempIndex integer;
2853 begin
2854 if(ruleTypeIn = ame_util.exceptionRuleType) then
2855 ruleType := ame_util.authorityRuleType;
2856 else
2857 ruleType := ruleTypeIn;
2858 end if;
2859 select count(*)
2860 into lineItemClassCount
2861 from ame_item_classes, ame_item_class_usages
2862 where
2863 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
2864 ame_item_class_usages.application_id = applicationIdIn and
2865 ame_item_classes.name = ame_util.lineItemItemClassName and
2866 sysdate between
2870 between ame_item_class_usages.start_date and
2867 ame_item_classes.start_date and
2868 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2869 sysdate
2871 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate);
2872 allowProductions := ame_util.getConfigVar(variableNameIn => ame_util.productionConfigVar,
2873 applicationIdIn => applicationIdIn);
2874 /*
2875 Transform the configuration-variable value into one of the pseudo-boolean values used by
2876 configuration variables, for ease of use in the cursor.
2877 */
2878 if(allowProductions = ame_util.perApproverProductions or
2879 allowProductions = ame_util.allProductions) then
2880 allowProductions := ame_util.yes;
2881 else
2882 allowProductions := ame_util.no;
2883 end if;
2884 allowAllApproverTypes := ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2885 applicationIdIn => applicationIdIn);
2886 open actionTypeCursor(ruleTypeIn => ruleType,
2887 allowAllApproverTypesIn => allowAllApproverTypes,
2888 allowProductionsIn => allowProductions);
2889 if(lineItemClassCount > 0) then
2890 fetch actionTypeCursor bulk collect
2891 into
2892 actionTypeIdsOut,
2893 actionTypeDescriptionsOut;
2894 close actionTypeCursor;
2895 else
2896 lineItemActionTypeId :=
2897 getActionTypeIdByName(actionTypeNameIn => ame_util.lineItemJobLevelTypeName);
2898 fetch actionTypecursor bulk collect
2899 into
2900 actionTypeIds,
2901 actionTypeDescriptions;
2902 close actionTypeCursor;
2903 for i in 1 .. actionTypeIds.count loop
2904 if(actionTypeIds(i) <> lineItemActionTypeId) then
2905 actionTypeIds2(i) := actionTypeIds(i);
2906 actionTypeDescriptions2(i) := actionTypeDescriptions(i);
2907 end if;
2908 end loop;
2909 ame_util.compactStringList(stringListInOut => actionTypeIds2);
2910 ame_util.compactStringList(stringListInOut => actionTypeDescriptions2);
2911 actionTypeIdsOut := actionTypeIds2;
2912 actionTypeDescriptionsOut := actionTypeDescriptions2;
2913 end if;
2914 exception
2915 when others then
2916 rollback;
2917 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2918 routineNameIn => 'getAvailableActionTypes',
2919 exceptionNumberIn => sqlcode,
2920 exceptionStringIn => sqlerrm);
2921 actionTypeIdsOut := ame_util.emptyStringList;
2922 actionTypeDescriptionsOut := ame_util.emptyStringList;
2923 raise;
2924 end getAvailableActionTypes;
2925 procedure getAvailCombActionTypes(applicationIdIn in integer,
2926 subOrListModActsForCombRuleIn in varchar2,
2927 actionTypeIdsOut out nocopy ame_util.stringList,
2928 actionTypeDescriptionsOut out nocopy ame_util.stringList) as
2929 cursor actionTypeCursor(allowAllApproverTypesIn in varchar2,
2930 allowProductionsIn in varchar2,
2931 subOrListModActsForCombRuleIn in varchar2) is
2932 select
2933 ame_action_types.action_type_id action_type_id,
2934 ame_action_types.description description
2935 from
2936 ame_action_types,
2937 ame_action_type_usages
2938 where
2939 ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2940 ((allowProductionsIn = ame_util.yes and
2941 ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2942 (subOrListModActsForCombRuleIn = ame_util.no and
2943 ame_action_type_usages.rule_type in
2944 (ame_util.authorityRuleType,
2945 ame_util.preListGroupRuleType,
2946 ame_util.postListGroupRuleType)) or
2947 (subOrListModActsForCombRuleIn = ame_util.yes and
2948 ame_action_type_usages.rule_type in
2949 (ame_util.listModRuleType,
2950 ame_util.substitutionRuleType))) and
2951 (allowAllApproverTypesIn = ame_util.yes or
2952 ame_action_types.action_type_id in (
2953 select distinct action_type_id
2954 from ame_approver_type_usages
2955 where
2956 (approver_type_id = ame_util.anyApproverType or
2957 approver_type_id in
2958 (select approver_type_id
2959 from ame_approver_types
2960 where
2961 orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
2962 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2963 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2964 sysdate between
2965 ame_action_types.start_date and
2966 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2967 sysdate
2971 actionTypeIds ame_util.stringList;
2968 between ame_action_type_usages.start_date and
2969 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2970 order by upper(description);
2972 actionTypeIds2 ame_util.stringList;
2973 actionTypeDescriptions ame_util.stringList;
2974 actionTypeDescriptions2 ame_util.stringList;
2975 allowProductions ame_util.stringType;
2976 allowAllApprovertypes ame_util.stringType;
2977 lineItemActionTypeId integer;
2978 lineItemClassCount integer;
2979 begin
2980 select count(*)
2981 into lineItemClassCount
2982 from ame_item_classes, ame_item_class_usages
2983 where
2984 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
2985 ame_item_class_usages.application_id = applicationIdIn and
2986 ame_item_classes.name = ame_util.lineItemItemClassName and
2987 sysdate between
2988 ame_item_classes.start_date and
2989 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2990 sysdate
2991 between ame_item_class_usages.start_date and
2992 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate);
2993 allowProductions := ame_util.getConfigVar(variableNameIn => ame_util.productionConfigVar,
2994 applicationIdIn => applicationIdIn);
2995 /*
2996 Transform the configuration-variable value into one of the pseudo-boolean values used by
2997 configuration variables, for ease of use in the cursor.
2998 */
2999 if(allowProductions = ame_util.perApproverProductions or
3000 allowProductions = ame_util.allProductions) then
3001 allowProductions := ame_util.yes;
3002 else
3003 allowProductions := ame_util.no;
3004 end if;
3005 allowAllApproverTypes := ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
3006 applicationIdIn => applicationIdIn);
3007 open actionTypeCursor(allowAllApproverTypesIn => allowAllApproverTypes,
3008 allowProductionsIn => allowProductions,
3009 subOrListModActsForCombRuleIn => subOrListModActsForCombRuleIn);
3010 --
3011 if(lineItemClassCount > 0) then
3012 fetch actionTypeCursor bulk collect
3013 into
3014 actionTypeIdsOut,
3015 actionTypeDescriptionsOut;
3016 close actionTypeCursor;
3017 else
3018 lineItemActionTypeId :=
3019 getActionTypeIdByName(actionTypeNameIn => ame_util.lineItemJobLevelTypeName);
3020 fetch actionTypecursor bulk collect
3021 into
3022 actionTypeIds,
3023 actionTypeDescriptions;
3024 close actionTypeCursor;
3025 for i in 1 .. actionTypeIds.count loop
3026 if(actionTypeIds(i) <> lineItemActionTypeId) then
3027 actionTypeIds2(i) := actionTypeIds(i);
3028 actionTypeDescriptions2(i) := actionTypeDescriptions(i);
3029 end if;
3030 end loop;
3031 ame_util.compactStringList(stringListInOut => actionTypeIds2);
3032 ame_util.compactStringList(stringListInOut => actionTypeDescriptions2);
3033 actionTypeIdsOut := actionTypeIds2;
3034 actionTypeDescriptionsOut := actionTypeDescriptions2;
3035 end if;
3036 exception
3037 when others then
3038 rollback;
3039 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3040 routineNameIn => 'getAvailCombActionTypes',
3041 exceptionNumberIn => sqlcode,
3042 exceptionStringIn => sqlerrm);
3043 actionTypeIdsOut := ame_util.emptyStringList;
3044 actionTypeDescriptionsOut := ame_util.emptyStringList;
3045 raise;
3046 end getAvailCombActionTypes;
3047 procedure incrementActionTypeOrdNumbers(applicationIdIn in integer,
3048 actionTypeIdIn in integer,
3049 orderNumberIn in integer,
3050 finalizeIn in boolean default false) as
3051 cursor orderNumberCursor(applicationIdIn in integer,
3052 actionTypeIdIn in integer,
3053 orderNumberIn in integer,
3054 ruleTypeIn in integer) is
3055 select ame_action_type_config.action_type_id,
3056 ame_action_type_config.order_number
3057 from ame_action_type_config,
3058 ame_action_type_usages
3059 where
3060 ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
3061 ame_action_type_config.application_id = applicationIdIn and
3062 ame_action_type_config.action_type_id <> actionTypeIdIn and
3063 ame_action_type_config.order_number >= orderNumberIn and
3064 ame_action_type_usages.rule_type = ruleTypeIn and
3065 sysdate between ame_action_type_config.start_date and
3066 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
3070 actionTypeIds ame_util.idList;
3067 sysdate between ame_action_type_usages.start_date and
3068 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
3069 order by order_number;
3071 chainOrderingMode ame_util.charType;
3072 currentUserId integer;
3073 orderNumbers ame_util.idList;
3074 processingDate date;
3075 ruleType integer;
3076 votingRegime ame_util.charType;
3077 begin
3078 currentUserId := ame_util.getCurrentUserId;
3079 processingDate := sysdate;
3080 ruleType := getAllowedRuleType(actionTypeIdIn => actionTypeIdIn);
3081 open orderNumberCursor(applicationIdIn => applicationIdIn,
3082 actionTypeIdIn => actionTypeIdIn,
3083 orderNumberIn => orderNumberIn,
3084 ruleTypeIn => ruleType);
3085 fetch orderNumberCursor bulk collect
3086 into actionTypeIds, orderNumbers;
3087 close orderNumberCursor;
3088 for i in 1 .. actionTypeIds.count loop
3089 votingRegime := getVotingRegime(actionTypeIdIn => actionTypeIds(i),
3090 applicationIdIn => applicationIdIn);
3091 chainOrderingMode := getChainOrderingMode(actionTypeIdIn => actionTypeIds(i),
3092 applicationIdIn => applicationIdIn);
3093 update ame_action_type_config
3094 set
3095 last_updated_by = currentUserId,
3096 last_update_date = processingDate,
3097 last_update_login = currentUserId,
3098 end_date = processingDate
3099 where
3100 application_id = applicationIdIn and
3101 action_type_id = actionTypeIds(i) and
3102 sysdate between start_date and
3103 nvl(end_date - ame_util.oneSecond, sysdate);
3104 insert into ame_action_type_config(application_id,
3105 action_type_id,
3106 voting_regime,
3107 order_number,
3108 chain_ordering_mode,
3109 created_by,
3110 creation_date,
3111 last_updated_by,
3112 last_update_date,
3113 last_update_login,
3114 start_date,
3115 end_date)
3116 values(applicationIdIn,
3117 actionTypeIds(i),
3118 votingRegime,
3119 (orderNumbers(i) + 1),
3120 chainOrderingMode,
3121 currentUserId,
3122 processingDate,
3123 currentUserId,
3124 processingDate,
3125 currentUserId,
3126 processingDate,
3127 null);
3128 end loop;
3129 if(finalizeIn) then
3130 commit;
3131 end if;
3132 exception
3133 when others then
3134 rollback;
3135 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3136 routineNameIn => 'incrementActionTypeOrdNumbers',
3137 exceptionNumberIn => sqlcode,
3138 exceptionStringIn => sqlerrm);
3139 raise;
3140 end incrementActionTypeOrdNumbers;
3141 procedure newActionTypeConfig(applicationIdIn in integer,
3142 actionTypeIdIn in integer,
3143 ruleTypeIn in integer,
3144 orderNumberUniqueIn in varchar2,
3145 orderNumberIn in integer,
3146 chainOrderingModeIn in varchar2,
3147 votingRegimeIn in varchar2,
3148 finalizeIn in boolean default false) as
3149 cursor applicationIdCursor is
3150 select application_id
3151 from ame_calling_apps
3152 where
3153 sysdate between start_date and
3154 nvl(end_date - ame_util.oneSecond, sysdate)
3155 order by application_id;
3156 applicationId integer;
3157 applicationIds ame_util.idList;
3158 chainOrderingMode ame_util.charType;
3159 currentUserId integer;
3160 maxOrderNumber integer;
3161 orderNumber ame_action_type_config.order_number%type;
3162 processingDate date;
3163 tempCount integer;
3164 votingRegime ame_util.charType;
3165 begin
3166 currentUserId := ame_util.getCurrentUserId;
3167 processingDate := sysdate;
3168 maxOrderNumber :=
3169 ame_action_pkg.getActionTypeMaxOrderNumber(applicationIdIn => applicationIdIn,
3170 ruleTypeIn => ruleTypeIn);
3171 open applicationIdCursor;
3172 fetch applicationIdCursor bulk collect
3173 into
3174 applicationIds;
3178 applicationId := applicationIds(i);
3175 close applicationIdCursor;
3176 for i in 1 .. applicationIds.count loop
3177 if(applicationIds(i) = applicationIdIn) then
3179 orderNumber := orderNumberIn;
3180 votingRegime := votingRegimeIn;
3181 chainOrderingMode := chainOrderingModeIn;
3182 else
3183 applicationId := applicationIds(i);
3184 votingRegime := ame_util.serializedVoting;
3185 chainOrderingMode := ame_util.serialChainsMode;
3186 select count(*)
3187 into tempCount
3188 from ame_action_type_config
3189 where
3190 application_id = applicationIds(i) and
3191 sysdate between start_date and
3192 nvl(end_date - ame_util.oneSecond, sysdate);
3193 if(tempCount = 0) then
3194 orderNumber := 1;
3195 else
3196 select (nvl(max(order_number), 0) + 1)
3197 into orderNumber
3198 from ame_action_type_config,
3199 ame_action_type_usages
3200 where
3201 ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
3202 ame_action_type_config.application_id = applicationIds(i) and
3203 ame_action_type_usages.rule_type = ruleTypeIn and
3204 sysdate between ame_action_type_config.start_date and
3205 nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
3206 sysdate between ame_action_type_usages.start_date and
3207 nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
3208 end if;
3209 end if;
3210 insert into ame_action_type_config(application_id,
3211 action_type_id,
3212 voting_regime,
3213 order_number,
3214 chain_ordering_mode,
3215 created_by,
3216 creation_date,
3217 last_updated_by,
3218 last_update_date,
3219 last_update_login,
3220 start_date,
3221 end_date)
3222 values(applicationId,
3223 actionTypeIdIn,
3224 votingRegime,
3225 orderNumber,
3226 chainOrderingMode,
3227 currentUserId,
3228 processingDate,
3229 currentUserId,
3230 processingDate,
3231 currentUserId,
3232 processingDate,
3233 null);
3234 end loop;
3235 if(orderNumberUniqueIn = ame_util.yes) then
3236 if(orderNumberIn <> (maxOrderNumber + 1)) then
3237 incrementActionTypeOrdNumbers(applicationIdIn => applicationIdIn,
3238 actionTypeIdIn => actionTypeIdIn,
3239 orderNumberIn => orderNumberIn);
3240 end if;
3241 end if;
3242 if(finalizeIn) then
3243 commit;
3244 end if;
3245 exception
3246 when others then
3247 rollback;
3248 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3249 routineNameIn => 'newActionTypeConfig',
3250 exceptionNumberIn => sqlcode,
3251 exceptionStringIn => sqlerrm);
3252 end newActionTypeConfig;
3253 procedure newActionTypeUsage(actionTypeIdIn in integer,
3254 ruleTypeIn in integer,
3255 finalizeIn in boolean default false,
3256 processingDateIn in date default null) as
3257 createdBy integer;
3258 currentUserId integer;
3259 processingDate date;
3260 tempCount integer;
3261 begin
3262 if processingDateIn is null then
3263 processingDate := sysdate;
3264 else
3265 processingDate := processingDateIn;
3266 end if;
3267 currentUserId := ame_util.getCurrentUserId;
3268 select count(*)
3269 into tempCount
3270 from ame_action_type_usages
3271 where
3272 action_type_id = actionTypeIdIn and
3273 created_by = ame_util.seededDataCreatedById;
3274 if(tempCount > 0) then
3275 createdBy := ame_util.seededDataCreatedById;
3276 else
3277 createdBy := currentUserId;
3278 end if;
3279 if(ruleTypeIn = ame_util.exceptionRuleType) then
3280 /* chain of authority so insert two rows,
3281 one for list-creation and one for list-exception */
3282 for i in 1 .. 2 loop
3283 insert into ame_action_type_usages
3284 (action_type_id,
3285 rule_type,
3286 created_by,
3287 creation_date,
3288 last_updated_by,
3289 last_update_date,
3293 values (actionTypeIdIn,
3290 last_update_login,
3291 start_date,
3292 end_date)
3294 i,
3295 createdBy,
3296 processingDate,
3297 currentUserId,
3298 processingDate,
3299 currentUserId,
3300 processingDate,
3301 null);
3302 end loop;
3303 else
3304 insert into ame_action_type_usages
3305 (action_type_id,
3306 rule_type,
3307 created_by,
3308 creation_date,
3309 last_updated_by,
3310 last_update_date,
3311 last_update_login,
3312 start_date,
3313 end_date)
3314 values(actionTypeIdIn,
3315 ruleTypeIn,
3316 createdBy,
3317 processingDate,
3318 currentUserId,
3319 processingDate,
3320 currentUserId,
3321 processingDate,
3322 null);
3323 end if;
3324 if(finalizeIn) then
3325 commit;
3326 end if;
3327 exception
3328 when others then
3329 rollback;
3330 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3331 routineNameIn => 'newActionTypeUsage',
3332 exceptionNumberIn => sqlcode,
3333 exceptionStringIn => '(action type ID ' ||
3334 actionTypeIdIn||
3335 ') ' ||
3336 sqlerrm);
3337 raise;
3338 end newActionTypeUsage;
3339 procedure remove(actionTypeIdIn in integer,
3340 finalizeIn in boolean default false,
3341 parentVersionStartDateIn in date,
3342 processingDateIn in date default null) as
3343 cursor startDateCursor is
3344 select start_date
3345 from ame_action_types
3346 where
3347 action_type_id = actionTypeIdIn and
3348 sysdate between start_date and
3349 nvl(end_date - ame_util.oneSecond, sysdate)
3350 for update;
3351 cursor applicationIdCursor is
3352 select application_id
3353 from ame_calling_apps
3354 where
3355 sysdate between start_date and
3356 nvl(end_date - ame_util.oneSecond, sysdate)
3357 order by application_id;
3358 applicationIds ame_util.idList;
3359 currentUserId integer;
3360 errorCode integer;
3361 errorMessage ame_util.longestStringType;
3362 inUseException exception;
3363 objectVersionNoDataException exception;
3364 orderNumber integer;
3365 startDate date;
3366 processingDate date;
3367 begin
3368 if processingDateIn is null then
3369 processingDate := sysdate;
3370 else
3371 processingDate := processingDateIn;
3372 end if;
3373 if(finalizeIn) then
3374 open startDateCursor;
3375 fetch startDateCursor into startDate;
3376 if startDateCursor%notfound then
3377 raise objectVersionNoDataException;
3378 end if;
3379 if(parentVersionStartDateIn <> startDate) then
3380 close startDateCursor;
3381 raise ame_util.objectVersionException;
3382 end if;
3383 end if;
3384 if(actionTypeIsInUse(actionTypeIdIn)) then
3385 raise inUseException;
3386 end if;
3387 open applicationIdCursor;
3388 fetch applicationIdCursor bulk collect
3389 into applicationIds;
3390 close applicationIdCursor;
3391 for i in 1 .. applicationIds.count loop
3392 select order_number
3393 into orderNumber
3394 from ame_action_type_config
3395 where
3396 application_id = applicationIds(i) and
3397 action_type_id = actionTypeIdIn and
3398 sysdate between start_date and
3399 nvl(end_date - ame_util.oneSecond, sysdate);
3400 if(orderNumberUnique(applicationIdIn => applicationIds(i),
3401 orderNumberIn => orderNumber,
3402 actionTypeIdIn => actionTypeIdIn)) then
3403 /* subtract 1 from the order number for those above the one being deleted */
3404 decrementActionTypeOrdNumbers(applicationIdIn => applicationIds(i),
3405 actionTypeIdIn => actionTypeIdIn,
3406 orderNumberIn => orderNumber,
3407 finalizeIn => false);
3408 end if;
3409 end loop;
3410 currentUserId := ame_util.getCurrentUserId;
3411 update ame_action_types
3412 set
3413 last_updated_by = currentUserId,
3417 where
3414 last_update_date = processingDate,
3415 last_update_login = currentUserId,
3416 end_date = processingDate
3418 action_type_id = actionTypeIdIn and
3419 processingDate between start_date and
3420 nvl(end_date - ame_util.oneSecond, processingDate);
3421 update ame_action_type_config
3422 set
3423 last_updated_by = currentUserId,
3424 last_update_date = processingDate,
3425 last_update_login = currentUserId,
3426 end_date = processingDate
3427 where
3428 action_type_id = actionTypeIdIn and
3429 processingDate between start_date and
3430 nvl(end_date - ame_util.oneSecond, processingDate);
3431 update ame_approver_type_usages
3432 set
3433 last_updated_by = currentUserId,
3434 last_update_date = processingDate,
3435 last_update_login = currentUserId,
3436 end_date = processingDate
3437 where
3438 action_type_id = actionTypeIdIn and
3439 processingDate between start_date and
3440 nvl(end_date - ame_util.oneSecond, processingDate);
3441 update ame_mandatory_attributes
3442 set
3443 last_updated_by = currentUserId,
3444 last_update_date = processingDate,
3445 last_update_login = currentUserId,
3446 end_date = processingDate
3447 where
3448 action_type_id = actionTypeIdIn and
3449 processingDate between start_date and
3450 nvl(end_date - ame_util.oneSecond, processingDate);
3451 removeActionTypeUsages(actionTypeIdIn => actionTypeIdIn,
3452 finalizeIn => false);
3453 if(finalizeIn) then
3454 close startDateCursor;
3455 commit;
3456 end if;
3457 exception
3458 when ame_util.objectVersionException then
3459 rollback;
3460 if(startDateCursor%isOpen) then
3461 close startDateCursor;
3462 end if;
3463 errorCode := -20001;
3464 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3465 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3466 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3467 routineNameIn => 'remove',
3468 exceptionNumberIn => errorCode,
3469 exceptionStringIn => errorMessage);
3470 raise_application_error(errorCode,
3471 errorMessage);
3472 when objectVersionNoDataException then
3473 rollback;
3474 if(startDateCursor%isOpen) then
3475 close startDateCursor;
3476 end if;
3477 errorCode := -20001;
3478 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3479 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3480 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3481 routineNameIn => 'remove',
3482 exceptionNumberIn => errorCode,
3483 exceptionStringIn => errorMessage);
3484 raise_application_error(errorCode,
3485 errorMessage);
3486 when inUseException then
3487 rollback;
3488 errorCode := -20001;
3489 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3490 messageNameIn => 'AME_400148_ACT_REM_APPR_ASSOC');
3491 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3492 routineNameIn => 'remove',
3493 exceptionNumberIn => errorCode,
3494 exceptionStringIn => errorMessage);
3495 raise_application_error(errorCode,
3496 errorMessage);
3497 when others then
3498 rollback;
3499 if(startDateCursor%isOpen) then
3500 close startDateCursor;
3501 end if;
3502 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3503 routineNameIn => 'remove',
3504 exceptionNumberIn => sqlcode,
3505 exceptionStringIn => '(action type ID ' ||
3506 actionTypeIdIn||
3507 ') ' ||
3508 sqlerrm);
3509 raise;
3510 end remove;
3511 procedure removeAction(actionTypeIdIn in integer,
3512 actionIdIn in ame_util.idList default ame_util.emptyIdList,
3513 childVersionStartDatesIn in ame_util.dateList,
3514 finalizeIn in boolean default false,
3515 processingDateIn in date default null) as
3516 cursor startDateCursor2(actionIdIn in integer) is
3517 select start_date
3518 from ame_actions
3519 where
3520 action_id = actionIdIn and
3521 sysdate between start_date and
3522 nvl(end_date - ame_util.oneSecond, sysdate)
3523 for update;
3524 actionTypeDescription ame_action_types.description%type;
3528 actionTypeName ame_action_types.name%type;
3525 actionTypeDescQuery ame_action_types.description_query%type;
3526 actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527 actionTypeId ame_action_types.action_type_id%type;
3529 actionTypeProcedureName ame_action_types.procedure_name%type;
3530 currentUserId integer;
3531 deleteCount integer;
3532 errorCode integer;
3533 errorMessage ame_util.longestStringType;
3534 inUseException exception;
3535 objectVersionNoDataException exception;
3536 startDate date;
3537 processingDate date;
3538 begin
3539 if processingDateIn is null then
3540 processingDate := sysdate;
3541 else
3542 processingDate := processingDateIn;
3543 end if;
3544 currentUserId := ame_util.getCurrentUserId;
3545 deleteCount := actionIdIn.count;
3546 for i in 1..deleteCount loop
3547 open startDateCursor2(actionIdIn(i));
3548 fetch startDateCursor2 into startDate;
3549 if startDateCursor2%notfound then
3550 raise objectVersionNoDataException;
3551 end if;
3552 if(childVersionStartDatesIn(i) = startDate) then
3553 if(isInUse(actionIdIn(i))) then
3554 raise inUseException;
3555 end if;
3556 update ame_actions
3557 set
3558 last_updated_by = currentUserId,
3559 last_update_date = processingDate,
3560 last_update_login = currentUserId,
3561 end_date = processingDate
3562 where
3563 action_id = actionIdIn(i) and
3564 processingDate between start_date and
3565 nvl(end_date - ame_util.oneSecond, processingDate) ;
3566 else
3567 close startDateCursor2;
3568 raise ame_util.objectVersionException;
3569 end if;
3570 close startDateCursor2;
3571 end loop;
3572 actionTypeName := getActionTypeName(actionTypeIdIn => actionTypeIdIn);
3573 actionTypeProcedureName := getActionTypeProcedureName(actionTypeIdIn => actionTypeIdIn);
3574 actionTypeDescription := getActionTypeDescription(actionTypeIdIn => actionTypeIdIn);
3575 actionTypeDescQuery := getActionTypeDescQuery(actionTypeIdIn => actionTypeIdIn);
3576 actionTypeDynamicDesc := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
3577 update ame_action_types
3578 set
3579 last_updated_by = currentUserId,
3580 last_update_date = processingDate,
3581 last_update_login = currentUserId,
3582 end_date = processingDate
3583 where
3584 action_type_id = actionTypeIdIn and
3585 processingDate between start_date and
3586 nvl(end_date - ame_util.oneSecond, processingDate) ;
3587 actionTypeId := new(nameIn => actionTypeName,
3588 procedureNameIn => actionTypeProcedureName,
3589 descriptionIn => actionTypeDescription,
3590 actionTypeIdIn => actionTypeIdIn,
3591 descriptionQueryIn => actionTypeDescQuery,
3592 dynamicDescriptionIn => actionTypeDynamicDesc,
3593 finalizeIn => false,
3594 processingDateIn => processingDate);
3595 exception
3596 when ame_util.objectVersionException then
3597 rollback;
3598 if(startDateCursor2%isOpen) then
3599 close startDateCursor2;
3600 end if;
3601 errorCode := -20001;
3602 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3603 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3604 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3605 routineNameIn => 'removeAction',
3606 exceptionNumberIn => errorCode,
3607 exceptionStringIn => errorMessage);
3608 raise_application_error(errorCode,
3609 errorMessage);
3610 when objectVersionNoDataException then
3611 rollback;
3612 if(startDateCursor2%isOpen) then
3613 close startDateCursor2;
3614 end if;
3615 errorCode := -20001;
3616 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3617 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3618 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3619 routineNameIn => 'removeAction',
3620 exceptionNumberIn => errorCode,
3621 exceptionStringIn => errorMessage);
3622 raise_application_error(errorCode,
3623 errorMessage);
3624 when inUseException then
3625 rollback;
3626 if(startDateCursor2%isOpen) then
3627 close startDateCursor2;
3628 end if;
3629 errorCode := -20001;
3630 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3631 messageNameIn => 'AME_400147_ACT_APR_IN_USE');
3632 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3633 routineNameIn => 'removeAction',
3634 exceptionNumberIn => errorCode,
3638 when others then
3635 exceptionStringIn => errorMessage);
3636 raise_application_error(errorCode,
3637 errorMessage);
3639 rollback;
3640 if(startDateCursor2%isOpen) then
3641 close startDateCursor2;
3642 end if;
3643 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3644 routineNameIn => 'removeAction',
3645 exceptionNumberIn => sqlcode,
3646 exceptionStringIn => '(action type ID ' ||
3647 actionTypeIdIn||
3648 ') ' ||
3649 sqlerrm);
3650 raise;
3651 end removeAction;
3652 procedure removeActionTypeUsage(actionTypeIdIn in integer,
3653 ruleTypeIn in integer,
3654 finalizeIn in boolean default false,
3655 processingDateIn in date default null) as
3656 currentUserId integer;
3657 processingDate date;
3658 begin
3659 if processingDateIn is null then
3660 processingDate := sysdate;
3661 else
3662 processingDate := processingDateIn;
3663 end if;
3664 currentUserId := ame_util.getCurrentUserId;
3665 update ame_action_type_usages
3666 set
3667 last_updated_by = currentUserId,
3668 last_update_date = processingDate,
3669 last_update_login = currentUserId,
3670 end_date = processingDate
3671 where
3672 action_type_id = actionTypeIdIn and
3673 rule_type = ruleTypeIn and
3674 processingDate between start_date and
3675 nvl(end_date - ame_util.oneSecond, processingDate) ;
3676 if(finalizeIn) then
3677 commit;
3678 end if;
3679 exception
3680 when others then
3681 rollback;
3682 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3683 routineNameIn => 'removeActionTypeUsage',
3684 exceptionNumberIn => sqlcode,
3685 exceptionStringIn => '(action type ID ' ||
3686 actionTypeIdIn||
3687 ') ' ||
3688 sqlerrm);
3689 raise;
3690 end removeActionTypeUsage;
3691 procedure removeActionTypeUsages(actionTypeIdIn in integer,
3692 finalizeIn in boolean default false,
3693 processingDateIn in date default null) as
3694 cursor getRuleTypesCur(actionTypeIdIn in integer) is
3695 select rule_type
3696 from ame_action_type_usages
3697 where
3698 action_type_id = actionTypeIdIn and
3699 sysdate between start_date and
3700 nvl(end_date - ame_util.oneSecond, sysdate)
3701 order by 1;
3702 processingDate date;
3703 begin
3704 processingDate := sysdate;
3705 for getRuleTypesRec in getRuleTypesCur(actionTypeIdIn) loop
3706 removeActionTypeUsage(actionTypeIdIn => actionTypeIdIn,
3707 ruleTypeIn => getRuleTypesRec.rule_type,
3708 finalizeIn => finalizeIn,
3709 processingDateIn => processingDate);
3710 end loop;
3711 exception
3712 when others then
3713 rollback;
3714 ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
3715 routineNameIn => 'removeActionTypeUsages',
3716 exceptionNumberIn => sqlcode,
3717 exceptionStringIn => '(action type ID ' ||
3718 actionTypeIdIn||
3719 ') ' ||
3720 sqlerrm);
3721 raise;
3722 end removeActionTypeUsages;
3723 end AME_action_pkg;