1 PACKAGE BODY FUN_TRADING_RELATION AS
2 /* $Header: funtraderelb.pls 120.12 2006/05/15 14:04:49 ashikuma noship $ */
3
4 l_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 l_state_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 l_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 l_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 l_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 l_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 l_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 l_path CONSTANT VARCHAR2(50) := 'FUN.PLSQL.funtraderelb.FUN_TRADING_RELATION_PKG.';
12
13
14 /* Internal routine.. Will be called only by get_customer and get_supplier, to validate the source*/
15 FUNCTION validate_source (p_source IN VARCHAR2)
16 RETURN boolean IS
17 l_RETURN NUMBER;
18 p_path CONSTANT VARCHAR2(50) := 'VALIDATE_SOURCE';
19 CURSOR c_source (p_source VARCHAR2) IS
20 SELECT 1
21 FROM FND_LOOKUP_VALUES
22 WHERE lookup_type = 'FUN_TRADE_REL_SOURCE'
23 AND language = USERENV('LANG')
24 AND lookup_code = p_source;
25 BEGIN
26 OPEN c_source (p_source);
27 FETCH c_source INto l_RETURN;
28 IF c_source%NOTFOUND THEN
29 RETURN false ;
30 else
31 RETURN true ;
32 END IF;
33 CLOSE c_source;
34 EXCEPTION
35 WHEN others THEN
36 IF (l_unexp_level >= l_debug_level ) THEN
37 FND_LOG.STRING (l_unexp_level , l_path || p_path , 'Unexpected Error4' );
38 END IF;
39 RETURN false;
40 app_exception.raise_exception;
41 END validate_source; -- End of validate_source
42
43
44
45 /* Internal routine.. Will be called only by get_customer and get_supplier*/
46 PROCEDURE get_relation (
47 p_source IN VARCHAR2,
48 p_type IN VARCHAR2,
49 p_trans_le_id IN NUMBER ,
50 p_tp_le_id IN NUMBER ,
51 p_trans_org_id IN NUMBER := NULL,
52 p_tp_org_id IN NUMBER := NULL,
53 p_trans_organization_id IN NUMBER,
54 p_tp_organization_id IN NUMBER,
55 x_relation_id OUT NOCOPY NUMBER,
56 x_success OUT NOCOPY VARCHAR2,
57 x_msg_data OUT NOCOPY VARCHAR2
58 )
59 IS
60 p_path CONSTANT VARCHAR2(50) := 'GET_RELATION';
61
62 CURSOR c_cust_relation(p_source VARCHAR2,
63 p_trans_le_id NUMBER,
64 p_tp_le_id NUMBER,
65 p_trans_org_id NUMBER,
66 p_tp_org_id NUMBER,
67 p_trans_organization_id NUMBER,
68 p_tp_organization_id NUMBER ) IS
69 SELECT rel.relation_id
70 FROM FUN_TRADE_RELATIONS rel,
71 fun_customer_maps cust
72 WHERE Nvl(rel.source,'INTERCOMPANY') = p_source
73 AND rel.transaction_le_id = p_trans_le_id
74 AND rel.tp_le_id = p_tp_le_id
75 AND nvl(rel.transaction_org_id , -1) = nvl(p_trans_org_id, -1)
76 AND nvl(rel.tp_org_id,-1) = nvl(p_tp_org_id,-1)
77 AND rel.transaction_organization_id=p_trans_organization_id
78 AND rel.tp_organization_id=p_tp_organization_id
79 AND rel.relation_id = cust.relation_id;
80
81 CURSOR c_supp_relation(p_source VARCHAR2,
82 p_trans_le_id NUMBER,
83 p_tp_le_id NUMBER,
84 p_trans_org_id NUMBER,
85 p_tp_org_id NUMBER,
86 p_trans_organization_id NUMBER,
87 p_tp_organization_id NUMBER ) IS
88 SELECT rel.relation_id
89 FROM FUN_TRADE_RELATIONS rel,
90 fun_supplier_maps supp
91 WHERE Nvl(rel.source,'INTERCOMPANY') = p_source
92 AND rel.transaction_le_id = p_trans_le_id
93 AND rel.tp_le_id = p_tp_le_id
94 AND nvl(rel.transaction_org_id , -1) = nvl(p_trans_org_id, -1)
95 AND nvl(rel.tp_org_id,-1) = nvl(p_tp_org_id,-1)
96 AND rel.transaction_organization_id=p_trans_organization_id
97 AND rel.tp_organization_id=p_tp_organization_id
98 AND rel.relation_id = supp.relation_id;
99 BEGIN
100 x_success := 'Y';
101
102 IF p_type = 'SUPPLIER'
103 THEN
104 OPEN c_supp_relation (p_source,
105 p_trans_le_id,
106 p_tp_le_id,
107 p_trans_org_id ,
108 p_tp_org_id,
109 p_trans_organization_id,
110 p_tp_organization_id );
111 FETCH c_supp_relation INTO x_relation_id;
112 CLOSE c_supp_relation;
113 ELSIF p_type = 'CUSTOMER'
114 THEN
115 OPEN c_cust_relation (p_source,
116 p_trans_le_id,
117 p_tp_le_id,
118 p_trans_org_id ,
119 p_tp_org_id,
120 p_trans_organization_id,
121 p_tp_organization_id );
122 FETCH c_cust_relation INTO x_relation_id;
123 CLOSE c_cust_relation;
124
125 END IF;
126 EXCEPTION
127 WHEN others THEN
128 x_success := 'N';
129 x_msg_data := 'Unexpected error1' || SQLERRM;
130 IF (l_excep_level >= l_debug_level ) THEN
131 FND_LOG.STRING (l_excep_level , l_path || p_path , x_msg_data );
132 END IF;
133 app_exception.raise_exception;
134 END get_relation;
135
136 /* GET_CUSTOMER RETURNs the customer information, relationship should be derived
137 on the hierarchial business logic using get_relation routine. */
138
139 FUNCTION get_customer (
140 p_source IN VARCHAR2,
141 p_trans_le_id IN NUMBER ,
142 p_tp_le_id IN NUMBER ,
143 p_trans_org_id IN NUMBER := NULL,
144 p_tp_org_id IN NUMBER := NULL,
145 p_trans_organization_id IN NUMBER,
146 p_tp_organization_id IN NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
148 x_cust_acct_id OUT NOCOPY NUMBER,
149 x_cust_acct_site_id OUT NOCOPY NUMBER,
150 x_site_use_id OUT NOCOPY NUMBER
151 ) RETURN boolean
152 IS
153 p_path CONSTANT VARCHAR2(50) := 'GET_CUSTOMER';
154 l_success VARCHAR2(1);
155 l_msg_data VARCHAR2(1000);
156 x_relation_id number;
157 x_customer_party_id number;
158
159 CURSOR c_cust_acct(x_relation_id NUMBER ) IS
160 SELECT mp.cust_account_id ,
161 mp.site_use_id,
162 site.cust_acct_site_id
163 FROM fun_customer_maps mp,
164 hz_cust_acct_sites_all site,
165 hz_cust_site_uses_all use
166 WHERE relation_id = x_relation_id
167 AND site.cust_account_id = mp.cust_account_id
168 AND site.cust_acct_site_id = use.cust_acct_site_id
169 AND use.site_use_id = mp.site_use_id
170 AND use.status = 'A'
171 AND site.status = 'A';
172
173 CURSOR c_site_use (p_trans_org_id NUMBER, x_cust_acct_id NUMBER) IS
174 SELECT site_use_id , cust_acct_site_id
175 FROM hz_cust_site_uses_all
176 WHERE cust_acct_site_id
177 IN ( SELECT cust_acct_site_id FROM hz_cust_acct_sites_all WHERE cust_account_id = x_cust_acct_id AND org_id = p_trans_org_id)
178 AND org_id = p_trans_org_id
179 AND site_use_code = 'BILL_TO'
180 AND status = 'A'
181 AND primary_flag = 'Y';
182
183 CURSOR c_cust_party (x_cust_acct_id NUMBER ) IS
184 SELECT party_id
185 FROM hz_cust_accounts_all
186 WHERE cust_account_id = x_cust_acct_id
187 AND status = 'A';
188
189 BEGIN
190 IF (p_trans_le_id IS NULL or p_tp_le_id IS NULL or p_source IS NULL) THEN
191 x_msg_data := 'Transacting Legal Entity,Trading Partner Legal Entity AND Source are mandatory parameters.' ;
192 IF (l_error_level >= l_debug_level ) THEN
193 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
194 END IF;
195 RETURN false;
196 END IF;
197
198 IF NOT validate_source (p_source) THEN
199 x_msg_data := 'Source is NOT a valid parameter' ;
200 IF (l_error_level >= l_debug_level ) THEN
201 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
202 END IF;
203 RETURN false;
204 END IF;
205
206 IF ( p_trans_org_id IS NOT NULL ) THEN
207 IF (p_tp_org_id IS NOT NULL ) THEN
208 GET_RELATION (p_source, 'CUSTOMER', p_trans_le_id , p_tp_le_id, p_trans_org_id, p_tp_org_id, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
209 END IF;
210 IF (x_relation_id IS NULL ) THEN
211 GET_RELATION (p_source, 'CUSTOMER', p_trans_le_id , p_tp_le_id, p_trans_org_id, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
212 END IF;
213 IF (x_relation_id IS NULL ) THEN
214 GET_RELATION (p_source, 'CUSTOMER', p_trans_le_id , p_tp_le_id, NULL, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
215 END IF;
216 IF (x_relation_id IS NULL ) THEN
217 IF (p_tp_org_id IS NOT NULL ) THEN
218 GET_RELATION ('ALL', 'CUSTOMER', p_trans_le_id , p_tp_le_id, p_trans_org_id, p_tp_org_id, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
219 END IF;
220 IF (x_relation_id IS NULL ) THEN
221 GET_RELATION ('ALL', 'CUSTOMER', p_trans_le_id , p_tp_le_id, p_trans_org_id, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
222 END IF;
223 IF (x_relation_id IS NULL ) THEN
224 GET_RELATION ('ALL', 'CUSTOMER', p_trans_le_id , p_tp_le_id, NULL, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
225 END IF;
226 END IF;
227 IF (x_relation_id IS NULL ) THEN
228 x_msg_data := 'Relation does not exist for the given pair of Transacting and Trading Partners';
229 IF (l_error_level >= l_debug_level ) THEN
230 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
231 END IF;
232 RETURN false;
233 else
234 OPEN c_cust_acct (x_relation_id);
235 FETCH c_cust_acct INto x_cust_acct_id,
236 x_site_use_id,
237 x_cust_acct_site_id;
238 IF c_cust_acct%NOTFOUND THEN
239 x_msg_data := 'Customer mapping is not present for the relation id ' || x_relation_id;
240 IF (l_error_level >= l_debug_level ) THEN
241 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
242 END IF;
243 CLOSE c_cust_acct;
244 RETURN FALSE;
245 END IF;
246 CLOSE c_cust_acct;
247
248 IF (x_cust_acct_id IS NOT NULL) THEN
249 OPEN c_cust_party (x_cust_acct_id );
250 FETCH c_cust_party INTO x_customer_party_id ;
251 CLOSE c_cust_party;
252 IF x_customer_party_id IS NULL THEN
253 x_msg_data := 'The Customer defined in the relation ' || x_relation_id || ' is inactive or does not have a customer party id ';
254 IF (l_error_level >= l_debug_level ) THEN
255 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
256 END IF;
257 RETURN false;
258 END IF;
259
260 IF (x_site_use_id IS NULL) THEN
261 OPEN c_site_use (p_trans_org_id , x_cust_acct_id);
262 FETCH c_site_use INto x_site_use_id, x_cust_acct_site_id;
263 CLOSE c_site_use;
264 END IF;
265
266 IF x_site_use_id IS NULL THEN
267 x_msg_data := 'Site is not defined for the customer mapping on the relation id '|| x_relation_id;
268 IF (l_error_level >= l_debug_level ) THEN
269 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
270 END IF;
271 RETURN false;
272 END IF;
273 RETURN true;
274 else
275 x_msg_data := 'The Customer defined in the relation ' || x_relation_id || ' does not have a customer account' ;
276 IF (l_error_level >= l_debug_level ) THEN
277 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
278 END IF;
279 RETURN false;
280 END IF;
281 END IF; /* Successfully got all data*/
282 else
283 /* only LE info*/
284 x_msg_data := 'Transacting Operating Unit is not passed, so unable to get Customer details ';
285 IF (l_error_level >= l_debug_level ) THEN
286 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
287 END IF;
288 RETURN false;
289 END IF;
290 EXCEPTION
291 WHEN others THEN
292 x_msg_data := 'Unexpected Error2';
293 IF (l_unexp_level >= l_debug_level ) THEN
294 FND_LOG.STRING (l_unexp_level , l_path || p_path , x_msg_data );
295 END IF;
296 RETURN false;
297 app_exception.raise_exception;
298 END get_customer;
299
300 /* GET SUPPLIER RETURNs the supplier information, relationship should be derived
301 on the hierarchial business logic using get_relation routine. */
302 -- Added p_trx_date to check if supplier is active - Bug 5176225
303 FUNCTION get_supplier (
304 p_source IN VARCHAR2,
305 p_trans_le_id IN NUMBER ,
306 p_tp_le_id IN NUMBER ,
307 p_trans_org_id IN NUMBER := NULL,
308 p_tp_org_id IN NUMBER := NULL,
309 p_trans_organization_id IN NUMBER,
310 p_tp_organization_id IN NUMBER,
311 p_trx_date IN DATE,
312 x_msg_data OUT NOCOPY VARCHAR2,
313 x_vendor_id OUT NOCOPY NUMBER,
314 x_pay_site_id OUT NOCOPY NUMBER
315 ) RETURN boolean
316 IS
317 p_path CONSTANT VARCHAR2(50) := 'GET_SUPPLIER';
318 l_success VARCHAR2(1);
319 l_msg_data VARCHAR2(1000);
320 x_relation_id number;
321
322 CURSOR c_vendor_info (x_relation_id NUMBER,
323 p_trx_date DATE) IS
324 SELECT rel.vendor_id, rel.vendor_site_id
325 FROM fun_supplier_maps rel,
326 ap_suppliers supp,
327 ap_supplier_sites_all site
328 WHERE relation_id = x_relation_id
329 AND supp.vendor_id = rel.vendor_id
330 AND site.vendor_site_id = rel.vendor_site_id
331 AND supp.vendor_id = site.vendor_id
332 AND p_trx_date BETWEEN Nvl(TRUNC(supp.start_date_active), p_trx_date) AND Nvl(TRUNC(supp.end_date_active), p_trx_date)
333 AND Nvl(TRUNC(site.inactive_date), p_trx_date) >= p_trx_date;
334
335
336 CURSOR c_vendor_site_info (x_vendor_id NUMBER, p_trans_org_id NUMBER ) IS
337 SELECT vendor_site_id
338 FROM po_vendor_sites_all
339 WHERE vendor_id = x_vendor_id AND (pay_site_flag = 'Y' or primary_pay_site_flag = 'Y' )
340 AND org_id = p_trans_org_id ;
341
342 BEGIN
343 IF (p_trans_le_id IS NULL or p_tp_le_id IS NULL or p_source IS NULL) THEN
344 x_msg_data := 'Transacting Legal Entity,Trading Partner Legal Entity and Source are mandatory parameters.' ;
345 IF (l_error_level >= l_debug_level ) THEN
346 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
347 END IF;
348 RETURN false;
349 END IF;
350
351 IF NOT validate_source (p_source) THEN
352 x_msg_data := 'Source is NOT a valid parameter' ;
353 IF (l_error_level >= l_debug_level ) THEN
354 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
355 END IF;
356 RETURN false;
357 END IF;
358
359
360
361 IF (p_trans_org_id IS NOT NULL) THEN
362 IF (p_tp_org_id IS NOT NULL) THEN
366 GET_RELATION (p_source, 'SUPPLIER', p_trans_le_id , p_tp_le_id, p_trans_org_id, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
363 GET_RELATION (p_source, 'SUPPLIER', p_trans_le_id , p_tp_le_id, p_trans_org_id, p_tp_org_id, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
364 END IF;
365 IF (x_relation_id IS NULL) THEN
367 END IF;
368 IF (x_relation_id IS NULL) THEN
369 GET_RELATION (p_source, 'SUPPLIER', p_trans_le_id , p_tp_le_id, NULL, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
370 END IF;
371 IF (x_relation_id IS NULL) THEN
372 IF (p_tp_org_id IS NOT NULL) THEN
373 GET_RELATION ('ALL', 'SUPPLIER', p_trans_le_id , p_tp_le_id, p_trans_org_id, p_tp_org_id, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
374 END IF;
375 IF (x_relation_id IS NULL) THEN
376 GET_RELATION ('ALL', 'SUPPLIER', p_trans_le_id , p_tp_le_id, p_trans_org_id, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
377 END IF;
378 IF (x_relation_id IS NULL) THEN
379 GET_RELATION ('ALL', 'SUPPLIER', p_trans_le_id , p_tp_le_id, NULL, NULL, p_trans_organization_id, p_tp_organization_id, x_relation_id, l_success, l_msg_data );
380 END IF;
381 END IF;
382 IF (x_relation_id IS NULL) THEN
383 x_msg_data := 'Relation does not exist for the given pair of Transacting AND Trading Partners';
384 IF (l_error_level >= l_debug_level ) THEN
385 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
386 END IF;
387 RETURN false;
388 else
389 OPEN c_vendor_info ( x_relation_id, p_trx_date);
390 FETCH c_vendor_info INto x_vendor_id, x_pay_site_id;
391 IF c_vendor_info%NOTFOUND THEN
392 x_msg_data := 'Valid Supplier Mapping is not present for the relation id ' || x_relation_id;
393 IF (l_error_level >= l_debug_level ) THEN
394 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
395 END IF;
396 CLOSE c_vendor_info;
397 RETURN FALSE;
398 END IF;
399 CLOSE c_vendor_info;
400 IF x_vendor_id IS NOT NULL THEN
401
402 IF ( x_pay_site_id IS NULL) THEN
403 OPEN c_vendor_site_info (x_vendor_id , p_trans_org_id);
404 FETCH c_vendor_site_info INto x_pay_site_id;
405 CLOSE c_vendor_site_info;
406 IF x_pay_site_id IS NULL THEN
407 x_msg_data := 'A valid Pay Site is not available for the Supplier';
408 IF (l_error_level >= l_debug_level ) THEN
409 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
410 END IF;
411 RETURN false;
412 END IF;
413 END IF;
414 else
415 x_msg_data := 'Supplier info is not available';
416 IF (l_error_level >= l_debug_level ) THEN
417 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
418 END IF;
419 RETURN false;
420 END IF;
421 END IF;
422 RETURN true;
423 else /*Only LE info available*/
424 x_msg_data := 'Transacting Operating Unit is not passed, so unable to get Supplier details ';
425 IF (l_error_level >= l_debug_level ) THEN
426 FND_LOG.STRING (l_error_level , l_path || p_path , x_msg_data );
427 END IF;
428 RETURN false;
429 END IF;
430 EXCEPTION
431 WHEN others THEN
432 x_msg_data := 'Unexpected Error3';
433 IF (l_unexp_level >= l_debug_level ) THEN
434 FND_LOG.STRING (l_unexp_level , l_path || p_path , x_msg_data );
435 END IF;
436 RETURN false;
437 app_exception.raise_exception;
438 END get_supplier;
439
440
441 END FUN_TRADING_RELATION;
442
[Home] [Help]