[Home] [Help]
PACKAGE BODY: APPS.AME_MANAGER_FINAL_HANDLER
Source
1 package body ame_manager_final_handler as
2 /* $Header: ameemfha.pkb 120.6 2011/05/18 11:24:26 nchinnam ship $ */
3 /* package variables */
4 approverCategories ame_util.charList;
5 managerFound 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 tempRuleSatisfied := false;
88 topDogFound := false;
89 if(jobLevelIn >= parameterNumbers(i)) then
90 tempRuleSatisfied := true;
91 elsif(parameterSigns(i) = '-') then
92 if(supervisorJobLevel is null) then
93 if(supervisorIdIn is null) then
94 raise noSupervisorException;
95 end if;
96 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => supervisorIdIn,
97 jobLevelOut => supervisorJobLevel,
98 supervisorIdOut => nextSupervisorIdOut);
99 supervisorJobLevelOut := supervisorJobLevel;
100 else
101 supervisorJobLevelOut := supervisorJobLevel;
102 end if;
103 if(supervisorJobLevel > parameterNumbers(i)) then
104 tempRuleSatisfied := true;
105 end if;
106 end if;
107 end if;
108 /* Update hasFinalAuthorityYN as needed. */
109 if(not tempRuleSatisfied and
110 hasFinalAuthorityYN = ame_util.booleanTrue) then
111 hasFinalAuthorityYN := ame_util.booleanFalse;
112 end if;
113 /* Determine whether the current rule requires the approver. */
114 tempRuleRequiresApprover := false;
115 if(ruleSatisfiedYN(i) = ame_util.booleanTrue) then
116 if(not tempRuleSatisfied) then
117 tempRuleRequiresApprover := true;
118 end if;
119 else
120 tempRuleRequiresApprover := true;
121 if(tempRuleSatisfied) then
122 ruleSatisfiedYN(i) := ame_util.booleanTrue;
123 end if;
124 end if;
125 if(tempRuleRequiresApprover) then
126 /* Update source. */
127 ame_util.appendRuleIdToSource(ruleIdIn => ruleIds(i),
128 sourceInOut => source);
129 /* Update category as needed. */
130 if(category = ame_util.fyiApproverCategory and
131 approverCategories(i) = ame_util.approvalApproverCategory) then
132 category := ame_util.approvalApproverCategory;
133 end if;
134 end if;
135 end loop;
136 categoryOut := category;
137 hasFinalAuthorityYNOut := hasFinalAuthorityYN;
138 sourceOut := source;
139 exception
140 when noSupervisorException then
141 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
142 origSystemIn => ame_util.perOrigSystem,
143 origSystemIdIn => personIdIn );
144 errorCode := -20208;
145 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
146 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
147 tokenNameOneIn => 'FIRST_NAME',
148 tokenValueOneIn => personDisplayName,
149 tokenNameTwoIn => 'LAST_NAME',
150 tokenValueTwoIn => null ,
151 tokenNameThreeIn => 'OTHER_NAME',
152 tokenValueThreeIn => null );
153 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
154 routineNameIn => 'getCatSourceAndAuthority',
155 exceptionNumberIn => errorCode,
156 exceptionStringIn => errorMessage);
157 raise_application_error(errorCode,
158 errorMessage);
159 when others then
160 categoryOut := null;
161 hasFinalAuthorityYNOut := null;
162 sourceOut := null;
163 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
164 routineNameIn => 'getCatSourceAndAuthority',
165 exceptionNumberIn => sqlcode,
166 exceptionStringIn => sqlerrm);
167 raise;
168 end getCatSourceAndAuthority;
169 procedure handler as
170 checkForInsertee boolean;
171 COAInsertee ame_util.approverRecord2;
172 errorCode integer;
173 errorMessage ame_util.longestStringType;
174 finalAuthorityApproverCategory ame_util.charType;
175 finalAuthorityFound boolean;
176 finalAuthoritySource ame_util.longStringType;
177 firstApproverSource ame_util.longStringType;
178 includeAllJobLevelApprovers boolean;
179 noSupervisorException exception;
180 nullFirstIdException exception;
181 personDisplayName ame_util.longStringType;
182 prevApprover ame_util.approverRecord2;
183 requestorId integer;
184 startingPointId integer;
185 tempApprover ame_util.approverRecord2;
186 tempHasFinalAuthorityYN ame_util.charType;
187 tempJobLevel integer;
188 tempMemberOrderNumber integer;
189 tempOldJobLevel integer;
190 tempSupervisorId integer;
191 tempSupervisorJobLevel integer;
192 tempNextSupervisorId integer;
193 topDogRequestorException exception;
194 votingRegimeType ame_util.stringType;
195 firstAuthInsExists boolean := false;
196 coaInsAuthForward boolean := false;
197 l_error_code number;
198 begin
199 finalAuthorityApproverCategory := null;
200 errorCode := -20232;
201 finalAuthoritySource := null;
202 includeAllJobLevelApprovers :=
203 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
204 ame_util.booleanAttributeTrue;
205 /* Populate some of the package variables. */
206 managerFound := false;
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 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
254 origSystemIn => ame_util.perOrigSystem,
255 origSystemIdIn => tempApprover.orig_system_id,
256 nameOut => tempApprover.name,
257 displayNameOut => tempApprover.display_name);
258 end if;
259 tempApprover.api_insertion := ame_util.oamGenerated;
260 else
261 tempApprover.name := COAInsertee.name;
262 tempApprover.orig_system := COAInsertee.orig_system;
263 tempApprover.orig_system_id := COAInsertee.orig_system_id;
264 tempApprover.display_name := COAInsertee.display_name;
265 firstApproverSource := COAInsertee.source;
266 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
267 firstAuthInsExists := true;
268 end if;
269 /* The threshhold Job level is the job level of this tempApprover */
270 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
271 jobLevelOut => tempJobLevel,
272 supervisorIdOut => tempSupervisorId);
273 threshholdJobLevel := tempJobLevel;
274 parametersCount := parameters.count;
275 parseAndSortRules;
276 for i in 1 .. ruleIds.count loop
277 ruleSatisfiedYN(i) := ame_util.booleanFalse;
278 end loop;
279 /*
280 Check whether self-approval is allowed. If so, check whether there is a non-default
281 starting point or a COA firstAuthority approver. If not, check whether the requestor
282 has enough authority to self-approve. If so, insert the approver as the only approver,
283 with a status of approved, and return. This can not be done earlier as we need the
284 parsed job levels
285 */
286 if(COAInsertee.name is null and
287 startingPointId is null )
288 then
289 if (ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
290 = ame_util.booleanAttributeTrue)
291 then
292 getCatSourceAndAuthority(personIdIn => requestorId,
293 jobLevelIn => tempJobLevel,
294 supervisorIdIn => tempSupervisorId,
295 categoryOut => tempApprover.approver_category,
296 sourceOut => tempApprover.source,
297 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
298 supervisorJobLevelOut => tempSupervisorJobLevel,
299 nextSupervisorIdOut => tempNextSupervisorId);
300 if(tempHasFinalAuthorityYN = ame_util.booleanTrue) then
301 tempApprover.api_insertion := ame_util.oamGenerated;
302 tempApprover.orig_system_id := requestorId;
303 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
304 origSystemIn => ame_util.perOrigSystem,
305 origSystemIdIn => tempApprover.orig_system_id,
306 nameOut => tempApprover.name,
307 displayNameOut => tempApprover.display_name);
308 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
309 nameIn => tempApprover.name,
310 itemClassIn => tempApprover.item_class,
311 itemIdIn => tempApprover.item_id,
312 actionTypeIdIn => tempApprover.action_type_id,
313 groupOrChainIdIn => tempApprover.group_or_chain_id);
314 tempApprover.member_order_number := 1;
315 tempApprover.approval_status := ame_util.approvedStatus;
316 ame_engine.addApprover(approverIn => tempApprover);
317 return;
318 end if;
319 end if;
320 /* As self approval is not an option. And only requestorId is defined, the chain
321 should start from the requestor's manager. Check to make sure tempSupervisorId
322 is not null, else raise noSupervisorException */
323 if tempSupervisorId is null then
324 if tempApprover.orig_system_id = topDogPersonId then
325 raise topDogRequestorException;
326 end if;
327 raise noSupervisorException;
328 else
329 tempApprover.orig_system_id := tempSupervisorId;
330 end if;
331 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
332 origSystemIn => ame_util.perOrigSystem,
333 origSystemIdIn => tempApprover.orig_system_id,
334 nameOut => tempApprover.name,
335 displayNameOut => tempApprover.display_name);
336 if tempSupervisorJobLevel is null then
337 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
338 personIdIn => tempApprover.orig_system_id,
339 jobLevelOut => tempJobLevel,
340 supervisorIdOut => tempSupervisorId);
341 else
342 tempJobLevel := tempSupervisorJobLevel;
343 end if;
344 end if;
345 /* Build the chain. */
346 prevApprover := ame_util.emptyApproverRecord2;
347 finalAuthorityFound := false;
348 tempMemberOrderNumber := 0; /* pre-increment */
349 errorCode := -20233;
350 loop
351 checkForInsertee := false;
352 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
353 jobLevelIn => tempJobLevel,
354 supervisorIdIn => tempSupervisorId,
355 categoryOut => tempApprover.approver_category,
356 sourceOut => tempApprover.source,
357 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
358 supervisorJobLevelOut => tempSupervisorJobLevel,
359 nextSupervisorIdOut => tempNextSupervisorId);
360 /* reassign the value of source in case approver was a firstAuthority insertee */
361 if firstApproverSource is not null then
362 tempApprover.source := firstApproverSource;
363 firstApproverSource := null;
364 end if;
365 if(not finalAuthorityFound and
366 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
367 finalAuthorityFound := true;
368 finalAuthorityApproverCategory := tempApprover.approver_category;
369 finalAuthoritySource := tempApprover.source;
370 end if;
371 if (tempApprover.source is null and
372 finalAuthoritySource is not null ) then
373 tempApprover.approver_category := finalAuthorityApproverCategory;
374 tempApprover.source := finalAuthoritySource;
375 end if;
376 tempApprover.api_insertion := ame_util.oamGenerated;
377 /* The engine will set tempApprover.approver_order_number; leave it null here. */
378 /* Decide whether to end the chain. */
379 if(topDogFound or
380 (finalAuthorityFound and
381 not includeAllJobLevelApprovers)) then
382 /*
383 The approver is the last approver in the chain. He should get inserted into the
384 approver List */
385 tempMemberOrderNumber := tempMemberOrderNumber + 1;
386 if(votingRegimeType = ame_util.serializedVoting) then
387 tempApprover.member_order_number := tempMemberOrderNumber;
388 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
389 tempApprover.member_order_number := 1;
390 end if;
391 checkForInsertee := true;
392 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
393 origSystemIn => ame_util.perOrigSystem,
394 origSystemIdIn => tempApprover.orig_system_id,
395 nameOut => tempApprover.name,
396 displayNameOut => tempApprover.display_name);
397 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
398 itemClassIn => tempApprover.item_class,
399 itemIdIn => tempApprover.item_id,
400 actionTypeIdIn => tempApprover.action_type_id,
401 groupOrChainIdIn => tempApprover.group_or_chain_id);
402 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
403 ame_engine.addApprover(approverIn => tempApprover);
404 exit;
405 end if;
406 if(tempSupervisorJobLevel is null) then
407 if(tempSupervisorId is null) then
408 raise noSupervisorException;
409 end if;
410 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempSupervisorId,
411 jobLevelOut => tempSupervisorJobLevel,
412 supervisorIdOut => tempNextSupervisorId);
413 end if;
414 /* At this point finalAuthorityFound implies includeAllJobLevelApprovers, so
415 the following if doesn't need to check includeAllJobLevelApprovers. But it's
416 implicit in the if statement. */
417 if(finalAuthorityFound and
418 tempSupervisorJobLevel <> tempJobLevel) then
419 /*
420 The approver was the last approver in the chain. He should get inserted
421 into the approver List */
422 tempMemberOrderNumber := tempMemberOrderNumber + 1;
423 if(votingRegimeType = ame_util.serializedVoting) then
424 tempApprover.member_order_number := tempMemberOrderNumber;
425 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
426 tempApprover.member_order_number := 1;
427 end if;
428 checkForInsertee := true;
429 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
430 origSystemIn => ame_util.perOrigSystem,
431 origSystemIdIn => tempApprover.orig_system_id,
432 nameOut => tempApprover.name,
433 displayNameOut => tempApprover.display_name);
434 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
435 itemClassIn => tempApprover.item_class,
436 itemIdIn => tempApprover.item_id,
437 actionTypeIdIn => tempApprover.action_type_id,
438 groupOrChainIdIn => tempApprover.group_or_chain_id);
439 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
440 ame_engine.addApprover(approverIn => tempApprover);
441 exit;
442 end if;
443 /* The approver is not the last approver in the chain. Must check the approver
444 category with that of prev approver to see if category change has occurred or if
445 the approver is the manager, if yes insert the approver in the chain */
446 if not managerFound then
447 managerFound := true;
448 tempMemberOrderNumber := tempMemberOrderNumber + 1;
449 if(votingRegimeType = ame_util.serializedVoting) then
450 tempApprover.member_order_number := tempMemberOrderNumber;
451 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
452 tempApprover.member_order_number := 1;
453 end if;
454 checkForInsertee := true;
455 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
456 origSystemIn => ame_util.perOrigSystem,
457 origSystemIdIn => tempApprover.orig_system_id,
458 nameOut => tempApprover.name,
459 displayNameOut => tempApprover.display_name);
460 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
461 itemClassIn => tempApprover.item_class,
462 itemIdIn => tempApprover.item_id,
463 actionTypeIdIn => tempApprover.action_type_id,
464 groupOrChainIdIn => tempApprover.group_or_chain_id);
465 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
466 ame_engine.addApprover(approverIn => tempApprover);
467 elsif (prevApprover.approver_category is not null and
468 tempApprover.approver_category <> prevApprover.approver_category ) then
469 managerFound := true;
470 tempMemberOrderNumber := tempMemberOrderNumber + 1;
471 if(votingRegimeType = ame_util.serializedVoting) then
472 prevApprover.member_order_number := tempMemberOrderNumber;
473 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
474 prevApprover.member_order_number := 1;
475 end if;
476 checkForInsertee := true;
477 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
478 origSystemIn => ame_util.perOrigSystem,
479 origSystemIdIn => prevApprover.orig_system_id,
480 nameOut => prevApprover.name,
481 displayNameOut => prevApprover.display_name);
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 ame_engine.addApprover(approverIn => prevApprover);
489 end if;
490 /* Check to see if an insertion was done to the approver list. If yes, check for any
491 COA insertions. NOTE: The same check will have to be done as the last step in
492 the handler again */
493 if checkForInsertee then
494 /* check to see if there is a COA insertion after this approver. If a COA
495 insertion is found, keep checking till no more COA insertions. The check for
496 final authority will need to be done again. */
497 checkForInsertee := false;
498 loop
499 /* Initialize COAInsertee approverRecord2 */
500 COAInsertee := ame_util.emptyApproverRecord2;
501 /* Check if there are any COAInsertions */
502 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
503 itemClassIn => tempApprover.item_class,
504 itemIdIn => tempApprover.item_id,
505 actionTypeIdIn => tempApprover.action_type_id,
506 groupOrChainIdIn => tempApprover.group_or_chain_id,
507 occurrenceIn => tempApprover.occurrence,
508 approvalStatusIn => tempApprover.approval_status,
509 nameOut => COAInsertee.name,
510 origSystemOut => COAInsertee.orig_system,
511 origSystemIdOut => COAInsertee.orig_system_id,
512 displayNameOut => COAInsertee.display_name,
513 sourceOut => COAInsertee.source);
514 if COAInsertee.name is null then
515 exit;
516 else
517 tempApprover.name := COAInsertee.name;
518 tempApprover.orig_system := COAInsertee.orig_system;
519 tempApprover.orig_system_id := COAInsertee.orig_system_id;
520 tempApprover.display_name := COAInsertee.display_name;
521 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
522 jobLevelOut => tempJobLevel,
523 supervisorIdOut => tempSupervisorId);
524 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
525 jobLevelIn => tempJobLevel,
526 supervisorIdIn => tempSupervisorId,
527 categoryOut => tempApprover.approver_category,
528 sourceOut => tempApprover.source,
529 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
530 supervisorJobLevelOut => tempSupervisorJobLevel,
531 nextSupervisorIdOut => tempNextSupervisorId);
532 tempApprover.source := COAInsertee.source;
533 tempApprover.approver_category := ame_util.approvalApproverCategory;
534 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
535 tempMemberOrderNumber := tempMemberOrderNumber + 1;
536 if(votingRegimeType = ame_util.serializedVoting) then
537 tempApprover.member_order_number := tempMemberOrderNumber;
538 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
539 tempApprover.member_order_number := 1;
540 end if;
541 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
542 itemClassIn => tempApprover.item_class,
543 itemIdIn => tempApprover.item_id,
544 actionTypeIdIn => tempApprover.action_type_id,
545 groupOrChainIdIn => tempApprover.group_or_chain_id);
546 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
547 /* If approver has a status of ame_util.approve or ame_util.approveAndForwardStatus or
548 ame_util.nullStatus check to see if approver could have final authority */
549 if ((tempApprover.approval_status is null) or
550 (tempApprover.approval_status in
551 (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
552 ame_util.repeatedStatus, ame_util.suppressedStatus,
553 ame_util.beatByFirstResponderStatus, ame_util.nullStatus)) or
554 (tempApprover.approver_category = ame_util.approvalApproverCategory and
555 tempApprover.approval_status = ame_util.notifiedStatus) )
556 then
557 if(not finalAuthorityFound and
558 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
559 finalAuthorityFound := true;
560 end if;
561 end if;
562 ame_engine.addApprover(approverIn => tempApprover);
563 end if;
564 coaInsAuthForward := true;
565 end loop;
566 if(finalAuthorityFound and
567 (( includeAllJobLevelApprovers and
568 tempSupervisorJobLevel <> tempJobLevel) or
569 (not includeAllJobLevelApprovers )) )
570 then
571 exit;
572 end if;
573 end if;
574 /* Check to see tempSupervisorId is not null, else raise noSupervisorException */
575 if tempSupervisorId is null then
576 raise noSupervisorException;
577 else
578 tempApprover.orig_system_id := tempSupervisorId;
579 end if;
580 tempOldJobLevel := tempJobLevel;
581 if(tempSupervisorJobLevel is null) then
582 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
583 jobLevelOut => tempJobLevel,
584 supervisorIdOut => tempSupervisorId);
585 else
586 tempJobLevel := tempSupervisorJobLevel;
587 tempSupervisorId := tempNextSupervisorId;
588 end if;
589 ame_util.copyApproverRecord2(approverRecord2In => tempApprover,
590 approverRecord2Out => prevApprover);
591 if firstAuthInsExists then
592 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
593 end if;
594 if coaInsAuthForward then
595 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
596 end if;
597 end loop;
598 /* Check to make sure that final approver was inserted. */
599 if checkForInsertee then
600 /* check to see if there is a COA insertion after this approver. If a COA
601 insertion is found, keep checking till no more COA insertions. The check for
602 final authority will need to be done again. */
603 checkForInsertee := false;
604 loop
605 /* Initialize COAInsertee approverRecord2 */
606 COAInsertee := ame_util.emptyApproverRecord2;
607 /* Check if there are any COAInsertions */
608 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
609 itemClassIn => tempApprover.item_class,
610 itemIdIn => tempApprover.item_id,
611 actionTypeIdIn => tempApprover.action_type_id,
612 groupOrChainIdIn => tempApprover.group_or_chain_id,
613 occurrenceIn => tempApprover.occurrence,
614 approvalStatusIn => tempApprover.approval_status,
615 nameOut => COAInsertee.name,
616 origSystemOut => COAInsertee.orig_system,
617 origSystemIdOut => COAInsertee.orig_system_id,
618 displayNameOut => COAInsertee.display_name,
619 sourceOut => COAInsertee.source);
620 if COAInsertee.name is null then
621 exit;
622 else
623 tempApprover.name := COAInsertee.name;
624 tempApprover.orig_system := COAInsertee.orig_system;
625 tempApprover.orig_system_id := COAInsertee.orig_system_id;
626 tempApprover.display_name := COAInsertee.display_name;
627 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
628 jobLevelOut => tempJobLevel,
629 supervisorIdOut => tempSupervisorId);
630 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
631 jobLevelIn => tempJobLevel,
632 supervisorIdIn => tempSupervisorId,
633 categoryOut => tempApprover.approver_category,
634 sourceOut => tempApprover.source,
635 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
636 supervisorJobLevelOut => tempSupervisorJobLevel,
637 nextSupervisorIdOut => tempNextSupervisorId);
638 tempApprover.source := COAInsertee.source;
639 tempApprover.approver_category := ame_util.approvalApproverCategory;
640 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
641 tempMemberOrderNumber := tempMemberOrderNumber + 1;
642 if(votingRegimeType = ame_util.serializedVoting) then
643 tempApprover.member_order_number := tempMemberOrderNumber;
644 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
645 tempApprover.member_order_number := 1;
646 end if;
647 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
648 itemClassIn => tempApprover.item_class,
649 itemIdIn => tempApprover.item_id,
650 actionTypeIdIn => tempApprover.action_type_id,
651 groupOrChainIdIn => tempApprover.group_or_chain_id);
652 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
653 ame_engine.addApprover(approverIn => tempApprover);
654 end if;
655 end loop;
656 end if;
657 exception
658 when noSupervisorException then
659 if tempApprover.display_name is null then
660 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
661 origSystemIn => ame_util.perOrigSystem,
662 origSystemIdIn => tempApprover.orig_system_id );
663 else
664 personDisplayName := tempApprover.display_name;
665 end if;
666 errorCode := -20208;
667 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
668 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
669 tokenNameOneIn => 'FIRST_NAME',
670 tokenValueOneIn => personDisplayName,
671 tokenNameTwoIn => 'LAST_NAME',
672 tokenValueTwoIn => null ,
673 tokenNameThreeIn => 'OTHER_NAME',
674 tokenValueThreeIn => null );
675 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
676 routineNameIn => 'handler',
677 exceptionNumberIn => errorCode,
678 exceptionStringIn => errorMessage);
679 raise_application_error(errorCode,
680 errorMessage);
681 when nullFirstIdException then
682 errorCode := -20106;
683 errorMessage :=
684 ame_util.getMessage(applicationShortNameIn => 'PER',
685 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
686 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
687 routineNameIn => 'handler',
688 exceptionNumberIn => errorCode,
689 exceptionStringIn => errorMessage);
690 raise_application_error(errorCode,
691 errorMessage);
692 when topDogRequestorException then
693 errorCode := -20111;
694 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
695 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
696 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
697 routineNameIn => 'handler',
698 exceptionNumberIn => errorCode,
699 exceptionStringIn => errorMessage);
700 raise_application_error(errorCode,
701 errorMessage);
702 when others then
703 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
704 routineNameIn => 'handler',
705 exceptionNumberIn => sqlcode,
706 exceptionStringIn => sqlerrm);
707 l_error_code := sqlcode;
708 if l_error_code = -20213 then
709 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
710 messageNameIn => 'AME_400834_INV_HANDLR_APR',
711 tokenNameOneIn => 'ACTION_TYPE_NAME',
712 tokenValueOneIn => ame_engine.getActionTypeName(tempApprover.action_type_id),
713 tokenNameTwoIn => 'ORIG_SYSTEM',
714 tokenValueTwoIn => ame_util.perOrigSystem,
715 tokenNameThreeIn => 'ORIG_SYSEM_ID',
716 tokenValueThreeIn => tempApprover.orig_system_id);
717 raise_application_error(errorCode,errorMessage);
718 end if;
719 raise;
720 end handler;
721 procedure parseAndSortRules as
722 badParameterException exception;
723 errorCode integer;
724 errorMessage ame_util.longestStringType;
725 tempCategory ame_util.charType;
726 tempLength integer;
727 tempNumber integer;
728 tempRuleId integer;
729 tempSign ame_util.charType;
730 upperLimit integer;
731 begin
732 /* Parse. */
733 for i in 1 .. parametersCount loop
734 tempLength := lengthb(parameters(i));
735 /*
736 if(substrb(parameters(i), 1, 1) = 'R') then
737 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) +
738 threshholdJobLevel;
739 parameterSigns(i) := substrb(parameters(i), -1, 1);
740 else
741 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) ;
742 parameterSigns(i) := substrb(parameters(i), -1, 1);
743 end if;
744 */
745 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
746 parameterSigns(i) := substrb(parameters(i), -1, 1);
747 if(parameterSigns(i) <> '+' and
748 parameterSigns(i) <> '-') then
749 raise badParameterException;
750 end if;
751 end loop;
752 /* Sort. */
753 for i in 2 .. parametersCount loop
754 upperLimit := i - 1;
755 for j in 1 .. upperLimit loop
756 if(parameterNumbers(i) < parameterNumbers(j) or
757 (parameterNumbers(i) = parameterNumbers(j) and
758 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
759 tempRuleId := ruleIds(j);
760 tempCategory := approverCategories(j);
761 tempNumber := parameterNumbers(j);
762 tempSign := parameterSigns(j);
763 ruleIds(j) := ruleIds(i);
764 approverCategories(j) := approverCategories(i);
765 parameterNumbers(j) := parameterNumbers(i);
766 parameterSigns(j) := parameterSigns(i);
767 ruleIds(i) := tempRuleId;
768 approverCategories(i) := tempCategory;
769 parameterNumbers(i) := tempNumber;
770 parameterSigns(i) := tempSign;
771 end if;
772 end loop;
773 end loop;
774 exception
775 when badParameterException then
776 errorCode := -20001;
777 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
778 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
779 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
780 routineNameIn => 'parseAndSortRules',
781 exceptionNumberIn => errorCode,
782 exceptionStringIn => errorMessage);
783 raise_application_error(errorCode,
784 errorMessage);
785 when others then
786 ame_util.runtimeException(packageNameIn => 'ame_manager_final_handler',
787 routineNameIn => 'parseAndSortRules',
788 exceptionNumberIn => sqlcode,
789 exceptionStringIn => sqlerrm);
790 raise;
791 end parseAndSortRules;
792 end ame_manager_final_handler;