1 PACKAGE BODY AP_SUPPLIER_INFO_PKG AS
2 /* $Header: apsupinfb.pls 120.1.12010000.3 2008/11/18 10:49:48 anarun noship $ */
3 ------------------------------------------------------------------------------
4 -- Global Variables --
5 ------------------------------------------------------------------------------
6 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP_SUPPLIER_INFO_PKG';
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10
11 -- Procedure Definitions
12
13 ------------------------------------------------------------------------------
14 ----------------------------- Supplier_Details -------------------------------
15 ------------------------------------------------------------------------------
16 /* Fetches the details of Supplier, Site and Contact
17 * This is an overloaded procedure
18 * Parameters : i_vendor_id - vendor_id of supplier
19 * i_vendor_site_id - vendor_site_id of supplier site
20 * i_vendor_contact_id - vendor_contact_id
21 * o_supplier_info - Supplier details are populated into
22 * this parameter
23 * o_success - If a valid combination of vendor_id,
24 * vendor_site_id and vendor_contact_id
25 * was not passed then o_success is set
26 * to FALSE
27 *
28 * Logic :
29 * 1) Validation check and query string formulation
30 * First check if the vendor_id exists in ap_suppliers.
31 * If vendor_site_id is available then
32 * a) check if it is valid
33 * b) append it to site query string
34 * c) If vendor_contact_id is available then
35 * i) check if this contact is valid for this combination of
36 * ( vendor_id, vendor_site_id )
37 * ii) append it to contact query string. Here we do not need to
38 * consider it for site query string because while opening
39 * contact cursor, if vendor_site_id is available then we will
40 * use it as a condition
41 * End If
42 * Else
43 * a) If vendor_contact_id is available then
44 * i) check if this contact is valid for this combination of
45 * ( vendor_id, vendor_site_id )
46 * ii) Get the list of vendor_site_id for this contact and add
47 * this list as a IN condition of site query string
48 * End If
49 * End If
50 * 2) Populate the suplier details
51 * 3) Loop through sites and get site details
52 * 4) Loop through contacts for each site to get contact details
53 *
54 */
55
56 PROCEDURE Supplier_Details(
57 i_vendor_id IN NUMBER ,
58 i_vendor_site_id IN NUMBER DEFAULT NULL,
59 i_vendor_contact_id IN NUMBER DEFAULT NULL,
60 o_supplier_info OUT NOCOPY t_supplier_info_rec,
61 o_success OUT NOCOPY BOOLEAN
62 )
63 IS
64 TYPE t_site_refcur IS REF CURSOR;
65 TYPE t_contact_refcur IS REF CURSOR;
66 TYPE t_site_tab IS TABLE OF ap_supplier_sites_all%ROWTYPE
67 INDEX BY BINARY_INTEGER;
68
69 c_site t_site_refcur;
70 c_contact t_contact_refcur;
71 l_site_tab t_site_tab;
72 l_contact_tab t_contacts_tab;
73 l_site_sel VARCHAR2(1000) := 'SELECT * FROM ap_supplier_sites_all ';
74 l_site_where VARCHAR2(1000) := ' WHERE vendor_id = '
75 || to_char(i_vendor_id);
76 l_contact_sel VARCHAR2(1000) := 'SELECT * FROM po_vendor_contacts ';
77 l_contact_where VARCHAR2(1000) := ' WHERE vendor_id = '
78 || to_char(i_vendor_id);
79 l_site_indx NUMBER;
80 l_contact_indx NUMBER;
81 l_check_vid NUMBER;
82 l_check_vsid NUMBER;
83 l_check_vcid NUMBER;
84 l_api_name CONSTANT VARCHAR2(200) := 'Supplier_Details';
85 l_debug_info VARCHAR2(2000);
86 BEGIN
87
88 l_debug_info := 'Called with parameters : i_vendor_id = '
89 || to_char(i_vendor_id) || ', i_vendor_site_id = '
90 || to_char(i_vendor_site_id) || ', i_vendor_contact_id = '
91 || to_char(i_vendor_contact_id);
92 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
93 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name,
94 l_debug_info);
95 END IF;
96
97 /* ========= Validation Check and query string begin ==========
98 | This section of code checks if the given combination of |
99 | vendor_id, vendor_site_id and vendor_contact_id is valid |
100 | or not and sets o_success to FALSE if it is invalid. |
101 | Simultaneously, it forms the query string. |
102 ============================================================ */
103
104 o_success := TRUE;
105
106 SELECT count(1)
107 INTO l_check_vid
108 FROM ap_suppliers
109 WHERE vendor_id = i_vendor_id;
110
111 IF l_check_vid = 0 THEN
112 o_success := FALSE;
113 l_debug_info := 'Invalid vendor_id';
114 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
115 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name,
116 l_debug_info);
117 END IF;
118 ELSE
119 IF i_vendor_site_id IS NOT NULL THEN
120 SELECT count(1)
121 INTO l_check_vsid
122 FROM ap_supplier_sites_all
123 WHERE vendor_id = i_vendor_id
124 AND vendor_site_id = i_vendor_site_id;
125
126 IF l_check_vsid = 0 THEN
127 o_success := FALSE;
128 l_debug_info := 'Invalid combination of (vendor_id, '
129 || 'vendor_site_id)';
130 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
131 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
132 l_api_name, l_debug_info);
133 END IF;
134 ELSE
135 l_site_where := l_site_where
136 || ' AND vendor_site_id = '
137 || to_char(i_vendor_site_id);
138 END IF;
139
140 IF i_vendor_contact_id IS NOT NULL THEN
141 SELECT count(1)
142 INTO l_check_vcid
143 FROM po_vendor_contacts
144 WHERE vendor_id = i_vendor_id
145 AND vendor_site_id = i_vendor_site_id
146 AND vendor_contact_id = i_vendor_contact_id;
147
148 IF l_check_vcid = 0 THEN
149 o_success := FALSE;
150 l_debug_info := 'Invalid combination of (vendor_id, '
151 || 'vendor_site_id, vendor_contact_id)';
152 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
153 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME
154 || l_api_name, l_debug_info);
155 END IF;
156 ELSE
157 l_contact_where := l_contact_where
158 || ' AND vendor_contact_id = '
159 || to_char(i_vendor_contact_id);
160 END IF;
161 END IF;
162 ELSE
163 IF i_vendor_contact_id IS NOT NULL THEN
164 SELECT count(1)
165 INTO l_check_vcid
166 FROM po_vendor_contacts
167 WHERE vendor_id = i_vendor_id
168 AND vendor_contact_id = i_vendor_contact_id;
169
170 IF l_check_vcid = 0 THEN
171 o_success := FALSE;
172 l_debug_info := 'Invalid combination of (vendor_id, '
173 || 'vendor_contact_id)';
174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
175 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME
176 || l_api_name, l_debug_info);
177 END IF;
178 ELSE
179 l_site_where := l_site_where
180 || ' AND vendor_site_id IN ( '
181 || 'SELECT vendor_site_id '
182 || 'FROM PO_VENDOR_CONTACTS '
183 || 'WHERE vendor_contact_id = '
184 || to_char( i_vendor_contact_id ) || ' )' ;
185
186 l_contact_where := l_contact_where
187 || ' AND vendor_contact_id = '
188 || to_char(i_vendor_contact_id);
189 END IF;
190 END IF;
191 END IF;
192 END IF;
193
194 IF o_success = FALSE THEN
195 return;
196 END IF;
197 /* ============ Validation Check and query string end ============= */
198
199 l_debug_info := 'l_site_where = ' || l_site_where ;
200 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name,
202 l_debug_info);
203 END IF;
204
205 l_debug_info := 'l_contact_where = ' || l_contact_where ;
206 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
207 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name,
208 l_debug_info);
209 END IF;
210
211 SELECT *
212 INTO o_supplier_info.supp_rec
213 FROM ap_suppliers
214 WHERE vendor_id = i_vendor_id;
215
216 OPEN c_site FOR l_site_sel || l_site_where ;
217 LOOP
218 FETCH c_site BULK COLLECT INTO l_site_tab LIMIT 100;
219 EXIT WHEN l_site_tab.COUNT = 0;
220
221 FOR l_site_loop_indx IN 1..l_site_tab.COUNT
222 LOOP
223 l_site_indx := NVL( o_supplier_info.site_con_tab.LAST, 0) + 1;
224 o_supplier_info.site_con_tab(l_site_indx).site_rec
225 := l_site_tab(l_site_loop_indx);
226 OPEN c_contact FOR l_contact_sel || l_contact_where
227 || ' AND vendor_site_id = '
228 || to_char(l_site_tab(l_site_loop_indx).vendor_site_id);
229 LOOP
230 FETCH c_contact BULK COLLECT INTO l_contact_tab LIMIT 100;
231 EXIT WHEN l_contact_tab.COUNT = 0;
232
233 FOR l_contact_loop_indx IN 1..l_contact_tab.COUNT
234 LOOP
235 l_contact_indx := NVL(
236 o_supplier_info.site_con_tab(l_site_indx).contact_tab.LAST,
237 0) + 1;
238 o_supplier_info.site_con_tab(l_site_indx).
239 contact_tab(l_contact_indx) :=
240 l_contact_tab(l_contact_loop_indx);
241 END LOOP;
242 l_contact_tab.DELETE;
243 END LOOP;
244 CLOSE c_contact;
245 END LOOP;
246 l_site_tab.DELETE;
247 END LOOP;
248 CLOSE c_site;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF (SQLCODE <> -20001 ) THEN
253 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
254 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
255 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
256 END IF;
257
258 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
259 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME || l_api_name,
260 SQLERRM);
261 END IF;
262
263 APP_EXCEPTION.RAISE_EXCEPTION;
264
265 END Supplier_Details;
266
267 ------------------------------------------------------------------------------
268 ----------------------------- Supplier_Details -------------------------------
269 ------------------------------------------------------------------------------
270 /* Fetches the details of Supplier, Site and Contact for a particular range
271 * of vendor_id.
272 * This is an overloaded procedure. The parameter o_supplier_info_tab can be
273 * used to distinguish between the calls. Here, both i_from_vendor_id and
274 * i_to_vendor_id are mandatory parameters.
275 * This procedure should be called with a reasonable range of vendor_id based
276 * on the resources available at customer's instance to avoid performance
277 * issues. If there is some performance issue then the only way to fix it will
278 * be to provide a lesser range of vendor_id.
279 *
280 * Parameters : i_from_vendor_id - Start range of vendor_id
281 * i_to_vendor_id - End range of vendor_id
282 * o_supplier_info_tab - Supplier details are populated into
283 * this parameter.
284 * o_success - If no vendor exists in the range of
285 * vendor_id for which this procedure is
286 * called then o_success is set to FALSE
287 *
288 */
289
290 PROCEDURE Supplier_Details(
291 i_from_vendor_id IN NUMBER ,
292 i_to_vendor_id IN NUMBER ,
293 o_supplier_info_tab OUT NOCOPY t_supplier_info_tab,
294 o_success OUT NOCOPY BOOLEAN
295 )
296 IS
297 TYPE t_site_refcur IS REF CURSOR;
298 TYPE t_contact_refcur IS REF CURSOR;
299 TYPE t_supplier_tab IS TABLE OF ap_suppliers%ROWTYPE
300 INDEX BY BINARY_INTEGER;
301 TYPE t_site_tab IS TABLE OF ap_supplier_sites_all%ROWTYPE
302 INDEX BY BINARY_INTEGER;
303
304 CURSOR c_supplier
305 IS
306 SELECT *
307 FROM ap_suppliers
308 WHERE vendor_id BETWEEN i_from_vendor_id AND i_to_vendor_id;
309
310 c_site t_site_refcur;
311 c_contact t_contact_refcur;
312 l_supplier_tab t_supplier_tab;
313 l_site_tab t_site_tab;
314 l_contact_tab t_contacts_tab;
315 l_site_sel VARCHAR2(1000) := 'SELECT * FROM ap_supplier_sites_all ';
316 l_site_where VARCHAR2(1000) := ' WHERE vendor_id = ' ;
317 l_contact_sel VARCHAR2(1000) := 'SELECT * FROM po_vendor_contacts ';
318 l_contact_where VARCHAR2(1000) := ' WHERE vendor_id = ' ;
319 l_supplier_indx NUMBER;
320 l_site_indx NUMBER;
321 l_contact_indx NUMBER;
322 l_check_vid NUMBER;
323 l_api_name CONSTANT VARCHAR2(200) := 'Supplier_Details';
324 l_debug_info VARCHAR2(2000);
325 BEGIN
326
327 l_debug_info := 'Called with parameters : i_from_vendor_id = '
328 || to_char(i_from_vendor_id) || ', i_to_vendor_id = '
329 || to_char(i_to_vendor_id) ;
330 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name,
332 l_debug_info);
333 END IF;
334
335 SELECT count(1)
336 INTO l_check_vid
337 FROM ap_suppliers
338 WHERE vendor_id BETWEEN i_from_vendor_id AND i_to_vendor_id;
339
340 IF l_check_vid = 0 THEN
341 o_success := FALSE;
342 l_debug_info := 'No vendor exists within this range of vendor_id';
343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
345 l_api_name, l_debug_info);
346 END IF;
347 ELSE
348 OPEN c_supplier;
349 LOOP
350 FETCH c_supplier BULK COLLECT INTO l_supplier_tab LIMIT 100;
351 EXIT WHEN l_supplier_tab.COUNT = 0;
352
353 FOR l_supp_loop_indx IN 1..l_supplier_tab.COUNT
354 LOOP
355 l_supplier_indx := NVL( o_supplier_info_tab.LAST, 0) + 1;
356 o_supplier_info_tab(l_supplier_indx).supp_rec :=
357 l_supplier_tab(l_supp_loop_indx);
358
359 OPEN c_site FOR l_site_sel || l_site_where
360 ||to_char(l_supplier_tab(l_supp_loop_indx).vendor_id);
361 LOOP
362 FETCH c_site BULK COLLECT INTO l_site_tab LIMIT 100;
363 EXIT WHEN l_site_tab.COUNT = 0;
364
365 FOR l_site_loop_indx IN 1..l_site_tab.COUNT
366 LOOP
367 l_site_indx :=
368 NVL( o_supplier_info_tab(l_supplier_indx).
369 site_con_tab.LAST, 0) + 1;
370 o_supplier_info_tab(l_supplier_indx).
371 site_con_tab(l_site_indx).site_rec
372 := l_site_tab(l_site_loop_indx);
373 OPEN c_contact FOR l_contact_sel || l_contact_where
374 || to_char( l_supplier_tab(l_supp_loop_indx).vendor_id )
375 || ' AND vendor_site_id = '
376 ||to_char(l_site_tab(l_site_loop_indx).vendor_site_id);
377 LOOP
378 FETCH c_contact
379 BULK COLLECT INTO l_contact_tab LIMIT 100;
380 EXIT WHEN l_contact_tab.COUNT = 0;
381
382 FOR l_contact_loop_indx IN 1..l_contact_tab.COUNT
383 LOOP
384 l_contact_indx := NVL(
385 o_supplier_info_tab(l_supplier_indx).
386 site_con_tab(l_site_indx).contact_tab.LAST,
387 0) + 1;
388 o_supplier_info_tab(l_supplier_indx).
389 site_con_tab(l_site_indx).
390 contact_tab(l_contact_indx) :=
391 l_contact_tab(l_contact_loop_indx);
392 END LOOP;
393 l_contact_tab.DELETE;
394 END LOOP;
395 CLOSE c_contact;
396 END LOOP;
397 l_site_tab.DELETE;
398 END LOOP;
399 CLOSE c_site;
400 END LOOP;
401 l_supplier_tab.DELETE;
402 END LOOP;
403 CLOSE c_supplier;
404 o_success := TRUE;
405 END IF;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 IF (SQLCODE <> -20001 ) THEN
410 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
411 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
412 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
413 END IF;
414
415 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
416 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME || l_api_name,
417 SQLERRM);
418 END IF;
419
420 APP_EXCEPTION.RAISE_EXCEPTION;
421
422 END Supplier_Details;
423
424 END AP_SUPPLIER_INFO_PKG;
[Home] [Help]