eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AME_APPROVER_TYPE_PKG dependencies on WF_ROLES

Line 11: the wf_roles display name is returned.

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);

Line 32: name wf_roles.display_name%type;

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);

Line 92: displayName wf_roles.display_name%type;

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

Line 98: from wf_roles

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

Line 132: displayName wf_roles.display_name%type;

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: /*

Line 138: converted to the PER originating system in wf_roles; otherwise, it gets

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

Line 147: from wf_roles

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

Line 166: where u.user_name = wf_roles.name

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

Line 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

Line 171: where wf_roles.orig_system = wf2.orig_system

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;

Line 172: and wf_roles.orig_system_id = wf2.orig_system_id

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);

Line 173: and wf_roles.start_date > wf2.start_date

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

Line 204: If the name is valid wf_roles name, validityOut is returned as true.If the name is not found,

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

Line 223: from wf_roles

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

Line 235: When the approver is not valid in WF_ROLES, try to get the approver info

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: */

Line 260: This function returns the displayname for the given wf_roles name.If the name is not valid,then

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: '.If the display name is not available, then it
262: returns the string 'Invalid: '.
263: */
264: function getApproverDisplayName3(nameIn in varchar2) return varchar2 as

Line 268: origSystemOut wf_roles.orig_system%type;

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

Line 283: This function returns the displayname for the given wf_roles name.If the name is not valid,then

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);

Line 290: origSystemOut wf_roles.orig_system%type;

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

Line 313: origSystem wf_roles.orig_system%type;

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

Line 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

Line 350: origSystem wf_roles.orig_system%type;

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

Line 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

Line 366: When the approver is not valid in WF_ROLES, try to get the approver info

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: */

Line 389: origSystem wf_roles.orig_system%type;

385: end getApproverOrigSystem2;
386: function getApproverOrigSystem3(nameIn in varchar2) return varchar2 as
387: errorCode integer;
388: errorMessage ame_util.longestStringType;
389: origSystem wf_roles.orig_system%type;
390: begin
391: select orig_system
392: into origSystem
393: from wf_roles

Line 393: from wf_roles

389: origSystem wf_roles.orig_system%type;
390: begin
391: select orig_system
392: into origSystem
393: from wf_roles
394: where
395: name = nameIn and
396: status = 'ACTIVE' and
397: (expiration_date is null or

Line 405: When the approver is not valid in WF_ROLES, try to get the approver info

401: exception
402: when no_data_found then
403: begin
404: /*
405: When the approver is not valid in WF_ROLES, try to get the approver info
406: from WF_LOCAL_ROLES. (It may still be there, even though the approver is
407: invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
408: See bug 3286313.
409: */

Line 428: origSystemId wf_roles.orig_system_id%type;

424: end getApproverOrigSystem3;
425: function getApproverOrigSystemId(nameIn in varchar2) return varchar2 as
426: errorCode integer;
427: errorMessage ame_util.longestStringType;
428: origSystemId wf_roles.orig_system_id%type;
429: begin
430: select orig_system_id
431: into origSystemId
432: from wf_roles

Line 432: from wf_roles

428: origSystemId wf_roles.orig_system_id%type;
429: begin
430: select orig_system_id
431: into origSystemId
432: from wf_roles
433: where
434: name = nameIn and
435: status = 'ACTIVE' and
436: (expiration_date is null or

Line 578: name wf_roles.name%type;

574: origSystemIdIn in integer,
575: raiseNoDataFoundIn in varchar2 default 'true') return varchar2 as
576: errorCode integer;
577: errorMessage ame_util.longestStringType;
578: name wf_roles.name%type;
579: begin
580: /*
581: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
582: converted to the PER originating system in wf_roles; otherwise, it gets

Line 582: converted to the PER originating system in wf_roles; otherwise, it gets

578: name wf_roles.name%type;
579: begin
580: /*
581: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
582: converted to the PER originating system in wf_roles; otherwise, it gets
583: converted to the FND_USR originating system. As just one of these will
584: happen, we can match both originating systems in a single-row query.
585: The order-by-name clause and rownum < 2 condition are necessary because we
586: have encountered data problems where there are several entries for a given

Line 591: from wf_roles

587: orig_system and orig_system_id pair.
588: */
589: select name
590: into name
591: from wf_roles
592: where
593: ((orig_system = origSystemIn and
594: orig_system_id = origSystemIdIn) or
595: (origSystemIn = ame_util.fndUserOrigSystem and

Line 610: where u.user_name = wf_roles.name

606: sysdate < expiration_date) and
607: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
608: or exists (select null
609: from fnd_user u
610: where u.user_name = wf_roles.name
611: and trunc(sysdate) between u.start_date
612: and nvl(u.end_date,trunc(sysdate)))) and
613: not exists (
614: select null from wf_roles wf2

Line 614: select null from wf_roles wf2

610: where u.user_name = wf_roles.name
611: and trunc(sysdate) between u.start_date
612: and nvl(u.end_date,trunc(sysdate)))) and
613: not exists (
614: select null from wf_roles wf2
615: where wf_roles.orig_system = wf2.orig_system
616: and wf_roles.orig_system_id = wf2.orig_system_id
617: and wf_roles.start_date > wf2.start_date
618: ) and

Line 615: where wf_roles.orig_system = wf2.orig_system

611: and trunc(sysdate) between u.start_date
612: and nvl(u.end_date,trunc(sysdate)))) and
613: not exists (
614: select null from wf_roles wf2
615: where wf_roles.orig_system = wf2.orig_system
616: and wf_roles.orig_system_id = wf2.orig_system_id
617: and wf_roles.start_date > wf2.start_date
618: ) and
619: rownum < 2;

