12: --Locks:
13: -- None.
14: --Function:
15: -- Get the vendor id (as in PO_VENDORS table ) for a given
16: -- user name ( as in FND_USER table)
17: --Parameters:
18: --IN:
19: --p_usename
20: -- fnd username
16: -- user name ( as in FND_USER table)
17: --Parameters:
18: --IN:
19: --p_usename
20: -- fnd username
21: --Returns
22: -- vendor id from the PO_VENDORS table.
23: --Notes:
24: --
32: l_vendor_id number;
33: BEGIN
34:
35: -- SQL What: select PO vendor id for a giver user name
36: -- SQL Why: for given fnd username ensure po vendor entry exists
37: -- SQL join: fnd_user.username
38: -- Moving the logic to the View.Also we do not use
39: -- POS_EMPLOYMENT/POS_VENDOR_PARTY any more in R12.
40:
33: BEGIN
34:
35: -- SQL What: select PO vendor id for a giver user name
36: -- SQL Why: for given fnd username ensure po vendor entry exists
37: -- SQL join: fnd_user.username
38: -- Moving the logic to the View.Also we do not use
39: -- POS_EMPLOYMENT/POS_VENDOR_PARTY any more in R12.
40:
41: select vendor_id
81: -- FND_API.G_RET_STS_ERROR - for expected error
82: -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
83: -- FND_API.G_RET_STS_SUCCESS - for success
84: --x_supplier_user_tbl
85: -- PL/SQL table to FND_USER.username for Contracts
86: --x_supplier_userlist
87: -- comma delimited supplier user names for locate_notifier to retain
88: -- backward compatibility
89: --x_supplier_userlist_for_sql
136: -- declare cursor
137: -- cursor to select vendor level contacts
138: cursor vendor_only_username(v_vendor_id NUMBER) IS
139: select DISTINCT user1.user_name
140: from fnd_user user1,
141: ak_web_user_sec_attr_values ak1,
142: fnd_user_resp_groups fur
143: where ak1.attribute_code='ICX_SUPPLIER_ORG_ID'
144: and ak1.number_value=v_vendor_id
138: cursor vendor_only_username(v_vendor_id NUMBER) IS
139: select DISTINCT user1.user_name
140: from fnd_user user1,
141: ak_web_user_sec_attr_values ak1,
142: fnd_user_resp_groups fur
143: where ak1.attribute_code='ICX_SUPPLIER_ORG_ID'
144: and ak1.number_value=v_vendor_id
145: and ak1.ATTRIBUTE_APPLICATION_ID=177
146: and ak1.web_user_id=user1.user_id
168: -- cusrsor to select vendor and site level contacts
169: cursor vendor_site_username(v_vendor_id NUMBER
170: , v_vendor_site_id number) IS
171: select DISTINCT user1.user_name
172: from fnd_user user1,
173: ak_web_user_sec_attr_values ak1,
174: ak_web_user_sec_attr_values ak2,
175: fnd_user_resp_groups fur
176: where
171: select DISTINCT user1.user_name
172: from fnd_user user1,
173: ak_web_user_sec_attr_values ak1,
174: ak_web_user_sec_attr_values ak2,
175: fnd_user_resp_groups fur
176: where
177: user1.user_id=ak1.web_user_id
178: and ak1.attribute_code='ICX_SUPPLIER_ORG_ID'
179: and ak1.number_value=v_vendor_id
202: -- cursor to select specified contacts for the vendor
203: cursor vendor_contact_username(v_vendor_id NUMBER
204: , v_vendor_contact_id NUMBER) IS
205: select DISTINCT user1.user_name
206: from fnd_user user1,
207: ak_web_user_sec_attr_values ak1,
208: ak_web_user_sec_attr_values ak3,
209: fnd_user_resp_groups fur
210: where user1.user_id=ak1.web_user_id
205: select DISTINCT user1.user_name
206: from fnd_user user1,
207: ak_web_user_sec_attr_values ak1,
208: ak_web_user_sec_attr_values ak3,
209: fnd_user_resp_groups fur
210: where user1.user_id=ak1.web_user_id
211: and ak1.attribute_code='ICX_SUPPLIER_ORG_ID'
212: and ak1.number_value=v_vendor_id
213: and ak1.ATTRIBUTE_APPLICATION_ID=177