[Home] [Help]
PACKAGE BODY: APPS.ECE_TRADING_PARTNERS_PUB
Source
1 PACKAGE BODY ece_trading_partners_pub AS
2 -- $Header: ECVNWTPB.pls 120.7.12010000.2 2008/09/04 12:22:58 hgandiko ship $
3
4 PROCEDURE ece_get_address_wrapper(
5 p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2,
13 x_status_code OUT NOCOPY NUMBER,
14 p_address_type IN NUMBER,
15 p_transaction_type IN VARCHAR2,
16 p_org_id_in IN NUMBER DEFAULT NULL,
17 p_address_id_in IN NUMBER DEFAULT NULL,
18 p_tp_location_code_in IN VARCHAR2 DEFAULT NULL,
19 p_translator_code_in IN VARCHAR2 DEFAULT NULL,
20 p_tp_location_name_in IN VARCHAR2 DEFAULT NULL,
21 p_address_line1_in IN VARCHAR2 DEFAULT NULL,
22 p_address_line2_in IN VARCHAR2 DEFAULT NULL,
23 p_address_line3_in IN VARCHAR2 DEFAULT NULL,
24 p_address_line4_in IN VARCHAR2 DEFAULT NULL,
25 p_address_line_alt_in IN VARCHAR2 DEFAULT NULL,
26 p_city_in IN VARCHAR2 DEFAULT NULL,
27 p_county_in IN VARCHAR2 DEFAULT NULL,
28 p_state_in IN VARCHAR2 DEFAULT NULL,
29 p_zip_in IN VARCHAR2 DEFAULT NULL,
30 p_province_in IN VARCHAR2 DEFAULT NULL,
31 p_country_in IN VARCHAR2 DEFAULT NULL,
32 p_region_1_in IN VARCHAR2 DEFAULT NULL,
33 p_region_2_in IN VARCHAR2 DEFAULT NULL,
34 p_region_3_in IN VARCHAR2 DEFAULT NULL,
35 x_entity_id_out OUT NOCOPY NUMBER,
36 x_org_id_out OUT NOCOPY NUMBER,
37 x_address_id_out OUT NOCOPY NUMBER,
38 x_tp_location_code_out OUT NOCOPY VARCHAR2,
39 x_translator_code_out OUT NOCOPY VARCHAR2,
40 x_tp_location_name_out OUT NOCOPY VARCHAR2,
41 x_address_line1_out OUT NOCOPY VARCHAR2,
42 x_address_line2_out OUT NOCOPY VARCHAR2,
43 x_address_line3_out OUT NOCOPY VARCHAR2,
44 x_address_line4_out OUT NOCOPY VARCHAR2,
45 x_address_line_alt_out OUT NOCOPY VARCHAR2,
46 x_city_out OUT NOCOPY VARCHAR2,
47 x_county_out OUT NOCOPY VARCHAR2,
48 x_state_out OUT NOCOPY VARCHAR2,
49 x_zip_out OUT NOCOPY VARCHAR2,
50 x_province_out OUT NOCOPY VARCHAR2,
51 x_country_out OUT NOCOPY VARCHAR2,
52 x_region_1_out OUT NOCOPY VARCHAR2,
56 v_precedence_code VARCHAR2(240);
53 x_region_2_out OUT NOCOPY VARCHAR2,
54 x_region_3_out OUT NOCOPY VARCHAR2) IS
55
57 v_profile_name VARCHAR2(80);
58
59 BEGIN
60 v_profile_name := 'ECE_' || NVL(scrub(p_transaction_type),'') || '_ADDRESS_PRECEDENCE';
61 fnd_profile.get(v_profile_name,v_precedence_code);
62
63 ece_get_address(
64 p_api_version_number,
65 p_init_msg_list,
66 p_simulate,
67 p_commit,
68 p_validation_level,
69 x_return_status,
70 x_msg_count,
71 x_msg_data,
72 x_status_code,
73 NVL(v_precedence_code,'0'),
74 p_address_type,
75 p_transaction_type,
76 p_org_id_in,
77 p_address_id_in,
78 p_tp_location_code_in,
79 p_translator_code_in,
80 p_tp_location_name_in,
81 p_address_line1_in,
82 p_address_line2_in,
83 p_address_line3_in,
84 p_address_line4_in,
85 p_address_line_alt_in,
86 p_city_in,
87 p_county_in,
88 p_state_in,
89 p_zip_in,
90 p_province_in,
91 p_country_in,
92 p_region_1_in,
93 p_region_2_in,
94 p_region_3_in,
95 x_org_id_out,
96 x_address_id_out,
97 x_tp_location_code_out,
98 x_translator_code_out,
99 x_tp_location_name_out,
100 x_address_line1_out,
101 x_address_line2_out,
102 x_address_line3_out,
103 x_address_line4_out,
104 x_address_line_alt_out,
105 x_city_out,
106 x_county_out,
107 x_state_out,
108 x_zip_out,
109 x_province_out,
110 x_country_out,
111 x_region_1_out,
112 x_region_2_out,
113 x_region_3_out);
114
115 -- If the address type is CUSTOMER or SUPPLIER and the ADDRESS ID is available,
116 -- then derive the CUSTOMER OR SUPPLIER's ID.
117 -- Bug 2570369. This is a fix for the bug 2641276 which is resolved by populating
118 -- x_translator_code_out with customer account_number.
119 IF x_address_id_out IS NOT NULL THEN
120 IF p_address_type = G_CUSTOMER THEN
121 SELECT cas.cust_account_id,pt.party_name, --Bug 2722334
122 ca.account_number
123 INTO x_entity_id_out,x_tp_location_name_out,
124 x_translator_code_out
125 FROM hz_cust_acct_sites_all cas,
126 hz_cust_accounts ca,
127 hz_parties pt
128 WHERE cas.cust_acct_site_id = x_address_id_out
129 AND cas.cust_account_id = ca.cust_account_id
130 AND ca.party_id = pt.party_id;
131 ELSIF p_address_type = G_SUPPLIER THEN
132 SELECT pvs.vendor_id,pv.vendor_name
133 INTO x_entity_id_out,x_tp_location_name_out
134 FROM po_vendor_sites_all pvs,
135 po_vendors pv
136 WHERE pvs.vendor_site_id = x_address_id_out AND
137 pvs.vendor_id = pv.vendor_id AND
138 ROWNUM = 1;
139 ELSE
140 x_entity_id_out := NULL;
141 END IF;
142 END IF;
143
144 END ece_get_address_wrapper;
145
146 PROCEDURE ece_get_address(
147 p_api_version_number IN NUMBER,
148 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
149 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
150 p_commit IN VARCHAR2 := FND_API.G_FALSE,
151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 x_status_code OUT NOCOPY NUMBER,
156 p_precedence_code IN VARCHAR2,
157 p_address_type IN NUMBER,
158 p_transaction_type IN VARCHAR2,
159 p_org_id_in IN NUMBER DEFAULT NULL,
160 p_address_id_in IN NUMBER DEFAULT NULL,
161 p_tp_location_code_in IN VARCHAR2 DEFAULT NULL,
162 p_translator_code_in IN VARCHAR2 DEFAULT NULL,
163 p_tp_location_name_in IN VARCHAR2 DEFAULT NULL,
164 p_address_line1_in IN VARCHAR2 DEFAULT NULL,
165 p_address_line2_in IN VARCHAR2 DEFAULT NULL,
166 p_address_line3_in IN VARCHAR2 DEFAULT NULL,
167 p_address_line4_in IN VARCHAR2 DEFAULT NULL,
168 p_address_line_alt_in IN VARCHAR2 DEFAULT NULL,
169 p_city_in IN VARCHAR2 DEFAULT NULL,
170 p_county_in IN VARCHAR2 DEFAULT NULL,
171 p_state_in IN VARCHAR2 DEFAULT NULL,
172 p_zip_in IN VARCHAR2 DEFAULT NULL,
173 p_province_in IN VARCHAR2 DEFAULT NULL,
174 p_country_in IN VARCHAR2 DEFAULT NULL,
175 p_region_1_in IN VARCHAR2 DEFAULT NULL,
176 p_region_2_in IN VARCHAR2 DEFAULT NULL,
177 p_region_3_in IN VARCHAR2 DEFAULT NULL,
178 x_org_id_out OUT NOCOPY NUMBER,
179 x_address_id_out OUT NOCOPY NUMBER,
180 x_tp_location_code_out OUT NOCOPY VARCHAR2,
184 x_address_line2_out OUT NOCOPY VARCHAR2,
181 x_translator_code_out OUT NOCOPY VARCHAR2,
182 x_tp_location_name_out OUT NOCOPY VARCHAR2,
183 x_address_line1_out OUT NOCOPY VARCHAR2,
185 x_address_line3_out OUT NOCOPY VARCHAR2,
186 x_address_line4_out OUT NOCOPY VARCHAR2,
187 x_address_line_alt_out OUT NOCOPY VARCHAR2,
188 x_city_out OUT NOCOPY VARCHAR2,
189 x_county_out OUT NOCOPY VARCHAR2,
190 x_state_out OUT NOCOPY VARCHAR2,
191 x_zip_out OUT NOCOPY VARCHAR2,
192 x_province_out OUT NOCOPY VARCHAR2,
193 x_country_out OUT NOCOPY VARCHAR2,
194 x_region_1_out OUT NOCOPY VARCHAR2,
195 x_region_2_out OUT NOCOPY VARCHAR2,
196 x_region_3_out OUT NOCOPY VARCHAR2) IS
197
198 b_use_addr_comp BOOLEAN := FALSE;
199 b_use_addr_id BOOLEAN := FALSE;
200 b_use_lctc BOOLEAN := FALSE;
201 b_use_loc_name BOOLEAN := FALSE;
202 b_use_org_id BOOLEAN := FALSE;
203
204 xProgress VARCHAR2(80);
205
206 l_api_name CONSTANT VARCHAR2(30) := 'ece_get_address';
207 l_api_version_number CONSTANT NUMBER := 1.0;
208 l_return_status VARCHAR2(10);
209
210 n_loop_count NUMBER := 0;
211 n_match_count NUMBER := 0;
212
213 n_org_id NUMBER;
214 n_address_id NUMBER;
215 v_pcode VARCHAR2(3);
216 v_tp_location_code VARCHAR2(32000);
217 v_translator_code VARCHAR2(32000);
218 v_tp_location_name VARCHAR2(32000);
219 v_address_line1 VARCHAR2(32000);
220 v_address_line2 VARCHAR2(32000);
221 v_address_line3 VARCHAR2(32000);
222 v_address_line4 VARCHAR2(32000);
223 v_address_line_alt VARCHAR2(32000);
224 v_city VARCHAR2(32000);
225 v_county VARCHAR2(32000);
226 v_state VARCHAR2(32000);
227 v_zip VARCHAR2(32000);
228 v_province VARCHAR2(32000);
229 v_country VARCHAR2(32000);
230 v_region_1 VARCHAR2(32000);
231 v_region_2 VARCHAR2(32000);
232 v_region_3 VARCHAR2(32000);
233
234 /*********************************************************************
235 | Cursor Declarations |
236 | Bug 2151462: Address derivation Cursors will now check for |
237 | location code,address and postal_code. |
238 *********************************************************************/
239 -- Bank Branches Cursor Bug 2551002
240 -- Bug 2422787 apb.bank_branch_id = NVL(cp_address_id_in,apb.bank_branch_id) AND
241 CURSOR c1_bank_branches(cp_transaction_type VARCHAR2,
242 cp_org_id_in NUMBER DEFAULT NULL,
243 cp_address_id_in NUMBER DEFAULT NULL,
244 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
245 cp_address_line1_in VARCHAR2 DEFAULT NULL,
246 cp_address_line2_in VARCHAR2 DEFAULT NULL,
247 cp_address_line3_in VARCHAR2 DEFAULT NULL,
248 cp_address_line4_in VARCHAR2 DEFAULT NULL,
249 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
250 cp_city_in VARCHAR2 DEFAULT NULL,
251 cp_county_in VARCHAR2 DEFAULT NULL,
252 cp_state_in VARCHAR2 DEFAULT NULL,
253 cp_zip_in VARCHAR2 DEFAULT NULL,
254 cp_province_in VARCHAR2 DEFAULT NULL,
255 cp_country_in VARCHAR2 DEFAULT NULL,
256 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
257 SELECT TO_NUMBER(NULL) org_id,
258 cbb.branch_party_id address_id,
259 hcp.edi_ece_tp_location_code tp_location_code,
260 cbb.bank_branch_name tp_location_name,
261 cbb.address_line1 address_line1,
262 cbb.address_line2 address_line2,
263 cbb.address_line3 address_line3,
264 cbb.address_line4 address_line4,
265 hzl.address_lines_phonetic address_line_alt,
266 cbb.city city,
267 hzl.county county,
268 cbb.state state,
269 cbb.zip zip,
270 cbb.province province,
271 cbb.country country,
272 TO_CHAR(NULL) region_1,
273 TO_CHAR(NULL) region_2,
274 TO_CHAR(NULL) region_3
275 FROM ce_bank_branches_v cbb,
276 hz_contact_points hcp,
277 hz_locations hzl,
278 hz_party_sites hps
279 WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
280 NVL(UPPER(cbb.address_line1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
284 NVL(UPPER(hzl.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
281 NVL(UPPER(cbb.address_line2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
282 NVL(UPPER(cbb.address_line3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
283 NVL(UPPER(cbb.address_line4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
285 NVL(UPPER(cbb.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
286 NVL(UPPER(cbb.zip),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
287 hcp.owner_table_id = cbb.branch_party_id AND
288 hcp.owner_table_name = 'HZ_PARTIES' AND
289 hcp.contact_point_type = 'EDI' AND
290 hps.party_id = cbb.branch_party_id AND
291 hps.identifying_address_flag = 'Y' AND
292 hzl.location_id = hps.party_id;
293
294 -- Bug 3351412
295 CURSOR c2_bank_branches(cp_transaction_type VARCHAR2,
296 cp_org_id_in NUMBER DEFAULT NULL,
297 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
298 cp_tp_translator_code VARCHAR2 DEFAULT NULL) IS
299 SELECT TO_NUMBER(NULL) org_id,
300 cbb.branch_party_id address_id,
301 hcp.edi_ece_tp_location_code tp_location_code,
302 cbb.bank_branch_name tp_location_name,
303 cbb.address_line1 address_line1,
304 cbb.address_line2 address_line2,
305 cbb.address_line3 address_line3,
306 cbb.address_line4 address_line4,
307 hzl.address_lines_phonetic address_line_alt,
308 cbb.city city,
309 hzl.county county,
310 cbb.state state,
311 cbb.zip zip,
312 cbb.province province,
313 cbb.country country,
314 TO_CHAR(NULL) region_1,
315 TO_CHAR(NULL) region_2,
316 TO_CHAR(NULL) region_3
317 FROM ce_bank_branches_v cbb,
318 ece_tp_details etd,
319 hz_contact_points hcp,
320 hz_locations hzl,
321 hz_party_sites hps
322 WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
323 hcp.edi_tp_header_id = etd.tp_header_id AND
324 etd.document_id = UPPER(cp_transaction_type) AND
325 NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
326 hcp.owner_table_id = cbb.branch_party_id AND
327 hcp.owner_table_name = 'HZ_PARTIES' AND
328 hcp.contact_point_type = 'EDI' AND
329 hps.party_id = cbb.branch_party_id AND
330 hps.identifying_address_flag = 'Y' AND
331 hzl.location_id = hps.party_id;
332
333 -- Internal Locations Cursor Bug 2551002
334 -- Bug 2570369 Split the c_locations cursor in two. Scan the 2nd
335 -- cursor if the 1st cursor returns no record.
336 -- hrl.org_id LIKE NVL(cp_org_id_in,'%') AND
337 --Bug 2422787 hrl.location_id = NVL(cp_address_id_in,hrl.location_id) AND
338 CURSOR c_locations_1(cp_transaction_type VARCHAR2,
339 cp_org_id_in NUMBER DEFAULT NULL,
340 cp_address_id_in NUMBER DEFAULT NULL,
341 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
342 cp_address_line1_in VARCHAR2 DEFAULT NULL,
343 cp_address_line2_in VARCHAR2 DEFAULT NULL,
344 cp_address_line3_in VARCHAR2 DEFAULT NULL,
345 cp_address_line4_in VARCHAR2 DEFAULT NULL,
346 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
347 cp_city_in VARCHAR2 DEFAULT NULL,
348 cp_county_in VARCHAR2 DEFAULT NULL,
349 cp_state_in VARCHAR2 DEFAULT NULL,
350 cp_zip_in VARCHAR2 DEFAULT NULL,
351 cp_province_in VARCHAR2 DEFAULT NULL,
352 cp_country_in VARCHAR2 DEFAULT NULL,
353 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
354 SELECT TO_NUMBER(NULL) org_id,
355 hrl.location_id address_id,
356 hrl.ece_tp_location_code tp_location_code,
357 hrl.location_code tp_location_name,
358 hrl.address_line_1 address_line1,
359 hrl.address_line_2 address_line2,
360 hrl.address_line_3 address_line3,
361 TO_CHAR(NULL) address_line4,
362 TO_CHAR(NULL) address_line_alt,
363 hrl.town_or_city city,
364 TO_CHAR(NULL) county,
365 TO_CHAR(NULL) state,
366 hrl.postal_code zip,
367 TO_CHAR(NULL) province,
368 hrl.country country,
369 hrl.region_1 region_1,
370 hrl.region_2 region_2,
371 hrl.region_3 region_3,
372 TO_CHAR(hrl.inventory_organization_id) inv_organization_id --Bug 2570369
373 FROM hr_locations_all hrl,
377 NVL(UPPER(hrl.address_line_2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
374 hr_locations_all_tl hrlt
375 WHERE NVL(UPPER(hrl.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
376 NVL(UPPER(hrl.address_line_1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
378 NVL(UPPER(hrl.address_line_3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
379 NVL(UPPER(hrl.town_or_city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
380 NVL(UPPER(hrl.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
381 hrl.location_id = hrlt.location_id AND
382 hrlt.language = userenv('LANG') AND
383 nvl(hrl.business_group_id, nvl(hr_general.get_business_group_id,-99)) =
384 nvl(hr_general.get_business_group_id,-99);
385
386 -- Internal Locations Cursor 2 Bug 2570369
387 CURSOR c_locations_2(cp_transaction_type VARCHAR2,
388 cp_org_id_in NUMBER DEFAULT NULL,
389 cp_address_id_in NUMBER DEFAULT NULL,
390 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
391 cp_address_line1_in VARCHAR2 DEFAULT NULL,
392 cp_address_line2_in VARCHAR2 DEFAULT NULL,
393 cp_address_line3_in VARCHAR2 DEFAULT NULL,
394 cp_address_line4_in VARCHAR2 DEFAULT NULL,
395 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
396 cp_city_in VARCHAR2 DEFAULT NULL,
397 cp_county_in VARCHAR2 DEFAULT NULL,
398 cp_state_in VARCHAR2 DEFAULT NULL,
399 cp_zip_in VARCHAR2 DEFAULT NULL,
400 cp_province_in VARCHAR2 DEFAULT NULL,
401 cp_country_in VARCHAR2 DEFAULT NULL,
402 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
403 SELECT TO_NUMBER(NULL) org_id,
404 hrl.location_id address_id,
405 TO_CHAR(NULL) tp_location_code,
406 TO_CHAR(NULL) tp_location_name,
407 hrl.address1 address_line1,
408 hrl.address2 address_line2,
409 hrl.address3 address_line3,
410 hrl.address4 address_line4,
411 TO_CHAR(NULL) address_line_alt,
412 hrl.city city,
413 hrl.county county,
414 hrl.state state,
415 hrl.postal_code zip,
416 hrl.province province,
417 hrl.country country,
418 TO_CHAR(NULL) region_1,
419 TO_CHAR(NULL) region_2,
420 TO_CHAR(NULL) region_3
421 FROM hz_locations hrl
422 WHERE NVL(UPPER(hrl.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
423 NVL(UPPER(hrl.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
424 NVL(UPPER(hrl.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
425 NVL(UPPER(hrl.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
426 NVL(UPPER(hrl.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%');
427
428 /* Bug 2551002
429 cas.party_site_id = pts.party_site_id AND
430 pts.location_id = hrl.location_id;
431 hz_cust_acct_sites_all cas ,
432 hz_party_sites pts
433 WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
434 Bug2422787
435 hrl.location_id = NVL(UPPER(cp_address_id_in,hrl.location_id) AND
436 NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%')
437 AND
438 */
439
440 --Bug 2422787 pvs.vendor_site_id= NVL(cp_address_id_in,pvs.vendor_site_id) AND
441 -- PO Vendors Cursor Bug 2551002
442 CURSOR c1_vendor_sites(cp_transaction_type VARCHAR2,
443 cp_org_id_in NUMBER DEFAULT NULL,
444 cp_address_id_in NUMBER DEFAULT NULL,
445 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
446 cp_address_line1_in VARCHAR2 DEFAULT NULL,
447 cp_address_line2_in VARCHAR2 DEFAULT NULL,
448 cp_address_line3_in VARCHAR2 DEFAULT NULL,
449 cp_address_line4_in VARCHAR2 DEFAULT NULL,
450 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
451 cp_city_in VARCHAR2 DEFAULT NULL,
452 cp_county_in VARCHAR2 DEFAULT NULL,
453 cp_state_in VARCHAR2 DEFAULT NULL,
454 cp_zip_in VARCHAR2 DEFAULT NULL,
455 cp_province_in VARCHAR2 DEFAULT NULL,
456 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
457 SELECT pvs.org_id org_id,
458 pvs.vendor_site_id address_id,
459 pvs.ece_tp_location_code tp_location_code,
460 TO_CHAR(NULL) tp_location_name,
461 pvs.address_line1 address_line1,
465 pvs.address_lines_alt address_line_alt,
462 pvs.address_line2 address_line2,
463 pvs.address_line3 address_line3,
464 pvs.address_line4 address_line4,
466 pvs.city city,
467 pvs.county county,
468 pvs.state state,
469 pvs.zip zip,
470 pvs.province province,
471 pvs.country country,
472 TO_CHAR(NULL) region_1,
473 TO_CHAR(NULL) region_2,
474 TO_CHAR(NULL) region_3
475 FROM po_vendor_sites_all pvs
476 WHERE nvl(pvs.org_id,-99) = NVL(cp_org_id_in,nvl(pvs.org_id,-99)) AND
477 NVL(UPPER(pvs.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
478 NVL(UPPER(pvs.address_line1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
479 NVL(UPPER(pvs.address_line2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
480 NVL(UPPER(pvs.address_line3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
481 NVL(UPPER(pvs.address_line4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
482 NVL(UPPER(pvs.address_lines_alt),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
483 NVL(UPPER(pvs.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
484 NVL(UPPER(pvs.zip),' ') LIKE NVL(UPPER(cp_zip_in),'%') ;
485
486 -- Bug 3351412
487 CURSOR c2_vendor_sites(cp_transaction_type VARCHAR2,
488 cp_org_id_in NUMBER DEFAULT NULL,
489 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
490 cp_tp_translator_code VARCHAR2 DEFAULT NULL) IS
491 SELECT pvs.org_id org_id,
492 pvs.vendor_site_id address_id,
493 pvs.ece_tp_location_code tp_location_code,
494 TO_CHAR(NULL) tp_location_name,
495 pvs.address_line1 address_line1,
496 pvs.address_line2 address_line2,
497 pvs.address_line3 address_line3,
498 pvs.address_line4 address_line4,
499 pvs.address_lines_alt address_line_alt,
500 pvs.city city,
501 pvs.county county,
502 pvs.state state,
503 pvs.zip zip,
504 pvs.province province,
505 pvs.country country,
506 TO_CHAR(NULL) region_1,
507 TO_CHAR(NULL) region_2,
508 TO_CHAR(NULL) region_3
509 FROM po_vendor_sites_all pvs,
510 ece_tp_details etd
511 WHERE nvl(pvs.org_id,-99) = NVL(cp_org_id_in,nvl(pvs.org_id,-99)) AND
512 NVL(UPPER(pvs.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
513 pvs.tp_header_id = etd.tp_header_id AND
514 NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
515 etd.document_id LIKE UPPER(cp_transaction_type);
516
517 -- Ra Addresses Cursor Bug 2551002
518 -- Ra Addresses Cursor Bug 2551002
519 CURSOR c_ra_address(cp_transaction_type VARCHAR2,
520 cp_org_id_in NUMBER DEFAULT NULL,
521 cp_address_id_in NUMBER DEFAULT NULL,
522 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
523 cp_address_line1_in VARCHAR2 DEFAULT NULL,
524 cp_address_line2_in VARCHAR2 DEFAULT NULL,
525 cp_address_line3_in VARCHAR2 DEFAULT NULL,
526 cp_address_line4_in VARCHAR2 DEFAULT NULL,
527 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
528 cp_city_in VARCHAR2 DEFAULT NULL,
529 cp_county_in VARCHAR2 DEFAULT NULL,
530 cp_state_in VARCHAR2 DEFAULT NULL,
531 cp_zip_in VARCHAR2 DEFAULT NULL,
532 cp_province_in VARCHAR2 DEFAULT NULL,
533 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
534 SELECT cas.org_id org_id,
535 cas.cust_acct_site_id address_id,
536 cas.ece_tp_location_code tp_location_code,
537 TO_CHAR(NULL) tp_location_name,
538 loc.address1 address_line1,
539 loc.address2 address_line2,
540 loc.address3 address_line3,
541 loc.address4 address_line4,
542 loc.address_lines_phonetic address_line_alt,
543 loc.city city,
544 loc.county county,
545 loc.state state,
546 loc.postal_code zip,
547 loc.province province,
548 loc.country country,
549 TO_CHAR(NULL) region_1,
550 TO_CHAR(NULL) region_2,
551 TO_CHAR(NULL) region_3
552 FROM hz_cust_acct_sites_all cas,
553 hz_party_sites pts,
557 NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
554 hz_locations loc
555 WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
556 -- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
558 NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
559 NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
560 NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
561 NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
562 NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
563 NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
564 NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
565 cas.party_site_id = pts.party_site_id AND
566 pts.location_id = loc.location_id;
567
568
569 -- Ra Addresses Cursor 2 Bug 2551002
570 -- The above SQL is split into 2 to improve the POI/POCI performance(bug 2340691).
571 CURSOR c2_ra_address( cp_org_id_in NUMBER DEFAULT NULL,
572 cp_tp_location_code_in VARCHAR2 DEFAULT NULL
573 ) IS
574 SELECT cas.org_id org_id,
575 cas.cust_acct_site_id address_id,
576 cas.ece_tp_location_code tp_location_code,
577 cas.party_site_id party_site_id
578 FROM hz_cust_acct_sites_all cas
579 WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
580 NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') ;
581
582 -- Bug 2708573
583 CURSOR c3_ra_address(cp_transaction_type VARCHAR2,
584 cp_address_line1_in VARCHAR2 DEFAULT NULL,
585 cp_address_line2_in VARCHAR2 DEFAULT NULL,
586 cp_address_line3_in VARCHAR2 DEFAULT NULL,
587 cp_address_line4_in VARCHAR2 DEFAULT NULL,
588 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
589 cp_city_in VARCHAR2 DEFAULT NULL,
590 cp_county_in VARCHAR2 DEFAULT NULL,
591 cp_state_in VARCHAR2 DEFAULT NULL,
592 cp_zip_in VARCHAR2 DEFAULT NULL,
593 cp_province_in VARCHAR2 DEFAULT NULL,
594 cp_region_1_in VARCHAR2 DEFAULT NULL) IS
595 SELECT loc.location_id location_id,
596 TO_CHAR(NULL) tp_location_name,
597 loc.address1 address_line1,
598 loc.address2 address_line2,
599 loc.address3 address_line3,
600 loc.address4 address_line4,
601 loc.address_lines_phonetic address_line_alt,
602 loc.city city,
603 loc.county county,
604 loc.state state,
605 loc.postal_code zip,
606 loc.province province,
607 loc.country country,
608 TO_CHAR(NULL) region_1,
609 TO_CHAR(NULL) region_2,
610 TO_CHAR(NULL) region_3
611 FROM hz_locations loc
612 WHERE NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
613 NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
614 NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
615 NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
616 NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
617 NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
618 NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') ;
619
620 -- bug3351412
621 CURSOR c4_ra_address(cp_transaction_type VARCHAR2,
622 cp_org_id_in NUMBER DEFAULT NULL,
623 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
624 cp_tp_translator_code VARCHAR2 DEFAULT NULL
625 ) IS
626 SELECT cas.org_id org_id,
627 cas.cust_acct_site_id address_id,
628 cas.ece_tp_location_code tp_location_code,
629 cas.party_site_id party_site_id
630 FROM hz_cust_acct_sites_all cas,
631 ece_tp_details etd
632 WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
633 NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
634 cas.tp_header_id = etd.tp_header_id AND
635 NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
636 etd.document_id = UPPER(cp_transaction_type);
637
638
639 CURSOR c5_ra_address(cp_transaction_type VARCHAR2,
640 cp_org_id_in NUMBER DEFAULT NULL,
644 cp_address_line2_in VARCHAR2 DEFAULT NULL,
641 cp_address_id_in NUMBER DEFAULT NULL,
642 cp_tp_location_code_in VARCHAR2 DEFAULT NULL,
643 cp_address_line1_in VARCHAR2 DEFAULT NULL,
645 cp_address_line3_in VARCHAR2 DEFAULT NULL,
646 cp_address_line4_in VARCHAR2 DEFAULT NULL,
647 cp_address_line_alt_in VARCHAR2 DEFAULT NULL,
648 cp_city_in VARCHAR2 DEFAULT NULL,
649 cp_county_in VARCHAR2 DEFAULT NULL,
650 cp_state_in VARCHAR2 DEFAULT NULL,
651 cp_zip_in VARCHAR2 DEFAULT NULL,
652 cp_province_in VARCHAR2 DEFAULT NULL,
653 cp_region_1_in VARCHAR2 DEFAULT NULL,
654 cp_customer_name_in VARCHAR2 DEFAULT NULL,
655 cp_customer_number_in VARCHAR2 DEFAULT NULL
656 ) IS
657 SELECT cas.org_id org_id,
658 cas.cust_acct_site_id address_id,
659 cas.ece_tp_location_code tp_location_code,
660 TO_CHAR(NULL) tp_location_name,
661 loc.address1 address_line1,
662 loc.address2 address_line2,
663 loc.address3 address_line3,
664 loc.address4 address_line4,
665 loc.address_lines_phonetic address_line_alt,
666 loc.city city,
667 loc.county county,
668 loc.state state,
669 loc.postal_code zip,
670 loc.province province,
671 loc.country country,
672 TO_CHAR(NULL) region_1,
673 TO_CHAR(NULL) region_2,
674 TO_CHAR(NULL) region_3
675 FROM hz_cust_acct_sites_all cas,
676 hz_party_sites pts,
677 hz_locations loc,
678 hz_cust_accounts ca,
679 hz_parties pt
680 WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
681 -- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
682 NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
683 NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
684 NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
685 NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
686 NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
687 NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
688 NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
689 NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
690 NVL(UPPER(pt.party_name),' ') LIKE NVL(UPPER(cp_customer_name_in),'%') AND
691 NVL(UPPER(ca.account_number),' ') LIKE NVL(UPPER(cp_customer_number_in),'%') AND
692 cas.party_site_id = pts.party_site_id AND
693 pts.location_id = loc.location_id AND
694 cas.cust_account_id = ca.cust_account_id AND
695 ca.party_id = pt.party_id;
696
697 BEGIN
698 /*********************************************************************
699 | API Related Housekeeping Code |
700 *********************************************************************/
701 -- Standard Start of API savepoint
702 xProgress := 'ADDRB-20-1000';
703 SAVEPOINT ece_get_address;
704
705 -- Standard call to check for call compatibility.
706 IF NOT fnd_api.compatible_api_call(l_api_version_number,
707 p_api_version_number,
708 l_api_name,
709 G_PKG_NAME) THEN
710 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
711 END IF;
712
713 -- Initialize message list if p_init_msg_list is set to TRUE.
714 IF fnd_api.to_boolean(p_init_msg_list) THEN
715 fnd_msg_pub.initialize;
716 END IF;
717
718 -- Initialize API return status to success
719 x_return_status := fnd_api.G_RET_STS_SUCCESS;
720
721 /*********************************************************************
722 | Internal Housekeeping Code |
723 *********************************************************************/
724 IF p_address_id_in IS NOT NULL THEN
725 xProgress := 'ADDRB-20-1010';
726 b_use_addr_id := TRUE;
727 END IF;
728
729 IF p_org_id_in IS NOT NULL THEN
730 xProgress := 'ADDRB-20-1020';
731 b_use_org_id := TRUE;
732 END IF;
733
734 IF p_tp_location_code_in IS NOT NULL THEN
735 xProgress := 'ADDRB-20-1030';
736 b_use_lctc := TRUE;
737 END IF;
738
739 IF p_tp_location_name_in IS NOT NULL THEN
740 xProgress := 'ADDRB-20-1040';
741 b_use_loc_name := TRUE;
745 | Let's Validate the Precedence Code |
742 END IF;
743
744 /*********************************************************************
746 *********************************************************************/
747 xProgress := 'ADDRB-20-1070';
748 v_pcode := NVL(p_precedence_code,'0'); -- Default precedence is 0 if NULL is passed in...
749
750 ec_debug.pl(3,'v_pcode',v_pcode);
751 -- bug3351412
752 IF (v_pcode <> '0' AND
753 v_pcode <> '1' AND v_pcode <> '2') THEN
754 xProgress := 'ADDRB-20-1080';
755 x_return_status := fnd_api.G_RET_STS_ERROR;
756 x_status_code := G_INVALID_PARAMETER;
757 GOTO l_end_of_program;
758 END IF;
759
760 /* Bug 2151462 : Removed the checks on country,state,regions and province */
761 IF (v_pcode = '0' AND (p_address_line1_in IS NOT NULL OR
762 p_address_line2_in IS NOT NULL OR
763 p_address_line3_in IS NOT NULL OR
764 p_address_line4_in IS NOT NULL OR
765 p_address_line_alt_in IS NOT NULL OR
766 p_city_in IS NOT NULL OR
767 p_zip_in IS NOT NULL )) THEN
768 xProgress := 'ADDRB-20-1050';
769 b_use_addr_comp := TRUE;
770 ELSIF (v_pcode = '2' AND (p_address_line1_in IS NOT NULL OR
771 p_address_line2_in IS NOT NULL OR
772 p_address_line3_in IS NOT NULL OR
773 p_address_line4_in IS NOT NULL OR
774 p_address_line_alt_in IS NOT NULL OR
775 p_city_in IS NOT NULL OR
776 p_zip_in IS NOT NULL OR
777 ece_rules_pkg.g_party_name IS NOT NULL OR
778 ece_rules_pkg.g_party_number IS NOT NULL)) THEN
779 xProgress := 'ADDRB-20-1060';
780 b_use_addr_comp := TRUE;
781 END IF;
782
783
784
785 /*********************************************************************
786 | Let's Validate the Org ID |
787 *********************************************************************/
788 IF b_use_org_id THEN
789 xProgress := 'ADDRB-20-1090';
790 SELECT COUNT(*) INTO n_org_id
791 FROM hr_organization_units
792 WHERE organization_id = p_org_id_in;
793
794 IF n_org_id <> 1 THEN
795 -- Looks like we have an invalid Org ID here...
796 xProgress := 'ADDRB-20-1100';
797 x_status_code := G_INVALID_ORG_ID;
798 RAISE fnd_api.G_EXC_ERROR;
799 END IF; -- n_org_id <> 1
800 END IF; -- IF b_use_org_id THEN
801
802 -- Handle special cases when none of the address components
803 -- are provided except the ORG_ID. This should be a success.
804 IF (NOT b_use_addr_id) AND
805 (NOT b_use_lctc) AND
806 (NOT b_use_loc_name) AND
807 (NOT b_use_addr_comp) THEN
808 xProgress := 'ADDRB-20-1060';
809 x_org_id_out := NULL;
810 x_address_id_out := NULL;
811 x_tp_location_code_out := NULL;
812 x_translator_code_out := NULL;
813 x_tp_location_name_out := NULL;
814 x_address_line1_out := NULL;
815 x_address_line2_out := NULL;
816 x_address_line3_out := NULL;
817 x_address_line4_out := NULL;
818 x_address_line_alt_out := NULL;
819 x_city_out := NULL;
820 x_county_out := NULL;
821 x_state_out := NULL;
822 x_zip_out := NULL;
823 x_province_out := NULL;
824 x_country_out := NULL;
825 x_region_1_out := NULL;
826 x_region_2_out := NULL;
827 x_region_3_out := NULL;
828
829 x_return_status := fnd_api.G_RET_STS_SUCCESS;
830 x_status_code := G_NO_ERRORS;
831 GOTO l_end_of_program;
832 END IF;
833
834 /*********************************************************************
835 | Let's See what type of address we're dealing w/ here. |
836 *********************************************************************/
837 /********************
838 | BANK |
839 ********************/
840 xProgress := 'ADDRB-20-1110';
841 IF p_address_type = G_BANK THEN -- Bank
842 xProgress := 'ADDRB-20-1120';
843 IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
844 xProgress := 'ADDRB-20-1130';
845 BEGIN
846 xProgress := 'ADDRB-20-1140';
847 SELECT TO_NUMBER(NULL),
848 cbb.bank_party_id,
849 hcp.edi_ece_tp_location_code,
850 p_translator_code_in,
851 cbb.bank_branch_name,
852 cbb.address_line1,
853 cbb.address_line2,
854 cbb.address_line3,
855 cbb.address_line4,
856 hzl.address_lines_phonetic,
857 cbb.city,
858 hzl.county,
859 cbb.state,
860 cbb.zip,
861 cbb.province,
862 cbb.country,
863 TO_CHAR(NULL),
864 TO_CHAR(NULL),
865 TO_CHAR(NULL)
866 INTO x_org_id_out,
867 x_address_id_out,
868 x_tp_location_code_out,
872 x_address_line2_out,
869 x_translator_code_out,
870 x_tp_location_name_out,
871 x_address_line1_out,
873 x_address_line3_out,
874 x_address_line4_out,
875 x_address_line_alt_out,
876 x_city_out,
877 x_county_out,
878 x_state_out,
879 x_zip_out,
880 x_province_out,
881 x_country_out,
882 x_region_1_out,
883 x_region_2_out,
884 x_region_3_out
885 FROM ce_bank_branches_v cbb,
886 hz_contact_points hcp,
887 hz_locations hzl,
888 hz_party_sites hps
889 WHERE cbb.branch_party_id = p_address_id_in AND
890 hcp.owner_table_id = cbb.branch_party_id AND
891 hcp.owner_table_name = 'HZ_PARTIES' AND
892 hcp.contact_point_type = 'EDI' AND
893 hps.party_id = cbb.branch_party_id AND
894 hps.identifying_address_flag = 'Y' AND
895 hzl.location_id = hps.party_id;
896
897 EXCEPTION
898 WHEN NO_DATA_FOUND THEN
899 -- Looks like we have an invalid ID here...
900 x_status_code := G_INVALID_ADDR_ID;
901 RAISE fnd_api.G_EXC_ERROR;
902
903 END;
904
905 -- Whatever happens, the API has executed successfully...
906 xProgress := 'ADDRB-20-1150';
907 x_return_status := fnd_api.G_RET_STS_SUCCESS;
908
909 -- If we were given LC/TC and it is not the same as the derived LC/TC...
910 IF b_use_lctc AND ((p_tp_location_code_in <> x_tp_location_code_out) OR
911 (p_translator_code_in <> x_translator_code_out)) THEN
912 xProgress := 'ADDRB-20-1160';
913 x_status_code := G_CANNOT_DERIVE_ADDR;
914 -- If were were given addreses components and they are not the same as what
915 -- was derived then...
916 ELSIF b_use_addr_comp THEN
917 /* bug2151462 AND NOT(ece_compare_addresses(p_address_line1_in,
918 p_address_line2_in,
919 p_address_line3_in,
920 p_address_line4_in,
921 p_address_line_alt_in,
922 p_city_in,
923 p_county_in,
924 p_state_in,
925 p_zip_in,
926 p_province_in,
927 p_country_in,
928 p_region_1_in,
929 p_region_2_in,
930 p_region_3_in,
931 x_address_line1_out,
932 x_address_line2_out,
933 x_address_line3_out,
934 x_address_line4_out,
935 x_address_line_alt_out,
936 x_city_out,
937 x_county_out,
938 x_state_out,
939 x_zip_out,
940 x_province_out,
941 x_country_out,
942 x_region_1_out,
943 x_region_2_out,
944 x_region_3_out)) THEN
945 xProgress := 'ADDRB-20-1170';
946 x_status_code := G_INCONSISTENT_ADDR_COMP;
947 -- If we were given location names and it is not the same as the derived
948 -- location name then...
949 ELSIF b_use_loc_name AND (p_tp_location_name_in <> x_tp_location_name_out) THEN
950 */
951 xProgress := 'ADDRB-20-1180';
952 x_status_code := G_INCONSISTENT_ADDR_COMP;
953 ELSE
954 xProgress := 'ADDRB-20-1190';
955 x_status_code := G_NO_ERRORS;
956 END IF;
957
958 -- Whether the address was consistent or not, we have an Address ID so time
959 -- to die...
960 xProgress := 'ADDRB-20-1200';
961 GOTO l_end_of_program;
962
963 END IF; -- IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
964
965 xProgress := 'ADDRB-20-1210';
966 IF b_use_lctc OR b_use_addr_comp THEN -- If LC/TC are available
967 xProgress := 'ADDRB-20-1220';
971 cp_org_id_in => p_org_id_in,
968 IF v_pcode = '1' THEN -- bug3351412
969 FOR r_addr_rec IN c2_bank_branches(
970 cp_transaction_type => p_transaction_type,
972 cp_tp_location_code_in => p_tp_location_code_in,
973 cp_tp_translator_code => p_translator_code_in) LOOP
974
975 n_loop_count := n_loop_count + 1;
976
977 x_org_id_out := r_addr_rec.org_id;
978 x_address_id_out := r_addr_rec.address_id;
979 x_tp_location_code_out := r_addr_rec.tp_location_code;
980 x_tp_location_name_out := r_addr_rec.tp_location_name;
981 x_address_line1_out := r_addr_rec.address_line1;
982 x_address_line2_out := r_addr_rec.address_line2;
983 x_address_line3_out := r_addr_rec.address_line3;
984 x_address_line4_out := r_addr_rec.address_line4;
985 x_address_line_alt_out := r_addr_rec.address_line_alt;
986 x_city_out := r_addr_rec.city;
987 x_county_out := r_addr_rec.county;
988 x_state_out := r_addr_rec.state;
989 x_zip_out := r_addr_rec.zip;
990 x_province_out := r_addr_rec.province;
991 x_country_out := r_addr_rec.country;
992 x_region_1_out := r_addr_rec.region_1;
993 x_region_2_out := r_addr_rec.region_2;
994 x_region_3_out := r_addr_rec.region_3;
995 END LOOP;
996 ELSE
997 FOR r_addr_rec IN c1_bank_branches(
998 cp_transaction_type => p_transaction_type,
999 cp_org_id_in => p_org_id_in,
1000 cp_tp_location_code_in => p_tp_location_code_in,
1001 cp_address_line1_in => p_address_line1_in,
1002 cp_address_line2_in => p_address_line2_in,
1003 cp_address_line3_in => p_address_line3_in,
1004 cp_address_line_alt_in => p_address_line_alt_in,
1005 cp_city_in => p_city_in,
1006 cp_zip_in => p_zip_in) LOOP
1007 /* bug 2151462: added the above parameters to the cursor call */
1008
1009 xProgress := 'ADDRB-20-1230';
1010 n_loop_count := n_loop_count + 1;
1011
1012 /* bug2151462 IF b_use_addr_comp THEN
1013 -- If Address Components are available...
1014 xProgress := 'ADDRB-20-1240';
1015 IF ece_compare_addresses(
1016 p_address_line1_in,
1017 p_address_line2_in,
1018 p_address_line3_in,
1019 p_address_line4_in,
1020 p_address_line_alt_in,
1021 p_city_in,
1022 p_county_in,
1023 p_state_in,
1024 p_zip_in,
1025 p_province_in,
1026 p_country_in,
1027 p_region_1_in,
1028 p_region_2_in,
1029 p_region_3_in,
1030 r_addr_rec.address_line1,
1031 r_addr_rec.address_line2,
1032 r_addr_rec.address_line3,
1033 r_addr_rec.address_line4,
1034 r_addr_rec.address_line_alt,
1035 r_addr_rec.city,
1036 r_addr_rec.county,
1037 r_addr_rec.state,
1038 r_addr_rec.zip,
1039 r_addr_rec.province,
1040 r_addr_rec.country,
1041 r_addr_rec.region_1,
1042 r_addr_rec.region_2,
1043 r_addr_rec.region_3) THEN
1044 xProgress := 'ADDRB-20-1250';
1045 n_match_count := n_match_count + 1;
1046
1047 x_org_id_out := r_addr_rec.org_id;
1048 x_address_id_out := r_addr_rec.address_id;
1049 x_tp_location_code_out := r_addr_rec.tp_location_code;
1050 x_tp_location_name_out := r_addr_rec.tp_location_name;
1051 x_address_line1_out := r_addr_rec.address_line1;
1052 x_address_line2_out := r_addr_rec.address_line2;
1053 x_address_line3_out := r_addr_rec.address_line3;
1054 x_address_line4_out := r_addr_rec.address_line4;
1055 x_address_line_alt_out := r_addr_rec.address_line_alt;
1056 x_city_out := r_addr_rec.city;
1057 x_county_out := r_addr_rec.county;
1058 x_state_out := r_addr_rec.state;
1059 x_zip_out := r_addr_rec.zip;
1060 x_province_out := r_addr_rec.province;
1061 x_country_out := r_addr_rec.country;
1062 x_region_1_out := r_addr_rec.region_1;
1063 x_region_2_out := r_addr_rec.region_2;
1064 x_region_3_out := r_addr_rec.region_3;
1065 END IF; -- IF ece_compare_addresses
1066 ELSE
1067 */
1068 xProgress := 'ADDRB-20-1260';
1069 x_org_id_out := r_addr_rec.org_id;
1073 x_address_line1_out := r_addr_rec.address_line1;
1070 x_address_id_out := r_addr_rec.address_id;
1071 x_tp_location_code_out := r_addr_rec.tp_location_code;
1072 x_tp_location_name_out := r_addr_rec.tp_location_name;
1074 x_address_line2_out := r_addr_rec.address_line2;
1075 x_address_line3_out := r_addr_rec.address_line3;
1076 x_address_line4_out := r_addr_rec.address_line4;
1077 x_address_line_alt_out := r_addr_rec.address_line_alt;
1078 x_city_out := r_addr_rec.city;
1079 x_county_out := r_addr_rec.county;
1080 x_state_out := r_addr_rec.state;
1081 x_zip_out := r_addr_rec.zip;
1082 x_province_out := r_addr_rec.province;
1083 x_country_out := r_addr_rec.country;
1084 x_region_1_out := r_addr_rec.region_1;
1085 x_region_2_out := r_addr_rec.region_2;
1086 x_region_3_out := r_addr_rec.region_3;
1087 --END IF; -- IF b_use_addr_comp THEN
1088 END LOOP;
1089 END IF;
1090
1091 xProgress := 'ADDRB-20-1270';
1092 IF n_loop_count = 0 THEN
1093 xProgress := 'ADDRB-20-1280';
1094 x_org_id_out := p_org_id_in;
1095 x_address_id_out := p_address_id_in;
1096 x_tp_location_code_out := p_tp_location_code_in;
1097 x_translator_code_out := p_translator_code_in;
1098 x_tp_location_name_out := p_tp_location_name_in;
1099 x_address_line1_out := p_address_line1_in;
1100 x_address_line2_out := p_address_line2_in;
1101 x_address_line3_out := p_address_line3_in;
1102 x_address_line4_out := p_address_line4_in;
1103 x_address_line_alt_out := p_address_line_alt_in;
1104 x_city_out := p_city_in;
1105 x_county_out := p_county_in;
1106 x_state_out := p_state_in;
1107 x_zip_out := p_zip_in;
1108 x_province_out := p_province_in;
1109 x_country_out := p_country_in;
1110 x_region_1_out := p_region_1_in;
1111 x_region_2_out := p_region_2_in;
1112 x_region_3_out := p_region_3_in;
1113
1114 IF b_use_addr_comp THEN
1115 xProgress := 'ADDRB-20-1290';
1116 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1117 x_status_code := G_INCONSISTENT_ADDR_COMP;
1118 ELSE
1119 xProgress := 'ADDRB-20-1300';
1120 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1121 x_status_code := G_CANNOT_DERIVE_ADDR;
1122 END IF; -- IF b_use_addr_comp THEN
1123
1124 GOTO l_end_of_program;
1125 ELSIF n_loop_count = 1 THEN
1126 xProgress := 'ADDRB-20-1310';
1127 /*bug2151462 IF b_use_addr_comp THEN
1128 xProgress := 'ADDRB-20-1320';
1129 IF n_match_count = 0 THEN
1130 xProgress := 'ADDRB-20-1330';
1131 x_org_id_out := p_org_id_in;
1132 x_address_id_out := p_address_id_in;
1133 x_tp_location_code_out := p_tp_location_code_in;
1134 x_translator_code_out := p_translator_code_in;
1135 x_tp_location_name_out := p_tp_location_name_in;
1136 x_address_line1_out := p_address_line1_in;
1137 x_address_line2_out := p_address_line2_in;
1138 x_address_line3_out := p_address_line3_in;
1139 x_address_line4_out := p_address_line4_in;
1140 x_address_line_alt_out := p_address_line_alt_in;
1141 x_city_out := p_city_in;
1142 x_county_out := p_county_in;
1143 x_state_out := p_state_in;
1144 x_zip_out := p_zip_in;
1145 x_province_out := p_province_in;
1146 x_country_out := p_country_in;
1147 x_region_1_out := p_region_1_in;
1148 x_region_2_out := p_region_2_in;
1149 x_region_3_out := p_region_3_in;
1150
1151 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1152 x_status_code := G_INCONSISTENT_ADDR_COMP;
1153
1154 GOTO l_end_of_program;
1155 ELSIF n_match_count = 1 THEN
1156 -- No need to assign variables in this scenario. Correct values
1157 -- are already assigned above.
1158 xProgress := 'ADDRB-20-1340';
1159 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1160 x_status_code := G_NO_ERRORS;
1161 GOTO l_end_of_program;
1162 END IF; -- IF n_match_count = 0 THEN
1163 ELSE
1164 */
1165 -- No need to assign variables in this scenario. Correct values
1166 -- are already assigned above.
1167 xProgress := 'ADDRB-20-1350';
1168 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1169 x_status_code := G_NO_ERRORS;
1173 xProgress := 'ADDRB-20-1360';
1170 GOTO l_end_of_program;
1171 -- END IF; -- b_use_addr_comp THEN
1172 ELSE -- n_loop_count > 1
1174 IF b_use_addr_comp THEN
1175 xProgress := 'ADDRB-20-1370';
1176 --IF n_match_count = 0 THEN
1177 --xProgress := 'ADDRB-20-1380';
1178 x_org_id_out := p_org_id_in;
1179 x_address_id_out := p_address_id_in;
1180 x_tp_location_code_out := p_tp_location_code_in;
1181 x_translator_code_out := p_translator_code_in;
1182 x_tp_location_name_out := p_tp_location_name_in;
1183 x_address_line1_out := p_address_line1_in;
1184 x_address_line2_out := p_address_line2_in;
1185 x_address_line3_out := p_address_line3_in;
1186 x_address_line4_out := p_address_line4_in;
1187 x_address_line_alt_out := p_address_line_alt_in;
1188 x_city_out := p_city_in;
1189 x_county_out := p_county_in;
1190 x_state_out := p_state_in;
1191 x_zip_out := p_zip_in;
1192 x_province_out := p_province_in;
1193 x_country_out := p_country_in;
1194 x_region_1_out := p_region_1_in;
1195 x_region_2_out := p_region_2_in;
1196 x_region_3_out := p_region_3_in;
1197
1198 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1199 x_status_code := G_MULTIPLE_ADDR_FOUND;
1200
1201 GOTO l_end_of_program;
1202 /* Bug 2151462 ELSIF n_match_count = 1 THEN
1203 -- No need to assign variables in this scenario. Correct values
1204 -- are already assigned above.
1205 xProgress := 'ADDRB-20-1390';
1206 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1207 x_status_code := G_NO_ERRORS;
1208 GOTO l_end_of_program;
1209 ELSE -- n_match_count > 1
1210 -- No need to assign variables in this scenario. Correct values
1211 -- are already assigned above. However, the Address ID has to be
1212 -- removed since we got multiple matches.
1213 xProgress := 'ADDRB-20-1400';
1214 x_address_id_out := NULL;
1215
1216 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1217 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1218
1219 GOTO l_end_of_program;
1220 END IF; -- IF n_match_count = 0 THEN
1221 */
1222 ELSE
1223 -- We have multiple hits on TC/LC pair and no way to tie-break.
1224 xProgress := 'ADDRB-20-1410';
1225 x_org_id_out := NULL;
1226 x_address_id_out := NULL;
1227 x_tp_location_code_out := p_tp_location_code_in;
1228 x_translator_code_out := p_translator_code_in;
1229 x_tp_location_name_out := NULL;
1230 x_address_line1_out := NULL;
1231 x_address_line2_out := NULL;
1232 x_address_line3_out := NULL;
1233 x_address_line4_out := NULL;
1234 x_address_line_alt_out := NULL;
1235 x_city_out := NULL;
1236 x_county_out := NULL;
1237 x_state_out := NULL;
1238 x_zip_out := NULL;
1239 x_province_out := NULL;
1240 x_country_out := NULL;
1241 x_region_1_out := NULL;
1242 x_region_2_out := NULL;
1243 x_region_3_out := NULL;
1244
1245 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1246 x_status_code := G_MULTIPLE_LOC_FOUND;
1247
1248 GOTO l_end_of_program;
1249 END IF; -- IF b_use_addr_comp THEN
1250 END IF; -- IF n_loop_count = 0 THEN
1251 END IF; -- IF b_use_lctc THEN
1252
1253 IF b_use_addr_comp THEN
1254 -- At this point, all we have are raw address components and nothing else.
1255 xProgress := 'ADDRB-20-1420';
1256 x_org_id_out := p_org_id_in;
1257 x_address_id_out := p_address_id_in;
1258 x_tp_location_code_out := p_tp_location_code_in;
1259 x_translator_code_out := p_translator_code_in;
1260 x_tp_location_name_out := p_tp_location_name_in;
1261 x_address_line1_out := p_address_line1_in;
1262 x_address_line2_out := p_address_line2_in;
1263 x_address_line3_out := p_address_line3_in;
1264 x_address_line4_out := p_address_line4_in;
1265 x_address_line_alt_out := p_address_line_alt_in;
1266 x_city_out := p_city_in;
1267 x_county_out := p_county_in;
1268 x_state_out := p_state_in;
1269 x_zip_out := p_zip_in;
1270 x_province_out := p_province_in;
1271 x_country_out := p_country_in;
1272 x_region_1_out := p_region_1_in;
1273 x_region_2_out := p_region_2_in;
1277 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1274 x_region_3_out := p_region_3_in;
1275
1276 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1278
1279 GOTO l_end_of_program;
1280 END IF; -- IF b_use_addr_comp THEN
1281
1282 xProgress := 'ADDRB-20-1430';
1283 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1284 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1285
1286 GOTO l_end_of_program;
1287
1288 /********************
1289 | CUSTOMER |
1290 ********************/
1291 ELSIF p_address_type = G_CUSTOMER THEN -- Customer
1292 IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
1293 BEGIN
1294 SELECT cas.org_id,
1295 cas.cust_acct_site_id,
1296 cas.ece_tp_location_code,
1297 p_translator_code_in,
1298 TO_CHAR(NULL),
1299 loc.address1,
1300 loc.address2,
1301 loc.address3,
1302 loc.address4,
1303 loc.address_lines_phonetic,
1304 loc.city,
1305 loc.county,
1306 loc.state,
1307 loc.postal_code,
1308 loc.province,
1309 loc.country,
1310 TO_CHAR(NULL),
1311 TO_CHAR(NULL),
1312 TO_CHAR(NULL)
1313 INTO x_org_id_out,
1314 x_address_id_out,
1315 x_tp_location_code_out,
1316 x_translator_code_out,
1317 x_tp_location_name_out,
1318 x_address_line1_out,
1319 x_address_line2_out,
1320 x_address_line3_out,
1321 x_address_line4_out,
1322 x_address_line_alt_out,
1323 x_city_out,
1324 x_county_out,
1325 x_state_out,
1326 x_zip_out,
1327 x_province_out,
1328 x_country_out,
1329 x_region_1_out,
1330 x_region_2_out,
1331 x_region_3_out
1332 FROM hz_cust_acct_sites_all cas,
1333 hz_party_sites pts,
1334 hz_locations loc
1335
1336
1337 WHERE cas.party_site_id = pts.party_site_id AND
1338 pts.location_id = loc.location_id AND
1339 cas.cust_acct_site_id = p_address_id_in;
1340
1341
1342 EXCEPTION
1343 WHEN NO_DATA_FOUND THEN
1344 -- Looks like we have an invalid ID here...
1345 x_status_code := G_INVALID_ADDR_ID;
1346 RAISE fnd_api.G_EXC_ERROR;
1347
1348 END;
1349
1350 -- Whatever happens, the API has executed successfully...
1351 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1352
1353 -- If we were given LC/TC and it is not the same as the derived LC/TC...
1354 IF b_use_lctc AND ((p_tp_location_code_in <> x_tp_location_code_out) OR
1355 (p_translator_code_in <> x_translator_code_out)) THEN
1356 x_status_code := G_CANNOT_DERIVE_ADDR;
1357 -- If were were given addreses components and they are not the same as what
1358 -- was derived then...
1359 ELSIF b_use_addr_comp THEN
1360 /*bug2151462 AND NOT(ece_compare_addresses(p_address_line1_in,
1361 p_address_line2_in,
1362 p_address_line3_in,
1363 p_address_line4_in,
1364 p_address_line_alt_in,
1365 p_city_in,
1366 p_county_in,
1367 p_state_in,
1368 p_zip_in,
1369 p_province_in,
1370 p_country_in,
1371 p_region_1_in,
1372 p_region_2_in,
1373 p_region_3_in,
1374 x_address_line1_out,
1375 x_address_line2_out,
1376 x_address_line3_out,
1377 x_address_line4_out,
1378 x_address_line_alt_out,
1379 x_city_out,
1383 x_province_out,
1380 x_county_out,
1381 x_state_out,
1382 x_zip_out,
1384 x_country_out,
1385 x_region_1_out,
1386 x_region_2_out,
1387 x_region_3_out)) THEN
1388 x_status_code := G_INCONSISTENT_ADDR_COMP;
1389 -- If we were given location names and it is not the same as the derived
1390 -- location name then...
1391 ELSIF b_use_loc_name AND (p_tp_location_name_in <> x_tp_location_name_out) THEN
1392 */
1393 x_status_code := G_INCONSISTENT_ADDR_COMP;
1394 ELSE
1395 x_status_code := G_NO_ERRORS;
1396 END IF;
1397
1398 -- Whether the address was consistent or not, we have an Address ID so time
1399 -- to die...
1400 GOTO l_end_of_program;
1401
1402 END IF; -- IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
1403
1404 --Bug 2340691 Split the c_ra_address cursor into 2 SQLs
1405 --and modified the call to cursor.
1406
1407 IF b_use_lctc AND NOT b_use_addr_comp THEN -- If LC is available
1408 IF v_pcode = '1' THEN -- bug3351412
1409 FOR r_addr_rec IN c4_ra_address(
1410 cp_transaction_type => p_transaction_type,
1411 cp_org_id_in => p_org_id_in,
1412 cp_tp_location_code_in => p_tp_location_code_in,
1413 cp_tp_translator_code => p_translator_code_in) LOOP
1414
1415 n_loop_count := n_loop_count + 1;
1416
1417 BEGIN
1418 SELECT TO_CHAR(NULL) tp_location_name,
1419 loc.address1 address_line1,
1420 loc.address2 address_line2,
1421 loc.address3 address_line3,
1422 loc.address4 address_line4,
1423 loc.address_lines_phonetic address_line_alt,
1424 loc.city city,
1425 loc.county county,
1426 loc.state state,
1427 loc.postal_code zip,
1428 loc.province province,
1429 loc.country country,
1430 TO_CHAR(NULL) region_1,
1431 TO_CHAR(NULL) region_2,
1432 TO_CHAR(NULL) region_3
1433 INTO x_tp_location_name_out,
1434 x_address_line1_out,
1435 x_address_line2_out,
1436 x_address_line3_out,
1437 x_address_line4_out,
1438 x_address_line_alt_out,
1439 x_city_out,
1440 x_county_out,
1441 x_state_out,
1442 x_zip_out,
1443 x_province_out,
1444 x_country_out,
1445 x_region_1_out,
1446 x_region_2_out,
1447 x_region_3_out
1448 FROM hz_party_sites pts,
1449 hz_locations loc
1450 WHERE pts.location_id = loc.location_id
1451 AND pts.party_site_id = r_addr_rec.party_site_id;
1452
1453 EXCEPTION
1454 WHEN NO_DATA_FOUND THEN
1455 exit;
1456
1457 END;
1458
1459 x_org_id_out := r_addr_rec.org_id;
1460 x_address_id_out := r_addr_rec.address_id;
1461 x_tp_location_code_out := r_addr_rec.tp_location_code;
1462
1463 END LOOP;
1464 ELSE
1465 FOR r_addr_rec IN c2_ra_address(
1466 cp_org_id_in => p_org_id_in,
1467 cp_tp_location_code_in => p_tp_location_code_in) LOOP
1468
1469 n_loop_count := n_loop_count + 1;
1470
1471 BEGIN
1472 SELECT TO_CHAR(NULL) tp_location_name,
1473 loc.address1 address_line1,
1474 loc.address2 address_line2,
1475 loc.address3 address_line3,
1476 loc.address4 address_line4,
1477 loc.address_lines_phonetic address_line_alt,
1478 loc.city city,
1479 loc.county county,
1480 loc.state state,
1481 loc.postal_code zip,
1482 loc.province province,
1483 loc.country country,
1484 TO_CHAR(NULL) region_1,
1485 TO_CHAR(NULL) region_2,
1486 TO_CHAR(NULL) region_3
1487 INTO x_tp_location_name_out,
1488 x_address_line1_out,
1489 x_address_line2_out,
1493 x_city_out,
1490 x_address_line3_out,
1491 x_address_line4_out,
1492 x_address_line_alt_out,
1494 x_county_out,
1495 x_state_out,
1496 x_zip_out,
1497 x_province_out,
1498 x_country_out,
1499 x_region_1_out,
1500 x_region_2_out,
1501 x_region_3_out
1502 FROM hz_party_sites pts,
1503 hz_locations loc
1504 WHERE pts.location_id = loc.location_id
1505 AND pts.party_site_id = r_addr_rec.party_site_id;
1506
1507 EXCEPTION
1508 WHEN NO_DATA_FOUND THEN
1509 exit;
1510 END;
1511
1512 x_org_id_out := r_addr_rec.org_id;
1513 x_address_id_out := r_addr_rec.address_id;
1514 x_tp_location_code_out := r_addr_rec.tp_location_code;
1515
1516 END LOOP;
1517 END IF;
1518
1519 ELSIF (b_use_addr_comp AND NOT b_use_lctc) THEN
1520 -- Bug 2708573
1521 IF (v_pcode = '0') then
1522 FOR r_addr_rec IN c3_ra_address(
1523 cp_transaction_type => p_transaction_type,
1524 cp_address_line1_in => p_address_line1_in,
1525 cp_address_line2_in => p_address_line2_in,
1526 cp_address_line3_in => p_address_line3_in,
1527 cp_address_line4_in => p_address_line4_in,
1528 cp_address_line_alt_in => p_address_line_alt_in,
1529 cp_city_in => p_city_in,
1530 cp_zip_in => p_zip_in) LOOP
1531 n_loop_count := n_loop_count + 1;
1532
1533 BEGIN
1534 SELECT cas.org_id,
1535 cas.cust_acct_site_id,
1536 cas.ece_tp_location_code
1537 INTO
1538 x_org_id_out,
1539 x_address_id_out,
1540 x_tp_location_code_out
1541 FROM hz_party_sites pts,
1542 hz_cust_acct_sites_all cas
1543 WHERE cas.party_site_id = pts.party_site_id
1544 AND pts.location_id = r_addr_rec.location_id
1545 AND nvl(cas.org_id,-99) = nvl(p_org_id_in,nvl(cas.org_id,-99));
1546
1547 EXCEPTION
1548 WHEN NO_DATA_FOUND THEN
1549 exit;
1550 END;
1551
1552 x_tp_location_name_out := r_addr_rec.tp_location_name;
1553 x_address_line1_out := r_addr_rec.address_line1;
1554 x_address_line2_out := r_addr_rec.address_line2;
1555 x_address_line3_out := r_addr_rec.address_line3;
1556 x_address_line4_out := r_addr_rec.address_line4;
1557 x_address_line_alt_out := r_addr_rec.address_line_alt;
1558 x_city_out := r_addr_rec.city;
1559 x_county_out := r_addr_rec.county;
1560 x_state_out := r_addr_rec.state;
1561 x_zip_out := r_addr_rec.zip;
1562 x_province_out := r_addr_rec.province;
1563 x_country_out := r_addr_rec.country;
1564 x_region_1_out := r_addr_rec.region_1;
1565 x_region_2_out := r_addr_rec.region_2;
1566 x_region_3_out := r_addr_rec.region_3;
1567 END LOOP;
1568 ELSE
1569 FOR r_addr_rec IN c5_ra_address(
1570 cp_transaction_type => p_transaction_type,
1571 cp_address_line1_in => p_address_line1_in,
1572 cp_address_line2_in => p_address_line2_in,
1573 cp_address_line3_in => p_address_line3_in,
1574 cp_address_line4_in => p_address_line4_in,
1575 cp_address_line_alt_in => p_address_line_alt_in,
1576 cp_city_in => p_city_in,
1577 cp_zip_in => p_zip_in,
1578 cp_customer_name_in => ece_rules_pkg.g_party_name,
1579 cp_customer_number_in => ece_rules_pkg.g_party_number) LOOP
1580 n_loop_count := n_loop_count + 1;
1581 x_org_id_out := r_addr_rec.org_id;
1582 x_address_id_out := r_addr_rec.address_id;
1583 x_tp_location_code_out := r_addr_rec.tp_location_code;
1584 x_tp_location_name_out := r_addr_rec.tp_location_name;
1585 x_address_line1_out := r_addr_rec.address_line1;
1586 x_address_line2_out := r_addr_rec.address_line2;
1587 x_address_line3_out := r_addr_rec.address_line3;
1588 x_address_line4_out := r_addr_rec.address_line4;
1589 x_address_line_alt_out := r_addr_rec.address_line_alt;
1590 x_city_out := r_addr_rec.city;
1591 x_county_out := r_addr_rec.county;
1592 x_state_out := r_addr_rec.state;
1593 x_zip_out := r_addr_rec.zip;
1594 x_province_out := r_addr_rec.province;
1595 x_country_out := r_addr_rec.country;
1599 END LOOP;
1596 x_region_1_out := r_addr_rec.region_1;
1597 x_region_2_out := r_addr_rec.region_2;
1598 x_region_3_out := r_addr_rec.region_3;
1600 END IF;
1601 ELSIF (b_use_lctc AND b_use_addr_comp) THEN
1602 -- Bug 2708573
1603 -- ELSIF (b_use_lctc AND b_use_addr_comp) OR b_use_addr_comp THEN
1604 IF (v_pcode = '2') then
1605 FOR r_addr_rec IN c5_ra_address(
1606 cp_transaction_type => p_transaction_type,
1607 cp_org_id_in => p_org_id_in,
1608 cp_tp_location_code_in => p_tp_location_code_in,
1609 cp_address_line1_in => p_address_line1_in,
1610 cp_address_line2_in => p_address_line2_in,
1611 cp_address_line3_in => p_address_line3_in,
1612 cp_address_line4_in => p_address_line4_in,
1613 cp_address_line_alt_in => p_address_line_alt_in,
1614 cp_city_in => p_city_in,
1615 cp_zip_in => p_zip_in,
1616 cp_customer_name_in => ece_rules_pkg.g_party_name,
1617 cp_customer_number_in => ece_rules_pkg.g_party_number) LOOP
1618 n_loop_count := n_loop_count + 1;
1619 x_org_id_out := r_addr_rec.org_id;
1620 x_address_id_out := r_addr_rec.address_id;
1621 x_tp_location_code_out := r_addr_rec.tp_location_code;
1622 x_tp_location_name_out := r_addr_rec.tp_location_name;
1623 x_address_line1_out := r_addr_rec.address_line1;
1624 x_address_line2_out := r_addr_rec.address_line2;
1625 x_address_line3_out := r_addr_rec.address_line3;
1626 x_address_line4_out := r_addr_rec.address_line4;
1627 x_address_line_alt_out := r_addr_rec.address_line_alt;
1628 x_city_out := r_addr_rec.city;
1629 x_county_out := r_addr_rec.county;
1630 x_state_out := r_addr_rec.state;
1631 x_zip_out := r_addr_rec.zip;
1632 x_province_out := r_addr_rec.province;
1633 x_country_out := r_addr_rec.country;
1634 x_region_1_out := r_addr_rec.region_1;
1635 x_region_2_out := r_addr_rec.region_2;
1636 x_region_3_out := r_addr_rec.region_3;
1637 END LOOP;
1638 ELSE
1639 -- Bug 2340691 If LC,Addr_comp or just Addr_comp are available
1640 FOR r_addr_rec IN c_ra_address(
1641 cp_transaction_type => p_transaction_type,
1642 cp_org_id_in => p_org_id_in,
1643 cp_tp_location_code_in => p_tp_location_code_in,
1644 cp_address_line1_in => p_address_line1_in,
1645 cp_address_line2_in => p_address_line2_in,
1646 cp_address_line3_in => p_address_line3_in,
1647 cp_address_line4_in => p_address_line4_in,
1648 cp_address_line_alt_in => p_address_line_alt_in,
1649 cp_city_in => p_city_in,
1650 cp_zip_in => p_zip_in) LOOP
1651 /* bug 2151462: added the above parameters to the cursor call */
1652
1653 n_loop_count := n_loop_count + 1;
1654
1655 /*bug2151462 IF b_use_addr_comp THEN -- If Address Components are available...
1656 IF ece_compare_addresses(
1657 p_address_line1_in,
1658 p_address_line2_in,
1659 p_address_line3_in,
1660 p_address_line4_in,
1661 p_address_line_alt_in,
1662 p_city_in,
1663 p_county_in,
1664 p_state_in,
1665 p_zip_in,
1666 p_province_in,
1667 p_country_in,
1668 p_region_1_in,
1669 p_region_2_in,
1670 p_region_3_in,
1671 r_addr_rec.address_line1,
1672 r_addr_rec.address_line2,
1673 r_addr_rec.address_line3,
1674 r_addr_rec.address_line4,
1675 r_addr_rec.address_line_alt,
1676 r_addr_rec.city,
1677 r_addr_rec.county,
1678 r_addr_rec.state,
1679 r_addr_rec.zip,
1680 r_addr_rec.province,
1681 r_addr_rec.country,
1682 r_addr_rec.region_1,
1683 r_addr_rec.region_2,
1684 r_addr_rec.region_3) THEN
1685 n_match_count := n_match_count + 1;
1686
1687 x_org_id_out := r_addr_rec.org_id;
1688 x_address_id_out := r_addr_rec.address_id;
1689 x_tp_location_code_out := r_addr_rec.tp_location_code;
1690 x_tp_location_name_out := r_addr_rec.tp_location_name;
1691 x_address_line1_out := r_addr_rec.address_line1;
1692 x_address_line2_out := r_addr_rec.address_line2;
1693 x_address_line3_out := r_addr_rec.address_line3;
1694 x_address_line4_out := r_addr_rec.address_line4;
1695 x_address_line_alt_out := r_addr_rec.address_line_alt;
1696 x_city_out := r_addr_rec.city;
1700 x_province_out := r_addr_rec.province;
1697 x_county_out := r_addr_rec.county;
1698 x_state_out := r_addr_rec.state;
1699 x_zip_out := r_addr_rec.zip;
1701 x_country_out := r_addr_rec.country;
1702 x_region_1_out := r_addr_rec.region_1;
1703 x_region_2_out := r_addr_rec.region_2;
1704 x_region_3_out := r_addr_rec.region_3;
1705 END IF; -- IF ece_compare_addresses
1706 ELSE
1707 */
1708 x_org_id_out := r_addr_rec.org_id;
1709 x_address_id_out := r_addr_rec.address_id;
1710 x_tp_location_code_out := r_addr_rec.tp_location_code;
1711 x_tp_location_name_out := r_addr_rec.tp_location_name;
1712 x_address_line1_out := r_addr_rec.address_line1;
1713 x_address_line2_out := r_addr_rec.address_line2;
1714 x_address_line3_out := r_addr_rec.address_line3;
1715 x_address_line4_out := r_addr_rec.address_line4;
1716 x_address_line_alt_out := r_addr_rec.address_line_alt;
1717 x_city_out := r_addr_rec.city;
1718 x_county_out := r_addr_rec.county;
1719 x_state_out := r_addr_rec.state;
1720 x_zip_out := r_addr_rec.zip;
1721 x_province_out := r_addr_rec.province;
1722 x_country_out := r_addr_rec.country;
1723 x_region_1_out := r_addr_rec.region_1;
1724 x_region_2_out := r_addr_rec.region_2;
1725 x_region_3_out := r_addr_rec.region_3;
1726 --END IF; -- IF b_use_addr_comp THEN
1727 END LOOP;
1728
1729 END IF;
1730 END IF;
1731 IF b_use_lctc OR b_use_addr_comp THEN -- Bug 2340691
1732 IF n_loop_count = 0 THEN
1733 x_org_id_out := p_org_id_in;
1734 x_address_id_out := p_address_id_in;
1735 x_tp_location_code_out := p_tp_location_code_in;
1736 x_translator_code_out := nvl(ece_rules_pkg.g_party_number,p_translator_code_in);
1737 x_tp_location_name_out := nvl(ece_rules_pkg.g_party_name,p_tp_location_name_in);
1738 x_address_line1_out := p_address_line1_in;
1739 x_address_line2_out := p_address_line2_in;
1740 x_address_line3_out := p_address_line3_in;
1741 x_address_line4_out := p_address_line4_in;
1742 x_address_line_alt_out := p_address_line_alt_in;
1743 x_city_out := p_city_in;
1744 x_county_out := p_county_in;
1745 x_state_out := p_state_in;
1746 x_zip_out := p_zip_in;
1747 x_province_out := p_province_in;
1748 x_country_out := p_country_in;
1749 x_region_1_out := p_region_1_in;
1750 x_region_2_out := p_region_2_in;
1751 x_region_3_out := p_region_3_in;
1752
1753 IF b_use_addr_comp THEN
1754 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1755 x_status_code := G_INCONSISTENT_ADDR_COMP;
1756 ELSE
1757 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1758 x_status_code := G_CANNOT_DERIVE_ADDR;
1759 END IF; -- IF b_use_addr_comp THEN
1760
1761 GOTO l_end_of_program;
1762 ELSIF n_loop_count = 1 THEN
1763 /*bug2151462 IF b_use_addr_comp THEN
1764 IF n_match_count = 0 THEN
1765 x_org_id_out := p_org_id_in;
1766 x_address_id_out := p_address_id_in;
1767 x_tp_location_code_out := p_tp_location_code_in;
1768 x_translator_code_out := p_translator_code_in;
1769 x_tp_location_name_out := p_tp_location_name_in;
1770 x_address_line1_out := p_address_line1_in;
1771 x_address_line2_out := p_address_line2_in;
1772 x_address_line3_out := p_address_line3_in;
1773 x_address_line4_out := p_address_line4_in;
1774 x_address_line_alt_out := p_address_line_alt_in;
1775 x_city_out := p_city_in;
1776 x_county_out := p_county_in;
1777 x_state_out := p_state_in;
1778 x_zip_out := p_zip_in;
1779 x_province_out := p_province_in;
1780 x_country_out := p_country_in;
1781 x_region_1_out := p_region_1_in;
1782 x_region_2_out := p_region_2_in;
1783 x_region_3_out := p_region_3_in;
1784
1785 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1786 x_status_code := G_INCONSISTENT_ADDR_COMP;
1787
1788 GOTO l_end_of_program;
1789 ELSIF n_match_count = 1 THEN
1790 -- No need to assign variables in this scenario. Correct values
1791 -- are already assigned above.
1792 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1793 x_status_code := G_NO_ERRORS;
1794 GOTO l_end_of_program;
1798 -- No need to assign variables in this scenario. Correct values
1795 END IF; -- IF n_match_count = 0 THEN
1796 ELSE
1797 */
1799 -- are already assigned above.
1800 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1801 x_status_code := G_NO_ERRORS;
1802 GOTO l_end_of_program;
1803 --END IF; -- b_use_addr_comp THEN
1804 ELSE -- n_loop_count > 1
1805 IF b_use_addr_comp THEN
1806 --IF n_match_count = 0 THEN
1807 x_org_id_out := p_org_id_in;
1808 x_address_id_out := p_address_id_in;
1809 x_tp_location_code_out := p_tp_location_code_in;
1810 x_translator_code_out := nvl(ece_rules_pkg.g_party_number,p_translator_code_in);
1811 x_tp_location_name_out := nvl(ece_rules_pkg.g_party_name,p_tp_location_name_in);
1812 x_address_line1_out := p_address_line1_in;
1813 x_address_line2_out := p_address_line2_in;
1814 x_address_line3_out := p_address_line3_in;
1815 x_address_line4_out := p_address_line4_in;
1816 x_address_line_alt_out := p_address_line_alt_in;
1817 x_city_out := p_city_in;
1818 x_county_out := p_county_in;
1819 x_state_out := p_state_in;
1820 x_zip_out := p_zip_in;
1821 x_province_out := p_province_in;
1822 x_country_out := p_country_in;
1823 x_region_1_out := p_region_1_in;
1824 x_region_2_out := p_region_2_in;
1825 x_region_3_out := p_region_3_in;
1826
1827 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1828 x_status_code := G_MULTIPLE_ADDR_FOUND;
1829
1830 GOTO l_end_of_program;
1831 /* bug2151462 ELSIF n_match_count = 1 THEN
1832 -- No need to assign variables in this scenario. Correct values
1833 -- are already assigned above.
1834 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1835 x_status_code := G_NO_ERRORS;
1836 GOTO l_end_of_program;
1837 ELSE -- n_match_count > 1
1838 -- No need to assign variables in this scenario. Correct values
1839 -- are already assigned above. However, the Address ID has to be
1840 -- removed since we got multiple matches.
1841 x_address_id_out := NULL;
1842
1843 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1844 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1845
1846 GOTO l_end_of_program;
1847 END IF; -- IF n_match_count = 0 THEN
1848 */
1849 ELSE
1850 -- We have multiple hits on TC/LC pair and no way to tie-break.
1851 x_org_id_out := NULL;
1852 x_address_id_out := NULL;
1853 x_tp_location_code_out := p_tp_location_code_in;
1854 x_translator_code_out := p_translator_code_in;
1855 x_tp_location_name_out := NULL;
1856 x_address_line1_out := NULL;
1857 x_address_line2_out := NULL;
1858 x_address_line3_out := NULL;
1859 x_address_line4_out := NULL;
1860 x_address_line_alt_out := NULL;
1861 x_city_out := NULL;
1862 x_county_out := NULL;
1863 x_state_out := NULL;
1864 x_zip_out := NULL;
1865 x_province_out := NULL;
1866 x_country_out := NULL;
1867 x_region_1_out := NULL;
1868 x_region_2_out := NULL;
1869 x_region_3_out := NULL;
1870
1871 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1872 x_status_code := G_MULTIPLE_LOC_FOUND;
1873
1874 GOTO l_end_of_program;
1875 END IF; -- IF b_use_addr_comp THEN
1876 END IF; -- IF n_loop_count = 0 THEN
1877 END IF; -- IF b_use_lctc THEN
1878
1879 IF b_use_addr_comp THEN
1880 -- At this point, all we have are raw address components and nothing else.
1881 x_org_id_out := p_org_id_in;
1882 x_address_id_out := p_address_id_in;
1883 x_tp_location_code_out := p_tp_location_code_in;
1884 x_translator_code_out := nvl(ece_rules_pkg.g_party_number,p_translator_code_in);
1885 x_tp_location_name_out := nvl(ece_rules_pkg.g_party_name,p_tp_location_name_in);
1886 x_address_line1_out := p_address_line1_in;
1887 x_address_line2_out := p_address_line2_in;
1888 x_address_line3_out := p_address_line3_in;
1889 x_address_line4_out := p_address_line4_in;
1890 x_address_line_alt_out := p_address_line_alt_in;
1891 x_city_out := p_city_in;
1892 x_county_out := p_county_in;
1893 x_state_out := p_state_in;
1894 x_zip_out := p_zip_in;
1895 x_province_out := p_province_in;
1896 x_country_out := p_country_in;
1900
1897 x_region_1_out := p_region_1_in;
1898 x_region_2_out := p_region_2_in;
1899 x_region_3_out := p_region_3_in;
1901 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1902 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1903
1904 GOTO l_end_of_program;
1905 END IF; -- IF b_use_addr_comp THEN
1906
1907 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1908 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
1909
1910 GOTO l_end_of_program;
1911
1912 /********************
1913 | INTERNAL |
1914 ********************/
1915 ELSIF p_address_type = G_HR_LOCATION THEN -- Internal Location
1916 IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
1917 BEGIN
1918 SELECT -- hrl.org_id org_id,
1919 hrl.location_id,
1920 hrl.ece_tp_location_code,
1921 p_translator_code_in,
1922 hrl.location_code,
1923 hrl.address_line_1,
1924 hrl.address_line_2,
1925 hrl.address_line_3,
1926 TO_CHAR(NULL),
1927 TO_CHAR(NULL),
1928 hrl.town_or_city,
1929 TO_CHAR(NULL),
1930 TO_CHAR(NULL),
1931 hrl.postal_code,
1932 TO_CHAR(NULL),
1933 hrl.country,
1934 hrl.region_1,
1935 hrl.region_2,
1936 hrl.region_3
1937 INTO -- x_org_id_out,
1938 x_address_id_out,
1939 x_tp_location_code_out,
1940 x_translator_code_out,
1941 x_tp_location_name_out,
1942 x_address_line1_out,
1943 x_address_line2_out,
1944 x_address_line3_out,
1945 x_address_line4_out,
1946 x_address_line_alt_out,
1947 x_city_out,
1948 x_county_out,
1949 x_state_out,
1950 x_zip_out,
1951 x_province_out,
1952 x_country_out,
1953 x_region_1_out,
1954 x_region_2_out,
1955 x_region_3_out
1956 FROM hr_locations_all hrl
1957 WHERE hrl.location_id = p_address_id_in;
1958
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 BEGIN -- Bug 2743560
1962 SELECT -- hrl.org_id org_id,
1963 hrl.location_id,
1964 TO_CHAR(NULL),
1965 p_translator_code_in,
1966 TO_CHAR(NULL),
1967 hrl.address1,
1968 hrl.address2,
1969 hrl.address3,
1970 hrl.address4,
1971 TO_CHAR(NULL),
1972 hrl.city,
1973 hrl.county,
1974 hrl.state,
1975 hrl.postal_code,
1976 hrl.province,
1977 hrl.country,
1978 TO_CHAR(NULL),
1979 TO_CHAR(NULL),
1980 TO_CHAR(NULL)
1981 INTO -- x_org_id_out,
1982 x_address_id_out,
1983 x_tp_location_code_out,
1984 x_translator_code_out,
1985 x_tp_location_name_out,
1986 x_address_line1_out,
1987 x_address_line2_out,
1988 x_address_line3_out,
1989 x_address_line4_out,
1990 x_address_line_alt_out,
1991 x_city_out,
1992 x_county_out,
1993 x_state_out,
1994 x_zip_out,
1995 x_province_out,
1996 x_country_out,
1997 x_region_1_out,
1998 x_region_2_out,
1999 x_region_3_out
2000 FROM hz_locations hrl
2001 WHERE hrl.location_id = p_address_id_in;
2002
2003 EXCEPTION
2004 WHEN NO_DATA_FOUND THEN
2005 -- Looks like we have an invalid ID here...
2006 x_status_code := G_INVALID_ADDR_ID;
2007 RAISE fnd_api.G_EXC_ERROR;
2008
2009 END;
2010 END;
2011
2012 -- Whatever happens, the API has executed successfully...
2013 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2014
2015 -- If we were given LC/TC and it is not the same as the derived LC/TC...
2016 IF b_use_lctc AND ((p_tp_location_code_in <> x_tp_location_code_out) OR
2020 -- was derived then...
2017 (p_translator_code_in <> x_translator_code_out)) THEN
2018 x_status_code := G_CANNOT_DERIVE_ADDR;
2019 -- If were were given addreses components and they are not the same as what
2021 ELSIF b_use_addr_comp THEN
2022 /* bug2151462 NOT(ece_compare_addresses(p_address_line1_in,
2023 p_address_line2_in,
2024 p_address_line3_in,
2025 p_address_line4_in,
2026 p_address_line_alt_in,
2027 p_city_in,
2028 p_county_in,
2029 p_state_in,
2030 p_zip_in,
2031 p_province_in,
2032 p_country_in,
2033 p_region_1_in,
2034 p_region_2_in,
2035 p_region_3_in,
2036 x_address_line1_out,
2037 x_address_line2_out,
2038 x_address_line3_out,
2039 x_address_line4_out,
2040 x_address_line_alt_out,
2041 x_city_out,
2042 x_county_out,
2043 x_state_out,
2044 x_zip_out,
2045 x_province_out,
2046 x_country_out,
2047 x_region_1_out,
2048 x_region_2_out,
2049 x_region_3_out)) THEN
2050 x_status_code := G_INCONSISTENT_ADDR_COMP;
2051 - If we were given location names and it is not the same as the derived
2052 -- location name then...
2053 ELSIF b_use_loc_name AND (p_tp_location_name_in <> x_tp_location_name_out) THEN
2054 */
2055 x_status_code := G_INCONSISTENT_ADDR_COMP;
2056 ELSE
2057 x_status_code := G_NO_ERRORS;
2058 END IF;
2059
2060 -- Whether the address was consistent or not, we have an Address ID so time
2061 -- to die...
2062 GOTO l_end_of_program;
2063
2064 END IF; -- IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
2065
2066 IF b_use_lctc OR b_use_addr_comp THEN -- If LC/TC are available
2067 /* Bug 2570369 Split the c_locations cursor in two. Thus scan the 2nd
2068 cursor if the 1st cursor returns no record
2069 */
2070 FOR r_addr_rec IN c_locations_1(
2071 cp_transaction_type => p_transaction_type,
2072 cp_org_id_in => p_org_id_in,
2073 cp_tp_location_code_in => p_tp_location_code_in,
2074 cp_address_line1_in => p_address_line1_in,
2075 cp_address_line2_in => p_address_line2_in,
2076 cp_address_line3_in => p_address_line3_in,
2077 cp_city_in => p_city_in,
2078 cp_zip_in => p_zip_in) LOOP
2079 /* bug 2151462: added the above parameters to the cursor call */
2080
2081 n_loop_count := n_loop_count + 1;
2082
2083 /*bug2151462 IF b_use_addr_comp THEN -- If Address Components are available...
2084 IF ece_compare_addresses(
2085 p_address_line1_in,
2086 p_address_line2_in,
2087 p_address_line3_in,
2088 p_address_line4_in,
2089 p_address_line_alt_in,
2090 p_city_in,
2091 p_county_in,
2092 p_state_in,
2093 p_zip_in,
2094 p_province_in,
2095 p_country_in,
2096 p_region_1_in,
2097 p_region_2_in,
2098 p_region_3_in,
2099 r_addr_rec.address_line1,
2100 r_addr_rec.address_line2,
2101 r_addr_rec.address_line3,
2102 r_addr_rec.address_line4,
2103 r_addr_rec.address_line_alt,
2104 r_addr_rec.city,
2105 r_addr_rec.county,
2106 r_addr_rec.state,
2107 r_addr_rec.zip,
2108 r_addr_rec.province,
2109 r_addr_rec.country,
2110 r_addr_rec.region_1,
2111 r_addr_rec.region_2,
2112 r_addr_rec.region_3) THEN
2113 n_match_count := n_match_count + 1;
2114 ec_debug.pl(0,'n_match_count',n_match_count);
2115
2116 x_org_id_out := r_addr_rec.org_id;
2120 x_address_line1_out := r_addr_rec.address_line1;
2117 x_address_id_out := r_addr_rec.address_id;
2118 x_tp_location_code_out := r_addr_rec.tp_location_code;
2119 x_tp_location_name_out := r_addr_rec.tp_location_name;
2121 x_address_line2_out := r_addr_rec.address_line2;
2122 x_address_line3_out := r_addr_rec.address_line3;
2123 x_address_line4_out := r_addr_rec.address_line4;
2124 x_address_line_alt_out := r_addr_rec.address_line_alt;
2125 x_city_out := r_addr_rec.city;
2126 x_county_out := r_addr_rec.county;
2127 x_state_out := r_addr_rec.state;
2128 x_zip_out := r_addr_rec.zip;
2129 x_province_out := r_addr_rec.province;
2130 x_country_out := r_addr_rec.country;
2131 x_region_1_out := r_addr_rec.region_1;
2132 x_region_2_out := r_addr_rec.region_2;
2133 x_region_3_out := r_addr_rec.region_3;
2134 END IF; -- IF ece_compare_addresses
2135 ELSE
2136 */
2137 x_org_id_out := r_addr_rec.org_id;
2138 x_address_id_out := r_addr_rec.address_id;
2139 x_tp_location_code_out := r_addr_rec.tp_location_code;
2140 x_tp_location_name_out := r_addr_rec.tp_location_name;
2141 x_address_line1_out := r_addr_rec.address_line1;
2142 x_address_line2_out := r_addr_rec.address_line2;
2143 x_address_line3_out := r_addr_rec.address_line3;
2144 x_address_line4_out := r_addr_rec.address_line4;
2145 x_address_line_alt_out := r_addr_rec.address_line_alt;
2146 x_city_out := r_addr_rec.city;
2147 x_county_out := r_addr_rec.county;
2148 x_state_out := r_addr_rec.state;
2149 x_zip_out := r_addr_rec.zip;
2150 x_province_out := r_addr_rec.province;
2151 x_country_out := r_addr_rec.country;
2152 x_region_1_out := r_addr_rec.region_1;
2153 x_region_2_out := r_addr_rec.region_2;
2154 x_region_3_out := r_addr_rec.region_3;
2155 x_translator_code_out := r_addr_rec.inv_organization_id; -- Bug 2570369
2156 --END IF; -- IF b_use_addr_comp THEN
2157 END LOOP;
2158
2159 /* Bug 2570369 Split the c_locations cursor in two. Thus scan the 2nd
2160 cursor if the 1st cursor returns no record
2161 */
2162 IF n_loop_count = 0 AND b_use_addr_comp THEN
2163 FOR r_addr_rec IN c_locations_2(
2164 cp_transaction_type => p_transaction_type,
2165 cp_org_id_in => p_org_id_in,
2166 cp_tp_location_code_in => p_tp_location_code_in,
2167 cp_address_line1_in => p_address_line1_in,
2168 cp_address_line2_in => p_address_line2_in,
2169 cp_address_line3_in => p_address_line3_in,
2170 cp_city_in => p_city_in,
2171 cp_zip_in => p_zip_in) LOOP
2172
2173 n_loop_count := n_loop_count + 1;
2174
2175 x_org_id_out := r_addr_rec.org_id;
2176 x_address_id_out := r_addr_rec.address_id;
2177 x_tp_location_code_out := r_addr_rec.tp_location_code;
2178 x_tp_location_name_out := r_addr_rec.tp_location_name;
2179 x_address_line1_out := r_addr_rec.address_line1;
2180 x_address_line2_out := r_addr_rec.address_line2;
2181 x_address_line3_out := r_addr_rec.address_line3;
2182 x_address_line4_out := r_addr_rec.address_line4;
2183 x_address_line_alt_out := r_addr_rec.address_line_alt;
2184 x_city_out := r_addr_rec.city;
2185 x_county_out := r_addr_rec.county;
2186 x_state_out := r_addr_rec.state;
2187 x_zip_out := r_addr_rec.zip;
2188 x_province_out := r_addr_rec.province;
2189 x_country_out := r_addr_rec.country;
2190 x_region_1_out := r_addr_rec.region_1;
2191 x_region_2_out := r_addr_rec.region_2;
2192 x_region_3_out := r_addr_rec.region_3;
2193 END LOOP;
2194 END IF;
2195
2196 IF n_loop_count = 0 THEN
2197 x_org_id_out := p_org_id_in;
2198 x_address_id_out := p_address_id_in;
2199 x_tp_location_code_out := p_tp_location_code_in;
2200 x_translator_code_out := p_translator_code_in;
2201 x_tp_location_name_out := p_tp_location_name_in;
2202 x_address_line1_out := p_address_line1_in;
2203 x_address_line2_out := p_address_line2_in;
2204 x_address_line3_out := p_address_line3_in;
2205 x_address_line4_out := p_address_line4_in;
2206 x_address_line_alt_out := p_address_line_alt_in;
2207 x_city_out := p_city_in;
2208 x_county_out := p_county_in;
2209 x_state_out := p_state_in;
2210 x_zip_out := p_zip_in;
2211 x_province_out := p_province_in;
2215 x_region_3_out := p_region_3_in;
2212 x_country_out := p_country_in;
2213 x_region_1_out := p_region_1_in;
2214 x_region_2_out := p_region_2_in;
2216
2217 IF b_use_addr_comp THEN
2218 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2219 x_status_code := G_INCONSISTENT_ADDR_COMP;
2220 ELSE
2221 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2222 x_status_code := G_CANNOT_DERIVE_ADDR;
2223 END IF; -- IF b_use_addr_comp THEN
2224
2225 GOTO l_end_of_program;
2226 ELSIF n_loop_count = 1 THEN
2227 /*bug2151462 IF b_use_addr_comp THEN
2228 IF n_match_count = 0 THEN
2229 x_org_id_out := p_org_id_in;
2230 x_address_id_out := p_address_id_in;
2231 x_tp_location_code_out := p_tp_location_code_in;
2232 x_translator_code_out := p_translator_code_in;
2233 x_tp_location_name_out := p_tp_location_name_in;
2234 x_address_line1_out := p_address_line1_in;
2235 x_address_line2_out := p_address_line2_in;
2236 x_address_line3_out := p_address_line3_in;
2237 x_address_line4_out := p_address_line4_in;
2238 x_address_line_alt_out := p_address_line_alt_in;
2239 x_city_out := p_city_in;
2240 x_county_out := p_county_in;
2241 x_state_out := p_state_in;
2242 x_zip_out := p_zip_in;
2243 x_province_out := p_province_in;
2244 x_country_out := p_country_in;
2245 x_region_1_out := p_region_1_in;
2246 x_region_2_out := p_region_2_in;
2247 x_region_3_out := p_region_3_in;
2248
2249 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2250 x_status_code := G_INCONSISTENT_ADDR_COMP;
2251
2252 GOTO l_end_of_program;
2253 ELSIF n_match_count = 1 THEN
2254 -- No need to assign variables in this scenario. Correct values
2255 -- are already assigned above.
2256 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2257 x_status_code := G_NO_ERRORS;
2258 GOTO l_end_of_program;
2259 END IF; -- IF n_match_count = 0 THEN
2260 ELSE
2261 */
2262 -- No need to assign variables in this scenario. Correct values
2263 -- are already assigned above.
2264 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2265 x_status_code := G_NO_ERRORS;
2266 GOTO l_end_of_program;
2267 -- END IF; -- b_use_addr_comp THEN
2268 ELSE -- n_loop_count > 1
2269 IF b_use_addr_comp THEN
2270 --IF n_match_count = 0 THEN
2271 x_org_id_out := p_org_id_in;
2272 x_address_id_out := p_address_id_in;
2273 x_tp_location_code_out := p_tp_location_code_in;
2274 x_translator_code_out := p_translator_code_in;
2275 x_tp_location_name_out := p_tp_location_name_in;
2276 x_address_line1_out := p_address_line1_in;
2277 x_address_line2_out := p_address_line2_in;
2278 x_address_line3_out := p_address_line3_in;
2279 x_address_line4_out := p_address_line4_in;
2280 x_address_line_alt_out := p_address_line_alt_in;
2281 x_city_out := p_city_in;
2282 x_county_out := p_county_in;
2283 x_state_out := p_state_in;
2284 x_zip_out := p_zip_in;
2285 x_province_out := p_province_in;
2286 x_country_out := p_country_in;
2287 x_region_1_out := p_region_1_in;
2288 x_region_2_out := p_region_2_in;
2289 x_region_3_out := p_region_3_in;
2290
2291 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2292 x_status_code := G_MULTIPLE_ADDR_FOUND;
2293
2294 GOTO l_end_of_program;
2295 /* bug2151462 ELSIF n_match_count = 1 THEN
2296 -- No need to assign variables in this scenario. Correct values
2297 -- are already assigned above.
2298 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2299 x_status_code := G_NO_ERRORS;
2300 GOTO l_end_of_program;
2301 ELSE -- n_match_count > 1
2302 -- No need to assign variables in this scenario. Correct values
2303 -- are already assigned above. However, the Address ID has to be
2304 -- removed since we got multiple matches.
2305 x_address_id_out := NULL;
2306 ec_debug.pl(3,'Failed here ');
2307 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2308 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2309
2310 GOTO l_end_of_program;
2311 END IF; -- IF n_match_count = 0 THEN
2312 */
2313 ELSE
2317 x_tp_location_code_out := p_tp_location_code_in;
2314 -- We have multiple hits on TC/LC pair and no way to tie-break.
2315 x_org_id_out := NULL;
2316 x_address_id_out := NULL;
2318 x_translator_code_out := p_translator_code_in;
2319 x_tp_location_name_out := NULL;
2320 x_address_line1_out := NULL;
2321 x_address_line2_out := NULL;
2322 x_address_line3_out := NULL;
2323 x_address_line4_out := NULL;
2324 x_address_line_alt_out := NULL;
2325 x_city_out := NULL;
2326 x_county_out := NULL;
2327 x_state_out := NULL;
2328 x_zip_out := NULL;
2329 x_province_out := NULL;
2330 x_country_out := NULL;
2331 x_region_1_out := NULL;
2332 x_region_2_out := NULL;
2333 x_region_3_out := NULL;
2334
2335 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2336 x_status_code := G_MULTIPLE_LOC_FOUND;
2337
2338 GOTO l_end_of_program;
2339 END IF; -- IF b_use_addr_comp THEN
2340 END IF; -- IF n_loop_count = 0 THEN
2341 END IF; -- IF b_use_lctc THEN
2342
2343 IF b_use_addr_comp THEN
2344 -- At this point, all we have are raw address components and nothing else.
2345 x_org_id_out := p_org_id_in;
2346 x_address_id_out := p_address_id_in;
2347 x_tp_location_code_out := p_tp_location_code_in;
2348 x_translator_code_out := p_translator_code_in;
2349 x_tp_location_name_out := p_tp_location_name_in;
2350 x_address_line1_out := p_address_line1_in;
2351 x_address_line2_out := p_address_line2_in;
2352 x_address_line3_out := p_address_line3_in;
2353 x_address_line4_out := p_address_line4_in;
2354 x_address_line_alt_out := p_address_line_alt_in;
2355 x_city_out := p_city_in;
2356 x_county_out := p_county_in;
2357 x_state_out := p_state_in;
2358 x_zip_out := p_zip_in;
2359 x_province_out := p_province_in;
2360 x_country_out := p_country_in;
2361 x_region_1_out := p_region_1_in;
2362 x_region_2_out := p_region_2_in;
2363 x_region_3_out := p_region_3_in;
2364
2365 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2366 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2367
2368 GOTO l_end_of_program;
2369 END IF; -- IF b_use_addr_comp THEN
2370
2371 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2372 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2373
2374 GOTO l_end_of_program;
2375
2376 /********************
2377 | SUPPLIER |
2378 ********************/
2379 ELSIF p_address_type = G_SUPPLIER THEN -- Supplier
2380 IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
2381 BEGIN
2382 SELECT pvs.org_id,
2383 pvs.vendor_site_id,
2384 pvs.ece_tp_location_code,
2385 p_translator_code_in,
2386 pvs.vendor_site_code,
2387 pvs.address_line1,
2388 pvs.address_line2,
2389 pvs.address_line3,
2390 pvs.address_line4,
2391 pvs.address_lines_alt,
2392 pvs.city,
2393 pvs.county,
2394 pvs.state,
2395 pvs.zip,
2396 pvs.province,
2397 pvs.country,
2398 TO_CHAR(NULL),
2399 TO_CHAR(NULL),
2400 TO_CHAR(NULL)
2401 INTO x_org_id_out,
2402 x_address_id_out,
2403 x_tp_location_code_out,
2404 x_translator_code_out,
2405 x_tp_location_name_out,
2406 x_address_line1_out,
2407 x_address_line2_out,
2408 x_address_line3_out,
2409 x_address_line4_out,
2410 x_address_line_alt_out,
2411 x_city_out,
2412 x_county_out,
2413 x_state_out,
2414 x_zip_out,
2415 x_province_out,
2416 x_country_out,
2417 x_region_1_out,
2418 x_region_2_out,
2419 x_region_3_out
2420 FROM po_vendor_sites_all pvs
2421 WHERE pvs.vendor_site_id = p_address_id_in;
2422
2423 EXCEPTION
2424 WHEN NO_DATA_FOUND THEN
2425 -- Looks like we have an invalid ID here...
2426 x_status_code := G_INVALID_ADDR_ID;
2427 RAISE fnd_api.G_EXC_ERROR;
2428
2429 END;
2430
2431 -- Whatever happens, the API has executed successfully...
2435 IF b_use_lctc AND ((p_tp_location_code_in <> x_tp_location_code_out) OR
2432 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2433
2434 -- If we were given LC/TC and it is not the same as the derived LC/TC...
2436 (p_translator_code_in <> x_translator_code_out)) THEN
2437 x_status_code := G_CANNOT_DERIVE_ADDR;
2438 -- If were were given addreses components and they are not the same as what
2439 -- was derived then...
2440 ELSIF b_use_addr_comp THEN
2441 /* bug2151462 AND NOT(ece_compare_addresses(p_address_line1_in,
2442 p_address_line2_in,
2443 p_address_line3_in,
2444 p_address_line4_in,
2445 p_address_line_alt_in,
2446 p_city_in,
2447 p_county_in,
2448 p_state_in,
2449 p_zip_in,
2450 p_province_in,
2451 p_country_in,
2452 p_region_1_in,
2453 p_region_2_in,
2454 p_region_3_in,
2455 x_address_line1_out,
2456 x_address_line2_out,
2457 x_address_line3_out,
2458 x_address_line4_out,
2459 x_address_line_alt_out,
2460 x_city_out,
2461 x_county_out,
2462 x_state_out,
2463 x_zip_out,
2464 x_province_out,
2465 x_country_out,
2466 x_region_1_out,
2467 x_region_2_out,
2468 x_region_3_out)) THEN
2469 x_status_code := G_INCONSISTENT_ADDR_COMP;
2470 -- If we were given location names and it is not the same as the derived
2471 -- location name then...
2472 ELSIF b_use_loc_name AND (p_tp_location_name_in <> x_tp_location_name_out) THEN
2473 */
2474 x_status_code := G_INCONSISTENT_ADDR_COMP;
2475 ELSE
2476 x_status_code := G_NO_ERRORS;
2477 END IF;
2478
2479 -- Whether the address was consistent or not, we have an Address ID so time
2480 -- to die...
2481 GOTO l_end_of_program;
2482
2483 END IF; -- IF b_use_addr_id THEN -- We have the ADDRESS_ID. Great!
2484
2485 IF b_use_lctc OR b_use_addr_comp THEN -- If LC/TC are available
2486 IF v_pcode = '1' THEN -- bug3351412
2487 FOR r_addr_rec IN c2_vendor_sites(
2488 cp_transaction_type => p_transaction_type,
2489 cp_org_id_in => p_org_id_in,
2490 cp_tp_location_code_in => p_tp_location_code_in,
2491 cp_tp_translator_code => p_translator_code_in) LOOP
2492
2493 n_loop_count := n_loop_count + 1;
2494
2495 x_org_id_out := r_addr_rec.org_id;
2496 x_address_id_out := r_addr_rec.address_id;
2497 x_tp_location_code_out := r_addr_rec.tp_location_code;
2498 x_tp_location_name_out := r_addr_rec.tp_location_name;
2499 x_address_line1_out := r_addr_rec.address_line1;
2500 x_address_line2_out := r_addr_rec.address_line2;
2501 x_address_line3_out := r_addr_rec.address_line3;
2502 x_address_line4_out := r_addr_rec.address_line4;
2503 x_address_line_alt_out := r_addr_rec.address_line_alt;
2504 x_city_out := r_addr_rec.city;
2505 x_county_out := r_addr_rec.county;
2506 x_state_out := r_addr_rec.state;
2507 x_zip_out := r_addr_rec.zip;
2508 x_province_out := r_addr_rec.province;
2509 x_country_out := r_addr_rec.country;
2510 x_region_1_out := r_addr_rec.region_1;
2511 x_region_2_out := r_addr_rec.region_2;
2512 x_region_3_out := r_addr_rec.region_3;
2513 END LOOP;
2514 ELSE
2515 FOR r_addr_rec IN c1_vendor_sites(
2516 cp_transaction_type => p_transaction_type,
2517 cp_org_id_in => p_org_id_in,
2518 cp_tp_location_code_in => p_tp_location_code_in,
2519 cp_address_line1_in => p_address_line1_in,
2520 cp_address_line2_in => p_address_line2_in,
2521 cp_address_line3_in => p_address_line3_in,
2522 cp_address_line_alt_in => p_address_line_alt_in,
2526
2523 cp_city_in => p_city_in,
2524 cp_zip_in => p_zip_in) LOOP
2525 /* bug 2151462: added the above parameters to the cursor call */
2527 n_loop_count := n_loop_count + 1;
2528
2529 /*bug2151462 IF b_use_addr_comp THEN -- If Address Components are available...
2530 IF ece_compare_addresses(
2531 p_address_line1_in,
2532 p_address_line2_in,
2533 p_address_line3_in,
2534 p_address_line4_in,
2535 p_address_line_alt_in,
2536 p_city_in,
2537 p_county_in,
2538 p_state_in,
2539 p_zip_in,
2540 p_province_in,
2541 p_country_in,
2542 p_region_1_in,
2543 p_region_2_in,
2544 p_region_3_in,
2545 r_addr_rec.address_line1,
2546 r_addr_rec.address_line2,
2547 r_addr_rec.address_line3,
2548 r_addr_rec.address_line4,
2549 r_addr_rec.address_line_alt,
2550 r_addr_rec.city,
2551 r_addr_rec.county,
2552 r_addr_rec.state,
2553 r_addr_rec.zip,
2554 r_addr_rec.province,
2555 r_addr_rec.country,
2556 r_addr_rec.region_1,
2557 r_addr_rec.region_2,
2558 r_addr_rec.region_3) THEN
2559 n_match_count := n_match_count + 1;
2560
2561 x_org_id_out := r_addr_rec.org_id;
2562 x_address_id_out := r_addr_rec.address_id;
2563 x_tp_location_code_out := r_addr_rec.tp_location_code;
2564 x_tp_location_name_out := r_addr_rec.tp_location_name;
2565 x_address_line1_out := r_addr_rec.address_line1;
2566 x_address_line2_out := r_addr_rec.address_line2;
2567 x_address_line3_out := r_addr_rec.address_line3;
2568 x_address_line4_out := r_addr_rec.address_line4;
2569 x_address_line_alt_out := r_addr_rec.address_line_alt;
2570 x_city_out := r_addr_rec.city;
2571 x_county_out := r_addr_rec.county;
2572 x_state_out := r_addr_rec.state;
2573 x_zip_out := r_addr_rec.zip;
2574 x_province_out := r_addr_rec.province;
2575 x_country_out := r_addr_rec.country;
2576 x_region_1_out := r_addr_rec.region_1;
2577 x_region_2_out := r_addr_rec.region_2;
2578 x_region_3_out := r_addr_rec.region_3;
2579 END IF; -- IF ece_compare_addresses
2580 ELSE
2581 */
2582 x_org_id_out := r_addr_rec.org_id;
2583 x_address_id_out := r_addr_rec.address_id;
2584 x_tp_location_code_out := r_addr_rec.tp_location_code;
2585 x_tp_location_name_out := r_addr_rec.tp_location_name;
2586 x_address_line1_out := r_addr_rec.address_line1;
2587 x_address_line2_out := r_addr_rec.address_line2;
2588 x_address_line3_out := r_addr_rec.address_line3;
2589 x_address_line4_out := r_addr_rec.address_line4;
2590 x_address_line_alt_out := r_addr_rec.address_line_alt;
2591 x_city_out := r_addr_rec.city;
2592 x_county_out := r_addr_rec.county;
2593 x_state_out := r_addr_rec.state;
2594 x_zip_out := r_addr_rec.zip;
2595 x_province_out := r_addr_rec.province;
2596 x_country_out := r_addr_rec.country;
2597 x_region_1_out := r_addr_rec.region_1;
2598 x_region_2_out := r_addr_rec.region_2;
2599 x_region_3_out := r_addr_rec.region_3;
2600 --END IF; -- IF b_use_addr_comp THEN
2601 END LOOP;
2602 END IF;
2603
2604 IF n_loop_count = 0 THEN
2605 x_org_id_out := p_org_id_in;
2606 x_address_id_out := p_address_id_in;
2607 x_tp_location_code_out := p_tp_location_code_in;
2608 x_translator_code_out := p_translator_code_in;
2609 x_tp_location_name_out := p_tp_location_name_in;
2610 x_address_line1_out := p_address_line1_in;
2611 x_address_line2_out := p_address_line2_in;
2612 x_address_line3_out := p_address_line3_in;
2613 x_address_line4_out := p_address_line4_in;
2614 x_address_line_alt_out := p_address_line_alt_in;
2615 x_city_out := p_city_in;
2616 x_county_out := p_county_in;
2617 x_state_out := p_state_in;
2618 x_zip_out := p_zip_in;
2619 x_province_out := p_province_in;
2620 x_country_out := p_country_in;
2621 x_region_1_out := p_region_1_in;
2622 x_region_2_out := p_region_2_in;
2623 x_region_3_out := p_region_3_in;
2624
2628 ELSE
2625 IF b_use_addr_comp THEN
2626 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2627 x_status_code := G_INCONSISTENT_ADDR_COMP;
2629 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2630 x_status_code := G_CANNOT_DERIVE_ADDR;
2631 END IF; -- IF b_use_addr_comp THEN
2632
2633 GOTO l_end_of_program;
2634 ELSIF n_loop_count = 1 THEN -- The Cursor Returned only one row.
2635 /* bug2151462 IF b_use_addr_comp THEN
2636 IF n_match_count = 0 THEN
2637 -- No need to assign variables in this scenario. Correct values
2638 -- are already assigned above.
2639
2640 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2641 x_status_code := G_INCONSISTENT_ADDR_COMP;
2642
2643 GOTO l_end_of_program;
2644 ELSIF n_match_count = 1 THEN
2645 -- No need to assign variables in this scenario. Correct values
2646 -- are already assigned above.
2647 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2648 x_status_code := G_NO_ERRORS;
2649 GOTO l_end_of_program;
2650 END IF;
2651 ELSE
2652 */
2653 -- No need to assign variables in this scenario. Correct values
2654 -- are already assigned above.
2655 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2656 x_status_code := G_NO_ERRORS;
2657 GOTO l_end_of_program;
2658 --END IF;
2659 ELSE -- n_loop_count > 1
2660 IF b_use_addr_comp THEN
2661 --IF n_match_count = 0 THEN
2662 x_org_id_out := p_org_id_in;
2663 x_address_id_out := p_address_id_in;
2664 x_tp_location_code_out := p_tp_location_code_in;
2665 x_translator_code_out := p_translator_code_in;
2666 x_tp_location_name_out := p_tp_location_name_in;
2667 x_address_line1_out := p_address_line1_in;
2668 x_address_line2_out := p_address_line2_in;
2669 x_address_line3_out := p_address_line3_in;
2670 x_address_line4_out := p_address_line4_in;
2671 x_address_line_alt_out := p_address_line_alt_in;
2672 x_city_out := p_city_in;
2673 x_county_out := p_county_in;
2674 x_state_out := p_state_in;
2675 x_zip_out := p_zip_in;
2676 x_province_out := p_province_in;
2677 x_country_out := p_country_in;
2678 x_region_1_out := p_region_1_in;
2679 x_region_2_out := p_region_2_in;
2680 x_region_3_out := p_region_3_in;
2681
2682 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2683 x_status_code := G_MULTIPLE_ADDR_FOUND;
2684
2685 GOTO l_end_of_program;
2686 /* bug2151462 ELSIF n_match_count = 1 THEN
2687 -- No need to assign variables in this scenario. Correct values
2688 -- are already assigned above.
2689 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2690 x_status_code := G_NO_ERRORS;
2691 GOTO l_end_of_program;
2692 ELSE -- n_match_count > 1
2693 -- No need to assign variables in this scenario. Correct values
2694 -- are already assigned above. However, the Address ID has to be
2695 -- removed since we got multiple matches.
2696 x_address_id_out := NULL;
2697
2698 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2699 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2700
2701 GOTO l_end_of_program;
2702 END IF; -- IF n_match_count = 0 THEN
2703 */
2704 ELSE
2705 -- We have multiple hits on TC/LC pair and no way to tie-break.
2706 x_org_id_out := NULL;
2707 x_address_id_out := NULL;
2708 x_tp_location_code_out := p_tp_location_code_in;
2709 x_translator_code_out := p_translator_code_in;
2710 x_tp_location_name_out := NULL;
2711 x_address_line1_out := NULL;
2712 x_address_line2_out := NULL;
2713 x_address_line3_out := NULL;
2714 x_address_line4_out := NULL;
2715 x_address_line_alt_out := NULL;
2716 x_city_out := NULL;
2717 x_county_out := NULL;
2718 x_state_out := NULL;
2719 x_zip_out := NULL;
2720 x_province_out := NULL;
2721 x_country_out := NULL;
2722 x_region_1_out := NULL;
2723 x_region_2_out := NULL;
2724 x_region_3_out := NULL;
2725
2726 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2727 x_status_code := G_MULTIPLE_LOC_FOUND;
2728
2729 GOTO l_end_of_program;
2730 END IF; -- IF b_use_addr_comp THEN
2734 IF b_use_addr_comp THEN
2731 END IF; -- IF n_loop_count = 0 THEN
2732 END IF; -- IF b_use_lctc THEN
2733
2735 -- At this point, all we have are raw address components and nothing else.
2736 x_org_id_out := p_org_id_in;
2737 x_address_id_out := p_address_id_in;
2738 x_tp_location_code_out := p_tp_location_code_in;
2739 x_translator_code_out := p_translator_code_in;
2740 x_tp_location_name_out := p_tp_location_name_in;
2741 x_address_line1_out := p_address_line1_in;
2742 x_address_line2_out := p_address_line2_in;
2743 x_address_line3_out := p_address_line3_in;
2744 x_address_line4_out := p_address_line4_in;
2745 x_address_line_alt_out := p_address_line_alt_in;
2746 x_city_out := p_city_in;
2747 x_county_out := p_county_in;
2748 x_state_out := p_state_in;
2749 x_zip_out := p_zip_in;
2750 x_province_out := p_province_in;
2751 x_country_out := p_country_in;
2752 x_region_1_out := p_region_1_in;
2753 x_region_2_out := p_region_2_in;
2754 x_region_3_out := p_region_3_in;
2755
2756 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2757 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2758
2759 GOTO l_end_of_program;
2760 END IF; -- IF b_use_addr_comp THEN
2761
2762 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2763 x_status_code := G_CANNOT_DERIVE_ADDR_ID;
2764
2765 GOTO l_end_of_program;
2766
2767 ELSE -- Invalid Parameter
2768 x_return_status := fnd_api.G_RET_STS_ERROR;
2769 x_status_code := G_INVALID_PARAMETER;
2770 END IF;
2771
2772 <<l_end_of_program>>
2773 NULL;
2774
2775 EXCEPTION
2776 WHEN FND_API.G_EXC_ERROR THEN
2777 ROLLBACK TO ece_get_address;
2778 x_return_status := fnd_api.G_RET_STS_ERROR;
2779
2780 fnd_msg_pub.COUNT_AND_GET(
2781 p_count => x_msg_count,
2782 p_data => x_msg_data);
2783
2784 WHEN OTHERS THEN
2785 ROLLBACK TO ece_get_address;
2786 x_status_code := G_UNEXP_ERROR;
2787 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2788
2789 fnd_msg_pub.COUNT_AND_GET(
2790 p_count => x_msg_count,
2791 p_data => x_msg_data);
2792
2793 END ece_get_address;
2794
2795 /* Function: ece_compare_addresses
2796 This function takes two addresses in components as parameters and returns TRUE if
2797 all the components match letter for letter. Case differences or leading and trailing
2798 spaces are not considered for comparison purposes. (e.g. "New York" and "NEW YORK "
2799 are considered identical.) */
2800 FUNCTION ece_compare_addresses(
2801 p_address_line1_in IN VARCHAR2,
2802 p_address_line2_in IN VARCHAR2,
2803 p_address_line3_in IN VARCHAR2,
2804 p_address_line4_in IN VARCHAR2,
2805 p_address_line_alt_in IN VARCHAR2,
2806 p_city_in IN VARCHAR2,
2807 p_county_in IN VARCHAR2,
2808 p_state_in IN VARCHAR2,
2809 p_zip_in IN VARCHAR2,
2810 p_province_in IN VARCHAR2,
2811 p_country_in IN VARCHAR2,
2812 p_region_1_in IN VARCHAR2,
2813 p_region_2_in IN VARCHAR2,
2814 p_region_3_in IN VARCHAR2,
2815 p2_address_line1_in IN VARCHAR2,
2816 p2_address_line2_in IN VARCHAR2,
2817 p2_address_line3_in IN VARCHAR2,
2818 p2_address_line4_in IN VARCHAR2,
2819 p2_address_line_alt_in IN VARCHAR2,
2820 p2_city_in IN VARCHAR2,
2821 p2_county_in IN VARCHAR2,
2822 p2_state_in IN VARCHAR2,
2823 p2_zip_in IN VARCHAR2,
2824 p2_province_in IN VARCHAR2,
2825 p2_country_in IN VARCHAR2,
2826 p2_region_1_in IN VARCHAR2,
2827 p2_region_2_in IN VARCHAR2,
2828 p2_region_3_in IN VARCHAR2) RETURN BOOLEAN IS
2829
2830 b_match BOOLEAN := TRUE;
2831
2832 BEGIN
2833 /* IF scrub(p_address_line1_in) <> scrub(p2_address_line1_in) THEN
2834 b_match := FALSE;
2835 ELSIF scrub(p_address_line2_in) <> scrub(p2_address_line2_in) THEN
2836 b_match := FALSE;
2837 ELSIF scrub(p_address_line3_in) <> scrub(p2_address_line3_in) THEN
2838 b_match := FALSE;
2839 ELSIF scrub(p_address_line4_in) <> scrub(p2_address_line4_in) THEN
2840 b_match := FALSE;
2841 ELSIF scrub(p_address_line_alt_in) <> scrub(p2_address_line_alt_in) THEN
2842 b_match := FALSE;
2843 ELSIF scrub(p_city_in) <> scrub(p2_city_in) THEN
2844 b_match := FALSE;
2845 END IF;
2846
2847 ELSIF scrub(p_county_in) <> scrub(p2_county_in) THEN
2848 b_match := FALSE;
2849 ELSIF scrub(p_state_in) <> scrub(p2_state_in) THEN
2850 b_match := FALSE;
2851 ELSIF scrub(p_zip_in) <> scrub(p2_zip_in) THEN
2852 b_match := FALSE;
2853 ELSIF scrub(p_province_in) <> scrub(p2_province_in) THEN
2854 b_match := FALSE;
2858 b_match := FALSE;
2855 ELSIF scrub(p_country_in) <> scrub(p2_country_in) THEN
2856 b_match := FALSE;
2857 ELSIF scrub(p_region_1_in) <> scrub(p2_region_1_in) THEN
2859 ELSIF scrub(p_region_2_in) <> scrub(p2_region_2_in) THEN
2860 b_match := FALSE;
2861 ELSIF scrub(p_region_3_in) <> scrub(p2_region_3_in) THEN
2862 b_match := FALSE;
2863 ELSE
2864 b_match := TRUE;
2865 END IF;*/
2866
2867 RETURN b_match;
2868 END ece_compare_addresses;
2869
2870 /* FUNCTION: scrub
2871 To facilitate comparison of addresses, this function was created. It takes a VARCHAR2
2872 value as a parameter and converts it to an uppercase string while removing leading and
2873 trailing blank spaces. If the parameter is a NULL value, it will return the word
2874 "NULL VALUE". */
2875 FUNCTION scrub(
2876 p_instring VARCHAR2) RETURN VARCHAR2 IS
2877
2878 BEGIN
2879 RETURN LTRIM(RTRIM(UPPER(NVL(p_instring,'NULL VALUE'))));
2880 END scrub;
2881
2882 PROCEDURE Get_TP_Address
2883 ( p_api_version_number IN NUMBER,
2884 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2885 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
2886 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2887 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2888 p_return_status OUT NOCOPY VARCHAR2,
2889 p_msg_count OUT NOCOPY NUMBER,
2890 p_msg_data OUT NOCOPY VARCHAR2,
2891 p_translator_code IN VARCHAR2,
2892 p_location_code_ext IN VARCHAR2,
2893 p_info_type IN VARCHAR2,
2894 p_entity_id OUT NOCOPY NUMBER,
2895 p_entity_address_id OUT NOCOPY NUMBER
2896 )
2897 IS
2898 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Address';
2899 l_api_version_number CONSTANT NUMBER := 1.0;
2900 l_return_status VARCHAR2(10);
2901
2902 l_entity_id NUMBER;
2903 l_entity_address_id NUMBER;
2904
2905 cursor ra_add is
2906 select cas.cust_account_id,
2907 cas.cust_acct_site_id
2908 from hz_cust_acct_sites cas,
2909 hz_cust_accounts ca,
2910 hz_parties pt,
2911 ece_tp_details etd
2912 where
2913 etd.translator_code = p_translator_code
2914 and cas.ece_tp_location_code = p_location_code_ext
2915 and etd.tp_header_id = cas.tp_header_id
2916 and cas.cust_account_id = ca.cust_account_id
2917 and ca.party_id = pt.party_id;
2918
2919 cursor po_site is
2920 select pv.vendor_id, pvs.vendor_site_id
2921 from po_vendors pv, po_vendor_sites pvs,
2922 -- ece_tp_headers ec,
2923 ece_tp_details etd
2924 where
2925 etd.translator_code = p_translator_code
2926 -- and etd.tp_header_id = ec.tp_header_id
2927 and pvs.ece_tp_location_code = p_location_code_ext
2928 and etd.tp_header_id = pvs.tp_header_id
2929 and pvs.vendor_id = pv.vendor_id;
2930
2931 cursor ap_bank is
2932 select cbb.branch_party_id
2933 from ce_bank_branches_v cbb,
2934 ece_tp_details etd,
2935 hz_contact_points hcp
2936 where
2937 etd.translator_code = p_translator_code
2938 and hcp.edi_ece_tp_location_code = p_location_code_ext
2939 and etd.tp_header_id = hcp.edi_tp_header_id
2940 and hcp.owner_table_id = cbb.branch_party_id
2941 and hcp.owner_table_name = 'HZ_PARTIES'
2942 and hcp.contact_point_type = 'EDI';
2943
2944 BEGIN
2945
2946 -- Standard Start of API savepoint
2947
2948 SAVEPOINT Get_TP_Address_PVT;
2949
2950 -- Standard call to check for call compatibility.
2951
2952 if NOT FND_API.Compatible_API_Call
2953 (
2954 l_api_version_number,
2955 p_api_version_number,
2956 l_api_name,
2957 G_PKG_NAME
2958 )
2959 then
2960 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2961 end if;
2962
2963 -- Initialize message list if p_init_msg_list is set to TRUE.
2964
2965 if FND_API.to_Boolean( p_init_msg_list)
2966 then
2967 FND_MSG_PUB.initialize;
2968 end if;
2969
2970 -- Initialize API return status to success
2971
2972 p_return_status := FND_API.G_RET_STS_SUCCESS;
2973
2974
2975 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
2976 then
2977 for addr in ra_add
2978 loop
2979 l_entity_id := addr.cust_account_id;
2980 l_entity_address_id := addr.cust_acct_site_id;
2981 end loop;
2982
2983 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
2984 then
2985 for site in po_site
2986 loop
2987 l_entity_id := site.vendor_id;
2988 l_entity_address_id := site.vendor_site_id;
2989 end loop;
2990
2991 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
2992 then
2993 for bank in ap_bank
2994 loop
2995 l_entity_id := -1;
2996 l_entity_address_id := bank.branch_party_id;
2997 end loop;
2998 else
2999 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3000 end if;
3001
3002 if l_entity_id is NULL
3003 and l_entity_address_id is NULL
3004 then
3005 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
3006 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
3007 p_msg_data := fnd_message.get;
3008 else
3009 p_entity_id := l_entity_id;
3010 p_entity_address_id := l_entity_address_id;
3011 end if;
3012
3013
3014 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
3015 -- the API exception handler.
3019
3016
3017 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3018 then
3020 -- Unexpected error, abort processing.
3021
3022 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3023
3024 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
3025
3026 -- Error, abort processing
3027
3028 raise FND_API.G_EXC_ERROR;
3029
3030 end if;
3031
3032 -- Standard check of p_simulate and p_commit parameters
3033
3034 if FND_API.To_Boolean( p_simulate)
3035 then
3036 ROLLBACK to Get_TP_Address_PVT;
3037
3038 elsif FND_API.To_Boolean( p_commit)
3039 then
3040 commit work;
3041 end if;
3042
3043 -- Standard call to get message count and if count is 1, get message info.
3044
3045 FND_MSG_PUB.Count_And_Get
3046 (
3047 p_count => p_msg_count,
3048 p_data => p_msg_data
3049 );
3050
3051 EXCEPTION
3052
3053 WHEN FND_API.G_EXC_ERROR THEN
3054
3055 Rollback to Get_TP_Address_PVT;
3056 p_return_status := FND_API.G_RET_STS_ERROR;
3057
3058 FND_MSG_PUB.Count_And_Get
3059 (
3060 p_count => p_msg_count,
3061 p_data => p_msg_data
3062 );
3063
3064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3065
3066 Rollback to Get_TP_Address_PVT;
3067 p_return_status := FND_API.G_RET_STS_ERROR;
3068
3069 FND_MSG_PUB.Count_And_Get
3070 (
3071 p_count => p_msg_count,
3072 p_data => p_msg_data
3073 );
3074
3075 WHEN OTHERS THEN
3076
3077 Rollback to Get_TP_Address_PVT;
3078 p_return_status := FND_API.G_RET_STS_ERROR;
3079
3080 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3081 then
3082 FND_MSG_PUB.Add_Exc_Msg
3083 (
3084 G_FILE_NAME,
3085 G_PKG_NAME,
3086 l_api_name
3087 );
3088 end if;
3089
3090 FND_MSG_PUB.Count_And_Get
3091 (
3092 p_count => p_msg_count,
3093 p_data => p_msg_data
3094 );
3095
3096 end Get_TP_Address;
3097
3098
3099 -- ***********************************************
3100 -- procedure Get_TP_Address_Ref
3101 --
3102 -- Overload this procedure per request from
3103 -- the automotive team
3104 -- ***********************************************
3105 PROCEDURE Get_TP_Address_Ref
3106 ( p_api_version_number IN NUMBER,
3107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3108 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
3109 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3110 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3111 p_return_status OUT NOCOPY VARCHAR2,
3112 p_msg_count OUT NOCOPY NUMBER,
3113 p_msg_data OUT NOCOPY VARCHAR2,
3114 -- p_translator_code IN VARCHAR2,
3115 -- p_location_code_ext IN VARCHAR2,
3116 p_reference_ext1 IN VARCHAR2,
3117 p_reference_ext2 IN VARCHAR2,
3118 p_info_type IN VARCHAR2,
3119 p_entity_id OUT NOCOPY NUMBER,
3120 p_entity_address_id OUT NOCOPY NUMBER
3121 )
3122 IS
3123 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Address_Ref';
3124 l_api_version_number CONSTANT NUMBER := 1.0;
3125 l_return_status VARCHAR2(10);
3126
3127 l_entity_id NUMBER;
3128 l_entity_address_id NUMBER;
3129
3130 cursor ra_add is
3131 select cas.cust_account_id,
3132 cas.cust_acct_site_id
3133 from hz_cust_acct_sites cas,
3134 hz_cust_accounts ca,
3135 hz_parties pt,
3136 ece_tp_headers eth
3137 where
3138 eth.tp_reference_ext1 = p_reference_ext1
3139 and eth.tp_reference_ext2 = p_reference_ext2
3140 and eth.tp_header_id = cas.tp_header_id
3141 and cas.cust_account_id = ca.cust_account_id
3142 and ca.party_id = pt.party_id;
3143
3144 cursor po_site is
3145 select pv.vendor_id, pvs.vendor_site_id
3146 from po_vendors pv, po_vendor_sites pvs,
3147 ece_tp_headers eth
3148 where
3149 eth.tp_reference_ext1 = p_reference_ext1
3150 and eth.tp_reference_ext2 = p_reference_ext2
3151 and eth.tp_header_id = pvs.tp_header_id
3152 and pvs.vendor_id = pv.vendor_id;
3153
3154 cursor ap_bank is
3155 select cbb.bank_party_id address_id
3156 from ce_bank_branches_v cbb,
3157 ece_tp_headers eth,
3158 hz_contact_points hcp
3159 where
3160 eth.tp_reference_ext1 = p_reference_ext1
3161 and eth.tp_reference_ext2 = p_reference_ext2
3162 and eth.tp_header_id = hcp.edi_tp_header_id
3163 and hcp.owner_table_id = cbb.branch_party_id
3164 and hcp.owner_table_name = 'HZ_PARTIES'
3165 and hcp.contact_point_type = 'EDI';
3166
3167 BEGIN
3168
3169 -- Standard Start of API savepoint
3170
3171 SAVEPOINT Get_TP_Address_Ref_PVT;
3172
3173 -- Standard call to check for call compatibility.
3174
3175 if NOT FND_API.Compatible_API_Call
3176 (
3177 l_api_version_number,
3178 p_api_version_number,
3179 l_api_name,
3180 G_PKG_NAME
3181 )
3182 then
3183 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3184 end if;
3185
3186 -- Initialize message list if p_init_msg_list is set to TRUE.
3187
3188 if FND_API.to_Boolean( p_init_msg_list)
3189 then
3190 FND_MSG_PUB.initialize;
3191 end if;
3192
3196
3193 -- Initialize API return status to success
3194
3195 p_return_status := FND_API.G_RET_STS_SUCCESS;
3197
3198 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
3199 then
3200 for addr in ra_add
3201 loop
3202 l_entity_id := addr.cust_account_id;
3203 l_entity_address_id := addr.cust_acct_site_id;
3204 end loop;
3205
3206 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
3207 then
3208 for site in po_site
3209 loop
3210 l_entity_id := site.vendor_id;
3211 l_entity_address_id := site.vendor_site_id;
3212 end loop;
3213
3214 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
3215 then
3216 for bank in ap_bank
3217 loop
3218 l_entity_id := -1;
3219 l_entity_address_id := bank.address_id;
3220 end loop;
3221 else
3222 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3223 end if;
3224
3225 if l_entity_id is NULL
3226 and l_entity_address_id is NULL
3227 then
3228 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
3229 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
3230 p_msg_data := fnd_message.get;
3231 else
3232 p_entity_id := l_entity_id;
3233 p_entity_address_id := l_entity_address_id;
3234 end if;
3235
3236 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
3237 -- the API exception handler.
3238
3239 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3240 then
3241
3242 -- Unexpected error, abort processing.
3243
3244 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3245
3246 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
3247
3248 -- Error, abort processing
3249
3250 raise FND_API.G_EXC_ERROR;
3251
3252 end if;
3253
3254 -- Standard check of p_simulate and p_commit parameters
3255
3256 if FND_API.To_Boolean( p_simulate)
3257 then
3258 ROLLBACK to Get_TP_Address_Ref_PVT;
3259
3260 elsif FND_API.To_Boolean( p_commit)
3261 then
3262 commit work;
3263 end if;
3264
3265 -- Standard call to get message count and if count is 1, get message info.
3266
3267 FND_MSG_PUB.Count_And_Get
3268 (
3269 p_count => p_msg_count,
3270 p_data => p_msg_data
3271 );
3272
3273 EXCEPTION
3274
3275 WHEN FND_API.G_EXC_ERROR THEN
3276
3277 Rollback to Get_TP_Address_Ref_PVT;
3278 p_return_status := FND_API.G_RET_STS_ERROR;
3279
3280 FND_MSG_PUB.Count_And_Get
3281 (
3282 p_count => p_msg_count,
3283 p_data => p_msg_data
3284 );
3285
3286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3287
3288 Rollback to Get_TP_Address_Ref_PVT;
3289 p_return_status := FND_API.G_RET_STS_ERROR;
3290
3291 FND_MSG_PUB.Count_And_Get
3292 (
3293 p_count => p_msg_count,
3294 p_data => p_msg_data
3295 );
3296
3297 WHEN OTHERS THEN
3298
3299 Rollback to Get_TP_Address_Ref_PVT;
3300 p_return_status := FND_API.G_RET_STS_ERROR;
3301
3302 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3303 then
3304 FND_MSG_PUB.Add_Exc_Msg
3305 (
3306 G_FILE_NAME,
3307 G_PKG_NAME,
3308 l_api_name
3309 );
3310 end if;
3311
3312 FND_MSG_PUB.Count_And_Get
3313 (
3314 p_count => p_msg_count,
3315 p_data => p_msg_data
3316 );
3317
3318 end Get_TP_Address_Ref;
3319
3320 -- ***********************************************
3321 -- procedure Get_TP_Location_Code
3322 -- ***********************************************
3323 PROCEDURE Get_TP_Location_Code
3324 ( p_api_version_number IN NUMBER,
3325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3326 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
3327 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3328 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3329 p_return_status OUT NOCOPY VARCHAR2,
3330 p_msg_count OUT NOCOPY NUMBER,
3331 p_msg_data OUT NOCOPY VARCHAR2,
3332 p_entity_address_id IN NUMBER,
3333 p_info_type IN VARCHAR2,
3334 p_location_code_ext OUT NOCOPY VARCHAR2,
3335 p_reference_ext1 OUT NOCOPY VARCHAR2,
3336 p_reference_ext2 OUT NOCOPY VARCHAR2
3337 )
3338 IS
3339 l_api_name CONSTANT VARCHAR2(30) := 'Get_TP_Location_Code';
3340 l_api_version_number CONSTANT NUMBER := 1.0;
3341 l_return_status VARCHAR2(10);
3342
3343 l_location_code_ext VARCHAR2(50);
3344
3345 cursor ra_add is
3346 select
3347 cas.ece_tp_location_code,
3348 ec.tp_reference_ext1,
3349 ec.tp_reference_ext2
3350 from hz_cust_acct_sites cas,
3351 ece_tp_headers ec
3352 where
3353
3354 ec.tp_header_id = cas.tp_header_id
3355 and cas.cust_acct_site_id = p_entity_address_id;
3356
3357
3358 cursor po_site is
3359 select pvs.ece_tp_location_code,
3360 ec.tp_reference_ext1,
3361 ec.tp_reference_ext2
3362 from ece_tp_headers ec, po_vendor_sites pvs
3363 where
3364 pvs.vendor_site_id = p_entity_address_id
3365 and pvs.tp_header_id = ec.tp_header_id;
3366
3367 cursor ap_bank is
3368 select hcp.edi_ece_tp_location_code,
3369 ec.tp_reference_ext1,
3370 ec.tp_reference_ext2
3371 from ece_tp_headers ec, ce_bank_branches_v cbb,
3372 hz_contact_points hcp
3373 where
3374 cbb.branch_party_id = p_entity_address_id
3375 and hcp.edi_tp_header_id = ec.tp_header_id
3376 and hcp.owner_table_id = cbb.branch_party_id
3377 and hcp.owner_table_name = 'HZ_PARTIES'
3378 and hcp.contact_point_type = 'EDI';
3379
3380 BEGIN
3381
3382 -- Standard Start of API savepoint
3383
3384 SAVEPOINT Get_TP_Location_Code_PVT;
3385
3386 -- Standard call to check for call compatibility.
3387
3388 if NOT FND_API.Compatible_API_Call
3389 (
3390 l_api_version_number,
3391 p_api_version_number,
3392 l_api_name,
3393 G_PKG_NAME
3394 )
3395 then
3396 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3397 end if;
3398
3399 -- Initialize message list if p_init_msg_list is set to TRUE.
3400
3401 if FND_API.to_Boolean( p_init_msg_list)
3402 then
3403 FND_MSG_PUB.initialize;
3404 end if;
3405
3406 -- Initialize API return status to success
3407
3408 p_return_status := FND_API.G_RET_STS_SUCCESS;
3409
3410
3411 if ( p_info_type = EC_Trading_Partner_PVT.G_CUSTOMER)
3412 then
3413 for addr in ra_add loop
3414 l_location_code_ext := addr.ece_tp_location_code;
3415 p_reference_ext1 := addr.tp_reference_ext1;
3416 p_reference_ext2 := addr.tp_reference_ext2;
3417 end loop;
3418
3419
3420 elsif (p_info_type = EC_Trading_Partner_PVT.G_SUPPLIER)
3421 then
3422 for site in po_site loop
3423 l_location_code_ext := site.ece_tp_location_code;
3424 p_reference_ext1 := site.tp_reference_ext1;
3425 p_reference_ext2 := site.tp_reference_ext2;
3426 end loop;
3427
3428 elsif (p_info_type = EC_Trading_Partner_PVT.G_BANK)
3429 then
3430 for bank in ap_bank loop
3431 l_location_code_ext := bank.edi_ece_tp_location_code;
3432 p_reference_ext1 := bank.tp_reference_ext1;
3433 p_reference_ext2 := bank.tp_reference_ext2;
3434 end loop;
3435 else
3436 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3437 end if;
3438
3439 if l_location_code_ext is NULL
3440 then
3441 p_return_status := EC_Trading_Partner_PVT.G_TP_NOT_FOUND;
3442 fnd_message.set_name('EC','ECE_TP_NOT_FOUND');
3443 p_msg_data := fnd_message.get;
3444 else
3445 p_location_code_ext := l_location_code_ext;
3446 end if;
3447
3448 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
3449 -- the API exception handler.
3450
3451 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3452 then
3453
3454 -- Unexpected error, abort processing.
3455
3456 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3457
3458 elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
3459
3460 -- Error, abort processing
3461
3462 raise FND_API.G_EXC_ERROR;
3463
3464 end if;
3465
3466 -- Standard check of p_simulate and p_commit parameters
3467
3468 if FND_API.To_Boolean( p_simulate)
3469 then
3470 ROLLBACK to Get_TP_Location_Code_PVT;
3471
3472 elsif FND_API.To_Boolean( p_commit)
3473 then
3474 commit work;
3475 end if;
3476
3477 -- Standard call to get message count and if count is 1, get message info.
3478
3479 FND_MSG_PUB.Count_And_Get
3480 (
3481 p_count => p_msg_count,
3482 p_data => p_msg_data
3483 );
3484
3485 EXCEPTION
3486
3487 WHEN FND_API.G_EXC_ERROR THEN
3488
3489 Rollback to Get_TP_Location_Code_PVT;
3490 p_return_status := FND_API.G_RET_STS_ERROR;
3491
3492 FND_MSG_PUB.Count_And_Get
3493 (
3494 p_count => p_msg_count,
3495 p_data => p_msg_data
3496 );
3497
3498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3499
3500 Rollback to Get_TP_Location_Code_PVT;
3501 p_return_status := FND_API.G_RET_STS_ERROR;
3502
3503 FND_MSG_PUB.Count_And_Get
3504 (
3505 p_count => p_msg_count,
3506 p_data => p_msg_data
3507 );
3508
3509 WHEN OTHERS THEN
3510
3511 Rollback to Get_TP_Location_Code_PVT;
3512 p_return_status := FND_API.G_RET_STS_ERROR;
3513
3514 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3515 then
3516 FND_MSG_PUB.Add_Exc_Msg
3517 (
3518 G_FILE_NAME,
3519 G_PKG_NAME,
3520 l_api_name
3521 );
3522 end if;
3523
3524 FND_MSG_PUB.Count_And_Get
3525 (
3526 p_count => p_msg_count,
3527 p_data => p_msg_data
3528 );
3529
3530 end Get_TP_Location_Code;
3531
3532 END ece_trading_partners_pub;
3533