[Home] [Help]
PACKAGE BODY: APPS.WSH_SITE_CONTACT_PERSON_PKG
Source
1 PACKAGE BODY WSH_SITE_CONTACT_PERSON_PKG as
2 /* $Header: WSHSPTHB.pls 120.1.12010000.2 2008/09/18 08:55:44 sankarun ship $ */
3
4 /*============================================================================
5 Procedure: CREATE_CONTACTPERSON
6 Purpose : Creates another party for the contact person.
7 =============================================================================*/
8
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SITE_CONTACT_PERSON_PKG';
11 --
12
13 PROCEDURE Create_ContactPerson(
14 P_CARRIER_PARTY_ID IN NUMBER,
15 P_STATUS IN VARCHAR2,
16 P_PERSON_NAME_PRE_ADJUNCT IN VARCHAR2,
17 P_PERSON_FIRST_NAME IN VARCHAR2,
18 P_PERSON_LAST_NAME IN VARCHAR2,
19 P_CARRIER_SITE_ID IN NUMBER,
20 P_CONTACT_NUMBER IN VARCHAR2,
21 P_LOAD_TENDER_FLAG IN VARCHAR2,
22 P_JOB_TITLE IN VARCHAR2,
23 P_EMAIL_ADDRESS IN VARCHAR2,
24 X_ORG_CONTACT_ID OUT NOCOPY NUMBER,
25 X_PERSON_ID OUT NOCOPY NUMBER,
26 X_RELATIONSHIP_ID OUT NOCOPY NUMBER,
27 X_CONTACT_POINT_ID OUT NOCOPY NUMBER,
28 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
29 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
30 X_POSITION OUT NOCOPY NUMBER,
31 X_PROCEDURE OUT NOCOPY VARCHAR2,
32 X_SQLERR OUT NOCOPY VARCHAR2,
33 X_SQL_CODE OUT NOCOPY VARCHAR2
34 )
35 IS
36
37 -------------------------
38 -- General Declarations.
39 -------------------------
40
41 l_return_status varchar2(100);
42 l_msg_count number;
43 l_position number;
44 l_call_procedure varchar2(100);
45 l_msg_data varchar2(2000);
46 l_party_number varchar2(100);
47 l_profile_id number;
48 l_relationship_id number;
49 l_exception_msg varchar2(1000);
50 HZ_FAIL_EXCEPTION exception;
51 l_party_relationship_id number;
52 l_contact_point_id number;
53 l_org_contact_id number;
54 l_party_id number;
55
56 ---------------------------------------------
57 -- Declarations for Party 'PERSON' Creation.
58 ---------------------------------------------
59
60 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
61 l_person_party_id number;
62
63 l_rel_rec_type HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
64 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
65 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
66 l_email_rec_type hz_contact_point_v2pub.email_rec_type;
67
68 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
69 l_hz_profile_option varchar2(2);
70 l_hz_profile_set boolean;
71
72
73 --
74 l_debug_on BOOLEAN;
75 --
76 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CONTACTPERSON';
77 --
78
79 BEGIN
80
81 -- Initialize the status to SUCCESS.
82
83 --
84 -- Debug Statements
85 --
86 --
87 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
88 --
89 IF l_debug_on IS NULL
90 THEN
91 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92 END IF;
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.push(l_module_name);
96 --
97 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_PARTY_ID',P_CARRIER_PARTY_ID);
98 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
99 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME_PRE_ADJUNCT',P_PERSON_NAME_PRE_ADJUNCT);
100 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_FIRST_NAME',P_PERSON_FIRST_NAME);
101 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_LAST_NAME',P_PERSON_LAST_NAME);
102 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_SITE_ID',P_CARRIER_SITE_ID);
103 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_NUMBER',P_CONTACT_NUMBER);
104 WSH_DEBUG_SV.log(l_module_name,'P_LOAD_TENDER_FLAG',P_LOAD_TENDER_FLAG);
105 WSH_DEBUG_SV.log(l_module_name,'P_JOB_TITLE',P_JOB_TITLE);
106 WSH_DEBUG_SV.log(l_module_name,'P_EMAIL_ADDRESS',P_EMAIL_ADDRESS);
107 END IF;
108 --
109
110 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
111
112 -- Initialize Messages.
113 fnd_msg_pub.initialize();
114
115
116 -- Put Information into per_rec.
117
118 l_per_rec.person_pre_name_adjunct := P_PERSON_NAME_PRE_ADJUNCT;
119 l_per_rec.person_first_name := P_PERSON_FIRST_NAME;
120 l_per_rec.person_last_name := P_PERSON_LAST_NAME;
121 l_per_rec.created_by_module := 'ORACLE_SHIPPING';
122 l_per_rec.party_rec.status := P_STATUS;
123
124 l_position := 10;
125 l_call_procedure := 'Calling TCA API Create_Person';
126
127 --
128 -- Debug Statements
129 --
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.Create_Person',WSH_DEBUG_SV.C_PROC_LEVEL);
132 END IF;
133 --
134 -- Set the Autogenerate Party Number to 'Yes'.
135 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
136 l_hz_profile_set := false;
137 l_hz_profile_option := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
138
139 IF (l_hz_profile_option = 'N' or l_hz_profile_option is null ) THEN
140 IF l_debug_on THEN
141 WSH_DEBUG_SV.logmsg(l_module_name,'Setting profile option HZ_GENERATE_PARTY_NUMBER to Yes');
142 END IF;
143 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
144 l_hz_profile_set := true;
145 END IF;
146
147 HZ_PARTY_V2PUB.Create_Person
148 (
149 p_init_msg_list => FND_API.G_TRUE,
150 p_person_rec => l_per_rec,
151 x_party_id => l_person_party_id,
152 x_party_number => l_party_number,
153 x_profile_id => l_profile_id,
154 x_return_status => l_return_status,
155 x_msg_count => l_msg_count,
156 x_msg_data => l_msg_data
157 );
158
159 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to previous value
160 IF l_hz_profile_set THEN
161 IF l_debug_on THEN
162 WSH_DEBUG_SV.logmsg(l_module_name,'Reverting the value of profile option HZ_GENERATE_PARTY_NUMBER');
163 END IF;
164 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER',l_hz_profile_option);
165 END IF;
166
167 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
168 x_return_status := l_return_status;
169 RAISE HZ_FAIL_EXCEPTION;
170 END IF;
171
172 x_person_id := l_person_party_id;
173
174 -- Create Org Contact
175
176 l_ocon_rec.party_rel_rec.subject_id := l_person_party_id;
177 l_ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
178 l_ocon_rec.party_rel_rec.subject_type := 'PERSON';
179 l_ocon_rec.party_rel_rec.object_id := p_carrier_party_id;
180 l_ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
181 l_ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
182 l_ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
183 l_ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
184 l_ocon_rec.party_rel_rec.status := 'A';
185 l_ocon_rec.party_rel_rec.start_date := sysdate;
186 l_ocon_rec.party_rel_rec.created_by_module := 'ORACLE_SHIPPING';
187 l_ocon_rec.party_site_id := p_carrier_site_id;
188 l_ocon_rec.decision_maker_flag := p_load_tender_flag;
189 l_ocon_rec.job_title_code := p_job_title;
190 l_ocon_rec.created_by_module := 'ORACLE_SHIPPING';
191 l_ocon_rec.contact_number := P_CONTACT_NUMBER;
192
193 -- Bug 7391414 Setting the profile option 'HZ GENERATE CONTACT NUMBER' to No if it is Yes or Null
194 l_hz_profile_set := false;
195 l_hz_profile_option := fnd_profile.value('HZ_GENERATE_CONTACT_NUMBER');
196
197 IF (l_hz_profile_option = 'Y' or l_hz_profile_option is null ) THEN
198 IF l_debug_on THEN
199 WSH_DEBUG_SV.logmsg(l_module_name,'Setting profile option HZ_GENERATE_CONTACT_NUMBER to No');
200 END IF;
201 fnd_profile.put('HZ_GENERATE_CONTACT_NUMBER','N');
202 l_hz_profile_set := true;
203 END IF;
204
205 l_position := 20;
206 l_call_procedure := 'Calling TCA API Create_Org_Contact';
207
208 --
209 -- Debug Statements
210 --
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_CONTACT_V2PUB.create_org_contact',WSH_DEBUG_SV.C_PROC_LEVEL);
213 END IF;
214 --
215
216 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
217 p_org_contact_rec => l_ocon_rec,
218 x_org_contact_id => l_org_contact_id,
219 x_party_rel_id => l_party_relationship_id,
220 x_party_id => l_party_id,
221 x_party_number => l_party_number,
222 x_return_status => l_return_status,
223 x_msg_count => l_msg_count,
224 x_msg_data => l_msg_data );
225
226 -- Bug 7391414 Setting the profile option 'HZ GENERATE CONTACT NUMBER' to previous value
227 IF l_hz_profile_set THEN
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.logmsg(l_module_name,'Reverting the value of profile option HZ_GENERATE_CONTACT_NUMBER');
230 END IF;
231 fnd_profile.put('HZ_GENERATE_CONTACT_NUMBER',l_hz_profile_option);
232 END IF;
233
234 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
235 RAISE HZ_FAIL_EXCEPTION;
236 END IF;
237
238 -- Create a EMAIL contact point
239
240 l_contact_points_rec_type.contact_point_type := 'EMAIL';
241 l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
242 l_contact_points_rec_type.owner_table_id := l_party_id;
243 l_contact_points_rec_type.primary_flag := 'Y';
244 l_contact_points_rec_type.status := p_status;
245 l_contact_points_rec_type.created_by_module := 'ORACLE_SHIPPING';
246
247 l_email_rec_type.email_address := p_email_address;
248
249 l_call_procedure := 'Calling TCA API Create_Contact_Points';
250
251 --
252 -- Debug Statements
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_CONTACT_POINT_V2PUB.Create_Contact_Point',WSH_DEBUG_SV.C_PROC_LEVEL);
256 END IF;
257 --
258 -- Bug 2754631
259 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point (
260 p_init_msg_list => FND_API.G_TRUE,
261 p_contact_point_rec => l_contact_points_rec_type,
262 p_email_rec => l_email_rec_type,
263 x_contact_point_id => l_contact_point_id,
264 x_return_status => l_return_status,
265 x_msg_count => l_msg_count,
266 x_msg_data => l_msg_data);
267
268
269 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
270 RAISE HZ_FAIL_EXCEPTION;
271 END IF;
272
273 X_ORG_CONTACT_ID := l_org_contact_id;
274 X_RELATIONSHIP_ID := l_party_relationship_id;
275 X_CONTACT_POINT_ID := l_contact_point_id;
276
277 --
278 -- Debug Statements
279 --
280 IF l_debug_on THEN
281 WSH_DEBUG_SV.pop(l_module_name);
282 END IF;
283 --
284
285 EXCEPTION
286 WHEN HZ_FAIL_EXCEPTION THEN
287 x_exception_msg := l_msg_data;
288 x_position := l_position;
289 x_procedure := l_call_procedure;
290 x_sqlerr := sqlerrm;
291 x_sql_code := sqlcode;
292
293 --
294 -- Debug Statements
295 --
296 IF l_debug_on THEN
297 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
298 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
299 END IF;
300 --
301
302 END CREATE_CONTACTPERSON;
303
304
305 PROCEDURE UPDATE_CONTACTPERSON(
306 P_PERSON_PARTY_ID IN NUMBER,
307 P_PERSON_NAME_PRE_ADJUNCT IN VARCHAR2,
308 P_PERSON_FIRST_NAME IN VARCHAR2,
309 P_PERSON_LAST_NAME IN VARCHAR2,
310 P_CONTACT_NUMBER IN VARCHAR2,
311 P_JOB_TITLE IN VARCHAR2,
312 P_EMAIL_ADDRESS IN VARCHAR2,
313 P_STATUS IN VARCHAR2,
314 P_LOAD_TENDER_FLAG IN VARCHAR2,
315 P_ORG_CONTACT_ID IN NUMBER,
316 P_CONTACT_POINT_ID IN NUMBER,
317 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
318 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
319 X_POSITION OUT NOCOPY NUMBER,
320 X_PROCEDURE OUT NOCOPY VARCHAR2,
321 X_SQLERR OUT NOCOPY VARCHAR2,
322 X_SQL_CODE OUT NOCOPY VARCHAR2 )
323
324 IS
325
326 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
327 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
328 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
329 l_email_rec_type hz_contact_point_v2pub.email_rec_type;
330
331 l_return_status varchar2(100);
332 l_msg_count number;
333 l_msg_data varchar2(2000);
334 l_profile_id number;
335 l_object_version_number number;
336 l_contact_object_version number;
337 l_rel_object_version number;
338 l_party_object_version number;
339 HZ_FAIL_EXCEPTION exception;
340 l_position number;
341 l_call_procedure varchar2(100);
342 l_cont_point_version number;
343
344 CURSOR Get_Object_Version_Number(p_person_party_id NUMBER) IS
345 select object_version_number
346 from hz_parties
347 where party_id = p_person_party_id;
348
349 CURSOR Get_Contact_Object_Version(p_org_contact_id NUMBER) IS
350 select object_version_number
351 from hz_org_contacts
352 where org_contact_id = p_org_contact_id;
353
354 CURSOR Get_Cont_Point_Version IS
355 select object_version_number
356 from hz_contact_points
357 where contact_point_id = p_contact_point_id;
358
359 --
360 l_debug_on BOOLEAN;
361 --
362 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTACTPERSON';
363 --
364
365 BEGIN
366
367 --Initialize the status to SUCCESS.
368
369 --
370 -- Debug Statements
371 --
372 --
373 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
374 --
375 IF l_debug_on IS NULL
376 THEN
377 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
378 END IF;
379 --
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.push(l_module_name);
382 --
383 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_PARTY_ID',P_PERSON_PARTY_ID);
384 WSH_DEBUG_SV.log(l_module_name,'P_ORG_CONTACT_ID',P_ORG_CONTACT_ID);
385 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
386 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME_PRE_ADJUNCT',P_PERSON_NAME_PRE_ADJUNCT);
387 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_FIRST_NAME',P_PERSON_FIRST_NAME);
388 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_LAST_NAME',P_PERSON_LAST_NAME);
389 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_NUMBER',P_CONTACT_NUMBER);
390 WSH_DEBUG_SV.log(l_module_name,'P_LOAD_TENDER_FLAG',P_LOAD_TENDER_FLAG);
391 WSH_DEBUG_SV.log(l_module_name,'P_JOB_TITLE',P_JOB_TITLE);
392 WSH_DEBUG_SV.log(l_module_name,'P_EMAIL_ADDRESS',P_EMAIL_ADDRESS);
393 END IF;
394 --
395
396 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
397
398 --Initialize Messages.
399
400 fnd_msg_pub.initialize();
401
402 --Put Information into person_rec.
403
404 l_person_rec.person_pre_name_adjunct := nvl(p_person_name_pre_adjunct, fnd_api.g_miss_char);
405 l_person_rec.person_first_name := nvl(p_person_first_name, fnd_api.g_miss_char);
406 l_person_rec.person_last_name := p_person_last_name;
407 l_person_rec.party_rec.party_id := p_person_party_id;
408 l_person_rec.party_rec.status := p_status;
409
410 --Get Object_Version_Number for the Person.
411
412 OPEN Get_Object_Version_Number(p_person_party_id);
413 FETCH Get_Object_Version_Number INTO l_object_version_number;
414 CLOSE Get_Object_Version_Number ;
415
416 --Update the Person information.
417
418 l_position := 10;
419 l_call_procedure := 'Calling TCA API Update_Person';
420
421 --
422 -- Debug Statements
423 --
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.Update_Person',WSH_DEBUG_SV.C_PROC_LEVEL);
426 END IF;
427 --
428
429 HZ_PARTY_V2PUB.Update_Person
430 (
431 p_init_msg_list => FND_API.G_TRUE,
432 p_person_rec => l_person_rec,
433 p_party_object_version_number => l_object_version_number,
434 x_profile_id => l_profile_id,
435 x_return_status => l_return_status,
436 x_msg_count => l_msg_count,
437 x_msg_data => l_msg_data
438 );
439
440 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
441 x_return_status := l_return_status;
442 RAISE HZ_FAIL_EXCEPTION;
443 END IF;
444
445 OPEN Get_Contact_Object_Version(p_org_contact_id);
446 FETCH Get_Contact_Object_Version INTO l_contact_object_version;
447 CLOSE Get_Contact_Object_Version ;
448
449
450 --Put information into Contact Rec Type
451
452 l_ocon_rec.decision_maker_flag := p_load_tender_flag;
453 l_ocon_rec.org_contact_id := p_org_contact_id;
454 l_ocon_rec.job_title_code := nvl(p_job_title, fnd_api.g_miss_char);
455 l_ocon_rec.contact_number := nvl(p_contact_number, fnd_api.g_miss_char);
456
457 l_position := 20;
458 l_call_procedure := 'Calling TCA API Update_Org_Contact';
459
460 --
461 -- Debug Statements
462 --
463 IF l_debug_on THEN
464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact',WSH_DEBUG_SV.C_PROC_LEVEL);
465 END IF;
466 --
467
468 HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact (
469 p_init_msg_list => FND_API.G_TRUE,
470 p_org_contact_rec => l_ocon_rec,
471 p_cont_object_version_number => l_contact_object_version,
472 p_rel_object_version_number => l_rel_object_version,
473 p_party_object_version_number => l_party_object_version,
474 x_return_status => l_return_status,
475 x_msg_count => l_msg_count,
476 x_msg_data => l_msg_data );
477
478 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
479 x_return_status := l_return_status;
480 RAISE HZ_FAIL_EXCEPTION;
481 END IF;
482
483 l_contact_points_rec_type.contact_point_id := P_CONTACT_POINT_ID;
484 l_email_rec_type.email_address := P_EMAIL_ADDRESS;
485
486 --
487 -- Debug Statements
488 --
489 IF l_debug_on THEN
490 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program Unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point',WSH_DEBUG_SV.C_PROC_LEVEL);
491 END IF;
492 --
493
494 OPEN Get_Cont_Point_Version;
495 FETCH Get_Cont_Point_Version INTO l_cont_point_version;
496 CLOSE Get_Cont_Point_Version ;
497 -- Bug 2754631
498 HZ_CONTACT_POINT_V2PUB.Update_Contact_Point(
499 p_init_msg_list => FND_API.G_TRUE,
500 p_contact_point_rec => l_contact_points_rec_type,
501 p_email_rec => l_email_rec_type,
502 p_object_version_number => l_cont_point_version,
503 x_return_status => l_return_status,
504 x_msg_count => l_msg_count,
505 x_msg_data => l_msg_data );
506
507 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
508 x_return_status := l_return_status;
509 RAISE HZ_FAIL_EXCEPTION;
510 END IF;
511
512 --
513 -- Debug Statements
514 --
515 IF l_debug_on THEN
516 WSH_DEBUG_SV.pop(l_module_name);
517 END IF;
518 --
519
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 x_exception_msg := 'EXCEPTION : No Data Found';
523 x_position := l_position;
524 x_procedure := l_call_procedure;
525 x_sqlerr := sqlerrm;
526 x_sql_code := sqlcode;
527
528 --
529 -- Debug Statements
530 --
531 IF l_debug_on THEN
532 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
533 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
534 END IF;
535 --
536
537 WHEN HZ_FAIL_EXCEPTION THEN
538 x_exception_msg := l_msg_data;
539 x_position := l_position;
540 x_procedure := l_call_procedure;
541 x_sqlerr := sqlerrm;
542 x_sql_code := sqlcode;
543
544 --
545 -- Debug Statements
546 --
547 IF l_debug_on THEN
548 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
549 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
550 END IF;
551 --
552
553 WHEN OTHERS THEN
554 x_exception_msg := 'EXCEPTION : Others';
555 x_position := l_position;
556 x_procedure := l_call_procedure;
557 x_sqlerr := sqlerrm;
558 x_sql_code := sqlcode;
559
560 --
561 -- Debug Statements
562 --
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
565 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
566 END IF;
567 --
568
569 END UPDATE_CONTACTPERSON;
570
571 END WSH_SITE_CONTACT_PERSON_PKG;