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