1 PACKAGE BODY WSH_ITM_ERROR_PKG AS
2 /* $Header: WSHITERB.pls 115.3 2002/12/12 12:01:46 bradha ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_ITM_ERROR_PKG';
6
7 /*----------------------------------------------------------*/
8 /* Insert_Row Procedure */
9 /*----------------------------------------------------------*/
10 /* --
11 -- Purpose
12 -- Insert a row into WSH_ITM_RESPONSE_RULES entity
13 --
14 -- Input Parameters
15 -- p_api_version
16 -- API version number (current version is 1.0)
17 -- p_init_msg_list (optional, default FND_API.G_FALSE)
18 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
19 -- if set to FND_API.G_TRUE
20 -- initialize error message list
21 -- if set to FND_API.G_FALSE - not initialize error
22 -- message list
23 -- p_commit (optional, default FND_API.G_FALSE)
24 -- whether or not to commit the changes to database
25 --
26 -- p_VENDOR_ID -- Vendor Id
27 -- P_VENDOR -- Service Provider
28 -- P_ERROR_TYPE -- Error Type
29 -- P_ERROR_CODE -- Error Code
30 -- P_INTERPRETED_CODE -- Interpreted Code
31 --
32 --*/
33
34 PROCEDURE Insert_Row
35 (
36 p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
38 p_commit IN VARCHAR2 := fnd_api.g_false ,
39 x_return_status OUT NOCOPY VARCHAR2 ,
40 x_msg_count OUT NOCOPY NUMBER ,
41 x_msg_data OUT NOCOPY VARCHAR2 ,
42 p_VENDOR_ID IN NUMBER,
43 p_VENDOR IN VARCHAR2,
44 p_ERROR_TYPE IN VARCHAR2,
45 p_ERROR_CODE IN VARCHAR2,
46 p_INTERPRETED_CODE IN VARCHAR2,
47 X_ROWID OUT NOCOPY VARCHAR2
48 )
49 IS
50 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
51 l_api_version number := 1.0;
52
53 BEGIN
54 -- Standard Start of API savepoint
55 SAVEPOINT WSH_ITM_ERROR_PKG;
56
57 -- Standard call to check for call compatibility.
58 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
59 p_api_version ,
60 l_api_name ,
61 G_PKG_NAME )
62 THEN
63 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
64 FND_MSG_PUB.ADD;
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list ) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- Initialize API return status to success
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 insert into wsh_itm_response_rules
77 (VENDOR_ID,
78 ERROR_TYPE,
79 ERROR_CODE,
80 INTERPRETED_VALUE_CODE,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_LOGIN
86 )
87 values (
88 p_VENDOR_ID
89 ,p_error_type
90 ,p_error_code
91 ,p_interpreted_code
92 ,sysdate
93 ,FND_GLOBAL.user_id
94 ,sysdate
95 ,FND_GLOBAL.user_id
96 ,FND_GLOBAL.login_id
97 ) ;
98
99 IF SQL%NOTFOUND THEN
100 FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
101 FND_MSG_PUB.ADD;
102 x_return_status := FND_API.G_RET_STS_ERROR;
103 RAISE FND_API.G_EXC_ERROR ;
104 END IF;
105
106 x_return_status := fnd_api.g_ret_sts_success;
107
108 SELECT rowid
109 INTO x_rowid
110 FROM wsh_itm_response_rules
111 WHERE vendor_id = p_vendor_id
112 AND nvl(error_code,-99) = nvl(p_error_code,-99)
113 AND error_type = p_error_type
114 AND interpreted_value_code = p_interpreted_code;
115
116
117 -- End of API body
118 -- Standard check of p_commit.
119
120 IF FND_API.To_Boolean( p_commit ) THEN
121 COMMIT WORK;
122 END IF;
123
124 -- Standard call to get message count and if count is 1,
125 -- get message info.
126
127 FND_MSG_PUB.Count_And_Get
128 ( p_count => x_msg_count,
129 p_data => x_msg_data
130 );
131
132 EXCEPTION
133 WHEN FND_API.G_EXC_ERROR THEN
134 ROLLBACK TO WSH_ITM_ERROR_PKG;
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 FND_MSG_PUB.Count_And_Get
137 ( p_count => x_msg_count,
138 p_data => x_msg_data
139 );
140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
141 ROLLBACK TO WSH_ITM_ERROR_PKG;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 FND_MSG_PUB.Count_And_Get
144 ( p_count => x_msg_count,
145 p_data => x_msg_data
146 );
147 WHEN OTHERS THEN
148 ROLLBACK TO WSH_ITM_ERROR_PKG;
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 IF FND_MSG_PUB.Check_Msg_Level
151 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg
154 ( G_PKG_NAME ,
155 l_api_name
156 );
157 END IF;
158 FND_MSG_PUB.Count_And_Get
159 ( p_count => x_msg_count,
160 p_data => x_msg_data
161 );
162 End Insert_Row;
163
164
165 /*----------------------------------------------------------*/
166 /* Update_Row Procedure */
167 /*----------------------------------------------------------*/
168 /* --
169 -- Purpose
170 -- Update a row into WSH_ITM_RESPONSE_RULES entity
171 --
172 -- Input Parameters
173 -- p_api_version
174 -- API version number (current version is 1.0)
175 -- p_init_msg_list (optional, default FND_API.G_FALSE)
176 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
177 -- if set to FND_API.G_TRUE
178 -- initialize error message list
179 -- if set to FND_API.G_FALSE - not initialize error
180 -- message list
181 -- p_commit (optional, default FND_API.G_FALSE)
182 -- whether or not to commit the changes to database
183 --
184 -- Input parameters for clear cross users informations
185 -- p_VENDOR_ID -- Vendor Id
186 -- P_VENDOR -- Service Provider
187 -- P_ERROR_TYPE -- Error Type
188 -- P_ERROR_CODE -- Error Code
189 -- P_INTERPRETED_CODE -- Interpreted Code
190 -- P_ROWID
191 --
192 -- Output Parameters
193 -- x_return_status
194 -- if the process succeeds, the value is
195 -- fnd_api.g_ret_sts_success;
196 -- if there is an expected error, the value is
197 -- fnd_api.g_ret_sts_error;
198 -- if there is an unexpected error, the value is
199 -- fnd_api.g_ret_sts_unexp_error;
200 -- x_msg_count
201 -- if there is one or more errors, the number of error messages
202 -- in the buffer
203 -- x_msg_data
204 -- if there is one and only one error, the error message
205 -- (See fnd_api package for more details about the above output parameters)
206
207 --*/
208 PROCEDURE Update_Row
209 (
210 p_api_version IN NUMBER ,
211 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
212 p_commit IN VARCHAR2 := fnd_api.g_false ,
213 x_return_status OUT NOCOPY VARCHAR2 ,
214 x_msg_count OUT NOCOPY NUMBER ,
215 x_msg_data OUT NOCOPY VARCHAR2 ,
216 p_VENDOR_ID IN NUMBER,
217 p_VENDOR IN VARCHAR2,
218 p_ERROR_TYPE IN VARCHAR2,
219 p_ERROR_CODE IN VARCHAR2,
220 p_INTERPRETED_CODE IN VARCHAR2,
221 p_ROWID IN VARCHAR2
222 )
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row' ;
225 l_api_version number := 1.0;
226
227 BEGIN
228
229 -- Standard Start of API savepoint
230 SAVEPOINT WSH_ITM_ERROR_PKG;
231
232 -- Standard call to check for call compatibility.
233 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
234 p_api_version ,
235 l_api_name ,
236 G_PKG_NAME )
237 THEN
238 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
239 FND_MSG_PUB.ADD;
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243 -- Initialize message list if p_init_msg_list is set to TRUE.
244 IF FND_API.to_Boolean( p_init_msg_list ) THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247
248 -- Initialize API return status to success
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 -- Update a row into wsh_itm_users entity with all detail information
252 -- for the given cc seq id
253
254 UPDATE WSH_ITM_RESPONSE_RULES
255 SET
256 VENDOR_ID = P_VENDOR_ID
257 ,ERROR_TYPE = P_ERROR_TYPE
258 ,ERROR_CODE = P_ERROR_CODE
259 ,INTERPRETED_VALUE_CODE = P_INTERPRETED_CODE
260 ,LAST_UPDATE_DATE = sysdate
261 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
262 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
263 WHERE rowid = p_rowid;
264
265 IF SQL%NOTFOUND THEN
266 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
267 FND_MSG_PUB.ADD;
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 RAISE FND_API.G_EXC_ERROR ;
270 END IF;
271
272 x_return_status := fnd_api.g_ret_sts_success;
273
274 -- End of API body
275 -- Standard check of p_commit.
276
277 IF FND_API.To_Boolean( p_commit ) THEN
278 COMMIT WORK;
279 END IF;
280
281 -- Standard call to get message count and if count is 1,
282 -- get message info.
283 FND_MSG_PUB.Count_And_Get
284 ( p_count => x_msg_count,
285 p_data => x_msg_data
286 );
287
288 EXCEPTION
289 WHEN FND_API.G_EXC_ERROR THEN
290 ROLLBACK TO WSH_ITM_ERROR_PKG;
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 FND_MSG_PUB.Count_And_Get
293 ( p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 ROLLBACK TO WSH_ITM_ERROR_PKG;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 FND_MSG_PUB.Count_And_Get
300 ( p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303 WHEN OTHERS THEN
304 ROLLBACK TO WSH_ITM_ERROR_PKG;
305 x_return_status := 'W';
306 IF FND_MSG_PUB.Check_Msg_Level
307 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
308 THEN
309 FND_MSG_PUB.Add_Exc_Msg
310 ( G_PKG_NAME ,
311 l_api_name
312 );
313 END IF;
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318
319 End Update_Row;
320
321 /*----------------------------------------------------------*/
322 /* Delete_Row Procedure */
323 /*----------------------------------------------------------*/
324 /* --
325 -- Purpose
326 -- Delete a row from WSH_ITM_RESPONE_RULES entity
327 --
328 -- Input Parameters
329 -- p_api_version
330 -- API version number (current version is 1.0)
331 -- p_init_msg_list (optional, default FND_API.G_FALSE)
332 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
333 -- if set to FND_API.G_TRUE
334 -- initialize error message list
335 -- if set to FND_API.G_FALSE - not initialize error
336 -- message list
337 -- p_commit (optional, default FND_API.G_FALSE)
338 -- whether or not to commit the changes to database
339 --
340 -- Input parameters for clear cross users informations
341 -- p_rowid
342 --
343 --
344 -- Output Parameters
345 -- x_return_status
346 -- if the process succeeds, the value is
347 -- fnd_api.g_ret_sts_success;
348 -- if there is an expected error, the value is
349 -- fnd_api.g_ret_sts_error;
350 -- if there is an unexpected error, the value is
351 -- fnd_api.g_ret_sts_unexp_error;
352 -- x_msg_count
353 -- if there is one or more errors, the number of error messages
354 -- in the buffer
355 -- x_msg_data
356 -- if there is one and only one error, the error message
357 -- (See fnd_api package for more details about the above output parameters)
358
359 --*/
360 PROCEDURE Delete_Row
361 (
362 p_api_version IN NUMBER ,
363 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
364 p_commit IN VARCHAR2 := fnd_api.g_false ,
365 x_return_status OUT NOCOPY VARCHAR2 ,
366 x_msg_count OUT NOCOPY NUMBER ,
367 x_msg_data OUT NOCOPY VARCHAR2 ,
368 p_rowid IN VARCHAR2
369 )
370 IS
371 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
372 l_api_version number := 1.0;
373
374 BEGIN
375
376 -- Standard Start of API savepoint
377 SAVEPOINT WSH_ITM_ERROR_PKG;
378
379 -- Standard call to check for call compatibility.
380 IF NOT FND_API.Compatible_API_Call
381 ( l_api_version ,
382 p_api_version ,
383 l_api_name ,
384 G_PKG_NAME )
385 THEN
386 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
387 FND_MSG_PUB.ADD;
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390
391 -- Initialize message list if p_init_msg_list is set to TRUE.
392 IF FND_API.to_Boolean( p_init_msg_list ) THEN
393 FND_MSG_PUB.initialize;
394 END IF;
395
396 -- Initialize API return status to success
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399
400 DELETE FROM WSH_ITM_RESPONSE_RULES
401 WHERE rowid = p_rowid;
402
403 IF SQL%NOTFOUND THEN
404 FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
405 FND_MSG_PUB.ADD;
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 RAISE FND_API.G_EXC_ERROR ;
408 END IF;
409
410 x_return_status := fnd_api.g_ret_sts_success;
411
412 -- End of API body
413 -- Standard check of p_commit.
414
415 IF FND_API.To_Boolean( p_commit ) THEN
416 COMMIT WORK;
417 END IF;
418
419 -- Standard call to get message count and if count is 1,
420 -- get message info.
421
422 FND_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426
427 EXCEPTION
428 WHEN FND_API.G_EXC_ERROR THEN
429 ROLLBACK TO WSH_ITM_ERROR_PKG;
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 FND_MSG_PUB.Count_And_Get
432 ( p_count => x_msg_count,
433 p_data => x_msg_data
434 );
435
436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
437 ROLLBACK TO WSH_ITM_ERROR_PKG;
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 FND_MSG_PUB.Count_And_Get
440 ( p_count => x_msg_count,
441 p_data => x_msg_data
442 );
443
444 WHEN OTHERS THEN
445 ROLLBACK TO WSH_ITM_ERROR_PKG;
446 x_return_status := 'W';
447 IF FND_MSG_PUB.Check_Msg_Level
448 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
449 THEN
450 FND_MSG_PUB.Add_Exc_Msg
451 ( G_PKG_NAME ,
452 l_api_name
453 );
454 END IF;
455 FND_MSG_PUB.Count_And_Get
456 ( p_count => x_msg_count,
457 p_data => x_msg_data
458 );
459
460 End Delete_Row;
461
462 /*----------------------------------------------------------*/
463 /* Lock_Row Procedure */
464 /*----------------------------------------------------------*/
465 /* --
466 -- Purpose
467 -- Lock a row into WSH_ITM_RESPONSE_RULES entity
468 --
469 -- Input Parameters
470 -- p_api_version
471 -- API version number (current version is 1.0)
472 -- p_init_msg_list (optional, default FND_API.G_FALSE)
473 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
474 -- if set to FND_API.G_TRUE
475 -- initialize error message list
476 -- if set to FND_API.G_FALSE - not initialize error
477 -- message list
478 -- p_commit (optional, default FND_API.G_FALSE)
479 -- whether or not to commit the changes to database
480 --
481 -- Input parameters for clear cross users informations
482 -- p_VENDOR_ID --Vendor Id
483 -- P_VENDOR -- Service Provider
484 -- P_ERROR_TYPE -- Error Type
485 -- P_ERROR_CODE -- Error Code
486 -- P_INTERPRETED_CODE -- Interpreted Code
487 --
488 --
489 -- Output Parameters
490 -- x_return_status
491 -- if the process succeeds, the value is
492 -- fnd_api.g_ret_sts_success;
493 -- if there is an expected error, the value is
494 -- fnd_api.g_ret_sts_error;
495 -- if there is an unexpected error, the value is
496 -- fnd_api.g_ret_sts_unexp_error;
497 -- x_msg_count
498 -- if there is one or more errors, the number of error messages
499 -- in the buffer
500 -- x_msg_data
501 -- if there is one and only one error, the error message
502 -- (See fnd_api package for more details about the above output parameters)
503
504 --*/
505 PROCEDURE Lock_Row
506 (
507 p_api_version IN NUMBER ,
508 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
509 p_commit IN VARCHAR2 := fnd_api.g_false ,
510 x_return_status OUT NOCOPY VARCHAR2 ,
511 x_msg_count OUT NOCOPY NUMBER ,
512 x_msg_data OUT NOCOPY VARCHAR2 ,
513 p_VENDOR_ID IN NUMBER,
514 p_VENDOR IN VARCHAR2,
515 p_ERROR_TYPE IN VARCHAR2,
516 p_ERROR_CODE IN VARCHAR2,
517 p_INTERPRETED_CODE IN VARCHAR2,
518 p_ROWID IN VARCHAR2
519 )
520 IS
521
522 changed exception;
523 others exception;
524 CURSOR lock_row IS
525 SELECT *
526 FROM WSH_ITM_RESPONSE_RULES
527 WHERE rowid = p_rowid
528 FOR UPDATE OF VENDOR_ID NOWAIT;
529
530 Recinfo lock_row%ROWTYPE;
531
532 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
533 l_api_version number := 1.0;
534 BEGIN
535
536 -- Standard Start of API savepoint
537 SAVEPOINT WSH_ITM_ERROR_PKG;
538
539 -- Standard call to check for call compatibility.
540 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
541 p_api_version ,
542 l_api_name ,
543 G_PKG_NAME )
544 THEN
545 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
546 FND_MSG_PUB.ADD;
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
548 END IF;
549
550 -- Initialize message list if p_init_msg_list is set to TRUE.
551 IF FND_API.to_Boolean( p_init_msg_list ) THEN
552 FND_MSG_PUB.initialize;
553 END IF;
554
555 -- Initialize API return status to success
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557
558 OPEN lock_row;
559 FETCH lock_row into Recinfo;
560
561 IF (lock_row%NOTFOUND) THEN
562 CLOSE lock_row;
563 Raise Others;
564 END IF;
565
566 IF ( (Recinfo.vendor_id = p_vendor_id)
567 AND (Recinfo.error_type =p_error_type)
568 AND ((Recinfo.error_code =p_error_code) OR
569 ( (Recinfo.error_code is null)
570 AND (p_error_code is null )))
571 AND (Recinfo.interpreted_value_code =p_interpreted_code)
572 ) THEN
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574 ELSE
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 Raise Changed;
577 END IF;
578 CLOSE lock_row;
579
580 EXCEPTION
581 WHEN Changed then
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
584 WHEN others THEN
585 x_return_status := FND_API.G_RET_STS_ERROR;
586 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
587 End Lock_Row;
588 END WSH_ITM_ERROR_PKG;
[Home] [Help]