[Home] [Help]
PACKAGE BODY: APPS.WSH_INTERFACE
Source
1 PACKAGE BODY WSH_INTERFACE as
2 /* $Header: WSHDDINB.pls 120.0.12000000.3 2007/04/11 23:10:12 rvarghes ship $ */
3
4 G_CALL_MODE VARCHAR2(6) := 'ONLINE'; -- global variable for PRINTMSG
5
6
7 --
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INTERFACE';
9 --
10 -- anxsharm For Load Tender
11 -- Forward Declaration
12 PROCEDURE Get_Details_Snapshot(
13 p_source_code IN VARCHAR2,
14 p_changed_attributes IN ChangedAttributeTabType,
15 p_phase IN NUMBER,
16 x_dd_ids IN OUT NOCOPY wsh_util_core.id_tab_type,
17 x_out_table OUT NOCOPY wsh_interface.deliverydetailtab,
18 x_return_status OUT NOCOPY VARCHAR2);
19
20 --
21
22 PROCEDURE Update_Shipping_Attributes(
23 p_source_code IN VARCHAR2
24 , p_changed_attributes IN ChangedAttributeTabType
25 , x_return_status OUT NOCOPY VARCHAR2
26 , p_log_level IN NUMBER -- log level fix
27 )
28 IS
29 l_interface_flag VARCHAR2(1);
30 l_rs VARCHAR2(1);
31 --
32 invalid_source_code exception;
33
34 l_debug_on BOOLEAN;
35
36 --
37 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIPPING_ATTRIBUTES';
38 --
39 --Bugfix 4070732
40 l_return_status VARCHAR2(32767);
41 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
42 l_reset_flags BOOLEAN;
43
44 -- K LPN CONV. rv
45 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
46 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(32767);
49 -- K LPN CONV. rv
50 BEGIN
51 -- Bugfix 4070732
52 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
53 THEN
54 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
55 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
56 END IF;
57 -- End of Code Bugfix 4070732
58 --log level fix
59 IF p_log_level <> FND_API.G_MISS_NUM THEN -- log level fix
60 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
61 END IF;
62
63 --
64 -- Debug Statements
65 --
66 --
67 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
68 --
69 IF l_debug_on IS NULL
70 THEN
71 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
72 END IF;
73 --
74 IF l_debug_on THEN
75 WSH_DEBUG_SV.push(l_module_name);
76 --
77 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
78 WSH_DEBUG_SV.log(l_module_name,'P_LOG_LEVEL',P_LOG_LEVEL);
79
80 WSH_DEBUG_SV.log(l_module_name,'COUNT OF P_CHANGED_ATTR Table',p_changed_attributes.COUNT);
81
82 END IF;
83 --
84 l_rs := FND_API.G_RET_STS_SUCCESS;
85 SAVEPOINT before_changes;
86
87 --
88 -- Debug Statements
89 --
90 IF l_debug_on THEN
91 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE WSH_INTERFACE.UPDATE_SHIPPING_ATTRIBUTES' );
92 END IF;
93 --
94
95 Lock_Records(
96 p_source_code => p_source_code,
97 p_changed_attributes => p_changed_attributes,
98 x_interface_flag => l_interface_flag,
99 x_return_status => l_rs);
100
101 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
102 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
103 IF p_source_code <> 'INV' THEN
104 --
105 -- Debug Statements
106 --
107 IF l_debug_on THEN
108 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_INTERFACE.LOCK_RECORDS BEFORE PROCESS_RECORDS ' || L_RS );
109 END IF;
110 --
111 -- p_source_code should only be 'INV' or 'OE', else raise error. -- jckwok
112 -- 5870774: Added OKE since there will be cancellations initiated from OKE
113 IF p_source_code NOT IN ('OE','WSH', 'OKE') THEN
114 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SOURCE_CODE');
115 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
116 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
117 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
118 ROLLBACK TO before_changes;
119 raise invalid_source_code;
120 END IF;
121
122 Process_Records(
123 p_source_code => p_source_code,
124 p_changed_attributes => p_changed_attributes,
125 p_interface_flag => l_interface_flag,
126 x_return_status => l_rs);
127 --
128 -- Debug Statements
129 --
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_INTERFACE.PROCESS_RECORDS ' || L_RS );
132 END IF;
133 --
134
135 ELSIF (p_source_code = 'INV') THEN
136
137 --
138 -- Debug Statements
139 --
140 IF l_debug_on THEN
141 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.UPDATE_INVENTORY_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
142 END IF;
143 --
144 WSH_USA_INV_PVT.Update_Inventory_Info(
145 p_changed_attributes => p_changed_attributes,
146 x_return_status => l_rs);
147
148 ELSE
149
150 --
151 -- Debug Statements
152 --
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID SOURCE CODE ' );
155 END IF;
156 --
157 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SOURCE_CODE');
158 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
159 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.G_RET_STS_ERROR);
160 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
161
162 END IF; -- p_source_code
163 --
164 -- K LPN CONV. rv
165 --
166 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
167 THEN
168 --{
169 IF l_debug_on THEN
170 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);
171 END IF;
172
173 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
174 (
175 p_in_rec => l_lpn_in_sync_comm_rec,
176 x_return_status => l_return_status,
177 x_out_rec => l_lpn_out_sync_comm_rec
178 );
179 --
180 --
181 IF l_debug_on THEN
182 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
183 END IF;
184 IF l_rs = WSH_UTIL_CORE.G_RET_STS_SUCCESS
185 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
186 THEN
187 --
188 l_rs := l_return_status;
189 --
190 ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) and l_rs <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
191 --
192 l_rs := l_return_status;
193 --
194 END IF;
195 --
196 --
197 --}
198 END IF;
199 --
200 -- K LPN CONV. rv
201 --
202
203 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
204 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
205 --
206 -- Debug Statements
207 --
208 IF l_debug_on THEN
209 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_INTERFACE.PROCESS_RECORDS / UPDATE_INVENTORY_INFO' );
210 END IF;
211 --
212 ROLLBACK TO before_changes;
213
214 END IF; -- return status after Update_INV and Process_Records
215
216 END IF; -- l_rs after lock_records
217 --
218
219 x_return_status := l_rs;
220
221 --
222 -- Start code for Bugfix 4070732
223 --
224 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
225 AND UPPER(WSH_UTIL_CORE.G_START_OF_SESSION_API) = UPPER(l_api_session_name) THEN
226 --{
227 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
228 --{
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
231 END IF;
232
233 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
234 x_return_status => l_return_status);
235
236
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
239 END IF;
240
241 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
242 x_return_status := l_return_status;
243 END IF;
244 --}
245 END IF;
246 --}
247 ELSIF UPPER(WSH_UTIL_CORE.G_START_OF_SESSION_API) = UPPER(l_api_session_name) THEN
248 --{
249 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
250 --{
251 l_reset_flags := TRUE;
252
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
255 END IF;
256
257 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
258 x_return_status => l_return_status);
259
260 IF l_debug_on THEN
261 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
262 END IF;
263
264 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
265 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
266 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
267 x_return_status := l_return_status;
268 END IF;
269
270 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
271 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
272 ) THEN
273 ROLLBACK TO before_changes;
274 END IF;
275 --}
276 END IF;
277 --}
278 END IF;
279 --
280 -- End of Code Bugfix 4070732
281 --
282
283 --
284 -- Debug Statements
285 --
286 IF l_debug_on THEN
287 WSH_DEBUG_SV.pop(l_module_name);
288 END IF;
289 --
290 EXCEPTION
291 -- J IB --jckwok
292 WHEN invalid_source_code THEN
293 --
294 -- K LPN CONV. rv
295 --
296 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
297 THEN
298 --{
299 IF l_debug_on THEN
300 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);
301 END IF;
302
303 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
304 (
305 p_in_rec => l_lpn_in_sync_comm_rec,
306 x_return_status => l_return_status,
307 x_out_rec => l_lpn_out_sync_comm_rec
308 );
309 --
310 --
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
313 END IF;
314 --
315 --
316 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
317 x_return_status := l_return_status;
318 END IF;
319 --
320 --}
321 END IF;
322 --
323 -- K LPN CONV. rv
324 --
325 --
326 -- Start code for Bugfix 4070732
327 --
328 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
329 --{
330 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
331 --{
332 IF l_debug_on THEN
333 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
334 END IF;
335
336 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
337 x_return_status => l_return_status);
338
339
340 IF l_debug_on THEN
341 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
342 END IF;
343
344 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
345 x_return_status := l_return_status;
346 END IF;
347 --}
348 END IF;
349 --}
350 END IF;
351 --
352 -- End of Code Bugfix 4070732
353 --
354 IF l_debug_on THEN
355 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_SOURCE_CODE');
356 END IF;
357 --
358 WHEN others THEN
359 ROLLBACK TO before_changes;
360 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
361 wsh_util_core.default_handler('WSH_INTERFACE.Update_Shipping_Attributes');
362 --
363 -- Start code for Bugfix 4070732
364 --
365 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
366 --{
367 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
368 --{
369 IF l_debug_on THEN
370 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
371 END IF;
372
373 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
374 x_return_status => l_return_status);
375
376
377 IF l_debug_on THEN
378 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
379 END IF;
380
381 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
382 x_return_status := l_return_status;
383 END IF;
384 --}
385 END IF;
386 --}
387 END IF;
388 --
389 -- End of Code Bugfix 4070732
390 --
391 --
392 -- Debug Statements
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
396 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
397 END IF;
398 --
399 END Update_Shipping_Attributes;
400
401
402
403 PROCEDURE Get_In_Transit_Qty(
404 p_source_code IN VARCHAR2 DEFAULT 'OE',
405 p_customer_id IN NUMBER,
406 p_ship_to_org_id IN NUMBER,
407 p_ship_from_org_id IN NUMBER,
408 p_inventory_item_id IN NUMBER,
409 p_order_header_id IN NUMBER,
410 p_cust_production_seq_num IN VARCHAR2,
411 p_shipper_recs IN t_shipper_rec,
412 p_schedule_generation_date IN DATE,
413 p_shipment_date IN DATE,
414 x_in_transit_qty OUT NOCOPY NUMBER,
415 x_return_status OUT NOCOPY VARCHAR2) IS
416 l_ship_to_location_id number;
417 l_ship_from_location_id number;
418 invalid_org exception;
419 invalid_cust_site exception;
420 l_location_status varchar2(30);
421 CURSOR C_transit_detail
422 IS
423 select dd.delivery_detail_id,
424 s.stop_id,
425 s.actual_departure_date,
426 nd.name,
427 dd.shipped_quantity
428 from wsh_delivery_Details dd,
429 wsh_trip_stops s,
430 wsh_delivery_legs dl,
431 wsh_delivery_assignments_v da,
432 wsh_new_deliveries nd
433 -- wsh_delivery_line_status_v ds
434 where s.stop_id = dl.pick_up_stop_id
435 and dl.delivery_id = nd.delivery_id
436 and nd.delivery_id = da.delivery_id
437 -- and dd.delivery_detail_id = ds.delivery_detail_id
438 and da.delivery_detail_id = dd.delivery_detail_id
439 and s.stop_location_id = nd.initial_pickup_location_id
440 and dd.customer_id = p_customer_id
441 and dd.ship_to_location_id = l_ship_to_location_id
442 and dd.ship_from_location_id = l_ship_from_location_id
443 and dd.inventory_item_id = p_inventory_item_id
444 and dd.source_header_id = p_order_header_id
445 -- and ds.delivery_status in ('CL', 'IT','CO')
446 and NVL(nd.shipment_direction, 'O') IN ('O', 'IO')
447 and NVL(dd.customer_prod_seq,'*') = NVL(NVL(p_cust_production_seq_num,dd.customer_prod_seq), '*');
448 l_transit_detail c_transit_detail%ROWTYPE;
449 l_total_qty_in_transit number;
450 --
451 l_debug_on BOOLEAN;
452 --
453 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_IN_TRANSIT_QTY';
454 --
455 begin
456 --
457 -- Debug Statements
458 --
459 --
460 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
461 --
462 IF l_debug_on IS NULL
463 THEN
464 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
465 END IF;
466 --
467 IF l_debug_on THEN
468 WSH_DEBUG_SV.push(l_module_name);
469 --
470 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
471 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
472 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_ORG_ID',P_SHIP_TO_ORG_ID);
473 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG_ID',P_SHIP_FROM_ORG_ID);
474 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
475 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
476 WSH_DEBUG_SV.log(l_module_name,'P_CUST_PRODUCTION_SEQ_NUM',P_CUST_PRODUCTION_SEQ_NUM);
477 WSH_DEBUG_SV.log(l_module_name,'P_SCHEDULE_GENERATION_DATE',P_SCHEDULE_GENERATION_DATE);
478 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_DATE',P_SHIPMENT_DATE);
479 END IF;
480 --
481 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
482 l_total_qty_in_transit := 0;
483 --
484 -- Debug Statements
485 --
486 IF l_debug_on THEN
487 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
488 END IF;
489 --
490 wsh_util_core.get_location_id('ORG',p_ship_from_org_id, l_ship_from_location_id, l_location_status, FALSE);
491 if (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
492 IF (l_location_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
493 AND (l_ship_from_location_id IS NULL) THEN
494 x_in_transit_qty := 0 ;
495 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
496 IF l_debug_on THEN
497 WSH_DEBUG_SV.log(l_module_name,'X_INTRANSIT_QTY',
498 x_in_transit_qty);
499 WSH_DEBUG_SV.pop(l_module_name);
500 END IF;
501 RETURN;
502 ELSE
503 raise INVALID_ORG;
504 END IF;
505 end if;
506 --
507 -- Debug Statements
508 --
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
511 END IF;
512 --
513 wsh_util_core.get_location_id('CUSTOMER SITE',p_ship_to_org_id, l_ship_to_location_id, l_location_status,FALSE);
514 if (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
515 IF (l_location_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
516 AND (l_ship_to_location_id IS NULL) THEN
517 x_in_transit_qty := 0 ;
518 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.log(l_module_name,'X_INTRANSIT_QTY',
521 x_in_transit_qty);
522 WSH_DEBUG_SV.pop(l_module_name);
523 END IF;
524 RETURN;
525 ELSE
526 raise invalid_cust_site;
527 END IF;
528 end if;
529 open c_transit_detail;
530
531 if (p_shipper_Recs.shipper_id1 is not null) then
532 loop
533 fetch c_transit_detail into l_transit_detail;
534 exit when c_transit_detail%NOTFOUND;
535 if ((l_transit_detail.name <> NVL(p_shipper_recs.shipper_id1,'*'))
536 and
537 (l_transit_detail.name <> NVL(p_shipper_recs.shipper_id2,'*'))
538 and
539 (l_transit_detail.name <> NVL(p_shipper_recs.shipper_id3,'*'))
540 and
541 (l_transit_detail.name <> NVL(p_shipper_recs.shipper_id4,'*'))
542 and
543 (l_transit_detail.name <> NVL(p_shipper_recs.shipper_id5,'*')))
544
545 then
546 l_total_qty_in_transit := l_total_qty_in_transit + l_transit_detail.shipped_quantity;
547 end if;
548 end loop;
549 elsif ( (p_shipper_Recs.shipper_id1 is null) and
550 (p_shipment_date is not null)) then
551 loop
552 fetch c_transit_detail into l_transit_detail;
553 exit when c_transit_detail%NOTFOUND;
554 if (p_shipment_date < l_transit_detail.actual_departure_date ) then
555 l_total_qty_in_transit := l_total_qty_in_transit + l_transit_detail.shipped_quantity;
556 end if;
557 end loop;
558 elsif (p_schedule_generation_date is not null) then
559 loop
560 fetch c_transit_detail into l_transit_detail;
561 exit when c_transit_detail%NOTFOUND;
562 if (p_schedule_generation_date < l_transit_detail.actual_departure_date ) then
563 l_total_qty_in_transit := l_total_qty_in_transit + l_transit_detail.shipped_quantity;
564 end if;
565 end loop;
566 end if;
567 close c_transit_detail;
568 x_in_transit_qty := l_total_qty_in_transit;
569 --
570 -- Debug Statements
571 --
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.pop(l_module_name);
574 END IF;
575 --
576 exception
577 when invalid_org then
578 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
579 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_ORG');
580 WSH_UTIL_CORE.add_message (x_return_status);
581 --
582 -- Debug Statements
583 --
584 IF l_debug_on THEN
585 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_ORG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
586 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_ORG');
587 END IF;
588 --
589 when invalid_cust_site then
590 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
591 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_SITE');
592 WSH_UTIL_CORE.add_message (x_return_status);
593 --
594 -- Debug Statements
595 --
596 IF l_debug_on THEN
597 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_CUST_SITE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
598 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_CUST_SITE');
599 END IF;
600 --
601 when others then
602 wsh_util_core.default_handler('WSH_INTERFACE.Get_In_Transit_Qty');
603 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
604 --
605 -- Debug Statements
606 --
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
609 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
610 END IF;
611 --
612 end Get_In_Transit_Qty;
613
614
615
616 --bug 1569962
617 PROCEDURE Get_In_Transit_Qty(
618 p_source_code IN VARCHAR2 DEFAULT 'OE',
619 p_customer_id IN NUMBER,
620 p_ship_to_org_id IN NUMBER,
621 p_ship_from_org_id IN NUMBER,
622 p_inventory_item_id IN NUMBER,
623 p_order_header_id IN NUMBER,
624 p_shipper_recs IN t_shipper_rec,
625 p_schedule_generation_date IN DATE,
626 x_in_transit_qty OUT NOCOPY NUMBER,
627 x_return_status OUT NOCOPY VARCHAR2) IS
628
629 l_seq_num varchar2(1);
630 l_shipment_date date;
631 --
632 l_debug_on BOOLEAN;
633 --
634 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_IN_TRANSIT_QTY';
635 --
636 begin
637
638 --
639 -- Debug Statements
640 --
641 --
642 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
643 --
644 IF l_debug_on IS NULL
645 THEN
646 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
647 END IF;
648 --
649 IF l_debug_on THEN
650 WSH_DEBUG_SV.push(l_module_name);
651 --
652 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
653 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
654 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_ORG_ID',P_SHIP_TO_ORG_ID);
655 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG_ID',P_SHIP_FROM_ORG_ID);
656 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
657 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
658 WSH_DEBUG_SV.log(l_module_name,'P_SCHEDULE_GENERATION_DATE',P_SCHEDULE_GENERATION_DATE);
659 END IF;
660 --
661 l_seq_num:=NULL;
662 l_shipment_date:=NULL;
663
664 Get_In_Transit_Qty(
665 p_customer_id=> p_customer_id,
666 p_ship_to_org_id=>p_ship_to_org_id,
667 p_ship_from_org_id=>p_ship_from_org_id,
668 p_inventory_item_id=>p_inventory_item_id,
669 p_order_header_id=>p_order_header_id,
670 p_cust_production_seq_num=>l_seq_num,
671 p_shipper_recs=>p_shipper_recs,
672 p_schedule_generation_date=>p_schedule_generation_date,
673 p_shipment_date=>l_shipment_date,
674 x_in_transit_qty=>x_in_transit_qty,
675 x_return_status=>x_return_status);
676
677 --
678 -- Debug Statements
679 --
680 IF l_debug_on THEN
681 WSH_DEBUG_SV.pop(l_module_name);
682 END IF;
683 --
684 exception
685 when others then
686 wsh_util_core.default_handler('WSH_INTERFACE.Get_In_Transit_Qty');
687 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
688 --
689 -- Debug Statements
690 --
691 IF l_debug_on THEN
692 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
693 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
694 END IF;
695 --
696 end Get_In_Transit_Qty;
697
698
699
700
701 PROCEDURE Import_Delivery_Details (
702 errbuf OUT NOCOPY VARCHAR2,
703 retcode OUT NOCOPY VARCHAR2,
704 p_source_line_id IN NUMBER,
705 p_source_code IN VARCHAR2)
706 IS
707 l_source_line_id NUMBER;
708 l_rs VARCHAR2(1);
709 l_status VARCHAR2(10);
710 l_temp BOOLEAN;
711 --
712 l_debug_on BOOLEAN;
713 --
714 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IMPORT_DELIVERY_DETAILS';
715 --
716 BEGIN
717
718 --
719 -- Debug Statements
720 --
721 --
722 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
723 --
724 IF l_debug_on IS NULL
725 THEN
726 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
727 END IF;
728 --
729 IF l_debug_on THEN
730 WSH_DEBUG_SV.push(l_module_name);
731 --
732 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
733 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
734 END IF;
735 --
736 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
737 G_CALL_MODE := 'CONC';
738
739 IF p_source_line_id = -9999 THEN
740 l_source_line_id := NULL;
741 ELSE
742 l_source_line_id := p_source_line_id;
743 END IF;
744
745 --
746 -- Debug Statements
747 --
748 IF l_debug_on THEN
749 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
750 END IF;
751 --
752 WSH_USA_ACTIONS_PVT.Import_Delivery_Details(
753 p_source_line_id => l_source_line_id,
754 p_source_code => p_source_code,
755 x_return_status => l_rs);
756
757 IF l_rs = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
758 l_status := 'NORMAL';
759 errbuf := 'Import Delivery Details is completed successfully';
760 retcode := '0';
761 ELSIF l_rs = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
762 l_status := 'WARNING';
763 errbuf := 'Import Delivery Details is completed with warning';
764 retcode := '1';
765 ELSE
766 l_status := 'ERROR';
767 errbuf := 'Import Delivery Details is completed with error';
768 retcode := '2';
769 END IF;
770
771 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_status,'');
772
773 --
774 -- Debug Statements
775 --
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.pop(l_module_name);
778 END IF;
779 --
780 EXCEPTION
781 WHEN OTHERS THEN
782 --
783 -- Debug Statements
784 --
785 IF l_debug_on THEN
786 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.PRINTMSG',WSH_DEBUG_SV.C_PROC_LEVEL);
787 END IF;
788 --
789 WSH_INTERFACE.PrintMsg('Import Delivery Details failed with unexpected error ' || SQLCODE);
790 --
791 -- Debug Statements
792 --
793 IF l_debug_on THEN
794 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.PRINTMSG',WSH_DEBUG_SV.C_PROC_LEVEL);
795 END IF;
796 --
797 WSH_INTERFACE.PrintMsg('The unexpected error is ' || SQLERRM);
798 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
799 errbuf := 'Import Delivery Details is completed with error';
800 retcode := '2';
801
802 --
803 -- Debug Statements
804 --
805 IF l_debug_on THEN
806 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
807 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
808 END IF;
809 --
810 END IMPORT_DELIVERY_DETAILS;
811
812
813
814 PROCEDURE Default_Container(
815 p_delivery_detail_id IN NUMBER
816 , x_return_status OUT NOCOPY VARCHAR2
817 )
818 IS
819 CURSOR C_container
820 IS
821 SELECT mci.master_container_item_id, mci.detail_container_item_id
822 FROM mtl_customer_items mci, wsh_delivery_details dd, oe_order_lines_all ool
823 WHERE dd.delivery_detail_id = p_delivery_detail_id AND
824 dd.source_line_id = ool.line_id AND
825 mci.customer_item_id(+) = ool.ordered_item_id AND
826 ool.item_identifier_type = 'CUST';
827
828 l_master_container_item_id NUMBER;
829 l_detail_container_item_id NUMBER;
830 no_container EXCEPTION;
831 --
832 l_debug_on BOOLEAN;
833 --
834 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_CONTAINER';
835 --
836 BEGIN
837 --
838 -- Debug Statements
839 --
840 --
841 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
842 --
843 IF l_debug_on IS NULL
844 THEN
845 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
846 END IF;
847 --
848 IF l_debug_on THEN
849 WSH_DEBUG_SV.push(l_module_name);
850 --
851 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
852 END IF;
853 --
854 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
855
856 OPEN C_Container;
857 FETCH C_Container INTO l_master_container_item_id, l_detail_container_item_id;
858
859 IF (C_Container%NOTFOUND) THEN
860 CLOSE C_Container;
861 RAISE no_container;
862 ELSE
863 UPDATE wsh_delivery_details
864 SET master_container_item_id = l_master_container_item_id,
865 detail_container_item_id = l_detail_container_item_id
866 WHERE delivery_detail_id = p_delivery_detail_id;
867 CLOSE C_Container;
868 END IF;
869
870 --
871 -- Debug Statements
872 --
873 IF l_debug_on THEN
874 WSH_DEBUG_SV.pop(l_module_name);
875 END IF;
876 --
877 EXCEPTION
878 WHEN no_container THEN
879 --
880 -- Debug Statements
881 --
882 IF l_debug_on THEN
883 WSH_DEBUG_SV.logmsg(l_module_name,'NO_CONTAINER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
884 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_CONTAINER');
885 END IF;
886 --
887 RETURN;
888 --
889 WHEN others THEN
890 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
891 wsh_util_core.default_handler('WSH_INTERFACE.Default_Container');
892
893 --
894 -- Debug Statements
895 --
896 IF l_debug_on THEN
897 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
898 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
899 END IF;
900 --
901 END Default_Container;
902
903
904 PROCEDURE Populate_Detail_Info(
905 p_old_delivery_detail_info IN WSH_DELIVERY_DETAILS%ROWTYPE
906 , x_new_delivery_detail_info OUT NOCOPY wsh_glbl_var_strct_grp.delivery_details_rec_type
907 , x_return_status OUT NOCOPY VARCHAR2
908 )
909 IS
910 --
911 l_debug_on BOOLEAN;
912 --
913 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_DETAIL_INFO';
914 --
915 BEGIN
916 --
917 -- Debug Statements
918 --
919 --
920 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
921 --
922 IF l_debug_on IS NULL
923 THEN
924 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
925 END IF;
926 --
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.push(l_module_name);
929 WSH_DEBUG_SV.log(l_module_name,'SOURCE_CODE',p_old_delivery_detail_info.source_code);
930 WSH_DEBUG_SV.log(l_module_name,'SOURCE_HEADER_ID',p_old_delivery_detail_info.source_header_id);
931 END IF;
932 --
933 x_new_delivery_detail_info.source_code := p_old_delivery_detail_info.source_code;
934 x_new_delivery_detail_info.source_header_id := p_old_delivery_detail_info.source_header_id;
935 x_new_delivery_detail_info.source_line_id := p_old_delivery_detail_info.source_line_id;
936 x_new_delivery_detail_info.customer_id := p_old_delivery_detail_info.customer_id;
937 x_new_delivery_detail_info.sold_to_contact_id := p_old_delivery_detail_info.sold_to_contact_id;
938 x_new_delivery_detail_info.inventory_item_id := p_old_delivery_detail_info.inventory_item_id;
939 x_new_delivery_detail_info.item_description := p_old_delivery_detail_info.item_description;
940 x_new_delivery_detail_info.hazard_class_id := p_old_delivery_detail_info.hazard_class_id;
941 x_new_delivery_detail_info.country_of_origin := p_old_delivery_detail_info.country_of_origin;
942 x_new_delivery_detail_info.classification := p_old_delivery_detail_info.classification;
943 x_new_delivery_detail_info.ship_from_location_id := p_old_delivery_detail_info.ship_from_location_id;
944 x_new_delivery_detail_info.ship_to_site_use_id := p_old_delivery_detail_info.ship_to_site_use_id;
945 x_new_delivery_detail_info.deliver_to_site_use_id := p_old_delivery_detail_info.deliver_to_site_use_id;
946 x_new_delivery_detail_info.ship_to_location_id := p_old_delivery_detail_info.ship_to_location_id;
947 x_new_delivery_detail_info.deliver_to_location_id := p_old_delivery_detail_info.deliver_to_location_id;
948 x_new_delivery_detail_info.ship_to_contact_id := p_old_delivery_detail_info.ship_to_contact_id;
949 x_new_delivery_detail_info.deliver_to_contact_id := p_old_delivery_detail_info.deliver_to_contact_id;
950 x_new_delivery_detail_info.intmed_ship_to_location_id := p_old_delivery_detail_info.intmed_ship_to_location_id;
951 x_new_delivery_detail_info.intmed_ship_to_contact_id := p_old_delivery_detail_info.intmed_ship_to_contact_id;
952 x_new_delivery_detail_info.ship_tolerance_above := p_old_delivery_detail_info.ship_tolerance_above;
953 x_new_delivery_detail_info.ship_tolerance_below := p_old_delivery_detail_info.ship_tolerance_below;
954 x_new_delivery_detail_info.requested_quantity := 0;
955 x_new_delivery_detail_info.requested_quantity_uom := p_old_delivery_detail_info.requested_quantity_uom;
956
957 -- hwahdani start of OPM changes (Update_Shipping)
958 x_new_delivery_detail_info.requested_quantity2 := NULL;
959 x_new_delivery_detail_info.requested_quantity_uom2 := p_old_delivery_detail_info.requested_quantity_uom2;
960 -- hwahdani end of OPM changes (Update_Shipping)
961
962 x_new_delivery_detail_info.subinventory := p_old_delivery_detail_info.subinventory;
963 x_new_delivery_detail_info.customer_requested_lot_flag := p_old_delivery_detail_info.customer_requested_lot_flag;
964 x_new_delivery_detail_info.date_requested := p_old_delivery_detail_info.date_requested;
965 x_new_delivery_detail_info.date_scheduled := p_old_delivery_detail_info.date_scheduled;
966 x_new_delivery_detail_info.master_container_item_id := p_old_delivery_detail_info.master_container_item_id;
967 x_new_delivery_detail_info.detail_container_item_id := p_old_delivery_detail_info.detail_container_item_id;
968 x_new_delivery_detail_info.load_seq_number := p_old_delivery_detail_info.load_seq_number;
969 x_new_delivery_detail_info.ship_method_code := p_old_delivery_detail_info.ship_method_code;
970 x_new_delivery_detail_info.carrier_id := p_old_delivery_detail_info.carrier_id;
971 x_new_delivery_detail_info.freight_terms_code := p_old_delivery_detail_info.freight_terms_code;
972 x_new_delivery_detail_info.shipment_priority_code := p_old_delivery_detail_info.shipment_priority_code;
973 x_new_delivery_detail_info.fob_code := p_old_delivery_detail_info.fob_code;
974 x_new_delivery_detail_info.customer_item_id := p_old_delivery_detail_info.customer_item_id;
975 x_new_delivery_detail_info.dep_plan_required_flag := p_old_delivery_detail_info.dep_plan_required_flag;
976 x_new_delivery_detail_info.customer_prod_seq := p_old_delivery_detail_info.customer_prod_seq;
977 x_new_delivery_detail_info.customer_dock_code := p_old_delivery_detail_info.customer_dock_code;
978 x_new_delivery_detail_info.cust_model_serial_number := p_old_delivery_detail_info.cust_model_serial_number;
979 x_new_delivery_detail_info.customer_job := p_old_delivery_detail_info.customer_job;
980 x_new_delivery_detail_info.customer_production_line := p_old_delivery_detail_info.customer_production_line;
981 x_new_delivery_detail_info.net_weight := p_old_delivery_detail_info.net_weight;
982 x_new_delivery_detail_info.weight_uom_code := p_old_delivery_detail_info.weight_uom_code;
983 x_new_delivery_detail_info.volume := p_old_delivery_detail_info.volume;
984 x_new_delivery_detail_info.volume_uom_code := p_old_delivery_detail_info.volume_uom_code;
985 x_new_delivery_detail_info.released_flag := 'N';
986 x_new_delivery_detail_info.mvt_stat_status := p_old_delivery_detail_info.mvt_stat_status;
987 x_new_delivery_detail_info.organization_id := p_old_delivery_detail_info.organization_id;
988 x_new_delivery_detail_info.ship_set_id := p_old_delivery_detail_info.ship_set_id;
989 x_new_delivery_detail_info.arrival_set_id := p_old_delivery_detail_info.arrival_set_id;
990 x_new_delivery_detail_info.ship_model_complete_flag := p_old_delivery_detail_info.ship_model_complete_flag;
991 x_new_delivery_detail_info.top_model_line_id := p_old_delivery_detail_info.top_model_line_id;
992 x_new_delivery_detail_info.source_header_number := p_old_delivery_detail_info.source_header_number;
993 x_new_delivery_detail_info.source_header_type_id := p_old_delivery_detail_info.source_header_type_id;
994 x_new_delivery_detail_info.source_header_type_name := p_old_delivery_detail_info.source_header_type_name;
995 x_new_delivery_detail_info.cust_po_number := p_old_delivery_detail_info.cust_po_number;
996 x_new_delivery_detail_info.ato_line_id := p_old_delivery_detail_info.ato_line_id;
997 x_new_delivery_detail_info.src_requested_quantity := p_old_delivery_detail_info.src_requested_quantity;
998 x_new_delivery_detail_info.src_requested_quantity_uom := p_old_delivery_detail_info.src_requested_quantity_uom;
999 -- hwahdani start of OPM changes (Update_Shipping)
1000 x_new_delivery_detail_info.src_requested_quantity2 := p_old_delivery_detail_info.src_requested_quantity2;
1001 x_new_delivery_detail_info.src_requested_quantity_uom2 := p_old_delivery_detail_info.src_requested_quantity_uom2;
1002 x_new_delivery_detail_info.cancelled_quantity2 := p_old_delivery_detail_info.cancelled_quantity2;
1003 x_new_delivery_detail_info.preferred_grade := p_old_delivery_detail_info.preferred_grade;
1004 x_new_delivery_detail_info.lot_number := p_old_delivery_detail_info.lot_number;
1005 -- HW OPMCONV - No need for sublot_number
1006 --x_new_delivery_detail_info.sublot_number := p_old_delivery_detail_info.sublot_number;
1007 -- hwahdani end of OPM changes (Update_Shipping)
1008
1009 x_new_delivery_detail_info.move_order_line_id := p_old_delivery_detail_info.move_order_line_id;
1010 x_new_delivery_detail_info.cancelled_quantity := p_old_delivery_detail_info.cancelled_quantity;
1011 x_new_delivery_detail_info.tracking_number := p_old_delivery_detail_info.tracking_number;
1012 x_new_delivery_detail_info.movement_id := p_old_delivery_detail_info.movement_id;
1013 x_new_delivery_detail_info.shipping_instructions := p_old_delivery_detail_info.shipping_instructions;
1014 x_new_delivery_detail_info.packing_instructions := p_old_delivery_detail_info.packing_instructions;
1015 x_new_delivery_detail_info.project_id := p_old_delivery_detail_info.project_id;
1016 x_new_delivery_detail_info.task_id := p_old_delivery_detail_info.task_id;
1017 x_new_delivery_detail_info.org_id := p_old_delivery_detail_info.org_id;
1018 -- x_new_delivery_detail_info.oe_interfaced_flag := 'N';
1019 x_new_delivery_detail_info.split_from_detail_id := p_old_delivery_detail_info.split_from_delivery_detail_id;
1020 -- x_new_delivery_detail_info.inv_interfaced_flag := 'N';
1021 x_new_delivery_detail_info.source_line_number := p_old_delivery_detail_info.source_line_number;
1022 x_new_delivery_detail_info.released_status := 'N';
1023 x_new_delivery_detail_info.container_flag := 'N';
1024 x_new_delivery_detail_info.container_type_code := NULL;
1025 x_new_delivery_detail_info.container_name := NULL;
1026 x_new_delivery_detail_info.fill_percent := NULL;
1027 x_new_delivery_detail_info.gross_weight := NULL;
1028 x_new_delivery_detail_info.master_serial_number := NULL;
1029 x_new_delivery_detail_info.maximum_load_weight := NULL;
1030 x_new_delivery_detail_info.maximum_volume := NULL;
1031 x_new_delivery_detail_info.minimum_fill_percent := NULL;
1032 x_new_delivery_detail_info.seal_code := NULL;
1033 x_new_delivery_detail_info.mvt_stat_status := p_old_delivery_detail_info.mvt_stat_status;
1034 x_new_delivery_detail_info.unit_price := p_old_delivery_detail_info.unit_price;
1035 x_new_delivery_detail_info.currency_code := p_old_delivery_detail_info.currency_code;
1036 x_new_delivery_detail_info.inspection_flag := p_old_delivery_detail_info.inspection_flag;
1037 x_new_delivery_detail_info.lpn_id := p_old_delivery_detail_info.lpn_id ;
1038 -- x_new_delivery_detail_info.attribute15 := p_old_delivery_detail_info.attribute15 ; -- 1561078
1039 x_new_delivery_detail_info.original_subinventory := p_old_delivery_detail_info.original_subinventory ;
1040 x_new_delivery_detail_info.pickable_flag := p_old_delivery_detail_info.pickable_flag ;
1041 IF (x_new_delivery_detail_info.source_code = 'OE') THEN
1042 x_new_delivery_detail_info.oe_interfaced_flag := 'N' ;
1043 IF (x_new_delivery_detail_info.pickable_flag = 'Y') THEN
1044 x_new_delivery_detail_info.inv_interfaced_flag := 'N';
1045 ELSE
1046 x_new_delivery_detail_info.inv_interfaced_flag := 'X';
1047 END IF;
1048 ELSE
1049 x_new_delivery_detail_info.inv_interfaced_flag := 'N';
1050 x_new_delivery_detail_info.oe_interfaced_flag := 'X';
1051 END IF;
1052
1053 -- anxsharm Bug 2181132
1054 x_new_delivery_detail_info.source_line_set_id :=
1055 p_old_delivery_detail_info.source_line_set_id ;
1056 --
1057 -- Debug Statements
1058 --
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.pop(l_module_name);
1061 END IF;
1062 --
1063 END Populate_Detail_Info;
1064
1065 --
1066 --Procedure: Process_Details
1067 --Parameters
1068 -- p_details_id : Table of delivery details
1069 -- p_cancel_delete_flag : 'C'(Cancel)/'D'(Delete)
1070 -- x_return_status : Return Status
1071 --Description:
1072 -- This procedure cancels or deletes the specified delivery details depending on p_cancel_delete_flag
1073 -- If p_cancel_delete_flag is 'D' and source_code is WSH, the API will Cancel the delivery details although
1074 -- p_cancel_delete_flag is specified as 'D'
1075
1076 PROCEDURE Process_Details(
1077 p_details_id IN WSH_UTIL_CORE.Id_Tab_Type,
1078 p_cancel_delete_flag IN VARCHAR2,
1079 x_return_status OUT NOCOPY VARCHAR2) IS
1080
1081 CURSOR c_assignment(c_detail_id NUMBER) IS
1082 SELECT delivery_assignment_id,
1083 parent_delivery_detail_id,
1084 delivery_id
1085 FROM wsh_delivery_assignments_v
1086 WHERE delivery_detail_id = c_detail_id;
1087 l_assign_rec c_assignment%ROWTYPE;
1088
1089 -- HW OPMCONV - Added Qty2
1090 CURSOR c_details(c_detail_id NUMBER) IS
1091 SELECT delivery_detail_id,
1092 organization_id,
1093 ship_from_location_id,
1094 inventory_item_id,
1095 requested_quantity,
1096 picked_quantity,
1097 requested_quantity2,
1098 picked_quantity2,
1099 move_order_line_id,
1100 released_status,
1101 source_code,
1102 container_flag,
1103 source_line_id -- Column added for Bug 5741373
1104 FROM wsh_delivery_details
1105 WHERE delivery_detail_id = c_detail_id;
1106
1107 l_detail_rec c_details%ROWTYPE;
1108
1109 l_return_status VARCHAR2(30);
1110 l_exception_msg_count NUMBER;
1111 l_exception_msg_data VARCHAR2(2000);
1112 l_planned_flag VARCHAR2(1);
1113 l_exception_return_status VARCHAR2(30);
1114 l_exception_location_id VARCHAR2(30);
1115 l_dummy_exception_id VARCHAR2(30);
1116 l_container_name VARCHAR2(30);
1117 l_exception_error_message VARCHAR2(2000) := NULL;
1118 l_move_line_id NUMBER;
1119 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1120 l_trolin_old_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1121 l_trolin_out_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1122 l_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type;
1123 l_trolin_table_id NUMBER;
1124 l_mo_line_msg_count NUMBER;
1125 l_mo_line_msg_data VARCHAR2(2000) := NULL;
1126 process_move_order_failed EXCEPTION;
1127 l_message VARCHAR2(2000) := NULL;
1128 -- hwahdani BUG#:1565518
1129 l_msg VARCHAR2(2000) := NULL;
1130 -- HW OPM for OM changes
1131 -- HW OPMCONV - Removed OPM variables
1132 -- HW OPM changes for NOCOPY. BUG#:2694418
1133
1134 l_commit VARCHAR2(1);
1135 l_msg_count NUMBER;
1136 l_msg_data VARCHAR2(3000);
1137 l_api_version_number NUMBER := 1.0;
1138 l_freight_cost_count NUMBER := 0;
1139 WSH_DEL_RESERVATION_FAILED EXCEPTION;
1140 WSH_DELETE_DETAIL_FAILED EXCEPTION;
1141 WSH_UNASSIGN_DETAIL_FAILED EXCEPTION;
1142
1143 /* H integration: mark WSH lines as cancelled wrudge */
1144 l_cancel_dds WSH_UTIL_CORE.Id_Tab_Type;
1145
1146 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1147 -- K LPN CONV. rv
1148 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
1149 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
1150 -- K LPN CONV. rv
1151
1152 -- Bug 5741373
1153 l_move_order_line_ids WSH_UTIL_CORE.Id_Tab_Type;
1154 l_source_line_ids WSH_UTIL_CORE.Id_Tab_Type;
1155 --
1156 l_debug_on BOOLEAN;
1157 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_DETAILS';
1158 l_notfound BOOLEAN;
1159
1160 BEGIN
1161
1162 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1163 IF l_debug_on IS NULL
1164 THEN
1165 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1166 END IF;
1167 IF l_debug_on THEN
1168 WSH_DEBUG_SV.push(l_module_name);
1169 WSH_DEBUG_SV.log(l_module_name,'COUNT of Details',p_details_id.count);
1170 WSH_DEBUG_SV.log(l_module_name,'p_cancel_delete_flag',p_cancel_delete_flag);
1171 END IF;
1172 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1173
1174 FOR i IN 1 .. p_details_id.COUNT
1175 LOOP
1176 OPEN c_assignment(p_details_id(i));
1177 FETCH c_assignment INTO l_assign_rec;
1178 l_notfound := c_assignment%NOTFOUND;
1179 CLOSE c_assignment;
1180
1181 OPEN c_details(p_details_id(i));
1182 FETCH c_details INTO l_detail_rec;
1183 l_notfound := l_notfound AND c_details%NOTFOUND;
1184 CLOSE c_details;
1185
1186 IF (l_notfound) THEN
1187 IF l_debug_on THEN
1188 WSH_DEBUG_SV.logmsg(l_module_name, 'NO ASSIGNMENT RECORDS ARE FOUND' );
1189 END IF;
1190 GOTO loop_end; -- maybe already deleted.
1191 END IF;
1192
1193 -- HW OPMCONV - Removed branching
1194
1195 IF (l_assign_rec.parent_delivery_detail_id IS NOT NULL) THEN
1196 -- if delivery line is packed, log exception for it
1197
1198 SELECT container_name
1199 INTO l_container_name
1200 FROM wsh_delivery_details
1201 WHERE delivery_detail_id = l_assign_rec.parent_delivery_detail_id;
1202
1203 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_PACKING');
1204 l_msg := FND_MESSAGE.GET;
1205 l_exception_location_id := l_detail_rec.ship_from_location_id;
1206
1207 IF l_debug_on THEN
1208 WSH_DEBUG_SV.logmsg(l_module_name,'Line is packed.',WSH_DEBUG_SV.C_PROC_LEVEL);
1209 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1210 END IF;
1211
1212 wsh_xc_util.log_exception(
1213 p_api_version => 1.0,
1214 x_return_status => l_exception_return_status,
1215 x_msg_count => l_exception_msg_count,
1216 x_msg_data=> l_exception_msg_data,
1217 x_exception_id=> l_dummy_exception_id ,
1218 p_logged_at_location_id => l_exception_location_id,
1219 p_exception_location_id => l_exception_location_id,
1220 p_logging_entity => 'SHIPPER',
1221 p_logging_entity_id => FND_GLOBAL.USER_ID,
1222 p_exception_name => 'WSH_INVALID_PACKING',
1223 p_message => l_msg,
1224 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1225 p_delivery_assignment_id => l_assign_rec.delivery_assignment_id,
1226 p_container_name => l_container_name,
1227 p_inventory_item_id => l_detail_rec.inventory_item_id,
1228 p_quantity=> l_detail_rec.requested_quantity,
1229 p_error_message => l_exception_error_message
1230 );
1231
1232 -- bug 2948940: it is OK to delete the line even when it is packed.
1233 -- the exception will alert the user that the container needs updating.
1234 -- continue flow, regardless of log_exception's return status
1235
1236 IF l_debug_on THEN
1237 WSH_DEBUG_SV.logmsg(l_module_name,'l_exception_return_status=' || l_exception_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
1238 END IF;
1239
1240 ELSIF (l_assign_rec.delivery_id IS NOT NULL) THEN
1241 -- if delivery line is assigned to delivery and delivery is planned,
1242 -- log exception for it
1243 SELECT planned_flag
1244 INTO l_planned_flag
1245 FROM wsh_new_deliveries
1246 WHERE delivery_id = l_assign_rec.delivery_id;
1247
1248 IF (NVL(l_planned_flag, 'N') IN ('Y','F')) THEN
1249 -- hwahdani BUG#:1565518
1250 l_msg := NULL;
1251 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_DELIVERY_PLANNING');
1252 l_msg := FND_MESSAGE.GET;
1253 l_exception_location_id := l_detail_rec.ship_from_location_id;
1254
1255 IF l_debug_on THEN
1256 WSH_DEBUG_SV.logmsg(l_module_name,'Line is in planned delivery.',WSH_DEBUG_SV.C_PROC_LEVEL);
1257 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1258 END IF;
1259
1260 wsh_xc_util.log_exception(
1261 p_api_version => 1.0,
1262 x_return_status => l_exception_return_status,
1263 x_msg_count => l_exception_msg_count,
1264 x_msg_data=> l_exception_msg_data,
1265 x_exception_id=> l_dummy_exception_id ,
1266 p_logged_at_location_id => l_exception_location_id,
1267 p_exception_location_id => l_exception_location_id,
1268 p_logging_entity => 'SHIPPER',
1269 p_logging_entity_id => FND_GLOBAL.USER_ID,
1270 p_exception_name => 'WSH_INVALID_DELIVERY_PLANNING',
1271 p_message => l_msg,
1272 p_delivery_id => l_assign_rec.delivery_id,
1273 p_error_message => l_exception_error_message
1274 );
1275
1276 -- bug 2948940: it is OK to delete the line even when it is packed.
1277 -- the exception will alert the user that the container needs updating.
1278 -- continue flow, regardless of log_exception's return status
1279
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.logmsg(l_module_name,'l_exception_return_status=' || l_exception_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
1282 END IF;
1283 END IF;
1284 END IF;
1285
1286 -- HW OPMCONV - Removed branching
1287
1288 IF l_detail_rec.move_order_line_id IS NOT NULL AND
1289 l_detail_rec.released_status = 'S' THEN
1290 IF l_debug_on THEN
1291 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_TROLIN_UTIL.QUERY_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
1292 END IF;
1293
1294 -- Added for bug 5741373
1295 l_move_order_line_ids(l_move_order_line_ids.count+1) := l_detail_rec.move_order_line_id;
1296 l_source_line_ids(l_source_line_ids.count+1) := l_detail_rec.source_line_id;
1297 --
1298 l_trolin_rec := INV_TROLIN_UTIL.Query_Row(p_line_id => l_detail_rec.move_order_line_id);
1299 l_trolin_table_id := l_trolin_tbl.count + 1;
1300 l_trolin_tbl(l_trolin_table_id) := l_trolin_rec;
1301 l_trolin_tbl(l_trolin_table_id).OPERATION := INV_GLOBALS.G_OPR_DELETE;
1302 l_trolin_old_tbl(l_trolin_table_id) := l_trolin_tbl(l_trolin_table_id);
1303
1304 IF l_debug_on THEN
1305 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE MOVE ORDER LINE '||L_TROLIN_REC.LINE_ID );
1306 END IF;
1307 END IF;
1308
1309 -- HW OPMCONV - Removed branching
1310
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
1313 END IF;
1314
1315 -- HW OPMCONV - Added Qty2
1316 WSH_DELIVERY_DETAILS_ACTIONS.Unreserve_delivery_detail(
1317 p_delivery_detail_id => p_details_id(i),
1318 p_quantity_to_unreserve => NVL(l_detail_rec.picked_quantity, l_detail_rec.requested_quantity),
1319 p_quantity2_to_unreserve => NVL(l_detail_rec.picked_quantity2, l_detail_rec.requested_quantity2),
1320 p_unreserve_mode => 'UNRESERVE',
1321 p_override_retain_ato_rsv => 'Y', -- 2747520
1322 x_return_status => l_return_status);
1323
1324 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1325 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1326 IF l_debug_on THEN
1327 WSH_DEBUG_SV.logmsg(l_module_name, 'UNRESERVE DELIVERY DETAIL '|| P_DETAILS_ID ( I ) || ' FAILED' );
1328 END IF;
1329 raise WSH_DEL_RESERVATION_FAILED;
1330 END IF;
1331
1332 /* H integration: 940/945 cancel 'WSH' line, not delete it wrudge */
1333 IF l_detail_rec.source_code = 'WSH' AND
1334 l_detail_rec.container_flag = 'N' THEN
1335
1336 l_cancel_dds( l_cancel_dds.count+1 ) := p_details_id(i);
1337 ELSE
1338 -- J: W/V Changes
1339 IF (l_assign_rec.parent_delivery_detail_id IS NOT NULL) THEN
1340 IF l_debug_on THEN
1341 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
1342 END IF;
1343
1344 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont(
1345 p_detail_id => p_details_id(i),
1346 p_validate_flag => 'N',
1347 x_return_status => l_return_status);
1348
1349 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1350 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1351
1352 IF l_debug_on THEN
1353 WSH_DEBUG_SV.logmsg(l_module_name, 'UNASSIGN DETAIL FROM CONTAINER FAILED FOR DD '|| P_DETAILS_ID ( I ));
1354 END IF;
1355 raise WSH_UNASSIGN_DETAIL_FAILED;
1356 END IF;
1357 END IF;
1358
1359 IF (l_assign_rec.delivery_id IS NOT NULL) THEN
1360 IF l_debug_on THEN
1361 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1362 END IF;
1363
1364 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Delivery(
1365 p_detail_id => p_details_id(i),
1366 p_validate_flag => 'N',
1367 x_return_status => l_return_status);
1368
1369 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1370 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1371
1372 IF l_debug_on THEN
1373 WSH_DEBUG_SV.logmsg(l_module_name, 'UNASSIGN DETAIL FROM DELIVERY FAILED FOR DD '|| P_DETAILS_ID ( I ));
1374 END IF;
1375 raise WSH_UNASSIGN_DETAIL_FAILED;
1376 END IF;
1377 END IF;
1378
1379 IF (p_cancel_delete_flag = 'C') THEN
1380 IF l_debug_on THEN
1381 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to CANCEL',WSH_DEBUG_SV.C_PROC_LEVEL);
1382 END IF;
1383
1384 WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
1385 p_delivery_detail_id => p_details_id(i),
1386 p_cancel_flag => 'Y',
1387 x_return_status => l_return_status );
1388
1389 ELSE -- delete details
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
1392 END IF;
1393
1394 WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
1395 p_delivery_detail_id => p_details_id(i),
1396 p_cancel_flag => 'N',
1397 x_return_status => l_return_status );
1398
1399 END IF;
1400 -- End J: W/V Changes
1401
1402 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1403 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1404
1405 IF l_debug_on THEN
1406 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE DELIVERY DETAIL '|| P_DETAILS_ID ( I ) || ' FAILED' );
1407 END IF;
1408 raise WSH_DELETE_DETAIL_FAILED;
1409 END IF;
1410
1411 END IF;
1412
1413 <<loop_end>>
1414 NULL;
1415 END LOOP;
1416
1417 -- Moved the call to process_move_order_line outside the loop.
1418 -- during bug fix 1785691.
1419 -- We call process_move_order_line only if the l_trolin_tab count is
1420 -- greater than zero, meaning there should be atleast
1421 -- one delivery detail with released status 'S'
1422
1423 -- HW OPMCONV - Removed branching
1424
1425 -- Bug 5741373 : Modified the below IF condition
1426 -- Also, call INV Cancel_Move_Order_Line API instead of Process_Move_Order_Line
1427 --
1428 IF (l_move_order_line_ids.count >0) THEN
1429 --{
1430 FOR i in 1..l_move_order_line_ids.count
1431 LOOP
1432 --{
1433 IF l_debug_on THEN
1434 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.Cancel_Move_Order_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
1435 WSH_DEBUG_SV.log(l_module_name, 'Move Order Line ID', l_move_order_line_ids(i));
1436 WSH_DEBUG_SV.log(l_module_name, 'Source Line ID', l_source_line_ids(i));
1437 END IF;
1438 --
1439 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(
1440 x_return_status => l_return_status,
1441 x_msg_count => l_mo_line_msg_count,
1442 x_msg_data => l_mo_line_msg_data,
1443 p_line_id => l_move_order_line_ids(i),
1444 p_delete_reservations => 'Y',
1445 p_txn_source_line_id => l_source_line_ids(i) );
1446 --
1447 IF l_debug_on THEN
1448 WSH_DEBUG_SV.logmsg(l_module_name, 'RETURN_STATUS FROM Cancel_Move_Order_line IS '||L_RETURN_STATUS );
1449 END IF;
1450
1451 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1452 --{
1453 FOR j IN 1..l_mo_line_msg_count
1454 LOOP
1455 --{
1456 l_message := FND_MSG_PUB.Get(j, 'F');
1457 l_message := replace(l_message, chr(0), ' ');
1458 IF l_debug_on THEN
1459 WSH_DEBUG_SV.logmsg(l_module_name, L_MESSAGE );
1460 END IF;
1461 --}
1462 END LOOP;
1463 RAISE Process_Move_Order_Failed;
1464 --}
1465 END IF;
1466 --}
1467 END LOOP;
1468 --}
1469 END IF;
1470 -- HW OPMCONV - Removed branching
1471
1472 /* H integration: 940/945 cancel lines wrudge */
1473 IF l_cancel_dds.count > 0 THEN
1474 -- UPDATE is copied/modified from Backorder API
1475 FORALL i IN 1..l_cancel_dds.count
1476 UPDATE wsh_delivery_details
1477 SET move_order_line_id = NULL ,
1478 released_status = 'D',
1479 cycle_count_quantity = NULL,
1480 cycle_count_quantity2 = NULL,
1481 shipped_quantity = NULL,
1482 shipped_quantity2 = NULL,
1483 picked_quantity = NULL,
1484 picked_quantity2 = NULL,
1485 subinventory = NULL,
1486 inv_interfaced_flag = NULL,
1487 oe_interfaced_flag = NULL,
1488 locator_id = NULL,
1489 preferred_grade = NULL,
1490 -- HW OPMCONV - No need for sublot_number
1491 -- sublot_number = NULL,
1492 lot_number = NULL,
1493 revision = null ,
1494 tracking_number = NULL
1495 WHERE delivery_detail_id = l_cancel_dds(i);
1496 -- delivery assignment records are not modified for source_code WSH.
1497 --
1498 -- Use l_cancel_dds to pass as table of delivery detail ids
1499 -- DBI Project
1500 -- Update of wsh_delivery_details where requested_quantity/released_status
1501 -- are changed, call DBI API after the update.
1502 -- DBI API will check if DBI is installed
1503 IF l_debug_on THEN
1504 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_cancel_dds.count);
1505 END IF;
1506 WSH_INTEGRATION.DBI_Update_Detail_Log
1507 (p_delivery_detail_id_tab => l_cancel_dds,
1508 p_dml_type => 'UPDATE',
1509 x_return_status => l_dbi_rs);
1510
1511 IF l_debug_on THEN
1512 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1513 END IF;
1514 -- Only Handle Unexpected error
1515 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1516 x_return_status := l_dbi_rs;
1517 -- just pass this Unexpected error to calling API
1518 -- since there is no further code flow in this procedure, no need to RETURN
1519 -- just continue
1520 END IF;
1521 -- all others are same as success
1522 -- End of Code for DBI Project
1523 --
1524
1525 END IF;
1526 --
1527 -- K LPN CONV. rv
1528 --
1529 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1530 THEN
1531 --{
1532 IF l_debug_on THEN
1533 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);
1534 END IF;
1535
1536 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1537 (
1538 p_in_rec => l_lpn_in_sync_comm_rec,
1539 x_return_status => l_return_status,
1540 x_out_rec => l_lpn_out_sync_comm_rec
1541 );
1542 --
1543 --
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1546 END IF;
1547 --
1548 --
1549 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
1550 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1551 THEN
1552 --
1553 x_return_status := l_return_status;
1554 --
1555 ELSIF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1556 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1557 THEN
1558 --
1559 x_return_status := l_return_status;
1560 --
1561 END IF;
1562 --}
1563 END IF;
1564 --
1565 -- K LPN CONV. rv
1566 --
1567
1568 IF l_debug_on THEN
1569 WSH_DEBUG_SV.pop(l_module_name);
1570 END IF;
1571
1572 EXCEPTION
1573
1574 WHEN WSH_DELETE_DETAIL_FAILED THEN
1575 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1576 --
1577 -- K LPN CONV. rv
1578 --
1579 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1580 THEN
1581 --{
1582 IF l_debug_on THEN
1583 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);
1584 END IF;
1585
1586 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1587 (
1588 p_in_rec => l_lpn_in_sync_comm_rec,
1589 x_return_status => l_return_status,
1590 x_out_rec => l_lpn_out_sync_comm_rec
1591 );
1592 --
1593 --
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1596 END IF;
1597 --
1598 --
1599 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1600 x_return_status := l_return_status;
1601 END IF;
1602 --
1603 --}
1604 END IF;
1605 --
1606 -- K LPN CONV. rv
1607 --
1608
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELETE_DETAIL_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1611 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELETE_DETAIL_FAILED');
1612 END IF;
1613 IF c_assignment%ISOPEN THEN
1614 CLOSE c_assignment;
1615 END IF;
1616 IF c_details%ISOPEN THEN
1617 CLOSE c_details;
1618 END IF;
1619
1620 WHEN WSH_DEL_RESERVATION_FAILED THEN
1621 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1622 --
1623 -- K LPN CONV. rv
1624 --
1625 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1626 THEN
1627 --{
1628 IF l_debug_on THEN
1629 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);
1630 END IF;
1631
1632 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1633 (
1634 p_in_rec => l_lpn_in_sync_comm_rec,
1635 x_return_status => l_return_status,
1636 x_out_rec => l_lpn_out_sync_comm_rec
1637 );
1638 --
1639 --
1640 IF l_debug_on THEN
1641 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1642 END IF;
1643 --
1644 --
1645 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1646 x_return_status := l_return_status;
1647 END IF;
1648 --
1649 --}
1650 END IF;
1651 --
1652 -- K LPN CONV. rv
1653 --
1654
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DEL_RESERVATION_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1657 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DEL_RESERVATION_FAILED');
1658 END IF;
1659 IF c_assignment%ISOPEN THEN
1660 CLOSE c_assignment;
1661 END IF;
1662 IF c_details%ISOPEN THEN
1663 CLOSE c_details;
1664 END IF;
1665 --
1666 WHEN OTHERS THEN
1667 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1668 wsh_util_core.default_handler('WSH_INTERFACE.PROCESS_DETAILS');
1669 IF l_debug_on THEN
1670 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1671 END IF;
1672 --
1673 -- K LPN CONV. rv
1674 --
1675 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1676 THEN
1677 --{
1678 IF l_debug_on THEN
1679 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);
1680 END IF;
1681
1682 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1683 (
1684 p_in_rec => l_lpn_in_sync_comm_rec,
1685 x_return_status => l_return_status,
1686 x_out_rec => l_lpn_out_sync_comm_rec
1687 );
1688 --
1689 --
1690 IF l_debug_on THEN
1691 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1692 END IF;
1693 --
1694 --
1695 --}
1696 END IF;
1697 --
1698 -- K LPN CONV. rv
1699 --
1700
1701 IF l_debug_on THEN
1702 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1703 END IF;
1704 IF c_assignment%ISOPEN THEN
1705 CLOSE c_assignment;
1706 END IF;
1707 IF c_details%ISOPEN THEN
1708 CLOSE c_details;
1709 END IF;
1710
1711 END Process_Details;
1712
1713 --
1714 --Procedure: Delete_Details
1715 --Parameters
1716 -- p_details_id : Table of delivery details
1717 -- x_return_status : Return Status
1718 --Description:
1719 -- This procedure calls private API process_details with p_cancel_delete_flag 'D'
1720 -- to delete the delivery details if source_code is 'OE' and cancel delivery details
1721 -- if source_code is 'WSH'
1722
1723 PROCEDURE Delete_Details(
1724 p_details_id IN WSH_UTIL_CORE.Id_Tab_Type,
1725 x_return_status OUT NOCOPY VARCHAR2) IS
1726
1727 l_debug_on BOOLEAN;
1728 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DETAILS';
1729
1730 BEGIN
1731 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1732 IF l_debug_on IS NULL
1733 THEN
1734 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1735 END IF;
1736 IF l_debug_on THEN
1737 WSH_DEBUG_SV.push(l_module_name);
1738 WSH_DEBUG_SV.log(l_module_name,'COUNT of Details',p_details_id.count);
1739 END IF;
1740 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1741
1742 process_details(
1743 p_details_id => p_details_id,
1744 p_cancel_delete_flag => 'D',
1745 x_return_status => x_return_status);
1746
1747 IF l_debug_on THEN
1748 WSH_DEBUG_SV.pop(l_module_name);
1749 END IF;
1750
1751 EXCEPTION
1752 WHEN OTHERS THEN
1753 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1754 wsh_util_core.default_handler('WSH_INTERFACE.DELETE_DETAILS');
1755
1756 IF l_debug_on THEN
1757 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1758 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1759 END IF;
1760
1761 END Delete_Details;
1762
1763 --Procedure: Cancel_Details
1764 --Parameters
1765 -- p_details_id : Table of delivery details
1766 -- x_return_status : Return Status
1767 --Description:
1768 -- This procedure calls private API process_details with p_cancel_delete_flag 'C'
1769 -- to cancel the delivery details
1770
1771 PROCEDURE Cancel_Details(
1772 p_details_id IN WSH_UTIL_CORE.Id_Tab_Type,
1773 x_return_status OUT NOCOPY VARCHAR2) IS
1774
1775 l_debug_on BOOLEAN;
1776 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_DETAILS';
1777
1778 --Bugfix 4070732
1779 l_return_status VARCHAR2(32767);
1780 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1781 l_reset_flags BOOLEAN;
1782
1783 BEGIN
1784 -- Bugfix 4070732
1785 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
1786 THEN
1787 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
1788 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
1789 END IF;
1790 -- End of Code Bugfix 4070732
1791 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1792 IF l_debug_on IS NULL
1793 THEN
1794 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1795 END IF;
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.push(l_module_name);
1798 WSH_DEBUG_SV.log(l_module_name,'COUNT of Details',p_details_id.count);
1799 END IF;
1800 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1801
1802 process_details(
1803 p_details_id => p_details_id,
1804 p_cancel_delete_flag => 'C',
1805 x_return_status => x_return_status);
1806
1807 --
1808 -- Start code for Bugfix 4070732
1809 --
1810 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1811 AND UPPER(WSH_UTIL_CORE.G_START_OF_SESSION_API) = UPPER(l_api_session_name) THEN
1812 --{
1813 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1814 --{
1815 IF l_debug_on THEN
1816 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1817 END IF;
1818
1819 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1820 x_return_status => l_return_status);
1821
1822
1823 IF l_debug_on THEN
1824 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1825 END IF;
1826
1827 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1828 x_return_status := l_return_status;
1829 END IF;
1830 --}
1831 END IF;
1832 --}
1833 ELSIF UPPER(WSH_UTIL_CORE.G_START_OF_SESSION_API) = UPPER(l_api_session_name) THEN
1834 --{
1835 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1836 --{
1837 l_reset_flags := TRUE;
1838
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1841 END IF;
1842
1843 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1844 x_return_status => l_return_status);
1845
1846 IF l_debug_on THEN
1847 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1848 END IF;
1849
1850 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1851 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1852 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1853 x_return_status := l_return_status;
1854 END IF;
1855 --}
1856 END IF;
1857 --}
1858 END IF;
1859 --
1860 -- End of Code Bugfix 4070732
1861 --
1862 IF l_debug_on THEN
1863 WSH_DEBUG_SV.pop(l_module_name);
1864 END IF;
1865
1866 EXCEPTION
1867 WHEN OTHERS THEN
1868 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1869 wsh_util_core.default_handler('WSH_INTERFACE.CANCEL_DETAILS');
1870 --
1871 -- Start code for Bugfix 4070732
1872 --
1873 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1874 --{
1875 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1876 --{
1877 IF l_debug_on THEN
1878 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1879 END IF;
1880
1881 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1882 x_return_status => l_return_status);
1883
1884
1885 IF l_debug_on THEN
1886 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1887 END IF;
1888
1889 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1890 x_return_status := l_return_status;
1891 END IF;
1892 --}
1893 END IF;
1894 --}
1895 END IF;
1896 --
1897 -- End of Code Bugfix 4070732
1898 --
1899
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1902 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1903 END IF;
1904
1905 END Cancel_Details;
1906
1907 PROCEDURE Get_Max_Load_Qty(
1908 p_move_order_line_id IN NUMBER,
1909 x_max_load_quantity OUT NOCOPY NUMBER,
1910 x_container_item_id OUT NOCOPY NUMBER,
1911 x_return_status OUT NOCOPY VARCHAR2) IS
1912
1913 get_max_load_qty_failed EXCEPTION;
1914 l_container_item_id number ;
1915 l_inventory_item_id number ;
1916 l_max_load_quantity number ;
1917
1918 --bug # 3259762
1919 l_item_name VARCHAR2(2000);
1920 l_org_name VARCHAR2(240);
1921 --
1922
1923 CURSOR c_details IS
1924 select detail_container_item_id , inventory_item_id , organization_id
1925 from wsh_delivery_Details
1926 where move_order_line_id = p_move_order_line_id
1927 and nvl(line_direction, 'O') IN ('O', 'IO')
1928 and rownum = 1 ;
1929
1930 l_detail_rec c_details%ROWTYPE;
1931
1932 --
1933 l_debug_on BOOLEAN;
1934 --
1935 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MAX_LOAD_QTY';
1936 --
1937 BEGIN
1938 --
1939 -- Debug Statements
1940 --
1941 --
1942 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1943 --
1944 IF l_debug_on IS NULL
1945 THEN
1946 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1947 END IF;
1948 --
1949 IF l_debug_on THEN
1950 WSH_DEBUG_SV.push(l_module_name);
1951 --
1952 WSH_DEBUG_SV.log(l_module_name,'P_MOVE_ORDER_LINE_ID',P_MOVE_ORDER_LINE_ID);
1953 END IF;
1954 --
1955 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1956
1957 OPEN c_details;
1958 FETCH c_details INTO l_detail_rec;
1959
1960 IF (c_details%NOTFOUND) THEN
1961 CLOSE c_details;
1962 RAISE get_max_load_qty_failed ;
1963 ELSE
1964
1965 -- bug # 3259762
1966 l_item_name := WSH_UTIL_CORE.Get_Item_Name(l_detail_rec.inventory_item_id, l_detail_rec.organization_id);
1967 l_org_name := WSH_UTIL_CORE.Get_Org_Name(l_detail_rec.organization_id);
1968 --
1969 if l_detail_rec.detail_container_item_id is not null then
1970
1971 l_container_item_id := l_detail_rec.detail_container_item_id ;
1972
1973 select max_load_quantity
1974 into l_max_load_quantity
1975 from wsh_container_items
1976 where container_item_id = l_detail_rec.detail_container_item_id
1977 and nvl ( load_item_id , l_detail_rec.inventory_item_id ) = l_detail_rec.inventory_item_id
1978 and master_organization_id = l_detail_rec.organization_id
1979 and rownum = 1 ;
1980
1981 else
1982
1983 select max_load_quantity , container_item_id
1984 into l_max_load_quantity , l_container_item_id
1985 from wsh_container_items
1986 where load_item_id = l_detail_rec.inventory_item_id
1987 and preferred_flag ='Y'
1988 and master_organization_id = l_detail_rec.organization_id
1989 and rownum = 1 ;
1990
1991 end if ;
1992
1993 x_max_load_quantity := l_max_load_quantity ;
1994 x_container_item_id := l_container_item_id ;
1995
1996 END IF;
1997
1998 --
1999 -- Debug Statements
2000 --
2001 IF l_debug_on THEN
2002 WSH_DEBUG_SV.pop(l_module_name);
2003 END IF;
2004 --
2005 EXCEPTION
2006 WHEN get_max_load_qty_failed THEN
2007 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2008 --
2009 -- Debug Statements
2010 --
2011 IF l_debug_on THEN
2012 WSH_DEBUG_SV.logmsg(l_module_name,'GET_MAX_LOAD_QTY_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2013 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GET_MAX_LOAD_QTY_FAILED');
2014 END IF;
2015 --
2016 RETURN;
2017 --
2018
2019 --bug # 3259762
2020 WHEN NO_DATA_FOUND THEN
2021 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2022 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONT_LOAD');
2023 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
2024 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
2025 --
2026 -- Debug Statements
2027 --
2028 IF l_debug_on THEN
2029 WSH_DEBUG_SV.logmsg(l_module_name,'The item ' || l_item_name || ' does not have a preferred container load relationship in the organization '|| l_org_name ,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2030 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2031 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
2032 END IF;
2033 --
2034 RETURN;
2035 --
2036
2037
2038 WHEN others THEN
2039 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2040 wsh_util_core.default_handler('WSH_INTERFACE.Get_Max_Load_Qty');
2041
2042 --
2043 -- Debug Statements
2044 --
2045 IF l_debug_on THEN
2046 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2047 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2048 END IF;
2049 --
2050 END Get_Max_Load_Qty;
2051
2052
2053
2054 PROCEDURE Lock_Records(
2055 p_source_code IN VARCHAR2,
2056 p_changed_attributes IN ChangedAttributeTabType,
2057 x_interface_flag OUT NOCOPY VARCHAR2,
2058 x_return_status OUT NOCOPY VARCHAR2
2059 ) IS
2060
2061 CURSOR c_source_line_to_lock(x_source_line_id NUMBER,
2062 x_neg_source_line_id NUMBER,
2063 x_source_code VARCHAR2) IS
2064 SELECT wdd.delivery_detail_id
2065 FROM wsh_delivery_details wdd
2066 WHERE wdd.source_line_id IN (x_source_line_id, x_neg_source_line_id)
2067 AND wdd.source_code = x_source_code
2068 AND wdd.container_flag = 'N'
2069 AND wdd.released_status <> 'D';
2070
2071 l_counter NUMBER;
2072 l_source_line_id NUMBER;
2073 l_confirmed_flag BOOLEAN := FALSE;
2074 l_shipped_flag BOOLEAN := FALSE;
2075 l_interface_flag VARCHAR2(1) := 'N';
2076 l_rs VARCHAR2(1);
2077
2078 --
2079 l_debug_on BOOLEAN;
2080 --
2081 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_RECORDS';
2082 --
2083 BEGIN
2084 --
2085 -- Debug Statements
2086 --
2087 --
2088 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2089 --
2090 IF l_debug_on IS NULL
2091 THEN
2092 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2093 END IF;
2094 --
2095 IF l_debug_on THEN
2096 WSH_DEBUG_SV.push(l_module_name);
2097 --
2098 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2099 END IF;
2100 --
2101 l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2102
2103 --
2104 -- Debug Statements
2105 --
2106 IF l_debug_on THEN
2107 WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_INTERFACE.LOCK_RECORDS ' );
2108 END IF;
2109 --
2110 <<records_loop>>
2111 FOR l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST LOOP
2112
2113 -- lock records in WSH_DELIVERY_DETAILS and wsh_delivery_assignments_v (if not Pick Confirm)
2114 -- unless we are importing source lines.
2115 -- We also look up delivery's status (if not Pick Confirm)
2116 IF p_changed_attributes(l_counter).action_flag <> 'I' THEN
2117
2118 -- check if we have already locked delivery_detail_id or source_line_id/original_source_line_id
2119
2120 IF p_changed_attributes(l_counter).delivery_detail_id <> FND_API.G_MISS_NUM THEN
2121
2122 Lock_Delivery_Detail(
2123 p_delivery_detail_id => p_changed_attributes(l_counter).delivery_detail_id,
2124 p_source_code => p_source_code,
2125 x_confirmed_flag => l_confirmed_flag,
2126 x_shipped_flag => l_shipped_flag,
2127 x_interface_flag => l_interface_flag,
2128 x_return_status => l_rs);
2129
2130 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2131 EXIT records_loop;
2132 END IF;
2133
2134 ELSE
2135
2136 IF p_changed_attributes(l_counter).action_flag = 'S' THEN
2137 l_source_line_id := p_changed_attributes(l_counter).original_source_line_id;
2138 ELSE
2139 l_source_line_id := p_changed_attributes(l_counter).source_line_id;
2140 END IF;
2141
2142 <<source_line_loop>>
2143 FOR c IN c_source_line_to_lock( l_source_line_id,
2144 -1*l_source_line_id,
2145 p_source_code) LOOP
2146 Lock_Delivery_Detail(
2147 p_delivery_detail_id => c.delivery_detail_id,
2148 p_source_code => p_source_code,
2149 x_confirmed_flag => l_confirmed_flag,
2150 x_shipped_flag => l_shipped_flag,
2151 x_interface_flag => l_interface_flag,
2152 x_return_status => l_rs);
2153
2154 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2155 EXIT records_loop;
2156 END IF;
2157
2158 --
2159 -- Debug Statements
2160 --
2161 IF l_debug_on THEN
2162 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER LOCKING DELIVERY DETAILS ' );
2163 END IF;
2164 --
2165
2166 END LOOP; -- source_line_loop
2167
2168 END IF; -- p_changed_attributes(l_counter).delivery_detail_id <> FND_API.G_MISS_NUM
2169
2170 END IF; -- p_changed_attributes(l_counter).action_flag <> 'I'
2171
2172 END LOOP; -- records_loop
2173
2174 -- Do we allow actions if the delivery lines are shipped or in confirmed deliveries?
2175 -- 5870774 : Extending/bypass this check for OKE also, since we are to allow cancellation of wdds not CLOSED or CO/ IT
2176 IF (p_source_code <> 'INV' and p_source_code <> 'OKE')
2177 AND (l_interface_flag = 'N')
2178 AND (l_confirmed_flag OR l_shipped_flag) THEN
2179 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_ATTR_NOT_ALLOWED');
2180 l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2181 WSH_UTIL_CORE.add_message (l_rs);
2182 END IF;
2183
2184 x_return_status := l_rs;
2185 x_interface_flag := l_interface_flag;
2186
2187 --
2188 -- Debug Statements
2189 --
2190 IF l_debug_on THEN
2191 WSH_DEBUG_SV.pop(l_module_name);
2192 END IF;
2193 --
2194 EXCEPTION
2195 WHEN others THEN
2196 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2197 wsh_util_core.default_handler('WSH_INTERFACE.Lock_Records');
2198
2199 --
2200 -- Debug Statements
2201 --
2202 IF l_debug_on THEN
2203 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2204 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2205 END IF;
2206 --
2207 END Lock_Records;
2208
2209
2210
2211 PROCEDURE Lock_Delivery_Detail(
2212 p_delivery_detail_id IN NUMBER,
2213 p_source_code IN VARCHAR2,
2214 x_confirmed_flag IN OUT NOCOPY BOOLEAN,
2215 x_shipped_flag IN OUT NOCOPY BOOLEAN,
2216 x_interface_flag IN OUT NOCOPY VARCHAR2,
2217 x_return_status OUT NOCOPY VARCHAR2) IS
2218
2219 RECORD_LOCKED EXCEPTION;
2220 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
2221
2222 l_rs VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2223 l_dummy_id NUMBER;
2224 l_del_status_code VARCHAR2(2);
2225 l_det_status_code VARCHAR2(2);
2226 l_ship_set_id NUMBER;
2227 l_source_header_id NUMBER;
2228
2229 -- Bug 2470320: Check if the line belongs to a ship set that
2230 -- has a line that is alredy pending interface.
2231
2232 cursor c_check_ship_set(c_ship_set IN NUMBER, c_source_header_id IN NUMBER) is
2233 select ship_set_id
2234 from wsh_delivery_details
2235 where source_header_id = c_source_header_id
2236 and ship_set_id = c_ship_set
2237 and source_code = p_source_code
2238 and oe_interfaced_flag = 'P'
2239 and rownum = 1;
2240
2241 -- bug 2068226: check oe_interfaced_flag = 'P'
2242 -- instead of looking for negative source_line_id,
2243 -- in case order lines are fully shipped in a stop being interfaced.
2244 l_interfaced_flag VARCHAR2(1);
2245
2246 --
2247 l_debug_on BOOLEAN;
2248 --
2249 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_DETAIL';
2250 --
2251 BEGIN
2252
2253 --
2254 -- Debug Statements
2255 --
2256 --
2257 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2258 --
2259 IF l_debug_on IS NULL
2260 THEN
2261 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2262 END IF;
2263 --
2264 IF l_debug_on THEN
2265 WSH_DEBUG_SV.push(l_module_name);
2266 --
2267 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2268 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2269 WSH_DEBUG_SV.log(l_module_name,'X_CONFIRMED_FLAG',X_CONFIRMED_FLAG);
2270 WSH_DEBUG_SV.log(l_module_name,'X_SHIPPED_FLAG',X_SHIPPED_FLAG);
2271 WSH_DEBUG_SV.log(l_module_name,'X_INTERFACE_FLAG',X_INTERFACE_FLAG);
2272 END IF;
2273 --
2274 -- Bug 2684221: Lock the delivery only if source_code is not 'INV'
2275 IF l_debug_on THEN
2276 WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE LOCKING DELIVERY DETAILS ' );
2277 END IF;
2278
2279 SELECT wdd.oe_interfaced_flag,
2280 wdd.released_status,
2281 wdd.ship_set_id,
2282 wdd.source_header_id,
2283 wda.delivery_id
2284 INTO l_interfaced_flag,
2285 l_det_status_code,
2286 l_ship_set_id,
2287 l_source_header_id,
2288 l_dummy_id
2289 FROM wsh_delivery_details wdd,
2290 wsh_delivery_assignments_v wda
2291 WHERE wdd.delivery_detail_id = p_delivery_detail_id
2292 AND wdd.delivery_detail_id = wda.delivery_detail_id
2293 AND wdd.container_flag = 'N'
2294 FOR UPDATE NOWAIT;
2295
2296 IF l_interfaced_flag = 'P' THEN
2297 x_interface_flag := 'Y';
2298 END IF;
2299
2300 IF (p_source_code = 'INV') THEN
2301 IF l_debug_on THEN
2302 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE CODE IS INV ' );
2303 WSH_DEBUG_SV.logmsg(l_module_name, 'Do not Lock the Delivery if source_code is INV');
2304 END IF;
2305 -- Do not Lock the Delivery if source_code is 'INV'
2306 IF (l_dummy_id IS NOT NULL) THEN
2307 SELECT wnd.status_code
2308 INTO l_del_status_code
2309 FROM wsh_new_deliveries wnd
2310 WHERE wnd.delivery_id = l_dummy_id;
2311 ELSE
2312 l_del_status_code := 'OP';
2313 END IF;
2314 ELSE
2315 -- Lock the Delivery if source_code is other than 'INV'
2316 IF l_debug_on THEN
2317 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE CODE IS NOT INV ' );
2318 WSH_DEBUG_SV.logmsg(l_module_name, 'Lock the Delivery if source_code is not INV');
2319 END IF;
2320 IF (l_dummy_id IS NOT NULL) THEN
2321 SELECT wnd.status_code
2322 INTO l_del_status_code
2323 FROM wsh_new_deliveries wnd
2324 WHERE wnd.delivery_id = l_dummy_id
2325 FOR UPDATE NOWAIT;
2326 ELSE
2327 l_del_status_code := 'OP';
2328 END IF;
2329 END IF;
2330
2331 -- Bug 2470320: If the line is shipped, or the delivery is confirmed, we
2332 -- check if the line belongs to a ship set that
2333 -- has a line that is alredy pending interface.
2334
2335 IF (l_del_status_code = 'CO') OR (l_det_status_code = 'C') THEN
2336
2337 IF (x_interface_flag) <> 'Y' AND (l_ship_set_id IS NOT NULL) THEN
2338
2339 OPEN c_check_ship_set(l_ship_set_id, l_source_header_id);
2340 FETCH c_check_ship_set into l_ship_set_id;
2341
2342 IF c_check_ship_set%FOUND THEN
2343
2344 x_interface_flag := 'Y';
2345
2346 END IF;
2347
2348 CLOSE c_check_ship_set;
2349
2350 END IF;
2351
2352 IF (l_del_status_code = 'CO') THEN
2353 x_confirmed_flag := TRUE;
2354 END IF;
2355 IF l_det_status_code = 'C' THEN
2356 x_shipped_flag := TRUE;
2357 END IF;
2358
2359 END IF;
2360
2361 x_return_status := l_rs;
2362
2363 --
2364 -- Debug Statements
2365 --
2366 IF l_debug_on THEN
2367 WSH_DEBUG_SV.pop(l_module_name);
2368 END IF;
2369 --
2370 EXCEPTION
2371 WHEN RECORD_LOCKED THEN
2372 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2373 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
2374 WSH_UTIL_CORE.add_message (x_return_status);
2375 --
2376 -- Debug Statements
2377 --
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2380 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
2381 END IF;
2382 --
2383 RETURN;
2384
2385 WHEN others THEN
2386 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2387 wsh_util_core.default_handler('WSH_INTERFACE.Lock_Delivery_Detail');
2388
2389 --
2390 -- Debug Statements
2391 --
2392 IF l_debug_on THEN
2393 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2394 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2395 END IF;
2396 --
2397 END Lock_Delivery_Detail;
2398
2399
2400
2401 PROCEDURE Process_Records(
2402 p_source_code IN VARCHAR2,
2403 p_changed_attributes IN ChangedAttributeTabType,
2404 p_interface_flag IN VARCHAR2,
2405 x_return_status OUT NOCOPY VARCHAR2
2406 ) IS
2407
2408 l_counter NUMBER;
2409 l_rs VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2410 l_update_allowed VARCHAR2(1);
2411
2412 --
2413 l_debug_on BOOLEAN;
2414 --
2415 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_RECORDS';
2416 --
2417 BEGIN
2418
2419 --
2420 -- Debug Statements
2421 --
2422 --
2423 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2424 --
2425 IF l_debug_on IS NULL
2426 THEN
2427 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2428 END IF;
2429 --
2430 IF l_debug_on THEN
2431 WSH_DEBUG_SV.push(l_module_name);
2432 --
2433 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2434 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
2435 END IF;
2436 --
2437 --
2438 -- Debug Statements
2439 --
2440 IF l_debug_on THEN
2441 WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_INTERFACE.PROCESS_RECORDS' );
2442 END IF;
2443 --
2444
2445 --
2446 -- Debug Statements
2447 --
2448 IF l_debug_on THEN
2449 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING CODE RELEASE LEVEL' );
2450 END IF;
2451 --
2452 IF WSH_CODE_CONTROL.Get_Code_Release_Level >= '110508' then
2453 /* H integration: 940/945 bug 2312168 wrudge
2454 ** During OM Interface, allow updates/splits to happen.
2455 ** Otherwise, check if changes are allowed.
2456 */
2457 IF p_interface_flag <> 'Y' THEN
2458 --
2459 -- Debug Statements
2460 --
2461 IF l_debug_on THEN
2462 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED' );
2463 END IF;
2464 --
2465 --
2466 -- Debug Statements
2467 --
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED',WSH_DEBUG_SV.C_PROC_LEVEL);
2470 END IF;
2471 --
2472 WSH_DELIVERY_UTIL.Check_Updates_Allowed(
2473 p_changed_attributes => p_changed_attributes,
2474 p_source_code => p_source_code,
2475 x_update_allowed => l_update_allowed,
2476 x_return_status => l_rs);
2477 --
2478 -- Debug Statements
2479 --
2480 IF l_debug_on THEN
2481 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING CHECK_UPDATES_ALLOWED ' || L_RS );
2482 END IF;
2483 --
2484 END IF;
2485 END IF;
2486
2487
2488 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2489
2490 IF p_interface_flag = 'N' THEN
2491 --
2492 -- Debug Statements
2493 --
2494 IF l_debug_on THEN
2495 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_ACTIONS_PVT.IMPORT_RECORDS' );
2496 END IF;
2497 --
2498 --
2499 -- Debug Statements
2500 --
2501 IF l_debug_on THEN
2502 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.IMPORT_RECORDS',WSH_DEBUG_SV.C_PROC_LEVEL);
2503 END IF;
2504 --
2505 WSH_USA_ACTIONS_PVT.Import_Records(
2506 p_source_code => p_source_code,
2507 p_changed_attributes => p_changed_attributes,
2508 x_return_status => l_rs);
2509 END IF;
2510 --
2511 -- Debug Statements
2512 --
2513 IF l_debug_on THEN
2514 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_USA_ACTIONS_PVT.IMPORT_RECORDS '|| L_RS );
2515 END IF;
2516 --
2517 END IF;
2518
2519 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2520 --
2521 -- Debug Statements
2522 --
2523 IF l_debug_on THEN
2524 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_ACTIONS_PVT.SPLIT_RECORDS' );
2525 END IF;
2526 --
2527 --
2528 -- Debug Statements
2529 --
2530 IF l_debug_on THEN
2531 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.SPLIT_RECORDS',WSH_DEBUG_SV.C_PROC_LEVEL);
2532 END IF;
2533 --
2534 WSH_USA_ACTIONS_PVT.Split_Records(
2535 p_source_code => p_source_code,
2536 p_changed_attributes => p_changed_attributes,
2537 p_interface_flag => p_interface_flag,
2538 x_return_status => l_rs);
2539 END IF;
2540
2541 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2542 --
2543 -- Debug Statements
2544 --
2545 IF l_debug_on THEN
2546 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_ACTIONS_PVT.UPDATE_RECORDS' );
2547 END IF;
2548 --
2549 --
2550 -- Debug Statements
2551 --
2552 IF l_debug_on THEN
2553 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.UPDATE_RECORDS',WSH_DEBUG_SV.C_PROC_LEVEL);
2554 END IF;
2555 --
2556 WSH_USA_ACTIONS_PVT.Update_Records(
2557 p_source_code => p_source_code,
2558 p_changed_attributes => p_changed_attributes,
2559 p_interface_flag => p_interface_flag,
2560 x_return_status => l_rs);
2561 END IF;
2562
2563 -- bug 2111278
2564 <<record_loop>>
2565 FOR l_counter IN p_changed_attributes.FIRST ..p_changed_attributes.LAST LOOP
2566 IF p_changed_attributes(l_counter).action_flag = 'D' THEN
2567 --
2568 -- Debug Statements
2569 --
2570 IF l_debug_on THEN
2571 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY' );
2572 END IF;
2573 --
2574 --
2575 -- Debug Statements
2576 --
2577 IF l_debug_on THEN
2578 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
2579 END IF;
2580 --
2581 WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity(
2582 p_changed_attribute =>p_changed_attributes(l_counter),
2583 p_source_code =>p_source_code,
2584 p_action_flag => 'D',
2585 x_return_status => l_rs);
2586
2587 IF l_rs <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2588 x_return_status := l_rs;
2589 exit;
2590 END IF;
2591 END IF;
2592
2593 END LOOP;
2594 --
2595
2596 x_return_status := l_rs;
2597
2598 --
2599 -- Debug Statements
2600 --
2601 IF l_debug_on THEN
2602 WSH_DEBUG_SV.pop(l_module_name);
2603 END IF;
2604 --
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2608 wsh_util_core.default_handler('WSH_INTERFACE.Process_Records');
2609
2610 --
2611 -- Debug Statements
2612 --
2613 IF l_debug_on THEN
2614 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2615 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2616 END IF;
2617 --
2618 END Process_Records;
2619
2620
2621 /* Bug 2313898 To avoid the null messages , IF name is NOT NULL included */
2622 PROCEDURE PRINTMSG (txt VARCHAR2,
2623 name VARCHAR2 DEFAULT NULL ) IS
2624 --
2625 l_debug_on BOOLEAN;
2626 --
2627 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINTMSG';
2628 --
2629 BEGIN
2630 --
2631 -- Debug Statements
2632 --
2633 --
2634 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2635 --
2636 IF l_debug_on IS NULL
2637 THEN
2638 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2639 END IF;
2640 --
2641 IF l_debug_on THEN
2642 WSH_DEBUG_SV.push(l_module_name);
2643 --
2644 WSH_DEBUG_SV.log(l_module_name,'TXT',TXT);
2645 WSH_DEBUG_SV.log(l_module_name,'NAME',NAME);
2646 END IF;
2647 --
2648 IF ( g_call_mode = 'CONC' ) THEN
2649 --
2650 -- Debug Statements
2651 --
2652 IF l_debug_on THEN
2653 WSH_DEBUG_SV.logmsg(l_module_name, TXT );
2654 END IF;
2655 --
2656 ELSE
2657 IF name is NOT NULL then
2658 FND_MESSAGE.SET_NAME('WSH', name);
2659 WSH_UTIL_CORE.add_message ('E');
2660 END IF;
2661 END IF;
2662 --
2663 -- Debug Statements
2664 --
2665 IF l_debug_on THEN
2666 WSH_DEBUG_SV.pop(l_module_name);
2667 END IF;
2668 --
2669 END PRINTMSG;
2670
2671 -- anxsharm for Load Tender
2672 /*
2673 -----------------------------------------------------------------------------
2674 PROCEDURE : Get_Details_Snapshot
2675 PARAMETERS : p_source_code - Input Source Code
2676 p_changed_attributes Table of changed attributes for detail
2677 p_phase - 1 for Before the action is performed, 2 for after.
2678 x_dd_ids - Table of Delivery Detail ids
2679 x_out_table - attributes of snap shot
2680 x_return_status - Return Status
2681 DESCRIPTION : This procedure gets attributes of delivery detail
2682 Added for Load Tender Project but this is independent of
2683 FTE is installed or not.
2684 ------------------------------------------------------------------------------
2685 */
2686
2687 PROCEDURE Get_Details_Snapshot(
2688 p_source_code IN VARCHAR2,
2689 p_changed_attributes IN ChangedAttributeTabType,
2690 p_phase IN NUMBER,
2691 x_dd_ids IN OUT NOCOPY wsh_util_core.id_tab_type,
2692 x_out_table OUT NOCOPY wsh_interface.deliverydetailtab,
2693 x_return_status OUT NOCOPY VARCHAR2) IS
2694
2695 -- use delivery detail id
2696 CURSOR get_dd_for_id (v_delivery_detail_id NUMBER)IS
2697 SELECT wdd.delivery_detail_id,
2698 wdd.requested_quantity,
2699 wdd.shipped_quantity,
2700 wdd.picked_quantity,
2701 wdd.gross_weight,
2702 wdd.net_weight,
2703 wdd.weight_uom_code,
2704 wdd.volume,
2705 wdd.volume_uom_code,
2706 wda.delivery_id,
2707 wda.parent_delivery_detail_id,
2708 wdd.released_status
2709 FROM wsh_delivery_details wdd,
2710 wsh_delivery_assignments_v wda
2711 WHERE wdd.delivery_detail_id = v_delivery_detail_id
2712 AND wdd.delivery_detail_id = wda.delivery_detail_id;
2713 -- cannot add wda.delivery_id is not null because this is a generic
2714 -- API and not specific for FTE
2715
2716 -- use source line id
2717 CURSOR get_dd_for_srcline (v_source_line_id NUMBER)IS
2718 SELECT wdd.delivery_detail_id,
2719 wdd.requested_quantity,
2720 wdd.shipped_quantity,
2721 wdd.picked_quantity,
2722 wdd.gross_weight,
2723 wdd.net_weight,
2724 wdd.weight_uom_code,
2725 wdd.volume,
2726 wdd.volume_uom_code,
2727 wda.delivery_id,
2728 wda.parent_delivery_detail_id,
2729 wdd.released_status
2730 FROM wsh_delivery_details wdd,
2731 wsh_delivery_assignments_v wda
2732 WHERE wdd.source_line_id = v_source_line_id
2733 AND wdd.delivery_detail_id = wda.delivery_detail_id
2734 AND nvl(wdd.line_direction, 'O') IN ('O', 'IO');
2735 i NUMBER;
2736 l_dd_rec wsh_interface.delivery_detail_rec;
2737 l_dd_tab wsh_interface.deliverydetailtab;
2738 l_dd_ids wsh_util_core.id_tab_type;
2739 l_source_line_id wsh_delivery_details.source_line_id%TYPE;
2740 --
2741 l_debug_on BOOLEAN;
2742 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DETAILS_SNAPSHOT';
2743 --
2744
2745 BEGIN
2746
2747 --
2748 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2749 --
2750 IF l_debug_on IS NULL THEN
2751 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2752 END IF;
2753 --
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.push(l_module_name);
2756 --
2757 WSH_DEBUG_SV.log(l_module_name,'source code',p_source_code);
2758 WSH_DEBUG_SV.log(l_module_name,'Changedattribute - count',p_changed_attributes.count);
2759 WSH_DEBUG_SV.log(l_module_name,'Phase',p_phase);
2760 END IF;
2761
2762 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2763
2764 l_dd_tab.delete;
2765
2766 --bug 2769339
2767 IF (p_changed_attributes.count>=1) THEN
2768
2769 FOR i IN p_changed_attributes.FIRST..p_changed_attributes.LAST
2770 LOOP
2771
2772 IF p_source_code = 'INV' THEN
2773 -- use delivery detail id
2774 OPEN get_dd_for_id(p_changed_attributes(i).delivery_detail_id);
2775 FETCH get_dd_for_id
2776 INTO l_dd_rec;
2777 CLOSE get_dd_for_id;
2778 ELSIF p_source_code <> 'INV' THEN
2779 IF p_changed_attributes(i).action_flag = 'S' THEN
2780 -- use original source_line_id
2781 l_source_line_id := p_changed_attributes(i).original_source_line_id;
2782 ELSIF p_changed_attributes(i).action_flag = 'U' THEN
2783 -- use source_line_id for action of 'U'
2784 l_source_line_id := p_changed_attributes(i).source_line_id;
2785 END IF;
2786 OPEN get_dd_for_srcline(l_source_line_id);
2787 FETCH get_dd_for_srcline
2788 INTO l_dd_rec;
2789 CLOSE get_dd_for_srcline;
2790 END IF;
2791 l_dd_tab(l_dd_tab.count + 1) := l_dd_rec;
2792 l_dd_ids(l_dd_ids.count + 1) := l_dd_rec.delivery_detail_id;
2793 IF l_debug_on THEN
2794 WSH_DEBUG_SV.log(l_module_name,'l_dd_rec.delivery_detail_id',l_dd_rec.delivery_detail_id);
2795 END IF;
2796 END LOOP;
2797
2798 x_dd_ids := l_dd_ids;
2799 x_out_table := l_dd_tab;
2800
2801 END IF;
2802
2803
2804 IF l_debug_on THEN
2805 WSH_DEBUG_SV.pop(l_module_name);
2806 END IF;
2807
2808 EXCEPTION
2809 WHEN others THEN
2810 wsh_util_core.default_handler('WSH_INTERFACE.get_details_snapshot');
2811 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2812 IF l_debug_on THEN
2813 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2814 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2815 END IF;
2816
2817
2818 END Get_Details_Snapshot;
2819
2820 -- anxsharm for Load Tender
2821
2822 END WSH_INTERFACE;