[Home] [Help]
PACKAGE BODY: APPS.AME_RELATIVE_JOB_LEVEL_HANDLER
Source
1 package body ame_relative_job_level_handler as
2 /* $Header: ameerjha.pkb 120.6 2011/05/18 11:26:55 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 threshholdJobLevel integer;
12 topDogFound boolean;
13 topDogPersonId integer;
14 /* forward declarations */
15 /*
16 getCatSourceAndAuthority does not account for the ALLOW_REQUESTOR_APPROVAL attribute.
17 The handler procedure does that.
18 */
19 procedure getCatSourceAndAuthority(personIdIn in integer,
20 jobLevelIn in integer,
21 supervisorIdIn in integer,
22 categoryOut out nocopy varchar2,
23 sourceOut out nocopy varchar2,
24 hasFinalAuthorityYNOut out nocopy varchar2,
25 supervisorJobLevelOut out nocopy integer,
26 nextSupervisorIdOut out nocopy integer);
27 /*
28 parseAndSortRules populates the parameterNumbers and parameterSigns tables in
29 ascending lexicographic order, first by numerical order, then with '+' dominating '-'.
30 Note that it does not sort the parameters proper.
31 */
32 procedure parseAndSortRules;
33 /* procedures */
34 procedure getCatSourceAndAuthority(personIdIn in integer,
35 jobLevelIn in integer,
36 supervisorIdIn in integer,
37 categoryOut out nocopy varchar2,
38 sourceOut out nocopy varchar2,
39 hasFinalAuthorityYNOut out nocopy varchar2,
40 supervisorJobLevelOut out nocopy integer,
41 nextSupervisorIdOut out nocopy integer) as
42 category ame_util.charType;
43 errorCode integer;
44 errorMessage ame_util.longestStringType;
45 hasFinalAuthorityYN ame_util.charType;
46 noSupervisorException exception;
47 personDisplayName ame_util.longStringType;
48 source ame_util.longStringType;
49 supervisorJobLevel integer;
50 tempRuleRequiresApprover boolean;
51 tempRuleSatisfied boolean;
52 begin
53 /* Initialize the two output arguments that might not otherwise get set. */
54 supervisorJobLevelOut := null;
55 nextSupervisorIdOut := null;
56 /*
57 1. An approver satisfies a rule in any of three cases:
58 A. The rule's parameter number does not exceed the approver's job level.
59 B. The rule's parameter sign is '-', and the job level of the approver's
60 supervisor exceeds the rule's parameter number.
61 C. The approver is the top dog.
62 2. An approver has final authority if the approver satisfies all the rules.
63 (The handler procedure proper takes care of adding subsequent approvers at
64 the same job level, if the relevant mandatory attribute so requires.)
65 3. The source value is a comma-delimited list of the IDs of the rules that
66 require an approver. This procedure builds up the source value according
67 to the following logic:
68 A. If a rule has not yet been satisfied, the rule requires the input
69 approver.
70 B. Otherwise, the rule requires the input approver only if the approver
71 does <<not>> satisfy the rule. (This would happen in the perverse case
72 that an approver satisfies a rule, but their supervisor has a lower
73 job level that does not satisfy the rule.)
74 4. An approver's category is ame_util.approvalApproverCategory if any of the
75 rule usages requiring the approver is of that category; otherwise the
76 approver's category is ame_util.fyiApproverCategory.
77 */
78 category := ame_util.fyiApproverCategory;
79 hasFinalAuthorityYN := ame_util.booleanTrue;
80 for i in 1 .. parametersCount loop
81 /* Determine whether the approver satisfies the current rule. */
82 if(personIdIn = topDogPersonId) then
83 topDogFound := true;
84 tempRuleSatisfied := true;
85 else
86 topDogFound := false;
87 tempRuleSatisfied := false;
88 if(jobLevelIn >= parameterNumbers(i)) then
89 tempRuleSatisfied := true;
90 elsif(parameterSigns(i) = '-') then
91 if supervisorIdIn is null then
92 supervisorJobLevel := 0;
93 else
94 if(supervisorJobLevel is null) then
95 if(supervisorIdIn is null) then
96 raise noSupervisorException;
97 end if;
98 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => supervisorIdIn,
99 jobLevelOut => supervisorJobLevel,
100 supervisorIdOut => nextSupervisorIdOut);
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 end if;
109 /* Update hasFinalAuthorityYN as needed. */
110 if(not tempRuleSatisfied and
111 hasFinalAuthorityYN = ame_util.booleanTrue) then
112 hasFinalAuthorityYN := ame_util.booleanFalse;
113 end if;
114 /* Determine whether the current rule requires the approver. */
115 tempRuleRequiresApprover := false;
116 if(ruleSatisfiedYN(i) = ame_util.booleanTrue) then
117 if(not tempRuleSatisfied) then
118 tempRuleRequiresApprover := true;
119 end if;
120 else
121 tempRuleRequiresApprover := true;
122 if(tempRuleSatisfied) then
123 ruleSatisfiedYN(i) := ame_util.booleanTrue;
124 end if;
125 end if;
126 if(tempRuleRequiresApprover) then
127 /* Update source. */
128 ame_util.appendRuleIdToSource(ruleIdIn => ruleIds(i),
129 sourceInOut => source);
130 /* Update category as needed. */
131 if(category = ame_util.fyiApproverCategory and
132 approverCategories(i) = ame_util.approvalApproverCategory) then
133 category := ame_util.approvalApproverCategory;
134 end if;
135 end if;
136 end loop;
137 categoryOut := category;
138 hasFinalAuthorityYNOut := hasFinalAuthorityYN;
139 sourceOut := source;
140 exception
141 when noSupervisorException then
142 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
143 origSystemIn => ame_util.perOrigSystem,
144 origSystemIdIn => personIdIn );
145 errorCode := -20209;
146 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
147 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
148 tokenNameOneIn => 'FIRST_NAME',
149 tokenValueOneIn => personDisplayName,
150 tokenNameTwoIn => 'LAST_NAME',
151 tokenValueTwoIn => null ,
152 tokenNameThreeIn => 'OTHER_NAME',
153 tokenValueThreeIn => null );
154 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
155 routineNameIn => 'getCatSourceAndAuthority',
156 exceptionNumberIn => errorCode,
157 exceptionStringIn => errorMessage);
158 raise_application_error(errorCode,
159 errorMessage);
160 when others then
161 categoryOut := null;
162 hasFinalAuthorityYNOut := null;
163 sourceOut := null;
164 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
165 routineNameIn => 'getCatSourceAndAuthority',
166 exceptionNumberIn => sqlcode,
167 exceptionStringIn => sqlerrm);
168 raise;
169 end getCatSourceAndAuthority;
170 procedure handler as
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 personDisplayName ame_util.longStringType;
180 noSupervisorException exception;
181 nullFirstIdException exception;
182 requestorId integer;
183 startingPointId integer;
184 tempApprover ame_util.approverRecord2;
185 tempHasFinalAuthorityYN ame_util.charType;
186 tempJobLevel integer;
187 tempMemberOrderNumber integer;
188 tempOldJobLevel integer;
189 tempSupervisorId integer;
190 tempSupervisorJobLevel integer;
191 tempNextSupervisorId integer;
192 topDogRequestorException exception;
193 votingRegimeType ame_util.stringType;
194 firstAuthInsExists boolean := false;
195 coaInsAuthForward boolean := false;
196 l_error_code number;
197 begin
198 finalAuthorityApproverCategory := null;
199 errorCode := -20228;
200 finalAuthoritySource := null;
201 includeAllJobLevelApprovers :=
202 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
203 ame_util.booleanAttributeTrue;
204 /* Populate some of the package variables. */
205 topDogPersonId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topSupPersonIdAttribute));
206 /*
207 The engine only calls a handler if a rule requiring it exists, so we can assume that
208 the package variables that ame_engine.getHandlerRules initializes are nonempty.
209 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
210 are harmless here.)
211 */
212 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
213 approverCategoriesOut => approverCategories,
214 parametersOut => parameters);
215 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
216 tempApprover.orig_system := ame_util.perOrigSystem;
217 tempApprover.authority := ame_util.authorityApprover;
218 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
219 tempApprover.item_class := ame_engine.getHandlerItemClassName;
220 tempApprover.item_id := ame_engine.getHandlerItemId;
221 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
222 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
223 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
224 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
225 tempApprover.group_or_chain_order_number := 1;
226 tempApprover.group_or_chain_id := 1;
227 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
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 /* Self Approval is only an option for this handler if requestor is the top dog. So only need to check for it in that case.
278 But in case of no COAInsertion and no startingPointId, the chain must start from requestor's manager. This could
279 not be set above as the threshhold has to be the requestor's approval authority. */
280 if(COAInsertee.name is null and
281 startingPointId is null )
282 then
283 /* Check to make sure requestor is not the topDogPersonId, if yes check for autoapproval */
284 if (requestorId = topDogPersonId ) then
285 if(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
286 = ame_util.booleanAttributeTrue) then
287 /* insert the requestor into the chain set the requestor's status to approved
288 end the chain with the requestor */
289 tempApprover.orig_system_id := requestorId;
290 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
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 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
299 nameIn => tempApprover.name,
300 itemClassIn => tempApprover.item_class,
301 itemIdIn => tempApprover.item_id,
302 actionTypeIdIn => tempApprover.action_type_id,
303 groupOrChainIdIn => tempApprover.group_or_chain_id);
304 tempApprover.member_order_number := 1;
305 tempApprover.api_insertion := ame_util.oamGenerated;
306 tempApprover.approval_status := ame_util.approvedStatus;
307 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
308 origSystemIdIn => tempApprover.orig_system_id,
309 nameOut => tempApprover.name,
310 displayNameOut => tempApprover.display_name);
311 ame_engine.addApprover(approverIn => tempApprover);
312 return;
313 else
314 raise topDogRequestorException;
315 end if;
316 elsif (tempSupervisorId is null) then
317 raise noSupervisorException;
318 else
319 tempApprover.orig_system_id := tempSupervisorId ;
320 end if;
321 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
322 jobLevelOut => tempJobLevel,
323 supervisorIdOut => tempSupervisorId);
324 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
325 origSystemIn => ame_util.perOrigSystem,
326 origSystemIdIn =>tempApprover.orig_system_id,
327 nameOut => tempApprover.name,
328 displayNameOut => tempApprover.display_name);
329 end if;
330 /* Build the chain. */
331 errorCode := -20229;
332 finalAuthorityFound := false;
333 tempMemberOrderNumber := 0; /* pre-increment */
334 loop
335 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
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 /* reassign the value of source in case approver was a firstAuthority insertee */
344 if firstApproverSource is not null then
345 tempApprover.source := firstApproverSource;
346 firstApproverSource := null;
347 end if;
348 if(not finalAuthorityFound and
349 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
350 finalAuthorityFound := true;
351 finalAuthorityApproverCategory := tempApprover.approver_category;
352 finalAuthoritySource := tempApprover.source;
353 end if;
354 if (tempApprover.source is null and
355 finalAuthoritySource is not null ) then
356 tempApprover.approver_category := finalAuthorityApproverCategory;
357 tempApprover.source := finalAuthoritySource;
358 end if;
359 tempApprover.api_insertion := ame_util.oamGenerated;
360 tempApprover.occurrence := 1;
361 tempMemberOrderNumber := tempMemberOrderNumber + 1;
362 if(votingRegimeType = ame_util.serializedVoting) then
363 tempApprover.member_order_number := tempMemberOrderNumber;
364 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
365 tempApprover.member_order_number := 1;
366 end if;
367 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
368 /* The engine will set tempApprover.approver_order_number; leave it null here. */
369 ame_engine.addApprover(approverIn => tempApprover);
370 /* check to see if there is a COA insertion after this approver. If a COA insertion is
371 found, keep checking till no more COA insertions. The check for final authority will need to be
372 done again.
373 */
374 loop
375 /* Initialize COAInsertee approverRecord2 */
376 COAInsertee := ame_util.emptyApproverRecord2;
377 /* Check if there are any COAInsertions */
378 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
379 itemClassIn => tempApprover.item_class,
380 itemIdIn => tempApprover.item_id,
381 actionTypeIdIn => tempApprover.action_type_id,
382 groupOrChainIdIn => tempApprover.group_or_chain_id,
383 occurrenceIn => tempApprover.occurrence,
384 approvalStatusIn => tempApprover.approval_status,
385 nameOut => COAInsertee.name,
386 origSystemOut => COAInsertee.orig_system,
387 origSystemIdOut => COAInsertee.orig_system_id,
388 displayNameOut => COAInsertee.display_name,
389 sourceOut => COAInsertee.source);
390 if COAInsertee.name is null then
391 exit;
392 else
393 tempApprover.name := COAInsertee.name;
394 tempApprover.orig_system := COAInsertee.orig_system;
395 tempApprover.orig_system_id := COAInsertee.orig_system_id;
396 tempApprover.display_name := COAInsertee.display_name;
397 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
398 personIdIn => tempApprover.orig_system_id,
399 jobLevelOut => tempJobLevel,
400 supervisorIdOut => tempSupervisorId);
401 coaInsAuthForward := true;
402 getCatSourceAndAuthority(personIdIn => tempApprover.orig_system_id,
403 jobLevelIn => tempJobLevel,
404 supervisorIdIn => tempSupervisorId,
405 categoryOut => tempApprover.approver_category,
406 sourceOut => tempApprover.source,
407 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
408 supervisorJobLevelOut => tempSupervisorJobLevel,
409 nextSupervisorIdOut => tempNextSupervisorId);
410 tempApprover.source := COAInsertee.source;
411 tempApprover.approver_category := ame_util.approvalApproverCategory;
412 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
413 tempMemberOrderNumber := tempMemberOrderNumber + 1;
414 if(votingRegimeType = ame_util.serializedVoting) then
415 tempApprover.member_order_number := tempMemberOrderNumber;
416 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
417 tempApprover.member_order_number := 1;
418 end if;
419 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
420 itemClassIn => tempApprover.item_class,
421 itemIdIn => tempApprover.item_id,
422 actionTypeIdIn => tempApprover.action_type_id,
423 groupOrChainIdIn => tempApprover.group_or_chain_id);
424 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
425 /* If approver has a status of ame_util.approve or ame_util.approveAndForwardStatus or
426 ame_util.nullStatus check to see if approver could have final authority */
427 if ((tempApprover.approval_status is null) or
428 (tempApprover.approval_status in
429 (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
430 ame_util.repeatedStatus, ame_util.suppressedStatus,
431 ame_util.beatByFirstResponderStatus, ame_util.nullStatus)) or
432 (tempApprover.approver_category = ame_util.approvalApproverCategory and
433 tempApprover.approval_status = ame_util.notifiedStatus) )
434 then
435 if(not finalAuthorityFound and
436 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
437 finalAuthorityFound := true;
438 end if;
439 end if;
440 ame_engine.addApprover(approverIn => tempApprover);
441 end if;
442 end loop;
443 /* Decide whether to end the chain. */
444 if(topDogFound or
445 (finalAuthorityFound and
446 not includeAllJobLevelApprovers)) then
447 exit;
448 end if;
449 /* Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
450 if tempSupervisorId is null then
451 raise noSupervisorException;
452 else
453 tempApprover.orig_system_id := tempSupervisorId;
454 end if;
455 tempOldJobLevel := tempJobLevel;
456 if(tempSupervisorJobLevel is null) then
457 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempApprover.orig_system_id,
458 jobLevelOut => tempJobLevel,
459 supervisorIdOut => tempSupervisorId);
460 else
461 tempJobLevel := tempSupervisorJobLevel;
462 tempSupervisorId := tempNextSupervisorId;
463 end if;
464 /*
465 At this point finalAuthorityFound implies includeAllJobLevelApprovers, so the following if
466 doesn't need to check includeAllJobLevelApprovers. But it's implicit in the if statement.
467 */
468 if(finalAuthorityFound and
469 tempOldJobLevel <> tempJobLevel) then
470 exit;
471 end if;
472 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
473 origSystemIn => ame_util.perOrigSystem,
474 origSystemIdIn =>tempApprover.orig_system_id,
475 nameOut => tempApprover.name,
476 displayNameOut => tempApprover.display_name);
477 if firstAuthInsExists then
478 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
479 end if;
480 if coaInsAuthForward then
481 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
482 end if;
483 end loop;
484 exception
485 when noSupervisorException then
486 if tempApprover.display_name is null then
487 personDisplayName := ame_approver_type_pkg.getApproverDisplayName2(
488 origSystemIn => ame_util.perOrigSystem,
489 origSystemIdIn => tempApprover.orig_system_id );
490 else
491 personDisplayName := tempApprover.display_name;
492 end if;
493 errorCode := -20209;
494 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
495 messageNameIn => 'AME_400297_HAN_LACK_SPVR',
496 tokenNameOneIn => 'FIRST_NAME',
497 tokenValueOneIn => personDisplayName,
498 tokenNameTwoIn => 'LAST_NAME',
499 tokenValueTwoIn => null ,
500 tokenNameThreeIn => 'OTHER_NAME',
501 tokenValueThreeIn => null );
502 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
503 routineNameIn => 'handler',
504 exceptionNumberIn => errorCode,
505 exceptionStringIn => errorMessage);
506 raise_application_error(errorCode,
507 errorMessage);
508 when nullFirstIdException then
509 errorCode := -20106;
510 errorMessage :=
511 ame_util.getMessage(applicationShortNameIn => 'PER',
512 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
513 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
514 routineNameIn => 'handler',
515 exceptionNumberIn => errorCode,
516 exceptionStringIn => errorMessage);
517 raise_application_error(errorCode,
518 errorMessage);
519 when topDogRequestorException then
520 errorCode := -20111;
521 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
522 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
523 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
524 routineNameIn => 'handler',
525 exceptionNumberIn => errorCode,
526 exceptionStringIn => errorMessage);
527 raise_application_error(errorCode,
528 errorMessage);
529 when others then
530 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
531 routineNameIn => 'handler',
532 exceptionNumberIn => sqlcode,
533 exceptionStringIn => sqlerrm);
534 l_error_code := sqlcode;
535 if l_error_code = -20213 then
536 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
537 messageNameIn => 'AME_400834_INV_HANDLR_APR',
538 tokenNameOneIn => 'ACTION_TYPE_NAME',
539 tokenValueOneIn => ame_engine.getActionTypeName(tempApprover.action_type_id),
540 tokenNameTwoIn => 'ORIG_SYSTEM',
541 tokenValueTwoIn => ame_util.perOrigSystem,
542 tokenNameThreeIn => 'ORIG_SYSEM_ID',
543 tokenValueThreeIn => tempApprover.orig_system_id);
544 raise_application_error(errorCode,errorMessage);
545 end if;
546 raise;
547 end handler;
548 procedure parseAndSortRules as
549 badParameterException exception;
550 errorCode integer;
551 errorMessage ame_util.longestStringType;
552 tempCategory ame_util.charType;
553 tempLength integer;
554 tempNumber integer;
555 tempRuleId integer;
556 tempSign ame_util.charType;
557 upperLimit integer;
558 begin
559 /* Parse. */
560 for i in 1 .. parametersCount loop
561 tempLength := lengthb(parameters(i));
562 parameterNumbers(i) := threshholdJobLevel + to_number(substrb(parameters(i), 1, tempLength - 1));
563 parameterSigns(i) := substrb(parameters(i), -1, 1);
564 if(parameterSigns(i) <> '+' and
565 parameterSigns(i) <> '-') then
566 raise badParameterException;
567 end if;
568 end loop;
569 /* Sort. */
570 for i in 2 .. parametersCount loop
571 upperLimit := i - 1;
572 for j in 1 .. upperLimit loop
573 if(parameterNumbers(i) < parameterNumbers(j) or
574 (parameterNumbers(i) = parameterNumbers(j) and
575 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
576 tempRuleId := ruleIds(j);
577 tempCategory := approverCategories(j);
578 tempNumber := parameterNumbers(j);
579 tempSign := parameterSigns(j);
580 ruleIds(j) := ruleIds(i);
581 approverCategories(j) := approverCategories(i);
582 parameterNumbers(j) := parameterNumbers(i);
583 parameterSigns(j) := parameterSigns(i);
584 ruleIds(i) := tempRuleId;
585 approverCategories(i) := tempCategory;
586 parameterNumbers(i) := tempNumber;
587 parameterSigns(i) := tempSign;
588 end if;
589 end loop;
590 end loop;
591 exception
592 when badParameterException then
593 errorCode := -20001;
594 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
595 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
596 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
597 routineNameIn => 'parseAndSortRules',
598 exceptionNumberIn => errorCode,
599 exceptionStringIn => errorMessage);
600 raise_application_error(errorCode,
601 errorMessage);
602 when others then
603 ame_util.runtimeException(packageNameIn => 'ame_relative_job_level_handler',
604 routineNameIn => 'parseAndSortRules',
605 exceptionNumberIn => sqlcode,
606 exceptionStringIn => sqlerrm);
607 raise;
608 end parseAndSortRules;
609 end ame_relative_job_level_handler;