[Home] [Help]
PACKAGE BODY: APPS.AME_POSITION_LEVEL_HANDLER
Source
1 package body ame_position_level_handler as
2 /* $Header: ameeplha.pkb 120.5 2007/12/20 20:03:41 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 topDogPositionId integer;
12 currentPositionLevel integer;
13 positionStructureId integer := null;
14 /* forward declarations */
15 function getPositionStructureId return integer;
16 /*
17 getCatSourceAndAuthority does not account for the ALLOW_REQUESTOR_APPROVAL attribute.
18 The handler procedure does that.
19 */
20 procedure getCatSourceAndAuthority(positionIdIn in integer,
21 categoryOut out nocopy varchar2,
22 sourceOut out nocopy varchar2,
23 hasFinalAuthorityYNOut out nocopy varchar2);
24 /*
25 parseAndSortRules populates the parameterNumbers and parameterSigns tables in
26 ascending lexicographic order.
27 */
28 procedure parseAndSortRules;
29 /* Functions */
30 function getNextPosition(positionIdIn in integer) return integer as
31 cursor positionCursor(positionIdIn in integer) is
32 select str.parent_position_id
33 from
34 per_pos_structure_elements str,
35 per_pos_structure_versions psv,
36 per_position_structures pst
37 where
38 str.subordinate_position_id = positionIdIn
39 and str.pos_structure_version_id = psv.pos_structure_version_id
40 and pst.position_structure_id = psv.position_structure_id
41 and pst.primary_position_flag = 'Y'
42 and trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate);
43 cursor positionCursor2(positionIdIn in integer,posStrIdIn in integer) is
44 select str.parent_position_id
45 from
46 per_pos_structure_elements str,
47 per_pos_structure_versions psv,
48 per_position_structures pst
49 where
50 str.subordinate_position_id = positionIdIn
51 and str.pos_structure_version_id = psv.pos_structure_version_id
52 and psv.position_structure_id = pst.position_structure_id
53 and pst.position_structure_id = posStrIdIn
54 and trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate);
55 approverDescription ame_util.longStringType;
56 approverName wf_roles.name%type;
57 errorCode integer;
58 errorMessage ame_util.longestStringType;
59 nextPositionId integer;
60 posStrId integer;
61 nullIdException exception;
62 begin
63 posStrId := getPositionStructureId;
64 if posStrId is null then
65 open positionCursor(positionIdIn => positionIdIn);
66 fetch positionCursor into nextPositionId;
67 if(positionCursor%notfound) then
68 raise nullIdException;
69 end if;
70 close positionCursor;
71 else
72 open positionCursor2(positionIdIn => positionIdIn,posStrIdIn => posStrId);
73 fetch positionCursor2 into nextPositionId;
74 if(positionCursor2%notfound) then
75 raise nullIdException;
76 end if;
77 close positionCursor2;
78 end if;
79 return(nextPositionId);
80 exception
81 when nullIdException then
82 errorCode := -20001;
83 approverName := ame_approver_type_pkg.getWfRolesName(
84 origSystemIn => ame_util.posOrigSystem,
85 origSystemIdIn => positionIdIn);
86 approverDescription :=
87 ame_approver_type_pkg.getApproverDescription(nameIn => approverName);
88 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
89 messageNameIn => 'AME_400407_NO_PARENT_POSITION',
90 tokenNameOneIn => 'POSITION',
91 tokenValueOneIn => substrb(approverDescription, 1,70));
92 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
93 routineNameIn => 'getNextPosition',
94 exceptionNumberIn => errorCode,
95 exceptionStringIn => errorMessage);
96 raise_application_error(errorCode,
97 errorMessage);
98 return(null);
99 when others then
100 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
101 routineNameIn => 'getNextPosition',
102 exceptionNumberIn => sqlcode,
103 exceptionStringIn => sqlerrm);
104 raise;
105 return(null);
106 end getNextPosition;
107 --
108 function getPositionStructureId return integer as
109 begin
110 positionStructureId :=
111 ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
112 return positionStructureId;
113 end getPositionStructureId;
114 /* Procedures */
115 procedure getCatSourceAndAuthority(positionIdIn in integer,
116 categoryOut out nocopy varchar2,
117 sourceOut out nocopy varchar2,
118 hasFinalAuthorityYNOut out nocopy varchar2) as
119 category ame_util.charType;
120 hasFinalAuthorityYN ame_util.charType;
121 source ame_util.longStringType;
122 tempRuleRequiresApprover boolean;
123 tempRuleSatisfied boolean;
124 begin
125 /*
126 1. An approver satisfies a rule in any of three cases:
127 A. The rule's parameter number does not exceed the currentPositionLevel.
128 B. The approver is the top dog and parameter number does exceed
129 the currentPositionLevel
130 2. An approver has final authority if the approver satisfies all the rules.
131 3. The source value is an ame_util.fieldDelimiter-delimited list of the IDs
132 of the rules that require an approver. This procedure builds up the
133 source value according to the following logic:
134 A. If a rule has not yet been satisfied, the rule requires the input
135 approver.
136 4. An approver's category is ame_util.approvalApproverCategory if any of the
137 rule usages requiring the approver is of that category; otherwise the
138 approver's category is ame_util.fyiApproverCategory.
139 */
140 if currentPositionLevel is null then
141 currentPositionLevel := 1;
142 else
143 currentPositionLevel := currentPositionLevel + 1;
144 end if;
145 category := ame_util.fyiApproverCategory;
146 hasFinalAuthorityYN := ame_util.booleanTrue;
147 for i in 1 .. parametersCount loop
148 /* if the rule is satisfied already no need to process again. */
149 if(ruleSatisfiedYN(i) = ame_util.booleanFalse) then
150 --
151 -- Determine whether the approver satisfies the current rule.
152 --
153 if(positionIdIn = topDogPositionId) then
154 tempRuleSatisfied := true;
155 else
156 tempRuleSatisfied := false;
157 if(currentPositionLevel >= parameterNumbers(i)) then
158 tempRuleSatisfied := true;
159 end if;
160 end if;
161 --
162 -- Update hasFinalAuthorityYN as needed.
163 --
164 if(not tempRuleSatisfied and
165 hasFinalAuthorityYN = ame_util.booleanTrue) then
166 hasFinalAuthorityYN := ame_util.booleanFalse;
167 end if;
168 --
169 -- Determine whether the current rule requires the approver.
170 --
171 if(tempRuleSatisfied) then
172 ruleSatisfiedYN(i) := ame_util.booleanTrue;
173 end if;
174 --
175 -- Update source. */
176 --
177 ame_util.appendRuleIdToSource(ruleIdIn => ruleIds(i),
178 sourceInOut => source);
179 --
180 -- Update category as needed.
181 --
182 if(category = ame_util.fyiApproverCategory and
183 approverCategories(i) = ame_util.approvalApproverCategory) then
184 category := ame_util.approvalApproverCategory;
185 end if;
186 end if;
187 end loop;
188 categoryOut := category;
189 hasFinalAuthorityYNOut := hasFinalAuthorityYN;
190 sourceOut := source;
191 exception
192 when others then
193 categoryOut := null;
194 hasFinalAuthorityYNOut := null;
195 sourceOut := null;
196 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
197 routineNameIn => 'getCatSourceAndAuthority',
198 exceptionNumberIn => sqlcode,
199 exceptionStringIn => sqlerrm);
200 raise;
201 end getCatSourceAndAuthority;
202 procedure handler as
203 COAInsertee ame_util.approverRecord2;
204 errorCode integer;
205 errorMessage ame_util.longestStringType;
206 finalAuthorityFound boolean;
207 firstApproverSource ame_util.longStringType;
208 nullFirstIdException exception;
209 requestorId integer;
210 startingPointId integer;
211 tempApprover ame_util.approverRecord2;
212 tempHasFinalAuthorityYN ame_util.charType;
213 tempMemberOrderNumber integer;
214 topDogRequestorException exception;
215 votingRegimeType ame_util.stringType;
216 firstAuthInsExists boolean := false;
217 coaInsAuthForward boolean := false;
218 begin
219 /*
220 The engine only calls a handler if a rule requiring it exists, so we can assume that
221 the package variables that ame_engine.getHandlerRules2 initializes are nonempty.
222 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
223 are harmless here.)
224 */
225 currentPositionLevel := 0;
226 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
227 approverCategoriesOut => approverCategories,
228 parametersOut => parameters);
229 /* Populate some of the package variables. */
230 topDogPositionId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topPositionIdAttribute));
231 parametersCount := parameters.count;
232 parseAndSortRules;
233 for i in 1 .. ruleIds.count loop
234 ruleSatisfiedYN(i) := ame_util.booleanFalse;
235 end loop;
236 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
237 tempApprover.orig_system := ame_util.posOrigSystem;
238 tempApprover.authority := ame_util.authorityApprover;
239 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
240 tempApprover.item_class := ame_engine.getHandlerItemClassName;
241 tempApprover.item_id := ame_engine.getHandlerItemId;
242 tempApprover.group_or_chain_id := 1;
243 --
244 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
245 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
246 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
247 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
248 tempApprover.group_or_chain_order_number := 1;
249 /* Fetch some of the required attributes. */
250 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
251 /* Check for COA Insertions */
252 ame_engine.getHandlerCOAFirstApprover(itemClassIn => tempApprover.item_class,
253 itemIdIn => tempApprover.item_id,
254 actionTypeIdIn => tempApprover.action_type_id,
255 groupOrChainIdIn => tempApprover.group_or_chain_id,
256 nameOut => COAInsertee.name,
257 origSystemOut => COAInsertee.orig_system,
258 origSystemIdOut => COAInsertee.orig_system_id,
259 displayNameOut => COAInsertee.display_name,
260 sourceOut => COAInsertee.source);
261 /* Start building the chain from the COA Insertee if defined otherwise from the
262 non-default starting point or the requestor's supervisor. */
263 if COAInsertee.name is null then
264 /* Fetch some of the required attributes. */
265 startingPointId :=
266 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.nonDefStartingPointPosAttr));
267 if(startingPointId is null) then
268 requestorId :=
269 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.transactionReqPositionAttr));
270 if (requestorId is null) then
271 raise nullFirstIdException;
272 end if;
273 if topDogPositionId = requestorId then
274 tempApprover.orig_system_id := requestorId;
275 /* Check if requestor can self approve. If so, insert the approver as the
276 only approver, with a status of approved, and return.*/
277 if(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
278 = ame_util.booleanAttributeTrue)
279 then
280 getCatSourceAndAuthority(positionIdIn => requestorId,
281 categoryOut => tempApprover.approver_category,
282 sourceOut => tempApprover.source,
283 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
284 tempApprover.api_insertion := ame_util.oamGenerated;
285 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
286 origSystemIn => ame_util.posOrigSystem,
287 origSystemIdIn => requestorId,
288 nameOut => tempApprover.name,
289 displayNameOut => tempApprover.display_name);
290 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
291 nameIn => tempApprover.name,
292 itemClassIn => tempApprover.item_class,
293 itemIdIn => tempApprover.item_id,
294 actionTypeIdIn => tempApprover.action_type_id,
295 groupOrChainIdIn => tempApprover.group_or_chain_id);
296 tempApprover.member_order_number := 1;
297 tempApprover.approval_status := ame_util.approvedStatus;
298 ame_engine.addApprover(approverIn => tempApprover);
299 return;
300 else
301 /* The requestor is the top position but he can not auto approve.
302 hence raise appropriate exception*/
303 raise topDogRequestorException;
304 end if;
305 end if;
306 /* The requestor could not self-approve, either because there was a non-default
307 starting point, or because the requestor lacked sufficient authority. So,
308 start building the chain from the non-default starting point or the requestor's
309 parent position. */
310 tempApprover.orig_system_id := getNextPosition(positionIdIn => requestorId);
311 else
312 tempApprover.orig_system_id := startingPointId;
313 end if;
314 tempApprover.api_insertion := ame_util.oamGenerated;
315 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.posOrigSystem,
316 origSystemIdIn => tempApprover.orig_system_id,
317 nameOut => tempApprover.name,
318 displayNameOut => tempApprover.display_name);
319 else
320 firstAuthInsExists := true;
321 tempApprover.name := COAInsertee.name;
322 tempApprover.orig_system := COAInsertee.orig_system;
323 tempApprover.orig_system_id := COAInsertee.orig_system_id;
324 tempApprover.display_name := COAInsertee.display_name;
325 firstApproverSource := COAInsertee.source;
326 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
327 end if;
328 /* Build the chain. */
329 currentPositionLevel := 0;
330 tempMemberOrderNumber := 0; /* pre-increment */
331 loop
332 getCatSourceAndAuthority(positionIdIn => tempApprover.orig_system_id,
333 categoryOut => tempApprover.approver_category,
334 sourceOut => tempApprover.source,
335 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
336 if firstApproverSource is not null then
337 tempApprover.source := firstApproverSource;
338 firstApproverSource := null;
339 end if;
340 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
341 nameIn => tempApprover.name,
342 itemClassIn => tempApprover.item_class,
343 itemIdIn => tempApprover.item_id,
344 actionTypeIdIn => tempApprover.action_type_id,
345 groupOrChainIdIn => tempApprover.group_or_chain_id);
346 tempMemberOrderNumber := tempMemberOrderNumber + 1;
347 if(votingRegimeType = ame_util.serializedVoting) then
348 tempApprover.member_order_number := tempMemberOrderNumber;
349 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
350 tempApprover.member_order_number := 1;
351 end if;
352 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
353 /* The engine will set tempApprover.approver_order_number; leave it null here. */
354 ame_engine.addApprover(approverIn => tempApprover);
355 /* check to see if there is a COA insertion after this approver. If a COA
356 insertion is found, keep checking till no more COA insertions. The check
357 for final authority will not be needed (similar to supervisory handler). */
358 loop
359 /* Initialize COAInsertee approverRecord2 */
360 COAInsertee := ame_util.emptyApproverRecord2;
361 /* Check if there are any COAInsertions */
362 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
363 itemClassIn => tempApprover.item_class,
364 itemIdIn => tempApprover.item_id,
365 actionTypeIdIn => tempApprover.action_type_id,
366 groupOrChainIdIn => tempApprover.group_or_chain_id,
367 occurrenceIn => tempApprover.occurrence,
368 approvalStatusIn => tempApprover.approval_status,
369 nameOut => COAInsertee.name,
370 origSystemOut => COAInsertee.orig_system,
371 origSystemIdOut => COAInsertee.orig_system_id,
372 displayNameOut => COAInsertee.display_name,
373 sourceOut => COAInsertee.source);
374 if COAInsertee.name is null then
375 exit;
376 else
377 coaInsAuthForward := true;
378 tempApprover.name := COAInsertee.name;
379 tempApprover.orig_system := COAInsertee.orig_system;
380 tempApprover.orig_system_id := COAInsertee.orig_system_id;
381 tempApprover.display_name := COAInsertee.display_name;
382 getCatSourceAndAuthority(positionIdIn => tempApprover.orig_system_id,
383 categoryOut => tempApprover.approver_category,
384 sourceOut => tempApprover.source,
385 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
386 tempApprover.source := COAInsertee.source;
387 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
388 tempMemberOrderNumber := tempMemberOrderNumber + 1;
389 if(votingRegimeType = ame_util.serializedVoting) then
390 tempApprover.member_order_number := tempMemberOrderNumber;
391 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
392 tempApprover.member_order_number := 1;
393 end if;
394 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
395 itemClassIn => tempApprover.item_class,
396 itemIdIn => tempApprover.item_id,
397 actionTypeIdIn => tempApprover.action_type_id,
398 groupOrChainIdIn => tempApprover.group_or_chain_id);
399 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn =>
400 tempApprover);
401 ame_engine.addApprover(approverIn => tempApprover);
402 end if;
403 end loop;
404 /* Decide whether to end the chain. */
405 if(tempHasFinalAuthorityYN = ame_util.booleanTrue ) then
406 exit;
407 end if;
408 tempApprover.orig_system_id := getNextPosition(positionIdIn =>tempApprover.orig_system_id );
409 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.posOrigSystem,
410 origSystemIdIn => tempApprover.orig_system_id,
411 nameOut => tempApprover.name,
412 displayNameOut => tempApprover.display_name);
413 if firstAuthInsExists then
414 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
415 end if;
416 if coaInsAuthForward then
417 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
418 end if;
419 tempApprover.api_insertion := ame_util.oamGenerated;
420 end loop;
421 exception
422 when nullFirstIdException then
423 errorCode := -20001;
424 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
425 messageNameIn => 'AME_400408_HAN_NO_TRANS_POS_ID');
426 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
427 routineNameIn => 'handler',
428 exceptionNumberIn => errorCode,
429 exceptionStringIn => errorMessage);
430 raise_application_error(errorCode,
431 errorMessage);
432 when topDogRequestorException then
433 errorCode := -20001;
434 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
435 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
436 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
437 routineNameIn => 'handler',
438 exceptionNumberIn => errorCode,
439 exceptionStringIn => errorMessage);
440 raise_application_error(errorCode,
441 errorMessage);
442 when others then
443 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
444 routineNameIn => 'handler',
445 exceptionNumberIn => sqlcode,
446 exceptionStringIn => sqlerrm);
447 raise;
448 end handler;
449 procedure parseAndSortRules as
450 badParameterException exception;
451 errorCode integer;
452 errorMessage ame_util.longestStringType;
453 signPosition integer;
454 tempCategory ame_util.charType;
455 tempLength integer;
456 tempNumber integer;
457 tempRuleId integer;
458 tempSign ame_util.charType;
459 tempParameter ame_util.parameterType;
460 upperLimit integer;
461 begin
462 /* Parse. */
463 for i in 1 .. parametersCount loop
464 signPosition := instrb(parameters(i), '+');
465 tempLength := lengthb(parameters(i));
466 if signPosition = 0 then
467 signPosition := instrb(parameters(i), '-');
468 if signPosition = 0 then
469 parameterSigns(i) := '+';
470 parameterNumbers(i) := to_number(parameters(i));
471 else
472 parameterSigns(i) := substrb(parameters(i), tempLength, tempLength);
473 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
474 end if;
475 else
476 parameterSigns(i) := substrb(parameters(i), tempLength, tempLength);
477 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
478 end if;
479 if(parameterSigns(i) <> '+' ) then
480 raise badParameterException;
481 end if;
482 end loop;
483 /* Sort. */
484 for i in 2 .. parametersCount loop
485 upperLimit := i - 1;
486 for j in 1 .. upperLimit loop
487 if(parameterNumbers(i) < parameterNumbers(j) ) then
488 tempRuleId := ruleIds(j);
489 tempCategory := approverCategories(j);
490 tempNumber := parameterNumbers(j);
491 tempSign := parameterSigns(j);
492 tempParameter := parameters(j);
493 --
494 ruleIds(j) := ruleIds(i);
495 approverCategories(j) := approverCategories(i);
496 parameterNumbers(j) := parameterNumbers(i);
497 parameterSigns(j) := parameterSigns(i);
498 parameters(j) := parameters(i);
499 --
500 ruleIds(i) := tempRuleId;
501 approverCategories(i) := tempCategory;
502 parameterNumbers(i) := tempNumber;
503 parameterSigns(i) := tempSign;
504 parameters(i) := tempParameter;
505 end if;
506 end loop;
507 end loop;
508 exception
509 when badParameterException then
510 errorCode := -20001;
511 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
512 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
513 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
514 routineNameIn => 'parseAndSortRules',
515 exceptionNumberIn => errorCode,
516 exceptionStringIn => errorMessage);
517 raise_application_error(errorCode,
518 errorMessage);
519 when others then
520 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
521 routineNameIn => 'parseAndSortRules',
522 exceptionNumberIn => sqlcode,
523 exceptionStringIn => sqlerrm);
524 raise;
525 end parseAndSortRules;
526 end ame_position_level_handler;