[Home] [Help]
PACKAGE BODY: APPS.EDR_TRANS_ACKN_PVT
Source
1 package body EDR_TRANS_ACKN_PVT as
2 /* $Header: EDRVACKB.pls 120.0.12000000.1 2007/01/18 05:55:58 appldev ship $ */
3
4 -- Private Utility Functions --
5
6 function IS_STATUS_VALID
7 ( p_status IN VARCHAR2 ,
8 p_mode IN VARCHAR2
9 )
10 RETURN BOOLEAN
11 is
12 l_return_value BOOLEAN := TRUE;
13 begin
14 --when a row is being inserted only the NOTACKNOWLEDGED status is valid.
15 --along with the one time migration value of NOTCOLLECTED
16 --the insert happens privately in the ERES Fwk
17
18 if (p_mode = G_INSERT_MODE)
19 then
20 if p_status <> EDR_CONSTANTS_GRP.g_no_ack_status
21 and p_status <> EDR_CONSTANTS_GRP.g_migration_ack_status
22 then
23 l_return_value := FALSE;
24 end if;
25
26 --when a row is being updated only the SUCCESS and ERROR are valid
27 --the upadate happens publicly by product teams
28
29 elsif (p_mode = G_UPDATE_MODE)
30 then
31 if p_status <> EDR_CONSTANTS_GRP.g_success_ack_status
32 and p_status <> EDR_CONSTANTS_GRP.g_error_ack_status
33 then
34 l_return_value := FALSE;
35 end if;
36 end if;
37
38 return l_return_value;
39
40 end IS_STATUS_VALID;
41
42 procedure INSERT_ROW
43 ( p_api_version IN NUMBER ,
44 p_init_msg_list IN VARCHAR2 ,
45 p_validation_level IN NUMBER ,
46 x_return_status OUT NOCOPY VARCHAR2 ,
47 x_msg_count OUT NOCOPY NUMBER ,
48 x_msg_data OUT NOCOPY VARCHAR2 ,
49 p_erecord_id IN NUMBER ,
50 p_trans_status IN VARCHAR2 ,
51 p_ackn_by IN VARCHAR2 ,
52 p_ackn_note IN VARCHAR2 ,
53 x_ackn_id OUT NOCOPY NUMBER
54 )
55 as
56 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
57 l_api_version CONSTANT NUMBER := 1.0;
58
59 l_return_status VARCHAR2(1);
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2(2000);
62 l_mesg_text VARCHAR2(2000);
63
64 L_CREATION_DATE DATE;
65 L_CREATED_BY NUMBER;
66 L_LAST_UPDATE_DATE DATE;
67 L_LAST_UPDATED_BY NUMBER;
68 L_LAST_UPDATE_LOGIN NUMBER;
69
70 l_valid_status BOOLEAN;
71 l_ackn_id NUMBER;
72 l_existing_ack_count PLS_INTEGER;
73
74 INVALID_ERECORD_ERROR EXCEPTION;
75 INVALID_ACK_STATUS_ERROR EXCEPTION;
76 DUPLICATE_ACK_ERROR EXCEPTION;
77
78 cursor l_ack_csr is
79 select count(ackn_id)
80 from edr_trans_ackn
81 where erecord_id = p_erecord_id;
82
83 begin
84 -- Standard call to check for call compatibility.
85 IF NOT FND_API.Compatible_API_Call (l_api_version ,
86 p_api_version ,
87 l_api_name ,
88 G_PKG_NAME )
89 THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean( p_init_msg_list ) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 -- Initialize API return status to success
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 -- API Body
102 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
103
104 -- validate if this is a duplicate acknowledgement
105 if p_validation_level >= G_VALIDATE_DUP_ACK then
106 open l_ack_csr;
107
108 fetch l_ack_csr into l_existing_ack_count;
109
110 if (l_existing_ack_count > 0) then
111 RAISE DUPLICATE_ACK_ERROR;
112 end if;
113
114 close l_ack_csr;
115
116 end if;
117
118 -- validate if the erecord id is valid
119 if p_validation_level >= G_VALIDATE_ERECORD then
120 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
121 ( p_api_version => 1.0,
122 x_return_status => l_return_status,
123 x_msg_count => l_msg_count,
124 x_msg_data => l_msg_data,
125 p_erecord_id => p_erecord_id
126 );
127
128 -- If any errors happen abort API.
129 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
132 RAISE INVALID_ERECORD_ERROR;
133 END IF;
134 end if;
135
136 -- validate if the status is valid
137 if p_validation_level >= G_VALIDATE_STATUS then
138 l_valid_status := IS_STATUS_VALID
139 ( p_status => p_trans_status ,
140 p_mode => G_INSERT_MODE
141 );
142
143 if not l_valid_status then
144 RAISE INVALID_ACK_STATUS_ERROR;
145 end if;
146 end if;
147
148 END IF; --if for validation check
149
150 -- after all the validations are done, insert the row in the
151 -- database
152
153 select edr_trans_ackn_s.nextval
154 into l_ackn_id
155 from dual;
156
157 EDR_UTILITIES.getWhoColumns
158 ( creation_date => l_creation_date ,
159 created_by => l_created_by ,
160 last_update_date => l_last_update_date ,
161 last_updated_by => l_last_updated_by ,
162 last_update_login => l_last_update_login
163 );
164
165 insert into EDR_TRANS_ACKN(
166 ACKN_ID
167 ,ERECORD_ID
168 ,ACKN_DATE
169 ,TRANSACTION_STATUS
170 ,ACKN_BY
171 ,ACKN_NOTE
172 ,CREATION_DATE
173 ,CREATED_BY
174 ,LAST_UPDATE_DATE
175 ,LAST_UPDATED_BY
176 ,LAST_UPDATE_LOGIN
177 ) values (
178 l_ackn_id
179 ,p_erecord_id
180 ,SYSDATE
181 ,p_trans_status
182 ,p_ackn_by
183 ,p_ackn_note
184 ,L_CREATION_DATE
185 ,L_CREATED_BY
186 ,L_LAST_UPDATE_DATE
187 ,L_LAST_UPDATED_BY
188 ,L_LAST_UPDATE_LOGIN
189 );
190
191 x_ackn_id := l_ackn_id;
192
193 -- End of API Body
194
195 -- Standard call to get message count and if count is 1,
196 --get message info.
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count ,
199 p_data => x_msg_data
200 );
201
202 EXCEPTION
203 WHEN INVALID_ERECORD_ERROR THEN
204 x_return_status := FND_API.G_RET_STS_ERROR ;
205
206 fnd_message.set_name('EDR','EDR_ACK_INVALID_ERECORD');
207 fnd_message.set_token('ERECORD_ID', p_erecord_id);
208 l_mesg_text := fnd_message.get();
209
210 FND_MSG_PUB.Add_Exc_Msg
211 ( G_PKG_NAME ,
212 l_api_name ,
213 l_mesg_text
214 );
215 FND_MSG_PUB.Count_And_Get
216 ( p_count => x_msg_count ,
217 p_data => x_msg_data
218 );
219
220 WHEN INVALID_ACK_STATUS_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_ERROR ;
222
223 fnd_message.set_name('EDR','EDR_ACK_INVALID_STATUS');
224 fnd_message.set_token('ERECORD_ID', p_erecord_id);
225 fnd_message.set_token('STATUS', p_trans_status);
226 l_mesg_text := fnd_message.get();
227
228 FND_MSG_PUB.Add_Exc_Msg
229 ( G_PKG_NAME ,
230 l_api_name ,
231 l_mesg_text
232 );
233 FND_MSG_PUB.Count_And_Get
234 ( p_count => x_msg_count ,
235 p_data => x_msg_data
236 );
237
238 WHEN DUPLICATE_ACK_ERROR THEN
239 x_return_status := FND_API.G_RET_STS_ERROR ;
240
241 --close the cursor that was opened
242 close l_ack_csr;
243
244 fnd_message.set_name('EDR','EDR_ACK_DUPLICATE_ACKN');
245 fnd_message.set_token('ERECORD_ID', p_erecord_id);
246 l_mesg_text := fnd_message.get();
247
248 FND_MSG_PUB.Add_Exc_Msg
249 ( G_PKG_NAME ,
250 l_api_name ,
251 l_mesg_text
252 );
253 FND_MSG_PUB.Count_And_Get
254 ( p_count => x_msg_count ,
255 p_data => x_msg_data
256 );
257
258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260
261 FND_MSG_PUB.Count_And_Get
262 ( p_count => x_msg_count ,
263 p_data => x_msg_data
264 );
265
266 WHEN OTHERS THEN
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
268
269 IF FND_MSG_PUB.Check_Msg_Level
270 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
271 THEN
272 FND_MSG_PUB.Add_Exc_Msg
273 ( G_PKG_NAME ,
274 l_api_name
275 );
276 END IF;
277
278 FND_MSG_PUB.Count_And_Get
279 (p_count => x_msg_count ,
280 p_data => x_msg_data
281 );
282
283 end INSERT_ROW;
284
285 procedure SEND_ACKN_AUTO
286 ( p_api_version IN NUMBER ,
287 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE ,
288 x_return_status OUT NOCOPY VARCHAR2 ,
289 x_msg_count OUT NOCOPY NUMBER ,
290 x_msg_data OUT NOCOPY VARCHAR2 ,
291 p_event_name IN VARCHAR2 ,
292 p_event_key IN VARCHAR2 ,
293 p_erecord_id IN NUMBER ,
294 p_trans_status IN VARCHAR2 ,
295 p_ackn_by IN VARCHAR2 default NULL ,
296 p_ackn_note IN VARCHAR2 default NULL
297 )
298 as PRAGMA AUTONOMOUS_TRANSACTION;
299 l_api_name CONSTANT VARCHAR2(30) := 'SEND_ACKN_AUTO';
300 l_api_version CONSTANT NUMBER := 1.0;
301
302 L_CREATION_DATE DATE;
303 L_CREATED_BY NUMBER;
304 L_LAST_UPDATE_DATE DATE;
305 L_LAST_UPDATED_BY NUMBER;
306 L_LAST_UPDATE_LOGIN NUMBER;
307
308 begin
309 -- Standard call to check for call compatibility.
310 IF NOT FND_API.Compatible_API_Call (l_api_version ,
311 p_api_version ,
312 l_api_name ,
313 G_PKG_NAME )
314 THEN
315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
316 END IF;
317
318 -- Initialize message list if p_init_msg_list is set to TRUE.
319 IF FND_API.to_Boolean( p_init_msg_list ) THEN
320 FND_MSG_PUB.initialize;
321 END IF;
322
323 -- Initialize API return status to success
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 -- API Body
327
328 -- This API is called after all the validations are done
329 -- so dont do any validation. just do an update and commit
330
331 EDR_UTILITIES.getWhoColumns
332 ( creation_date => l_creation_date ,
333 created_by => l_created_by ,
334 last_update_date => l_last_update_date ,
335 last_updated_by => l_last_updated_by ,
336 last_update_login => l_last_update_login
337 );
338
339 update EDR_TRANS_ACKN SET
340 ACKN_DATE = SYSDATE ,
341 TRANSACTION_STATUS = p_trans_status ,
342 ACKN_BY = p_ackn_by ,
343 ACKN_NOTE = p_ackn_note ,
344 LAST_UPDATE_DATE = l_last_update_date ,
345 LAST_UPDATED_BY = l_last_updated_by ,
346 LAST_UPDATE_LOGIN = l_last_update_login
347 where ERECORD_ID = p_erecord_id;
348
349 -- always commit, this is an autonomous txn
350 COMMIT;
351
352 EXCEPTION
353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
354 ROLLBACK;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
356
357 FND_MSG_PUB.Count_And_Get
358 ( p_count => x_msg_count ,
359 p_data => x_msg_data
360 );
361
362 WHEN OTHERS THEN
363 ROLLBACK;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365
366 IF FND_MSG_PUB.Check_Msg_Level
367 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
368 THEN
369 FND_MSG_PUB.Add_Exc_Msg
370 ( G_PKG_NAME ,
371 l_api_name
372 );
373 END IF;
374
375 FND_MSG_PUB.Count_And_Get
376 (p_count => x_msg_count ,
377 p_data => x_msg_data
378 );
379
380 end SEND_ACKN_AUTO;
381
382 end EDR_TRANS_ACKN_PVT;