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