[Home] [Help]
PACKAGE BODY: APPS.RCV_ASN_ATTACHMENT_PKG
Source
1 PACKAGE BODY RCV_ASN_ATTACHMENT_PKG AS
2 /* $Header: RCVASNAB.pls 120.1 2006/06/24 03:02:37 hvadlamu noship $*/
3
4
5 g_pkg_name CONSTANT VARCHAR2(50) := 'RCV_ASN_ATTACHMENT_PKG';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
7
8 -- Read the profile option that enables/disables the debug log
9 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10
11
12 g_asn_attach_id_tbl asn_attach_id_tbl_type;
13
14
15
16 PROCEDURE copy_asn_line_attachment (
17 p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_interface_txn_id IN NUMBER,
23 p_shipment_line_id IN NUMBER )
24
25 IS
26
27 l_api_name CONSTANT VARCHAR2(30) := 'COPY_ASN_LINE_ATTACHMENT';
28 l_api_version CONSTANT NUMBER := 1.0;
29
30 l_asn_attach_id NUMBER := 0;
31 l_counter NUMBER;
32 l_created_by NUMBER;
33 l_last_update_login NUMBER;
34 l_attch_exist_flag VARCHAR2(1) := 'N';
35
36
37 CURSOR l_asn_attach_id_cur IS
38 select rti.asn_attach_id,rti.created_by,rti.last_update_login
39 from fnd_attached_documents fad,
40 rcv_transactions_interface rti
41 where rti.interface_transaction_id = p_interface_txn_id
42 and rti.asn_attach_id is not null
43 and to_char(rti.asn_attach_id) = fad.PK1_value
44 and fad.entity_name = 'ASN_ATTACH';
45
46 BEGIN
47
48 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
53 l_api_name, g_pkg_name)
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58 x_return_status := FND_API.g_ret_sts_success;
59
60 BEGIN
61 OPEN l_asn_attach_id_cur;
62 FETCH l_asn_attach_id_cur INTO
63 l_asn_attach_id,l_created_by,l_last_update_login;
64 CLOSE l_asn_attach_id_cur;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 l_asn_attach_id := 0;
69 X_RETURN_STATUS := FND_API.g_ret_sts_error;
70
71 IF l_asn_attach_id_cur%ISOPEN THEN
72 CLOSE l_asn_attach_id_cur;
73 END IF;
74 END;
75
76
77 /* If Attachment exist for the line then call FND api to copy attachments. */
78 if (l_asn_attach_id <> 0) then
79
80 BEGIN
81 fnd_attached_documents2_pkg.copy_attachments(
82 X_from_entity_name => 'ASN_ATTACH',
83 X_from_pk1_value => to_char(l_asn_attach_id),
84 X_to_entity_name => 'RCV_LINES',
85 X_to_pk1_value => to_char(p_shipment_line_id),
86 x_created_by => l_created_by,
87 x_last_update_login => l_last_update_login);
88
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 X_RETURN_STATUS := FND_API.g_ret_sts_error;
93 END;
94
95 commit;
96
97 IF (g_asn_attach_id_tbl.count = 0) THEN
98 g_asn_attach_id_tbl(0) := l_asn_attach_id;
99
100 ELSE
101
102 FOR l_counter IN 0..g_asn_attach_id_tbl.count - 1 LOOP
103 IF (l_asn_attach_id = g_asn_attach_id_tbl(l_counter)) THEN
104 l_attch_exist_flag := 'Y';
105 exit;
106 END IF;
107
108 END LOOP;
109
110 IF (l_attch_exist_flag = 'N') THEN
111 g_asn_attach_id_tbl(l_counter + 1) := l_asn_attach_id;
112 END IF;
113
114 END IF;
115
116 end if; /* end if (l_asn_attach_id <> 0) */
117
118 EXCEPTION
119 WHEN FND_API.g_exc_error THEN
120 x_return_status := FND_API.g_ret_sts_error;
121 WHEN FND_API.g_exc_unexpected_error THEN
122 x_return_status := FND_API.g_ret_sts_unexp_error;
123 WHEN OTHERS THEN
124 x_return_status := FND_API.g_ret_sts_unexp_error;
125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
126 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
127 IF (g_fnd_debug = 'Y') THEN
128 asn_debug.put_line('Unexpected error '||sqlcode,FND_LOG.level_unexpected);
129 END IF;
130 END IF;
131 raise;
132 END copy_asn_line_attachment;
133
134
135
136
137 PROCEDURE delete_asn_intf_attachments (
138 p_api_version IN NUMBER,
139 p_init_msg_list IN VARCHAR2,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2 )
143
144 IS
145
146 l_counter NUMBER;
147 l_asn_attach_id NUMBER;
148 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ASN_INTF_ATTACHMENTS';
149 l_api_version CONSTANT NUMBER := 1.0;
150
151
152 BEGIN
153
154 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
155 FND_MSG_PUB.initialize;
156 END IF;
157
158 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
159 l_api_name, g_pkg_name)
160 THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166
167 IF (g_asn_attach_id_tbl.count > 0) THEN
168
169 FOR l_counter IN 0..g_asn_attach_id_tbl.count - 1 LOOP
170 l_asn_attach_id := g_asn_attach_id_tbl(l_counter);
171
172 IF (l_asn_attach_id is not null) THEN
173
174 BEGIN
175 delete_line_attachment (
176 p_api_version => p_api_version,
177 p_init_msg_list => p_init_msg_list,
178 x_return_status => x_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data,
181 p_asn_attach_id => l_asn_attach_id );
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 X_RETURN_STATUS := FND_API.g_ret_sts_error;
186 END;
187
188 END IF;
189 END LOOP;
190
191 END IF;
192
193 commit;
194
195 EXCEPTION
196 WHEN FND_API.g_exc_error THEN
197 x_return_status := FND_API.g_ret_sts_error;
198 WHEN FND_API.g_exc_unexpected_error THEN
199 x_return_status := FND_API.g_ret_sts_unexp_error;
200 WHEN OTHERS THEN
201 x_return_status := FND_API.g_ret_sts_unexp_error;
202 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
203 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
204 IF (g_fnd_debug = 'Y') THEN
205 asn_debug.put_line('Unexpected error '||sqlcode,FND_LOG.level_unexpected);
206 END IF;
207 END IF;
208
209 END delete_asn_intf_attachments;
210
211
212
213 PROCEDURE delete_line_attachment (
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2,
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2,
219 p_asn_attach_id IN NUMBER )
220
221 IS
222
223 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LINE_ATTACHMENT';
224 l_api_version CONSTANT NUMBER := 1.0;
225
226 l_datatype_id NUMBER := 0;
227 l_delete_doc_flag VARCHAR2(1) := 'Y';
228
229 CURSOR l_doctype_cur IS
230 SELECT FD.datatype_id
231 FROM FND_DOCUMENTS FD,
232 FND_ATTACHED_DOCUMENTS FAD
233 WHERE FAD.entity_name = 'ASN_ATTACH'
234 AND FAD.pk1_value = to_char(p_asn_attach_id)
235 AND FD.document_id = FAD.document_id;
236
237 BEGIN
238
239 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
240 FND_MSG_PUB.initialize;
241 END IF;
242
243 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
244 l_api_name, g_pkg_name)
245 THEN
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 END IF;
248
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 IF (p_asn_attach_id is not null) THEN
252
253 BEGIN
254 OPEN l_doctype_cur;
255 LOOP
256 FETCH l_doctype_cur INTO l_datatype_id;
257 EXIT WHEN l_doctype_cur%NOTFOUND;
258 IF (l_datatype_id = 5) THEN
259 EXIT;
260 END IF;
261
262 END LOOP;
263 CLOSE l_doctype_cur;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 l_datatype_id := 0;
268
269 IF l_doctype_cur%ISOPEN THEN
270 CLOSE l_doctype_cur;
271 END IF;
272 END;
273
274 /* Do not delete URL document since it was not copied. */
275 IF (l_datatype_id = 5) THEN
276 l_delete_doc_flag := 'N';
277 END IF;
278
279 BEGIN
280 fnd_attached_documents2_pkg.delete_attachments(
281 X_entity_name => 'ASN_ATTACH',
282 X_pk1_value => to_char(p_asn_attach_id),
283 X_delete_document_flag => l_delete_doc_flag);
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 X_RETURN_STATUS := FND_API.g_ret_sts_error;
288 END;
289
290 END IF;
291
292 EXCEPTION
293 WHEN FND_API.g_exc_error THEN
294 x_return_status := FND_API.g_ret_sts_error;
295 WHEN FND_API.g_exc_unexpected_error THEN
296 x_return_status := FND_API.g_ret_sts_unexp_error;
297 WHEN OTHERS THEN
298 x_return_status := FND_API.g_ret_sts_unexp_error;
299 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
300 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
301 IF (g_fnd_debug = 'Y') THEN
302 asn_debug.put_line('Unexpected error '||sqlcode,FND_LOG.level_unexpected);
303 END IF;
304 END IF;
305
306 END delete_line_attachment;
307
308
309 END RCV_ASN_ATTACHMENT_PKG;