[Home] [Help]
PACKAGE BODY: APPS.IBE_PARTY_V2PVT
Source
1 PACKAGE BODY IBE_PARTY_V2PVT AS
2 /* $Header: IBEVPARB.pls 120.6 2005/11/07 04:27:33 banatara ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_PARTY_V2PVT';
5 G_CREATED_BY_MODULE VARCHAR2(30) := 'USER REGISTRATION';
6 G_APPLICATION_ID NUMBER := 671;
7 l_true VARCHAR2(1) := FND_API.G_TRUE;
8
9 -----------------------public procedures -------------------------------------
10 /*=======================================================================
11 | Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
12 | All rights reserved.
13 |=======================================================================
14 | PROCEDURE NAME
15 | Create_Individual_User
16 |
17 | DESCRIPTION
18 | This API is called during Individuale User Registration
19 |
20 | USAGE
21 | - Create a party in HzParties table.
22 | - Creates Contact Points
23 | - Creates Preferences
24 | - Create an apps username/password combination in FND_USER table.
25 |
26 | - Link the fnd_user table and hz_parties table by setting
27 | the customer_id column in fnd_user table.
28 | REFERENCED APIS
29 | This API calls the following APIs
30 | - Create_Person
31 | - Create_Contact_Preference
32 | - Create_User
33 |
34 |=======================================================================*/
35
36 Procedure Create_Individual_User(
37 p_username IN VARCHAR2,
38 p_password IN VARCHAR2,
39 p_person_rec IN HZ_PARTY_V2PUB.person_rec_type,
40 p_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
41 p_work_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
42 p_home_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
43 p_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
44 p_contact_preference IN VARCHAR2,
45 x_person_party_id OUT NOCOPY NUMBER,
46 x_user_id OUT NOCOPY NUMBER,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2) is
50
51 l_preference VARCHAR2(30);
52 l_reference_id NUMBER;
53 l_contact_preference_id NUMBER;
54
55 l_account_id NUMBER;
56 BEGIN
57 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
58 IBE_UTIL.debug('enter ibe_party_v2pvt.create_individual_user');
59 END IF;
60
61
62
63 -- initialize message list
64 FND_MSG_PUB.initialize;
65
66
67 -- Initialize API return status to success
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
71 IBE_UTIL.debug('Call Create_Person() API');
72 END IF;
73
74 -- calling internal API to create_person
75
76 Create_Person(p_person_rec => p_person_rec,
77 p_email_rec => p_email_rec,
78 p_work_phone_rec => p_work_phone_rec,
79 p_home_phone_rec => p_home_phone_rec,
80 p_fax_rec => p_fax_rec,
81 p_created_by_module =>G_CREATED_BY_MODULE,
82 p_account => 'false',
83 x_account_id => l_account_id,
84 x_person_party_id => x_person_party_id,
85 x_return_status => x_return_status,
86 x_msg_count => x_msg_count,
87 x_msg_data => x_msg_data);
88
89 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
90 IBE_UTIL.debug('After call to Create_Person - x_return_status : '|| x_return_status);
91 IBE_UTIL.debug('After call to Create_Person - x_msg_count: '|| x_msg_count);
92 IBE_UTIL.debug('After call to Create_Person - x_msg_data : '|| x_msg_data);
93 END IF;
94 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
95 RAISE FND_API.G_EXC_ERROR;
96 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
101 IBE_UTIL.debug('After call to Create_Person - party_id : ' || x_person_party_id);
102 END IF;
103
104
105 -- set the preference value and call update_contact_preference
106 -- to send marketing/promotional emails
107
108 if p_contact_preference = 'YES' then
109 l_preference := 'DO';
110 else
111 l_preference := 'DO_NOT';
112 end if;
113
114 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
115 IBE_UTIL.debug('Before call to Update_Contact_Preference');
116 END IF;
117
118 Update_Contact_Preference(p_party_id => x_person_party_id,
119 p_preference => l_preference,
120 p_object_version_number => null,
121 p_created_by_module => G_CREATED_BY_MODULE,
122 x_return_status => x_return_status,
123 x_msg_count => x_msg_count,
124 x_msg_data => x_msg_data);
125
126 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
127 IBE_UTIL.debug('After call to Update_Contact_Preference - x_return_status : '|| x_return_status);
128 IBE_UTIL.debug('After call to Update_Contact_Preference - x_msg_count : '|| x_msg_count);
129 IBE_UTIL.debug('After call to Update_Contact_Preference - x_msg_data : '|| x_msg_data);
130 END IF;
131
132 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
133 RAISE FND_API.G_EXC_ERROR;
134 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
139 IBE_UTIL.debug('After call to Update_Contact_Preference - x_contact_preference_id : ' || l_contact_preference_id);
140 END IF;
141
142
143 -- Call Create_User to create user in FND by setting
144 -- by setting the customer_id column from fnd_user table
145 -- to person_party_id
146 IF (p_username <> FND_API.G_MISS_CHAR and p_username is not null) then
147 IBE_USER_PVT.Create_User(p_user_name => p_username,
148 p_password => p_password,
149 p_start_date => sysdate,
150 p_end_date => null,
151 p_password_date => sysdate,
152 p_email_address => p_email_rec.email_address,
153 p_customer_id => x_person_party_id,
154 x_user_id => x_user_id);
155
156 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
157 IBE_UTIL.debug('After call to Create_User - x_user_id : '|| to_char(x_user_id));
158 END IF;
159
160 if (x_user_id is null) then
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 end if;
163 END IF;
164
165
166
167 -- standard call to get message count and if count is 1, get message info
168 FND_MSG_PUB.count_and_get(
169 p_encoded => FND_API.G_FALSE,
170 p_count => x_msg_count,
171 p_data => x_msg_data
172 );
176 END IF;
173
174 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
175 IBE_UTIL.debug('exit ibe_party_v2.create_individual_user');
177
178
179 EXCEPTION
180
181 WHEN FND_API.G_EXC_ERROR THEN
182
183
184
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 FND_MSG_PUB.ADD;
187 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
188 p_data => x_msg_data,
189 p_encoded => 'F');
190
191 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
192 IBE_UTIL.debug('G_EXC_ERROR exception');
193 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
194 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
195 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
196 IBE_UTIL.debug('error text : '|| SQLERRM);
197 END IF;
198
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200
201
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.ADD;
204 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
205 p_data => x_msg_data,
206 p_encoded => 'F');
207
208 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
209 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
210 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
211 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
212 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
213 IBE_UTIL.debug('error text : '|| SQLERRM);
214 END IF;
215
216 WHEN OTHERS THEN
217
218
219 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
220 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
221 FND_MESSAGE.Set_Token('REASON', SQLERRM);
222 FND_MSG_PUB.Add;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 FND_MSG_PUB.Add;
225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
226 p_data => x_msg_data,
227 p_encoded => 'F');
228
229 --IBE_UTIL.debug('OTHER exception');
230 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
231 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
232 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
233 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
234 IBE_UTIL.debug('error text : '|| SQLERRM);
235 END IF;
236
237 END create_individual_user;
238
239 /*=======================================================================
240 | Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
241 | All rights reserved.
242 +=======================================================================
243 | PROCEDURE NAME
244 | Create_Business_User
245 |
246 | DESCRIPTION
247 | This API is called during Business User Registration
248 |
249 | USAGE
250 | - Create an Organization if organization does not exists
251 | - Create a party in HzParties table.
252 | - Record the organizations location and contact points
253 | - Register the user as an org contact for the organization
254 | - Create an apps username/password combination in FND_USER table.
255 |
256 | - Link the fnd_user table and hz_parties table by setting
257 | the customer_id column in fnd_user table as the relationship_party_id.
258 |
259 | REFERENCED APIS
260 | This API calls the following APIs
261 | - Find_Organizatio n
262 | - Create_Organization
263 | - Create_Org_Contact
264 | - Update_Contact_Preference
265 | - Create_User
266 |
267 +=======================================================================*/
268
269 Procedure Create_Business_User(
270 p_username IN VARCHAR2,
271 p_password IN VARCHAR2,
272 p_person_rec IN HZ_PARTY_V2PUB.person_rec_type,
273 p_organization_rec IN HZ_PARTY_V2PUB.organization_rec_type,
274 p_location_rec IN HZ_LOCATION_V2PUB.location_rec_type,
275 p_org_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
276 p_org_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
277 p_rel_workphone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
278 p_rel_homephone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
279 p_rel_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
280 p_rel_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
281 p_rel_contact_preference IN VARCHAR2,
282 x_person_party_id OUT NOCOPY NUMBER,
283 x_rel_party_id OUT NOCOPY NUMBER,
284 x_org_party_id OUT NOCOPY NUMBER,
285 x_user_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2) is
289
290 l_preference VARCHAR2(30);
291 l_preference_id NUMBER;
292 l_rel_party_id NUMBER; ---party_id of type party_relationship
293 l_contact_preference_id NUMBER;
294 l_party_site_id NUMBER;
295
296
297 p_org_name HZ_PARTIES.PARTY_NAME%TYPE := null;
298 p_org_num HZ_PARTIES.PARTY_NUMBER%TYPE := null;
299 l_account_id NUMBER;
300 --p_party_rec HZ_PARTY_V2PUB.party_rec_type;
301 BEGIN
302 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
303 IBE_UTIL.debug('enter ibe_party_v2pvt.create_business_user');
304 END IF;
305
306
307 -- initialize message list
308 FND_MSG_PUB.initialize;
309
310
311 -- Initialize API return status to success
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313 -- Check whether Organization exists
314
315 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
316 IBE_UTIL.debug('Party_number : '||p_organization_rec.party_rec.party_number);
317 END IF;
318 if (( p_organization_rec.party_rec.party_number is not null) AND
319 ( p_organization_rec.party_rec.party_number <> FND_API.G_MISS_CHAR)) then
320 p_org_num := p_organization_rec.party_rec.party_number;
321 end if;
322 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
323 IBE_UTIL.debug('Party_name : '||p_organization_rec.organization_name);
324 END IF;
325 if ((p_organization_rec.organization_name is not null) AND
326 (p_organization_rec.organization_name <> FND_API.G_MISS_CHAR)) then
327 p_org_name := p_organization_rec.organization_name;
328 end if;
329 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
330 IBE_UTIL.debug('p_org_num : '||p_org_num);
331 IBE_UTIL.debug('p_org_name : '||p_org_name);
332 IBE_UTIL.debug('Before p_org_nname not null');
333 END IF;
334 --p_org_name := p_organization_rec.organization_name;
335
336 if ((p_org_name is not null) ) then
337 /* Removing duplicate org name check */
338 /* IBE_UTIL.debug('Call Find_Organization()in p_org_name not null API');
339
340 If ( Find_Organization(x_org_id => x_org_party_id,
341 x_org_num => p_org_num,
342 x_org_name =>p_org_name
343 )) then
344 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
345 IBE_UTIL.debug('Inside find_organization - x_org_party_id : '||x_org_party_id);
346 IBE_UTIL.debug('Inside find_organization - x_org_num : '||p_org_num);
347 IBE_UTIL.debug('Inside find_organization - x_org_name : '||p_org_name);
348 END IF;
349
350 FND_MESSAGE.SET_NAME('IBE','IBE_ERR_UM_ORG_EXISTS');
351 FND_MSG_PUB.ADD;
352 RAISE FND_API.G_EXC_ERROR;
353 end if;
354 else
355 */
356 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
357 IBE_UTIL.debug('Before Call to Create Organization');
358 IBE_UTIL.debug('Before Call to Create Organization address1 :'|| p_location_rec.address1);
359 IBE_UTIL.debug('Before Call to Create Organization country :'||p_location_rec.country);
360 IBE_UTIL.debug('Before Call to Create Organization city :'||p_location_rec.city);
361 END IF;
362 Create_Organization(
363 p_organization_rec =>p_organization_rec,
364 p_location_rec =>p_location_rec,
365 p_party_site_rec => null,
366 p_org_workphone_rec =>p_org_phone_rec,
367 /*p_org_homephone_rec => null,*/
368 p_org_fax_rec => p_org_fax_rec,
369 p_primary_billto => FND_API.G_TRUE,
370 p_primary_shipto => FND_API.G_TRUE,
371 p_billto => FND_API.G_TRUE,
372 p_shipto => FND_API.G_TRUE,
373 p_default_primary => FND_API.G_TRUE,
374 p_created_by_module =>G_CREATED_BY_MODULE,
375 p_account =>'false',
376 x_account_id => l_account_id,
377 x_party_site_id => l_party_site_id,
378 x_org_party_id =>x_org_party_id,
379 x_return_status =>x_return_status,
380 x_msg_count =>x_msg_count,
381 x_msg_data =>x_msg_data);
382
383 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
384 RAISE FND_API.G_EXC_ERROR;
385 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
388 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
389 IBE_UTIL.debug('After call to Create_Organization - x_return_status : '|| x_return_status);
390 IBE_UTIL.debug('After call to Create_Organization - x_msg_count : '|| x_msg_count);
391 IBE_UTIL.debug('After call to Create_Organization - x_msg_data : '|| x_msg_data);
392 IBE_UTIL.debug('After Create Organization x_org_party_id :' || x_org_party_id);
393 END IF;
394
395 elsif (p_org_num is not null) then
396 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
397 IBE_UTIL.debug('Call Find_Organization()in p_org_num not null API');
398 END IF;
399 if (not Find_Organization(x_org_id => x_org_party_id,
400 x_org_num => p_org_num,
401 x_org_name => p_org_name)) then
402 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
403 IBE_UTIL.debug('Inside NOT find_organization - x_org_party_id : '||x_org_party_id);
404 IBE_UTIL.debug('Inside NOT find_organization - x_org_num :'||p_org_num);
405 IBE_UTIL.debug('Inside NOT find_organization - x_org_name : '||p_org_name);
406 END IF;
407 /* If organization does not exist then raise error */
408 FND_MESSAGE.SET_NAME('IBE','IBE_ERR_UM_ORG_NOT_FOUND');
409 FND_MSG_PUB.ADD;
410 RAISE FND_API.G_EXC_ERROR;
411 end if;
412
413
414 end if;
415
416 -- Create ORG Contact which internally creates party_relationship
417 -- Pass organization party id from the above api and as
418 -- p_org_id to create org contact also pass in the account_id if
419 -- cust_account_role needs to be created
420
421 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
422 IBE_UTIL.debug('Before Create_Org_Contact');
423 END IF;
424 Create_Org_Contact(
425 p_person_rec => p_person_rec,
426 p_relationship_type => 'EMPLOYEE_OF',
427 p_org_party_id =>x_org_party_id,
428 p_email_rec =>p_rel_email_rec,
429 p_work_phone_rec =>p_rel_workphone_rec,
430 p_home_phone_rec =>p_rel_homephone_rec,
431 p_fax_rec => p_rel_fax_rec,
432 p_created_by_module =>G_CREATED_BY_MODULE,
433 x_person_party_id => x_person_party_id,
434 x_rel_party_id => l_rel_party_id,
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data);
438 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
442 END IF;
439 RAISE FND_API.G_EXC_ERROR;
440 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443
444 x_rel_party_id := l_rel_party_id;
445
446 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
447 IBE_UTIL.debug('After call to Create_Org_Contact - x_return_status : '|| x_return_status);
448 IBE_UTIL.debug('After call to Create_Org_Contact - x_msg_count : '|| x_msg_count);
449 IBE_UTIL.debug('After call to Create_Org_Contact - x_msg_data : '|| x_msg_data);
450 IBE_UTIL.debug('After Create_Org_Contact x_rel_party_id: '||x_rel_party_id);
451 END IF;
452 -- Populate contact_level_id with l_rel_party_id which is the
453 -- relationship_id and call Create_Contact_Preference to send marketing/promotional emails
454
455 If p_rel_contact_preference = 'YES' then
456 l_preference := 'DO';
457 Else
458 l_preference := 'DO_NOT';
459 End if;
460
461 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
462 IBE_UTIL.debug('Before Update_Contact_Preference');
463 END IF;
464 Update_Contact_Preference(
465 p_party_id => x_rel_party_id ,
466 p_preference => l_preference,
467 p_object_version_number => null,
468 p_created_by_module => G_CREATED_BY_MODULE,
469 x_return_status => x_return_status,
470 x_msg_count => x_msg_count,
471 x_msg_data => x_msg_data) ;
472
473 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 end if;
476
477 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
478 IBE_UTIL.debug('After call to Update_Contact_Preference - x_return_status : '|| x_return_status);
479 IBE_UTIL.debug('After call to Update_Contact_Preference - x_msg_count : '|| x_msg_count);
480 IBE_UTIL.debug('After call to Update_Contact_Preference - x_msg_data : '|| x_msg_data);
481 END IF;
482
483
484 -- Call Create_User to create user in FND by setting
485 -- by setting the customer_id column from fnd_user table
486 -- to x_rel_party_id
487 IF (p_username <> FND_API.G_MISS_CHAR and p_username is not null) then
488 IBE_USER_PVT.Create_User(p_user_name => p_username,
489 p_password => p_password,
490 p_start_date => sysdate,
491 p_end_date => null,
492 p_password_date => sysdate,
493 p_email_address => p_rel_email_rec.email_address,
494 p_customer_id => x_rel_party_id,
495 x_user_id => x_user_id);
496
497 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
498 IBE_UTIL.debug('After call to Create_User - x_user_id : '|| to_char(x_user_id));
499 END IF;
500
501 if (x_user_id is null) then
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 end if;
504
505
506 END IF;
507
508 -- standard call to get message count and if count is 1, get message info
509 FND_MSG_PUB.count_and_get(
510 p_encoded => FND_API.G_FALSE,
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514
515 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
516 IBE_UTIL.debug('exit ibe_party_v2.create_business_user');
517 END IF;
518
519
520 EXCEPTION
521
522 WHEN FND_API.G_EXC_ERROR THEN
523
524
525
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 FND_MSG_PUB.ADD;
528 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
529 p_data => x_msg_data,
530 p_encoded => 'F');
531
535 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
532 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
533 IBE_UTIL.debug('G_EXC_ERROR exception');
534 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
536 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
537 IBE_UTIL.debug('error text : '|| SQLERRM);
538 END IF;
539
540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541
542
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 FND_MSG_PUB.ADD;
545 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
546 p_data => x_msg_data,
547 p_encoded => 'F');
548
549 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
550 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
551 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
552 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
553 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
554 IBE_UTIL.debug('error text : '|| SQLERRM);
555 END IF;
556
557 WHEN OTHERS THEN
558
559
560 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
561 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
562 FND_MESSAGE.Set_Token('REASON', SQLERRM);
563 FND_MSG_PUB.Add;
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565 FND_MSG_PUB.Add;
566 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
567 p_data => x_msg_data,
568 p_encoded => 'F');
569
570 --IBE_UTIL.debug('OTHER exception');
571 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
572 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
573 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
574 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
575 IBE_UTIL.debug('error text : '|| SQLERRM);
576 END IF;
577
578 END;
579
580
581
582 /*+=======================================================================
583 | Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
584 | All rights reserved.
585 +=======================================================================
586 | PROCEDURE NAME
587 | Create_Org_Contact
588 |
589 | DESCRIPTION
590 | This API is called during Business User Registration and while
591 | creating Contacts in User Management
592 |
593 | USAGE
594 | - Create a party in HzParties table.
595 | - Creates the user as the org contact for the organization
596 | - Creates COntact Points
597 |
598 | REFERENCED APIS
599 | This API calls the following APIs
600 | - Create_Person
601 | - HZ_PARTY_CONTACT_V2PUB.create_org_contact
602 | - Create_Contact_Points
603 |
604 +=======================================================================*/
605
606 Procedure Create_Org_Contact(
607 p_person_rec IN HZ_PARTY_V2PUB.person_rec_type,
608 p_relationship_type IN VARCHAR2, -- 'EMPLOYEE_OF' or 'CONTACT_OF'
609 p_org_party_id IN NUMBER,
610 p_work_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
611 p_home_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
612 p_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
613 p_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
614 p_created_by_module IN VARCHAR2,
615 x_person_party_id OUT NOCOPY NUMBER,
616 x_rel_party_id OUT NOCOPY NUMBER,
617 x_return_status OUT NOCOPY VARCHAR2,
618 x_msg_count OUT NOCOPY NUMBER,
619 x_msg_data OUT NOCOPY VARCHAR2) is
620
621 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
622 l_org_contact_party_id NUMBER;
623 l_rel_id NUMBER;
624 l_account_id NUMBER;
625 l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
626 l_rel_party_id NUMBER; ---party_id of type party_relationship
627 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
628 BEGIN
629 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
630 IBE_UTIL.debug('enter ibe_party_v2pvt.create_org_contact');
631 END IF;
632
633
634
635 -- initialize message list
636 FND_MSG_PUB.initialize;
637
638
639 -- Initialize API return status to success
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
642 IBE_UTIL.debug('Call Create_Person() API');
643 END IF;
644
645 -- calling internal API to create_person
646 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
647 IBE_UTIL.debug('Call Create_Person() API');
648 END IF;
649
650 Create_Person(p_person_rec => p_person_rec,
651 p_email_rec => null,
652 p_work_phone_rec => null,
653 p_home_phone_rec => null,
654 p_fax_rec => null,
655 p_created_by_module => p_created_by_module,
656 x_person_party_id => x_person_party_id,
657 x_account_id => l_account_id,
658 x_return_status => x_return_status,
659 x_msg_count => x_msg_count,
660 x_msg_data => x_msg_data);
661
662 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
663 IBE_UTIL.debug('After call to Create_Person - x_return_status : '|| x_return_status);
664 IBE_UTIL.debug('After call to Create_Person - x_msg_count : '|| x_msg_count);
665 IBE_UTIL.debug('After call to Create_Person - x_msg_data :' || x_msg_data);
666 END IF;
667 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
668 RAISE FND_API.G_EXC_ERROR;
669 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END IF;
672
673 -- Create ORG Contact which internally creates party_relationship
674 -- Pass person_party_id as subject_id
675
676 l_party_rel_rec.subject_id := x_person_party_id;
677 l_party_rel_rec.subject_type := 'PERSON';
678 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
679
680 -- pass organization_party_id as object_id
681 l_party_rel_rec.object_id := p_org_party_id;
682 l_party_rel_rec.object_type := 'ORGANIZATION';
683 l_party_rel_rec.object_table_name := 'HZ_PARTIES';
684
685
686
687 if (p_relationship_type = 'EMPLOYEE_OF') then
688 l_party_rel_rec.relationship_type := 'EMPLOYMENT';
689 l_party_rel_rec.relationship_code := 'EMPLOYEE_OF';
690 elsif (p_relationship_type = 'CONTACT_OF') then
691 l_party_rel_rec.relationship_type := 'CONTACT';
692 l_party_rel_rec.relationship_code := 'CONTACT_OF';
693 end if;
694
695 l_party_rel_rec.start_date:= sysdate;
696 l_party_rel_rec.created_by_module := P_CREATED_BY_MODULE;
697 l_party_rel_rec.application_id := G_APPLICATION_ID;
698
699 /*
700 -- bug 2600165 fix --
701 IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'), 'Y') = 'N' THEN
702 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
703 IBE_UTIL.debug('Party Number Auto generation is off');
704 END IF;
705 select hz_party_number_s.nextval into l_rel_party_number from dual;
706 l_party_rel_rec.party_rec.party_number := l_rel_party_number;
707 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
708 IBE_UTIL.debug('Relationship Party Number :' || l_rel_party_number);
709 END IF;
710 END IF;
711 */
712
713 l_org_contact_rec.party_rel_rec := l_party_rel_rec;
714 l_org_contact_rec.created_by_module := P_CREATED_BY_MODULE;
715 l_org_contact_rec.application_id := G_APPLICATION_ID;
716
717
718
719 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
720 IBE_UTIL.debug('Call HZ_PARTY_CONTACT_V2PUB.create_org_contact () API');
721 END IF;
722
723 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
724 p_org_contact_rec => l_org_contact_rec,
725 x_org_contact_id => l_org_contact_party_id,
726 x_party_rel_id => l_rel_id,
727 x_party_id => l_rel_party_id,
728 x_party_number => l_rel_party_number,
729 x_return_status => x_return_status,
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data);
732 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
733 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - l_org_contact_party_id : '|| l_org_contact_party_id);
734 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_return_status : '|| x_return_status);
735 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_msg_count : '|| x_msg_count);
736 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_msg_data :' || x_msg_data);
737 END IF;
738
739
740 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742 end if;
743
744 x_rel_party_id := l_rel_party_id;
745 -- Create Relationship contact_points, pass rel_party_id from the
746 -- above API as owner_table_id
747 -- Call internal Create_Contact_Points API
748
749 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
750 IBE_UTIL.debug('Call Create_Contact_Points() API');
751 END IF;
752 Create_Contact_Points(
753 p_owner_table_id => x_rel_party_id,
754 p_email_rec => p_email_rec,
755 p_work_phone_rec => p_work_phone_rec,
756 p_home_phone_rec => p_home_phone_rec,
757 p_fax_rec => p_fax_rec,
758 p_contact_point_purpose => true,
759 p_created_by_module => G_CREATED_BY_MODULE,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data);
763
764 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
765 IBE_UTIL.debug('After call to Create_Contact_Points() - x_return_status : '|| x_return_status);
766 IBE_UTIL.debug('After call to Create_Contact_Points() - x_msg_count : '|| x_msg_count);
767 IBE_UTIL.debug('After call to Create_Contact_Points()- x_msg_data : '|| x_msg_data);
768 END IF;
769 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
770 RAISE FND_API.G_EXC_ERROR;
771 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775
776
777
778 -- standard call to get message count and if count is 1, get message info
779 FND_MSG_PUB.count_and_get(
780 p_encoded => FND_API.G_FALSE,
781 p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784
785 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
786 IBE_UTIL.debug('exit ibe_party_v2.create_individual_user');
787 END IF;
788
789
790 EXCEPTION
791
792 WHEN FND_API.G_EXC_ERROR THEN
793
794
795
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 FND_MSG_PUB.ADD;
798 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
799 p_data => x_msg_data,
800 p_encoded => 'F');
801
802 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
803 IBE_UTIL.debug('G_EXC_ERROR exception');
804 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
805 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
806 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
807 IBE_UTIL.debug('error text : '|| SQLERRM);
808 END IF;
809
810 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
811
812
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 FND_MSG_PUB.ADD;
815 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
816 p_data => x_msg_data,
817 p_encoded => 'F');
818
819 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
820 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
821 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
822 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
823 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
824 IBE_UTIL.debug('error text : '|| SQLERRM);
825 END IF;
826
827 WHEN OTHERS THEN
828
829
830 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
831 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
835 FND_MSG_PUB.Add;
832 FND_MESSAGE.Set_Token('REASON', SQLERRM);
833 FND_MSG_PUB.Add;
834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
837 p_data => x_msg_data,
838 p_encoded => 'F');
839
840 --IBE_UTIL.debug('OTHER exception');
841 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
842 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
843 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
844 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
845 IBE_UTIL.debug('error text : '|| SQLERRM);
846 END IF;
847
848 END;
849
850 /*====================================================================
851 | PROCEDURE NAME
852 | Create_Person
853 |
854 | DESCRIPTION
855 | This API is called by Create_Individual_User,
856 | Create_Customer
857 | Create_Org_Contact
858 |
859 | USAGE
860 | - Create a party in HzParties table.
861 | - Creates Contact Points
862 | - Creates Accountss
863 |
864 | REFERENCED APIS
865 | This API calls the following APIs
866 | - HZ_PARTY_v2PUB.create_person
867 | - Create_Contact_Points
868 |======================================================================*/
869 Procedure Create_Person(
870 p_person_rec IN HZ_PARTY_V2PUB.person_rec_type,
871 p_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
872 p_work_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
873 p_home_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
874 p_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
875 p_created_by_module IN VARCHAR2,
876 p_account IN VARCHAR2 ,
877 x_person_party_id OUT NOCOPY NUMBER,
878 x_account_id OUT NOCOPY NUMBER,
879 x_return_status OUT NOCOPY VARCHAR2,
880 x_msg_count OUT NOCOPY NUMBER,
881 x_msg_data OUT NOCOPY VARCHAR2) is
882
883
884 l_party_id NUMBER;
885 l_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
886 l_profile_id NUMBER;
887 l_account_number NUMBER;
888 l_account_id NUMBER;
889 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
890
891 l_home_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type := null;
892 l_work_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type := null;
893 l_fax_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type := null;
894 l_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type := null;
895 BEGIN
896 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
897 IBE_UTIL.debug('enter ibe_party_v2pvt.create_person');
898 END IF;
899
900 l_person_rec := p_person_rec;
901 l_person_rec.created_by_module := p_created_by_module;
902 l_person_rec.application_id := G_APPLICATION_ID;
903
904
905
906 -- initialize message list
907 FND_MSG_PUB.initialize;
908
909
910 -- Initialize API return status to success
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
914 IBE_UTIL.debug('Call Create_Person() API');
915 END IF;
916
917 /*
918 -- bug 2600165 fix --
919 IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'), 'Y') = 'N' THEN
920 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
921 IBE_UTIL.debug('Party Number Auto generation is off');
922 END IF;
923 select hz_party_number_s.nextval into l_party_number from dual;
924 l_person_rec.party_rec.party_number := l_party_number;
925 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
926 IBE_UTIL.debug('Person Party Number :' || l_party_number);
927 END IF;
928 END IF;
929 */
930
931 -- calling TCA API to create_person
932
933 HZ_PARTY_V2PUB.create_person (
934 p_person_rec => l_person_rec,
935 x_party_id => l_party_id,
936 x_party_number => l_party_number,
937 x_profile_id => l_profile_id,
938 x_return_status => x_return_status,
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data);
941
942 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
944 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
946 END IF;
947
948 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
949 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_person - l_party_id : '|| l_party_id);
950 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_person - x_return_status : '|| x_return_status);
951 IBE_UTIL.debug('After call HZ_PARTY_V2PUB.create_person - x_msg_count : '|| x_msg_count);
952 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_person - x_msg_data : '|| x_msg_data);
953 END IF;
954
955 x_person_party_id := l_party_id;
956
957
958 -- Call internal Create_Contact_Points API
959 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
960 IBE_UTIL.debug('Call Create_Contact_Points() API');
961 END IF;
962 Create_Contact_Points(
963 p_owner_table_id => l_party_id,
964 p_email_rec => p_email_rec,
965 p_work_phone_rec => p_work_phone_rec,
966 p_home_phone_rec => p_home_phone_rec,
967 p_fax_rec => p_fax_rec,
968 p_contact_point_purpose => true,
969 p_created_by_module => G_CREATED_BY_MODULE,
970 x_return_status => x_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data);
973
974 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
975 IBE_UTIL.debug('After call to Create_Contact_Points() - x_return_status : '|| x_return_status);
976 IBE_UTIL.debug('After call to Create_Contact_Points() - x_msg_count : '|| x_msg_count);
977 IBE_UTIL.debug('After call to Create_Contact_Points()- x_msg_data : '|| x_msg_data);
978 END IF;
979 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
980 RAISE FND_API.G_EXC_ERROR;
981 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983 END IF;
984
985 --if p_account - true then call create_account
986
987
988 If (p_account= 'true') then
989 /*** Create Account for the Person***/
990 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
991 IBE_UTIL.debug(' Call to Create_Account');
992 END IF;
993
994 Create_Account(
995 p_party_id => l_party_id,
996 p_party_type => 'P',
997 p_created_by_module =>p_created_by_module,
998 x_account_id => x_account_id,
999 x_return_status =>x_return_status,
1000 x_msg_count =>x_msg_count,
1001 x_msg_data =>x_msg_data);
1002
1003
1004
1005 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1006 IBE_UTIL.debug('After call to Create_Account() - x_return_status : '|| x_return_status);
1007 IBE_UTIL.debug('After call to Create_Account() - x_msg_count : '|| x_msg_count);
1008 IBE_UTIL.debug('After call to Create_Account()- x_msg_data : '|| x_msg_data);
1009 END IF;
1010 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1011 RAISE FND_API.G_EXC_ERROR;
1012 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1014 END IF;
1015
1016 End if;
1017 -- standard call to get message count and if count is 1, get message info
1018 FND_MSG_PUB.count_and_get(
1019 p_encoded => FND_API.G_FALSE,
1020 p_count => x_msg_count,
1021 p_data => x_msg_data
1022 );
1023
1024 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1025 IBE_UTIL.debug('exit ibe_party_v2.create_person');
1026 END IF;
1027
1028
1029 EXCEPTION
1030
1031 WHEN FND_API.G_EXC_ERROR THEN
1032
1033
1034
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 FND_MSG_PUB.ADD;
1037 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1038 p_data => x_msg_data,
1039 p_encoded => 'F');
1040
1041 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1042 IBE_UTIL.debug('G_EXC_ERROR exception');
1043 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1044 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1045 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1046 IBE_UTIL.debug('error text : '|| SQLERRM);
1047 END IF;
1048
1049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1050
1051
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 FND_MSG_PUB.ADD;
1054 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1055 p_data => x_msg_data,
1056 p_encoded => 'F');
1057
1058 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1059 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1060 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1061 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1062 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1063 IBE_UTIL.debug('error text : '|| SQLERRM);
1064 END IF;
1065
1066 WHEN OTHERS THEN
1067
1068
1069 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1070 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1071 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1072 FND_MSG_PUB.Add;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 FND_MSG_PUB.Add;
1075 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1076 p_data => x_msg_data,
1077 p_encoded => 'F');
1078
1079 --IBE_UTIL.debug('OTHER exception');
1080 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1081 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1082 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1083 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1084 IBE_UTIL.debug('error text : '|| SQLERRM);
1085 END IF;
1086 END create_person;
1087
1088 /*+====================================================================
1089 | PROCEDURE NAME
1090 | Create_Organization
1091 |
1092 | DESCRIPTION
1093 | This API is called by Create_Business_User,
1094 | Create_Customer
1095 |
1096 |
1097 | USAGE
1098 | - Create Organization
1099 | - Creates Location and PartySite
1100 | - Creates Organization contact points
1101 |
1102 | REFERENCED APIS
1103 | This API calls the following APIs
1104 | - HZ_PARTY_v2PUB.create_organization
1105 | - IBE_ADDRESS_V2PVT.create_address
1106 | - Create_Contact_Points
1107 | - Create_Account id p_account is true
1108 +======================================================================*/
1109 Procedure Create_Organization(
1110 p_organization_rec IN HZ_PARTY_V2PUB.organization_rec_type,
1111 p_org_workphone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1112 /* p_org_homephone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1113 */
1114 p_org_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1115 p_location_rec IN HZ_LOCATION_V2PUB.location_rec_type,
1116 p_party_site_rec IN HZ_PARTY_SITE_V2PUB.party_site_rec_type,
1117 p_primary_billto IN VARCHAR2 := FND_API.G_FALSE,
1118 p_primary_shipto IN VARCHAR2 := FND_API.G_FALSE,
1119 p_billto IN VARCHAR2 := FND_API.G_FALSE,
1120 p_shipto IN VARCHAR2 := FND_API.G_FALSE,
1121 p_default_primary IN VARCHAR2 := FND_API.G_FALSE,
1122 p_created_by_module IN VARCHAR2,
1123 p_account IN VARCHAR2 ,
1124 x_org_party_id OUT NOCOPY NUMBER,
1125 x_account_id OUT NOCOPY NUMBER,
1126 x_party_site_id OUT NOCOPY NUMBER,
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 x_msg_count OUT NOCOPY NUMBER,
1129 x_msg_data OUT NOCOPY VARCHAR2) is
1130
1131
1132 l_party_id NUMBER;
1133 l_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
1134 l_location_id NUMBER;
1135 l_party_site_id NUMBER;
1136 l_profile_id NUMBER;
1137 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
1138 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
1139 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
1140
1141 l_org_workphone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type := null;
1142 /*l_org_homephone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type:= null;*/
1143 l_org_fax_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type:= null;
1144
1145
1146 --l_account_id NUMBER;
1147 BEGIN
1148 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1149 IBE_UTIL.debug('enter ibe_party_v2pvt.create_organization');
1150 END IF;
1151
1152
1153 l_organization_rec := p_organization_rec;
1154 l_organization_rec.created_by_module := p_created_by_module;
1155 l_organization_rec.application_id := G_APPLICATION_ID;
1156
1157 l_location_rec := p_location_rec;
1158 l_location_rec.created_by_module := p_created_by_module;
1159 l_location_rec.application_id := G_APPLICATION_ID;
1160 -- initialize message list
1161 FND_MSG_PUB.initialize;
1162
1163
1164 -- Initialize API return status to success
1165 x_return_status := FND_API.G_RET_STS_SUCCESS;
1166
1167 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1168 IBE_UTIL.debug('Call Create_Organization() API');
1169 END IF;
1170 /*
1171 -- bug 2600165 fix --
1172 IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'), 'Y') = 'N' THEN
1173 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1174 IBE_UTIL.debug('Party Number Auto generation is off');
1175 END IF;
1176 select hz_party_number_s.nextval into l_party_number from dual;
1177 l_organization_rec.party_rec.party_number := l_party_number;
1178 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1179 IBE_UTIL.debug('Organization Party Number :' || l_party_number);
1180 END IF;
1181
1182 END IF;
1183 */
1184 -- calling TCA API to create_Organization
1185
1186 HZ_PARTY_V2PUB.create_organization (
1187 p_organization_rec => l_organization_rec,
1188 x_party_id => l_party_id,
1189 x_party_number => l_party_number,
1190 x_profile_id => l_profile_id,
1191 x_return_status => x_return_status,
1192 x_msg_count => x_msg_count,
1193 x_msg_data => x_msg_data);
1194 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1195 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_organization - l_party_id : '|| l_party_id);
1196 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_organization - x_return_status : '|| x_return_status);
1197 IBE_UTIL.debug('After call HZ_PARTY_V2PUB.create_organization - x_msg_count : '|| x_msg_count);
1198 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create_organization - x_msg_data : '|| x_msg_data);
1199 END IF;
1200 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206
1207 x_org_party_id := l_party_id;
1208
1209 l_party_site_rec := p_party_site_rec;
1210 -- Create Party_Site for the Organization
1211 -- associate org_party_id to l_party_site_rec
1212 --l_party_site_rec.party_id := l_party_id;
1213 l_party_site_rec.party_id := x_org_party_id;
1214 l_party_site_rec.created_by_module := p_created_by_module;
1215 l_party_site_rec.application_id := G_APPLICATION_ID;
1216
1217 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1218 IBE_UTIL.debug('Call IBE_ADDRESS_V2PVT.creat_address() API');
1219 IBE_UTIL.debug('Call IBE_ADDRESS_V2PVT.creat_address() API address1 :'||l_location_rec.address1);
1220 END IF;
1221 -- calling internal API to create address (location , party_site)
1222 IBE_ADDRESS_V2PVT.create_address(
1223 p_api_version => 1.0,
1224 p_init_msg_list => null,
1225 p_commit => null,
1226 p_location => l_location_rec,
1227 p_party_site => l_party_site_rec,
1228 p_primary_billto => p_primary_billto,
1229 p_primary_shipto => p_primary_shipto,
1230 p_billto => p_billto,
1231 p_shipto => p_shipto,
1232 p_default_primary =>p_default_primary,
1233 x_location_id => l_location_id,
1234 x_party_site_id => x_party_site_id,
1235 x_return_status => x_return_status,
1236 x_msg_count => x_msg_count,
1237 x_msg_data => x_msg_data);
1238
1239 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1241 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243 END IF;
1244 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1245 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - l_location_id : '|| l_location_id);
1246 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_party_site_id : '|| x_party_site_id);
1247 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_return_status : '|| x_return_status);
1248 IBE_UTIL.debug('After call IBE_ADDRESS_V2PVT.create_address - x_msg_count : '|| x_msg_count);
1249 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_msg_data : '|| x_msg_data);
1250 END IF;
1251
1252
1253
1254
1255
1256 -- Call internal Create_Contact_Points API to create Org Contact Points
1257 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1258 IBE_UTIL.debug('Call Create_Contact_Points() API');
1259 END IF;
1260 Create_Contact_Points(
1261 p_owner_table_id => x_org_party_id,
1262 p_work_phone_rec => p_org_workphone_rec,
1263 p_home_phone_rec => null,
1264 p_email_rec => null,
1265 p_fax_rec =>p_org_fax_rec,
1266 p_contact_point_purpose => true,
1267 p_created_by_module => G_CREATED_BY_MODULE,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data);
1271
1272 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1273 IBE_UTIL.debug('After call to Create_Contact_Points() - x_return_status : '|| x_return_status);
1274 IBE_UTIL.debug('After call to Create_Contact_Points() - x_msg_count : '|| x_msg_count);
1275 IBE_UTIL.debug('After call to Create_Contact_Points()- x_msg_data : '|| x_msg_data);
1276 END IF;
1277 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1278 RAISE FND_API.G_EXC_ERROR;
1279 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END IF;
1282
1283
1284
1285
1286 --- if p_accont - true then call create_account
1287
1288
1289 If (p_account = 'true') then
1293 END IF;
1290 /*** Create Account for the Organization***/
1291 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1292 IBE_UTIL.debug(' Call to Create_Account');
1294
1295 Create_Account(
1296 p_party_id => x_org_party_id,
1297 p_party_type => 'O',
1298 p_created_by_module =>p_created_by_module,
1299 x_account_id => x_account_id,
1300 x_return_status =>x_return_status,
1301 x_msg_count =>x_msg_count,
1302 x_msg_data =>x_msg_data);
1303
1304
1305
1306 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1307 IBE_UTIL.debug('After call to Create_Account() - x_return_status : '|| x_return_status);
1308 IBE_UTIL.debug('After call to Create_Account() - x_account_id : '|| x_account_id);
1309 IBE_UTIL.debug('After call to Create_Account() - x_msg_count : '|| x_msg_count);
1310 IBE_UTIL.debug('After call to Create_Account()- x_msg_data : '|| x_msg_data);
1311 END IF;
1312 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1313 RAISE FND_API.G_EXC_ERROR;
1314 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 END IF;
1317
1318 End if;
1319 -- standard call to get message count and if count is 1, get message info
1320 FND_MSG_PUB.count_and_get(
1321 p_encoded => FND_API.G_FALSE,
1322 p_count => x_msg_count,
1323 p_data => x_msg_data
1324 );
1325
1326 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1327 IBE_UTIL.debug('exit ibe_party_v2.create_person');
1328 END IF;
1329
1330
1331 EXCEPTION
1332
1333 WHEN FND_API.G_EXC_ERROR THEN
1334
1335
1336
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 FND_MSG_PUB.ADD;
1339 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1340 p_data => x_msg_data,
1341 p_encoded => 'F');
1342
1343 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1344 IBE_UTIL.debug('G_EXC_ERROR exception');
1345 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1346 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1347 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1348 IBE_UTIL.debug('error text : '|| SQLERRM);
1349 END IF;
1350
1351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1352
1353
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1355 FND_MSG_PUB.ADD;
1356 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1357 p_data => x_msg_data,
1358 p_encoded => 'F');
1359
1360 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1361 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1362 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1363 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1364 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1365 IBE_UTIL.debug('error text : '|| SQLERRM);
1366 END IF;
1367
1368 WHEN OTHERS THEN
1369
1370
1371 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1372 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1373 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1374 FND_MSG_PUB.Add;
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1376 FND_MSG_PUB.Add;
1377 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1378 p_data => x_msg_data,
1379 p_encoded => 'F');
1380
1381 --IBE_UTIL.debug('OTHER exception');
1382 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1383 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1384 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1385 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1386 IBE_UTIL.debug('error text : '|| SQLERRM);
1387 END IF;
1388 END create_organization;
1389
1390
1391 /*+====================================================================
1392 | PROCEDURE NAME
1393 | Create_Contact_Points
1394 |
1395 | DESCRIPTION
1396 | This API is called By Create_Person,
1397 | Creat_Organization
1398 | Create_Org_Contact
1399 |
1400 | USAGE
1401 |
1402 | - Creates Contact Points in TCA tables
1403 |
1404 |
1405 | REFERENCED APIS
1406 | This API calls the following APIs
1407 | - HZ_CONTACT_POINTS_V2PUB.Create_Contact_Points
1408 +======================================================================*/
1409 Procedure Create_Contact_Points(
1410 p_owner_table_id IN NUMBER,
1411 p_work_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1412 p_home_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1413 p_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
1414 p_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
1415 p_contact_point_purpose IN BOOLEAN, --indicates whether to populate contact_point_purpose
1416 p_created_by_module IN VARCHAR2,
1417 x_return_status OUT NOCOPY VARCHAR2,
1418 x_msg_count OUT NOCOPY NUMBER,
1419 x_msg_data OUT NOCOPY VARCHAR2) is
1420
1421
1422 l_contact_point_id NUMBER;
1423
1424 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
1425
1426 BEGIN
1427 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1428 IBE_UTIL.debug('enter ibe_party_v2pvt.create_contact_points');
1429 END IF;
1430
1431 l_contact_point_rec.status := 'A';
1432 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1433 l_contact_point_rec.owner_table_id := p_owner_table_id;
1434 l_contact_point_rec.created_by_module := p_created_by_module;
1435 l_contact_point_rec.application_id := G_APPLICATION_ID;
1436
1437
1438
1439 -- initialize message list
1440 FND_MSG_PUB.initialize;
1441 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1442 IBE_UTIL.debug('Enter Create_Contact_Points()');
1443 END IF;
1444
1445 -- Initialize API return status to success
1446 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447 l_contact_point_rec.contact_point_type := null;
1448 if (p_email_rec.email_address is not NULL) then
1449
1450 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1451 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_email_contact_point API');
1452 END IF;
1453 l_contact_point_rec.contact_point_type := 'EMAIL';
1454
1455 -- calling TCA API to create_email_contact_points
1456
1457 HZ_CONTACT_POINT_V2PUB.create_email_contact_point (
1458 p_contact_point_rec => l_contact_point_rec,
1459 p_email_rec => p_email_rec,
1460 x_contact_point_id => l_contact_point_id,
1461 x_return_status => x_return_status,
1462 x_msg_count => x_msg_count,
1463 x_msg_data => x_msg_data);
1464 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1465 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_email_contact_point l_contact_point_id : '|| l_contact_point_id);
1466 END IF;
1467 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469 end if;
1470
1471 end if;
1472
1473 l_contact_point_rec.contact_point_type := null;
1474 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1475 IBE_UTIL.debug(' Workphone_Number : '|| Length(p_work_phone_rec.phone_number));
1476 END IF;
1477 if ( p_work_phone_rec.phone_number is not NULL) or (p_work_phone_rec.phone_number <> '' ) then
1478 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1479 IBE_UTIL.debug('Work Phone num is not null phone_Number : '|| p_work_phone_rec.phone_number);
1480 END IF;
1481 l_contact_point_rec.contact_point_type := 'PHONE';
1482 if (p_contact_point_purpose) then
1483 l_contact_point_rec.contact_point_purpose := 'BUSINESS';
1484 end if;
1485 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1486 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_phone_contact_point API');
1487 END IF;
1488 --l_contact_point_rec.primary_flag := 'Y';
1489 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
1490 p_contact_point_rec => l_contact_point_rec,
1491 p_phone_rec => p_work_phone_rec,
1492 x_contact_point_id => l_contact_point_id,
1493 x_return_status => x_return_status,
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data );
1496 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1497 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_phone_contact_point(work) - l_contact_point_id : '|| l_contact_point_id);
1498 END IF;
1499 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501 end if;
1502 end if;
1503
1504 l_contact_point_rec.contact_point_type := null;
1505 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1506 IBE_UTIL.debug('HOME_phone_Number : '|| p_home_phone_rec.phone_number);
1507 END IF;
1508 if ( p_home_phone_rec.phone_number is not NULL ) then
1509 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1510 IBE_UTIL.debug('Home Phone num is not null phone_Number : '|| p_home_phone_rec.phone_number);
1511 END IF;
1512 l_contact_point_rec.contact_point_type := 'PHONE';
1513 if (p_contact_point_purpose) then
1514 l_contact_point_rec.contact_point_purpose := 'PERSONAL';
1515 end if;
1516 --if (p_work_phone_rec.phone_number is null) then
1517 --l_contact_point_rec.primary_flag := 'Y';
1518 --end if;
1519 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1520 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_phone_contact_point API');
1521 END IF;
1522 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
1523 p_contact_point_rec => l_contact_point_rec,
1524 p_phone_rec => p_home_phone_rec,
1525 x_contact_point_id => l_contact_point_id,
1526 x_return_status => x_return_status,
1527 x_msg_count => x_msg_count,
1528 x_msg_data => x_msg_data );
1529 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1530 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (home)- l_contact_point_id : '|| l_contact_point_id);
1531 END IF;
1532 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1534 end if;
1535 end if;
1536 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1537 IBE_UTIL.debug('Fax_phone_Number : '|| p_fax_rec.phone_number);
1538 END IF;
1539 if ( p_fax_rec.phone_number is not NULL ) then
1540 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1541 IBE_UTIL.debug('Fax_phone_Number is not null ');
1542 END IF;
1543
1544 l_contact_point_rec.contact_point_type := 'PHONE';
1545 l_contact_point_rec.contact_point_purpose := null;
1546
1547 --if (p_work_phone_rec.phone_number is null) and (p_home_phone_rec.phone_number is null) then
1548 --l_contact_point_rec.primary_flag := 'Y';
1549 --end if;
1550 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1551 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_phone_contact_point API');
1552 END IF;
1553 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
1554 p_contact_point_rec => l_contact_point_rec,
1555 p_phone_rec => p_fax_rec,
1556 x_contact_point_id => l_contact_point_id,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data );
1560 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1561 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (fax)- l_contact_point_id : '|| l_contact_point_id);
1562 END IF;
1563 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1565 end if;
1566 end if;
1567 -- standard call to get message count and if count is 1, get message info
1568 FND_MSG_PUB.count_and_get(
1569 p_encoded => FND_API.G_FALSE,
1570 p_count => x_msg_count,
1571 p_data => x_msg_data
1572 );
1573
1574 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1575 IBE_UTIL.debug('exit ibe_party_v2.create_contact_points');
1576 END IF;
1577
1578
1579 EXCEPTION
1580
1581 WHEN FND_API.G_EXC_ERROR THEN
1582
1583
1584
1585 x_return_status := FND_API.G_RET_STS_ERROR;
1586 FND_MSG_PUB.ADD;
1587 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1588 p_data => x_msg_data,
1589 p_encoded => 'F');
1590
1591 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1592 IBE_UTIL.debug('G_EXC_ERROR exception');
1593 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1594 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1595 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1596 IBE_UTIL.debug('error text : '|| SQLERRM);
1597 END IF;
1598
1599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1600
1601
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1603 FND_MSG_PUB.ADD;
1604 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1605 p_data => x_msg_data,
1606 p_encoded => 'F');
1607
1608 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1609 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1610 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1611 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1615
1612 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1613 IBE_UTIL.debug('error text : '|| SQLERRM);
1614 END IF;
1616 WHEN OTHERS THEN
1617
1618
1619 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1620 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1621 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1622 FND_MSG_PUB.Add;
1623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1624 FND_MSG_PUB.Add;
1625 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1626 p_data => x_msg_data,
1627 p_encoded => 'F');
1628
1629 --IBE_UTIL.debug('OTHER exception');
1630 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1631 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1632 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1633 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1634 IBE_UTIL.debug('error text : '|| SQLERRM);
1635 END IF;
1636
1637
1638 END create_contact_points;
1639
1640 /*+===========================================================================
1641 | PROCEDURE NAME
1642 | Update_Contact_Preference
1643 |
1644 | DESCRIPTION
1645 | This API is called By Create_Individual_User,
1646 | Creat_Business_User
1647 |
1648 |
1649 | USAGE
1650 |
1651 | - Create Contact Prefernece in HZ_CONTACT_PREFERNECE_V2PUB
1652 |
1653 |
1654 | REFERENCED APIS
1655 | This API calls the following APIs
1656 | - HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference if no row exists
1657 | - HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference if row exists
1658 +=============================================================================*/
1659 PROCEDURE Update_Contact_Preference(
1660 p_party_id IN NUMBER,
1661 p_preference IN VARCHAR2,
1662 p_object_version_number IN NUMBER,
1663 p_created_by_module IN VARCHAR2,
1664 x_return_status OUT NOCOPY VARCHAR2,
1665 x_msg_count OUT NOCOPY NUMBER,
1666 x_msg_data OUT NOCOPY VARCHAR2
1667
1668 )
1669 IS
1670 l_contact_preference_rec hz_contact_preference_v2pub.contact_preference_rec_type;
1671 l_contact_preference_rec2 hz_contact_preference_v2pub.contact_preference_rec_type;
1672 l_contact_preference_id NUMBER;
1673 l_object_version_number NUMBER;
1674 l_id NUMBER;
1675
1676
1677 BEGIN
1678 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1679 IBE_UTIL.debug('enter ibe_party_v2pvt.update_contact_preference');
1680 END IF;
1681
1682 FND_MSG_PUB.initialize;
1683
1684
1685 --begin set/create contact preferences
1686 BEGIN
1687 SELECT contact_preference_id, object_version_number
1688 INTO l_id , l_object_version_number
1689 From hz_contact_preferences
1690 WHERE contact_level_table='HZ_PARTIES'
1691 AND contact_level_table_id=p_party_id;
1692
1693 --update reason_code if record found
1694 l_contact_preference_rec.contact_preference_id := l_id;
1695 l_contact_preference_rec.preference_code := p_preference;
1696 if (p_object_version_number is not null) then
1697 l_object_version_number := p_object_version_number;
1698 end if;
1699 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1700 IBE_UTIL.debug('Call HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference API');
1701 END IF;
1702 HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference(
1703 FND_API.G_FALSE,
1704 l_contact_preference_rec,
1705 l_object_version_number,
1706 x_return_status,
1707 x_msg_count,
1708 x_msg_data
1709 );
1710 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1711 IBE_UTIL.debug('After Call HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference API');
1712 END IF;
1713 --create row when no record found
1714 EXCEPTION WHEN NO_DATA_FOUND THEN
1715 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1716 IBE_UTIL.debug('Got No Data Found Exception in update_contact_preference');
1717 END IF;
1718
1719 l_contact_preference_rec2.contact_level_table := 'HZ_PARTIES';
1720 l_contact_preference_rec2.contact_level_table_id := p_party_id;
1721 l_contact_preference_rec2.contact_type := 'EMAIL';
1722 l_contact_preference_rec2.preference_code := p_preference;
1723 l_contact_preference_rec2.requested_by := 'INTERNAL';
1724 l_contact_preference_rec2.status := 'A';
1725 l_contact_preference_rec2.created_by_module := p_created_by_module;
1726
1727 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1728 IBE_UTIL.debug('Call HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference API');
1729 END IF;
1730 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
1731 FND_API.G_FALSE,
1732 l_contact_preference_rec2,
1733 l_contact_preference_id,
1734 x_return_status,
1735 x_msg_count,
1736 x_msg_data
1737 );
1738 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1739 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference API');
1740 END IF;
1741 END;
1742 --end set/create contact preferences
1743
1744
1745 -- standard call to get message count and if count is 1, get message info
1746 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1747 IBE_UTIL.debug('Before FND_MSG_API.count_and_get');
1748 END IF;
1749
1750 FND_MSG_PUB.count_and_get(
1754 );
1751 p_encoded => FND_API.G_FALSE,
1752 p_count => x_msg_count,
1753 p_data => x_msg_data
1755
1756 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1757 IBE_UTIL.debug('After FND_MSG_API.count_and_get');
1758 END IF;
1759
1760 --standard exception catching for main body
1761 EXCEPTION
1762 WHEN FND_API.G_EXC_ERROR THEN
1763 --IBE_UTIL.enable_debug();
1764
1765 x_return_status := FND_API.G_RET_STS_ERROR;
1766
1767 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1768 IBE_UTIL.debug('Before FND_MSG_API.count_and_get in Exception');
1769 END IF;
1770 FND_MSG_PUB.count_and_get(
1771 p_encoded => FND_API.G_FALSE,
1772 p_count => x_msg_count,
1773 p_data => x_msg_data
1774 );
1775 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1776 IBE_UTIL.debug('G_EXC_ERROR exception');
1777 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1778 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1779 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1780 IBE_UTIL.debug('error text : '|| SQLERRM);
1781 END IF;
1782
1783 --IBE_UTIL.disable_debug();
1784
1785 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1786 --IBE_UTIL.enable_debug();
1787
1788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1789 FND_MSG_PUB.count_and_get(
1790 p_encoded => FND_API.G_FALSE,
1791 p_count => x_msg_count,
1792 p_data => x_msg_data
1793 );
1794 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1795 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1796 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1797 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1798 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1799 IBE_UTIL.debug('error text : '|| SQLERRM);
1800 END IF;
1801
1802
1803 WHEN OTHERS THEN
1804
1805
1806
1807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1808 FND_MSG_PUB.count_and_get(
1809 p_encoded => FND_API.G_FALSE,
1810 p_count => x_msg_count,
1811 p_data => x_msg_data
1812 );
1813 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1814 IBE_UTIL.debug('OTHER exception');
1815 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1816 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1817 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1818 IBE_UTIL.debug('error text : '|| SQLERRM);
1819 END IF;
1820
1821
1822
1823 END Update_Contact_Preference;
1824
1825 /*+====================================================================
1826 | PROCEDURE NAME
1827 | Update_Person_Language
1828 |
1829 | DESCRIPTION
1830 | If the API finds a language preference for the given party then it updates
1831 | the primary indicator to 'N' then it sets the new language as the primary
1832 | language. If it does not find any language preference for the party then
1833 | it creates a new one and makrs it primary
1834 |
1835 | USAGE
1836 |
1837 | - Create Person Language preference in HZ_PERSON_INFO_V2PUB
1838 |
1839 |
1840 | REFERENCED APIS
1841 | This API calls the following APIs
1842 | - HZ_PERSON_INFO_V2PUB.create_person_language if no row exists
1843 | - HZ_PERSON_INFO_V2PUB.update_person_language if row exists
1844 +======================================================================*/
1845 PROCEDURE Update_Person_Language(
1846 p_party_id IN NUMBER,
1847 p_language_name IN VARCHAR2,
1848 p_created_by_module IN VARCHAR2,
1849 x_return_status OUT NOCOPY VARCHAR2,
1850 x_msg_count OUT NOCOPY NUMBER,
1851 x_msg_data OUT NOCOPY VARCHAR2
1852 )
1853 IS
1854 l_per_language_rec hz_person_info_v2pub.person_language_rec_type;
1855 l_id NUMBER;
1856 l_per_language_rec2 hz_person_info_v2pub.person_language_rec_type;
1857 l_language_use_reference_id number;
1858 l_object_version_number NUMBER;
1859
1860 BEGIN
1861
1862 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1863 IBE_UTIL.debug('enter ibe_party_v2pvt.update_person_language');
1864 END IF;
1865
1866 FND_MSG_PUB.initialize;
1867
1868
1869 --begin unset primary language indicator
1870 BEGIN
1871 SELECT language_use_reference_id, object_version_number
1872 INTO l_id, l_object_version_number
1873 FROM hz_person_language
1874 WHERE party_id=p_party_id and primary_language_indicator='Y';
1875
1876 l_per_language_rec.primary_language_indicator := 'N';
1877 l_per_language_rec.language_use_reference_id := l_id;
1878
1879 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1880 IBE_UTIL.debug('Call HZ_PERSON_INFO_V2PUB.update_person_language API to unset primary');
1881 END IF;
1882 hz_person_info_v2pub.update_person_language(
1883 p_person_language_rec => l_per_language_rec,
1884 p_object_version_number => l_object_version_number,
1885 x_return_status => x_return_status,
1886 x_msg_count => x_msg_count,
1887 x_msg_data => x_msg_data);
1888 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1889 IBE_UTIL.debug(' After Call HZ_PERSON_INFO_V2PUB.update_person_language APIto unset primary');
1890 END IF;
1891 EXCEPTION WHEN NO_DATA_FOUND THEN
1892 NULL;
1893 END;
1894 --end unset primary language indicator
1895
1896 --begin set primary language indicator
1897 BEGIN
1898 SELECT language_use_reference_id, object_version_number
1899 INTO l_id, l_object_version_number
1900 FROM hz_person_language
1901 WHERE party_id=p_party_id
1902 AND language_name=p_language_name
1906 l_per_language_rec2.language_use_reference_id := l_id;
1903 AND nvl(status,'A') = 'A';
1904
1905 l_per_language_rec2.primary_language_indicator := 'Y';
1907
1908
1909 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1910 IBE_UTIL.debug('Call HZ_PERSON_INFO_V2PUB.update_person_language API to set primary');
1911 END IF;
1912 hz_person_info_v2pub.update_person_language(
1913 p_person_language_rec => l_per_language_rec2,
1914 p_object_version_number => l_object_version_number,
1915 x_return_status => x_return_status,
1916 x_msg_count => x_msg_count,
1917 x_msg_data => x_msg_data);
1918
1919 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1920 IBE_UTIL.debug('After Call HZ_PERSON_INFO_V2PUB.update_person_language API to set primary');
1921 END IF;
1922 EXCEPTION WHEN NO_DATA_FOUND THEN
1923 l_per_language_rec2.primary_language_indicator := 'Y';
1924 l_per_language_rec2.party_id := p_party_id;
1925 l_per_language_rec2.language_name := p_language_name;
1926 l_per_language_rec2.created_by_module := p_created_by_module;
1927
1928 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1929 IBE_UTIL.debug('Call HZ_PERSON_INFO_V2PUB.create_person_language API to set primary');
1930 END IF;
1931 hz_person_info_v2pub.create_person_language(
1932 p_person_language_rec => l_per_language_rec2,
1933 x_language_use_reference_id => l_language_use_reference_id,
1934 x_return_status => x_return_status,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data);
1937 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1938 IBE_UTIL.debug('Call HZ_PERSON_INFO_V2PUB.create_person_language API to set primary');
1939 END IF;
1940
1941 END;
1942 --end set primary language indicator
1943
1944
1945
1946 -- standard call to get message count and if count is 1, get message info
1947 FND_MSG_PUB.count_and_get(
1948 p_encoded => FND_API.G_FALSE,
1949 p_count => x_msg_count,
1950 p_data => x_msg_data
1951 );
1952 --IBE_UTIL.disable_debug();
1953
1954 --standard exception catching for main body
1955 EXCEPTION
1956 WHEN FND_API.G_EXC_ERROR THEN
1957
1958 x_return_status := FND_API.G_RET_STS_ERROR;
1959 FND_MSG_PUB.count_and_get(
1960 p_encoded => FND_API.G_FALSE,
1961 p_count => x_msg_count,
1962 p_data => x_msg_data
1963 );
1964 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1965 IBE_UTIL.debug('G_EXC_ERROR exception');
1966 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1967 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1968 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1969 IBE_UTIL.debug('error text : '|| SQLERRM);
1970 END IF;
1971
1972
1973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1974
1975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1976 FND_MSG_PUB.count_and_get(
1977 p_encoded => FND_API.G_FALSE,
1978 p_count => x_msg_count,
1979 p_data => x_msg_data
1980 );
1981 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1982 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1983 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1984 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1985 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1986 IBE_UTIL.debug('error text : '|| SQLERRM);
1987 END IF;
1988
1989
1990 WHEN OTHERS THEN
1991
1992
1993 ROLLBACK TO person_language;
1994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1995 FND_MSG_PUB.count_and_get(
1996 p_encoded => FND_API.G_FALSE,
1997 p_count => x_msg_count,
1998 p_data => x_msg_data
1999 );
2000 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2001 IBE_UTIL.debug('OTHER exception');
2002 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2003 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2004 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2005 IBE_UTIL.debug('error text : '|| SQLERRM);
2006 END IF;
2007
2008
2009 END update_person_language;
2010
2011
2012
2013 /*+====================================================================
2014 | PROCEDURE NAME
2015 | Create_Account
2016 |
2017 | DESCRIPTION
2018 | This API is called by Create_Person,
2019 | Create_Organization
2020 |
2021 |
2022 | USAGE
2023 | - Create an Account for the Person or Organization
2024 |
2025 |
2026 | REFERENCED APIS
2027 | This API calls the following APIs
2028 | - HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account
2029 +======================================================================*/
2030 Procedure Create_Account(
2031 p_party_id IN NUMBER, -- person_party_id or org_party_id
2032 p_party_type IN VARCHAR2,
2033 p_created_by_module IN VARCHAR2,
2034 x_account_id OUT NOCOPY NUMBER,
2035 x_return_status OUT NOCOPY VARCHAR2,
2036 x_msg_count OUT NOCOPY NUMBER,
2037 x_msg_data OUT NOCOPY VARCHAR2) is
2038
2039 ddp_account_rec hz_cust_account_v2pub.cust_account_rec_type;
2040 ddp_person_rec hz_party_v2pub.person_rec_type;
2041 ddp_organization_rec hz_party_v2pub.organization_rec_type;
2042 ddp_cust_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
2043
2044 l_gen_cust_num VARCHAR2(1);
2045 l_acct_num VARCHAR2(30);
2046 l_account_number VARCHAR2(30);
2047 l_count NUMBER;
2048 l_account_id NUMBER;
2049 l_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2050 l_party_id NUMBER;
2054
2051 l_profile_id NUMBER;
2052
2053
2055 BEGIN
2056 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2057 IBE_UTIL.debug('enter ibe_party_v2pvt.create_account');
2058 END IF;
2059
2060 -- initialize message list if p_init_msg_list is set to TRUE.
2061 FND_MSG_PUB.initialize;
2062
2063 -- Initialize API return status to success
2064 x_return_status := FND_API.G_RET_STS_SUCCESS;
2065 BEGIN
2066 -- pass the account_number if auto generation is off
2067 l_gen_cust_num := HZ_MO_GLOBAL_CACHE.get_generate_customer_number();
2068 exception when no_data_found then
2069 l_gen_cust_num := 'N';
2070 END;
2071
2072 IF l_gen_cust_num <> 'Y' THEN
2073 -- always generate an account number
2074
2075 l_count := 1;
2076 WHILE l_count > 0 LOOP
2077
2078 SELECT hz_account_num_s.nextval INTO l_account_number
2079 FROM dual;
2080
2081
2082 SELECT COUNT(*) INTO l_count
2083 FROM hz_cust_accounts
2084 WHERE account_number = l_account_number;
2085
2086 END LOOP;
2087
2088
2089
2090 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2091 IBE_UTIL.debug('account number from sequence : ' || l_account_number);
2092 END IF;
2093
2094 ddp_account_rec.account_number := l_account_number;
2095 END IF;
2096
2097 ddp_account_rec.created_by_module := p_created_by_module;
2098 ddp_account_rec.status := 'A';
2099 ddp_account_rec.application_id := G_APPLICATION_ID;
2100
2101
2102 if p_party_type = 'P' then
2103 ddp_person_rec.party_rec.party_id := p_party_id;
2104 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2105 IBE_UTIL.debug('Call HZ_CUST_ACCOUNT_V2PUB.create_cust_account() API to create Person Account');
2106 END IF;
2107 HZ_CUST_ACCOUNT_V2PUB.create_cust_account(
2108 p_cust_account_rec =>ddp_account_rec,
2109 p_person_rec =>ddp_person_rec,
2110 p_customer_profile_rec=>ddp_cust_profile_rec,
2111 p_create_profile_amt => FND_API.G_FALSE,
2112 x_return_status =>x_return_status,
2113 x_msg_count =>x_msg_count,
2114 x_msg_data =>x_msg_data,
2115 x_cust_account_id => x_account_id,
2116 x_account_number => l_acct_num,
2117 x_party_id => l_party_id,
2118 x_party_number =>l_party_number,
2119 x_profile_id => l_profile_id);
2120
2121 elsif p_party_type = 'O' then
2122 ddp_organization_rec.party_rec.party_id := p_party_id;
2123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2124 IBE_UTIL.debug('Call HZ_CUST_ACCOUNT_V2PUB.create_cust_account() API to create Organization Account');
2125 END IF;
2126 HZ_CUST_ACCOUNT_V2PUB.create_cust_account(
2127 p_cust_account_rec =>ddp_account_rec,
2128 p_organization_rec =>ddp_organization_rec,
2129 p_customer_profile_rec=>ddp_cust_profile_rec,
2130 p_create_profile_amt => FND_API.G_FALSE,
2131 x_return_status =>x_return_status,
2132 x_msg_count =>x_msg_count,
2133 x_msg_data =>x_msg_data,
2134 x_cust_account_id => x_account_id,
2135 x_account_number => l_acct_num,
2136 x_party_id => l_party_id,
2137 x_party_number =>l_party_number,
2138 x_profile_id => l_profile_id);
2139 End if;
2140
2141 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2143 end if;
2144
2145 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2146 IBE_UTIL.debug('exit ibe_party_v2.create_account');
2147 END IF;
2148
2149
2150 EXCEPTION
2151
2152 WHEN FND_API.G_EXC_ERROR THEN
2153
2154 x_return_status := FND_API.G_RET_STS_ERROR;
2155 FND_MSG_PUB.ADD;
2156 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2157 p_data => x_msg_data,
2158 p_encoded => 'F');
2159
2160 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2161 IBE_UTIL.debug('G_EXC_ERROR exception');
2162 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2163 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2164 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2165 IBE_UTIL.debug('error text : '|| SQLERRM);
2166 END IF;
2167
2168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2169
2170
2171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2172 FND_MSG_PUB.ADD;
2173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2174 p_data => x_msg_data,
2175 p_encoded => 'F');
2176
2177 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2178 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
2179 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2180 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2181 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2182 IBE_UTIL.debug('error text : '|| SQLERRM);
2183 END IF;
2184
2185 WHEN OTHERS THEN
2186
2187
2188 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2189 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2190 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2191 FND_MSG_PUB.Add;
2192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193 FND_MSG_PUB.Add;
2194 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2195 p_data => x_msg_data,
2196 p_encoded => 'F');
2197
2198 --IBE_UTIL.debug('OTHER exception');
2199 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2200 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2204 END IF;
2201 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2202 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2203 IBE_UTIL.debug('error text : '|| SQLERRM);
2205 END create_account;
2206
2207 /*+====================================================================
2208 | PROCEDURE NAME
2209 | Update_Party_Status
2210 |
2211 | DESCRIPTION
2212 | This API is used to update party and all party_related TCA entity status
2213 |
2214 | USAGE
2215 | - Activate Party and Inactivate Party
2216 |
2217 | REFERENCED APIS
2218 | This API calls the following APIs to update the TCA Entity Status
2219 | - Hz_party_v2pub.update_person
2220 | - Hz_party_contact_v2pub.update_org_contact
2221 | - Hz_party_v2pub.update_organization
2222 | - Hz_cust_account_v2pub.update_cust_account
2223 | - Hz_cust_account_role_v2pub.update_cust_account_role
2224 | - Hz_contact_point_v2pub.update_contact_point
2225 | - Hz_party_site_v2pub.update_party_site
2226 | - HZ_.update_contact_preferences
2227 +======================================================================*/
2228 Procedure Update_Party_Status(
2229 p_party_id IN NUMBER,
2230 p_party_status IN VARCHAR2,
2231 p_change_org_status IN VARCHAR2 := FND_API.G_FALSE,
2232 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2233 x_return_status OUT NOCOPY VARCHAR2,
2234 x_msg_count OUT NOCOPY NUMBER,
2235 x_msg_data OUT NOCOPY VARCHAR2) is
2236
2237 --TCA Records for updation
2238 l_person_rec hz_party_v2pub.person_rec_type;
2239 l_org_rec hz_party_v2pub.organization_rec_type;
2240 l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
2241 l_account_rec hz_cust_account_v2pub.cust_account_rec_type;
2242 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
2243 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
2244 l_custacct_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
2245 l_cntct_pref_rec hz_contact_preference_v2pub.contact_preference_rec_type;
2246
2247 --Local variables
2248 l_party_type varchar2(30);
2249 l_party_status varchar2(1);
2250 l_contact_point_id number;
2251 l_cp_object_version_number number;
2252 l_org_id number;
2253 l_person_party_id number;
2254 l_person_profile_id number;
2255
2256 --Cursors for querying TCA Data
2257 cursor c_getPartyInfo(l_party_id number) IS
2258 select p.party_type, p.status, p.object_version_number,
2259 rel.relationship_id,rel.object_version_number rel_object_version_number,
2260 oc.org_contact_id,oc.object_version_number cont_object_version_number,
2261 a.cust_account_id,a.object_version_number acct_object_version_number
2262 from hz_parties p, hz_relationships rel,hz_cust_accounts a,hz_org_contacts oc
2263 where p.party_id = l_party_id and rel.party_id(+) = p.party_id and a.party_id(+) = p.party_id
2264 and oc.party_relationship_id(+) = rel.relationship_id and rownum <2;
2265 rec_party_info c_getPartyInfo%rowtype;
2266
2267 cursor c_getContactPoints(l_party_id number) IS
2268 select contact_point_id,object_version_number
2269 from hz_contact_points
2270 where owner_table_id = l_party_id
2271 and owner_table_name = 'HZ_PARTIES';
2272 rec_contact_point c_getContactPoints%rowtype;
2273
2274 cursor c_getPartySite(l_party_id number) IS
2275 select party_site_id,location_id,object_version_number
2276 from hz_party_sites
2277 where party_id = l_party_id;
2278 rec_party_site c_getPartySite%rowtype;
2279
2280 cursor c_getPersonOrgPartyId(l_party_id number) IS
2281 select subject_id,object_id from hz_relationships
2282 where party_id = l_party_id and subject_type = 'ORGANIZATION';
2283
2284 cursor c_getCustAcctRole(l_party_id number) IS
2285 select cust_account_role_id,object_version_number
2286 from hz_cust_account_roles
2287 where party_id = l_party_id;
2288 rec_custAcct_role c_getCustAcctRole%rowtype;
2289
2290 cursor c_getContactPref(l_party_id number) IS
2291 select contact_preference_id,object_version_number
2292 from hz_contact_preferences
2293 where contact_level_table_id = l_party_id;
2294 rec_cntct_pref c_getContactPref%rowtype;
2295
2296 BEGIN
2297 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2298 IBE_UTIL.debug('Begin ibe_party_v2pvt.update_party_status');
2299 END IF;
2300 -- initialize message list if p_init_msg_list is set to TRUE.
2301 FND_MSG_PUB.initialize;
2302 -- Initialize API return status to success
2303 x_return_status := FND_API.G_RET_STS_SUCCESS;
2304
2305 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2306 IBE_UTIL.debug('Input party_Id is '||p_party_id);
2307 END IF;
2308 --Get Party Details
2309 OPEN c_getPartyInfo(p_party_id);
2310 LOOP
2311 FETCH c_getPartyInfo into rec_party_info;
2312 l_party_type := rec_party_info.party_type;
2313 l_party_status := rec_party_info.status;
2314 EXIT WHEN c_getPartyInfo%notfound;
2315 END LOOP;
2316 CLOSE c_getPartyInfo;
2317 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2318 IBE_UTIL.debug('party_type is '||l_party_type);
2319 IBE_UTIL.debug('DB party_status is '||l_party_status);
2320 IBE_UTIL.debug('INPUT party_status is '||p_party_status);
2321 END IF;
2322 IF (p_party_status <> l_party_status)THEN
2323 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2324 IBE_UTIL.debug('DB Party Status and Input Party_Status are not equal,Begin Update');
2325 END IF;
2326 IF(l_party_type = 'PERSON') THEN
2330 l_person_rec.party_rec.party_id := p_party_id;
2327 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2328 IBE_UTIL.debug('Update Person Rec');
2329 END IF;
2331 l_person_rec.party_rec.status := p_party_status;
2332 hz_party_v2pub.update_person
2333 (p_person_rec => l_person_rec,
2334 p_party_object_version_number => rec_party_info.object_version_number,
2335 x_profile_id => l_person_profile_id,
2336 x_return_status => x_return_status,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data
2339 );
2340 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2341 IBE_UTIL.debug('x_msg_count from update_person'||x_msg_count);
2342 IBE_UTIL.debug('x_msg_data from update_person'||x_msg_data);
2343 END IF;
2344 ELSIF(l_party_type = 'PARTY_RELATIONSHIP') THEN
2345 --Org Contacts updation
2346 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2347 IBE_UTIL.debug('Update OrgContact with Party Rel');
2348 END IF;
2349 l_org_contact_rec.org_contact_id := rec_party_info.org_contact_id;
2350 l_org_contact_rec.party_rel_rec.relationship_id := rec_party_info.relationship_id;
2351 l_org_contact_rec.party_rel_rec.status := p_party_status;
2352 l_org_contact_rec.party_rel_rec.party_rec.party_id := p_party_id;
2353 l_org_contact_rec.party_rel_rec.party_rec.status := p_party_status;
2354 hz_party_contact_v2pub.update_org_contact
2355 (p_org_contact_rec => l_org_contact_rec,
2356 p_cont_object_version_number => rec_party_info.cont_object_version_number,
2357 p_rel_object_version_number => rec_party_info.rel_object_version_number,
2358 p_party_object_version_number => rec_party_info.object_version_number,
2359 x_return_status => x_return_status,
2360 x_msg_count => x_msg_count,
2361 x_msg_data => x_msg_data
2362 );
2363 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2364 IBE_UTIL.debug('x_msg_count from update_org_contact'||x_msg_count);
2365 IBE_UTIL.debug('x_msg_data from update_org_contact'||x_msg_data);
2366 END IF;
2367 --Person party entities and Org Party Entities should also be inactivate
2368 --So calling this same procedure again with Person PartyId/Org PartyId
2369 OPEN c_getPersonOrgPartyId(p_party_id);
2370 LOOP
2371 FETCH c_getPersonOrgPartyId into l_org_id,l_person_party_id;
2372 EXIT WHEN c_getPersonOrgPartyId%notfound;
2373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2374 IBE_UTIL.debug('update person_party_id'||l_person_party_id);
2375 END IF;
2376 update_party_status(l_person_party_id,p_party_status,FND_API.G_FALSE,FND_API.G_FALSE,x_return_status,x_msg_count,x_msg_data);
2377 IF (FND_API.To_Boolean(p_change_org_status))THEN
2378 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2379 IBE_UTIL.debug('change_org_status = True, update org_id'||l_org_id);
2380 END IF;
2381 update_party_status(l_org_id,p_party_status,FND_API.G_FALSE,FND_API.G_FALSE,x_return_status,x_msg_count,x_msg_data);
2382 END IF;
2383 END LOOP;
2384 CLOSE c_getPersonOrgPartyId;
2385 ELSIF(l_party_type = 'ORGANIZATION') THEN
2386 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2387 IBE_UTIL.debug('Update Organization');
2388 END IF;
2389 l_org_rec.party_rec.party_id := p_party_id;
2390 l_org_rec.party_rec.status := p_party_status;
2391 hz_party_v2pub.update_organization
2392 (p_organization_rec => l_org_rec,
2393 p_party_object_version_number => rec_party_info.object_version_number,
2394 x_profile_id => l_person_profile_id,
2395 x_return_status => x_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data
2398 );
2399 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2400 IBE_UTIL.debug('x_msg_count from update_org'||x_msg_count);
2401 IBE_UTIL.debug('x_msg_data from update_org'||x_msg_data);
2402 END IF;
2403 END IF;
2404 --Cust Account status updation
2405 IF(rec_party_info.cust_account_id IS NOT null) THEN
2406 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2407 IBE_UTIL.debug('Update Account Id'||rec_party_info.cust_account_id);
2408 END IF;
2409 l_account_rec.cust_account_id := rec_party_info.cust_account_id;
2410 l_account_rec.status := p_party_status;
2411 hz_cust_account_v2pub.update_cust_account
2412 (p_cust_account_rec => l_account_rec,
2413 p_object_version_number => rec_party_info.acct_object_version_number,
2414 x_return_status => x_return_status,
2415 x_msg_count => x_msg_count,
2416 x_msg_data => x_msg_data
2417 );
2418 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2419 IBE_UTIL.debug('x_msg_count from update_cust_account'||x_msg_count);
2420 IBE_UTIL.debug('x_msg_data from update_cust_account'||x_msg_data);
2421 END IF;
2422 END IF;
2423 --Cust Acct Role status update
2424 FOR rec_custAcct_role in c_getCustAcctRole(p_party_id) loop
2425 exit when c_getCustAcctRole%notfound;
2426 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2427 IBE_UTIL.debug('Update AcctRole Id'||rec_custAcct_role.cust_account_role_id);
2428 END IF;
2429 l_custacct_role_rec.cust_account_role_id := rec_custAcct_role.cust_account_role_id;
2430 l_custacct_role_rec.status := p_party_status;
2431 hz_cust_account_role_v2pub.update_cust_account_role
2435 x_msg_count => x_msg_count,
2432 (p_cust_account_role_rec => l_custacct_role_rec,
2433 p_object_version_number => rec_custAcct_role.object_version_number,
2434 x_return_status => x_return_status,
2436 x_msg_data => x_msg_data
2437 );
2438 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2439 IBE_UTIL.debug('x_msg_count from update_cust_account_role'||x_msg_count);
2440 IBE_UTIL.debug('x_msg_data from update_cust_account_role'||x_msg_data);
2441 END IF;
2442 end loop;
2443 --Contact Points status update
2444 FOR rec_contact_point in c_getContactPoints(p_party_id) loop
2445 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2446 IBE_UTIL.debug('Update Contact_Point Id'||rec_contact_point.contact_point_id);
2447 END IF;
2448 l_contact_point_rec.contact_point_id := rec_contact_point.contact_point_id;
2449 l_contact_point_rec.status := p_party_status;
2450 hz_contact_point_v2pub.update_contact_point
2451 (p_contact_point_rec => l_contact_point_rec,
2452 p_object_version_number => rec_contact_point.object_version_number,
2453 x_return_status => x_return_status,
2454 x_msg_count => x_msg_count,
2455 x_msg_data => x_msg_data
2456 );
2457 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2458 IBE_UTIL.debug('x_msg_count from update_contact_point'||x_msg_count);
2459 IBE_UTIL.debug('x_msg_data from update_contact_point'||x_msg_data);
2460 END IF;
2461 exit when c_getContactPoints%notfound;
2462 end loop;
2463
2464 --Party_Site status update
2465 FOR rec_party_site in c_getPartySite(p_party_id) loop
2466 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2467 IBE_UTIL.debug('Update PartySite Id'||rec_party_site.party_site_id);
2468 END IF;
2469 l_party_site_rec.party_site_id := rec_party_site.party_site_id;
2470 l_party_site_rec.status := p_party_status;
2471 hz_party_site_v2pub.update_party_site
2472 (p_party_site_rec => l_party_site_rec,
2473 p_object_version_number => rec_party_site.object_version_number,
2474 x_return_status => x_return_status,
2475 x_msg_count => x_msg_count,
2476 x_msg_data => x_msg_data
2477 );
2478 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2479 IBE_UTIL.debug('x_msg_count from update_party_site'||x_msg_count);
2480 IBE_UTIL.debug('x_msg_data from update_party_site'||x_msg_data);
2481 END IF;
2482 exit when c_getPartySite%notfound;
2483 end loop;
2484
2485 --Contact Preference status update
2486 FOR rec_cntct_pref in c_getContactPref(p_party_id) loop
2487 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2488 IBE_UTIL.debug('Update Preference Id'||rec_cntct_pref.contact_preference_id);
2489 END IF;
2490 l_cntct_pref_rec.contact_preference_id := rec_cntct_pref.contact_preference_id;
2491 l_cntct_pref_rec.status := p_party_status;
2492 hz_contact_preference_v2pub.update_contact_preference
2493 (p_contact_preference_rec => l_cntct_pref_rec,
2494 p_object_version_number => rec_cntct_pref.object_version_number,
2495 x_return_status => x_return_status,
2496 x_msg_count => x_msg_count,
2497 x_msg_data => x_msg_data
2498 );
2499 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2500 IBE_UTIL.debug('x_msg_count from update_contact_preference'||x_msg_count);
2501 IBE_UTIL.debug('x_msg_data from update_contact_preference'||x_msg_data);
2502 END IF;
2503 exit when c_getContactPref%notfound;
2504 end loop;
2505 END IF;
2506 IF FND_API.To_Boolean( p_commit ) THEN
2507 COMMIT WORK;
2508 END IF;
2509 EXCEPTION
2510 WHEN FND_API.G_EXC_ERROR THEN
2511 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2512 IBE_UTIL.debug('Expected Error');
2513 END IF;
2514 x_return_status := FND_API.G_RET_STS_ERROR;
2515 FND_MSG_PUB.ADD;
2516 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2517 p_data => x_msg_data,
2518 p_encoded => 'F');
2519
2520 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2521 IBE_UTIL.debug('G_EXC_ERROR exception');
2522 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2523 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2524 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2525 IBE_UTIL.debug('error text : '|| SQLERRM);
2526 END IF;
2527
2528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2529
2530 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2531 IBE_UTIL.debug('UnExpected Error');
2532 END IF;
2533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2534 FND_MSG_PUB.ADD;
2535 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2536 p_data => x_msg_data,
2537 p_encoded => 'F');
2538
2539 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2540 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
2541 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2542 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2543 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2544 IBE_UTIL.debug('error text : '|| SQLERRM);
2545 END IF;
2546
2547 WHEN OTHERS THEN
2548 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2549 IBE_UTIL.debug('Other Exception');
2550 END IF;
2551 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2552 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2553 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2554 FND_MSG_PUB.Add;
2555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559 p_encoded => 'F');
2556 FND_MSG_PUB.Add;
2557 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2558 p_data => x_msg_data,
2560
2561 --IBE_UTIL.debug('OTHER exception');
2562 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2563 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
2564 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
2565 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
2566 IBE_UTIL.debug('error text : '|| SQLERRM);
2567 END IF;
2568 END Update_Party_Status;
2569
2570 /*+====================================================================
2571 | PROCEDURE NAME
2572 | Find_Organization
2573 |
2574 | DESCRIPTION
2575 | Internal API
2576 | Find Organization based on org-party_id, org_party_number or
2577 | org_party_name
2578 |
2579 |
2580 | USAGE
2581 | Find Organization based on org-party_id, org_party_number or
2582 | org_party_name
2583 |
2584 | REFERENCED APIS
2585 |
2586 +======================================================================*/
2587 Function Find_Organization(
2588 x_org_id IN OUT NOCOPY NUMBER, -- org_party_id
2589 x_org_num IN OUT NOCOPY VARCHAR2,
2590 x_org_name IN OUT NOCOPY VARCHAR2) return boolean IS
2591
2592 cursor c_party_name IS
2593 select party_id, party_name, party_number from hz_parties where party_name = x_org_name;
2594 cursor c_party_num IS
2595 select party_id, party_name, party_number from hz_parties where party_number = x_org_num;
2596 cursor c_party_id IS
2597 select party_id, party_name, party_number from hz_parties where party_id = x_org_id;
2598
2599 ret_val boolean := false;
2600 l_party_rec c_party_name%rowtype;
2601
2602 l_org_name HZ_PARTIES.PARTY_NAME%TYPE := null;
2603 l_org_num HZ_PARTIES.PARTY_NUMBER%TYPE := null;
2604
2605 BEGIN
2606 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2607 IBE_UTIL.debug('enter ibe_party_v2pvt.find_organization');
2608 IBE_UTIL.debug('x_org_name: '|| x_org_name);
2609 IBE_UTIL.debug('x_org_num: '|| x_org_num);
2610 IBE_UTIL.debug('x_org_id: '|| x_org_id);
2611 END IF;
2612
2613
2614 if (x_org_name is not null) then
2615 l_org_name := x_org_name;
2616 end if;
2617 if (x_org_num is not null) then
2618 l_org_num := x_org_num;
2619 end if;
2620
2621 if (l_org_name is not null ) then
2622 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2623 IBE_UTIL.debug('Inside l_org_name not null');
2624 END IF;
2625 Open c_party_name;
2626 Fetch c_party_name into l_party_rec;
2627 If (c_party_name%FOUND) then
2628 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2629 IBE_UTIL.debug('Inside party_name found');
2630 END IF;
2631 x_org_num := l_party_rec.party_number;
2632 x_org_name := l_party_rec.party_name;
2633 x_org_id := l_party_rec.party_id;
2634 ret_val := true;
2635 end if;
2636 Close c_party_name;
2637 Elsif (l_org_num is not null) then
2638 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2639 IBE_UTIL.debug('Inside l_org_num not null');
2640 END IF;
2641 Open c_party_num;
2642 Fetch c_party_num into l_party_rec;
2643 If (c_party_num%FOUND) then
2644 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2645 IBE_UTIL.debug('Inside party_number found');
2646 END IF;
2647 x_org_num := l_party_rec.party_number;
2648 x_org_name := l_party_rec.party_name;
2649 x_org_id := l_party_rec.party_id;
2650 ret_val := true;
2651 end if;
2652 Close c_party_num;
2653
2654 Elsif ((x_org_id is not null) AND (x_org_id <> ' ') AND (x_org_id <> ''))then
2655 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2656 IBE_UTIL.debug('Inside l_org_id not null');
2657 END IF;
2658 Open c_party_id;
2659 Fetch c_party_id into l_party_rec;
2660 If (c_party_id%FOUND) then
2661 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2662 IBE_UTIL.debug('Inside party_id found');
2663 END IF;
2664 x_org_num := l_party_rec.party_number;
2665 x_org_name := l_party_rec.party_name;
2666 x_org_id := l_party_rec.party_id;
2667 ret_val := true;
2668 end if;
2669 Close c_party_num;
2670 End if;
2671
2672 --IBE_UTIL.debug('exit ibe_party_v2pvt.find_organization ret_val :' || ret_val);
2673 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2674 IBE_UTIL.debug('x_org_name: '|| x_org_name);
2675 IBE_UTIL.debug('x_org_num: '|| x_org_num);
2676 IBE_UTIL.debug('x_org_id: '|| x_org_id);
2677 END IF;
2678 --IBE_UTIL.debug('exit ibe_party_v2pvt.find_organization ret_val :' || ret_val));
2679 return ret_val;
2680 END Find_Organization;
2681
2682
2683
2684 /*+====================================================================
2685 | PROCEDURE NAME
2686 | Save_Tca_Entities
2687 |
2688 | DESCRIPTION
2689 | Internal API called by IBE_PARTY_V2PVT_W wrapper which in turn is
2690 | called by saveTcaEntities in PartV2Wrap.java
2691 |
2692 |
2693 | USAGE
2694 | This API is used for creating/updating a B2C user, Creating/Updating
2695 | a B2B user, Upgrading a B2C user into a B2B user.
2696 |
2697 | REFERENCED APIS
2698 | HZ_PARTY_V2PUB.create_person()
2699 | HZ_PARTY_V2PUB.update_person()
2700 | HZ_PARTY_V2PUB.create_organization()
2704 | HZ_PARTY_SITE_V2PUB.create_party_site()
2701 | HZ_PARTY_V2PUB.update_organization()
2702 | HZ_LOCATION_V2PUB.create_location()
2703 | HZ_LOCATION_V2PUB.update_location()
2705 | HZ_PARTY_CONTACT_V2PUB.create_org_contact()
2706 | HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference()
2707 | HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference()
2708 | HZ_CONTACT_POINT_V2PUB.create_email_contact_point (
2709 | HZ_CONTACT_POINT_V2PUB.update_email_contact_point (
2710 | HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
2711 | HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
2712 |
2713 +======================================================================*/
2714 Procedure Save_Tca_Entities(
2715 p_person_rec IN HZ_PARTY_V2PUB.person_rec_type,
2716 p_person_object_version_number IN NUMBER,
2717 p_email_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2718 p_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
2719 p_email_object_version_number IN NUMBER,
2720 p_workph_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2721 p_work_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
2722 p_workph_object_version_number IN NUMBER,
2723 p_homeph_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2724 p_home_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
2725 p_homeph_object_version_number IN NUMBER,
2726 p_fax_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2727 p_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
2728 p_fax_object_version_number IN NUMBER,
2729 p_contact_pref_rec IN HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type,
2730 p_cntct_pref_object_ver_num IN NUMBER,
2731 p_organization_rec IN HZ_PARTY_V2PUB.organization_rec_type,
2732 p_org_object_version_number IN NUMBER,
2733 p_location_rec IN HZ_LOCATION_V2PUB.location_rec_type,
2734 p_loc_object_version_number IN NUMBER,
2735 p_orgph_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2736 p_org_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
2737 p_orgph_object_version_number IN NUMBER,
2738 p_orgfax_contact_point_rec IN HZ_CONTACT_POINT_V2PUB.contact_point_rec_type,
2739 p_org_fax_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
2740 p_orgfax_object_version_number IN NUMBER,
2741 p_create_party_rel IN VARCHAR2,
2742 p_created_by_module IN VARCHAR2,
2743 x_person_party_id OUT NOCOPY NUMBER,
2744 x_rel_party_id OUT NOCOPY NUMBER,
2745 x_org_party_id OUT NOCOPY NUMBER,
2746 x_return_status OUT NOCOPY VARCHAR2,
2747 x_msg_count OUT NOCOPY NUMBER,
2748 x_msg_data OUT NOCOPY VARCHAR2
2749 )
2750 as
2751
2752 cursor c_get_party_relationship (c_subject_id NUMBER, c_object_id NUMBER) is
2753 SELECT party_id FROM hz_relationships
2754 WHERE subject_id = c_subject_id and subject_type = 'PERSON'
2755 and object_id = c_object_id and object_type = 'ORGANIZATION';
2756
2757 cursor c_get_contact_preference (c_party_id NUMBER,c_table_name VARCHAR2) is
2758 SELECT contact_preference_id,object_version_number FROM hz_contact_preferences
2759 WHERE contact_level_table_id = c_party_id and contact_level_table = c_table_name
2760 and contact_type = 'ALL';
2761
2762 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
2763 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
2764 l_location_rec HZ_LOCATION_V2PUB.location_rec_type := p_location_rec;
2765
2766 l_party_id NUMBER;
2767 l_person_profile_id NUMBER;
2768 l_person_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2769 l_org_profile_id NUMBER;
2770 l_org_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2771 l_org_contact_id NUMBER;
2772 l_party_rel_unq_id NUMBER; -- unique id of the party relationship
2773 l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2774 l_location_id NUMBER;
2775 l_contact_preference_id NUMBER;
2776 l_party_site_id NUMBER;
2777 l_party_site_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
2778
2779 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
2780 l_contact_pref_rec HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type := p_contact_pref_rec;
2781 l_person_rec HZ_PARTY_V2PUB.person_rec_type := p_person_rec;
2782 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type := p_organization_rec;
2783
2784 l_email_contact_point_id NUMBER;
2785 l_workph_contact_point_id NUMBER;
2786 l_homeph_contact_point_id NUMBER;
2787 l_fax_contact_point_id NUMBER;
2788 l_orgph_contact_point_id NUMBER;
2789 l_orgfax_contact_point_id NUMBER;
2790
2791 l_person_object_version_number NUMBER := p_person_object_version_number;
2792 l_email_object_version_number NUMBER := p_email_object_version_number;
2793 l_workph_object_version_number NUMBER := p_workph_object_version_number;
2794 l_homeph_object_version_number NUMBER := p_homeph_object_version_number;
2795 l_fax_object_version_number NUMBER := p_fax_object_version_number;
2796 l_cntct_pref_object_ver_num NUMBER := p_cntct_pref_object_ver_num;
2800 l_orgfax_object_version_number NUMBER := p_orgfax_object_version_number;
2797 l_org_object_version_number NUMBER := p_org_object_version_number;
2798 l_loc_object_version_number NUMBER := p_loc_object_version_number;
2799 l_orgph_object_version_number NUMBER := p_orgph_object_version_number;
2801
2802 l_email_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_email_contact_point_rec;
2803 l_workph_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_workph_contact_point_rec;
2804 l_homeph_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_homeph_contact_point_rec;
2805 l_fax_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_fax_contact_point_rec;
2806 l_orgph_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_orgph_contact_point_rec;
2807 l_orgfax_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type := p_orgfax_contact_point_rec;
2808
2809 l_org_name HZ_PARTIES.PARTY_NAME%TYPE := null;
2810
2811 l_person_party_id NUMBER := l_person_rec.party_rec.party_id;
2812 l_rel_party_id NUMBER := FND_API.G_MISS_NUM;
2813 l_org_party_id NUMBER := l_organization_rec.party_rec.party_id;
2814
2815 BEGIN
2816 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2817 IBE_UTIL.debug('enter ibe_party_v2pvt.Save_Tca_Entities');
2818 END IF;
2819
2820 -- initialize message list
2821 FND_MSG_PUB.initialize;
2822
2823 -- Initialize API return status to success
2824 x_return_status := FND_API.G_RET_STS_SUCCESS;
2825
2826 -- Create or Update Person
2827 IF ( l_person_rec.party_rec.party_id = FND_API.G_MISS_NUM or l_person_rec.party_rec.party_id is NULL) THEN
2828 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2829 IBE_UTIL.debug('Call HZ_PARTY_V2PUB.create_person () API');
2830 END IF;
2831 l_person_rec.created_by_module := p_created_by_module;
2832 HZ_PARTY_V2PUB.Create_Person(
2833 p_person_rec => l_person_rec,
2834 x_party_id => l_person_party_id,
2835 x_party_number => l_person_party_number,
2836 x_profile_id => l_person_profile_id,
2837 x_return_status => x_return_status,
2838 x_msg_count => x_msg_count,
2839 x_msg_data => x_msg_data);
2840 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2841 IBE_UTIL.debug('Completed Call HZ_PARTY_V2PUB.create_person () API');
2842 END IF;
2843 ELSIF ( l_person_object_version_number <> FND_API.G_MISS_NUM ) THEN
2844 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2845 IBE_UTIL.debug('Call HZ_PARTY_V2PUB.update_person () API');
2846 END IF;
2847
2848 l_person_rec.created_by_module := null;
2849 l_person_rec.application_id := null;
2850 l_person_rec.party_rec.party_number := null;
2851 l_person_rec.party_rec.orig_system_reference := null;
2852
2853 l_person_party_id := l_person_rec.party_rec.party_id;
2854
2855 HZ_PARTY_V2PUB.Update_Person(
2856 p_person_rec => l_person_rec,
2857 p_party_object_version_number => l_person_object_version_number,
2858 x_return_status => x_return_status,
2859 x_profile_id => l_person_profile_id,
2860 x_msg_count => x_msg_count,
2861 x_msg_data => x_msg_data);
2862
2863 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2864 IBE_UTIL.debug('Completed Call HZ_PARTY_V2PUB.update_person () API');
2865 END IF;
2866 END IF;
2867
2868 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2869 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update person - x_return_status : '|| x_return_status);
2870 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update person - x_msg_count : '|| x_msg_count);
2871 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update person - x_msg_data :' || x_msg_data);
2872 END IF;
2873
2874 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2876 END IF;
2877
2878 -- Create or Update Organization
2879 IF ( l_organization_rec.party_rec.party_id = FND_API.G_MISS_NUM Or l_organization_rec.party_rec.party_id is NULL) THEN
2880 IF( l_organization_rec.organization_name is not null AND l_organization_rec.organization_name <> FND_API.G_MISS_CHAR) THEN
2881 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2882 IBE_UTIL.debug('Call HZ_PARTY_V2PUB.create_organization () API');
2883 END IF;
2884
2885 l_organization_rec.created_by_module := p_created_by_module;
2886 l_organization_rec.application_id := G_APPLICATION_ID;
2887 HZ_PARTY_V2PUB.Create_Organization (
2888 p_organization_rec => l_organization_rec,
2889 x_return_status => x_return_status,
2890 x_msg_count => x_msg_count,
2891 x_msg_data => x_msg_data,
2892 x_party_id => l_org_party_id,
2893 x_party_number => l_org_party_number,
2894 x_profile_id => l_org_profile_id);
2895
2896 l_organization_rec.party_rec.party_id := l_org_party_id;
2897
2898 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2899 IBE_UTIL.debug('Completed Call HZ_PARTY_V2PUB.create_organization () API'||l_organization_rec.party_rec.party_id);
2900 END IF;
2901 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2902 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_return_status : '|| x_return_status);
2903 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_msg_count : '|| x_msg_count);
2904 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_msg_data :' || x_msg_data);
2905 END IF;
2906 END IF;
2907 ELSIF ( l_org_object_version_number <> FND_API.G_MISS_NUM ) THEN
2908 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2909 IBE_UTIL.debug('Call HZ_PARTY_V2PUB.update_organization () API');
2910 END IF;
2911
2912 l_organization_rec.created_by_module := null;
2913 l_organization_rec.application_id := null;
2914 l_organization_rec.party_rec.orig_system_reference := null;
2915 HZ_PARTY_V2PUB.Update_Organization (
2916 p_organization_rec => l_organization_rec,
2917 p_party_object_version_number => l_org_object_version_number,
2918 x_profile_id => l_org_profile_id,
2919 x_return_status => x_return_status,
2920 x_msg_count => x_msg_count,
2921 x_msg_data => x_msg_data);
2922
2923 l_org_party_id := l_organization_rec.party_rec.party_id;
2924
2925 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2926 IBE_UTIL.debug('Completed Call HZ_PARTY_V2PUB.update_organization () API');
2927 END IF;
2928
2929 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2930 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_return_status : '|| x_return_status);
2931 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_msg_count : '|| x_msg_count);
2932 IBE_UTIL.debug('After call to HZ_PARTY_V2PUB.create/update organization - x_msg_data :' || x_msg_data);
2933 END IF;
2934 END IF;
2935
2936 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2938 END IF;
2939 IF ( (l_org_party_id is null OR l_org_party_id=FND_API.G_MISS_NUM)
2940 AND l_organization_rec.party_rec.party_number is not null AND l_organization_rec.party_rec.party_number <> FND_API.G_MISS_CHAR) THEN
2941
2942 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2943 IBE_UTIL.debug('Getting the org_id for the party_number: '|| l_organization_rec.party_rec.party_number );
2944 END IF;
2945
2946 IF(Find_Organization(
2947 x_org_id => l_org_party_id,
2948 x_org_num => l_organization_rec.party_rec.party_number,
2949 x_org_name => l_org_name)) THEN
2950
2951 l_organization_rec.party_rec.party_id := l_org_party_id;
2952
2953 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2954 IBE_UTIL.debug('After call to Find_Organization - x_org_party_id : '|| l_org_party_id);
2955 IBE_UTIL.debug('After call to Find_Organization - x_org_num : '|| l_organization_rec.party_rec.party_number);
2956 IBE_UTIL.debug('After call to Find_Organization - x_org_name :' || l_org_name);
2957 END IF;
2958 END IF;
2959 END IF;
2960
2961 -- Create Location and Party Site
2962 IF(l_organization_rec.party_rec.party_id is not null AND l_organization_rec.party_rec.party_id <> FND_API.G_MISS_NUM
2963 AND l_location_rec.Address1 is not null AND l_location_rec.Address1 <> FND_API.G_MISS_CHAR
2964 AND l_location_rec.Country is not null AND l_location_rec.Country <> FND_API.G_MISS_CHAR
2965 ) THEN -- Creating Location only for Organization in B2B flow.
2966 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2967 IBE_UTIL.debug('Before Saving the Location - country: ' || l_location_rec.country);
2968 IBE_UTIL.debug('Before Saving the Location - address1: ' || l_location_rec.address1);
2969 END IF;
2970 IF ( l_location_rec.location_id = FND_API.G_MISS_NUM OR l_location_rec.location_id is NULL) THEN
2971 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2972 IBE_UTIL.debug('Call IBE_ADDRESS_V2PVT.create_address API');
2973 END IF;
2974
2975 l_location_rec.created_by_module := p_created_by_module;
2976 l_location_rec.application_id := G_APPLICATION_ID;
2977
2978 l_party_site_rec.party_id := l_org_party_id;
2979 l_party_site_rec.created_by_module := p_created_by_module;
2980 l_party_site_rec.application_id := G_APPLICATION_ID;
2981
2982 IBE_ADDRESS_V2PVT.create_address(
2983 p_api_version => 1.0,
2984 p_init_msg_list => null,
2985 p_commit => null,
2986 p_location => l_location_rec,
2987 p_party_site => l_party_site_rec,
2991 p_shipto => FND_API.G_TRUE,
2988 p_primary_billto => FND_API.G_TRUE,
2989 p_primary_shipto => FND_API.G_TRUE,
2990 p_billto => FND_API.G_TRUE,
2992 p_default_primary => FND_API.G_TRUE,
2993 x_location_id => l_location_id,
2994 x_party_site_id => l_party_site_id,
2995 x_return_status => x_return_status,
2996 x_msg_count => x_msg_count,
2997 x_msg_data => x_msg_data);
2998
2999 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3000 IBE_UTIL.debug('After Call to IBE_ADDRESS_V2PVT.create_address API');
3001 END IF;
3002
3003 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3004 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_return_status : '|| x_return_status);
3005 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_msg_count : '|| x_msg_count);
3006 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - x_msg_data :' || x_msg_data);
3007 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - l_location_id : '|| l_location_id);
3008 IBE_UTIL.debug('After call to IBE_ADDRESS_V2PVT.create_address - l_party_site_id : '|| l_party_site_id);
3009 END IF;
3010
3011 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3013 END IF;
3014
3015 ELSIF ( l_loc_object_version_number <> FND_API.G_MISS_NUM ) THEN
3016 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3017 IBE_UTIL.debug('Call HZ_LOCATION_V2PUB.update_location () API');
3018 END IF;
3019
3020 HZ_LOCATION_V2PUB.update_location (
3021 p_location_rec => l_location_rec,
3022 p_object_version_number => l_loc_object_version_number,
3023 x_return_status => x_return_status,
3024 x_msg_count => x_msg_count,
3025 x_msg_data => x_msg_data);
3026
3027 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3028 IBE_UTIL.debug('After Call to HZ_LOCATION_V2PUB.update_location API');
3029 END IF;
3030
3031 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3032 IBE_UTIL.debug('After call to HZ_LOCATION_V2PUB.update_location - x_return_status : '|| x_return_status);
3033 IBE_UTIL.debug('After call to HZ_LOCATION_V2PUB.update_location - x_msg_count : '|| x_msg_count);
3034 IBE_UTIL.debug('After call to HZ_LOCATION_V2PUB.update_location - x_msg_data :' || x_msg_data);
3035 END IF;
3036
3037 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3039 END IF;
3040 END IF;
3041 END IF; -- if(Organization Name is not NULL)
3042
3043 IF (p_create_party_rel = 'Y') THEN
3044 OPEN c_get_party_relationship(l_person_party_id, l_org_party_id);
3045 FETCH c_get_party_relationship into l_rel_party_id;
3046 IF c_get_party_relationship%NOTFOUND or l_rel_party_id is null or l_rel_party_id=FND_API.G_MISS_NUM THEN
3047 l_party_rel_rec.subject_id := l_person_party_id;
3048 l_party_rel_rec.subject_type := 'PERSON';
3049 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
3050
3051 -- pass organization_party_id as object_id
3052 l_party_rel_rec.object_id := l_org_party_id;
3053 l_party_rel_rec.object_type := 'ORGANIZATION';
3054 l_party_rel_rec.object_table_name := 'HZ_PARTIES';
3055
3056 l_party_rel_rec.relationship_type := 'EMPLOYMENT';
3057 l_party_rel_rec.relationship_code := 'EMPLOYEE_OF';
3058
3059 l_party_rel_rec.start_date:= sysdate;
3060 l_party_rel_rec.created_by_module := P_CREATED_BY_MODULE;
3061 l_party_rel_rec.application_id := G_APPLICATION_ID;
3062
3063 l_org_contact_rec.party_rel_rec := l_party_rel_rec;
3064 l_org_contact_rec.created_by_module := P_CREATED_BY_MODULE;
3065 l_org_contact_rec.application_id := G_APPLICATION_ID;
3066
3067
3068 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3069 IBE_UTIL.debug('Call HZ_PARTY_CONTACT_V2PUB.create_org_contact () API');
3070 END IF;
3071
3072 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
3073 p_org_contact_rec => l_org_contact_rec,
3074 x_org_contact_id => l_org_contact_id,
3075 x_party_rel_id => l_party_rel_unq_id,
3076 x_party_id => l_rel_party_id,
3077 x_party_number => l_rel_party_number,
3078 x_return_status => x_return_status,
3079 x_msg_count => x_msg_count,
3080 x_msg_data => x_msg_data);
3081
3082 l_party_id := l_rel_party_id;
3083
3084 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3085 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_return_status : '|| x_return_status);
3086 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_msg_count : '|| x_msg_count);
3087 IBE_UTIL.debug('After call to HZ_PARTY_CONTACT_V2PUB.create_org_contact - x_msg_data :' || x_msg_data);
3088 END IF;
3089
3090 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3092 END IF;
3093 ELSE
3094 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3095 IBE_UTIL.debug('Party relationship already exists with party_id: ' || l_rel_party_id);
3096 END IF;
3097 l_party_id := l_rel_party_id;
3101 --Find Party_Rel if org_party_id is available
3098 END IF;
3099 CLOSE c_get_party_relationship;
3100 ELSE
3102 IF (l_person_party_id <> FND_API.G_MISS_NUM and l_person_party_id is not null and l_org_party_id <> FND_API.G_MISS_NUM and l_org_party_id is not null) THEN
3103 OPEN c_get_party_relationship(l_person_party_id, l_org_party_id);
3104 FETCH c_get_party_relationship into l_rel_party_id;
3105 CLOSE c_get_party_relationship;
3106 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3107 IBE_UTIL.debug('New Find Party Relationship output '||l_rel_party_id);
3108 END IF;
3109 END IF;
3110 IF (l_rel_party_id is not null and l_rel_party_id <> FND_API.G_MISS_NUM) then
3111 l_party_id := l_rel_party_id;
3112 ELSE
3113 l_party_id := l_person_party_id;
3114 END IF;
3115 END IF; -- p_create_party_rel = 'Y'
3116 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3117 IBE_UTIL.debug('Going to use this l_party_id for contact_point/contact pref creation'||l_party_id);
3118 END IF;
3119
3120 -- Find existing contact preferences
3121 IF ( l_contact_pref_rec.contact_preference_id = FND_API.G_MISS_NUM or l_contact_pref_rec.contact_preference_id is NULL AND
3122 (l_contact_pref_rec.preference_code is not null AND l_contact_pref_rec.preference_code <> FND_API.G_MISS_CHAR)) THEN
3123
3124 IF ( l_contact_pref_rec.contact_level_table is NULL or l_contact_pref_rec.contact_level_table = FND_API.G_MISS_CHAR ) THEN
3125 l_contact_pref_rec.contact_level_table := 'HZ_PARTIES';
3126 END IF;
3127
3128 IF ( l_contact_pref_rec.contact_level_table_id is NULL or l_contact_pref_rec.contact_level_table_id = FND_API.G_MISS_NUM ) THEN
3129 l_contact_pref_rec.contact_level_table_id := l_party_id;
3130 END IF;
3131
3132 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3133 IBE_UTIL.debug('Find Exisiting Contact Preference Record for: ' || l_contact_pref_rec.contact_level_table_id ||',' || l_contact_pref_rec.contact_level_table);
3134 END IF;
3135
3136 --Execute teh cursor
3137 OPEN c_get_contact_preference(l_contact_pref_rec.contact_level_table_id,l_contact_pref_rec.contact_level_table);
3138 FETCH c_get_contact_preference into l_contact_pref_rec.contact_preference_id,l_cntct_pref_object_ver_num;
3139 CLOSE c_get_contact_preference;
3140
3141 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3142 IBE_UTIL.debug('After Finding Exisiting Contact Preference Record : ' || l_contact_pref_rec.contact_preference_id || ',' || l_cntct_pref_object_ver_num);
3143 END IF;
3144 END IF;
3145
3146 -- Create contact preference
3147 IF ( l_contact_pref_rec.contact_preference_id = FND_API.G_MISS_NUM or l_contact_pref_rec.contact_preference_id is NULL AND
3148 (l_contact_pref_rec.preference_code is not null AND l_contact_pref_rec.preference_code <> FND_API.G_MISS_CHAR)) THEN
3149 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3150 IBE_UTIL.debug('Call HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference API');
3151 END IF;
3152 l_contact_pref_rec.contact_type := 'ALL';
3153 l_contact_pref_rec.requested_by := 'INTERNAL';
3154 l_contact_pref_rec.status := 'A';
3155 l_contact_pref_rec.created_by_module := p_created_by_module;
3156
3157
3158 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
3159
3160 p_contact_preference_rec => l_contact_pref_rec,
3161 x_contact_preference_id => l_contact_preference_id,
3162 x_return_status => x_return_status,
3163 x_msg_count => x_msg_count,
3164 x_msg_data => x_msg_data);
3165 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3166 IBE_UTIL.debug('After Call to HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference API');
3167 END IF;
3168 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3169 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference - x_return_status : '|| x_return_status);
3170 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference - x_msg_count : '|| x_msg_count);
3171 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference - x_msg_data :' || x_msg_data);
3172 END IF;
3173 ELSIF ( l_cntct_pref_object_ver_num <> FND_API.G_MISS_NUM ) THEN
3174 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3175 IBE_UTIL.debug('Call HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference API');
3176 END IF;
3177
3178 HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference(
3179 p_contact_preference_rec => l_contact_pref_rec,
3180 p_object_version_number => l_cntct_pref_object_ver_num,
3181 x_return_status => x_return_status,
3182 x_msg_count => x_msg_count,
3183 x_msg_data => x_msg_data);
3184 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3185 IBE_UTIL.debug('After Call to HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference API');
3186 END IF;
3187 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3188 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference - x_return_status : '|| x_return_status);
3189 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference - x_msg_count : '|| x_msg_count);
3190 IBE_UTIL.debug('After call to HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference - x_msg_data :' || x_msg_data);
3191 END IF;
3192 END IF;
3193
3194 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196 END IF;
3200 IBE_UTIL.debug('Email address is not null, Email Address: ' || p_email_rec.email_address);
3197 -- Create Party/Relationship Contact Points
3198 if (p_email_rec.email_address is not NULL and p_email_rec.email_address <> FND_API.G_MISS_CHAR) then
3199 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3201 END IF;
3202 -- calling TCA API to create_email_contact_points
3203 IF (l_email_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_email_contact_point_rec.contact_point_id is NULL) THEN
3204 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3205 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_email_contact_point API');
3206 END IF;
3207
3208 l_email_contact_point_rec.status := 'A';
3209 l_email_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3210 l_email_contact_point_rec.created_by_module := p_created_by_module;
3211 l_email_contact_point_rec.application_id := G_APPLICATION_ID;
3212 l_email_contact_point_rec.contact_point_type := 'EMAIL';
3213 l_email_contact_point_rec.contact_point_purpose := null;
3214
3215 IF ( l_email_contact_point_rec.owner_table_id = FND_API.G_MISS_NUM or l_email_contact_point_rec.owner_table_id is null) THEN
3216 l_email_contact_point_rec.owner_table_id := l_party_id;
3217 END IF;
3218
3219 HZ_CONTACT_POINT_V2PUB.create_email_contact_point (
3220 p_contact_point_rec => l_email_contact_point_rec,
3221 p_email_rec => p_email_rec,
3222 x_contact_point_id => l_email_contact_point_id,
3223 x_return_status => x_return_status,
3224 x_msg_count => x_msg_count,
3225 x_msg_data => x_msg_data);
3226
3227 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3228 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_email_contact_point');
3229 END IF;
3230
3231 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3232 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_email_contact_point - x_return_status : '|| x_return_status);
3233 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_email_contact_point - x_msg_count : '|| x_msg_count);
3234 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_email_contact_point - x_msg_data :' || x_msg_data);
3235 END IF;
3236 ELSIF ( l_email_object_version_number <> FND_API.G_MISS_NUM ) THEN
3237 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3238 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_email_contact_point API');
3239 END IF;
3240 IBE_UTIL.debug('Email Object Version Number' || l_email_object_version_number);
3241 HZ_CONTACT_POINT_V2PUB.update_email_contact_point (
3242 p_contact_point_rec => l_email_contact_point_rec,
3243 p_email_rec => p_email_rec,
3244 p_object_version_number => l_email_object_version_number,
3245 x_return_status => x_return_status,
3246 x_msg_count => x_msg_count,
3247 x_msg_data => x_msg_data);
3248
3249 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3250 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_email_contact_point');
3251 END IF;
3252
3253 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3254 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_email_contact_point - x_return_status : '|| x_return_status);
3255 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_email_contact_point - x_msg_count : '|| x_msg_count);
3256 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_email_contact_point - x_msg_data :' || x_msg_data);
3257 END IF;
3258 END IF;
3259
3260 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3262 end if;
3263 end if;
3264
3265
3266 -- Create Person/Contact WorkPhoneNumber
3267 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3268 IBE_UTIL.debug(' Workphone_Number : '|| Length(p_work_phone_rec.phone_number));
3269 END IF;
3270
3271 if ( (p_work_phone_rec.phone_number is not NULL) and ( Length(p_work_phone_rec.phone_number) > 0 ) and (p_work_phone_rec.phone_number <> FND_API.G_MISS_CHAR) ) then
3272 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3273 IBE_UTIL.debug('Work Phone num is not null phone_Number : '|| p_work_phone_rec.phone_number);
3274 END IF;
3275
3276 IF (l_workph_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_workph_contact_point_rec.contact_point_id is NULL) THEN
3277 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3278 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_workph_contact_point API');
3279 END IF;
3280
3281 l_workph_contact_point_rec.status := 'A';
3282 l_workph_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3283 l_workph_contact_point_rec.created_by_module := p_created_by_module;
3284 l_workph_contact_point_rec.application_id := G_APPLICATION_ID;
3285 l_workph_contact_point_rec.contact_point_type := 'PHONE';
3286 l_workph_contact_point_rec.contact_point_purpose := 'BUSINESS';
3287
3288 IF ( l_workph_contact_point_rec.owner_table_id= FND_API.G_MISS_NUM or l_workph_contact_point_rec.owner_table_id is null) THEN
3289 l_workph_contact_point_rec.owner_table_id := l_party_id;
3290 END IF;
3291
3292 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
3293 p_contact_point_rec => l_workph_contact_point_rec,
3294 p_phone_rec => p_work_phone_rec,
3295 x_contact_point_id => l_workph_contact_point_id,
3296 x_return_status => x_return_status,
3300 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3297 x_msg_count => x_msg_count,
3298 x_msg_data => x_msg_data);
3299
3301 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_workph_contact_point');
3302 END IF;
3303
3304 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3305 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_workph_contact_point - x_return_status : '|| x_return_status);
3306 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_workph_contact_point - x_msg_count : '|| x_msg_count);
3307 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_workph_contact_point - x_msg_data :' || x_msg_data);
3308 END IF;
3309 ELSIF ( l_workph_object_version_number <> FND_API.G_MISS_NUM ) THEN
3310 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3311 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_workph_contact_point API');
3312 END IF;
3313
3314 HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
3315 p_contact_point_rec => l_workph_contact_point_rec,
3316 p_phone_rec => p_work_phone_rec,
3317 p_object_version_number => l_workph_object_version_number,
3318 x_return_status => x_return_status,
3319 x_msg_count => x_msg_count,
3320 x_msg_data => x_msg_data);
3321
3322 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3323 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_workph_contact_point');
3324 END IF;
3325
3326 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3327 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_workph_contact_point - x_return_status : '|| x_return_status);
3328 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_workph_contact_point - x_msg_count : '|| x_msg_count);
3329 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_workph_contact_point - x_msg_data :' || x_msg_data);
3330 END IF;
3331 END IF;
3332
3333 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3335 end if;
3336 end if;
3337
3338
3339 -- Create Person/Contact PersonalPhoneNumber
3340 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3341 IBE_UTIL.debug(' Homephone_Number : '|| Length(p_home_phone_rec.phone_number));
3342 END IF;
3343
3344 if ( (p_home_phone_rec.phone_number is not NULL) and ( Length(p_home_phone_rec.phone_number) > 0 ) and (p_home_phone_rec.phone_number <> FND_API.G_MISS_CHAR) ) then
3345 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3346 IBE_UTIL.debug('Home Phone num is not null phone_Number : '|| p_home_phone_rec.phone_number);
3347 END IF;
3348
3349 IF (l_homeph_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_homeph_contact_point_rec.contact_point_id is NULL) THEN
3350 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3351 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_homeph_contact_point API');
3352 END IF;
3353
3354 l_homeph_contact_point_rec.status := 'A';
3355 l_homeph_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3356 l_homeph_contact_point_rec.created_by_module := p_created_by_module;
3357 l_homeph_contact_point_rec.application_id := G_APPLICATION_ID;
3358 l_homeph_contact_point_rec.contact_point_type := 'PHONE';
3359 l_homeph_contact_point_rec.contact_point_purpose := 'PERSONAL';
3360
3361 IF ( l_homeph_contact_point_rec.owner_table_id = FND_API.G_MISS_NUM or l_homeph_contact_point_rec.owner_table_id is null) THEN
3362 l_homeph_contact_point_rec.owner_table_id := l_party_id;
3363 END IF;
3364
3365 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
3366 p_contact_point_rec => l_homeph_contact_point_rec,
3367 p_phone_rec => p_home_phone_rec,
3368 x_contact_point_id => l_homeph_contact_point_id,
3369 x_return_status => x_return_status,
3370 x_msg_count => x_msg_count,
3371 x_msg_data => x_msg_data);
3372
3373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3374 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_homeph_contact_point');
3375 END IF;
3376
3377 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3378 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_homeph_contact_point - x_return_status : '|| x_return_status);
3379 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_homeph_contact_point - x_msg_count : '|| x_msg_count);
3380 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_homeph_contact_point - x_msg_data :' || x_msg_data);
3381 END IF;
3382 ELSIF ( l_homeph_object_version_number <> FND_API.G_MISS_NUM ) THEN
3383 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3384 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_homeph_contact_point API');
3385 END IF;
3386
3387 HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
3388 p_contact_point_rec => l_homeph_contact_point_rec,
3389 p_phone_rec => p_home_phone_rec,
3390 p_object_version_number => l_homeph_object_version_number,
3391 x_return_status => x_return_status,
3392 x_msg_count => x_msg_count,
3393 x_msg_data => x_msg_data);
3394
3395 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3396 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_homeph_contact_point');
3397 END IF;
3398
3399 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3400 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_homeph_contact_point - x_return_status : '|| x_return_status);
3401 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_homeph_contact_point - x_msg_count : '|| x_msg_count);
3402 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_homeph_contact_point - x_msg_data :' || x_msg_data);
3403 END IF;
3404 END IF;
3405
3406 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3408 end if;
3409 end if;
3410
3411 -- Create Person/Contact Fax Number
3412 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3413 IBE_UTIL.debug(' Fax Number : '|| Length(p_fax_rec.phone_number));
3414 END IF;
3415
3416 if ( (p_fax_rec.phone_number is not NULL) and ( Length(p_fax_rec.phone_number) > 0 ) and (p_fax_rec.phone_number <> FND_API.G_MISS_CHAR) ) then
3417 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3418 IBE_UTIL.debug('Fax num is not null phone_Number : '|| p_fax_rec.phone_number);
3419 END IF;
3420
3421 IF (l_fax_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_fax_contact_point_rec.contact_point_id is NULL) THEN
3422 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3423 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_fax_contact_point API');
3424 END IF;
3425
3426 l_fax_contact_point_rec.status := 'A';
3427 l_fax_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3428 l_fax_contact_point_rec.created_by_module := p_created_by_module;
3429 l_fax_contact_point_rec.application_id := G_APPLICATION_ID;
3430 l_fax_contact_point_rec.contact_point_type := 'PHONE';
3431 l_fax_contact_point_rec.contact_point_purpose := null;
3432
3433 IF ( l_fax_contact_point_rec.owner_table_id = FND_API.G_MISS_NUM or l_fax_contact_point_rec.owner_table_id is null) THEN
3434 l_fax_contact_point_rec.owner_table_id := l_party_id;
3435 END IF;
3436
3437 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
3438 p_contact_point_rec => l_fax_contact_point_rec,
3439 p_phone_rec => p_fax_rec,
3440 x_contact_point_id => l_fax_contact_point_id,
3441 x_return_status => x_return_status,
3442 x_msg_count => x_msg_count,
3443 x_msg_data => x_msg_data);
3444
3445 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3446 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_fax_contact_point');
3447 END IF;
3448
3449 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3450 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_fax_contact_point - x_return_status : '|| x_return_status);
3451 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_fax_contact_point - x_msg_count : '|| x_msg_count);
3452 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_fax_contact_point - x_msg_data :' || x_msg_data);
3453 END IF;
3454 ELSIF ( l_fax_object_version_number <> FND_API.G_MISS_NUM ) THEN
3455 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3456 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_fax_contact_point API');
3457 END IF;
3458
3459 HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
3460 p_contact_point_rec => l_fax_contact_point_rec,
3461 p_phone_rec => p_fax_rec,
3462 p_object_version_number => l_fax_object_version_number,
3463 x_return_status => x_return_status,
3464 x_msg_count => x_msg_count,
3465 x_msg_data => x_msg_data);
3466
3467 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3468 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_fax_contact_point');
3469 END IF;
3470
3471 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3472 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_fax_contact_point - x_return_status : '|| x_return_status);
3473 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_fax_contact_point - x_msg_count : '|| x_msg_count);
3474 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_fax_contact_point - x_msg_data :' || x_msg_data);
3475 END IF;
3476 END IF;
3477
3478 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3480 end if;
3481 end if;
3482
3483 -- Create Organization Phone Number
3484
3485 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3486 IBE_UTIL.debug(' org_phone_Number : '|| Length(p_org_phone_rec.phone_number));
3487 END IF;
3488
3492 END IF;
3489 if ( (p_org_phone_rec.phone_number is not NULL) and ( Length(p_org_phone_rec.phone_number) > 0 ) and (p_org_phone_rec.phone_number <> FND_API.G_MISS_CHAR) ) then
3490 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3491 IBE_UTIL.debug('Org Phone num is not null phone_Number : '|| p_org_phone_rec.phone_number);
3493
3494
3495 IF ( l_orgph_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_orgph_contact_point_rec.contact_point_id is NULL) THEN
3496
3497 l_orgph_contact_point_rec.status := 'A';
3498 l_orgph_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3499 l_orgph_contact_point_rec.created_by_module := p_created_by_module;
3500 l_orgph_contact_point_rec.application_id := G_APPLICATION_ID;
3501 l_orgph_contact_point_rec.contact_point_type := 'PHONE';
3502 l_orgph_contact_point_rec.contact_point_purpose := 'BUSINESS';
3503
3504 IF ( l_org_party_id is not NULL AND l_org_party_id <> FND_API.G_MISS_NUM ) THEN
3505
3506 IF( l_orgph_contact_point_rec.owner_table_id = FND_API.G_MISS_NUM or l_orgph_contact_point_rec.owner_table_id is null) THEN
3507 l_orgph_contact_point_rec.owner_table_id := l_org_party_id;
3508 END IF;
3509
3510 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3511 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_orgPh_contact_point API');
3512 END IF;
3513
3514 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
3515 p_contact_point_rec => l_orgph_contact_point_rec,
3516 p_phone_rec => p_org_phone_rec,
3517 x_contact_point_id => l_orgph_contact_point_id,
3518 x_return_status => x_return_status,
3519 x_msg_count => x_msg_count,
3520 x_msg_data => x_msg_data);
3521
3522 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3523 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgPh_contact_point');
3524 END IF;
3525
3526 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3527 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgPh_contact_point - x_return_status : '|| x_return_status);
3528 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgPh_contact_point - x_msg_count : '|| x_msg_count);
3529 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgPh_contact_point - x_msg_data :' || x_msg_data);
3530 END IF;
3531 END IF;
3532 ELSIF ( l_orgph_contact_point_rec.contact_point_id <> FND_API.G_MISS_NUM or l_orgph_contact_point_rec.contact_point_id is not NULL
3533 AND l_orgph_object_version_number <> FND_API.G_MISS_NUM ) THEN
3534 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3535 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_orgPh_contact_point API');
3536 END IF;
3537
3538 HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
3539 p_contact_point_rec => l_orgph_contact_point_rec,
3540 p_phone_rec => p_org_phone_rec,
3541 p_object_version_number => l_orgph_object_version_number,
3542 x_return_status => x_return_status,
3543 x_msg_count => x_msg_count,
3544 x_msg_data => x_msg_data);
3545
3546 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3547 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgPh_contact_point');
3548 END IF;
3549
3550 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3551 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgPh_contact_point - x_return_status : '|| x_return_status);
3552 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgPh_contact_point - x_msg_count : '|| x_msg_count);
3553 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgPh_contact_point - x_msg_data :' || x_msg_data);
3554 END IF;
3555 END IF;
3556
3557 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3559 end if;
3560 end if;
3561
3562 -- Create Organization Fax Number
3563
3564 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3565 IBE_UTIL.debug(' Org_fax_Number : '|| Length(p_org_fax_rec.phone_number));
3566 END IF;
3567
3568
3569 if ( (p_org_fax_rec.phone_number is not NULL) and ( Length(p_org_fax_rec.phone_number) > 0 ) and (p_org_fax_rec.phone_number <> FND_API.G_MISS_CHAR)) then
3570 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3571 IBE_UTIL.debug('Fax num is not null phone_Number : '|| p_org_fax_rec.phone_number);
3572 END IF;
3573
3574 IF (l_orgfax_contact_point_rec.contact_point_id = FND_API.G_MISS_NUM or l_orgfax_contact_point_rec.contact_point_id is NULL) THEN
3575
3576 l_orgfax_contact_point_rec.status := 'A';
3577 l_orgfax_contact_point_rec.owner_table_name := 'HZ_PARTIES';
3578 l_orgfax_contact_point_rec.created_by_module := p_created_by_module;
3579 l_orgfax_contact_point_rec.application_id := G_APPLICATION_ID;
3580 l_orgfax_contact_point_rec.contact_point_type := 'PHONE';
3581 l_orgfax_contact_point_rec.contact_point_purpose := null;
3582
3583 IF ( l_org_party_id is not NULL AND l_org_party_id <> FND_API.G_MISS_NUM ) THEN
3584 IF (l_orgfax_contact_point_rec.owner_table_id = FND_API.G_MISS_NUM or l_orgfax_contact_point_rec.owner_table_id is null) THEN
3585 l_orgfax_contact_point_rec.owner_table_id := l_org_party_id;
3586 END IF;
3587
3588 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3589 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.create_orgFax_contact_point API');
3590 END IF;
3591
3592 HZ_CONTACT_POINT_V2PUB.create_phone_contact_point (
3593 p_contact_point_rec => l_orgfax_contact_point_rec,
3594 p_phone_rec => p_org_fax_rec,
3595 x_contact_point_id => l_orgfax_contact_point_id,
3596 x_return_status => x_return_status,
3597 x_msg_count => x_msg_count,
3598 x_msg_data => x_msg_data);
3599
3600 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3601 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgFax_contact_point');
3602 END IF;
3603
3604 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3605 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgFax_contact_point - x_return_status : '|| x_return_status);
3606 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgFax_contact_point - x_msg_count : '|| x_msg_count);
3607 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.create_orgFax_contact_point - x_msg_data :' || x_msg_data);
3608 END IF;
3609 END IF;
3610 ELSIF ( l_orgfax_contact_point_rec.owner_table_id <> FND_API.G_MISS_NUM AND l_orgfax_contact_point_rec.owner_table_id is not null
3611 AND l_orgfax_object_version_number <> FND_API.G_MISS_NUM ) THEN
3612 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3613 IBE_UTIL.debug('Call HZ_CONTACT_POINT_V2PUB.update_orgFax_contact_point API');
3614 END IF;
3615
3616 HZ_CONTACT_POINT_V2PUB.update_phone_contact_point (
3617 p_contact_point_rec => l_orgfax_contact_point_rec,
3618 p_phone_rec => p_org_fax_rec,
3619 p_object_version_number => l_orgfax_object_version_number,
3620 x_return_status => x_return_status,
3621 x_msg_count => x_msg_count,
3622 x_msg_data => x_msg_data);
3623
3624 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3625 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgFax_contact_point');
3626 END IF;
3627
3628 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3629 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgFax_contact_point - x_return_status : '|| x_return_status);
3630 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgFax_contact_point - x_msg_count : '|| x_msg_count);
3631 IBE_UTIL.debug('After call to HZ_CONTACT_POINT_V2PUB.update_orgFax_contact_point - x_msg_data :' || x_msg_data);
3632 END IF;
3633 END IF;
3634
3635 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3637 end if;
3638 end if;
3639
3640 x_person_party_id := l_person_party_id;
3641 x_rel_party_id := l_rel_party_id;
3642 x_org_party_id := l_org_party_id;
3643
3644 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3645 IBE_UTIL.debug('Before completing the process in IBE_PARTY_V2PVT.Save_Tca_Entites - x_person_party_id: '|| x_person_party_id);
3646 IBE_UTIL.debug('Before completing the process in IBE_PARTY_V2PVT.Save_Tca_Entites - x_rel_party_id: '|| x_rel_party_id);
3647 IBE_UTIL.debug('Before completing the process in IBE_PARTY_V2PVT.Save_Tca_Entites - x_org_party_id:' || x_org_party_id);
3648 IBE_UTIL.debug('IBE_PARTY_V2PVT.Save_Tca_Entites: Completing the call to Save_Tca_Entties');
3649 END IF;
3650
3651 EXCEPTION
3652
3653 WHEN FND_API.G_EXC_ERROR THEN
3654
3655
3656
3657 x_return_status := FND_API.G_RET_STS_ERROR;
3658 FND_MSG_PUB.ADD;
3659 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3660 p_data => x_msg_data,
3661 p_encoded => 'F');
3662
3663 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3664 IBE_UTIL.debug('G_EXC_ERROR exception');
3665 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
3666 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
3667 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
3668 IBE_UTIL.debug('error text : '|| SQLERRM);
3669 END IF;
3670
3671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3672
3673
3674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3675 FND_MSG_PUB.ADD;
3676 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3677 p_data => x_msg_data,
3678 p_encoded => 'F');
3679
3680 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3681 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
3682 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
3683 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
3684 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
3685 IBE_UTIL.debug('error text : '|| SQLERRM);
3686 END IF;
3687
3688 WHEN OTHERS THEN
3689
3690
3691 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
3692 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
3693 FND_MESSAGE.Set_Token('REASON', SQLERRM);
3694 FND_MSG_PUB.Add;
3695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3696 FND_MSG_PUB.Add;
3697 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3698 p_data => x_msg_data,
3699 p_encoded => 'F');
3700
3701 --IBE_UTIL.debug('OTHER exception');
3702 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3703 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
3704 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
3705 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
3706 IBE_UTIL.debug('error text : '|| SQLERRM);
3707 END IF;
3708
3709 END Save_Tca_Entities;
3710
3711
3712 END IBE_PARTY_V2PVT;