[Home] [Help]
PACKAGE BODY: APPS.AME_POSITION_LEVEL_HANDLER
Source
1 package body ame_position_level_handler as
2 /* $Header: ameeplha.pkb 120.10 2011/05/17 11:39:54 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 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 := -20110;
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 l_error_code number;
219 begin
220 /*
221 The engine only calls a handler if a rule requiring it exists, so we can assume that
222 the package variables that ame_engine.getHandlerRules2 initializes are nonempty.
223 Fetch the rules and sort them in increasing parameter order. (Duplicate parameters
224 are harmless here.)
225 */
226 errorCode := -20240;
227 currentPositionLevel := 0;
228 ame_engine.getHandlerRules2(ruleIdsOut => ruleIds,
229 approverCategoriesOut => approverCategories,
230 parametersOut => parameters);
231 /* Populate some of the package variables. */
232 topDogPositionId := to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.topPositionIdAttribute));
233 parametersCount := parameters.count;
234 parseAndSortRules;
235 for i in 1 .. ruleIds.count loop
236 ruleSatisfiedYN(i) := ame_util.booleanFalse;
237 end loop;
238 /* Set the fields in tempApprover that are constant for the entire handler cycle. */
239 tempApprover.orig_system := ame_util.posOrigSystem;
240 tempApprover.authority := ame_util.authorityApprover;
241 tempApprover.action_type_id := ame_engine.getHandlerActionTypeId;
242 tempApprover.item_class := ame_engine.getHandlerItemClassName;
243 tempApprover.item_id := ame_engine.getHandlerItemId;
244 tempApprover.group_or_chain_id := 1;
245 --
246 tempApprover.item_class_order_number := ame_engine.getHandlerItemClassOrderNumber;
247 tempApprover.item_order_number := ame_engine.getHandlerItemOrderNumber;
248 tempApprover.sub_list_order_number := ame_engine.getHandlerSublistOrderNum;
249 tempApprover.action_type_order_number := ame_engine.getHandlerActionTypeOrderNum;
250 tempApprover.group_or_chain_order_number := 1;
251 /* Fetch some of the required attributes. */
252 votingRegimeType := ame_engine.getActionTypeVotingRegime(actionTypeIdIn => tempApprover.action_type_id);
253 /* Check for COA Insertions */
254 ame_engine.getHandlerCOAFirstApprover(itemClassIn => tempApprover.item_class,
255 itemIdIn => tempApprover.item_id,
256 actionTypeIdIn => tempApprover.action_type_id,
257 groupOrChainIdIn => tempApprover.group_or_chain_id,
258 nameOut => COAInsertee.name,
259 origSystemOut => COAInsertee.orig_system,
260 origSystemIdOut => COAInsertee.orig_system_id,
261 displayNameOut => COAInsertee.display_name,
262 sourceOut => COAInsertee.source);
263 /* Start building the chain from the COA Insertee if defined otherwise from the
264 non-default starting point or the requestor's supervisor. */
265 if COAInsertee.name is null then
266 /* Fetch some of the required attributes. */
267 startingPointId :=
268 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.nonDefStartingPointPosAttr));
269 if(startingPointId is null) then
270 requestorId :=
271 to_number(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.transactionReqPositionAttr));
272 if (requestorId is null) then
273 raise nullFirstIdException;
274 end if;
275 if topDogPositionId = requestorId then
276 tempApprover.orig_system_id := requestorId;
277 /* Check if requestor can self approve. If so, insert the approver as the
278 only approver, with a status of approved, and return.*/
279 if(ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowAutoApprovalAttribute)
280 = ame_util.booleanAttributeTrue)
281 then
282 getCatSourceAndAuthority(positionIdIn => requestorId,
283 categoryOut => tempApprover.approver_category,
284 sourceOut => tempApprover.source,
285 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
286 tempApprover.api_insertion := ame_util.oamGenerated;
287 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
288 origSystemIn => ame_util.posOrigSystem,
289 origSystemIdIn => requestorId,
290 nameOut => tempApprover.name,
291 displayNameOut => tempApprover.display_name);
292 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
293 nameIn => tempApprover.name,
294 itemClassIn => tempApprover.item_class,
295 itemIdIn => tempApprover.item_id,
296 actionTypeIdIn => tempApprover.action_type_id,
297 groupOrChainIdIn => tempApprover.group_or_chain_id);
298 tempApprover.member_order_number := 1;
299 tempApprover.approval_status := ame_util.approvedStatus;
300 ame_engine.addApprover(approverIn => tempApprover);
301 return;
302 else
303 /* The requestor is the top position but he can not auto approve.
304 hence raise appropriate exception*/
305 raise topDogRequestorException;
306 end if;
307 end if;
308 /* The requestor could not self-approve, either because there was a non-default
309 starting point, or because the requestor lacked sufficient authority. So,
310 start building the chain from the non-default starting point or the requestor's
311 parent position. */
312 tempApprover.orig_system_id := getNextPosition(positionIdIn => requestorId);
313 else
314 tempApprover.orig_system_id := startingPointId;
315 end if;
316 tempApprover.api_insertion := ame_util.oamGenerated;
317 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.posOrigSystem,
318 origSystemIdIn => tempApprover.orig_system_id,
319 nameOut => tempApprover.name,
320 displayNameOut => tempApprover.display_name);
321 else
322 firstAuthInsExists := true;
323 tempApprover.name := COAInsertee.name;
324 tempApprover.orig_system := COAInsertee.orig_system;
325 tempApprover.orig_system_id := COAInsertee.orig_system_id;
326 tempApprover.display_name := COAInsertee.display_name;
327 firstApproverSource := COAInsertee.source;
328 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
329 end if;
330 /* Build the chain. */
331 currentPositionLevel := 0;
332 tempMemberOrderNumber := 0; /* pre-increment */
333 errorCode := -20241;
334 loop
335 getCatSourceAndAuthority(positionIdIn => tempApprover.orig_system_id,
336 categoryOut => tempApprover.approver_category,
337 sourceOut => tempApprover.source,
338 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
339 if firstApproverSource is not null then
340 tempApprover.source := firstApproverSource;
341 firstApproverSource := null;
342 end if;
343 tempApprover.occurrence := ame_engine.getHandlerOccurrence(
344 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 tempMemberOrderNumber := tempMemberOrderNumber + 1;
350 if(votingRegimeType = ame_util.serializedVoting) then
351 tempApprover.member_order_number := tempMemberOrderNumber;
352 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
353 tempApprover.member_order_number := 1;
354 end if;
355 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn => tempApprover);
356 /* The engine will set tempApprover.approver_order_number; leave it null here. */
357 ame_engine.addApprover(approverIn => tempApprover);
358 /* check to see if there is a COA insertion after this approver. If a COA
359 insertion is found, keep checking till no more COA insertions. The check
360 for final authority will not be needed (similar to supervisory handler). */
361 loop
362 /* Initialize COAInsertee approverRecord2 */
363 COAInsertee := ame_util.emptyApproverRecord2;
364 /* Check if there are any COAInsertions */
365 ame_engine.getHandlerCOAInsertion(nameIn => tempApprover.name,
366 itemClassIn => tempApprover.item_class,
367 itemIdIn => tempApprover.item_id,
368 actionTypeIdIn => tempApprover.action_type_id,
369 groupOrChainIdIn => tempApprover.group_or_chain_id,
370 occurrenceIn => tempApprover.occurrence,
371 approvalStatusIn => tempApprover.approval_status,
372 nameOut => COAInsertee.name,
373 origSystemOut => COAInsertee.orig_system,
374 origSystemIdOut => COAInsertee.orig_system_id,
375 displayNameOut => COAInsertee.display_name,
376 sourceOut => COAInsertee.source);
377 if COAInsertee.name is null then
378 exit;
379 else
380 coaInsAuthForward := true;
381 tempApprover.name := COAInsertee.name;
382 tempApprover.orig_system := COAInsertee.orig_system;
383 tempApprover.orig_system_id := COAInsertee.orig_system_id;
384 tempApprover.display_name := COAInsertee.display_name;
385 getCatSourceAndAuthority(positionIdIn => tempApprover.orig_system_id,
386 categoryOut => tempApprover.approver_category,
387 sourceOut => tempApprover.source,
388 hasFinalAuthorityYNOut => tempHasFinalAuthorityYN);
389 tempApprover.source := COAInsertee.source;
390 tempApprover.api_insertion := ame_util.apiAuthorityInsertion;
391 tempMemberOrderNumber := tempMemberOrderNumber + 1;
392 if(votingRegimeType = ame_util.serializedVoting) then
393 tempApprover.member_order_number := tempMemberOrderNumber;
394 else /* votingRegimeType in (ame_util.consensusVoting, ame_util.firstApproverVoting) */
395 tempApprover.member_order_number := 1;
396 end if;
397 tempApprover.occurrence := ame_engine.getHandlerOccurrence(nameIn => tempApprover.name,
398 itemClassIn => tempApprover.item_class,
399 itemIdIn => tempApprover.item_id,
400 actionTypeIdIn => tempApprover.action_type_id,
401 groupOrChainIdIn => tempApprover.group_or_chain_id);
402 tempApprover.approval_status := ame_engine.getHandlerApprovalStatus(approverIn =>
403 tempApprover);
404 ame_engine.addApprover(approverIn => tempApprover);
405 end if;
406 end loop;
407 /* Decide whether to end the chain. */
408 if(tempHasFinalAuthorityYN = ame_util.booleanTrue ) then
409 exit;
410 end if;
411 tempApprover.orig_system_id := getNextPosition(positionIdIn =>tempApprover.orig_system_id );
412 ame_approver_type_pkg.getWfRolesNameAndDisplayName(origSystemIn => ame_util.posOrigSystem,
413 origSystemIdIn => tempApprover.orig_system_id,
414 nameOut => tempApprover.name,
415 displayNameOut => tempApprover.display_name);
416 if firstAuthInsExists then
417 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.firstauthHandlerInsReason,null);
418 end if;
419 if coaInsAuthForward then
420 ame_engine.setDeviationReasonDate(ame_approver_deviation_pkg.forwarHandlerAuthInsReason,null);
421 end if;
422 tempApprover.api_insertion := ame_util.oamGenerated;
423 end loop;
424 exception
425 when nullFirstIdException then
426 errorCode := -20107;
427 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
428 messageNameIn => 'AME_400408_HAN_NO_TRANS_POS_ID');
429 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
430 routineNameIn => 'handler',
431 exceptionNumberIn => errorCode,
432 exceptionStringIn => errorMessage);
433 raise_application_error(errorCode,
434 errorMessage);
435 when topDogRequestorException then
436 errorCode := -20111;
437 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
438 messageNameIn => 'AME_400421_REQ_CANNOT_APPROVE');
439 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
440 routineNameIn => 'handler',
441 exceptionNumberIn => errorCode,
442 exceptionStringIn => errorMessage);
443 raise_application_error(errorCode,
444 errorMessage);
445 when others then
446 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
447 routineNameIn => 'handler',
448 exceptionNumberIn => sqlcode,
449 exceptionStringIn => sqlerrm);
450 l_error_code := sqlcode;
451 if l_error_code = -20213 then
452 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
453 messageNameIn => 'AME_400834_INV_HANDLR_APR',
454 tokenNameOneIn => 'ACTION_TYPE_NAME',
455 tokenValueOneIn => ame_engine.getActionTypeName(tempApprover.action_type_id),
456 tokenNameTwoIn => 'ORIG_SYSTEM',
457 tokenValueTwoIn => ame_util.posOrigSystem,
458 tokenNameThreeIn => 'ORIG_SYSEM_ID',
459 tokenValueThreeIn => tempApprover.orig_system_id);
460 raise_application_error(errorCode,errorMessage);
461 end if;
462 raise;
463 end handler;
464 procedure parseAndSortRules as
465 badParameterException exception;
466 errorCode integer;
467 errorMessage ame_util.longestStringType;
468 signPosition integer;
469 tempCategory ame_util.charType;
470 tempLength integer;
471 tempNumber integer;
472 tempRuleId integer;
473 tempSign ame_util.charType;
474 tempParameter ame_util.parameterType;
475 upperLimit integer;
476 begin
477 /* Parse. */
478 for i in 1 .. parametersCount loop
479 signPosition := instrb(parameters(i), '+');
480 tempLength := lengthb(parameters(i));
481 if signPosition = 0 then
482 signPosition := instrb(parameters(i), '-');
483 if signPosition = 0 then
484 parameterSigns(i) := '+';
485 parameterNumbers(i) := to_number(parameters(i));
486 else
487 parameterSigns(i) := substrb(parameters(i), tempLength, tempLength);
488 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
489 end if;
490 else
491 parameterSigns(i) := substrb(parameters(i), tempLength, tempLength);
492 parameterNumbers(i) := to_number(substrb(parameters(i), 1, tempLength - 1));
493 end if;
494 if(parameterSigns(i) <> '+' ) then
495 raise badParameterException;
496 end if;
497 end loop;
498 /* Sort. */
499 for i in 2 .. parametersCount loop
500 upperLimit := i - 1;
501 for j in 1 .. upperLimit loop
502 if(parameterNumbers(i) < parameterNumbers(j) ) then
503 tempRuleId := ruleIds(j);
504 tempCategory := approverCategories(j);
505 tempNumber := parameterNumbers(j);
506 tempSign := parameterSigns(j);
507 tempParameter := parameters(j);
508 --
509 ruleIds(j) := ruleIds(i);
510 approverCategories(j) := approverCategories(i);
511 parameterNumbers(j) := parameterNumbers(i);
512 parameterSigns(j) := parameterSigns(i);
513 parameters(j) := parameters(i);
514 --
515 ruleIds(i) := tempRuleId;
516 approverCategories(i) := tempCategory;
517 parameterNumbers(i) := tempNumber;
518 parameterSigns(i) := tempSign;
519 parameters(i) := tempParameter;
520 end if;
521 end loop;
522 end loop;
523 exception
524 when badParameterException then
525 errorCode := -20001;
526 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
527 messageNameIn => 'AME_400234_HAN_ACT_PAR_SIGN');
528 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
529 routineNameIn => 'parseAndSortRules',
530 exceptionNumberIn => errorCode,
531 exceptionStringIn => errorMessage);
532 raise_application_error(errorCode,
533 errorMessage);
534 when others then
535 ame_util.runtimeException(packageNameIn => 'ame_position_level_handler',
536 routineNameIn => 'parseAndSortRules',
537 exceptionNumberIn => sqlcode,
538 exceptionStringIn => sqlerrm);
539 raise;
540 end parseAndSortRules;
541 end ame_position_level_handler;