1 PACKAGE BODY PV_Partner_Geo_Match_PVT as
2 /* $Header: pvxvpgmb.pls 115.2 2004/06/02 18:57:03 ktsao ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Partner_Geo_Match_PVT
7 -- Purpose
8 --
9 -- History
10 -- 02-JUN-2004 ktsao Fixed for sql repository issues
11 --
12 -- NOTE
13 --
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Partner_Geo_Match_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpgmb.pls';
19
20 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
21 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
22 --
23 TYPE address_rec_type IS RECORD
24 (
25 postal_code NUMBER
26 ,city NUMBER
27 ,state NUMBER
28 ,country NUMBER
29 );
30
31 -- Foreward Procedure Declarations
32 --
33
34 -- ==============================================================================
35 -- Start of Comments
36 -- ==============================================================================
37 -- API Name
38 -- Get_Matched_Geo_Hierarchy_Id
39 -- Type
40 -- Private
41 -- Pre-Req
42 --
43 -- Parameters
44 --
45 -- IN
46 -- p_api_version_number IN NUMBER Required
47 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
48 --
49 -- OUT
50 -- x_return_status OUT VARCHAR2
51 -- x_msg_count OUT NUMBER
52 -- x_msg_data OUT VARCHAR2
53 -- Version : Current version 1.0
54 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
55 -- and basic operation, developer must manually add parameters and business logic as necessary.
56 --
57 -- End of Comments
58 -- ==============================================================================
59
60 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
61 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
62 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
63
64 PROCEDURE Get_Matched_Geo_Hierarchy_Id(
65 p_api_version_number IN NUMBER
66 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
67
68 ,x_return_status OUT NOCOPY VARCHAR2
69 ,x_msg_count OUT NOCOPY NUMBER
70 ,x_msg_data OUT NOCOPY VARCHAR2
71
72 ,p_partner_party_id IN NUMBER
73 ,p_geo_hierarchy_id IN JTF_NUMBER_TABLE
74 ,x_geo_hierarchy_id OUT NOCOPY NUMBER
75 )
76
77 IS
78 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Matched_Geog_Hierarchy_Id';
79 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
80
81 CURSOR c_get_ordered_loc IS
82 select LH.location_hierarchy_id,
83 LH.LOCATION_TYPE_CODE TYPE_CODE,
84 DECODE(LH.LOCATION_TYPE_CODE,
85 'AREA1', LH.AREA1_CODE,
86 'AREA2',LH.AREA2_CODE,
87 'COUNTRY', LH.COUNTRY_CODE,
88 'CREGION', LH.COUNTRY_REGION_CODE,
89 'STATE', LH.STATE_CODE,
90 'SREGION', LH.STATE_REGION_CODE,
91 'CITY', LH.CITY_CODE,
92 'POSTAL_CODE', LH.POSTAL_CODE_START||'-'||LH.POSTAL_CODE_END) LOWEST_CODE,
93 DECODE(LH.LOCATION_TYPE_CODE, 'POSTAL_CODE',1,
94 'CITY', 2,
95 'SREGION', 3,
96 'STATE', 4,
97 'CREGION', 5,
98 'COUNTRY', 6,
99 'AREA2', 7,
100 'AREA1', 8) LOC_TYPE
101 from JTF_LOC_HIERARCHIES_VL LH
102 where LH.location_hierarchy_id in (
103 SELECT * FROM TABLE (CAST(p_geo_hierarchy_id AS JTF_NUMBER_TABLE))
104 )
105 and lh.location_type_code in ('AREA1', 'AREA2', 'COUNTRY', 'CREGION', 'STATE', 'SREGION', 'CITY', 'POSTAL_CODE')
106 order by loc_type;
107
108 CURSOR c_get_address(cv_partner_party_id NUMBER) IS
109 select L.postal_code, L.city, L.state, L.country
110 from hz_party_sites PS, hz_locations L
111 where PS.location_id = L.location_id
112 and PS.party_id = cv_partner_party_id
113 and PS.identifying_address_flag = 'Y';
114
115 CURSOR c_exist_postal (cv_postal_code VARCHAR2, cv_city_code VARCHAR2,
116 cv_state_code VARCHAR2, cv_country_code VARCHAR2) IS
117 select 'x'
118 from JTF_LOC_HIERARCHIES_VL LH
119 where LH.postal_code_start <= cv_postal_code
120 and LH.postal_code_end >= cv_postal_code
121 and LH.city_code = cv_city_code
122 and LH.state_code = cv_state_code
123 and LH.country_code = cv_country_code
124 and LH.location_type_code = 'POSTAL_CODE';
125
126 CURSOR c_exist_postal_sregion (cv_postal_code VARCHAR2, cv_city_code VARCHAR2,
127 cv_state_region_code VARCHAR2, cv_state_code VARCHAR2,
128 cv_country_code VARCHAR2) IS
129 select 'x'
130 from JTF_LOC_HIERARCHIES_VL LH
131 where LH.postal_code_start <= cv_postal_code
132 and LH.postal_code_end >= cv_postal_code
133 and LH.city_code = cv_city_code
134 and LH.state_region_code = cv_state_region_code
135 and LH.state_code = cv_state_code
136 and LH.country_code = cv_country_code
137 and LH.location_type_code = 'POSTAL_CODE';
138
139 CURSOR c_exist_postal_cregion (cv_postal_code VARCHAR2, cv_city_code VARCHAR2,
140 cv_state_code VARCHAR2, cv_country_region_code VARCHAR2,
141 cv_country_code VARCHAR2) IS
142 select 'x'
143 from JTF_LOC_HIERARCHIES_VL LH
144 where LH.postal_code_start <= cv_postal_code
145 and LH.postal_code_end >= cv_postal_code
146 and LH.city_code = cv_city_code
147 and LH.state_code = cv_state_code
148 and LH.country_region_code = cv_country_region_code
149 and LH.country_code = cv_country_code
150 and LH.location_type_code = 'POSTAL_CODE';
151
152 CURSOR c_exist_city (cv_city_code VARCHAR2, cv_state_code VARCHAR2,
153 cv_country_code VARCHAR2) IS
154 select 'x'
155 from JTF_LOC_HIERARCHIES_VL LH
156 where LH.city_code = cv_city_code
157 and LH.state_code = cv_state_code
158 and LH.country_code = cv_country_code
159 and LH.location_type_code = 'CITY';
160
161 CURSOR c_exist_city_sregion (cv_city_code VARCHAR2, cv_state_code VARCHAR2,
162 cv_state_region_code VARCHAR2, cv_country_code VARCHAR2) IS
163 select 'x'
164 from JTF_LOC_HIERARCHIES_VL LH
165 where LH.city_code = cv_city_code
166 and LH.state_region_code = cv_state_region_code
167 and LH.state_code = cv_state_code
168 and LH.country_code = cv_country_code
169 and LH.location_type_code = 'CITY';
170
171 CURSOR c_exist_city_cregion (cv_city_code VARCHAR2, cv_state_code VARCHAR2,
172 cv_country_code VARCHAR2, cv_country_region_code VARCHAR2) IS
173 select 'x'
174 from JTF_LOC_HIERARCHIES_VL LH
175 where LH.city_code = cv_city_code
176 and LH.state_code = cv_state_code
177 and LH.country_region_code = cv_country_region_code
178 and LH.country_code = cv_country_code
179 and LH.location_type_code = 'CITY';
180
181 CURSOR c_exist_state (cv_state_code VARCHAR2, cv_country_code VARCHAR2) IS
182 select 'x'
183 from JTF_LOC_HIERARCHIES_VL LH
184 where LH.state_code = cv_state_code
185 and LH.country_code = cv_country_code
186 and lh.location_type_code = 'STATE';
187
188 CURSOR c_exist_state_cregion (cv_state_code VARCHAR2, cv_country_code VARCHAR2,
189 cv_country_region_code VARCHAR2) IS
190 select 'x'
191 from JTF_LOC_HIERARCHIES_VL LH
192 where LH.state_code = cv_state_code
193 and LH.country_region_code = cv_country_region_code
194 and LH.country_code = cv_country_code
195 and LH.location_type_code = 'STATE';
196
197 CURSOR c_exist_country (cv_country_code VARCHAR2) IS
198 select 'x'
199 from JTF_LOC_HIERARCHIES_VL LH
200 where LH.country_code = cv_country_code
201 and LH.location_type_code = 'COUNTRY';
202
203 CURSOR c_exist_country_area2 (cv_country_code VARCHAR2, cv_area2_code VARCHAR2) IS
204 select 'x'
205 from JTF_LOC_HIERARCHIES_VL LH
206 where LH.country_code = cv_country_code
207 and LH.area2_code = cv_area2_code
208 and LH.location_type_code = 'COUNTRY';
209
210 CURSOR c_exist_country_area1 (cv_country_code VARCHAR2, cv_area1_code VARCHAR2) IS
211 select 'x'
212 from JTF_LOC_HIERARCHIES_VL LH
213 where LH.country_code = cv_country_code
214 and LH.area1_code = cv_area1_code
215 and LH.location_type_code = 'COUNTRY';
216
217 l_address_rec c_get_address%ROWTYPE;
218 l_exist_address_flag VARCHAR2(1) := 'Y';
219 l_ordered_loc_rec c_get_ordered_loc%ROWTYPE;
220 l_geo_hierarchy_id NUMBER;
221 l_postal_code_start VARCHAR2(6);
222 l_postal_code_end VARCHAR2(6);
223
224
225 BEGIN
226 -- Standard Start of API savepoint
227 SAVEPOINT Get_Matched_Geo_Hierarchy_Id;
228
229 -- Standard call to check for call compatibility.
230 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
231 p_api_version_number,
232 l_api_name,
233 G_PKG_NAME)
234 THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237
238 -- Initialize message list if p_init_msg_list is set to TRUE.
239 IF FND_API.to_Boolean( p_init_msg_list )
240 THEN
241 FND_MSG_PUB.initialize;
242 END IF;
243
244 -- Debug Message
245 IF (PV_DEBUG_HIGH_ON) THEN
246
247 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
248 END IF;
249
250
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 -- open c_get_address fetch into l_address_rec;
256 OPEN c_get_address(p_partner_party_id);
257 FETCH c_get_address INTO l_address_rec;
258 IF c_get_address%NOTFOUND THEN
259 IF (PV_DEBUG_HIGH_ON) THEN
260 PVX_UTILITY_PVT.debug_message('Address not found.');
261 END IF;
262 l_exist_address_flag := 'N';
263 END IF;
264 CLOSE c_get_address;
265
266 IF l_exist_address_flag = 'Y' THEN
267 <<ordered_loc_loop>>
268 FOR l_ordered_loc_rec IN c_get_ordered_loc
269 LOOP
270 IF l_ordered_loc_rec.type_code = 'POSTAL_CODE' THEN
271 -- If the code is the same, try to see if the partner address
272 -- exists in JTF_LOC_HIERARCHIES_VL.
273 SELECT substr(l_ordered_loc_rec.lowest_code, instr(l_ordered_loc_rec.lowest_code, '-')+1)
274 INTO l_postal_code_start
275 FROM dual;
276
277 SELECT substr(l_ordered_loc_rec.lowest_code, 1, instr(l_ordered_loc_rec.lowest_code, '-')-1)
278 INTO l_postal_code_end
279 FROM dual;
280
281 IF (PV_DEBUG_HIGH_ON) THEN
282 PVX_UTILITY_PVT.debug_message('l_postal_code_start = ' || l_postal_code_start);
283 END IF;
284
285 IF (PV_DEBUG_HIGH_ON) THEN
286 PVX_UTILITY_PVT.debug_message('l_postal_code_end = ' || l_postal_code_end);
287 END IF;
288
289 IF l_postal_code_start <= l_address_rec.postal_code AND
290 l_address_rec.postal_code >= l_postal_code_end THEN
291 -- call c_exist_postal
292 FOR x IN c_exist_postal(l_address_rec.postal_code,
293 l_address_rec.city,
294 l_address_rec.state,
295 l_address_rec.country) LOOP
296 -- if found, get l_ordered_loc_rec.location_hierarchy_id
297 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
298 EXIT ordered_loc_loop;
299 END LOOP;
300 END IF;
301
302 ELSIF l_ordered_loc_rec.type_code = 'CITY' THEN
303 IF l_ordered_loc_rec.lowest_code = l_address_rec.city THEN
304 -- If the code is the same, try to see if the partner address
305 -- exists in JTF_LOC_HIERARCHIES_VL.
306 -- call c_exist_city
307 FOR x IN c_exist_city(l_address_rec.city,
308 l_address_rec.state,
309 l_address_rec.country) LOOP
310 -- if found, get l_ordered_loc_rec.location_hierarchy_id
311 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
312 EXIT ordered_loc_loop;
313 END LOOP;
314 END IF;
315
316 ELSIF l_ordered_loc_rec.type_code = 'SREGION' THEN
317 -- Because partner's address information doesn't contain state region,
318 -- if there is a location hierarchy id defined for a state region, we need to add
319 -- state region as a WHERE condition
320 -- call c_exist_postal_sregion
321 FOR x IN c_exist_postal_sregion(l_address_rec.postal_code,
322 l_address_rec.city,
326 -- if found, get l_ordered_loc_rec.location_hierarchy_id
323 l_ordered_loc_rec.lowest_code,
324 l_address_rec.state,
325 l_address_rec.country) LOOP
327 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
328 EXIT ordered_loc_loop;
329 END LOOP;
330
331 -- If the l_address_rec.postal_code doesn't exist in JTF_LOC_HIERARCHIES_VL,
332 -- the city might exist. We drop the postal_code and try to match from city up.
333 -- call c_exist_city_sregion
334 FOR x IN c_exist_city_sregion(l_address_rec.city,
335 l_ordered_loc_rec.lowest_code,
336 l_address_rec.state,
337 l_address_rec.country) LOOP
338 -- if found, get l_ordered_loc_rec.location_hierarchy_id
339 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
340 EXIT ordered_loc_loop;
341 END LOOP;
342
343 ELSIF l_ordered_loc_rec.type_code = 'STATE' THEN
344 IF l_ordered_loc_rec.lowest_code = l_address_rec.state THEN
345 -- call c_exist_state
346 FOR x IN c_exist_state(l_address_rec.state,
347 l_address_rec.country) LOOP
348 -- if found, get l_ordered_loc_rec.location_hierarchy_id
349 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
350 EXIT ordered_loc_loop;
351 END LOOP;
352 END IF;
353
354 ELSIF l_ordered_loc_rec.type_code = 'CREGION' THEN
355 -- Because partner's address information doesn't contain country region,
356 -- if there is a location hierarchy id defined for a country region, we need to add
357 -- country region as a WHERE condition
358 -- call c_exist_postal_cregion
359 FOR x IN c_exist_postal_cregion(l_address_rec.postal_code,
360 l_address_rec.city,
361 l_address_rec.state,
362 l_ordered_loc_rec.lowest_code,
363 l_address_rec.country) LOOP
364 -- if found, get l_ordered_loc_rec.location_hierarchy_id
365 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
366 EXIT ordered_loc_loop;
367 END LOOP;
368
369 -- If the l_address_rec.postal_code doesn't exist in JTF_LOC_HIERARCHIES_VL,
370 -- the city might exist. We drop the postal_code and then try to
371 -- match from city up.
372 -- call c_exist_city_cregion
373 FOR x IN c_exist_city_cregion(l_address_rec.city,
374 l_address_rec.state,
375 l_ordered_loc_rec.lowest_code,
376 l_address_rec.country) LOOP
377 -- if found, get l_ordered_loc_rec.location_hierarchy_id
378 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
379 EXIT ordered_loc_loop;
380 END LOOP;
381
382 -- If the l_address_rec.postal_code doesn't exist in JTF_LOC_HIERARCHIES_VL,
383 -- the city might exist. We drop the postal_code and city and then try to
384 -- match from state up.
385 -- call c_exist_state_cregion
386 FOR x IN c_exist_state_cregion(l_address_rec.state,
387 l_ordered_loc_rec.lowest_code,
388 l_address_rec.country) LOOP
389 -- if found, get l_ordered_loc_rec.location_hierarchy_id
390 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
391 EXIT ordered_loc_loop;
392 END LOOP;
393
394 ELSIF l_ordered_loc_rec.type_code = 'COUNTRY' THEN
395 IF l_ordered_loc_rec.lowest_code = l_address_rec.country THEN
396 -- If the code is the same, try to see if the partner address
397 -- exists in JTF_LOC_HIERARCHIES_VL.
398 -- call c_exist_country
399 FOR x IN c_exist_country(l_address_rec.country) LOOP
400 -- if found, get l_ordered_loc_rec.location_hierarchy_id
401 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
402 EXIT ordered_loc_loop;
403 END LOOP;
404 END IF;
405
406 ELSIF l_ordered_loc_rec.type_code = 'AREA2' THEN
407 -- call c_exist_country_area2
408 FOR x IN c_exist_country_area2 (l_address_rec.country,
409 l_ordered_loc_rec.lowest_code) LOOP
410 -- if found, get l_ordered_loc_rec.location_hierarchy_id
411 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
412 EXIT ordered_loc_loop;
413 END LOOP;
414
415 ELSIF l_ordered_loc_rec.type_code = 'AREA1' THEN
416 -- call c_exist_country_area1
417 FOR x IN c_exist_country_area1 (l_address_rec.country,
418 l_ordered_loc_rec.lowest_code) LOOP
419 -- if found, get l_ordered_loc_rec.location_hierarchy_id
420 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
421 EXIT ordered_loc_loop;
422 END LOOP;
423 END IF;
424 END LOOP;
425 END IF;
426
427 x_geo_hierarchy_id := l_geo_hierarchy_id;
428
432
429 IF (PV_DEBUG_HIGH_ON) THEN
430 PVX_UTILITY_PVT.debug_message('Final used geo_hierarchy_id = ' || x_geo_hierarchy_id);
431 END IF;
433
434 -- Debug Message
435 IF (PV_DEBUG_HIGH_ON) THEN
436
437 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
438 END IF;
439
440 -- Standard call to get message count and if count is 1, get message info.
441 FND_MSG_PUB.Count_And_Get
442 ( p_encoded => FND_API.G_FALSE,
443 p_count => x_msg_count,
444 p_data => x_msg_data
445 );
446
447 EXCEPTION
448
449 WHEN Fnd_Api.G_EXC_ERROR THEN
450 ROLLBACK TO Get_Matched_Geo_Hierarchy_Id;
451 x_return_status := Fnd_Api.G_RET_STS_ERROR;
452
453 -- Standard call to get message count and if count=1, get the message
454 Fnd_Msg_Pub.Count_And_Get (
455 p_encoded => Fnd_Api.G_FALSE
456 ,p_count => x_msg_count
457 ,p_data => x_msg_data
458 );
459
460 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
461 ROLLBACK TO Get_Matched_Geo_Hierarchy_Id;
462 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
463 -- Standard call to get message count and if count=1, get the message
464 Fnd_Msg_Pub.Count_And_Get (
465 p_encoded => Fnd_Api.G_FALSE
466 ,p_count => x_msg_count
467 ,p_data => x_msg_data
468 );
469
470 WHEN OTHERS THEN
471 ROLLBACK TO Get_Matched_Geo_Hierarchy_Id;
472 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
473 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
474 THEN
475 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
476 END IF;
477 -- Standard call to get message count and if count=1, get the message
478 Fnd_Msg_Pub.Count_And_Get (
479 p_encoded => Fnd_Api.G_FALSE
480 ,p_count => x_msg_count
481 ,p_data => x_msg_data
482 );
483
484
485 END Get_Matched_Geo_Hierarchy_Id;
486
487 /**********
488 *
489 * Get_Matched_Geo_Id_By_Country will match only for locaton type above Country.
490 * That is, only match for Area 1, Area 2, and Country
491 *
492 ******/
493 PROCEDURE Get_Matched_Geo_Id_By_Country (
494 p_country IN VARCHAR2
495 ,p_geo_hierarchy_id IN JTF_NUMBER_TABLE
496 ,x_geo_hierarchy_id OUT NOCOPY NUMBER
497 )
498 IS
499 CURSOR c_get_ordered_loc IS
500 select LH.location_hierarchy_id,
501 LH.LOCATION_TYPE_CODE TYPE_CODE,
502 DECODE(LH.LOCATION_TYPE_CODE,
503 'AREA1', LH.AREA1_CODE,
504 'AREA2',LH.AREA2_CODE,
505 'COUNTRY', LH.COUNTRY_CODE) LOWEST_CODE,
506 DECODE(LH.LOCATION_TYPE_CODE,
507 'COUNTRY', 6,
508 'AREA2', 7,
509 'AREA1', 8) LOC_TYPE
510 from JTF_LOC_HIERARCHIES_VL LH
511 where LH.location_hierarchy_id in (
512 SELECT * FROM TABLE (CAST(p_geo_hierarchy_id AS JTF_NUMBER_TABLE))
513 )
514 and LH.LOCATION_TYPE_CODE in ('AREA1', 'AREA2', 'COUNTRY')
515 order by loc_type;
516
517 CURSOR c_exist_country (cv_country_code VARCHAR2) IS
518 select 'x'
519 from JTF_LOC_HIERARCHIES_VL LH
520 where LH.country_code = cv_country_code
521 and LH.location_type_code = 'COUNTRY';
522
523 CURSOR c_exist_country_area2 (cv_country_code VARCHAR2, cv_area2_code VARCHAR2) IS
524 select 'x'
525 from JTF_LOC_HIERARCHIES_VL LH
526 where LH.country_code = cv_country_code
527 and LH.area2_code = cv_area2_code
528 and LH.location_type_code = 'COUNTRY';
529
530 CURSOR c_exist_country_area1 (cv_country_code VARCHAR2, cv_area1_code VARCHAR2) IS
531 select 'x'
532 from JTF_LOC_HIERARCHIES_VL LH
533 where LH.country_code = cv_country_code
534 and LH.area1_code = cv_area1_code
535 and LH.location_type_code = 'COUNTRY';
536
537 l_ordered_loc_rec c_get_ordered_loc%ROWTYPE;
538 l_geo_hierarchy_id NUMBER;
539
540
541 BEGIN
542 <<ordered_loc_loop>>
543 FOR l_ordered_loc_rec IN c_get_ordered_loc
544 LOOP
545 IF l_ordered_loc_rec.type_code = 'COUNTRY' THEN
546 IF l_ordered_loc_rec.lowest_code = p_country THEN
547 -- If the code is the same, try to see if the partner address
548 -- exists in JTF_LOC_HIERARCHIES_VL.
549 -- call c_exist_country
550 FOR x IN c_exist_country(p_country) LOOP
551 -- if found, get l_ordered_loc_rec.location_hierarchy_id
552 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
553 EXIT ordered_loc_loop;
554 END LOOP;
555 END IF;
556
557 ELSIF l_ordered_loc_rec.type_code = 'AREA2' THEN
558 -- call c_exist_country_area2
559 FOR x IN c_exist_country_area2 (p_country,
560 l_ordered_loc_rec.lowest_code) LOOP
561 -- if found, get l_ordered_loc_rec.location_hierarchy_id
562 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
563 EXIT ordered_loc_loop;
564 END LOOP;
565
566 ELSIF l_ordered_loc_rec.type_code = 'AREA1' THEN
567 -- call c_exist_country_area1
568 FOR x IN c_exist_country_area1 (p_country,
569 l_ordered_loc_rec.lowest_code) LOOP
570 -- if found, get l_ordered_loc_rec.location_hierarchy_id
574 END IF;
571 l_geo_hierarchy_id := l_ordered_loc_rec.location_hierarchy_id;
572 EXIT ordered_loc_loop;
573 END LOOP;
575 END LOOP;
576
577 x_geo_hierarchy_id := l_geo_hierarchy_id;
578
579 IF (PV_DEBUG_HIGH_ON) THEN
580 PVX_UTILITY_PVT.debug_message('Final used geo_hierarchy_id = ' || x_geo_hierarchy_id);
581 END IF;
582 END Get_Matched_Geo_Id_By_Country;
583
584
585 /**********
586 *
587 * Get_Ptnr_Matched_Geo_Id will match only for locaton type above
588 * Country by passing in the partner id.
589 * That is, only match for Area 1, Area 2, and Country
590 *
591 ******/
592 PROCEDURE Get_Ptnr_Matched_Geo_Id (
593 p_api_version_number IN NUMBER
594 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
595
596 ,x_return_status OUT NOCOPY VARCHAR2
597 ,x_msg_count OUT NOCOPY NUMBER
598 ,x_msg_data OUT NOCOPY VARCHAR2
599
600 ,p_partner_id IN NUMBER
601 ,p_geo_hierarchy_id IN JTF_NUMBER_TABLE
602 ,x_geo_hierarchy_id OUT NOCOPY NUMBER
603 )
604
605 IS
606 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Ptnr_Matched_Geo_Id';
607 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
608
609 CURSOR c_get_address(cv_partner_id NUMBER) IS
610 select L.country
611 from hz_party_sites PS, hz_locations L, pv_partner_profiles PP
612 where PS.location_id = L.location_id
613 and PP.partner_party_id = PS.party_id
614 and PP.partner_id = cv_partner_id
615 and PS.identifying_address_flag = 'Y';
616
617 BEGIN
618 -- Standard Start of API savepoint
619 SAVEPOINT Get_Ptnr_Matched_Geo_Id;
620
621 -- Standard call to check for call compatibility.
622 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
623 p_api_version_number,
624 l_api_name,
625 G_PKG_NAME)
626 THEN
627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628 END IF;
629
630 -- Initialize message list if p_init_msg_list is set to TRUE.
631 IF FND_API.to_Boolean( p_init_msg_list )
632 THEN
633 FND_MSG_PUB.initialize;
634 END IF;
635
636 -- Debug Message
637 IF (PV_DEBUG_HIGH_ON) THEN
638
639 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
640 END IF;
641
642 -- Initialize API return status to SUCCESS
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645 FOR x in c_get_address(p_partner_id)
646 LOOP
647 Get_Matched_Geo_Id_By_Country (
648 p_country => x.country
649 ,p_geo_hierarchy_id => p_geo_hierarchy_id
650 ,x_geo_hierarchy_id => x_geo_hierarchy_id
651 );
652 END LOOP;
653
654 -- Standard call to get message count and if count=1, get the message
655 Fnd_Msg_Pub.Count_And_Get (
656 p_encoded => Fnd_Api.G_FALSE
657 ,p_count => x_msg_count
658 ,p_data => x_msg_data
659 );
660
661 EXCEPTION
662
663 WHEN OTHERS THEN
664 ROLLBACK TO Get_Ptnr_Matched_Geo_Id;
665 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
666 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
667 THEN
668 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
669 END IF;
670 -- Standard call to get message count and if count=1, get the message
671 Fnd_Msg_Pub.Count_And_Get (
672 p_encoded => Fnd_Api.G_FALSE
673 ,p_count => x_msg_count
674 ,p_data => x_msg_data
675 );
676
677 END Get_Ptnr_Matched_Geo_Id;
678
679 /**********
680 *
681 * Get_Ptnr_Org_Matched_Geo_Id will match only for locaton type above
682 * Country by passing the partner org id.
683 * That is, only match for Area 1, Area 2, and Country
684 *
685 ******/
686 PROCEDURE Get_Ptnr_Org_Matched_Geo_Id (
687 p_api_version_number IN NUMBER
688 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
689
690 ,x_return_status OUT NOCOPY VARCHAR2
691 ,x_msg_count OUT NOCOPY NUMBER
692 ,x_msg_data OUT NOCOPY VARCHAR2
693
694 ,p_party_id IN NUMBER
695 ,p_geo_hierarchy_id IN JTF_NUMBER_TABLE
696 ,x_geo_hierarchy_id OUT NOCOPY NUMBER
697 )
698
699 IS
700 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Ptnr_Org_Matched_Geo_Id';
701 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
702
703 CURSOR c_get_address(cv_party_id NUMBER) IS
704 select L.country
705 from hz_party_sites PS, hz_locations L
706 where PS.location_id = L.location_id
707 and PS.party_id = cv_party_id
708 and PS.identifying_address_flag = 'Y';
709
710 BEGIN
711 -- Standard Start of API savepoint
712 SAVEPOINT Get_Ptnr_Org_Matched_Geo_Id;
713
714 -- Standard call to check for call compatibility.
715 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
716 p_api_version_number,
717 l_api_name,
718 G_PKG_NAME)
719 THEN
720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
721 END IF;
722
723 -- Initialize message list if p_init_msg_list is set to TRUE.
724 IF FND_API.to_Boolean( p_init_msg_list )
725 THEN
726 FND_MSG_PUB.initialize;
727 END IF;
728
729 -- Debug Message
730 IF (PV_DEBUG_HIGH_ON) THEN
731
732 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
733 END IF;
734
735 -- Initialize API return status to SUCCESS
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 FOR x in c_get_address(p_party_id)
739 LOOP
740 Get_Matched_Geo_Id_By_Country (
741 p_country => x.country
742 ,p_geo_hierarchy_id => p_geo_hierarchy_id
743 ,x_geo_hierarchy_id => x_geo_hierarchy_id
744 );
745 END LOOP;
746
747 -- Standard call to get message count and if count=1, get the message
748 Fnd_Msg_Pub.Count_And_Get (
749 p_encoded => Fnd_Api.G_FALSE
750 ,p_count => x_msg_count
751 ,p_data => x_msg_data
752 );
753
754 EXCEPTION
755
756 WHEN OTHERS THEN
757 ROLLBACK TO Get_Ptnr_Org_Matched_Geo_Id;
758 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
759 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
760 THEN
761 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
762 END IF;
763 -- Standard call to get message count and if count=1, get the message
764 Fnd_Msg_Pub.Count_And_Get (
765 p_encoded => Fnd_Api.G_FALSE
766 ,p_count => x_msg_count
767 ,p_data => x_msg_data
768 );
769
770 END Get_Ptnr_Org_Matched_Geo_Id;
771
772
773 END PV_Partner_Geo_Match_PVT;
[Home] [Help]