[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_PRTY_ACCSS_PVT
Source
1 PACKAGE BODY Ibe_Msite_Prty_Accss_Pvt AS
2 /* $Header: IBEVMPRB.pls 120.0 2005/05/30 02:17:13 appldev noship $ */
3
4 -- HISTORY
5 -- 12/13/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
6 -- *********************************************************************************
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_PRTY_ACCSS_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMPRB.pls';
10
11 PROCEDURE Validate_Msite_Id_Exists
12 (
13 p_msite_id IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_api_name CONSTANT VARCHAR2(30) :=
20 'Validate_Msite_Id_Exists';
21 l_api_version CONSTANT NUMBER := 1.0;
22 l_tmp_id NUMBER;
23
24 CURSOR c1(l_c_msite_id IN NUMBER)
25 IS SELECT msite_id FROM ibe_msites_b
26 WHERE msite_id = l_c_msite_id
27 AND master_msite_flag = 'N';
28
29 BEGIN
30
31 -- Initialize status to SUCCESS
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 -- Check if msite_id exists in ibe_msites_b
35 OPEN c1(p_msite_id);
36 FETCH c1 INTO l_tmp_id;
37 IF (c1%NOTFOUND) THEN
38 CLOSE c1;
39 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_NOT_FOUND');
40 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
41 FND_MSG_PUB.Add;
42 RAISE FND_API.G_EXC_ERROR;
43 END IF;
44 CLOSE c1;
45
46 EXCEPTION
47
48 WHEN FND_API.G_EXC_ERROR THEN
49 x_return_status := FND_API.G_RET_STS_ERROR;
50 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
51 p_data => x_msg_data,
52 p_encoded => 'F');
53
54 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
55 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
56 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
57 p_data => x_msg_data,
58 p_encoded => 'F');
59
60 WHEN OTHERS THEN
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
62
63 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
64 THEN
65 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
66 END IF;
67
68 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
69 p_data => x_msg_data,
70 p_encoded => 'F');
71
72 END Validate_Msite_Id_Exists;
73
74 PROCEDURE Validate_Prty_Id_Exists
75 (
76 p_party_id IN NUMBER,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2
80 )
81 IS
82 l_api_name CONSTANT VARCHAR2(30) :=
83 'Validate_Prty_Id_Exists';
84 l_api_version CONSTANT NUMBER := 1.0;
85 l_tmp_id NUMBER;
86
87 CURSOR c1(l_c_party_id IN NUMBER)
88 IS SELECT party_id FROM hz_parties
89 WHERE party_id = l_c_party_id;
90
91 BEGIN
92
93 -- Initialize status to SUCCESS
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 -- Check if party_id exists in hz_parties
97 OPEN c1(p_party_id);
98 FETCH c1 INTO l_tmp_id;
99 IF (c1%NOTFOUND) THEN
100 CLOSE c1;
101 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PARTY_NOT_FOUND');
102 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
103 FND_MSG_PUB.Add;
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106 CLOSE c1;
107
108 EXCEPTION
109
110 WHEN FND_API.G_EXC_ERROR THEN
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
113 p_data => x_msg_data,
114 p_encoded => 'F');
115
116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
117 x_return_status := FND_API.G_RET_STS_UNEXP_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 OTHERS THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124
125 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
126 THEN
127 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
128 END IF;
129
130 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
131 p_data => x_msg_data,
132 p_encoded => 'F');
133
134 END Validate_Prty_Id_Exists;
135
136 PROCEDURE Validate_Msite_Prty_Id_Exists
137 (
138 p_msite_prty_accss_id IN NUMBER,
139 p_msite_id IN NUMBER,
140 p_party_id IN NUMBER,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2
144 )
145 IS
146 l_api_name CONSTANT VARCHAR2(30) :=
147 'Validate_Msite_Prty_Id_Exists';
148 l_api_version CONSTANT NUMBER := 1.0;
149 l_tmp_id NUMBER;
150
151 CURSOR c1(l_c_msite_prty_accss_id IN NUMBER)
152 IS SELECT msite_prty_accss_id FROM ibe_msite_prty_accss
153 WHERE msite_prty_accss_id = l_c_msite_prty_accss_id;
154
155 CURSOR c2(l_c_msite_id IN NUMBER, l_c_party_id IN NUMBER)
156 IS SELECT msite_prty_accss_id FROM ibe_msite_prty_accss
157 WHERE msite_id = l_c_msite_id
158 AND party_id = l_c_party_id;
159
160 BEGIN
161
162 -- Initialize status to SUCCESS
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 -- Check if msite_prty_accss_id or combination of msite_id and party_id
166 -- in ibe_msite_prty_accss
167 IF ((p_msite_prty_accss_id IS NOT NULL) AND
168 (p_msite_prty_accss_id <> FND_API.G_MISS_NUM))
169 THEN
170
171 OPEN c1(p_msite_prty_accss_id);
172 FETCH c1 INTO l_tmp_id;
173 IF (c1%NOTFOUND) THEN
174 CLOSE c1;
175 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_NOT_FOUND');
176 FND_MESSAGE.Set_Token('MSITE_PRTY_ACCSS_ID', p_msite_prty_accss_id);
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179 CLOSE c1;
180
181 ELSIF ((p_msite_id IS NOT NULL) AND
182 (p_msite_id <> FND_API.G_MISS_NUM) AND
183 (p_party_id IS NOT NULL) AND
184 (p_party_id <> FND_API.G_MISS_NUM))
185 THEN
186
187 OPEN c2(p_msite_id, p_party_id);
188 FETCH c2 INTO l_tmp_id;
189 IF (c2%NOTFOUND) THEN
190 CLOSE c2;
191 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITEPRTY_NOT_FOUND');
192 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
193 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
194 RAISE FND_API.G_EXC_ERROR;
195 END IF;
196 CLOSE c2;
197
198 ELSE
199 -- neither msite_prty_accss_id nor combination of
200 -- msite_id and party_id is specified
201 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MP_IDS_SPEC');
202 FND_MSG_PUB.Add;
203 RAISE FND_API.G_EXC_ERROR;
204 END IF;
205
206 EXCEPTION
207
208 WHEN FND_API.G_EXC_ERROR THEN
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
211 p_data => x_msg_data,
212 p_encoded => 'F');
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
217 p_data => x_msg_data,
218 p_encoded => 'F');
219
220 WHEN OTHERS THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222
223 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
224 THEN
225 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
226 END IF;
227
228 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
229 p_data => x_msg_data,
230 p_encoded => 'F');
231
232 END Validate_Msite_Prty_Id_Exists;
233
234 PROCEDURE Validate_Create
235 (
236 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
237 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
238 p_msite_id IN NUMBER,
239 p_party_id IN NUMBER,
240 p_start_date_active IN DATE,
241 p_end_date_active IN DATE,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_count OUT NOCOPY NUMBER,
244 x_msg_data OUT NOCOPY VARCHAR2
245 )
246 IS
247 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
248 l_api_version CONSTANT NUMBER := 1.0;
249
250 l_msite_prty_accss_id NUMBER;
251 l_msite_id NUMBER;
252 l_party_id NUMBER;
253
254 BEGIN
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean(p_init_msg_list) THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263
264 --
265 -- Check null values for required fields
266 --
267
268 -- p_msite_id
269 IF ((p_msite_id IS NULL) OR
270 (p_msite_id = FND_API.G_MISS_NUM))
271 THEN
272 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_MSITE_ID');
273 FND_MSG_PUB.Add;
274 RAISE FND_API.G_EXC_ERROR;
275 END IF;
276
277 -- p_party_id
278 IF ((p_party_id IS NULL) OR
279 (p_party_id = FND_API.G_MISS_NUM))
280 THEN
281 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_PRTY_ID');
282 FND_MSG_PUB.Add;
283 RAISE FND_API.G_EXC_ERROR;
284 END IF;
285
286 -- start_date_active
287 IF ((p_start_date_active IS NULL) OR
288 (p_start_date_active = FND_API.G_MISS_DATE))
289 THEN
290 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
291 FND_MSG_PUB.Add;
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294
295 --
296 -- non-null field validation
297 --
298
299 --
300 -- Foreign key integrity constraint check
301 --
302
303 -- msite_id
304 Validate_Msite_Id_Exists
305 (
306 p_msite_id => p_msite_id,
307 x_return_status => x_return_status,
308 x_msg_count => x_msg_count,
309 x_msg_data => x_msg_data
310 );
311
312 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
313 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
314 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
315 FND_MSG_PUB.Add;
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
318 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
319 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
320 RAISE FND_API.G_EXC_ERROR; -- invalid msite_id
321 END IF;
322
323 -- party_id
324 Validate_Prty_Id_Exists
325 (
326 p_party_id => p_party_id,
327 x_return_status => x_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data
330 );
331
332 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
333 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PRTY_ID_VLD_FAIL');
334 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
335 FND_MSG_PUB.Add;
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
338 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PRTY_ID_VLD_FAIL');
339 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342
343 -- p_msite_id, p_party_id (check for duplicate)
344 Validate_Msite_Prty_Id_Exists
345 (
346 p_msite_prty_accss_id => FND_API.G_MISS_NUM,
347 p_msite_id => p_msite_id,
348 p_party_id => p_party_id,
349 x_return_status => x_return_status,
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data
352 );
353
354 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
357 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSPRT_ID_VLD_FAIL');
358 FND_MSG_PUB.Add;
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN -- duplicate exists
361 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSPRT_ID_DUP_EXISTS');
362 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
363 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
364 FND_MSG_PUB.Add;
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 EXCEPTION
369
370 WHEN FND_API.G_EXC_ERROR THEN
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
373 p_data => x_msg_data,
374 p_encoded => 'F');
375
376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
379 p_data => x_msg_data,
380 p_encoded => 'F');
381
382 WHEN OTHERS THEN
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
384
385 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
386 THEN
387 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
388 END IF;
389
390 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
391 p_data => x_msg_data,
392 p_encoded => 'F');
393
394 END Validate_Create;
395
396 PROCEDURE Validate_Update
397 (
398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
400 p_msite_prty_accss_id IN NUMBER,
401 p_object_version_number IN NUMBER,
402 p_start_date_active IN DATE,
403 p_end_date_active IN DATE,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2
407 )
408 IS
409 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
410 l_api_version CONSTANT NUMBER := 1.0;
411
412 l_msite_prty_accss_id NUMBER;
413 l_msite_id NUMBER;
414 l_party_id NUMBER;
415
416 BEGIN
417
418 -- Initialize message list if p_init_msg_list is set to TRUE.
419 IF FND_API.to_Boolean(p_init_msg_list) THEN
420 FND_MSG_PUB.initialize;
421 END IF;
422
423 -- Initialize API return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426 --
427 -- Check null values for required fields
428 --
429
430 -- msite_prty_accss_id
431 IF (p_msite_prty_accss_id IS NULL) THEN
432 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSPRT_ID_IS_NULL');
433 FND_MSG_PUB.Add;
434 RAISE FND_API.G_EXC_ERROR;
435 END IF;
436
437 -- object_version_number
438 IF (p_object_version_number IS NULL)
439 THEN
440 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_OVN_IS_NULL');
441 FND_MSG_PUB.Add;
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444
445 -- start_date_active
446 IF (p_start_date_active IS NULL) THEN
447 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
448 FND_MSG_PUB.Add;
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 --
453 -- non-null field validation
454 --
455
456 -- check if the association already exists, if not, then throw error
457 Validate_Msite_Prty_Id_Exists
458 (
459 p_msite_prty_accss_id => p_msite_prty_accss_id,
460 p_msite_id => FND_API.G_MISS_NUM,
461 p_party_id => FND_API.G_MISS_NUM,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data
465 );
466
467 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
468 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSPRT_ID_VLD_FAIL');
469 FND_MSG_PUB.Add;
470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
472 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSPRT_ID_VLD_FAIL');
473 FND_MSG_PUB.Add;
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476
477
478 EXCEPTION
479
480 WHEN FND_API.G_EXC_ERROR THEN
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
483 p_data => x_msg_data,
484 p_encoded => 'F');
485
486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
489 p_data => x_msg_data,
490 p_encoded => 'F');
491
492 WHEN OTHERS THEN
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494
495 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
496 THEN
497 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
498 END IF;
499
500 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
501 p_data => x_msg_data,
502 p_encoded => 'F');
503
504 END Validate_Update;
505
506
507 PROCEDURE Create_Msite_Prty_Accss
508 (
509 p_api_version IN NUMBER,
510 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
511 p_commit IN VARCHAR2 := FND_API.G_FALSE,
512 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
513 p_msite_id IN NUMBER,
514 p_party_id IN NUMBER,
515 p_start_date_active IN DATE,
516 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
517 x_msite_prty_accss_id OUT NOCOPY NUMBER,
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2
521 )
522 IS
523 l_api_name CONSTANT VARCHAR2(30) := 'Create_Msite_Prty_Accss';
524 l_api_version CONSTANT NUMBER := 1.0;
525
526 l_object_version_number CONSTANT NUMBER := 1;
527 l_rowid VARCHAR2(30);
528
529 BEGIN
530
531 -- Standard Start of API savepoint
532 SAVEPOINT create_msite_prty_accss_pvt;
533
534 -- Standard call to check for call compatibility.
535 IF NOT FND_API.Compatible_API_Call(l_api_version,
536 p_api_version,
537 l_api_name,
538 G_PKG_NAME)
539 THEN
540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
541 END IF;
542
543 -- Initialize message list if p_init_msg_list is set to TRUE.
544 IF FND_API.to_Boolean(p_init_msg_list) THEN
545 FND_MSG_PUB.initialize;
546 END IF;
547
548 -- Initialize API return status to success
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 -- API body
552 -- CALL FLOW :
553 -- 1. Check if everything is valid
554 -- 2. Insert row with section data into section table
555 --
556
557 --
558 -- 1. Check if everything is valid
559 --
560 Validate_Create
561 (
562 p_init_msg_list => FND_API.G_FALSE,
563 p_validation_level => p_validation_level,
564 p_msite_id => p_msite_id,
565 p_party_id => p_party_id,
566 p_start_date_active => p_start_date_active,
567 p_end_date_active => p_end_date_active,
568 x_return_status => x_return_status,
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data
571 );
572
573 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
574 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MP_INVALID_CREATE');
575 FND_MSG_PUB.Add;
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
578 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MP_INVALID_CREATE');
579 FND_MSG_PUB.Add;
580 RAISE FND_API.G_EXC_ERROR; -- invalid
581 END IF;
582
583 --
584 -- 2. Insert row
585 --
586 BEGIN
587 Ibe_Msite_Prty_Accss_Pkg.insert_row
588 (
589 p_msite_prty_accss_id => FND_API.G_MISS_NUM,
590 p_object_version_number => l_object_version_number,
591 p_msite_id => p_msite_id,
592 p_party_id => p_party_id,
593 p_start_date_active => p_start_date_active,
594 p_end_date_active => p_end_date_active,
595 p_creation_date => sysdate,
596 p_created_by => FND_GLOBAL.user_id,
597 p_last_update_date => sysdate,
598 p_last_updated_by => FND_GLOBAL.user_id,
599 p_last_update_login => FND_GLOBAL.login_id,
600 x_rowid => l_rowid,
601 x_msite_prty_accss_id => x_msite_prty_accss_id
602 );
603
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_INSERT_FL');
607 FND_MSG_PUB.Add;
608 RAISE FND_API.G_EXC_ERROR;
609 WHEN OTHERS THEN
610 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_INSERT_FL');
611 FND_MSG_PUB.Add;
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END;
614
615 --
616 -- End of main API body.
617
618 -- Standard check of p_commit.
619 IF (FND_API.To_Boolean(p_commit)) THEN
620 COMMIT WORK;
621 END IF;
622
623 -- Standard call to get message count and if count is 1, get message info.
624 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
625 p_data => x_msg_data,
626 p_encoded => 'F');
627
628 EXCEPTION
629
630 WHEN FND_API.G_EXC_ERROR THEN
631 ROLLBACK TO create_msite_prty_accss_pvt;
632 x_return_status := FND_API.G_RET_STS_ERROR;
633 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
634 p_data => x_msg_data,
635 p_encoded => 'F');
636
637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
638 ROLLBACK TO create_msite_prty_accss_pvt;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
641 p_data => x_msg_data,
642 p_encoded => 'F');
643
644 WHEN OTHERS THEN
645 ROLLBACK TO create_msite_prty_accss_pvt;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647
648 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
649 THEN
650 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
651 END IF;
652
653 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
654 p_data => x_msg_data,
655 p_encoded => 'F');
656
657 END Create_Msite_Prty_Accss;
658
659 PROCEDURE Update_Msite_Prty_Accss
660 (
661 p_api_version IN NUMBER,
662 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
663 p_commit IN VARCHAR2 := FND_API.G_FALSE,
664 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
665 p_msite_prty_accss_id IN NUMBER := FND_API.G_MISS_NUM,
666 p_object_version_number IN NUMBER,
667 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
668 p_party_id IN NUMBER := FND_API.G_MISS_NUM,
669 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
670 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2
674 )
675 IS
676 l_api_name CONSTANT VARCHAR2(30) := 'Update_Msite_Prty_Accss';
677 l_api_version CONSTANT NUMBER := 1.0;
678
679 l_msite_prty_accss_id NUMBER;
680
681 CURSOR c1(l_c_msite_id IN NUMBER, l_c_party_id IN NUMBER)
682 IS SELECT msite_prty_accss_id FROM ibe_msite_prty_accss
683 WHERE msite_id = l_c_msite_id
684 AND party_id = l_c_party_id;
685
686 BEGIN
687
688 -- Standard Start of API savepoint
689 SAVEPOINT update_msite_prty_accss_pvt;
690
691 -- Standard call to check for call compatibility.
692 IF NOT FND_API.Compatible_API_Call(l_api_version,
693 p_api_version,
694 l_api_name,
695 G_PKG_NAME)
696 THEN
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 END IF;
699
700 -- Initialize message list if p_init_msg_list is set to TRUE.
701 IF FND_API.to_Boolean(p_init_msg_list) THEN
702 FND_MSG_PUB.initialize;
703 END IF;
704
705 -- Initialize API return status to success
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 -- API body
709 -- CALL FLOW :
710 -- 1. Check if everything is valid
711 -- 2. Update row
712
713 -- 1a. Check if either msite_prty_accss_id or combination of
714 -- msite_id and party_id is specified
715 IF ((p_msite_prty_accss_id IS NOT NULL) AND
716 (p_msite_prty_accss_id <> FND_API.G_MISS_NUM))
717 THEN
718 -- msite_prty_accss_id specified, continue
719 l_msite_prty_accss_id := p_msite_prty_accss_id;
720 ELSIF ((p_msite_id IS NOT NULL) AND
721 (p_msite_id <> FND_API.G_MISS_NUM) AND
722 (p_party_id IS NOT NULL) AND
723 (p_party_id <> FND_API.G_MISS_NUM))
724 THEN
725 -- If combination of msite_id and party_id
726 -- is specified, then query for msite_prty_accss_id
727
728 OPEN c1(p_msite_id, p_party_id);
729 FETCH c1 INTO l_msite_prty_accss_id;
730 IF (c1%NOTFOUND) THEN
731 CLOSE c1;
732 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITEPRTY_NOT_FOUND');
733 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
734 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737 CLOSE c1;
738
739 ELSE
740 -- neither msite_prty_accss_id nor combination of
741 -- msite_id and party_id is specified
742 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MP_IDS_SPEC');
743 FND_MSG_PUB.Add;
744 RAISE FND_API.G_EXC_ERROR;
745 END IF;
746
747 --
748 -- 1b. Validate the input data
749 --
750 Validate_Update
751 (
752 p_init_msg_list => FND_API.G_FALSE,
753 p_validation_level => p_validation_level,
754 p_msite_prty_accss_id => l_msite_prty_accss_id,
755 p_object_version_number => p_object_version_number,
756 p_start_date_active => p_start_date_active,
757 p_end_date_active => p_end_date_active,
758 x_return_status => x_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data
761 );
762
763 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
764 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MP_INVALID_UPDATE');
765 FND_MSG_PUB.Add;
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
768 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MP_INVALID_UPDATE');
769 FND_MSG_PUB.Add;
770 RAISE FND_API.G_EXC_ERROR; -- invalid
771 END IF;
772
773 -- 2. update row with section data into section table
774 BEGIN
775 Ibe_Msite_Prty_Accss_Pkg.update_row
776 (
777 p_msite_prty_accss_id => l_msite_prty_accss_id,
778 p_object_version_number => p_object_version_number,
779 p_start_date_active => p_start_date_active,
780 p_end_date_active => p_end_date_active,
781 p_last_update_date => sysdate,
782 p_last_updated_by => FND_GLOBAL.user_id,
783 p_last_update_login => FND_GLOBAL.login_id
784 );
785
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_UPDATE_FL');
789 FND_MSG_PUB.ADD;
790 RAISE FND_API.G_EXC_ERROR;
791 WHEN OTHERS THEN
792 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_UPDATE_FL');
793 FND_MSG_PUB.ADD;
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 END;
796
797 --
798 -- end of main api body.
799
800 -- standard check of p_commit.
801 IF (FND_API.to_boolean(p_commit)) THEN
802 COMMIT WORK;
803 END IF;
804
805 -- standard call to get message count and if count is 1, get message info.
806 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
807 p_data => x_msg_data,
808 p_encoded => 'F');
809
810 EXCEPTION
811
812 WHEN FND_API.G_EXC_ERROR THEN
813 ROLLBACK TO update_msite_prty_accss_pvt;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
816 p_data => x_msg_data,
817 p_encoded => 'F');
818
819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
820 ROLLBACK TO update_msite_prty_accss_pvt;
821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
822 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
823 p_data => x_msg_data,
824 p_encoded => 'F');
825
826 WHEN OTHERS THEN
827 ROLLBACK TO update_msite_prty_accss_pvt;
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
829
830 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
831 THEN
832 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
833 END IF;
834
835 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
836 p_data => x_msg_data,
837 p_encoded => 'F');
838
839 END Update_Msite_Prty_Accss;
840
841 PROCEDURE Delete_Msite_Prty_Accss
842 (
843 p_api_version IN NUMBER,
844 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
845 p_commit IN VARCHAR2 := FND_API.G_FALSE,
846 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
847 p_msite_prty_accss_id IN NUMBER := FND_API.G_MISS_NUM,
848 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
849 p_party_id IN NUMBER := FND_API.G_MISS_NUM,
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_count OUT NOCOPY NUMBER,
852 x_msg_data OUT NOCOPY VARCHAR2
853 )
854 IS
855 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Msite_Prty_Accss';
856 l_api_version CONSTANT NUMBER := 1.0;
857
858 l_msite_prty_accss_id NUMBER;
859
860 CURSOR c1(l_c_msite_id IN NUMBER, l_c_party_id IN NUMBER)
861 IS SELECT msite_prty_accss_id FROM ibe_msite_prty_accss
862 WHERE msite_id = l_c_msite_id
863 AND party_id = l_c_party_id;
864
865 BEGIN
866
867 -- Standard Start of API savepoint
868 SAVEPOINT delete_msite_prty_accss_pvt;
869
870 -- Standard call to check for call compatibility.
871 IF NOT FND_API.Compatible_API_Call(l_api_version,
872 p_api_version,
873 l_api_name,
874 G_PKG_NAME)
875 THEN
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 END IF;
878
879 -- Initialize message list if p_init_msg_list is set to TRUE.
880 IF FND_API.to_Boolean(p_init_msg_list) THEN
881 FND_MSG_PUB.initialize;
882 END IF;
883
884 -- Initialize API return status to success
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886
887 -- CALL FLOW
888
889 -- 1a. Check if either msite_prty_accss_id or combination of
890 -- msite_id and party_id is specified
891 IF ((p_msite_prty_accss_id IS NOT NULL) AND
892 (p_msite_prty_accss_id <> FND_API.G_MISS_NUM))
893 THEN
894 -- msite_prty_accss_id specified, continue
895 l_msite_prty_accss_id := p_msite_prty_accss_id;
896 ELSIF ((p_msite_id IS NOT NULL) AND
897 (p_msite_id <> FND_API.G_MISS_NUM) AND
898 (p_party_id IS NOT NULL) AND
899 (p_party_id <> FND_API.G_MISS_NUM))
900 THEN
901 -- If combination of msite_id and party_id
902 -- is specified, then query for msite_prty_accss_id
903
904 OPEN c1(p_msite_id, p_party_id);
905 FETCH c1 INTO l_msite_prty_accss_id;
906 IF (c1%NOTFOUND) THEN
907 CLOSE c1;
908 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITEPRTY_NOT_FOUND');
909 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
910 FND_MESSAGE.Set_Token('PARTY_ID', p_party_id);
911 RAISE FND_API.G_EXC_ERROR;
912 END IF;
913 CLOSE c1;
914
915 ELSE
916 -- neither msite_prty_accss_id nor combination of
917 -- msite_id and party_id is specified
918 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MP_IDS_SPEC');
919 FND_MSG_PUB.Add;
920 RAISE FND_API.G_EXC_ERROR;
921 END IF;
922
923 -- delete for ibe_msite_prty_accss
924 BEGIN
925 Ibe_Msite_Prty_Accss_Pkg.delete_row(l_msite_prty_accss_id);
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_DELETE_FL');
929 FND_MSG_PUB.Add;
930 RAISE FND_API.G_EXC_ERROR;
931 WHEN OTHERS THEN
932 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_PRTY_DELETE_FL');
933 FND_MSG_PUB.Add;
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
935 END;
936
937 EXCEPTION
938
939 WHEN FND_API.G_EXC_ERROR THEN
940 ROLLBACK TO delete_msite_prty_accss_pvt;
941 x_return_status := FND_API.G_RET_STS_ERROR;
942 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
943 p_data => x_msg_data,
944 p_encoded => 'F');
945
946 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
947 ROLLBACK TO delete_msite_prty_accss_pvt;
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
950 p_data => x_msg_data,
951 p_encoded => 'F');
952
953 WHEN OTHERS THEN
954 ROLLBACK TO delete_msite_prty_accss_pvt;
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956
957 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
958 THEN
959 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
960 END IF;
961
962 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => 'F');
965
966 END Delete_Msite_Prty_Accss;
967
968 END Ibe_Msite_Prty_Accss_Pvt;