[Home] [Help]
PACKAGE BODY: APPS.PVX_ATTACHMENT_INT
Source
1 PACKAGE BODY PVX_ATTACHMENT_INT AS
2 /* $Header: pvxpathb.pls 120.1 2005/08/23 01:31:21 appldev noship $ */
3 -- Start of Comments
4 -- Package name : PVX_ATTACHMENT_INT
5 -- Purpose :
6 -- History :
7 -- 10/22/2002 hyang - 2633826, added validation in delete_attachments
8 -- NOTE :
9 -- End of Comments
10
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30) :='PVX_ATTACHMENT_INT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxatit.pls';
15
16 -- Add_Attachment
17 -- IN
18 -- p_seq_num - Attachment Seq Number.
19 -- p_category_id - category of the attachment
20 -- p_document_description - desciption of the document
21 -- p_datatype_id - Datatype identifier
22 -- p_text - Text Input.
23 -- p_file_name - File name
24 -- p_url - URL from which the attachments is invoked from.
25 -- This is required to set the back link.
26 -- p_function_name - Function name of the form
27 -- p_media_id - Document Content reference.
28
29 PROCEDURE Add_Attachment(
30 p_api_version_number IN NUMBER
31 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
32 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
33 ,p_seq_num IN VARCHAR2
34 ,p_category_id IN VARCHAR2
35 ,p_document_description IN VARCHAR2
36 ,p_datatype_id IN VARCHAR2
37 ,p_text IN LONG
38 ,p_file_name IN VARCHAR2
39 ,p_url IN VARCHAR2
40 ,p_function_name IN VARCHAR2
41 ,p_quote_header_id IN NUMBER
42 ,p_media_id IN NUMBER
43 ,x_return_status OUT NOCOPY VARCHAR2
44 ,x_msg_count OUT NOCOPY NUMBER
45 ,x_msg_data OUT NOCOPY VARCHAR2
46 ) IS
47 l_api_name CONSTANT VARCHAR2(30) := 'Add_Attachment';
48 l_api_version CONSTANT NUMBER := 1.0;
49
50 l_file_name varchar2(200);
51
52 CURSOR c_get_file_name (l_file_id number) IS
53 SELECT file_name
54 FROM FND_lobs
55 WHERE fnd_lobs.file_id =l_file_id;
56
57
58 BEGIN
59
60 -- Standard Start of API savepoint
61 SAVEPOINT Add_ATTACHMENT_PVT;
62
63 IF NOT FND_API.Compatible_API_Call (l_api_version
64 ,P_Api_Version_Number
65 ,l_api_name
66 ,G_PKG_NAME )
67 THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70
71
72 OPEN c_get_file_name( p_media_id);
73 FETCH c_get_file_name into l_file_name;
74
75 IF c_get_file_name%notFOUND THEN
76 l_file_name := p_file_name;
77 END IF;
78 close c_get_file_name;
79
80 -- Initialize message list if p_init_msg_list is set to TRUE.
81 IF FND_API.to_Boolean(p_init_msg_list) THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85
86
87 -- Initialize API return status to error, i.e, its not duplicate
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90
91 FND_WEBATTCH.Add_Attachment(
92 seq_num => p_seq_num
93 ,category_id => p_category_id
94 ,document_description => p_document_description
95 ,datatype_id => p_datatype_id
96 ,text => p_text
97 ,file_name => l_file_name
98 ,url => p_url
99 ,function_name => NULL
100 ,entity_name => 'PVATTACH'
101 ,pk1_value => TO_CHAR(p_quote_header_id)
102 ,pk2_value => NULL
103 ,pk3_value => NULL
104 ,pk4_value => NULL
105 ,pk5_value => NULL
106 ,media_id => p_media_id
107 ,user_id => to_char(FND_GLOBAL.USER_ID)
108 );
109
110 -- End of API body.
111 -- Standard check of p_commit.
112 IF FND_API.to_Boolean( p_commit )
113 THEN
114 COMMIT WORK;
115 END IF;
116
117
118
119 -- Standard call to get message count and if count is 1, get message info.
120 FND_MSG_PUB.Count_And_Get
121 ( p_count => x_msg_count,
122 p_data => x_msg_data
123 );
124
125 EXCEPTION
126 WHEN FND_API.G_EXC_ERROR THEN
127 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
128 P_API_NAME => L_API_NAME
129 ,P_PKG_NAME => G_PKG_NAME
130 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
131 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
132 ,P_SQLCODE => SQLCODE
133 ,P_SQLERRM => SQLERRM
134 ,X_MSG_COUNT => X_MSG_COUNT
135 ,X_MSG_DATA => X_MSG_DATA
136 ,X_RETURN_STATUS => X_RETURN_STATUS);
137
138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
139 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
140 P_API_NAME => L_API_NAME
141 ,P_PKG_NAME => G_PKG_NAME
142 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
143 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
144 ,P_SQLCODE => SQLCODE
145 ,P_SQLERRM => SQLERRM
146 ,X_MSG_COUNT => X_MSG_COUNT
147 ,X_MSG_DATA => X_MSG_DATA
148 ,X_RETURN_STATUS => X_RETURN_STATUS);
149
150 WHEN OTHERS THEN
151 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
152 P_API_NAME => L_API_NAME
153 ,P_PKG_NAME => G_PKG_NAME
154 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
155 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
156 ,P_SQLCODE => SQLCODE
157 ,P_SQLERRM => SQLERRM
158 ,X_MSG_COUNT => X_MSG_COUNT
159 ,X_MSG_DATA => X_MSG_DATA
160 ,X_RETURN_STATUS => X_RETURN_STATUS);
161
162 END Add_Attachment;
163
164
165 PROCEDURE Delete_Attachments(
166 p_api_version_number IN NUMBER
167 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
168 ,p_commit IN VARCHAR2 := FND_API.g_false
169 ,p_quote_header_id IN NUMBER
170 ,p_quote_attachment_ids IN JTF_VARCHAR2_TABLE_100
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 )
175 IS
176 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
177 l_api_version CONSTANT NUMBER := 1.0;
178
179 l_media_id NUMBER;
180 l_document_id NUMBER;
181 l_entity_name FND_ATTACHED_DOCUMENTS.ENTITY_NAME%TYPE;
182
183 /* 2633826 - hyang: added checking of p_quote_header_id */
184 CURSOR c_get_media_id (l_attachment_id varchar2) IS
185 SELECT a.media_id, b.document_id
186 FROM FND_ATTACHED_DOCUMENTS b
187 ,FND_DOCUMENTS_TL a
188 WHERE a.document_id = b.document_id
189 AND b.attached_document_id = l_attachment_id
190 AND b.pk1_value = p_quote_header_id
191 AND b.entity_name = 'PVATTACH';
192
193 CURSOR c_get_document_id_rows (p_document_id number) IS
194 SELECT a.document_id
195 FROM FND_ATTACHED_DOCUMENTS a
196 WHERE a.document_id = p_document_id
197 AND a.pk1_value <> p_quote_header_id;
198 --AND a.entity_name <> 'ASO_QUOTE_HEADERS_ALL';
199
200 BEGIN
201
202 -- Standard Start of API savepoint
203 SAVEPOINT DELETE_ATTACHMENTS_PVT;
204
205 IF NOT FND_API.Compatible_API_Call (l_api_version
206 ,P_Api_Version_Number
207 ,l_api_name
208 ,G_PKG_NAME )
209 THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213
214 -- Initialize message list if p_init_msg_list is set to TRUE.
215 IF FND_API.to_Boolean(p_init_msg_list) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218
219
220 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
221 aso_debug_pub.add (
222 'DELETE_ATTACHMENTS: quote_header_id ' || p_quote_header_id,
223 1,
224 'Y'
225 );
226 END IF;
227
228
229
230 -- Initialize API return status to error, i.e, its not duplicate
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 FOR i in 1..p_quote_attachment_ids.COUNT LOOP
234
235 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
236 aso_debug_pub.add ('DELETE_ATTACHMENTS: quote_attachment_id ' || p_quote_attachment_ids(i),1,'Y');
237 END IF;
238
239 -- Get the media_id for the attachment.
240 OPEN c_get_media_id (p_quote_attachment_ids(i));
241 FETCH c_get_media_id into l_media_id, l_document_id;
242
243 /*
244 * 2633826 - hyang: added validation of quote_header_id.
245 * Returns error if association is not found.
246 */
247 IF c_get_media_id%NOTFOUND
248 THEN
249 CLOSE c_get_media_id;
250
251 IF fnd_msg_pub.check_msg_level (
252 fnd_msg_pub.g_msg_lvl_error
253 )
254 THEN
255 fnd_message.set_name (
256 'ASO',
257 'ASO_API_INVALID_ID'
258 );
259 FND_MESSAGE.Set_Token(
260 'COLUMN',
261 'QUOTE_HEADER_ID', FALSE);
262 fnd_msg_pub.ADD;
263 END IF;
264
265 RAISE fnd_api.g_exc_error;
266 ELSE
267 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
268 aso_debug_pub.add ('DELETE_ATTACHMENTS: l_document_id : ' || l_document_id,1,'Y');
269 END IF;
270 END IF;
271
272 CLOSE c_get_media_id;
273
274 -- Call the procedure to delete the attachment and document.
275 FND_ATTACHED_DOCUMENTS3_PKG.delete_row ( p_quote_attachment_ids(i),
276 '6', 'N' );
277
278 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
279 aso_debug_pub.add ( 'DELETE_ATTACHMENTS: Check to see if the attachment was from an Opportunity...', 1, 'Y');
280 END IF;
281
282 OPEN c_get_document_id_rows( l_document_id);
283 FETCH c_get_document_id_rows into l_document_id;
284
285 IF c_get_document_id_rows%NOTFOUND
286 THEN
287 CLOSE c_get_document_id_rows;
288 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
289 aso_debug_pub.add ( 'DELETE_ATTACHMENTS: Attachment is not from an Opportunity...', 1, 'Y');
290 END IF;
291 DELETE FROM fnd_lobs WHERE file_id = l_media_id;
292 ELSE
293 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
294 aso_debug_pub.add ( 'DELETE_ATTACHMENTS: Attachment is from an Opportunity...', 1, 'Y');
295 END IF;
296 END IF;
297
298 IF c_get_document_id_rows%ISOPEN THEN
299 CLOSE c_get_document_id_rows;
300 END IF;
301
302 END LOOP;
303
304 -- End of API body.
305 -- Standard check of p_commit.
306 IF FND_API.to_Boolean( p_commit )
307 THEN
308 COMMIT WORK;
309 END IF;
310
311
312
313 -- Standard call to get message count and if count is 1, get message info.
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
322 P_API_NAME => L_API_NAME
323 ,P_PKG_NAME => G_PKG_NAME
324 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
325 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
326 ,P_SQLCODE => SQLCODE
327 ,P_SQLERRM => SQLERRM
328 ,X_MSG_COUNT => X_MSG_COUNT
329 ,X_MSG_DATA => X_MSG_DATA
330 ,X_RETURN_STATUS => X_RETURN_STATUS);
331
332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
334 P_API_NAME => L_API_NAME
335 ,P_PKG_NAME => G_PKG_NAME
336 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
337 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
338 ,P_SQLCODE => SQLCODE
339 ,P_SQLERRM => SQLERRM
340 ,X_MSG_COUNT => X_MSG_COUNT
341 ,X_MSG_DATA => X_MSG_DATA
342 ,X_RETURN_STATUS => X_RETURN_STATUS);
343
344 WHEN OTHERS THEN
345 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
346 P_API_NAME => L_API_NAME
347 ,P_PKG_NAME => G_PKG_NAME
348 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
349 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
350 ,P_SQLCODE => SQLCODE
351 ,P_SQLERRM => SQLERRM
352 ,X_MSG_COUNT => X_MSG_COUNT
353 ,X_MSG_DATA => X_MSG_DATA
354 ,X_RETURN_STATUS => X_RETURN_STATUS);
355
356 END Delete_Attachments;
357
358 END PVX_ATTACHMENT_INT;