[Home] [Help]
PACKAGE BODY: APPS.IBE_ATTACHMENT_GRP
Source
1 PACKAGE BODY IBE_Attachment_GRP AS
2 /* $Header: IBEGATHB.pls 120.1 2005/11/17 15:55:21 szou noship $ */
3 g_amv_api_version CONSTANT NUMBER := 1.0;
4
5 TYPE AthCurTyp IS REF CURSOR;
6
7 --modifed by G. Zhang 05/09/2001 04:06PM
8 g_view_name CONSTANT VARCHAR2(48) := 'jtf_amv_attachments a';
9
10 PROCEDURE list_attachment (
11 p_api_version IN NUMBER,
12 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16
17 --added by G. Zhang 04/30/2001 11:18AM
18 p_appl_id IN NUMBER := 671,
19
20 p_deliverable_id IN NUMBER,
21 p_start_id IN NUMBER,
22 p_batch_size IN NUMBER,
23 x_row_count OUT NOCOPY NUMBER,
24 x_ath_id_tbl OUT NOCOPY NUMBER_TABLE,
25 x_dlv_id_tbl OUT NOCOPY NUMBER_TABLE,
26 x_file_name_tbl OUT NOCOPY VARCHAR2_TABLE_300,
27
28 --added by G. Zhang 04/30/2001 11:18AM
29 x_file_id_tbl OUT NOCOPY NUMBER_TABLE,
30 x_file_ext_tbl OUT NOCOPY VARCHAR2_TABLE_20,
31 x_dsp_width_tbl OUT NOCOPY NUMBER_TABLE,
32 x_dsp_height_tbl OUT NOCOPY NUMBER_TABLE,
33
34 x_version_tbl OUT NOCOPY NUMBER_TABLE ) IS
35
36 l_api_name CONSTANT VARCHAR2(30) := 'list_attachment';
37 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
38
39 l_return_status VARCHAR2(1);
40
41 l_ath_cv AthCurTyp;
42 l_flag NUMBER := 0;
43
44 --modified by G. Zhang 05/09/2001 04:06PM
45 sql_cond VARCHAR2(240);
46 sql_stmt VARCHAR2(960);
47
48 start_pnt NUMBER;
49 end_pnt NUMBER;
50 l_index NUMBER;
51 l_count NUMBER;
52
53 l_ath_id_tbl JTF_NUMBER_TABLE;
54 l_dlv_id_tbl JTF_NUMBER_TABLE;
55 l_file_name_tbl JTF_VARCHAR2_TABLE_300;
56
57 --added by G. Zhang 04/30/2001 11:18AM
58 l_file_id_tbl JTF_NUMBER_TABLE;
59 l_file_ext_tbl JTF_VARCHAR2_TABLE_100;
60 l_dsp_width_tbl JTF_NUMBER_TABLE;
61 l_dsp_height_tbl JTF_NUMBER_TABLE;
62
63 l_version_tbl JTF_NUMBER_TABLE;
64 BEGIN
65 -- Standard call to check for call compatibility
66 IF NOT FND_API.compatible_api_call(
67 g_api_version,
68 p_api_version,
69 l_api_name,
70 g_pkg_name ) THEN
71 RAISE FND_API.g_exc_unexpected_error;
72 END IF;
73
74 -- Initialize message list if p_init_msg_list is set to TRUE
75 IF FND_API.to_boolean(p_init_msg_list) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API rturn status to success
80 x_return_status := FND_API.g_ret_sts_success;
81 sql_cond := ' FROM ' || g_view_name ||
82 ' WHERE a.application_id = :appl_id ' ||
83 ' AND EXISTS (SELECT NULL FROM ' ||
84 ' ibe_dsp_lgl_phys_map m WHERE ' ||
85 ' a.attachment_id = m.attachment_id) ';
86
87 -- API body
88
89 IF p_start_id < -1 OR p_batch_size < 0
90 OR (p_start_id = -1 AND p_batch_size = 0) THEN
91 FND_MESSAGE.set_name('IBE', 'IBE_DSP_QUERY_INVLD');
92 FND_MESSAGE.set_token('0', p_start_id);
93 FND_MESSAGE.set_token('1', p_batch_size);
94 FND_MSG_PUB.add;
95 RAISE FND_API.g_exc_unexpected_error;
96 END IF;
97
98 IF p_deliverable_id IS NOT NULL THEN
99 l_flag := 1;
100 --modified by G. Zhang 05/09/2001 04:06PM
101 sql_cond := sql_cond || 'AND a.attachment_used_by_id = :dlv_id ';
102 END IF;
103
104 -- fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, 671);
105 -- l_appl_id := FND_GLOBAL.resp_appl_id;
106
107 -- Get Total Row Number
108 --modified by G. Zhang 05/09/2001 04:06PM
109 sql_stmt := 'SELECT COUNT(*) ' || sql_cond;
110 IF l_flag = 1 THEN
111 OPEN l_ath_cv FOR sql_stmt
112 USING p_appl_id,p_deliverable_id;
113 ELSE
114 OPEN l_ath_cv FOR sql_stmt USING p_appl_id;
115 END IF;
116
117 FETCH l_ath_cv INTO x_row_count;
118 CLOSE l_ath_cv;
119
120 IF x_row_count = 0 THEN
121 FND_MSG_PUB.count_and_get(
122 p_encoded => FND_API.g_false,
123 p_count => x_msg_count,
124 p_data => x_msg_data
125 );
126 RETURN;
127 END IF;
128
129 x_ath_id_tbl := NULL;
130 x_dlv_id_tbl := NULL;
131 x_file_name_tbl := NULL;
132
133 --added by G. Zhang 04/30/2001 11:18AM
134 x_file_id_tbl :=NULL;
135 x_file_ext_tbl :=NULL;
136 x_dsp_width_tbl :=NULL;
137 x_dsp_height_tbl :=NULL;
138
139 x_version_tbl := NULL;
140
141 -- Get matchined rows
142 IF p_start_id > -1 THEN
143 IF p_start_id >= x_row_count THEN
144 FND_MSG_PUB.count_and_get(
145 p_encoded => FND_API.g_false,
146 p_count => x_msg_count,
147 p_data => x_msg_data
148 );
149 RETURN;
150 END IF;
151
152 start_pnt := p_start_id + 1;
153 IF p_batch_size > 0 THEN
154 end_pnt := p_start_id + p_batch_size;
155 ELSE
156 end_pnt := x_row_count;
157 END IF;
158 ELSE
159 end_pnt := x_row_count;
160 start_pnt := end_pnt - p_batch_size + 1;
161 IF start_pnt < 1 THEN
162 start_pnt := 1;
163 END IF;
164 END IF;
165
166 -- modified by G. Zhang 04/30/2001 11:18AM
167 sql_stmt := 'BEGIN '
168 || 'SELECT attachment_id, attachment_used_by_id, file_name, file_id, file_extension, display_width, display_height, '
169 || 'object_version_number '
170 || 'BULK COLLECT INTO :id_tbl, :dlv_id_tbl, :file_tbl, :file_id_tbl, :file_ext_tbl, :dsp_width_tbl, :dsp_height_tbl, :version_tbl '
171 || 'FROM (SELECT a.attachment_id, a.attachment_used_by_id, a.file_name, a.file_id, a.file_extension, a.display_width, a.display_height, a.object_version_number '
172 || sql_cond
173 || 'ORDER BY a.file_name ) '
174 || 'WHERE ROWNUM <= :row_num '
175 || '; END;';
176
177 -- dbms_output.put_line('sql_cond=' || sql_cond);
178 -- dbms_output.put_line('sql_stmt=' || sql_stmt);
179
180 IF l_flag = 1 THEN
181 EXECUTE IMMEDIATE sql_stmt USING
182 OUT l_ath_id_tbl, OUT l_dlv_id_tbl, OUT l_file_name_tbl,
183
184 --added by G. Zhang 04/30/2001 11:18AM
185 OUT l_file_id_tbl, OUT l_file_ext_tbl, OUT l_dsp_width_tbl, OUT l_dsp_height_tbl,
186
187 OUT l_version_tbl,
188
189 --modified by G. Zhang 04/30/2001 11:18AM
190 p_appl_id, p_deliverable_id, end_pnt;
191
192 ELSE
193 EXECUTE IMMEDIATE sql_stmt USING
194 OUT l_ath_id_tbl, OUT l_dlv_id_tbl, OUT l_file_name_tbl,
195
196 --added by G. Zhang 04/30/2001 11:18AM
197 OUT l_file_id_tbl, OUT l_file_ext_tbl, OUT l_dsp_width_tbl, OUT l_dsp_height_tbl,
198
199 OUT l_version_tbl,
200
201 --modified by G. Zhang 04/30/2001 11:18AM
202 p_appl_id, end_pnt;
203
204 END IF;
205
206 -- dbms_output.put_line('executed');
207
208 IF l_ath_id_tbl IS NOT NULL AND start_pnt <= l_ath_id_tbl.COUNT THEN
209 x_ath_id_tbl := NUMBER_TABLE(l_ath_id_tbl(start_pnt));
210 x_dlv_id_tbl := NUMBER_TABLE(l_dlv_id_tbl(start_pnt));
211 x_file_name_tbl := VARCHAR2_TABLE_300(l_file_name_tbl(start_pnt));
212
213 --added by G. Zhang 04/30/2001 11:18AM
214 x_file_id_tbl := NUMBER_TABLE(l_file_id_tbl(start_pnt));
215 x_file_ext_tbl := VARCHAR2_TABLE_20(l_file_ext_tbl(start_pnt));
216 x_dsp_width_tbl := NUMBER_TABLE(l_dsp_width_tbl(start_pnt));
217 x_dsp_height_tbl := NUMBER_TABLE(l_dsp_height_tbl(start_pnt));
218
219 x_version_tbl := NUMBER_TABLE(l_version_tbl(start_pnt));
220
221 l_count := 1;
222 FOR l_index IN start_pnt+1..l_ath_id_tbl.COUNT LOOP
223 IF l_index > end_pnt THEN
224 EXIT;
225 END IF;
226 x_ath_id_tbl.EXTEND;
227 x_dlv_id_tbl.EXTEND;
228 x_file_name_tbl.EXTEND;
229
230 --added by G. Zhang 04/30/2001 11:18AM
231 x_file_id_tbl.EXTEND;
232 x_file_ext_tbl.EXTEND;
233 x_dsp_width_tbl.EXTEND;
234 x_dsp_height_tbl.EXTEND;
235
236 x_version_tbl.EXTEND;
237
238 l_count := l_count + 1;
239 x_ath_id_tbl(l_count) := l_ath_id_tbl(l_index);
240 x_dlv_id_tbl(l_count) := l_dlv_id_tbl(l_index);
241 x_file_name_tbl(l_count) := l_file_name_tbl(l_index);
242
243 --added by G. Zhang 04/30/2001 11:18AM
244 x_file_id_tbl(l_count) := l_file_id_tbl(l_index);
245 x_file_ext_tbl(l_count) := l_file_ext_tbl(l_index);
246 x_dsp_width_tbl(l_count) := l_dsp_width_tbl(l_index);
247 x_dsp_height_tbl(l_count) := l_dsp_height_tbl(l_index);
248
249 x_version_tbl(l_count) := l_version_tbl(l_index);
250 END LOOP;
251 END IF;
252
253 -- Standard call to get message count and if count is 1, get message info
254 FND_MSG_PUB.count_and_get(
255 p_encoded => FND_API.g_false,
256 p_count => x_msg_count,
257 p_data => x_msg_data
258 );
259
260 -- dbms_output.put_line('reached where');
261
262 EXCEPTION
263
264 WHEN FND_API.g_exc_unexpected_error THEN
265 x_return_status := FND_API.g_ret_sts_unexp_error ;
266 FND_MSG_PUB.count_and_get(
267 p_encoded => FND_API.g_false,
268 p_count => x_msg_count,
269 p_data => x_msg_data
270 );
271
272 WHEN OTHERS THEN
273 x_return_status := FND_API.g_ret_sts_unexp_error ;
274
275 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
276 END IF;
277
278 FND_MSG_PUB.count_and_get(
279 p_encoded => FND_API.g_false,
280 p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283
284 END list_attachment;
285
286
287 ---------------------------------------------------------------------+
288 --+PROCEDURE
289 -- save_attachment
290 --+
291 -- PURPOSE
292 -- Save a physical attachment
293 --+
294 -- PARAMETERS
295 -- p_attachment_rec: the physical attachment to be saved
296 --+
297 -- NOTES
298 -- 1. Insert a new attachment if the attachment_id is null; Update otherwise
299 -- 2. Raise an exception if file_name is null or not unique
300 -- 3. Raise an exception if the deliverable doesn't exist (create)
301 -- 4. Raise an exception if the attachment doesn't exist; or the version
302 -- doesn't match (update)
303 -- 5. Raise an exception for any other errors
304 ---------------------------------------------------------------------+
305 PROCEDURE save_attachment (
306 p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
308 p_commit IN VARCHAR2 := FND_API.g_false,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 p_attachment_rec IN OUT NOCOPY ATTACHMENT_REC_TYPE ) IS
313
314 l_api_name CONSTANT VARCHAR2(30) := 'save_attachment';
315 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
316
317 l_operation_type VARCHAR2(10) := 'INSERT';
318
319 l_attachment_id NUMBER;
320 l_act_attachment_rec JTF_AMV_ATTACHMENT_PUB.ACT_ATTACHMENT_REC_TYPE;
321 l_return_status VARCHAR2(1);
322 l_appl_id NUMBER;
323 BEGIN
324 -- Standard start of API savepoint
325 SAVEPOINT save_attachment_grp;
326
327 -- Standard call to check for call compatibility
328 IF NOT FND_API.compatible_api_call(
329 g_api_version,
330 p_api_version,
331 l_api_name,
332 g_pkg_name ) THEN
333 RAISE FND_API.g_exc_unexpected_error;
334 END IF;
335
336 -- Initialize message list if p_init_msg_list is set to TRUE
337 IF FND_API.to_boolean(p_init_msg_list) THEN
338 FND_MSG_PUB.initialize;
339 END IF;
340
341 -- Initialize API rturn status to success
342 x_return_status := FND_API.g_ret_sts_success;
343
344 -- API body
345
346 l_act_attachment_rec.LAST_UPDATE_DATE := NULL;
347 l_act_attachment_rec.LAST_UPDATED_BY := NULL;
348 l_act_attachment_rec.CREATION_DATE := NULL;
349 l_act_attachment_rec.CREATED_BY := NULL;
350 l_act_attachment_rec.LAST_UPDATE_LOGIN := NULL;
351
352 l_act_attachment_rec.OWNER_USER_ID := NULL;
353 l_act_attachment_rec.VERSION := NULL;
354
355 -- comment out by G. Zhang 04/30/2001 11:18AM
356 -- l_act_attachment_rec.ENABLED_FLAG := 'N';
357 -- l_act_attachment_rec.CAN_FULFILL_ELECTRONIC_FLAG := 'N';
358 -- l_act_attachment_rec.FILE_ID := NULL;
359 -- l_act_attachment_rec.FILE_EXTENSION := NULL;
360 -- l_act_attachment_rec.KEYWORDS := NULL;
361 -- l_act_attachment_rec.DISPLAY_WIDTH := NULL;
362 -- l_act_attachment_rec.DISPLAY_HEIGHT := NULL;
363 -- l_act_attachment_rec.DISPLAY_LOCATION := NULL;
364 -- l_act_attachment_rec.LINK_TO := NULL;
365 -- l_act_attachment_rec.LINK_URL := NULL;
366 -- l_act_attachment_rec.SEND_FOR_PREVIEW_FLAG := NULL;
367 -- l_act_attachment_rec.ATTACHMENT_TYPE := NULL;
368 -- l_act_attachment_rec.LANGUAGE_CODE := NULL;
369 -- l_act_attachment_rec.DESCRIPTION := NULL;
370 -- l_act_attachment_rec.DEFAULT_STYLE_SHEET := NULL;
371 -- l_act_attachment_rec.DISPLAY_RULE_ID := NULL;
372 -- l_act_attachment_rec.DISPLAY_PROGRAM := NULL;
373 -- l_act_attachment_rec.ATTRIBUTE_CATEGORY := NULL;
374 -- l_act_attachment_rec.ATTRIBUTE1 := NULL;
375 -- l_act_attachment_rec.ATTRIBUTE2 := NULL;
376 -- l_act_attachment_rec.ATTRIBUTE3 := NULL;
377 -- l_act_attachment_rec.ATTRIBUTE4 := NULL;
378 -- l_act_attachment_rec.ATTRIBUTE5 := NULL;
379 -- l_act_attachment_rec.ATTRIBUTE6 := NULL;
380 -- l_act_attachment_rec.ATTRIBUTE7 := NULL;
381 -- l_act_attachment_rec.ATTRIBUTE8 := NULL;
382 -- l_act_attachment_rec.ATTRIBUTE9 := NULL;
383 -- l_act_attachment_rec.ATTRIBUTE10 := NULL;
384 -- l_act_attachment_rec.ATTRIBUTE11 := NULL;
385 -- l_act_attachment_rec.ATTRIBUTE12 := NULL;
386 -- l_act_attachment_rec.ATTRIBUTE13 := NULL;
387 -- l_act_attachment_rec.ATTRIBUTE14 := NULL;
388 -- l_act_attachment_rec.ATTRIBUTE15 := NULL;
389 -- l_act_attachment_rec.DISPLAY_URL := 'not in use';
390
391 -- Modified by G. Zhang 04/30/2001 11:18AM
392 l_act_attachment_rec.ENABLED_FLAG := p_attachment_rec.ENABLED_FLAG;
393 l_act_attachment_rec.CAN_FULFILL_ELECTRONIC_FLAG := p_attachment_rec.CAN_FULFILL_ELECTRONIC_FLAG;
394 IF p_attachment_rec.FILE_ID > 0 THEN
395 l_act_attachment_rec.FILE_ID := p_attachment_rec.FILE_ID;
396 ELSE
397 l_act_attachment_rec.FILE_ID := NULL;
398 END IF;
399 l_act_attachment_rec.SECURED_FLAG := p_attachment_rec.SECURED_FLAG; --bug 2633722
400 l_act_attachment_rec.FILE_EXTENSION := p_attachment_rec.FILE_EXTENSION;
401 l_act_attachment_rec.KEYWORDS := p_attachment_rec.KEYWORDS;
402 l_act_attachment_rec.DISPLAY_WIDTH := p_attachment_rec.DISPLAY_WIDTH;
403 l_act_attachment_rec.DISPLAY_HEIGHT := p_attachment_rec.DISPLAY_HEIGHT;
404 l_act_attachment_rec.DISPLAY_LOCATION := p_attachment_rec.DISPLAY_LOCATION;
405 l_act_attachment_rec.LINK_TO := p_attachment_rec.LINK_TO;
406 l_act_attachment_rec.LINK_URL := p_attachment_rec.LINK_URL;
407 l_act_attachment_rec.SEND_FOR_PREVIEW_FLAG := p_attachment_rec.SEND_FOR_PREVIEW_FLAG;
408 l_act_attachment_rec.ATTACHMENT_TYPE := p_attachment_rec.ATTACHMENT_TYPE;
409 l_act_attachment_rec.LANGUAGE_CODE := p_attachment_rec.LANGUAGE_CODE;
410 l_act_attachment_rec.DESCRIPTION := p_attachment_rec.DESCRIPTION;
411 l_act_attachment_rec.DEFAULT_STYLE_SHEET := p_attachment_rec.DEFAULT_STYLE_SHEET;
412 l_act_attachment_rec.DISPLAY_RULE_ID := p_attachment_rec.DISPLAY_RULE_ID;
413 l_act_attachment_rec.DISPLAY_PROGRAM := p_attachment_rec.DISPLAY_PROGRAM;
414 l_act_attachment_rec.ATTRIBUTE_CATEGORY := p_attachment_rec.ATTRIBUTE_CATEGORY;
415 l_act_attachment_rec.ATTRIBUTE1 := p_attachment_rec.ATTRIBUTE1;
416 l_act_attachment_rec.ATTRIBUTE2 := p_attachment_rec.ATTRIBUTE2;
417 l_act_attachment_rec.ATTRIBUTE3 := p_attachment_rec.ATTRIBUTE3;
418 l_act_attachment_rec.ATTRIBUTE4 := p_attachment_rec.ATTRIBUTE4;
419 l_act_attachment_rec.ATTRIBUTE5 := p_attachment_rec.ATTRIBUTE5;
420 l_act_attachment_rec.ATTRIBUTE6 := p_attachment_rec.ATTRIBUTE6;
421 l_act_attachment_rec.ATTRIBUTE7 := p_attachment_rec.ATTRIBUTE7;
422 l_act_attachment_rec.ATTRIBUTE8 := p_attachment_rec.ATTRIBUTE8;
423 l_act_attachment_rec.ATTRIBUTE9 := p_attachment_rec.ATTRIBUTE9;
424 l_act_attachment_rec.ATTRIBUTE10 := p_attachment_rec.ATTRIBUTE10;
425 l_act_attachment_rec.ATTRIBUTE11 := p_attachment_rec.ATTRIBUTE11;
426 l_act_attachment_rec.ATTRIBUTE12 := p_attachment_rec.ATTRIBUTE12;
427 l_act_attachment_rec.ATTRIBUTE13 := p_attachment_rec.ATTRIBUTE13;
428 l_act_attachment_rec.ATTRIBUTE14 := p_attachment_rec.ATTRIBUTE14;
429 l_act_attachment_rec.ATTRIBUTE15 := p_attachment_rec.ATTRIBUTE15;
430 l_act_attachment_rec.DISPLAY_URL := p_attachment_rec.DISPLAY_URL;
431
432 --added as jtf_amv_attachment_pub requires this if deliverable type
433 --is MEDIA.
434 If( l_act_attachment_rec.DISPLAY_URL is NULL) Then
435 l_act_attachment_rec.DISPLAY_URL := 'not in use';
436 End If;
437
438 -- fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, 671);
439 -- l_appl_id := FND_GLOBAL.resp_appl_id;
440 -- comment out by G. Zhang 04/30/2001 11:18AM
441 --l_appl_id := 671;
442 --l_act_attachment_rec.APPLICATION_ID := l_appl_id;
443 --l_act_attachment_rec.ATTACHMENT_USED_BY := 'ITEM';
444
445 -- modified by G. Zhang 04/30/2001 11:18AM
446 l_act_attachment_rec.APPLICATION_ID := p_attachment_rec.APPLICATION_ID;
447 l_act_attachment_rec.ATTACHMENT_USED_BY := p_attachment_rec.ATTACHMENT_USED_BY;
448
449 l_act_attachment_rec.attachment_id := p_attachment_rec.attachment_id;
450 l_act_attachment_rec.file_name := TRIM(p_attachment_rec.file_name);
451 l_act_attachment_rec.object_version_number := p_attachment_rec.object_version_number;
452 -- BUG # 1715934 - need ability to have one attachment
453 -- being used by mutiple items.
454 --IF NOT IBE_DSPMGRVALIDATION_GRP.check_attachment_filename(
455 -- l_act_attachment_rec.attachment_id,
456 -- l_act_attachment_rec.file_name) THEN
457 -- RAISE FND_API.g_exc_error;
458 --END IF;
459
460 --added by G. Zhang 04/30/2001 11:18AM
461 IF l_act_attachment_rec.APPLICATION_ID IS NULL THEN
462 RAISE FND_API.g_exc_error;
463 END IF;
464 IF l_act_attachment_rec.ATTACHMENT_USED_BY IS NULL THEN
465 RAISE FND_API.g_exc_error;
466 END IF;
467
468 IF l_act_attachment_rec.attachment_id IS NOT NULL THEN
469 -- Update an existing attachment
470 l_operation_type := 'UPDATE';
471 ELSE
472 IF NOT IBE_DSPMGRVALIDATION_GRP.check_deliverable_exists(
473 p_attachment_rec.deliverable_id) THEN
474 RAISE FND_API.g_exc_error;
475 END IF;
476 END IF;
477
478 IF (l_operation_type = 'INSERT') THEN
479 l_act_attachment_rec.attachment_used_by_id := p_attachment_rec.deliverable_id;
480
481 JTF_AMV_ATTACHMENT_PUB.create_act_attachment(
482 p_api_version => g_amv_api_version,
483 x_return_status => l_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data,
486 p_act_attachment_rec => l_act_attachment_rec,
487 x_act_attachment_id => l_attachment_id
488 );
489
490 IF l_return_status = FND_API.g_ret_sts_error THEN
491 RAISE FND_API.g_exc_error;
492 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
493 RAISE FND_API.g_exc_unexpected_error;
494 END IF;
495
496 -- update the attachment_id and object_version_number
497 p_attachment_rec.attachment_id := l_attachment_id;
498 p_attachment_rec.object_version_number := 1;
499
500 ELSE
501
502 l_act_attachment_rec.attachment_used_by_id
503 := FND_API.G_MISS_NUM;
504
505 JTF_AMV_ATTACHMENT_PUB.update_act_attachment(
506 p_api_version => g_amv_api_version,
507 x_return_status => l_return_status,
508 x_msg_count => x_msg_count,
509 x_msg_data => x_msg_data,
510 p_act_attachment_rec => l_act_attachment_rec
511 );
512
513 IF l_return_status = FND_API.g_ret_sts_error THEN
514 RAISE FND_API.g_exc_error;
515 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
516 RAISE FND_API.g_exc_unexpected_error;
517 END IF;
518
519 -- update the object_version_number
520 p_attachment_rec.object_version_number :=
521 p_attachment_rec.object_version_number + 1;
522
523 END IF;
524
525 p_attachment_rec.x_action_status
526 := FND_API.g_ret_sts_success;
527
528 -- Check if the caller requested to commit ,
529 -- If p_commit set to true, commit the transaction
530 IF FND_API.to_boolean(p_commit) THEN
531 COMMIT;
532 END IF;
533
534 -- Standard call to get message count and if count is 1, get message info
535 FND_MSG_PUB.count_and_get(
536 p_encoded => FND_API.g_false,
537 p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540
541 EXCEPTION
542
543 WHEN FND_API.g_exc_error THEN
544 ROLLBACK TO save_attachment_grp;
545 x_return_status := FND_API.g_ret_sts_error;
546 p_attachment_rec.x_action_status := FND_API.g_ret_sts_error;
547 FND_MSG_PUB.count_and_get(
548 p_encoded => FND_API.g_false,
549 p_count => x_msg_count,
550 p_data => x_msg_data
551 );
552
553 WHEN FND_API.g_exc_unexpected_error THEN
554 ROLLBACK TO save_attachment_grp;
555 x_return_status := FND_API.g_ret_sts_unexp_error ;
556 p_attachment_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
557 FND_MSG_PUB.count_and_get(
558 p_encoded => FND_API.g_false,
559 p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562
563 WHEN OTHERS THEN
564 ROLLBACK TO save_attachment_grp;
565 x_return_status := FND_API.g_ret_sts_unexp_error ;
566 p_attachment_rec.x_action_status := FND_API.g_ret_sts_unexp_error;
567
568 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
569 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
570 END IF;
571
572 FND_MSG_PUB.count_and_get(
573 p_encoded => FND_API.g_false,
574 p_count => x_msg_count,
575 p_data => x_msg_data
576 );
577
578 END save_attachment;
579
580
581 ---------------------------------------------------------------------
582 -- PROCEDURE
583 -- save_attachment
584 --
585 -- PURPOSE
586 -- Save a collection of physical attachments
587 --
588 -- PARAMETERS
589 -- p_attachment_tbl: A collection of the physical attachments to be saved
590 --
591 -- NOTES
592 -- 1. Insert a new attachment if the attachment_id is null; Update otherwise
593 -- 2. Raise an exception if file_name is null or not unique
594 -- 3. Raise an exception if the deliverable doesn't exist (create)
595 -- 4. Raise an exception if the attachment doesn't exist; or the version
596 -- doesn't match (update)
597 -- 5. Raise an exception for any other errors
598 ---------------------------------------------------------------------
599 PROCEDURE save_attachment (
600 p_api_version IN NUMBER,
601 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
602 p_commit IN VARCHAR2 := FND_API.g_false,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 p_attachment_tbl IN OUT NOCOPY ATTACHMENT_TBL_TYPE ) IS
607
608 l_api_name CONSTANT VARCHAR2(30) := 'save_attachment';
609 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
610
611 l_return_status VARCHAR2(1);
612
613 l_index NUMBER;
614
615 BEGIN
616
617 -- Standard start of API savepoint
618 SAVEPOINT save_attachment_grp;
619
620 -- Standard call to check for call compatibility
621 IF NOT FND_API.compatible_api_call(
622 g_api_version,
623 p_api_version,
624 l_api_name,
625 g_pkg_name
626 ) THEN
627 RAISE FND_API.g_exc_unexpected_error;
628 END IF;
629
630 -- Initialize message list if p_init_msg_list is set to TRUE
631 IF FND_API.to_boolean(p_init_msg_list) THEN
632 FND_MSG_PUB.initialize;
633 END IF;
634
635 -- Initialize API rturn status to success
636 x_return_status := FND_API.g_ret_sts_success;
637
638 -- API body
639
640 IF p_attachment_tbl IS NOT NULL THEN
641 FOR l_index IN 1..p_attachment_tbl.COUNT LOOP
642
643 save_attachment(
644 p_api_version => p_api_version,
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648 p_attachment_rec => p_attachment_tbl(l_index)
649 );
650
651
652 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
653 x_return_status := FND_API.g_ret_sts_unexp_error;
654 ELSIF l_return_status = FND_API.g_ret_sts_error
655 AND x_return_status <> FND_API.g_ret_sts_unexp_error THEN
656 x_return_status := FND_API.g_ret_sts_error;
657 END IF;
658
659 END LOOP;
660 END IF;
661
662 -- Check if the caller requested to commit ,
663 -- If p_commit set to true, commit the transaction
664 IF FND_API.to_boolean(p_commit) THEN
665 COMMIT;
666 END IF;
667
668 -- Standard call to get message count and if count is 1, get message info
669 FND_MSG_PUB.count_and_get(
670 p_encoded => FND_API.g_false,
671 p_count => x_msg_count,
672 p_data => x_msg_data
673 );
674
675
676 EXCEPTION
677
678 WHEN FND_API.g_exc_error THEN
679 ROLLBACK TO save_attachment_grp;
680 x_return_status := FND_API.g_ret_sts_error;
681 FND_MSG_PUB.count_and_get(
682 p_encoded => FND_API.g_false,
683 p_count => x_msg_count,
684 p_data => x_msg_data
685 );
686
687 WHEN FND_API.g_exc_unexpected_error THEN
688 ROLLBACK TO save_attachment_grp;
689 x_return_status := FND_API.g_ret_sts_unexp_error ;
690 FND_MSG_PUB.count_and_get(
691 p_encoded => FND_API.g_false,
692 p_count => x_msg_count,
693 p_data => x_msg_data
694 );
695
696 WHEN OTHERS THEN
697 ROLLBACK TO save_attachment_grp;
698 x_return_status := FND_API.g_ret_sts_unexp_error ;
699 FND_MSG_PUB.count_and_get(
700 p_encoded => FND_API.g_false,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704
705 x_return_status := FND_API.g_ret_sts_unexp_error ;
706
707 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
708 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
709 END IF;
710
711 FND_MSG_PUB.count_and_get(
712 p_encoded => FND_API.g_false,
713 p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716
717 END save_attachment;
718
719
720 -------------------------------------------------------------------
721 -- PROCEDURE
722 -- delete_attachment
723 --
724 -- PURPOSE
725 -- Delete a collection of physical attachments
726 --
727 -- PARAMETERS
728 -- p_ath_id_ver_tbl: A collection of IDs and versions of the physical
729 -- attachments to be deleted
730 --
731 -- NOTES
732 -- 1. Delete all the attachments and associated physical_site_language
733 -- mappings
734 -- 2. Raise an exception if the attachment doesn't exist; or the version
735 -- doesn't match
736 -- 3. Raise an exception for any other errors
737 ---------------------------------------------------------------------
738 PROCEDURE delete_attachment (
739 p_api_version IN NUMBER,
740 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
741 p_commit IN VARCHAR2 := FND_API.g_false,
742 x_return_status OUT NOCOPY VARCHAR2,
743 x_msg_count OUT NOCOPY NUMBER,
744 x_msg_data OUT NOCOPY VARCHAR2,
745 p_ath_id_ver_tbl IN OUT NOCOPY ATH_ID_VER_TBL_TYPE ) IS
746
747 l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment';
748 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
749
750 l_index NUMBER;
751 l_file_id NUMBER;
752 l_count NUMBER;
753
754 l_return_status VARCHAR2(1);
755
756 BEGIN
757
758 -- Standard start of API savepoint
759 SAVEPOINT save_attachment_grp;
760
761 -- Standard call to check for call compatibility
762 IF NOT FND_API.compatible_api_call(
763 g_api_version,
764 p_api_version,
765 l_api_name,
766 g_pkg_name
767 ) THEN
768 RAISE FND_API.g_exc_unexpected_error;
769 END IF;
770
771 -- Initialize message list if p_init_msg_list is set to TRUE
772 IF FND_API.to_boolean(p_init_msg_list) THEN
773 FND_MSG_PUB.initialize;
774 END IF;
775
776 -- Initialize API rturn status to success
777 x_return_status := FND_API.g_ret_sts_success;
778
779 -- API body
780
781 IF p_ath_id_ver_tbl IS NOT NULL THEN
782 FOR l_index IN 1..p_ath_id_ver_tbl.COUNT LOOP
783 BEGIN
784
785 -- Standard start of API savepoint
786 SAVEPOINT delete_one_attachment_grp;
787
788 /*
789 p_ath_id_ver_tbl(l_index).x_action_status
790 := FND_API.g_ret_sts_error;
791 */
792
793 IF NOT IBE_DSPMGRVALIDATION_GRP.check_attachment_exists(
794 p_ath_id_ver_tbl(l_index).attachment_id,
795 p_ath_id_ver_tbl(l_index).object_version_number) THEN
796 RAISE FND_API.g_exc_error;
797 END IF;
798
799 IBE_PhysicalMap_GRP.delete_attachment(
800 p_ath_id_ver_tbl(l_index).attachment_id
801 );
802
803 --added by YAXU on 07/31/02
804 select file_id into l_file_id
805 from jtf_amv_attachments
806 where attachment_id = p_ath_id_ver_tbl(l_index).attachment_id;
807
808
809 -- Delete the attachment
810 JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
811 p_api_version => g_amv_api_version,
812 x_return_status => l_return_status,
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data,
815 p_act_attachment_id => p_ath_id_ver_tbl(l_index).attachment_id,
816 p_object_version => p_ath_id_ver_tbl(l_index).object_version_number
817 );
818
819 --added by YAXU on 07/31/02
820 IF l_file_id>0
821 THEN
822 select count(1) into l_count
823 from jtf_amv_attachments
824 where file_id = l_file_id;
825 IF l_count=0
826 THEN
827 fnd_gfm.delete_lob(l_file_id );
828 END IF;
829 END IF;
830
831
832 IF l_return_status = FND_API.g_ret_sts_error THEN
833 RAISE FND_API.g_exc_error;
834 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
835 RAISE FND_API.g_exc_unexpected_error;
836 END IF;
837
838 p_ath_id_ver_tbl(l_index).x_action_status
839 := FND_API.g_ret_sts_success;
840
841 EXCEPTION
842
843 WHEN FND_API.g_exc_error THEN
844 ROLLBACK TO delete_one_attachment_grp;
845 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
846 x_return_status := FND_API.g_ret_sts_error;
847 END IF;
848 p_ath_id_ver_tbl(l_index).x_action_status
849 := FND_API.g_ret_sts_error;
850
851 WHEN FND_API.g_exc_unexpected_error THEN
852 ROLLBACK TO delete_one_attachment_grp;
853 x_return_status := FND_API.g_ret_sts_unexp_error ;
854 p_ath_id_ver_tbl(l_index).x_action_status
855 := FND_API.g_ret_sts_unexp_error ;
856
857 WHEN OTHERS THEN
858 ROLLBACK TO delete_one_attachment_grp;
859 x_return_status := FND_API.g_ret_sts_unexp_error ;
860 p_ath_id_ver_tbl(l_index).x_action_status
861 := FND_API.g_ret_sts_unexp_error ;
862 IF FND_MSG_PUB.check_msg_level(
863 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
864 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
865 END IF;
866
867 END;
868 END LOOP;
869 END IF;
870
871 -- Check if the caller requested to commit ,
872 -- If p_commit set to true, commit the transaction
873 IF FND_API.to_boolean(p_commit) THEN
874 COMMIT;
875 END IF;
876
877 -- Standard call to get message count and if count is 1, get message info
878 FND_MSG_PUB.count_and_get(
879 p_encoded => FND_API.g_false,
880 p_count => x_msg_count,
881 p_data => x_msg_data
882 );
883 -- x_msg_count := FND_MSG_PUB.count_msg();
884 -- x_msg_data := FND_MSG_PUB.get(FND_MSG_PUB.g_last, FND_API.g_false);
885
886 EXCEPTION
887
888 WHEN FND_API.g_exc_error THEN
889 ROLLBACK TO delete_attachment_grp;
890 x_return_status := FND_API.g_ret_sts_error;
891 FND_MSG_PUB.count_and_get(
892 p_encoded => FND_API.g_false,
893 p_count => x_msg_count,
894 p_data => x_msg_data
895 );
896
897 WHEN FND_API.g_exc_unexpected_error THEN
898 ROLLBACK TO delete_attachment_grp;
899 x_return_status := FND_API.g_ret_sts_unexp_error ;
900 FND_MSG_PUB.count_and_get(
901 p_encoded => FND_API.g_false,
902 p_count => x_msg_count,
903 p_data => x_msg_data
904 );
905
906 WHEN OTHERS THEN
907 ROLLBACK TO delete_attachment_grp;
908 x_return_status := FND_API.g_ret_sts_unexp_error ;
909
910 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
911 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
912 END IF;
913
914 FND_MSG_PUB.count_and_get(
915 p_encoded => FND_API.g_false,
916 p_count => x_msg_count,
917 p_data => x_msg_data
918 );
919
920 END delete_attachment;
921
922 END IBE_Attachment_GRP;