[Home] [Help]
PACKAGE BODY: APPS.ARH_PHON_PKG
Source
1 PACKAGE BODY arh_phon_pkg as
2 /* $Header: ARHPHONB.pls 120.2 2005/06/16 21:14:19 jhuang ship $*/
3 --
4 -- PROCEDURE
5 -- get_level
6 --
7 -- DESCRIPTION
8 -- This procedure detemermins which level the phone
9 -- is connected to cust|addr|cont
10 --
11 -- SCOPE - PROVATE
12 --
13 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
14 --
15 -- ARGUMENTS : IN:
16 -- p_customer_id
17 -- p_address_id
18 -- p_contact_id
19 -- p_type
20 -- p_id
21 -- OUT:
22 -- None
23 --
24 -- RETURNS : NONE
25 --
26 -- NOTES
27 --
28 -- MODIFICATION HISTORY - Created by Kevin Hudson
29 --
30 --
31
32 FUNCTION INIT_SWITCH
33 ( p_date IN DATE,
34 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
35 RETURN DATE
36 IS
37 res_date date;
38 BEGIN
39 IF p_switch = 'NULL_GMISS' THEN
40 IF p_date IS NULL THEN
41 res_date := FND_API.G_MISS_DATE;
42 ELSE
43 res_date := p_date;
44 END IF;
45 ELSIF p_switch = 'GMISS_NULL' THEN
46 IF p_date = FND_API.G_MISS_DATE THEN
47 res_date := NULL;
48 ELSE
49 res_date := p_date;
50 END IF;
51 ELSE
52 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
53 END IF;
54 RETURN res_date;
55 END;
56
57 FUNCTION INIT_SWITCH
58 ( p_char IN VARCHAR2,
59 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
60 RETURN VARCHAR2
61 IS
62 res_char varchar2(2000);
63 BEGIN
64 IF p_switch = 'NULL_GMISS' THEN
65 IF p_char IS NULL THEN
66 return FND_API.G_MISS_CHAR;
67 ELSE
68 return p_char;
69 END IF;
70 ELSIF p_switch = 'GMISS_NULL' THEN
71 IF p_char = FND_API.G_MISS_CHAR THEN
72 return NULL;
73 ELSE
74 return p_char;
75 END IF;
76 ELSE
77 return ('INCORRECT_P_SWITCH');
78 END IF;
79 END;
80
81 FUNCTION INIT_SWITCH
82 ( p_num IN NUMBER,
83 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
84 RETURN NUMBER
85 IS
86 BEGIN
87 IF p_switch = 'NULL_GMISS' THEN
88 IF p_num IS NULL THEN
89 return FND_API.G_MISS_NUM;
90 ELSE
91 return p_num;
92 END IF;
93 ELSIF p_switch = 'GMISS_NULL' THEN
94 IF p_num = FND_API.G_MISS_NUM THEN
95 return NULL;
96 ELSE
97 return p_num;
98 END IF;
99 ELSE
100 return ('9999999999');
101 END IF;
102 END;
103
104 PROCEDURE object_version_select
105 (p_table_name IN VARCHAR2,
106 p_col_id IN VARCHAR2,
107 x_rowid IN OUT NOCOPY ROWID,
108 x_object_version_number IN OUT NOCOPY NUMBER,
109 x_last_update_date IN OUT NOCOPY DATE,
110 x_id_value IN OUT NOCOPY NUMBER,
111 x_return_status IN OUT NOCOPY VARCHAR2,
112 x_msg_count IN OUT NOCOPY NUMBER,
113 x_msg_data IN OUT NOCOPY VARCHAR2 )
114 IS
115 CURSOR cu_contact_point_version IS
116 SELECT ROWID,
117 OBJECT_VERSION_NUMBER,
118 LAST_UPDATE_DATE,
119 NULL
120 FROM HZ_CONTACT_POINTS
121 WHERE CONTACT_POINT_ID = p_col_id;
122
123 l_last_update_date DATE;
124
125 BEGIN
126 IF p_table_name = 'HZ_CONTACT_POINTS' THEN
127 OPEN cu_contact_point_version;
128 FETCH cu_contact_point_version INTO
129 x_rowid ,
130 x_object_version_number,
131 l_last_update_date ,
132 x_id_value;
133 CLOSE cu_contact_point_version;
134 END IF;
135
136 IF x_rowid IS NULL THEN
137 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
138 FND_MESSAGE.SET_TOKEN('RECORD',p_table_name);
139 FND_MESSAGE.SET_TOKEN('ID',p_col_id);
140 FND_MSG_PUB.ADD;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 ELSE
143 IF TO_CHAR(x_last_update_date,'DD-MON-YYYY HH:MI:SS') <>
144 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
145 THEN
146 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
147 FND_MESSAGE.SET_TOKEN('TABLE', p_table_name);
148 FND_MSG_PUB.ADD;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 END IF;
151 END IF;
152
153 END;
154
155
156 procedure get_level( p_customer_id in number,
157 p_address_id in number,
158 p_contact_id in number,
159 p_type out NOCOPY varchar2,
160 p_id out NOCOPY number ) is
161 begin
162 if ( p_contact_id is not null ) then
163 p_type := 'CONT';
164 p_id := p_contact_id;
165 elsif (p_address_id is not null ) then
166 p_type := 'ADDR';
167 p_id := p_address_id;
168 else
169 p_type := 'CUST';
170 p_id := p_customer_id;
171 end if;
172 --
173 end get_level;
174 --
175 --
176 -- PROCEDURE
177 --
178 -- check_primary
179 --
180 -- DESCRIPTION
181 -- This procedure ensure that a cust|addr|cont only
182 -- has one primary telephone.
183 --
184 -- SCOPE -
185 -- PUBLIC
186 --
187 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
188 --
189 -- ARGUMENTS : IN: p_phone_id
190 -- p_type (CUST|ADDR|CONT)
191 -- p_id
192 -- OUT:
193 -- None
194 --
195 -- RETURNS : NONE
196 --
197 -- NOTES
198 --
199 -- MODIFICATION HISTORY - Created by Kevin Hudson
200 --
201 --
202 PROCEDURE check_primary(p_phone_id in number,
203 p_type in varchar2,
204 p_id in number ) is
205 --
206 --
207 primary_count number;
208 --
209 BEGIN
210 --
211 if (p_type = 'CUST') then
212
213 select count(1)
214 into primary_count
215 from hz_contact_points cont_point,
216 hz_cust_account_roles car
217 where car.party_id = cont_point.owner_table_id
218 and cont_point.owner_table_name = 'HZ_PARTIES'
219 and cont_point.contact_point_type not
220 in ('EDI','EMAIL','WEB')
221 and car.cust_account_id = p_id
222 and car.cust_acct_site_id is null
223 and car.cust_account_role_id is null
224 and cont_point.primary_flag = 'Y'
225 and ( (p_phone_id is null)
226 or cont_point.contact_point_id <> p_phone_id );
227 --
228 if ( primary_count >= 1 ) then
229 fnd_message.set_name('AR','AR_CUST_ONE_PRIMARY_PHONE');
230 app_exception.raise_exception;
231 end if;
232 --
233 elsif (p_type = 'ADDR' ) then
234 --
235 select count(1)
236 into primary_count
237 from hz_contact_points cont_point,
238 hz_cust_account_roles car
239 where car.party_id = cont_point.owner_table_id
240 and cont_point.owner_table_name = 'HZ_PARTIES'
241 and cont_point.contact_point_type not
242 in ('EDI','EMAIL','WEB')
243 and car.cust_acct_site_id = p_id
244 and car.cust_account_role_id is null
245 and cont_point.primary_flag = 'Y'
246 and ( (p_phone_id is null)
247 or cont_point.contact_point_id <> p_phone_id );
248 --
249 if ( primary_count >= 1 ) then
250 fnd_message.set_name('AR','AR_CUST_ADDR_ONE_PRIMARY_PHONE');
251 app_exception.raise_exception;
252 end if;
253 elsif (p_type = 'CONT' ) then
254 --
255 select count(1)
256 into primary_count
257 from hz_contact_points cont_point,
258 hz_cust_account_roles car
259 where car.party_id = cont_point.owner_table_id
260 and cont_point.owner_table_name = 'HZ_PARTIES'
261 and cont_point.contact_point_type not
262 in ('EDI','EMAIL','WEB')
263 and car.cust_account_role_id = p_id
264 and cont_point.primary_flag = 'Y'
265 and ( (p_phone_id is null)
266 or cont_point.contact_point_id <> p_phone_id );
267 --
268 if ( primary_count >= 1 ) then
269 fnd_message.set_name('AR','AR_CUST_CONT_ONE_PRIMARY_PHONE');
270 app_exception.raise_exception;
271 end if;
272 else
273 app_exception.invalid_argument('arp_phones_pkg.check_primary','p_type',p_type);
274
275 end if;
276 --
277 END check_primary;
278 --
279 --
280 --
281 PROCEDURE Insert_Row(
282 X_Phone_Id IN OUT NOCOPY NUMBER,
283 X_Last_Update_Date DATE,
284 X_Last_Updated_By NUMBER,
285 X_Creation_Date DATE,
286 X_Created_By NUMBER,
287 X_Phone_Number VARCHAR2,
288 X_Status VARCHAR2,
289 X_Phone_Type VARCHAR2,
290 X_Last_Update_Login NUMBER,
291 X_Customer_Id NUMBER,
292 X_Address_Id NUMBER,
293 X_Contact_Id NUMBER,
294 X_Country_Code VARCHAR2,
295 X_Area_Code VARCHAR2,
296 X_Extension VARCHAR2,
297 X_Primary_Flag VARCHAR2,
298 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
299 X_Attribute_Category VARCHAR2,
300 X_Attribute1 VARCHAR2,
301 X_Attribute2 VARCHAR2,
302 X_Attribute3 VARCHAR2,
303 X_Attribute4 VARCHAR2,
304 X_Attribute5 VARCHAR2,
305 X_Attribute6 VARCHAR2,
306 X_Attribute7 VARCHAR2,
307 X_Attribute8 VARCHAR2,
308 X_Attribute9 VARCHAR2,
309 X_Attribute10 VARCHAR2,
310 X_Attribute11 VARCHAR2,
311 X_Attribute12 VARCHAR2,
312 X_Attribute13 VARCHAR2,
313 X_Attribute14 VARCHAR2,
314 X_Attribute15 VARCHAR2,
315 X_Attribute16 VARCHAR2,
316 X_Attribute17 VARCHAR2,
317 X_Attribute18 VARCHAR2,
318 X_Attribute19 VARCHAR2,
319 X_Attribute20 VARCHAR2,
320 x_party_id NUMBER,
321 x_party_site_id NUMBER,
322 x_primary_by_purpose VARCHAR2,
323 x_contact_point_purpose VARCHAR2,
324 x_email_format VARCHAR2,
325 x_email_address VARCHAR2,
326 x_url VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 x_return_status OUT NOCOPY VARCHAR2
330 ) IS
331 --
332 l_type varchar2(4);
333 l_id number(15);
334 --
335 -- email_rec hz_contact_point_pub.email_rec_type;
336 -- edi_rec hz_contact_point_pub.edi_rec_type;
337 -- phone_rec hz_contact_point_pub.phone_rec_type;
338 -- telex_rec hz_contact_point_pub.telex_rec_type;
339 -- web_rec hz_contact_point_pub.web_rec_type;
340 -- cpoint_rec hz_contact_point_pub.contact_points_rec_type;
341 -- cust_point_rec hz_customer_accounts_pub.cust_contact_pt_rec_type;
342 --
343 email_rec hz_contact_point_v2pub.email_rec_type;
344 edi_rec hz_contact_point_v2pub.edi_rec_type;
345 phone_rec hz_contact_point_v2pub.phone_rec_type;
346 telex_rec hz_contact_point_v2pub.telex_rec_type;
347 web_rec hz_contact_point_v2pub.web_rec_type;
348 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
349 --
350 x_cust_acct_profile_amt_id NUMBER;
351 x_owner_table VARCHAR2(30);
352 x_owner_table_id NUMBER;
353 tmp_var VARCHAR2(2000);
354 i NUMBER;
355 tmp_var1 VARCHAR2(2000);
356 x_cust_contact_point_id NUMBER;
357 l_count NUMBER;
358 --
359 BEGIN
360 --
361
362 -- if (x_primary_flag = 'Y') then
363 --
364 --
365 get_level(p_customer_id => x_customer_id,
366 p_address_id => x_address_id,
367 p_contact_id => x_contact_id,
368 p_type => l_type,
369 p_id => l_id);
370 --
371 --
372 if l_type = 'CUST' then
373 x_owner_table := 'HZ_PARTIES';
374 x_owner_table_id := x_party_id;
375 end if;
376
377 if l_type = 'ADDR' then
378 x_owner_table := 'HZ_PARTY_SITES';
379 x_owner_table_id := x_party_site_id;
380 end if;
381
382 if l_type = 'CONT' then
383 x_owner_table := 'HZ_PARTIES';
384 x_owner_table_id := x_party_id;
385 end if;
386
387 if (x_primary_flag = 'Y') then
388 check_primary(p_phone_id => x_phone_id,
389 p_type => l_type,
390 p_id => l_id );
391 --
392 --
393 end if;
394 --
395
396 l_count := 1;
397
398 while l_count > 0 loop
399 SELECT hz_contact_points_s.nextval
400 INTO x_phone_id
401 FROM dual;
402
403 select count(*) into l_count from hz_contact_points
404 where contact_point_id = x_phone_id ;
405
406 END LOOP;
407 --
408 SELECT hz_cust_contact_points_s.nextval
409 into x_cust_contact_point_id
410 from dual;
411 --
412 IF ( x_orig_system_reference is null)
413 THEN
414 x_orig_system_reference := x_phone_id;
415 END IF;
416 --
417 cpoint_rec.contact_point_id := x_phone_Id;
418 cpoint_rec.contact_point_type := X_Phone_Type;
419 cpoint_rec.status := x_status;
420 cpoint_rec.owner_table_name := x_owner_table;
421 cpoint_rec.owner_table_id := x_owner_table_id;
422 cpoint_rec.primary_flag := x_primary_flag;
423 cpoint_rec.orig_system_reference := x_orig_system_reference;
424 cpoint_rec.attribute_category := x_attribute_category;
425 cpoint_rec.attribute1 := x_attribute1;
426 cpoint_rec.attribute2 := x_attribute2;
427 cpoint_rec.attribute3 := x_attribute3;
428 cpoint_rec.attribute4 := x_attribute4;
429 cpoint_rec.attribute5 := x_attribute5;
430 cpoint_rec.attribute6 := x_attribute6;
431 cpoint_rec.attribute7 := x_attribute7;
432 cpoint_rec.attribute8 := x_attribute8;
433 cpoint_rec.attribute9 := x_attribute9;
434 cpoint_rec.attribute10 := x_attribute10;
435 cpoint_rec.attribute11 := x_attribute11;
436 cpoint_rec.attribute12 := x_attribute12;
437 cpoint_rec.attribute13 := x_attribute13;
438 cpoint_rec.attribute14 := x_attribute14;
439 cpoint_rec.attribute15 := x_attribute15;
440 cpoint_rec.attribute16 := x_attribute16;
441 cpoint_rec.attribute17 := x_attribute17;
442 cpoint_rec.attribute18 := x_attribute18;
443 cpoint_rec.attribute19 := x_attribute19;
444 cpoint_rec.attribute20 := x_attribute20;
445 cpoint_rec.created_by_module := 'TCA_FORM_WRAPPER';
446 cpoint_rec.contact_point_purpose := x_contact_point_purpose;
447 cpoint_rec.primary_by_purpose := x_primary_by_purpose;
448 IF x_phone_type = 'TLX' THEN
449 telex_rec.telex_number := x_phone_number;
450 END IF;
451
452 IF x_phone_type = 'TLX' AND x_area_code IS NOT NULL THEN
453 telex_rec.telex_number := x_area_code ||'-'||x_phone_number;
454 END IF;
455
456 IF x_phone_type NOT IN ( 'TLX','EMAIL','WEB') THEN
457 phone_rec.phone_line_type := x_phone_type;
458 cpoint_rec.contact_point_type := 'PHONE';
459 phone_rec.phone_number := x_phone_number;
460 phone_rec.phone_country_code := x_country_code;
461 phone_rec.phone_area_code := x_area_code;
462 phone_rec.phone_extension := x_extension;
463 END IF;
464 IF x_phone_type = 'WEB' THEN
465 web_rec.url := x_email_address;
466 cpoint_rec.contact_point_type := 'WEB';
467 web_rec.web_type := 'WEB';
468 END IF;
469 IF x_phone_type = 'EMAIL' THEN
470 email_rec.email_format := x_email_format;
471 email_rec.email_address := x_email_address;
472 cpoint_rec.contact_point_type := x_phone_type;
473 END IF;
474 -- call V2 API.
475 HZ_CONTACT_POINT_V2PUB.create_contact_point (
476 p_contact_point_rec => cpoint_rec,
477 p_edi_rec => edi_rec,
478 p_email_rec => email_rec,
479 p_phone_rec => phone_rec,
480 p_telex_rec => telex_rec,
481 p_web_rec => web_rec,
482 x_contact_point_id => x_phone_id,
483 x_return_status => x_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data
486 );
487
488
489 IF x_msg_count > 1 THEN
490 FOR i IN 1..x_msg_count LOOP
491 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
492 tmp_var1 := tmp_var1 || ' '|| tmp_var;
493 END LOOP;
494 x_msg_data := tmp_var1;
495 END IF;
496
497 IF x_return_status <> 'S' THEN
498 return;
499 END IF;
500
501 END Insert_Row;
502
503 PROCEDURE Update_Row(
504 X_Phone_Id NUMBER,
505 X_Last_Update_Date IN OUT NOCOPY DATE,
506 X_Last_Updated_By NUMBER,
507 X_Phone_Number VARCHAR2,
508 X_Status VARCHAR2,
509 X_Phone_Type VARCHAR2,
510 X_Last_Update_Login NUMBER,
511 X_Customer_Id NUMBER,
512 X_Address_Id NUMBER,
513 X_Contact_Id NUMBER,
514 X_Country_code VARCHAR2,
515 X_Area_Code VARCHAR2,
516 X_Extension VARCHAR2,
517 X_Primary_Flag VARCHAR2,
518 X_Attribute_Category VARCHAR2,
519 X_Attribute1 VARCHAR2,
520 X_Attribute2 VARCHAR2,
521 X_Attribute3 VARCHAR2,
522 X_Attribute4 VARCHAR2,
523 X_Attribute5 VARCHAR2,
524 X_Attribute6 VARCHAR2,
525 X_Attribute7 VARCHAR2,
526 X_Attribute8 VARCHAR2,
527 X_Attribute9 VARCHAR2,
528 X_Attribute10 VARCHAR2,
529 X_Attribute11 VARCHAR2,
530 X_Attribute12 VARCHAR2,
531 X_Attribute13 VARCHAR2,
532 X_Attribute14 VARCHAR2,
533 X_Attribute15 VARCHAR2,
534 X_Attribute16 VARCHAR2,
535 X_Attribute17 VARCHAR2,
536 X_Attribute18 VARCHAR2,
537 X_Attribute19 VARCHAR2,
538 X_Attribute20 VARCHAR2,
539 x_cust_contact_point_id NUMBER,
540 x_primary_by_purpose VARCHAR2,
541 x_contact_point_purpose VARCHAR2,
542 x_email_format VARCHAR2,
543 x_email_address VARCHAR2,
544 x_url VARCHAR2,
545 x_msg_count OUT NOCOPY NUMBER,
546 x_msg_data OUT NOCOPY VARCHAR2,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_object_version IN OUT NOCOPY NUMBER
549
550 ) IS
551
552 l_type VARCHAR2(4);
553 l_id NUMBER(15);
554 email_rec hz_contact_point_v2pub.email_rec_type;
555 edi_rec hz_contact_point_v2pub.edi_rec_type;
556 phone_rec hz_contact_point_v2pub.phone_rec_type;
557 telex_rec hz_contact_point_v2pub.telex_rec_type;
558 web_rec hz_contact_point_v2pub.web_rec_type;
559 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
560 tmp_var VARCHAR2(2000);
561 i NUMBER;
562 tmp_var1 VARCHAR2(2000);
563 l_update_date DATE;
564 l_rowid ROWID;
565 l_object_version NUMBER;
566 l_dummy NUMBER;
567
568 BEGIN
569 --
570 if (x_primary_flag = 'Y' ) then
571 get_level(x_customer_id,x_address_id,x_contact_id,l_type,l_id);
572 --
573 check_primary(x_phone_id,l_type,l_id);
574 --
575 end if;
576 --
577 cpoint_rec.contact_point_id := x_phone_Id;
578 cpoint_rec.status := INIT_SWITCH(x_status);
579 cpoint_rec.primary_flag := INIT_SWITCH(x_primary_flag);
580 cpoint_rec.attribute_category := INIT_SWITCH(x_attribute_category);
581 cpoint_rec.attribute1 := INIT_SWITCH(x_attribute1);
582 cpoint_rec.attribute2 := INIT_SWITCH(x_attribute2);
583 cpoint_rec.attribute3 := INIT_SWITCH(x_attribute3);
584 cpoint_rec.attribute4 := INIT_SWITCH(x_attribute4);
585 cpoint_rec.attribute5 := INIT_SWITCH(x_attribute5);
586 cpoint_rec.attribute6 := INIT_SWITCH(x_attribute6);
587 cpoint_rec.attribute7 := INIT_SWITCH(x_attribute7);
588 cpoint_rec.attribute8 := INIT_SWITCH(x_attribute8);
589 cpoint_rec.attribute9 := INIT_SWITCH(x_attribute9);
590 cpoint_rec.attribute10 := INIT_SWITCH(x_attribute10);
591 cpoint_rec.attribute11 := INIT_SWITCH(x_attribute11);
592 cpoint_rec.attribute12 := INIT_SWITCH(x_attribute12);
593 cpoint_rec.attribute13 := INIT_SWITCH(x_attribute13);
594 cpoint_rec.attribute14 := INIT_SWITCH(x_attribute14);
595 cpoint_rec.attribute15 := INIT_SWITCH(x_attribute15);
596 cpoint_rec.attribute16 := INIT_SWITCH(x_attribute16);
597 cpoint_rec.attribute17 := INIT_SWITCH(x_attribute17);
598 cpoint_rec.attribute18 := INIT_SWITCH(x_attribute18);
599 cpoint_rec.attribute19 := INIT_SWITCH(x_attribute19);
600 cpoint_rec.attribute20 := INIT_SWITCH(x_attribute20);
601 cpoint_rec.contact_point_purpose := INIT_SWITCH(x_contact_point_purpose);
602 cpoint_rec.primary_by_purpose := INIT_SWITCH(x_primary_by_purpose);
603 IF x_phone_type = 'TLX' THEN
604 telex_rec.telex_number := INIT_SWITCH(x_phone_number);
605 END IF;
606
607 IF x_phone_type = 'TLX' AND x_area_code IS NOT NULL THEN
608 telex_rec.telex_number := x_area_code ||'-'||x_phone_number;
609 ELSE
610 telex_rec.telex_number := INIT_SWITCH(telex_rec.telex_number);
611 END IF;
612
613 IF x_phone_type not in ( 'TLX') THEN
614 phone_rec.phone_line_type := INIT_SWITCH(x_phone_type);
615 --BUG Fix 1870911,Contact_point_type is non-Updateable
616 -- cpoint_rec.contact_point_type := 'PHONE';
617 phone_rec.phone_number := INIT_SWITCH(x_phone_number);
618 phone_rec.phone_country_code:= INIT_SWITCH(x_country_code);
619 phone_rec.phone_area_code := INIT_SWITCH(x_area_code);
620 phone_rec.phone_extension := INIT_SWITCH(x_extension);
621 END IF;
622 IF x_phone_type = 'WEB' THEN
623 web_rec.url := INIT_SWITCH(x_email_address);
624 END IF;
625 IF x_phone_type = 'EMAIL' THEN
626 email_rec.email_format := INIT_SWITCH(x_email_format);
627 email_rec.email_address := INIT_SWITCH(x_email_address);
628 END IF;
629 l_object_version := x_object_version;
630 IF l_object_version = -1 THEN
631 object_version_select
632 (p_table_name => 'HZ_CONTACT_POINTS',
633 p_col_id => x_phone_Id,
634 x_rowid => l_rowid,
635 x_object_version_number => l_object_version,
636 x_last_update_date => l_update_date,
637 x_id_value => l_dummy,
638 x_return_status => x_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data );
641 END IF;
642
643 IF x_msg_count > 1 THEN
644 FOR i IN 1..x_msg_count LOOP
645 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
646 tmp_var1 := tmp_var1 || ' '|| tmp_var;
647 END LOOP;
648 x_msg_data := tmp_var1;
649 END IF;
650
651 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
652 return;
653 END IF;
654
655
656 HZ_CONTACT_POINT_V2PUB.update_contact_point (
657 p_contact_point_rec => cpoint_rec,
658 p_edi_rec => edi_rec,
659 p_email_rec => email_rec,
660 p_phone_rec => phone_rec,
661 p_telex_rec => telex_rec,
662 p_web_rec => web_rec,
663 p_object_version_number => l_object_version,
664 x_return_status => x_return_status,
665 x_msg_count => x_msg_count,
666 x_msg_data => x_msg_data
667 );
668
669 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
670 select last_update_date,
671 object_version_number
672 into X_Last_Update_Date,
673 x_object_version
674 from hz_contact_points
675 where contact_point_id = X_Phone_Id;
676 END IF;
677
678 IF x_msg_count > 1 THEN
679 FOR i IN 1..x_msg_count LOOP
680 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
681 tmp_var1 := tmp_var1 || ' '|| tmp_var;
682 END LOOP;
683 x_msg_data := tmp_var1;
684 END IF;
685
686 END Update_Row;
687
688 --
689 -- Overload method for V2 API uptake
690 --
691 PROCEDURE Update_Row(
692 X_Phone_Id NUMBER,
693 X_Last_Update_Date IN OUT NOCOPY DATE,
694 X_Last_Updated_By NUMBER,
695 X_Phone_Number VARCHAR2,
696 X_Status VARCHAR2,
697 X_Phone_Type VARCHAR2,
698 X_Last_Update_Login NUMBER,
699 X_Customer_Id NUMBER,
700 X_Address_Id NUMBER,
701 X_Contact_Id NUMBER,
702 X_Country_code VARCHAR2,
703 X_Area_Code VARCHAR2,
704 X_Extension VARCHAR2,
705 X_Primary_Flag VARCHAR2,
706 X_Attribute_Category VARCHAR2,
707 X_Attribute1 VARCHAR2,
708 X_Attribute2 VARCHAR2,
709 X_Attribute3 VARCHAR2,
710 X_Attribute4 VARCHAR2,
711 X_Attribute5 VARCHAR2,
712 X_Attribute6 VARCHAR2,
713 X_Attribute7 VARCHAR2,
714 X_Attribute8 VARCHAR2,
715 X_Attribute9 VARCHAR2,
716 X_Attribute10 VARCHAR2,
717 X_Attribute11 VARCHAR2,
718 X_Attribute12 VARCHAR2,
719 X_Attribute13 VARCHAR2,
720 X_Attribute14 VARCHAR2,
721 X_Attribute15 VARCHAR2,
722 X_Attribute16 VARCHAR2,
723 X_Attribute17 VARCHAR2,
724 X_Attribute18 VARCHAR2,
725 X_Attribute19 VARCHAR2,
726 X_Attribute20 VARCHAR2,
727 x_cust_contact_point_id NUMBER,
728 x_primary_by_purpose VARCHAR2,
729 x_contact_point_purpose VARCHAR2,
730 x_email_format VARCHAR2,
731 x_email_address VARCHAR2,
732 x_url VARCHAR2,
733 x_msg_count OUT NOCOPY NUMBER,
734 x_msg_data OUT NOCOPY VARCHAR2,
735 x_return_status OUT NOCOPY VARCHAR2
736 )
737 IS
738 l_object_version NUMBER := -1;
739 BEGIN
740 Update_Row( X_Phone_Id ,
741 X_Last_Update_Date,
742 X_Last_Updated_By,
743 X_Phone_Number ,
744 X_Status ,
745 X_Phone_Type ,
746 X_Last_Update_Login,
747 X_Customer_Id ,
748 X_Address_Id ,
749 X_Contact_Id ,
750 X_Country_code ,
751 X_Area_Code ,
752 X_Extension ,
753 X_Primary_Flag ,
754 X_Attribute_Category,
755 X_Attribute1 ,
756 X_Attribute2 ,
757 X_Attribute3 ,
758 X_Attribute4 ,
759 X_Attribute5 ,
760 X_Attribute6 ,
761 X_Attribute7 ,
762 X_Attribute8 ,
763 X_Attribute9 ,
764 X_Attribute10 ,
765 X_Attribute11 ,
766 X_Attribute12 ,
767 X_Attribute13 ,
768 X_Attribute14 ,
769 X_Attribute15 ,
770 X_Attribute16 ,
771 X_Attribute17 ,
772 X_Attribute18 ,
773 X_Attribute19 ,
774 X_Attribute20 ,
775 x_cust_contact_point_id,
776 x_primary_by_purpose,
777 x_contact_point_purpose,
778 x_email_format,
779 x_email_address,
780 x_url,
781 x_msg_count ,
782 x_msg_data ,
783 x_return_status ,
784 l_object_version );
785
786 END;
787
788 -- Overload procedure for collection workbench
789 -- Fix bug 1694959
790 PROCEDURE Insert_Row(
791 X_Phone_Id IN OUT NOCOPY NUMBER,
792 X_Last_Update_Date DATE,
793 X_Last_Updated_By NUMBER,
794 X_Creation_Date DATE,
795 X_Created_By NUMBER,
796 X_Phone_Number VARCHAR2,
797 X_Status VARCHAR2,
798 X_Phone_Type VARCHAR2,
799 X_Last_Update_Login NUMBER,
800 X_Customer_Id NUMBER,
801 X_Address_Id NUMBER,
802 X_Contact_Id NUMBER,
803 X_Area_Code VARCHAR2,
804 X_Extension VARCHAR2,
805 X_Primary_Flag VARCHAR2,
806 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
807 X_Attribute_Category VARCHAR2,
808 X_Attribute1 VARCHAR2,
809 X_Attribute2 VARCHAR2,
810 X_Attribute3 VARCHAR2,
811 X_Attribute4 VARCHAR2,
812 X_Attribute5 VARCHAR2,
813 X_Attribute6 VARCHAR2,
814 X_Attribute7 VARCHAR2,
815 X_Attribute8 VARCHAR2,
816 X_Attribute9 VARCHAR2,
817 X_Attribute10 VARCHAR2,
818 X_Attribute11 VARCHAR2,
819 X_Attribute12 VARCHAR2,
820 X_Attribute13 VARCHAR2,
821 X_Attribute14 VARCHAR2,
822 X_Attribute15 VARCHAR2,
823 X_Attribute16 VARCHAR2,
824 X_Attribute17 VARCHAR2,
825 X_Attribute18 VARCHAR2,
826 X_Attribute19 VARCHAR2,
827 X_Attribute20 VARCHAR2,
828 x_party_id NUMBER,
829 x_party_site_id NUMBER,
830 x_msg_count OUT NOCOPY NUMBER,
831 x_msg_data OUT NOCOPY VARCHAR2,
832 x_return_status OUT NOCOPY VARCHAR2
833 ) IS
834
835 i_return_status VARCHAR2(1);
836 i_msg_count NUMBER;
837 i_msg_data VARCHAR2(2000);
838 i_Default_Phone_Country_Code VARCHAR2(30);
839
840 BEGIN
841
842 i_Default_Phone_Country_Code := Get_Default_Phone_Country_Code;
843
844 Insert_Row(
845 X_Phone_Id
846 , X_Last_Update_Date
847 , X_Last_Updated_By
848 , X_Creation_Date
849 , X_Created_By
850 , X_Phone_Number
851 , X_Status
852 , X_Phone_Type
853 , X_Last_Update_Login
854 , X_Customer_Id
855 , X_Address_Id
856 , X_Contact_Id
857 , i_Default_Phone_Country_Code
858 , X_Area_Code
859 , X_Extension
860 , X_Primary_Flag
861 , X_Orig_System_Reference
862 , X_Attribute_Category
863 , X_Attribute1
864 , X_Attribute2
865 , X_Attribute3
866 , X_Attribute4
867 , X_Attribute5
868 , X_Attribute6
869 , X_Attribute7
870 , X_Attribute8
871 , X_Attribute9
872 , X_Attribute10
873 , X_Attribute11
874 , X_Attribute12
875 , X_Attribute13
876 , X_Attribute14
877 , X_Attribute15
878 , X_Attribute16
879 , X_Attribute17
880 , X_Attribute18
881 , X_Attribute19
882 , X_Attribute20
883 , x_party_id
884 , x_party_site_id
885 , 'N'
886 , NULL
887 , NULL
888 , NULL
889 , NULL
890 , i_msg_count
891 , i_msg_data
892 , i_return_status
893 );
894
895 X_Return_Status := i_return_status;
896 X_Msg_Count := i_msg_count;
897 X_Msg_Data := i_msg_data;
898
899 END Insert_Row;
900
901 -- Overload procedure for collection workbench
902 -- Fix bug 1694959
903 PROCEDURE Update_Row(
904 X_Phone_Id NUMBER,
905 X_Last_Update_Date IN OUT NOCOPY DATE,
906 X_Last_Updated_By NUMBER,
907 X_Phone_Number VARCHAR2,
908 X_Status VARCHAR2,
909 X_Phone_Type VARCHAR2,
910 X_Last_Update_Login NUMBER,
911 X_Customer_Id NUMBER,
912 X_Address_Id NUMBER,
913 X_Contact_Id NUMBER,
914 X_Area_Code VARCHAR2,
915 X_Extension VARCHAR2,
916 X_Primary_Flag VARCHAR2,
917 X_Attribute_Category VARCHAR2,
918 X_Attribute1 VARCHAR2,
919 X_Attribute2 VARCHAR2,
920 X_Attribute3 VARCHAR2,
921 X_Attribute4 VARCHAR2,
922 X_Attribute5 VARCHAR2,
923 X_Attribute6 VARCHAR2,
924 X_Attribute7 VARCHAR2,
925 X_Attribute8 VARCHAR2,
926 X_Attribute9 VARCHAR2,
927 X_Attribute10 VARCHAR2,
928 X_Attribute11 VARCHAR2,
929 X_Attribute12 VARCHAR2,
930 X_Attribute13 VARCHAR2,
931 X_Attribute14 VARCHAR2,
932 X_Attribute15 VARCHAR2,
933 X_Attribute16 VARCHAR2,
934 X_Attribute17 VARCHAR2,
935 X_Attribute18 VARCHAR2,
936 X_Attribute19 VARCHAR2,
937 X_Attribute20 VARCHAR2,
938 x_cust_contact_point_id NUMBER,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2,
941 x_return_status OUT NOCOPY VARCHAR2
942
943 ) IS
944
945 i_return_status VARCHAR2(1);
946 i_msg_count NUMBER;
947 i_msg_data VARCHAR2(2000);
948 i_Default_Phone_Country_Code VARCHAR2(30);
949
950 BEGIN
951
952 i_Default_Phone_Country_Code := Get_Default_Phone_Country_Code;
953
954 Update_Row(
955 X_Phone_Id
956 , X_Last_Update_Date
957 , X_Last_Updated_By
958 , X_Phone_Number
959 , X_Status
960 , X_Phone_Type
961 , X_Last_Update_Login
962 , X_Customer_Id
963 , X_Address_Id
964 , X_Contact_Id
965 , i_Default_Phone_Country_Code
966 , X_Area_Code
967 , X_Extension
968 , X_Primary_Flag
969 , X_Attribute_Category
970 , X_Attribute1
971 , X_Attribute2
972 , X_Attribute3
973 , X_Attribute4
974 , X_Attribute5
975 , X_Attribute6
976 , X_Attribute7
977 , X_Attribute8
978 , X_Attribute9
979 , X_Attribute10
980 , X_Attribute11
981 , X_Attribute12
982 , X_Attribute13
983 , X_Attribute14
984 , X_Attribute15
985 , X_Attribute16
986 , X_Attribute17
987 , X_Attribute18
988 , X_Attribute19
989 , X_Attribute20
990 , x_cust_contact_point_id
991 , 'N'
992 , NULL
993 , NULL
994 , NULL
995 , NULL
996 , i_msg_count
997 , i_msg_data
998 , i_return_status
999 );
1000
1001 X_Return_Status := i_return_status;
1002 X_Msg_Count := i_msg_count;
1003 X_Msg_Data := i_msg_data;
1004
1005 END Update_Row;
1006
1007
1008 PROCEDURE Delete_Row(X_phoneid VARCHAR2) IS
1009 BEGIN
1010
1011 DELETE FROM hz_contact_points
1012 WHERE contact_point_id = X_phoneid;
1013 if (SQL%NOTFOUND) then
1014 Raise NO_DATA_FOUND;
1015 end if;
1016
1017 END Delete_Row;
1018 --
1019 -- FUNCTION
1020 -- Get_Default_Phone_Country_Code
1021 --
1022 -- DESCRIPTION
1023 -- This function provide a default phone country code
1024 --
1025 -- SCOPE - PROVATE
1026 --
1027 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1028 --
1029 -- ARGUMENTS : IN:
1030 -- None
1031 -- OUT:
1032 -- None
1033 --
1034 -- RETURNS : Default_Phone_Country_Code
1035 --
1036 -- NOTES
1037 --
1038 -- MODIFICATION HISTORY - Created by Dylan Wan
1039 --
1040 --
1041 FUNCTION Get_Default_Phone_Country_Code
1042 RETURN VARCHAR2
1043 IS
1044
1045 l_phone_country_code VARCHAR2(10);
1046 l_home_country_code VARCHAR2(60);
1047 l_default_country_code VARCHAR2(60);
1048
1049 BEGIN
1050
1051 l_home_country_code := arp_standard.sysparm.default_country;
1052 fnd_profile.get('DEFAULT_COUNTRY',l_default_country_code);
1053
1054 IF ( l_default_country_code IS NULL )
1055 THEN
1056 l_default_country_code := l_home_country_code;
1057 END IF;
1058
1059 SELECT a.phone_country_code
1060 INTO l_phone_country_code
1061 FROM hz_phone_country_codes a
1062 WHERE a.territory_code = l_default_country_code;
1063
1064 RETURN l_phone_country_code;
1065
1066 END Get_Default_Phone_Country_Code;
1067
1068 END arh_phon_pkg;