[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVER_TYPE_PKG
Source
1 package body ame_approver_type_pkg as
2 /* $Header: ameoatyp.pkb 120.8.12020000.3 2012/09/25 12:32:25 nivsubra ship $ */
3 function getApproverDescription(nameIn in varchar2) return varchar2 as
4 descriptionOut ame_util.longestStringType;
5 validityOut boolean;
6 begin
7 /*
8 getApproverDescAndValidity checks for invalid approvers and produces
9 a description string even for these, prepending to an invalid approver's
10 description a label identifying the approver as invalid.If the approver is valid,
11 the wf_roles display name is returned.
12 */
13 getApproverDescAndValidity(nameIn => nameIn,
14 descriptionOut => descriptionOut,
15 validityOut => validityOut);
16 return(descriptionOut);
17 exception
18 when others then
19 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
20 routineNameIn => 'getApproverDescription',
21 exceptionNumberIn => sqlcode,
22 exceptionStringIn => sqlerrm);
23 raise;
24 end getApproverDescription;
25
26 function getApproverDescription2(origSystemIn in varchar2
27 ,origSystemIdIn in integer
28 ,raiseNoDataFoundIn in varchar2 default 'true')
29 return varchar2 as
30 errorCode integer;
31 errorMessage ame_util.longestStringType;
32 name wf_roles.display_name%type;
33 begin
34 name := getWfRolesName(origSystemIn => origSystemIn
35 ,origSystemIdIn => origSystemIdIn
36 ,raiseNoDataFoundIn => raiseNoDataFoundIn);
37 if(name is not null) then
38 return getApproverDisplayName(nameIn => name);
39 else
40 select display_name
41 into name
42 from wf_local_roles
43 where ((orig_system = origSystemIn and
44 orig_system_id = origSystemIdIn) or
45 (origSystemIn = ame_util.fndUserOrigSystem and
46 orig_system = ame_util.perOrigSystem and
47 orig_system_id = (select employee_id
48 from fnd_user
49 where user_id = origSystemIdIn)))
50 and status = 'ACTIVE'
51 and (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
52 or exists
53 (select null
54 from fnd_user u
55 where u.user_name = wf_local_roles.name))
56 and not exists (
57 select null from wf_local_roles wf2
58 where wf_local_roles.orig_system = wf2.orig_system
59 and wf_local_roles.orig_system_id = wf2.orig_system_id
60 and wf_local_roles.start_date > wf2.start_date)
61 and rownum < 2;
62 end if;
63 return('Invalid Approver: ' || name);
64 exception
65 when no_data_found then
66 if(raiseNoDataFoundIn = 'true') then
67 errorCode := -20213;
68 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
69 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
70 tokenNameOneIn => 'ORIG_SYSTEM_ID',
71 tokenValueOneIn => origSystemIdIn,
72 tokenNameTwoIn => 'ORIG_SYSTEM',
73 tokenValueTwoIn => origSystemIn
74 );
75 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
76 routineNameIn => 'getApproverDescription2',
77 exceptionNumberIn => errorCode,
78 exceptionStringIn => errorMessage);
79 raise_application_error(errorCode,
80 errorMessage);
81 end if;
82 return(null);
83 when others then
84 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
85 routineNameIn => 'getApproverDescription2',
86 exceptionNumberIn => sqlcode,
87 exceptionStringIn => sqlerrm);
88 raise;
89 return(null);
90 end getApproverDescription2;
91 function getApproverDisplayName(nameIn in varchar2) return varchar2 as
92 displayName wf_roles.display_name%type;
93 errorCode integer;
94 errorMessage ame_util.longestStringType;
95 begin
96 select display_name
97 into displayName
98 from wf_roles
99 where
100 name = nameIn and
101 status = 'ACTIVE' and
102 (expiration_date is null or
103 sysdate < expiration_date) and
104 rownum < 2;
105 return(displayName);
106 exception
107 when no_data_found then
108 errorCode := -20213;
109 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
110 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
111 tokenNameOneIn => 'NAME',
112 tokenValueOneIn => nameIn);
113 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
114 routineNameIn => 'getApproverDisplayName',
115 exceptionNumberIn => errorCode,
116 exceptionStringIn => errorMessage);
117 raise_application_error(errorCode,
118 errorMessage);
119 return(ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
120 attributeCodeIn => 'AME_INVALID_COLON') || nameIn);
121 when others then
122 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
123 routineNameIn => 'getApproverDisplayName',
124 exceptionNumberIn => sqlcode,
125 exceptionStringIn => sqlerrm);
126 raise;
127 return(ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
128 attributeCodeIn => 'AME_INVALID_COLON') || nameIn);
129 end getApproverDisplayName;
130 function getApproverDisplayName2(origSystemIn in varchar2,
131 origSystemIdIn in integer) return varchar2 as
132 displayName wf_roles.display_name%type;
133 errorCode integer;
134 errorMessage ame_util.longestStringType;
135 begin
136 /*
137 If an fnd_user entry has a non-null employee_id (person ID) value, it gets
138 converted to the PER originating system in wf_roles; otherwise, it gets
139 converted to the FND_USR originating system. As just one of these will
140 happen, we can match both originating systems in a single-row query.
141 The order-by-name clause and rownum < 2 condition are necessary because we
142 have encountered data problems where there are several entries for a given
143 orig_system and orig_system_id pair.
144 */
145 select display_name
146 into displayName
147 from wf_roles
148 where
149 ((orig_system = origSystemIn and
150 orig_system_id = origSystemIdIn) or
151 (origSystemIn = ame_util.fndUserOrigSystem and
152 orig_system = ame_util.perOrigSystem and
153 orig_system_id = (select employee_id
154 from fnd_user
155 where
156 user_id = origSystemIdIn and
157 sysdate between
158 start_date and
159 nvl(end_date, sysdate)))) and
160 status = 'ACTIVE' and
161 (expiration_date is null or
162 sysdate < expiration_date) and
163 (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
164 or exists (select null
165 from fnd_user u
166 where u.user_name = wf_roles.name
167 and trunc(sysdate) between u.start_date
168 and nvl(u.end_date,trunc(sysdate)))) and
169 not exists (
170 select null from wf_roles wf2
171 where wf_roles.orig_system = wf2.orig_system
172 and wf_roles.orig_system_id = wf2.orig_system_id
173 and wf_roles.start_date > wf2.start_date
174 ) and
175 rownum < 2;
176 return(displayName);
177 exception
178 when no_data_found then
179 errorCode := -20213;
180 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
181 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
182 tokenNameOneIn => 'ORIG_SYSTEM_ID',
183 tokenValueOneIn => origSystemIdIn,
184 tokenNameTwoIn => 'ORIG_SYSTEM',
185 tokenValueTwoIn => origSystemIn
186 );
187 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
188 routineNameIn => 'getApproverDisplayName2',
189 exceptionNumberIn => errorCode,
190 exceptionStringIn => errorMessage);
191 raise_application_error(errorCode,
192 errorMessage);
193 return(null);
194 when others then
195 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
196 routineNameIn => 'getApproverDisplayName2',
197 exceptionNumberIn => sqlcode,
198 exceptionStringIn => sqlerrm);
199 raise;
200 return(null);
201 end getApproverDisplayName2;
202 /*
203 This procedure returns the display name, origsystemid and origsystem for the given role name.
204 If the name is valid wf_roles name, validityOut is returned as true.If the name is not found,
205 validityOut is returned as false and the display name, origsystemid and origsystem are returned
206 from the wf_local_roles.
207 */
208 procedure getApproverDetails(nameIn in varchar2
209 ,validityOut out NOCOPY varchar2
210 ,displayNameOut out NOCOPY varchar2
211 ,origSystemIdOut out NOCOPY integer
212 ,origSystemOut out NOCOPY varchar2 ) as
213 begin
214 validityOut := 'INVALID';
215 select
216 display_name,
217 orig_system,
218 orig_system_id
219 into
220 displayNameOut,
221 origSystemOut,
222 origSystemIdOut
223 from wf_roles
224 where
225 name = nameIn and
226 status = 'ACTIVE' and
227 (expiration_date is null or
228 sysdate < expiration_date) and
229 rownum < 2;
230 validityOut := 'VALID';
231 exception
232 when no_data_found then
233 begin
234 /*
235 When the approver is not valid in WF_ROLES, try to get the approver info
236 from WF_LOCAL_ROLES. (It may still be there, even though the approver is
237 invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
238 See bug 3286313.
239 */
240 select
241 display_name,
242 orig_system,
243 orig_system_id
244 into
245 displayNameOut,
246 origSystemOut,
247 origSystemIdOut
248 from wf_local_roles
249 where
250 name = nameIn and
251 rownum < 2;
252 validityOut := 'INACTIVE';
253 exception
254 when no_data_found then
255 displayNameOut := nameIn;
256 origSystemOut := 'PER';
257 end;
258 end getApproverDetails;
259 /*
260 This function returns the displayname for the given wf_roles name.If the name is not valid,then
261 it returns the string 'Inactive: <old display name>'.If the display name is not available, then it
262 returns the string 'Invalid: <role_name>'.
263 */
264 function getApproverDisplayName3(nameIn in varchar2) return varchar2 as
265 validityOut varchar2(100);
266 displayNameOut ame_util.longestStringType;
267 origSystemIdOut integer;
268 origSystemOut wf_roles.orig_system%type;
269 begin
270 getApproverDetails(nameIn => nameIn
271 ,validityOut => validityOut
272 ,displayNameOut => displayNameOut
273 ,origSystemIdOut => origSystemIdOut
274 ,origSystemOut => origSystemOut );
275 if(validityOut = 'VALID') then
276 return displayNameOut;
277 else
278 return(ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
279 attributeCodeIn => 'AME_INVALID_COLON') || getOrigSystemDisplayName(origSystemIn => origSystemOut) || ':' || displayNameOut);
280 end if;
281 end getApproverDisplayName3;
282 /*
283 This function returns the displayname for the given wf_roles name.If the name is not valid,then
284 it returns the string 'Invalid : <origSystem display name> : <old display name>'
285 */
286 function getApproverDisplayName4(nameIn in varchar2) return varchar2 as
287 validityOut varchar2(100);
288 displayNameOut ame_util.longestStringType;
289 origSystemIdOut integer;
290 origSystemOut wf_roles.orig_system%type;
291 begin
292 getApproverDetails(nameIn => nameIn
293 ,validityOut => validityOut
294 ,displayNameOut => displayNameOut
295 ,origSystemIdOut => origSystemIdOut
296 ,origSystemOut => origSystemOut );
297 if(validityOut = 'VALID') then
298 return displayNameOut;
299 elsif(validityOut = 'INVALID') then
300 return(ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
301 attributeCodeIn => 'AME_INVALID_COLON') || displayNameOut);
302 else
303 return ame_util.getMessage(applicationShortNameIn => 'PER',
304 messageNameIn => 'AME_400790_INACTIVE_APPROVER',
305 tokenNameOneIn => 'NAME',
306 tokenValueOneIn => displayNameOut);
307
308 end if;
309 end getApproverDisplayName4;
310 function getApproverOrigSystem(nameIn in varchar2) return varchar2 as
311 errorCode integer;
312 errorMessage ame_util.longestStringType;
313 origSystem wf_roles.orig_system%type;
314 begin
315 select orig_system
316 into origSystem
317 from wf_roles
318 where
319 name = nameIn and
320 status = 'ACTIVE' and
321 (expiration_date is null or
322 sysdate < expiration_date) and
323 rownum < 2;
324 return(origSystem);
325 exception
326 when no_data_found then
327 errorCode := -20213;
328 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
329 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
330 tokenNameOneIn => 'NAME',
331 tokenValueOneIn => nameIn);
332 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
333 routineNameIn => 'getApproverOrigSystem',
334 exceptionNumberIn => errorCode,
335 exceptionStringIn => errorMessage);
336 raise_application_error(errorCode,
337 errorMessage);
338 return(null);
339 when others then
340 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
341 routineNameIn => 'getApproverOrigSystem',
342 exceptionNumberIn => sqlcode,
343 exceptionStringIn => sqlerrm);
344 raise;
345 return(null);
346 end getApproverOrigSystem;
347 function getApproverOrigSystem2(nameIn in varchar2) return varchar2 as
348 errorCode integer;
349 errorMessage ame_util.longestStringType;
350 origSystem wf_roles.orig_system%type;
351 begin
352 select orig_system
353 into origSystem
354 from wf_roles
355 where
356 name = nameIn and
357 status = 'ACTIVE' and
358 (expiration_date is null or
359 sysdate < expiration_date) and
360 rownum < 2;
361 return(origSystem);
362 exception
363 when no_data_found then
364 begin
365 /*
366 When the approver is not valid in WF_ROLES, try to get the approver info
367 from WF_LOCAL_ROLES. (It may still be there, even though the approver is
368 invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
369 See bug 3286313.
370 */
371 select
372 orig_system
373 into
374 origSystem
375 from wf_local_roles
376 where
377 name = nameIn and
378 rownum < 2;
379 return(origSystem);
380 exception
381 when no_data_found then
382 origSystem := 'PER';
383 return(origSystem);
384 end;
385 end getApproverOrigSystem2;
386 function getApproverOrigSystem3(nameIn in varchar2) return varchar2 as
387 errorCode integer;
388 errorMessage ame_util.longestStringType;
389 origSystem wf_roles.orig_system%type;
390 begin
391 select orig_system
392 into origSystem
393 from wf_roles
394 where
395 name = nameIn and
396 status = 'ACTIVE' and
397 (expiration_date is null or
398 sysdate < expiration_date) and
399 rownum < 2;
400 return(origSystem);
401 exception
402 when no_data_found then
403 begin
404 /*
405 When the approver is not valid in WF_ROLES, try to get the approver info
406 from WF_LOCAL_ROLES. (It may still be there, even though the approver is
407 invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
408 See bug 3286313.
409 */
410 select
411 orig_system
412 into
413 origSystem
414 from wf_local_roles
415 where
416 name = nameIn and
417 rownum < 2;
418 return(origSystem);
419 exception
420 when no_data_found then
421 origSystem := null;
422 return(origSystem);
423 end;
424 end getApproverOrigSystem3;
425 function getApproverOrigSystemId(nameIn in varchar2) return varchar2 as
426 errorCode integer;
427 errorMessage ame_util.longestStringType;
428 origSystemId wf_roles.orig_system_id%type;
429 begin
430 select orig_system_id
431 into origSystemId
432 from wf_roles
433 where
434 name = nameIn and
435 status = 'ACTIVE' and
436 (expiration_date is null or
437 sysdate < expiration_date) and
438 rownum < 2;
439 return(origSystemId);
440 exception
441 when no_data_found then
442 errorCode := -20213;
443 errorMessage :=
444 ame_util.getMessage(applicationShortNameIn => 'PER',
445 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
446 tokenNameOneIn => 'NAME',
447 tokenValueOneIn => nameIn);
448 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
449 routineNameIn => 'getApproverOrigSystemId',
450 exceptionNumberIn => errorCode,
451 exceptionStringIn => errorMessage);
452 raise_application_error(errorCode,
453 errorMessage);
454 return(null);
455 when others then
456 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
457 routineNameIn => 'getApproverOrigSystemId',
458 exceptionNumberIn => sqlcode,
459 exceptionStringIn => sqlerrm);
460 raise;
461 return(null);
462 end getApproverOrigSystemId;
463 function getApproverTypeId(origSystemIn in varchar2) return integer as
464 approverTypeId ame_approver_types.approver_type_id%type;
465 begin
466 select approver_type_id
467 into approverTypeId
468 from ame_approver_types
469 where
470 orig_system = origSystemIn and
471 sysdate between
472 start_date and
473 nvl(end_date - ame_util.oneSecond, sysdate) and
474 rownum < 2;
475 return(approverTypeId);
476 exception
477 when others then
478 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
479 routineNameIn => 'getApproverTypeId',
480 exceptionNumberIn => sqlcode,
481 exceptionStringIn => sqlerrm);
482 raise;
483 return(null);
484 end getApproverTypeId;
485 function getApproverTypeOrigSystem(approverTypeIdIn in integer) return varchar2 as
486 origSystem ame_approver_types.orig_system%type;
487 begin
488 select orig_system
489 into origSystem
490 from ame_approver_types
491 where
492 approver_type_id = approverTypeIdIn and
493 sysdate between
494 start_date and
495 nvl(end_date - ame_util.oneSecond, sysdate) and
496 rownum < 2;
497 return(origSystem);
498 exception
499 when others then
500 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
501 routineNameIn => 'getApproverTypeOrigSystem',
502 exceptionNumberIn => sqlcode,
503 exceptionStringIn => sqlerrm);
504 raise;
505 return(null);
506 end getApproverTypeOrigSystem;
507 function getApproverTypeDisplayName(approverTypeIdIn in integer) return varchar2 as
508 origSystem ame_approver_types.orig_system%type;
509 begin
510 select orig_system
511 into origSystem
512 from ame_approver_types
513 where
514 approver_type_id = approverTypeIdIn and
515 sysdate between start_date and
516 nvl(end_date - ame_util.oneSecond, sysdate) and
517 rownum < 2;
518 return(ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem));
519 exception
520 when others then
521 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
522 routineNameIn => 'getApproverTypeDisplayName',
523 exceptionNumberIn => sqlcode,
524 exceptionStringIn => sqlerrm);
525 raise;
526 return(null);
527 end getApproverTypeDisplayName;
528 function getOrigSystemDisplayName(origSystemIn in varchar2) return varchar2 as
529 origDisplayName fnd_lookups.meaning%type;
530 begin
531 select meaning
532 into origDisplayName
533 from fnd_lookups
534 where
535 lookup_type = ame_util.origSystemLookupType and
536 lookup_code = origSystemIn and
537 sysdate between
538 start_date_active and
539 nvl(end_date_active, sysdate) and
540 rownum < 2;
541 return(origDisplayName);
542 exception
543 when others then
544 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
545 routineNameIn => 'getOrigSystemDisplayName',
546 exceptionNumberIn => sqlcode,
547 exceptionStringIn => sqlerrm);
548 raise;
549 return(null);
550 end getOrigSystemDisplayName;
551 function getQueryProcedure(approverTypeIdIn in integer) return varchar2 as
552 queryProcedure ame_approver_types.query_procedure%type;
553 begin
554 select query_procedure
555 into queryProcedure
556 from ame_approver_types
557 where
558 approver_type_id = approverTypeIdIn and
559 sysdate between
560 start_date and
561 nvl(end_date - ame_util.oneSecond, sysdate) and
562 rownum < 2;
563 return(queryProcedure);
564 exception
565 when others then
566 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
567 routineNameIn => 'getQueryProcedure',
568 exceptionNumberIn => sqlcode,
569 exceptionStringIn => sqlerrm);
570 raise;
571 return(null);
572 end getQueryProcedure;
573 function getWfRolesName(origSystemIn in varchar2,
574 origSystemIdIn in integer,
575 raiseNoDataFoundIn in varchar2 default 'true') return varchar2 as
576 errorCode integer;
577 errorMessage ame_util.longestStringType;
578 name wf_roles.name%type;
579 begin
580 /*
581 If an fnd_user entry has a non-null employee_id (person ID) value, it gets
582 converted to the PER originating system in wf_roles; otherwise, it gets
583 converted to the FND_USR originating system. As just one of these will
584 happen, we can match both originating systems in a single-row query.
585 The order-by-name clause and rownum < 2 condition are necessary because we
586 have encountered data problems where there are several entries for a given
587 orig_system and orig_system_id pair.
588 */
589 select name
590 into name
591 from wf_roles
592 where
593 ((orig_system = origSystemIn and
594 orig_system_id = origSystemIdIn) or
595 (origSystemIn = ame_util.fndUserOrigSystem and
596 orig_system = ame_util.perOrigSystem and
597 orig_system_id = (select employee_id
598 from fnd_user
599 where
600 user_id = origSystemIdIn and
601 sysdate between
602 start_date and
603 nvl(end_date, sysdate)))) and
604 status = 'ACTIVE' and
605 (expiration_date is null or
606 sysdate < expiration_date) and
607 (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
608 or exists (select null
609 from fnd_user u
610 where u.user_name = wf_roles.name
611 and trunc(sysdate) between u.start_date
612 and nvl(u.end_date,trunc(sysdate)))) and
613 not exists (
614 select null from wf_roles wf2
615 where wf_roles.orig_system = wf2.orig_system
616 and wf_roles.orig_system_id = wf2.orig_system_id
617 and wf_roles.start_date > wf2.start_date
618 ) and
619 rownum < 2;
620 return(name);
621 exception
622 when no_data_found then
623 if(raiseNoDataFoundIn = 'true') then
624 errorCode := -20213;
625 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
626 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
627 tokenNameOneIn => 'ORIG_SYSTEM_ID',
628 tokenValueOneIn => origSystemIdIn,
629 tokenNameTwoIn => 'ORIG_SYSTEM',
630 tokenValueTwoIn => origSystemIn
631 );
632 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
633 routineNameIn => 'getWfRolesName',
634 exceptionNumberIn => errorCode,
635 exceptionStringIn => errorMessage);
636 raise_application_error(errorCode,
637 errorMessage);
638 end if;
639 return(null);
640 when others then
641 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
642 routineNameIn => 'getWfRolesName',
643 exceptionNumberIn => sqlcode,
644 exceptionStringIn => sqlerrm);
645 raise;
646 return(null);
647 end getWfRolesName;
648 function allowsAllApproverTypes(actionTypeIdIn in integer) return boolean as
649 rowCount integer;
650 begin
651 select count(*)
652 into rowCount
653 from ame_approver_type_usages
654 where
655 action_type_id = actionTypeIdIn and
656 approver_type_id = ame_util.anyApproverType and
657 sysdate between
658 start_date and
659 nvl(end_date - ame_util.oneSecond, sysdate);
660 if(rowCount > 0) then
661 return(true);
662 end if;
663 return false;
664 exception
665 when others then
666 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
667 routineNameIn => 'allowsAllApproverTypes',
668 exceptionNumberIn => sqlcode,
669 exceptionStringIn => sqlerrm);
670 raise;
671 return(null);
672 end allowsAllApproverTypes;
673 function isASubordinate(approverIn in ame_util.approverRecord2,
674 possibleSubordApproverIn in ame_util.approverRecord2) return boolean is
675 errorCode integer;
676 errorMessage ame_util.longestStringType;
677 noSurrogateException exception;
678 origSystemId integer;
679 positionStructureId integer;
680 superiorId integer;
681 superiorFound boolean;
682 begin
683 if(approverIn.orig_system <> possibleSubordApproverIn.orig_system) then
684 return false;
685 end if;
686 if(approverIn.orig_system = ame_util.perOrigSystem) then
687 superiorFound := false;
688 origSystemId := possibleSubordApproverIn.orig_system_id;
689 loop
690 select supervisor_id
691 into superiorId
692 from per_all_assignments_f
693 where
694 per_all_assignments_f.person_id = origSystemId and
695 per_all_assignments_f.primary_flag = 'Y' and
696 per_all_assignments_f.assignment_type in ('E','C') and
697 per_all_assignments_f.assignment_status_type_id not in
698 (select assignment_status_type_id
699 from per_assignment_status_types
700 where per_system_status = 'TERM_ASSIGN') and
701 trunc(sysdate) between
702 per_all_assignments_f.effective_start_date and
703 per_all_assignments_f.effective_end_date;
704 if(superiorId is null) then
705 exit;
706 elsif(superiorId = approverIn.orig_system_id) then
707 superiorFound := true;
708 exit;
709 end if;
710 origSystemId := superiorId;
711 end loop;
712 return(superiorFound);
713 elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
714 /* No hierarchy defined here, so always return false. */
715 return(false);
716 elsif(approverIn.orig_system = ame_util.posOrigSystem) then
717 superiorFound := false;
718 origSystemId := possibleSubordApproverIn.orig_system_id;
719 begin
720 positionStructureId :=
721 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.nonDefPosStructureAttr);
722 Exception
723 when others then
724 return(false);
725 end;
726 loop
727 select str.parent_position_id
728 into superiorId
729 from per_pos_structure_elements str,
730 per_pos_structure_versions psv
731 where
732 str.subordinate_position_id = origSystemId and
733 str.pos_structure_version_id = psv.pos_structure_version_id and
734 trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate) and
735 psv.position_structure_id =
736 (select position_structure_id
737 from per_position_structures
738 where
739 ((positionStructureId is not null and
740 position_structure_id = positionStructureId) or
741 (positionStructureId is null and
742 business_group_id = str.business_group_id and
743 primary_position_flag = 'Y')));
744 if(superiorId is null) then
745 exit;
746 elsif(superiorId = approverIn.orig_system_id) then
747 superiorFound := true;
748 exit;
749 end if;
750 origSystemId := superiorId;
751 end loop;
752 return(superiorFound);
753 elsif(approverIn.orig_system = ame_util.fndRespOrigSystem) then
754 /* To be coded later. For now just return false. */
755 return(false);
756 else
757 return(false);
758 end if;
759 exception
760 when no_data_found then
761 return false;
762 when others then
763 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
764 routineNameIn => 'isASubordinate',
765 exceptionNumberIn => sqlcode,
766 exceptionStringIn => sqlerrm);
767 raise;
768 end isASubordinate;
769 function validateApprover(nameIn in varchar2) return boolean as
770 errorCode integer;
771 errorMessage ame_util.longestStringType;
772 rowCount integer;
773 begin
774 select count(*)
775 into rowCount
776 from wf_roles
777 where
778 name = nameIn and
779 status = 'ACTIVE' and
780 (expiration_date is null or
781 sysdate < expiration_date);
782 if(rowCount > 0) then
783 return(true);
784 end if;
785 return(false);
786 exception
787 when no_data_found then
788 errorCode := -20213;
789 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
790 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
791 tokenNameOneIn => 'NAME',
792 tokenValueOneIn => nameIn);
793 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
794 routineNameIn => 'validateApprover',
795 exceptionNumberIn => errorCode,
796 exceptionStringIn => errorMessage);
797 raise_application_error(errorCode,
798 errorMessage);
799 return(false);
800 when others then
801 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
802 routineNameIn => 'validateApprover',
803 exceptionNumberIn => sqlcode,
804 exceptionStringIn => sqlerrm);
805 raise;
806 return(false);
807 end validateApprover;
808 procedure fndUsrApproverQuery(criteria1In in varchar2 default null,
809 criteria2In in varchar2 default null,
810 criteria3In in varchar2 default null,
811 criteria4In in varchar2 default null,
812 criteria5In in varchar2 default null,
813 excludeListCountIn in integer,
814 approverNamesOut out nocopy varchar2,
815 approverDescriptionsOut out nocopy varchar2) as
816 cursor fndUsrCursor(userNameIn in varchar2,
817 emailAddressIn in varchar2,
818 truncatedSysdateIn in date,
819 rowsToExcludeIn in integer) is
820 select
821 /* The compiler forces passing arguments by position in the following function calls. */
822 getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
823 getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
824 from
825 fnd_user,
826 wf_roles
827 where
828 wf_roles.orig_system_id = fnd_user.user_id and
829 wf_roles.orig_system = ame_util.fndUserOrigSystem and
830 wf_roles.status = 'ACTIVE' and
831 wf_roles.name = fnd_user.user_name and
832 (userNameIn is null or
833 upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
834 (emailAddressIn is null or
835 upper(fnd_user.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%') and
836 truncatedSysdateIn between
837 fnd_user.start_date and
838 nvl(fnd_user.end_date, truncatedSysdateIn) and
839 rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
840 order by fnd_user.user_name;
841 /* local variables */
842 approverNames ame_util.longStringList;
843 approverDescriptions ame_util.longStringList;
844 errorCode integer;
845 errorMessage ame_util.longestStringType;
846 truncatedSysdate date;
847 /* procedure body */
848 begin
849 /*
850 Fetch the cursor into approverNames and approverDescriptions. Note that
851 the cursors will only fetch at most 51 + excludeIdListIn.count rows, preventing
852 oversized fetches.
853 */
854 truncatedSysdate := trunc(sysdate);
855 open fndUsrCursor(userNameIn => criteria1In,
856 emailAddressIn => criteria2In,
857 truncatedSysdateIn => truncatedSysdate,
858 rowsToExcludeIn => excludeListCountIn);
859 fetch fndUsrCursor bulk collect
860 into
861 approverNames,
862 approverDescriptions;
863 close fndUsrCursor;
864 /* Check for too many results. */
865 if(approverNames.count - excludeListCountIn > 50) then
866 raise ame_util.tooManyApproversException;
867 approverNamesOut := null;
868 approverDescriptionsOut := null;
869 return;
870 end if;
871 /* Check for zero approvers. */
872 if(approverNames.count = 0) then
873 raise ame_util.zeroApproversException;
874 approverNamesOut := null;
875 approverDescriptionsOut := null;
876 return;
877 end if;
878 /*
879 Return the results. (ame_util.serializeApprovers procedure will raise
880 ame_util.tooManyApproversException if it can't serialize both input lists.)
881 */
882 ame_util.serializeApprovers(approverNamesIn => approverNames,
883 approverDescriptionsIn => approverDescriptions,
884 maxOutputLengthIn => ame_util.longestStringTypeLength,
885 approverNamesOut => approverNamesOut,
886 approverDescriptionsOut => approverDescriptionsOut);
887 exception
888 when ame_util.tooManyApproversException then
889 errorCode := -20001;
890 errorMessage :=
891 ame_util.getMessage(applicationShortNameIn => 'PER',
892 messageNameIn => 'AME_400111_UIN_MANY_ROWS');
893 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
894 routineNameIn => 'fndUsrApproverQuery',
895 exceptionNumberIn => errorCode,
896 exceptionStringIn => errorMessage);
897 approverNamesOut := null;
898 approverDescriptionsOut := null;
899 raise_application_error(errorCode,
900 errorMessage);
901 when ame_util.zeroApproversException then
902 errorCode := -20001;
903 errorMessage :=
904 ame_util.getMessage(applicationShortNameIn => 'PER',
905 messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
906 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
907 routineNameIn => 'fndUsrApproverQuery',
908 exceptionNumberIn => errorCode,
909 exceptionStringIn => errorMessage);
910 approverNamesOut := null;
911 approverDescriptionsOut := null;
912 raise_application_error(errorCode,
913 errorMessage);
914 when others then
915 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
916 routineNameIn => 'fndUsrApproverQuery',
917 exceptionNumberIn => sqlcode,
918 exceptionStringIn => sqlerrm);
919 approverNamesOut := null;
920 approverDescriptionsOut := null;
921 raise;
922 end fndUsrApproverQuery;
923 procedure fndRespApproverQuery(criteria1In in varchar2 default null,
924 criteria2In in varchar2 default null,
925 criteria3In in varchar2 default null,
926 criteria4In in varchar2 default null,
927 criteria5In in varchar2 default null,
928 excludeListCountIn in integer,
929 approverNamesOut out nocopy varchar2,
930 approverDescriptionsOut out nocopy varchar2) as
931 cursor respCursor(applicationNameIn in varchar2,
932 responsibilityNameIn in varchar2,
933 truncatedSysdateIn in date,
934 rowsToExcludeIn in integer) is
935 select
936 getWfRolesName(ame_util.fndRespOrigSystem||resp.application_id, resp.responsibility_id) approver_name,
937 getApproverDescription(getWfRolesName(ame_util.fndRespOrigSystem || resp.application_id,
938 resp.responsibility_id)) approver_description
939 from
940 fnd_application_vl apps,
941 fnd_responsibility_vl resp
942 where
943 (applicationNameIn is null or
944 upper(apps.application_name) like upper(replace(applicationNameIn, '''', '''''')) || '%') and
945 (responsibilityNameIn is null or
946 upper(resp.responsibility_name) like upper(replace(responsibilityNameIn, '''', '''''')) || '%') and
947 resp.application_id = apps.application_id and
948 truncatedSysdateIn between
949 resp.start_date and
950 nvl(resp.end_date,truncatedSysdateIn) and
951 rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
952 order by apps.application_name;
953 /* local variables */
954 approverNames ame_util.longStringList;
955 approverDescriptions ame_util.longStringList;
956 errorCode integer;
957 errorMessage ame_util.longestStringType;
958 truncatedSysdate date;
959 /* procedure body */
960 begin
961 /*
962 Fetch the cursor into approverNames and approverDescriptions. Note that
963 the cursors will only fetch at most 51 + excludeIdListIn.count rows, preventing
964 oversized fetches.
965 */
966 truncatedSysdate := trunc(sysdate);
967 open respCursor(applicationNameIn => criteria1In,
968 responsibilityNameIn => criteria2In,
969 truncatedSysdateIn => truncatedSysdate,
970 rowsToExcludeIn => excludeListCountIn);
971 fetch respCursor bulk collect
972 into
973 approverNames,
974 approverDescriptions;
975 close respCursor;
976 /* Check for too many results. */
977 if(approverNames.count - excludeListCountIn > 50) then
978 raise ame_util.tooManyApproversException;
979 approverNamesOut := null;
980 approverDescriptionsOut := null;
981 return;
982 end if;
983 /* Check for zero approvers. */
984 if(approverNames.count = 0) then
985 raise ame_util.zeroApproversException;
986 approverNamesOut := null;
987 approverDescriptionsOut := null;
988 return;
989 end if;
990 /*
991 Return the results. (ame_util.serializeApprovers procedure will raise
992 ame_util.tooManyApproversException if it can't serialize both input lists.)
993 */
994 ame_util.serializeApprovers(approverNamesIn => approverNames,
995 approverDescriptionsIn => approverDescriptions,
996 maxOutputLengthIn => ame_util.longestStringTypeLength,
997 approverNamesOut => approverNamesOut,
998 approverDescriptionsOut => approverDescriptionsOut);
999 exception
1000 when ame_util.tooManyApproversException then
1001 errorCode := -20001;
1002 errorMessage :=
1003 ame_util.getMessage(applicationShortNameIn => 'PER',
1004 messageNameIn => 'AME_400111_UIN_MANY_ROWS');
1005 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1006 routineNameIn => 'fndRespApproverQuery',
1007 exceptionNumberIn => errorCode,
1008 exceptionStringIn => errorMessage);
1009 approverNamesOut := null;
1010 approverDescriptionsOut := null;
1011 raise_application_error(errorCode,
1012 errorMessage);
1013 when ame_util.zeroApproversException then
1014 errorCode := -20001;
1015 errorMessage :=
1016 ame_util.getMessage(applicationShortNameIn => 'PER',
1017 messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
1018 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1019 routineNameIn => 'fndRespApproverQuery',
1020 exceptionNumberIn => errorCode,
1021 exceptionStringIn => errorMessage);
1022 approverNamesOut := null;
1023 approverDescriptionsOut := null;
1024 raise_application_error(errorCode,
1025 errorMessage);
1026 when others then
1027 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1028 routineNameIn => 'fndRespApproverQuery',
1029 exceptionNumberIn => sqlcode,
1030 exceptionStringIn => sqlerrm);
1031 approverNamesOut := null;
1032 approverDescriptionsOut := null;
1033 raise;
1034 end fndRespApproverQuery;
1035 procedure getApproverDescAndValidity(nameIn in varchar2,
1036 descriptionOut out nocopy varchar2,
1037 validityOut out nocopy boolean) as
1038 origSystem wf_roles.orig_system%type;
1039 begin
1040 /*
1041 This function needs to return the same data for all approver types. Do NOT modify this
1042 function to return extra data for specific approver types. This function should return
1043 a string that will fit in an ame_util.longStringType.
1044 */
1045 validityOut := false;
1046 begin
1047 select
1048 display_name,
1049 orig_system
1050 into
1051 descriptionOut,
1052 origSystem
1053 from wf_roles
1054 where
1055 name = nameIn and
1056 status = 'ACTIVE' and
1057 (expiration_date is null or
1058 sysdate < expiration_date) and
1059 rownum < 2;
1060 validityOut := true;
1061 exception
1062 when no_data_found then
1063 begin
1064 /*
1065 When the approver is not valid in WF_ROLES, try to get the approver info
1066 from WF_LOCAL_ROLES. (It may still be there, even though the approver is
1067 invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
1068 See bug 3286313.
1069 */
1070 select
1071 display_name,
1072 orig_system
1073 into
1074 descriptionOut,
1075 origSystem
1076 from wf_local_roles
1077 where
1078 name = nameIn and
1079 rownum < 2;
1080 exception
1081 when no_data_found then
1082 descriptionOut := nameIn;
1083 end;
1084 end;
1085 /*
1086 The following if statement reflects a kludge in the originating-system display-name
1087 data in fnd_lookups for FND responsibilities. The kludge is permanent, so we have
1088 to accommodate it here.
1089 */
1090 if origSystem is not null then
1091 if(origSystem like ame_util.fndRespOrigSystem || '%') then
1092 origSystem := ame_util.fndRespOrigSystem;
1093 end if;
1094 descriptionOut :=
1095 ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem)
1096 ||': '
1097 ||descriptionOut;
1098 end if;
1099 if(not validityOut) then
1100 descriptionOut := ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
1101 attributeCodeIn => 'AME_INVALID_COLON') || descriptionOut;
1102 end if;
1103 exception
1104 when others then
1105 descriptionOut := ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
1106 attributeCodeIn => 'AME_INVALID_COLON') || nameIn;
1107 validityOut := false;
1108 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1109 routineNameIn => 'getApproverDescAndValidity',
1110 exceptionNumberIn => sqlcode,
1111 exceptionStringIn => sqlerrm);
1112 raise;
1113 end getApproverDescAndValidity;
1114 procedure getApproverOrigSystemAndId(nameIn in varchar2,
1115 origSystemOut out nocopy varchar2,
1116 origSystemIdOut out nocopy integer) as
1117 errorCode integer;
1118 errorMessage ame_util.longestStringType;
1119 begin
1120 select
1121 orig_system,
1122 orig_system_id
1123 into
1124 origSystemOut,
1125 origSystemIdOut
1126 from wf_roles
1127 where
1128 name = nameIn and
1129 status = 'ACTIVE' and
1130 (expiration_date is null or
1131 sysdate < expiration_date) and
1132 rownum < 2;
1133 exception
1134 when no_data_found then
1135 origSystemOut := null;
1136 origSystemIdOut := null;
1137 errorCode := -20213;
1138 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1139 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1140 tokenNameOneIn => 'NAME',
1141 tokenValueOneIn => nameIn);
1142 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1143 routineNameIn => 'getApproverOrigSystemAndId',
1144 exceptionNumberIn => errorCode,
1145 exceptionStringIn => errorMessage);
1146 raise_application_error(errorCode,
1147 errorMessage);
1148 when others then
1149 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1150 routineNameIn => 'getApproverOrigSystemAndId',
1151 exceptionNumberIn => sqlcode,
1152 exceptionStringIn => sqlerrm);
1153 origSystemOut := null;
1154 origSystemIdOut := null;
1155 raise;
1156 end getApproverOrigSystemAndId;
1157 procedure getApprovalTypes(approverTypeIdIn in integer,
1158 actionTypeNamesOut out nocopy ame_util.stringList) as
1159 cursor getApprovalTypeCursor is
1160 select name
1161 from
1162 ame_action_types,
1163 ame_approver_type_usages
1164 where
1165 ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
1166 approver_type_id = approverTypeIdIn and
1167 sysdate between ame_action_types.start_date and
1168 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1169 sysdate between ame_approver_type_usages.start_date and
1170 nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1171 cursor getApprovalTypeCursor2 is
1172 select name
1173 from
1174 ame_action_types,
1175 ame_approver_type_usages
1176 where
1177 ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
1178 approver_type_id = ame_util.anyApproverType and
1179 sysdate between ame_action_types.start_date and
1180 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1181 sysdate between ame_approver_type_usages.start_date and
1182 nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1183 tempIndex integer;
1184 begin
1185 tempIndex := 1;
1186 for getApprovalTypeRec in getApprovalTypeCursor loop
1187 actionTypeNamesOut(tempIndex) := getApprovalTypeRec.name;
1188 tempIndex := tempIndex + 1;
1189 end loop;
1190 for getApprovalTypeRec in getApprovalTypeCursor2 loop
1191 actionTypeNamesOut(tempIndex) := getApprovalTypeRec.name;
1192 tempIndex := tempIndex + 1;
1193 end loop;
1194 exception
1195 when others then
1196 rollback;
1197 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1198 routineNameIn => 'getApprovalTypes',
1199 exceptionNumberIn => sqlcode,
1200 exceptionStringIn => sqlerrm);
1201 actionTypeNamesOut := ame_util.emptyStringList;
1202 raise;
1203 end getApprovalTypes;
1204 procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1205 queryVariableLabelsOut out nocopy ame_util.longStringList,
1206 variableLovQueriesOut out nocopy ame_util.longStringList) as
1207 queryVariableLabels ame_util.longStringList;
1208 variableLovQueries ame_util.longStringList;
1209 begin
1210 /* select queryVariableLabels and variableLovQueries into plsql tables */
1211 select
1212 query_variable_1_label,
1213 query_variable_2_label,
1214 query_variable_3_label,
1215 query_variable_4_label,
1216 query_variable_5_label,
1217 variable_1_lov_query,
1218 variable_2_lov_query,
1219 variable_3_lov_query,
1220 variable_4_lov_query,
1221 variable_5_lov_query
1222 into
1223 queryVariableLabels(1),
1224 queryVariableLabels(2),
1225 queryVariableLabels(3),
1226 queryVariableLabels(4),
1227 queryVariableLabels(5),
1228 variableLovQueries(1),
1229 variableLovQueries(2),
1230 variableLovQueries(3),
1231 variableLovQueries(4),
1232 variableLovQueries(5)
1233 from ame_approver_types
1234 where
1235 approver_type_id = approverTypeIdIn and
1236 sysdate between
1237 start_date and
1238 nvl(end_date - ame_util.oneSecond, sysdate);
1239 /* loop through the label, assigning the output arguments: */
1240 for i in 1 .. queryVariableLabels.count loop
1241 if(queryVariableLabels(i) is null) then
1242 exit;
1243 end if;
1244 queryVariableLabelsOut(i) := queryVariableLabels(i);
1245 variableLovQueriesOut(i) := variableLovQueries(i);
1246 end loop;
1247 exception
1248 when others then
1249 rollback;
1250 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1251 routineNameIn => 'getApproverTypeQueryData',
1252 exceptionNumberIn => sqlcode,
1253 exceptionStringIn => sqlerrm);
1254 queryVariableLabelsOut := ame_util.emptyLongStringList;
1255 variableLovQueriesOut := ame_util.emptyLongStringList;
1256 raise;
1257 end getApproverTypeQueryData;
1258 procedure getAvailableApproverTypes(applicationIdIn in integer default null,
1259 topLabelIn in varchar2 default null,
1260 topValueIn in varchar2 default null,
1261 approverTypeIdsOut out nocopy ame_util.stringList,
1262 approverTypeNamesOut out nocopy ame_util.stringList) as
1263 cursor getApproverTypeDataCursor is
1264 select
1265 approver_type_id,
1266 ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1267 from ame_approver_types
1268 where sysdate between
1269 start_date and
1270 nvl(end_date - ame_util.oneSecond, sysdate);
1271 cursor getApproverTypeDataCursor2 is
1272 select
1273 approver_type_id,
1274 ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1275 from ame_approver_types
1276 where
1277 orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
1278 sysdate between
1279 start_date and
1280 nvl(end_date - ame_util.oneSecond, sysdate);
1281 tempIndex integer;
1282 configVarValue ame_util.stringType;
1283 begin
1284 /* check configuration variable value for allowAllApproverTypes */
1285 configVarValue := ame_util.getConfigVar(applicationIdIn => applicationIdIn,
1286 variableNameIn => ame_util.allowAllApproverTypesConfigVar);
1287 tempIndex := 1;
1288 if(configVarValue = ame_util.yes) then
1289 /* loop through getApproverTypeDataCursor assigning the output arguments */
1290 for getApproverTypeDataRec in getApproverTypeDataCursor loop
1291 if(tempIndex = 1 and topLabelIn is not null) then
1292 approverTypeIdsOut(1) := topValueIn;
1293 approverTypeNamesOut(1) := topLabelIn;
1294 tempIndex := tempIndex + 1;
1295 end if;
1296 approverTypeIdsOut(tempIndex) := to_char(getApproverTypeDataRec.approver_type_id);
1297 approverTypeNamesOut(tempIndex) := getApprovertypeDataRec.approver_type_name;
1298 tempIndex := tempIndex + 1;
1299 end loop;
1300 else
1301 for getApproverTypeDataRec in getApproverTypeDataCursor2 loop
1302 if(tempIndex = 1 and topLabelIn is not null) then
1303 approverTypeIdsOut(1) := topValueIn;
1304 approverTypeNamesOut(1) := topLabelIn;
1305 tempIndex := tempIndex + 1;
1306 end if;
1307 approverTypeIdsOut(tempIndex) := to_char(getApproverTypeDataRec.approver_type_id);
1308 approverTypeNamesOut(tempIndex) := getApprovertypeDataRec.approver_type_name;
1309 tempIndex := tempIndex + 1;
1310 end loop;
1311 end if;
1312 exception
1313 when others then
1314 rollback;
1315 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1316 routineNameIn => 'getAvailableApproverTypes',
1317 exceptionNumberIn => sqlcode,
1318 exceptionStringIn => sqlerrm);
1319 approverTypeIdsOut := ame_util.emptyStringList;
1320 approverTypeNamesOut := ame_util.emptyStringList;
1321 raise;
1322 end getAvailableApproverTypes;
1323 procedure getAvailableApproverTypes2(actionTypeIdIn in integer,
1324 approverTypeIdsOut out nocopy ame_util.stringList,
1325 approverTypeNamesOut out nocopy ame_util.stringList) as
1326 cursor unusedApproverTypeCursor is
1327 select
1328 approver_type_id,
1329 ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1330 from ame_approver_types
1331 where sysdate between
1332 start_date and
1333 nvl(end_date - ame_util.oneSecond, sysdate)
1334 minus
1335 select
1336 ame_approver_types.approver_type_id,
1337 ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1338 from ame_approver_types,
1339 ame_approver_type_usages
1340 where
1341 ame_approver_types.approver_type_id = ame_approver_type_usages.approver_type_id and
1342 ame_approver_type_usages.action_type_id = actionTypeIdIn and
1343 sysdate between
1344 ame_approver_types.start_date and
1345 nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
1346 sysdate between
1347 ame_approver_type_usages.start_date and
1348 nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
1349 order by approver_type_name;
1350 tempIndex integer;
1351 begin
1352 approverTypeIdsOut(1) := ame_util.anyApproverType;
1353 approverTypeNamesOut(1) := ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_TYPE');
1354 tempIndex := 2;
1355 for unusedApproverTypeRec in unusedApproverTypeCursor loop
1356 /* The explicit conversion below lets nocopy work. */
1357 approverTypeIdsOut(tempIndex) := to_char(unusedApproverTypeRec.approver_type_id);
1358 approverTypeNamesOut(tempIndex) := unusedApproverTypeRec.approver_type_name;
1359 tempIndex := tempIndex + 1;
1360 end loop;
1361 exception
1362 when others then
1363 rollback;
1364 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1365 routineNameIn => 'getAvailableApproverTypes2',
1366 exceptionNumberIn => sqlcode,
1367 exceptionStringIn => sqlerrm);
1368 approverTypeIdsOut := ame_util.emptyStringList;
1369 approverTypeNamesOut := ame_util.emptyStringList;
1370 raise;
1371 end getAvailableApproverTypes2;
1372 procedure getAvailableApproverTypes3(actionTypeIdIn in integer,
1373 approverTypeIdsOut out nocopy ame_util.idList) as
1374 cursor availableApproverTypesCursor(actionTypeIdIn in integer) is
1375 select approver_type_id
1376 from ame_approver_type_usages
1377 where
1378 action_type_id = actionTypeIdIn and
1379 approver_type_id <> ame_util.anyApproverType and
1380 sysdate between
1381 start_date and
1382 nvl(end_date - ame_util.oneSecond, sysdate);
1383 tempIndex integer;
1384 begin
1385 tempIndex := 1;
1386 for availableApproverTypesRec in availableApproverTypesCursor(actionTypeIdIn => actionTypeIdIn) loop
1387 /* The explicit conversion below lets nocopy work. */
1388 approverTypeIdsOut(tempIndex) := availableApproverTypesRec.approver_type_id;
1389 tempIndex := tempIndex + 1;
1390 end loop;
1391 exception
1392 when others then
1393 rollback;
1394 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1395 routineNameIn => 'getAvailableApproverTypes3',
1396 exceptionNumberIn => sqlcode,
1397 exceptionStringIn => sqlerrm);
1398 approverTypeIdsOut := ame_util.emptyIdList;
1399 raise;
1400 end getAvailableApproverTypes3;
1401 procedure getOrigSystemIdAndDisplayName(nameIn in varchar2,
1402 origSystemOut out nocopy varchar2,
1403 origSystemIdOut out nocopy integer,
1404 displayNameOut out nocopy varchar2) as
1405 errorCode integer;
1406 errorMessage ame_util.longestStringType;
1407 begin
1408 select
1409 orig_system,
1410 orig_system_id,
1411 display_name
1412 into
1413 origSystemOut,
1414 origSystemIdOut,
1415 displayNameOut
1416 from wf_roles
1417 where
1418 name = nameIn and
1419 status = 'ACTIVE' and
1420 (expiration_date is null or
1421 sysdate < expiration_date) and
1422 rownum < 2;
1423 exception
1424 when no_data_found then
1425 origSystemOut := null;
1426 origSystemIdOut := null;
1427 displayNameOut := null;
1428 errorCode := -20213;
1429 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1430 messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1431 tokenNameOneIn => 'NAME',
1432 tokenValueOneIn => nameIn);
1433 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1434 routineNameIn => 'getOrigSystemIdAndDisplayName',
1435 exceptionNumberIn => errorCode,
1436 exceptionStringIn => errorMessage);
1437 raise_application_error(errorCode,
1438 errorMessage);
1439 when others then
1440 origSystemOut := null;
1441 origSystemIdOut := null;
1442 displayNameOut := null;
1443 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1444 routineNameIn => 'getOrigSystemIdAndDisplayName',
1445 exceptionNumberIn => sqlcode,
1446 exceptionStringIn => sqlerrm);
1447 raise;
1448 end getOrigSystemIdAndDisplayName;
1449 /*
1450 ame_api2 calls getSuperior. See ER 3267685 for a discussion of how
1451 this procedure will likely be revised after the 11.5.10 release.
1452 */
1453 procedure getSuperior(approverIn in ame_util.approverRecord2,
1454 superiorOut out nocopy ame_util.approverRecord2) is
1455 approverName wf_roles.display_name%type;
1456 errorCode integer;
1457 errorMessage ame_util.longestStringType;
1458 positionStructureId integer;
1459 noSurrogateException exception;
1460 begin
1461 begin
1462 if(approverIn.orig_system = ame_util.perOrigSystem) then
1463 superiorOut.orig_system := ame_util.perOrigSystem;
1464 select
1465 per_all_assignments_f.supervisor_id,
1466 wf_roles.name,
1467 wf_roles.display_name
1468 into
1469 superiorOut.orig_system_id,
1470 superiorOut.name,
1471 superiorOut.display_name
1472 from
1473 wf_roles,
1474 per_all_assignments_f
1475 where
1476 wf_roles.orig_system = ame_util.perOrigSystem and
1477 wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1478 wf_roles.status = 'ACTIVE' and
1479 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480 exists (select null
1481 from fnd_user u
1482 where u.user_name = wf_roles.name
1483 and trunc(sysdate) between u.start_date
1484 and nvl(u.end_date,trunc(sysdate))) and
1485 not exists (
1486 select null from wf_roles wf2
1487 where wf_roles.orig_system = wf2.orig_system
1488 and wf_roles.orig_system_id = wf2.orig_system_id
1489 and wf_roles.start_date > wf2.start_date
1490 ) and
1491 per_all_assignments_f.person_id =approverIn.orig_system_id and
1492 per_all_assignments_f.primary_flag = 'Y' and
1493 per_all_assignments_f.assignment_type in ('E','C') and
1494 per_all_assignments_f.assignment_status_type_id not in
1495 (select assignment_status_type_id
1496 from per_assignment_status_types
1497 where per_system_status = 'TERM_ASSIGN') and
1498 trunc(sysdate) between
1499 per_all_assignments_f.effective_start_date and
1500 per_all_assignments_f.effective_end_date and
1501 rownum < 2;
1502 if(superiorOut.orig_system_id is null) then
1503 raise noSurrogateException;
1504 end if;
1505 elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
1506 superiorOut.orig_system := ame_util.fndUserOrigSystem;
1507 select
1508 per_all_assignments_f.supervisor_id,
1509 wf_roles.name,
1510 wf_roles.display_name
1511 into
1512 superiorOut.orig_system_id,
1513 superiorOut.name,
1514 superiorOut.display_name
1515 from
1516 wf_roles,
1517 per_all_assignments_f
1518 where
1519 wf_roles.orig_system = ame_util.perOrigSystem and
1520 wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1521 wf_roles.status = 'ACTIVE' and
1522 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523 exists (select null
1524 from fnd_user u
1525 where u.user_name = wf_roles.name
1526 and trunc(sysdate) between u.start_date
1527 and nvl(u.end_date,trunc(sysdate))) and
1528 per_all_assignments_f.person_id =
1529 (select employee_id
1530 from fnd_user
1531 where
1532 user_id = approverIn.orig_system_id and
1533 rownum < 2) and
1534 per_all_assignments_f.primary_flag = 'Y' and
1535 per_all_assignments_f.assignment_type in ('E','C') and
1536 per_all_assignments_f.assignment_status_type_id not in
1537 (select assignment_status_type_id
1538 from per_assignment_status_types
1539 where per_system_status = 'TERM_ASSIGN') and
1540 trunc(sysdate) between
1541 per_all_assignments_f.effective_start_date and
1542 per_all_assignments_f.effective_end_date and
1543 rownum < 2
1544 order by wf_roles.name; /* Select the first matching wf_roles entry. */
1545 if(superiorOut.orig_system_id is null) then
1546 raise noSurrogateException;
1547 end if;
1548 elsif(approverIn.orig_system = ame_util.posOrigSystem) then
1549 superiorOut.orig_system := ame_util.posOrigSystem;
1550 positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1551 if (positionStructureId is null) then
1552 select
1553 str.parent_position_id,
1554 wf_roles.name,
1555 wf_roles.display_name
1556 into
1557 superiorOut.orig_system_id,
1558 superiorOut.name,
1559 superiorOut.display_name
1560 from
1561 per_pos_structure_elements str,
1562 per_pos_structure_versions psv,
1563 per_position_structures pst,
1564 wf_roles
1565 where
1566 str.subordinate_position_id = approverIn.orig_system_id and
1567 str.business_group_id =
1568 nvl(hr_general.get_business_group_id,str.business_group_id) and
1569 str.pos_structure_version_id = psv.pos_structure_version_id and
1570 pst.position_structure_id = psv.position_structure_id and
1571 pst.primary_position_flag = 'Y' and
1572 wf_roles.orig_system = ame_util.posOrigSystem and
1573 wf_roles.orig_system_id = str.parent_position_id and
1574 wf_roles.status = 'ACTIVE' and
1575 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576 trunc(sysdate) between
1577 psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1578 rownum < 2
1579 order by wf_roles.name;
1580 else
1581 select
1582 str.parent_position_id,
1583 wf_roles.name,
1584 wf_roles.display_name
1585 into
1586 superiorOut.orig_system_id,
1587 superiorOut.name,
1588 superiorOut.display_name
1589 from
1590 per_pos_structure_elements str,
1591 per_pos_structure_versions psv,
1592 per_position_structures pst,
1593 wf_roles
1594 where
1595 str.subordinate_position_id = approverIn.orig_system_id and
1596 str.pos_structure_version_id = psv.pos_structure_version_id and
1597 pst.position_structure_id = positionStructureId and
1598 pst.position_structure_id = psv.position_structure_id and
1599 wf_roles.orig_system = ame_util.posOrigSystem and
1600 wf_roles.orig_system_id = str.parent_position_id and
1601 wf_roles.status = 'ACTIVE' and
1602 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603 trunc(sysdate) between
1604 psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1605 rownum < 2
1606 order by wf_roles.name;
1607 end if;
1608 elsif(approverIn.orig_system = ame_util.fndRespOrigSystem) then
1609 null;
1610 else
1611 raise noSurrogateException;
1612 end if;
1613 exception
1614 when no_data_found then
1615 raise noSurrogateException;
1616 when others then
1617 raise;
1618 end;
1619 exception
1620 when noSurrogateException then
1621 approverName := getApproverDisplayName2(origSystemIn => approverIn.orig_system,
1622 origSystemIdIn => approverIn.orig_system_id);
1623 errorCode := -20212;
1624 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1625 messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1626 tokenNameOneIn => 'NAME',
1627 tokenValueOneIn => approverName);
1628 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1629 routineNameIn => 'getSuperior',
1630 exceptionNumberIn => errorCode,
1631 exceptionStringIn => errorMessage);
1632 raise_application_error(errorCode,
1633 errorMessage);
1634 when others then
1635 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1636 routineNameIn => 'getSuperior',
1637 exceptionNumberIn => sqlcode,
1638 exceptionStringIn => sqlerrm);
1639 raise;
1640 end getSuperior;
1641 /*
1642 The engine calls getSurrogate, so it needs to be as efficient as possible.
1643 See ER 3267685 for a discussion of how this procedure will likely be revised
1644 after the 11.5.10 release.
1645 */
1646 procedure getSurrogate(origSystemIn in varchar2,
1647 origSystemIdIn in integer,
1648 origSystemIdOut out nocopy integer,
1649 wfRolesNameOut out nocopy varchar2,
1650 displayNameOut out nocopy varchar2) as
1651 approverName wf_roles.display_name%type;
1652 errorCode integer;
1653 errorMessage ame_util.longestStringType;
1654 positionStructureId integer;
1655 noSurrogateException exception;
1656 begin
1657 begin
1658 if(origSystemIn = ame_util.perOrigSystem) then
1659 select
1660 per_all_assignments_f.supervisor_id,
1661 wf_roles.name,
1662 wf_roles.display_name
1663 into
1664 origSystemIdOut,
1665 wfRolesNameOut,
1666 displayNameOut
1667 from
1668 wf_roles,
1669 per_all_assignments_f
1670 where
1671 wf_roles.orig_system = ame_util.perOrigSystem and
1672 wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1673 wf_roles.status = 'ACTIVE' and
1674 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675 exists (select null
1676 from fnd_user u
1677 where u.user_name = wf_roles.name
1678 and trunc(sysdate) between u.start_date
1679 and nvl(u.end_date,trunc(sysdate))) and
1680 not exists (
1681 select null from wf_roles wf2
1682 where wf_roles.orig_system = wf2.orig_system
1683 and wf_roles.orig_system_id = wf2.orig_system_id
1684 and wf_roles.start_date > wf2.start_date
1685 ) and
1686 per_all_assignments_f.person_id = origSystemIdIn and
1687 per_all_assignments_f.primary_flag = 'Y' and
1688 per_all_assignments_f.assignment_type in ('E','C') and
1689 per_all_assignments_f.assignment_status_type_id not in
1690 (select assignment_status_type_id
1691 from per_assignment_status_types
1692 where per_system_status = 'TERM_ASSIGN') and
1693 trunc(sysdate) between
1694 per_all_assignments_f.effective_start_date and
1695 per_all_assignments_f.effective_end_date and
1696 rownum < 2;
1697 if(origSystemIdOut is null) then
1698 raise noSurrogateException;
1699 end if;
1700 elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1701 select
1702 per_all_assignments_f.supervisor_id,
1703 wf_roles.name,
1704 wf_roles.display_name
1705 into
1706 origSystemIdOut,
1707 wfRolesNameOut,
1708 displayNameOut
1709 from
1710 wf_roles,
1711 per_all_assignments_f
1712 where
1713 wf_roles.orig_system = ame_util.perOrigSystem and
1714 wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1715 wf_roles.status = 'ACTIVE' and
1716 exists (select null
1717 from fnd_user u
1718 where u.user_name = wf_roles.name
1719 and trunc(sysdate) between u.start_date
1720 and nvl(u.end_date,trunc(sysdate))) and
1721 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1722 per_all_assignments_f.person_id =
1723 (select employee_id
1724 from fnd_user
1725 where
1726 user_id = origSystemIdIn and
1727 rownum < 2) and
1728 per_all_assignments_f.primary_flag = 'Y' and
1729 per_all_assignments_f.assignment_type in ('E','C') and
1730 per_all_assignments_f.assignment_status_type_id not in
1731 (select assignment_status_type_id
1732 from per_assignment_status_types
1733 where per_system_status = 'TERM_ASSIGN') and
1734 trunc(sysdate) between
1735 per_all_assignments_f.effective_start_date and
1736 per_all_assignments_f.effective_end_date and
1737 rownum < 2
1738 order by wf_roles.name; /* Select the first matching wf_roles entry. */
1739 if(origSystemIdOut is null) then
1740 raise noSurrogateException;
1741 end if;
1742 elsif(origSystemIn = ame_util.posOrigSystem) then
1743 begin
1744 positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1745 exception
1746 when others then
1747 positionStructureId := null;
1748 end;
1749 if (positionStructureId is null) then
1750 select
1751 str.parent_position_id,
1752 wf_roles.name,
1753 wf_roles.display_name
1754 into
1755 origSystemIdOut,
1756 wfRolesNameOut,
1757 displayNameOut
1758 from
1759 per_pos_structure_elements str,
1760 per_pos_structure_versions psv,
1761 per_position_structures pst,
1762 wf_roles
1763 where
1764 str.subordinate_position_id = origSystemIdIn and
1765 str.business_group_id =
1766 nvl(hr_general.get_business_group_id,str.business_group_id) and
1767 str.pos_structure_version_id = psv.pos_structure_version_id and
1768 pst.position_structure_id = psv.position_structure_id and
1769 pst.primary_position_flag = 'Y' and
1770 wf_roles.orig_system = ame_util.posOrigSystem and
1771 wf_roles.orig_system_id = str.parent_position_id and
1772 wf_roles.status = 'ACTIVE' and
1773 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774 trunc(sysdate) between
1775 psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1776 rownum < 2
1777 order by wf_roles.name;
1778 else
1779 select
1780 str.parent_position_id,
1781 wf_roles.name,
1782 wf_roles.display_name
1783 into
1784 origSystemIdOut,
1785 wfRolesNameOut,
1786 displayNameOut
1787 from
1788 per_pos_structure_elements str,
1789 per_pos_structure_versions psv,
1790 per_position_structures pst,
1791 wf_roles
1792 where
1793 str.subordinate_position_id = origSystemIdIn and
1794 str.pos_structure_version_id = psv.pos_structure_version_id and
1795 pst.position_structure_id = positionStructureId and
1796 pst.position_structure_id = psv.position_structure_id and
1797 wf_roles.orig_system = ame_util.posOrigSystem and
1798 wf_roles.orig_system_id = str.parent_position_id and
1799 wf_roles.status = 'ACTIVE' and
1800 (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801 trunc(sysdate) between
1802 psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1803 rownum < 2
1804 order by wf_roles.name;
1805 end if;
1806 elsif(origSystemIn = ame_util.fndRespOrigSystem) then
1807 null;
1808 else
1809 raise noSurrogateException;
1810 end if;
1811 exception
1812 when no_data_found then
1813 raise noSurrogateException;
1814 when others then
1815 raise;
1816 end;
1817 exception
1818 when noSurrogateException then
1819 approverName := getApproverDisplayName2(origSystemIn => origSystemIn,
1820 origSystemIdIn => origSystemIdIn);
1821 errorCode := -20212;
1822 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1823 messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1824 tokenNameOneIn => 'NAME',
1825 tokenValueOneIn => approverName);
1826 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1827 routineNameIn => 'getSurrogate',
1828 exceptionNumberIn => errorCode,
1829 exceptionStringIn => errorMessage);
1830 raise_application_error(errorCode,
1831 errorMessage);
1832 when others then
1833 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1834 routineNameIn => 'getSurrogate',
1835 exceptionNumberIn => sqlcode,
1836 exceptionStringIn => sqlerrm);
1837 raise;
1838 end getSurrogate;
1839 procedure getWfRolesNameAndDisplayName(origSystemIn in varchar2,
1840 origSystemIdIn in integer,
1841 nameOut out nocopy ame_util.longStringType,
1842 displayNameOut out nocopy ame_util.longStringType) as
1843 errorCode integer;
1844 errorMessage ame_util.longestStringType;
1845 begin
1846 /*
1847 This procedure should select the input approver's wf_roles.display_name, NOT the
1848 display name of the input approver's orig_system.
1849 */
1850 /*
1851 If an fnd_user entry has a non-null employee_id (person ID) value, it gets
1852 converted to the PER originating system in wf_roles; otherwise, it gets
1853 converted to the FND_USR originating system. As just one of these will
1854 happen, we can match both originating systems in a single-row query.
1855 The order-by-name clause and rownum < 2 condition are necessary because we
1856 have encountered data problems where there are several entries for a given
1857 orig_system and orig_system_id pair.
1858 */
1859 select
1860 name,
1861 display_name
1862 into
1863 nameOut,
1864 displayNameOut
1865 from wf_roles
1866 where
1867 ((orig_system = origSystemIn and
1868 orig_system_id = origSystemIdIn) or
1869 (origSystemIn = ame_util.fndUserOrigSystem and
1870 orig_system = ame_util.perOrigSystem and
1871 orig_system_id = (select employee_id
1872 from fnd_user
1873 where
1874 user_id = origSystemIdIn and
1875 sysdate between
1876 start_date and
1877 nvl(end_date, sysdate)))) and
1878 status = 'ACTIVE' and
1879 (expiration_date is null or
1880 sysdate < expiration_date) and
1881 (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
1882 or exists (select null
1883 from fnd_user u
1884 where u.user_name = wf_roles.name
1885 and trunc(sysdate) between u.start_date
1886 and nvl(u.end_date,trunc(sysdate)))) and
1887 not exists (
1888 select null from wf_roles wf2
1889 where wf_roles.orig_system = wf2.orig_system
1890 and wf_roles.orig_system_id = wf2.orig_system_id
1891 and wf_roles.start_date > wf2.start_date
1892 ) and
1893 rownum < 2;
1894 exception
1895 when no_data_found then
1896 errorCode := -20213;
1897 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1898 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
1899 tokenNameOneIn => 'ORIG_SYSTEM_ID',
1900 tokenValueOneIn => origSystemIdIn,
1901 tokenNameTwoIn => 'ORIG_SYSTEM',
1902 tokenValueTwoIn => origSystemIn
1903 );
1904 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1905 routineNameIn => 'getWfRolesNameAndDisplayName',
1906 exceptionNumberIn => errorCode,
1907 exceptionStringIn => errorMessage);
1908 nameOut := null;
1909 displayNameOut := null;
1910 raise_application_error(errorCode,
1911 errorMessage);
1912 when others then
1913 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1914 routineNameIn => 'getWfRolesNameAndDisplayName',
1915 exceptionNumberIn => sqlcode,
1916 exceptionStringIn => sqlerrm);
1917 nameOut := null;
1918 displayNameOut := null;
1919 raise;
1920 end getWfRolesNameAndDisplayName;
1921 procedure newApproverTypeUsage(actionTypeIdIn in integer,
1922 approverTypeIdIn in integer,
1923 processingDateIn in date) as
1924 currentUserId integer;
1925 begin
1926 currentUserId := ame_util.getCurrentUserId;
1927 insert into ame_approver_type_usages(approver_type_id,
1928 action_type_id,
1929 created_by,
1930 creation_date,
1931 last_updated_by,
1932 last_update_date,
1933 last_update_login,
1934 start_date,
1935 end_date)
1936 values(approverTypeIdIn,
1937 actionTypeIdIn,
1938 currentUserId,
1939 processingDateIn,
1940 currentUserId,
1941 processingDateIn,
1942 currentUserId,
1943 processingDateIn,
1944 null);
1945 exception
1946 when others then
1947 rollback;
1948 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1949 routineNameIn => 'newApproverTypeUsage',
1950 exceptionNumberIn => sqlcode,
1951 exceptionStringIn => sqlerrm);
1952 raise;
1953 end newApproverTypeUsage;
1954 procedure newApproverTypeUsages(actionTypeIdIn in integer,
1955 approverTypeIdsIn in ame_util.idList,
1956 finalizeIn in boolean default false,
1957 processingDateIn in date default null) as
1958 processingDate date;
1959 begin
1960 if(processingDateIn is null) then
1961 processingDate := sysdate;
1962 else
1963 processingDate := processingDateIn;
1964 end if;
1965 for i in 1 .. approverTypeIdsIn.count loop
1966 newApproverTypeUsage(actionTypeIdIn => actionTypeIdIn,
1967 approverTypeIdIn => approverTypeIdsIn(i),
1968 processingDateIn => processingDate);
1969 end loop;
1970 if(finalizeIn) then
1971 commit;
1972 end if;
1973 exception
1974 when others then
1975 rollback;
1976 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1977 routineNameIn => 'newApproverTypeUsages',
1978 exceptionNumberIn => sqlcode,
1979 exceptionStringIn => sqlerrm);
1980 raise;
1981 end newApproverTypeUsages;
1982 procedure perApproverQuery(criteria1In in varchar2 default null,
1983 criteria2In in varchar2 default null,
1984 criteria3In in varchar2 default null,
1985 criteria4In in varchar2 default null,
1986 criteria5In in varchar2 default null,
1987 excludeListCountIn in integer,
1988 approverNamesOut out nocopy varchar2,
1989 approverDescriptionsOut out nocopy varchar2) as
1990 cursor personCursor(firstNameIn in varchar2,
1991 lastNameIn in varchar2,
1992 emailAddressIn in varchar2,
1993 truncatedSysdateIn in date,
1994 rowsToExcludeIn in integer) is
1995 select
1996 /* The compiler forces passing arguments by position in the following function calls. */
1997 getWfRolesName(ame_util.perOrigSystem, pap.person_id) approver_name
1998 ,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
1999 from
2000 per_all_people_f pap
2001 ,hr_all_organization_units haou
2002 ,wf_roles wfr
2003 ,per_all_assignments_f pas
2004 where pap.person_id = pas.person_id
2005 and pas.primary_flag = 'Y'
2006 and pas.assignment_type in ('E','C')
2007 and pas.assignment_status_type_id not in
2008 (select assignment_status_type_id
2009 from per_assignment_status_types
2010 where per_system_status = 'TERM_ASSIGN')
2011 and wfr.orig_system_id = pap.person_id
2012 and wfr.orig_system = ame_util.perOrigSystem
2013 and wfr.status = 'ACTIVE'
2014 and exists (select null
2015 from fnd_user u
2016 where u.user_name = wfr.name
2017 and truncatedSysdateIn between u.start_date
2018 and nvl(u.end_date,truncatedSysdateIn))
2019 and (firstNameIn is null or upper(pap.first_name) like upper(replace(firstNameIn, '''', '''''')) || '%')
2020 and (lastNameIn is null or upper(pap.last_name) like upper(replace(lastNameIn, '''', '''''')) || '%')
2021 and (emailAddressIn is null or upper(pap.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%')
2022 and pap.business_group_id = haou.organization_id
2023 and truncatedSysdateIn between pap.effective_start_date and nvl(pap.effective_end_date, truncatedSysdateIn)
2024 and truncatedSysdateIn between haou.date_from and nvl(haou.date_to, truncatedSysdateIn)
2025 and truncatedSysdateIn between pas.effective_start_date and pas.effective_end_date
2026 and rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
2027 order by last_name;
2028 /* local variables */
2029 approverNames ame_util.longStringList;
2030 approverDescriptions ame_util.longStringList;
2031 errorCode integer;
2032 errorMessage ame_util.longestStringType;
2033 truncatedSysdate date;
2034 /* procedure body */
2035 begin
2036 /*
2037 Fetch the cursor into approverNames and approverDescriptions. Note that
2038 the cursors will only fetch at most 51 + excludeIdListIn.count rows, preventing
2039 oversized fetches.
2040 */
2041 truncatedSysdate := trunc(sysdate);
2042 open personCursor(firstNameIn => criteria1In,
2043 lastNameIn => criteria2In,
2044 emailAddressIn => criteria3In,
2045 truncatedSysdateIn => truncatedSysdate,
2046 rowsToExcludeIn => excludeListCountIn);
2047 fetch personCursor bulk collect
2048 into
2049 approverNames,
2050 approverDescriptions;
2051 close personCursor;
2052 /* Check for too many results. */
2053 if(approverNames.count - excludeListCountIn > 50) then
2054 raise ame_util.tooManyApproversException;
2055 approverNamesOut := null;
2056 approverDescriptionsOut := null;
2057 return;
2058 end if;
2059 /* Check for zero approvers. */
2060 if(approverNames.count = 0) then
2061 raise ame_util.zeroApproversException;
2062 approverNamesOut := null;
2063 approverDescriptionsOut := null;
2064 return;
2065 end if;
2066 /*
2067 Return the results. (ame_util.serializeApprovers procedure will raise
2068 ame_util.tooManyApproversException if it can't serialize both input lists.)
2069 */
2070 ame_util.serializeApprovers(approverNamesIn => approverNames,
2071 approverDescriptionsIn => approverDescriptions,
2072 maxOutputLengthIn => ame_util.longestStringTypeLength,
2073 approverNamesOut => approverNamesOut,
2074 approverDescriptionsOut => approverDescriptionsOut);
2075 exception
2076 when ame_util.tooManyApproversException then
2077 errorCode := -20001;
2078 errorMessage :=
2079 ame_util.getMessage(applicationShortNameIn => 'PER',
2080 messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2081 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2082 routineNameIn => 'perApproverQuery',
2083 exceptionNumberIn => errorCode,
2084 exceptionStringIn => errorMessage);
2085 approverNamesOut := null;
2086 approverDescriptionsOut := null;
2087 raise_application_error(errorCode,
2088 errorMessage);
2089 when ame_util.zeroApproversException then
2090 errorCode := -20001;
2091 errorMessage :=
2092 ame_util.getMessage(applicationShortNameIn => 'PER',
2093 messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2094 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2095 routineNameIn => 'perApproverQuery',
2096 exceptionNumberIn => errorCode,
2097 exceptionStringIn => errorMessage);
2098 approverNamesOut := null;
2099 approverDescriptionsOut := null;
2100 raise_application_error(errorCode,
2101 errorMessage);
2102 when others then
2103 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2104 routineNameIn => 'perApproverQuery',
2105 exceptionNumberIn => sqlcode,
2106 exceptionStringIn => sqlerrm);
2107 approverNamesOut := null;
2108 approverDescriptionsOut := null;
2109 raise;
2110 end perApproverQuery;
2111 procedure posApproverQuery(criteria1In in varchar2 default null,
2112 criteria2In in varchar2 default null,
2113 criteria3In in varchar2 default null,
2114 criteria4In in varchar2 default null,
2115 criteria5In in varchar2 default null,
2116 excludeListCountIn in integer,
2117 approverNamesOut out nocopy varchar2,
2118 approverDescriptionsOut out nocopy varchar2) as
2119 cursor positionCursor(positionNameIn in varchar2,
2120 businessGroupNameIn in varchar2,
2121 truncatedSysdateIn in date,
2122 rowsToExcludeIn in integer) is
2123 select
2124 getWfRolesName(ame_util.posOrigSystem, per_positions.position_id) approver_name,
2125 getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
2126 from
2127 per_positions,
2128 hr_organization_units,
2129 wf_roles
2130 where
2131 wf_roles.orig_system_id = per_positions.position_id and
2132 wf_roles.orig_system = ame_util.posOrigSystem and
2133 wf_roles.status = 'ACTIVE' and
2134 (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2135 (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
2136 per_positions.business_group_id = hr_organization_units.organization_id and
2137 truncatedSysdateIn between
2138 per_positions.date_effective and
2139 nvl(per_positions.date_end, truncatedSysdateIn) and
2140 truncatedSysdateIn between
2141 hr_organization_units.date_from and
2142 nvl(hr_organization_units.date_to, truncatedSysdateIn) and
2143 rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
2144 order by per_positions.name;
2145 /* local variables */
2146 approverNames ame_util.longStringList;
2147 approverDescriptions ame_util.longStringList;
2148 errorCode integer;
2149 errorMessage ame_util.longestStringType;
2150 truncatedSysdate date;
2151 /* procedure body */
2152 begin
2153 /*
2154 Fetch the cursor into approverNames and approverDescriptions. Note that
2155 the cursors will only fetch at most 51 + excludeIdListIn.count rows, preventing
2156 oversized fetches.
2157 */
2158 truncatedSysdate := trunc(sysdate);
2159 open positionCursor(positionNameIn => criteria1In,
2160 businessGroupNameIn => criteria2In,
2161 truncatedSysdateIn => truncatedSysdate,
2162 rowsToExcludeIn => excludeListCountIn);
2163 fetch positionCursor bulk collect
2164 into
2165 approverNames,
2166 approverDescriptions;
2167 close positionCursor;
2168 /* Check for too many results. */
2169 if(approverNames.count - excludeListCountIn > 50) then
2170 raise ame_util.tooManyApproversException;
2171 approverNamesOut := null;
2172 approverDescriptionsOut := null;
2173 return;
2174 end if;
2175 /* Check for zero approvers. */
2176 if(approverNames.count = 0) then
2177 raise ame_util.zeroApproversException;
2178 approverNamesOut := null;
2179 approverDescriptionsOut := null;
2180 return;
2181 end if;
2182 /*
2183 Return the results. (ame_util.serializeApprovers procedure will raise
2184 ame_util.tooManyApproversException if it can't serialize both input lists.)
2185 */
2186 ame_util.serializeApprovers(approverNamesIn => approverNames,
2187 approverDescriptionsIn => approverDescriptions,
2188 maxOutputLengthIn => ame_util.longestStringTypeLength,
2189 approverNamesOut => approverNamesOut,
2190 approverDescriptionsOut => approverDescriptionsOut);
2191 exception
2192 when ame_util.tooManyApproversException then
2193 errorCode := -20001;
2194 errorMessage :=
2195 ame_util.getMessage(applicationShortNameIn => 'PER',
2196 messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2197 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2198 routineNameIn => 'posApproverQuery',
2199 exceptionNumberIn => errorCode,
2200 exceptionStringIn => errorMessage);
2201 approverNamesOut := null;
2202 approverDescriptionsOut := null;
2203 raise_application_error(errorCode,
2204 errorMessage);
2205 when ame_util.zeroApproversException then
2206 errorCode := -20001;
2207 errorMessage :=
2208 ame_util.getMessage(applicationShortNameIn => 'PER',
2209 messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2210 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2211 routineNameIn => 'posApproverQuery',
2212 exceptionNumberIn => errorCode,
2213 exceptionStringIn => errorMessage);
2214 approverNamesOut := null;
2215 approverDescriptionsOut := null;
2216 raise_application_error(errorCode,
2217 errorMessage);
2218 when others then
2219 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2220 routineNameIn => 'posApproverQuery',
2221 exceptionNumberIn => sqlcode,
2222 exceptionStringIn => sqlerrm);
2223 approverNamesOut := null;
2224 approverDescriptionsOut := null;
2225 raise;
2226 end posApproverQuery;
2227 procedure processApproverQuery(selectClauseIn in varchar2,
2228 approverNamesOut out nocopy ame_util.longStringList,
2229 approverDisplayNamesOut out nocopy ame_util.longStringList) as
2230 tempApproverDisplayName ame_util.longStringType;
2231 tempApproverName ame_util.longStringType;
2232 tempIndex integer;
2233 variableCur ame_util.queryCursor;
2234 begin
2235 /* call the ame_util.getQuery routine and assign to variableCur */
2236 variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn);
2237 tempIndex := 1;
2238 /* loop through the dynamic cursor fetching into the local variables
2239 variableName and variableDisplayName */
2240 loop
2241 fetch variableCur
2242 into tempApproverName,
2243 tempApproverDisplayName;
2244 exit when variableCur%notfound;
2245 /* assign variableName, variableDisplayName to the output arguments
2246 approverNamesOut, approverDisplayNamesOut */
2247 approverNamesOut(tempIndex) := tempApproverName;
2248 approverDisplayNamesOut(tempIndex) := tempApproverDisplayName;
2249 tempIndex := tempIndex + 1;
2250 end loop;
2251 close variableCur;
2252 exception
2253 when others then
2254 rollback;
2255 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2256 routineNameIn => 'processApproverQuery',
2257 exceptionNumberIn => sqlcode,
2258 exceptionStringIn => sqlerrm);
2259 approverNamesOut := ame_util.emptyLongStringList;
2260 approverDisplayNamesOut := ame_util.emptyLongStringList;
2261 raise;
2262 end processApproverQuery;
2263 procedure processApproverQuery2(selectClauseIn in varchar2,
2264 approverNamesOut out nocopy ame_util.longStringList) as
2265 tempApproverDisplayName ame_util.longStringType;
2266 tempApproverName ame_util.longStringType;
2267 tempIndex integer;
2268 variableCur ame_util.queryCursor;
2269 begin
2270 /* call the ame_util.getQuery routine and assign to variableCur */
2271 variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn); -- may not be right, need to check on this);
2272 tempIndex := 1;
2273 /* loop through the dynamic cursor fetching into the local variables
2274 variableName and variableDisplayName */
2275 loop
2276 fetch variableCur
2277 into tempApproverName;
2278 exit when variableCur%notfound;
2279 /* assign variableName, variableDisplayName to the output arguments
2280 approverNamesOut, approverDisplayNamesOut */
2281 approverNamesOut(tempIndex) := tempApproverName;
2282 tempIndex := tempIndex + 1;
2283 end loop;
2284 close variableCur;
2285 exception
2286 when others then
2287 rollback;
2288 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2289 routineNameIn => 'processApproverQuery2',
2290 exceptionNumberIn => sqlcode,
2291 exceptionStringIn => sqlerrm);
2292 approverNamesOut := ame_util.emptyLongStringList;
2293 raise;
2294 end processApproverQuery2;
2295 procedure removeApproverTypeUsage(actionTypeIdIn in integer,
2296 approverTypeIdIn in integer,
2297 processingDateIn in date default null) as
2298 currentUserId integer;
2299 errorCode integer;
2300 errorMessage ame_util.longestStringType;
2301 inUseException exception;
2302 processingDate date;
2303 begin
2304 currentUserId := ame_util.getCurrentUserId;
2305 update ame_approver_type_usages
2306 set
2307 last_updated_by = currentUserId,
2308 last_update_date = processingDateIn,
2309 last_update_login = currentUserId,
2310 end_date = processingDateIn
2311 where
2312 action_type_id = actionTypeIdIn and
2313 approver_type_id = approverTypeIdIn and
2314 processingDateIn between start_date and
2315 nvl(end_date - ame_util.oneSecond, processingDateIn) ;
2316 exception
2317 when others then
2318 rollback;
2319 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2320 routineNameIn => 'removeApproverTypeUsage',
2321 exceptionNumberIn => sqlcode,
2322 exceptionStringIn => sqlerrm);
2323 raise;
2324 end removeApproverTypeUsage;
2325 procedure removeApproverTypeUsages(actionTypeIdIn in integer,
2326 approverTypeIdsIn in ame_util.idList default ame_util.emptyIdList,
2327 finalizeIn in boolean default false,
2328 processingDateIn in date default null) as
2329 currentUserId integer;
2330 errorCode integer;
2331 errorMessage ame_util.longestStringType;
2332 inUseException exception;
2333 processingDate date;
2334 begin
2335 processingDate := sysdate;
2336 /* loop through approverTypeIdsIn and update/end date
2337 ame_approver_type_usages */
2338 for i in 1..approverTypeIdsIn.count loop
2339 removeApproverTypeUsage(actionTypeIdIn => actionTypeIdIn,
2340 approverTypeIdIn => approverTypeIdsIn(i),
2341 processingDateIn => processingDate);
2342 end loop;
2343 if(finalizeIn) then
2344 commit;
2345 end if;
2346 exception
2347 when others then
2348 rollback;
2349 ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2350 routineNameIn => 'removeApproverTypeUsages',
2351 exceptionNumberIn => sqlcode,
2352 exceptionStringIn => sqlerrm);
2353 raise;
2354 end removeApproverTypeUsages;
2355 end ame_approver_type_pkg;