[Home] [Help]
PACKAGE BODY: APPS.HR_BPL_ALERT_ADDRESS
Source
1 PACKAGE BODY HR_BPL_ALERT_ADDRESS AS
2 /* $Header: perbaadr.pkb 120.1 2006/01/06 08:06:15 adhunter noship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 -- Format the output address
6 -- -----------------------------------------------------------------------------
7 --
8 PROCEDURE format_address(p_component_string IN VARCHAR2)
9 IS
10 BEGIN
11 --
12 IF p_component_string IS NOT NULL AND g_addr_address IS NOT NULL THEN
13 --
14 g_addr_address := g_addr_address || ', ' || p_component_string;
15 --
16 ELSIF p_component_string IS NOT NULL AND g_addr_address IS NULL THEN
17 --
18 g_addr_address := p_component_string;
19 --
20 END IF;
21 --
22 END format_address;
23 --
24 -- -----------------------------------------------------------------------------
25 -- Format the output contact for a new contact
26 -- -----------------------------------------------------------------------------
27 --
28 PROCEDURE format_emrg_contacts(p_emrg_full_name IN VARCHAR2
29 ,p_emrg_phone_number IN VARCHAR2
30 ,p_emrg_phone_type IN VARCHAR2)
31 IS
32 BEGIN
33 --
34 g_emrg_contacts := g_emrg_contacts
35 || hr_view_alert_messages.get_message_lng_psn
36 ('HR_AO_CONTACT_NAME'
37 ,p_emrg_full_name
38 ,g_emrg_person_id);
39 --
40 -- prints the tel numbers if there are tel numbers present
41 --
42 IF p_emrg_phone_number IS NOT NULL THEN
43 g_emrg_contacts := g_emrg_contacts
44 || hr_view_alert_messages.get_message_lng_psn
45 ('HR_AO_DYNAMIC_TITLE'
46 ,p_emrg_phone_type
47 ,p_emrg_phone_number
48 ,g_emrg_person_id);
49 --
50 END IF;
51 --
52 END format_emrg_contacts;
53 --
54 -- -----------------------------------------------------------------------------
55 -- Format the output contact for additional contact numbers
56 -- -----------------------------------------------------------------------------
57 --
58 PROCEDURE format_emrg_contacts(p_emrg_phone_number IN VARCHAR2
59 ,p_emrg_phone_type IN VARCHAR2)
60 IS
61 BEGIN
62 --
63 g_emrg_contacts := g_emrg_contacts
64 || hr_view_alert_messages.get_message_lng_psn
65 ('HR_AO_DYNAMIC_TITLE'
66 ,p_emrg_phone_type
67 ,p_emrg_phone_number
68 ,g_emrg_person_id);
69 --
70 END format_emrg_contacts;
71 --
72 -- -----------------------------------------------------------------------------
73 -- Gets the details of a persons primary address
74 -- -----------------------------------------------------------------------------
75 --
76 PROCEDURE cache_psn_addrss_details(p_person_id IN NUMBER)
77 IS
78 --
79 -- The following cursor gets all the required address fields for
80 -- a given person id, in order to later construct an address.
81 --
82 CURSOR c_psn_addrss_details(cp_person_id NUMBER)
83 IS
84 SELECT addr.style
85 , addr.address_line1
86 , addr.address_line2
87 , addr.address_line3
88 , addr.address_type
89 , addr.country
90 , addr.postal_code
91 , addr.region_1
92 , addr.region_2
93 , addr.region_3
94 , addr.town_or_city
95 , addr.add_information13
96 , addr.add_information14
97 , addr.add_information15
98 , addr.add_information16
99 , addr.add_information17
100 , addr.add_information18
101 , addr.add_information19
102 , addr.add_information20
103 , NULL
104 FROM per_addresses addr
105 WHERE addr.person_id = cp_person_id
106 AND TRUNC(sysdate)
107 BETWEEN date_from
108 AND NVL(addr.date_to
109 ,TRUNC(SYSDATE))
110 AND addr.primary_flag = 'Y'
111 AND addr.address_type = 'H';
112 --
113 -- This cursor identifies the correct order of address components
114 -- for a given address style.
115 --
116 CURSOR c_psn_addrss_struct(cp_addr_style VARCHAR2)
117 IS
118 SELECT fdc.application_column_name
119 FROM fnd_descr_flex_column_usages fdc
120 WHERE fdc.descriptive_flexfield_name = 'Address Structure'
121 AND fdc.enabled_flag = 'Y'
122 AND fdc.display_flag = 'Y'
123 AND fdc.application_column_name NOT LIKE 'TELEPHONE_NUMBER%'
124 AND fdc.descriptive_flex_context_code = cp_addr_style
125 ORDER BY fdc.column_seq_num;
126 --
127 l_count NUMBER(7);
128 --
129 BEGIN
130 --
131 -- Address components
132 --
133 -- If a person is not provided then set the components to NULL
134 --
135 IF p_person_id IS NULL
136 THEN
137 --
138 g_addr_address_style := NULL;
139 g_addr_address_line1 := NULL;
140 g_addr_address_line2 := NULL;
141 g_addr_address_line3 := NULL;
142 g_addr_address_type := NULL;
143 g_addr_country := NULL;
144 g_addr_postal_code := NULL;
145 g_addr_region_1 := NULL;
146 g_addr_region_2 := NULL;
147 g_addr_region_3 := NULL;
148 g_addr_town_or_city := NULL;
149 g_addr_add_information13 := NULL;
150 g_addr_add_information14 := NULL;
151 g_addr_add_information15 := NULL;
152 g_addr_add_information16 := NULL;
153 g_addr_add_information17 := NULL;
154 g_addr_add_information18 := NULL;
155 g_addr_add_information19 := NULL;
156 g_addr_add_information20 := NULL;
157 g_addr_address := NULL;
158 --
159 -- If not already cached then retrieve address components
160 --
161 ELSIF p_person_id <> NVL(g_person_id,-1)
162 THEN
163 --
164 g_person_id := p_person_id;
165 --
166 OPEN c_psn_addrss_details(p_person_id);
167 --
168 FETCH c_psn_addrss_details
169 INTO g_addr_address_style
170 , g_addr_address_line1
171 , g_addr_address_line2
172 , g_addr_address_line3
173 , g_addr_address_type
174 , g_addr_country
175 , g_addr_postal_code
176 , g_addr_region_1
177 , g_addr_region_2
178 , g_addr_region_3
179 , g_addr_town_or_city
180 , g_addr_add_information13
181 , g_addr_add_information14
182 , g_addr_add_information15
183 , g_addr_add_information16
184 , g_addr_add_information17
185 , g_addr_add_information18
186 , g_addr_add_information19
187 , g_addr_add_information20
188 , g_addr_address;
189 --
190 l_count := c_psn_addrss_details%rowcount;
191 --
192 CLOSE c_psn_addrss_details;
193 --
194 IF l_count = 0 THEN
195 g_addr_address_style := NULL;
196 g_addr_address_line1 := NULL;
197 g_addr_address_line2 := NULL;
198 g_addr_address_line3 := NULL;
199 g_addr_address_type := NULL;
200 g_addr_country := NULL;
201 g_addr_postal_code := NULL;
202 g_addr_region_1 := NULL;
203 g_addr_region_2 := NULL;
204 g_addr_region_3 := NULL;
205 g_addr_town_or_city := NULL;
206 g_addr_add_information13 := NULL;
207 g_addr_add_information14 := NULL;
208 g_addr_add_information15 := NULL;
209 g_addr_add_information16 := NULL;
210 g_addr_add_information17 := NULL;
211 g_addr_add_information18 := NULL;
212 g_addr_add_information19 := NULL;
213 g_addr_add_information20 := NULL;
214 g_addr_address := NULL;
215 --
216 -- No point in proceding if there are no address components
217 --
218 RETURN;
219 --
220 END IF;
221 --
222 END IF;
223 --
224 -- Address structure
225 --
226 -- If necessary default the style to US address
227 --
228 IF g_addr_address_style IS NULL
229 THEN
230 --
231 g_addr_address_style := 'US';
232 --
233 END IF;
234 --
235 OPEN c_psn_addrss_struct(g_addr_address_style);
236 --
237 LOOP
238 --
239 FETCH c_psn_addrss_struct INTO g_addr_col_name;
240 --
241 EXIT WHEN c_psn_addrss_struct%notfound ;
242 --
243 IF NVL(g_addr_col_name,'X') = 'ADDRESS_LINE1'
244 THEN
245 --
246 format_address(g_addr_address_line1);
247 --
248 ELSIF NVL(g_addr_col_name,'X') = 'ADDRESS_LINE2'
249 THEN
250 --
251 format_address(g_addr_address_line2);
252 --
253 ELSIF NVL(g_addr_col_name,'X') = 'ADDRESS_LINE3'
254 THEN
255 --
256 format_address(g_addr_address_line3);
257 --
258 ELSIF NVL(g_addr_col_name,'X') = 'TOWN_OR_CITY'
259 THEN
260 --
261 format_address(g_addr_town_or_city);
262 --
263 ELSIF NVL(g_addr_col_name,'X') = 'REGION_1'
264 THEN
265 --
266 format_address(g_addr_region_1);
267 --
268 ELSIF NVL(g_addr_col_name,'X') = 'REGION_2'
269 THEN
270 --
271 format_address(g_addr_region_2);
272 --
273 ELSIF NVL(g_addr_col_name,'X') = 'REGION_3'
274 THEN
275 --
276 format_address(g_addr_region_3);
277 --
278 ELSIF NVL(g_addr_col_name,'X') = 'COUNTRY'
279 THEN
280 --
281 format_address(g_addr_country);
282 --
283 ELSIF NVL(g_addr_col_name,'X') = 'POSTAL_CODE'
284 THEN
285 --
286 format_address(g_addr_country);
287 --
288 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION13'
289 THEN
290 --
291 format_address(g_addr_add_information13);
292 --
293 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION14'
294 THEN
295 --
296 format_address(g_addr_add_information14);
297 --
298 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION15'
299 THEN
300 --
301 format_address(g_addr_add_information15);
302 --
303 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION16'
304 THEN
305 --
306 format_address(g_addr_add_information16);
307 --
308 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION17'
309 THEN
310 --
311 format_address(g_addr_add_information17);
312 --
313 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION18'
314 THEN
315 --
316 format_address(g_addr_add_information18);
317 --
318 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION19'
319 THEN
320 --
321 format_address(g_addr_add_information19);
322 --
323 ELSIF NVL(g_addr_col_name,'X') = 'ADD_INFORMATION20'
324 THEN
325 --
326 format_address(g_addr_add_information20);
327 --
328 END IF;
329 --
330 END LOOP;
331 --
332 CLOSE c_psn_addrss_struct;
333 --
334 EXCEPTION
335 --
336 WHEN OTHERS THEN
337 --
338 CLOSE c_psn_addrss_details;
339 --
340 g_addr_address_style := NULL;
341 g_addr_address_line1 := NULL;
342 g_addr_address_line2 := NULL;
343 g_addr_address_line3 := NULL;
344 g_addr_address_type := NULL;
345 g_addr_country := NULL;
346 g_addr_postal_code := NULL;
347 g_addr_region_1 := NULL;
348 g_addr_region_2 := NULL;
349 g_addr_region_3 := NULL;
350 g_addr_town_or_city := NULL;
351 g_addr_add_information13 := NULL;
352 g_addr_add_information14 := NULL;
353 g_addr_add_information15 := NULL;
354 g_addr_add_information16 := NULL;
355 g_addr_add_information17 := NULL;
356 g_addr_add_information18 := NULL;
357 g_addr_add_information19 := NULL;
358 g_addr_add_information20 := NULL;
359 g_addr_address := NULL;
360 g_addr_col_name := NULL;
361 g_person_id := NULL;
362 --
363 --
364 END cache_psn_addrss_details;
365 --
366 -- -----------------------------------------------------------------------------
367 -- Gets the details of a persons emergency contacts
368 -- -----------------------------------------------------------------------------
369 --
370 PROCEDURE cache_psn_emrg_contacts(p_person_id IN NUMBER)
371 IS
372 --
373 -- The cursor gets all the emergency contacts for a given person_id.
374 --
375 CURSOR c_psn_emrg_contacts_details(cp_person_id NUMBER)
376 IS
377 SELECT psn.full_name contact_person_name
378 , pho.phone_number contact_phone_number
379 , pho.phone_type phone_type
380 FROM per_contact_relationships con
381 , per_all_people_f psn
382 , per_phones pho
383 WHERE psn.person_id = con.contact_person_id
384 AND psn.person_id = pho.parent_id (+)
385 AND pho.parent_table= 'PER_ALL_PEOPLE_F'
386 AND con.person_id = cp_person_id
387 AND con.contact_type = 'EMRG'
388 AND TRUNC(sysdate)
389 BETWEEN psn.effective_start_date
390 AND psn.effective_end_date
391 AND TRUNC(SYSDATE)
392 BETWEEN con.date_start
393 AND NVL(con.date_end
394 ,hr_general.end_of_time)
395 AND TRUNC(SYSDATE)
396 BETWEEN NVL(pho.date_from
397 ,hr_general.start_of_time)
398 AND NVL(pho.date_to
399 ,hr_general.end_of_time);
400 --
401 v_full_name VARCHAR2(2000) := NULL;
402 --
403 BEGIN
404 --
405 IF p_person_id IS NULL THEN
406 --
407 g_emrg_contacts := NULL;
408 g_emrg_full_name := NULL;
409 g_emrg_phone_number := NULL;
410 g_emrg_phone_type := NULL;
411 --
412 -- If not already cached then retrieve contact
413 --
414 ELSIF p_person_id <> NVL(g_emrg_person_id,-1)
415 THEN
416 --
417 g_emrg_contacts := NULL;
418 g_emrg_full_name := NULL;
419 g_emrg_phone_number := NULL;
420 g_emrg_phone_type := NULL;
421 --
422 g_emrg_person_id := p_person_id;
423 --
424 OPEN c_psn_emrg_contacts_details(p_person_id);
425 --
426 LOOP
427 --
428 FETCH c_psn_emrg_contacts_details
429 INTO g_emrg_full_name
430 , g_emrg_phone_number
431 , g_emrg_phone_type;
432 --
433 If c_psn_emrg_contacts_details%notfound THEN
434 --
435 EXIT;
436 --
437 END IF;
438 --
439 -- format the contact depending if this is an additional contact no.
440 --
441 IF v_full_name = g_emrg_full_name THEN
442 --
443 format_emrg_contacts(g_emrg_phone_number
444 ,hr_view_alert_trnslt.psn_lng_decode_lookup
448 --
445 ('PHONE_TYPE'
446 ,g_emrg_phone_type
447 ,g_emrg_person_id));
449 ELSE
450 --
451 format_emrg_contacts(g_emrg_full_name
452 ,g_emrg_phone_number
453 ,hr_view_alert_trnslt.psn_lng_decode_lookup
454 ('PHONE_TYPE'
455 ,g_emrg_phone_type
456 ,g_emrg_person_id));
457 --
458 v_full_name := g_emrg_full_name;
459 --
460 END IF;
461 --
462 END LOOP;
463 --
464 CLOSE c_psn_emrg_contacts_details;
465 --
466 RETURN;
467 --
468 END IF;
469 --
470 EXCEPTION
471 --
472 WHEN OTHERS THEN
473 --
474 CLOSE c_psn_emrg_contacts_details;
475 --
476 g_emrg_contacts := NULL;
477 g_emrg_full_name := NULL;
478 g_emrg_phone_number := NULL;
479 g_emrg_phone_type := NULL;
480 --
481 --
482 END cache_psn_emrg_contacts;
483 --
484 -- -----------------------------------------------------------------------------
485 -- Gets a single string containing the address
486 -- -----------------------------------------------------------------------------
487 --
488 FUNCTION get_psn_addrss(p_person_id IN NUMBER) RETURN VARCHAR2 IS
489 BEGIN
490 --
491 cache_psn_addrss_details(p_person_id);
492 --
493 RETURN g_addr_address;
494 --
495 END get_psn_addrss;
496 --
497 -- -----------------------------------------------------------------------------
498 -- Gets a single string containing the emergency contact details
499 -- -----------------------------------------------------------------------------
500 --
501 FUNCTION get_psn_emrg_contacts(p_person_id IN NUMBER) RETURN VARCHAR2 IS
502 BEGIN
503 --
504 cache_psn_emrg_contacts(p_person_id);
505 --
506 RETURN g_emrg_contacts;
507 --
508 END get_psn_emrg_contacts;
509 --
510 -- -----------------------------------------------------------------------------
511 --
512 END HR_BPL_ALERT_ADDRESS;