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