[Home] [Help]
PACKAGE BODY: APPS.WSH_PROCESS_INTERFACED_PKG
Source
1 PACKAGE BODY wsh_process_interfaced_pkg AS
2 /* $Header: WSHINPSB.pls 120.4 2008/01/14 15:44:57 skanduku ship $ */
3
4
5 /*=====================================================================================
6
7 PROCEDURE NAME : Process_Inbound
8
9 This Procedure is called from the Workflow to Derive, Validate data in the Interface
10 tables and finally move it to the Base Tables.
11
12 This procedure looks at the Document Type and accordingly calls Ship Request API or
13 Ship Advice API for further processing.
14 =======================================================================================*/
15
16 --
17 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PROCESS_INTERFACED_PKG';
18 --
19 PROCEDURE process_inbound (
20 l_trns_history_rec IN wsh_transactions_history_pkg.txns_history_record_type,
21 x_return_status OUT NOCOPY VARCHAR2
22 )
23 IS
24
25 pragma AUTONOMOUS_TRANSACTION;
26 invalid_doc_direction EXCEPTION;
27 invalid_doc_type EXCEPTION;
28 invalid_action_type EXCEPTION;
29 invalid_entity_type EXCEPTION;
30 l_delivery_interface_id NUMBER;
31 l_return_status VARCHAR2 (1);
32 t_return_status VARCHAR2 (1);
33 l_rs BOOLEAN;
34 oe_debug_dir VARCHAR2(255);
35 oe_debug_file VARCHAR2(255);
36 wsh_debug_file VARCHAR2(255);
37 wsh_debug_dir VARCHAR2(255);
38 x_delivery_id NUMBER;
39
40 --
41 l_debug_on BOOLEAN;
42 --
43 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INBOUND';
44 --
45 -- Following 2 variables are added for bugfix #4070732
46 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
47 l_reset_flags BOOLEAN;
48
49 -- K LPN CONV. rv
50 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
51 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(32767);
54 -- K LPN CONV. rv
55 --k proj bmso
56 l_cancellation_in_progress BOOLEAN := FALSE;
57 CURSOR c_get_cancel_record (v_entity_number VARCHAR2)
58 IS
59 SELECT wth3.transaction_id,
60 wth3.document_type,
61 wth3.document_direction,
62 wth3.document_number,
63 wth3.orig_document_number,
64 wth3.entity_number,
65 wth3.entity_type,
66 wth3.trading_partner_id,
67 wth3.action_type,
68 wth3.transaction_status,
69 wth3.ecx_message_id,
70 wth3.event_name,
71 wth3.event_key ,
72 wth3.item_type,
73 wth3.internal_control_number,
74 wth3.attribute_category,
75 wth3.attribute1,
76 wth3.attribute2,
77 wth3.attribute3,
78 wth3.attribute4,
79 wth3.attribute5,
80 wth3.attribute6,
81 wth3.attribute7,
82 wth3.attribute8,
83 wth3.attribute9,
84 wth3.attribute10,
85 wth3.attribute11,
86 wth3.attribute12,
87 wth3.attribute13,
88 wth3.attribute14,
89 wth3.attribute15
90 FROM wsh_transactions_history wth1,
91 wsh_transactions_history wth2,
92 wsh_transactions_history wth3
93 WHERE
94 wth2.entity_number = v_entity_number
95 AND wth2.document_direction = 'I'
96 AND wth2.document_type = 'SA'
97 AND wth1.event_key = wth2.event_key
98 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
99 AND wth1.action_type = 'A'
100 and wth1.document_direction = 'O'
101 and wth1.document_type = 'SR'
102 AND wth1.entity_type = 'DLVY'
103 AND wth3.entity_number = wth1.entity_number
104 AND wth3.document_type = 'SR'
105 AND wth3.document_direction = 'O'
106 AND wth3.action_type = 'D'
107 ORDER BY wth1.transaction_id DESC;
108
109 l_cancel_hist_record c_get_cancel_record%ROWTYPE;
110 BEGIN
111
112 -- Bugfix 4070732
113 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
114 THEN
115 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
116 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
117 END IF;
118
119 -- End of Code Bugfix 4070732
120 --
121 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
122 --
123 IF l_debug_on IS NULL
124 THEN
125 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
126 END IF;
127 --
128 wsh_debug_sv.start_debug (l_trns_history_rec.entity_number);
129
130 /*
131 wsh_debug_sv.start_other_app_debug(
132 p_application => 'OE',
133 x_debug_directory => oe_debug_dir,
134 x_debug_file => oe_debug_file,
135 x_return_status => l_return_status);
136
137 wsh_debug_file := WSH_DEBUG_SV.G_FILE;
138 wsh_debug_dir := WSH_DEBUG_SV.G_DIR;
139 */
140 IF l_debug_on THEN
141 wsh_debug_sv.push (l_module_name, 'PROCESS_INBOUND');
142 wsh_debug_sv.log (l_module_name,'DOCUMENT NUMBER',l_trns_history_rec.document_number);
143 wsh_debug_sv.log (l_module_name,'DOC TYPE',l_trns_history_rec.document_type);
144 wsh_debug_sv.log (l_module_name,'ACTION TYPE',l_trns_history_rec.action_type);
145 wsh_debug_sv.log (l_module_name,'ENTITY NUMBER',l_trns_history_rec.entity_number);
146 wsh_debug_sv.log (l_module_name,'ENTITY TYPE',l_trns_history_rec.entity_type);
147 wsh_debug_sv.log (l_module_name,'DIRECTION',l_trns_history_rec.document_direction);
148 wsh_debug_sv.log (l_module_name,'TRADING PARTNER',l_trns_history_rec.trading_partner_id);
149 wsh_debug_sv.log (l_module_name,'ORIG DOC NUMBER',l_trns_history_rec.orig_document_number);
150 END IF;
151
152 -- bug 2393138
153 wsh_delivery_util.g_inbound_flag := TRUE;
154 -- bug 2393138
155
156 -- { frontport of 4317121
157 --Deleting these cache tables to avoid session based problems
158 WSH_INTERFACE_COMMON_ACTIONS.G_Update_Attributes_Tab.delete;
159 WSH_INTERFACE_COMMON_ACTIONS.G_Packing_Detail_Tab.delete;
160 WSH_INTERFACE_COMMON_ACTIONS.G_SERIAL_RANGE_TAB.delete;
161 -- } frontport of 4317121
162
163 IF (l_trns_history_rec.document_number IS NOT NULL)
164 AND (l_trns_history_rec.document_type IS NOT NULL)
165 AND (l_trns_history_rec.action_type IS NOT NULL)
166 AND (l_trns_history_rec.entity_number IS NOT NULL)
167 AND (l_trns_history_rec.entity_type IS NOT NULL)
168 AND (l_trns_history_rec.document_direction IS NOT NULL)
169 AND (l_trns_history_rec.trading_partner_id IS NOT NULL)
170 THEN
171
172 IF l_debug_on THEN
173 wsh_debug_sv.log (l_module_name,'Parameters Not Null');
174 END IF;
175
176 IF (l_trns_history_rec.document_direction NOT IN ('I'))
177 THEN
178 RAISE invalid_doc_direction;
179 END IF;
180
181 IF (l_trns_history_rec.document_type NOT IN ('SR', 'SA'))
182 THEN
183 RAISE invalid_doc_type;
184 END IF;
185
186 IF (l_trns_history_rec.action_type NOT IN ('A', 'D'))
187 THEN
188 RAISE invalid_action_type;
189 END IF;
190
191 IF (l_trns_history_rec.entity_type NOT IN ('DLVY', 'DLVY_INT'))
192 THEN
193 RAISE invalid_entity_type;
194 END IF;
195
196 IF l_debug_on THEN
197 wsh_debug_sv.log (l_module_name,'Valid Parameters');
198 END IF;
199
200 IF (l_trns_history_rec.entity_type = 'DLVY_INT')
201 THEN
202
203 l_delivery_interface_id := to_number(l_trns_history_rec.entity_number);
204
205 Derive_ids (l_delivery_interface_id,l_trns_history_rec.document_type,l_return_status);
206
207 IF l_debug_on THEN
208 wsh_debug_sv.log (l_module_name,'After Calling Derive IDS l_return_status',l_return_status);
209 END IF;
210
211 IF (l_return_status = wsh_util_core.g_ret_sts_success) THEN
212
213 IF (l_trns_history_rec.document_type = 'SR') THEN
214
215 wsh_inbound_ship_request_pkg.process_ship_request (
216 l_trns_history_rec.item_type,
217 l_trns_history_rec.event_key,
218 l_trns_history_rec.action_type,
219 l_delivery_interface_id,
220 x_delivery_id,
221 t_return_status
222 );
223
224 IF l_debug_on THEN
225 wsh_debug_sv.log (l_module_name,'l_delivery_interface_id',l_delivery_interface_id);
226 wsh_debug_sv.log (l_module_name,'x_delivery_id',x_delivery_id);
227 wsh_debug_sv.log (l_module_name,'t_return_status',t_return_status);
228 END IF;
229
230 ELSE
231 wsh_inbound_ship_advice_pkg.process_ship_advice (
232 l_delivery_interface_id,
233 l_trns_history_rec.event_key,
234 t_return_status
235 );
236
237 IF l_debug_on THEN
238 wsh_debug_sv.log (l_module_name,'t_return_status',t_return_status);
239 END IF;
240
241 END IF;
242
243 x_return_status := t_return_status;
244
245 IF (t_return_status = wsh_util_core.g_ret_sts_success)
246 THEN --{
247
248 IF (l_trns_history_rec.document_type = 'SA') THEN --{
249 --k proj
250 -- if there is a cancellation workflow instance exist close
251 -- it. This happens when the cacellation is in progress,
252 -- but the user override the cancellation by processing
253 -- the SA from the message correction form.
254
255 IF WSH_TRANSACTIONS_UTIL.branch_cms_tpw_flow
256 (
257 p_event_key => l_trns_history_rec.event_key
258 )
259 THEN --{
260 OPEN c_get_cancel_record(l_trns_history_rec.entity_number);
261 FETCH c_get_cancel_record INTO l_cancel_hist_record;
262 IF c_get_cancel_record%FOUND THEN --{
263 WSH_TRANSACTIONS_UTIL.Check_cancellation_inprogress(
264 p_delivery_name => l_cancel_hist_record.entity_number, x_cancellation_in_progress => l_cancellation_in_progress,
265 x_return_status => l_return_status
266 );
267 IF l_cancellation_in_progress THEN
268 -- Close the cancellation workflow instance
269 l_cancel_hist_record.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
270 WSH_EXTERNAL_INTERFACE_SV.Raise_Event
271 (
272 l_cancel_hist_record, '99', l_Return_Status
273 );
274 END IF;
275 END IF; --}
276 CLOSE c_get_cancel_record;
277 END IF; --}
278 END IF; --}
279
280 IF l_debug_on THEN
281 wsh_debug_sv.log (l_module_name,'Ship Request or Advice Succeeded');
282 END IF;
283
284 -- Update done only for 'SR' because , for 'SA', update
285 -- done in ship_advice_pkg
286 UPDATE wsh_transactions_history
287 SET transaction_status = 'SC',
288 entity_number = x_delivery_id,
289 entity_type = 'DLVY'
290 WHERE entity_type = 'DLVY_INT'
291 AND entity_number = to_char(l_delivery_interface_id)
292 AND document_type = 'SR';
293
294 -- Delete only for 'SR' because for 'SA' delete done in ship_advice_pkg
295 IF(l_trns_history_rec.document_type = 'SR') THEN
296 Delete_Interface_Records(
297 L_Delivery_Interface_ID,
298 X_Return_Status);
299 IF l_debug_on THEN
300 wsh_debug_sv.log (l_module_name, 'Return status after delete interface records', X_Return_Status);
301 END IF;
302 END IF;
303
304 ELSE --}
305 IF l_debug_on THEN
306 wsh_debug_sv.log (l_module_name, 'Ship Request or Advice error');
307 END IF;
308 -- Update done only for 'SR' because , for 'SA', update
309 -- done in ship_advice_pkg
310 UPDATE wsh_transactions_history
311 SET transaction_status = 'ER'
312 WHERE entity_type = 'DLVY_INT'
313 AND entity_number = to_char(l_delivery_interface_id)
314 AND document_type = 'SR';
315 END IF; -- if t_return_status
316
317 --
318 -- K LPN CONV. rv
319 --
320 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
321 THEN
322 --{
323
324 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
325 (
326 p_in_rec => l_lpn_in_sync_comm_rec,
327 x_return_status => l_return_status,
328 x_out_rec => l_lpn_out_sync_comm_rec
329 );
330 --
331 --
332 IF l_debug_on THEN
333 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
334 END IF;
335 --
336 --
337 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
338 RAISE FND_API.G_EXC_ERROR;
339 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 ELSE
342 IF NVL(x_return_status,wsh_util_core.g_ret_sts_success) =
343 wsh_util_core.g_ret_sts_success
344 THEN
345 x_return_status := l_return_status;
346 END IF;
347 END IF;
348 --}
349 END IF;
350 --
351 -- K LPN CONV. rv
352 --
353
354 -- We need this commit so that the savepoints set in
355 -- Process Ship Advice are committed
356 -- Added on Mar 29th
357 --
358 -- Start code for Bugfix 4070732
359 --
360 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
361 --{
362 IF l_debug_on THEN
363 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
364 END IF;
365
366 l_reset_flags := FALSE;
367
368 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
369 x_return_status => l_return_status);
370
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
373 END IF;
374
375 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
376 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
377 RAISE FND_API.G_EXC_ERROR;
378 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381 --}
382 END IF;
383 --
384 -- End of code for Bugfix 4070732
385 --
386
387 COMMIT;
388
389 ELSE
390 x_return_status := wsh_util_core.g_ret_sts_error;
391 -- Derive_ids returned error. Some validation has failed.
392 --Need to update the status to Error. Otherwise, status will continue to 'IP'
393 UPDATE wsh_transactions_history
394 SET transaction_status = 'ER'
395 WHERE entity_type = 'DLVY_INT'
396 AND entity_number = to_char(l_delivery_interface_id)
397 AND document_type IN ('SR', 'SA');
398
399 --
400 -- K LPN CONV. rv
401 --
402 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
403 (
404 p_in_rec => l_lpn_in_sync_comm_rec,
405 x_return_status => l_return_status,
406 x_out_rec => l_lpn_out_sync_comm_rec
407 );
408 --
409 --
410 IF l_debug_on THEN
411 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
412 END IF;
413 --
414 --
415 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
416 RAISE FND_API.G_EXC_ERROR;
417 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 --ELSE
420 --x_return_status := l_return_status;
421 END IF;
422 --
423 -- K LPN CONV. rv
424 --
425 --
426 -- Start code for Bugfix 4070732
427 --
428 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
429 --{
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
432 END IF;
433
434 l_reset_flags := FALSE;
435
436 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
437 x_return_status => l_return_status);
438
439 IF l_debug_on THEN
440 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
441 END IF;
442
443 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
444 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
445 RAISE FND_API.G_EXC_ERROR;
446 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449 --}
450 END IF;
451 --
452 -- End of code for Bugfix 4070732
453 --
454 COMMIT;
455
456 END IF; -- End of IF (L_Return_Status ....
457
458 ELSE -- if Not Null Check failed.
459 x_return_status := wsh_util_core.g_ret_sts_error;
460 END IF; -- Check for Null Values.
461
462 END IF; -- End of (IF Entity_Type = 'DLVY_INT') ...
463
464
465 --bug 4070732
466 --End of the API handling of calls to process_stops_for_load_tender
467 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
468 THEN
469 --{
470 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
471 --{
472 l_reset_flags := TRUE;
473
474 IF l_debug_on THEN
475 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
476 END IF;
477
478 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
479 x_return_status => l_return_status);
480
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
483 END IF;
484
485 IF (
486 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
487 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
488 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
489 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
490 AND x_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
491 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) )
492 )
493 THEN
494 --{
495 x_return_status := l_return_status;
496 --}
497 END IF;
498
499 --}
500 END IF;
501 --}
502 END IF;
503 --bug 4070732
504
505 IF l_debug_on THEN
506 wsh_debug_sv.log (l_module_name,'Return Status',X_Return_Status);
507 wsh_debug_sv.pop (l_module_name);
508 END IF;
509
510 --wsh_debug_sv.stop_other_app_debug('OE', l_return_status);
511 wsh_debug_sv.stop_debug;
512 EXCEPTION
513 WHEN FND_API.G_EXC_ERROR THEN
514 x_return_status := FND_API.G_RET_STS_ERROR ;
515 --
516 -- K LPN CONV. rv
517 --
518 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
519 THEN
520 --{
521 IF l_debug_on THEN
522 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
523 END IF;
524
525 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
526 (
527 p_in_rec => l_lpn_in_sync_comm_rec,
528 x_return_status => l_return_status,
529 x_out_rec => l_lpn_out_sync_comm_rec
530 );
531 --
532 --
533 IF l_debug_on THEN
534 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
535 END IF;
536 --
537 --
538 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
539 x_return_status := l_return_status;
540 END IF;
541 --
542 --}
543 END IF;
544 --
545 -- K LPN CONV. rv
546 --
547 --
548 -- Start code for Bugfix 4070732
549 --
550 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
551 THEN
552 --{
553 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
554 --{
555 IF l_debug_on THEN
556 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
557 END IF;
558
559 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
560 x_return_status => l_return_status);
561
562 IF l_debug_on THEN
563 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
564 END IF;
565
566 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
567 x_return_status := l_return_status;
568 END IF;
569 --}
570 END IF;
571 --}
572 END IF;
573 --
574 -- End of Code Bugfix 4070732
575 --
576
577 IF l_debug_on THEN
578 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
579 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
580 END IF;
581 --
582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
584 --
585 -- K LPN CONV. rv
586 --
587 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
588 THEN
589 --{
590 IF l_debug_on THEN
591 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
592 END IF;
593
594 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
595 (
596 p_in_rec => l_lpn_in_sync_comm_rec,
597 x_return_status => l_return_status,
598 x_out_rec => l_lpn_out_sync_comm_rec
599 );
600 --
601 --
602 IF l_debug_on THEN
603 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
604 END IF;
605 --
606 --
607 --}
608 END IF;
609 --
610 -- K LPN CONV. rv
611 --
612 --
613 -- Start code for Bugfix 4070732
614 --
615 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
616 THEN
617 --{
618 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
619 --{
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
622 END IF;
623
624 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
625 x_return_status => l_return_status);
626
627 IF l_debug_on THEN
628 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
629 END IF;
630 --}
631 END IF;
632 --}
633 END IF;
634 --
635 -- End of Code Bugfix 4070732
636 --
637 IF l_debug_on THEN
638 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
639 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
640 END IF;
641 --
642 WHEN invalid_doc_direction
643 THEN
644 x_return_status := wsh_util_core.g_ret_sts_error;
645 --
646 -- K LPN CONV. rv
647 --
648 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
649 THEN
650 --{
651 IF l_debug_on THEN
652 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
653 END IF;
654
655 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
656 (
657 p_in_rec => l_lpn_in_sync_comm_rec,
658 x_return_status => l_return_status,
659 x_out_rec => l_lpn_out_sync_comm_rec
660 );
661 --
662 --
663 IF l_debug_on THEN
664 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
665 END IF;
666 --
667 --
668 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
669 x_return_status := l_return_status;
670 END IF;
671 --
672 --}
673 END IF;
674 --
675 -- K LPN CONV. rv
676 --
677
678 -- Start code for Bugfix 4070732
679 --
680 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
681 THEN
682 --{
683 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
684 --{
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
687 END IF;
688
689 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
690 x_return_status => l_return_status);
691
692 IF l_debug_on THEN
693 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
694 END IF;
695
696 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
697 x_return_status := l_return_status;
698 END IF;
699 --}
700 END IF;
701 --}
702 END IF;
703 --
704 -- End of Code Bugfix 4070732
705 --
706
707 IF l_debug_on THEN
708 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_direction exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
709 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_direction');
710 END IF;
711 wsh_debug_sv.stop_debug;
712 WHEN invalid_doc_type
713 THEN
714 x_return_status := wsh_util_core.g_ret_sts_error;
715
716 --
717 -- K LPN CONV. rv
718 --
719 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
720 THEN
721 --{
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
724 END IF;
725
726 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
727 (
728 p_in_rec => l_lpn_in_sync_comm_rec,
729 x_return_status => l_return_status,
730 x_out_rec => l_lpn_out_sync_comm_rec
731 );
732 --
733 --
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
736 END IF;
737 --
738 --
739 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
740 x_return_status := l_return_status;
741 END IF;
742 --
743 --}
744 END IF;
745 --
746 -- K LPN CONV. rv
747 --
748 --
749 -- Start code for Bugfix 4070732
750 --
751 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
752 THEN
753 --{
754 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
755 --{
756 IF l_debug_on THEN
757 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
758 END IF;
759
760 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
761 x_return_status => l_return_status);
762
763 IF l_debug_on THEN
764 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
765 END IF;
766
767 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
768 x_return_status := l_return_status;
769 END IF;
770 --}
771 END IF;
772 --}
773 END IF;
774 --
775 -- End of Code Bugfix 4070732
776 --
777
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
780 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_type');
781 END IF;
782 wsh_debug_sv.stop_debug;
783 WHEN invalid_action_type
784 THEN
785 x_return_status := wsh_util_core.g_ret_sts_error;
786 --
787 -- K LPN CONV. rv
788 --
789 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
790 THEN
791 --{
792 IF l_debug_on THEN
793 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
794 END IF;
795
796 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
797 (
798 p_in_rec => l_lpn_in_sync_comm_rec,
799 x_return_status => l_return_status,
800 x_out_rec => l_lpn_out_sync_comm_rec
801 );
802 --
803 --
804 IF l_debug_on THEN
805 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
806 END IF;
807 --
808 --
809 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
810 x_return_status := l_return_status;
811 END IF;
812 --
813 --}
814 END IF;
815 --
816 -- K LPN CONV. rv
817 --
818
819 --
820 -- Start code for Bugfix 4070732
821 --
822 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
823 THEN
824 --{
825 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
826 --{
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
829 END IF;
830
831 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
832 x_return_status => l_return_status);
833
834 IF l_debug_on THEN
835 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
836 END IF;
837
838 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
839 x_return_status := l_return_status;
840 END IF;
841 --}
842 END IF;
843 --}
844 END IF;
845 --
846 -- End of Code Bugfix 4070732
847 --
848
849 IF l_debug_on THEN
850 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
851 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_type');
852 END IF;
853 wsh_debug_sv.stop_debug;
854 WHEN invalid_entity_type
855 THEN
856 x_return_status := wsh_util_core.g_ret_sts_error;
857
858 --
859 -- K LPN CONV. rv
860 --
861 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
862 THEN
863 --{
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
866 END IF;
867
868 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
869 (
870 p_in_rec => l_lpn_in_sync_comm_rec,
871 x_return_status => l_return_status,
872 x_out_rec => l_lpn_out_sync_comm_rec
873 );
874 --
875 --
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
878 END IF;
879 --
880 --
881 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
882 x_return_status := l_return_status;
883 END IF;
884 --
885 --}
886 END IF;
887 --
888 -- K LPN CONV. rv
889 --
890 --
891 -- Start code for Bugfix 4070732
892 --
893 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
894 THEN
895 --{
896 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
897 --{
898 IF l_debug_on THEN
899 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
900 END IF;
901
902 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
903 x_return_status => l_return_status);
904
905 IF l_debug_on THEN
906 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
907 END IF;
908
909 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
910 x_return_status := l_return_status;
911 END IF;
912 --}
913 END IF;
914 --}
915 END IF;
916 --
917 -- End of Code Bugfix 4070732
918 --
919
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_entity_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
922 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_entity_type');
923 END IF;
924 wsh_debug_sv.stop_debug;
925 WHEN OTHERS
926 THEN
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
928 IF l_debug_on THEN
929 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
930 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
931 END IF;
932 --
933 -- K LPN CONV. rv
934 --
935 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
936 THEN
937 --{
938 IF l_debug_on THEN
939 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
940 END IF;
941
942 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
943 (
944 p_in_rec => l_lpn_in_sync_comm_rec,
945 x_return_status => l_return_status,
946 x_out_rec => l_lpn_out_sync_comm_rec
947 );
948 --
949 --
950 IF l_debug_on THEN
951 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
952 END IF;
953 --
954 --
955 --}
956 END IF;
957 --
958 -- K LPN CONV. rv
959 --
960 --
961 -- Start code for Bugfix 4070732
962 --
963 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
964 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
967 END IF;
968
969 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
970 x_return_status => l_return_status);
971
972
973 IF l_debug_on THEN
974 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
975 END IF;
976
977 END IF;
978 END IF;
979 --
980 -- End of Code Bugfix 4070732
981 --
982
983 IF l_debug_on THEN
984 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
985 END IF;
986 wsh_debug_sv.stop_debug;
987 END process_inbound;
988
989
990 /*=======================================================================================
991
992 PROCEDURE NAME : Derive_IDs
993
994 This Procedure is called from the WSH_PROCESS_INTERFACED_PKG.Process_Inbound Procedure
995 to Derive, Validate data in the Interface tables and update ID columns in interface tables.
996 This procedure includes calls to various APIs available in Shipping.
997
998 Only fields which are not being Derived/validated by any of the Public APIs(which will be
999 called subsequently) are Derived/Validated here.
1000
1001 If all the fields are successfully derived/Validated, the ID columns will be updated in
1002 the Interface tables.
1003 =======================================================================================*/
1004
1005
1006 PROCEDURE derive_ids (
1007 p_delivery_interface_id IN NUMBER,
1008 p_document_type IN VARCHAR2,
1009 x_return_status OUT NOCOPY VARCHAR2
1010 )
1011 IS
1012 CURSOR delivery_cur
1013 IS
1014 SELECT name, organization_code, customer_number,
1015 intmed_ship_to_location_code, initial_pickup_location_code,
1016 ultimate_dropoff_location_code, customer_name
1017 FROM wsh_new_del_interface
1018 WHERE delivery_interface_id = p_delivery_interface_id
1019 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1020 --bug 3920178
1021 CURSOR c_loc_org_check(p_delivery_id NUMBER, p_org_id NUMBER) IS
1022 SELECT 'X'
1023 FROM HZ_CUST_ACCT_SITES_ALL HCAS,
1024 HZ_CUST_SITE_USES_ALL HCSU,
1025 HZ_CUST_ACCOUNTS HCA,
1026 HZ_PARTY_SITES HPS,
1027 WSH_LOCATIONS WL,
1028 WSH_NEW_DELIVERIES WND
1029 WHERE wnd.delivery_id = p_delivery_id
1030 AND wnd.ultimate_dropoff_location_id = wl.wsh_location_id
1031 AND wl.location_source_code = 'HZ'
1032 AND wl.source_location_id = hps.location_id
1033 AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
1034 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
1035 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
1036 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
1037 AND HCSU.STATUS = 'A'
1038 AND HCAS.STATUS = 'A'
1039 AND HCA.STATUS = 'A'
1040 AND HCAS.ORG_ID = HCSU.ORG_ID
1041 AND WND.CUSTOMER_ID= HCAS.cust_account_id
1042 AND HCAS.ORG_ID = p_org_id;
1043 -- removed the NVL from org_id k proj
1044 CURSOR delivery_detail_cur
1045 IS
1046 SELECT wddi.item_number, wddi.customer_item_number, wddi.organization_code,
1047 wddi.ship_from_location_code, wddi.ship_to_location_code,
1048 wddi.deliver_to_location_code, wddi.customer_number, wddi.subinventory,
1049 wddi.revision, wddi.lot_number, wddi.locator_id,
1050 wddi.intmed_ship_to_location_code, wddi.delivery_detail_interface_id,
1051 wddi.customer_name, wddi.container_flag
1052 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1053 WHERE wdai.delivery_interface_id = p_delivery_interface_id
1054 AND wddi.delivery_detail_interface_id =
1055 wdai.delivery_detail_interface_id
1056 AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
1057 AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
1058
1059
1060 CURSOR dlvy_id_cur(l_del_name VARCHAR2) IS
1061 SELECT delivery_id
1062 FROM wsh_new_deliveries
1063 WHERE name=l_del_name;
1064
1065 -- bug 3920178
1066 CURSOR c_org_oper_unit(p_organization_id IN NUMBER) IS
1067 SELECT to_number(org_information3)
1068 FROM hr_organization_information
1069 WHERE organization_id = p_organization_id
1070 AND org_information_context = 'Accounting Information';
1071
1072 interface_errors_rec wsh_interface_validations_pkg.interface_errors_rec_type;
1073 l_del_count NUMBER;
1074 l_dlvy_id NUMBER;
1075 l_d_temp_status VARCHAR2 (10) := ' ';
1076 l_dd_temp_status VARCHAR2 (10) := ' ';
1077 l_intmed_ship_to_location_id wsh_new_del_interface.intmed_ship_to_location_id%TYPE;
1078 l_customer_id wsh_new_del_interface.customer_id%TYPE;
1079 l_org_id wsh_new_del_interface.organization_id%TYPE;
1080 l_initial_pickup_location_id wsh_new_del_interface.initial_pickup_location_id%TYPE;
1081 l_ultimate_dropoff_location_id wsh_new_del_interface.ultimate_dropoff_location_id%TYPE;
1082 l_inventory_item_id wsh_del_details_interface.inventory_item_id%TYPE;
1083 l_customer_item_id wsh_del_details_interface.customer_item_number%TYPE;
1084 l_det_org_id wsh_del_details_interface.organization_id%TYPE;
1085 l_ship_from_location_id wsh_del_details_interface.ship_from_location_id%TYPE;
1086 l_ship_to_location_id wsh_del_details_interface.ship_to_location_id%TYPE;
1087 l_det_intmed_shipto wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1088 l_deliver_to_location_id wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1089 l_intpickup_location_id wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1090 l_det_customer_id wsh_del_details_interface.customer_number%TYPE;
1091 x_result BOOLEAN;
1092 l_seg_array fnd_flex_ext.segmentarray;
1093 l_detail_customer_name VARCHAR2(360);
1094 l_dlvy_customer_name VARCHAR2(360);
1095 l_op_unit_id NUMBER;
1096 l_line_op_unit_id NUMBER;
1097
1098 l_ship_to_site_use_id wsh_del_details_interface.ship_to_site_use_id%TYPE;
1099 l_deliver_to_site_use_id wsh_del_details_interface.deliver_to_site_use_id%TYPE;
1100 l_dummy_site_use_id NUMBER;
1101 l_dummy VARCHAR2(10);
1102
1103 invalid_delivery_int_id EXCEPTION;
1104 invalid_delivery_name EXCEPTION;
1105 invalid_customer_name EXCEPTION;
1106 --
1107 l_debug_on BOOLEAN;
1108 --
1109 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DERIVE_IDS';
1110 --
1111 BEGIN
1112 --
1113 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1114 --
1115 IF l_debug_on IS NULL
1116 THEN
1117 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1118 END IF;
1119 --
1120 IF l_debug_on THEN
1121 wsh_debug_sv.push (l_module_name, 'DERIVE_IDS');
1122 wsh_debug_sv.log (l_module_name,'DELIVERY_INTERFACE_ID',p_delivery_interface_id);
1123 END IF;
1124
1125 /* Check if the values passed are valid */
1126
1127 IF (p_delivery_interface_id IS NULL)
1128 THEN
1129 RAISE invalid_delivery_int_id;
1130 END IF;
1131
1132 SELECT COUNT (delivery_interface_id)
1133 INTO l_del_count
1134 FROM wsh_new_del_interface
1135 WHERE delivery_interface_id = p_delivery_interface_id
1136 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1137
1138 IF l_debug_on THEN
1139 wsh_debug_sv.log (l_module_name, 'l_del_count',l_del_count);
1140 END IF;
1141
1142 IF (l_del_count <> 1)
1143 THEN
1144 RAISE invalid_delivery_int_id;
1145 END IF;
1146
1147 FOR delivery_rec IN delivery_cur
1148 LOOP
1149 IF l_debug_on THEN
1150 wsh_debug_sv.log (l_module_name,'Inside Delivery Rec Loop');
1151 wsh_debug_sv.log (l_module_name, 'Delivery Attributes');
1152 wsh_debug_sv.log (l_module_name, 'Delivery Name', delivery_rec.name);
1153 wsh_debug_sv.log (l_module_name, 'Org Code', delivery_rec.organization_code);
1154 wsh_debug_sv.log (l_module_name, 'Initial pickup', delivery_rec.initial_pickup_location_code);
1155 wsh_debug_sv.log (l_module_name, 'Ultimate Dropoff', delivery_rec.ultimate_dropoff_location_code);
1156 wsh_debug_sv.log (l_module_name, 'IntMed ShipTo', delivery_rec.intmed_ship_to_location_code);
1157 wsh_debug_sv.log (l_module_name, 'Customer Name', delivery_rec.customer_name);
1158 END IF;
1159
1160 WSH_UTIL_VALIDATE.Validate_Org (
1161 l_org_id,
1162 delivery_rec.organization_code,
1163 x_return_status);
1164
1165 IF l_debug_on THEN
1166 wsh_debug_sv.log (l_module_name,'Status after validate_org',x_return_status);
1167 END IF;
1168
1169 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1170 THEN
1171 interface_errors_rec.p_token1 := 'Organization_Code';
1172 interface_errors_rec.p_value1 := delivery_rec.organization_code;
1173 l_d_temp_status := 'INVALID';
1174 END IF;
1175 --bug 3920178
1176
1177 OPEN c_org_oper_unit (l_org_id);
1178 FETCH c_org_oper_unit INTO l_op_unit_id;
1179 CLOSE c_org_oper_unit;
1180
1181 IF l_op_unit_id IS NULL THEN
1182 --{
1183 --
1184 IF l_debug_on THEN
1185 wsh_debug_sv.logmsg(l_module_name, 'Error: Location Org match not found');
1186 END IF;
1187 --
1188
1189 interface_errors_rec.p_token1 := 'Operating_unit';
1190 interface_errors_rec.p_value1 := NULL;
1191 l_d_temp_status := 'INVALID';
1192
1193 --}
1194 END IF;
1195
1196 -- derive the delivery id for Shipment Advice inbound
1197 IF(p_document_type = 'SA') THEN
1198 OPEN dlvy_id_cur(delivery_rec.name);
1199 FETCH dlvy_id_cur INTO l_dlvy_id;
1200
1201 IF(dlvy_id_cur%NOTFOUND) THEN
1202 raise invalid_delivery_name;
1203 END IF;
1204
1205 CLOSE dlvy_id_cur;
1206 END IF; -- if p_document_type = SA
1207
1208 -- Logic to handle cases where delivery does not have customer information
1209 -- Select the distinct customer_name from this delivery's delivery details
1210 -- If there is more than one distinct customer_name at the delivery detail level,
1211 -- Raise an exception because that is an invalid case
1212
1213 IF(delivery_rec.customer_name IS NULL) THEN
1214 IF l_debug_on THEN
1215 wsh_debug_sv.log (l_module_name, 'Delivery Rec customer name is null');
1216 END IF;
1217
1218 BEGIN
1219 SELECT DISTINCT customer_name
1220 INTO l_detail_customer_name
1221 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1222 WHERE wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
1223 AND wddi.customer_name IS NOT NULL
1224 AND wdai.delivery_interface_id = p_delivery_interface_id
1225 AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
1226 AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
1227 IF l_debug_on THEN
1228 wsh_debug_sv.log (l_module_name, 'Detail Rec distinct customer name', l_detail_customer_name);
1229 END IF;
1230
1231 IF l_detail_customer_name IS NOT NULL THEN
1232 delivery_rec.customer_name := l_detail_customer_name;
1233 ELSE
1234 raise invalid_customer_name;
1235 END IF;
1236
1237 EXCEPTION
1238 WHEN NO_DATA_FOUND THEN
1239 raise invalid_customer_name;
1240 WHEN TOO_MANY_ROWS THEN
1241 IF l_debug_on THEN
1242 wsh_debug_sv.log (l_module_name, 'Multiple distinct customer_names for details');
1243 END IF;
1244 raise invalid_customer_name;
1245 END;
1246 END IF; -- if delivery_rec.customer_name
1247
1248 -- store the delivery rec customer name for possible use at delivery detail level
1249 l_dlvy_customer_name := delivery_rec.customer_name;
1250
1251 IF(delivery_rec.intmed_ship_to_location_code IS NOT NULL) THEN
1252 IF l_debug_on THEN
1253 wsh_debug_sv.log (l_module_name, 'Organization id for delivery ' || l_org_id);
1254 END IF;
1255
1256 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1257 p_customer_name => delivery_rec.customer_name,
1258 p_location => delivery_rec.intmed_ship_to_location_code,
1259 x_customer_id => l_customer_id,
1260 x_location_id => l_intmed_ship_to_location_id,
1261 x_return_status => x_return_status,
1262 p_site_use_code => 'SHIP_TO',
1263 x_site_use_id => l_dummy_site_use_id);
1264
1265
1266 IF l_debug_on THEN
1267 wsh_debug_sv.log (l_module_name,'Status after Intmed_ship_to_location_code',x_return_status);
1268 END IF;
1269
1270 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1271 interface_errors_rec.p_token2 := 'Intmed_Ship_To_Location_Code';
1272 interface_errors_rec.p_value2 := delivery_rec.intmed_ship_to_location_code;
1273 l_d_temp_status := 'INVALID';
1274 END IF;
1275 END IF;
1276
1277 /*
1278 wsh_util_validate.validate_customer (l_customer_id,delivery_rec.customer_number,x_return_status);
1279
1280 IF l_debug_on THEN
1281 wsh_debug_sv.log (l_module_name,'Status after Validate Customer ',x_return_status);
1282 END IF;
1283
1284 IF (x_return_status = wsh_util_core.g_ret_sts_error)
1285 THEN
1286 interface_errors_rec.p_token3 := 'Customer_Number';
1287 interface_errors_rec.p_value3 := delivery_rec.customer_number;
1288 l_d_temp_status := 'INVALID';
1289 END IF;
1290 */
1291
1292 WSH_UTIL_VALIDATE.Validate_Location(
1293 l_initial_pickup_location_id,
1294 delivery_rec.initial_pickup_location_code,
1295 x_return_status);
1296
1297 IF l_debug_on THEN
1298 wsh_debug_sv.log (l_module_name,'Status after Initial_pickup_location ',x_return_status);
1299 END IF;
1300
1301 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1302 THEN
1303 interface_errors_rec.p_token4 := 'Initial Pickup Location Code';
1304 interface_errors_rec.p_value4 := delivery_rec.initial_pickup_location_code;
1305 l_d_temp_status := 'INVALID';
1306 END IF;
1307
1308 IF l_debug_on THEN
1309 wsh_debug_sv.log (l_module_name, 'Organization id for delivery - 2 ' || l_org_id);
1310 END IF;
1311 IF p_document_type <> 'SA' THEN --{ bug 3920178
1312
1313 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1314 p_customer_name => delivery_rec.customer_name,
1315 p_location => delivery_rec.ultimate_dropoff_location_code,
1316 x_customer_id => l_customer_id,
1317 x_location_id => l_ultimate_dropoff_location_id,
1318 x_return_status => x_return_status,
1319 p_site_use_code => 'SHIP_TO',
1320 x_site_use_id => l_dummy_site_use_id,
1321 p_org_id => l_op_unit_id);
1322
1323 IF l_debug_on THEN
1324 wsh_debug_sv.log (l_module_name,'Status after ultimate_dropoff_location ',x_return_status);
1325 END IF;
1326
1327 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1328 THEN
1329 interface_errors_rec.p_token5 := 'Ultimate Dropoff Location Code';
1330 interface_errors_rec.p_value5 := delivery_rec.ultimate_dropoff_location_code;
1331 l_d_temp_status := 'INVALID';
1332 END IF;
1333 END IF; -- bug 3920178 }
1334
1335 /* Update ID fields in interface table only if all the validations succeeded */
1336
1337 IF l_debug_on THEN
1338 wsh_debug_sv.log (l_module_name, 'l_d_temp_status',l_d_temp_status);
1339 END IF;
1340
1341 IF (l_d_temp_status <> 'INVALID')
1342 THEN
1343 UPDATE wsh_new_del_interface
1344 SET delivery_id = decode(p_document_type, 'SA',l_dlvy_id,delivery_id),
1345 customer_id = l_customer_id,
1346 organization_id = l_org_id,
1347 intmed_ship_to_location_id = l_intmed_ship_to_location_id,
1348 initial_pickup_location_id = l_initial_pickup_location_id,
1349 ultimate_dropoff_location_id = l_ultimate_dropoff_location_id
1350 WHERE delivery_interface_id = p_delivery_interface_id;
1351
1352 UPDATE wsh_del_legs_interface
1353 SET delivery_id = l_dlvy_id
1354 WHERE delivery_interface_id = p_delivery_interface_id;
1355 END IF;
1356
1357 IF (l_d_temp_status = 'INVALID')
1358 THEN
1359 interface_errors_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
1360 interface_errors_rec.p_interface_id := p_delivery_interface_id;
1361 wsh_interface_validations_pkg.log_interface_errors (
1362 p_interface_errors_rec =>interface_errors_rec,
1363 p_api_name =>'WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To',
1364 x_return_status =>x_return_status);
1365 IF l_debug_on THEN
1366 wsh_debug_sv.log (l_module_name, 'log_interface_errors x_return_status',x_return_status);
1367 END IF;
1368 END IF;
1369 END LOOP;
1370
1371
1372 FOR delivery_detail_rec IN delivery_detail_cur
1373 LOOP
1374
1375 -- Validation for Org
1376 WSH_UTIL_VALIDATE.Validate_Org(
1377 l_det_org_id,
1378 delivery_detail_rec.organization_code,
1379 x_return_status);
1380
1381 IF l_debug_on THEN
1382 wsh_debug_sv.log (l_module_name, 'Organization Code' , delivery_detail_rec.organization_code);
1383 wsh_debug_sv.log (l_module_name, 'Derived Org id', l_det_org_id);
1384 wsh_debug_sv.log (l_module_name, 'Status after validate org for detail',x_return_status);
1385 END IF;
1386
1387 IF (x_return_status = wsh_util_core.g_ret_sts_error)
1388 THEN
1389 interface_errors_rec.p_token1 := 'Organization Code';
1390 interface_errors_rec.p_value1 :=delivery_detail_rec.organization_code;
1391 l_dd_temp_status := 'INVALID';
1392 END IF;
1393 --bug 3920178
1394
1395 OPEN c_org_oper_unit (l_det_org_id);
1396 FETCH c_org_oper_unit INTO l_line_op_unit_id;
1397 CLOSE c_org_oper_unit;
1398
1399
1400 IF l_line_op_unit_id IS NULL
1401 THEN
1402 --{
1403 --
1404 IF l_debug_on THEN
1405 wsh_debug_sv.logmsg(l_module_name, 'Error: Location Org match not found');
1406 END IF;
1407 --
1408
1409 interface_errors_rec.p_token1 := 'Operating_unit'; --bmso
1410 interface_errors_rec.p_value1 := NULL;
1411 l_d_temp_status := 'INVALID';
1412
1413 --}
1414 END IF;
1415
1416
1417 -- Validation for Item
1418 WSH_EXTERNAL_INTERFACE_SV.Validate_Item (
1419 p_concatenated_segments => Delivery_Detail_Rec.Item_Number,
1420 p_organization_id =>L_Det_org_ID,
1421 x_inventory_item_id => L_Inventory_Item_ID,
1422 x_return_status =>X_Return_Status);
1423
1424 IF l_debug_on THEN
1425 wsh_debug_sv.log (l_module_name, 'Return status after validate item',X_Return_Status);
1426 wsh_debug_sv.log (l_module_name, 'Item Number', Delivery_Detail_Rec.Item_Number);
1427 wsh_debug_sv.log (l_module_name, 'Derived Item Id', L_Inventory_Item_ID);
1428 END IF;
1429 IF (x_return_status <> WSH_UTIL_CORE.g_ret_sts_success) Then
1430 Interface_Errors_rec.P_token2 := 'Item Number';
1431 Interface_Errors_rec.P_value2 := Delivery_Detail_Rec.Item_Number;
1432 L_DD_Temp_Status := 'INVALID';
1433 End If;
1434
1435 /*
1436 WSH_UTIL_VALIDATE. Validate_Item(L_Customer_item_ID,
1437 Delivery_Detail_Rec.Customer_Item_Number,
1438 L_Det_org_ID,
1439 L_Seg_Array,
1440 X_Return_Status);
1441
1442 IF (x_return_status <> WSH_UTIL_CORE.g_ret_sts_success) Then
1443 Interface_Errors_rec.P_token3 := 'Customer Item Number';
1444 Interface_Errors_rec.P_value3 := Delivery_Detail_Rec.Customer_Item_Number;
1445 L_DD_Temp_Status := 'INVALID';
1446 End If; */
1447
1448 -- Validation for Ship From
1449 WSH_UTIL_VALIDATE.Validate_Location(
1450 l_ship_from_location_id,
1451 delivery_detail_rec.ship_from_location_code,
1452 x_return_status);
1453
1454 IF (x_return_status = wsh_util_core.g_ret_sts_error)
1455 THEN
1456 interface_errors_rec.p_token4 := 'Ship From Location Code';
1457 interface_errors_rec.p_value4 := delivery_detail_rec.ship_from_location_code;
1458 l_dd_temp_status := 'INVALID';
1459 END IF;
1460
1461 -- Logic for delivery detail customer_name
1462 -- If the detail does not have a customer name, get the delivery's customer_name
1463 -- If the delivery customer_name is also null, then raise an exception. Invalid case.
1464 IF(delivery_detail_rec.customer_name IS NULL) THEN
1465 IF(l_dlvy_customer_name IS NOT NULL) THEN
1466 delivery_detail_rec.customer_name := l_dlvy_customer_name;
1467 ELSE
1468 raise invalid_customer_name;
1469 END IF;
1470 END IF;
1471
1472 -- Validation for Ship To
1473 -- Ship To does not at the container level , in the inbound message
1474 -- Hence we need to validate the ship to only for non-containers.
1475 IF (delivery_detail_rec.container_flag = 'N') THEN
1476 IF l_debug_on THEN
1477 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail ',l_det_org_id);
1478 END IF;
1479 IF p_document_type <> 'SA' THEN --{ bug 3920178
1480 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1481 p_customer_name => delivery_detail_rec.customer_name,
1482 p_location => delivery_detail_rec.ship_to_location_code,
1483 x_customer_id => l_det_customer_id,
1484 x_location_id => l_ship_to_location_id,
1485 x_return_status => x_return_status,
1486 p_site_use_code => 'SHIP_TO',
1487 x_site_use_id => l_ship_to_site_use_id);
1488
1489 IF l_debug_on THEN
1490 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1491 END IF;
1492
1493 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1494 interface_errors_rec.p_token5 := 'Ship To Location Code';
1495 interface_errors_rec.p_value5 := delivery_detail_rec.ship_to_location_code;
1496 l_dd_temp_status := 'INVALID';
1497 END IF;
1498 END IF;
1499
1500 -- Validation for Int_Med Ship To
1501 IF(delivery_detail_rec.intmed_ship_to_location_code IS NOT NULL) THEN
1502 IF l_debug_on THEN
1503 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail - 2' ,l_det_org_id);
1504 END IF;
1505 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1506 p_customer_name => delivery_detail_rec.customer_name,
1507 p_location => delivery_detail_rec.intmed_ship_to_location_code,
1508 x_customer_id => l_det_customer_id,
1509 x_location_id => l_det_intmed_shipto,
1510 x_return_status => x_return_status,
1511 p_site_use_code => 'SHIP_TO',
1512 x_site_use_id => l_ship_to_site_use_id);
1513
1514 IF l_debug_on THEN
1515 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1516 END IF;
1517 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1518 interface_errors_rec.p_token6 := 'IntMed ShipTo Location Code';
1519 interface_errors_rec.p_value6 := delivery_detail_rec.intmed_ship_to_location_code;
1520 l_dd_temp_status := 'INVALID';
1521 END IF;
1522 END IF;
1523
1524 -- Validation for Deliver To
1525 IF (delivery_detail_rec.container_flag = 'N') THEN
1526 IF l_debug_on THEN
1527 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail' ,l_det_org_id);
1528 END IF;
1529
1530 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1531 p_customer_name => delivery_detail_rec.customer_name,
1532 p_location => delivery_detail_rec.deliver_to_location_code,
1533 x_customer_id => l_det_customer_id,
1534 x_location_id => l_deliver_to_location_id,
1535 x_return_status => x_return_status,
1536 p_site_use_code => 'DELIVER_TO',
1537 --bug 3960768
1538 --x_site_use_id => l_ship_to_site_use_id);
1539 x_site_use_id => l_deliver_to_site_use_id);
1540
1541 IF l_debug_on THEN
1542 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1543 END IF;
1544
1545 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1546 interface_errors_rec.p_token7 := 'Deliver To Location Code';
1547 interface_errors_rec.p_value7 := delivery_detail_rec.deliver_to_location_code;
1548 l_dd_temp_status := 'INVALID';
1549 END IF;
1550 END IF; --} matches if p_document_type <> SA
1551 END IF;
1552
1553 /*
1554 wsh_util_validate.validate_customer(l_det_customer_id,
1555 delivery_detail_rec.customer_number,
1556 x_return_status);
1557
1558 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1559 THEN
1560 interface_errors_rec.p_token8 := 'Customer Number';
1561 interface_errors_rec.p_value8 := delivery_detail_rec.customer_number;
1562 l_dd_temp_status := 'INVALID';
1563 END IF;
1564 */
1565
1566 /* Call validation APIs for Lot and Subinventory etc which are not validated
1567 in any of the APIs which will be called to populate data into base tables for
1568 a 940 Transaction */
1569
1570 IF (p_document_type = 'SR')
1571 THEN
1572 IF (delivery_detail_rec.subinventory IS NOT NULL)
1573 THEN
1574 wsh_delivery_details_inv.validate_subinventory (p_subinventory => delivery_detail_rec.subinventory,
1575 p_organization_id => l_det_org_id,
1576 p_inventory_item_id => l_inventory_item_id,
1577 x_return_status => x_return_status,
1578 x_result => x_result);
1579
1580 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1581 THEN
1582 interface_errors_rec.p_token9 := 'Subinventory';
1583 interface_errors_rec.p_value9 := delivery_detail_rec.subinventory;
1584 l_dd_temp_status := 'INVALID';
1585 END IF;
1586 END IF;
1587
1588 IF (delivery_detail_rec.revision IS NOT NULL)
1589 THEN
1590 wsh_delivery_details_inv.validate_revision (delivery_detail_rec.revision,
1591 l_det_org_id,
1592 l_inventory_item_id,
1593 x_return_status,
1594 x_result
1595 );
1596
1597 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1598 THEN
1599 interface_errors_rec.p_token10 := 'Revision';
1600 interface_errors_rec.p_value10 := delivery_detail_rec.revision;
1601 l_dd_temp_status := 'INVALID';
1602 END IF;
1603 END IF;
1604
1605 IF (delivery_detail_rec.lot_number IS NOT NULL)
1606 THEN
1607 wsh_delivery_details_inv.validate_lot_number (p_lot_number => delivery_detail_rec.lot_number,
1608 p_organization_id => l_det_org_id,
1609 p_inventory_item_id => l_inventory_item_id,
1610 p_subinventory => delivery_detail_rec.subinventory,
1611 p_revision => delivery_detail_rec.revision,
1612 p_locator_id => delivery_detail_rec.locator_id,
1613 x_return_status => x_return_status,
1614 x_result => x_result);
1615
1616 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1617 THEN
1618 interface_errors_rec.p_token11 := 'Lot Number';
1619 interface_errors_rec.p_value11 := delivery_detail_rec.lot_number;
1620 l_dd_temp_status := 'INVALID';
1621 END IF;
1622 END IF;
1623
1624 END IF; -- End of IF (P_Document_Type ='SR' ....
1625
1626 /* Update ID fields in interface table only if all the validations succeeded */
1627 IF l_debug_on THEN
1628 wsh_debug_sv.log (l_module_name, 'Delivery ID', l_dlvy_id);
1629 wsh_debug_sv.log (l_module_name, 'l_dd_temp_status', l_dd_temp_status);
1630 END IF;
1631
1632 IF (l_dd_temp_status <> 'INVALID')
1633 THEN
1634 UPDATE wsh_del_details_interface
1635 SET inventory_item_id = l_inventory_item_id,
1636 customer_item_id = l_customer_item_id,
1637 organization_id = l_det_org_id,
1638 ship_from_location_id = l_ship_from_location_id,
1639 ship_to_location_id = l_ship_to_location_id,
1640 intmed_ship_to_location_id = l_det_intmed_shipto,
1641 deliver_to_location_id = l_deliver_to_location_id,
1642 customer_id = l_det_customer_id,
1643 ship_to_site_use_id = l_ship_to_site_use_id,
1644 deliver_to_site_use_id = l_deliver_to_site_use_id,
1645 org_id = l_line_op_unit_id,
1646 source_header_id = decode(p_document_type, 'SR', l_dlvy_id, source_header_id)
1647 WHERE delivery_detail_interface_id = delivery_detail_rec.delivery_detail_interface_id;
1648
1649 END IF;
1650
1651 IF (l_dd_temp_status = 'INVALID')
1652 THEN
1653 interface_errors_rec.p_interface_table_name := 'WSH_DEL_DETAILS_INTERFACE';
1654 interface_errors_rec.p_interface_id := delivery_detail_rec.delivery_detail_interface_id;
1655 wsh_interface_validations_pkg.log_interface_errors (
1656 p_interface_errors_rec =>interface_errors_rec,
1657 p_api_name => 'wsh_delivery_details_inv.validate_lot_number',
1658 x_return_status =>x_return_status);
1659 IF l_debug_on THEN
1660 wsh_debug_sv.log (l_module_name, 'log_interface_errors x_return_status', x_return_status);
1661 END IF;
1662 END IF;
1663 END LOOP;
1664
1665 IF (l_dd_temp_status <> 'INVALID') AND (l_d_temp_status <> 'INVALID')
1666 THEN
1667 x_return_status := wsh_util_core.g_ret_sts_success;
1668 ELSE
1669 x_return_status := wsh_util_core.g_ret_sts_error;
1670 END IF;
1671
1672 wsh_debug_sv.pop (l_module_name);
1673 EXCEPTION
1674 WHEN invalid_delivery_name THEN
1675 IF(dlvy_id_cur%ISOPEN) THEN
1676 CLOSE dlvy_id_cur;
1677 END IF;
1678 x_return_status := wsh_util_core.g_ret_sts_error;
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1681 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_name');
1682 END IF;
1683
1684 WHEN invalid_delivery_int_id THEN
1685 x_return_status := wsh_util_core.g_ret_sts_error;
1686 IF l_debug_on THEN
1687 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_int_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1688 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_int_id');
1689 END IF;
1690 WHEN invalid_customer_name THEN
1691 x_return_status := wsh_util_core.g_ret_sts_error;
1692 IF l_debug_on THEN
1693 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_customer_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1694 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_customer_name');
1695 END IF;
1696 WHEN OTHERS THEN
1697 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1698 IF l_debug_on THEN
1699 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1700 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1701 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1702 END IF;
1703 END derive_ids;
1704
1705
1706 /*=======================================================================================
1707
1708 PROCEDURE NAME : Delete_Interface_Records
1709
1710 This Procedure will be used to delete record in the different interface tables, after data
1711 is populated in the base tables ????
1712
1713 =======================================================================================*/
1714
1715 PROCEDURE delete_interface_records (
1716 p_delivery_interface_id IN NUMBER,
1717 x_return_status OUT NOCOPY VARCHAR2
1718 )
1719 IS
1720 l_transaction_status wsh_transactions_history.transaction_status%TYPE;
1721 invalid_delete EXCEPTION;
1722 invalid_delivery EXCEPTION;
1723
1724 CURSOR detail_interface_ids
1725 IS
1726 SELECT delivery_detail_interface_id, del_assgn_interface_id
1727 FROM wsh_del_assgn_interface
1728 WHERE delivery_interface_id = p_delivery_interface_id
1729 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1730
1731 CURSOR ids
1732 IS
1733 SELECT wdli.delivery_leg_interface_id,
1734 wdli.pick_up_stop_interface_id,
1735 wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
1736 FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
1737 WHERE wdli.delivery_interface_id = p_delivery_interface_id
1738 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
1739 AND wdli.INTERFACE_ACTION_CODE = '94X_INBOUND'
1740 AND wtsi.INTERFACE_ACTION_CODE = '94X_INBOUND';
1741
1742 l_del_count NUMBER;
1743 --
1744 l_debug_on BOOLEAN;
1745 --
1746 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_INTERFACE_RECORDS';
1747 --
1748 BEGIN
1749 --
1750 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1751 --
1752 IF l_debug_on IS NULL
1753 THEN
1754 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1755 END IF;
1756 --
1757 IF l_debug_on THEN
1758 wsh_debug_sv.push (l_module_name, 'DELETE INTERFACE RECORDS');
1759 wsh_debug_sv.log ( l_module_name, 'DELIVERY INTERFACE ID', p_delivery_interface_id);
1760 END IF;
1761
1762 SELECT COUNT (*)
1763 INTO l_del_count
1764 FROM wsh_new_del_interface
1765 WHERE delivery_interface_id = p_delivery_interface_id
1766 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1767
1768 IF (l_del_count <> 0)
1769 THEN
1770 FOR ids_rec IN ids
1771 LOOP
1772
1773 DELETE wsh_del_legs_interface
1774 WHERE delivery_leg_interface_id = ids_rec.delivery_leg_interface_id
1775 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1776
1777 DELETE wsh_trip_stops_interface
1778 WHERE stop_interface_id IN
1779 (ids_rec.pick_up_stop_interface_id,
1780 ids_rec.drop_off_stop_interface_id
1781 )
1782 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1783
1784 DELETE wsh_trips_interface
1785 WHERE trip_interface_id = ids_rec.trip_interface_id
1786 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1787 END LOOP; -- End of FOR IDs_Rec IN IDs LOOP
1788
1789 FOR detail_interface_ids_rec IN detail_interface_ids
1790 LOOP
1791 DELETE wsh_del_details_interface
1792 WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
1793 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1794
1795 DELETE wsh_del_assgn_interface
1796 WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
1797 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1798 END LOOP; -- End of FOR Details_Interface_IDs_Rec ...
1799
1800 DELETE wsh_new_del_interface
1801 WHERE delivery_interface_id = p_delivery_interface_id
1802 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1803
1804 x_return_status := wsh_util_core.g_ret_sts_success;
1805 ELSE -- IF Delivery Doesnot exists
1806 RAISE invalid_delivery;
1807 END IF;
1808
1809 wsh_debug_sv.pop (l_module_name);
1810 EXCEPTION
1811 WHEN invalid_delivery
1812 THEN
1813 x_return_status := wsh_util_core.g_ret_sts_error;
1814 IF l_debug_on THEN
1815 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1816 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery');
1817 END IF;
1818 WHEN invalid_delete
1819 THEN
1820 x_return_status := wsh_util_core.g_ret_sts_error;
1821 IF l_debug_on THEN
1822 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delete exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1823 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delete');
1824 END IF;
1825 WHEN OTHERS
1826 THEN
1827 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1828 IF l_debug_on THEN
1829 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1830 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1831 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1832 END IF;
1833 END delete_interface_records;
1834 END wsh_process_interfaced_pkg;