DBA Data[Home] [Help]

APPS.JTF_UM_WF_APPROVAL dependencies on FND_USER

Line 104: requesterUsername fnd_user.user_name%type;

100:
101: -- Return the requester username
102: function getRequesterUsername (userID in varchar2) return varchar2 is
103:
104: requesterUsername fnd_user.user_name%type;
105:
106: cursor getUserNameCursor is
107: select USER_NAME
108: from FND_USER

Line 108: from FND_USER

104: requesterUsername fnd_user.user_name%type;
105:
106: cursor getUserNameCursor is
107: select USER_NAME
108: from FND_USER
109: where USER_ID = userID
110: and (nvl (END_DATE, sysdate + 1) > sysdate
111: or to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
112: -- Bug Fix: 4741111: Added the clause to look for pending users

Line 133: function getUserID (username in varchar2) return fnd_user.user_id%type is

129: return requesterUsername;
130: end getRequesterUsername;
131:
132: -- Return the userid from username
133: function getUserID (username in varchar2) return fnd_user.user_id%type is
134:
135: userId fnd_user.user_id%type;
136:
137: cursor getUserIDCursor is

Line 135: userId fnd_user.user_id%type;

131:
132: -- Return the userid from username
133: function getUserID (username in varchar2) return fnd_user.user_id%type is
134:
135: userId fnd_user.user_id%type;
136:
137: cursor getUserIDCursor is
138: select user_id
139: from FND_USER

Line 139: from FND_USER

135: userId fnd_user.user_id%type;
136:
137: cursor getUserIDCursor is
138: select user_id
139: from FND_USER
140: where USER_NAME = username
141: and (nvl (END_DATE, sysdate + 1) > sysdate OR
142: to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
143:

Line 215: -- p_user_id - fnd user_id

211: --
212: -- Description
213: -- Return the organization information of the user.
214: -- IN
215: -- p_user_id - fnd user_id
216: -- OUT
217: -- x_org_name - organization's name
218: -- x_org_number - organization's number
219: --

Line 220: procedure get_org_info (p_user_id in fnd_user.user_id%type,

216: -- OUT
217: -- x_org_name - organization's name
218: -- x_org_number - organization's number
219: --
220: procedure get_org_info (p_user_id in fnd_user.user_id%type,
221: x_org_name out NOCOPY hz_parties.party_name%type,
222: x_org_number out NOCOPY hz_parties.party_number%type) is
223:
224: l_method_name varchar2 (12) := 'GET_ORG_INFO';

Line 228: from fnd_user fnd, hz_parties hz, hz_relationships hzr

224: l_method_name varchar2 (12) := 'GET_ORG_INFO';
225:
226: cursor getOrgNameAndNumber is
227: select hz.party_name, hz.party_number
228: from fnd_user fnd, hz_parties hz, hz_relationships hzr
229: where fnd.user_id = p_user_id
230: and fnd.customer_id = hzr.party_id
231: and hzr.start_date <= sysdate
232: and nvl (hzr.end_date, sysdate + 1) > sysdate

Line 229: where fnd.user_id = p_user_id

225:
226: cursor getOrgNameAndNumber is
227: select hz.party_name, hz.party_number
228: from fnd_user fnd, hz_parties hz, hz_relationships hzr
229: where fnd.user_id = p_user_id
230: and fnd.customer_id = hzr.party_id
231: and hzr.start_date <= sysdate
232: and nvl (hzr.end_date, sysdate + 1) > sysdate
233: and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')

Line 301: -- ownerUserID -- The FND userID of the workflow owner

297: -- Initiate workflow for a um approval
298: -- This API will not launch the workflow process.
299: -- To launch workflow process, call LaunchProcess.
300: -- IN
301: -- ownerUserID -- The FND userID of the workflow owner
302: -- requestType -- The type of request, 'ENROLLMENT/USERTYPE'
303: -- requestID -- ID of the request.
304: -- requesterUserID -- The FND userID of the requester
305: -- requestRegID -- USERTYPE_REG_ID or SUBSCRIPTION_REG_ID

Line 304: -- requesterUserID -- The FND userID of the requester

300: -- IN
301: -- ownerUserID -- The FND userID of the workflow owner
302: -- requestType -- The type of request, 'ENROLLMENT/USERTYPE'
303: -- requestID -- ID of the request.
304: -- requesterUserID -- The FND userID of the requester
305: -- requestRegID -- USERTYPE_REG_ID or SUBSCRIPTION_REG_ID
306: --
307: procedure CreateProcess (ownerUserId in number := null,
308: requestType in varchar2,

Line 317: requesterUsername fnd_user.user_name%type;

313: itemtype varchar2 (8);
314: itemkey number := requestRegID;
315: itemUserKey wf_items.user_key%type;
316: userID number;
317: requesterUsername fnd_user.user_name%type;
318: requesterUsertypeID number;
319: approvalID number;
320: usePendingReqFlag varchar2 (1);
321: processOwner varchar2 (100);

Line 608: ownerUsername fnd_user.user_name%type;

604: approvalID number;
605: approvalURL fnd_profile_option_values.profile_option_value%type;
606: companyNumber number;
607: senderName fnd_profile_option_values.profile_option_value%type;
608: ownerUsername fnd_user.user_name%type;
609: requesterUserID fnd_user.user_id%type;
610: requesterUserName fnd_user.user_name%type;
611: requestId number;
612: requestName varchar2 (1000);

Line 609: requesterUserID fnd_user.user_id%type;

605: approvalURL fnd_profile_option_values.profile_option_value%type;
606: companyNumber number;
607: senderName fnd_profile_option_values.profile_option_value%type;
608: ownerUsername fnd_user.user_name%type;
609: requesterUserID fnd_user.user_id%type;
610: requesterUserName fnd_user.user_name%type;
611: requestId number;
612: requestName varchar2 (1000);
613: requestType varchar2 (10);

Line 610: requesterUserName fnd_user.user_name%type;

606: companyNumber number;
607: senderName fnd_profile_option_values.profile_option_value%type;
608: ownerUsername fnd_user.user_name%type;
609: requesterUserID fnd_user.user_id%type;
610: requesterUserName fnd_user.user_name%type;
611: requestId number;
612: requestName varchar2 (1000);
613: requestType varchar2 (10);
614: respApplID number;

Line 632: from hz_parties hz, hz_relationships hzr, fnd_user fnd

628:
629: --
630: cursor getCompanyNumber is
631: select hz.party_number
632: from hz_parties hz, hz_relationships hzr, fnd_user fnd
633: where fnd.user_id = requesterUserID
634: and fnd.customer_id = hzr.party_id
635: and hzr.start_date <= sysdate
636: and nvl (hzr.end_date, sysdate + 1) > sysdate

Line 633: where fnd.user_id = requesterUserID

629: --
630: cursor getCompanyNumber is
631: select hz.party_number
632: from hz_parties hz, hz_relationships hzr, fnd_user fnd
633: where fnd.user_id = requesterUserID
634: and fnd.customer_id = hzr.party_id
635: and hzr.start_date <= sysdate
636: and nvl (hzr.end_date, sysdate + 1) > sysdate
637: and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')

Line 896: 'Requester User ID is missing in the FND_USER');

892:
893: exception
894: when MISSING_REQUESTER_USER_ID then
895: wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode,
896: 'Requester User ID is missing in the FND_USER');
897: raise;
898: when others then
899: wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode);
900: raise;

Line 927: l_requester_user_id fnd_user.user_id%type;

923: x_role_name out NOCOPY varchar2,
924: x_role_name_display out NOCOPY varchar2) is
925:
926: l_method_name varchar2 (20) := 'GET_ORG_AD_HOC_ROLE';
927: l_requester_user_id fnd_user.user_id%type;
928: l_org_name hz_parties.party_name%type;
929: l_org_number hz_parties.party_number%type;
930: l_uni_approver_not_found boolean := true;
931: l_role_name wf_local_roles.name%type;

