1 PACKAGE BODY AP_WEB_LOCATIONS_PKG as
2 /* $Header: apwelocb.pls 120.2 2006/04/06 02:22:01 krmenon noship $ */
3
4 TYPE t_string_array IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
5 g_country_cache t_string_array;
6
7 ------------------------------------------------------------
8 -- Package private procedures
9 ------------------------------------------------------------
10 procedure get_location(p_location in out nocopy location_rec,
11 x_return_status in OUT nocopy varchar2,
12 x_msg_count in out NOCOPY number,
13 x_msg_data in out nocopy varchar2);
14 procedure get_location(p_loc_array in out nocopy loc_array,
15 x_return_status in out nocopy varchar2,
16 x_msg_count in out nocopy number,
17 x_msg_data in out nocopy varchar2);
18 procedure default_org_id(p_location in out nocopy location_rec,
19 p_card_program_id in number);
20 procedure set_address_key(p_loc_rec in out nocopy location_rec);
21 procedure get_geometry(p_loc_array in out nocopy loc_array);
22 procedure get_geometry(p_location in out nocopy location_rec);
23 function default_country_pvt(p_card_program_id in number) return varchar2;
24
25 --
26 -- get_location procedure checks to see if an identical address exists
27 -- in the AP_EXP_LOCATIONS table. If it exists, it does nothing.
28 -- If it doesn't exist, it will populate AP_EXP_LOCATIONS. It will
29 -- also call out to eLocations to populate the spatial coordinates
30 -- NOTE: AP_EXP_LOCATIONS will only be populated if
31 -- MERCHANT_COUNTRY_CODE is non-null.
32
33 ------------------------------------------------------------
34 -- get_location using location_rec
35 ------------------------------------------------------------
36 procedure get_location(p_location in out nocopy location_rec,
37 x_return_status in out nocopy varchar2,
38 x_msg_count in out nocopy number,
39 x_msg_data in out nocopy varchar2)
40 is
41 l_loc_array loc_array := loc_array(p_location);
42 begin
43 get_location(l_loc_array, x_return_status, x_msg_count, x_msg_data);
44
45 p_location.location_id := l_loc_array(1).location_id;
46 p_location.geometry_status_code := l_loc_array(1).geometry_status_code;
47 p_location.geometry := l_loc_array(1).geometry;
48 end;
49
50
51 ------------------------------------------------------------
52 -- get_location using loc_array
53 ------------------------------------------------------------
54 procedure get_location(p_loc_array in out nocopy loc_array,
55 x_return_status in out nocopy varchar2,
56 x_msg_count in out nocopy number,
57 x_msg_data in out nocopy varchar2)
58 is
59 l_count number;
60
61 l_ins_count number := 0;
62 l_ins_array loc_array := loc_array();
63
64 user_id number(15);
65 login_id number(15);
66 todays_date date;
67 begin
68 l_count := p_loc_array.count;
69 if l_count = 0 then
70 return;
71 end if;
72
73 user_id := fnd_global.user_id;
74 login_id := fnd_global.login_id;
75 todays_date := sysdate;
76
77 for i in 1..l_count loop
78 p_loc_array(i).address1 := upper(p_loc_array(i).address1);
79 p_loc_array(i).address2 := upper(p_loc_array(i).address2);
80 p_loc_array(i).address3 := upper(p_loc_array(i).address3);
81 p_loc_array(i).address4 := upper(p_loc_array(i).address4);
82 p_loc_array(i).city := upper(p_loc_array(i).city);
83 p_loc_array(i).province_state := upper(p_loc_array(i).province_state);
84 p_loc_array(i).postal_code := upper(p_loc_array(i).postal_code);
85 p_loc_array(i).country := upper(p_loc_array(i).country);
86
87 if p_loc_array(i).country = 'UNITED STATES' then
88 p_loc_array(i).country := 'US';
89 end if;
90
91 set_address_key(p_loc_array(i));
92
93 begin
94 select location_id, geometry, geometry_status_code
95 into p_loc_array(i).location_id, p_loc_array(i).geometry, p_loc_array(i).geometry_status_code
96 from ap_exp_locations
97 where address_key = p_loc_array(i).address_key;
98
99 exception
100 when no_data_found then
101 select ap_exp_locations_s.nextval
102 into p_loc_array(i).location_id
103 from dual;
104
105 get_geometry(p_loc_array(i));
106
107 insert into ap_exp_locations
108 ( location_id,
109 address_key,
110 address1,
111 address2,
112 address3,
113 address4,
114 city,
115 province_state,
116 postal_code,
117 country,
118 geometry,
119 geometry_status_code,
120 created_by,
121 creation_date,
122 last_updated_by,
123 last_update_date,
124 last_update_login,
125 org_id )
126 values
127 ( p_loc_array(i).location_id,
128 p_loc_array(i).address_key,
129 p_loc_array(i).address1,
130 p_loc_array(i).address2,
131 p_loc_array(i).address3,
132 p_loc_array(i).address4,
133 p_loc_array(i).city,
134 p_loc_array(i).province_state,
135 p_loc_array(i).postal_code,
136 p_loc_array(i).country,
137 p_loc_array(i).geometry,
138 p_loc_array(i).geometry_status_code,
139 user_id,
140 todays_date,
141 user_id,
142 todays_date,
143 login_id,
144 mo_global.get_current_org_id() );
145 end;
146 end loop;
147
148 end get_location;
149
150
151 ------------------------------------------------------------
152 -- get_location using ap_credit_card_trxns_all
153 ------------------------------------------------------------
154 procedure get_location(p_cc_trx in out nocopy ap_credit_card_trxns_all%rowtype,
155 x_return_status in out nocopy varchar2,
156 x_msg_count in out nocopy number,
157 x_msg_data in out nocopy varchar2)
158 is
159 l_cc_trx_arr cc_trx_array := cc_trx_array(p_cc_trx);
160 begin
161 get_location(l_cc_trx_arr, x_return_status, x_msg_count, x_msg_data);
162 p_cc_trx.location_id := l_cc_trx_arr(1).location_id;
163 end get_location;
164
165 ------------------------------------------------------------
166 -- get_location using cc_trx_array
167 ------------------------------------------------------------
168 procedure get_location(p_cc_trx in out nocopy cc_trx_array,
169 x_return_status in out nocopy varchar2,
170 x_msg_count in out nocopy number,
171 x_msg_data in out nocopy varchar2)
172 is
173 l_loc_array loc_array := loc_array();
174 l_count number;
175 begin
176 l_count := p_cc_trx.COUNT;
177 for i in 1..l_count loop
178 if p_cc_trx(i).merchant_country_code is not null then
179 l_loc_array.extend;
180
181 l_loc_array(i).address1 := p_cc_trx(i).merchant_address1;
182 l_loc_array(i).address2 := p_cc_trx(i).merchant_address2;
183 l_loc_array(i).address3 := p_cc_trx(i).merchant_address3;
184 l_loc_array(i).address4 := p_cc_trx(i).merchant_address4;
185 l_loc_array(i).city := p_cc_trx(i).merchant_city;
186 l_loc_array(i).province_state := p_cc_trx(i).merchant_province_state;
187 l_loc_array(i).postal_code := p_cc_trx(i).merchant_postal_code;
188 l_loc_array(i).country := p_cc_trx(i).merchant_country_code;
189 l_loc_array(i).org_id := p_cc_trx(i).org_id;
190 end if;
191 end loop;
192
193 get_location(l_loc_array, x_return_status, x_msg_count, x_msg_data);
194
195 for i in 1..l_count loop
196 p_cc_trx(i).location_id := l_loc_array(i).location_id;
197 end loop;
198 end get_location;
199
200
201 ------------------------------------------------------------
202 --
203 -- Uses various information about the card program to default a country.
204 -- This includes....
205 -- o Supplier Site
206 -- o Currency territories
207 -- o Organization
208 ------------------------------------------------------------
209 function default_country(p_card_program_id in number)
210 return varchar2 is
211 begin
212 return g_country_cache(p_card_program_id);
213 exception
214 when no_data_found then
215 g_country_cache(p_card_program_id) := default_country_pvt(p_card_program_id);
216 return g_country_cache(p_card_program_id);
217 end default_country;
218
219 function default_country_pvt(p_card_program_id in number)
220 return varchar2 is
221 l_country varchar2(80);
222 begin
223 if p_card_program_id is null then
224 --
225 -- Default country from supplier site
226 --
227 begin
228 select site.country
229 into l_country
230 from ap_card_programs_all card,
231 ap_supplier_sites_all site
232 where card.vendor_site_id = site.vendor_site_id
233 and card.card_program_id = p_card_program_id;
234
235 return l_country;
236 exception
237 when no_data_found then
238 l_country := null;
239 end;
240
241 --
242 -- Default country from currency
243 --
244 begin
245 select curr.issuing_territory_code
246 into l_country
247 from ap_card_programs_all card,
248 fnd_currencies curr
249 where card.card_program_id = p_card_program_id
250 and card.card_program_currency_code = curr.currency_code;
251
252 return l_country;
253 exception
254 when no_data_found then
255 l_country := null;
256 end;
257
258 end if;
259
260 --
261 -- Default country from ORG_ID
262 --
263 begin
264 select loc.country
265 into l_country
266 from hr_all_organization_units org,
267 hr_locations loc,
268 ap_card_programs_all p
269 where org.location_id = loc.location_id
270 and org.organization_id = p.org_id
271 and p.card_program_id = p_card_program_id;
272
273 return l_country;
274 exception
275 when no_data_found then
276 l_country := null;
277 end;
278
279 return null;
280 end default_country_pvt;
281
282
283 ------------------------------------ PRIVATE -----------------------------------
284
285 ------------------------------------------------------------
286 -- Default ORG_ID based on the card program
287 ------------------------------------------------------------
288 procedure default_org_id(p_location in out nocopy location_rec,
289 p_card_program_id in number)
290 is
291 begin
292 if p_location.org_id is not null then
293 return;
294 end if;
295
296 if p_card_program_id is not null then
297 select org_id into p_location.org_id
298 from ap_card_programs_all
299 where card_program_id = p_card_program_id;
300 end if;
301 end default_org_id;
302
303 ------------------------------------------------------------
304 -- Set the address key for the location
305 ------------------------------------------------------------
306 procedure set_address_key(p_loc_rec in out nocopy location_rec)
307 is
308 begin
309 p_loc_rec.address_key :=
310 to_char(p_loc_rec.org_id)|| fnd_global.newline ||
311 upper(p_loc_rec.country) ||fnd_global.newline||
312 upper(p_loc_rec.postal_code) ||fnd_global.newline||
313 upper(p_loc_rec.province_state) ||fnd_global.newline||
314 upper(p_loc_rec.city) ||fnd_global.newline||
315 upper(p_loc_rec.address1) ||fnd_global.newline||
316 upper(p_loc_rec.address2) ||fnd_global.newline||
317 upper(p_loc_rec.address3) ||fnd_global.newline||
318 upper(p_loc_rec.address4);
319
320 end set_address_key;
321
322 ------------------------------------------------------------
323 -- Call out to HZ APIs to integrate with eLocation
324 ------------------------------------------------------------
325 procedure get_geometry(p_loc_array in out nocopy loc_array)
326 is
327 /*
328 l_array hz_geocode_pkg.loc_array := hz_geocode_pkg.loc_array();
329
330 l_http_ad varchar2(2000);
331 l_bypass_domains varchar2(2000);
332 l_proxy varchar2(100);
333 l_proxy_port varchar2(10);
334
335 l_count number;
336
337 l_return_status VARCHAR2(10);
338 l_msg_count number;
339 l_msg_data varchar2(2000);
340 */
341 begin
342 NULL;
343 /*
344 This procedure has been completely commented out because
345 HZ_GEOCODE_PKG causes a dependency on HZ.H which is higher than
346 the required 11.5.4 base. This should be uncommented in 11i.X or later
347 whenever eLocation integration needs to be reintroduced.
348 eLocation integration is currently not documented nor supported in 11.5.10.
349
350 fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
351 fnd_profile.get('WEB_PROXY_BYPASS_DOMAINS', l_bypass_domains);
352
353 IF hz_geocode_pkg.in_bypass_list(
354 l_http_ad,
355 l_bypass_domains
356 ) THEN
357 -- site is in the bypass list.
358 l_proxy := NULL;
359 l_proxy_port := NULL;
360 ELSE
361 -- site is not in the bypass list.
362 -- First, attempt to get proxy value from FND. If the proxy name is not
363 -- found, try the TCA values regardless of whether the port is found.
364 fnd_profile.get('WEB_PROXY_HOST', l_proxy);
365 fnd_profile.get('WEB_PROXY_PORT', l_proxy_port);
366
367 IF l_proxy IS NULL THEN
368 fnd_profile.get('HZ_WEBPROXY_NAME', l_proxy);
369 fnd_profile.get('HZ_WEBPROXY_PORT', l_proxy_port);
370 END IF;
371 END IF;
372
373 l_count := p_loc_array.count;
374 l_array.extend(l_count);
375 for i in 1..l_count loop
376 l_array(i).location_id := p_loc_array(i).location_id;
377 l_array(i).address1 := p_loc_array(i).address1;
378 l_array(i).address2 := p_loc_array(i).address2;
379 l_array(i).address2 := p_loc_array(i).address2;
380 l_array(i).address2 := p_loc_array(i).address2;
381 l_array(i).city := p_loc_array(i).city;
382 l_array(i).state := p_loc_array(i).province_state;
383 l_array(i).province := p_loc_array(i).province_state;
384 l_array(i).postal_code := p_loc_array(i).postal_code;
385 l_array(i).country := p_loc_array(i).country;
386 end loop;
387
388 hz_geocode_pkg.get_spatial_coords(
389 p_loc_array => l_array,
390 p_name => NULL,
391 p_http_ad => l_http_ad,
392 p_proxy => l_proxy,
393 p_port => l_proxy_port,
394 p_retry => 3,
395 x_return_status => l_return_status,
396 x_msg_count => l_msg_count,
397 x_msg_data => l_msg_data
398 );
399
400 if l_return_status = fnd_api.g_ret_sts_unexp_error then
401 for i in 1..l_msg_count loop
402 fnd_log.string(fnd_log.level_unexpected,
403 'OIE_LOCATIONS_PKG.get_geometry',
404 fnd_msg_pub.get(i, 'F'));
405 end loop;
406 elsif l_return_status = fnd_api.g_ret_sts_error then
407 for i in 1..l_msg_count loop
408 fnd_log.string(fnd_log.level_error,
409 'OIE_LOCATIONS_PKG.get_geometry',
410 fnd_msg_pub.get(i, 'F'));
411 end loop;
412 end if;
413
414 for i in 1..l_count loop
415 p_loc_array(i).geometry_status_code := l_array(i).geometry_status_code;
416 p_loc_array(i).geometry := l_array(i).geometry;
417 end loop;
418 */
419 end get_geometry;
420
421 procedure get_geometry(p_location in out nocopy location_rec)
422 is
423 l_loc_array loc_array :=
424 loc_array(p_location);
425 begin
426 get_geometry(l_loc_array);
427 p_location.geometry_status_code := l_loc_array(1).geometry_status_code;
428 p_location.geometry := l_loc_array(1).geometry;
429 end;
430
431
432
433 end;
[Home] [Help]