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