[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_RESP_MGR_PVT
Source
1 PACKAGE BODY Ibe_Msite_Resp_Mgr_Pvt AS
2 /* $Header: IBEVMRMB.pls 120.0 2005/05/30 02:44:37 appldev noship $ */
3
4 --
8 -- NAME
5 --
6 -- Start of Comments
7 --
9 -- Ibe_Msite_Resp_Mgr_Pvt
10 --
11 -- PURPOSE
12 --
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- 01/24/01 VPALAIYA Created
18 -- 04/06/01 SSRIDHAR Modified
19 -- the query criteria string in Get_Resp_Appl_Id_List should be
20 -- DESC and not DESCRIPTION, as the UI is passing DESC.
21
22 -- 12/12/02 SCHAK
23 -- Modified for NOCOPY (Bug # 2691704) and Debug (Bug # 2691710) Changes.
24
25 -- 12/19/02 SCHAK
26 -- Modified for reverting Debug (IBEUtil) Changes.
27 -- 05/05/03 JQU
28 -- Delete procedure Get_Resp_Appl_Id_List and
29 -- Get_Resp_Appl_Info_For_Lookup for performance bug 2935856
30 --
31 -- 07/28/03 JQU
32 -- Modified procedures for addition of group_code column
33 -- **************************************************************************
34
35 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_RESP_MGR_PVT';
36 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMRMB.pls';
37
38 --
39 -- Associate (p_responsibility_ids, p_application_ids) with p_msite_id.
40 -- x_is_any_duplicate_status will be FND_API.G_RET_STS_SUCCESS, if there is
41 -- no duplicate and will be FND_API.G_RET_STS_ERROR when there is at least 1
42 -- duplicate association attempted
43 --
44 PROCEDURE Associate_Resps_To_MSite
45 (
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_commit IN VARCHAR2 := FND_API.G_FALSE,
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 p_msite_id IN NUMBER,
51 p_responsibility_ids IN JTF_NUMBER_TABLE,
52 p_application_ids IN JTF_NUMBER_TABLE,
53 p_start_date_actives IN JTF_DATE_TABLE,
54 p_end_date_actives IN JTF_DATE_TABLE,
55 p_sort_orders IN JTF_NUMBER_TABLE,
56 p_display_names IN JTF_VARCHAR2_TABLE_300,
57 x_msite_resp_ids OUT NOCOPY JTF_NUMBER_TABLE,
58 x_duplicate_association_status OUT NOCOPY JTF_VARCHAR2_TABLE_100,
59 x_is_any_duplicate_status OUT NOCOPY VARCHAR2,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2
63 )
64 IS
65 l_api_name CONSTANT VARCHAR2(30) :=
66 'Associate_Resps_To_MSite';
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_tmp_id NUMBER;
69
70 CURSOR c1(l_c_msite_id IN NUMBER, l_c_responsibility_id IN NUMBER,
71 l_c_application_id IN NUMBER)
72 IS SELECT msite_resp_id FROM ibe_msite_resps_b
73 WHERE msite_id = l_c_msite_id
74 AND responsibility_id = l_c_responsibility_id
75 AND application_id = l_c_application_id;
76
77 BEGIN
78
79 -- Standard Start of API savepoint
80 SAVEPOINT Associate_Resps_To_Msite_Pvt;
81
82 -- Standard call to check for call compatibility.
83 IF NOT FND_API.Compatible_API_Call(l_api_version,
84 p_api_version,
85 l_api_name,
86 G_PKG_NAME)
87 THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 -- Initialize message list if p_init_msg_list is set to TRUE.
92 IF FND_API.to_Boolean(p_init_msg_list) THEN
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 -- Initialize API return status to success
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 -- Check if the association already exists. Populate the
100 -- x_duplicate_association_status with the appropriate information
101 x_duplicate_association_status := JTF_VARCHAR2_TABLE_100();
102 x_msite_resp_ids := JTF_NUMBER_TABLE();
103 x_is_any_duplicate_status := FND_API.G_RET_STS_SUCCESS;
104
105 FOR i IN 1..p_responsibility_ids.COUNT LOOP
106
107 x_duplicate_association_status.EXTEND();
108 x_msite_resp_ids.EXTEND();
109
110 OPEN c1(p_msite_id, p_responsibility_ids(i), p_application_ids(i));
111 FETCH c1 INTO l_tmp_id;
112 IF(c1%FOUND) THEN
113 CLOSE c1;
114 -- duplicate exists
115 x_duplicate_association_status(i) := FND_API.G_RET_STS_ERROR;
116 x_is_any_duplicate_status := FND_API.G_RET_STS_ERROR;
117 x_msite_resp_ids(i) := l_tmp_id;
118 ELSE
119 CLOSE c1;
120 -- no duplicate exists, create new entry
121 x_duplicate_association_status(i) := FND_API.G_RET_STS_SUCCESS;
122
123 Ibe_Msite_Resp_Pvt.Create_Msite_Resp
124 (
125 p_api_version => p_api_version,
126 p_init_msg_list => FND_API.G_FALSE,
127 p_commit => FND_API.G_FALSE,
128 p_validation_level => p_validation_level,
129 p_msite_resp_id => FND_API.G_MISS_NUM,
130 p_msite_id => p_msite_id,
131 p_responsibility_id => p_responsibility_ids(i),
132 p_application_id => p_application_ids(i),
133 p_start_date_active => p_start_date_actives(i),
134 p_end_date_active => p_end_date_actives(i),
135 p_sort_order => p_sort_orders(i),
136 p_display_name => p_display_names(i),
137 x_msite_resp_id => x_msite_resp_ids(i),
138 x_return_status => x_return_status,
139 x_msg_count => x_msg_count,
140 x_msg_data => x_msg_data
141 );
142
143 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
144 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_RESP_FL');
145 FND_MSG_PUB.Add;
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
148 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_RESP_FL');
149 FND_MSG_PUB.Add;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 END IF;
154
155 END LOOP; -- end for i
156
157 --
158 -- End of main API body.
159
160 -- Standard check of p_commit.
161 IF (FND_API.To_Boolean(p_commit)) THEN
162 COMMIT WORK;
163 END IF;
164
165 -- Standard call to get message count and if count is 1, get message info.
166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
167 p_data => x_msg_data,
168 p_encoded => 'F');
169
170 EXCEPTION
171
172 WHEN FND_API.G_EXC_ERROR THEN
173 ROLLBACK TO Associate_Resps_To_Msite_Pvt;
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
176 p_data => x_msg_data,
177 p_encoded => 'F');
178
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 ROLLBACK TO Associate_Resps_To_Msite_Pvt;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
183 p_data => x_msg_data,
184 p_encoded => 'F');
185
186 WHEN OTHERS THEN
187 ROLLBACK TO Associate_Resps_To_Msite_Pvt;
188 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
189 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
190 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
191 FND_MESSAGE.Set_Token('REASON', SQLERRM);
192 FND_MSG_PUB.Add;
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194
195 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
196 THEN
197 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
198 END IF;
199
200 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
201 p_data => x_msg_data,
202 p_encoded => 'F');
203
204 END Associate_Resps_To_MSite;
205
206 --
207 -- to update and delete multiple entries.
208 --
209 PROCEDURE Update_Delete_Msite_Resps
210 (
211 p_api_version IN NUMBER,
212 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 p_msite_resp_ids IN JTF_NUMBER_TABLE,
216 p_object_version_numbers IN JTF_NUMBER_TABLE,
217 p_start_date_actives IN JTF_DATE_TABLE,
218 p_end_date_actives IN JTF_DATE_TABLE,
219 p_sort_orders IN JTF_NUMBER_TABLE,
220 p_display_names IN JTF_VARCHAR2_TABLE_300,
221 p_delete_flags IN JTF_VARCHAR2_TABLE_100,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2
225 )
226 IS
227 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Msite_Resps';
228 l_api_version CONSTANT NUMBER := 1.0;
229
230 BEGIN
231
232 -- Standard Start of API savepoint
233 SAVEPOINT Update_Delete_Msite_Resps_Pvt;
234
235 -- Standard call to check for call compatibility.
236 IF NOT FND_API.Compatible_API_Call(l_api_version,
237 p_api_version,
238 l_api_name,
239 G_PKG_NAME)
240 THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 -- Initialize message list if p_init_msg_list is set to TRUE.
245 IF FND_API.to_Boolean(p_init_msg_list) THEN
246 FND_MSG_PUB.initialize;
247 END IF;
248
249 -- Initialize API return status to success
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 -- API body
253 -- CALL FLOW :
254 -- 1.
255
256 FOR i IN 1..p_msite_resp_ids.COUNT LOOP
257
258 IF (p_delete_flags(i) = 'Y') THEN
259
260 Ibe_Msite_Resp_Pvt.Delete_Msite_Resp
261 (
262 p_api_version => p_api_version,
263 p_init_msg_list => FND_API.G_FALSE,
264 p_commit => FND_API.G_FALSE,
265 p_validation_level => p_validation_level,
266 p_msite_resp_id => p_msite_resp_ids(i),
267 p_msite_id => FND_API.G_MISS_NUM,
268 p_responsibility_id => FND_API.G_MISS_NUM,
269 p_application_id => FND_API.G_MISS_NUM,
270 x_return_status => x_return_status,
271 x_msg_count => x_msg_count,
272 x_msg_data => x_msg_data
273 );
274
275 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
276 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
277 FND_MSG_PUB.Add;
278 RAISE FND_API.G_EXC_ERROR;
279 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
280 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
281 FND_MSG_PUB.Add;
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END IF;
284
285 ELSE
286
287 Ibe_Msite_Resp_Pvt.Update_Msite_Resp
288 (
289 p_api_version => p_api_version,
290 p_init_msg_list => FND_API.G_FALSE,
291 p_commit => FND_API.G_FALSE,
292 p_validation_level => p_validation_level,
293 p_msite_resp_id => p_msite_resp_ids(i),
294 p_object_version_number => p_object_version_numbers(i),
295 p_msite_id => FND_API.G_MISS_NUM,
296 p_responsibility_id => FND_API.G_MISS_NUM,
297 p_application_id => FND_API.G_MISS_NUM,
298 p_start_date_active => p_start_date_actives(i),
299 p_end_date_active => p_end_date_actives(i),
300 p_sort_order => p_sort_orders(i),
301 p_display_name => p_display_names(i),
302 p_group_code => FND_API.G_MISS_CHAR,
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_ERROR) THEN
309 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
310 FND_MSG_PUB.Add;
311 RAISE FND_API.G_EXC_ERROR;
312 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
313 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
314 FND_MSG_PUB.Add;
315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
316 END IF;
317
318 END IF;
319
320 END LOOP; -- end for i
321
322 --
323 -- End of main API body.
324
325 -- Standard check of p_commit.
326 IF (FND_API.To_Boolean(p_commit)) THEN
327 COMMIT WORK;
328 END IF;
329
330 -- Standard call to get message count and if count is 1, get message info.
331 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
332 p_data => x_msg_data,
333 p_encoded => 'F');
334
335 EXCEPTION
336
337 WHEN FND_API.G_EXC_ERROR THEN
338 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
341 p_data => x_msg_data,
342 p_encoded => 'F');
343
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
348 p_data => x_msg_data,
349 p_encoded => 'F');
350
351 WHEN OTHERS THEN
352 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354
355 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
356 THEN
357 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
358 END IF;
359
360 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
361 p_data => x_msg_data,
362 p_encoded => 'F');
363
364 END Update_Delete_Msite_Resps;
365
366 --
367 -- to update and delete multiple entries.
368 --
369 PROCEDURE Update_Delete_Msite_Resps
370 (
371 p_api_version IN NUMBER,
372 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
373 p_commit IN VARCHAR2 := FND_API.G_FALSE,
374 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
375 p_msite_resp_ids IN JTF_NUMBER_TABLE,
376 p_msite_ids IN JTF_NUMBER_TABLE,
377 p_responsibility_ids IN JTF_NUMBER_TABLE,
378 p_application_ids IN JTF_NUMBER_TABLE,
379 p_object_version_numbers IN JTF_NUMBER_TABLE,
380 p_start_date_actives IN JTF_DATE_TABLE,
381 p_end_date_actives IN JTF_DATE_TABLE,
382 p_sort_orders IN JTF_NUMBER_TABLE,
383 p_display_names IN JTF_VARCHAR2_TABLE_300,
384 p_group_codes IN JTF_VARCHAR2_TABLE_300,
385 p_delete_flags IN JTF_VARCHAR2_TABLE_100,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2
389 )
390 IS
391 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Msite_Resps';
392 l_api_version CONSTANT NUMBER := 1.0;
393 l_msite_resp_id NUMBER;
394 l_group_code VARCHAR2(80);
395 l_msite_resp_ids JTF_NUMBER_TABLE;
396
397 Cursor c_msite_resp_group (l_msite_resp_id Number)
398 Is Select group_code
399 From ibe_msite_resps_b
400 where msite_resp_id = l_msite_resp_id;
401
402 BEGIN
403
404 l_msite_resp_ids := JTF_NUMBER_TABLE();
405
406 -- Standard Start of API savepoint
407 SAVEPOINT Update_Delete_Msite_Resps_Pvt;
408
409 -- Standard call to check for call compatibility.
410 IF NOT FND_API.Compatible_API_Call(l_api_version,
411 p_api_version,
412 l_api_name,
413 G_PKG_NAME)
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
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 -- API body
427 -- CALL FLOW :
428 -- 1.
429
430 FOR i IN 1..p_msite_resp_ids.COUNT LOOP
431 l_msite_resp_ids.EXTEND();
432
433 IF (p_delete_flags(i) = 'Y') THEN
434
435 Ibe_Msite_Resp_Pvt.Delete_Msite_Resp
436 (
437 p_api_version => p_api_version,
438 p_init_msg_list => FND_API.G_FALSE,
439 p_commit => FND_API.G_FALSE,
440 p_validation_level => p_validation_level,
441 p_msite_resp_id => p_msite_resp_ids(i),
442 p_msite_id => FND_API.G_MISS_NUM,
443 p_responsibility_id => FND_API.G_MISS_NUM,
444 p_application_id => FND_API.G_MISS_NUM,
445 x_return_status => x_return_status,
446 x_msg_count => x_msg_count,
447 x_msg_data => x_msg_data
448 );
449
450 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
451 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
452 FND_MSG_PUB.Add;
453 RAISE FND_API.G_EXC_ERROR;
454 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
455 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
456 FND_MSG_PUB.Add;
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459
460 ELSIF (p_delete_flags(i) = 'IG') THEN
461
462 Open C_msite_resp_group(p_msite_resp_ids(i));
463 Fetch C_msite_resp_group INTO l_group_code;
464 CLOSE c_msite_resp_group;
465
466 IF (l_group_code is null) THEN
467
468 Ibe_Msite_Resp_Pvt.Update_Msite_Resp
469 (
470 p_api_version => p_api_version,
471 p_init_msg_list => FND_API.G_FALSE,
472 p_commit => FND_API.G_FALSE,
473 p_validation_level => p_validation_level,
474 p_msite_resp_id => p_msite_resp_ids(i),
475 p_object_version_number => p_object_version_numbers(i),
476 p_msite_id => FND_API.G_MISS_NUM,
477 p_responsibility_id => FND_API.G_MISS_NUM,
478 p_application_id => FND_API.G_MISS_NUM,
479 p_start_date_active => p_start_date_actives(i),
480 p_end_date_active => p_end_date_actives(i),
481 p_sort_order => p_sort_orders(i),
482 p_display_name => p_display_names(i),
483 p_group_code => p_group_codes(i),
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data
487 );
488
489 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
490 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
491 FND_MSG_PUB.Add;
492 RAISE FND_API.G_EXC_ERROR;
493 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
494 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
495 FND_MSG_PUB.Add;
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498
499 ELSE
500
501 Ibe_Msite_Resp_Pvt.Create_Msite_Resp
502 (
503 p_api_version => p_api_version,
504 p_init_msg_list => FND_API.G_FALSE,
505 p_commit => FND_API.G_FALSE,
506 p_validation_level => p_validation_level,
507 p_msite_resp_id => p_msite_resp_ids(i),
508 p_msite_id => p_msite_ids(i),
509 p_responsibility_id => p_responsibility_ids(i),
510 p_application_id => p_application_ids(i),
511 p_start_date_active => p_start_date_actives(i),
512 p_end_date_active => p_end_date_actives(i),
513 p_sort_order => p_sort_orders(i),
514 p_display_name => p_display_names(i),
515 p_group_code => p_group_codes(i),
516 x_msite_resp_id => l_msite_resp_ids(i),
517 x_return_status => x_return_status,
518 x_msg_count => x_msg_count,
519 x_msg_data => x_msg_data
520 );
521
522 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
523 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_RESP_FL');
524 FND_MSG_PUB.Add;
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
527 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_RESP_FL');
528 FND_MSG_PUB.Add;
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531
532 END IF;
533
534 ELSIF (p_delete_flags(i) = 'DG') THEN
535
536 Ibe_Msite_Resp_Pvt.Delete_Msite_Resp_Group
537 (
538 p_api_version => p_api_version,
539 p_init_msg_list => FND_API.G_FALSE,
540 p_commit => FND_API.G_FALSE,
541 p_validation_level => p_validation_level,
542 p_msite_resp_id => p_msite_resp_ids(i),
543 p_msite_id => FND_API.G_MISS_NUM,
544 p_responsibility_id => FND_API.G_MISS_NUM,
545 p_application_id => FND_API.G_MISS_NUM,
546 p_group_code => p_group_codes(i),
547 x_return_status => x_return_status,
548 x_msg_count => x_msg_count,
549 x_msg_data => x_msg_data
550 );
551
552 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
553 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
554 FND_MSG_PUB.Add;
555 RAISE FND_API.G_EXC_ERROR;
556 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
557 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
558 FND_MSG_PUB.Add;
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561
562 ELSIF (p_delete_flags(i) = 'N') THEN
563 NULL;
564 END IF;
565 END LOOP; -- end for i
566
567 --
568 -- End of main API body.
569
570 -- Standard check of p_commit.
571 IF (FND_API.To_Boolean(p_commit)) THEN
572 COMMIT WORK;
573 END IF;
574
575 -- Standard call to get message count and if count is 1, get message info.
576 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
577 p_data => x_msg_data,
578 p_encoded => 'F');
579
580 EXCEPTION
581
582 WHEN FND_API.G_EXC_ERROR THEN
583 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
584 x_return_status := FND_API.G_RET_STS_ERROR;
585 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
586 p_data => x_msg_data,
587 p_encoded => 'F');
588
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
593 p_data => x_msg_data,
594 p_encoded => 'F');
595
596 WHEN OTHERS THEN
597 ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
599
600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
601 THEN
602 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
603 END IF;
604
605 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
606 p_data => x_msg_data,
607 p_encoded => 'F');
608
609 END Update_Delete_Msite_Resps;
610
611
612 --
613 -- Return data (association + minisite data + responsibility data) belonging to
614 -- the p_msite_id and to a particular p_application_id. If p_application_id is
615 -- -1, NULL, or FND_API.G_MISS_NUM, then load for all applications
616 --
617 PROCEDURE Load_MsiteResps_For_Msite
618 (
619 p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
621 p_commit IN VARCHAR2 := FND_API.G_FALSE,
622 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
623 p_msite_id IN NUMBER,
624 p_application_id IN NUMBER,
625 x_msite_csr OUT NOCOPY MSITE_CSR,
626 x_msite_resp_csr OUT NOCOPY MSITE_RESP_CSR,
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2
630 )
631 IS
632 l_api_name CONSTANT VARCHAR2(30) :=
633 'Load_MsiteResps_For_Msite';
634 l_api_version CONSTANT NUMBER := 1.0;
635 BEGIN
636
637 -- Initialize message list if p_init_msg_list is set to TRUE.
638 IF FND_API.to_Boolean(p_init_msg_list) THEN
639 FND_MSG_PUB.initialize;
640 END IF;
641
642 -- Initialize API return status to success
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645 -- Get the mini-site data
646 OPEN x_msite_csr FOR SELECT msite_name, msite_description
647 FROM ibe_msites_vl
648 WHERE msite_id = p_msite_id and site_type = 'I';
649
653 p_application_id = fnd_api.g_miss_num)
650 -- Get the msite-resp data and resp data
651 IF (p_application_id = -1 OR
652 p_application_id IS NULL OR
654 THEN
655
656 OPEN x_msite_resp_csr FOR SELECT MR.msite_resp_id,
657 MR.object_version_number, R.responsibility_id, R.application_id,
658 MR.display_name, A.application_name,
659 R.responsibility_key, R.responsibility_name, MR.start_date_active,
660 MR.end_date_active, MR.sort_order
661 FROM ibe_msite_resps_vl MR, fnd_responsibility_vl R, fnd_application_vl A
662 WHERE MR.msite_id = p_msite_id
663 AND MR.responsibility_id = R.responsibility_id
664 AND MR.application_id = R.application_id
665 AND R.application_id = A.application_id;
666
667 ELSE
668
669 OPEN x_msite_resp_csr FOR SELECT MR.msite_resp_id,
670 MR.object_version_number, R.responsibility_id, R.application_id,
671 MR.display_name, A.application_name,
672 R.responsibility_key, R.responsibility_name, MR.start_date_active,
673 MR.end_date_active, MR.sort_order
674 FROM ibe_msite_resps_vl MR, fnd_responsibility_vl R, fnd_application_vl A
675 WHERE MR.msite_id = p_msite_id
676 AND MR.application_id = p_application_id
677 AND MR.responsibility_id = R.responsibility_id
678 AND MR.application_id = R.application_id
679 AND R.application_id = A.application_id;
680
681 END IF;
682
683 EXCEPTION
684
685 WHEN FND_API.G_EXC_ERROR THEN
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
688 p_data => x_msg_data,
689 p_encoded => 'F');
690
691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
694 p_data => x_msg_data,
695 p_encoded => 'F');
696
697 WHEN OTHERS THEN
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699
700 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 THEN
702 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
703 END IF;
704
705 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => 'F');
708
709 END Load_MsiteResps_For_Msite;
710
711
712 --
713 -- to sort multiple entries.
714 --
715 PROCEDURE Update_Msite_Resps
716 (
717 p_api_version IN NUMBER,
718 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
719 p_commit IN VARCHAR2 := FND_API.G_FALSE,
720 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
721 p_msite_resp_ids IN JTF_NUMBER_TABLE,
722 p_object_version_numbers IN JTF_NUMBER_TABLE,
723 p_sort_orders IN JTF_NUMBER_TABLE,
724 x_return_status OUT NOCOPY VARCHAR2,
725 x_msg_count OUT NOCOPY NUMBER,
726 x_msg_data OUT NOCOPY VARCHAR2
727 )
728 IS
729 l_api_name CONSTANT VARCHAR2(30) := 'Update_Msite_Resps';
730 l_api_version CONSTANT NUMBER := 1.0;
731
732
733 BEGIN
734
735 -- Standard Start of API savepoint
736 SAVEPOINT Update_Msite_Resps_Pvt;
737
738 -- Standard call to check for call compatibility.
739 IF NOT FND_API.Compatible_API_Call(l_api_version,
740 p_api_version,
741 l_api_name,
742 G_PKG_NAME)
743 THEN
744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745 END IF;
746
747 -- Initialize message list if p_init_msg_list is set to TRUE.
748 IF FND_API.to_Boolean(p_init_msg_list) THEN
749 FND_MSG_PUB.initialize;
750 END IF;
751
752 -- Initialize API return status to success
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754
755 -- API body
756 -- CALL FLOW :
757 -- 1.
758
759 FOR i IN 1..p_msite_resp_ids.COUNT LOOP
760
761
762 Ibe_Msite_Resp_Pvt.Update_Msite_Resp
763 (
764 p_api_version => p_api_version,
765 p_init_msg_list => FND_API.G_FALSE,
766 p_commit => FND_API.G_FALSE,
767 p_validation_level => p_validation_level,
768 p_msite_resp_id => p_msite_resp_ids(i),
769 p_object_version_number => p_object_version_numbers(i),
770 p_msite_id => FND_API.G_MISS_NUM,
771 p_responsibility_id => FND_API.G_MISS_NUM,
772 p_application_id => FND_API.G_MISS_NUM,
773 p_start_date_active => FND_API.G_MISS_DATE,
774 p_end_date_active => FND_API.G_MISS_DATE,
775 p_sort_order => p_sort_orders(i),
776 p_display_name => FND_API.G_MISS_CHAR,
777 p_group_code => FND_API.G_MISS_CHAR,
778 x_return_status => x_return_status,
779 x_msg_count => x_msg_count,
780 x_msg_data => x_msg_data
781 );
782
783
784 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
785 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
786 FND_MSG_PUB.Add;
787 RAISE FND_API.G_EXC_ERROR;
788 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
789 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_RESP_FL');
790 FND_MSG_PUB.Add;
791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
792 END IF;
793
794
795 END LOOP; -- end for i
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_Delete_Msite_Resps_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_Delete_Msite_Resps_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_Delete_Msite_Resps_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_Resps;
840
841
842 END Ibe_Msite_Resp_Mgr_Pvt;