1 PACKAGE BODY WSH_XC_UTIL as
2 /* $Header: WSHXCUTB.pls 120.12.12010000.2 2008/08/04 12:34:27 suppal ship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_XC_UTIL';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8
9 C_ACTION_SEMICLOSED CONSTANT VARCHAR2(11) := 'SEMI-CLOSED'; -- FP bug 4370532
10 C_STATUS_CLOSED CONSTANT VARCHAR2(6) := 'CLOSED';
11 C_SEVERITY_INFO CONSTANT VARCHAR2(4) := 'INFO';
12
13 C_OTM_EXC_NAME CONSTANT VARCHAR2(22) := 'WSH_OTM_SHIPMENT_ERROR';
14
15 -- exception shared between validate_location_id() and
16 -- log_exception()
17
18 WSH_XC_INVALID_LOCATION EXCEPTION;
19
20 -- bug 5183769 : BEGIN
21 -- following types/variable are added to cache Exception Definitions
22
23 TYPE Exception_Def_Rec_Typ IS RECORD(
24 DEFAULT_SEVERITY WSH_EXCEPTION_DEFINITIONS_VL.DEFAULT_SEVERITY%TYPE,
25 EXCEPTION_HANDLING WSH_EXCEPTION_DEFINITIONS_VL.EXCEPTION_HANDLING%TYPE,
26 INITIATE_WORKFLOW WSH_EXCEPTION_DEFINITIONS_VL.INITIATE_WORKFLOW%TYPE,
27 ENABLED WSH_EXCEPTION_DEFINITIONS_VL.ENABLED%TYPE
28 );
29
30 TYPE Exception_Def_Tab_Typ IS TABLE OF Exception_Def_Rec_Typ INDEX BY VARCHAR2(30);
31
32 g_exception_defs Exception_Def_Tab_Typ;
33
34 -- bug 5183769 : END
35
36 -- -------------------------------------------------------------------
37 -- Start of comments
38 -- API name : Get_Exception_Def
39 -- Type : private
40 -- Function : return the attributes for the given exception name
41 -- added to fix bug 5183769(Caching of Exception Definitions)
42 --
43 -- IN Parameters : p_exception_name Exception Name
44 -- OUT Parameters : x_exception_def_info Record of Exception Attributes
45 -- x_return_status Return Status
46 -- End of comments
47 -- ---------------------------------------------------------------------
48 PROCEDURE Get_Exception_Def(
49 p_exception_name IN VARCHAR2,
50 x_exception_def_info OUT NOCOPY Exception_Def_Rec_Typ,
51 x_return_status OUT NOCOPY VARCHAR2) IS
52
53 -- cursor to get exception attributes
54 CURSOR C_EXC_DEF(c_exception_name VARCHAR2) IS
55 SELECT UPPER(default_severity),
56 UPPER(exception_handling),
57 UPPER(initiate_workflow),
58 enabled
59 FROM wsh_exception_definitions_vl
60 WHERE exception_name = c_exception_name;
61
62 l_debug_on BOOLEAN;
63 l_msg_data VARCHAR(2000);
64 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EXCEPTION_DEF';
65
66 BEGIN
67
68 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
69 --
70 IF l_debug_on IS NULL
71 THEN
72 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
73 END IF;
74 --
75 IF l_debug_on THEN
76 WSH_DEBUG_SV.push(l_module_name);
77 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME', p_exception_name);
78 END IF;
79 --
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 -- Check if exception definition is already cached
83 IF (g_exception_defs.count <> 0 AND
84 g_exception_defs.EXISTS(p_exception_name)) THEN
85
86 x_exception_def_info := g_exception_defs(p_exception_name);
87
88 IF l_debug_on THEN
89 WSH_DEBUG_SV.log(l_module_name,'CACHED:EXCEPTION_NAME ',p_exception_name);
90 WSH_DEBUG_SV.pop(l_module_name);
91 END IF;
92
93 RETURN;
94
95 END IF;
96
97 -- Need to fetch and cache exception definition info
98
99 OPEN c_exc_def(p_exception_name);
100 FETCH c_exc_def INTO x_exception_def_info;
101
102 IF c_exc_def%NOTFOUND THEN
103 RAISE NO_DATA_FOUND;
104 END IF;
105
106 CLOSE c_exc_def;
107 g_exception_defs(p_exception_name) := x_exception_def_info;
108
109 -- Debug Statements
110 IF l_debug_on THEN
111 WSH_DEBUG_SV.log(l_module_name,'CACHING:EXCEPTION_NAME ',p_exception_name);
112 WSH_DEBUG_SV.log(l_module_name,'CACHING:EXCEPTION_HANDLING ',g_exception_defs(p_exception_name).exception_handling);
113 WSH_DEBUG_SV.log(l_module_name,'CACHING:INITIATE_WORKFLOW ',g_exception_defs(p_exception_name).initiate_workflow);
114 WSH_DEBUG_SV.log(l_module_name,'CACHING:DEFAULT_SEVERITY ',g_exception_defs(p_exception_name).default_severity);
115 WSH_DEBUG_SV.log(l_module_name,'CACHING:ENABLED ',g_exception_defs(p_exception_name).enabled);
116 WSH_DEBUG_SV.pop(l_module_name);
117 END IF;
118
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 FND_MESSAGE.Set_Name('WSH', 'WSH_XC_DEF_NOT_FOUND');
122 IF c_exc_def%ISOPEN THEN
123 CLOSE c_exc_def;
124 END IF;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 IF l_debug_on THEN
127 WSH_DEBUG_SV.log(l_module_name,'ERROR: ','Exception Not Defined:'||p_exception_name);
128 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
129 END IF;
130 -- end of NO_DATA_FOUND exception handling
131 WHEN OTHERS THEN
132 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
133 FND_MESSAGE.Set_Token('PACKAGE','WSH_XC_UTIL.GET_EXCEPTION_DEF');
134 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
135 FND_MESSAGE.Set_Token('ORA_TEXT','');
136 IF c_exc_def%ISOPEN THEN
137 CLOSE c_exc_def;
138 END IF;
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 --
141 -- Debug Statements
142 --
143 IF l_debug_on THEN
144 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
145 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
146 END IF;
147 -- end of OTHERS exception handling
148 END Get_Exception_Def;
149
150 -- -----------------------------------------------------------------
151 -- Start of comments
152 -- API name : log_exception
153 -- Type : Public
154 -- Function : This function is to log a new exception or to restrictly
155 -- update an existing exception. Update is allowed only
156 -- for the fields which are NULL due to the nature of
157 -- exceptions. The update function is mostly useful to
158 -- add exception name to an open exception
159 -- Pre-reqs : None
160 -- Version : Initial version 1.0
161 -- Notes : please use :set tabstop=3 to view this file in vi to get
162 -- proper alignment
163 --
164 -- BUG#: 1549665 hwahdani : added p_request_id parameter
165 -- BUG#: 1900149 hwahdani : added opm attributes to the parameter list
166 -- BUG#: 1729516 rvarghes : added batch_id (Picking Batch id)
167 -- End of comments
168 -- ------------------------------------------------------------------
169 PROCEDURE log_exception(
170
171 -- Standard parameters
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2,
174 p_commit IN VARCHAR2,
175 p_validation_level IN NUMBER,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2,
179
180 -- Program Specific parameters
181 x_exception_id IN OUT NOCOPY NUMBER,
182 p_exception_location_id IN NUMBER,
183 p_logged_at_location_id IN NUMBER,
184 p_logging_entity IN VARCHAR2,
185 p_logging_entity_id IN NUMBER,
186 p_exception_name IN VARCHAR2,
187 p_message IN VARCHAR2,
188 p_severity IN VARCHAR2,
189 p_manually_logged IN VARCHAR2,
190 p_exception_handling IN VARCHAR2,
191 p_trip_id IN NUMBER,
192 p_trip_name IN VARCHAR2,
193 p_trip_stop_id IN NUMBER,
194 p_delivery_id IN NUMBER,
195 p_delivery_name IN VARCHAR2,
196 p_delivery_detail_id IN NUMBER,
197 p_delivery_assignment_id IN NUMBER,
198 p_container_name IN VARCHAR2,
199 p_inventory_item_id IN NUMBER,
200 p_lot_number IN VARCHAR2,
201 -- HW OPMCONV - No need for sublot_number
202 -- p_sublot_number IN VARCHAR2,
203 p_revision IN VARCHAR2,
204 p_serial_number IN VARCHAR2,
205 p_unit_of_measure IN VARCHAR2,
206 p_quantity IN NUMBER,
207 p_unit_of_measure2 IN VARCHAR2,
208 p_quantity2 IN NUMBER,
209 p_subinventory IN VARCHAR2,
210 p_locator_id IN NUMBER,
211 p_arrival_date IN DATE,
212 p_departure_date IN DATE,
213 p_error_message IN VARCHAR2,
214 p_attribute_category IN VARCHAR2,
215 p_attribute1 IN VARCHAR2,
216 p_attribute2 IN VARCHAR2,
217 p_attribute3 IN VARCHAR2,
218 p_attribute4 IN VARCHAR2,
219 p_attribute5 IN VARCHAR2,
220 p_attribute6 IN VARCHAR2,
221 p_attribute7 IN VARCHAR2,
222 p_attribute8 IN VARCHAR2,
223 p_attribute9 IN VARCHAR2,
224 p_attribute10 IN VARCHAR2,
225 p_attribute11 IN VARCHAR2,
226 p_attribute12 IN VARCHAR2,
227 p_attribute13 IN VARCHAR2,
228 p_attribute14 IN VARCHAR2,
229 p_attribute15 IN VARCHAR2,
230 p_request_id IN NUMBER,
231 p_batch_id IN NUMBER,
232 --bug 3362060
233 p_creation_date IN DATE DEFAULT NULL,
234 p_created_by IN NUMBER DEFAULT NULL,
235 p_last_update_date IN DATE DEFAULT NULL,
236 p_last_updated_by IN NUMBER DEFAULT NULL,
237 p_last_update_login IN NUMBER DEFAULT NULL,
238 p_program_application_id IN NUMBER DEFAULT NULL,
239 p_program_id IN NUMBER DEFAULT NULL,
240 p_program_update_date IN DATE DEFAULT NULL,
241 p_status IN VARCHAR2 DEFAULT NULL,
242 p_action IN VARCHAR2 DEFAULT NULL
243 ) IS
244
245 -- standard version infermation
246 l_api_version CONSTANT NUMBER := 1.0;
247 l_api_name CONSTANT VARCHAR2(30):= 'log_exception';
248
249 -- cursor to validate logging_entity
250 CURSOR C2 (c_logging_entity VARCHAR2) IS
251 SELECT lookup_code
252 FROM fnd_lookup_values_vl
253 WHERE lookup_type = 'LOGGING_ENTITY' AND
254 lookup_code = c_logging_entity AND
255 enabled_flag = 'Y' AND
256 (sysdate BETWEEN NVL(start_date_active,sysdate) AND
257 NVL(end_date_active, sysdate));
258
259 -- cursor for update
260 CURSOR C3 (c_exception_id NUMBER) IS
261 --Changed for BUG#3330869
262 -- SELECT *
263 SELECT
264 exception_id,
265 message,
266 exception_name,
267 status,
268 severity,
269 trip_id,
270 trip_name,
271 trip_stop_id,
272 delivery_id,
273 delivery_name,
274 delivery_detail_id,
275 delivery_assignment_id,
276 container_name,
277 inventory_item_id,
278 lot_number,
279 -- HW OPMCONV - No need for sublot_number
280 -- sublot_number,
281 revision,
282 serial_number,
283 unit_of_measure,
284 quantity,
285 unit_of_measure2,
286 quantity2,
287 subinventory,
288 locator_id,
289 arrival_date,
290 departure_date,
291 error_message,
292 attribute_category,
293 attribute1,
294 attribute2,
295 attribute3,
296 attribute4,
297 attribute5,
298 attribute6,
299 attribute7,
300 attribute8,
301 attribute9,
302 attribute10,
303 attribute11,
304 attribute12,
305 attribute13,
306 attribute14,
307 attribute15,
308 request_id,
309 logged_at_location_id,
310 logging_entity,
311 logging_entity_id,
312 exception_location_id,
313 manually_logged
314 FROM wsh_exceptions
315 WHERE exception_id = c_exception_id FOR UPDATE;
316
317
318 -- local variables
319 l_exception_id NUMBER := NULL;
320 l_severity VARCHAR2(10) := NULL;
321 l_exc_def_info Exception_Def_Rec_Typ;
322 l_manually_logged VARCHAR2(1) := FND_API.G_FALSE;
323 -- Shipping exception enhancement : changed default to 'NO_ACTION_REQUIRED'
324 l_status VARCHAR2(30) :='NO_ACTION_REQUIRED';
325 l_exception_name VARCHAR2(30) := NULL;
326 l_applicatoin_id NUMBER;
327 l_xcp_record C3%ROWTYPE;
328 l_logging_entity VARCHAR2(30) := NULL;
329 -- Bug# 1924574 , added l_return_status, l_location_id
330 l_return_status VARCHAR2(30);
331 l_location_id NUMBER;
332 -- local variable to hold token for exception WSH_XC_INVALID_OPERATION
333 l_msg_name VARCHAR2(30) := NULL;
334 l_field_name VARCHAR2(30) := NULL;
335 l_msg_summary VARCHAR2(2000) := NULL;
336 l_msg_details VARCHAR2(4000) := NULL;
337
338 WSH_XC_INVALID_LOGGING_ENTITY EXCEPTION;
339 WSH_XC_INVALID_SEVERITY EXCEPTION;
340 WSH_XC_INVALID_OPERATION EXCEPTION;
341 WSH_XC_NOT_FOUND EXCEPTION;
342 WSH_XC_DEF_NOT_FOUND EXCEPTION;
343 WSH_XC_DATA_ERROR EXCEPTION;
344
345 -- Raising Event for Workflow Enabled exception
346 l_msg_parameter_list WF_PARAMETER_LIST_T;
347 l_event_name VARCHAR2(120) := 'oracle.apps.wsh.excp.log';
348 l_event_key VARCHAR2(30);
349 l_p_entity_name VARCHAR2(30);
350 l_p_entity_id VARCHAR2(30);
351 l_exception_name_exists BOOLEAN := TRUE;
352 -- Bug 3711661 , added l_request_id, l_batch_id
353 l_request_id number;
354 l_batch_id number;
355 --
356 l_debug_on BOOLEAN;
357 --
358 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_EXCEPTION';
359 --
360
361 --
362 BEGIN
363
364 -- Standard begin of API savepoint
365 --
366 -- Debug Statements
367 --
368 --
369 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
370 --
371 IF l_debug_on IS NULL
372 THEN
373 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
374 END IF;
375 --
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.push(l_module_name);
378 --
379 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
380 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
381 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
382 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
383 WSH_DEBUG_SV.log(l_module_name,'X_EXCEPTION_ID',X_EXCEPTION_ID);
384 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_LOCATION_ID',P_EXCEPTION_LOCATION_ID);
385 WSH_DEBUG_SV.log(l_module_name,'P_LOGGED_AT_LOCATION_ID',P_LOGGED_AT_LOCATION_ID);
386 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY',P_LOGGING_ENTITY);
387 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_ID',P_LOGGING_ENTITY_ID);
388 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
389 WSH_DEBUG_SV.log(l_module_name,'P_MESSAGE',P_MESSAGE);
390 WSH_DEBUG_SV.log(l_module_name,'P_MANUALLY_LOGGED',P_MANUALLY_LOGGED);
391 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
392 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_NAME',P_TRIP_NAME);
393 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
394 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
395 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
396 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
397 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ASSIGNMENT_ID',P_DELIVERY_ASSIGNMENT_ID);
398 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_NAME',P_CONTAINER_NAME);
399 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
400 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
401 -- HW OPMCONV - No need for sublot_number
402 -- WSH_DEBUG_SV.log(l_module_name,'P_SUBLOT_NUMBER',P_SUBLOT_NUMBER);
403 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
404 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER',P_SERIAL_NUMBER);
405 WSH_DEBUG_SV.log(l_module_name,'P_UNIT_OF_MEASURE',P_UNIT_OF_MEASURE);
406 WSH_DEBUG_SV.log(l_module_name,'P_QUANTITY',P_QUANTITY);
407 WSH_DEBUG_SV.log(l_module_name,'P_UNIT_OF_MEASURE2',P_UNIT_OF_MEASURE2);
408 WSH_DEBUG_SV.log(l_module_name,'P_QUANTITY2',P_QUANTITY2);
409 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
410 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
411 WSH_DEBUG_SV.log(l_module_name,'P_ARRIVAL_DATE',P_ARRIVAL_DATE);
412 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE',P_DEPARTURE_DATE);
413 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_MESSAGE',P_ERROR_MESSAGE);
414 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
415 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
416 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
417 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
418 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
419 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
420 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
421 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
422 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
423 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
424 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
425 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
426 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
427 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
428 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
429 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
430 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
431 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
432 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
433 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
434 END IF;
435 --
436 SAVEPOINT Log_Exception_PUB;
437 -- Standard call to check for call compatibility.
438 IF NOT FND_API.compatible_api_call( l_api_version,
439 p_api_version,
440 l_api_name,
441 G_PKG_NAME) THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445 -- Check p_init_msg_list
446 IF FND_API.to_boolean(p_init_msg_list) THEN
447 FND_MSG_PUB.initialize;
448 END IF;
449
450 -- initialize API return status to success
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453 IF x_exception_id IS NULL THEN
454 -- insert record
455
456 -- validate required parameters
457 IF p_logged_at_location_id IS NULL OR
458 p_logging_entity IS NULL OR
459 p_logging_entity_id IS NULL OR
460 p_message IS NULL
461 THEN
462 l_msg_name := 'WSH_XC_MIS_REQ_PARAM';
463 RAISE WSH_XC_INVALID_OPERATION;
464 END IF;
465
466 -- validate logging entity
467 OPEN C2(p_logging_entity);
468 FETCH C2 INTO l_logging_entity;
469 IF C2%NOTFOUND THEN
470 CLOSE C2;
471 RAISE WSH_XC_INVALID_LOGGING_ENTITY;
472 END IF;
473 CLOSE C2;
474
475 -- validate p_exception_location_id
476 -- 1924574 Changes, Calling API and removing earlier Cursors
477 l_location_id := p_exception_location_id;
478 --
479 -- Debug Statements
480 --
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
483 END IF;
484 --
485 WSH_UTIL_VALIDATE.Validate_Location (
486 p_location_id => l_location_id,
487 p_location_code => NULL ,
488 x_return_status => l_return_status);
489 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
490 RAISE WSH_XC_INVALID_LOCATION;
491 END IF;
492
493 -- validate p_logged_at_location_id
494 l_location_id := p_logged_at_location_id;
495 -- 1924574 Changes, Calling Procedure and removing earlier Cursors
496 --
497 -- Debug Statements
498 --
499 IF l_debug_on THEN
500 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
501 END IF;
502 --
503 WSH_UTIL_VALIDATE.Validate_Location (
504 p_location_id => l_location_id,
505 p_location_code => null,
506 x_return_status => l_return_status);
507 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
508 RAISE WSH_XC_INVALID_LOCATION;
509 END IF;
510
511 -- set default value for p_manually_logged is 'F'
512 IF p_manually_logged=FND_API.G_TRUE
513 THEN
514 l_manually_logged := p_manually_logged;
515 ELSE -- Bugfix 3711661
516 IF (p_request_id is null) THEN
517 l_request_id := fnd_global.conc_request_id;
518 ELSE
519 l_request_id := p_request_id;
520 END IF;
521
522 IF (p_batch_id is null)
523 THEN
524 l_batch_id := WSH_PICK_LIST.G_BATCH_ID;
525 ELSE
526 l_batch_id := p_batch_id;
527 END IF;
528 END IF;
529
530 -- check if exception name is null
531 IF p_exception_name IS NULL THEN
532 -- Shipping exception enhancement :
533 -- changed default status from OPEN to NO_ACTION_REQUIRED
534 -- added default severity as INFO
535 l_status := 'NO_ACTION_REQUIRED'; -- default status
536 l_severity := 'INFO';
537 ELSE -- exception name is not null
538
539 -- validate exception_name and get default attr
540 -- bug 5183769, call get_exception for caching
541
542 Get_Exception_Def(
543 p_exception_name => p_exception_name,
544 x_exception_def_info => l_exc_def_info,
545 x_return_status => l_return_status);
546
547 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
548 RAISE WSH_XC_DEF_NOT_FOUND;
549 ELSIF l_exc_def_info.enabled = 'N' THEN
550 IF l_debug_on THEN
551 WSH_DEBUG_SV.logmsg(l_module_name,'Not logging exception since the exception is not enabled',WSH_DEBUG_SV.C_PROC_LEVEL);
552 END IF;
553 GOTO end_proc;
554 END IF;
555
556 -- set default severity if it is not passed
557 -- Shipping exception enhancement : assign default severity, exception_handling
558 l_severity := l_exc_def_info.default_severity;
559
560 -- set default status
561 -- Shipping Exception enhancement
562
563 If p_action = 'SPLIT-LINE' and p_status is not null and p_exception_name is not null then
564 l_status := p_status;
565 else
566 IF l_severity IN ( 'ERROR' , 'WARNING' ) THEN
567 l_status := 'OPEN';
568 ELSE
569 l_status := 'NO_ACTION_REQUIRED';
570 End if;
571 END IF;
572
573 l_exception_name := UPPER(p_exception_name);
574 l_exception_name_exists := FALSE; -- since exception_name was NULL
575 END IF; -- check if exception name is NULL
576
577 IF l_debug_on THEN
578 WSH_DEBUG_SV.logmsg(l_module_name,'Logging exception with severity '||l_severity||' , status '||l_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
579 END IF;
580
581 -- populate l_exception_id
582 -- Bug 6615016 using the wsh_exceptions_s.nextval directly in the insert statement.
583 -- SELECT wsh_exceptions_s.nextval INTO l_exception_id FROM SYS.DUAL;
584
585 IF l_debug_on THEN
586 wsh_debug_sv.logmsg(l_module_name, '**** Actual values ****');
587 -- Bug 6615016
588 --wsh_debug_Sv.log(l_module_name, 'Exception ID', l_exception_id);
589 wsh_debug_sv.log(l_module_name, 'Exception Loc ID', p_exception_location_id);
590 wsh_debug_sv.log(l_module_name, 'Logged at loc Id', p_logged_at_location_id);
591 wsh_debug_Sv.log(l_module_name, 'Logging Entity', p_logging_entity);
592 wsh_debug_Sv.log(l_module_name, 'Logging Entity ID', p_logging_entity_id);
593 wsh_debug_sv.log(l_module_name, 'Exception Name', l_exception_name);
594 wsh_debug_sv.log(l_module_name, 'Message', p_message);
595 wsh_debug_sv.log(l_module_name, 'Severity', l_severity);
596 wsh_debug_Sv.log(l_module_name, 'Manually Logged?', l_manually_logged);
597 wsh_debug_sv.log(l_module_name, 'Status', l_status);
598 wsh_debug_sv.log(l_module_name, 'Trip ID', p_trip_id);
599 wsh_debug_sv.log(l_module_name, 'Trip Name', p_trip_name);
600 wsh_debug_sv.log(l_module_name, 'Trip Stop Id', p_trip_stop_id);
601 wsh_debug_sv.log(l_module_name, 'Delivery Id', p_delivery_id);
602 wsh_debug_sv.log(l_module_name, 'Delivery name', p_delivery_name);
603 wsh_debug_sv.log(l_module_name, 'Delivery Detail ID', p_delivery_detail_id);
604 wsh_debug_Sv.log(l_module_name, 'Assignment Id', p_delivery_assignment_id);
605 wsh_debug_sv.log(l_module_name, 'Request Id', l_request_id); -- Bugfix 3711661, previously p_request_id was passed.
606 wsh_debug_sv.log(l_module_name, 'Batch Id', l_batch_id); -- Bugfix 3711661, previously p_batch_id was passed.
607 END IF;
608 --
609 IF (p_container_name is not null) and (p_delivery_detail_id is null) then --LPN Synch Up..samanna
610 l_msg_name := 'WSH_MISSING_DETAILS';
611 raise WSH_XC_INVALID_OPERATION;
612 END IF;
613 --
614 INSERT INTO wsh_exceptions(
615 exception_id,
616 exception_location_id,
617 logged_at_location_id,
618 logging_entity,
619 logging_entity_id,
620 exception_name,
621 message,
622 severity,
623 manually_logged,
624 status,
625 trip_id,
626 trip_name,
627 trip_stop_id,
628 delivery_id,
629 delivery_name,
630 delivery_detail_id,
631 delivery_assignment_id,
632 container_name,
633 inventory_item_id,
634 lot_number,
635 revision,
636 serial_number,
637 unit_of_measure,
638 quantity,
639 subinventory,
640 locator_id,
641 arrival_date,
642 departure_date,
643 error_message,
644 attribute_category,
645 attribute1,
646 attribute2,
647 attribute3,
648 attribute4,
649 attribute5,
650 attribute6,
651 attribute7,
652 attribute8,
653 attribute9,
654 attribute10,
655 attribute11,
656 attribute12,
657 attribute13,
658 attribute14,
659 attribute15,
660 creation_date,
661 created_by,
662 last_update_date,
663 last_updated_by,
664 last_update_login,
665 -- BUG#:1549665 hwahdani added request_id
666 request_id ,
667 -- HW BUG#:1900149
668 -- HW OPMCONV - No need for sublot_number
669 -- sublot_number,
670 unit_of_measure2,
671 quantity2,
672 -- HW end of 1900149
673 batch_id,
674 program_application_id,
675 program_id,
676 program_update_date
677 )
678 VALUES (
679 -- Bug 6615016 using the wsh_exceptions_s.nextval directly in the insert statement.
680 --l_exception_id,
681 wsh_exceptions_s.nextval,
682 p_exception_location_id,
683 p_logged_at_location_id,
684 UPPER(p_logging_entity),
685 p_logging_entity_id,
686 DECODE(l_exception_name, NULL, NULL, UPPER(l_exception_name)),
687 p_message,
688 -- Shipping Exception enhancement
689 l_severity,
690 l_manually_logged,
691 l_status,
692 p_trip_id,
693 p_trip_name,
694 p_trip_stop_id,
695 p_delivery_id,
696 p_delivery_name,
697 p_delivery_detail_id,
698 p_delivery_assignment_id,
699 null, --LPN_conv Synch Up..samanna
700 p_inventory_item_id,
701 p_lot_number,
702 p_revision,
703 p_serial_number,
704 p_unit_of_measure,
705 p_quantity,
706 p_subinventory,
707 p_locator_id,
708 p_arrival_date,
709 p_departure_date,
710 p_error_message,
711 p_attribute_category,
712 p_attribute1,
713 p_attribute2,
714 p_attribute3,
715 p_attribute4,
716 p_attribute5,
717 p_attribute6,
718 p_attribute7,
719 p_attribute8,
720 p_attribute9,
721 p_attribute10,
722 p_attribute11,
723 p_attribute12,
724 p_attribute13,
725 p_attribute14,
726 p_attribute15,
727 NVL(p_creation_date,SYSDATE),
728 NVL(p_created_by,FND_GLOBAL.USER_ID),
729 NVL(p_last_update_date,SYSDATE),
730 NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
731 NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID),
732 -- BUG#:1549665 hwahdani added request_id
733 l_request_id, -- Bugfix 3711661, previously p_request_id was passed.
734 -- HW BUG#:1900149 added opm columns
735 -- HW OPMCONV - No need for sublot_number
736 -- p_sublot_number,
737 p_unit_of_measure2,
738 p_quantity2,
739 -- HW end of 1900149
740 l_batch_id, -- Bugfix 3711661, previously p_batch_id was passed.
741 p_program_application_id,
742 p_program_id,
743 p_program_update_date
744 );
745
746 IF FND_API.TO_BOOLEAN(p_commit) THEN
747 COMMIT WORK;
748 END IF;
749
750 x_exception_id := l_exception_id;
751
752
753
754
755 ELSE -- update
756
757 -- validate x_exception_id
758 OPEN C3(x_exception_id);
759 FETCH C3 INTO l_xcp_record;
760 IF C3%NOTFOUND THEN
761 CLOSE C3;
762 RAISE WSH_XC_NOT_FOUND;
763 END IF;
764
765 l_exception_id := l_xcp_record.exception_id;
766
767 -- update an existing exception,the required fields can't be changed.
768 IF ( p_logged_at_location_id IS NOT NULL) OR
769 (p_exception_location_id IS NOT NULL) OR
770 (p_logging_entity IS NOT NULL) OR
771 (p_logging_entity_id IS NOT NULL)
772 THEN
773 l_msg_name := 'WSH_XC_CHANGE_REQ_FIELD';
774 RAISE WSH_XC_INVALID_OPERATION;
775 END IF;
776
777
778 -- append p_message to the current message text if p_message is
779 -- not NULL
780 IF p_message is NOT NULL then
781 -- l_xcp_record.message := l_xcp_record.message || p_message;
782 l_xcp_record.message := p_message;
783 END IF;
784
785 -- Only update on a NULL field is allowed.
786 -- If the current value is already the same with the parameter,
787 -- the current value is not changed and the procedure won't fail.
788
789 IF p_exception_name IS NOT NULL THEN
790 IF l_xcp_record.exception_name IS NOT NULL and
791 l_xcp_record.exception_name <> p_exception_name THEN
792 l_msg_name := 'WSH_XC_EXP_NAME_EXIST';
793 RAISE WSH_XC_INVALID_OPERATION;
794 ELSIF l_xcp_record.exception_name IS NULL THEN
795 l_exception_name_exists := FALSE; -- since exception_name was NULL
796 END IF;
797
798 -- validate exception_name
799 -- bug 5183769, call get_exception for caching
800
801 Get_Exception_Def(
802 p_exception_name => p_exception_name,
803 x_exception_def_info => l_exc_def_info,
804 x_return_status => l_return_status);
805
806 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
807 RAISE WSH_XC_DEF_NOT_FOUND;
808 ELSIF l_exc_def_info.enabled = 'N' THEN
809 IF l_debug_on THEN
810 WSH_DEBUG_SV.logmsg(l_module_name,'Not logging exception since the exception is not enabled',WSH_DEBUG_SV.C_PROC_LEVEL);
811 END IF;
812 GOTO end_proc;
813 END IF;
814
815 -- get default status
816 -- Shipping Exception enhancement
817 IF l_exc_def_info.default_severity IN ( 'ERROR' , 'WARNING' ) THEN
818 l_xcp_record.status := 'OPEN';
819 l_status := 'OPEN';
820 ELSE
821 l_xcp_record.status := 'NO_ACTION_REQUIRED';
822 l_status := 'NO_ACTION_REQUIRED';
823 END IF;
824
825 l_xcp_record.exception_name := UPPER(p_exception_name);
826
827 -- set default severity
828 l_xcp_record.severity := UPPER(l_exc_def_info.default_severity);
829 l_severity := l_xcp_record.severity;
830
831 END IF; -- end of IF p_exception_name IS NOT NULL THEN
832
833 IF p_trip_id IS NOT NULL THEN
834 IF l_xcp_record.trip_id IS NULL THEN
835 l_xcp_record.trip_id:= p_trip_id;
836 ELSE
837 IF l_xcp_record.trip_id <> p_trip_id THEN
838 l_msg_name := 'WSH_XC_TRIP_NUM_EXIST';
839 RAISE WSH_XC_INVALID_OPERATION;
840 END IF;
841 END IF;
842 END IF;
843
844 IF p_trip_name IS NOT NULL THEN
845 IF l_xcp_record.trip_name IS NULL THEN
846 l_xcp_record.trip_name:= p_trip_name;
847 ELSE
848 IF l_xcp_record.trip_name <> p_trip_name THEN
849 l_msg_name := 'WSH_XC_TRIP_NAME_EXIST';
850 RAISE WSH_XC_INVALID_OPERATION;
851 END IF;
852 END IF;
853 END IF;
854
855 IF p_trip_stop_id IS NOT NULL THEN
856 IF l_xcp_record.trip_stop_id IS NULL THEN
857 l_xcp_record.trip_stop_id:= p_trip_stop_id;
858 ELSE
859 IF l_xcp_record.trip_stop_id <> p_trip_stop_id THEN
860 l_msg_name := 'WSH_XC_STOP_NUM_EXIST';
861 RAISE WSH_XC_INVALID_OPERATION;
862 END IF;
863 END IF;
864 END IF;
865
866 IF p_delivery_id IS NOT NULL THEN
867 IF l_xcp_record.delivery_id IS NULL THEN
868 l_xcp_record.delivery_id:= p_delivery_id;
869 ELSE
870 IF l_xcp_record.delivery_id <> p_delivery_id THEN
871 l_msg_name := 'WSH_XC_DEL_NUM_EXIST';
872 RAISE WSH_XC_INVALID_OPERATION;
873 END IF;
874 END IF;
875 END IF;
876
877 IF p_delivery_name IS NOT NULL THEN
878 IF l_xcp_record.delivery_name IS NULL THEN
879 l_xcp_record.delivery_name:= p_delivery_name;
880 ELSE
881 IF l_xcp_record.delivery_name <> p_delivery_name THEN
882 l_msg_name := 'WSH_XC_DEL_NAME_EXIST';
883 RAISE WSH_XC_INVALID_OPERATION;
884 END IF;
885 END IF;
886 END IF;
887
888 IF p_delivery_detail_id IS NOT NULL THEN
889 IF l_xcp_record.delivery_detail_id IS NULL THEN
890 l_xcp_record.delivery_detail_id:= p_delivery_detail_id;
891 ELSE
892 IF l_xcp_record.delivery_detail_id <> p_delivery_detail_id THEN
893 l_msg_name := 'WSH_XC_DET_NUM_EXIST';
894 RAISE WSH_XC_INVALID_OPERATION;
895 END IF;
896 END IF;
897 END IF;
898
899 IF p_delivery_assignment_id IS NOT NULL THEN
900 IF l_xcp_record.delivery_assignment_id IS NULL THEN
901 l_xcp_record.delivery_assignment_id:= p_delivery_assignment_id;
902 ELSE
903 IF l_xcp_record.delivery_assignment_id <> p_delivery_assignment_id THEN
904 l_msg_name := 'WSH_XC_ASSIGN_NUM_EXIST';
905 RAISE WSH_XC_INVALID_OPERATION;
906 END IF;
907 END IF;
908 END IF;
909
910
911 /*IF p_container_name IS NOT NULL THEN
912 IF l_xcp_record.container_name IS NULL THEN
913 l_xcp_record.container_name:= p_container_name;
914 ELSE
915 IF l_xcp_record.container_name <> p_container_name THEN
916 l_msg_name := 'WSH_XC_CON_NAME_EXIST';
917 RAISE WSH_XC_INVALID_OPERATION;
918 END IF;
919 END IF;
920 END IF;*/--LPN Synch Up ..samanna
921
922 IF p_inventory_item_id IS NOT NULL THEN
923 IF l_xcp_record.inventory_item_id IS NULL THEN
924 l_xcp_record.inventory_item_id := p_inventory_item_id;
925 ELSE
926 IF l_xcp_record.inventory_item_id <> p_inventory_item_id THEN
927 l_msg_name := 'WSH_XC_INV_ITEM_ID_EXIST';
928 RAISE WSH_XC_INVALID_OPERATION;
929 END IF;
930 END IF;
931 END IF;
932
933
934 IF p_lot_number IS NOT NULL THEN
935 IF l_xcp_record.lot_number IS NULL THEN
936 l_xcp_record.lot_number:=p_lot_number ;
937 ELSE
938 IF l_xcp_record.lot_number <> p_lot_number THEN
939 l_msg_name := 'WSH_XC_LOT_NUM_EXIST';
940 RAISE WSH_XC_INVALID_OPERATION;
941 END IF;
942 END IF;
943 END IF;
944 -- HW BUG#:1900149
945 -- HW OPMCONV - Removed sublot code
946
947 -- HW end of 1900149
948
949 IF p_revision IS NOT NULL THEN
950 IF l_xcp_record.revision IS NULL THEN
951 l_xcp_record.revision:=p_revision ;
952 ELSE
953 IF l_xcp_record.revision <> p_revision THEN
954 l_msg_name := 'WSH_XC_REVSION_EXIST';
955 RAISE WSH_XC_INVALID_OPERATION;
956 END IF;
957 END IF;
958 END IF;
959
960 IF p_serial_number IS NOT NULL THEN
961 IF l_xcp_record.serial_number IS NULL THEN
962 l_xcp_record.serial_number := p_serial_number ;
963 ELSE
964 IF l_xcp_record.serial_number <> p_serial_number THEN
965 l_msg_name := 'WSH_XC_SER_NUM_EXIST';
966 RAISE WSH_XC_INVALID_OPERATION;
967 END IF;
968 END IF;
969 END IF;
970
971 IF p_unit_of_measure IS NOT NULL THEN
972 IF l_xcp_record.unit_of_measure IS NULL THEN
973 l_xcp_record.unit_of_measure :=p_unit_of_measure ;
974 ELSE
975 IF l_xcp_record.unit_of_measure <> p_unit_of_measure THEN
976 l_msg_name := 'WSH_XC_UOM_EXIST';
977 RAISE WSH_XC_INVALID_OPERATION;
978 END IF;
979 END IF;
980 END IF;
981
982 IF p_quantity IS NOT NULL THEN
983 IF l_xcp_record.quantity IS NULL THEN
984 l_xcp_record.quantity := p_quantity ;
985 ELSE
986 IF l_xcp_record.quantity <> p_quantity THEN
987 l_msg_name := 'WSH_XC_QUANTITY_EXIST';
988 RAISE WSH_XC_INVALID_OPERATION;
989 END IF;
990 END IF;
991 END IF;
992
993 -- HW BUG#:1900149
994 IF p_unit_of_measure2 IS NOT NULL THEN
995 IF l_xcp_record.unit_of_measure2 IS NULL THEN
996 l_xcp_record.unit_of_measure2 :=p_unit_of_measure2 ;
997 ELSE
998 IF l_xcp_record.unit_of_measure2 <> p_unit_of_measure2 THEN
999 l_msg_name := 'WSH_XC_SEC_UOM_EXIST';
1000 RAISE WSH_XC_INVALID_OPERATION;
1001 END IF;
1002 END IF;
1003 END IF;
1004
1005 IF p_quantity2 IS NOT NULL THEN
1006 IF l_xcp_record.quantity2 IS NULL THEN
1007 l_xcp_record.quantity2 := p_quantity2 ;
1008 ELSE
1009 IF l_xcp_record.quantity2 <> p_quantity2 THEN
1010 l_msg_name := 'WSH_XC_SEC_QUANTITY_EXIST';
1011 RAISE WSH_XC_INVALID_OPERATION;
1012 END IF;
1013 END IF;
1014 END IF;
1015 -- HW end of 1900149
1016
1017
1018 IF p_subinventory IS NOT NULL THEN
1019 IF l_xcp_record.subinventory IS NULL THEN
1020 l_xcp_record.subinventory := p_subinventory ;
1021 ELSE
1022 IF l_xcp_record.subinventory <> p_subinventory THEN
1023 l_msg_name := 'WSH_XC_SUBINVENTORY_EXIST';
1024 RAISE WSH_XC_INVALID_OPERATION;
1025 END IF;
1026 END IF;
1027 END IF;
1028
1029 IF p_locator_id IS NOT NULL THEN
1030 IF l_xcp_record.locator_id IS NULL THEN
1031 l_xcp_record.locator_id := p_locator_id ;
1032 ELSE
1033 IF l_xcp_record.locator_id <> p_locator_id THEN
1034 l_msg_name := 'WSH_XC_LOCATOR_NUM_EXIST';
1035 RAISE WSH_XC_INVALID_OPERATION;
1036 END IF;
1037 END IF;
1038 END IF;
1039
1040 IF p_arrival_date IS NOT NULL THEN
1041 IF l_xcp_record.arrival_date IS NULL THEN
1042 l_xcp_record.arrival_date := p_arrival_date ;
1043 ELSE
1044 IF l_xcp_record.arrival_date <> p_arrival_date THEN
1045 l_msg_name := 'WSH_XC_ARR_DATE_EXIST';
1046 RAISE WSH_XC_INVALID_OPERATION;
1047 END IF;
1048 END IF;
1049 END IF;
1050
1051 IF p_departure_date IS NOT NULL THEN
1052 IF l_xcp_record.departure_date IS NULL THEN
1053 l_xcp_record.departure_date := p_departure_date ;
1054 ELSE
1055 IF l_xcp_record.departure_date <> p_departure_date THEN
1056 l_msg_name := 'WSH_XC_DEP_DATE_EXIST';
1057 RAISE WSH_XC_INVALID_OPERATION;
1058 END IF;
1059 END IF;
1060 END IF;
1061
1062
1063
1064 -- update p_error_message to the current message text if p_error_message is
1065 -- not NULL
1066 IF p_error_message is NOT NULL then
1067 l_xcp_record.error_message := p_error_message;
1068 END IF;
1069
1070 /*-- Bug No:2363908 ---> Fix : Update is allowed for all the DFF attributes appearing in SHipping Exception Form
1071 Fix is done by commenting all the checks
1072
1073 IF p_attribute_category IS NOT NULL THEN
1074 IF l_xcp_record.attribute_category IS NULL THEN
1075 l_xcp_record.attribute_category := p_attribute_category ;
1076 ELSE
1077 IF l_xcp_record.attribute_category <> p_attribute_category THEN
1078 l_msg_name := 'WSH_XC_ATTR_CATEGORY_EXIST';
1079 RAISE WSH_XC_INVALID_OPERATION;
1080 END IF;
1081 END IF;
1082 END IF;
1083
1084 IF p_attribute1 IS NOT NULL THEN
1085 IF l_xcp_record.attribute1 IS NULL THEN
1086 l_xcp_record.attribute1 := p_attribute1;
1087 ELSE
1088 IF l_xcp_record.attribute1 <> p_attribute1 THEN
1089 l_msg_name := 'WSH_XC_ATTR_EXIST';
1090 l_field_name := 'ATTRIBUTE1';
1091 RAISE WSH_XC_INVALID_OPERATION;
1092 END IF;
1093 END IF;
1094 END IF;
1095
1096 IF p_attribute2 IS NOT NULL THEN
1097 IF l_xcp_record.attribute2 IS NULL THEN
1098 l_xcp_record.attribute2 := p_attribute2;
1099 ELSE
1100 IF l_xcp_record.attribute2 <> p_attribute2 THEN
1101 l_msg_name := 'WSH_XC_ATTR_EXIST';
1102 l_field_name := 'ATTRIBUTE2';
1103 RAISE WSH_XC_INVALID_OPERATION;
1104 END IF;
1105 END IF;
1106 END IF;
1107
1108 IF p_attribute3 IS NOT NULL THEN
1109 IF l_xcp_record.attribute3 IS NULL THEN
1110 l_xcp_record.attribute3 := p_attribute3;
1111 ELSE
1112 IF l_xcp_record.attribute3 <> p_attribute3 THEN
1113 l_msg_name := 'WSH_XC_ATTR_EXIST';
1114 l_field_name := 'ATTRIBUTE3';
1115 RAISE WSH_XC_INVALID_OPERATION;
1116 END IF;
1117 END IF;
1118 END IF;
1119
1120 IF p_attribute4 IS NOT NULL THEN
1121 IF l_xcp_record.attribute4 IS NULL THEN
1122 l_xcp_record.attribute4 := p_attribute4;
1123 ELSE
1124 IF l_xcp_record.attribute4 <> p_attribute4 THEN
1125 l_msg_name := 'WSH_XC_ATTR_EXIST';
1126 l_field_name := 'ATTRIBUTE4';
1127 RAISE WSH_XC_INVALID_OPERATION;
1128 END IF;
1129 END IF;
1130 END IF;
1131
1132 IF p_attribute5 IS NOT NULL THEN
1133 IF l_xcp_record.attribute5 IS NULL THEN
1134 l_xcp_record.attribute5 := p_attribute5;
1135 ELSE
1136 IF l_xcp_record.attribute5 <> p_attribute5 THEN
1137 l_msg_name := 'WSH_XC_ATTR_EXIST';
1138 l_field_name := 'ATTRIBUTE5';
1139 RAISE WSH_XC_INVALID_OPERATION;
1140 END IF;
1141 END IF;
1142 END IF;
1143
1144 IF p_attribute6 IS NOT NULL THEN
1145 IF l_xcp_record.attribute6 IS NULL THEN
1146 l_xcp_record.attribute6 := p_attribute6;
1147 ELSE
1148 IF l_xcp_record.attribute6 <> p_attribute6 THEN
1149 l_msg_name := 'WSH_XC_ATTR_EXIST';
1150 l_field_name := 'ATTRIBUTE6';
1151 RAISE WSH_XC_INVALID_OPERATION;
1152 END IF;
1153 END IF;
1154 END IF;
1155
1156 IF p_attribute7 IS NOT NULL THEN
1157 IF l_xcp_record.attribute7 IS NULL THEN
1158 l_xcp_record.attribute7 := p_attribute7;
1159 ELSE
1160 IF l_xcp_record.attribute7 <> p_attribute7 THEN
1161 l_msg_name := 'WSH_XC_ATTR_EXIST';
1162 l_field_name := 'ATTRIBUTE7';
1163 RAISE WSH_XC_INVALID_OPERATION;
1164 END IF;
1165 END IF;
1166 END IF;
1167
1168 IF p_attribute8 IS NOT NULL THEN
1169 IF l_xcp_record.attribute8 IS NULL THEN
1170 l_xcp_record.attribute8 := p_attribute8;
1171 ELSE
1172 IF l_xcp_record.attribute8 <> p_attribute8 THEN
1173 l_msg_name := 'WSH_XC_ATTR_EXIST';
1174 l_field_name := 'ATTRIBUTE8';
1175 RAISE WSH_XC_INVALID_OPERATION;
1176 END IF;
1177 END IF;
1178 END IF;
1179
1180 IF p_attribute9 IS NOT NULL THEN
1181 IF l_xcp_record.attribute9 IS NULL THEN
1182 l_xcp_record.attribute9 := p_attribute9;
1183 ELSE
1184 IF l_xcp_record.attribute9 <> p_attribute9 THEN
1185 l_msg_name := 'WSH_XC_ATTR_EXIST';
1186 l_field_name := 'ATTRIBUTE9';
1187 RAISE WSH_XC_INVALID_OPERATION;
1188 END IF;
1189 END IF;
1190 END IF;
1191
1192 IF p_attribute10 IS NOT NULL THEN
1193 IF l_xcp_record.attribute10 IS NULL THEN
1194 l_xcp_record.attribute10 := p_attribute10;
1195 ELSE
1196 IF l_xcp_record.attribute10 <> p_attribute10 THEN
1197 l_msg_name := 'WSH_XC_ATTR_EXIST';
1198 l_field_name := 'ATTRIBUTE10';
1199 RAISE WSH_XC_INVALID_OPERATION;
1200 END IF;
1201 END IF;
1202 END IF;
1203
1204 IF p_attribute11 IS NOT NULL THEN
1205 IF l_xcp_record.attribute11 IS NULL THEN
1206 l_xcp_record.attribute11 := p_attribute11;
1207 ELSE
1208 IF l_xcp_record.attribute11 <> p_attribute11 THEN
1209 l_msg_name := 'WSH_XC_ATTR_EXIST';
1210 l_field_name := 'ATTRIBUTE11';
1211 RAISE WSH_XC_INVALID_OPERATION;
1212 END IF;
1213 END IF;
1214 END IF;
1215
1216 IF p_attribute12 IS NOT NULL THEN
1217 IF l_xcp_record.attribute12 IS NULL THEN
1218 l_xcp_record.attribute12 := p_attribute12;
1219 ELSE
1220 IF l_xcp_record.attribute12 <> p_attribute12 THEN
1221 l_msg_name := 'WSH_XC_ATTR_EXIST';
1222 l_field_name := 'ATTRIBUTE12';
1223 RAISE WSH_XC_INVALID_OPERATION;
1224 END IF;
1225 END IF;
1226 END IF;
1227
1228 IF p_attribute13 IS NOT NULL THEN
1229 IF l_xcp_record.attribute13 IS NULL THEN
1230 l_xcp_record.attribute13 := p_attribute13;
1231 ELSE
1232 IF l_xcp_record.attribute13 <> p_attribute13 THEN
1233 l_msg_name := 'WSH_XC_ATTR_EXIST';
1234 l_field_name := 'ATTRIBUTE13';
1235 RAISE WSH_XC_INVALID_OPERATION;
1236 END IF;
1237 END IF;
1238 END IF;
1239
1240 IF p_attribute14 IS NOT NULL THEN
1241 IF l_xcp_record.attribute14 IS NULL THEN
1242 l_xcp_record.attribute14 := p_attribute14;
1243 ELSE
1244 IF l_xcp_record.attribute14 <> p_attribute14 THEN
1245 l_msg_name := 'WSH_XC_ATTR_EXIST';
1246 l_field_name := 'ATTRIBUTE14';
1247 RAISE WSH_XC_INVALID_OPERATION;
1248 END IF;
1249 END IF;
1250 END IF;
1251
1252 IF l_debug_on THEN
1253
1254 WSH_DEBUG_SV.log(l_module_name,'p_attribute15',p_attribute15);
1255 WSH_DEBUG_SV.log(l_module_name,'l_xcp_record.attribute15',l_xcp_record.attribute15);
1256 END IF;
1257 IF p_attribute15 IS NOT NULL THEN
1258
1259 IF l_xcp_record.attribute15 IS NULL THEN
1260 l_xcp_record.attribute15 := p_attribute15;
1261 ELSE
1262 IF l_xcp_record.attribute15 <> p_attribute15 THEN
1263 l_msg_name := 'WSH_XC_ATTR_EXIST';
1264 l_field_name := 'ATTRIBUTE15';
1265 RAISE WSH_XC_INVALID_OPERATION;
1266 END IF;
1267 END IF;
1268 END IF;
1269
1270 -- End of fix for Bug:2363908 */
1271
1272
1273 -- BUG#:1549665 hwahdani added p_request_id
1274 IF p_request_id IS NOT NULL THEN
1275 IF l_xcp_record.request_id IS NULL THEN
1276 l_xcp_record.request_id := p_request_id ;
1277 ELSE
1278 IF l_xcp_record.request_id <> p_request_id THEN
1279 l_msg_name := 'WSH_XC_REQ_ID_EXIST';
1280 RAISE WSH_XC_INVALID_OPERATION;
1281 END IF;
1282 END IF;
1283 END IF;
1284
1285
1286 UPDATE wsh_exceptions
1287 SET
1288 logged_at_location_id = l_xcp_record.logged_at_location_id,
1289 logging_entity = l_xcp_record.logging_entity,
1290 logging_entity_id = l_xcp_record.logging_entity_id,
1291 exception_name = l_xcp_record.exception_name,
1292 exception_location_id = l_xcp_record.exception_location_id,
1293 message = l_xcp_record.message,
1294 severity = l_xcp_record.severity,
1295 manually_logged = l_xcp_record.manually_logged,
1296 status = l_xcp_record.status,
1297 trip_id = l_xcp_record.trip_id,
1298 trip_name = l_xcp_record.trip_name,
1299 trip_stop_id = l_xcp_record.trip_stop_id,
1300 delivery_id = l_xcp_record.delivery_id,
1301 delivery_name = l_xcp_record.delivery_name,
1302 delivery_detail_id = l_xcp_record.delivery_detail_id,
1303 delivery_assignment_id = l_xcp_record.delivery_assignment_id,
1304 container_name =l_xcp_record.container_name,
1305 inventory_item_id = l_xcp_record.inventory_item_id,
1306 lot_number = l_xcp_record.lot_number,
1307 -- HW BUG#:1900149 added sublot
1308 -- HW OPMCONV - No need for sublot_number
1309 -- sublot_number = l_xcp_record.sublot_number,
1310 revision = l_xcp_record.revision,
1311 serial_number = l_xcp_record.serial_number,
1312 unit_of_measure = l_xcp_record.unit_of_measure,
1313 quantity = l_xcp_record.quantity,
1314 -- HW BUG#:1900149 added uom2 and qty2
1315 unit_of_measure2 = l_xcp_record.unit_of_measure2,
1316 quantity2 = l_xcp_record.quantity2,
1317 -- HW end of 1900149
1318 subinventory = l_xcp_record.subinventory,
1319 locator_id = l_xcp_record.locator_id,
1320 arrival_date = l_xcp_record.arrival_date,
1321 departure_date = l_xcp_record.departure_date,
1322 error_message = l_xcp_record.error_message,
1323 attribute_category = p_attribute_category,
1324 attribute1 = p_attribute1,
1325 attribute2 = p_attribute2,
1326 attribute3 = p_attribute3,
1327 attribute4 = p_attribute4,
1328 attribute5 = p_attribute5,
1329 attribute6 = p_attribute6,
1330 attribute7 = p_attribute7,
1331 attribute8 = p_attribute8,
1332 attribute9 = p_attribute9,
1333 attribute10 = p_attribute10,
1334 attribute11 = p_attribute11,
1335 attribute12 = p_attribute12,
1336 attribute13 = p_attribute13,
1337 attribute14 = p_attribute14,
1338 attribute15 = p_attribute15,
1339 last_update_date = NVL(p_last_update_date,SYSDATE),
1340 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
1341 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID),
1342 --BUG#:1549665 hwahdani added request_id
1343 request_id = l_xcp_record.request_id
1344 WHERE CURRENT OF C3;
1345
1346 IF FND_API.TO_BOOLEAN(p_commit) THEN
1347 COMMIT WORK;
1348 x_exception_id := l_exception_id;
1349 END IF;
1350 CLOSE C3;
1351
1352
1353 END IF;
1354
1355 -- Raise Business Event , if exception is workflow enabled and Exception name is being passed first time
1356 IF p_exception_name IS NOT NULL AND NOT (l_exception_name_exists)
1357 AND nvl(l_exc_def_info.initiate_workflow,'N') = 'Y' THEN
1358 IF l_debug_on THEN
1359 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Business event for exception : '||l_exception_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1360 END IF;
1361 l_event_key := x_exception_id;
1362 IF p_delivery_detail_id IS NOT NULL THEN
1363 l_p_entity_name := 'LINE';
1364 l_p_entity_id := p_delivery_detail_id;
1365 ELSIF p_trip_stop_id IS NOT NULL THEN
1366 l_p_entity_name := 'STOP';
1367 l_p_entity_id := p_trip_stop_id;
1368 ELSIF p_delivery_id IS NOT NULL THEN
1369 l_p_entity_name := 'DELIVERY';
1370 l_p_entity_id := p_delivery_id;
1371 ELSIF p_trip_id IS NOT NULL THEN
1372 l_p_entity_name := 'TRIP';
1373 l_p_entity_id := p_trip_id;
1374 ELSE
1375 l_p_entity_name := NULL;
1376 l_p_entity_id := NULL;
1377 END IF;
1378 IF l_debug_on THEN
1379 WSH_DEBUG_SV.logmsg(l_module_name,'Entity Name : '||l_p_entity_name||' Entity Id : '||l_p_entity_id ,WSH_DEBUG_SV.C_PROC_LEVEL);
1380 END IF;
1381 WF_EVENT.AddParameterToList ( p_name => 'EXCEPTION_NAME',
1382 p_value => p_exception_name,
1383 p_parameterlist => l_msg_parameter_list);
1384 WF_EVENT.AddParameterToList ( p_name => 'EXCEPTION_ID',
1385 p_value => x_exception_id,
1386 p_parameterlist => l_msg_parameter_list);
1387 WF_EVENT.AddParameterToList ( p_name => 'ENTITY_NAME',
1388 p_value => l_p_entity_name,
1389 p_parameterlist => l_msg_parameter_list);
1390 WF_EVENT.AddParameterToList ( p_name => 'ENTITY_ID',
1391 p_value => l_p_entity_id,
1392 p_parameterlist => l_msg_parameter_list);
1393 WF_EVENT.AddParameterToList ( p_name => 'EXCEPTION_BEHAVIOR',
1394 p_value => l_severity,
1395 p_parameterlist => l_msg_parameter_list);
1396 IF l_debug_on THEN
1397 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_EVENT.RAISE' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1398 END IF;
1399 WF_EVENT.Raise ( p_event_name => l_event_name,
1400 p_event_key => 'EXCP'||l_event_key,
1401 p_parameters => l_msg_parameter_list );
1402 END IF;
1403
1404 <<end_proc>>
1405
1406 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1407 if x_msg_count > 1 then
1408 IF l_debug_on THEN
1409 wsh_debug_sv.logmsg(l_module_name, 'x_msg_count > 1');
1410 END IF;
1411 x_msg_data := SUBSTRB((l_msg_summary || l_msg_details),1, 2000);
1412 else
1413 IF l_debug_on THEN
1414 wsh_debug_sv.logmsg(l_module_name, 'x_msg_count < 1');
1415 END IF;
1416 x_msg_data := SUBSTRB(l_msg_summary, 1, 2000);
1417 end if;
1418
1419 --
1420 -- Debug Statements
1421 --
1422 IF l_debug_on THEN
1423 WSH_DEBUG_SV.pop(l_module_name);
1424 END IF;
1425 --
1426 EXCEPTION
1427
1428
1429 WHEN WSH_XC_INVALID_OPERATION THEN
1430 IF C3%ISOPEN THEN
1431 CLOSE C3;
1432 END IF;
1433
1434 FND_MESSAGE.SET_NAME('WSH', l_msg_name);
1435 IF l_msg_name = 'WSH_XC_ATTR_EXIST' THEN
1436 FND_MESSAGE.SET_TOKEN('FIELD', l_field_name , TRUE);
1437 END IF;
1438
1439 IF l_msg_name = 'WSH_MISSING_DETAILS' THEN --LPN Synch Up..samanna
1440 FND_MESSAGE.SET_TOKEN('Cont_name',p_container_name);
1441 END IF;
1442
1443 --l_msg_summary := fnd_message.get;
1444
1445 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1446 x_return_status := FND_API.G_RET_STS_ERROR;
1447
1448 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1449 --
1450 -- Debug Statements
1451 --
1452 IF l_debug_on THEN
1453 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1454 END IF;
1455 --
1456 if x_msg_count > 1 then
1457 x_msg_data := l_msg_summary || l_msg_details;
1458 else
1459 x_msg_data := l_msg_summary;
1460 end if;
1461
1462 --
1463 -- Debug Statements
1464 --
1465 IF l_debug_on THEN
1466 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_OPERATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1467 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_OPERATION');
1468 END IF;
1469 --
1470 WHEN WSH_XC_INVALID_LOGGING_ENTITY THEN
1471 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_LOGGING_ENTITY');
1472
1473 --l_msg_summary := fnd_message.get;
1474
1475 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1478 --
1479 -- Debug Statements
1480 --
1481 IF l_debug_on THEN
1482 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1483 END IF;
1484 --
1485 if x_msg_count > 1 then
1486 x_msg_data := l_msg_summary || l_msg_details;
1487 else
1488 x_msg_data := l_msg_summary;
1489 end if;
1490
1491 --
1492 -- Debug Statements
1493 --
1494 IF l_debug_on THEN
1495 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_LOGGING_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_LOGGING_ENTITY');
1497 END IF;
1498 --
1499 WHEN WSH_XC_INVALID_SEVERITY THEN
1500 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_SEVERITY');
1501
1502 --l_msg_summary := fnd_message.get;
1503
1504 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1505 x_return_status := FND_API.G_RET_STS_ERROR;
1506 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1507 --
1508 -- Debug Statements
1509 --
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1512 END IF;
1513 --
1514 if x_msg_count > 1 then
1515 x_msg_data := l_msg_summary || l_msg_details;
1516 else
1517 x_msg_data := l_msg_summary;
1518 end if;
1519
1520
1521 --
1522 -- Debug Statements
1523 --
1524 IF l_debug_on THEN
1525 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_SEVERITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_SEVERITY');
1527 END IF;
1528 --
1529 WHEN WSH_XC_INVALID_LOCATION THEN
1530 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_LOCATION');
1531
1532 --l_msg_summary := fnd_message.get;
1533
1534 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1537 --
1538 -- Debug Statements
1539 --
1540 IF l_debug_on THEN
1541 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1542 END IF;
1543 --
1544 if x_msg_count > 1 then
1545 x_msg_data := l_msg_summary || l_msg_details;
1546 else
1547 x_msg_data := l_msg_summary;
1548 end if;
1549
1550
1551 --
1552 -- Debug Statements
1553 --
1554 IF l_debug_on THEN
1555 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_LOCATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_LOCATION');
1557 END IF;
1558 --
1559 WHEN WSH_XC_NOT_FOUND THEN
1560 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_NOT_FOUND');
1561
1562 --l_msg_summary := fnd_message.get;
1563
1564 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1565 x_return_status := FND_API.G_RET_STS_ERROR;
1566 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1567 --
1568 -- Debug Statements
1569 --
1570 IF l_debug_on THEN
1571 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1572 END IF;
1573 --
1574 if x_msg_count > 1 then
1575 x_msg_data := l_msg_summary || l_msg_details;
1576 else
1577 x_msg_data := l_msg_summary;
1578 end if;
1579
1580
1581 --
1582 -- Debug Statements
1583 --
1584 IF l_debug_on THEN
1585 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1586 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_NOT_FOUND');
1587 END IF;
1588 --
1589 WHEN WSH_XC_DEF_NOT_FOUND THEN
1590
1591 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1592 x_return_status := FND_API.G_RET_STS_ERROR;
1593 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1594 --
1595 -- Debug Statements
1596 --
1597 IF l_debug_on THEN
1598 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1599 END IF;
1600 --
1601 if x_msg_count > 1 then
1602 x_msg_data := l_msg_summary || l_msg_details;
1603 else
1604 x_msg_data := l_msg_summary;
1605 end if;
1606
1607 --
1608 -- Debug Statements
1609 --
1610 IF l_debug_on THEN
1611 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1612 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_NOT_FOUND');
1613 END IF;
1614 --
1615 WHEN WSH_XC_DATA_ERROR THEN
1616 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DATA_ERROR');
1617
1618 --l_msg_summary := fnd_message.get;
1619
1620 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1621 x_return_status := FND_API.G_RET_STS_ERROR;
1622 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1623 --
1624 -- Debug Statements
1625 --
1626 IF l_debug_on THEN
1627 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1628 END IF;
1629 --
1630 if x_msg_count > 1 then
1631 x_msg_data := l_msg_summary || l_msg_details;
1632 else
1633 x_msg_data := l_msg_summary;
1634 end if;
1635
1636
1637 --
1638 -- Debug Statements
1639 --
1640 IF l_debug_on THEN
1641 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DATA_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1642 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DATA_ERROR');
1643 END IF;
1644 --
1645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1646 ROLLBACK TO Log_Exception_PUB ;
1647
1648 l_msg_summary := 'unexpected error';
1649 --
1650 -- Debug Statements
1651 --
1652 IF l_debug_on THEN
1653 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
1654 END IF;
1655 --
1656
1657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1658 IF FND_MSG_PUB.check_msg_level
1659 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1660 THEN
1661 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1662 END IF;
1663 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
1664
1665
1666 --
1667 -- Debug Statements
1668 --
1669 IF l_debug_on THEN
1670 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1671 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1672 END IF;
1673 --
1674 WHEN OTHERS THEN
1675 ROLLBACK TO Log_Exception_PUB ;
1676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1677 IF FND_MSG_PUB.check_msg_level
1678 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1679 THEN
1680 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1681 END IF;
1682 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
1683
1684 --
1685 -- Debug Statements
1686 --
1687 IF l_debug_on THEN
1688 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1689 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1690 END IF;
1691 --
1692 END log_exception;
1693
1694 -- ---------------------------------------------------------------------
1695 -- Start of comments
1696 -- API name : change_status
1697 -- Type : Public
1698 -- Function : If the p_old_status matches the current exception status,
1699 -- this procedure will change the status in two ways:
1700 -- 1) if p_set_default_status = FND_API.G_TRUE (i.e. 'T'),
1701 -- then it sets the exception to default status
1702 -- 2) if p_set_default_status is missing, it sets the
1703 -- exception to x_new_status
1704 --
1705 -- Pre-reqs : The existance of the exception
1706 -- Version : Initial version 1.0
1707 -- Notes :
1708 --
1709 -- End of comments
1710 -- ---------------------------------------------------------------------
1711
1712
1713 PROCEDURE change_status(
1714 -- standard parameters
1715 p_api_version IN NUMBER,
1716 p_init_msg_list IN VARCHAR2,
1717 p_commit IN VARCHAR2,
1718 p_validation_level IN NUMBER,
1719 x_return_status OUT NOCOPY VARCHAR2,
1720 x_msg_count OUT NOCOPY NUMBER,
1721 x_msg_data OUT NOCOPY VARCHAR2,
1722
1723 -- program specific parameters
1724 p_exception_id IN NUMBER,
1725 p_old_status IN VARCHAR2,
1726 p_set_default_status IN VARCHAR2,
1727 x_new_status IN OUT NOCOPY VARCHAR2
1728 ) IS
1729
1730 l_api_version CONSTANT NUMBER := 1.0;
1731 l_api_name CONSTANT VARCHAR2(30) := 'Change_Status';
1732
1733
1734 CURSOR C1(c_exception_id2 NUMBER) IS
1735 SELECT UPPER(exception_name),
1736 DECODE( UPPER(status), 'MANUAL','OPEN' ,
1737 'LOGGED','OPEN' ,
1738 'IN_PROCESS','OPEN' ,
1739 'ERROR','OPEN' ,
1740 'NOT_HANDLED','NO_ACTION_REQUIRED' ,
1741 NULL,'NO_ACTION_REQUIRED' ,
1742 UPPER(status)
1743 )
1744 FROM wsh_exceptions
1745 WHERE exception_id = c_exception_id2;
1746
1747 CURSOR C2(c_exception_id NUMBER) IS
1748 SELECT UPPER(default_severity), UPPER(exception_handling),
1749 UPPER(initiate_workflow)
1750 FROM wsh_exception_definitions_vl
1751 WHERE exception_name = (
1752 SELECT exception_name
1753 FROM wsh_exceptions
1754 WHERE exception_id = c_exception_id);
1755
1756 l_exception_name VARCHAR2(30);
1757 l_new_status VARCHAR2(30) := NULL;
1758 l_old_status VARCHAR2(30);
1759 l_p_old_status VARCHAR2(30);
1760
1761 l_lookups_status VARCHAR2(30);
1762 l_status_valid VARCHAR2(1) := FND_API.G_FALSE;
1763 l_exception_handling VARCHAR2(16);
1764 l_initiate_workflow VARCHAR2(1) := 'N';
1765 l_severity VARCHAR2(10);
1766
1767 -- local variable to hold the token for WSH_XC_INVALID_OPERATION
1768 l_msg_name VARCHAR2(30) := NULL;
1769 l_msg_summary VARCHAR2(2000) := NULL;
1770 l_msg_details VARCHAR2(4000) := NULL;
1771
1772
1773 WSH_XC_STATUS_MISMATCH EXCEPTION;
1774
1775 -- current status does not match p_old_status
1776 WSH_XC_NOT_FOUND EXCEPTION; -- exception not found
1777 WSH_XC_INVALID_OPERATION EXCEPTION; -- operation not allowed
1778 WSH_XC_INVALID_STATUS EXCEPTION; -- the new status is not valid
1779 WSH_XC_DATA_ERROR EXCEPTION;
1780 WSH_XC_EXCEPTION_CLOSED EXCEPTION;
1781 WSH_XC_OTM_ERROR EXCEPTION; -- OTM R12 glog project
1782
1783 --
1784 l_debug_on BOOLEAN;
1785 --
1786 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_STATUS';
1787 --
1788 BEGIN
1789 -- Standard begin of API savepoint
1790 --
1791 -- Debug Statements
1792 --
1793 --
1794 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1795 --
1796 IF l_debug_on IS NULL
1797 THEN
1798 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1799 END IF;
1800 --
1801 IF l_debug_on THEN
1802 WSH_DEBUG_SV.push(l_module_name);
1803 --
1804 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1805 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1806 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1807 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1808 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_ID',P_EXCEPTION_ID);
1809 WSH_DEBUG_SV.log(l_module_name,'P_OLD_STATUS',P_OLD_STATUS);
1810 WSH_DEBUG_SV.log(l_module_name,'P_SET_DEFAULT_STATUS',P_SET_DEFAULT_STATUS);
1811 WSH_DEBUG_SV.log(l_module_name,'X_NEW_STATUS',X_NEW_STATUS);
1812 END IF;
1813 --
1814 SAVEPOINT Change_Status_PUB;
1815 -- Standard call to check for call compatibility.
1816 IF NOT FND_API.compatible_api_call(l_api_version,
1817 p_api_version,
1818 l_api_name,
1819 G_PKG_NAME) THEN
1820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1821 END IF;
1822
1823 -- Check p_init_msg_list
1824 IF FND_API.to_boolean(p_init_msg_list) THEN
1825 FND_MSG_PUB.initialize;
1826 END IF;
1827
1828 -- initialize API return status to success
1829 x_return_status := FND_API.G_RET_STS_SUCCESS;
1830
1831 -- Converting the p_old_status to applicable status (OPEN,CLOSED,NO_ACTION_REQUIRED) since we do not upgrade old exceptions status to new
1832 -- However new exceptions will be logged with new valid status
1833 -- If p_old_status is CLOSED, then raise error
1834 IF p_old_status IN ('MANUAL','LOGGED','IN_PROCESS','ERROR') THEN
1835 l_p_old_status := 'OPEN';
1836 ELSIF p_old_status IN ('NOT_HANDLED',NULL) THEN
1837 l_p_old_status := 'NO_ACTION_REQUIRED';
1838 ELSIF p_old_status IN ('CLOSED') THEN
1839 RAISE WSH_XC_EXCEPTION_CLOSED;
1840 ELSE
1841 l_p_old_status := UPPER(p_old_status);
1842 END IF;
1843
1844 -- validate existence of this exception
1845 OPEN C1(p_exception_id);
1846 FETCH C1 INTO l_exception_name, l_old_status;
1847 IF (C1%FOUND) THEN
1848 IF l_debug_on THEN
1849 WSH_DEBUG_SV.logmsg(l_module_name,'l_old_status:'||l_old_status||' l_p_old_status:'||l_p_old_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1850 END IF;
1851 IF l_old_status <> UPPER(l_p_old_status) THEN
1852 CLOSE C1;
1853 RAISE WSH_XC_STATUS_MISMATCH;
1854 END IF;
1855 ELSE
1856 CLOSE C1;
1857 RAISE WSH_XC_NOT_FOUND;
1858 END IF;
1859
1860 -- validate parameters, p_set_default_status and x_new_status is mutually exclusive
1861 --
1862 IF p_set_default_status = FND_API.G_TRUE THEN
1863 x_new_status := NULL;
1864 OPEN C2(p_exception_id);
1865 FETCH C2 INTO l_severity, l_exception_handling, l_initiate_workflow;
1866
1867 IF (C2%FOUND) THEN
1868 -- Shipping exception enhancement
1869 IF l_severity IN ('ERROR', 'WARNING') THEN
1870 l_new_status := 'OPEN';
1871 ELSE
1872 l_new_status := 'NO_ACTION_REQUIRED';
1873 END IF;
1874
1875 ELSE
1876 -- exception name not found in wsh_exception_definitions_vl
1877 CLOSE C2;
1878 RAISE WSH_XC_DATA_ERROR;
1879 END IF;
1880
1881 CLOSE C2;
1882
1883 ELSE -- p_set_default_status is false
1884
1885 IF x_new_status IS NULL THEN
1886 l_msg_name := 'WSH_XC_STATUS_REQUIRED';
1887 RAISE WSH_XC_INVALID_OPERATION;
1888 ELSIF l_p_old_status = 'CLOSED' THEN
1889 RAISE WSH_XC_EXCEPTION_CLOSED;
1890 ELSIF UPPER(x_new_status) = 'CLOSED' THEN
1891 l_new_status := UPPER(x_new_status);
1892 ELSE
1893 RAISE WSH_XC_INVALID_STATUS;
1894 END IF;
1895
1896 END IF;
1897
1898 -- OTM R12 glog project
1899 --IF l_exception_name = 'WSH_OTM_SHIPMENT_ERROR' AND
1900 IF l_exception_name = C_OTM_EXC_NAME AND
1901 l_new_status = 'CLOSED' THEN
1902 RAISE WSH_XC_OTM_ERROR;
1903 END IF;
1904 -- OTM R12 end of glog project
1905
1906 UPDATE wsh_exceptions
1907 SET status = l_new_status,
1908 last_update_date = SYSDATE,
1909 last_updated_by = FND_GLOBAL.USER_ID,
1910 last_update_login = FND_GLOBAL.LOGIN_ID
1911 WHERE exception_id = p_exception_id
1912 AND DECODE( UPPER(status),
1913 'MANUAL','OPEN' ,
1914 'LOGGED','OPEN' ,
1915 'IN_PROCESS','OPEN' ,
1916 'ERROR','OPEN' ,
1917 'NOT_HANDLED','NO_ACTION_REQUIRED' ,
1918 NULL,'NO_ACTION_REQUIRED' ,
1919 UPPER(status)
1920 ) = l_p_old_status ;
1921
1922 IF FND_API.TO_BOOLEAN(p_commit) THEN
1923 COMMIT WORK;
1924 END IF;
1925
1926 x_new_status := l_new_status;
1927
1928 --
1929 -- Debug Statements
1930 --
1931 IF l_debug_on THEN
1932 WSH_DEBUG_SV.pop(l_module_name);
1933 END IF;
1934 --
1935 EXCEPTION
1936
1937 -- OTM R12 glog project
1938 WHEN WSH_XC_OTM_ERROR THEN
1939 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_OTM_EXCEPTION');
1940 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1941 x_return_status := FND_API.G_RET_STS_ERROR;
1942 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1943 if x_msg_count > 1 then
1944 x_msg_data := l_msg_summary || l_msg_details;
1945 else
1946 x_msg_data := l_msg_summary;
1947 end if;
1948 --
1949 -- Debug Statements
1950 --
1951 IF l_debug_on THEN
1952 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_OTM_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1953 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_OTM_ERROR');
1954 END IF;
1955 -- OTM R12 end of glog project
1956
1957 WHEN WSH_XC_INVALID_STATUS THEN
1958 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_INVALID_STATUS');
1959 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1960 x_return_status := FND_API.G_RET_STS_ERROR;
1961 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1962 if x_msg_count > 1 then
1963 x_msg_data := l_msg_summary || l_msg_details;
1964 else
1965 x_msg_data := l_msg_summary;
1966 end if;
1967
1968
1969 --
1970 -- Debug Statements
1971 --
1972 IF l_debug_on THEN
1973 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_STATUS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1974 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_STATUS');
1975 END IF;
1976 --
1977 WHEN WSH_XC_INVALID_OPERATION THEN
1978 FND_MESSAGE.SET_NAME('WSH', l_msg_name);
1979 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1980 x_return_status := FND_API.G_RET_STS_ERROR;
1981 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
1982 if x_msg_count > 1 then
1983 x_msg_data := l_msg_summary || l_msg_details;
1984 else
1985 x_msg_data := l_msg_summary;
1986 end if;
1987
1988 --
1989 -- Debug Statements
1990 --
1991 IF l_debug_on THEN
1992 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_OPERATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1993 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_OPERATION');
1994 END IF;
1995 --
1996 WHEN WSH_XC_STATUS_MISMATCH THEN
1997 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_STATUS_MISMATCH');
1998 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
1999 x_return_status := FND_API.G_RET_STS_ERROR;
2000 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
2001 if x_msg_count > 1 then
2002 x_msg_data := l_msg_summary || l_msg_details;
2003 else
2004 x_msg_data := l_msg_summary;
2005 end if;
2006
2007
2008 --
2009 -- Debug Statements
2010 --
2011 IF l_debug_on THEN
2012 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_STATUS_MISMATCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2013 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_STATUS_MISMATCH');
2014 END IF;
2015 --
2016 WHEN WSH_XC_NOT_FOUND THEN
2017 IF C1%ISOPEN THEN
2018 CLOSE C1;
2019 END IF;
2020 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_NOT_FOUND');
2021 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
2022 x_return_status := FND_API.G_RET_STS_ERROR;
2023 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
2024 if x_msg_count > 1 then
2025 x_msg_data := l_msg_summary || l_msg_details;
2026 else
2027 x_msg_data := l_msg_summary;
2028 end if;
2029
2030
2031 --
2032 -- Debug Statements
2033 --
2034 IF l_debug_on THEN
2035 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2036 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_NOT_FOUND');
2037 END IF;
2038 --
2039 WHEN WSH_XC_DATA_ERROR THEN
2040 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DATA_ERROR');
2041 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
2042 x_return_status := FND_API.G_RET_STS_ERROR;
2043 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
2044 if x_msg_count > 1 then
2045 x_msg_data := l_msg_summary || l_msg_details;
2046 else
2047 x_msg_data := l_msg_summary;
2048 end if;
2049
2050
2051 --
2052 -- Debug Statements
2053 --
2054 IF l_debug_on THEN
2055 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DATA_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2056 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DATA_ERROR');
2057 END IF;
2058 --
2059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2060 ROLLBACK TO Change_Status_PUB ;
2061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2062 IF FND_MSG_PUB.check_msg_level
2063 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2064 THEN
2065 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2066 END IF;
2067 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2068
2069
2070 --
2071 -- Debug Statements
2072 --
2073 IF l_debug_on THEN
2074 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2075 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2076 END IF;
2077 --
2078
2079 WHEN WSH_XC_EXCEPTION_CLOSED THEN
2080 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_EXCEPTION_CLOSED');
2081 FND_MESSAGE.SET_TOKEN('EXCEPTION_ID', P_EXCEPTION_ID);
2082 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
2083 x_return_status := FND_API.G_RET_STS_ERROR;
2084 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
2085 if x_msg_count > 1 then
2086 x_msg_data := l_msg_summary || l_msg_details;
2087 else
2088 x_msg_data := l_msg_summary;
2089 end if;
2090
2091
2092 IF l_debug_on THEN
2093 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_EXCEPTION_CLOSED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2094 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_OPERATION');
2095 END IF;
2096
2097
2098 WHEN OTHERS THEN
2099 ROLLBACK TO Change_Status_PUB ;
2100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2101 IF FND_MSG_PUB.check_msg_level
2102 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2103 THEN
2104 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2105 END IF;
2106 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2107
2108 --
2109 -- Debug Statements
2110 --
2111 IF l_debug_on THEN
2112 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2113 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2114 END IF;
2115 --
2116 END change_status;
2117
2118
2119 -- -------------------------------------------------------------------
2120 -- Start of comments
2121 -- API name : insert_xc_def_form
2122 -- Type : public
2123 -- Function : insert exception definitions, called by form
2124 -- Version : Initial version 1.0
2125 --
2126 -- End of comments
2127 -- ---------------------------------------------------------------------
2128
2129 procedure insert_xc_def_form (
2130 x_exception_definition_id in out NOCOPY NUMBER,
2131 p_exception_name in VARCHAR2,
2132 p_description in VARCHAR2,
2133 p_exception_type in VARCHAR2,
2134 p_default_severity in VARCHAR2,
2135 p_exception_handling in VARCHAR2,
2136 p_workflow_item_type in VARCHAR2,
2137 p_workflow_process in VARCHAR2,
2138 p_initiate_workflow in VARCHAR2,
2139 p_update_allowed in VARCHAR2,
2140 p_enabled in VARCHAR2,
2141 p_attribute_category in VARCHAR2,
2142 p_attribute1 in VARCHAR2,
2143 p_attribute2 in VARCHAR2,
2144 p_attribute3 in VARCHAR2,
2145 p_attribute4 in VARCHAR2,
2146 p_attribute5 in VARCHAR2,
2147 p_attribute6 in VARCHAR2,
2148 p_attribute7 in VARCHAR2,
2149 p_attribute8 in VARCHAR2,
2150 p_attribute9 in VARCHAR2,
2151 p_attribute10 in VARCHAR2,
2152 p_attribute11 in VARCHAR2,
2153 p_attribute12 in VARCHAR2,
2154 p_attribute13 in VARCHAR2,
2155 p_attribute14 in VARCHAR2,
2156 p_attribute15 in VARCHAR2,
2157 p_creation_date in DATE,
2158 p_created_by in NUMBER,
2159 p_last_update_date in DATE,
2160 p_last_updated_by in NUMBER,
2161 p_last_update_login in NUMBER
2162 ) is
2163
2164 cursor C1 (c_exception_name VARCHAR2) is
2165 select exception_name
2166 from wsh_exception_definitions_vl
2167 where exception_name = c_exception_name;
2168
2169 cursor C2 (c_exception_definition_id NUMBER) is
2170 select ROWID from WSH_EXCEPTION_DEFINITIONS_B
2171 where EXCEPTION_DEFINITION_ID =
2172 c_exception_definition_id;
2173
2174 l_exception_name VARCHAR2(30);
2175 l_exception_definition_id NUMBER;
2176 l_rowid VARCHAR2(30) := NULL;
2177
2178 WSH_XC_DEF_DUP exception;
2179 WSH_XC_DEF_NOT_FOUND exception;
2180
2181
2182 --
2183 l_debug_on BOOLEAN;
2184 --
2185 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_XC_DEF_FORM';
2186 --
2187 begin
2188
2189 --
2190 -- Debug Statements
2191 --
2192 --
2193 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2194 --
2195 IF l_debug_on IS NULL
2196 THEN
2197 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2198 END IF;
2199 --
2200 IF l_debug_on THEN
2201 WSH_DEBUG_SV.push(l_module_name);
2202 --
2203 WSH_DEBUG_SV.log(l_module_name,'X_EXCEPTION_DEFINITION_ID',X_EXCEPTION_DEFINITION_ID);
2204 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
2205 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION',P_DESCRIPTION);
2206 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_TYPE',P_EXCEPTION_TYPE);
2207 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_SEVERITY',P_DEFAULT_SEVERITY);
2208 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_HANDLING',P_EXCEPTION_HANDLING);
2209 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_ITEM_TYPE',P_WORKFLOW_ITEM_TYPE);
2210 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_PROCESS',P_WORKFLOW_PROCESS);
2211 WSH_DEBUG_SV.log(l_module_name,'P_INITIATE_WORKFLOW',P_INITIATE_WORKFLOW);
2212 WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
2213 WSH_DEBUG_SV.log(l_module_name,'P_ENABLED',P_ENABLED);
2214 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
2215 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
2216 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
2217 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
2218 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
2219 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
2220 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
2221 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
2222 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
2223 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
2224 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
2225 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
2226 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
2227 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
2228 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
2229 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
2230 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
2231 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
2232 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
2233 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
2234 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
2235 END IF;
2236 --
2237 open C1(p_exception_name);
2238 fetch C1 into l_exception_name;
2239 if (C1%NOTFOUND) then
2240 if (x_exception_definition_id is not NULL) then
2241 open C2(x_exception_definition_id);
2242 fetch C2 into l_rowid;
2243 if l_rowid is not NULL then
2244 raise WSH_XC_DEF_DUP;
2245 else
2246 l_exception_definition_id := x_exception_definition_id;
2247 end if;
2248 close C2;
2249 else
2250 -- populate l_exception_id
2251 SELECT wsh_exception_definitions_s.nextval INTO l_exception_definition_id FROM SYS.DUAL;
2252 end if;
2253 insert into wsh_exception_definitions_b (
2254 exception_definition_id,
2255 exception_type,
2256 default_severity,
2257 exception_handling,
2258 workflow_item_type,
2259 workflow_process,
2260 initiate_workflow,
2261 update_allowed,
2262 enabled,
2263 attribute_category,
2264 attribute1,
2265 attribute2,
2266 attribute3,
2267 attribute4,
2268 attribute5,
2269 attribute6,
2270 attribute7,
2271 attribute8,
2272 attribute9,
2273 attribute10,
2274 attribute11,
2275 attribute12,
2276 attribute13,
2277 attribute14,
2278 attribute15,
2279 creation_date,
2280 created_by,
2281 last_update_date,
2282 last_updated_by,
2283 last_update_login
2284 ) values (
2285 l_exception_definition_id,
2286 UPPER(p_exception_type),
2287 UPPER(p_default_severity),
2288 UPPER(p_exception_handling),
2289 UPPER(p_workflow_item_type),
2290 UPPER(p_workflow_process),
2291 UPPER(p_initiate_workflow),
2292 UPPER(p_update_allowed),
2293 UPPER(p_enabled),
2294 p_attribute_category,
2295 p_attribute1,
2296 p_attribute2,
2297 p_attribute3,
2298 p_attribute4,
2299 p_attribute5,
2300 p_attribute6,
2301 p_attribute7,
2302 p_attribute8,
2303 p_attribute9,
2304 p_attribute10,
2305 p_attribute11,
2306 p_attribute12,
2307 p_attribute13,
2308 p_attribute14,
2309 p_attribute15,
2310 p_creation_date,
2311 p_created_by,
2312 p_last_update_date,
2313 p_last_updated_by,
2314 p_last_update_login
2315 );
2316
2317
2318 insert into WSH_EXCEPTION_DEFINITIONS_TL (
2319 EXCEPTION_DEFINITION_ID,
2320 EXCEPTION_NAME,
2321 DESCRIPTION,
2322 CREATION_DATE,
2323 CREATED_BY,
2324 LAST_UPDATE_DATE,
2325 LAST_UPDATED_BY,
2326 LAST_UPDATE_LOGIN,
2327 LANGUAGE,
2328 SOURCE_LANG
2329 ) select
2330 l_exception_definition_id,
2331 p_exception_name,
2332 p_description,
2333 p_creation_date,
2334 p_created_by,
2335 p_last_update_date,
2336 p_last_updated_by,
2337 p_last_update_login,
2338 L.LANGUAGE_CODE,
2339 userenv('LANG')
2340 from FND_LANGUAGES L
2341 where L.INSTALLED_FLAG in ('I', 'B')
2342 and not exists
2343 (select NULL
2344 from WSH_EXCEPTION_DEFINITIONS_TL T
2345 where T.EXCEPTION_DEFINITION_ID = l_exception_definition_id
2346 and T.LANGUAGE = L.LANGUAGE_CODE);
2347
2348
2349
2350 open C2 (l_exception_definition_id);
2351 fetch C2 into l_rowid;
2352 if (C2%notfound) then
2353 close C2;
2354 raise WSH_XC_DEF_NOT_FOUND;
2355 end if;
2356 close C2;
2357 close C1;
2358 else
2359 close C1;
2360 raise WSH_XC_DEF_DUP;
2361 end if;
2362 x_exception_definition_id := l_exception_definition_id;
2363
2364 --
2365 -- Debug Statements
2366 --
2367 IF l_debug_on THEN
2368 WSH_DEBUG_SV.pop(l_module_name);
2369 END IF;
2370 --
2371 exception
2372
2373 WHEN WSH_XC_DEF_DUP THEN
2374 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DEF_DUP');
2375 APP_EXCEPTION.RAISE_EXCEPTION;
2376
2377 --
2378 -- Debug Statements
2379 --
2380 IF l_debug_on THEN
2381 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_DUP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2382 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_DUP');
2383 END IF;
2384 --
2385 WHEN WSH_XC_DEF_NOT_FOUND THEN
2386 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_NOT_FOUND');
2387 APP_EXCEPTION.RAISE_EXCEPTION;
2388
2389 --
2390 -- Debug Statements
2391 --
2392 IF l_debug_on THEN
2393 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2394 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_NOT_FOUND');
2395 END IF;
2396 --
2397 end insert_xc_def_form;
2398
2399
2400
2401
2402
2403 -- -------------------------------------------------------------------
2404 -- Start of comments
2405 -- API name : update_xc_def_form
2406 -- Type : public
2407 -- Function : update exception definitions, called by form
2408 -- Version : Initial version 1.0
2409 --
2410 -- End of comments
2411 -- ---------------------------------------------------------------------
2412 procedure update_xc_def_form (
2413 p_exception_definition_id in NUMBER,
2414 p_exception_name in VARCHAR2,
2415 p_description in VARCHAR2,
2416 p_exception_type in VARCHAR2,
2417 p_default_severity in VARCHAR2,
2418 p_exception_handling in VARCHAR2,
2419 p_workflow_item_type in VARCHAR2,
2420 p_workflow_process in VARCHAR2,
2421 p_initiate_workflow in VARCHAR2,
2422 p_update_allowed in VARCHAR2,
2423 p_enabled in VARCHAR2,
2424 p_attribute_category in VARCHAR2,
2425 p_attribute1 in VARCHAR2,
2426 p_attribute2 in VARCHAR2,
2427 p_attribute3 in VARCHAR2,
2428 p_attribute4 in VARCHAR2,
2429 p_attribute5 in VARCHAR2,
2430 p_attribute6 in VARCHAR2,
2431 p_attribute7 in VARCHAR2,
2432 p_attribute8 in VARCHAR2,
2433 p_attribute9 in VARCHAR2,
2434 p_attribute10 in VARCHAR2,
2435 p_attribute11 in VARCHAR2,
2436 p_attribute12 in VARCHAR2,
2437 p_attribute13 in VARCHAR2,
2438 p_attribute14 in VARCHAR2,
2439 p_attribute15 in VARCHAR2,
2440 p_creation_date in DATE,
2441 p_created_by in NUMBER,
2442 p_last_update_date in DATE,
2443 p_last_updated_by in NUMBER,
2444 p_last_update_login in NUMBER,
2445 p_caller in VARCHAR2 -- 5986504
2446 ) is
2447 l_exception_name VARCHAR2(30);
2448 l_update_allowed VARCHAR2(1);
2449 --5986504
2450
2451 cursor C2 (c_exception_definition_id NUMBER) is
2452 select update_allowed from WSH_EXCEPTION_DEFINITIONS_B
2453 where EXCEPTION_DEFINITION_ID =
2454 c_exception_definition_id;
2455 --
2456 WSH_XC_DEF_NOT_FOUND exception;
2457 --
2458 l_debug_on BOOLEAN;
2459 --
2460 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_XC_DEF_FORM';
2461 --
2462 begin
2463 --
2464 -- Debug Statements
2465 --
2466 --
2467 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2468 --
2469 IF l_debug_on IS NULL
2470 THEN
2471 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2472 END IF;
2473 --
2474 -- 5986504
2475 open C2 (p_exception_definition_id);
2476 fetch C2 into l_update_allowed;
2477 close C2;
2478 --
2479 IF l_debug_on THEN
2480 WSH_DEBUG_SV.push(l_module_name);
2481 --
2482 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_DEFINITION_ID',P_EXCEPTION_DEFINITION_ID);
2483 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
2484 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION',P_DESCRIPTION);
2485 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_TYPE',P_EXCEPTION_TYPE);
2486 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_SEVERITY',P_DEFAULT_SEVERITY);
2487 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_HANDLING',P_EXCEPTION_HANDLING);
2488 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_ITEM_TYPE',P_WORKFLOW_ITEM_TYPE);
2489 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_PROCESS',P_WORKFLOW_PROCESS);
2490 WSH_DEBUG_SV.log(l_module_name,'P_INITIATE_WORKFLOW',P_INITIATE_WORKFLOW);
2491 WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
2492 WSH_DEBUG_SV.log(l_module_name,'P_ENABLED',P_ENABLED);
2493 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
2494 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
2495 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
2496 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
2497 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
2498 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
2499 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
2500 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
2501 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
2502 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
2503 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
2504 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
2505 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
2506 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
2507 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
2508 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
2509 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
2510 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
2511 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
2512 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
2513 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
2514 WSH_DEBUG_SV.log(l_module_name,'L_UPDATE_ALLOWED -in Db ', L_UPDATE_ALLOWED); --5986504
2515 WSH_DEBUG_SV.log(l_module_name,'L_CALLER -Loader / Form ', P_CALLER); --5986504
2516 END IF;
2517 -- 5986504{ If Caller is Loader then Either p_update_allowed should be N or a Flip (Y -> N or N-Y)
2518 -- should have taken place to be Elligible for Update.
2519 -- If Caller is Form , then Updates are Elligible
2520 if ( nvl(p_caller, 'FORM') = 'LOADER' and ( upper(p_update_allowed) = 'N' or
2521 (upper(p_update_allowed) = 'Y' and l_update_allowed = 'N')) ) OR
2522 ( nvl(p_caller, 'FORM') = 'FORM' ) THEN
2523 update wsh_exception_definitions_b
2524 set
2525 exception_type = p_exception_type,
2526 default_severity = UPPER(p_default_severity),
2527 exception_handling = UPPER(p_exception_handling),
2528 workflow_item_type = UPPER(p_workflow_item_type),
2529 workflow_process = UPPER(p_workflow_process),
2530 initiate_workflow = UPPER(p_initiate_workflow),
2531 update_allowed = UPPER(p_update_allowed),
2532 enabled = UPPER(p_enabled),
2533 attribute_category = p_attribute_category,
2534 attribute1 = p_attribute1,
2535 attribute2 = p_attribute2,
2536 attribute3 = p_attribute3,
2537 attribute4 = p_attribute4,
2538 attribute5 = p_attribute5,
2539 attribute6 = p_attribute6,
2540 attribute7 = p_attribute7,
2541 attribute8 = p_attribute8,
2542 attribute9 = p_attribute9,
2543 attribute10 = p_attribute10,
2544 attribute11 = p_attribute11,
2545 attribute12 = p_attribute12,
2546 attribute13 = p_attribute13,
2547 attribute14 = p_attribute14,
2548 attribute15 = p_attribute15,
2549 creation_date = p_creation_date,
2550 created_by = p_created_by,
2551 last_update_date = p_last_update_date,
2552 last_updated_by = p_last_updated_by,
2553 last_update_login = p_last_update_login
2554 where EXCEPTION_DEFINITION_ID = p_exception_definition_id ;
2555 if (sql%notfound) then
2556 raise WSH_XC_DEF_NOT_FOUND;
2557 end if;
2558
2559 -- 5986504
2560 IF l_debug_on THEN
2561 WSH_DEBUG_SV.log(l_module_name,'After Base Table Update ... ', P_CALLER);
2562 END IF;
2563
2564 update WSH_EXCEPTION_DEFINITIONS_TL set
2565 EXCEPTION_NAME = p_exception_name,
2566 DESCRIPTION = p_description,
2567 LAST_UPDATE_DATE = p_last_update_date,
2568 LAST_UPDATED_BY = p_last_updated_by,
2569 LAST_UPDATE_LOGIN = p_last_update_login,
2570 SOURCE_LANG = userenv('LANG')
2571 where EXCEPTION_DEFINITION_ID = p_exception_definition_id
2572 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2573
2574 if (sql%notfound) then
2575 raise WSH_XC_DEF_NOT_FOUND;
2576 end if;
2577
2578 -- 5986504
2579 IF l_debug_on THEN
2580 WSH_DEBUG_SV.log(l_module_name,'After TL Table Update ... ', P_CALLER);
2581 WSH_DEBUG_SV.logmsg(l_module_name,'After Updates '||P_EXCEPTION_DEFINITION_ID,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2582 WSH_DEBUG_SV.logmsg(l_module_name,'After Updates '||P_CALLER,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2583 END IF;
2584
2585 end if; -- 5986504 }
2586
2587 --
2588 -- Debug Statements
2589 --
2590 IF l_debug_on THEN
2591 WSH_DEBUG_SV.pop(l_module_name);
2592 END IF;
2593 --
2594 exception
2595
2596 WHEN WSH_XC_DEF_NOT_FOUND THEN
2597 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DEF_NOT_FOUND');
2598 IF l_debug_on THEN
2599 WSH_DEBUG_SV.log(l_module_name,'- Failed in Update .. ', P_CALLER);
2600 END IF;
2601 APP_EXCEPTION.RAISE_EXCEPTION;
2602
2603 --
2604 -- Debug Statements
2605 --
2606 IF l_debug_on THEN
2607 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2608 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_NOT_FOUND');
2609 END IF;
2610 --
2611 end update_xc_def_form;
2612
2613
2614
2615
2616
2617 -- ---------------------------------------------------------------------
2618 -- procedure: Load_Row
2619 -- description: called by the generic loader to upload exception definition
2620 -- This procedure should be called only by generic loader
2621 -- no one else should call this procedure.
2622 -- ---------------------------------------------------------------------
2623 procedure Load_Row (
2624 p_language in VARCHAR2,
2625 p_source_lang in VARCHAR2,
2626 p_exception_definition_id in NUMBER,
2627 p_exception_name in VARCHAR2,
2628 p_description in VARCHAR2,
2629 p_exception_type in VARCHAR2,
2630 p_default_severity in VARCHAR2,
2631 p_exception_handling in VARCHAR2,
2632 p_workflow_item_type in VARCHAR2,
2633 p_workflow_process in VARCHAR2,
2634 p_initiate_workflow in VARCHAR2,
2635 p_update_allowed in VARCHAR2,
2636 p_enabled in VARCHAR2,
2637 p_attribute_category in VARCHAR2,
2638 p_attribute1 in VARCHAR2,
2639 p_attribute2 in VARCHAR2,
2640 p_attribute3 in VARCHAR2,
2641 p_attribute4 in VARCHAR2,
2642 p_attribute5 in VARCHAR2,
2643 p_attribute6 in VARCHAR2,
2644 p_attribute7 in VARCHAR2,
2645 p_attribute8 in VARCHAR2,
2646 p_attribute9 in VARCHAR2,
2647 p_attribute10 in VARCHAR2,
2648 p_attribute11 in VARCHAR2,
2649 p_attribute12 in VARCHAR2,
2650 p_attribute13 in VARCHAR2,
2651 p_attribute14 in VARCHAR2,
2652 p_attribute15 in VARCHAR2,
2653 p_creation_date in DATE,
2654 p_created_by in NUMBER,
2655 p_last_update_date in DATE,
2656 p_last_updated_by in NUMBER,
2657 p_last_update_login in NUMBER
2658
2659 ) is
2660 cursor C1 (c_exception_name VARCHAR2) is
2661 select exception_definition_id from WSH_EXCEPTION_DEFINITIONS_TL
2662 where EXCEPTION_NAME = c_exception_name
2663 and SOURCE_LANG = userenv('LANG');
2664
2665
2666 -- 5986504
2667 l_exception_definition_id NUMBER := NULL;
2668 l_caller VARCHAR2(30) := 'LOADER';
2669
2670 WSH_XC_DEF_DUP exception;
2671
2672 --
2673 l_debug_on BOOLEAN;
2674 --
2675 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_ROW';
2676 --
2677 begin
2678
2679 --
2680 -- Debug Statements
2681 --
2682 --
2683 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2684 --
2685 IF l_debug_on IS NULL
2686 THEN
2687 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2688 END IF;
2689 --
2690 IF l_debug_on THEN
2691 WSH_DEBUG_SV.push(l_module_name);
2692 --
2693 WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE',P_LANGUAGE);
2694 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LANG',P_SOURCE_LANG);
2695 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_DEFINITION_ID',P_EXCEPTION_DEFINITION_ID);
2696 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
2697 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION',P_DESCRIPTION);
2698 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_TYPE',P_EXCEPTION_TYPE);
2699 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_SEVERITY',P_DEFAULT_SEVERITY);
2700 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_HANDLING',P_EXCEPTION_HANDLING);
2701 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_ITEM_TYPE',P_WORKFLOW_ITEM_TYPE);
2702 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_PROCESS',P_WORKFLOW_PROCESS);
2703 WSH_DEBUG_SV.log(l_module_name,'P_INITIATE_WORKFLOW',P_INITIATE_WORKFLOW);
2704 WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
2705 WSH_DEBUG_SV.log(l_module_name,'P_ENABLED',P_ENABLED);
2706 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
2707 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
2708 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
2709 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
2710 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
2711 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
2712 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
2713 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
2714 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
2715 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
2716 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
2717 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
2718 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
2719 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
2720 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
2721 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
2722 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
2723 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
2724 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
2725 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
2726 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
2727 WSH_DEBUG_SV.log(l_module_name,'L_CALLER', l_caller); -- 5986504
2728 END IF;
2729 --
2730 if (p_exception_name is not NULL ) then
2731 if (p_source_lang = userenv('LANG')) then
2732 open C1(p_exception_name);
2733 fetch C1 into l_exception_definition_id ;
2734 if (C1%NOTFOUND) then
2735 l_exception_definition_id := p_exception_definition_id;
2736 insert_xc_def_form(
2737 x_exception_definition_id=>l_exception_definition_id,
2738 p_exception_name=> p_exception_name,
2739 p_description=> p_description,
2740 p_exception_type=>p_exception_type,
2741 p_default_severity=>p_default_severity,
2742 p_exception_handling=>p_exception_handling,
2743 p_workflow_item_type=>p_workflow_item_type,
2744 p_workflow_process=>p_workflow_process,
2745 p_initiate_workflow=>p_initiate_workflow,
2746 p_update_allowed=>p_update_allowed,
2747 p_enabled=>p_enabled,
2748 p_attribute_category=>p_attribute_category,
2749 p_attribute1=>p_attribute1,
2750 p_attribute2=>p_attribute2,
2751 p_attribute3=>p_attribute3,
2752 p_attribute4=>p_attribute4,
2753 p_attribute5=>p_attribute5,
2754 p_attribute6=>p_attribute6,
2755 p_attribute7=>p_attribute7,
2756 p_attribute8=>p_attribute8,
2757 p_attribute9=>p_attribute9,
2758 p_attribute10=>p_attribute10,
2759 p_attribute11=>p_attribute11,
2760 p_attribute12=>p_attribute12,
2761 p_attribute13=>p_attribute13,
2762 p_attribute14=>p_attribute14,
2763 p_attribute15=>p_attribute15,
2764 p_creation_date=> p_creation_date,
2765 p_created_by=> p_created_by,
2766 p_last_update_date=>p_last_update_date,
2767 p_last_updated_by=>p_last_updated_by,
2768 p_last_update_login=>p_last_update_login
2769 );
2770
2771 else
2772 -- if l_exception_definition_id <> p_exception_definition_id then
2773 /* update all columns except exception_definition_id */
2774 -- raise WSH_XC_DEF_DUP;
2775 -- else
2776 update_xc_def_form(
2777 p_exception_definition_id=> l_exception_definition_id,
2778 p_exception_name=> p_exception_name,
2779 p_description=> p_description,
2780 p_exception_type=>p_exception_type,
2781 p_default_severity=>p_default_severity,
2782 p_exception_handling=>p_exception_handling,
2783 p_workflow_item_type=>p_workflow_item_type,
2784 p_workflow_process=>p_workflow_process,
2785 p_initiate_workflow=>p_initiate_workflow,
2786 p_update_allowed=>p_update_allowed,
2787 p_enabled=>p_enabled,
2788 p_attribute_category=>p_attribute_category,
2789 p_attribute1=>p_attribute1,
2790 p_attribute2=>p_attribute2,
2791 p_attribute3=>p_attribute3,
2792 p_attribute4=>p_attribute4,
2793 p_attribute5=>p_attribute5,
2794 p_attribute6=>p_attribute6,
2795 p_attribute7=>p_attribute7,
2796 p_attribute8=>p_attribute8,
2797 p_attribute9=>p_attribute9,
2798 p_attribute10=>p_attribute10,
2799 p_attribute11=>p_attribute11,
2800 p_attribute12=>p_attribute12,
2801 p_attribute13=>p_attribute13,
2802 p_attribute14=>p_attribute14,
2803 p_attribute15=>p_attribute15,
2804 p_creation_date=>p_creation_date,
2805 p_created_by=>p_created_by,
2806 p_last_update_date=>p_last_update_date,
2807 p_last_updated_by=>p_last_updated_by,
2808 p_last_update_login=>p_last_update_login,
2809 p_caller=>l_caller
2810 );
2811 -- end if;
2812 end if;
2813 close C1;
2814 end if;
2815 end if;
2816
2817
2818 --
2819 -- Debug Statements
2820 --
2821 IF l_debug_on THEN
2822 WSH_DEBUG_SV.pop(l_module_name);
2823 END IF;
2824 --
2825 exception
2826
2827 WHEN WSH_XC_DEF_DUP THEN
2828 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DEF_DUP');
2829 APP_EXCEPTION.RAISE_EXCEPTION;
2830
2831 --
2832 -- Debug Statements
2833 --
2834 IF l_debug_on THEN
2835 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_DUP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2836 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_DUP');
2837 END IF;
2838 --
2839 end Load_Row;
2840
2841
2842
2843
2844
2845 -- ---------------------------------------------------------------------
2846 -- procedure: Translate_Row
2847 -- description: called by the generic loader to translate exception definition
2848 -- This procedure should be called only by generic loader
2849 -- no one else should call this procedure.
2850 -- ---------------------------------------------------------------------
2851 procedure Translate_Row (
2852 p_exception_definition_id in number,
2853 p_exception_name in VARCHAR2,
2854 p_description in VARCHAR2,
2855 p_owner in VARCHAR2
2856 ) is
2857 --
2858 l_debug_on BOOLEAN;
2859 --
2860 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRANSLATE_ROW';
2861 --
2862 begin
2863 --
2864 -- Debug Statements
2865 --
2866 --
2867 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2868 --
2869 IF l_debug_on IS NULL
2870 THEN
2871 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2872 END IF;
2873 --
2874 IF l_debug_on THEN
2875 WSH_DEBUG_SV.push(l_module_name);
2876 --
2877 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_DEFINITION_ID',P_EXCEPTION_DEFINITION_ID);
2878 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
2879 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION',P_DESCRIPTION);
2880 WSH_DEBUG_SV.log(l_module_name,'P_OWNER',P_OWNER);
2881 END IF;
2882 --
2883 if p_exception_name is not NULL then
2884 update WSH_EXCEPTION_DEFINITIONS_TL
2885 set
2886 EXCEPTION_NAME = p_exception_name,
2887 DESCRIPTION = p_description,
2888 LAST_UPDATE_DATE = sysdate,
2889 LAST_UPDATED_BY = decode(p_owner,'SEED',1,0),
2890 LAST_UPDATE_LOGIN = 0,
2891 SOURCE_LANG = userenv('LANG')
2892 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2893 and EXCEPTION_DEFINITION_ID = p_exception_definition_id;
2894 end if;
2895 --
2896 -- Debug Statements
2897 --
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.pop(l_module_name);
2900 END IF;
2901 --
2902 end Translate_Row;
2903
2904
2905 -- -------------------------------------------------------------------
2906 -- Start of comments
2907 -- API name : delete_xc_def_form
2908 -- Type : public
2909 -- Function : delete exception definitions, called by form
2910 -- Version : Initial version 1.0
2911 --
2912 -- End of comments
2913 -- ---------------------------------------------------------------------
2914
2915 procedure delete_xc_def_form (
2916 p_exception_definition_id IN NUMBER)
2917 is
2918 WSH_XC_DEF_NOT_FOUND exception;
2919 --
2920 l_debug_on BOOLEAN;
2921 --
2922 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_XC_DEF_FORM';
2923 --
2924 begin
2925
2926 --
2927 -- Debug Statements
2928 --
2929 --
2930 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2931 --
2932 IF l_debug_on IS NULL
2933 THEN
2934 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2935 END IF;
2936 --
2937 IF l_debug_on THEN
2938 WSH_DEBUG_SV.push(l_module_name);
2939 --
2940 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_DEFINITION_ID',P_EXCEPTION_DEFINITION_ID);
2941 END IF;
2942 --
2943 delete from WSH_EXCEPTION_DEFINITIONS_TL
2944 where EXCEPTION_DEFINITION_ID = p_exception_definition_id ;
2945
2946 if (sql%notfound) then
2947 raise WSH_XC_DEF_NOT_FOUND;
2948 end if;
2949
2950 delete from WSH_EXCEPTION_DEFINITIONS_B
2951 where EXCEPTION_DEFINITION_ID = p_exception_definition_id;
2952
2953 if (sql%notfound) then
2954 raise WSH_XC_DEF_NOT_FOUND;
2955 end if;
2956
2957 --
2958 -- Debug Statements
2959 --
2960 IF l_debug_on THEN
2961 WSH_DEBUG_SV.pop(l_module_name);
2962 END IF;
2963 --
2964 exception
2965
2966 WHEN WSH_XC_DEF_NOT_FOUND THEN
2967 FND_MESSAGE.SET_NAME('WSH', 'WSH_XC_DEF_NOT_FOUND');
2968 APP_EXCEPTION.RAISE_EXCEPTION;
2969
2970
2971 --
2972 -- Debug Statements
2973 --
2974 IF l_debug_on THEN
2975 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_DEF_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2976 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_DEF_NOT_FOUND');
2977 END IF;
2978 --
2979 end delete_xc_def_form ;
2980
2981
2982 -- ---------------------------------------------------------------------
2983 -- procedure: Add_Language
2984 -- description: called by the loader script
2985 --
2986 --
2987 -- ---------------------------------------------------------------------
2988 procedure ADD_LANGUAGE
2989 is
2990 --
2991 l_debug_on BOOLEAN;
2992 --
2993 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_LANGUAGE';
2994 --
2995 begin
2996 --
2997 -- Debug Statements
2998 --
2999 --
3000 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3001 --
3002 IF l_debug_on IS NULL
3003 THEN
3004 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3005 END IF;
3006 --
3007 IF l_debug_on THEN
3008 WSH_DEBUG_SV.push(l_module_name);
3009 END IF;
3010 --
3011 delete from WSH_EXCEPTION_DEFINITIONS_TL T
3012 where not exists
3013 (select to_char(NULL)
3014 from WSH_EXCEPTION_DEFINITIONS_B B
3015 where B.EXCEPTION_DEFINITION_ID = T.EXCEPTION_DEFINITION_ID
3016 );
3017
3018 update WSH_EXCEPTION_DEFINITIONS_TL T set (
3019 EXCEPTION_NAME,
3020 DESCRIPTION
3021 ) = (select
3022 B.EXCEPTION_NAME,
3023 B.DESCRIPTION
3024 from WSH_EXCEPTION_DEFINITIONS_TL B
3025 where B.EXCEPTION_DEFINITION_ID = T.EXCEPTION_DEFINITION_ID
3026 and B.LANGUAGE = T.SOURCE_LANG)
3027 where (
3028 T.EXCEPTION_DEFINITION_ID,
3029 T.LANGUAGE
3030 ) in (select
3031 SUBT.EXCEPTION_DEFINITION_ID,
3032 SUBT.LANGUAGE
3033 from WSH_EXCEPTION_DEFINITIONS_TL SUBB, WSH_EXCEPTION_DEFINITIONS_TL SUBT
3034 where SUBB.EXCEPTION_DEFINITION_ID = SUBT.EXCEPTION_DEFINITION_ID
3035 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3036 and (SUBB.EXCEPTION_NAME <> SUBT.EXCEPTION_NAME
3037 or (SUBB.EXCEPTION_NAME is null and SUBT.EXCEPTION_NAME is not null)
3038 or (SUBB.EXCEPTION_NAME is not null and SUBT.EXCEPTION_NAME is null)
3039 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
3040 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
3041 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
3042 ));
3043
3044 insert into WSH_EXCEPTION_DEFINITIONS_TL (
3045 EXCEPTION_DEFINITION_ID,
3046 EXCEPTION_NAME,
3047 DESCRIPTION,
3048 CREATION_DATE,
3049 CREATED_BY,
3050 LAST_UPDATE_DATE,
3051 LAST_UPDATED_BY,
3052 LAST_UPDATE_LOGIN,
3053 LANGUAGE,
3054 SOURCE_LANG
3055 ) select
3056 B.EXCEPTION_DEFINITION_ID,
3057 B.EXCEPTION_NAME,
3058 B.DESCRIPTION,
3059 B.CREATION_DATE,
3060 B.CREATED_BY,
3061 B.LAST_UPDATE_DATE,
3062 B.LAST_UPDATED_BY,
3063 B.LAST_UPDATE_LOGIN,
3064 L.LANGUAGE_CODE,
3065 B.SOURCE_LANG
3066 from WSH_EXCEPTION_DEFINITIONS_TL B, FND_LANGUAGES L
3067 where L.INSTALLED_FLAG in ('I', 'B')
3068 and B.LANGUAGE = userenv('LANG')
3069 and not exists
3070 (select to_char(NULL)
3071 from WSH_EXCEPTION_DEFINITIONS_TL T
3072 where T.EXCEPTION_DEFINITION_ID = B.EXCEPTION_DEFINITION_ID
3073 and T.LANGUAGE = L.LANGUAGE_CODE);
3074 --
3075 -- Debug Statements
3076 --
3077 IF l_debug_on THEN
3078 WSH_DEBUG_SV.pop(l_module_name);
3079 END IF;
3080 --
3081 end ADD_LANGUAGE;
3082
3083
3084 -- ---------------------------------------------------------------------
3085 -- function: Get_Lookup_Meaning
3086 -- description: called by the view WSH_EXCEPTIONS_V to get meaning
3087 -- for EXCEPTION_SEVERITY and LOGGING_ENTITY
3088 --
3089 -- return: meaning
3090 -- ---------------------------------------------------------------------
3091 function Get_Lookup_Meaning(
3092 p_lookup_code IN VARCHAR2,
3093 p_lookup_type IN VARCHAR2 ) return VARCHAR2
3094 is
3095
3096 CURSOR C1(c_lookup_code VARCHAR2, c_lookup_type VARCHAR2) is
3097 select meaning from fnd_lookup_values_vl
3098 where lookup_code = c_lookup_code and
3099 lookup_type = c_lookup_type;
3100
3101 l_lookup_meaning VARCHAR2(80) := NULL;
3102
3103 --
3104 l_debug_on BOOLEAN;
3105 --
3106 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOOKUP_MEANING';
3107 --
3108 begin
3109 --
3110 -- Debug Statements
3111 --
3112 --
3113 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3114 --
3115 IF l_debug_on IS NULL
3116 THEN
3117 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3118 END IF;
3119 --
3120 IF l_debug_on THEN
3121 WSH_DEBUG_SV.push(l_module_name);
3122 --
3123 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_CODE',P_LOOKUP_CODE);
3124 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_TYPE',P_LOOKUP_TYPE);
3125 END IF;
3126 --
3127 if p_lookup_code is NULL or p_lookup_type is NULL then
3128 --
3129 -- Debug Statements
3130 --
3131 IF l_debug_on THEN
3132 WSH_DEBUG_SV.pop(l_module_name);
3133 END IF;
3134 --
3135 return NULL;
3136 else
3137 open C1(p_lookup_code, p_lookup_type);
3138 fetch C1 into l_lookup_meaning;
3139 if (C1%NOTFOUND) then
3140 close C1;
3141 --
3142 -- Debug Statements
3143 --
3144 IF l_debug_on THEN
3145 WSH_DEBUG_SV.pop(l_module_name);
3146 END IF;
3147 --
3148 return NULL;
3149 else
3150 close C1;
3151 --
3152 -- Debug Statements
3153 --
3154 IF l_debug_on THEN
3155 WSH_DEBUG_SV.pop(l_module_name);
3156 END IF;
3157 --
3158 return l_lookup_meaning;
3159 end if;
3160 end if;
3161 end Get_Lookup_Meaning;
3162
3163
3164 /*-- --------------------------------------------------------------------------
3165 -- Procedure: Purge
3166 -- Description: This procedure will purge the exception data based on the
3167 -- given input criteria
3168 -- --------------------------------------------------------------------------
3169 -- Purpose
3170 -- Purge the WSH_EXCEPIONS table based on input criteria
3171 --
3172 -- Input Parameters
3173 -- p_api_version
3174 -- API version number (current version is 1.0)
3175 -- p_init_msg_list (optional, default FND_API.G_FALSE)
3176 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
3177 -- if set to FND_API.G_TRUE
3178 -- initialize error message list
3179 -- if set to FND_API.G_FALSE - not initialize error
3180 -- message list
3181 -- p_commit (optional, default FND_API.G_FALSE)
3182 -- whether or not to commit the changes to database
3183 --
3184 -- Input parameters for purging the data
3185 -- See the API for more information
3186 -- Input parameters for purging the data
3187 -- See the API for more information
3188 -- p_action (optional):
3189 -- NULL: do nothing
3190 -- 'PURGE': delete closed exceptions
3191 -- 'CLOSED': set all exceptions' status to Closed.
3192 -- C_ACTION_SEMICLOSED: internal use only -- close all but
3193 -- Information Only exceptions.
3194 -- When this action is used, only the entity ID
3195 -- can be passed.
3196 -- Added to resolve bug 4318747 for ITM exceptions
3197 -- Frontported by bug 4370532
3198 --
3199 -- Output Parameters
3200 -- x_return_status
3201 -- if the process succeeds, the value is
3202 -- fnd_api.g_ret_sts_success;
3203 -- if there is an expected error, the value is
3204 -- fnd_api.g_ret_sts_error;
3205 -- if there is an unexpected error, the value is
3206 -- fnd_api.g_ret_sts_unexp_error;
3207 -- x_msg_count
3208 -- if there is one or more errors, the number of error messages
3209 -- in the buffer
3210 -- x_msg_data
3211 -- if there is one and only one error, the error message
3212 -- (See fnd_api package for more details about the above output parameters)
3213 -- x_no_of_records - Number of Records Deleted
3214 --*/
3215
3216 PROCEDURE Purge
3217 (p_api_version IN NUMBER,
3218 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3219 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3220 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
3221 x_return_status OUT NOCOPY VARCHAR2,
3222 x_msg_count OUT NOCOPY NUMBER,
3223 x_msg_data OUT NOCOPY VARCHAR2,
3224 p_request_id IN NUMBER DEFAULT NULL ,
3225 p_exception_name IN VARCHAR2 DEFAULT NULL,
3226 p_logging_entity IN VARCHAR2 DEFAULT NULL,
3227 p_exception_location_id IN NUMBER DEFAULT NULL,
3228 p_logged_at_location_id IN NUMBER DEFAULT NULL ,
3229 p_inventory_org_id IN NUMBER DEFAULT NULL,
3230 p_exception_type IN VARCHAR2 DEFAULT NULL,
3231 p_severity IN VARCHAR2 DEFAULT NULL,
3232 p_status IN VARCHAR2 DEFAULT NULL,
3233 p_arrival_date_from IN DATE DEFAULT NULL,
3234 p_arrival_date_to IN DATE DEFAULT NULL,
3235 p_departure_date_from IN DATE DEFAULT NULL,
3236 p_departure_date_to IN DATE DEFAULT NULL,
3237 p_creation_date_from IN DATE DEFAULT NULL,
3238 p_creation_date_to IN DATE DEFAULT NULL,
3239 p_data_older_no_of_days IN NUMBER DEFAULT NULL,
3240 x_no_of_recs_purged OUT NOCOPY NUMBER,
3241 p_delivery_id IN NUMBER DEFAULT NULL,
3242 p_trip_id IN NUMBER DEFAULT NULL,
3243 p_trip_stop_id IN NUMBER DEFAULT NULL,
3244 p_delivery_detail_id IN NUMBER DEFAULT NULL,
3245 p_delivery_contents IN VARCHAR2 DEFAULT 'Y',
3246 p_action IN VARCHAR2 DEFAULT NULL
3247 )
3248 IS
3249 l_no_of_records NUMBER ;
3250 l_api_name CONSTANT VARCHAR2(30) := 'Purge';
3251 l_api_version number := 1.0;
3252 l_msg_summary VARCHAR2(2000) := NULL;
3253 l_msg_details VARCHAR2(4000) := NULL;
3254 WSH_PURGE_FAILED EXCEPTION;
3255
3256
3257 /*CURSOR c_exceptions IS
3258 select we.rowid
3259 from wsh_exceptions we
3260 where
3261 nvl(we.request_id,-999) = nvl(p_request_id , nvl(we.request_id,-999))
3262 and nvl(we.exception_name,'XXX') = nvl(p_exception_name, nvl(we.exception_name,'XXX'))
3263 and we.logging_entity = nvl(p_logging_entity, we.logging_entity )
3264 and we.logged_at_location_id = nvl(p_logged_at_location_id , we.logged_at_location_id )
3265 and we.exception_location_id = nvl(p_exception_location_id , we.exception_location_id )
3266 and we.severity = nvl(p_severity, we.severity)
3267 -- If Action=PURGE , Choose Recs. with Status=CLOSED
3268 -- If Action=CLOSED , if p_status is NULL, then Choose Recs. with Status<>CLOSED
3269 -- If Action=CLOSED , if p_status is NOT NULL, then Choose Recs. with p_status<>CLOSED and p_status = Status
3270 and ( ( p_action = 'PURGE' AND we.status = 'CLOSED' ) OR
3271 ( p_action = 'CLOSED' AND ( ( p_status IS NULL and we.status <> 'CLOSED' ) OR
3272 ( p_status IS NOT NULL and p_status <> 'CLOSED' and we.status = p_status )
3273 )
3274 )
3275 )
3276 and to_char(we.creation_date,'YYYY/MM/DD') BETWEEN
3277 nvl(TO_CHAR(p_creation_date_from,'YYYY/MM/DD') , to_char(we.creation_date,'YYYY/MM/DD') )
3278 and nvl(TO_CHAR(p_creation_date_to,'YYYY/MM/DD') , to_char(we.creation_date,'YYYY/MM/DD') )
3279 and to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') BETWEEN
3280 nvl(TO_CHAR(nvl(p_arrival_date_from,nvl(we.arrival_date,sysdate)),'YYYY/MM/DD') ,
3281 to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') )
3282 and nvl(TO_CHAR(nvl(p_arrival_date_to,nvl(we.arrival_date,sysdate)),'YYYY/MM/DD') ,
3283 to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') )
3284 and to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') BETWEEN
3285 nvl(TO_CHAR(nvl(p_departure_date_from,nvl(we.departure_date,sysdate)),'YYYY/MM/DD') ,
3286 to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') )
3287 and nvl(TO_CHAR(nvl(p_departure_date_to,nvl(we.departure_date,sysdate)),'YYYY/MM/DD') ,
3288 to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') )
3289 and (sysdate - we.creation_date) > nvl(p_data_older_no_of_days,(sysdate - we.creation_date - 1))
3290 and ( (we.delivery_detail_id in (select distinct a.delivery_detail_id
3291 from wsh_delivery_assignments_v a, wsh_exceptions b
3292 where a.delivery_detail_id=b.delivery_detail_id
3293 and a.delivery_id=p_delivery_id)
3294 AND p_delivery_id is not null
3295 AND p_delivery_contents = 'Y')
3296 /*OR (we.container_name in (select distinct a.container_name --LPN Synch Up..samanna
3297 from wsh_delivery_details a, wsh_delivery_assignments_v b
3298 where a.delivery_detail_id = b.delivery_detail_id
3299 and a.container_flag = 'Y'
3300 and b.delivery_id=p_delivery_id)
3301 AND p_delivery_id is not null
3302 AND p_delivery_contents = 'Y')
3303 OR nvl(we.delivery_id,-999) = nvl(p_delivery_id, nvl(we.delivery_id,-999))
3304 )
3305 and nvl(we.trip_id,-999) = nvl(p_trip_id, nvl(we.trip_id,-999))
3306 and nvl(we.trip_stop_id,-999) = nvl(p_trip_stop_id, nvl(we.trip_stop_id,-999))
3307 and nvl(we.delivery_detail_id,-999) = nvl(p_delivery_detail_id, nvl(we.delivery_detail_id,-999))
3308 ;*/
3309
3310
3311 TYPE TableOfROWID is TABLE of ROWID INDEX BY BINARY_INTEGER;
3312
3313 RowIdList TableOfROWID;
3314
3315
3316 Exceptions_SQL VARCHAR2(32000) := NULL;
3317 Temp_SQL VARCHAR2(300) := NULL;
3318 c_exceptions WSH_UTIL_CORE.RefCurType;
3319 bind_col_tab WSH_UTIL_CORE.tbl_varchar;
3320
3321 --Bug 5943326 Variables for bulk purge
3322 BulkBatchSize Number := 1000;
3323
3324 c_cfetch number;
3325 c_pfetch number;
3326 l_count number := 0;
3327 l_use_dynamic varchar2(1); -- Bug 3582688
3328 --
3329 l_debug_on BOOLEAN;
3330 --
3331 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE';
3332 --
3333 begin
3334 -- Standard Start of API savepoint
3335 --
3336 -- Debug Statements
3337 --
3338 --
3339 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3340 --
3341 IF l_debug_on IS NULL
3342 THEN
3343 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3344 END IF;
3345 --
3346 IF l_debug_on THEN
3347 WSH_DEBUG_SV.push(l_module_name);
3348 --
3349 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3350 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3351 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3352 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3353 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
3354 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
3355 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY',P_LOGGING_ENTITY);
3356 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_LOCATION_ID',P_EXCEPTION_LOCATION_ID);
3357 WSH_DEBUG_SV.log(l_module_name,'P_LOGGED_AT_LOCATION_ID',P_LOGGED_AT_LOCATION_ID);
3358 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ORG_ID',P_INVENTORY_ORG_ID);
3359 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_TYPE',P_EXCEPTION_TYPE);
3360 WSH_DEBUG_SV.log(l_module_name,'P_SEVERITY',P_SEVERITY);
3361 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
3362 WSH_DEBUG_SV.log(l_module_name,'P_ARRIVAL_DATE_FROM',P_ARRIVAL_DATE_FROM);
3363 WSH_DEBUG_SV.log(l_module_name,'P_ARRIVAL_DATE_TO',P_ARRIVAL_DATE_TO);
3364 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_FROM',P_DEPARTURE_DATE_FROM);
3365 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_TO',P_DEPARTURE_DATE_TO);
3366 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE_FROM',P_CREATION_DATE_FROM);
3367 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE_TO',P_CREATION_DATE_TO);
3368 WSH_DEBUG_SV.log(l_module_name,'P_DATA_OLDER_NO_OF_DAYS',P_DATA_OLDER_NO_OF_DAYS);
3369 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
3370 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
3371 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
3372 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
3373 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
3374 END IF;
3375 --
3376 SAVEPOINT WSH_XC_UTIL;
3377 -- Standard call to check for call compatibility.
3378 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3379 p_api_version ,
3380 l_api_name ,
3381 G_PKG_NAME )
3382 THEN
3383 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
3384 FND_MSG_PUB.ADD;
3385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3386 END IF;
3387 -- Initialize message list if p_init_msg_list is set to TRUE.
3388 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3389 FND_MSG_PUB.initialize;
3390 END IF;
3391 -- Initialize API return status to success
3392 x_return_status := FND_API.G_RET_STS_SUCCESS;
3393
3394 -- Bug#3582688: Purge API is also called for closing exceptions (during ship confirm)
3395 -- for a trip or trip stop or delivery or delivery detail. We should be using static
3396 -- SQLs in this case since dynamic sql has performance issue if executed multiple
3397 -- times.
3398
3399 l_use_dynamic := 'Y';
3400 IF p_action IN ('CLOSED', C_ACTION_SEMICLOSED) THEN --{ case where purge is called only with entity_id
3401 IF (p_request_id is NULL and
3402 p_exception_name is NULL and
3403 p_logging_entity is NULL and
3404 p_exception_location_id is NULL and
3405 p_logged_at_location_id is NULL and
3406 p_inventory_org_id is NULL and
3407 p_exception_type is NULL and
3408 p_severity is NULL and
3409 p_status is NULL and
3410 p_arrival_date_from is NULL and
3411 p_arrival_date_to is NULL and
3412 p_departure_date_from is NULL and
3413 p_departure_date_to is NULL and
3414 p_creation_date_from is NULL and
3415 p_creation_date_to is NULL and
3416 p_data_older_no_of_days is NULL) THEN --{ check for common NULL paramaters
3417
3418 IF p_trip_id IS NOT NULL and
3419 (p_delivery_id is NULL and
3420 p_trip_stop_id is NULL and
3421 p_delivery_detail_id is NULL) THEN -- { Close for ONLY entity passed
3422
3423 l_use_dynamic := 'N';
3424 -- FP bug 4370532: this is the only context where
3425 -- action C_ACTION_SEMICLOSED can be used.
3426 IF (p_action = C_ACTION_SEMICLOSED) THEN
3427 UPDATE WSH_EXCEPTIONS
3428 SET status= 'CLOSED',
3429 last_update_date = sysdate,
3430 last_updated_by = FND_GLOBAL.USER_ID,
3431 last_update_login = FND_GLOBAL.USER_ID
3432 WHERE trip_id = p_trip_id
3433 AND status <> 'CLOSED'
3434 AND severity <> 'INFO';
3435 ELSE
3436 UPDATE WSH_EXCEPTIONS
3437 SET status= 'CLOSED',
3438 last_update_date = sysdate,
3439 last_updated_by = FND_GLOBAL.USER_ID,
3440 last_update_login = FND_GLOBAL.USER_ID
3441 WHERE trip_id = p_trip_id
3442 AND status <> 'CLOSED';
3443 END IF;
3444 l_count := SQL%rowcount;
3445
3446 ELSIF p_trip_stop_id IS NOT NULL and
3447 (p_delivery_id is NULL and
3448 p_trip_id is NULL and
3449 p_delivery_detail_id is NULL) THEN
3450
3451 l_use_dynamic := 'N';
3452 -- FP bug 4370532: this is the only context where
3453 -- action C_ACTION_SEMICLOSED can be used.
3454 IF (p_action = C_ACTION_SEMICLOSED) THEN
3455 UPDATE WSH_EXCEPTIONS
3456 SET status='CLOSED',
3457 last_update_date = sysdate,
3458 last_updated_by = FND_GLOBAL.USER_ID,
3459 last_update_login = FND_GLOBAL.USER_ID
3460 WHERE trip_stop_id = p_trip_stop_id
3461 AND status <> 'CLOSED'
3462 AND severity <> 'INFO';
3463 ELSE
3464 UPDATE WSH_EXCEPTIONS
3465 SET status='CLOSED',
3466 last_update_date = sysdate,
3467 last_updated_by = FND_GLOBAL.USER_ID,
3468 last_update_login = FND_GLOBAL.USER_ID
3469 WHERE trip_stop_id = p_trip_stop_id
3470 AND status <> 'CLOSED';
3471
3472 END IF;
3473 l_count := SQL%rowcount;
3474
3475 ELSIF p_delivery_id IS NOT NULL and
3476 (p_trip_id is NULL and
3477 p_trip_stop_id is NULL and
3478 p_delivery_detail_id is NULL) THEN
3479
3480 l_use_dynamic := 'N';
3481 IF p_delivery_contents = 'Y' THEN
3482 -- FP bug 4370532: this is the only context where
3483 -- action C_ACTION_SEMICLOSED can be used.
3484 IF (p_action = C_ACTION_SEMICLOSED) THEN
3485 -- performance bug 5257207: SC-11: split UPDATE to reduce
3486 -- the total number of queries on WSH_EXCEPTIONS.
3487 --
3488 -- This code is part of the ship confirm flow.
3489
3490 UPDATE WSH_EXCEPTIONS
3491 SET status = C_STATUS_CLOSED,
3492 last_update_date = sysdate,
3493 last_updated_by = FND_GLOBAL.USER_ID,
3494 last_update_login = FND_GLOBAL.USER_ID
3495 WHERE delivery_detail_id IN
3496 (SELECT wda.delivery_detail_id
3497 FROM wsh_delivery_assignments wda
3498 WHERE wda.delivery_id = p_delivery_id)
3499 AND status <> C_STATUS_CLOSED
3500 AND severity <> C_SEVERITY_INFO;
3501
3502 l_count := SQL%rowcount;
3503
3504 UPDATE WSH_EXCEPTIONS
3505 SET status = C_STATUS_CLOSED,
3506 last_update_date = sysdate,
3507 last_updated_by = FND_GLOBAL.USER_ID,
3508 last_update_login = FND_GLOBAL.USER_ID
3509 WHERE delivery_id = p_delivery_id
3510 AND status <> C_STATUS_CLOSED
3511 AND severity <> C_SEVERITY_INFO;
3512
3513 l_count := l_count + SQL%rowcount;
3514
3515 ELSE
3516 -- UPDATES are tuned the same way as above.
3517 UPDATE WSH_EXCEPTIONS
3518 SET status = p_action,
3519 last_update_date = sysdate,
3520 last_updated_by = FND_GLOBAL.USER_ID,
3521 last_update_login = FND_GLOBAL.USER_ID
3522 WHERE delivery_detail_id IN
3523 (SELECT wda.delivery_detail_id
3524 FROM wsh_delivery_assignments wda
3525 WHERE wda.delivery_id = p_delivery_id)
3526 AND status <> C_STATUS_CLOSED;
3527
3528 l_count := SQL%rowcount;
3529
3530 UPDATE WSH_EXCEPTIONS
3531 SET status = p_action,
3532 last_update_date = sysdate,
3533 last_updated_by = FND_GLOBAL.USER_ID,
3534 last_update_login = FND_GLOBAL.USER_ID
3535 WHERE delivery_id = p_delivery_id
3536 AND status <> C_STATUS_CLOSED;
3537
3538 l_count := l_count + SQL%rowcount;
3539
3540 END IF;
3541 ELSE
3542 -- p_delivery_contents is 'N' here.
3543
3544 -- bug 4318747: this is the only context where
3545 -- action C_ACTION_SEMICLOSED can be used.
3546 IF (p_action = C_ACTION_SEMICLOSED) THEN
3547 UPDATE WSH_EXCEPTIONS
3548 SET status='CLOSED',
3549 last_update_date = sysdate,
3550 last_updated_by = FND_GLOBAL.USER_ID,
3551 last_update_login = FND_GLOBAL.USER_ID
3552 WHERE delivery_id = p_delivery_id
3553 AND status <> 'CLOSED'
3554 AND severity <> 'INFO'
3555 --AND exception_name <> 'WSH_OTM_SHIPMENT_ERROR'; -- OTM R12 glog project;
3556 AND exception_name <> C_OTM_EXC_NAME; -- OTM R12 glog project;
3557 ELSE
3558 UPDATE WSH_EXCEPTIONS
3559 SET status='CLOSED',
3560 last_update_date = sysdate,
3561 last_updated_by = FND_GLOBAL.USER_ID,
3562 last_update_login = FND_GLOBAL.USER_ID
3563 WHERE delivery_id = p_delivery_id
3564 AND status <> 'CLOSED'
3565 --AND exception_name <> 'WSH_OTM_SHIPMENT_ERROR'; -- OTM R12 glog project;
3566 AND exception_name <> C_OTM_EXC_NAME; -- OTM R12 glog project;
3567 END IF;
3568 l_count := SQL%rowcount;
3569 END IF;
3570
3571 ELSIF p_delivery_detail_id IS NOT NULL and
3572 (p_trip_id is NULL and
3573 p_trip_stop_id is NULL and
3574 p_delivery_id is NULL) THEN
3575
3576 l_use_dynamic := 'N';
3577 UPDATE WSH_EXCEPTIONS
3578 SET status=p_action ,
3579 last_update_date = sysdate,
3580 last_updated_by = FND_GLOBAL.USER_ID,
3581 last_update_login = FND_GLOBAL.USER_ID
3582 WHERE delivery_detail_id = p_delivery_detail_id
3583 AND status <> 'CLOSED';
3584 l_count := SQL%rowcount;
3585
3586 END IF; -- } Close when ONLY the entity is passed
3587 END IF; ---} check for common NULL parameters
3588 END IF; --} p_action = CLOSED, C_ACTION_SEMICLOSED
3589
3590 IF l_use_dynamic = 'Y' THEN -- Dynamic cusrsor required
3591 --{
3592 -- Bug#3200314: Constructing the Dynamic Cursor.
3593 Exceptions_SQL := Exceptions_SQL || 'SELECT WE.ROWID';
3594 Exceptions_SQL := Exceptions_SQL || ' FROM WSH_EXCEPTIONS WE';
3595 Exceptions_SQL := Exceptions_SQL || ' WHERE';
3596 IF p_request_id IS NOT NULL THEN
3597 Exceptions_SQL := Exceptions_SQL || ' WE.REQUEST_ID = :p_request_id AND';
3598 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_request_id);
3599 END IF;
3600 IF p_exception_name IS NOT NULL THEN
3601 Exceptions_SQL := Exceptions_SQL || ' WE.EXCEPTION_NAME = :p_exception_name AND';
3602 bind_col_tab(bind_col_tab.COUNT+1) := p_exception_name;
3603 END IF;
3604 IF p_logging_entity IS NOT NULL THEN
3605 Exceptions_SQL := Exceptions_SQL || ' WE.LOGGING_ENTITY = :p_logging_entity AND';
3606 bind_col_tab(bind_col_tab.COUNT+1) := p_logging_entity;
3607 END IF;
3608 IF p_logged_at_location_id IS NOT NULL THEN
3609 Exceptions_SQL := Exceptions_SQL || ' WE.LOGGED_AT_LOCATION_ID = :p_logged_at_location_id AND';
3610 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_logged_at_location_id);
3611 END IF;
3612 IF p_exception_location_id IS NOT NULL THEN
3613 Exceptions_SQL := Exceptions_SQL || ' WE.EXCEPTION_LOCATION_ID = :p_exception_location_id AND';
3614 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_exception_location_id);
3615 END IF;
3616 IF p_severity IS NOT NULL THEN
3617 Exceptions_SQL := Exceptions_SQL || ' WE.SEVERITY = :p_severity AND';
3618 bind_col_tab(bind_col_tab.COUNT+1) := p_severity;
3619 END IF;
3620
3621 -- If Action=PURGE , Choose Recs. with Status=CLOSED
3622 -- If Action=CLOSED , if p_status is NULL, then Choose Recs. with Status<>CLOSED
3623 -- If Action=CLOSED , if p_status is NOT NULL, then Choose Recs. with p_status<>CLOSED and p_status = Status
3624
3625 IF p_action = 'PURGE' THEN
3626 Exceptions_SQL := Exceptions_SQL || ' we.status = ''CLOSED'' AND';
3627 ELSIF p_action = 'CLOSED' THEN
3628 IF p_status IS NULL THEN
3629 Exceptions_SQL := Exceptions_SQL || ' we.status <> ''CLOSED'' AND';
3630 ELSIF p_status <> 'CLOSED' THEN
3631 Exceptions_SQL := Exceptions_SQL || ' we.status = :p_status AND';
3632 bind_col_tab(bind_col_tab.COUNT+1) := p_status;
3633 END IF;
3634 END IF;
3635 --Bug 7153170:Modified logic so that the index on the column creation_date is picked up
3636 IF p_creation_date_from IS NOT NULL OR p_creation_date_to IS NOT NULL THEN
3637 IF p_creation_date_from IS NOT NULL THEN
3638 Exceptions_SQL := Exceptions_SQL || ' we.creation_date BETWEEN';
3639 Exceptions_SQL := Exceptions_SQL || ' fnd_date.canonical_to_date(:p_creation_date_from) AND ';
3640 IF p_creation_date_to IS NOT NULL THEN
3641 Exceptions_SQL := Exceptions_SQL || ' fnd_date.canonical_to_date(:p_creation_date_to) AND ';
3642 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_creation_date_from, 'YYYY/MM/DD');
3643 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_creation_date_to, 'YYYY/MM/DD');
3644 ELSE
3645 Exceptions_SQL := Exceptions_SQL || ' sysdate AND ';
3646 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_creation_date_from, 'YYYY/MM/DD');
3647 END IF;
3648 ELSE
3649 Exceptions_SQL := Exceptions_SQL || ' we.creation_date < fnd_date.canonical_to_date(:p_creation_date_to) AND ';
3650 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_creation_date_to, 'YYYY/MM/DD');
3651 END IF;
3652 --End of modifications for bug 7153170
3653 END IF;
3654
3655 IF p_arrival_date_from IS NOT NULL OR p_arrival_date_to IS NOT NULL THEN
3656 Exceptions_SQL := Exceptions_SQL || ' to_char(nvl(we.arrival_date,SYSDATE),''YYYY/MM/DD'') BETWEEN';
3657 Exceptions_SQL := Exceptions_SQL || ' NVL(:p_arrival_date_from, to_char(nvl(we.arrival_date,SYSDATE),''YYYY/MM/DD'') )';
3658 Exceptions_SQL := Exceptions_SQL || ' AND NVL(:p_arrival_date_to, to_char(nvl(we.arrival_date,SYSDATE),''YYYY/MM/DD'') ) AND';
3659 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_arrival_date_from,'YYYY/MM/DD');
3660 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_arrival_date_to,'YYYY/MM/DD');
3661 END IF;
3662
3663 IF p_departure_date_from IS NOT NULL OR p_departure_date_to IS NOT NULL THEN
3664 Exceptions_SQL := Exceptions_SQL || ' to_char(nvl(we.departure_date,SYSDATE),''YYYY/MM/DD'') BETWEEN';
3665 Exceptions_SQL := Exceptions_SQL || ' NVL(:p_departure_date_from, to_char(nvl(we.departure_date,SYSDATE),''YYYY/MM/DD'') )';
3666 Exceptions_SQL := Exceptions_SQL || ' AND NVL(:p_departure_date_to, to_char(nvl(we.departure_date,SYSDATE),''YYYY/MM/DD'') ) AND';
3667 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_departure_date_from,'YYYY/MM/DD');
3668 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_departure_date_to,'YYYY/MM/DD');
3669 END IF;
3670
3671 IF p_data_older_no_of_days IS NOT NULL THEN
3672 Exceptions_SQL := Exceptions_SQL || ' (SYSDATE - :p_data_older_no_of_days ) > we.creation_date AND';
3673 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_data_older_no_of_days);
3674 END IF;
3675
3676 IF p_delivery_id IS NOT NULL THEN
3677 IF p_delivery_contents = 'Y' THEN
3678 Temp_SQL := ' SELECT ROWID';
3679 Temp_SQL := Temp_SQL || ' FROM WSH_EXCEPTIONS';
3680 Temp_SQL := Temp_SQL || ' WHERE';
3681 Exceptions_SQL := Exceptions_SQL || ' WE.ROWID IN (';
3682 Exceptions_SQL := Exceptions_SQL || Temp_SQL;
3683 Exceptions_SQL := Exceptions_SQL || ' DELIVERY_DETAIL_ID IN';
3684 Exceptions_SQL := Exceptions_SQL || ' (SELECT DISTINCT a.delivery_detail_id';
3685 Exceptions_SQL := Exceptions_SQL || ' FROM wsh_delivery_assignments_v a, wsh_exceptions b';
3686 Exceptions_SQL := Exceptions_SQL || ' WHERE a.delivery_detail_id=b.delivery_detail_id';
3687 Exceptions_SQL := Exceptions_SQL || ' AND a.delivery_id= :p_delivery_id)';
3688 Exceptions_SQL := Exceptions_SQL || ' UNION ALL';
3689 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_delivery_id);
3690 /*Exceptions_SQL := Exceptions_SQL || Temp_SQL;
3691 Exceptions_SQL := Exceptions_SQL || ' CONTAINER_NAME IN';
3692 Exceptions_SQL := Exceptions_SQL || ' (SELECT DISTINCT a.container_name';
3693 Exceptions_SQL := Exceptions_SQL || ' FROM wsh_delivery_details a, wsh_delivery_assignments_v b';
3694 Exceptions_SQL := Exceptions_SQL || ' WHERE a.delivery_detail_id=b.delivery_detail_id';
3695 Exceptions_SQL := Exceptions_SQL || ' AND a.container_flag = ''Y''';
3696 Exceptions_SQL := Exceptions_SQL || ' AND b.delivery_id= :p_delivery_id)';
3697 Exceptions_SQL := Exceptions_SQL || ' UNION ALL';
3698 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_delivery_id);*/
3699 Exceptions_SQL := Exceptions_SQL || Temp_SQL;
3700 Exceptions_SQL := Exceptions_SQL || ' delivery_id = :p_delivery_id ) AND';
3701 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_delivery_id);
3702 ELSE
3703 Exceptions_SQL := Exceptions_SQL || ' WE.delivery_id = :p_delivery_id AND';
3704 -- OTM R12 glog project
3705 -- Purge only looks for closed exceptions
3706 -- so only for close action, filter out this exception name
3707 IF p_action = 'CLOSED' THEN
3708 --Exceptions_SQL := Exceptions_SQL ||' WE.exception_name <> ''WSH_OTM_SHIPMENT_ERROR'' AND';
3709 Exceptions_SQL := Exceptions_SQL ||' WE.exception_name <> '''|| C_OTM_EXC_NAME ||''' AND';
3710 END IF;
3711 -- OTM R12 end of glog project
3712 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_delivery_id);
3713 END IF;
3714 END IF;
3715
3716 IF p_trip_id IS NOT NULL THEN
3717 Exceptions_SQL := Exceptions_SQL || ' WE.TRIP_ID = :p_trip_id AND';
3718 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_trip_id);
3719 END IF;
3720 IF p_trip_stop_id IS NOT NULL THEN
3721 Exceptions_SQL := Exceptions_SQL || ' WE.TRIP_STOP_ID = :p_trip_stop_id AND';
3722 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_trip_stop_id);
3723 END IF;
3724 IF p_delivery_detail_id IS NOT NULL THEN
3725 Exceptions_SQL := Exceptions_SQL || ' WE.DELIVERY_DETAIL_ID = :p_delivery_detail_id AND';
3726 bind_col_tab(bind_col_tab.COUNT+1) := TO_CHAR(p_delivery_detail_id);
3727 END IF;
3728
3729 -- Bug#3200314
3730 -- Process the records based on the input criteria.
3731 Exceptions_SQL := Exceptions_SQL ||' 1=1';
3732 WSH_UTIL_CORE.OpenDynamicCursor(c_exceptions, Exceptions_SQL, bind_col_tab);
3733
3734 -- Bug 3576661 : TST11510.10: ERROR IN SHIP CONFIRM DELIVERY CANNOT BE SHIP CONFIRMED
3735 -- Dynamic SQL cannot be used with Bulk Features in pre 9i environments
3736 -- Either of these can be used, so using Variable Cursor as of now but not
3737 -- using Bulk Collect feature while fetching the record
3738
3739 -- bug 5943326 added c_cpfetch and c_pfetch
3740 c_cfetch := 0;
3741 c_pfetch := 0;
3742 l_count := 0;
3743 IF p_action IN ('PURGE','CLOSED') THEN --{ Closed and purge
3744 -- bug 5943326 using BULK PURGE
3745 LOOP
3746 /* FETCH c_exceptions INTO RowIdList(l_count+1) ;
3747 EXIT WHEN (c_exceptions%NOTFOUND);
3748 l_count := l_count+1;
3749 END LOOP;
3750 CLOSE c_exceptions;*/
3751 FETCH c_exceptions BULK COLLECT
3752 INTO RowIdList LIMIT BulkBatchSize;
3753 c_cfetch := c_exceptions%rowcount - c_pfetch;
3754 EXIT WHEN (c_cfetch=0);
3755 IF p_action='PURGE' THEN
3756 FORALL l_counter IN 1..c_cfetch
3757 DELETE from WSH_EXCEPTIONS where rowid = RowIdList(l_counter);
3758 ELSE -- 'CLOSED'
3759 FORALL l_counter IN 1..c_cfetch
3760 UPDATE WSH_EXCEPTIONS
3761 SET status=p_action ,
3762 last_update_date = sysdate,
3763 last_updated_by = FND_GLOBAL.USER_ID,
3764 last_update_login = FND_GLOBAL.USER_ID
3765 WHERE rowid = RowIdList(l_counter);
3766 END IF;
3767
3768 --IF (l_count > 0) THEN
3769 IF SQL%NOTFOUND THEN
3770 FND_MESSAGE.SET_NAME('WSH', 'WSH_PURGE_FAILED');
3771 FND_MSG_PUB.ADD;
3772 RAISE WSH_PURGE_FAILED;
3773 END IF;
3774
3775 --END IF;
3776
3777 IF FND_API.To_Boolean( p_commit ) THEN
3778 COMMIT WORK;
3779 END IF;
3780 c_pfetch := c_exceptions%rowcount;
3781 l_count := c_pfetch;
3782 END LOOP;
3783 CLOSE c_exceptions;
3784 END IF; --} End of Closed and Purge
3785 END IF; --} Dynamic cursor required
3786 IF l_count > 0 THEN
3787 IF FND_API.To_Boolean( p_commit ) THEN
3788 COMMIT WORK;
3789 END IF;
3790 END IF;
3791 x_no_of_recs_purged := l_count;
3792
3793 --
3794 -- Debug Statements
3795 --
3796 IF l_debug_on THEN
3797 WSH_DEBUG_SV.log(l_module_name,'Count',x_no_of_recs_purged);
3798 WSH_DEBUG_SV.pop(l_module_name);
3799 END IF;
3800 --
3801 EXCEPTION
3802 WHEN WSH_PURGE_FAILED THEN
3803 ROLLBACK TO WSH_XC_UTIL;
3804 x_return_status := FND_API.G_RET_STS_ERROR;
3805 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
3806 if x_msg_count > 1 then
3807 x_msg_data := l_msg_summary || l_msg_details;
3808 else
3809 x_msg_data := l_msg_summary;
3810 end if;
3811 x_msg_data := nvl(x_msg_data,sqlerrm);
3812 --
3813 -- Debug Statements
3814 --
3815 IF l_debug_on THEN
3816 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_PURGE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3817 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_PURGE_FAILED');
3818 END IF;
3819 --
3820 WHEN FND_API.G_EXC_ERROR THEN
3821 ROLLBACK TO WSH_XC_UTIL;
3822 x_return_status := FND_API.G_RET_STS_ERROR;
3823 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
3824 if x_msg_count > 1 then
3825 x_msg_data := l_msg_summary || l_msg_details;
3826 else
3827 x_msg_data := l_msg_summary;
3828 end if;
3829 x_msg_data := nvl(x_msg_data,sqlerrm);
3830 --
3831 -- Debug Statements
3832 --
3833 IF l_debug_on THEN
3834 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3836 END IF;
3837 --
3838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3839 ROLLBACK TO WSH_XC_UTIL;
3840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3841 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
3842 if x_msg_count > 1 then
3843 x_msg_data := l_msg_summary || l_msg_details;
3844 else
3845 x_msg_data := l_msg_summary;
3846 end if;
3847 x_msg_data := nvl(x_msg_data,sqlerrm);
3848 --
3849 -- Debug Statements
3850 --
3851 IF l_debug_on THEN
3852 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3854 END IF;
3855 --
3856 WHEN OTHERS THEN
3857 ROLLBACK TO WSH_XC_UTIL;
3858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3859 WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
3860 if x_msg_count > 1 then
3861 x_msg_data := l_msg_summary || l_msg_details;
3862 else
3863 x_msg_data := l_msg_summary;
3864 end if;
3865 x_msg_data := nvl(x_msg_data,sqlerrm);
3866 --
3867 -- Debug Statements
3868 --
3869 IF l_debug_on THEN
3870 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3871 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3872 END IF;
3873 --
3874 end;
3875
3876
3877
3878 -- -------------------------------------------------------------------------------
3879 -- Start of comments
3880 -- API name : Check_exceptions
3881 -- Type : Public
3882 -- Function : This procedure takes input as Entity Name and Entity Id
3883 -- and finds the maximum severity exception logged against it.
3884 -- Only Error and Warning Exceptions are considered, Information Only
3885 -- are not considered.
3886 -- If p_consider_content is set to 'Y', then the API also looks
3887 -- at the contents of the Entity and checks for the maximum severity
3888 -- against each child entity. This is drilled to lowest child entity.
3889 -- The API returns a PL/SQL table of records with Entity Name, Entity ID
3890 -- Exception Behavior. The table is populated with the Top Most entity
3891 -- followed by its child entities (if exceptions exist against them) in
3892 -- a hierarchial tree structure.
3893 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
3894 -- TRIP, STOP
3895 -- End of comments
3896 -- --------------------------------------------------------------------------------
3897
3898 PROCEDURE Check_Exceptions (
3899 -- Standard parameters
3900 p_api_version IN NUMBER,
3901 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3902 x_return_status OUT NOCOPY VARCHAR2,
3903 x_msg_count OUT NOCOPY NUMBER,
3904 x_msg_data OUT NOCOPY VARCHAR2,
3905
3906 -- program specific parameters
3907 p_logging_entity_id IN NUMBER,
3908 p_logging_entity_name IN VARCHAR2,
3909 p_consider_content IN VARCHAR2,
3910
3911 -- program specific out parameters
3912 x_exceptions_tab IN OUT NOCOPY XC_TAB_TYPE,
3913 p_caller IN VARCHAR2
3914 ) IS
3915
3916 CURSOR Get_Trip_Exceptions (v_trip_id NUMBER) IS
3917 SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
3918 FROM wsh_exceptions
3919 WHERE trip_id = v_trip_id
3920 AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
3921 AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
3922 ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
3923
3924 CURSOR Get_Stop_Exceptions (v_stop_id NUMBER) IS
3925 SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
3926 FROM wsh_exceptions
3927 WHERE trip_stop_id = v_stop_id
3928 AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
3929 AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
3930 ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
3931
3932 CURSOR Get_Delivery_Exceptions (v_delivery_id NUMBER) IS
3933 SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
3934 FROM wsh_exceptions
3935 WHERE delivery_id = v_delivery_id
3936 AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
3937 AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
3938 ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
3939
3940 CURSOR Get_Detail_Exceptions (v_detail_id NUMBER) IS
3941 SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
3942 FROM wsh_exceptions
3943 WHERE delivery_detail_id = v_detail_id
3944 AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
3945 AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
3946 ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
3947
3948 CURSOR Get_Details_Delivery(v_delivery_id NUMBER) IS
3949 SELECT wdd.delivery_detail_id, wdd.container_flag
3950 FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
3951 WHERE wda.delivery_id = v_delivery_id
3952 AND wdd.delivery_detail_id = wda.delivery_detail_id
3953 ORDER BY wdd.container_flag;
3954
3955 -- performance bug 5257207: SC-1: added ALL since the selections
3956 -- are mutually exclusive (a delivery cannot be both picked up
3957 -- and dropped off at the same stop).
3958 -- This is the same cursor as in close_exceptions.
3959 CURSOR Get_Deliveries_Stop(v_stop_id NUMBER) IS
3960 -- pick up deliveries
3961 SELECT dg.delivery_id
3962 FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
3963 WHERE dg.delivery_id = dl.delivery_id
3964 AND st.stop_location_id = dl.initial_pickup_location_id
3965 AND st.stop_id = dg.pick_up_stop_id
3966 AND st.stop_id = v_stop_id
3967 UNION ALL
3968 -- drop off deliveries
3969 SELECT dg.delivery_id
3970 FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
3971 WHERE dg.delivery_id = dl.delivery_id
3972 AND st.stop_location_id = dl.ultimate_dropoff_location_id
3973 AND st.stop_id = dg.drop_off_stop_id
3974 AND st.stop_id = v_stop_id;
3975
3976 CURSOR Get_Stops_Trip(v_trip_id NUMBER) IS
3977 SELECT stop_id
3978 FROM wsh_trip_stops
3979 WHERE trip_id = v_trip_id
3980 ORDER BY stop_sequence_number ASC ;
3981
3982 CURSOR Get_Contents_Container (v_container_id NUMBER) IS
3983 SELECT delivery_detail_id
3984 FROM wsh_delivery_assignments_v
3985 START WITH parent_delivery_detail_id = v_container_id
3986 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
3987
3988 CURSOR Get_Container_Flag (v_delivery_detail_id NUMBER) IS
3989 SELECT container_flag
3990 FROM wsh_delivery_details
3991 WHERE delivery_detail_id = v_delivery_detail_id;
3992
3993 CURSOR c_dummy_stop (v_stop_id NUMBER) IS
3994 SELECT stop_id
3995 FROM wsh_trip_stops wts
3996 WHERE wts.physical_stop_id IS NOT NULL
3997 AND wts.physical_location_id IS NOT NULL
3998 AND wts.physical_stop_id=v_stop_id
3999 AND wts.trip_id =(SELECT trip_id
4000 FROM wsh_trip_stops wts1
4001 WHERE wts1.stop_id=v_stop_id);
4002
4003 l_severity VARCHAR2(30);
4004
4005 -- Standard call to check for call compatibility
4006 l_api_version CONSTANT NUMBER := 1.0;
4007 l_api_name CONSTANT VARCHAR2(30):= 'Check_Exceptions';
4008
4009 l_count NUMBER;
4010 l_delivery_exists BOOLEAN;
4011 l_container_flag VARCHAR2(1);
4012
4013 l_return_status VARCHAR2(1);
4014 l_msg_count NUMBER;
4015 l_msg_data VARCHAR2(2000);
4016
4017 c_trip CONSTANT VARCHAR2(30):= 'TRIP';
4018 l_exceptions_tab XC_TAB_TYPE;
4019
4020 WSH_INVALID_LOGGING_ENTITY EXCEPTION;
4021
4022 WSH_CHECK_EXCEPTIONS_FAILED EXCEPTION;
4023
4024 --
4025 l_debug_on BOOLEAN;
4026 --
4027 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'|| 'CHECK_EXCEPTIONS';
4028 --
4029 BEGIN
4030
4031 --
4032 --
4033 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4034 --
4035 IF l_debug_on IS NULL
4036 THEN
4037 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4038 END IF;
4039 --
4040 --
4041 -- Debug Statements
4042 --
4043 IF l_debug_on THEN
4044 WSH_DEBUG_SV.push(l_module_name);
4045 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
4046 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
4047 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_NAME',P_LOGGING_ENTITY_NAME);
4048 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_ID',P_LOGGING_ENTITY_ID);
4049 WSH_DEBUG_SV.log(l_module_name,'P_CONSIDER_CONTENT',P_CONSIDER_CONTENT);
4050 END IF;
4051
4052 IF NOT FND_API.compatible_api_call (
4053 l_api_version,
4054 p_api_version,
4055 l_api_name,
4056 G_PKG_NAME) THEN
4057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4058 END IF;
4059
4060 -- Check p_init_msg_list
4061 IF FND_API.to_boolean(p_init_msg_list) THEN
4062 FND_MSG_PUB.initialize;
4063 END IF;
4064
4065 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4066
4067 l_count := 0;
4068
4069 IF (p_logging_entity_name = 'TRIP') THEN
4070 OPEN Get_Trip_Exceptions(p_logging_entity_id);
4071 FETCH Get_Trip_Exceptions INTO l_severity;
4072 IF Get_Trip_Exceptions%NOTFOUND THEN
4073 l_severity := NULL;
4074 END IF;
4075 CLOSE Get_Trip_Exceptions;
4076 IF l_severity IS NOT NULL THEN
4077 l_count := x_exceptions_tab.COUNT + 1;
4078 x_exceptions_tab(l_count).entity_name := p_logging_entity_name;
4079 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4080 x_exceptions_tab(l_count).exception_behavior := l_severity;
4081 END IF;
4082 IF p_consider_content = 'Y' THEN
4083 -- check for stop level exceptions
4084 FOR stop_rec IN Get_Stops_Trip(p_logging_entity_id) LOOP
4085 Check_Exceptions(
4086 -- Standard parameters
4087 p_api_version => 1.0,
4088 x_return_status => l_return_status,
4089 x_msg_count => l_msg_count,
4090 x_msg_data => l_msg_data,
4091 -- program specific parameters
4092 p_logging_entity_id => stop_rec.stop_id,
4093 p_logging_entity_name => 'STOP',
4094 p_consider_content => 'Y',
4095 -- program specific out parameters
4096 x_exceptions_tab => x_exceptions_tab,
4097 p_caller => p_caller||c_trip
4098 );
4099 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4100 IF l_debug_on THEN
4101 WSH_DEBUG_SV.logmsg('Check Exception failed for Stop :'||stop_rec.stop_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4102 END IF;
4103 RAISE WSH_CHECK_EXCEPTIONS_FAILED;
4104 END IF;
4105
4106 END LOOP;
4107 END IF;
4108
4109 ELSIF (p_logging_entity_name = 'STOP') THEN
4110 OPEN Get_Stop_Exceptions(p_logging_entity_id);
4111 FETCH Get_Stop_Exceptions INTO l_severity;
4112 IF Get_Stop_Exceptions%NOTFOUND THEN
4113 l_severity := NULL;
4114 END IF;
4115 CLOSE Get_Stop_Exceptions;
4116 IF l_severity IS NOT NULL THEN
4117 l_count := x_exceptions_tab.COUNT + 1;
4118 x_exceptions_tab(l_count).entity_name := p_logging_entity_name;
4119 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4120 x_exceptions_tab(l_count).exception_behavior := l_severity;
4121 END IF;
4122 IF p_consider_content = 'Y' THEN
4123 -- check for the deliveries in the stop
4124 FOR del_rec IN Get_Deliveries_Stop(p_logging_entity_id) LOOP
4125 Check_Exceptions(
4126 -- Standard parameters
4127 p_api_version => 1.0,
4128 x_return_status => l_return_status,
4129 x_msg_count => l_msg_count,
4130 x_msg_data => l_msg_data,
4131 -- program specific parameters
4132 p_logging_entity_id => del_rec.delivery_id,
4133 p_logging_entity_name => 'DELIVERY',
4134 p_consider_content => 'Y',
4135 -- program specific out parameters
4136 x_exceptions_tab => x_exceptions_tab
4137 );
4138 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4139 IF l_debug_on THEN
4140 WSH_DEBUG_SV.logmsg('Check Exception failed for Delivery :'||del_rec.delivery_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4141 END IF;
4142 RAISE WSH_CHECK_EXCEPTIONS_FAILED;
4143 END IF;
4144
4145 END LOOP;
4146 END IF;
4147 -- get dummy stop if caller is FTE or IB or TP Release.
4148 -- also caller shud not be TRIP (above), so that there is no dual count
4149 IF ( nvl(p_caller,'@@@') like 'FTE%'
4150 OR nvl(p_caller,'@@@') like 'WSH_IB%'
4151 OR nvl(p_caller,'@@@') like 'WSH_TP_RELEASE%'
4152 ) AND NOT ( nvl(p_caller,'@@@') like '%TRIP%') THEN
4153 FOR stop_rec IN c_dummy_stop(p_logging_entity_id) LOOP
4154 Check_Exceptions(
4155 -- Standard parameters
4156 p_api_version => 1.0,
4157 x_return_status => l_return_status,
4158 x_msg_count => l_msg_count,
4159 x_msg_data => l_msg_data,
4160 -- program specific parameters
4161 p_logging_entity_id => stop_rec.stop_id,
4162 p_logging_entity_name => 'STOP',
4163 p_consider_content => p_consider_content,
4164 -- program specific out parameters
4165 x_exceptions_tab => l_exceptions_tab,
4166 p_caller => p_caller
4167 );
4168 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4169 IF l_debug_on THEN
4170 WSH_DEBUG_SV.logmsg('Check Exception failed for Stop :'||stop_rec.stop_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4171 END IF;
4172 RAISE WSH_CHECK_EXCEPTIONS_FAILED;
4173 END IF;
4174 IF l_exceptions_tab IS NOT NULL AND l_exceptions_tab.COUNT>0 THEN
4175 FOR i IN 1..l_exceptions_tab.COUNT LOOP
4176 l_count := x_exceptions_tab.COUNT + 1;
4177 --if exception is logged against dummy stop, make that to be against physical stop if caller is FTE/IB/TP
4178 IF p_logging_entity_name='STOP' AND stop_rec.stop_id=l_exceptions_tab(i).entity_id THEN
4179 x_exceptions_tab(l_count).entity_name := p_logging_entity_name;
4180 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4181 x_exceptions_tab(l_count).exception_behavior := l_exceptions_tab(i).exception_behavior;
4182 ELSE
4183 x_exceptions_tab(l_count).entity_name := l_exceptions_tab(i).entity_name;
4184 x_exceptions_tab(l_count).entity_id := l_exceptions_tab(i).entity_id;
4185 x_exceptions_tab(l_count).exception_behavior := l_exceptions_tab(i).exception_behavior;
4186 END IF;
4187 END LOOP;
4188 END IF;
4189 END LOOP;
4190 END IF; --p_caller
4191
4192 ELSIF (p_logging_entity_name = 'DELIVERY') THEN
4193 l_delivery_exists := FALSE;
4194 FOR j in 1..x_exceptions_tab.COUNT LOOP
4195 IF x_exceptions_tab(j).entity_name = 'DELIVERY' AND
4196 x_exceptions_tab(j).entity_id = p_logging_entity_id THEN
4197 l_delivery_exists := TRUE;
4198 EXIT;
4199 END IF;
4200 END LOOP;
4201 IF NOT (l_delivery_exists) THEN
4202 OPEN Get_Delivery_Exceptions(p_logging_entity_id);
4203 FETCH Get_Delivery_Exceptions INTO l_severity;
4204 IF Get_Delivery_Exceptions%NOTFOUND THEN
4205 l_severity := NULL;
4206 END IF;
4207 CLOSE Get_Delivery_Exceptions;
4208 IF l_severity IS NOT NULL THEN
4209 l_count := x_exceptions_tab.COUNT + 1;
4210 x_exceptions_tab(l_count).entity_name := p_logging_entity_name;
4211 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4212 x_exceptions_tab(l_count).exception_behavior := l_severity;
4213 END IF;
4214
4215 IF p_consider_content = 'Y' THEN
4216 -- go for the contents of the delivery
4217 FOR detail_rec IN Get_Details_Delivery(p_logging_entity_id) LOOP
4218 Check_Exceptions(
4219 -- Standard parameters
4220 p_api_version => 1.0,
4221 x_return_status => l_return_status,
4222 x_msg_count => l_msg_count,
4223 x_msg_data => l_msg_data,
4224 -- program specific parameters
4225 p_logging_entity_id => detail_rec.delivery_detail_id,
4226 p_logging_entity_name => 'LINE',
4227 p_consider_content => 'Y',
4228 -- program specific out parameters
4229 x_exceptions_tab => x_exceptions_tab
4230 );
4231 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4232 IF l_debug_on THEN
4233 WSH_DEBUG_SV.logmsg('Check Exception failed for Detail :'||detail_rec.delivery_detail_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4234 END IF;
4235 RAISE WSH_CHECK_EXCEPTIONS_FAILED;
4236 END IF;
4237
4238 END LOOP;
4239 END IF;
4240 END IF;
4241
4242 ELSIF (p_logging_entity_name = 'LINE') THEN
4243 OPEN Get_Detail_Exceptions(p_logging_entity_id);
4244 FETCH Get_Detail_Exceptions INTO l_severity;
4245 IF Get_Detail_Exceptions%NOTFOUND THEN
4246 l_severity := NULL;
4247 END IF;
4248 CLOSE Get_Detail_Exceptions;
4249 IF l_severity IS NOT NULL THEN
4250 l_count := x_exceptions_tab.COUNT + 1;
4251 OPEN Get_Container_Flag(p_logging_entity_id);
4252 FETCH Get_Container_Flag INTO l_container_flag;
4253 CLOSE Get_Container_Flag;
4254 IF l_container_flag = 'Y' THEN
4255 x_exceptions_tab(l_count).entity_name := 'CONTAINER';
4256 ELSE
4257 x_exceptions_tab(l_count).entity_name := 'LINE';
4258 END IF;
4259 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4260 x_exceptions_tab(l_count).exception_behavior := l_severity;
4261 END IF;
4262
4263 ELSIF (p_logging_entity_name = 'CONTAINER') THEN
4264 OPEN Get_Detail_Exceptions(p_logging_entity_id);
4265 FETCH Get_Detail_Exceptions INTO l_severity;
4266 IF Get_Detail_Exceptions%NOTFOUND THEN
4267 l_severity := NULL;
4268 END IF;
4269 CLOSE Get_Detail_Exceptions;
4270 IF l_severity IS NOT NULL THEN
4271 l_count := x_exceptions_tab.COUNT + 1;
4272 x_exceptions_tab(l_count).entity_name := p_logging_entity_name;
4273 x_exceptions_tab(l_count).entity_id := p_logging_entity_id;
4274 x_exceptions_tab(l_count).exception_behavior := l_severity;
4275 END IF;
4276
4277 IF p_consider_content = 'Y' THEN
4278 -- go for the contents of the container
4279 FOR detail_rec IN Get_Contents_Container(p_logging_entity_id) LOOP
4280 Check_Exceptions(
4281 -- Standard parameters
4282 p_api_version => 1.0,
4283 x_return_status => l_return_status,
4284 x_msg_count => l_msg_count,
4285 x_msg_data => l_msg_data,
4286 -- program specific parameters
4287 p_logging_entity_id => detail_rec.delivery_detail_id,
4288 p_logging_entity_name => 'LINE',
4289 p_consider_content => 'Y',
4290 -- program specific out parameters
4291 x_exceptions_tab => x_exceptions_tab
4292 );
4293 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4294 IF l_debug_on THEN
4295 WSH_DEBUG_SV.logmsg('Check Exception failed for Detail :'||detail_rec.delivery_detail_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4296 END IF;
4297 RAISE WSH_CHECK_EXCEPTIONS_FAILED;
4298 END IF;
4299
4300 END LOOP;
4301 END IF;
4302
4303
4304 ELSE
4305 RAISE WSH_INVALID_LOGGING_ENTITY;
4306 END IF;
4307
4308 --
4309 -- Debug Statements
4310 --
4311 IF l_debug_on THEN
4312 WSH_DEBUG_SV.pop(l_module_name);
4313 END IF;
4314 --
4315
4316 EXCEPTION
4317 WHEN WSH_CHECK_EXCEPTIONS_FAILED THEN
4318 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4319 wsh_util_core.add_message(x_return_status);
4320 FND_MSG_PUB.Count_And_Get(
4321 p_count => x_msg_count,
4322 p_data => x_msg_data,
4323 p_encoded => FND_API.G_FALSE
4324 );
4325 --
4326 -- Debug Statements
4327 --
4328 IF l_debug_on THEN
4329 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CHECK_EXCEPTIONS_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4330 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CHECK_EXCEPTIONS_FAILED');
4331 END IF;
4332 --
4333
4334 WHEN WSH_INVALID_LOGGING_ENTITY THEN
4335 FND_MESSAGE.Set_Name('FND', 'WSH_XC_INVALID_LOGGING_ENTITY');
4336 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4337 wsh_util_core.add_message(x_return_status);
4338 FND_MSG_PUB.Count_And_Get(
4339 p_count => x_msg_count,
4340 p_data => x_msg_data,
4341 p_encoded => FND_API.G_FALSE
4342 );
4343 --
4344 -- Debug Statements
4345 --
4346 IF l_debug_on THEN
4347 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_XC_INVALID_LOGGING_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4348 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_XC_INVALID_LOGGING_ENTITY');
4349 END IF;
4350 --
4351
4352 WHEN others THEN
4353 IF Get_Trip_Exceptions%ISOPEN THEN
4354 CLOSE Get_Trip_Exceptions;
4355 END IF;
4356 IF Get_Stop_Exceptions%ISOPEN THEN
4357 CLOSE Get_Stop_Exceptions;
4358 END IF;
4359 IF Get_Delivery_Exceptions%ISOPEN THEN
4360 CLOSE Get_Delivery_Exceptions;
4361 END IF;
4362 IF Get_Detail_Exceptions%ISOPEN THEN
4363 CLOSE Get_Detail_Exceptions;
4364 END IF;
4365 IF Get_Details_Delivery%ISOPEN THEN
4366 CLOSE Get_Details_Delivery;
4367 END IF;
4368 IF Get_Deliveries_Stop%ISOPEN THEN
4369 CLOSE Get_Deliveries_Stop;
4370 END IF;
4371 IF Get_Stops_Trip%ISOPEN THEN
4372 CLOSE Get_Stops_Trip;
4373 END IF;
4374
4375 wsh_util_core.default_handler('WSH_XC_UTIL.CHECK_EXCEPTIONS');
4376 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4377
4378 --
4379 -- Debug Statements
4380 --
4381 IF l_debug_on THEN
4382 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4383 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4384 END IF;
4385 --
4386
4387 END Check_Exceptions;
4388
4389
4390 -- -------------------------------------------------------------------------------
4391 -- Start of comments
4392 -- API name : Close_Exceptions
4393 -- Type : Public
4394 -- Function : This procedure takes input as Entity Name and Entity Id
4395 -- and closes all exceptions logged against it.
4396 -- If p_consider_content is set to 'Y', then the API also looks
4397 -- at the contents of the Entity and closes all exceptions for the
4398 -- child entities. This is drilled to lowest child entity.
4399 -- This API should be called ONLY if Check_Exceptions is called before
4400 -- it. This is because this API assumes all Error Exceptions are Resolved
4401 -- prior to this API call and closes OPEN/NO_ACTION_REQUIRED exceptions
4402 -- unless they are Information Only (FP bug 4370532).
4403 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
4404 -- TRIP, STOP
4405 -- End of comments
4406 -- --------------------------------------------------------------------------------
4407
4408 PROCEDURE Close_Exceptions (
4409 -- Standard parameters
4410 p_api_version IN NUMBER,
4411 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4412 x_return_status OUT NOCOPY VARCHAR2,
4413 x_msg_count OUT NOCOPY NUMBER,
4414 x_msg_data OUT NOCOPY VARCHAR2,
4415
4416 -- program specific parameters
4417 p_logging_entity_id IN NUMBER,
4418 p_logging_entity_name IN VARCHAR2,
4419 p_consider_content IN VARCHAR2,
4420 p_caller IN VARCHAR2
4421 ) IS
4422
4423 CURSOR Get_Details_Delivery(v_delivery_id NUMBER) IS
4424 SELECT wdd.delivery_detail_id, wdd.container_flag
4425 FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
4426 WHERE wda.delivery_id = v_delivery_id
4427 AND wdd.delivery_detail_id = wda.delivery_detail_id
4428 ORDER BY wdd.container_flag;
4429
4430 -- performance bug 5257207: SC-1: added ALL since the selections
4431 -- are mutually exclusive (a delivery cannot be both picked up
4432 -- and dropped off at the same stop).
4433 -- This is the same cursor as in check_exceptions.
4434 CURSOR Get_Deliveries_Stop(v_stop_id NUMBER) IS
4435 -- pick up deliveries
4436 SELECT dg.delivery_id
4437 FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
4438 WHERE dg.delivery_id = dl.delivery_id
4439 AND st.stop_location_id = dl.initial_pickup_location_id
4440 AND st.stop_id = dg.pick_up_stop_id
4441 AND st.stop_id = v_stop_id
4442 UNION ALL
4443 -- drop off deliveries
4444 SELECT dg.delivery_id
4445 FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
4446 WHERE dg.delivery_id = dl.delivery_id
4447 AND st.stop_location_id = dl.ultimate_dropoff_location_id
4448 AND st.stop_id = dg.drop_off_stop_id
4449 AND st.stop_id = v_stop_id;
4450
4451 CURSOR Get_Stops_Trip(v_trip_id NUMBER) IS
4452 SELECT stop_id
4453 FROM wsh_trip_stops
4454 WHERE trip_id = v_trip_id
4455 ORDER BY stop_sequence_number ASC ;
4456
4457 CURSOR c_dummy_stop (v_stop_id NUMBER) IS
4458 SELECT stop_id
4459 FROM wsh_trip_stops wts
4460 WHERE wts.physical_stop_id IS NOT NULL
4461 AND wts.physical_location_id IS NOT NULL
4462 AND wts.physical_stop_id=v_stop_id
4463 AND wts.trip_id =(SELECT trip_id
4464 FROM wsh_trip_stops wts1
4465 WHERE wts1.stop_id=v_stop_id);
4466
4467 -- Standard call to check for call compatibility
4468 l_api_version CONSTANT NUMBER := 1.0;
4469 l_api_name CONSTANT VARCHAR2(30):= 'Close_Exceptions';
4470 c_trip CONSTANT VARCHAR2(30):= 'TRIP';
4471
4472 WSH_INVALID_LOGGING_ENTITY EXCEPTION;
4473 WSH_PURGE_FAILED EXCEPTION;
4474 WSH_CLOSE_EXCEPTIONS_FAILED EXCEPTION;
4475
4476 l_count NUMBER;
4477 l_return_status VARCHAR2(1);
4478 l_msg_count NUMBER;
4479 l_msg_data VARCHAR2(2000);
4480
4481 --
4482 l_debug_on BOOLEAN;
4483 --
4484 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'|| 'CLOSE_EXCEPTIONS';
4485 --
4486 BEGIN
4487
4488 --
4489 --
4490 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4491 --
4492 IF l_debug_on IS NULL
4493 THEN
4494 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4495 END IF;
4496 --
4497 --
4498 -- Debug Statements
4499 --
4500 IF l_debug_on THEN
4501 WSH_DEBUG_SV.push(l_module_name);
4502 --
4503 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
4504 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
4505 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_NAME',P_LOGGING_ENTITY_NAME);
4506 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_ID',P_LOGGING_ENTITY_ID);
4507 END IF;
4508
4509 IF NOT FND_API.compatible_api_call (
4510 l_api_version,
4511 p_api_version,
4512 l_api_name,
4513 G_PKG_NAME) THEN
4514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4515 END IF;
4516
4517 -- Check p_init_msg_list
4518 IF FND_API.to_boolean(p_init_msg_list) THEN
4519 FND_MSG_PUB.initialize;
4520 END IF;
4521
4522 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4523
4524 IF (p_logging_entity_name = 'TRIP') THEN
4525 WSH_XC_UTIL.Purge (
4526 p_api_version => p_api_version,
4527 x_return_status => l_return_status,
4528 x_msg_count => l_msg_count,
4529 x_msg_data => l_msg_data,
4530 x_no_of_recs_purged => l_count,
4531 p_trip_id => p_logging_entity_id,
4532 p_action => C_ACTION_SEMICLOSED
4533 );
4534 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4535 IF l_debug_on THEN
4536 WSH_DEBUG_SV.logmsg('Purge failed for Trip :'||p_logging_entity_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4537 END IF;
4538 RAISE WSH_PURGE_FAILED;
4539 END IF;
4540
4541 IF p_consider_content = 'Y' THEN
4542 -- Close all stop level exceptions
4543 FOR stop_rec IN Get_Stops_Trip(p_logging_entity_id) LOOP
4544 WSH_XC_UTIL.Close_Exceptions (
4545 p_api_version => p_api_version,
4546 x_return_status => l_return_status,
4547 x_msg_count => l_msg_count,
4548 x_msg_data => l_msg_data,
4549 p_logging_entity_id => stop_rec.stop_id,
4550 p_logging_entity_name => 'STOP',
4551 p_consider_content => p_consider_content,
4552 p_caller => p_caller||c_trip
4553 );
4554 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4555 IF l_debug_on THEN
4556 WSH_DEBUG_SV.logmsg('Close_Exception failed for Stop :'||stop_rec.stop_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4557 END IF;
4558 RAISE WSH_CLOSE_EXCEPTIONS_FAILED;
4559 END IF;
4560 END LOOP;
4561 END IF;
4562
4563 ELSIF (p_logging_entity_name = 'STOP') THEN
4564
4565 IF l_debug_on THEN
4566 WSH_DEBUG_SV.logmsg('Calling Purge for Stop '||p_logging_entity_id ,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4567 END IF;
4568 WSH_XC_UTIL.Purge (
4569 p_api_version => p_api_version,
4570 x_return_status => x_return_status,
4571 x_msg_count => x_msg_count,
4572 x_msg_data => x_msg_data,
4573 x_no_of_recs_purged => l_count,
4574 p_trip_stop_id => p_logging_entity_id,
4575 p_action => C_ACTION_SEMICLOSED
4576 );
4577 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4578 IF l_debug_on THEN
4579 WSH_DEBUG_SV.logmsg('Purge failed for Stop :'||p_logging_entity_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4580 END IF;
4581 RAISE WSH_PURGE_FAILED;
4582 END IF;
4583
4584 IF p_consider_content = 'Y' THEN
4585 -- go for the deliveries, lines and containers
4586 FOR del_rec IN Get_Deliveries_Stop(p_logging_entity_id) LOOP
4587 WSH_XC_UTIL.Close_Exceptions (
4588 p_api_version => p_api_version,
4589 x_return_status => l_return_status,
4590 x_msg_count => l_msg_count,
4591 x_msg_data => l_msg_data,
4592 p_logging_entity_id => del_rec.delivery_id,
4593 p_logging_entity_name => 'DELIVERY',
4594 p_consider_content => p_consider_content
4595 );
4596 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4597 IF l_debug_on THEN
4598 WSH_DEBUG_SV.logmsg('Close_Exception failed for Delivery :'||del_rec.delivery_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4599 END IF;
4600 RAISE WSH_CLOSE_EXCEPTIONS_FAILED;
4601 END IF;
4602 END LOOP;
4603 END IF;
4604
4605 -- get dummy stop if caller is FTE or IB or TP Release.
4606 -- also caller shud not be TRIP (above), so that there is no dual count
4607 IF ( nvl(p_caller,'@@@') like 'FTE%'
4608 OR nvl(p_caller,'@@@') like 'WSH_IB%'
4609 OR nvl(p_caller,'@@@') like 'WSH_TP_RELEASE%'
4610 ) AND NOT ( nvl(p_caller,'@@@') like '%TRIP%') THEN
4611 FOR stop_rec IN c_dummy_stop(p_logging_entity_id) LOOP
4612 WSH_XC_UTIL.Close_Exceptions (
4613 p_api_version => p_api_version,
4614 x_return_status => l_return_status,
4615 x_msg_count => l_msg_count,
4616 x_msg_data => l_msg_data,
4617 p_logging_entity_id => stop_rec.stop_id,
4618 p_logging_entity_name => 'STOP',
4619 p_consider_content => p_consider_content,
4620 p_caller => p_caller
4621 );
4622 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4623 IF l_debug_on THEN
4624 WSH_DEBUG_SV.logmsg('Close_Exception failed for Stop :'||stop_rec.stop_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4625 END IF;
4626 RAISE WSH_CLOSE_EXCEPTIONS_FAILED;
4627 END IF;
4628 END LOOP;
4629 END IF;
4630 ELSIF (p_logging_entity_name = 'DELIVERY') THEN
4631 WSH_XC_UTIL.Purge (
4632 p_api_version => p_api_version,
4633 x_return_status => x_return_status,
4634 x_msg_count => x_msg_count,
4635 x_msg_data => x_msg_data,
4636 x_no_of_recs_purged => l_count,
4637 p_delivery_id => p_logging_entity_id,
4638 p_delivery_contents => p_consider_content,
4639 p_action => C_ACTION_SEMICLOSED
4640 );
4641 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4642 IF l_debug_on THEN
4643 WSH_DEBUG_SV.logmsg('Purge failed for Delivery :'||p_logging_entity_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4644 END IF;
4645 RAISE WSH_PURGE_FAILED;
4646 END IF;
4647
4648 ELSIF (p_logging_entity_name IN ('LINE','CONTAINER')) THEN
4649 WSH_XC_UTIL.Purge (
4650 p_api_version => p_api_version,
4651 x_return_status => x_return_status,
4652 x_msg_count => x_msg_count,
4653 x_msg_data => x_msg_data,
4654 x_no_of_recs_purged => l_count,
4655 p_delivery_detail_id => p_logging_entity_id,
4656 p_action => C_ACTION_SEMICLOSED
4657 );
4658 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4659 IF l_debug_on THEN
4660 WSH_DEBUG_SV.logmsg('Purge failed for Detail/Container :'||p_logging_entity_id,WSH_DEBUG_SV.C_EXCEP_LEVEL);
4661 END IF;
4662 RAISE WSH_PURGE_FAILED;
4663 END IF;
4664
4665 ELSE
4666
4667 RAISE WSH_INVALID_LOGGING_ENTITY;
4668
4669 END IF;
4670
4671 --
4672 -- Debug Statements
4673 --
4674 IF l_debug_on THEN
4675 WSH_DEBUG_SV.pop(l_module_name);
4676 END IF;
4677 --
4678
4679 EXCEPTION
4680 WHEN WSH_CLOSE_EXCEPTIONS_FAILED THEN
4681 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4682 wsh_util_core.add_message(x_return_status);
4683 FND_MSG_PUB.Count_And_Get(
4684 p_count => x_msg_count,
4685 p_data => x_msg_data,
4686 p_encoded => FND_API.G_FALSE
4687 );
4688
4689 --
4690 -- Debug Statements
4691 --
4692 IF l_debug_on THEN
4693 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CLOSE_EXCEPTIONS_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4694 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CLOSE_EXCEPTIONS_FAILED');
4695 END IF;
4696 --
4697
4698 WHEN WSH_PURGE_FAILED THEN
4699 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4700 wsh_util_core.add_message(x_return_status);
4701 FND_MSG_PUB.Count_And_Get(
4702 p_count => x_msg_count,
4703 p_data => x_msg_data,
4704 p_encoded => FND_API.G_FALSE
4705 );
4706
4707 --
4708 -- Debug Statements
4709 --
4710 IF l_debug_on THEN
4711 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_PURGE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4712 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_PURGE_FAILED');
4713 END IF;
4714 --
4715
4716 WHEN WSH_INVALID_LOGGING_ENTITY THEN
4717 FND_MESSAGE.Set_Name('FND', 'WSH_XC_INVALID_LOGGING_ENTITY');
4718 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4719 wsh_util_core.add_message(x_return_status);
4720 FND_MSG_PUB.Count_And_Get(
4721 p_count => x_msg_count,
4722 p_data => x_msg_data,
4723 p_encoded => FND_API.G_FALSE
4724 );
4725
4726 --
4727 -- Debug Statements
4728 --
4729 IF l_debug_on THEN
4730 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_LOGGING_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4731 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_LOGGING_ENTITY');
4732 END IF;
4733 --
4734
4735 WHEN others THEN
4736 wsh_util_core.default_handler('WSH_XC_UTIL.CLOSE_EXCEPTIONS');
4737 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4738
4739 --
4740 -- Debug Statements
4741 --
4742 IF l_debug_on THEN
4743 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4745 END IF;
4746 --
4747
4748 END Close_Exceptions;
4749
4750 --OTM R12
4751 ----------------------------------------------------------
4752 -- PROCEDURE CLOSE_OTM_EXCEPTION
4753 --
4754 -- parameters: p_delivery_id - The ID of the delivery whose exception should be closed.
4755 -- p_exceptions_to_close_tab - The Exceptions that are to be closed.
4756 -- x_return_status - return status
4757 --
4758 -- description: Closes the OTM exceptions passed in 'p_exceptions_to_close_tab' for the delivery p_delivery_id
4759 --
4760 ----------------------------------------------------------
4761
4762 PROCEDURE CLOSE_OTM_EXCEPTION(
4763 p_delivery_id IN WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE,
4764 p_exceptions_to_close_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
4765 x_return_status OUT NOCOPY VARCHAR2) IS
4766
4767 l_exception_name WSH_EXCEPTION_DEFINITIONS_TL.EXCEPTION_NAME%TYPE;
4768 l_debug_on BOOLEAN;
4769 l_count NUMBER;
4770 l_msg_count NUMBER;
4771 l_msg_data VARCHAR2(32767);
4772 l_return_status VARCHAR2(1);
4773 i NUMBER;
4774 l_num_warn NUMBER;
4775 --
4776 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CLOSE_OTM_EXCEPTION';
4777 --
4778
4779 BEGIN
4780 --
4781 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4782 --
4783 IF l_debug_on IS NULL THEN
4784 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4785 END IF;
4786 --
4787 IF l_debug_on THEN
4788 WSH_DEBUG_SV.push(l_module_name);
4789 WSH_DEBUG_SV.log(l_module_name, 'delivery id', p_delivery_id);
4790 WSH_DEBUG_SV.log(l_module_name, 'number of exceptions to close', p_exceptions_to_close_tab.COUNT);
4791 END IF;
4792 --
4793 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4794 l_exception_name := NULL;
4795 l_count := 0;
4796 l_msg_count := 0;
4797 l_msg_data := NULL;
4798 l_num_warn := 0;
4799 i := 0;
4800
4801 IF (p_exceptions_to_close_tab.COUNT > 0 AND p_delivery_id IS NOT NULL) THEN
4802
4803 i := p_exceptions_to_close_tab.FIRST;
4804 WHILE i IS NOT NULL LOOP
4805
4806 l_exception_name := p_exceptions_to_close_tab(i);
4807
4808 -- call purge with action 'CLOSED'
4809 IF l_debug_on THEN
4810 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.PURGE',WSH_DEBUG_SV.C_PROC_LEVEL);
4811 END IF;
4812
4813 WSH_XC_UTIL.purge (
4814 p_api_version => 1.0,
4815 x_return_status => l_return_status,
4816 x_msg_count => l_msg_count,
4817 x_msg_data => l_msg_data,
4818 x_no_of_recs_purged => l_count,
4819 p_exception_name => l_exception_name,
4820 p_delivery_id => p_delivery_id,
4821 p_delivery_contents => 'N',
4822 p_action => 'CLOSED'
4823 );
4824
4825 IF l_debug_on THEN
4826 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.purge: ' || l_return_status);
4827 WSH_DEBUG_SV.log(l_module_name, 'number of exceptions purged', l_count);
4828 END IF;
4829
4830 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4831 x_return_status := l_return_status;
4832 EXIT;
4833 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4834 l_num_warn := l_num_warn + 1;
4835 END IF;
4836
4837 i := p_exceptions_to_close_tab.NEXT(i);
4838 END LOOP;
4839 END IF;
4840
4841 IF l_num_warn > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4842 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4843 END IF;
4844
4845 -- Debug Statements
4846 --
4847 IF l_debug_on THEN
4848 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4849 WSH_DEBUG_SV.pop(l_module_name);
4850 END IF;
4851 --
4852
4853 EXCEPTION
4854 --
4855 WHEN OTHERS THEN
4856 wsh_util_core.default_handler('WSH_XC_UTIL.CLOSE_OTM_EXCEPTION', l_module_name);
4857 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4858 IF l_debug_on THEN
4859 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected error has occured. '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4860 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4861 END IF;
4862
4863 END CLOSE_OTM_EXCEPTION;
4864
4865 ----------------------------------------------------------
4866 -- PROCEDURE LOG_OTM_EXCEPTION
4867 --
4868 -- parameters: p_delivery_info_tab table of deliveries to check and log
4869 -- exceptions for
4870 -- p_new_interface_flag_tab the table of new interface flag for
4871 -- the table of deliveries
4872 -- x_return_status return status
4873 --
4874 -- description: This procedure checks each delivery's tms_interface_flag to
4875 -- see if it has changed. The new tms_interface_flag is either
4876 -- calculated or supplied in the parameters. The old
4877 -- tms_interface_flag is stored in the info table. The procedure
4878 -- log and close exceptions on the delivery based on the old
4879 -- and new tms_interface_flag.
4880 ----------------------------------------------------------
4881
4882 PROCEDURE LOG_OTM_EXCEPTION(
4883 p_delivery_info_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
4884 p_new_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
4885 x_return_status OUT NOCOPY VARCHAR2) IS
4886
4887 l_msg_count NUMBER;
4888 l_msg_data VARCHAR2(32767);
4889 l_exception_id WSH_EXCEPTIONS.EXCEPTION_ID%TYPE;
4890 l_exception_message WSH_EXCEPTIONS.MESSAGE%TYPE;
4891 l_num_error NUMBER;
4892 l_num_warn NUMBER;
4893 l_return_status VARCHAR2(1);
4894
4895 l_new_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
4896 l_exception_name WSH_EXCEPTION_DEFINITIONS_TL.EXCEPTION_NAME%TYPE;
4897 l_exceptions_to_close_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
4898 i NUMBER;
4899 --
4900 l_debug_on BOOLEAN;
4901 --
4902 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
4903 '.' || 'LOG_OTM_EXCEPTION';
4904 --
4905 BEGIN
4906 --
4907 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4908 --
4909 IF l_debug_on IS NULL THEN
4910 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4911 END IF;
4912 --
4913 IF l_debug_on THEN
4914 WSH_DEBUG_SV.push(l_module_name);
4915 WSH_DEBUG_SV.log(l_module_name, 'delivery record count', p_delivery_info_tab.COUNT);
4916 WSH_DEBUG_SV.log(l_module_name, 'interface flag count', p_new_interface_flag_tab.COUNT);
4917 END IF;
4918
4919 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4920
4921 --initialize
4922 l_num_warn := 0;
4923 l_num_error := 0;
4924 l_msg_count := 0;
4925 l_msg_data := NULL;
4926 l_new_interface_flag := NULL;
4927 i := 0;
4928
4929 IF (p_delivery_info_tab.COUNT = 0) THEN
4930 IF l_debug_on THEN
4931 WSH_DEBUG_SV.logmsg(l_module_name, 'no delivery to log exceptions');
4932 END IF;
4933 END IF;
4934
4935 i := p_delivery_info_tab.FIRST;
4936
4937 WHILE i IS NOT NULL LOOP
4938
4939 --this loop is used to figure out the new tms interface flag value depending on the old one
4940 --when the input tms interface flag was not specified
4941 IF (p_new_interface_flag_tab.COUNT = 0 OR p_new_interface_flag_tab(i) IS NULL) THEN
4942 --figure out the new status as if it's just an update on delivery
4943 IF (p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
4944 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
4945 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
4946 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
4947 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
4948 l_new_interface_flag := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
4949 ELSE
4950 l_new_interface_flag := p_delivery_info_tab(i).tms_interface_flag;
4951 END IF;
4952 ELSE
4953 l_new_interface_flag := p_new_interface_flag_tab(i);
4954 END IF;
4955
4956 IF l_debug_on THEN
4957 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id' , p_delivery_info_tab(i).delivery_id);
4958 WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag', l_new_interface_flag);
4959 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_info_tab(i).tms_interface_flag', p_delivery_info_tab(i).tms_interface_flag);
4960 IF p_new_interface_flag_tab.COUNT > 0 THEN
4961 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_info_tab(i).tms_interface_flag', p_new_interface_flag_tab(i));
4962 END IF;
4963 END IF;
4964
4965 --log and close exceptions only if new status is different and not IN Process status
4966 IF ((l_new_interface_flag <> p_delivery_info_tab(i).tms_interface_flag)
4967 AND (l_new_interface_flag NOT IN (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
4968 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
4969 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS))) THEN
4970
4971 l_exception_id := NULL;
4972 l_exception_message := NULL;
4973 l_exception_name := NULL;
4974
4975 -- Start of Ship Confirm ECO
4976 -- Close the old exception(s) logged against this delivery, as we are going to
4977 -- log a new exception based on the l_new_interface_flag.
4978 --
4979 IF p_delivery_info_tab(i).tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER THEN
4980 l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_AWAIT_TRIP';
4981 ELSIF p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
4982 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
4983 l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_UPDATE_REQ';
4984 ELSIF p_delivery_info_tab(i).tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED THEN
4985 l_exceptions_to_close_tab(1) := 'WSH_OTM_SHIPMENT_REC';
4986 ELSIF p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
4987 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS) THEN
4988 l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_CREATE_REQ';
4989 ELSIF p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
4990 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
4991 l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_DELETE_REQ';
4992 END IF;
4993
4994 IF l_exceptions_to_close_tab.COUNT > 0 THEN
4995
4996 IF l_debug_on THEN
4997 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.CLOSE_OTM_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
4998 END IF;
4999
5000 WSH_XC_UTIL.close_otm_exception(
5001 p_delivery_id => p_delivery_info_tab(i).delivery_id,
5002 p_exceptions_to_close_tab => l_exceptions_to_close_tab,
5003 x_return_status => l_return_status
5004 );
5005
5006 IF l_debug_on THEN
5007 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.close_otm_exception: ' || l_return_status);
5008 END IF;
5009
5010 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5011 l_num_error := l_num_error + 1;
5012 EXIT;
5013 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5014 l_num_warn := l_num_warn + 1;
5015 END IF;
5016 END IF;
5017
5018 --log the deleted exception before the new CR exception is logged
5019
5020 IF ((l_new_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
5021 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
5022 WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED))
5023 AND (p_delivery_info_tab(i).tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS)) THEN
5024
5025 --log deleted, only when set from DP to NS or CR or CMP
5026
5027 l_exception_name := 'WSH_OTM_DEL_DELETED';
5028 FND_MESSAGE.SET_NAME('WSH',l_exception_name); --message name is same as exception name
5029 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', p_delivery_info_tab(i).name);
5030 l_exception_message := FND_MESSAGE.GET;
5031
5032 IF l_debug_on THEN
5033 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
5034 END IF;
5035
5036 WSH_XC_UTIL.log_exception(
5037 p_api_version => 1.0,
5038 x_return_status => l_return_status,
5039 x_msg_count => l_msg_count,
5040 x_msg_data => l_msg_data,
5041 x_exception_id => l_exception_id,
5042 p_exception_location_id => p_delivery_info_tab(i).INITIAL_PICKUP_LOCATION_ID,
5043 p_logged_at_location_id => p_delivery_info_tab(i).INITIAL_PICKUP_LOCATION_ID,
5044 p_logging_entity => 'SHIPPER',
5045 p_logging_entity_id => FND_GLOBAL.USER_ID,
5046 p_exception_name => l_exception_name,
5047 p_delivery_id => p_delivery_info_tab(i).delivery_id,
5048 p_message => l_exception_message);
5049
5050
5051 IF l_debug_on THEN
5052 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.log_exception: ' || l_return_status);
5053 END IF;
5054
5055 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5056 l_num_error := l_num_error + 1;
5057 EXIT;
5058 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5059 l_num_warn := l_num_warn + 1;
5060 END IF;
5061
5062 END IF;
5063
5064 l_exception_name := NULL;
5065
5066 --logging the new exception
5067 IF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED) THEN
5068
5069 l_exception_name := 'WSH_OTM_DEL_CREATE_REQ';
5070
5071 ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED) THEN
5072
5073 l_exception_name := 'WSH_OTM_DEL_UPDATE_REQ';
5074
5075 ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED) THEN
5076
5077 l_exception_name := 'WSH_OTM_DEL_DELETE_REQ';
5078
5079 ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER) THEN
5080
5081 l_exception_name := 'WSH_OTM_DEL_AWAIT_TRIP';
5082
5083 ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED) THEN
5084
5085 l_exception_name := 'WSH_OTM_SHIPMENT_REC';
5086
5087 END IF;
5088
5089 IF (l_exception_name IS NOT NULL) THEN
5090
5091 --log the messages, message name is same as exception name
5092 IF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED) THEN
5093
5094 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DELIVERY_SUCCESS');
5095
5096 ELSE
5097
5098 FND_MESSAGE.SET_NAME('WSH',l_exception_name);
5099 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', p_delivery_info_tab(i).name);
5100
5101 END IF;
5102
5103 l_exception_message := FND_MESSAGE.GET;
5104
5105 IF l_debug_on THEN
5106 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
5107 END IF;
5108
5109 WSH_XC_UTIL.log_exception(
5110 p_api_version => 1.0,
5111 x_return_status => l_return_status,
5112 x_msg_count => l_msg_count,
5113 x_msg_data => l_msg_data,
5114 x_exception_id => l_exception_id,
5115 p_exception_location_id => p_delivery_info_tab(i).INITIAL_PICKUP_LOCATION_ID,
5116 p_logged_at_location_id => p_delivery_info_tab(i).INITIAL_PICKUP_LOCATION_ID,
5117 p_logging_entity => 'SHIPPER',
5118 p_logging_entity_id => FND_GLOBAL.USER_ID,
5119 p_exception_name => l_exception_name,
5120 p_delivery_id => p_delivery_info_tab(i).delivery_id,
5121 p_message => l_exception_message);
5122
5123 IF l_debug_on THEN
5124 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.log_exception: ' || l_return_status);
5125 END IF;
5126
5127 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5128 l_num_error := l_num_error + 1;
5129 EXIT;
5130 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5131 l_num_warn := l_num_warn + 1;
5132 END IF;
5133
5134 END IF;
5135 END IF;
5136
5137 i := p_delivery_info_tab.NEXT(i);
5138
5139 END LOOP;
5140
5141 IF (l_num_error > 0)THEN
5142 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5143 ELSIF (l_num_warn > 0) THEN
5144 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5145 ELSE
5146 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5147 END IF;
5148
5149 --
5150 -- Debug Statements
5151 --
5152 IF l_debug_on THEN
5153 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5154 WSH_DEBUG_SV.pop(l_module_name);
5155 END IF;
5156 --
5157
5158 EXCEPTION
5159 WHEN others THEN
5160 wsh_util_core.default_handler('WSH_XC_UTIL.LOG_OTM_EXCEPTION', l_module_name);
5161 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5162
5163 IF l_debug_on THEN
5164 WSH_DEBUG_SV.logmsg(l_module_name,
5165 'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5166 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5167 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5168 END IF;
5169
5170 END LOG_OTM_EXCEPTION;
5171
5172 ----------------------------------------------------------
5173 -- PROCEDURE GET_OTM_DELIVERY_EXCEPTION
5174 --
5175 -- Parameters
5176 --
5177 -- Input p_delivery_id the delivery to get the OTM exceptions severity.
5178 --
5179 -- Output x_exception_name, The name of the OTM exception
5180 -- x_severity, Severity of the Exception
5181 -- x_return_status S when success U when exception is thrown.
5182 --
5183 -- description: This procedure gets the delivery's OTM exception and severity
5184 ----------------------------------------------------------
5185 PROCEDURE GET_OTM_DELIVERY_EXCEPTION
5186 (p_delivery_id IN WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE,
5187 x_exception_name OUT NOCOPY WSH_EXCEPTIONS.EXCEPTION_NAME%TYPE,
5188 x_severity OUT NOCOPY WSH_EXCEPTIONS.SEVERITY%TYPE,
5189 x_return_status OUT NOCOPY VARCHAR2) IS
5190
5191 --this cursor gets the delivery's otm exceptions ordered by severity
5192 --we used order by 1 to take advantage of the alphabetical order of ERROR and WARNING
5193 CURSOR c_get_delivery_otm_exceptions (p_delivery_id IN NUMBER) IS
5194 SELECT severity,
5195 DECODE(severity,'HIGH', 1, 'ERROR', 1, 'MEDIUM', 2, 'WARNING', 2, 3) rank,
5196 exception_name
5197 FROM wsh_exceptions
5198 WHERE delivery_id = p_delivery_id
5199 AND status IN ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
5200 AND severity IN ('HIGH','MEDIUM','ERROR','WARNING')
5201 AND exception_name IN
5202 ('WSH_OTM_DEL_CREATE_REQ','WSH_OTM_DEL_UPDATE_REQ',
5203 'WSH_OTM_DEL_AWAIT_TRIP')
5204 ORDER BY rank ASC;
5205
5206 l_severity WSH_EXCEPTIONS.SEVERITY%TYPE;
5207 l_exception_name WSH_EXCEPTIONS.EXCEPTION_NAME%TYPE;
5208 l_rank NUMBER;
5209
5210 l_debug_on BOOLEAN;
5211 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'GET_OTM_DELIVERY_EXCEPTION';
5212
5213 BEGIN
5214
5215 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5216
5217 IF l_debug_on IS NULL THEN
5218 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5219 END IF;
5220 --
5221 IF l_debug_on THEN
5222 WSH_DEBUG_SV.push(l_module_name);
5223 WSH_DEBUG_SV.log(l_module_name, 'delivery id', p_delivery_id);
5224 END IF;
5225
5226 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5227
5228 l_severity := NULL;
5229 l_rank := NULL;
5230 l_exception_name := NULL;
5231
5232 IF (p_delivery_id IS NULL) THEN
5233 IF l_debug_on THEN
5234 WSH_DEBUG_SV.logmsg(l_module_name, 'delivery id is NULL');
5235 WSH_DEBUG_SV.pop(l_module_name);
5236 END IF;
5237 x_severity := l_severity;
5238 x_exception_name := l_exception_name;
5239 RETURN;
5240 END IF;
5241
5242
5243 -- Return the exception with the highest severity.
5244 OPEN c_get_delivery_otm_exceptions(p_delivery_id);
5245
5246 FETCH c_get_delivery_otm_exceptions INTO l_severity, l_rank, l_exception_name;
5247
5248 IF c_get_delivery_otm_exceptions%NOTFOUND THEN
5249 l_severity := NULL;
5250 l_exception_name := NULL;
5251 END IF;
5252
5253 CLOSE c_get_delivery_otm_exceptions;
5254
5255 -- Assign to the output variables.
5256 -- Reason for the coresponding local variables being extendability.
5257
5258 x_severity := l_severity;
5259 x_exception_name := l_exception_name;
5260
5261 IF l_debug_on THEN
5262 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_severity);
5263 WSH_DEBUG_SV.log(l_module_name, 'x_exception_name', x_exception_name);
5264 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5265 WSH_DEBUG_SV.pop(l_module_name);
5266 END IF;
5267
5268 EXCEPTION
5269 WHEN OTHERS THEN
5270 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5271 wsh_util_core.default_handler('WSH_XC_UTIL.GET_OTM_DELIVERY_EXCEPTION', l_module_name);
5272
5273 IF c_get_delivery_otm_exceptions%ISOPEN THEN
5274 CLOSE c_get_delivery_otm_exceptions;
5275 END IF;
5276 IF l_debug_on THEN
5277 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5278 WSH_DEBUG_SV.pop(l_module_name);
5279 END IF;
5280
5281 END GET_OTM_DELIVERY_EXCEPTION;
5282 --END OTM R12
5283
5284 END WSH_XC_UTIL;
[Home] [Help]