[Home] [Help]
PACKAGE BODY: APPS.PV_LOCATOR
Source
1 PACKAGE BODY PV_LOCATOR AS
2 /* $Header: pvxvlcrb.pls 120.5 2006/06/06 20:53:13 dhii ship $ */
3
4
5
6 FUNCTION ADDRESS_TO_GEOCODEXML ( name VARCHAR2,
7 street VARCHAR2,
8 city VARCHAR2,
9 state VARCHAR2,
10 zip_code VARCHAR2)
11 RETURN VARCHAR2;
12 PROCEDURE Debug(
13 p_msg_string IN VARCHAR2
14 ) ;
15 PROCEDURE Set_Error_Message(
16 p_msg_level IN NUMBER,
17 p_msg_name IN VARCHAR2,
18 p_token1 IN VARCHAR2,
19 p_token1_value IN VARCHAR2,
20 p_token2 IN VARCHAR2 := NULL ,
21 p_token2_value IN VARCHAR2 := NULL,
22 p_token3 IN VARCHAR2 := NULL,
23 p_token3_value IN VARCHAR2 := NULL
24 );
25 ---------------------------------------------------------------------
26 -- PROCEDURE
27 -- get locator partners
28 --
29 -- PURPOSE
30 -- Based on the sql query, this API queries and get partners information in to
31 -- adddress record and find all partners with in the distance range form the
32 -- customer adress limited by the max number of partner returned .
33 -- This API is used from the wrapper API for locator and opportunity matching
34 --
35 -- PARAMETERS
36 -- p_party_address_rec: the record to hold customer address.
37 -- p_partner_tbl: returns the list of partners sorted based on the distance
38 --
39 -- NOTES
40 -- 1. object_version_number will be set to 1.
41 ---------------------------------------------------------------------
42 PROCEDURE Get_Locator_Partners(
43 p_api_version IN NUMBER
44 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
45 ,p_commit IN VARCHAR2 := FND_API.g_false
46 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
47 ,p_customer_address1 IN HZ_LOCATIONS.ADDRESS1%TYPE
48 ,p_customer_address2 IN HZ_LOCATIONS.ADDRESS2%TYPE
49 ,p_customer_address3 IN HZ_LOCATIONS.ADDRESS3%TYPE
50 ,p_customer_city IN HZ_LOCATIONS.CITY%TYPE
51 ,p_customer_state IN HZ_LOCATIONS.STATE%TYPE
52 ,p_customer_country IN HZ_LOCATIONS.COUNTRY%TYPE
53 ,p_customer_postalcode IN HZ_LOCATIONS.POSTAL_CODE%TYPE
54 ,p_customer_lattitude IN VARCHAR2
55 ,p_customer_longitude IN VARCHAR2
56 ,p_max_no_partners IN NUMBER
57 ,p_distance IN NUMBER
58 ,p_distance_unit IN VARCHAR2
59 ,p_sql_query IN VARCHAR2
60 ,p_attr_id_tbl IN OUT NOCOPY JTF_NUMBER_TABLE
61 ,p_attr_value_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_4000
62 ,p_attr_operator_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100
63 ,p_attr_data_type_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100
64 ,x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE
65 ,x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE
66 ,x_return_status OUT NOCOPY VARCHAR2
67 ,x_msg_count OUT NOCOPY NUMBER
68 ,x_msg_data OUT NOCOPY VARCHAR2
69 )
70
71 IS
72
73 l_api_version CONSTANT NUMBER := 1.0;
74 l_api_name CONSTANT VARCHAR2(30) := 'Get_Locator_Partners';
75 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
76 l_msg_data VARCHAR2(10000):='';
77 l_msg_count NUMBER:=0;
78 type l_partner_rec_type IS RECORD
79 (
80 DISTANCE NUMBER,
81 PARTY_RELATION_ID NUMBER,
82 PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE,
83 ADDRESS_LINE1 HZ_LOCATIONS.ADDRESS1%TYPE,
84 ADDRESS_LINE2 HZ_LOCATIONS.ADDRESS2%TYPE,
85 ADDRESS_LINE3 HZ_LOCATIONS.ADDRESS3%TYPE,
86 CITY HZ_LOCATIONS.CITY%TYPE,
87 STATE HZ_LOCATIONS.STATE%TYPE,
88 COUNTRY HZ_LOCATIONS.COUNTRY%TYPE,
89 POSTAL_CODE HZ_LOCATIONS.POSTAL_CODE%TYPE,
90 PARTNER_URL HZ_PARTIES.URL%TYPE,
91 PHONE_COUNTRY_CODE HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE,
92 PHONE_AREA_CODE HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE,
93 PHONE_NUMBER HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE,
94 row_number NUMBER
95 );
96
97 l_partner_rec l_partner_rec_type;
98 type cur_type IS REF CURSOR;
99 --l_parties_cursor cur_type;
100 l_partner_tbl party_address_rec_tbl;
101 l_counter NUMBER :=0;
102 l_return_partner_tbl party_address_rec_tbl;
103 l_customer_rec party_address_rec_type;
104 l_customer_geocode_object HZ_LOCATIONS.GEOMETRY%TYPE;
105 l_query VARCHAR2(4000);
106 l_string VARCHAR2(4000);
107 l_skip_server VARCHAR2(4000);
108 x_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
109
110
111 cursor l_parties_cursor(l_geo HZ_LOCATIONS.GEOMETRY%TYPE
112 ,l_dist_unit VARCHAR2
113 ,x_matched_id JTF_NUMBER_TABLE
114 ,p_distance NUMBER
115 ,p_customer_city VARCHAR2) is
116 select * from
117 (
118 select /*+ cardinality( tmp 10 ) */ pv_locator.geocode_distance (l_geo, hzl.geometry, l_dist_unit) dis,
119 tmp.party_id, org.party_name, hzl.address1, hzl.address2, hzl.address3,
120 hzl.city, hzl.state, hzl.country, hzl.postal_code,
121 org.url, cp.phone_country_code, cp.phone_area_code, cp.phone_number, rownum rn
122 from
123 (
124 select p.party_id from
125 (SELECT column_value party_id FROM TABLE (CAST( x_matched_id AS JTF_NUMBER_TABLE)) ) p
126 ) tmp,
127 hz_parties org,
128 hz_party_sites hzs,
129 hz_locations hzl,
130 hz_contact_points cp,
131 pv_partner_profiles pvpp
132 where tmp.party_id = pvpp.partner_id
133 and org.party_id = pvpp.partner_party_id
134 and org.party_type = 'ORGANIZATION'
135 and org.party_id = cp.owner_table_id (+)
136 and cp.owner_table_name (+) = 'HZ_PARTIES'
137 and cp.contact_point_type (+) = 'PHONE'
138 and cp.primary_flag (+) = 'Y'
139 and org.party_id = hzs.party_id
140 and hzs.location_id = hzl.location_id
141 and hzs.identifying_address_flag = 'Y'
142 -- and UPPER(hzl.CITY) = UPPER(p_customer_city)
143 and hzl.geometry is not null
144 )
145 where dis <= p_distance order by 1 asc;
146
147
148 BEGIN
149
150
151 -- ------------------------------------------------------------------------
152 -- Retrieve profile value for stack trace profile option.
153 -- ------------------------------------------------------------------------
154
155 -- Standard call to check for call compatibility.
156 IF NOT FND_API.Compatible_API_Call ( l_api_version,
157 p_api_version,
158 l_api_name,
159 G_PKG_NAME)
160 THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 -- Initialize message list if p_init_msg_list is set to TRUE.
165 IF FND_API.to_Boolean( p_init_msg_list )
166 THEN
167 FND_MSG_PUB.initialize;
168 END IF;
169
170 -- Debug Message
171 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
172
173 Debug(
174 p_msg_string => 'In ' || l_api_name
175 );
176 END IF;
177
178 -- Storing customer information in customer address record
179 l_customer_rec.ADDRESS_LINE1 := p_customer_address1;
180 l_customer_rec.ADDRESS_LINE2 := p_customer_address2;
181 l_customer_rec.ADDRESS_LINE3 := p_customer_address3;
182 l_customer_rec.CITY := p_customer_city;
183 l_customer_rec.STATE := p_customer_state;
184 l_customer_rec.COUNTRY := p_customer_country;
185 l_customer_rec.POSTAL_CODE := p_customer_postalcode;
186
187 l_msg_data:=l_msg_data || 'Lattitude ' || p_customer_lattitude || 'longitude '
188 || p_customer_longitude || p_customer_state ;
189
190 if(p_customer_lattitude is null or p_customer_longitude is null) then
191 -- creating geocode object for customer
192 l_customer_geocode_object := address_to_geometry(null,
193 lower(l_customer_rec.ADDRESS_LINE1 || ' ' ||l_customer_rec.ADDRESS_LINE2 || ' ' ||l_customer_rec.ADDRESS_LINE3),
194 l_customer_rec.CITY, lower(l_customer_rec.STATE), l_customer_rec.POSTAL_CODE);
195 else
196 l_customer_geocode_object := MDSYS.SDO_GEOMETRY(g_geometry_param1,
197 g_geometry_param2,
198 MDSYS.SDO_POINT_TYPE(p_customer_longitude,
199 p_customer_lattitude,
200 NULL),
201 NULL,
202 NULL);
203 end if;
204
205 if(l_customer_geocode_object IS NULL) then
206
207 --raise_application_error(-20502, 'In ' || l_api_name || ' customer address geocode object is null');
208
209 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
210 p_msg_name => 'PV_LOCATOR_CUST_ADDR_INVALID',
211 p_token1 => null,
212 p_token1_value => null,
213 p_token2 => null,
214 p_token2_value => null);
215
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 end if;
218 -- Initialize API return status to SUCCESS
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220 --x_partner_tbl := p_partner_tbl;
221
222
223 -- Call to form_where_clause to get partners by attributes
224 -- filters.
225
226 PV_MATCH_V2_PUB.Form_Where_Clause(
227 p_api_version_number => p_api_version
228 ,p_init_msg_list => p_init_msg_list
229 ,p_commit => p_commit
230 ,p_validation_level => p_validation_level
231 ,p_attr_id_tbl => p_attr_id_tbl
232 ,p_attr_value_tbl => p_attr_value_tbl
233 ,p_attr_operator_tbl => p_attr_operator_tbl
234 ,p_attr_data_type_tbl => p_attr_data_type_tbl
235 ,p_attr_selection_mode => 'AND'
236 ,p_att_delmter => '+++'
237 ,p_selection_criteria => 'ALL'
238 ,p_resource_id => NULL
239 ,p_lead_id => NULL
240 ,p_auto_match_flag => 'N'
241 ,x_matched_id => x_matched_id
242 ,x_return_status => x_return_status
243 ,x_msg_count => x_msg_count
244 ,x_msg_data => x_msg_data
245 );
246
247
248 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
249 Debug( p_msg_string => 'Success String from FormWhere ' || x_return_status);
250 Debug( p_msg_string => 'msg_data String from FormWhere ' || x_msg_data);
251 END IF;
252
253
254 -- FOR i in 1..ceil((length(p_sql_query)/100)) LOOP
255 -- IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
256 -- debug(substr(p_sql_query, (i-1)*100+1, 100));
257 -- END IF;
258 -- l_msg_data:=l_msg_data || substr(p_sql_query, (i-1)*100+1, 100);
259 -- END LOOP;
260
261 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
262 debug('# Partners from form_where_clause:::' || x_matched_id.COUNT );
263 END IF;
264
265 l_msg_data:=l_msg_data || '# Partners from form_where_clause:::' || x_matched_id.COUNT ;
266
267 IF ( x_matched_id.COUNT <> 0 ) THEN
268
269 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
270 Debug(p_msg_string => 'Before opening cursor...');
271 END IF;
272
273 OPEN l_parties_cursor(l_customer_geocode_object
274 ,p_distance_unit
275 ,x_matched_id
276 ,p_distance
277 ,p_customer_city);
278
279 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
280 --Debug(p_msg_string => 'Values sent to cursor...'||l_customer_geocode_object );
281 Debug(p_msg_string => 'Values sent to cursor...'||p_distance_unit );
282 FOR ccc in 1..x_matched_id.COUNT LOOP
283 Debug(p_msg_string => 'Values sent to cursor...'||x_matched_id(ccc) );
284 END LOOP;
285 Debug(p_msg_string => 'Values sent to cursor...'||p_distance );
286 Debug(p_msg_string => 'Values sent to cursor...'||p_customer_city );
287
288 END IF;
289
290
291 LOOP
292 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
293 Debug(p_msg_string => 'Inside Loop...');
294 END IF;
295
296 FETCH l_parties_cursor into l_partner_rec;
297 EXIT WHEN l_parties_cursor%NOTFOUND;
298 l_counter :=l_counter+1;
299
300 l_partner_tbl(l_counter).PARTY_RELATION_ID := l_partner_rec.party_relation_id;
301 l_partner_tbl(l_counter).PARTY_NAME := l_partner_rec.party_name;
302 l_partner_tbl(l_counter).ADDRESS_LINE1 := l_partner_rec.address_line1;
303 l_partner_tbl(l_counter).ADDRESS_LINE2 := l_partner_rec.address_line2;
304 l_partner_tbl(l_counter).ADDRESS_LINE3 := l_partner_rec.address_line3;
305 l_partner_tbl(l_counter).CITY := l_partner_rec.city;
306 l_partner_tbl(l_counter).STATE := l_partner_rec.state;
307 l_partner_tbl(l_counter).COUNTRY := l_partner_rec.country;
308 l_partner_tbl(l_counter).POSTAL_CODE := l_partner_rec.postal_code;
309 l_partner_tbl(l_counter).distance := l_partner_rec.distance;
310
311 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
312 Debug(p_msg_string => ':::Distance values:::'||l_partner_rec.DISTANCE);
313 END IF;
314
315 END LOOP;
316
317 CLOSE l_parties_cursor;
318
319 END IF; -- END IF FOR x_matched_id_COUNT being non zero
320
321 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
322 Debug(
323 p_msg_string => 'Total No. Of Partners before sending to e_location: ' || l_partner_tbl.count
324 );
325 END IF;
326
327 l_msg_data:=l_msg_data || 'Total No. Of Partners before sending to e_location: ' || l_partner_tbl.count;
328
329 l_skip_server := fnd_profile.value('PV_LOCATOR_DO_SKIP_SERVER');
330
331 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
332 Debug(p_msg_string => 'Do we SKIP E-Location???: ' || l_skip_server);
333 END IF;
334
335
336 IF(l_partner_tbl.count <>0) THEN
337 IF l_skip_server = 'Y' THEN
338 l_return_partner_tbl := l_partner_tbl;
339 ELSE
340 Get_Partners_From_ELocation(
341 p_api_version =>p_api_version
342 ,p_init_msg_list =>p_init_msg_list
343 ,p_commit =>p_commit
344 ,p_validation_level =>p_validation_level
345 ,p_customer_address =>l_customer_rec
346 ,p_partner_tbl =>l_partner_tbl
347 ,p_max_no_partners =>p_max_no_partners
348 ,p_distance =>p_distance
349 ,p_distance_unit =>p_distance_unit
350 ,x_partner_tbl =>l_return_partner_tbl
351 ,x_return_status =>x_return_status
352 ,x_msg_count =>x_msg_count
353 ,x_msg_data =>x_msg_data
354 );
355 END IF;
356 ELSE
357 l_return_partner_tbl := l_partner_tbl;
358 END IF;
359
360 x_partner_tbl := JTF_NUMBER_TABLE();
361 x_distance_tbl := JTF_NUMBER_TABLE();
362
363 FOR i in 1 .. l_return_partner_tbl.count LOOP
364 x_partner_tbl.extend;
365 x_distance_tbl.extend;
366
367 x_partner_tbl(i) := l_return_partner_tbl(i).party_relation_id;
368 x_distance_tbl(i) := l_return_partner_tbl(i).distance;
369 END LOOP;
370
371 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
372 Debug(p_msg_string => 'Total No. Of Partners after getting response form e_location: ' || x_partner_tbl.count);
373 END IF;
374
375 l_msg_data:=l_msg_data ||'Total No. Of Partners after getting response form e_location: ' || x_partner_tbl.count;
376
377 -- Standard call to get message count and if count is 1, get message info.
378 FND_MSG_PUB.Count_And_Get
379 (
380 p_count => x_msg_count,
381 p_data => x_msg_data
382 );
383
384 x_msg_data:=l_msg_data;
385
386
387 EXCEPTION
388
389 WHEN FND_API.G_EXC_ERROR THEN
390
391 x_return_status := FND_API.G_RET_STS_ERROR ;
392 fnd_msg_pub.Count_And_Get
393 (
394 p_encoded => FND_API.G_TRUE,
395 p_count => x_msg_count,
396 p_data => x_msg_data
397 );
398
399 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
400
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 fnd_msg_pub.Count_And_Get
403 (
404 p_encoded => FND_API.G_TRUE,
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408
409 WHEN OTHERS THEN
410
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 --FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
413 fnd_msg_pub.Count_And_Get
414 (
415 p_encoded => FND_API.G_TRUE,
416 p_count => x_msg_count,
417 p_data => x_msg_data
418 );
419 END Get_Locator_Partners;
420
421 ---------------------------------------------------------------------
422 -- PROCEDURE
423 -- get_partners
424 --
425 -- PURPOSE
426 -- Based on the starting address, the API finds the all the partners
427 -- limited by the max number of partner returned within the distance provided
428 -- This API is used from the wrapper API for locator and opportunity matching
429 --
430 -- PARAMETERS
431 -- p_party_address_rec: the record to hold customer address.
432 -- p_partner_tbl: returns the list of partners sorted based on the distance
433 --
434 -- NOTES
435 -- 1. object_version_number will be set to 1.
436 ---------------------------------------------------------------------
437 PROCEDURE Get_Partners(
438 p_api_version IN NUMBER
439 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
440 ,p_commit IN VARCHAR2 := FND_API.g_false
441 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
442
443 ,p_customer_address IN party_address_rec_type
444 ,p_partner_tbl IN JTF_NUMBER_TABLE
445 ,p_max_no_partners IN NUMBER
446 ,p_distance IN NUMBER
447 ,p_distance_unit IN VARCHAR2
448 ,p_sort_by_distance IN VARCHAR2 := 'T'
449 ,x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE
450 ,x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE
451 ,x_distance_unit OUT NOCOPY VARCHAR2
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_msg_count OUT NOCOPY NUMBER
454 ,x_msg_data OUT NOCOPY VARCHAR2
455 )
456 IS
457
458 l_api_version CONSTANT NUMBER := 1.0;
459 l_api_name CONSTANT VARCHAR2(30) := 'Get_Partners';
460 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
461
462 l_distance_unit VARCHAR2(30);
463 type l_partner_rec_type IS RECORD
464 (
465 DISTANCE NUMBER,
466 --rownumber NUMBER,
467 PARTY_RELATION_ID NUMBER,
468 PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE,
469 ADDRESS_LINE1 HZ_LOCATIONS.ADDRESS1%TYPE,
470 ADDRESS_LINE2 HZ_LOCATIONS.ADDRESS2%TYPE,
471 ADDRESS_LINE3 HZ_LOCATIONS.ADDRESS3%TYPE,
472 CITY HZ_LOCATIONS.CITY%TYPE,
473 STATE HZ_LOCATIONS.STATE%TYPE,
474 COUNTRY HZ_LOCATIONS.COUNTRY%TYPE,
475 POSTAL_CODE HZ_LOCATIONS.POSTAL_CODE%TYPE,
476 row_number NUMBER
477 );
478
479
480 l_ADDRESS_LINE1 HZ_LOCATIONS.ADDRESS1%TYPE;
481 l_ADDRESS_LINE2 HZ_LOCATIONS.ADDRESS2%TYPE;
482 l_ADDRESS_LINE3 HZ_LOCATIONS.ADDRESS3%TYPE;
483 l_CITY HZ_LOCATIONS.CITY%TYPE;
484 l_STATE HZ_LOCATIONS.STATE%TYPE;
485 l_COUNTRY HZ_LOCATIONS.COUNTRY%TYPE;
486 l_POSTAL_CODE HZ_LOCATIONS.POSTAL_CODE%TYPE;
487
488 l_partner_rec l_partner_rec_type;
489 type cur_type IS REF CURSOR;
490 l_parties_cursor cur_type;
491 l_partner_tbl party_address_rec_tbl;
492 l_counter NUMBER :=0;
493 --l_return_partner_tbl party_address_rec_tbl;
494 l_return_final_partner_tbl party_address_rec_tbl;
495 l_customer_rec party_address_rec_type;
496 l_customer_geocode_object HZ_LOCATIONS.GEOMETRY%TYPE;
497 --l_partner_id_string VARCHAR2(2000):='';
498 l_query VARCHAR2(4000);
499 l_string VARCHAR2(4000);
500 l_max_no_partners NUMBER;
501 l_distance NUMBER;
502 l_partner_count NUMBER;
503
504 CURSOR lc_geometry (pc_location_id number) IS
505 select hzl.geometry,hzl.address1,hzl.address2,hzl.address3,hzl.city,hzl.state,hzl.country,hzl.postal_code
506 from hz_locations hzl
507 where hzl.location_id =pc_location_id;
508
509 l_count NUMBER;
510 l_skip_server CONSTANT VARCHAR(1) :=nvl(fnd_profile.value('PV_SKIP_ELOCATION_FOR_MATCHING'), 'N');
511
512 my_message VARCHAR2(2000);
513
514 BEGIN
515
516
517
518 -- Standard call to check for call compatibility.
519 IF NOT FND_API.Compatible_API_Call ( l_api_version,
520 p_api_version,
521 l_api_name,
522 G_PKG_NAME)
523 THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526
527 -- Initialize message list if p_init_msg_list is set to TRUE.
528 IF FND_API.to_Boolean( p_init_msg_list )
529 THEN
530 FND_MSG_PUB.initialize;
531 END IF;
532
533 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
534 Debug(
535 p_msg_string => '........................................................... '
536 );
537 END IF;
538 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
539 Debug(
540 p_msg_string => 'START OF PV_LOCATOR.GET_PARTNERS() '
541 );
542 END IF;
543
544
545 -- Debug Message
546 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
547 Debug(
548 p_msg_string => 'In ' || l_api_name
549 );
550 END IF;
551
552
553 -- Initialize API return status to SUCCESS
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555 --x_partner_tbl := p_partner_tbl;
556
557 --Check for distance unit
558 if(p_distance_unit is null) then
559
560 --l_distance_unit:= fnd_profile.value('PV_LOCATOR_DEFAULT_DISTANCE_UOM');
561 l_distance_unit:= fnd_profile.value('PV_LOCATOR_DISTANCE_UNIT');
562
563 if(l_distance_unit = 'MILES') then
564 l_distance_unit:= g_distance_unit_mile;
565 elsif(l_distance_unit = 'KILOMETERS') then
566 l_distance_unit:= g_distance_unit_km;
567 else
568 l_distance_unit:= g_distance_unit_mile;
569 end if;
570
571 else
572 l_distance_unit:=p_distance_unit;
573 end if;
574
575 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
576 Debug(
577 p_msg_string => 'Distance unit::::' || l_distance_unit
578
579 );
580 END IF;
581
582 -- getting geocode object for customer
583 if(p_customer_address.LOCATION_ID is not null) then
584 -- dbms_output.put_line('Location id is not null');
585 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
586 Debug(
587 p_msg_string => 'Location Id::::' || p_customer_address.LOCATION_ID
588
589 );
590 END IF;
591
592 OPEN lc_geometry (pc_location_id => p_customer_address.LOCATION_ID);
593 FETCH lc_geometry INTO l_customer_geocode_object, l_ADDRESS_LINE1,l_ADDRESS_LINE2,l_ADDRESS_LINE3,l_city,l_state,l_country,l_postal_code;
594 CLOSE lc_geometry;
595
596 end if;
597
598
599
600
601
602 if(p_partner_tbl IS NULL) then
603 --raise_application_error(-20506, ' partner id table received is null' || ' in ' || l_api_name );
604 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
605 p_msg_name => 'PV_LOCATOR_NO_PARTNERS',
606 p_token1 => null,
607 p_token1_value => null,
608 p_token2 => null,
609 p_token2_value => null);
610
611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
612 end if;
613
614
615 if ( p_distance is null and p_max_no_partners is null and
616 (l_customer_geocode_object IS NULL or (l_customer_geocode_object IS NOT NULL and l_customer_geocode_object.sdo_point.x IS NULL))
617 ) then
618 --if this condition satisfies, return all partners as it is.
619 --else contact Location server and return...
620
621
622
623 x_distance_unit :=l_distance_unit;
624 x_partner_tbl := JTF_NUMBER_TABLE();
625 x_distance_tbl := JTF_NUMBER_TABLE();
626 FOR i in 1 .. p_partner_tbl.count LOOP
627 x_partner_tbl.extend;
628 x_distance_tbl.extend;
629 x_partner_tbl(i):=p_partner_tbl(i);
630 --x_distance_tbl(i) := l_return_final_partner_tbl(i).distance;
631 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
632 Debug(
633 p_msg_string => 'id: ' || x_partner_tbl(i) || ' Distance: ' || x_distance_tbl(i)
634 );
635 END IF;
636 END LOOP;
637
638
639 Debug(
640 p_msg_string => ' Returning all partners back as it is'
641 );
642
643 else
644 if(p_customer_address.LOCATION_ID is null or l_customer_geocode_object is null) then
645 l_customer_geocode_object := address_to_geometry(null, lower(p_customer_address.ADDRESS_LINE1 || ' ' ||p_customer_address.ADDRESS_LINE2 || ' ' ||p_customer_address.ADDRESS_LINE3),
646 p_customer_address.CITY, lower(p_customer_address.STATE), p_customer_address.POSTAL_CODE);
647 end if;
648
649
650 if(l_customer_geocode_object IS NULL) then
651
652 --raise_application_error(-20504, ' customer geocode object is null' || ' in ' || l_api_name );
653 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
654 p_msg_name => 'PV_LOCATOR_CUST_ADDR_INVALID',
655 p_token1 => null,
656 p_token1_value => null,
657 p_token2 => null,
658 p_token2_value => null);
659
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 end if;
662
663 if(l_customer_geocode_object IS NOT NULL and l_customer_geocode_object.sdo_point.x IS NULL) then
664
665 --raise_application_error(-20505, 'The customer does not have a valid address' );
666 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
667 p_msg_name => 'PV_LOCATOR_CUST_ADDR_INVALID',
668 p_token1 => null,
669 p_token1_value => null,
670 p_token2 => null,
671 p_token2_value => null);
672
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 end if;
675
676
677 --Forming a string of all partner ids to use it in query
678 /* --------------------------------------------------------------------------------
679 FOR partner_index IN 1..p_partner_tbl.count LOOP
680 l_partner_id_string:=l_partner_id_string || ',' || p_partner_tbl(partner_index);
681 end loop;
682 l_partner_id_string := substr(l_partner_id_string,2);
683 * -------------------------------------------------------------------------------- */
684
685 l_customer_rec.ADDRESS_LINE1 := l_ADDRESS_LINE1;
686 l_customer_rec.ADDRESS_LINE2 := l_ADDRESS_LINE2;
687 l_customer_rec.ADDRESS_LINE3 := l_ADDRESS_LINE3;
688 l_customer_rec.CITY := l_city;
689 l_customer_rec.STATE := l_state;
690 l_customer_rec.COUNTRY := l_country;
691 l_customer_rec.POSTAL_CODE := l_postal_code;
692
693 Debug(p_msg_string => 'p_partner_tbl.COUNT: ' || p_partner_tbl.count);
694
695
696 -- ---------------------------------------------------------------------
697 -- The "leading" hint is to make sure that the optimizer will make
698 -- c (CAST PLSQL table) as the driving table as it is most likely the
699 -- smallest "table" in the join. This, in most cases, speeds up the
700 -- performance dramatically.
701 -- ---------------------------------------------------------------------
702 l_query:=
703 'select * from ( ' ||
704 ' select /*+ leading(c) */ pv_locator.geocode_distance(:1,hzl.geometry,:2) dis, ' ||
705 ' pvpp.partner_id party_id, ' ||
706 ' org.party_name, '||
707 ' hzl.address1, '||
708 ' hzl.address2, '||
709 ' hzl.address3, '||
710 ' hzl.city, '||
711 ' hzl.state, '||
712 ' hzl.country, '||
713 ' hzl.postal_code, '||
714 ' rownum rn '||
715 ' from hz_parties org, ' ||
716 ' hz_party_sites hzs, ' ||
717 ' hz_locations hzl, '||
718 ' pv_partner_profiles pvpp, '||
719 ' (SELECT * ' ||
720 ' FROM (SELECT column_value party_id ' ||
721 ' FROM (SELECT column_value ' ||
722 ' FROM TABLE (CAST(:p_partner_tbl AS JTF_NUMBER_TABLE))))) c ' ||
723 ' where pvpp.partner_id = c.party_id ' ||
724 ' and org.party_id (+) = pvpp.partner_party_id '||
725 ' and org.party_type (+) = '||''''||'ORGANIZATION'||''''||
726 ' and org.party_id = hzs.party_id (+) '||
727 ' and hzs.location_id = hzl.location_id (+) and '||
728 ' hzs.identifying_address_flag (+) = '||''''||'Y'||'''' ;
729
730
731 --Here we are changing the query because, when p_distance and p_max_no_partners are both null, then it should resturn al;l partners
732 -- irrespective of geometry object
733
734 if (p_distance is null and p_max_no_partners is null) then
735 l_distance:=0;
736 l_query:=l_query || ') where (dis is null or dis >=:3) ';
737 elsif(p_distance is null ) then
738 l_distance:=0;
739 l_query:=l_query || ' and hzl.geometry is not null) where dis>=:3 ';
740 else
741 l_distance:=p_distance;
742 l_query:=l_query || ' and hzl.geometry is not null) where dis<=:3 ';
743 end if;
744
745 --adding order by column
746 if (UPPER(p_sort_by_distance) = 'T') then
747 l_query:=l_query || ' order by 1 asc';
748 else
749 l_query:=l_query || ' order by 2 ';
750 end if;
751
752
753 /*
754 if(p_distance is not null) THEN
755 l_distance:=p_distance;
756 l_query:=l_query || ' where dis<=:3 order by 1 asc';
757 else
758 l_distance:=0;
759 l_query:=l_query || ' where dis>=:3 order by 1 asc';
760 end if;
761 */
762
763 -- THE QUERY IS
764 -- --------------------------------------------------------------------------------
765 -- The second SELECT statement actually starts with /*+ leading(c) */
766 -- --------------------------------------------------------------------------------
767 /*
768 SELECT *
769 FROM (SELECT pv_locator.geocode_distance(:1,hzl.geometry,:2) dis,
770 pvpp.partner_id party_id, org.party_name, hzl.address1,
771 hzl.address2, hzl.address3, hzl.city, hzl.state, hzl.country,
772 hzl.postal_code, rownum rn
773 FROM hz_parties org,
774 hz_party_sites hzs,
775 hz_locations hzl,
776 pv_partner_profiles pvpp,
777 (SELECT *
778 FROM (SELECT column_value party_id
779 FROM (SELECT column_value
780 FROM TABLE(CAST(:p_partner_tbl AS JTF_NUMBER_TABLE))))) c
781 WHERE pvpp.partner_id = c.party_id and
782 org.party_id (+) = pvpp.partner_party_id and
783 org.party_type (+) = 'ORGANIZATION' and
784 org.party_id = hzs.party_id (+) and
785 hzs.location_id = hzl.location_id (+) and
786 hzs.identifying_address_flag (+) = 'Y' and
787 hzl.geometry is not null)
788 WHERE dis<=:3
789 ORDER BY 1 ASC;
790 */
791
792 l_string := l_query;
793 loop
794 exit when l_string is null;
795 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
796 Debug(
797 p_msg_string => substr( l_string, 1, 200 )
798 );
799 END IF;
800 l_string := substr( l_string, 201 );
801 end loop;
802
803
804 OPEN l_parties_cursor FOR l_query
805 USING l_customer_geocode_object, l_distance_unit, p_partner_tbl, l_distance;
806
807 LOOP
808
809 FETCH l_parties_cursor into l_partner_rec;
810 EXIT WHEN l_parties_cursor%NOTFOUND;
811 l_counter :=l_counter+1;
812
813 l_partner_tbl(l_counter).PARTY_RELATION_ID := l_partner_rec.party_relation_id;
814 l_partner_tbl(l_counter).PARTY_NAME := l_partner_rec.party_name;
815 l_partner_tbl(l_counter).ADDRESS_LINE1 := l_partner_rec.address_line1;
816 l_partner_tbl(l_counter).ADDRESS_LINE2 := l_partner_rec.address_line2;
817 l_partner_tbl(l_counter).ADDRESS_LINE3 := l_partner_rec.address_line3;
818 l_partner_tbl(l_counter).CITY := l_partner_rec.city;
819 l_partner_tbl(l_counter).STATE := l_partner_rec.state;
820 l_partner_tbl(l_counter).COUNTRY := l_partner_rec.country;
821 l_partner_tbl(l_counter).POSTAL_CODE := l_partner_rec.postal_code;
822 l_partner_tbl(l_counter).distance := l_partner_rec.distance;
823
824
825 END LOOP;
826
827 CLOSE l_parties_cursor;
828
829
830 --dbms_output.put_line('no of partners before sending eLocation' || l_partner_tbl.count || ' distance' || p_distance || 'unit' || l_distance_unit || 'no' || p_max_no_partners);
831 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
832 Debug(
833 p_msg_string => 'Total No. Of Partners before sending to e_location: ' || l_partner_tbl.count
834 );
835 END IF;
836
837 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
838 Debug(
839 p_msg_string => 'Skipping e_location server?? ' || l_skip_server
840 );
841 END IF;
842
843 -- if skip server profile value is set to true, we need to skip the server and return results
844 -- based on the radial distances.
845 if(upper(l_skip_server) = 'Y') then
846
847 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
848 Debug(
849 p_msg_string => 'Returning partners with out calling Get_Partners_From_ELocation'
850 );
851 END IF;
852
853 if( p_max_no_partners is null) then
854 l_partner_count := l_partner_tbl.count;
855
856 elsif(p_max_no_partners > l_partner_tbl.count) then
857 l_partner_count := l_partner_tbl.count;
858 else
859 l_partner_count :=p_max_no_partners;
860 end if;
861
862 FOR i in 1 .. l_partner_count LOOP
863 l_return_final_partner_tbl(i):=l_partner_tbl(i);
864 END LOOP;
865
866
867 elsif(l_partner_tbl.count <>0) then
868 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
869 Debug(
870 p_msg_string => 'Before calling Get_Partners_From_ELocation'
871 );
872 END IF;
873
874 Get_Partners_From_ELocation(
875 p_api_version =>p_api_version
876 ,p_init_msg_list =>p_init_msg_list
877 ,p_commit =>p_commit
878 ,p_validation_level =>p_validation_level
879
880 ,p_customer_address =>l_customer_rec
881 ,p_partner_tbl =>l_partner_tbl
882 ,p_max_no_partners =>p_max_no_partners
883 ,p_distance =>p_distance
884 ,p_distance_unit =>l_distance_unit
885 ,p_sort_by_distance =>p_sort_by_distance
886 ,x_partner_tbl =>l_return_final_partner_tbl
887 ,x_return_status =>x_return_status
888 ,x_msg_count =>x_msg_count
889 ,x_msg_data =>x_msg_data
890 );
891
892
893 else
894 l_return_final_partner_tbl := l_partner_tbl;
895 end if;
896 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
897 Debug(
898 p_msg_string => 'Total No. Of Partnersafter getting from e_location: ' || l_return_final_partner_tbl.count
899 );
900 END IF;
901 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
902 Debug(
903 p_msg_string => 'Partners List after getting from eLocation...
904 ::::'
905 );
906 END IF;
907
908 x_distance_unit :=l_distance_unit;
909 x_partner_tbl := JTF_NUMBER_TABLE();
910 x_distance_tbl := JTF_NUMBER_TABLE();
911 FOR i in 1 .. l_return_final_partner_tbl.count LOOP
912 x_partner_tbl.extend;
913 x_distance_tbl.extend;
914 x_partner_tbl(i):=l_return_final_partner_tbl(i).party_relation_id;
915 x_distance_tbl(i) := l_return_final_partner_tbl(i).distance;
916 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
917 Debug(
918 p_msg_string => 'id: ' || x_partner_tbl(i) || ' Distance: ' || x_distance_tbl(i)
919 );
920 END IF;
921
922 END LOOP;
923 end if;
924
925
926
927 --dbms_output.put_line('no of patners returned' || l_return_final_partner_tbl.count);
928 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
929 Debug(
930 p_msg_string => 'END OF PV_LOCATOR.GET_PARTNERS() '
931 );
932 END IF;
933 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
934 Debug(
935 p_msg_string => '........................................................... '
936 );
937 END IF;
938
939
940 -- Standard call to get message count and if count is 1, get message info.
941 FND_MSG_PUB.Count_And_Get
942 (
943 p_count => x_msg_count,
944 p_data => x_msg_data
945 );
946
947 EXCEPTION
948
949 WHEN FND_API.G_EXC_ERROR THEN
950
951 x_return_status := FND_API.G_RET_STS_ERROR ;
952 fnd_msg_pub.Count_And_Get
953 (
954 p_encoded => FND_API.G_TRUE,
955 p_count => x_msg_count,
956 p_data => x_msg_data
957 );
958
959 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
960
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
962
963 fnd_msg_pub.Count_And_Get
964 (
965 p_encoded => FND_API.G_TRUE,
966 p_count => x_msg_count,
967 p_data => x_msg_data
968 );
969
970 WHEN OTHERS THEN
971
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 --FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
974 --Debug(SQLCODE || ':::' || SQLERRM);
975 fnd_msg_pub.Count_And_Get
976 (
977 p_encoded => FND_API.G_TRUE,
978 p_count => x_msg_count,
979 p_data => x_msg_data
980 );
981 END Get_Partners;
982
983 ---------------------------------------------------------------------
984 -- PROCEDURE
985 -- get_partners
986 --
987 -- PURPOSE
988 -- Based on the starting address, the API finds the all the partners
989 -- limited by the max number of partner returned within the distance provided
990 -- This API is used from the wrapper API for locator and opportunity matching
991 --
992 -- PARAMETERS
993 -- p_party_address_rec: the record to hold customer address.
994 -- p_partner_tbl: returns the list of partners sorted based on the distance
995 --
996 -- NOTES
997 -- 1. object_version_number will be set to 1.
998 ---------------------------------------------------------------------
999 PROCEDURE Get_Partners_From_ELocation(
1000 p_api_version IN NUMBER
1001 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1002 ,p_commit IN VARCHAR2 := FND_API.g_false
1003 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1004 ,p_customer_address IN party_address_rec_type
1005 ,p_partner_tbl IN party_address_rec_tbl
1006 ,p_max_no_partners IN NUMBER
1007 ,p_distance IN NUMBER
1008 ,p_distance_unit IN VARCHAR2
1009 ,p_sort_by_distance IN VARCHAR2 := 'T'
1010 ,x_partner_tbl OUT NOCOPY party_address_rec_tbl
1011 ,x_return_status OUT NOCOPY VARCHAR2
1012 ,x_msg_count OUT NOCOPY NUMBER
1013 ,x_msg_data OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016
1017 l_api_version CONSTANT NUMBER := 1.0;
1018 l_api_name CONSTANT VARCHAR2(30) := 'Get_Partners_From_ELocation';
1019 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1020
1021 l_partner_tbl party_address_rec_tbl;
1022
1023 /*
1024 geocoder_host VARCHAR2(128) := 'virao-pc.us.oracle.com:8888';
1025 geocoder_path VARCHAR2(128) := '/servlet/routem';
1026 */
1027
1028 xml_request VARCHAR2(32000);
1029 cust_xml VARCHAR2(32000) := '';
1030 partner_xml VARCHAR2(32000) := '';
1031 xml_response VARCHAR2(32000);
1032 url VARCHAR2(4000);
1033 l_string VARCHAR2(32000);
1034 l_first_quote_loc NUMBER;
1035 l_second_quote_loc NUMBER;
1036 l_distance_loc NUMBER;
1037 l_partyid_loc NUMBER;
1038 l_distance NUMBER;
1039 l_party_id NUMBER;
1040 l_party_id_str VARCHAR(100);
1041 l_distance_str VARCHAR(100);
1042 l_count NUMBER :=1;
1043 l_max_no_partners NUMBER :=0;
1044 l_partner_count NUMBER :=0;
1045 l_loop_count NUMBEr :=0;
1046 l_content_type VARCHAR2(100);
1047 l_msg_data VARCHAR2(10000):='';
1048 l_sort_by_distance VARCHAR2(100);
1049 l_route_id_loc NUMBER;
1050 l_error_id_loc NUMBER;
1051
1052 BEGIN
1053
1054
1055 -- Standard call to check for call compatibility.
1056 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1057 p_api_version,
1058 l_api_name,
1059 G_PKG_NAME)
1060 THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 END IF;
1063
1064 -- Initialize message list if p_init_msg_list is set to TRUE.
1065 IF FND_API.to_Boolean( p_init_msg_list )
1066 THEN
1067 FND_MSG_PUB.initialize;
1068 END IF;
1069 -- Debug Message
1070 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1071 Debug(
1072 p_msg_string => 'In ' || l_api_name
1073 );
1074 END IF;
1075
1076
1077 -- Initialize API return status to SUCCESS
1078 x_return_status := FND_API.G_RET_STS_SUCCESS;
1079
1080 --checking sort_by_distance
1081 if(UPPER(p_sort_by_distance)='T') then
1082 l_sort_by_distance := 'true';
1083 else
1084 l_sort_by_distance := 'false';
1085 end if;
1086 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1087 Debug(
1088 p_msg_string => 'Sort By Distance::::::::::::: ' || l_sort_by_distance
1089 );
1090 END IF;
1091
1092 --dbms_output.put_line('in elocation call');
1093 -- construct customer XML part
1094 cust_xml:=cust_xml || '<start_location> ' ;
1095 cust_xml:=cust_xml || '<input_location id="1"> ' ;
1096 cust_xml:=cust_xml || '<input_address> ' ;
1097 cust_xml:=cust_xml || '<us_form1 street="' || p_customer_address.ADDRESS_LINE1 || ' ' ||
1098 p_customer_address.ADDRESS_LINE2 || ' ' || p_customer_address.ADDRESS_LINE3 ||
1099 '" lastline="' || p_customer_address.CITY || ', ' || p_customer_address.STATE ||
1100 ' ' || p_customer_address.POSTAL_CODE || '" /> ' ;
1101 cust_xml:=cust_xml || '</input_address> ' ;
1102 cust_xml:=cust_xml || '</input_location> ' ;
1103 cust_xml:=cust_xml || '</start_location> ';
1104
1105
1106 if(p_partner_tbl IS NULL) then
1107 --raise_application_error(-20508, 'In ' || l_api_name || ' partner address table is null');
1108 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1109 p_msg_name => 'PV_LOCATOR_PARTNERS_NONE',
1110 p_token1 => null,
1111 p_token1_value => null,
1112 p_token2 => null,
1113 p_token2_value => null);
1114
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 end if;
1117 -- max_no-of_partners can not be more than no of entries in the table.
1118 --even if it is, it wont retrieve those many . so, making max as table count.
1119 if (p_max_no_partners is null) then
1120 l_max_no_partners := p_partner_tbl.count;
1121 elsif (p_max_no_partners >= p_partner_tbl.count) then
1122 l_max_no_partners := p_partner_tbl.count;
1123 else
1124 l_max_no_partners := p_max_no_partners;
1125 end if;
1126
1127
1128
1129
1130 -- what this loop does is
1131 -- It is sending requests for max_no_of_partners at a time.
1132 -- and gets response from elocation. If it gets max_no_of_partners back , It is going to stop
1133 -- It sends another set of partners until it gets max no of partners.
1134 l_msg_data:= l_msg_data || 'The distance' || p_distance || 'unit '|| p_distance_unit || 'max no '|| p_max_no_partners;
1135
1136 --dbms_output.put_line('in elocation call' || 'The distance' || p_distance || 'unit '|| p_distance_unit || 'max no '|| p_max_no_partners);
1137 Loop --main loop exits when you get max no of partners or when you end up sending all partners in partner table
1138 exit when l_partner_count >= l_max_no_partners or l_loop_count >= p_partner_tbl.count;
1139 -- construct partners XML part
1140
1141
1142 l_msg_data:= l_msg_data || 'in main loop ' || ' l_partner_count: ' || l_partner_count ||
1143 ' l_max_no_partners: ' || l_max_no_partners ||
1144 ' l_loop_count'|| l_loop_count || ' p_partner_tbl.count: ' ||p_partner_tbl.count;
1145
1146 partner_xml:='';
1147 FOR i IN 1..l_max_no_partners LOOP
1148
1149 exit when l_loop_count >= p_partner_tbl.count;
1150 --l_msg_data:= l_msg_data || 'id' || p_partner_tbl(1+l_loop_count).PARTY_RELATION_ID || p_partner_tbl(1+l_loop_count).ADDRESS_LINE1 ||
1151 --p_partner_tbl(1+l_loop_count).CITY || p_partner_tbl(1+l_loop_count).STATE || p_partner_tbl(1+l_loop_count).POSTAL_CODE || 'gap' ;
1152
1153 partner_xml:=partner_xml || '<end_location> ';
1154 partner_xml:=partner_xml || '<input_location id="' || p_partner_tbl(1+l_loop_count).PARTY_RELATION_ID || '"> ' ;
1155 partner_xml:=partner_xml || '<input_address> ' ;
1156 partner_xml:=partner_xml || '<us_form1 street="' || p_partner_tbl(1+l_loop_count).ADDRESS_LINE1 || ' ' ||
1157 p_partner_tbl(1+l_loop_count).ADDRESS_LINE2 || ' ' || p_partner_tbl(1+l_loop_count).ADDRESS_LINE3 ||
1158 '" lastline="' || p_partner_tbl(1+l_loop_count).CITY || ', ' || p_partner_tbl(1+l_loop_count).STATE ||
1159 ' ' || p_partner_tbl(1+l_loop_count).POSTAL_CODE || '" /> ' ;
1160 partner_xml:=partner_xml || '</input_address> ' ;
1161 partner_xml:=partner_xml || '</input_location> ' ;
1162 partner_xml:=partner_xml || '</end_location> ' ;
1163
1164 l_loop_count :=l_loop_count+1;
1165 --l_msg_data:= l_msg_data || partner_xml;
1166 END LOOP;
1167
1168 -- construct XML request
1169 xml_request := '<?xml version="1.0" standalone="yes"?> ' ||
1170 ' <batch_route_request id="8" route_preference="fastest" ' ||
1171 ' road_preference="highway" return_driving_directions="false" ' ||
1172 ' sort_by_distance="' || l_sort_by_distance || '" cutoff_distance="'|| p_distance ||'" ' ||
1173 ' distance_unit="'||p_distance_unit||'" time_unit="second"> ' ||
1174 cust_xml ||
1175 partner_xml ||
1176 ' </batch_route_request>';
1177
1178 --dbms_output.put_line('in elocation call after xml request');
1179 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1180 Debug(
1181 p_msg_string => 'XML REQUEST: **************'
1182 );
1183 END IF;
1184 --dbms_output.put_line('XML REQUEST: **************');
1185 --dbms_output.put_line('in elocation call in loop');
1186 --printing xml_request
1187 l_string := xml_request;
1188 loop
1189 exit when l_string is null;
1190 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1191 Debug(
1192 p_msg_string => substr( l_string, 1, 200 )
1193 );
1194 END IF;
1195 l_string := substr( l_string, 201 );
1196 end loop;
1197 --l_msg_data:= l_msg_data || 'Request is '|| xml_request;
1198 -- dbms_output.put_line('Sending Request..');
1199 hz_http_pkg.post(
1200 doc => 'xml_request=' || xml_request,
1201 content_type => g_input_content_type,
1202 url => g_route_url,
1203 resp => xml_response,
1204 resp_content_type => l_content_type,
1205 proxyserver => g_proxy_server,
1206 proxyport => g_proxy_port,
1207 x_return_status => x_return_status,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data
1210 );
1211
1212 -- dbms_output.put_line('got the response..');
1213 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1214 Debug(
1215 p_msg_string => 'XML RESPONSE: **************'
1216 );
1217 END IF;
1218 --dbms_output.put_line('XML RESPONSE: **************');
1219 --printing xml_response
1220 l_string := xml_response;
1221 loop
1222 exit when l_string is null;
1223 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1224 Debug(
1225 p_msg_string => substr( l_string, 1, 200 )
1226 );
1227 END IF;
1228 l_string := substr( l_string, 201 );
1229 end loop;
1230 -- l_msg_data:= l_msg_data || 'Response is '|| xml_response || 'got response';
1231
1232
1233 --what if xml_response is null
1234 if (xml_response is null) then
1235 --raise_application_error(-20516, 'In ' || l_api_name || ' Server is not available. Sever may be down right now. Try after some time.');
1236 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1237 p_msg_name => 'PV_LOCATOR_SERVICE_UNAVAILABLE',
1238 p_token1 => null,
1239 p_token1_value => null,
1240 p_token2 => null,
1241 p_token2_value => null);
1242
1243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1244 end if;
1245
1246
1247 loop --parsing loop
1248
1249 --if xml_response is an exception, this will throw an exception so that we can
1250 --display message like temporarily unavailable to user
1251 IF( INSTR(xml_response,'500 Internal Server Error',1, 1) <> 0 or
1252 INSTR(xml_response,'400 Bad Request',1, 1) <> 0 or
1253 INSTR(xml_response,'NegativeArraySizeException',1, 1) <> 0 or
1254 INSTR(xml_response,'Fatal error in file',1, 1) <> 0 or
1255 INSTR(xml_response,'component_error',1, 1) <> 0 or
1256 INSTR(xml_response,'generic_error',1, 1) <> 0 or
1257 INSTR(xml_response,' Error parsing',1, 1) <> 0 ) THEN
1258
1259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1260 --raise_application_error(-20509, ' eLocation Server is not available' || 'in ' || l_api_name );
1261 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1262 p_msg_name => 'PV_LOCATOR_SERVICE_UNAVAILABLE',
1263 p_token1 => null,
1264 p_token1_value => null,
1265 p_token2 => null,
1266 p_token2_value => null);
1267
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 -- dbms_output.put_line('Error occured in parsing..');
1270 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271 END IF;
1272
1273 --exit when xml_response is null or no id string in xml_response;
1274 --EXIT when INSTR(xml_response, 'route id', 1, 1) =0 ;
1275
1276
1277 -----------------------------------------------check for both here
1278
1279 l_route_id_loc := INSTR(xml_response, 'route id', 1, 1);
1280 l_error_id_loc := INSTR(xml_response, 'router_error id', 1, 1);
1281
1282
1283 --if 'route id' string and erro_id string does not exist in xml_response , then exit
1284 --if route_id exists and error_id does not exist, then go to route_partner loop
1285 --if route_id does not exist and error_id exists, then go to error_partner loop
1286 -- if both exists and route_id_loc is greater than erro_id_loc, go to errored partner loop
1287 -- if both exists and route_id_loc is less than erro_id_loc, go to route partner loop
1288
1289
1290 if(l_route_id_loc =0 and l_error_id_loc =0) then
1291 exit;
1292 elsif(
1293 (
1294 (l_route_id_loc > 0 and l_error_id_loc > 0) and ( l_route_id_loc >= l_error_id_loc )
1295 )
1296 or
1297 (l_route_id_loc =0 and l_error_id_loc>0)
1298 )
1299 then
1300
1301 EXIT when INSTR(xml_response, 'router_error id', 1, 1) =0 ;
1302
1303 --First find location for id and then find location for " if id exists.
1304 l_partyid_loc := INSTR(xml_response, 'router_error id', 1, 1);
1305 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1306 Debug( p_msg_string => 'partner with invalid geocode (Geometry)' || l_partyid_loc );
1307 END IF;
1308 IF l_partyid_loc = 0 THEN
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1310 --raise_application_error(-20510,' eLocation Server is not available' || 'in ' || l_api_name);
1311 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1312 p_msg_name => 'PV_LOCATOR_SERVICE_UNAVAILABLE',
1313 p_token1 => null,
1314 p_token1_value => null,
1315 p_token2 => null,
1316 p_token2_value => null);
1317
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1320 END IF;
1321 l_first_quote_loc := INSTR(xml_response, '"', l_partyid_loc, 1);
1322 l_second_quote_loc := INSTR(xml_response, '"', l_partyid_loc, 2);
1323 --this line will fetch id value that is between " and "
1324 l_party_id_str := SUBSTR(
1325 xml_response,
1326 l_first_quote_loc + 1,
1327 (l_second_quote_loc - 1)-(l_first_quote_loc + 1) + 1);
1328
1329 l_party_id:=to_number(l_party_id_str);
1330
1331 --at this point, check l_party_id
1332 --if l_party_id is 1, that means error comes at geocoding customer address which is start address.
1333 -- then eLocation server, will not return any party ids with distances.
1334 --So we need to handle this seperately in manual matching case where p_distance is null and p_max_no_partners is null
1335 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1336 Debug( p_msg_string => 'partner with invalid geocode (Geometry):party_id:' || l_party_id );
1337 END IF;
1338 --if p_distance is null and p_max_no_partners is null, then only we have to return partners with null distance
1339 -- so checking here
1340 if (p_distance is null and p_max_no_partners is null) then
1341 --add partner_id to partner table and null to distance table
1342 if (l_partner_count >= l_max_no_partners) then
1343
1344 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1345 Debug(
1346 p_msg_string => 'max_no_of_partners reached ...exiting..' ||l_api_name
1347 );
1348 END IF;
1349 exit;
1350 --at this point, check l_party_id
1351 --if l_party_id is 1, that means error comes at geocoding customer address which is start address.
1352 -- then eLocation server, will not return any party ids with distances.
1353 --So we need to handle this seperately in manual matching case where p_distance is null and p_max_no_partners is null
1354
1355 elsif (l_party_id=1) then
1356 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1357 Debug( p_msg_string => 'Customer address (start address) is not geocodable, Change customer address and try again' );
1358 END IF;
1359
1360
1361 FOR i IN 1..l_max_no_partners LOOP
1362 l_partner_tbl(l_count).PARTY_RELATION_ID:=p_partner_tbl(i).PARTY_RELATION_ID;
1363 l_partner_tbl(l_count).DISTANCE:=null;
1364 l_partner_count := l_partner_count +1;
1365 l_count := l_count+1;
1366
1367 END LOOP;
1368
1369
1370 exit;
1371
1372 else
1373
1374
1375
1376 l_partner_tbl(l_count).PARTY_RELATION_ID:=l_party_id;
1377 l_partner_tbl(l_count).DISTANCE:=null;
1378 l_partner_count := l_partner_count +1;
1379 l_count := l_count+1;
1380 end if;
1381
1382 end if; --end of if (p_distance is null and p_max_no_partners is null) then
1383 l_first_quote_loc := INSTR(xml_response, 'error_msg', l_partyid_loc, 1);
1384 xml_response:=substr( xml_response, l_first_quote_loc );
1385
1386
1387 elsif(
1388 (
1389 (l_route_id_loc > 0 and l_error_id_loc > 0) and ( l_route_id_loc < l_error_id_loc )
1390 )
1391 or
1392 (l_route_id_loc >0 and l_error_id_loc=0)
1393 )
1394 then
1395
1396
1397 --First find location for id and then find location for " if id exists.
1398 l_partyid_loc := INSTR(xml_response, 'route id', 1, 1);
1399
1400 IF l_partyid_loc = 0 THEN
1401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1402 --raise_application_error(-20510,' eLocation Server is not available' || 'in ' || l_api_name);
1403 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1404 p_msg_name => 'PV_LOCATOR_SERVICE_UNAVAILABLE',
1405 p_token1 => null,
1406 p_token1_value => null,
1407 p_token2 => null,
1408 p_token2_value => null);
1409
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1412 END IF;
1413
1414 l_first_quote_loc := INSTR(xml_response, '"', l_partyid_loc, 1);
1415 l_second_quote_loc := INSTR(xml_response, '"', l_partyid_loc, 2);
1416 --this line will fetch id value that is between " and "
1417 l_party_id_str := SUBSTR(
1418 xml_response,
1419 l_first_quote_loc + 1,
1420 (l_second_quote_loc - 1)-(l_first_quote_loc + 1) + 1);
1421
1422 l_party_id:=to_number(l_party_id_str);
1423 --similarly do for distance
1424 l_distance_loc := INSTR(xml_response, 'distance', 1, 1);
1425
1426 IF l_distance_loc = 0 THEN
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1428 --raise_application_error(-20511, ' eLocation Server is not available' || 'in ' || l_api_name );
1429 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1430 p_msg_name => 'PV_LOCATOR_SERVICE_UNAVAILABLE',
1431 p_token1 => null,
1432 p_token1_value => null,
1433 p_token2 => null,
1434 p_token2_value => null);
1435
1436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1438 END IF;
1439 l_first_quote_loc := INSTR(xml_response, '"', l_distance_loc, 1);
1440 l_second_quote_loc := INSTR(xml_response, '"', l_distance_loc, 2);
1441
1442 l_distance_str := SUBSTR(
1443 xml_response,
1444 l_first_quote_loc + 1,
1445 (l_second_quote_loc - 1)-(l_first_quote_loc + 1) + 1);
1446
1447 l_msg_data:= l_msg_data || 'The DISTANCE ' || l_distance_str || ' ';
1448
1449 l_distance:=to_number(l_distance_str,'99999999999999999999.99999999999999999999');
1450 --if(l_distance <= p_distance) then
1451
1452
1453 if (l_partner_count >= l_max_no_partners) then
1454
1455
1456 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1457 Debug(
1458 p_msg_string => 'max_no_of_partners reached ...exiting..' ||l_api_name
1459 );
1460 END IF;
1461 exit;
1462
1463 else
1464
1465
1466 l_partner_tbl(l_count).PARTY_RELATION_ID:=l_party_id;
1467 l_partner_tbl(l_count).DISTANCE:=l_distance;
1468 l_partner_count := l_partner_count +1;
1469 l_count := l_count+1;
1470 end if;
1471 --end if;
1472 l_first_quote_loc := INSTR(xml_response, 'time_unit', l_distance_loc, 1);
1473 xml_response:=substr( xml_response, l_first_quote_loc );
1474 end if; -- end of else if(INSTR(xml_response, 'route id', 1, 1) =0 )...
1475
1476
1477 end loop; -- end of parsing loop
1478 end loop; --end of mian loop
1479
1480
1481 l_msg_data:= l_msg_data || 'size of l_partner Table is ' || l_partner_tbl.count;
1482 x_partner_tbl:=l_partner_tbl;
1483 -- dbms_output.put_line('size of l_partner Table is ' || l_partner_tbl.count);
1484
1485 --************************************************
1486 -- Standard call to get message count and if count is 1, get message info.
1487 FND_MSG_PUB.Count_And_Get
1488 (
1489 p_count => x_msg_count,
1490 p_data => x_msg_data
1491 );
1492 --x_msg_data:= l_msg_data;
1493
1494 EXCEPTION
1495
1496 WHEN FND_API.G_EXC_ERROR THEN
1497
1498 x_return_status := FND_API.G_RET_STS_ERROR ;
1499 fnd_msg_pub.Count_And_Get
1500 (
1501 p_encoded => FND_API.G_TRUE,
1502 p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1509 fnd_msg_pub.Count_And_Get
1510 (
1511 p_encoded => FND_API.G_TRUE,
1512 p_count => x_msg_count,
1513 p_data => x_msg_data
1514 );
1515
1516 WHEN OTHERS THEN
1517
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1520 fnd_msg_pub.Count_And_Get
1521 (
1522 p_encoded => FND_API.G_TRUE,
1523 p_count => x_msg_count,
1524 p_data => x_msg_data
1525 );
1526 END Get_Partners_From_ELocation;
1527
1528 ---------------------------------------------------------------------
1529 -- FUNCTION
1530 -- address_to_geometry
1531 --
1532 -- PURPOSE
1533 -- Based on the address,city,state,zipcode, This function finnds geocode object for that address
1534 --
1535 -- PARAMETERS
1536 -- name,street,city,satte,zip_code
1537 -- returns geocode object
1538 --
1539 -- NOTES
1540 --
1541 ---------------------------------------------------------------------
1542
1543
1544 FUNCTION address_to_geometry( name VARCHAR2,
1545 street VARCHAR2,
1546 city VARCHAR2,
1547 state VARCHAR2,
1548 zip_code VARCHAR2)
1549 RETURN MDSYS.SDO_GEOMETRY
1550 AS
1551 latitude_loc NUMBER;
1552 latitude_str VARCHAR2(200);
1553 latitude NUMBER;
1554 longitude_loc NUMBER;
1555 longitude_str VARCHAR2(200);
1556 longitude NUMBER;
1557 xml_response VARCHAR2(4000);
1558 first_quote_loc NUMBER;
1559 second_quote_loc NUMBER;
1560 my_message VARCHAR2(4000);
1561 BEGIN
1562 -- Get xml geocode response string
1563 xml_response := address_to_geocodexml(
1564 name,
1565 street,
1566 city,
1567 state,
1568 zip_code);
1569
1570 -- Extract latitude
1571 latitude_loc := INSTR(xml_response, 'latitude', 1, 1);
1572 IF latitude_loc = 0 THEN
1573 --raise_application_error(-20512, 'latitude is missing');
1574 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1575 p_msg_name => 'PV_LOCATOR_CUST_ADDR_INVALID',
1576 p_token1 => null,
1577 p_token1_value => null,
1578 p_token2 => null,
1579 p_token2_value => null);
1580
1581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1582 END IF;
1583 first_quote_loc := INSTR(xml_response, '"', latitude_loc, 1);
1584 second_quote_loc := INSTR(xml_response, '"', latitude_loc, 2);
1585 latitude_str := SUBSTR(
1586 xml_response,
1587 first_quote_loc + 1,
1588 (second_quote_loc - 1)-(first_quote_loc + 1) + 1);
1589 latitude:= to_number(latitude_str,'99999999999999999999.99999999999999999999');
1590 /*SELECT latitude_str
1591 INTO latitude
1592 FROM DUAL;
1593 */
1594 -- Extract longitude
1595 longitude_loc := INSTR(xml_response, 'longitude', 1, 1);
1596 IF longitude_loc = 0 THEN
1597 --raise_application_error(-20513, 'longitude is missing');
1598 Set_Error_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1599 p_msg_name => 'PV_LOCATOR_CUST_ADDR_INVALID',
1600 p_token1 => null,
1601 p_token1_value => null,
1602 p_token2 => null,
1603 p_token2_value => null);
1604
1605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1606 END IF;
1607 first_quote_loc := INSTR(xml_response, '"', longitude_loc, 1);
1608 second_quote_loc := INSTR(xml_response, '"', longitude_loc, 2);
1609 longitude_str := SUBSTR(
1610 xml_response,
1611 first_quote_loc + 1,
1612 (second_quote_loc - 1)-(first_quote_loc + 1) + 1);
1613 longitude:= to_number(longitude_str,'99999999999999999999.99999999999999999999');
1614 /*SELECT longitude_str
1615 INTO longitude
1616 FROM DUAL;
1617 */
1618 RETURN MDSYS.SDO_GEOMETRY(2001,
1619 8307,
1620 MDSYS.SDO_POINT_TYPE(longitude,
1621 latitude,
1622 NULL),
1623 NULL,
1624 NULL);
1625 END address_to_geometry;
1626
1627 ---------------------------------------------------------------------
1628 -- FUNCTION
1629 -- ADDRESS_TO_GEOCODEXML
1630 --
1631 -- PURPOSE
1632 -- Based on the address,city,state,zipcode, This function construct an xml_string
1633 --makes a request to eLocation servlet and gets xml response that contains lattitude and longitude values in it
1634 --
1635 -- PARAMETERS
1636 -- name,street,city,satte,zip_code
1637 -- returns xml_string as varchar2
1638 --
1639 -- NOTES
1640 --
1641 ---------------------------------------------------------------------
1642
1643
1644 FUNCTION ADDRESS_TO_GEOCODEXML ( name VARCHAR2,
1645 street VARCHAR2,
1646 city VARCHAR2,
1647 state VARCHAR2,
1648 zip_code VARCHAR2)
1649 RETURN VARCHAR2
1650 AS
1651 /* geocoder_host VARCHAR2(128) := 'elocation.us.oracle.com';
1652 geocoder_path VARCHAR2(128) := '/servlets/lbs';
1653 */
1654
1655 us_form2 VARCHAR2(4000);
1656 xml_request VARCHAR2(4000);
1657 xml_response VARCHAR2(4000);
1658 url VARCHAR2(4000);
1659 content_type VARCHAR2(100);
1660 x_return_status VARCHAR2(1);
1661 x_msg_count NUMBER;
1662 x_msg_data VARCHAR2(4000);
1663
1664 BEGIN
1665 -- construct us_form2
1666 us_form2 := '<us_form2 ';
1667 IF NOT (name IS NULL) THEN
1668 us_form2 := us_form2 || 'name="' || name || '" ';
1669 END IF;
1670 IF NOT (street IS NULL) THEN
1671 us_form2 := us_form2 || 'street="' || street || '" ';
1672 END IF;
1673 IF NOT (city IS NULL) THEN
1674 us_form2 := us_form2 || 'city="' || city || '" ';
1675 END IF;
1676 IF NOT (state IS NULL) THEN
1677 us_form2 := us_form2 || 'state="' || state || '" ';
1678 END IF;
1679 IF NOT (zip_code IS NULL) THEN
1680 us_form2 := us_form2 || 'zip_code="' || zip_code || '" ';
1681 END IF;
1682 us_form2 := us_form2 || '/>';
1683
1684
1685 -- construct XML request
1686 xml_request := '<?xml version="1.0" standalone="yes" ?>' ||
1687 '<geocode_request vendor="elocation">' ||
1688 ' <address_list>' ||
1689 ' <input_location id="1">' ||
1690 ' <input_address match_mode=' ||
1691 ' "relax_street_type">' ||
1692 us_form2 ||
1693 ' </input_address>' ||
1694 ' </input_location>' ||
1695 ' </address_list>' ||
1696 '</geocode_request>';
1697 --dbms_output.put_line('Here is the xml_request: ');
1698 --dbms_output.put_line(xml_request);
1699 -- replace characters in xml_request with escapes
1700
1701 hz_http_pkg.post(
1702 doc => 'xml_request=' || xml_request,
1703 content_type => g_input_content_type,
1704 url => g_geocode_url,
1705 resp => xml_response,
1706 resp_content_type => content_type,
1707 proxyserver => g_proxy_server,
1708 proxyport => g_proxy_port,
1709 x_return_status => x_return_status,
1710 x_msg_count => x_msg_count,
1711 x_msg_data => x_msg_data
1712
1713 );
1714
1715
1716
1717 return xml_response;
1718
1719
1720 END ADDRESS_TO_GEOCODEXML;
1721
1722 --------------------------------------------------------------------------------
1723 -- CONSTANTS thar are being used in this function . getting these values from profiles
1724 --------------------------------------------------------------------------------
1725 /* PI number := 3.1415926535897932;
1726 TWOPI number := 2.0*PI;
1727 TORAD number := PI/180.0;
1728 EARTHRAD number := 6371007.000;*/ /* WGS-84 authalic radius in meters*/
1729 --------------------------------------------------------------------------------
1730 --
1731 -- This routine computes the distance between two point geometries
1732 -- using an authalic spherical approximation to the earth ellipsoid.
1733 -- mdsys.sdo_geometry geom1, The first geometry
1734 -- mdsys.sdo_geometry geom2, The second geometry
1735 -- Both geometries should be point geometries with geodetic longitude/latitude
1736 -- coordinates in degrees. Result is returned in meters.
1737 --------------------------------------------------------------------------------
1738 FUNCTION geocode_distance (geom1 MDSYS.SDO_GEOMETRY,
1739 geom2 MDSYS.SDO_GEOMETRY,
1740 distance_unit VARCHAR2)
1741 RETURN NUMBER IS
1742
1743 l_ct number;
1744 l_st number;
1745 l_cp number;
1746 l_sp number;
1747 l_p1x number;
1748 l_p1y number;
1749 l_p1z number;
1750 l_p2x number;
1751 l_p2y number;
1752 l_p2z number;
1753 l_dist number;
1754 l_PI number :=3.1415926535897932;-- to_number(g_pi_value); --value is 3.1415926535897932
1755 --l_TWOPI number := 2.0*l_PI;
1756 l_TORAD number := l_PI/180.0; --to_number(g_torad_degree); --grad degree is 180.0
1757 l_EARTHRAD number := 6371007.000; --to_number(g_earth_radious); -- earch radious is 6371007.000; /* WGS-84 authalic radius in meters*/
1758
1759 begin
1760
1761
1762
1763 if(geom1.sdo_point.y=geom2.sdo_point.y and geom1.sdo_point.x=geom2.sdo_point.x) then
1764 l_dist :=0;
1765 else
1766 l_ct := COS(geom1.sdo_point.y*l_TORAD);
1767 l_st := SIN(geom1.sdo_point.y*l_TORAD);
1768 l_cp := COS(geom1.sdo_point.x*l_TORAD);
1769 l_sp := SIN(geom1.sdo_point.x*l_TORAD);
1770 l_p1x := l_ct*l_cp;
1771 l_p1y := l_ct*l_sp;
1772 l_p1z := l_st;
1773
1774 l_ct := COS(geom2.sdo_point.y*l_TORAD);
1775 l_st := SIN(geom2.sdo_point.y*l_TORAD);
1776 l_cp := COS(geom2.sdo_point.x*l_TORAD);
1777 l_sp := SIN(geom2.sdo_point.x*l_TORAD);
1778 l_p2x := l_ct*l_cp;
1779 l_p2y := l_ct*l_sp;
1780 l_p2z := l_st;
1781
1782 l_dist := l_EARTHRAD*ACOS(l_p1x*l_p2x + l_p1y*l_p2y + l_p1z*l_p2z);
1783 end if;
1784 -- We got distance in meter. Now we need to convert based on teh distance unit
1785 if(distance_unit=g_distance_unit_mile) then
1786 l_dist:= l_dist*g_miles_per_meter;
1787 elsif(distance_unit=g_distance_unit_km) then
1788 l_dist:= l_dist/g_meters_per_km;
1789 elsif(distance_unit=g_distance_unit_meter) then
1790 l_dist:= l_dist;
1791 else
1792 l_dist:= l_dist*g_miles_per_meter;
1793 end if;
1794
1795 return l_dist;
1796 END geocode_distance;
1797
1798 --=============================================================================+
1799 --| Public Procedure |
1800 --| |
1801 --| Debug |
1802 --| |
1803 --| Parameters |
1804 --| IN |
1805 --| OUT |
1806 --| |
1807 --| |
1808 --| NOTES: |
1809 --| |
1810 --| HISTORY |
1811 --| |
1812 --==============================================================================
1813 PROCEDURE Debug(
1814 p_msg_string IN VARCHAR2
1815 )
1816 IS
1817
1818 BEGIN
1819
1820 IF
1821 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1822 THEN
1823 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1824 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
1825 FND_MSG_PUB.Add;
1826 END IF;
1827 END Debug;
1828 -- =================================End of Debug================================
1829
1830
1831
1832 --=============================================================================+
1833 --| Public Procedure |
1834 --| |
1835 --| Set_Error_Message |
1836 --| |
1837 --| Parameters |
1838 --| IN |
1839 --| OUT |
1840 --| |
1841 --| |
1842 --| NOTES: |
1843 --| |
1844 --| HISTORY |
1845 --| |
1846 --==============================================================================
1847 PROCEDURE Set_Error_Message(
1848 p_msg_level IN NUMBER,
1849 p_msg_name IN VARCHAR2,
1850 p_token1 IN VARCHAR2,
1851 p_token1_value IN VARCHAR2,
1852 p_token2 IN VARCHAR2 := NULL ,
1853 p_token2_value IN VARCHAR2 := NULL,
1854 p_token3 IN VARCHAR2 := NULL,
1855 p_token3_value IN VARCHAR2 := NULL
1856 )
1857 IS
1858 BEGIN
1859 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
1860 FND_MESSAGE.Set_Name('PV', p_msg_name);
1861
1862 IF (p_token1 IS NOT NULL) THEN
1863 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1864 END IF;
1865
1866 IF (p_token2 IS NOT NULL) THEN
1867 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1868 END IF;
1869
1870 IF (p_token3 IS NOT NULL) THEN
1871 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1872 END IF;
1873
1874 FND_MSG_PUB.Add;
1875 END IF;
1876 END Set_Error_Message;
1877 -- ==============================End of Set_Error_Message==============================
1878
1879 End PV_LOCATOR;
1880