[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_RESP_PVT
Source
1 PACKAGE BODY Ibe_Msite_Resp_Pvt AS
2 /* $Header: IBEVMRSB.pls 120.0 2005/05/30 02:58:19 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_RESP_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMRSB.pls';
6
7 -- HISTORY
8 -- 12/13/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
9 -- 07/28/03 JQU
10 -- Modified procedures for addition of group_code column
11 -- *********************************************************************************
12
13 PROCEDURE Validate_Msite_Id_Exists
14 (
15 p_msite_id IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 )
20 IS
21 l_api_name CONSTANT VARCHAR2(30) :=
22 'Validate_Msite_Id_Exists';
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_tmp_id NUMBER;
25
26 CURSOR c1(l_c_msite_id IN NUMBER)
27 IS SELECT msite_id FROM ibe_msites_b
28 WHERE msite_id = l_c_msite_id
29 AND master_msite_flag = 'N' and site_type = 'I';
30
31 BEGIN
32
33 -- Initialize status to SUCCESS
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 -- Check if msite_id exists in ibe_msites_b
37 OPEN c1(p_msite_id);
38 FETCH c1 INTO l_tmp_id;
39 IF (c1%NOTFOUND) THEN
40 CLOSE c1;
41 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_NOT_FOUND');
42 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
43 FND_MSG_PUB.Add;
44 RAISE FND_API.G_EXC_ERROR;
45 END IF;
46 CLOSE c1;
47
48 EXCEPTION
49
50 WHEN FND_API.G_EXC_ERROR THEN
51 x_return_status := FND_API.G_RET_STS_ERROR;
52 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
53 p_data => x_msg_data,
54 p_encoded => 'F');
55
56 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
57 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
58 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
59 p_data => x_msg_data,
60 p_encoded => 'F');
61
62 WHEN OTHERS THEN
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
64
65 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
66 THEN
67 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
68 END IF;
69
70 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
71 p_data => x_msg_data,
72 p_encoded => 'F');
73
74 END Validate_Msite_Id_Exists;
75
76 PROCEDURE Validate_Resp_Appl_Id_Exists
77 (
78 p_responsibility_id IN NUMBER,
79 p_application_id IN NUMBER,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2
83 )
84 IS
85 l_api_name CONSTANT VARCHAR2(30) :=
86 'Validate_Resp_Appl_Id_Exists';
87 l_api_version CONSTANT NUMBER := 1.0;
88 l_tmp_id NUMBER;
89
90 CURSOR c1(l_c_responsibility_id IN NUMBER, l_c_application_id IN NUMBER)
91 IS SELECT responsibility_id FROM fnd_responsibility_vl
92 WHERE responsibility_id = l_c_responsibility_id
93 AND application_id = l_c_application_id;
94
95 BEGIN
96
97 -- Initialize status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- Check if responsibility_id and application_id combination
101 -- exists in fnd_responsibility
102 OPEN c1(p_responsibility_id, p_application_id);
103 FETCH c1 INTO l_tmp_id;
104 IF (c1%NOTFOUND) THEN
105 CLOSE c1;
106 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_RESP_APPL_NOT_FOUND');
107 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
108 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
109 FND_MSG_PUB.Add;
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112 CLOSE c1;
113
114 EXCEPTION
115
116 WHEN FND_API.G_EXC_ERROR THEN
117 x_return_status := FND_API.G_RET_STS_ERROR;
118 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
119 p_data => x_msg_data,
120 p_encoded => 'F');
121
122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
125 p_data => x_msg_data,
126 p_encoded => 'F');
127
128 WHEN OTHERS THEN
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130
131 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
132 THEN
133 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
134 END IF;
135
136 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
137 p_data => x_msg_data,
138 p_encoded => 'F');
139
140 END Validate_Resp_Appl_Id_Exists;
141
142 PROCEDURE Validate_Msite_Resp_Id_Exists
143 (
144 p_msite_resp_id IN NUMBER,
145 p_msite_id IN NUMBER,
146 p_responsibility_id IN NUMBER,
147 p_application_id IN NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2
151 )
152 IS
153 l_api_name CONSTANT VARCHAR2(30) :=
154 'Validate_Msite_Resp_Id_Exists';
155 l_api_version CONSTANT NUMBER := 1.0;
156 l_tmp_id NUMBER;
157
158 CURSOR c1(l_c_msite_resp_id IN NUMBER)
159 IS SELECT msite_resp_id FROM ibe_msite_resps_b
160 WHERE msite_resp_id = l_c_msite_resp_id;
161
162 CURSOR c2(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
163 l_c_application_id IN NUMBER)
164 IS SELECT msite_resp_id FROM ibe_msite_resps_b
165 WHERE msite_id = l_c_msite_id
166 AND responsibility_id = l_c_responsibility_id
167 AND application_id = l_c_application_id;
168
169 BEGIN
170
171 -- Initialize status to SUCCESS
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 -- Check if msite_resp_id or combination of msite_id, responsibility_id,
175 -- application_id exists in ibe_msite_resps_b
176 IF ((p_msite_resp_id IS NOT NULL) AND
177 (p_msite_resp_id <> FND_API.G_MISS_NUM))
178 THEN
179
180 OPEN c1(p_msite_resp_id);
181 FETCH c1 INTO l_tmp_id;
182 IF (c1%NOTFOUND) THEN
183 CLOSE c1;
184 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_NOT_FOUND');
185 FND_MESSAGE.Set_Token('MSITE_RESP_ID', p_msite_resp_id);
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188 CLOSE c1;
189
190 ELSIF ((p_msite_id IS NOT NULL) AND
191 (p_msite_id <> FND_API.G_MISS_NUM) AND
192 (p_responsibility_id IS NOT NULL) AND
193 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
194 (p_application_id IS NOT NULL) AND
195 (p_application_id <> FND_API.G_MISS_NUM))
196 THEN
197
198 OPEN c2(p_msite_id, p_responsibility_id, p_application_id);
199 FETCH c2 INTO l_tmp_id;
200 IF (c2%NOTFOUND) THEN
201 CLOSE c2;
202 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITERESP_NOT_FOUND');
203 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
204 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
205 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208 CLOSE c2;
209
210 ELSE
211 -- neither msite_resp_id nor combination of
212 -- msite_id, responsibility_id and application_id is specified
213 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MR_IDS_SPEC');
214 FND_MSG_PUB.Add;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 EXCEPTION
219
220 WHEN FND_API.G_EXC_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
223 p_data => x_msg_data,
224 p_encoded => 'F');
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
229 p_data => x_msg_data,
230 p_encoded => 'F');
231
232 WHEN OTHERS THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234
235 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
236 THEN
237 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
238 END IF;
239
240 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
241 p_data => x_msg_data,
242 p_encoded => 'F');
243
244 END Validate_Msite_Resp_Id_Exists;
245
246 PROCEDURE Validate_Create
247 (
248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
250 p_msite_id IN NUMBER,
251 p_responsibility_id IN NUMBER,
252 p_application_id IN NUMBER,
253 p_start_date_active IN DATE,
254 p_end_date_active IN DATE,
255 p_sort_order IN NUMBER,
256 p_display_name IN VARCHAR2,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2
260 )
261 IS
262 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
263 l_api_version CONSTANT NUMBER := 1.0;
264
265 l_msite_resp_id NUMBER;
266 l_msite_id NUMBER;
267 l_responsibility_id NUMBER;
268 l_application_id NUMBER;
269
270 BEGIN
271
272 -- Initialize message list if p_init_msg_list is set to TRUE.
273 IF FND_API.to_Boolean(p_init_msg_list) THEN
274 FND_MSG_PUB.initialize;
275 END IF;
276
277 -- Initialize API return status to success
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 --
281 -- Check null values for required fields
282 --
283 -- p_msite_id
284 IF ((p_msite_id IS NULL) OR
285 (p_msite_id = FND_API.G_MISS_NUM))
286 THEN
287 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_MSITE_ID');
288 FND_MSG_PUB.Add;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 -- p_responsibility_id
293 IF ((p_responsibility_id IS NULL) OR
294 (p_responsibility_id = FND_API.G_MISS_NUM))
295 THEN
296 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_RESP_ID');
297 FND_MSG_PUB.Add;
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300
301 -- application_id
302 IF ((p_application_id IS NULL) OR
303 (p_application_id = FND_API.G_MISS_NUM))
304 THEN
305 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_APPL_ID');
306 FND_MSG_PUB.Add;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309
310 -- start_date_active
311 IF ((p_start_date_active IS NULL) OR
312 (p_start_date_active = FND_API.G_MISS_DATE))
313 THEN
314 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
315 FND_MSG_PUB.Add;
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318
319 -- display_name
320 IF ((p_display_name IS NULL) OR
321 (p_display_name = FND_API.G_MISS_CHAR))
322 THEN
323 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVLD_MSRSP_DSP_NAME');
324 FND_MSG_PUB.Add;
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327
328 --
329 -- non-null field validation
330 --
331
332 -- sort order
333 IF ((p_sort_order IS NOT NULL) AND
334 (p_sort_order <> FND_API.G_MISS_NUM))
335 THEN
336 IF(p_sort_order < 0) THEN
337 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVLD_SORT_ORDER');
338 FND_MSG_PUB.Add;
339 RAISE FND_API.G_EXC_ERROR;
340 END IF;
341 END IF;
342
343 --
344 -- Foreign key integrity constraint check
345 --
346
347 -- msite_id
348 Validate_Msite_Id_Exists
349 (
350 p_msite_id => p_msite_id,
351 x_return_status => x_return_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data
354 );
355
356
357 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
358 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
359 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
360 FND_MSG_PUB.Add;
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
363 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
364 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
365 RAISE FND_API.G_EXC_ERROR; -- invalid msite_id
366 END IF;
367
368 -- responsibility_id and application_id
369 Validate_Resp_Appl_Id_Exists
370 (
371 p_responsibility_id => p_responsibility_id,
372 p_application_id => p_application_id,
373 x_return_status => x_return_status,
374 x_msg_count => x_msg_count,
375 x_msg_data => x_msg_data
376 );
377
378
379 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
380 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_RESP_ID_VLD_FAIL');
381 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
382 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
383 FND_MSG_PUB.Add;
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
386 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_RESP_ID_VLD_FAIL');
387 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
388 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 -- p_msite_id, p_responsibility_id, p_application_id (check for duplicate)
393 -- Validate_Msite_Resp_Id_Exists
394 -- (
395 -- p_msite_resp_id => FND_API.G_MISS_NUM,
396 -- p_msite_id => p_msite_id,
397 -- p_responsibility_id => p_responsibility_id,
398 -- p_application_id => p_application_id,
399 -- x_return_status => x_return_status,
400 -- x_msg_count => x_msg_count,
401 -- x_msg_data => x_msg_data
402 -- );
403
404 -- IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
405 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
406 -- ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
407 -- FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSRSP_ID_VLD_FAIL');
408 -- FND_MSG_PUB.Add;
409 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 -- ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN -- duplicate exists
411 -- FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSRSP_ID_DUP_EXISTS');
412 -- FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
416 -- RAISE FND_API.G_EXC_ERROR;
413 -- FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
414 -- FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
415 -- FND_MSG_PUB.Add;
417 -- END IF;
418
419 EXCEPTION
420
421 WHEN FND_API.G_EXC_ERROR THEN
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
424 p_data => x_msg_data,
425 p_encoded => 'F');
426
427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
430 p_data => x_msg_data,
431 p_encoded => 'F');
432
433 WHEN OTHERS THEN
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435
436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
437 THEN
438 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
439 END IF;
440
441 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
442 p_data => x_msg_data,
443 p_encoded => 'F');
444
445
446 END Validate_Create;
447
448 PROCEDURE Validate_Update
449 (
450 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
451 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
452 p_msite_resp_id IN NUMBER,
453 p_object_version_number IN NUMBER,
454 p_start_date_active IN DATE,
455 p_end_date_active IN DATE,
456 p_sort_order IN NUMBER,
457 p_display_name IN VARCHAR2,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_msg_count OUT NOCOPY NUMBER,
460 x_msg_data OUT NOCOPY VARCHAR2
461 )
462 IS
463 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
464 l_api_version CONSTANT NUMBER := 1.0;
465
466 l_msite_resp_id NUMBER;
467 l_msite_id NUMBER;
468 l_responsibility_id NUMBER;
469 l_application_id NUMBER;
470
471 BEGIN
472
473 -- Initialize message list if p_init_msg_list is set to TRUE.
474 IF FND_API.to_Boolean(p_init_msg_list) THEN
475 FND_MSG_PUB.initialize;
476 END IF;
477
478 -- Initialize API return status to success
479 x_return_status := FND_API.G_RET_STS_SUCCESS;
480
481 --
482 -- Check null values for required fields
483 --
484
485 -- msite_resp_id
486 IF (p_msite_resp_id IS NULL) THEN
487 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSRSP_ID_IS_NULL');
488 FND_MSG_PUB.Add;
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 -- object_version_number
493 IF (p_object_version_number IS NULL)
494 THEN
495 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_OVN_IS_NULL');
496 FND_MSG_PUB.Add;
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499
500 -- start_date_active
501 IF (p_start_date_active IS NULL) THEN
502 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
503 FND_MSG_PUB.Add;
504 RAISE FND_API.G_EXC_ERROR;
505 END IF;
506
507 -- display_name
508 IF (p_display_name IS NULL) THEN
509 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVLD_MSRSP_DSP_NAME');
510 FND_MSG_PUB.Add;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513
514 --
515 -- non-null field validation
516 --
517
518 -- sort order
519 IF ((p_sort_order IS NOT NULL) AND
520 (p_sort_order <> FND_API.G_MISS_NUM))
521 THEN
522 IF(p_sort_order < 0) THEN
523 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVLD_SORT_ORDER');
524 FND_MSG_PUB.Add;
525 RAISE FND_API.G_EXC_ERROR;
526 END IF;
527 END IF;
528
529 -- check if the association already exists, if not, then throw error
530 Validate_Msite_Resp_Id_Exists
531 (
532 p_msite_resp_id => p_msite_resp_id,
533 p_msite_id => FND_API.G_MISS_NUM,
534 p_responsibility_id => FND_API.G_MISS_NUM,
535 p_application_id => FND_API.G_MISS_NUM,
536 x_return_status => x_return_status,
537 x_msg_count => x_msg_count,
538 x_msg_data => x_msg_data
539 );
540
541 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
542 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSRSP_ID_VLD_FAIL');
543 FND_MSG_PUB.Add;
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
546 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSRSP_ID_VLD_FAIL');
547 FND_MSG_PUB.Add;
548 RAISE FND_API.G_EXC_ERROR;
549 END IF;
550
551
552 EXCEPTION
553
554 WHEN FND_API.G_EXC_ERROR THEN
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
557 p_data => x_msg_data,
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 p_encoded => 'F');
559
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
562 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
563 p_data => x_msg_data,
564 p_encoded => 'F');
565
566 WHEN OTHERS THEN
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568
569 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
570 THEN
571 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
572 END IF;
573
574 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
575 p_data => x_msg_data,
576 p_encoded => 'F');
577
578 END Validate_Update;
579
580
581 PROCEDURE Create_Msite_Resp
582 (
583 p_api_version IN NUMBER,
584 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
585 p_commit IN VARCHAR2 := FND_API.G_FALSE,
586 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
587 p_msite_resp_id IN NUMBER,
588 p_msite_id IN NUMBER,
589 p_responsibility_id IN NUMBER,
590 p_application_id IN NUMBER,
591 p_start_date_active IN DATE,
592 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
593 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
594 p_display_name IN VARCHAR2,
595 p_group_code IN VARCHAR2,
596 x_msite_resp_id OUT NOCOPY NUMBER,
597 x_return_status OUT NOCOPY VARCHAR2,
598 x_msg_count OUT NOCOPY NUMBER,
599 x_msg_data OUT NOCOPY VARCHAR2
600 )
601 IS
602 l_api_name CONSTANT VARCHAR2(30) := 'Create_Msite_Resp';
603 l_api_version CONSTANT NUMBER := 1.0;
604
605 l_object_version_number CONSTANT NUMBER := 1;
606 l_rowid VARCHAR2(30);
607
608 BEGIN
609
610 -- Standard Start of API savepoint
611 SAVEPOINT create_msite_resp_pvt;
612
613 -- Standard call to check for call compatibility.
614 IF NOT FND_API.Compatible_API_Call(l_api_version,
615 p_api_version,
616 l_api_name,
617 G_PKG_NAME)
618 THEN
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END IF;
621
622 -- Initialize message list if p_init_msg_list is set to TRUE.
623 IF FND_API.to_Boolean(p_init_msg_list) THEN
624 FND_MSG_PUB.initialize;
625 END IF;
626
627 -- Initialize API return status to success
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 -- API body
631 -- CALL FLOW :
632 -- 1. Check if everything is valid
633 -- 2. Insert row with section data into section table
634 --
635
636 --
637 -- 1. Check if everything is valid
638 --
639 Validate_Create
640 (
641 p_init_msg_list => FND_API.G_FALSE,
642 p_validation_level => p_validation_level,
643 p_msite_id => p_msite_id,
644 p_responsibility_id => p_responsibility_id,
645 p_application_id => p_application_id,
646 p_start_date_active => p_start_date_active,
647 p_end_date_active => p_end_date_active,
648 p_sort_order => p_sort_order,
649 p_display_name => p_display_name,
650 x_return_status => x_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data
653 );
654
655 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
656 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MR_INVALID_CREATE');
657 FND_MSG_PUB.Add;
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
660 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MR_INVALID_CREATE');
661 FND_MSG_PUB.Add;
662 RAISE FND_API.G_EXC_ERROR; -- invalid
663 END IF;
664
665 --
666 -- 2. Insert row
667 --
668 BEGIN
669 Ibe_Msite_Resp_Pkg.insert_row
670 (
671 p_msite_resp_id => p_msite_resp_id,
672 p_object_version_number => l_object_version_number,
673 p_msite_id => p_msite_id,
674 p_responsibility_id => p_responsibility_id,
675 p_application_id => p_application_id,
676 p_start_date_active => p_start_date_active,
677 p_end_date_active => p_end_date_active,
678 p_sort_order => p_sort_order,
679 p_display_name => p_display_name,
680 p_group_code => p_group_code,
681 p_creation_date => sysdate,
682 p_created_by => FND_GLOBAL.user_id,
686 x_rowid => l_rowid,
683 p_last_update_date => sysdate,
684 p_last_updated_by => FND_GLOBAL.user_id,
685 p_last_update_login => FND_GLOBAL.login_id,
687 x_msite_resp_id => x_msite_resp_id
688 );
689
690 EXCEPTION
691 WHEN NO_DATA_FOUND THEN
692 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_INSERT_FL');
693 FND_MSG_PUB.Add;
694 RAISE FND_API.G_EXC_ERROR;
695 WHEN OTHERS THEN
696 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_INSERT_FL');
697 FND_MSG_PUB.Add;
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END;
700
701 --
702 -- End of main API body.
703
704 -- Standard check of p_commit.
705 IF (FND_API.To_Boolean(p_commit)) THEN
706 COMMIT WORK;
707 END IF;
708
709 -- Standard call to get message count and if count is 1, get message info.
710 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
711 p_data => x_msg_data,
712 p_encoded => 'F');
713 EXCEPTION
714
715 WHEN FND_API.G_EXC_ERROR THEN
716 ROLLBACK TO create_msite_resp_pvt;
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
719 p_data => x_msg_data,
720 p_encoded => 'F');
721
722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
723 ROLLBACK TO create_msite_resp_pvt;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
726 p_data => x_msg_data,
727 p_encoded => 'F');
728
729 WHEN OTHERS THEN
730 ROLLBACK TO create_msite_resp_pvt;
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
732
733 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
734 THEN
735 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
736 END IF;
737
738 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
739 p_data => x_msg_data,
740 p_encoded => 'F');
741
742 END Create_Msite_Resp;
743
744 PROCEDURE Update_Msite_Resp
745 (
746 p_api_version IN NUMBER,
747 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
748 p_commit IN VARCHAR2 := FND_API.G_FALSE,
749 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
750 p_msite_resp_id IN NUMBER := FND_API.G_MISS_NUM,
751 p_object_version_number IN NUMBER,
752 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
753 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
754 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
755 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
756 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
757 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
758 p_display_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
759 p_group_code IN VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2,
761 x_msg_count OUT NOCOPY NUMBER,
762 x_msg_data OUT NOCOPY VARCHAR2
763 )
764 IS
765 l_api_name CONSTANT VARCHAR2(30) := 'Update_Msite_Resp';
766 l_api_version CONSTANT NUMBER := 1.0;
767
768 l_msite_resp_id NUMBER;
769
770 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
771 l_c_application_id IN NUMBER)
772 IS SELECT msite_resp_id FROM ibe_msite_resps_b
773 WHERE msite_id = l_c_msite_id
774 AND responsibility_id = l_c_responsibility_id
775 AND application_id = l_c_application_id;
776
777 BEGIN
778
779 -- Standard Start of API savepoint
780 SAVEPOINT update_msite_resp_pvt;
781
782 -- Standard call to check for call compatibility.
783 IF NOT FND_API.Compatible_API_Call(l_api_version,
784 p_api_version,
785 l_api_name,
786 G_PKG_NAME)
787 THEN
788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
789 END IF;
790
791 -- Initialize message list if p_init_msg_list is set to TRUE.
792 IF FND_API.to_Boolean(p_init_msg_list) THEN
793 FND_MSG_PUB.initialize;
794 END IF;
795
796 -- Initialize API return status to success
797 x_return_status := FND_API.G_RET_STS_SUCCESS;
798
799 -- API body
800 -- CALL FLOW :
801 -- 1. Check if everything is valid
802 -- 2. Update row
803
804 -- 1a. Check if either msite_resp_id or combination of
805 -- msite_id, responsibility_id and application_id is specified
806 IF ((p_msite_resp_id IS NOT NULL) AND
807 (p_msite_resp_id <> FND_API.G_MISS_NUM))
808 THEN
809 -- msite_resp_id specified, continue
810 l_msite_resp_id := p_msite_resp_id;
814 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
811 ELSIF ((p_msite_id IS NOT NULL) AND
812 (p_msite_id <> FND_API.G_MISS_NUM) AND
813 (p_responsibility_id IS NOT NULL) AND
815 (p_application_id IS NOT NULL) AND
816 (p_application_id <> FND_API.G_MISS_NUM))
817 THEN
818 -- If combination of msite_id, responsibility_id and application_id
819 -- is specified, then query for msite_resp_id
820
821 OPEN c1(p_msite_id, p_responsibility_id, p_application_id);
822 FETCH c1 INTO l_msite_resp_id;
823 IF (c1%NOTFOUND) THEN
824 CLOSE c1;
825 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITERESP_NOT_FOUND');
826 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
827 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
828 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 CLOSE c1;
832
833 ELSE
834 -- neither msite_resp_id nor combination of
835 -- msite_id, responsibility_id and application_id is specified
836 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MR_IDS_SPEC');
837 FND_MSG_PUB.Add;
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841 --
842 -- 1b. Validate the input data
843 --
844 Validate_Update
845 (
846 p_init_msg_list => FND_API.G_FALSE,
847 p_validation_level => p_validation_level,
848 p_msite_resp_id => l_msite_resp_id,
849 p_object_version_number => p_object_version_number,
850 p_start_date_active => p_start_date_active,
851 p_end_date_active => p_end_date_active,
852 p_sort_order => p_sort_order,
853 p_display_name => p_display_name,
854 x_return_status => x_return_status,
855 x_msg_count => x_msg_count,
856 x_msg_data => x_msg_data
857 );
858
859 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
860 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MR_INVALID_UPDATE');
861 FND_MSG_PUB.Add;
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
864 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MR_INVALID_UPDATE');
865 FND_MSG_PUB.Add;
866 RAISE FND_API.G_EXC_ERROR; -- invalid
867 END IF;
868
869 -- 2. update row with section data into section table
870 BEGIN
871 Ibe_Msite_Resp_Pkg.update_row
872 (
873 p_msite_resp_id => l_msite_resp_id,
874 p_object_version_number => p_object_version_number,
875 p_start_date_active => p_start_date_active,
876 p_end_date_active => p_end_date_active,
877 p_sort_order => p_sort_order,
878 p_display_name => p_display_name,
879 p_group_code => p_group_code,
880 p_last_update_date => sysdate,
881 p_last_updated_by => FND_GLOBAL.user_id,
882 p_last_update_login => FND_GLOBAL.login_id
883 );
884
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_UPDATE_FL');
888 FND_MSG_PUB.ADD;
889 RAISE FND_API.G_EXC_ERROR;
890 WHEN OTHERS THEN
891 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_UPDATE_FL');
892 FND_MSG_PUB.ADD;
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 END;
895
896 --
897 -- end of main api body.
898
899 -- standard check of p_commit.
900 IF (FND_API.to_boolean(p_commit)) THEN
901 COMMIT WORK;
902 END IF;
903
904 -- standard call to get message count and if count is 1, get message info.
905 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
906 p_data => x_msg_data,
907 p_encoded => 'F');
908
909 EXCEPTION
910
911 WHEN FND_API.G_EXC_ERROR THEN
912 ROLLBACK TO update_msite_resp_pvt;
913 x_return_status := FND_API.G_RET_STS_ERROR;
914 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => 'F');
917
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 ROLLBACK TO update_msite_resp_pvt;
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
922 p_data => x_msg_data,
923 p_encoded => 'F');
924
925 WHEN OTHERS THEN
926 ROLLBACK TO update_msite_resp_pvt;
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928
929 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
930 THEN
931 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
932 END IF;
933
934 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
935 p_data => x_msg_data,
939
936 p_encoded => 'F');
937
938 END Update_Msite_Resp;
940 PROCEDURE Delete_Msite_Resp
941 (
942 p_api_version IN NUMBER,
943 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
944 p_commit IN VARCHAR2 := FND_API.G_FALSE,
945 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
946 p_msite_resp_id IN NUMBER := FND_API.G_MISS_NUM,
947 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
948 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
949 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2
953 )
954 IS
955 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Msite_Resp';
956 l_api_version CONSTANT NUMBER := 1.0;
957
958 l_msite_resp_id NUMBER;
959
960 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
961 l_c_application_id IN NUMBER)
962 IS SELECT msite_resp_id FROM ibe_msite_resps_b
963 WHERE msite_id = l_c_msite_id
964 AND responsibility_id = l_c_responsibility_id
965 AND application_id = l_c_application_id;
966
967 BEGIN
968
969 -- Standard Start of API savepoint
970 SAVEPOINT delete_msite_resp_pvt;
971
972 -- Standard call to check for call compatibility.
973 IF NOT FND_API.Compatible_API_Call(l_api_version,
974 p_api_version,
975 l_api_name,
976 G_PKG_NAME)
977 THEN
978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
979 END IF;
980
981 -- Initialize message list if p_init_msg_list is set to TRUE.
982 IF FND_API.to_Boolean(p_init_msg_list) THEN
983 FND_MSG_PUB.initialize;
984 END IF;
985
986 -- Initialize API return status to success
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 -- CALL FLOW
990
991 -- 1a. Check if either msite_resp_id or combination of
992 -- msite_id, responsibility_id and application_id is specified
993 IF ((p_msite_resp_id IS NOT NULL) AND
994 (p_msite_resp_id <> FND_API.G_MISS_NUM))
995 THEN
996 -- msite_resp_id specified, continue
997 l_msite_resp_id := p_msite_resp_id;
998 ELSIF ((p_msite_id IS NOT NULL) AND
999 (p_msite_id <> FND_API.G_MISS_NUM) AND
1000 (p_responsibility_id IS NOT NULL) AND
1001 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
1002 (p_application_id IS NOT NULL) AND
1003 (p_application_id <> FND_API.G_MISS_NUM))
1004 THEN
1005 -- If combination of msite_id, responsibility_id and application_id
1006 -- is specified, then query for msite_resp_id
1007
1008 OPEN c1(p_msite_id, p_responsibility_id, p_application_id);
1009 FETCH c1 INTO l_msite_resp_id;
1010 IF (c1%NOTFOUND) THEN
1011 CLOSE c1;
1012 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITERESP_NOT_FOUND');
1013 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
1014 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
1015 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018 CLOSE c1;
1019
1020 ELSE
1021 -- neither msite_resp_id nor combination of
1022 -- msite_id, responsibility_id and application_id is specified
1023 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MR_IDS_SPEC');
1024 FND_MSG_PUB.Add;
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027
1028 -- delete for ibe_msite_resps_b and _tl tables
1029 BEGIN
1030 Ibe_Msite_Resp_Pkg.delete_row(l_msite_resp_id);
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1034 FND_MSG_PUB.Add;
1035 RAISE FND_API.G_EXC_ERROR;
1036 WHEN OTHERS THEN
1037 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1038 FND_MSG_PUB.Add;
1039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1040 END;
1041
1042 EXCEPTION
1043
1044 WHEN FND_API.G_EXC_ERROR THEN
1045 ROLLBACK TO delete_msite_resp_pvt;
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1048 p_data => x_msg_data,
1049 p_encoded => 'F');
1050
1051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1052 ROLLBACK TO delete_msite_resp_pvt;
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1055 p_data => x_msg_data,
1056 p_encoded => 'F');
1057
1058 WHEN OTHERS THEN
1059 ROLLBACK TO delete_msite_resp_pvt;
1060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1061
1062 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1063 THEN
1064 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1065 END IF;
1066
1070
1067 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1068 p_data => x_msg_data,
1069 p_encoded => 'F');
1071 END Delete_Msite_Resp;
1072
1073
1074 PROCEDURE Delete_Msite_Resp_Group
1075 (
1076 p_api_version IN NUMBER,
1077 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1078 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1079 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1080 p_msite_resp_id IN NUMBER := FND_API.G_MISS_NUM,
1081 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
1082 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
1083 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
1084 p_group_code IN VARCHAR2,
1085 x_return_status OUT NOCOPY VARCHAR2,
1086 x_msg_count OUT NOCOPY NUMBER,
1087 x_msg_data OUT NOCOPY VARCHAR2
1088 )
1089 IS
1090 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Msite_Resp_Group';
1091 l_api_version CONSTANT NUMBER := 1.0;
1092
1093 l_msite_resp_id NUMBER;
1094 l_count NUMBER;
1095
1096 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
1097 l_c_application_id IN NUMBER)
1098 IS SELECT msite_resp_id
1099 FROM ibe_msite_resps_b
1100 WHERE msite_id = l_c_msite_id
1101 AND responsibility_id = l_c_responsibility_id
1102 AND application_id = l_c_application_id;
1103
1104 CURSOR c2(l_msite_resp_id IN NUMBER)
1105 IS SELECT count(*)
1106 FROM ibe_msite_resps_b
1107 WHERE msite_resp_id = l_msite_resp_id;
1108
1109 BEGIN
1110
1111 -- Standard Start of API savepoint
1112 SAVEPOINT delete_msite_resp_pvt;
1113
1114 -- Standard call to check for call compatibility.
1115 IF NOT FND_API.Compatible_API_Call(l_api_version,
1116 p_api_version,
1117 l_api_name,
1118 G_PKG_NAME)
1119 THEN
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122
1123 -- Initialize message list if p_init_msg_list is set to TRUE.
1124 IF FND_API.to_Boolean(p_init_msg_list) THEN
1125 FND_MSG_PUB.initialize;
1126 END IF;
1127
1128 -- Initialize API return status to success
1129 x_return_status := FND_API.G_RET_STS_SUCCESS;
1130
1131 -- CALL FLOW
1132
1133 -- 1a. Check if either msite_resp_id or combination of
1134 -- msite_id, responsibility_id and application_id is specified
1135 IF ((p_msite_resp_id IS NOT NULL) AND
1136 (p_msite_resp_id <> FND_API.G_MISS_NUM))
1137 THEN
1138 -- msite_resp_id specified, continue
1139 l_msite_resp_id := p_msite_resp_id;
1140 ELSIF ((p_msite_id IS NOT NULL) AND
1141 (p_msite_id <> FND_API.G_MISS_NUM) AND
1142 (p_responsibility_id IS NOT NULL) AND
1143 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
1144 (p_application_id IS NOT NULL) AND
1145 (p_application_id <> FND_API.G_MISS_NUM))
1146 THEN
1147 -- If combination of msite_id, responsibility_id and application_id
1148 -- is specified, then query for msite_resp_id
1149
1150 OPEN c1(p_msite_id, p_responsibility_id, p_application_id);
1151 FETCH c1 INTO l_msite_resp_id;
1152 IF (c1%NOTFOUND) THEN
1153 CLOSE c1;
1154 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITERESP_NOT_FOUND');
1155 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
1156 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
1157 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160 CLOSE c1;
1161
1162 ELSE
1163 -- neither msite_resp_id nor combination of
1164 -- msite_id, responsibility_id and application_id is specified
1165 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MR_IDS_SPEC');
1166 FND_MSG_PUB.Add;
1167 RAISE FND_API.G_EXC_ERROR;
1168 END IF;
1169
1170
1171 OPEN c2(p_msite_resp_id);
1172 FETCH c2 INTO l_count;
1173 IF (c2%NOTFOUND) THEN
1174 CLOSE c2;
1175 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITERESP_NOT_FOUND');
1176 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
1177 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
1178 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
1179 RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181
1182 IF (l_count = 1) THEN
1183 -- update the last group code to be null
1184 BEGIN
1185
1186 UPDATE ibe_msite_resps_b SET
1187 group_code = null
1188 where msite_resp_id = p_msite_resp_id
1189 AND group_code = p_group_code;
1190
1191 IF (sql%NOTFOUND) THEN
1192 RAISE NO_DATA_FOUND;
1193 END IF;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1198 FND_MSG_PUB.Add;
1199 RAISE FND_API.G_EXC_ERROR;
1200 WHEN OTHERS THEN
1201 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1202 FND_MSG_PUB.Add;
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204
1205 END;
1206
1207 ELSE
1208
1209 -- delete for ibe_msite_resps_b and _tl tables
1210 BEGIN
1211
1212 DELETE FROM ibe_msite_resps_b
1213 WHERE msite_resp_id = p_msite_resp_id
1214 AND group_code = p_group_code;
1215
1216 IF (sql%NOTFOUND) THEN
1217 RAISE NO_DATA_FOUND;
1218 END IF;
1219
1220 EXCEPTION
1221 WHEN NO_DATA_FOUND THEN
1222 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1223 FND_MSG_PUB.Add;
1224 RAISE FND_API.G_EXC_ERROR;
1225 WHEN OTHERS THEN
1226 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_RESP_DELETE_FL');
1227 FND_MSG_PUB.Add;
1228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229 END;
1230 END IF;
1231 EXCEPTION
1232
1233 WHEN FND_API.G_EXC_ERROR THEN
1234 ROLLBACK TO delete_msite_resp_pvt;
1235 x_return_status := FND_API.G_RET_STS_ERROR;
1236 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1237 p_data => x_msg_data,
1238 p_encoded => 'F');
1239
1240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1241 ROLLBACK TO delete_msite_resp_pvt;
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1244 p_data => x_msg_data,
1245 p_encoded => 'F');
1246
1247 WHEN OTHERS THEN
1248 ROLLBACK TO delete_msite_resp_pvt;
1249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1250
1251 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1252 THEN
1253 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1254 END IF;
1255
1256 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1257 p_data => x_msg_data,
1258 p_encoded => 'F');
1259
1260 END Delete_Msite_Resp_Group;
1261
1262 END Ibe_Msite_Resp_Pvt;