[Home] [Help]
PACKAGE BODY: APPS.WSH_PROCESS_INTERFACED_PKG
Source
1 PACKAGE BODY wsh_process_interfaced_pkg AS
2 /* $Header: WSHINPSB.pls 120.11 2011/12/21 10:08: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 --R12.1.1 STANDALONE PROJECT
75 wth3.document_revision,
76 wth3.attribute_category,
77 wth3.attribute1,
78 wth3.attribute2,
79 wth3.attribute3,
80 wth3.attribute4,
81 wth3.attribute5,
82 wth3.attribute6,
83 wth3.attribute7,
84 wth3.attribute8,
85 wth3.attribute9,
86 wth3.attribute10,
87 wth3.attribute11,
88 wth3.attribute12,
89 wth3.attribute13,
90 wth3.attribute14,
91 wth3.attribute15,
92 NULL -- LSP PROJECT : just added for dependency for client_id
93 FROM wsh_transactions_history wth1,
94 wsh_transactions_history wth2,
95 wsh_transactions_history wth3
96 WHERE
97 wth2.entity_number = v_entity_number
98 AND wth2.document_direction = 'I'
99 AND wth2.document_type = 'SA'
100 AND wth1.event_key = wth2.event_key
101 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
102 AND wth1.action_type = 'A'
103 and wth1.document_direction = 'O'
104 and wth1.document_type = 'SR'
105 AND wth1.entity_type = 'DLVY'
106 AND wth3.entity_number = wth1.entity_number
107 AND wth3.document_type = 'SR'
108 AND wth3.document_direction = 'O'
109 AND wth3.action_type = 'D'
110 ORDER BY wth1.transaction_id DESC;
111
112 l_cancel_hist_record c_get_cancel_record%ROWTYPE;
113 BEGIN
114
115 -- Bugfix 4070732
116 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
117 THEN
118 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
119 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
120 END IF;
121
122 -- End of Code Bugfix 4070732
123 --
124 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
125 --
126 IF l_debug_on IS NULL
127 THEN
128 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
129 END IF;
130 --
131 wsh_debug_sv.start_debug (l_trns_history_rec.entity_number);
132
133 /*
134 wsh_debug_sv.start_other_app_debug(
135 p_application => 'OE',
136 x_debug_directory => oe_debug_dir,
137 x_debug_file => oe_debug_file,
138 x_return_status => l_return_status);
139
140 wsh_debug_file := WSH_DEBUG_SV.G_FILE;
141 wsh_debug_dir := WSH_DEBUG_SV.G_DIR;
142 */
143 IF l_debug_on THEN
144 wsh_debug_sv.push (l_module_name, 'PROCESS_INBOUND');
145 wsh_debug_sv.log (l_module_name,'DOCUMENT NUMBER',l_trns_history_rec.document_number);
146 wsh_debug_sv.log (l_module_name,'DOC TYPE',l_trns_history_rec.document_type);
147 wsh_debug_sv.log (l_module_name,'ACTION TYPE',l_trns_history_rec.action_type);
148 wsh_debug_sv.log (l_module_name,'ENTITY NUMBER',l_trns_history_rec.entity_number);
149 wsh_debug_sv.log (l_module_name,'ENTITY TYPE',l_trns_history_rec.entity_type);
150 wsh_debug_sv.log (l_module_name,'DIRECTION',l_trns_history_rec.document_direction);
151 wsh_debug_sv.log (l_module_name,'TRADING PARTNER',l_trns_history_rec.trading_partner_id);
152 wsh_debug_sv.log (l_module_name,'ORIG DOC NUMBER',l_trns_history_rec.orig_document_number);
153 END IF;
154
155 IF (WMS_DEPLOY.WMS_DEPLOYMENT_MODE IN ('D','L')) THEN --{ LSP PROJECT : Consider LSP mode also
156 IF (l_trns_history_rec.document_number IS NOT NULL)
157 AND (l_trns_history_rec.document_revision IS NOT NULL)
158 AND (l_trns_history_rec.document_type IS NOT NULL)
159 AND (l_trns_history_rec.action_type IS NOT NULL)
160 AND (l_trns_history_rec.entity_number IS NOT NULL)
161 AND (l_trns_history_rec.entity_type IS NOT NULL)
162 AND (l_trns_history_rec.document_direction IS NOT NULL)
163 AND (l_trns_history_rec.trading_partner_id IS NOT NULL)
164 THEN
165
166 IF l_debug_on THEN
167 wsh_debug_sv.log (l_module_name,'Parameters Not Null');
168 END IF;
169
170 IF (l_trns_history_rec.document_direction NOT IN ('I'))
171 THEN
172 RAISE invalid_doc_direction;
173 END IF;
174
175 IF (l_trns_history_rec.document_type NOT IN ('SR'))
176 THEN
177 RAISE invalid_doc_type;
178 END IF;
179
180 IF (l_trns_history_rec.action_type NOT IN ('A', 'C', 'D'))
181 THEN
182 RAISE invalid_action_type;
183 END IF;
184
185 IF (l_trns_history_rec.entity_type NOT IN ('DLVY_INT'))
186 THEN
187 RAISE invalid_entity_type;
188 END IF;
189 END IF;
190
191 IF l_debug_on THEN
192 wsh_debug_sv.log (l_module_name,'Valid Parameters');
193 END IF;
194
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request',WSH_DEBUG_SV.C_PROC_LEVEL);
197 END IF;
198
199 WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request(
200 p_transaction_rec => l_trns_history_rec,
201 p_commit_flag => FND_API.G_TRUE,
202 x_return_status => l_return_status);
203
204 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
205 RAISE FND_API.G_EXC_ERROR;
206 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 ELSE
209 IF NVL(x_return_status,wsh_util_core.g_ret_sts_success) =
210 wsh_util_core.g_ret_sts_success
211 THEN
212 x_return_status := l_return_status;
213 END IF;
214 END IF;
215
216 ELSE --} {
217
218 -- bug 2393138
219 wsh_delivery_util.g_inbound_flag := TRUE;
220 -- bug 2393138
221
222 -- { frontport of 4317121
223 --Deleting these cache tables to avoid session based problems
224 WSH_INTERFACE_COMMON_ACTIONS.G_Update_Attributes_Tab.delete;
225 WSH_INTERFACE_COMMON_ACTIONS.G_Packing_Detail_Tab.delete;
226 WSH_INTERFACE_COMMON_ACTIONS.G_SERIAL_RANGE_TAB.delete;
227 -- } frontport of 4317121
228
229 IF (l_trns_history_rec.document_number IS NOT NULL)
230 AND (l_trns_history_rec.document_type IS NOT NULL)
231 AND (l_trns_history_rec.action_type IS NOT NULL)
232 AND (l_trns_history_rec.entity_number IS NOT NULL)
233 AND (l_trns_history_rec.entity_type IS NOT NULL)
234 AND (l_trns_history_rec.document_direction IS NOT NULL)
235 AND (l_trns_history_rec.trading_partner_id IS NOT NULL)
236 THEN
237
238 IF l_debug_on THEN
239 wsh_debug_sv.log (l_module_name,'Parameters Not Null');
240 END IF;
241
242 IF (l_trns_history_rec.document_direction NOT IN ('I'))
243 THEN
244 RAISE invalid_doc_direction;
245 END IF;
246
247 IF (l_trns_history_rec.document_type NOT IN ('SR', 'SA'))
248 THEN
249 RAISE invalid_doc_type;
250 END IF;
251
252 IF (l_trns_history_rec.action_type NOT IN ('A', 'D'))
253 THEN
254 RAISE invalid_action_type;
255 END IF;
256
257 IF (l_trns_history_rec.entity_type NOT IN ('DLVY', 'DLVY_INT'))
258 THEN
259 RAISE invalid_entity_type;
260 END IF;
261
262 IF l_debug_on THEN
263 wsh_debug_sv.log (l_module_name,'Valid Parameters');
264 END IF;
265
266 IF (l_trns_history_rec.entity_type = 'DLVY_INT')
267 THEN
268
269 l_delivery_interface_id := to_number(l_trns_history_rec.entity_number);
270
271 Derive_ids (l_delivery_interface_id,l_trns_history_rec.document_type,l_return_status);
272
273 IF l_debug_on THEN
274 wsh_debug_sv.log (l_module_name,'After Calling Derive IDS l_return_status',l_return_status);
275 END IF;
276
277 IF (l_return_status = wsh_util_core.g_ret_sts_success) THEN
278
279 -- TPW - Distributed changes
280 -- savepoint before_process NOT required since api
281 -- wsh_inbound_ship_advice_pkg.process_ship_advice takes care of
282 -- setting appropriate savepoints.
283
284 IF (l_trns_history_rec.document_type = 'SR') THEN
285
286 wsh_inbound_ship_request_pkg.process_ship_request (
287 l_trns_history_rec.item_type,
288 l_trns_history_rec.event_key,
289 l_trns_history_rec.action_type,
290 l_delivery_interface_id,
291 x_delivery_id,
292 t_return_status
293 );
294
295 IF l_debug_on THEN
296 wsh_debug_sv.log (l_module_name,'l_delivery_interface_id',l_delivery_interface_id);
297 wsh_debug_sv.log (l_module_name,'x_delivery_id',x_delivery_id);
298 wsh_debug_sv.log (l_module_name,'t_return_status',t_return_status);
299 END IF;
300
301 ELSE
302 wsh_inbound_ship_advice_pkg.process_ship_advice (
303 l_delivery_interface_id,
304 l_trns_history_rec.event_key,
305 t_return_status
306 );
307
308 IF l_debug_on THEN
309 wsh_debug_sv.log (l_module_name,'t_return_status',t_return_status);
310 END IF;
311
312 END IF;
313
314 x_return_status := t_return_status;
315
316 IF (t_return_status = wsh_util_core.g_ret_sts_success)
317 THEN --{
318
319 IF (l_trns_history_rec.document_type = 'SA') THEN --{
320 --k proj
321 -- if there is a cancellation workflow instance exist close
322 -- it. This happens when the cacellation is in progress,
323 -- but the user override the cancellation by processing
324 -- the SA from the message correction form.
325
326 IF WSH_TRANSACTIONS_UTIL.branch_cms_tpw_flow
327 (
328 p_event_key => l_trns_history_rec.event_key
329 )
330 THEN --{
331 OPEN c_get_cancel_record(l_trns_history_rec.entity_number);
332 FETCH c_get_cancel_record INTO l_cancel_hist_record;
333 IF c_get_cancel_record%FOUND THEN --{
334 WSH_TRANSACTIONS_UTIL.Check_cancellation_inprogress(
335 p_delivery_name => l_cancel_hist_record.entity_number, x_cancellation_in_progress => l_cancellation_in_progress,
336 x_return_status => l_return_status
337 );
338 IF l_cancellation_in_progress THEN
339 -- Close the cancellation workflow instance
340 l_cancel_hist_record.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
341 WSH_EXTERNAL_INTERFACE_SV.Raise_Event
342 (
343 l_cancel_hist_record, '99', l_Return_Status
344 );
345 END IF;
346 END IF; --}
347 CLOSE c_get_cancel_record;
348 END IF; --}
349 END IF; --}
350
351 IF l_debug_on THEN
352 wsh_debug_sv.log (l_module_name,'Ship Request or Advice Succeeded');
353 END IF;
354
355 -- Update done only for 'SR' because , for 'SA', update
356 -- done in ship_advice_pkg
357
358 UPDATE wsh_transactions_history
359 SET transaction_status = 'SC',
360 entity_number = x_delivery_id,
361 entity_type = 'DLVY'
362 WHERE entity_type = 'DLVY_INT'
363 AND entity_number = to_char(l_delivery_interface_id)
364 AND document_type = 'SR';
365
366 -- Delete only for 'SR' because for 'SA' delete done in ship_advice_pkg
367 IF(l_trns_history_rec.document_type = 'SR') THEN
368 Delete_Interface_Records(
369 L_Delivery_Interface_ID,
370 X_Return_Status);
371 IF l_debug_on THEN
372 wsh_debug_sv.log (l_module_name, 'Return status after delete interface records', X_Return_Status);
373 END IF;
374 END IF;
375
376 ELSE --}
377 IF l_debug_on THEN
378 wsh_debug_sv.log (l_module_name, 'Ship Request or Advice error.');
379 END IF;
380
381 -- Update done only for 'SR' because , for 'SA', update
382 -- done in ship_advice_pkg
383 --Fulfillment Batch XML Project
384 --Moved the code to handle error status in workflow activity, in case the shipment advice handling for batches is carried out through workflows.
385 IF l_trns_history_rec.document_type = 'SA'
386 AND l_trns_history_rec.event_name = 'oracle.apps.wsh.batch.bsai'
387 AND l_trns_history_rec.event_key IS NOT NULL THEN
388 IF l_debug_on THEN
389 wsh_debug_sv.log (l_module_name, 'Ship Advice error.Move to error event in workflow.');
390 END IF;
391 ELSE
392 UPDATE wsh_transactions_history
393 SET transaction_status = 'ER'
394 WHERE entity_type = 'DLVY_INT'
395 AND entity_number = to_char(l_delivery_interface_id)
396 -- TPW - Distributed changes
397 AND document_type in ('SR', 'SA');
398 END IF;
399 END IF; -- if t_return_status
400
401 --
402 -- K LPN CONV. rv
403 --
404 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
405 THEN
406 --{
407
408 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
409 (
410 p_in_rec => l_lpn_in_sync_comm_rec,
411 x_return_status => l_return_status,
412 x_out_rec => l_lpn_out_sync_comm_rec
413 );
414 --
415 --
416 IF l_debug_on THEN
417 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
418 END IF;
419 --
420 --
421 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
422 RAISE FND_API.G_EXC_ERROR;
423 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 ELSE
426 IF NVL(x_return_status,wsh_util_core.g_ret_sts_success) =
427 wsh_util_core.g_ret_sts_success
428 THEN
429 x_return_status := l_return_status;
430 END IF;
431 END IF;
432 --}
433 END IF;
434 --
435 -- K LPN CONV. rv
436 --
437
438 -- We need this commit so that the savepoints set in
439 -- Process Ship Advice are committed
440 -- Added on Mar 29th
441 --
442 -- Start code for Bugfix 4070732
443 --
444 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
445 --{
446 IF l_debug_on THEN
447 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
448 END IF;
449
450 l_reset_flags := FALSE;
451
452 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
453 x_return_status => l_return_status);
454
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
457 END IF;
458
459 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
460 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
461 RAISE FND_API.G_EXC_ERROR;
462 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465 --}
466 END IF;
467 --
468 -- End of code for Bugfix 4070732
469 --
470
471 COMMIT;
472
473 ELSE
474 x_return_status := wsh_util_core.g_ret_sts_error;
475 -- Derive_ids returned error. Some validation has failed.
476 --Need to update the status to Error. Otherwise, status will continue to 'IP'
477 UPDATE wsh_transactions_history
478 SET transaction_status = 'ER'
479 WHERE entity_type = 'DLVY_INT'
480 AND entity_number = to_char(l_delivery_interface_id)
481 AND document_type IN ('SR', 'SA');
482
483 --
484 -- K LPN CONV. rv
485 --
486 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
487 (
488 p_in_rec => l_lpn_in_sync_comm_rec,
489 x_return_status => l_return_status,
490 x_out_rec => l_lpn_out_sync_comm_rec
491 );
492 --
493 --
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
496 END IF;
497 --
498 --
499 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
500 RAISE FND_API.G_EXC_ERROR;
501 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 --ELSE
504 --x_return_status := l_return_status;
505 END IF;
506 --
507 -- K LPN CONV. rv
508 --
509 --
510 -- Start code for Bugfix 4070732
511 --
512 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
513 --{
514 IF l_debug_on THEN
515 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
516 END IF;
517
518 l_reset_flags := FALSE;
519
520 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
521 x_return_status => l_return_status);
522
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
525 END IF;
526
527 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
528 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
529 RAISE FND_API.G_EXC_ERROR;
530 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533 --}
534 END IF;
535 --
536 -- End of code for Bugfix 4070732
537 --
538 COMMIT;
539
540 END IF; -- End of IF (L_Return_Status ....
541
542 ELSE -- if Not Null Check failed.
543 x_return_status := wsh_util_core.g_ret_sts_error;
544 END IF; -- Check for Null Values.
545
546 END IF; -- End of (IF Entity_Type = 'DLVY_INT') ...
547
548 END IF; --} --End of WMS_DEPLOYMENT_MODE
549
550 --bug 4070732
551 --End of the API handling of calls to process_stops_for_load_tender
552 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
553 THEN
554 --{
555 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
556 --{
557 l_reset_flags := TRUE;
558
559 IF l_debug_on THEN
560 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
561 END IF;
562
563 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
564 x_return_status => l_return_status);
565
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
568 END IF;
569
570 IF (
571 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
572 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
573 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
574 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
575 AND x_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
576 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) )
577 )
578 THEN
579 --{
580 x_return_status := l_return_status;
581 --}
582 END IF;
583
584 --}
585 END IF;
586 --}
587 END IF;
588 --bug 4070732
589
590 IF l_debug_on THEN
591 wsh_debug_sv.log (l_module_name,'Return Status',X_Return_Status);
592 wsh_debug_sv.pop (l_module_name);
593 END IF;
594
595 --wsh_debug_sv.stop_other_app_debug('OE', l_return_status);
596 wsh_debug_sv.stop_debug;
597 EXCEPTION
598 WHEN FND_API.G_EXC_ERROR THEN
599 x_return_status := FND_API.G_RET_STS_ERROR ;
600 --
601 -- K LPN CONV. rv
602 --
603 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
604 THEN
605 --{
606 IF l_debug_on THEN
607 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);
608 END IF;
609
610 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
611 (
612 p_in_rec => l_lpn_in_sync_comm_rec,
613 x_return_status => l_return_status,
614 x_out_rec => l_lpn_out_sync_comm_rec
615 );
616 --
617 --
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
620 END IF;
621 --
622 --
623 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
624 x_return_status := l_return_status;
625 END IF;
626 --
627 --}
628 END IF;
629 --
630 -- K LPN CONV. rv
631 --
632 --
633 -- Start code for Bugfix 4070732
634 --
635 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
636 THEN
637 --{
638 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
639 --{
640 IF l_debug_on THEN
641 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
642 END IF;
643
644 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
645 x_return_status => l_return_status);
646
647 IF l_debug_on THEN
648 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
649 END IF;
650
651 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
652 x_return_status := l_return_status;
653 END IF;
654 --}
655 END IF;
656 --}
657 END IF;
658 --
659 -- End of Code Bugfix 4070732
660 --
661
662 IF l_debug_on THEN
663 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
665 END IF;
666 --
667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
669 --
670 -- K LPN CONV. rv
671 --
672 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
673 THEN
674 --{
675 IF l_debug_on THEN
676 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);
677 END IF;
678
679 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
680 (
681 p_in_rec => l_lpn_in_sync_comm_rec,
682 x_return_status => l_return_status,
683 x_out_rec => l_lpn_out_sync_comm_rec
684 );
685 --
686 --
687 IF l_debug_on THEN
688 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
689 END IF;
690 --
691 --
692 --}
693 END IF;
694 --
695 -- K LPN CONV. rv
696 --
697 --
698 -- Start code for Bugfix 4070732
699 --
700 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
701 THEN
702 --{
703 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
704 --{
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
707 END IF;
708
709 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
710 x_return_status => l_return_status);
711
712 IF l_debug_on THEN
713 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
714 END IF;
715 --}
716 END IF;
717 --}
718 END IF;
719 --
720 -- End of Code Bugfix 4070732
721 --
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
724 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
725 END IF;
726 --
727 WHEN invalid_doc_direction
728 THEN
729 x_return_status := wsh_util_core.g_ret_sts_error;
730 --
731 -- K LPN CONV. rv
732 --
733 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
734 THEN
735 --{
736 IF l_debug_on THEN
737 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);
738 END IF;
739
740 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
741 (
742 p_in_rec => l_lpn_in_sync_comm_rec,
743 x_return_status => l_return_status,
744 x_out_rec => l_lpn_out_sync_comm_rec
745 );
746 --
747 --
748 IF l_debug_on THEN
749 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
750 END IF;
751 --
752 --
753 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
754 x_return_status := l_return_status;
755 END IF;
756 --
757 --}
758 END IF;
759 --
760 -- K LPN CONV. rv
761 --
762
763 -- Start code for Bugfix 4070732
764 --
765 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
766 THEN
767 --{
768 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
769 --{
770 IF l_debug_on THEN
771 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
772 END IF;
773
774 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
775 x_return_status => l_return_status);
776
777 IF l_debug_on THEN
778 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
779 END IF;
780
781 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
782 x_return_status := l_return_status;
783 END IF;
784 --}
785 END IF;
786 --}
787 END IF;
788 --
789 -- End of Code Bugfix 4070732
790 --
791
792 IF l_debug_on THEN
793 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_direction exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
794 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_direction');
795 END IF;
796 wsh_debug_sv.stop_debug;
797 WHEN invalid_doc_type
798 THEN
799 x_return_status := wsh_util_core.g_ret_sts_error;
800
801 --
802 -- K LPN CONV. rv
803 --
804 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
805 THEN
806 --{
807 IF l_debug_on THEN
808 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);
809 END IF;
810
811 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
812 (
813 p_in_rec => l_lpn_in_sync_comm_rec,
814 x_return_status => l_return_status,
815 x_out_rec => l_lpn_out_sync_comm_rec
816 );
817 --
818 --
819 IF l_debug_on THEN
820 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
821 END IF;
822 --
823 --
824 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
825 x_return_status := l_return_status;
826 END IF;
827 --
828 --}
829 END IF;
830 --
831 -- K LPN CONV. rv
832 --
833 --
834 -- Start code for Bugfix 4070732
835 --
836 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
837 THEN
838 --{
839 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
840 --{
841 IF l_debug_on THEN
842 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
843 END IF;
844
845 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
846 x_return_status => l_return_status);
847
848 IF l_debug_on THEN
849 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
850 END IF;
851
852 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
853 x_return_status := l_return_status;
854 END IF;
855 --}
856 END IF;
857 --}
858 END IF;
859 --
860 -- End of Code Bugfix 4070732
861 --
862
863 IF l_debug_on THEN
864 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
865 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_type');
866 END IF;
867 wsh_debug_sv.stop_debug;
868 WHEN invalid_action_type
869 THEN
870 x_return_status := wsh_util_core.g_ret_sts_error;
871 --
872 -- K LPN CONV. rv
873 --
874 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
875 THEN
876 --{
877 IF l_debug_on THEN
878 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);
879 END IF;
880
881 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
882 (
883 p_in_rec => l_lpn_in_sync_comm_rec,
884 x_return_status => l_return_status,
885 x_out_rec => l_lpn_out_sync_comm_rec
886 );
887 --
888 --
889 IF l_debug_on THEN
890 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
891 END IF;
892 --
893 --
894 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
895 x_return_status := l_return_status;
896 END IF;
897 --
898 --}
899 END IF;
900 --
901 -- K LPN CONV. rv
902 --
903
904 --
905 -- Start code for Bugfix 4070732
906 --
907 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
908 THEN
909 --{
910 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
911 --{
912 IF l_debug_on THEN
913 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
914 END IF;
915
916 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
917 x_return_status => l_return_status);
918
919 IF l_debug_on THEN
920 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
921 END IF;
922
923 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
924 x_return_status := l_return_status;
925 END IF;
926 --}
927 END IF;
928 --}
929 END IF;
930 --
931 -- End of Code Bugfix 4070732
932 --
933
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
936 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_type');
937 END IF;
938 wsh_debug_sv.stop_debug;
939 WHEN invalid_entity_type
940 THEN
941 x_return_status := wsh_util_core.g_ret_sts_error;
942
943 --
944 -- K LPN CONV. rv
945 --
946 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
947 THEN
948 --{
949 IF l_debug_on THEN
950 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);
951 END IF;
952
953 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
954 (
955 p_in_rec => l_lpn_in_sync_comm_rec,
956 x_return_status => l_return_status,
957 x_out_rec => l_lpn_out_sync_comm_rec
958 );
959 --
960 --
961 IF l_debug_on THEN
962 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
963 END IF;
964 --
965 --
966 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
967 x_return_status := l_return_status;
968 END IF;
969 --
970 --}
971 END IF;
972 --
973 -- K LPN CONV. rv
974 --
975 --
976 -- Start code for Bugfix 4070732
977 --
978 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
979 THEN
980 --{
981 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
982 --{
983 IF l_debug_on THEN
984 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
985 END IF;
986
987 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
988 x_return_status => l_return_status);
989
990 IF l_debug_on THEN
991 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
992 END IF;
993
994 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
995 x_return_status := l_return_status;
996 END IF;
997 --}
998 END IF;
999 --}
1000 END IF;
1001 --
1002 -- End of Code Bugfix 4070732
1003 --
1004
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_entity_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1007 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_entity_type');
1008 END IF;
1009 wsh_debug_sv.stop_debug;
1010 WHEN OTHERS
1011 THEN
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1013 IF l_debug_on THEN
1014 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1015 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1016 END IF;
1017 --
1018 -- K LPN CONV. rv
1019 --
1020 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1021 THEN
1022 --{
1023 IF l_debug_on THEN
1024 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);
1025 END IF;
1026
1027 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1028 (
1029 p_in_rec => l_lpn_in_sync_comm_rec,
1030 x_return_status => l_return_status,
1031 x_out_rec => l_lpn_out_sync_comm_rec
1032 );
1033 --
1034 --
1035 IF l_debug_on THEN
1036 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1037 END IF;
1038 --
1039 --
1040 --}
1041 END IF;
1042 --
1043 -- K LPN CONV. rv
1044 --
1045 --
1046 -- Start code for Bugfix 4070732
1047 --
1048 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1049 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1050 IF l_debug_on THEN
1051 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1052 END IF;
1053
1054 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1055 x_return_status => l_return_status);
1056
1057
1058 IF l_debug_on THEN
1059 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1060 END IF;
1061
1062 END IF;
1063 END IF;
1064 --
1065 -- End of Code Bugfix 4070732
1066 --
1067
1068 IF l_debug_on THEN
1069 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1070 END IF;
1071 wsh_debug_sv.stop_debug;
1072 END process_inbound;
1073
1074
1075 /*=======================================================================================
1076
1077 PROCEDURE NAME : Derive_IDs
1078
1079 This Procedure is called from the WSH_PROCESS_INTERFACED_PKG.Process_Inbound Procedure
1080 to Derive, Validate data in the Interface tables and update ID columns in interface tables.
1081 This procedure includes calls to various APIs available in Shipping.
1082
1083 Only fields which are not being Derived/validated by any of the Public APIs(which will be
1084 called subsequently) are Derived/Validated here.
1085
1086 If all the fields are successfully derived/Validated, the ID columns will be updated in
1087 the Interface tables.
1088 =======================================================================================*/
1089
1090
1091 PROCEDURE derive_ids (
1092 p_delivery_interface_id IN NUMBER,
1093 p_document_type IN VARCHAR2,
1094 x_return_status OUT NOCOPY VARCHAR2
1095 )
1096 IS
1097 CURSOR delivery_cur
1098 IS
1099 SELECT name, organization_code, customer_number,
1100 intmed_ship_to_location_code, initial_pickup_location_code,
1101 ultimate_dropoff_location_code, customer_name,
1102 -- TPW - Distributed changes
1103 ship_to_customer_name,
1104 ship_to_address1, ship_to_address2, ship_to_address3, ship_to_address4,
1105 ship_to_city, ship_to_state, ship_to_postal_code, ship_to_country
1106 FROM wsh_new_del_interface
1107 WHERE delivery_interface_id = p_delivery_interface_id
1108 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1109 --bug 3920178
1110 CURSOR c_loc_org_check(p_delivery_id NUMBER, p_org_id NUMBER) IS
1111 SELECT 'X'
1112 FROM HZ_CUST_ACCT_SITES_ALL HCAS,
1113 HZ_CUST_SITE_USES_ALL HCSU,
1114 HZ_CUST_ACCOUNTS HCA,
1115 HZ_PARTY_SITES HPS,
1116 WSH_LOCATIONS WL,
1117 WSH_NEW_DELIVERIES WND
1118 WHERE wnd.delivery_id = p_delivery_id
1119 AND wnd.ultimate_dropoff_location_id = wl.wsh_location_id
1120 AND wl.location_source_code = 'HZ'
1121 AND wl.source_location_id = hps.location_id
1122 AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
1123 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
1124 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
1125 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
1126 AND HCSU.STATUS = 'A'
1127 AND HCAS.STATUS = 'A'
1128 AND HCA.STATUS = 'A'
1129 AND HCAS.ORG_ID = HCSU.ORG_ID
1130 AND WND.CUSTOMER_ID= HCAS.cust_account_id
1131 AND HCAS.ORG_ID = p_org_id;
1132 -- removed the NVL from org_id k proj
1133 CURSOR delivery_detail_cur
1134 IS
1135 SELECT wddi.item_number, wddi.customer_item_number, wddi.organization_code,
1136 wddi.ship_from_location_code, wddi.ship_to_location_code,
1137 wddi.deliver_to_location_code, wddi.customer_number, wddi.subinventory,
1138 wddi.revision, wddi.lot_number, wddi.locator_id,
1139 wddi.intmed_ship_to_location_code, wddi.delivery_detail_interface_id,
1140 wddi.customer_name, wddi.container_flag,
1141 -- TPW - Distributed changes
1142 wddi.locator_code
1143 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1144 WHERE wdai.delivery_interface_id = p_delivery_interface_id
1145 AND wddi.delivery_detail_interface_id =
1146 wdai.delivery_detail_interface_id
1147 AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
1148 AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
1149
1150
1151 CURSOR dlvy_id_cur(l_del_name VARCHAR2) IS
1152 SELECT delivery_id, ultimate_dropoff_location_id
1153 FROM wsh_new_deliveries
1154 WHERE name=l_del_name;
1155
1156 -- bug 3920178
1157 CURSOR c_org_oper_unit(p_organization_id IN NUMBER) IS
1158 SELECT to_number(org_information3)
1159 FROM hr_organization_information
1160 WHERE organization_id = p_organization_id
1161 AND org_information_context = 'Accounting Information';
1162
1163 --Fulfillment Batch XML Project
1164 CURSOR Get_Inv_Controls (v_inventory_item_id NUMBER,
1165 v_organization_id NUMBER )
1166 IS
1167 SELECT DECODE(msi.location_control_code, 1, 'N', 'Y') loc_control_flag,
1168 msi.restrict_locators_code,
1169 msi.restrict_subinventories_code,
1170 msi.location_control_code,
1171 msi.reservable_type,
1172 msi.MTL_TRANSACTIONS_ENABLED_FLAG -- Bug 3599363
1173 FROM MTL_SYSTEM_ITEMS msi
1174 WHERE msi.inventory_item_id = v_inventory_item_id
1175 AND msi.organization_id = v_organization_id;
1176
1177
1178
1179
1180 --Fulfillment Batch XML Project
1181 l_loc_flag VARCHAR2(3);
1182 l_txn_enabled_flag VARCHAR2(1);
1183 l_reservable_type NUMBER ;
1184
1185 l_location_control_code NUMBER;
1186 l_restrict_loc_code NUMBER;
1187 l_restrict_sub_code NUMBER;
1188
1189 l_inv_item_id NUMBER;
1190 l_sub_loc_code NUMBER;
1191 l_loc_ctl_code NUMBER;
1192 l_org_loc_code NUMBER;
1193
1194 interface_errors_rec wsh_interface_validations_pkg.interface_errors_rec_type;
1195 l_del_count NUMBER;
1196 l_dlvy_id NUMBER;
1197 l_d_temp_status VARCHAR2 (10) := ' ';
1198 l_dd_temp_status VARCHAR2 (10) := ' ';
1199 -- TPW - Distributed changes - New Variable added
1200 l_loc_temp_status VARCHAR2 (10) := ' ';
1201 l_intmed_ship_to_location_id wsh_new_del_interface.intmed_ship_to_location_id%TYPE;
1202 l_customer_id wsh_new_del_interface.customer_id%TYPE;
1203 l_org_id wsh_new_del_interface.organization_id%TYPE;
1204 l_initial_pickup_location_id wsh_new_del_interface.initial_pickup_location_id%TYPE;
1205 l_ultimate_dropoff_location_id wsh_new_del_interface.ultimate_dropoff_location_id%TYPE;
1206 l_inventory_item_id wsh_del_details_interface.inventory_item_id%TYPE;
1207 l_customer_item_id wsh_del_details_interface.customer_item_number%TYPE;
1208 l_det_org_id wsh_del_details_interface.organization_id%TYPE;
1209 l_ship_from_location_id wsh_del_details_interface.ship_from_location_id%TYPE;
1210 l_ship_to_location_id wsh_del_details_interface.ship_to_location_id%TYPE;
1211 l_det_intmed_shipto wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1212 l_deliver_to_location_id wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1213 l_intpickup_location_id wsh_del_details_interface.intmed_ship_to_location_id%TYPE;
1214 l_det_customer_id wsh_del_details_interface.customer_number%TYPE;
1215 x_result BOOLEAN;
1216 l_seg_array fnd_flex_ext.segmentarray;
1217 l_detail_customer_name VARCHAR2(360);
1218 l_dlvy_customer_name VARCHAR2(360);
1219 l_op_unit_id NUMBER;
1220 l_line_op_unit_id NUMBER;
1221
1222 l_ship_to_site_use_id wsh_del_details_interface.ship_to_site_use_id%TYPE;
1223 l_deliver_to_site_use_id wsh_del_details_interface.deliver_to_site_use_id%TYPE;
1224 l_dummy_site_use_id NUMBER;
1225 l_dummy VARCHAR2(10);
1226
1227 -- TPW - Distributed changes
1228 l_ship_to_cust_id number;
1229 l_return_status varchar2(1);
1230 l_warehouse_type varchar2(10);
1231 l_locator_id number;
1232 l_ou_org_id wsh_new_del_interface.organization_id%TYPE;
1233
1234 invalid_delivery_int_id EXCEPTION;
1235 invalid_delivery_name EXCEPTION;
1236 invalid_customer_name EXCEPTION;
1237 invalid_locator EXCEPTION; -- Fulfillment Batch XML Project
1238 -- TPW - Distributed changes
1239 get_warehouse_type_failed EXCEPTION;
1240
1241 --
1242 l_debug_on BOOLEAN;
1243 --
1244 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DERIVE_IDS';
1245 --
1246 BEGIN
1247 --
1248 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1249 --
1250 IF l_debug_on IS NULL
1251 THEN
1252 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1253 END IF;
1254 --
1255 IF l_debug_on THEN
1256 wsh_debug_sv.push (l_module_name, 'DERIVE_IDS');
1257 wsh_debug_sv.log (l_module_name,'DELIVERY_INTERFACE_ID',p_delivery_interface_id);
1258 END IF;
1259
1260 /* Check if the values passed are valid */
1261
1262 IF (p_delivery_interface_id IS NULL)
1263 THEN
1264 RAISE invalid_delivery_int_id;
1265 END IF;
1266
1267 SELECT COUNT (delivery_interface_id)
1268 INTO l_del_count
1269 FROM wsh_new_del_interface
1270 WHERE delivery_interface_id = p_delivery_interface_id
1271 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1272
1273 IF l_debug_on THEN
1274 wsh_debug_sv.log (l_module_name, 'l_del_count',l_del_count);
1275 END IF;
1276
1277 IF (l_del_count <> 1)
1278 THEN
1279 RAISE invalid_delivery_int_id;
1280 END IF;
1281
1282 FOR delivery_rec IN delivery_cur
1283 LOOP
1284 IF l_debug_on THEN
1285 wsh_debug_sv.log (l_module_name,'Inside Delivery Rec Loop');
1286 wsh_debug_sv.log (l_module_name, 'Delivery Attributes');
1287 wsh_debug_sv.log (l_module_name, 'Delivery Name', delivery_rec.name);
1288 wsh_debug_sv.log (l_module_name, 'Org Code', delivery_rec.organization_code);
1289 wsh_debug_sv.log (l_module_name, 'Initial pickup', delivery_rec.initial_pickup_location_code);
1290 wsh_debug_sv.log (l_module_name, 'Ultimate Dropoff', delivery_rec.ultimate_dropoff_location_code);
1291 wsh_debug_sv.log (l_module_name, 'IntMed ShipTo', delivery_rec.intmed_ship_to_location_code);
1292 wsh_debug_sv.log (l_module_name, 'Customer Name', delivery_rec.customer_name);
1293 END IF;
1294
1295 WSH_UTIL_VALIDATE.Validate_Org (
1296 l_org_id,
1297 delivery_rec.organization_code,
1298 x_return_status);
1299
1300 IF l_debug_on THEN
1301 wsh_debug_sv.log (l_module_name,'Status after validate_org',x_return_status);
1302 END IF;
1303
1304 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1305 THEN
1306 interface_errors_rec.p_token1 := 'Organization_Code';
1307 interface_errors_rec.p_value1 := delivery_rec.organization_code;
1308 l_d_temp_status := 'INVALID';
1309 END IF;
1310 --bug 3920178
1311
1312 -- TPW - Distributed changes
1313 -- check for warehouse type
1314 l_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(
1315 P_Organization_ID => l_org_id,
1316 X_Return_Status => l_return_status);
1317
1318 IF l_debug_on THEN
1319 wsh_debug_sv.log (l_module_name, 'Return status from get warehouse type', l_return_status);
1320 wsh_debug_sv.log (l_module_name, 'Warehouse type ', l_warehouse_type);
1321 END IF;
1322
1323 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1324 raise get_warehouse_type_failed;
1325 END IF;
1326
1327
1328 OPEN c_org_oper_unit (l_org_id);
1329 FETCH c_org_oper_unit INTO l_op_unit_id;
1330 CLOSE c_org_oper_unit;
1331
1332 IF l_op_unit_id IS NULL THEN
1333 --{
1334 --
1335 IF l_debug_on THEN
1336 wsh_debug_sv.logmsg(l_module_name, 'Error: Location Org match not found');
1337 END IF;
1338 --
1339
1340 interface_errors_rec.p_token1 := 'Operating_unit';
1341 interface_errors_rec.p_value1 := NULL;
1342 l_d_temp_status := 'INVALID';
1343
1344 --}
1345 END IF;
1346
1347 -- TPW - Distributed changes
1348 WSH_UTIL_VALIDATE.Validate_Location(
1349 l_initial_pickup_location_id,
1350 delivery_rec.initial_pickup_location_code,
1351 x_return_status);
1352
1353 IF l_debug_on THEN
1354 wsh_debug_sv.log (l_module_name,'Status after Initial_pickup_location ',x_return_status);
1355 END IF;
1356
1357 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1358 THEN
1359 interface_errors_rec.p_token2 := 'Initial Pickup Location Code';
1360 interface_errors_rec.p_value2 := delivery_rec.initial_pickup_location_code;
1361 l_d_temp_status := 'INVALID';
1362 END IF;
1363
1364 -- TPW - Distributed changes
1365 IF (p_document_type = 'SA' AND (nvl(l_warehouse_type, '!') = 'TW2')) THEN --{
1366 IF l_debug_on THEN
1367 wsh_debug_sv.log (l_module_name, 'Validating Customer Name');
1368 END IF;
1369
1370 --Calling Get_Operating_Unit to get the org_id of the organization_id
1371 l_ou_org_id := WSH_UTIL_CORE.Get_Operating_Unit (p_organization_id => l_org_id);
1372 --Set the Policy Context before calling OM API
1373 MO_GLOBAL.set_policy_context('S', l_ou_org_id);
1374
1375 IF ( delivery_rec.customer_name is not null )
1376 THEN
1377 --
1378 IF l_debug_on THEN
1379 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling OE_Value_To_Id.Sold_To_Org', WSH_DEBUG_SV.C_PROC_LEVEL);
1380 END IF;
1381 --
1382 l_customer_id := OE_Value_To_Id.Sold_To_Org(
1383 p_sold_to_org => delivery_rec.customer_name,
1384 p_customer_number => NULL );
1385
1386 --
1387 IF l_debug_on THEN
1388 WSH_DEBUG_SV.log(l_module_name, 'SoldTo Customer derived from Customer Name', l_customer_id );
1389 END IF;
1390 --
1391
1392 IF nvl(l_customer_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
1393 l_customer_id := null;
1394 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1395 THEN
1396 interface_errors_rec.p_token3 := 'Customer Name';
1397 interface_errors_rec.p_value3 := delivery_rec.customer_name;
1398 l_d_temp_status := 'INVALID';
1399 END IF;
1400
1401 END IF;
1402 END IF;
1403
1404 -- Ship-To Customer
1405 IF l_debug_on THEN
1406 wsh_debug_sv.log (l_module_name, 'Validating Ship To Customer Name');
1407 END IF;
1408 IF (delivery_rec.ship_to_customer_name is not null)
1409 THEN
1410 --Check if ShipTo Customer is same as SoldTo
1411 IF ( delivery_rec.ship_to_customer_name = delivery_rec.customer_name )
1412 THEN
1413 l_ship_to_cust_id := l_customer_id;
1414 ELSE
1415 --
1416 IF l_debug_on THEN
1417 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling OE_Value_To_Id.Sold_To_Org to derive Ship-To customer', WSH_DEBUG_SV.C_PROC_LEVEL);
1418 WSH_DEBUG_SV.logmsg(l_module_name, 'delivery_rec.ship_to_customer_name ='||delivery_rec.ship_to_customer_name, WSH_DEBUG_SV.C_PROC_LEVEL);
1419 END IF;
1420 --
1421 l_ship_to_cust_id := OE_Value_To_Id.Sold_To_Org(
1422 p_sold_to_org => delivery_rec.ship_to_customer_name,
1423 p_customer_number => NULL );
1424
1425 WSH_DEBUG_SV.logmsg(l_module_name, 'l_ship_to_cust_id = '||l_ship_to_cust_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1426
1427 IF nvl(l_ship_to_cust_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
1428 l_ship_to_cust_id := null;
1429 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1430 THEN
1431 interface_errors_rec.p_token3 := 'Ship To Customer Name';
1432 interface_errors_rec.p_value3 := delivery_rec.ship_to_customer_name;
1433 l_d_temp_status := 'INVALID';
1434 END IF;
1435 END IF;
1436 END IF;
1437
1438 IF (l_ship_to_cust_id is not null) THEN
1439
1440 IF (delivery_rec.ship_to_address1 is not null)
1441 THEN
1442 --
1443 IF l_debug_on THEN
1444 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling OE_Value_To_Id.Ship_To_Org', WSH_DEBUG_SV.C_PROC_LEVEL);
1445 END IF;
1446 --
1447 l_ship_to_site_use_id :=
1448 OE_Value_To_Id.Ship_To_Org(
1449 p_ship_to_address1 => delivery_rec.ship_to_address1,
1450 p_ship_to_address2 => delivery_rec.ship_to_address2,
1451 p_ship_to_address3 => delivery_rec.ship_to_address3,
1452 p_ship_to_address4 => delivery_rec.ship_to_address4,
1453 p_ship_to_location => NULL,
1454 p_ship_to_org => NULL,
1455 p_sold_to_org_id => l_ship_to_cust_id,
1456 p_ship_to_city => delivery_rec.ship_to_city,
1457 p_ship_to_state => delivery_rec.ship_to_state,
1458 p_ship_to_postal_code => delivery_rec.ship_to_postal_code,
1459 p_ship_to_country => delivery_rec.ship_to_country,
1460 p_ship_to_customer_id => l_ship_to_cust_id);
1461
1462 --
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.log(l_module_name, 'ShipTo Site Use Id', l_ship_to_site_use_id);
1465 END IF;
1466 IF ( nvl(l_ship_to_site_use_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM )
1467 THEN
1468 l_ship_to_site_use_id := null;
1469 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1470 THEN
1471 interface_errors_rec.p_token3 := 'Ship To Address';
1472 interface_errors_rec.p_value3 := delivery_rec.ship_to_address1;
1473 l_d_temp_status := 'INVALID';
1474 END IF;
1475 END IF;
1476 END IF;
1477
1478 IF l_ship_to_site_use_id is not null THEN
1479 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE',
1480 l_ship_to_site_use_id,
1481 l_ultimate_dropoff_location_id,
1482 l_return_status);
1483 IF l_debug_on THEN
1484 WSH_DEBUG_SV.logmsg(l_module_name, 'l_return_status '||l_return_status||' Ult Loc Id '||l_ultimate_dropoff_location_id);
1485 END IF;
1486 END IF;
1487 END IF;
1488 END IF;
1489
1490 ELSE --} {
1491
1492
1493 -- derive the delivery id for Shipment Advice inbound
1494 IF(p_document_type = 'SA') THEN
1495 OPEN dlvy_id_cur(delivery_rec.name);
1496 FETCH dlvy_id_cur INTO l_dlvy_id, l_ship_to_location_id;
1497
1498 IF(dlvy_id_cur%NOTFOUND) THEN
1499 raise invalid_delivery_name;
1500 END IF;
1501
1502 CLOSE dlvy_id_cur;
1503 END IF; -- if p_document_type = SA
1504
1505 -- Logic to handle cases where delivery does not have customer information
1506 -- Select the distinct customer_name from this delivery's delivery details
1507 -- If there is more than one distinct customer_name at the delivery detail level,
1508 -- Raise an exception because that is an invalid case
1509
1510 IF(delivery_rec.customer_name IS NULL) THEN
1511 IF l_debug_on THEN
1512 wsh_debug_sv.log (l_module_name, 'Delivery Rec customer name is null');
1513 END IF;
1514
1515 BEGIN
1516 SELECT DISTINCT customer_name
1517 INTO l_detail_customer_name
1518 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1519 WHERE wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
1520 AND wddi.customer_name IS NOT NULL
1521 AND wdai.delivery_interface_id = p_delivery_interface_id
1522 AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
1523 AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
1524 IF l_debug_on THEN
1525 wsh_debug_sv.log (l_module_name, 'Detail Rec distinct customer name', l_detail_customer_name);
1526 END IF;
1527
1528 IF l_detail_customer_name IS NOT NULL THEN
1529 delivery_rec.customer_name := l_detail_customer_name;
1530 ELSE
1531 raise invalid_customer_name;
1532 END IF;
1533
1534 EXCEPTION
1535 WHEN NO_DATA_FOUND THEN
1536 raise invalid_customer_name;
1537 WHEN TOO_MANY_ROWS THEN
1538 IF l_debug_on THEN
1539 wsh_debug_sv.log (l_module_name, 'Multiple distinct customer_names for details');
1540 END IF;
1541 raise invalid_customer_name;
1542 END;
1543 END IF; -- if delivery_rec.customer_name
1544
1545 -- store the delivery rec customer name for possible use at delivery detail level
1546 l_dlvy_customer_name := delivery_rec.customer_name;
1547
1548 IF(delivery_rec.intmed_ship_to_location_code IS NOT NULL) THEN
1549 IF l_debug_on THEN
1550 wsh_debug_sv.log (l_module_name, 'Organization id for delivery ' || l_org_id);
1551 END IF;
1552
1553 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1554 p_customer_name => delivery_rec.customer_name,
1555 p_location => delivery_rec.intmed_ship_to_location_code,
1556 x_customer_id => l_customer_id,
1557 x_location_id => l_intmed_ship_to_location_id,
1558 x_return_status => x_return_status,
1559 p_site_use_code => 'SHIP_TO',
1560 x_site_use_id => l_dummy_site_use_id);
1561
1562
1563 IF l_debug_on THEN
1564 wsh_debug_sv.log (l_module_name,'Status after Intmed_ship_to_location_code',x_return_status);
1565 END IF;
1566
1567 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1568 interface_errors_rec.p_token2 := 'Intmed_Ship_To_Location_Code';
1569 interface_errors_rec.p_value2 := delivery_rec.intmed_ship_to_location_code;
1570 l_d_temp_status := 'INVALID';
1571 END IF;
1572 END IF;
1573
1574 IF l_debug_on THEN
1575 wsh_debug_sv.log (l_module_name, 'Organization id for delivery - 2 ' || l_org_id);
1576 END IF;
1577 IF p_document_type <> 'SA' THEN --{ bug 3920178
1578
1579 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1580 p_customer_name => delivery_rec.customer_name,
1581 p_location => delivery_rec.ultimate_dropoff_location_code,
1582 x_customer_id => l_customer_id,
1583 x_location_id => l_ultimate_dropoff_location_id,
1584 x_return_status => x_return_status,
1585 p_site_use_code => 'SHIP_TO',
1586 x_site_use_id => l_dummy_site_use_id,
1587 p_org_id => l_op_unit_id);
1588
1589 IF l_debug_on THEN
1590 wsh_debug_sv.log (l_module_name,'Status after ultimate_dropoff_location ',x_return_status);
1591 END IF;
1592
1593 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1594 THEN
1595 interface_errors_rec.p_token5 := 'Ultimate Dropoff Location Code';
1596 interface_errors_rec.p_value5 := delivery_rec.ultimate_dropoff_location_code;
1597 l_d_temp_status := 'INVALID';
1598 END IF;
1599 END IF; -- bug 3920178 }
1600 END IF; --}
1601
1602 /* Update ID fields in interface table only if all the validations succeeded */
1603
1604 IF l_debug_on THEN
1605 wsh_debug_sv.log (l_module_name, 'l_d_temp_status',l_d_temp_status);
1606 END IF;
1607
1608 IF (l_d_temp_status <> 'INVALID')
1609 THEN
1610 UPDATE wsh_new_del_interface
1611 SET delivery_id = decode(p_document_type, 'SA',l_dlvy_id,delivery_id),
1612 customer_id = l_customer_id,
1613 organization_id = l_org_id,
1614 intmed_ship_to_location_id = l_intmed_ship_to_location_id,
1615 initial_pickup_location_id = l_initial_pickup_location_id,
1616 ultimate_dropoff_location_id = l_ultimate_dropoff_location_id
1617 WHERE delivery_interface_id = p_delivery_interface_id;
1618
1619 -- TPW - Distributed changes
1620 IF nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') in ('TPW', 'CMS') THEN
1621 UPDATE wsh_del_legs_interface
1622 SET delivery_id = l_dlvy_id
1623 WHERE delivery_interface_id = p_delivery_interface_id;
1624 END IF;
1625 END IF;
1626
1627 IF (l_d_temp_status = 'INVALID')
1628 THEN
1629 interface_errors_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
1630 interface_errors_rec.p_interface_id := p_delivery_interface_id;
1631 wsh_interface_validations_pkg.log_interface_errors (
1632 p_interface_errors_rec =>interface_errors_rec,
1633 p_api_name =>'WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To',
1634 x_return_status =>x_return_status);
1635 IF l_debug_on THEN
1636 wsh_debug_sv.log (l_module_name, 'log_interface_errors x_return_status',x_return_status);
1637 END IF;
1638 END IF;
1639 END LOOP;
1640
1641 -- TPW - Distributed changes
1642 IF l_debug_on THEN
1643 wsh_debug_sv.log (l_module_name, '*** Validating Delivery details interface ***');
1644 END IF;
1645
1646 FOR delivery_detail_rec IN delivery_detail_cur
1647 LOOP
1648
1649 l_locator_id := null;
1650 l_loc_temp_status := ' ';
1651
1652 -- Validation for Org
1653 WSH_UTIL_VALIDATE.Validate_Org(
1654 l_det_org_id,
1655 delivery_detail_rec.organization_code,
1656 x_return_status);
1657
1658 IF l_debug_on THEN
1659 wsh_debug_sv.log (l_module_name, 'Organization Code' , delivery_detail_rec.organization_code);
1660 wsh_debug_sv.log (l_module_name, 'Derived Org id', l_det_org_id);
1661 wsh_debug_sv.log (l_module_name, 'Status after validate org for detail',x_return_status);
1662 END IF;
1663
1664 IF (x_return_status = wsh_util_core.g_ret_sts_error)
1665 THEN
1666 interface_errors_rec.p_token1 := 'Organization Code';
1667 interface_errors_rec.p_value1 :=delivery_detail_rec.organization_code;
1668 l_dd_temp_status := 'INVALID';
1669 END IF;
1670 --bug 3920178
1671
1672 OPEN c_org_oper_unit (l_det_org_id);
1673 FETCH c_org_oper_unit INTO l_line_op_unit_id;
1674 CLOSE c_org_oper_unit;
1675
1676
1677 IF l_line_op_unit_id IS NULL
1678 THEN
1679 --{
1680 --
1681 IF l_debug_on THEN
1682 wsh_debug_sv.logmsg(l_module_name, 'Error: Location Org match not found');
1683 END IF;
1684 --
1685
1686 interface_errors_rec.p_token1 := 'Operating_unit'; --bmso
1687 interface_errors_rec.p_value1 := NULL;
1688 l_d_temp_status := 'INVALID';
1689
1690 --}
1691 END IF;
1692
1693
1694 -- Validation for Item
1695 WSH_EXTERNAL_INTERFACE_SV.Validate_Item (
1696 p_concatenated_segments => Delivery_Detail_Rec.Item_Number,
1697 p_organization_id =>L_Det_org_ID,
1698 x_inventory_item_id => L_Inventory_Item_ID,
1699 x_return_status =>X_Return_Status);
1700
1701 IF l_debug_on THEN
1702 wsh_debug_sv.log (l_module_name, 'Return status after validate item',X_Return_Status);
1703 wsh_debug_sv.log (l_module_name, 'Item Number', Delivery_Detail_Rec.Item_Number);
1704 wsh_debug_sv.log (l_module_name, 'Derived Item Id', L_Inventory_Item_ID);
1705 END IF;
1706 IF (x_return_status <> WSH_UTIL_CORE.g_ret_sts_success) Then
1707 Interface_Errors_rec.P_token2 := 'Item Number';
1708 Interface_Errors_rec.P_value2 := Delivery_Detail_Rec.Item_Number;
1709 L_DD_Temp_Status := 'INVALID';
1710 End If;
1711
1712 /*
1713 WSH_UTIL_VALIDATE. Validate_Item(L_Customer_item_ID,
1714 Delivery_Detail_Rec.Customer_Item_Number,
1715 L_Det_org_ID,
1716 L_Seg_Array,
1717 X_Return_Status);
1718
1719 IF (x_return_status <> WSH_UTIL_CORE.g_ret_sts_success) Then
1720 Interface_Errors_rec.P_token3 := 'Customer Item Number';
1721 Interface_Errors_rec.P_value3 := Delivery_Detail_Rec.Customer_Item_Number;
1722 L_DD_Temp_Status := 'INVALID';
1723 End If; */
1724
1725 IF nvl(l_warehouse_type, '!') in ('TW2') THEN -- {
1726 -- Validation for Locator Id
1727 If (Delivery_Detail_Rec.locator_id is null) and (Delivery_Detail_Rec.Locator_code is not null) THEN
1728 WSH_UTIL_VALIDATE.Validate_Locator_Code (
1729 p_locator_code => Delivery_Detail_Rec.Locator_code,
1730 p_organization_id =>L_Det_org_ID,
1731 x_locator_id => l_locator_id,
1732 x_return_status =>X_Return_Status);
1733
1734 IF l_debug_on THEN
1735 wsh_debug_sv.log (l_module_name, 'Return status after Validate Locator Code',X_Return_Status);
1736 wsh_debug_sv.log (l_module_name, 'Locator Code', Delivery_Detail_Rec.Locator_code);
1737 wsh_debug_sv.log (l_module_name, 'Derived Locator Id', l_locator_id);
1738 END IF;
1739 IF (x_return_status <> WSH_UTIL_CORE.g_ret_sts_success) Then
1740 Interface_Errors_rec.P_token3 := 'Locator Code';
1741 Interface_Errors_rec.P_value3 := Delivery_Detail_Rec.Locator_code;
1742 l_loc_temp_status := 'INVALID';
1743 End If;
1744 Else
1745 l_locator_id := Delivery_Detail_Rec.locator_id;
1746 END IF;
1747
1748 ELSE
1749
1750 -- Validation for Ship From
1751 WSH_UTIL_VALIDATE.Validate_Location(
1752 l_ship_from_location_id,
1753 delivery_detail_rec.ship_from_location_code,
1754 x_return_status);
1755
1756 IF (x_return_status = wsh_util_core.g_ret_sts_error)
1757 THEN
1758 interface_errors_rec.p_token4 := 'Ship From Location Code';
1759 interface_errors_rec.p_value4 := delivery_detail_rec.ship_from_location_code;
1760 l_dd_temp_status := 'INVALID';
1761 END IF;
1762
1763 -- Logic for delivery detail customer_name
1764 -- If the detail does not have a customer name, get the delivery's customer_name
1765 -- If the delivery customer_name is also null, then raise an exception. Invalid case.
1766 IF(delivery_detail_rec.customer_name IS NULL) THEN
1767 IF(l_dlvy_customer_name IS NOT NULL) THEN
1768 delivery_detail_rec.customer_name := l_dlvy_customer_name;
1769 ELSE
1770 raise invalid_customer_name;
1771 END IF;
1772 END IF;
1773
1774 -- Validation for Ship To
1775 -- Ship To does not at the container level , in the inbound message
1776 -- Hence we need to validate the ship to only for non-containers.
1777 IF (delivery_detail_rec.container_flag = 'N') THEN
1778 IF l_debug_on THEN
1779 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail ',l_det_org_id);
1780 END IF;
1781 IF p_document_type <> 'SA' THEN --{ bug 3920178
1782 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1783 p_customer_name => delivery_detail_rec.customer_name,
1784 p_location => delivery_detail_rec.ship_to_location_code,
1785 x_customer_id => l_det_customer_id,
1786 x_location_id => l_ship_to_location_id,
1787 x_return_status => x_return_status,
1788 p_site_use_code => 'SHIP_TO',
1789 x_site_use_id => l_ship_to_site_use_id);
1790
1791 IF l_debug_on THEN
1792 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1793 END IF;
1794
1795 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1796 interface_errors_rec.p_token5 := 'Ship To Location Code';
1797 interface_errors_rec.p_value5 := delivery_detail_rec.ship_to_location_code;
1798 l_dd_temp_status := 'INVALID';
1799 END IF;
1800 END IF;
1801
1802 -- Validation for Int_Med Ship To
1803 IF(delivery_detail_rec.intmed_ship_to_location_code IS NOT NULL) THEN
1804 IF l_debug_on THEN
1805 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail - 2' ,l_det_org_id);
1806 END IF;
1807 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1808 p_customer_name => delivery_detail_rec.customer_name,
1809 p_location => delivery_detail_rec.intmed_ship_to_location_code,
1810 x_customer_id => l_det_customer_id,
1811 x_location_id => l_det_intmed_shipto,
1812 x_return_status => x_return_status,
1813 p_site_use_code => 'SHIP_TO',
1814 x_site_use_id => l_ship_to_site_use_id);
1815
1816 IF l_debug_on THEN
1817 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1818 END IF;
1819 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1820 interface_errors_rec.p_token6 := 'IntMed ShipTo Location Code';
1821 interface_errors_rec.p_value6 := delivery_detail_rec.intmed_ship_to_location_code;
1822 l_dd_temp_status := 'INVALID';
1823 END IF;
1824 END IF;
1825
1826 -- Validation for Deliver To
1827 IF (delivery_detail_rec.container_flag = 'N') THEN
1828 IF l_debug_on THEN
1829 wsh_debug_sv.log (l_module_name, 'Organization id for delivery detail' ,l_det_org_id);
1830 END IF;
1831
1832 WSH_EXTERNAL_INTERFACE_SV.Validate_Ship_To(
1833 p_customer_name => delivery_detail_rec.customer_name,
1834 p_location => delivery_detail_rec.deliver_to_location_code,
1835 x_customer_id => l_det_customer_id,
1836 x_location_id => l_deliver_to_location_id,
1837 x_return_status => x_return_status,
1838 p_site_use_code => 'DELIVER_TO',
1839 --bug 3960768
1840 --x_site_use_id => l_ship_to_site_use_id);
1841 x_site_use_id => l_deliver_to_site_use_id);
1842
1843 IF l_debug_on THEN
1844 wsh_debug_sv.log (l_module_name, 'Validate_Ship_To x_return_status',x_return_status );
1845 END IF;
1846
1847 IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
1848 interface_errors_rec.p_token7 := 'Deliver To Location Code';
1849 interface_errors_rec.p_value7 := delivery_detail_rec.deliver_to_location_code;
1850 l_dd_temp_status := 'INVALID';
1851 END IF;
1852 END IF; --} matches if p_document_type <> SA
1853 END IF;
1854
1855 /*
1856 wsh_util_validate.validate_customer(l_det_customer_id,
1857 delivery_detail_rec.customer_number,
1858 x_return_status);
1859
1860 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1861 THEN
1862 interface_errors_rec.p_token8 := 'Customer Number';
1863 interface_errors_rec.p_value8 := delivery_detail_rec.customer_number;
1864 l_dd_temp_status := 'INVALID';
1865 END IF;
1866 */
1867
1868 /* Call validation APIs for Lot and Subinventory etc which are not validated
1869 in any of the APIs which will be called to populate data into base tables for
1870 a 940 Transaction */
1871
1872 IF (p_document_type = 'SR')
1873 THEN
1874 IF (delivery_detail_rec.subinventory IS NOT NULL)
1875 THEN
1876 wsh_delivery_details_inv.validate_subinventory (p_subinventory => delivery_detail_rec.subinventory,
1877 p_organization_id => l_det_org_id,
1878 p_inventory_item_id => l_inventory_item_id,
1879 x_return_status => x_return_status,
1880 x_result => x_result);
1881
1882 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1883 THEN
1884 interface_errors_rec.p_token9 := 'Subinventory';
1885 interface_errors_rec.p_value9 := delivery_detail_rec.subinventory;
1886 l_dd_temp_status := 'INVALID';
1887 END IF;
1888 END IF;
1889
1890 IF (delivery_detail_rec.revision IS NOT NULL)
1891 THEN
1892 wsh_delivery_details_inv.validate_revision (delivery_detail_rec.revision,
1893 l_det_org_id,
1894 l_inventory_item_id,
1895 x_return_status,
1896 x_result
1897 );
1898
1899 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1900 THEN
1901 interface_errors_rec.p_token10 := 'Revision';
1902 interface_errors_rec.p_value10 := delivery_detail_rec.revision;
1903 l_dd_temp_status := 'INVALID';
1904 END IF;
1905 END IF;
1906
1907 IF (delivery_detail_rec.lot_number IS NOT NULL)
1908 THEN
1909 wsh_delivery_details_inv.validate_lot_number (p_lot_number => delivery_detail_rec.lot_number,
1910 p_organization_id => l_det_org_id,
1911 p_inventory_item_id => l_inventory_item_id,
1912 p_subinventory => delivery_detail_rec.subinventory,
1913 p_revision => delivery_detail_rec.revision,
1914 p_locator_id => delivery_detail_rec.locator_id,
1915 x_return_status => x_return_status,
1916 x_result => x_result);
1917
1918 IF (x_return_status <> wsh_util_core.g_ret_sts_success)
1919 THEN
1920 interface_errors_rec.p_token11 := 'Lot Number';
1921 interface_errors_rec.p_value11 := delivery_detail_rec.lot_number;
1922 l_dd_temp_status := 'INVALID';
1923 END IF;
1924 END IF;
1925
1926 END IF; -- End of IF (P_Document_Type ='SR' ....
1927 END IF; --}
1928
1929 /* Update ID fields in interface table only if all the validations succeeded */
1930 IF l_debug_on THEN
1931 wsh_debug_sv.log (l_module_name, 'Delivery ID', l_dlvy_id);
1932 wsh_debug_sv.log (l_module_name, 'l_dd_temp_status', l_dd_temp_status);
1933 wsh_debug_sv.log (l_module_name, 'l_loc_temp_status', l_loc_temp_status);
1934 END IF;
1935
1936 IF (l_dd_temp_status <> 'INVALID')
1937 THEN
1938 UPDATE wsh_del_details_interface
1939 SET inventory_item_id = l_inventory_item_id,
1940 customer_item_id = l_customer_item_id,
1941 organization_id = l_det_org_id,
1942 ship_from_location_id = l_ship_from_location_id,
1943 ship_to_location_id = l_ship_to_location_id,
1944 intmed_ship_to_location_id = l_det_intmed_shipto,
1945 deliver_to_location_id = l_deliver_to_location_id,
1946 customer_id = l_det_customer_id,
1947 ship_to_site_use_id = l_ship_to_site_use_id,
1948 deliver_to_site_use_id = l_deliver_to_site_use_id,
1949 org_id = l_line_op_unit_id,
1950 -- TPW - Distributed changes
1951 locator_id = l_locator_id,
1952 source_header_id = decode(p_document_type, 'SR', l_dlvy_id, source_header_id)
1953 WHERE delivery_detail_interface_id = delivery_detail_rec.delivery_detail_interface_id;
1954
1955 END IF;
1956
1957 -- TPW - Distributed changes - added l_loc_temp_status status check
1958 IF (l_dd_temp_status = 'INVALID' or l_loc_temp_status = 'INVALID' )
1959 THEN
1960 interface_errors_rec.p_interface_table_name := 'WSH_DEL_DETAILS_INTERFACE';
1961 interface_errors_rec.p_interface_id := delivery_detail_rec.delivery_detail_interface_id;
1962 wsh_interface_validations_pkg.log_interface_errors (
1963 p_interface_errors_rec =>interface_errors_rec,
1964 p_api_name => 'wsh_delivery_details_inv.validate_lot_number',
1965 x_return_status =>x_return_status);
1966 IF l_debug_on THEN
1967 wsh_debug_sv.log (l_module_name, 'log_interface_errors x_return_status', x_return_status);
1968 END IF;
1969 --Fulfillment Batch XML Project
1970 IF l_loc_temp_status = 'INVALID' THEN
1971 IF l_debug_on THEN
1972 wsh_debug_sv.log (l_module_name, 'Checking if item is locator controlled', x_return_status);
1973 END IF;
1974
1975
1976 OPEN Get_Inv_Controls (L_Inventory_Item_ID,L_Det_org_ID);
1977 FETCH Get_Inv_Controls INTO l_loc_flag,l_restrict_loc_code,l_restrict_sub_code,l_location_control_code,l_reservable_type,l_txn_enabled_flag ;
1978 CLOSE Get_Inv_Controls;
1979
1980 l_org_loc_code := WSH_DELIVERY_DETAILS_INV.Get_Org_Loc (l_org_id);
1981 l_sub_loc_code := WSH_DELIVERY_DETAILS_INV.Sub_Loc_Ctl (delivery_detail_rec.subinventory,l_org_id);
1982 l_loc_ctl_code := WSH_DELIVERY_DETAILS_INV.Locator_Ctl_Code(l_org_id,
1983 l_restrict_loc_code,
1984 l_org_loc_code,
1985 l_sub_loc_code,
1986 l_location_control_code);
1987 IF l_loc_ctl_code = 1 then
1988 raise invalid_locator;
1989 end if;
1990 END IF;
1991 END IF;
1992 END LOOP;
1993
1994 IF (l_dd_temp_status <> 'INVALID') AND (l_d_temp_status <> 'INVALID')
1995 THEN
1996 x_return_status := wsh_util_core.g_ret_sts_success;
1997 ELSE
1998 x_return_status := wsh_util_core.g_ret_sts_error;
1999 END IF;
2000
2001 wsh_debug_sv.pop (l_module_name);
2002 EXCEPTION
2003 WHEN invalid_delivery_name THEN
2004 IF(dlvy_id_cur%ISOPEN) THEN
2005 CLOSE dlvy_id_cur;
2006 END IF;
2007 x_return_status := wsh_util_core.g_ret_sts_error;
2008 IF l_debug_on THEN
2009 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2010 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_name');
2011 END IF;
2012
2013 WHEN invalid_delivery_int_id THEN
2014 x_return_status := wsh_util_core.g_ret_sts_error;
2015 IF l_debug_on THEN
2016 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_int_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2017 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_int_id');
2018 END IF;
2019 WHEN invalid_locator THEN
2020 IF Get_Inv_Controls%ISOPEN THEN
2021 CLOSE Get_Inv_Controls;
2022 END IF;
2023 x_return_status := wsh_util_core.g_ret_sts_error;
2024 IF l_debug_on THEN
2025 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_locator exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2026 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_locator');
2027 END IF;
2028 WHEN invalid_customer_name THEN
2029 x_return_status := wsh_util_core.g_ret_sts_error;
2030 IF l_debug_on THEN
2031 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_customer_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2032 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_customer_name');
2033 END IF;
2034 WHEN get_warehouse_type_failed THEN
2035 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2036 IF l_debug_on THEN
2037 WSH_DEBUG_SV.logmsg(l_module_name,'get_warehouse_type_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2038 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:get_warehouse_type_failed');
2039 END IF;
2040 WHEN OTHERS THEN
2041 IF Get_Inv_Controls%ISOPEN THEN
2042 CLOSE Get_Inv_Controls;
2043 END IF;
2044 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2045 IF l_debug_on THEN
2046 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2047 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2048 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2049 END IF;
2050 END derive_ids;
2051
2052
2053 /*=======================================================================================
2054
2055 PROCEDURE NAME : Delete_Interface_Records
2056
2057 This Procedure will be used to delete record in the different interface tables, after data
2058 is populated in the base tables ????
2059
2060 =======================================================================================*/
2061
2062 PROCEDURE delete_interface_records (
2063 p_delivery_interface_id IN NUMBER,
2064 x_return_status OUT NOCOPY VARCHAR2
2065 )
2066 IS
2067 l_transaction_status wsh_transactions_history.transaction_status%TYPE;
2068 invalid_delete EXCEPTION;
2069 invalid_delivery EXCEPTION;
2070
2071 CURSOR detail_interface_ids
2072 IS
2073 SELECT delivery_detail_interface_id, del_assgn_interface_id
2074 FROM wsh_del_assgn_interface
2075 WHERE delivery_interface_id = p_delivery_interface_id
2076 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2077
2078 CURSOR ids
2079 IS
2080 SELECT wdli.delivery_leg_interface_id,
2081 wdli.pick_up_stop_interface_id,
2082 wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
2083 FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
2084 WHERE wdli.delivery_interface_id = p_delivery_interface_id
2085 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
2086 AND wdli.INTERFACE_ACTION_CODE = '94X_INBOUND'
2087 AND wtsi.INTERFACE_ACTION_CODE = '94X_INBOUND';
2088
2089 l_del_count NUMBER;
2090 --
2091 l_debug_on BOOLEAN;
2092 --
2093 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_INTERFACE_RECORDS';
2094 --
2095 BEGIN
2096 --
2097 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2098 --
2099 IF l_debug_on IS NULL
2100 THEN
2101 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2102 END IF;
2103 --
2104 IF l_debug_on THEN
2105 wsh_debug_sv.push (l_module_name, 'DELETE INTERFACE RECORDS');
2106 wsh_debug_sv.log ( l_module_name, 'DELIVERY INTERFACE ID', p_delivery_interface_id);
2107 END IF;
2108
2109 SELECT COUNT (*)
2110 INTO l_del_count
2111 FROM wsh_new_del_interface
2112 WHERE delivery_interface_id = p_delivery_interface_id
2113 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2114
2115 IF (l_del_count <> 0)
2116 THEN
2117 FOR ids_rec IN ids
2118 LOOP
2119
2120 DELETE wsh_del_legs_interface
2121 WHERE delivery_leg_interface_id = ids_rec.delivery_leg_interface_id
2122 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2123
2124 --Added for bug 7615007
2125 DELETE wsh_freight_costs_interface
2126 WHERE delivery_leg_interface_id = ids_rec.delivery_leg_interface_id
2127 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2128
2129 DELETE wsh_trip_stops_interface
2130 WHERE stop_interface_id IN
2131 (ids_rec.pick_up_stop_interface_id,
2132 ids_rec.drop_off_stop_interface_id
2133 )
2134 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2135
2136 --Added for bug 7615007
2137 DELETE wsh_freight_costs_interface
2138 WHERE stop_interface_id IN
2139 (ids_rec.pick_up_stop_interface_id,
2140 ids_rec.drop_off_stop_interface_id
2141 )
2142 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2143
2144 DELETE wsh_trips_interface
2145 WHERE trip_interface_id = ids_rec.trip_interface_id
2146 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2147
2148 --Added for bug 7615007
2149 DELETE wsh_freight_costs_interface
2150 WHERE trip_interface_id = ids_rec.trip_interface_id
2151 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2152
2153 END LOOP; -- End of FOR IDs_Rec IN IDs LOOP
2154
2155 FOR detail_interface_ids_rec IN detail_interface_ids
2156 LOOP
2157 DELETE wsh_del_details_interface
2158 WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
2159 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2160
2161 --Added for bug 7615007
2162 DELETE wsh_freight_costs_interface
2163 WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
2164 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2165
2166 DELETE wsh_del_assgn_interface
2167 WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
2168 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2169 END LOOP; -- End of FOR Details_Interface_IDs_Rec ...
2170
2171 DELETE wsh_new_del_interface
2172 WHERE delivery_interface_id = p_delivery_interface_id
2173 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2174
2175 --Added for bug 7615007
2176 DELETE wsh_freight_costs_interface
2177 WHERE delivery_interface_id = p_delivery_interface_id
2178 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2179
2180 x_return_status := wsh_util_core.g_ret_sts_success;
2181 ELSE -- IF Delivery Doesnot exists
2182 RAISE invalid_delivery;
2183 END IF;
2184
2185 wsh_debug_sv.pop (l_module_name);
2186 EXCEPTION
2187 WHEN invalid_delivery
2188 THEN
2189 x_return_status := wsh_util_core.g_ret_sts_error;
2190 IF l_debug_on THEN
2191 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2192 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery');
2193 END IF;
2194 WHEN invalid_delete
2195 THEN
2196 x_return_status := wsh_util_core.g_ret_sts_error;
2197 IF l_debug_on THEN
2198 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delete exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2199 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delete');
2200 END IF;
2201 WHEN OTHERS
2202 THEN
2203 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2204 IF l_debug_on THEN
2205 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2206 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2207 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2208 END IF;
2209 END delete_interface_records;
2210
2211 --R12.1.1 STANDALONE PROJECT - Added new API
2212 /*=======================================================================================
2213
2214 PROCEDURE NAME : Delete_Interface_Records
2215
2216 This Procedure will be used to delete record in the different interface tables, after data
2217 is populated in the base tables
2218
2219 =======================================================================================*/
2220
2221 PROCEDURE delete_interface_records (
2222 p_del_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
2223 p_del_det_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
2224 p_del_assgn_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
2225 p_del_error_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
2226 p_det_error_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
2227 x_return_status OUT NOCOPY VARCHAR2
2228 )
2229 IS
2230 l_debug_on BOOLEAN;
2231 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_INTERFACE_RECORDS';
2232
2233 BEGIN
2234 --
2235 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2236 --
2237 IF l_debug_on IS NULL
2238 THEN
2239 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2240 END IF;
2241 --
2242 IF l_debug_on THEN
2243 wsh_debug_sv.push (l_module_name, 'DELETE INTERFACE RECORDS');
2244 wsh_debug_sv.log ( l_module_name, 'p_del_interface_id_tbl.COUNT', p_del_interface_id_tbl.COUNT);
2245 wsh_debug_sv.log ( l_module_name, 'p_del_det_interface_id_tbl.COUNT', p_del_det_interface_id_tbl.COUNT);
2246 wsh_debug_sv.log ( l_module_name, 'p_del_assgn_interface_id_tbl.COUNT', p_del_assgn_interface_id_tbl.COUNT);
2247 wsh_debug_sv.log ( l_module_name, 'p_del_error_interface_id_tbl.COUNT', p_del_error_interface_id_tbl.COUNT);
2248 wsh_debug_sv.log ( l_module_name, 'p_det_error_interface_id_tbl.COUNT', p_det_error_interface_id_tbl.COUNT);
2249 END IF;
2250
2251 x_return_status := wsh_util_core.g_ret_sts_success;
2252 --
2253 IF (p_det_error_interface_id_tbl.COUNT > 0) THEN
2254 FORALL i in p_det_error_interface_id_tbl.FIRST..p_det_error_interface_id_tbl.LAST
2255 DELETE FROM wsh_interface_errors
2256 WHERE interface_error_id = p_det_error_interface_id_tbl(i);
2257 --
2258 IF l_debug_on THEN
2259 wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_interface_errors');
2260 END IF;
2261 --
2262 END IF;
2263 --
2264 IF (p_del_error_interface_id_tbl.COUNT > 0) THEN
2265 FORALL i in p_del_error_interface_id_tbl.FIRST..p_del_error_interface_id_tbl.LAST
2266 DELETE FROM wsh_interface_errors
2267 WHERE interface_error_id = p_del_error_interface_id_tbl(i);
2268 --
2269 IF l_debug_on THEN
2270 wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_interface_errors');
2271 END IF;
2272 --
2273 END IF;
2274 --
2275 IF (p_del_assgn_interface_id_tbl.COUNT > 0) THEN
2276 FORALL i in p_del_assgn_interface_id_tbl.FIRST..p_del_assgn_interface_id_tbl.LAST
2277 DELETE FROM wsh_del_assgn_interface
2278 WHERE del_assgn_interface_id = p_del_assgn_interface_id_tbl(i);
2279 --
2280 IF l_debug_on THEN
2281 wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_del_assgn_interface');
2282 END IF;
2283 --
2284 END IF;
2285 --
2286 IF (p_del_det_interface_id_tbl.COUNT > 0) THEN
2287 FORALL i in p_del_det_interface_id_tbl.FIRST..p_del_det_interface_id_tbl.LAST
2288 DELETE FROM wsh_del_details_interface
2289 WHERE delivery_detail_interface_id = p_del_det_interface_id_tbl(i);
2290 --
2291 IF l_debug_on THEN
2292 wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_del_details_interface');
2293 END IF;
2294 --
2295 END IF;
2296 --
2297 IF (p_del_interface_id_tbl.COUNT > 0) THEN
2298 FORALL i in p_del_interface_id_tbl.FIRST..p_del_interface_id_tbl.LAST
2299 DELETE FROM wsh_new_del_interface
2300 WHERE delivery_interface_id = p_del_interface_id_tbl(i);
2301 --
2302 IF l_debug_on THEN
2303 wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_new_del_interface');
2304 END IF;
2305 --
2306 END IF;
2307 --
2308 IF l_debug_on THEN
2309 WSH_DEBUG_SV.log(l_module_name, 'Return Status',x_return_status);
2310 wsh_debug_sv.pop (l_module_name);
2311 END IF;
2312 --
2313 EXCEPTION
2314 WHEN OTHERS THEN
2315 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2316 --
2317 IF l_debug_on THEN
2318 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2319 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2320 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2321 END IF;
2322 --
2323 END delete_interface_records;
2324
2325 END wsh_process_interfaced_pkg;