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