[Home] [Help]
PACKAGE BODY: APPS.JTF_MSITE_RESP_PVT
Source
1 PACKAGE BODY Jtf_Msite_Resp_Pvt AS
2 /* $Header: JTFVMRSB.pls 115.2 2002/02/14 05:49:39 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_MSITE_RESP_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):= 'JTFVMRSB.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_Resp_Appl_Id_Exists
71 (
72 p_responsibility_id IN NUMBER,
73 p_application_id IN NUMBER,
74 x_return_status OUT VARCHAR2,
75 x_msg_count OUT NUMBER,
76 x_msg_data OUT VARCHAR2
77 )
78 IS
79 l_api_name CONSTANT VARCHAR2(30) :=
80 'Validate_Resp_Appl_Id_Exists';
81 l_api_version CONSTANT NUMBER := 1.0;
82 l_tmp_id NUMBER;
83
84 CURSOR c1(l_c_responsibility_id IN NUMBER, l_c_application_id IN NUMBER)
85 IS SELECT responsibility_id FROM fnd_responsibility_vl
86 WHERE responsibility_id = l_c_responsibility_id
87 AND application_id = l_c_application_id;
88
89 BEGIN
90
91 -- Initialize status to SUCCESS
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- Check if responsibility_id and application_id combination
95 -- exists in fnd_responsibility
96 OPEN c1(p_responsibility_id, p_application_id);
97 FETCH c1 INTO l_tmp_id;
98 IF (c1%NOTFOUND) THEN
99 CLOSE c1;
100 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_RESP_APPL_NOT_FOUND');
101 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
102 FND_MESSAGE.Set_Token('APPL_ID', p_application_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_Resp_Appl_Id_Exists;
135
136 PROCEDURE Validate_Msite_Resp_Id_Exists
137 (
138 p_msite_resp_id IN NUMBER,
139 p_msite_id IN NUMBER,
140 p_responsibility_id IN NUMBER,
141 p_application_id IN NUMBER,
142 x_return_status OUT VARCHAR2,
143 x_msg_count OUT NUMBER,
144 x_msg_data OUT VARCHAR2
145 )
146 IS
147 l_api_name CONSTANT VARCHAR2(30) :=
148 'Validate_Msite_Resp_Id_Exists';
149 l_api_version CONSTANT NUMBER := 1.0;
150 l_tmp_id NUMBER;
151
152 CURSOR c1(l_c_msite_resp_id IN NUMBER)
153 IS SELECT msite_resp_id FROM jtf_msite_resps_b
154 WHERE msite_resp_id = l_c_msite_resp_id;
155
156 CURSOR c2(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
157 l_c_application_id IN NUMBER)
158 IS SELECT msite_resp_id FROM jtf_msite_resps_b
159 WHERE msite_id = l_c_msite_id
160 AND responsibility_id = l_c_responsibility_id
161 AND application_id = l_c_application_id;
162
163 BEGIN
164
165 -- Initialize status to SUCCESS
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167
168 -- Check if msite_resp_id or combination of msite_id, responsibility_id,
169 -- application_id exists in jtf_msite_resps_b
170 IF ((p_msite_resp_id IS NOT NULL) AND
171 (p_msite_resp_id <> FND_API.G_MISS_NUM))
172 THEN
173
174 OPEN c1(p_msite_resp_id);
175 FETCH c1 INTO l_tmp_id;
176 IF (c1%NOTFOUND) THEN
177 CLOSE c1;
178 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_NOT_FOUND');
179 FND_MESSAGE.Set_Token('MSITE_RESP_ID', p_msite_resp_id);
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182 CLOSE c1;
183
184 ELSIF ((p_msite_id IS NOT NULL) AND
185 (p_msite_id <> FND_API.G_MISS_NUM) AND
186 (p_responsibility_id IS NOT NULL) AND
187 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
188 (p_application_id IS NOT NULL) AND
189 (p_application_id <> FND_API.G_MISS_NUM))
190 THEN
191
192 OPEN c2(p_msite_id, p_responsibility_id, p_application_id);
193 FETCH c2 INTO l_tmp_id;
194 IF (c2%NOTFOUND) THEN
195 CLOSE c2;
196 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITERESP_NOT_FOUND');
197 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
198 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
199 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202 CLOSE c2;
203
204 ELSE
205 -- neither msite_resp_id nor combination of
206 -- msite_id, responsibility_id and application_id is specified
207 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_MR_IDS_SPEC');
208 FND_MSG_PUB.Add;
209 RAISE FND_API.G_EXC_ERROR;
210 END IF;
211
212 EXCEPTION
213
214 WHEN FND_API.G_EXC_ERROR THEN
215 x_return_status := FND_API.G_RET_STS_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 FND_API.G_EXC_UNEXPECTED_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_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 OTHERS THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228
229 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
230 THEN
231 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
232 END IF;
233
234 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
235 p_data => x_msg_data,
236 p_encoded => 'F');
237
238 END Validate_Msite_Resp_Id_Exists;
239
240 PROCEDURE Validate_Create
241 (
242 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 p_msite_id IN NUMBER,
245 p_responsibility_id IN NUMBER,
246 p_application_id IN NUMBER,
247 p_start_date_active IN DATE,
248 p_end_date_active IN DATE,
249 p_sort_order IN NUMBER,
250 p_display_name IN VARCHAR2,
251 x_return_status OUT VARCHAR2,
252 x_msg_count OUT NUMBER,
253 x_msg_data OUT VARCHAR2
254 )
255 IS
256 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
257 l_api_version CONSTANT NUMBER := 1.0;
258
259 l_msite_resp_id NUMBER;
260 l_msite_id NUMBER;
261 l_responsibility_id NUMBER;
262 l_application_id NUMBER;
263
264 BEGIN
265
266 -- Initialize message list if p_init_msg_list is set to TRUE.
267 IF FND_API.to_Boolean(p_init_msg_list) THEN
268 FND_MSG_PUB.initialize;
269 END IF;
270
271 -- Initialize API return status to success
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273
274 --
275 -- Check null values for required fields
276 --
277
278 -- p_msite_id
279 IF ((p_msite_id IS NULL) OR
280 (p_msite_id = FND_API.G_MISS_NUM))
281 THEN
282 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_MSITE_ID');
283 FND_MSG_PUB.Add;
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286
287 -- p_responsibility_id
288 IF ((p_responsibility_id IS NULL) OR
289 (p_responsibility_id = FND_API.G_MISS_NUM))
290 THEN
291 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVALID_RESP_ID');
292 FND_MSG_PUB.Add;
293 RAISE FND_API.G_EXC_ERROR;
294 END IF;
295
296 -- application_id
297 IF ((p_application_id IS NULL) OR
298 (p_application_id = FND_API.G_MISS_NUM))
299 THEN
300 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVALID_APPL_ID');
301 FND_MSG_PUB.Add;
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304
305 -- start_date_active
306 IF ((p_start_date_active IS NULL) OR
307 (p_start_date_active = FND_API.G_MISS_DATE))
308 THEN
309 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_START_DATE');
310 FND_MSG_PUB.Add;
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 -- display_name
315 IF ((p_display_name IS NULL) OR
316 (p_display_name = FND_API.G_MISS_CHAR))
317 THEN
318 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_MSRSP_DSP_NAME');
319 FND_MSG_PUB.Add;
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322
323 --
324 -- non-null field validation
325 --
326
327 -- sort order
328 IF ((p_sort_order IS NOT NULL) AND
329 (p_sort_order <> FND_API.G_MISS_NUM))
330 THEN
331 IF(p_sort_order < 0) THEN
332 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_SORT_ORDER');
333 FND_MSG_PUB.Add;
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336 END IF;
337
338 --
339 -- Foreign key integrity constraint check
340 --
341
342 -- msite_id
343 Validate_Msite_Id_Exists
344 (
345 p_msite_id => p_msite_id,
346 x_return_status => x_return_status,
347 x_msg_count => x_msg_count,
348 x_msg_data => x_msg_data
349 );
350
351 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
352 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_ID_VLD_FAIL');
353 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
354 FND_MSG_PUB.Add;
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
357 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_ID_VLD_FAIL');
358 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
359 RAISE FND_API.G_EXC_ERROR; -- invalid msite_id
360 END IF;
361
362 -- responsibility_id and application_id
363 Validate_Resp_Appl_Id_Exists
364 (
365 p_responsibility_id => p_responsibility_id,
366 p_application_id => p_application_id,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data
370 );
371
372 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
373 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_RESP_ID_VLD_FAIL');
374 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
375 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
376 FND_MSG_PUB.Add;
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
379 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_RESP_ID_VLD_FAIL');
380 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
381 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385 -- p_msite_id, p_responsibility_id, p_application_id (check for duplicate)
386 Validate_Msite_Resp_Id_Exists
387 (
388 p_msite_resp_id => FND_API.G_MISS_NUM,
389 p_msite_id => p_msite_id,
390 p_responsibility_id => p_responsibility_id,
391 p_application_id => p_application_id,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data
395 );
396
397 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
400 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSRSP_ID_VLD_FAIL');
401 FND_MSG_PUB.Add;
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN -- duplicate exists
404 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSRSP_ID_DUP_EXISTS');
405 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
406 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
407 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
408 FND_MSG_PUB.Add;
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411
412 EXCEPTION
413
414 WHEN FND_API.G_EXC_ERROR THEN
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
417 p_data => x_msg_data,
418 p_encoded => 'F');
419
420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
423 p_data => x_msg_data,
424 p_encoded => 'F');
425
426 WHEN OTHERS THEN
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428
429 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
430 THEN
431 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
432 END IF;
433
434 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
435 p_data => x_msg_data,
436 p_encoded => 'F');
437
438 END Validate_Create;
439
440 PROCEDURE Validate_Update
441 (
442 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
443 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
444 p_msite_resp_id IN NUMBER,
445 p_object_version_number IN NUMBER,
446 p_start_date_active IN DATE,
447 p_end_date_active IN DATE,
448 p_sort_order IN NUMBER,
449 p_display_name IN VARCHAR2,
450 x_return_status OUT VARCHAR2,
451 x_msg_count OUT NUMBER,
452 x_msg_data OUT VARCHAR2
453 )
454 IS
455 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
456 l_api_version CONSTANT NUMBER := 1.0;
457
458 l_msite_resp_id NUMBER;
459 l_msite_id NUMBER;
460 l_responsibility_id NUMBER;
461 l_application_id NUMBER;
462
463 BEGIN
464
465 -- Initialize message list if p_init_msg_list is set to TRUE.
466 IF FND_API.to_Boolean(p_init_msg_list) THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469
470 -- Initialize API return status to success
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472
473 --
474 -- Check null values for required fields
475 --
476
477 -- msite_resp_id
478 IF (p_msite_resp_id IS NULL) THEN
479 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSRSP_ID_IS_NULL');
480 FND_MSG_PUB.Add;
481 RAISE FND_API.G_EXC_ERROR;
482 END IF;
483
484 -- object_version_number
485 IF (p_object_version_number IS NULL)
486 THEN
487 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_OVN_IS_NULL');
488 FND_MSG_PUB.Add;
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 -- start_date_active
493 IF (p_start_date_active IS NULL) THEN
494 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_START_DATE');
495 FND_MSG_PUB.Add;
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499 -- display_name
500 IF (p_display_name IS NULL) THEN
501 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_MSRSP_DSP_NAME');
502 FND_MSG_PUB.Add;
503 RAISE FND_API.G_EXC_ERROR;
504 END IF;
505
506 --
507 -- non-null field validation
508 --
509
510 -- sort order
511 IF ((p_sort_order IS NOT NULL) AND
512 (p_sort_order <> FND_API.G_MISS_NUM))
513 THEN
514 IF(p_sort_order < 0) THEN
515 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_SORT_ORDER');
516 FND_MSG_PUB.Add;
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519 END IF;
520
521 -- check if the association already exists, if not, then throw error
522 Validate_Msite_Resp_Id_Exists
523 (
524 p_msite_resp_id => p_msite_resp_id,
525 p_msite_id => FND_API.G_MISS_NUM,
526 p_responsibility_id => FND_API.G_MISS_NUM,
527 p_application_id => FND_API.G_MISS_NUM,
528 x_return_status => x_return_status,
529 x_msg_count => x_msg_count,
530 x_msg_data => x_msg_data
531 );
532
533 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
534 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSRSP_ID_VLD_FAIL');
535 FND_MSG_PUB.Add;
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
538 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSRSP_ID_VLD_FAIL');
539 FND_MSG_PUB.Add;
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542
543
544 EXCEPTION
545
546 WHEN FND_API.G_EXC_ERROR THEN
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
549 p_data => x_msg_data,
550 p_encoded => 'F');
551
552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
555 p_data => x_msg_data,
556 p_encoded => 'F');
557
558 WHEN OTHERS THEN
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560
561 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
562 THEN
563 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
564 END IF;
565
566 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
567 p_data => x_msg_data,
568 p_encoded => 'F');
569
570 END Validate_Update;
571
572
573 PROCEDURE Create_Msite_Resp
574 (
575 p_api_version IN NUMBER,
576 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
577 p_commit IN VARCHAR2 := FND_API.G_FALSE,
578 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
579 p_msite_id IN NUMBER,
580 p_responsibility_id IN NUMBER,
581 p_application_id IN NUMBER,
582 p_start_date_active IN DATE,
583 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
584 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
585 p_display_name IN VARCHAR2,
586 x_msite_resp_id OUT NUMBER,
587 x_return_status OUT VARCHAR2,
588 x_msg_count OUT NUMBER,
589 x_msg_data OUT VARCHAR2
590 )
591 IS
592 l_api_name CONSTANT VARCHAR2(30) := 'Create_Msite_Resp';
593 l_api_version CONSTANT NUMBER := 1.0;
594
595 l_object_version_number CONSTANT NUMBER := 1;
596 l_rowid VARCHAR2(30);
597
598 BEGIN
599
600 -- Standard Start of API savepoint
601 SAVEPOINT create_msite_resp_pvt;
602
603 -- Standard call to check for call compatibility.
604 IF NOT FND_API.Compatible_API_Call(l_api_version,
605 p_api_version,
606 l_api_name,
607 G_PKG_NAME)
608 THEN
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610 END IF;
611
612 -- Initialize message list if p_init_msg_list is set to TRUE.
613 IF FND_API.to_Boolean(p_init_msg_list) THEN
614 FND_MSG_PUB.initialize;
615 END IF;
616
617 -- Initialize API return status to success
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619
620 -- API body
621 -- CALL FLOW :
622 -- 1. Check if everything is valid
623 -- 2. Insert row with section data into section table
624 --
625
626 --
627 -- 1. Check if everything is valid
628 --
629 Validate_Create
630 (
631 p_init_msg_list => FND_API.G_FALSE,
632 p_validation_level => p_validation_level,
633 p_msite_id => p_msite_id,
634 p_responsibility_id => p_responsibility_id,
635 p_application_id => p_application_id,
636 p_start_date_active => p_start_date_active,
637 p_end_date_active => p_end_date_active,
638 p_sort_order => p_sort_order,
639 p_display_name => p_display_name,
640 x_return_status => x_return_status,
641 x_msg_count => x_msg_count,
642 x_msg_data => x_msg_data
643 );
644
645 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
646 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MR_INVALID_CREATE');
647 FND_MSG_PUB.Add;
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
650 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MR_INVALID_CREATE');
651 FND_MSG_PUB.Add;
652 RAISE FND_API.G_EXC_ERROR; -- invalid
653 END IF;
654
655 --
656 -- 2. Insert row
657 --
658 BEGIN
659 Jtf_Msite_Resp_Pkg.insert_row
660 (
661 p_msite_resp_id => FND_API.G_MISS_NUM,
662 p_object_version_number => l_object_version_number,
663 p_msite_id => p_msite_id,
664 p_responsibility_id => p_responsibility_id,
665 p_application_id => p_application_id,
666 p_start_date_active => p_start_date_active,
667 p_end_date_active => p_end_date_active,
668 p_sort_order => p_sort_order,
669 p_security_group_id => FND_API.G_MISS_NUM,
670 p_display_name => p_display_name,
671 p_creation_date => sysdate,
672 p_created_by => FND_GLOBAL.user_id,
673 p_last_update_date => sysdate,
674 p_last_updated_by => FND_GLOBAL.user_id,
675 p_last_update_login => FND_GLOBAL.login_id,
676 x_rowid => l_rowid,
677 x_msite_resp_id => x_msite_resp_id
678 );
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_INSERT_FL');
683 FND_MSG_PUB.Add;
684 RAISE FND_API.G_EXC_ERROR;
685 WHEN OTHERS THEN
686 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_INSERT_FL');
687 FND_MSG_PUB.Add;
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689 END;
690
691 --
692 -- End of main API body.
693
694 -- Standard check of p_commit.
695 IF (FND_API.To_Boolean(p_commit)) THEN
696 COMMIT WORK;
697 END IF;
698
699 -- Standard call to get message count and if count is 1, get message info.
700 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
701 p_data => x_msg_data,
702 p_encoded => 'F');
703
704 EXCEPTION
705
706 WHEN FND_API.G_EXC_ERROR THEN
707 ROLLBACK TO create_msite_resp_pvt;
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
710 p_data => x_msg_data,
711 p_encoded => 'F');
712
713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
714 ROLLBACK TO create_msite_resp_pvt;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
717 p_data => x_msg_data,
718 p_encoded => 'F');
719
720 WHEN OTHERS THEN
721 ROLLBACK TO create_msite_resp_pvt;
722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
723
724 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
725 THEN
726 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
727 END IF;
728
729 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
730 p_data => x_msg_data,
731 p_encoded => 'F');
732
733 END Create_Msite_Resp;
734
735 PROCEDURE Update_Msite_Resp
736 (
737 p_api_version IN NUMBER,
738 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
739 p_commit IN VARCHAR2 := FND_API.G_FALSE,
740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
741 p_msite_resp_id IN NUMBER := FND_API.G_MISS_NUM,
742 p_object_version_number IN NUMBER,
743 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
744 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
745 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
746 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
747 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
748 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
749 p_display_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
750 x_return_status OUT VARCHAR2,
751 x_msg_count OUT NUMBER,
752 x_msg_data OUT VARCHAR2
753 )
754 IS
755 l_api_name CONSTANT VARCHAR2(30) := 'Update_Msite_Resp';
756 l_api_version CONSTANT NUMBER := 1.0;
757
758 l_msite_resp_id NUMBER;
759
760 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
761 l_c_application_id IN NUMBER)
762 IS SELECT msite_resp_id FROM jtf_msite_resps_b
763 WHERE msite_id = l_c_msite_id
764 AND responsibility_id = l_c_responsibility_id
765 AND application_id = l_c_application_id;
766
767 BEGIN
768
769 -- Standard Start of API savepoint
770 SAVEPOINT update_msite_resp_pvt;
771
772 -- Standard call to check for call compatibility.
773 IF NOT FND_API.Compatible_API_Call(l_api_version,
774 p_api_version,
775 l_api_name,
776 G_PKG_NAME)
777 THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 END IF;
780
781 -- Initialize message list if p_init_msg_list is set to TRUE.
782 IF FND_API.to_Boolean(p_init_msg_list) THEN
783 FND_MSG_PUB.initialize;
784 END IF;
785
786 -- Initialize API return status to success
787 x_return_status := FND_API.G_RET_STS_SUCCESS;
788
789 -- API body
790 -- CALL FLOW :
791 -- 1. Check if everything is valid
792 -- 2. Update row
793
794 -- 1a. Check if either msite_resp_id or combination of
795 -- msite_id, responsibility_id and application_id is specified
796 IF ((p_msite_resp_id IS NOT NULL) AND
797 (p_msite_resp_id <> FND_API.G_MISS_NUM))
798 THEN
799 -- msite_resp_id specified, continue
800 l_msite_resp_id := p_msite_resp_id;
801 ELSIF ((p_msite_id IS NOT NULL) AND
802 (p_msite_id <> FND_API.G_MISS_NUM) AND
803 (p_responsibility_id IS NOT NULL) AND
804 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
805 (p_application_id IS NOT NULL) AND
806 (p_application_id <> FND_API.G_MISS_NUM))
807 THEN
808 -- If combination of msite_id, responsibility_id and application_id
809 -- is specified, then query for msite_resp_id
810
811 OPEN c1(p_msite_id, p_responsibility_id, p_application_id);
812 FETCH c1 INTO l_msite_resp_id;
813 IF (c1%NOTFOUND) THEN
814 CLOSE c1;
815 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITERESP_NOT_FOUND');
816 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
817 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
818 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
821 CLOSE c1;
822
823 ELSE
824 -- neither msite_resp_id nor combination of
825 -- msite_id, responsibility_id and application_id is specified
826 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_MR_IDS_SPEC');
827 FND_MSG_PUB.Add;
828 RAISE FND_API.G_EXC_ERROR;
829 END IF;
830
831 --
832 -- 1b. Validate the input data
833 --
834 Validate_Update
835 (
836 p_init_msg_list => FND_API.G_FALSE,
837 p_validation_level => p_validation_level,
838 p_msite_resp_id => l_msite_resp_id,
839 p_object_version_number => p_object_version_number,
840 p_start_date_active => p_start_date_active,
841 p_end_date_active => p_end_date_active,
842 p_sort_order => p_sort_order,
843 p_display_name => p_display_name,
844 x_return_status => x_return_status,
845 x_msg_count => x_msg_count,
846 x_msg_data => x_msg_data
847 );
848
849 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
850 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MR_INVALID_UPDATE');
851 FND_MSG_PUB.Add;
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
854 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MR_INVALID_UPDATE');
855 FND_MSG_PUB.Add;
856 RAISE FND_API.G_EXC_ERROR; -- invalid
857 END IF;
858
859 -- 2. update row with section data into section table
860 BEGIN
861 Jtf_Msite_Resp_Pkg.update_row
862 (
863 p_msite_resp_id => l_msite_resp_id,
864 p_object_version_number => p_object_version_number,
865 p_start_date_active => p_start_date_active,
866 p_end_date_active => p_end_date_active,
867 p_sort_order => p_sort_order,
868 p_security_group_id => FND_API.G_MISS_NUM,
869 p_display_name => p_display_name,
870 p_last_update_date => sysdate,
871 p_last_updated_by => FND_GLOBAL.user_id,
872 p_last_update_login => FND_GLOBAL.login_id
873 );
874
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_UPDATE_FL');
878 FND_MSG_PUB.ADD;
879 RAISE FND_API.G_EXC_ERROR;
880 WHEN OTHERS THEN
881 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_UPDATE_FL');
882 FND_MSG_PUB.ADD;
883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
884 END;
885
886 --
887 -- end of main api body.
888
889 -- standard check of p_commit.
890 IF (FND_API.to_boolean(p_commit)) THEN
891 COMMIT WORK;
892 END IF;
893
894 -- standard call to get message count and if count is 1, get message info.
895 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
896 p_data => x_msg_data,
897 p_encoded => 'F');
898
899 EXCEPTION
900
901 WHEN FND_API.G_EXC_ERROR THEN
902 ROLLBACK TO update_msite_resp_pvt;
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
905 p_data => x_msg_data,
906 p_encoded => 'F');
907
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 ROLLBACK TO update_msite_resp_pvt;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
912 p_data => x_msg_data,
913 p_encoded => 'F');
914
915 WHEN OTHERS THEN
916 ROLLBACK TO update_msite_resp_pvt;
917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
918
919 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
920 THEN
921 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
922 END IF;
923
924 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
925 p_data => x_msg_data,
926 p_encoded => 'F');
927
928 END Update_Msite_Resp;
929
930 PROCEDURE Delete_Msite_Resp
931 (
932 p_api_version IN NUMBER,
933 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
934 p_commit IN VARCHAR2 := FND_API.G_FALSE,
935 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
936 p_msite_resp_id IN NUMBER := FND_API.G_MISS_NUM,
937 p_msite_id IN NUMBER := FND_API.G_MISS_NUM,
938 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
939 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
940 x_return_status OUT VARCHAR2,
941 x_msg_count OUT NUMBER,
942 x_msg_data OUT VARCHAR2
943 )
944 IS
945 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Msite_Resp';
946 l_api_version CONSTANT NUMBER := 1.0;
947
948 l_msite_resp_id NUMBER;
949
950 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
951 l_c_application_id IN NUMBER)
952 IS SELECT msite_resp_id FROM jtf_msite_resps_b
953 WHERE msite_id = l_c_msite_id
954 AND responsibility_id = l_c_responsibility_id
955 AND application_id = l_c_application_id;
956
957 BEGIN
958
959 -- Standard Start of API savepoint
960 SAVEPOINT delete_msite_resp_pvt;
961
962 -- Standard call to check for call compatibility.
963 IF NOT FND_API.Compatible_API_Call(l_api_version,
964 p_api_version,
965 l_api_name,
966 G_PKG_NAME)
967 THEN
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END IF;
970
971 -- Initialize message list if p_init_msg_list is set to TRUE.
972 IF FND_API.to_Boolean(p_init_msg_list) THEN
973 FND_MSG_PUB.initialize;
974 END IF;
975
976 -- Initialize API return status to success
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978
979 -- CALL FLOW
980
981 -- 1a. Check if either msite_resp_id or combination of
982 -- msite_id, responsibility_id and application_id is specified
983 IF ((p_msite_resp_id IS NOT NULL) AND
984 (p_msite_resp_id <> FND_API.G_MISS_NUM))
985 THEN
986 -- msite_resp_id specified, continue
987 l_msite_resp_id := p_msite_resp_id;
988 ELSIF ((p_msite_id IS NOT NULL) AND
989 (p_msite_id <> FND_API.G_MISS_NUM) AND
990 (p_responsibility_id IS NOT NULL) AND
991 (p_responsibility_id <> FND_API.G_MISS_NUM) AND
992 (p_application_id IS NOT NULL) AND
993 (p_application_id <> FND_API.G_MISS_NUM))
994 THEN
995 -- If combination of msite_id, responsibility_id and application_id
996 -- is specified, then query for msite_resp_id
997
998 OPEN c1(p_msite_id, p_responsibility_id, p_application_id);
999 FETCH c1 INTO l_msite_resp_id;
1000 IF (c1%NOTFOUND) THEN
1001 CLOSE c1;
1002 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITERESP_NOT_FOUND');
1003 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
1004 FND_MESSAGE.Set_Token('RESP_ID', p_responsibility_id);
1005 FND_MESSAGE.Set_Token('APPL_ID', p_application_id);
1006 RAISE FND_API.G_EXC_ERROR;
1007 END IF;
1008 CLOSE c1;
1009
1010 ELSE
1011 -- neither msite_resp_id nor combination of
1012 -- msite_id, responsibility_id and application_id is specified
1013 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_MR_IDS_SPEC');
1014 FND_MSG_PUB.Add;
1015 RAISE FND_API.G_EXC_ERROR;
1016 END IF;
1017
1018 -- delete for jtf_msite_resps_b and _tl tables
1019 BEGIN
1020 Jtf_Msite_Resp_Pkg.delete_row(l_msite_resp_id);
1021 EXCEPTION
1022 WHEN NO_DATA_FOUND THEN
1023 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_DELETE_FL');
1024 FND_MSG_PUB.Add;
1025 RAISE FND_API.G_EXC_ERROR;
1026 WHEN OTHERS THEN
1027 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_MSITE_RESP_DELETE_FL');
1028 FND_MSG_PUB.Add;
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END;
1031
1032 EXCEPTION
1033
1034 WHEN FND_API.G_EXC_ERROR THEN
1035 ROLLBACK TO delete_msite_resp_pvt;
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1038 p_data => x_msg_data,
1039 p_encoded => 'F');
1040
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO delete_msite_resp_pvt;
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1045 p_data => x_msg_data,
1046 p_encoded => 'F');
1047
1048 WHEN OTHERS THEN
1049 ROLLBACK TO delete_msite_resp_pvt;
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051
1052 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1053 THEN
1054 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1055 END IF;
1056
1057 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1058 p_data => x_msg_data,
1059 p_encoded => 'F');
1060
1061 END Delete_Msite_Resp;
1062
1063 END Jtf_Msite_Resp_Pvt;