[Home] [Help]
7: /*
8: getApproverDescAndValidity checks for invalid approvers and produces
9: a description string even for these, prepending to an invalid approver's
10: description a label identifying the approver as invalid.If the approver is valid,
11: the wf_roles display name is returned.
12: */
13: getApproverDescAndValidity(nameIn => nameIn,
14: descriptionOut => descriptionOut,
15: validityOut => validityOut);
28: ,raiseNoDataFoundIn in varchar2 default 'true')
29: return varchar2 as
30: errorCode integer;
31: errorMessage ame_util.longestStringType;
32: name wf_roles.display_name%type;
33: begin
34: name := getWfRolesName(origSystemIn => origSystemIn
35: ,origSystemIdIn => origSystemIdIn
36: ,raiseNoDataFoundIn => raiseNoDataFoundIn);
88: raise;
89: return(null);
90: end getApproverDescription2;
91: function getApproverDisplayName(nameIn in varchar2) return varchar2 as
92: displayName wf_roles.display_name%type;
93: errorCode integer;
94: errorMessage ame_util.longestStringType;
95: begin
96: select display_name
94: errorMessage ame_util.longestStringType;
95: begin
96: select display_name
97: into displayName
98: from wf_roles
99: where
100: name = nameIn and
101: status = 'ACTIVE' and
102: (expiration_date is null or
128: attributeCodeIn => 'AME_INVALID_COLON') || nameIn);
129: end getApproverDisplayName;
130: function getApproverDisplayName2(origSystemIn in varchar2,
131: origSystemIdIn in integer) return varchar2 as
132: displayName wf_roles.display_name%type;
133: errorCode integer;
134: errorMessage ame_util.longestStringType;
135: begin
136: /*
134: errorMessage ame_util.longestStringType;
135: begin
136: /*
137: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
138: converted to the PER originating system in wf_roles; otherwise, it gets
139: converted to the FND_USR originating system. As just one of these will
140: happen, we can match both originating systems in a single-row query.
141: The order-by-name clause and rownum < 2 condition are necessary because we
142: have encountered data problems where there are several entries for a given
143: orig_system and orig_system_id pair.
144: */
145: select display_name
146: into displayName
147: from wf_roles
148: where
149: ((orig_system = origSystemIn and
150: orig_system_id = origSystemIdIn) or
151: (origSystemIn = ame_util.fndUserOrigSystem and
162: sysdate < expiration_date) and
163: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
164: or exists (select null
165: from fnd_user u
166: where u.user_name = wf_roles.name
167: and trunc(sysdate) between u.start_date
168: and nvl(u.end_date,trunc(sysdate)))) and
169: not exists (
170: select null from wf_roles wf2
166: where u.user_name = wf_roles.name
167: and trunc(sysdate) between u.start_date
168: and nvl(u.end_date,trunc(sysdate)))) and
169: not exists (
170: select null from wf_roles wf2
171: where wf_roles.orig_system = wf2.orig_system
172: and wf_roles.orig_system_id = wf2.orig_system_id
173: and wf_roles.start_date > wf2.start_date
174: ) and
167: and trunc(sysdate) between u.start_date
168: and nvl(u.end_date,trunc(sysdate)))) and
169: not exists (
170: select null from wf_roles wf2
171: where wf_roles.orig_system = wf2.orig_system
172: and wf_roles.orig_system_id = wf2.orig_system_id
173: and wf_roles.start_date > wf2.start_date
174: ) and
175: rownum < 2;
168: and nvl(u.end_date,trunc(sysdate)))) and
169: not exists (
170: select null from wf_roles wf2
171: where wf_roles.orig_system = wf2.orig_system
172: and wf_roles.orig_system_id = wf2.orig_system_id
173: and wf_roles.start_date > wf2.start_date
174: ) and
175: rownum < 2;
176: return(displayName);
169: not exists (
170: select null from wf_roles wf2
171: where wf_roles.orig_system = wf2.orig_system
172: and wf_roles.orig_system_id = wf2.orig_system_id
173: and wf_roles.start_date > wf2.start_date
174: ) and
175: rownum < 2;
176: return(displayName);
177: exception
200: return(null);
201: end getApproverDisplayName2;
202: /*
203: This procedure returns the display name, origsystemid and origsystem for the given role name.
204: If the name is valid wf_roles name, validityOut is returned as true.If the name is not found,
205: validityOut is returned as false and the display name, origsystemid and origsystem are returned
206: from the wf_local_roles.
207: */
208: procedure getApproverDetails(nameIn in varchar2
219: into
220: displayNameOut,
221: origSystemOut,
222: origSystemIdOut
223: from wf_roles
224: where
225: name = nameIn and
226: status = 'ACTIVE' and
227: (expiration_date is null or
231: exception
232: when no_data_found then
233: begin
234: /*
235: When the approver is not valid in WF_ROLES, try to get the approver info
236: from WF_LOCAL_ROLES. (It may still be there, even though the approver is
237: invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
238: See bug 3286313.
239: */
256: origSystemOut := 'PER';
257: end;
258: end getApproverDetails;
259: /*
260: This function returns the displayname for the given wf_roles name.If the name is not valid,then
261: it returns the string 'Inactive:
262: returns the string 'Invalid:
263: */
264: function getApproverDisplayName3(nameIn in varchar2) return varchar2 as
264: function getApproverDisplayName3(nameIn in varchar2) return varchar2 as
265: validityOut varchar2(100);
266: displayNameOut ame_util.longestStringType;
267: origSystemIdOut integer;
268: origSystemOut wf_roles.orig_system%type;
269: begin
270: getApproverDetails(nameIn => nameIn
271: ,validityOut => validityOut
272: ,displayNameOut => displayNameOut
279: attributeCodeIn => 'AME_INVALID_COLON') || getOrigSystemDisplayName(origSystemIn => origSystemOut) || ':' || displayNameOut);
280: end if;
281: end getApproverDisplayName3;
282: /*
283: This function returns the displayname for the given wf_roles name.If the name is not valid,then
284: it returns the string 'Invalid :
285: */
286: function getApproverDisplayName4(nameIn in varchar2) return varchar2 as
287: validityOut varchar2(100);
286: function getApproverDisplayName4(nameIn in varchar2) return varchar2 as
287: validityOut varchar2(100);
288: displayNameOut ame_util.longestStringType;
289: origSystemIdOut integer;
290: origSystemOut wf_roles.orig_system%type;
291: begin
292: getApproverDetails(nameIn => nameIn
293: ,validityOut => validityOut
294: ,displayNameOut => displayNameOut
309: end getApproverDisplayName4;
310: function getApproverOrigSystem(nameIn in varchar2) return varchar2 as
311: errorCode integer;
312: errorMessage ame_util.longestStringType;
313: origSystem wf_roles.orig_system%type;
314: begin
315: select orig_system
316: into origSystem
317: from wf_roles
313: origSystem wf_roles.orig_system%type;
314: begin
315: select orig_system
316: into origSystem
317: from wf_roles
318: where
319: name = nameIn and
320: status = 'ACTIVE' and
321: (expiration_date is null or
346: end getApproverOrigSystem;
347: function getApproverOrigSystem2(nameIn in varchar2) return varchar2 as
348: errorCode integer;
349: errorMessage ame_util.longestStringType;
350: origSystem wf_roles.orig_system%type;
351: begin
352: select orig_system
353: into origSystem
354: from wf_roles
350: origSystem wf_roles.orig_system%type;
351: begin
352: select orig_system
353: into origSystem
354: from wf_roles
355: where
356: name = nameIn and
357: status = 'ACTIVE' and
358: (expiration_date is null or
362: exception
363: when no_data_found then
364: begin
365: /*
366: When the approver is not valid in WF_ROLES, try to get the approver info
367: from WF_LOCAL_ROLES. (It may still be there, even though the approver is
368: invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
369: See bug 3286313.
370: */
385: end getApproverOrigSystem2;
386: function getApproverOrigSystemId(nameIn in varchar2) return varchar2 as
387: errorCode integer;
388: errorMessage ame_util.longestStringType;
389: origSystemId wf_roles.orig_system_id%type;
390: begin
391: select orig_system_id
392: into origSystemId
393: from wf_roles
389: origSystemId wf_roles.orig_system_id%type;
390: begin
391: select orig_system_id
392: into origSystemId
393: from wf_roles
394: where
395: name = nameIn and
396: status = 'ACTIVE' and
397: (expiration_date is null or
535: origSystemIdIn in integer,
536: raiseNoDataFoundIn in varchar2 default 'true') return varchar2 as
537: errorCode integer;
538: errorMessage ame_util.longestStringType;
539: name wf_roles.name%type;
540: begin
541: /*
542: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
543: converted to the PER originating system in wf_roles; otherwise, it gets
539: name wf_roles.name%type;
540: begin
541: /*
542: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
543: converted to the PER originating system in wf_roles; otherwise, it gets
544: converted to the FND_USR originating system. As just one of these will
545: happen, we can match both originating systems in a single-row query.
546: The order-by-name clause and rownum < 2 condition are necessary because we
547: have encountered data problems where there are several entries for a given
548: orig_system and orig_system_id pair.
549: */
550: select name
551: into name
552: from wf_roles
553: where
554: ((orig_system = origSystemIn and
555: orig_system_id = origSystemIdIn) or
556: (origSystemIn = ame_util.fndUserOrigSystem and
567: sysdate < expiration_date) and
568: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
569: or exists (select null
570: from fnd_user u
571: where u.user_name = wf_roles.name
572: and trunc(sysdate) between u.start_date
573: and nvl(u.end_date,trunc(sysdate)))) and
574: not exists (
575: select null from wf_roles wf2
571: where u.user_name = wf_roles.name
572: and trunc(sysdate) between u.start_date
573: and nvl(u.end_date,trunc(sysdate)))) and
574: not exists (
575: select null from wf_roles wf2
576: where wf_roles.orig_system = wf2.orig_system
577: and wf_roles.orig_system_id = wf2.orig_system_id
578: and wf_roles.start_date > wf2.start_date
579: ) and
572: and trunc(sysdate) between u.start_date
573: and nvl(u.end_date,trunc(sysdate)))) and
574: not exists (
575: select null from wf_roles wf2
576: where wf_roles.orig_system = wf2.orig_system
577: and wf_roles.orig_system_id = wf2.orig_system_id
578: and wf_roles.start_date > wf2.start_date
579: ) and
580: rownum < 2;
573: and nvl(u.end_date,trunc(sysdate)))) and
574: not exists (
575: select null from wf_roles wf2
576: where wf_roles.orig_system = wf2.orig_system
577: and wf_roles.orig_system_id = wf2.orig_system_id
578: and wf_roles.start_date > wf2.start_date
579: ) and
580: rownum < 2;
581: return(name);
574: not exists (
575: select null from wf_roles wf2
576: where wf_roles.orig_system = wf2.orig_system
577: and wf_roles.orig_system_id = wf2.orig_system_id
578: and wf_roles.start_date > wf2.start_date
579: ) and
580: rownum < 2;
581: return(name);
582: exception
728: rowCount integer;
729: begin
730: select count(*)
731: into rowCount
732: from wf_roles
733: where
734: name = nameIn and
735: status = 'ACTIVE' and
736: (expiration_date is null or
778: getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
779: getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
780: from
781: fnd_user,
782: wf_roles
783: where
784: wf_roles.orig_system_id = fnd_user.user_id and
785: wf_roles.orig_system = ame_util.fndUserOrigSystem and
786: wf_roles.status = 'ACTIVE' and
780: from
781: fnd_user,
782: wf_roles
783: where
784: wf_roles.orig_system_id = fnd_user.user_id and
785: wf_roles.orig_system = ame_util.fndUserOrigSystem and
786: wf_roles.status = 'ACTIVE' and
787: wf_roles.name = fnd_user.user_name and
788: (userNameIn is null or
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
782: wf_roles
783: where
784: wf_roles.orig_system_id = fnd_user.user_id and
785: wf_roles.orig_system = ame_util.fndUserOrigSystem and
786: wf_roles.status = 'ACTIVE' and
787: wf_roles.name = fnd_user.user_name and
788: (userNameIn is null or
789: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
790: (emailAddressIn is null or
783: where
784: wf_roles.orig_system_id = fnd_user.user_id and
785: wf_roles.orig_system = ame_util.fndUserOrigSystem and
786: wf_roles.status = 'ACTIVE' and
787: wf_roles.name = fnd_user.user_name and
788: (userNameIn is null or
789: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
790: (emailAddressIn is null or
791: upper(fnd_user.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%') and
990: end fndRespApproverQuery;
991: procedure getApproverDescAndValidity(nameIn in varchar2,
992: descriptionOut out nocopy varchar2,
993: validityOut out nocopy boolean) as
994: origSystem wf_roles.orig_system%type;
995: begin
996: /*
997: This function needs to return the same data for all approver types. Do NOT modify this
998: function to return extra data for specific approver types. This function should return
1005: orig_system
1006: into
1007: descriptionOut,
1008: origSystem
1009: from wf_roles
1010: where
1011: name = nameIn and
1012: status = 'ACTIVE' and
1013: (expiration_date is null or
1017: exception
1018: when no_data_found then
1019: begin
1020: /*
1021: When the approver is not valid in WF_ROLES, try to get the approver info
1022: from WF_LOCAL_ROLES. (It may still be there, even though the approver is
1023: invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
1024: See bug 3286313.
1025: */
1078: orig_system_id
1079: into
1080: origSystemOut,
1081: origSystemIdOut
1082: from wf_roles
1083: where
1084: name = nameIn and
1085: status = 'ACTIVE' and
1086: (expiration_date is null or
1368: into
1369: origSystemOut,
1370: origSystemIdOut,
1371: displayNameOut
1372: from wf_roles
1373: where
1374: name = nameIn and
1375: status = 'ACTIVE' and
1376: (expiration_date is null or
1407: this procedure will likely be revised after the 11.5.10 release.
1408: */
1409: procedure getSuperior(approverIn in ame_util.approverRecord2,
1410: superiorOut out nocopy ame_util.approverRecord2) is
1411: approverName wf_roles.display_name%type;
1412: errorCode integer;
1413: errorMessage ame_util.longestStringType;
1414: positionStructureId integer;
1415: noSurrogateException exception;
1418: if(approverIn.orig_system = ame_util.perOrigSystem) then
1419: superiorOut.orig_system := ame_util.perOrigSystem;
1420: select
1421: per_all_assignments_f.supervisor_id,
1422: wf_roles.name,
1423: wf_roles.display_name
1424: into
1425: superiorOut.orig_system_id,
1426: superiorOut.name,
1419: superiorOut.orig_system := ame_util.perOrigSystem;
1420: select
1421: per_all_assignments_f.supervisor_id,
1422: wf_roles.name,
1423: wf_roles.display_name
1424: into
1425: superiorOut.orig_system_id,
1426: superiorOut.name,
1427: superiorOut.display_name
1425: superiorOut.orig_system_id,
1426: superiorOut.name,
1427: superiorOut.display_name
1428: from
1429: wf_roles,
1430: per_all_assignments_f
1431: where
1432: wf_roles.orig_system = ame_util.perOrigSystem and
1433: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
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
1429: wf_roles,
1430: per_all_assignments_f
1431: where
1432: wf_roles.orig_system = ame_util.perOrigSystem and
1433: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1434: wf_roles.status = 'ACTIVE' and
1435: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1436: exists (select null
1437: from fnd_user u
1430: per_all_assignments_f
1431: where
1432: wf_roles.orig_system = ame_util.perOrigSystem and
1433: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1434: wf_roles.status = 'ACTIVE' and
1435: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1436: exists (select null
1437: from fnd_user u
1438: where u.user_name = wf_roles.name
1431: where
1432: wf_roles.orig_system = ame_util.perOrigSystem and
1433: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1434: wf_roles.status = 'ACTIVE' and
1435: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1436: exists (select null
1437: from fnd_user u
1438: where u.user_name = wf_roles.name
1439: and trunc(sysdate) between u.start_date
1434: wf_roles.status = 'ACTIVE' and
1435: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1436: exists (select null
1437: from fnd_user u
1438: where u.user_name = wf_roles.name
1439: and trunc(sysdate) between u.start_date
1440: and nvl(u.end_date,trunc(sysdate))) and
1441: not exists (
1442: select null from wf_roles wf2
1438: where u.user_name = wf_roles.name
1439: and trunc(sysdate) between u.start_date
1440: and nvl(u.end_date,trunc(sysdate))) and
1441: not exists (
1442: select null from wf_roles wf2
1443: where wf_roles.orig_system = wf2.orig_system
1444: and wf_roles.orig_system_id = wf2.orig_system_id
1445: and wf_roles.start_date > wf2.start_date
1446: ) and
1439: and trunc(sysdate) between u.start_date
1440: and nvl(u.end_date,trunc(sysdate))) and
1441: not exists (
1442: select null from wf_roles wf2
1443: where wf_roles.orig_system = wf2.orig_system
1444: and wf_roles.orig_system_id = wf2.orig_system_id
1445: and wf_roles.start_date > wf2.start_date
1446: ) and
1447: per_all_assignments_f.person_id =approverIn.orig_system_id and
1440: and nvl(u.end_date,trunc(sysdate))) and
1441: not exists (
1442: select null from wf_roles wf2
1443: where wf_roles.orig_system = wf2.orig_system
1444: and wf_roles.orig_system_id = wf2.orig_system_id
1445: and wf_roles.start_date > wf2.start_date
1446: ) and
1447: per_all_assignments_f.person_id =approverIn.orig_system_id and
1448: per_all_assignments_f.primary_flag = 'Y' and
1441: not exists (
1442: select null from wf_roles wf2
1443: where wf_roles.orig_system = wf2.orig_system
1444: and wf_roles.orig_system_id = wf2.orig_system_id
1445: and wf_roles.start_date > wf2.start_date
1446: ) and
1447: per_all_assignments_f.person_id =approverIn.orig_system_id and
1448: per_all_assignments_f.primary_flag = 'Y' and
1449: per_all_assignments_f.assignment_type in ('E','C') and
1461: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
1462: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1463: select
1464: per_all_assignments_f.supervisor_id,
1465: wf_roles.name,
1466: wf_roles.display_name
1467: into
1468: superiorOut.orig_system_id,
1469: superiorOut.name,
1462: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1463: select
1464: per_all_assignments_f.supervisor_id,
1465: wf_roles.name,
1466: wf_roles.display_name
1467: into
1468: superiorOut.orig_system_id,
1469: superiorOut.name,
1470: superiorOut.display_name
1468: superiorOut.orig_system_id,
1469: superiorOut.name,
1470: superiorOut.display_name
1471: from
1472: wf_roles,
1473: per_all_assignments_f
1474: where
1475: wf_roles.orig_system = ame_util.perOrigSystem and
1476: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
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
1472: wf_roles,
1473: per_all_assignments_f
1474: where
1475: wf_roles.orig_system = ame_util.perOrigSystem and
1476: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1477: wf_roles.status = 'ACTIVE' and
1478: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1479: exists (select null
1480: from fnd_user u
1473: per_all_assignments_f
1474: where
1475: wf_roles.orig_system = ame_util.perOrigSystem and
1476: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1477: wf_roles.status = 'ACTIVE' and
1478: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1479: exists (select null
1480: from fnd_user u
1481: where u.user_name = wf_roles.name
1474: where
1475: wf_roles.orig_system = ame_util.perOrigSystem and
1476: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1477: wf_roles.status = 'ACTIVE' and
1478: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1479: exists (select null
1480: from fnd_user u
1481: where u.user_name = wf_roles.name
1482: and trunc(sysdate) between u.start_date
1477: wf_roles.status = 'ACTIVE' and
1478: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1479: exists (select null
1480: from fnd_user u
1481: where u.user_name = wf_roles.name
1482: and trunc(sysdate) between u.start_date
1483: and nvl(u.end_date,trunc(sysdate))) and
1484: per_all_assignments_f.person_id =
1485: (select employee_id
1496: trunc(sysdate) between
1497: per_all_assignments_f.effective_start_date and
1498: per_all_assignments_f.effective_end_date and
1499: rownum < 2
1500: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1501: if(superiorOut.orig_system_id is null) then
1502: raise noSurrogateException;
1503: end if;
1504: elsif(approverIn.orig_system = ame_util.posOrigSystem) then
1506: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1507: if (positionStructureId is null) then
1508: select
1509: str.parent_position_id,
1510: wf_roles.name,
1511: wf_roles.display_name
1512: into
1513: superiorOut.orig_system_id,
1514: superiorOut.name,
1507: if (positionStructureId is null) then
1508: select
1509: str.parent_position_id,
1510: wf_roles.name,
1511: wf_roles.display_name
1512: into
1513: superiorOut.orig_system_id,
1514: superiorOut.name,
1515: superiorOut.display_name
1516: from
1517: per_pos_structure_elements str,
1518: per_pos_structure_versions psv,
1519: per_position_structures pst,
1520: wf_roles
1521: where
1522: str.subordinate_position_id = approverIn.orig_system_id and
1523: str.business_group_id =
1524: nvl(hr_general.get_business_group_id,str.business_group_id) and
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
1525: str.pos_structure_version_id = psv.pos_structure_version_id and
1526: pst.position_structure_id = psv.position_structure_id and
1527: pst.primary_position_flag = 'Y' and
1528: wf_roles.orig_system = ame_util.posOrigSystem and
1529: wf_roles.orig_system_id = str.parent_position_id and
1530: wf_roles.status = 'ACTIVE' and
1531: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1532: trunc(sysdate) between
1533: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1526: pst.position_structure_id = psv.position_structure_id and
1527: pst.primary_position_flag = 'Y' and
1528: wf_roles.orig_system = ame_util.posOrigSystem and
1529: wf_roles.orig_system_id = str.parent_position_id and
1530: wf_roles.status = 'ACTIVE' and
1531: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1532: trunc(sysdate) between
1533: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1534: rownum < 2
1527: pst.primary_position_flag = 'Y' and
1528: wf_roles.orig_system = ame_util.posOrigSystem and
1529: wf_roles.orig_system_id = str.parent_position_id and
1530: wf_roles.status = 'ACTIVE' and
1531: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1532: trunc(sysdate) between
1533: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1534: rownum < 2
1535: order by wf_roles.name;
1531: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1532: trunc(sysdate) between
1533: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1534: rownum < 2
1535: order by wf_roles.name;
1536: else
1537: select
1538: str.parent_position_id,
1539: wf_roles.name,
1535: order by wf_roles.name;
1536: else
1537: select
1538: str.parent_position_id,
1539: wf_roles.name,
1540: wf_roles.display_name
1541: into
1542: superiorOut.orig_system_id,
1543: superiorOut.name,
1536: else
1537: select
1538: str.parent_position_id,
1539: wf_roles.name,
1540: wf_roles.display_name
1541: into
1542: superiorOut.orig_system_id,
1543: superiorOut.name,
1544: superiorOut.display_name
1545: from
1546: per_pos_structure_elements str,
1547: per_pos_structure_versions psv,
1548: per_position_structures pst,
1549: wf_roles
1550: where
1551: str.subordinate_position_id = approverIn.orig_system_id and
1552: str.pos_structure_version_id = psv.pos_structure_version_id and
1553: pst.position_structure_id = positionStructureId and
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
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
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
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;
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
1603: origSystemIdIn in integer,
1604: origSystemIdOut out nocopy integer,
1605: wfRolesNameOut out nocopy varchar2,
1606: displayNameOut out nocopy varchar2) as
1607: approverName wf_roles.display_name%type;
1608: errorCode integer;
1609: errorMessage ame_util.longestStringType;
1610: positionStructureId integer;
1611: noSurrogateException exception;
1613: begin
1614: if(origSystemIn = ame_util.perOrigSystem) then
1615: select
1616: per_all_assignments_f.supervisor_id,
1617: wf_roles.name,
1618: wf_roles.display_name
1619: into
1620: origSystemIdOut,
1621: wfRolesNameOut,
1614: if(origSystemIn = ame_util.perOrigSystem) then
1615: select
1616: per_all_assignments_f.supervisor_id,
1617: wf_roles.name,
1618: wf_roles.display_name
1619: into
1620: origSystemIdOut,
1621: wfRolesNameOut,
1622: displayNameOut
1620: origSystemIdOut,
1621: wfRolesNameOut,
1622: displayNameOut
1623: from
1624: wf_roles,
1625: per_all_assignments_f
1626: where
1627: wf_roles.orig_system = ame_util.perOrigSystem and
1628: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
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
1624: wf_roles,
1625: per_all_assignments_f
1626: where
1627: wf_roles.orig_system = ame_util.perOrigSystem and
1628: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1629: wf_roles.status = 'ACTIVE' and
1630: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1631: exists (select null
1632: from fnd_user u
1625: per_all_assignments_f
1626: where
1627: wf_roles.orig_system = ame_util.perOrigSystem and
1628: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1629: wf_roles.status = 'ACTIVE' and
1630: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1631: exists (select null
1632: from fnd_user u
1633: where u.user_name = wf_roles.name
1626: where
1627: wf_roles.orig_system = ame_util.perOrigSystem and
1628: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1629: wf_roles.status = 'ACTIVE' and
1630: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1631: exists (select null
1632: from fnd_user u
1633: where u.user_name = wf_roles.name
1634: and trunc(sysdate) between u.start_date
1629: wf_roles.status = 'ACTIVE' and
1630: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1631: exists (select null
1632: from fnd_user u
1633: where u.user_name = wf_roles.name
1634: and trunc(sysdate) between u.start_date
1635: and nvl(u.end_date,trunc(sysdate))) and
1636: not exists (
1637: select null from wf_roles wf2
1633: where u.user_name = wf_roles.name
1634: and trunc(sysdate) between u.start_date
1635: and nvl(u.end_date,trunc(sysdate))) and
1636: not exists (
1637: select null from wf_roles wf2
1638: where wf_roles.orig_system = wf2.orig_system
1639: and wf_roles.orig_system_id = wf2.orig_system_id
1640: and wf_roles.start_date > wf2.start_date
1641: ) and
1634: and trunc(sysdate) between u.start_date
1635: and nvl(u.end_date,trunc(sysdate))) and
1636: not exists (
1637: select null from wf_roles wf2
1638: where wf_roles.orig_system = wf2.orig_system
1639: and wf_roles.orig_system_id = wf2.orig_system_id
1640: and wf_roles.start_date > wf2.start_date
1641: ) and
1642: per_all_assignments_f.person_id = origSystemIdIn and
1635: and nvl(u.end_date,trunc(sysdate))) and
1636: not exists (
1637: select null from wf_roles wf2
1638: where wf_roles.orig_system = wf2.orig_system
1639: and wf_roles.orig_system_id = wf2.orig_system_id
1640: and wf_roles.start_date > wf2.start_date
1641: ) and
1642: per_all_assignments_f.person_id = origSystemIdIn and
1643: per_all_assignments_f.primary_flag = 'Y' and
1636: not exists (
1637: select null from wf_roles wf2
1638: where wf_roles.orig_system = wf2.orig_system
1639: and wf_roles.orig_system_id = wf2.orig_system_id
1640: and wf_roles.start_date > wf2.start_date
1641: ) and
1642: per_all_assignments_f.person_id = origSystemIdIn and
1643: per_all_assignments_f.primary_flag = 'Y' and
1644: per_all_assignments_f.assignment_type in ('E','C') and
1655: end if;
1656: elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1657: select
1658: per_all_assignments_f.supervisor_id,
1659: wf_roles.name,
1660: wf_roles.display_name
1661: into
1662: origSystemIdOut,
1663: wfRolesNameOut,
1656: elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1657: select
1658: per_all_assignments_f.supervisor_id,
1659: wf_roles.name,
1660: wf_roles.display_name
1661: into
1662: origSystemIdOut,
1663: wfRolesNameOut,
1664: displayNameOut
1662: origSystemIdOut,
1663: wfRolesNameOut,
1664: displayNameOut
1665: from
1666: wf_roles,
1667: per_all_assignments_f
1668: where
1669: wf_roles.orig_system = ame_util.perOrigSystem and
1670: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
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
1666: wf_roles,
1667: per_all_assignments_f
1668: where
1669: wf_roles.orig_system = ame_util.perOrigSystem and
1670: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1671: wf_roles.status = 'ACTIVE' and
1672: exists (select null
1673: from fnd_user u
1674: where u.user_name = wf_roles.name
1667: per_all_assignments_f
1668: where
1669: wf_roles.orig_system = ame_util.perOrigSystem and
1670: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1671: wf_roles.status = 'ACTIVE' and
1672: exists (select null
1673: from fnd_user u
1674: where u.user_name = wf_roles.name
1675: and trunc(sysdate) between u.start_date
1670: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1671: wf_roles.status = 'ACTIVE' and
1672: exists (select null
1673: from fnd_user u
1674: where u.user_name = wf_roles.name
1675: and trunc(sysdate) between u.start_date
1676: and nvl(u.end_date,trunc(sysdate))) and
1677: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1678: per_all_assignments_f.person_id =
1673: from fnd_user u
1674: where u.user_name = wf_roles.name
1675: and trunc(sysdate) between u.start_date
1676: and nvl(u.end_date,trunc(sysdate))) and
1677: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1678: per_all_assignments_f.person_id =
1679: (select employee_id
1680: from fnd_user
1681: where
1690: trunc(sysdate) between
1691: per_all_assignments_f.effective_start_date and
1692: per_all_assignments_f.effective_end_date and
1693: rownum < 2
1694: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1695: if(origSystemIdOut is null) then
1696: raise noSurrogateException;
1697: end if;
1698: elsif(origSystemIn = ame_util.posOrigSystem) then
1699: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1700: if (positionStructureId is null) then
1701: select
1702: str.parent_position_id,
1703: wf_roles.name,
1704: wf_roles.display_name
1705: into
1706: origSystemIdOut,
1707: wfRolesNameOut,
1700: if (positionStructureId is null) then
1701: select
1702: str.parent_position_id,
1703: wf_roles.name,
1704: wf_roles.display_name
1705: into
1706: origSystemIdOut,
1707: wfRolesNameOut,
1708: displayNameOut
1709: from
1710: per_pos_structure_elements str,
1711: per_pos_structure_versions psv,
1712: per_position_structures pst,
1713: wf_roles
1714: where
1715: str.subordinate_position_id = origSystemIdIn and
1716: str.business_group_id =
1717: nvl(hr_general.get_business_group_id,str.business_group_id) and
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
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
1726: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1719: pst.position_structure_id = psv.position_structure_id and
1720: pst.primary_position_flag = 'Y' and
1721: wf_roles.orig_system = ame_util.posOrigSystem and
1722: wf_roles.orig_system_id = str.parent_position_id and
1723: wf_roles.status = 'ACTIVE' and
1724: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1725: trunc(sysdate) between
1726: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1727: rownum < 2
1720: pst.primary_position_flag = 'Y' and
1721: wf_roles.orig_system = ame_util.posOrigSystem and
1722: wf_roles.orig_system_id = str.parent_position_id and
1723: wf_roles.status = 'ACTIVE' and
1724: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1725: trunc(sysdate) between
1726: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1727: rownum < 2
1728: order by wf_roles.name;
1724: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1725: trunc(sysdate) between
1726: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1727: rownum < 2
1728: order by wf_roles.name;
1729: else
1730: select
1731: str.parent_position_id,
1732: wf_roles.name,
1728: order by wf_roles.name;
1729: else
1730: select
1731: str.parent_position_id,
1732: wf_roles.name,
1733: wf_roles.display_name
1734: into
1735: origSystemIdOut,
1736: wfRolesNameOut,
1729: else
1730: select
1731: str.parent_position_id,
1732: wf_roles.name,
1733: wf_roles.display_name
1734: into
1735: origSystemIdOut,
1736: wfRolesNameOut,
1737: displayNameOut
1738: from
1739: per_pos_structure_elements str,
1740: per_pos_structure_versions psv,
1741: per_position_structures pst,
1742: wf_roles
1743: where
1744: str.subordinate_position_id = origSystemIdIn and
1745: str.pos_structure_version_id = psv.pos_structure_version_id and
1746: pst.position_structure_id = positionStructureId and
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
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
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
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;
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
1794: errorCode integer;
1795: errorMessage ame_util.longestStringType;
1796: begin
1797: /*
1798: This procedure should select the input approver's wf_roles.display_name, NOT the
1799: display name of the input approver's orig_system.
1800: */
1801: /*
1802: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
1799: display name of the input approver's orig_system.
1800: */
1801: /*
1802: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
1803: converted to the PER originating system in wf_roles; otherwise, it gets
1804: converted to the FND_USR originating system. As just one of these will
1805: happen, we can match both originating systems in a single-row query.
1806: The order-by-name clause and rownum < 2 condition are necessary because we
1807: have encountered data problems where there are several entries for a given
1812: display_name
1813: into
1814: nameOut,
1815: displayNameOut
1816: from wf_roles
1817: where
1818: ((orig_system = origSystemIn and
1819: orig_system_id = origSystemIdIn) or
1820: (origSystemIn = ame_util.fndUserOrigSystem and
1831: sysdate < expiration_date) and
1832: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
1833: or exists (select null
1834: from fnd_user u
1835: where u.user_name = wf_roles.name
1836: and trunc(sysdate) between u.start_date
1837: and nvl(u.end_date,trunc(sysdate)))) and
1838: not exists (
1839: select null from wf_roles wf2
1835: where u.user_name = wf_roles.name
1836: and trunc(sysdate) between u.start_date
1837: and nvl(u.end_date,trunc(sysdate)))) and
1838: not exists (
1839: select null from wf_roles wf2
1840: where wf_roles.orig_system = wf2.orig_system
1841: and wf_roles.orig_system_id = wf2.orig_system_id
1842: and wf_roles.start_date > wf2.start_date
1843: ) and
1836: and trunc(sysdate) between u.start_date
1837: and nvl(u.end_date,trunc(sysdate)))) and
1838: not exists (
1839: select null from wf_roles wf2
1840: where wf_roles.orig_system = wf2.orig_system
1841: and wf_roles.orig_system_id = wf2.orig_system_id
1842: and wf_roles.start_date > wf2.start_date
1843: ) and
1844: rownum < 2;
1837: and nvl(u.end_date,trunc(sysdate)))) and
1838: not exists (
1839: select null from wf_roles wf2
1840: where wf_roles.orig_system = wf2.orig_system
1841: and wf_roles.orig_system_id = wf2.orig_system_id
1842: and wf_roles.start_date > wf2.start_date
1843: ) and
1844: rownum < 2;
1845: exception
1838: not exists (
1839: select null from wf_roles wf2
1840: where wf_roles.orig_system = wf2.orig_system
1841: and wf_roles.orig_system_id = wf2.orig_system_id
1842: and wf_roles.start_date > wf2.start_date
1843: ) and
1844: rownum < 2;
1845: exception
1846: when no_data_found then
1949: ,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
1950: from
1951: per_all_people_f pap
1952: ,hr_all_organization_units haou
1953: ,wf_roles wfr
1954: ,per_all_assignments_f pas
1955: where pap.person_id = pas.person_id
1956: and pas.primary_flag = 'Y'
1957: and pas.assignment_type in ('E','C')
2076: getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
2077: from
2078: per_positions,
2079: hr_organization_units,
2080: wf_roles
2081: where
2082: wf_roles.orig_system_id = per_positions.position_id and
2083: wf_roles.orig_system = ame_util.posOrigSystem and
2084: wf_roles.status = 'ACTIVE' and
2078: per_positions,
2079: hr_organization_units,
2080: wf_roles
2081: where
2082: wf_roles.orig_system_id = per_positions.position_id and
2083: wf_roles.orig_system = ame_util.posOrigSystem and
2084: wf_roles.status = 'ACTIVE' and
2085: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2086: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
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
2080: wf_roles
2081: where
2082: wf_roles.orig_system_id = per_positions.position_id and
2083: wf_roles.orig_system = ame_util.posOrigSystem and
2084: wf_roles.status = 'ACTIVE' and
2085: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2086: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
2087: per_positions.business_group_id = hr_organization_units.organization_id and
2088: truncatedSysdateIn between