[Home] [Help]
PACKAGE BODY: APPS.IGS_OR_CONTACTS_PKG
Source
1 PACKAGE BODY IGS_OR_CONTACTS_PKG AS
2 /* $Header: IGSOI24B.pls 120.4 2006/05/30 09:10:52 vskumar ship $ */
3 /* Change History
4 | who when what
5
6 | npalanis 15-feb-2002 Bug ID - 2225917 : SWCR008 Removed parameters
7 | Customer_Id ,Address_Id,Contact_Id, in
8 | Insert_row and cust_acct_role_id is removed
9 | from Update Row Procedure .Org_contact_role_id is added
10 | in both insert_row and update_row .Procedure to create
11 | org_contact_role is created .Cursor to get last_update_date
12 | from hz_org_contact_roles table is written . Call To HZ_CUSTOMER_ACCOUTN.CREATE_ACCT_ROLES
13 | is removed .Sequences HZ_CUST_ACCT_ROLES_S and HZ_CUST_CONTACT_POINTS are removed
14 | pkpatel 03-APR-2002 Bug ID - 2283145 : Added the Code for Generating And Passing the party number
15 | if the profile value is set to 'No' for generating the party number
16 | ssawhney 7-may Bug 2338473 -- allow for more than one HZ error to appear.
17 | kpadiyar 15-May-2002 Bug 2373159 -- OSSIMP15: ERROR MESSAGE FOR DUPLICTED INSTITUTION CONTACT NOT INFORMATIVE.
18 | kpadiyar 19-May-2002 Bug 2373159 -- Code changed to return the party number instead of the party id - Review comments.
19
20 gmuralid 25-NOV-2002 BUG 2466674 -- V2 API UPTAKE CHANGED REFERENCE OF HZ_CONTACT_POINT_PUB TO
21 HZ_CONTACT_POINT_V2PUB FOR BOTH CREATE AND UPDATE CONTACT POINTS THROUGHOUT
22 THE PACKAGE BODY.
23 | masehgal 26-Nov-2002 TCA_V2API_uptake corrected as per V2
24 ssawhney 30-apr-2003 V2API OVN changes
25 pkpatel 4-Oct-2005 Bug 4654735 (Replaced the reference of HZ_PARTY_RELATIONSHIPS with HZ_RELATIONSHIPS)
26 */
27
28 g_email CONSTANT VARCHAR2(10) := 'EMAIL';
29 g_status CONSTANT VARCHAR2(1) := 'A';
30 g_own_tab_name CONSTANT VARCHAR2(10) := 'HZ_PARTIES';
31 g_contact_of CONSTANT VARCHAR2(15) := 'CONTACT_OF';
32 g_contact CONSTANT VARCHAR2(20) := 'CONTACT';
33
34 FUNCTION get_error_msg(p_msg_cnt IN NUMBER) RETURN VARCHAR2 AS
35 l_count NUMBER(3);
36 l_cnt NUMBER(3);
37 l_message VARCHAR2(2000);
38 l_var VARCHAR2(2000);
39 /******************************************************************
40
41 Created By: Amit Gairola
42
43 Date Created By: 01-AUG-2001
44
45 Purpose: This function returns the message from the message stack
46 and concatenates all the messages
47
48 Known limitations,enhancements,remarks:
49
50 Change History
51
52 Who When What
53 kpadiyar 15-May-2002 Added the message index parameter in the FND_MSG_PUB call
54 and the if condition logic to check the no of msgs in stack
55 and give the right output.
56 ***************************************************************** */
57 BEGIN
58 IF p_msg_cnt = 1 THEN
59 l_message := FND_MSG_PUB.Get(p_msg_index => p_msg_cnt , p_encoded => FND_API.G_FALSE);
60 ELSE
61 FOR l_cnt IN 1..p_msg_cnt LOOP
62 l_var := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
63 l_message := l_message||' '||l_var;
64 END LOOP;
65 END IF;
66
67 RETURN (l_message);
68 END get_error_msg;
69
70 PROCEDURE Insert_Row( x_last_name VARCHAR2 ,
71 x_orig_system_reference IN OUT NOCOPY VARCHAR2 ,
72 x_status VARCHAR2 ,
73 x_contact_key VARCHAR2 ,
74 x_first_name VARCHAR2 ,
75 x_job_title VARCHAR2 ,
76 x_mail_stop VARCHAR2 ,
77 x_title VARCHAR2 ,
78 x_attribute_category VARCHAR2 ,
79 x_attribute1 VARCHAR2 ,
80 x_attribute2 VARCHAR2 ,
81 x_attribute3 VARCHAR2 ,
82 x_attribute4 VARCHAR2 ,
83 x_attribute5 VARCHAR2 ,
84 x_attribute6 VARCHAR2 ,
85 x_attribute7 VARCHAR2 ,
86 x_attribute8 VARCHAR2 ,
87 x_attribute9 VARCHAR2 ,
88 x_attribute10 VARCHAR2 ,
89 x_attribute11 VARCHAR2 ,
90 x_attribute12 VARCHAR2 ,
91 x_attribute13 VARCHAR2 ,
92 x_attribute14 VARCHAR2 ,
93 x_attribute15 VARCHAR2 ,
94 x_attribute16 VARCHAR2 ,
95 x_attribute17 VARCHAR2 ,
96 x_attribute18 VARCHAR2 ,
97 x_attribute19 VARCHAR2 ,
98 x_attribute20 VARCHAR2 ,
99 x_attribute21 VARCHAR2 ,
100 x_attribute22 VARCHAR2 ,
101 x_attribute23 VARCHAR2 ,
102 x_attribute24 VARCHAR2 ,
103 x_attribute25 VARCHAR2 ,
104 x_email_address VARCHAR2 ,
105 x_last_name_alt VARCHAR2 ,
106 x_first_name_alt VARCHAR2 ,
107 x_contact_number IN OUT NOCOPY VARCHAR2 ,
108 x_party_id NUMBER ,
109 x_party_site_id NUMBER ,
110 x_contact_party_id IN OUT NOCOPY NUMBER ,
111 x_org_contact_id IN OUT NOCOPY NUMBER ,
112 x_contact_point_id IN OUT NOCOPY NUMBER ,
113 x_rel_party_id IN OUT NOCOPY NUMBER ,
114 x_created_by NUMBER ,
115 x_creation_date DATE ,
116 x_updated_by NUMBER ,
117 x_update_date DATE ,
118 x_last_update_login NUMBER ,
119 x_return_status OUT NOCOPY VARCHAR2 ,
120 x_msg_count OUT NOCOPY NUMBER ,
121 x_msg_data OUT NOCOPY VARCHAR2 ,
122 x_org_contact_role_id IN OUT NOCOPY NUMBER,
123 P_ORG_ROLE_OVN IN OUT NOCOPY NUMBER,
124 P_REL_OVN IN OUT NOCOPY NUMBER,
125 P_REL_PARTY_OVN IN OUT NOCOPY NUMBER,
126 P_ORG_CONT_OVN IN OUT NOCOPY NUMBER,
127 P_CONTACT_POINT_OVN IN OUT NOCOPY NUMBER
128 ) AS
129 /******************************************************************
130
131 Created By: Amit Gairola
132
133 Date Created By: 01-AUG-2001
134
135 Purpose: This procedure creates the records in the Org Contacts
136 Creates the Customer Contact Points and the Cust Account
137 Roles
138
139 Known limitations,enhancements,remarks:
140
141 Change History
142
143 Who When What
144 ssawhney bug 2338473. logic for more than one error modified.
145 kpadiyar 15-May-2002 Added the call to the local get_error_msg function to get the correct message
146 kpadiyar 19-May-2002 Added local function get_party_number to returnt the party_number instead of party_id.- Review comments
147 kumma 07-JUN-2002 Commented out NOCOPY the call to fnd_msg_pub.add as a call to igs_ge_msg_stact.add is also present
148 vskumar 24-May-2006 xbuild3 performace fix. changed first select statment related to table hz_relationships.
149 ***************************************************************** */
150 -- masehgal changed to hz_xxxx_V2PUB
151 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
152 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
153 email_rec hz_contact_point_v2pub.email_rec_type;
154 l_party_rec hz_party_v2pub.party_rec_type;
155 org_rel_rec hz_relationship_v2pub.relationship_rec_type;
156
157 l_var VARCHAR2(1);
158 l_party_number hz_parties.party_number%TYPE;
159 l_party_relationship_id hz_relationships.relationship_id%TYPE;
160 l_party_id hz_parties.party_id%TYPE;
161 l_contact_point_id NUMBER(10);
162
163 tmp_var1 VARCHAR2(2000);
164 tmp_var VARCHAR2(2000);
165
166 lv_party_number hz_parties.party_number%TYPE;
167 lv_contact_party_number hz_parties.party_number%TYPE;
168
169 -- Cursor for checking if the record exists in the
170 -- IGS_OR_CONTACTS_V for the Org Party Id and the
171 -- Contact Party Id
172 CURSOR cur_or_cntcts(cp_org_party_id NUMBER,
173 cp_contact_party_id NUMBER) IS
174 SELECT 1
175 FROM hz_relationships rel
176 WHERE object_id = cp_org_party_id AND
177 subject_id = cp_contact_party_id AND
178 REL.SUBJECT_TYPE = 'PERSON' AND
179 REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND
180 REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND
181 REL.DIRECTIONAL_FLAG = 'F' AND
182 REL.RELATIONSHIP_TYPE = 'CONTACT';
183
184
185 FUNCTION get_party_number ( p_party_id number)
186 RETURN VARCHAR2 IS
187 CURSOR c_party IS
188 SELECT party_number
189 FROM hz_parties
190 WHERE party_id = p_party_id;
191
192 lv_party_number VARCHAR2(30);
193
194 BEGIN
195 OPEN c_party;
196 FETCH c_party INTO lv_party_number;
197 RETURN lv_party_number;
198 CLOSE c_party;
199 END;
200
201 BEGIN
202
203 -- Open the cursor for checking if the
204 -- record already exists
205 OPEN cur_or_cntcts(x_party_id, x_contact_party_id);
206 FETCH cur_or_cntcts INTO l_var;
207 IF cur_or_cntcts%FOUND THEN
208 -- If the record already exists, then the
209 -- Error has to be returned to the form
210 CLOSE cur_or_cntcts;
211 x_return_status := 'E';
212
213 /* To get the party number for the party id to pass to the message */
214 lv_party_number := get_party_number(x_party_id);
215 lv_contact_party_number := get_party_number(x_contact_party_id);
216
217 FND_MESSAGE.Set_Name('IGS','IGS_OR_REC_EXISTS');
218 FND_MESSAGE.Set_Token('ORG_PARTY_ID',lv_party_number);
219 FND_MESSAGE.Set_Token('CONT_PARTY_ID',lv_contact_party_number);
220
221 -- FND_MSG_PUB.Add;
222 x_msg_count :=1;
223 IGS_GE_MSG_STACK.ADD;
224 APP_EXCEPTION.RAISE_EXCEPTION;
225
226 END IF;
227 CLOSE cur_or_cntcts;
228
229 SELECT hz_contact_points_s.NEXTVAL
230 INTO l_contact_point_id
231 FROM DUAL;
232
233 -- Assign the values in the Contacts Record
234 org_rel_rec.subject_id := x_contact_party_id;
235 org_rel_rec.object_id := x_party_id;
236 org_rel_rec.relationship_type := g_contact;
237 org_rel_rec.start_date := TRUNC(SYSDATE);
238 org_rel_rec.created_by_module := 'IGS';
239 org_rel_rec.STATUS := x_status;
240 ocon_rec.contact_number := x_contact_number;
241 ocon_rec.title := x_title;
242 ocon_rec.job_title := x_job_title;
243 ocon_rec.party_site_id := x_party_site_id;
244 ocon_rec.orig_system_reference := x_orig_system_reference;
245 ocon_rec.created_by_module := 'IGS';
246 ocon_rec.attribute_category := x_attribute_category;
247 ocon_rec.attribute1 := x_attribute1;
248 ocon_rec.attribute2 := x_attribute2;
249 ocon_rec.attribute3 := x_attribute3;
250 ocon_rec.attribute4 := x_attribute4;
251 ocon_rec.attribute5 := x_attribute5;
252 ocon_rec.attribute6 := x_attribute6;
253 ocon_rec.attribute7 := x_attribute7;
254 ocon_rec.attribute8 := x_attribute8;
255 ocon_rec.attribute9 := x_attribute9;
256 ocon_rec.attribute10 := x_attribute10;
257 ocon_rec.attribute11 := x_attribute11;
258 ocon_rec.attribute12 := x_attribute12;
259 ocon_rec.attribute13 := x_attribute13;
260 ocon_rec.attribute14 := x_attribute14;
261 ocon_rec.attribute15 := x_attribute15;
262 ocon_rec.attribute16 := x_attribute16;
263 ocon_rec.attribute17 := x_attribute17;
264 ocon_rec.attribute18 := x_attribute18;
265 ocon_rec.attribute19 := x_attribute19;
266 ocon_rec.attribute20 := x_attribute20;
267 org_rel_rec.subject_type := 'PERSON';
268 org_rel_rec.subject_table_name := 'HZ_PARTIES';
269 org_rel_rec.object_type := 'ORGANIZATION';
270 org_rel_rec.object_table_name := 'HZ_PARTIES';
271 org_rel_rec.relationship_code := g_contact_of;
272
273 -- Generating And Passing the party number if the profile value is set to 'No' for generating the party number
274 IF FND_PROFILE.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
275 SELECT hz_party_number_s.NEXTVAL
276 INTO l_party_rec.party_number
277 FROM dual;
278
279 org_rel_rec.party_rec.party_number := l_party_rec.party_number;
280
281 END IF;
282 ocon_rec.party_rel_rec := org_rel_rec;
283
284 -- masehgal changed Call to V2PUB for the Creation of the Org Contacts
285 hz_party_contact_v2pub.create_org_contact(
286 p_init_msg_list => 'T',
287 p_org_contact_rec => ocon_rec,
288 x_return_status => x_return_status,
289 x_msg_count => x_msg_count,
290 x_msg_data => x_msg_data,
291 x_org_contact_id => x_org_contact_id,
292 x_party_rel_id => l_party_relationship_id,
293 x_party_id => l_party_id,
294 x_party_number => l_party_number
295 );
296
297 -- If the return status is not 'S' from the API Call
298 -- then exit from the procedure
299 IF x_return_status <> 'S' THEN
300 -- bug 2338473 logic to display more than one error modified.
301
302 IF x_msg_count > 1 THEN
303 FOR i IN 1..x_msg_count LOOP
304 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
305 tmp_var1 := tmp_var1 || ' '|| tmp_var;
306 END LOOP;
307 x_msg_data := tmp_var1;
308 END IF;
309 RETURN;
310
311 END IF;
312
313 -- If the return status is 'S' i.e. not in 'E' or 'U'
314 IF x_return_status NOT IN ('E','U') THEN
315
316 -- Assign the values to the Contact Points Record
317 x_rel_party_id := l_party_id;
318 cpoint_rec.contact_point_id := l_contact_point_id;
319 cpoint_rec.contact_point_type := g_email;
320 cpoint_rec.status := g_status;
321 cpoint_rec.owner_table_name := g_own_tab_name;
322 cpoint_rec.owner_table_id := l_party_id;
323 cpoint_rec.primary_flag := 'Y';
324 cpoint_rec.created_by_module := 'IGS';
325 cpoint_rec.content_source_type := 'USER_ENTERED';
326 email_rec.email_address := x_email_address;
327
328 -- If the Email Address is provided, then create the Contact Point
329 IF x_email_address IS NOT NULL THEN
330 hz_contact_point_v2pub.create_contact_point(
331 p_init_msg_list => FND_API.G_FALSE,
332 p_contact_point_rec => cpoint_rec ,
333 p_edi_rec => NULL,
334 p_email_rec => email_rec,
335 p_phone_rec => NULL,
336 p_telex_rec => NULL,
337 p_web_rec => NULL,
338 x_contact_point_id => x_contact_point_id,
339 x_return_status => x_return_status ,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data );
342
343 IF x_return_status <> 'S' THEN
344 -- 2338473.
345 IF x_msg_count > 1 THEN
346 -- initialise the variables again
347 tmp_var := NULL;
348 tmp_var1 := NULL;
349
350 FOR i IN 1..x_msg_count LOOP
351 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
352 tmp_var1 := tmp_var1 || ' '|| tmp_var;
353 END LOOP;
354 x_msg_data := tmp_var1;
355 END IF;
356 RETURN;
357 END IF;
358 END IF;
359 END IF;
360
361 -- All inserts are successful.
362
363 P_REL_OVN :=1;
364 P_REL_PARTY_OVN :=1;
365 P_ORG_CONT_OVN :=1;
366 P_CONTACT_POINT_OVN :=1;
367
368 END insert_row;
369
370 PROCEDURE Update_Row(x_Last_Name VARCHAR2,
371 x_Last_Updated_By NUMBER,
372 x_Last_Update_Date IN OUT NOCOPY DATE,
373 x_party_Last_Update_Date IN OUT NOCOPY DATE,
374 x_org_cont_Last_Update_Date IN OUT NOCOPY DATE,
375 x_cont_point_Last_Update_Date IN OUT NOCOPY DATE,
376 x_prel_Last_Update_Date IN OUT NOCOPY DATE,
377 x_rel_party_Last_Update_Date IN OUT NOCOPY DATE,
378 x_Status VARCHAR2,
379 x_Contact_Key VARCHAR2,
380 x_First_Name VARCHAR2,
381 x_Job_Title VARCHAR2,
382 x_Last_Update_Login NUMBER,
383 x_Mail_Stop VARCHAR2,
384 x_Title VARCHAR2,
385 x_Attribute_Category VARCHAR2,
386 x_Attribute1 VARCHAR2,
387 x_Attribute2 VARCHAR2,
388 x_Attribute3 VARCHAR2,
389 x_Attribute4 VARCHAR2,
390 x_Attribute5 VARCHAR2,
391 x_Attribute6 VARCHAR2,
392 x_Attribute7 VARCHAR2,
393 x_Attribute8 VARCHAR2,
394 x_Attribute9 VARCHAR2,
395 x_Attribute10 VARCHAR2,
396 x_Attribute11 VARCHAR2,
397 x_Attribute12 VARCHAR2,
398 x_Attribute13 VARCHAR2,
399 x_Attribute14 VARCHAR2,
400 x_Attribute15 VARCHAR2,
401 x_Attribute16 VARCHAR2,
402 x_Attribute17 VARCHAR2,
403 x_Attribute18 VARCHAR2,
404 x_Attribute19 VARCHAR2,
405 x_Attribute20 VARCHAR2,
406 x_Attribute21 VARCHAR2,
407 x_Attribute22 VARCHAR2,
408 x_Attribute23 VARCHAR2,
409 x_Attribute24 VARCHAR2,
410 x_Attribute25 VARCHAR2,
411 x_Email_Address VARCHAR2,
412 x_Last_Name_Alt VARCHAR2 ,
413 x_First_Name_Alt VARCHAR2 ,
414 x_contact_number VARCHAR2,
415 x_party_id NUMBER,
416 x_party_site_id NUMBER,
417 x_contact_party_id NUMBER,
418 x_org_contact_id NUMBER,
419 x_contact_point_id IN OUT NOCOPY NUMBER,
420 x_party_relationship_id NUMBER,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2,
424 x_rel_party_id NUMBER ,
425 x_org_contact_role_id IN OUT NOCOPY NUMBER,
426 P_ORG_ROLE_OVN IN OUT NOCOPY NUMBER,
427 P_REL_OVN IN OUT NOCOPY NUMBER,
428 P_REL_PARTY_OVN IN OUT NOCOPY NUMBER,
429 P_ORG_CONT_OVN IN OUT NOCOPY NUMBER,
430 P_CONTACT_POINT_OVN IN OUT NOCOPY NUMBER
431 ) AS
432 /******************************************************************
433
434 Created By: Amit Gairola
435
436 Date Created By: 01-AUG-2001
437
438 Purpose: This procedure Updates the records in the Org Contacts
439 Updates the Customer Contact Points
440
441 Known limitations,enhancements,remarks:
442
443 Change History
444
445 Who When What
446 ssawhney 2338473 -- to display all message if mroe than one error
447 ***************************************************************** */
448 ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
449 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
450 email_rec hz_contact_point_v2pub.email_rec_type;
451 org_rel_rec hz_relationship_v2pub.relationship_rec_type;
452 l_party_id hz_parties.party_id%TYPE;
453 l_last_update_date DATE;
454 l_contact_point_id NUMBER(20);
455 tmp_var1 VARCHAR2(2000);
456 tmp_var VARCHAR2(2000);
457
458 -- Cursor for the email address from the HZ_CONTACT_POINTS
459 CURSOR cur_hz_contact_pts(cp_contact_point_id NUMBER) IS
460 SELECT email_address
461 FROM hz_contact_points
462 WHERE contact_point_id = cp_contact_point_id;
463
464 l_obj_ver hz_contact_points.object_version_number%TYPE;
465 l_org_contacts_obj_ver_num hz_org_contacts.object_version_number%TYPE;
466 l_rel_obj_ver_num hz_org_contacts.object_version_number%TYPE;
467 l_hz_parties_obj_ver_num hz_org_contacts.object_version_number%TYPE;
468
469 BEGIN
470
471 -- Assign the values to the Org Contact Record
472 ocon_rec.org_contact_id := x_org_contact_id;
473 ocon_rec.contact_number := NVL(x_contact_number,FND_API.G_MISS_CHAR);
474 ocon_rec.title := NVL(x_title,FND_API.G_MISS_CHAR);
475 ocon_rec.job_title := NVL(x_job_title,FND_API.G_MISS_CHAR);
476 ocon_rec.attribute_category := NVL(x_Attribute_Category,FND_API.G_MISS_CHAR);
477 ocon_rec.attribute1 := NVL(x_Attribute1,FND_API.G_MISS_CHAR);
478 ocon_rec.attribute2 := NVL(x_Attribute2,FND_API.G_MISS_CHAR);
479 ocon_rec.attribute3 := NVL(x_Attribute3,FND_API.G_MISS_CHAR);
480 ocon_rec.attribute4 := NVL(x_Attribute4,FND_API.G_MISS_CHAR);
481 ocon_rec.attribute5 := NVL(x_Attribute5,FND_API.G_MISS_CHAR);
482 ocon_rec.attribute6 := NVL(x_Attribute6,FND_API.G_MISS_CHAR);
483 ocon_rec.attribute7 := NVL(x_Attribute7,FND_API.G_MISS_CHAR);
484 ocon_rec.attribute8 := NVL(x_attribute8,FND_API.G_MISS_CHAR);
485 ocon_rec.attribute9 := NVL(x_Attribute9,FND_API.G_MISS_CHAR);
486 ocon_rec.attribute10 := NVL(x_Attribute10,FND_API.G_MISS_CHAR);
487 ocon_rec.attribute11 := NVL(x_Attribute11,FND_API.G_MISS_CHAR);
488 ocon_rec.attribute12 := NVL(x_Attribute12,FND_API.G_MISS_CHAR);
489 ocon_rec.attribute13 := NVL(x_Attribute13,FND_API.G_MISS_CHAR);
490 ocon_rec.attribute14 := NVL(x_Attribute14,FND_API.G_MISS_CHAR);
491 ocon_rec.attribute15 := NVL(x_Attribute15,FND_API.G_MISS_CHAR);
492 ocon_rec.attribute16 := NVL(x_Attribute16,FND_API.G_MISS_CHAR);
493 ocon_rec.attribute17 := NVL(x_Attribute17,FND_API.G_MISS_CHAR);
494 ocon_rec.attribute18 := NVL(x_Attribute18,FND_API.G_MISS_CHAR);
495 ocon_rec.attribute19 := NVL(x_Attribute19,FND_API.G_MISS_CHAR);
496 ocon_rec.attribute20 := NVL(x_Attribute20,FND_API.G_MISS_CHAR);
497
498 org_rel_rec.subject_type := 'PERSON';
499 org_rel_rec.subject_table_name := 'HZ_PARTIES';
500 org_rel_rec.object_type := 'ORGANIZATION';
501 org_rel_rec.object_table_name := 'HZ_PARTIES';
502 org_rel_rec.status := x_Status;
503 org_rel_rec.relationship_code := g_contact_of;
504 org_rel_rec.relationship_id := x_party_relationship_id;
505
506 ocon_rec.party_rel_rec := org_rel_rec;
507
508 -- IF x_last_update_date IS NULL THEN
509
510 -- END IF;
511 -- Assign the values to the Contact Points Record
512 cpoint_rec.contact_point_id := x_contact_point_Id;
513 cpoint_rec.status := NVL(x_status,'I');
514
515 -- Assign the value to the Email Record
516 -- email_rec.email_address := NVL(X_Email_Address,FND_API.G_MISS_CHAR);
517
518 -- Fetch the Org Contact Last Update Date
519 l_org_contacts_obj_ver_num := P_ORG_CONT_OVN;
520 l_hz_parties_obj_ver_num := P_REL_PARTY_OVN;
521 l_rel_obj_ver_num := P_REL_OVN;
522
523 -- Call the API for the updation of the Org Contact Record
524 hz_party_contact_v2pub.update_org_contact(
525 p_init_msg_list => 'T',
526 p_org_contact_rec => ocon_rec,
527 p_cont_object_version_number => l_org_contacts_obj_ver_num,
528 p_rel_object_version_number => l_rel_obj_ver_num,
529 p_party_object_version_number => l_hz_parties_obj_ver_num,
530 x_return_status => x_return_status,
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data);
533
534 -- If the API returns Error,
535 -- then exit the procedure
536 IF x_return_status <> 'S' THEN
537 IF x_msg_count > 1 THEN
538 -- initialise the variables again
539 tmp_var := NULL;
540 tmp_var1 := NULL;
541
542 FOR i IN 1..x_msg_count LOOP
543 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
544 tmp_var1 := tmp_var1 || ' '|| tmp_var;
545 END LOOP;
546 x_msg_data := tmp_var1;
547 END IF;
548 RETURN;
549 END IF;
550
551 -- Assign the values to the Contact Points Record
552 cpoint_rec.contact_point_id := x_contact_point_id;
553 cpoint_rec.contact_point_type := g_email;
554 cpoint_rec.status := 'A';
555 cpoint_rec.owner_table_name := g_own_tab_name;
556 cpoint_rec.owner_table_id := x_rel_party_id;
557
558 -- If the Contact Point Id is not present, then
559 -- Create the Contact Point if the Email Address is NOT NULL
560 -- else update the Contact Point
561
562
563 IF x_contact_point_id IS NOT NULL THEN
564
565 IF x_email_address IS NOT NULL THEN
566 cpoint_rec.primary_flag := 'Y';
567 email_rec.email_address := x_email_address;
568 ELSE
569
570 cpoint_rec.primary_flag := 'N';
571 email_rec.email_address := FND_API.G_MISS_CHAR;
572 END IF;
573
574 l_obj_ver := P_CONTACT_POINT_OVN;
575
576
577 HZ_CONTACT_POINT_V2PUB.update_contact_point(
578 p_init_msg_list => FND_API.G_FALSE,
579 p_contact_point_rec => cpoint_rec,
580 p_edi_rec => NULL,
581 p_email_rec => email_rec,
582 p_phone_rec => NULL,
583 p_telex_rec => NULL,
584 p_web_rec => NULL,
585 p_object_version_number => l_obj_ver,
586 x_return_status => x_return_status,
587 x_msg_count => x_msg_count,
588 x_msg_data => x_msg_data
589 );
590
591 ELSE
592
593 IF x_email_address IS NOT NULL THEN
594 cpoint_rec.primary_flag := 'Y';
595 email_rec.email_address := x_email_address;
596
597 SELECT hz_contact_points_s.NEXTVAL
598 INTO cpoint_rec.contact_point_id
599 FROM dual;
600
601 cpoint_rec.created_by_module := 'IGS' ;
602 cpoint_rec.content_source_type := 'USER_ENTERED';
603
604 hz_contact_point_v2pub.create_contact_point(
605 p_init_msg_list => FND_API.G_FALSE,
606 p_contact_point_rec => cpoint_rec ,
607 p_edi_rec => NULL,
608 p_email_rec => email_rec,
609 p_phone_rec => NULL,
610 p_telex_rec => NULL,
611 p_web_rec => NULL,
612 x_contact_point_id => l_contact_point_id,
613 x_return_status => x_return_status ,
614 x_msg_count => x_msg_count,
615 x_msg_data => x_msg_data );
616
617 END IF;
618 END IF;
619
620 IF x_return_status <> 'S' THEN
621 IF x_msg_count > 1 THEN
622 -- initialise the variables again
623 tmp_var := NULL;
624 tmp_var1 := NULL;
625
626 FOR i IN 1..x_msg_count LOOP
627 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
628 tmp_var1 := tmp_var1 || ' '|| tmp_var;
629 END LOOP;
630 x_msg_data := tmp_var1;
631 END IF;
632 RETURN;
633 END IF;
634
635 -- every thing successful then
636 P_CONTACT_POINT_OVN := l_obj_ver ;
637 P_ORG_CONT_OVN := l_org_contacts_obj_ver_num;
638 P_REL_PARTY_OVN := l_hz_parties_obj_ver_num;
639 P_REL_OVN := l_rel_obj_ver_num ;
640 END update_row;
641
642 END igs_or_contacts_pkg;