[Home] [Help]
PACKAGE BODY: APPS.ARH_CONT_PKG
Source
4 -- PROCEDURE
1 PACKAGE BODY arh_cont_pkg as
2 /* $Header: ARHCONTB.pls 120.12 2006/12/04 09:58:42 salladi ship $*/
3 --
5 -- check_unique_contact_name
6 --
7 -- DESCRIPTION
8 -- This procedure checks that a contact name is unique.
9 -- If it is not it sets a message on the stack and returns
10 -- a warning flag.
11 --
12 -- SCOPE - PUBLIC
13 --
17 -- x_customer_id - customer_id
14 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
15 --
16 -- ARGUMENTS : IN: x_rowid - uid of row
18 -- x_first_name -
19 -- x_last_name
20 -- OUT:
21 -- x_warning_flag - W - Warning generated
22 -- null - no warning genrated
23 -- NOTES
24 --
25 --
26 --
27 --
28
29 -- Local procedure specifications
30 PROCEDURE compare_existing_contact_info
31 ( p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type,
32 x_update_required IN OUT NOCOPY VARCHAR2,
33 x_relationship_id IN OUT NOCOPY NUMBER,
34 x_ocon_version_number IN OUT NOCOPY NUMBER );
35
36 PROCEDURE upd_ocon_update
37 (p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type);
38 --}
39
40 FUNCTION INIT_SWITCH
41 ( p_date IN DATE,
42 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
43 RETURN DATE
44 IS
45 res_date date;
46 BEGIN
47 IF p_switch = 'NULL_GMISS' THEN
48 IF p_date IS NULL THEN
49 res_date := FND_API.G_MISS_DATE;
50 ELSE
51 res_date := p_date;
52 END IF;
53 ELSIF p_switch = 'GMISS_NULL' THEN
54 IF p_date = FND_API.G_MISS_DATE THEN
55 res_date := NULL;
56 ELSE
57 res_date := p_date;
58 END IF;
59 ELSE
60 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
61 END IF;
62 RETURN res_date;
63 END;
64
65 FUNCTION INIT_SWITCH
66 ( p_char IN VARCHAR2,
67 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
68 RETURN VARCHAR2
69 IS
70 res_char varchar2(2000);
71 BEGIN
72 IF p_switch = 'NULL_GMISS' THEN
73 IF p_char IS NULL THEN
74 return FND_API.G_MISS_CHAR;
75 ELSE
76 return p_char;
77 END IF;
78 ELSIF p_switch = 'GMISS_NULL' THEN
79 IF p_char = FND_API.G_MISS_CHAR THEN
80 return NULL;
81 ELSE
82 return p_char;
83 END IF;
84 ELSE
85 return ('INCORRECT_P_SWITCH');
86 END IF;
87 END;
88
89 FUNCTION INIT_SWITCH
90 ( p_num IN NUMBER,
91 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
92 RETURN NUMBER
93 IS
94 BEGIN
95 IF p_switch = 'NULL_GMISS' THEN
96 IF p_num IS NULL THEN
97 return FND_API.G_MISS_NUM;
98 ELSE
99 return p_num;
100 END IF;
101 ELSIF p_switch = 'GMISS_NULL' THEN
102 IF p_num = FND_API.G_MISS_NUM THEN
103 return NULL;
104 ELSE
105 return p_num;
106 END IF;
107 ELSE
108 return ('9999999999');
109 END IF;
110 END;
111
112 PROCEDURE object_version_select
113 (p_table_name IN VARCHAR2,
114 p_col_id IN VARCHAR2,
115 x_rowid IN OUT NOCOPY ROWID,
116 x_object_version_number IN OUT NOCOPY NUMBER,
117 x_last_update_date IN OUT NOCOPY DATE,
118 x_id_value IN OUT NOCOPY NUMBER,
119 x_return_status IN OUT NOCOPY VARCHAR2,
120 x_msg_count IN OUT NOCOPY NUMBER,
121 x_msg_data IN OUT NOCOPY VARCHAR2 )
122 IS
123 CURSOR cu_party_version IS
124 SELECT ROWID,
125 OBJECT_VERSION_NUMBER,
126 LAST_UPDATE_DATE,
127 NULL
128 FROM HZ_PARTIES
129 WHERE PARTY_ID = p_col_id;
130
131 CURSOR cu_org_contact_version IS
132 SELECT ROWID,
133 OBJECT_VERSION_NUMBER,
134 LAST_UPDATE_DATE,
135 PARTY_RELATIONSHIP_ID
136 FROM HZ_ORG_CONTACTS
137 WHERE ORG_CONTACT_ID = p_col_id;
138
139 CURSOR cu_relationship_version IS
140 SELECT ROWID,
141 OBJECT_VERSION_NUMBER,
142 LAST_UPDATE_DATE,
143 PARTY_ID
144 FROM HZ_RELATIONSHIPS
145 WHERE RELATIONSHIP_ID = p_col_id
146 AND DIRECTIONAL_FLAG = 'F';
147
148 CURSOR cu_contact_pt_version IS
149 SELECT ROWID,
150 OBJECT_VERSION_NUMBER,
151 LAST_UPDATE_DATE,
152 NULL
153 FROM HZ_CONTACT_POINTS
154 WHERE CONTACT_POINT_ID = p_col_id;
155
156 CURSOR cu_acct_role_version IS
157 SELECT ROWID,
158 OBJECT_VERSION_NUMBER,
159 LAST_UPDATE_DATE,
160 NULL
161 FROM HZ_CUST_ACCOUNT_ROLES
162 WHERE CUST_ACCOUNT_ROLE_ID = p_col_id;
163 l_last_update_date DATE;
164 BEGIN
165 IF p_table_name = 'HZ_PARTIES' THEN
166 OPEN cu_party_version;
167 FETCH cu_party_version INTO
168 x_rowid ,
169 x_object_version_number,
170 l_last_update_date ,
171 x_id_value;
172 CLOSE cu_party_version;
173 ELSIF p_table_name = 'HZ_ORG_CONTACTS' THEN
174 OPEN cu_org_contact_version;
175 FETCH cu_org_contact_version INTO
176 x_rowid ,
177 x_object_version_number,
178 l_last_update_date ,
179 x_id_value;
180 CLOSE cu_org_contact_version ;
181
182 ELSIF p_table_name = 'HZ_RELATIONSHIPS' THEN
183 OPEN cu_relationship_version;
187 l_last_update_date ,
184 FETCH cu_relationship_version INTO
185 x_rowid ,
186 x_object_version_number,
188 x_id_value;
189 CLOSE cu_relationship_version;
190
191 ELSIF p_table_name = 'HZ_CONTACT_POINTS' THEN
192 OPEN cu_contact_pt_version;
193 FETCH cu_contact_pt_version INTO
194 x_rowid ,
195 x_object_version_number,
196 l_last_update_date ,
197 x_id_value;
198 CLOSE cu_contact_pt_version ;
199
200 ELSIF p_table_name = 'HZ_CUST_ACCOUNT_ROLES' THEN
201 OPEN cu_acct_role_version;
202 FETCH cu_acct_role_version INTO
203 x_rowid ,
204 x_object_version_number,
205 l_last_update_date ,
206 x_id_value;
207 CLOSE cu_acct_role_version;
208
209 END IF;
210
211 IF x_rowid IS NULL THEN
212 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
213 FND_MESSAGE.SET_TOKEN('RECORD',p_table_name);
214 FND_MESSAGE.SET_TOKEN('ID',p_col_id);
215 FND_MSG_PUB.ADD;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 ELSE
218 IF TO_CHAR(x_last_update_date,'DD-MON-YYYY HH:MI:SS') <>
219 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
220 THEN
221 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
222 FND_MESSAGE.SET_TOKEN('TABLE', p_table_name);
223 FND_MSG_PUB.ADD;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 END IF;
226 END IF;
227 END;
228
229 PROCEDURE update_mail_stop
230 ( p_org_contact_id IN NUMBER,
231 p_mail_stop IN VARCHAR2,
232 x_return_status IN OUT NOCOPY VARCHAR2,
233 x_msg_data IN OUT NOCOPY VARCHAR2)
234 IS
235 CURSOR cu_org_contact_update
236 IS
237 SELECT org_contact_id
238 FROM hz_org_contacts
239 WHERE org_contact_id = p_org_contact_id
240 FOR UPDATE OF org_contact_id NOWAIT;
241 l_lock NUMBER;
242 BEGIN
243 OPEN cu_org_contact_update;
244 FETCH cu_org_contact_update INTO l_lock;
245 IF cu_org_contact_update%FOUND THEN
246 UPDATE hz_org_contacts
247 SET mail_stop = p_mail_stop
248 WHERE org_contact_id = p_org_contact_id;
249 ELSE
250 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
251 FND_MESSAGE.SET_TOKEN('RECORD','HZ_ORG_CONTACTS');
252 FND_MESSAGE.SET_TOKEN('ID',p_org_contact_id);
253 FND_MSG_PUB.ADD;
254 x_return_status := FND_API.G_RET_STS_ERROR;
255 END IF;
256 CLOSE cu_org_contact_update;
257 EXCEPTION
258 WHEN OTHERS THEN
259 IF cu_org_contact_update%ISOPEN THEN
260 CLOSE cu_org_contact_update;
261 END IF;
262 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
263 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
264 FND_MSG_PUB.ADD;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 END;
267
268
269 PROCEDURE check_unique_contact_name (
270 x_customer_id IN NUMBER,
271 x_first_name IN VARCHAR2,
272 x_last_name IN VARCHAR2,
273 x_warning_flag IN OUT NOCOPY VARCHAR2 ) IS
274 dummy number;
275 begin
276 select 1
277 into dummy
278 from dual
279 where not exists ( select 1
280 from hz_cust_account_roles acct_role,
281 hz_parties party,
282 hz_relationships rel
283 where acct_role.party_id = rel.party_id
284 and acct_role.role_type = 'CONTACT'
285 and rel.subject_id = party.party_id
286 and party.person_last_name = x_last_name
287 and party.person_first_name = x_first_name
288 and acct_role.cust_account_id = x_customer_id
289 and rel.subject_table_name = 'HZ_PARTIES'
290 and rel.object_table_name = 'HZ_PARTIES'
291 and rel.directional_flag = 'F'
292 );
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 fnd_message.set_name ('AR','AR_CUST_DUP_CONTACT_NAME');
296 x_warning_flag := 'W';
297 END check_unique_contact_name;
298 --
299 --
300 --
301 --
302 --
303 -- PROCEDURE
304 -- check_unique_orig_system_ref
305 --
306 -- DESCRIPTION
307 -- This procedure checks that orig_system_reference is unique.
308 -- If it is not it sets a message on the stack and returns
309 -- failure.
310 --
311 -- SCOPE - PUBLIC
312 --
313 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
314 --
315 -- ARGUMENTS : IN: x_rowid - uid of row
316 -- x_orig_system_reference - value to check
317 -- OUT:
318
319 -- NOTES
320 --
321 --
322 --
323 --
324 PROCEDURE check_unique_orig_system_ref(
325 x_orig_system_reference IN VARCHAR2 ) IS
326 --
327 --
328 dummy number;
329 BEGIN
330 select 1
331 into dummy
332 from dual
333 where not exists ( select 1
334 from hz_cust_account_roles acct_role
335 where acct_role.orig_system_reference
336 = x_orig_system_reference
337 );
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 fnd_message.set_name ('AR','AR_CUST_CONT_REF_EXISTS');
341 app_exception.raise_exception;
342 --
346 PROCEDURE Insert_Row(
343 END check_unique_orig_system_ref;
344
345
347 X_Contact_Id IN OUT NOCOPY NUMBER,
348 X_Created_By NUMBER,
349 X_Creation_Date DATE,
350 X_Customer_Id NUMBER,
351 X_Last_Name VARCHAR2,
352 X_Last_Updated_By NUMBER,
353 X_Last_Update_Date DATE,
354 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
355 X_Status VARCHAR2,
356 X_Address_Id NUMBER,
357 X_Contact_Key VARCHAR2,
358 X_First_Name VARCHAR2,
359 X_Job_Title VARCHAR2,
360 X_Last_Update_Login NUMBER,
361 X_Mail_Stop VARCHAR2,
362 X_Title VARCHAR2,
363 X_Attribute_Category VARCHAR2,
364 X_Attribute1 VARCHAR2,
365 X_Attribute2 VARCHAR2,
366 X_Attribute3 VARCHAR2,
367 X_Attribute4 VARCHAR2,
368 X_Attribute5 VARCHAR2,
369 X_Attribute6 VARCHAR2,
370 X_Attribute7 VARCHAR2,
371 X_Attribute8 VARCHAR2,
372 X_Attribute9 VARCHAR2,
376 X_Attribute13 VARCHAR2,
373 X_Attribute10 VARCHAR2,
374 X_Attribute11 VARCHAR2,
375 X_Attribute12 VARCHAR2,
377 X_Attribute14 VARCHAR2,
378 X_Attribute15 VARCHAR2,
379 X_Attribute16 VARCHAR2,
380 X_Attribute17 VARCHAR2,
381 X_Attribute18 VARCHAR2,
382 X_Attribute19 VARCHAR2,
383 X_Attribute20 VARCHAR2,
384 X_Attribute21 VARCHAR2,
385 X_Attribute22 VARCHAR2,
386 X_Attribute23 VARCHAR2,
387 X_Attribute24 VARCHAR2,
388 X_Attribute25 VARCHAR2,
389 X_Email_Address VARCHAR2,
390 X_Last_Name_Alt VARCHAR2 DEFAULT NULL,
391 X_First_Name_Alt VARCHAR2 DEFAULT NULL,
392 X_Contact_Number IN OUT NOCOPY VARCHAR2,
393 X_Party_Id NUMBER,
394 X_Party_Site_Id NUMBER,
395 X_Contact_Party_Id IN OUT NOCOPY NUMBER,
396 X_Org_Contact_Id IN OUT NOCOPY NUMBER,
397 X_Contact_Point_Id IN OUT NOCOPY NUMBER,
398 X_Cust_Account_Role_Id IN OUT NOCOPY NUMBER,
399 X_Return_Status OUT NOCOPY VARCHAR2,
400 X_Msg_Count OUT NOCOPY NUMBER,
401 X_Msg_Data OUT NOCOPY VARCHAR2
402 ) IS
403
404
405 i_rel_party_id NUMBER;
406 i_sub_party_id NUMBER;
407 i_return_status VARCHAR2(1);
408 i_msg_count NUMBER;
409 i_msg_data VARCHAR2(2000);
410 i_job_title_code VARCHAR2(30);
411 BEGIN
412
413 Insert_Row( X_Contact_Id
414 , X_Created_By
415 , X_Creation_Date
416 , X_Customer_Id
417 , X_Last_Name
418 , X_Last_Updated_By
419 , X_Last_Update_Date
420 , X_Orig_System_Reference
421 , X_Status
422 , X_Address_Id
423 , X_Contact_Key
424 , X_First_Name
425 , X_Job_Title
426 , I_Job_Title_Code
427 , X_Last_Update_Login
431 , X_Attribute1
428 , X_Mail_Stop
429 , X_Title
430 , X_Attribute_Category
432 , X_Attribute2
433 , X_Attribute3
434 , X_Attribute4
435 , X_Attribute5
436 , X_Attribute6
437 , X_Attribute7
438 , X_Attribute8
439 , X_Attribute9
440 , X_Attribute10
441 , X_Attribute11
442 , X_Attribute12
443 , X_Attribute13
444 , X_Attribute14
445 , X_Attribute15
446 , X_Attribute16
447 , X_Attribute17
448 , X_Attribute18
449 , X_Attribute19
450 , X_Attribute20
451 , X_Attribute21
452 , X_Attribute22
453 , X_Attribute23
454 , X_Attribute24
455 , X_Attribute25
456 , X_Email_Address
457 , X_Last_Name_Alt
458 , X_First_Name_Alt
459 , X_Contact_Number
460 , X_Party_Id
461 , i_sub_party_id
462 , X_Party_Site_Id
463 , X_Contact_Party_Id
464 , i_rel_party_id
465 , X_Org_Contact_Id
466 , X_Contact_Point_Id
467 , X_Cust_Account_Role_Id
468 , i_return_status
469 , i_msg_count
470 , i_msg_data
471 );
472
473 X_Return_Status := i_return_status;
474 X_Msg_Count := i_msg_count;
475 X_Msg_Data := i_msg_data;
476
477
478 END Insert_Row;
479
480
481
482 PROCEDURE Insert_Row(
483 X_Contact_Id IN OUT NOCOPY NUMBER,
484 X_Created_By NUMBER,
485 X_Creation_Date DATE,
486 X_Customer_Id NUMBER,
487 X_Last_Name VARCHAR2,
488 X_Last_Updated_By NUMBER,
489 X_Last_Update_Date DATE,
490 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
491 X_Status VARCHAR2,
492 X_Address_Id NUMBER,
493 X_Contact_Key VARCHAR2,
494 X_First_Name VARCHAR2,
495 X_Job_Title VARCHAR2,
496 X_Job_Title_Code VARCHAR2,
497 X_Last_Update_Login NUMBER,
498 X_Mail_Stop VARCHAR2,
499 X_Title VARCHAR2,
500 X_Attribute_Category VARCHAR2,
501 X_Attribute1 VARCHAR2,
502 X_Attribute2 VARCHAR2,
503 X_Attribute3 VARCHAR2,
504 X_Attribute4 VARCHAR2,
505 X_Attribute5 VARCHAR2,
506 X_Attribute6 VARCHAR2,
507 X_Attribute7 VARCHAR2,
508 X_Attribute8 VARCHAR2,
509 X_Attribute9 VARCHAR2,
510 X_Attribute10 VARCHAR2,
511 X_Attribute11 VARCHAR2,
512 X_Attribute12 VARCHAR2,
513 X_Attribute13 VARCHAR2,
514 X_Attribute14 VARCHAR2,
515 X_Attribute15 VARCHAR2,
516 X_Attribute16 VARCHAR2,
517 X_Attribute17 VARCHAR2,
521 X_Attribute21 VARCHAR2,
518 X_Attribute18 VARCHAR2,
519 X_Attribute19 VARCHAR2,
520 X_Attribute20 VARCHAR2,
522 X_Attribute22 VARCHAR2,
523 X_Attribute23 VARCHAR2,
524 X_Attribute24 VARCHAR2,
525 X_Attribute25 VARCHAR2,
526 X_Email_Address VARCHAR2,
527 X_Last_Name_Alt VARCHAR2 DEFAULT NULL,
528 X_First_Name_Alt VARCHAR2 DEFAULT NULL,
529 X_Contact_Number IN OUT NOCOPY VARCHAR2,
530 X_Party_Id NUMBER,
531 X_Sub_Party_Id NUMBER,
532 X_Party_Site_Id NUMBER,
533 X_Contact_Party_Id IN OUT NOCOPY NUMBER,
534 X_Rel_Party_Id IN OUT NOCOPY NUMBER,
535 X_Org_Contact_Id IN OUT NOCOPY NUMBER,
536 X_Contact_Point_Id IN OUT NOCOPY NUMBER,
537 X_Cust_Account_Role_Id IN OUT NOCOPY NUMBER,
538 X_Return_Status OUT NOCOPY VARCHAR2,
539 X_Msg_Count OUT NOCOPY NUMBER,
540 X_Msg_Data OUT NOCOPY VARCHAR2
541 ) IS
542
543 --party_rec hz_party_pub.party_rec_type;
544 --per_rec hz_party_pub.person_rec_type;
545 --prel_rec hz_party_pub.party_rel_rec_type;
546 --ocon_rec hz_party_pub.org_contact_rec_type;
547 --cpoint_rec hz_contact_point_pub.contact_points_rec_type;
548 --cust_point_rec hz_customer_accounts_pub.cust_contact_pt_rec_type;
549 --email_rec hz_contact_point_pub.email_rec_type;
550 --arole_rec hz_customer_accounts_pub.cust_acct_roles_rec_type;
551
552 party_rec hz_party_v2pub.party_rec_type;
553 per_rec hz_party_v2pub.person_rec_type;
554 prel_rec hz_relationship_v2pub.relationship_rec_type;
555 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
556 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
557 email_rec hz_contact_point_v2pub.email_rec_type;
558 arole_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
559
560 i_subject_party_id NUMBER;
561 i_subject_party_number VARCHAR2(30);
562 i_object_party_id NUMBER;
563 i_profile_id NUMBER;
564 tmp_var VARCHAR2(2000);
565 i_party_relationship_id NUMBER;
566 i_party_id NUMBER;
567 i_party_number VARCHAR2(30);
568 ii_party_number VARCHAR2(30);
569 i_org_contact_id NUMBER;
570 i_contact_point_id NUMBER;
571 i NUMBER;
572 tmp_var1 VARCHAR2(2000);
573 i_create_org_contact VARCHAR2(1) := 'Y';
574 i_lock_id NUMBER;
575 x_cust_contact_point_id NUMBER;
576 l_generate_party_number VARCHAR2(1);
577
578 CURSOR cu_party_type(i_party_id IN NUMBER)
579 IS
580 SELECT party_type
581 FROM hz_parties
582 WHERE party_id = i_party_id;
583
584 l_subject_type VARCHAR2(30);
585 l_object_type VARCHAR2(30);
586
587 l_relationship_id NUMBER;
588 l_ocon_version_number NUMBER;
589 l_update_required VARCHAR2(1) := 'N';
590
591 BEGIN
592
593 arp_standard.debug('arh_cont_pkg.insert_row +');
594 arp_standard.debug(' x_contact_number :'||x_contact_number );
595 arp_standard.debug(' x_contact_id :'||x_contact_id );
596 --
597 -- Bug 631501: automatically assign contact number if profile
598 -- option AR_AUTOMATIC_CONTACT_NUNMBERING is set to 'Yes'
599 --
600 IF X_Contact_Number is NULL THEN
601 -- if (nvl(fnd_profile.value('AR_AUTOMATIC_CONTACT_NUMBERING'), 'N')
602 -- = 'Y') then
603 SELECT hz_contact_numbers_s.nextval INTO X_Contact_Number FROM DUAL;
604 -- end if;
605 -- Bug Fix : 2172123.
606 END IF;
607
608 /* Bug fix 3421879 - ended IF statement above and created a new IF below.
609 Earlier it was one if seperated by ELSIF
610 */
611 IF X_Org_Contact_Id IS NOT NULL THEN
612 i_create_org_contact := 'N';
613 END IF;
614
615 IF i_create_org_contact = 'Y' THEN
616 SELECT hz_org_contacts_s.nextval INTO x_contact_id FROM DUAL;
617 END IF;
618
619 arp_standard.debug(' i_create_org_contact :'||i_create_org_contact );
620
621 -- BugFix:2225260
622 --- If Subject Party does not exist,added this following if clause.
623 --
624 IF X_Sub_Party_Id IS NULL THEN
625 select hz_party_number_s.nextval into i_subject_party_number from dual;
626 select hz_parties_s.nextval into i_subject_party_id from dual;
627 ELSE
628 i_subject_party_id := X_Sub_Party_Id;
629 END IF;
630
631 arp_standard.debug(' X_Sub_Party_Id :'||X_Sub_Party_Id );
632 arp_standard.debug(' i_subject_party_id :'||i_subject_party_id );
633
634 select hz_contact_points_s.nextval into i_contact_point_id from dual;
635 select hz_cust_account_roles_s.nextval into x_cust_account_role_id from dual;
636 select hz_cust_contact_points_s.nextval into x_cust_contact_point_id from dual;
637
641 arh_cont_pkg.check_unique_orig_system_ref(x_orig_system_reference);
638 --
639 -- validate uniqueness
640 --
642 l_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
643
644
645 --
646 -- Create a person party
647 --
648 per_rec.party_rec.party_id := i_subject_party_id;
649
650 IF l_generate_party_number = 'N' THEN
651 per_rec.party_rec.party_number := i_subject_party_number;
652 END IF;
653
654 per_rec.party_rec.status := 'A';
655 per_rec.person_first_name := x_first_name;
656 per_rec.person_last_name := x_last_name;
657 /* Bug Fix : 2500275 */
658 -- per_rec.person_title := x_title;
659 per_rec.person_pre_name_adjunct := x_title;
660 per_rec.created_by_module := 'TCA_FORM_WRAPPER';
661
662 --Bug fix 1688212
663 --Bug:2225260,added sub party id checking
664 IF i_create_org_contact = 'Y' AND X_Sub_Party_Id IS NULL THEN
665
666 HZ_PARTY_V2PUB.create_person (
667 p_person_rec => per_rec,
668 x_party_id => i_subject_party_id,
669 x_party_number => i_subject_party_number,
670 x_profile_id => i_profile_id,
671 x_return_status => x_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data );
674
675 IF x_msg_count > 1 THEN
676 FOR i IN 1..x_msg_count LOOP
677 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
678 tmp_var1 := tmp_var1 || ' '|| tmp_var;
679 END LOOP;
680 x_msg_data := tmp_var1;
681 END IF;
682
683 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
684 return;
685 END IF;
686
687 x_contact_party_id := i_subject_party_id;
688
689 END IF;
690
691 --
692 -- Create an Org Contact
693 --
694 OPEN cu_party_type(x_party_id);
695 FETCH cu_party_type INTO l_object_type;
696 CLOSE cu_party_type;
697
698 OPEN cu_party_type(i_subject_party_id);
699 FETCH cu_party_type INTO l_subject_type;
700 CLOSE cu_party_type;
701
702 SELECT hz_party_number_s.nextval INTO ii_party_number FROM DUAL;
703
704 ocon_rec.party_rel_rec.subject_id := i_subject_party_id;
705 ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
706 -- ocon_rec.party_rel_rec.subject_type := 'PERSON';
707 ocon_rec.party_rel_rec.subject_type := l_subject_type;
708 ocon_rec.party_rel_rec.object_id := x_party_id;
709 ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
710 ocon_rec.party_rel_rec.object_type := l_object_type;
711 ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
712 ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
713 -- ocon_rec.party_rel_rec.directional_flag := 'Y';
714 ocon_rec.party_rel_rec.status := 'A';
715 ocon_rec.party_rel_rec.start_date := sysdate;
716 -- ocon_rec.party_rel_rec.party_relationship_id := i_party_relationship_id;
717 ocon_rec.party_rel_rec.created_by_module := 'TCA_FORM_WRAPPER';
718
719 IF l_generate_party_number = 'N' THEN
720 ocon_rec.party_rel_rec.party_rec.party_number := ii_party_number;
721 END IF;
722
723 ocon_rec.contact_number := x_contact_number;
724 ocon_rec.title := x_title;
725 ocon_rec.job_title := x_job_title;
726 ocon_rec.job_title_code := x_job_title_code;
727 -- ocon_rec.mail_stop := x_mail_stop;
728 -- ocon_rec.contact_key := x_contact_key;
729 ocon_rec.party_site_id := x_party_site_id;
730 ocon_rec.orig_system_reference := x_orig_system_reference;
731 -- ocon_rec.status := x_status;
732
733 /*bug5442145-5330162
734 ocon_rec.attribute_category := x_Attribute_Category;
735 ocon_rec.attribute1 := x_Attribute1;
736 ocon_rec.attribute2 := x_Attribute2;
737 ocon_rec.attribute3 := x_Attribute3;
738 ocon_rec.attribute4 := x_Attribute4;
739 ocon_rec.attribute5 := x_Attribute5;
740 ocon_rec.attribute6 := x_Attribute6;
741 ocon_rec.attribute7 := x_Attribute7;
742 ocon_rec.attribute8 := x_attribute8;
743 ocon_rec.attribute9 := x_Attribute9;
744 ocon_rec.attribute10 := x_Attribute10;
745 ocon_rec.attribute11 := x_Attribute11;
749 ocon_rec.attribute15 := x_Attribute15;
746 ocon_rec.attribute12 := x_Attribute12;
747 ocon_rec.attribute13 := x_Attribute13;
748 ocon_rec.attribute14 := x_Attribute14;
750 ocon_rec.attribute16 := x_Attribute16;
751 ocon_rec.attribute17 := x_Attribute17;
752 ocon_rec.attribute18 := x_Attribute18;
753 ocon_rec.attribute19 := x_Attribute19;
754 ocon_rec.attribute20 := x_Attribute20;
755 */
756 ocon_rec.created_by_module := 'TCA_FORM_WRAPPER';
757
758 IF i_create_org_contact = 'Y' THEN
759
760 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
761 p_org_contact_rec => ocon_rec,
762 x_org_contact_id => i_org_contact_id,
763 x_party_rel_id => i_party_relationship_id,
764 x_party_id => i_party_id,
765 x_party_number => i_party_number,
766 x_return_status => x_return_status,
767 x_msg_count => x_msg_count,
768 x_msg_data => x_msg_data );
769
770 IF x_msg_count > 1 THEN
771 FOR i IN 1..x_msg_count LOOP
772 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
773 tmp_var1 := tmp_var1 || ' '|| tmp_var;
774 END LOOP;
775 x_msg_data := tmp_var1;
776 END IF;
777
778 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
779 return;
780 END IF;
781
782 x_org_contact_id := i_org_contact_id;
783 x_rel_party_id := i_party_id;
784
785 END IF;
786
787 --
788 -- mail_stop is not in V2 hz_party_contact_v2pub.org_contact_rec_type
789 -- Need to do this additional update in replacement
790 -- This fix has to be removed if V2 hz_party_contact_v2pub.org_contact_rec_type is
791 -- changed to support the attribute MAIL_STOP
792 --
793 IF ( (X_Mail_Stop IS NOT NULL)
794 AND (X_Mail_Stop <> FND_API.G_MISS_CHAR))
795 THEN
796 update_mail_stop
797 ( p_org_contact_id => x_org_contact_id,
798 p_mail_stop => X_Mail_Stop,
799 x_return_status => x_return_status,
800 x_msg_data => x_msg_data);
801
802 IF x_msg_count > 1 THEN
803 FOR i IN 1..x_msg_count LOOP
804 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
805 tmp_var1 := tmp_var1 || ' '|| tmp_var;
806 END LOOP;
807 x_msg_data := tmp_var1;
808 END IF;
809
810 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
811 return;
812 END IF;
813
814 END IF;
815
816 --{BUG#4064156
817 IF i_create_org_contact = 'N' THEN
818
819 arp_standard.debug('Calling compare_existing_contact_info ');
820
821 ocon_rec.org_contact_id := X_Org_Contact_Id;
822
823 compare_existing_contact_info
824 ( p_ocon_rec => ocon_rec,
825 x_update_required => l_update_required,
826 x_relationship_id => l_relationship_id,
827 x_ocon_version_number => l_ocon_version_number );
828
829 arp_standard.debug(' l_update_required :'||l_update_required);
830 arp_standard.debug(' l_relationship_id :'||l_relationship_id);
831 arp_standard.debug(' l_ocon_version_number :'||l_ocon_version_number);
832
833
834 IF l_update_required = 'Y' THEN
835 arp_standard.debug('Calling upd_ocon_update ');
836 arp_standard.debug(' ocon_rec.org_contact_id :'||ocon_rec.org_contact_id);
837
838 upd_ocon_update(p_ocon_rec => ocon_rec);
839 END IF;
840
841 END IF;
842 --}
843
844 IF i_create_org_contact = 'N' THEN
845 i_subject_party_id := x_contact_party_id;
846 END IF;
847
848
849 --
850 -- Create an email for the contact
851 --
852 cpoint_rec.contact_point_id := i_contact_point_Id;
853 cpoint_rec.contact_point_type := 'EMAIL';
854 cpoint_rec.status := 'A';
855 cpoint_rec.owner_table_name := 'HZ_PARTIES';
856 cpoint_rec.owner_table_id := i_party_id;
857
858 -- the next 1 line is added to make sure the email_address is
859 -- denormalised into hz_parties. bug - 1276469.
860 cpoint_rec.primary_flag := 'Y';
861 cpoint_rec.created_by_module := 'TCA_FORM_WRAPPER';
862 email_rec.email_address := X_Email_Address;
863
864 IF i_create_org_contact = 'N' THEN
865
866 SELECT p.party_id
867 INTO cpoint_rec.owner_table_id
868 FROM hz_relationships p,
869 hz_org_contacts o
870 WHERE o.org_contact_id = x_org_contact_id
871 AND o.party_relationship_id = p.relationship_id
872 AND p.subject_table_name = 'HZ_PARTIES'
873 AND p.object_table_name = 'HZ_PARTIES'
874 AND p.directional_flag = 'F';
875
876 END IF;
877
878 IF X_Email_Address is not null THEN
879
880 HZ_CONTACT_POINT_V2PUB.create_contact_point (
881 p_contact_point_rec => cpoint_rec,
885 x_msg_count => x_msg_count,
882 p_email_rec => email_rec,
883 x_contact_point_id => i_contact_point_id,
884 x_return_status => x_return_status,
886 x_msg_data => x_msg_data
887 );
888
889 x_contact_point_id := i_contact_point_id;
890
891 IF x_msg_count > 1 THEN
892 FOR i IN 1..x_msg_count LOOP
893 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
894 tmp_var1 := tmp_var1 || ' '|| tmp_var;
895 END LOOP;
896 x_msg_data := tmp_var1;
897 END IF;
898
899 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
900 return;
901 END IF;
902
903 END IF;
904
905 --
906 -- Create a Cust Account Role
907 --
908 arole_rec.cust_account_role_id := x_cust_account_role_id;
909
910 IF i_create_org_contact = 'Y' THEN
911 arole_rec.party_id := i_party_id;
912 ELSE
913 arole_rec.party_id := x_contact_party_id;
914 END IF;
915
916 arole_rec.cust_account_id := x_customer_id;
917 arole_rec.cust_acct_site_id := x_address_id;
918 -- arole_rec.begin_date := sysdate;
919 arole_rec.role_type := 'CONTACT';
920 arole_rec.attribute_category := x_Attribute_Category;
921 arole_rec.attribute1 := x_Attribute1;
922 arole_rec.attribute2 := x_Attribute2;
923 arole_rec.attribute3 := x_Attribute3;
924 arole_rec.attribute4 := x_Attribute4;
925 arole_rec.attribute5 := x_Attribute5;
926 arole_rec.attribute6 := x_Attribute6;
927 arole_rec.attribute7 := x_Attribute7;
928 arole_rec.attribute8 := x_attribute8;
929 arole_rec.attribute9 := x_Attribute9;
930 arole_rec.attribute10 := x_Attribute10;
931 arole_rec.attribute11 := x_Attribute11;
932 arole_rec.attribute12 := x_Attribute12;
933 arole_rec.attribute13 := x_Attribute13;
934 arole_rec.attribute14 := x_Attribute14;
935 arole_rec.attribute15 := x_Attribute15;
936 arole_rec.attribute16 := x_Attribute16;
937 arole_rec.attribute17 := x_Attribute17;
938 arole_rec.attribute18 := x_Attribute18;
939 arole_rec.attribute19 := x_Attribute19;
940 arole_rec.attribute20 := x_Attribute20;
941 arole_rec.created_by_module := 'TCA_FORM_WRAPPER';
942 --Bug Fix : 2305458.
943 arole_rec.status := x_status;
944 -- bug 1276469. passed the contactstatus.
945 -- arole_rec.current_role_state := x_Status;
946
947 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
948 p_cust_account_role_rec => arole_rec,
949 x_cust_account_role_id => x_cust_account_role_id,
950 x_return_status => x_return_status,
951 x_msg_count => x_msg_count,
952 x_msg_data => x_msg_data
953 );
954
958 tmp_var1 := tmp_var1 || ' '|| tmp_var;
955 IF x_msg_count > 1 THEN
956 FOR i IN 1..x_msg_count LOOP
957 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
959 END LOOP;
960 x_msg_data := tmp_var1;
961 END IF;
962
963 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
964 return;
965 END IF;
966
967 IF x_return_status = 'S' and x_orig_system_reference is not null THEN
968
969 update hz_cust_account_roles
970 set orig_system_reference = x_orig_system_reference
971 where cust_account_role_id = x_cust_account_role_id;
972
973 select orig_system_reference
974 into x_orig_system_reference
975 from hz_cust_account_roles
976 where cust_account_role_id = x_cust_account_role_id;
977
978 END IF;
979
980 IF x_return_status = 'S' THEN
981
982 select cust_account_role_id
983 into i_lock_id
984 from hz_cust_account_roles
985 where cust_account_role_id = x_cust_account_role_id
986 for update of cust_account_role_id nowait;
987
988 update hz_cust_account_roles
989 set attribute21 = X_Attribute21,
990 attribute22 = X_Attribute22,
991 attribute23 = X_Attribute23,
992 attribute24 = X_Attribute24,
993 attribute25 = X_Attribute25
994 where cust_account_role_id = x_cust_account_role_id;
995
996 select orig_system_reference
997 into x_orig_system_reference
998 from hz_cust_account_roles
999 where cust_account_role_id = x_cust_account_role_id;
1000
1001 END IF;
1002
1003 END Insert_Row;
1004
1005
1006 PROCEDURE Update_Row( X_contact_id number,
1007 X_Last_Name VARCHAR2,
1008 X_Last_Updated_By NUMBER,
1009 X_Last_Update_Date in out NOCOPY DATE,
1010 X_party_Last_Update_Date in out NOCOPY DATE,
1011 X_org_cont_Last_Update_Date in out NOCOPY DATE,
1012 X_cont_point_Last_Update_Date in out NOCOPY DATE,
1013 X_prel_Last_Update_Date in out NOCOPY DATE,
1014 X_rel_party_Last_Update_Date in out NOCOPY DATE,
1015 X_Status VARCHAR2,
1016 X_Contact_Key VARCHAR2,
1017 X_First_Name VARCHAR2,
1018 X_Job_Title VARCHAR2,
1019 X_Job_Title_Code VARCHAR2,
1020 X_Last_Update_Login NUMBER,
1021 X_Mail_Stop VARCHAR2,
1022 X_Title VARCHAR2,
1023 X_Attribute_Category VARCHAR2,
1024 X_Attribute1 VARCHAR2,
1025 X_Attribute2 VARCHAR2,
1026 X_Attribute3 VARCHAR2,
1027 X_Attribute4 VARCHAR2,
1028 X_Attribute5 VARCHAR2,
1029 X_Attribute6 VARCHAR2,
1030 X_Attribute7 VARCHAR2,
1031 X_Attribute8 VARCHAR2,
1032 X_Attribute9 VARCHAR2,
1033 X_Attribute10 VARCHAR2,
1034 X_Attribute11 VARCHAR2,
1035 X_Attribute12 VARCHAR2,
1036 X_Attribute13 VARCHAR2,
1037 X_Attribute14 VARCHAR2,
1038 X_Attribute15 VARCHAR2,
1039 X_Attribute16 VARCHAR2,
1040 X_Attribute17 VARCHAR2,
1041 X_Attribute18 VARCHAR2,
1042 X_Attribute19 VARCHAR2,
1043 X_Attribute20 VARCHAR2,
1044 X_Attribute21 VARCHAR2,
1045 X_Attribute22 VARCHAR2,
1046 X_Attribute23 VARCHAR2,
1047 X_Attribute24 VARCHAR2,
1048 X_Attribute25 VARCHAR2,
1049 X_Email_Address VARCHAR2,
1050 X_Last_Name_Alt VARCHAR2 default g_varchar2,
1051 X_First_Name_Alt VARCHAR2 default g_varchar2,
1052 x_contact_number VARCHAR2,
1053 x_party_id number,
1054 x_party_site_id number,
1055 x_contact_party_id number,
1056 x_org_contact_id number,
1057 x_contact_point_id in out NOCOPY number,
1058 x_cust_account_role_id number,
1059 x_party_relationship_id number,
1060 x_return_status out NOCOPY varchar2,
1061 x_msg_count out NOCOPY number,
1062 x_msg_data out NOCOPY varchar2,
1063 x_rel_party_id number default null)
1064 IS
1065 X_PERSON_OBJECT_VERSION NUMBER :=-1;
1066 x_org_contact_object_version NUMBER :=-1;
1067 x_rel_object_version NUMBER :=-1;
1068 x_party_object_version NUMBER :=-1;
1069 x_contact_pt_object_version NUMBER :=-1;
1070 x_acct_role_object_version NUMBER :=-1;
1071 -- x_job_title_code VARCHAR2(30);
1072 BEGIN
1073 Update_Row(
1074 X_contact_id,
1075 X_Last_Name,
1076 X_Last_Updated_By,
1077 X_Last_Update_Date,
1078 X_party_Last_Update_Date,
1079 X_org_cont_Last_Update_Date,
1080 X_cont_point_Last_Update_Date,
1081 X_prel_Last_Update_Date,
1082 X_rel_party_Last_Update_Date,
1083 X_Status ,
1084 X_Contact_Key ,
1085 X_First_Name ,
1086 X_Job_Title ,
1087 X_Job_Title_Code ,
1088 X_Last_Update_Login ,
1089 X_Mail_Stop ,
1090 X_Title ,
1091 X_Attribute_Category ,
1092 X_Attribute1 ,
1093 X_Attribute2 ,
1094 X_Attribute3 ,
1095 X_Attribute4 ,
1096 X_Attribute5 ,
1097 X_Attribute6 ,
1098 X_Attribute7 ,
1099 X_Attribute8 ,
1100 X_Attribute9 ,
1101 X_Attribute10 ,
1102 X_Attribute11 ,
1103 X_Attribute12 ,
1104 X_Attribute13 ,
1105 X_Attribute14 ,
1106 X_Attribute15 ,
1107 X_Attribute16 ,
1108 X_Attribute17 ,
1109 X_Attribute18 ,
1110 X_Attribute19 ,
1111 X_Attribute20 ,
1112 X_Attribute21 ,
1113 X_Attribute22 ,
1114 X_Attribute23 ,
1115 X_Attribute24 ,
1116 X_Attribute25 ,
1117 X_Email_Address ,
1118 X_Last_Name_Alt ,
1119 X_First_Name_Alt ,
1120 x_contact_number ,
1121 x_party_id ,
1122 x_party_site_id ,
1123 x_contact_party_id ,
1124 x_org_contact_id ,
1125 x_contact_point_id ,
1126 x_cust_account_role_id ,
1127 x_party_relationship_id ,
1128 x_return_status ,
1129 x_msg_count ,
1130 x_msg_data ,
1131 x_rel_party_id ,
1132 x_person_object_version ,
1133 x_org_contact_object_version,
1134 x_rel_object_version ,
1135 x_party_object_version ,
1136 x_contact_pt_object_version ,
1137 x_acct_role_object_version );
1138 END;
1139
1140
1141 PROCEDURE Update_Row(
1142 X_contact_id number,
1143 X_Last_Name VARCHAR2,
1144 X_Last_Updated_By NUMBER,
1145 X_Last_Update_Date in out NOCOPY DATE,
1146 X_party_Last_Update_Date in out NOCOPY DATE,
1147 X_org_cont_Last_Update_Date in out NOCOPY DATE,
1148 X_cont_point_Last_Update_Date in out NOCOPY DATE,
1149 X_prel_Last_Update_Date in out NOCOPY DATE,
1150 X_rel_party_Last_Update_Date in out NOCOPY DATE,
1151 X_Status VARCHAR2,
1152 X_Contact_Key VARCHAR2,
1153 X_First_Name VARCHAR2,
1154 X_Job_Title VARCHAR2,
1155 X_Job_Title_Code VARCHAR2,
1156 X_Last_Update_Login NUMBER,
1157 X_Mail_Stop VARCHAR2,
1158 X_Title VARCHAR2,
1159 X_Attribute_Category VARCHAR2,
1160 X_Attribute1 VARCHAR2,
1161 X_Attribute2 VARCHAR2,
1162 X_Attribute3 VARCHAR2,
1163 X_Attribute4 VARCHAR2,
1164 X_Attribute5 VARCHAR2,
1165 X_Attribute6 VARCHAR2,
1166 X_Attribute7 VARCHAR2,
1167 X_Attribute8 VARCHAR2,
1168 X_Attribute9 VARCHAR2,
1169 X_Attribute10 VARCHAR2,
1170 X_Attribute11 VARCHAR2,
1171 X_Attribute12 VARCHAR2,
1172 X_Attribute13 VARCHAR2,
1173 X_Attribute14 VARCHAR2,
1174 X_Attribute15 VARCHAR2,
1175 X_Attribute16 VARCHAR2,
1176 X_Attribute17 VARCHAR2,
1177 X_Attribute18 VARCHAR2,
1178 X_Attribute19 VARCHAR2,
1179 X_Attribute20 VARCHAR2,
1180 X_Attribute21 VARCHAR2,
1181 X_Attribute22 VARCHAR2,
1182 X_Attribute23 VARCHAR2,
1183 X_Attribute24 VARCHAR2,
1184 X_Attribute25 VARCHAR2,
1185 X_Email_Address VARCHAR2,
1186 X_Last_Name_Alt VARCHAR2 default g_varchar2,
1187 X_First_Name_Alt VARCHAR2 default g_varchar2,
1188 x_contact_number VARCHAR2,
1189 x_party_id number,
1190 x_party_site_id number,
1191 x_contact_party_id number,
1192 x_org_contact_id number,
1193 x_contact_point_id in out NOCOPY number,
1194 x_cust_account_role_id number,
1195 x_party_relationship_id number,
1196 x_return_status out NOCOPY varchar2,
1197 x_msg_count out NOCOPY number,
1198 x_msg_data out NOCOPY varchar2,
1199 x_rel_party_id number default null,
1200 x_person_object_version in out NOCOPY NUMBER,
1201 x_org_contact_object_version in out NOCOPY NUMBER,
1202 x_rel_object_version in out NOCOPY NUMBER,
1203 x_party_object_version in out NOCOPY NUMBER,
1204 x_contact_pt_object_version in out NOCOPY NUMBER,
1205 x_acct_role_object_version in out NOCOPY NUMBER
1206 )
1207 IS
1208 party_rec hz_party_v2pub.party_rec_type;
1209 per_rec hz_party_v2pub.person_rec_type;
1210 prel_rec hz_relationship_v2pub.relationship_rec_type;
1211 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
1212 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
1213 email_rec hz_contact_point_v2pub.email_rec_type;
1214 arole_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
1215
1216 tmp_var VARCHAR2(2000);
1217 i number;
1218 tmp_var1 VARCHAR2(2000);
1219 x_profile_id NUMBER;
1220 i_rel_party_id number;
1221 i_lock_id number;
1222 l_date date;
1223 i_contact_point_id number;
1224 x_cust_contact_point_id number;
1225 l_rowid ROWID;
1226 l_version NUMBER;
1227 l_person_rowid ROWID;
1228 l_person_object_version NUMBER;
1229 l_person_last_update_date DATE;
1230 l_org_contact_rowid ROWID;
1231 l_org_contact_object_version NUMBER;
1232 l_org_contact_last_update_date DATE;
1233 l_rel_rowid ROWID;
1234 l_rel_object_version NUMBER;
1235 l_rel_last_update_date DATE;
1236 l_dummy_id NUMBER;
1237 l_rel_id NUMBER;
1238 l_party_id NUMBER;
1239 l_party_rowid ROWID;
1240 l_party_object_version NUMBER;
1241 l_party_last_update_date DATE;
1242 l_contact_pt_rowid ROWID;
1243 l_contact_pt_object_version NUMBER;
1244 l_contact_pt_last_update_date DATE;
1245 l_acct_role_rowid ROWID;
1246 l_acct_role_object_version NUMBER;
1247 l_acct_role_last_update_date DATE;
1248
1249 BEGIN
1250
1251 --
1252 party_rec.party_id := x_contact_party_id;
1253 party_rec.attribute_category := INIT_SWITCH(x_Attribute_Category);
1254 party_rec.attribute1 := INIT_SWITCH(x_Attribute1);
1258 party_rec.attribute5 := INIT_SWITCH(x_Attribute5);
1255 party_rec.attribute2 := INIT_SWITCH(x_Attribute2);
1256 party_rec.attribute3 := INIT_SWITCH(x_Attribute3);
1257 party_rec.attribute4 := INIT_SWITCH(x_Attribute4);
1259 party_rec.attribute6 := INIT_SWITCH(x_Attribute6);
1260 party_rec.attribute7 := INIT_SWITCH(x_Attribute7);
1261 party_rec.attribute8 := INIT_SWITCH(x_attribute8);
1262 party_rec.attribute9 := INIT_SWITCH(x_Attribute9);
1263 party_rec.attribute10 := INIT_SWITCH(x_Attribute10);
1264 party_rec.attribute11 := INIT_SWITCH(x_Attribute11);
1265 party_rec.attribute12 := INIT_SWITCH(x_Attribute12);
1266 party_rec.attribute13 := INIT_SWITCH(x_Attribute13);
1267 party_rec.attribute14 := INIT_SWITCH(x_Attribute14);
1268 party_rec.attribute15 := INIT_SWITCH(x_Attribute15);
1269 party_rec.attribute16 := INIT_SWITCH(x_Attribute16);
1270 party_rec.attribute17 := INIT_SWITCH(x_Attribute17);
1271 party_rec.attribute18 := INIT_SWITCH(x_Attribute18);
1272 party_rec.attribute19 := INIT_SWITCH(x_Attribute19);
1273 party_rec.attribute20 := INIT_SWITCH(x_Attribute20);
1274
1275 per_rec.party_rec.party_id := x_contact_party_id;
1276 per_rec.person_first_name := INIT_SWITCH(x_first_name);
1277 per_rec.person_last_name := INIT_SWITCH(x_last_name);
1278 /* Bug Fix : 2500275 */
1279 --per_rec.person_title := INIT_SWITCH(x_title);
1280 per_rec.person_pre_name_adjunct := INIT_SWITCH(x_title);
1281
1282 ocon_rec.org_contact_id := x_org_contact_id;
1283 ocon_rec.contact_number := INIT_SWITCH(x_contact_number);
1284 ocon_rec.title := INIT_SWITCH(x_title);
1285 ocon_rec.job_title := INIT_SWITCH(x_job_title);
1286 ocon_rec.job_title_code := INIT_SWITCH(x_job_title_code);
1287 -- ocon_rec.mail_stop := INIT_SWITCH(x_mail_stop);
1288 -- ocon_rec.contact_key := INIT_SWITCH(x_contact_key);
1289 -- ocon_rec.status := nvl(x_status,'I');
1290
1291 /*bug5442145-5330162
1292 ocon_rec.attribute_category := INIT_SWITCH(x_Attribute_Category);
1293 ocon_rec.attribute1 := INIT_SWITCH(x_Attribute1);
1294 ocon_rec.attribute2 := INIT_SWITCH(x_Attribute2);
1295 ocon_rec.attribute3 := INIT_SWITCH(x_Attribute3);
1296 ocon_rec.attribute4 := INIT_SWITCH(x_Attribute4);
1297 ocon_rec.attribute5 := INIT_SWITCH(x_Attribute5);
1298 ocon_rec.attribute6 := INIT_SWITCH(x_Attribute6);
1299 ocon_rec.attribute7 := INIT_SWITCH(x_Attribute7);
1300 ocon_rec.attribute8 := INIT_SWITCH(x_attribute8);
1301 ocon_rec.attribute9 := INIT_SWITCH(x_Attribute9);
1302 ocon_rec.attribute10 := INIT_SWITCH(x_Attribute10);
1303 ocon_rec.attribute11 := INIT_SWITCH(x_Attribute11);
1304 ocon_rec.attribute12 := INIT_SWITCH(x_Attribute12);
1305 ocon_rec.attribute13 := INIT_SWITCH(x_Attribute13);
1306 ocon_rec.attribute14 := INIT_SWITCH(x_Attribute14);
1307 ocon_rec.attribute15 := INIT_SWITCH(x_Attribute15);
1308 ocon_rec.attribute16 := INIT_SWITCH(x_Attribute16);
1309 ocon_rec.attribute17 := INIT_SWITCH(x_Attribute17);
1310 ocon_rec.attribute18 := INIT_SWITCH(x_Attribute18);
1311 ocon_rec.attribute19 := INIT_SWITCH(x_Attribute19);
1312 ocon_rec.attribute20 := INIT_SWITCH(x_Attribute20);
1313 */
1314
1315 cpoint_rec.contact_point_id := x_contact_point_Id;
1316 cpoint_rec.status := nvl(x_status,'I');
1317
1318 email_rec.email_address := INIT_SWITCH(X_Email_Address);
1319
1320 arole_rec.cust_account_role_id := INIT_SWITCH(x_cust_account_role_id);
1321 arole_rec.attribute_category := INIT_SWITCH(x_Attribute_Category);
1322 arole_rec.attribute1 := INIT_SWITCH(x_Attribute1);
1323 arole_rec.attribute2 := INIT_SWITCH(x_Attribute2);
1324 arole_rec.attribute3 := INIT_SWITCH(x_Attribute3);
1325 arole_rec.attribute4 := INIT_SWITCH(x_Attribute4);
1326 arole_rec.attribute5 := INIT_SWITCH(x_Attribute5);
1327 arole_rec.attribute6 := INIT_SWITCH(x_Attribute6);
1328 arole_rec.attribute7 := INIT_SWITCH(x_Attribute7);
1329 arole_rec.attribute8 := INIT_SWITCH(x_attribute8);
1330 arole_rec.attribute9 := INIT_SWITCH(x_Attribute9);
1331 arole_rec.attribute10 := INIT_SWITCH(x_Attribute10);
1332 arole_rec.attribute11 := INIT_SWITCH(x_Attribute11);
1333 arole_rec.attribute12 := INIT_SWITCH(x_Attribute12);
1334 arole_rec.attribute13 := INIT_SWITCH(x_Attribute13);
1335 arole_rec.attribute14 := INIT_SWITCH(x_Attribute14);
1336 arole_rec.attribute15 := INIT_SWITCH(x_Attribute15);
1337 arole_rec.attribute16 := INIT_SWITCH(x_Attribute16);
1338 arole_rec.attribute17 := INIT_SWITCH(x_Attribute17);
1339 arole_rec.attribute18 := INIT_SWITCH(x_Attribute18);
1340 arole_rec.attribute19 := INIT_SWITCH(x_Attribute19);
1341 arole_rec.attribute20 := INIT_SWITCH(x_Attribute20);
1342 -- Bug Fix : 2305458.
1343 arole_rec.status := INIT_SWITCH(x_status);
1344 -- bug 1276469. passed the contactstatus.
1345 -- arole_rec.current_role_state := INIT_SWITCH(x_Status);
1346
1347 l_person_object_version := x_person_object_version;
1348 IF l_person_object_version = -1 THEN
1349 object_version_select(
1350 p_table_name => 'HZ_PARTIES',
1351 p_col_id => x_contact_party_id,
1352 x_rowid => l_person_rowid,
1356 x_return_status => x_return_status,
1353 x_object_version_number => l_person_object_version,
1354 x_last_update_date => l_person_last_update_date,
1355 x_id_value => l_dummy_id,
1357 x_msg_count => x_msg_count,
1358 x_msg_data => x_msg_data );
1359
1360 IF x_msg_count > 1 THEN
1361 FOR i IN 1..x_msg_count LOOP
1362 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1363 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1364 END LOOP;
1365 x_msg_data := tmp_var1;
1366 END IF;
1367
1368 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1369 return;
1370 END IF;
1371
1372 END IF;
1373
1374 HZ_PARTY_V2PUB.update_person (
1375 p_person_rec => per_rec,
1376 p_party_object_version_number => l_person_object_version,
1377 x_profile_id => x_profile_id,
1378 x_return_status => x_return_status,
1379 x_msg_count => x_msg_count,
1380 x_msg_data => x_msg_data
1381 );
1382
1383 IF x_msg_count > 1 THEN
1384 FOR i IN 1..x_msg_count LOOP
1385 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1386 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1387 END LOOP;
1388 x_msg_data := tmp_var1;
1389 END IF;
1390
1391 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1392 return;
1393 END IF;
1394
1395
1396 SELECT last_update_date,
1397 object_version_number
1398 INTO x_party_Last_Update_Date,
1399 x_person_object_version
1400 FROM hz_parties
1401 WHERE party_id = x_contact_party_id;
1402
1403 l_org_contact_object_version := x_org_contact_object_version;
1404 IF l_org_contact_object_version = -1 THEN
1405 object_version_select(
1406 p_table_name => 'HZ_ORG_CONTACTS',
1407 p_col_id => x_org_contact_id,
1408 x_rowid => l_org_contact_rowid,
1409 x_object_version_number => l_org_contact_object_version,
1410 x_last_update_date => l_org_contact_last_update_date,
1411 x_id_value => l_rel_id,
1412 x_return_status => x_return_status,
1413 x_msg_count => x_msg_count,
1414 x_msg_data => x_msg_data );
1415
1416 IF x_msg_count > 1 THEN
1417 FOR i IN 1..x_msg_count LOOP
1418 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1419 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1420 END LOOP;
1421 x_msg_data := tmp_var1;
1422 END IF;
1423
1424 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1425 return;
1426 END IF;
1427
1428 END IF;
1429
1430 --HYU
1431 l_rel_object_version := x_rel_object_version;
1432 IF l_rel_object_version = -1 THEN
1433 object_version_select(
1434 p_table_name => 'HZ_RELATIONSHIPS',
1435 p_col_id => l_rel_id,
1436 x_rowid => l_rel_rowid,
1437 x_object_version_number => l_rel_object_version,
1438 x_last_update_date => l_rel_last_update_date,
1439 x_id_value => l_party_id,
1440 x_return_status => x_return_status,
1441 x_msg_count => x_msg_count,
1442 x_msg_data => x_msg_data );
1443
1444 IF x_msg_count > 1 THEN
1445 FOR i IN 1..x_msg_count LOOP
1446 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1447 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1448 END LOOP;
1449 x_msg_data := tmp_var1;
1450 END IF;
1451
1452 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1453 return;
1454 END IF;
1455 END IF;
1456
1457 l_party_object_version := x_party_object_version;
1458 IF l_party_object_version = -1 THEN
1459 object_version_select(
1460 p_table_name => 'HZ_PARTIES',
1461 p_col_id => l_party_id,
1462 x_rowid => l_party_rowid,
1463 x_object_version_number => l_party_object_version,
1464 x_last_update_date => l_party_last_update_date,
1465 x_id_value => l_dummy_id,
1466 x_return_status => x_return_status,
1467 x_msg_count => x_msg_count,
1468 x_msg_data => x_msg_data );
1469
1470 IF x_msg_count > 1 THEN
1471 FOR i IN 1..x_msg_count LOOP
1472 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1473 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1474 END LOOP;
1475 x_msg_data := tmp_var1;
1476 END IF;
1477
1478 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1479 return;
1480 END IF;
1481
1482 END IF;
1483
1484 IF X_org_cont_Last_Update_Date = FND_API.G_MISS_DATE
1485 OR
1486 X_org_cont_Last_Update_Date IS NULL
1487 THEN
1488 ocon_rec.party_rel_rec.party_rec.party_id := NULL;
1489 ELSE
1490 ocon_rec.party_rel_rec.party_rec.party_id := l_party_id;
1491 END IF;
1492
1493 IF l_rel_last_update_date IS NOT NULL AND
1494 l_rel_last_update_date <> FND_API.G_MISS_DATE
1498 ocon_rec.party_rel_rec.relationship_id := NULL;
1495 THEN
1496 ocon_rec.party_rel_rec.relationship_id := l_rel_id;
1497 ELSE
1499 END IF;
1500
1501 HZ_PARTY_CONTACT_V2PUB.update_org_contact (
1502 p_org_contact_rec => ocon_rec,
1503 p_cont_object_version_number => l_org_contact_object_version,
1504 p_rel_object_version_number => l_rel_object_version,
1505 p_party_object_version_number => l_party_object_version,
1506 x_return_status => x_return_status,
1507 x_msg_count => x_msg_count,
1508 x_msg_data => x_msg_data
1509 );
1510
1511 IF x_msg_count > 1 THEN
1512 FOR i IN 1..x_msg_count LOOP
1513 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1514 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1515 END LOOP;
1516 x_msg_data := tmp_var1;
1517 END IF;
1518
1519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1520 return;
1521 END IF;
1522
1523
1524 --
1525 -- mail_stop is not in V2 hz_party_contact_v2pub.org_contact_rec_type
1526 -- Need to do this additional update in replacement
1527 -- This fix has to be removed if V2 hz_party_contact_v2pub.org_contact_rec_type is
1528 -- changed to support the attribute MAIL_STOP
1529 --
1530 IF (X_Mail_Stop IS NOT NULL)
1531 AND (X_Mail_Stop <> FND_API.G_MISS_CHAR)
1532 THEN
1533 update_mail_stop
1534 ( p_org_contact_id => x_org_contact_id,
1535 p_mail_stop => X_Mail_Stop,
1536 x_return_status => x_return_status,
1537 x_msg_data => x_msg_data);
1538
1539 IF x_msg_count > 1 THEN
1540 FOR i IN 1..x_msg_count LOOP
1541 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1542 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1543 END LOOP;
1544 x_msg_data := tmp_var1;
1545 END IF;
1546
1547 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1548 return;
1549 END IF;
1550
1551 END IF;
1552
1553 select last_update_date,
1554 object_version_number
1555 into X_org_cont_Last_Update_Date,
1556 x_org_contact_object_version
1557 from hz_org_contacts
1558 where org_contact_id = x_org_contact_id;
1559
1560 select last_update_date,
1561 object_version_number
1562 into X_prel_Last_Update_Date,
1563 x_rel_object_version
1564 from hz_relationships
1565 where relationship_id = x_party_relationship_id
1566 AND subject_table_name = 'HZ_PARTIES'
1567 AND object_table_name = 'HZ_PARTIES'
1568 AND directional_flag = 'F';
1569
1570 select party_id
1571 into i_rel_party_id
1572 from hz_relationships
1573 where relationship_id = x_party_relationship_id
1574 AND subject_table_name = 'HZ_PARTIES'
1575 AND object_table_name = 'HZ_PARTIES'
1576 AND directional_flag = 'F';
1577
1578 select last_update_date ,
1579 object_version_number
1580 into X_rel_party_Last_Update_Date ,
1581 x_party_object_version
1582 from hz_parties
1583 where party_id = i_rel_party_id;
1584
1585
1586 /**** the following is added for bug 1276469 ***/
1587 cpoint_rec.contact_point_id := x_contact_point_Id;
1588 cpoint_rec.contact_point_type := 'EMAIL';
1589 cpoint_rec.status := 'A';
1590 /********** Bug Fix Begin#1: 3403289 ***********************/
1591 --bug 4930521 Remove the comments
1592
1593 cpoint_rec.owner_table_name := 'HZ_PARTIES';
1594 cpoint_rec.owner_table_id := x_rel_party_id;
1595
1596 /********** Bug Fix End#1: 3403289 ***********************/
1597 -- the next 1 line is added to make sure the email_address is
1598 -- denormalised into hz_parties. bug - 1276469.
1599 l_date := X_Cont_Point_Last_Update_Date;
1600
1601 IF x_contact_point_Id is not null then
1602 if X_Email_Address is not null then
1603 -- if new email address is passed, update contact_points
1604 -- record and set primary to 'Y' so that it is reflected
1605 -- in hz_parties through denormalization
1606 cpoint_rec.primary_flag := 'Y';
1607 email_rec.email_address := X_Email_Address;
1608 else
1609 -- if email address is set to null from some value
1610 -- then make the contact_points record non-primary.
1611 -- this will set email in hz_parties to null by
1612 -- denormalization process.
1613 select email_address into email_rec.email_address
1614 from hz_contact_points
1615 where contact_point_id = x_contact_point_id;
1616 cpoint_rec.primary_flag := 'N';
1617 end if;
1618
1619 l_contact_pt_object_version := x_contact_pt_object_version;
1620 IF l_contact_pt_object_version = -1 THEN
1621 object_version_select(
1622 p_table_name => 'HZ_CONTACT_POINTS',
1623 p_col_id => x_contact_point_Id,
1624 x_rowid => l_contact_pt_rowid,
1625 x_object_version_number => l_contact_pt_object_version,
1626 x_last_update_date => l_contact_pt_last_update_date,
1627 x_id_value => l_dummy_id,
1628 x_return_status => x_return_status,
1629 x_msg_count => x_msg_count,
1630 x_msg_data => x_msg_data );
1631
1635 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1632 IF x_msg_count > 1 THEN
1633 FOR i IN 1..x_msg_count LOOP
1634 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1636 END LOOP;
1637 x_msg_data := tmp_var1;
1638 END IF;
1639
1640 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1641 return;
1642 END IF;
1643 END IF;
1644
1645 /********** Bug Fix Begin#2: 3403289 **********************/
1646 SELECT owner_table_name,
1647 owner_table_id
1648 INTO cpoint_rec.owner_table_name,
1649 cpoint_rec.owner_table_id
1650 FROM hz_contact_points
1651 WHERE contact_point_id = x_contact_point_Id;
1652 /********** Bug Fix End#2: 3403289 ***********************/
1653
1654 HZ_CONTACT_POINT_V2PUB.update_contact_point (
1655 p_contact_point_rec => cpoint_rec,
1656 p_email_rec => email_rec,
1657 p_object_version_number => l_contact_pt_object_version,
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data
1661 );
1662
1663 IF x_msg_count > 1 THEN
1664 FOR i IN 1..x_msg_count LOOP
1665 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1666 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1667 END LOOP;
1668 x_msg_data := tmp_var1;
1669 END IF;
1670
1671 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1672 return;
1673 END IF;
1674
1675 select last_update_date,
1676 object_version_number
1677 into X_cont_point_last_update_date,
1678 x_contact_pt_object_version
1679 from hz_contact_points
1680 where contact_point_id = x_contact_point_id;
1681
1682
1683 ELSE
1684 -- if contact_point does not exist, create one if non null
1685 -- email address has been passed.
1686 if X_Email_Address IS NOT NULL then
1687 cpoint_rec.primary_flag := 'Y';
1688 email_rec.email_address := X_Email_Address;
1689
1690 select hz_contact_points_s.nextval
1691 into i_contact_point_id
1692 from dual;
1693
1694 cpoint_rec.contact_point_id := i_contact_point_Id;
1695 cpoint_rec.created_by_module := 'TCA_FORM_WRAPPER';
1696
1697 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1698 p_contact_point_rec => cpoint_rec,
1699 p_email_rec => email_rec,
1700 x_contact_point_id => i_contact_point_id,
1701 x_return_status => x_return_status,
1702 x_msg_count => x_msg_count,
1703 x_msg_data => x_msg_data
1704 );
1705
1706
1707 IF x_msg_count > 1 THEN
1708 FOR i IN 1..x_msg_count LOOP
1709 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1710 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1711 END LOOP;
1712 x_msg_data := tmp_var1;
1713 END IF;
1714
1715 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1716 return;
1717 END IF;
1718
1719 x_contact_point_id := i_contact_point_id;
1720 select last_update_date,
1721 object_version_number
1722 into X_cont_point_last_update_date,
1723 x_contact_pt_object_version
1724 from hz_contact_points
1725 where contact_point_id = x_contact_point_id;
1726
1727
1728 end if;
1729
1730 End if;
1731
1732
1733 /*** end of code added for bug - 1276469 ***/
1734
1735 l_acct_role_object_version := x_acct_role_object_version;
1736 IF l_acct_role_object_version = -1 THEN
1737 object_version_select(
1738 p_table_name => 'HZ_CUST_ACCOUNT_ROLES',
1739 p_col_id => X_cust_account_role_id,
1740 x_rowid => l_acct_role_rowid,
1741 x_object_version_number => l_acct_role_object_version,
1742 x_last_update_date => l_acct_role_last_update_date,
1743 x_id_value => l_dummy_id,
1744 x_return_status => x_return_status,
1745 x_msg_count => x_msg_count,
1746 x_msg_data => x_msg_data );
1747
1748 IF x_msg_count > 1 THEN
1749 FOR i IN 1..x_msg_count LOOP
1750 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1751 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1752 END LOOP;
1753 x_msg_data := tmp_var1;
1754 END IF;
1755
1756 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1757 return;
1758 END IF;
1759 END IF;
1760
1761 HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
1762 p_cust_account_role_rec => arole_rec,
1763 p_object_version_number => l_acct_role_object_version,
1764 x_return_status => x_return_status,
1765 x_msg_count => x_msg_count,
1766 x_msg_data => x_msg_data
1767 );
1768
1769 IF x_msg_count > 1 THEN
1770 FOR i IN 1..x_msg_count LOOP
1771 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1772 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1773 END LOOP;
1774 x_msg_data := tmp_var1;
1775 END IF;
1776
1777 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1778 return;
1779 END IF;
1780
1781 select cust_account_role_id
1782 into i_lock_id
1783 from hz_cust_account_roles
1784 where cust_account_role_id = x_cust_account_role_id
1785 for update of cust_account_role_id nowait;
1786
1787 update hz_cust_account_roles
1788 set attribute21 = X_Attribute21,
1789 attribute22 = X_Attribute22,
1790 attribute23 = X_Attribute23,
1791 attribute24 = X_Attribute24,
1792 attribute25 = X_Attribute25
1793 where cust_account_role_id = X_contact_id ;
1794
1795 select last_update_date,
1796 object_version_number
1797 into X_Last_Update_Date,
1798 x_acct_role_object_version
1799 from hz_cust_account_roles
1800 where cust_account_role_id = X_contact_id;
1801
1802
1803 END Update_Row;
1804
1805
1806 --{BUG#4064156
1807 PROCEDURE compare_existing_contact_info
1808 ( p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type,
1809 x_update_required IN OUT NOCOPY VARCHAR2,
1810 x_relationship_id IN OUT NOCOPY NUMBER,
1811 x_ocon_version_number IN OUT NOCOPY NUMBER )
1812 IS
1813 CURSOR c IS
1814 SELECT contact_number,
1815 title ,
1816 job_title ,
1817 job_title_code,
1818 party_site_id ,
1819 orig_system_reference,
1820 attribute_category ,
1821 attribute1 ,
1822 attribute2 ,
1823 attribute3 ,
1824 attribute4 ,
1825 attribute5 ,
1826 attribute6 ,
1827 attribute7 ,
1828 attribute8 ,
1829 attribute9 ,
1830 attribute10,
1831 attribute11,
1832 attribute12,
1833 attribute13,
1834 attribute14,
1835 attribute15,
1836 attribute16,
1837 attribute17,
1838 attribute18,
1839 attribute19,
1840 attribute20,
1841 object_version_number,
1842 party_relationship_id
1843 FROM hz_org_contacts
1844 WHERE org_contact_id = p_ocon_rec.org_contact_id;
1845 l_ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
1846 BEGIN
1847 x_update_required := 'N';
1848 IF p_ocon_rec.org_contact_id IS NOT NULL THEN
1849 OPEN c;
1850 FETCH c INTO
1851 l_ocon_rec.contact_number,
1852 l_ocon_rec.title ,
1853 l_ocon_rec.job_title ,
1854 l_ocon_rec.job_title_code,
1855 l_ocon_rec.party_site_id ,
1856 l_ocon_rec.orig_system_reference,
1857 l_ocon_rec.attribute_category ,
1858 l_ocon_rec.attribute1 ,
1859 l_ocon_rec.attribute2 ,
1860 l_ocon_rec.attribute3 ,
1861 l_ocon_rec.attribute4 ,
1862 l_ocon_rec.attribute5 ,
1863 l_ocon_rec.attribute6 ,
1864 l_ocon_rec.attribute7 ,
1865 l_ocon_rec.attribute8 ,
1866 l_ocon_rec.attribute9 ,
1867 l_ocon_rec.attribute10 ,
1868 l_ocon_rec.attribute11 ,
1869 l_ocon_rec.attribute12 ,
1870 l_ocon_rec.attribute13 ,
1871 l_ocon_rec.attribute14 ,
1872 l_ocon_rec.attribute15 ,
1873 l_ocon_rec.attribute16 ,
1874 l_ocon_rec.attribute17 ,
1875 l_ocon_rec.attribute18 ,
1876 l_ocon_rec.attribute19 ,
1877 l_ocon_rec.attribute20 ,
1878 x_ocon_version_number,
1879 x_relationship_id;
1880 IF c%NOTFOUND THEN
1881 RAISE NO_DATA_FOUND;
1882 END IF;
1883 CLOSE c;
1884
1885 IF NVL(l_ocon_rec.contact_number,fnd_api.g_miss_char)
1886 <> NVL(p_ocon_rec.contact_number,fnd_api.g_miss_char) THEN
1887 x_update_required := 'Y';
1888 ELSIF
1889 NVL(l_ocon_rec.title ,fnd_api.g_miss_char)
1890 <> NVL(p_ocon_rec.title ,fnd_api.g_miss_char) THEN
1891 x_update_required := 'Y';
1892 ELSIF
1893 NVL(l_ocon_rec.job_title ,fnd_api.g_miss_char)
1894 <> NVL(p_ocon_rec.job_title ,fnd_api.g_miss_char) THEN
1895 x_update_required := 'Y';
1896 ELSIF
1897 NVL(l_ocon_rec.job_title_code,fnd_api.g_miss_char)
1898 <> NVL(p_ocon_rec.job_title_code,fnd_api.g_miss_char) THEN
1899 x_update_required := 'Y';
1900 ELSIF
1901 NVL(l_ocon_rec.party_site_id ,fnd_api.g_miss_num)
1902 <> NVL(p_ocon_rec.party_site_id ,fnd_api.g_miss_num) THEN
1903 x_update_required := 'Y';
1904 ELSIF
1905 NVL(l_ocon_rec.orig_system_reference,fnd_api.g_miss_char)
1906 <> NVL(p_ocon_rec.orig_system_reference,fnd_api.g_miss_char) THEN
1907 x_update_required := 'Y';
1908 ELSIF
1909 NVL(l_ocon_rec.attribute_category ,fnd_api.g_miss_char)
1910 <> NVL(p_ocon_rec.attribute_category ,fnd_api.g_miss_char) THEN
1911 x_update_required := 'Y';
1912 ELSIF
1913 NVL(l_ocon_rec.attribute1 ,fnd_api.g_miss_char)
1914 <> NVL(p_ocon_rec.attribute1 ,fnd_api.g_miss_char) THEN
1915 x_update_required := 'Y';
1916 ELSIF
1917 NVL(l_ocon_rec.attribute2 ,fnd_api.g_miss_char)
1918 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1919 x_update_required := 'Y';
1920 ELSIF
1921 NVL(l_ocon_rec.attribute3 ,fnd_api.g_miss_char)
1922 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1923 x_update_required := 'Y';
1924 ELSIF
1925 NVL(l_ocon_rec.attribute4 ,fnd_api.g_miss_char)
1926 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1927 x_update_required := 'Y';
1928 ELSIF
1929 NVL(l_ocon_rec.attribute5 ,fnd_api.g_miss_char)
1930 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1931 x_update_required := 'Y';
1932 ELSIF
1933 NVL(l_ocon_rec.attribute6 ,fnd_api.g_miss_char)
1934 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1935 x_update_required := 'Y';
1936 ELSIF
1937 NVL(l_ocon_rec.attribute7 ,fnd_api.g_miss_char)
1938 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1939 x_update_required := 'Y';
1940 ELSIF
1941 NVL(l_ocon_rec.attribute8 ,fnd_api.g_miss_char)
1942 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1943 x_update_required := 'Y';
1944 ELSIF
1945 NVL(l_ocon_rec.attribute9 ,fnd_api.g_miss_char)
1946 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1947 x_update_required := 'Y';
1948 ELSIF
1949 NVL(l_ocon_rec.attribute10 ,fnd_api.g_miss_char)
1950 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1951 x_update_required := 'Y';
1952 ELSIF
1953 NVL(l_ocon_rec.attribute11 ,fnd_api.g_miss_char)
1954 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1955 x_update_required := 'Y';
1956 ELSIF
1957 NVL(l_ocon_rec.attribute12 ,fnd_api.g_miss_char)
1958 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1959 x_update_required := 'Y';
1960 ELSIF
1961 NVL(l_ocon_rec.attribute13 ,fnd_api.g_miss_char)
1962 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1963 x_update_required := 'Y';
1964 ELSIF
1965 NVL(l_ocon_rec.attribute14 ,fnd_api.g_miss_char)
1966 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1967 x_update_required := 'Y';
1968 ELSIF
1969 NVL(l_ocon_rec.attribute15 ,fnd_api.g_miss_char)
1970 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1971 x_update_required := 'Y';
1972 ELSIF
1973 NVL(l_ocon_rec.attribute16 ,fnd_api.g_miss_char)
1974 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1975 x_update_required := 'Y';
1976 ELSIF
1977 NVL(l_ocon_rec.attribute17 ,fnd_api.g_miss_char)
1978 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1979 x_update_required := 'Y';
1980 ELSIF
1981 NVL(l_ocon_rec.attribute18 ,fnd_api.g_miss_char)
1982 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1983 x_update_required := 'Y';
1984 ELSIF
1985 NVL(l_ocon_rec.attribute19 ,fnd_api.g_miss_char)
1986 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1987 x_update_required := 'Y';
1988 ELSIF
1989 NVL(l_ocon_rec.attribute20 ,fnd_api.g_miss_char)
1990 <> NVL(p_ocon_rec.attribute2 ,fnd_api.g_miss_char) THEN
1991 x_update_required := 'Y';
1992 END IF;
1993 END IF;
1994 EXCEPTION
1995 WHEN NO_DATA_FOUND THEN NULL;
1996 END;
1997
1998 PROCEDURE upd_ocon_update
1999 (p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type)
2000 IS
2001 BEGIN
2002 UPDATE hz_org_contacts
2003 SET contact_number = p_ocon_rec.contact_number,
2004 title = p_ocon_rec.title,
2005 job_title = p_ocon_rec.job_title,
2006 job_title_code = p_ocon_rec.job_title_code,
2007 attribute_category = p_ocon_rec.attribute_category,
2008 attribute1 = p_ocon_rec.attribute1,
2009 attribute2 = p_ocon_rec.attribute2,
2010 attribute3 = p_ocon_rec.attribute3,
2011 attribute4 = p_ocon_rec.attribute4,
2012 attribute5 = p_ocon_rec.attribute5,
2013 attribute6 = p_ocon_rec.attribute6,
2014 attribute7 = p_ocon_rec.attribute7,
2015 attribute8 = p_ocon_rec.attribute8,
2016 attribute9 = p_ocon_rec.attribute9,
2017 attribute10 = p_ocon_rec.attribute10,
2018 attribute11 = p_ocon_rec.attribute11,
2019 attribute12 = p_ocon_rec.attribute12,
2020 attribute13 = p_ocon_rec.attribute13,
2021 attribute14 = p_ocon_rec.attribute14,
2022 attribute15 = p_ocon_rec.attribute15,
2023 attribute16 = p_ocon_rec.attribute16,
2024 attribute17 = p_ocon_rec.attribute17,
2025 attribute18 = p_ocon_rec.attribute18,
2026 attribute19 = p_ocon_rec.attribute18,
2027 attribute20 = p_ocon_rec.attribute20,
2028 object_version_number= object_version_number + 1
2029 WHERE org_contact_id = p_ocon_rec.org_contact_id;
2030 EXCEPTION
2031 WHEN NO_DATA_FOUND THEN NULL;
2032 END;
2033
2034 --}
2035
2036 END arh_cont_pkg;