[Home] [Help]
PACKAGE BODY: APPS.IEX_CASE_CONTACTS_PVT
Source
1 PACKAGE BODY IEX_CASE_CONTACTS_PVT as
2 /* $Header: iexvconb.pls 120.1 2006/05/30 21:13:08 scherkas noship $ */
3 -- Start of Comments
4 -- Package name : IEX_CASE_CONTACTS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_CASE_CONTACTS_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvconb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
17 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18
19 PROCEDURE Create_case_contact(
20 P_Api_Version_Number IN NUMBER,
21 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
22 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 P_case_contact_Rec IN case_contact_Rec_Type := G_MISS_case_contact_REC,
25 X_CAS_CONTACT_ID OUT NOCOPY NUMBER,
26 X_Return_Status OUT NOCOPY VARCHAR2,
27 X_Msg_Count OUT NOCOPY NUMBER,
28 X_Msg_Data OUT NOCOPY VARCHAR2
29 )
30
31 IS
32 l_api_name CONSTANT VARCHAR2(30) := 'Create_case_contact';
33 l_api_version_number CONSTANT NUMBER := 2.0;
34 l_return_status_full VARCHAR2(1);
35 v_rowid VARCHAR2(24);
36 v_cas_contact_id iex_case_contacts.cas_contact_id%TYPE;
37 v_object_version_number iex_case_contacts.object_version_number%TYPE;
38 v_active_flag iex_case_contacts.active_flag%TYPE;
39 v_address_id iex_case_contacts.address_id%TYPE;
40 v_phone_id iex_case_contacts.phone_id%TYPE;
41 v_primary_flag iex_case_contacts.primary_flag%TYPE;
42
43 Cursor c2 is SELECT IEX_CASE_CONTACTS_S.nextval from dual;
44
45 CURSOR C_GET_ADDRESS(P_PARTY_ID NUMBER) IS
46 SELECT PARTY_SITE_ID
47 FROM HZ_PARTY_SITES
48 WHERE PARTY_ID = P_PARTY_ID
49 AND IDENTIFYING_ADDRESS_FLAG = 'Y';
50
51 cursor c_get_phone(x_owner_table_id number) is
52 select contact_point_id
53 from hz_contact_points
54 where owner_table_id = x_owner_table_id
55 and owner_table_name = 'HZ_PARTIES'
56 and contact_point_type = 'PHONE'
57 and primary_flag = 'Y';
58
59 BEGIN
60 -- IF PG_DEBUG < 10 THEN
61 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
62 IEX_DEBUG_PUB.LogMessage ('********* start of Procedure =>IEX_CASE_CONTACTS_PVT.Create_case_contact ******** ');
63 END IF;
64 -- Standard Start of API savepoint
65 SAVEPOINT CREATE_CASE_CONTACT_PVT;
66
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
69 p_api_version_number,
70 l_api_name,
71 G_PKG_NAME)
72 THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75 -- IF PG_DEBUG < 10 THEN
76 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
77 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After Compatibility Check');
78 END IF;
79
80
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean( p_init_msg_list )
83 THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86
87
88 -- Initialize API return status to SUCCESS
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 --
92 -- API body
93 --
94 -- ******************************************************************
95 -- Validate Environment
96 -- ******************************************************************
97 IF FND_GLOBAL.User_Id IS NULL
98 THEN
99 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
100 THEN
101 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
102 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
103 FND_MSG_PUB.ADD;
104 END IF;
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107 -- IF PG_DEBUG < 10 THEN
108 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
109 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After Global user Check');
110 END IF;
111
112 --IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
113
114 --object version Number
115 v_object_version_number :=1;
116 --Active_flag
117 v_active_flag :='Y';
118
119 -- get cas_id
120 OPEN C2;
121 FETCH C2 INTO v_CAS_CONTACT_ID;
122 CLOSE C2;
123 -- IF PG_DEBUG < 10 THEN
124 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
125 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After CAS CONTACT ID Check and cas_contact_id is => '||v_cas_contact_id);
126 END IF;
127 --check for cas_id
128 IF (p_case_contact_rec.cas_id IS NULL) OR (p_case_contact_rec.cas_ID = FND_API.G_MISS_NUM) THEN
129 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
130 fnd_message.set_token('API_NAME', l_api_name);
131 fnd_message.set_token('MISSING_PARAM', 'cas_id');
132 fnd_msg_pub.add;
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135 -- IF PG_DEBUG < 10 THEN
136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
137 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After CAS ID Check ');
138 END IF;
139 --check for contact_party_id
140 IF (p_case_contact_rec.contact_party_id IS NULL) OR (p_case_contact_rec.contact_party_id = FND_API.G_MISS_NUM) THEN
141 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
142 fnd_message.set_token('API_NAME', l_api_name);
143 fnd_message.set_token('MISSING_PARAM', 'contact_party_id');
144 fnd_msg_pub.add;
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 -- IF PG_DEBUG < 10 THEN
148 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
149 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After CAS contact ID Check ');
150 END IF;
151 --Populate default address_id if it is not passed
152 IF (p_case_contact_rec.address_id IS NULL)
153 OR (p_case_contact_rec.address_id = FND_API.G_MISS_NUM) THEN
154 OPEN C_GET_ADDRESS(p_case_contact_rec.CONTACT_PARTY_ID);
155 FETCH C_GET_ADDRESS INTO v_address_id;
156 CLOSE C_GET_ADDRESS;
157 ELSE
158 v_address_id :=p_case_contact_rec.address_id;
159 END IF;
160 --Populate default phone_id if it is not passed
161 IF (p_case_contact_rec.phone_id IS NULL)
162 OR (p_case_contact_rec.phone_id = FND_API.G_MISS_NUM) THEN
163 OPEN C_GET_phone(p_case_contact_rec.CONTACT_PARTY_ID);
164 FETCH C_GET_phone INTO v_phone_id;
165 CLOSE C_GET_phone;
166 ELSE
167 v_phone_id :=p_case_contact_rec.phone_id;
168 END IF;
169 -- Primary flag // added on 01/07/02
170 IF (p_case_contact_rec.primary_flag IS NULL)
171 OR (p_case_contact_rec.primary_flag = FND_API.G_MISS_CHAR) THEN
172 v_primary_flag :='N';
173 ELSE
174 v_primary_flag :=p_case_contact_rec.primary_flag;
175 END IF;
176
177 --END IF;
178 -- IF PG_DEBUG < 10 THEN
179 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
180 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'Before calling IEX_CASE_CONTACTS_PKG.Insert_Row');
181 END IF;
182 -- Invoke table handler(IEX_CASE_CONTACTS_PKG.Insert_Row)
183 IEX_CASE_CONTACTS_PKG.Insert_Row(
184 x_rowid =>v_rowid,
185 p_CAS_CONTACT_ID => v_CAS_CONTACT_ID,
186 p_CAS_ID => p_case_contact_rec.CAS_ID,
187 p_CONTACT_PARTY_ID => p_case_contact_rec.CONTACT_PARTY_ID,
188 p_OBJECT_VERSION_NUMBER => v_OBJECT_VERSION_NUMBER,
189 p_ACTIVE_FLAG => v_ACTIVE_FLAG,
190 p_address_id => v_address_id,
191 p_phone_id => v_phone_id,
192 p_REQUEST_ID => p_case_contact_rec.REQUEST_ID,
193 p_PROGRAM_APPLICATION_ID => p_case_contact_rec.PROGRAM_APPLICATION_ID,
194 p_PROGRAM_ID => p_case_contact_rec.PROGRAM_ID,
195 p_PROGRAM_UPDATE_DATE => p_case_contact_rec.PROGRAM_UPDATE_DATE,
196 p_ATTRIBUTE_CATEGORY => p_case_contact_rec.ATTRIBUTE_CATEGORY,
197 p_ATTRIBUTE1 => p_case_contact_rec.ATTRIBUTE1,
198 p_ATTRIBUTE2 => p_case_contact_rec.ATTRIBUTE2,
199 p_ATTRIBUTE3 => p_case_contact_rec.ATTRIBUTE3,
200 p_ATTRIBUTE4 => p_case_contact_rec.ATTRIBUTE4,
201 p_ATTRIBUTE5 => p_case_contact_rec.ATTRIBUTE5,
202 p_ATTRIBUTE6 => p_case_contact_rec.ATTRIBUTE6,
203 p_ATTRIBUTE7 => p_case_contact_rec.ATTRIBUTE7,
204 p_ATTRIBUTE8 => p_case_contact_rec.ATTRIBUTE8,
205 p_ATTRIBUTE9 => p_case_contact_rec.ATTRIBUTE9,
206 p_ATTRIBUTE10 => p_case_contact_rec.ATTRIBUTE10,
207 p_ATTRIBUTE11 => p_case_contact_rec.ATTRIBUTE11,
208 p_ATTRIBUTE12 => p_case_contact_rec.ATTRIBUTE12,
209 p_ATTRIBUTE13 => p_case_contact_rec.ATTRIBUTE13,
210 p_ATTRIBUTE14 => p_case_contact_rec.ATTRIBUTE14,
211 p_ATTRIBUTE15 => p_case_contact_rec.ATTRIBUTE15,
212 p_CREATED_BY => FND_GLOBAL.USER_ID,
213 p_CREATION_DATE => SYSDATE,
214 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
215 p_LAST_UPDATE_DATE => SYSDATE,
216 p_LAST_UPDATE_LOGIN => p_case_contact_rec.LAST_UPDATE_LOGIN,
217 p_PRIMARY_FLAG => V_PRIMARY_FLAG
218 );
219
220 x_CAS_CONTACT_ID := V_CAS_CONTACT_ID;
221 -- IF PG_DEBUG < 10 THEN
222 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
223 IEX_DEBUG_PUB.LogMessage('Create_case_contact: ' || 'After Calling IEX_CASE_CONTACTS_PKG.Insert_Row and cas Contact id => '
224 ||x_cas_contact_id);
225 END IF;
226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
227 RAISE FND_API.G_EXC_ERROR;
228 END IF;
229
230 --
231 -- End of API body
232 --
233
234 -- Standard check for p_commit
235 IF FND_API.to_Boolean( p_commit )
236 THEN
237 COMMIT WORK;
238 END IF;
239
240
241
242 -- Standard call to get message count and if count is 1, get message info.
243 FND_MSG_PUB.Count_And_Get
244 ( p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247 -- IF PG_DEBUG < 10 THEN
248 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
249 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASE_CONTACTS_PVT.Create_case_contact ******** ');
250 END IF;
251 EXCEPTION
252 WHEN FND_API.G_EXC_ERROR THEN
253 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
254 P_API_NAME => L_API_NAME
255 ,P_PKG_NAME => G_PKG_NAME
256 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
257 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
258 ,X_MSG_COUNT => X_MSG_COUNT
259 ,X_MSG_DATA => X_MSG_DATA
260 ,X_RETURN_STATUS => X_RETURN_STATUS);
261
262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
263 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
264 P_API_NAME => L_API_NAME
265 ,P_PKG_NAME => G_PKG_NAME
266 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
267 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
268 ,X_MSG_COUNT => X_MSG_COUNT
269 ,X_MSG_DATA => X_MSG_DATA
270 ,X_RETURN_STATUS => X_RETURN_STATUS);
271
272 WHEN OTHERS THEN
273 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
274 P_API_NAME => L_API_NAME
275 ,P_PKG_NAME => G_PKG_NAME
276 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
277 ,P_SQLCODE => SQLCODE
278 ,P_SQLERRM => SQLERRM
279 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
280 ,X_MSG_COUNT => X_MSG_COUNT
281 ,X_MSG_DATA => X_MSG_DATA
282 ,X_RETURN_STATUS => X_RETURN_STATUS);
283 End Create_case_contact;
284
285
286 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
287 PROCEDURE Update_case_contact(
288 P_Api_Version_Number IN NUMBER,
289 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
290 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
291 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
292 P_case_contact_Rec IN case_contact_Rec_Type,
293 X_Return_Status OUT NOCOPY VARCHAR2,
294 X_Msg_Count OUT NOCOPY NUMBER,
295 X_Msg_Data OUT NOCOPY VARCHAR2,
296 xo_object_version_number OUT NOCOPY NUMBER
297
298 )
299
300 IS
301 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CASE_CONTACT';
302 l_api_version_number CONSTANT NUMBER := 2.0;
303 l_object_version_number iex_case_contacts.object_version_number%TYPE:=p_case_contact_rec.object_version_number;
304 BEGIN
305 -- IF PG_DEBUG < 10 THEN
306 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
307 IEX_DEBUG_PUB.LogMessage ('********* start of Procedure =>IEX_CASE_CONTACTS_PVT.update_case_contact ******** ');
308 END IF;
309 -- Standard Start of API savepoint
310 SAVEPOINT UPDATE_case_contact_PVT;
311
312 -- Standard call to check for call compatibility.
313 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
314 p_api_version_number,
315 l_api_name,
316 G_PKG_NAME)
317 THEN
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319 END IF;
320
321
322 -- Initialize message list if p_init_msg_list is set to TRUE.
323 IF FND_API.to_Boolean( p_init_msg_list )
324 THEN
325 FND_MSG_PUB.initialize;
326 END IF;
327
328
329
330 -- Initialize API return status to SUCCESS
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 --
334 -- Api body
335 --
336 -- Invoke table handler(IEX_CASES_ALL_B_PKG.Update_Row)
337 -- call locking table handler
338 IEX_CASE_CONTACTS_PKG.lock_row (
339 p_case_contact_rec.cas_contact_id,
340 l_object_version_number
341 );
342
343 -- Invoke table handler(IEX_CASE_CONTACTS_PKG.Update_Row)
344 IEX_CASE_CONTACTS_PKG.Update_Row(
345 p_CAS_CONTACT_ID => p_case_contact_rec.CAS_CONTACT_ID,
346 p_CAS_ID => p_case_contact_rec.CAS_ID,
347 p_CONTACT_PARTY_ID => p_case_contact_rec.CONTACT_PARTY_ID,
348 p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER +1,
349 p_ACTIVE_FLAG => p_case_contact_rec.ACTIVE_FLAG,
350 p_REQUEST_ID => p_case_contact_rec.REQUEST_ID,
351 p_address_id => p_case_contact_rec.address_id,
352 p_phone_id => p_case_contact_rec.phone_id,
353 p_PROGRAM_APPLICATION_ID => p_case_contact_rec.PROGRAM_APPLICATION_ID,
354 p_PROGRAM_ID => p_case_contact_rec.PROGRAM_ID,
355 p_PROGRAM_UPDATE_DATE => p_case_contact_rec.PROGRAM_UPDATE_DATE,
356 p_ATTRIBUTE_CATEGORY => p_case_contact_rec.ATTRIBUTE_CATEGORY,
357 p_ATTRIBUTE1 => p_case_contact_rec.ATTRIBUTE1,
358 p_ATTRIBUTE2 => p_case_contact_rec.ATTRIBUTE2,
359 p_ATTRIBUTE3 => p_case_contact_rec.ATTRIBUTE3,
360 p_ATTRIBUTE4 => p_case_contact_rec.ATTRIBUTE4,
361 p_ATTRIBUTE5 => p_case_contact_rec.ATTRIBUTE5,
362 p_ATTRIBUTE6 => p_case_contact_rec.ATTRIBUTE6,
363 p_ATTRIBUTE7 => p_case_contact_rec.ATTRIBUTE7,
364 p_ATTRIBUTE8 => p_case_contact_rec.ATTRIBUTE8,
365 p_ATTRIBUTE9 => p_case_contact_rec.ATTRIBUTE9,
366 p_ATTRIBUTE10 => p_case_contact_rec.ATTRIBUTE10,
367 p_ATTRIBUTE11 => p_case_contact_rec.ATTRIBUTE11,
368 p_ATTRIBUTE12 => p_case_contact_rec.ATTRIBUTE12,
369 p_ATTRIBUTE13 => p_case_contact_rec.ATTRIBUTE13,
370 p_ATTRIBUTE14 => p_case_contact_rec.ATTRIBUTE14,
371 p_ATTRIBUTE15 => p_case_contact_rec.ATTRIBUTE15,
372 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
373 p_LAST_UPDATE_DATE => SYSDATE,
374 p_LAST_UPDATE_LOGIN => p_case_contact_rec.LAST_UPDATE_LOGIN,
375 p_PRIMARY_FLAG => p_case_contact_rec.PRIMARY_FLAG);
376
377 --Return Version number
378 xo_object_version_number := l_object_version_number + 1;
379
380 --
381 -- End of API body.
382 --
383
384 -- Standard check for p_commit
385 IF FND_API.to_Boolean( p_commit )
386 THEN
387 COMMIT WORK;
388 END IF;
389
390
391
392 -- Standard call to get message count and if count is 1, get message info.
393 FND_MSG_PUB.Count_And_Get
394 ( p_count => x_msg_count,
395 p_data => x_msg_data
396 );
397 -- IF PG_DEBUG < 10 THEN
398 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
399 IEX_DEBUG_PUB.LogMessage ('********* end of Procedure =>IEX_CASE_CONTACTS_PVT.update_case_contact ******** ');
400 END IF;
401 EXCEPTION
402 WHEN FND_API.G_EXC_ERROR THEN
403 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
404 P_API_NAME => L_API_NAME
405 ,P_PKG_NAME => G_PKG_NAME
406 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
407 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
408 ,X_MSG_COUNT => X_MSG_COUNT
409 ,X_MSG_DATA => X_MSG_DATA
410 ,X_RETURN_STATUS => X_RETURN_STATUS);
411
412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
413 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
414 P_API_NAME => L_API_NAME
415 ,P_PKG_NAME => G_PKG_NAME
416 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
417 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
418 ,X_MSG_COUNT => X_MSG_COUNT
419 ,X_MSG_DATA => X_MSG_DATA
420 ,X_RETURN_STATUS => X_RETURN_STATUS);
421
422 WHEN OTHERS THEN
423 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
424 P_API_NAME => L_API_NAME
425 ,P_PKG_NAME => G_PKG_NAME
426 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
427 ,P_SQLCODE => SQLCODE
428 ,P_SQLERRM => SQLERRM
429 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
430 ,X_MSG_COUNT => X_MSG_COUNT
431 ,X_MSG_DATA => X_MSG_DATA
432 ,X_RETURN_STATUS => X_RETURN_STATUS);
433 End Update_case_contact;
434
435
436 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
437 -- The Master delete procedure may not be needed depends on different business requirements.
438 PROCEDURE Delete_case_contact(
439 P_Api_Version_Number IN NUMBER,
440 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
441 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
442 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
443 P_cas_contact_ID IN NUMBER,
444 X_Return_Status OUT NOCOPY VARCHAR2,
445 X_Msg_Count OUT NOCOPY NUMBER,
446 X_Msg_Data OUT NOCOPY VARCHAR2
447 )
448
449 IS
450 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CASE_CONTACT';
451 l_api_version_number CONSTANT NUMBER := 2.0;
452
453 BEGIN
454 -- IF PG_DEBUG < 10 THEN
455 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
456 IEX_DEBUG_PUB.LogMessage ('********* start of Procedure =>IEX_CASE_CONTACTS_PVT.delete_case_contact ******** ');
457 END IF;
458 -- Standard Start of API savepoint
459 SAVEPOINT DELETE_case_contact_PVT;
460
461 -- Standard call to check for call compatibility.
462 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
463 p_api_version_number,
464 l_api_name,
465 G_PKG_NAME)
466 THEN
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 END IF;
469
470
471 -- Initialize message list if p_init_msg_list is set to TRUE.
472 IF FND_API.to_Boolean( p_init_msg_list )
473 THEN
474 FND_MSG_PUB.initialize;
475 END IF;
476
477 -- Initialize API return status to SUCCESS
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480 --
481 -- Api body
482 --
483
484 -- Invoke table handler(IEX_CASE_CONTACTS_PKG.Delete_Row)
485 IEX_CASE_CONTACTS_PKG.Delete_Row(
486 p_CAS_CONTACT_ID => p_CAS_CONTACT_ID);
487 --
488 -- End of API body
489 --
490
491 -- Standard check for p_commit
492 IF FND_API.to_Boolean( p_commit )
493 THEN
494 COMMIT WORK;
495 END IF;
496
497
498
499 -- Standard call to get message count and if count is 1, get message info.
500 FND_MSG_PUB.Count_And_Get
501 ( p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504 -- IF PG_DEBUG < 10 THEN
505 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
506 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASE_CONTACTS_PVT.delete_case_contact ******** ');
507 END IF;
508 EXCEPTION
509 WHEN FND_API.G_EXC_ERROR THEN
510 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
511 P_API_NAME => L_API_NAME
512 ,P_PKG_NAME => G_PKG_NAME
513 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
514 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
515 ,X_MSG_COUNT => X_MSG_COUNT
516 ,X_MSG_DATA => X_MSG_DATA
517 ,X_RETURN_STATUS => X_RETURN_STATUS);
518
519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
520 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
521 P_API_NAME => L_API_NAME
522 ,P_PKG_NAME => G_PKG_NAME
523 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
524 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
525 ,X_MSG_COUNT => X_MSG_COUNT
526 ,X_MSG_DATA => X_MSG_DATA
527 ,X_RETURN_STATUS => X_RETURN_STATUS);
528
529 WHEN OTHERS THEN
530 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
531 P_API_NAME => L_API_NAME
532 ,P_PKG_NAME => G_PKG_NAME
533 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
534 ,P_SQLCODE => SQLCODE
535 ,P_SQLERRM => SQLERRM
536 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
537 ,X_MSG_COUNT => X_MSG_COUNT
538 ,X_MSG_DATA => X_MSG_DATA
539 ,X_RETURN_STATUS => X_RETURN_STATUS);
540 End Delete_case_contact;
541
542
543 End IEX_CASE_CONTACTS_PVT;