Line 941: select fnd.user_name

937: from WF_LOCAL_ROLES
938: where name = x_role_name;
939:
940: cursor getUniversalApprovers is
941: select fnd.user_name
942: from hz_parties hz_org, hz_relationships hzr, fnd_user fnd
943: where hz_org.party_number = l_org_number
944: and hz_org.party_type = 'ORGANIZATION'
945: and hz_org.party_id = hzr.object_id

Line 942: from hz_parties hz_org, hz_relationships hzr, fnd_user fnd

938: where name = x_role_name;
939:
940: cursor getUniversalApprovers is
941: select fnd.user_name
942: from hz_parties hz_org, hz_relationships hzr, fnd_user fnd
943: where hz_org.party_number = l_org_number
944: and hz_org.party_type = 'ORGANIZATION'
945: and hz_org.party_id = hzr.object_id
946: and hzr.start_date <= sysdate

Line 967: and prin_b.principal_name = fnd.user_name

963: and role_perms.jtf_auth_permission_id = perm.jtf_auth_permission_id
964: and prin_maps.jtf_auth_domain_id = domains_b.jtf_auth_domain_id
965: and domains_b.domain_name = 'CRM_DOMAIN'
966: and perm.permission_name = 'JTF_PRIMARY_USER_SUMMARY'
967: and prin_b.principal_name = fnd.user_name
968: );
969: --changes for 4734470
970:
971: UserTable WF_DIRECTORY.UserTable;

