[Home] [Help]
PACKAGE BODY: APPS.WSH_EXCEPTIONS_GRP
Source
1 PACKAGE BODY WSH_EXCEPTIONS_GRP AS
2 /* $Header: WSHXCPGB.pls 120.4 2006/01/04 10:02:21 parkhj noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_EXCEPTIONS_GRP';
8 -- add your constants here if any
9
10 --===================
11 -- PROCEDURES
12 --===================
13
14 ------------------------------------------------------------------------------
15 -- Procedure: Get_Exceptions
16 --
17 -- Parameters: 1) p_logging_entity_id - entity id for a particular entity name
18 -- 2) p_logging_entity_name - can be 'TRIP', 'STOP', 'DELIVERY',
19 -- 'DETAIL', or 'CONTAINER'
20 -- 3) x_exceptions_tab - list of exceptions
21 --
22 -- Description: This procedure takes in a logging entity id and logging entity
23 -- name and create an exception table.
24 ------------------------------------------------------------------------------
25
26 PROCEDURE Get_Exceptions (
27 -- Standard parameters
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33
34 -- program specific parameters
35 p_logging_entity_id IN NUMBER,
36 p_logging_entity_name IN VARCHAR2,
37
38 -- program specific out parameters
39 x_exceptions_tab OUT NOCOPY WSH_EXCEPTIONS_PUB.XC_TAB_TYPE
40 ) IS
41
42 CURSOR Get_Trip_Exceptions (v_trip_id NUMBER) IS
43 SELECT exception_id, exception_name, status
44 FROM wsh_exceptions
45 WHERE trip_id = v_trip_id;
46
47 CURSOR Get_Stop_Exceptions (v_stop_id NUMBER) IS
48 SELECT exception_id, exception_name, status
49 FROM wsh_exceptions
50 WHERE trip_stop_id = v_stop_id;
51
52 CURSOR Get_Delivery_Exceptions (v_delivery_id NUMBER) IS
53 SELECT exception_id, exception_name, status
54 FROM wsh_exceptions
55 WHERE delivery_id = v_delivery_id;
56
57 CURSOR Get_Detail_Exceptions (v_detail_id NUMBER) IS
58 SELECT exception_id, exception_name, status
59 FROM wsh_exceptions
60 WHERE delivery_detail_id = v_detail_id;
61
62 CURSOR Get_Container_Exceptions (v_del_detail_id NUMBER) IS
63 SELECT exception_id, exception_name, status
64 FROM WSH_EXCEPTIONS
65 WHERE delivery_detail_id = v_del_detail_id;
66
67 -- Standard call to check for call compatibility
68 l_api_version CONSTANT NUMBER := 1.0;
69 l_api_name CONSTANT VARCHAR2(30):= 'Get_Exceptions';
70
71 l_count NUMBER;
72
73 wsh_invalid_exception_name EXCEPTION;
74
75 --
76 l_debug_on BOOLEAN;
77 --
78 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EXCEPTIONS';
79 --
80 BEGIN
81
82 --
83 --
84 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
85 --
86 IF l_debug_on IS NULL
87 THEN
88 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
89 END IF;
90 --
91 --
92 -- Debug Statements
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.push(l_module_name);
96 --
97 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
98 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
99 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_NAME',P_LOGGING_ENTITY_NAME);
100 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_ID',P_LOGGING_ENTITY_ID);
101 END IF;
102
103 IF NOT FND_API.compatible_api_call (
104 l_api_version,
105 p_api_version,
106 l_api_name,
107 G_PKG_NAME) THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111 -- Check p_init_msg_list
112 IF FND_API.to_boolean(p_init_msg_list) THEN
113 FND_MSG_PUB.initialize;
114 END IF;
115
116 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
117
118 l_count := 0;
119 IF (p_logging_entity_name = 'TRIP') THEN
120 FOR rec in Get_Trip_Exceptions(p_logging_entity_id) LOOP
121 l_count := l_count + 1;
122 x_exceptions_tab(l_count) := rec;
123 END LOOP;
124 ELSIF (p_logging_entity_name = 'STOP') THEN
125 FOR rec in Get_Stop_Exceptions(p_logging_entity_id) LOOP
126 l_count := l_count + 1;
127 x_exceptions_tab(l_count) := rec;
128 END LOOP;
129 ELSIF (p_logging_entity_name = 'DELIVERY') THEN
130 FOR rec in Get_Delivery_Exceptions(p_logging_entity_id) LOOP
131 l_count := l_count + 1;
132 x_exceptions_tab(l_count) := rec;
133 END LOOP;
134 ELSIF (p_logging_entity_name = 'DETAIL') THEN
135 FOR rec in Get_Detail_Exceptions(p_logging_entity_id) LOOP
136 l_count := l_count + 1;
137 x_exceptions_tab(l_count) := rec;
138 END LOOP;
139 ELSIF (p_logging_entity_name = 'CONTAINER') THEN
140 FOR rec in Get_Container_Exceptions(p_logging_entity_id) LOOP
141 l_count := l_count + 1;
142 x_exceptions_tab(l_count) := rec;
143 END LOOP;
144 ELSE
145 raise wsh_invalid_exception_name;
146 END IF;
147
148 --
149 -- Debug Statements
150 --
151 IF l_debug_on THEN
152 WSH_DEBUG_SV.pop(l_module_name);
153 END IF;
154 --
155
156 EXCEPTION
157 WHEN wsh_invalid_exception_name THEN
158 FND_MESSAGE.Set_Name('FND', 'WSH_XC_INVALID_LOGGING_ENTITY');
159 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
160 wsh_util_core.add_message(x_return_status);
161 FND_MSG_PUB.Count_And_Get(
162 p_count => x_msg_count,
163 p_data => x_msg_data,
164 p_encoded => FND_API.G_FALSE
165 );
166
167 --
168 -- Debug Statements
169 --
170 IF l_debug_on THEN
171 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_EXCEPTION_NAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
172 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_EXCEPTION_NAME');
173 END IF;
174 --
175
176 WHEN others THEN
177 IF Get_Trip_Exceptions%ISOPEN THEN
178 CLOSE Get_Trip_Exceptions;
179 END IF;
180 IF Get_Stop_Exceptions%ISOPEN THEN
181 CLOSE Get_Stop_Exceptions;
182 END IF;
183 IF Get_Delivery_Exceptions%ISOPEN THEN
184 CLOSE Get_Delivery_Exceptions;
185 END IF;
186 IF Get_Detail_Exceptions%ISOPEN THEN
187 CLOSE Get_Detail_Exceptions;
188 END IF;
189 IF Get_Container_Exceptions%ISOPEN THEN
190 CLOSE Get_Container_Exceptions;
191 END IF;
192
193 wsh_util_core.default_handler('WSH_EXCEPTIONS_GRP.GET_EXCEPTIONS');
194 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
195
196 --
197 -- Debug Statements
198 --
199 IF l_debug_on THEN
200 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
201 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
202 END IF;
203 --
204
205 END Get_Exceptions;
206
207
208 ------------------------------------------------------------------------------
209 -- Procedure: Change_Status
210 --
211 -- Parameters: 1) p_logging_entity_id - entity id for a particular entity name
212 -- 2) p_logging_entity_name - can be 'TRIP', 'STOP', 'DELIVERY',
213 -- 'DETAIL', or 'CONTAINER'
214 -- 3) p_exception_name - name of exceptions which will have
215 -- status updated
216 -- 4) p_exception_id - specific exception to be changed
217 -- 5) p_new_status - Status which exceptions will be updated to
218 -- 6) x_updated_rows - returns number of rows get updated
219 -- Description: This procedure will change status of exceptions for a
220 -- particular entity id, entity name, exception name to a new
221 -- status specified.
222 ------------------------------------------------------------------------------
223
224 PROCEDURE Change_Status (
225 -- Standard parameters
226 p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
228 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
229 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233
234 -- program specific parameters
235 p_logging_entity_id IN NUMBER,
236 p_logging_entity_name IN VARCHAR2,
237 p_exception_name IN VARCHAR2 DEFAULT NULL,
238 p_exception_id IN NUMBER DEFAULT NULL,
239 p_new_status IN VARCHAR2,
240 x_updated_rows OUT NOCOPY NUMBER
241 ) IS
242
243 -- To get the current status of the Exception
244 CURSOR get_status IS
245 SELECT status
246 FROM wsh_exceptions
247 WHERE exception_id = p_exception_id ;
248
249 -- Standard call to check for call compatibility
250 l_api_version CONSTANT NUMBER := 1.0;
251 l_api_name CONSTANT VARCHAR2(30):= 'Change_Status';
252
253 l_exceptions_tab WSH_EXCEPTIONS_PUB.xc_tab_type;
254 l_temp_tab WSH_EXCEPTIONS_PUB.xc_tab_type;
255 l_count NUMBER;
256 l_new_status VARCHAR2(30);
257 l_old_status VARCHAR2(30);
258 l_success_count NUMBER;
259 l_error_count NUMBER;
260
261 WSH_INVALID_EXCEPTION_ID EXCEPTION;
262
263 --
264 l_debug_on BOOLEAN;
265 --
266 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_STATUS';
267 --
268 BEGIN
269
270 --
271 --
272 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
273 --
274 IF l_debug_on IS NULL
275 THEN
276 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
277 END IF;
278 --
279 --
280 -- Debug Statements
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.push(l_module_name);
284 --
285 WSH_DEBUG_SV.log(l_module_name,'p_api_version',p_api_version);
286 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list);
287 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
288 WSH_DEBUG_SV.log(l_module_name,'p_validation_level',p_validation_level);
289 WSH_DEBUG_SV.log(l_module_name,'p_logging_entity_id',p_logging_entity_id);
290 WSH_DEBUG_SV.log(l_module_name,'p_logging_entity_name',p_logging_entity_name);
291 WSH_DEBUG_SV.log(l_module_name,'p_exception_name',p_exception_name);
292 WSH_DEBUG_SV.log(l_module_name,'p_exception_id',p_exception_id);
293 WSH_DEBUG_SV.log(l_module_name,'p_new_status',p_new_status);
294 END IF;
295
296 IF NOT FND_API.compatible_api_call (
297 l_api_version,
298 p_api_version,
299 l_api_name,
300 G_PKG_NAME) THEN
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303
304 -- Check p_init_msg_list
305 IF FND_API.to_boolean(p_init_msg_list) THEN
306 FND_MSG_PUB.initialize;
307 END IF;
308
309 -- initialize parameters
310 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
311 l_new_status := p_new_status;
312
313 -- Checking p_logging_entity_id, p_logging_entity_name, and p_exception_name
314 -- if any of them is not valid then return
315 IF (p_logging_entity_id = FND_API.G_MISS_NUM) OR
316 (p_logging_entity_name = FND_API.G_MISS_CHAR) OR
317 (p_exception_name = FND_API.G_MISS_CHAR) OR
318 (p_new_status = FND_API.G_MISS_CHAR) THEN
319 x_updated_rows := 0;
320 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
321 --
322 -- Debug Statements
323 --
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.pop(l_module_name);
326 END IF;
327 --
328 RETURN;
329 END IF;
330
331 IF p_exception_id IS NULL THEN
332 -- Call get_exceptions to get a list of exceptions per logging_entity_id and logging_entity_name
333 --
334 -- Debug Statements
335 --
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXCEPTIONS_GRP.GET_EXCEPTIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
338 END IF;
339 --
340 WSH_EXCEPTIONS_GRP.Get_Exceptions (p_api_version => p_api_version,
341 p_init_msg_list => p_init_msg_list,
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data,
345 p_logging_entity_id => p_logging_entity_id,
346 p_logging_entity_name => p_logging_entity_name,
347 x_exceptions_tab => l_temp_tab);
348 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) OR (l_temp_tab.count = 0) THEN
349 x_updated_rows := 0;
350 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
351 --
352 -- Debug Statements
353 --
354 IF l_debug_on THEN
355 WSH_DEBUG_SV.pop(l_module_name);
356 END IF;
357 --
358 RETURN;
359 END IF;
360
361 -- If exception_name is specified, only the particular exception will be updated with
362 -- new_status for the passed logging_entity_id; otherwise, all the exceptions will be
363 -- updated with new_status for the passed logging_entity_id.
364 IF p_exception_name IS NULL THEN
365 l_exceptions_tab := l_temp_tab;
366 ELSE
367 l_count := 0;
368 For i in 1..l_temp_tab.count LOOP
369 IF l_temp_tab(i).exception_name = p_exception_name THEN
370 l_count := l_count + 1;
371 l_exceptions_tab(l_count) := l_temp_tab(i);
372 END IF;
373 END LOOP;
374 END IF;
375
376 -- Looping through the exception list and update status accordingly
377 IF l_exceptions_tab.count > 0 THEN
378 l_error_count := 0;
379 l_success_count := 0;
380 For i in 1..l_exceptions_tab.count LOOP
381 --
382 -- Debug Statements
383 --
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.CHANGE_STATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
386 END IF;
387 --
388 WSH_XC_UTIL.change_status (p_api_version => p_api_version,
389 p_init_msg_list => p_init_msg_list,
390 p_commit => FND_API.g_false,
391 p_validation_level => p_validation_level,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data,
395 p_exception_id => l_exceptions_tab(i).exception_id,
396 p_old_status => l_exceptions_tab(i).status,
397 p_set_default_status => FND_API.G_FALSE,
398 x_new_status => l_new_status
399 );
400
401 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
402 l_error_count := l_error_count + 1;
403 ELSE
404 l_success_count := l_success_count + 1;
405 END IF;
406 END LOOP;
410 -- Specific Exception has to be updated
407 END IF;
408
409 ELSE
411 l_error_count := 0;
412 l_success_count := 0;
413
414 OPEN get_status;
415 FETCH get_status INTO l_old_status;
416 IF get_status%NOTFOUND THEN
417 RAISE WSH_INVALID_EXCEPTION_ID;
418 END IF;
419 CLOSE get_status;
420
421 --
422 -- Debug Statements
423 --
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.CHANGE_STATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
426 END IF;
427 --
428 WSH_XC_UTIL.change_status (
429 p_api_version => p_api_version,
430 p_init_msg_list => p_init_msg_list,
431 p_commit => FND_API.g_false,
432 p_validation_level => p_validation_level,
433 x_return_status => x_return_status,
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_exception_id => p_exception_id,
437 p_old_status => l_old_status,
438 p_set_default_status => FND_API.G_FALSE,
439 x_new_status => l_new_status
440 );
441
442 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
443 l_error_count := l_error_count + 1;
444 ELSE
445 l_success_count := l_success_count + 1;
446 END IF;
447
448 END IF;
449
450 IF l_error_count > 0 THEN
451 IF l_success_count = 0 THEN
452 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
453 ELSE
454 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
455 END IF;
456 ELSE
457 IF l_success_count = 0 THEN
458 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
459 ELSE
460 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
461 END IF;
462 END IF;
463
464 x_updated_rows := l_success_count;
465
466 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING))
467 AND FND_API.TO_BOOLEAN(p_commit) THEN
468 COMMIT WORK;
469 END IF;
470
471 FND_MSG_PUB.Count_And_Get(
472 p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE
475 );
476 --
477 -- Debug Statements
478 --
479 IF l_debug_on THEN
480 WSH_DEBUG_SV.pop(l_module_name);
481 END IF;
482 --
483
484 EXCEPTION
485 WHEN WSH_INVALID_EXCEPTION_ID THEN
486 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
487 wsh_util_core.add_message(x_return_status);
488 FND_MSG_PUB.Count_And_Get(
489 p_count => x_msg_count,
490 p_data => x_msg_data,
491 p_encoded => FND_API.G_FALSE
492 );
493
494 --
495 -- Debug Statements
496 --
497 IF l_debug_on THEN
498 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_EXCEPTION_ID exception has occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
499 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_EXCEPTION_ID');
500 END IF;
501 --
502
503 WHEN others THEN
504 wsh_util_core.default_handler('WSH_EXCEPTIONS_GRP.CHANGE_STATUS');
505 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
506 IF FND_MSG_PUB.Check_Msg_Level
507 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.Add_Exc_Msg
510 ( G_PKG_NAME,
511 l_api_name
512 );
513 END IF;
514 FND_MSG_PUB.Count_And_Get
515 ( p_count => x_msg_count,
516 p_data => x_msg_data,
517 p_encoded => FND_API.G_FALSE
518 );
519
520 --
521 -- Debug Statements
522 --
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
525 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
526 END IF;
527 --
528
529 END Change_Status;
530
531
532 ------------------------------------------------------------------------------
533 -- Procedure: Log_Exception
534 --
535 -- Parameters:
536 --
537 -- Description: This Procedure is to log a new exception or to restrictly
538 -- update an existing exception. Update is allowed only for
539 -- the fields which are NULL.
540 --
541 ------------------------------------------------------------------------------
542
543 PROCEDURE Log_Exception (
544 -- Standard parameters
545 p_api_version IN NUMBER,
546 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
547 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
551 x_return_status OUT NOCOPY VARCHAR2,
548 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
549 x_msg_count OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2,
552
553 -- Program specific parameters
554 p_exception_rec IN OUT NOCOPY WSH_EXCEPTIONS_PUB.XC_ACTION_REC_TYPE
555 ) IS
556
557 -- cursor to validate stop_location
558 CURSOR C1(c_trip_id NUMBER) IS
559 SELECT trip_stop_id
560 FROM wsh_xc_trip_stops_v
561 WHERE trip_id = c_trip_id
562 AND location_code = p_exception_rec.stop_location_id;
563
564 -- cursor to validate delivery_detail_id
565 CURSOR C2(c_delivery_id NUMBER) IS
566 SELECT wdd.delivery_detail_id, wda.delivery_id, wda.delivery_assignment_id
567 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
568 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
569 AND ((c_delivery_id is null) or (wda.delivery_id =c_delivery_id))
570 AND wdd.delivery_detail_id = p_exception_rec.delivery_detail_id
571 AND wdd.container_flag IN ('N', 'Y'); -- R12 MDC
572
573 -- cursor to get the delivery_detail_id for container_name
574 CURSOR C_del_detail (c_container_name VARCHAR2) IS
575 SELECT delivery_detail_id
576 FROM wsh_delivery_details
577 WHERE container_flag = 'Y'
578 AND container_name = c_container_name
579 AND delivery_detail_id =
580 nvl(p_exception_rec.delivery_detail_id, delivery_detail_id);
581
582 --R12 MDC
583 CURSOR c_check_consol_dlvy(p_delivery_id IN NUMBER) IS
584 SELECT delivery_id
585 FROM wsh_new_deliveries
586 WHERE delivery_id = p_delivery_id
587 AND delivery_type = 'STANDARD';
588
589 -- local variables
590 l_exception_id NUMBER DEFAULT NULL;
591 l_trip_id NUMBER DEFAULT NULL;
592 l_trip_stop_id NUMBER DEFAULT NULL;
593 l_delivery_id NUMBER DEFAULT NULL;
594 l_delivery_id_temp NUMBER DEFAULT NULL;
595 l_logged_at_location_id NUMBER;
596 l_exception_location_id NUMBER;
597 l_delivery_detail_id NUMBER;
598 l_delivery_assignment_id NUMBER;
599 l_result BOOLEAN;
600 l_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
601 l_msg_reason VARCHAR2(150);
602 l_logging_entity VARCHAR2(30);
603 l_severity VARCHAR2(30);
604
605 -- standard version information
606 l_api_version CONSTANT NUMBER := 1.0;
607 l_api_name CONSTANT VARCHAR2(30) := 'Log_Exception';
608
609 WSH_XC_INVALID_LOCATION EXCEPTION;
610 WSH_INVALID_DELIVERY_DETAIL EXCEPTION;
611 WSH_INVALID_CONTAINER_NAME EXCEPTION;
612 WSH_INVALID_TRIPNAME EXCEPTION;
613 WSH_INVALID_DELIVERY EXCEPTION;
614 WSH_INVALID_CONSOL_DELIVERY EXCEPTION;
615 WSH_INVALID_EXCEPTION_LOCATION EXCEPTION;
616 WSH_INVALID_LOGGED_AT_LOCATION EXCEPTION;
617 WSH_XC_INVALID_DATE EXCEPTION;
618 WSH_XC_INVALID_OPERATION EXCEPTION;
619 WSH_INVALID_INVENTORY_CONTROL EXCEPTION;
620 WSH_XC_LOOKUP_LOG EXCEPTION;
621 WSH_XC_LOOKUP_SEVERITY EXCEPTION;
622 WSH_MULTIPLE_CONTAINERS EXCEPTION;
623 --
624 l_debug_on BOOLEAN;
625 --
626 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_EXCEPTION';
627 --
628 BEGIN
629
630 --
631 --
632 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
633 --
634 IF l_debug_on IS NULL
635 THEN
636 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
637 END IF;
638 --
639 --
640 -- Debug Statements
641 --
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.push(l_module_name);
644 --
645 wsh_debug_sv.log(l_module_name,'p_api_version',p_api_version);
646 wsh_debug_sv.log(l_module_name,'p_init_msg_list',p_init_msg_list);
647 wsh_debug_sv.log(l_module_name,'p_validation_level',p_validation_level);
648 wsh_debug_sv.log(l_module_name,'p_commit',p_commit);
649 wsh_debug_sv.log (l_module_name,'request_id', p_exception_rec.request_id);
650 wsh_debug_sv.log (l_module_name,'exception_name',
651 p_exception_rec.exception_name);
652 wsh_debug_sv.log (l_module_name,'status', p_exception_rec.status);
653 wsh_debug_sv.log (l_module_name,'logging_entity',
654 p_exception_rec.logging_entity);
655 wsh_debug_sv.log (l_module_name,'logging_entity_id',
656 p_exception_rec.logging_entity_id);
657 wsh_debug_sv.log (l_module_name,'logged_at_location_code',
658 p_exception_rec.logged_at_location_code);
662 wsh_debug_sv.log (l_module_name,'delivery_name', p_exception_rec.delivery_name);
659 wsh_debug_sv.log (l_module_name,'exception_location_code',
660 p_exception_rec.exception_location_code);
661 wsh_debug_sv.log (l_module_name,'severity', p_exception_rec.severity);
663 wsh_debug_sv.log (l_module_name,'trip_name', p_exception_rec.trip_name);
664 wsh_debug_sv.log (l_module_name,'stop_location_id', p_exception_rec.stop_location_id);
665 wsh_debug_sv.log (l_module_name,'delivery_detail_id', p_exception_rec.delivery_detail_id);
666 END IF;
667
668 IF NOT FND_API.compatible_api_call(
669 l_api_version,
670 p_api_version,
671 l_api_name,
672 G_PKG_NAME) THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 -- Check p_init_msg_list
677 IF FND_API.to_boolean(p_init_msg_list) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682
683 -- Check for Required parameter 'exception_location'
684 IF p_exception_rec.exception_id IS NULL THEN
685 IF p_exception_rec.exception_location_code IS NULL THEN
686 l_msg_reason := 'Required parameter exception_location is missing for logging a new exception';
687 RAISE WSH_XC_INVALID_OPERATION;
688 END IF;
689 END IF;
690
691 -- Validating logging_entity
692 IF p_exception_rec.logging_entity IS NOT NULL THEN
693 --
694 -- Debug Statements
695 --
696 IF l_debug_on THEN
697 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOOKUP to Validate the logging entity',WSH_DEBUG_SV.C_PROC_LEVEL);
698 END IF;
699 --
700 WSH_UTIL_VALIDATE.Validate_Lookup('LOGGING_ENTITY',l_logging_entity,
701 p_exception_rec.logging_entity,l_return_status);
702 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
703 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
704 x_return_status := l_return_status;
705 ELSE
706 RAISE WSH_XC_LOOKUP_LOG;
707 END IF;
708 END IF;
709 END IF;
710
711 -- Validate logged_at_location
712 IF p_exception_rec.logged_at_location_code IS NOT NULL THEN
713 --
714 -- Debug Statements
715 --
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION to Validate the Logged at Location',WSH_DEBUG_SV.C_PROC_LEVEL);
718 END IF;
719 --
720 WSH_UTIL_VALIDATE.Validate_Location(
721 p_location_id => l_logged_at_location_id,
722 p_location_code => p_exception_rec.logged_at_location_code,
723 x_return_status => l_return_status);
724 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
725 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
726 x_return_status := l_return_status;
727 ELSE
728 RAISE WSH_INVALID_LOGGED_AT_LOCATION;
729 END IF;
730 END IF;
731 END IF;
732
733 -- Validate exception_location
734 IF p_exception_rec.exception_location_code IS NOT NULL THEN
735 --
736 -- Debug Statements
737 --
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION to Validate the Exception Location',WSH_DEBUG_SV.C_PROC_LEVEL);
740 END IF;
741 --
742 WSH_UTIL_VALIDATE.Validate_Location (
743 p_location_id => l_exception_location_id,
744 p_location_code => p_exception_rec.exception_location_code,
745 x_return_status => l_return_status);
746 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
747 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
748 x_return_status := l_return_status;
749 ELSE
750 RAISE WSH_INVALID_EXCEPTION_LOCATION;
751 END IF;
752 END IF;
753 END IF;
754
755 -- Validate Trip Name
756 IF p_exception_rec.trip_name IS NOT NULL THEN
757 --
758 -- Debug Statements
759 --
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_TRIP_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
762 END IF;
763 --
764 WSH_UTIL_VALIDATE.Validate_Trip_Name(
765 p_trip_id => l_trip_id,
766 p_trip_name => p_exception_rec.trip_name,
770 x_return_status := l_return_status;
767 x_return_status => l_return_status);
768 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
769 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
771 ELSE
772 RAISE WSH_INVALID_TRIPNAME;
773 END IF;
774 END IF;
775 END IF;
776
777 -- Validate stop_location
778 IF l_trip_id IS NOT NULL AND p_exception_rec.stop_location_id IS NOT NULL THEN
779 OPEN C1(l_trip_id);
780 FETCH C1 INTO l_trip_stop_id;
781 IF C1%NOTFOUND THEN
782 RAISE WSH_XC_INVALID_LOCATION;
783 END IF;
784 CLOSE C1;
785 END IF;
786
787 -- Validate delivery_name
788 IF p_exception_rec.delivery_name IS NOT NULL THEN
789 --
790 -- Debug Statements
791 --
792 IF l_debug_on THEN
793 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_DELIVERY_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
794 END IF;
795 --
796 WSH_UTIL_VALIDATE.Validate_Delivery_Name(
797 p_delivery_id => l_delivery_id,
798 p_delivery_name => p_exception_rec.delivery_name,
799 x_return_status => l_return_status);
800 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
801 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
802 x_return_status := l_return_status;
803 ELSE
804 RAISE WSH_INVALID_DELIVERY;
805 END IF;
806 END IF;
807
808 --R12 MDC
809 --Delivery must be a standard delivery
810 OPEN c_check_consol_dlvy(l_delivery_Id);
811 FETCH c_check_consol_dlvy INTO l_delivery_Id;
812 IF c_check_consol_dlvy%NOTFOUND THEN
813 CLOSE c_check_consol_dlvy;
814 RAISE WSH_INVALID_CONSOL_DELIVERY;
815 END IF;
816 CLOSE c_check_consol_dlvy;
817 END IF;
818
819 -- Validate delivery_detail_id
820 IF p_exception_rec.delivery_detail_id IS NOT NULL THEN
821 OPEN C2(l_delivery_id);
822 FETCH C2 INTO l_delivery_detail_id,l_delivery_id_temp,l_delivery_assignment_id;
823 IF C2%NOTFOUND THEN
824 RAISE WSH_INVALID_DELIVERY_DETAIL;
825 END IF;
826 CLOSE C2;
827 END IF;
828 IF l_delivery_id IS NULL THEN
829 l_delivery_id := l_delivery_id_temp;
830 END IF;
831
832 -- Get the delivery_detail_id if only Container_Name is passed
833 IF p_exception_rec.container_name IS NOT NULL THEN
834 OPEN C_del_detail(p_exception_rec.container_name);
835 FETCH C_del_detail INTO l_delivery_detail_id;
836 IF C_del_detail%NOTFOUND THEN
837 RAISE WSH_INVALID_CONTAINER_NAME;
838 END IF;
839 FETCH C_del_detail INTO l_delivery_detail_id;
840 IF C_del_detail%FOUND THEN --LPN Synch Up..samanna.to check if multiple records returned
841 RAISE WSH_MULTIPLE_CONTAINERS;
842 END IF;
843 CLOSE C_del_detail;
844
845 END IF;
846
847 -- Validate if departure date is prior to arrival_date
848 IF p_exception_rec.departure_date > p_exception_rec.arrival_date THEN
849 RAISE WSH_XC_INVALID_DATE;
850 END IF;
851
852 -- Validate inventory controls of the item
853 IF p_exception_rec.inventory_item_id IS NOT NULL THEN
854 --
855 -- Debug Statements
856 --
857 IF l_debug_on THEN
858 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
859 END IF;
860 --
861 WSH_DELIVERY_DETAILS_INV.Validate_Serial(
862 p_serial_number => p_exception_rec.serial_number,
863 p_lot_number => p_exception_rec.lot_number,
864 p_organization_id => p_exception_rec.org_id,
865 p_inventory_item_id => p_exception_rec.inventory_item_id,
866 p_subinventory => p_exception_rec.subinventory,
867 p_revision => p_exception_rec.revision,
868 p_locator_id => p_exception_rec.locator_id,
869 x_return_status => l_return_status,
870 x_result => l_result );
871 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
872 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
873 x_return_status := l_return_status;
874 ELSE
875 RAISE WSH_INVALID_INVENTORY_CONTROL;
876 END IF;
877 END IF;
878 END IF;
879
880 -- Call Private API to Log a new exception or update the existing exception
881 --
882 -- Debug Statements
883 --
887 --
884 IF l_debug_on THEN
885 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
886 END IF;
888 WSH_XC_UTIL.log_exception(
889 p_api_version => p_api_version,
890 p_init_msg_list => FND_API.G_FALSE,
891 p_commit => FND_API.G_FALSE,
892 p_validation_level => p_validation_level,
893 x_return_status => l_return_status,
894 x_msg_count => x_msg_count,
895 x_msg_data => x_msg_data,
896 x_exception_id => p_exception_rec.exception_id,
897 p_exception_location_id => l_exception_location_id,
898 p_logged_at_location_id => l_logged_at_location_id,
899 p_logging_entity => l_logging_entity,
900 p_logging_entity_id => p_exception_rec.logging_entity_id,
901 p_exception_name => p_exception_rec.exception_name,
902 p_message => p_exception_rec.message,
903 p_manually_logged => p_exception_rec.manually_logged,
904 p_trip_id => l_trip_id,
905 p_trip_name => p_exception_rec.trip_name,
906 p_trip_stop_id => l_trip_stop_id,
907 p_delivery_id => l_delivery_id,
908 p_delivery_name => p_exception_rec.delivery_name,
909 p_delivery_detail_id => p_exception_rec.delivery_detail_id,
910 p_delivery_assignment_id => l_delivery_assignment_id,
911 p_container_name => p_exception_rec.container_name,
912 p_inventory_item_id => p_exception_rec.inventory_item_id,
913 p_lot_number => p_exception_rec.lot_number,
914 -- HW OPMCONV. No need for sublot anymore
915 -- p_sublot_number => p_exception_rec.sublot_number,
916 p_revision => p_exception_rec.revision,
917 p_serial_number => p_exception_rec.serial_number,
918 p_unit_of_measure => p_exception_rec.unit_of_measure,
919 p_quantity => p_exception_rec.quantity,
920 p_unit_of_measure2 => p_exception_rec.unit_of_measure2,
921 p_quantity2 => p_exception_rec.quantity2,
922 p_subinventory => p_exception_rec.subinventory,
923 p_locator_id => p_exception_rec.locator_id,
924 p_arrival_date => p_exception_rec.arrival_date,
925 p_departure_date => p_exception_rec.departure_date,
926 p_error_message => p_exception_rec.error_message,
927 p_attribute_category => p_exception_rec.attribute_category,
928 p_attribute1 => p_exception_rec.attribute1,
929 p_attribute2 => p_exception_rec.attribute2,
930 p_attribute3 => p_exception_rec.attribute3,
931 p_attribute4 => p_exception_rec.attribute4,
932 p_attribute5 => p_exception_rec.attribute5,
933 p_attribute6 => p_exception_rec.attribute6,
934 p_attribute7 => p_exception_rec.attribute7,
935 p_attribute8 => p_exception_rec.attribute8,
936 p_attribute9 => p_exception_rec.attribute9,
937 p_attribute10 => p_exception_rec.attribute10,
938 p_attribute11 => p_exception_rec.attribute11,
939 p_attribute12 => p_exception_rec.attribute12,
940 p_attribute13 => p_exception_rec.attribute13,
941 p_attribute14 => p_exception_rec.attribute14,
942 p_attribute15 => p_exception_rec.attribute15,
943 p_request_id => p_exception_rec.request_id,
944 p_batch_id => p_exception_rec.batch_id
945 );
946
947 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
948 x_return_status := l_return_status;
949 END IF;
950
951 FND_MSG_PUB.Count_And_Get(
952 p_count => x_msg_count,
953 p_data => x_msg_data,
954 p_encoded => FND_API.G_FALSE
955 );
956 --
957 -- Debug Statements
958 --
959 IF l_debug_on THEN
960 WSH_DEBUG_SV.pop(l_module_name);
961 END IF;
962 --
963
964 EXCEPTION
965 -- LPN Synch Up ..samanna
966 WHEN WSH_MULTIPLE_CONTAINERS THEN
967 IF C_Del_Detail%ISOPEN THEN
968 close C_Del_Detail;
969 END IF;
970 FND_MESSAGE.SET_NAME('WSH','WSH_MULTIPLE_CONTAINERS');
971 FND_MESSAGE.SET_TOKEN('CONT_NAME',p_exception_rec.container_name);
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 wsh_util_core.add_message(x_return_status,l_module_name);
974 FND_MSG_PUB.Count_And_Get(
975 p_count => x_msg_count,
976 p_data => x_msg_data,
980 IF l_debug_on THEN
977 p_encoded => FND_API.G_FALSE
978 );
979 --debug messages
981 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_MULTIPLE_CONTAINERS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
982 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_MULTIPLE_CONTAINERS');
983 END IF;
984 --
985 WHEN WSH_XC_INVALID_OPERATION THEN
986 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_OPERATION');
987 FND_MESSAGE.SET_TOKEN('REASON', l_msg_reason);
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 wsh_util_core.add_message(x_return_status);
990 FND_MSG_PUB.Count_And_Get(
991 p_count => x_msg_count,
992 p_data => x_msg_data,
993 p_encoded => FND_API.G_FALSE
994 );
995 --
996 -- Debug Statements
997 --
998 IF l_debug_on THEN
999 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_OPERATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1000 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_OPERATION');
1001 END IF;
1002 --
1003
1004 WHEN WSH_XC_LOOKUP_LOG THEN
1005 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_TYPE');
1006 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE','LOGGING_ENTITY');
1007 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1008 wsh_util_core.add_message(x_return_status);
1009 FND_MSG_PUB.Count_And_Get(
1010 p_count => x_msg_count,
1011 p_data => x_msg_data,
1012 p_encoded => FND_API.G_FALSE
1013 );
1014 --
1015 -- Debug Statements
1016 --
1017 IF l_debug_on THEN
1018 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_LOOKUP_LOG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1019 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_LOOKUP_LOG');
1020 END IF;
1021 --
1022
1023 WHEN WSH_XC_LOOKUP_SEVERITY THEN
1024 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_TYPE');
1025 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE','SEVERITY');
1026 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1027 wsh_util_core.add_message(x_return_status);
1028 FND_MSG_PUB.Count_And_Get(
1029 p_count => x_msg_count,
1030 p_data => x_msg_data,
1031 p_encoded => FND_API.G_FALSE
1032 );
1033 --
1034 -- Debug Statements
1035 --
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_LOOKUP_SEVERITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1038 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_LOOKUP_SEVERITY');
1039 END IF;
1040 --
1041
1042 WHEN WSH_INVALID_TRIPNAME THEN
1043 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_INVALID_PARAMETER');
1044 FND_MESSAGE.SET_TOKEN('PARAMETER','Trip_Name');
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046 wsh_util_core.add_message(x_return_status);
1047 FND_MSG_PUB.Count_And_Get(
1048 p_count => x_msg_count,
1049 p_data => x_msg_data,
1050 p_encoded => FND_API.G_FALSE
1051 );
1052 --
1053 -- Debug Statements
1054 --
1055 IF l_debug_on THEN
1056 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_TRIPNAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1057 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_TRIPNAME');
1058 END IF;
1059 --
1060
1061 WHEN WSH_INVALID_DELIVERY THEN
1062 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_INVALID_PARAMETER');
1063 FND_MESSAGE.SET_TOKEN('PARAMETER','Delivery_Name');
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 wsh_util_core.add_message(x_return_status);
1066 FND_MSG_PUB.Count_And_Get(
1067 p_count => x_msg_count,
1068 p_data => x_msg_data,
1069 p_encoded => FND_API.G_FALSE
1070 );
1071 --
1072 -- Debug Statements
1073 --
1074 IF l_debug_on THEN
1075 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1076 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DELIVERY');
1077 END IF;
1078 --
1079 WHEN WSH_INVALID_CONSOL_DELIVERY THEN
1080 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_CONSOL_DEL_EXP');
1081 FND_MESSAGE.SET_TOKEN('PARAMETER',p_exception_rec.delivery_name);
1082 x_return_status := FND_API.G_RET_STS_ERROR;
1083 wsh_util_core.add_message(x_return_status);
1084 FND_MSG_PUB.Count_And_Get(
1085 p_count => x_msg_count,
1086 p_data => x_msg_data,
1087 p_encoded => FND_API.G_FALSE
1088 );
1089 --
1090 -- Debug Statements
1091 --
1092 IF l_debug_on THEN
1093 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONSOL_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1094 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONSOL_DELIVERY');
1095 END IF;
1096 --
1097
1098 WHEN WSH_INVALID_LOGGED_AT_LOCATION THEN
1099 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_LOGGED_AT_LOCATION');
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101 wsh_util_core.add_message(x_return_status);
1102 FND_MSG_PUB.Count_And_Get(
1103 p_count => x_msg_count,
1104 p_data => x_msg_data,
1105 p_encoded => FND_API.G_FALSE
1109 --
1106 );
1107 --
1108 -- Debug Statements
1110 IF l_debug_on THEN
1111 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_LOGGED_AT_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1112 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_LOGGED_AT_LOCATION');
1113 END IF;
1114 --
1115
1116 WHEN WSH_INVALID_EXCEPTION_LOCATION THEN
1117 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_EXCEPTION_LOCATION');
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 wsh_util_core.add_message(x_return_status);
1120 FND_MSG_PUB.Count_And_Get(
1121 p_count => x_msg_count,
1122 p_data => x_msg_data,
1123 p_encoded => FND_API.G_FALSE
1124 );
1125 --
1126 -- Debug Statements
1127 --
1128 IF l_debug_on THEN
1129 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_EXCEPTION_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1130 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_EXCEPTION_LOCATION');
1131 END IF;
1132 --
1133
1134 WHEN WSH_XC_INVALID_LOCATION THEN
1135 IF C1%ISOPEN THEN
1136 CLOSE C1;
1137 END IF;
1138 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_LOCATION');
1139 x_return_status := FND_API.G_RET_STS_ERROR;
1140 wsh_util_core.add_message(x_return_status);
1141 FND_MSG_PUB.Count_And_Get(
1142 p_count => x_msg_count,
1143 p_data => x_msg_data,
1144 p_encoded => FND_API.G_FALSE
1145 );
1146 --
1147 -- Debug Statements
1148 --
1149 IF l_debug_on THEN
1150 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1151 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_LOCATION');
1152 END IF;
1153 --
1154
1155 WHEN WSH_INVALID_DELIVERY_DETAIL THEN
1156 IF C2%ISOPEN THEN
1157 CLOSE C2;
1158 END IF;
1159 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_INVALID_PARAMETER');
1160 FND_MESSAGE.SET_TOKEN('PARAMETER', 'Delivery_Detail_Id');
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162 wsh_util_core.add_message(x_return_status);
1163 FND_MSG_PUB.Count_And_Get(
1164 p_count => x_msg_count,
1165 p_data => x_msg_data,
1166 p_encoded => FND_API.G_FALSE
1167 );
1168 --
1169 -- Debug Statements
1170 --
1171 IF l_debug_on THEN
1172 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DELIVERY_DETAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1173 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DELIVERY_DETAIL');
1174 END IF;
1175 --
1176
1177 WHEN WSH_INVALID_CONTAINER_NAME THEN
1178 IF C_del_detail%ISOPEN THEN
1179 CLOSE C_del_detail;
1180 END IF;
1181 FND_MESSAGE.SET_NAME('WSH', 'WSH_PUB_INVALID_PARAMETER');
1182 FND_MESSAGE.SET_TOKEN('PARAMETER', 'Container_Name');
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 wsh_util_core.add_message(x_return_status);
1185 FND_MSG_PUB.Count_And_Get(
1186 p_count => x_msg_count,
1187 p_data => x_msg_data,
1188 p_encoded => FND_API.G_FALSE
1189 );
1190 --
1191 -- Debug Statements
1192 --
1193 IF l_debug_on THEN
1194 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONTAINER_NAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1195 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONTAINER_NAME');
1196 END IF;
1197 --
1198
1199 WHEN WSH_INVALID_INVENTORY_CONTROL THEN
1200 x_return_status := FND_API.G_RET_STS_ERROR;
1201 --
1202 -- Debug Statements
1203 --
1204 IF l_debug_on THEN
1205 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_INVENTORY_CONTROL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1206 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_INVENTORY_CONTROL');
1207 END IF;
1208 --
1209
1210 WHEN WSH_XC_INVALID_DATE THEN
1211 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_DATE');
1212 x_return_status := FND_API.G_RET_STS_ERROR;
1213 wsh_util_core.add_message(x_return_status);
1214 FND_MSG_PUB.Count_And_Get(
1215 p_count => x_msg_count,
1216 p_data => x_msg_data,
1217 p_encoded => FND_API.G_FALSE
1218 );
1219 --
1220 -- Debug Statements
1221 --
1222 IF l_debug_on THEN
1223 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_DATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_DATE');
1225 END IF;
1226 --
1227
1228 WHEN OTHERS THEN
1229 wsh_util_core.default_handler('WSH_EXCEPTIONS_GRP.LOG_EXCEPTION');
1230 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1231 IF FND_MSG_PUB.Check_Msg_Level
1232 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1233 THEN
1234 FND_MSG_PUB.Add_Exc_Msg
1235 ( G_PKG_NAME,
1236 l_api_name
1237 );
1238 END IF;
1239 FND_MSG_PUB.Count_And_Get
1240 ( p_count => x_msg_count,
1241 p_data => x_msg_data,
1242 p_encoded => FND_API.G_FALSE
1243 );
1244
1245 --
1246 -- Debug Statements
1247 --
1248 IF l_debug_on THEN
1249 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1253
1250 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1251 END IF;
1252 --
1254 END Log_Exception;
1255
1256
1257 ------------------------------------------------------------------------------
1258 -- Procedure: Purge_Exception
1259 --
1260 -- Parameters:
1261 --
1262 -- Description: This procedure purges all exceptions which fall into the creiteria
1263 -- entered by the user.
1264 ------------------------------------------------------------------------------
1265
1266 PROCEDURE Purge_Exception (
1267 -- Standard parameters
1268 p_api_version IN NUMBER,
1269 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1270 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1271 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 x_return_status OUT NOCOPY VARCHAR2,
1275
1276 -- Program specific parameters
1277 p_exception_rec IN WSH_EXCEPTIONS_PUB.XC_ACTION_REC_TYPE,
1278 p_action IN VARCHAR2
1279 ) IS
1280
1281 -- cursor to validate the exception name
1282
1283 /*
1284
1285 -- Performance bug 4891910 : 15039503 on 12/22/2005 by parkhj
1286 -- no need to use wsh_exception_definitions_v
1287 -- since only exception_name is accessed
1288
1289 CURSOR c_exception_name IS
1290 SELECT exception_name
1291 FROM wsh_exception_definitions_v
1292 WHERE exception_name = p_exception_rec.exception_name;
1293 */
1294
1295 CURSOR c_exception_name IS
1296 SELECT exception_name
1297 FROM wsh_exception_definitions_tl
1298 WHERE exception_name = p_exception_rec.exception_name
1299 AND language = userenv('LANG');
1300
1301 l_exception_name VARCHAR2(50);
1302 l_return_status VARCHAR2(1);
1303 l_msg_data VARCHAR2(200);
1304 l_msg_count NUMBER := 0;
1305 l_no_of_records NUMBER := 0;
1306 l_delivery_id NUMBER DEFAULT NULL;
1307 l_lookup_code VARCHAR2(30) DEFAULT NULL;
1308 l_logging_entity VARCHAR2(30) DEFAULT NULL;
1309 l_exception_type VARCHAR2(30) DEFAULT NULL;
1310 l_severity VARCHAR2(30) DEFAULT NULL;
1311 l_logged_at_location_id NUMBER DEFAULT NULL;
1312 l_exception_location_id NUMBER DEFAULT NULL;
1313 l_trip_id NUMBER;
1314
1315 -- Standard call to check for call compatibility
1316 l_api_version CONSTANT NUMBER := 1.0;
1317 l_api_name CONSTANT VARCHAR2(30):= 'Purge_Exception';
1318
1319 WSH_XC_INVALID_STATUS EXCEPTION;
1320 WSH_XC_NOT_FOUND EXCEPTION;
1321 WSH_XC_INVALID_LOCATION EXCEPTION;
1322 WSH_XC_INVALID_DATE_RANGE EXCEPTION;
1323 WSH_PURGE_NOT_ALLOWED EXCEPTION;
1324 WSH_XC_INVALID_OPERATION EXCEPTION;
1325 WSH_XC_LOOKUP_LOG EXCEPTION;
1326 WSH_XC_LOOKUP_EXCEPTION EXCEPTION;
1327 WSH_XC_LOOKUP_SEVERITY EXCEPTION;
1328 WSH_INVALID_LOGGED_AT_LOCATION EXCEPTION;
1329 WSH_INVALID_EXCEPTION_LOCATION EXCEPTION;
1330 WSH_XC_INVALID_DELIVERY EXCEPTION;
1331
1332 --
1333 l_debug_on BOOLEAN;
1334 --
1335 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_EXCEPTION';
1336 --
1337 BEGIN
1338
1339 --
1340 --
1341 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1342 --
1343 IF l_debug_on IS NULL
1344 THEN
1345 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1346 END IF;
1347 --
1348 --
1349 -- Debug Statements
1350 --
1351 IF l_debug_on THEN
1352 wsh_debug_sv.push(l_module_name);
1353 --
1354 wsh_debug_sv.log(l_module_name,'p_api_version',p_api_version);
1355 wsh_debug_sv.log(l_module_name,'p_init_msg_list',p_init_msg_list);
1356 wsh_debug_sv.log(l_module_name,'p_validation_level',p_validation_level);
1357 wsh_debug_sv.log(l_module_name,'p_commit',p_commit);
1358 wsh_debug_sv.log(l_module_name,'p_action',p_action);
1359 wsh_debug_sv.log (l_module_name,'request_id', p_exception_rec.request_id);
1360 wsh_debug_sv.log (l_module_name,'exception_id',
1361 p_exception_rec.exception_id);
1362 wsh_debug_sv.log (l_module_name,'exception_name',
1363 p_exception_rec.exception_name);
1364 wsh_debug_sv.log (l_module_name,'status', p_exception_rec.status);
1365 wsh_debug_sv.log (l_module_name,'logging_entity',
1366 p_exception_rec.logging_entity);
1367 wsh_debug_sv.log (l_module_name,'logged_at_location_code',
1368 p_exception_rec.logged_at_location_code);
1369 wsh_debug_sv.log (l_module_name,'exception_location_code',
1370 p_exception_rec.exception_location_code);
1374 END IF;
1371 wsh_debug_sv.log (l_module_name,'severity', p_exception_rec.severity);
1372 wsh_debug_sv.log (l_module_name,'delivery_name', p_exception_rec.delivery_name);
1373 wsh_debug_sv.log (l_module_name,'data_older_no_of_days', p_exception_rec.data_older_no_of_days);
1375
1376 IF NOT FND_API.compatible_api_call (
1377 l_api_version,
1378 p_api_version,
1379 l_api_name,
1380 G_PKG_NAME) THEN
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 END IF;
1383
1384 -- Check p_init_msg_list
1385 IF FND_API.to_boolean(p_init_msg_list) THEN
1386 FND_MSG_PUB.initialize;
1387 END IF;
1388
1389 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1390
1391 IF ( p_exception_rec.request_id IS NULL AND
1392 p_exception_rec.exception_name IS NULL AND
1393 p_exception_rec.logging_entity IS NULL AND
1394 p_exception_rec.logged_at_location_code IS NULL AND
1395 p_exception_rec.exception_location_code IS NULL AND
1396 p_exception_rec.exception_type IS NULL AND
1397 p_exception_rec.severity IS NULL AND
1398 p_exception_rec.status IS NULL AND
1399 p_exception_rec.arrival_date IS NULL AND
1400 p_exception_rec.arrival_date_to IS NULL AND
1401 p_exception_rec.departure_date IS NULL AND
1402 p_exception_rec.departure_date_to IS NULL AND
1403 p_exception_rec.creation_date IS NULL AND
1404 p_exception_rec.creation_date_to IS NULL AND
1405 p_exception_rec.delivery_name IS NULL AND
1406 NVL(p_exception_rec.data_older_no_of_days,0) = 0 ) THEN
1407 RAISE WSH_XC_INVALID_OPERATION;
1408 END IF;
1409
1410 -- Validating status of the exception
1411 IF (p_exception_rec.status IS NOT NULL and p_exception_rec.status NOT IN
1412 ('CLOSED','ERROR','IN_PROCESS','LOGGED','MANUAL','NO_ACTION_REQUIRED','NOT_HANDLED','OPEN')) THEN
1413 RAISE WSH_XC_INVALID_STATUS;
1414 END IF;
1415
1416 -- Validating the status of the exception if the action is PURGE
1417 IF ( p_exception_rec.status IS NOT NULL AND
1418 p_exception_rec.status <> 'CLOSED') THEN
1419 RAISE WSH_PURGE_NOT_ALLOWED;
1420 END IF;
1421
1422 -- Validating the name of the exception
1423 IF ( p_exception_rec.exception_name IS NOT NULL ) THEN
1424 OPEN c_exception_name;
1425 FETCH c_exception_name into l_exception_name;
1426 IF c_exception_name%NOTFOUND THEN
1427 RAISE WSH_XC_NOT_FOUND;
1428 END IF;
1429 CLOSE c_exception_name;
1430 END IF;
1431
1432 -- Validating logging_entity
1433 IF p_exception_rec.logging_entity IS NOT NULL THEN
1434 --
1435 -- Debug Statements
1436 --
1437 IF l_debug_on THEN
1438 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOOKUP to Validate the loggint entity',WSH_DEBUG_SV.C_PROC_LEVEL);
1439 END IF;
1440 --
1441 WSH_UTIL_VALIDATE.Validate_Lookup('LOGGING_ENTITY',l_logging_entity,
1442 p_exception_rec.logging_entity,l_return_status);
1443 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1444 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1445 x_return_status := l_return_status;
1446 ELSE
1447 RAISE WSH_XC_LOOKUP_LOG;
1448 END IF;
1449 END IF;
1450 END IF;
1451
1452 -- Validating type of the exception
1453 IF p_exception_rec.exception_type IS NOT NULL THEN
1454 --
1455 -- Debug Statements
1456 --
1457 IF l_debug_on THEN
1458 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOOKUPto Validate the Exception Type',WSH_DEBUG_SV.C_PROC_LEVEL);
1459 END IF;
1460 --
1461 WSH_UTIL_VALIDATE.Validate_Lookup('EXCEPTION_TYPE',l_exception_type,
1462 p_exception_rec.exception_type,l_return_status);
1463 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1464 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1465 x_return_status := l_return_status;
1466 ELSE
1467 RAISE WSH_XC_LOOKUP_EXCEPTION;
1468 END IF;
1469 END IF;
1470 END IF;
1471
1472 -- Validating severity of the exception
1473 IF p_exception_rec.severity IS NOT NULL THEN
1474 --
1475 -- Debug Statements
1476 --
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOOKUPto Validate the Severity',WSH_DEBUG_SV.C_PROC_LEVEL);
1479 END IF;
1480 --
1481 WSH_UTIL_VALIDATE.Validate_Lookup('EXCEPTION_BEHAVIOR',l_severity,
1485 x_return_status := l_return_status;
1482 p_exception_rec.severity,l_return_status);
1483 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1484 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1486 ELSE
1487 RAISE WSH_XC_LOOKUP_SEVERITY;
1488 END IF;
1489 END IF;
1490 END IF;
1491
1492 -- Validating logged_at_location of the exception
1493 IF p_exception_rec.logged_at_location_code IS NOT NULL THEN
1494 --
1495 -- Debug Statements
1496 --
1497 IF l_debug_on THEN
1498 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION to Validate the Logged at Location',WSH_DEBUG_SV.C_PROC_LEVEL);
1499 END IF;
1500 --
1501 WSH_UTIL_VALIDATE.Validate_Location(l_logged_at_location_id,
1502 p_exception_rec.logged_at_location_code,l_return_status);
1503 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1504 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1505 x_return_status := l_return_status;
1506 ELSE
1507 RAISE WSH_INVALID_LOGGED_AT_LOCATION;
1508 END IF;
1509 END IF;
1510 END IF;
1511
1512 -- Validating the location where exception has happened
1513 IF p_exception_rec.exception_location_code IS NOT NULL THEN
1514 --
1515 -- Debug Statements
1516 --
1517 IF l_debug_on THEN
1518 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION to Validate Exception Location',WSH_DEBUG_SV.C_PROC_LEVEL);
1519 END IF;
1520 --
1521 WSH_UTIL_VALIDATE.Validate_Location(l_exception_location_id,
1522 p_exception_rec.exception_location_code,l_return_status);
1523 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1524 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1525 x_return_status := l_return_status;
1526 ELSE
1527 RAISE WSH_INVALID_EXCEPTION_LOCATION;
1528 END IF;
1529 END IF;
1530 END IF;
1531
1532 -- Validating the delivery name
1533 IF p_exception_rec.delivery_name IS NOT NULL THEN
1534 --
1535 -- Debug Statements
1536 --
1537 IF l_debug_on THEN
1538 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_DELIVERY_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1539 END IF;
1540 --
1541 WSH_UTIL_VALIDATE.Validate_Delivery_Name(l_delivery_id,
1542 p_exception_rec.delivery_name,l_return_status);
1543 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1544 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1545 x_return_status := l_return_status;
1546 ELSE
1547 RAISE WSH_XC_INVALID_DELIVERY;
1548 END IF;
1549 END IF;
1550 END IF;
1551
1552 -- Validating if to_date is greater than from_date
1553 IF (p_exception_rec.departure_date > p_exception_rec.departure_date_to OR
1554 p_exception_rec.arrival_date > p_exception_rec.arrival_date_to OR
1555 p_exception_rec.creation_date > p_exception_rec.creation_date_to) THEN
1556 RAISE WSH_XC_INVALID_DATE_RANGE;
1557 END IF;
1558
1559 -- Call the Private API to Purge the Exceptions
1560 --
1561 -- Debug Statements
1562 --
1563 IF l_debug_on THEN
1564 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.PURGE',WSH_DEBUG_SV.C_PROC_LEVEL);
1565 END IF;
1566 --
1567 WSH_XC_UTIL.Purge(
1568 p_api_version => p_api_version,
1569 p_init_msg_list => FND_API.G_FALSE,
1570 p_commit => FND_API.G_FALSE,
1571 p_validation_level => p_validation_level,
1572 x_return_status => l_return_status,
1573 x_msg_count => x_msg_count,
1574 x_msg_data => x_msg_data,
1575 p_request_id => p_exception_rec.request_id,
1576 p_exception_name => p_exception_rec.exception_name,
1577 p_logging_entity => l_logging_entity,
1578 p_logged_at_location_id => l_logged_at_location_id,
1579 p_exception_location_id => l_exception_location_id,
1580 -- p_inventory_org_id => inventory_org_id,
1581 p_exception_type => l_exception_type,
1582 p_severity => l_severity,
1583 p_status => p_exception_rec.status,
1584 p_arrival_date_from => p_exception_rec.arrival_date,
1585 p_arrival_date_to => p_exception_rec.arrival_date_to,
1586 p_departure_date_from => p_exception_rec.departure_date,
1590 p_data_older_no_of_days => p_exception_rec.data_older_no_of_days,
1587 p_departure_date_to => p_exception_rec.departure_date_to,
1588 p_creation_date_from => p_exception_rec.creation_date,
1589 p_creation_date_to => p_exception_rec.creation_date_to,
1591 x_no_of_recs_purged => l_no_of_records,
1592 p_delivery_id => l_delivery_id,
1593 p_action => p_action
1594 );
1595
1596 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1597 FND_MESSAGE.SET_NAME('WSH','WSH_NO_OF_RECS_PURGED');
1598 FND_MESSAGE.SET_TOKEN('NO_OF_RECS',l_no_of_records);
1599 wsh_util_core.add_message(l_return_status);
1600 END IF;
1601
1602 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1603 x_return_status := l_return_status;
1604 END IF;
1605
1606 FND_MSG_PUB.Count_And_Get(
1607 p_count => x_msg_count,
1608 p_data => x_msg_data,
1609 p_encoded => FND_API.G_FALSE
1610 );
1611 --
1612 -- Debug Statements
1613 --
1614 IF l_debug_on THEN
1615 WSH_DEBUG_SV.pop(l_module_name);
1616 END IF;
1617 --
1618
1619 EXCEPTION
1620 WHEN WSH_XC_NOT_FOUND THEN
1621 IF c_exception_name%ISOPEN THEN
1622 CLOSE c_exception_name;
1623 END IF;
1624 FND_MESSAGE.SET_NAME('WSH','WSH_XC_DEF_NOT_FOUND');
1625 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1626 wsh_util_core.add_message(x_return_status);
1627 FND_MSG_PUB.Count_And_Get(
1628 p_count => x_msg_count,
1629 p_data => x_msg_data,
1630 p_encoded => FND_API.G_FALSE
1631 );
1632 --
1633 -- Debug Statements
1634 --
1635 IF l_debug_on THEN
1636 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1637 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_NOT_FOUND');
1638 END IF;
1639 --
1640
1641 WHEN WSH_XC_INVALID_STATUS THEN
1642 FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_STATUS');
1643 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1644 wsh_util_core.add_message(x_return_status);
1645 FND_MSG_PUB.Count_And_Get(
1646 p_count => x_msg_count,
1647 p_data => x_msg_data,
1648 p_encoded => FND_API.G_FALSE
1649 );
1650 --
1651 -- Debug Statements
1652 --
1653 IF l_debug_on THEN
1654 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_STATUS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1655 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_STATUS');
1656 END IF;
1657 --
1658
1659 WHEN WSH_PURGE_NOT_ALLOWED THEN
1660 FND_MESSAGE.SET_NAME('WSH','WSH_PURGE_NOT_ALLOWED');
1661 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1662 wsh_util_core.add_message(x_return_status);
1663 FND_MSG_PUB.Count_And_Get(
1664 p_count => x_msg_count,
1665 p_data => x_msg_data,
1666 p_encoded => FND_API.G_FALSE
1667 );
1668 --
1669 -- Debug Statements
1670 --
1671 IF l_debug_on THEN
1672 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_PURGE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1673 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_PURGE_NOT_ALLOWED');
1674 END IF;
1675 --
1676
1677 WHEN WSH_XC_INVALID_DATE_RANGE THEN
1678 FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_DATE_RANGE');
1679 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1680 wsh_util_core.add_message(x_return_status);
1681 FND_MSG_PUB.Count_And_Get(
1682 p_count => x_msg_count,
1683 p_data => x_msg_data,
1684 p_encoded => FND_API.G_FALSE
1685 );
1686 --
1687 -- Debug Statements
1688 --
1689 IF l_debug_on THEN
1690 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_DATE_RANGE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1691 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_DATE_RANGE');
1692 END IF;
1693 --
1694
1695 WHEN WSH_XC_INVALID_OPERATION THEN
1696 FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_OPERATION');
1697 FND_MESSAGE.SET_TOKEN('REASON','Trying to Purge All Records',TRUE);
1698 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1699 wsh_util_core.add_message(x_return_status);
1700 FND_MSG_PUB.Count_And_Get(
1701 p_count => x_msg_count,
1702 p_data => x_msg_data,
1703 p_encoded => FND_API.G_FALSE
1704 );
1705 --
1706 -- Debug Statements
1707 --
1708 IF l_debug_on THEN
1709 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_OPERATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1710 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_OPERATION');
1711 END IF;
1712 --
1713
1714 WHEN WSH_XC_LOOKUP_LOG THEN
1715 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_TYPE');
1716 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE','LOGGING_ENTITY');
1720 p_count => x_msg_count,
1717 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1718 wsh_util_core.add_message(x_return_status);
1719 FND_MSG_PUB.Count_And_Get(
1721 p_data => x_msg_data,
1722 p_encoded => FND_API.G_FALSE
1723 );
1724 --
1725 -- Debug Statements
1726 --
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_LOOKUP_LOG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1729 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_LOOKUP_LOG');
1730 END IF;
1731 --
1732
1733 WHEN WSH_XC_LOOKUP_EXCEPTION THEN
1734 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_TYPE');
1735 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE','EXCEPTION_TYPE');
1736 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1737 wsh_util_core.add_message(x_return_status);
1738 FND_MSG_PUB.Count_And_Get(
1739 p_count => x_msg_count,
1740 p_data => x_msg_data,
1741 p_encoded => FND_API.G_FALSE
1742 );
1743 --
1744 -- Debug Statements
1745 --
1746 IF l_debug_on THEN
1747 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_LOOKUP_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_LOOKUP_EXCEPTION');
1749 END IF;
1750 --
1751
1752 WHEN WSH_XC_LOOKUP_SEVERITY THEN
1753 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_TYPE');
1754 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE','EXCEPTION_SEVERITY');
1755 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756 wsh_util_core.add_message(x_return_status);
1757 FND_MSG_PUB.Count_And_Get(
1758 p_count => x_msg_count,
1759 p_data => x_msg_data,
1760 p_encoded => FND_API.G_FALSE
1761 );
1762 --
1763 -- Debug Statements
1764 --
1765 IF l_debug_on THEN
1766 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_LOOKUP_SEVERITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1767 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_LOOKUP_SEVERITY');
1768 END IF;
1769 --
1770
1771 WHEN WSH_INVALID_LOGGED_AT_LOCATION THEN
1772 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOGGED_AT_LOCATION');
1773 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1774 wsh_util_core.add_message(x_return_status);
1775 FND_MSG_PUB.Count_And_Get(
1776 p_count => x_msg_count,
1777 p_data => x_msg_data,
1778 p_encoded => FND_API.G_FALSE
1779 );
1780 --
1781 -- Debug Statements
1782 --
1783 IF l_debug_on THEN
1784 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_LOGGED_AT_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1785 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_LOGGED_AT_LOCATION');
1786 END IF;
1787 --
1788
1789 WHEN WSH_INVALID_EXCEPTION_LOCATION THEN
1790 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_EXCEPTION_LOCATION');
1791 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1792 wsh_util_core.add_message(x_return_status);
1793 FND_MSG_PUB.Count_And_Get(
1794 p_count => x_msg_count,
1795 p_data => x_msg_data,
1796 p_encoded => FND_API.G_FALSE
1797 );
1798 --
1799 -- Debug Statements
1800 --
1801 IF l_debug_on THEN
1802 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_EXCEPTION_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1803 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_EXCEPTION_LOCATION');
1804 END IF;
1805 --
1806
1807 WHEN WSH_XC_INVALID_DELIVERY THEN
1808 FND_MESSAGE.SET_NAME('WSH', 'WSH_OI_INVALID_DEL_NAME');
1809 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1810 wsh_util_core.add_message(x_return_status);
1811 FND_MSG_PUB.Count_And_Get(
1812 p_count => x_msg_count,
1813 p_data => x_msg_data,
1814 p_encoded => FND_API.G_FALSE
1815 );
1816 --
1817 -- Debug Statements
1818 --
1819 IF l_debug_on THEN
1820 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1821 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_DELIVERY');
1822 END IF;
1823 --
1824
1825 WHEN OTHERS THEN
1826 WSH_UTIL_CORE.default_handler('WSH_EXCEPTIONS_GRP.EXCEPTION_ACTION');
1827 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1828 IF FND_MSG_PUB.Check_Msg_Level
1829 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1830 THEN
1831 FND_MSG_PUB.Add_Exc_Msg
1832 ( G_PKG_NAME,
1833 l_api_name
1834 );
1835 END IF;
1836 FND_MSG_PUB.Count_And_Get
1837 ( p_count => x_msg_count,
1838 p_data => x_msg_data,
1839 p_encoded => FND_API.G_FALSE
1840 );
1841 --
1842 -- Debug Statements
1843 --
1844 IF l_debug_on THEN
1845 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1846 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1847 END IF;
1848 --
1849
1850 END Purge_Exception;
1851
1855 --
1852
1853 ------------------------------------------------------------------------------
1854 -- Procedure: Exception_Action
1856 -- Parameters:
1857 --
1858 -- Description: This procedure calls the corresponding procedures to Log,
1859 -- Purge and Change_Status of the exceptions based on the action
1860 -- code it receives through the parameter p_action.
1861 ------------------------------------------------------------------------------
1862
1863 PROCEDURE Exception_Action (
1864 -- Standard parameters
1865 p_api_version IN NUMBER,
1866 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1867 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1868 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1869 x_msg_count OUT NOCOPY NUMBER,
1870 x_msg_data OUT NOCOPY VARCHAR2,
1871 x_return_status OUT NOCOPY VARCHAR2,
1872
1873 -- Program specific parameters
1874 p_exception_rec IN OUT NOCOPY WSH_EXCEPTIONS_PUB.XC_ACTION_REC_TYPE,
1875 p_action IN VARCHAR2
1876 ) IS
1877
1878 l_action VARCHAR2(20);
1879 l_updated_rows NUMBER;
1880 l_return_status VARCHAR2(1);
1881
1882 -- Standard call to check for call compatibility
1883 l_api_version CONSTANT NUMBER := 1.0;
1884 l_api_name CONSTANT VARCHAR2(30):= 'Exception_Action';
1885
1886 WSH_INVALID_ACTION_CODE EXCEPTION;
1887
1888 --
1889 l_debug_on BOOLEAN;
1890 --
1891 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXCEPTION_ACTION';
1892 --
1893 BEGIN
1894
1895 --
1896 --
1897 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1898 --
1899 IF l_debug_on IS NULL
1900 THEN
1901 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1902 END IF;
1903 --
1904 --
1905 -- Debug Statements
1906 --
1907 IF l_debug_on THEN
1908 WSH_DEBUG_SV.push(l_module_name);
1909 --
1910 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1911 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1912 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1913 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1914 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
1915 END IF;
1916
1917 IF NOT FND_API.compatible_api_call(
1918 l_api_version,
1919 p_api_version,
1920 l_api_name,
1921 G_PKG_NAME) THEN
1922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1923 END IF;
1924
1925 -- Check p_init_msg_list
1926 IF FND_API.to_boolean(p_init_msg_list) THEN
1927 FND_MSG_PUB.initialize;
1928 END IF;
1929
1930 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1931
1932 l_action := UPPER(p_action);
1933 IF (l_action NOT IN ( 'CHANGE_STATUS','PURGE','LOG' )) THEN
1934 RAISE WSH_INVALID_ACTION_CODE;
1935 END IF;
1936
1937 -- Call the private API if the action requested is PURGE
1938 IF l_action = 'PURGE' THEN
1939 Purge_Exception(
1943 p_commit => FND_API.G_FALSE,
1940 p_api_version => p_api_version,
1941 p_init_msg_list => FND_API.G_FALSE,
1942 p_validation_level => p_validation_level,
1944 x_msg_count => x_msg_count,
1945 x_msg_data => x_msg_data,
1946 x_return_status => l_return_status,
1947
1948 -- program specific parameters
1949 p_exception_rec => p_exception_rec,
1950 p_action => l_action
1951 );
1952
1953 END IF;
1954
1955 -- Call the private API if the action requested is LOG
1956 IF l_action = 'LOG' THEN
1957 Log_Exception(
1958 p_api_version => p_api_version,
1959 p_init_msg_list => FND_API.G_FALSE,
1960 p_validation_level => p_validation_level,
1961 p_commit => FND_API.G_FALSE,
1962 x_msg_count => x_msg_count,
1963 x_msg_data => x_msg_data,
1964 x_return_status => l_return_status,
1965
1966 -- program specific parameters
1967 p_exception_rec => p_exception_rec
1968 );
1969 END IF;
1970
1971 -- Call the private API if the action requested is CHANGE_STATUS
1972 IF l_action = 'CHANGE_STATUS' THEN
1973 Change_Status(
1974 p_api_version => p_api_version,
1975 p_init_msg_list => FND_API.G_FALSE,
1976 p_validation_level => p_validation_level,
1977 p_commit => FND_API.G_FALSE,
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data,
1980 x_return_status => l_return_status,
1981
1982 -- program specific parameters
1983 p_logging_entity_id => p_exception_rec.logging_entity_id,
1984 p_logging_entity_name => p_exception_rec.logging_entity,
1985 p_exception_name => p_exception_rec.exception_name,
1986 p_exception_id => p_exception_rec.exception_id,
1987 p_new_status => p_exception_rec.new_status,
1988 x_updated_rows => l_updated_rows
1989 );
1990 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1991 FND_MESSAGE.SET_NAME('WSH','WSH_NO_OF_RECS_UPDATED');
1992 FND_MESSAGE.SET_TOKEN('NO_OF_RECS',l_updated_rows);
1993 END IF;
1994 END IF;
1995
1996 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1997 x_return_status := l_return_status;
1998 END IF;
1999
2000 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) AND
2001 FND_API.To_Boolean(p_commit) THEN
2002 COMMIT WORK;
2003 END IF;
2004
2005 FND_MSG_PUB.Count_And_Get(
2006 p_count => x_msg_count,
2007 p_data => x_msg_data,
2008 p_encoded => FND_API.G_FALSE
2009 );
2010 --
2011 -- Debug Statements
2012 --
2013 IF l_debug_on THEN
2014 WSH_DEBUG_SV.pop(l_module_name);
2015 END IF;
2016 --
2017
2018 EXCEPTION
2019 WHEN WSH_INVALID_ACTION_CODE THEN
2020 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
2021 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action);
2022 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2023 wsh_util_core.add_message(x_return_status);
2024 FND_MSG_PUB.Count_And_Get(
2025 p_count => x_msg_count,
2026 p_data => x_msg_data,
2027 p_encoded => FND_API.G_FALSE
2028 );
2029 --
2030 -- Debug Statements
2031 --
2032 IF l_debug_on THEN
2033 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_ACTION_CODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2034 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ACTION_CODE');
2035 END IF;
2036 --
2037
2038 WHEN OTHERS THEN
2039 WSH_UTIL_CORE.default_handler('WSH_EXCEPTIONS_GRP.EXCEPTION_ACTION');
2040 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2041 IF FND_MSG_PUB.Check_Msg_Level
2042 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2043 THEN
2044 FND_MSG_PUB.Add_Exc_Msg
2045 ( G_PKG_NAME,
2046 l_api_name
2047 );
2048 END IF;
2049 FND_MSG_PUB.Count_And_Get
2050 ( p_count => x_msg_count,
2051 p_data => x_msg_data,
2052 p_encoded => FND_API.G_FALSE
2053 );
2054 --
2055 -- Debug Statements
2056 --
2057 IF l_debug_on THEN
2058 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2059 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2060 END IF;
2061 --
2062
2063 END Exception_Action;
2064
2065
2066 END WSH_EXCEPTIONS_GRP;