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