[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.7 2011/05/17 11:42:13 nchinnam ship $ */
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 := -20251;
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',
75 raise_application_error(errorCode,
72 routineNameIn => 'getJobLevelAndSupervisor',
73 exceptionNumberIn => errorCode,
74 exceptionStringIn => errorMessage);
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 := -20204;
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;
179 /* Update hasFinalAuthorityYN as needed. */
176 end if;
177 end if;
178 end if;
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 := -20204;
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 l_error_code number;
269 begin
270 /* Populate some of the package variables. */
271 finalAuthorityApproverCategory := null;
272 errorCode := -20230;
273 finalAuthoritySource := null;
274 includeAllJobLevelApprovers :=
275 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
276 ame_util.booleanAttributeTrue;
277 topDogPersonId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topSupPersonIdAttribute));
278 /*
279 The engine only calls a handler if a rule requiring it exists, so we can assume that
280 the package variables that ame_engine.getHandlerRules initializes are nonempty.
281 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
282 are harmless here.)
283 */
284 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
285 approverCategoriesOut => approverCategories,
286 parametersOut => parameters);
287 parametersCount := parameters.count;
288 parseAndSortRules;
289 for i in 1 .. ruleIds.count loop
290 ruleSatisfiedYN(i) := ame_util.booleanFalse;
291 end loop;
292 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
293 tempApprover.orig_system := ame_util.perOrigSystem;
297 tempApprover.item_id := ame_engine.getHandlerItemId;
294 tempApprover.authority := ame_util.authorityApprover;
295 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
296 tempApprover.item_class := ame_engine.getHandlerItemClassName;
298 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
299 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
300 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
301 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
302 tempApprover.group_or_chain_order_number := 1;
303 tempApprover.group_or_chain_id := 1;
304 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
305 /* Check to see if there is a COA first approver set */
306 ame_engine.getHandlerCOAFirstApprover(itemClassIn => tempApprover.item_class,
307 itemIdIn => tempApprover.item_id,
308 actionTypeIdIn => tempApprover.action_type_id,
309 groupOrChainIdIn => tempApprover.group_or_chain_id,
310 nameOut => COAInsertee.name,
311 origSystemOut => COAInsertee.orig_system,
312 origSystemIdOut => COAInsertee.orig_system_id,
313 displayNameOut => COAInsertee.display_name,
314 sourceOut => COAInsertee.source);
315 if(COAInsertee.name is null) then
316 /* Check to see if job_level_non_default_starting_point_person_id is defined */
317 startingPointId :=
318 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.jobLevelStartingPointAttribute));
319 if startingPointId is null then
320 requestorId :=
321 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.transactionRequestorAttribute));
322 if(requestorId is null) then
323 raise nullFirstIdException;
324 end if;
325 tempApprover.orig_system_id := requestorId;
326 /* Chain has to start from requestor or requestor's manager*/
327 getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
328 jobLevelOut => tempJobLevel,
329 supervisorIdOut => tempSupervisorId);
330 /* Check if requestor can self approve. If so, insert the approver as the only approver, with a status
331 of approved, and return.*/
332 if(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
333 = ame_util.booleanAttributeTrue)
334 then
335 getCatSourceAndAuthority(personIdIn => requestorId,
336 jobLevelIn => tempJobLevel,
337 supervisorIdIn => tempSupervisorId,
338 categoryOut => tempApprover.approver_category,
339 sourceOut => tempApprover.source,
340 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
341 supervisorJobLevelOut => tempSupervisorJobLevel,
342 nextSupervisorIdOut => tempNextSupervisorId);
343 if(tempHasFinalAuthorityYN = ame_util.booleanTrue) then
344 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
345 itemClassIn => tempApprover.item_class,
346 itemIdIn => tempApprover.item_id,
347 actionTypeIdIn => tempApprover.action_type_id,
348 groupOrChainIdIn => tempApprover.group_or_chain_id);
349 tempApprover.member_order_number := 1;
350 tempApprover.api_insertion := ame_util.oamGenerated;
351 tempApprover.approval_status := ame_util.approvedStatus;
352 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
353 origSystemIdIn => tempApprover.orig_system_id,
354 nameOut => tempApprover.name,
355 displayNameOut => tempApprover.display_name);
356 ame_engine.addApprover(approverIn => tempApprover);
357 return;
358 end if;
359 end if;
360 /* As requestor can not self approve, the chain will start from requestor's manager.
361 Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
362 if tempSupervisorId is null then
363 /* check if the requestor was a topDog, if yes raise appropriate exception */
364 if tempApprover.orig_system_id = topDogPersonId then
365 raise topDogRequestorException;
366 end if;
367 raise noSupervisorException;
368 else
369 tempApprover.orig_system_id := tempSupervisorId;
370 end if;
371 else
372 tempApprover.orig_system_id := startingPointId;
373 end if;
374 tempApprover.api_insertion := ame_util.oamGenerated;
375 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
376 origSystemIdIn => tempApprover.orig_system_id,
377 nameOut => tempApprover.name,
381 tempApprover.name := COAInsertee.name;
378 displayNameOut => tempApprover.display_name);
379 else /* COA firstAuthority insertion found */
380 /* assign tempApprover with appropriate values */
382 tempApprover.orig_system := COAInsertee.orig_system;
383 tempApprover.orig_system_id := COAInsertee.orig_system_id;
384 tempApprover.display_name := COAInsertee.display_name;
385 firstApproverSource := COAInsertee.source;
386 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
387 firstAuthInsExists := true;
388 end if;
389 getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
390 jobLevelOut => tempJobLevel,
391 supervisorIdOut => tempSupervisorId);
392 /* Build the chain. */
393 errorCode := -20231;
394 finalAuthorityFound := false;
395 tempMemberOrderNumber := 0; /* pre-increment */
396 loop
397 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
398 jobLevelIn => tempJobLevel,
399 supervisorIdIn => tempSupervisorId,
400 categoryOut => tempApprover.approver_category,
401 sourceOut => tempApprover.source,
402 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
403 supervisorJobLevelOut => tempSupervisorJobLevel,
404 nextSupervisorIdOut => tempNextSupervisorId);
405 /* reassign the value of source in case approver was a firstAuthority insertee */
406 if firstApproverSource is not null then
407 tempApprover.source := firstApproverSource;
408 firstApproverSource := null;
409 end if;
410 if(not finalAuthorityFound and
411 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
412 finalAuthorityFound := true;
413 finalAuthorityApproverCategory := tempApprover.approver_category;
414 finalAuthoritySource := tempApprover.source;
415 end if;
416 if (tempApprover.source is null and
417 finalAuthoritySource is not null ) then
418 tempApprover.approver_category := finalAuthorityApproverCategory;
419 tempApprover.source := finalAuthoritySource;
420 end if;
421 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
422 origSystemIdIn => tempApprover.orig_system_id,
423 nameOut => tempApprover.name,
424 displayNameOut => tempApprover.display_name);
425 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
426 itemClassIn => tempApprover.item_class,
427 itemIdIn => tempApprover.item_id,
428 actionTypeIdIn => tempApprover.action_type_id,
429 groupOrChainIdIn => tempApprover.group_or_chain_id);
430 tempMemberOrderNumber := tempMemberOrderNumber + 1;
431 if(votingRegimeType = ame_util.serializedVoting) then
432 tempApprover.member_order_number := tempMemberOrderNumber;
433 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
434 tempApprover.member_order_number := 1;
435 end if;
436 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
437 /* The engine will set tempApprover.approver_order_number; leave it null here. */
438 ame_engine.addApprover(approverIn => tempApprover);
439 /* check to see if there is a COA insertion after this approver. If a COA insertion is
440 found, keep checking till no more COA insertions. The check for final authority will need to be
441 done again.
442 */
443 loop
444 /* Initialize COAInsertee approverRecord2 */
445 COAInsertee := ame_util.emptyApproverRecord2;
446 /* Check if there are any COAInsertions */
447 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
448 itemClassIn => tempApprover.item_class,
449 itemIdIn => tempApprover.item_id,
450 actionTypeIdIn => tempApprover.action_type_id,
451 groupOrChainIdIn => tempApprover.group_or_chain_id,
452 occurrenceIn => tempApprover.occurrence,
453 approvalStatusIn => tempApprover.approval_status,
454 nameOut => COAInsertee.name,
455 origSystemOut => COAInsertee.orig_system,
456 origSystemIdOut => COAInsertee.orig_system_id,
457 displayNameOut => COAInsertee.display_name,
458 sourceOut => COAInsertee.source);
459 if COAInsertee.name is null then
460 exit;
461 else
462 tempApprover.name := COAInsertee.name;
463 tempApprover.orig_system := COAInsertee.orig_system;
464 tempApprover.orig_system_id := COAInsertee.orig_system_id;
465 tempApprover.display_name := COAInsertee.display_name;
469 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
466 getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
467 jobLevelOut => tempJobLevel,
468 supervisorIdOut => tempSupervisorId);
470 jobLevelIn => tempJobLevel,
471 supervisorIdIn => tempSupervisorId,
472 categoryOut => tempApprover.approver_category,
473 sourceOut => tempApprover.source,
474 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
475 supervisorJobLevelOut => tempSupervisorJobLevel,
476 nextSupervisorIdOut => tempNextSupervisorId);
477 tempApprover.source := COAInsertee.source;
478 tempApprover.approver_category := ame_util.approvalApproverCategory;
479 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
480 tempMemberOrderNumber := tempMemberOrderNumber + 1;
481 if(votingRegimeType = ame_util.serializedVoting) then
482 tempApprover.member_order_number := tempMemberOrderNumber;
483 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
484 tempApprover.member_order_number := 1;
485 end if;
486 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
487 itemClassIn => tempApprover.item_class,
488 itemIdIn => tempApprover.item_id,
489 actionTypeIdIn => tempApprover.action_type_id,
490 groupOrChainIdIn => tempApprover.group_or_chain_id);
491 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
492 /* If approver has a status of ame_util.approve or ame_util.approveAndForwardStatus or
493 ame_util.nullStatus check to see if approver could have final authority */
494 if ((tempApprover.approval_status is null) or
495 (tempApprover.approval_status in
496 (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
497 ame_util.repeatedStatus, ame_util.suppressedStatus,
498 ame_util.beatByFirstResponderStatus, ame_util.nullStatus)) or
499 (tempApprover.approver_category = ame_util.approvalApproverCategory and
500 tempApprover.approval_status = ame_util.notifiedStatus) )
501 then
502 if(not finalAuthorityFound and
503 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
504 finalAuthorityFound := true;
505 end if;
506 end if;
507 ame_engine.addApprover(approverIn => tempApprover);
508 coaInsAuthForward := true;
509 end if;
510 end loop;
511 /* Decide whether to end the chain. */
512 if(topDogFound or
513 (finalAuthorityFound and
514 not includeAllJobLevelApprovers)) then
515 exit;
516 end if;
517 /* Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
518 if tempSupervisorId is null then
519 raise noSupervisorException;
520 else
521 tempApprover.orig_system_id := tempSupervisorId;
522 end if;
523 tempOldJobLevel := tempJobLevel;
524 if(tempSupervisorJobLevel is null) then
525 getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
526 jobLevelOut => tempJobLevel,
527 supervisorIdOut => tempSupervisorId);
528 else
529 tempJobLevel := tempSupervisorJobLevel;
530 tempSupervisorId := tempNextSupervisorId;
531 end if;
532 /*
533 At this point finalAuthorityFound implies includeAllJobLevelApprovers, so the following if
534 doesn't need to check includeAllJobLevelApprovers. But it's implicit in the if statement.
535 */
536 if firstAuthInsExists then
537 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
538 end if;
539 if coaInsAuthForward then
540 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
541 end if;
542 if(finalAuthorityFound and
543 tempOldJobLevel <> tempJobLevel) then
544 exit;
545 end if;
546 end loop;
547 exception
548 when noSupervisorException then
549 if tempApprover.display_name is null then
550 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
551 origSystemIn => ame_util.perOrigSystem,
552 origSystemIdIn => tempApprover.orig_system_id );
553 else
554 personDisplayName := tempApprover.display_name;
555 end if;
556 errorCode := -20204;
557 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
558 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
559 tokenNameOneIn => 'FIRST_NAME',
560 tokenValueOneIn => personDisplayName,
561 tokenNameTwoIn => 'LAST_NAME',
562 tokenValueTwoIn => null ,
563 tokenNameThreeIn => 'OTHER_NAME',
564 tokenValueThreeIn => null );
565 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
566 routineNameIn => 'handler',
567 exceptionNumberIn => errorCode,
571 when nullFirstIdException then
568 exceptionStringIn => errorMessage);
569 raise_application_error(errorCode,
570 errorMessage);
572 errorCode := -20007;
573 errorMessage :=
574 ame_util.getMessage(applicationShortNameIn => 'PER',
575 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
576 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
577 routineNameIn => 'handler',
578 exceptionNumberIn => errorCode,
579 exceptionStringIn => errorMessage);
580 raise_application_error(errorCode,
581 errorMessage);
582 when topDogRequestorException then
583 errorCode := -20111;
584 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
585 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
586 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
587 routineNameIn => 'handler',
588 exceptionNumberIn => errorCode,
589 exceptionStringIn => errorMessage);
590 raise_application_error(errorCode,
591 errorMessage);
592 when others then
593 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
594 routineNameIn => 'handler',
595 exceptionNumberIn => sqlcode,
596 exceptionStringIn => sqlerrm);
597 l_error_code := sqlcode;
598 if l_error_code = -20213 then
599 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
600 messageNameIn => 'AME_400834_INV_HANDLR_APR',
601 tokenNameOneIn => 'ACTION_TYPE_NAME',
602 tokenValueOneIn => ame_engine.getActionTypeName(tempApprover.action_type_id),
603 tokenNameTwoIn => 'ORIG_SYSTEM',
604 tokenValueTwoIn => ame_util.perOrigSystem,
605 tokenNameThreeIn => 'ORIG_SYSEM_ID',
606 tokenValueThreeIn => tempApprover.orig_system_id);
607 raise_application_error(errorCode,errorMessage);
608 end if;
609 raise;
610 end handler;
611 procedure parseAndSortRules as
612 badParameterException exception;
613 errorCode integer;
614 errorMessage ame_util.longestStringType;
615 tempCategory ame_util.charType;
616 tempLength integer;
617 tempNumber integer;
618 tempRuleId integer;
619 tempSign ame_util.charType;
620 upperLimit integer;
621 begin
622 /* Parse. */
623 for i in 1 .. parametersCount loop
624 tempLength := lengthb(parameters(i));
625 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
626 parameterSigns(i) := substrb(parameters(i), tempLength, tempLength);
627 if(parameterSigns(i) <> '+' and
628 parameterSigns(i) <> '-') then
629 raise badParameterException;
630 end if;
631 end loop;
632 /* Sort. */
633 for i in 2 .. parametersCount loop
634 upperLimit := i - 1;
635 for j in 1 .. upperLimit loop
636 if(parameterNumbers(i) < parameterNumbers(j) or
637 (parameterNumbers(i) = parameterNumbers(j) and
638 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
639 tempRuleId := ruleIds(j);
640 tempCategory := approverCategories(j);
641 tempNumber := parameterNumbers(j);
642 tempSign := parameterSigns(j);
643 ruleIds(j) := ruleIds(i);
644 approverCategories(j) := approverCategories(i);
645 parameterNumbers(j) := parameterNumbers(i);
646 parameterSigns(j) := parameterSigns(i);
647 ruleIds(i) := tempRuleId;
648 approverCategories(i) := tempCategory;
649 parameterNumbers(i) := tempNumber;
650 parameterSigns(i) := tempSign;
651 end if;
652 end loop;
653 end loop;
654 exception
655 when badParameterException then
656 errorCode := -20001;
657 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
658 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
659 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
660 routineNameIn => 'parseAndSortRules',
661 exceptionNumberIn => errorCode,
662 exceptionStringIn => errorMessage);
663 raise_application_error(errorCode,
664 errorMessage);
665 when others then
666 ame_util.runtimeException(packageNameIn => 'ame_absolute_job_level_handler',
667 routineNameIn => 'parseAndSortRules',
671 end parseAndSortRules;
668 exceptionNumberIn => sqlcode,
669 exceptionStringIn => sqlerrm);
670 raise;
672 end ame_absolute_job_level_handler;