[Home] [Help]
PACKAGE BODY: APPS.EDR_TRANS_ACKN_PUB
Source
1 package body EDR_TRANS_ACKN_PUB as
2 /* $Header: EDRPACKB.pls 120.0.12000000.1 2007/01/18 05:54:16 appldev ship $ */
3
4 procedure SEND_ACKN
5 ( p_api_version IN NUMBER ,
6 p_init_msg_list IN VARCHAR2 ,
7 x_return_status OUT NOCOPY VARCHAR2 ,
8 x_msg_count OUT NOCOPY NUMBER ,
9 x_msg_data OUT NOCOPY VARCHAR2 ,
10 p_event_name IN VARCHAR2 ,
11 p_event_key IN VARCHAR2 ,
12 p_erecord_id IN NUMBER ,
13 p_trans_status IN VARCHAR2 ,
14 p_ackn_by IN VARCHAR2 ,
15 p_ackn_note IN VARCHAR2 ,
16 p_autonomous_commit IN VARCHAR2
17 )
18 as
19 l_api_name CONSTANT VARCHAR2(30) := 'SEND_ACKN';
20 l_api_version CONSTANT NUMBER := 1.0;
21
22 l_return_status VARCHAR2(1);
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(2000);
25 l_mesg_text VARCHAR2(2000);
26
27 L_CREATION_DATE DATE;
28 L_CREATED_BY NUMBER;
29 L_LAST_UPDATE_DATE DATE;
30 L_LAST_UPDATED_BY NUMBER;
31 L_LAST_UPDATE_LOGIN NUMBER;
32
33 l_valid_status BOOLEAN;
34 l_ackn_id NUMBER;
35 l_status VARCHAR2(30);
36
37 l_event_name VARCHAR2(80);
38 l_event_key VARCHAR2(240);
39 l_existing_ack_count PLS_INTEGER := 0;
40
41 cursor l_ack_csr is
42 select transaction_status
43 from edr_trans_ackn
44 where erecord_id = p_erecord_id;
45
46 INVALID_ERECORD_ERROR EXCEPTION;
47 INVALID_ACK_STATUS_ERROR EXCEPTION;
48 INVALID_EVENT_ERROR EXCEPTION;
49 DUPLICATE_ACK_ERROR EXCEPTION;
50 NO_DATA_ERROR EXCEPTION;
51 begin
52 /*
53 -- Standard Start of API savepoint
54 SAVEPOINT SEND_ACKN_PUB;
55 */
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call (l_api_version ,
58 p_api_version ,
59 l_api_name ,
60 G_PKG_NAME )
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 -- Initialize message list if p_init_msg_list is set to TRUE.
66 IF FND_API.to_Boolean( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
74 edr_ctx_pkg.set_secure_attr;
75 -- END Bug : 3834375
76 -- API Body
77
78 -- validate that the erecord id is valid and that the event
79 -- name and event key are the right ones for the erecord id
80
81 EDR_ERES_EVENT_PUB.GET_EVENT_DETAILS
82 ( p_api_version => 1.0 ,
83 x_return_status => l_return_status ,
84 x_msg_count => l_msg_count ,
85 x_msg_data => l_msg_data ,
86 p_erecord_id => p_erecord_id ,
87 x_event_name => l_event_name ,
88 x_event_key => l_event_key
89 );
90
91 -- If any errors happen abort API.
92 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
95 RAISE INVALID_ERECORD_ERROR;
96 END IF;
97
98 if l_event_name <> p_event_name OR l_event_key <> p_event_key
99 then
100 RAISE INVALID_EVENT_ERROR;
101 end if;
102
103 -- now validate that the value of the trans ackn status is
104 -- valid
105
106 l_valid_status := EDR_TRANS_ACKN_PVT.IS_STATUS_VALID
107 ( p_status => p_trans_status ,
108 p_mode => EDR_TRANS_ACKN_PVT.G_UPDATE_MODE
109 );
110
111 if not l_valid_status then
112 RAISE INVALID_ACK_STATUS_ERROR;
113 end if;
114
115 -- now validate that there this is not a duplicate acknowledgement
116
117 open l_ack_csr;
118
119 loop
120 fetch l_ack_csr into l_status;
121 exit when l_ack_csr%NOTFOUND;
122
123 l_existing_ack_count := l_existing_ack_count + 1;
124 --
125 -- Start bug fix 3129598
126 -- Commented following 5 lines of code which verifies
127 -- current call is duplicate or not
128 --
129 /*
130 if l_status = EDR_CONSTANTS_GRP.g_success_ack_status
131 or l_status = EDR_CONSTANTS_GRP.g_error_ack_status
132 then
133 RAISE DUPLICATE_ACK_ERROR;
134 end if;
135 */
136 end loop;
137
138 if l_existing_ack_count = 0 then
139 RAISE NO_DATA_ERROR;
140 end if;
141
142 close l_ack_csr;
143
144 -- after all the validations are done, update the acknowledgement
145 -- row in the database
146
147 -- Find out if the autonomous commit flag is on or not
148 -- if an auto commit is required call the corresponding
149 -- auto txn
150
151 IF FND_API.To_Boolean(p_autonomous_commit) THEN
152 EDR_TRANS_ACKN_PVT.SEND_ACKN_AUTO
153 ( p_api_version => 1.0 ,
154 p_init_msg_list => FND_API.G_FALSE ,
155 x_return_status => l_return_status ,
156 x_msg_count => l_msg_count ,
157 x_msg_data => l_msg_data ,
158 p_event_name => p_event_name ,
159 p_event_key => p_event_key ,
160 p_erecord_id => p_erecord_id ,
161 p_trans_status => p_trans_status ,
162 p_ackn_by => p_ackn_by ,
163 p_ackn_note => p_ackn_note
164 );
165
166 -- If any errors happen abort API.
167 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171 ELSE
172 EDR_UTILITIES.getWhoColumns
173 ( creation_date => l_creation_date ,
174 created_by => l_created_by ,
175 last_update_date => l_last_update_date ,
176 last_updated_by => l_last_updated_by ,
177 last_update_login => l_last_update_login
178 );
179
180 update EDR_TRANS_ACKN SET
181 ACKN_DATE = SYSDATE ,
182 TRANSACTION_STATUS = p_trans_status ,
183 ACKN_BY = p_ackn_by ,
184 ACKN_NOTE = p_ackn_note ,
185 LAST_UPDATE_DATE = l_last_update_date ,
186 LAST_UPDATED_BY = l_last_updated_by ,
187 LAST_UPDATE_LOGIN = l_last_update_login
188 where ERECORD_ID = p_erecord_id;
189
190 END IF;
191
192 -- End of API Body
193
194 -- Standard call to get message count and if count is 1,
195 --get message info.
196 FND_MSG_PUB.Count_And_Get
197 ( p_count => x_msg_count ,
198 p_data => x_msg_data
199 );
200
201 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
202 edr_ctx_pkg.unset_secure_attr;
203 -- END Bug : 3834375
204 EXCEPTION
205 WHEN INVALID_ERECORD_ERROR THEN
206 -- ROLLBACK TO SEND_ACKN_PUB;
207 x_return_status := FND_API.G_RET_STS_ERROR ;
208
209 fnd_message.set_name('EDR','EDR_ACK_INVALID_ERECORD');
210 fnd_message.set_token('ERECORD_ID', p_erecord_id);
211 l_mesg_text := fnd_message.get();
212
213 FND_MSG_PUB.Add_Exc_Msg
214 ( G_PKG_NAME ,
215 l_api_name ,
216 l_mesg_text
217 );
218 FND_MSG_PUB.Count_And_Get
219 ( p_count => x_msg_count ,
220 p_data => x_msg_data
221 );
222
223 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
224 edr_ctx_pkg.unset_secure_attr;
225 -- END Bug : 3834375
226 WHEN INVALID_EVENT_ERROR THEN
227 -- ROLLBACK TO SEND_ACKN_PUB;
228 x_return_status := FND_API.G_RET_STS_ERROR ;
229
230 fnd_message.set_name('EDR','EDR_ACK_INVALID_EVENT');
231 fnd_message.set_token('ERECORD_ID', p_erecord_id);
232 fnd_message.set_token('EVENT_NAME', p_event_name);
233 fnd_message.set_token('EVENT_KEY', p_event_key);
234 l_mesg_text := fnd_message.get();
235
236 FND_MSG_PUB.Add_Exc_Msg
237 ( G_PKG_NAME ,
238 l_api_name ,
239 l_mesg_text
240 );
241 FND_MSG_PUB.Count_And_Get
242 ( p_count => x_msg_count ,
243 p_data => x_msg_data
244 );
245
246 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
247 edr_ctx_pkg.unset_secure_attr;
248 -- END Bug : 3834375
249 WHEN INVALID_ACK_STATUS_ERROR THEN
250 -- ROLLBACK TO SEND_ACKN_PUB;
251 x_return_status := FND_API.G_RET_STS_ERROR ;
252
253 fnd_message.set_name('EDR','EDR_ACK_INVALID_STATUS');
254 fnd_message.set_token('ERECORD_ID', p_erecord_id);
255 fnd_message.set_token('STATUS', p_trans_status);
256 l_mesg_text := fnd_message.get();
257
258 FND_MSG_PUB.Add_Exc_Msg
259 ( G_PKG_NAME ,
260 l_api_name ,
261 l_mesg_text
262 );
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count ,
265 p_data => x_msg_data
266 );
267
268 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
269 edr_ctx_pkg.unset_secure_attr;
270 -- END Bug : 3834375
271 WHEN DUPLICATE_ACK_ERROR THEN
272 -- ROLLBACK TO SEND_ACKN_PUB;
273 x_return_status := FND_API.G_RET_STS_ERROR ;
274
275 --close the cursor that was opened
276 close l_ack_csr;
277
278 fnd_message.set_name('EDR','EDR_ACK_DUPLICATE_ACKN');
279 fnd_message.set_token('ERECORD_ID', p_erecord_id);
280 l_mesg_text := fnd_message.get();
281
282 FND_MSG_PUB.Add_Exc_Msg
283 ( G_PKG_NAME ,
284 l_api_name ,
285 l_mesg_text
286 );
287 FND_MSG_PUB.Count_And_Get
288 ( p_count => x_msg_count ,
289 p_data => x_msg_data
290 );
291
292 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
293 edr_ctx_pkg.unset_secure_attr;
294 -- END Bug : 3834375
295 WHEN NO_DATA_ERROR then
296 -- ROLLBACK TO SEND_ACKN_PUB;
297
298 --close the cursor that was opened
299 close l_ack_csr;
300
301 x_return_status := FND_API.G_RET_STS_ERROR;
302
303 fnd_message.set_name('EDR','EDR_ACK_UNEXPECTED_ERROR');
304 fnd_message.set_token('ERECORD_ID', p_erecord_id);
305 l_mesg_text := fnd_message.get();
306
307 FND_MSG_PUB.Add_Exc_Msg
308 ( G_PKG_NAME ,
309 l_api_name ,
310 l_mesg_text
311 );
312 FND_MSG_PUB.Count_And_Get
313 ( p_count => x_msg_count ,
314 p_data => x_msg_data
315 );
316
317 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
318 edr_ctx_pkg.unset_secure_attr;
319 -- END Bug : 3834375
320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
321 -- ROLLBACK;
322 -- ROLLBACK TO SEND_ACKN_PUB;
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
324
325 FND_MSG_PUB.Count_And_Get
326 ( p_count => x_msg_count ,
327 p_data => x_msg_data
328 );
329
330 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
331 edr_ctx_pkg.unset_secure_attr;
332 -- END Bug : 3834375
333 WHEN OTHERS THEN
334 -- ROLLBACK;
335 -- ROLLBACK TO SEND_ACKN_PUB;
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
337
338 IF FND_MSG_PUB.Check_Msg_Level
339 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
340 THEN
341 FND_MSG_PUB.Add_Exc_Msg
342 ( G_PKG_NAME ,
343 l_api_name
344 );
345 END IF;
346
347 FND_MSG_PUB.Count_And_Get
348 (p_count => x_msg_count ,
349 p_data => x_msg_data
350 );
351
352 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
353 edr_ctx_pkg.unset_secure_attr;
354 -- END Bug : 3834375
355 end SEND_ACKN;
356
357 end EDR_TRANS_ACKN_PUB;