[Home] [Help]
PACKAGE BODY: APPS.EC_TRADING_PARTNER_PVT
Source
1 PACKAGE BODY EC_Trading_Partner_PVT AS
2 -- $Header: ECVTPXFB.pls 120.4 2006/04/27 04:23:00 arsriniv ship $
3
4 -- ***********************************************
5 -- procedure Get_TP_Address
6 --
7 -- WARNING: This procedure is overloaded
8 -- ***********************************************
9 PROCEDURE Get_TP_Address
10 ( p_api_version_number IN NUMBER,
11 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
12 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
13 p_commit IN VARCHAR2 := FND_API.G_FALSE,
14 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
15 p_return_status OUT NOCOPY VARCHAR2,
16 p_msg_count OUT NOCOPY NUMBER,
17 p_msg_data OUT NOCOPY VARCHAR2,
18 p_translator_code IN VARCHAR2,
19 p_location_code_ext IN VARCHAR2,
20 p_info_type IN VARCHAR2,
21 p_entity_id OUT NOCOPY NUMBER,
22 p_entity_address_id OUT NOCOPY NUMBER
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Address';
26 l_api_version_number CONSTANT NUMBER := 1.0;
27 l_return_status VARCHAR2(10);
28
29 l_entity_id NUMBER;
30 l_entity_address_id NUMBER;
31
32 cursor ra_add is
33 select cas.cust_account_id ,
34 cas.cust_acct_site_id
35 from hz_cust_acct_sites cas,
36 hz_cust_accounts ca,
37 hz_parties pt,
38 ece_tp_details etd
39 where
40 etd.translator_code = p_translator_code
41 and cas.ece_tp_location_code = p_location_code_ext
42 and etd.tp_header_id = cas.tp_header_id
43 and cas.cust_account_id = ca.cust_account_id
44 and ca.party_id = pt.party_id;
45
46 cursor po_site is
47 select pv.vendor_id, pvs.vendor_site_id
48 from po_vendors pv, po_vendor_sites pvs,
49 -- ece_tp_headers ec,
50 ece_tp_details etd
51 where
52 etd.translator_code = p_translator_code
53 -- and etd.tp_header_id = ec.tp_header_id
54 and pvs.ece_tp_location_code = p_location_code_ext
55 and etd.tp_header_id = pvs.tp_header_id
56 and pvs.vendor_id = pv.vendor_id;
57
58 cursor ap_bank is
59 select cbb.branch_party_id
60 from ce_bank_branches_v cbb,
61 ece_tp_details etd,
62 hz_contact_points hcp
63 where
64 etd.translator_code = p_translator_code
65 and hcp.edi_ece_tp_location_code = p_location_code_ext
66 and hcp.edi_tp_header_id = etd.tp_header_id
67 and hcp.owner_table_id = cbb.branch_party_id
68 and hcp.owner_table_name = 'HZ_PARTIES'
69 and hcp.contact_point_type = 'EDI';
70
71 BEGIN
72
73 EC_DEBUG.PUSH('EC_Trading_Partner_PVT.Get_TP_Address');
74 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
75 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
76 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
77 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
78 EC_DEBUG.PL(3, 'p_translator_code: ',p_translator_code);
79 EC_DEBUG.PL(3, 'p_location_code_ext: ',p_location_code_ext);
80 EC_DEBUG.PL(3, 'p_info_type: ',p_info_type);
81
82
83 -- Standard Start of API savepoint
84
85 SAVEPOINT Get_TP_Address_PVT;
86
87 -- Standard call to check for call compatibility.
88
89 if NOT FND_API.Compatible_API_Call
90 (
91 l_api_version_number,
92 p_api_version_number,
93 l_api_name,
94 G_PKG_NAME
95 )
96 then
97 raise FND_API.G_EXC_UNEXPECTED_ERROR;
98 end if;
99
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101
102 if FND_API.to_Boolean( p_init_msg_list)
103 then
104 FND_MSG_PUB.initialize;
105 end if;
106
107 -- Initialize API return status to success
108
109 p_return_status := FND_API.G_RET_STS_SUCCESS;
110
111
112 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
113 then
114 for addr in ra_add
115 loop
116 l_entity_id := addr.cust_account_id;
117 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
118 l_entity_address_id := addr.cust_acct_site_id;
119 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
120 end loop;
121
122 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
123 then
124 for site in po_site
125 loop
126 l_entity_id := site.vendor_id;
127 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
128 l_entity_address_id := site.vendor_site_id;
129 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
130 end loop;
131
132 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
133 then
134 for bank in ap_bank
135 loop
136 l_entity_id := -1;
137 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
138 l_entity_address_id := bank.branch_party_id;
139 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
140 end loop;
141 else
142 raise FND_API.G_EXC_UNEXPECTED_ERROR;
143 end if;
144
145 if l_entity_id is NULL
146 and l_entity_address_id is NULL
147 then
148 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
149 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
150 p_msg_data := fnd_message.get;
151 else
152 p_entity_id := l_entity_id;
153 EC_DEBUG.PL(3, 'p_entity_id: ',p_entity_id);
154 p_entity_address_id := l_entity_address_id;
155 EC_DEBUG.PL(3, 'p_entity_address_id: ',p_entity_address_id);
156 end if;
157
158
159 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
160 -- the API exception handler.
161
162 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
163 then
164
165 -- Unexpected error, abort processing.
166
167 raise FND_API.G_EXC_UNEXPECTED_ERROR;
168
169 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
170
171 -- Error, abort processing
172
173 raise FND_API.G_EXC_ERROR;
174
175 end if;
176
177 -- Standard check of p_simulate and p_commit parameters
178
179 if FND_API.To_Boolean( p_simulate)
180 then
181 ROLLBACK to Get_TP_Address_PVT;
182
183 elsif FND_API.To_Boolean( p_commit)
184 then
185 commit work;
186 end if;
187
188 -- Standard call to get message count and if count is 1, get message info.
189
190 FND_MSG_PUB.Count_And_Get
191 (
192 p_count => p_msg_count,
193 p_data => p_msg_data
194 );
195
196 EC_DEBUG.POP('EC_Trading_Partner_PVT.Get_TP_Address');
197 EXCEPTION
198
199 WHEN FND_API.G_EXC_ERROR THEN
200
201 Rollback to Get_TP_Address_PVT;
202 p_return_status := FND_API.G_RET_STS_ERROR;
203
204 FND_MSG_PUB.Count_And_Get
205 (
206 p_count => p_msg_count,
207 p_data => p_msg_data
208 );
209
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211
212 Rollback to Get_TP_Address_PVT;
213 p_return_status := FND_API.G_RET_STS_ERROR;
214
215 FND_MSG_PUB.Count_And_Get
216 (
217 p_count => p_msg_count,
218 p_data => p_msg_data
219 );
220
221 WHEN OTHERS THEN
222 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
223 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
224 Rollback to Get_TP_Address_PVT;
225 p_return_status := FND_API.G_RET_STS_ERROR;
226
227 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
228 then
229 FND_MSG_PUB.Add_Exc_Msg
230 (
231 G_FILE_NAME,
232 G_PKG_NAME,
233 l_api_name
234 );
235 end if;
236
237 FND_MSG_PUB.Count_And_Get
238 (
239 p_count => p_msg_count,
240 p_data => p_msg_data
241 );
242
243 end Get_TP_Address;
244
245
246 -- ***********************************************
247 -- procedure Get_TP_Address_Ref
248 --
249 -- Overload this procedure per request from
250 -- the automotive team
251 -- ***********************************************
252 PROCEDURE Get_TP_Address_Ref
253 ( p_api_version_number IN NUMBER,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
255 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
256 p_commit IN VARCHAR2 := FND_API.G_FALSE,
257 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
258 p_return_status OUT NOCOPY VARCHAR2,
259 p_msg_count OUT NOCOPY NUMBER,
260 p_msg_data OUT NOCOPY VARCHAR2,
261 -- p_translator_code IN VARCHAR2,
262 -- p_location_code_ext IN VARCHAR2,
263 p_reference_ext1 IN VARCHAR2,
264 p_reference_ext2 IN VARCHAR2,
265 p_info_type IN VARCHAR2,
266 p_entity_id OUT NOCOPY NUMBER,
267 p_entity_address_id OUT NOCOPY NUMBER
268 )
269 IS
270 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Address_Ref';
271 l_api_version_number CONSTANT NUMBER := 1.0;
272 l_return_status VARCHAR2(10);
273
274 l_entity_id NUMBER;
275 l_entity_address_id NUMBER;
276
277 cursor ra_add is
278 select cas.cust_account_id ,
279 cas.cust_acct_site_id
280 from hz_cust_acct_sites cas,
281 hz_cust_accounts ca,
282 hz_parties pt,
283 ece_tp_headers eth
284 where
285 eth.tp_reference_ext1 = p_reference_ext1
286 and eth.tp_reference_ext2 = p_reference_ext2
287 and eth.tp_header_id = cas.tp_header_id
288 and cas.cust_account_id = ca.cust_account_id
289 and ca.party_id = pt.party_id;
290
291 cursor po_site is
292 select pv.vendor_id, pvs.vendor_site_id
293 from po_vendors pv, po_vendor_sites pvs,
294 ece_tp_headers eth
295 where
296 eth.tp_reference_ext1 = p_reference_ext1
297 and eth.tp_reference_ext2 = p_reference_ext2
298 and eth.tp_header_id = pvs.tp_header_id
299 and pvs.vendor_id = pv.vendor_id;
300
301 cursor ap_bank is
302 select cbb.branch_party_id
303 from ce_bank_branches_v cbb,
304 ece_tp_headers eth,
305 hz_contact_points hcp
306 where
307 eth.tp_reference_ext1 = p_reference_ext1
308 and eth.tp_reference_ext2 = p_reference_ext2
309 and eth.tp_header_id = hcp.edi_tp_header_id
310 and hcp.owner_table_id = cbb.branch_party_id
311 and hcp.owner_table_name = 'HZ_PARTIES'
312 and hcp.contact_point_type = 'EDI';
313
314 BEGIN
315
316 EC_DEBUG.PUSH('EC_Trading_Partner_PVT.Get_TP_Address_Ref');
317 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
318 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
319 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
320 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
321 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
322 EC_DEBUG.PL(3, 'p_reference_ext1: ',p_reference_ext1);
323 EC_DEBUG.PL(3, 'p_reference_ext2: ',p_reference_ext2);
324 EC_DEBUG.PL(3, 'p_info_type: ',p_info_type);
325
326 -- Standard Start of API savepoint
327
328 SAVEPOINT Get_TP_Address_Ref_PVT;
329
330 -- Standard call to check for call compatibility.
331
332 if NOT FND_API.Compatible_API_Call
333 (
334 l_api_version_number,
335 p_api_version_number,
336 l_api_name,
337 G_PKG_NAME
338 )
339 then
340 raise FND_API.G_EXC_UNEXPECTED_ERROR;
341 end if;
342
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344
345 if FND_API.to_Boolean( p_init_msg_list)
346 then
347 FND_MSG_PUB.initialize;
348 end if;
349
350 -- Initialize API return status to success
351
352 p_return_status := FND_API.G_RET_STS_SUCCESS;
353
354
355 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
356 then
357 for addr in ra_add
358 loop
359 l_entity_id := addr.cust_account_id;
360 l_entity_address_id := addr.cust_acct_site_id;
361 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
362 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
363 end loop;
364
365 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
366 then
367 for site in po_site
368 loop
369 l_entity_id := site.vendor_id;
370 l_entity_address_id := site.vendor_site_id;
371 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
372 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
373 end loop;
374
375 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
376 then
377 for bank in ap_bank
378 loop
379 l_entity_id := -1;
380 l_entity_address_id := bank.branch_party_id;
381 EC_DEBUG.PL(3, 'l_entity_id: ',l_entity_id);
382 EC_DEBUG.PL(3, 'l_entity_address_id: ',l_entity_address_id);
383 end loop;
384 else
385 raise FND_API.G_EXC_UNEXPECTED_ERROR;
386 end if;
387
388 if l_entity_id is NULL
389 and l_entity_address_id is NULL
390 then
391 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
392 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
393 p_msg_data := fnd_message.get;
394 else
395 p_entity_id := l_entity_id;
396 p_entity_address_id := l_entity_address_id;
397 end if;
398
399 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
400 -- the API exception handler.
401
402 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
403 then
404
405 -- Unexpected error, abort processing.
406
407 raise FND_API.G_EXC_UNEXPECTED_ERROR;
408
409 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
410
411 -- Error, abort processing
412
413 raise FND_API.G_EXC_ERROR;
414
415 end if;
416
417 -- Standard check of p_simulate and p_commit parameters
418
419 if FND_API.To_Boolean( p_simulate)
420 then
421 ROLLBACK to Get_TP_Address_Ref_PVT;
422
423 elsif FND_API.To_Boolean( p_commit)
424 then
425 commit work;
426 end if;
427
428 -- Standard call to get message count and if count is 1, get message info.
429
430 FND_MSG_PUB.Count_And_Get
431 (
432 p_count => p_msg_count,
433 p_data => p_msg_data
434 );
435
436 EC_DEBUG.POP('EC_Trading_Partner_PVT.Get_TP_Address_Ref');
437 EXCEPTION
438
439 WHEN FND_API.G_EXC_ERROR THEN
440
441 Rollback to Get_TP_Address_Ref_PVT;
442 p_return_status := FND_API.G_RET_STS_ERROR;
443
444 FND_MSG_PUB.Count_And_Get
445 (
446 p_count => p_msg_count,
447 p_data => p_msg_data
448 );
449
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
451
452 Rollback to Get_TP_Address_Ref_PVT;
453 p_return_status := FND_API.G_RET_STS_ERROR;
454
455 FND_MSG_PUB.Count_And_Get
456 (
457 p_count => p_msg_count,
458 p_data => p_msg_data
459 );
460
461 WHEN OTHERS THEN
462
463 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
464 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
465 Rollback to Get_TP_Address_Ref_PVT;
466 p_return_status := FND_API.G_RET_STS_ERROR;
467
468 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
469 then
470 FND_MSG_PUB.Add_Exc_Msg
471 (
472 G_FILE_NAME,
473 G_PKG_NAME,
474 l_api_name
475 );
476 end if;
477
478 FND_MSG_PUB.Count_And_Get
479 (
480 p_count => p_msg_count,
481 p_data => p_msg_data
482 );
483
484 end Get_TP_Address_Ref;
485
486 -- ***********************************************
487 -- procedure Get_TP_Location_Code
488 -- ***********************************************
489 PROCEDURE Get_TP_Location_Code
490 ( p_api_version_number IN NUMBER,
491 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
492 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
493 p_commit IN VARCHAR2 := FND_API.G_FALSE,
494 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
495 p_return_status OUT NOCOPY VARCHAR2,
496 p_msg_count OUT NOCOPY NUMBER,
497 p_msg_data OUT NOCOPY VARCHAR2,
498 p_entity_address_id IN NUMBER,
499 p_info_type IN VARCHAR2,
500 p_location_code_ext OUT NOCOPY VARCHAR2,
501 p_reference_ext1 OUT NOCOPY VARCHAR2,
502 p_reference_ext2 OUT NOCOPY VARCHAR2
503 )
504 IS
505 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Location_Code';
506 l_api_version_number CONSTANT NUMBER := 1.0;
507 l_return_status VARCHAR2(10);
508
509 l_location_code_ext VARCHAR2(50);
510
511 cursor ra_add is
512 select
513 cas.ece_tp_location_code,
514 ec.tp_reference_ext1,
515 ec.tp_reference_ext2
516 from hz_cust_acct_sites cas,
517 ece_tp_headers ec
518 where
519
520 ec.tp_header_id = cas.tp_header_id
521 and cas.cust_acct_site_id = p_entity_address_id;
522
523 cursor po_site is
524 select pvs.ece_tp_location_code,
525 ec.tp_reference_ext1,
526 ec.tp_reference_ext2
527 from ece_tp_headers ec, po_vendor_sites pvs
528 where
529 pvs.vendor_site_id = p_entity_address_id
530 and pvs.tp_header_id = ec.tp_header_id;
531
532 cursor ap_bank is
533 select hcp.edi_ece_tp_location_code,
534 ec.tp_reference_ext1,
535 ec.tp_reference_ext2
536 from ece_tp_headers ec,
537 ce_bank_branches_v cbb,
538 hz_contact_points hcp
539 where
540 cbb.branch_party_id = p_entity_address_id
541 and hcp.edi_tp_header_id = ec.tp_header_id
542 and hcp.owner_table_id = cbb.branch_party_id
543 and hcp.owner_table_name = 'HZ_PARTIES'
544 and hcp.contact_point_type = 'EDI';
545
546 BEGIN
547
548 EC_DEBUG.PUSH('EC_Trading_Partner_PVT.Get_TP_Location_Code');
549 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
550 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
551 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
552 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
553 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
554 EC_DEBUG.PL(3, 'p_entity_address_id: ',p_entity_address_id);
555 EC_DEBUG.PL(3, 'p_info_type: ',p_info_type);
556 -- Standard Start of API savepoint
557
558 SAVEPOINT Get_TP_Location_Code_PVT;
559
560 -- Standard call to check for call compatibility.
561
562 if NOT FND_API.Compatible_API_Call
563 (
564 l_api_version_number,
565 p_api_version_number,
566 l_api_name,
567 G_PKG_NAME
568 )
569 then
570 raise FND_API.G_EXC_UNEXPECTED_ERROR;
571 end if;
572
573 -- Initialize message list if p_init_msg_list is set to TRUE.
574
575 if FND_API.to_Boolean( p_init_msg_list)
576 then
577 FND_MSG_PUB.initialize;
578 end if;
579
580 -- Initialize API return status to success
581
582 p_return_status := FND_API.G_RET_STS_SUCCESS;
583
584
585 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
586 then
587 for addr in ra_add loop
588 l_location_code_ext := addr.ece_tp_location_code;
589 p_reference_ext1 := addr.tp_reference_ext1;
590 p_reference_ext2 := addr.tp_reference_ext2;
591 EC_DEBUG.PL(3, 'l_location_code_ext: ',l_location_code_ext);
592 EC_DEBUG.PL(3, 'addr.tp_reference_ext1',addr.tp_reference_ext1);
593 EC_DEBUG.PL(3, 'addr.tp_reference_ext2',addr.tp_reference_ext2);
594 end loop;
595
596
597 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
598 then
599 for site in po_site loop
600 l_location_code_ext := site.ece_tp_location_code;
601 p_reference_ext1 := site.tp_reference_ext1;
602 p_reference_ext2 := site.tp_reference_ext2;
603 EC_DEBUG.PL(3, 'site.tp_reference_ext1',site.tp_reference_ext1);
604 EC_DEBUG.PL(3, 'site.tp_reference_ext2',site.tp_reference_ext2);
605 end loop;
606
607 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
608 then
609 for bank in ap_bank loop
610 l_location_code_ext := bank.edi_ece_tp_location_code;
611 p_reference_ext1 := bank.tp_reference_ext1;
612 p_reference_ext2 := bank.tp_reference_ext2;
613 EC_DEBUG.PL(3, 'bank.tp_reference_ext1',bank.tp_reference_ext1);
614 EC_DEBUG.PL(3, 'bank.tp_reference_ext2',bank.tp_reference_ext2);
615 end loop;
616 else
617 raise FND_API.G_EXC_UNEXPECTED_ERROR;
618 end if;
619
620 if l_location_code_ext is NULL
621 then
622 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
623 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
624 p_msg_data := fnd_message.get;
625 EC_DEBUG.PL(3, 'p_msg_data',p_msg_data);
626 else
627 p_location_code_ext := l_location_code_ext;
628 EC_DEBUG.PL(3, 'l_location_code_ext',l_location_code_ext);
629 end if;
630
631 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
632 -- the API exception handler.
633
634 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
635 then
636
637 -- Unexpected error, abort processing.
638
639 raise FND_API.G_EXC_UNEXPECTED_ERROR;
640
641 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
642
643 -- Error, abort processing
644
645 raise FND_API.G_EXC_ERROR;
646
647 end if;
648
649 -- Standard check of p_simulate and p_commit parameters
650
651 if FND_API.To_Boolean( p_simulate)
652 then
653 ROLLBACK to Get_TP_Location_Code_PVT;
654
655 elsif FND_API.To_Boolean( p_commit)
656 then
657 commit work;
658 end if;
659
660 -- Standard call to get message count and if count is 1, get message info.
661
662 FND_MSG_PUB.Count_And_Get
663 (
664 p_count => p_msg_count,
665 p_data => p_msg_data
666 );
667
668 EC_DEBUG.POP('EC_Trading_Partner_PVT.Get_TP_Location_Code');
669 EXCEPTION
670
671 WHEN FND_API.G_EXC_ERROR THEN
672
673 Rollback to Get_TP_Location_Code_PVT;
674 p_return_status := FND_API.G_RET_STS_ERROR;
675
676 FND_MSG_PUB.Count_And_Get
677 (
678 p_count => p_msg_count,
679 p_data => p_msg_data
680 );
681
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683
684 Rollback to Get_TP_Location_Code_PVT;
685 p_return_status := FND_API.G_RET_STS_ERROR;
686
687 FND_MSG_PUB.Count_And_Get
688 (
689 p_count => p_msg_count,
690 p_data => p_msg_data
691 );
692
693 WHEN OTHERS THEN
694 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
695 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
696
697 Rollback to Get_TP_Location_Code_PVT;
698 p_return_status := FND_API.G_RET_STS_ERROR;
699
700 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 then
702 FND_MSG_PUB.Add_Exc_Msg
703 (
704 G_FILE_NAME,
705 G_PKG_NAME,
706 l_api_name
707 );
708 end if;
709
710 FND_MSG_PUB.Count_And_Get
711 (
712 p_count => p_msg_count,
713 p_data => p_msg_data
714 );
715
716 end Get_TP_Location_Code;
717
718 FUNCTION IS_ENTITY_ENABLED
719 ( p_api_version_number IN NUMBER,
720 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
721 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
722 p_commit IN VARCHAR2 := FND_API.G_FALSE,
723 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
724 p_return_status OUT NOCOPY VARCHAR2,
725 p_msg_count OUT NOCOPY NUMBER,
726 p_msg_data OUT NOCOPY VARCHAR2,
727 p_transaction_type IN VARCHAR2,
728 p_transaction_subtype IN VARCHAR2,
729 p_entity_type IN VARCHAR2,
730 p_entity_id IN NUMBER
731 ) RETURN BOOLEAN
732 IS
733 l_api_name CONSTANT VARCHAR2(30) := 'IS_ENTITY_ENABLED';
734 l_api_version_number CONSTANT NUMBER := 1.0;
735 l_return_status VARCHAR2(10);
736
737
738 x_tp_detail_id NUMBER;
739
740 cursor type_customer is
741 select tpd.tp_detail_id
742 from ece_tp_details tpd,hz_cust_acct_sites cas
743 where tpd.tp_header_id = cas.tp_header_id
744 and tpd.document_id = p_transaction_type
745 and tpd.document_type = nvl(p_transaction_subtype,tpd.document_type)
746 and cas.cust_acct_site_id = p_entity_id;
747
748 cursor type_supplier is
749 select tpd.tp_detail_id
750 from ece_tp_details tpd,po_vendor_sites povs
751 where tpd.tp_header_id = povs.tp_header_id
752 and tpd.document_id = p_transaction_type
753 and tpd.document_type = nvl(p_transaction_subtype,tpd.document_type)
754 and povs.vendor_site_id = p_entity_id;
755
756 cursor type_bank is
757 select tpd.tp_detail_id
758 from ece_tp_details tpd,
759 ce_bank_branches_v cbb,
760 hz_contact_points hcp
761 where tpd.tp_header_id = hcp.edi_tp_header_id
762 and tpd.document_id = p_transaction_type
763 and tpd.document_type = nvl(p_transaction_subtype,tpd.document_type)
764 and cbb.branch_party_id = p_entity_id
765 and cbb.branch_party_id = hcp.owner_table_id
766 and hcp.owner_table_name = 'HZ_PARTIES'
767 and hcp.contact_point_type = 'EDI';
768
769 cursor type_location is
770 select tpd.tp_detail_id
771 from ece_tp_details tpd,hr_locations hrl
772 where tpd.tp_header_id = hrl.tp_header_id
773 and tpd.document_id = p_transaction_type
774 and tpd.document_type = nvl(p_transaction_subtype,tpd.document_type)
775 and hrl.location_id = p_entity_id;
776
777 BEGIN
778
779 EC_DEBUG.PUSH('EC_Trading_Partner_PVT.IS_ENTITY_ENABLED');
780 EC_DEBUG.PL(3, 'p_transaction_type: ',p_transaction_type);
781 EC_DEBUG.PL(3, 'p_transaction_subtype: ',p_transaction_subtype);
782 EC_DEBUG.PL(3, 'p_entity_type: ',p_entity_type);
783 EC_DEBUG.PL(3, 'p_entity_id: ',p_entity_id);
784
785 SAVEPOINT Get_TP_Location_Code_PVT;
786
787 -- Standard call to check for call compatibility.
788
789 if NOT FND_API.Compatible_API_Call
790 (
791 l_api_version_number,
792 p_api_version_number,
793 l_api_name,
794 G_PKG_NAME
795 )
796 then
797 raise FND_API.G_EXC_UNEXPECTED_ERROR;
798 end if;
799
800 -- Initialize message list if p_init_msg_list is set to TRUE.
801
802 if FND_API.to_Boolean( p_init_msg_list)
803 then
804 FND_MSG_PUB.initialize;
805 end if;
806
807 -- Initialize API return status to success
808
809 p_return_status := FND_API.G_RET_STS_SUCCESS;
810
811
812 if ( p_entity_type = EC_Trading_Partner_PVT.G_CUSTOMER)
813 then
814 for addr in type_customer loop
815 x_tp_detail_id := addr.tp_detail_id;
816 EC_DEBUG.PL(3, 'x_tp_detail_id: ',x_tp_detail_id);
817 end loop;
818 elsif (p_entity_type = EC_Trading_Partner_PVT.G_SUPPLIER)
819 then
820 for addr in type_supplier loop
821 x_tp_detail_id := addr.tp_detail_id;
822 EC_DEBUG.PL(3, 'x_tp_detail_id: ',x_tp_detail_id);
823 end loop;
824 elsif (p_entity_type = EC_Trading_Partner_PVT.G_BANK)
825 then
826 for addr in type_bank loop
827 x_tp_detail_id := addr.tp_detail_id;
828 EC_DEBUG.PL(3, 'x_tp_detail_id: ',x_tp_detail_id);
829 end loop;
830 elsif (p_entity_type = EC_Trading_Partner_PVT.G_LOCATION)
831 then
832 for addr in type_location loop
833 x_tp_detail_id := addr.tp_detail_id;
834 EC_DEBUG.PL(3, 'x_tp_detail_id: ',x_tp_detail_id);
835 end loop;
836 else
837 raise FND_API.G_EXC_UNEXPECTED_ERROR;
838 end if;
839
840 if x_tp_detail_id is null
841 then
842 RETURN FALSE;
843 else
844 RETURN TRUE;
845 end if;
846
847 -- Standard check of p_simulate and p_commit parameters
848
849 if FND_API.To_Boolean( p_simulate)
850 then
851 ROLLBACK to Get_TP_Location_Code_PVT;
852
853 elsif FND_API.To_Boolean( p_commit)
854 then
855 commit work;
856 end if;
857
858 -- Standard call to get message count and if count is 1, get message info.
859 FND_MSG_PUB.Count_And_Get
860 (
861 p_count => p_msg_count,
862 p_data => p_msg_data
863 );
864
865 EC_DEBUG.POP('EC_Trading_Partner_PVT.IS_ENTITY_ENABLED');
866 EXCEPTION
867 WHEN FND_API.G_EXC_ERROR THEN
868
869 Rollback to Get_TP_Location_Code_PVT;
870 p_return_status := FND_API.G_RET_STS_ERROR;
871
872 FND_MSG_PUB.Count_And_Get
873 (
874 p_count => p_msg_count,
875 p_data => p_msg_data
876 );
877
878 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
879
880 Rollback to Get_TP_Location_Code_PVT;
881 p_return_status := FND_API.G_RET_STS_ERROR;
882
883 FND_MSG_PUB.Count_And_Get
884 (
885 p_count => p_msg_count,
886 p_data => p_msg_data
887 );
888
889 WHEN OTHERS THEN
890 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
891 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
892
893 Rollback to Get_TP_Location_Code_PVT;
894 p_return_status := FND_API.G_RET_STS_ERROR;
895
896 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
897 then
898 FND_MSG_PUB.Add_Exc_Msg
899 (
900 G_FILE_NAME,
901 G_PKG_NAME,
902 l_api_name
903 );
904 end if;
905
906 FND_MSG_PUB.Count_And_Get
907 (
908 p_count => p_msg_count,
909 p_data => p_msg_data
910 );
911
912
913 END IS_ENTITY_ENABLED;
914
915
916 END EC_Trading_Partner_PVT;
917