[Home] [Help]
PACKAGE BODY: APPS.AME_API3
Source
1 package body ame_api3 as
2 /* $Header: ameeapi3.pkb 120.13 2007/12/12 12:41:39 prasashe noship $ */
3 ambiguousApproverException exception;
4 ambiguousApproverMessage constant ame_util.longestStringType :=
5 ame_util.getMessage(applicationShortNameIn =>'PER',
6 messageNameIn => 'AME_400812_NULL_APPR_REC_NAME');
7 /* functions */
8 function getRuleDescription(ruleIdIn in varchar2) return varchar2 as
9 begin
10 return(ame_rule_pkg.getDescription(ruleIdIn => ruleIdIn));
11 exception
12 when others then
13 ame_util.runtimeException(packageNameIn => 'ame_api3',
14 routineNameIn => 'getRuleDescription',
15 exceptionNumberIn => sqlcode,
16 exceptionStringIn => sqlerrm);
17 raise;
18 return(null);
19 end getRuleDescription;
20 /* procedures */
21 procedure clearInsertion(applicationIdIn in number,
22 transactionTypeIn in varchar2,
23 transactionIdIn in varchar2,
24 approverIn in ame_util.approverRecord2) as
25 ameAppId integer;
26 errorCode integer;
27 errorMessage ame_util.longStringType;
28 appr_rec_params varchar2(100);
29 cursor getAnchorInsertions (parameterIn varchar2) is
30 (select name
31 from ame_temp_insertions
32 where application_id = ame_engine.getAmeApplicationId
33 and transaction_id = transactionIdIn
34 and parameter = parameterIn);
35 nameList ame_util.longStringList;
36 anchorsExistException exception;
37 cmpParameter ame_temp_insertions.parameter%type;
38 anchorName varchar2(320);
39 anchorList varchar2(1000);
40 approvers ame_util.approversTable2;
41 nullApprRecordFieldException Exception;
42 begin
43 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
44 transactionIdIn => transactionIdIn,
45 transactionTypeIdIn => transactionTypeIn);
46 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
47 transactionTypeIdIn => transactionTypeIn);
48 appr_rec_params:=null;
49 if(approverIn.name is null) then
50 appr_rec_params := 'name ';
51 end if;
52 if approverIn.item_class is null then
53 appr_rec_params := appr_rec_params || ', item_class ';
54 end if;
55 if approverIn.item_id is null then
56 appr_rec_params := appr_rec_params || ', item_id ';
57 end if;
58 if appr_rec_params is not null then
59 raise nullApprRecordFieldException;
60 end if;
61 ame_engine.updateTransactionState(isTestTransactionIn => false,
62 isLocalTransactionIn => false,
63 fetchConfigVarsIn => true,
64 fetchOldApproversIn => true,
65 fetchInsertionsIn => true,
66 fetchDeletionsIn => true,
67 fetchAttributeValuesIn => true,
68 fetchInactiveAttValuesIn => false,
69 processProductionActionsIn => false,
70 processProductionRulesIn => false,
71 updateCurrentApproverListIn => true,
72 updateOldApproverListIn => true,
73 processPrioritiesIn => true,
74 prepareItemDataIn => false,
75 prepareRuleIdsIn => false,
76 prepareRuleDescsIn => false,
77 transactionIdIn => transactionIdIn,
78 ameApplicationIdIn => null,
79 fndApplicationIdIn => applicationIdIn,
80 transactionTypeIdIn => transactionTypeIn );
81 ame_engine.getApprovers(approversOut => approvers);
82 for i in 1 .. approvers.count loop
83 if approvers(i).name = approverIn.name and
84 approvers(i).item_class = approverIn.item_class and
85 approvers(i).item_id = approverIn.item_id and
86 approvers(i).api_insertion = ame_util.apiInsertion then
87 anchorName := approvers(i).name;
88 cmpParameter := approvers(i).name ||
89 ame_util.fieldDelimiter ||
90 approvers(i).item_class ||
91 ame_util.fieldDelimiter ||
92 approvers(i).item_id ||
93 ame_util.fieldDelimiter ||
94 approvers(i).action_type_id ||
95 ame_util.fieldDelimiter ||
96 approvers(i).group_or_chain_id ||
97 ame_util.fieldDelimiter ||
98 approvers(i).occurrence;
99 open getAnchorInsertions(cmpParameter);
100 fetch getAnchorInsertions bulk collect into nameList;
101 if nameList.count > 0 then
102 close getAnchorInsertions;
103 raise anchorsExistException;
104 end if;
105 close getAnchorInsertions;
106 end if;
107 end loop;
108 for i in 1 .. approvers.count loop
109 if approvers(i).name = approverIn.name and
110 approvers(i).item_class = approverIn.item_class and
111 approvers(i).item_id = approverIn.item_id then
112 ame_engine.updateInsertions(indexIn => i);
113 end if;
114 end loop;
115 delete from ame_temp_insertions
116 where
117 application_id = ameAppId and
118 transaction_id = transactionIdIn and
119 name = approverIn.name and
120 item_class = approverIn.item_class and
121 item_id = approverIn.item_id ;
122 if sql%found then
123 ame_approver_deviation_pkg.clearDeviationState(
124 applicationIdIn => ameAppId
125 ,transactionIdIn => transactionIdIn );
126 end if;
127 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
128 transactionIdIn => transactionIdIn,
129 transactionTypeIdIn => transactionTypeIn);
130 exception
131 when anchorsExistException then
132 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
133 transactionIdIn => transactionIdIn,
134 transactionTypeIdIn => transactionTypeIn);
135 anchorList := '';
136 for i in 1 .. nameList.count loop
137 anchorList := anchorList || ame_approver_type_pkg.getApproverDescription(nameList(i));
138 if i <> nameList.count then
139 anchorList := anchorList || '; ';
140 end if;
141 end loop;
142 errorCode := -20001;
143 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
144 messageNameIn => 'AME_400651_ANCHORS_EXIST',
145 tokenNameOneIn => 'APPROVER',
146 tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
147 tokenNameTwoIn => 'ANCHORED_APPROVERS',
148 tokenValueTwoIn => anchorList);
149 ame_util.runtimeException(packageNameIn => 'ame_api3',
150 routineNameIn => 'clearInsertion',
151 exceptionNumberIn => errorCode,
152 exceptionStringIn => errorMessage);
153 raise_application_error(errorCode,
154 errorMessage);
155 when nullApprRecordFieldException then
156 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
157 transactionIdIn => transactionIdIn,
158 transactionTypeIdIn => transactionTypeIn);
159 errorCode := -20001;
160 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
161 messageNameIn => 'AME_400813_NULL_CLINS_APPR_FLD',
162 tokenNameOneIn => 'APPROVER_REC_PARAMS',
163 tokenValueOneIn => appr_rec_params);
164 ame_util.runtimeException(packageNameIn => 'ame_api3',
165 routineNameIn => 'clearInsertion',
166 exceptionNumberIn => errorCode,
167 exceptionStringIn => errorMessage);
168 raise_application_error(errorCode,
169 errorMessage);
170 when others then
171 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
172 transactionIdIn => transactionIdIn,
173 transactionTypeIdIn => transactionTypeIn);
174 ame_util.runtimeException(packageNameIn => 'ame_api3',
175 routineNameIn => 'clearInsertion',
176 exceptionNumberIn => sqlcode,
177 exceptionStringIn => sqlerrm);
178 raise;
179 end clearInsertion;
180 procedure clearInsertions(applicationIdIn in integer,
181 transactionTypeIn in varchar2,
182 transactionIdIn in varchar2) as
183 ameAppId integer;
184 begin
185 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
186 transactionIdIn => transactionIdIn,
187 transactionTypeIdIn => transactionTypeIn);
188 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
189 transactionTypeIdIn => transactionTypeIn);
190 delete from ame_temp_insertions
191 where
192 application_id = ameAppId and
193 transaction_id = transactionIdIn ;
194 if sql%found then
195 ame_approver_deviation_pkg.clearDeviationState(
196 applicationIdIn => ameAppId
197 ,transactionIdIn => transactionIdIn );
198 end if;
199 ame_engine.updateTransactionState(isTestTransactionIn => false,
200 isLocalTransactionIn => false,
201 fetchConfigVarsIn => true,
202 fetchOldApproversIn => true,
203 fetchInsertionsIn => true,
204 fetchDeletionsIn => true,
205 fetchAttributeValuesIn => true,
206 fetchInactiveAttValuesIn => false,
207 processProductionActionsIn => false,
208 processProductionRulesIn => false,
209 updateCurrentApproverListIn => true,
210 updateOldApproverListIn => true,
211 processPrioritiesIn => true,
212 prepareItemDataIn => false,
213 prepareRuleIdsIn => false,
214 prepareRuleDescsIn => false,
215 transactionIdIn => transactionIdIn,
216 ameApplicationIdIn => null,
217 fndApplicationIdIn => applicationIdIn,
218 transactionTypeIdIn => transactionTypeIn );
219 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
220 transactionIdIn => transactionIdIn,
221 transactionTypeIdIn => transactionTypeIn);
222 exception
223 when others then
224 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
225 transactionIdIn => transactionIdIn,
226 transactionTypeIdIn => transactionTypeIn);
227 ame_util.runtimeException(packageNameIn => 'ame_api3',
228 routineNameIn => 'clearInsertions',
229 exceptionNumberIn => sqlcode,
230 exceptionStringIn => sqlerrm);
231 raise;
232 end clearInsertions;
233 procedure clearSuppression(applicationIdIn in number,
234 transactionTypeIn in varchar2,
235 transactionIdIn in varchar2,
236 approverIn in ame_util.approverRecord2) as
237 ameAppId integer;
238 errorCode integer;
239 errorMessage ame_util.longStringType;
240 appr_rec_params varchar2(100);
241 nullApprRecordFieldException Exception;
242 begin
243 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
244 transactionIdIn => transactionIdIn,
245 transactionTypeIdIn => transactionTypeIn);
246 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
247 transactionTypeIdIn => transactionTypeIn);
248 appr_rec_params:=null;
249 if(approverIn.name is null) then
250 appr_rec_params := 'name ';
251 end if;
252 if approverIn.item_class is null then
253 appr_rec_params := appr_rec_params || ', item_class ';
254 end if;
255 if approverIn.item_id is null then
256 appr_rec_params := appr_rec_params || ', item_id ';
257 end if;
258 if approverIn.action_type_id is null then
259 appr_rec_params := appr_rec_params || ', action_type_id ';
260 end if;
261 if approverIn.group_or_chain_id is null then
262 appr_rec_params := appr_rec_params || ', group_or_chain_id ';
263 end if;
264 if appr_rec_params is not null then
265 raise nullApprRecordFieldException;
266 end if;
267 delete from ame_temp_deletions
268 where
269 application_id = ameAppId and
270 transaction_id = transactionIdIn and
271 name = approverIn.name and
272 item_class = approverIn.item_class and
273 item_id = approverIn.item_id and
274 action_type_id = approverIn.action_type_id and
275 group_or_chain_id = approverIn.group_or_chain_id ;
276 if sql%found then
277 ame_approver_deviation_pkg.clearDeviationState(
278 applicationIdIn => ameAppId
279 ,transactionIdIn => transactionIdIn );
280 end if;
281 --
282 -- Cycle the engine to account for changes in the deletions table. (There is no previous
283 -- call to initializePlsqlContext, so all of the boolean arguments need to be true.)
284 --
285 ame_engine.updateTransactionState(isTestTransactionIn => false,
286 isLocalTransactionIn => false,
287 fetchConfigVarsIn => true,
288 fetchOldApproversIn => true,
289 fetchInsertionsIn => true,
290 fetchDeletionsIn => true,
291 fetchAttributeValuesIn => true,
292 fetchInactiveAttValuesIn => false,
293 processProductionActionsIn => false,
294 processProductionRulesIn => false,
295 updateCurrentApproverListIn => true,
296 updateOldApproverListIn => true,
297 processPrioritiesIn => true,
298 prepareItemDataIn => false,
299 prepareRuleIdsIn => false,
300 prepareRuleDescsIn => false,
301 transactionIdIn => transactionIdIn,
302 ameApplicationIdIn => null,
303 fndApplicationIdIn => applicationIdIn,
304 transactionTypeIdIn => transactionTypeIn );
305 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
306 transactionIdIn => transactionIdIn,
307 transactionTypeIdIn => transactionTypeIn);
308 exception
309 when nullApprRecordFieldException then
310 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
311 transactionIdIn => transactionIdIn,
312 transactionTypeIdIn => transactionTypeIn);
313 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
314 messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
315 tokenNameOneIn => 'APPROVER_REC_PARAMS',
316 tokenvalueOneIn=>appr_rec_params);
317 errorCode := -20001;
318 ame_util.runtimeException(packageNameIn => 'ame_api3',
319 routineNameIn => 'clearSuppression',
320 exceptionNumberIn => errorCode,
321 exceptionStringIn => errorMessage);
322 raise_application_error(errorCode,
323 errorMessage);
324 when others then
325 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
326 transactionIdIn => transactionIdIn,
327 transactionTypeIdIn => transactionTypeIn);
328 ame_util.runtimeException(packageNameIn => 'ame_api3',
329 routineNameIn => 'clearSuppression',
330 exceptionNumberIn => sqlcode,
331 exceptionStringIn => sqlerrm);
332 raise;
333 end clearSuppression;
334 procedure clearSuppressions(applicationIdIn in integer,
335 transactionTypeIn in varchar2,
336 transactionIdIn in varchar2) as
337 ameAppId integer;
338 begin
339 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
340 transactionIdIn => transactionIdIn,
341 transactionTypeIdIn => transactionTypeIn);
342 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
343 transactionTypeIdIn => transactionTypeIn);
344 delete from ame_temp_deletions
345 where
346 application_id = ameAppId and
347 transaction_id = transactionIdIn ;
348 if sql%found then
349 ame_approver_deviation_pkg.clearDeviationState(
350 applicationIdIn => ameAppId
351 ,transactionIdIn => transactionIdIn );
352 end if;
353 ame_engine.updateTransactionState(isTestTransactionIn => false,
354 isLocalTransactionIn => false,
355 fetchConfigVarsIn => true,
356 fetchOldApproversIn => true,
357 fetchInsertionsIn => true,
358 fetchDeletionsIn => true,
359 fetchAttributeValuesIn => true,
360 fetchInactiveAttValuesIn => false,
361 processProductionActionsIn => false,
362 processProductionRulesIn => false,
363 updateCurrentApproverListIn => true,
364 updateOldApproverListIn => true,
365 processPrioritiesIn => true,
366 prepareItemDataIn => false,
367 prepareRuleIdsIn => false,
368 prepareRuleDescsIn => false,
369 transactionIdIn => transactionIdIn,
370 ameApplicationIdIn => null,
371 fndApplicationIdIn => applicationIdIn,
372 transactionTypeIdIn => transactionTypeIn );
373 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
374 transactionIdIn => transactionIdIn,
375 transactionTypeIdIn => transactionTypeIn);
376 exception
377 when others then
378 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
379 transactionIdIn => transactionIdIn,
380 transactionTypeIdIn => transactionTypeIn);
381 ame_util.runtimeException(packageNameIn => 'ame_api3',
382 routineNameIn => 'clearSuppressions',
383 exceptionNumberIn => sqlcode,
384 exceptionStringIn => sqlerrm);
385 raise;
386 end clearSuppressions;
387 procedure getAllApprovalGroups(groupIdsOut out nocopy ame_util.idList,
388 groupNamesOut out nocopy ame_util.stringList) as
389 cursor fetchGroupsCursor is
390 select approval_group_id, name
391 from ame_approval_groups
392 where end_date is null
393 order by name;
394 begin
395 open fetchGroupsCursor;
396 fetch fetchGroupsCursor bulk collect
397 into
398 groupIdsOut,
399 groupNamesOut;
400 close fetchGroupsCursor;
401 exception
402 when others then
403 ame_util.runtimeException(packageNameIn => 'ame_api3',
404 routineNameIn => 'getAllApprovalGroups',
405 exceptionNumberIn => sqlcode,
406 exceptionStringIn => sqlerrm);
407 raise;
408 end getAllApprovalGroups;
409 procedure getApplicableRules1(applicationIdIn in integer,
410 transactionIdIn in varchar2,
411 transactionTypeIn in varchar2,
412 ruleIdsOut out nocopy ame_util.idList) as
413 ruleDescriptions ame_util.stringList;
414 productionFunctionality ame_util.stringType;
415 processProductionRules boolean;
416 begin
417 productionFunctionality := ame_util.getConfigVar
418 (variableNameIn => ame_util.productionConfigVar
419 ,applicationIdIn => ame_admin_pkg.getApplicationId
420 (fndAppIdIn => applicationIdIn
421 ,transactionTypeIdIn => transactionTypeIn
422 )
423 );
424 if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
425 processProductionRules := false;
426 else
427 processProductionRules := true;
428 end if;
429 ame_engine.updateTransactionState(isTestTransactionIn => false,
430 isLocalTransactionIn => false,
431 fetchConfigVarsIn => true,
432 fetchOldApproversIn => true,
433 fetchInsertionsIn => true,
434 fetchDeletionsIn => true,
435 fetchAttributeValuesIn => true,
436 fetchInactiveAttValuesIn => false,
437 processProductionActionsIn => false,
438 processProductionRulesIn => processProductionRules,
439 updateCurrentApproverListIn => true,
440 updateOldApproverListIn => true,
441 processPrioritiesIn => true,
442 prepareItemDataIn => false,
443 prepareRuleIdsIn => true,
444 prepareRuleDescsIn => false,
445 transactionIdIn => transactionIdIn,
446 ameApplicationIdIn => null,
447 fndApplicationIdIn => applicationIdIn,
448 transactionTypeIdIn => transactionTypeIn );
449 ame_engine.getApplicableRules
450 (ruleIdsOut => ruleIdsOut
451 ,ruleDescriptionsOut => ruleDescriptions);
452 exception
453 when others then
454 ame_util.runtimeException(packageNameIn => 'ame_api3',
455 routineNameIn => 'getApplicableRules1',
456 exceptionNumberIn => sqlcode,
457 exceptionStringIn => sqlerrm);
458 raise;
459 end getApplicableRules1;
460 procedure getApplicableRules2(applicationIdIn in integer,
461 transactionIdIn in varchar2,
462 transactionTypeIn in varchar2,
463 ruleDescriptionsOut out nocopy ame_util.stringList) as
464 ruleIds ame_util.idList;
465 productionFunctionality ame_util.stringType;
466 processProductionRules boolean;
467 begin
468 productionFunctionality := ame_util.getConfigVar
469 (variableNameIn => ame_util.productionConfigVar
470 ,applicationIdIn => ame_admin_pkg.getApplicationId
471 (fndAppIdIn => applicationIdIn
472 ,transactionTypeIdIn => transactionTypeIn
473 )
474 );
475 if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
476 processProductionRules := false;
477 else
478 processProductionRules := true;
479 end if;
480 ame_engine.updateTransactionState(isTestTransactionIn => false,
481 isLocalTransactionIn => false,
482 fetchConfigVarsIn => true,
483 fetchOldApproversIn => true,
484 fetchInsertionsIn => true,
485 fetchDeletionsIn => true,
486 fetchAttributeValuesIn => true,
487 fetchInactiveAttValuesIn => false,
488 processProductionActionsIn => false,
489 processProductionRulesIn => processProductionRules,
490 updateCurrentApproverListIn => true,
491 updateOldApproverListIn => true,
492 processPrioritiesIn => true,
493 prepareItemDataIn => false,
494 prepareRuleIdsIn => true,
495 prepareRuleDescsIn => true,
496 transactionIdIn => transactionIdIn,
497 ameApplicationIdIn => null,
498 fndApplicationIdIn => applicationIdIn,
499 transactionTypeIdIn => transactionTypeIn );
500 ame_engine.getApplicableRules
501 (ruleIdsOut => ruleIds
502 ,ruleDescriptionsOut => ruleDescriptionsOut);
503 exception
504 when others then
505 ame_util.runtimeException(packageNameIn => 'ame_api3',
506 routineNameIn => 'getApplicableRules2',
507 exceptionNumberIn => sqlcode,
508 exceptionStringIn => sqlerrm);
509 raise;
510 end getApplicableRules2;
511 procedure getApplicableRules3(applicationIdIn in integer,
512 transactionIdIn in varchar2,
513 transactionTypeIn in varchar2,
514 ruleIdsOut out nocopy ame_util.idList,
515 ruleDescriptionsOut out nocopy ame_util.stringList) as
516 productionFunctionality ame_util.stringType;
517 processProductionRules boolean;
518 begin
519 productionFunctionality := ame_util.getConfigVar
520 (variableNameIn => ame_util.productionConfigVar
521 ,applicationIdIn => ame_admin_pkg.getApplicationId
522 (fndAppIdIn => applicationIdIn
523 ,transactionTypeIdIn => transactionTypeIn
524 )
525 );
526 if productionFunctionality in (ame_util.noProductions, ame_util.perApproverProductions) then
527 processProductionRules := false;
528 else
529 processProductionRules := true;
530 end if;
531 ame_engine.updateTransactionState(isTestTransactionIn => false,
532 isLocalTransactionIn => false,
533 fetchConfigVarsIn => true,
534 fetchOldApproversIn => true,
535 fetchInsertionsIn => true,
536 fetchDeletionsIn => true,
537 fetchAttributeValuesIn => true,
538 fetchInactiveAttValuesIn => false,
539 processProductionActionsIn => false,
540 processProductionRulesIn => processProductionRules,
541 updateCurrentApproverListIn => true,
542 updateOldApproverListIn => true,
543 processPrioritiesIn => true,
544 prepareItemDataIn => false,
545 prepareRuleIdsIn => true,
546 prepareRuleDescsIn => true,
547 transactionIdIn => transactionIdIn,
548 ameApplicationIdIn => null,
549 fndApplicationIdIn => applicationIdIn,
550 transactionTypeIdIn => transactionTypeIn);
551 ame_engine.getApplicableRules
552 (ruleIdsOut => ruleIdsOut
553 ,ruleDescriptionsOut => ruleDescriptionsOut);
554 exception
555 when others then
556 ame_util.runtimeException(packageNameIn => 'ame_api3',
557 routineNameIn => 'getApplicableRules3',
558 exceptionNumberIn => sqlcode,
559 exceptionStringIn => sqlerrm);
560 raise;
561 end getApplicableRules3;
562 procedure getApprovalGroupId(groupNameIn ame_util.stringType,
563 groupIdOut out nocopy number) as
564 errorCode integer;
565 errorMessage ame_util.longestStringType;
566 begin
567 select approval_group_id
568 into groupIdOut
569 from ame_approval_groups
570 where name = groupNameIn
571 and end_date is null;
572 exception
573 when no_data_found then
574 errorCode := -20001;
575 errorMessage := ame_util.getMessage(
576 applicationShortNameIn => 'PER',
577 messageNameIn => 'AME_400453_GROUP_NOT_DEFINED',
578 tokenNameOneIn => 'APPROVAL_GROUP',
579 tokenValueOneIn => groupNameIn);
580 ame_util.runtimeException(packageNameIn => 'ame_api3',
581 routineNameIn => 'getApprovalGroupId',
582 exceptionNumberIn => errorCode,
583 exceptionStringIn => errorMessage);
584 raise_application_error(errorCode,
585 errorMessage);
586 when others then
587 ame_util.runtimeException(packageNameIn => 'ame_api3',
588 routineNameIn => 'getApprovalGroupId',
589 exceptionNumberIn => sqlcode,
590 exceptionStringIn => sqlerrm);
591 raise;
592 end getApprovalGroupId;
593 procedure getAttributeValue( applicationIdIn in number,
594 transactionTypeIn in varchar2,
595 transactionIdIn in varchar2,
596 attributeNameIn in varchar2,
597 itemIdIn in varchar2,
598 attributeValue1Out out nocopy varchar2,
599 attributeValue2Out out nocopy varchar2,
600 attributeValue3Out out nocopy varchar2) as
601 itemId ame_util.stringType;
602 begin
603 ame_engine.updateTransactionState(isTestTransactionIn => false,
604 isLocalTransactionIn => false,
605 fetchConfigVarsIn => false,
606 fetchOldApproversIn => false,
607 fetchInsertionsIn => false,
608 fetchDeletionsIn => false,
609 fetchAttributeValuesIn => true,
610 fetchInactiveAttValuesIn => true,
611 processProductionActionsIn => false,
612 processProductionRulesIn => false,
613 updateCurrentApproverListIn => false,
614 updateOldApproverListIn => false,
615 processPrioritiesIn => false,
616 prepareItemDataIn => false,
617 prepareRuleIdsIn => false,
618 prepareRuleDescsIn => false,
619 transactionIdIn => transactionIdIn,
620 ameApplicationIdIn => null,
621 fndApplicationIdIn => applicationIdIn,
622 transactionTypeIdIn => transactionTypeIn );
623 /* In case no itemId is passed in, assume it belongs to the header itemClass and
624 pass in the transactionIdIn */
625 if itemIdIn is null then
626 itemId := transactionIdIn;
627 else
628 itemId := itemIdIn;
629 end if;
630 ame_engine.getItemAttValues2(attributeNameIn => attributeNameIn,
631 itemIdIn => itemId,
632 attributeValue1Out => attributeValue1Out,
633 attributeValue2Out => attributeValue2Out,
634 attributeValue3Out => attributeValue3Out);
635 exception
636 when others then
637 ame_util.runtimeException(packageNameIn => 'ame_api3',
638 routineNameIn => 'getAttributeValue',
639 exceptionNumberIn => sqlcode,
640 exceptionStringIn => sqlerrm);
641 raise;
642 end getAttributeValue;
643 /*
644 getAvailableInsertions limits its output to insertions available for the order type orderTypeIn,
645 if this is null; otherwise getAvailableInsertions outputs insertions available for all order types.
646 Chain-of-authority insertees (those having authority = ame_util.authorityApprover and
647 api_insertion = ame_util.apiAuthorityInsertion) must have the approver_category value
648 ame_util.approvalApproverCategory. Ad-hoc insertees may be of either approver category.
649 */
650 procedure getAvailableInsertions(applicationIdIn in number,
651 transactionTypeIn in varchar2,
652 transactionIdIn in varchar2,
653 positionIn in number,
654 orderTypeIn in varchar2 default null,
655 availableInsertionsOut out nocopy ame_util.insertionsTable2) as
656 begin
657 ame_engine.updateTransactionState(isTestTransactionIn => false,
658 isLocalTransactionIn => false,
659 fetchConfigVarsIn => true,
660 fetchOldApproversIn => true,
661 fetchInsertionsIn => true,
662 fetchDeletionsIn => true,
663 fetchAttributeValuesIn => true,
664 fetchInactiveAttValuesIn => false,
665 processProductionActionsIn => false,
666 processProductionRulesIn => false,
667 updateCurrentApproverListIn => true,
668 updateOldApproverListIn => false,
669 processPrioritiesIn => true,
670 prepareItemDataIn => false,
671 prepareRuleIdsIn => false,
672 prepareRuleDescsIn => false,
673 transactionIdIn => transactionIdIn,
674 ameApplicationIdIn => null,
675 fndApplicationIdIn => applicationIdIn,
676 transactionTypeIdIn => transactionTypeIn );
677
678 ame_engine.getInsertions
679 (positionIn => positionIn
680 ,coaInsertionsYNIn => ame_util.booleanTrue
681 ,orderTypeIn => orderTypeIn
682 ,availableInsertionsOut => availableInsertionsOut
683 );
684 exception
685 when others then
686 ame_util.runtimeException
687 (packageNameIn => 'ame_api3'
688 ,routineNameIn => 'getAvailableInsertions'
689 ,exceptionNumberIn => sqlcode
690 ,exceptionStringIn => sqlerrm
691 );
692 raise;
693 end getAvailableInsertions;
694 procedure getConditionDetails(conditionIdIn in integer,
695 attributeNameOut out nocopy varchar2,
696 attributeTypeOut out nocopy varchar2,
697 attributeDescriptionOut out nocopy varchar2,
698 lowerLimitOut out nocopy varchar2,
699 upperLimitOut out nocopy varchar2,
700 includeLowerLimitOut out nocopy varchar2,
701 includeUpperLimitOut out nocopy varchar2,
702 currencyCodeOut out nocopy varchar2,
703 allowedValuesOut out nocopy ame_util.longestStringList) as
704 begin
705 select
706 ame_attributes.name,
707 ame_attributes.attribute_type,
708 ame_attributes.description,
709 ame_conditions.parameter_one,
710 ame_conditions.parameter_two,
711 ame_conditions.include_lower_limit,
712 ame_conditions.include_upper_limit,
713 ame_conditions.parameter_three
714 into
715 attributeNameOut,
716 attributeTypeOut,
717 attributeDescriptionOut,
718 lowerLimitOut,
719 upperLimitOut,
720 includeLowerLimitOut,
721 includeUpperLimitOut,
722 currencyCodeOut
723 from
724 ame_attributes,
725 ame_conditions
726 where
727 ame_attributes.attribute_id = ame_conditions.attribute_id and
728 ame_conditions.condition_id = conditionIdIn and
729 sysdate between
730 ame_attributes.start_date and
731 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
732 sysdate between
733 ame_conditions.start_date and
734 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
735 if(attributeTypeOut = ame_util.stringAttributeType) then
736 ame_condition_pkg.getStringValueList(conditionIdIn => conditionIdIn,
737 stringValueListOut => allowedValuesOut);
738 end if;
739 exception
740 when others then
741 ame_util.runtimeException(packageNameIn => 'ame_api3',
742 routineNameIn => 'getConditionDetails',
743 exceptionNumberIn => sqlcode,
744 exceptionStringIn => sqlerrm);
745 raise;
746 end getConditionDetails;
747 procedure getGroupMembers1(applicationIdIn in number default null,
748 transactionTypeIn in varchar2 default null,
749 transactionIdIn in varchar2 default null,
750 groupIdIn in number,
751 memberDisplayNamesOut out nocopy ame_util.longStringList)as
752 cursor groupMemberCursor(groupIdIn in integer) is
753 select
754 parameter,
755 upper(parameter_name),
756 query_string,
757 decode(parameter_name,
758 ame_util.approverOamGroupId, null,
759 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
760 from ame_approval_group_members
761 where
762 approval_group_id = groupIdIn
763 order by order_number;
764 badDynamicMemberException exception;
765 noItemBindException exception;
766 dynamicCursor integer;
767 colonLocation1 integer;
768 colonLocation2 integer;
769 displayNames ame_util.longStringList;
770 errorCode integer;
771 errorMessage ame_util.longestStringType;
772 noTransIdDefinedException exception;
773 orderNumbers ame_util.idList;
774 memberOrigSystem ame_util.stringType;
775 memberOrigSystemId number;
776 outputIndex integer;
777 parameters ame_util.longStringList;
778 queryStrings ame_util.longestStringList;
779 rowsFound integer;
780 tempGroupMembers dbms_sql.Varchar2_Table;
781 upperParameterNames ame_util.stringList;
782 tempGroupName ame_util.stringType;
783 begin
784 open groupMemberCursor(groupIdIn => groupIdIn);
785 fetch groupMemberCursor bulk collect
786 into
787 parameters,
788 upperParameterNames,
789 queryStrings,
790 displayNames;
791 close groupMemberCursor;
792 outputIndex := 0; /* pre-increment */
793 for i in 1 .. parameters.count loop
794 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
795 dynamicCursor := dbms_sql.open_cursor;
796 dbms_sql.parse(dynamicCursor,
797 ame_util.removeReturns(stringIn => queryStrings(i),
798 replaceWithSpaces => true),
799 dbms_sql.native);
800 if(instrb(queryStrings(i),
801 ame_util.transactionIdPlaceholder) > 0) then
802 if transactionIdIn is null then
803 dbms_sql.close_cursor(dynamicCursor);
804 raise noTransIdDefinedException;
805 end if;
806 dbms_sql.bind_variable(dynamicCursor,
807 ame_util.transactionIdPlaceholder,
808 transactionIdIn,
809 50);
810 end if;
811 if(instrb(queryStrings(i),
812 ame_util2.itemClassPlaceHolder) > 0) then
813 dbms_sql.bind_variable(dynamicCursor,
814 ame_util2.itemClassPlaceHolder,
815 ame_util.headerItemClassName,
816 50);
817 end if;
818 if (instrb(queryStrings(i),
819 ame_util2.itemIdPlaceHolder) > 0) then
820 if transactionIdIn is null then
821 dbms_sql.close_cursor(dynamicCursor);
822 raise noTransIdDefinedException;
823 end if;
824 dbms_sql.bind_variable(dynamicCursor,
825 ame_util2.itemIdPlaceHolder,
826 transactionIdIn,
827 50);
828 end if;
829 dbms_sql.define_array(dynamicCursor,
830 1,
831 tempGroupMembers,
832 100,
833 1);
834 rowsFound := dbms_sql.execute(dynamicCursor);
835 loop
836 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
837 dbms_sql.column_value(dynamicCursor,
838 1,
839 tempGroupMembers);
840 exit when rowsFound < 100;
841 end loop;
842 dbms_sql.close_cursor(dynamicCursor);
843 /*
844 Dynamic groups' query strings may return rows having one of two forms:
845 (1) approver_type:approver_id
846 (2) orig_system:orig_system_id:approver_name
847 */
848 for j in 1 .. tempGroupMembers.count loop
849 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
850 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
851 if(colonLocation1 = 0) then
852 raise badDynamicMemberException;
853 end if;
854 outputIndex := outputIndex + 1;
855 if(colonLocation2 = 0) then /* first case (old style) */
856 memberOrigSystemId :=
857 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
858 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
859 upper(ame_util.approverPersonId)) then
860 memberOrigSystem := ame_util.perOrigSystem;
861 elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
862 upper(ame_util.approverUserId)) then
863 memberOrigSystem := ame_util.fndUserOrigSystem;
864 else
865 memberOrigSystem :=
866 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
867 memberOrigSystemId :=
868 substrb(tempGroupMembers(j),instrb(tempGroupMembers(j), ':', 1, 1) + 1);
869 end if;
870 else
871 memberOrigSystem :=
872 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
873 memberOrigSystemId :=
874 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
875 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
876 end if;
877 memberDisplayNamesOut(outputIndex) :=
878 ame_approver_type_pkg.getApproverDisplayName2(
879 origSystemIn => memberOrigSystem,
880 origSystemIdIn => memberOrigSystemId);
881 end loop;
882 else /* Copy the static group into the engGroup caches. */
883 outputIndex := outputIndex + 1;
884 memberDisplayNamesOut(outputIndex) := displayNames(i);
885 end if;
886 end loop;
887 exception
888 when badDynamicMemberException then
889 if(groupMemberCursor%isopen) then
890 close groupMemberCursor;
891 end if;
892 memberDisplayNamesOut.delete;
893 errorCode := -20001;
894 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
895 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
896 ame_util.runtimeException(packageNameIn => 'ame_api3',
897 routineNameIn => 'getGroupMembers1',
898 exceptionNumberIn => errorCode,
899 exceptionStringIn => errorMessage);
900 raise_application_error(errorCode,
901 errorMessage);
902 when noItemBindException then
903 if(groupMemberCursor%isopen) then
904 close groupMemberCursor;
905 end if;
906 memberDisplayNamesOut.delete;
907 errorCode := -20001;
908 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
909 ,groupNameOut => tempGroupName);
910 errorMessage := ame_util.getMessage(
911 applicationShortNameIn => 'PER',
912 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
913 tokenNameOneIn => 'APPROVER_GROUP',
914 tokenValueOneIn => tempGroupName);
915 ame_util.runtimeException(packageNameIn => 'ame_api3',
916 routineNameIn => 'getGroupMembers1',
917 exceptionNumberIn => errorCode,
918 exceptionStringIn => errorMessage);
919 raise_application_error(errorCode,
920 errorMessage);
921 when noTransIdDefinedException then
922 if(groupMemberCursor%isopen) then
923 close groupMemberCursor;
924 end if;
925 memberDisplayNamesOut.delete;
926 errorCode := -20001;
927 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
928 ,groupNameOut => tempGroupName);
929 errorMessage := ame_util.getMessage(
930 applicationShortNameIn => 'PER',
931 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
932 tokenNameOneIn => 'APPROVAL_GROUP',
933 tokenValueOneIn => tempGroupName);
934 ame_util.runtimeException(packageNameIn => 'ame_api3',
935 routineNameIn => 'getGroupMembers1',
936 exceptionNumberIn => errorCode,
937 exceptionStringIn => errorMessage);
938 raise_application_error(errorCode,
939 errorMessage);
940 when others then
941 if(groupMemberCursor%isopen) then
942 close groupMemberCursor;
943 end if;
944 memberDisplayNamesOut.delete;
945 ame_util.runtimeException(packageNameIn => 'ame_api3',
946 routineNameIn => 'getGroupMembers1',
947 exceptionNumberIn => sqlcode,
948 exceptionStringIn => sqlerrm);
949 raise;
950 end getGroupMembers1;
951 procedure getGroupMembers2(applicationIdIn in number default null,
952 transactionTypeIn in varchar2 default null,
953 transactionIdIn in varchar2 default null,
954 groupIdIn in number,
955 memberNamesOut out nocopy ame_util.longStringList,
956 memberDisplayNamesOut out nocopy ame_util.longStringList)as
957 cursor groupMemberCursor(groupIdIn in integer) is
958 select
959 parameter,
960 upper(parameter_name),
961 query_string,
962 decode(parameter_name,
963 ame_util.approverOamGroupId, null,
964 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
965 decode(parameter_name,
966 ame_util.approverOamGroupId, null,
967 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
968 from ame_approval_group_members
969 where
970 approval_group_id = groupIdIn
971 order by order_number;
972 badDynamicMemberException exception;
973 noItemBindException exception;
974 dynamicCursor integer;
975 colonLocation1 integer;
976 colonLocation2 integer;
977 displayNames ame_util.longStringList;
978 errorCode integer;
979 errorMessage ame_util.longestStringType;
980 approverNames ame_util.longStringList;
981 memberOrigSystem ame_util.stringType;
982 memberOrigSystemId number;
983 noTransIdDefinedException exception;
984 orderNumbers ame_util.idList;
985 origSystemIds ame_util.idList;
986 origSystems ame_util.stringList;
987 outputIndex integer;
988 parameters ame_util.longStringList;
989 queryStrings ame_util.longestStringList;
990 rowsFound integer;
991 tempGroupMembers dbms_sql.Varchar2_Table;
992 upperParameterNames ame_util.stringList;
993 tempGroupName ame_util.stringType;
994 begin
995 open groupMemberCursor(groupIdIn => groupIdIn);
996 fetch groupMemberCursor bulk collect
997 into
998 parameters,
999 upperParameterNames,
1000 queryStrings,
1001 approverNames,
1002 displayNames;
1003 close groupMemberCursor;
1004 outputIndex := 0; /* pre-increment */
1005 for i in 1 .. parameters.count loop
1006 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1007 dynamicCursor := dbms_sql.open_cursor;
1008 dbms_sql.parse(dynamicCursor,
1009 ame_util.removeReturns(stringIn => queryStrings(i),
1010 replaceWithSpaces => true),
1011 dbms_sql.native);
1012 if(instrb(queryStrings(i),
1013 ame_util.transactionIdPlaceholder) > 0) then
1014 if transactionIdIn is null then
1015 dbms_sql.close_cursor(dynamicCursor);
1016 raise noTransIdDefinedException;
1017 end if;
1018 dbms_sql.bind_variable(dynamicCursor,
1019 ame_util.transactionIdPlaceholder,
1020 transactionIdIn,
1021 50);
1022 end if;
1023 if(instrb(queryStrings(i),
1024 ame_util2.itemClassPlaceHolder) > 0) then
1025 dbms_sql.bind_variable(dynamicCursor,
1026 ame_util2.itemClassPlaceHolder,
1027 ame_util.headerItemClassName,
1028 50);
1029 end if;
1030 if (instrb(queryStrings(i),
1031 ame_util2.itemIdPlaceHolder) > 0) then
1032 if transactionIdIn is null then
1033 dbms_sql.close_cursor(dynamicCursor);
1034 raise noTransIdDefinedException;
1035 end if;
1036 dbms_sql.bind_variable(dynamicCursor,
1037 ame_util2.itemIdPlaceHolder,
1038 transactionIdIn,
1039 50);
1040 end if;
1041 dbms_sql.define_array(dynamicCursor,
1042 1,
1043 tempGroupMembers,
1044 100,
1045 1);
1046 rowsFound := dbms_sql.execute(dynamicCursor);
1047 loop
1048 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
1049 dbms_sql.column_value(dynamicCursor,
1050 1,
1051 tempGroupMembers);
1052 exit when rowsFound < 100;
1053 end loop;
1054 dbms_sql.close_cursor(dynamicCursor);
1055 /*
1056 Dynamic groups' query strings may return rows having one of two forms:
1057 (1) approver_type:approver_id
1058 (2) orig_system:orig_system_id:approver_name
1059 */
1060 for j in 1 .. tempGroupMembers.count loop
1061 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
1062 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
1063 if(colonLocation1 = 0) then
1064 raise badDynamicMemberException;
1065 end if;
1066 outputIndex := outputIndex + 1;
1067 if(colonLocation2 = 0) then /* first case (old style) */
1068 memberOrigSystemId :=
1069 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1070 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1071 upper(ame_util.approverPersonId)) then
1072 memberOrigSystem := ame_util.perOrigSystem;
1073 elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1074 upper(ame_util.approverUserId)) then
1075 memberOrigSystem := ame_util.fndUserOrigSystem;
1076 else
1077 memberOrigSystem :=
1078 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1079 memberOrigSystemId :=
1080 substrb(tempGroupMembers(j),instrb(tempGroupMembers(j), ':', 1, 1) + 1);
1081 end if;
1082 else
1083 memberOrigSystem :=
1084 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1085 memberOrigSystemId :=
1086 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
1087 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
1088 end if;
1089 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
1090 origSystemIn => memberOrigSystem,
1091 origSystemIdIn => memberOrigSystemId,
1092 nameOut => memberNamesOut(outputIndex),
1093 displayNameOut => memberDisplayNamesOut(outputIndex));
1094 end loop;
1095 else /* Copy the static group into the engGroup caches. */
1096 outputIndex := outputIndex + 1;
1097 memberNamesOut(outputIndex) := approverNames(i);
1098 memberDisplayNamesOut(outputIndex) := displayNames(i);
1099 end if;
1100 end loop;
1101 exception
1102 when badDynamicMemberException then
1103 if(groupMemberCursor%isopen) then
1104 close groupMemberCursor;
1105 end if;
1106 errorCode := -20001;
1107 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1108 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1109 ame_util.runtimeException(packageNameIn => 'ame_api3',
1110 routineNameIn => 'getGroupMembers2',
1111 exceptionNumberIn => errorCode,
1112 exceptionStringIn => errorMessage);
1113 raise_application_error(errorCode,
1114 errorMessage);
1115 when noTransIdDefinedException then
1116 if(groupMemberCursor%isopen) then
1117 close groupMemberCursor;
1118 end if;
1119 errorCode := -20001;
1120 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1121 ,groupNameOut => tempGroupName);
1122 errorMessage := ame_util.getMessage(
1123 applicationShortNameIn => 'PER',
1124 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1125 tokenNameOneIn => 'APPROVAL_GROUP',
1126 tokenValueOneIn => tempGroupName);
1127 ame_util.runtimeException(packageNameIn => 'ame_api3',
1128 routineNameIn => 'getGroupMembers2',
1129 exceptionNumberIn => errorCode,
1130 exceptionStringIn => errorMessage);
1131 raise_application_error(errorCode,
1132 errorMessage);
1133 when noItemBindException then
1134 if(groupMemberCursor%isopen) then
1135 close groupMemberCursor;
1136 end if;
1137 memberDisplayNamesOut.delete;
1138 errorCode := -20001;
1139 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1140 ,groupNameOut => tempGroupName);
1141 errorMessage := ame_util.getMessage(
1142 applicationShortNameIn => 'PER',
1143 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1144 tokenNameOneIn => 'APPROVER_GROUP',
1145 tokenValueOneIn => tempGroupName);
1146 ame_util.runtimeException(packageNameIn => 'ame_api3',
1147 routineNameIn => 'getGroupMembers2',
1148 exceptionNumberIn => errorCode,
1149 exceptionStringIn => errorMessage);
1150 raise_application_error(errorCode,
1151 errorMessage);
1152 when others then
1153 if(groupMemberCursor%isopen) then
1154 close groupMemberCursor;
1155 end if;
1156 ame_util.runtimeException(packageNameIn => 'ame_api3',
1157 routineNameIn => 'getGroupMembers2',
1158 exceptionNumberIn => sqlcode,
1159 exceptionStringIn => sqlerrm);
1160 raise;
1161 end getGroupMembers2;
1162 procedure getGroupMembers3(applicationIdIn in number default null,
1163 transactionTypeIn in varchar2 default null,
1164 transactionIdIn in varchar2 default null,
1165 groupIdIn in number,
1166 memberNamesOut out nocopy ame_util.longStringList,
1167 memberOrderNumbersOut out nocopy ame_util.idList,
1168 memberDisplayNamesOut out nocopy ame_util.longStringList)as
1169 cursor groupMemberCursor(groupIdIn in integer) is
1170 select
1171 parameter,
1172 upper(parameter_name),
1173 query_string,
1174 order_number,
1175 decode(parameter_name,
1176 ame_util.approverOamGroupId, null,
1177 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
1178 decode(parameter_name,
1179 ame_util.approverOamGroupId, null,
1180 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
1181 from ame_approval_group_members
1182 where
1183 approval_group_id = groupIdIn
1184 order by order_number;
1185 badDynamicMemberException exception;
1186 noItemBindException exception;
1187 dynamicCursor integer;
1188 colonLocation1 integer;
1189 colonLocation2 integer;
1190 displayNames ame_util.longStringList;
1191 errorCode integer;
1192 errorMessage ame_util.longestStringType;
1193 approverNames ame_util.longStringList;
1194 memberOrigSystem ame_util.stringType;
1195 memberOrigSystemId number;
1196 noTransIdDefinedException exception;
1197 orderNumbers ame_util.idList;
1198 origSystemIds ame_util.idList;
1199 origSystems ame_util.stringList;
1200 outputIndex integer;
1201 parameters ame_util.longStringList;
1202 queryStrings ame_util.longestStringList;
1203 rowsFound integer;
1204 tempGroupMembers dbms_sql.Varchar2_Table;
1205 upperParameterNames ame_util.stringList;
1206 tempGroupName ame_util.stringType;
1207 begin
1208 open groupMemberCursor(groupIdIn => groupIdIn);
1209 fetch groupMemberCursor bulk collect
1210 into
1211 parameters,
1212 upperParameterNames,
1213 queryStrings,
1214 orderNumbers,
1215 approverNames,
1216 displayNames;
1217 close groupMemberCursor;
1218 outputIndex := 0; /* pre-increment */
1219 for i in 1 .. parameters.count loop
1220 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1221 dynamicCursor := dbms_sql.open_cursor;
1222 dbms_sql.parse(dynamicCursor,
1223 ame_util.removeReturns(stringIn => queryStrings(i),
1224 replaceWithSpaces => true),
1225 dbms_sql.native);
1226 if(instrb(queryStrings(i),
1227 ame_util.transactionIdPlaceholder) > 0) then
1228 if transactionIdIn is null then
1229 dbms_sql.close_cursor(dynamicCursor);
1230 raise noTransIdDefinedException;
1231 end if;
1232 dbms_sql.bind_variable(dynamicCursor,
1233 ame_util.transactionIdPlaceholder,
1234 transactionIdIn,
1235 50);
1236 end if;
1237 if(instrb(queryStrings(i),
1238 ame_util2.itemClassPlaceHolder) > 0) then
1239 dbms_sql.bind_variable(dynamicCursor,
1240 ame_util2.itemClassPlaceHolder,
1241 ame_util.headerItemClassName,
1242 50);
1243 end if;
1244 if (instrb(queryStrings(i),
1245 ame_util2.itemIdPlaceHolder) > 0) then
1246 if transactionIdIn is null then
1247 dbms_sql.close_cursor(dynamicCursor);
1248 raise noTransIdDefinedException;
1249 end if;
1250 dbms_sql.bind_variable(dynamicCursor,
1251 ame_util2.itemIdPlaceHolder,
1252 transactionIdIn,
1253 50);
1254 end if;
1255 dbms_sql.define_array(dynamicCursor,
1256 1,
1257 tempGroupMembers,
1258 100,
1259 1);
1260 rowsFound := dbms_sql.execute(dynamicCursor);
1261 loop
1262 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
1263 dbms_sql.column_value(dynamicCursor,
1264 1,
1265 tempGroupMembers);
1266 exit when rowsFound < 100;
1267 end loop;
1268 dbms_sql.close_cursor(dynamicCursor);
1269 /*
1270 Dynamic groups' query strings may return rows having one of two forms:
1271 (1) approver_type:approver_id
1272 (2) orig_system:orig_system_id:approver_name
1273 */
1274 for j in 1 .. tempGroupMembers.count loop
1275 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
1276 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
1277 if(colonLocation1 = 0) then
1278 raise badDynamicMemberException;
1279 end if;
1280 outputIndex := outputIndex + 1;
1281 memberOrderNumbersOut(outputIndex) := j;
1282 if(colonLocation2 = 0) then /* first case (old style) */
1283 memberOrigSystemId :=
1284 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1285 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1286 upper(ame_util.approverPersonId)) then
1287 memberOrigSystem := ame_util.perOrigSystem;
1288 elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1289 upper(ame_util.approverUserId)) then
1290 memberOrigSystem := ame_util.fndUserOrigSystem;
1291 else
1292 memberOrigSystem :=
1293 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1294 memberOrigSystemId :=
1295 substrb(tempGroupMembers(j),instrb(tempGroupMembers(j), ':', 1, 1) + 1);
1296 end if;
1297 else
1298 memberOrigSystem :=
1299 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1300 memberOrigSystemId :=
1301 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
1302 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
1303 end if;
1304 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
1305 origSystemIn => memberOrigSystem,
1306 origSystemIdIn => memberOrigSystemId,
1307 nameOut => memberNamesOut(outputIndex),
1308 displayNameOut => memberDisplayNamesOut(outputIndex));
1309 end loop;
1310 else /* Copy the static group into the engGroup caches. */
1311 outputIndex := outputIndex + 1;
1312 memberOrderNumbersOut(outputIndex) := orderNumbers(i);
1313 memberNamesOut(outputIndex) := approverNames(i);
1314 memberDisplayNamesOut(outputIndex) := displayNames(i);
1315 end if;
1316 end loop;
1317 exception
1318 when badDynamicMemberException then
1319 if(groupMemberCursor%isopen) then
1320 close groupMemberCursor;
1321 end if;
1322 errorCode := -20001;
1323 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1324 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1325 ame_util.runtimeException(packageNameIn => 'ame_api3',
1326 routineNameIn => 'getGroupMembers3',
1327 exceptionNumberIn => errorCode,
1328 exceptionStringIn => errorMessage);
1329 raise_application_error(errorCode,
1330 errorMessage);
1331 when noTransIdDefinedException then
1332 if(groupMemberCursor%isopen) then
1333 close groupMemberCursor;
1334 end if;
1335 errorCode := -20001;
1336 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1337 ,groupNameOut => tempGroupName);
1338 errorMessage := ame_util.getMessage(
1339 applicationShortNameIn => 'PER',
1340 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1341 tokenNameOneIn => 'APPROVAL_GROUP',
1342 tokenValueOneIn => tempGroupName);
1343 ame_util.runtimeException(packageNameIn => 'ame_api3',
1344 routineNameIn => 'getGroupMembers3',
1345 exceptionNumberIn => errorCode,
1346 exceptionStringIn => errorMessage);
1347 raise_application_error(errorCode,
1348 errorMessage);
1349 when noItemBindException then
1350 if(groupMemberCursor%isopen) then
1351 close groupMemberCursor;
1352 end if;
1353 memberDisplayNamesOut.delete;
1354 errorCode := -20001;
1355 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1356 ,groupNameOut => tempGroupName);
1357 errorMessage := ame_util.getMessage(
1358 applicationShortNameIn => 'PER',
1359 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1360 tokenNameOneIn => 'APPROVER_GROUP',
1361 tokenValueOneIn => tempGroupName);
1362 ame_util.runtimeException(packageNameIn => 'ame_api3',
1363 routineNameIn => 'getGroupMembers3',
1364 exceptionNumberIn => errorCode,
1365 exceptionStringIn => errorMessage);
1366 raise_application_error(errorCode,
1367 errorMessage);
1368 when others then
1369 if(groupMemberCursor%isopen) then
1370 close groupMemberCursor;
1371 end if;
1372 ame_util.runtimeException(packageNameIn => 'ame_api3',
1373 routineNameIn => 'getGroupMembers3',
1374 exceptionNumberIn => sqlcode,
1375 exceptionStringIn => sqlerrm);
1376 raise;
1377 end getGroupMembers3;
1378 procedure getGroupMembers4(applicationIdIn in number default null,
1379 transactionTypeIn in varchar2 default null,
1380 transactionIdIn in varchar2 default null,
1381 groupIdIn in number,
1382 memberNamesOut out nocopy ame_util.longStringList,
1383 memberOrderNumbersOut out nocopy ame_util.idList,
1384 memberDisplayNamesOut out nocopy ame_util.longStringList,
1385 memberOrigSystemIdsOut out nocopy ame_util.idList,
1386 memberOrigSystemsOut out nocopy ame_util.stringList)as
1387 cursor groupMemberCursor(groupIdIn in integer) is
1388 select
1389 orig_system,
1390 orig_system_id,
1391 parameter,
1392 upper(parameter_name),
1393 query_string,
1394 order_number,
1395 decode(parameter_name,
1396 ame_util.approverOamGroupId, null,
1397 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
1398 decode(parameter_name,
1399 ame_util.approverOamGroupId, null,
1400 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
1401 from ame_approval_group_members
1402 where
1403 approval_group_id = groupIdIn
1404 order by order_number;
1405 badDynamicMemberException exception;
1406 noItemBindException exception;
1407 dynamicCursor integer;
1408 colonLocation1 integer;
1409 colonLocation2 integer;
1410 displayNames ame_util.longStringList;
1411 errorCode integer;
1412 errorMessage ame_util.longestStringType;
1413 approverNames ame_util.longStringList;
1414 noTransIdDefinedException exception;
1415 orderNumbers ame_util.idList;
1416 origSystemIds ame_util.idList;
1417 origSystems ame_util.stringList;
1418 outputIndex integer;
1419 parameters ame_util.longStringList;
1420 queryStrings ame_util.longestStringList;
1421 rowsFound integer;
1422 tempGroupMembers dbms_sql.Varchar2_Table;
1423 upperParameterNames ame_util.stringList;
1424 tempGroupName ame_util.stringType;
1425 begin
1426 open groupMemberCursor(groupIdIn => groupIdIn);
1427 fetch groupMemberCursor bulk collect
1428 into
1429 origSystems,
1430 origSystemIds,
1431 parameters,
1432 upperParameterNames,
1433 queryStrings,
1434 orderNumbers,
1435 approverNames,
1436 displayNames;
1437 close groupMemberCursor;
1438 outputIndex := 0; /* pre-increment */
1439 for i in 1 .. parameters.count loop
1440 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
1441 dynamicCursor := dbms_sql.open_cursor;
1442 dbms_sql.parse(dynamicCursor,
1443 ame_util.removeReturns(stringIn => queryStrings(i),
1444 replaceWithSpaces => true),
1445 dbms_sql.native);
1446 if(instrb(queryStrings(i),
1447 ame_util.transactionIdPlaceholder) > 0) then
1448 if transactionIdIn is null then
1449 dbms_sql.close_cursor(dynamicCursor);
1450 raise noTransIdDefinedException;
1451 end if;
1452 dbms_sql.bind_variable(dynamicCursor,
1453 ame_util.transactionIdPlaceholder,
1454 transactionIdIn,
1455 50);
1456 end if;
1457 if(instrb(queryStrings(i),
1458 ame_util2.itemClassPlaceHolder) > 0) then
1459 dbms_sql.bind_variable(dynamicCursor,
1460 ame_util2.itemClassPlaceHolder,
1461 ame_util.headerItemClassName,
1462 50);
1463 end if;
1464 if (instrb(queryStrings(i),
1465 ame_util2.itemIdPlaceHolder) > 0) then
1466 if transactionIdIn is null then
1467 dbms_sql.close_cursor(dynamicCursor);
1468 raise noTransIdDefinedException;
1469 end if;
1470 dbms_sql.bind_variable(dynamicCursor,
1471 ame_util2.itemIdPlaceHolder,
1472 transactionIdIn,
1473 50);
1474 end if;
1475 dbms_sql.define_array(dynamicCursor,
1476 1,
1477 tempGroupMembers,
1478 100,
1479 1);
1480 rowsFound := dbms_sql.execute(dynamicCursor);
1481 loop
1482 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
1483 dbms_sql.column_value(dynamicCursor,
1484 1,
1485 tempGroupMembers);
1486 exit when rowsFound < 100;
1487 end loop;
1488 dbms_sql.close_cursor(dynamicCursor);
1489 /*
1490 Dynamic groups' query strings may return rows having one of two forms:
1491 (1) approver_type:approver_id
1492 (2) orig_system:orig_system_id:approver_name
1493 */
1494 for j in 1 .. tempGroupMembers.count loop
1495 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
1496 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
1497 if(colonLocation1 = 0) then
1498 raise badDynamicMemberException;
1499 end if;
1500 outputIndex := outputIndex + 1;
1501 memberOrderNumbersOut(outputIndex) := j;
1502 if(colonLocation2 = 0) then /* first case (old style) */
1503 memberOrigSystemIdsOut(outputIndex) :=
1504 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
1505 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1506 upper(ame_util.approverPersonId)) then
1507 memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
1508 elsif (substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
1509 upper(ame_util.approverUserId)) then
1510 memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
1511 else
1512 memberOrigSystemsOut(outputIndex) :=
1513 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1514 memberOrigSystemIdsOut(outputIndex) :=
1515 substrb(tempGroupMembers(j),instrb(tempGroupMembers(j), ':', 1, 1) + 1);
1516 end if;
1517 else
1518 memberOrigSystemsOut(outputIndex) :=
1519 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
1520 memberOrigSystemIdsOut(outputIndex) :=
1521 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
1522 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
1523 end if;
1524 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
1525 origSystemIn => memberOrigSystemsOut(outputIndex),
1526 origSystemIdIn => memberOrigSystemIdsOut(outputIndex),
1527 nameOut => memberNamesOut(outputIndex),
1528 displayNameOut => memberDisplayNamesOut(outputIndex));
1529 end loop;
1530 else /* Copy the static group into the engGroup caches. */
1531 outputIndex := outputIndex + 1;
1532 memberNamesOut(outputIndex) := approverNames(i);
1533 memberOrderNumbersOut(outputIndex) := orderNumbers(i);
1534 memberDisplayNamesOut(outputIndex) := displayNames(i);
1535 memberOrigSystemsOut(outputIndex) := origSystems(i);
1536 memberOrigSystemIdsOut(outputIndex) := origSystemIds(i);
1537 end if;
1538 end loop;
1539 exception
1540 when badDynamicMemberException then
1541 if(groupMemberCursor%isopen) then
1542 close groupMemberCursor;
1543 end if;
1544 errorCode := -20001;
1545 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1546 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1547 ame_util.runtimeException(packageNameIn => 'ame_api3',
1548 routineNameIn => 'getGroupMembers4',
1549 exceptionNumberIn => errorCode,
1550 exceptionStringIn => errorMessage);
1551 raise_application_error(errorCode,
1552 errorMessage);
1553 when noTransIdDefinedException then
1554 if(groupMemberCursor%isopen) then
1555 close groupMemberCursor;
1556 end if;
1557 errorCode := -20001;
1558 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1559 ,groupNameOut => tempGroupName);
1560 errorMessage := ame_util.getMessage(
1561 applicationShortNameIn => 'PER',
1562 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
1563 tokenNameOneIn => 'APPROVAL_GROUP',
1564 tokenValueOneIn => tempGroupName);
1565 ame_util.runtimeException(packageNameIn => 'ame_api3',
1566 routineNameIn => 'getGroupMembers4',
1567 exceptionNumberIn => errorCode,
1568 exceptionStringIn => errorMessage);
1569 raise_application_error(errorCode,
1570 errorMessage);
1571 when noItemBindException then
1572 if(groupMemberCursor%isopen) then
1573 close groupMemberCursor;
1574 end if;
1575 memberDisplayNamesOut.delete;
1576 errorCode := -20001;
1577 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
1578 ,groupNameOut => tempGroupName);
1579 errorMessage := ame_util.getMessage(
1580 applicationShortNameIn => 'PER',
1581 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1582 tokenNameOneIn => 'APPROVER_GROUP',
1583 tokenValueOneIn => tempGroupName);
1584 ame_util.runtimeException(packageNameIn => 'ame_api3',
1585 routineNameIn => 'getGroupMembers4',
1586 exceptionNumberIn => errorCode,
1587 exceptionStringIn => errorMessage);
1588 raise_application_error(errorCode,
1589 errorMessage);
1590 when others then
1591 if(groupMemberCursor%isopen) then
1592 close groupMemberCursor;
1593 end if;
1594 ame_util.runtimeException(packageNameIn => 'ame_api3',
1595 routineNameIn => 'getGroupMembers4',
1596 exceptionNumberIn => sqlcode,
1597 exceptionStringIn => sqlerrm);
1598 raise;
1599 end getGroupMembers4;
1600 procedure getItemClasses( applicationIdIn in number,
1601 transactionTypeIn in varchar2,
1602 itemClassIdsOut out nocopy ame_util.idList,
1603 itemClassNamesOut out nocopy ame_util.stringList) as
1604 ameAppId integer;
1605 begin
1606 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
1607 transactionTypeIdIn => transactionTypeIn);
1608 ame_admin_pkg.getTransTypeItemClasses2(applicationIdIn => ameAppId,
1609 itemClassIdsOut => itemClassIdsOut,
1610 itemClassNamesOut => itemClassNamesOut);
1611 exception
1612 when others then
1613 ame_util.runtimeException(packageNameIn => 'ame_api3',
1614 routineNameIn => 'getItemClasses',
1615 exceptionNumberIn => sqlcode,
1616 exceptionStringIn => sqlerrm);
1617 raise;
1618 end getItemClasses;
1619 procedure getItemClassId( itemClassNameIn in varchar2,
1620 itemClassIdOut out nocopy number) as
1621 ameAppId integer;
1622 begin
1623 itemClassIdOut := ame_admin_pkg.getItemClassIdByName(itemClassNameIn => itemClassNameIn);
1624 exception
1625 when others then
1626 ame_util.runtimeException(packageNameIn => 'ame_api3',
1627 routineNameIn => 'getItemClassId',
1628 exceptionNumberIn => sqlcode,
1629 exceptionStringIn => sqlerrm);
1630 raise;
1631 end getItemClassId;
1632 procedure getItemClassName( itemClassIdIn in number,
1633 itemClassNameOut out nocopy varchar2) as
1634 ameAppId integer;
1635 begin
1636 itemClassNameOut := ame_admin_pkg.getItemClassName(itemClassIdIn => itemClassIdIn);
1637 exception
1638 when others then
1639 ame_util.runtimeException(packageNameIn => 'ame_api3',
1640 routineNameIn => 'getItemClassName',
1641 exceptionNumberIn => sqlcode,
1642 exceptionStringIn => sqlerrm);
1643 raise;
1644 end getItemClassName;
1645 procedure getOldApprovers( applicationIdIn in number,
1646 transactionTypeIn in varchar2,
1647 transactionIdIn in varchar2,
1648 oldApproversOut out nocopy ame_util.approversTable2) as
1649 ameAppId integer;
1650 cursor oldApproverCursor(applicationIdIn in integer,
1651 transactionIdIn in varchar2) is
1652 select
1653 name,
1654 item_class,
1655 item_id,
1656 approver_category,
1657 api_insertion,
1658 authority,
1659 approval_status,
1660 action_type_id,
1661 group_or_chain_id,
1662 occurrence
1663 from ame_temp_old_approver_lists
1664 where
1665 application_id = applicationIdIn and
1666 transaction_id = transactionIdIn
1667 order by order_number;
1668 tempIndex integer;
1669 begin
1670 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
1671 transactionTypeIdIn => transactionTypeIn);
1672 tempIndex := 0;
1673 for tempOldApprover in oldApproverCursor(applicationIdIn => ameAppId,
1674 transactionIdIn => transactionIdIn) loop
1675 tempIndex := tempIndex + 1;
1676 oldApproversOut(tempIndex).name := tempOldApprover.name;
1677 oldApproversOut(tempIndex).item_class := tempOldApprover.item_class;
1678 oldApproversOut(tempIndex).item_id := tempOldApprover.item_id;
1679 oldApproversOut(tempIndex).approver_category := tempOldApprover.approver_category;
1680 oldApproversOut(tempIndex).api_insertion := tempOldApprover.api_insertion;
1681 oldApproversOut(tempIndex).authority := tempOldApprover.authority;
1682 oldApproversOut(tempIndex).approval_status := tempOldApprover.approval_status;
1683 oldApproversOut(tempIndex).action_type_id := tempOldApprover.action_type_id;
1684 oldApproversOut(tempIndex).group_or_chain_id := tempOldApprover.group_or_chain_id;
1685 oldApproversOut(tempIndex).occurrence := tempOldApprover.occurrence;
1686 ame_approver_type_pkg.getApproverOrigSystemAndId
1687 (nameIn => tempOldApprover.name
1688 ,origSystemOut => oldApproversOut(tempIndex).orig_system
1689 ,origSystemIdOut => oldApproversOut(tempIndex).orig_system_id);
1690 /*
1691 The old approver list does not maintain source. Calling applications requiring
1692 source data must get it by calling getNextApprover or getAllApprovers.
1693 */
1694 oldApproversOut(tempIndex).source := null;
1695 end loop;
1696 exception
1697 when others then
1698 ame_util.runtimeException(packageNameIn => 'ame_api3',
1699 routineNameIn => 'getOldApprovers',
1700 exceptionNumberIn => sqlcode,
1701 exceptionStringIn => sqlerrm);
1702 oldApproversOut.delete;
1703 ame_api2.getAdminApprover(applicationIdIn => applicationIdIn,
1704 transactionTypeIn => transactionTypeIn,
1705 adminApproverOut => oldApproversOut(1));
1706 raise;
1707 end getOldApprovers;
1708 procedure getRuleDetails1( ruleIdIn in number,
1709 ruleTypeOut out nocopy varchar2,
1710 ruleDescriptionOut out nocopy varchar2,
1711 conditionIdsOut out nocopy ame_util.idList,
1712 actionTypeNamesOut out nocopy ame_util.stringList,
1713 actionTypeDescriptionsOut out nocopy ame_util.stringList,
1714 actionDescriptionsOut out nocopy ame_util.stringList) as
1715 cursor actionCursor is
1716 select
1717 ame_action_types.name,
1718 ame_action_types.description,
1719 ame_actions.description
1720 from
1721 ame_action_usages,
1722 ame_action_types,
1723 ame_actions
1724 where
1725 ame_action_usages.rule_id = ruleIdIn and
1726 ame_actions.action_id = ame_action_usages.action_id and
1727 ame_action_types.action_type_id = ame_actions.action_type_id and
1728 sysdate between
1729 ame_action_usages.start_date and
1730 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1731 sysdate between
1732 ame_action_types.start_date and
1733 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1734 sysdate between
1735 ame_actions.start_date and
1736 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1737 cursor conditionCursor(ruleIdIn in integer) is
1738 select condition_id
1739 from ame_condition_usages
1740 where
1741 ame_condition_usages.rule_id = ruleIdIn and
1742 sysdate between
1743 start_date and
1744 nvl(end_date - ame_util.oneSecond, sysdate);
1745 cursor ruleCursor is
1746 select
1747 ame_rules.rule_type,
1748 ame_rules.description
1749 from
1750 ame_rules
1751 where
1752 ame_rules.rule_id = ruleIdIn and
1753 sysdate between
1754 ame_rules.start_date and
1755 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
1756 begin
1757 open ruleCursor;
1758 fetch ruleCursor into
1759 ruleTypeOut,
1760 ruleDescriptionOut;
1761 close ruleCursor;
1762 open actionCursor;
1763 fetch actionCursor bulk collect into
1764 actionTypeNamesOut,
1765 actionTypeDescriptionsOut,
1766 actionDescriptionsOut;
1767 close actionCursor;
1768 open conditionCursor(ruleIdIn => ruleIdIn);
1769 fetch conditionCursor bulk collect
1770 into conditionIdsOut;
1771 close conditionCursor;
1772 exception
1773 when others then
1774 ame_util.runtimeException(packageNameIn => 'ame_api3',
1775 routineNameIn => 'getRuleDetails1',
1776 exceptionNumberIn => sqlcode,
1777 exceptionStringIn => sqlerrm);
1778 conditionIdsOut.delete;
1779 raise;
1780 end getRuleDetails1;
1781 procedure getRuleDetails2( ruleIdIn in number,
1782 ruleTypeOut out nocopy varchar2,
1783 ruleDescriptionOut out nocopy varchar2,
1784 conditionDescriptionsOut out nocopy ame_util.longestStringList,
1785 actionTypeNamesOut out nocopy ame_util.stringList,
1786 actionTypeDescriptionsOut out nocopy ame_util.stringList,
1787 actionDescriptionsOut out nocopy ame_util.stringList) as
1788 cursor actionCursor is
1789 select
1790 ame_action_types.name,
1791 ame_action_types.description,
1792 ame_actions.description
1793 from
1794 ame_action_usages,
1795 ame_action_types,
1796 ame_actions
1797 where
1798 ame_action_usages.rule_id = ruleIdIn and
1799 ame_actions.action_id = ame_action_usages.action_id and
1800 ame_action_types.action_type_id = ame_actions.action_type_id and
1801 sysdate between
1802 ame_action_usages.start_date and
1803 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1804 sysdate between
1805 ame_action_types.start_date and
1806 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1807 sysdate between
1808 ame_actions.start_date and
1809 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1810 cursor conditionCursor(ruleIdIn in integer) is
1811 select ame_condition_pkg.getDescription(ame_condition_usages.condition_id)
1812 from ame_condition_usages
1813 where
1814 ame_condition_usages.rule_id = ruleIdIn and
1815 sysdate between
1816 ame_condition_usages.start_date and
1817 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate);
1818 cursor ruleCursor is
1819 select
1820 ame_rules.rule_type,
1821 ame_rules.description
1822 from
1823 ame_rules
1824 where
1825 ame_rules.rule_id = ruleIdIn and
1826 sysdate between
1827 ame_rules.start_date and
1828 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
1829 begin
1830 open ruleCursor;
1831 fetch ruleCursor into
1832 ruleTypeOut,
1833 ruleDescriptionOut;
1834 close ruleCursor;
1835 open actionCursor;
1836 fetch actionCursor bulk collect into
1837 actionTypeNamesOut,
1838 actionTypeDescriptionsOut,
1839 actionDescriptionsOut;
1840 close actionCursor;
1841 open conditionCursor(ruleIdIn => ruleIdIn);
1842 fetch conditionCursor bulk collect
1843 into conditionDescriptionsOut;
1844 close conditionCursor;
1845 exception
1846 when others then
1847 ame_util.runtimeException(packageNameIn => 'ame_api3',
1848 routineNameIn => 'getRuleDetails2',
1849 exceptionNumberIn => sqlcode,
1850 exceptionStringIn => sqlerrm);
1851 conditionDescriptionsOut.delete;
1852 raise;
1853 end getRuleDetails2;
1854 procedure getRuleDetails3( ruleIdIn in number,
1855 ruleTypeOut out nocopy varchar2,
1856 ruleDescriptionOut out nocopy varchar2,
1857 conditionIdsOut out nocopy ame_util.idList,
1858 conditionDescriptionsOut out nocopy ame_util.longestStringList,
1859 conditionHasLOVsOut out nocopy ame_util.charList,
1860 actionTypeNamesOut out nocopy ame_util.stringList,
1861 actionTypeDescriptionsOut out nocopy ame_util.stringList,
1862 actionDescriptionsOut out nocopy ame_util.stringList) as
1863 cursor actionCursor is
1864 select
1865 ame_action_types.name,
1866 ame_action_types.description,
1867 ame_actions.description
1868 from
1869 ame_action_usages,
1870 ame_action_types,
1871 ame_actions
1872 where
1873 ame_action_usages.rule_id = ruleIdIn and
1874 ame_actions.action_id = ame_action_usages.action_id and
1875 ame_action_types.action_type_id = ame_actions.action_type_id and
1876 sysdate between
1877 ame_action_usages.start_date and
1878 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
1879 sysdate between
1880 ame_action_types.start_date and
1881 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1882 sysdate between
1883 ame_actions.start_date and
1884 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
1885 cursor conditionCursor(ruleIdIn in integer) is
1886 select
1887 ame_conditions.condition_id,
1888 ame_condition_pkg.getDescription(ame_conditions.condition_id),
1889 decode(ame_attributes.attribute_type,
1890 ame_util.stringAttributeType, ame_util.booleanTrue,
1891 /* default */ ame_util.booleanFalse)
1892 from
1893 ame_conditions,
1894 ame_condition_usages,
1895 ame_attributes
1896 where
1897 ame_condition_usages.rule_id = ruleIdIn and
1898 ame_conditions.condition_id = ame_condition_usages.condition_id and
1899 ame_attributes.attribute_id = ame_conditions.attribute_id and
1900 sysdate between
1901 ame_condition_usages.start_date and
1902 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate) and
1903 sysdate between
1904 ame_conditions.start_date and
1905 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
1906 sysdate between
1907 ame_attributes.start_date and
1908 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
1909 cursor ruleCursor is
1910 select
1911 ame_rules.rule_type,
1912 ame_rules.description
1913 from
1914 ame_rules
1915 where
1916 ame_rules.rule_id = ruleIdIn and
1917 sysdate between
1918 ame_rules.start_date and
1919 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate) ;
1920 begin
1921 open ruleCursor;
1922 fetch ruleCursor into
1923 ruleTypeOut,
1924 ruleDescriptionOut;
1925 close ruleCursor;
1926 open actionCursor;
1927 fetch actionCursor bulk collect into
1928 actionTypeNamesOut,
1929 actionTypeDescriptionsOut,
1930 actionDescriptionsOut;
1931 close actionCursor;
1932 open conditionCursor(ruleIdIn => ruleIdIn);
1933 fetch conditionCursor bulk collect
1934 into
1935 conditionIdsOut,
1936 conditionDescriptionsOut,
1937 conditionHasLOVsOut;
1938 close conditionCursor;
1939 exception
1940 when others then
1941 ame_util.runtimeException(packageNameIn => 'ame_api3',
1942 routineNameIn => 'getRuleDetails3',
1943 exceptionNumberIn => sqlcode,
1944 exceptionStringIn => sqlerrm);
1945 conditionDescriptionsOut.delete;
1946 conditionIdsOut.delete;
1947 conditionHasLOVsOut.delete;
1948 raise;
1949 end getRuleDetails3;
1950 procedure insertApprover( applicationIdIn in number,
1951 transactionTypeIn in varchar2,
1952 transactionIdIn in varchar2,
1953 approverIn in ame_util.approverRecord2,
1954 positionIn in number,
1955 insertionIn in ame_util.insertionRecord2) as
1956 ameApplicationId integer;
1957 absoluteOrder integer;
1958 availableInsertions ame_util.insertionsTable2;
1959 badInsertionTypeException exception;
1960 badStatusException exception;
1961 disallowedAuthException exception;
1962 errorCode integer;
1963 errorMessage ame_util.longStringType;
1964 incompatibleApproverException exception;
1965 insertionOrder integer;
1966 insertionTypeNotFound boolean;
1967 noApproverCategoryException exception;
1968 tempCount integer;
1969 begin
1970 /* Validate input data */
1971 if approverIn.name is null then
1972 raise ambiguousApproverException;
1973 end if;
1974 if insertionIn.item_class is null or
1975 insertionIn.parameter is null then
1976 raise incompatibleApproverException;
1977 end if;
1978 /* Make sure that the approverIn and insertionsIn relate to the same chain */
1979 if (approverIn.item_class <> insertionIn.item_class ) or
1980 (approverIn.item_id <> insertionIn.item_id) or
1981 (approverIn.action_type_id <> insertionIn.action_type_id) or
1982 (approverIn.group_or_chain_id <> insertionIn.group_or_chain_id) or
1983 (approverIn.api_insertion <> insertionIn.api_insertion) or
1984 (approverIn.authority <> insertionIn.authority) then
1985 raise incompatibleApproverException;
1986 end if;
1987 /* Make sure that the approver catgeory is defined correctly for approver */
1988 if (( approverIn.approver_category is null ) or
1989 ( approverIn.approver_category <> ame_util.approvalApproverCategory and
1990 approverIn.approver_category <> ame_util.fyiApproverCategory)) then
1991 raise noApproverCategoryException;
1992 end if;
1993 /* Handler ame_util.firstApprover order types specially. */
1994 if(insertionIn.order_type = ame_util.firstAuthority) then
1995 ame_api2.setFirstAuthorityApprover(applicationIdIn => applicationIdIn,
1996 transactionTypeIn => transactionTypeIn,
1997 transactionIdIn => transactionIdIn,
1998 approverIn => approverIn,
1999 clearChainStatusYNIn => ame_util.booleanFalse);
2000 return;
2001 end if;
2002 /* Make sure approverIn.approval_status is null. */
2003 if(approverIn.approval_status is not null) then
2004 raise badStatusException;
2005 end if;
2006 /* Make sure approverIn.api_insertion is of the right type. */
2007 if(approverIn.api_insertion = ame_util.oamGenerated) then
2008 raise badInsertionTypeException;
2009 end if;
2010 /* Lock Transactions */
2011 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
2012 transactionIdIn => transactionIdIn,
2013 transactionTypeIdIn => transactionTypeIn);
2014 /*
2015 Check that only allowed insertion-order and approver-type values are passed, by calling
2016 getAvailableInsertions and comparing values.
2017 */
2018 getAvailableInsertions(applicationIdIn => applicationIdIn,
2019 transactionTypeIn => transactionTypeIn,
2020 transactionIdIn => transactionIdIn,
2021 positionIn => positionIn,
2022 orderTypeIn => insertionIn.order_type,
2023 availableInsertionsOut => availableInsertions);
2024 tempCount := availableInsertions.count;
2025 insertionTypeNotFound := true;
2026 /* Check if insertionIn is a valid insertion in availableInsertions */
2027 for i in 1 .. tempCount loop
2028 if(availableInsertions(i).order_type = insertionIn.order_type and
2029 availableInsertions(i).parameter = insertionIn.parameter and
2030 availableInsertions(i).api_insertion = insertionIn.api_insertion and
2031 availableInsertions(i).authority = insertionIn.authority) then
2032 insertionTypeNotFound := false;
2033 exit;
2034 end if;
2035 end loop;
2036 if(insertionTypeNotFound) then
2037 raise badInsertionTypeException;
2038 end if;
2039 /* Perform the insertion. */
2040 insertionOrder := ame_engine.getNextInsertionOrder;
2041 ameApplicationId := ame_engine.getAmeApplicationId;
2042 insert into ame_temp_insertions(
2043 transaction_id,
2044 application_id,
2045 insertion_order,
2046 order_type,
2047 parameter,
2048 description,
2049 name,
2050 item_class,
2051 item_id,
2052 approver_category,
2053 api_insertion,
2054 authority,
2055 effective_date,
2056 reason) values(
2057 transactionIdIn,
2058 ameApplicationId,
2059 insertionOrder,
2060 insertionIn.order_type,
2061 insertionIn.parameter,
2062 insertionIn.description,
2063 approverIn.name,
2064 approverIn.item_class,
2065 approverIn.item_id,
2066 approverIn.approver_category,
2067 insertionIn.api_insertion,
2068 insertionIn.authority,
2069 sysdate,
2070 ame_approver_deviation_pkg.insertReason
2071 );
2072 /* Cycle the engine to account for changes in the insertions table. */
2073 ame_engine.updateTransactionState(isTestTransactionIn => false,
2074 isLocalTransactionIn => false,
2075 fetchConfigVarsIn => true,
2076 fetchOldApproversIn => true,
2077 fetchInsertionsIn => true,
2078 fetchDeletionsIn => true,
2079 fetchAttributeValuesIn => true,
2080 fetchInactiveAttValuesIn => false,
2081 processProductionActionsIn => false,
2082 processProductionRulesIn => false,
2083 updateCurrentApproverListIn => true,
2084 updateOldApproverListIn => true,
2085 processPrioritiesIn => true,
2086 prepareItemDataIn => false,
2087 prepareRuleIdsIn => false,
2088 prepareRuleDescsIn => false,
2089 transactionIdIn => transactionIdIn,
2090 ameApplicationIdIn => null,
2091 fndApplicationIdIn => applicationIdIn,
2092 transactionTypeIdIn => transactionTypeIn );
2093 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2094 transactionIdIn => transactionIdIn,
2095 transactionTypeIdIn => transactionTypeIn);
2096 exception
2097 when ambiguousApproverException then
2098 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2099 transactionIdIn => transactionIdIn,
2100 transactionTypeIdIn => transactionTypeIn);
2101 errorCode := -20001;
2102 errorMessage := ambiguousApproverMessage;
2103 ame_util.runtimeException(packageNameIn => 'ame_api3',
2104 routineNameIn => 'insertApprover',
2105 exceptionNumberIn => errorCode,
2106 exceptionStringIn => errorMessage);
2107 raise_application_error(errorCode,
2108 errorMessage);
2109 when badInsertionTypeException then
2110 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2111 transactionIdIn => transactionIdIn,
2112 transactionTypeIdIn => transactionTypeIn);
2113 errorCode := -20001;
2114 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2115 messageNameIn => 'AME_400240_API_INV_ORDER_TYPE');
2116 ame_util.runtimeException(packageNameIn => 'ame_api3',
2117 routineNameIn => 'insertApprover',
2118 exceptionNumberIn => errorCode,
2119 exceptionStringIn => errorMessage);
2120 raise_application_error(errorCode,
2121 errorMessage);
2122 when badStatusException then
2123 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2124 transactionIdIn => transactionIdIn,
2125 transactionTypeIdIn => transactionTypeIn);
2126 errorCode := -20001;
2127 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2128 messageNameIn => 'AME_400241_API_NON_NULL_INSAPP');
2129 ame_util.runtimeException(packageNameIn => 'ame_api3',
2130 routineNameIn => 'insertApprover',
2131 exceptionNumberIn => errorCode,
2132 exceptionStringIn => errorMessage);
2133 raise_application_error(errorCode,
2134 errorMessage);
2135 when incompatibleApproverException then
2136 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2137 transactionIdIn => transactionIdIn,
2138 transactionTypeIdIn => transactionTypeIn);
2139 errorCode := -20001;
2140 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2141 messageNameIn => 'AME_400446_INCMPTBLE_APPR_INS');
2142 ame_util.runtimeException(packageNameIn => 'ame_api3',
2143 routineNameIn => 'insertApprover',
2144 exceptionNumberIn => errorCode,
2145 exceptionStringIn => errorMessage);
2146 raise_application_error(errorCode,
2147 errorMessage);
2148 when noApproverCategoryException then
2149 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2150 transactionIdIn => transactionIdIn,
2151 transactionTypeIdIn => transactionTypeIn);
2152 errorCode := -20001;
2153 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2154 messageNameIn => 'AME_400447_INVALID_APPR_CATG');
2155 ame_util.runtimeException(packageNameIn => 'ame_api3',
2156 routineNameIn => 'insertApprover',
2157 exceptionNumberIn => errorCode,
2158 exceptionStringIn => errorMessage);
2159 raise_application_error(errorCode,
2160 errorMessage);
2161 when others then
2162 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2163 transactionIdIn => transactionIdIn,
2164 transactionTypeIdIn => transactionTypeIn);
2165 ame_util.runtimeException(packageNameIn => 'ame_api3',
2166 routineNameIn => 'insertApprover',
2167 exceptionNumberIn => sqlcode,
2168 exceptionStringIn => sqlerrm);
2169 raise;
2170 end insertApprover;
2171 procedure parseApproverSource(approverSourceIn in varchar2,
2172 sourceDescriptionOut out nocopy varchar2,
2173 ruleIdListOut out nocopy ame_util.idList) as
2174 begin
2175 ame_util.parseSourceValue(sourceValueIn => approverSourceIn,
2176 sourceDescriptionOut => sourceDescriptionOut,
2177 ruleIdListOut => ruleIdListOut);
2178 end parseApproverSource;
2179 procedure suppressApprover(applicationIdIn in number,
2180 transactionTypeIn in varchar2,
2181 transactionIdIn in varchar2,
2182 approverIn in ame_util.approverRecord2) as
2183 ameApplicationId integer;
2184 approverIndex integer;
2185 approvers ame_util.approversTable2;
2186 allowDeletingOamApprovers ame_util.attributeValueType;
2187 badDeletionException exception;
2188 errorCode integer;
2189 errorMessage ame_util.longStringType;
2190 noMatchException exception;
2191 orderTypeLocation integer;
2192 parameterLocation integer;
2193 ruleIdList ame_util.idList;
2194 sourceDescription ame_util.stringType;
2195 sourceLength integer;
2196 tempOrderType ame_temp_insertions.order_type%type;
2197 tempParameter ame_temp_insertions.parameter%type;
2198 upperLimit integer;
2199 cursor getAnchorInsertions (parameterIn varchar2) is
2200 (select name
2201 from ame_temp_insertions
2202 where application_id = ame_engine.getAmeApplicationId
2203 and transaction_id = transactionIdIn
2204 and parameter = parameterIn);
2205 nameList ame_util.longStringList;
2206 anchorsExistException exception;
2207 cmpParameter ame_temp_insertions.parameter%type;
2208 anchorName varchar2(320);
2209 anchorList varchar2(1000);
2210 appr_rec_params varchar2(100);
2211 nullApprRecordFieldException Exception;
2212 begin
2213 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
2214 transactionIdIn => transactionIdIn,
2215 transactionTypeIdIn => transactionTypeIn);
2216 appr_rec_params:=null;
2217 if(approverIn.name is null) then
2218 appr_rec_params := 'name ';
2219 end if;
2220 if approverIn.item_class is null then
2221 appr_rec_params := appr_rec_params || ', item_class ';
2222 end if;
2223 if approverIn.item_id is null then
2224 appr_rec_params := appr_rec_params || ', item_id ';
2225 end if;
2226 if approverIn.action_type_id is null then
2227 appr_rec_params := appr_rec_params || ', action_type_id ';
2228 end if;
2229 if approverIn.group_or_chain_id is null then
2230 appr_rec_params := appr_rec_params || ', group_or_chain_id ';
2231 end if;
2232 if appr_rec_params is not null then
2233 raise nullApprRecordFieldException;
2234 end if;
2235 ame_engine.updateTransactionState(isTestTransactionIn => false,
2236 isLocalTransactionIn => false,
2237 fetchConfigVarsIn => true,
2238 fetchOldApproversIn => true,
2239 fetchInsertionsIn => true,
2240 fetchDeletionsIn => true,
2241 fetchAttributeValuesIn => true,
2242 fetchInactiveAttValuesIn => false,
2243 processProductionActionsIn => false,
2244 processProductionRulesIn => false,
2245 updateCurrentApproverListIn => true,
2246 updateOldApproverListIn => false,
2247 processPrioritiesIn => true,
2248 prepareItemDataIn => false,
2249 prepareRuleIdsIn => false,
2250 prepareRuleDescsIn => false,
2251 transactionIdIn => transactionIdIn,
2252 ameApplicationIdIn => null,
2253 fndApplicationIdIn => applicationIdIn,
2254 transactionTypeIdIn => transactionTypeIn );
2255 /* Try to match the approver. */
2256 ame_engine.getApprovers(approversOut => approvers);
2257 upperLimit := approvers.count;
2258 approverIndex := null;
2259 for i in 1 .. upperLimit loop
2260 if((approvers(i).approval_status is null or
2261 approvers(i).approval_status in
2262 (ame_util.exceptionStatus,
2263 ame_util.noResponseStatus,
2264 ame_util.notifiedStatus,
2265 ame_util.rejectStatus)) and
2266 ame_engine.approversMatch(approverRecord1In => approverIn,
2267 approverRecord2In => approvers(i))) then
2268 approverIndex := i;
2269 if approvers(approverIndex).api_insertion = ame_util.apiInsertion then
2270 anchorName := approvers(i).name;
2271 cmpParameter := approvers(i).name ||
2272 ame_util.fieldDelimiter ||
2273 approvers(i).item_class ||
2274 ame_util.fieldDelimiter ||
2275 approvers(i).item_id ||
2276 ame_util.fieldDelimiter ||
2277 approvers(i).action_type_id ||
2278 ame_util.fieldDelimiter ||
2279 approvers(i).group_or_chain_id ||
2280 ame_util.fieldDelimiter ||
2281 approvers(i).occurrence;
2282 open getAnchorInsertions(cmpParameter);
2283 fetch getAnchorInsertions bulk collect into nameList;
2284 if nameList.count > 0 then
2285 close getAnchorInsertions;
2286 raise anchorsExistException;
2287 end if;
2288 close getAnchorInsertions;
2289 end if;
2290 exit;
2291 end if;
2292 end loop;
2293 -- If there is no match, raise an exception.
2294 if(approverIndex is null) then
2295 raise noMatchException;
2296 end if;
2297 ameApplicationId := ame_engine.getAmeApplicationId;
2298 -- parse the source to see if the approver was inserted.
2299 ame_util.parseSourceValue(sourceValueIn => approvers(approverIndex).source,
2300 sourceDescriptionOut => sourceDescription,
2301 ruleIdListOut => ruleIdList);
2302 -- If the approver was inserted, delete the approver from ame_temp_insertions.
2303 -- If the approver was OAM generated, check whether deleting OAM-generated approvers
2304 -- is allowed. If so, record the deletion.
2305 --
2306 if(approvers(approverIndex).api_insertion = ame_util.oamGenerated or
2307 sourceDescription = ame_util.ruleGeneratedSource or
2308 sourceDescription = ame_util.surrogateSource) then
2309 allowDeletingOamApprovers :=
2310 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
2311 if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
2312 raise badDeletionException;
2313 end if;
2314 insert into ame_temp_deletions(
2315 transaction_id,
2316 application_id,
2317 name,
2318 item_class,
2319 item_id,
2320 approver_category,
2321 action_type_id,
2322 group_or_chain_id,
2323 occurrence,
2324 effective_date,
2325 reason) values(
2326 transactionIdIn,
2327 ameApplicationId,
2328 approvers(approverIndex).name,
2329 approvers(approverIndex).item_class,
2330 approvers(approverIndex).item_id,
2331 approvers(approverIndex).approver_category,
2332 approvers(approverIndex).action_type_id,
2333 approvers(approverIndex).group_or_chain_id,
2334 approvers(approverIndex).occurrence,
2335 sysdate,
2336 ame_approver_deviation_pkg.suppressReason
2337 );
2338 else
2339 for i in 1 .. approvers.count loop
2340 if approvers(i).name = approverIn.name and
2341 approvers(i).item_class = approverIn.item_class and
2342 approvers(i).item_id = approverIn.item_id then
2343 ame_engine.updateInsertions(indexIn => i);
2344 end if;
2345 end loop;
2346 delete from ame_temp_insertions
2347 where
2348 application_id = ameApplicationId and
2349 transaction_id = transactionIdIn and
2350 name = approvers(approverIndex).name and
2351 item_class = approvers(approverIndex).item_class and
2352 item_id = approvers(approverIndex).item_id ;
2353 end if;
2354 -- Cycle the engine to account for changes in the deletions table.
2355 ame_engine.updateTransactionState(isTestTransactionIn => false,
2356 isLocalTransactionIn => false,
2357 fetchConfigVarsIn => true,
2358 fetchOldApproversIn => true,
2359 fetchInsertionsIn => true,
2360 fetchDeletionsIn => true,
2361 fetchAttributeValuesIn => true,
2362 fetchInactiveAttValuesIn => false,
2363 processProductionActionsIn => false,
2364 processProductionRulesIn => false,
2365 updateCurrentApproverListIn => true,
2366 updateOldApproverListIn => true,
2367 processPrioritiesIn => true,
2368 prepareItemDataIn => false,
2369 prepareRuleIdsIn => false,
2370 prepareRuleDescsIn => false,
2371 transactionIdIn => transactionIdIn,
2372 ameApplicationIdIn => null,
2373 fndApplicationIdIn => applicationIdIn,
2374 transactionTypeIdIn => transactionTypeIn );
2375 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2376 transactionIdIn => transactionIdIn,
2377 transactionTypeIdIn => transactionTypeIn);
2378 exception
2379 when anchorsExistException then
2380 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2381 transactionIdIn => transactionIdIn,
2382 transactionTypeIdIn => transactionTypeIn);
2383 anchorList := '';
2384 for i in 1 .. nameList.count loop
2385 anchorList := anchorList || ame_approver_type_pkg.getApproverDescription(nameList(i));
2386 if i <> nameList.count then
2387 anchorList := anchorList || '; ';
2388 end if;
2389 end loop;
2390 errorCode := -20001;
2391 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2392 messageNameIn => 'AME_400651_ANCHORS_EXIST',
2393 tokenNameOneIn => 'APPROVER',
2394 tokenValueOneIn => ame_approver_type_pkg.getApproverDescription(anchorName),
2395 tokenNameTwoIn => 'ANCHORED_APPROVERS',
2396 tokenValueTwoIn => anchorList);
2397 ame_util.runtimeException(packageNameIn => 'ame_api3',
2398 routineNameIn => 'suppressApprover',
2399 exceptionNumberIn => errorCode,
2400 exceptionStringIn => errorMessage);
2401 raise_application_error(errorCode,
2402 errorMessage);
2403 when nullApprRecordFieldException then
2404 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2405 transactionIdIn => transactionIdIn,
2406 transactionTypeIdIn => transactionTypeIn);
2407 errorCode := -20001;
2408 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2409 messageNameIn => 'AME_400814_NULL_SUPPR_APPR_FLD',
2410 tokenNameOneIn => 'APPROVER_REC_PARAMS',
2411 tokenValueOneIn=>appr_rec_params);
2412 ame_util.runtimeException(packageNameIn => 'ame_api3',
2413 routineNameIn => 'suppressApprover',
2414 exceptionNumberIn => errorCode,
2415 exceptionStringIn => errorMessage);
2416 raise_application_error(errorCode,
2417 errorMessage);
2418 when badDeletionException then
2419 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2420 transactionIdIn => transactionIdIn,
2421 transactionTypeIdIn => transactionTypeIn);
2422 errorCode := -20001;
2423 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2424 messageNameIn => 'AME_400236_API_ADRGA_TRUE');
2425 ame_util.runtimeException(packageNameIn => 'ame_api3',
2426 routineNameIn => 'suppressApprover',
2427 exceptionNumberIn => errorCode,
2428 exceptionStringIn => errorMessage);
2429 raise_application_error(errorCode,
2430 errorMessage);
2431 when noMatchException then
2432 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2433 transactionIdIn => transactionIdIn,
2434 transactionTypeIdIn => transactionTypeIn);
2435 errorCode := -20001;
2436 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2437 messageNameIn => 'AME_400237_API_NO MATCH_APPR');
2438 ame_util.runtimeException(packageNameIn => 'ame_api3',
2439 routineNameIn => 'suppressApprover',
2440 exceptionNumberIn => errorCode,
2441 exceptionStringIn => errorMessage);
2442 raise_application_error(errorCode,
2443 errorMessage);
2444 when others then
2445 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
2446 transactionIdIn => transactionIdIn,
2447 transactionTypeIdIn => transactionTypeIn);
2448 ame_util.runtimeException(packageNameIn => 'ame_api3',
2449 routineNameIn => 'suppressApprover',
2450 exceptionNumberIn => sqlcode,
2451 exceptionStringIn => sqlerrm);
2452 raise;
2453 end suppressApprover;
2454 procedure suppressApprovers(applicationIdIn in integer,
2455 transactionTypeIn in varchar2,
2456 transactionIdIn in varchar2,
2457 approversIn in ame_util.approversTable2) as
2458 currentApproverInIndex integer;
2459 lastApproverInIndex integer;
2460 nextApproverInIndex integer;
2461 begin
2462 lastApproverInIndex := approversIn.last;
2463 currentApproverInIndex := approversIn.first;
2464 --
2465 -- This procedure should always depend on suppressApprovers, so that we don't need to repeat its
2466 -- error-checking logic here.
2467 --
2468 loop
2469 suppressApprover(applicationIdIn => applicationIdIn,
2470 transactionIdIn => transactionIdIn,
2471 approverIn => approversIn(currentApproverInIndex),
2472 transactionTypeIn => transactionTypeIn);
2473 if(currentApproverInIndex = lastApproverInIndex) then
2474 exit;
2475 end if;
2476 currentApproverInIndex := approversIn.next(currentApproverInIndex);
2477 end loop;
2478 exception
2479 when others then
2480 ame_util.runtimeException(packageNameIn => 'ame_api3',
2481 routineNameIn => 'suppressApprovers',
2482 exceptionNumberIn => sqlcode,
2483 exceptionStringIn => sqlerrm);
2484 raise;
2485 end suppressApprovers;
2486 end ame_api3;