[Home] [Help]
PACKAGE BODY: APPS.PO_HR_LOCATION
Source
1 PACKAGE BODY PO_HR_LOCATION AS
2 /* $Header: POXPRPOB.pls 120.6.12020000.2 2013/02/10 14:40:11 vegajula ship $*/
3
4 g_address_details PO_HR_LOCATION.address; -- Its a PL/SQL table of po_address_details_gt rowtype
5
6 g_addr_prompt_query PO_HR_LOCATION.addr_prompt_query; --PL/SQL table for storing style code, query and prompt list.
7
8 -- Debugging booleans used to bypass logging when turned off
9 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12 -- Logging constants
13 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_HR_LOCATION';
14 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
15 -- <R12 PO OTM Integration START>
16 /********************************************************************************
17 ** Procedure get_address
18 ********************************************************************************/
19 PROCEDURE get_address
20 ( p_location_id IN NUMBER
21 , x_address_line_1 OUT NOCOPY VARCHAR2
22 , x_address_line_2 OUT NOCOPY VARCHAR2
23 , x_address_line_3 OUT NOCOPY VARCHAR2
24 , x_town_or_city OUT NOCOPY VARCHAR2
25 , x_state_or_province OUT NOCOPY VARCHAR2
26 , x_postal_code OUT NOCOPY VARCHAR2
27 , x_territory_short_name OUT NOCOPY VARCHAR2
28 , x_iso_territory_code OUT NOCOPY VARCHAR2
29 )
30 IS
31 l_temp_location_id NUMBER;
32
33 d_progress VARCHAR2(3);
34 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_ADDRESS';
35
36 BEGIN
37
38 d_progress := '000';
39
40 IF (g_debug_stmt) THEN
41 PO_DEBUG.debug_begin(d_module);
42 PO_DEBUG.debug_var(d_module, d_progress, 'p_location_id', p_location_id);
43 END IF;
44
45 d_progress := '100';
46
47 -- See if this location is in HR_LOCATIONS or if it
48 -- is a drop ship location
49 BEGIN
50 SELECT hrl.location_id
51 INTO l_temp_location_id
52 FROM hr_locations hrl
53 WHERE hrl.location_id = p_location_id;
54
55 d_progress := '110';
56
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 d_progress := '120';
60 l_temp_location_id := NULL;
61 END;
62
63 IF (g_debug_stmt) THEN
64 PO_DEBUG.debug_var(d_module, d_progress, 'l_temp_location_id', l_temp_location_id);
65 END IF;
66
67 d_progress := '130';
68
69 IF (l_temp_location_id IS NOT NULL) THEN
70 -- Regular HR location
71 d_progress := '200';
72
73 BEGIN
74 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
75 SELECT hrl.address_line_1
76 , hrl.address_line_2
77 , hrl.address_line_3
78 --, hrl.town_or_city -- bug#15993315 commented to take town_or_city from fnd_lookup_values
79 , Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city)
80 , NVL(DECODE(hrl.region_1,NULL,hrl.region_2,
81 DECODE(flv1.meaning,NULL,
82 DECODE(flv2.meaning,NULL,flv3.meaning,flv2.lookup_code)
83 , flv1.lookup_code)), hrl.region_2)
84 , hrl.postal_code
85 , NVL(ftel.territory_short_name,hrl.country)
86 , fte.iso_territory_code
87 INTO x_address_line_1
88 , x_address_line_2
89 , x_address_line_3
90 , x_town_or_city
91 , x_state_or_province
92 , x_postal_code
93 , x_territory_short_name
94 , x_iso_territory_code
95 FROM hr_locations_all hrl
96 , fnd_territories fte
97 , fnd_territories_tl ftel
98 , fnd_lookup_values flv1
99 , fnd_lookup_values flv2
100 , fnd_lookup_values flv3
101 , fnd_lookup_values flv4
102 WHERE hrl.location_id = p_location_id
103 AND hrl.country = fte.territory_code (+)
104 AND hrl.country = ftel.territory_code (+)
105 AND DECODE(ftel.territory_code, NULL, '1', ftel.language) =
106 DECODE(ftel.territory_code, NULL, '1', USERENV('LANG'))
107 AND hrl.region_1 = flv1.lookup_code (+)
108 AND hrl.country || '_PROVINCE' = flv1.lookup_type (+)
109 AND DECODE(flv1.lookup_code, NULL, '1', flv1.security_group_id) =
110 DECODE(flv1.lookup_code, NULL, '1',
111 FND_GLOBAL.lookup_security_group(flv1.lookup_type, flv1.view_application_id))
112 AND DECODE(flv1.lookup_code, NULL, '1', flv1.view_application_id) =
113 DECODE(flv1.lookup_code, NULL, '1', 3)
114 AND DECODE(flv1.lookup_code, NULL, '1', flv1.language) =
115 DECODE(flv1.lookup_code, NULL, '1', USERENV('LANG'))
116 AND hrl.region_2 = flv2.lookup_code (+)
117 AND hrl.country || '_STATE' = flv2.lookup_type (+)
118 AND DECODE(flv2.lookup_code, NULL, '1', flv2.security_group_id) =
119 DECODE(flv2.lookup_code, NULL, '1',
120 FND_GLOBAL.lookup_security_group(flv2.lookup_type, flv2.view_application_id))
121 AND DECODE(flv2.lookup_code, NULL, '1', flv2.view_application_id) =
122 DECODE(flv2.lookup_code, NULL, '1', 3)
123 AND DECODE(flv2.lookup_code, NULL, '1', flv2.language) =
124 DECODE(flv2.lookup_code, NULL, '1', USERENV('LANG'))
125 AND hrl.region_1 = flv3.lookup_code (+)
126 AND hrl.country || '_COUNTY' = flv3.lookup_type (+)
127 AND DECODE(flv3.lookup_code, NULL, '1', flv3.security_group_id) =
128 DECODE(flv3.lookup_code, NULL, '1',
129 FND_GLOBAL.lookup_security_group(flv3.lookup_type, flv3.view_application_id))
130 AND DECODE(flv3.lookup_code, NULL, '1', flv3.view_application_id) =
131 DECODE(flv3.lookup_code, NULL, '1', 3)
132 AND DECODE(flv3.lookup_code, NULL, '1', flv3.language) =
133 DECODE(flv3.lookup_code, NULL, '1', USERENV('LANG'))
134 AND hrl.town_or_city = flv4.lookup_code(+)
135 AND hrl.country || '_PROVINCE' = flv4.lookup_type (+)
136 AND DECODE(flv4.lookup_code, NULL, '1', flv4.security_group_id) =
137 DECODE(flv4.lookup_code, NULL, '1',
138 FND_GLOBAL.lookup_security_group(flv4.lookup_type, flv4.view_application_id))
139 AND DECODE(flv4.lookup_code, NULL, '1', flv4.view_application_id) =
140 DECODE(flv4.lookup_code, NULL, '1', 3)
141 AND DECODE(flv4.lookup_code, NULL, '1', flv4.language) =
142 DECODE(flv4.lookup_code, NULL, '1', USERENV('LANG'))
143 ;
144
145 d_progress := '210';
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 IF (g_debug_unexp) THEN
150 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception occurred retrieving location');
151 END IF;
152 x_address_line_1 := '';
153 x_address_line_2 := '';
154 x_address_line_3 := '';
155 x_town_or_city := '';
156 x_state_or_province := '';
157 x_postal_code := '';
158 x_territory_short_name := '';
159 x_iso_territory_code := '';
160
161 END;
162
163 ELSE
164 -- HZ Location
165 d_progress := '300';
166
167 BEGIN
168 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
169 SELECT hzl.address1
170 , hzl.address2
171 , hzl.address3
172 , hzl.city
173 , NVL(DECODE(hzl.county,NULL,hzl.state,
174 DECODE(flv1.meaning,NULL,
175 DECODE(flv2.meaning,NULL,flv3.meaning,flv2.lookup_code)
176 , flv1.lookup_code)), hzl.state)|| Decode (hzl.province, NULL , '', ', ' || hzl.province) --bug10245785
177 , hzl.postal_code
178 , NVL(ftel.territory_short_name, hzl.country)
179 , fte.iso_territory_code
180 INTO x_address_line_1
181 , x_address_line_2
182 , x_address_line_3
183 , x_town_or_city
184 , x_state_or_province
185 , x_postal_code
186 , x_territory_short_name
187 , x_iso_territory_code
188 FROM hz_locations hzl
189 , fnd_territories fte
190 , fnd_territories_tl ftel
191 , fnd_lookup_values flv1
192 , fnd_lookup_values flv2
193 , fnd_lookup_values flv3
194 WHERE hzl.location_id = p_location_id
195 AND hzl.country = fte.territory_code (+)
196 AND hzl.country = ftel.territory_code (+)
197 AND DECODE(ftel.territory_code, NULL, '1', ftel.language) =
198 DECODE(ftel.territory_code, NULL, '1', USERENV('LANG'))
199 AND hzl.county = flv1.lookup_code (+)
200 AND hzl.country || '_PROVINCE' = flv1.lookup_type (+)
201 AND DECODE(flv1.lookup_code, NULL, '1', flv1.security_group_id) =
202 DECODE(flv1.lookup_code, NULL, '1',
203 FND_GLOBAL.lookup_security_group(flv1.lookup_type, flv1.view_application_id))
204 AND DECODE(flv1.lookup_code, NULL, '1', flv1.view_application_id) =
205 DECODE(flv1.lookup_code, NULL, '1', 3)
206 AND DECODE(flv1.lookup_code, NULL, '1', flv1.language) =
207 DECODE(flv1.lookup_code, NULL, '1', USERENV('LANG'))
208 AND hzl.state = flv2.lookup_code (+)
209 AND hzl.country || '_STATE' = flv2.lookup_type (+)
210 AND DECODE(flv2.lookup_code, NULL, '1', flv2.security_group_id) =
211 DECODE(flv2.lookup_code, NULL, '1',
212 FND_GLOBAL.lookup_security_group(flv2.lookup_type, flv2.view_application_id))
213 AND DECODE(flv2.lookup_code, NULL, '1', flv2.view_application_id) =
214 DECODE(flv2.lookup_code, NULL, '1', 3)
215 AND DECODE(flv2.lookup_code, NULL, '1', flv2.language) =
216 DECODE(flv2.lookup_code, NULL, '1', USERENV('LANG'))
217 AND hzl.county = flv3.lookup_code (+)
218 AND hzl.country || '_COUNTY' = flv3.lookup_type (+)
219 AND DECODE(flv3.lookup_code, NULL, '1', flv3.security_group_id) =
220 DECODE(flv3.lookup_code, NULL, '1',
221 FND_GLOBAL.lookup_security_group(flv3.lookup_type, flv3.view_application_id))
222 AND DECODE(flv3.lookup_code, NULL, '1', flv3.view_application_id) =
223 DECODE(flv3.lookup_code, NULL, '1', 3)
224 AND DECODE(flv3.lookup_code, NULL, '1', flv3.language) =
225 DECODE(flv3.lookup_code, NULL, '1', USERENV('LANG'))
226 ;
227
228 d_progress := '310';
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 IF (g_debug_unexp) THEN
233 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception occurred retrieving location');
234 END IF;
235 x_address_line_1 := '';
236 x_address_line_2 := '';
237 x_address_line_3 := '';
238 x_town_or_city := '';
239 x_state_or_province := '';
240 x_postal_code := '';
241 x_territory_short_name := '';
242 x_iso_territory_code := '';
243
244 END;
245
246 END IF; -- IF (l_temp_location_id IS NOT NULL) THEN
247
248 d_progress := '140';
249
250 IF (g_debug_stmt) THEN
251 PO_DEBUG.debug_var(d_module, d_progress, 'x_address_line_1', x_address_line_1);
252 PO_DEBUG.debug_var(d_module, d_progress, 'x_address_line_2', x_address_line_2);
253 PO_DEBUG.debug_var(d_module, d_progress, 'x_address_line_3', x_address_line_3);
254 PO_DEBUG.debug_var(d_module, d_progress, 'x_town_or_city', x_town_or_city);
255 PO_DEBUG.debug_var(d_module, d_progress, 'x_state_or_province', x_state_or_province);
256 PO_DEBUG.debug_var(d_module, d_progress, 'x_postal_code', x_postal_code);
257 PO_DEBUG.debug_var(d_module, d_progress, 'x_territory_short_name', x_territory_short_name);
258 PO_DEBUG.debug_var(d_module, d_progress, 'x_iso_territory_code', x_iso_territory_code);
259 PO_DEBUG.debug_end(d_module);
260 END IF;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 IF (g_debug_unexp) THEN
265 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception occurred retrieving location');
266 END IF;
267
268 x_address_line_1 := '';
269 x_address_line_2 := '';
270 x_address_line_3 := '';
271 x_town_or_city := '';
272 x_state_or_province := '';
273 x_postal_code := '';
274 x_territory_short_name := '';
275 x_iso_territory_code := '';
276
277 END get_address;
278 -- <R12 PO OTM Integration END>
279
280 /********************************************************************************
281 **
282 ** Procedure get_address
283 ** Created for ER 2291745
284 ** Logic
285 ** 1. The x_temp_location_id will be set if the location exists in hr_Locations
286 ** 2. Based on the x_temp_location_id the address will be selected from either
287 ** hr_locations or from hz_locations
288 **
289 ********************************************************************************/
290
291 PROCEDURE get_address
292 ( x_location_id IN Number,
293 Address_line_1 OUT NOCOPY Varchar2,
294 Address_line_2 OUT NOCOPY Varchar2,
295 Address_line_3 OUT NOCOPY Varchar2,
296 Territory_short_name OUT NOCOPY VArchar2,
297 Address_info OUT NOCOPY Varchar2 )
298 IS
299 l_town_or_city HR_LOCATIONS_ALL.town_or_city%TYPE;
300 l_state_or_province HR_LOCATIONS_ALL.region_1%TYPE;
301 l_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
302 l_iso_territory_code FND_TERRITORIES.iso_territory_code%TYPE;
303
304 Begin
305
306 get_address (
307 p_location_id => x_location_id
308 , x_address_line_1 => address_line_1
309 , x_address_line_2 => address_line_2
310 , x_address_line_3 => address_line_3
311 , x_town_or_city => l_town_or_city
312 , x_state_or_province => l_state_or_province
313 , x_postal_code => l_postal_code
314 , x_territory_short_name => territory_short_name
315 , x_iso_territory_code => l_iso_territory_code );
316
317 IF (l_town_or_city IS NULL) THEN
318 address_info := l_state_or_province || ' ' || l_postal_code;
319 ELSE
320 address_info := l_town_or_city || ',' || l_state_or_province || ' ' || l_postal_code;
321 END IF;
322
323
324 END GET_ADDRESS;
325
326 /********************************************************************************
327 **
328 ** Procedure get_address (with over loading)
329 ** Created for FPJ PO Communication Enhancement
330 ** Logic
331 ** 1. The x_temp_location_id will be set if the location exists in hr_Locations
332 ** 2. Based on the x_temp_location_id the address will be selected from either
333 ** hr_locations or from hz_locations
334 **--Change Hisotry: bug#3438608 added the out variables x_town_or_city
335 --x_postal_code and x_state_or_province
336 ********************************************************************************/
337 PROCEDURE get_address
338 ( p_location_id IN Number,
339 x_address_line_1 OUT NOCOPY Varchar2,
340 x_address_line_2 OUT NOCOPY Varchar2,
341 x_address_line_3 OUT NOCOPY Varchar2,
342 x_territory_short_name OUT NOCOPY VArchar2,
343 x_address_info OUT NOCOPY Varchar2,
344 x_location_name OUT NOCOPY Varchar2,
345 x_contact_phone OUT NOCOPY Varchar2,
346 x_contact_fax OUT NOCOPY Varchar2,
347 x_address_line_4 OUT NOCOPY Varchar2,
348 x_town_or_city OUT NOCOPY HR_LOCATIONS.town_or_city%type,
349 x_postal_code OUT NOCOPY HR_LOCATIONS.postal_code%type,
350 x_state_or_province OUT NOCOPY varchar2)
351
352 IS
353 l_town_or_city Varchar2(240);
354 l_state_or_province Varchar2(240);
355 l_postal_code Varchar2(240);
356 l_temp_location_id Number := NULL ;
357 Begin
358
359 /* Select the location id from hr_locations. If the location is in hr_locations
360 it will be populated. Else the l_temp_location_id will be made NULL */
361
362 Begin
363 Select location_id into l_temp_location_id
364 from hr_locations
365 where location_id = p_location_id;
366 exception
367 WHEN NO_DATA_FOUND THEN
368 l_temp_location_id := NULL;
369 end;
370
371
372 if (l_temp_location_id is not null) then
373
374 /* If the l_addr_select_qry location id is not null then get the address from hr_locations */
375
376 Begin
377 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
378 Select HLC.ADDRESS_LINE_1,
379 HLC.ADDRESS_LINE_2,
380 HLC.ADDRESS_LINE_3,
381 -- HLC.TOWN_OR_CITY, --bug#15993315 commented to fetch town_or_city from fnd_looup_values
382 Decode(HLC.TOWN_OR_CITY,FCL4.lookup_code,FCL4.meaning,HLC.TOWN_OR_CITY),
383 NVL(DECODE(HLC.REGION_1, NULL, HLC.REGION_2,
384 DECODE(FCL1.MEANING, NULL,
385 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
386 FCL1.LOOKUP_CODE)), HLC.REGION_2) ,
387 HLC.POSTAL_CODE,
388 NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY),
389 HLC.LOCATION_CODE,
390 HLC.TELEPHONE_NUMBER_1,
391 HLC.TELEPHONE_NUMBER_2
392 INTO
393 x_address_line_1 ,
394 x_address_line_2 ,
395 x_address_line_3 ,
396 l_town_or_city ,
397 l_state_or_province,
398 l_postal_code,
399 x_territory_short_name,
400 x_location_name,
401 x_contact_phone,
402 x_contact_fax
403 FROM
404 HR_LOCATIONS HLC,
405 FND_TERRITORIES_TL FTE,
406 FND_LOOKUP_VALUES FCL1,
407 FND_LOOKUP_VALUES FCL2,
408 FND_LOOKUP_VALUES FCL3,
409 FND_LOOKUP_VALUES FCL4
410 Where
411 HLC.LOCATION_ID = p_location_id AND
412 HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
413 DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
414 DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
415 HLC.REGION_1 = FCL1.LOOKUP_CODE (+) AND
416 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
417 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
418 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
419 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
420 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
421 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
422 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
423 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
424 HLC.REGION_2 = FCL2.LOOKUP_CODE (+) AND
425 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
426 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
427 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
428 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
429 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
430 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
431 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
432 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
433 HLC.REGION_1 = FCL3.LOOKUP_CODE (+) AND
434 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
435 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
436 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
437 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
438 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
439 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
440 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
441 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
442 HLC.TOWN_OR_CITY = FCL4.LOOKUP_CODE (+) AND
443 HLC.COUNTRY || '_PROVINCE' = FCL4.LOOKUP_TYPE (+) AND
444 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.SECURITY_GROUP_ID) =
445 DECODE(FCL4.LOOKUP_CODE, NULL, '1',
446 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL4.LOOKUP_TYPE, FCL4.VIEW_APPLICATION_ID)) AND
447 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.VIEW_APPLICATION_ID) =
448 DECODE(FCL4.LOOKUP_CODE, NULL, '1', 3) AND
449 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.LANGUAGE) =
450 DECODE(FCL4.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
451
452 Exception
453 WHEN OTHERS then
454 x_address_line_1 := '';
455 x_address_line_2 := '';
456 x_address_line_3 := '';
457 l_town_or_city := '';
458 l_state_or_province := '';
459 l_postal_code := '';
460 --bug#3438608
461 x_town_or_city := '';
462 x_state_or_province := '';
463 x_postal_code := '';
464 --bug#3438608
465
466 x_territory_short_name := '';
467 x_location_name := '';
468 x_contact_phone := '';
469 x_contact_fax := '';
470
471 End; /* hr_locations */
472
473 else
474
475 /* If the l_addr_select_qry location id is null then select the address from hz_locations */
476 /*
477 bug#3463617: address4 is selected from hz_locations.
478 */
479 Begin
480 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
481 SELECT
482 HLC.ADDRESS1,
483 HLC.ADDRESS2,
484 HLC.ADDRESS3,
485 HLC.CITY,
486 NVL(DECODE(HLC.county, NULL, HLC.state,
487 DECODE(FCL1.MEANING, NULL,
488 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
489 FCL1.LOOKUP_CODE)), HLC.state)|| Decode (HLC.province, NULL , '', ', ' || HLC.province) , --bug10245785
490 HLC.POSTAL_CODE,
491 NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
492 HLC.ADDRESS4
493 INTO
494 x_address_line_1 ,
495 x_address_line_2 ,
496 x_address_line_3 ,
497 l_town_or_city ,
498 l_state_or_province,
499 l_postal_code,
500 x_territory_short_name,
501 x_address_line_4
502 FROM
503 HZ_LOCATIONS HLC,
504 FND_TERRITORIES_TL FTE,
505 FND_LOOKUP_VALUES FCL1,
506 FND_LOOKUP_VALUES FCL2,
507 FND_LOOKUP_VALUES FCL3
508 WHERE
509 HLC.LOCATION_ID = p_location_id AND
510 HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
511 DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
512 DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
513 HLC.county = FCL1.LOOKUP_CODE (+) AND
514 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
515 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
516 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
517 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
518 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
519 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
520 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
521 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
522 HLC.state = FCL2.LOOKUP_CODE (+) AND
523 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
524 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
525 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
526 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
527 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
528 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
529 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
530 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
531 HLC.county = FCL3.LOOKUP_CODE (+) AND
532 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
533 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
534 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
535 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
536 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
537 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
538 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
539 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
540 /*
541 In hz_locations table there is no columns for location code, phone and fax.
542 */
543 --bug#3438608 nulling out the columns for hz_locations table where there is no column for location_code
544 --phone and fax..
545 x_location_name := null;
546 x_contact_phone :=null;
547 x_contact_fax :=null;
548
549 Exception
550 WHEN OTHERS then
551 --bug# 3438608
552 x_address_line_1 := '';
553 x_address_line_2 := '';
554 x_address_line_3 := '';
555 --bug#3438608
556 l_town_or_city := '';
557 l_state_or_province := '';
558 l_postal_code := '';
559 x_town_or_city := '';
560 x_state_or_province := '';
561 x_postal_code := '';
562 x_territory_short_name := '';
563 x_location_name := '';
564 x_contact_phone := '';
565 x_contact_fax := '';
566 x_address_line_4 := '';
567
568 END; /* HZ_LOCATIONS */
569
570 end if;
571
572 If (l_town_or_city is null) then
573 x_address_info := l_state_or_province||' '|| l_postal_code;
574 else
575 x_address_info := l_town_or_city||', '||l_state_or_province||' '||l_postal_code;
576 end if;
577 --bug#3438608 copy the values of l_town_or_city,l_postal_code and
578 --l_state_or_province to their respective out variables
579 x_town_or_city:=l_town_or_city;
580 x_postal_code:=l_postal_code;
581 x_state_or_province:=l_state_or_province;
582
583 Exception
584 /* If thers is any error null out all the fields */
585 WHEN OTHERS THEN
586 x_address_line_1 := '';
587 x_address_line_2 := '';
588 x_address_line_3 := '';
589 --bug#3438608
590 x_town_or_city := '';
591 x_state_or_province := '';
592 x_postal_code := '';
593 --bug#3438608
594
595 x_territory_short_name := '';
596 x_location_name := '';
597 x_contact_phone := '';
598 x_contact_fax := '';
599 x_address_line_4 := '';
600
601 END GET_ADDRESS;
602
603
604 /*********************************************************************************************
605 **
606 ** Procedure: get_alladdress_lines
607 ** This procedure is used to retriev the address values mapped to
608 ** HR_LOCATIONS or HZ_LOCATIONS.
609 **
610 ** The prompts and the columns names where the prompts are mapped to
611 ** HR_LOCATIONS table is retrieved by using fnd_dflex package. fnd_dflex package is
612 ** is having functions and procedures to retrieve prompts names and the column names
613 ** of HR_LOCATIONS where the address details are stored.
614 ** Note: Some look up codes are stored in HR_LOCATIONS table. Current procedure is not
615 ** retrieving the loop up values for these codes.
616 **
617 ** There is no package available to retrieve the prompts and column names
618 ** that are mapped to HZ_LOCATIONS. But HZ_FORMAT_PUB package is having a procedure
619 ** FORMAT_ADDRESS which returns the formatted address values for a given location id
620 ** and style code. This procedure doesn't give the prompts and column names of
621 ** HZ_LOCATIONS table where the address details are stored. Only address values are
622 ** retrieved for HZ_LOCATIONS.
623 **
624 ** Note: Since address are customizable and there is no limitaion in using the
625 ** number of segments, this procedure assumed that a max of 20 segments are enabled.
626 ** If more than 20 values are enabled this procedure will retrieve first 20 segment values.
627 **
628 **--Change Hisotry:
629 *********************************************************************************************/
630 PROCEDURE get_alladdress_lines
631 ( p_location_id IN Number,
632 x_address_line_1 OUT NOCOPY Varchar2,
633 x_address_line_2 OUT NOCOPY Varchar2,
634 x_address_line_3 OUT NOCOPY Varchar2,
635 x_territory_short_name OUT NOCOPY VArchar2,
636 x_address_info OUT NOCOPY Varchar2,
637 x_location_name OUT NOCOPY Varchar2,
638 x_contact_phone OUT NOCOPY Varchar2,
639 x_contact_fax OUT NOCOPY Varchar2,
640 x_address_line_4 OUT NOCOPY Varchar2,
641 x_town_or_city OUT NOCOPY HR_LOCATIONS.town_or_city%type,
642 x_postal_code OUT NOCOPY HR_LOCATIONS.postal_code%type,
643 x_state_or_province OUT NOCOPY varchar2)
644
645 IS
646
647 /* Variables used in retreiving segments, prompts and column name from fnd_dflex*/
648
649 flexfield fnd_dflex.dflex_r;
650 flexinfo fnd_dflex.dflex_dr;
651 lcontext fnd_dflex.context_r;
652 i BINARY_INTEGER;
653 segments fnd_dflex.segments_dr;
654 l_addr_prompts_array vchar_array := vchar_array(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
655 l_addr_col_names_array vchar_array := vchar_array('NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL');
656 l_addr_values_array vchar_array := vchar_array(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
657 l_count number := 0;
658 l_addr_col_names varchar2(4000) :=NULL; -- Contains list of column names
659 --l_addr_into_qry varchar2(4000) :=NULL;
660
661 /*Bug 5854013 l_style_code holds the address_style from hz_locations also.Hence declaring the variable
662 to hold the length same as of HZ_LOCATIONS.ADDRESS_STYLE which is bigger than HR_LOCATIONS.STYLE
663 l_style_code HR_LOCATIONS.STYLE%type := null; */
664 l_style_code HZ_LOCATIONS.ADDRESS_STYLE%type := null;
665
666 l_temp_location_id Number := NULL ;
667 l_addr_select_qry varchar2(4000);
668 l_table_count number := 0;
669
670 /*end of variables*/
671
672 /* Variables used in HZ_FORMAT_PUB.FORMAT_ADDRESS procedure */
673
674 x_formatted_address_tbl HZ_FORMAT_PUB.string_tbl_type;
675 x_return_status VARCHAR2(2) ;
676 x_msg_count NUMBER ;
677 x_msg_data VARCHAR2(4000) ;
678 x_formatted_address VARCHAR2(4000) ;
679 x_formatted_lines_cnt NUMBER ;
680
681 /*end of variables */
682
683 l_addr_prompt_query_count number := 0;
684 l_location_id_exists varchar2(1) := 'N';
685 l_style_code_exists varchar2(1) := 'N';
686
687 c_log_head CONSTANT VARCHAR2(30) := 'PO_HR_LOCATION.';
688 l_api_name CONSTANT VARCHAR2(30):= 'GET_ALLADDRESS_LINES';
689
690
691 Begin
692
693 /* Select the location id from hr_locations. If the location is in hr_locations
694 it will be populated. Else the l_temp_location_id will be made NULL */
695
696 Begin
697 Select location_id, style into l_temp_location_id, l_style_code
698 from hr_locations
699 where location_id = p_location_id;
700 exception
701 WHEN NO_DATA_FOUND THEN
702 l_temp_location_id := NULL;
703 l_style_code := NULL;
704 end;
705
706
707 if (l_temp_location_id is not null) then
708
709 /* If location id is not null then get the address from hr_locations */
710
711 Begin
712 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
713 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Retreiving data from HR_LOCATIONS');
714 END IF;
715 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
716 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'location Id:'|| p_location_id);
717 END IF;
718 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
719 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Style code:'|| l_style_code);
720 END IF;
721
722 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
723 Select HLC.ADDRESS_LINE_1,
724 HLC.ADDRESS_LINE_2,
725 HLC.ADDRESS_LINE_3,
726 -- HLC.TOWN_OR_CITY, --bug#15993315 commented to fetch town_or_city from fnd_lookup_values
727 Decode(HLC.TOWN_OR_CITY,FCL4.lookup_code,FCL4.meaning,HLC.TOWN_OR_CITY),
728 NVL(DECODE(HLC.REGION_1, NULL, HLC.REGION_2,
729 DECODE(FCL1.MEANING, NULL,
730 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
731 FCL1.LOOKUP_CODE)), HLC.REGION_2) ,
732 HLC.POSTAL_CODE,
733 NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
734 HLC.LOCATION_CODE,
735 HLC.TELEPHONE_NUMBER_1,
736 HLC.TELEPHONE_NUMBER_2
737 INTO
738 x_address_line_1 ,
739 x_address_line_2 ,
740 x_address_line_3 ,
741 x_town_or_city ,
742 x_state_or_province,
743 x_postal_code,
744 x_territory_short_name,
745 x_location_name,
746 x_contact_phone,
747 x_contact_fax
748 FROM
749 HR_LOCATIONS HLC,
750 FND_TERRITORIES_TL FTE,
751 FND_LOOKUP_VALUES FCL1,
752 FND_LOOKUP_VALUES FCL2,
753 FND_LOOKUP_VALUES FCL3,
754 FND_LOOKUP_VALUES FCL4
755 Where
756 HLC.LOCATION_ID = p_location_id AND
757 HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
758 DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
759 DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
760 HLC.REGION_1 = FCL1.LOOKUP_CODE (+) AND
761 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
762 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
763 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
764 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
765 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
766 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
767 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
768 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
769 HLC.REGION_2 = FCL2.LOOKUP_CODE (+) AND
770 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
771 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
772 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
773 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
774 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
775 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
776 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
777 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
778 HLC.REGION_1 = FCL3.LOOKUP_CODE (+) AND
779 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
780 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
781 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
782 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
783 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
784 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
785 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
786 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
787 HLC.TOWN_OR_CITY = FCL4.LOOKUP_CODE (+) AND
788 HLC.COUNTRY || '_PROVINCE' = FCL4.LOOKUP_TYPE (+) AND
789 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.SECURITY_GROUP_ID) =
790 DECODE(FCL4.LOOKUP_CODE, NULL, '1',
791 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL4.LOOKUP_TYPE, FCL4.VIEW_APPLICATION_ID)) AND
792 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.VIEW_APPLICATION_ID) =
793 DECODE(FCL4.LOOKUP_CODE, NULL, '1', 3) AND
794 DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.LANGUAGE) =
795 DECODE(FCL4.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
796
797 /*******************************************************************************************************
798 Logic used to improve performance.
799 1. Check whether the given location id exists in g_address_details PL/SQL table.
800 2. FND_DFLEX package is not called if location id exists.
801 3. IF location id is not in PL/SQL table check whether the style code exists
802 in g_addr_prompt_query PL/SQL table.
803 4. If the style code exists retrieve select query and prompts list from g_addr_prompt_query PL/SQL table,
804 else use FND_DFLEX package to retrieve the column names and prompts and add them to
805 g_addr_prompt_query PL/SQL table.
806 *******************************************************************************************************/
807
808 l_table_count := g_address_details.count; -- Number of rows in the PL/SQl table
809 l_addr_prompt_query_count := g_addr_prompt_query.count; -- Number of records in g_addr_prompt_query PL/SQL table
810
811 /* Check whether the location id exists in g_address_details PL/SQL table.*/
812 FOR i IN 1 .. l_table_count LOOP
813
814 IF g_address_details(i).location_id = p_location_id THEN
815 l_location_id_exists := 'Y';
816 EXIT ;
817 END IF;
818 END LOOP;
819
820
821 /* IF location id is not in g_address_details table enter into IF condition*/
822 IF (l_location_id_exists <> 'Y') THEN
823
824 FOR i IN 1 .. l_addr_prompt_query_count LOOP
825
826 IF g_addr_prompt_query(i).address_style = l_style_code THEN
827 l_addr_select_qry := g_addr_prompt_query(i).query;
828 -- Start bug#3622675: Removed l_addr_prompts_array and added 20 variables
829 -- to hold the prompt names.
830
831 l_addr_prompts_array(1):= g_addr_prompt_query(i).addr_label_1;
832 l_addr_prompts_array(2):= g_addr_prompt_query(i).addr_label_2;
833 l_addr_prompts_array(3):= g_addr_prompt_query(i).addr_label_3;
834 l_addr_prompts_array(4):= g_addr_prompt_query(i).addr_label_4;
835 l_addr_prompts_array(5):= g_addr_prompt_query(i).addr_label_5;
836 l_addr_prompts_array(6):= g_addr_prompt_query(i).addr_label_6;
837 l_addr_prompts_array(7):= g_addr_prompt_query(i).addr_label_7;
838 l_addr_prompts_array(8):= g_addr_prompt_query(i).addr_label_8;
839 l_addr_prompts_array(9):= g_addr_prompt_query(i).addr_label_9;
840 l_addr_prompts_array(10):= g_addr_prompt_query(i).addr_label_10;
841 l_addr_prompts_array(11):= g_addr_prompt_query(i).addr_label_11;
842 l_addr_prompts_array(12):= g_addr_prompt_query(i).addr_label_12;
843 l_addr_prompts_array(13):= g_addr_prompt_query(i).addr_label_13;
844 l_addr_prompts_array(14):= g_addr_prompt_query(i).addr_label_14;
845 l_addr_prompts_array(15):= g_addr_prompt_query(i).addr_label_15;
846 l_addr_prompts_array(16):= g_addr_prompt_query(i).addr_label_16;
847 l_addr_prompts_array(17):= g_addr_prompt_query(i).addr_label_17;
848 l_addr_prompts_array(18):= g_addr_prompt_query(i).addr_label_18;
849 l_addr_prompts_array(19):= g_addr_prompt_query(i).addr_label_19;
850 l_addr_prompts_array(20):= g_addr_prompt_query(i).addr_label_20;
851 -- end of bug#3622675 -->
852 l_style_code_exists := 'Y';
853 EXIT ;
854 END IF;
855 END LOOP;
856
857 /* If style code not exists use FND_DFLEX package to retireve prompts and column names. */
858
859 IF l_style_code_exists <> 'Y' THEN
860
861 fnd_dflex.get_flexfield('PER', 'Address Location', flexfield, flexinfo);
862 lcontext.flexfield := flexfield;
863 lcontext.context_code := l_style_code;
864 fnd_dflex.get_segments(lcontext, segments);
865 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
866 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Retrieved Values from descriptive flex');
867 END IF;
868 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
869 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Number of values retrieved:'||segments.nsegments);
870 END IF;
871
872 FOR i IN 1 .. segments.nsegments LOOP
873
874 if(l_count = 20) then
875 EXIT;
876 END IF;
877
878 l_addr_prompts_array(i) := segments.segment_name(i);
879 l_addr_col_names_array(i) := segments.application_column_name(i);
880 l_count := l_count+1;
881
882 END LOOP;
883
884 /* Concatinate the column names separated by ',' */
885 FOR i IN 1 .. 20 LOOP
886
887 IF l_addr_col_names is NULL THEN
888 l_addr_col_names := l_addr_col_names_array(i);
889 ELSE
890 l_addr_col_names := l_addr_col_names || ', '|| l_addr_col_names_array(i) ;
891
892 END IF;
893
894 END LOOP;
895
896 -- Query to retrieve the address values from HR_LOCATIONS.
897 l_addr_select_qry := 'select '|| l_addr_col_names || ' from hr_locations where location_id = :1 ' ;
898 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
899 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'select query:'||l_addr_select_qry);
900 END IF;
901
902 END IF;
903
904 /* Assign the values to g_address_details PL/SQL table */
905 l_table_count := l_table_count + 1;
906 g_address_details(l_table_count).location_id := p_location_id;
907 g_address_details(l_table_count).address_style := l_style_code;
908 g_address_details(l_table_count).addr_label_1 := l_addr_prompts_array(1);
909 g_address_details(l_table_count).addr_label_2 := l_addr_prompts_array(2);
910 g_address_details(l_table_count).addr_label_3 := l_addr_prompts_array(3);
911 g_address_details(l_table_count).addr_label_4 := l_addr_prompts_array(4);
912 g_address_details(l_table_count).addr_label_5 := l_addr_prompts_array(5);
913 g_address_details(l_table_count).addr_label_6 := l_addr_prompts_array(6);
914 g_address_details(l_table_count).addr_label_7 := l_addr_prompts_array(7);
915 g_address_details(l_table_count).addr_label_8 := l_addr_prompts_array(8);
916 g_address_details(l_table_count).addr_label_9 := l_addr_prompts_array(9);
917 g_address_details(l_table_count).addr_label_10 := l_addr_prompts_array(10);
918 g_address_details(l_table_count).addr_label_11 := l_addr_prompts_array(11);
919 g_address_details(l_table_count).addr_label_12 := l_addr_prompts_array(12);
920 g_address_details(l_table_count).addr_label_13 := l_addr_prompts_array(13);
921 g_address_details(l_table_count).addr_label_14 := l_addr_prompts_array(14);
922 g_address_details(l_table_count).addr_label_15 := l_addr_prompts_array(15);
923 g_address_details(l_table_count).addr_label_16 := l_addr_prompts_array(16);
924 g_address_details(l_table_count).addr_label_17 := l_addr_prompts_array(17);
925 g_address_details(l_table_count).addr_label_18 := l_addr_prompts_array(18);
926 g_address_details(l_table_count).addr_label_19 := l_addr_prompts_array(19);
927 g_address_details(l_table_count).addr_label_20 := l_addr_prompts_array(20);
928
929
930 --Add style code, address prompts array and select query to PL/SQL if the style code is not in PL/SQL table.
931 IF l_style_code_exists <> 'Y' THEN
932 l_addr_prompt_query_count := l_addr_prompt_query_count+1;
933 g_addr_prompt_query(l_addr_prompt_query_count).address_style := l_style_code;
934
935 -- Start bug#3622675
936 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_1 := l_addr_prompts_array(1) ;
937 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_2 := l_addr_prompts_array(2) ;
938 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_3 := l_addr_prompts_array(3) ;
939 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_4 := l_addr_prompts_array(4) ;
940 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_5 := l_addr_prompts_array(5) ;
941 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_6 := l_addr_prompts_array(6) ;
942 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_7 := l_addr_prompts_array(7) ;
943 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_8 := l_addr_prompts_array(8) ;
944 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_9 := l_addr_prompts_array(9) ;
945 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_10:= l_addr_prompts_array(10);
946 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_11:= l_addr_prompts_array(11);
947 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_12:= l_addr_prompts_array(12);
948 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_13:= l_addr_prompts_array(13);
949 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_14:= l_addr_prompts_array(14);
950 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_15:= l_addr_prompts_array(15);
951 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_16:= l_addr_prompts_array(16);
952 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_17:= l_addr_prompts_array(17);
953 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_18:= l_addr_prompts_array(18);
954 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_19:= l_addr_prompts_array(19);
955 g_addr_prompt_query(l_addr_prompt_query_count).addr_label_20:= l_addr_prompts_array(20);
956 -- End bug#3622675
957 g_addr_prompt_query(l_addr_prompt_query_count).query := l_addr_select_qry;
958 END IF;
959 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
960 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Before executing the HR_LOCATIONS query');
961 END IF;
962
963 execute immediate l_addr_select_qry INTO g_address_details(l_table_count).addr_data_1, g_address_details(l_table_count).addr_data_2, g_address_details(l_table_count).addr_data_3,
964 g_address_details(l_table_count).addr_data_4, g_address_details(l_table_count).addr_data_5, g_address_details(l_table_count).addr_data_6,
965 g_address_details(l_table_count).addr_data_7, g_address_details(l_table_count).addr_data_8, g_address_details(l_table_count).addr_data_9,
966 g_address_details(l_table_count).addr_data_10, g_address_details(l_table_count).addr_data_11, g_address_details(l_table_count).addr_data_12,
967 g_address_details(l_table_count).addr_data_13, g_address_details(l_table_count).addr_data_14, g_address_details(l_table_count).addr_data_15,
968 g_address_details(l_table_count).addr_data_16, g_address_details(l_table_count).addr_data_17, g_address_details(l_table_count).addr_data_18,
969 g_address_details(l_table_count).addr_data_19, g_address_details(l_table_count).addr_data_20 USING p_location_id ;
970 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
971 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'After executing the HR_LOCATIONS query');
972 END IF;
973
974
975
976 END IF;
977
978
979 Exception
980 WHEN OTHERS then
981 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
982 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Exception while retrieving data from HR_LOCATIONS');
983 END IF;
984 x_address_line_1 := '';
985 x_address_line_2 := '';
986 x_address_line_3 := '';
987 x_town_or_city := '';
988 x_state_or_province := '';
989 x_postal_code := '';
990 x_territory_short_name := '';
991 x_location_name := '';
992 x_contact_phone := '';
993 x_contact_fax := '';
994
995 End; /* hr_locations */
996
997 else
998
999 /* If location id is null then select the address from hz_locations */
1000 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1001 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Before executing the HZ_LOCATIONS query');
1002 END IF;
1003 Begin
1004 /*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
1005 SELECT
1006 HLC.ADDRESS1,
1007 HLC.ADDRESS2,
1008 HLC.ADDRESS3,
1009 HLC.CITY,
1010 NVL(DECODE(HLC.county, NULL, HLC.state,
1011 DECODE(FCL1.MEANING, NULL,
1012 DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
1013 FCL1.LOOKUP_CODE)), HLC.state)|| Decode (HLC.province, NULL , '', ', ' || HLC.province) ,--bug10245785
1014 HLC.POSTAL_CODE,
1015 NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
1016 HLC.ADDRESS4,
1017 ADDRESS_STYLE
1018 INTO
1019 x_address_line_1 ,
1020 x_address_line_2 ,
1021 x_address_line_3 ,
1022 x_town_or_city ,
1023 x_state_or_province,
1024 x_postal_code,
1025 x_territory_short_name,
1026 x_address_line_4,
1027 l_style_code
1028 FROM
1029 HZ_LOCATIONS HLC,
1030 FND_TERRITORIES_TL FTE,
1031 FND_LOOKUP_VALUES FCL1,
1032 FND_LOOKUP_VALUES FCL2,
1033 FND_LOOKUP_VALUES FCL3
1034 WHERE
1035 HLC.LOCATION_ID = p_location_id AND
1036 HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
1037 DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
1038 DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
1039 HLC.county = FCL1.LOOKUP_CODE (+) AND
1040 HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
1041 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
1042 DECODE(FCL1.LOOKUP_CODE, NULL, '1',
1043 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
1044 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
1045 DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
1046 DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
1047 DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
1048 HLC.state = FCL2.LOOKUP_CODE (+) AND
1049 HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
1050 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
1051 DECODE(FCL2.LOOKUP_CODE, NULL, '1',
1052 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
1053 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
1054 DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
1055 DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
1056 DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
1057 HLC.county = FCL3.LOOKUP_CODE (+) AND
1058 HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
1059 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
1060 DECODE(FCL3.LOOKUP_CODE, NULL, '1',
1061 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
1062 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
1063 DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
1064 DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
1065 DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
1066 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1067 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'After executing the HZ_LOCATIONS query');
1068 END IF;
1069
1070 /* hz_locations table doesn't have columns for location code, phone and fax. */
1071
1072 x_location_name := null;
1073 x_contact_phone :=null;
1074 x_contact_fax :=null;
1075
1076 l_table_count := g_address_details.count; -- Number of rows in the PL/SQl table
1077 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1078 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'l_table_count:'||l_table_count);
1079 END IF;
1080
1081 /* Check whether the location id exists in g_address_details PL/SQL table.*/
1082 FOR i IN 1 .. l_table_count LOOP
1083
1084 IF g_address_details(i).location_id = p_location_id THEN
1085 l_location_id_exists := 'Y';
1086 EXIT ;
1087 END IF;
1088 END LOOP;
1089 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1090 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'l_location_id_exists:'||l_location_id_exists);
1091 END IF;
1092
1093 IF l_location_id_exists <> 'Y' THEN
1094
1095 l_table_count := l_table_count + 1;
1096 g_address_details(l_table_count).location_id := p_location_id;
1097 g_address_details(l_table_count).address_style := l_style_code;
1098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1099 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'p_location_id:'|| p_location_id);
1100 END IF;
1101
1102
1103 HZ_FORMAT_PUB.FORMAT_ADDRESS(p_location_id => p_location_id,
1104 -- output parameters
1105 x_return_status => x_return_status, x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data, x_formatted_address => x_formatted_address,
1107 x_formatted_lines_cnt => x_formatted_lines_cnt,
1108 x_formatted_address_tbl => x_formatted_address_tbl) ;
1109 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1110 /* assign address values to l_addr_values_array list */
1111
1112 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'x_formatted_lines_cnt:'|| x_formatted_lines_cnt);
1113 END IF;
1114
1115
1116 IF x_formatted_lines_cnt > 0 THEN
1117 FOR i IN 1 .. x_formatted_lines_cnt
1118 LOOP
1119 l_addr_values_array(i) := x_formatted_address_tbl(i);
1120 IF (i = 20) then
1121 EXIT;
1122 END IF;
1123 END LOOP;
1124 END IF;
1125 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1126 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Before adding HZ_LOCATIONS to PL/SQL tables');
1127 END IF;
1128 /* Assigning the values to global PL/SQL table */
1129 g_address_details(l_table_count).addr_data_1 := l_addr_values_array(1);
1130 g_address_details(l_table_count).addr_data_2 := l_addr_values_array(2);
1131 g_address_details(l_table_count).addr_data_3 := l_addr_values_array(3);
1132 g_address_details(l_table_count).addr_data_4 := l_addr_values_array(4);
1133 g_address_details(l_table_count).addr_data_5 := l_addr_values_array(5);
1134 g_address_details(l_table_count).addr_data_6 := l_addr_values_array(6);
1135 g_address_details(l_table_count).addr_data_7 := l_addr_values_array(7);
1136 g_address_details(l_table_count).addr_data_8 := l_addr_values_array(8);
1137 g_address_details(l_table_count).addr_data_9 := l_addr_values_array(9);
1138 g_address_details(l_table_count).addr_data_10 := l_addr_values_array(10);
1139 g_address_details(l_table_count).addr_data_11 := l_addr_values_array(11);
1140 g_address_details(l_table_count).addr_data_12 := l_addr_values_array(12);
1141 g_address_details(l_table_count).addr_data_13 := l_addr_values_array(13);
1142 g_address_details(l_table_count).addr_data_14 := l_addr_values_array(14);
1143 g_address_details(l_table_count).addr_data_15 := l_addr_values_array(15);
1144 g_address_details(l_table_count).addr_data_16 := l_addr_values_array(16);
1145 g_address_details(l_table_count).addr_data_17 := l_addr_values_array(17);
1146 g_address_details(l_table_count).addr_data_18 := l_addr_values_array(18);
1147 g_address_details(l_table_count).addr_data_19 := l_addr_values_array(19);
1148 g_address_details(l_table_count).addr_data_20 := l_addr_values_array(20);
1149 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1150 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'After adding HZ_LOCATIONS to PL/SQL tables');
1151 END IF;
1152
1153 END IF;
1154
1155 Exception
1156 WHEN OTHERS then
1157 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1158 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Exception in retrieving data from HZ_LOCATIONS');
1159 END IF;
1160 x_address_line_1 := '';
1161 x_address_line_2 := '';
1162 x_address_line_3 := '';
1163 x_town_or_city := '';
1164 x_state_or_province := '';
1165 x_postal_code := '';
1166 x_territory_short_name := '';
1167 x_location_name := '';
1168 x_contact_phone := '';
1169 x_contact_fax := '';
1170 x_address_line_4 := '';
1171
1172 END; /* HZ_LOCATIONS */
1173
1174 end if;
1175
1176 If (x_town_or_city is null) then
1177 x_address_info := x_state_or_province||' '|| x_postal_code;
1178 else
1179 x_address_info := x_town_or_city||', '||x_state_or_province||' '||x_postal_code;
1180 end if;
1181
1182 Exception
1183 /* If thers is any error null out all the fields */
1184 WHEN OTHERS THEN
1185 x_address_line_1 := '';
1186 x_address_line_2 := '';
1187 x_address_line_3 := '';
1188 x_town_or_city := '';
1189 x_state_or_province := '';
1190 x_postal_code := '';
1191 x_territory_short_name := '';
1192 x_location_name := '';
1193 x_contact_phone := '';
1194 x_contact_fax := '';
1195 x_address_line_4 := '';
1196
1197 END GET_ALLADDRESS_LINES;
1198
1199 /***********************************************************************************
1200 Procedure:populate_gt
1201
1202 Why: As the function get_alladdress_lines is callled from the XML views,
1203 DML queries cannot be used in the select queries. The work around for this
1204 is populate the PL/SQL table when the function is called in the select query
1205 and then insert values into global temp table after selecting the values.
1206
1207 Why Global temp table: XML cannot be generated from global PL/SQL table.
1208
1209 ***********************************************************************************/
1210 PROCEDURE populate_gt is
1211
1212 l_count number := 0;
1213 begin
1214
1215 l_count := g_address_details.count ;
1216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1217 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt Before inserting values in global temp table');
1218 END IF;
1219 FOR i IN 1..l_count LOOP
1220 INSERT INTO po_address_details_gt
1221 (location_id,
1222 address_style,
1223 addr_label_1,
1224 addr_label_2,
1225 addr_label_3,
1226 addr_label_4,
1227 addr_label_5,
1228 addr_label_6,
1229 addr_label_7,
1230 addr_label_8,
1231 addr_label_9,
1232 addr_label_10,
1233 addr_label_11,
1234 addr_label_12,
1235 addr_label_13,
1236 addr_label_14,
1237 addr_label_15,
1238 addr_label_16,
1239 addr_label_17,
1240 addr_label_18,
1241 addr_label_19,
1242 addr_label_20,
1243 addr_data_1,
1244 addr_data_2,
1245 addr_data_3,
1246 addr_data_4,
1247 addr_data_5,
1248 addr_data_6,
1249 addr_data_7,
1250 addr_data_8,
1251 addr_data_9,
1252 addr_data_10,
1253 addr_data_11,
1254 addr_data_12,
1255 addr_data_13,
1256 addr_data_14,
1257 addr_data_15,
1258 addr_data_16,
1259 addr_data_17,
1260 addr_data_18,
1261 addr_data_19,
1262 addr_data_20)
1263 VALUES(
1264 g_address_details(i).location_id,
1265 g_address_details(i).address_style,
1266 g_address_details(i).addr_label_1,
1267 g_address_details(i).addr_label_2,
1268 g_address_details(i).addr_label_3,
1269 g_address_details(i).addr_label_4,
1270 g_address_details(i).addr_label_5,
1271 g_address_details(i).addr_label_6,
1272 g_address_details(i).addr_label_7,
1273 g_address_details(i).addr_label_8,
1274 g_address_details(i).addr_label_9,
1275 g_address_details(i).addr_label_10,
1276 g_address_details(i).addr_label_11,
1277 g_address_details(i).addr_label_12,
1278 g_address_details(i).addr_label_13,
1279 g_address_details(i).addr_label_14,
1280 g_address_details(i).addr_label_15,
1281 g_address_details(i).addr_label_16,
1282 g_address_details(i).addr_label_17,
1283 g_address_details(i).addr_label_18,
1284 g_address_details(i).addr_label_19,
1285 g_address_details(i).addr_label_20,
1286 g_address_details(i).addr_data_1,
1287 g_address_details(i).addr_data_2,
1288 g_address_details(i).addr_data_3,
1289 g_address_details(i).addr_data_4,
1290 g_address_details(i).addr_data_5,
1291 g_address_details(i).addr_data_6,
1292 g_address_details(i).addr_data_7,
1293 g_address_details(i).addr_data_8,
1294 g_address_details(i).addr_data_9,
1295 g_address_details(i).addr_data_10,
1296 g_address_details(i).addr_data_11,
1297 g_address_details(i).addr_data_12,
1298 g_address_details(i).addr_data_13,
1299 g_address_details(i).addr_data_14,
1300 g_address_details(i).addr_data_15,
1301 g_address_details(i).addr_data_16,
1302 g_address_details(i).addr_data_17,
1303 g_address_details(i).addr_data_18,
1304 g_address_details(i).addr_data_19,
1305 g_address_details(i).addr_data_20);
1306 end loop;
1307 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1308 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt: After inserting values in global temp table');
1309 END IF;
1310
1311 g_address_details.delete;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1316 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt: Error while inserting values in global temp table');
1317 END IF;
1318 end;
1319
1320 -----------------------------------------------------------
1321 -- HTML Orders R12
1322 -- Function : get_formatted_address
1323 --
1324 -- Description : Gets the concatenated address given the location
1325 --
1326 -- IN
1327 -- p_location_id
1328 -- HR or HZ location Id
1329 -- Testing: None
1330 --
1331 ------------------------------------------------------------
1332 FUNCTION get_formatted_address(p_location_id IN NUMBER)
1333 RETURN VARCHAR2
1334 IS
1335 l_address_line_1 Varchar2(240) := '';
1336 l_address_line_2 Varchar2(240) := '';
1337 l_address_line_3 Varchar2(240) := '';
1338 l_territory Varchar2(240);
1339 l_address_info Varchar2(2000);
1340 l_address_lines Varchar2(2000);
1341 l_formatted_address Varchar2(2000);
1342 l_location_name Varchar2(240) := '';
1343 l_contact_phone Varchar2(240) := '';
1344 l_contact_fax Varchar2(240) := '';
1345 l_address_line_4 Varchar2(240) := '';
1346 l_town_or_city Varchar2(240) := '';
1347 l_postal_code Varchar2(240) := '';
1348 l_state_or_province Varchar2(240) := '';
1349
1350 BEGIN
1351 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1352 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'Call the get_address procedure');
1353 END IF;
1354 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1355 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'Location Id: '|| p_location_id);
1356 END IF;
1357
1358 get_address
1359 (p_location_id => p_location_id,
1360 x_address_line_1 => l_address_line_1,
1361 x_address_line_2 => l_address_line_2,
1362 x_address_line_3 => l_address_line_3,
1363 x_territory_short_name => l_territory,
1364 x_address_info => l_address_info,
1365 x_location_name => l_location_name,
1366 x_contact_phone => l_contact_phone,
1367 x_contact_fax => l_contact_fax,
1368 x_address_line_4 => l_address_line_4,
1369 x_town_or_city => l_town_or_city,
1370 x_postal_code => l_postal_code,
1371 x_state_or_province => l_state_or_province );
1372
1373 if l_address_line_2 is not null then
1374 l_address_line_2 := l_address_line_2 ||', ';
1375 end if;
1376
1377 if l_address_line_3 is not null then
1378 l_address_line_3 := l_address_line_3 ||', ';
1379 end if;
1380
1381 if l_address_line_4 is not null then
1382 l_address_line_4 := l_address_line_4 ||', ';
1383 end if;
1384
1385 l_address_lines := l_address_line_1 || ', ' || l_address_line_2 ||
1386 l_address_line_3 || l_address_line_4;
1387 l_formatted_address := substrb(l_address_lines || l_address_info || ', '|| l_territory, 1,2000);
1388 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1389 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION',
1390 'Complete address: '|| l_formatted_address);
1391 END IF;
1392
1393 Return l_formatted_address;
1394
1395 Exception
1396 When Others then
1397 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1398 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,'PO_HR_LOCATION','Error in Retrieving address');
1399 END IF;
1400 l_formatted_address := null;
1401 END;
1402
1403 END PO_HR_LOCATION;