[Home] [Help]
PACKAGE BODY: APPS.CSFW_SIGNATURE_PVT
Source
1 PACKAGE BODY CSFW_SIGNATURE_PVT as
2 /* $Header: csfwsigb.pls 115.2 2003/10/29 23:51:46 pgiri noship $ */
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 PROCEDURE UPLOAD_SIGNATURE
26 (p_debrief_header_id NUMBER,
27 p_task_assignment_id NUMBER,
28 p_description VARCHAR2,
29 p_file_data BLOB,
30 p_error_msg OUT NOCOPY VARCHAR2,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 ) IS
33
34 l_seq_num number;
35 l_category_id number;
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(240);
38 l_fnd_lobs_id number;
39 l_language VARCHAR2(4);
40 l_error_msg varchar(1024);
41
42 l_signature_loc blob;
43 l_signature_raw raw(32767);
44 l_signature_size number;
45 l_dummy NUMBER;
46
47 -- get max document sequence number for debrief header
48 CURSOR l_max_seq_no_cursor(x_debrief_header_id IN number)
49 IS
50 SELECT nvl(max(fad.seq_num),0)+10
51 FROM fnd_attached_documents fad,
52 fnd_documents fd
53 WHERE fad.pk1_value = to_char(x_debrief_header_id)
54 AND fd.document_id = fad.document_id
55 AND EXISTS
56 (SELECT 1
57 FROM fnd_document_categories_tl cat_tl
58 WHERE cat_tl.category_id = fd.category_id
59 AND cat_tl.user_name = 'Signature'
60 );
61
62 -- get the category_id
63 CURSOR l_category_id_cursor
64 IS
65 SELECT category_id
66 FROM fnd_document_categories_tl
67 WHERE user_name = 'Signature';
68
69 -- get next sequence value for FND_LOBS
70 CURSOR l_fnd_lobs_s_cursor
71 IS
72 select fnd_lobs_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 DEBRIEF_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_debrief_header_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 Signature
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 -- enter data into fnd_lobs
110 BEGIN
111 OPEN l_fnd_lobs_s_cursor;
112 FETCH l_fnd_lobs_s_cursor INTO l_fnd_lobs_id;
113 CLOSE l_fnd_lobs_s_cursor;
114
115 INSERT INTO fnd_lobs(file_id, file_name, file_content_type, file_data, upload_date, language, file_format)
116 VALUES (l_fnd_lobs_id, 'INTERNAL', 'image/bmp', empty_blob(), SYSDATE, l_language, 'binary')
117 RETURN file_data into l_signature_loc;
118
119 l_signature_size := dbms_lob.getLength(p_file_data);
120 dbms_lob.read(p_file_data, l_signature_size, 1, l_signature_raw);
121
122 dbms_lob.write(l_signature_loc, l_signature_size, 1, l_signature_raw);
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 -- check if the record exists
127 open l_lobs_fileid_csr(l_fnd_lobs_id) ;
128 fetch l_lobs_fileid_csr into l_dummy;
129 if l_lobs_fileid_csr%found then
130 --the record exists. Dont show any error.
131 null;
132 else
133 --record could not be inserted, throw the exception
134 raise RECORD_NOT_INSERTED;
135 end if;
136 close l_lobs_fileid_csr;
137
138 END;
139
140
141 -- Add attachement to Debrief
142 BEGIN
143 fnd_webattch.add_attachment(
144 seq_num => l_seq_num,
145 category_id => l_category_id,
146 document_description=> p_description, --l_signed_date||' '||l_signed_by,
147 datatype_id => 6,
148 text => NULL,
149 file_name => 'INTERNAL',
150 url => NULL,
151 function_name => 'CSFFEDBF',
152 entity_name => 'CSF_DEBRIEF_HEADERS',
153 pk1_value => p_debrief_header_id,
154 pk2_value => NULL,
155 pk3_value => NULL,
156 pk4_value => NULL,
157 pk5_value => NULL,
158 media_id => l_fnd_lobs_id,
159 user_id => fnd_global.login_id);
160 EXCEPTION
161 WHEN OTHERS THEN
162 raise DEBRIEF_ATTACH_FAILED;
163 END;
164
165 EXCEPTION
166
167 WHEN RECORD_NOT_INSERTED THEN
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 p_error_msg := 'Record not inserted';
170
171 WHEN DEBRIEF_ATTACH_FAILED THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 p_error_msg := 'Attachment of BLOB:' || l_fnd_lobs_id || ' to Debrief Header Id:'|| p_debrief_header_id || ' failed';
174
175 WHEN OTHERS THEN
176
177 p_error_msg := SQLERRM;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179
180 END UPLOAD_SIGNATURE;
181
182 END CSFW_SIGNATURE_PVT;