[Home] [Help]
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
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;
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
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)))
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'
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))
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',
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;
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
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',
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,
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',
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;
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
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
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
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
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
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',
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,
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;
262: returns the string 'Invalid:
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
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: /*
284: it returns the string 'Invalid :
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
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',
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
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',
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,
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
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
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
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',
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,
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;
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
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;
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
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;
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
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;
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;
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
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;
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
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
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
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
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',
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;
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
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;
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;
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;
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;
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;
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
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;
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 :=
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;
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);
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;
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
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',
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,
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;
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
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
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
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;
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 */
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: /*
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;
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;
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,
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,
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);
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
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');
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,
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;
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');
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,
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;
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,
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
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;
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 */
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: /*
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;
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;
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,
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,
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);
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
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');
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,
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;
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');
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,
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;
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
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)
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: ||': '
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
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',
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;
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
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',
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,
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,
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
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,
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);
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
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
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;
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,
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
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 */
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
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,
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;
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;
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,
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,
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,
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
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
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;
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 */
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);
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 */
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
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;
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;
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,
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,
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,
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
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
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;
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;
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. */
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);
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;
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,
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
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
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;
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
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;
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,
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,
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',
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,
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;
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;
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;
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
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,
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
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
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,
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
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
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
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,
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,
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
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;
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',
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,
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;
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
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
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
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
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
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
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;
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
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;
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',
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,
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;
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
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: /*
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.
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
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
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
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',
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;
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,
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;
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
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;
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
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
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
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;
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 */
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: /*
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;
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;
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,
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,
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);
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
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');
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,
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;
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');
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,
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;
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,
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
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
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;
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 */
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: /*
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;
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;
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,
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,
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);
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
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');
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,
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;
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');
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,
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;
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;
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
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 */
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;
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 */
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
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;
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,
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
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;
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
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 */
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;
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 */
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
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;
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,
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;
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,
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',
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;
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;
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;
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;