[Home] [Help]
PACKAGE BODY: APPS.PQP_ADDRESS_SYNCHRONIZATION
Source
1 Package Body PQP_Address_Synchronization AS
2 /* $Header: pqaddsyn.pkb 120.0 2005/05/29 01:41:47 appldev noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_pkg CONSTANT Varchar2(150) := 'PQP_Address_Synchronization.';
8 -- =============================================================================
9 -- ~ Package Body Cursor variables:
10 -- =============================================================================
11 -- Cursor to check if person is student
12 CURSOR csr_stu (c_person_id IN Number) IS
13 SELECT pei.pei_information5
14 FROM per_people_extra_info pei
15 WHERE pei.information_type = 'PQP_OSS_PERSON_DETAILS'
16 AND pei.pei_information_category = 'PQP_OSS_PERSON_DETAILS'
17 AND pei.person_id = c_person_id;
18
19 -- Get person party id
20 CURSOR csr_partyid (c_person_id IN Number) IS
21 SELECT ppf.party_id,pbg.legislation_code
22 FROM per_all_people_f ppf
23 ,per_business_groups_perf pbg
24 WHERE ppf.person_id = c_person_id
25 AND pbg.business_group_id = ppf.business_group_id;
26
27 -- Get the primary address from OSS
28 g_hz_add_sql CONSTANT Varchar2(1000) :=
29 'SELECT hps.party_site_id ' ||
30 ' ,hzl.location_id ' ||
31 ' ,hzl.object_version_number ' ||
32 ' ,hps.object_version_number ' ||
33 ' ,hzl.Rowid ' ||
34 ' ,hzl.last_update_date ' ||
35 ' FROM hz_party_sites hps ' ||
36 ' ,hz_locations hzl ' ||
37 ' WHERE hps.party_id = :c_party_id ' ||
38 ' AND hzl.location_id(+) = hps.location_id ' ||
39 ' AND hps.status = '||'''A'''||
40 ' AND hps.identifying_address_flag(+) = '||'''Y''';
41
42 -- Cursor to get the leg. code
43 CURSOR csr_bg_code (c_bg_grp_id IN Number) IS
44 SELECT pbg.legislation_code
45 FROM per_business_groups pbg
46 WHERE pbg.business_group_id = c_bg_grp_id;
47
48 -- =============================================================================
49 -- ~ Addr_DDF_Ins:
50 -- =============================================================================
51 PROCEDURE Addr_DDF_Ins
52 (p_address_id IN Number
53 ,p_business_group_id IN Number
54 ,p_person_id IN Number
55 ,p_party_id IN Number
56 ,p_date_from IN Date
57 ,p_primary_flag IN Varchar2
58 ,p_style IN Varchar2
59 ,p_address_line1 IN Varchar2
60 ,p_address_line2 IN Varchar2
61 ,p_address_line3 IN Varchar2
62 ,p_address_type IN Varchar2
63 ,p_country IN Varchar2
64 ,p_date_to IN Date
65 ,p_postal_code IN Varchar2
66 ,p_region_1 IN Varchar2
67 ,p_region_2 IN Varchar2
68 ,p_region_3 IN Varchar2
69 ,p_telephone_number_1 IN Varchar2
70 ,p_telephone_number_2 IN Varchar2
71 ,p_telephone_number_3 IN Varchar2
72 ,p_town_or_city IN Varchar2
73 ,p_add_information13 IN Varchar2
74 ,p_add_information14 IN Varchar2
75 ,p_add_information15 IN Varchar2
76 ,p_add_information16 IN Varchar2
77 ,p_add_information17 IN Varchar2
78 ,p_add_information18 IN Varchar2
79 ,p_add_information19 IN Varchar2
80 ,p_add_information20 IN Varchar2
81 ,p_object_version_number IN Number
82 ,p_effective_date IN Date
83 ,p_validate_county IN Boolean
84 ) AS
85
86 TYPE csr_oss_t IS REF CURSOR;
87 csr_hz_loc csr_oss_t;
88 --
89 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Addr_DDF_Ins';
90 l_Stu_OSSData_Sync Varchar2(5);
91 l_return_flag Boolean;
92 l_addr_rec per_addresses%ROWTYPE;
93 l_addr_rec_new per_addresses%ROWTYPE;
94 l_addr_rec_old per_addresses%ROWTYPE;
95 l_last_update_date Date;
96 l_rowid ROWID;
97 --
98 l_party_site_id Number;
99 l_location_id Number;
100 l_location_ovn Number;
101 l_party_site_ovn Number;
102 l_hz_loc_rowid Rowid;
103 l_hz_loc_upd_dt Date;
104 --
105 l_create_oss_addr Boolean;
106 l_update_oss_addr Boolean;
107 --
108 l_msg_data Varchar2(2000);
109 l_return_status Varchar2(5);
110 --
111
112 BEGIN
113 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
114 OPEN csr_stu (c_person_id => p_person_id);
115 FETCH csr_stu INTO l_Stu_OSSData_Sync;
116 CLOSE csr_stu;
117 --
118 IF Nvl(l_Stu_OSSData_Sync,'-1') <> 'Y' OR
119 Nvl(p_primary_flag,'-1') <> 'Y' OR
120 Nvl(Fnd_Profile.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'N' OR
121 p_party_id IS NULL
122 THEN
123 l_return_flag := TRUE;
124 END IF;
125 --
126 IF l_return_flag THEN
127 RETURN;
128 END IF;
129 OPEN csr_hz_loc FOR g_hz_add_sql
130 Using p_party_id;
131 FETCH csr_hz_loc INTO
132 l_party_site_id,
133 l_location_id,
134 l_location_ovn,
135 l_party_site_ovn,
136 l_hz_loc_rowid,
137 l_hz_loc_upd_dt;
138 --
139 IF csr_hz_loc%NOTFOUND THEN
140 l_create_oss_addr := TRUE;
141 ELSE
142 l_update_oss_addr := TRUE;
143 END IF;
144 CLOSE csr_hz_loc;
145 --
146 l_addr_rec.address_id := p_address_id;
147 l_addr_rec.business_group_id := p_business_group_id;
148 l_addr_rec.person_id := p_person_id;
149 l_addr_rec.party_id := p_party_id;
150 l_addr_rec.date_from := p_date_from;
151 l_addr_rec.primary_flag := p_primary_flag;
152 l_addr_rec.style := p_style;
153 l_addr_rec.address_line1 := p_address_line1;
154 l_addr_rec.address_line2 := p_address_line2;
155 l_addr_rec.address_line3 := p_address_line3;
156 l_addr_rec.address_type := p_address_type;
157 l_addr_rec.country := p_country;
158 l_addr_rec.date_to := p_date_to;
159 l_addr_rec.postal_code := p_postal_code;
160 l_addr_rec.region_1 := p_region_1;
161 l_addr_rec.region_2 := p_region_2;
162 l_addr_rec.region_3 := p_region_3;
163 l_addr_rec.telephone_number_1 := p_telephone_number_1;
164 l_addr_rec.telephone_number_2 := p_telephone_number_2;
165 l_addr_rec.telephone_number_3 := p_telephone_number_3;
166 l_addr_rec.town_or_city := p_town_or_city;
167 l_addr_rec.add_information13 := p_add_information13;
168 l_addr_rec.add_information14 := p_add_information14;
169 l_addr_rec.add_information15 := p_add_information15;
170 l_addr_rec.add_information16 := p_add_information16;
171 l_addr_rec.add_information17 := p_add_information17;
172 l_addr_rec.add_information18 := p_add_information18;
173 l_addr_rec.add_information19 := p_add_information19;
174 l_addr_rec.add_information20 := p_add_information20;
175 l_addr_rec.object_version_number := p_object_version_number;
176 --
177 IF l_create_oss_addr THEN
178 PQP_HRTCA_Integration.Create_Address_HR_To_TCA
179 (p_business_group_id => p_business_group_id
180 ,p_person_id => p_person_id
181 ,p_party_id => p_party_id
182 ,p_address_id => p_address_id
183 ,p_effective_date => p_effective_date
184 ,p_per_addr_rec_new => l_addr_rec
185 -- TCA
186 ,p_party_type => 'PERSON'
187 ,p_action => 'INSERT'
188 ,p_status => 'A'
189 -- In Out Variables
190 ,p_location_id => l_location_id
191 ,p_party_site_id => l_party_site_id
192 ,p_last_update_date => l_last_update_date
193 ,p_party_site_ovn => l_party_site_ovn
194 ,p_location_ovn => l_location_ovn
195 ,p_rowid => l_rowid
196 -- Out Variables
197 ,p_return_status => l_return_status
198 ,p_msg_data => l_msg_data
199 );
200 END IF;
201
202 IF l_update_oss_addr THEN
203 l_addr_rec_new := l_addr_rec;
204 l_addr_rec_old := l_addr_rec;
205 PQP_HRTCA_Integration.Update_Address_HR_To_TCA
206 (p_business_group_id => p_business_group_id
207 ,p_person_id => p_person_id
208 ,p_party_id => p_party_id
209 ,p_address_id => p_address_id
210 ,p_effective_date => p_effective_date
211 ,p_per_addr_rec_new => l_addr_rec_new
212 ,p_per_addr_rec_old => l_addr_rec_old
213 -- TCA
214 ,p_party_type => 'PERSON'
215 ,p_action => 'UPDATE'
216 ,p_status => 'A'
217 -- In Out Variables
218 ,p_location_id => l_location_id
219 ,p_party_site_id => l_party_site_id
220 ,p_last_update_date => l_hz_loc_upd_dt
221 ,p_party_site_ovn => l_party_site_ovn
222 ,p_location_ovn => l_location_ovn
223 ,p_rowid => l_hz_loc_rowid
224 -- Out Variables
225 ,p_return_status => l_return_status
226 ,p_msg_data => l_msg_data
227 );
228 END IF;
229
230 IF l_return_status IN ('E','U') THEN
231 Hr_Utility.set_location('SQLCODE :'||SQLCODE,90);
232 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
233 Hr_Utility.set_message_token('GENERIC_TOKEN',l_msg_data );
234 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
235 Hr_Utility.raise_error;
236 END IF;
237
238 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
239
240 END Addr_DDF_Ins;
241 -- =============================================================================
242 -- ~ Addr_DDF_Upd:
243 -- =============================================================================
244 PROCEDURE Addr_DDF_Upd
245 (p_address_id IN Number
246 ,p_business_group_id IN Number
247 ,p_person_id IN Number
248 ,p_date_from IN Date
249 ,p_address_line1 IN Varchar2
250 ,p_address_line2 IN Varchar2
251 ,p_address_line3 IN Varchar2
252 ,p_address_type IN Varchar2
253 ,p_country IN Varchar2
254 ,p_date_to IN Date
255 ,p_postal_code IN Varchar2
256 ,p_region_1 IN Varchar2
257 ,p_region_2 IN Varchar2
258 ,p_region_3 IN Varchar2
259 ,p_telephone_number_1 IN Varchar2
260 ,p_telephone_number_2 IN Varchar2
261 ,p_telephone_number_3 IN Varchar2
262 ,p_town_or_city IN Varchar2
263 ,p_add_information13 IN Varchar2
267 ,p_add_information17 IN Varchar2
264 ,p_add_information14 IN Varchar2
265 ,p_add_information15 IN Varchar2
266 ,p_add_information16 IN Varchar2
268 ,p_add_information18 IN Varchar2
269 ,p_add_information19 IN Varchar2
270 ,p_add_information20 IN Varchar2
271 ,p_object_version_number IN Number
272 ,p_effective_date IN Date
273 ,p_prflagval_override IN Boolean
274 ,p_validate_county IN Boolean
275 -- Old
276 ,p_business_group_id_o IN Number
277 ,p_person_id_o IN Number
278 ,p_date_from_o IN Date
279 ,p_primary_flag_o IN Varchar2
280 ,p_style_o IN Varchar2
281 ,p_address_line1_o IN Varchar2
282 ,p_address_line2_o IN Varchar2
283 ,p_address_line3_o IN Varchar2
284 ,p_address_type_o IN Varchar2
285 ,p_country_o IN Varchar2
286 ,p_date_to_o IN Date
287 ,p_postal_code_o IN Varchar2
288 ,p_region_1_o IN Varchar2
289 ,p_region_2_o IN Varchar2
290 ,p_region_3_o IN Varchar2
291 ,p_telephone_number_1_o IN Varchar2
292 ,p_telephone_number_2_o IN Varchar2
293 ,p_telephone_number_3_o IN Varchar2
294 ,p_town_or_city_o IN Varchar2
295 ,p_add_information13_o IN Varchar2
296 ,p_add_information14_o IN Varchar2
297 ,p_add_information15_o IN Varchar2
298 ,p_add_information16_o IN Varchar2
299 ,p_add_information17_o IN Varchar2
300 ,p_add_information18_o IN Varchar2
301 ,p_add_information19_o IN Varchar2
302 ,p_add_information20_o IN Varchar2
303 ,p_object_version_number_o IN Number
304 ,p_party_id_o IN Number
305 ) AS
306 TYPE csr_oss_t IS REF CURSOR;
307 csr_hz_loc csr_oss_t;
308 --
309 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Addr_DDF_Upd';
310 l_Stu_OSSData_Sync Varchar2(5);
311 l_return_flag Boolean;
312 l_addr_rec_new per_addresses%ROWTYPE;
313 l_addr_rec_old per_addresses%ROWTYPE;
314 --
315 l_party_site_id Number;
316 l_location_id Number;
317 l_location_ovn Number;
318 l_party_site_ovn Number;
319 l_hz_loc_rowid Rowid;
320 l_hz_loc_upd_dt Date;
321 --
322 l_msg_data Varchar2(2000);
323 l_return_status Varchar2(5);
324 l_create_oss_addr Boolean;
325 l_update_oss_addr Boolean;
326 --
327 BEGIN
328 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
329 OPEN csr_stu (c_person_id => p_person_id);
330 FETCH csr_stu INTO l_Stu_OSSData_Sync;
331 CLOSE csr_stu;
332 --
333 IF Nvl(l_Stu_OSSData_Sync,'-1') <> 'Y' OR
334 Nvl(p_primary_flag_o,'-1') <> 'Y' OR
335 Nvl(Fnd_Profile.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'N' OR
336 p_party_id_o IS NULL
337 THEN
338 l_return_flag := TRUE;
339 END IF;
340 --
341 IF l_return_flag THEN
342 RETURN;
343 END IF;
344 OPEN csr_hz_loc FOR g_hz_add_sql
345 Using p_party_id_o;
346 FETCH csr_hz_loc INTO
347 l_party_site_id,
348 l_location_id,
349 l_location_ovn,
350 l_party_site_ovn,
351 l_hz_loc_rowid,
352 l_hz_loc_upd_dt;
353 IF csr_hz_loc%NOTFOUND THEN
354 l_create_oss_addr := TRUE;
355 ELSE
356 l_update_oss_addr := TRUE;
357 END IF;
358 CLOSE csr_hz_loc;
359
360 l_addr_rec_old.address_id := p_address_id;
361 -- Old Values
362 l_addr_rec_old.person_id := p_person_id_o;
363 l_addr_rec_old.business_group_id := p_business_group_id_o;
364 l_addr_rec_old.party_id := p_party_id_o;
365 l_addr_rec_old.primary_flag := p_primary_flag_o;
366 l_addr_rec_old.date_from := p_date_from_o;
367 l_addr_rec_old.date_to := p_date_to_o;
368 l_addr_rec_old.style := p_style_o;
369 l_addr_rec_old.address_line1 := p_address_line1_o;
370 l_addr_rec_old.address_line2 := p_address_line2_o;
371 l_addr_rec_old.address_line3 := p_address_line3_o;
372 l_addr_rec_old.address_type := p_address_type_o;
373 l_addr_rec_old.country := p_country_o;
374 l_addr_rec_old.postal_code := p_postal_code_o;
375 l_addr_rec_old.region_1 := p_region_1_o;
376 l_addr_rec_old.region_2 := p_region_2_o;
377 l_addr_rec_old.region_3 := p_region_3_o;
378 l_addr_rec_old.telephone_number_1 := p_telephone_number_1_o;
379 l_addr_rec_old.telephone_number_2 := p_telephone_number_2_o;
380 l_addr_rec_old.telephone_number_3 := p_telephone_number_3_o;
381 l_addr_rec_old.town_or_city := p_town_or_city_o;
382 l_addr_rec_old.add_information13 := p_add_information13_o;
383 l_addr_rec_old.add_information14 := p_add_information14_o;
384 l_addr_rec_old.add_information15 := p_add_information15_o;
385 l_addr_rec_old.add_information16 := p_add_information16_o;
386 l_addr_rec_old.add_information17 := p_add_information17_o;
387 l_addr_rec_old.add_information18 := p_add_information18_o;
388 l_addr_rec_old.add_information19 := p_add_information19_o;
392 -- New Values
389 l_addr_rec_old.add_information20 := p_add_information20_o;
390 l_addr_rec_old.object_version_number := p_object_version_number_o;
391 --
393 --
394 l_addr_rec_new.address_id := p_address_id;
395 l_addr_rec_new.business_group_id := p_business_group_id;
396 l_addr_rec_new.person_id := p_person_id;
397 l_addr_rec_new.date_from := p_date_from;
398 l_addr_rec_new.style := p_style_o;
399 l_addr_rec_new.address_line1 := p_address_line1;
400 l_addr_rec_new.address_line2 := p_address_line2;
401 l_addr_rec_new.address_line3 := p_address_line3;
402 l_addr_rec_new.address_type := p_address_type;
403 l_addr_rec_new.country := p_country;
404 l_addr_rec_new.date_to := p_date_to;
405 l_addr_rec_new.postal_code := p_postal_code;
406 l_addr_rec_new.region_1 := p_region_1;
407 l_addr_rec_new.region_2 := p_region_2;
408 l_addr_rec_new.region_3 := p_region_3;
409 l_addr_rec_new.telephone_number_1 := p_telephone_number_1;
410 l_addr_rec_new.telephone_number_2 := p_telephone_number_2;
411 l_addr_rec_new.telephone_number_3 := p_telephone_number_3;
412 l_addr_rec_new.town_or_city := p_town_or_city;
413 l_addr_rec_new.add_information13 := p_add_information13;
414 l_addr_rec_new.add_information14 := p_add_information14;
415 l_addr_rec_new.add_information15 := p_add_information15;
416 l_addr_rec_new.add_information16 := p_add_information16;
417 l_addr_rec_new.add_information17 := p_add_information17;
418 l_addr_rec_new.add_information18 := p_add_information18;
419 l_addr_rec_new.add_information19 := p_add_information19;
420 l_addr_rec_new.add_information20 := p_add_information20;
421 l_addr_rec_new.object_version_number := p_object_version_number;
422 --
423 IF l_create_oss_addr THEN
424 Pqp_Hrtca_Integration.Create_Address_HR_To_TCA
425 (p_business_group_id => p_business_group_id
426 ,p_person_id => p_person_id
427 ,p_party_id => p_party_id_o
428 ,p_address_id => p_address_id
429 ,p_effective_date => p_effective_date
430 ,p_per_addr_rec_new => l_addr_rec_new
431 -- TCA
432 ,p_party_type => 'PERSON'
433 ,p_action => 'INSERT'
434 ,p_status => 'A'
435 -- In Out Variables
436 ,p_location_id => l_location_id
437 ,p_party_site_id => l_party_site_id
438 ,p_last_update_date => l_hz_loc_upd_dt
439 ,p_party_site_ovn => l_party_site_ovn
440 ,p_location_ovn => l_location_ovn
441 ,p_rowid => l_hz_loc_rowid
442 -- Out Variables
443 ,p_return_status => l_return_status
444 ,p_msg_data => l_msg_data
445 );
446 END IF;
447 --
448 IF l_update_oss_addr THEN
449 Pqp_Hrtca_Integration.Update_Address_HR_To_TCA
450 (p_business_group_id => p_business_group_id
451 ,p_person_id => p_person_id
452 ,p_party_id => p_party_id_o
453 ,p_address_id => p_address_id
454 ,p_effective_date => p_effective_date
455 ,p_per_addr_rec_new => l_addr_rec_new
456 ,p_per_addr_rec_old => l_addr_rec_old
457 -- TCA
458 ,p_party_type => 'PERSON'
459 ,p_action => 'UPDATE'
460 ,p_status => 'A'
461 -- In Out Variables
462 ,p_location_id => l_location_id
463 ,p_party_site_id => l_party_site_id
464 ,p_last_update_date => l_hz_loc_upd_dt
465 ,p_party_site_ovn => l_party_site_ovn
466 ,p_location_ovn => l_location_ovn
467 ,p_rowid => l_hz_loc_rowid
468 -- Out Variables
469 ,p_return_status => l_return_status
470 ,p_msg_data => l_msg_data
471 );
472 END IF;
473 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
474 IF l_return_status IN ('E','U') THEN
475 Hr_Utility.set_location('SQLCODE :'||SQLCODE,90);
476 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
477 Hr_Utility.set_message_token('GENERIC_TOKEN',l_msg_data );
478 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
479 Hr_Utility.raise_error;
480 END IF;
481
482 END Addr_DDF_Upd;
483 -- =============================================================================
484 -- ~ Addr_DDF_Del:
485 -- =============================================================================
486 PROCEDURE Addr_DDF_Del
487 (p_address_id IN Number
488 ,p_business_group_id_o IN Number
489 ,p_date_from_o IN Date
490 ,p_address_line1_o IN Varchar2
491 ,p_address_line2_o IN Varchar2
492 ,p_address_line3_o IN Varchar2
493 ,p_address_type_o IN Varchar2
494 ,p_country_o IN Varchar2
495 ,p_date_to_o IN Date
496 ,p_postal_code_o IN Varchar2
497 ,p_region_1_o IN Varchar2
498 ,p_region_2_o IN Varchar2
499 ,p_region_3_o IN Varchar2
500 ,p_telephone_number_1_o IN Varchar2
501 ,p_telephone_number_2_o IN Varchar2
502 ,p_telephone_number_3_o IN Varchar2
503 ,p_town_or_city_o IN Varchar2
504 ,p_add_information13_o IN Varchar2
505 ,p_add_information14_o IN Varchar2
506 ,p_add_information15_o IN Varchar2
507 ,p_add_information16_o IN Varchar2
508 ,p_add_information17_o IN Varchar2
509 ,p_add_information18_o IN Varchar2
510 ,p_add_information19_o IN Varchar2
511 ,p_add_information20_o IN Varchar2
512 ,p_object_version_number_o IN Number
513 ) AS
514
515 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Addr_DDF_Del';
516
517 BEGIN
518 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
519 NULL;
520 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
521 END Addr_DDF_Del;
522
523 End PQP_Address_Synchronization;