[Home] [Help]
PACKAGE BODY: APPS.CSFW_ATTACHMENT_PVT
Source
1 PACKAGE BODY CSFW_ATTACHMENT_PVT AS
2 /* $Header: csfwattachb.pls 120.1.12020000.3 2012/07/23 08:10:38 shadas ship $ */
3
4 /*
5 Procedure to upload attachment to Server
6 */
7 PROCEDURE UPLOAD_ATTACHMENT
8 (p_incident_id NUMBER,
9 p_incident_number VARCHAR2,
10 p_datatype_id NUMBER,
11 p_title VARCHAR2,
12 p_description VARCHAR2,
13 p_category_user_name VARCHAR2,
14 p_file_name VARCHAR2,
15 p_file_content_type VARCHAR2,
16 p_text VARCHAR2,
17 p_url VARCHAR2,
18 p_file_data BLOB,
19 p_error_msg OUT NOCOPY VARCHAR2,
20 x_return_status IN OUT NOCOPY VARCHAR2
21 ) IS
22
23 l_seq_num number;
24 l_category_id number;
25 l_msg_count NUMBER;
26 l_msg_data VARCHAR2(240);
27 l_fnd_lobs_id number;
28 l_fnd_text_id number;
29 l_language VARCHAR2(4);
30 l_error_msg varchar(1024);
31
32 l_attachment_text VARCHAR2(32767);
33 l_attachment_loc blob;
34 l_attachment_raw raw(32767);
35 l_attachment_size number;
36 l_dummy number;
37
38 l_rw_amount number;
39 l_rw_offset number;
40
41 -- get max document sequence number for the incident id
42 CURSOR l_max_seq_no_cursor(x_incident_id IN number)
43 IS
44 SELECT nvl(max(fad.seq_num),0)+10
45 FROM fnd_attached_documents fad,
46 fnd_documents fd
47 WHERE fad.pk1_value = to_char(x_incident_id)
48 AND fd.document_id = fad.document_id
49 AND EXISTS
50 (SELECT 1
51 FROM fnd_document_categories_tl cat_tl
52 WHERE cat_tl.category_id = fd.category_id
53 --AND cat_tl.user_name = p_category_user_name
54 );
55
56 -- get the category_id
57 CURSOR l_category_id_cursor
58 IS
59 SELECT category_id
60 FROM fnd_document_categories_tl
61 WHERE user_name = p_category_user_name;
62
63 -- get next sequence value for FND_LOBS
64 CURSOR l_fnd_lobs_s_cursor
65 IS
66 select fnd_lobs_s.nextval
67 from dual;
68
69 -- get next sequence value for fnd_documents_short_text
70 CURSOR fnd_short_text_s_cursor
71 is
72 select fnd_documents_short_text_s.nextval
73 from dual;
74
75 -- get language of user
76 CURSOR l_language_cursor
77 IS
78 select userenv('LANG')
79 from dual;
80
81 --to check if the record exists in the database
82 cursor l_lobs_fileid_csr (p_file_id fnd_lobs.file_id%TYPE) is
83 select 1
84 from fnd_lobs
85 where file_id = p_file_id;
86
87 RECORD_NOT_INSERTED EXCEPTION;
88 ATTACH_FAILED EXCEPTION;
89
90 BEGIN
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- get the max seq no
95 open l_max_seq_no_cursor(p_incident_id);
96 FETCH l_max_seq_no_cursor INTO l_seq_num;
97 CLOSE l_max_seq_no_cursor;
98
99 -- get the category id for given Category name
100 OPEN l_category_id_cursor;
101 FETCH l_category_id_cursor INTO l_category_id;
102 CLOSE l_category_id_cursor;
103
104 -- get language of session
105 OPEN l_language_cursor;
106 FETCH l_language_cursor INTO l_language;
107 CLOSE l_language_cursor;
108
109
110 IF (p_datatype_id = 6) THEN
111 BEGIN
112 -- enter data into fnd_lobs
113 OPEN l_fnd_lobs_s_cursor;
114 FETCH l_fnd_lobs_s_cursor INTO l_fnd_lobs_id;
115 CLOSE l_fnd_lobs_s_cursor;
116
117 INSERT INTO fnd_lobs(file_id, file_name, file_content_type, file_data, upload_date, language, file_format)
118 VALUES (l_fnd_lobs_id, p_file_name, p_file_content_type, empty_blob(), SYSDATE, l_language, 'binary')
119 RETURN file_data into l_attachment_loc;
120
121 l_attachment_size := dbms_lob.getLength(p_file_data);
122 --insert into shakti_audit(msg) values('File size : '|| l_attachment_size);
123
124 l_rw_offset := 1;
125 while (l_attachment_size > 0) loop
126 if (l_attachment_size > 32767) then
127 l_rw_amount := 32767;
128 else
129 l_rw_amount := l_attachment_size;
130 end if;
131 l_attachment_size := l_attachment_size - l_rw_amount;
132 --insert into shakti_audit(msg) values('New attachment size : '|| l_attachment_size);
133
134 dbms_lob.read(p_file_data, l_rw_amount, l_rw_offset, l_attachment_raw);
135 --insert into shakti_audit(msg) values('Read complete');
136
137 dbms_lob.write(l_attachment_loc, l_rw_amount, l_rw_offset, l_attachment_raw);
138 --insert into shakti_audit(msg) values('Write complete');
139
140 l_rw_offset := l_rw_offset + 32767;
141 --insert into shakti_audit(msg) values('New offset : '|| l_rw_offset);
142
143 end loop;
144 exception
145 when others then
146 --insert into shakti_audit(msg) values('Error');
147 --raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
148 -- check if the record exists
149 open l_lobs_fileid_csr(l_fnd_lobs_id) ;
150 fetch l_lobs_fileid_csr into l_dummy;
151 if l_lobs_fileid_csr%found then
152 --the record exists. Dont show any error.
153 null;
154 else
155 --record could not be inserted, throw the exception
156 raise RECORD_NOT_INSERTED;
157 end if;
158 close l_lobs_fileid_csr;
159 END;
160 ELSIF (p_datatype_id = 1) THEN
161 -- Enter data into fnd_documents_short_text
162 BEGIN
163 OPEN fnd_short_text_s_cursor;
164 FETCH fnd_short_text_s_cursor INTO l_fnd_text_id;
165 CLOSE fnd_short_text_s_cursor;
166
167 INSERT INTO fnd_documents_short_text(media_id, short_text)
168 VALUES (l_fnd_text_id, p_text)
169 RETURN short_text into l_attachment_text;
170
171 END;
172 -- ELSIF (p_datatype_id = 5) THEN
173 -- BEGIN
174 -- END;
175 END IF;
176
177 -- Add attachement to Service Request
178 BEGIN
179 fnd_webattch.add_attachment(
180 seq_num => l_seq_num,
181 category_id => l_category_id,
182 document_description => p_description,
183 datatype_id => p_datatype_id,
184 text => p_text,
185 file_name => p_file_name,
186 url => p_url,
187 function_name => 'CSXSRISR', -- CSXSRISR - Create Service Request Form Name, i.e., form function name
188 entity_name => 'CS_INCIDENTS',
189 pk1_value => p_incident_id,
190 pk2_value => NULL,
191 pk3_value => NULL,
192 pk4_value => NULL,
193 pk5_value => NULL,
194 media_id => l_fnd_lobs_id,
195 user_id => fnd_global.login_id,
196 title => p_title);
197 EXCEPTION
198 WHEN OTHERS THEN
199 raise ATTACH_FAILED;
200 END;
201
202 EXCEPTION
203
204 WHEN RECORD_NOT_INSERTED THEN
205 x_return_status := FND_API.G_RET_STS_ERROR;
206 p_error_msg := 'Record not inserted';
207
208 WHEN ATTACH_FAILED THEN
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 p_error_msg := 'Attachment of BLOB:' || l_fnd_lobs_id || ' to Service Request Number:'|| p_incident_number || ' failed';
211
212 WHEN OTHERS THEN
213
214 p_error_msg := SQLERRM;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216
217 END UPLOAD_ATTACHMENT;
218
219 END CSFW_ATTACHMENT_PVT;
220