Line 616: and wf_roles.orig_system_id = wf2.orig_system_id

612: and nvl(u.end_date,trunc(sysdate)))) and
613: not exists (
614: select null from wf_roles wf2
615: where wf_roles.orig_system = wf2.orig_system
616: and wf_roles.orig_system_id = wf2.orig_system_id
617: and wf_roles.start_date > wf2.start_date
618: ) and
619: rownum < 2;
620: return(name);

Line 617: and wf_roles.start_date > wf2.start_date

613: not exists (
614: select null from wf_roles wf2
615: where wf_roles.orig_system = wf2.orig_system
616: and wf_roles.orig_system_id = wf2.orig_system_id
617: and wf_roles.start_date > wf2.start_date
618: ) and
619: rownum < 2;
620: return(name);
621: exception

Line 776: from wf_roles

772: rowCount integer;
773: begin
774: select count(*)
775: into rowCount
776: from wf_roles
777: where
778: name = nameIn and
779: status = 'ACTIVE' and
780: (expiration_date is null or

Line 826: wf_roles

822: getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
823: getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
824: from
825: fnd_user,
826: wf_roles
827: where
828: wf_roles.orig_system_id = fnd_user.user_id and
829: wf_roles.orig_system = ame_util.fndUserOrigSystem and
830: wf_roles.status = 'ACTIVE' and

Line 828: wf_roles.orig_system_id = fnd_user.user_id and

824: from
825: fnd_user,
826: wf_roles
827: where
828: wf_roles.orig_system_id = fnd_user.user_id and
829: wf_roles.orig_system = ame_util.fndUserOrigSystem and
830: wf_roles.status = 'ACTIVE' and
831: wf_roles.name = fnd_user.user_name and
832: (userNameIn is null or

Line 829: wf_roles.orig_system = ame_util.fndUserOrigSystem and

825: fnd_user,
826: wf_roles
827: where
828: wf_roles.orig_system_id = fnd_user.user_id and
829: wf_roles.orig_system = ame_util.fndUserOrigSystem and
830: wf_roles.status = 'ACTIVE' and
831: wf_roles.name = fnd_user.user_name and
832: (userNameIn is null or
833: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and

Line 830: wf_roles.status = 'ACTIVE' and

826: wf_roles
827: where
828: wf_roles.orig_system_id = fnd_user.user_id and
829: wf_roles.orig_system = ame_util.fndUserOrigSystem and
830: wf_roles.status = 'ACTIVE' and
831: wf_roles.name = fnd_user.user_name and
832: (userNameIn is null or
833: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
834: (emailAddressIn is null or

Line 831: wf_roles.name = fnd_user.user_name and

827: where
828: wf_roles.orig_system_id = fnd_user.user_id and
829: wf_roles.orig_system = ame_util.fndUserOrigSystem and
830: wf_roles.status = 'ACTIVE' and
831: wf_roles.name = fnd_user.user_name and
832: (userNameIn is null or
833: upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
834: (emailAddressIn is null or
835: upper(fnd_user.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%') and

Line 1038: origSystem wf_roles.orig_system%type;

1034: end fndRespApproverQuery;
1035: procedure getApproverDescAndValidity(nameIn in varchar2,
1036: descriptionOut out nocopy varchar2,
1037: validityOut out nocopy boolean) as
1038: origSystem wf_roles.orig_system%type;
1039: begin
1040: /*
1041: This function needs to return the same data for all approver types. Do NOT modify this
1042: function to return extra data for specific approver types. This function should return

Line 1053: from wf_roles

1049: orig_system
1050: into
1051: descriptionOut,
1052: origSystem
1053: from wf_roles
1054: where
1055: name = nameIn and
1056: status = 'ACTIVE' and
1057: (expiration_date is null or

Line 1065: When the approver is not valid in WF_ROLES, try to get the approver info

1061: exception
1062: when no_data_found then
1063: begin
1064: /*
1065: When the approver is not valid in WF_ROLES, try to get the approver info
1066: from WF_LOCAL_ROLES. (It may still be there, even though the approver is
1067: invalid.) If not found in WF_LOCAL_ROLES, return nameIn in descriptionOut.
1068: See bug 3286313.
1069: */

Line 1126: from wf_roles

1122: orig_system_id
1123: into
1124: origSystemOut,
1125: origSystemIdOut
1126: from wf_roles
1127: where
1128: name = nameIn and
1129: status = 'ACTIVE' and
1130: (expiration_date is null or

Line 1416: from wf_roles

1412: into
1413: origSystemOut,
1414: origSystemIdOut,
1415: displayNameOut
1416: from wf_roles
1417: where
1418: name = nameIn and
1419: status = 'ACTIVE' and
1420: (expiration_date is null or

Line 1455: approverName wf_roles.display_name%type;

1451: this procedure will likely be revised after the 11.5.10 release.
1452: */
1453: procedure getSuperior(approverIn in ame_util.approverRecord2,
1454: superiorOut out nocopy ame_util.approverRecord2) is
1455: approverName wf_roles.display_name%type;
1456: errorCode integer;
1457: errorMessage ame_util.longestStringType;
1458: positionStructureId integer;
1459: noSurrogateException exception;

Line 1466: wf_roles.name,

1462: if(approverIn.orig_system = ame_util.perOrigSystem) then
1463: superiorOut.orig_system := ame_util.perOrigSystem;
1464: select
1465: per_all_assignments_f.supervisor_id,
1466: wf_roles.name,
1467: wf_roles.display_name
1468: into
1469: superiorOut.orig_system_id,
1470: superiorOut.name,

Line 1467: wf_roles.display_name

1463: superiorOut.orig_system := ame_util.perOrigSystem;
1464: select
1465: per_all_assignments_f.supervisor_id,
1466: wf_roles.name,
1467: wf_roles.display_name
1468: into
1469: superiorOut.orig_system_id,
1470: superiorOut.name,
1471: superiorOut.display_name

Line 1473: wf_roles,

1469: superiorOut.orig_system_id,
1470: superiorOut.name,
1471: superiorOut.display_name
1472: from
1473: wf_roles,
1474: per_all_assignments_f
1475: where
1476: wf_roles.orig_system = ame_util.perOrigSystem and
1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

Line 1476: wf_roles.orig_system = ame_util.perOrigSystem and

1472: from
1473: wf_roles,
1474: per_all_assignments_f
1475: where
1476: wf_roles.orig_system = ame_util.perOrigSystem and
1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1478: wf_roles.status = 'ACTIVE' and
1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480: exists (select null

Line 1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

1473: wf_roles,
1474: per_all_assignments_f
1475: where
1476: wf_roles.orig_system = ame_util.perOrigSystem and
1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1478: wf_roles.status = 'ACTIVE' and
1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480: exists (select null
1481: from fnd_user u

Line 1478: wf_roles.status = 'ACTIVE' and

1474: per_all_assignments_f
1475: where
1476: wf_roles.orig_system = ame_util.perOrigSystem and
1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1478: wf_roles.status = 'ACTIVE' and
1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480: exists (select null
1481: from fnd_user u
1482: where u.user_name = wf_roles.name

Line 1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1475: where
1476: wf_roles.orig_system = ame_util.perOrigSystem and
1477: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1478: wf_roles.status = 'ACTIVE' and
1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480: exists (select null
1481: from fnd_user u
1482: where u.user_name = wf_roles.name
1483: and trunc(sysdate) between u.start_date

Line 1482: where u.user_name = wf_roles.name

1478: wf_roles.status = 'ACTIVE' and
1479: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1480: exists (select null
1481: from fnd_user u
1482: where u.user_name = wf_roles.name
1483: and trunc(sysdate) between u.start_date
1484: and nvl(u.end_date,trunc(sysdate))) and
1485: not exists (
1486: select null from wf_roles wf2

Line 1486: select null from wf_roles wf2

1482: where u.user_name = wf_roles.name
1483: and trunc(sysdate) between u.start_date
1484: and nvl(u.end_date,trunc(sysdate))) and
1485: not exists (
1486: select null from wf_roles wf2
1487: where wf_roles.orig_system = wf2.orig_system
1488: and wf_roles.orig_system_id = wf2.orig_system_id
1489: and wf_roles.start_date > wf2.start_date
1490: ) and

Line 1487: where wf_roles.orig_system = wf2.orig_system

1483: and trunc(sysdate) between u.start_date
1484: and nvl(u.end_date,trunc(sysdate))) and
1485: not exists (
1486: select null from wf_roles wf2
1487: where wf_roles.orig_system = wf2.orig_system
1488: and wf_roles.orig_system_id = wf2.orig_system_id
1489: and wf_roles.start_date > wf2.start_date
1490: ) and
1491: per_all_assignments_f.person_id =approverIn.orig_system_id and

Line 1488: and wf_roles.orig_system_id = wf2.orig_system_id

1484: and nvl(u.end_date,trunc(sysdate))) and
1485: not exists (
1486: select null from wf_roles wf2
1487: where wf_roles.orig_system = wf2.orig_system
1488: and wf_roles.orig_system_id = wf2.orig_system_id
1489: and wf_roles.start_date > wf2.start_date
1490: ) and
1491: per_all_assignments_f.person_id =approverIn.orig_system_id and
1492: per_all_assignments_f.primary_flag = 'Y' and

Line 1489: and wf_roles.start_date > wf2.start_date

1485: not exists (
1486: select null from wf_roles wf2
1487: where wf_roles.orig_system = wf2.orig_system
1488: and wf_roles.orig_system_id = wf2.orig_system_id
1489: and wf_roles.start_date > wf2.start_date
1490: ) and
1491: per_all_assignments_f.person_id =approverIn.orig_system_id and
1492: per_all_assignments_f.primary_flag = 'Y' and
1493: per_all_assignments_f.assignment_type in ('E','C') and

Line 1509: wf_roles.name,

1505: elsif(approverIn.orig_system = ame_util.fndUserOrigSystem) then
1506: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1507: select
1508: per_all_assignments_f.supervisor_id,
1509: wf_roles.name,
1510: wf_roles.display_name
1511: into
1512: superiorOut.orig_system_id,
1513: superiorOut.name,

Line 1510: wf_roles.display_name

1506: superiorOut.orig_system := ame_util.fndUserOrigSystem;
1507: select
1508: per_all_assignments_f.supervisor_id,
1509: wf_roles.name,
1510: wf_roles.display_name
1511: into
1512: superiorOut.orig_system_id,
1513: superiorOut.name,
1514: superiorOut.display_name

Line 1516: wf_roles,

1512: superiorOut.orig_system_id,
1513: superiorOut.name,
1514: superiorOut.display_name
1515: from
1516: wf_roles,
1517: per_all_assignments_f
1518: where
1519: wf_roles.orig_system = ame_util.perOrigSystem and
1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

Line 1519: wf_roles.orig_system = ame_util.perOrigSystem and

1515: from
1516: wf_roles,
1517: per_all_assignments_f
1518: where
1519: wf_roles.orig_system = ame_util.perOrigSystem and
1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1521: wf_roles.status = 'ACTIVE' and
1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523: exists (select null

Line 1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

1516: wf_roles,
1517: per_all_assignments_f
1518: where
1519: wf_roles.orig_system = ame_util.perOrigSystem and
1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1521: wf_roles.status = 'ACTIVE' and
1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523: exists (select null
1524: from fnd_user u

Line 1521: wf_roles.status = 'ACTIVE' and

1517: per_all_assignments_f
1518: where
1519: wf_roles.orig_system = ame_util.perOrigSystem and
1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1521: wf_roles.status = 'ACTIVE' and
1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523: exists (select null
1524: from fnd_user u
1525: where u.user_name = wf_roles.name

Line 1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1518: where
1519: wf_roles.orig_system = ame_util.perOrigSystem and
1520: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1521: wf_roles.status = 'ACTIVE' and
1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523: exists (select null
1524: from fnd_user u
1525: where u.user_name = wf_roles.name
1526: and trunc(sysdate) between u.start_date

Line 1525: where u.user_name = wf_roles.name

1521: wf_roles.status = 'ACTIVE' and
1522: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1523: exists (select null
1524: from fnd_user u
1525: where u.user_name = wf_roles.name
1526: and trunc(sysdate) between u.start_date
1527: and nvl(u.end_date,trunc(sysdate))) and
1528: per_all_assignments_f.person_id =
1529: (select employee_id

Line 1544: order by wf_roles.name; /* Select the first matching wf_roles entry. */

1540: trunc(sysdate) between
1541: per_all_assignments_f.effective_start_date and
1542: per_all_assignments_f.effective_end_date and
1543: rownum < 2
1544: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1545: if(superiorOut.orig_system_id is null) then
1546: raise noSurrogateException;
1547: end if;
1548: elsif(approverIn.orig_system = ame_util.posOrigSystem) then

Line 1554: wf_roles.name,

1550: positionStructureId := ame_engine.getHeaderAttValue2(attributeNameIn =>ame_util.nonDefPosStructureAttr);
1551: if (positionStructureId is null) then
1552: select
1553: str.parent_position_id,
1554: wf_roles.name,
1555: wf_roles.display_name
1556: into
1557: superiorOut.orig_system_id,
1558: superiorOut.name,

Line 1555: wf_roles.display_name

1551: if (positionStructureId is null) then
1552: select
1553: str.parent_position_id,
1554: wf_roles.name,
1555: wf_roles.display_name
1556: into
1557: superiorOut.orig_system_id,
1558: superiorOut.name,
1559: superiorOut.display_name

Line 1564: wf_roles

1560: from
1561: per_pos_structure_elements str,
1562: per_pos_structure_versions psv,
1563: per_position_structures pst,
1564: wf_roles
1565: where
1566: str.subordinate_position_id = approverIn.orig_system_id and
1567: str.business_group_id =
1568: nvl(hr_general.get_business_group_id,str.business_group_id) and

Line 1572: wf_roles.orig_system = ame_util.posOrigSystem and

1568: nvl(hr_general.get_business_group_id,str.business_group_id) and
1569: str.pos_structure_version_id = psv.pos_structure_version_id and
1570: pst.position_structure_id = psv.position_structure_id and
1571: pst.primary_position_flag = 'Y' and
1572: wf_roles.orig_system = ame_util.posOrigSystem and
1573: wf_roles.orig_system_id = str.parent_position_id and
1574: wf_roles.status = 'ACTIVE' and
1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576: trunc(sysdate) between

Line 1573: wf_roles.orig_system_id = str.parent_position_id and

1569: str.pos_structure_version_id = psv.pos_structure_version_id and
1570: pst.position_structure_id = psv.position_structure_id and
1571: pst.primary_position_flag = 'Y' and
1572: wf_roles.orig_system = ame_util.posOrigSystem and
1573: wf_roles.orig_system_id = str.parent_position_id and
1574: wf_roles.status = 'ACTIVE' and
1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576: trunc(sysdate) between
1577: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and

Line 1574: wf_roles.status = 'ACTIVE' and

1570: pst.position_structure_id = psv.position_structure_id and
1571: pst.primary_position_flag = 'Y' and
1572: wf_roles.orig_system = ame_util.posOrigSystem and
1573: wf_roles.orig_system_id = str.parent_position_id and
1574: wf_roles.status = 'ACTIVE' and
1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576: trunc(sysdate) between
1577: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1578: rownum < 2

Line 1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1571: pst.primary_position_flag = 'Y' and
1572: wf_roles.orig_system = ame_util.posOrigSystem and
1573: wf_roles.orig_system_id = str.parent_position_id and
1574: wf_roles.status = 'ACTIVE' and
1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576: trunc(sysdate) between
1577: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1578: rownum < 2
1579: order by wf_roles.name;

Line 1579: order by wf_roles.name;

1575: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1576: trunc(sysdate) between
1577: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1578: rownum < 2
1579: order by wf_roles.name;
1580: else
1581: select
1582: str.parent_position_id,
1583: wf_roles.name,

Line 1583: wf_roles.name,

1579: order by wf_roles.name;
1580: else
1581: select
1582: str.parent_position_id,
1583: wf_roles.name,
1584: wf_roles.display_name
1585: into
1586: superiorOut.orig_system_id,
1587: superiorOut.name,

Line 1584: wf_roles.display_name

1580: else
1581: select
1582: str.parent_position_id,
1583: wf_roles.name,
1584: wf_roles.display_name
1585: into
1586: superiorOut.orig_system_id,
1587: superiorOut.name,
1588: superiorOut.display_name

Line 1593: wf_roles

1589: from
1590: per_pos_structure_elements str,
1591: per_pos_structure_versions psv,
1592: per_position_structures pst,
1593: wf_roles
1594: where
1595: str.subordinate_position_id = approverIn.orig_system_id and
1596: str.pos_structure_version_id = psv.pos_structure_version_id and
1597: pst.position_structure_id = positionStructureId and

Line 1599: wf_roles.orig_system = ame_util.posOrigSystem and

1595: str.subordinate_position_id = approverIn.orig_system_id and
1596: str.pos_structure_version_id = psv.pos_structure_version_id and
1597: pst.position_structure_id = positionStructureId and
1598: pst.position_structure_id = psv.position_structure_id and
1599: wf_roles.orig_system = ame_util.posOrigSystem and
1600: wf_roles.orig_system_id = str.parent_position_id and
1601: wf_roles.status = 'ACTIVE' and
1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603: trunc(sysdate) between

Line 1600: wf_roles.orig_system_id = str.parent_position_id and

1596: str.pos_structure_version_id = psv.pos_structure_version_id and
1597: pst.position_structure_id = positionStructureId and
1598: pst.position_structure_id = psv.position_structure_id and
1599: wf_roles.orig_system = ame_util.posOrigSystem and
1600: wf_roles.orig_system_id = str.parent_position_id and
1601: wf_roles.status = 'ACTIVE' and
1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603: trunc(sysdate) between
1604: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and

Line 1601: wf_roles.status = 'ACTIVE' and

1597: pst.position_structure_id = positionStructureId and
1598: pst.position_structure_id = psv.position_structure_id and
1599: wf_roles.orig_system = ame_util.posOrigSystem and
1600: wf_roles.orig_system_id = str.parent_position_id and
1601: wf_roles.status = 'ACTIVE' and
1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603: trunc(sysdate) between
1604: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1605: rownum < 2

Line 1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1598: pst.position_structure_id = psv.position_structure_id and
1599: wf_roles.orig_system = ame_util.posOrigSystem and
1600: wf_roles.orig_system_id = str.parent_position_id and
1601: wf_roles.status = 'ACTIVE' and
1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603: trunc(sysdate) between
1604: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1605: rownum < 2
1606: order by wf_roles.name;

Line 1606: order by wf_roles.name;

1602: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1603: trunc(sysdate) between
1604: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1605: rownum < 2
1606: order by wf_roles.name;
1607: end if;
1608: elsif(approverIn.orig_system = ame_util.fndRespOrigSystem) then
1609: null;
1610: else

Line 1651: approverName wf_roles.display_name%type;

1647: origSystemIdIn in integer,
1648: origSystemIdOut out nocopy integer,
1649: wfRolesNameOut out nocopy varchar2,
1650: displayNameOut out nocopy varchar2) as
1651: approverName wf_roles.display_name%type;
1652: errorCode integer;
1653: errorMessage ame_util.longestStringType;
1654: positionStructureId integer;
1655: noSurrogateException exception;

Line 1661: wf_roles.name,

1657: begin
1658: if(origSystemIn = ame_util.perOrigSystem) then
1659: select
1660: per_all_assignments_f.supervisor_id,
1661: wf_roles.name,
1662: wf_roles.display_name
1663: into
1664: origSystemIdOut,
1665: wfRolesNameOut,

Line 1662: wf_roles.display_name

1658: if(origSystemIn = ame_util.perOrigSystem) then
1659: select
1660: per_all_assignments_f.supervisor_id,
1661: wf_roles.name,
1662: wf_roles.display_name
1663: into
1664: origSystemIdOut,
1665: wfRolesNameOut,
1666: displayNameOut

Line 1668: wf_roles,

1664: origSystemIdOut,
1665: wfRolesNameOut,
1666: displayNameOut
1667: from
1668: wf_roles,
1669: per_all_assignments_f
1670: where
1671: wf_roles.orig_system = ame_util.perOrigSystem and
1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

Line 1671: wf_roles.orig_system = ame_util.perOrigSystem and

1667: from
1668: wf_roles,
1669: per_all_assignments_f
1670: where
1671: wf_roles.orig_system = ame_util.perOrigSystem and
1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1673: wf_roles.status = 'ACTIVE' and
1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675: exists (select null

Line 1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

1668: wf_roles,
1669: per_all_assignments_f
1670: where
1671: wf_roles.orig_system = ame_util.perOrigSystem and
1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1673: wf_roles.status = 'ACTIVE' and
1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675: exists (select null
1676: from fnd_user u

Line 1673: wf_roles.status = 'ACTIVE' and

1669: per_all_assignments_f
1670: where
1671: wf_roles.orig_system = ame_util.perOrigSystem and
1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1673: wf_roles.status = 'ACTIVE' and
1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675: exists (select null
1676: from fnd_user u
1677: where u.user_name = wf_roles.name

Line 1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1670: where
1671: wf_roles.orig_system = ame_util.perOrigSystem and
1672: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1673: wf_roles.status = 'ACTIVE' and
1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675: exists (select null
1676: from fnd_user u
1677: where u.user_name = wf_roles.name
1678: and trunc(sysdate) between u.start_date

Line 1677: where u.user_name = wf_roles.name

1673: wf_roles.status = 'ACTIVE' and
1674: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1675: exists (select null
1676: from fnd_user u
1677: where u.user_name = wf_roles.name
1678: and trunc(sysdate) between u.start_date
1679: and nvl(u.end_date,trunc(sysdate))) and
1680: not exists (
1681: select null from wf_roles wf2

Line 1681: select null from wf_roles wf2

1677: where u.user_name = wf_roles.name
1678: and trunc(sysdate) between u.start_date
1679: and nvl(u.end_date,trunc(sysdate))) and
1680: not exists (
1681: select null from wf_roles wf2
1682: where wf_roles.orig_system = wf2.orig_system
1683: and wf_roles.orig_system_id = wf2.orig_system_id
1684: and wf_roles.start_date > wf2.start_date
1685: ) and

Line 1682: where wf_roles.orig_system = wf2.orig_system

1678: and trunc(sysdate) between u.start_date
1679: and nvl(u.end_date,trunc(sysdate))) and
1680: not exists (
1681: select null from wf_roles wf2
1682: where wf_roles.orig_system = wf2.orig_system
1683: and wf_roles.orig_system_id = wf2.orig_system_id
1684: and wf_roles.start_date > wf2.start_date
1685: ) and
1686: per_all_assignments_f.person_id = origSystemIdIn and

Line 1683: and wf_roles.orig_system_id = wf2.orig_system_id

1679: and nvl(u.end_date,trunc(sysdate))) and
1680: not exists (
1681: select null from wf_roles wf2
1682: where wf_roles.orig_system = wf2.orig_system
1683: and wf_roles.orig_system_id = wf2.orig_system_id
1684: and wf_roles.start_date > wf2.start_date
1685: ) and
1686: per_all_assignments_f.person_id = origSystemIdIn and
1687: per_all_assignments_f.primary_flag = 'Y' and

Line 1684: and wf_roles.start_date > wf2.start_date

1680: not exists (
1681: select null from wf_roles wf2
1682: where wf_roles.orig_system = wf2.orig_system
1683: and wf_roles.orig_system_id = wf2.orig_system_id
1684: and wf_roles.start_date > wf2.start_date
1685: ) and
1686: per_all_assignments_f.person_id = origSystemIdIn and
1687: per_all_assignments_f.primary_flag = 'Y' and
1688: per_all_assignments_f.assignment_type in ('E','C') and

Line 1703: wf_roles.name,

1699: end if;
1700: elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1701: select
1702: per_all_assignments_f.supervisor_id,
1703: wf_roles.name,
1704: wf_roles.display_name
1705: into
1706: origSystemIdOut,
1707: wfRolesNameOut,

Line 1704: wf_roles.display_name

1700: elsif(origSystemIn = ame_util.fndUserOrigSystem) then
1701: select
1702: per_all_assignments_f.supervisor_id,
1703: wf_roles.name,
1704: wf_roles.display_name
1705: into
1706: origSystemIdOut,
1707: wfRolesNameOut,
1708: displayNameOut

Line 1710: wf_roles,

1706: origSystemIdOut,
1707: wfRolesNameOut,
1708: displayNameOut
1709: from
1710: wf_roles,
1711: per_all_assignments_f
1712: where
1713: wf_roles.orig_system = ame_util.perOrigSystem and
1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

Line 1713: wf_roles.orig_system = ame_util.perOrigSystem and

1709: from
1710: wf_roles,
1711: per_all_assignments_f
1712: where
1713: wf_roles.orig_system = ame_util.perOrigSystem and
1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1715: wf_roles.status = 'ACTIVE' and
1716: exists (select null
1717: from fnd_user u

Line 1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and

1710: wf_roles,
1711: per_all_assignments_f
1712: where
1713: wf_roles.orig_system = ame_util.perOrigSystem and
1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1715: wf_roles.status = 'ACTIVE' and
1716: exists (select null
1717: from fnd_user u
1718: where u.user_name = wf_roles.name

Line 1715: wf_roles.status = 'ACTIVE' and

1711: per_all_assignments_f
1712: where
1713: wf_roles.orig_system = ame_util.perOrigSystem and
1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1715: wf_roles.status = 'ACTIVE' and
1716: exists (select null
1717: from fnd_user u
1718: where u.user_name = wf_roles.name
1719: and trunc(sysdate) between u.start_date

Line 1718: where u.user_name = wf_roles.name

1714: wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
1715: wf_roles.status = 'ACTIVE' and
1716: exists (select null
1717: from fnd_user u
1718: where u.user_name = wf_roles.name
1719: and trunc(sysdate) between u.start_date
1720: and nvl(u.end_date,trunc(sysdate))) and
1721: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1722: per_all_assignments_f.person_id =

Line 1721: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1717: from fnd_user u
1718: where u.user_name = wf_roles.name
1719: and trunc(sysdate) between u.start_date
1720: and nvl(u.end_date,trunc(sysdate))) and
1721: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1722: per_all_assignments_f.person_id =
1723: (select employee_id
1724: from fnd_user
1725: where

Line 1738: order by wf_roles.name; /* Select the first matching wf_roles entry. */

1734: trunc(sysdate) between
1735: per_all_assignments_f.effective_start_date and
1736: per_all_assignments_f.effective_end_date and
1737: rownum < 2
1738: order by wf_roles.name; /* Select the first matching wf_roles entry. */
1739: if(origSystemIdOut is null) then
1740: raise noSurrogateException;
1741: end if;
1742: elsif(origSystemIn = ame_util.posOrigSystem) then

Line 1752: wf_roles.name,

1748: end;
1749: if (positionStructureId is null) then
1750: select
1751: str.parent_position_id,
1752: wf_roles.name,
1753: wf_roles.display_name
1754: into
1755: origSystemIdOut,
1756: wfRolesNameOut,

Line 1753: wf_roles.display_name

1749: if (positionStructureId is null) then
1750: select
1751: str.parent_position_id,
1752: wf_roles.name,
1753: wf_roles.display_name
1754: into
1755: origSystemIdOut,
1756: wfRolesNameOut,
1757: displayNameOut

Line 1762: wf_roles

1758: from
1759: per_pos_structure_elements str,
1760: per_pos_structure_versions psv,
1761: per_position_structures pst,
1762: wf_roles
1763: where
1764: str.subordinate_position_id = origSystemIdIn and
1765: str.business_group_id =
1766: nvl(hr_general.get_business_group_id,str.business_group_id) and

Line 1770: wf_roles.orig_system = ame_util.posOrigSystem and

1766: nvl(hr_general.get_business_group_id,str.business_group_id) and
1767: str.pos_structure_version_id = psv.pos_structure_version_id and
1768: pst.position_structure_id = psv.position_structure_id and
1769: pst.primary_position_flag = 'Y' and
1770: wf_roles.orig_system = ame_util.posOrigSystem and
1771: wf_roles.orig_system_id = str.parent_position_id and
1772: wf_roles.status = 'ACTIVE' and
1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774: trunc(sysdate) between

Line 1771: wf_roles.orig_system_id = str.parent_position_id and

1767: str.pos_structure_version_id = psv.pos_structure_version_id and
1768: pst.position_structure_id = psv.position_structure_id and
1769: pst.primary_position_flag = 'Y' and
1770: wf_roles.orig_system = ame_util.posOrigSystem and
1771: wf_roles.orig_system_id = str.parent_position_id and
1772: wf_roles.status = 'ACTIVE' and
1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774: trunc(sysdate) between
1775: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and

Line 1772: wf_roles.status = 'ACTIVE' and

1768: pst.position_structure_id = psv.position_structure_id and
1769: pst.primary_position_flag = 'Y' and
1770: wf_roles.orig_system = ame_util.posOrigSystem and
1771: wf_roles.orig_system_id = str.parent_position_id and
1772: wf_roles.status = 'ACTIVE' and
1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774: trunc(sysdate) between
1775: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1776: rownum < 2

Line 1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1769: pst.primary_position_flag = 'Y' and
1770: wf_roles.orig_system = ame_util.posOrigSystem and
1771: wf_roles.orig_system_id = str.parent_position_id and
1772: wf_roles.status = 'ACTIVE' and
1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774: trunc(sysdate) between
1775: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1776: rownum < 2
1777: order by wf_roles.name;

Line 1777: order by wf_roles.name;

1773: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1774: trunc(sysdate) between
1775: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1776: rownum < 2
1777: order by wf_roles.name;
1778: else
1779: select
1780: str.parent_position_id,
1781: wf_roles.name,

Line 1781: wf_roles.name,

1777: order by wf_roles.name;
1778: else
1779: select
1780: str.parent_position_id,
1781: wf_roles.name,
1782: wf_roles.display_name
1783: into
1784: origSystemIdOut,
1785: wfRolesNameOut,

Line 1782: wf_roles.display_name

1778: else
1779: select
1780: str.parent_position_id,
1781: wf_roles.name,
1782: wf_roles.display_name
1783: into
1784: origSystemIdOut,
1785: wfRolesNameOut,
1786: displayNameOut

Line 1791: wf_roles

1787: from
1788: per_pos_structure_elements str,
1789: per_pos_structure_versions psv,
1790: per_position_structures pst,
1791: wf_roles
1792: where
1793: str.subordinate_position_id = origSystemIdIn and
1794: str.pos_structure_version_id = psv.pos_structure_version_id and
1795: pst.position_structure_id = positionStructureId and

Line 1797: wf_roles.orig_system = ame_util.posOrigSystem and

1793: str.subordinate_position_id = origSystemIdIn and
1794: str.pos_structure_version_id = psv.pos_structure_version_id and
1795: pst.position_structure_id = positionStructureId and
1796: pst.position_structure_id = psv.position_structure_id and
1797: wf_roles.orig_system = ame_util.posOrigSystem and
1798: wf_roles.orig_system_id = str.parent_position_id and
1799: wf_roles.status = 'ACTIVE' and
1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801: trunc(sysdate) between

Line 1798: wf_roles.orig_system_id = str.parent_position_id and

1794: str.pos_structure_version_id = psv.pos_structure_version_id and
1795: pst.position_structure_id = positionStructureId and
1796: pst.position_structure_id = psv.position_structure_id and
1797: wf_roles.orig_system = ame_util.posOrigSystem and
1798: wf_roles.orig_system_id = str.parent_position_id and
1799: wf_roles.status = 'ACTIVE' and
1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801: trunc(sysdate) between
1802: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and

Line 1799: wf_roles.status = 'ACTIVE' and

1795: pst.position_structure_id = positionStructureId and
1796: pst.position_structure_id = psv.position_structure_id and
1797: wf_roles.orig_system = ame_util.posOrigSystem and
1798: wf_roles.orig_system_id = str.parent_position_id and
1799: wf_roles.status = 'ACTIVE' and
1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801: trunc(sysdate) between
1802: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1803: rownum < 2

Line 1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and

1796: pst.position_structure_id = psv.position_structure_id and
1797: wf_roles.orig_system = ame_util.posOrigSystem and
1798: wf_roles.orig_system_id = str.parent_position_id and
1799: wf_roles.status = 'ACTIVE' and
1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801: trunc(sysdate) between
1802: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1803: rownum < 2
1804: order by wf_roles.name;

Line 1804: order by wf_roles.name;

1800: (wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
1801: trunc(sysdate) between
1802: psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
1803: rownum < 2
1804: order by wf_roles.name;
1805: end if;
1806: elsif(origSystemIn = ame_util.fndRespOrigSystem) then
1807: null;
1808: else

Line 1847: This procedure should select the input approver's wf_roles.display_name, NOT the

1843: errorCode integer;
1844: errorMessage ame_util.longestStringType;
1845: begin
1846: /*
1847: This procedure should select the input approver's wf_roles.display_name, NOT the
1848: display name of the input approver's orig_system.
1849: */
1850: /*
1851: If an fnd_user entry has a non-null employee_id (person ID) value, it gets

Line 1852: converted to the PER originating system in wf_roles; otherwise, it gets

1848: display name of the input approver's orig_system.
1849: */
1850: /*
1851: If an fnd_user entry has a non-null employee_id (person ID) value, it gets
1852: converted to the PER originating system in wf_roles; otherwise, it gets
1853: converted to the FND_USR originating system. As just one of these will
1854: happen, we can match both originating systems in a single-row query.
1855: The order-by-name clause and rownum < 2 condition are necessary because we
1856: have encountered data problems where there are several entries for a given

Line 1865: from wf_roles

1861: display_name
1862: into
1863: nameOut,
1864: displayNameOut
1865: from wf_roles
1866: where
1867: ((orig_system = origSystemIn and
1868: orig_system_id = origSystemIdIn) or
1869: (origSystemIn = ame_util.fndUserOrigSystem and

Line 1884: where u.user_name = wf_roles.name

1880: sysdate < expiration_date) and
1881: (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
1882: or exists (select null
1883: from fnd_user u
1884: where u.user_name = wf_roles.name
1885: and trunc(sysdate) between u.start_date
1886: and nvl(u.end_date,trunc(sysdate)))) and
1887: not exists (
1888: select null from wf_roles wf2

Line 1888: select null from wf_roles wf2

1884: where u.user_name = wf_roles.name
1885: and trunc(sysdate) between u.start_date
1886: and nvl(u.end_date,trunc(sysdate)))) and
1887: not exists (
1888: select null from wf_roles wf2
1889: where wf_roles.orig_system = wf2.orig_system
1890: and wf_roles.orig_system_id = wf2.orig_system_id
1891: and wf_roles.start_date > wf2.start_date
1892: ) and

Line 1889: where wf_roles.orig_system = wf2.orig_system

1885: and trunc(sysdate) between u.start_date
1886: and nvl(u.end_date,trunc(sysdate)))) and
1887: not exists (
1888: select null from wf_roles wf2
1889: where wf_roles.orig_system = wf2.orig_system
1890: and wf_roles.orig_system_id = wf2.orig_system_id
1891: and wf_roles.start_date > wf2.start_date
1892: ) and
1893: rownum < 2;

Line 1890: and wf_roles.orig_system_id = wf2.orig_system_id

1886: and nvl(u.end_date,trunc(sysdate)))) and
1887: not exists (
1888: select null from wf_roles wf2
1889: where wf_roles.orig_system = wf2.orig_system
1890: and wf_roles.orig_system_id = wf2.orig_system_id
1891: and wf_roles.start_date > wf2.start_date
1892: ) and
1893: rownum < 2;
1894: exception

Line 1891: and wf_roles.start_date > wf2.start_date

1887: not exists (
1888: select null from wf_roles wf2
1889: where wf_roles.orig_system = wf2.orig_system
1890: and wf_roles.orig_system_id = wf2.orig_system_id
1891: and wf_roles.start_date > wf2.start_date
1892: ) and
1893: rownum < 2;
1894: exception
1895: when no_data_found then

Line 2002: ,wf_roles wfr

1998: ,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
1999: from
2000: per_all_people_f pap
2001: ,hr_all_organization_units haou
2002: ,wf_roles wfr
2003: ,per_all_assignments_f pas
2004: where pap.person_id = pas.person_id
2005: and pas.primary_flag = 'Y'
2006: and pas.assignment_type in ('E','C')

Line 2129: wf_roles

2125: getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
2126: from
2127: per_positions,
2128: hr_organization_units,
2129: wf_roles
2130: where
2131: wf_roles.orig_system_id = per_positions.position_id and
2132: wf_roles.orig_system = ame_util.posOrigSystem and
2133: wf_roles.status = 'ACTIVE' and

Line 2131: wf_roles.orig_system_id = per_positions.position_id and

2127: per_positions,
2128: hr_organization_units,
2129: wf_roles
2130: where
2131: wf_roles.orig_system_id = per_positions.position_id and
2132: wf_roles.orig_system = ame_util.posOrigSystem and
2133: wf_roles.status = 'ACTIVE' and
2134: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2135: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and

Line 2132: wf_roles.orig_system = ame_util.posOrigSystem and

2128: hr_organization_units,
2129: wf_roles
2130: where
2131: wf_roles.orig_system_id = per_positions.position_id and
2132: wf_roles.orig_system = ame_util.posOrigSystem and
2133: wf_roles.status = 'ACTIVE' and
2134: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2135: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
2136: per_positions.business_group_id = hr_organization_units.organization_id and

Line 2133: wf_roles.status = 'ACTIVE' and

2129: wf_roles
2130: where
2131: wf_roles.orig_system_id = per_positions.position_id and
2132: wf_roles.orig_system = ame_util.posOrigSystem and
2133: wf_roles.status = 'ACTIVE' and
2134: (positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
2135: (businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
2136: per_positions.business_group_id = hr_organization_units.organization_id and
2137: truncatedSysdateIn between