[Home] [Help]
PACKAGE BODY: APPS.AME_API7
Source
1 package body ame_api7 as
2 /* $Header: ameeapi7.pkb 120.3 2011/05/17 11:40:15 nchinnam ship $ */
3 /*The following method validates itemClass for the current transaction*/
4 function validateItemClass(itemClassIn varchar2) return boolean as
5 itemClassNames ame_util.stringList;
6 begin
7 ame_engine.getAllItemClasses(itemClassNamesOut => itemClassNames);
8 for i in 1..itemClassNames.count loop
9 if itemClassNames(i) = itemClassIn then
10 return true;
11 end if;
12 end loop;
13 return false;
14 end;
15 /*The following method validates itemId for the itemClass for the current
16 transaction*/
17 function validateItemClassItemId(itemClassIn varchar2
18 ,itemIdIn varchar2) return boolean as
19 itemIds ame_util.stringList;
20 begin
21 ame_engine.getItemClassItemIds
22 (itemClassIdIn => ame_admin_pkg.getItemClassIdByName(itemClassNameIn => itemClassIn),
23 itemIdsOut => itemIds );
24 for i in 1..itemIds.count loop
25 if itemIds(i) = itemIdIn then
26 return true;
27 end if;
28 end loop;
29 return false;
30 end;
31 procedure getAttributeValue( applicationIdIn in number,
32 transactionTypeIn in varchar2,
33 transactionIdIn in varchar2,
34 attributeNameIn in varchar2,
35 itemClassIn in varchar2,
36 itemIdIn in varchar2,
37 attributeValue1Out out nocopy varchar2,
38 attributeValue2Out out nocopy varchar2,
39 attributeValue3Out out nocopy varchar2) as
40 itemId ame_util.stringType;
41 itemClass ame_util.stringType;
42 invalidItemIdException exception;
43 errorCode integer;
44 errorMessage ame_util.longestStringType;
45 begin
46 ame_engine.updateTransactionState(isTestTransactionIn => false,
47 isLocalTransactionIn => false,
48 fetchConfigVarsIn => false,
49 fetchOldApproversIn => false,
50 fetchInsertionsIn => false,
51 fetchDeletionsIn => false,
52 fetchAttributeValuesIn => true,
53 fetchInactiveAttValuesIn => true,
54 processProductionActionsIn => false,
55 processProductionRulesIn => false,
56 updateCurrentApproverListIn => false,
57 updateOldApproverListIn => false,
58 processPrioritiesIn => false,
59 prepareItemDataIn => false,
60 prepareRuleIdsIn => false,
61 prepareRuleDescsIn => false,
62 transactionIdIn => transactionIdIn,
63 ameApplicationIdIn => null,
64 fndApplicationIdIn => applicationIdIn,
65 transactionTypeIdIn => transactionTypeIn );
66 /* In case no itemClass is passed in , assume it is header and set itemId as
67 transactionIdIn */
68
69 if itemClassIn is null or itemClassIn = ame_util.headerItemClassName then
70 itemClass := ame_util.headerItemClassName;
71 itemId := transactionIdIn;
72 else
73 itemId := itemIdIn;
74 itemClass := itemClassIn;
75 end if;
76 --+
77 --+ Validate Item Class Name
78 --+
79 if not validateItemClass (itemClassIn => itemClass) then
80 raise invalidItemIdException;
81 end if;
82 --+
83 --+ Validate Item Id
84 --+
85 if not validateItemClassItemId (itemClassIn => itemClass,
86 itemIdIn => itemId ) then
87 raise invalidItemIdException;
88 end if;
89 /*Handle variant attributes */
90 if (attributeNameIn = ame_util.jobLevelStartingPointAttribute or
91 attributeNameIn = ame_util.nonDefStartingPointPosAttr or
92 attributeNameIn = ame_util.nonDefPosStructureAttr or
93 attributeNameIn = ame_util.supStartingPointAttribute or
94 attributeNameIn = ame_util.firstStartingPointAttribute or
95 attributeNameIn = ame_util.secondStartingPointAttribute ) then
96 attributeValue1Out := ame_engine.getVariantAttributeValue(attributeIdIn => ame_attribute_pkg.getIdByName(
97 attributeNameIn => attributeNameIn),
98 itemClassIn => itemClass,
99 itemIdIn => itemId
100 );
101 else
102 ame_engine.getItemAttValues2(attributeNameIn => attributeNameIn,
103 itemIdIn => itemId,
104 attributeValue1Out => attributeValue1Out,
105 attributeValue2Out => attributeValue2Out,
106 attributeValue3Out => attributeValue3Out);
107 end if;
108 exception
109 when invalidItemIdException then
110 errorCode := -20317;
111 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
112 messageNameIn => 'AME_400800_INVALID_ITEM_ID');
113 ame_util.runtimeException(packageNameIn => 'ame_api7',
114 routineNameIn => 'getAttributeValue',
115 exceptionNumberIn => errorCode,
116 exceptionStringIn => errorMessage);
117 raise_application_error(errorCode,
118 errorMessage);
119 when others then
120 ame_util.runtimeException(packageNameIn => 'ame_api7',
121 routineNameIn => 'getAttributeValue',
122 exceptionNumberIn => sqlcode,
123 exceptionStringIn => sqlerrm);
124 raise;
125 end getAttributeValue;
126 procedure getGroupMembers1(applicationIdIn in number default null,
127 transactionTypeIn in varchar2 default null,
128 transactionIdIn in varchar2 default null,
129 itemClassIn in varchar2,
130 itemIdIn in varchar2,
131 groupIdIn in number,
132 memberDisplayNamesOut out nocopy ame_util.longStringList)as
133 cursor groupMemberCursor(groupIdIn in integer) is
134 select
135 parameter,
136 upper(parameter_name),
137 query_string,
138 orig_system,
139 orig_system_id
140 from ame_approval_group_members
141 where
142 approval_group_id = groupIdIn
143 order by order_number;
144 badDynamicMemberException exception;
145 noItemBindException exception;
146 dynamicCursor integer;
147 colonLocation1 integer;
148 colonLocation2 integer;
149 displayNames ame_util.longStringList;
150 errorCode integer;
151 errorMessage ame_util.longestStringType;
152 noTransIdDefinedException exception;
153 orderNumbers ame_util.idList;
154 l_orig_systemList ame_util.stringList;
155 l_orig_system_id ame_util.idList;
156 memberOrigSystem ame_util.stringType;
157 memberOrigSystemId number;
158 outputIndex integer;
159 parameters ame_util.longStringList;
160 queryStrings ame_util.longestStringList;
161 rowsFound integer;
162 tempGroupMembers dbms_sql.Varchar2_Table;
163 upperParameterNames ame_util.stringList;
164 tempGroupName ame_util.stringType;
165 l_error_code number;
166 begin
167 open groupMemberCursor(groupIdIn => groupIdIn);
168 fetch groupMemberCursor bulk collect
169 into
170 parameters,
171 upperParameterNames,
172 queryStrings,
173 l_orig_systemList,
174 l_orig_system_id;
175 close groupMemberCursor;
176 outputIndex := 0; /* pre-increment */
177 for i in 1 .. parameters.count loop
178 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
179 dynamicCursor := dbms_sql.open_cursor;
180 dbms_sql.parse(dynamicCursor,
181 ame_util.removeReturns(stringIn => queryStrings(i),
182 replaceWithSpaces => true),
183 dbms_sql.native);
184 if(instrb(queryStrings(i),
185 ame_util.transactionIdPlaceholder) > 0) then
186 if transactionIdIn is null then
187 dbms_sql.close_cursor(dynamicCursor);
188 raise noTransIdDefinedException;
189 end if;
190 dbms_sql.bind_variable(dynamicCursor,
191 ame_util.transactionIdPlaceholder,
192 transactionIdIn,
193 50);
194 end if;
195 if(instrb(queryStrings(i),
196 ame_util2.itemClassPlaceHolder) > 0) then
197 if transactionIdIn is null then
198 dbms_sql.close_cursor(dynamicCursor);
199 raise noItemBindException;
200 end if;
201 dbms_sql.bind_variable(dynamicCursor,
202 ame_util2.itemClassPlaceHolder,
203 itemClassIn,
204 50);
205 end if;
206 if(instrb(queryStrings(i),
207 ame_util2.itemIdPlaceHolder) > 0) then
208 if transactionIdIn is null then
209 dbms_sql.close_cursor(dynamicCursor);
210 raise noItemBindException;
211 end if;
212 dbms_sql.bind_variable(dynamicCursor,
213 ame_util2.itemIdPlaceHolder,
214 itemIdIn,
215 50);
216 end if;
217 dbms_sql.define_array(dynamicCursor,
218 1,
219 tempGroupMembers,
220 100,
221 1);
222 rowsFound := dbms_sql.execute(dynamicCursor);
223 loop
224 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
225 dbms_sql.column_value(dynamicCursor,
226 1,
227 tempGroupMembers);
228 exit when rowsFound < 100;
229 end loop;
230 dbms_sql.close_cursor(dynamicCursor);
231 /*
232 Dynamic groups' query strings may return rows having one of two forms:
233 (1) approver_type:approver_id
234 (2) orig_system:orig_system_id:approver_name
235 */
236 for j in 1 .. tempGroupMembers.count loop
237 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
238 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
239 if(colonLocation1 = 0) then
240 raise badDynamicMemberException;
241 end if;
242 outputIndex := outputIndex + 1;
243 if(colonLocation2 = 0) then /* first case (old style) */
244 memberOrigSystemId :=
245 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
246 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
247 upper(ame_util.approverPersonId)) then
248 memberOrigSystem := ame_util.perOrigSystem;
249 else
250 memberOrigSystem := ame_util.fndUserOrigSystem;
251 end if;
252 else
253 memberOrigSystem :=
254 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
255 memberOrigSystemId :=
256 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
257 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
258 end if;
259
260 begin
261 memberDisplayNamesOut(outputIndex) :=
262 ame_approver_type_pkg.getApproverDisplayName2(
263 origSystemIn => memberOrigSystem,
264 origSystemIdIn => memberOrigSystemId);
265 exception
266 when others then
267 l_error_code := sqlcode;
268 if l_error_code = -20213 then
269 errorCode := -20225;
270 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
271 messageNameIn => 'AME_400837_INV_APR_FOUND',
272 tokenNameOneIn => 'PROCESS_NAME',
273 tokenValueOneIn => 'ame_api7.getGroupMembers1',
274 tokenNameTwoIn => 'ORIG_SYSTEM',
275 tokenValueTwoIn => memberOrigSystem,
276 tokenNameThreeIn => 'ORIG_SYSEM_ID',
277 tokenValueThreeIn => memberOrigSystemId);
278 raise_application_error(errorCode,errorMessage);
279 end if;
280 raise;
281 end;
282 end loop;
283 else /* Copy the static group into the engGroup caches. */
284 outputIndex := outputIndex + 1;
285 begin
286 displayNames(i) := ame_approver_type_pkg.getApproverDisplayName2(l_orig_systemList(i), l_orig_system_id(i));
287 exception
288 when others then
289 l_error_code := sqlcode;
290 if l_error_code = -20213 then
291 errorCode := -20225;
292 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
293 messageNameIn => 'AME_400837_INV_APR_FOUND',
294 tokenNameOneIn => 'PROCESS_NAME',
295 tokenValueOneIn => 'ame_api7.getGroupMembers1',
296 tokenNameTwoIn => 'ORIG_SYSTEM',
297 tokenValueTwoIn => l_orig_systemList(i),
298 tokenNameThreeIn => 'ORIG_SYSEM_ID',
299 tokenValueThreeIn => l_orig_system_id(i));
300 raise_application_error(errorCode,errorMessage);
301 end if;
302 raise;
303 end;
304 end if;
305 end loop;
306 exception
307 when badDynamicMemberException then
308 if(groupMemberCursor%isopen) then
309 close groupMemberCursor;
310 end if;
311 memberDisplayNamesOut.delete;
312 errorCode := -20315;
313 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
314 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
315 ame_util.runtimeException(packageNameIn => 'ame_api3',
316 routineNameIn => 'getGroupMembers1',
317 exceptionNumberIn => errorCode,
318 exceptionStringIn => errorMessage);
319 raise_application_error(errorCode,
320 errorMessage);
321 when noItemBindException then
322 if(groupMemberCursor%isopen) then
323 close groupMemberCursor;
324 end if;
325 memberDisplayNamesOut.delete;
326 errorCode := -20316;
327 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
328 ,groupNameOut => tempGroupName);
329 errorMessage := ame_util.getMessage(
330 applicationShortNameIn => 'PER',
331 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
332 tokenNameOneIn => 'APPROVER_GROUP',
333 tokenValueOneIn => tempGroupName);
334 ame_util.runtimeException(packageNameIn => 'ame_api3',
335 routineNameIn => 'getGroupMembers3',
336 exceptionNumberIn => errorCode,
337 exceptionStringIn => errorMessage);
338 raise_application_error(errorCode,
339 errorMessage);
340 when noTransIdDefinedException then
341 if(groupMemberCursor%isopen) then
342 close groupMemberCursor;
343 end if;
344 memberDisplayNamesOut.delete;
345 errorCode := -20001;
346 errorMessage := ame_util.getMessage(
347 applicationShortNameIn => 'PER',
348 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
349 tokenNameOneIn => 'APPROVAL_GROUP',
350 tokenValueOneIn => 'TO_BE_MODIFIED');
351 ame_util.runtimeException(packageNameIn => 'ame_api3',
352 routineNameIn => 'getGroupMembers1',
353 exceptionNumberIn => errorCode,
354 exceptionStringIn => errorMessage);
355 raise_application_error(errorCode,
356 errorMessage);
357 when others then
358 if(groupMemberCursor%isopen) then
359 close groupMemberCursor;
360 end if;
361 memberDisplayNamesOut.delete;
362 ame_util.runtimeException(packageNameIn => 'ame_api3',
363 routineNameIn => 'getGroupMembers1',
364 exceptionNumberIn => sqlcode,
365 exceptionStringIn => sqlerrm);
366 raise;
367 end getGroupMembers1;
368 procedure getGroupMembers2(applicationIdIn in number default null,
369 transactionTypeIn in varchar2 default null,
370 transactionIdIn in varchar2 default null,
371 itemClassIn in varchar2,
372 itemIdIn in varchar2,
373 groupIdIn in number,
374 memberNamesOut out nocopy ame_util.longStringList,
375 memberDisplayNamesOut out nocopy ame_util.longStringList)as
376 cursor groupMemberCursor(groupIdIn in integer) is
377 select
378 parameter,
379 upper(parameter_name),
380 query_string,
381 orig_system,
382 orig_system_id
383 from ame_approval_group_members
384 where
385 approval_group_id = groupIdIn
386 order by order_number;
387 badDynamicMemberException exception;
388 noItemBindException exception;
389 dynamicCursor integer;
390 colonLocation1 integer;
391 colonLocation2 integer;
392 displayNames ame_util.longStringList;
393 errorCode integer;
394 errorMessage ame_util.longestStringType;
395 approverNames ame_util.longStringList;
396 orig_systemList ame_util.stringList;
397 orig_system_idList ame_util.idList;
398 memberOrigSystem ame_util.stringType;
399 memberOrigSystemId number;
400 noTransIdDefinedException exception;
401 orderNumbers ame_util.idList;
402 origSystemIds ame_util.idList;
403 origSystems ame_util.stringList;
404 outputIndex integer;
405 parameters ame_util.longStringList;
406 queryStrings ame_util.longestStringList;
407 rowsFound integer;
408 tempGroupMembers dbms_sql.Varchar2_Table;
409 upperParameterNames ame_util.stringList;
410 tempGroupName ame_util.stringType;
411 l_error_code number;
412 begin
413 open groupMemberCursor(groupIdIn => groupIdIn);
414 fetch groupMemberCursor bulk collect
415 into
416 parameters,
417 upperParameterNames,
418 queryStrings,
419 orig_systemList,
420 orig_system_idList;
421 close groupMemberCursor;
422 outputIndex := 0; /* pre-increment */
423 for i in 1 .. parameters.count loop
424 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
425 dynamicCursor := dbms_sql.open_cursor;
426 dbms_sql.parse(dynamicCursor,
427 ame_util.removeReturns(stringIn => queryStrings(i),
428 replaceWithSpaces => true),
429 dbms_sql.native);
430 if(instrb(queryStrings(i),
431 ame_util.transactionIdPlaceholder) > 0) then
432 if transactionIdIn is null then
433 dbms_sql.close_cursor(dynamicCursor);
434 raise noTransIdDefinedException;
435 end if;
436 dbms_sql.bind_variable(dynamicCursor,
437 ame_util.transactionIdPlaceholder,
438 transactionIdIn,
439 50);
440 end if;
441 if(instrb(queryStrings(i),
442 ame_util2.itemClassPlaceHolder) > 0) then
443 if transactionIdIn is null then
444 dbms_sql.close_cursor(dynamicCursor);
445 raise noItemBindException;
446 end if;
447 dbms_sql.bind_variable(dynamicCursor,
448 ame_util2.itemClassPlaceHolder,
449 itemClassIn,
450 50);
451 end if;
452 if(instrb(queryStrings(i),
453 ame_util2.itemIdPlaceHolder) > 0) then
454 if transactionIdIn is null then
455 dbms_sql.close_cursor(dynamicCursor);
456 raise noItemBindException;
457 end if;
458 dbms_sql.bind_variable(dynamicCursor,
459 ame_util2.itemIdPlaceHolder,
460 itemIdIn,
461 50);
462 end if;
463 dbms_sql.define_array(dynamicCursor,
464 1,
465 tempGroupMembers,
466 100,
467 1);
468 rowsFound := dbms_sql.execute(dynamicCursor);
469 loop
470 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
471 dbms_sql.column_value(dynamicCursor,
472 1,
473 tempGroupMembers);
474 exit when rowsFound < 100;
475 end loop;
476 dbms_sql.close_cursor(dynamicCursor);
477 /*
478 Dynamic groups' query strings may return rows having one of two forms:
479 (1) approver_type:approver_id
480 (2) orig_system:orig_system_id:approver_name
481 */
482 for j in 1 .. tempGroupMembers.count loop
483 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
484 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
485 if(colonLocation1 = 0) then
486 raise badDynamicMemberException;
487 end if;
488 outputIndex := outputIndex + 1;
489 if(colonLocation2 = 0) then /* first case (old style) */
490 memberOrigSystemId :=
491 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
492 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
493 upper(ame_util.approverPersonId)) then
494 memberOrigSystem := ame_util.perOrigSystem;
495 else
496 memberOrigSystem := ame_util.fndUserOrigSystem;
497 end if;
498 else
499 memberOrigSystem :=
500 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
501 memberOrigSystemId :=
502 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
503 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
504 end if;
505
506 begin
507 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
508 origSystemIn => memberOrigSystem,
509 origSystemIdIn => memberOrigSystemId,
510 nameOut => memberNamesOut(outputIndex),
511 displayNameOut => memberDisplayNamesOut(outputIndex));
512 exception
513 when others then
514 l_error_code := sqlcode;
515 if l_error_code = -20213 then
516 errorCode := -20225;
517 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
518 messageNameIn => 'AME_400837_INV_APR_FOUND',
519 tokenNameOneIn => 'PROCESS_NAME',
520 tokenValueOneIn => 'ame_api7.getGroupMembers2',
521 tokenNameTwoIn => 'ORIG_SYSTEM',
522 tokenValueTwoIn => memberOrigSystem,
523 tokenNameThreeIn => 'ORIG_SYSEM_ID',
524 tokenValueThreeIn => memberOrigSystemId);
525 raise_application_error(errorCode,errorMessage);
526 end if;
527 raise;
528 end;
529 end loop;
530 else /* Copy the static group into the engGroup caches. */
531 outputIndex := outputIndex + 1;
532 begin
533 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
534 origSystemIn => orig_systemList(i),
535 origSystemIdIn => orig_system_idList(i),
536 nameOut => memberNamesOut(outputIndex),
537 displayNameOut => memberDisplayNamesOut(outputIndex));
538 exception
539 when others then
540 l_error_code := sqlcode;
541 if l_error_code = -20213 then
542 errorCode := -20225;
543 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
544 messageNameIn => 'AME_400837_INV_APR_FOUND',
545 tokenNameOneIn => 'PROCESS_NAME',
546 tokenValueOneIn => 'ame_api7.getGroupMembers2',
547 tokenNameTwoIn => 'ORIG_SYSTEM',
548 tokenValueTwoIn => orig_systemList(i),
549 tokenNameThreeIn => 'ORIG_SYSEM_ID',
550 tokenValueThreeIn => orig_system_idList(i));
551 raise_application_error(errorCode,errorMessage);
552 end if;
553 raise;
554 end;
555 end if;
556 end loop;
557 exception
558 when badDynamicMemberException then
559 if(groupMemberCursor%isopen) then
560 close groupMemberCursor;
561 end if;
562 errorCode := -20315;
563 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
564 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
565 ame_util.runtimeException(packageNameIn => 'ame_api3',
566 routineNameIn => 'getGroupMembers2',
567 exceptionNumberIn => errorCode,
568 exceptionStringIn => errorMessage);
569 raise_application_error(errorCode,
570 errorMessage);
571 when noTransIdDefinedException then
572 if(groupMemberCursor%isopen) then
573 close groupMemberCursor;
574 end if;
575 errorCode := -20001;
576 errorMessage := ame_util.getMessage(
577 applicationShortNameIn => 'PER',
578 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
579 tokenNameOneIn => 'APPROVAL_GROUP',
580 tokenValueOneIn => 'TO_BE_MODIFIED');
581 ame_util.runtimeException(packageNameIn => 'ame_api3',
582 routineNameIn => 'getGroupMembers2',
583 exceptionNumberIn => errorCode,
584 exceptionStringIn => errorMessage);
585 raise_application_error(errorCode,
586 errorMessage);
587 when noItemBindException then
588 if(groupMemberCursor%isopen) then
589 close groupMemberCursor;
590 end if;
591 memberDisplayNamesOut.delete;
592 errorCode := -20316;
593 errorMessage := ame_util.getMessage(
594 applicationShortNameIn => 'PER',
595 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
596 tokenNameOneIn => 'APPROVER_GROUP',
597 tokenValueOneIn => tempGroupName);
598 ame_util.runtimeException(packageNameIn => 'ame_api3',
599 routineNameIn => 'getGroupMembers3',
600 exceptionNumberIn => errorCode,
601 exceptionStringIn => errorMessage);
602 raise_application_error(errorCode,
603 errorMessage);
604 when others then
605 if(groupMemberCursor%isopen) then
606 close groupMemberCursor;
607 end if;
608 ame_util.runtimeException(packageNameIn => 'ame_api3',
609 routineNameIn => 'getGroupMembers2',
610 exceptionNumberIn => sqlcode,
611 exceptionStringIn => sqlerrm);
612 raise;
613 end getGroupMembers2;
614 procedure getGroupMembers3(applicationIdIn in number default null,
615 transactionTypeIn in varchar2 default null,
616 transactionIdIn in varchar2 default null,
617 itemClassIn in varchar2,
618 itemIdIn in varchar2,
619 groupIdIn in number,
620 memberNamesOut out nocopy ame_util.longStringList,
621 memberOrderNumbersOut out nocopy ame_util.idList,
622 memberDisplayNamesOut out nocopy ame_util.longStringList)as
623 cursor groupMemberCursor(groupIdIn in integer) is
624 select
625 parameter,
626 upper(parameter_name),
627 query_string,
628 order_number,
629 orig_system,
630 orig_system_id
631 from ame_approval_group_members
632 where
633 approval_group_id = groupIdIn
634 order by order_number;
635 badDynamicMemberException exception;
636 noItemBindException exception;
637 dynamicCursor integer;
638 colonLocation1 integer;
639 colonLocation2 integer;
640 displayNames ame_util.longStringList;
641 errorCode integer;
642 errorMessage ame_util.longestStringType;
643 approverNames ame_util.longStringList;
644 orig_systemList ame_util.stringList;
645 orig_systemIdList ame_util.idList;
646 memberOrigSystem ame_util.stringType;
647 memberOrigSystemId number;
648 noTransIdDefinedException exception;
649 orderNumbers ame_util.idList;
650 origSystemIds ame_util.idList;
651 origSystems ame_util.stringList;
652 outputIndex integer;
653 parameters ame_util.longStringList;
654 queryStrings ame_util.longestStringList;
655 rowsFound integer;
656 tempGroupMembers dbms_sql.Varchar2_Table;
657 tempGroupName ame_util.stringType;
658 upperParameterNames ame_util.stringList;
659 l_error_code number;
660 begin
661 open groupMemberCursor(groupIdIn => groupIdIn);
662 fetch groupMemberCursor bulk collect
663 into
664 parameters,
665 upperParameterNames,
666 queryStrings,
667 orderNumbers,
668 orig_systemList,
669 orig_systemIdList;
670 close groupMemberCursor;
671 outputIndex := 0; /* pre-increment */
672 for i in 1 .. parameters.count loop
673 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
674 dynamicCursor := dbms_sql.open_cursor;
675 dbms_sql.parse(dynamicCursor,
676 ame_util.removeReturns(stringIn => queryStrings(i),
677 replaceWithSpaces => true),
678 dbms_sql.native);
679 if(instrb(queryStrings(i),
680 ame_util.transactionIdPlaceholder) > 0) then
681 if transactionIdIn is null then
682 dbms_sql.close_cursor(dynamicCursor);
683 raise noTransIdDefinedException;
684 end if;
685 dbms_sql.bind_variable(dynamicCursor,
686 ame_util.transactionIdPlaceholder,
687 transactionIdIn,
688 50);
689 end if;
690 if(instrb(queryStrings(i),
691 ame_util2.itemClassPlaceHolder) > 0) then
692 if transactionIdIn is null then
693 dbms_sql.close_cursor(dynamicCursor);
694 raise noItemBindException;
695 end if;
696 dbms_sql.bind_variable(dynamicCursor,
697 ame_util2.itemClassPlaceHolder,
698 itemClassIn,
699 50);
700 end if;
701 if(instrb(queryStrings(i),
702 ame_util2.itemIdPlaceHolder) > 0) then
703 if transactionIdIn is null then
704 dbms_sql.close_cursor(dynamicCursor);
705 raise noItemBindException;
706 end if;
707 dbms_sql.bind_variable(dynamicCursor,
708 ame_util2.itemIdPlaceHolder,
709 itemIdIn,
710 50);
711 end if;
712 dbms_sql.define_array(dynamicCursor,
713 1,
714 tempGroupMembers,
715 100,
716 1);
717 rowsFound := dbms_sql.execute(dynamicCursor);
718 loop
719 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
720 dbms_sql.column_value(dynamicCursor,
721 1,
722 tempGroupMembers);
723 exit when rowsFound < 100;
724 end loop;
725 dbms_sql.close_cursor(dynamicCursor);
726 /*
727 Dynamic groups' query strings may return rows having one of two forms:
728 (1) approver_type:approver_id
729 (2) orig_system:orig_system_id:approver_name
730 */
731 for j in 1 .. tempGroupMembers.count loop
732 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
733 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
734 if(colonLocation1 = 0) then
735 raise badDynamicMemberException;
736 end if;
737 outputIndex := outputIndex + 1;
738 memberOrderNumbersOut(outputIndex) := j;
739 if(colonLocation2 = 0) then /* first case (old style) */
740 memberOrigSystemId :=
741 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
742 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
743 upper(ame_util.approverPersonId)) then
744 memberOrigSystem := ame_util.perOrigSystem;
745 else
746 memberOrigSystem := ame_util.fndUserOrigSystem;
747 end if;
748 else
749 memberOrigSystem :=
750 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
751 memberOrigSystemId :=
752 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
753 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
754 end if;
755 begin
756 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
757 origSystemIn => memberOrigSystem,
758 origSystemIdIn => memberOrigSystemId,
759 nameOut => memberNamesOut(outputIndex),
760 displayNameOut => memberDisplayNamesOut(outputIndex));
761 exception
762 when others then
763 l_error_code := sqlcode;
764 if l_error_code = -20213 then
765 errorCode := -20225;
766 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
767 messageNameIn => 'AME_400837_INV_APR_FOUND',
768 tokenNameOneIn => 'PROCESS_NAME',
769 tokenValueOneIn => 'ame_api7.getGroupMembers3',
770 tokenNameTwoIn => 'ORIG_SYSTEM',
771 tokenValueTwoIn => memberOrigSystem,
772 tokenNameThreeIn => 'ORIG_SYSEM_ID',
773 tokenValueThreeIn => memberOrigSystemId);
774 raise_application_error(errorCode,errorMessage);
775 end if;
776 raise;
777 end;
778 end loop;
779 else /* Copy the static group into the engGroup caches. */
780 outputIndex := outputIndex + 1;
781 memberOrderNumbersOut(outputIndex) := orderNumbers(i);
782 begin
783 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
784 origSystemIn => orig_systemList(i),
785 origSystemIdIn => orig_systemIdList(i),
786 nameOut => memberNamesOut(outputIndex),
787 displayNameOut => memberDisplayNamesOut(outputIndex));
788 exception
789 when others then
790 l_error_code := sqlcode;
791 if l_error_code = -20213 then
792 errorCode := -20225;
793 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
794 messageNameIn => 'AME_400837_INV_APR_FOUND',
795 tokenNameOneIn => 'PROCESS_NAME',
796 tokenValueOneIn => 'ame_api7.getGroupMembers3',
797 tokenNameTwoIn => 'ORIG_SYSTEM',
798 tokenValueTwoIn => orig_systemList(i),
799 tokenNameThreeIn => 'ORIG_SYSEM_ID',
800 tokenValueThreeIn => orig_systemIdList(i));
801 raise_application_error(errorCode,errorMessage);
802 end if;
803 raise;
804 end;
805 end if;
806 end loop;
807 exception
808 when badDynamicMemberException then
809 if(groupMemberCursor%isopen) then
810 close groupMemberCursor;
811 end if;
812 errorCode := -20315;
813 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
814 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
815 ame_util.runtimeException(packageNameIn => 'ame_api3',
816 routineNameIn => 'getGroupMembers3',
817 exceptionNumberIn => errorCode,
818 exceptionStringIn => errorMessage);
819 raise_application_error(errorCode,
820 errorMessage);
821 when noTransIdDefinedException then
822 if(groupMemberCursor%isopen) then
823 close groupMemberCursor;
824 end if;
825 errorCode := -20001;
826 errorMessage := ame_util.getMessage(
827 applicationShortNameIn => 'PER',
828 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
829 tokenNameOneIn => 'APPROVAL_GROUP',
830 tokenValueOneIn => 'TO_BE_MODIFIED');
831 ame_util.runtimeException(packageNameIn => 'ame_api3',
832 routineNameIn => 'getGroupMembers3',
833 exceptionNumberIn => errorCode,
834 exceptionStringIn => errorMessage);
835 raise_application_error(errorCode,
836 errorMessage);
837 when noItemBindException then
838 if(groupMemberCursor%isopen) then
839 close groupMemberCursor;
840 end if;
841 memberDisplayNamesOut.delete;
842 errorCode := -20316;
843 errorMessage := ame_util.getMessage(
844 applicationShortNameIn => 'PER',
845 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
846 tokenNameOneIn => 'APPROVER_GROUP',
847 tokenValueOneIn => tempGroupName);
848 ame_util.runtimeException(packageNameIn => 'ame_api3',
849 routineNameIn => 'getGroupMembers3',
850 exceptionNumberIn => errorCode,
851 exceptionStringIn => errorMessage);
852 raise_application_error(errorCode,
853 errorMessage);
854 when others then
855 if(groupMemberCursor%isopen) then
856 close groupMemberCursor;
857 end if;
858 ame_util.runtimeException(packageNameIn => 'ame_api3',
859 routineNameIn => 'getGroupMembers3',
860 exceptionNumberIn => sqlcode,
861 exceptionStringIn => sqlerrm);
862 raise;
863 end getGroupMembers3;
864 procedure getGroupMembers4(applicationIdIn in number default null,
865 transactionTypeIn in varchar2 default null,
866 transactionIdIn in varchar2 default null,
867 itemClassIn in varchar2,
868 itemIdIn in varchar2,
869 groupIdIn in number,
870 memberNamesOut out nocopy ame_util.longStringList,
871 memberOrderNumbersOut out nocopy ame_util.idList,
872 memberDisplayNamesOut out nocopy ame_util.longStringList,
873 memberOrigSystemIdsOut out nocopy ame_util.idList,
874 memberOrigSystemsOut out nocopy ame_util.stringList)as
875 cursor groupMemberCursor(groupIdIn in integer) is
876 select
877 orig_system,
878 orig_system_id,
879 parameter,
880 upper(parameter_name),
881 query_string,
882 order_number
883 from ame_approval_group_members
884 where
885 approval_group_id = groupIdIn
886 order by order_number;
887 badDynamicMemberException exception;
888 dynamicCursor integer;
889 colonLocation1 integer;
890 colonLocation2 integer;
891 displayNames ame_util.longStringList;
892 errorCode integer;
893 errorMessage ame_util.longestStringType;
894 approverNames ame_util.longStringList;
895 noTransIdDefinedException exception;
896 noItemBindException exception;
897 orderNumbers ame_util.idList;
898 origSystemIds ame_util.idList;
899 origSystems ame_util.stringList;
900 outputIndex integer;
901 parameters ame_util.longStringList;
902 queryStrings ame_util.longestStringList;
903 rowsFound integer;
904 tempGroupMembers dbms_sql.Varchar2_Table;
905 tempGroupName ame_util.stringType;
906 upperParameterNames ame_util.stringList;
907 l_error_code number;
908 begin
909 open groupMemberCursor(groupIdIn => groupIdIn);
910 fetch groupMemberCursor bulk collect
911 into
912 origSystems,
913 origSystemIds,
914 parameters,
915 upperParameterNames,
916 queryStrings,
917 orderNumbers;
918 close groupMemberCursor;
919 outputIndex := 0; /* pre-increment */
920 for i in 1 .. parameters.count loop
921 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
922 dynamicCursor := dbms_sql.open_cursor;
923 dbms_sql.parse(dynamicCursor,
924 ame_util.removeReturns(stringIn => queryStrings(i),
925 replaceWithSpaces => true),
926 dbms_sql.native);
927 if(instrb(queryStrings(i),
928 ame_util.transactionIdPlaceholder) > 0) then
929 if transactionIdIn is null then
930 dbms_sql.close_cursor(dynamicCursor);
931 raise noTransIdDefinedException;
932 end if;
933 dbms_sql.bind_variable(dynamicCursor,
934 ame_util.transactionIdPlaceholder,
935 transactionIdIn,
936 50);
937 end if;
938 if(instrb(queryStrings(i),
939 ame_util2.itemClassPlaceHolder) > 0) then
940 if transactionIdIn is null then
941 dbms_sql.close_cursor(dynamicCursor);
942 raise noItemBindException;
943 end if;
944 dbms_sql.bind_variable(dynamicCursor,
945 ame_util2.itemClassPlaceHolder,
946 itemClassIn,
947 50);
948 end if;
949 if(instrb(queryStrings(i),
950 ame_util2.itemIdPlaceHolder) > 0) then
951 if transactionIdIn is null then
952 dbms_sql.close_cursor(dynamicCursor);
953 raise noItemBindException;
954 end if;
955 dbms_sql.bind_variable(dynamicCursor,
956 ame_util2.itemIdPlaceHolder,
957 itemIdIn,
958 50);
959 end if;
960 dbms_sql.define_array(dynamicCursor,
961 1,
962 tempGroupMembers,
963 100,
964 1);
965 rowsFound := dbms_sql.execute(dynamicCursor);
966 loop
967 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
968 dbms_sql.column_value(dynamicCursor,
969 1,
970 tempGroupMembers);
971 exit when rowsFound < 100;
972 end loop;
973 dbms_sql.close_cursor(dynamicCursor);
974 /*
975 Dynamic groups' query strings may return rows having one of two forms:
976 (1) approver_type:approver_id
977 (2) orig_system:orig_system_id:approver_name
978 */
979 for j in 1 .. tempGroupMembers.count loop
980 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
981 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
982 if(colonLocation1 = 0) then
983 raise badDynamicMemberException;
984 end if;
985 outputIndex := outputIndex + 1;
986 memberOrderNumbersOut(outputIndex) := j;
987 if(colonLocation2 = 0) then /* first case (old style) */
988 memberOrigSystemIdsOut(outputIndex) :=
989 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
990 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
991 upper(ame_util.approverPersonId)) then
992 memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
993 else
994 memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
995 end if;
996 else
997 memberOrigSystemsOut(outputIndex) :=
998 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
999 memberOrigSystemIdsOut(outputIndex) :=
1000 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
1001 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
1002 end if;
1003 begin
1004 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
1005 origSystemIn => memberOrigSystemsOut(outputIndex),
1006 origSystemIdIn => memberOrigSystemIdsOut(outputIndex),
1007 nameOut => memberNamesOut(outputIndex),
1008 displayNameOut => memberDisplayNamesOut(outputIndex));
1009 exception
1010 when others then
1011 l_error_code := sqlcode;
1012 if l_error_code = -20213 then
1013 errorCode := -20225;
1014 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1015 messageNameIn => 'AME_400837_INV_APR_FOUND',
1016 tokenNameOneIn => 'PROCESS_NAME',
1017 tokenValueOneIn => 'ame_api7.getGroupMembers4',
1018 tokenNameTwoIn => 'ORIG_SYSTEM',
1019 tokenValueTwoIn => memberOrigSystemsOut(outputIndex),
1020 tokenNameThreeIn => 'ORIG_SYSEM_ID',
1021 tokenValueThreeIn => memberOrigSystemIdsOut(outputIndex));
1022 raise_application_error(errorCode,errorMessage);
1023 end if;
1024 raise;
1025 end;
1026 end loop;
1027 else /* Copy the static group into the engGroup caches. */
1028 outputIndex := outputIndex + 1;
1029 memberNamesOut(outputIndex) := approverNames(i);
1030 begin
1031 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
1032 origSystemIn => origSystems(i),
1033 origSystemIdIn => origSystemIds(i),
1034 nameOut => memberNamesOut(outputIndex),
1035 displayNameOut => memberDisplayNamesOut(outputIndex));
1036 exception
1037 when others then
1038 l_error_code := sqlcode;
1039 if l_error_code = -20213 then
1040 errorCode := -20225;
1041 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
1042 messageNameIn => 'AME_400837_INV_APR_FOUND',
1043 tokenNameOneIn => 'PROCESS_NAME',
1044 tokenValueOneIn => 'ame_api7.getGroupMembers4',
1045 tokenNameTwoIn => 'ORIG_SYSTEM',
1046 tokenValueTwoIn => origSystems(i),
1047 tokenNameThreeIn => 'ORIG_SYSEM_ID',
1048 tokenValueThreeIn => origSystemIds(i));
1049 raise_application_error(errorCode,errorMessage);
1050 end if;
1051 raise;
1052 end;
1053 memberOrigSystemsOut(outputIndex) := origSystems(i);
1054 memberOrigSystemIdsOut(outputIndex) := origSystemIds(i);
1055 end if;
1056 end loop;
1057 exception
1058 when badDynamicMemberException then
1059 if(groupMemberCursor%isopen) then
1060 close groupMemberCursor;
1061 end if;
1062 errorCode := -20315;
1063 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1064 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
1065 ame_util.runtimeException(packageNameIn => 'ame_api3',
1066 routineNameIn => 'getGroupMembers4',
1067 exceptionNumberIn => errorCode,
1068 exceptionStringIn => errorMessage);
1069 raise_application_error(errorCode,
1070 errorMessage);
1071 when noTransIdDefinedException then
1072 if(groupMemberCursor%isopen) then
1073 close groupMemberCursor;
1074 end if;
1075 errorCode := -20316;
1076 errorMessage := ame_util.getMessage(
1077 applicationShortNameIn => 'PER',
1078 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
1079 tokenNameOneIn => 'APPROVER_GROUP',
1080 tokenValueOneIn => tempGroupName);
1081 ame_util.runtimeException(packageNameIn => 'ame_api3',
1082 routineNameIn => 'getGroupMembers3',
1083 exceptionNumberIn => errorCode,
1084 exceptionStringIn => errorMessage);
1085 raise_application_error(errorCode,
1086 errorMessage);
1087 when others then
1088 if(groupMemberCursor%isopen) then
1089 close groupMemberCursor;
1090 end if;
1091 ame_util.runtimeException(packageNameIn => 'ame_api3',
1092 routineNameIn => 'getGroupMembers4',
1093 exceptionNumberIn => sqlcode,
1094 exceptionStringIn => sqlerrm);
1095 raise;
1096 end getGroupMembers4;
1097 end ame_api7;