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