1 PACKAGE BODY PO_VENDORS_PVT AS
2 /* $Header: POXVVENB.pls 120.3 2005/12/22 03:54:10 jbalakri noship $ */
3
4
5 -------------------------------------------------------------------------------
6 --Start of Comments
7 --Name: get_po_vendor_id_for_user
8 --Pre-reqs:
9 -- None.
10 --Modifies:
11 -- None.
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
21 --Returns
22 -- vendor id from the PO_VENDORS table.
23 --Notes:
24 --
25 --Testing:
26 --
27 --End of Comments
28 -------------------------------------------------------------------------------
29
30 FUNCTION get_po_vendor_id_for_user(p_username IN VARCHAR2)
31 RETURN NUMBER IS
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
41 select vendor_id
42 into l_vendor_id
43 from pos_supplier_users_v
44 where user_name = p_username;
45
46 return l_vendor_id;
47 EXCEPTION
48 WHEN OTHERS THEN
49 return -1;
50 END get_po_vendor_id_for_user;
51
52 -------------------------------------------------------------------------------
53 --Start of Comments
54 --Name: get_supplier_userlist
55 --Pre-reqs:
56 -- None.
57 --Modifies:
58 -- None.
59 --Locks:
60 -- None.
61 --Function:
62 -- This procedure is sliced from PO_REQAPPROVAL_INIT1 package to return a list
63 -- of supplier users on a supplier PO document. This procedure is called by
64 -- Contracts team to determine the supplier users to send notifications to,
65 -- when deliverables undergo a status change (example: it is overdue) and
66 -- supplier user is not specified on the deliverable.
67 -- Other refereces - PO_REQAPPROVAL_INIT1.locate_notifier
68 -- Transportation.
69 -- PO_CONTERMS_UTL_GRP.get_external_userlist
70 --Parameters:
71 --IN:
72 --p_document_id
73 -- PO header ID
74 --p_document_type
75 -- Contracts business document type ex: PA_BLANKET or PO_STANDARD
76 -- This will be parsed to retrieve the PO document type
77 --p_external_contact_id
78 -- Supplier contact id on the deliverable. Default is null
79 --OUT:
80 --x_return_status
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
90 -- space delimited supplier user names for locate_notifier to retain
91 -- backward compatibility
92 --x_num_users
93 -- No of supplier users for locate_notifier to retain backwards compatibility
94 --x_vendor_id
95 -- PO document vendor id for locate_notifier to retain backward compatibility
96 --Notes:
97 -- SAHEGDE 07/18/2003
98 -- This procedure was sliced from the locate_notifier procedure in the
99 -- PO_REQAPPROVAL_INIT1 package to allow calling it from multiple procedures.
100 -- In order to retain backward compatibility with locate notifier curently
101 -- procedure returns the names as VARCHAR2 as well as a PL/SQL table. Going
102 -- forward the code will be refactored to return only PL/SQL table and calling
103 -- code in PO_REQAPPROVAL_INIT1 will be modified to reflect this change.
104 --Testing:
105 --
106 --End of Comments
107 -------------------------------------------------------------------------------
108 PROCEDURE get_supplier_userlist(p_document_id IN NUMBER
109 ,p_document_type IN VARCHAR2
110 ,p_external_contact_id IN NUMBER DEFAULT NULL
111 ,x_return_status OUT NOCOPY VARCHAR2
112 ,x_supplier_user_tbl OUT NOCOPY supplier_user_tbl_type
113 ,x_supplier_userlist OUT NOCOPY VARCHAR2
114 ,x_supplier_userlist_for_sql OUT NOCOPY VARCHAR2
115 ,x_num_users OUT NOCOPY NUMBER
116 ,x_vendor_id OUT NOCOPY NUMBER)IS
117
118
119 -- declare local and out variables
120 l_user_name varchar2(100);
121 l_user_id number;
122 l_vendor_contact_id number;
123 l_vendor_id number;
124 l_vendor_site_id number;
125 l_step varchar2(32000);
126 l_temp varchar2(100);
127
128 l_num_users number := 0;
129 l_namelist VARCHAR2(31990) := NULL;
130 l_namelist_for_sql VARCHAR2(32000) := NULL;
131 l_supplier_user_tbl supplier_user_tbl_type;
132 l_progress VARCHAR2(3) := '000';
133
134 l_api_name CONSTANT VARCHAR2(30) := 'get_supplier_userlist';
135
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
145 and ak1.ATTRIBUTE_APPLICATION_ID=177
146 and ak1.web_user_id=user1.user_id
147 and fur.responsibility_application_id = 177
148 and fur.start_date < sysdate
149 and nvl(fur.end_date, sysdate + 1) >= sysdate
150 and fur.user_id = user1.user_id
151 and trunc(sysdate)
152 BETWEEN nvl(trunc(user1.start_date), trunc(sysdate))
153 AND nvl(trunc(user1.end_date), trunc(sysdate))
154 and not exists(
155 select 1
156 from ak_web_user_sec_attr_values ak2
157 where ak2.attribute_code='ICX_SUPPLIER_CONTACT_ID'
158 and ak2.web_user_id=ak1.web_user_id
159 and ak2.ATTRIBUTE_APPLICATION_ID=177)
160 and not exists(
161 select 1
162 from ak_web_user_sec_attr_values ak3
163 where ak3.attribute_code='ICX_SUPPLIER_SITE_ID'
164 and ak3.web_user_id=ak1.web_user_id
165 and ak3.ATTRIBUTE_APPLICATION_ID=177)
166 order by user1.user_name;
167
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
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
180 and ak1.ATTRIBUTE_APPLICATION_ID=177
181 and ak2.attribute_code='ICX_SUPPLIER_SITE_ID'
182 and ak2.number_value=v_vendor_site_id
183 and ak2.ATTRIBUTE_APPLICATION_ID=177
184 and ak1.web_user_id=ak2.web_user_id
185 and fur.responsibility_application_id = 177
186 and fur.user_id = user1.user_id
187 and fur.start_date < sysdate
188 and nvl(fur.end_date, sysdate + 1) >= sysdate
189 and trunc(sysdate)
190 BETWEEN nvl(trunc(user1.start_date), trunc(sysdate))
191 AND nvl(trunc(user1.end_date), trunc(sysdate))
192 and v_vendor_site_id not in
193 (select pvc.vendor_site_id
194 from po_vendor_contacts pvc
195 , ak_web_user_sec_attr_values ak3
196 where ak3.attribute_code='ICX_SUPPLIER_CONTACT_ID'
197 and ak3.web_user_id=ak1.web_user_id
198 and ak3.ATTRIBUTE_APPLICATION_ID=177
199 and ak3.number_value=pvc.vendor_contact_id)
200 order by user1.user_name;
201
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
211 and ak1.attribute_code='ICX_SUPPLIER_ORG_ID'
212 and ak1.number_value=v_vendor_id
213 and ak1.ATTRIBUTE_APPLICATION_ID=177
214 and ak3.attribute_code='ICX_SUPPLIER_CONTACT_ID'
215 and ak3.number_value=v_vendor_contact_id
216 and ak3.ATTRIBUTE_APPLICATION_ID=177
217 and ak1.web_user_id=ak3.web_user_id
218 and fur.responsibility_application_id = 177
219 and fur.user_id = user1.user_id
220 and fur.start_date < sysdate
221 and nvl(fur.end_date, sysdate + 1) >= sysdate
222 and trunc(sysdate)
223 BETWEEN nvl(trunc(user1.start_date), trunc(sysdate))
224 AND nvl(trunc(user1.end_date), trunc(sysdate))
225 order by user1.user_name;
226
227 BEGIN
228
229 -- Initialize API return status to success
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231 x_num_users := 0;
232 l_step := 0;
233 l_progress := '010';
234
235 begin
236 if (p_document_type in ('PO', 'PA')) THEN
237 l_progress := '020';
238 select DECODE(p_external_contact_id, null, vendor_contact_id, p_external_contact_id) vendor_contact_id
239 ,vendor_site_id, vendor_id, to_char(revision_num)
240 into l_vendor_contact_id
241 ,l_vendor_site_id, l_vendor_id, l_temp
242 from po_headers_all
243 where po_header_id = to_number(p_document_id);
244
245 elsif (p_document_type = 'RELEASE') THEN
246 l_progress := '030';
247
248 select poh.vendor_contact_id
249 ,poh.vendor_site_id, poh.vendor_id
250 , to_char(por.revision_num)
251 into l_vendor_contact_id
252 ,l_vendor_site_id, l_vendor_id
253 , l_temp
254 from po_releases por, po_headers poh
255 where por.po_release_id = to_number(p_document_id)
256 and por.po_header_id = poh.po_header_id;
257
258 elsif (p_document_type in ('RS')) then -- Bug 3197483
259 l_progress := '040';
260
261 select vendor_contact_id, vendor_site_id
262 , vendor_id, to_char(null)
263 into l_vendor_contact_id, l_vendor_site_id
264 , l_vendor_id, l_temp
265 from po_requisition_suppliers
266 where requisition_supplier_id = to_number(p_document_id);
267
268 elsif (p_document_type in ('RQ')) then -- Bug 3626250
269 l_progress := '050';
270
271 select vendor_contact_id, vendor_site_id, vendor_id, to_char(null)
272 into l_vendor_contact_id, l_vendor_site_id, l_vendor_id, l_temp
273 from po_requisition_lines_all
274 where requisition_line_id = to_number(p_document_id);
275 ELSE
276 l_progress := '060';
277 end if;
278 exception
279 when no_data_found then
280 l_vendor_contact_id := null;
281 l_vendor_site_id := null;
282 l_vendor_id:=null;
283 end;
284
285 l_progress := '070';
286
287 l_step := '1' ;
288 if(l_vendor_contact_id is not null) then
289 open vendor_contact_username
290 (l_vendor_id
291 , l_vendor_contact_id);
292 loop
293 fetch vendor_contact_username into l_user_name;
294 exit when vendor_contact_username%NOTFOUND;
295 if (l_vendor_id =
296 get_po_vendor_id_for_user (l_user_name)) then
297 if(l_namelist is null) then
298 l_num_users := l_num_users + 1;
299 l_namelist_for_sql := ''''||l_user_name||'''';
300 l_namelist:= l_user_name;
301 else
302 l_num_users := l_num_users + 1;
303 l_namelist_for_sql :=
304 l_namelist_for_sql||','||''''||l_user_name||'''';
305 l_namelist:=l_namelist||' '||l_user_name;
306 end if;
307 x_supplier_user_tbl(l_num_users) := l_user_name;
308 end if;
309 end loop;
310 close vendor_contact_username;
311
312 end if; -- vendor id is null
313
314 l_step := '2'||l_namelist ;
315 if(l_namelist IS NULL) then
316
317 open vendor_site_username
318 (l_vendor_id
319 , l_vendor_site_id);
320 loop
321 fetch vendor_site_username into l_user_name;
322 exit when vendor_site_username%NOTFOUND;
323 if (l_vendor_id =
324 get_po_vendor_id_for_user (l_user_name)) then
325 if(l_namelist is null) then
326 l_num_users := l_num_users + 1;
327 l_namelist_for_sql := ''''||l_user_name||'''';
328 l_namelist:= l_user_name;
329 else
330 l_num_users := l_num_users + 1;
331 l_namelist_for_sql :=
332 l_namelist_for_sql||','||''''||l_user_name||'''';
333 l_namelist:=l_namelist||' '||l_user_name;
334 end if;
335 x_supplier_user_tbl(l_num_users) := l_user_name;
336 end if;
337 end loop;
338 close vendor_site_username;
339
340 end if; -- vendor id is null
341
342 l_progress := '080';
343 l_step := '3'||l_namelist ;
344 if(l_namelist IS NULL) then
345
346 open vendor_only_username(l_vendor_id);
347 loop
348 fetch vendor_only_username into l_user_name;
349 exit when vendor_only_username%NOTFOUND;
350 if (l_vendor_id =
351 get_po_vendor_id_for_user (l_user_name)) then
352 if(l_namelist is null) then
353 l_num_users := l_num_users + 1;
354 l_namelist_for_sql := ''''||l_user_name||'''';
355 l_namelist:= l_user_name;
356 else
357 l_num_users := l_num_users + 1;
358 l_namelist_for_sql :=
359 l_namelist_for_sql||','||''''||l_user_name||'''';
360 l_namelist:=l_namelist||' '||l_user_name;
361 end if;
362 x_supplier_user_tbl(l_num_users) := l_user_name;
363 end if;
364 end loop;
365 close vendor_only_username;
366
367 end if; -- vendor id is null
368 l_progress := '090';
369 -- populate the out parameters
370 x_supplier_userlist := l_namelist;
371 x_supplier_userlist_for_sql := l_namelist_for_sql;
372 x_num_users := l_num_users;
373 x_vendor_id := l_vendor_id;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
379 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
380 IF (g_fnd_debug='Y') THEN
381 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
382 FND_LOG.string(log_level => FND_LOG.level_unexpected
383 ,module => g_module_prefix ||l_api_name || ' ' ||l_progress
384 ,message => SQLERRM);
385 END IF;
386
387
388 END IF;
389 END IF;
390
391 END get_supplier_userlist;
392
393
394 END PO_VENDORS_PVT ;
[Home] [Help]