[Home] [Help]
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);
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||
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;
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||
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;
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||
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;
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||
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;
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||
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;
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||
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);
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
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;
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;
180: end getActionTypeMaxOrderNumber;
181: function getActionTypeName(actionTypeIdIn in integer) return varchar2 as
182: name ame_action_types_vl.user_action_type_name%type;
183: begin
184: if(actionTypeIdIn = ame_util.nullInsertionActionTypeId) then
185: return('no action type');
186: end if;
187: select user_action_type_name
188: into name
189: from ame_action_types_vl
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;
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||
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
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;
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||
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;
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||
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;
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||
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;
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 ' ||
304: actionTypeIdIn||
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);
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
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
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'));
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
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'));
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
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'));
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
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'));
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
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'));
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
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'));
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
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);
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;
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
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;
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;
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
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;
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;
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
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;
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;
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
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
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,
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||
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
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
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,
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 ' ||
536: actionIdIn||
539: raise;
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;
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 */
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
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,
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;
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 */
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||
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
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;
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;
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;
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||
652: from ame_actions
653: where
654: action_id = actionIdIn and
655: sysdate between start_date and
656: nvl(end_date - ame_util.oneSecond, sysdate) ;
657: return(parameter);
658: exception
659: when others then
660: rollback;
657: return(parameter);
658: exception
659: when others then
660: rollback;
661: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
662: routineNameIn => 'getParameter',
663: exceptionNumberIn => sqlcode,
664: exceptionStringIn => '(action ID ' ||
665: actionIdIn||
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;
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||
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
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;
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||
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
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;
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;
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
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;
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;
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;
773: return(votingRegime);
774: exception
775: when others then
776: rollback;
777: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
778: routineNameIn => 'getVotingRegime',
779: exceptionNumberIn => sqlcode,
780: exceptionStringIn => sqlerrm);
781: raise;
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
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)));
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);
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);
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||
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);
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);
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||
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);
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||
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;
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'));
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;
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
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;
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
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;
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;
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;
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;
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;
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
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;
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;
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,
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,
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,
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,
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',
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,
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,
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',
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,
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,
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',
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,
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,
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,
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,
1116: when descriptionQueryException then
1117: rollback;
1118: errorCode := -20001;
1119: errorMessage :=
1120: ame_util.getMessage(applicationShortNameIn => 'PER',
1121: messageNameIn => 'AME_400372_ACT DYNAMIC_DESC2');
1122: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1123: routineNameIn => 'new',
1124: exceptionNumberIn => errorCode,
1118: errorCode := -20001;
1119: errorMessage :=
1120: ame_util.getMessage(applicationShortNameIn => 'PER',
1121: messageNameIn => 'AME_400372_ACT DYNAMIC_DESC2');
1122: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1123: routineNameIn => 'new',
1124: exceptionNumberIn => errorCode,
1125: exceptionStringIn => errorMessage);
1126: raise_application_error(errorCode,
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,
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,
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',
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,
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||
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;
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;
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;
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;
1234: end if;
1235: select count(*)
1236: into actionCount
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',
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;
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;
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
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;
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;
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,
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;
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,
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,
1350: exceptionStringIn => errorMessage);
1351: raise_application_error(errorCode,
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,
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,
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,
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,
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,
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,
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,
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',
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,
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,
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',
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,
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,
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,
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,
1450: errorMessage);
1451: return(null);
1452: when others then
1453: rollback;
1454: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1455: routineNameIn => 'newAction',
1456: exceptionNumberIn => sqlcode,
1457: exceptionStringIn => '(action type ID ' ||
1458: actionTypeIdIn||
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);
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;
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;
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||
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
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;
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;
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;
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;
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;
1564: tempIndex integer;
1565: tempIndex2 integer;
1566: begin
1567: actionTypeSeeded := isSeeded(actionTypeIdIn => actionTypeIdIn);
1568: currentUserId := ame_util.getCurrentUserId;
1569: if(not actionTypeSeeded) then
1570: removeActionTypeUsages(actionTypeIdIn => actionTypeIdIn,
1571: finalizeIn => false,
1572: processingDateIn => processingDateIn);
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,
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;
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;
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,
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,
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,
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,
1672: rollback;
1673: if(startDateCursor%isOpen) then
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||
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
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;
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;
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;
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
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
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
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,
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,
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,
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,
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,
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,
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,
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||
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
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
1900: application_id = applicationIdIn and
1901: sysdate between start_date and
1902: nvl(end_date - ame_util.oneSecond, sysdate)
1903: for update;
1904: currentUserId integer;
1905: description ame_approval_groups.description%type;
1906: dynamicDescription ame_action_types.dynamic_description%type;
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;
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
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;
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;
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,
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,
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,
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',
2013: exceptionNumberIn => errorCode,
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',
2013: exceptionNumberIn => errorCode,
2014: exceptionStringIn => errorMessage);
2015: raise_application_error(errorCode,
2016: errorMessage);
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,
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,
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||
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;
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);
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;
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;
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
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);
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);
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
2110: end if;
2111: else
2112: newOrderNumber := orderNumberIn;
2113: end if;
2114: if(orderNumberUniqueIn = ame_util.yes) then
2115: incrementActionTypeOrdNumbers(applicationIdIn => applicationIdIn,
2116: actionTypeIdIn => actionTypeIdIn,
2117: orderNumberIn => newOrderNumber);
2118: end if;
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,
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,
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,
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||
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
2224: sysdate between ame_action_type_config.start_date 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;
2223: ame_action_type_usages.rule_type = ruleTypeIn and
2224: sysdate between ame_action_type_config.start_date 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;
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;
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;
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
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);
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,
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,
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;
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,
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;
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;
2330: end if;
2331: exception
2332: when others then
2333: rollback;
2334: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2335: routineNameIn => 'getActions',
2336: exceptionNumberIn => sqlcode,
2337: exceptionStringIn => '(action type ID ' ||
2338: actionTypeIdIn||
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,
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,
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;
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);
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||
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,
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,
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
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
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
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,
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,
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;
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;
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
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,
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;
2438: actionDescriptionsOut(tempIndex) using
2439: in tempAction.parameter;
2440: end if;
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 */
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||
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;
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;
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;
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,
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,
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,
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,
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;
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
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
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,
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;
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 */
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;
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;
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
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
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
2546: name
2547: from ame_action_types
2548: where
2549: sysdate between start_date and
2550: nvl(end_date - ame_util.oneSecond, sysdate)
2551: order by name;
2552: tempIndex integer;
2553: begin
2554: 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;
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
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,
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
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;
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;
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,
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,
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
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,
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,
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,
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
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)
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,
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;
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;
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;
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;
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,
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
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,
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,
2656: description
2656: description
2657: from ame_action_types
2658: where
2659: sysdate between start_date and
2660: nvl(end_date - ame_util.oneSecond, sysdate)
2661: order by description;
2662: tempIndex integer;
2663: begin
2664: 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;
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,
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
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
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;
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||
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
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
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
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
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;
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,
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,
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,
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
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;
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
2765: close getApproverTypesCursor;
2766: exception
2767: when others then
2768: rollback;
2769: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
2770: routineNameIn => 'getAllowedApproverTypes',
2771: exceptionNumberIn => sqlcode,
2772: exceptionStringIn => '(action type ID ' ||
2773: actionTypeIdIn||
2772: exceptionStringIn => '(action type ID ' ||
2773: actionTypeIdIn||
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,
2773: actionTypeIdIn||
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
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
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;
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;
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;
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');
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');
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');
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');
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');
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');
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
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
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;
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',
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;
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,
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,
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
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
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
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
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
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
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
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
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);
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;
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;
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;
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;
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;
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;
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;
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;
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(*)
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
2867: ame_item_classes.start_date and
2868: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2869: sysdate
2864: ame_item_class_usages.application_id = applicationIdIn and
2865: ame_item_classes.name = ame_util.lineItemItemClassName and
2866: sysdate between
2867: ame_item_classes.start_date and
2868: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2869: sysdate
2870: between ame_item_class_usages.start_date and
2871: nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate);
2872: allowProductions := ame_util.getConfigVar(variableNameIn => ame_util.productionConfigVar,
2867: ame_item_classes.start_date and
2868: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2869: sysdate
2870: between ame_item_class_usages.start_date and
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
2868: nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2869: sysdate
2870: between ame_item_class_usages.start_date and
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.
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;
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;
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,
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,
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);
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;
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;
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
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;
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,
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,
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
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
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,
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,
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
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
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,
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
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
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
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
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
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
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
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
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
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
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);
2965: ame_action_types.start_date and
2966: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2967: sysdate
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);
2971: actionTypeIds ame_util.stringList;
2972: actionTypeIds2 ame_util.stringList;
2973: actionTypeDescriptions ame_util.stringList;
2967: sysdate
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);
2971: actionTypeIds ame_util.stringList;
2972: actionTypeIds2 ame_util.stringList;
2973: actionTypeDescriptions ame_util.stringList;
2974: actionTypeDescriptions2 ame_util.stringList;
2975: allowProductions ame_util.stringType;
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);
2971: actionTypeIds ame_util.stringList;
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;
2969: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2970: order by upper(description);
2971: actionTypeIds ame_util.stringList;
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;
2970: order by upper(description);
2971: actionTypeIds ame_util.stringList;
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;
2971: actionTypeIds ame_util.stringList;
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
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(*)
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
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,
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
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.
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;
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;
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,
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,
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);
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;
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;
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
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;
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,
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,
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
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;
3070: actionTypeIds ame_util.idList;
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
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;
3070: actionTypeIds ame_util.idList;
3071: chainOrderingMode ame_util.charType;
3072: currentUserId integer;
3066: nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
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;
3070: actionTypeIds ame_util.idList;
3071: chainOrderingMode ame_util.charType;
3072: currentUserId integer;
3073: orderNumbers ame_util.idList;
3074: processingDate date;
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;
3070: actionTypeIds ame_util.idList;
3071: chainOrderingMode ame_util.charType;
3072: currentUserId integer;
3073: orderNumbers ame_util.idList;
3074: processingDate date;
3075: ruleType integer;
3069: order by order_number;
3070: actionTypeIds ame_util.idList;
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
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);
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,
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,
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;
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;
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;
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;
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 :=
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);
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
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
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)
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;
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,
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);
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;
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
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;
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
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
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||
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
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;
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;
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;
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;
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 */
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,
3414: last_update_date = processingDate,
3416: end_date = processingDate
3417: where
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,
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,
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,
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;
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;
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,
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,
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,
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,
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,
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||
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
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;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%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;
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;
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;
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);
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,
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;
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,
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,
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,
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,
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,
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,
3635: exceptionStringIn => errorMessage);
3636: raise_application_error(errorCode,
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||
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,
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
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||
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;
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||