[Home] [Help]
PACKAGE BODY: APPS.IBE_DELIVERABLE_GRP
Source
1 PACKAGE BODY IBE_Deliverable_GRP AS
2 /* $Header: IBEGDLVB.pls 120.0 2005/05/30 03:31:26 appldev noship $ */
3
4 /*
5 ===================================================================
6 -- Copyright (c) 1999 Oracle Corporation --
7 -- Redwood Shores, California, USA --
8 -- All rights reserved. --
9 ------------------------------------------------------------------
10
11
12 -----------------------------------------------------------
13 -- PACKAGE
14 -- IBE_Deliverable_GRP
15 --
16 -- PROCEDURES
17 -- save_deliverable
18 -- delete_deliverable
19 -- list_deliverable
20 -- HISTORY
21 -- 11/27/99 wxyu Created
22 -- 05/17/01 G. Zhang Modified to support DB Media
23 -- 05/13/02 YAXU modified to support updateing the item_type
24 -- 11/14/02 abhandar modified to support updateing the applicable_to
25
26 -- 12/31/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
27 -- 03/01/03 SCHAK Modified for NOCOPY changes.
28
29 ================================================================================
30 */
31
32 g_amv_api_version CONSTANT NUMBER := 1.0;
33
34 TYPE DlvCurTyp IS REF CURSOR;
35
36 PROCEDURE list_deliverable (
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2,
42 p_item_type IN VARCHAR2,
43 p_item_applicable_to IN VARCHAR2,
44 p_search_type IN VARCHAR2,
45 p_search_value IN VARCHAR2,
46 p_start_id IN NUMBER,
47 p_batch_size IN NUMBER,
48 x_row_count OUT NOCOPY NUMBER,
49 x_dlv_id_tbl OUT NOCOPY NUMBER_TABLE,
50 x_acc_name_tbl OUT NOCOPY VARCHAR2_TABLE_100,
51 x_dsp_name_tbl OUT NOCOPY VARCHAR2_TABLE_300,
52 x_item_type_tbl OUT NOCOPY VARCHAR2_TABLE_100,
53 x_appl_to_tbl OUT NOCOPY VARCHAR2_TABLE_100,
54 x_keyword_tbl OUT NOCOPY VARCHAR2_TABLE_300,
55 x_desc_tbl OUT NOCOPY VARCHAR2_TABLE_2000,
56 x_version_tbl OUT NOCOPY NUMBER_TABLE,
57 x_file_name_tbl OUT NOCOPY VARCHAR2_TABLE_300,
58
59 --added by G. Zhang 05/17/01 5:42PM
60 x_file_id_tbl OUT NOCOPY NUMBER_TABLE ) IS
61
62 BEGIN
63
64 list_deliverable(
65 p_api_version,
66 p_init_msg_list,
67 x_return_status,
68 x_msg_count,
69 x_msg_data,
70 NULL,
71 p_item_type,
72 p_item_applicable_to,
73 p_search_type,
74 p_search_value,
75 p_start_id,
76 p_batch_size,
77 x_row_count,
78 x_dlv_id_tbl,
79 x_acc_name_tbl,
80 x_dsp_name_tbl,
81 x_item_type_tbl,
82 x_appl_to_tbl,
83 x_keyword_tbl,
84 x_desc_tbl,
85 x_version_tbl,
86 x_file_name_tbl,
87
88 --added by G. Zhang 05/17/01 5:42PM
89 x_file_id_tbl
90 );
91
92 END list_deliverable;
93
94
95 PROCEDURE list_deliverable (
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2,
101 p_category_id IN NUMBER,
102 p_item_type IN VARCHAR2,
103 p_item_applicable_to IN VARCHAR2,
104 p_search_type IN VARCHAR2,
105 p_search_value IN VARCHAR2,
106 p_start_id IN NUMBER,
107 p_batch_size IN NUMBER,
108 x_row_count OUT NOCOPY NUMBER,
109 x_dlv_id_tbl OUT NOCOPY NUMBER_TABLE,
110 x_acc_name_tbl OUT NOCOPY VARCHAR2_TABLE_100,
111 x_dsp_name_tbl OUT NOCOPY VARCHAR2_TABLE_300,
112 x_item_type_tbl OUT NOCOPY VARCHAR2_TABLE_100,
113 x_appl_to_tbl OUT NOCOPY VARCHAR2_TABLE_100,
114 x_keyword_tbl OUT NOCOPY VARCHAR2_TABLE_300,
115 x_desc_tbl OUT NOCOPY VARCHAR2_TABLE_2000,
116 x_version_tbl OUT NOCOPY NUMBER_TABLE,
117 x_file_name_tbl OUT NOCOPY VARCHAR2_TABLE_300,
118
119 --added by G. Zhang 05/17/01 5:42PM
120 x_file_id_tbl OUT NOCOPY NUMBER_TABLE ) IS
121
122 l_api_name CONSTANT VARCHAR2(30) := 'list_deliverable';
123 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
124
125 l_return_status VARCHAR2(1);
126
127 l_dlv_cv DlvCurTyp;
128 l_flag NUMBER := 0;
129 l_search_value VARCHAR2(240);
130 sql_cond VARCHAR2(480) := 'FROM ibe_dsp_amv_items_v WHERE application_id = :appl_id ';
131 sql_stmt VARCHAR2(960);
132
133 start_pnt NUMBER;
134 end_pnt NUMBER;
135
136 l_dlv_id_tbl JTF_NUMBER_TABLE;
137 l_acc_name_tbl JTF_VARCHAR2_TABLE_100;
138 l_dsp_name_tbl JTF_VARCHAR2_TABLE_300;
139 l_item_type_tbl JTF_VARCHAR2_TABLE_100;
140 l_appl_to_tbl JTF_VARCHAR2_TABLE_100;
141 l_keyword_tbl JTF_VARCHAR2_TABLE_300;
142 l_desc_tbl JTF_VARCHAR2_TABLE_2000;
143 l_version_tbl JTF_NUMBER_TABLE;
144 l_file_name_tbl JTF_VARCHAR2_TABLE_300;
145
146 --added by G. Zhang 05/17/01 5:42PM
147 l_file_id_tbl JTF_NUMBER_TABLE;
148
149 l_index NUMBER;
150 l_count NUMBER;
151
152 l_appl_id NUMBER;
153
154 l_item_applicable_to VARCHAR2(30) := p_item_applicable_to;
155 BEGIN
156
157 -- Standard call to check for call compatibility
158 IF NOT FND_API.compatible_api_call(
159 g_api_version,
160 p_api_version,
161 l_api_name,
162 g_pkg_name
163 ) THEN
164 RAISE FND_API.g_exc_unexpected_error;
165 END IF;
166
167 -- Initialize message list if p_init_msg_list is set to TRUE
168 IF FND_API.to_boolean(p_init_msg_list) THEN
169 FND_MSG_PUB.initialize;
170 END IF;
171
172 -- Initialize API rturn status to success
173 x_return_status := FND_API.g_ret_sts_success;
174
175 -- API body
176
177 IF p_start_id < -1 OR p_batch_size < 0
178 OR (p_start_id = -1 AND p_batch_size = 0) THEN
179 FND_MESSAGE.set_name('IBE', 'IBE_DSP_QUERY_INVLD');
180 FND_MESSAGE.set_token('0', TO_CHAR(p_start_id));
181 FND_MESSAGE.set_token('1', TO_CHAR(p_batch_size));
182 FND_MSG_PUB.add;
183 RAISE FND_API.g_exc_unexpected_error;
184 END IF;
185
186 IF p_category_id IS NOT NULL
187 AND TRIM(p_item_applicable_to) <> 'CATEGORY' THEN
188 FND_MESSAGE.set_name('IBE', 'IBE_DSP_AVAIL_INVLD');
189 FND_MESSAGE.set_token('0', TO_CHAR(p_category_id));
190 FND_MESSAGE.set_token('1', p_item_applicable_to);
191 FND_MSG_PUB.add;
192 RAISE FND_API.g_exc_unexpected_error;
193 END IF;
194
195 IF TRIM(p_item_type) IS NOT NULL THEN
196 l_flag := 1;
197 sql_cond := sql_cond || 'AND deliverable_type_code = :item_type ';
198 END IF;
199
200 IF TRIM(p_item_applicable_to) IS NOT NULL THEN
201 l_flag := l_flag + 10;
202 sql_cond := sql_cond || 'AND applicable_to_code = :appl_to ';
203 END IF;
204
205 IF TRIM(p_search_type) IS NOT NULL AND TRIM(p_search_value) IS NOT NULL THEN
206 l_flag := l_flag + 100;
207 l_search_value := '%' || LOWER(TRIM(p_search_value)) || '%';
208 sql_cond := sql_cond || 'AND LOWER(' || p_search_type || ') LIKE :value ';
209 END IF;
210
211 IF p_category_id IS NOT NULL THEN
212 l_flag := l_flag + 1000;
213 sql_cond := sql_cond
214 || 'AND item_id NOT IN (SELECT item_id ' ||
215 ' FROM ibe_dsp_tpl_ctg '
216 || 'WHERE category_id = :ctg_id) ';
217 END IF;
218
219 -- dbms_output.put_line('resp_appl_id=' || FND_GLOBAL.resp_appl_id);
220
221 -- fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, 671);
222 -- l_appl_id := FND_GLOBAL.resp_appl_id;
223 l_appl_id := 671;
224
225 -- Get Total Row Number
226 sql_stmt := 'SELECT COUNT(*) ' || sql_cond;
227 IF l_flag = 1111 THEN
228 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
229 p_item_type, p_item_applicable_to, l_search_value, p_category_id;
230 ELSIF l_flag = 1110 THEN
231 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
232 p_item_applicable_to, l_search_value, p_category_id;
233 ELSIF l_flag = 1101 THEN
234 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
235 p_item_type, l_search_value, p_category_id;
236 ELSIF l_flag = 1100 THEN
237 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
238 l_search_value, p_category_id;
239 ELSIF l_flag = 1011 THEN
240 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
241 p_item_type, p_item_applicable_to, p_category_id;
242 ELSIF l_flag = 1010 THEN
243 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
244 p_item_applicable_to, p_category_id;
245 ELSIF l_flag = 1001 THEN
246 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
247 p_item_type, p_category_id;
248 ELSIF l_flag = 1000 THEN
249 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
250 p_category_id;
251 ELSIF l_flag = 111 THEN
252 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
253 p_item_type, p_item_applicable_to, l_search_value;
254 ELSIF l_flag = 110 THEN
255 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
256 p_item_applicable_to, l_search_value;
257 ELSIF l_flag = 101 THEN
258 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
259 p_item_type, l_search_value;
260 ELSIF l_flag = 100 THEN
261 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
262 l_search_value;
263 ELSIF l_flag = 11 THEN
264 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
265 p_item_type, p_item_applicable_to;
266 ELSIF l_flag = 10 THEN
267 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
268 p_item_applicable_to;
269 ELSIF l_flag = 1 THEN
270 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id,
271 p_item_type;
272 ELSE
273 OPEN l_dlv_cv FOR sql_stmt USING l_appl_id;
274 END IF;
275
276 FETCH l_dlv_cv INTO x_row_count;
277 CLOSE l_dlv_cv;
278
279 IF x_row_count = 0 THEN
280 FND_MSG_PUB.count_and_get(
281 p_encoded => FND_API.g_false,
282 p_count => x_msg_count,
283 p_data => x_msg_data
284 );
285 RETURN;
286 END IF;
287
288 x_dlv_id_tbl := NULL;
289 x_acc_name_tbl := NULL;
290 x_dsp_name_tbl := NULL;
291 x_item_type_tbl := NULL;
292 x_appl_to_tbl := NULL;
293 x_desc_tbl := NULL;
294 x_keyword_tbl := NULL;
295 x_file_name_tbl := NULL;
296
297 --added by G. Zhang 05/17/01 5:42PM
298 x_file_id_tbl :=NULL;
299
300 x_version_tbl := NULL;
301
302 -- Get matched rows
303 IF p_start_id > -1 THEN
304 IF p_start_id >= x_row_count THEN
305 FND_MSG_PUB.count_and_get(
306 p_encoded => FND_API.g_false,
307 p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310 RETURN;
311 END IF;
312
313 start_pnt := p_start_id + 1;
314 IF p_batch_size > 0 THEN
315 end_pnt := p_start_id + p_batch_size;
316 ELSE
317 end_pnt := x_row_count;
318 END IF;
319 ELSE
320 end_pnt := x_row_count;
321 start_pnt := end_pnt - p_batch_size + 1;
322 IF start_pnt < 1 THEN
323 start_pnt := 1;
324 END IF;
325 END IF;
326
327 sql_stmt := 'BEGIN '
328 || 'SELECT item_id, access_name, item_name, deliverable_type_code, '
329
330 --modified by G. Zhang 05/17/01 5:42PM
331 || 'applicable_to_code, description, keyword, file_name, file_id, '
332
333 || 'object_version_number '
334 || 'BULK COLLECT INTO :id_tbl, :acc_tbl, :dsp_tbl, :type_tbl, '
335
336 --modified by G. Zhang 05/17/01 5:42PM
337 || ':appl_tbl, :desc_tbl, :key_tbl, :file_tbl, :file_id_tbl, :version_tbl '
338
339 || 'FROM (SELECT * '
340 || sql_cond
341 || 'ORDER BY item_name ) '
342 || 'WHERE ROWNUM <= :row_num '
343 || '; END;';
344
345 -- dbms_output.put_line('sql_cond=' || sql_cond);
346 -- dbms_output.put_line('row_num=' || end_pnt);
347 -- dbms_output.put_line('sql_stmt=' || sql_stmt);
348
349 IF l_flag = 1111 THEN
350 EXECUTE IMMEDIATE sql_stmt USING
351 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
352 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
353
354 --modified by G. Zhang 05/17/01 5:42PM
355 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
356
357 l_appl_id,
358 p_item_type, p_item_applicable_to, l_search_value,
359 p_category_id,
360 end_pnt;
361 ELSIF l_flag = 1110 THEN
362 EXECUTE IMMEDIATE sql_stmt USING
363 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
364 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
365
366 --modified by G. Zhang 05/17/01 5:42PM
367 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
368
369 l_appl_id,
370 p_item_applicable_to, l_search_value,
371 p_category_id,
372 end_pnt;
373 ELSIF l_flag = 1101 THEN
374 EXECUTE IMMEDIATE sql_stmt USING
375 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
376 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
377
378 --modified by G. Zhang 05/17/01 5:42PM
379 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
380
381 l_appl_id,
382 p_item_type, l_search_value,
383 p_category_id,
384 end_pnt;
385 ELSIF l_flag = 1100 THEN
386 EXECUTE IMMEDIATE sql_stmt USING
387 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
388 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
389
390 --modified by G. Zhang 05/17/01 5:42PM
391 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
392
393 l_appl_id,
394 l_search_value,
395 p_category_id,
396 end_pnt;
397 ELSIF l_flag = 1011 THEN
398 EXECUTE IMMEDIATE sql_stmt USING
399 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
400 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
401
402 --modified by G. 3
403 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
404
405 l_appl_id,
406 p_item_type, p_item_applicable_to,
407 p_category_id,
408 end_pnt;
409 ELSIF l_flag = 1010 THEN
410 EXECUTE IMMEDIATE sql_stmt USING
411 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
412 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
413
414 --modified by G. Zhang 05/17/01 5:42PM
415 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
416
417 l_appl_id,
418 p_item_applicable_to,
419 p_category_id,
420 end_pnt;
421 ELSIF l_flag = 1001 THEN
422 EXECUTE IMMEDIATE sql_stmt USING
423 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
424 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
425
426 --added by G. Zhang 05/17/01 5:42PM
427 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
428
429 l_appl_id,
430 p_item_type,
431 p_category_id,
432 end_pnt;
433 ELSIF l_flag = 1000 THEN
434 EXECUTE IMMEDIATE sql_stmt USING
435 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
436 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
437
438 --modified by G. Zhang 05/17/01 5:42PM
439 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
440
441 l_appl_id,
442 p_category_id,
443 end_pnt;
444 ELSIF l_flag = 111 THEN
445 EXECUTE IMMEDIATE sql_stmt USING
446 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
447 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
448
449 --modified by G. Zhang 05/17/01 5:42PM
450 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
451
452 l_appl_id,
453 p_item_type, p_item_applicable_to, l_search_value, end_pnt;
454 ELSIF l_flag = 110 THEN
455 EXECUTE IMMEDIATE sql_stmt USING
456 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
457 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
458
459 --modified by G. Zhang 05/17/01 5:42PM
460 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
461
462 l_appl_id,
463 p_item_applicable_to, l_search_value, end_pnt;
464 ELSIF l_flag = 101 THEN
465 EXECUTE IMMEDIATE sql_stmt USING
466 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
467 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
468
469 --modified by G. Zhang 05/17/01 5:42PM
470 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
471
472 l_appl_id,
473 p_item_type, l_search_value, end_pnt;
474 ELSIF l_flag = 100 THEN
475 EXECUTE IMMEDIATE sql_stmt USING
476 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
477 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
478
479 --modified by G. Zhang 05/17/01 5:42PM
480 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
481
482 l_appl_id,
483 l_search_value, end_pnt;
484 ELSIF l_flag = 11 THEN
485 EXECUTE IMMEDIATE sql_stmt USING
486 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
487 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
488
489 --modified by G. Zhang 05/17/01 5:42PM
490 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
491
492 l_appl_id,
493 p_item_type, p_item_applicable_to, end_pnt;
494 ELSIF l_flag = 10 THEN
495 EXECUTE IMMEDIATE sql_stmt USING
496 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
497 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
498
499 --modified by G. Zhang 05/17/01 5:42PM
500 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
501
502 l_appl_id,
503 p_item_applicable_to, end_pnt;
504 ELSIF l_flag = 1 THEN
505 EXECUTE IMMEDIATE sql_stmt USING
506 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
507 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
508
509 --modified by G. Zhang 05/17/01 5:42PM
510 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
511
512 l_appl_id,
513 p_item_type, end_pnt;
514 ELSE
515 EXECUTE IMMEDIATE sql_stmt USING
516 OUT l_dlv_id_tbl, OUT l_acc_name_tbl, OUT l_dsp_name_tbl,
517 OUT l_item_type_tbl, OUT l_appl_to_tbl, OUT l_desc_tbl,
518
519 --modified by G. Zhang 05/17/01 5:42PM
520 OUT l_keyword_tbl, OUT l_file_name_tbl, OUT l_file_id_tbl, OUT l_version_tbl,
521
522 l_appl_id,
523 end_pnt;
524 END IF;
525
526 -- dbms_output.put_line('executed');
527
528 IF l_dlv_id_tbl IS NOT NULL AND start_pnt <= l_dlv_id_tbl.COUNT THEN
529 x_dlv_id_tbl := NUMBER_TABLE(l_dlv_id_tbl(start_pnt));
530 x_acc_name_tbl := VARCHAR2_TABLE_100(l_acc_name_tbl(start_pnt));
531 x_dsp_name_tbl := VARCHAR2_TABLE_300(l_dsp_name_tbl(start_pnt));
532 x_item_type_tbl := VARCHAR2_TABLE_100(l_item_type_tbl(start_pnt));
533 x_appl_to_tbl := VARCHAR2_TABLE_100(l_appl_to_tbl(start_pnt));
534 x_desc_tbl := VARCHAR2_TABLE_2000(l_desc_tbl(start_pnt));
535 x_keyword_tbl := VARCHAR2_TABLE_300(l_keyword_tbl(start_pnt));
536 x_file_name_tbl := VARCHAR2_TABLE_300(l_file_name_tbl(start_pnt));
537
538 --added by G. Zhang 05/17/01 5:42PM
539 x_file_id_tbl := NUMBER_TABLE(l_file_id_tbl(start_pnt));
540
541 x_version_tbl := NUMBER_TABLE(l_version_tbl(start_pnt));
542
543 l_count := 1;
544 FOR l_index IN start_pnt+1..l_dlv_id_tbl.COUNT LOOP
545 IF l_index > end_pnt THEN
546 EXIT;
547 END IF;
548 x_dlv_id_tbl.EXTEND;
549 x_acc_name_tbl.EXTEND;
550 x_dsp_name_tbl.EXTEND;
551 x_item_type_tbl.EXTEND;
552 x_appl_to_tbl.EXTEND;
553 x_desc_tbl.EXTEND;
554 x_keyword_tbl.EXTEND;
555 x_file_name_tbl.EXTEND;
556
557 --added by G. Zhang 05/17/01 5:42PM
558 x_file_id_tbl.EXTEND;
559
560 x_version_tbl.EXTEND;
561
562 l_count := l_count + 1;
563 x_dlv_id_tbl(l_count) := l_dlv_id_tbl(l_index);
564 x_acc_name_tbl(l_count) := l_acc_name_tbl(l_index);
565 x_dsp_name_tbl(l_count) := l_dsp_name_tbl(l_index);
566 x_item_type_tbl(l_count) := l_item_type_tbl(l_index);
567 x_appl_to_tbl(l_count) := l_appl_to_tbl(l_index);
568 x_desc_tbl(l_count) := l_desc_tbl(l_index);
569 x_keyword_tbl(l_count) := l_keyword_tbl(l_index);
570 x_file_name_tbl(l_count) := l_file_name_tbl(l_index);
571
572 --added by G. Zhang 05/17/01 5:42PM
573 x_file_id_tbl(l_count) := l_file_id_tbl(l_index);
574
575 x_version_tbl(l_count) := l_version_tbl(l_index);
576 END LOOP;
577 END IF;
578
579 /*
580 -- Get matchined rows
581 IF p_start_id > -1 THEN
582 IF p_start_id >= x_row_count THEN
583 RETURN;
584 END IF;
585
586 start_pnt := p_start_id;
587 IF p_batch_size > 0 THEN
588 end_pnt := p_start_id + p_batch_size;
589 ELSE
590 end_pnt := x_row_count;
591 END IF;
592 ELSE
593 end_pnt := x_row_count;
594 start_pnt := end_pnt - p_batch_size;
595 IF start_pnt < 0 THEN
596 start_pnt := 0;
597 END IF;
598 END IF;
599
600 IF l_flag = 111 THEN
601 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
602 p_item_type, p_item_applicable_to, l_search_value, end_pnt;
603 ELSIF l_flag = 110 THEN
604 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
605 p_item_applicable_to, l_search_value, end_pnt;
606 ELSIF l_flag = 101 THEN
607 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
608 p_item_type, l_search_value, end_pnt;
609 ELSIF l_flag = 100 THEN
610 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
611 l_search_value, end_pnt;
612 ELSIF l_flag = 11 THEN
613 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
614 p_item_type, p_item_applicable_to, end_pnt;
615 ELSIF l_flag = 10 THEN
616 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
617 p_item_applicable_to, end_pnt;
618 ELSIF l_flag = 1 THEN
619 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id,
620 p_item_type, end_pnt;
621 ELSE
622 OPEN l_dlv_cv FOR sql_stmt USING FND_GLOBAL.resp_appl_id, end_pnt;
623 END IF;
624
625 IF start_pnt > 0 THEN
626 FETCH l_dlv_cv BULK COLLECT INTO l_dlv_id_tbl, l_acc_name_tbl,
627 l_dsp_name_tbl, l_item_type_tbl, l_appl_to_tbl, l_desc_tbl,
628 l_keyword_tbl, l_file_name_tbl, l_version_tbl LIMIT start_pnt;
629 IF l_dlv_cv%NOTFOUND THEN
630 CLOSE l_dlv_cv;
631 RETURN;
632 END IF;
633 END IF;
634
635 FETCH l_dlv_cv BULK COLLECT INTO x_dlv_id_tbl, x_acc_name_tbl,
636 x_dsp_name_tbl, x_item_type_tbl, x_appl_to_tbl, x_desc_tbl,
637 x_keyword_tbl, x_file_name_tbl, x_version_tbl;
638 CLOSE l_dlv_cv;
639 */
640
641 -- Standard call to get message count and if count is 1, get message info
642 FND_MSG_PUB.count_and_get(
643 p_encoded => FND_API.g_false,
644 p_count => x_msg_count,
645 p_data => x_msg_data
646 );
647
648 EXCEPTION
649
650 WHEN FND_API.g_exc_unexpected_error THEN
651 x_return_status := FND_API.g_ret_sts_unexp_error ;
652 FND_MSG_PUB.count_and_get(
653 p_encoded => FND_API.g_false,
654 p_count => x_msg_count,
655 p_data => x_msg_data
656 );
657
658 WHEN OTHERS THEN
659 x_return_status := FND_API.g_ret_sts_unexp_error ;
660
661 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
662 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
663 END IF;
664
665 FND_MSG_PUB.count_and_get(
666 p_encoded => FND_API.g_false,
667 p_count => x_msg_count,
668 p_data => x_msg_data
669 );
670
671 END list_deliverable;
672
673
674 ---------------------------------------------------------------------
675 -- PROCEDURE
676 -- save_deliverable
677 --
678 -- PURPOSE
679 -- Save a logical deliverable
680 --
681 -- PARAMETERS
682 -- p_deliverable_rec: The logical deliverables to be saved
683 --
684 -- NOTES
685 -- 1. Insert a new deliverable if deliverable_id is null; update otherwise
686 -- 2. Raise an exception if access_name or display_name is missing;
687 -- or access_name is not unique
688 -- 3. Raise an exception if item_type or item_applicable_to is missing
689 -- or invalid (create)
690 -- 4. Raise an exception if the deliverable doesn't exist; or the version
691 -- doesn't match (update)
692 -- 5. Raise an exception for any other errors
693
694 ---------------------------------------------------------------------
695 PROCEDURE save_deliverable (
696 p_api_version IN NUMBER,
697 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
698 p_commit IN VARCHAR2 := FND_API.g_false,
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2,
702 p_deliverable_rec IN OUT NOCOPY DELIVERABLE_REC_TYPE ) IS
703
704 l_api_name CONSTANT VARCHAR2(30) := 'save_deliverable';
705 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
706
707 l_operation_type VARCHAR2(10) := 'INSERT';
708
709 l_deliverable_id NUMBER;
710 l_item_rec JTF_AMV_ITEM_PUB.ITEM_REC_TYPE;
711 l_return_status VARCHAR2(1);
712
713 l_appl_id NUMBER;
714
715 l_temp NUMBER;
716 BEGIN
717
718 -- Standard start of API savepoint
719 SAVEPOINT save_deliverable_grp;
720
721 -- Standard call to check for call compatibility
722 IF NOT FND_API.compatible_api_call(
723 g_api_version,
724 p_api_version,
725 l_api_name,
726 g_pkg_name
727 ) THEN
728 RAISE FND_API.g_exc_unexpected_error;
729 END IF;
730
731 -- Initialize message list if p_init_msg_list is set to TRUE
732 IF FND_API.to_boolean(p_init_msg_list) THEN
733 FND_MSG_PUB.initialize;
734 END IF;
735
736 -- Initialize API rturn status to success
737 x_return_status := FND_API.g_ret_sts_success;
738
739 -- API body
740
741 l_item_rec.external_access_flag := NULL;
742 l_item_rec.text_string := NULL;
743 l_item_rec.language_code := NULL;
744 l_item_rec.status_code := NULL;
745 l_item_rec.effective_start_date := NULL;
746 l_item_rec.EXPIRATION_DATE := NULL;
747 l_item_rec.ITEM_TYPE := NULL;
748 l_item_rec.URL_STRING := NULL;
749 l_item_rec.PUBLICATION_DATE := NULL;
750 l_item_rec.PRIORITY := NULL;
751 l_item_rec.CONTENT_TYPE_ID := NULL;
752 l_item_rec.OWNER_ID := NULL;
753 l_item_rec.DEFAULT_APPROVER_ID := NULL;
754 l_item_rec.ITEM_DESTINATION_TYPE := NULL;
755
756 l_item_rec.creation_date := NULL;
757 l_item_rec.created_by := NULL;
758 l_item_rec.last_update_date := NULL;
759 l_item_rec.last_updated_by := NULL;
760 l_item_rec.last_update_login := NULL;
761
762 -- fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, 671);
763 -- l_appl_id := FND_GLOBAL.resp_appl_id;
764 l_appl_id := 671;
765
766 l_item_rec.application_id := l_appl_id;
767
768 /*
769 p_deliverable_rec.x_action_status
770 := FND_API.g_ret_sts_error;
771 */
772
773 l_item_rec.item_id
774 := p_deliverable_rec.deliverable_id;
775 l_item_rec.item_name
776 := TRIM(p_deliverable_rec.display_name);
777 l_item_rec.access_name
778 := TRIM(p_deliverable_rec.access_name);
779 l_item_rec.description := p_deliverable_rec.description;
780 l_item_rec.object_version_number
781 := p_deliverable_rec.object_version_number;
782
783 IF NOT IBE_DSPMGRVALIDATION_GRP.check_deliverable_accessname(
784 l_item_rec.item_id, l_item_rec.access_name) THEN
785 RAISE FND_API.g_exc_error;
786 END IF;
787
788 IF l_item_rec.item_id IS NOT NULL THEN
789 -- Update an existing deliverable
790 l_operation_type := 'UPDATE';
791 END IF;
792
793 IF (l_operation_type = 'INSERT') THEN
794 l_item_rec.deliverable_type_code
795 := TRIM(p_deliverable_rec.item_type);
796 l_item_rec.applicable_to_code
797 := TRIM(p_deliverable_rec.item_applicable_to);
798
799 -- new validation code for enhancement 2317704
800 l_temp := IBE_DSPMGRVALIDATION_GRP.check_media_object(
801 p_operation => 'CREATE',
802 p_access_name => l_item_rec.access_name,
803 p_deliverable_type_code => l_item_rec.deliverable_type_code,
804 p_applicable_to_code => l_item_rec.applicable_to_code);
805 IF l_temp < 0 THEN
806 RAISE FND_API.g_exc_error;
807 END IF;
808 -- end for enhancement 2317704
809 JTF_AMV_ITEM_PUB.create_item(
810 p_api_version => g_amv_api_version,
811 x_return_status => l_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_item_rec => l_item_rec,
815 x_item_id => l_deliverable_id
816 );
817
818 IF l_return_status = FND_API.g_ret_sts_error THEN
819 RAISE FND_API.g_exc_error;
820 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
821 RAISE FND_API.g_exc_unexpected_error;
822 END IF;
823
824 IF TRIM(p_deliverable_rec.keywords) IS NOT NULL THEN
825 JTF_AMV_ITEM_PUB.add_itemkeyword(
826 p_api_version => g_amv_api_version,
827 x_return_status => l_return_status,
828 x_msg_count => x_msg_count,
829 x_msg_data => x_msg_data,
830 p_item_id => l_deliverable_id,
831 p_keyword => p_deliverable_rec.keywords
832 );
833
834 IF l_return_status = FND_API.g_ret_sts_error THEN
835 RAISE FND_API.g_exc_error;
836 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
837 RAISE FND_API.g_exc_unexpected_error;
838 END IF;
839 END IF;
840
841 p_deliverable_rec.deliverable_id := l_deliverable_id;
842 p_deliverable_rec.object_version_number := 1;
843
844 ELSE
845 -- 05/13/02 YAXU modified to support updateing the item_type
846 l_item_rec.deliverable_type_code := TRIM(p_deliverable_rec.item_type);
847 IF l_item_rec.deliverable_type_code IS NULL THEN
848 l_item_rec.deliverable_type_code := FND_API.g_miss_char;
849 END IF;
850 -- modified by abhandar to update the 'applicable to' also
851 --l_item_rec.applicable_to_code := FND_API.g_miss_char;
852 l_item_rec.applicable_to_code := TRIM(p_deliverable_rec.item_applicable_to);
853 IF l_item_rec.applicable_to_code IS NULL THEN
854 l_item_rec.applicable_to_code := FND_API.g_miss_char;
855 END IF;
856
857 -- new validation code for enhancement 2317704
858 l_temp := IBE_DSPMGRVALIDATION_GRP.check_media_object(
859 p_operation => 'UPDATE',
860 p_access_name => l_item_rec.access_name,
861 p_deliverable_type_code => l_item_rec.deliverable_type_code,
862 p_applicable_to_code => l_item_rec.applicable_to_code);
863 IF l_temp < 0 THEN
864 RAISE FND_API.g_exc_error;
865 END IF;
866 -- end for enhancement 2317704
867 JTF_AMV_ITEM_PUB.update_item(
868 p_api_version => g_amv_api_version,
869 x_return_status => l_return_status,
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_item_rec => l_item_rec
873 );
874
875 IF l_return_status = FND_API.g_ret_sts_error THEN
876 RAISE FND_API.g_exc_error;
877 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
878 RAISE FND_API.g_exc_unexpected_error;
879 END IF;
880
881 -- Delete existing keywords
882 JTF_AMV_ITEM_PUB.delete_itemkeyword(
883 p_api_version => g_amv_api_version,
884 x_return_status => l_return_status,
885 x_msg_count => x_msg_count,
886 x_msg_data => x_msg_data,
887 p_item_id => p_deliverable_rec.deliverable_id,
888 p_keyword_tab => NULL
889 );
890
891 IF l_return_status = FND_API.g_ret_sts_error THEN
892 RAISE FND_API.g_exc_error;
893 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
894 RAISE FND_API.g_exc_unexpected_error;
895 END IF;
896
897 IF TRIM(p_deliverable_rec.keywords) IS NOT NULL THEN
898 JTF_AMV_ITEM_PUB.add_itemkeyword(
899 p_api_version => g_amv_api_version,
900 x_return_status => l_return_status,
901 x_msg_count => x_msg_count,
902 x_msg_data => x_msg_data,
903 p_item_id => p_deliverable_rec.deliverable_id,
904 p_keyword => p_deliverable_rec.keywords
905 );
906
907 IF l_return_status = FND_API.g_ret_sts_error THEN
908 RAISE FND_API.g_exc_error;
909 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
910 RAISE FND_API.g_exc_unexpected_error;
911 END IF;
912 END IF;
913
914 -- update the object_version_number
915 p_deliverable_rec.object_version_number :=
916 p_deliverable_rec.object_version_number + 1;
917
918 END IF;
919
920 p_deliverable_rec.x_action_status
921 := FND_API.g_ret_sts_success;
922
923 -- Check if the caller requested to commit ,
924 -- If p_commit set to true, commit the transaction
925 IF FND_API.to_boolean(p_commit) THEN
926 COMMIT;
927 END IF;
928
929 -- Standard call to get message count and if count is 1, get message info
930 FND_MSG_PUB.count_and_get(
931 p_encoded => FND_API.g_false,
932 p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935
936 EXCEPTION
937
938 WHEN FND_API.g_exc_error THEN
939 ROLLBACK TO save_deliverable_grp;
940 x_return_status := FND_API.g_ret_sts_error;
941 p_deliverable_rec.x_action_status := FND_API.g_ret_sts_error;
942 FND_MSG_PUB.count_and_get(
943 p_encoded => FND_API.g_false,
944 p_count => x_msg_count,
945 p_data => x_msg_data
946 );
947
948 WHEN FND_API.g_exc_unexpected_error THEN
949 ROLLBACK TO save_deliverable_grp;
950 x_return_status := FND_API.g_ret_sts_unexp_error;
951 p_deliverable_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
952 FND_MSG_PUB.count_and_get(
953 p_encoded => FND_API.g_false,
954 p_count => x_msg_count,
955 p_data => x_msg_data
956 );
957
958 WHEN OTHERS THEN
959 ROLLBACK TO save_deliverable_grp;
960 x_return_status := FND_API.g_ret_sts_unexp_error;
961 p_deliverable_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
962
963 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
964 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
965 END IF;
966
967 FND_MSG_PUB.count_and_get(
968 p_encoded => FND_API.g_false,
969 p_count => x_msg_count,
970 p_data => x_msg_data
971 );
972
973 END save_deliverable;
974
975
976 ---------------------------------------------------------------------
977 -- PROCEDURE
978 -- save_deliverable
979 --
980 -- PURPOSE
981 -- Save a collection of logical deliverables
982 --
983 -- PARAMETERS
984 -- p_deliverable_tbl: A collection of the logical deliverables to be saved
985 --
986 -- NOTES
987 -- 1. Insert a new deliverable if deliverable_id is null; update otherwise
988 -- 2. Raise an exception if access_name or display_name is missing;
989 -- or access_name is not unique
990 -- 3. Raise an exception if item_type or item_applicable_to is missing
991 -- or invalid (create)
992 -- 4. Raise an exception if the deliverable doesn't exist; or the version
993 -- doesn't match (update)
994 -- 5. Raise an exception for any other errors
995
996 ---------------------------------------------------------------------
997 PROCEDURE save_deliverable (
998 p_api_version IN NUMBER,
999 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1000 p_commit IN VARCHAR2 := FND_API.g_false,
1001 x_return_status OUT NOCOPY VARCHAR2,
1002 x_msg_count OUT NOCOPY NUMBER,
1003 x_msg_data OUT NOCOPY VARCHAR2,
1004 p_deliverable_tbl IN OUT NOCOPY DELIVERABLE_TBL_TYPE ) IS
1005
1006 l_api_name CONSTANT VARCHAR2(30) := 'save_deliverable';
1007 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1008
1009 l_return_status VARCHAR2(1);
1010
1011 l_index NUMBER;
1012
1013 BEGIN
1014
1015 -- Standard start of API savepoint
1016 SAVEPOINT save_deliverable_grp;
1017
1018 -- Standard call to check for call compatibility
1019 IF NOT FND_API.compatible_api_call(
1020 g_api_version,
1021 p_api_version,
1022 l_api_name,
1023 g_pkg_name
1024 ) THEN
1025 RAISE FND_API.g_exc_unexpected_error;
1026 END IF;
1027
1028 -- Initialize message list if p_init_msg_list is set to TRUE
1029 IF FND_API.to_boolean(p_init_msg_list) THEN
1030 FND_MSG_PUB.initialize;
1031 END IF;
1032
1033 -- Initialize API rturn status to success
1034 x_return_status := FND_API.g_ret_sts_success;
1035
1036 -- API body
1037
1038 IF p_deliverable_tbl IS NOT NULL THEN
1039 FOR l_index IN 1..p_deliverable_tbl.COUNT LOOP
1040
1041 save_deliverable(
1042 p_api_version => p_api_version,
1043 x_return_status => l_return_status,
1044 x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data,
1046 p_deliverable_rec => p_deliverable_tbl(l_index)
1047 );
1048
1049 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1050 x_return_status := FND_API.g_ret_sts_unexp_error;
1051 ELSIF l_return_status = FND_API.g_ret_sts_error
1052 AND x_return_status <> FND_API.g_ret_sts_unexp_error THEN
1053 x_return_status := FND_API.g_ret_sts_error;
1054 END IF;
1055
1056 END LOOP;
1057 END IF;
1058
1059 -- Check if the caller requested to commit ,
1060 -- If p_commit set to true, commit the transaction
1061 IF FND_API.to_boolean(p_commit) THEN
1062 COMMIT;
1063 END IF;
1064
1065 -- Standard call to get message count and if count is 1, get message info
1066 FND_MSG_PUB.count_and_get(
1067 p_encoded => FND_API.g_false,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071
1072
1073 EXCEPTION
1074
1075 WHEN FND_API.g_exc_error THEN
1076 ROLLBACK TO save_deliverable_grp;
1077 x_return_status := FND_API.g_ret_sts_error;
1078 FND_MSG_PUB.count_and_get(
1079 p_encoded => FND_API.g_false,
1080 p_count => x_msg_count,
1081 p_data => x_msg_data
1082 );
1083
1084 WHEN FND_API.g_exc_unexpected_error THEN
1085 ROLLBACK TO save_deliverable_grp;
1086 x_return_status := FND_API.g_ret_sts_unexp_error ;
1087 FND_MSG_PUB.count_and_get(
1088 p_encoded => FND_API.g_false,
1089 p_count => x_msg_count,
1090 p_data => x_msg_data
1091 );
1092
1093
1094 WHEN OTHERS THEN
1095 ROLLBACK TO save_deliverable_grp;
1096 x_return_status := FND_API.g_ret_sts_unexp_error ;
1097
1098 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1099 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1100 END IF;
1101
1102 FND_MSG_PUB.count_and_get(
1103 p_encoded => FND_API.g_false,
1104 p_count => x_msg_count,
1105 p_data => x_msg_data
1106 );
1107
1108 END save_deliverable;
1109
1110
1111 ---------------------------------------------------------------------
1112 -- PROCEDURE
1113 -- save_deliverable
1114 --
1115 -- PURPOSE
1116 -- Save a logical deliverable with the default attachment for all-site
1117 -- and all-language
1118 --
1119 -- PARAMETERS
1120 -- p_dlv_ath_rec: A logical deliverable with the default attachment
1121 -- for all-site and all-language to be saved
1122 --
1123 -- NOTES
1124 -- 1. Insert a new deliverable if deliverable_id is null; update otherwise
1125 -- 2. Raise an exception if access_name or display_name is missing;
1126 -- or access_name is not unique
1127 -- 3. Raise an exception if item_type or item_applicable_to is missing
1128 -- or invalid (create)
1129 -- 4. Raise an exception if the deliverable doesn't exist; or the version
1130 -- doesn't match (update)
1131 -- 5. If creating/updating deliverable succeeds, update the default
1132 -- attachment for all-site and all-language
1133 -- 6. Raise an exception if fails to create an attachment, or all-site
1134 -- and all-language mappings. Only undo the changes made to attachment
1135 -- physicalmap tables
1136 -- 7. Raise an exception if chosen default attachment is invalid, e.g.,
1137 -- it's not associated with the given deliverable.
1138 -- 8. Raise an exception for any other errors
1139
1140 ---------------------------------------------------------------------
1141 PROCEDURE save_deliverable (
1142 p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1144 p_commit IN VARCHAR2 := FND_API.g_false,
1145 x_return_status OUT NOCOPY VARCHAR2,
1146 x_msg_count OUT NOCOPY NUMBER,
1147 x_msg_data OUT NOCOPY VARCHAR2,
1148 p_dlv_ath_rec IN OUT NOCOPY DLV_ATH_REC_TYPE ) IS
1149
1150 l_api_name CONSTANT VARCHAR2(30) := 'save_deliverable';
1151 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1152
1153 l_operation_type VARCHAR2(10) := 'INSERT';
1154 l_create_attachment VARCHAR2(1) := FND_API.g_false;
1155 l_delete_mapping VARCHAR2(1) := FND_API.g_false;
1156 l_create_mapping VARCHAR2(1) := FND_API.g_false;
1157 l_attachment_id NUMBER;
1158
1159 l_deliverable_rec DELIVERABLE_REC_TYPE;
1160 l_attachment_rec IBE_Attachment_GRP.ATTACHMENT_REC_TYPE;
1161 l_language_code_tbl IBE_PhysicalMap_GRP.LANGUAGE_CODE_TBL_TYPE
1162 := IBE_PhysicalMap_GRP.LANGUAGE_CODE_TBL_TYPE(NULL);
1163
1164 l_return_status VARCHAR2(1);
1165
1166 BEGIN
1167
1168 -- Standard start of API savepoint
1169 SAVEPOINT save_deliverable_grp;
1170
1171 -- Standard call to check for call compatibility
1172 IF NOT FND_API.compatible_api_call(
1173 g_api_version,
1174 p_api_version,
1175 l_api_name,
1176 g_pkg_name
1177 ) THEN
1178 RAISE FND_API.g_exc_unexpected_error;
1179 END IF;
1180
1181 -- Initialize message list if p_init_msg_list is set to TRUE
1182 IF FND_API.to_boolean(p_init_msg_list) THEN
1183 FND_MSG_PUB.initialize;
1184 END IF;
1185
1186 -- Initialize API rturn status to success
1187 x_return_status := FND_API.g_ret_sts_success;
1188
1189 -- API body
1190
1191 IF p_dlv_ath_rec.deliverable_id IS NOT NULL THEN
1192 -- Update an existing deliverable
1193 l_operation_type := 'UPDATE';
1194 END IF;
1195
1196 l_deliverable_rec.deliverable_id := p_dlv_ath_rec.deliverable_id;
1197 l_deliverable_rec.access_name := p_dlv_ath_rec.access_name;
1198 l_deliverable_rec.display_name := p_dlv_ath_rec.display_name;
1199 l_deliverable_rec.item_type := p_dlv_ath_rec.item_type;
1200 l_deliverable_rec.item_applicable_to := p_dlv_ath_rec.item_applicable_to;
1201 l_deliverable_rec.keywords := p_dlv_ath_rec.keywords;
1202 l_deliverable_rec.description := p_dlv_ath_rec.description;
1203 l_deliverable_rec.object_version_number := p_dlv_ath_rec.object_version_number;
1204 l_deliverable_rec.x_action_status := NULL;
1205
1206 save_deliverable(
1207 p_api_version => p_api_version,
1208 x_return_status => l_return_status,
1209 x_msg_count => x_msg_count,
1210 x_msg_data => x_msg_data,
1211 p_deliverable_rec => l_deliverable_rec
1212 );
1213
1214 p_dlv_ath_rec.deliverable_id := l_deliverable_rec.deliverable_id;
1215 p_dlv_ath_rec.object_version_number := l_deliverable_rec.object_version_number;
1216 p_dlv_ath_rec.x_action_status := l_deliverable_rec.x_action_status;
1217
1218 IF l_return_status = FND_API.g_ret_sts_error THEN
1219 RAISE FND_API.g_exc_error;
1220 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1221 RAISE FND_API.g_exc_unexpected_error;
1222 END IF;
1223
1224 -- Update/Create default attachment for all-site and all-language
1225
1226 BEGIN
1227
1228 SAVEPOINT save_one_attachment_grp;
1229
1230 IF l_operation_type = 'INSERT' THEN
1231
1232 -- Create an attachment for all-site and all-language if any
1233 IF TRIM(p_dlv_ath_rec.ath_file_name) IS NOT NULL THEN
1234 l_create_attachment := FND_API.g_true;
1235 l_create_mapping := FND_API.g_true;
1236
1237 END IF;
1238
1239 ELSE
1240
1241 -- Update the default attachment for the existing deliverable
1242
1243 IF TRIM(p_dlv_ath_rec.ath_file_name) IS NULL THEN
1244 l_delete_mapping := FND_API.g_true;
1245
1246 ELSE
1247
1248 -- Check if it's an existing attachment
1249 -- If so, validate it! Update all-site and all-lang mappings
1250 -- if it's the new default
1251 -- If not, delete all-site and all-lang mappings if any
1252 -- and create the new attachment with all-site and all-lang
1253 -- mappings
1254
1255 --modified by G. Zhang 05/23/01 10:57AM
1256 l_attachment_id := IBE_DSPMGRVALIDATION_GRP.check_attachment_exists(
1257 p_dlv_ath_rec.deliverable_id,p_dlv_ath_rec.ath_file_id,p_dlv_ath_rec.ath_file_name);
1258
1259 IF l_attachment_id IS NOT NULL THEN
1260 -- existing attachment
1261 -- validate it!
1262 --modified by G. Zhang 05/23/01 10:57AM
1263 --IF NOT IBE_DSPMGRVALIDATION_GRP.check_attachment_deliverable(
1264 -- l_attachment_id, p_dlv_ath_rec.deliverable_id) THEN
1265 -- -- invalid attachment for the given deliverable
1266 -- RAISE FND_API.g_exc_error;
1267 --END IF;
1268
1269 IF NOT IBE_DSPMGRVALIDATION_GRP.check_default_attachment(
1270 l_attachment_id) THEN
1271 l_delete_mapping := FND_API.g_true;
1272 l_create_mapping := FND_API.g_true;
1273 END IF;
1274
1275 ELSE
1276 -- new attachment
1277 l_create_attachment := FND_API.g_true;
1278 l_delete_mapping := FND_API.g_true;
1279 l_create_mapping := FND_API.g_true;
1280 END IF;
1281
1282 END IF;
1283
1284 END IF;
1285
1286 IF l_create_attachment = FND_API.g_true THEN
1287 l_attachment_rec.attachment_id := NULL;
1288 l_attachment_rec.deliverable_id := p_dlv_ath_rec.deliverable_id;
1289 l_attachment_rec.file_name := p_dlv_ath_rec.ath_file_name;
1290
1291 --added by G. Zhang 05/17/01 5:42PM
1292 l_attachment_rec.file_id := p_dlv_ath_rec.ath_file_id;
1293
1294 --added by G. Zhang 08/01/01 6:15PM
1295 --fix bug#1911212
1296 l_attachment_rec.application_id := 671;
1297 l_attachment_rec.attachment_used_by := 'ITEM';
1298
1299 l_attachment_rec.object_version_number := NULL;
1300 l_attachment_rec.x_action_status := NULL;
1301 IBE_Attachment_GRP.save_attachment(
1302
1303 p_api_version => p_api_version,
1304 x_return_status => l_return_status,
1305 x_msg_count => x_msg_count,
1306 x_msg_data => x_msg_data,
1307 p_attachment_rec => l_attachment_rec
1308 );
1309
1310 IF l_return_status = FND_API.g_ret_sts_error THEN
1311 RAISE FND_API.g_exc_error;
1312 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1313 RAISE FND_API.g_exc_unexpected_error;
1314 END IF;
1315
1316 l_attachment_id := l_attachment_rec.attachment_id;
1317 END IF;
1318
1319 IF l_delete_mapping = FND_API.g_true THEN
1320 -- Delete all-site and all-lang mapping for this deliverable
1321 IBE_PhysicalMap_GRP.delete_dlv_all_all(
1322 p_deliverable_id => p_dlv_ath_rec.deliverable_id
1323 );
1324 END IF;
1325
1326 IF l_create_mapping = FND_API.g_true THEN
1327 -- Create all-site and all-lang mapping for this default attachment
1328 IBE_PhysicalMap_GRP.save_physicalmap(
1329 p_api_version => p_api_version,
1330 x_return_status => l_return_status,
1331 x_msg_count => x_msg_count,
1332 x_msg_data => x_msg_data,
1333 p_attachment_id => l_attachment_id,
1334 p_msite_id => NULL,
1335 p_language_code_tbl => l_language_code_tbl
1336 );
1337
1338 IF l_return_status = FND_API.g_ret_sts_error THEN
1339 RAISE FND_API.g_exc_error;
1340 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1341 RAISE FND_API.g_exc_unexpected_error;
1342 END IF;
1343 END IF;
1344
1345 p_dlv_ath_rec.x_ath_action_status := FND_API.g_ret_sts_success;
1346
1347 EXCEPTION
1348
1349 WHEN FND_API.g_exc_error THEN
1350 ROLLBACK TO save_one_attachment_grp;
1351 p_dlv_ath_rec.x_ath_action_status := FND_API.g_ret_sts_error;
1352 x_return_status := FND_API.g_ret_sts_error;
1353
1354 WHEN FND_API.g_exc_unexpected_error THEN
1355 ROLLBACK TO save_one_attachment_grp;
1356 p_dlv_ath_rec.x_ath_action_status := FND_API.g_ret_sts_unexp_error;
1357 x_return_status := FND_API.g_ret_sts_unexp_error;
1358
1359 WHEN OTHERS THEN
1360 ROLLBACK TO save_one_attachment_grp;
1361 p_dlv_ath_rec.x_ath_action_status := FND_API.g_ret_sts_unexp_error;
1362 x_return_status := FND_API.g_ret_sts_unexp_error ;
1363
1364 IF FND_MSG_PUB.check_msg_level(
1365 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1366 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1367 END IF;
1368 END;
1369
1370 -- Check if the caller requested to commit ,
1371 -- If p_commit set to true, commit the transaction
1372 IF FND_API.to_boolean(p_commit) THEN
1373 COMMIT;
1374 END IF;
1375
1376 -- Standard call to get message count and if count is 1, get message info
1377 FND_MSG_PUB.count_and_get(
1378 p_encoded => FND_API.g_false,
1379 p_count => x_msg_count,
1380 p_data => x_msg_data
1381 );
1382
1383 EXCEPTION
1384
1385 WHEN FND_API.g_exc_error THEN
1386 ROLLBACK TO save_deliverable_grp;
1387 x_return_status := FND_API.g_ret_sts_error;
1388 p_dlv_ath_rec.x_action_status := FND_API.g_ret_sts_error;
1389 FND_MSG_PUB.count_and_get(
1390 p_encoded => FND_API.g_false,
1391 p_count => x_msg_count,
1392 p_data => x_msg_data
1393 );
1394
1395 WHEN FND_API.g_exc_unexpected_error THEN
1396 ROLLBACK TO save_deliverable_grp;
1397 x_return_status := FND_API.g_ret_sts_unexp_error ;
1398 p_dlv_ath_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
1399 FND_MSG_PUB.count_and_get(
1400 p_encoded => FND_API.g_false,
1401 p_count => x_msg_count,
1402 p_data => x_msg_data
1403 );
1404
1405 WHEN OTHERS THEN
1406 ROLLBACK TO save_deliverable_grp;
1407 x_return_status := FND_API.g_ret_sts_unexp_error ;
1408 p_dlv_ath_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
1409
1410 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1411 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1412 END IF;
1413
1414 FND_MSG_PUB.count_and_get(
1415 p_encoded => FND_API.g_false,
1416 p_count => x_msg_count,
1417 p_data => x_msg_data
1418 );
1419
1420 END save_deliverable;
1421
1422
1423 ---------------------------------------------------------------------
1424 -- PROCEDURE
1425 -- save_deliverable
1426 --
1427 -- PURPOSE
1428 -- Save a collection of logical deliverables with the default attachments
1429 -- for all-site and all-language
1430 --
1431 -- PARAMETERS
1432 -- p_dlv_ath_tbl: A collection of logical deliverables with the default
1433 -- attachments for all-site and all-language to be saved
1434 --
1435 -- NOTES
1436 -- 1. Insert a new deliverable if deliverable_id is null; update otherwise
1437 -- 2. Raise an exception if access_name or display_name is missing;
1438 -- or access_name is not unique
1439 -- 3. Raise an exception if item_type or item_applicable_to is missing
1440 -- or invalid (create)
1441 -- 4. Raise an exception if the deliverable doesn't exist; or the version
1442 -- doesn't match (update)
1443 -- 5. If creating/updating deliverable succeeds, update the default
1444 -- attachment for all-site and all-language
1445 -- 6. Raise an exception if fails to create an attachment, or all-site
1446 -- and all-language mappings. Only undo the changes made to attachment
1447 -- physicalmap tables
1448 -- 7. Raise an exception if chosen default attachment is invalid, e.g.,
1449 -- it's not associated with the given deliverable.
1450 -- 8. Raise an exception for any other errors
1451
1452 ---------------------------------------------------------------------
1453 PROCEDURE save_deliverable (
1454 p_api_version IN NUMBER,
1455 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1456 p_commit IN VARCHAR2 := FND_API.g_false,
1457 x_return_status OUT NOCOPY VARCHAR2,
1458 x_msg_count OUT NOCOPY NUMBER,
1459 x_msg_data OUT NOCOPY VARCHAR2,
1460 p_dlv_ath_tbl IN OUT NOCOPY DLV_ATH_TBL_TYPE ) IS
1461
1462 l_api_name CONSTANT VARCHAR2(30) := 'save_deliverable';
1463 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1464
1465 l_return_status VARCHAR2(1);
1466
1467 BEGIN
1468
1469 -- Standard start of API savepoint
1470 SAVEPOINT save_deliverable_grp;
1471
1472 -- Standard call to check for call compatibility
1473 IF NOT FND_API.compatible_api_call(
1474 g_api_version,
1475 p_api_version,
1476 l_api_name,
1477 g_pkg_name
1478 ) THEN
1479 RAISE FND_API.g_exc_unexpected_error;
1480 END IF;
1481
1482 -- Initialize message list if p_init_msg_list is set to TRUE
1483 IF FND_API.to_boolean(p_init_msg_list) THEN
1484 FND_MSG_PUB.initialize;
1485 END IF;
1486
1487 -- Initialize API rturn status to success
1488 x_return_status := FND_API.g_ret_sts_success;
1489
1490 -- API body
1491
1492 IF p_dlv_ath_tbl IS NOT NULL THEN
1493 FOR l_index IN 1..p_dlv_ath_tbl.COUNT LOOP
1494
1495 save_deliverable(
1496 p_api_version => p_api_version,
1497 x_return_status => l_return_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data,
1500 p_dlv_ath_rec => p_dlv_ath_tbl(l_index)
1501 );
1502
1503 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1504 x_return_status := FND_API.g_ret_sts_unexp_error;
1505 ELSIF l_return_status = FND_API.g_ret_sts_error
1506 AND x_return_status <> FND_API.g_ret_sts_unexp_error THEN
1507 x_return_status := FND_API.g_ret_sts_error;
1508 END IF;
1509
1510 END LOOP;
1511 END IF;
1512
1513 -- Check if the caller requested to commit ,
1514 -- If p_commit set to true, commit the transaction
1515 IF FND_API.to_boolean(p_commit) THEN
1516 COMMIT;
1517 END IF;
1518
1519 -- Standard call to get message count and if count is 1, get message info
1520 FND_MSG_PUB.count_and_get(
1521 p_encoded => FND_API.g_false,
1522 p_count => x_msg_count,
1523 p_data => x_msg_data
1524 );
1525
1526 EXCEPTION
1527
1528 WHEN FND_API.g_exc_error THEN
1529 ROLLBACK TO save_deliverable_grp;
1530 x_return_status := FND_API.g_ret_sts_error;
1531 FND_MSG_PUB.count_and_get(
1532 p_encoded => FND_API.g_false,
1533 p_count => x_msg_count,
1534 p_data => x_msg_data
1535 );
1536
1537 WHEN FND_API.g_exc_unexpected_error THEN
1538 ROLLBACK TO save_deliverable_grp;
1539 x_return_status := FND_API.g_ret_sts_unexp_error ;
1540 FND_MSG_PUB.count_and_get(
1541 p_encoded => FND_API.g_false,
1542 p_count => x_msg_count,
1543 p_data => x_msg_data
1544 );
1545
1546 WHEN OTHERS THEN
1547 ROLLBACK TO save_deliverable_grp;
1548 x_return_status := FND_API.g_ret_sts_unexp_error ;
1549
1550 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1551 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1552 END IF;
1553
1554 FND_MSG_PUB.count_and_get(
1555 p_encoded => FND_API.g_false,
1556 p_count => x_msg_count,
1557 p_data => x_msg_data
1558 );
1559
1560 END save_deliverable;
1561
1562
1563 ---------------------------------------------------------------------
1564 -- PROCEDURE
1565 -- delete_deliverable
1566 --
1567 -- PURPOSE
1568 -- Delete a collection of logical deliverables
1569 --
1570 -- PARAMETERS
1571 -- p_dlv_id_ver_tbl: A collection of IDs and versions of the logical
1572 -- deliverables to be deleted
1573 --
1574 -- NOTES
1575 -- 1. Delete all the deliverables and associated physical attachments along
1576 -- with all the associations
1577 -- 2. Raise an exception if the deliverable doesn't exist; or the version
1578 -- doesn't match
1579 -- 3. A logical deliverable is not allowed to be deleted if it's currently
1580 -- in use unless the caller has the right privilege
1581 -- 4. Raise an exception for any other errors
1582 ---------------------------------------------------------------------
1583 PROCEDURE delete_deliverable (
1584 p_api_version IN NUMBER,
1585 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1586 p_commit IN VARCHAR2 := FND_API.g_false,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_dlv_id_ver_tbl IN OUT NOCOPY DLV_ID_VER_TBL_TYPE ) IS
1591
1592 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
1593 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1594
1595 l_index NUMBER;
1596
1597 l_return_status VARCHAR2(1);
1598
1599 BEGIN
1600
1601 -- Standard start of API savepoint
1602 SAVEPOINT delete_deliverable_grp;
1603
1604 -- Standard call to check for call compatibility
1605 IF NOT FND_API.compatible_api_call(
1606 g_api_version,
1607 p_api_version,
1608 l_api_name,
1609 g_pkg_name
1610 ) THEN
1611 RAISE FND_API.g_exc_unexpected_error;
1612 END IF;
1613
1614 -- Initialize message list if p_init_msg_list is set to TRUE
1615 IF FND_API.to_boolean(p_init_msg_list) THEN
1616 FND_MSG_PUB.initialize;
1617 END IF;
1618
1619 -- Initialize API rturn status to success
1620 x_return_status := FND_API.g_ret_sts_success;
1621
1622 -- API body
1623
1624 -- Check if the deliverable exists
1625 IF p_dlv_id_ver_tbl IS NOT NULL THEN
1626 FOR l_index IN 1..p_dlv_id_ver_tbl.COUNT LOOP
1627 BEGIN
1628
1629 -- Standard start of API savepoint
1630 SAVEPOINT delete_one_deliverable_grp;
1631
1632 IF NOT IBE_DSPMGRVALIDATION_GRP.check_deliverable_exists(
1633 p_dlv_id_ver_tbl(l_index).deliverable_id,
1634 p_dlv_id_ver_tbl(l_index).object_version_number) THEN
1635 RAISE FND_API.g_exc_error;
1636 END IF;
1637
1638 -- Delete from the section tables
1639 IBE_DSP_SECTION_GRP.update_deliverable_to_null(
1640 p_api_version => p_api_version,
1641 p_deliverable_id => p_dlv_id_ver_tbl(l_index).deliverable_id,
1642 x_return_status => l_return_status,
1643 x_msg_count => x_msg_count,
1644 x_msg_data => x_msg_data
1645 );
1646
1647 IF l_return_status = FND_API.g_ret_sts_error THEN
1648 RAISE FND_API.g_exc_error;
1649 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1650 RAISE FND_API.g_exc_unexpected_error;
1651 END IF;
1652
1653 -- Delete all the associated display contexts
1654 IBE_DisplayContext_GRP.delete_deliverable(
1655 p_dlv_id_ver_tbl(l_index).deliverable_id
1656 );
1657
1658 -- Delete all the associated template categories
1659 IBE_TplCategory_GRP.delete_deliverable(
1660 p_dlv_id_ver_tbl(l_index).deliverable_id
1661 );
1662
1663 -- Delete all the associated logical contents
1664 IBE_LogicalContent_GRP.delete_deliverable(
1665 p_dlv_id_ver_tbl(l_index).deliverable_id
1666 );
1667
1668 -- Delete all the associated physical_site_language mappings
1669 IBE_PhysicalMap_GRP.delete_deliverable(
1670 p_dlv_id_ver_tbl(l_index).deliverable_id
1671 );
1672
1673 -- Delete the item
1674 JTF_AMV_ITEM_PUB.delete_item(
1675 p_api_version => g_amv_api_version,
1676 x_return_status => l_return_status,
1677 x_msg_count => x_msg_count,
1678 x_msg_data => x_msg_data,
1679 p_item_id => p_dlv_id_ver_tbl(l_index).deliverable_id
1680 );
1681
1682 IF l_return_status = FND_API.g_ret_sts_error THEN
1683 RAISE FND_API.g_exc_error;
1684 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1685 RAISE FND_API.g_exc_unexpected_error;
1686 END IF;
1687
1688 p_dlv_id_ver_tbl(l_index).x_action_status
1689 := FND_API.g_ret_sts_success;
1690
1691 EXCEPTION
1692
1693 WHEN FND_API.g_exc_error THEN
1694 ROLLBACK TO delete_one_deliverable_grp;
1695 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
1696 x_return_status := FND_API.g_ret_sts_error;
1697 END IF;
1698 p_dlv_id_ver_tbl(l_index).x_action_status
1699 := FND_API.g_ret_sts_error;
1700
1701 WHEN FND_API.g_exc_unexpected_error THEN
1702 ROLLBACK TO delete_one_deliverable_grp;
1703 x_return_status := FND_API.g_ret_sts_unexp_error;
1704 p_dlv_id_ver_tbl(l_index).x_action_status
1705 := FND_API.g_ret_sts_unexp_error;
1706
1707 WHEN OTHERS THEN
1708 ROLLBACK TO delete_one_deliverable_grp;
1709 x_return_status := FND_API.g_ret_sts_unexp_error ;
1710 p_dlv_id_ver_tbl(l_index).x_action_status
1711 := FND_API.g_ret_sts_unexp_error;
1712
1713 IF FND_MSG_PUB.check_msg_level(
1714 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1715 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1716 END IF;
1717
1718 END;
1719 END LOOP;
1720 END IF;
1721
1722 -- Check if the caller requested to commit ,
1723 -- If p_commit set to true, commit the transaction
1724 IF FND_API.to_boolean(p_commit) THEN
1725 COMMIT;
1726 END IF;
1727
1728 -- Standard call to get message count and if count is 1, get message info
1729 FND_MSG_PUB.count_and_get(
1730 p_encoded => FND_API.g_false,
1731 p_count => x_msg_count,
1732 p_data => x_msg_data
1733 );
1734
1735 EXCEPTION
1736
1737 WHEN FND_API.g_exc_error THEN
1738 ROLLBACK TO delete_deliverable_grp;
1739 x_return_status := FND_API.g_ret_sts_error;
1740 FND_MSG_PUB.count_and_get(
1741 p_encoded => FND_API.g_false,
1742 p_count => x_msg_count,
1743 p_data => x_msg_data
1744 );
1745
1746 WHEN FND_API.g_exc_unexpected_error THEN
1747 ROLLBACK TO delete_deliverable_grp;
1748 x_return_status := FND_API.g_ret_sts_unexp_error ;
1749 FND_MSG_PUB.count_and_get(
1750 p_encoded => FND_API.g_false,
1751 p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754
1755 WHEN OTHERS THEN
1756 ROLLBACK TO delete_deliverable_grp;
1757 x_return_status := FND_API.g_ret_sts_unexp_error ;
1758
1759 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1760 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1761 END IF;
1762
1763 FND_MSG_PUB.count_and_get(
1764 p_encoded => FND_API.g_false,
1765 p_count => x_msg_count,
1766 p_data => x_msg_data
1767 );
1768
1769 END delete_deliverable;
1770
1771 END IBE_Deliverable_GRP;