[Home] [Help]
PACKAGE BODY: APPS.PER_JP_DEPENDENT_PKG
Source
1 PACKAGE BODY per_jp_dependent_pkg AS
2 /* $Header: pejpdepf.pkb 115.3 2003/12/09 22:12:09 ttagawa noship $ */
3 --
4 FUNCTION address_count(
5 p_person_id IN NUMBER,
6 p_effective_date IN DATE) RETURN NUMBER IS
7 --
8 CURSOR cel_address_exist IS
9 SELECT COUNT(person_id) FROM per_addresses
10 WHERE person_id = p_person_id
11 AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
12 --
13 l_address_count NUMBER;
14 BEGIN
15 --
16 OPEN cel_address_exist;
17 FETCH cel_address_exist INTO l_address_count;
18 CLOSE cel_address_exist;
19 --
20 RETURN(l_address_count);
21 --
22 END address_count;
23 --
24 FUNCTION address_style(
25 p_style IN VARCHAR2) RETURN VARCHAR2 IS
26 --
27 CURSOR cel_address_style IS
28 SELECT descriptive_flex_context_name
29 FROM fnd_descr_flex_contexts_vl
30 WHERE application_id = 800
31 AND descriptive_flexfield_name = 'Address Structure'
32 AND descriptive_flex_context_code = p_style
33 AND enabled_flag = 'Y'
34 AND descriptive_flex_context_code NOT IN ('Global Data Elements','GENERIC')
35 AND (hr_general.chk_geocodes_installed = 'Y'
36 OR descriptive_flex_context_code NOT IN ('CA','US'));
37 --
38 l_style_meaning VARCHAR2(80);
39 --
40 BEGIN
41 --
42 OPEN cel_address_style;
43 FETCH cel_address_style INTO l_style_meaning;
44 CLOSE cel_address_style;
45 --
46 RETURN l_style_meaning;
47 --
48 END address_style;
49 --
50 PROCEDURE address_detail(
51 p_person_id IN NUMBER,
52 p_effective_date IN DATE,
53 p_address_type_meaning OUT NOCOPY VARCHAR2,
54 p_style OUT NOCOPY VARCHAR2,
55 p_primary_flag OUT NOCOPY VARCHAR2,
56 p_address_line1 OUT NOCOPY VARCHAR2,
57 p_address_line2 OUT NOCOPY VARCHAR2,
58 p_address_line3 OUT NOCOPY VARCHAR2,
59 p_country OUT NOCOPY VARCHAR2,
60 p_postal_code OUT NOCOPY VARCHAR2,
61 p_region_1 OUT NOCOPY VARCHAR2,
62 p_region_2 OUT NOCOPY VARCHAR2,
63 p_region_3 OUT NOCOPY VARCHAR2,
64 p_telephone_number_1 OUT NOCOPY VARCHAR2,
65 p_telephone_number_2 OUT NOCOPY VARCHAR2,
66 p_telephone_number_3 OUT NOCOPY VARCHAR2,
67 p_town_or_city OUT NOCOPY VARCHAR2,
68 p_add_information13 OUT NOCOPY VARCHAR2,
69 p_add_information14 OUT NOCOPY VARCHAR2,
70 p_add_information15 OUT NOCOPY VARCHAR2,
71 p_add_information16 OUT NOCOPY VARCHAR2,
72 p_add_information17 OUT NOCOPY VARCHAR2,
73 p_add_information18 OUT NOCOPY VARCHAR2,
74 p_add_information19 OUT NOCOPY VARCHAR2,
75 p_add_information20 OUT NOCOPY VARCHAR2,
76 p_date_from OUT NOCOPY DATE,
77 p_date_to OUT NOCOPY DATE) IS
78 --
79 CURSOR cel_address_detail IS
80 SELECT
81 SUBSTRB(hr_general.decode_lookup('ADDRESS_TYPE',address_type),1,80) address_type_meaning,
82 style,
83 primary_flag,
84 address_line1,
85 address_line2,
86 address_line3,
87 country,
88 postal_code,
89 region_1,
90 region_2,
91 region_3,
92 telephone_number_1,
93 telephone_number_2,
94 telephone_number_3,
95 town_or_city,
96 add_information13,
97 add_information14,
98 add_information15,
99 add_information16,
100 add_information17,
101 add_information18,
102 add_information19,
103 add_information20,
104 date_from,
105 date_to
106 FROM per_addresses
107 WHERE person_id = p_person_id
108 AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
109 --
110 celrec_address_detail cel_address_detail%ROWTYPE;
111 --
112 BEGIN
113 --
114 OPEN cel_address_detail;
115 FETCH cel_address_detail INTO celrec_address_detail;
116 --
117 p_address_type_meaning := celrec_address_detail.address_type_meaning;
118 p_style := celrec_address_detail.style;
119 p_primary_flag := celrec_address_detail.primary_flag;
120 p_date_from := celrec_address_detail.date_from;
121 p_date_to := celrec_address_detail.date_to;
122 p_address_line1 := celrec_address_detail.address_line1;
123 p_address_line2 := celrec_address_detail.address_line2;
124 p_address_line3 := celrec_address_detail.address_line3;
125 p_country := celrec_address_detail.country;
126 p_postal_code := celrec_address_detail.postal_code;
127 p_region_1 := celrec_address_detail.region_1;
128 p_region_2 := celrec_address_detail.region_2;
129 p_region_3 := celrec_address_detail.region_3;
130 p_telephone_number_1 := celrec_address_detail.telephone_number_1;
131 p_telephone_number_2 := celrec_address_detail.telephone_number_2;
132 p_telephone_number_3 := celrec_address_detail.telephone_number_3;
133 p_town_or_city := celrec_address_detail.town_or_city;
134 p_add_information13 := celrec_address_detail.add_information13;
135 p_add_information14 := celrec_address_detail.add_information14;
136 p_add_information15 := celrec_address_detail.add_information15;
137 p_add_information16 := celrec_address_detail.add_information16;
138 p_add_information17 := celrec_address_detail.add_information17;
139 p_add_information18 := celrec_address_detail.add_information18;
140 p_add_information19 := celrec_address_detail.add_information19;
141 p_add_information20 := celrec_address_detail.add_information20;
142 --
143 CLOSE cel_address_detail;
144 --
145 END address_detail;
146 --
147 PROCEDURE populate_address_fields(
148 p_person_id IN NUMBER,
149 p_effective_date IN DATE,
150 p_count OUT NOCOPY NUMBER,
151 p_d_address_type OUT NOCOPY VARCHAR2,
152 p_style OUT NOCOPY VARCHAR2,
153 p_d_style OUT NOCOPY VARCHAR2,
154 p_primary_flag OUT NOCOPY VARCHAR2,
155 p_address_line1 OUT NOCOPY VARCHAR2,
156 p_address_line2 OUT NOCOPY VARCHAR2,
157 p_address_line3 OUT NOCOPY VARCHAR2,
158 p_country OUT NOCOPY VARCHAR2,
159 p_postal_code OUT NOCOPY VARCHAR2,
160 p_region_1 OUT NOCOPY VARCHAR2,
161 p_region_2 OUT NOCOPY VARCHAR2,
162 p_region_3 OUT NOCOPY VARCHAR2,
163 p_telephone_number_1 OUT NOCOPY VARCHAR2,
164 p_telephone_number_2 OUT NOCOPY VARCHAR2,
165 p_telephone_number_3 OUT NOCOPY VARCHAR2,
166 p_town_or_city OUT NOCOPY VARCHAR2,
167 p_add_information13 OUT NOCOPY VARCHAR2,
168 p_add_information14 OUT NOCOPY VARCHAR2,
169 p_add_information15 OUT NOCOPY VARCHAR2,
170 p_add_information16 OUT NOCOPY VARCHAR2,
171 p_add_information17 OUT NOCOPY VARCHAR2,
172 p_add_information18 OUT NOCOPY VARCHAR2,
173 p_add_information19 OUT NOCOPY VARCHAR2,
174 p_add_information20 OUT NOCOPY VARCHAR2,
175 p_date_from OUT NOCOPY DATE,
176 p_date_to OUT NOCOPY DATE) IS
177 --
178 l_address_count NUMBER := address_count(p_person_id, p_effective_date);
179 --
180 BEGIN
181 --
182 p_count := l_address_count;
183 --
184 IF l_address_count = 0 THEN
185 --
186 p_d_address_type := NULL;
187 p_style := NULL;
188 p_d_style := NULL;
189 p_primary_flag := NULL;
190 p_address_line1 := NULL;
191 p_address_line2 := NULL;
192 p_address_line3 := NULL;
193 p_country := NULL;
194 p_postal_code := NULL;
195 p_region_1 := NULL;
196 p_region_2 := NULL;
197 p_region_3 := NULL;
198 p_telephone_number_1 := NULL;
199 p_telephone_number_2 := NULL;
200 p_telephone_number_3 := NULL;
201 p_town_or_city := NULL;
202 p_add_information13 := NULL;
203 p_add_information14 := NULL;
204 p_add_information15 := NULL;
205 p_add_information16 := NULL;
206 p_add_information17 := NULL;
207 p_add_information18 := NULL;
208 p_add_information19 := NULL;
209 p_add_information20 := NULL;
210 p_date_from := NULL;
211 p_date_to := NULL;
212 --
213 ELSIF l_address_count = 1 THEN
214 --
215 address_detail(
216 p_person_id => p_person_id,
217 p_effective_date => p_effective_date,
218 p_address_type_meaning => p_d_address_type,
219 p_style => p_style,
220 p_primary_flag => p_primary_flag,
221 p_address_line1 => p_address_line1,
222 p_address_line2 => p_address_line2,
223 p_address_line3 => p_address_line3,
224 p_country => p_country,
225 p_postal_code => p_postal_code,
226 p_region_1 => p_region_1,
227 p_region_2 => p_region_2,
228 p_region_3 => p_region_3,
229 p_telephone_number_1 => p_telephone_number_1,
230 p_telephone_number_2 => p_telephone_number_2,
231 p_telephone_number_3 => p_telephone_number_3,
232 p_town_or_city => p_town_or_city,
233 p_add_information13 => p_add_information13,
234 p_add_information14 => p_add_information14,
235 p_add_information15 => p_add_information15,
236 p_add_information16 => p_add_information16,
237 p_add_information17 => p_add_information17,
238 p_add_information18 => p_add_information18,
239 p_add_information19 => p_add_information19,
240 p_add_information20 => p_add_information20,
241 p_date_from => p_date_from,
242 p_date_to => p_date_to);
243 --
244 p_d_style := address_style(p_style);
245 --
246 ELSE
247 --
248 hr_utility.set_message(
249 applid => 800,
250 l_message_name => 'PER_JP_ALL_COUNT_ADDRESSES');
251 --
252 p_d_address_type := '** ' || l_address_count || ' ' || hr_utility.get_message;
253 p_style := NULL;
254 p_d_style := NULL;
255 p_primary_flag := NULL;
256 p_address_line1 := NULL;
257 p_address_line2 := NULL;
258 p_address_line3 := NULL;
259 p_country := NULL;
260 p_postal_code := NULL;
261 p_region_1 := NULL;
262 p_region_2 := NULL;
263 p_region_3 := NULL;
264 p_telephone_number_1 := NULL;
265 p_telephone_number_2 := NULL;
266 p_telephone_number_3 := NULL;
267 p_town_or_city := NULL;
268 p_add_information13 := NULL;
269 p_add_information14 := NULL;
270 p_add_information15 := NULL;
271 p_add_information16 := NULL;
272 p_add_information17 := NULL;
273 p_add_information18 := NULL;
274 p_add_information19 := NULL;
275 p_add_information20 := NULL;
276 p_date_from := NULL;
277 p_date_to := NULL;
278 --
279 END IF;
280 --
281 END populate_address_fields;
282 --
283 FUNCTION phone_count(
284 p_person_id IN NUMBER,
285 p_effective_date IN DATE) RETURN NUMBER IS
286 --
287 CURSOR cel_phone_exist IS
288 SELECT COUNT(parent_id) FROM per_phones
289 WHERE parent_id = p_person_id
290 AND parent_table = 'PER_ALL_PEOPLE_F'
291 AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
292 --
293 l_phone_count NUMBER;
294 --
295 BEGIN
296 --
297 OPEN cel_phone_exist;
298 FETCH cel_phone_exist INTO l_phone_count;
299 CLOSE cel_phone_exist;
300 --
301 RETURN(l_phone_count);
302 --
303 END phone_count;
304 --
305 PROCEDURE phone_detail(
306 p_person_id IN NUMBER,
307 p_effective_date IN DATE,
308 p_phone_type_meaning OUT NOCOPY VARCHAR2,
309 p_phone_number OUT NOCOPY VARCHAR2,
310 p_date_from OUT NOCOPY DATE,
311 p_date_to OUT NOCOPY DATE) IS
312 --
313 CURSOR cel_phone_detail IS
314 SELECT
315 SUBSTRB(hr_general.decode_lookup('PHONE_TYPE',phone_type),1,80) phone_type_meaning,
316 phone_number,
317 date_from,
318 date_to
319 FROM per_phones
320 WHERE parent_id = p_person_id
321 AND parent_table = 'PER_ALL_PEOPLE_F'
322 AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
323 --
324 celrec_phone_detail cel_phone_detail%ROWTYPE;
325 --
326 BEGIN
327 --
328 OPEN cel_phone_detail;
329 FETCH cel_phone_detail INTO celrec_phone_detail;
330 --
331 p_phone_type_meaning := celrec_phone_detail.phone_type_meaning;
332 p_phone_number := celrec_phone_detail.phone_number;
333 p_date_from := celrec_phone_detail.date_from;
334 p_date_to := celrec_phone_detail.date_to;
335 --
336 CLOSE cel_phone_detail;
337 --
338 END phone_detail;
339 --
340 PROCEDURE populate_phone_fields(
341 p_person_id IN NUMBER,
342 p_effective_date IN DATE,
343 p_count OUT NOCOPY NUMBER,
344 p_d_phone_type OUT NOCOPY VARCHAR2,
345 p_phone_number OUT NOCOPY VARCHAR2,
346 p_date_from OUT NOCOPY DATE,
347 p_date_to OUT NOCOPY DATE) IS
348 --
349 l_phone_count NUMBER := phone_count(p_person_id,p_effective_date);
350 --
351 BEGIN
352 --
353 p_count := l_phone_count;
354 --
355 IF l_phone_count = 0 THEN
356 --
357 p_d_phone_type := NULL;
358 p_phone_number := NULL;
359 p_date_from := NULL;
360 p_date_to := NULL;
361 --
362 ELSIF l_phone_count = 1 THEN
363 --
364 phone_detail(
365 p_person_id => p_person_id,
366 p_effective_date => p_effective_date,
367 p_phone_type_meaning => p_d_phone_type,
368 p_phone_number => p_phone_number,
369 p_date_from => p_date_from,
370 p_date_to => p_date_to);
371 --
372 ELSE
373 --
374 hr_utility.set_message(
375 applid => 800,
379 p_phone_number := NULL;
376 l_message_name => 'PER_JP_ALL_COUNT_PHONES');
377 --
378 p_d_phone_type := '** ' || l_phone_count || ' ' || hr_utility.get_message;
380 p_date_from := NULL;
381 p_date_to := NULL;
382 --
383 END IF;
384 --
385 END populate_phone_fields;
386 --
387 FUNCTION extra_info_count(
388 p_contact_relationship_id IN NUMBER,
389 p_effective_date IN DATE,
390 p_information_type_group IN VARCHAR2) RETURN NUMBER IS
391 --
392 CURSOR cel_extra_info_exist IS
393 SELECT COUNT(contact_relationship_id) FROM per_contact_extra_info_f
394 WHERE contact_relationship_id = p_contact_relationship_id
395 AND information_type LIKE p_information_type_group || '%'
396 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
397 --
398 l_extra_info_count NUMBER;
399 BEGIN
400 --
401 OPEN cel_extra_info_exist;
402 FETCH cel_extra_info_exist INTO l_extra_info_count;
403 CLOSE cel_extra_info_exist;
404 --
405 RETURN(l_extra_info_count);
406 --
407 END extra_info_count;
408 --
409 PROCEDURE extra_info_detail(
410 p_contact_relationship_id IN NUMBER,
411 p_effective_date IN DATE,
412 p_information_type_group IN VARCHAR2,
413 p_contact_extra_info_id OUT NOCOPY NUMBER,
414 p_information_type OUT NOCOPY VARCHAR2,
415 p_d_information_type OUT NOCOPY VARCHAR2,
416 p_cei_information_category OUT NOCOPY VARCHAR2,
417 p_cei_information1 OUT NOCOPY VARCHAR2,
418 p_cei_information2 OUT NOCOPY VARCHAR2,
419 p_cei_information3 OUT NOCOPY VARCHAR2,
420 p_cei_information4 OUT NOCOPY VARCHAR2,
421 p_cei_information5 OUT NOCOPY VARCHAR2,
422 p_cei_information6 OUT NOCOPY VARCHAR2,
423 p_cei_information7 OUT NOCOPY VARCHAR2,
424 p_cei_information8 OUT NOCOPY VARCHAR2,
425 p_cei_information9 OUT NOCOPY VARCHAR2,
426 p_cei_information10 OUT NOCOPY VARCHAR2,
427 p_cei_information11 OUT NOCOPY VARCHAR2,
428 p_cei_information12 OUT NOCOPY VARCHAR2,
429 p_cei_information13 OUT NOCOPY VARCHAR2,
430 p_cei_information14 OUT NOCOPY VARCHAR2,
431 p_cei_information15 OUT NOCOPY VARCHAR2,
432 p_cei_information16 OUT NOCOPY VARCHAR2,
433 p_cei_information17 OUT NOCOPY VARCHAR2,
434 p_cei_information18 OUT NOCOPY VARCHAR2,
435 p_cei_information19 OUT NOCOPY VARCHAR2,
436 p_cei_information20 OUT NOCOPY VARCHAR2,
437 p_cei_information21 OUT NOCOPY VARCHAR2,
438 p_cei_information22 OUT NOCOPY VARCHAR2,
439 p_cei_information23 OUT NOCOPY VARCHAR2,
440 p_cei_information24 OUT NOCOPY VARCHAR2,
441 p_cei_information25 OUT NOCOPY VARCHAR2,
442 p_cei_information26 OUT NOCOPY VARCHAR2,
443 p_cei_information27 OUT NOCOPY VARCHAR2,
444 p_cei_information28 OUT NOCOPY VARCHAR2,
445 p_cei_information29 OUT NOCOPY VARCHAR2,
446 p_cei_information30 OUT NOCOPY VARCHAR2,
447 p_effective_start_date OUT NOCOPY DATE,
448 p_effective_end_date OUT NOCOPY DATE,
449 p_cei_attribute_category OUT NOCOPY VARCHAR2,
450 p_cei_attribute1 OUT NOCOPY VARCHAR2,
451 p_cei_attribute2 OUT NOCOPY VARCHAR2,
452 p_cei_attribute3 OUT NOCOPY VARCHAR2,
453 p_cei_attribute4 OUT NOCOPY VARCHAR2,
454 p_cei_attribute5 OUT NOCOPY VARCHAR2,
455 p_cei_attribute6 OUT NOCOPY VARCHAR2,
456 p_cei_attribute7 OUT NOCOPY VARCHAR2,
457 p_cei_attribute8 OUT NOCOPY VARCHAR2,
458 p_cei_attribute9 OUT NOCOPY VARCHAR2,
459 p_cei_attribute10 OUT NOCOPY VARCHAR2,
460 p_cei_attribute11 OUT NOCOPY VARCHAR2,
461 p_cei_attribute12 OUT NOCOPY VARCHAR2,
462 p_cei_attribute13 OUT NOCOPY VARCHAR2,
463 p_cei_attribute14 OUT NOCOPY VARCHAR2,
464 p_cei_attribute15 OUT NOCOPY VARCHAR2,
465 p_cei_attribute16 OUT NOCOPY VARCHAR2,
466 p_cei_attribute17 OUT NOCOPY VARCHAR2,
467 p_cei_attribute18 OUT NOCOPY VARCHAR2,
468 p_cei_attribute19 OUT NOCOPY VARCHAR2,
469 p_cei_attribute20 OUT NOCOPY VARCHAR2,
470 p_object_version_number OUT NOCOPY NUMBER,
471 p_last_update_date OUT NOCOPY DATE,
472 p_last_updated_by OUT NOCOPY NUMBER,
473 p_last_update_login OUT NOCOPY NUMBER,
474 p_created_by OUT NOCOPY NUMBER,
475 p_creation_date OUT NOCOPY DATE,
476 p_request_id OUT NOCOPY NUMBER,
477 p_program_application_id OUT NOCOPY NUMBER,
478 p_program_id OUT NOCOPY NUMBER,
479 p_program_update_date OUT NOCOPY DATE) IS
480 --
481 CURSOR cel_extra_info_detail IS
482 SELECT
483 pceif.contact_extra_info_id,
484 pceif.information_type,
485 pcitv.description,
486 pceif.cei_information_category,
487 pceif.cei_information1,
488 pceif.cei_information2,
489 pceif.cei_information3,
490 pceif.cei_information4,
491 pceif.cei_information5,
492 pceif.cei_information6,
493 pceif.cei_information7,
494 pceif.cei_information8,
495 pceif.cei_information9,
496 pceif.cei_information10,
497 pceif.cei_information11,
498 pceif.cei_information12,
499 pceif.cei_information13,
500 pceif.cei_information14,
501 pceif.cei_information15,
502 pceif.cei_information16,
503 pceif.cei_information17,
504 pceif.cei_information18,
505 pceif.cei_information19,
506 pceif.cei_information20,
507 pceif.cei_information21,
508 pceif.cei_information22,
509 pceif.cei_information23,
513 pceif.cei_information27,
510 pceif.cei_information24,
511 pceif.cei_information25,
512 pceif.cei_information26,
514 pceif.cei_information28,
515 pceif.cei_information29,
516 pceif.cei_information30,
517 pceif.effective_start_date,
518 pceif.effective_end_date,
519 pceif.cei_attribute_category,
520 pceif.cei_attribute1,
521 pceif.cei_attribute2,
522 pceif.cei_attribute3,
523 pceif.cei_attribute4,
524 pceif.cei_attribute5,
525 pceif.cei_attribute6,
526 pceif.cei_attribute7,
527 pceif.cei_attribute8,
528 pceif.cei_attribute9,
529 pceif.cei_attribute10,
530 pceif.cei_attribute11,
531 pceif.cei_attribute12,
532 pceif.cei_attribute13,
533 pceif.cei_attribute14,
534 pceif.cei_attribute15,
535 pceif.cei_attribute16,
536 pceif.cei_attribute17,
537 pceif.cei_attribute18,
538 pceif.cei_attribute19,
539 pceif.cei_attribute20,
540 pceif.object_version_number,
541 pceif.last_update_date,
542 pceif.last_updated_by,
543 pceif.last_update_login,
544 pceif.created_by,
545 pceif.creation_date,
546 pceif.request_id,
547 pceif.program_application_id,
548 pceif.program_id,
549 pceif.program_update_date
550 FROM
551 per_contact_extra_info_f pceif,
552 per_contact_info_types_vl pcitv
553 WHERE pceif.contact_relationship_id = p_contact_relationship_id
554 AND pceif.information_type LIKE p_information_type_group || '%'
555 AND p_effective_date BETWEEN pceif.effective_start_date AND pceif.effective_end_date
556 AND pceif.information_type = pcitv.information_type
557 -- /* Added by keyazawa at 2003/10/02 for bugfix 3047148. */
558 and exists(
559 select null
560 from per_contact_relationships pcr,
561 per_business_groups_perf pbg,
562 per_info_type_security_cit_v pitsc
563 -- /* This relation is to fetch legislation code, ideally this should be parameter. */
564 where pcr.contact_relationship_id = pceif.contact_relationship_id
565 and pbg.business_group_id = pcr.business_group_id
566 -- /* This sql should be called by EBS, ideally these should be parameter.
567 -- fnd_global is better than fnd_profile.value. */
568 and pitsc.application_id = fnd_global.resp_appl_id
569 and pitsc.responsibility_id = fnd_global.resp_id
570 and pitsc.information_type = pceif.information_type
571 and pitsc.legislation_code = nvl(pbg.legislation_code,pitsc.legislation_code));
572 --
573 celrec_extra_info_detail cel_extra_info_detail%ROWTYPE;
574 --
575 BEGIN
576 --
577 OPEN cel_extra_info_detail;
578 FETCH cel_extra_info_detail INTO celrec_extra_info_detail;
579 --
580 p_contact_extra_info_id := celrec_extra_info_detail.contact_extra_info_id;
581 p_information_type := celrec_extra_info_detail.information_type;
582 p_d_information_type := celrec_extra_info_detail.description;
583 p_cei_information_category := celrec_extra_info_detail.cei_information_category;
584 p_cei_information1 := celrec_extra_info_detail.cei_information1;
585 p_cei_information2 := celrec_extra_info_detail.cei_information2;
586 p_cei_information3 := celrec_extra_info_detail.cei_information3;
587 p_cei_information4 := celrec_extra_info_detail.cei_information4;
588 p_cei_information5 := celrec_extra_info_detail.cei_information5;
589 p_cei_information6 := celrec_extra_info_detail.cei_information6;
590 p_cei_information7 := celrec_extra_info_detail.cei_information7;
591 p_cei_information8 := celrec_extra_info_detail.cei_information8;
592 p_cei_information9 := celrec_extra_info_detail.cei_information9;
593 p_cei_information10 := celrec_extra_info_detail.cei_information10;
594 p_cei_information11 := celrec_extra_info_detail.cei_information11;
595 p_cei_information12 := celrec_extra_info_detail.cei_information12;
596 p_cei_information13 := celrec_extra_info_detail.cei_information13;
597 p_cei_information14 := celrec_extra_info_detail.cei_information14;
598 p_cei_information15 := celrec_extra_info_detail.cei_information15;
599 p_cei_information16 := celrec_extra_info_detail.cei_information16;
600 p_cei_information17 := celrec_extra_info_detail.cei_information17;
601 p_cei_information18 := celrec_extra_info_detail.cei_information18;
602 p_cei_information19 := celrec_extra_info_detail.cei_information19;
603 p_cei_information20 := celrec_extra_info_detail.cei_information20;
604 p_cei_information21 := celrec_extra_info_detail.cei_information21;
605 p_cei_information22 := celrec_extra_info_detail.cei_information22;
606 p_cei_information23 := celrec_extra_info_detail.cei_information23;
607 p_cei_information24 := celrec_extra_info_detail.cei_information24;
608 p_cei_information25 := celrec_extra_info_detail.cei_information25;
609 p_cei_information26 := celrec_extra_info_detail.cei_information26;
610 p_cei_information27 := celrec_extra_info_detail.cei_information27;
611 p_cei_information28 := celrec_extra_info_detail.cei_information28;
612 p_cei_information29 := celrec_extra_info_detail.cei_information29;
613 p_cei_information30 := celrec_extra_info_detail.cei_information30;
614 p_effective_start_date := celrec_extra_info_detail.effective_start_date;
615 p_effective_end_date := celrec_extra_info_detail.effective_end_date;
619 p_cei_attribute3 := celrec_extra_info_detail.cei_attribute3;
616 p_cei_attribute_category := celrec_extra_info_detail.cei_attribute_category;
617 p_cei_attribute1 := celrec_extra_info_detail.cei_attribute1;
618 p_cei_attribute2 := celrec_extra_info_detail.cei_attribute2;
620 p_cei_attribute4 := celrec_extra_info_detail.cei_attribute4;
621 p_cei_attribute5 := celrec_extra_info_detail.cei_attribute5;
622 p_cei_attribute6 := celrec_extra_info_detail.cei_attribute6;
623 p_cei_attribute7 := celrec_extra_info_detail.cei_attribute7;
624 p_cei_attribute8 := celrec_extra_info_detail.cei_attribute8;
625 p_cei_attribute9 := celrec_extra_info_detail.cei_attribute9;
626 p_cei_attribute10 := celrec_extra_info_detail.cei_attribute10;
627 p_cei_attribute11 := celrec_extra_info_detail.cei_attribute11;
628 p_cei_attribute12 := celrec_extra_info_detail.cei_attribute12;
629 p_cei_attribute13 := celrec_extra_info_detail.cei_attribute13;
630 p_cei_attribute14 := celrec_extra_info_detail.cei_attribute14;
631 p_cei_attribute15 := celrec_extra_info_detail.cei_attribute15;
632 p_cei_attribute16 := celrec_extra_info_detail.cei_attribute16;
633 p_cei_attribute17 := celrec_extra_info_detail.cei_attribute17;
634 p_cei_attribute18 := celrec_extra_info_detail.cei_attribute18;
635 p_cei_attribute19 := celrec_extra_info_detail.cei_attribute19;
636 p_cei_attribute20 := celrec_extra_info_detail.cei_attribute20;
637 p_object_version_number := celrec_extra_info_detail.object_version_number;
638 p_last_update_date := celrec_extra_info_detail.last_update_date;
639 p_last_updated_by := celrec_extra_info_detail.last_updated_by;
640 p_last_update_login := celrec_extra_info_detail.last_update_login;
641 p_created_by := celrec_extra_info_detail.created_by;
642 p_creation_date := celrec_extra_info_detail.creation_date;
643 p_request_id := celrec_extra_info_detail.request_id;
644 p_program_application_id := celrec_extra_info_detail.program_application_id;
645 p_program_id := celrec_extra_info_detail.program_id;
646 p_program_update_date := celrec_extra_info_detail.program_update_date;
647 --
648 CLOSE cel_extra_info_detail;
649 --
650 END extra_info_detail;
651 --
652 PROCEDURE populate_extra_info_field(
653 p_contact_relationship_id IN NUMBER,
654 p_effective_date IN DATE,
655 p_information_type_group IN VARCHAR2,
656 p_contact_extra_info_id OUT NOCOPY NUMBER,
657 p_information_type OUT NOCOPY VARCHAR2,
658 p_d_information_type OUT NOCOPY VARCHAR2,
659 p_cei_information_category OUT NOCOPY VARCHAR2,
660 p_cei_information1 OUT NOCOPY VARCHAR2,
661 p_cei_information2 OUT NOCOPY VARCHAR2,
662 p_cei_information3 OUT NOCOPY VARCHAR2,
663 p_cei_information4 OUT NOCOPY VARCHAR2,
664 p_cei_information5 OUT NOCOPY VARCHAR2,
665 p_cei_information6 OUT NOCOPY VARCHAR2,
666 p_cei_information7 OUT NOCOPY VARCHAR2,
667 p_cei_information8 OUT NOCOPY VARCHAR2,
668 p_cei_information9 OUT NOCOPY VARCHAR2,
669 p_cei_information10 OUT NOCOPY VARCHAR2,
670 p_cei_information11 OUT NOCOPY VARCHAR2,
671 p_cei_information12 OUT NOCOPY VARCHAR2,
672 p_cei_information13 OUT NOCOPY VARCHAR2,
673 p_cei_information14 OUT NOCOPY VARCHAR2,
674 p_cei_information15 OUT NOCOPY VARCHAR2,
675 p_cei_information16 OUT NOCOPY VARCHAR2,
676 p_cei_information17 OUT NOCOPY VARCHAR2,
677 p_cei_information18 OUT NOCOPY VARCHAR2,
678 p_cei_information19 OUT NOCOPY VARCHAR2,
679 p_cei_information20 OUT NOCOPY VARCHAR2,
680 p_cei_information21 OUT NOCOPY VARCHAR2,
681 p_cei_information22 OUT NOCOPY VARCHAR2,
682 p_cei_information23 OUT NOCOPY VARCHAR2,
683 p_cei_information24 OUT NOCOPY VARCHAR2,
684 p_cei_information25 OUT NOCOPY VARCHAR2,
685 p_cei_information26 OUT NOCOPY VARCHAR2,
686 p_cei_information27 OUT NOCOPY VARCHAR2,
687 p_cei_information28 OUT NOCOPY VARCHAR2,
688 p_cei_information29 OUT NOCOPY VARCHAR2,
689 p_cei_information30 OUT NOCOPY VARCHAR2,
690 p_effective_start_date OUT NOCOPY DATE,
691 p_effective_end_date OUT NOCOPY DATE,
692 p_cei_attribute_category OUT NOCOPY VARCHAR2,
693 p_cei_attribute1 OUT NOCOPY VARCHAR2,
694 p_cei_attribute2 OUT NOCOPY VARCHAR2,
695 p_cei_attribute3 OUT NOCOPY VARCHAR2,
696 p_cei_attribute4 OUT NOCOPY VARCHAR2,
697 p_cei_attribute5 OUT NOCOPY VARCHAR2,
698 p_cei_attribute6 OUT NOCOPY VARCHAR2,
699 p_cei_attribute7 OUT NOCOPY VARCHAR2,
700 p_cei_attribute8 OUT NOCOPY VARCHAR2,
701 p_cei_attribute9 OUT NOCOPY VARCHAR2,
702 p_cei_attribute10 OUT NOCOPY VARCHAR2,
703 p_cei_attribute11 OUT NOCOPY VARCHAR2,
704 p_cei_attribute12 OUT NOCOPY VARCHAR2,
705 p_cei_attribute13 OUT NOCOPY VARCHAR2,
706 p_cei_attribute14 OUT NOCOPY VARCHAR2,
707 p_cei_attribute15 OUT NOCOPY VARCHAR2,
708 p_cei_attribute16 OUT NOCOPY VARCHAR2,
709 p_cei_attribute17 OUT NOCOPY VARCHAR2,
710 p_cei_attribute18 OUT NOCOPY VARCHAR2,
711 p_cei_attribute19 OUT NOCOPY VARCHAR2,
712 p_cei_attribute20 OUT NOCOPY VARCHAR2,
713 p_object_version_number OUT NOCOPY NUMBER,
714 p_last_update_date OUT NOCOPY DATE,
715 p_last_updated_by OUT NOCOPY NUMBER,
716 p_last_update_login OUT NOCOPY NUMBER,
717 p_created_by OUT NOCOPY NUMBER,
718 p_creation_date OUT NOCOPY DATE,
719 p_request_id OUT NOCOPY NUMBER,
720 p_program_application_id OUT NOCOPY NUMBER,
724 --
721 p_program_id OUT NOCOPY NUMBER,
722 p_program_update_date OUT NOCOPY DATE,
723 p_extra_info_count OUT NOCOPY NUMBER) IS
725 l_extra_info_count NUMBER := extra_info_count(p_contact_relationship_id, p_effective_date, p_information_type_group);
726 --
727 BEGIN
728 --
729 p_extra_info_count := l_extra_info_count;
730 --
731 IF l_extra_info_count = 0 THEN
732 --
733 p_contact_extra_info_id := NULL;
734 p_information_type := NULL;
735 p_d_information_type := NULL;
736 p_cei_information_category := NULL;
737 p_cei_information1 := NULL;
738 p_cei_information2 := NULL;
739 p_cei_information3 := NULL;
740 p_cei_information4 := NULL;
741 p_cei_information5 := NULL;
742 p_cei_information6 := NULL;
743 p_cei_information7 := NULL;
744 p_cei_information8 := NULL;
745 p_cei_information9 := NULL;
746 p_cei_information10 := NULL;
747 p_cei_information11 := NULL;
748 p_cei_information12 := NULL;
749 p_cei_information13 := NULL;
750 p_cei_information14 := NULL;
751 p_cei_information15 := NULL;
752 p_cei_information16 := NULL;
753 p_cei_information17 := NULL;
754 p_cei_information18 := NULL;
755 p_cei_information19 := NULL;
756 p_cei_information20 := NULL;
757 p_cei_information21 := NULL;
758 p_cei_information22 := NULL;
759 p_cei_information23 := NULL;
760 p_cei_information24 := NULL;
761 p_cei_information25 := NULL;
762 p_cei_information26 := NULL;
763 p_cei_information27 := NULL;
764 p_cei_information28 := NULL;
765 p_cei_information29 := NULL;
766 p_cei_information30 := NULL;
767 p_effective_start_date := NULL;
768 p_effective_end_date := NULL;
769 p_cei_attribute_category := NULL;
770 p_cei_attribute1 := NULL;
771 p_cei_attribute2 := NULL;
772 p_cei_attribute3 := NULL;
773 p_cei_attribute4 := NULL;
774 p_cei_attribute5 := NULL;
775 p_cei_attribute6 := NULL;
776 p_cei_attribute7 := NULL;
777 p_cei_attribute8 := NULL;
778 p_cei_attribute9 := NULL;
779 p_cei_attribute10 := NULL;
780 p_cei_attribute11 := NULL;
781 p_cei_attribute12 := NULL;
782 p_cei_attribute13 := NULL;
783 p_cei_attribute14 := NULL;
784 p_cei_attribute15 := NULL;
785 p_cei_attribute16 := NULL;
786 p_cei_attribute17 := NULL;
787 p_cei_attribute18 := NULL;
788 p_cei_attribute19 := NULL;
789 p_cei_attribute20 := NULL;
790 p_object_version_number := NULL;
791 p_last_update_date := NULL;
792 p_last_updated_by := NULL;
793 p_last_update_login := NULL;
794 p_created_by := NULL;
795 p_creation_date := NULL;
796 p_request_id := NULL;
797 p_program_application_id := NULL;
798 p_program_id := NULL;
799 p_program_update_date := NULL;
800 --
801 ELSE
802 --
803 extra_info_detail(
804 p_contact_relationship_id => p_contact_relationship_id,
805 p_effective_date => p_effective_date,
806 p_information_type_group => p_information_type_group,
807 p_contact_extra_info_id => p_contact_extra_info_id,
808 p_information_type => p_information_type,
809 p_d_information_type => p_d_information_type,
810 p_cei_information_category => p_cei_information_category,
811 p_cei_information1 => p_cei_information1,
812 p_cei_information2 => p_cei_information2,
813 p_cei_information3 => p_cei_information3,
814 p_cei_information4 => p_cei_information4,
815 p_cei_information5 => p_cei_information5,
816 p_cei_information6 => p_cei_information6,
817 p_cei_information7 => p_cei_information7,
818 p_cei_information8 => p_cei_information8,
819 p_cei_information9 => p_cei_information9,
820 p_cei_information10 => p_cei_information10,
821 p_cei_information11 => p_cei_information11,
822 p_cei_information12 => p_cei_information12,
823 p_cei_information13 => p_cei_information13,
824 p_cei_information14 => p_cei_information14,
825 p_cei_information15 => p_cei_information15,
826 p_cei_information16 => p_cei_information16,
827 p_cei_information17 => p_cei_information17,
828 p_cei_information18 => p_cei_information18,
829 p_cei_information19 => p_cei_information19,
830 p_cei_information20 => p_cei_information20,
831 p_cei_information21 => p_cei_information21,
832 p_cei_information22 => p_cei_information22,
833 p_cei_information23 => p_cei_information23,
834 p_cei_information24 => p_cei_information24,
835 p_cei_information25 => p_cei_information25,
836 p_cei_information26 => p_cei_information26,
837 p_cei_information27 => p_cei_information27,
838 p_cei_information28 => p_cei_information28,
839 p_cei_information29 => p_cei_information29,
840 p_cei_information30 => p_cei_information30,
841 p_effective_start_date => p_effective_start_date,
842 p_effective_end_date => p_effective_end_date,
843 p_cei_attribute_category => p_cei_attribute_category,
844 p_cei_attribute1 => p_cei_attribute1,
845 p_cei_attribute2 => p_cei_attribute2,
846 p_cei_attribute3 => p_cei_attribute3,
847 p_cei_attribute4 => p_cei_attribute4,
848 p_cei_attribute5 => p_cei_attribute5,
849 p_cei_attribute6 => p_cei_attribute6,
850 p_cei_attribute7 => p_cei_attribute7,
851 p_cei_attribute8 => p_cei_attribute8,
852 p_cei_attribute9 => p_cei_attribute9,
853 p_cei_attribute10 => p_cei_attribute10,
854 p_cei_attribute11 => p_cei_attribute11,
855 p_cei_attribute12 => p_cei_attribute12,
856 p_cei_attribute13 => p_cei_attribute13,
857 p_cei_attribute14 => p_cei_attribute14,
858 p_cei_attribute15 => p_cei_attribute15,
859 p_cei_attribute16 => p_cei_attribute16,
860 p_cei_attribute17 => p_cei_attribute17,
861 p_cei_attribute18 => p_cei_attribute18,
862 p_cei_attribute19 => p_cei_attribute19,
863 p_cei_attribute20 => p_cei_attribute20,
864 p_object_version_number => p_object_version_number,
865 p_last_update_date => p_last_update_date,
866 p_last_updated_by => p_last_updated_by,
867 p_last_update_login => p_last_update_login,
868 p_created_by => p_created_by,
869 p_creation_date => p_creation_date,
870 p_request_id => p_request_id,
871 p_program_application_id => p_program_application_id,
872 p_program_id => p_program_id,
873 p_program_update_date => p_program_update_date);
874 --
875 END IF;
876 --
877 END populate_extra_info_field;
878 --
879 PROCEDURE populate_itax_result_field(
880 p_person_id IN NUMBER,
881 p_effective_date IN DATE,
882 p_itax_type_iv_id IN NUMBER,
883 p_deductible_spouse_status OUT NOCOPY VARCHAR2,
884 p_disabled_spouse_status OUT NOCOPY VARCHAR2,
885 p_dependents OUT NOCOPY NUMBER,
886 p_aged_dependents OUT NOCOPY NUMBER,
887 p_aged_parents OUT NOCOPY NUMBER,
888 p_specified_dependents OUT NOCOPY NUMBER,
889 p_ordinary_disabled OUT NOCOPY NUMBER,
890 p_severely_disabled OUT NOCOPY NUMBER,
891 p_severely_disabled_live_with OUT NOCOPY NUMBER) IS
892 --
893 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
894 l_itax_type hr_lookups.lookup_code%TYPE;
895 l_deductible_spouse_status VARCHAR2(30);
896 l_disabled_spouse_status VARCHAR2(30);
897 l_minor_dpnts NUMBER;
898 l_multiple_spouses_warning BOOLEAN;
899 --
900 CURSOR cel_assignment_exists IS
901 SELECT assignment_id FROM per_all_assignments_f
902 WHERE person_id = p_person_id
903 AND primary_flag = 'Y'
904 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
905 --
906 BEGIN
907 --
908 OPEN cel_assignment_exists;
909 FETCH cel_assignment_exists INTO l_assignment_id;
910 --
911 IF cel_assignment_exists%FOUND THEN
912 --
913 l_itax_type := pay_jp_balance_pkg.get_entry_value_char(
914 p_input_value_id => p_itax_type_iv_id,
915 p_assignment_id => l_assignment_id,
916 p_effective_date => p_effective_date);
917 --
918 IF l_itax_type IN ('M_KOU', 'D_KOU', 'M_OTSU', 'D_OTSU') THEN
919 --
920 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
921 p_assignment_id => l_assignment_id,
922 p_itax_type => l_itax_type,
923 p_effective_date => p_effective_date,
924 p_dpnt_spouse_type => l_deductible_spouse_status,
925 p_dpnt_spouse_dsbl_type => l_disabled_spouse_status,
926 p_dpnts => p_dependents,
927 p_aged_dpnts => p_aged_dependents,
928 p_cohab_aged_asc_dpnts => p_aged_parents,
929 p_major_dpnts => p_specified_dependents,
930 p_minor_dpnts => l_minor_dpnts,
931 p_dsbl_dpnts => p_ordinary_disabled,
932 p_svr_dsbl_dpnts => p_severely_disabled,
933 p_cohab_svr_dsbl_dpnts => p_severely_disabled_live_with,
934 p_multiple_spouses_warning => l_multiple_spouses_warning,
935 p_use_cache => FALSE);
936 --
937 p_deductible_spouse_status := hr_general.decode_lookup('JP_SPOUSE_STATUS', l_deductible_spouse_status);
938 p_disabled_spouse_status := hr_general.decode_lookup('JP_DISABLED_SPOUSE_STATUS', l_disabled_spouse_status);
939 --
940 ELSE
941 --
942 p_deductible_spouse_status := NULL;
943 p_disabled_spouse_status := NULL;
944 p_dependents := NULL;
945 p_aged_dependents := NULL;
946 p_aged_parents := NULL;
947 p_specified_dependents := NULL;
948 p_ordinary_disabled := NULL;
949 p_severely_disabled := NULL;
950 p_severely_disabled_live_with := NULL;
951 --
952 END IF;
953 --
954 ELSE
955 --
956 p_deductible_spouse_status := NULL;
957 p_disabled_spouse_status := NULL;
958 p_dependents := NULL;
959 p_aged_dependents := NULL;
960 p_aged_parents := NULL;
961 p_specified_dependents := NULL;
962 p_ordinary_disabled := NULL;
963 p_severely_disabled := NULL;
964 p_severely_disabled_live_with := NULL;
965 --
966 END IF;
967 --
968 CLOSE cel_assignment_exists;
969 --
970 END populate_itax_result_field;
971 --
972 END per_jp_dependent_pkg;