[Home] [Help]
PACKAGE BODY: APPS.CSFW_SIGNATURE_PVT
Source
1 PACKAGE BODY CSFW_SIGNATURE_PVT as
2 /* $Header: csfwsigb.pls 120.1.12020000.2 2013/02/14 10:56:59 kgambhir ship $ */
3 /*
4 --Start of Comments
5 Package name : CSFW_SIGNATURE_PVT
6 Purpose : to upload signature associated with Debrief
7 History :
8 NOTE : Please see the function details for additional information
9
10 UPDATE NOTES
11 | Date Developer Change
12 |------ --------------- --------------------------------------
13 08-06-2003 MMERCHAN Created
14
15
16 --End of Comments
17 */
18
19
20 /*
21 Procedure to insert signature, name and date to Server
22 p_description is composed of --l_signed_date||' '||l_signed_by,
23 */
24
25 /*
26 13411903
27 */
28
29 PROCEDURE UPLOAD_SIGNATURE
30 (p_debrief_header_id NUMBER,
31 p_task_assignment_id NUMBER,
32 p_description VARCHAR2,
33 p_file_name VARCHAR2,
34 p_file_type VARCHAR2,
35 p_file_data BLOB,
36 p_error_msg OUT NOCOPY VARCHAR2,
37 x_return_status IN OUT NOCOPY VARCHAR2
38 ) IS
39
40 l_seq_num number;
41 l_category_id number;
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(240);
44 l_fnd_lobs_id number;
45 l_language VARCHAR2(4);
46 l_error_msg varchar(1024);
47
48 l_signature_loc blob;
49 l_signature_raw raw(32767);
50 l_signature_size number;
51 l_dummy NUMBER;
52
53 -- get max document sequence number for debrief header
54 CURSOR l_max_seq_no_cursor(x_debrief_header_id IN number)
55 IS
56 SELECT nvl(max(fad.seq_num),0)+10
57 FROM fnd_attached_documents fad,
58 fnd_documents fd
59 WHERE fad.pk1_value = to_char(x_debrief_header_id)
60 AND fd.document_id = fad.document_id
61 AND EXISTS
62 (SELECT 1
63 FROM fnd_document_categories_tl cat_tl
64 WHERE cat_tl.category_id = fd.category_id
65 AND cat_tl.user_name = 'Signature'
66 );
67
68 -- get the category_id
69 CURSOR l_category_id_cursor
70 IS
71 SELECT category_id
72 FROM fnd_document_categories_tl
73 WHERE user_name = 'Signature';
74
75 -- get next sequence value for FND_LOBS
76 CURSOR l_fnd_lobs_s_cursor
77 IS
78 select fnd_lobs_s.nextval
79 from dual;
80
81 -- get language of user
82 CURSOR l_language_cursor
83 IS
84 select userenv('LANG')
85 from dual;
86
87 --to check if the record exists in the database
88 cursor l_lobs_fileid_csr (p_file_id fnd_lobs.file_id%TYPE) is
89 select 1
90 from fnd_lobs
91 where file_id = p_file_id;
92
93 RECORD_NOT_INSERTED EXCEPTION;
94 DEBRIEF_ATTACH_FAILED EXCEPTION;
95
96 BEGIN
97
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- get the max seq no
101 open l_max_seq_no_cursor(p_debrief_header_id);
102 FETCH l_max_seq_no_cursor INTO l_seq_num;
103 CLOSE l_max_seq_no_cursor;
104
105 -- get the category id for Signature
106 OPEN l_category_id_cursor;
107 FETCH l_category_id_cursor INTO l_category_id;
108 CLOSE l_category_id_cursor;
109
110 -- get language of session
111 OPEN l_language_cursor;
112 FETCH l_language_cursor INTO l_language;
113 CLOSE l_language_cursor;
114
115 -- enter data into fnd_lobs
116 BEGIN
117 OPEN l_fnd_lobs_s_cursor;
118 FETCH l_fnd_lobs_s_cursor INTO l_fnd_lobs_id;
119 CLOSE l_fnd_lobs_s_cursor;
120
121 --13411903
122 INSERT INTO fnd_lobs(file_id, file_name, file_content_type, file_data, upload_date, language, file_format)
123 VALUES (l_fnd_lobs_id, p_file_name, p_file_type, empty_blob(), SYSDATE, l_language, 'binary')
124 RETURN file_data into l_signature_loc;
125
126 l_signature_size := dbms_lob.getLength(p_file_data);
127 dbms_lob.read(p_file_data, l_signature_size, 1, l_signature_raw);
128
129 dbms_lob.write(l_signature_loc, l_signature_size, 1, l_signature_raw);
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 -- check if the record exists
134 open l_lobs_fileid_csr(l_fnd_lobs_id) ;
135 fetch l_lobs_fileid_csr into l_dummy;
136 if l_lobs_fileid_csr%found then
137 --the record exists. Dont show any error.
138 null;
139 else
140 --record could not be inserted, throw the exception
141 raise RECORD_NOT_INSERTED;
142 end if;
143 close l_lobs_fileid_csr;
144
145 END;
146
147
148 -- Add attachement to Debrief
149 BEGIN
150 fnd_webattch.add_attachment(
151 seq_num => l_seq_num,
152 category_id => l_category_id,
153 document_description=> p_description, --l_signed_date||' '||l_signed_by,
154 datatype_id => 6,
155 text => NULL,
156 file_name => p_file_name,
157 url => NULL,
158 function_name => 'CSFFEDBF',
159 entity_name => 'CSF_DEBRIEF_HEADERS',
160 pk1_value => p_debrief_header_id,
161 pk2_value => NULL,
162 pk3_value => NULL,
163 pk4_value => NULL,
164 pk5_value => NULL,
165 media_id => l_fnd_lobs_id,
166 user_id => fnd_global.login_id);
167 EXCEPTION
168 WHEN OTHERS THEN
169 raise DEBRIEF_ATTACH_FAILED;
170 END;
171
172 EXCEPTION
173
174 WHEN RECORD_NOT_INSERTED THEN
175 x_return_status := FND_API.G_RET_STS_ERROR;
176 p_error_msg := 'Record not inserted';
177
178 WHEN DEBRIEF_ATTACH_FAILED THEN
179 x_return_status := FND_API.G_RET_STS_ERROR;
180 p_error_msg := 'Attachment of BLOB:' || l_fnd_lobs_id || ' to Debrief Header Id:'|| p_debrief_header_id || ' failed';
181
182 WHEN OTHERS THEN
183
184 p_error_msg := SQLERRM;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186
187 END UPLOAD_SIGNATURE;
188
189 FUNCTION encode_base64(p_blob_in in blob) return clob is
190 v_clob clob;
191 v_result clob;
192 v_offset integer;
193 v_chunk_size binary_integer := (48 / 4) * 3;
194 v_buffer_varchar varchar2(48);
195 v_buffer_raw raw(48);
196 begin
197 if p_blob_in is null then
198 return null;
199 end if;
200 dbms_lob.createtemporary(v_clob, true);
201 v_offset := 1;
202 for i in 1 .. ceil(dbms_lob.getlength(p_blob_in) / v_chunk_size) loop
203 dbms_lob.read(p_blob_in, v_chunk_size, v_offset, v_buffer_raw);
204 v_buffer_raw := utl_encode.base64_encode(v_buffer_raw);
205 v_buffer_varchar := utl_raw.cast_to_varchar2(v_buffer_raw);
206 dbms_lob.writeappend(v_clob, length(v_buffer_varchar), v_buffer_varchar);
207 v_offset := v_offset + v_chunk_size;
208 end loop;
209 v_result := v_clob;
210 dbms_lob.freetemporary(v_clob);
211 return v_result;
212 end encode_base64;
213
214 END CSFW_SIGNATURE_PVT;