[Home] [Help]
PACKAGE BODY: APPS.PER_ADDRESSES_PKG
Source
1 PACKAGE BODY PER_ADDRESSES_PKG AS
2 /* $Header: peadd01t.pkb 120.2.12020000.6 2012/07/05 00:33:17 amnaraya ship $ */
3
4 g_pkg CONSTANT Varchar2(150):='PER_Addresses_Pkg.';
5 -- ===========================================================================
6 -- InsUpd_OSS_Person_Add: Insert or Update address into hz_locations only if
7 -- the person is a student or faculty employee.
8 -- p_action : Valid Values INSERT and UPDATE
9 -- p_effective_date :
10 -- ===========================================================================
11 PROCEDURE InsUpd_OSS_Person_Add
12 (p_addr_rec_new in per_addresses%ROWTYPE
13 ,p_addr_rec_old in per_addresses%ROWTYPE
14 ,p_action in varchar2
15 ,p_effective_date in date
16 ) As
17 -- Cursor to check if person is student
18 CURSOR csr_stu (c_person_id IN Number) IS
19 SELECT pei.pei_information5
20 FROM per_people_extra_info pei
21 WHERE pei.information_type = 'PQP_OSS_PERSON_DETAILS'
22 AND pei.pei_information_category = 'PQP_OSS_PERSON_DETAILS'
23 AND pei.person_id = c_person_id;
24
25 l_rowid ROWID;
26 --
27 l_party_site_id Number;
28 l_location_id Number;
29 l_location_ovn Number;
30 l_party_site_ovn Number;
31 l_hz_loc_rowid Rowid;
32 l_hz_loc_upd_dt Date;
33 l_last_update_date Date;
34 l_return_flag Boolean;
35 l_Stu_OSSData_Sync Varchar2(5);
36 l_return_status Varchar2(5);
37 l_msg_data Varchar2(2000);
38 l_error_msg Varchar2(2000);
39 l_proc_name CONSTANT Varchar2(150):= g_pkg||'InsUpd_OSS_Person_Add';
40 BEGIN
41 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
42 l_return_flag := False;
43 OPEN csr_stu (c_person_id => p_addr_rec_new.person_id);
44 FETCH csr_stu INTO l_Stu_OSSData_Sync;
45 CLOSE csr_stu;
46 --
47 IF Nvl(l_Stu_OSSData_Sync,'-1') <> 'Y' OR
48 Nvl(p_addr_rec_new.primary_flag,'-1') <> 'Y' OR
49 Nvl(Fnd_Profile.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'N' OR
50 p_addr_rec_new.party_id IS NULL
51 THEN
52 l_return_flag := TRUE;
53 END IF;
54 Hr_Utility.set_location('..person_id : '||p_addr_rec_new.person_id, 6);
55 Hr_Utility.set_location('..sync Flag : '||l_Stu_OSSData_Sync, 6);
56 Hr_Utility.set_location('..party_id : '||p_addr_rec_new.party_id , 6);
57 Hr_Utility.set_location('..Bus Grp Id : '||p_addr_rec_new.business_group_id, 6);
58 -- Return if any of the above conditions are true
59 IF l_return_flag THEN
60 Hr_Utility.set_location('..Returning : '||l_proc_name,7 );
61 RETURN;
62 END IF;
63
64 If p_action = 'UPDATE' AND
65 Not l_return_flag THEN
66 Hr_Utility.set_location('..p_action : '||p_action,9 );
67 Pqp_Hrtca_Integration.Update_Address_HR_To_TCA
68 (p_business_group_id => p_addr_rec_new.business_group_id
69 ,p_person_id => p_addr_rec_new.person_id
70 ,p_party_id => p_addr_rec_new.party_id
71 ,p_address_id => p_addr_rec_new.address_id
72 ,p_effective_date => p_effective_date
73 ,p_per_addr_rec_new => p_addr_rec_new
74 ,p_per_addr_rec_old => p_addr_rec_old
75 -- TCA
76 ,p_party_type => 'PERSON'
77 ,p_action => p_action
78 ,p_status => 'A'
79 -- In Out Variables
80 ,p_location_id => l_location_id
81 ,p_party_site_id => l_party_site_id
82 ,p_last_update_date => l_hz_loc_upd_dt
83 ,p_party_site_ovn => l_party_site_ovn
84 ,p_location_ovn => l_location_ovn
85 ,p_rowid => l_hz_loc_rowid
86 -- Out Variables
87 ,p_return_status => l_return_status
88 ,p_msg_data => l_msg_data
89 );
90 ELSIF p_action = 'INSERT' AND
91 Not l_return_flag THEN
92 Hr_Utility.set_location('..p_action : '||p_action,9 );
93 Pqp_Hrtca_Integration.Create_Address_HR_To_TCA
94 (p_business_group_id => p_addr_rec_new.business_group_id
95 ,p_person_id => p_addr_rec_new.person_id
96 ,p_party_id => p_addr_rec_new.party_id
97 ,p_address_id => p_addr_rec_new.address_id
98 ,p_effective_date => p_effective_date
99 ,p_per_addr_rec_new => p_addr_rec_new
100 -- TCA
101 ,p_party_type => 'PERSON'
102 ,p_action => p_action
103 ,p_status => 'A'
104 -- In Out Variables
105 ,p_location_id => l_location_id
106 ,p_party_site_id => l_party_site_id
107 ,p_last_update_date => l_last_update_date
108 ,p_party_site_ovn => l_party_site_ovn
109 ,p_location_ovn => l_location_ovn
110 ,p_rowid => l_rowid
111 -- Out Variables
112 ,p_return_status => l_return_status
113 ,p_msg_data => l_msg_data
114 );
115 END IF;
116 IF l_return_status IN ('E','U') THEN
117 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
118 Hr_Utility.set_message_token('GENERIC_TOKEN',l_msg_data );
119 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
120 Hr_Utility.raise_error;
121 END IF;
122
123 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 l_error_msg := Substrb(l_msg_data,1,2000);
128 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
129 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
130 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
131 Hr_Utility.raise_error;
132
133 END InsUpd_OSS_Person_Add;
134
135 /*
136 Procedure to perform DML on the table PER_ADDRESSES
137 when it is used in Forms which utilise the Base View method
138 */
139 --
140 --
141 procedure insert_row(p_row_id in out nocopy VARCHAR2
142 ,p_address_id in out nocopy NUMBER
143 ,p_business_group_id NUMBER
144 ,p_person_id NUMBER
145 ,p_date_from DATE
146 ,p_primary_flag VARCHAR2
147 ,p_style VARCHAR2
148 ,p_address_line1 VARCHAR2
149 ,p_address_line2 VARCHAR2
150 ,p_address_line3 VARCHAR2
151 ,p_address_type VARCHAR2
152 ,p_comments VARCHAR2
153 ,p_country VARCHAR2
154 ,p_date_to DATE
155 ,p_postal_code VARCHAR2
156 ,p_region_1 VARCHAR2
157 ,p_region_2 VARCHAR2
158 ,p_region_3 VARCHAR2
159 ,p_telephone_number_1 VARCHAR2
160 ,p_telephone_number_2 VARCHAR2
161 ,p_telephone_number_3 VARCHAR2
162 ,p_town_or_city VARCHAR2
163 ,p_request_id NUMBER
164 ,p_program_application_id NUMBER
165 ,p_program_id NUMBER
166 ,p_program_update_date DATE
167 ,p_addr_attribute_category VARCHAR2
168 ,p_addr_attribute1 VARCHAR2
169 ,p_addr_attribute2 VARCHAR2
170 ,p_addr_attribute3 VARCHAR2
171 ,p_addr_attribute4 VARCHAR2
172 ,p_addr_attribute5 VARCHAR2
173 ,p_addr_attribute6 VARCHAR2
174 ,p_addr_attribute7 VARCHAR2
175 ,p_addr_attribute8 VARCHAR2
176 ,p_addr_attribute9 VARCHAR2
177 ,p_addr_attribute10 VARCHAR2
178 ,p_addr_attribute11 VARCHAR2
179 ,p_addr_attribute12 VARCHAR2
180 ,p_addr_attribute13 VARCHAR2
181 ,p_addr_attribute14 VARCHAR2
182 ,p_addr_attribute15 VARCHAR2
183 ,p_addr_attribute16 VARCHAR2
184 ,p_addr_attribute17 VARCHAR2
185 ,p_addr_attribute18 VARCHAR2
186 ,p_addr_attribute19 VARCHAR2
187 ,p_addr_attribute20 VARCHAR2
188 -- ***** Start new code for bug 2711964 **************
189 ,p_add_information13 VARCHAR2
190 ,p_add_information14 VARCHAR2
191 ,p_add_information15 VARCHAR2
192 ,p_add_information16 VARCHAR2
193 -- ***** End new code for bug 2711964 ***************
194 ,p_add_information17 VARCHAR2
195 ,p_add_information18 VARCHAR2
196 ,p_add_information19 VARCHAR2
197 ,p_add_information20 VARCHAR2
198 ,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
199 ) is
200 --
201 -- Local Variables
202 --
203 l_default_primary VARCHAR2(1);
204 begin
205 per_addresses_pkg.insert_row(p_row_id
206 ,p_address_id
207 ,p_business_group_id
208 ,p_person_id
209 ,p_date_from
210 ,p_primary_flag
211 ,p_style
212 ,p_address_line1
213 ,p_address_line2
214 ,p_address_line3
215 ,p_address_type
216 ,p_comments
217 ,p_country
218 ,p_date_to
219 ,p_postal_code
220 ,p_region_1
221 ,p_region_2
222 ,p_region_3
223 ,p_telephone_number_1
224 ,p_telephone_number_2
225 ,p_telephone_number_3
226 ,p_town_or_city
227 ,p_request_id
228 ,p_program_application_id
229 ,p_program_id
230 ,p_program_update_date
231 ,p_addr_attribute_category
232 ,p_addr_attribute1
233 ,p_addr_attribute2
234 ,p_addr_attribute3
235 ,p_addr_attribute4
236 ,p_addr_attribute5
237 ,p_addr_attribute6
238 ,p_addr_attribute7
239 ,p_addr_attribute8
240 ,p_addr_attribute9
241 ,p_addr_attribute10
242 ,p_addr_attribute11
243 ,p_addr_attribute12
244 ,p_addr_attribute13
245 ,p_addr_attribute14
246 ,p_addr_attribute15
247 ,p_addr_attribute16
248 ,p_addr_attribute17
249 ,p_addr_attribute18
250 ,p_addr_attribute19
251 ,p_addr_attribute20
252 -- ***** Start new code for bug 2711964 **************
253 ,p_add_information13
254 ,p_add_information14
255 ,p_add_information15
256 ,p_add_information16
257 -- ***** End new code for bug 2711964 ***************
258 ,p_add_information17
259 ,p_add_information18
260 ,p_add_information19
261 ,p_add_information20
262 ,p_end_of_time
263 ,l_default_primary
264 );
265 end insert_row;
266 --
267 procedure insert_row(p_row_id in out nocopy VARCHAR2
268 ,p_address_id in out nocopy NUMBER
269 ,p_business_group_id NUMBER
270 ,p_person_id NUMBER
271 ,p_date_from DATE
272 ,p_primary_flag VARCHAR2
273 ,p_style VARCHAR2
274 ,p_address_line1 VARCHAR2
275 ,p_address_line2 VARCHAR2
276 ,p_address_line3 VARCHAR2
277 ,p_address_type VARCHAR2
278 ,p_comments VARCHAR2
279 ,p_country VARCHAR2
280 ,p_date_to DATE
281 ,p_postal_code VARCHAR2
282 ,p_region_1 VARCHAR2
283 ,p_region_2 VARCHAR2
284 ,p_region_3 VARCHAR2
285 ,p_telephone_number_1 VARCHAR2
286 ,p_telephone_number_2 VARCHAR2
287 ,p_telephone_number_3 VARCHAR2
288 ,p_town_or_city VARCHAR2
289 ,p_request_id NUMBER
290 ,p_program_application_id NUMBER
291 ,p_program_id NUMBER
292 ,p_program_update_date DATE
293 ,p_addr_attribute_category VARCHAR2
294 ,p_addr_attribute1 VARCHAR2
295 ,p_addr_attribute2 VARCHAR2
296 ,p_addr_attribute3 VARCHAR2
297 ,p_addr_attribute4 VARCHAR2
298 ,p_addr_attribute5 VARCHAR2
299 ,p_addr_attribute6 VARCHAR2
300 ,p_addr_attribute7 VARCHAR2
301 ,p_addr_attribute8 VARCHAR2
302 ,p_addr_attribute9 VARCHAR2
303 ,p_addr_attribute10 VARCHAR2
304 ,p_addr_attribute11 VARCHAR2
305 ,p_addr_attribute12 VARCHAR2
306 ,p_addr_attribute13 VARCHAR2
307 ,p_addr_attribute14 VARCHAR2
308 ,p_addr_attribute15 VARCHAR2
309 ,p_addr_attribute16 VARCHAR2
310 ,p_addr_attribute17 VARCHAR2
311 ,p_addr_attribute18 VARCHAR2
312 ,p_addr_attribute19 VARCHAR2
313 ,p_addr_attribute20 VARCHAR2
314 -- ***** Start new code for bug 2711964 **************
315 ,p_add_information13 VARCHAR2
316 ,p_add_information14 VARCHAR2
317 ,p_add_information15 VARCHAR2
318 ,p_add_information16 VARCHAR2
319 -- ***** End new code for bug 2711964 ***************
320 ,p_add_information17 VARCHAR2
321 ,p_add_information18 VARCHAR2
322 ,p_add_information19 VARCHAR2
323 ,p_add_information20 VARCHAR2
324 ,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
325 ,p_default_primary IN OUT NOCOPY VARCHAR2
326 ) is
327 --
328 cursor c1 is select per_addresses_s.nextval
329 from sys.dual;
330 --
331 cursor c2 is select rowid
332 from per_addresses
333 where address_id = p_address_id;
334 -- rpinjala
335
336 cursor c3 is select *
337 from per_addresses
338 where address_id = p_address_id;
339 l_addr_rec per_addresses%ROWTYPE;
340 -- rpinjala
341 --
342 cursor csr_get_party_id is
343 select max(party_id)
344 from per_all_people_f
345 where person_id = p_person_id;
346
347 --
348 /* Need to check that US payroll is installed */
349 /*CURSOR get_install_info IS
350 SELECT fpi.status
351 FROM fnd_product_installations fpi,
352 per_people_f ppf,
353 per_business_groups pbg
354 WHERE fpi.application_id = 801
355 AND p_person_id = ppf.person_id
356 AND p_business_group_id = pbg.business_group_id
357 AND pbg.legislation_code = 'US'
358 AND p_person_id = ppf.person_id
359 AND ppf.current_employee_flag = 'Y'
360 AND p_style = 'US'
361 AND p_primary_flag = 'Y';
362 */
363
364 /* Need to check that legislation_code of the business group,to avoid
365 default_tax_with_validation getting called for other legislation code except
366 'US' - mmukherj */
367
368 CURSOR get_legislation_code IS
369 SELECT pbg.legislation_code
370 from per_business_groups pbg
371 where p_business_group_id = pbg.business_group_id;
372
373 --
374 l_status VARCHAR2(50);
375 l_return_code number;
376 l_return_text varchar2(240);
377 l_legislation_code varchar2(240);
378 l_party_id number;
379 --
380 -- Fix for WWBUG 1408379
381 --
382 l_old ben_add_ler.g_add_ler_rec;
383 l_new ben_add_ler.g_add_ler_rec;
384 --
385 -- End of Fix for WWBUG 1408379
386 --
387 begin
388 hr_utility.set_location('Insert_row',1);
389 --
390 open c1;
391 fetch c1 into p_address_id;
392 close c1;
393 --
394 open csr_get_party_id;
395 fetch csr_get_party_id into l_party_id;
396 close csr_get_party_id;
397 --
398 insert into per_addresses(
399 address_id
400 ,business_group_id
401 ,person_id
402 ,date_from
403 ,primary_flag
404 ,style
405 ,address_line1
406 ,address_line2
407 ,address_line3
408 ,address_type
409 ,comments
410 ,country
411 ,date_to
412 ,postal_code
413 ,region_1
414 ,region_2
415 ,region_3
416 ,telephone_number_1
417 ,telephone_number_2
418 ,telephone_number_3
419 ,town_or_city
420 ,request_id
421 ,program_application_id
422 ,program_id
423 ,program_update_date
424 ,addr_attribute_category
425 ,addr_attribute1
426 ,addr_attribute2
427 ,addr_attribute3
428 ,addr_attribute4
429 ,addr_attribute5
430 ,addr_attribute6
431 ,addr_attribute7
432 ,addr_attribute8
433 ,addr_attribute9
434 ,addr_attribute10
435 ,addr_attribute11
436 ,addr_attribute12
437 ,addr_attribute13
438 ,addr_attribute14
439 ,addr_attribute15
440 ,addr_attribute16
441 ,addr_attribute17
442 ,addr_attribute18
443 ,addr_attribute19
444 ,addr_attribute20
445 -- ***** Start new code for bug 2711964 **************
446 ,add_information13
447 ,add_information14
448 ,add_information15
449 ,add_information16
450 -- ***** End new code for bug 2711964 ***************
451 ,add_information17
452 ,add_information18
453 ,add_information19
454 ,add_information20
455 ,party_id
456 )
457 values
458 (p_address_id
459 ,p_business_group_id
460 ,p_person_id
461 ,p_date_from
462 ,p_primary_flag
463 ,p_style
464 ,p_address_line1
465 ,p_address_line2
466 ,p_address_line3
467 ,p_address_type
468 ,p_comments
469 ,p_country
470 ,p_date_to
471 ,p_postal_code
472 ,p_region_1
473 ,p_region_2
474 ,p_region_3
475 ,p_telephone_number_1
476 ,p_telephone_number_2
477 ,p_telephone_number_3
478 ,p_town_or_city
479 ,p_request_id
480 ,p_program_application_id
481 ,p_program_id
482 ,p_program_update_date
483 ,p_addr_attribute_category
484 ,p_addr_attribute1
485 ,p_addr_attribute2
486 ,p_addr_attribute3
487 ,p_addr_attribute4
488 ,p_addr_attribute5
489 ,p_addr_attribute6
490 ,p_addr_attribute7
491 ,p_addr_attribute8
492 ,p_addr_attribute9
493 ,p_addr_attribute10
494 ,p_addr_attribute11
495 ,p_addr_attribute12
496 ,p_addr_attribute13
497 ,p_addr_attribute14
498 ,p_addr_attribute15
499 ,p_addr_attribute16
500 ,p_addr_attribute17
501 ,p_addr_attribute18
502 ,p_addr_attribute19
503 ,p_addr_attribute20
504 -- ***** Start new code for bug 2711964 **************
505 ,p_add_information13
506 ,p_add_information14
507 ,p_add_information15
508 ,p_add_information16
509 -- ***** End new code for bug 2711964 ***************
510 ,p_add_information17
511 ,p_add_information18
512 ,p_add_information19
513 ,p_add_information20
514 ,l_party_id
515 );
516 --
517 -- Fix for WWBUG 1408379
518 --
519 l_new.person_id := p_person_id;
520 l_new.business_group_id := p_business_group_id;
521 l_new.date_from := p_date_from;
522 l_new.date_to := p_date_to;
523 l_new.primary_flag := p_primary_flag;
524 l_new.postal_code := p_postal_code;
525 l_new.region_2 := p_region_2;
526 l_new.address_type := p_address_type;
527 l_new.address_id := p_address_id;
528 --
529 ben_add_ler.ler_chk(p_old => l_old,
530 p_new => l_new,
531 p_effective_date => l_new.date_from);
532 --
533 -- End of Fix for WWBUG 1408379
534 --
535 -- ==============================================================
536 -- Call to HZ V2 Address API for creating address in HZ_LOCATIONS
537 -- as per OSS HRMS integration
538 -- ==============================================================
539 open c3;
540 fetch c3 into l_addr_rec;
541 close c3;
542
543 InsUpd_OSS_Person_Add
544 (p_addr_rec_new => l_addr_rec
545 ,p_addr_rec_old => Null
546 ,p_action => 'INSERT'
547 ,p_effective_date => p_date_from
548 );
549 -- ==============================================================
550
551 open c2;
552 --
553 fetch c2 into p_row_id;
554 --
555 close c2;
556 --
557 p_default_primary := per_addresses_pkg.does_primary_exist(p_person_id
558 ,p_business_group_id
559 ,p_end_of_time);
560 -- Now need to insure that tax record exists if this is the Primary address.
561 -- For US Payroll installed, employees only.
562 /* Check For if this is a primary address for a
563 US employee when payroll is installed will be made in default_tax_with_validation procedure
564 Changes are made as an impact of datetracking of W4 form*/
565
566 -- OPEN get_install_info;
567 -- FETCH get_install_info INTO l_status;
568 -- CLOSE get_install_info;
569 -- IF l_status = 'I' THEN
570
571 open get_legislation_code;
572 fetch get_legislation_code into l_legislation_code;
573 close get_legislation_code;
574
575 IF l_legislation_code = 'US' THEN
576 pay_us_emp_dt_tax_rules.default_tax_with_validation(
577 p_assignment_id => NULL,
578 p_person_id => p_person_id,
579 p_effective_start_date => p_date_from,
580 p_effective_end_date => p_date_to,
581 p_session_date => NULL,
582 p_business_group_id => p_business_group_id,
583 p_from_form => 'Address',
584 p_mode => NULL,
585 p_location_id => NULL,
586 p_return_code => l_return_code,
587 p_return_text => l_return_text);
588 end if;
589 -- No need to check return, because if not possible then no
590 -- user message ness.
591 -- end if;
592 end insert_row;
593 --
594 procedure delete_row(p_row_id VARCHAR2) is
595 --
596 -- local variables
597 --
598 l_person_id NUMBER;
599 l_business_group_id NUMBER;
600 l_end_of_time DATE;
601 l_default_primary VARCHAR2(1);
602 begin
603 per_addresses_pkg.delete_row(p_row_id
604 ,l_person_id
605 ,l_business_group_id
606 ,l_end_of_time
607 ,l_default_primary);
608 end delete_row;
609 --
610 procedure delete_row(p_row_id VARCHAR2
611 ,p_person_id NUMBER
612 ,p_business_group_id NUMBER
613 ,p_end_of_time DATE
614 ,p_default_primary IN OUT NOCOPY VARCHAR2) is
615 --
616 begin
617 delete from per_addresses pa
618 where pa.rowid = chartorowid(p_row_id);
619 --
620 p_default_primary := per_addresses_pkg.does_primary_exist(p_person_id
621 ,p_business_group_id
622 ,p_end_of_time);
623 --
624 end delete_row;
625 --
626 procedure lock_row(p_row_id VARCHAR2
627 ,p_address_id NUMBER
628 ,p_business_group_id NUMBER
629 ,p_person_id NUMBER
630 ,p_date_from DATE
631 ,p_primary_flag VARCHAR2
632 ,p_style VARCHAR2
633 ,p_address_line1 VARCHAR2
634 ,p_address_line2 VARCHAR2
635 ,p_address_line3 VARCHAR2
636 ,p_address_type VARCHAR2
637 ,p_comments VARCHAR2
638 ,p_country VARCHAR2
639 ,p_date_to DATE
640 ,p_postal_code VARCHAR2
641 ,p_region_1 VARCHAR2
642 ,p_region_2 VARCHAR2
643 ,p_region_3 VARCHAR2
644 ,p_telephone_number_1 VARCHAR2
645 ,p_telephone_number_2 VARCHAR2
646 ,p_telephone_number_3 VARCHAR2
647 ,p_town_or_city VARCHAR2
648 ,p_addr_attribute_category VARCHAR2
649 ,p_addr_attribute1 VARCHAR2
650 ,p_addr_attribute2 VARCHAR2
651 ,p_addr_attribute3 VARCHAR2
652 ,p_addr_attribute4 VARCHAR2
653 ,p_addr_attribute5 VARCHAR2
654 ,p_addr_attribute6 VARCHAR2
655 ,p_addr_attribute7 VARCHAR2
656 ,p_addr_attribute8 VARCHAR2
657 ,p_addr_attribute9 VARCHAR2
658 ,p_addr_attribute10 VARCHAR2
659 ,p_addr_attribute11 VARCHAR2
660 ,p_addr_attribute12 VARCHAR2
661 ,p_addr_attribute13 VARCHAR2
662 ,p_addr_attribute14 VARCHAR2
663 ,p_addr_attribute15 VARCHAR2
664 ,p_addr_attribute16 VARCHAR2
665 ,p_addr_attribute17 VARCHAR2
666 ,p_addr_attribute18 VARCHAR2
667 ,p_addr_attribute19 VARCHAR2
668 ,p_addr_attribute20 VARCHAR2
669 ,p_add_information17 VARCHAR2
670 ,p_add_information18 VARCHAR2
671 ,p_add_information19 VARCHAR2
672 ,p_add_information20 VARCHAR2
673 ) is
674 cursor addr is select *
675 from per_addresses
676 where rowid = chartorowid(p_row_id)
677 for update nowait;
678 add_rec addr%rowtype;
679 begin
680 open addr;
681 fetch addr into add_rec;
682 close addr;
683 add_rec.addr_attribute13 := rtrim(add_rec.addr_attribute13);
684 add_rec.addr_attribute14 := rtrim(add_rec.addr_attribute14);
685 add_rec.addr_attribute15 := rtrim(add_rec.addr_attribute15);
686 add_rec.addr_attribute16 := rtrim(add_rec.addr_attribute16);
687 add_rec.addr_attribute17 := rtrim(add_rec.addr_attribute17);
688 add_rec.addr_attribute18 := rtrim(add_rec.addr_attribute18);
689 add_rec.addr_attribute19 := rtrim(add_rec.addr_attribute19);
690 add_rec.addr_attribute20 := rtrim(add_rec.addr_attribute20);
691 add_rec.primary_flag := rtrim(add_rec.primary_flag);
692 add_rec.style := rtrim(add_rec.style);
693 add_rec.address_line1 := rtrim(add_rec.address_line1);
694 add_rec.address_line2 := rtrim(add_rec.address_line2);
695 add_rec.address_line3 := rtrim(add_rec.address_line3);
696 add_rec.address_type := rtrim(add_rec.address_type);
697 add_rec.comments := rtrim(add_rec.comments);
698 add_rec.country := rtrim(add_rec.country);
699 add_rec.postal_code := rtrim(add_rec.postal_code);
700 add_rec.region_1 := rtrim(add_rec.region_1);
701 add_rec.region_2 := rtrim(add_rec.region_2);
702 add_rec.region_3 := rtrim(add_rec.region_3);
703 add_rec.telephone_number_1 := rtrim(add_rec.telephone_number_1);
704 add_rec.telephone_number_2 := rtrim(add_rec.telephone_number_2);
705 add_rec.telephone_number_3 := rtrim(add_rec.telephone_number_3);
706 add_rec.town_or_city := rtrim(add_rec.town_or_city);
707 add_rec.addr_attribute_category := rtrim(add_rec.addr_attribute_category);
708 add_rec.addr_attribute1 := rtrim(add_rec.addr_attribute1);
709 add_rec.addr_attribute2 := rtrim(add_rec.addr_attribute2);
710 add_rec.addr_attribute3 := rtrim(add_rec.addr_attribute3);
711 add_rec.addr_attribute4 := rtrim(add_rec.addr_attribute4);
712 add_rec.addr_attribute5 := rtrim(add_rec.addr_attribute5);
713 add_rec.addr_attribute6 := rtrim(add_rec.addr_attribute6);
714 add_rec.addr_attribute7 := rtrim(add_rec.addr_attribute7);
715 add_rec.addr_attribute8 := rtrim(add_rec.addr_attribute8);
716 add_rec.addr_attribute9 := rtrim(add_rec.addr_attribute9);
717 add_rec.addr_attribute10 := rtrim(add_rec.addr_attribute10);
718 add_rec.addr_attribute11 := rtrim(add_rec.addr_attribute11);
719 add_rec.addr_attribute12 := rtrim(add_rec.addr_attribute12);
720 add_rec.add_information17 := rtrim(add_rec.add_information17);
721 add_rec.add_information18 := rtrim(add_rec.add_information18);
722 add_rec.add_information19 := rtrim(add_rec.add_information19);
723 add_rec.add_information20 := rtrim(add_rec.add_information20);
724 --
725 if ( ((add_rec.address_id = p_address_id)
726 or (add_rec.address_id is null
727 and (p_address_id is null)))
728 and ((add_rec.business_group_id= p_business_group_id)
729 or (add_rec.business_group_id is null
730 and (p_business_group_id is null)))
731 and ((add_rec.person_id= p_person_id)
732 or (add_rec.person_id is null
733 and (p_person_id is null)))
734 and ((add_rec.date_from= p_date_from)
735 or (add_rec.date_from is null
736 and (p_date_from is null)))
737 and ((add_rec.primary_flag = p_primary_flag)
738 or (add_rec.primary_flag is null
739 and (p_primary_flag is null)))
740 and ((add_rec.style= p_style)
741 or (add_rec.style is null
742 and (p_style is null)))
743 and ((add_rec.address_line1= p_address_line1)
744 or (add_rec.address_line1 is null
745 and (p_address_line1 is null)))
746 and ((add_rec.address_line2= p_address_line2)
747 or (add_rec.address_line2 is null
748 and (p_address_line2 is null)))
749 and ((add_rec.address_line3= p_address_line3)
750 or (add_rec.address_line3 is null
751 and (p_address_line3 is null)))
752 and ((add_rec.address_type = p_address_type)
753 or (add_rec.address_type is null
754 and (p_address_type is null)))
755 and ((add_rec.comments = p_comments)
756 or (add_rec.comments is null
757 and (p_comments is null)))
758 and ((add_rec.country= p_country)
759 or (add_rec.country is null
760 and (p_country is null)))
761 and ((add_rec.date_to= p_date_to)
762 or (add_rec.date_to is null
763 and (p_date_to is null)))
764 and ((add_rec.postal_code= p_postal_code)
765 or (add_rec.postal_code is null
766 and (p_postal_code is null)))
767 and ((add_rec.region_1 = p_region_1)
768 or (add_rec.region_1 is null
769 and (p_region_1 is null)))
770 and ((add_rec.region_2 = p_region_2)
771 or (add_rec.region_2 is null
772 and (p_region_2 is null)))
773 and ((add_rec.region_3 = p_region_3)
774 or (add_rec.region_3 is null
775 and (p_region_3 is null)))
776 and ((add_rec.telephone_number_1 = p_telephone_number_1)
777 or (add_rec.telephone_number_1 is null
778 and (p_telephone_number_1 is null)))
779 and ((add_rec.telephone_number_2 = p_telephone_number_2)
780 or (add_rec.telephone_number_2 is null
781 and (p_telephone_number_2 is null)))
782 and ((add_rec.telephone_number_3 = p_telephone_number_3)
783 or (add_rec.telephone_number_3 is null
784 and (p_telephone_number_3 is null)))
785 and ((add_rec.town_or_city = p_town_or_city)
786 or (add_rec.town_or_city is null
787 and (p_town_or_city is null)))
788 and ((add_rec.addr_attribute_category= p_addr_attribute_category)
789 or (add_rec.addr_attribute_category is null
790 and (p_addr_attribute_category is null)))
791 and ((add_rec.addr_attribute1= p_addr_attribute1)
792 or (add_rec.addr_attribute1 is null
793 and (p_addr_attribute1 is null)))
794 and ((add_rec.addr_attribute2= p_addr_attribute2)
795 or (add_rec.addr_attribute2 is null
796 and (p_addr_attribute2 is null)))
797 and ((add_rec.addr_attribute3= p_addr_attribute3)
798 or (add_rec.addr_attribute3 is null
799 and (p_addr_attribute3 is null)))
800 and ((add_rec.addr_attribute4= p_addr_attribute4)
801 or (add_rec.addr_attribute4 is null
802 and (p_addr_attribute4 is null)))
803 and ((add_rec.addr_attribute5= p_addr_attribute5)
804 or (add_rec.addr_attribute5 is null
805 and (p_addr_attribute5 is null)))
806 and ((add_rec.addr_attribute6= p_addr_attribute6)
807 or (add_rec.addr_attribute6 is null
808 and (p_addr_attribute6 is null)))
809 and ((add_rec.addr_attribute7= p_addr_attribute7)
810 or (add_rec.addr_attribute7 is null
811 and (p_addr_attribute7 is null)))
812 and ((add_rec.addr_attribute8= p_addr_attribute8)
813 or (add_rec.addr_attribute8 is null
814 and (p_addr_attribute8 is null)))
815 and ((add_rec.addr_attribute9= p_addr_attribute9)
816 or (add_rec.addr_attribute9 is null
817 and (p_addr_attribute9 is null)))
818 and ((add_rec.addr_attribute10 = p_addr_attribute10)
819 or (add_rec.addr_attribute10 is null
820 and (p_addr_attribute10 is null)))
821 and ((add_rec.addr_attribute11 = p_addr_attribute11)
822 or (add_rec.addr_attribute11 is null
823 and (p_addr_attribute11 is null)))
824 and ((add_rec.addr_attribute12 = p_addr_attribute12)
825 or (add_rec.addr_attribute12 is null
826 and (p_addr_attribute12 is null)))
827 and ((add_rec.addr_attribute13 = p_addr_attribute13)
828 or (add_rec.addr_attribute13 is null
829 and (p_addr_attribute13 is null)))
830 and ((add_rec.addr_attribute14 = p_addr_attribute14)
831 or (add_rec.addr_attribute14 is null
832 and (p_addr_attribute14 is null)))
833 and ((add_rec.addr_attribute15 = p_addr_attribute15)
834 or (add_rec.addr_attribute15 is null
835 and (p_addr_attribute15 is null)))
836 and ((add_rec.addr_attribute16 = p_addr_attribute16)
837 or (add_rec.addr_attribute16 is null
838 and (p_addr_attribute16 is null)))
839 and ((add_rec.addr_attribute17 = p_addr_attribute17)
840 or (add_rec.addr_attribute17 is null
841 and (p_addr_attribute17 is null)))
842 and ((add_rec.addr_attribute18 = p_addr_attribute18)
843 or (add_rec.addr_attribute18 is null
844 and (p_addr_attribute18 is null)))
845 and ((add_rec.addr_attribute19 = p_addr_attribute19)
846 or (add_rec.addr_attribute19 is null
847 and (p_addr_attribute19 is null)))
848 and ((add_rec.addr_attribute20 = p_addr_attribute20)
849 or (add_rec.addr_attribute20 is null
850 and (p_addr_attribute20 is null)))
851 and ((add_rec.add_information17 = p_add_information17)
852 or (add_rec.add_information17 is null
853 and (p_add_information17 is null)))
854 and ((add_rec.add_information18 = p_add_information18)
855 or (add_rec.add_information18 is null
856 and (p_add_information18 is null)))
857 and ((add_rec.add_information19 = p_add_information19)
858 or (add_rec.add_information19 is null
859 and (p_add_information19 is null)))
860 and ((add_rec.add_information20 = p_add_information20)
861 or (add_rec.add_information20 is null
862 and (p_add_information20 is null)))
863 ) then
864 return;
865 end if;
866 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
867 app_exception.raise_exception;
868 exception when no_data_found then
869 raise;
870 when others then raise;
871 end lock_row;
872 --
873 procedure update_row(p_row_id VARCHAR2
874 ,p_address_id NUMBER
875 ,p_business_group_id NUMBER
876 ,p_person_id NUMBER
877 ,p_date_from DATE
878 ,p_primary_flag VARCHAR2
879 ,p_style VARCHAR2
880 ,p_address_line1 VARCHAR2
881 ,p_address_line2 VARCHAR2
882 ,p_address_line3 VARCHAR2
883 ,p_address_type VARCHAR2
884 ,p_comments VARCHAR2
885 ,p_country VARCHAR2
886 ,p_date_to DATE
887 ,p_postal_code VARCHAR2
888 ,p_region_1 VARCHAR2
889 ,p_region_2 VARCHAR2
890 ,p_region_3 VARCHAR2
891 ,p_telephone_number_1 VARCHAR2
892 ,p_telephone_number_2 VARCHAR2
893 ,p_telephone_number_3 VARCHAR2
894 ,p_town_or_city VARCHAR2
895 ,p_request_id NUMBER
896 ,p_program_application_id NUMBER
897 ,p_program_id NUMBER
898 ,p_program_update_date DATE
899 ,p_addr_attribute_category VARCHAR2
900 ,p_addr_attribute1 VARCHAR2
901 ,p_addr_attribute2 VARCHAR2
902 ,p_addr_attribute3 VARCHAR2
903 ,p_addr_attribute4 VARCHAR2
904 ,p_addr_attribute5 VARCHAR2
905 ,p_addr_attribute6 VARCHAR2
906 ,p_addr_attribute7 VARCHAR2
907 ,p_addr_attribute8 VARCHAR2
908 ,p_addr_attribute9 VARCHAR2
909 ,p_addr_attribute10 VARCHAR2
910 ,p_addr_attribute11 VARCHAR2
911 ,p_addr_attribute12 VARCHAR2
912 ,p_addr_attribute13 VARCHAR2
913 ,p_addr_attribute14 VARCHAR2
914 ,p_addr_attribute15 VARCHAR2
915 ,p_addr_attribute16 VARCHAR2
916 ,p_addr_attribute17 VARCHAR2
917 ,p_addr_attribute18 VARCHAR2
918 ,p_addr_attribute19 VARCHAR2
919 ,p_addr_attribute20 VARCHAR2
920 ,p_add_information17 VARCHAR2
921 ,p_add_information18 VARCHAR2
922 ,p_add_information19 VARCHAR2
923 ,p_add_information20 VARCHAR2
924 ,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
925 ) is
926 --
927 -- Local Variables
928 --
929 l_default_primary VARCHAR2(1);
930 l_return_code number;
931 l_return_text varchar2(240);
932
933 begin
934 hr_utility.set_location('update_row',1);
935 PER_ADDRESSES_PKG.update_row(p_row_id
936 ,p_address_id
937 ,p_business_group_id
938 ,p_person_id
939 ,p_date_from
940 ,p_primary_flag
941 ,p_style
942 ,p_address_line1
943 ,p_address_line2
944 ,p_address_line3
945 ,p_address_type
946 ,p_comments
947 ,p_country
948 ,p_date_to
949 ,p_postal_code
950 ,p_region_1
951 ,p_region_2
952 ,p_region_3
953 ,p_telephone_number_1
954 ,p_telephone_number_2
955 ,p_telephone_number_3
956 ,p_town_or_city
957 ,p_request_id
958 ,p_program_application_id
959 ,p_program_id
960 ,p_program_update_date
961 ,p_addr_attribute_category
962 ,p_addr_attribute1
963 ,p_addr_attribute2
964 ,p_addr_attribute3
965 ,p_addr_attribute4
966 ,p_addr_attribute5
967 ,p_addr_attribute6
968 ,p_addr_attribute7
969 ,p_addr_attribute8
970 ,p_addr_attribute9
971 ,p_addr_attribute10
972 ,p_addr_attribute11
973 ,p_addr_attribute12
974 ,p_addr_attribute13
975 ,p_addr_attribute14
976 ,p_addr_attribute15
977 ,p_addr_attribute16
978 ,p_addr_attribute17
979 ,p_addr_attribute18
980 ,p_addr_attribute19
981 ,p_addr_attribute20
982 ,p_add_information17
983 ,p_add_information18
984 ,p_add_information19
985 ,p_add_information20
986 ,p_end_of_time
987 ,l_default_primary
988 );
989 --
990 --
991 end update_row;
992 --
993 procedure update_row(p_row_id VARCHAR2
994 ,p_address_id NUMBER
995 ,p_business_group_id NUMBER
996 ,p_person_id NUMBER
997 ,p_date_from DATE
998 ,p_primary_flag VARCHAR2
999 ,p_style VARCHAR2
1000 ,p_address_line1 VARCHAR2
1001 ,p_address_line2 VARCHAR2
1002 ,p_address_line3 VARCHAR2
1003 ,p_address_type VARCHAR2
1004 ,p_comments VARCHAR2
1005 ,p_country VARCHAR2
1006 ,p_date_to DATE
1007 ,p_postal_code VARCHAR2
1008 ,p_region_1 VARCHAR2
1009 ,p_region_2 VARCHAR2
1010 ,p_region_3 VARCHAR2
1011 ,p_telephone_number_1 VARCHAR2
1012 ,p_telephone_number_2 VARCHAR2
1013 ,p_telephone_number_3 VARCHAR2
1014 ,p_town_or_city VARCHAR2
1015 ,p_request_id NUMBER
1016 ,p_program_application_id NUMBER
1017 ,p_program_id NUMBER
1018 ,p_program_update_date DATE
1019 ,p_addr_attribute_category VARCHAR2
1020 ,p_addr_attribute1 VARCHAR2
1021 ,p_addr_attribute2 VARCHAR2
1022 ,p_addr_attribute3 VARCHAR2
1023 ,p_addr_attribute4 VARCHAR2
1024 ,p_addr_attribute5 VARCHAR2
1025 ,p_addr_attribute6 VARCHAR2
1026 ,p_addr_attribute7 VARCHAR2
1027 ,p_addr_attribute8 VARCHAR2
1028 ,p_addr_attribute9 VARCHAR2
1029 ,p_addr_attribute10 VARCHAR2
1030 ,p_addr_attribute11 VARCHAR2
1031 ,p_addr_attribute12 VARCHAR2
1032 ,p_addr_attribute13 VARCHAR2
1033 ,p_addr_attribute14 VARCHAR2
1034 ,p_addr_attribute15 VARCHAR2
1035 ,p_addr_attribute16 VARCHAR2
1036 ,p_addr_attribute17 VARCHAR2
1037 ,p_addr_attribute18 VARCHAR2
1038 ,p_addr_attribute19 VARCHAR2
1039 ,p_addr_attribute20 VARCHAR2
1040 ,p_add_information17 VARCHAR2
1041 ,p_add_information18 VARCHAR2
1042 ,p_add_information19 VARCHAR2
1043 ,p_add_information20 VARCHAR2
1044 ,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
1045 ,p_default_primary IN OUT NOCOPY VARCHAR2
1046 ) is
1047 /* Need to check that US payroll is installed.
1048 This will now be checked in default_tax_with_validation_package */
1049 --
1050 /*
1051 CURSOR get_install_info IS
1052 SELECT fpi.status
1053 FROM fnd_product_installations fpi,
1054 per_people_f ppf,
1055 per_business_groups pbg
1056 WHERE fpi.application_id = 801
1057 AND p_person_id = ppf.person_id
1058 AND ppf.current_employee_flag = 'Y'
1059 AND p_business_group_id = pbg.business_group_id
1060 AND pbg.legislation_code = 'US'
1061 AND p_style = 'US'
1062 AND p_primary_flag = 'Y';
1063 */
1064 /* Need to check that legislation_code of the business group,to avoid
1065 default_tax_with_validation getting called for other legislation code except
1066 'US' - mmukherj */
1067 --
1068 CURSOR get_legislation_code IS
1069 SELECT pbg.legislation_code
1070 from per_business_groups pbg
1071 where p_business_group_id = pbg.business_group_id;
1072 --
1073 l_status VARCHAR2(50);
1074 l_person_type VARCHAR2(50);
1075 l_return_code number;
1076 l_return_text varchar2(240);
1077 l_legislation_code varchar2(240);
1078 --
1079 -- Fix for WWBUG 1408379
1080 --
1081 l_old ben_add_ler.g_add_ler_rec;
1082 l_new ben_add_ler.g_add_ler_rec;
1083
1084 --
1085 cursor c1 is
1086 select *
1087 from per_addresses
1088 where rowid = chartorowid(p_row_id);
1089 --
1090 l_c1 c1%rowtype;
1091 l_c2 c1%rowtype;
1092
1093 l_rec_found boolean := false;
1094 --
1095 -- End of Fix for WWBUG 1408379
1096 --
1097 begin
1098 --
1099 /*hr_person.validate_address(p_person_id
1100 ,p_business_group_id
1101 ,p_address_id
1102 ,p_date_from
1103 ,p_date_to
1104 ,p_end_of_time
1105 ,p_primary_flag);*/
1106 --
1107 -- Fix for WWBUG 1408379
1108 --
1109 open c1;
1110 --
1111 fetch c1 into l_c1;
1112 --
1113 if c1%found then
1114 --
1115 l_rec_found := true;
1116 --
1117 end if;
1118 --
1119 close c1;
1120 --
1121 -- End of Fix for WWBUG 1408379
1122 --
1123 update per_addresses pa set
1124 pa.address_id = p_address_id
1125 ,pa.business_group_id = p_business_group_id
1126 ,pa.person_id = p_person_id
1127 ,pa.date_from = p_date_from
1128 ,pa.primary_flag = p_primary_flag
1129 ,pa.style = p_style
1130 ,pa.address_line1 = p_address_line1
1131 ,pa.address_line2 = p_address_line2
1132 ,pa.address_line3 = p_address_line3
1133 ,pa.address_type = p_address_type
1134 ,pa.comments = p_comments
1135 ,pa.country = p_country
1136 ,pa.date_to = p_date_to
1137 ,pa.postal_code = p_postal_code
1138 ,pa.region_1 = p_region_1
1139 ,pa.region_2 = p_region_2
1140 ,pa.region_3 = p_region_3
1141 ,pa.telephone_number_1 = p_telephone_number_1
1142 ,pa.telephone_number_2 = p_telephone_number_2
1143 ,pa.telephone_number_3 = p_telephone_number_3
1144 ,pa.town_or_city = p_town_or_city
1145 ,pa.request_id = p_request_id
1146 ,pa.program_application_id = p_program_application_id
1147 ,pa.program_id = p_program_id
1148 ,pa.program_update_date = p_program_update_date
1149 ,pa.addr_attribute_category = p_addr_attribute_category
1150 ,pa.addr_attribute1 = p_addr_attribute1
1151 ,pa.addr_attribute2 = p_addr_attribute2
1152 ,pa.addr_attribute3 = p_addr_attribute3
1153 ,pa.addr_attribute4 = p_addr_attribute4
1154 ,pa.addr_attribute5 = p_addr_attribute5
1155 ,pa.addr_attribute6 = p_addr_attribute6
1156 ,pa.addr_attribute7 = p_addr_attribute7
1157 ,pa.addr_attribute8 = p_addr_attribute8
1158 ,pa.addr_attribute9 = p_addr_attribute9
1159 ,pa.addr_attribute10 = p_addr_attribute10
1160 ,pa.addr_attribute11 = p_addr_attribute11
1161 ,pa.addr_attribute12 = p_addr_attribute12
1162 ,pa.addr_attribute13 = p_addr_attribute13
1163 ,pa.addr_attribute14 = p_addr_attribute14
1164 ,pa.addr_attribute15 = p_addr_attribute15
1165 ,pa.addr_attribute16 = p_addr_attribute16
1166 ,pa.addr_attribute17 = p_addr_attribute17
1167 ,pa.addr_attribute18 = p_addr_attribute18
1168 ,pa.addr_attribute19 = p_addr_attribute19
1169 ,pa.addr_attribute20 = p_addr_attribute20
1170 ,pa.add_information17 = p_add_information17
1171 ,pa.add_information18 = p_add_information18
1172 ,pa.add_information19 = p_add_information19
1173 ,pa.add_information20 = p_add_information20
1174 where pa.rowid = chartorowid(p_row_id);
1175 --
1176 -- Fix for WWBUG 1408379
1177 --
1178 if l_rec_found then
1179 --
1180 -- Call OAB hook
1181 --
1182 l_old.person_id := l_c1.person_id;
1183 l_old.business_group_id := l_c1.business_group_id;
1184 l_old.date_from := l_c1.date_from;
1185 l_old.date_to := l_c1.date_to;
1186 l_old.primary_flag := l_c1.primary_flag;
1187 l_old.postal_code := l_c1.postal_code;
1188 l_old.region_2 := l_c1.region_2;
1189 l_old.address_type := l_c1.address_type;
1190 l_old.address_id := l_c1.address_id;
1191
1192 l_new.person_id := p_person_id;
1193 l_new.business_group_id := p_business_group_id;
1194 l_new.date_from := p_date_from;
1195 l_new.date_to := p_date_to;
1196 l_new.primary_flag := p_primary_flag;
1197 l_new.postal_code := p_postal_code;
1198 l_new.region_2 := p_region_2;
1199 l_new.address_type := p_address_type;
1200 l_new.address_id := p_address_id;
1201 --
1202 ben_add_ler.ler_chk(p_old => l_old,
1203 p_new => l_new,
1204 p_effective_date => p_date_from);
1205 end if;
1206 -- ==============================================================
1207 -- Call to HZ V2 Address API for updating address in HZ_LOCATIONS
1208 -- as per OSS HRMS integration
1209 -- ==============================================================
1210 open c1;
1211 fetch c1 into l_c2;
1212 close c1;
1213 l_c2.party_id := l_c1.party_id;
1214 InsUpd_OSS_Person_Add
1215 (p_addr_rec_new => l_c2
1216 ,p_addr_rec_old => l_c1
1217 ,p_action => 'UPDATE'
1218 ,p_effective_date => p_date_from
1219 );
1220 -- ==============================================================
1221 --
1222 -- End of Fix for WWBUG 1408379
1223 --
1224 p_default_primary := per_addresses_pkg.does_primary_exist(p_person_id
1225 ,p_business_group_id
1226 ,p_end_of_time);
1227 --
1228 --OPEN get_install_info;
1229 --FETCH get_install_info INTO l_status;
1230 --CLOSE get_install_info;
1231
1232 /* Check For if this is a primary address for a
1233 US employee when payroll is installed will be made in default_tax_with_validation procedure
1234 Changes are made as an impact of datetracking of W4 form*/
1235
1236 --IF l_status = 'I' THEN
1237 open get_legislation_code;
1238 fetch get_legislation_code into l_legislation_code;
1239 close get_legislation_code;
1240
1241 IF l_legislation_code = 'US' THEN
1242 pay_us_emp_dt_tax_rules.default_tax_with_validation(
1243 p_assignment_id => NULL,
1244 p_person_id => p_person_id,
1245 p_effective_start_date => p_date_from,
1246 p_effective_end_date => p_date_to,
1247 p_session_date => NULL,
1248 p_business_group_id => p_business_group_id,
1249 p_from_form => 'Address',
1250 p_mode => NULL,
1251 p_location_id => NULL,
1252 p_return_code => l_return_code,
1253 p_return_text => l_return_text);
1254 end if;
1255 --
1256 -- No need to check return, because if not possible then no
1257 -- user message ness.
1258 --END IF;
1259 end update_row;
1260 --
1261 function does_primary_exist(p_person_id NUMBER
1262 ,p_business_group_id NUMBER
1263 ,p_end_of_time DATE) return VARCHAR2 is
1264 cursor primary_address is
1265 select 'Y'
1266 from per_addresses pa
1267 , fnd_sessions fs
1268 where pa.business_group_id + 0 = p_business_group_id
1269 and pa.person_id = p_person_id
1270 and pa.primary_flag = 'Y'
1271 and fs.session_id(+) = userenv('sessionid')
1272 and nvl(fs.effective_date,sysdate) between pa.date_from
1273 and nvl(pa.date_to,p_end_of_time);
1274 --
1275 -- local variable
1276 --
1277 l_exists VARCHAR2(1);
1278 begin
1279 open primary_address;
1280 fetch primary_address into l_exists;
1281 loop
1282 exit when primary_address%NOTFOUND;
1283 fetch primary_address into l_exists;
1284 end loop;
1285 if primary_address%ROWCOUNT <>0 then
1286 return 'N';
1287 else
1288 return 'Y';
1289 end if;
1290 end does_primary_exist;
1291 --
1292 procedure find_gaps(p_person_id NUMBER
1293 ,p_end_of_time DATE) is
1294 --
1295 --
1296 cursor get_addr is
1297 select 'Y'
1298 from per_addresses pa1
1299 where pa1.primary_flag = 'N'
1300 and pa1.person_id = p_person_id
1301 and not exists (select 'x'
1302 from per_addresses pa2
1303 where pa2.person_id = pa1.person_id
1304 and pa2.primary_flag = 'Y'
1305 and pa2.address_id <> pa1.address_id
1306 and pa2.date_from <=pa1.date_from
1307 and nvl(pa2.date_to, p_end_of_time) >=
1308 nvl(pa1.date_to, p_end_of_time));
1309 --
1310 l_gap_exists VARCHAR2(1);
1311 --
1312 begin
1313 open get_addr;
1314 fetch get_addr into l_gap_exists;
1315 if get_addr%FOUND then
1316 hr_utility.set_message(801,'HR_51030_ADDR_PRIM_GAP');
1317 hr_utility.raise_error;
1318 end if;
1319 close get_addr;
1320 end find_gaps;
1321 --
1322 procedure get_default_style(p_legislation_code VARCHAR2
1323 ,p_default_country IN OUT NOCOPY VARCHAR2
1324 ,p_default_style IN OUT NOCOPY VARCHAR2) is
1325 --
1326 l_geocodes_installed varchar2(1);
1327 l_default varchar2(80);
1328 l_default_code varchar2(30);
1329 --
1330 -- Bug fix 3648688
1331 -- Added application_id = 800 to cursors local_default and
1332 -- global_default to improve performance.
1333
1334 cursor local_default is
1335 select descriptive_flex_context_name, descriptive_flex_context_code
1336 from fnd_descr_flex_contexts_vl
1337 where (descriptive_flex_context_code = p_legislation_code
1338 or (p_legislation_code = descriptive_flex_context_code
1339 and p_legislation_code in ('CA','US')
1340 and l_geocodes_installed = 'Y'))
1341 and descriptive_flexfield_name = 'Address Structure'
1342 and application_id = 800 -- bug fix 3648688.
1343 and enabled_flag = 'Y'
1344 ;
1345 --
1346 cursor global_default is
1347 select descriptive_flex_context_name,descriptive_flex_context_code
1348 from fnd_descr_flex_contexts_vl
1349 where substr(descriptive_flex_context_code,1,2)= p_legislation_code
1350 and descriptive_flexfield_name = 'Address Structure'
1351 and application_id = 800 -- bug fix 3648688.
1352 and enabled_flag = 'Y';
1353 --
1354 begin
1355 --
1356 l_geocodes_installed := hr_general.chk_geocodes_installed;
1357 --
1358 open local_default;
1359 fetch local_default into l_default,l_default_code;
1360 if local_default%notfound then
1361 open global_default;
1362 fetch global_default into l_default,l_default_code;
1363 close global_default;
1364 end if;
1365 close local_default;
1366 --
1367 p_default_country := l_default;
1368 p_default_style := l_default_code;
1369 --
1370 hr_utility.set_location('l_default'||l_default,1);
1371 hr_utility.set_location('l_default_code'||l_default_code,2);
1372 end get_default_style;
1373 --
1374 procedure get_addresses(p_legislation_code VARCHAR2
1375 ,p_default_country IN OUT NOCOPY VARCHAR2) is
1376 begin
1377
1378 -- bug fix 3648688
1379 -- Application_id = 800 is added to sql to improve performance.
1380
1381 select ft.territory_short_name
1382 into p_default_country
1383 from fnd_territories_vl ft
1384 , fnd_descr_flex_contexts fdfc
1385 where ft.territory_code = p_legislation_code
1386 and fdfc.descriptive_flex_context_code = ft.territory_code
1387 and fdfc.descriptive_flexfield_name ='Address Structure'
1388 and fdfc.application_id = 800 -- bug fix 3648688
1389 and fdfc.enabled_flag = 'Y';
1390 --
1391 exception
1392 when no_data_found then
1393 null;
1394 when others then
1395 raise;
1396 end get_addresses;
1397 --
1398 procedure form_startup1(p_person_id NUMBER
1399 ,p_business_group_id NUMBER
1400 ,p_end_of_time DATE
1401 ,p_primary_flag IN OUT NOCOPY VARCHAR2
1402 ,p_legislation_code VARCHAR2
1403 ,p_default_country IN OUT NOCOPY VARCHAR2
1404 ,p_default_style IN OUT NOCOPY VARCHAR2) is
1405 begin
1406 p_primary_flag := per_addresses_pkg.does_primary_exist(p_person_id
1407 ,p_business_group_id
1408 ,p_end_of_time);
1409 --
1410 per_addresses_pkg.get_default_style(p_legislation_code
1411 ,p_default_country
1412 ,p_default_style);
1413 --
1414 end form_startup1;
1415 --
1416 procedure form_startup(p_person_id NUMBER
1417 ,p_business_group_id NUMBER
1418 ,p_end_of_time DATE
1419 ,p_primary_flag IN OUT NOCOPY VARCHAR2
1420 ,p_legislation_code VARCHAR2
1421 ,p_default_country IN OUT NOCOPY VARCHAR2) is
1422 begin
1423 p_primary_flag := per_addresses_pkg.does_primary_exist(p_person_id
1424 ,p_business_group_id
1425 ,p_end_of_time);
1426 --
1427 per_addresses_pkg.get_addresses(p_legislation_code
1428 ,p_default_country);
1429 --
1430 end form_startup;
1431 ------------------------- BEGIN: validate_address --------------------
1432 --
1433 --NAME
1434 -- validate_address
1435 --DESCRIPTION
1436 -- Validates the Address Entered.
1437 --PARAMETERS
1438 -- p_person_id : Unique Id of the person.
1439 -- p_end_of_time :Ultimate date on Oracle system 31-Dec-4712.
1440 --
1441 -----------------------------------------------------------------------
1442 --
1443 PROCEDURE validate_address(p_person_id INTEGER
1444 ,p_end_of_time DATE) is
1445 --
1446 v_dummy VARCHAR2(30);
1447 -- primary flag test.
1448 l_primary_flag VARCHAR2(1) :='Y';
1449 l_date_from date;
1450 l_date_to date;
1451 a_date_from date;
1452 a_date_to date;
1453 b_date_from date;
1454 b_date_to date;
1455 v_effective_date date;
1456 --
1457 cursor has_addrs is
1458 select 'Y'
1459 from per_addresses pa
1460 where pa.person_id = p_person_id;
1461 --
1462 cursor check_min_is_primary is
1463 select 'Y'
1464 from per_addresses pa
1465 where person_id = p_person_id
1466 and date_from = l_date_from
1467 and primary_flag = 'Y';
1468 --
1469 cursor has_primary is
1470 select 'Y'
1471 from per_addresses pa
1472 where person_id = p_person_id
1473 and primary_flag = 'Y';
1474 --
1475 cursor get_mins is
1476 select min(date_from)
1477 from per_addresses
1478 where person_id = p_person_id;
1479
1480
1481 cursor get_payroll is
1482 select effective_start_date
1483 from per_assignments_f asg
1484 where asg.person_id = p_person_id
1485 and asg.payroll_id is not null
1486 and asg.assignment_type ='E' -- fix for bug14248791
1487 order by effective_start_date;
1488
1489 /*
1490 cursor get_next(l_date_from in date) is
1491 select date_to
1492 from per_addresses pa
1493 where date_from = l_date_from
1494 and person_id = p_person_id
1495 and primary_flag = 'Y';
1496 */
1497 cursor get_next is
1498 select date_from,
1499 date_to
1500 from per_addresses pa
1501 where person_id = p_person_id
1502 and primary_flag = 'Y'
1503 order by date_from;
1504 --
1505 cursor get_overlapping is
1506 select 'Y'
1507 from per_addresses pa
1508 where person_id = p_person_id
1509 and primary_flag = 'Y'
1510 and exists (select 'Y'
1511 from per_addresses pa2
1512 where pa.person_id = pa2.person_id
1513 and pa.address_id <> pa2.address_id
1514 and pa2.primary_flag = 'Y'
1515 and ((pa2.date_from between pa.date_from and
1516 nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY')))
1517 or (pa.date_from between pa2.date_from
1518 and nvl(pa2.date_to,to_date('31-12-4712','DD-MM-YYYY')) )
1519 ));
1520 --
1521 -- start changes for bug#10067018
1522 -- start changes for bug13562523
1523 cursor check_emp_cwk_person_type1(date1 date) is
1524 select 'Y'
1525 from per_person_type_usages_f p, per_person_types t
1526 where p.person_id = p_person_id
1527 and p.person_type_id = t.person_type_id
1528 and t.system_person_type in ('EMP','CWK')
1529 and nvl(date1,to_date('31-12-4712','DD-MM-YYYY')) between effective_start_date and effective_end_date-1;
1530
1531 cursor check_emp_cwk_person_type2(date1 date) is
1532 select effective_start_date, effective_end_date
1533 from per_person_type_usages_f p, per_person_types t
1534 where p.person_id = p_person_id
1535 and p.person_type_id = t.person_type_id
1536 and t.system_person_type in ('EMP','CWK')
1537 and effective_start_date > nvl(date1,to_date('31-12-4712','DD-MM-YYYY')) ;
1538 -- end changes for bug13562523
1539 -- end changes for bug #10067018
1540
1541
1542 --
1543 -- Validate Address type Cursor
1544 --
1545 cursor validate_address_types is
1546 select 1
1547 from per_addresses pa
1548 where pa.address_type is not null
1549 and pa.person_id = p_person_id
1550 and exists( select 1
1551 from per_addresses pa2
1552 where pa2.address_id <> pa.address_id
1553 and pa2.address_type is not null
1554 and pa.address_type = pa2.address_type
1555 and pa.person_id = pa2.person_id
1556 and ((pa.date_from between pa2.date_from and nvl(pa2.date_to,
1557 p_end_of_time))
1558 or
1559 (nvl(pa.date_to,p_end_of_time) between pa2.date_from and
1560 nvl(pa2.date_to, p_end_of_time))
1561 ));
1562 --
1563 l_proc VARCHAR2(50) := 'per_addresses_pkg.validate_address';
1564 --
1565 begin
1566 --
1567 hr_utility.set_location('Entering : '||l_proc,10);
1568
1569 if per_per_bus.return_legislation_code(p_person_id) ='US' -- changes for bug14230926
1570 and ((hr_general.chk_product_installed(801) = 'TRUE') or
1571 (fnd_profile.value('PER_ENABLE_DTW4') = 'Y')) then
1572
1573 open get_payroll;
1574 fetch get_payroll into v_effective_date;
1575
1576 if get_payroll%Found then
1577 close get_payroll;
1578 open get_next ;
1579 fetch get_next into a_date_from, a_date_to;
1580 if get_next%found then
1581 close get_next;
1582 if a_date_from>v_effective_date then
1583 hr_utility.set_message(800,'PER_PAYROLL_EXISTS');
1584 hr_utility.raise_error;
1585 end if;
1586 else
1587 close get_next;
1588 hr_utility.set_message(800,'PER_51831_NO_PRI_ADDRESS');
1589 hr_utility.raise_error;
1590 end if;
1591 else
1592 close get_payroll;
1593 end if;
1594 end if;
1595 --
1596 open has_addrs;
1597 fetch has_addrs into v_dummy;
1598 --
1599 if has_addrs%notfound then
1600 --
1601 hr_utility.set_location(l_proc,20);
1602 --
1603 close has_addrs;
1604 --
1605 else
1606 --
1607 close has_addrs;
1608 --
1609 hr_utility.set_location(l_proc,30);
1610 --
1611 -- Get the start and end of the first address
1612 --
1613 open get_mins;
1614 fetch get_mins into l_date_from;
1615 --
1616 close get_mins;
1617 --
1618 -- Establish whether the minimum row is the primary
1619 --
1620 open check_min_is_primary;
1621 fetch check_min_is_primary into v_dummy;
1622 --
1623 if check_min_is_primary%NOTFOUND then
1624 --
1625 hr_utility.set_location(l_proc,40);
1626 --
1627 -- Minimum is not a primary,
1628 -- does a primary exist?
1629 --
1630 close check_min_is_primary;
1631 --
1632 open has_primary;
1633 fetch has_primary into v_dummy;
1634 --
1635 if has_primary%NOTFOUND then
1636 --
1637 hr_utility.set_location(l_proc,50);
1638 --
1639 -- No primary, pass relevant error back
1640 --
1641 close has_primary;
1642 --
1643 hr_utility.set_message(801,'HR_7144_PER_NO_PRIM_ADD');
1644 hr_utility.raise_error;
1645 --
1646 else
1647 --
1648 -- Primary exists, and as minimum not the primary
1649 -- a gap in the primaries also exists
1650 --
1651 close has_primary;
1652 --
1653 hr_utility.set_message(800,'PER_52473_ADDR_SEC_AFTER_PRIM');
1654 hr_utility.raise_error;
1655 --
1656 end if;
1657 --
1658 else
1659 --
1660 hr_utility.set_location(l_proc,60);
1661 --
1662 close check_min_is_primary;
1663 --
1664 end if;
1665 --
1666 --
1667
1668 -- Bug 13570836
1669 open get_overlapping;
1670 fetch get_overlapping into v_dummy;
1671 --
1672 if get_overlapping%FOUND then
1673 --
1674 close get_overlapping;
1675 --
1676 hr_utility.set_message(800,'HR_50442_PER_PRIM_ADDR_OVERLAP');
1677 hr_utility.raise_error;
1678 --
1679 end if;
1680 --
1681 close get_overlapping;
1682 -- Bug 13570836
1683 /*
1684 open get_next(l_date_from);
1685 fetch get_next into l_date_to;
1686 close get_next;
1687 if l_date_to is not null
1688 then
1689 loop
1690 hr_utility.set_location('hr_person.validate_address',5);
1691 exit when l_date_to is null;
1692 l_date_from := l_date_to + 1;
1693 open get_next(l_date_from);
1694 fetch get_next into l_date_to;
1695 hr_utility.set_location('hr_person.validate_address',6);
1696 if get_next%NOTFOUND
1697 then
1698 hr_utility.set_message(801,'HR_51030_ADDR_PRIM_GAP');
1699 hr_utility.raise_error;
1700 end if;
1701 hr_utility.set_location('hr_person.validate_address',7);
1702 close get_next;
1703 end loop;
1704 end if;
1705 */
1706 --
1707 -- Get the fist primary address
1708 --
1709 open get_next;
1710 fetch get_next into a_date_from, a_date_to;
1711 --
1712 hr_utility.set_location(l_proc||'/'||a_date_from||'/'||a_Date_to,65);
1713 --
1714 loop
1715 --
1716 hr_utility.set_location(l_proc,70);
1717 --
1718 fetch get_next into b_date_from, b_date_to;
1719 --
1720 hr_utility.set_location(l_proc||'/'||b_date_from||'/'||b_Date_to,75);
1721 --
1722 -- If there is another primary address then check that the
1723 -- dates match up so there is no gaps between the end of one
1724 -- primary address and the beginning of the next.
1725 --
1726 if get_next%FOUND then
1727 --
1728 hr_utility.set_location(l_proc,80);
1729 --
1730 if b_date_from <> a_date_to + 1 then
1731 --
1732 hr_utility.set_location(l_proc,90);
1733 --
1734 close get_next;
1735 --
1736 hr_utility.set_message(801,'HR_51030_ADDR_PRIM_GAP');
1737 hr_utility.raise_error;
1738 --
1739 end if;
1740 --
1741 else
1742 --
1743 hr_utility.set_location(l_proc,100);
1744
1745 -- start changes for bug #10067018
1746 --
1747 open check_emp_cwk_person_type1(a_date_to);
1748 fetch check_emp_cwk_person_type1 into v_dummy;
1749 --
1750 if check_emp_cwk_person_type1%FOUND then
1751 close check_emp_cwk_person_type1;
1752 -- end changes for bug #10067018
1753 --
1754 -- This makes no sense if I am trying to end date an address record
1755 -- through fastpath and then create a new address record via fastpath.
1756 -- This is preventing the action from taking place hence the code has
1757 -- been removed. WWBUG 1814842.
1758 --
1759 -- Added back in to fix bug 2273441
1760 --
1761 if a_date_to is not null then
1762 --
1763 close get_next;
1764 --
1765 hr_utility.set_message(801,'HR_7144_PER_NO_PRIM_ADD');
1766 hr_utility.raise_error;
1767 --
1768 end if;
1769
1770 -- start changes for bug #10067018
1771 else
1772 close check_emp_cwk_person_type1;
1773 hr_utility.set_location(l_proc,110);
1774 --
1775 --exit;
1776 --
1777 end if;
1778
1779
1780 open check_emp_cwk_person_type2(a_date_to);
1781 fetch check_emp_cwk_person_type2 into l_date_from, l_date_to;
1782
1783 if check_emp_cwk_person_type2%FOUND then
1784 begin
1785 /* select 'Y'
1786 into v_dummy
1787 from per_addresses pa
1788 where person_id = p_person_id
1789 and primary_flag = 'N'
1790 and date_from between l_date_from and l_date_to;
1791
1792 if SQL%FOUND then
1793 close get_next;
1794 close check_emp_cwk_person_type2;
1795 hr_utility.set_message(801,'HR_7144_PER_NO_PRIM_ADD');
1796 hr_utility.raise_error;
1797 end if; */
1798
1799 -- changes for bug13602807
1800 select 'Y'
1801 into v_dummy
1802 from per_addresses pa
1803 where person_id = p_person_id
1804 and primary_flag = 'Y'
1805 and l_date_to between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY')) ;
1806
1807
1808 exception when no_data_found then
1809 close get_next;
1810 close check_emp_cwk_person_type2;
1811 hr_utility.set_message(801,'HR_7144_PER_NO_PRIM_ADD');
1812 hr_utility.raise_error;
1813
1814 end;
1815
1816 -- end of changes for bug13602807
1817 else
1818
1819 close check_emp_cwk_person_type2;
1820 hr_utility.set_location(l_proc,130);
1821 exit;
1822 end if;
1823 -- end changes for bug #10067018
1824 --
1825 end if;
1826 --
1827 exit when a_date_to is null;
1828 --
1829 a_date_from := b_date_from;
1830 a_date_to := b_date_to;
1831 --
1832 end loop;
1833 --
1834 hr_utility.set_location(l_proc,120);
1835 --
1836 close get_next;
1837 --
1838 -- Primary exists and form trying to enter primary
1839 -- then raise overlapping error.
1840 --
1841 --
1842 end if;
1843 --
1844 open validate_address_types;
1845 fetch validate_address_types into v_dummy;
1846 --
1847 if validate_address_types%FOUND then
1848 --
1849 close validate_address_types;
1850 --
1851 hr_utility.set_message(800,'PER_52244_ONE_ADD_OF_EACH_TYPE');
1852 hr_utility.raise_error;
1853 --
1854 end if;
1855 --
1856 close validate_address_types;
1857 --
1858 hr_utility.set_location('Leaving : '||l_proc,999);
1859 --
1860 end validate_address;
1861 --
1862 END PER_ADDRESSES_PKG;