[Home] [Help]
PACKAGE BODY: APPS.AME_FINAL_ONLY_HANDLER
Source
1 package body ame_final_only_handler as
2 /* $Header: ameefoha.pkb 120.7 2011/05/17 11:37:45 nchinnam ship $ */
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 := -20206;
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 l_error_code number;
199 begin
200 finalAuthorityApproverCategory := null;
201 errorCode := -20234;
202 finalAuthoritySource := null;
203 includeAllJobLevelApprovers :=
204 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
205 ame_util.booleanAttributeTrue;
206 /* Populate some of the package variables. */
207 topDogPersonId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topSupPersonIdAttribute));
208 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
209 tempApprover.orig_system := ame_util.perOrigSystem;
210 tempApprover.authority := ame_util.authorityApprover;
211 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
212 tempApprover.item_class := ame_engine.getHandlerItemClassName;
213 tempApprover.item_id := ame_engine.getHandlerItemId;
214 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
215 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
216 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
217 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
218 tempApprover.group_or_chain_order_number := 1;
219 tempApprover.group_or_chain_id := 1;
220 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
221 /*
222 The engine only calls a handler if a rule requiring it exists, so we can assume that
223 the package variables that ame_engine.getHandlerRules initializes are nonempty.
224 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
225 are harmless here.)
226 */
227 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
228 approverCategoriesOut => approverCategories,
229 parametersOut => parameters);
230 /* Check for COA 'firstAuthority' insertions */
231 ame_engine.getHandlerCOAFirstApprover(itemClassIn => tempApprover.item_class,
232 itemIdIn => tempApprover.item_id,
233 actionTypeIdIn => tempApprover.action_type_id,
234 groupOrChainIdIn => tempApprover.group_or_chain_id,
235 nameOut => COAInsertee.name,
236 origSystemOut => COAInsertee.orig_system,
237 origSystemIdOut => COAInsertee.orig_system_id,
238 displayNameOut => COAInsertee.display_name,
239 sourceOut => COAInsertee.source);
240 if COAInsertee.name is null then
241 /* Fetch some of the required attributes. */
242 startingPointId :=
243 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.jobLevelStartingPointAttribute));
244 if(startingPointId is null) then
245 requestorId :=
246 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.transactionRequestorAttribute));
247 if (requestorId is null) then
248 raise nullFirstIdException;
249 end if;
250 tempApprover.orig_system_id := requestorId;
251 else
252 tempApprover.orig_system_id := startingPointId;
253 end if;
254 tempApprover.api_insertion := ame_util.oamGenerated;
255 else
256 tempApprover.name := COAInsertee.name;
257 tempApprover.orig_system := COAInsertee.orig_system;
258 tempApprover.orig_system_id := COAInsertee.orig_system_id;
259 tempApprover.display_name := COAInsertee.display_name;
260 firstApproverSource := COAInsertee.source;
261 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
262 firstAuthInsExists := true;
263 end if;
264 /* The threshhold Job level is the job level of this tempApprover */
265 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
266 jobLevelOut => tempJobLevel,
267 supervisorIdOut => tempSupervisorId);
268 threshholdJobLevel := tempJobLevel;
269 parametersCount := parameters.count;
270 parseAndSortRules;
271 for i in 1 .. ruleIds.count loop
272 ruleSatisfiedYN(i) := ame_util.booleanFalse;
273 end loop;
274 /*
275 Check whether self-approval is allowed. If so, check whether there is a non-default
276 starting point or a COA firstAuthority approver. If not, check whether the requestor has enough
277 authority to self-approve. If so, insert the approver as the only approver, with a status of approved,
278 and return.
279 This can not be done earlier as we need the parsed job levels
280 */
281 if(COAInsertee.name is null and
282 startingPointId is null )
283 then
284 if (ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
285 = ame_util.booleanAttributeTrue)
286 then
287 getCatSourceAndAuthority(personIdIn => requestorId,
288 jobLevelIn => tempJobLevel,
289 supervisorIdIn => tempSupervisorId,
290 categoryOut => tempApprover.approver_category,
291 sourceOut => tempApprover.source,
292 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
293 supervisorJobLevelOut => tempSupervisorJobLevel,
294 nextSupervisorIdOut => tempNextSupervisorId);
295 if(tempHasFinalAuthorityYN = ame_util.booleanTrue) then
296 tempApprover.api_insertion := ame_util.oamGenerated;
297 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
298 origSystemIdIn => requestorId,
299 nameOut => tempApprover.name,
300 displayNameOut => tempApprover.display_name);
301 tempApprover.orig_system_id := requestorId;
302 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
303 itemClassIn => tempApprover.item_class,
304 itemIdIn => tempApprover.item_id,
305 actionTypeIdIn => tempApprover.action_type_id,
306 groupOrChainIdIn => tempApprover.group_or_chain_id);
307 tempApprover.member_order_number := 1;
308 tempApprover.approval_status := ame_util.approvedStatus;
309 finalApproverFound := true;
310 ame_engine.addApprover(approverIn => tempApprover);
311 return;
312 end if;
313 end if;
314 /* As self approval is not an option. And only requestorId is defined, the chain
315 should start from the requestor's manager Check to make sure tempSupervisorId
316 is not null, else raise noSupervisorException */
317 if tempSupervisorId is null then
318 if tempApprover.orig_system_id = topDogPersonId then
319 raise topDogRequestorException;
320 end if;
321 raise noSupervisorException;
322 else
323 tempApprover.orig_system_id := tempSupervisorId ;
324 end if;
325 if tempSupervisorJobLevel is null then
326 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
327 jobLevelOut => tempJobLevel,
328 supervisorIdOut => tempSupervisorId);
329 else
330 tempJobLevel := tempSupervisorJobLevel;
331 end if;
332 end if;
333 prevApprover := ame_util.emptyApproverRecord2;
334 /* Build the chain. */
335 errorCode := -20235;
336 finalAuthorityFound := false;
337 tempMemberOrderNumber := 0; /* pre-increment */
338 loop
339 finalApproverFound := false;
340 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
341 jobLevelIn => tempJobLevel,
342 supervisorIdIn => tempSupervisorId,
343 categoryOut => tempApprover.approver_category,
344 sourceOut => tempApprover.source,
345 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
346 supervisorJobLevelOut => tempSupervisorJobLevel,
347 nextSupervisorIdOut => tempNextSupervisorId);
348 if(not finalAuthorityFound and
349 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
350 finalAuthorityFound := true;
351 finalAuthorityApproverCategory := tempApprover.approver_category;
352 finalAuthoritySource := tempApprover.source;
353 tempOldJobLevel := tempJobLevel;
354 end if;
355 if (tempApprover.source is null and
356 finalAuthoritySource is not null ) then
357 tempApprover.approver_category := finalAuthorityApproverCategory;
358 tempApprover.source := finalAuthoritySource;
359 end if;
360 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
361 origSystemIdIn => tempApprover.orig_system_id,
362 nameOut => tempApprover.name,
363 displayNameOut => tempApprover.display_name);
364 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
365 itemClassIn => tempApprover.item_class,
366 itemIdIn => tempApprover.item_id,
367 actionTypeIdIn => tempApprover.action_type_id,
368 groupOrChainIdIn => tempApprover.group_or_chain_id);
369 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
370 /* The engine will set tempApprover.approver_order_number; leave it null here. */
371 /* Decide whether to end the chain. */
372 if(topDogFound or
373 (finalAuthorityFound and
374 not includeAllJobLevelApprovers)) then
375 /*
376 The approver is the last approver in the chain. He should get inserted into the
377 approver List */
378 tempMemberOrderNumber := tempMemberOrderNumber + 1;
379 if(votingRegimeType = ame_util.serializedVoting) then
380 tempApprover.member_order_number := tempMemberOrderNumber;
381 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
382 tempApprover.member_order_number := 1;
383 end if;
384 finalApproverFound := true;
385 /* reassign the value of source in case approver was a firstAuthority insertee */
386 if firstApproverSource is not null then
387 tempApprover.source := firstApproverSource;
388 firstApproverSource := null;
389 end if;
390 ame_engine.addApprover(approverIn => tempApprover);
391 exit;
392 end if;
393 /*
394 At this point finalAuthorityFound implies includeAllJobLevelApprovers, so the following if
395 doesn't need to check includeAllJobLevelApprovers. But it's implicit in the if statement.
396 */
397 if(finalAuthorityFound and
398 tempOldJobLevel <> tempJobLevel) then
399 /*
400 The approver is the last approver in the chain. He should get inserted into the
401 approver List */
402 tempMemberOrderNumber := tempMemberOrderNumber + 1;
403 if(votingRegimeType = ame_util.serializedVoting) then
404 prevApprover.member_order_number := tempMemberOrderNumber;
405 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
406 prevApprover.member_order_number := 1;
407 end if;
408 finalApproverFound := true;
409 /* reassign the value of source in case approver was a firstAuthority insertee */
410 if firstApproverSource is not null then
411 prevApprover.source := firstApproverSource;
412 firstApproverSource := null;
413 end if;
414 ame_engine.addApprover(approverIn => prevApprover);
415 exit;
416 end if;
417 tempOldJobLevel := tempJobLevel;
418 /* The approver is not the last approver in the chain. Must check the approver
419 category with that of prev approver to see if category change has occurred If yes
420 insert the approver in the chain */
421 if prevApprover.approver_category is not null and
422 tempApprover.approver_category <> prevApprover.approver_category then
423 tempMemberOrderNumber := tempMemberOrderNumber + 1;
424 if(votingRegimeType = ame_util.serializedVoting) then
425 prevApprover.member_order_number := tempMemberOrderNumber;
426 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
427 prevApprover.member_order_number := 1;
428 end if;
429 finalApproverFound := true;
430 /* reassign the value of source in case approver was a firstAuthority insertee */
431 if firstApproverSource is not null then
432 prevApprover.source := firstApproverSource;
433 firstApproverSource := null;
434 end if;
435 ame_engine.addApprover(approverIn => prevApprover);
436 /* check to see if there is a COA insertion after this approver. If a COA insertion is
437 found, keep checking till no more COA insertions. The check for final authority will need to be
438 done again.
439 */
440 loop
441 /* Initialize COAInsertee approverRecord2 */
442 COAInsertee := ame_util.emptyApproverRecord2;
443 /* Check if there are any COAInsertions */
444 ame_engine.getHandlerCOAInsertion(nameIn => prevApprover.name,
445 itemClassIn => prevApprover.item_class,
446 itemIdIn => prevApprover.item_id,
447 actionTypeIdIn => prevApprover.action_type_id,
448 groupOrChainIdIn => prevApprover.group_or_chain_id,
449 occurrenceIn => prevApprover.occurrence,
450 approvalStatusIn => prevApprover.approval_status,
451 nameOut => COAInsertee.name,
452 origSystemOut => COAInsertee.orig_system,
453 origSystemIdOut => COAInsertee.orig_system_id,
454 displayNameOut => COAInsertee.display_name,
455 sourceOut => COAInsertee.source);
456 if COAInsertee.name is null then
457 exit;
458 else
459 prevApprover.name := COAInsertee.name;
460 prevApprover.orig_system := COAInsertee.orig_system;
461 prevApprover.orig_system_id := COAInsertee.orig_system_id;
462 prevApprover.display_name := COAInsertee.display_name;
463 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => prevApprover.orig_system_id,
464 jobLevelOut => tempJobLevel,
465 supervisorIdOut => tempSupervisorId);
466 getCatSourceAndAuthority(personIdIn => prevApprover.orig_system_id,
467 jobLevelIn => tempJobLevel,
468 supervisorIdIn => tempSupervisorId,
469 categoryOut => prevApprover.approver_category,
470 sourceOut => prevApprover.source,
471 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
472 supervisorJobLevelOut => tempSupervisorJobLevel,
473 nextSupervisorIdOut => tempNextSupervisorId);
474 prevApprover.source := COAInsertee.source;
475 prevApprover.api_insertion := ame_util.apiAuthorityInsertion;
476 tempMemberOrderNumber := tempMemberOrderNumber + 1;
477 if(votingRegimeType = ame_util.serializedVoting) then
478 prevApprover.member_order_number := tempMemberOrderNumber;
479 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
480 prevApprover.member_order_number := 1;
481 end if;
482 prevApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => prevApprover.name,
483 itemClassIn => prevApprover.item_class,
484 itemIdIn => prevApprover.item_id,
485 actionTypeIdIn => prevApprover.action_type_id,
486 groupOrChainIdIn => prevApprover.group_or_chain_id);
487 prevApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => prevApprover);
488 /* If approver has a status of ame_util.approve or ame_util.approveAndForwardStatus or
489 ame_util.nullStatus check to see if approver could have final authority */
490 if ((prevApprover.approval_status is null) or
491 (prevApprover.approval_status in
492 (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
493 ame_util.repeatedStatus, ame_util.suppressedStatus,
494 ame_util.beatByFirstResponderStatus, ame_util.nullStatus)) or
495 (prevApprover.approver_category = ame_util.approvalApproverCategory and
496 prevApprover.approval_status = ame_util.notifiedStatus) )
497 then
498 if(not finalAuthorityFound and
499 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
500 finalAuthorityFound := true;
501 end if;
502 end if;
503 ame_engine.addApprover(approverIn => prevApprover);
504 coaInsAuthForward := true;
505 end if;
506 end loop;
507 end if;
508 ame_util.copyApproverRecord2(approverRecord2In => tempApprover,
509 approverRecord2Out => prevApprover);
510 /* Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
511 if tempSupervisorId is null then
512 raise noSupervisorException;
513 else
514 tempApprover.orig_system_id := tempSupervisorId;
515 end if;
516 tempApprover.api_insertion := ame_util.oamGenerated;
517 if(tempSupervisorJobLevel is null) then
518 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
519 jobLevelOut => tempJobLevel,
520 supervisorIdOut => tempSupervisorId);
521 else
522 tempJobLevel := tempSupervisorJobLevel;
523 tempSupervisorId := tempNextSupervisorId;
524 end if;
525 if firstAuthInsExists then
526 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
527 end if;
528 if coaInsAuthForward then
529 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
530 end if;
531 end loop;
532 /* check to see if there is a COA insertion after the final approver. If a COA insertion is
533 found, keep checking till no more COA insertions. The check for final authority need not be
534 done again.
535 */
536 loop
537 /* Initialize COAInsertee approverRecord2 */
538 COAInsertee := ame_util.emptyApproverRecord2;
539 /* Check if there are any COAInsertions */
540 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
541 itemClassIn => tempApprover.item_class,
542 itemIdIn => tempApprover.item_id,
543 actionTypeIdIn => tempApprover.action_type_id,
544 groupOrChainIdIn => tempApprover.group_or_chain_id,
545 occurrenceIn => tempApprover.occurrence,
546 approvalStatusIn => tempApprover.approval_status,
547 nameOut => COAInsertee.name,
548 origSystemOut => COAInsertee.orig_system,
549 origSystemIdOut => COAInsertee.orig_system_id,
550 displayNameOut => COAInsertee.display_name,
551 sourceOut => COAInsertee.source);
552 if COAInsertee.name is null then
553 exit;
554 else
555 tempApprover.name := COAInsertee.name;
556 tempApprover.orig_system := COAInsertee.orig_system;
557 tempApprover.orig_system_id := COAInsertee.orig_system_id;
558 tempApprover.display_name := COAInsertee.display_name;
559 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
560 jobLevelOut => tempJobLevel,
561 supervisorIdOut => tempSupervisorId);
562 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
563 jobLevelIn => tempJobLevel,
564 supervisorIdIn => tempSupervisorId,
565 categoryOut => tempApprover.approver_category,
566 sourceOut => tempApprover.source,
567 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
568 supervisorJobLevelOut => tempSupervisorJobLevel,
569 nextSupervisorIdOut => tempNextSupervisorId);
570 tempApprover.source := COAInsertee.source;
571 tempApprover.approver_category := ame_util.approvalApproverCategory;
572 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
573 tempMemberOrderNumber := tempMemberOrderNumber + 1;
574 if(votingRegimeType = ame_util.serializedVoting) then
575 tempApprover.member_order_number := tempMemberOrderNumber;
576 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
577 tempApprover.member_order_number := 1;
578 end if;
579 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
580 itemClassIn => tempApprover.item_class,
581 itemIdIn => tempApprover.item_id,
582 actionTypeIdIn => tempApprover.action_type_id,
583 groupOrChainIdIn => tempApprover.group_or_chain_id);
584 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
585 ame_engine.addApprover(approverIn => tempApprover);
586 end if;
587 end loop;
588 exception
589 when noSupervisorException then
590 if tempApprover.display_name is null then
591 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
592 origSystemIn => ame_util.perOrigSystem,
593 origSystemIdIn => tempApprover.orig_system_id );
594 else
595 personDisplayName := tempApprover.display_name;
596 end if;
597 errorCode := -20206;
598 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
599 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
600 tokenNameOneIn => 'FIRST_NAME',
601 tokenValueOneIn => personDisplayName,
602 tokenNameTwoIn => 'LAST_NAME',
603 tokenValueTwoIn => null ,
604 tokenNameThreeIn => 'OTHER_NAME',
605 tokenValueThreeIn => null );
606 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
607 routineNameIn => 'handler',
608 exceptionNumberIn => errorCode,
609 exceptionStringIn => errorMessage);
610 raise_application_error(errorCode,
611 errorMessage);
612 when nullFirstIdException then
613 errorCode := -20106;
614 errorMessage :=
615 ame_util.getMessage(applicationShortNameIn => 'PER',
616 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
617 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
618 routineNameIn => 'handler',
619 exceptionNumberIn => errorCode,
620 exceptionStringIn => errorMessage);
621 raise_application_error(errorCode,
622 errorMessage);
623 when topDogRequestorException then
624 errorCode := -20111;
625 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
626 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
627 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
628 routineNameIn => 'handler',
629 exceptionNumberIn => errorCode,
630 exceptionStringIn => errorMessage);
631 raise_application_error(errorCode,
632 errorMessage);
633 when others then
634 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
635 routineNameIn => 'handler',
636 exceptionNumberIn => sqlcode,
637 exceptionStringIn => sqlerrm);
638 l_error_code := sqlcode;
639 if l_error_code = -20213 then
640 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
641 messageNameIn => 'AME_400834_INV_HANDLR_APR',
642 tokenNameOneIn => 'ACTION_TYPE_NAME',
643 tokenValueOneIn => ame_engine.getActionTypeName(tempApprover.action_type_id),
644 tokenNameTwoIn => 'ORIG_SYSTEM',
645 tokenValueTwoIn => ame_util.perOrigSystem,
646 tokenNameThreeIn => 'ORIG_SYSEM_ID',
647 tokenValueThreeIn => tempApprover.orig_system_id);
648 raise_application_error(errorCode,errorMessage);
649 end if;
650 raise;
651 end handler;
652 procedure parseAndSortRules as
653 badParameterException exception;
654 errorCode integer;
655 errorMessage ame_util.longestStringType;
656 tempCategory ame_util.charType;
657 tempLength integer;
658 tempNumber integer;
659 tempRuleId integer;
660 tempSign ame_util.charType;
661 upperLimit integer;
662 begin
663 /* Parse. */
664 for i in 1 .. parametersCount loop
665 tempLength := lengthb(parameters(i));
666 if(substrb(parameters(i), 1, 1) = 'R') then
667 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) +
668 threshholdJobLevel;
669 parameterSigns(i) := substrb(parameters(i), -1, 1);
670 else
671 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) ;
672 parameterSigns(i) := substrb(parameters(i), -1, 1);
673 end if;
674 if(parameterSigns(i) <> '+' and
675 parameterSigns(i) <> '-') then
676 raise badParameterException;
677 end if;
678 end loop;
679 /* Sort. */
680 for i in 2 .. parametersCount loop
681 upperLimit := i - 1;
682 for j in 1 .. upperLimit loop
683 if(parameterNumbers(i) < parameterNumbers(j) or
684 (parameterNumbers(i) = parameterNumbers(j) and
685 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
686 tempRuleId := ruleIds(j);
687 tempCategory := approverCategories(j);
688 tempNumber := parameterNumbers(j);
689 tempSign := parameterSigns(j);
690 ruleIds(j) := ruleIds(i);
691 approverCategories(j) := approverCategories(i);
692 parameterNumbers(j) := parameterNumbers(i);
693 parameterSigns(j) := parameterSigns(i);
694 ruleIds(i) := tempRuleId;
695 approverCategories(i) := tempCategory;
696 parameterNumbers(i) := tempNumber;
697 parameterSigns(i) := tempSign;
698 end if;
699 end loop;
700 end loop;
701 exception
702 when badParameterException then
703 errorCode := -20001;
704 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
705 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
706 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
707 routineNameIn => 'parseAndSortRules',
708 exceptionNumberIn => errorCode,
709 exceptionStringIn => errorMessage);
710 raise_application_error(errorCode,
711 errorMessage);
712 when others then
713 ame_util.runtimeException(packageNameIn => 'ame_final_only_handler',
714 routineNameIn => 'parseAndSortRules',
715 exceptionNumberIn => sqlcode,
716 exceptionStringIn => sqlerrm);
717 raise;
718 end parseAndSortRules;
719 end ame_final_only_handler;