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