[Home] [Help]
PACKAGE BODY: APPS.AME_NON_FINAL_HANDLER
Source
1 package body ame_non_final_handler as
2 /* $Header: ameenfha.pkb 120.6 2011/05/17 11:39:35 nchinnam ship $ */
3 actionParameters ame_util.stringList;
4 allRuleIds ame_util.idList;
5 allRuleIndexes ame_util.idList;
6 currentRuleIdcount integer;
7 listModParameterOnes ame_util.stringList;
8 listModParameterTwos ame_util.longStringList;
9 parametersCount integer;
10 parameterNumbers ame_util.idList;
11 parameterSigns ame_util.charList;
12 parameters ame_util.stringList;
13 ruleIds ame_util.idList;
14 ruleIndexes ame_util.idList;
15 ruleSatisfiedYN ame_util.charList;
16 threshholdJobLevel integer;
17 topDogFound boolean;
18 topDogPersonId integer;
19 /* forward declaration */
20 procedure getSourceAndAuthority(personIdIn in integer,
21 jobLevelIn in integer,
22 supervisorIdIn in integer,
23 sourceOut out nocopy varchar2,
24 hasFinalAuthorityYNOut out nocopy varchar2,
25 supervisorJobLevelOut out nocopy integer,
26 nextSupervisorIdOut out nocopy integer) ;
27 /* getNextTargetApprover, will iterate thru the sorted listModParameterTwos and return
28 all the parameters for the next target approver */
29 procedure getNextTargetApprover;
30 /* This routine will group all the actionParameters, listModParameterTwos and ruleIds
31 based on the listModParameterTwos values.i.e. based on the target approver*/
32 procedure groupRules;
33 procedure parseAndSortRules;
34 /* procedures */
35 /* getNextTargetApprover, will iterate thru the sorted listModParameterTwos and return
36 all the parameters for the next target approver */
37 procedure getNextTargetApprover is
38 rowCount integer;
39 tempInteger integer;
40 begin
41 rowCount := 1 ; /* post increment */
42 if currentRuleIdCount = actionParameters.count then
43 return;
44 end if;
45 currentRuleIdCount := currentRuleIdCount + 1;
46 parameters(rowCount) := actionParameters(currentRuleIdCount);
47 ruleIds(rowCount) := allRuleIds(currentRuleIdCount);
48 ruleIndexes(rowCount) := allRuleIndexes(currentRuleIdCount);
49 tempInteger := currentRuleIdCount + 1;
50 for i in tempInteger.. actionParameters.count loop
51 if listModParameterTwos(i) = listModParameterTwos(currentRuleIdCount) then
52 rowCount := rowCount + 1;
53 currentRuleIdCount := currentRuleIdCount + 1;
54 parameters(rowCount) := actionParameters(currentRuleIdCount);
55 ruleIds(rowCount) := allRuleIds(currentRuleIdCount);
56 ruleIndexes(rowCount) := allRuleIndexes(currentRuleIdCount);
57 else
58 exit;
59 end if;
60 end loop;
61 exception
62 when others then
63 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
64 routineNameIn => 'getNextTargetApprover',
65 exceptionNumberIn => sqlcode,
66 exceptionStringIn => sqlerrm);
67 raise;
68 end getNextTargetApprover;
69 /*
70 getSourceAndAuthority does not account for the ALLOW_REQUESTOR_APPROVAL attribute.
71 The handler procedure does that.
72 */
73 procedure getSourceAndAuthority(personIdIn in integer,
74 jobLevelIn in integer,
75 supervisorIdIn in integer,
76 sourceOut out nocopy varchar2,
77 hasFinalAuthorityYNOut out nocopy varchar2,
78 supervisorJobLevelOut out nocopy integer,
79 nextSupervisorIdOut out nocopy integer) is
80 hasFinalAuthorityYN ame_util.charType;
81 source ame_util.longStringType;
82 supervisorJobLevel integer;
83 tempRuleRequiresApprover boolean;
84 tempRuleSatisfied boolean;
85 begin
86 /* Initialize the two output arguments that might not otherwise get set. */
87 supervisorJobLevelOut := null;
88 nextSupervisorIdOut := null;
89 /*
90 1. An approver satisfies a rule in any of three cases:
91 A. The rule's parameter number does not exceed the approver's job level.
92 B. The rule's parameter sign is '-', and the job level of the approver's
93 supervisor exceeds the rule's parameter number.
94 C. The approver is the top dog.
95 2. An approver has final authority if the approver satisfies all the rules.
96 (The handler procedure proper takes care of adding subsequent approvers at
97 the same job level, if the relevant mandatory attribute so requires.)
98 */
99 hasFinalAuthorityYN := ame_util.booleanTrue;
100 for i in 1 .. parametersCount loop
101 /* Determine whether the approver satisfies the current rule. */
102 if(personIdIn = topDogPersonId) then
103 tempRuleSatisfied := true;
104 topDogFound := true;
105 else
106 topDogFound := false;
107 tempRuleSatisfied := false;
108 if(jobLevelIn >= parameterNumbers(i)) then
109 tempRuleSatisfied := true;
110 elsif(parameterSigns(i) = '-') then
111 if supervisorIdIn is null then
112 supervisorJobLevel := 0;
113 else
114 if(supervisorJobLevel is null) then
115 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => supervisorIdIn,
116 jobLevelOut => supervisorJobLevel,
117 supervisorIdOut => nextSupervisorIdOut);
118 supervisorJobLevelOut := supervisorJobLevel;
119 end if;
120 if(supervisorJobLevel > parameterNumbers(i)) then
121 tempRuleSatisfied := true;
122 end if;
123 end if;
124 end if;
125 end if;
126 /* Update hasFinalAuthorityYN as needed. */
127 if(not tempRuleSatisfied and
128 hasFinalAuthorityYN = ame_util.booleanTrue) then
129 hasFinalAuthorityYN := ame_util.booleanFalse;
130 end if;
131 /* Determine whether the current rule requires the approver. */
132 tempRuleRequiresApprover := false;
133 if(ruleSatisfiedYN(i) = ame_util.booleanTrue) then
134 if(not tempRuleSatisfied) then
135 tempRuleRequiresApprover := true;
136 end if;
137 else
138 tempRuleRequiresApprover := true;
139 if(tempRuleSatisfied) then
140 ruleSatisfiedYN(i) := ame_util.booleanTrue;
141 end if;
142 end if;
143 if(tempRuleRequiresApprover )
144 then
145 /* Update source. */
146 ame_util.appendRuleIdToSource(ruleIdIn => ruleIds(i),
147 sourceInOut => source);
148 end if;
149 end loop;
150 hasFinalAuthorityYNOut := hasFinalAuthorityYN;
151 sourceOut := source;
152 exception
153 when others then
154 hasFinalAuthorityYNOut := null;
155 sourceOut := null;
156 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
157 routineNameIn => 'getSourceAndAuthority',
158 exceptionNumberIn => sqlcode,
159 exceptionStringIn => sqlerrm);
160 raise;
161 end getSourceAndAuthority;
162 procedure groupRules is
163 insertCount integer;
164 lowerLimit integer;
165 tempRuleId integer;
166 tempRuleIndex integer;
167 tempActionParameter ame_util.stringType;
168 tempListModParameterTwo ame_util.stringType;
169 tempListModParameterOne ame_util.stringType;
170 tempParameterCount integer;
171 begin
172 tempParameterCount := listModParameterTwos.count;
173 for i in 1 .. tempParameterCount loop
174 lowerLimit := i + 1;
175 insertCount := i ; /* Pre increment */
176 for j in lowerLimit .. tempParameterCount loop
177 if(listModParameterTwos(i) = listModParameterTwos(j) ) then
178 insertCount := insertCount + 1;
179 if j <> insertCount then
180 tempRuleId := allRuleIds(j);
181 tempRuleIndex := allRuleIndexes(j);
182 tempActionParameter := actionParameters(j);
183 tempListModParameterTwo := listModParameterTwos(j);
184 tempListModParameterOne := listModParameterOnes(j);
185 allRuleIds(j) := allRuleIds(insertCount);
186 allRuleIndexes(j) := allRuleIndexes(insertCount);
187 actionParameters(j) := actionParameters(insertCount);
188 listModParameterTwos(j) := listModParameterTwos(insertCount);
189 listModParameterOnes(j) := listModParameterOnes(insertCount);
190 allRuleIds(insertCount) := tempRuleId;
191 allRuleIndexes(insertCount) := tempRuleIndex;
192 actionParameters(insertCount) := tempActionParameter;
193 listModParameterTwos(insertCount) := tempListModParameterTwo;
194 listModParameterOnes(insertCount) := tempListModParameterOne;
195 end if;
196 end if;
197 end loop;
198 end loop;
199 exception
200 when others then
201 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
202 routineNameIn => 'groupRules',
203 exceptionNumberIn => sqlcode,
204 exceptionStringIn => sqlerrm);
205 raise;
206 end groupRules;
207 procedure handler as
208 badParameterException exception;
209 COAInsertee ame_util.approverRecord2;
210 errorCode integer;
211 errorMessage ame_util.longestStringType;
212 extensionApprovers ame_util.approversTable2;
213 extIndex integer;
214 finalAuthorityNotFound boolean;
215 finalAuthorityFound boolean;
216 finalAuthoritySource ame_util.longStringType;
217 includeAllJobLevelApprovers boolean;
218 insertionThreshhold integer;
219 lastForwardeeIndexes ame_util.idList;
220 noSupervisorException exception;
221 nullFirstIdException exception;
222 requestorId integer;
223 source ame_util.longStringType;
224 startingPointId integer;
225 tempApprovers ame_util.approversTable2;
226 tempApproverIndexes ame_util.idList;
227 tempHasFinalAuthorityYN ame_util.charType;
228 tempIndex integer;
229 tempJobLevel integer;
230 tempLength integer;
231 tempMemberOrderNumber integer;
232 tempOldJobLevel integer;
233 tempSupervisorId integer;
234 tempSupervisorJobLevel integer;
235 tempNextSupervisorId integer;
236 l_error_code number;
237 begin
238 /* Populate some of the package variables. */
239 includeAllJobLevelApprovers := null;
240 topDogPersonId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topSupPersonIdAttribute));
241 ame_engine.getHandlerRules3(ruleIdsOut => allRuleIds,
242 ruleIndexesOut => allRuleIndexes,
243 parametersOut => actionParameters,
244 listModParameterOnesOut => listModParameterOnes,
245 listModParameterTwosOut => listModParameterTwos);
246 /* sort all the rules so that they are grouped by listModParameterTwos. In all these
247 cases the listModParameterOnes should be final_approver, so, ignore this value */
248 groupRules;
249 /* Loop for each distinct listModParameterTwos */
250 currentRuleIdCount := 0; /* pre increment */
251 loop
252 /* initialize arrays */
253 parameters.delete;
254 getNextTargetApprover;
255 if parameters.count = 0 then
256 exit;
257 end if;
258 tempApproverIndexes.delete;
259 insertionThreshhold := 0;
260 ame_engine.getHandlerLMApprovers(listModParameterOneIn => listModParameterOnes(currentRuleIdCount),
261 listModParameterTwoIn => listModParameterTwos(currentRuleIdCount),
262 includeFyiApproversIn => false,
263 includeApprovalGroupsIn => false,
264 returnForwardeesIn => true,
265 approverIndexesOut => tempApproverIndexes,
266 lastForwardeeIndexesOut => lastForwardeeIndexes);
267 for j in 1 .. tempApproverIndexes.count loop
268 finalAuthorityNotFound := true;
269 finalAuthoritySource := null;
270 tempIndex := lastForwardeeIndexes(j) + insertionThreshhold;
271 /* Get the approver list. This should be inside the loop so that we get the latest list*/
272 ame_engine.getApprovers(approversOut => tempApprovers);
273 /* Check that the action type id of the target is based on job level handler */
274 if (tempApprovers(tempIndex).action_type_id =
275 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.absoluteJobLevelTypeName) or
276 tempApprovers(tempIndex).action_type_id =
277 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.relativeJobLevelTypeName) or
278 tempApprovers(tempIndex).action_type_id =
279 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.managerFinalApproverTypeName) or
280 tempApprovers(tempIndex).action_type_id =
281 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.finalApproverOnlyTypeName) or
282 tempApprovers(tempIndex).action_type_id =
283 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.lineItemJobLevelTypeName) or
284 tempApprovers(tempIndex).action_type_id =
285 ame_engine.getActionTypeId(actionTypeNameIn => ame_util.dualChainsAuthorityTypeName) )
286 then
287 if(includeAllJobLevelApprovers is null) then
288 includeAllJobLevelApprovers :=
289 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.includeAllApproversAttribute) =
290 ame_util.booleanAttributeTrue;
291 end if;
292 /* First, get the orig_system_id and job level of the target approver. */
293 if tempApprovers(tempIndex).orig_system_id is null then
294 errorCode :=1;
295 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn =>tempApprovers(tempIndex).name,
296 origSystemOut => tempApprovers(tempIndex).orig_system,
297 origSystemIdOut => tempApprovers(tempIndex).orig_system_id);
298 end if;
299 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
300 personIdIn => tempApprovers(tempIndex).orig_system_id,
301 jobLevelOut => tempJobLevel,
302 supervisorIdOut => tempSupervisorId);
303 threshholdJobLevel := tempJobLevel;
304 parseAndSortRules;
305 for i in 1 .. ruleIds.count loop
306 ruleSatisfiedYN(i) := ame_util.booleanFalse;
307 ame_engine.setRuleApplied(ruleIndexIn => ruleIndexes(i));
308 end loop;
309 /*
310 Third, walk the chain starting with the target and ending with the last forwardee,
311 checking for final authority.
312 */
313 for k in tempApproverIndexes(j) .. lastForwardeeIndexes(j) loop
314 /* no need to check that the approver is in the same chain as the target approver
315 as this is done in the ame_engine.getHandlerLMApprovers() */
316 /* Check whether the approver has sufficient authority as per the
317 actionParameter defined */
318 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
319 personIdIn => tempApprovers(k).orig_system_id,
320 jobLevelOut => tempJobLevel,
321 supervisorIdOut => tempSupervisorId);
322 getSourceAndAuthority(personIdIn => tempApprovers(tempIndex).orig_system_id,
323 jobLevelIn => tempJobLevel,
324 supervisorIdIn => tempSupervisorId,
325 sourceOut => source,
326 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
327 supervisorJobLevelOut => tempSupervisorJobLevel,
328 nextSupervisorIdOut => tempNextSupervisorId);
329 if(tempHasFinalAuthorityYN = ame_util.booleanTrue) then
330 finalAuthorityNotFound := false;
331 exit;
332 end if;
333 end loop;
334 /* If final authority was not found at the previous step, extend the chain,
335 starting from the last forwardee.
336 All the approvers inserted this way will have an approver category of 'A'.
337 */
338 if finalAuthorityNotFound then
339 extensionApprovers.delete;
340 extIndex := 1;
341 finalAuthorityFound := false;
342 tempMemberOrderNumber := tempApprovers(tempIndex).member_order_number; /* pre-increment */
343 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
344 personIdIn => tempApprovers(tempIndex).orig_system_id,
345 jobLevelOut => tempJobLevel,
346 supervisorIdOut => tempSupervisorId);
347 extensionApprovers(extIndex).orig_system_id := tempSupervisorId;
348 while finalAuthorityNotFound loop
349 /* get the next approver to be inserted */
350 ame_absolute_job_level_handler.getJobLevelAndSupervisor(
351 personIdIn => extensionApprovers(extIndex).orig_system_id,
352 jobLevelOut => tempJobLevel,
353 supervisorIdOut => tempSupervisorId);
354 extensionApprovers(extIndex).orig_system := ame_util.perOrigSystem;
355 extensionApprovers(extIndex).authority := ame_util.authorityApprover;
356 -- preserve the action_type_ID and action_type_order_number values of the original chains
357 extensionApprovers(extIndex).action_type_id := tempApprovers(tempIndex).action_type_id;
358 extensionApprovers(extIndex).item_class := tempApprovers(tempIndex).item_class;
359 extensionApprovers(extIndex).item_id := tempApprovers(tempIndex).item_id;
360 extensionApprovers(extIndex).item_class_order_number := tempApprovers(tempIndex).item_class_order_number;
361 extensionApprovers(extIndex).item_order_number := tempApprovers(tempIndex).item_order_number;
362 extensionApprovers(extIndex).sub_list_order_number := tempApprovers(tempIndex).sub_list_order_number;
363 extensionApprovers(extIndex).action_type_order_number := tempApprovers(tempIndex).action_type_order_number;
364 extensionApprovers(extIndex).group_or_chain_order_number := tempApprovers(tempIndex).group_or_chain_order_number;
365 extensionApprovers(extIndex).group_or_chain_id := tempApprovers(tempIndex).group_or_chain_id;
366 extensionApprovers(extIndex).approver_category := ame_util.approvalApproverCategory;
367 extensionApprovers(extIndex).api_insertion := ame_util.oamGenerated;
368 getSourceAndAuthority(personIdIn => extensionApprovers(extIndex).orig_system_id,
369 jobLevelIn => tempJobLevel,
370 supervisorIdIn => tempSupervisorId,
371 sourceOut => extensionApprovers(extIndex).source,
372 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
373 supervisorJobLevelOut => tempSupervisorJobLevel,
374 nextSupervisorIdOut => tempNextSupervisorId);
375 if(not finalAuthorityFound and
376 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
377 finalAuthorityFound := true;
378 finalAuthoritySource := extensionApprovers(extIndex).source;
379 end if;
380 if (extensionApprovers(extIndex).source is null and
381 finalAuthoritySource is not null ) then
382 extensionApprovers(extIndex).source := finalAuthoritySource;
383 end if;
384 errorCode := 2;
385 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.perOrigSystem,
386 origSystemIdIn => extensionApprovers(extIndex).orig_system_id,
387 nameOut => extensionApprovers(extIndex).name,
388 displayNameOut => extensionApprovers(extIndex).display_name);
389 extensionApprovers(extIndex).occurrence := ame_engine.getHandlerOccurrence(
390 nameIn =>extensionApprovers(extIndex).name,
391 itemClassIn => extensionApprovers(extIndex).item_class,
392 itemIdIn => extensionApprovers(extIndex).item_id,
393 actionTypeIdIn => extensionApprovers(extIndex).action_type_id,
394 groupOrChainIdIn => extensionApprovers(extIndex).group_or_chain_id);
395 tempMemberOrderNumber := tempMemberOrderNumber + 1;
396 extensionApprovers(extIndex).member_order_number := tempMemberOrderNumber;
397 extensionApprovers(extIndex).approval_status := ame_engine.getHandlerApprovalStatus(
398 approverIn => extensionApprovers(extIndex));
399 /* The engine will set extensionApprovers(extIndex).approver_order_number; leave it null here. */
400 /* check to see if there is a COA insertion after this approver. If a COA
401 insertion is found, keep checking till no more COA insertions. The check
402 for final authority will need to be done again. */
403 loop
404 /* Initialize COAInsertee approverRecord2 */
405 COAInsertee := ame_util.emptyApproverRecord2;
406 /* Check if there are any COAInsertions */
407 ame_engine.getHandlerCOAInsertion(nameIn => extensionApprovers(extIndex).name,
408 itemClassIn => extensionApprovers(extIndex).item_class,
409 itemIdIn => extensionApprovers(extIndex).item_id,
410 actionTypeIdIn => extensionApprovers(extIndex).action_type_id,
411 groupOrChainIdIn => extensionApprovers(extIndex).group_or_chain_id,
412 occurrenceIn => extensionApprovers(extIndex).occurrence,
413 approvalStatusIn => extensionApprovers(extIndex).approval_status,
414 nameOut => COAInsertee.name,
415 origSystemOut => COAInsertee.orig_system,
416 origSystemIdOut => COAInsertee.orig_system_id,
417 displayNameOut => COAInsertee.display_name,
418 sourceOut => COAInsertee.source);
419 if COAInsertee.name is null then
420 exit;
421 else
422 extIndex := extIndex + 1;
423 extensionApprovers(extIndex).name := COAInsertee.name;
424 extensionApprovers(extIndex).orig_system := COAInsertee.orig_system;
425 extensionApprovers(extIndex).orig_system_id := COAInsertee.orig_system_id;
426 extensionApprovers(extIndex).display_name := COAInsertee.display_name;
427 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => extensionApprovers(extIndex).orig_system_id,
428 jobLevelOut => tempJobLevel,
429 supervisorIdOut => tempSupervisorId);
430 extensionApprovers(extIndex).orig_system_id := tempSupervisorId;
431 extensionApprovers(extIndex).orig_system := ame_util.perOrigSystem;
432 extensionApprovers(extIndex).authority := ame_util.authorityApprover;
433 extensionApprovers(extIndex).action_type_id := ame_engine.getHandlerActionTypeId;
434 extensionApprovers(extIndex).item_class := tempApprovers(tempIndex).item_class;
435 extensionApprovers(extIndex).item_id := tempApprovers(tempIndex).item_id;
436 extensionApprovers(extIndex).item_class_order_number := tempApprovers(tempIndex).item_class_order_number;
437 extensionApprovers(extIndex).item_order_number := tempApprovers(tempIndex).item_order_number;
438 extensionApprovers(extIndex).sub_list_order_number := tempApprovers(tempIndex).sub_list_order_number;
439 extensionApprovers(extIndex).action_type_order_number := tempApprovers(tempIndex).action_type_order_number;
440 extensionApprovers(extIndex).group_or_chain_order_number := tempApprovers(tempIndex).group_or_chain_order_number;
441 extensionApprovers(extIndex).group_or_chain_id := tempApprovers(tempIndex).group_or_chain_id;
442 getSourceAndAuthority(personIdIn => extensionApprovers(extIndex).orig_system_id,
443 jobLevelIn => tempJobLevel,
444 supervisorIdIn => tempSupervisorId,
445 sourceOut => extensionApprovers(extIndex).source,
446 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN,
447 supervisorJobLevelOut => tempSupervisorJobLevel,
448 nextSupervisorIdOut => tempNextSupervisorId);
449 extensionApprovers(extIndex).source := COAInsertee.source;
450 extensionApprovers(extIndex).api_insertion := ame_util.apiAuthorityInsertion;
451 tempMemberOrderNumber := tempMemberOrderNumber + 1;
452 extensionApprovers(extIndex).member_order_number := tempMemberOrderNumber;
453 extensionApprovers(extIndex).occurrence := ame_engine.getHandlerOccurrence(
454 nameIn => extensionApprovers(extIndex).name,
455 itemClassIn => extensionApprovers(extIndex).item_class,
456 itemIdIn => extensionApprovers(extIndex).item_id,
457 actionTypeIdIn => extensionApprovers(extIndex).action_type_id,
458 groupOrChainIdIn => extensionApprovers(extIndex).group_or_chain_id);
459 extensionApprovers(extIndex).approval_status :=
460 ame_engine.getHandlerApprovalStatus(approverIn => extensionApprovers(extIndex));
461 /* If approver has a status of ame_util.approve or
462 ame_util.approveAndForwardStatus or ame_util.nullStatus check to see
463 if approver could have final authority */
464 if extensionApprovers(extIndex).approval_status in (ame_util.approvedStatus, ame_util.approveAndForwardStatus,
465 ame_util.nullStatus)
466 then
467 if(not finalAuthorityFound and
468 tempHasFinalAuthorityYN = ame_util.booleanTrue) then
469 finalAuthorityFound := true;
470 tempOldJobLevel := tempJobLevel;
471 end if;
472 end if;
473 extIndex := extIndex + 1;
474 end if;
475 end loop;
476 /* Decide whether to end the chain. */
477 if(topDogFound or
478 (finalAuthorityFound and
479 not includeAllJobLevelApprovers)) then
480 finalAuthorityNotFound := false;
481 exit;
482 end if;
483 tempOldJobLevel := tempJobLevel;
484 /* Check to make sure tempSupervisorId is not null, else raise noSupervisorException */
485 if tempSupervisorId is null then
486 raise noSupervisorException;
487 end if;
488 if(tempSupervisorJobLevel is null) then
489 ame_absolute_job_level_handler.getJobLevelAndSupervisor(personIdIn => tempSupervisorId,
490 jobLevelOut => tempSupervisorJobLevel,
491 supervisorIdOut => tempNextSupervisorId);
492 end if;
493 /*
494 At this point finalAuthorityFound implies includeAllJobLevelApprovers, so the following if
495 doesn't need to check includeAllJobLevelApprovers. But it's implicit in the if statement.
496 */
497 if(finalAuthorityFound and
498 tempOldJobLevel < tempSupervisorJobLevel) then
499 finalAuthorityNotFound := false;
500 exit;
501 end if;
502 extIndex := extIndex + 1;
503 extensionApprovers(extIndex).orig_system_id := tempSupervisorId;
504 tempJobLevel := tempSupervisorJobLevel;
505 tempSupervisorId := tempNextSupervisorId;
506 end loop;
507 ame_engine.insertApprovers(firstIndexIn => lastForwardeeIndexes(j)+ insertionThreshhold + 1,
508 approversIn => extensionApprovers);
509 insertionThreshhold := insertionThreshhold + extensionApprovers.count;
510 end if;
511 end if; /* This if corresponds to the check that action type id is valid */
512 end loop;
513 end loop;
514 exception
515 when badParameterException then
516 errorCode := -20001;
517 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
518 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
519 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
520 routineNameIn => 'handler',
521 exceptionNumberIn => errorCode,
522 exceptionStringIn => errorMessage);
523 raise_application_error(errorCode,
524 errorMessage);
525 when noSupervisorException then
526 errorCode := -20105;
527 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
528 messageNameIn => 'AME_400232_HAN_NO_SUPVSR_ID',
529 tokenNameOneIn => 'PERSON_ID',
530 tokenValueOneIn =>extensionApprovers(extIndex).orig_system_id);
531 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
532 routineNameIn => 'handler',
533 exceptionNumberIn => errorCode,
534 exceptionStringIn => errorMessage);
535 raise_application_error(errorCode,
536 errorMessage);
537 when nullFirstIdException then
538 errorCode := -20106;
539 errorMessage :=
540 ame_util.getMessage(applicationShortNameIn => 'PER',
541 messageNameIn => 'AME_400233_HAN_NO_TRANS_PER_ID');
542 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
543 routineNameIn => 'handler',
544 exceptionNumberIn => errorCode,
545 exceptionStringIn => errorMessage);
546 raise_application_error(errorCode,
547 errorMessage);
548 when others then
549 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
550 routineNameIn => 'handler',
551 exceptionNumberIn => sqlcode,
552 exceptionStringIn => sqlerrm);
553 l_error_code := sqlcode;
554 if l_error_code = -20213 then
555 if errorCode = 1 then
556 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
557 messageNameIn => 'AME_400833_INV_HANDLR_APR',
558 tokenNameOneIn => 'ACTION_TYPE_NAME',
559 tokenValueOneIn => ame_engine.getActionTypeId(actionTypeNameIn => ame_util.nonFinalAuthority),
560 tokenNameTwoIn => 'NAME',
561 tokenValueTwoIn => tempApprovers(tempIndex).name);
562 end if;
563 if errorCode = 2 then
564 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
565 messageNameIn => 'AME_400834_INV_HANDLR_APR',
566 tokenNameOneIn => 'ACTION_TYPE_NAME',
567 tokenValueOneIn => ame_engine.getActionTypeId(actionTypeNameIn => ame_util.nonFinalAuthority),
568 tokenNameTwoIn => 'ORIG_SYSTEM',
569 tokenValueTwoIn => ame_util.perOrigSystem,
570 tokenNameThreeIn => 'ORIG_SYSEM_ID',
571 tokenValueThreeIn => extensionApprovers(extIndex).orig_system_id);
572 end if;
573 errorCode := -20244;
574
575 raise_application_error(errorCode,errorMessage);
576 end if;
577 if l_error_code = -20204 then
578 errorCode := -20105;
579 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
580 messageNameIn => 'AME_400232_HAN_NO_SUPVSR_ID',
581 tokenNameOneIn => 'PERSON_ID',
582 tokenValueOneIn =>extensionApprovers(extIndex).orig_system_id);
583 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
584 routineNameIn => 'handler',
585 exceptionNumberIn => errorCode,
586 exceptionStringIn => errorMessage);
587 raise_application_error(errorCode,
588 errorMessage);
589 end if;
590 raise;
591 end handler;
592 procedure parseAndSortRules is
593 badParameterException exception;
594 errorCode integer;
595 errorMessage ame_util.longestStringType;
596 tempCategory ame_util.charType;
597 tempLength integer;
598 tempNumber integer;
599 tempRuleId integer;
600 tempRuleIndex integer;
601 tempSign ame_util.charType;
602 upperLimit integer;
603 begin
604 parametersCount := parameters.count;
605 /* Parse. */
606 for i in 1 .. parametersCount loop
607 tempLength := lengthb(parameters(i));
608 if(substrb(parameters(i), 1, 1) = 'R') then
609 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) +
610 threshholdJobLevel;
611 parameterSigns(i) := substrb(parameters(i), -1, 1);
612 else
613 parameterNumbers(i) := to_number(substrb(parameters(i), 2, tempLength - 2)) ;
614 parameterSigns(i) := substrb(parameters(i), -1, 1);
615 end if;
616 if(parameterSigns(i) <> '+' and
617 parameterSigns(i) <> '-') then
618 raise badParameterException;
619 end if;
620 end loop;
621 /* Sort. */
622 for i in 2 .. parametersCount loop
623 upperLimit := i - 1;
624 for j in 1 .. upperLimit loop
625 if(parameterNumbers(i) < parameterNumbers(j) or
626 (parameterNumbers(i) = parameterNumbers(j) and
627 parameterSigns(i) = '-' and parameterSigns(j) = '+')) then
628 tempRuleId := ruleIds(j);
629 tempRuleIndex := ruleIndexes(j);
630 tempNumber := parameterNumbers(j);
631 tempSign := parameterSigns(j);
632 ruleIds(j) := ruleIds(i);
633 ruleIndexes(j) := ruleIndexes(i);
634 parameterNumbers(j) := parameterNumbers(i);
635 parameterSigns(j) := parameterSigns(i);
636 ruleIds(i) := tempRuleId;
637 ruleIndexes(i) := tempRuleIndex;
638 parameterNumbers(i) := tempNumber;
639 parameterSigns(i) := tempSign;
640 end if;
641 end loop;
642 end loop;
643 exception
644 when badParameterException then
645 errorCode := -20001;
646 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
647 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
648 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
649 routineNameIn => 'parseAndSortRules',
650 exceptionNumberIn => errorCode,
651 exceptionStringIn => errorMessage);
652 raise_application_error(errorCode,
653 errorMessage);
654 when others then
655 ame_util.runtimeException(packageNameIn => 'ame_non_final_handler',
656 routineNameIn => 'parseAndSortRules',
657 exceptionNumberIn => sqlcode,
658 exceptionStringIn => sqlerrm);
659 raise;
660 end parseAndSortRules ;
661 end ame_non_final_handler;