[Home] [Help]
PACKAGE BODY: APPS.AME_FINAL_ONLY_HANDLER
Source
1 package body ame_final_only_handler as
2 /* $Header: ameefoha.pkb 120.3 2007/12/20 20:02:23 prasashe noship $ */
3 /* package variables */
4 approverCategories ame_util.charList;
5 finalApproverFound boolean;
6 parametersCount integer;
7 parameterNumbers ame_util.idList;
8 parameters ame_util.stringList;
9 parameterSigns ame_util.charList;
10 ruleIds ame_util.idList;
11 ruleSatisfiedYN ame_util.charList;
12 threshholdJobLevel integer;
13 topDogFound boolean;
14 topDogPersonId integer;
15 /* forward declarations */
16 /*
17 getCatSourceAndAuthority does not account for the ALLOW_REQUESTOR_APPROVAL attribute.
18 The handler procedure does that.
19 */
20 procedure getCatSourceAndAuthority(personIdIn in integer,
21 jobLevelIn in integer,
22 supervisorIdIn in integer,
23 categoryOut out nocopy varchar2,
24 sourceOut out nocopy varchar2,
25 hasFinalAuthorityYNOut out nocopy varchar2,
26 supervisorJobLevelOut out nocopy integer,
27 nextSupervisorIdOut out nocopy integer);
28 /*
29 parseAndSortRules populates the parameterNumbers and parameterSigns tables in
30 ascending lexicographic order, first by numerical order, then with '+' dominating '-'.
31 Note that it does not sort the parameters proper.
32 */
33 procedure parseAndSortRules;
34 /* procedures */
35 procedure getCatSourceAndAuthority(personIdIn in integer,
36 jobLevelIn in integer,
37 supervisorIdIn in integer,
38 categoryOut out nocopy varchar2,
39 sourceOut out nocopy varchar2,
40 hasFinalAuthorityYNOut out nocopy varchar2,
41 supervisorJobLevelOut out nocopy integer,
42 nextSupervisorIdOut out nocopy integer) as
43 category ame_util.charType;
44 errorCode integer;
45 errorMessage ame_util.longestStringType;
46 hasFinalAuthorityYN ame_util.charType;
47 noSupervisorException exception;
48 personDisplayName ame_util.longStringType;
49 source ame_util.longStringType;
50 supervisorJobLevel integer;
51 tempRuleRequiresApprover boolean;
52 tempRuleSatisfied boolean;
53 begin
54 /* Initialize the two output arguments that might not otherwise get set. */
55 supervisorJobLevelOut := null;
56 nextSupervisorIdOut := null;
57 /*
58 1. An approver satisfies a rule in any of three cases:
59 A. The rule's parameter number does not exceed the approver's job level.
60 B. The rule's parameter sign is '-', and the job level of the approver's
61 supervisor exceeds the rule's parameter number.
62 C. The approver is the top dog.
63 2. An approver has final authority if the approver satisfies all the rules.
64 (The handler procedure proper takes care of adding subsequent approvers at
65 the same job level, if the relevant mandatory attribute so requires.)
66 3. The source value is a comma-delimited list of the IDs of the rules that
67 require an approver. This procedure builds up the source value according
68 to the following logic:
69 A. If a rule has not yet been satisfied, the rule requires the input
70 approver.
71 B. Otherwise, the rule requires the input approver only if the approver
72 does <<not>> satisfy the rule. (This would happen in the perverse case
73 that an approver satisfies a rule, but their supervisor has a lower
74 job level that does not satisfy the rule.)
75 4. An approver's category is ame_util.approvalApproverCategory if any of the
76 rule usages requiring the approver is of that category; otherwise the
77 approver's category is ame_util.fyiApproverCategory.
78 */
79 category := ame_util.fyiApproverCategory;
80 hasFinalAuthorityYN := ame_util.booleanTrue;
81 for i in 1 .. parametersCount loop
82 /* Determine whether the approver satisfies the current rule. */
83 if(personIdIn = topDogPersonId) then
84 tempRuleSatisfied := true;
85 topDogFound := true;
86 else
87 topDogFound := false;
88 tempRuleSatisfied := false;
89 if(jobLevelIn >= parameterNumbers(i)) then
90 tempRuleSatisfied := true;
91 elsif(parameterSigns(i) = '-') then
92 if supervisorIdIn is null then
93 supervisorJobLevel := 0;
94 else
95 if(supervisorJobLevel is null) then
96 if(supervisorIdIn is null) then
97 raise noSupervisorException;
98 end if;
99 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => supervisorIdIn,
100 jobLevelOut => supervisorJobLevel,
101 supervisorIdOut => nextSupervisorIdOut);
102 supervisorJobLevelOut := supervisorJobLevel;
103 end if;
104 if(supervisorJobLevel > parameterNumbers(i)) then
105 tempRuleSatisfied := true;
106 end if;
107 end if;
108 end if;
109 end if;
110 /* Update hasFinalAuthorityYN as needed. */
111 if(not tempRuleSatisfied and
112 hasFinalAuthorityYN = ame_util.booleanTrue) then
113 hasFinalAuthorityYN := ame_util.booleanFalse;
114 end if;
115 /* Determine whether the current rule requires the approver. */
116 tempRuleRequiresApprover := false;
117 if(ruleSatisfiedYN(i) = ame_util.booleanTrue) then
118 if(not tempRuleSatisfied) then
119 tempRuleRequiresApprover := true;
120 end if;
121 else
122 tempRuleRequiresApprover := true;
123 if(tempRuleSatisfied) then
124 ruleSatisfiedYN(i) := ame_util.booleanTrue;
125 end if;
126 end if;
127 if(tempRuleRequiresApprover) then
128 /* Update source. */
129 ame_util.appendRuleIdToSource(ruleIdIn => ruleIds(i),
130 sourceInOut => source);
131 /* Update category as needed. */
132 if(category = ame_util.fyiApproverCategory and
133 approverCategories(i) = ame_util.approvalApproverCategory) then
134 category := ame_util.approvalApproverCategory;
135 end if;
136 end if;
137 end loop;
138 categoryOut := category;
139 hasFinalAuthorityYNOut := hasFinalAuthorityYN;
140 sourceOut := source;
141 exception
142 when noSupervisorException then
143 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
144 origSystemIn => ame_util.perOrigSystem,
145 origSystemIdIn => personIdIn );
146 errorCode := -20001;
147 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
148 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
149 tokenNameOneIn => 'FIRST_NAME',
150 tokenValueOneIn => personDisplayName,
151 tokenNameTwoIn => 'LAST_NAME',
152 tokenValueTwoIn => null ,
153 tokenNameThreeIn => 'OTHER_NAME',
154 tokenValueThreeIn => null );
155 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
156 routineNameIn => 'getCatSourceAndAuthority',
157 exceptionNumberIn => errorCode,
158 exceptionStringIn => errorMessage);
159 raise_application_error(errorCode,
160 errorMessage);
161 when others then
162 categoryOut := null;
163 hasFinalAuthorityYNOut := null;
164 sourceOut := null;
165 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
166 routineNameIn => 'getCatSourceAndAuthority',
167 exceptionNumberIn => sqlcode,
168 exceptionStringIn => sqlerrm);
169 raise;
170 end getCatSourceAndAuthority;
171 procedure handler as
172 COAInsertee ame_util.approverRecord2;
173 errorCode integer;
174 errorMessage ame_util.longestStringType;
175 finalAuthorityApproverCategory ame_util.charType;
176 finalAuthorityFound boolean;
177 finalAuthoritySource ame_util.longStringType;
178 firstApproverSource ame_util.longStringType;
179 includeAllJobLevelApprovers boolean;
180 noSupervisorException exception;
181 nullFirstIdException exception;
182 personDisplayName ame_util.longStringType;
183 prevApprover ame_util.approverRecord2;
184 requestorId integer;
185 startingPointId integer;
186 tempApprover ame_util.approverRecord2;
187 tempHasFinalAuthorityYN ame_util.charType;
188 tempJobLevel integer;
189 tempMemberOrderNumber integer;
190 tempOldJobLevel integer;
191 tempSupervisorId integer;
192 tempSupervisorJobLevel integer;
193 tempNextSupervisorId integer;
194 topDogRequestorException exception;
195 votingRegimeType ame_util.stringType;
196 firstAuthInsExists boolean := false;
197 coaInsAuthForward boolean := false;
198 begin
199 finalAuthorityApproverCategory := null;
200 finalAuthoritySource := null;
201 includeAllJobLevelApprovers :=
202 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
203 ame_util.booleanAttributeTrue;
204 /* Populate some of the package variables. */
205 topDogPersonId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topSupPersonIdAttribute));
206 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
207 tempApprover.orig_system := ame_util.perOrigSystem;
208 tempApprover.authority := ame_util.authorityApprover;
209 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
210 tempApprover.item_class := ame_engine.getHandlerItemClassName;
211 tempApprover.item_id := ame_engine.getHandlerItemId;
212 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
213 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
214 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
215 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
216 tempApprover.group_or_chain_order_number := 1;
217 tempApprover.group_or_chain_id := 1;
218 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
219 /*
220 The engine only calls a handler if a rule requiring it exists, so we can assume that
221 the package variables that ame_engine.getHandlerRules initializes are nonempty.
222 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
223 are harmless here.)
224 */
225 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
226 approverCategoriesOut => approverCategories,
227 parametersOut => parameters);
228 /* Check for COA 'firstAuthority' insertions */
229 ame_engine.getHandlerCOAFirstApprover(itemClassIn => tempApprover.item_class,
230 itemIdIn => tempApprover.item_id,
231 actionTypeIdIn => tempApprover.action_type_id,
232 groupOrChainIdIn => tempApprover.group_or_chain_id,
233 nameOut => COAInsertee.name,
234 origSystemOut => COAInsertee.orig_system,
235 origSystemIdOut => COAInsertee.orig_system_id,
236 displayNameOut => COAInsertee.display_name,
237 sourceOut => COAInsertee.source);
238 if COAInsertee.name is null then
239 /* Fetch some of the required attributes. */
240 startingPointId :=
241 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.jobLevelStartingPointAttribute));
242 if(startingPointId is null) then
243 requestorId :=
244 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.transactionRequestorAttribute));
245 if (requestorId is null) then
246 raise nullFirstIdException;
247 end if;
248 tempApprover.orig_system_id := requestorId;
249 else
250 tempApprover.orig_system_id := startingPointId;
251 end if;
252 tempApprover.api_insertion := ame_util.oamGenerated;
253 else
254 tempApprover.name := COAInsertee.name;
255 tempApprover.orig_system := COAInsertee.orig_system;
256 tempApprover.orig_system_id := COAInsertee.orig_system_id;
257 tempApprover.display_name := COAInsertee.display_name;
258 firstApproverSource := COAInsertee.source;
259 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
260 firstAuthInsExists := true;
261 end if;
262 /* The threshhold Job level is the job level of this tempApprover */
263 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
264 jobLevelOut => tempJobLevel,
265 supervisorIdOut => tempSupervisorId);
266 threshholdJobLevel := tempJobLevel;
267 parametersCount := parameters.count;
268 parseAndSortRules;
269 for i in 1 .. ruleIds.count loop
270 ruleSatisfiedYN(i) := ame_util.booleanFalse;
271 end loop;
272 /*
273 Check whether self-approval is allowed. If so, check whether there is a non-default
274 starting point or a COA firstAuthority approver. If not, check whether the requestor has enough
275 authority to self-approve. If so, insert the approver as the only approver, with a status of approved,
276 and return.
277 This can not be done earlier as we need the parsed job levels
278 */
279 if(COAInsertee.name is null and
280 startingPointId is null )
281 then
282 if (ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
283 = ame_util.booleanAttributeTrue)
284 then
285 getCatSourceAndAuthority(personIdIn => requestorId,
286 jobLevelIn => tempJobLevel,
287 supervisorIdIn => tempSupervisorId,
288 categoryOut => tempApprover.approver_category,
289 sourceOut => tempApprover.source,
290 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
291 supervisorJobLevelOut => tempSupervisorJobLevel,
292 nextSupervisorIdOut => tempNextSupervisorId);
293 if(tempHasFinalAuthorityYN = ame_util.booleanTrue) then
294 tempApprover.api_insertion := ame_util.oamGenerated;
295 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
296 origSystemIdIn => requestorId,
297 nameOut => tempApprover.name,
298 displayNameOut => tempApprover.display_name);
299 tempApprover.orig_system_id := requestorId;
300 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
301 itemClassIn => tempApprover.item_class,
302 itemIdIn => tempApprover.item_id,
303 actionTypeIdIn => tempApprover.action_type_id,
304 groupOrChainIdIn => tempApprover.group_or_chain_id);
305 tempApprover.member_order_number := 1;
306 tempApprover.approval_status := ame_util.approvedStatus;
307 finalApproverFound := true;
308 ame_engine.addApprover(approverIn => tempApprover);
309 return;
310 end if;
311 end if;
312 /* As self approval is not an option. And only requestorId is defined, the chain
313 should start from the requestor's manager Check to make sure tempSupervisorId
314 is not null, else raise noSupervisorException */
315 if tempSupervisorId is null then
316 if tempApprover.orig_system_id = topDogPersonId then
317 raise topDogRequestorException;
318 end if;
319 raise noSupervisorException;
320 else
321 tempApprover.orig_system_id := tempSupervisorId ;
322 end if;
323 if tempSupervisorJobLevel is null then
324 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
325 jobLevelOut => tempJobLevel,
326 supervisorIdOut => tempSupervisorId);
327 else
328 tempJobLevel := tempSupervisorJobLevel;
329 end if;
330 end if;
331 prevApprover := ame_util.emptyApproverRecord2;
332 /* Build the chain. */
333 finalAuthorityFound := false;
334 tempMemberOrderNumber := 0; /* pre-increment */
335 loop
336 finalApproverFound := false;
337 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
338 jobLevelIn => tempJobLevel,
339 supervisorIdIn => tempSupervisorId,
340 categoryOut => tempApprover.approver_category,
341 sourceOut => tempApprover.source,
342 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
343 supervisorJobLevelOut => tempSupervisorJobLevel,
344 nextSupervisorIdOut => tempNextSupervisorId);
345 if(not finalAuthorityFound and
346 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
347 finalAuthorityFound := true;
348 finalAuthorityApproverCategory := tempApprover.approver_category;
349 finalAuthoritySource := tempApprover.source;
350 tempOldJobLevel := tempJobLevel;
351 end if;
352 if (tempApprover.source is null and
353 finalAuthoritySource is not null ) then
354 tempApprover.approver_category := finalAuthorityApproverCategory;
355 tempApprover.source := finalAuthoritySource;
356 end if;
357 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
358 origSystemIdIn => tempApprover.orig_system_id,
359 nameOut => tempApprover.name,
360 displayNameOut => tempApprover.display_name);
361 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
362 itemClassIn => tempApprover.item_class,
363 itemIdIn => tempApprover.item_id,
364 actionTypeIdIn => tempApprover.action_type_id,
365 groupOrChainIdIn => tempApprover.group_or_chain_id);
366 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
367 /* The engine will set tempApprover.approver_order_number; leave it null here. */
368 /* Decide whether to end the chain. */
369 if(topDogFound or
370 (finalAuthorityFound and
371 not includeAllJobLevelApprovers)) then
372 /*
373 The approver is the last approver in the chain. He should get inserted into the
374 approver List */
375 tempMemberOrderNumber := tempMemberOrderNumber + 1;
376 if(votingRegimeType = ame_util.serializedVoting) then
377 tempApprover.member_order_number := tempMemberOrderNumber;
378 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
379 tempApprover.member_order_number := 1;
380 end if;
381 finalApproverFound := true;
382 /* reassign the value of source in case approver was a firstAuthority insertee */
383 if firstApproverSource is not null then
384 tempApprover.source := firstApproverSource;
385 firstApproverSource := null;
386 end if;
387 ame_engine.addApprover(approverIn => tempApprover);
388 exit;
389 end if;
390 /*
391 At this point finalAuthorityFound implies includeAllJobLevelApprovers, so the following if
392 doesn't need to check includeAllJobLevelApprovers. But it's implicit in the if statement.
393 */
394 if(finalAuthorityFound and
395 tempOldJobLevel <> tempJobLevel) then
396 /*
397 The approver is the last approver in the chain. He should get inserted into the
398 approver List */
399 tempMemberOrderNumber := tempMemberOrderNumber + 1;
400 if(votingRegimeType = ame_util.serializedVoting) then
401 prevApprover.member_order_number := tempMemberOrderNumber;
402 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
403 prevApprover.member_order_number := 1;
404 end if;
405 finalApproverFound := true;
406 /* reassign the value of source in case approver was a firstAuthority insertee */
407 if firstApproverSource is not null then
408 prevApprover.source := firstApproverSource;
409 firstApproverSource := null;
410 end if;
411 ame_engine.addApprover(approverIn => prevApprover);
412 exit;
413 end if;
414 tempOldJobLevel := tempJobLevel;
415 /* The approver is not the last approver in the chain. Must check the approver
416 category with that of prev approver to see if category change has occurred If yes
417 insert the approver in the chain */
418 if prevApprover.approver_category is not null and
419 tempApprover.approver_category <> prevApprover.approver_category then
420 tempMemberOrderNumber := tempMemberOrderNumber + 1;
421 if(votingRegimeType = ame_util.serializedVoting) then
422 prevApprover.member_order_number := tempMemberOrderNumber;
423 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
424 prevApprover.member_order_number := 1;
425 end if;
426 finalApproverFound := true;
427 /* reassign the value of source in case approver was a firstAuthority insertee */
428 if firstApproverSource is not null then
429 prevApprover.source := firstApproverSource;
430 firstApproverSource := null;
431 end if;
432 ame_engine.addApprover(approverIn => prevApprover);
433 /* check to see if there is a COA insertion after this approver. If a COA insertion is
434 found, keep checking till no more COA insertions. The check for final authority will need to be
435 done again.
436 */
437 loop
438 /* Initialize COAInsertee approverRecord2 */
439 COAInsertee := ame_util.emptyApproverRecord2;
440 /* Check if there are any COAInsertions */
441 ame_engine.getHandlerCOAInsertion(nameIn => prevApprover.name,
442 itemClassIn => prevApprover.item_class,
443 itemIdIn => prevApprover.item_id,
444 actionTypeIdIn => prevApprover.action_type_id,
445 groupOrChainIdIn => prevApprover.group_or_chain_id,
446 occurrenceIn => prevApprover.occurrence,
447 approvalStatusIn => prevApprover.approval_status,
448 nameOut => COAInsertee.name,
449 origSystemOut => COAInsertee.orig_system,
450 origSystemIdOut => COAInsertee.orig_system_id,
451 displayNameOut => COAInsertee.display_name,
452 sourceOut => COAInsertee.source);
453 if COAInsertee.name is null then
454 exit;
455 else
456 prevApprover.name := COAInsertee.name;
457 prevApprover.orig_system := COAInsertee.orig_system;
458 prevApprover.orig_system_id := COAInsertee.orig_system_id;
459 prevApprover.display_name := COAInsertee.display_name;
460 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => prevApprover.orig_system_id,
461 jobLevelOut => tempJobLevel,
462 supervisorIdOut => tempSupervisorId);
463 getCatSourceAndAuthority(personIdIn => prevApprover.orig_system_id,
464 jobLevelIn => tempJobLevel,
465 supervisorIdIn => tempSupervisorId,
466 categoryOut => prevApprover.approver_category,
467 sourceOut => prevApprover.source,
468 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
469 supervisorJobLevelOut => tempSupervisorJobLevel,
470 nextSupervisorIdOut => tempNextSupervisorId);
471 prevApprover.source := COAInsertee.source;
472 prevApprover.api_insertion := ame_util.apiAuthorityInsertion;
473 tempMemberOrderNumber := tempMemberOrderNumber + 1;
474 if(votingRegimeType = ame_util.serializedVoting) then
475 prevApprover.member_order_number := tempMemberOrderNumber;
476 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
477 prevApprover.member_order_number := 1;
478 end if;
479 prevApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => prevApprover.name,
480 itemClassIn => prevApprover.item_class,
481 itemIdIn => prevApprover.item_id,
482 actionTypeIdIn => prevApprover.action_type_id,
483 groupOrChainIdIn => prevApprover.group_or_chain_id);
484 prevApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => prevApprover);
485 /* If approver has a status of ame_util.approve or ame_util.approveAndForwardStatus or
486 ame_util.nullStatus check to see if approver could have final authority */
487 if ((prevApprover.approval_status is null) or
488 (prevApprover.approval_status in
489 (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
490 ame_util.repeatedStatus, ame_util.suppressedStatus,
491 ame_util.beatByFirstResponderStatus, ame_util.nullStatus)) or
492 (prevApprover.approver_category = ame_util.approvalApproverCategory and
493 prevApprover.approval_status = ame_util.notifiedStatus) )
494 then
495 if(not finalAuthorityFound and
496 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
497 finalAuthorityFound := true;
498 end if;
499 end if;
500 ame_engine.addApprover(approverIn => prevApprover);
501 coaInsAuthForward := true;
502 end if;
503 end loop;
504 end if;
505 ame_util.copyApproverRecord2(approverRecord2In => tempApprover,
506 approverRecord2Out => prevApprover);
507 /* Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
508 if tempSupervisorId is null then
509 raise noSupervisorException;
510 else
511 tempApprover.orig_system_id := tempSupervisorId;
512 end if;
513 tempApprover.api_insertion := ame_util.oamGenerated;
514 if(tempSupervisorJobLevel is null) then
515 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
516 jobLevelOut => tempJobLevel,
517 supervisorIdOut => tempSupervisorId);
518 else
519 tempJobLevel := tempSupervisorJobLevel;
520 tempSupervisorId := tempNextSupervisorId;
521 end if;
522 if firstAuthInsExists then
523 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
524 end if;
525 if coaInsAuthForward then
526 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
527 end if;
528 end loop;
529 /* check to see if there is a COA insertion after the final approver. If a COA insertion is
530 found, keep checking till no more COA insertions. The check for final authority need not be
531 done again.
532 */
533 loop
534 /* Initialize COAInsertee approverRecord2 */
535 COAInsertee := ame_util.emptyApproverRecord2;
536 /* Check if there are any COAInsertions */
537 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
538 itemClassIn => tempApprover.item_class,
539 itemIdIn => tempApprover.item_id,
540 actionTypeIdIn => tempApprover.action_type_id,
541 groupOrChainIdIn => tempApprover.group_or_chain_id,
542 occurrenceIn => tempApprover.occurrence,
543 approvalStatusIn => tempApprover.approval_status,
544 nameOut => COAInsertee.name,
545 origSystemOut => COAInsertee.orig_system,
546 origSystemIdOut => COAInsertee.orig_system_id,
547 displayNameOut => COAInsertee.display_name,
548 sourceOut => COAInsertee.source);
549 if COAInsertee.name is null then
550 exit;
551 else
552 tempApprover.name := COAInsertee.name;
553 tempApprover.orig_system := COAInsertee.orig_system;
554 tempApprover.orig_system_id := COAInsertee.orig_system_id;
555 tempApprover.display_name := COAInsertee.display_name;
556 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
557 jobLevelOut => tempJobLevel,
558 supervisorIdOut => tempSupervisorId);
559 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
560 jobLevelIn => tempJobLevel,
561 supervisorIdIn => tempSupervisorId,
562 categoryOut => tempApprover.approver_category,
563 sourceOut => tempApprover.source,
564 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
565 supervisorJobLevelOut => tempSupervisorJobLevel,
566 nextSupervisorIdOut => tempNextSupervisorId);
567 tempApprover.source := COAInsertee.source;
568 tempApprover.approver_category := ame_util.approvalApproverCategory;
569 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
570 tempMemberOrderNumber := tempMemberOrderNumber + 1;
571 if(votingRegimeType = ame_util.serializedVoting) then
572 tempApprover.member_order_number := tempMemberOrderNumber;
573 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
574 tempApprover.member_order_number := 1;
575 end if;
576 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
577 itemClassIn => tempApprover.item_class,
578 itemIdIn => tempApprover.item_id,
579 actionTypeIdIn => tempApprover.action_type_id,
580 groupOrChainIdIn => tempApprover.group_or_chain_id);
581 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
582 ame_engine.addApprover(approverIn => tempApprover);
583 end if;
584 end loop;
585 exception
586 when noSupervisorException then
587 if tempApprover.display_name is null then
588 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
589 origSystemIn => ame_util.perOrigSystem,
590 origSystemIdIn => tempApprover.orig_system_id );
591 else
592 personDisplayName := tempApprover.display_name;
593 end if;
594 errorCode := -20001;
595 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
596 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
597 tokenNameOneIn => 'FIRST_NAME',
598 tokenValueOneIn => personDisplayName,
599 tokenNameTwoIn => 'LAST_NAME',
600 tokenValueTwoIn => null ,
601 tokenNameThreeIn => 'OTHER_NAME',
602 tokenValueThreeIn => null );
603 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
604 routineNameIn => 'handler',
605 exceptionNumberIn => errorCode,
606 exceptionStringIn => errorMessage);
607 raise_application_error(errorCode,
608 errorMessage);
609 when nullFirstIdException then
610 errorCode := -20001;
611 errorMessage :=
612 ame_util.getMessage(applicationShortNameIn => 'PER',
613 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
614 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
615 routineNameIn => 'handler',
616 exceptionNumberIn => errorCode,
617 exceptionStringIn => errorMessage);
618 raise_application_error(errorCode,
619 errorMessage);
620 when topDogRequestorException then
621 errorCode := -20001;
622 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
623 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
624 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
625 routineNameIn => 'handler',
626 exceptionNumberIn => errorCode,
627 exceptionStringIn => errorMessage);
628 raise_application_error(errorCode,
629 errorMessage);
630 when others then
631 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
632 routineNameIn => 'handler',
633 exceptionNumberIn => sqlcode,
634 exceptionStringIn => sqlerrm);
635 raise;
636 end handler;
637 procedure parseAndSortRules as
638 badParameterException exception;
639 errorCode integer;
640 errorMessage ame_util.longestStringType;
641 tempCategory ame_util.charType;
642 tempLength integer;
643 tempNumber integer;
644 tempRuleId integer;
645 tempSign ame_util.charType;
646 upperLimit integer;
647 begin
648 /* Parse. */
649 for i in 1 .. parametersCount loop
650 tempLength := lengthb(parameters(i));
651 if(substrb(parameters(i), 1, 1) = 'R') then
652 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) +
653 threshholdJobLevel;
654 parameterSigns(i) := substrb(parameters(i), -1, 1);
655 else
656 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) ;
657 parameterSigns(i) := substrb(parameters(i), -1, 1);
658 end if;
659 if(parameterSigns(i) <> '+' and
660 parameterSigns(i) <> '-') then
661 raise badParameterException;
662 end if;
663 end loop;
664 /* Sort. */
665 for i in 2 .. parametersCount loop
666 upperLimit := i - 1;
667 for j in 1 .. upperLimit loop
668 if(parameterNumbers(i) < parameterNumbers(j) or
669 (parameterNumbers(i) = parameterNumbers(j) and
670 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
671 tempRuleId := ruleIds(j);
672 tempCategory := approverCategories(j);
673 tempNumber := parameterNumbers(j);
674 tempSign := parameterSigns(j);
675 ruleIds(j) := ruleIds(i);
676 approverCategories(j) := approverCategories(i);
677 parameterNumbers(j) := parameterNumbers(i);
678 parameterSigns(j) := parameterSigns(i);
679 ruleIds(i) := tempRuleId;
680 approverCategories(i) := tempCategory;
681 parameterNumbers(i) := tempNumber;
682 parameterSigns(i) := tempSign;
683 end if;
684 end loop;
685 end loop;
686 exception
687 when badParameterException then
688 errorCode := -20001;
689 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
690 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
691 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
692 routineNameIn => 'parseAndSortRules',
693 exceptionNumberIn => errorCode,
694 exceptionStringIn => errorMessage);
695 raise_application_error(errorCode,
696 errorMessage);
697 when others then
698 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
699 routineNameIn => 'parseAndSortRules',
700 exceptionNumberIn => sqlcode,
701 exceptionStringIn => sqlerrm);
702 raise;
703 end parseAndSortRules;
704 end ame_final_only_handler;