[Home] [Help]
PACKAGE BODY: APPS.JTF_AMV_ATTACHMENT_PUB
Source
1 PACKAGE BODY jtf_amv_attachment_pub AS
2 /* $Header: jtfpattb.pls 115.11 2002/11/26 22:14:43 stopiwal ship $ */
3 --
4 -- PACKAGE
5 -- JTF_AMV_ATTACHMENT_PUB
6 --
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_AMV_ATTACHMENT_PUB';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfpattb.pls';
10 --
11 g_number CONSTANT NUMBER := 1; -- data type is number
12 g_varchar2 CONSTANT NUMBER := 2; -- data type is varchar2
13 --
14 G_USED_BY_ITEM CONSTANT VARCHAR2(30) := 'ITEM';
15 G_MES_APPL_ID CONSTANT NUMBER := 520;
16 G_ISTORE_APPL_ID CONSTANT NUMBER := 671;
17 --
18 --------------------------------------------------------------------------
19 ------------------------- Private Procedure ------------------------------
20 -- PROCEDURE
21 -- check_uniqueness
22 FUNCTION check_uniqueness(
23 p_table_name IN VARCHAR2,
24 p_where_clause IN VARCHAR2
25 ) RETURN VARCHAR2 AS
26 l_sql VARCHAR2(4000);
27 l_count NUMBER;
28 BEGIN
29
30 l_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
31 l_sql := l_sql || ' WHERE ' || p_where_clause;
32
33 EXECUTE IMMEDIATE l_sql INTO l_count;
34
35 IF l_count = 0 THEN
36 RETURN FND_API.g_true;
37 ELSE
38 RETURN FND_API.g_false;
39 END IF;
40
41 END check_uniqueness;
42 --------------------------------------------------------------------------
43 FUNCTION check_fk_exists(
44 p_table_name IN VARCHAR2,
45 p_pk_name IN VARCHAR2,
46 p_pk_value IN VARCHAR2,
47 p_pk_data_type IN NUMBER := g_number,
48 p_additional_where_clause IN VARCHAR2 := NULL
49 ) RETURN VARCHAR2 AS
50 l_sql VARCHAR2(4000);
51 l_count NUMBER;
52 BEGIN
53 l_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
54 l_sql := l_sql || ' WHERE ' || p_pk_name || ' = ';
55
56 IF p_PK_data_type = g_varchar2 THEN
57 l_sql := l_sql || '''' || p_pk_value || '''';
58 ELSE
59 l_sql := l_sql || p_pk_value;
60 END IF;
61
62 IF p_additional_where_clause IS NOT NULL THEN
63 l_sql := l_sql || ' AND ' || p_additional_where_clause;
64 END IF;
65
66 EXECUTE IMMEDIATE l_sql INTO l_count;
67 IF l_count = 0 THEN
68 RETURN FND_API.g_false;
69 ELSE
70 RETURN FND_API.g_true;
71 END IF;
72 END check_fk_exists;
73 ---------------------------------------------------------------------
74 FUNCTION check_lookup_exists(
75 p_lookup_table_name IN VARCHAR2,
76 p_lookup_type IN VARCHAR2,
77 p_lookup_code IN VARCHAR2
78 ) Return VARCHAR2 AS
79 l_sql VARCHAR2(2000);
80 l_count NUMBER;
81 BEGIN
82 l_sql := 'SELECT COUNT(*) FROM ' || p_lookup_table_name;
83 l_sql := l_sql || ' WHERE lookup_type = ''' || p_lookup_type ||'''';
84 l_sql := l_sql || ' AND lookup_code = ''' || p_lookup_code ||'''';
85 l_sql := l_sql || ' AND enabled_flag = ''Y''';
86
87 EXECUTE IMMEDIATE l_sql INTO l_count;
88
89 IF l_count = 0 THEN
90 RETURN FND_API.g_false;
91 ELSE
92 RETURN FND_API.g_true;
93 END IF;
94
95 END check_lookup_exists;
96 ---------------------------------------------------------------------
97 FUNCTION is_Y_or_N(
98
99 p_value IN VARCHAR2
100 ) RETURN VARCHAR2 AS
101 BEGIN
102 IF p_value = 'Y' or p_value = 'N' THEN
103 RETURN FND_API.g_true;
104 ELSE
105 RETURN FND_API.g_false;
106 END IF;
107 END is_Y_or_N;
108 --------------------------------------------------------------------------
109 -- PROCEDURE
110 -- check_act_attachment_req_items
111 --
112 -- HISTORY
113 -- 10/11/99 khung Create.
114 ---------------------------------------------------------------------
115 PROCEDURE check_act_attachment_req_items
116 (
117 p_act_attachment_rec IN act_attachment_rec_type,
118 x_return_status OUT NOCOPY VARCHAR2
119 ) AS
120 BEGIN
121 x_return_status := FND_API.g_ret_sts_success;
122
123 -------------------- put required items here ---------------------
124
125 --IF p_act_attachment_rec.xxx IS NULL THEN
126 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
127 -- FND_MESSAGE.set_name('JTF', 'JTF_AMV_API_RECORD_NOT_FOUND');
128 -- FND_MSG_PUB.add;
129 -- END IF;
130
131 -- x_return_status := FND_API.g_ret_sts_error;
132 -- RETURN;
133 --END IF;
134
135 END check_act_attachment_req_items;
136
137 ---------------------------------------------------------------------
138 -- PROCEDURE
139 -- check_act_attachment_uk_items
140 --
141 -- HISTORY
142 -- 10/11/99 khung Create.
143 ---------------------------------------------------------------------
144
145 PROCEDURE check_act_attachment_uk_items
146 (
147 p_act_attachment_rec IN act_attachment_rec_type,
148 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
149 x_return_status OUT NOCOPY VARCHAR2
150 ) AS
151 l_valid_flag VARCHAR2(1);
152 BEGIN
153
154 x_return_status := FND_API.g_ret_sts_success;
155
156 -- For create_act_attachment, when attachment_id is passed in, we
157 -- need to check if this attachment_id is unique.
158 IF p_validation_mode = JTF_PLSQL_API.g_create AND
159 p_act_attachment_rec.attachment_id IS NOT NULL THEN
160 IF check_uniqueness(
161 'jtf_amv_attachments',
162 'attachment_id = ' || p_act_attachment_rec.attachment_id
163 ) = FND_API.g_false THEN
164 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
165 FND_MESSAGE.set_name('JTF', 'JTF_AMV_ACT_ATTACH_DUPL_ID');
166 FND_MSG_PUB.add;
167 END IF;
168 x_return_status := FND_API.g_ret_sts_error;
169 RETURN;
170 END IF;
171 END IF;
172
173 -- check other unique items
174
175 END check_act_attachment_uk_items;
176
177 ---------------------------------------------------------------------
178 -- PROCEDURE
179 -- check_act_attachment_fk_items
180 --
181 -- HISTORY
182 -- 10/11/99 khung Create.
183 ---------------------------------------------------------------------
184 PROCEDURE check_act_attachment_fk_items
185 (
186 p_act_attachment_rec IN act_attachment_rec_type,
187 x_return_status OUT NOCOPY VARCHAR2
188 ) AS
189 BEGIN
190
191 x_return_status := FND_API.g_ret_sts_success;
192
193 /*
194 ----------------------- status_code ------------------------
195 IF p_act_attachment_rec.xxx <> FND_API.g_miss_num THEN
196 IF check_fk_exists(
197 'ams_statuses_vl',
198 'status_code',
199 'p_act_attachment_rec.xxx'
200 ) = FND_API.g_false
201 THEN
202 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
203 THEN
204 FND_MESSAGE.set_name('JTF', 'JTF_AMV_ACT_ATTACH_BAD_XXX');
205 FND_MSG_PUB.add;
206 END IF;
207
208 x_return_status := FND_API.g_ret_sts_error;
209 RETURN;
210 END IF;
211 END IF;
212 */
213 -- check other fk items
214
215 END check_act_attachment_fk_items;
216
217 ---------------------------------------------------------------------
218 -- PROCEDURE
219 -- check_act_attachment_lk_items (lookup)
220 --
221 -- HISTORY
222 -- 10/11/99 khung Create.
223 ---------------------------------------------------------------------
224 PROCEDURE check_act_attachment_lk_items
225 (
226 p_act_attachment_rec IN act_attachment_rec_type,
227 x_return_status OUT NOCOPY VARCHAR2
228 ) AS
229 BEGIN
230
231 x_return_status := FND_API.g_ret_sts_success;
232
233 ----------------------- status_code ------------------------
234
235 -- check other lookup codes
236
237 END check_act_attachment_lk_items;
238
239
240 ---------------------------------------------------------------------
241 -- PROCEDURE
242 -- check_act_attachment_fg_items (flag)
243 --
244 -- HISTORY
245 -- 10/11/99 khung Create.
246 ---------------------------------------------------------------------
247 PROCEDURE check_act_attachment_fg_items
248 (
249 p_act_attachment_rec IN act_attachment_rec_type,
250 x_return_status OUT NOCOPY VARCHAR2
251 )
252 IS
253 BEGIN
254
255 x_return_status := FND_API.g_ret_sts_success;
256
257 ----------------------- enabled_flag ------------------------
258 IF p_act_attachment_rec.enabled_flag <> FND_API.g_miss_char
259 AND p_act_attachment_rec.enabled_flag IS NOT NULL
260 THEN
261 IF is_Y_or_N(p_act_attachment_rec.enabled_flag) = FND_API.g_false THEN
262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
263 THEN
264 FND_MESSAGE.set_name('JTF', 'JTF_AMV_ACT_ATTACH_BAD_FLAG');
265 FND_MSG_PUB.add;
266 END IF;
267 x_return_status := FND_API.g_ret_sts_error;
268 RETURN;
269 END IF;
270 END IF;
271
272 ----------------------- can_fulfill_electronic_flag ------------------------
273 IF p_act_attachment_rec.can_fulfill_electronic_flag <> FND_API.g_miss_char
274 AND p_act_attachment_rec.can_fulfill_electronic_flag IS NOT NULL
275 THEN
276 IF is_Y_or_N(p_act_attachment_rec.can_fulfill_electronic_flag)
277 = FND_API.g_false THEN
278 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
279 FND_MESSAGE.set_name('JTF', 'JTF_AMV_BAD_CAN_FUL_ELEC');
280 FND_MSG_PUB.add;
281 END IF;
282 x_return_status := FND_API.g_ret_sts_error;
283 RETURN;
284 END IF;
285 END IF;
286 END check_act_attachment_fg_items;
287 --------------------------------------------------------------------------
288 --------------------------------------------------------------------------
289 -- PROCEDURE
290 -- create_act_attachment
291 --
292 -- HISTORY
293 -- 10/09/99 khung Create.
294 -- 06/20/00 rmajumda Modified the Insert statment to accomodate
295 -- new columns added display_text,alternate_text
296 -- and attachment_sub_type
297 ---------------------------------------------------------------------
298
299 PROCEDURE create_act_attachment
300 (
301 p_api_version IN NUMBER,
302 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
303 p_commit IN VARCHAR2 := FND_API.g_false,
304 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
305
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2,
309
310 p_act_attachment_rec IN act_attachment_rec_type,
311 x_act_attachment_id OUT NOCOPY NUMBER
312 ) AS
313
314 l_api_version CONSTANT NUMBER := 1.0;
315 l_api_name CONSTANT VARCHAR2(30) := 'create_act_attachment';
316 --
317 l_return_status VARCHAR2(1);
318 l_act_attachment_rec act_attachment_rec_type := p_act_attachment_rec;
319 l_act_attachment_count NUMBER;
320
321 CURSOR c_act_attachment_seq IS
322 SELECT jtf_amv_attachments_s.NEXTVAL
323 FROM DUAL;
324
325 CURSOR c_act_attachment_count(act_attachment_id IN NUMBER) IS
326 SELECT COUNT(*)
327 FROM jtf_amv_attachments
328 WHERE attachment_id = act_attachment_id;
329 --
330 CURSOR c_get_deli_type_code(p_item_id IN NUMBER) IS
331 SELECT deliverable_type_code
332 FROM jtf_amv_items_b
333 WHERE item_id = p_item_id;
334 l_deli_type_code VARCHAR2(40);
335 --
336 BEGIN
337 --------------------- initialize -----------------------
338 SAVEPOINT create_act_attachment;
339 IF FND_API.to_boolean(p_init_msg_list) THEN
340 FND_MSG_PUB.initialize;
341 END IF;
342 IF NOT FND_API.compatible_api_call
343 (
344 l_api_version,
345 p_api_version,
346 l_api_name,
347 g_pkg_name
348 ) THEN
349 RAISE FND_API.g_exc_unexpected_error;
350 END IF;
351 x_return_status := FND_API.g_ret_sts_success;
352
353 ----------------------- validate -----------------------
354 validate_act_attachment
355 (
356 p_api_version => l_api_version,
357 p_init_msg_list => FND_API.G_FALSE,
358 p_validation_level => p_validation_level,
359 x_return_status => l_return_status,
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data,
362 p_act_attachment_rec => l_act_attachment_rec
363 );
364
365 IF l_return_status = FND_API.g_ret_sts_error THEN
366 RAISE FND_API.g_exc_error;
367 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
368 RAISE FND_API.g_exc_unexpected_error;
369 END IF;
370 -------------------------- insert --------------------------
371 IF l_act_attachment_rec.attachment_id IS NULL THEN
372 LOOP
373 OPEN c_act_attachment_seq;
374 FETCH c_act_attachment_seq INTO l_act_attachment_rec.attachment_id;
375 CLOSE c_act_attachment_seq;
376
377 OPEN c_act_attachment_count(l_act_attachment_rec.attachment_id);
378 FETCH c_act_attachment_count INTO l_act_attachment_count;
379 CLOSE c_act_attachment_count;
380
381 EXIT WHEN l_act_attachment_count = 0;
382 END LOOP;
383 END IF;
384 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM) THEN
385 OPEN c_get_deli_type_code(l_act_attachment_rec.attachment_used_by_id);
386 FETCH c_get_deli_type_code INTO l_deli_type_code;
387 IF (c_get_deli_type_code%NOTFOUND) THEN
388 CLOSE c_get_deli_type_code;
389 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
390 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
391 FND_MESSAGE.Set_Token('ID',
392 to_char(nvl(l_act_attachment_rec.attachment_used_by_id,-1)));
393 FND_MSG_PUB.Add;
394 END IF;
395 RAISE FND_API.G_EXC_ERROR;
396 END IF;
397 CLOSE c_get_deli_type_code;
398 END IF;
399 -- Istore specific stuff.
400 IF (l_act_attachment_rec.application_id = G_ISTORE_APPL_ID) THEN
401 IF l_act_attachment_rec.file_name is null THEN
402 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
403 FND_MESSAGE.Set_name('JTF','JTF_AMV_FILENAME_NULL');
404 FND_MSG_PUB.Add;
405 END IF;
406 RAISE FND_API.G_EXC_ERROR;
407 END IF;
408 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM AND
409 l_act_attachment_rec.display_url is null AND
410 l_deli_type_code = 'MEDIA') THEN
411 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
412 FND_MESSAGE.Set_name('JTF','JTF_AMV_DISPLAY_RUL_NULL');
413 FND_MSG_PUB.Add;
414 END IF;
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417 END IF;
418 INSERT INTO jtf_amv_attachments
419 (
420 attachment_id,
421 last_update_date,
422 last_updated_by,
423 creation_date,
424 created_by,
425 last_update_login,
426 object_version_number,
427 owner_user_id,
428 attachment_used_by_id,
429 attachment_used_by,
430 version,
431 enabled_flag,
432 can_fulfill_electronic_flag,
433 file_id,
434 file_name,
435 file_extension,
439 display_height,
436 document_id,
437 keywords,
438 display_width,
440 display_location,
441 link_to,
442 link_URL,
443 send_for_preview_flag,
444 attachment_type,
445 language_code,
446 application_id,
447 description,
448 default_style_sheet,
449 display_url,
450 display_rule_id,
451 display_program,
452 attribute_category,
453 attribute1,
454 attribute2,
455 attribute3,
456 attribute4,
457 attribute5,
458 attribute6,
459 attribute7,
460 attribute8,
461 attribute9,
462 attribute10,
463 attribute11,
464 attribute12,
465 attribute13,
466 attribute14,
467 attribute15,
468 display_text,
469 alternate_text,
470 secured_flag,
471 attachment_sub_type
472 )
473 VALUES (
474 l_act_attachment_rec.attachment_id,
475 SYSDATE,
476 FND_GLOBAL.user_id,
477 SYSDATE,
478 FND_GLOBAL.user_id,
479 FND_GLOBAL.conc_login_id,
480 1, -- object_version_number
481 l_act_attachment_rec.owner_user_id,
482 l_act_attachment_rec.attachment_used_by_id,
483 l_act_attachment_rec.attachment_used_by,
484 l_act_attachment_rec.version,
485 NVL(l_act_attachment_rec.enabled_flag, 'Y'),
486 NVL(l_act_attachment_rec.can_fulfill_electronic_flag, 'N'),
487 l_act_attachment_rec.file_id,
488 l_act_attachment_rec.file_name,
489 l_act_attachment_rec.file_extension,
490 l_act_attachment_rec.document_id,
491 l_act_attachment_rec.keywords,
492 l_act_attachment_rec.display_width,
493 l_act_attachment_rec.display_height,
494 l_act_attachment_rec.display_location,
495 l_act_attachment_rec.link_to,
496 l_act_attachment_rec.link_URL,
497 l_act_attachment_rec.send_for_preview_flag,
498 l_act_attachment_rec.attachment_type,
499 l_act_attachment_rec.language_code,
500 l_act_attachment_rec.application_id,
501 l_act_attachment_rec.description,
502 l_act_attachment_rec.default_style_sheet,
503 l_act_attachment_rec.display_url,
504 l_act_attachment_rec.display_rule_id,
505 l_act_attachment_rec.display_program,
506 l_act_attachment_rec.attribute_category,
507 l_act_attachment_rec.attribute1,
508 l_act_attachment_rec.attribute2,
509 l_act_attachment_rec.attribute3,
510 l_act_attachment_rec.attribute4,
511 l_act_attachment_rec.attribute5,
512 l_act_attachment_rec.attribute6,
513 l_act_attachment_rec.attribute7,
514 l_act_attachment_rec.attribute8,
515 l_act_attachment_rec.attribute9,
516 l_act_attachment_rec.attribute10,
517 l_act_attachment_rec.attribute11,
518 l_act_attachment_rec.attribute12,
519 l_act_attachment_rec.attribute13,
520 l_act_attachment_rec.attribute14,
521 l_act_attachment_rec.attribute15,
522 l_act_attachment_rec.display_text,
523 l_act_attachment_rec.alternate_text,
524 l_act_attachment_rec.secured_flag,
525 l_act_attachment_rec.attachment_sub_type
526 );
527 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM) THEN
528 update jtf_amv_items_b
529 set last_update_date = sysdate,
530 last_updated_by = FND_GLOBAL.user_id,
531 last_update_login = FND_GLOBAL.conc_login_id
532 where item_id = l_act_attachment_rec.attachment_used_by_id ;
533 END IF;
534 ------------------------- finish -------------------------------
535 x_act_attachment_id := l_act_attachment_rec.attachment_id;
536 IF FND_API.to_boolean(p_commit) THEN
537 COMMIT;
538 END IF;
539 FND_MSG_PUB.count_and_get
540 (
541 p_encoded => FND_API.g_false,
542 p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545 EXCEPTION
546 WHEN FND_API.g_exc_error THEN
547 ROLLBACK TO create_act_attachment;
548 x_return_status := FND_API.g_ret_sts_error;
549 FND_MSG_PUB.count_and_get(
550 p_encoded => FND_API.g_false,
551 p_count => x_msg_count,
552 p_data => x_msg_data
553 );
554
555 WHEN FND_API.g_exc_unexpected_error THEN
556 ROLLBACK TO create_act_attachment;
557 x_return_status := FND_API.g_ret_sts_unexp_error ;
558 FND_MSG_PUB.count_and_get(
559 p_encoded => FND_API.g_false,
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563
564 WHEN OTHERS THEN
565 ROLLBACK TO create_act_attachment;
566 x_return_status := FND_API.g_ret_sts_unexp_error ;
567 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
568 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
569 END IF;
570 FND_MSG_PUB.count_and_get(
571 p_encoded => FND_API.g_false,
572 p_count => x_msg_count,
573 p_data => x_msg_data
574 );
575 END create_act_attachment;
576
580 --
577 --------------------------------------------------------------------
578 -- PROCEDURE
579 -- delete_act_attachment
581 -- HISTORY
582 -- 10/09/99 khung Create.
583 --------------------------------------------------------------------
584 PROCEDURE delete_act_attachment
585 (
586 p_api_version IN NUMBER,
587 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
588 p_commit IN VARCHAR2 := FND_API.g_false,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_act_attachment_id IN NUMBER,
593 p_object_version IN NUMBER
594 ) AS
595 l_api_version CONSTANT NUMBER := 1.0;
596 l_api_name CONSTANT VARCHAR2(30) := 'delete_act_attachment';
597 BEGIN
598
599 --------------------- initialize -----------------------
600 SAVEPOINT delete_act_attachment;
601 IF FND_API.to_boolean(p_init_msg_list) THEN
602 FND_MSG_PUB.initialize;
603 END IF;
604 IF NOT FND_API.compatible_api_call(
605 l_api_version,
606 p_api_version,
607 l_api_name,
608 g_pkg_name
609 ) THEN
610 RAISE FND_API.g_exc_unexpected_error;
611 END IF;
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 ------------------------ delete ------------------------
615 DELETE FROM jtf_amv_attachments
616 WHERE attachment_id = p_act_attachment_id
617 AND object_version_number = p_object_version;
618
619 IF (SQL%NOTFOUND) THEN
620 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
621 FND_MESSAGE.set_name('JTF', 'JTF_AMV_API_RECORD_NOT_FOUND');
622 FND_MSG_PUB.add;
623 END IF;
624 RAISE FND_API.g_exc_error;
625 END IF;
626
627 -------------------- finish --------------------------
628 IF FND_API.to_boolean(p_commit) THEN
629 COMMIT;
630 END IF;
631 FND_MSG_PUB.count_and_get(
632 p_encoded => FND_API.g_false,
633 p_count => x_msg_count,
634 p_data => x_msg_data
635 );
636
637 EXCEPTION
638
639 WHEN FND_API.g_exc_error THEN
640 ROLLBACK TO delete_act_attachment;
641 x_return_status := FND_API.g_ret_sts_error;
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 WHEN FND_API.g_exc_unexpected_error THEN
649 ROLLBACK TO delete_act_attachment;
650 x_return_status := FND_API.g_ret_sts_unexp_error ;
651 FND_MSG_PUB.count_and_get(
652 p_encoded => FND_API.g_false,
653 p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656
657 WHEN OTHERS THEN
658 ROLLBACK TO delete_act_attachment;
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 END delete_act_attachment;
671
672 ---------------------------------------------------------------------
673 -- PROCEDURE
674 -- update_act_attachment
675 --
676 -- HISTORY
677 -- 10/09/99 khung Create.
678 -- 06/20/00 rmajumda Modified the Update statement
679 ----------------------------------------------------------------------
680
681 PROCEDURE update_act_attachment
682 (
683 p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
685 p_commit IN VARCHAR2 := FND_API.g_false,
686 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
687
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2,
691
692 p_act_attachment_rec IN act_attachment_rec_type
693 ) AS
694 l_api_version CONSTANT NUMBER := 1.0;
695 l_api_name CONSTANT VARCHAR2(30) := 'update_act_attachment';
696 l_act_attachment_rec act_attachment_rec_type;
697 l_return_status VARCHAR2(1);
698 --
699 CURSOR c_get_deli_type_code(p_item_id IN NUMBER) IS
700 SELECT deliverable_type_code
701 FROM jtf_amv_items_b
702 WHERE item_id = p_item_id;
703 l_deli_type_code VARCHAR2(40);
704 --
705 BEGIN
706 -------------------- initialize -------------------------
707 SAVEPOINT update_act_attachment;
708 IF FND_API.to_boolean(p_init_msg_list) THEN
709 FND_MSG_PUB.initialize;
710 END IF;
711 IF NOT FND_API.compatible_api_call
712 (
713 l_api_version,
714 p_api_version,
715 l_api_name,
716 g_pkg_name
717 ) THEN
718 RAISE FND_API.g_exc_unexpected_error;
719 END IF;
720 x_return_status := FND_API.G_RET_STS_SUCCESS;
721
722 ----------------------- validate ----------------------
726 p_act_attachment_rec => p_act_attachment_rec,
723 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
724 check_act_attachment_items
725 (
727 p_validation_mode => JTF_PLSQL_API.g_update,
728 x_return_status => l_return_status
729 );
730
731 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
732 RAISE FND_API.g_exc_unexpected_error;
733 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
734 RAISE FND_API.g_exc_error;
735 END IF;
736 END IF;
737
738 -- replace g_miss_char/num/date with current column values
739 complete_act_attachment_rec(p_act_attachment_rec, l_act_attachment_rec);
740
741 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
742 check_act_attachment_record
743 (
744 p_act_attachment_rec => p_act_attachment_rec,
745 p_complete_rec => l_act_attachment_rec,
746 x_return_status => l_return_status
747 );
748
749 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
750 RAISE FND_API.g_exc_unexpected_error;
751 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
752 RAISE FND_API.g_exc_error;
753 END IF;
754 END IF;
755 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM) THEN
756 OPEN c_get_deli_type_code(l_act_attachment_rec.attachment_used_by_id);
757 FETCH c_get_deli_type_code INTO l_deli_type_code;
758 IF (c_get_deli_type_code%NOTFOUND) THEN
759 CLOSE c_get_deli_type_code;
760 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
761 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
762 FND_MESSAGE.Set_Token('ID',
763 to_char(nvl(l_act_attachment_rec.attachment_used_by_id,-1)));
764 FND_MSG_PUB.Add;
765 END IF;
766 RAISE FND_API.G_EXC_ERROR;
767 END IF;
768 CLOSE c_get_deli_type_code;
769 END IF;
770 IF (l_act_attachment_rec.application_id = G_ISTORE_APPL_ID) THEN
771 IF l_act_attachment_rec.file_name is null THEN
772 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
773 FND_MESSAGE.Set_name('JTF','JTF_AMV_FILENAME_NULL');
774 FND_MSG_PUB.Add;
775 END IF;
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM AND
779 l_act_attachment_rec.display_url is null AND
780 l_deli_type_code = 'MEDIA') THEN
781 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
782 FND_MESSAGE.Set_name('JTF','JTF_AMV_DISPLAY_RUL_NULL');
783 FND_MSG_PUB.Add;
784 END IF;
785 RAISE FND_API.G_EXC_ERROR;
786 END IF;
787 END IF;
788 -------------------------- update --------------------
789 UPDATE jtf_amv_attachments SET
790 last_update_date = SYSDATE,
791 last_updated_by = FND_GLOBAL.user_id,
792 last_update_login = FND_GLOBAL.conc_login_id,
793 object_version_number = l_act_attachment_rec.object_version_number + 1,
794 owner_user_id = l_act_attachment_rec.owner_user_id,
795 attachment_used_by_id = l_act_attachment_rec.attachment_used_by_id,
796 attachment_used_by = l_act_attachment_rec.attachment_used_by,
797 version = l_act_attachment_rec.version,
798 enabled_flag = l_act_attachment_rec.enabled_flag,
799 can_fulfill_electronic_flag =
800 l_act_attachment_rec.can_fulfill_electronic_flag,
801 file_id = l_act_attachment_rec.file_id,
802 file_name = l_act_attachment_rec.file_name,
803 file_extension = l_act_attachment_rec.file_extension,
804 document_id = l_act_attachment_rec.document_id,
805 keywords = l_act_attachment_rec.keywords,
806 display_width = l_act_attachment_rec.display_width,
807 display_height = l_act_attachment_rec.display_height,
808 display_location = l_act_attachment_rec.display_location,
809 link_to = l_act_attachment_rec.link_to,
810 link_url = l_act_attachment_rec.link_url,
811 send_for_preview_flag = l_act_attachment_rec.send_for_preview_flag,
812 attachment_type = l_act_attachment_rec.attachment_type,
813 language_code = l_act_attachment_rec.language_code,
814 application_id = l_act_attachment_rec.application_id,
815 description = l_act_attachment_rec.description,
816 default_style_sheet = l_act_attachment_rec.default_style_sheet,
817 display_rule_id = l_act_attachment_rec.display_rule_id,
818 display_url = l_act_attachment_rec.display_url,
819 display_program = l_act_attachment_rec.display_program,
820 attribute_category = l_act_attachment_rec.attribute_category,
821 attribute1 = l_act_attachment_rec.attribute1,
822 attribute2 = l_act_attachment_rec.attribute2,
823 attribute3 = l_act_attachment_rec.attribute3,
824 attribute4 = l_act_attachment_rec.attribute4,
825 attribute5 = l_act_attachment_rec.attribute5,
826 attribute6 = l_act_attachment_rec.attribute6,
827 attribute7 = l_act_attachment_rec.attribute7,
828 attribute8 = l_act_attachment_rec.attribute8,
829 attribute9 = l_act_attachment_rec.attribute9,
830 attribute10 = l_act_attachment_rec.attribute10,
831 attribute11 = l_act_attachment_rec.attribute11,
832 attribute12 = l_act_attachment_rec.attribute12,
836 display_text = l_act_attachment_rec.display_text,
833 attribute13 = l_act_attachment_rec.attribute13,
834 attribute14 = l_act_attachment_rec.attribute14,
835 attribute15 = l_act_attachment_rec.attribute15,
837 alternate_text = l_act_attachment_rec.alternate_text,
838 secured_flag = l_act_attachment_rec.secured_flag,
839 attachment_sub_type = l_act_attachment_rec.attachment_sub_type
840 WHERE attachment_id = l_act_attachment_rec.attachment_id
841 AND object_version_number = l_act_attachment_rec.object_version_number;
842
843 IF (SQL%NOTFOUND) THEN
844 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
845 FND_MESSAGE.set_name('AMS', 'JTF_AMV_API_RECORD_NOT_FOUND');
846 FND_MSG_PUB.add;
847 END IF;
848 RAISE FND_API.g_exc_error;
849 END IF;
850
851 IF (l_act_attachment_rec.attachment_used_by = G_USED_BY_ITEM) THEN
852 update jtf_amv_items_b
853 set last_update_date = sysdate,
854 last_updated_by = FND_GLOBAL.user_id,
855 last_update_login = FND_GLOBAL.conc_login_id
856 where item_id = l_act_attachment_rec.attachment_used_by_id;
857 END IF;
858 -------------------- finish --------------------------
859 IF FND_API.to_boolean(p_commit) THEN
860 COMMIT;
861 END IF;
862
863 FND_MSG_PUB.count_and_get
864 (
865 p_encoded => FND_API.g_false,
866 p_count => x_msg_count,
867 p_data => x_msg_data
868 );
869
870 EXCEPTION
871
872 WHEN FND_API.g_exc_error THEN
873 ROLLBACK TO update_act_attachment;
874 x_return_status := FND_API.g_ret_sts_error;
875 FND_MSG_PUB.count_and_get
876 (
877 p_encoded => FND_API.g_false,
878 p_count => x_msg_count,
879 p_data => x_msg_data
880 );
881
882 WHEN FND_API.g_exc_unexpected_error THEN
883 ROLLBACK TO update_act_attachment;
884 x_return_status := FND_API.g_ret_sts_unexp_error ;
885 FND_MSG_PUB.count_and_get
886 (
887 p_encoded => FND_API.g_false,
888 p_count => x_msg_count,
889 p_data => x_msg_data
890 );
891
892 WHEN OTHERS THEN
893 ROLLBACK TO update_act_attachment;
894 x_return_status := FND_API.g_ret_sts_unexp_error ;
895 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
896 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
897 END IF;
898 FND_MSG_PUB.count_and_get
899 (
900 p_encoded => FND_API.g_false,
901 p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904 END update_act_attachment;
905 -------------------------------------------------------------------
906 -- PROCEDURE
907 -- lock_act_attachment
908 --
909 -- HISTORY
910 -- 10/09/99 khung Create.
911 --------------------------------------------------------------------
912 PROCEDURE lock_act_attachment
913 (
914 p_api_version IN NUMBER,
915 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
916
917 x_return_status OUT NOCOPY VARCHAR2,
918 x_msg_count OUT NOCOPY NUMBER,
919 x_msg_data OUT NOCOPY VARCHAR2,
920
921 p_act_attachment_id IN NUMBER,
922 p_object_version IN NUMBER
923 ) AS
924 l_api_version CONSTANT NUMBER := 1.0;
925 l_api_name CONSTANT VARCHAR2(30) := 'lock_act_attachment';
926
927 l_act_attachment_id NUMBER;
928
929 CURSOR c_act_attachment IS
930 SELECT attachment_id
931 FROM jtf_amv_attachments
932 WHERE attachment_id = p_act_attachment_id
933 AND object_version_number = p_object_version
934 FOR UPDATE OF attachment_id NOWAIT;
935
936 BEGIN
937 -------------------- initialize ------------------------
938 IF FND_API.to_boolean(p_init_msg_list) THEN
939 FND_MSG_PUB.initialize;
940 END IF;
941
942 IF NOT FND_API.compatible_api_call(
943 l_api_version,
944 p_api_version,
945 l_api_name,
946 g_pkg_name
947 ) THEN
948 RAISE FND_API.g_exc_unexpected_error;
949 END IF;
950
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952
953 ------------------------ lock -------------------------
954 OPEN c_act_attachment;
955 FETCH c_act_attachment INTO l_act_attachment_id;
956 IF (c_act_attachment%NOTFOUND) THEN
957 CLOSE c_act_attachment;
958 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
959 FND_MESSAGE.set_name('AMS', 'JTF_AMV_API_RECORD_NOT_FOUND');
960 FND_MSG_PUB.add;
961 END IF;
962 RAISE FND_API.g_exc_error;
963 END IF;
964 CLOSE c_act_attachment;
965
966 -------------------- finish --------------------------
967 FND_MSG_PUB.count_and_get
968 (
969 p_encoded => FND_API.g_false,
970 p_count => x_msg_count,
971 p_data => x_msg_data
972 );
973
974 EXCEPTION
975 WHEN FND_API.g_exc_unexpected_error THEN
976 x_return_status := FND_API.g_ret_sts_unexp_error ;
980 p_data => x_msg_data
977 FND_MSG_PUB.count_and_get(
978 p_encoded => FND_API.g_false,
979 p_count => x_msg_count,
981 );
982
983 WHEN OTHERS THEN
984 x_return_status := FND_API.g_ret_sts_unexp_error ;
985 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
986 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
987 END IF;
988
989 FND_MSG_PUB.count_and_get(
990 p_encoded => FND_API.g_false,
991 p_count => x_msg_count,
992 p_data => x_msg_data
993 );
994
995 END lock_act_attachment;
996 ---------------------------------------------------------------------
997 -- PROCEDURE
998 -- validate_act_attachment
999 --
1000 -- HISTORY
1001 -- 10/09/99 khung Create.
1002 ----------------------------------------------------------------------
1003 PROCEDURE validate_act_attachment
1004 (
1005 p_api_version IN NUMBER,
1006 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1007 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1008
1009 x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2,
1012
1013 p_act_attachment_rec IN act_attachment_rec_type
1014 ) AS
1015 l_api_version CONSTANT NUMBER := 1.0;
1016 l_api_name CONSTANT VARCHAR2(30) := 'validate_act_attachment';
1017 l_return_status VARCHAR2(1);
1018
1019 BEGIN
1020 ----------------------- initialize --------------------
1021 IF FND_API.to_boolean(p_init_msg_list) THEN
1022 FND_MSG_PUB.initialize;
1023 END IF;
1024 IF NOT FND_API.compatible_api_call(
1025 l_api_version,
1026 p_api_version,
1027 l_api_name,
1028 g_pkg_name
1029 ) THEN
1030 RAISE FND_API.g_exc_unexpected_error;
1031 END IF;
1032 x_return_status := FND_API.g_ret_sts_success;
1033
1034 ---------------------- validate ------------------------
1035 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1036 check_act_attachment_items
1037 (
1038 p_act_attachment_rec => p_act_attachment_rec,
1039 p_validation_mode => JTF_PLSQL_API.g_create,
1040 x_return_status => l_return_status
1041 );
1042 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1043 RAISE FND_API.g_exc_unexpected_error;
1044 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1045 RAISE FND_API.g_exc_error;
1046 END IF;
1047 END IF;
1048
1049 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1050 check_act_attachment_record
1051 (
1052 p_act_attachment_rec => p_act_attachment_rec,
1053 p_complete_rec => NULL,
1054 x_return_status => l_return_status
1055 );
1056
1057 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1058 RAISE FND_API.g_exc_unexpected_error;
1059 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1060 RAISE FND_API.g_exc_error;
1061 END IF;
1062 END IF;
1063
1064 -------------------- finish --------------------------
1065 FND_MSG_PUB.count_and_get
1066 (
1067 p_encoded => FND_API.g_false,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071
1072 EXCEPTION
1073
1074 WHEN FND_API.g_exc_error THEN
1075 x_return_status := FND_API.g_ret_sts_error;
1076 FND_MSG_PUB.count_and_get(
1077 p_encoded => FND_API.g_false,
1078 p_count => x_msg_count,
1079 p_data => x_msg_data
1080 );
1081
1082 WHEN FND_API.g_exc_unexpected_error THEN
1083 x_return_status := FND_API.g_ret_sts_unexp_error ;
1084 FND_MSG_PUB.count_and_get(
1085 p_encoded => FND_API.g_false,
1086 p_count => x_msg_count,
1087 p_data => x_msg_data
1088 );
1089
1090 WHEN OTHERS THEN
1091 x_return_status := FND_API.g_ret_sts_unexp_error;
1092 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1093 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1094 END IF;
1095
1096 FND_MSG_PUB.count_and_get(
1097 p_encoded => FND_API.g_false,
1098 p_count => x_msg_count,
1099 p_data => x_msg_data
1100 );
1101 END validate_act_attachment;
1102
1103 ---------------------------------------------------------------------
1104 -- PROCEDURE
1105 -- check_act_attachment_items
1106 --
1107 -- HISTORY
1108 -- 10/09/99 khung Create.
1109 ---------------------------------------------------------------------
1110 PROCEDURE check_act_attachment_items
1111 (
1112 p_act_attachment_rec IN act_attachment_rec_type,
1113 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1114 x_return_status OUT NOCOPY VARCHAR2
1115 ) AS
1116 BEGIN
1117
1118 check_act_attachment_req_items
1119 (
1120 p_act_attachment_rec => p_act_attachment_rec,
1124 IF x_return_status <> FND_API.g_ret_sts_success THEN
1121 x_return_status => x_return_status
1122 );
1123
1125 RETURN;
1126 END IF;
1127
1128 check_act_attachment_uk_items
1129 (
1130 p_act_attachment_rec => p_act_attachment_rec,
1131 p_validation_mode => p_validation_mode,
1132 x_return_status => x_return_status
1133 );
1134
1135 IF x_return_status <> FND_API.g_ret_sts_success THEN
1136 RETURN;
1137 END IF;
1138
1139 check_act_attachment_fk_items
1140 (
1141 p_act_attachment_rec => p_act_attachment_rec,
1142 x_return_status => x_return_status
1143 );
1144
1145 IF x_return_status <> FND_API.g_ret_sts_success THEN
1146 RETURN;
1147 END IF;
1148
1149 check_act_attachment_lk_items
1150 (
1151 p_act_attachment_rec => p_act_attachment_rec,
1152 x_return_status => x_return_status
1153 );
1154
1155 IF x_return_status <> FND_API.g_ret_sts_success THEN
1156 RETURN;
1157 END IF;
1158
1159 check_act_attachment_fg_items
1160 (
1161 p_act_attachment_rec => p_act_attachment_rec,
1162 x_return_status => x_return_status
1163 );
1164
1165 IF x_return_status <> FND_API.g_ret_sts_success THEN
1166 RETURN;
1167 END IF;
1168
1169 END check_act_attachment_items;
1170 ---------------------------------------------------------------------
1171 -- PROCEDURE
1172 -- check_act_attachment_record
1173 --
1174 -- HISTORY
1175 -- 10/09/99 khung Create.
1176 ---------------------------------------------------------------------
1177
1178 PROCEDURE check_act_attachment_record
1179 (
1180 p_act_attachment_rec IN act_attachment_rec_type,
1181 p_complete_rec IN act_attachment_rec_type := NULL,
1182 x_return_status OUT NOCOPY VARCHAR2
1183 )
1184 IS
1185 BEGIN
1186 x_return_status := FND_API.g_ret_sts_success;
1187
1188 -- do other record level checkings
1189
1190 END check_act_attachment_record;
1191
1192 ---------------------------------------------------------------------
1193 -- PROCEDURE
1194 -- miss_act_attachment_rec
1195 --
1196 -- PURPOSE
1197 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
1198 -- History
1199 -- 06/20/00 rmajumda Assigning values to three more columns
1200 -- display_text,alternate_text and
1201 -- attachment_sub_type
1202 ---------------------------------------------------------------------
1203
1204 PROCEDURE miss_act_attachment_rec
1205 (
1206 x_act_attachment_rec OUT NOCOPY act_attachment_rec_type
1207 ) AS
1208
1209 BEGIN
1210 x_act_attachment_rec.attachment_id := FND_API.g_miss_num;
1211 x_act_attachment_rec.last_update_date := FND_API.g_miss_date;
1212 x_act_attachment_rec.last_updated_by := FND_API.g_miss_num;
1213 x_act_attachment_rec.creation_date := FND_API.g_miss_date;
1214 x_act_attachment_rec.created_by := FND_API.g_miss_num;
1215 x_act_attachment_rec.last_update_login := FND_API.g_miss_num;
1216 x_act_attachment_rec.object_version_number := FND_API.g_miss_num;
1217 x_act_attachment_rec.owner_user_id := FND_API.g_miss_num;
1218 x_act_attachment_rec.attachment_used_by_id := FND_API.g_miss_num;
1219 x_act_attachment_rec.attachment_used_by := FND_API.g_miss_char;
1220 x_act_attachment_rec.version := FND_API.g_miss_char;
1221 x_act_attachment_rec.enabled_flag := FND_API.g_miss_char;
1222 x_act_attachment_rec.can_fulfill_electronic_flag := FND_API.g_miss_char;
1223 x_act_attachment_rec.file_id := FND_API.g_miss_num;
1224 x_act_attachment_rec.file_name := FND_API.g_miss_char;
1225 x_act_attachment_rec.file_extension := FND_API.g_miss_char;
1226 x_act_attachment_rec.keywords := FND_API.g_miss_char;
1227 x_act_attachment_rec.document_id := FND_API.g_miss_num;
1228 x_act_attachment_rec.display_width := FND_API.g_miss_num;
1229 x_act_attachment_rec.display_height := FND_API.g_miss_num;
1230 x_act_attachment_rec.display_location := FND_API.g_miss_char;
1231 x_act_attachment_rec.link_to := FND_API.g_miss_char;
1232 x_act_attachment_rec.link_url := FND_API.g_miss_char;
1233 x_act_attachment_rec.send_for_preview_flag := FND_API.g_miss_char;
1234 x_act_attachment_rec.attachment_type := FND_API.g_miss_char;
1235 x_act_attachment_rec.language_code := FND_API.g_miss_char;
1236 x_act_attachment_rec.application_id := FND_API.g_miss_num;
1237 x_act_attachment_rec.description := FND_API.g_miss_char;
1238 x_act_attachment_rec.default_style_sheet := FND_API.g_miss_char;
1239 x_act_attachment_rec.display_url := FND_API.g_miss_char;
1240 x_act_attachment_rec.display_rule_id := FND_API.g_miss_num;
1241 x_act_attachment_rec.display_program := FND_API.g_miss_char;
1242
1243 x_act_attachment_rec.attribute_category := FND_API.g_miss_char;
1244 x_act_attachment_rec.attribute1 := FND_API.g_miss_char;
1245 x_act_attachment_rec.attribute2 := FND_API.g_miss_char;
1246 x_act_attachment_rec.attribute3 := FND_API.g_miss_char;
1250 x_act_attachment_rec.attribute7 := FND_API.g_miss_char;
1247 x_act_attachment_rec.attribute4 := FND_API.g_miss_char;
1248 x_act_attachment_rec.attribute5 := FND_API.g_miss_char;
1249 x_act_attachment_rec.attribute6 := FND_API.g_miss_char;
1251 x_act_attachment_rec.attribute8 := FND_API.g_miss_char;
1252 x_act_attachment_rec.attribute9 := FND_API.g_miss_char;
1253 x_act_attachment_rec.attribute10 := FND_API.g_miss_char;
1254 x_act_attachment_rec.attribute11 := FND_API.g_miss_char;
1255 x_act_attachment_rec.attribute12 := FND_API.g_miss_char;
1256 x_act_attachment_rec.attribute13 := FND_API.g_miss_char;
1257 x_act_attachment_rec.attribute14 := FND_API.g_miss_char;
1258 x_act_attachment_rec.attribute15 := FND_API.g_miss_char;
1259 x_act_attachment_rec.display_text := FND_API.g_miss_char;
1260 x_act_attachment_rec.alternate_text := FND_API.g_miss_char;
1261 x_act_attachment_rec.secured_flag := FND_API.g_miss_char;
1262 x_act_attachment_rec.attachment_sub_type := FND_API.g_miss_char;
1263 END miss_act_attachment_rec;
1264
1265
1266 ---------------------------------------------------------------------
1267 -- PROCEDURE
1268 -- complete_act_attachment_rec
1269 --
1270 -- HISTORY
1271 -- 10/09/99 khung Create.
1272 ---------------------------------------------------------------------
1273 PROCEDURE complete_act_attachment_rec
1274 (
1275 p_act_attachment_rec IN act_attachment_rec_type,
1276 x_complete_rec OUT NOCOPY act_attachment_rec_type
1277 ) AS
1278 CURSOR c_act_attachment IS
1279 SELECT *
1280 FROM jtf_amv_attachments
1281 WHERE attachment_id = p_act_attachment_rec.attachment_id;
1282
1283 l_act_attachment_rec c_act_attachment%ROWTYPE;
1284
1285 BEGIN
1286
1287 x_complete_rec := p_act_attachment_rec;
1288
1289 OPEN c_act_attachment;
1290 FETCH c_act_attachment INTO l_act_attachment_rec;
1291 IF c_act_attachment%NOTFOUND THEN
1292 CLOSE c_act_attachment;
1293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1294 FND_MESSAGE.set_name('JTF', 'JTF_AMV_API_RECORD_NOT_FOUND');
1295 FND_MSG_PUB.add;
1296 END IF;
1297 RAISE FND_API.g_exc_error;
1298 END IF;
1299 CLOSE c_act_attachment;
1300
1301 IF p_act_attachment_rec.attachment_id = FND_API.g_miss_num THEN
1302 x_complete_rec.attachment_id := l_act_attachment_rec.attachment_id;
1303 END IF;
1304
1305 IF p_act_attachment_rec.owner_user_id = FND_API.g_miss_num THEN
1306 x_complete_rec.owner_user_id := l_act_attachment_rec.owner_user_id;
1307 END IF;
1308
1309 IF p_act_attachment_rec.attachment_used_by_id = FND_API.g_miss_num THEN
1310 x_complete_rec.attachment_used_by_id := l_act_attachment_rec.attachment_used_by_id;
1311 END IF;
1312
1313 IF p_act_attachment_rec.attachment_used_by = FND_API.g_miss_char THEN
1314 x_complete_rec.attachment_used_by := l_act_attachment_rec.attachment_used_by;
1315 END IF;
1316
1317 IF p_act_attachment_rec.version = FND_API.g_miss_char THEN
1318 x_complete_rec.version := l_act_attachment_rec.version;
1319 END IF;
1320
1321 IF p_act_attachment_rec.enabled_flag = FND_API.g_miss_char THEN
1322 x_complete_rec.enabled_flag := l_act_attachment_rec.enabled_flag;
1323 END IF;
1324
1325 IF p_act_attachment_rec.can_fulfill_electronic_flag = FND_API.g_miss_char THEN
1326 x_complete_rec.can_fulfill_electronic_flag := l_act_attachment_rec.can_fulfill_electronic_flag;
1327 END IF;
1328
1329 IF p_act_attachment_rec.file_id = FND_API.g_miss_num THEN
1330 x_complete_rec.file_id := l_act_attachment_rec.file_id;
1331 END IF;
1332
1333 IF p_act_attachment_rec.file_name = FND_API.g_miss_char THEN
1334 x_complete_rec.file_name := l_act_attachment_rec.file_name;
1335 END IF;
1336
1337 IF p_act_attachment_rec.file_extension = FND_API.g_miss_char THEN
1338 x_complete_rec.file_extension := l_act_attachment_rec.file_extension;
1339 END IF;
1340
1341 IF p_act_attachment_rec.document_id = FND_API.g_miss_num THEN
1342 x_complete_rec.document_id := l_act_attachment_rec.document_id;
1343 END IF;
1344
1345 IF p_act_attachment_rec.keywords = FND_API.g_miss_char THEN
1346 x_complete_rec.keywords := l_act_attachment_rec.keywords;
1347 END IF;
1348
1349 IF p_act_attachment_rec.display_width = FND_API.g_miss_num THEN
1350 x_complete_rec.display_width := l_act_attachment_rec.display_width;
1351 END IF;
1352
1353 IF p_act_attachment_rec.display_height = FND_API.g_miss_num THEN
1354 x_complete_rec.display_height := l_act_attachment_rec.display_height;
1355 END IF;
1356
1357 IF p_act_attachment_rec.display_location = FND_API.g_miss_char THEN
1358 x_complete_rec.display_location := l_act_attachment_rec.display_location;
1359 END IF;
1360
1361 IF p_act_attachment_rec.link_to = FND_API.g_miss_char THEN
1362 x_complete_rec.link_to := l_act_attachment_rec.link_to;
1363 END IF;
1364
1365 IF p_act_attachment_rec.link_url = FND_API.g_miss_char THEN
1366 x_complete_rec.link_url := l_act_attachment_rec.link_url;
1367 END IF;
1368
1369 IF p_act_attachment_rec.send_for_preview_flag = FND_API.g_miss_char THEN
1373 IF p_act_attachment_rec.attachment_type = FND_API.g_miss_char THEN
1370 x_complete_rec.send_for_preview_flag := l_act_attachment_rec.send_for_preview_flag;
1371 END IF;
1372
1374 x_complete_rec.attachment_type := l_act_attachment_rec.attachment_type;
1375 END IF;
1376
1377 IF p_act_attachment_rec.language_code = FND_API.g_miss_char THEN
1378 x_complete_rec.language_code := l_act_attachment_rec.language_code;
1379 END IF;
1380
1381 IF p_act_attachment_rec.application_id = FND_API.g_miss_num THEN
1382 x_complete_rec.application_id := l_act_attachment_rec.application_id;
1383 END IF;
1384
1385 IF p_act_attachment_rec.description = FND_API.g_miss_char THEN
1386 x_complete_rec.description := l_act_attachment_rec.description;
1387 END IF;
1388
1389 IF p_act_attachment_rec.default_style_sheet = FND_API.g_miss_char THEN
1390 x_complete_rec.default_style_sheet := l_act_attachment_rec.default_style_sheet;
1391 END IF;
1392
1393 IF p_act_attachment_rec.display_url = FND_API.g_miss_char THEN
1394 x_complete_rec.display_url := l_act_attachment_rec.display_url;
1395 END IF;
1396
1397 IF p_act_attachment_rec.display_rule_id = FND_API.g_miss_num THEN
1398 x_complete_rec.display_rule_id := l_act_attachment_rec.display_rule_id;
1399 END IF;
1400
1401 IF p_act_attachment_rec.display_program = FND_API.g_miss_char THEN
1402 x_complete_rec.display_program := l_act_attachment_rec.display_program;
1403 END IF;
1404
1405 IF p_act_attachment_rec.attribute_category = FND_API.g_miss_char THEN
1406 x_complete_rec.attribute_category := l_act_attachment_rec.attribute_category;
1407 END IF;
1408
1409 IF p_act_attachment_rec.attribute1 = FND_API.g_miss_char THEN
1410 x_complete_rec.attribute1 := l_act_attachment_rec.attribute1;
1411 END IF;
1412
1413 IF p_act_attachment_rec.attribute2 = FND_API.g_miss_char THEN
1414 x_complete_rec.attribute2 := l_act_attachment_rec.attribute2;
1415 END IF;
1416
1417 IF p_act_attachment_rec.attribute3 = FND_API.g_miss_char THEN
1418 x_complete_rec.attribute3 := l_act_attachment_rec.attribute3;
1419 END IF;
1420
1421 IF p_act_attachment_rec.attribute4 = FND_API.g_miss_char THEN
1422 x_complete_rec.attribute4 := l_act_attachment_rec.attribute4;
1423 END IF;
1424
1425 IF p_act_attachment_rec.attribute5 = FND_API.g_miss_char THEN
1426 x_complete_rec.attribute5 := l_act_attachment_rec.attribute5;
1427 END IF;
1428
1429 IF p_act_attachment_rec.attribute6 = FND_API.g_miss_char THEN
1430 x_complete_rec.attribute6 := l_act_attachment_rec.attribute6;
1431 END IF;
1432
1433 IF p_act_attachment_rec.attribute7 = FND_API.g_miss_char THEN
1434 x_complete_rec.attribute7 := l_act_attachment_rec.attribute7;
1435 END IF;
1436
1437 IF p_act_attachment_rec.attribute8 = FND_API.g_miss_char THEN
1438 x_complete_rec.attribute8 := l_act_attachment_rec.attribute8;
1439 END IF;
1440
1441 IF p_act_attachment_rec.attribute9 = FND_API.g_miss_char THEN
1442 x_complete_rec.attribute9 := l_act_attachment_rec.attribute9;
1443 END IF;
1444
1445 IF p_act_attachment_rec.attribute10 = FND_API.g_miss_char THEN
1446 x_complete_rec.attribute10 := l_act_attachment_rec.attribute10;
1447 END IF;
1448
1449 IF p_act_attachment_rec.attribute11 = FND_API.g_miss_char THEN
1450 x_complete_rec.attribute11 := l_act_attachment_rec.attribute11;
1451 END IF;
1452
1453 IF p_act_attachment_rec.attribute12 = FND_API.g_miss_char THEN
1454 x_complete_rec.attribute12 := l_act_attachment_rec.attribute12;
1455 END IF;
1456
1457 IF p_act_attachment_rec.attribute13 = FND_API.g_miss_char THEN
1458 x_complete_rec.attribute13 := l_act_attachment_rec.attribute13;
1459 END IF;
1460
1461 IF p_act_attachment_rec.attribute14 = FND_API.g_miss_char THEN
1462 x_complete_rec.attribute14 := l_act_attachment_rec.attribute14;
1463 END IF;
1464
1465 IF p_act_attachment_rec.attribute15 = FND_API.g_miss_char THEN
1466 x_complete_rec.attribute15 := l_act_attachment_rec.attribute15;
1467 END IF;
1468
1469 IF p_act_attachment_rec.display_text = FND_API.g_miss_char THEN
1470 x_complete_rec.display_text := l_act_attachment_rec.display_text;
1471 END IF;
1472
1473 IF p_act_attachment_rec.alternate_text = FND_API.g_miss_char THEN
1474 x_complete_rec.alternate_text := l_act_attachment_rec.alternate_text;
1475 END IF;
1476
1477 IF p_act_attachment_rec.secured_flag = FND_API.g_miss_char THEN
1478 x_complete_rec.secured_flag := l_act_attachment_rec.secured_flag;
1479 END IF;
1480
1481 IF p_act_attachment_rec.attachment_sub_type = FND_API.g_miss_char THEN
1482 x_complete_rec.attachment_sub_type :=
1483 l_act_attachment_rec.attachment_sub_type ;
1484 END IF;
1485
1486 END complete_act_attachment_rec;
1487
1488 END jtf_amv_attachment_pub;