1 PACKAGE BODY WSH_INBOUND_SHIP_ADVICE_PKG as
2 /* $Header: WSHINSAB.pls 120.5 2011/12/21 10:12:24 skanduku ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INBOUND_SHIP_ADVICE_PKG';
6 --
7 PROCEDURE Process_Ship_Advice(
8 p_delivery_interface_id IN NUMBER,
9 p_event_key IN VARCHAR2,
10 x_return_status OUT NOCOPY VARCHAR2
11 ) IS
12 l_delivery_id NUMBER;
13 l_delivery_name VARCHAR2(30);
14 l_organization_id NUMBER := NULL;
15 l_return_status VARCHAR2(30);
16 l_del_rows wsh_util_core.id_tab_type;
17 l_unplan_del_rows wsh_util_core.id_tab_type;
18 l_warehouse_type VARCHAR2(30);
19 l_dummy NUMBER;
20 l_not_recv_count NUMBER;
21 -- ship confirm variables
22 l_sc_action_flag VARCHAR2(1);
23 l_sc_intransit_flag VARCHAR2(1);
24 l_sc_close_trip_flag VARCHAR2(1);
25 l_sc_create_bol_flag VARCHAR2(1);
26 l_sc_stage_del_flag VARCHAR2(1);
27 l_sc_trip_ship_method VARCHAR2(30);
28 l_sc_actual_dep_date DATE;
29 l_sc_report_set_id NUMBER;
30 l_sc_report_set_name VARCHAR2(30);
31
32 l_pickup_stop_id NUMBER;
33 l_dropoff_stop_id NUMBER;
34 l_trip_id NUMBER;
35 l_actual_dep_date DATE;
36 l_stop_rows wsh_util_core.id_tab_type;
37
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(4000);
40 l_msg_details VARCHAR2(4000);
41
42 l_bo_rows wsh_util_core.id_tab_type; -- list of details to Backorder
43 l_bo_qtys wsh_util_core.id_tab_type; -- list of details BO qty
44 l_bo_qtys2 wsh_util_core.id_tab_type; -- list of details BO qty2
45 l_req_qtys wsh_util_core.id_tab_type; -- list of details req qty
46 l_overpick_qtys wsh_util_core.id_tab_type; -- list of details overpick qty
47 l_overpick_qtys2 wsh_util_core.id_tab_type; --list of details overpick qty2
48 l_cc_ids wsh_util_core.id_tab_type;
49
50 l_det_rq NUMBER;
51 l_det_pq NUMBER;
52 l_overpick_qty NUMBER;
53 l_cont_flag VARCHAR2(1);
54 l_wf_rs VARCHAR2(1); --Pick tp POD Wf Project
55 -- cursors
56
57 CURSOR del_id IS
58 SELECT delivery_id, name,
59 organization_id --AD Trip Consolidation heali
60 FROM wsh_new_del_interface
61 WHERE delivery_interface_id = p_delivery_interface_id
62 AND INTERFACE_ACTION_CODE ='94X_INBOUND';
63
64 -- TPW - Distributed changes
65 CURSOR c_get_del_name (c_delivery_id IN NUMBER) IS
66 SELECT name FROM wsh_new_deliveries
67 WHERE delivery_id = c_delivery_id;
68
69 CURSOR cont_not_recv_cur(l_delivery_id NUMBER, l_delivery_interface_id NUMBER) IS
70 SELECT wda.delivery_detail_id
71 FROM wsh_delivery_assignments_v wda,
72 wsh_delivery_details wdd
73 where wdd.delivery_detail_id = wda.delivery_detail_id
74 and wdd.container_flag = 'Y'
75 and wda.parent_delivery_detail_id IS NULL
76 and wda.delivery_id IS NOT NULL
77 and wda.delivery_id = l_delivery_id
78 MINUS
79 SELECT wdai.delivery_detail_id
80 FROM wsh_del_assgn_interface wdai,
81 wsh_del_details_interface wddi
82 where wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
83 and wddi.container_flag = 'Y'
84 and wdai.parent_delivery_detail_id IS NULL
85 and wdai.delivery_interface_id IS NOT NULL
86 and wdai.delivery_interface_id = l_delivery_interface_id
87 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
88 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
89
90 CURSOR cont_contents_cur(l_del_detail_id NUMBER) IS
91 SELECT wda.delivery_detail_id
92 FROM wsh_delivery_assignments_v wda
93 WHERE parent_delivery_detail_id IS NOT NULL
94 START WITH wda.delivery_detail_id = l_del_detail_id
95 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
96
97 CURSOR det_qtys_cur(l_del_detail_id NUMBER) IS
98 SELECT requested_quantity, picked_quantity, container_flag
99 FROM wsh_delivery_details
100 WHERE delivery_detail_id = l_del_detail_id;
101
102 --Bug 3346237:ENFORCE SHIP METHOD NOT ENFORCED WHILE SHIP CONFIRMING
103 --Created new cursor get_defer_interface_info to get defer_interface
104 --value from global parameters table.
105
106
107 --SA Trip Consolidation heali
108
109 CURSOR get_inbd_trip_info (p_organization_id NUMBER) IS
110 SELECT nvl(IGNORE_INBOUND_TRIP,'N')
111 FROM WSH_SHIPPING_PARAMETERS
112 WHERE organization_id = p_organization_id;
113
114 CURSOR get_defer_interface_info IS
115 SELECT nvl(defer_interface,'N')
116 FROM WSH_GLOBAL_PARAMETERS;
117
118
119 l_ignore_inbd_trip varchar2(1);
120 l_autocreate_trip_flag varchar2(1);
121 l_defer_interface_flag varchar2(1);
122 l_del_orgaization_id NUMBER;
123
124 -- l_dummy_line_ids WSH_UTIL_CORE.Id_Tab_Type;
125
126 --exceptions
127 process_delivery_failed exception;
128 create_update_trip_failed exception;
129 validate_delivery_failed exception;
130 ship_confirm_failed exception;
131 update_dlvy_status_failed exception;
132 invalid_input exception;
133 get_warehouse_type_failed exception;
134 others_update_dlvy_det exception;
135 others_create_update_trip exception;
136 others_ship_confirm exception;
137
138 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
139 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
140 --
141 l_debug_on BOOLEAN;
142 --
143 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_SHIP_ADVICE';
144 --
145 BEGIN
146 --
147 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
148 --
149 IF l_debug_on IS NULL
150 THEN
151 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
152 END IF;
153 --
154 IF l_debug_on THEN
155 wsh_debug_sv.push(l_module_name);
156 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
157 wsh_debug_sv.log (l_module_name, 'Event Key', p_event_key);
158 END IF;
159
160 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
161
162 IF(p_delivery_interface_id IS NULL) THEN
163 raise invalid_input;
164 END IF;
165
166 OPEN del_id;
167 FETCH del_id INTO l_delivery_id, l_delivery_name,l_del_orgaization_id;
168 CLOSE del_id;
169
170 IF l_debug_on THEN
171 wsh_debug_sv.log (l_module_name, 'Delivery ID', l_delivery_id);
172 END IF;
173
174 --SA Trip Consolidation heali
175 OPEN get_inbd_trip_info(l_del_orgaization_id);
176 FETCH get_inbd_trip_info INTO l_ignore_inbd_trip;
177 CLOSE get_inbd_trip_info;
178 --SA Trip Consolidation heali
179
180 IF l_debug_on THEN
181 wsh_debug_sv.log (l_module_name, 'l_ignore_inbd_trip',l_ignore_inbd_trip);
182 END IF;
183
184 ----Bug 3346237:ENFORCE SHIP METHOD NOT ENFORCED WHILE SHIP CONFIRMING
185 OPEN get_defer_interface_info;
186 FETCH get_defer_interface_info INTO l_defer_interface_flag;
187 CLOSE get_defer_interface_info;
188
189 IF l_debug_on THEN
190 wsh_debug_sv.log (l_module_name, 'l_defer_interface_flag',l_defer_interface_flag);
191 END IF;
192
193 -- check for warehouse type
194 l_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(
195 -- TPW - Distributed changes
196 P_Organization_ID => l_del_orgaization_id,
197 P_Event_Key => p_event_key,
198 X_Return_Status => l_return_status);
199
200 IF l_debug_on THEN
201 wsh_debug_sv.log (l_module_name, 'Return status from get warehouse type', l_return_status);
202 wsh_debug_sv.log (l_module_name, 'Warehouse type ', l_warehouse_type);
203 END IF;
204
205 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
206 raise get_warehouse_type_failed;
207 END IF;
208
209 G_WAREHOUSE_TYPE := l_warehouse_type;
210
211
212 -- Validate the delivery and the delivery details
213 -- This procedure checks if the incoming delivery_id and delivery_detail_ids
214 -- exist in the instance
215 -- For delivery details, only the non-container records would be checked
216 -- Because, for inbound from TPW, there could be new delivery details for containers
217
218 -- TPW - Distributed changes
219 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
220 WSH_INTERFACE_VALIDATIONS_PKG.Validate_Delivery_Details
221 (p_delivery_interface_id => p_delivery_interface_id,
222 x_return_Status => l_return_status);
223 IF l_debug_on THEN
224 wsh_debug_sv.log (l_module_name, 'Return status from Validate Delivery Details', l_return_status);
225 END IF;
226
227 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
228 raise validate_delivery_failed;
229 END IF;
230 ELSE
231 WSH_INTERFACE_VALIDATIONS_PKG.Validate_Deliveries
232 (p_delivery_id => l_delivery_id,
233 x_return_Status => l_return_status);
234 IF l_debug_on THEN
235 wsh_debug_sv.log (l_module_name, 'Return status from Validate Deliveries', l_return_status);
236 END IF;
237
238 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
239 raise validate_delivery_failed;
240 END IF;
241 END IF;
242
243 -- Then compare the base table data with interface
244 -- To see if the critical data are same
245 -- This comparison is needed only for TPW.
246
247 /*IF (nvl(l_warehouse_type, '!') = 'TPW') THEN
248
249 WSH_INTERFACE_VALIDATIONS_PKG.Compare_Ship_Request_Advice
250 (P_Delivery_ID => l_delivery_id,
251 X_Return_Status => l_return_status);
252
253 IF l_debug_on THEN
254 wsh_debug_sv.log (l_module_name, 'Return status from compare procedure', l_return_status);
255 END IF;
256
257 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
258 raise validate_delivery_failed;
259 END IF;
260 END IF; */
261
262 -- TPW - Distributed changes
263 IF (l_delivery_id IS NULL) AND (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') in ('TPW', 'CMS')) THEN
264 raise invalid_input;
265 END IF;
266
267 SAVEPOINT before_update_dlvy_det;
268
269 BEGIN
270
271 -- TPW - Distributed changes
272 IF nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2' THEN
273 WSH_INTERFACE_COMMON_ACTIONS.Delivery_Interface_Wrapper(
274 p_delivery_interface_id => p_delivery_interface_id,
275 p_action_code => 'CREATE',
276 x_delivery_id => l_delivery_id,
277 x_return_status => l_return_status);
278 ELSE
279
280 WSH_INTERFACE_COMMON_ACTIONS.Delivery_Interface_Wrapper(
281 p_delivery_interface_id => p_delivery_interface_id,
282 p_action_code => 'UPDATE',
283 x_delivery_id => l_dummy,
284 x_return_status => l_return_status);
285 END IF;
286
287 IF l_debug_on THEN
288 wsh_debug_sv.log (l_module_name, 'Return status from Dlvy Wrapper', l_return_status);
289 END IF;
290
291 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
292 raise process_delivery_failed;
293 ELSE
294 IF (nvl(l_warehouse_type, '!') = 'CMS') THEN
295 -- If the inbound from CMS does not have a container that was sent in
296 -- the outbound, the delivery details of the container need to be
297 -- backordered. The following code does this check and action.
298
299 -- First get the list of containers that are not received.
300 l_not_recv_count := 0;
301 FOR l_cont_not_recv IN cont_not_recv_cur(l_delivery_id, p_delivery_interface_id) LOOP
302 l_not_recv_count := l_not_recv_count + 1;
303 IF l_debug_on THEN
304 wsh_debug_sv.log (l_module_name, 'Container not received', l_cont_not_recv.delivery_detail_id);
305 END IF;
306 -- For each of these containers, need to drill down and
307 -- backorder the delivery details that are not containers
308 FOR non_cont IN cont_contents_cur(l_cont_not_recv.delivery_detail_id) LOOP
309 IF l_debug_on THEN
310 wsh_debug_sv.log (l_module_name, 'Contents', non_cont.delivery_detail_id);
311 END IF;
312 OPEN det_qtys_cur(non_cont.delivery_detail_id);
313 FETCH det_qtys_cur INTO l_det_rq, l_det_pq, l_cont_flag;
314 CLOSE det_qtys_cur;
315
316 IF(l_cont_flag = 'N') THEN
317 l_bo_rows(l_bo_rows.count + 1) := non_cont.delivery_detail_id;
318 l_req_qtys(l_req_qtys.count + 1) := l_det_rq;
319 l_overpick_qty := LEAST(l_det_rq,
320 NVL(l_det_pq, l_det_rq) - l_det_rq);
321 IF l_debug_on THEN
322 wsh_debug_sv.log (l_module_name, 'Over picked qty', l_overpick_qty);
323 wsh_debug_sv.log (l_module_name, 'Req qty', l_det_rq);
324 wsh_debug_sv.log (l_module_name, 'Picked qty', l_det_pq);
325 END IF;
326 l_overpick_qtys(l_overpick_qtys.count + 1) := l_overpick_qty;
327 l_bo_qtys(l_bo_qtys.count + 1) := l_det_rq - l_overpick_qty;
328 END IF;
329
330 END LOOP;
331 END LOOP;
332
333 IF l_debug_on THEN
334 wsh_debug_sv.log (l_module_name, 'Rows count ', l_bo_rows.count);
335 wsh_debug_sv.log (l_module_name, 'Not received count', l_not_recv_count);
336 END IF;
337 IF(l_not_recv_count > 0 AND l_bo_rows.count > 0) THEN
338 wsh_ship_confirm_actions2.Backorder(
339 p_detail_ids => l_bo_rows ,
340 p_bo_qtys => l_bo_qtys ,
341 p_req_qtys => l_req_qtys ,
342 p_bo_qtys2 => l_bo_qtys2 ,
343 p_overpick_qtys => l_overpick_qtys ,
344 p_overpick_qtys2 => l_overpick_qtys2 ,
345 p_bo_mode => 'UNRESERVE',
346 x_out_rows => l_cc_ids ,
347 x_return_status => l_return_status);
348 IF l_debug_on THEN
349 wsh_debug_sv.log (l_module_name, 'Return status after backorder', l_return_status);
350 END IF;
351 END IF; -- if l_not_recv_count
352 END IF; -- if l_warehouse_type = cms
353
354 END IF; -- if l_return_status<>success
355
356 EXCEPTION
357 WHEN process_delivery_failed THEN
358 RAISE process_delivery_failed;
359 WHEN others THEN
360 RAISE others_update_dlvy_det;
361 END;
362
363 -- need to process trip and stops
364 BEGIN
365
366 IF l_ignore_inbd_trip='N' THEN --{
367 --SA Trip Consolidation heali
368
369 WSH_INTERFACE_COMMON_ACTIONS.Create_Update_Trip_For_Dlvy(
370 p_delivery_id =>l_delivery_id,
371 x_pickup_stop_id => l_pickup_stop_id,
372 x_dropoff_stop_id => l_dropoff_stop_id,
373 x_trip_id => l_trip_id,
374 x_return_status => l_return_status);
375
376 IF l_debug_on THEN
377 wsh_debug_sv.log (l_module_name, 'Return status from create-update-trip', l_return_status);
378 END IF;
379
380 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
381 raise create_update_trip_failed;
382 ELSE
383 -- Return status success
384
385 -- TPW - Distributed changes
386 open c_get_del_name(l_delivery_id);
387 fetch c_get_del_name into l_delivery_name;
388 close c_get_del_name;
389
390 IF l_debug_on THEN
391 wsh_debug_sv.log (l_module_name, 'Delivery Name', l_delivery_name);
392 END IF;
393
394 -- 1. Need to update the txn. history record to success
395 -- Because all data(dlvy, details, trip, stop) have been successfully moved to base tables
396 UPDATE wsh_transactions_history
397 SET transaction_status = 'SC',
398 entity_number = l_delivery_name,
399 entity_type = 'DLVY'
400 WHERE entity_type = 'DLVY_INT'
401 AND entity_number = to_char(p_delivery_interface_id)
402 AND document_type = 'SA';
403
404
405 IF (nvl(l_warehouse_type, '!') = 'CMS') THEN
406 -- If there is atleast one container that is not received from CMS,
407 -- we backorder the contents of that container
408 -- Ship confirm will try to unassign those backordered lines from the delivery
409 -- But since the delivery is planned , the unassign api will fail.
410 -- Hence we need to UNPLAN the delivery in such cases.
411
412 IF l_debug_on THEN
413 wsh_debug_sv.log (l_module_name, 'Contnr not received count', l_not_recv_count);
414 END IF;
415
416 IF(l_not_recv_count > 0) THEN
417 l_unplan_del_rows(1) := l_delivery_id;
418 WSH_NEW_DELIVERY_ACTIONS.Unplan(
419 p_del_rows => l_unplan_del_rows,
420 x_return_status => l_return_status);
421 IF l_debug_on THEN
422 wsh_debug_sv.log (l_module_name, 'Return status after Unplan', l_return_status);
423 END IF;
424 END IF;
425 END IF; -- if l_warehouse_type
426
427 -- 2. Need to delete the records in interface tables
428 WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records(
429 p_delivery_interface_id => p_delivery_interface_id,
430 x_return_status => l_return_status);
431 IF l_debug_on THEN
432 wsh_debug_sv.log(l_module_name,'Return status after delete interface records', l_return_status);
433 END IF;
434 END IF;
435 END IF; --}
436 EXCEPTION
437 WHEN create_update_trip_failed THEN
438 RAISE create_update_trip_failed;
439 WHEN update_dlvy_status_failed THEN
440 RAISE update_dlvy_status_failed;
441 WHEN others THEN
442 RAISE others_create_update_trip;
443 END;
444 l_stop_rows(l_stop_rows.count + 1 ) := l_pickup_stop_id;
445 l_stop_rows(l_stop_rows.count + 1 ) := l_dropoff_stop_id;
446
447
448
449 SAVEPOINT before_ship_confirm;
450 BEGIN
451 l_sc_intransit_flag := 'Y';
452 --Standalone TPW FP changes
453 --for TPW Batch based (TW2), setting the action flag to 'Ship'
454 IF l_warehouse_type IN ('CMS', 'TW2') THEN
455 l_sc_action_flag := 'S';
456 ELSIF l_warehouse_type = 'TPW' THEN
457 l_sc_action_flag := 'B';
458 END IF;
459 l_sc_close_trip_flag := 'Y';
460
461 l_del_rows(1) := l_delivery_id;
462
463 IF l_ignore_inbd_trip = 'N' THEN
464 l_autocreate_trip_flag:= 'Y';
465 ELSE
466 l_autocreate_trip_flag:= 'N';
467 END IF;
468
469 WSH_NEW_DELIVERY_ACTIONS.Confirm_Delivery
470 (p_del_rows => l_del_rows,
471 p_action_flag => l_sc_action_flag,
472 p_intransit_flag => l_sc_intransit_flag,
473 p_close_flag => l_sc_close_trip_flag,
474 p_stage_del_flag => l_sc_stage_del_flag,
475 p_actual_dep_date => l_sc_actual_dep_date,
476 p_report_set_id => l_sc_report_set_id,
477 p_ship_method => l_sc_trip_ship_method,
478 p_bol_flag => l_sc_create_bol_flag,
479 p_mc_bol_flag => l_sc_create_bol_flag,
480 p_defer_interface_flag => l_defer_interface_flag,
481 p_send_945_flag => 'N',
482 p_autocreate_trip_flag => l_autocreate_trip_flag,
483 x_return_status => l_return_status,
484 p_caller => 'WSH_IB');
485
486 wsh_util_core.get_messages('Y', l_msg_data, l_msg_details, l_msg_count);
487 IF l_debug_on THEN
488 wsh_debug_sv.log (l_module_name, 'Return status from ship confirm', l_return_status);
489 wsh_debug_sv.log (l_module_name, 'Ship confirm message count',l_msg_count);
490 wsh_debug_sv.log (l_module_name, 'Ship confirm messages', l_msg_data);
491 wsh_debug_sv.log (l_module_name, 'Ship confirm message details', l_msg_details);
492 END IF;
493
494 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
495 IF(l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
496 -- If Ship confirm completes with warning and if the global variable is set to 'E'
497 -- Then need to raise the exception
498 -- Otherwise, i.e if the global variable is not 'E' then it means the warning
499 -- can be ignored and hence no need to raise the exception
500 IF l_debug_on THEN
501 wsh_debug_sv.log (l_module_name, 'Ship confirm global variable', WSH_NEW_DELIVERY_ACTIONS.g_error_level);
502 END IF;
503 IF(nvl(WSH_NEW_DELIVERY_ACTIONS.g_error_level, '!') = 'E') THEN
504 -- Reset the global variable to null
505 -- And then raise the exception
506 WSH_NEW_DELIVERY_ACTIONS.g_error_level := NULL;
507 raise ship_confirm_failed;
508 END IF;
509 ELSE
510 -- If return status is not warning and not success, then it is
511 -- either error or unexpected error. Hence raise exception.
512 raise ship_confirm_failed;
513 END IF;
514 END IF;
515 EXCEPTION
516 WHEN ship_confirm_failed THEN
517 RAISE ship_confirm_failed;
518 WHEN others THEN
519 RAISE others_ship_confirm;
520 END;
521
522
523 IF l_debug_on THEN
524 wsh_debug_sv.pop(l_module_name);
525 END IF;
526
527 EXCEPTION
528 WHEN invalid_input THEN
529 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
530 IF l_debug_on THEN
531 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
532 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
533 END IF;
534 WHEN validate_delivery_failed THEN
535 FND_MESSAGE.SET_NAME('WSH','WSH_VALIDATE_DLVY_ERROR');
536 FND_MESSAGE.SET_TOKEN('DLVY', l_delivery_id);
537 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
538 IF l_debug_on THEN
539 WSH_DEBUG_SV.logmsg(l_module_name,'validate_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
540 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:validate_delivery_failed');
541 END IF;
542 WHEN process_delivery_failed THEN
543 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
544 ROLLBACK TO before_update_dlvy_det;
545 IF l_debug_on THEN
546 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
547 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_failed');
548 END IF;
549 WHEN create_update_trip_failed THEN
550 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_PROCESS_ERROR');
551 FND_MESSAGE.SET_TOKEN('DLVY',l_delivery_id);
552 ROLLBACK TO before_update_dlvy_det;
553 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.logmsg(l_module_name,'create_update_trip_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_update_trip_failed');
557 END IF;
558 WHEN ship_confirm_failed THEN
559 FND_MESSAGE.SET_NAME('WSH','WSH_TXN_SHIP_CONFIRM_ERROR');
560 FND_MESSAGE.SET_TOKEN('DLVY',l_delivery_id);
561 ROLLBACK TO before_ship_confirm;
562 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
563 --Fulfillment Batch XML Project
564 --When Shipconfirm errors out, the workflow(WSHBATI) should
565 --get closed and the delivery should remain in status 'SA'.
566 --Hence setting the x_return_status is being set to success.
567 IF l_warehouse_type ='TW2' THEN
568 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
569 END IF;
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.logmsg(l_module_name,'ship_confirm_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
572 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:ship_confirm_failed');
573 END IF;
574 WHEN update_dlvy_status_failed THEN
575 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
576 ROLLBACK TO before_update_dlvy_det;
577 IF l_debug_on THEN
578 WSH_DEBUG_SV.logmsg(l_module_name,'update_dlvy_status_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
579 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_dlvy_status_failed');
580 END IF;
581 WHEN get_warehouse_type_failed THEN
582 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
583 IF l_debug_on THEN
584 WSH_DEBUG_SV.logmsg(l_module_name,'get_warehouse_type_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
585 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:get_warehouse_type_failed');
586 END IF;
587 WHEN others_update_dlvy_det THEN
588 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
589 ROLLBACK TO before_update_dlvy_det;
590 IF l_debug_on THEN
591 WSH_DEBUG_SV.logmsg(l_module_name,'others_update_dlvy_det exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
592 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_update_dlvy_det');
593 END IF;
594 WHEN others_create_update_trip THEN
595 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
596 ROLLBACK TO before_update_dlvy_det;
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.logmsg(l_module_name,'others_create_update_trip exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
599 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_create_update_trip');
600 END IF;
601 WHEN others_ship_confirm THEN
602 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
603 --Fulfillment Batch XML Project
604 --When Shipconfirm errors out, the workflow(WSHBATI) should
605 --get closed and the delivery should remain in status 'SA'.
606 --Hence setting the x_return_status is being set to success.
607 IF l_warehouse_type ='TW2' THEN
608 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
609 END IF;
610 ROLLBACK TO before_ship_confirm;
611 IF l_debug_on THEN
612 WSH_DEBUG_SV.logmsg(l_module_name,'others_ship_confirm exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
613 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_ship_confirm');
614 END IF;
615 WHEN OTHERS THEN
616 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
617 IF l_debug_on THEN
618 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
619 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
620 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
621 END IF;
622
623 END Process_Ship_Advice;
624
625
626 END WSH_INBOUND_SHIP_ADVICE_PKG;
[Home] [Help]