Line 1069: approverUsername fnd_user.user_name%type;

1065: resultout out NOCOPY varchar2) is
1066: --
1067: applID number;
1068: approverID number (15);
1069: approverUsername fnd_user.user_name%type;
1070: approverUserID fnd_user.user_id%type;
1071: requestType varchar2 (10);
1072: requestId number;
1073: resultType varchar2 (5);

Line 1070: approverUserID fnd_user.user_id%type;

1066: --
1067: applID number;
1068: approverID number (15);
1069: approverUsername fnd_user.user_name%type;
1070: approverUserID fnd_user.user_id%type;
1071: requestType varchar2 (10);
1072: requestId number;
1073: resultType varchar2 (5);
1074: uniPrimaryUser fnd_profile_option_values.profile_option_value%type;

Line 1240: from JTF_UM_APPROVERS a, FND_USER f

1236: and APPROVAL_ID = l_approvalID;
1237:
1238: cursor nextApproverInfoCursor is
1239: select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1240: from JTF_UM_APPROVERS a, FND_USER f
1241: where a.APPROVER_SEQ > l_approverSeq
1242: and a.APPROVAL_ID = l_approvalID
1243: and a.org_party_id is null
1244: and a.EFFECTIVE_START_DATE <= sysdate

Line 1254: from JTF_UM_APPROVERS a, FND_USER f

1250: order by a.APPROVER_SEQ;
1251:
1252: cursor nextOrgApproverInfoCursor is
1253: select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1254: from JTF_UM_APPROVERS a, FND_USER f
1255: where a.APPROVER_SEQ > l_approverSeq
1256: and a.APPROVAL_ID = l_approvalID
1257: and a.ORG_PARTY_ID = l_org_party_id
1258: and a.EFFECTIVE_START_DATE <= sysdate

Line 1269: FND_USER f

1265:
1266: cursor OrgApproverOverrideCursor is
1267: select 'X'
1268: from JTF_UM_APPROVERS a,
1269: FND_USER f
1270: where a.APPROVAL_ID = l_approvalID
1271: and a.ORG_PARTY_ID = l_org_party_id
1272: and a.EFFECTIVE_START_DATE <= sysdate
1273: and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate

Line 1282: FND_USER fu

1278: -- select the requesters org party id
1279: cursor requesterOrgCursor is
1280: select hzr.object_id requester_org_id
1281: from hz_relationships hzr,
1282: FND_USER fu
1283: where fu.USER_ID = l_requesterUserID
1284: and fu.CUSTOMER_ID = hzr.PARTY_ID
1285: and hzr.start_date <= sysdate
1286: and nvl (hzr.END_DATE, sysdate + 1) > sysdate

Line 1535: from FND_USER

1531: requestType varchar2 (10);
1532:
1533: cursor getUserID is
1534: select USER_ID
1535: from FND_USER
1536: where USER_NAME = ownerUsername;
1537: --
1538: begin
1539:

Line 1696: from FND_USER

1692: l_return_status varchar2 (1);
1693:
1694: cursor getUserID is
1695: select USER_ID
1696: from FND_USER
1697: where USER_NAME = WF_ENGINE.context_text;
1698: --
1699: begin
1700:

Line 1897: from FND_USER

