1 PACKAGE BODY pa_location_utils AS
2 -- $Header: PALOUTLB.pls 120.2 2005/11/03 12:04:49 ramurthy noship $
3 -- PROCEDURE
4 -- Check_Country_Name_Or_Code
5 -- PURPOSE
6 -- This procedure does the following
7 -- If country name is passed converts it to the code
8 -- If code is passed, based on the check_id_flag validates it
9 -- HISTORY
10 -- 23-JUN-2000 R. Krishnamurthy Created
11 -- 13-APR-2001 Ranga Iyengar Modified Check_Country_Name_Or_Code api to validate
12 -- LOV fileds bug fix : 1364336
13 -- 04-APR-2002 adabdull Return success status for Check_Location_Exists
14 -- when no_data_found (bug2304360)
15 ----------------------------------------------------------------------------------
16 -- Start of comments
17 --
18 -- API Name : pa_location_utils.Check_Country_Name_Or_Code
19 --
20 -- Type : Public
21 --
22 -- Pre-reqs : None
23 --
24 -- Function : This procedure does the the following
25 -- 1>. If a country name is passed it derrives the country code.
26 -- 2>. If a country code is passed, the code is validated based on
27 -- the check id flag.
28 --
29 -- If a valid country is not found an invalid country error code
30 -- is returned. Also is there are more that one records retrieved
31 -- then it will flag an error of ambigous country.
32 --
33 --
34 -- Parameters :
35 -- p_country_code IN VARCHAR2
36 -- p_country_name IN VARCHAR2
37 -- p_check_id_flag IN VARCHAR2
38 -- x_country_code OUT VARCHAR2
39 -- x_return_status OUT VARCHAR2
40 -- x_error_message_code OUT VARCHAR2
41
42 -- Version : Initial version 115.0
43 --
44 -- End of comments
45 ----------------------------------------------------------------------------------
46
47 PROCEDURE Check_Country_Name_Or_Code
48 ( p_country_code IN VARCHAR2
49 ,p_country_name IN VARCHAR2
50 ,p_check_id_flag IN VARCHAR2 default 'A'
51 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
52 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
53 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
54
55
56 l_current_id VARCHAR2(100);
57 l_id_found_flag VARCHAR2(1):= 'N';
58 l_num_ids NUMBER := 0;
59 CURSOR c_ids IS
60 SELECT territory_code
61 FROM fnd_territories_vl
62 WHERE territory_short_name = p_country_name;
63
64
65
66 BEGIN
67 IF ((p_country_code IS NOT NULL) AND
68 (p_country_code <> FND_API.G_MISS_CHAR)) THEN
69 IF p_check_id_flag = 'Y' THEN
70 SELECT territory_code
71 INTO x_country_code
72 FROM fnd_territories
73 WHERE territory_code = p_country_code;
74 ---------------------------------------------------
75 -- Added the following code to fix the bug : 1364336
76 -- to validate the LOV based on the user inputs
77 ---------------------------------------------------
78 ELSIF (p_check_id_flag = 'N') THEN
79 -- No ID validation necessary
80 x_country_code := p_country_code;
81
82 ELSIF (p_check_id_flag = 'A') THEN
83
84 IF (p_country_name IS NULL) THEN
85 -- Return a null ID since the name is null.
86 x_country_code := NULL;
87
88 ELSE
89 -- Find the ID which matches the Name passed
90 OPEN c_ids;
91 LOOP
92 FETCH c_ids INTO l_current_id;
93 EXIT WHEN c_ids%NOTFOUND;
94 IF (l_current_id = p_country_code) THEN
95 l_id_found_flag := 'Y';
96 x_country_code := p_country_code;
97 END IF;
98 END LOOP;
99 l_num_ids := c_ids%ROWCOUNT;
100 CLOSE c_ids;
101
102 IF (l_num_ids = 0) THEN
103 -- No IDs for name
104 RAISE NO_DATA_FOUND;
105 ELSIF (l_num_ids = 1) THEN
106 -- Since there is only one ID for the name use it.
107 x_country_code := l_current_id;
108 ELSIF (l_num_ids > 0 OR l_id_found_flag = 'N') THEN
109 -- More than one ID for the name and none of the IDs matched
110 -- the ID passed in.
111 RAISE TOO_MANY_ROWS;
112 END IF;
113 END IF; -- end if for country name
114 --x_country_code := p_country_code;
115 END IF; -- end if for check id flag
116 ELSE
117 If p_country_name is NOT NULL then
118 SELECT territory_code
119 INTO x_country_code
120 FROM fnd_territories_vl
121 WHERE territory_short_name = p_country_name;
122
123 Else
124 x_country_code := NULL;
125 End if;
126 END IF;
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 x_return_status := FND_API.G_RET_STS_ERROR;
132 x_error_message_code := 'PA_COUNTRY_INVALID';
133 x_country_code := NULL;
134 WHEN TOO_MANY_ROWS THEN
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 x_error_message_code := 'PA_COUNTRY_AMBIGOUS';
137 x_country_code := NULL;
138 WHEN OTHERS THEN
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
140 x_country_code := NULL;
141 RAISE ;
142
143 END Check_Country_Name_Or_Code;
144
145 ----------------------------------------------------------------------------------
146 -- Start of comments
147 --
148 -- API Name : pa_location_utils.Get_ORG_Location_Details
149 --
150 -- Type : Public
151 --
152 -- Pre-reqs : None
153 --
154 -- Procedure : This procedure accepts the organization id and returns the following
155 -- 1>. Country name
156 -- 2>. City
157 -- 3>. Region/State
158 -- 4>. Country Code
159 --
160 -- It is used to get the location of an organization.
161 --
162 -- Parameters :
163 -- p_organization_id IN NUMBER
164 -- x_country_name OUT VARCHAR2
165 -- x_city OUT VARCHAR2
166 -- x_region OUT VARCHAR2
167 -- x_country_code OUT VARCHAR2
168 -- x_return_status OUT VARCHAR2
169 -- x_error_message_code OUT VARCHAR2
170
171 -- Version : Initial version 115.0
172 --
173 -- End of comments
174 ----------------------------------------------------------------------------------
175 procedure Get_ORG_Location_Details
176 ( p_organization_id IN NUMBER
177 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
178 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
179 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
180 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
181 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
182 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
183
184 BEGIN
185
186 /* Bug 4092701 - Commented the prm_licensed check */
187 /* IF PA_INSTALL.IS_PRM_LICENSED = 'Y' THEN */
188
189 SELECT l.country,
190 t.territory_short_name,
191 l.town_or_city,
192 decode(l.region_2, NULL, l.region_1, l.region_2)
193 INTO x_country_code,
194 x_country_name,
195 x_city,
196 x_region
197 FROM hr_all_organization_units o, -- Bug 4684196
198 -- hr_organization_units o,
199 hr_locations_all l,
200 fnd_territories_vl t
201 WHERE t.territory_code = l.country
202 AND o.location_id = l.location_id
203 AND o.organization_id = p_organization_id ;
204
205 IF x_country_code IS NULL THEN
206
207 RAISE NO_DATA_FOUND ;
208 ELSE
209
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211
212 END IF ;
213
214 /* ELSE
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 END IF ; */
218
219
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 /* Commented the below two lines for bug 2686227 */
223 /* x_return_status := FND_API.G_RET_STS_ERROR;
224 x_error_message_code := 'PA_ORGANIZATION_INVALID';
225 */
226 NULL;
227 WHEN TOO_MANY_ROWS THEN
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 x_error_message_code := 'PA_ORGANIZATION_AMBIGOUS';
230 WHEN OTHERS THEN
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
232 RAISE ;
233
234 END Get_ORG_Location_Details;
235
236 ----------------------------------------------------------------------------------
237 -- Start of comments
238 --
239 -- API Name : pa_location_utils.Get_EMP_Location_Details
240 --
241 -- Type : Public
242 --
243 -- Pre-reqs : None
244 --
245 -- Procedure : This procedure accepts the person id and returns the following
246 -- 1>. Country name
247 -- 2>. City
248 -- 3>. Region/State
249 -- 4>. Country Code
250 --
251 -- It is used to get the primary location of an person.
252 --
253 --
254 --
255 -- Parameters :
256 -- p_person_id IN NUMBER
257 -- x_country_name OUT VARCHAR2
258 -- x_city OUT VARCHAR2
259 -- x_region OUT VARCHAR2
260 -- x_country_code OUT VARCHAR2
261 -- x_return_status OUT VARCHAR2
262 -- x_error_message_code OUT VARCHAR2
263
264 -- Version : Initial version 115.0
265 --
266 -- End of comments
267 ----------------------------------------------------------------------------------
268 procedure Get_EMP_Location_Details
269 ( p_person_id IN NUMBER
270 ,p_assign_date IN DATE
271 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
272 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
273 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
274 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
275 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
276 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
277
278 BEGIN
279
280 /* Bug 4092701 - Commented the prm_licensed check */
281 /* IF PA_INSTALL.IS_PRM_LICENSED = 'Y' THEN */
282
283 SELECT l.country,
284 t.territory_short_name,
285 l.town_or_city,
286 decode(l.region_2, NULL, l.region_1, l.region_2)
287 INTO x_country_code,
288 x_country_name,
289 x_city,
290 x_region
291 FROM per_addresses l,
292 fnd_territories_vl t
293 WHERE t.territory_code = l.country
294 AND l.primary_flag = 'Y'
295 AND p_assign_date between l.DATE_FROM and nvl(l.DATE_TO, p_assign_date)
296 AND l.person_id = p_person_id ;
297
298
299 /* ELSE */
300
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 /* END IF ; */
304
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 NULL ;
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309 WHEN TOO_MANY_ROWS THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 x_error_message_code := 'PA_EMP_LOCATION_AMBIGOUS';
312 WHEN OTHERS THEN
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314 RAISE ;
315
316 END Get_EMP_Location_Details;
317
318 ----------------------------------------------------------------------------------
319 -- Start of comments
320 --
321 -- API Name : pa_location_utils.Get_PA_Location_Details
322 --
323 -- Type : Public
324 --
325 -- Pre-reqs : None
326 --
327 -- Procedure : This procedure accepts the location id and returns the following
328 -- 1>. Country name
329 -- 2>. City
330 -- 3>. Region/State
331 -- 4>. Country Code
332 --
333 -- It is used to get the project location.
334 --
335 --
336 --
337 -- Parameters :
338 -- p_location_id IN NUMBER
339 -- x_country_name OUT VARCHAR2
340 -- x_city OUT VARCHAR2
341 -- x_region OUT VARCHAR2
342 -- x_country_code OUT VARCHAR2
343 -- x_return_status OUT VARCHAR2
344 -- x_error_message_code OUT VARCHAR2
345
346 -- Version : Initial version 11.0
347 --
348 -- End of comments
349 ----------------------------------------------------------------------------------
350 PROCEDURE Get_PA_Location_Details
351 ( p_location_id IN NUMBER
352 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
353 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
354 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
355 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
356 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
357 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
358
359
360 BEGIN
361
362 /* Bug 4092701 - Commented the prm_licensed check */
363 /* IF PA_INSTALL.IS_PRM_LICENSED = 'Y' THEN */
364
365 SELECT l.country_code,
366 t.territory_short_name,
367 l.city,
368 l.region
369 INTO x_country_code,
370 x_country_name,
371 x_city,
372 x_region
373 FROM pa_locations l,
374 fnd_territories_vl t
375 WHERE t.territory_code = l.country_code
376 AND l.location_id = p_location_id ;
377
378 /* END IF ; */
382 EXCEPTION
379 x_return_status := FND_API.G_RET_STS_SUCCESS;
380
381
383 WHEN NO_DATA_FOUND THEN
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 x_error_message_code := 'PA_PROJ_LOCATION_INVALID';
386 WHEN TOO_MANY_ROWS THEN
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 x_error_message_code := 'PA_PROJ_LOCATION_AMBIGOUS';
389 WHEN OTHERS THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
391 RAISE ;
392
393 END Get_PA_Location_Details;
394
395 ----------------------------------------------------------------------------------
396 -- Start of comments
397 --
398 -- API Name : pa_location_utils.Check_Location_Exists
399 --
400 -- Type : Public
401 --
402 -- Pre-reqs : None
403 --
404 -- Procedure : This procedure is used to check if a valid project location
405 -- for that combination of country, city and region.
406 --
407 --
408 --
409 -- Parameters :
410 -- p_country_code IN VARCHAR2
411 -- p_city IN VARCHAR2
412 -- p_region IN VARCHAR2
413 -- x_location_id OUT NUMBER
414 -- x_return_status OUT VARCHAR2
415
416 -- Version : Initial version 115.0
417 --
418 -- End of comments
419 ----------------------------------------------------------------------------------
420 PROCEDURE Check_Location_Exists( p_country_code IN VARCHAR2
421 ,p_city IN VARCHAR2
422 ,p_region IN VARCHAR2
423 ,x_location_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
424 ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
425 BEGIN
426
427 SELECT l.location_id
428 INTO x_location_id
429 FROM pa_locations l
430 WHERE ( ( l.city = p_city)
431 OR (l.city IS NULL AND p_city IS NULL))
432 AND ( (l.region = p_region)
433 OR (l.region IS NULL AND p_region IS NULL))
434 AND l.country_code = p_country_code ;
435
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 x_location_id := NULL ;
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442 WHEN OTHERS THEN
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
444 RAISE ;
445
446 END Check_Location_Exists;
447
448 ----------------------------------------------------------------------------------
449 -- Start of comments
450 --
451 -- API Name : pa_location_utils.Get_Location
452 --
453 -- Type : Public
454 --
455 -- Pre-reqs : None
456 --
457 -- Procedure : This procedure accepts country code, city and region and input
458 -- parameters, checks if the location exists and returns the location
459 -- id if the location exists. If the location does not exist a new
460 -- location is created with the country, city and region and the new
461 -- location id is returned.
462 --
463 --
464 -- Parameters :
465 -- p_country_code IN VARCHAR2
466 -- p_city IN VARCHAR2
467 -- p_region IN VARCHAR2
468 -- x_location_id OUT NUMBER
469 -- x_return_status OUT VARCHAR2
470
471 -- Version : Initial version 115.0
472 --
473 -- End of comments
474 ----------------------------------------------------------------------------------
475 PROCEDURE Get_Location ( p_country_code IN VARCHAR2
476 ,p_city IN VARCHAR2
477 ,p_region IN VARCHAR2
478 ,x_location_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
479 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
480 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
481
482 l_location_id NUMBER;
483 l_return_status VARCHAR2(30);
484 l_created_by NUMBER := fnd_global.user_id ;
485 l_login_id NUMBER := fnd_global.login_id ;
486 l_ROW_ID ROWID ;
487
488 BEGIN
489
490 /* Bug 4092701 - Commented the prm_licensed check */
491 /* IF PA_INSTALL.IS_PRM_LICENSED = 'Y' THEN */
492
493 IF p_country_code is NULL THEN
494
495 x_error_message_code := 'PA_PROJ_COUNTRY_NULL' ;
496
497 ELSE
498
499
500 Check_Location_Exists ( p_country_code => p_country_code
501 , p_city => p_city
502 , p_region => p_region
503 , x_location_id => l_location_id
504 , x_return_status => l_return_status );
505
506 IF l_location_id IS NULL THEN
507
508 PA_LOCATIONS_PKG.INSERT_ROW (
509 p_CITY => p_city
510 , p_REGION => p_region
511 , p_COUNTRY_CODE => p_country_code
512 , p_CREATION_DATE => sysdate
513 , p_CREATED_BY => l_created_by
514 , p_LAST_UPDATE_DATE => sysdate
515 , p_LAST_UPDATED_BY => l_login_id
516 , p_LAST_UPDATE_LOGIN => l_login_id
517 , X_ROWID => l_ROW_ID
518 , X_LOCATION_ID => l_location_id );
519
520 END IF;
521
522 x_location_id := l_location_id ;
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524
525 END IF;
526 /* ELSE
527 x_return_status := FND_API.G_RET_STS_SUCCESS;
528
529 END IF ; */
530
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
535 RAISE ;
536
537 END Get_Location;
538
539 ----------------------------------------------------------------------------------
540 -- Start of comments
541 --
542 -- API Name : pa_location_utils.Get_Country_Code_Name
543 --
544 -- Procedure : This procedure returns country code and count name
545 -- from location id.
546 --
547 -- Parameters :
548 -- p_location IN NUMBER
549 -- x_country_code IN VARCHAR2
550 -- x_country_name IN VARCHAR2
551 --
552 -- End of comments
553 -------------------------------------------------------------------------------
554 Procedure Get_Country_Code_Name(p_location_id IN NUMBER,
555 x_country_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
556 x_country_name OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
557 IS
558
559 BEGIN
560 SELECT l.country_code,
561 t.territory_short_name
562 INTO x_country_code,
563 x_country_name
564 FROM pa_locations l,
565 fnd_territories_vl t
566 WHERE t.territory_code = l.country_code
567 AND l.location_id = p_location_id;
568
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571 x_country_code := '';
572 x_country_name := '';
573 WHEN OTHERS THEN
574 x_country_code := '';
575 x_country_name := '';
576 END Get_Country_Code_Name;
577
578
579 END pa_location_utils ;
[Home] [Help]