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