[Home] [Help]
1: package body ame_approver_type_pkg as
2: /* $Header: ameoatyp.pkb 120.3.12000000.2 2007/01/31 18:09:07 prasashe noship $ */
3: function getApproverDescription(nameIn in varchar2) return varchar2 as
4: descriptionOut ame_util.longestStringType;
5: validityOut boolean;
6: begin
7: /*
8: getApproverDescAndValidity checks for invalid approvers and produces
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 := -20001;
68: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
69: messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
70: tokenNameOneIn => 'ORIG_SYSTEM_ID',
71: tokenValueOneIn => origSystemIdIn,
72: tokenNameTwoIn => 'ORIG_SYSTEM',
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 := -20001;
109: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
110: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
111: tokenNameOneIn => 'NAME',
112: tokenValueOneIn => nameIn);
113: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
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 := -20001;
180: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
181: messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
182: tokenNameOneIn => 'ORIG_SYSTEM_ID',
183: tokenValueOneIn => origSystemIdIn,
184: tokenNameTwoIn => 'ORIG_SYSTEM',
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 := -20001;
328: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
329: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
330: tokenNameOneIn => 'NAME',
331: tokenValueOneIn => nameIn);
332: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
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 getApproverOrigSystemId(nameIn in varchar2) return varchar2 as
387: errorCode integer;
388: errorMessage ame_util.longestStringType;
389: origSystemId wf_roles.orig_system_id%type;
390: begin
391: select orig_system_id
392: into origSystemId
401: exception
402: when no_data_found then
403: errorCode := -20001;
404: errorMessage :=
405: ame_util.getMessage(applicationShortNameIn => 'PER',
406: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
407: tokenNameOneIn => 'NAME',
408: tokenValueOneIn => nameIn);
409: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
405: ame_util.getMessage(applicationShortNameIn => 'PER',
406: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
407: tokenNameOneIn => 'NAME',
408: tokenValueOneIn => nameIn);
409: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
410: routineNameIn => 'getApproverOrigSystemId',
411: exceptionNumberIn => errorCode,
412: exceptionStringIn => errorMessage);
413: raise_application_error(errorCode,
413: raise_application_error(errorCode,
414: errorMessage);
415: return(null);
416: when others then
417: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
418: routineNameIn => 'getApproverOrigSystemId',
419: exceptionNumberIn => sqlcode,
420: exceptionStringIn => sqlerrm);
421: raise;
430: where
431: orig_system = origSystemIn and
432: sysdate between
433: start_date and
434: nvl(end_date - ame_util.oneSecond, sysdate) and
435: rownum < 2;
436: return(approverTypeId);
437: exception
438: when others then
435: rownum < 2;
436: return(approverTypeId);
437: exception
438: when others then
439: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
440: routineNameIn => 'getApproverTypeId',
441: exceptionNumberIn => sqlcode,
442: exceptionStringIn => sqlerrm);
443: raise;
452: where
453: approver_type_id = approverTypeIdIn and
454: sysdate between
455: start_date and
456: nvl(end_date - ame_util.oneSecond, sysdate) and
457: rownum < 2;
458: return(origSystem);
459: exception
460: when others then
457: rownum < 2;
458: return(origSystem);
459: exception
460: when others then
461: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
462: routineNameIn => 'getApproverTypeOrigSystem',
463: exceptionNumberIn => sqlcode,
464: exceptionStringIn => sqlerrm);
465: raise;
473: from ame_approver_types
474: where
475: approver_type_id = approverTypeIdIn and
476: sysdate between start_date and
477: nvl(end_date - ame_util.oneSecond, sysdate) and
478: rownum < 2;
479: return(ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem));
480: exception
481: when others then
478: rownum < 2;
479: return(ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem));
480: exception
481: when others then
482: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
483: routineNameIn => 'getApproverTypeDisplayName',
484: exceptionNumberIn => sqlcode,
485: exceptionStringIn => sqlerrm);
486: raise;
492: select meaning
493: into origDisplayName
494: from fnd_lookups
495: where
496: lookup_type = ame_util.origSystemLookupType and
497: lookup_code = origSystemIn and
498: sysdate between
499: start_date_active and
500: nvl(end_date_active, sysdate) and
501: rownum < 2;
502: return(origDisplayName);
503: exception
504: when others then
505: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
506: routineNameIn => 'getOrigSystemDisplayName',
507: exceptionNumberIn => sqlcode,
508: exceptionStringIn => sqlerrm);
509: raise;
518: where
519: approver_type_id = approverTypeIdIn and
520: sysdate between
521: start_date and
522: nvl(end_date - ame_util.oneSecond, sysdate) and
523: rownum < 2;
524: return(queryProcedure);
525: exception
526: when others then
523: rownum < 2;
524: return(queryProcedure);
525: exception
526: when others then
527: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
528: routineNameIn => 'getQueryProcedure',
529: exceptionNumberIn => sqlcode,
530: exceptionStringIn => sqlerrm);
531: raise;
534: function getWfRolesName(origSystemIn in varchar2,
535: origSystemIdIn in integer,
536: raiseNoDataFoundIn in varchar2 default 'true') return varchar2 as
537: errorCode integer;
538: errorMessage ame_util.longestStringType;
539: name wf_roles.name%type;
540: begin
541: /*
542: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
552: from wf_roles
553: where
554: ((orig_system = origSystemIn and
555: orig_system_id = origSystemIdIn) or
556: (origSystemIn = ame_util.fndUserOrigSystem and
557: orig_system = ame_util.perOrigSystem and
558: orig_system_id = (select employee_id
559: from fnd_user
560: where
553: where
554: ((orig_system = origSystemIn and
555: orig_system_id = origSystemIdIn) or
556: (origSystemIn = ame_util.fndUserOrigSystem and
557: orig_system = ame_util.perOrigSystem and
558: orig_system_id = (select employee_id
559: from fnd_user
560: where
561: user_id = origSystemIdIn and
564: nvl(end_date, sysdate)))) and
565: status = 'ACTIVE' and
566: (expiration_date is null or
567: sysdate < expiration_date) and
568: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
569: or exists (select null
570: from fnd_user u
571: where u.user_name = wf_roles.name
572: and trunc(sysdate) between u.start_date
582: exception
583: when no_data_found then
584: if(raiseNoDataFoundIn = 'true') then
585: errorCode := -20001;
586: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
587: messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
588: tokenNameOneIn => 'ORIG_SYSTEM_ID',
589: tokenValueOneIn => origSystemIdIn,
590: tokenNameTwoIn => 'ORIG_SYSTEM',
589: tokenValueOneIn => origSystemIdIn,
590: tokenNameTwoIn => 'ORIG_SYSTEM',
591: tokenValueTwoIn => origSystemIn
592: );
593: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
594: routineNameIn => 'getWfRolesName',
595: exceptionNumberIn => errorCode,
596: exceptionStringIn => errorMessage);
597: raise_application_error(errorCode,
598: errorMessage);
599: end if;
600: return(null);
601: when others then
602: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
603: routineNameIn => 'getWfRolesName',
604: exceptionNumberIn => sqlcode,
605: exceptionStringIn => sqlerrm);
606: raise;
613: into rowCount
614: from ame_approver_type_usages
615: where
616: action_type_id = actionTypeIdIn and
617: approver_type_id = ame_util.anyApproverType and
618: sysdate between
619: start_date and
620: nvl(end_date - ame_util.oneSecond, sysdate);
621: if(rowCount > 0) then
616: action_type_id = actionTypeIdIn and
617: approver_type_id = ame_util.anyApproverType and
618: sysdate between
619: start_date and
620: nvl(end_date - ame_util.oneSecond, sysdate);
621: if(rowCount > 0) then
622: return(true);
623: end if;
624: return false;
623: end if;
624: return false;
625: exception
626: when others then
627: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
628: routineNameIn => 'allowsAllApproverTypes',
629: exceptionNumberIn => sqlcode,
630: exceptionStringIn => sqlerrm);
631: raise;
630: exceptionStringIn => sqlerrm);
631: raise;
632: return(null);
633: end allowsAllApproverTypes;
634: function isASubordinate(approverIn in ame_util.approverRecord2,
635: possibleSubordApproverIn in ame_util.approverRecord2) return boolean is
636: errorCode integer;
637: errorMessage ame_util.longestStringType;
638: noSurrogateException exception;
631: raise;
632: return(null);
633: end allowsAllApproverTypes;
634: function isASubordinate(approverIn in ame_util.approverRecord2,
635: possibleSubordApproverIn in ame_util.approverRecord2) return boolean is
636: errorCode integer;
637: errorMessage ame_util.longestStringType;
638: noSurrogateException exception;
639: origSystemId integer;
633: end allowsAllApproverTypes;
634: function isASubordinate(approverIn in ame_util.approverRecord2,
635: possibleSubordApproverIn in ame_util.approverRecord2) return boolean is
636: errorCode integer;
637: errorMessage ame_util.longestStringType;
638: noSurrogateException exception;
639: origSystemId integer;
640: positionStructureId integer;
641: superiorId integer;
643: begin
644: if(approverIn.orig_system <> possibleSubordApproverIn.orig_system) then
645: return false;
646: end if;
647: if(approverIn.orig_system = ame_util.perOrigSystem) then
648: superiorFound := false;
649: origSystemId := possibleSubordApproverIn.orig_system_id;
650: loop
651: select supervisor_id
670: end if;
671: origSystemId := superiorId;
672: end loop;
673: return(superiorFound);
674: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
675: /* No hierarchy defined here, so always return false. */
676: return(false);
677: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
678: superiorFound := false;
673: return(superiorFound);
674: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
675: /* No hierarchy defined here, so always return false. */
676: return(false);
677: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
678: superiorFound := false;
679: origSystemId := possibleSubordApproverIn.orig_system_id;
680: positionStructureId :=
681: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.nonDefPosStructureAttr);
677: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
678: superiorFound := false;
679: origSystemId := possibleSubordApproverIn.orig_system_id;
680: positionStructureId :=
681: ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.nonDefPosStructureAttr);
682: loop
683: select str.parent_position_id
684: into superiorId
685: from per_pos_structure_elements str,
705: end if;
706: origSystemId := superiorId;
707: end loop;
708: return(superiorFound);
709: elsif(approverIn.orig_system = ame_util.fndRespOrigSystem) then
710: /* To be coded later. For now just return false. */
711: return(false);
712: else
713: return(false);
715: exception
716: when no_data_found then
717: return false;
718: when others then
719: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
720: routineNameIn => 'isASubordinate',
721: exceptionNumberIn => sqlcode,
722: exceptionStringIn => sqlerrm);
723: raise;
723: raise;
724: end isASubordinate;
725: function validateApprover(nameIn in varchar2) return boolean as
726: errorCode integer;
727: errorMessage ame_util.longestStringType;
728: rowCount integer;
729: begin
730: select count(*)
731: into rowCount
741: return(false);
742: exception
743: when no_data_found then
744: errorCode := -20001;
745: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
746: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
747: tokenNameOneIn => 'NAME',
748: tokenValueOneIn => nameIn);
749: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
745: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
746: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
747: tokenNameOneIn => 'NAME',
748: tokenValueOneIn => nameIn);
749: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
750: routineNameIn => 'validateApprover',
751: exceptionNumberIn => errorCode,
752: exceptionStringIn => errorMessage);
753: raise_application_error(errorCode,
753: raise_application_error(errorCode,
754: errorMessage);
755: return(false);
756: when others then
757: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
758: routineNameIn => 'validateApprover',
759: exceptionNumberIn => sqlcode,
760: exceptionStringIn => sqlerrm);
761: raise;
774: truncatedSysdateIn in date,
775: rowsToExcludeIn in integer) is
776: select
777: /* The compiler forces passing arguments by position in the following function calls. */
778: getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
779: getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
780: from
781: fnd_user,
782: wf_roles
775: rowsToExcludeIn in integer) is
776: select
777: /* The compiler forces passing arguments by position in the following function calls. */
778: getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
779: getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
780: from
781: fnd_user,
782: wf_roles
783: where
781: fnd_user,
782: wf_roles
783: where
784: wf_roles.orig_system_id = fnd_user.user_id and
785: wf_roles.orig_system = ame_util.fndUserOrigSystem and
786: wf_roles.status = 'ACTIVE' and
787: wf_roles.name = fnd_user.user_name and
788: (userNameIn is null or
789: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
794: nvl(fnd_user.end_date, truncatedSysdateIn) and
795: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
796: order by fnd_user.user_name;
797: /* local variables */
798: approverNames ame_util.longStringList;
799: approverDescriptions ame_util.longStringList;
800: errorCode integer;
801: errorMessage ame_util.longestStringType;
802: truncatedSysdate date;
795: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
796: order by fnd_user.user_name;
797: /* local variables */
798: approverNames ame_util.longStringList;
799: approverDescriptions ame_util.longStringList;
800: errorCode integer;
801: errorMessage ame_util.longestStringType;
802: truncatedSysdate date;
803: /* procedure body */
797: /* local variables */
798: approverNames ame_util.longStringList;
799: approverDescriptions ame_util.longStringList;
800: errorCode integer;
801: errorMessage ame_util.longestStringType;
802: truncatedSysdate date;
803: /* procedure body */
804: begin
805: /*
818: approverDescriptions;
819: close fndUsrCursor;
820: /* Check for too many results. */
821: if(approverNames.count - excludeListCountIn > 50) then
822: raise ame_util.tooManyApproversException;
823: approverNamesOut := null;
824: approverDescriptionsOut := null;
825: return;
826: end if;
825: return;
826: end if;
827: /* Check for zero approvers. */
828: if(approverNames.count = 0) then
829: raise ame_util.zeroApproversException;
830: approverNamesOut := null;
831: approverDescriptionsOut := null;
832: return;
833: end if;
831: approverDescriptionsOut := null;
832: return;
833: end if;
834: /*
835: Return the results. (ame_util.serializeApprovers procedure will raise
836: ame_util.tooManyApproversException if it can't serialize both input lists.)
837: */
838: ame_util.serializeApprovers(approverNamesIn => approverNames,
839: approverDescriptionsIn => approverDescriptions,
832: return;
833: end if;
834: /*
835: Return the results. (ame_util.serializeApprovers procedure will raise
836: ame_util.tooManyApproversException if it can't serialize both input lists.)
837: */
838: ame_util.serializeApprovers(approverNamesIn => approverNames,
839: approverDescriptionsIn => approverDescriptions,
840: maxOutputLengthIn => ame_util.longestStringTypeLength,
834: /*
835: Return the results. (ame_util.serializeApprovers procedure will raise
836: ame_util.tooManyApproversException if it can't serialize both input lists.)
837: */
838: ame_util.serializeApprovers(approverNamesIn => approverNames,
839: approverDescriptionsIn => approverDescriptions,
840: maxOutputLengthIn => ame_util.longestStringTypeLength,
841: approverNamesOut => approverNamesOut,
842: approverDescriptionsOut => approverDescriptionsOut);
836: ame_util.tooManyApproversException if it can't serialize both input lists.)
837: */
838: ame_util.serializeApprovers(approverNamesIn => approverNames,
839: approverDescriptionsIn => approverDescriptions,
840: maxOutputLengthIn => ame_util.longestStringTypeLength,
841: approverNamesOut => approverNamesOut,
842: approverDescriptionsOut => approverDescriptionsOut);
843: exception
844: when ame_util.tooManyApproversException then
840: maxOutputLengthIn => ame_util.longestStringTypeLength,
841: approverNamesOut => approverNamesOut,
842: approverDescriptionsOut => approverDescriptionsOut);
843: exception
844: when ame_util.tooManyApproversException then
845: errorCode := -20001;
846: errorMessage :=
847: ame_util.getMessage(applicationShortNameIn => 'PER',
848: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
843: exception
844: when ame_util.tooManyApproversException then
845: errorCode := -20001;
846: errorMessage :=
847: ame_util.getMessage(applicationShortNameIn => 'PER',
848: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
849: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
850: routineNameIn => 'fndUsrApproverQuery',
851: exceptionNumberIn => errorCode,
845: errorCode := -20001;
846: errorMessage :=
847: ame_util.getMessage(applicationShortNameIn => 'PER',
848: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
849: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
850: routineNameIn => 'fndUsrApproverQuery',
851: exceptionNumberIn => errorCode,
852: exceptionStringIn => errorMessage);
853: approverNamesOut := null;
853: approverNamesOut := null;
854: approverDescriptionsOut := null;
855: raise_application_error(errorCode,
856: errorMessage);
857: when ame_util.zeroApproversException then
858: errorCode := -20001;
859: errorMessage :=
860: ame_util.getMessage(applicationShortNameIn => 'PER',
861: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
856: errorMessage);
857: when ame_util.zeroApproversException then
858: errorCode := -20001;
859: errorMessage :=
860: ame_util.getMessage(applicationShortNameIn => 'PER',
861: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
862: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
863: routineNameIn => 'fndUsrApproverQuery',
864: exceptionNumberIn => errorCode,
858: errorCode := -20001;
859: errorMessage :=
860: ame_util.getMessage(applicationShortNameIn => 'PER',
861: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
862: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
863: routineNameIn => 'fndUsrApproverQuery',
864: exceptionNumberIn => errorCode,
865: exceptionStringIn => errorMessage);
866: approverNamesOut := null;
867: approverDescriptionsOut := null;
868: raise_application_error(errorCode,
869: errorMessage);
870: when others then
871: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
872: routineNameIn => 'fndUsrApproverQuery',
873: exceptionNumberIn => sqlcode,
874: exceptionStringIn => sqlerrm);
875: approverNamesOut := null;
888: responsibilityNameIn in varchar2,
889: truncatedSysdateIn in date,
890: rowsToExcludeIn in integer) is
891: select
892: getWfRolesName(ame_util.fndRespOrigSystem||resp.application_id, resp.responsibility_id) approver_name,
893: getApproverDescription(getWfRolesName(ame_util.fndRespOrigSystem || resp.application_id,
894: resp.responsibility_id)) approver_description
895: from
896: fnd_application_vl apps,
889: truncatedSysdateIn in date,
890: rowsToExcludeIn in integer) is
891: select
892: getWfRolesName(ame_util.fndRespOrigSystem||resp.application_id, resp.responsibility_id) approver_name,
893: getApproverDescription(getWfRolesName(ame_util.fndRespOrigSystem || resp.application_id,
894: resp.responsibility_id)) approver_description
895: from
896: fnd_application_vl apps,
897: fnd_responsibility_vl resp
906: nvl(resp.end_date,truncatedSysdateIn) and
907: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
908: order by apps.application_name;
909: /* local variables */
910: approverNames ame_util.longStringList;
911: approverDescriptions ame_util.longStringList;
912: errorCode integer;
913: errorMessage ame_util.longestStringType;
914: truncatedSysdate date;
907: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
908: order by apps.application_name;
909: /* local variables */
910: approverNames ame_util.longStringList;
911: approverDescriptions ame_util.longStringList;
912: errorCode integer;
913: errorMessage ame_util.longestStringType;
914: truncatedSysdate date;
915: /* procedure body */
909: /* local variables */
910: approverNames ame_util.longStringList;
911: approverDescriptions ame_util.longStringList;
912: errorCode integer;
913: errorMessage ame_util.longestStringType;
914: truncatedSysdate date;
915: /* procedure body */
916: begin
917: /*
930: approverDescriptions;
931: close respCursor;
932: /* Check for too many results. */
933: if(approverNames.count - excludeListCountIn > 50) then
934: raise ame_util.tooManyApproversException;
935: approverNamesOut := null;
936: approverDescriptionsOut := null;
937: return;
938: end if;
937: return;
938: end if;
939: /* Check for zero approvers. */
940: if(approverNames.count = 0) then
941: raise ame_util.zeroApproversException;
942: approverNamesOut := null;
943: approverDescriptionsOut := null;
944: return;
945: end if;
943: approverDescriptionsOut := null;
944: return;
945: end if;
946: /*
947: Return the results. (ame_util.serializeApprovers procedure will raise
948: ame_util.tooManyApproversException if it can't serialize both input lists.)
949: */
950: ame_util.serializeApprovers(approverNamesIn => approverNames,
951: approverDescriptionsIn => approverDescriptions,
944: return;
945: end if;
946: /*
947: Return the results. (ame_util.serializeApprovers procedure will raise
948: ame_util.tooManyApproversException if it can't serialize both input lists.)
949: */
950: ame_util.serializeApprovers(approverNamesIn => approverNames,
951: approverDescriptionsIn => approverDescriptions,
952: maxOutputLengthIn => ame_util.longestStringTypeLength,
946: /*
947: Return the results. (ame_util.serializeApprovers procedure will raise
948: ame_util.tooManyApproversException if it can't serialize both input lists.)
949: */
950: ame_util.serializeApprovers(approverNamesIn => approverNames,
951: approverDescriptionsIn => approverDescriptions,
952: maxOutputLengthIn => ame_util.longestStringTypeLength,
953: approverNamesOut => approverNamesOut,
954: approverDescriptionsOut => approverDescriptionsOut);
948: ame_util.tooManyApproversException if it can't serialize both input lists.)
949: */
950: ame_util.serializeApprovers(approverNamesIn => approverNames,
951: approverDescriptionsIn => approverDescriptions,
952: maxOutputLengthIn => ame_util.longestStringTypeLength,
953: approverNamesOut => approverNamesOut,
954: approverDescriptionsOut => approverDescriptionsOut);
955: exception
956: when ame_util.tooManyApproversException then
952: maxOutputLengthIn => ame_util.longestStringTypeLength,
953: approverNamesOut => approverNamesOut,
954: approverDescriptionsOut => approverDescriptionsOut);
955: exception
956: when ame_util.tooManyApproversException then
957: errorCode := -20001;
958: errorMessage :=
959: ame_util.getMessage(applicationShortNameIn => 'PER',
960: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
955: exception
956: when ame_util.tooManyApproversException then
957: errorCode := -20001;
958: errorMessage :=
959: ame_util.getMessage(applicationShortNameIn => 'PER',
960: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
961: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
962: routineNameIn => 'fndRespApproverQuery',
963: exceptionNumberIn => errorCode,
957: errorCode := -20001;
958: errorMessage :=
959: ame_util.getMessage(applicationShortNameIn => 'PER',
960: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
961: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
962: routineNameIn => 'fndRespApproverQuery',
963: exceptionNumberIn => errorCode,
964: exceptionStringIn => errorMessage);
965: approverNamesOut := null;
965: approverNamesOut := null;
966: approverDescriptionsOut := null;
967: raise_application_error(errorCode,
968: errorMessage);
969: when ame_util.zeroApproversException then
970: errorCode := -20001;
971: errorMessage :=
972: ame_util.getMessage(applicationShortNameIn => 'PER',
973: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
968: errorMessage);
969: when ame_util.zeroApproversException then
970: errorCode := -20001;
971: errorMessage :=
972: ame_util.getMessage(applicationShortNameIn => 'PER',
973: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
974: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
975: routineNameIn => 'fndRespApproverQuery',
976: exceptionNumberIn => errorCode,
970: errorCode := -20001;
971: errorMessage :=
972: ame_util.getMessage(applicationShortNameIn => 'PER',
973: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
974: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
975: routineNameIn => 'fndRespApproverQuery',
976: exceptionNumberIn => errorCode,
977: exceptionStringIn => errorMessage);
978: approverNamesOut := null;
979: approverDescriptionsOut := null;
980: raise_application_error(errorCode,
981: errorMessage);
982: when others then
983: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
984: routineNameIn => 'fndRespApproverQuery',
985: exceptionNumberIn => sqlcode,
986: exceptionStringIn => sqlerrm);
987: approverNamesOut := null;
995: begin
996: /*
997: This function needs to return the same data for all approver types. Do NOT modify this
998: function to return extra data for specific approver types. This function should return
999: a string that will fit in an ame_util.longStringType.
1000: */
1001: validityOut := false;
1002: begin
1003: select
1043: data in fnd_lookups for FND responsibilities. The kludge is permanent, so we have
1044: to accommodate it here.
1045: */
1046: if origSystem is not null then
1047: if(origSystem like ame_util.fndRespOrigSystem || '%') then
1048: origSystem := ame_util.fndRespOrigSystem;
1049: end if;
1050: descriptionOut :=
1051: ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem)
1044: to accommodate it here.
1045: */
1046: if origSystem is not null then
1047: if(origSystem like ame_util.fndRespOrigSystem || '%') then
1048: origSystem := ame_util.fndRespOrigSystem;
1049: end if;
1050: descriptionOut :=
1051: ame_approver_type_pkg.getOrigSystemDisplayName(origSystemIn => origSystem)
1052: ||': '
1052: ||': '
1053: ||descriptionOut;
1054: end if;
1055: if(not validityOut) then
1056: descriptionOut := ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
1057: attributeCodeIn => 'AME_INVALID_COLON') || descriptionOut;
1058: end if;
1059: exception
1060: when others then
1057: attributeCodeIn => 'AME_INVALID_COLON') || descriptionOut;
1058: end if;
1059: exception
1060: when others then
1061: descriptionOut := ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
1062: attributeCodeIn => 'AME_INVALID_COLON') || nameIn;
1063: validityOut := false;
1064: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1065: routineNameIn => 'getApproverDescAndValidity',
1060: when others then
1061: descriptionOut := ame_util.getLabel(attributeApplicationIdIn => ame_util.perFndAppId,
1062: attributeCodeIn => 'AME_INVALID_COLON') || nameIn;
1063: validityOut := false;
1064: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1065: routineNameIn => 'getApproverDescAndValidity',
1066: exceptionNumberIn => sqlcode,
1067: exceptionStringIn => sqlerrm);
1068: raise;
1070: procedure getApproverOrigSystemAndId(nameIn in varchar2,
1071: origSystemOut out nocopy varchar2,
1072: origSystemIdOut out nocopy integer) as
1073: errorCode integer;
1074: errorMessage ame_util.longestStringType;
1075: begin
1076: select
1077: orig_system,
1078: orig_system_id
1090: when no_data_found then
1091: origSystemOut := null;
1092: origSystemIdOut := null;
1093: errorCode := -20001;
1094: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1095: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1096: tokenNameOneIn => 'NAME',
1097: tokenValueOneIn => nameIn);
1098: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1094: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1095: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1096: tokenNameOneIn => 'NAME',
1097: tokenValueOneIn => nameIn);
1098: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1099: routineNameIn => 'getApproverOrigSystemAndId',
1100: exceptionNumberIn => errorCode,
1101: exceptionStringIn => errorMessage);
1102: raise_application_error(errorCode,
1101: exceptionStringIn => errorMessage);
1102: raise_application_error(errorCode,
1103: errorMessage);
1104: when others then
1105: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1106: routineNameIn => 'getApproverOrigSystemAndId',
1107: exceptionNumberIn => sqlcode,
1108: exceptionStringIn => sqlerrm);
1109: origSystemOut := null;
1110: origSystemIdOut := null;
1111: raise;
1112: end getApproverOrigSystemAndId;
1113: procedure getApprovalTypes(approverTypeIdIn in integer,
1114: actionTypeNamesOut out nocopy ame_util.stringList) as
1115: cursor getApprovalTypeCursor is
1116: select name
1117: from
1118: ame_action_types,
1120: where
1121: ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
1122: approver_type_id = approverTypeIdIn and
1123: sysdate between ame_action_types.start_date and
1124: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1125: sysdate between ame_approver_type_usages.start_date and
1126: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1127: cursor getApprovalTypeCursor2 is
1128: select name
1122: approver_type_id = approverTypeIdIn and
1123: sysdate between ame_action_types.start_date and
1124: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1125: sysdate between ame_approver_type_usages.start_date and
1126: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1127: cursor getApprovalTypeCursor2 is
1128: select name
1129: from
1130: ame_action_types,
1130: ame_action_types,
1131: ame_approver_type_usages
1132: where
1133: ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
1134: approver_type_id = ame_util.anyApproverType and
1135: sysdate between ame_action_types.start_date and
1136: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1137: sysdate between ame_approver_type_usages.start_date and
1138: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1132: where
1133: ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
1134: approver_type_id = ame_util.anyApproverType and
1135: sysdate between ame_action_types.start_date and
1136: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1137: sysdate between ame_approver_type_usages.start_date and
1138: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1139: tempIndex integer;
1140: begin
1134: approver_type_id = ame_util.anyApproverType and
1135: sysdate between ame_action_types.start_date and
1136: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
1137: sysdate between ame_approver_type_usages.start_date and
1138: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
1139: tempIndex integer;
1140: begin
1141: tempIndex := 1;
1142: for getApprovalTypeRec in getApprovalTypeCursor loop
1149: end loop;
1150: exception
1151: when others then
1152: rollback;
1153: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1154: routineNameIn => 'getApprovalTypes',
1155: exceptionNumberIn => sqlcode,
1156: exceptionStringIn => sqlerrm);
1157: actionTypeNamesOut := ame_util.emptyStringList;
1153: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1154: routineNameIn => 'getApprovalTypes',
1155: exceptionNumberIn => sqlcode,
1156: exceptionStringIn => sqlerrm);
1157: actionTypeNamesOut := ame_util.emptyStringList;
1158: raise;
1159: end getApprovalTypes;
1160: procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1161: queryVariableLabelsOut out nocopy ame_util.longStringList,
1157: actionTypeNamesOut := ame_util.emptyStringList;
1158: raise;
1159: end getApprovalTypes;
1160: procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1161: queryVariableLabelsOut out nocopy ame_util.longStringList,
1162: variableLovQueriesOut out nocopy ame_util.longStringList) as
1163: queryVariableLabels ame_util.longStringList;
1164: variableLovQueries ame_util.longStringList;
1165: begin
1158: raise;
1159: end getApprovalTypes;
1160: procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1161: queryVariableLabelsOut out nocopy ame_util.longStringList,
1162: variableLovQueriesOut out nocopy ame_util.longStringList) as
1163: queryVariableLabels ame_util.longStringList;
1164: variableLovQueries ame_util.longStringList;
1165: begin
1166: /* select queryVariableLabels and variableLovQueries into plsql tables */
1159: end getApprovalTypes;
1160: procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1161: queryVariableLabelsOut out nocopy ame_util.longStringList,
1162: variableLovQueriesOut out nocopy ame_util.longStringList) as
1163: queryVariableLabels ame_util.longStringList;
1164: variableLovQueries ame_util.longStringList;
1165: begin
1166: /* select queryVariableLabels and variableLovQueries into plsql tables */
1167: select
1160: procedure getApproverTypeQueryData(approverTypeIdIn in integer,
1161: queryVariableLabelsOut out nocopy ame_util.longStringList,
1162: variableLovQueriesOut out nocopy ame_util.longStringList) as
1163: queryVariableLabels ame_util.longStringList;
1164: variableLovQueries ame_util.longStringList;
1165: begin
1166: /* select queryVariableLabels and variableLovQueries into plsql tables */
1167: select
1168: query_variable_1_label,
1190: where
1191: approver_type_id = approverTypeIdIn and
1192: sysdate between
1193: start_date and
1194: nvl(end_date - ame_util.oneSecond, sysdate);
1195: /* loop through the label, assigning the output arguments: */
1196: for i in 1 .. queryVariableLabels.count loop
1197: if(queryVariableLabels(i) is null) then
1198: exit;
1202: end loop;
1203: exception
1204: when others then
1205: rollback;
1206: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1207: routineNameIn => 'getApproverTypeQueryData',
1208: exceptionNumberIn => sqlcode,
1209: exceptionStringIn => sqlerrm);
1210: queryVariableLabelsOut := ame_util.emptyLongStringList;
1206: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1207: routineNameIn => 'getApproverTypeQueryData',
1208: exceptionNumberIn => sqlcode,
1209: exceptionStringIn => sqlerrm);
1210: queryVariableLabelsOut := ame_util.emptyLongStringList;
1211: variableLovQueriesOut := ame_util.emptyLongStringList;
1212: raise;
1213: end getApproverTypeQueryData;
1214: procedure getAvailableApproverTypes(applicationIdIn in integer default null,
1207: routineNameIn => 'getApproverTypeQueryData',
1208: exceptionNumberIn => sqlcode,
1209: exceptionStringIn => sqlerrm);
1210: queryVariableLabelsOut := ame_util.emptyLongStringList;
1211: variableLovQueriesOut := ame_util.emptyLongStringList;
1212: raise;
1213: end getApproverTypeQueryData;
1214: procedure getAvailableApproverTypes(applicationIdIn in integer default null,
1215: topLabelIn in varchar2 default null,
1213: end getApproverTypeQueryData;
1214: procedure getAvailableApproverTypes(applicationIdIn in integer default null,
1215: topLabelIn in varchar2 default null,
1216: topValueIn in varchar2 default null,
1217: approverTypeIdsOut out nocopy ame_util.stringList,
1218: approverTypeNamesOut out nocopy ame_util.stringList) as
1219: cursor getApproverTypeDataCursor is
1220: select
1221: approver_type_id,
1214: procedure getAvailableApproverTypes(applicationIdIn in integer default null,
1215: topLabelIn in varchar2 default null,
1216: topValueIn in varchar2 default null,
1217: approverTypeIdsOut out nocopy ame_util.stringList,
1218: approverTypeNamesOut out nocopy ame_util.stringList) as
1219: cursor getApproverTypeDataCursor is
1220: select
1221: approver_type_id,
1222: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1222: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1223: from ame_approver_types
1224: where sysdate between
1225: start_date and
1226: nvl(end_date - ame_util.oneSecond, sysdate);
1227: cursor getApproverTypeDataCursor2 is
1228: select
1229: approver_type_id,
1230: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1229: approver_type_id,
1230: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1231: from ame_approver_types
1232: where
1233: orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
1234: sysdate between
1235: start_date and
1236: nvl(end_date - ame_util.oneSecond, sysdate);
1237: tempIndex integer;
1232: where
1233: orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
1234: sysdate between
1235: start_date and
1236: nvl(end_date - ame_util.oneSecond, sysdate);
1237: tempIndex integer;
1238: configVarValue ame_util.stringType;
1239: begin
1240: /* check configuration variable value for allowAllApproverTypes */
1234: sysdate between
1235: start_date and
1236: nvl(end_date - ame_util.oneSecond, sysdate);
1237: tempIndex integer;
1238: configVarValue ame_util.stringType;
1239: begin
1240: /* check configuration variable value for allowAllApproverTypes */
1241: configVarValue := ame_util.getConfigVar(applicationIdIn => applicationIdIn,
1242: variableNameIn => ame_util.allowAllApproverTypesConfigVar);
1237: tempIndex integer;
1238: configVarValue ame_util.stringType;
1239: begin
1240: /* check configuration variable value for allowAllApproverTypes */
1241: configVarValue := ame_util.getConfigVar(applicationIdIn => applicationIdIn,
1242: variableNameIn => ame_util.allowAllApproverTypesConfigVar);
1243: tempIndex := 1;
1244: if(configVarValue = ame_util.yes) then
1245: /* loop through getApproverTypeDataCursor assigning the output arguments */
1238: configVarValue ame_util.stringType;
1239: begin
1240: /* check configuration variable value for allowAllApproverTypes */
1241: configVarValue := ame_util.getConfigVar(applicationIdIn => applicationIdIn,
1242: variableNameIn => ame_util.allowAllApproverTypesConfigVar);
1243: tempIndex := 1;
1244: if(configVarValue = ame_util.yes) then
1245: /* loop through getApproverTypeDataCursor assigning the output arguments */
1246: for getApproverTypeDataRec in getApproverTypeDataCursor loop
1240: /* check configuration variable value for allowAllApproverTypes */
1241: configVarValue := ame_util.getConfigVar(applicationIdIn => applicationIdIn,
1242: variableNameIn => ame_util.allowAllApproverTypesConfigVar);
1243: tempIndex := 1;
1244: if(configVarValue = ame_util.yes) then
1245: /* loop through getApproverTypeDataCursor assigning the output arguments */
1246: for getApproverTypeDataRec in getApproverTypeDataCursor loop
1247: if(tempIndex = 1 and topLabelIn is not null) then
1248: approverTypeIdsOut(1) := topValueIn;
1267: end if;
1268: exception
1269: when others then
1270: rollback;
1271: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1272: routineNameIn => 'getAvailableApproverTypes',
1273: exceptionNumberIn => sqlcode,
1274: exceptionStringIn => sqlerrm);
1275: approverTypeIdsOut := ame_util.emptyStringList;
1271: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1272: routineNameIn => 'getAvailableApproverTypes',
1273: exceptionNumberIn => sqlcode,
1274: exceptionStringIn => sqlerrm);
1275: approverTypeIdsOut := ame_util.emptyStringList;
1276: approverTypeNamesOut := ame_util.emptyStringList;
1277: raise;
1278: end getAvailableApproverTypes;
1279: procedure getAvailableApproverTypes2(actionTypeIdIn in integer,
1272: routineNameIn => 'getAvailableApproverTypes',
1273: exceptionNumberIn => sqlcode,
1274: exceptionStringIn => sqlerrm);
1275: approverTypeIdsOut := ame_util.emptyStringList;
1276: approverTypeNamesOut := ame_util.emptyStringList;
1277: raise;
1278: end getAvailableApproverTypes;
1279: procedure getAvailableApproverTypes2(actionTypeIdIn in integer,
1280: approverTypeIdsOut out nocopy ame_util.stringList,
1276: approverTypeNamesOut := ame_util.emptyStringList;
1277: raise;
1278: end getAvailableApproverTypes;
1279: procedure getAvailableApproverTypes2(actionTypeIdIn in integer,
1280: approverTypeIdsOut out nocopy ame_util.stringList,
1281: approverTypeNamesOut out nocopy ame_util.stringList) as
1282: cursor unusedApproverTypeCursor is
1283: select
1284: approver_type_id,
1277: raise;
1278: end getAvailableApproverTypes;
1279: procedure getAvailableApproverTypes2(actionTypeIdIn in integer,
1280: approverTypeIdsOut out nocopy ame_util.stringList,
1281: approverTypeNamesOut out nocopy ame_util.stringList) as
1282: cursor unusedApproverTypeCursor is
1283: select
1284: approver_type_id,
1285: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1285: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1286: from ame_approver_types
1287: where sysdate between
1288: start_date and
1289: nvl(end_date - ame_util.oneSecond, sysdate)
1290: minus
1291: select
1292: ame_approver_types.approver_type_id,
1293: ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
1297: ame_approver_types.approver_type_id = ame_approver_type_usages.approver_type_id and
1298: ame_approver_type_usages.action_type_id = actionTypeIdIn and
1299: sysdate between
1300: ame_approver_types.start_date and
1301: nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
1302: sysdate between
1303: ame_approver_type_usages.start_date and
1304: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
1305: order by approver_type_name;
1300: ame_approver_types.start_date and
1301: nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
1302: sysdate between
1303: ame_approver_type_usages.start_date and
1304: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
1305: order by approver_type_name;
1306: tempIndex integer;
1307: begin
1308: approverTypeIdsOut(1) := ame_util.anyApproverType;
1304: nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
1305: order by approver_type_name;
1306: tempIndex integer;
1307: begin
1308: approverTypeIdsOut(1) := ame_util.anyApproverType;
1309: approverTypeNamesOut(1) := ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_TYPE');
1310: tempIndex := 2;
1311: for unusedApproverTypeRec in unusedApproverTypeCursor loop
1312: /* The explicit conversion below lets nocopy work. */
1305: order by approver_type_name;
1306: tempIndex integer;
1307: begin
1308: approverTypeIdsOut(1) := ame_util.anyApproverType;
1309: approverTypeNamesOut(1) := ame_util.getLabel(ame_util.perFndAppId,'AME_ANY_APPROVER_TYPE');
1310: tempIndex := 2;
1311: for unusedApproverTypeRec in unusedApproverTypeCursor loop
1312: /* The explicit conversion below lets nocopy work. */
1313: approverTypeIdsOut(tempIndex) := to_char(unusedApproverTypeRec.approver_type_id);
1316: end loop;
1317: exception
1318: when others then
1319: rollback;
1320: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1321: routineNameIn => 'getAvailableApproverTypes2',
1322: exceptionNumberIn => sqlcode,
1323: exceptionStringIn => sqlerrm);
1324: approverTypeIdsOut := ame_util.emptyStringList;
1320: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1321: routineNameIn => 'getAvailableApproverTypes2',
1322: exceptionNumberIn => sqlcode,
1323: exceptionStringIn => sqlerrm);
1324: approverTypeIdsOut := ame_util.emptyStringList;
1325: approverTypeNamesOut := ame_util.emptyStringList;
1326: raise;
1327: end getAvailableApproverTypes2;
1328: procedure getAvailableApproverTypes3(actionTypeIdIn in integer,
1321: routineNameIn => 'getAvailableApproverTypes2',
1322: exceptionNumberIn => sqlcode,
1323: exceptionStringIn => sqlerrm);
1324: approverTypeIdsOut := ame_util.emptyStringList;
1325: approverTypeNamesOut := ame_util.emptyStringList;
1326: raise;
1327: end getAvailableApproverTypes2;
1328: procedure getAvailableApproverTypes3(actionTypeIdIn in integer,
1329: approverTypeIdsOut out nocopy ame_util.idList) as
1325: approverTypeNamesOut := ame_util.emptyStringList;
1326: raise;
1327: end getAvailableApproverTypes2;
1328: procedure getAvailableApproverTypes3(actionTypeIdIn in integer,
1329: approverTypeIdsOut out nocopy ame_util.idList) as
1330: cursor availableApproverTypesCursor(actionTypeIdIn in integer) is
1331: select approver_type_id
1332: from ame_approver_type_usages
1333: where
1331: select approver_type_id
1332: from ame_approver_type_usages
1333: where
1334: action_type_id = actionTypeIdIn and
1335: approver_type_id <> ame_util.anyApproverType and
1336: sysdate between
1337: start_date and
1338: nvl(end_date - ame_util.oneSecond, sysdate);
1339: tempIndex integer;
1334: action_type_id = actionTypeIdIn and
1335: approver_type_id <> ame_util.anyApproverType and
1336: sysdate between
1337: start_date and
1338: nvl(end_date - ame_util.oneSecond, sysdate);
1339: tempIndex integer;
1340: begin
1341: tempIndex := 1;
1342: for availableApproverTypesRec in availableApproverTypesCursor(actionTypeIdIn => actionTypeIdIn) loop
1346: end loop;
1347: exception
1348: when others then
1349: rollback;
1350: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1351: routineNameIn => 'getAvailableApproverTypes3',
1352: exceptionNumberIn => sqlcode,
1353: exceptionStringIn => sqlerrm);
1354: approverTypeIdsOut := ame_util.emptyIdList;
1350: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1351: routineNameIn => 'getAvailableApproverTypes3',
1352: exceptionNumberIn => sqlcode,
1353: exceptionStringIn => sqlerrm);
1354: approverTypeIdsOut := ame_util.emptyIdList;
1355: raise;
1356: end getAvailableApproverTypes3;
1357: procedure getOrigSystemIdAndDisplayName(nameIn in varchar2,
1358: origSystemOut out nocopy varchar2,
1358: origSystemOut out nocopy varchar2,
1359: origSystemIdOut out nocopy integer,
1360: displayNameOut out nocopy varchar2) as
1361: errorCode integer;
1362: errorMessage ame_util.longestStringType;
1363: begin
1364: select
1365: orig_system,
1366: orig_system_id,
1381: origSystemOut := null;
1382: origSystemIdOut := null;
1383: displayNameOut := null;
1384: errorCode := -20001;
1385: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1386: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1387: tokenNameOneIn => 'NAME',
1388: tokenValueOneIn => nameIn);
1389: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1385: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1386: messageNameIn => 'AME_400405_APPR_TYPE_NDF',
1387: tokenNameOneIn => 'NAME',
1388: tokenValueOneIn => nameIn);
1389: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1390: routineNameIn => 'getOrigSystemIdAndDisplayName',
1391: exceptionNumberIn => errorCode,
1392: exceptionStringIn => errorMessage);
1393: raise_application_error(errorCode,
1395: when others then
1396: origSystemOut := null;
1397: origSystemIdOut := null;
1398: displayNameOut := null;
1399: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1400: routineNameIn => 'getOrigSystemIdAndDisplayName',
1401: exceptionNumberIn => sqlcode,
1402: exceptionStringIn => sqlerrm);
1403: raise;
1405: /*
1406: ame_api2 calls getSuperior. See ER 3267685 for a discussion of how
1407: this procedure will likely be revised after the 11.5.10 release.
1408: */
1409: procedure getSuperior(approverIn in ame_util.approverRecord2,
1410: superiorOut out nocopy ame_util.approverRecord2) is
1411: approverName wf_roles.display_name%type;
1412: errorCode integer;
1413: errorMessage ame_util.longestStringType;
1406: ame_api2 calls getSuperior. See ER 3267685 for a discussion of how
1407: this procedure will likely be revised after the 11.5.10 release.
1408: */
1409: procedure getSuperior(approverIn in ame_util.approverRecord2,
1410: superiorOut out nocopy ame_util.approverRecord2) is
1411: approverName wf_roles.display_name%type;
1412: errorCode integer;
1413: errorMessage ame_util.longestStringType;
1414: positionStructureId integer;
1409: procedure getSuperior(approverIn in ame_util.approverRecord2,
1410: superiorOut out nocopy ame_util.approverRecord2) is
1411: approverName wf_roles.display_name%type;
1412: errorCode integer;
1413: errorMessage ame_util.longestStringType;
1414: positionStructureId integer;
1415: noSurrogateException exception;
1416: begin
1417: begin
1414: positionStructureId integer;
1415: noSurrogateException exception;
1416: begin
1417: begin
1418: if(approverIn.orig_system = ame_util.perOrigSystem) then
1419: superiorOut.orig_system := ame_util.perOrigSystem;
1420: select
1421: per_all_assignments_f.supervisor_id,
1422: wf_roles.name,
1415: noSurrogateException exception;
1416: begin
1417: begin
1418: if(approverIn.orig_system = ame_util.perOrigSystem) then
1419: superiorOut.orig_system := ame_util.perOrigSystem;
1420: select
1421: per_all_assignments_f.supervisor_id,
1422: wf_roles.name,
1423: wf_roles.display_name
1428: from
1429: wf_roles,
1430: per_all_assignments_f
1431: where
1432: wf_roles.orig_system = ame_util.perOrigSystem and
1433: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1434: wf_roles.status = 'ACTIVE' and
1435: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1436: exists (select null
1457: rownum < 2;
1458: if(superiorOut.orig_system_id is null) then
1459: raise noSurrogateException;
1460: end if;
1461: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
1462: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1463: select
1464: per_all_assignments_f.supervisor_id,
1465: wf_roles.name,
1458: if(superiorOut.orig_system_id is null) then
1459: raise noSurrogateException;
1460: end if;
1461: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
1462: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1463: select
1464: per_all_assignments_f.supervisor_id,
1465: wf_roles.name,
1466: wf_roles.display_name
1471: from
1472: wf_roles,
1473: per_all_assignments_f
1474: where
1475: wf_roles.orig_system = ame_util.perOrigSystem and
1476: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1477: wf_roles.status = 'ACTIVE' and
1478: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1479: exists (select null
1500: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1501: if(superiorOut.orig_system_id is null) then
1502: raise noSurrogateException;
1503: end if;
1504: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
1505: superiorOut.orig_system := ame_util.posOrigSystem;
1506: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1507: if (positionStructureId is null) then
1508: select
1501: if(superiorOut.orig_system_id is null) then
1502: raise noSurrogateException;
1503: end if;
1504: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
1505: superiorOut.orig_system := ame_util.posOrigSystem;
1506: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1507: if (positionStructureId is null) then
1508: select
1509: str.parent_position_id,
1502: raise noSurrogateException;
1503: end if;
1504: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
1505: superiorOut.orig_system := ame_util.posOrigSystem;
1506: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1507: if (positionStructureId is null) then
1508: select
1509: str.parent_position_id,
1510: wf_roles.name,
1524: nvl(hr_general.get_business_group_id,str.business_group_id) and
1525: str.pos_structure_version_id = psv.pos_structure_version_id and
1526: pst.position_structure_id = psv.position_structure_id and
1527: pst.primary_position_flag = 'Y' and
1528: wf_roles.orig_system = ame_util.posOrigSystem and
1529: wf_roles.orig_system_id = str.parent_position_id and
1530: wf_roles.status = 'ACTIVE' and
1531: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1532: trunc(sysdate) between
1551: str.subordinate_position_id = approverIn.orig_system_id and
1552: str.pos_structure_version_id = psv.pos_structure_version_id and
1553: pst.position_structure_id = positionStructureId and
1554: pst.position_structure_id = psv.position_structure_id and
1555: wf_roles.orig_system = ame_util.posOrigSystem and
1556: wf_roles.orig_system_id = str.parent_position_id and
1557: wf_roles.status = 'ACTIVE' and
1558: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1559: trunc(sysdate) between
1560: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1561: rownum < 2
1562: order by wf_roles.name;
1563: end if;
1564: elsif(approverIn.orig_system = ame_util.fndRespOrigSystem) then
1565: null;
1566: else
1567: raise noSurrogateException;
1568: end if;
1576: when noSurrogateException then
1577: approverName := getApproverDisplayName2(origSystemIn => approverIn.orig_system,
1578: origSystemIdIn => approverIn.orig_system_id);
1579: errorCode := -20001;
1580: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1581: messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1582: tokenNameOneIn => 'NAME',
1583: tokenValueOneIn => approverName);
1584: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1580: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1581: messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1582: tokenNameOneIn => 'NAME',
1583: tokenValueOneIn => approverName);
1584: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1585: routineNameIn => 'getSuperior',
1586: exceptionNumberIn => errorCode,
1587: exceptionStringIn => errorMessage);
1588: raise_application_error(errorCode,
1587: exceptionStringIn => errorMessage);
1588: raise_application_error(errorCode,
1589: errorMessage);
1590: when others then
1591: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1592: routineNameIn => 'getSuperior',
1593: exceptionNumberIn => sqlcode,
1594: exceptionStringIn => sqlerrm);
1595: raise;
1605: wfRolesNameOut out nocopy varchar2,
1606: displayNameOut out nocopy varchar2) as
1607: approverName wf_roles.display_name%type;
1608: errorCode integer;
1609: errorMessage ame_util.longestStringType;
1610: positionStructureId integer;
1611: noSurrogateException exception;
1612: begin
1613: begin
1610: positionStructureId integer;
1611: noSurrogateException exception;
1612: begin
1613: begin
1614: if(origSystemIn = ame_util.perOrigSystem) then
1615: select
1616: per_all_assignments_f.supervisor_id,
1617: wf_roles.name,
1618: wf_roles.display_name
1623: from
1624: wf_roles,
1625: per_all_assignments_f
1626: where
1627: wf_roles.orig_system = ame_util.perOrigSystem and
1628: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1629: wf_roles.status = 'ACTIVE' and
1630: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1631: exists (select null
1652: rownum < 2;
1653: if(origSystemIdOut is null) then
1654: raise noSurrogateException;
1655: end if;
1656: elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1657: select
1658: per_all_assignments_f.supervisor_id,
1659: wf_roles.name,
1660: wf_roles.display_name
1665: from
1666: wf_roles,
1667: per_all_assignments_f
1668: where
1669: wf_roles.orig_system = ame_util.perOrigSystem and
1670: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1671: wf_roles.status = 'ACTIVE' and
1672: exists (select null
1673: from fnd_user u
1694: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1695: if(origSystemIdOut is null) then
1696: raise noSurrogateException;
1697: end if;
1698: elsif(origSystemIn = ame_util.posOrigSystem) then
1699: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1700: if (positionStructureId is null) then
1701: select
1702: str.parent_position_id,
1695: if(origSystemIdOut is null) then
1696: raise noSurrogateException;
1697: end if;
1698: elsif(origSystemIn = ame_util.posOrigSystem) then
1699: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1700: if (positionStructureId is null) then
1701: select
1702: str.parent_position_id,
1703: wf_roles.name,
1717: nvl(hr_general.get_business_group_id,str.business_group_id) and
1718: str.pos_structure_version_id = psv.pos_structure_version_id and
1719: pst.position_structure_id = psv.position_structure_id and
1720: pst.primary_position_flag = 'Y' and
1721: wf_roles.orig_system = ame_util.posOrigSystem and
1722: wf_roles.orig_system_id = str.parent_position_id and
1723: wf_roles.status = 'ACTIVE' and
1724: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1725: trunc(sysdate) between
1744: str.subordinate_position_id = origSystemIdIn and
1745: str.pos_structure_version_id = psv.pos_structure_version_id and
1746: pst.position_structure_id = positionStructureId and
1747: pst.position_structure_id = psv.position_structure_id and
1748: wf_roles.orig_system = ame_util.posOrigSystem and
1749: wf_roles.orig_system_id = str.parent_position_id and
1750: wf_roles.status = 'ACTIVE' and
1751: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1752: trunc(sysdate) between
1753: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1754: rownum < 2
1755: order by wf_roles.name;
1756: end if;
1757: elsif(origSystemIn = ame_util.fndRespOrigSystem) then
1758: null;
1759: else
1760: raise noSurrogateException;
1761: end if;
1769: when noSurrogateException then
1770: approverName := getApproverDisplayName2(origSystemIn => origSystemIn,
1771: origSystemIdIn => origSystemIdIn);
1772: errorCode := -20001;
1773: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1774: messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1775: tokenNameOneIn => 'NAME',
1776: tokenValueOneIn => approverName);
1777: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1773: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1774: messageNameIn => 'AME_400436_APPR_NO_APPR_EXTS',
1775: tokenNameOneIn => 'NAME',
1776: tokenValueOneIn => approverName);
1777: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1778: routineNameIn => 'getSurrogate',
1779: exceptionNumberIn => errorCode,
1780: exceptionStringIn => errorMessage);
1781: raise_application_error(errorCode,
1780: exceptionStringIn => errorMessage);
1781: raise_application_error(errorCode,
1782: errorMessage);
1783: when others then
1784: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1785: routineNameIn => 'getSurrogate',
1786: exceptionNumberIn => sqlcode,
1787: exceptionStringIn => sqlerrm);
1788: raise;
1788: raise;
1789: end getSurrogate;
1790: procedure getWfRolesNameAndDisplayName(origSystemIn in varchar2,
1791: origSystemIdIn in integer,
1792: nameOut out nocopy ame_util.longStringType,
1793: displayNameOut out nocopy ame_util.longStringType) as
1794: errorCode integer;
1795: errorMessage ame_util.longestStringType;
1796: begin
1789: end getSurrogate;
1790: procedure getWfRolesNameAndDisplayName(origSystemIn in varchar2,
1791: origSystemIdIn in integer,
1792: nameOut out nocopy ame_util.longStringType,
1793: displayNameOut out nocopy ame_util.longStringType) as
1794: errorCode integer;
1795: errorMessage ame_util.longestStringType;
1796: begin
1797: /*
1791: origSystemIdIn in integer,
1792: nameOut out nocopy ame_util.longStringType,
1793: displayNameOut out nocopy ame_util.longStringType) as
1794: errorCode integer;
1795: errorMessage ame_util.longestStringType;
1796: begin
1797: /*
1798: This procedure should select the input approver's wf_roles.display_name, NOT the
1799: display name of the input approver's orig_system.
1816: from wf_roles
1817: where
1818: ((orig_system = origSystemIn and
1819: orig_system_id = origSystemIdIn) or
1820: (origSystemIn = ame_util.fndUserOrigSystem and
1821: orig_system = ame_util.perOrigSystem and
1822: orig_system_id = (select employee_id
1823: from fnd_user
1824: where
1817: where
1818: ((orig_system = origSystemIn and
1819: orig_system_id = origSystemIdIn) or
1820: (origSystemIn = ame_util.fndUserOrigSystem and
1821: orig_system = ame_util.perOrigSystem and
1822: orig_system_id = (select employee_id
1823: from fnd_user
1824: where
1825: user_id = origSystemIdIn and
1828: nvl(end_date, sysdate)))) and
1829: status = 'ACTIVE' and
1830: (expiration_date is null or
1831: sysdate < expiration_date) and
1832: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
1833: or exists (select null
1834: from fnd_user u
1835: where u.user_name = wf_roles.name
1836: and trunc(sysdate) between u.start_date
1844: rownum < 2;
1845: exception
1846: when no_data_found then
1847: errorCode := -20001;
1848: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1849: messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
1850: tokenNameOneIn => 'ORIG_SYSTEM_ID',
1851: tokenValueOneIn => origSystemIdIn,
1852: tokenNameTwoIn => 'ORIG_SYSTEM',
1851: tokenValueOneIn => origSystemIdIn,
1852: tokenNameTwoIn => 'ORIG_SYSTEM',
1853: tokenValueTwoIn => origSystemIn
1854: );
1855: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1856: routineNameIn => 'getWfRolesNameAndDisplayName',
1857: exceptionNumberIn => errorCode,
1858: exceptionStringIn => errorMessage);
1859: nameOut := null;
1860: displayNameOut := null;
1861: raise_application_error(errorCode,
1862: errorMessage);
1863: when others then
1864: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1865: routineNameIn => 'getWfRolesNameAndDisplayName',
1866: exceptionNumberIn => sqlcode,
1867: exceptionStringIn => sqlerrm);
1868: nameOut := null;
1873: approverTypeIdIn in integer,
1874: processingDateIn in date) as
1875: currentUserId integer;
1876: begin
1877: currentUserId := ame_util.getCurrentUserId;
1878: insert into ame_approver_type_usages(approver_type_id,
1879: action_type_id,
1880: created_by,
1881: creation_date,
1895: null);
1896: exception
1897: when others then
1898: rollback;
1899: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1900: routineNameIn => 'newApproverTypeUsage',
1901: exceptionNumberIn => sqlcode,
1902: exceptionStringIn => sqlerrm);
1903: raise;
1902: exceptionStringIn => sqlerrm);
1903: raise;
1904: end newApproverTypeUsage;
1905: procedure newApproverTypeUsages(actionTypeIdIn in integer,
1906: approverTypeIdsIn in ame_util.idList,
1907: finalizeIn in boolean default false,
1908: processingDateIn in date default null) as
1909: processingDate date;
1910: begin
1923: end if;
1924: exception
1925: when others then
1926: rollback;
1927: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
1928: routineNameIn => 'newApproverTypeUsages',
1929: exceptionNumberIn => sqlcode,
1930: exceptionStringIn => sqlerrm);
1931: raise;
1944: truncatedSysdateIn in date,
1945: rowsToExcludeIn in integer) is
1946: select
1947: /* The compiler forces passing arguments by position in the following function calls. */
1948: getWfRolesName(ame_util.perOrigSystem, pap.person_id) approver_name
1949: ,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
1950: from
1951: per_all_people_f pap
1952: ,hr_all_organization_units haou
1945: rowsToExcludeIn in integer) is
1946: select
1947: /* The compiler forces passing arguments by position in the following function calls. */
1948: getWfRolesName(ame_util.perOrigSystem, pap.person_id) approver_name
1949: ,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
1950: from
1951: per_all_people_f pap
1952: ,hr_all_organization_units haou
1953: ,wf_roles wfr
1959: (select assignment_status_type_id
1960: from per_assignment_status_types
1961: where per_system_status = 'TERM_ASSIGN')
1962: and wfr.orig_system_id = pap.person_id
1963: and wfr.orig_system = ame_util.perOrigSystem
1964: and wfr.status = 'ACTIVE'
1965: and exists (select null
1966: from fnd_user u
1967: where u.user_name = wfr.name
1976: and truncatedSysdateIn between pas.effective_start_date and pas.effective_end_date
1977: and rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
1978: order by last_name;
1979: /* local variables */
1980: approverNames ame_util.longStringList;
1981: approverDescriptions ame_util.longStringList;
1982: errorCode integer;
1983: errorMessage ame_util.longestStringType;
1984: truncatedSysdate date;
1977: and rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
1978: order by last_name;
1979: /* local variables */
1980: approverNames ame_util.longStringList;
1981: approverDescriptions ame_util.longStringList;
1982: errorCode integer;
1983: errorMessage ame_util.longestStringType;
1984: truncatedSysdate date;
1985: /* procedure body */
1979: /* local variables */
1980: approverNames ame_util.longStringList;
1981: approverDescriptions ame_util.longStringList;
1982: errorCode integer;
1983: errorMessage ame_util.longestStringType;
1984: truncatedSysdate date;
1985: /* procedure body */
1986: begin
1987: /*
2001: approverDescriptions;
2002: close personCursor;
2003: /* Check for too many results. */
2004: if(approverNames.count - excludeListCountIn > 50) then
2005: raise ame_util.tooManyApproversException;
2006: approverNamesOut := null;
2007: approverDescriptionsOut := null;
2008: return;
2009: end if;
2008: return;
2009: end if;
2010: /* Check for zero approvers. */
2011: if(approverNames.count = 0) then
2012: raise ame_util.zeroApproversException;
2013: approverNamesOut := null;
2014: approverDescriptionsOut := null;
2015: return;
2016: end if;
2014: approverDescriptionsOut := null;
2015: return;
2016: end if;
2017: /*
2018: Return the results. (ame_util.serializeApprovers procedure will raise
2019: ame_util.tooManyApproversException if it can't serialize both input lists.)
2020: */
2021: ame_util.serializeApprovers(approverNamesIn => approverNames,
2022: approverDescriptionsIn => approverDescriptions,
2015: return;
2016: end if;
2017: /*
2018: Return the results. (ame_util.serializeApprovers procedure will raise
2019: ame_util.tooManyApproversException if it can't serialize both input lists.)
2020: */
2021: ame_util.serializeApprovers(approverNamesIn => approverNames,
2022: approverDescriptionsIn => approverDescriptions,
2023: maxOutputLengthIn => ame_util.longestStringTypeLength,
2017: /*
2018: Return the results. (ame_util.serializeApprovers procedure will raise
2019: ame_util.tooManyApproversException if it can't serialize both input lists.)
2020: */
2021: ame_util.serializeApprovers(approverNamesIn => approverNames,
2022: approverDescriptionsIn => approverDescriptions,
2023: maxOutputLengthIn => ame_util.longestStringTypeLength,
2024: approverNamesOut => approverNamesOut,
2025: approverDescriptionsOut => approverDescriptionsOut);
2019: ame_util.tooManyApproversException if it can't serialize both input lists.)
2020: */
2021: ame_util.serializeApprovers(approverNamesIn => approverNames,
2022: approverDescriptionsIn => approverDescriptions,
2023: maxOutputLengthIn => ame_util.longestStringTypeLength,
2024: approverNamesOut => approverNamesOut,
2025: approverDescriptionsOut => approverDescriptionsOut);
2026: exception
2027: when ame_util.tooManyApproversException then
2023: maxOutputLengthIn => ame_util.longestStringTypeLength,
2024: approverNamesOut => approverNamesOut,
2025: approverDescriptionsOut => approverDescriptionsOut);
2026: exception
2027: when ame_util.tooManyApproversException then
2028: errorCode := -20001;
2029: errorMessage :=
2030: ame_util.getMessage(applicationShortNameIn => 'PER',
2031: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2026: exception
2027: when ame_util.tooManyApproversException then
2028: errorCode := -20001;
2029: errorMessage :=
2030: ame_util.getMessage(applicationShortNameIn => 'PER',
2031: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2032: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2033: routineNameIn => 'perApproverQuery',
2034: exceptionNumberIn => errorCode,
2028: errorCode := -20001;
2029: errorMessage :=
2030: ame_util.getMessage(applicationShortNameIn => 'PER',
2031: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2032: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2033: routineNameIn => 'perApproverQuery',
2034: exceptionNumberIn => errorCode,
2035: exceptionStringIn => errorMessage);
2036: approverNamesOut := null;
2036: approverNamesOut := null;
2037: approverDescriptionsOut := null;
2038: raise_application_error(errorCode,
2039: errorMessage);
2040: when ame_util.zeroApproversException then
2041: errorCode := -20001;
2042: errorMessage :=
2043: ame_util.getMessage(applicationShortNameIn => 'PER',
2044: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2039: errorMessage);
2040: when ame_util.zeroApproversException then
2041: errorCode := -20001;
2042: errorMessage :=
2043: ame_util.getMessage(applicationShortNameIn => 'PER',
2044: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2045: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2046: routineNameIn => 'perApproverQuery',
2047: exceptionNumberIn => errorCode,
2041: errorCode := -20001;
2042: errorMessage :=
2043: ame_util.getMessage(applicationShortNameIn => 'PER',
2044: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2045: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2046: routineNameIn => 'perApproverQuery',
2047: exceptionNumberIn => errorCode,
2048: exceptionStringIn => errorMessage);
2049: approverNamesOut := null;
2050: approverDescriptionsOut := null;
2051: raise_application_error(errorCode,
2052: errorMessage);
2053: when others then
2054: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2055: routineNameIn => 'perApproverQuery',
2056: exceptionNumberIn => sqlcode,
2057: exceptionStringIn => sqlerrm);
2058: approverNamesOut := null;
2071: businessGroupNameIn in varchar2,
2072: truncatedSysdateIn in date,
2073: rowsToExcludeIn in integer) is
2074: select
2075: getWfRolesName(ame_util.posOrigSystem, per_positions.position_id) approver_name,
2076: getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
2077: from
2078: per_positions,
2079: hr_organization_units,
2072: truncatedSysdateIn in date,
2073: rowsToExcludeIn in integer) is
2074: select
2075: getWfRolesName(ame_util.posOrigSystem, per_positions.position_id) approver_name,
2076: getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
2077: from
2078: per_positions,
2079: hr_organization_units,
2080: wf_roles
2079: hr_organization_units,
2080: wf_roles
2081: where
2082: wf_roles.orig_system_id = per_positions.position_id and
2083: wf_roles.orig_system = ame_util.posOrigSystem and
2084: wf_roles.status = 'ACTIVE' and
2085: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2086: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
2087: per_positions.business_group_id = hr_organization_units.organization_id and
2093: nvl(hr_organization_units.date_to, truncatedSysdateIn) and
2094: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
2095: order by per_positions.name;
2096: /* local variables */
2097: approverNames ame_util.longStringList;
2098: approverDescriptions ame_util.longStringList;
2099: errorCode integer;
2100: errorMessage ame_util.longestStringType;
2101: truncatedSysdate date;
2094: rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
2095: order by per_positions.name;
2096: /* local variables */
2097: approverNames ame_util.longStringList;
2098: approverDescriptions ame_util.longStringList;
2099: errorCode integer;
2100: errorMessage ame_util.longestStringType;
2101: truncatedSysdate date;
2102: /* procedure body */
2096: /* local variables */
2097: approverNames ame_util.longStringList;
2098: approverDescriptions ame_util.longStringList;
2099: errorCode integer;
2100: errorMessage ame_util.longestStringType;
2101: truncatedSysdate date;
2102: /* procedure body */
2103: begin
2104: /*
2117: approverDescriptions;
2118: close positionCursor;
2119: /* Check for too many results. */
2120: if(approverNames.count - excludeListCountIn > 50) then
2121: raise ame_util.tooManyApproversException;
2122: approverNamesOut := null;
2123: approverDescriptionsOut := null;
2124: return;
2125: end if;
2124: return;
2125: end if;
2126: /* Check for zero approvers. */
2127: if(approverNames.count = 0) then
2128: raise ame_util.zeroApproversException;
2129: approverNamesOut := null;
2130: approverDescriptionsOut := null;
2131: return;
2132: end if;
2130: approverDescriptionsOut := null;
2131: return;
2132: end if;
2133: /*
2134: Return the results. (ame_util.serializeApprovers procedure will raise
2135: ame_util.tooManyApproversException if it can't serialize both input lists.)
2136: */
2137: ame_util.serializeApprovers(approverNamesIn => approverNames,
2138: approverDescriptionsIn => approverDescriptions,
2131: return;
2132: end if;
2133: /*
2134: Return the results. (ame_util.serializeApprovers procedure will raise
2135: ame_util.tooManyApproversException if it can't serialize both input lists.)
2136: */
2137: ame_util.serializeApprovers(approverNamesIn => approverNames,
2138: approverDescriptionsIn => approverDescriptions,
2139: maxOutputLengthIn => ame_util.longestStringTypeLength,
2133: /*
2134: Return the results. (ame_util.serializeApprovers procedure will raise
2135: ame_util.tooManyApproversException if it can't serialize both input lists.)
2136: */
2137: ame_util.serializeApprovers(approverNamesIn => approverNames,
2138: approverDescriptionsIn => approverDescriptions,
2139: maxOutputLengthIn => ame_util.longestStringTypeLength,
2140: approverNamesOut => approverNamesOut,
2141: approverDescriptionsOut => approverDescriptionsOut);
2135: ame_util.tooManyApproversException if it can't serialize both input lists.)
2136: */
2137: ame_util.serializeApprovers(approverNamesIn => approverNames,
2138: approverDescriptionsIn => approverDescriptions,
2139: maxOutputLengthIn => ame_util.longestStringTypeLength,
2140: approverNamesOut => approverNamesOut,
2141: approverDescriptionsOut => approverDescriptionsOut);
2142: exception
2143: when ame_util.tooManyApproversException then
2139: maxOutputLengthIn => ame_util.longestStringTypeLength,
2140: approverNamesOut => approverNamesOut,
2141: approverDescriptionsOut => approverDescriptionsOut);
2142: exception
2143: when ame_util.tooManyApproversException then
2144: errorCode := -20001;
2145: errorMessage :=
2146: ame_util.getMessage(applicationShortNameIn => 'PER',
2147: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2142: exception
2143: when ame_util.tooManyApproversException then
2144: errorCode := -20001;
2145: errorMessage :=
2146: ame_util.getMessage(applicationShortNameIn => 'PER',
2147: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2148: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2149: routineNameIn => 'posApproverQuery',
2150: exceptionNumberIn => errorCode,
2144: errorCode := -20001;
2145: errorMessage :=
2146: ame_util.getMessage(applicationShortNameIn => 'PER',
2147: messageNameIn => 'AME_400111_UIN_MANY_ROWS');
2148: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2149: routineNameIn => 'posApproverQuery',
2150: exceptionNumberIn => errorCode,
2151: exceptionStringIn => errorMessage);
2152: approverNamesOut := null;
2152: approverNamesOut := null;
2153: approverDescriptionsOut := null;
2154: raise_application_error(errorCode,
2155: errorMessage);
2156: when ame_util.zeroApproversException then
2157: errorCode := -20001;
2158: errorMessage :=
2159: ame_util.getMessage(applicationShortNameIn => 'PER',
2160: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2155: errorMessage);
2156: when ame_util.zeroApproversException then
2157: errorCode := -20001;
2158: errorMessage :=
2159: ame_util.getMessage(applicationShortNameIn => 'PER',
2160: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2161: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2162: routineNameIn => 'posApproverQuery',
2163: exceptionNumberIn => errorCode,
2157: errorCode := -20001;
2158: errorMessage :=
2159: ame_util.getMessage(applicationShortNameIn => 'PER',
2160: messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
2161: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2162: routineNameIn => 'posApproverQuery',
2163: exceptionNumberIn => errorCode,
2164: exceptionStringIn => errorMessage);
2165: approverNamesOut := null;
2166: approverDescriptionsOut := null;
2167: raise_application_error(errorCode,
2168: errorMessage);
2169: when others then
2170: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2171: routineNameIn => 'posApproverQuery',
2172: exceptionNumberIn => sqlcode,
2173: exceptionStringIn => sqlerrm);
2174: approverNamesOut := null;
2175: approverDescriptionsOut := null;
2176: raise;
2177: end posApproverQuery;
2178: procedure processApproverQuery(selectClauseIn in varchar2,
2179: approverNamesOut out nocopy ame_util.longStringList,
2180: approverDisplayNamesOut out nocopy ame_util.longStringList) as
2181: tempApproverDisplayName ame_util.longStringType;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2176: raise;
2177: end posApproverQuery;
2178: procedure processApproverQuery(selectClauseIn in varchar2,
2179: approverNamesOut out nocopy ame_util.longStringList,
2180: approverDisplayNamesOut out nocopy ame_util.longStringList) as
2181: tempApproverDisplayName ame_util.longStringType;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2177: end posApproverQuery;
2178: procedure processApproverQuery(selectClauseIn in varchar2,
2179: approverNamesOut out nocopy ame_util.longStringList,
2180: approverDisplayNamesOut out nocopy ame_util.longStringList) as
2181: tempApproverDisplayName ame_util.longStringType;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2185: begin
2178: procedure processApproverQuery(selectClauseIn in varchar2,
2179: approverNamesOut out nocopy ame_util.longStringList,
2180: approverDisplayNamesOut out nocopy ame_util.longStringList) as
2181: tempApproverDisplayName ame_util.longStringType;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2185: begin
2186: /* call the ame_util.getQuery routine and assign to variableCur */
2180: approverDisplayNamesOut out nocopy ame_util.longStringList) as
2181: tempApproverDisplayName ame_util.longStringType;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2185: begin
2186: /* call the ame_util.getQuery routine and assign to variableCur */
2187: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn);
2188: tempIndex := 1;
2182: tempApproverName ame_util.longStringType;
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2185: begin
2186: /* call the ame_util.getQuery routine and assign to variableCur */
2187: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn);
2188: tempIndex := 1;
2189: /* loop through the dynamic cursor fetching into the local variables
2190: variableName and variableDisplayName */
2183: tempIndex integer;
2184: variableCur ame_util.queryCursor;
2185: begin
2186: /* call the ame_util.getQuery routine and assign to variableCur */
2187: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn);
2188: tempIndex := 1;
2189: /* loop through the dynamic cursor fetching into the local variables
2190: variableName and variableDisplayName */
2191: loop
2202: close variableCur;
2203: exception
2204: when others then
2205: rollback;
2206: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2207: routineNameIn => 'processApproverQuery',
2208: exceptionNumberIn => sqlcode,
2209: exceptionStringIn => sqlerrm);
2210: approverNamesOut := ame_util.emptyLongStringList;
2206: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2207: routineNameIn => 'processApproverQuery',
2208: exceptionNumberIn => sqlcode,
2209: exceptionStringIn => sqlerrm);
2210: approverNamesOut := ame_util.emptyLongStringList;
2211: approverDisplayNamesOut := ame_util.emptyLongStringList;
2212: raise;
2213: end processApproverQuery;
2214: procedure processApproverQuery2(selectClauseIn in varchar2,
2207: routineNameIn => 'processApproverQuery',
2208: exceptionNumberIn => sqlcode,
2209: exceptionStringIn => sqlerrm);
2210: approverNamesOut := ame_util.emptyLongStringList;
2211: approverDisplayNamesOut := ame_util.emptyLongStringList;
2212: raise;
2213: end processApproverQuery;
2214: procedure processApproverQuery2(selectClauseIn in varchar2,
2215: approverNamesOut out nocopy ame_util.longStringList) as
2211: approverDisplayNamesOut := ame_util.emptyLongStringList;
2212: raise;
2213: end processApproverQuery;
2214: procedure processApproverQuery2(selectClauseIn in varchar2,
2215: approverNamesOut out nocopy ame_util.longStringList) as
2216: tempApproverDisplayName ame_util.longStringType;
2217: tempApproverName ame_util.longStringType;
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2212: raise;
2213: end processApproverQuery;
2214: procedure processApproverQuery2(selectClauseIn in varchar2,
2215: approverNamesOut out nocopy ame_util.longStringList) as
2216: tempApproverDisplayName ame_util.longStringType;
2217: tempApproverName ame_util.longStringType;
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2220: begin
2213: end processApproverQuery;
2214: procedure processApproverQuery2(selectClauseIn in varchar2,
2215: approverNamesOut out nocopy ame_util.longStringList) as
2216: tempApproverDisplayName ame_util.longStringType;
2217: tempApproverName ame_util.longStringType;
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2220: begin
2221: /* call the ame_util.getQuery routine and assign to variableCur */
2215: approverNamesOut out nocopy ame_util.longStringList) as
2216: tempApproverDisplayName ame_util.longStringType;
2217: tempApproverName ame_util.longStringType;
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2220: begin
2221: /* call the ame_util.getQuery routine and assign to variableCur */
2222: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn); -- may not be right, need to check on this);
2223: tempIndex := 1;
2217: tempApproverName ame_util.longStringType;
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2220: begin
2221: /* call the ame_util.getQuery routine and assign to variableCur */
2222: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn); -- may not be right, need to check on this);
2223: tempIndex := 1;
2224: /* loop through the dynamic cursor fetching into the local variables
2225: variableName and variableDisplayName */
2218: tempIndex integer;
2219: variableCur ame_util.queryCursor;
2220: begin
2221: /* call the ame_util.getQuery routine and assign to variableCur */
2222: variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn); -- may not be right, need to check on this);
2223: tempIndex := 1;
2224: /* loop through the dynamic cursor fetching into the local variables
2225: variableName and variableDisplayName */
2226: loop
2235: close variableCur;
2236: exception
2237: when others then
2238: rollback;
2239: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2240: routineNameIn => 'processApproverQuery2',
2241: exceptionNumberIn => sqlcode,
2242: exceptionStringIn => sqlerrm);
2243: approverNamesOut := ame_util.emptyLongStringList;
2239: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2240: routineNameIn => 'processApproverQuery2',
2241: exceptionNumberIn => sqlcode,
2242: exceptionStringIn => sqlerrm);
2243: approverNamesOut := ame_util.emptyLongStringList;
2244: raise;
2245: end processApproverQuery2;
2246: procedure removeApproverTypeUsage(actionTypeIdIn in integer,
2247: approverTypeIdIn in integer,
2247: approverTypeIdIn in integer,
2248: processingDateIn in date default null) as
2249: currentUserId integer;
2250: errorCode integer;
2251: errorMessage ame_util.longestStringType;
2252: inUseException exception;
2253: processingDate date;
2254: begin
2255: currentUserId := ame_util.getCurrentUserId;
2251: errorMessage ame_util.longestStringType;
2252: inUseException exception;
2253: processingDate date;
2254: begin
2255: currentUserId := ame_util.getCurrentUserId;
2256: update ame_approver_type_usages
2257: set
2258: last_updated_by = currentUserId,
2259: last_update_date = processingDateIn,
2262: where
2263: action_type_id = actionTypeIdIn and
2264: approver_type_id = approverTypeIdIn and
2265: processingDateIn between start_date and
2266: nvl(end_date - ame_util.oneSecond, processingDateIn) ;
2267: exception
2268: when others then
2269: rollback;
2270: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2266: nvl(end_date - ame_util.oneSecond, processingDateIn) ;
2267: exception
2268: when others then
2269: rollback;
2270: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2271: routineNameIn => 'removeApproverTypeUsage',
2272: exceptionNumberIn => sqlcode,
2273: exceptionStringIn => sqlerrm);
2274: raise;
2273: exceptionStringIn => sqlerrm);
2274: raise;
2275: end removeApproverTypeUsage;
2276: procedure removeApproverTypeUsages(actionTypeIdIn in integer,
2277: approverTypeIdsIn in ame_util.idList default ame_util.emptyIdList,
2278: finalizeIn in boolean default false,
2279: processingDateIn in date default null) as
2280: currentUserId integer;
2281: errorCode integer;
2278: finalizeIn in boolean default false,
2279: processingDateIn in date default null) as
2280: currentUserId integer;
2281: errorCode integer;
2282: errorMessage ame_util.longestStringType;
2283: inUseException exception;
2284: processingDate date;
2285: begin
2286: processingDate := sysdate;
2296: end if;
2297: exception
2298: when others then
2299: rollback;
2300: ame_util.runtimeException(packageNameIn => 'ame_approver_type_pkg',
2301: routineNameIn => 'removeApproverTypeUsages',
2302: exceptionNumberIn => sqlcode,
2303: exceptionStringIn => sqlerrm);
2304: raise;