[Home] [Help]
PACKAGE BODY: APPS.AME_API2
Source
1 package body ame_api2 as
2 /* $Header: ameeapi2.pkb 120.11 2011/05/17 11:42:40 nchinnam ship $ */
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 validateApprover(approverIn in ame_util.approverRecord2)
9 return boolean as
10 tempCount integer;
11 begin
12 /* Just check if the employee is current. */
13 if approverIn.name is null then
14 return(false);
15 end if;
16 select count(*)
17 into tempCount
18 from wf_roles
19 where name = approverIn.name and
20 status = 'ACTIVE' and
21 (expiration_date is null or
22 sysdate < expiration_date ) ; /* Don't use tempEffectiveRuleDate here. */
23 if(tempCount = 0) then
24 return(false);
25 end if;
26 return(true);
27 exception
28 when others then
29 ame_util.runtimeException(packageNameIn => 'ame_api2',
30 routineNameIn => 'validateApprover',
31 exceptionNumberIn => sqlcode,
32 exceptionStringIn => sqlerrm);
33 raise;
34 return(false);
35 end validateApprover;
36 /* procedures */
37 procedure clearAllApprovals(applicationIdIn in number,
38 transactionTypeIn in varchar2,
39 transactionIdIn in varchar2)as
40 ameAppId integer;
41 begin
42 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
43 transactionIdIn => transactionIdIn,
44 transactionTypeIdIn => transactionTypeIn);
45 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
46 transactionTypeIdIn => transactionTypeIn);
47 delete from ame_temp_old_approver_lists
48 where
49 application_id = ameAppId and
50 transaction_id = transactionIdIn;
51 delete from ame_temp_insertions
52 where
53 application_id = ameAppId and
54 transaction_id = transactionIdIn;
55 delete from ame_temp_deletions
56 where
57 application_id = ameAppId and
58 transaction_id = transactionIdIn;
59 /* update all existing history rows from the Approval Notification History table
60 to indicate the rows were cleared */
61 update AME_TRANS_APPROVAL_HISTORY set
62 date_cleared = sysdate
63 where
64 application_id = ameAppId and
65 transaction_id = transactionIdIn
66 and date_cleared is null;
67 ame_approver_deviation_pkg.clearDeviationState(
68 applicationIdIn => ameAppId
69 ,transactionIdIn => transactionIdIn );
70 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
71 transactionIdIn => transactionIdIn,
72 transactionTypeIdIn => transactionTypeIn);
73 exception
74 when others then
75 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
76 transactionIdIn => transactionIdIn,
77 transactionTypeIdIn => transactionTypeIn);
78 ame_util.runtimeException(packageNameIn => 'ame_api2',
79 routineNameIn => 'clearAllApprovals',
80 exceptionNumberIn => sqlcode,
81 exceptionStringIn => sqlerrm);
82 raise;
83 end clearAllApprovals;
84 procedure getAdminApprover(applicationIdIn in number,
85 transactionTypeIn in varchar2,
86 adminApproverOut out nocopy ame_util.approverRecord2)as
87 badAdminApproverException exception;
88 configVarValue ame_config_vars.variable_value%type;
89 configVarLength integer;
90 errorCode integer;
91 errorMessage ame_util.longStringType;
92 tempAmeApplicationId integer;
93 l_admin_apr varchar2(100);
94 l_level number;
95 l_count number;
96 l_token varchar2(100);
97 l_txn_type varchar2(200);
98 begin
99 if ( applicationIdIn is null or transactionTypeIn is null) then
100 adminApproverOut.name := ame_util.getAdminName;
101 else
102 select application_id
103 into tempAmeApplicationId
104 from ame_calling_apps
105 where fnd_application_id = applicationIdIn and
106 transaction_type_id = transactionTypeIn and
107 sysdate between start_date and
108 nvl(end_date - ame_util.oneSecond, sysdate);
109 adminApproverOut.name := ame_util.getAdminName(applicationIdIn => tempAmeApplicationId);
110 end if;
111 l_admin_apr := adminApproverOut.name;
112 begin
113 if not ame_approver_type_pkg.validateApprover(l_admin_apr) then
114 l_count := 0;
115 select count(*)
116 into l_count
117 from ame_config_vars
118 where variable_name = ame_util.adminApproverConfigVar and
119 application_id = tempAmeApplicationId and
120 variable_value = l_admin_apr and
121 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate) ;
122 if l_count > 0 then
123 l_level :=1;
124 else
125 l_level := 0;
126 raise badAdminApproverException;
127 end if;
128 if l_level > 0 then
129 select variable_value
130 into l_admin_apr
131 from ame_config_vars
132 where variable_name = ame_util.adminApproverConfigVar and
133 (application_id is null or application_id = 0) and
134 sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate) ;
135 adminApproverOut.name := l_admin_apr;
136 if not ame_approver_type_pkg.validateApprover(l_admin_apr) then
137 raise badAdminApproverException;
138 end if;
139 end if;
140 end if;
141 end;
142 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn =>adminApproverOut.name,
143 origSystemOut => adminApproverOut.orig_system,
144 origSystemIdOut => adminApproverOut.orig_system_id);
145 adminApproverOut.approval_status := ame_util.exceptionStatus;
146 adminApproverOut.item_class := null;
147 adminApproverOut.item_id := null;
148 adminApproverOut.item_class_order_number := null;
149 adminApproverOut.item_order_number := null;
150 adminApproverOut.sub_list_order_number := null;
151 adminApproverOut.action_type_order_number := null;
152 adminApproverOut.group_or_chain_order_number := null;
153 adminApproverOut.member_order_number := null;
154 adminApproverOut.approver_category := null;
155 adminApproverOut.authority := null;
156 adminApproverOut.api_insertion := null;
157 adminApproverOut.source := null;
158 adminApproverOut.action_type_id := null;
159 adminApproverOut.group_or_chain_id := null;
160 adminApproverOut.occurrence := null;
161 exception
162 when badAdminApproverException then
163 if l_level > 0 then
164 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
165 messageNameIn => 'AME_400839_API_NO_TXN_ADM_CNFG',
166 tokenNameOneIn => 'TXN_TYPE',
167 tokenValueOneIn => ame_admin_pkg.getApplicationName(tempAmeApplicationId));
168 else
169 errorMessage :=
170 ame_util.getMessage(applicationShortNameIn =>'PER',
171 messageNameIn => 'AME_400238_API_NO_DEF_ADM_CNFG');
172 end if;
173 errorCode := -20303;
174 ame_util.runtimeException(packageNameIn => 'ame_api2',
175 routineNameIn => 'getAdminApprover',
176 exceptionNumberIn => errorCode,
177 exceptionStringIn => errorMessage);
178 adminApproverOut.name := null;
179 adminApproverOut.approval_status := ame_util.exceptionStatus;
180 raise_application_error(errorCode,
181 errorMessage);
182 when others then
183 ame_util.runtimeException(packageNameIn => 'ame_api2',
184 routineNameIn => 'getAdminApprover',
185 exceptionNumberIn => sqlcode,
186 exceptionStringIn => sqlerrm);
187 adminApproverOut.name := null;
188 adminApproverOut.approval_status := ame_util.exceptionStatus;
189 raise;
190 end getAdminApprover;
191 procedure getAllApprovers1(applicationIdIn in number,
192 transactionTypeIn in varchar2,
193 transactionIdIn in varchar2,
194 approvalProcessCompleteYNOut out nocopy varchar2,
195 approversOut out nocopy ame_util.approversTable2,
196 itemIndexesOut out nocopy ame_util.idList,
197 itemClassesOut out nocopy ame_util.stringList,
198 itemIdsOut out nocopy ame_util.stringList,
199 itemSourcesOut out nocopy ame_util.longStringList)as
200 begin
201 ame_engine.updateTransactionState(isTestTransactionIn => false
202 ,isLocalTransactionIn => false
203 ,fetchConfigVarsIn => true
204 ,fetchOldApproversIn => true
205 ,fetchInsertionsIn => true
206 ,fetchDeletionsIn => true
207 ,fetchAttributeValuesIn => true
208 ,fetchInactiveAttValuesIn => false
209 ,processProductionActionsIn => false
210 ,processProductionRulesIn => false
211 ,updateCurrentApproverListIn => true
212 ,updateOldApproverListIn => false
213 ,processPrioritiesIn => true
214 ,prepareItemDataIn => true
215 ,prepareRuleIdsIn => false
216 ,prepareRuleDescsIn => false
217 ,prepareApproverTreeIn => true
218 ,transactionIdIn => transactionIdIn
219 ,ameApplicationIdIn => null
220 ,fndApplicationIdIn => applicationIdIn
221 ,transactionTypeIdIn => transactionTypeIn );
222 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
223 ame_engine.getApprovers(approversOut => approversOut);
224 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
225 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
226 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
227 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
228 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
229 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
230 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
231 approvalProcessCompleteYNOut := ame_util.booleanTrue;
232 else
233 approvalProcessCompleteYNOut := ame_util.booleanFalse;
234 end if;
235 else
236 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
237 end if;
238 exception
239 when others then
240 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
241 ame_util.runtimeException(packageNameIn => 'ame_api2',
242 routineNameIn => 'getAllApprovers1',
243 exceptionNumberIn => sqlcode,
244 exceptionStringIn => sqlerrm);
245 approvalProcessCompleteYNOut:= null;
246 approversOut.delete;
247 raise;
248 end getAllApprovers1;
249 procedure getAllApprovers2(applicationIdIn in number,
250 transactionTypeIn in varchar2,
251 transactionIdIn in varchar2,
252 approvalProcessCompleteYNOut out nocopy varchar2,
253 approversOut out nocopy ame_util.approversTable2,
254 itemIndexesOut out nocopy ame_util.idList,
255 itemClassesOut out nocopy ame_util.stringList,
256 itemIdsOut out nocopy ame_util.stringList,
257 itemSourcesOut out nocopy ame_util.longStringList,
258 productionIndexesOut out nocopy ame_util.idList,
259 variableNamesOut out nocopy ame_util.stringList,
260 variableValuesOut out nocopy ame_util.stringList)as
261 begin
262 ame_engine.updateTransactionState(isTestTransactionIn => false,
263 isLocalTransactionIn => false,
264 fetchConfigVarsIn => true,
265 fetchOldApproversIn => true,
266 fetchInsertionsIn => true,
267 fetchDeletionsIn => true,
268 fetchAttributeValuesIn => true,
269 fetchInactiveAttValuesIn => false,
270 processProductionActionsIn => true,
271 processProductionRulesIn => false,
272 updateCurrentApproverListIn => true,
273 updateOldApproverListIn => false,
274 processPrioritiesIn => true,
275 prepareItemDataIn => true,
276 prepareRuleIdsIn => false,
277 prepareRuleDescsIn => false,
278 prepareApproverTreeIn => true,
279 transactionIdIn => transactionIdIn,
280 ameApplicationIdIn => null,
281 fndApplicationIdIn => applicationIdIn,
282 transactionTypeIdIn => transactionTypeIn );
283 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
284 ame_engine.getApprovers(approversOut => approversOut);
285 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
286 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
287 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
288 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
289 ame_engine.getProductionIndexes(productionIndexesOut => productionIndexesOut);
290 ame_engine.getVariableNames(variableNamesOut=> variableNamesOut);
291 ame_engine.getVariableValues(variableValuesOut => variableValuesOut);
292 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
293 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
294 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
295 approvalProcessCompleteYNOut := ame_util.booleanTrue;
296 else
297 approvalProcessCompleteYNOut := ame_util.booleanFalse;
298 end if;
299 else
300 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
301 end if;
302 exception
303 when others then
304 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
305 ame_util.runtimeException(packageNameIn => 'ame_api2',
306 routineNameIn => 'getAllApprovers2',
307 exceptionNumberIn => sqlcode,
308 exceptionStringIn => sqlerrm);
309 approvalProcessCompleteYNOut:= null;
310 approversOut.delete;
311 raise;
312 end getAllApprovers2;
313 procedure getAllApprovers3(applicationIdIn in number,
314 transactionTypeIn in varchar2,
315 transactionIdIn in varchar2,
316 approvalProcessCompleteYNOut out nocopy varchar2,
317 approversOut out nocopy ame_util.approversTable2,
318 itemIndexesOut out nocopy ame_util.idList,
319 itemClassesOut out nocopy ame_util.stringList,
320 itemIdsOut out nocopy ame_util.stringList,
321 itemSourcesOut out nocopy ame_util.longStringList,
322 productionIndexesOut out nocopy ame_util.idList,
323 variableNamesOut out nocopy ame_util.stringList,
324 variableValuesOut out nocopy ame_util.stringList,
325 transVariableNamesOut out nocopy ame_util.stringList,
326 transVariableValuesOut out nocopy ame_util.stringList)as
327 begin
328 ame_engine.updateTransactionState(isTestTransactionIn => false,
329 isLocalTransactionIn => false,
330 fetchConfigVarsIn => true,
331 fetchOldApproversIn => true,
332 fetchInsertionsIn => true,
333 fetchDeletionsIn => true,
334 fetchAttributeValuesIn => true,
335 fetchInactiveAttValuesIn => false,
336 processProductionActionsIn => true,
337 processProductionRulesIn => true,
338 updateCurrentApproverListIn => true,
339 updateOldApproverListIn => false,
340 processPrioritiesIn => true,
341 prepareItemDataIn => true,
342 prepareRuleIdsIn => false,
343 prepareRuleDescsIn => false,
344 prepareApproverTreeIn => true,
345 transactionIdIn => transactionIdIn,
346 ameApplicationIdIn => null,
347 fndApplicationIdIn => applicationIdIn,
348 transactionTypeIdIn => transactionTypeIn );
349 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
350 ame_engine.getApprovers(approversOut => approversOut);
351 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
352 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
353 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
354 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
355 ame_engine.getProductionIndexes(productionIndexesOut => productionIndexesOut);
356 ame_engine.getVariableNames(variableNamesOut=> variableNamesOut);
357 ame_engine.getVariableValues(variableValuesOut => variableValuesOut);
358 ame_engine.getTransVariableNames(transVariableNamesOut => transVariableNamesOut);
359 ame_engine.getTransVariableValues(transVariableValuesOut => transVariableValuesOut);
360 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
361 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
362 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
363 approvalProcessCompleteYNOut := ame_util.booleanTrue;
364 else
365 approvalProcessCompleteYNOut := ame_util.booleanFalse;
366 end if;
367 else
368 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
369 end if;
370 exception
371 when others then
372 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
373 ame_util.runtimeException(packageNameIn => 'ame_api2',
374 routineNameIn => 'getAllApprovers3',
375 exceptionNumberIn => sqlcode,
376 exceptionStringIn => sqlerrm);
377 approvalProcessCompleteYNOut:= null;
378 approversOut.delete;
379 raise;
380 end getAllApprovers3;
381 procedure getAllApprovers4(applicationIdIn in number,
382 transactionTypeIn in varchar2,
383 transactionIdIn in varchar2,
384 approvalProcessCompleteYNOut out nocopy varchar2,
385 approversOut out nocopy ame_util.approversTable2,
386 itemIndexesOut out nocopy ame_util.idList,
387 itemClassesOut out nocopy ame_util.stringList,
388 itemIdsOut out nocopy ame_util.stringList,
389 itemSourcesOut out nocopy ame_util.longStringList,
390 ruleIndexesOut out nocopy ame_util.idList,
391 sourceTypesOut out nocopy ame_util.stringList,
392 ruleIdsOut out nocopy ame_util.idList)as
393 begin
394 ame_engine.updateTransactionState(isTestTransactionIn => false,
395 isLocalTransactionIn => false,
396 fetchConfigVarsIn => true,
397 fetchOldApproversIn => true,
398 fetchInsertionsIn => true,
399 fetchDeletionsIn => true,
400 fetchAttributeValuesIn => true,
401 fetchInactiveAttValuesIn => false,
402 processProductionActionsIn => false,
403 processProductionRulesIn => false,
404 updateCurrentApproverListIn => true,
405 updateOldApproverListIn => false,
406 processPrioritiesIn => true,
407 prepareItemDataIn => true,
408 prepareRuleIdsIn => true,
409 prepareRuleDescsIn => false,
410 prepareApproverTreeIn => true,
411 transactionIdIn => transactionIdIn,
412 ameApplicationIdIn => null,
413 fndApplicationIdIn => applicationIdIn,
414 transactionTypeIdIn => transactionTypeIn );
415 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
416 ame_engine.getApprovers(approversOut => approversOut);
417 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
418 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
419 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
420 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
421 ame_engine.getRuleIndexes(ruleIndexesOut => ruleIndexesOut);
422 ame_engine.getSourceTypes(sourceTypesOut => sourceTypesOut);
423 ame_engine.getRuleIds(ruleIdsOut => ruleIdsOut);
424 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
425 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
426 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
427 approvalProcessCompleteYNOut := ame_util.booleanTrue;
428 else
429 approvalProcessCompleteYNOut := ame_util.booleanFalse;
430 end if;
431 else
432 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
433 end if;
434 exception
435 when others then
436 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
437 ame_util.runtimeException(packageNameIn => 'ame_api2',
438 routineNameIn => 'getAllApprovers4',
439 exceptionNumberIn => sqlcode,
440 exceptionStringIn => sqlerrm);
441 approvalProcessCompleteYNOut:= null;
442 approversOut.delete;
443 raise;
444 end getAllApprovers4;
445 procedure getAllApprovers5(applicationIdIn in number,
446 transactionTypeIn in varchar2,
447 transactionIdIn in varchar2,
448 approvalProcessCompleteYNOut out nocopy varchar2,
449 approversOut out nocopy ame_util.approversTable2,
450 itemIndexesOut out nocopy ame_util.idList,
451 itemClassesOut out nocopy ame_util.stringList,
452 itemIdsOut out nocopy ame_util.stringList,
453 itemSourcesOut out nocopy ame_util.longStringList,
454 ruleIndexesOut out nocopy ame_util.idList,
455 sourceTypesOut out nocopy ame_util.stringList,
456 ruleDescriptionsOut out nocopy ame_util.stringList) as
457 begin
458 ame_engine.updateTransactionState(isTestTransactionIn => false,
459 isLocalTransactionIn => false,
460 fetchConfigVarsIn => true,
461 fetchOldApproversIn => true,
462 fetchInsertionsIn => true,
463 fetchDeletionsIn => true,
464 fetchAttributeValuesIn => true,
465 fetchInactiveAttValuesIn => false,
466 processProductionActionsIn => false,
467 processProductionRulesIn => false,
468 updateCurrentApproverListIn => true,
469 updateOldApproverListIn => false,
470 processPrioritiesIn => true,
471 prepareItemDataIn => true,
472 prepareRuleIdsIn => true,
473 prepareRuleDescsIn => true,
474 prepareApproverTreeIn => true,
475 transactionIdIn => transactionIdIn,
476 ameApplicationIdIn => null,
477 fndApplicationIdIn => applicationIdIn,
478 transactionTypeIdIn => transactionTypeIn );
479 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
480 ame_engine.getApprovers(approversOut => approversOut);
481 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
482 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
483 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
484 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
485 ame_engine.getRuleIndexes(ruleIndexesOut => ruleIndexesOut);
486 ame_engine.getSourceTypes(sourceTypesOut => sourceTypesOut);
487 ame_engine.getRuleDescriptions(ruleDescriptionsOut => ruleDescriptionsOut);
488 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
489 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
490 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
491 approvalProcessCompleteYNOut := ame_util.booleanTrue;
492 else
493 approvalProcessCompleteYNOut := ame_util.booleanFalse;
494 end if;
495 else
496 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
497 end if;
498 exception
499 when others then
500 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
501 ame_util.runtimeException(packageNameIn => 'ame_api2',
502 routineNameIn => 'getAllApprovers5',
503 exceptionNumberIn => sqlcode,
504 exceptionStringIn => sqlerrm);
505 approvalProcessCompleteYNOut:= null;
506 approversOut.delete;
507 raise;
508 end getAllApprovers5;
509 procedure getAllApprovers6(applicationIdIn in number,
510 transactionTypeIn in varchar2,
511 transactionIdIn in varchar2,
512 approvalProcessCompleteYNOut out nocopy varchar2,
513 approversOut out nocopy ame_util.approversTable2,
514 itemIndexesOut out nocopy ame_util.idList,
515 itemClassesOut out nocopy ame_util.stringList,
516 itemIdsOut out nocopy ame_util.stringList,
517 itemSourcesOut out nocopy ame_util.longStringList,
518 ruleIndexesOut out nocopy ame_util.idList,
519 sourceTypesOut out nocopy ame_util.stringList,
520 ruleIdsOut out nocopy ame_util.idList,
521 ruleDescriptionsOut out nocopy ame_util.stringList) as
522 begin
523 ame_engine.updateTransactionState(isTestTransactionIn => false,
524 isLocalTransactionIn => false,
525 fetchConfigVarsIn => true,
526 fetchOldApproversIn => true,
527 fetchInsertionsIn => true,
528 fetchDeletionsIn => true,
529 fetchAttributeValuesIn => true,
530 fetchInactiveAttValuesIn => false,
531 processProductionActionsIn => false,
532 processProductionRulesIn => false,
533 updateCurrentApproverListIn => true,
534 updateOldApproverListIn => false,
535 processPrioritiesIn => true,
536 prepareItemDataIn => true,
537 prepareRuleIdsIn => true,
538 prepareRuleDescsIn => true,
539 prepareApproverTreeIn => true,
540 transactionIdIn => transactionIdIn,
541 ameApplicationIdIn => null,
542 fndApplicationIdIn => applicationIdIn,
543 transactionTypeIdIn => transactionTypeIn );
544 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
545 ame_engine.getApprovers(approversOut => approversOut);
546 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
547 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
548 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
549 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
550 ame_engine.getRuleIndexes(ruleIndexesOut => ruleIndexesOut);
551 ame_engine.getSourceTypes(sourceTypesOut => sourceTypesOut);
552 ame_engine.getRuleIds(ruleIdsOut => ruleIdsOut);
553 ame_engine.getRuleDescriptions(ruleDescriptionsOut => ruleDescriptionsOut);
554 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
555 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
556 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
557 approvalProcessCompleteYNOut := ame_util.booleanTrue;
558 else
559 approvalProcessCompleteYNOut := ame_util.booleanFalse;
560 end if;
561 else
562 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
563 end if;
564 exception
565 when others then
566 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
567 ame_util.runtimeException(packageNameIn => 'ame_api2',
568 routineNameIn => 'getAllApprovers6',
569 exceptionNumberIn => sqlcode,
570 exceptionStringIn => sqlerrm);
571 approvalProcessCompleteYNOut:= null;
572 approversOut.delete;
573 raise;
574 end getAllApprovers6;
575 procedure getAllApprovers7(applicationIdIn in number,
576 transactionTypeIn in varchar2,
577 transactionIdIn in varchar2,
578 approvalProcessCompleteYNOut out nocopy varchar2,
579 approversOut out nocopy ame_util.approversTable2) as
580 begin
581 ame_engine.updateTransactionState(isTestTransactionIn => false,
582 isLocalTransactionIn => false,
583 fetchConfigVarsIn => true,
584 fetchOldApproversIn => true,
585 fetchInsertionsIn => true,
586 fetchDeletionsIn => true,
587 fetchAttributeValuesIn => true,
588 fetchInactiveAttValuesIn => false,
589 processProductionActionsIn => false,
590 processProductionRulesIn => false,
591 updateCurrentApproverListIn => true,
592 updateOldApproverListIn => false,
593 processPrioritiesIn => true,
594 prepareItemDataIn => false,
595 prepareRuleIdsIn => false,
596 prepareRuleDescsIn => false,
597 prepareApproverTreeIn => true,
598 transactionIdIn => transactionIdIn,
599 ameApplicationIdIn => null,
600 fndApplicationIdIn => applicationIdIn,
601 transactionTypeIdIn => transactionTypeIn );
602 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
603 ame_engine.getApprovers(approversOut => approversOut);
604 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
605 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
606 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
607 approvalProcessCompleteYNOut := ame_util.booleanTrue;
608 else
609 approvalProcessCompleteYNOut := ame_util.booleanFalse;
610 end if;
611 else
612 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
613 end if;
614 exception
615 when others then
616 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
617 ame_util.runtimeException(packageNameIn => 'ame_api2',
618 routineNameIn => 'getAllApprovers7',
619 exceptionNumberIn => sqlcode,
620 exceptionStringIn => sqlerrm);
621 approvalProcessCompleteYNOut:= null;
622 approversOut.delete;
623 raise;
624 end getAllApprovers7;
625 procedure getAllItemApprovers1(applicationIdIn in number,
626 transactionTypeIn in varchar2,
627 transactionIdIn in varchar2,
628 itemClassIdIn in number,
629 itemIdIn in varchar2,
630 approvalProcessCompleteYNOut out nocopy varchar2,
631 approversOut out nocopy ame_util.approversTable2) as
632 approverCount integer;
633 approvers ame_util.approversTable2;
634 errorCode integer;
635 errorMessage ame_util.longStringType;
636 invalidItemClassIdException exception;
637 invalidItemException exception;
638 itemAppProcessCompleteYN ame_util.charList;
639 itemClasses ame_util.stringList;
640 itemClassName ame_util.stringType;
641 itemIds ame_util.stringList;
642 begin
643 ame_engine.updateTransactionState(isTestTransactionIn => false,
644 isLocalTransactionIn => false,
645 fetchConfigVarsIn => true,
646 fetchOldApproversIn => true,
647 fetchInsertionsIn => true,
648 fetchDeletionsIn => true,
649 fetchAttributeValuesIn => true,
650 fetchInactiveAttValuesIn => false,
651 processProductionActionsIn => false,
652 processProductionRulesIn => false,
653 updateCurrentApproverListIn => true,
654 updateOldApproverListIn => false,
655 processPrioritiesIn => true,
656 prepareItemDataIn => true,
657 prepareRuleIdsIn => false,
658 prepareRuleDescsIn => false,
659 prepareApproverTreeIn => true,
660 transactionIdIn => transactionIdIn,
661 ameApplicationIdIn => null,
662 fndApplicationIdIn => applicationIdIn,
663 transactionTypeIdIn => transactionTypeIn );
664 approvalProcessCompleteYNOut := null;
665 -- get the approvers
666 ame_engine.getApprovers(approversOut => approvers);
667 -- get the Item Details
668 ame_engine.getAllItemIds(itemIdsOut => itemIds);
669 ame_engine.getAllItemClasses(itemClassNamesOut => itemClasses);
670 ame_engine.getItemAppProcessCompleteYN(itemAppProcessCompleteYNOut => itemAppProcessCompleteYN);
671 -- get item class name
672 begin
673 itemClassName := ame_engine.getItemClassName(itemClassIdIn => itemClassIdIn);
674 exception
675 when others then
676 raise invalidItemClassIdException;
677 end;
678 for i in 1 .. itemIds.count loop
679 if (itemIds(i) = itemIdIn) and
680 ( itemClasses(i) = itemClassName) then
681 approvalProcessCompleteYNOut := itemAppProcessCompleteYN(i);
682 exit;
683 end if;
684 end loop;
685 if approvalProcessCompleteYNOut is null then
686 raise invalidItemException;
687 end if;
688 -- identify approvers for this item class and Item Id
689 approverCount := 0;
690 for i in 1..approvers.count loop
691 if (approvers(i).item_class = itemClassName and
692 approvers(i).item_id = itemIdIn ) then
693 approverCount := approverCount + 1;
694 ame_util.copyApproverRecord2(approverRecord2In => approvers(i),
695 approverRecord2Out => approversOut(approverCount) );
696 end if;
697 end loop;
698 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
699 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
700 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
701 approvalProcessCompleteYNOut := ame_util.booleanTrue;
702 else
703 approvalProcessCompleteYNOut := ame_util.booleanFalse;
704 end if;
705 else
706 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
707 end if;
708 exception
709 when invalidItemClassIdException then
710 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
711 errorCode := -20311;
712 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
713 messageNameIn => 'AME_400419_INVALID_IC') ;
714 ame_util.runtimeException(packageNameIn => 'ame_api2',
715 routineNameIn => 'getAllItemApprovers1',
716 exceptionNumberIn => errorCode,
717 exceptionStringIn => errorMessage);
718 approvalProcessCompleteYNOut := null;
719 raise_application_error(errorCode,
720 errorMessage);
721 when invalidItemException then
722 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
723 errorCode := -20312;
724 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
725 messageNameIn => 'AME_400420_INVALID_ITEM_ID') ;
726 ame_util.runtimeException(packageNameIn => 'ame_api2',
727 routineNameIn => 'getAllItemApprovers1',
728 exceptionNumberIn => errorCode,
729 exceptionStringIn => errorMessage);
730 approvalProcessCompleteYNOut := null;
731 raise_application_error(errorCode,
732 errorMessage);
733 when others then
734 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
735 ame_util.runtimeException(packageNameIn => 'ame_api2',
736 routineNameIn => 'getAllItemApprovers1',
737 exceptionNumberIn => sqlcode,
738 exceptionStringIn => sqlerrm);
739 approvalProcessCompleteYNOut:= null;
740 approversOut.delete;
741 raise;
742 end getAllItemApprovers1;
743 procedure getAllItemApprovers2(applicationIdIn in number,
744 transactionTypeIn in varchar2,
745 transactionIdIn in varchar2,
746 itemClassNameIn in varchar2,
747 itemIdIn in varchar2,
748 approvalProcessCompleteYNOut out nocopy varchar2,
749 approversOut out nocopy ame_util.approversTable2)as
750 approverCount integer;
751 approvers ame_util.approversTable2;
752 errorCode integer;
753 errorMessage ame_util.longStringType;
754 invalidItemClassIdException exception;
755 invalidItemException exception;
756 itemAppProcessCompleteYN ame_util.charList;
757 itemClasses ame_util.stringList;
758 itemIds ame_util.stringList;
759 begin
760 ame_engine.updateTransactionState(isTestTransactionIn => false,
761 isLocalTransactionIn => false,
762 fetchConfigVarsIn => true,
763 fetchOldApproversIn => true,
764 fetchInsertionsIn => true,
765 fetchDeletionsIn => true,
766 fetchAttributeValuesIn => true,
767 fetchInactiveAttValuesIn => false,
768 processProductionActionsIn => false,
769 processProductionRulesIn => false,
770 updateCurrentApproverListIn => true,
771 updateOldApproverListIn => false,
772 processPrioritiesIn => true,
773 prepareItemDataIn => true,
774 prepareRuleIdsIn => false,
775 prepareRuleDescsIn => false,
776 prepareApproverTreeIn => true,
777 transactionIdIn => transactionIdIn,
778 ameApplicationIdIn => null,
779 fndApplicationIdIn => applicationIdIn,
780 transactionTypeIdIn => transactionTypeIn );
781 approvalProcessCompleteYNOut := null;
782 -- get the approvers
783 ame_engine.getApprovers(approversOut => approvers);
784 -- get the Item Details
785 ame_engine.getAllItemIds(itemIdsOut => itemIds);
786 ame_engine.getAllItemClasses(itemClassNamesOut => itemClasses);
787 ame_engine.getItemAppProcessCompleteYN(itemAppProcessCompleteYNOut => itemAppProcessCompleteYN);
788 for i in 1 .. itemIds.count loop
789 if (itemIds(i) = itemIdIn) and
790 ( itemClasses(i) = itemClassNameIn) then
791 approvalProcessCompleteYNOut := itemAppProcessCompleteYN(i);
792 exit;
793 end if;
794 end loop;
795 if approvalProcessCompleteYNOut is null then
796 raise invalidItemException;
797 end if;
798 -- identify approvers for this item class and Item Id
799 approverCount := 0;
800 for i in 1..approvers.count loop
801 if (approvers(i).item_class = itemClassNameIn and
802 approvers(i).item_id = itemIdIn ) then
803 approverCount := approverCount + 1;
804 ame_util.copyApproverRecord2(approverRecord2In => approvers(i),
805 approverRecord2Out => approversOut(approverCount) );
806 end if;
807 end loop;
808 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
809 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
810 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
811 approvalProcessCompleteYNOut := ame_util.booleanTrue;
812 else
813 approvalProcessCompleteYNOut := ame_util.booleanFalse;
814 end if;
815 else
816 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
817 end if;
818 exception
819 when invalidItemException then
820 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
821 errorCode := -20312;
822 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
823 messageNameIn => 'AME_400420_INVALID_ITEM_ID') ;
824 ame_util.runtimeException(packageNameIn => 'ame_api2',
825 routineNameIn => 'getAllItemApprovers2',
826 exceptionNumberIn => errorCode,
827 exceptionStringIn => errorMessage);
828 approvalProcessCompleteYNOut := null;
829 raise_application_error(errorCode,
830 errorMessage);
831 when others then
832 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
833 ame_util.runtimeException(packageNameIn => 'ame_api2',
834 routineNameIn => 'getAllItemApprovers2',
835 exceptionNumberIn => sqlcode,
836 exceptionStringIn => sqlerrm);
837 approvalProcessCompleteYNOut:= null;
838 approversOut.delete;
839 raise;
840 end getAllItemApprovers2;
841 procedure getAndRecordAllApprovers(applicationIdIn in number,
842 transactionTypeIn in varchar2,
843 transactionIdIn in varchar2,
844 approvalProcessCompleteYNOut out nocopy varchar2,
845 approversOut out nocopy ame_util.approversTable2,
846 itemIndexesOut out nocopy ame_util.idList,
847 itemClassesOut out nocopy ame_util.stringList,
848 itemIdsOut out nocopy ame_util.stringList,
849 itemSourcesOut out nocopy ame_util.longStringList)as
850 begin
851 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
852 transactionIdIn => transactionIdIn,
853 transactionTypeIdIn => transactionTypeIn);
854 ame_engine.updateTransactionState(isTestTransactionIn => false,
855 isLocalTransactionIn => false,
856 fetchConfigVarsIn => true,
857 fetchOldApproversIn => true,
858 fetchInsertionsIn => true,
859 fetchDeletionsIn => true,
860 fetchAttributeValuesIn => true,
861 fetchInactiveAttValuesIn => false,
862 processProductionActionsIn => false,
863 processProductionRulesIn => false,
864 updateCurrentApproverListIn => true,
865 updateOldApproverListIn => true,
866 processPrioritiesIn => true,
867 prepareItemDataIn => true,
868 prepareRuleIdsIn => false,
869 prepareRuleDescsIn => false,
870 prepareApproverTreeIn => true,
871 transactionIdIn => transactionIdIn,
872 ameApplicationIdIn => null,
873 fndApplicationIdIn => applicationIdIn,
874 transactionTypeIdIn => transactionTypeIn );
875 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
876 ame_engine.getApprovers(approversOut => approversOut);
877 ame_engine.getItemIndexes(itemIndexesOut => itemIndexesOut);
878 ame_engine.getItemClasses(itemClassesOut => itemClassesOut);
879 ame_engine.getItemIds(itemIdsOut => itemIdsOut);
880 ame_engine.getItemSources(itemSourcesOut => itemSourcesOut);
881 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
882 transactionIdIn => transactionIdIn,
883 transactionTypeIdIn => transactionTypeIn);
884 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
885 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
886 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
887 approvalProcessCompleteYNOut := ame_util.booleanTrue;
888 else
889 approvalProcessCompleteYNOut := ame_util.booleanFalse;
890 end if;
891 else
892 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
893 end if;
894 exception
895 when others then
896 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
897 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
898 transactionIdIn => transactionIdIn,
899 transactionTypeIdIn => transactionTypeIn);
900 ame_util.runtimeException(packageNameIn => 'ame_api2',
901 routineNameIn => 'getAndRecordAllApprovers',
902 exceptionNumberIn => sqlcode,
903 exceptionStringIn => sqlerrm);
904 approvalProcessCompleteYNOut:= null;
905 approversOut.delete;
906 raise;
907 end getAndRecordAllApprovers;
908 procedure getItemStatus1(applicationIdIn in number,
909 transactionTypeIn in varchar2,
910 transactionIdIn in varchar2,
911 itemClassIdIn in integer,
912 itemIdIn in varchar2,
913 approvalProcessCompleteYNOut out nocopy varchar2)as
914 approvers ame_util.approversTable;
915 errorCode integer;
916 errorMessage ame_util.longStringType;
917 invalidItemClassIdException exception;
918 invalidItemException exception;
919 itemAppProcessCompleteYN ame_util.charList;
920 itemClasses ame_util.stringList;
921 itemClassName ame_util.stringType;
922 itemIds ame_util.stringList;
923 begin
924 ame_engine.updateTransactionState(isTestTransactionIn => false,
925 isLocalTransactionIn => false,
926 fetchConfigVarsIn => true,
927 fetchOldApproversIn => true,
928 fetchInsertionsIn => true,
929 fetchDeletionsIn => true,
930 fetchAttributeValuesIn => true,
931 fetchInactiveAttValuesIn => false,
932 processProductionActionsIn => false,
933 processProductionRulesIn => false,
934 updateCurrentApproverListIn => true,
935 updateOldApproverListIn => false,
936 processPrioritiesIn => true,
937 prepareItemDataIn => true,
938 prepareRuleIdsIn => false,
939 prepareRuleDescsIn => false,
940 transactionIdIn => transactionIdIn,
941 ameApplicationIdIn => null,
942 fndApplicationIdIn => applicationIdIn,
943 transactionTypeIdIn => transactionTypeIn );
944 approvalProcessCompleteYNOut := null;
945 -- get the Item Details
946 ame_engine.getAllItemIds(itemIdsOut => itemIds);
947 ame_engine.getAllItemClasses(itemClassNamesOut => itemClasses);
948 ame_engine.getItemAppProcessCompleteYN(itemAppProcessCompleteYNOut => itemAppProcessCompleteYN);
949 -- get item class name
950 begin
951 itemClassName := ame_engine.getItemClassName(itemClassIdIn => itemClassIdIn);
952 exception
953 when others then
954 raise invalidItemClassIdException;
955 end;
956 for i in 1 .. itemIds.count loop
957 if (itemIds(i) = itemIdIn) and
958 ( itemClasses(i) = itemClassName) then
959 approvalProcessCompleteYNOut := itemAppProcessCompleteYN(i);
960 exit;
961 end if;
962 end loop;
963 if approvalProcessCompleteYNOut is null then
964 raise invalidItemException;
965 end if;
966 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
967 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
968 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
969 approvalProcessCompleteYNOut := ame_util.booleanTrue;
970 else
971 approvalProcessCompleteYNOut := ame_util.booleanFalse;
972 end if;
973 else
974 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
975 end if;
976 exception
977 when invalidItemClassIdException then
978 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
979 errorCode := -20311;
980 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
981 messageNameIn => 'AME_400419_INVALID_IC') ;
982 ame_util.runtimeException(packageNameIn => 'ame_api2',
983 routineNameIn => 'getItemStatus1',
984 exceptionNumberIn => errorCode,
985 exceptionStringIn => errorMessage);
986 approvalProcessCompleteYNOut := null;
987 raise_application_error(errorCode,
988 errorMessage);
989 when invalidItemException then
990 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
991 errorCode := -20312;
992 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
993 messageNameIn => 'AME_400420_INVALID_ITEM_ID') ;
994 ame_util.runtimeException(packageNameIn => 'ame_api2',
995 routineNameIn => 'getItemStatus1',
996 exceptionNumberIn => errorCode,
997 exceptionStringIn => errorMessage);
998 approvalProcessCompleteYNOut := null;
999 raise_application_error(errorCode,
1000 errorMessage);
1001 when others then
1002 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1003 ame_util.runtimeException(packageNameIn => 'ame_api2',
1004 routineNameIn => 'getItemStatus1',
1005 exceptionNumberIn => sqlcode,
1006 exceptionStringIn => sqlerrm);
1007 approvalProcessCompleteYNOut:= null;
1008 raise;
1009 end getItemStatus1;
1010 procedure getItemStatus2(applicationIdIn in number,
1011 transactionTypeIn in varchar2,
1012 transactionIdIn in varchar2,
1013 itemClassNameIn in varchar2,
1014 itemIdIn in varchar2,
1015 approvalProcessCompleteYNOut out nocopy varchar2)as
1016 approvers ame_util.approversTable;
1017 errorCode integer;
1018 errorMessage ame_util.longStringType;
1019 invalidItemClassIdException exception;
1020 invalidItemException exception;
1021 itemAppProcessCompleteYN ame_util.charList;
1022 itemClasses ame_util.stringList;
1023 itemIds ame_util.stringList;
1024 begin
1025 ame_engine.updateTransactionState(isTestTransactionIn => false,
1026 isLocalTransactionIn => false,
1027 fetchConfigVarsIn => true,
1028 fetchOldApproversIn => true,
1029 fetchInsertionsIn => true,
1030 fetchDeletionsIn => true,
1031 fetchAttributeValuesIn => true,
1032 fetchInactiveAttValuesIn => false,
1033 processProductionActionsIn => false,
1034 processProductionRulesIn => false,
1035 updateCurrentApproverListIn => true,
1036 updateOldApproverListIn => false,
1037 processPrioritiesIn => true,
1038 prepareItemDataIn => true,
1039 prepareRuleIdsIn => false,
1040 prepareRuleDescsIn => false,
1041 transactionIdIn => transactionIdIn,
1042 ameApplicationIdIn => null,
1043 fndApplicationIdIn => applicationIdIn,
1044 transactionTypeIdIn => transactionTypeIn );
1045 approvalProcessCompleteYNOut := null;
1046 -- get the Item Details
1047 ame_engine.getAllItemIds(itemIdsOut => itemIds);
1048 ame_engine.getAllItemClasses(itemClassNamesOut => itemClasses);
1049 ame_engine.getItemAppProcessCompleteYN(itemAppProcessCompleteYNOut => itemAppProcessCompleteYN);
1050 -- get item class name
1051 for i in 1 .. itemIds.count loop
1052 if (itemIds(i) = itemIdIn) and
1053 ( itemClasses(i) = itemClassNameIn) then
1054 approvalProcessCompleteYNOut := itemAppProcessCompleteYN(i);
1055 exit;
1056 end if;
1057 end loop;
1058 if approvalProcessCompleteYNOut is null then
1059 raise invalidItemException;
1060 end if;
1061 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1062 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1063 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1064 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1065 else
1066 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1067 end if;
1068 else
1069 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1070 end if;
1071 exception
1072 when invalidItemClassIdException then
1073 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1074 errorCode := -20311;
1075 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1076 messageNameIn => 'AME_400419_INVALID_IC') ;
1077 ame_util.runtimeException(packageNameIn => 'ame_api2',
1078 routineNameIn => 'getItemStatus2',
1079 exceptionNumberIn => errorCode,
1080 exceptionStringIn => errorMessage);
1081 approvalProcessCompleteYNOut := null;
1082 raise_application_error(errorCode,
1083 errorMessage);
1084 when invalidItemException then
1085 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1086 errorCode := -20312;
1087 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1088 messageNameIn => 'AME_400420_INVALID_ITEM_ID') ;
1089 ame_util.runtimeException(packageNameIn => 'ame_api2',
1090 routineNameIn => 'getItemStatus2',
1091 exceptionNumberIn => errorCode,
1092 exceptionStringIn => errorMessage);
1093 approvalProcessCompleteYNOut := null;
1094 raise_application_error(errorCode,
1095 errorMessage);
1096 when others then
1097 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1098 ame_util.runtimeException(packageNameIn => 'ame_api2',
1099 routineNameIn => 'getItemStatus2',
1100 exceptionNumberIn => sqlcode,
1101 exceptionStringIn => sqlerrm);
1102 approvalProcessCompleteYNOut:= null;
1103 raise;
1104 end getItemStatus2;
1105 procedure getItemStatuses(applicationIdIn in number,
1106 transactionTypeIn in varchar2,
1107 transactionIdIn in varchar2,
1108 itemClassNamesOut out nocopy ame_util.stringList,
1109 itemIdsOut out nocopy ame_util.stringList,
1110 approvalProcessesCompleteYNOut out nocopy ame_util.charList)as
1111 i number;
1112 begin
1113 ame_engine.updateTransactionState(isTestTransactionIn => false,
1114 isLocalTransactionIn => false,
1115 fetchConfigVarsIn => true,
1116 fetchOldApproversIn => true,
1117 fetchInsertionsIn => true,
1118 fetchDeletionsIn => true,
1119 fetchAttributeValuesIn => true,
1120 fetchInactiveAttValuesIn => false,
1121 processProductionActionsIn => false,
1122 processProductionRulesIn => false,
1123 updateCurrentApproverListIn => true,
1124 updateOldApproverListIn => false,
1125 processPrioritiesIn => true,
1126 prepareItemDataIn => true,
1127 prepareRuleIdsIn => false,
1128 prepareRuleDescsIn => false,
1129 transactionIdIn => transactionIdIn,
1130 ameApplicationIdIn => null,
1131 fndApplicationIdIn => applicationIdIn,
1132 transactionTypeIdIn => transactionTypeIn );
1133 ame_engine.getAllItemIds(itemIdsOut => itemIdsOut);
1134 ame_engine.getAllItemClasses(itemClassNamesOut => itemClassNamesOut);
1135 ame_engine.getItemAppProcessCompleteYN(itemAppProcessCompleteYNOut => approvalProcessesCompleteYNOut);
1136 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1137 for i in 1 .. approvalProcessesCompleteYNOut.count loop
1138 if approvalProcessesCompleteYNOut(i) = ame_util2.completeFullyApproved
1139 or approvalProcessesCompleteYNOut(i) = ame_util2.completeNoApprovers then
1140 approvalProcessesCompleteYNOut(i) := ame_util.booleanTrue;
1141 else
1142 approvalProcessesCompleteYNOut(i) := ame_util.booleanFalse;
1143 end if;
1144 end loop;
1145 else
1146 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1147 end if;
1148 exception
1149 when others then
1150 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1151 ame_util.runtimeException(packageNameIn => 'ame_api2',
1152 routineNameIn => 'getItemStatuses',
1153 exceptionNumberIn => sqlcode,
1154 exceptionStringIn => sqlerrm);
1155 itemClassNamesOut.delete;
1156 itemIdsOut.delete;
1157 approvalProcessesCompleteYNOut.delete;
1158 raise;
1159 end getItemStatuses;
1160 procedure getNextApprovers1(applicationIdIn in number,
1161 transactionTypeIn in varchar2,
1162 transactionIdIn in varchar2,
1163 flagApproversAsNotifiedIn in varchar2 default ame_util.booleanTrue,
1164 approvalProcessCompleteYNOut out nocopy varchar2,
1165 nextApproversOut out nocopy ame_util.approversTable2,
1166 itemIndexesOut out nocopy ame_util.idList,
1167 itemClassesOut out nocopy ame_util.stringList,
1168 itemIdsOut out nocopy ame_util.stringList,
1169 itemSourcesOut out nocopy ame_util.longStringList)as
1170 dummyIdList ame_util.idList;
1171 dummyStringList ame_util.stringList;
1172 begin
1173 ame_engine.getNextApprovers(
1174 applicationIdIn => applicationIdIn
1175 ,transactionTypeIn => transactionTypeIn
1176 ,transactionIdIn => transactionIdIn
1177 ,nextApproversType => 1
1178 ,flagApproversAsNotifiedIn => flagApproversAsNotifiedIn
1179 ,approvalProcessCompleteYNOut => approvalProcessCompleteYNOut
1180 ,nextApproversOut => nextApproversOut
1181 ,itemIndexesOut => itemIndexesOut
1182 ,itemClassesOut => itemClassesOut
1183 ,itemIdsOut => itemIdsOut
1184 ,itemSourcesOut => itemSourcesOut
1185 ,productionIndexesOut => dummyIdList
1186 ,variableNamesOut => dummyStringList
1187 ,variableValuesOut => dummyStringList
1188 ,transVariableNamesOut => dummyStringList
1189 ,transVariableValuesOut => dummyStringList);
1190 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1191 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1192 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1193 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1194 else
1195 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1196 end if;
1197 else
1198 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1199 end if;
1200 exception
1201 when others then
1202 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1203 ame_util.runtimeException(packageNameIn => 'ame_api2',
1204 routineNameIn => 'getNextApprovers1',
1205 exceptionNumberIn => sqlcode,
1206 exceptionStringIn => sqlerrm);
1207 approvalProcessCompleteYNOut:= null;
1208 nextApproversOut.delete;
1209 itemIndexesOut.delete;
1210 itemClassesOut.delete;
1211 itemIdsOut.delete;
1212 itemSourcesOut.delete;
1213 raise;
1214 end getNextApprovers1;
1215 procedure getNextApprovers2(applicationIdIn in number,
1216 transactionTypeIn in varchar2,
1217 transactionIdIn in varchar2,
1218 flagApproversAsNotifiedIn in varchar2 default ame_util.booleanTrue,
1219 approvalProcessCompleteYNOut out nocopy varchar2,
1220 nextApproversOut out nocopy ame_util.approversTable2,
1221 itemIndexesOut out nocopy ame_util.idList,
1222 itemClassesOut out nocopy ame_util.stringList,
1223 itemIdsOut out nocopy ame_util.stringList,
1224 itemSourcesOut out nocopy ame_util.longStringList,
1225 productionIndexesOut out nocopy ame_util.idList,
1226 variableNamesOut out nocopy ame_util.stringList,
1227 variableValuesOut out nocopy ame_util.stringList) as
1228 dummyStringList ame_util.stringList;
1229 begin
1230 ame_engine.getNextApprovers(
1231 applicationIdIn => applicationIdIn
1232 ,transactionTypeIn => transactionTypeIn
1233 ,transactionIdIn => transactionIdIn
1234 ,nextApproversType => 2
1235 ,flagApproversAsNotifiedIn => flagApproversAsNotifiedIn
1236 ,approvalProcessCompleteYNOut => approvalProcessCompleteYNOut
1237 ,nextApproversOut => nextApproversOut
1238 ,itemIndexesOut => itemIndexesOut
1239 ,itemClassesOut => itemClassesOut
1240 ,itemIdsOut => itemIdsOut
1241 ,itemSourcesOut => itemSourcesOut
1242 ,productionIndexesOut => productionIndexesOut
1243 ,variableNamesOut => variableNamesOut
1244 ,variableValuesOut => variableValuesOut
1245 ,transVariableNamesOut => dummyStringList
1246 ,transVariableValuesOut => dummyStringList);
1247 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1248 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1249 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1250 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1251 else
1252 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1253 end if;
1254 else
1255 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1256 end if;
1257 exception
1258 when others then
1259 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1260 ame_util.runtimeException(packageNameIn => 'ame_api2',
1261 routineNameIn => 'getNextApprovers2',
1262 exceptionNumberIn => sqlcode,
1263 exceptionStringIn => sqlerrm);
1264 approvalProcessCompleteYNOut:= null;
1265 nextApproversOut.delete;
1266 itemIndexesOut.delete;
1267 itemClassesOut.delete;
1268 itemIdsOut.delete;
1269 itemSourcesOut.delete;
1270 productionIndexesOut.delete;
1271 variableNamesOut.delete;
1272 variableValuesOut.delete;
1273 raise;
1274 end getNextApprovers2;
1275 procedure getNextApprovers3(applicationIdIn in number,
1276 transactionTypeIn in varchar2,
1277 transactionIdIn in varchar2,
1278 flagApproversAsNotifiedIn in varchar2 default ame_util.booleanTrue,
1279 approvalProcessCompleteYNOut out nocopy varchar2,
1280 nextApproversOut out nocopy ame_util.approversTable2,
1281 itemIndexesOut out nocopy ame_util.idList,
1282 itemClassesOut out nocopy ame_util.stringList,
1283 itemIdsOut out nocopy ame_util.stringList,
1284 itemSourcesOut out nocopy ame_util.longStringList,
1285 productionIndexesOut out nocopy ame_util.idList,
1286 variableNamesOut out nocopy ame_util.stringList,
1287 variableValuesOut out nocopy ame_util.stringList,
1288 transVariableNamesOut out nocopy ame_util.stringList,
1289 transVariableValuesOut out nocopy ame_util.stringList) as
1290 begin
1291 ame_engine.getNextApprovers(
1292 applicationIdIn => applicationIdIn
1293 ,transactionTypeIn => transactionTypeIn
1294 ,transactionIdIn => transactionIdIn
1295 ,nextApproversType => 3
1296 ,flagApproversAsNotifiedIn => flagApproversAsNotifiedIn
1297 ,approvalProcessCompleteYNOut => approvalProcessCompleteYNOut
1298 ,nextApproversOut => nextApproversOut
1299 ,itemIndexesOut => itemIndexesOut
1300 ,itemClassesOut => itemClassesOut
1301 ,itemIdsOut => itemIdsOut
1302 ,itemSourcesOut => itemSourcesOut
1303 ,productionIndexesOut => productionIndexesOut
1304 ,variableNamesOut => variableNamesOut
1305 ,variableValuesOut => variableValuesOut
1306 ,transVariableNamesOut => transVariableNamesOut
1307 ,transVariableValuesOut => transVariableValuesOut);
1308 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1309 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1310 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1311 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1312 else
1313 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1314 end if;
1315 else
1316 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1317 end if;
1318 exception
1319 when others then
1320 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1321 ame_util.runtimeException(packageNameIn => 'ame_api2',
1322 routineNameIn => 'getNextApprovers3',
1323 exceptionNumberIn => sqlcode,
1324 exceptionStringIn => sqlerrm);
1325 approvalProcessCompleteYNOut:= null;
1326 nextApproversOut.delete;
1327 itemIndexesOut.delete;
1328 itemClassesOut.delete;
1329 itemIdsOut.delete;
1330 itemSourcesOut.delete;
1331 productionIndexesOut.delete;
1332 variableNamesOut.delete;
1333 variableValuesOut.delete;
1334 transVariableNamesOut.delete;
1335 transVariableValuesOut.delete;
1336 raise;
1337 end getNextApprovers3;
1338 procedure getNextApprovers4(applicationIdIn in number,
1339 transactionTypeIn in varchar2,
1340 transactionIdIn in varchar2,
1341 flagApproversAsNotifiedIn in varchar2 default ame_util.booleanTrue,
1342 approvalProcessCompleteYNOut out nocopy varchar2,
1343 nextApproversOut out nocopy ame_util.approversTable2) as
1344 dummyIdList ame_util.idList;
1345 dummyLongStringList ame_util.longStringList;
1346 dummyStringList ame_util.stringList;
1347 begin
1348 ame_engine.getNextApprovers(
1349 applicationIdIn => applicationIdIn
1350 ,transactionTypeIn => transactionTypeIn
1351 ,transactionIdIn => transactionIdIn
1352 ,nextApproversType => 4
1353 ,flagApproversAsNotifiedIn => flagApproversAsNotifiedIn
1354 ,approvalProcessCompleteYNOut => approvalProcessCompleteYNOut
1355 ,nextApproversOut => nextApproversOut
1356 ,itemIndexesOut => dummyIdList
1357 ,itemClassesOut => dummyStringList
1358 ,itemIdsOut => dummyStringList
1359 ,itemSourcesOut => dummyLongStringList
1360 ,productionIndexesOut => dummyIdList
1361 ,variableNamesOut => dummyStringList
1362 ,variableValuesOut => dummyStringList
1363 ,transVariableNamesOut => dummyStringList
1364 ,transVariableValuesOut => dummyStringList);
1365 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1366 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1367 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1368 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1369 else
1370 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1371 end if;
1372 else
1373 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1374 end if;
1375 exception
1376 when others then
1377 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1378 ame_util.runtimeException(packageNameIn => 'ame_api2',
1379 routineNameIn => 'getNextApprovers4',
1380 exceptionNumberIn => sqlcode,
1381 exceptionStringIn => sqlerrm);
1382 approvalProcessCompleteYNOut:= null;
1383 nextApproversOut.delete;
1384 raise;
1385 end getNextApprovers4;
1386 procedure getPendingApprovers(applicationIdIn in number,
1387 transactionTypeIn in varchar2,
1388 transactionIdIn in varchar2,
1389 approvalProcessCompleteYNOut out nocopy varchar2,
1390 approversOut out nocopy ame_util.approversTable2) as
1391 approverCount integer;
1392 approvers ame_util.approversTable2;
1393 begin
1394 ame_engine.updateTransactionState(isTestTransactionIn => false,
1395 isLocalTransactionIn => false,
1396 fetchConfigVarsIn => true,
1397 fetchOldApproversIn => true,
1398 fetchInsertionsIn => true,
1399 fetchDeletionsIn => true,
1400 fetchAttributeValuesIn => true,
1401 fetchInactiveAttValuesIn => false,
1402 processProductionActionsIn => false,
1403 processProductionRulesIn => false,
1404 updateCurrentApproverListIn => true,
1405 updateOldApproverListIn => false,
1406 processPrioritiesIn => true,
1407 prepareItemDataIn => false,
1408 prepareRuleIdsIn => false,
1409 prepareRuleDescsIn => false,
1410 prepareApproverTreeIn => true,
1411 transactionIdIn => transactionIdIn,
1412 ameApplicationIdIn => null,
1413 fndApplicationIdIn => applicationIdIn,
1414 transactionTypeIdIn => transactionTypeIn );
1415 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
1416 ame_engine.getApprovers(approversOut => approvers);
1417 approverCount := 0;
1418 for i in 1..approvers.count loop
1419 if (approvers(i).approver_category = ame_util.approvalApproverCategory and
1420 approvers(i).approval_status = ame_util.notifiedStatus ) then
1421 approverCount := approverCount + 1;
1422 ame_util.copyApproverRecord2(approverRecord2In => approvers(i),
1423 approverRecord2Out => approversOut(approverCount) );
1424 end if;
1425 end loop;
1426 if ame_util2.detailedApprovalStatusFlagYN = ame_util.booleanFalse then
1427 if approvalProcessCompleteYNOut = ame_util2.completeFullyApproved
1428 or approvalProcessCompleteYNOut = ame_util2.completeNoApprovers then
1429 approvalProcessCompleteYNOut := ame_util.booleanTrue;
1430 else
1431 approvalProcessCompleteYNOut := ame_util.booleanFalse;
1432 end if;
1433 else
1434 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1435 end if;
1436 exception
1437 when others then
1438 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanFalse;
1439 ame_util.runtimeException(packageNameIn => 'ame_api2',
1440 routineNameIn => 'getPendingApprovers',
1441 exceptionNumberIn => sqlcode,
1442 exceptionStringIn => sqlerrm);
1443 approvalProcessCompleteYNOut:= null;
1444 approversOut.delete;
1445 raise;
1446 end getPendingApprovers;
1447 procedure getTransactionProductions(applicationIdIn in number,
1448 transactionTypeIn in varchar2,
1449 transactionIdIn in varchar2,
1450 variableNamesOut out nocopy ame_util.stringList,
1451 variableValuesOut out nocopy ame_util.stringList) as
1452 tempProductions ame_util2.productionsTable;
1453 tempIndex integer;
1454 begin
1455 ame_engine.updateTransactionState(isTestTransactionIn => false,
1456 isLocalTransactionIn => false,
1457 fetchConfigVarsIn => true,
1458 fetchOldApproversIn => true,
1459 fetchInsertionsIn => true,
1460 fetchDeletionsIn => true,
1461 fetchAttributeValuesIn => true,
1462 fetchInactiveAttValuesIn => false,
1463 processProductionActionsIn => false,
1464 processProductionRulesIn => true,
1465 updateCurrentApproverListIn => false,
1466 updateOldApproverListIn => false,
1467 processPrioritiesIn => true,
1468 prepareItemDataIn => false,
1469 prepareRuleIdsIn => false,
1470 prepareRuleDescsIn => false,
1471 transactionIdIn => transactionIdIn,
1472 ameApplicationIdIn => null,
1473 fndApplicationIdIn => applicationIdIn,
1474 transactionTypeIdIn => transactionTypeIn );
1475 ame_engine.getProductions(itemClassIn => ame_util.headerItemClassName
1476 ,itemIdIn => transactionIdIn
1477 ,productionsOut => tempProductions);
1478 tempIndex := 1;
1479 for i in 1 .. tempProductions.count loop
1480 variableNamesOut(tempIndex) := tempProductions(i).variable_name;
1481 variableValuesOut(tempIndex) := tempProductions(i).variable_value;
1482 tempIndex := tempIndex+1;
1483 end loop;
1484 exception
1485 when others then
1486 ame_util.runtimeException(packageNameIn => 'ame_api2',
1487 routineNameIn => 'getTransactionProductions',
1488 exceptionNumberIn => sqlcode,
1489 exceptionStringIn => sqlerrm);
1490 variableNamesOut.delete;
1491 variableValuesOut.delete;
1492 raise;
1493 end getTransactionProductions;
1494 procedure initializeApprovalProcess(applicationIdIn in number,
1495 transactionTypeIn in varchar2,
1496 transactionIdIn in varchar2,
1497 recordApproverListIn in boolean default false) as
1498
1499 begin
1500 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
1501 transactionIdIn => transactionIdIn,
1502 transactionTypeIdIn => transactionTypeIn);
1503 ame_engine.updateTransactionState(isTestTransactionIn => false,
1504 isLocalTransactionIn => false,
1505 fetchConfigVarsIn => true,
1506 fetchOldApproversIn => true,
1507 fetchInsertionsIn => true,
1508 fetchDeletionsIn => true,
1509 fetchAttributeValuesIn => true,
1510 fetchInactiveAttValuesIn => false,
1511 processProductionActionsIn => false,
1512 processProductionRulesIn => false,
1513 updateCurrentApproverListIn => recordApproverListIn,
1514 updateOldApproverListIn => recordApproverListIn,
1515 processPrioritiesIn => true,
1516 prepareItemDataIn => false,
1517 prepareRuleIdsIn => false,
1518 prepareRuleDescsIn => false,
1519 transactionIdIn => transactionIdIn,
1520 ameApplicationIdIn => null,
1521 fndApplicationIdIn => applicationIdIn,
1522 transactionTypeIdIn => transactionTypeIn );
1523 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
1524 transactionIdIn => transactionIdIn,
1525 transactionTypeIdIn => transactionTypeIn);
1526 exception
1527 when others then
1528 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
1529 transactionIdIn => transactionIdIn,
1530 transactionTypeIdIn => transactionTypeIn);
1531 ame_util.runtimeException(packageNameIn => 'ame_api2',
1532 routineNameIn => 'initializeApprovalProcess',
1533 exceptionNumberIn => sqlcode,
1534 exceptionStringIn => sqlerrm);
1535 raise;
1536 end initializeApprovalProcess;
1537 procedure setFirstAuthorityApprover(applicationIdIn in number,
1538 transactionTypeIn in varchar2,
1539 transactionIdIn in varchar2,
1540 approverIn in ame_util.approverRecord2,
1541 clearChainStatusYNIn in varchar2) as
1542 ameApplicationId integer;
1543 approversCount integer;
1544 approvers ame_util.approversTable2;
1545 approvalProcessCompleteYN ame_util.charType;
1546 itemApprovalProcessCompleteYN ame_util.charType;
1547 badStatusException exception;
1548 currentApprovers ame_util.approversTable2;
1549 errorCode integer;
1550 errorMessage ame_util.longStringType;
1551 invalidApproverException exception;
1552 tempCount integer;
1553 tempOrderNumber integer;
1554 tempParameter ame_temp_insertions.parameter%type;
1555 tooLateException exception;
1556 origSystemList ame_util.stringList;
1557 actionTypeCount number;
1558 invalidActionTypeException exception;
1559 invalidApproverTypeException exception;
1560 actionTypeName ame_action_types_tl.user_action_type_name%type;
1561 l_error_code number;
1562 cursor getOrigSystem(actionTypeIdIn in number) is
1563 select apr.orig_system
1564 from ame_approver_type_usages apu
1565 ,ame_approver_types apr
1566 where apu.action_type_id=actionTypeIdIn
1567 and apr.approver_type_id = apu.approver_type_id
1568 and sysdate between apu.start_date
1569 and nvl(apu.end_date - ame_util.oneSecond,sysdate)
1570 and sysdate between apr.start_date
1571 and nvl(apr.end_date - ame_util.oneSecond,sysdate);
1572 cursor getActionTypeCount(actionTypeIdIn in number) is
1573 select distinct count(*) into actionTypeCount
1574 from ame_action_type_usages
1575 where action_type_id = approverIn.action_type_id
1576 and rule_type = ame_util.authorityRuleType
1577 and sysdate between start_date
1578 and nvl(end_date - ame_util.oneSecond,sysdate);
1579 cursor getActionTypeName(actionTypeIdIn in number) is
1580 select user_action_type_name
1581 from ame_action_types_vl
1582 where action_type_id = actionTypeIdIn
1583 and sysdate between start_date
1584 and nvl(end_date - ame_util.oneSecond,sysdate);
1585 --+
1586 begin
1587 /* Validate Input data */
1588 if approverIn.name is null then
1589 raise ambiguousApproverException;
1590 end if;
1591 -- Make sure approverIn.approval_status is null.
1592 if(approverIn.approval_status is not null) then
1593 raise badStatusException;
1594 end if;
1595 -- Validate approver.
1596 if(approverIn.name is null or
1597 approverIn.api_insertion <> ame_util.apiAuthorityInsertion or
1598 approverIn.authority <> ame_util.authorityApprover or
1599 approverIn.approval_status is not null) then
1600 raise invalidApproverException;
1601 end if;
1602 open getActionTypeCount(approverIn.action_type_id);
1603 fetch getActionTypeCount into actionTypeCount;
1604 close getActionTypeCount;
1605 if actionTypeCount = 0 then
1606 raise invalidActionTypeException;
1607 end if;
1608 open getOrigSystem(approverIn.action_type_id);
1609 fetch getOrigSystem bulk collect into origSystemList;
1610 close getOrigSystem;
1611 for i in 1..origSystemList.count loop
1612 if ame_approver_type_pkg.getApproverOrigSystem(nameIn => approverIn.name)<>origSystemList(i) then
1613 raise invalidApproverTypeException;
1614 end if;
1615 end loop;
1616 -- Cycle the engine, check if the approval process is complete for the transaction
1617 ame_engine.updateTransactionState(isTestTransactionIn => false,
1618 isLocalTransactionIn => false,
1619 fetchConfigVarsIn => true,
1620 fetchOldApproversIn => true,
1621 fetchInsertionsIn => true,
1622 fetchDeletionsIn => true,
1623 fetchAttributeValuesIn => true,
1624 fetchInactiveAttValuesIn => false,
1625 processProductionActionsIn => false,
1626 processProductionRulesIn => false,
1627 updateCurrentApproverListIn => true,
1628 updateOldApproverListIn => true,
1629 processPrioritiesIn => true,
1630 prepareItemDataIn => false,
1631 prepareRuleIdsIn => false,
1632 prepareRuleDescsIn => false,
1633 transactionIdIn => transactionIdIn,
1634 ameApplicationIdIn => null,
1635 fndApplicationIdIn => applicationIdIn,
1636 transactionTypeIdIn => transactionTypeIn );
1637 /* Insertions are possible for a transaction with an empty approver list and also
1638 an approver list which has only suppressed approvers
1639 */
1640 ame_engine.getApprovers(approversOut => approvers);
1641 approversCount := approvers.count;
1642 ameApplicationId := ame_engine.getAmeApplicationId;
1643 tempParameter := ame_util.firstAuthorityParameter ||
1644 ame_util.fieldDelimiter ||
1645 approverIn.item_class ||
1646 ame_util.fieldDelimiter ||
1647 approverIn.item_id ||
1648 ame_util.fieldDelimiter ||
1649 approverIn.action_type_id ||
1650 ame_util.fieldDelimiter ||
1651 approverIn.group_or_chain_id;
1652 if clearChainStatusYNIn = ame_util.booleanFalse then
1653 /* Check whether any chain-of-authority approvers have acted on the transaction's chain */
1654 select count(*)
1655 into tempCount
1656 from ame_temp_old_approver_lists
1657 where
1658 application_id = ameApplicationId and
1659 transaction_id = transactionIdIn and
1660 item_class = approverIn.item_class and
1661 item_id = approverIn.item_id and
1662 action_type_id = approverIn.action_type_id and
1663 group_or_chain_id = approverIn.group_or_chain_id and
1664 authority = ame_util.authorityApprover and
1665 api_insertion in (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) and
1666 approval_status not in (ame_util.nullStatus,ame_util.notifiedStatus);
1667 if(tempCount > 0) then
1668 raise tooLateException;
1669 end if;
1670 else /* clearChainStatusYNIn = 'Y' */
1671 /* Clear the status of the relevant chain for all approvers who have responded and have an approval
1672 approver category. Approvers with a status of 'NOTIFIED' will not be cleared */
1673 update ame_temp_old_approver_lists set
1674 approval_status = ame_util.nullStatus
1675 where
1676 application_id = ameApplicationId and
1677 transaction_id = transactionIdIn and
1678 item_class = approverIn.item_class and
1679 item_id = approverIn.item_id and
1680 action_type_id = approverIn.action_type_id and
1681 group_or_chain_id = approverIn.group_or_chain_id and
1682 authority = ame_util.authorityApprover and
1683 api_insertion in (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) and
1684 approval_status not in (ame_util.nullStatus,ame_util.notifiedStatus);
1685 /* update all existing history rows from the Approval Notification History table
1686 to indicate the rows were cleared */
1687 update AME_TRANS_APPROVAL_HISTORY set
1688 date_cleared = sysdate
1689 where
1690 application_id = ameApplicationId and
1691 transaction_id = transactionIdIn and
1692 item_class = approverIn.item_class and
1693 item_id = approverIn.item_id and
1694 action_type_id = approverIn.action_type_id and
1695 group_or_chain_id = approverIn.group_or_chain_id and
1696 authority = ame_util.authorityApprover and
1697 status not in (ame_util.nullStatus,ame_util.notifiedStatus) and
1698 date_cleared is null;
1699 end if;
1700 /* If there is already a firstAuthority in the insertions table for the transaction, item class, item_id ,
1701 delete it. */
1702 if(ame_engine.insertionExists(orderTypeIn => ame_util.firstAuthority,
1703 parameterIn => tempParameter)) then
1704 delete from ame_temp_insertions
1705 where
1706 application_id = ameApplicationId and
1707 transaction_id = transactionIdIn and
1708 item_class = approverIn.item_class and
1709 item_id = approverIn.item_id and
1710 order_type = ame_util.firstAuthority;
1711 end if;
1712 -- Perform the insertion.
1713 tempCount := ame_engine.getNextInsertionOrder;
1714 insert into ame_temp_insertions(
1715 transaction_id,
1716 application_id,
1717 insertion_order,
1718 order_type,
1719 parameter,
1720 description,
1721 name,
1722 item_class,
1723 item_id,
1724 approver_category,
1725 api_insertion,
1726 authority,
1727 effective_date,
1728 reason) values(
1729 transactionIdIn,
1730 ameApplicationId,
1731 tempCount,
1732 ame_util.firstAuthority,
1733 tempParameter,
1734 ame_util.firstAuthorityDescription,
1735 approverIn.name,
1736 approverIn.item_class,
1737 approverIn.item_id,
1738 approverIn.approver_category,
1739 ame_util.apiAuthorityInsertion,
1740 ame_util.authorityApprover,
1741 sysdate,
1742 ame_approver_deviation_pkg.firstauthReason
1743 );
1744 -- Cycle the engine to account for changes in the insertions table.
1745 ame_engine.updateTransactionState(isTestTransactionIn => false,
1746 isLocalTransactionIn => false,
1747 fetchConfigVarsIn => true,
1748 fetchOldApproversIn => true,
1749 fetchInsertionsIn => true,
1750 fetchDeletionsIn => true,
1751 fetchAttributeValuesIn => true,
1752 fetchInactiveAttValuesIn => false,
1753 processProductionActionsIn => false,
1754 processProductionRulesIn => false,
1755 updateCurrentApproverListIn => true,
1756 updateOldApproverListIn => true,
1757 processPrioritiesIn => true,
1758 prepareItemDataIn => false,
1759 prepareRuleIdsIn => false,
1760 prepareRuleDescsIn => false,
1761 transactionIdIn => transactionIdIn,
1762 ameApplicationIdIn => null,
1763 fndApplicationIdIn => applicationIdIn,
1764 transactionTypeIdIn => transactionTypeIn );
1765 exception
1766 when ambiguousApproverException then
1767 errorCode := -20318;
1768 errorMessage := ambiguousApproverMessage;
1769 ame_util.runtimeException(packageNameIn => 'ame_api2',
1770 routineNameIn => 'setFirstAuthorityApprover',
1771 exceptionNumberIn => errorCode,
1772 exceptionStringIn => errorMessage);
1773 raise_application_error(errorCode,
1774 errorMessage);
1775 when badStatusException then
1776 errorCode := -20306;
1777 errorMessage :=
1778 ame_util.getMessage(applicationShortNameIn =>'PER',
1779 messageNameIn => 'AME_400242_API_NON_NULL_FRSAPP');
1780 ame_util.runtimeException(packageNameIn => 'ame_api2',
1781 routineNameIn => 'setFirstAuthorityApprover',
1782 exceptionNumberIn => errorCode,
1783 exceptionStringIn => errorMessage);
1784 raise_application_error(errorCode,
1785 errorMessage);
1786 when invalidApproverException then
1787 errorCode := -20307;
1788 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1789 messageNameIn => 'AME_400243_API_INV_FRSTAPP');
1790 ame_util.runtimeException(packageNameIn => 'ame_api2',
1791 routineNameIn => 'setFirstAuthorityApprover',
1792 exceptionNumberIn => errorCode,
1793 exceptionStringIn => errorMessage);
1794 raise_application_error(errorCode,
1795 errorMessage);
1796 when invalidActionTypeException then
1797 errorCode := -20321;
1798 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1799 messageNameIn => 'AME_400815_INV_COA_ACT_TYP');
1800 ame_util.runtimeException(packageNameIn => 'ame_api2',
1801 routineNameIn => 'setFirstAuthorityApprover',
1802 exceptionNumberIn => errorCode,
1803 exceptionStringIn => errorMessage);
1804 raise_application_error(errorCode,
1805 errorMessage);
1806 when invalidApproverTypeException then
1807 errorCode := -20322;
1808 open getActionTypeName(approverIn.action_type_id);
1809 fetch getActionTypeName into actionTypeName;
1810 close getActionTypeName;
1811 errorMessage :=
1812 ame_util.getMessage(
1813 applicationShortNameIn =>'PER',
1814 messageNameIn => 'AME_400816_INV_APPROVER_TYPE',
1815 tokenNameOneIn => 'ACTION_TYPE',
1816 tokenvalueOneIn => actionTypeName,
1817 tokenNameTwoIn => 'ORIG_SYSTEM',
1818 tokenvalueTwoIn =>
1819 ame_approver_type_pkg.getApproverOrigSystem(
1820 nameIn => approverIn.name));
1821 ame_util.runtimeException(packageNameIn => 'ame_api2',
1822 routineNameIn => 'setFirstAuthorityApprover',
1823 exceptionNumberIn => errorCode,
1824 exceptionStringIn => errorMessage);
1825 raise_application_error(errorCode,
1826 errorMessage);
1827 when tooLateException then
1828 errorCode := -20001;
1829 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1830 messageNameIn => 'AME_400244_API_CHN_AUTH_TRANS');
1831 ame_util.runtimeException(packageNameIn => 'ame_api2',
1832 routineNameIn => 'setFirstAuthorityApprover',
1833 exceptionNumberIn => errorCode,
1834 exceptionStringIn => errorMessage);
1835 raise_application_error(errorCode,
1836 errorMessage);
1837 when others then
1838 l_error_code := sqlcode;
1839 if l_error_code = -20213 then
1840 errorCode := -20222;
1841 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1842 messageNameIn => 'AME_400837_INV_APR_FOUND',
1843 tokenNameOneIn => 'PROCESS_NAME',
1844 tokenValueOneIn => 'setFirstAuthorityApprover',
1845 tokenNameTwoIn => 'NAME',
1846 tokenValueTwoIn => approverIn.name);
1847 raise_application_error(errorCode,errorMessage);
1848 end if;
1849 ame_util.runtimeException(packageNameIn => 'ame_api2',
1850 routineNameIn => 'setFirstAuthorityApprover',
1851 exceptionNumberIn => sqlcode,
1852 exceptionStringIn => sqlerrm);
1853 raise;
1854 end setFirstAuthorityApprover;
1855 procedure updateApprovalStatus(applicationIdIn in number,
1856 transactionTypeIn in varchar2,
1857 transactionIdIn in varchar2,
1858 approverIn in ame_util.approverRecord2,
1859 forwardeeIn in ame_util.approverRecord2 default
1860 ame_util.emptyApproverRecord2,
1861 updateItemIn in boolean default false) as
1862 errorCode integer;
1863 errorMessage ame_util.longStringType;
1864 begin
1865 /*validate the input approver*/
1866 if(approverIn.name is null) then
1867 raise ambiguousApproverException;
1868 end if;
1869 ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
1870 transactionTypeIn => transactionTypeIn,
1871 transactionIdIn => transactionIdIn,
1872 approverIn => approverIn,
1873 forwardeeIn => forwardeeIn,
1874 updateItemIn => updateItemIn);
1875 exception
1876 when ambiguousApproverException then
1877 errorCode := -20318;
1878 errorMessage := ambiguousApproverMessage;
1879 ame_util.runtimeException(packageNameIn => 'ame_api2',
1880 routineNameIn => 'updateApprovalStatus',
1881 exceptionNumberIn => errorCode,
1882 exceptionStringIn => errorMessage);
1883 raise_application_error(errorCode,
1884 errorMessage);
1885 when others then
1886 ame_util.runtimeException(packageNameIn => 'ame_api2',
1887 routineNameIn => 'updateApprovalStatus',
1888 exceptionNumberIn => sqlcode,
1889 exceptionStringIn => sqlerrm);
1890 raise;
1891 end updateApprovalStatus;
1892 procedure updateApprovalStatuses(applicationIdIn in number,
1893 transactionTypeIn in varchar2,
1894 transactionIdIn in varchar2,
1895 approverIn in ame_util.approverRecord2,
1896 approvalStatusesIn in ame_util.stringList default ame_util.emptyStringList,
1897 itemClassesIn in ame_util.stringList default ame_util.emptyStringList,
1898 itemIdsIn in ame_util.stringList default ame_util.emptyStringList,
1899 forwardeesIn in ame_util.approversTable2 default ame_util.emptyApproversTable2) as
1900 begin
1901 null;
1902 end updateApprovalStatuses;
1903 procedure updateApprovalStatus2(applicationIdIn in number,
1904 transactionTypeIn in varchar2,
1905 transactionIdIn in varchar2,
1906 approvalStatusIn in varchar2,
1907 approverNameIn in varchar2,
1908 itemClassIn in varchar2 default null,
1909 itemIdIn in varchar2 default null,
1910 actionTypeIdIn in number default null,
1911 groupOrChainIdIn in number default null,
1912 occurrenceIn in number default null,
1913 forwardeeIn in ame_util.approverRecord2 default ame_util.emptyApproverRecord2,
1914 updateItemIn in boolean default false) as
1915 approver ame_util.approverRecord2;
1916 errorCode integer;
1917 errorMessage ame_util.longStringType;
1918 nullApproverException exception;
1919 l_error_code number;
1920 begin
1921 /* No locking needed here as it is done in updateApprovalStatus */
1922 if approverNameIn is not null then
1923 approver.name := approverNameIn;
1924 else
1925 raise nullApproverException;
1926 end if;
1927 approver.item_class := itemClassIn ;
1928 approver.item_id := itemIdIn ;
1929 approver.approval_status := approvalStatusIn;
1930 approver.action_type_id :=actionTypeIdIn ;
1931 approver.group_or_chain_id := groupOrChainIdIn;
1932 approver.occurrence := occurrenceIn;
1933 ame_approver_type_pkg.getOrigSystemIdAndDisplayName(nameIn =>approver.name,
1934 origSystemOut => approver.orig_system,
1935 origSystemIdOut => approver.orig_system_id,
1936 displayNameOut => approver.display_name);
1937 ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
1938 transactionIdIn => transactionIdIn,
1939 approverIn => approver,
1940 transactionTypeIn => transactionTypeIn,
1941 forwardeeIn => forwardeeIn,
1942 updateItemIn => updateItemIn);
1943 exception
1944 when nullApproverException then
1945 errorCode := -20318;
1946 errorMessage :=
1947 ame_util.getMessage(applicationShortNameIn =>'PER',
1948 messageNameIn => 'AME_400812_NULL_APPR_REC_NAME');
1949 ame_util.runtimeException(packageNameIn => 'ame_api2',
1950 routineNameIn => 'updateApprovalStatus2',
1951 exceptionNumberIn => errorCode,
1952 exceptionStringIn => errorMessage);
1953 raise_application_error(errorCode,
1954 errorMessage);
1955 when others then
1956 l_error_code := sqlcode;
1957 if l_error_code = -20213 then
1958 errorCode := -20222;
1959 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1960 messageNameIn => 'AME_400837_INV_APR_FOUND',
1961 tokenNameOneIn => 'PROCESS_NAME',
1962 tokenValueOneIn => 'updateApprovalStatus2',
1963 tokenNameTwoIn => 'NAME',
1964 tokenValueTwoIn => approver.name);
1965 raise_application_error(errorCode,errorMessage);
1966 end if;
1967
1968 ame_util.runtimeException(packageNameIn => 'ame_api2',
1969 routineNameIn => 'updateApprovalStatus2',
1970 exceptionNumberIn => sqlcode,
1971 exceptionStringIn => sqlerrm);
1972 raise;
1973 end updateApprovalStatus2;
1974 procedure updateApprovalStatuses2(applicationIdIn in number,
1975 transactionTypeIn in varchar2,
1976 transactionIdIn in varchar2,
1977 approvalStatusIn in varchar2,
1978 approverNameIn in varchar2,
1979 itemClassIn in varchar2 default null,
1980 itemIdIn in varchar2 default null,
1981 actionTypeIdIn in number default null,
1982 groupOrChainIdIn in number default null,
1983 occurrenceIn in number default null,
1984 approvalStatusesIn in ame_util.stringList default ame_util.emptyStringList,
1985 itemClassesIn in ame_util.stringList default ame_util.emptyStringList,
1986 itemIdsIn in ame_util.stringList default ame_util.emptyStringList,
1987 forwardeesIn in ame_util.approversTable2 default ame_util.emptyApproversTable2) is
1988 begin
1989 null;
1990 end updateApprovalStatuses2;
1991
1992 end ame_api2;