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