1 PACKAGE BODY WSH_INBOUND_SHIP_ADVICE_PKG as
2 /* $Header: WSHINSAB.pls 120.0.12000000.3 2007/02/26 23:05:21 bsadri 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(3000);
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 CURSOR cont_not_recv_cur(l_delivery_id NUMBER, l_delivery_interface_id NUMBER) IS
65 SELECT wda.delivery_detail_id
66 FROM wsh_delivery_assignments_v wda,
67 wsh_delivery_details wdd
68 where wdd.delivery_detail_id = wda.delivery_detail_id
69 and wdd.container_flag = 'Y'
70 and wda.parent_delivery_detail_id IS NULL
71 and wda.delivery_id IS NOT NULL
72 and wda.delivery_id = l_delivery_id
73 MINUS
74 SELECT wdai.delivery_detail_id
75 FROM wsh_del_assgn_interface wdai,
76 wsh_del_details_interface wddi
77 where wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
78 and wddi.container_flag = 'Y'
79 and wdai.parent_delivery_detail_id IS NULL
80 and wdai.delivery_interface_id IS NOT NULL
81 and wdai.delivery_interface_id = l_delivery_interface_id
82 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
83 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
84
85 CURSOR cont_contents_cur(l_del_detail_id NUMBER) IS
86 SELECT wda.delivery_detail_id
87 FROM wsh_delivery_assignments_v wda
88 WHERE parent_delivery_detail_id IS NOT NULL
89 START WITH wda.delivery_detail_id = l_del_detail_id
90 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
91
92 CURSOR det_qtys_cur(l_del_detail_id NUMBER) IS
93 SELECT requested_quantity, picked_quantity, container_flag
94 FROM wsh_delivery_details
95 WHERE delivery_detail_id = l_del_detail_id;
96
97 --Bug 3346237:ENFORCE SHIP METHOD NOT ENFORCED WHILE SHIP CONFIRMING
98 --Created new cursor get_defer_interface_info to get defer_interface
99 --value from global parameters table.
100
101
102 --SA Trip Consolidation heali
103
104 CURSOR get_inbd_trip_info (p_organization_id NUMBER) IS
105 SELECT nvl(IGNORE_INBOUND_TRIP,'N')
106 FROM WSH_SHIPPING_PARAMETERS
107 WHERE organization_id = p_organization_id;
108
109 CURSOR get_defer_interface_info IS
110 SELECT nvl(defer_interface,'N')
111 FROM WSH_GLOBAL_PARAMETERS;
112
113
114 l_ignore_inbd_trip varchar2(1);
115 l_autocreate_trip_flag varchar2(1);
116 l_defer_interface_flag varchar2(1);
117 l_del_orgaization_id NUMBER;
118
119 -- l_dummy_line_ids WSH_UTIL_CORE.Id_Tab_Type;
120
121 --exceptions
122 process_delivery_failed exception;
123 create_update_trip_failed exception;
124 validate_delivery_failed exception;
125 ship_confirm_failed exception;
126 update_dlvy_status_failed exception;
127 invalid_input exception;
128 get_warehouse_type_failed exception;
129 others_update_dlvy_det exception;
130 others_create_update_trip exception;
131 others_ship_confirm exception;
132
133 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
134 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
135 --
136 l_debug_on BOOLEAN;
137 --
138 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_SHIP_ADVICE';
139 --
140 BEGIN
141 --
142 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
143 --
144 IF l_debug_on IS NULL
145 THEN
146 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
147 END IF;
148 --
149 IF l_debug_on THEN
150 wsh_debug_sv.push(l_module_name);
151 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
152 wsh_debug_sv.log (l_module_name, 'Event Key', p_event_key);
153 END IF;
154
155 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
156
157 IF(p_delivery_interface_id IS NULL) THEN
158 raise invalid_input;
159 END IF;
160
161 OPEN del_id;
162 FETCH del_id INTO l_delivery_id, l_delivery_name,l_del_orgaization_id;
163 CLOSE del_id;
164
165 IF l_debug_on THEN
166 wsh_debug_sv.log (l_module_name, 'Delivery ID', l_delivery_id);
167 END IF;
168
169 IF(l_delivery_id IS NULL) THEN
170 raise invalid_input;
171 END IF;
172
173 --SA Trip Consolidation heali
174 OPEN get_inbd_trip_info(l_del_orgaization_id);
175 FETCH get_inbd_trip_info INTO l_ignore_inbd_trip;
176 CLOSE get_inbd_trip_info;
177 --SA Trip Consolidation heali
178
179 IF l_debug_on THEN
180 wsh_debug_sv.log (l_module_name, 'l_ignore_inbd_trip',l_ignore_inbd_trip);
181 END IF;
182
183 ----Bug 3346237:ENFORCE SHIP METHOD NOT ENFORCED WHILE SHIP CONFIRMING
184 OPEN get_defer_interface_info;
185 FETCH get_defer_interface_info INTO l_defer_interface_flag;
186 CLOSE get_defer_interface_info;
187
188 IF l_debug_on THEN
189 wsh_debug_sv.log (l_module_name, 'l_defer_interface_flag',l_defer_interface_flag);
190 END IF;
191
192 -- Validate the delivery and the delivery details
193 -- This procedure checks if the incoming delivery_id and delivery_detail_ids
194 -- exist in the instance
195 -- For delivery details, only the non-container records would be checked
196 -- Because, for inbound from TPW, there could be new delivery details for containers
197
198 WSH_INTERFACE_VALIDATIONS_PKG.Validate_Deliveries
199 (p_delivery_id => l_delivery_id,
200 x_return_Status => l_return_status);
201
202 IF l_debug_on THEN
203 wsh_debug_sv.log (l_module_name, 'Return status from Validate Deliveries', l_return_status);
204 END IF;
205
206 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
207 raise validate_delivery_failed;
208 END IF;
209
210 -- check for warehouse type
211 l_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(
212 P_Organization_ID => l_organization_id,
213 P_Event_Key => p_event_key,
214 X_Return_Status => l_return_status);
215
216 IF l_debug_on THEN
217 wsh_debug_sv.log (l_module_name, 'Return status from get warehouse type', l_return_status);
218 wsh_debug_sv.log (l_module_name, 'Warehouse type ', l_warehouse_type);
219 END IF;
220
221 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
222 raise get_warehouse_type_failed;
223 END IF;
224
225 G_WAREHOUSE_TYPE := l_warehouse_type;
226 -- Then compare the base table data with interface
227 -- To see if the critical data are same
228 -- This comparison is needed only for TPW.
229
230 /*IF (nvl(l_warehouse_type, '!') = 'TPW') THEN
231
232 WSH_INTERFACE_VALIDATIONS_PKG.Compare_Ship_Request_Advice
233 (P_Delivery_ID => l_delivery_id,
234 X_Return_Status => l_return_status);
235
236 IF l_debug_on THEN
237 wsh_debug_sv.log (l_module_name, 'Return status from compare procedure', l_return_status);
238 END IF;
239
240 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
241 raise validate_delivery_failed;
242 END IF;
243 END IF; */
244
245 UPDATE wsh_transactions_history
246 SET transaction_status = 'ER'
247 WHERE entity_type = 'DLVY_INT'
248 AND entity_number = to_char(p_delivery_interface_id)
249 AND document_type = 'SA';
250
251 SAVEPOINT before_update_dlvy_det;
252
253 BEGIN
254 /* Patchset I: Update of Delivery Status to SA and line status to Y
255 is done before calling the 'delivery_interface_wrapper' API.
256 Reason: Unless the status is updated before hand, various attributes
257 are disabled by the 'get_disabled_list' api, which is called by
258 the group api for create-update of delivery details */
259
260 -- Need to update the delivery status to SA
261 -- 2394893. This change has been made as a part of this bug because of
262 -- the change made in the API Update_Dlvy_Status ( Autonomous Transaction ).
263
264 update wsh_new_deliveries
265 set status_code ='SA'
266 where delivery_id = l_delivery_id
267 and status_code IN ('SR','SC');
268
269 -- Update released status only for TPW because only this needs the update to staged status
270 -- For Carrier Manifesting systems, status would already be staged
271 -- and hence no update is needed for CMS
272
273 IF (nvl(l_warehouse_type, '!') = 'TPW') THEN
274 -- This update is only for transactions from TPW
275
276 UPDATE wsh_delivery_details
277 SET released_status = 'Y'
278 WHERE delivery_detail_id IN (
279 SELECT delivery_detail_id
280 FROM wsh_delivery_assignments_v
281 WHERE delivery_id = l_delivery_id)
282 AND released_status IN ('R', 'B', 'X')
283 AND container_flag = 'N'
284 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project;
285
286 --Raise Event : Pick To Pod Workflow
287 FOR i in l_detail_tab.first .. l_detail_tab.last LOOP
288 IF ( l_detail_tab.exists(i) ) THEN
289 WSH_WF_STD.Raise_Event(
290 p_entity_type => 'Line',
291 p_entity_id => l_detail_tab(i) ,
292 p_event => 'oracle.apps.wsh.line.gen.staged' ,
293 --p_parameters IN wf_parameter_list_t DEFAULT NULL,
294 p_organization_id => l_organization_id,
295 x_return_status => l_wf_rs ) ;
296 --Error Handling to be done in WSH_WF_STD.Raise_Event itself
297 IF l_debug_on THEN
298 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
299 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ',l_detail_tab(i) );
300 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
301 END IF;
302 --Even if raising of event fails the flow continues.
303 --IF (l_wf_rs <>WSH_UTIL_CORE.G_RET_STS_SUCCESS ) then
304 --No Action
305 --END IF;
306 END IF;
307 END LOOP;
308 --Done Raise Event: Pick To Pod Workflow
309 --
310 -- DBI Project
311 -- Update of wsh_delivery_details where requested_quantity/released_status
312 -- are changed, call DBI API after the update.
313 -- This API will also check for DBI Installed or not
314 IF l_debug_on THEN
315 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_detail_tab.count);
316 END IF;
317 WSH_INTEGRATION.DBI_Update_Detail_Log
318 (p_delivery_detail_id_tab => l_detail_tab,
319 p_dml_type => 'UPDATE',
320 x_return_status => l_dbi_rs);
321
322 IF l_debug_on THEN
323 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
324 END IF;
325 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
326 -- just pass this return status to caller API
327 -- this is a pre-defined exception handled in parent EXCEPTIONS block
328 -- x_return_status is set as Unexpected in exceptions handler
329 RAISE others_update_dlvy_det;
330 END IF;
331 -- treat all other return status as Success
332 -- End of Code for DBI Project
333 --
334 END IF;
335
336
337 WSH_INTERFACE_COMMON_ACTIONS.Delivery_Interface_Wrapper(
338 p_delivery_interface_id => p_delivery_interface_id,
339 p_action_code => 'UPDATE',
340 x_delivery_id => l_dummy,
341 x_return_status => l_return_status);
342
343 IF l_debug_on THEN
344 wsh_debug_sv.log (l_module_name, 'Return status from Dlvy Wrapper', l_return_status);
345 END IF;
346
347 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
348 raise process_delivery_failed;
349 ELSE
350 IF (nvl(l_warehouse_type, '!') = 'CMS') THEN
351 -- If the inbound from CMS does not have a container that was sent in
352 -- the outbound, the delivery details of the container need to be
353 -- backordered. The following code does this check and action.
354
355 -- First get the list of containers that are not received.
356 l_not_recv_count := 0;
357 FOR l_cont_not_recv IN cont_not_recv_cur(l_delivery_id, p_delivery_interface_id) LOOP
358 l_not_recv_count := l_not_recv_count + 1;
359 IF l_debug_on THEN
360 wsh_debug_sv.log (l_module_name, 'Container not received', l_cont_not_recv.delivery_detail_id);
361 END IF;
362 -- For each of these containers, need to drill down and
363 -- backorder the delivery details that are not containers
364 FOR non_cont IN cont_contents_cur(l_cont_not_recv.delivery_detail_id) LOOP
365 IF l_debug_on THEN
366 wsh_debug_sv.log (l_module_name, 'Contents', non_cont.delivery_detail_id);
367 END IF;
368 OPEN det_qtys_cur(non_cont.delivery_detail_id);
369 FETCH det_qtys_cur INTO l_det_rq, l_det_pq, l_cont_flag;
370 CLOSE det_qtys_cur;
371
372 IF(l_cont_flag = 'N') THEN
373 l_bo_rows(l_bo_rows.count + 1) := non_cont.delivery_detail_id;
374 l_req_qtys(l_req_qtys.count + 1) := l_det_rq;
375 l_overpick_qty := LEAST(l_det_rq,
376 NVL(l_det_pq, l_det_rq) - l_det_rq);
377 IF l_debug_on THEN
378 wsh_debug_sv.log (l_module_name, 'Over picked qty', l_overpick_qty);
379 wsh_debug_sv.log (l_module_name, 'Req qty', l_det_rq);
380 wsh_debug_sv.log (l_module_name, 'Picked qty', l_det_pq);
381 END IF;
382 l_overpick_qtys(l_overpick_qtys.count + 1) := l_overpick_qty;
383 l_bo_qtys(l_bo_qtys.count + 1) := l_det_rq - l_overpick_qty;
384 END IF;
385
386 END LOOP;
387 END LOOP;
388
389 IF l_debug_on THEN
390 wsh_debug_sv.log (l_module_name, 'Rows count ', l_bo_rows.count);
391 wsh_debug_sv.log (l_module_name, 'Not received count', l_not_recv_count);
392 END IF;
393 IF(l_not_recv_count > 0 AND l_bo_rows.count > 0) THEN
394 wsh_ship_confirm_actions2.Backorder(
395 p_detail_ids => l_bo_rows ,
396 p_bo_qtys => l_bo_qtys ,
397 p_req_qtys => l_req_qtys ,
398 p_bo_qtys2 => l_bo_qtys2 ,
399 p_overpick_qtys => l_overpick_qtys ,
400 p_overpick_qtys2 => l_overpick_qtys2 ,
401 p_bo_mode => 'UNRESERVE',
402 x_out_rows => l_cc_ids ,
403 x_return_status => l_return_status);
404 IF l_debug_on THEN
405 wsh_debug_sv.log (l_module_name, 'Return status after backorder', l_return_status);
406 END IF;
407 END IF; -- if l_not_recv_count
408 END IF; -- if l_warehouse_type = cms
409
410 END IF; -- if l_return_status<>success
411
412 EXCEPTION
413 WHEN process_delivery_failed THEN
414 RAISE process_delivery_failed;
415 WHEN others THEN
416 RAISE others_update_dlvy_det;
417 END;
418
419 -- need to process trip and stops
420 BEGIN
421
422 IF l_ignore_inbd_trip='N' THEN --{
423 --SA Trip Consolidation heali
424
425 WSH_INTERFACE_COMMON_ACTIONS.Create_Update_Trip_For_Dlvy(
426 p_delivery_id =>l_delivery_id,
427 x_pickup_stop_id => l_pickup_stop_id,
428 x_dropoff_stop_id => l_dropoff_stop_id,
429 x_trip_id => l_trip_id,
430 x_return_status => l_return_status);
431
432 IF l_debug_on THEN
433 wsh_debug_sv.log (l_module_name, 'Return status from create-update-trip', l_return_status);
434 END IF;
435
436 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
437 raise create_update_trip_failed;
438 ELSE
439 -- Return status success
440 -- 1. Need to update the txn. history record to success
441 -- Because all data(dlvy, details, trip, stop) have been successfully moved to base tables
442 UPDATE wsh_transactions_history
443 SET transaction_status = 'SC',
444 entity_number = l_delivery_name,
445 entity_type = 'DLVY'
446 WHERE entity_type = 'DLVY_INT'
447 AND entity_number = to_char(p_delivery_interface_id)
448 AND document_type = 'SA';
449
450
451 IF (nvl(l_warehouse_type, '!') = 'CMS') THEN
452 -- If there is atleast one container that is not received from CMS,
453 -- we backorder the contents of that container
454 -- Ship confirm will try to unassign those backordered lines from the delivery
455 -- But since the delivery is planned , the unassign api will fail.
456 -- Hence we need to UNPLAN the delivery in such cases.
457
458 IF l_debug_on THEN
459 wsh_debug_sv.log (l_module_name, 'Contnr not received count', l_not_recv_count);
460 END IF;
461
462 IF(l_not_recv_count > 0) THEN
463 l_unplan_del_rows(1) := l_delivery_id;
464 WSH_NEW_DELIVERY_ACTIONS.Unplan(
465 p_del_rows => l_unplan_del_rows,
466 x_return_status => l_return_status);
467 IF l_debug_on THEN
468 wsh_debug_sv.log (l_module_name, 'Return status after Unplan', l_return_status);
469 END IF;
470 END IF;
471 END IF; -- if l_warehouse_type
472
473 -- 2. Need to delete the records in interface tables
474 WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records(
475 p_delivery_interface_id => p_delivery_interface_id,
476 x_return_status => l_return_status);
477 IF l_debug_on THEN
478 wsh_debug_sv.log(l_module_name,'Return status after delete interface records', l_return_status);
479 END IF;
480 END IF;
481 END IF; --}
482 EXCEPTION
483 WHEN create_update_trip_failed THEN
484 RAISE create_update_trip_failed;
485 WHEN update_dlvy_status_failed THEN
486 RAISE update_dlvy_status_failed;
487 WHEN others THEN
488 RAISE others_create_update_trip;
489 END;
490 l_stop_rows(l_stop_rows.count + 1 ) := l_pickup_stop_id;
491 l_stop_rows(l_stop_rows.count + 1 ) := l_dropoff_stop_id;
492
493
494
495 SAVEPOINT before_ship_confirm;
496 BEGIN
497 l_sc_intransit_flag := 'Y';
498
499 IF l_warehouse_type = 'CMS' THEN
500 l_sc_action_flag := 'S';
501 ELSIF l_warehouse_type = 'TPW' THEN
502 l_sc_action_flag := 'B';
503 END IF;
504 l_sc_close_trip_flag := 'Y';
505
506 l_del_rows(1) := l_delivery_id;
507
508 IF l_ignore_inbd_trip = 'N' THEN
509 l_autocreate_trip_flag:= 'Y';
510 ELSE
511 l_autocreate_trip_flag:= 'N';
512 END IF;
513
514 WSH_NEW_DELIVERY_ACTIONS.Confirm_Delivery
515 (p_del_rows => l_del_rows,
516 p_action_flag => l_sc_action_flag,
517 p_intransit_flag => l_sc_intransit_flag,
518 p_close_flag => l_sc_close_trip_flag,
519 p_stage_del_flag => l_sc_stage_del_flag,
520 p_actual_dep_date => l_sc_actual_dep_date,
521 p_report_set_id => l_sc_report_set_id,
522 p_ship_method => l_sc_trip_ship_method,
523 p_bol_flag => l_sc_create_bol_flag,
524 p_mc_bol_flag => l_sc_create_bol_flag,
525 p_defer_interface_flag => l_defer_interface_flag,
526 p_send_945_flag => 'N',
527 p_autocreate_trip_flag => l_autocreate_trip_flag,
528 x_return_status => l_return_status,
529 p_caller => 'WSH_IB');
530
531 wsh_util_core.get_messages('Y', l_msg_data, l_msg_details, l_msg_count);
532 IF l_debug_on THEN
533 wsh_debug_sv.log (l_module_name, 'Return status from ship confirm', l_return_status);
534 wsh_debug_sv.log (l_module_name, 'Ship confirm message count',l_msg_count);
535 wsh_debug_sv.log (l_module_name, 'Ship confirm messages', l_msg_data);
536 wsh_debug_sv.log (l_module_name, 'Ship confirm message details', l_msg_details);
537 END IF;
538
539 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
540 IF(l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
541 -- If Ship confirm completes with warning and if the global variable is set to 'E'
542 -- Then need to raise the exception
543 -- Otherwise, i.e if the global variable is not 'E' then it means the warning
544 -- can be ignored and hence no need to raise the exception
545 IF l_debug_on THEN
546 wsh_debug_sv.log (l_module_name, 'Ship confirm global variable', WSH_NEW_DELIVERY_ACTIONS.g_error_level);
547 END IF;
548 IF(nvl(WSH_NEW_DELIVERY_ACTIONS.g_error_level, '!') = 'E') THEN
549 -- Reset the global variable to null
550 -- And then raise the exception
551 WSH_NEW_DELIVERY_ACTIONS.g_error_level := NULL;
552 raise ship_confirm_failed;
553 END IF;
554 ELSE
555 -- If return status is not warning and not success, then it is
556 -- either error or unexpected error. Hence raise exception.
557 raise ship_confirm_failed;
558 END IF;
559 END IF;
560 EXCEPTION
561 WHEN ship_confirm_failed THEN
562 RAISE ship_confirm_failed;
563 WHEN others THEN
564 RAISE others_ship_confirm;
565 END;
566
567
568 IF l_debug_on THEN
569 wsh_debug_sv.pop(l_module_name);
570 END IF;
571
572 EXCEPTION
573 WHEN invalid_input THEN
574 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
575 IF l_debug_on THEN
576 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
577 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
578 END IF;
579 WHEN validate_delivery_failed THEN
580 FND_MESSAGE.SET_NAME('WSH','WSH_VALIDATE_DLVY_ERROR');
581 FND_MESSAGE.SET_TOKEN('DLVY', l_delivery_id);
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,'validate_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
585 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:validate_delivery_failed');
586 END IF;
587 WHEN process_delivery_failed THEN
588 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
589 ROLLBACK TO before_update_dlvy_det;
590 IF l_debug_on THEN
591 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
592 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_failed');
593 END IF;
594 WHEN create_update_trip_failed THEN
595 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_PROCESS_ERROR');
596 FND_MESSAGE.SET_TOKEN('DLVY',l_delivery_id);
597 ROLLBACK TO before_update_dlvy_det;
598 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
599 IF l_debug_on THEN
600 WSH_DEBUG_SV.logmsg(l_module_name,'create_update_trip_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
601 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_update_trip_failed');
602 END IF;
603 WHEN ship_confirm_failed THEN
604 FND_MESSAGE.SET_NAME('WSH','WSH_TXN_SHIP_CONFIRM_ERROR');
605 FND_MESSAGE.SET_TOKEN('DLVY',l_delivery_id);
606 ROLLBACK TO before_ship_confirm;
607 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
608 IF l_debug_on THEN
609 WSH_DEBUG_SV.logmsg(l_module_name,'ship_confirm_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
610 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:ship_confirm_failed');
611 END IF;
612 WHEN update_dlvy_status_failed THEN
613 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
614 ROLLBACK TO before_update_dlvy_det;
615 IF l_debug_on THEN
616 WSH_DEBUG_SV.logmsg(l_module_name,'update_dlvy_status_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
617 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_dlvy_status_failed');
618 END IF;
619 WHEN get_warehouse_type_failed THEN
620 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
621 IF l_debug_on THEN
622 WSH_DEBUG_SV.logmsg(l_module_name,'get_warehouse_type_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
623 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:get_warehouse_type_failed');
624 END IF;
625 WHEN others_update_dlvy_det THEN
626 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
627 ROLLBACK TO before_update_dlvy_det;
628 IF l_debug_on THEN
629 WSH_DEBUG_SV.logmsg(l_module_name,'others_update_dlvy_det exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
630 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_update_dlvy_det');
631 END IF;
632 WHEN others_create_update_trip THEN
633 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
634 ROLLBACK TO before_update_dlvy_det;
635 IF l_debug_on THEN
636 WSH_DEBUG_SV.logmsg(l_module_name,'others_create_update_trip exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
637 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_create_update_trip');
638 END IF;
639 WHEN others_ship_confirm THEN
640 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
641 ROLLBACK TO before_ship_confirm;
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.logmsg(l_module_name,'others_ship_confirm exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
644 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_ship_confirm');
645 END IF;
646 WHEN OTHERS THEN
647 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
648 IF l_debug_on THEN
649 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
650 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
652 END IF;
653
654 END Process_Ship_Advice;
655
656
657 END WSH_INBOUND_SHIP_ADVICE_PKG;
[Home] [Help]