1893: and STATUS_CODE = 'PENDING';
1894:
1895: cursor requesterUserNameCursor is
1896: select USER_NAME
1897: from FND_USER
1898: where USER_ID = requesterUserID
1899: and (nvl(END_DATE,sysdate) >= sysdate OR
1900: to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
1901:

Line 2112: l_customer_id FND_USER.CUSTOMER_ID%TYPE;

2108:
2109: userStartDate date;
2110: userEndDate date;
2111: -- adding for Bug 4320347
2112: l_customer_id FND_USER.CUSTOMER_ID%TYPE;
2113: l_person_party_id FND_USER.PERSON_PARTY_ID%TYPE;
2114: -- end of changes for 4320347
2115: --
2116: cursor enrollmentsCursor is

Line 2113: l_person_party_id FND_USER.PERSON_PARTY_ID%TYPE;

2109: userStartDate date;
2110: userEndDate date;
2111: -- adding for Bug 4320347
2112: l_customer_id FND_USER.CUSTOMER_ID%TYPE;
2113: l_person_party_id FND_USER.PERSON_PARTY_ID%TYPE;
2114: -- end of changes for 4320347
2115: --
2116: cursor enrollmentsCursor is
2117: select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID

Line 2199: From FND_USER

2195: -- release the user name
2196: -- check if user is a pending user
2197: Select start_date,end_date,USER_NAME,customer_id,person_party_id
2198: Into userStartDate,userEndDate,requesterUsername,l_customer_id,l_person_party_id
2199: From FND_USER
2200: Where user_id = requesterUserID;
2201:
2202: If to_char(userStartDate) = to_char(FND_API.G_MISS_DATE)
2203: And to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then

Line 2205: FND_USER_PKG.RemovePendingUser(requesterUsername);

2201:
2202: If to_char(userStartDate) = to_char(FND_API.G_MISS_DATE)
2203: And to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then
2204: -- release user
2205: FND_USER_PKG.RemovePendingUser(requesterUsername);
2206: End If;
2207:
2208: -- Event handling
2209: -- Get the values for creation of parameters for the event

Line 2325: from fnd_user

2321: l_result varchar (10);
2322:
2323: cursor getFNDUserID is
2324: select user_id
2325: from fnd_user
2326: where (nvl (end_date, sysdate + 1) > sysdate
2327: OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
2328:
2329: and user_name = l_approver_username;

Line 2489: l_approver_username fnd_user.user_name%type;

2485: funcmode in varchar2,
2486: resultout out NOCOPY varchar2) is
2487:
2488: l_appl_id JTF_UM_USERTYPES_B.APPLICATION_ID%TYPE;
2489: l_approver_username fnd_user.user_name%type;
2490: l_org_name hz_parties.party_name%type;
2491: l_org_number hz_parties.party_number%type;
2492: l_primary_user_role fnd_profile_option_values.profile_option_value%type;
2493: l_procedure_name CONSTANT varchar2(26) := 'universal_approvers_exists';

Line 2496: l_requester_user_id fnd_user.user_id%type;

2492: l_primary_user_role fnd_profile_option_values.profile_option_value%type;
2493: l_procedure_name CONSTANT varchar2(26) := 'universal_approvers_exists';
2494: l_request_id number;
2495: l_request_type varchar2 (10);
2496: l_requester_user_id fnd_user.user_id%type;
2497: l_universal_approvers fnd_profile_option_values.profile_option_value%type;
2498:
2499: begin
2500:

Line 2723: from jtf_um_usertype_reg reg , fnd_user fnd

2719: cursor getOrgDetail is
2720: SELECT party.status FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
2721: WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
2722: AND PREL.PARTY_ID = (select fnd.customer_id
2723: from jtf_um_usertype_reg reg , fnd_user fnd
2724: where usertype_reg_id = to_number(itemkey)
2725: and reg.user_id=fnd.USER_ID
2726: )
2727: AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

Line 2725: and reg.user_id=fnd.USER_ID

2721: WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
2722: AND PREL.PARTY_ID = (select fnd.customer_id
2723: from jtf_um_usertype_reg reg , fnd_user fnd
2724: where usertype_reg_id = to_number(itemkey)
2725: and reg.user_id=fnd.USER_ID
2726: )
2727: AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2728: AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2729: AND PREL.START_DATE < SYSDATE

Line 3249: from jtf_um_usertype_reg utreg, fnd_user fu, hz_relationships hzr

3245: p_org_party_id in number) is
3246:
3247: cursor usertype_reg is
3248: select utreg.usertype_reg_id, utreg.user_id
3249: from jtf_um_usertype_reg utreg, fnd_user fu, hz_relationships hzr
3250: where utreg.usertype_id = p_usertype_id
3251: and utreg.status_code = 'PENDING'
3252: and nvl (utreg.effective_end_date, sysdate + 1) > sysdate
3253: and utreg.user_id = fu.user_id

Line 3378: from jtf_um_subscription_reg subreg, fnd_user fu, hz_relationships hzr

3374: or effective_end_date > sysdate);
3375:
3376: cursor subscription_reg_w_org is
3377: select subreg.subscription_reg_id, fu.user_id, subreg.wf_item_type
3378: from jtf_um_subscription_reg subreg, fnd_user fu, hz_relationships hzr
3379: where subreg.subscription_id = p_subscription_id
3380: and subreg.status_code = 'PENDING'
3381: and nvl (subreg.effective_end_date, sysdate + 1) > sysdate
3382: and subreg.user_id = fu.user_id