1 PACKAGE BODY POS_VENDOR_UTIL_PKG as
2 -- $Header: POSVENDB.pls 120.7 2006/05/08 10:46:59 gdwivedi noship $
3
4 -- This procedure will merge the related parties for
5 -- given vendor_id 's
6 -- On error typically exceptions is raised keeping the error
7 -- stack information.
8
9 -- In Release 12, POS is no longer maintaining the TCA links
10 -- so all we have to do is maintain the registration details
11
12 PROCEDURE merge_vendor_parties
13 ( p_vendor_id IN NUMBER, -- new VENDOR_ID
14 p_dup_vendor_id IN NUMBER -- old / disabled VENDOR_ID
15 --,p_vendor_site_id IN NUMBER, -- new VENDOR_SITE_ID
16 --p_dup_vendor_site_id IN NUMBER -- old / disabled VENDOR_SITE_ID
17 )
18 IS
19 BEGIN
20 -- merge the POS registration records
21 merge_registration_details(p_vendor_id, p_dup_vendor_id);
22 END merge_vendor_parties;
23
24 -- This procedure will update the registration details tables when a
25 -- party merge occurs
26 -- This is called internally from merge_vendor_parties
27 PROCEDURE merge_registration_details
28 (p_vendor_id IN NUMBER, -- new VENDOR_ID
29 p_dup_vendor_id IN NUMBER -- old / disabled VENDOR_ID
30 )
31 IS
32 x_exception_msg varchar2(500);
33 l_new_vendor_name varchar2(240);
34 BEGIN
35 -- Correct the name first and then correct the ID
36 select vendor_name
37 into l_new_vendor_name
38 from ap_suppliers
39 where vendor_id = p_vendor_id;
40
41 -- We are not comparing the supplier name as this information
42 -- might not be reliable ( could have different spelling).
43 -- But the supplier number must be
44 -- reliable.
45 update fnd_registration_details
46 set field_value_string = l_new_vendor_name
47 , last_updated_by = 1, last_update_date = sysdate
48 where application_id = 177
49 and field_name = 'Supplier Name'
50 and registration_id in
51 (select fr.registration_id from fnd_registrations fr,
52 fnd_registration_details frd
53 where registration_status IN ('INVITED', 'REGISTERED')
54 and frd.registration_id = fr.registration_id
55 and frd.field_name = 'Supplier Number'
56 and frd.field_value_number = p_dup_vendor_id
57 and frd.application_id = 177
58 );
59
60 update fnd_registration_details
61 set field_value_number = p_vendor_id
62 , last_update_date = sysdate, last_updated_by = 1
63 where field_value_number = p_dup_vendor_id
64 and application_id = 177
65 and field_name = 'Supplier Number'
66 and registration_id in
67 (select registration_id from fnd_registrations
68 where registration_status IN ('INVITED', 'REGISTERED')
69 );
70 EXCEPTION
71 WHEN OTHERS THEN
72 x_exception_msg := 'POSVENDB.pls: Merging vendor data in Fnd_registration_details table:\n Error :'
73 ||x_exception_msg;
74 raise_application_error(-20001, x_exception_msg, true);
75 END merge_registration_details;
76
77 FUNCTION get_party_id_for_vendor(p_vendor_id IN NUMBER)
78 RETURN NUMBER IS
79 l_party_id NUMBER;
80 CURSOR l_cur IS
81 SELECT party_id
82 FROM ap_suppliers
83 WHERE vendor_id = p_vendor_id;
84 BEGIN
85 OPEN l_cur;
86 FETCH l_cur INTO l_party_id;
87 IF l_cur%notfound THEN
88 CLOSE l_cur;
89 raise_application_error
90 (-20001
91 , 'pos_vendor_util_pkg.get_party_id_for_vendor error: Invalid vendor_id '
92 || p_vendor_id
93 , true);
94 END IF;
95
96 CLOSE l_cur;
97 RETURN l_party_id;
98 END get_party_id_for_vendor;
99
100 -- Get the vendor id (as in AP_SUPPLIERS table ) for a given
101 -- user name ( as in FND_USER table)
102 FUNCTION get_po_vendor_id_for_user(p_username IN VARCHAR2)
103 RETURN NUMBER
104 IS
105 l_vendor_id NUMBER;
106 CURSOR l_cur IS
107 SELECT vendor_id
108 FROM pos_supplier_users_v
109 WHERE user_name = p_username;
110 BEGIN
111 OPEN l_cur;
112 FETCH l_cur INTO l_vendor_id;
113 CLOSE l_cur;
114 RETURN l_vendor_id;
115 END get_po_vendor_id_for_user;
116
117 FUNCTION get_vendor_party_id_for_user(p_username IN VARCHAR2)
118 RETURN NUMBER
119 IS
120 l_party_id NUMBER;
121 CURSOR l_cur IS
122 SELECT vendor_party_id
123 FROM pos_supplier_users_v
124 WHERE user_name = p_username;
125 BEGIN
126 OPEN l_cur;
127 FETCH l_cur INTO l_party_id;
128 CLOSE l_cur;
129 RETURN l_party_id;
130 END get_vendor_party_id_for_user;
131
132 -- validate_user_setup
133 -- Purpose: to make sure the user has the correct vendor, site and contact setup
134 -- for a single supplier hierarchy
135 -- Return Value: returns value 'Y' or 'N' indicating respectively whether user
136 -- set-up is valid or not
137
138 FUNCTION validate_user_setup (p_user_id in number) RETURN VARCHAR2 IS
139
140 l_root_vendor_id NUMBER;
141 l_number NUMBER;
142
143 CURSOR l_vendor_cur IS
144 SELECT akw.number_value
145 FROM ak_web_user_sec_attr_values akw, ap_suppliers pv
146 WHERE akw.web_user_id = p_user_id
147 AND akw.attribute_code = 'ICX_SUPPLIER_ORG_ID'
148 AND akw.attribute_application_id = 177
149 AND akw.number_value = pv.vendor_id
150 AND ((pv.parent_vendor_id is null) OR
151 (pv.parent_vendor_id not in
152 (SELECT number_value
153 FROM ak_web_user_sec_attr_values
154 WHERE web_user_id = p_user_id
155 AND attribute_code = 'ICX_SUPPLIER_ORG_ID'
156 AND attribute_application_id = 177
157 )
158 )
159 )
160 AND ROWNUM < 3;
161
162 CURSOR l_invalid_site_cur IS
163 SELECT akw.number_value
164 FROM ak_web_user_sec_attr_values akw, ap_supplier_sites_all pvs
165 WHERE akw.web_user_id = p_user_id
166 AND akw.attribute_code = 'ICX_SUPPLIER_SITE_ID'
167 AND akw.attribute_application_id = 177
168 AND akw.number_value = pvs.vendor_site_id
169 AND pvs.vendor_id NOT IN
170 (select vendor_id from ap_suppliers
171 start with vendor_id = l_root_vendor_id
172 connect by prior vendor_id = parent_vendor_id
173 )
174 AND ROWNUM < 2;
175
176 CURSOR l_invalid_contact_cur IS
177 SELECT akw.number_value
178 FROM ak_web_user_sec_attr_values akw, po_vendor_contacts pvc
179 WHERE akw.web_user_id = p_user_id
180 AND akw.attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
181 AND akw.attribute_application_id = 177
182 AND akw.number_value = pvc.vendor_contact_id
183 AND pvc.vendor_site_id not in
184 (SELECT vendor_site_id
185 FROM ap_supplier_sites_all
186 WHERE vendor_id in
187 (select vendor_id
188 from ap_suppliers
189 start with vendor_id = l_root_vendor_id
190 connect by prior vendor_id = parent_vendor_id
191 )
192 )
193 AND ROWNUM < 2;
194 BEGIN
195 -- there should be exactly one vendor with no parent_vendor in the list
196 OPEN l_vendor_cur;
197 FETCH l_vendor_cur INTO l_root_vendor_id;
198 IF l_vendor_cur%notfound THEN
199 CLOSE l_vendor_cur;
200 RETURN 'N';
201 END IF;
202
203 FETCH l_vendor_cur INTO l_number;
204 IF l_vendor_cur%found THEN
205 CLOSE l_vendor_cur;
206 -- found two then it is wrong
207 RETURN 'N';
208 END IF;
209 CLOSE l_vendor_cur;
210
211 -- do the check for vendor_sites
212 -- to make sure all sites listed for the user belong to the parent vendor hierarchy
213
214 OPEN l_invalid_site_cur;
215 FETCH l_invalid_site_cur INTO l_number;
216 IF l_invalid_site_cur%found THEN
217 CLOSE l_invalid_site_cur;
218 RETURN 'N';
219 END IF;
220 CLOSE l_invalid_site_cur;
221
222 -- do the check for vendor_contacts
223 -- to make sure all contacts listed for the user belong to the parent vendor hierarchy
224
225 OPEN l_invalid_contact_cur;
226 FETCH l_invalid_contact_cur INTO l_number;
227 IF l_invalid_contact_cur%found THEN
228 CLOSE l_invalid_contact_cur;
229 RETURN 'N';
230 END IF;
231 CLOSE l_invalid_contact_cur;
232
233 RETURN 'Y';
234
235 END validate_user_setup;
236
237 -- Return Y if p_vendor_name already exists in ap_suppliers
238 -- Note: this api does case insensitive check
239 FUNCTION vendor_name_exist (p_vendor_name IN VARCHAR2) RETURN VARCHAR2
240 IS
241 CURSOR l_cur IS
242 SELECT 1
243 FROM ap_suppliers
244 WHERE Upper(vendor_name) = Upper(p_vendor_name);
245 l_number NUMBER;
246 BEGIN
247 OPEN l_cur;
248 FETCH l_cur INTO l_number;
249 IF l_cur%found THEN
250 CLOSE l_cur;
251 RETURN 'Y';
252 ELSE
253 CLOSE l_cur;
254 RETURN 'N';
255 END IF;
256 END vendor_name_exist;
257
258 PROCEDURE get_le_by_liability_acct
259 (p_accts_pay_ccid IN NUMBER,
260 p_operating_unit_id IN NUMBER,
261 x_le_id OUT nocopy NUMBER,
262 x_le_name OUT nocopy VARCHAR2
263 )
264 IS
265 l_return_status VARCHAR2(1);
266 l_msg_data VARCHAR2(3000);
267 l_ptop_le_info xle_businessinfo_grp.ptop_le_rec;
268 BEGIN
269 IF p_accts_pay_ccid IS NULL OR p_operating_unit_id IS NULL THEN
270 x_le_id := NULL;
271 x_le_name := NULL;
272 RETURN;
273 END IF;
274
275 xle_businessinfo_grp.get_purchasetopay_info
276 (
277 x_return_status => l_return_status,
278 x_msg_data => l_msg_data,
279 P_registration_code => NULL,
280 P_registration_number => NULL,
281 P_location_id => NULL,
282 p_code_combination_id => p_accts_pay_ccid,
283 P_operating_unit_id => p_operating_unit_id,
284 x_ptop_Le_info => l_ptop_le_info
285 );
286 IF l_return_status IS NULL OR
287 l_return_status <> fnd_api.g_ret_sts_success THEN
288 x_le_id := NULL;
289 x_le_name := NULL;
290 RETURN;
291 END IF;
292
293 x_le_id := l_ptop_le_info.legal_entity_id;
294 x_le_name := l_ptop_le_info.name;
295
296 EXCEPTION
297 WHEN OTHERS THEN
298 x_le_id := NULL;
299 x_le_name := NULL;
300
301 END get_le_by_liability_acct;
302
303 -- Return legal entity id based on the liability account
304 -- (accts_pay_ccid), and the operating unit id
305 -- of a vendor site; return null if error
306 FUNCTION get_le_id_by_liability_acct
307 (p_accts_pay_ccid IN NUMBER,
308 p_operating_unit_id IN NUMBER
309 )
310 RETURN NUMBER
311 IS
312 l_le_id NUMBER;
313 l_le_name xle_entity_profiles.NAME%TYPE;
314 BEGIN
315 get_le_by_liability_acct
316 (p_accts_pay_ccid => p_accts_pay_ccid,
317 p_operating_unit_id => p_operating_unit_id,
318 x_le_id => l_le_id,
319 x_le_name => l_le_name
320 );
321 RETURN l_le_id;
322 END get_le_id_by_liability_acct;
323
324 -- Return legal entity id based on the liability account
325 -- (accts_pay_ccid), and the operating unit id
326 -- of a vendor site; return null if error
327 FUNCTION get_le_name_by_liability_acct
328 (p_accts_pay_ccid IN NUMBER,
329 p_operating_unit_id IN NUMBER
330 )
331 RETURN VARCHAR2
332 IS
333 l_le_id NUMBER;
334 l_le_name xle_entity_profiles.NAME%TYPE;
335 BEGIN
336 get_le_by_liability_acct
337 (p_accts_pay_ccid => p_accts_pay_ccid,
338 p_operating_unit_id => p_operating_unit_id,
339 x_le_id => l_le_id,
340 x_le_name => l_le_name
341 );
342 RETURN l_le_name;
343 END get_le_name_by_liability_acct;
344
345 END POS_VENDOR_UTIL_PKG;
[Home] [Help]