[Home] [Help]
PACKAGE BODY: APPS.AME_API7
Source
1 package body ame_api7 as
2 /* $Header: ameeapi7.pkb 120.0 2005/11/28 07:24:33 srpurani noship $ */
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 := -20001;
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 decode(parameter_name,
139 ame_util.approverOamGroupId, null,
140 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
141 from ame_approval_group_members
142 where
143 approval_group_id = groupIdIn
144 order by order_number;
145 badDynamicMemberException exception;
146 noItemBindException exception;
147 dynamicCursor integer;
148 colonLocation1 integer;
149 colonLocation2 integer;
150 displayNames ame_util.longStringList;
151 errorCode integer;
152 errorMessage ame_util.longestStringType;
153 noTransIdDefinedException exception;
154 orderNumbers ame_util.idList;
155 memberOrigSystem ame_util.stringType;
156 memberOrigSystemId number;
157 outputIndex integer;
158 parameters ame_util.longStringList;
159 queryStrings ame_util.longestStringList;
160 rowsFound integer;
161 tempGroupMembers dbms_sql.Varchar2_Table;
162 upperParameterNames ame_util.stringList;
163 tempGroupName ame_util.stringType;
164 begin
165 open groupMemberCursor(groupIdIn => groupIdIn);
166 fetch groupMemberCursor bulk collect
167 into
168 parameters,
169 upperParameterNames,
170 queryStrings,
171 displayNames;
172 close groupMemberCursor;
173 outputIndex := 0; /* pre-increment */
174 for i in 1 .. parameters.count loop
175 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
176 dynamicCursor := dbms_sql.open_cursor;
177 dbms_sql.parse(dynamicCursor,
178 ame_util.removeReturns(stringIn => queryStrings(i),
179 replaceWithSpaces => true),
180 dbms_sql.native);
181 if(instrb(queryStrings(i),
182 ame_util.transactionIdPlaceholder) > 0) then
183 if transactionIdIn is null then
184 dbms_sql.close_cursor(dynamicCursor);
185 raise noTransIdDefinedException;
186 end if;
187 dbms_sql.bind_variable(dynamicCursor,
188 ame_util.transactionIdPlaceholder,
189 transactionIdIn,
190 50);
191 end if;
192 if(instrb(queryStrings(i),
193 ame_util2.itemClassPlaceHolder) > 0) then
194 if transactionIdIn is null then
195 dbms_sql.close_cursor(dynamicCursor);
196 raise noItemBindException;
197 end if;
198 dbms_sql.bind_variable(dynamicCursor,
199 ame_util2.itemClassPlaceHolder,
200 itemClassIn,
201 50);
202 end if;
203 if(instrb(queryStrings(i),
204 ame_util2.itemIdPlaceHolder) > 0) then
205 if transactionIdIn is null then
206 dbms_sql.close_cursor(dynamicCursor);
207 raise noItemBindException;
208 end if;
209 dbms_sql.bind_variable(dynamicCursor,
210 ame_util2.itemIdPlaceHolder,
211 itemIdIn,
212 50);
213 end if;
214 dbms_sql.define_array(dynamicCursor,
215 1,
216 tempGroupMembers,
217 100,
218 1);
219 rowsFound := dbms_sql.execute(dynamicCursor);
220 loop
221 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
222 dbms_sql.column_value(dynamicCursor,
223 1,
224 tempGroupMembers);
225 exit when rowsFound < 100;
226 end loop;
227 dbms_sql.close_cursor(dynamicCursor);
228 /*
229 Dynamic groups' query strings may return rows having one of two forms:
230 (1) approver_type:approver_id
231 (2) orig_system:orig_system_id:approver_name
232 */
233 for j in 1 .. tempGroupMembers.count loop
234 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
235 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
236 if(colonLocation1 = 0) then
237 raise badDynamicMemberException;
238 end if;
239 outputIndex := outputIndex + 1;
240 if(colonLocation2 = 0) then /* first case (old style) */
241 memberOrigSystemId :=
242 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
243 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
244 upper(ame_util.approverPersonId)) then
245 memberOrigSystem := ame_util.perOrigSystem;
246 else
247 memberOrigSystem := ame_util.fndUserOrigSystem;
248 end if;
249 else
250 memberOrigSystem :=
251 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
252 memberOrigSystemId :=
253 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
254 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
255 end if;
256 memberDisplayNamesOut(outputIndex) :=
257 ame_approver_type_pkg.getApproverDisplayName2(
258 origSystemIn => memberOrigSystem,
259 origSystemIdIn => memberOrigSystemId);
260 end loop;
261 else /* Copy the static group into the engGroup caches. */
262 outputIndex := outputIndex + 1;
263 memberDisplayNamesOut(outputIndex) := displayNames(i);
264 end if;
265 end loop;
266 exception
267 when badDynamicMemberException then
268 if(groupMemberCursor%isopen) then
269 close groupMemberCursor;
270 end if;
271 memberDisplayNamesOut.delete;
272 errorCode := -20001;
273 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
274 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
275 ame_util.runtimeException(packageNameIn => 'ame_api3',
276 routineNameIn => 'getGroupMembers1',
277 exceptionNumberIn => errorCode,
278 exceptionStringIn => errorMessage);
279 raise_application_error(errorCode,
280 errorMessage);
281 when noItemBindException then
285 memberDisplayNamesOut.delete;
282 if(groupMemberCursor%isopen) then
283 close groupMemberCursor;
284 end if;
286 errorCode := -20001;
287 ame_api5.getApprovalGroupName(groupIdIn => groupIdIn
288 ,groupNameOut => tempGroupName);
289 errorMessage := ame_util.getMessage(
290 applicationShortNameIn => 'PER',
291 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
292 tokenNameOneIn => 'APPROVER_GROUP',
293 tokenValueOneIn => tempGroupName);
294 ame_util.runtimeException(packageNameIn => 'ame_api3',
295 routineNameIn => 'getGroupMembers3',
296 exceptionNumberIn => errorCode,
297 exceptionStringIn => errorMessage);
298 raise_application_error(errorCode,
299 errorMessage);
300 when noTransIdDefinedException then
301 if(groupMemberCursor%isopen) then
302 close groupMemberCursor;
303 end if;
304 memberDisplayNamesOut.delete;
305 errorCode := -20001;
306 errorMessage := ame_util.getMessage(
307 applicationShortNameIn => 'PER',
308 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
309 tokenNameOneIn => 'APPROVAL_GROUP',
310 tokenValueOneIn => 'TO_BE_MODIFIED');
311 ame_util.runtimeException(packageNameIn => 'ame_api3',
312 routineNameIn => 'getGroupMembers1',
313 exceptionNumberIn => errorCode,
314 exceptionStringIn => errorMessage);
315 raise_application_error(errorCode,
316 errorMessage);
317 when others then
318 if(groupMemberCursor%isopen) then
319 close groupMemberCursor;
320 end if;
321 memberDisplayNamesOut.delete;
322 ame_util.runtimeException(packageNameIn => 'ame_api3',
323 routineNameIn => 'getGroupMembers1',
324 exceptionNumberIn => sqlcode,
325 exceptionStringIn => sqlerrm);
326 raise;
327 end getGroupMembers1;
328 procedure getGroupMembers2(applicationIdIn in number default null,
329 transactionTypeIn in varchar2 default null,
330 transactionIdIn in varchar2 default null,
331 itemClassIn in varchar2,
332 itemIdIn in varchar2,
333 groupIdIn in number,
334 memberNamesOut out nocopy ame_util.longStringList,
335 memberDisplayNamesOut out nocopy ame_util.longStringList)as
336 cursor groupMemberCursor(groupIdIn in integer) is
337 select
338 parameter,
339 upper(parameter_name),
340 query_string,
341 decode(parameter_name,
342 ame_util.approverOamGroupId, null,
343 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
344 decode(parameter_name,
345 ame_util.approverOamGroupId, null,
346 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
347 from ame_approval_group_members
348 where
349 approval_group_id = groupIdIn
350 order by order_number;
351 badDynamicMemberException exception;
352 noItemBindException exception;
353 dynamicCursor integer;
354 colonLocation1 integer;
355 colonLocation2 integer;
356 displayNames ame_util.longStringList;
357 errorCode integer;
358 errorMessage ame_util.longestStringType;
359 approverNames ame_util.longStringList;
360 memberOrigSystem ame_util.stringType;
361 memberOrigSystemId number;
362 noTransIdDefinedException exception;
363 orderNumbers ame_util.idList;
364 origSystemIds ame_util.idList;
365 origSystems ame_util.stringList;
366 outputIndex integer;
367 parameters ame_util.longStringList;
368 queryStrings ame_util.longestStringList;
369 rowsFound integer;
370 tempGroupMembers dbms_sql.Varchar2_Table;
371 upperParameterNames ame_util.stringList;
372 tempGroupName ame_util.stringType;
373 begin
374 open groupMemberCursor(groupIdIn => groupIdIn);
375 fetch groupMemberCursor bulk collect
376 into
377 parameters,
378 upperParameterNames,
379 queryStrings,
380 approverNames,
381 displayNames;
382 close groupMemberCursor;
383 outputIndex := 0; /* pre-increment */
384 for i in 1 .. parameters.count loop
385 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
386 dynamicCursor := dbms_sql.open_cursor;
387 dbms_sql.parse(dynamicCursor,
388 ame_util.removeReturns(stringIn => queryStrings(i),
389 replaceWithSpaces => true),
390 dbms_sql.native);
391 if(instrb(queryStrings(i),
392 ame_util.transactionIdPlaceholder) > 0) then
393 if transactionIdIn is null then
394 dbms_sql.close_cursor(dynamicCursor);
395 raise noTransIdDefinedException;
396 end if;
397 dbms_sql.bind_variable(dynamicCursor,
401 end if;
398 ame_util.transactionIdPlaceholder,
399 transactionIdIn,
400 50);
402 if(instrb(queryStrings(i),
403 ame_util2.itemClassPlaceHolder) > 0) then
404 if transactionIdIn is null then
405 dbms_sql.close_cursor(dynamicCursor);
406 raise noItemBindException;
407 end if;
408 dbms_sql.bind_variable(dynamicCursor,
409 ame_util2.itemClassPlaceHolder,
410 itemClassIn,
411 50);
412 end if;
413 if(instrb(queryStrings(i),
414 ame_util2.itemIdPlaceHolder) > 0) then
415 if transactionIdIn is null then
416 dbms_sql.close_cursor(dynamicCursor);
417 raise noItemBindException;
418 end if;
419 dbms_sql.bind_variable(dynamicCursor,
420 ame_util2.itemIdPlaceHolder,
421 itemIdIn,
422 50);
423 end if;
424 dbms_sql.define_array(dynamicCursor,
425 1,
426 tempGroupMembers,
427 100,
428 1);
429 rowsFound := dbms_sql.execute(dynamicCursor);
430 loop
431 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
432 dbms_sql.column_value(dynamicCursor,
433 1,
434 tempGroupMembers);
435 exit when rowsFound < 100;
436 end loop;
437 dbms_sql.close_cursor(dynamicCursor);
438 /*
439 Dynamic groups' query strings may return rows having one of two forms:
440 (1) approver_type:approver_id
441 (2) orig_system:orig_system_id:approver_name
442 */
443 for j in 1 .. tempGroupMembers.count loop
444 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
445 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
446 if(colonLocation1 = 0) then
447 raise badDynamicMemberException;
448 end if;
449 outputIndex := outputIndex + 1;
450 if(colonLocation2 = 0) then /* first case (old style) */
451 memberOrigSystemId :=
452 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
453 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
454 upper(ame_util.approverPersonId)) then
455 memberOrigSystem := ame_util.perOrigSystem;
456 else
457 memberOrigSystem := ame_util.fndUserOrigSystem;
458 end if;
459 else
460 memberOrigSystem :=
461 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
462 memberOrigSystemId :=
463 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
464 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
465 end if;
466 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
467 origSystemIn => memberOrigSystem,
468 origSystemIdIn => memberOrigSystemId,
469 nameOut => memberNamesOut(outputIndex),
470 displayNameOut => memberDisplayNamesOut(outputIndex));
471 end loop;
472 else /* Copy the static group into the engGroup caches. */
473 outputIndex := outputIndex + 1;
474 memberNamesOut(outputIndex) := approverNames(i);
475 memberDisplayNamesOut(outputIndex) := displayNames(i);
476 end if;
477 end loop;
478 exception
479 when badDynamicMemberException then
480 if(groupMemberCursor%isopen) then
481 close groupMemberCursor;
482 end if;
483 errorCode := -20001;
484 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
485 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
486 ame_util.runtimeException(packageNameIn => 'ame_api3',
487 routineNameIn => 'getGroupMembers2',
488 exceptionNumberIn => errorCode,
489 exceptionStringIn => errorMessage);
490 raise_application_error(errorCode,
491 errorMessage);
492 when noTransIdDefinedException then
493 if(groupMemberCursor%isopen) then
494 close groupMemberCursor;
495 end if;
496 errorCode := -20001;
497 errorMessage := ame_util.getMessage(
498 applicationShortNameIn => 'PER',
499 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
500 tokenNameOneIn => 'APPROVAL_GROUP',
501 tokenValueOneIn => 'TO_BE_MODIFIED');
502 ame_util.runtimeException(packageNameIn => 'ame_api3',
503 routineNameIn => 'getGroupMembers2',
504 exceptionNumberIn => errorCode,
505 exceptionStringIn => errorMessage);
506 raise_application_error(errorCode,
507 errorMessage);
508 when noItemBindException then
509 if(groupMemberCursor%isopen) then
510 close groupMemberCursor;
514 errorMessage := ame_util.getMessage(
511 end if;
512 memberDisplayNamesOut.delete;
513 errorCode := -20001;
515 applicationShortNameIn => 'PER',
516 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
517 tokenNameOneIn => 'APPROVER_GROUP',
518 tokenValueOneIn => tempGroupName);
519 ame_util.runtimeException(packageNameIn => 'ame_api3',
520 routineNameIn => 'getGroupMembers3',
521 exceptionNumberIn => errorCode,
522 exceptionStringIn => errorMessage);
523 raise_application_error(errorCode,
524 errorMessage);
525 when others then
526 if(groupMemberCursor%isopen) then
527 close groupMemberCursor;
528 end if;
529 ame_util.runtimeException(packageNameIn => 'ame_api3',
530 routineNameIn => 'getGroupMembers2',
531 exceptionNumberIn => sqlcode,
532 exceptionStringIn => sqlerrm);
533 raise;
534 end getGroupMembers2;
535 procedure getGroupMembers3(applicationIdIn in number default null,
536 transactionTypeIn in varchar2 default null,
537 transactionIdIn in varchar2 default null,
538 itemClassIn in varchar2,
539 itemIdIn in varchar2,
540 groupIdIn in number,
541 memberNamesOut out nocopy ame_util.longStringList,
542 memberOrderNumbersOut out nocopy ame_util.idList,
543 memberDisplayNamesOut out nocopy ame_util.longStringList)as
544 cursor groupMemberCursor(groupIdIn in integer) is
545 select
546 parameter,
547 upper(parameter_name),
548 query_string,
549 order_number,
550 decode(parameter_name,
551 ame_util.approverOamGroupId, null,
552 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
553 decode(parameter_name,
554 ame_util.approverOamGroupId, null,
555 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
556 from ame_approval_group_members
557 where
558 approval_group_id = groupIdIn
559 order by order_number;
560 badDynamicMemberException exception;
561 noItemBindException exception;
562 dynamicCursor integer;
563 colonLocation1 integer;
564 colonLocation2 integer;
565 displayNames ame_util.longStringList;
566 errorCode integer;
567 errorMessage ame_util.longestStringType;
568 approverNames ame_util.longStringList;
569 memberOrigSystem ame_util.stringType;
570 memberOrigSystemId number;
571 noTransIdDefinedException exception;
572 orderNumbers ame_util.idList;
573 origSystemIds ame_util.idList;
574 origSystems ame_util.stringList;
575 outputIndex integer;
576 parameters ame_util.longStringList;
577 queryStrings ame_util.longestStringList;
578 rowsFound integer;
579 tempGroupMembers dbms_sql.Varchar2_Table;
580 tempGroupName ame_util.stringType;
581 upperParameterNames ame_util.stringList;
582 begin
583 open groupMemberCursor(groupIdIn => groupIdIn);
584 fetch groupMemberCursor bulk collect
585 into
586 parameters,
587 upperParameterNames,
588 queryStrings,
589 orderNumbers,
590 approverNames,
591 displayNames;
592 close groupMemberCursor;
593 outputIndex := 0; /* pre-increment */
594 for i in 1 .. parameters.count loop
595 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
596 dynamicCursor := dbms_sql.open_cursor;
597 dbms_sql.parse(dynamicCursor,
598 ame_util.removeReturns(stringIn => queryStrings(i),
599 replaceWithSpaces => true),
600 dbms_sql.native);
601 if(instrb(queryStrings(i),
602 ame_util.transactionIdPlaceholder) > 0) then
603 if transactionIdIn is null then
604 dbms_sql.close_cursor(dynamicCursor);
605 raise noTransIdDefinedException;
606 end if;
607 dbms_sql.bind_variable(dynamicCursor,
608 ame_util.transactionIdPlaceholder,
609 transactionIdIn,
610 50);
611 end if;
612 if(instrb(queryStrings(i),
613 ame_util2.itemClassPlaceHolder) > 0) then
614 if transactionIdIn is null then
615 dbms_sql.close_cursor(dynamicCursor);
616 raise noItemBindException;
617 end if;
618 dbms_sql.bind_variable(dynamicCursor,
619 ame_util2.itemClassPlaceHolder,
620 itemClassIn,
621 50);
622 end if;
623 if(instrb(queryStrings(i),
624 ame_util2.itemIdPlaceHolder) > 0) then
625 if transactionIdIn is null then
626 dbms_sql.close_cursor(dynamicCursor);
627 raise noItemBindException;
628 end if;
629 dbms_sql.bind_variable(dynamicCursor,
633 end if;
630 ame_util2.itemIdPlaceHolder,
631 itemIdIn,
632 50);
634 dbms_sql.define_array(dynamicCursor,
635 1,
636 tempGroupMembers,
637 100,
638 1);
639 rowsFound := dbms_sql.execute(dynamicCursor);
640 loop
641 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
642 dbms_sql.column_value(dynamicCursor,
643 1,
644 tempGroupMembers);
645 exit when rowsFound < 100;
646 end loop;
647 dbms_sql.close_cursor(dynamicCursor);
648 /*
649 Dynamic groups' query strings may return rows having one of two forms:
650 (1) approver_type:approver_id
651 (2) orig_system:orig_system_id:approver_name
652 */
653 for j in 1 .. tempGroupMembers.count loop
654 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
655 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
656 if(colonLocation1 = 0) then
657 raise badDynamicMemberException;
658 end if;
659 outputIndex := outputIndex + 1;
660 memberOrderNumbersOut(outputIndex) := j;
661 if(colonLocation2 = 0) then /* first case (old style) */
662 memberOrigSystemId :=
663 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
664 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
665 upper(ame_util.approverPersonId)) then
666 memberOrigSystem := ame_util.perOrigSystem;
667 else
668 memberOrigSystem := ame_util.fndUserOrigSystem;
669 end if;
670 else
671 memberOrigSystem :=
672 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
673 memberOrigSystemId :=
674 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
675 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
676 end if;
677 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
678 origSystemIn => memberOrigSystem,
679 origSystemIdIn => memberOrigSystemId,
680 nameOut => memberNamesOut(outputIndex),
681 displayNameOut => memberDisplayNamesOut(outputIndex));
682 end loop;
683 else /* Copy the static group into the engGroup caches. */
684 outputIndex := outputIndex + 1;
685 memberOrderNumbersOut(outputIndex) := orderNumbers(i);
686 memberNamesOut(outputIndex) := approverNames(i);
687 memberDisplayNamesOut(outputIndex) := displayNames(i);
688 end if;
689 end loop;
690 exception
691 when badDynamicMemberException then
692 if(groupMemberCursor%isopen) then
693 close groupMemberCursor;
694 end if;
695 errorCode := -20001;
696 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
697 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
698 ame_util.runtimeException(packageNameIn => 'ame_api3',
699 routineNameIn => 'getGroupMembers3',
700 exceptionNumberIn => errorCode,
701 exceptionStringIn => errorMessage);
702 raise_application_error(errorCode,
703 errorMessage);
704 when noTransIdDefinedException then
705 if(groupMemberCursor%isopen) then
706 close groupMemberCursor;
707 end if;
708 errorCode := -20001;
709 errorMessage := ame_util.getMessage(
710 applicationShortNameIn => 'PER',
711 messageNameIn => 'AME_400455_GRP_DYN_NULL_TXID',
712 tokenNameOneIn => 'APPROVAL_GROUP',
713 tokenValueOneIn => 'TO_BE_MODIFIED');
714 ame_util.runtimeException(packageNameIn => 'ame_api3',
715 routineNameIn => 'getGroupMembers3',
716 exceptionNumberIn => errorCode,
717 exceptionStringIn => errorMessage);
718 raise_application_error(errorCode,
719 errorMessage);
720 when noItemBindException then
721 if(groupMemberCursor%isopen) then
722 close groupMemberCursor;
723 end if;
724 memberDisplayNamesOut.delete;
725 errorCode := -20001;
726 errorMessage := ame_util.getMessage(
727 applicationShortNameIn => 'PER',
728 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
729 tokenNameOneIn => 'APPROVER_GROUP',
730 tokenValueOneIn => tempGroupName);
731 ame_util.runtimeException(packageNameIn => 'ame_api3',
732 routineNameIn => 'getGroupMembers3',
733 exceptionNumberIn => errorCode,
734 exceptionStringIn => errorMessage);
735 raise_application_error(errorCode,
736 errorMessage);
737 when others then
738 if(groupMemberCursor%isopen) then
739 close groupMemberCursor;
743 exceptionNumberIn => sqlcode,
740 end if;
741 ame_util.runtimeException(packageNameIn => 'ame_api3',
742 routineNameIn => 'getGroupMembers3',
744 exceptionStringIn => sqlerrm);
745 raise;
746 end getGroupMembers3;
747 procedure getGroupMembers4(applicationIdIn in number default null,
748 transactionTypeIn in varchar2 default null,
749 transactionIdIn in varchar2 default null,
750 itemClassIn in varchar2,
751 itemIdIn in varchar2,
752 groupIdIn in number,
753 memberNamesOut out nocopy ame_util.longStringList,
754 memberOrderNumbersOut out nocopy ame_util.idList,
755 memberDisplayNamesOut out nocopy ame_util.longStringList,
756 memberOrigSystemIdsOut out nocopy ame_util.idList,
757 memberOrigSystemsOut out nocopy ame_util.stringList)as
758 cursor groupMemberCursor(groupIdIn in integer) is
759 select
760 orig_system,
761 orig_system_id,
762 parameter,
763 upper(parameter_name),
764 query_string,
765 order_number,
766 decode(parameter_name,
767 ame_util.approverOamGroupId, null,
768 ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) approver_name,
769 decode(parameter_name,
770 ame_util.approverOamGroupId, null,
771 ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
772 from ame_approval_group_members
773 where
774 approval_group_id = groupIdIn
775 order by order_number;
776 badDynamicMemberException exception;
777 dynamicCursor integer;
778 colonLocation1 integer;
779 colonLocation2 integer;
780 displayNames ame_util.longStringList;
781 errorCode integer;
782 errorMessage ame_util.longestStringType;
783 approverNames ame_util.longStringList;
784 noTransIdDefinedException exception;
785 noItemBindException exception;
786 orderNumbers ame_util.idList;
787 origSystemIds ame_util.idList;
788 origSystems ame_util.stringList;
789 outputIndex integer;
790 parameters ame_util.longStringList;
791 queryStrings ame_util.longestStringList;
792 rowsFound integer;
793 tempGroupMembers dbms_sql.Varchar2_Table;
794 tempGroupName ame_util.stringType;
795 upperParameterNames ame_util.stringList;
796 begin
797 open groupMemberCursor(groupIdIn => groupIdIn);
798 fetch groupMemberCursor bulk collect
799 into
800 origSystems,
801 origSystemIds,
802 parameters,
803 upperParameterNames,
804 queryStrings,
805 orderNumbers,
806 approverNames,
807 displayNames;
808 close groupMemberCursor;
809 outputIndex := 0; /* pre-increment */
810 for i in 1 .. parameters.count loop
811 if(upperParameterNames(i) = upper(ame_util.approverOamGroupId)) then
812 dynamicCursor := dbms_sql.open_cursor;
813 dbms_sql.parse(dynamicCursor,
814 ame_util.removeReturns(stringIn => queryStrings(i),
815 replaceWithSpaces => true),
816 dbms_sql.native);
817 if(instrb(queryStrings(i),
818 ame_util.transactionIdPlaceholder) > 0) then
819 if transactionIdIn is null then
820 dbms_sql.close_cursor(dynamicCursor);
821 raise noTransIdDefinedException;
822 end if;
823 dbms_sql.bind_variable(dynamicCursor,
824 ame_util.transactionIdPlaceholder,
825 transactionIdIn,
826 50);
827 end if;
828 if(instrb(queryStrings(i),
829 ame_util2.itemClassPlaceHolder) > 0) then
830 if transactionIdIn is null then
831 dbms_sql.close_cursor(dynamicCursor);
832 raise noItemBindException;
833 end if;
834 dbms_sql.bind_variable(dynamicCursor,
835 ame_util2.itemClassPlaceHolder,
836 itemClassIn,
837 50);
838 end if;
839 if(instrb(queryStrings(i),
840 ame_util2.itemIdPlaceHolder) > 0) then
841 if transactionIdIn is null then
842 dbms_sql.close_cursor(dynamicCursor);
843 raise noItemBindException;
844 end if;
845 dbms_sql.bind_variable(dynamicCursor,
846 ame_util2.itemIdPlaceHolder,
847 itemIdIn,
848 50);
849 end if;
850 dbms_sql.define_array(dynamicCursor,
851 1,
852 tempGroupMembers,
853 100,
854 1);
855 rowsFound := dbms_sql.execute(dynamicCursor);
856 loop
857 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
858 dbms_sql.column_value(dynamicCursor,
859 1,
863 dbms_sql.close_cursor(dynamicCursor);
860 tempGroupMembers);
861 exit when rowsFound < 100;
862 end loop;
864 /*
865 Dynamic groups' query strings may return rows having one of two forms:
866 (1) approver_type:approver_id
867 (2) orig_system:orig_system_id:approver_name
868 */
869 for j in 1 .. tempGroupMembers.count loop
870 colonLocation1 := instrb(tempGroupMembers(j), ':', 1, 1);
871 colonLocation2 := instrb(tempGroupMembers(j), ':', 1, 2);
872 if(colonLocation1 = 0) then
873 raise badDynamicMemberException;
874 end if;
875 outputIndex := outputIndex + 1;
876 memberOrderNumbersOut(outputIndex) := j;
877 if(colonLocation2 = 0) then /* first case (old style) */
878 memberOrigSystemIdsOut(outputIndex) :=
879 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1) + 1));
880 if(substrb(upper(tempGroupMembers(j)), 1, (instrb(tempGroupMembers(j), ':', 1, 1) - 1)) =
881 upper(ame_util.approverPersonId)) then
882 memberOrigSystemsOut(outputIndex) := ame_util.perOrigSystem;
883 else
884 memberOrigSystemsOut(outputIndex) := ame_util.fndUserOrigSystem;
885 end if;
886 else
887 memberOrigSystemsOut(outputIndex) :=
888 substrb(tempGroupMembers(j), 1, (instrb(tempGroupMembers(j), ':', 1, 1)-1));
889 memberOrigSystemIdsOut(outputIndex) :=
890 substrb(tempGroupMembers(j), (instrb(tempGroupMembers(j), ':', 1, 1)+1),
891 (instrb(tempGroupMembers(j), ':', 1, 2)-1));
892 end if;
893 ame_approver_type_pkg.getWfRolesNameAndDisplayName(
894 origSystemIn => memberOrigSystemsOut(outputIndex),
895 origSystemIdIn => memberOrigSystemIdsOut(outputIndex),
896 nameOut => memberNamesOut(outputIndex),
897 displayNameOut => memberDisplayNamesOut(outputIndex));
898 end loop;
899 else /* Copy the static group into the engGroup caches. */
900 outputIndex := outputIndex + 1;
901 memberNamesOut(outputIndex) := approverNames(i);
902 memberOrderNumbersOut(outputIndex) := orderNumbers(i);
903 memberDisplayNamesOut(outputIndex) := displayNames(i);
904 memberOrigSystemsOut(outputIndex) := origSystems(i);
905 memberOrigSystemIdsOut(outputIndex) := origSystemIds(i);
906 end if;
907 end loop;
908 exception
909 when badDynamicMemberException then
910 if(groupMemberCursor%isopen) then
911 close groupMemberCursor;
912 end if;
913 errorCode := -20001;
914 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
915 messageNameIn => 'AME_400454_GRP_DYN_QRY_ERR');
916 ame_util.runtimeException(packageNameIn => 'ame_api3',
917 routineNameIn => 'getGroupMembers4',
918 exceptionNumberIn => errorCode,
919 exceptionStringIn => errorMessage);
920 raise_application_error(errorCode,
921 errorMessage);
922 when noTransIdDefinedException then
923 if(groupMemberCursor%isopen) then
924 close groupMemberCursor;
925 end if;
926 errorCode := -20001;
927 errorMessage := ame_util.getMessage(
928 applicationShortNameIn => 'PER',
929 messageNameIn => 'AME_400798_GROUP_ITEM_BIND',
930 tokenNameOneIn => 'APPROVER_GROUP',
931 tokenValueOneIn => tempGroupName);
932 ame_util.runtimeException(packageNameIn => 'ame_api3',
933 routineNameIn => 'getGroupMembers3',
934 exceptionNumberIn => errorCode,
935 exceptionStringIn => errorMessage);
936 raise_application_error(errorCode,
937 errorMessage);
938 when others then
939 if(groupMemberCursor%isopen) then
940 close groupMemberCursor;
941 end if;
942 ame_util.runtimeException(packageNameIn => 'ame_api3',
943 routineNameIn => 'getGroupMembers4',
944 exceptionNumberIn => sqlcode,
945 exceptionStringIn => sqlerrm);
946 raise;
947 end getGroupMembers4;
948 end ame_api7;