[Home] [Help]
PACKAGE BODY: APPS.POS_HZ_CONTACT_POINT_PKG
Source
1 PACKAGE BODY pos_hz_contact_point_pkg AS
2 /*$Header: POSHZCPB.pls 120.5 2007/12/28 20:45:39 pbaldota ship $ */
3
4 -- update if exists, create otherwise
5 PROCEDURE create_or_update_tca_phone
6 ( p_owner_table_id IN NUMBER ,
7 p_owner_table_name IN VARCHAR2 ,
8 p_country_code IN VARCHAR2 ,
9 p_area_code IN VARCHAR2 ,
10 p_number IN VARCHAR2 ,
11 p_extension IN VARCHAR2 ,
12 --Start Bug 6620664
13 p_phone_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
14 --End Bug 6620664
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER ,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )
19 IS
20 CURSOR l_cur IS
21 SELECT contact_point_id, object_version_number,
22 phone_number, phone_area_code, phone_extension
23 FROM hz_contact_points
24 WHERE owner_table_name = p_owner_table_name
25 AND owner_table_id = p_owner_table_id
26 AND contact_point_type = 'PHONE'
27 AND phone_line_type = 'GEN'
28 AND primary_flag = 'Y'
29 AND status = 'A' ;
30
31 l_rec l_cur%ROWTYPE;
32 l_found BOOLEAN;
33
34 l_contact_point_id NUMBER;
35 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
36 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
37
38 BEGIN
39
40 OPEN l_cur;
41 FETCH l_cur INTO l_rec;
42 l_found := l_cur%found;
43 CLOSE l_cur;
44
45 IF l_found THEN
46
47 IF p_number is NULL OR trim(p_number) IS NULL THEN
48
49 l_contact_points_rec.status := 'I';
50
51 -- keep the old number when inactivating
52 l_phone_rec.phone_number := l_rec.phone_number;
53
54 ELSE
55 IF (l_rec.phone_area_code IS NULL AND p_area_code IS NULL OR
56 l_rec.phone_area_code IS NOT NULL AND p_area_code IS NOT NULL AND
57 l_rec.phone_area_code = p_area_code
58 ) AND
59 (l_rec.phone_number IS NULL AND p_number IS NULL OR
60 l_rec.phone_number IS NOT NULL AND p_number IS NOT NULL AND
61 l_rec.phone_number = p_number
62 ) AND
63 (l_rec.phone_extension IS NULL AND p_extension IS NULL OR
64 l_rec.phone_extension IS NOT NULL AND p_extension IS NOT NULL AND
65 l_rec.phone_extension = p_extension
66 ) THEN
67 -- the current data is the same as the new data so no change needed
68 x_return_status := fnd_api.g_ret_sts_success;
69 RETURN;
70 END IF;
71
72 l_contact_points_rec.status := 'A';
73 l_phone_rec.phone_number := p_number;
74
75 END IF;
76
77 l_contact_points_rec.contact_point_id := l_rec.contact_point_id;
78
79 l_phone_rec.phone_country_code := p_country_code;
80 l_phone_rec.phone_area_code := p_area_code;
81 l_phone_rec.phone_extension := p_extension;
82 l_phone_rec.phone_line_type := 'GEN';
83
84 --Start for Bug 6620664
85 IF p_phone_object_version_number <> fnd_api.G_NULL_NUM THEN
86
87 l_rec.object_version_number := p_phone_object_version_number;
88 END IF;
89 --End for Bug 6620664
90
91 hz_contact_point_v2pub.update_contact_point
92 (p_init_msg_list => fnd_api.g_false,
93 p_contact_point_rec => l_contact_points_rec,
94 p_phone_rec => l_phone_rec,
95 p_object_version_number => l_rec.object_version_number,
96 x_return_status => x_return_status,
97 x_msg_count => x_msg_count,
98 x_msg_data => x_msg_data
99 );
100
101 pos_log.log_call_result
102 (p_module => 'POSADMB',
103 p_prefix => 'call hz_contact_point_v2pub.update_contact_point',
104 p_return_status => x_return_status,
105 p_msg_count => x_msg_count,
106 p_msg_data => x_msg_data
107 );
108
109 ELSIF p_number IS NOT NULL THEN
110 l_phone_rec.phone_country_code := p_country_code;
111 l_phone_rec.phone_area_code := p_area_code;
112 l_phone_rec.phone_number := p_number;
113 l_phone_rec.phone_extension := p_extension;
114 l_phone_rec.phone_line_type := 'GEN';
115
116 l_contact_points_rec.contact_point_type := 'PHONE';
117 l_contact_points_rec.status := 'A';
118 l_contact_points_rec.owner_table_name := p_owner_table_name;
119 l_contact_points_rec.owner_table_id := p_owner_table_id;
120 l_contact_points_rec.created_by_module := 'POS_SUPPLIER_MGMT';
121 l_contact_points_rec.application_id := 177;
122 l_contact_points_rec.primary_flag := 'Y';
123
124 hz_contact_point_v2pub.create_contact_point
125 ( p_init_msg_list => fnd_api.g_false,
126 p_contact_point_rec => l_contact_points_rec,
127 p_phone_rec => l_phone_rec,
128 x_contact_point_id => l_contact_point_id,
129 x_return_status => x_return_status,
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data
132 );
133
134 pos_log.log_call_result
135 (p_module => 'POSADMB',
136 p_prefix => 'call hz_contact_point_v2pub.create_contact_point',
137 p_return_status => x_return_status,
138 p_msg_count => x_msg_count,
139 p_msg_data => x_msg_data
140 );
141 ELSE
142 x_return_status := fnd_api.g_ret_sts_success;
143 END IF;
144
145 END create_or_update_tca_phone;
146
147 -- update if exists, create otherwise
148 PROCEDURE create_or_update_tca_fax
149 ( p_owner_table_id IN NUMBER ,
150 p_owner_table_name IN VARCHAR2 ,
151 p_country_code IN VARCHAR2 ,
152 p_area_code IN VARCHAR2 ,
153 p_number IN VARCHAR2 ,
154 p_extension IN VARCHAR2 ,
155 --Start Bug 6620664
156 p_fax_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
157 --End Bug 6620664
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER ,
160 x_msg_data OUT NOCOPY VARCHAR2
161 )
162 IS
163 CURSOR l_cur IS
164 SELECT contact_point_id, object_version_number, phone_number,
165 phone_area_code, phone_extension
166 FROM hz_contact_points
167 WHERE owner_table_name = p_owner_table_name
168 AND owner_table_id = p_owner_table_id
169 AND contact_point_type = 'PHONE'
170 AND phone_line_type = 'FAX'
171 AND status = 'A' ;
172
173 l_rec l_cur%ROWTYPE;
174 l_found BOOLEAN;
175
176 l_contact_point_id NUMBER;
177 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
178 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
179
180 BEGIN
181
182 OPEN l_cur;
183 FETCH l_cur INTO l_rec;
184 l_found := l_cur%found;
185 CLOSE l_cur;
186
187 IF l_found THEN
188
189 IF p_number is NULL OR trim(p_number) IS NULL THEN
190
191 l_contact_points_rec.status := 'I';
192
193 -- keep the old number when inactivating
194 l_phone_rec.phone_number := l_rec.phone_number;
195 ELSE
196 IF (l_rec.phone_area_code IS NULL AND p_area_code IS NULL OR
197 l_rec.phone_area_code IS NOT NULL AND p_area_code IS NOT NULL AND
198 l_rec.phone_area_code = p_area_code
199 ) AND
200 (l_rec.phone_number IS NULL AND p_number IS NULL OR
201 l_rec.phone_number IS NOT NULL AND p_number IS NOT NULL AND
202 l_rec.phone_number = p_number
203 ) AND
204 (l_rec.phone_number IS NULL AND p_number IS NULL OR
205 l_rec.phone_number IS NOT NULL AND p_number IS NOT NULL AND
206 l_rec.phone_number = p_number
207 ) THEN
208 -- the current data is the same as the new data so no change needed
209 x_return_status := fnd_api.g_ret_sts_success;
210 RETURN;
211 END IF;
212 l_contact_points_rec.status := 'A';
213 l_phone_rec.phone_number := p_number;
214 END IF;
215
216 l_contact_points_rec.contact_point_id := l_rec.contact_point_id;
217
218 l_phone_rec.phone_country_code := p_country_code;
219 l_phone_rec.phone_area_code := p_area_code;
220 l_phone_rec.phone_extension := p_extension;
221 l_phone_rec.phone_line_type := 'FAX';
222
223 --Start for Bug 6620664
224 IF p_fax_object_version_number <> fnd_api.G_NULL_NUM THEN
225
226 l_rec.object_version_number := p_fax_object_version_number;
227 END IF;
228 --End for Bug 6620664
229
230 hz_contact_point_v2pub.update_contact_point
231 (p_init_msg_list => fnd_api.g_false,
232 p_contact_point_rec => l_contact_points_rec,
233 p_phone_rec => l_phone_rec,
234 p_object_version_number => l_rec.object_version_number,
235 x_return_status => x_return_status,
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data
238 );
239
240 ELSIF p_number IS NOT NULL THEN
241 l_phone_rec.phone_country_code := p_country_code;
242 l_phone_rec.phone_area_code := p_area_code;
243 l_phone_rec.phone_number := p_number;
244 l_phone_rec.phone_extension := p_extension;
245 l_phone_rec.phone_line_type := 'FAX';
246
247 l_contact_points_rec.contact_point_type := 'PHONE';
248 l_contact_points_rec.status := 'A';
249 l_contact_points_rec.owner_table_name := p_owner_table_name;
250 l_contact_points_rec.owner_table_id := p_owner_table_id;
251 l_contact_points_rec.created_by_module := 'POS_SUPPLIER_MGMT';
252 l_contact_points_rec.application_id := 177;
253
254 hz_contact_point_v2pub.create_contact_point
255 ( p_init_msg_list => fnd_api.g_false,
256 p_contact_point_rec => l_contact_points_rec,
257 p_phone_rec => l_phone_rec,
258 x_contact_point_id => l_contact_point_id,
259 x_return_status => x_return_status,
260 x_msg_count => x_msg_count,
261 x_msg_data => x_msg_data
262 );
263 ELSE
264 x_return_status := fnd_api.g_ret_sts_success;
265 END IF;
266
267 END create_or_update_tca_fax;
268
269 -- update if exists, create otherwise
270 PROCEDURE create_or_update_tca_email
271 ( p_owner_table_id IN NUMBER,
272 p_owner_table_name IN VARCHAR2,
273 p_email_address IN VARCHAR2,
274 --Start Bug 6620664
275 p_email_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
276 --End Bug 6620664
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2
280 )
281 IS
282 CURSOR l_cur IS
283 SELECT contact_point_id, object_version_number, email_address
284 FROM hz_contact_points
285 WHERE owner_table_name = p_owner_table_name
286 AND owner_table_id = p_owner_table_id
287 AND contact_point_type = 'EMAIL'
288 AND primary_flag = 'Y'
289 AND status = 'A';
290
291 l_rec l_cur%ROWTYPE;
292 l_found BOOLEAN;
293
294 l_contact_point_id NUMBER;
295 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
296 l_email_rec hz_contact_point_v2pub.email_rec_type;
297
298 BEGIN
299 OPEN l_cur;
300 FETCH l_cur INTO l_rec;
301 l_found := l_cur%found;
302 CLOSE l_cur;
303
304 IF l_found THEN
305 IF p_email_address is NULL or trim(p_email_address) IS NULL THEN
306
307 l_contact_points_rec.status := 'I';
308
309 -- keep the old value when inactivating
310 l_email_rec.email_address := l_rec.email_address;
311 ELSE
312 IF (l_rec.email_address IS NULL AND p_email_address IS NULL OR
313 l_rec.email_address IS NOT NULL AND p_email_address IS NOT NULL AND
314 l_rec.email_address = p_email_address
315 ) THEN
316 -- the current data is the same as the new data so no change needed
317 x_return_status := fnd_api.g_ret_sts_success;
318 RETURN;
319 END IF;
320
321 l_contact_points_rec.status := 'A';
322 l_email_rec.email_address := p_email_address;
323 END IF;
324
325 l_contact_points_rec.contact_point_id := l_rec.contact_point_id;
326
327 l_email_rec.email_format := 'MAILTEXT';
328
329 --Start for Bug 6620664
330 IF p_email_object_version_number <> fnd_api.G_NULL_NUM THEN
331
332 l_rec.object_version_number := p_email_object_version_number;
333 END IF;
334 --End for Bug 6620664
335
336 hz_contact_point_v2pub.update_contact_point
337 (p_init_msg_list => fnd_api.g_false,
338 p_contact_point_rec => l_contact_points_rec,
339 p_email_rec => l_email_rec,
340 p_object_version_number => l_rec.object_version_number,
341 x_return_status => x_return_status,
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data
344 );
345
346 ELSIF p_email_address is NOT NULL THEN
347 l_email_rec.email_format := 'MAILTEXT';
348 l_email_rec.email_address := p_email_address;
349
350 l_contact_points_rec.contact_point_type := 'EMAIL';
351 l_contact_points_rec.status := 'A';
352 l_contact_points_rec.owner_table_name := p_owner_table_name;
353 l_contact_points_rec.owner_table_id := p_owner_table_id;
354 l_contact_points_rec.created_by_module := 'POS_SUPPLIER_MGMT';
355 l_contact_points_rec.application_id := 177;
356
357 hz_contact_point_v2pub.create_contact_point
358 ( p_init_msg_list => fnd_api.g_false,
359 p_contact_point_rec => l_contact_points_rec,
360 p_email_rec => l_email_rec,
361 x_contact_point_id => l_contact_point_id,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data
365 );
366 ELSE
367 x_return_status := fnd_api.g_ret_sts_success;
368 END IF;
369
370 END create_or_update_tca_email;
371
372 PROCEDURE update_party_phone
373 ( p_party_id IN NUMBER
374 , p_country_code IN VARCHAR2
375 , p_area_code IN VARCHAR2
376 , p_number IN VARCHAR2
377 , p_extension IN VARCHAR2
378 --Start Bug 6620664
379 , p_phone_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM
380 --End Bug 6620664
381 , x_return_status OUT NOCOPY VARCHAR2
382 , x_msg_count OUT nocopy VARCHAR2
383 , x_msg_data OUT NOCOPY VARCHAR2
384 )
385 IS
386 BEGIN
387 create_or_update_tca_phone
388 (p_owner_table_id => p_party_id,
389 p_owner_table_name => 'HZ_PARTIES',
390 p_country_code => p_country_code,
391 p_area_code => nvl(p_area_code, fnd_api.g_miss_char),
392 p_number => p_number,
393 p_extension => nvl(p_extension,fnd_api.g_miss_char),
394 --Start Bug 6620664
395 p_phone_object_version_number => p_phone_object_version_number,
396 --End Bug 6620664
397 x_return_status => x_return_status,
398 x_msg_count => x_msg_count,
399 x_msg_data => x_msg_data
400 );
401 END update_party_phone;
402
406 , p_area_code IN VARCHAR2
403 PROCEDURE update_party_fax
404 ( p_party_id IN NUMBER
405 , p_country_code IN VARCHAR2
407 , p_number IN VARCHAR2
408 , p_extension IN VARCHAR2
409 --Start Bug 6620664
410 , p_fax_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM
411 --End Bug 6620664
412 , x_return_status OUT NOCOPY VARCHAR2
413 , x_msg_count OUT nocopy VARCHAR2
414 , x_msg_data OUT NOCOPY VARCHAR2
415 )
416 IS
417 BEGIN
418 create_or_update_tca_fax
419 (p_owner_table_id => p_party_id,
420 p_owner_table_name => 'HZ_PARTIES',
421 p_country_code => p_country_code,
422 p_area_code => p_area_code,
423 p_number => p_number,
424 p_extension => p_extension,
425 --Start Bug 6620664
426 p_fax_object_version_number => p_fax_object_version_number,
427 --End Bug 6620664
428 x_return_status => x_return_status,
429 x_msg_count => x_msg_count,
430 x_msg_data => x_msg_data
431 );
432 END update_party_fax;
433
434 PROCEDURE update_party_email
435 ( p_party_id IN NUMBER
436 , p_email IN VARCHAR2
437 --Start Bug 6620664
438 , p_email_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM
439 --End Bug 6620664
440 , x_return_status OUT NOCOPY VARCHAR2
441 , x_msg_count OUT nocopy VARCHAR2
442 , x_msg_data OUT NOCOPY VARCHAR2
443 )
444 IS
445 BEGIN
446 create_or_update_tca_email
447 (p_owner_table_id => p_party_id,
448 p_owner_table_name => 'HZ_PARTIES',
449 p_email_address => p_email,
450 --Start Bug 6620664
451 p_email_object_version_number => p_email_object_version_number,
452 --End Bug 6620664
453 x_return_status => x_return_status,
454 x_msg_count => x_msg_count,
455 x_msg_data => x_msg_data
456 );
457 END update_party_email;
458
459 PROCEDURE update_party_site_phone
460 ( p_party_site_id IN NUMBER
461 , p_country_code IN VARCHAR2
462 , p_area_code IN VARCHAR2
463 , p_number IN VARCHAR2
464 , p_extension IN VARCHAR2
465 , x_return_status OUT NOCOPY VARCHAR2
466 , x_msg_count OUT nocopy VARCHAR2
467 , x_msg_data OUT NOCOPY VARCHAR2
468 ) IS
469 BEGIN
470 create_or_update_tca_phone
471 (p_owner_table_id => p_party_site_id,
472 p_owner_table_name => 'HZ_PARTY_SITES',
473 p_country_code => p_country_code,
474 p_area_code => p_area_code,
475 p_number => p_number,
476 p_extension => p_extension,
477 x_return_status => x_return_status,
478 x_msg_count => x_msg_count,
479 x_msg_data => x_msg_data
480 );
481 END update_party_site_phone;
482
483 PROCEDURE update_party_site_fax
484 ( p_party_site_id IN NUMBER
485 , p_country_code IN VARCHAR2
486 , p_area_code IN VARCHAR2
487 , p_number IN VARCHAR2
488 , p_extension IN VARCHAR2
489 , x_return_status OUT NOCOPY VARCHAR2
490 , x_msg_count OUT nocopy VARCHAR2
491 , x_msg_data OUT NOCOPY VARCHAR2
492 ) IS
493 BEGIN
494 create_or_update_tca_fax
495 (p_owner_table_id => p_party_site_id,
496 p_owner_table_name => 'HZ_PARTY_SITES',
497 p_country_code => p_country_code,
498 p_area_code => p_area_code,
499 p_number => p_number,
500 p_extension => p_extension,
501 x_return_status => x_return_status,
502 x_msg_count => x_msg_count,
503 x_msg_data => x_msg_data
504 );
505 END update_party_site_fax;
506
507 PROCEDURE update_party_site_email
508 ( p_party_site_id IN NUMBER
509 , p_email IN VARCHAR2
510 , x_return_status OUT NOCOPY VARCHAR2
511 , x_msg_count OUT nocopy VARCHAR2
512 , x_msg_data OUT NOCOPY VARCHAR2
513 ) IS
514 BEGIN
515 create_or_update_tca_email
516 (p_owner_table_id => p_party_site_id,
517 p_owner_table_name => 'HZ_PARTY_SITES',
518 p_email_address => p_email,
519 x_return_status => x_return_status,
520 x_msg_count => x_msg_count,
521 x_msg_data => x_msg_data
522 );
523 END update_party_site_email;
524
525 END pos_hz_contact_point_pkg;