[Home] [Help]
PACKAGE BODY: APPS.WSH_USA_ACTIONS_PVT
Source
1 PACKAGE BODY WSH_USA_ACTIONS_PVT as
2 /* $Header: WSHUSAAB.pls 120.38.12020000.6 2013/05/30 13:28:25 skanduku ship $ */
3
4 -- Start of body implementations
5
6
7 --
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_USA_ACTIONS_PVT';
9 --
10 PROCEDURE Import_Records(
11 p_source_code IN VARCHAR2,
12 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
13 x_return_status OUT NOCOPY VARCHAR2)
14 IS
15 l_counter NUMBER;
16 l_rs VARCHAR2(1);
17 --
18 l_debug_on BOOLEAN;
19 --
20 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IMPORT_RECORDS';
21 --
22 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
23 l_organization_tab WSH_UTIL_CORE.id_tab_type; --Pick To POD WF Project
24 l_wf_rs VARCHAR2(1); --Pick To POD WF Project
25 l_dbi_rs VARCHAR2(1); -- DBI Project
26
27 BEGIN
28 --
29 --
30 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
31 --
32 IF l_debug_on IS NULL
33 THEN
34 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
35 END IF;
36 --
37 IF l_debug_on THEN
38 WSH_DEBUG_SV.push(l_module_name);
39 --
40 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
41 END IF;
42 l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
43 <<records_loop>>
44 FOR l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST LOOP
45 IF p_changed_attributes(l_counter).action_flag = 'I' THEN
46 Import_Delivery_Details(
47 p_source_line_id => p_changed_attributes(l_counter).source_line_id,
48 p_source_code => p_source_code,
49 x_return_status => l_rs);
50 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
51 EXIT records_loop;
52 END IF;
53
54 IF (NVL(p_changed_attributes(l_counter).released_status,'@') <> FND_API.G_MISS_CHAR) THEN
55
56 -- pickable lines should have status updated to 'R'.
57 -- non-pickable (non-reservable) lines should have status updated to 'X'.
58 IF l_debug_on THEN
59 WSH_DEBUG_SV.log(l_module_name,'source_line_id',p_changed_attributes(l_counter).source_line_id);
60 END IF;
61 UPDATE WSH_DELIVERY_DETAILS
62 SET released_status = decode(pickable_flag, 'Y','R', 'X')
63 WHERE source_line_id = p_changed_attributes(l_counter).source_line_id
64 AND source_code = p_source_code
65 RETURNING delivery_detail_id, organization_id BULK COLLECT INTO l_detail_tab, l_organization_tab;
66 -- Added for DBI Project
67 --Organization_id added for Pick to Pod Workflow
68
69 --Raise Event: Pick To Pod Workflow
70 FOR i in l_detail_tab.first .. l_detail_tab.last
71 LOOP
72 IF (l_detail_tab.exists(i)) THEN
73 WSH_WF_STD.Raise_Event(
74 p_entity_type => 'LINE',
75 p_entity_id => l_detail_tab(i) ,
76 p_event => 'oracle.apps.wsh.line.gen.readytorelease' ,
77 --p_parameters IN wf_parameter_list_t DEFAULT NULL,
78 p_organization_id => l_organization_tab(i),
79 x_return_status => l_wf_rs ) ;
80 IF l_debug_on THEN
81 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
82 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ',l_detail_tab(i) );
83 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
84 END IF;
85 END IF;
86 END LOOP;
87 --Done Raise Event: Pick To Pod Workflow
88 --
89 -- DBI Project
90 -- Update of wsh_delivery_details where requested_quantity/released_status
91 -- are changed, call DBI API after the update.
92 -- This API will also check for DBI Installed or not
93 IF l_debug_on THEN
94 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count',l_detail_tab.count);
95 END IF;
96 WSH_INTEGRATION.DBI_Update_Detail_Log
97 (p_delivery_detail_id_tab => l_detail_tab,
98 p_dml_type => 'UPDATE',
99 x_return_status => l_dbi_rs);
100
101 IF l_debug_on THEN
102 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
103 END IF;
104 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
105 x_return_status := l_dbi_rs;
106 -- just pass this return status to caller API
107 IF l_debug_on THEN
108 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
109 WSH_DEBUG_SV.pop(l_module_name);
110 END IF;
111 return;
112 END IF;
113 END IF;
114
115 END IF;
116
117
118 END LOOP; -- l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST
119
120 x_return_status := l_rs;
121
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
125 WSH_DEBUG_SV.pop(l_module_name);
126 END IF;
127 --
128 EXCEPTION
129 WHEN OTHERS THEN
130 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
131 wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Import_Records',l_module_name);
132 --
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
135 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
136 END IF;
137 --
138 END Import_Records;
139
140 --
141 -- Procedure : sort_splits
142 -- Parameters: p_index_tab IN OUT table of indexes of the input parameter p_split_table
143 -- p_split_table IN OUT table of records passed by OM for splitting.
144 -- x_return_status OUT return status
145 --
146 -- Description:
147 -- Sorts the p_split_table the the following order :
148 -- original_source_line_id asc
149 -- source_line_id desc
150 -- date_requested desc
151 --
152
153 PROCEDURE sort_splits ( p_index_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type ,
154 p_split_table IN OUT NOCOPY split_Table_tab_type,
155 x_return_status OUT NOCOPY VARCHAR2 )
156 IS
157 l_tmp NUMBER ;
158 l_tmp_date DATE ;
159 l_split_table split_Table_tab_type ;
160 l_tmp_split_table split_Table_type ;
161 l_exchange BOOLEAN ;
162 l_sorted BOOLEAN ;
163 l_total_splits NUMBER ;
164
165
166 l_debug_on BOOLEAN;
167 --
168 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'sort_splits';
169
170 l_ref NUMBER ;
171 l_cmp NUMBER ;
172
173 BEGIN
174 --
175 --
176 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
177 --
178 IF l_debug_on IS NULL
179 THEN
180 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
181 END IF;
182
183 -- add the original line also in the l_split_table
184 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
185
186 IF l_debug_on THEN
187 WSH_DEBUG_SV.logmsg(l_module_name,'Inside sort splits ' );
188 END IF;
189
190 FOR i in 1..p_split_table.count
191 LOOP
192 l_split_table(i).source_line_id := p_split_table(i).source_line_id ;
193 l_split_table(i).original_source_line_id := p_split_table(i).original_source_line_id ;
194 l_split_table(i).date_requested := p_split_table(i).date_requested ;
195 l_split_table(i).changed_Attributes_index := p_split_table(i).changed_Attributes_index ;
196 p_index_tab(i) := i ;
197 END LOOP;
198
199 IF l_debug_on THEN
200 WSH_DEBUG_SV.logmsg(l_module_name,'Assigned l_split_table');
201 END IF;
202
203 l_ref := 1 ;
204 l_exchange := TRUE ;
205 l_sorted := FALSE ;
206
207 WHILE not l_sorted
208 LOOP
209 l_sorted := TRUE ;
210
211 -- for loop ignores last 2 lines because
212 -- a. Since we are comparing the current line with the NEXT line , the current line cannot be the
213 -- last line and
214 -- b. the last line is the reference line for schedule_date so does not have to be used for comparision
215 -- for sorting.
216
217 FOR l_ref in 1..l_split_table.count - 2
218 LOOP
219 l_exchange := FALSE ;
220 l_cmp := l_ref + 1 ;
221
222 IF l_debug_on THEN
223 WSH_DEBUG_SV.logmsg(l_module_name,l_split_table(l_cmp ).original_source_line_id || ' : ' || l_split_table(l_ref).original_source_line_id );
224 WSH_DEBUG_SV.logmsg(l_module_name,l_split_table(l_cmp ).source_line_id || ' : ' || l_split_table(l_ref).source_line_id );
225 WSH_DEBUG_SV.logmsg(l_module_name,l_split_table(l_cmp ).date_requested || ' : ' || l_split_table(l_ref).date_requested );
226 END IF;
227
228 IF l_split_table(l_cmp ).original_source_line_id > l_split_table(l_ref).original_source_line_id
229 THEN
230 l_exchange := TRUE ;
231 IF l_debug_on THEN
232 WSH_DEBUG_SV.logmsg(l_module_name,'exchang = TRUE 1 ' );
233 END IF;
234 ELSIF l_split_table(l_cmp ).original_source_line_id = l_split_table(l_ref).original_source_line_id
235 and nvl( l_split_table(l_cmp ).date_requested , SYSDATE ) < NVL ( l_split_table(l_ref).date_requested , SYSDATE )
236 THEN
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.logmsg(l_module_name,'exchang = TRUE 2 ' );
239 END IF;
240
241 l_exchange := TRUE ;
242 ELSIF l_split_table(l_cmp ).original_source_line_id = l_split_table(l_ref).original_source_line_id
243 and nvl( l_split_table(l_cmp ).date_requested, SYSDATE) = nvl( l_split_table(l_ref).date_requested , SYSDATE )
244 and l_split_table(l_cmp ).source_line_id < l_split_table(l_ref).source_line_id
245 THEN
246 l_exchange := TRUE ;
247 END IF ;
248
249 IF l_exchange then
250 l_sorted := FALSE ;
251 -- IS the p_index_Tab required if the changedattrib index is already
252 -- stored in p_split_Tab ?!! - splitrsv
253 l_tmp := p_index_tab(l_ref) ;
254 p_index_tab(l_ref) := p_index_tab(l_cmp );
255 p_index_Tab(l_cmp ) := l_tmp ;
256
257 l_tmp_split_Table := l_split_table(l_ref);
258 l_split_Table(l_ref) := l_split_Table(l_cmp );
259 l_split_Table(l_cmp ) := l_tmp_split_Table ;
260
261 END IF ;
262
263 END LOOP ;
264
265 END LOOP ;
266
267 -- update directions with 'F' till you see original line . after that update direction to 'L'
268 -- for every new orginal_source_line_id you encounter reset to 'F' .
269
270 l_total_splits := l_split_table.count-1 ;
271
272 FOR i in 1..l_total_splits --{
273 LOOP
274 IF l_split_table(i).date_requested is not null AND
275 l_split_Table(l_total_splits + 1 ).date_requested is not null AND
276 l_split_table(i).date_requested > l_split_Table(l_total_splits + 1 ).date_requested then
277 l_split_table(i).direction_flag := 'F' ;
278 ELSE
279 l_split_table(i).direction_flag := 'L' ;
280 END IF ;
281 p_split_table(i) := l_split_table(i);
282 END LOOP; --}
283
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.logmsg(l_module_name,'Exception in sort_splits' );
290 WSH_DEBUG_SV.logmsg(l_module_name, SQLERRM || ' : ' || SQLCODE );
291 END IF;
292
293 END sort_splits ;
294 --
295 -- Procedure : split_records_int ( internal API )
296 -- Parameters: p_source_code IN source_code
297 -- p_changed_attributes IN table of records passed by OM for changing.
298 -- p_interface_flag IN is the record interfaced to OM ?
299 -- p_index_tab IN table of indexes pointing to p_changed_attributes
300 -- p_split_lines IN table of records of lines that are to be split.
301 -- x_return_status OUT return status
302 --
303 -- Description:
304 -- Sorts the p_split_table the the following order :
305 -- original_source_line_id asc
306 -- source_line_id desc
307 -- date_requested desc
308 --
309
310 PROCEDURE Split_Records_Int (
311 p_source_code IN VARCHAR2,
312 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
313 p_interface_flag IN VARCHAR2,
314 p_index_tab IN WSH_UTIL_CORE.ID_Tab_Type ,
315 p_split_lines IN WSH_USA_ACTIONS_PVT.split_Table_Tab_type ,
316 x_return_status OUT NOCOPY VARCHAR2
317 ) IS
318
319 -- cursor to calculate the total requested quantity for the original source_line_id .
320
321 -- HW OPMCONV -Retrieve Qty2
322 CURSOR c_total_requested_quantity (p_source_line_id NUMBER)
323 IS
324 SELECT inventory_item_id , requested_quantity_uom ,sum ( requested_quantity ),
325 sum ( NVL(requested_quantity2,0) ),organization_id --bugfix 8780998
326 from wsh_delivery_details
327 where source_line_id = p_source_line_id
328 and source_code = 'OE'
329 group by inventory_item_id , requested_quantity_uom,organization_id;
330
331 -- This cursor is the same for both normal and OM Process
332 -- We assume that the caller will have screened source lines
333 -- for delivery lines that are shipped or in confirmed deliveries.
334 CURSOR c_details(x_source_code VARCHAR2,
335 x_original_source_line_id NUMBER,
336 x_interface_flag VARCHAR2,
337 x_shipped_flag VARCHAR2,
338 -- muom
339 x_fulfillment_base VARCHAR2) IS
340 SELECT wdd.delivery_detail_id,
341 wdd.requested_quantity,
342 wdd.picked_quantity,
343 wdd.shipped_quantity,
344 wdd.cycle_count_quantity,
345 wdd.requested_quantity_uom,
346 wdd.requested_quantity2,
347 wdd.picked_quantity2,
348 wdd.shipped_quantity2,
349 wdd.cycle_count_quantity2,
350 wdd.requested_quantity_uom2,
351 wdd.released_status,
352 wdd.move_order_line_id,
353 wdd.organization_id,
354 wdd.inventory_item_id,
355 wdd.revision,
356 wdd.subinventory,
357 wdd.lot_number,
358 -- HW OPMCONV - No need for sublot_number
359 -- wdd.sublot_number,
360 wdd.locator_id,
361 wdd.source_line_id,
362 wdd.source_code,
363 wdd.source_header_id,
364 wdd.net_weight,
365 wdd.cancelled_quantity,
366 wdd.cancelled_quantity2,
367 wdd.serial_number,
368 wdd.to_serial_number,
369 wdd.transaction_temp_id,
370 wdd.pickable_flag,
371 wdd.ato_line_id,
372 wdd.container_flag,
373 NVL(wdd.inv_interfaced_flag, 'N') inv_interfaced_flag,
374 wdd.source_line_set_id, -- anxsharm Bug 2181132
375 wda.delivery_id,
376 wda.parent_delivery_detail_id ,
377 wdd1.lpn_id, -- Bug 2773605 : Need to fetch the parent's lpn_id also.
378 -- J: W/V Changes
379 wdd.gross_weight,
380 wdd.volume,
381 wdd.weight_uom_code,
382 wdd.volume_uom_code,
383 wdd.wv_frozen_flag,
384 -- End W/V Changes
385 wdd.source_header_number,-- ECO 4524041, add field to display in message
386 -- K: MDC
387 wda.parent_delivery_id,
388 NVL(wda.type, 'S') wda_type,
389 -- END K: MDC
390 wdd.replenishment_status -- bug# 6719369 (replenishment project)
391 FROM wsh_delivery_details wdd,
392 wsh_delivery_assignments wda,
393 wsh_delivery_details wdd1 -- Bug 2773605: Added to fetch parent's lpn_id
394 WHERE wdd.source_code = x_source_code
395 AND wdd.source_line_id = x_original_source_line_id
396 AND wdd.container_flag = 'N'
397 AND wdd.released_status <> 'D'
398 AND (
399 ( x_interface_flag = 'N'
400 )
401 OR
402 ( x_interface_flag = 'Y'
403 AND (
404 (x_shipped_flag = 'Y' AND wdd.oe_interfaced_flag = 'P')
405 OR (x_shipped_flag = 'N' AND NVL(wdd.oe_interfaced_flag, 'N') = 'N')
406 )
407 )
408 )
409 AND wda.delivery_detail_id = wdd.delivery_detail_id
410 AND NVL(wda.type, 'S') in ('S', 'C')
411 AND wda.parent_Delivery_Detail_id = wdd1.delivery_Detail_id(+)
412 ORDER BY DECODE(wdd.released_status,
413 'C', 1,
414 'Y', 2,
415 'R', 3,
416 'N', 4,
417 'B', 5,
418 'X', 6,
419 7), -- 'S': save "released to warehouse" for last
420 -- muom
421 decode(x_fulfillment_base,'S',wdd.REQUESTED_QUANTITY2,wdd.REQUESTED_QUANTITY) DESC;
422
423 -- bug 3364238
424 CURSOR c_req_qty(x_source_code VARCHAR2,
425 x_original_source_line_id NUMBER
426 ) IS
427 SELECT sum(nvl(wdd.requested_quantity, 0))
428 -- muom
429 ,sum(nvl(wdd.requested_quantity2, 0))
430 FROM wsh_delivery_details wdd,
431 wsh_delivery_assignments_v wda,
432 wsh_delivery_details wdd1
433 WHERE wdd.source_code = x_source_code
434 AND wdd.source_line_id = x_original_source_line_id
435 AND wdd.container_flag = 'N'
436 AND wdd.released_status <> 'D'
437 AND NVL(wdd.oe_interfaced_flag, 'N') = 'N'
438 AND wda.delivery_detail_id = wdd.delivery_detail_id
439 AND wda.parent_Delivery_Detail_id = wdd1.delivery_Detail_id(+);
440
441 -- muom
442 CURSOR c_zero_req_cur(x_line_id NUMBER) IS
443 SELECT requested_quantity2,
444 requested_quantity_uom,
445 requested_quantity_uom2,
446 inventory_item_id,
447 delivery_detail_id
448 FROM wsh_delivery_details wdd
449 WHERE wdd.source_code = 'OE'
450 AND wdd.source_line_id = x_line_id
451 AND wdd.requested_quantity = 0
452 AND wdd.released_status <> 'D';
453
454 l_detail_inv_rec WSH_USA_INV_PVT.DeliveryDetailInvRecType;
455 l_detail_info WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
456
457 l_cancel_Reservation_Tab_Type WSH_USA_ACTIONS_PVT.Cancel_Reservation_Tab_Type; --bugfix 8915868
458 l_cancel_counter NUMBER :=0 ; --bugfix 8915868
459
460 l_uom_converted BOOLEAN;
461 l_counter NUMBER;
462 l_rs VARCHAR2(1);
463 l_quantity_to_split NUMBER;
464 l_quantity_to_split2 NUMBER; -- AG
465 l_quantity_split NUMBER;
466 l_quantity_split2 NUMBER; -- AG
467 l_working_detail_id NUMBER;
468 l_reservable VARCHAR2(1);
469
470 --wrudge
471 -- bug 2121426: keep track of reservation quantity to transfer
472 l_res_qty_to_transfer NUMBER;
473 l_res_qty2_to_transfer NUMBER;
474
475 l_ato_split BOOLEAN;
476 l_msg_count NUMBER;
477 l_msg_data VARCHAR2(2000);
478 -- HW OPM for OM changes
479 l_prev_org_id NUMBER := NULL;
480 -- HW OPMCONV. Removed OPM variables
481
482 -- HW OPMCONV - Added two new variables
483 l_detailed_qty NUMBER;
484 l_detailed_qty2 NUMBER;
485 l_prev_source_line_id NUMBER;
486
487 l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete delivery lines pending overpick
488
489 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
490 l_dbi_rs VARCHAR2(1); -- DBI Project
491
492
493 --
494 l_debug_on BOOLEAN;
495 --
496 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SPLIT_RECORDS_Int';
497 --
498 -- Bug 2540015: Added l_move_order_line_status
499 l_move_order_line_status VARCHAR2(20); -- bug # 9410461 : increased the size.
500 l_trnfer_reservations VARCHAR2(1); -- bug # 9410461
501
502 -- Variables added for bug 2919848.
503 l_quantity_to_keep NUMBER;
504 l_total_res_qty_to_transfer NUMBER;
505 -- HW OPMCONV - Added Qty2
506 l_total_res_qty2_to_transfer NUMBER;
507 l_found BOOLEAN;
508
509 l_total_spare_rsv NUMBER ;
510 -- HW OPMCONV - Added Qty2
511 l_total_spare_rsv2 NUMBER ;
512 l_total_rsv_to_split WSH_UTIL_CORE.id_Tab_type ;
513 -- HW OPMCONV - Added Qty2
514 l_total_rsv_to_split2 WSH_UTIL_CORE.id_Tab_type ;
515
516 l_inventory_item_id NUMBER;
517 l_organization_id NUMBER;
518 l_last_orig_line_id NUMBER;
519
520 l_pickable_flag VARCHAR2(1);
521
522 l_sdd_tab WSH_UTIL_CORE.Id_Tab_Type ; -- to store delivery details marked as 'R'
523 l_total_reserved_quantity NUMBER ;
524 -- HW OPMCONV - Added Qty2
525 l_total_reserved_quantity2 NUMBER ;
526 l_total_requested_quantity NUMBER ;
527 -- HW OPMCONV - Added Qty2
528 l_total_requested_quantity2 NUMBER ;
529 l_total_unstaged_quantity NUMBER ;
530 l_max_rsv_to_split NUMBER ;
531 -- HW OPMCONV - Added Qty2
532 l_max_rsv_to_split2 NUMBER ;
533 l_requested_quantity_uom VARCHAR2(3);
534 l_direction_flag VARCHAR2(1);
535 -- End of variables added for bug 2919848
536 /*Bug#8373924*/
537 l_line_ordered_quantinty2 NUMBER;
538 cursor ordered_quantity2(l_source_line_id NUMBER) IS
539 select ordered_quantity2
540 from oe_order_lines
541 where line_id = l_source_line_id;
542
543 -- muom
544 l_fulfillment_base VARCHAR2(1);
545 l_total_unstaged_quantity2 NUMBER ;
546 l_update_qty number;
547 l_net_weight number;
548 l_volume number;
549 l_return_status varchar2(1);
550 l_rsv_to_split NUMBER;
551 l_rsv_to_split2 NUMBER;
552
553 BEGIN
554 --
555 --
556 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
557 --
558 IF l_debug_on IS NULL
559 THEN
560 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
561 END IF;
562 --
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.push(l_module_name);
565 --
566 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
567 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
568 END IF;
569 --
570 l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
571 SAVEPOINT before_splits;
572
573 l_total_requested_quantity := 0 ;
574 -- HW OPMCONV - Added Qty2
575 l_total_requested_quantity2 :=0;
576 l_last_orig_line_id := 0 ;
577
578 l_prev_source_line_id := 0;
579
580 <<records_loop>>
581 FOR i IN 1..p_split_lines.count-1 LOOP
582
583 l_counter := p_split_lines(i).changed_attributes_index ;
584
585 IF p_changed_attributes(l_counter).action_flag = 'S' THEN
586
587 IF p_interface_flag = 'Y' THEN
588 IF l_debug_on THEN
589 WSH_DEBUG_SV.log(l_module_name,'original_source_line_id',p_changed_attributes(l_counter).original_source_line_id);
590 WSH_DEBUG_SV.log(l_module_name,'source_line_id',p_changed_attributes(l_counter).source_line_id);
591 WSH_DEBUG_SV.log(l_module_name,'source_line_set_id',p_changed_attributes(l_counter).source_line_set_id);
592 WSH_DEBUG_SV.log(l_module_name,'shipped_flag',p_changed_attributes(l_counter).shipped_flag);
593 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity',p_changed_attributes(l_counter).ordered_quantity);
594 END IF;
595 END IF;
596
597 l_uom_converted := FALSE;
598 l_ato_split := FALSE;
599
600 l_last_orig_line_id := p_changed_attributes(l_counter).original_source_line_id ;
601
602 -- muom
603 l_fulfillment_base := WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE',l_last_orig_line_id);
604
605 <<details_loop>>
606 FOR c IN c_details(p_source_code,
607 p_changed_attributes(l_counter).original_source_line_id,
608 p_interface_flag,
609 p_changed_attributes(l_counter).shipped_flag,
610 -- muom
611 l_fulfillment_base)
612 LOOP
613
614 l_trnfer_reservations := NULL; -- bug # 9410461: initializing the flag value to NULL.
615 --ECO 4524041
616 --Do not allow split for Released to Warehouse lines which have been
617 --progressed from Planned for Crossdocking status
618 --Bugfix 10135994 added condition for p_interface_flag
619 IF ((c.released_status = 'S') AND (c.move_order_line_id IS NOT NULL) AND (p_interface_flag = 'N')) THEN
620 --{
621 IF l_debug_on THEN
622 WSH_DEBUG_SV.log(l_module_name,'Checking Released to Warehouse line,MOL is--',c.move_order_line_id) ;
623 END IF;
624 -- Keeping the function call separate to avoid extra overhead of calling
625 -- a function for each check, it will be called only when above is
626 -- satisfied
627 IF wsh_usa_inv_pvt.is_mo_type_putaway (c.move_order_line_id) = 'Y' THEN
628 --{
629 IF l_debug_on THEN
630 WSH_DEBUG_SV.log(l_module_name,'DO NOT ALLOW SPLIT FOR PUTAWAY MOL,---',c.move_order_line_id) ;
631 WSH_DEBUG_SV.log(l_module_name,'ORDER NUMBER---',c.source_header_number) ;
632 END IF;
633 --Split API is called for a source line and if any of the details is
634 --in Rel. to warehouse status as described above, the flow needs to
635 --stop
636 FND_MESSAGE.SET_NAME('WSH', 'WSH_SPLIT_NOT_ALLOWED');
637 FND_MESSAGE.SET_TOKEN('ORDER', c.source_header_number);
638 WSH_UTIL_CORE.add_message(l_rs,l_module_name);
639 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
640 ROLLBACK to before_splits;
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE ERROR AS SPLIT IS NOT ALLOWED');
643 WSH_DEBUG_SV.pop(l_module_name);
644 END IF;
645 RETURN;
646 END IF;--}
647 END IF;--}
648 --End of ECO 4524041
649
650 -- bug 2662327: overship tolerance fix to update line_set_id for all delivery lines if it is populated
651 -- assumption: line_set_id will be populated only when OM splits order line.
652 IF (p_changed_attributes(l_counter).source_line_set_id <> FND_API.G_MISS_NUM)
653 AND ( (c.source_line_set_id IS NULL)
654 OR (p_changed_attributes(l_counter).source_line_set_id <> c.source_line_set_id))
655 AND (l_prev_source_line_id <> p_changed_attributes(l_counter).original_source_line_id) THEN
656 UPDATE WSH_DELIVERY_DETAILS
657 SET source_line_set_id = p_changed_attributes(l_counter).source_line_set_id,
658 last_update_date = SYSDATE,
659 last_updated_by = FND_GLOBAL.USER_ID,
660 last_update_login = FND_GLOBAL.LOGIN_ID
661 WHERE source_line_id = p_changed_attributes(l_counter).original_source_line_id
662 AND source_code = p_source_code
663 AND container_flag = 'N'
664 AND released_status <> 'D';
665 l_prev_source_line_id := p_changed_attributes(l_counter).original_source_line_id;
666 END IF;
667
668 IF (l_prev_org_id IS NULL)
669 OR (l_prev_org_id <> c.organization_id) THEN -- cache process org call
670 l_prev_org_id := c.organization_id;
671 -- HW OPM for OM changes
672 --
673 -- HW OPMCONV. Removed checking for process org
674
675 END IF; -- cache process org call
676
677 IF NOT l_uom_converted THEN --{
678 l_uom_converted := TRUE;
679 -- HW OPM for OM changes- Need to branch
680 -- HW OPMCONV. Removed forking
681
682 -- bug 3364238 : During OM interface, for non-model lines, we have to transfer all the pending delivery
683 -- details to new order line irrespective of whatever quantity OM passes. If we rely on
684 -- OM quantity there could be some precision loss and we may have orphan pending details
685 -- still attached to original order line.
686 IF ((p_interface_flag = 'Y') AND
687 ((p_changed_attributes(l_counter).top_model_line_id = FND_API.G_MISS_NUM) OR
688 (p_changed_attributes(l_counter).top_model_line_id IS NULL)))
689 THEN
690 OPEN c_req_qty(p_source_code,
691 p_changed_attributes(l_counter).original_source_line_id);
692 FETCH c_req_qty INTO l_quantity_to_split,l_quantity_to_split2; -- muom
693 IF c_req_qty%NOTFOUND THEN
694 IF l_debug_on THEN
695 WSH_DEBUG_SV.log(l_module_name,'c_req_qty notfound for line', p_changed_attributes(l_counter).original_source_line_id);
696 END IF;
697 CLOSE c_req_qty;
698 END IF;
699 CLOSE c_req_qty;
700 IF l_debug_on THEN
701 -- muom
702 WSH_DEBUG_SV.logmsg(l_module_name,'Inside If l_quantity_to_split'||l_quantity_to_split||' l_quantity_to_split2 '||l_quantity_to_split2);
703 END IF;
704 ELSE
705 l_quantity_to_split := WSH_WV_UTILS.Convert_UOM(
706 from_uom => p_changed_attributes(l_counter).order_quantity_uom,
707 to_uom => c.requested_quantity_uom,
708 quantity => p_changed_attributes(l_counter).ordered_quantity,
709 item_id => c.inventory_item_id);
710 END IF;
711
712 -- HW OPMCONV - Moved the following from top + removed forking
713 IF ( p_changed_attributes(l_counter).ordered_quantity2 is NOT NULL) THEN
714 l_quantity_to_split2 := p_changed_attributes(l_counter).ordered_quantity2;
715 END IF;
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.log(l_module_name,'Inside If l_quantity_to_split2', l_quantity_to_split2);
718 END IF;
719
720 -- muom
721 IF ((l_fulfillment_base = 'P' AND p_changed_attributes(l_counter).ordered_quantity > 0 AND l_quantity_to_split <= 0) OR
722 (l_fulfillment_base = 'S' AND p_changed_attributes(l_counter).ordered_quantity2 > 0 AND l_quantity_to_split2 <= 0)) THEN
723 l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
724 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_CANNOT_SPLIT');
725 WSH_UTIL_CORE.add_message(l_rs,l_module_name);
726 EXIT records_loop;
727 END IF;
728 END IF; -- NOT l_uom_converted --}
729
730 -- delete delivery details pending overpick
731 /*
732 IF (c.requested_quantity = 0 AND c.released_status = 'S') THEN
733 */
734 IF (l_fulfillment_base = 'P' AND c.requested_quantity = 0 AND c.released_status = 'S') OR
735 (l_fulfillment_base = 'S' AND c.requested_quantity2 = 0 AND c.released_status = 'S') THEN
736 l_delete_dds( l_delete_dds.count+1 ) := c.delivery_detail_id;
737 GOTO split_next_record;
738 END IF;
739
740 --wrudge
741 -- bug 2121426/2129298: transfer reservations only if delivery line is
742 -- transferred to or split for the new order line.
743 -- Resetting them to NULL will let us know if we need to transfer.
744 l_res_qty_to_transfer := NULL;
745 l_res_qty2_to_transfer := NULL;
746
747 --wrudge
748 -- Bug 2129298: if requested_quantity = 0 and quantity_to_split = 0,
749 -- we should transfer this delivery line to the new order line (since 0=0).
750 -- if requested_quantity = 0, line is overpicked.
751
752 l_move_order_line_status := 'TRANSFER';
753 IF l_debug_on THEN
754 WSH_DEBUG_SV.log(l_module_name,'Processing Delivery Detail ',c.delivery_detail_id);
755 END IF;
756
757 -- muom
758 IF (l_fulfillment_base = 'P' and c.requested_quantity <= l_quantity_to_split) OR
759 (l_fulfillment_base = 'S' and c.requested_quantity2 <= l_quantity_to_split2) THEN
760 -- assign the entire delivery line to new source_line_id
761 l_working_detail_id := c.delivery_detail_id;
762 l_quantity_split := c.requested_quantity;
763 l_quantity_split2 := c.requested_quantity2;
764 --
765 IF l_debug_on THEN
766 WSH_DEBUG_SV.log(l_module_name,'c.requested_quantity',c.requested_quantity);
767 WSH_DEBUG_SV.log(l_module_name,'l_quantity_to_split',l_quantity_to_split);
768 -- HW OPMCONV - Print Qty2
769 WSH_DEBUG_SV.log(l_module_name,'c.requested_quantity2',c.requested_quantity2);
770 WSH_DEBUG_SV.log(l_module_name,'l_quantity_split2',l_quantity_split2);
771 END IF;
772 --
773 UPDATE WSH_DELIVERY_DETAILS
774 SET source_line_id = p_changed_attributes(l_counter).source_line_id,
775 last_update_date = SYSDATE,
776 last_updated_by = FND_GLOBAL.USER_ID,
777 last_update_login = FND_GLOBAL.LOGIN_ID
778 WHERE delivery_detail_id = c.delivery_detail_id;
779
780 --wrudge
781 -- bug 2121426: keep track of reservation quantity to transfer
782 -- Since we transfer the full delivery line to the new order line,
783 -- we should move all of its reservations picked or requested.
784 -- This also takes care of transferring overpicked reservations
785 -- when requested quantity = 0 which is always <= quantity to split.
786 l_res_qty_to_transfer := NVL(c.picked_quantity, c.requested_quantity);
787 l_res_qty2_to_transfer := NVL(c.picked_quantity2, c.requested_quantity2);
788
789 --wrudge
790 -- Bug 2540015
791 IF (c.released_status = 'S') THEN
792 IF (p_interface_flag = 'Y') THEN
793 IF ((p_changed_attributes(l_counter).top_model_line_id = FND_API.G_MISS_NUM) OR
794 (p_changed_attributes(l_counter).top_model_line_id IS NULL)) OR
795 -- the following condition added for bug 3858111(front port of bug 3808946)
796 ( p_changed_attributes(l_counter).top_model_line_id <> FND_API.G_MISS_NUM
797 AND p_changed_attributes(l_counter).top_model_line_id IS NOT NULL
798 AND p_changed_attributes(l_counter).top_model_line_id = p_changed_attributes(l_counter).ato_line_id )
799 THEN
800 IF l_debug_on THEN
801 WSH_DEBUG_SV.logmsg(l_module_name,'Transferring the MO line');
802 END IF;
803 l_move_order_line_status := 'TRANSFER';
804 -- Bug 2939884/2919186
805 -- If the line is in status 'S' we transfer
806 -- the detailed quantity of the move order line
807 -- or the quantity in the delivery detail, which ever is
808 -- greater.
809 IF l_debug_on THEN
810 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_USA_INV_PVT.get_detailed_quantity');
811 WSH_DEBUG_SV.log(l_module_name,'move_order',c.move_order_line_id);
812 END IF;
813
814 -- Bug3143426 (included branch for l_res_qty_to_transfer calculation)
815 -- HW OPMCONV. Removed forking and Call get_detailed_quantity as a procedure
816 -- anxsharm, X-dock changes
817 --{
818 IF c.move_order_line_id IS NOT NULL THEN
819 WSH_USA_INV_PVT.get_detailed_quantity (
820 p_mo_line_id => c.move_order_line_id,
821 x_detailed_qty => l_detailed_qty,
822 x_detailed_qty2 => l_detailed_qty2,
823 x_return_status => x_return_status);
824
825 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
828 EXIT records_loop;
829 END IF;
830 END IF;
831 ELSE
832 l_detailed_qty := c.requested_quantity;
833 l_detailed_qty2 := c.requested_quantity2;
834 END IF; -- if c.move_order_line_id IS NOT NULL
835 --}
836 -- anxsharm, end of X-dock changes
837
838 -- HW OPMCONV - Change values and capture Qty2
839 l_res_qty_to_transfer := GREATEST(l_res_qty_to_transfer, l_detailed_qty);
840 l_res_qty2_to_transfer := GREATEST(l_res_qty2_to_transfer, l_detailed_qty2);
841
842 -- END IF;
843
844 ELSE
845 -- Cancel the move order line for models always.
846 -- Reason: Otherwise we have to match the detailed quantities of MO lines with that of
847 -- Reservations in case of REMNANT models
848 IF l_debug_on THEN
849 WSH_DEBUG_SV.logmsg(l_module_name,'The line belongs to model (not ATO) and DD status is S. Transfer Move order line');
850 END IF;
851 -- bug # 9410461: Changed value from 'CANCEL' to 'PTOTRANSFER'. It prevents the cancelling of move order lines
852 -- associated to PTO components. Code has been added in the API WSH_USA_INV_PVT.Split_Reservation
853 -- for the value 'PTOTRANSFER'.
854 l_move_order_line_status := 'PTOTRANSFER';
855 END IF;
856 ELSE
857 l_move_order_line_status := 'CANCEL';
858 END IF;
859 END IF;
860 -- Bug 2540015
861 -- muom
862 ELSIF (l_fulfillment_base = 'P' and l_quantity_to_split > 0) OR
863 (l_fulfillment_base = 'S' and l_quantity_to_split2 > 0) THEN
864 -- Bug 2129298: since requested_quantity > quantity_to_split > 0,
865 -- we can split the delivery line.
866 --
867 IF l_debug_on THEN
868 WSH_DEBUG_SV.logmsg(l_module_name, 'IN ELSIF IN SPLIT_RECORDS_Int' );
869 END IF;
870 --
871 l_quantity_split := l_quantity_to_split;
872 l_quantity_split2 := l_quantity_to_split2;
873
874 l_detail_info.delivery_detail_id := c.delivery_detail_id;
875 l_detail_info.requested_quantity := c.requested_quantity;
876 l_detail_info.picked_quantity := c.picked_quantity;
877 l_detail_info.shipped_quantity := c.shipped_quantity;
878 l_detail_info.cycle_count_quantity := c.cycle_count_quantity;
879 l_detail_info.requested_quantity_uom := c.requested_quantity_uom;
880
881 l_detail_info.requested_quantity2 := c.requested_quantity2;
882 l_detail_info.picked_quantity2 := c.picked_quantity2;
883 l_detail_info.shipped_quantity2 := c.shipped_quantity2;
884 l_detail_info.cycle_count_quantity2 := c.cycle_count_quantity2;
885 l_detail_info.requested_quantity_uom2 := c.requested_quantity_uom2;
886
887 l_detail_info.organization_id := c.organization_id;
888 l_detail_info.inventory_item_id := c.inventory_item_id;
889 l_detail_info.subinventory := c.subinventory;
890 l_detail_info.lot_number := c.lot_number;
891 -- HW OPMCONV - No need for sublot_number
892 -- l_detail_info.sublot_number := c.sublot_number;
893 l_detail_info.locator_id := c.locator_id;
894 l_detail_info.source_line_id := c.source_line_id;
895 l_detail_info.net_weight := c.net_weight;
896 l_detail_info.cancelled_quantity := c.cancelled_quantity;
897 l_detail_info.cancelled_quantity2 := c.cancelled_quantity2;
898 l_detail_info.serial_number := c.serial_number;
899 l_detail_info.to_serial_number := c.to_serial_number;
900 l_detail_info.transaction_temp_id := c.transaction_temp_id;
901
902 l_detail_info.container_flag := c.container_flag;
903 l_detail_info.delivery_id := c.delivery_id;
904 l_detail_info.parent_delivery_detail_id := c.parent_delivery_detail_id;
905 -- Bug 2419301
906 l_detail_info.oe_interfaced_flag := NULL;
907 --
908 -- J: W/V Changes
909 l_detail_info.gross_weight := c.gross_weight;
910 l_detail_info.volume := c.volume;
911 l_detail_info.weight_uom_code := c.weight_uom_code;
912 l_detail_info.volume_uom_code := c.volume_uom_code;
913 l_detail_info.wv_frozen_flag := c.wv_frozen_flag;
914 -- End W/V Changes
915 --
916 -- K: MDC
917 l_detail_info.parent_delivery_id := c.parent_delivery_id;
918 l_detail_info.wda_type := c.wda_type;
919 -- END K: MDC
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DETAIL_INT IN SPLIT_RECORDS_Int' );
922 END IF;
923 --
924 -- HW OPMCONV. Removed l_process_flag
925 -- Bug 16722851
926 l_rsv_to_split := NULL;
927 l_rsv_to_split2 := NULL;
928
929 WSH_DELIVERY_DETAILS_ACTIONS.Split_Detail_INT(
930 p_old_delivery_detail_rec => l_detail_info,
931 p_new_source_line_id => p_changed_attributes(l_counter).source_line_id,
932 p_quantity_to_split => l_quantity_split,
933 p_quantity_to_split2 => l_quantity_split2,
934 p_split_sn => 'Y',
935 -- muom
936 -- bug 16833534
937 p_source_code => p_source_code,
938 p_fulfillment_base => l_fulfillment_base,
939 x_split_detail_id => l_working_detail_id,
940 -- Bug 16722851
941 x_rsv_to_split => l_rsv_to_split,
942 x_rsv_to_split2 => l_rsv_to_split2,
943 x_return_status => l_rs);
944
945
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.log(l_module_name,'l_rs',l_rs);
948 END IF;
949 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
950 EXIT records_loop;
951 END IF;
952
953 --bug# 6719369 (replenishment project) (begin) : call WMS for whenever there is split on replenishment requested
954 -- delivery detail lines with the new quantity for the old delivery detail line on p_primary_quantity parameter.
955 -- Inturn WMS creates a new replenishment record for p_split_delivery_detail_id with old delivery detail line old qty - old delivery detail line
956 -- new quantity (p_primary_quantity).
957 IF ( c.replenishment_status = 'R' and c.released_status in ('R','B')) THEN
958 --{
959 IF l_debug_on THEN
960 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
961 END IF;
962 WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL(
963 p_delivery_detail_id => l_detail_info.delivery_detail_id,
964 p_primary_quantity => c.requested_quantity - l_quantity_split,
965 p_split_delivery_detail_id => l_working_detail_id,
966 p_split_source_line_id => p_changed_attributes(l_counter).source_line_id,
967 x_return_status => x_return_status);
968 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
969 --{
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
972 WSH_DEBUG_SV.pop(l_module_name);
973 END IF;
974 EXIT records_loop;
975 --}
976 END IF;
977 --}
978 END IF;
979 --bug# 6719369 (replenishment project): end
980
981 -- Bug 16722851
982 l_res_qty2_to_transfer := l_rsv_to_split2;
983 l_res_qty_to_transfer := l_rsv_to_split;
984 l_move_order_line_status := 'CANCEL';
985
986 -- bug fix 2187012 fix#1 start
987 --
988 --{
989 IF l_debug_on THEN
990 WSH_DEBUG_SV.log(l_module_name,'RELEASED STATUS',c.released_status);
991 END IF;
992 IF (c.released_status = 'S' ) THEN
993 IF l_debug_on THEN
994 WSH_DEBUG_SV.log(l_module_name,'MOL id ',c.move_order_line_id);
995 END IF;
996 --X-dock,split
997 -- For rel. to warehouse lines, progressed from X-dock
998 -- validation will be done earlier to stop the flow
999 -- the code would come here only for Inventory org where
1000 -- MOL will be not null or
1001 -- X-dock where MOL will be null, in which case we want to
1002 -- retain the released_status of 'S' with null MOL
1003 IF c.move_order_line_id IS NOT NULL THEN
1004 UPDATE wsh_delivery_details
1005 SET released_status = 'R',
1006 move_order_line_id = NULL
1007 WHERE delivery_detail_id = c.delivery_detail_id;
1008 -- else released_status should be 'S' and MOL should be null
1009 ELSE
1010 UPDATE wsh_delivery_details
1011 SET released_status = 'S',
1012 move_order_line_id = NULL
1013 WHERE delivery_detail_id = c.delivery_detail_id;
1014 END IF;
1015 --End of X-dock,split
1016
1017 --
1018 -- DBI Project
1019 -- Update of wsh_delivery_details where requested_quantity/released_status
1020 -- are changed, call DBI API after the update.
1021 -- This API will also check for DBI Installed or not
1022 IF l_debug_on THEN
1023 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API delievery detail id:',c.delivery_detail_id);
1024 END IF;
1025 l_detail_tab(1) := c.delivery_detail_id;
1026 WSH_INTEGRATION.DBI_Update_Detail_Log
1027 (p_delivery_detail_id_tab => l_detail_tab,
1028 p_dml_type => 'UPDATE',
1029 x_return_status => l_dbi_rs);
1030
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1033 END IF;
1034 --{
1035 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1036 x_return_status := l_dbi_rs;
1037 ROLLBACK to before_splits;
1038 -- just pass this return status to caller API
1039 IF l_debug_on THEN
1040 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1041 WSH_DEBUG_SV.pop(l_module_name);
1042 END IF;
1043 return;
1044 END IF;
1045 --}
1046 -- End of Code for DBI Project
1047 --
1048 -- to be used later for splitting unstaged reservations
1049 l_sdd_tab(l_sdd_tab.count + 1 ) := c.delivery_detail_id ;
1050 END IF;
1051 --}
1052
1053 END IF;
1054
1055 l_quantity_to_split := l_quantity_to_split - l_quantity_split;
1056 l_quantity_to_split2 := l_quantity_to_split2 - l_quantity_split2;
1057
1058 -- mark ATO split flag for CTO callback.
1059 IF c.ato_line_id IS NOT NULL THEN
1060 l_ato_split := TRUE;
1061 END IF;
1062 --
1063 -- bug # 9410461: Begin
1064 -- Need to call the transfer/cancle reservation API
1065 -- only when the released to warehouse dd is considereed
1066 -- to be transferred to the new line.
1067 -- NOTE: L_RES_QTY_TO_TRANSFER gets some value only when DD is transferred to new line.
1068 IF (c.released_status = 'S') AND (p_interface_flag = 'Y')
1069 AND ( p_changed_attributes(l_counter).top_model_line_id <> FND_API.G_MISS_NUM
1070 AND p_changed_attributes(l_counter).top_model_line_id IS NOT NULL
1071 AND (p_changed_attributes(l_counter).ato_line_id IS NULL OR p_changed_attributes(l_counter).ato_line_id = FND_API.G_MISS_NUM )) THEN
1072 --{
1073 IF L_RES_QTY_TO_TRANSFER IS NULL THEN
1074 l_trnfer_reservations := 'N';
1075 ELSE
1076 l_trnfer_reservations := 'Y';
1077 END IF;
1078 --}
1079 END IF;
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.logmsg(l_module_name, 'l_trnfer_reservations:' || l_trnfer_reservations);
1082 END IF;
1083 -- bug # 9410461: end
1084 --
1085 -- update reservations only if this delivery line can have them:
1086 -- non transactable lines do not have reservations.
1087 -- And delivery lines interfaced to inventory have consumed reservations.
1088 -- Bug 2119916: backordered delivery lines could still have
1089 -- reservations (from PO and WIP for expected quantities,
1090 -- as well as INV which user may manually create).
1091 -- Since we take care of other statuses first (except 'S')
1092 -- it should be OK to split reservations for 'B'.
1093
1094 -- Bug 2121426/2129298: transfer reservations only if we have
1095 -- transferred delivery line to the new order line.
1096 --
1097 IF l_debug_on THEN
1098 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVATION QUANTITY TO TRANSFER: ' || L_RES_QTY_TO_TRANSFER );
1099 -- HW OPMCONV -Added Qty2
1100 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVATION QUANTITY2 TO TRANSFER: ' || L_RES_QTY2_TO_TRANSFER );
1101 END IF;
1102 --
1103 -- bug 3364238 - added the condition when released_status is 'S' and get_detailed_quantity > 0
1104 --- HW 3530178 added the l_process_flag condition
1105 -- HW OPMCONV - Call get_detailed_quantity as a procedure and
1106 -- removed forking
1107
1108 -- detailed quantity is only for details released to warehouse.
1109 -- anxsharm, X-dock changes
1110 --{
1111 IF c.released_status = 'S' AND c.move_order_line_id IS NOT NULL THEN
1112 WSH_USA_INV_PVT.get_detailed_quantity (
1113 p_mo_line_id => c.move_order_line_id,
1114 x_detailed_qty => l_detailed_qty,
1115 x_detailed_qty2 => l_detailed_qty2,
1116 x_return_status => x_return_status);
1117
1118 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1119 IF l_debug_on THEN
1120 WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
1121 EXIT records_loop;
1122 END IF;
1123 END IF;
1124 ELSE
1125 l_detailed_qty := c.requested_quantity;
1126 l_detailed_qty2 := c.requested_quantity2;
1127 END IF; -- if c.move_order_line_id IS NOT NULL
1128 --}
1129 -- anxsharm, end of X-dock changes
1130 -- bug # 9410461: Added l_trnfer_reservations condition.
1131 -- HW OPMCONV - Use l_detailed_qty instead of calling get_detailed_quantity directly
1132 -- and removed check for process in condition
1133 IF ( c.released_status in ('Y', 'C')) OR
1134 ( ( (c.released_status = 'S'
1135 -- muom
1136 AND ((l_fulfillment_base = 'P' and l_detailed_qty > 0) OR (l_fulfillment_base = 'S' and l_detailed_qty2 > 0))
1137 AND l_trnfer_reservations IS NULL)
1138 OR
1139 (l_trnfer_reservations = 'Y' ))) OR
1140 --bug 6313281: added status 'B' for ato_lines as they can have reservations.
1141 ( c.released_status = 'B' AND l_ato_split ) THEN
1142 l_reservable := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1143 x_item_id => c.inventory_item_id,
1144 x_organization_id => c.organization_id,
1145 x_pickable_flag => c.pickable_flag);
1146
1147 IF l_reservable = 'Y' THEN
1148 -- HW 3530178 added the l_process_flag condition
1149 -- HW OPMCONV - Removed forking
1150 -- bug 8754085 commented both the lines below
1151 -- l_res_qty_to_transfer := l_quantity_split;
1152 -- HW OPMCONV Added Qty2
1153 -- l_res_qty2_to_transfer := l_quantity_split2;
1154
1155 l_detail_inv_rec.delivery_detail_id := l_working_detail_id;
1156 l_detail_inv_rec.released_status := c.released_status;
1157 l_detail_inv_rec.move_order_line_id := c.move_order_line_id;
1158 l_detail_inv_rec.organization_id := c.organization_id;
1159 l_detail_inv_rec.inventory_item_id := c.inventory_item_id;
1160 l_detail_inv_rec.subinventory := c.subinventory;
1161 l_detail_inv_rec.revision := c.revision;
1162 l_detail_inv_rec.lot_number := c.lot_number;
1163 l_detail_inv_rec.locator_id := c.locator_id;
1164 -- Bug 2773605 : Update the lpn_id also to pass to Split_Reservation.
1165 l_detail_inv_rec.lpn_id := c.lpn_id;
1166
1167 --wrudge
1168 -- bug 2121426: pass the correct quantity of reservations to transfer
1169 --
1170 -- bugfix 8780988 Do not transfer the reservation when organization is changed for a split line (manual split)
1171 IF l_debug_on THEN
1172 WSH_DEBUG_SV.logmsg(l_module_name, 'p_changed_attributes(l_counter).ship_from_org_id: ' || p_changed_attributes(l_counter).ship_from_org_id );
1173 WSH_DEBUG_SV.logmsg(l_module_name, 'l_detail_inv_rec.organization_id: ' || l_detail_inv_rec.organization_id );
1174 END IF;
1175
1176 --bugfix 8976069 added join for 'Y' and 'C'
1177 IF (p_changed_attributes(l_counter).ship_from_org_id <> l_detail_inv_rec.organization_id) and c.released_status in ('Y', 'C') THEN
1178
1179 WSH_USA_INV_PVT.cancel_staged_reservation(p_source_code => p_source_code,
1180 p_source_header_id => p_changed_attributes(l_counter).source_header_id,
1181 p_source_line_id => p_changed_attributes(l_counter).original_source_line_id,
1182 p_delivery_detail_split_rec => l_detail_inv_rec,
1183 p_cancellation_quantity =>l_res_qty_to_transfer,
1184 p_cancellation_quantity2 =>l_res_qty2_to_transfer,
1185 x_return_status => l_rs);
1186
1187
1188 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.logmsg(l_module_name,'unexpected error after WSH_USA_INV_PVT.cancel_staged_reservation ');
1191 END IF;
1192 exit records_loop ;
1193 END IF;
1194
1195 ELSE
1196
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.SPLIT_RESERVATION',WSH_DEBUG_SV.C_PROC_LEVEL);
1199 END IF;
1200 --
1201 -- Added parameter p_shipped_flag for bug 10105817
1202 WSH_USA_INV_PVT.Split_Reservation (
1203 p_delivery_detail_split_rec => l_detail_inv_rec,
1204 p_source_code => p_source_code,
1205 p_source_header_id => p_changed_attributes(l_counter).source_header_id,
1206 p_original_source_line_id => p_changed_attributes(l_counter).original_source_line_id,
1207 p_split_source_line_id => p_changed_attributes(l_counter).source_line_id,
1208 p_split_quantity => l_res_qty_to_transfer,
1209 p_split_quantity2 => l_res_qty2_to_transfer,
1210 p_move_order_line_status => l_move_order_line_status,
1211 p_direction_flag => p_split_lines(i).direction_flag ,
1212 p_shipped_flag => p_changed_attributes(l_counter).shipped_flag,
1213 x_return_status => l_rs);
1214
1215 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1216 EXIT records_loop;
1217 END IF;
1218 END IF; --if ship_from_org_id
1219
1220 ELSE
1221 l_total_unstaged_quantity := l_total_unstaged_quantity + NVL(c.picked_quantity, c.requested_quantity);
1222 -- muom
1223 l_total_unstaged_quantity2 := l_total_unstaged_quantity2 + NVL(c.picked_quantity2, c.requested_quantity2);
1224 END IF; -- If reservable
1225 END IF; -- If c.released_status = 'Y'
1226
1227 --wrudge
1228 -- Bug 2129298: when quantity_to_split becomes zero during a normal
1229 -- user-initated action, we are done.
1230 -- But when we do the split during OM Interface, we need to continue
1231 -- the loop and look for other delivery lines with requested_quantity = 0.
1232 -- This ensures that the overpicked delivery lines are all moved to the
1233 -- new order line, since the original order line becomes closed.
1234 -- muom
1235 IF ((l_fulfillment_base = 'P' and l_quantity_to_split <= 0) OR (l_fulfillment_base = 'S' and l_quantity_to_split2 <= 0))
1236 AND (p_interface_flag = 'N') THEN
1237 -- We are finished with splitting this original source line.
1238 EXIT details_loop;
1239 END IF;
1240
1241 END LOOP; -- delivery lines loop
1242
1243 -- muom
1244 /* Reset delivery details with 0 requested quantity to converted quantity from secondary */
1245 IF l_fulfillment_base = 'S' THEN
1246
1247 IF l_debug_on THEN
1248 WSH_DEBUG_SV.logmsg(l_module_name,'Adjusting Delivery Details with 0 Requested Quantity for line_id '||p_changed_attributes(l_counter).original_source_line_id);
1249 END IF;
1250
1251 FOR crec in c_zero_req_cur(p_changed_attributes(l_counter).original_source_line_id) LOOP
1252 IF l_debug_on THEN
1253 WSH_DEBUG_SV.logmsg(l_module_name,'Adjusting Delivery Detail => '||crec.delivery_detail_id);
1254 END IF;
1255 l_update_qty := WSH_WV_UTILS.Convert_UOM(
1256 from_uom => crec.requested_quantity_uom2,
1257 to_uom => crec.requested_quantity_uom,
1258 quantity => crec.requested_quantity2,
1259 item_id => crec.inventory_item_id);
1260
1261 update wsh_delivery_details
1262 set requested_quantity = l_update_qty,
1263 last_update_date = SYSDATE,
1264 last_updated_by = FND_GLOBAL.USER_ID,
1265 last_update_login = FND_GLOBAL.LOGIN_ID
1266 where delivery_detail_id = crec.delivery_detail_id;
1267
1268 IF l_debug_on THEN
1269 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Detail_Weight_Volume',WSH_DEBUG_SV.C_PROC_LEVEL);
1270 END IF;
1271
1272 WSH_WV_UTILS.Detail_Weight_Volume (
1273 p_delivery_detail_id => crec.delivery_detail_id,
1274 p_update_flag => 'Y',
1275 p_post_process_flag => 'Y',
1276 x_net_weight => l_net_weight,
1277 x_volume => l_volume,
1278 x_return_status => l_return_status);
1279
1280 END LOOP;
1281
1282 END IF;
1283
1284 -- after split of ATO delivery lines,
1285 -- we need to call CTO to update information on ATO for the new order line.
1286 IF l_ato_split AND p_source_code = 'OE' THEN
1287
1288 -- update CTO for newly split order line
1289 IF l_debug_on THEN
1290 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CTO_WORKFLOW_API_PK.WF_UPDATE_AFTER_INV_UNRESERV',WSH_DEBUG_SV.C_PROC_LEVEL);
1291 END IF;
1292 --
1293 CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv(
1294 p_order_line_id => p_changed_attributes(l_counter).source_line_id,
1295 x_return_status => l_rs,
1296 x_msg_count => l_msg_count,
1297 x_msg_data => l_msg_data);
1298 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1299 IF l_debug_on THEN
1300 WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
1301 END IF;
1302 EXIT records_loop;
1303 END IF;
1304 -- update CTO for original order line
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit CTO_WORKFLOW_API_PK.WF_UPDATE_AFTER_INV_UNRESERV',WSH_DEBUG_SV.C_PROC_LEVEL);
1307 END IF;
1308 --
1309 CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv(
1310 p_order_line_id => p_changed_attributes(l_counter).original_source_line_id,
1311 x_return_status => l_rs,
1312 x_msg_count => l_msg_count,
1313 x_msg_data => l_msg_data);
1314 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
1317 END IF;
1318 EXIT records_loop;
1319 END IF;
1320
1321
1322 END IF; -- l_ato_split AND p_source_code = 'OE'
1323
1324 /* Bug#8373924 */
1325 OPEN ordered_quantity2(p_changed_attributes(l_counter).original_source_line_id);
1326 FETCH ordered_quantity2 INTO l_line_ordered_quantinty2;
1327 CLOSE ordered_quantity2;
1328
1329 -- bug fix 2187012 fix #2 start
1330 -- No Update of released status
1331 UPDATE WSH_DELIVERY_DETAILS
1332 SET SRC_REQUESTED_QUANTITY = SRC_REQUESTED_QUANTITY - p_changed_attributes(l_counter).ordered_quantity,
1333 SRC_REQUESTED_QUANTITY2 = l_line_ordered_quantinty2 --SRC_REQUESTED_QUANTITY2 - p_changed_attributes(l_counter).ordered_quantity2
1334 WHERE SOURCE_LINE_ID = p_changed_attributes(l_counter).original_source_line_id
1335 AND SOURCE_CODE = p_source_code;
1336 -- bug fix 2187012 fix #2 end
1337
1338 END IF; -- p_changed_attributes(l_counter).action_flag = 'S'
1339
1340 <<split_next_record>>
1341 NULL;
1342 END LOOP; -- records loop
1343
1344 l_last_orig_line_id := 0 ;
1345
1346 -- Loop here to calculate the reservations to split for each p_changed_attribute
1347 -- IF Condition added for bug 10105817
1348 -- Proceed further only l_rs is SUCCESS/WARNING.
1349 -- Added If Condition, since ITS proceeds further and completes normally even if API
1350 -- WSH_USA_INV_PVT.Split_Reservation returs Expected/Unexpected error.
1351 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN -- {
1352 -- Loop here to calculate the reservations to split for each p_changed_attribute
1353
1354
1355 l_total_spare_rsv := 0; --bugfix 8780988
1356 l_total_spare_rsv2 := 0; --bugfix 8780988
1357
1358
1359 FOR i IN 1..p_split_lines.count -1 LOOP --{
1360
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.logmsg(l_module_name,'i = ' || i ) ;
1363 END IF;
1364 l_counter := p_split_lines(i).changed_attributes_index ;
1365
1366 IF l_debug_on THEN
1367 WSH_DEBUG_SV.logmsg(l_module_name,'Calculating unstaged reservations for line_id ' || p_changed_attributes(l_counter).source_line_id || ' , action_flag is ' || p_changed_attributes(l_counter).action_flag );
1368 END IF;
1369
1370 IF p_changed_attributes(l_counter).action_flag = 'S' THEN --{
1371
1372 -- Get total ordered Quantity
1373
1374 IF l_last_orig_line_id <> p_changed_attributes(l_counter).original_source_line_id THEN --{
1375
1376 IF l_debug_on THEN
1377 WSH_DEBUG_SV.logmsg(l_module_name,'new line_id ' || p_changed_attributes(l_counter).original_source_line_id);
1378 END IF;
1379 open c_total_requested_quantity ( p_changed_attributes(l_counter).original_source_line_id);
1380 fetch c_total_requested_quantity into l_inventory_item_id , l_requested_quantity_uom , l_total_requested_quantity,
1381 l_total_requested_quantity2,l_organization_id; --bugfix 8780988
1382 close c_total_requested_quantity ;
1383
1384 -- Get total already reserved on the original line
1385 -- HW OPMCONV - Added Qty2 parameter
1386 --bugfix 8915868 Store the Spare reservation for original line
1387 IF l_total_spare_rsv > 0 THEN
1388
1389 l_cancel_counter :=l_cancel_counter + 1;
1390
1391 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_code:= p_source_code ;
1392 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_header_id:= p_changed_attributes(l_counter).source_header_id;
1393 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_line_id:= l_last_orig_line_id;
1394 l_Cancel_Reservation_Tab_Type(l_cancel_counter).delivery_detail_id:= NULL;
1395 l_Cancel_Reservation_Tab_Type(l_cancel_counter).organization_id:= l_organization_id;
1396 l_Cancel_Reservation_Tab_Type(l_cancel_counter).cancelled_quantity := l_total_spare_rsv;
1397 l_Cancel_Reservation_Tab_Type(l_cancel_counter).cancelled_quantity2 := l_total_spare_rsv2;
1398
1399 END IF;
1400
1401 WSH_USA_INV_PVT.Get_total_reserved_quantity (
1402 p_source_code => p_source_code ,
1403 p_source_header_id => p_changed_attributes(l_counter ).source_header_id ,
1404 p_source_line_id => p_changed_attributes(l_counter ).original_source_line_id ,
1405 p_organization_id => p_changed_attributes(l_counter ).organization_id ,
1406 x_total_rsv => l_total_reserved_quantity ,
1407 x_total_rsv2 => l_total_reserved_quantity2 ,
1408 x_return_status => l_rs );
1409
1410 -- Get spare reservation quantity
1411
1412 l_total_spare_rsv := GREATEST ( l_total_reserved_quantity - l_total_requested_quantity , 0 ) ;
1413 -- HW OPMCONV - AddedQty2
1414 l_total_spare_rsv2 := GREATEST ( l_total_reserved_quantity2 - l_total_requested_quantity2 , 0 ) ;
1415
1416 l_last_orig_line_id := p_changed_attributes(l_counter).original_source_line_id ;
1417
1418 END IF ; --}
1419 --bugfix 8780988 Added check for organization id
1420 -- muom
1421 IF ((l_fulfillment_base = 'P' and l_total_spare_rsv > 0) OR (l_fulfillment_base = 'S' and l_total_spare_rsv2 > 0)) AND
1422 (p_changed_attributes(l_counter).Ship_from_org_id = l_organization_id) THEN --{
1423
1424 -- Get total reserved quantity on current line
1425 -- HW OPMCONV - Pass Qty2 as a parameter
1426 WSH_USA_INV_PVT.Get_total_reserved_quantity (
1427 p_source_code => p_source_code ,
1428 p_source_header_id => p_changed_attributes(l_counter ).source_header_id ,
1429 p_source_line_id => p_changed_attributes(l_counter ).source_line_id ,
1430 p_organization_id => p_changed_attributes(l_counter ).organization_id ,
1431 x_total_rsv => l_total_reserved_quantity ,
1432 x_total_rsv2 => l_total_reserved_quantity2 ,
1433 x_return_status => l_rs );
1434
1435 -- HW OPMCONV. Removed forking
1436 l_quantity_to_split := WSH_WV_UTILS.Convert_UOM(
1437 from_uom => p_changed_attributes(l_counter).order_quantity_uom,
1438 to_uom => l_requested_quantity_uom,
1439 quantity => p_changed_attributes(l_counter).ordered_quantity ,
1440 item_id => l_inventory_item_id);
1441
1442 -- HW OPMCONV - Added Qty2
1443 l_quantity_to_split2 := NVL(p_changed_attributes(l_counter).ordered_quantity2,0);
1444 l_max_rsv_to_split := GREATEST ( l_quantity_to_split - l_total_reserved_quantity , 0 ) ;
1445 -- HW OPMCONV - Added Qty2
1446 l_max_rsv_to_split2 := GREATEST ( l_quantity_to_split2 - l_total_reserved_quantity2 , 0 ) ;
1447
1448 l_total_rsv_to_split(l_counter) := LEAST ( l_total_spare_rsv , l_max_rsv_to_split ) ;
1449
1450 l_total_rsv_to_split2(l_counter) := LEAST ( l_total_spare_rsv2 , l_max_rsv_to_split2 ) ;
1451 l_total_spare_rsv := l_total_spare_rsv - l_total_rsv_to_split(l_counter) ;
1452 -- HW OPMCONV - Added Qty2
1453 l_total_spare_rsv2 := l_total_spare_rsv2 - l_total_rsv_to_split2(l_counter) ;
1454 l_max_rsv_to_split:= 0 ;
1455 -- HW OPMCONV - Added Qty2
1456 l_max_rsv_to_split2:= 0 ;
1457
1458 else
1459 l_total_rsv_to_split(l_counter) := 0 ;
1460 -- HW OPMCONV - Added Qty2
1461 l_total_rsv_to_split2(l_counter) := 0 ;
1462 END IF ; --}
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_rsv_to_split ( ' || l_counter || ' ) = ' || l_total_rsv_to_split(l_counter));
1465 -- HW OPMCONV - Print Qty2
1466 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_rsv_to_split2 ( ' || l_counter || ' ) = ' || l_total_rsv_to_split2(l_counter));
1467 END IF;
1468 END IF ; --} Action_flag = 'S'
1469
1470 END LOOP ;--}
1471
1472 --transfer unstaged reservations loop
1473 <<unstaged_rsv_loop>>
1474 FOR i IN 1..p_changed_attributes.count LOOP --{
1475
1476 l_counter := p_changed_attributes.count -i + 1 ;
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(l_module_name,'Unstg RSV xfr loop : l_counter ' || l_counter );
1479 END IF;
1480
1481 IF p_changed_attributes(l_counter).action_flag = 'S' THEN --{
1482
1483 l_uom_converted := FALSE;
1484
1485 l_total_res_qty_to_transfer := l_total_rsv_to_split(l_counter);
1486 -- HW OPMCONV - Added Qty2
1487 l_total_res_qty2_to_transfer := l_total_rsv_to_split2(l_counter);
1488
1489 IF l_debug_on THEN
1490 WSH_DEBUG_SV.logmsg(l_module_name,'Unstg RSV xfr loop : l_counter ' || l_counter || ' : OLID ' || p_changed_attributes(l_counter).original_source_line_id || ' : LID ' || p_changed_attributes(l_counter).source_line_id );
1491 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_res_qty_to_transfer ' || l_total_res_qty_to_transfer );
1492 -- HW OPMCONV - Print qty2
1493 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_res2_qty_to_transfer ' || l_total_res_qty2_to_transfer );
1494 END IF;
1495
1496 -- muom Bug 16722851
1497 if ( l_fulfillment_base = 'P' and l_total_res_qty_to_transfer > 0 ) OR ( l_fulfillment_base = 'S' and l_total_res_qty2_to_transfer > 0 ) then --{
1498
1499 FOR c IN c_details(p_source_code,
1500 p_changed_attributes(l_counter).source_line_id,
1501 p_interface_flag,
1502 p_changed_attributes(l_counter).shipped_flag,
1503 -- muom
1504 l_fulfillment_base ) LOOP --{
1505 -- bug 3364238 - added the condition when released_status is 'S' and get_detailed_quantity > 0
1506 -- HW OPMCONV - Get detailed_qty using new procedure
1507
1508 -- detailed quantity is only for details released to warehouse.
1509 -- anxsharm, X-dock changes
1510 --{
1511 IF c.released_status = 'S' AND c.move_order_line_id IS NOT NULL THEN
1512 WSH_USA_INV_PVT.get_detailed_quantity (
1513 p_mo_line_id => c.move_order_line_id,
1514 x_detailed_qty => l_detailed_qty,
1515 x_detailed_qty2 => l_detailed_qty2,
1516 x_return_status => x_return_status);
1517
1518 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1519 IF l_debug_on THEN
1520 WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
1521 EXIT unstaged_rsv_loop;
1522 END IF;
1523 END IF;
1524 ELSE
1525 l_detailed_qty := c.requested_quantity;
1526 l_detailed_qty2 := c.requested_quantity2;
1527 END IF; -- if released to whse and c.move_order_line_id IS NOT NULL
1528 --}
1529 -- anxsharm, end of X-dock changes
1530
1531 -- muom
1532 IF ( c.released_status NOT IN ('Y','C') ) AND
1533 ((l_fulfillment_base = 'P' and NOT ( c.released_status = 'S' AND l_detailed_qty > 0 )) OR
1534 (l_fulfillment_base = 'S' and NOT ( c.released_status = 'S' AND l_detailed_qty2 > 0 ))) THEN -- {
1535 IF NOT l_uom_converted THEN --{
1536 l_uom_converted := TRUE;
1537 -- HW OPMCONV. Removed forking
1538 l_quantity_to_split := WSH_WV_UTILS.Convert_UOM(
1539 from_uom => p_changed_attributes(l_counter).order_quantity_uom,
1540 to_uom => c.requested_quantity_uom,
1541 quantity => p_changed_attributes(l_counter).ordered_quantity,
1542 item_id => c.inventory_item_id);
1543 -- HW OPMCONV - Added Qty2
1544
1545 l_quantity_to_split2 := p_changed_attributes(l_counter).ordered_quantity2;
1546 END IF ; --}
1547
1548 l_detail_inv_rec.delivery_detail_id := c.delivery_detail_id ;
1549
1550 l_found := FALSE ;
1551 l_move_order_line_status := 'TRANSFER';
1552 for i in 1..l_sdd_tab.count loop
1553 if l_sdd_tab(i) = c.delivery_Detail_id then
1554 l_found := TRUE ;
1555 l_move_order_line_status := 'CANCEL';
1556 exit ;
1557 end if ;
1558 end loop ;
1559
1560 if not l_found then
1561 l_detail_inv_rec.released_status := c.released_status;
1562 else
1563 l_detail_inv_rec.released_status := 'S' ;
1564 end if ;
1565
1566 l_detail_inv_rec.move_order_line_id := c.move_order_line_id;
1567 l_detail_inv_rec.organization_id := c.organization_id;
1568 l_detail_inv_rec.inventory_item_id := c.inventory_item_id;
1569 l_detail_inv_rec.subinventory := c.subinventory;
1570 l_detail_inv_rec.revision := c.revision;
1571 l_detail_inv_rec.lot_number := c.lot_number;
1572 l_detail_inv_rec.locator_id := c.locator_id;
1573 l_detail_inv_rec.lpn_id := c.lpn_id;
1574
1575 IF l_debug_on THEN
1576 WSH_DEBUG_SV.logmsg(l_module_name,'TRansfering unstaged reservations : BEfore WSH_USA_INV_PVT.Split_Reservation ');
1577 END IF;
1578 -- HW OPMCONV - Added Qty2
1579 l_res_qty_to_transfer := LEAST ( nvl(c.picked_quantity , c.requested_quantity ) ,
1580 l_total_res_qty_to_transfer );
1581
1582 l_res_qty2_to_transfer := LEAST ( nvl(c.picked_quantity2 , c.requested_quantity2 ) ,
1583 l_total_res_qty2_to_transfer );
1584
1585
1586 IF l_debug_on THEN
1587 WSH_DEBUG_SV.logmsg(l_module_name,'c.picked_quantity ' || c.picked_quantity );
1588 WSH_DEBUG_SV.logmsg(l_module_name,'c.requested_quantity ' || c.requested_quantity );
1589 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_res_qty_to_transfer ' || l_total_res_qty_to_transfer );
1590 -- HW OPMCONV - Print Qty2
1591 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_res_qty2_to_transfer ' || l_total_res_qty2_to_transfer );
1592 WSH_DEBUG_SV.logmsg(l_module_name,'c.picked_quantity2 ' || c.picked_quantity2 );
1593 WSH_DEBUG_SV.logmsg(l_module_name,'c.requested_quantity2 ' || c.requested_quantity2 );
1594 WSH_DEBUG_SV.logmsg(l_module_name,'l_res_qty_to_transfer = min of above = ' || l_res_qty_to_transfer );
1595 -- HW OPMCONV - Print Qty2
1596 WSH_DEBUG_SV.logmsg(l_module_name,'l_res_qty2_to_transfer = min of above = ' || l_res_qty2_to_transfer );
1597 WSH_DEBUG_SV.logmsg(l_module_name,'released_status = ' || c.released_Status );
1598 END IF;
1599
1600 -- Bugfix 8864613 add code to get the direction flag
1601 l_direction_flag := NULL;
1602 --
1603 FOR j IN 1..p_split_lines.count LOOP
1604 --
1605 IF p_changed_attributes(l_counter).source_line_id = p_split_lines(j).source_line_id THEN
1606 l_direction_flag :=p_split_lines(j).direction_flag ;
1607 EXIT;
1608 END IF;
1609 --
1610 END LOOP;
1611
1612 --
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.logmsg(l_module_name,'l_direction_flag = ' || l_direction_flag );
1615 WSH_DEBUG_SV.logmsg(l_module_name,'p_shipped_flag = '||p_changed_attributes(l_counter).shipped_flag);
1616 END IF;
1617 --
1618 WSH_USA_INV_PVT.Split_Reservation (
1619 p_delivery_detail_split_rec => l_detail_inv_rec,
1620 p_source_code => p_source_code,
1621 p_source_header_id => p_changed_attributes(l_counter).source_header_id,
1622 p_original_source_line_id => p_changed_attributes(l_counter).original_source_line_id,
1623 p_split_source_line_id => p_changed_attributes(l_counter).source_line_id,
1624 p_split_quantity => l_res_qty_to_transfer,
1625 p_split_quantity2 => l_res_qty2_to_transfer,
1626 p_move_order_line_status => l_move_order_line_status, -- spltrsv:this needs to be set correctly.
1627 p_direction_flag => l_direction_flag,
1628 p_shipped_flag => p_changed_attributes(l_counter).shipped_flag,
1629 x_return_status => l_rs);
1630
1631 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1632 IF l_debug_on THEN
1633 WSH_DEBUG_SV.logmsg(l_module_name,'unexpected error after WSH_USA_INV_PVT.Split_Reservation ');
1634 END IF;
1635 exit unstaged_rsv_loop ;
1636 END IF;
1637
1638
1639 l_total_res_qty_to_transfer := l_total_res_qty_to_transfer - l_res_qty_to_transfer ;
1640 -- HW OPMCONV - Added Qty2
1641 l_total_res_qty2_to_transfer := l_total_res_qty2_to_transfer - l_res_qty2_to_transfer ;
1642
1643 -- muom
1644 IF ( (l_fulfillment_base = 'P' and l_total_res_qty_to_transfer <= 0) OR (l_fulfillment_base = 'S' and l_total_res_qty2_to_transfer <= 0) ) then
1645 IF l_debug_on THEN
1646 WSH_DEBUG_SV.logmsg(l_module_name,'Exiting to next source_line');
1647 END IF;
1648 EXIT ;
1649 END IF ;
1650 END IF; --} c.released_status NOT IN ('Y','C')
1651 END LOOP ; --}
1652 END IF ; --} if total reservation to transfer > 0
1653 END IF ; --} Action_flag = 'S'
1654 END LOOP; --}
1655 --
1656
1657 IF l_debug_on THEN
1658 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_spare_rsv = ' ||l_total_spare_rsv);
1659 WSH_DEBUG_SV.logmsg(l_module_name,'l_total_spare_rsv2 = ' ||l_total_spare_rsv2);
1660 END IF;
1661 -- bugfix 8780988 delete all the remaining unstaged reservation
1662 -- l_total_spare_rsv is populated only for split and unstaged reservation
1663 -- bugfix 8915868
1664 -- bug10082354 added check for p_interface_flag
1665
1666 -- muom
1667 IF ((l_fulfillment_base = 'P' and l_total_spare_rsv > 0) OR (l_fulfillment_base = 'S' and l_total_spare_rsv2 > 0)) AND (p_interface_flag = 'N') THEN
1668
1669 l_cancel_counter := l_cancel_counter + 1 ;
1670
1671 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_code:= p_source_code ;
1672 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_header_id:= p_changed_attributes(l_counter).source_header_id;
1673 l_Cancel_Reservation_Tab_Type(l_cancel_counter).source_line_id:= p_changed_attributes(l_counter).original_source_line_id;
1674 l_Cancel_Reservation_Tab_Type(l_cancel_counter).delivery_detail_id:= NULL;
1675 l_Cancel_Reservation_Tab_Type(l_cancel_counter).organization_id:= l_organization_id;
1676 l_Cancel_Reservation_Tab_Type(l_cancel_counter).cancelled_quantity := l_total_spare_rsv;
1677 l_Cancel_Reservation_Tab_Type(l_cancel_counter).cancelled_quantity2 := l_total_spare_rsv2;
1678
1679 END IF;
1680 --
1681 -- bug10082354 added check for p_interface_flag
1682 IF (l_cancel_counter > 0) AND (p_interface_flag = 'N') THEN
1683
1684 FOR i in 1..l_cancel_counter LOOP
1685
1686 WSH_USA_INV_PVT.cancel_nonstaged_reservation( p_source_code => l_Cancel_Reservation_Tab_Type(i).source_code,
1687 p_source_header_id => l_Cancel_Reservation_Tab_Type(i).source_header_id,
1688 p_source_line_id => l_Cancel_Reservation_Tab_Type(i).source_line_id,
1689 p_delivery_detail_id => l_Cancel_Reservation_Tab_Type(i).delivery_detail_id,
1690 p_organization_id => l_Cancel_Reservation_Tab_Type(i).organization_id ,
1691 p_cancellation_quantity => l_Cancel_Reservation_Tab_Type(i).cancelled_quantity,
1692 p_cancellation_quantity2 => l_Cancel_Reservation_Tab_Type(i).cancelled_quantity2,
1693 x_return_status => l_rs);
1694 --
1695 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1696 IF l_debug_on THEN
1697 WSH_DEBUG_SV.logmsg(l_module_name,'unexpected error after WSH_USA_INV_PVT.cancel_nonstaged_reservation');
1698 END IF;
1699 END IF;
1700 --
1701 END LOOP ;
1702 END IF;
1703
1704 l_cancel_counter := 0;
1705
1706 END IF; -- } Added for bug 10105817
1707 --
1708 -- purge delivery details pending overpick
1709 IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1710 AND l_delete_dds.count > 0 THEN
1711 WSH_INTERFACE.Delete_Details(
1712 p_details_id => l_delete_dds,
1713 x_return_status => l_rs
1714 );
1715 END IF;
1716
1717 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1718 ROLLBACK to before_splits;
1719 END IF;
1720
1721 x_return_status := l_rs;
1722 --
1723 IF l_debug_on THEN
1724 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1725 WSH_DEBUG_SV.pop(l_module_name);
1726 END IF;
1727 --
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 ROLLBACK TO before_splits;
1731 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1732 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_Records_Int',l_module_name);
1733 --
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1736 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1737 END IF;
1738 --
1739 END Split_Records_Int;
1740
1741 PROCEDURE Split_Records (
1742 p_source_code IN VARCHAR2,
1743 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
1744 p_interface_flag IN VARCHAR2,
1745 x_return_status OUT NOCOPY VARCHAR2
1746 ) IS
1747 l_index number ;
1748 l_direction_flag direction_flag_tab_type ;
1749 l_index_tab WSH_UTIL_CORE.ID_Tab_Type ;
1750 l_split_table split_Table_Tab_Type ;
1751 l_smallest_orig_line_id NUMBER ;
1752 l_updates_count NUMBER ;
1753 l_original_line_found BOOLEAN ;
1754 l_smallest_date DATE ;
1755 l_return_status VARCHAR2(1);
1756 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Split_Records';
1757 l_debug_on BOOLEAN;
1758 BEGIN
1759 --
1760 --
1761 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1762 --
1763 IF l_debug_on IS NULL
1764 THEN
1765 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1766 END IF;
1767 --
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.push(l_module_name);
1770 --
1771 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1772 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1773 WSH_DEBUG_SV.logmsg(l_module_name,'Inside Split_Records');
1774 END IF;
1775
1776 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1777
1778 -- Populate p_split_table
1779 l_smallest_orig_line_id := 0 ;
1780 l_updates_count := 0 ;
1781
1782 FOR i in p_changed_attributes.FIRST..p_changed_attributes.LAST --{loop on p_changed_attributes to scan for request_date on update records.
1783 LOOP
1784
1785 IF l_debug_on THEN
1786 WSH_DEBUG_SV.log(l_module_name,'Index i',i);
1787 END IF;
1788
1789 IF p_changed_attributes(i).action_flag = 'U' THEN
1790 l_updates_count := l_updates_count + 1 ;
1791 ELSIF p_changed_attributes(i).action_flag = 'S' THEN
1792 -- is this the earlier original_source_line ?
1793 if p_changed_attributes(i).original_source_line_id < l_smallest_orig_line_id then
1794 l_smallest_orig_line_id := p_changed_attributes(i).original_source_line_id ;
1795 l_smallest_date := p_changed_attributes(i).date_requested ;
1796 end if ;
1797
1798 -- Bug 2896725 : update the next record in l_split_table .
1799 l_split_table(l_split_table.count+1 ).source_line_id := p_changed_attributes(i).source_line_id ;
1800 l_split_table(l_split_table.count ).original_source_line_id := p_changed_attributes(i).original_source_line_id ;
1801
1802 l_split_table(l_split_table.count ).changed_Attributes_index := i;
1803
1804 FOR j in p_changed_attributes.FIRST..p_changed_attributes.LAST --{
1805 LOOP
1806 IF p_changed_attributes(j).action_flag = 'U' and
1807 p_changed_Attributes(i).source_line_id = p_changed_attributes(j).source_line_id THEN
1808 l_split_table(l_split_table.count).date_requested := p_changed_attributes(j).date_requested ;
1809 l_split_table(l_split_table.count).date_requested := p_changed_attributes(j).date_requested ;
1810 END IF ;
1811 END LOOP; --}
1812
1813 END IF ;
1814 END LOOP ; --} loop on p_changed_attributes ..
1815
1816 -- Populate the last split record with the smalled orginal source line id
1817 -- and its scheduled date for reference later for setting the direction.
1818
1819 IF l_debug_on THEN
1820 WSH_DEBUG_SV.logmsg(l_module_name,'Inside Split_Records: split_table.count = ' || l_split_table.count );
1821 END IF;
1822
1823 if l_split_table.count > 0 then --{ call sort_splits if one or more records
1824 -- because sort_splits also sets the direction_flag
1825 l_split_Table(l_split_table.count + 1 ).original_source_line_id := l_smallest_orig_line_id ;
1826 l_split_Table(l_split_table.count ).source_line_id := l_smallest_orig_line_id ;
1827 l_split_Table(l_split_table.count ).date_requested := l_smallest_date ;
1828
1829 IF l_debug_on THEN
1830 WSH_DEBUG_SV.logmsg(l_module_name,'Before sort splits' );
1831
1832 WSH_DEBUG_SV.logmsg(l_module_name,'[original source_line AttrIdx date-requested direction_flag]' );
1833 END IF;
1834
1835 FOR i in 1..l_split_table.count
1836 LOOP
1837 IF l_debug_on THEN
1838 WSH_DEBUG_SV.logmsg(l_module_name,'[' || l_split_table(i).original_source_line_id || ' , '
1839 || l_split_table(i).source_line_id || ' , '
1840 || l_split_table(i).changed_Attributes_index || ' , '
1841 || l_split_table(i).date_requested || ' , '
1842 || l_split_table(i).direction_flag || ' ] ' );
1843
1844 END IF;
1845 END LOOP;
1846
1847 -- Bug 2899127: even if one record exists , we still need to pass
1848 -- l_split_table to sort_splits to get the direction
1849 -- flag set correctly.
1850 if l_split_table.count > 1 then
1851 sort_splits (p_index_tab => l_index_tab ,
1852 p_split_table => l_split_table,
1853 x_return_status => l_return_status );
1854
1855 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1856 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1857 return ;
1858 END IF;
1859 end if ;
1860
1861 IF l_debug_on THEN
1862 WSH_DEBUG_SV.logmsg(l_module_name,'[original source_line AttrIdx date-requested direction_flag]');
1863 END IF;
1864
1865 FOR i in 1..l_split_table.count
1866 LOOP
1867 IF l_debug_on THEN
1868 WSH_DEBUG_SV.logmsg(l_module_name,'[' || l_split_table(i).original_source_line_id || ' , '
1869 || l_split_table(i).source_line_id || ' , '
1870 || l_split_table(i).changed_Attributes_index || ' , '
1871 || l_split_table(i).date_requested || ' , '
1872 || l_split_table(i).direction_flag || ' ] ' );
1873 END IF;
1874 END LOOP;
1875
1876
1877 Split_Records_INT (
1878 p_source_code => p_source_code ,
1879 p_changed_attributes => p_changed_attributes,
1880 p_interface_flag => p_interface_flag ,
1881 p_index_tab => l_index_tab ,
1882 p_split_lines => l_split_table,
1883 x_return_status => l_return_status
1884 ) ;
1885
1886 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1887 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1888 IF l_debug_on THEN
1889 WSH_DEBUG_SV.logmsg(l_module_name,' Error in split_records_int ' );
1890 END IF;
1891 END IF;
1892
1893 END IF ; --} if count > 1
1894
1895 x_return_status := l_return_status ;
1896
1897 --Debug message added in bug 9265925
1898 IF l_debug_on THEN
1899 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1900 WSH_DEBUG_SV.pop(l_module_name);
1901 END IF;
1902 --
1903 EXCEPTION
1904
1905 WHEN others THEN
1906 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_records ');
1907 IF l_debug_on THEN
1908 WSH_DEBUG_SV.logmsg(l_module_name,SQLCODE || ' : ' || SQLERRM );
1909 END IF;
1910 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1911
1912 END split_Records ;
1913
1914 PROCEDURE Update_Records(
1915 p_source_code IN VARCHAR2,
1916 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
1917 p_interface_flag IN VARCHAR2,
1918 x_return_status OUT NOCOPY VARCHAR2)
1919 IS
1920 l_counter NUMBER;
1921 l_update_allowed VARCHAR2(1);
1922 l_rs VARCHAR2(1);
1923 l_changed_detail WSH_USA_CATEGORIES_PVT.ChangedDetailRec;
1924 --
1925 l_debug_on BOOLEAN;
1926 --
1927 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_RECORDS';
1928 --
1929 BEGIN
1930
1931 --
1932 --
1933 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1934 --
1935 IF l_debug_on IS NULL
1936 THEN
1937 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1938 END IF;
1939 --
1940 IF l_debug_on THEN
1941 WSH_DEBUG_SV.push(l_module_name);
1942 --
1943 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1944 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1945 END IF;
1946 --
1947 l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1948 <<records_loop>>
1949 FOR l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST LOOP
1950
1951 IF p_changed_attributes(l_counter).action_flag IN ('S', 'U') THEN
1952
1953 IF p_interface_flag = 'Y' THEN
1954 -- always allow update during OM Interface without validation or further changes.
1955 l_update_allowed := 'Y';
1956 ELSE
1957 -- normal process requires validation and sometimes changes.
1958 WSH_USA_CATEGORIES_PVT.Check_Attributes(
1959 p_source_code => p_source_code,
1960 p_attributes_rec => p_changed_attributes(l_counter),
1961 x_changed_detail => l_changed_detail,
1962 x_update_allowed => l_update_allowed,
1963 x_return_status => l_rs);
1964
1965 IF l_debug_on THEN
1966 WSH_DEBUG_SV.log(l_module_name, 'AFTER CALLING WSH_USA_CATEGORIES_PVT.CHECK_ATTRIBUTES: L_RS', L_RS );
1967 WSH_DEBUG_SV.log(l_module_name, 'L_UPDATE_ALLOWED', l_update_allowed);
1968 END IF;
1969
1970 IF NVL(l_update_allowed, 'N') = 'N' THEN
1971 -- bug 5387341: to prevent data corruption, we should return
1972 -- error when update is not allowed, since that means that
1973 -- the delivery detail(s) will not be synchronized with the
1974 -- order line.
1975 IF NVL(l_rs, 'X') <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1976 l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
1977 END IF;
1978 END IF;
1979
1980 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1981 EXIT records_loop;
1982 END IF;
1983 --
1984 END IF; -- p_interface_flag = 'Y'
1985
1986 IF l_update_allowed = 'Y' THEN
1987 WSH_USA_ACTIONS_PVT.Update_Attributes(
1988 p_source_code => p_source_code,
1989 p_attributes_rec => p_changed_attributes(l_counter),
1990 p_changed_detail => l_changed_detail,
1991 p_interface_flag => p_interface_flag, --bugfix 10175902
1992 x_return_status => l_rs);
1993
1994 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1995 EXIT records_loop;
1996 END IF;
1997 --
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_USA_ACTIONS_PVT.UPDATE_ATTRIBUTES ' || L_RS );
2000 END IF;
2001 --
2002 END IF; -- l_update_allowed = 'Y'
2003
2004 END IF; -- p_changed_attributes(l_counter).action_flag in ('S', 'U')
2005
2006 END LOOP; -- l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST
2007
2008 x_return_status := l_rs;
2009 --
2010 IF l_debug_on THEN
2011 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2012 WSH_DEBUG_SV.pop(l_module_name);
2013 END IF;
2014 --
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2018 wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Update_Records',l_module_name);
2019 --
2020 IF l_debug_on THEN
2021 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2022 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2023 END IF;
2024 --
2025 END Update_Records;
2026
2027
2028
2029 Procedure Update_Attributes(
2030 p_source_code IN VARCHAR2,
2031 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
2032 p_changed_detail IN WSH_USA_CATEGORIES_PVT.ChangedDetailRec,
2033 p_interface_flag IN VARCHAR2 , --bugfix 10175902
2034 x_return_status OUT NOCOPY VARCHAR2)
2035 IS
2036
2037 --bug#6407943:Needs to change items org dependent attributes when org changes.
2038 cursor c_is_reservable IS
2039 select inventory_item_id, organization_id, pickable_flag,requested_quantity_uom,
2040 unit_weight,weight_uom_code,unit_volume,volume_uom_code,hazard_class_id,item_description
2041 from wsh_delivery_details
2042 where source_line_id = p_attributes_rec.source_line_id
2043 and source_code = p_source_code
2044 and rownum = 1;
2045
2046 /* H projects: pricing integration csun */ /* J TP Release */
2047 --4410272
2048 cursor c_get_delivery_detail_id IS
2049 select delivery_detail_id, released_status, date_requested, date_scheduled
2050 from wsh_delivery_details
2051 WHERE source_code = p_source_code
2052 AND source_line_id = p_attributes_rec.source_line_id
2053 AND container_flag = 'N'
2054 AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
2055 delivery_detail_id, p_attributes_rec.delivery_detail_id );
2056
2057 cursor c_get_tpdetails IS
2058 select organization_id, carrier_id, ship_method_code, ignore_for_planning
2059 from wsh_delivery_details
2060 WHERE source_code = p_source_code
2061 AND source_line_id = p_attributes_rec.source_line_id
2062 AND container_flag = 'N'
2063 AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
2064 delivery_detail_id, p_attributes_rec.delivery_detail_id )
2065 AND nvl(ignore_for_planning,'N')<>'Y'
2066 AND rownum=1;
2067
2068 b_ignore BOOLEAN;
2069
2070 CURSOR c_get_det_in_del (p_detailid NUMBER) IS
2071 SELECT wnd.name delivery_name
2072 FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
2073 WHERE wda.delivery_id = wnd.delivery_id AND
2074 wda.delivery_id IS NOT NULL AND
2075 wda.delivery_detail_id=p_detailid;
2076
2077 -- Bug 3125768: this cursor is introduced to get the inventory transactable flag
2078 CURSOR c_get_pickable(c_item_id NUMBER, c_org_id NUMBER) IS
2079 SELECT NVL(mtl_transactions_enabled_flag, 'N')
2080 FROM mtl_system_items
2081 WHERE inventory_item_id = c_item_id
2082 AND organization_id = c_org_id;
2083
2084 -- bug#6407943 (begin):Needs to change items org dependent attributes when org changes
2085 CURSOR C_specific_item_info(c_p_inventory_item_id number,
2086 c_p_organization_id number)
2087 IS
2088 SELECT hazard_class_id, primary_uom_code, weight_uom_code,
2089 unit_weight, volume_uom_code, unit_volume,description
2090 FROM mtl_system_items m
2091 WHERE m.inventory_item_id = c_p_inventory_item_id
2092 AND m.organization_id = c_p_organization_id;
2093
2094 l_haz_class_id number;
2095 l_primary_uom_code varchar2(3);
2096 l_weight_uom varchar2(3);
2097 l_unit_weight number;
2098 l_volume_uom varchar2(3);
2099 l_unit_volume number;
2100 l_db_requested_quantity_uom varchar2(3);
2101 l_db_unit_volume number;
2102 l_db_unit_weight number;
2103 l_db_volume_uom varchar2(3);
2104 l_db_weight_uom varchar2(3);
2105 l_db_haz_class_id number;
2106 l_change_req_quantity_uom varchar2(1):='N';
2107 l_change_unit_weight varchar2(1) :='N';
2108 l_change_unit_volume varchar2(1):= 'N';
2109 l_change_weight_uom varchar2(1) :='N';
2110 l_change_volume_uom varchar2(1):= 'N';
2111 l_change_weight varchar2(1):= 'N';
2112 l_change_volume varchar2(1):= 'N';
2113 l_change_haz_class_id varchar2(1):= 'N';
2114 l_db_item_description VARCHAR2(250);
2115 l_item_description VARCHAR2(250);
2116 l_change_item_desc VARCHAR2(1):='N';
2117 -- bug#6407943 (end):Needs to change items org dependent attributes when org changes
2118
2119
2120 l_wh_type VARCHAR2(3);
2121 l_ignore_for_planning VARCHAR2(1);
2122 l_groupbysmc VARCHAR2(1);
2123 l_groupbycarrier VARCHAR2(1);
2124 l_in_ids wsh_util_core.id_tab_type;
2125 l_datetype VARCHAR2(30);
2126 l_smc wsh_delivery_details.ship_method_code%TYPE;
2127 l_carrierid wsh_delivery_details.carrier_id%TYPE;
2128 l_orgid wsh_delivery_details.organization_id%TYPE;
2129 l_earliest_pickup_date DATE;
2130 l_latest_pickup_date DATE;
2131 l_earliest_dropoff_date DATE;
2132 l_latest_dropoff_date DATE;
2133
2134
2135 l_mark_reprice_flag VARCHAR2(1) := 'N';
2136 l_delivery_detail_id NUMBER ;
2137 m NUMBER := 0;
2138 l_details_marked WSH_UTIL_CORE.Id_Tab_Type;
2139 --4410272
2140 l_tp_details WSH_UTIL_CORE.Id_Tab_Type;
2141 l_return_status VARCHAR2(1);
2142 mark_reprice_error EXCEPTION;
2143
2144
2145 l_inventory_item_id NUMBER;
2146 l_organization_id NUMBER;
2147 l_pickable_flag VARCHAR2(1);
2148 l_reservable_flag VARCHAR2(1);
2149 l_ship_from_location_id NUMBER;
2150 l_ship_to_location_id NUMBER;
2151 l_deliver_to_location_id NUMBER;
2152 l_intmed_ship_to_location_id NUMBER;
2153 l_carrier_rec WSH_CARRIERS_GRP.Carrier_Service_InOut_Rec_Type;
2154 l_generic_flag VARCHAR2(1);
2155 l_service_level VARCHAR2(30);
2156 l_mode_of_transport VARCHAR2(30);
2157 l_carrier_id NUMBER;
2158 l_ship_method_code VARCHAR2(30);
2159 tpdates_changed VARCHAR2(1);
2160
2161 --OTM R12 Start Org-Specific
2162 l_gc3_is_installed VARCHAR2(1);
2163 l_shipping_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
2164
2165 -- 5870774
2166 l_oke_full_cancel_flag VARCHAR2(1) := 'N';
2167
2168 Update_Failed Exception;
2169
2170 l_change_sub VARCHAR2(1); --Added for bug 8995849
2171
2172 --
2173 l_debug_on BOOLEAN;
2174 --
2175 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ATTRIBUTES';
2176 l_dbi_rs VARCHAR2(1); -- DBI Project
2177 --
2178 BEGIN
2179 --
2180 --
2181 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2182 --
2183 IF l_debug_on IS NULL
2184 THEN
2185 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2186 END IF;
2187 --
2188 --
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.push(l_module_name);
2191 --
2192 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2193 WSH_DEBUG_SV.log(l_module_name,'p_interface_flag',p_interface_flag);
2194 END IF;
2195 --
2196 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2197
2198 --OTM R12 Start Org-Specific
2199 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2200 IF l_gc3_is_installed IS NULL THEN
2201 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2202 END IF;
2203 IF l_debug_on THEN
2204 WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed',l_gc3_is_installed);
2205 END IF;
2206 --OTM R12
2207
2208 SAVEPOINT before_changes;
2209
2210 OPEN c_is_reservable;
2211 -- bug#6407943 : Added extra fields related to item specific attribute values.
2212 FETCH c_is_reservable INTO l_inventory_item_id, l_organization_id, l_pickable_flag,l_db_requested_quantity_uom,
2213 l_db_unit_weight,l_db_weight_uom,l_db_unit_volume,l_db_volume_uom,
2214 l_db_haz_class_id,l_db_item_description;
2215
2216 CLOSE c_is_reservable;
2217
2218 -- Bug 3125768: Commenting the following call that gets the reservable flag.
2219 -- Now, Get the reservable flag after fetching the pickable flag for the new Organization
2220 /*
2221 l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
2222 x_item_id => l_inventory_item_id,
2223 x_organization_id => l_organization_id,
2224 x_pickable_flag => l_pickable_flag);
2225 */
2226
2227 -- bug#6407943 (Begin):
2228 --When there is a change in org value on sale order line, needs to change
2229 --item attributes which are org dependent .
2230 --BUG#11893915
2231 IF p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM
2232 AND p_attributes_rec.ship_from_org_id <> l_organization_id
2233 AND ( (p_attributes_rec.inventory_item_id = FND_API.G_MISS_NUM) OR
2234 (p_attributes_rec.inventory_item_id = l_inventory_item_id) )
2235 AND p_attributes_rec.ship_from_org_id IS NOT NULL THEN
2236 --{
2237 OPEN c_specific_item_info(l_inventory_item_id, p_attributes_rec.ship_from_org_id);
2238 FETCH C_SPECIFIC_ITEM_INFO INTO l_haz_class_id, l_primary_uom_code, l_weight_uom, l_unit_weight,
2239 l_volume_uom, l_unit_volume,l_item_description;
2240
2241 CLOSE c_specific_item_info;
2242
2243 IF (l_db_requested_quantity_uom <> l_primary_uom_code) THEN
2244 --{
2245 l_change_req_quantity_uom:='Y';
2246 --}
2247 END IF;
2248 IF (nvl(l_db_unit_weight,-99) <> nvl(l_unit_weight,-99)) THEN
2249 l_change_unit_weight:='Y';
2250 END IF;
2251 IF (nvl(l_db_unit_volume,-99)<> nvl(l_unit_volume,-99)) THEN
2252 l_change_unit_volume:='Y';
2253 END IF;
2254 IF (nvl(l_db_weight_uom,-99) <> nvl(l_weight_uom,-99)) THEN
2255 l_change_weight_uom:='Y';
2256 END IF;
2257 IF (nvl(l_db_volume_uom,-99)<> nvl(l_volume_uom,-99)) THEN
2258 l_change_volume_uom:='Y';
2259 END IF;
2260 IF (l_change_unit_weight ='Y' OR l_change_req_quantity_uom = 'Y') THEN
2261 l_change_weight:='Y';
2262 END IF;
2263 IF (l_change_unit_volume ='Y' OR l_change_req_quantity_uom = 'Y') THEN
2264 l_change_volume:='Y';
2265 END IF;
2266 IF (nvl(l_db_haz_class_id,-99) <> nvl(l_haz_class_id,-99)) THEN
2267 l_change_haz_class_id:='Y';
2268 END IF;
2269 IF (nvl(l_db_item_description,-99) <> nvl(l_item_description,-99)) THEN
2270 l_change_item_desc:='Y';
2271 END IF;
2272
2273
2274 --}
2275 END IF;
2276 -- bug#6407943 (end):Needs to change items org dependent attributes when org changes.
2277
2278 IF p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM THEN
2279
2280 WSH_UTIL_CORE.GET_LOCATION_ID('ORG', p_attributes_rec.ship_from_org_id,
2281 l_ship_from_location_id, x_return_status);
2282
2283 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.log(l_module_name,'get_reservable_flag returned error');
2286 END IF;
2287 raise Update_Failed;
2288 END IF;
2289
2290 -- HW OPMCONV -Removed forking
2291
2292 -- Bug 3125768: Getting the inventory transactable flag for the item of the new Organization
2293 OPEN c_get_pickable(l_inventory_item_id, p_attributes_rec.ship_from_org_id);
2294 FETCH c_get_pickable INTO l_pickable_flag;
2295 CLOSE c_get_pickable;
2296
2297
2298 END IF;
2299
2300 -- Bug 3125768
2301 l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
2302 x_item_id => l_inventory_item_id,
2303 x_organization_id => l_organization_id,
2304 x_pickable_flag => l_pickable_flag);
2305
2306 IF p_attributes_rec.ship_to_org_id <> FND_API.G_MISS_NUM THEN
2307
2308 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2309 l_ship_to_location_id, x_return_status);
2310
2311 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2312 IF l_debug_on THEN
2313 WSH_DEBUG_SV.log(l_module_name,'GET_LOCATION_ID failed');
2314 END IF;
2315 raise Update_Failed;
2316 END IF;
2317
2318 END IF;
2319
2320 IF (p_attributes_rec.deliver_to_org_id IS NOT NULL)
2321 AND (p_attributes_rec.deliver_to_org_id <> FND_API.G_MISS_NUM) THEN
2322 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.deliver_to_org_id,
2323 l_deliver_to_location_id, x_return_status);
2324
2325 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2326 IF l_debug_on THEN
2327 WSH_DEBUG_SV.log(l_module_name,'procedure GET_LOCATION_ID failed');
2328 END IF;
2329 raise Update_Failed;
2330 END IF;
2331
2332 END IF;
2333
2334 IF (p_attributes_rec.intmed_ship_to_org_id IS NOT NULL)
2335 AND (p_attributes_rec.intmed_ship_to_org_id <> FND_API.G_MISS_NUM) THEN
2336 --
2337 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.intmed_ship_to_org_id,
2338 l_intmed_ship_to_location_id, x_return_status);
2339
2340 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2341 IF l_debug_on THEN
2342 WSH_DEBUG_SV.log(l_module_name,'program GET_LOCATION_ID failed');
2343 END IF;
2344 raise Update_Failed;
2345 END IF;
2346
2347 END IF;
2348
2349 IF (l_deliver_to_location_id is NULL) AND (l_ship_to_location_id IS NOT NULL) THEN
2350 l_deliver_to_location_id := l_ship_to_location_id;
2351 ELSIF (l_deliver_to_location_id is NULL) AND (l_ship_to_location_id IS NULL)
2352 AND (p_attributes_rec.ship_to_org_id <> FND_API.G_MISS_NUM) THEN
2353 --
2354 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2355 l_deliver_to_location_id, x_return_status);
2356
2357 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2358 IF l_debug_on THEN
2359 WSH_DEBUG_SV.log(l_module_name,'GET_LOCATION_ID failed for ship_to');
2360 END IF;
2361 raise Update_Failed;
2362 END IF;
2363
2364 END IF;
2365 --OTM R12 Org-Specific Start
2366 IF l_gc3_is_installed = 'Y' THEN
2367 IF ( p_attributes_rec.ship_from_org_id IS NOT NULL AND
2368 p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM) THEN
2369 IF l_debug_on THEN
2370 WSH_DEBUG_SV.log(l_module_name,'Before call to WSH_SHIPPING_PARAMS_PVT.Get to parameter values for Org',
2371 p_attributes_rec.ship_from_org_id );
2372 END IF;
2373 WSH_SHIPPING_PARAMS_PVT.Get(
2374 p_organization_id => p_attributes_rec.ship_from_org_id,
2375 p_client_id => p_attributes_rec.client_id, -- LSP PROJECT.
2376 x_param_info => l_shipping_param_info,
2377 x_return_status => l_return_status);
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PARAMS_PVT.Get l_return_status ',l_return_status);
2380 WSH_DEBUG_SV.log(l_module_name,'l_shipping_param_info.otm_enabled ',l_shipping_param_info.otm_enabled);
2381 END IF;
2382
2383 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2384 Raise Update_failed;
2385 END IF;
2386 End If;
2387 END IF;
2388 --OTM R12 End
2389
2390 /* J TP Release */
2391 IF (WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y') OR
2392 (l_gc3_is_installed = 'Y')THEN --OTM R12 Org-Specific. Added the second OR condition.
2393 b_ignore:=FALSE;
2394 --find atleast 1 detail with null or 'N' ignore
2395 OPEN c_get_tpdetails;
2396 FETCH c_get_tpdetails INTO l_orgid, l_carrierid, l_smc, l_ignore_for_planning;
2397 IF c_get_tpdetails%FOUND THEN
2398 b_ignore:=TRUE;
2399 END IF;
2400 CLOSE c_get_tpdetails;
2401
2402 --1. check for updates to org, carrier, smc and based on
2403 -- that set ignore_for_planning
2404
2405 IF (((p_attributes_rec.ship_from_org_id IS NOT NULL AND p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM)
2406 OR (p_attributes_rec.carrier_id IS NOT NULL AND p_attributes_rec.carrier_id <> FND_API.G_MISS_NUM)
2407 OR (p_attributes_rec.shipping_method_code IS NOT NULL AND p_attributes_rec.shipping_method_code <> FND_API.G_MISS_CHAR)
2408 AND b_ignore )
2409 OR (l_shipping_param_info.otm_enabled='Y')) --OTM R12 Org-Specific
2410
2411 THEN
2412
2413 IF (p_attributes_rec.ship_from_org_id IS NOT NULL AND p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM) THEN
2414 l_orgid:=p_attributes_rec.ship_from_org_id;
2415 END IF;
2416 IF (p_attributes_rec.carrier_id IS NOT NULL AND p_attributes_rec.carrier_id <> FND_API.G_MISS_NUM) THEN
2417 l_carrierid:=p_attributes_rec.carrier_id;
2418 END IF;
2419 IF (p_attributes_rec.shipping_method_code IS NOT NULL AND p_attributes_rec.shipping_method_code <> FND_API.G_MISS_CHAR) THEN
2420 l_smc:=p_attributes_rec.shipping_method_code;
2421 END IF;
2422 IF l_debug_on THEN
2423 WSH_DEBUG_SV.log(l_module_name,'l_orgid', l_orgid);
2424 WSH_DEBUG_SV.log(l_module_name,'l_carrierid', l_carrierid);
2425 WSH_DEBUG_SV.log(l_module_name,'l_smc', l_smc);
2426 END IF;
2427
2428 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
2429 (p_organization_id => l_orgid,
2430 p_carrier_id => l_carrierid,
2431 p_ship_method_code => l_smc,
2432 p_msg_display => 'N',
2433 x_return_status => l_return_status
2434 );
2435
2436 IF l_debug_on THEN
2437 WSH_DEBUG_SV.log(l_module_name,' Get_Warehouse_Type l_wh_type,l_return_status',l_wh_type||l_return_status);
2438 END IF;
2439
2440 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2441 IF l_debug_on THEN
2442 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type failed');
2443 END IF;
2444 raise Update_Failed;
2445 END IF;
2446
2447 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS')) THEN --{
2448 l_ignore_for_planning:='Y';
2449 WSH_TP_RELEASE.Check_Shipset_Ignoreflag(p_attributes_rec.delivery_detail_id ,'Y',TRUE,x_return_status);
2450 --OTM R12 Start Org-Specific
2451 ELSIF (l_gc3_is_installed ='Y') THEN
2452 IF (l_shipping_param_info.otm_enabled ='Y') THEN
2453 l_ignore_for_planning:='N';
2454 ELSE
2455 l_ignore_for_planning:='Y';
2456 END IF;
2457 IF l_debug_on THEN
2458 WSH_DEBUG_SV.log(l_module_name,'l_ignore_for_planning ',
2459 l_ignore_for_planning );
2460 END IF;
2461 --OTM R12 End
2462 END IF; --}
2463 END IF;--org_id or carrier_id or smc is changed
2464 END IF; --tp_is_installed
2465
2466 --OTM R12 Start Org-Specific
2467 IF l_debug_on THEN
2468 WSH_DEBUG_SV.log(l_module_name,'l_ignore_for_planning ',
2469 l_ignore_for_planning );
2470 END IF;
2471 --OTM R12 End
2472
2473
2474 IF (p_attributes_rec.shipping_method_code IS NOT NULL
2475 AND p_attributes_rec.shipping_method_code <> FND_API.G_MISS_CHAR) THEN
2476
2477 l_carrier_rec.ship_method_code := p_attributes_rec.shipping_method_code;
2478 IF l_debug_on THEN
2479 WSH_DEBUG_SV.log(l_module_name,'ship_method_code ',p_attributes_rec.shipping_method_code);
2480 END IF;
2481
2482 WSH_CARRIERS_GRP.get_carrier_service_mode(
2483 p_carrier_service_inout_rec => l_carrier_rec,
2484 x_return_status => x_return_status);
2485
2486 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2487 IF l_debug_on THEN
2488 WSH_DEBUG_SV.log(l_module_name,'procedure get_carrier_service_mode failed');
2489 END IF;
2490 raise Update_Failed;
2491 END IF;
2492
2493 IF l_carrier_rec.generic_flag = 'Y' THEN
2494
2495 l_ship_method_code := NULL;
2496 l_carrier_id := NULL;
2497
2498 ELSE
2499
2500 l_ship_method_code := p_attributes_rec.shipping_method_code;
2501 l_carrier_id := l_carrier_rec.carrier_id;
2502
2503 END IF;
2504
2505 l_service_level := l_carrier_rec.service_level;
2506 l_mode_of_transport := l_carrier_rec.mode_of_transport;
2507
2508
2509
2510 END IF;
2511
2512 IF
2513 (p_changed_detail.earliest_pickup_date IS NULL AND p_changed_detail.latest_pickup_date IS NULL AND
2514 p_changed_detail.earliest_dropoff_date IS NULL AND p_changed_detail.latest_dropoff_date IS NULL ) THEN
2515
2516 tpdates_changed := 'N';
2517
2518 ELSE
2519 tpdates_changed := 'Y';
2520 END IF;
2521
2522 -- Added for bug 4410272
2523 -- Call API WSH_TP_RELEASE.calculate_cont_del_tpdates only if dates
2524 -- are changed in WDD.
2525 FOR cur in c_get_delivery_detail_id LOOP
2526 IF ( cur.released_status <> 'C' AND
2527 ( ( cur.date_requested <> p_attributes_rec.date_requested and
2528 p_attributes_rec.date_requested <> FND_API.G_MISS_DATE ) OR
2529 ( cur.date_scheduled <> p_attributes_rec.date_scheduled and
2530 p_attributes_rec.date_scheduled <> FND_API.G_MISS_DATE ) OR
2531 tpdates_changed = 'Y' ) )
2532 THEN
2533 l_tp_details(l_tp_details.COUNT+1) := cur.delivery_detail_id;
2534 END IF;
2535
2536 l_details_marked(l_details_marked.COUNT+1) := cur.delivery_detail_id;
2537 END LOOP;
2538
2539 -- 5870774: If these are OKE lines and SRC Ord. Qty was 0 , then we have to leave line src_req_qty as such
2540 -- because the src_req_qty on the dds that were not Cancelled in the UpdateOrdQty Procedure (WSHUSAQB.pls)
2541 -- would have been Updated in the same Procedure (only true for OKE lines)
2542 l_oke_full_cancel_flag := 'N';
2543 if (p_source_code NOT IN ('OE','WSH','INV') and p_attributes_rec.ordered_quantity = 0) THEN --RTV changes
2544 l_oke_full_cancel_flag := 'Y';
2545 end if; -- OKE
2546
2547 --Added for bug 8995849
2548 If l_organization_id <> nvl(p_attributes_rec.ship_from_org_id,FND_API.G_MISS_NUM) THEN
2549 l_change_sub := 'Y';
2550 ELSE
2551 l_change_sub := 'N';
2552 END IF;
2553
2554 /*2740139 : For non-transactable,non-reservable,but stockable and shippable
2555 items, the release status is 'X'.In this case the subinventory value is
2556 set to the value that is present in wdd and not from the order lines.*/
2557 UPDATE wsh_delivery_details
2558 SET sold_to_contact_id = decode ( p_attributes_rec.sold_to_contact_id, FND_API.G_MISS_NUM ,
2559 sold_to_contact_id , NVL(p_attributes_rec.sold_to_contact_id, sold_to_contact_id) ) ,
2560 ship_to_contact_id = decode ( p_attributes_rec.ship_to_contact_id, FND_API.G_MISS_NUM ,
2561 ship_to_contact_id , p_attributes_rec.ship_to_contact_id ) ,
2562 deliver_to_contact_id = decode ( p_attributes_rec.deliver_to_contact_id, FND_API.G_MISS_NUM ,
2563 deliver_to_contact_id , p_attributes_rec.deliver_to_contact_id ) ,
2564 organization_id = decode (p_attributes_rec.ship_from_org_id,
2565 FND_API.G_MISS_NUM, organization_id,
2566 NULL, organization_id,
2567 p_attributes_rec.ship_from_org_id),
2568 ship_from_location_id = decode (p_attributes_rec.ship_from_org_id,
2569 FND_API.G_MISS_NUM, ship_from_location_id,
2570 NULL, ship_from_location_id,
2571 -- bug 2894922: if organization is not changed, keep ship_from_location_id
2572 organization_id, ship_from_location_id,
2573 l_ship_from_location_id ) ,
2574 ship_to_location_id = decode (p_attributes_rec.ship_to_org_id, FND_API.G_MISS_NUM ,
2575 ship_to_location_id , l_ship_to_location_id ) ,
2576 ship_to_site_use_id = decode ( p_attributes_rec.ship_to_org_id, FND_API.G_MISS_NUM ,
2577 ship_to_site_use_id , p_attributes_rec.ship_to_org_id ) ,
2578 deliver_to_site_use_id = decode ( p_attributes_rec.deliver_to_org_id, FND_API.G_MISS_NUM ,
2579 deliver_to_site_use_id , p_attributes_rec.deliver_to_org_id ) ,
2580 deliver_to_location_id = decode (p_attributes_rec.deliver_to_org_id, FND_API.G_MISS_NUM ,
2581 deliver_to_location_id , l_deliver_to_location_id ) ,
2582 intmed_ship_to_contact_id = decode ( p_attributes_rec.intmed_ship_to_contact_id, FND_API.G_MISS_NUM ,
2583 intmed_ship_to_contact_id , p_attributes_rec.intmed_ship_to_contact_id ) ,
2584 intmed_ship_to_location_id = decode (p_attributes_rec.intmed_ship_to_org_id, FND_API.G_MISS_NUM ,
2585 intmed_ship_to_location_id , l_intmed_ship_to_location_id ) ,
2586 customer_id = decode (p_attributes_rec.sold_to_org_id, FND_API.G_MISS_NUM ,
2587 customer_id , p_attributes_rec.sold_to_org_id),
2588 ship_tolerance_above = decode ( p_attributes_rec.ship_tolerance_above, FND_API.G_MISS_NUM ,
2589 ship_tolerance_above , p_attributes_rec.ship_tolerance_above ) ,
2590 ship_tolerance_below = decode ( p_attributes_rec.ship_tolerance_below, FND_API.G_MISS_NUM ,
2591 ship_tolerance_below , p_attributes_rec.ship_tolerance_below ) ,
2592 customer_requested_lot_flag = decode ( p_attributes_rec.customer_requested_lot_flag, FND_API.G_MISS_CHAR ,
2593 customer_requested_lot_flag , p_attributes_rec.customer_requested_lot_flag ),
2594 date_requested = decode ( p_attributes_rec.date_requested, FND_API.G_MISS_DATE ,
2595 date_requested , p_attributes_rec.date_requested ) ,
2596 date_scheduled = decode ( p_attributes_rec.date_scheduled, FND_API.G_MISS_DATE ,
2597 date_scheduled , p_attributes_rec.date_scheduled ) ,
2598 dep_plan_required_flag = decode ( p_attributes_rec.dep_plan_required_flag, FND_API.G_MISS_CHAR ,
2599 dep_plan_required_flag , p_attributes_rec.dep_plan_required_flag ) ,
2600 customer_prod_seq = decode ( p_attributes_rec.customer_prod_seq, FND_API.G_MISS_CHAR ,
2601 customer_prod_seq , p_attributes_rec.customer_prod_seq ) ,
2602 customer_dock_code = decode ( p_attributes_rec.customer_dock_code, FND_API.G_MISS_CHAR ,
2603 customer_dock_code , p_attributes_rec.customer_dock_code ) ,
2604 cust_model_serial_number = decode ( p_attributes_rec.cust_model_serial_number, FND_API.G_MISS_CHAR ,
2605 cust_model_serial_number , p_attributes_rec.cust_model_serial_number ) ,
2606 customer_job = decode ( p_attributes_rec.customer_job, FND_API.G_MISS_CHAR ,
2607 customer_job , p_attributes_rec.customer_job ) ,
2608 customer_production_line = decode ( p_attributes_rec.customer_production_line, FND_API.G_MISS_CHAR ,
2609 customer_production_line , p_attributes_rec.customer_production_line ) ,
2610 cust_po_number = decode ( p_attributes_rec.cust_po_number, FND_API.G_MISS_CHAR ,
2611 cust_po_number , p_attributes_rec.cust_po_number ) ,
2612 packing_instructions = decode(p_interface_flag, 'N',decode ( p_attributes_rec.packing_instructions, FND_API.G_MISS_CHAR ,
2613 packing_instructions , p_attributes_rec.packing_instructions) ,packing_instructions), --bugfix 10175902
2614 shipment_priority_code = decode ( p_attributes_rec.shipment_priority_code, FND_API.G_MISS_CHAR ,
2615 shipment_priority_code , p_attributes_rec.shipment_priority_code ) ,
2616 ship_set_id = decode ( p_attributes_rec.ship_set_id, FND_API.G_MISS_NUM ,
2617 ship_set_id , p_attributes_rec.ship_set_id ) ,
2618 ato_line_id = decode ( p_attributes_rec.ato_line_id, FND_API.G_MISS_NUM ,
2619 ato_line_id , p_attributes_rec.ato_line_id ) ,
2620 arrival_set_id = decode ( p_attributes_rec.arrival_set_id, FND_API.G_MISS_NUM ,
2621 arrival_set_id , p_attributes_rec.arrival_set_id ) ,
2622 ship_model_complete_flag = decode ( p_attributes_rec.ship_model_complete_flag, FND_API.G_MISS_CHAR ,
2623 ship_model_complete_flag , p_attributes_rec.ship_model_complete_flag ) ,
2624 -- Bug 2830372. We update the released status to 'N' only if the released status is in ('R', 'B', 'Y').
2625 -- ATO sets the released status to 'N' only when the reservations are removed for that order line.
2626 -- OM does not pass a released status of 'N', always passes 'R'.
2627 -- Bug 3125768: Checking for l_pickable_flag to update the released_status
2628 released_status = decode(released_status,
2629 'C', released_status,
2630 'D', released_status,
2631
2632 'Y', decode(p_attributes_rec.released_status,
2633 'N', p_attributes_rec.released_status,
2634 released_status),
2635 'X', decode(p_source_code , 'OKE', released_status,
2636 decode(l_pickable_flag, 'N', released_status, 'R') ), -- 5870774
2637 'S', released_status,
2638 'B', decode(p_attributes_rec.released_status,
2639 'N', p_attributes_rec.released_status,
2640 decode(l_pickable_flag, 'Y', released_status, 'X')),
2641 /* bug 2421965: backordered should stay backordered except for ATO reservations: Bug: 2587777 */
2642 decode ( l_pickable_flag, 'N' , 'X',
2643 decode ( p_attributes_rec.released_status, FND_API.G_MISS_CHAR ,
2644 released_status , p_attributes_rec.released_status ))) ,
2645
2646 shipping_instructions = decode(p_interface_flag, 'N',decode ( p_attributes_rec.shipping_instructions, FND_API.G_MISS_CHAR,
2647 shipping_instructions , p_attributes_rec.shipping_instructions ),shipping_instructions) , --bugfix 10175902
2648 shipped_quantity = decode ( p_attributes_rec.shipped_quantity, FND_API.G_MISS_NUM ,
2649 shipped_quantity , p_attributes_rec.shipped_quantity ) ,
2650 cycle_count_quantity = decode ( p_attributes_rec.cycle_count_quantity,
2651 FND_API.G_MISS_NUM , decode(p_attributes_rec.shipped_quantity,
2652 FND_API.G_MISS_NUM, cycle_count_quantity,
2653 GREATEST(requested_quantity - p_attributes_rec.shipped_quantity, 0)),
2654 p_attributes_rec.cycle_count_quantity),
2655 -- OPM
2656 shipped_quantity2 = decode ( p_attributes_rec.shipped_quantity2, FND_API.G_MISS_NUM ,
2657 shipped_quantity2 , p_attributes_rec.shipped_quantity2 ) ,
2658 cycle_count_quantity2 = decode ( p_attributes_rec.cycle_count_quantity2,
2659 FND_API.G_MISS_NUM , decode(p_attributes_rec.shipped_quantity2,
2660 FND_API.G_MISS_NUM , cycle_count_quantity2 ,
2661 GREATEST(requested_quantity2 - p_attributes_rec.shipped_quantity2, 0)),
2662 p_attributes_rec.cycle_count_quantity2 ),
2663 currency_code = decode ( p_attributes_rec.currency_code, FND_API.G_MISS_CHAR ,
2664 currency_code , p_attributes_rec.currency_code ) ,
2665 tracking_number = decode(p_attributes_rec.tracking_number, FND_API.G_MISS_CHAR ,
2666 tracking_number , p_attributes_rec.tracking_number ) ,
2667 locator_id = decode(p_attributes_rec.locator_id,
2668 FND_API.G_MISS_NUM, locator_id,
2669 decode(released_status,
2670 'C', locator_id,
2671 p_attributes_rec.locator_id)),
2672 serial_number = decode(p_attributes_rec.serial_number,
2673 FND_API.G_MISS_CHAR, serial_number,
2674 decode(released_status,
2675 'C',serial_number,
2676 p_attributes_rec.serial_number)),
2677 lot_number = decode(p_attributes_rec.lot_number,
2678 FND_API.G_MISS_CHAR, lot_number,
2679 decode(released_status,
2680 'C', lot_number,
2681 p_attributes_rec.lot_number)),
2682 -- OPM
2683 -- HW OPMCONV - Removed sublot code
2684
2685 preferred_grade = decode(p_attributes_rec.preferred_grade,
2686 FND_API.G_MISS_CHAR, preferred_grade,
2687 decode(released_status,
2688 'C', preferred_grade,
2689 'Y', preferred_grade,
2690 p_attributes_rec.preferred_grade)),
2691 revision = decode(p_attributes_rec.revision,
2692 FND_API.G_MISS_CHAR, revision,
2693 decode(released_status,
2694 'C', revision,
2695 p_attributes_rec.revision)),
2696 -- Bug 3125768: changed pickable_flag to l_pickable_flag
2697 subinventory = decode(p_attributes_rec.subinventory,
2698 FND_API.G_MISS_CHAR, subinventory,
2699 decode(released_status,
2700 'Y', decode(l_reservable_flag,
2701 'N', decode(original_subinventory,
2702 p_attributes_rec.subinventory, subinventory,
2703 p_attributes_rec.subinventory),
2704 subinventory),
2705 'S', decode(l_reservable_flag,
2706 'N', decode(l_pickable_flag,
2707 'N', decode(original_subinventory,
2708 p_attributes_rec.subinventory, subinventory,
2709 p_attributes_rec.subinventory),
2710 subinventory),
2711 subinventory),
2712 'C', subinventory,
2713 -- Bug 7665338:For Non-Inventory items,when Subinventory is changed in Order lines, it should be reflected on delivery details.
2714 'X', decode(subinventory,
2715 NULL,decode(p_attributes_rec.subinventory,FND_API.G_MISS_CHAR,subinventory,p_attributes_rec.subinventory),
2716 subinventory),
2717 --bug 8995849:Modified decode statement such that for non-reservable items,when Warehouse is modified on
2718 -- Order lines,Subinventory should be updated on WDD
2719 decode(l_reservable_flag,
2720 'N',decode(l_change_sub,'Y',p_attributes_rec.subinventory,
2721 decode(original_subinventory,
2722 p_attributes_rec.subinventory, subinventory,
2723 p_attributes_rec.subinventory)),
2724 p_attributes_rec.subinventory))),
2725
2726 original_subinventory = decode(p_attributes_rec.subinventory,
2727 FND_API.G_MISS_CHAR, original_subinventory,
2728 decode (released_status,
2729 'C', original_subinventory,
2730 p_attributes_rec.subinventory)),
2731
2732 source_line_number = decode ( p_attributes_rec.line_number, FND_API.G_MISS_CHAR ,
2733 source_line_number , p_attributes_rec.line_number ) ,
2734 master_container_item_id = decode ( p_attributes_rec.master_container_item_id, FND_API.G_MISS_NUM ,
2735 master_container_item_id , p_attributes_rec.master_container_item_id ) ,
2736 detail_container_item_id = decode ( p_attributes_rec.detail_container_item_id, FND_API.G_MISS_NUM ,
2737 detail_container_item_id , p_attributes_rec.detail_container_item_id ) ,
2738 ship_method_code = decode ( l_ship_method_code, FND_API.G_MISS_CHAR ,
2739 ship_method_code , l_ship_method_code ) ,
2740 mode_of_transport = decode ( l_mode_of_transport, FND_API.G_MISS_CHAR ,
2741 mode_of_transport , l_mode_of_transport ) ,
2742 service_level = decode ( l_service_level, FND_API.G_MISS_CHAR ,
2743 service_level , l_service_level ) ,
2744 carrier_id = decode ( l_carrier_id, FND_API.G_MISS_NUM ,
2745 carrier_id , l_carrier_id ) ,
2746 freight_terms_code = decode ( p_attributes_rec.freight_terms_code, FND_API.G_MISS_CHAR ,
2747 freight_terms_code , p_attributes_rec.freight_terms_code ) ,
2748 fob_code = decode ( p_attributes_rec.fob_code, FND_API.G_MISS_CHAR ,
2749 fob_code , p_attributes_rec.fob_code ) ,
2750 customer_item_id = decode ( p_attributes_rec.customer_item_id, FND_API.G_MISS_NUM ,
2751 customer_item_id , p_attributes_rec.customer_item_id ) ,
2752 top_model_line_id = decode ( p_attributes_rec.top_model_line_id, FND_API.G_MISS_NUM ,
2753 top_model_line_id , p_attributes_rec.top_model_line_id ) ,
2754 hold_code = decode ( p_attributes_rec.hold_code, FND_API.G_MISS_CHAR ,
2755 hold_code , p_attributes_rec.hold_code ) ,
2756 inspection_flag = decode ( p_attributes_rec.inspection_flag, FND_API.G_MISS_CHAR ,
2757 inspection_flag , p_attributes_rec.inspection_flag ) ,
2758 src_requested_quantity = decode ( l_oke_full_cancel_flag, 'Y', -- 5870774, Bypass for Non-Canceled dds
2759 src_requested_quantity,
2760 decode ( p_attributes_rec.ordered_quantity, FND_API.G_MISS_NUM ,
2761 src_requested_quantity , p_attributes_rec.ordered_quantity )) ,
2762 src_requested_quantity_uom = decode ( p_attributes_rec.order_quantity_uom, FND_API.G_MISS_CHAR ,
2763 src_requested_quantity_uom , p_attributes_rec.order_quantity_uom ) ,
2764 src_requested_quantity2 = decode ( p_attributes_rec.ordered_quantity2, FND_API.G_MISS_NUM ,
2765 src_requested_quantity2 , p_attributes_rec.ordered_quantity2 ) ,
2766 src_requested_quantity_uom2 = decode ( p_attributes_rec.ordered_quantity_uom2, FND_API.G_MISS_CHAR ,
2767 src_requested_quantity_uom2 , p_attributes_rec.ordered_quantity_uom2 ) ,
2768 attribute_category = decode ( p_attributes_rec.attribute_category, FND_API.G_MISS_CHAR ,
2769 attribute_category , p_attributes_rec.attribute_category ) ,
2770 attribute1 = decode ( p_attributes_rec.attribute1, FND_API.G_MISS_CHAR ,
2771 attribute1 , p_attributes_rec.attribute1 ) ,
2772 attribute2 = decode ( p_attributes_rec.attribute2, FND_API.G_MISS_CHAR ,
2773 attribute2 , p_attributes_rec.attribute2 ) ,
2774 attribute3 = decode ( p_attributes_rec.attribute3, FND_API.G_MISS_CHAR ,
2775 attribute3 , p_attributes_rec.attribute3 ) ,
2776 attribute4 = decode ( p_attributes_rec.attribute4, FND_API.G_MISS_CHAR ,
2777 attribute4 , p_attributes_rec.attribute4 ) ,
2778 attribute5 = decode ( p_attributes_rec.attribute5, FND_API.G_MISS_CHAR ,
2779 attribute5 , p_attributes_rec.attribute5 ) ,
2780 attribute6 = decode ( p_attributes_rec.attribute6, FND_API.G_MISS_CHAR ,
2781 attribute6 , p_attributes_rec.attribute6 ) ,
2782 attribute7 = decode ( p_attributes_rec.attribute7, FND_API.G_MISS_CHAR ,
2783 attribute7 , p_attributes_rec.attribute7 ) ,
2784 attribute8 = decode ( p_attributes_rec.attribute8, FND_API.G_MISS_CHAR ,
2785 attribute8 , p_attributes_rec.attribute8 ) ,
2786 attribute9 = decode ( p_attributes_rec.attribute9, FND_API.G_MISS_CHAR ,
2787 attribute9 , p_attributes_rec.attribute9 ) ,
2788 attribute10 = decode ( p_attributes_rec.attribute10, FND_API.G_MISS_CHAR ,
2789 attribute10 , p_attributes_rec.attribute10 ) ,
2790 attribute11 = decode ( p_attributes_rec.attribute11, FND_API.G_MISS_CHAR ,
2791 attribute11 , p_attributes_rec.attribute11 ) ,
2792 attribute12 = decode ( p_attributes_rec.attribute12, FND_API.G_MISS_CHAR ,
2793 attribute12 , p_attributes_rec.attribute12 ) ,
2794 attribute13 = decode ( p_attributes_rec.attribute13, FND_API.G_MISS_CHAR ,
2795 attribute13 , p_attributes_rec.attribute13 ) ,
2796 attribute14 = decode ( p_attributes_rec.attribute14, FND_API.G_MISS_CHAR ,
2797 attribute14 , p_attributes_rec.attribute14 ) ,
2798 attribute15 = decode ( p_attributes_rec.attribute15, FND_API.G_MISS_CHAR ,
2799 attribute15 , p_attributes_rec.attribute15 ),
2800 cancelled_quantity = decode ( p_attributes_rec.cancelled_quantity, FND_API.G_MISS_NUM ,
2801 cancelled_quantity , p_attributes_rec.cancelled_quantity ),
2802 cancelled_quantity2 = decode ( p_attributes_rec.cancelled_quantity2, FND_API.G_MISS_NUM ,
2803 cancelled_quantity2 , p_attributes_rec.cancelled_quantity2 ) ,
2804 classification = decode ( p_attributes_rec.classification, FND_API.G_MISS_CHAR ,
2805 classification , p_attributes_rec.classification ) ,
2806 commodity_code_cat_id = decode ( p_attributes_rec.commodity_code_cat_id, FND_API.G_MISS_NUM ,
2807 commodity_code_cat_id , p_attributes_rec.commodity_code_cat_id ) ,
2808 container_flag = decode ( p_attributes_rec.container_flag, FND_API.G_MISS_CHAR ,
2809 container_flag , p_attributes_rec.container_flag ) ,
2810 container_name = decode ( p_attributes_rec.container_name, FND_API.G_MISS_CHAR ,
2811 container_name , p_attributes_rec.container_name ) ,
2812 container_type_code = decode ( p_attributes_rec.container_type_code, FND_API.G_MISS_CHAR ,
2813 container_type_code , p_attributes_rec.container_type_code ) ,
2814 country_of_origin = decode ( p_attributes_rec.country_of_origin, FND_API.G_MISS_CHAR ,
2815 country_of_origin , p_attributes_rec.country_of_origin ) ,
2816 delivered_quantity = decode ( p_attributes_rec.delivered_quantity, FND_API.G_MISS_NUM ,
2817 delivered_quantity , p_attributes_rec.delivered_quantity ) ,
2818 delivered_quantity2 = decode ( p_attributes_rec.delivered_quantity2, FND_API.G_MISS_NUM ,
2819 delivered_quantity2 , p_attributes_rec.delivered_quantity2 ) ,
2820 fill_percent = decode ( p_attributes_rec.fill_percent, FND_API.G_MISS_NUM ,
2821 fill_percent , p_attributes_rec.fill_percent ) ,
2822 freight_class_cat_id = decode ( p_attributes_rec.freight_class_cat_id, FND_API.G_MISS_NUM ,
2823 freight_class_cat_id , p_attributes_rec.freight_class_cat_id ) ,
2824 -- Bug 3125768: Checking for l_pickable_flag to update the inv_interfaced_flag
2825 inv_interfaced_flag = decode (inv_interfaced_flag, 'Y',
2826 decode ( p_attributes_rec.inv_interfaced_flag, FND_API.G_MISS_CHAR ,
2827 inv_interfaced_flag , p_attributes_rec.inv_interfaced_flag ) ,
2828 decode (l_pickable_flag, 'N', 'X', 'N')
2829 ),
2830 inventory_item_id = decode ( p_attributes_rec.inventory_item_id, FND_API.G_MISS_NUM ,
2831 inventory_item_id , p_attributes_rec.inventory_item_id ),
2832 --bug#6407943 (begin) :Needs to change items org dependent attributes when org changes
2833 item_description = decode(l_change_item_desc,'Y',l_item_description,
2834 decode (p_attributes_rec.item_description, FND_API.G_MISS_CHAR ,
2835 item_description , p_attributes_rec.item_description )),
2836 requested_quantity_uom = decode (l_change_req_quantity_uom,'Y',l_primary_uom_code,
2837 requested_quantity_uom) ,
2838 unit_weight = decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
2839 unit_volume = decode (l_change_unit_volume,'Y',l_unit_volume,unit_volume),
2840 net_weight = decode(l_change_weight,'Y',requested_quantity * decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
2841 decode ( p_attributes_rec.net_weight, FND_API.G_MISS_NUM ,
2842 net_weight , p_attributes_rec.net_weight )) ,
2843 gross_weight = decode(l_change_weight,'Y',requested_quantity * decode(l_change_unit_weight,'Y',l_unit_weight,unit_weight),
2844 decode ( p_attributes_rec.gross_weight, FND_API.G_MISS_NUM ,
2845 gross_weight , p_attributes_rec.gross_weight )) ,
2846 weight_uom_code = decode(l_change_weight_uom,'Y',l_weight_uom,decode ( p_attributes_rec.weight_uom_code, FND_API.G_MISS_CHAR ,
2847 weight_uom_code , p_attributes_rec.weight_uom_code )) ,
2848 volume = decode(l_change_volume,'Y', requested_quantity * decode (l_change_unit_volume,'Y',l_unit_volume,unit_volume),
2849 decode ( p_attributes_rec.volume, FND_API.G_MISS_NUM ,
2850 volume , p_attributes_rec.volume )) ,
2851 volume_uom_code = decode(l_change_volume_uom,'Y',l_volume_uom,decode ( p_attributes_rec.volume_uom_code, FND_API.G_MISS_CHAR ,
2852 volume_uom_code , p_attributes_rec.volume_uom_code )) ,
2853 hazard_class_id = decode(l_change_haz_class_id,'Y',l_haz_class_id,
2854 decode( p_attributes_rec.hazard_class_id, FND_API.G_MISS_NUM ,
2855 hazard_class_id , p_attributes_rec.hazard_class_id)),
2856 --bug#6407943 (end):Needs to change items org dependent attributes when org changes.
2857 load_seq_number = decode ( p_attributes_rec.load_seq_number, FND_API.G_MISS_NUM ,
2858 load_seq_number , p_attributes_rec.load_seq_number ) ,
2859 lpn_id = decode ( p_attributes_rec.lpn_id, FND_API.G_MISS_NUM ,
2860 lpn_id , p_attributes_rec.lpn_id ) ,
2861 maximum_load_weight = decode ( p_attributes_rec.maximum_load_weight, FND_API.G_MISS_NUM ,
2862 maximum_load_weight , p_attributes_rec.maximum_load_weight ) ,
2863 maximum_volume = decode ( p_attributes_rec.maximum_volume, FND_API.G_MISS_NUM ,
2864 maximum_volume , p_attributes_rec.maximum_volume ) ,
2865 minimum_fill_percent = decode ( p_attributes_rec.minimum_fill_percent, FND_API.G_MISS_NUM ,
2866 minimum_fill_percent , p_attributes_rec.minimum_fill_percent ) ,
2867 move_order_line_id = decode ( p_attributes_rec.move_order_line_id, FND_API.G_MISS_NUM ,
2868 move_order_line_id, p_attributes_rec.move_order_line_id ) ,
2869 movement_id = decode ( p_attributes_rec.movement_id, FND_API.G_MISS_NUM ,
2870 movement_id , p_attributes_rec.movement_id ) ,
2871 mvt_stat_status = decode ( p_attributes_rec.mvt_stat_status, FND_API.G_MISS_CHAR ,
2872 mvt_stat_status , p_attributes_rec.mvt_stat_status ) ,
2873 oe_interfaced_flag = decode ( p_attributes_rec.oe_interfaced_flag, FND_API.G_MISS_CHAR ,
2874 oe_interfaced_flag , p_attributes_rec.oe_interfaced_flag ) ,
2875 org_id = decode ( p_attributes_rec.org_id, FND_API.G_MISS_NUM ,
2876 org_id , p_attributes_rec.org_id ) ,
2877 -- Bug 3125768: changed pickable_flag to l_pickable_flag
2878 pickable_flag = decode ( p_attributes_rec.pickable_flag, FND_API.G_MISS_CHAR ,
2879 l_pickable_flag , p_attributes_rec.pickable_flag ) ,
2880 picked_quantity = decode ( p_attributes_rec.picked_quantity, FND_API.G_MISS_NUM ,
2881 picked_quantity , p_attributes_rec.picked_quantity ),
2882 picked_quantity2 = decode ( p_attributes_rec.picked_quantity2, FND_API.G_MISS_NUM ,
2883 picked_quantity2 , p_attributes_rec.picked_quantity2 ),
2884 project_id = decode ( p_attributes_rec.project_id, FND_API.G_MISS_NUM ,
2885 project_id , p_attributes_rec.project_id ) ,
2886 quality_control_quantity = decode ( p_attributes_rec.quality_control_quantity, FND_API.G_MISS_NUM ,
2887 quality_control_quantity , p_attributes_rec.quality_control_quantity ) ,
2888 quality_control_quantity2 = decode ( p_attributes_rec.quality_control_quantity2, FND_API.G_MISS_NUM ,
2889 quality_control_quantity2 , p_attributes_rec.quality_control_quantity2 ) ,
2890 received_quantity = decode ( p_attributes_rec.received_quantity, FND_API.G_MISS_NUM ,
2891 received_quantity , p_attributes_rec.received_quantity ) ,
2892 received_quantity2 = decode ( p_attributes_rec.received_quantity2, FND_API.G_MISS_NUM ,
2893 received_quantity2 , p_attributes_rec.received_quantity2 ) ,
2894 request_id = decode ( p_attributes_rec.request_id, FND_API.G_MISS_NUM ,
2895 request_id , p_attributes_rec.request_id ) ,
2896 seal_code = decode ( p_attributes_rec.seal_code, FND_API.G_MISS_CHAR ,
2897 seal_code , p_attributes_rec.seal_code ) ,
2898 source_code = decode ( p_attributes_rec.source_code, FND_API.G_MISS_CHAR ,
2899 source_code , p_attributes_rec.source_code ),
2900 source_header_id = decode ( p_attributes_rec.source_header_id, FND_API.G_MISS_NUM ,
2901 source_header_id , p_attributes_rec.source_header_id ) ,
2902 source_header_number = decode ( p_attributes_rec.source_header_number, FND_API.G_MISS_CHAR ,
2903 source_header_number , p_attributes_rec.source_header_number ) ,
2904 source_header_type_id = decode ( p_attributes_rec.source_header_type_id, FND_API.G_MISS_NUM ,
2905 source_header_type_id , p_attributes_rec.source_header_type_id ) ,
2906 source_header_type_name = decode ( p_attributes_rec.source_header_type_name, FND_API.G_MISS_CHAR ,
2907 source_header_type_name , p_attributes_rec.source_header_type_name ) ,
2908 source_line_id = decode ( p_attributes_rec.source_line_id, FND_API.G_MISS_NUM ,
2909 source_line_id , p_attributes_rec.source_line_id ) ,
2910 source_line_set_id = decode ( p_attributes_rec.source_line_set_id, FND_API.G_MISS_NUM ,
2911 source_line_set_id , p_attributes_rec.source_line_set_id ) ,
2912 split_from_delivery_detail_id = decode (p_attributes_rec.split_from_delivery_detail_id, FND_API.G_MISS_NUM,
2913 split_from_delivery_detail_id , p_attributes_rec.split_from_delivery_detail_id ) ,
2914 task_id = decode ( p_attributes_rec.task_id, FND_API.G_MISS_NUM ,
2915 task_id , p_attributes_rec.task_id ) ,
2916 to_serial_number = decode ( p_attributes_rec.to_serial_number, FND_API.G_MISS_CHAR ,
2917 to_serial_number , p_attributes_rec.to_serial_number ) ,
2918 tp_attribute1 = decode ( p_attributes_rec.tp_attribute1, FND_API.G_MISS_CHAR ,
2919 tp_attribute1 , p_attributes_rec.tp_attribute1 ),
2920 tp_attribute10 = decode ( p_attributes_rec.tp_attribute10, FND_API.G_MISS_CHAR ,
2921 tp_attribute10 , p_attributes_rec.tp_attribute10 ) ,
2922 tp_attribute11 = decode ( p_attributes_rec.tp_attribute11, FND_API.G_MISS_CHAR ,
2923 tp_attribute11 , p_attributes_rec.tp_attribute11 ) ,
2924 tp_attribute12 = decode ( p_attributes_rec.tp_attribute12, FND_API.G_MISS_CHAR ,
2925 tp_attribute12 , p_attributes_rec.tp_attribute12 ) ,
2926 tp_attribute13 = decode ( p_attributes_rec.tp_attribute13, FND_API.G_MISS_CHAR ,
2927 tp_attribute13 , p_attributes_rec.tp_attribute13 ) ,
2928 tp_attribute14 = decode ( p_attributes_rec.tp_attribute14, FND_API.G_MISS_CHAR ,
2929 tp_attribute14 , p_attributes_rec.tp_attribute14 ) ,
2930 tp_attribute15 = decode ( p_attributes_rec.tp_attribute15, FND_API.G_MISS_CHAR ,
2931 tp_attribute15 , p_attributes_rec.tp_attribute15 ) ,
2932 tp_attribute2 = decode ( p_attributes_rec.tp_attribute2, FND_API.G_MISS_CHAR ,
2933 tp_attribute2 , p_attributes_rec.tp_attribute2 ) ,
2934 tp_attribute3 = decode ( p_attributes_rec.tp_attribute3, FND_API.G_MISS_CHAR ,
2935 tp_attribute3 , p_attributes_rec.tp_attribute3 ) ,
2936 tp_attribute4 = decode ( p_attributes_rec.tp_attribute4, FND_API.G_MISS_CHAR ,
2937 tp_attribute4 , p_attributes_rec.tp_attribute4 ) ,
2938 tp_attribute5 = decode ( p_attributes_rec.tp_attribute5, FND_API.G_MISS_CHAR ,
2939 tp_attribute5 , p_attributes_rec.tp_attribute5 ) ,
2940 tp_attribute6 = decode ( p_attributes_rec.tp_attribute6, FND_API.G_MISS_CHAR ,
2941 tp_attribute6 , p_attributes_rec.tp_attribute6 ) ,
2942 tp_attribute7 = decode ( p_attributes_rec.tp_attribute7, FND_API.G_MISS_CHAR ,
2943 tp_attribute7 , p_attributes_rec.tp_attribute7 ) ,
2944 tp_attribute8 = decode ( p_attributes_rec.tp_attribute8, FND_API.G_MISS_CHAR ,
2945 tp_attribute8 , p_attributes_rec.tp_attribute8 ) ,
2946 tp_attribute9 = decode ( p_attributes_rec.tp_attribute9, FND_API.G_MISS_CHAR ,
2947 tp_attribute9 , p_attributes_rec.tp_attribute9 ),
2948 tp_attribute_category = decode ( p_attributes_rec.tp_attribute_category, FND_API.G_MISS_CHAR ,
2949 tp_attribute_category , p_attributes_rec.tp_attribute_category ) ,
2950 transaction_temp_id = decode ( p_attributes_rec.transaction_temp_id, FND_API.G_MISS_NUM ,
2951 transaction_temp_id , p_attributes_rec.transaction_temp_id ) ,
2952 unit_number = decode ( p_attributes_rec.unit_number, FND_API.G_MISS_CHAR ,
2953 unit_number , p_attributes_rec.unit_number ) ,
2954 unit_price = decode ( p_attributes_rec.unit_price, FND_API.G_MISS_NUM ,
2955 unit_price , p_attributes_rec.unit_price ),
2956 /* J TP Release */
2957 earliest_pickup_date = decode(tpdates_changed, 'N' ,
2958 to_date(to_char(earliest_pickup_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
2959 , p_changed_detail.earliest_pickup_date ),
2960 latest_pickup_date = decode(tpdates_changed, 'N' ,
2961 to_date(to_char(latest_pickup_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
2962 , p_changed_detail.latest_pickup_date ),
2963 earliest_dropoff_date = decode(tpdates_changed, 'N' ,
2964 to_date(to_char(earliest_dropoff_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
2965 , p_changed_detail.earliest_dropoff_date ),
2966 latest_dropoff_date = decode(tpdates_changed, 'N' ,
2967 to_date(to_char(latest_dropoff_date,'mm-dd-yy HH24:MI:SS'),'mm-dd-yy HH24:MI:SS')
2968 , p_changed_detail.latest_dropoff_date),
2969 --OTM R12 Org-Specific ( Changes made for update to ignore_for_planning column ).
2970 --Update the field only if organization_id is changed.
2971 ignore_for_planning = decode(organization_id, l_orgid, ignore_for_planning,
2972 nvl(l_ignore_for_planning,nvl(ignore_for_planning,'N'))),
2973 -- Bug 3244272 : Added laste_update_date and last_updated_by in the
2974 -- update statement.
2975 last_update_date = SYSDATE,
2976 last_updated_by = FND_GLOBAL.USER_ID,
2977 last_update_login = FND_GLOBAL.LOGIN_ID
2978 WHERE source_code = p_source_code
2979 AND source_line_id = p_attributes_rec.source_line_id
2980 AND container_flag = 'N'
2981 AND delivery_detail_id = decode( p_attributes_rec.delivery_detail_id, FND_API.G_MISS_NUM ,
2982 delivery_detail_id, p_attributes_rec.delivery_detail_id );
2983
2984 /* H projects: pricing integration csun */
2985
2986 IF (p_attributes_rec.gross_weight IS NOT NULL)
2987 AND (p_attributes_rec.gross_weight <> FND_API.G_MISS_NUM) THEN
2988 l_mark_reprice_flag := 'Y';
2989 ELSIF (p_attributes_rec.net_weight IS NOT NULL)
2990 AND (p_attributes_rec.net_weight <> FND_API.G_MISS_NUM) THEN
2991 l_mark_reprice_flag := 'Y';
2992 ELSIF (p_attributes_rec.volume IS NOT NULL)
2993 AND (p_attributes_rec.volume <> FND_API.G_MISS_NUM) THEN
2994 l_mark_reprice_flag := 'Y';
2995 ELSIF (p_attributes_rec.volume_uom_code IS NOT NULL)
2996 AND (p_attributes_rec.volume_uom_code <> FND_API.G_MISS_CHAR) THEN
2997 l_mark_reprice_flag := 'Y';
2998 ELSIF (p_attributes_rec.weight_uom_code IS NOT NULL)
2999 AND (p_attributes_rec.weight_uom_code <> FND_API.G_MISS_CHAR) THEN
3000 l_mark_reprice_flag := 'Y';
3001 ELSIF (p_attributes_rec.subinventory IS NOT NULL)
3002 AND (p_attributes_rec.subinventory <> FND_API.G_MISS_CHAR) THEN
3003 l_mark_reprice_flag := 'Y';
3004 ELSIF (p_attributes_rec.ship_from_org_id IS NOT NULL)
3005 AND (p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM) THEN
3006 l_mark_reprice_flag := 'Y';
3007 ELSIF (p_attributes_rec.ship_to_org_id IS NOT NULL)
3008 AND (p_attributes_rec.ship_to_org_id <> FND_API.G_MISS_NUM) THEN
3009 l_mark_reprice_flag := 'Y';
3010 ELSIF (p_attributes_rec.deliver_to_org_id IS NOT NULL)
3011 AND (p_attributes_rec.deliver_to_org_id <> FND_API.G_MISS_NUM) THEN
3012 l_mark_reprice_flag := 'Y';
3013 ELSIF (p_attributes_rec.intmed_ship_to_org_id IS NOT NULL)
3014 AND (p_attributes_rec.intmed_ship_to_org_id <> FND_API.G_MISS_NUM) THEN
3015 l_mark_reprice_flag := 'Y';
3016 END IF;
3017 IF l_debug_on THEN
3018 WSH_DEBUG_SV.log(l_module_name,'l_mark_reprice_flag',l_mark_reprice_flag);
3019 END IF;
3020
3021 /* 4410272
3022 FOR cur in c_get_delivery_detail_id LOOP
3023 l_details_marked(l_details_marked.COUNT+1) := cur.delivery_detail_id;
3024 END LOOP;
3025 */
3026 IF l_mark_reprice_flag = 'Y' THEN
3027 IF l_details_marked.count > 0 THEN
3028 --
3029 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
3030 p_entity_type => 'DELIVERY_DETAIL',
3031 p_entity_ids => l_details_marked,
3032 x_return_status => l_return_status);
3033 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3034 raise mark_reprice_error;
3035 END IF;
3036 END IF;
3037 END IF;
3038
3039 -- Added for bug 4410272
3040 --
3041 IF l_debug_on THEN
3042 WSH_DEBUG_SV.log(l_module_name, 'TP Details count', l_tp_details.count);
3043 END IF;
3044 --
3045 -- IF condition added for bug 4410272
3046 IF ( l_tp_details.COUNT > 0 )
3047 THEN
3048 -- {
3049 WSH_TP_RELEASE.calculate_cont_del_tpdates(
3050 p_entity => 'DLVB',
3051 p_entity_ids => l_tp_details,
3052 x_return_status => l_return_status);
3053 -- }
3054 IF (l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3055 WSH_INTERFACE.PrintMsg(name=>'WSH_CALC_CONT_DEL_TPDATES',
3056 txt=>'Error in calculating Container/Delivery TP dates ');
3057 IF l_debug_on THEN
3058 WSH_DEBUG_SV.log(l_module_name,'Error in calculating Container/Delivery TP dates ');
3059 END IF;
3060 END IF;
3061 END IF;
3062 --
3063 -- DBI Project
3064 -- Update of wsh_delivery_details where requested_quantity/released_status
3065 -- are changed, call DBI API after the update.
3066 -- This API will also check for DBI Installed or not
3067 IF l_debug_on THEN
3068 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API delivery details l_details_marked count :',l_details_marked.COUNT);
3069 END IF;
3070 WSH_INTEGRATION.DBI_Update_Detail_Log
3071 (p_delivery_detail_id_tab => l_details_marked,
3072 p_dml_type => 'UPDATE',
3073 x_return_status => l_dbi_rs);
3074
3075 IF l_debug_on THEN
3076 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3077 END IF;
3078 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3079 x_return_status := l_dbi_rs;
3080 ROLLBACK to before_changes;
3081 -- just pass this return status to caller API
3082 IF l_debug_on THEN
3083 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
3084 WSH_DEBUG_SV.pop(l_module_name);
3085 END IF;
3086 return;
3087 END IF;
3088 -- End of Code for DBI Project
3089 --
3090 --
3091 IF l_debug_on THEN
3092 WSH_DEBUG_SV.pop(l_module_name);
3093 END IF;
3094 --
3095 EXCEPTION
3096 WHEN mark_reprice_error then
3097 FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
3098 x_return_status := l_return_status;
3099 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3100 --
3101 IF l_debug_on THEN
3102 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3103 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
3104 END IF;
3105 --
3106 WHEN Update_Failed THEN
3107 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_LOCATION');
3108 --
3109 IF l_debug_on THEN
3110 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3111 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_FAILED');
3112 END IF;
3113 --
3114 WHEN others THEN
3115 IF c_is_reservable%ISOPEN THEN
3116 CLOSE c_is_reservable;
3117 END IF;
3118 --
3119 ROLLBACK TO before_changes;
3120 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3121 wsh_util_core.default_handler('WSH_USA_ACTIONS.Update_Attributes',l_module_name);
3122 --
3123 IF l_debug_on THEN
3124 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3125 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3126 END IF;
3127 --
3128 END Update_Attributes;
3129
3130
3131
3132 PROCEDURE Import_Delivery_Details(
3133 p_source_line_id IN NUMBER,
3134 p_source_code IN VARCHAR2,
3135 x_return_status OUT NOCOPY VARCHAR2)
3136 IS
3137 CURSOR C_specific_item_info(c_p_inventory_item_id number,
3138 c_p_organization_id number)
3139 IS
3140 SELECT hazard_class_id, primary_uom_code, weight_uom_code,
3141 unit_weight, volume_uom_code, unit_volume , decode(mtl_transactions_enabled_flag,'Y','Y','N')
3142 FROM mtl_system_items m
3143 WHERE m.inventory_item_id = c_p_inventory_item_id
3144 AND m.organization_id = c_p_organization_id;
3145
3146 -- Bug 2995052 : Treating the Back Order delivery detail lines also as not released lines.
3147 -- Bug 2896605 : Remove restriction on adding lines with different released status' to the
3148 -- same ship set.
3149 -- CURSOR c_check_ship_sets is defined in the spec
3150
3151 l_ship_set_id NUMBER;
3152 haz_class_id number;
3153 prim_uom_code varchar2(3);
3154 transactable_flag varchar2(1);
3155 weight_uom varchar2(3);
3156 unit_weight number;
3157 volume_uom varchar2(3);
3158 unit_volume number;
3159 invalid_qty_or_uom exception;
3160
3161 create_details_failed exception;
3162 create_assignments_failed exception;
3163 process_order_failed exception;
3164 invalid_source_code exception;
3165 l_cr_dt_summary varchar2(3000);
3166 l_cr_dt_details varchar2(3000);
3167 l_cr_dt_count number;
3168 l_cr_as_summary varchar2(3000);
3169 l_cr_as_details varchar2(3000);
3170 l_cr_as_count number;
3171 i number;
3172 l_return_status varchar2(30);
3173 l_msg_data varchar2(4000);
3174 l_msg_count number;
3175 l_ship_from_location_id number;
3176 l_ship_from_location_id1 number;
3177 l_ship_to_location_id number;
3178 l_ship_to_location_id1 number;
3179 l_deliver_to_location_id number;
3180 l_deliver_to_location_id1 number;
3181 l_intmed_ship_to_location_id number;
3182 l_intmed_ship_to_location_id1 number;
3183 l_location_status varchar2(30);
3184 l_line_rec OE_ORDER_PUB.line_rec_type;
3185 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
3186 l_control_rec OE_GLOBALS.control_rec_type;
3187 l_header_out_rec OE_ORDER_PUB.Header_Rec_Type;
3188 l_line_out_tbl OE_ORDER_PUB.Line_Tbl_Type;
3189 l_line_adj_out_tbl oe_order_pub.line_Adj_Tbl_Type;
3190 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
3191 l_Header_Scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3192 l_Line_Scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
3193 l_action_request_out_tbl OE_Order_PUB.request_Tbl_type;
3194 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
3195 l_header_val_rec OE_Order_PUB.header_val_rec_type;
3196 l_header_adj_val_tbl OE_Order_PUB.header_adj_val_tbl_type;
3197 l_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_val_Tbl_Type;
3198 l_line_val_tbl OE_ORDER_PUB.Line_val_Tbl_Type;
3199 l_line_adj_val_tbl oe_order_pub.line_Adj_val_Tbl_Type;
3200 l_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_val_Tbl_Type;
3201 l_header_Scredit_val_tbl OE_Order_PUB.header_Scredit_val_Tbl_Type;
3202 l_line_index NUMBER := 0;
3203 l_summary varchar2(3000);
3204 l_details varchar2(3000);
3205 l_get_msg_count number;
3206 invalid_org exception;
3207 invalid_cust_site exception;
3208 invalid_ship_set exception;
3209 -- HW OPM BUG#:2677054
3210 -- HW OPMCONV. Removed OPM variables
3211
3212 -- HW end of 2677054
3213 l_cr_dt_status varchar2(30);
3214 dummy_assgn_rowid varchar2(30);
3215 dummy_delivery_assignment_id number;
3216 l_cr_as_status varchar2(30);
3217 k number;
3218
3219 -- odaboval : Begin of OPM Changes
3220
3221 l_transfer_qty NUMBER(19,9);
3222 -- 2654051: Exception (if any) raised for Booked lines
3223 l_booked_ln_excpn BOOLEAN;
3224
3225 /* HW OPMCOMV - No need to get OPM item info
3226 CURSOR C_GET_OPM_LOT_ID(c_opm_item_id NUMBER,
3227 c_opm_lot_number VARCHAR2,
3228 c_opm_sublot_number VARCHAR2)
3229 IS
3230 SELECT lot_id
3231 FROM ic_lots_mst
3232 WHERE item_id = c_opm_item_id
3233 AND lot_no = c_opm_lot_number
3234 AND sublot_no = c_opm_sublot_number;
3235 -- odaboval : End of OPM Changes
3236 */
3237
3238 -- performance bug 4891897 : high Sharable Memory usage (1,472,182)
3239 -- divide the cursor into two and use the appropriate one
3240
3241 -- columns/views must be in sync with C_PULL_ONE_LINE
3242
3243 CURSOR C_PULL_DELIVERY_DETAILS is
3244 SELECT
3245 HEADER_ID,
3246 HEADER_NUMBER,
3247 HEADER_TYPE_ID,
3248 HEADER_TYPE_NAME,
3249 LINE_ID,
3250 LINE_NUMBER,
3251 ORG_ID,
3252 SOLD_TO_ORG_ID,
3253 INVENTORY_ITEM_ID,
3254 ITEM_DESCRIPTION,
3255 SHIP_FROM_ORG_ID,
3256 SUBINVENTORY,
3257 SHIP_TO_ORG_ID,
3258 DELIVER_TO_ORG_ID,
3259 SHIP_TO_CONTACT_ID,
3260 DELIVER_TO_CONTACT_ID,
3261 INTMED_SHIP_TO_ORG_ID,
3262 INTMED_SHIP_TO_CONTACT_ID,
3263 SHIP_TOLERANCE_ABOVE,
3264 SHIP_TOLERANCE_BELOW,
3265 ORDERED_QUANTITY,
3266 SHIPPED_QUANTITY,
3267 DELIVERED_QUANTITY,
3268 ORDER_QUANTITY_UOM,
3269 SHIPPING_QUANTITY_UOM,
3270 SHIPPING_QUANTITY,
3271 DATE_SCHEDULED,
3272 SHIPPING_METHOD_CODE,
3273 FREIGHT_CARRIER_CODE,
3274 FREIGHT_TERMS_CODE,
3275 SHIPMENT_PRIORITY_CODE,
3276 FOB_CODE,
3277 ITEM_IDENTIFIER_TYPE,
3278 ORDERED_ITEM_ID,
3279 DATE_REQUESTED,
3280 DEP_PLAN_REQUIRED_FLAG,
3281 CUSTOMER_PROD_SEQ_NUMBER,
3282 CUSTOMER_DOCK_CODE,
3283 SHIPPING_INTERFACED_FLAG,
3284 SHIP_SET_ID,
3285 ATO_LINE_ID,
3286 SHIP_MODEL_COMPLETE_FLAG,
3287 TOP_MODEL_LINE_ID,
3288 ITEM_TYPE_CODE,
3289 CUST_PO_NUMBER,
3290 ARRIVAL_SET_ID,
3291 SOURCE_TYPE_CODE,
3292 LINE_TYPE_ID,
3293 PROJECT_ID,
3294 TASK_ID,
3295 SHIPPING_INSTRUCTIONS,
3296 PACKING_INSTRUCTIONS,
3297 MASTER_CONTAINER_ITEM_ID,
3298 DETAIL_CONTAINER_ITEM_ID,
3299 PREFERRED_GRADE,
3300 ORDERED_QUANTITY2,
3301 ORDERED_QUANTITY_UOM2,
3302 UNIT_LIST_PRICE,
3303 TRANSACTIONAL_CURR_CODE,
3304 END_ITEM_UNIT_NUMBER,
3305 TP_CONTEXT,
3306 TP_ATTRIBUTE1,
3307 TP_ATTRIBUTE2,
3308 TP_ATTRIBUTE3,
3309 TP_ATTRIBUTE4,
3310 TP_ATTRIBUTE5,
3311 TP_ATTRIBUTE6,
3312 TP_ATTRIBUTE7,
3313 TP_ATTRIBUTE8,
3314 TP_ATTRIBUTE9,
3315 TP_ATTRIBUTE10,
3316 TP_ATTRIBUTE11,
3317 TP_ATTRIBUTE12,
3318 TP_ATTRIBUTE13,
3319 TP_ATTRIBUTE14,
3320 TP_ATTRIBUTE15,
3321 SOLD_TO_CONTACT_ID,
3322 CUSTOMER_JOB,
3323 CUSTOMER_PRODUCTION_LINE,
3324 CUST_MODEL_SERIAL_NUMBER,
3325 LINE_SET_ID,
3326 /* J TP Release */
3327 latest_acceptable_date,
3328 promise_date,
3329 schedule_arrival_date,
3330 earliest_acceptable_date,
3331 earliest_ship_date, --equivalent of demand_satisfaction_date in TP
3332 order_date_type_code,
3333 source_document_type_id
3334 from oe_delivery_lines_v
3335 where ship_from_org_id is not NULL
3336 and order_quantity_uom is not NULL
3337 and ordered_quantity is not NULL;
3338
3339 -- columns/views must be in sync with C_PULL_DELIVERY_DETAILS
3340
3341 CURSOR C_PULL_ONE_LINE is
3342 SELECT
3343 HEADER_ID,
3344 HEADER_NUMBER,
3345 HEADER_TYPE_ID,
3346 HEADER_TYPE_NAME,
3347 LINE_ID,
3348 LINE_NUMBER,
3349 ORG_ID,
3350 SOLD_TO_ORG_ID,
3351 INVENTORY_ITEM_ID,
3352 ITEM_DESCRIPTION,
3353 SHIP_FROM_ORG_ID,
3354 SUBINVENTORY,
3355 SHIP_TO_ORG_ID,
3356 DELIVER_TO_ORG_ID,
3357 SHIP_TO_CONTACT_ID,
3358 DELIVER_TO_CONTACT_ID,
3359 INTMED_SHIP_TO_ORG_ID,
3360 INTMED_SHIP_TO_CONTACT_ID,
3361 SHIP_TOLERANCE_ABOVE,
3362 SHIP_TOLERANCE_BELOW,
3363 ORDERED_QUANTITY,
3364 SHIPPED_QUANTITY,
3365 DELIVERED_QUANTITY,
3366 ORDER_QUANTITY_UOM,
3367 SHIPPING_QUANTITY_UOM,
3368 SHIPPING_QUANTITY,
3369 DATE_SCHEDULED,
3370 SHIPPING_METHOD_CODE,
3371 FREIGHT_CARRIER_CODE,
3372 FREIGHT_TERMS_CODE,
3373 SHIPMENT_PRIORITY_CODE,
3374 FOB_CODE,
3375 ITEM_IDENTIFIER_TYPE,
3376 ORDERED_ITEM_ID,
3377 DATE_REQUESTED,
3378 DEP_PLAN_REQUIRED_FLAG,
3379 CUSTOMER_PROD_SEQ_NUMBER,
3380 CUSTOMER_DOCK_CODE,
3381 SHIPPING_INTERFACED_FLAG,
3382 SHIP_SET_ID,
3383 ATO_LINE_ID,
3384 SHIP_MODEL_COMPLETE_FLAG,
3385 TOP_MODEL_LINE_ID,
3386 ITEM_TYPE_CODE,
3387 CUST_PO_NUMBER,
3388 ARRIVAL_SET_ID,
3389 SOURCE_TYPE_CODE,
3390 LINE_TYPE_ID,
3391 PROJECT_ID,
3392 TASK_ID,
3393 SHIPPING_INSTRUCTIONS,
3394 PACKING_INSTRUCTIONS,
3395 MASTER_CONTAINER_ITEM_ID,
3396 DETAIL_CONTAINER_ITEM_ID,
3397 PREFERRED_GRADE,
3398 ORDERED_QUANTITY2,
3399 ORDERED_QUANTITY_UOM2,
3400 UNIT_LIST_PRICE,
3401 TRANSACTIONAL_CURR_CODE,
3402 END_ITEM_UNIT_NUMBER,
3403 TP_CONTEXT,
3404 TP_ATTRIBUTE1,
3405 TP_ATTRIBUTE2,
3406 TP_ATTRIBUTE3,
3407 TP_ATTRIBUTE4,
3408 TP_ATTRIBUTE5,
3409 TP_ATTRIBUTE6,
3410 TP_ATTRIBUTE7,
3411 TP_ATTRIBUTE8,
3412 TP_ATTRIBUTE9,
3413 TP_ATTRIBUTE10,
3414 TP_ATTRIBUTE11,
3415 TP_ATTRIBUTE12,
3416 TP_ATTRIBUTE13,
3417 TP_ATTRIBUTE14,
3418 TP_ATTRIBUTE15,
3419 SOLD_TO_CONTACT_ID,
3420 CUSTOMER_JOB,
3421 CUSTOMER_PRODUCTION_LINE,
3422 CUST_MODEL_SERIAL_NUMBER,
3423 LINE_SET_ID,
3424 latest_acceptable_date,
3425 promise_date,
3426 schedule_arrival_date,
3427 earliest_acceptable_date,
3428 earliest_ship_date,
3429 order_date_type_code,
3430 source_document_type_id
3431 from oe_delivery_lines_v
3432 WHERE line_id = p_source_line_id
3433 and p_source_line_id IS NOT NULL
3434 and ship_from_org_id is not NULL
3435 and order_quantity_uom is not NULL
3436 and ordered_quantity is not NULL;
3437
3438 -- Bug: 1902176. Need to Lock the oe_line (Current Line fetched)
3439 -- otherwise it could result in Multiple Wsh_del_dtl records to be created
3440 -- for instance in Import Del. Details.
3441
3442 CURSOR C_OE_LINES_REC_LOCK (c_line_id NUMBER) is
3443 SELECT line_id
3444 FROM oe_order_lines_all
3445 --FROM oe_delivery_lines_v
3446 WHERE line_id = c_line_id
3447 and nvl(shipping_interfaced_flag, 'N') = 'N'
3448 FOR UPDATE NOWAIT;
3449
3450 ln_rec_info C_PULL_DELIVERY_DETAILS%ROWTYPE;
3451
3452 l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
3453 l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_assignments_rec_TYPE;
3454 dummy_rowid VARCHAR2(30);
3455 dummy_delivery_detail_id number;
3456 dummy_slid number;
3457 l_header_price_att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
3458 l_header_adj_assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
3459 l_header_adj_att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
3460 l_line_price_att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
3461 l_line_adj_assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
3462 l_line_adj_att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
3463
3464 l_wf_source_header_id NUMBER;
3465 l_wf_source_code VARCHAR2(30);
3466 l_wf_order_number NUMBER;
3467 l_wf_contact_type VARCHAR2(10);
3468 l_wf_contact_id NUMBER;
3469 l_result BOOLEAN;
3470 latest_pickup_tpdate_excep DATE;
3471 latest_dropoff_tpdate_excep DATE;
3472
3473 CURSOR c_shipping_parameters(c_organization_id NUMBER) IS
3474 SELECT freight_class_cat_set_id, commodity_code_cat_set_id, enforce_ship_set_and_smc --2373131
3475 FROM wsh_shipping_parameters
3476 WHERE organization_id = c_organization_id;
3477
3478 l_ship_parameters c_shipping_parameters%ROWTYPE;
3479
3480 cursor c_get_ship_set_name(c_set_id IN NUMBER) is --2373131
3481 select set_name
3482 from oe_sets
3483 where set_id = c_set_id;
3484
3485 l_ship_set_name VARCHAR2(30);
3486
3487 CURSOR c_category_id( c_inventory_item_id NUMBER, c_organization_id NUMBER,
3488 c_category_set_id NUMBER) IS
3489 SELECT category_id
3490 FROM mtl_item_categories
3491 WHERE inventory_item_id = c_inventory_item_id
3492 AND organization_id = c_organization_id
3493 AND category_set_id = c_category_set_id;
3494
3495 l_freight_cl_cat_id NUMBER;
3496 l_commodity_cat_id NUMBER;
3497 l_oe_line_id_locked NUMBER; -- 1902176
3498
3499 l_carrier_rec WSH_CARRIERS_GRP.Carrier_Service_InOut_Rec_Type;
3500 l_generic_flag VARCHAR2(1);
3501 l_service_level VARCHAR2(30);
3502 l_mode_of_transport VARCHAR2(30);
3503 l_carrier_id NUMBER;
3504
3505 l_pull_lines_count NUMBER :=0;
3506
3507 --OTM R12
3508 l_delivery_detail_tab WSH_ENTITY_INFO_TAB;
3509 l_delivery_detail_rec WSH_ENTITY_INFO_REC;
3510 l_item_quantity_uom_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3511 l_gc3_is_installed VARCHAR2(1);
3512 --
3513
3514 --
3515 l_debug_on BOOLEAN;
3516 --
3517 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IMPORT_DELIVERY_DETAILS';
3518 --
3519 BEGIN
3520 --
3521 --
3522 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3523 --
3524 IF l_debug_on IS NULL
3525 THEN
3526 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3527 END IF;
3528 --
3529 IF l_debug_on THEN
3530 WSH_DEBUG_SV.push(l_module_name);
3531 --
3532 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
3533 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
3534 END IF;
3535 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3536
3537 --OTM R12, initialize
3538 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3539 IF l_gc3_is_installed IS NULL THEN
3540 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3541 END IF;
3542 IF (l_gc3_is_installed = 'Y') THEN
3543 l_delivery_detail_tab := WSH_ENTITY_INFO_TAB();
3544 l_delivery_detail_tab.EXTEND;
3545 END IF;
3546 --
3547
3548 i := 0;
3549 IF (P_SOURCE_CODE <> 'OE') THEN
3550 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SOURCE_CODE');
3551 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
3552 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3553 WSH_UTIL_CORE.add_message(x_return_status,l_module_name);
3554 --
3555 IF l_debug_on THEN
3556 WSH_DEBUG_SV.pop(l_module_name);
3557 END IF;
3558 --
3559 RETURN;
3560 END IF;
3561
3562 -- 2654051
3563 l_booked_ln_excpn := FALSE;
3564
3565 -- bug 4891897
3566 IF p_source_line_id IS NULL THEN
3567 OPEN C_PULL_DELIVERY_DETAILS;
3568 ELSE
3569 OPEN C_PULL_ONE_LINE;
3570 END IF;
3571
3572 LOOP
3573 <<start_over>>
3574 IF C_OE_LINES_REC_LOCK%ISOPEN THEN -- Bug 2410864
3575 CLOSE C_OE_LINES_REC_LOCK;
3576 END IF;
3577
3578 -- 2680026
3579 IF ( l_booked_ln_excpn and p_source_line_id IS NOT NULL ) THEN
3580 raise create_details_failed;
3581 END IF;
3582
3583 i := i + 1;
3584
3585 IF p_source_line_id IS NULL THEN
3586 FETCH C_PULL_DELIVERY_DETAILS into ln_rec_info;
3587 EXIT WHEN C_PULL_DELIVERY_DETAILS%NOTFOUND; -- Bug 2410864
3588 ELSE
3589 FETCH C_PULL_ONE_LINE into ln_rec_info;
3590 EXIT WHEN C_PULL_ONE_LINE%NOTFOUND;
3591 END IF;
3592 --Added below IF condition to check whether the workflow has ship line activity or not --Bugfix 6740363
3593 IF p_source_line_id IS NULL AND NOT wf_engine.activity_exist_in_process('OEOL', to_char(ln_rec_info.line_id), 'OEOL', 'SHIP_LINE') THEN --{
3594 GOTO start_over;
3595 ELSE
3596 l_pull_lines_count := l_pull_lines_count + 1;
3597
3598 -- Bug: 1902176. Need to Lock the oe_line (Current Line fetched)
3599 BEGIN -- 1902176: Block to Lock Oe_line REcord and to TRap NO WAIT error
3600 -- 1902176 :Requerying record - FOR UPDATE NOWAIT (locking this record)
3601 IF l_debug_on THEN
3602 WSH_DEBUG_SV.log(l_module_name,'line_id',ln_rec_info.line_id);
3603 END IF;
3604 OPEN C_OE_LINES_REC_LOCK( ln_rec_info.line_id); -- 1902176
3605 FETCH C_OE_LINES_REC_LOCK INTO l_oe_line_id_locked; -- 1902176
3606
3607 OPEN c_specific_item_info(ln_rec_info.inventory_item_id, ln_rec_info.ship_from_org_id);
3608 FETCH C_SPECIFIC_ITEM_INFO INTO haz_class_id, prim_uom_code, weight_uom, unit_weight,
3609 volume_uom, unit_volume, transactable_flag;
3610 CLOSE c_specific_item_info;
3611 WSH_INTERFACE.PrintMsg(txt=>'Importing order line ' || ln_rec_info.line_id);
3612 WSH_UTIL_CORE.GET_LOCATION_ID('ORG', ln_rec_info.ship_from_org_id,
3613 l_ship_from_location_id, l_location_status);
3614
3615 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3616 IF l_debug_on THEN
3617 WSH_DEBUG_SV.log(l_module_name,'Failed to get location for line: '||ln_rec_info.line_id||' org:'||ln_rec_info.ship_from_org_id);
3618 END IF;
3619 l_booked_ln_excpn := TRUE;
3620 GOTO start_over;
3621 END IF;
3622 WSH_INTERFACE.PrintMsg(txt=>'From organization:'|| ln_rec_info.ship_from_org_id||' loc:'|| l_ship_from_location_id );
3623
3624 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.ship_to_org_id,
3625 l_ship_to_location_id, l_location_status);
3626 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3627 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3628 txt=>'Failed to get location for line: '||ln_rec_info.line_id||
3629 ' ship to org:'||ln_rec_info.ship_to_org_id);
3630 IF l_debug_on THEN
3631 WSH_DEBUG_SV.log(l_module_name,'Failed to get location for line: '||ln_rec_info.line_id|| ' ship to org:'||ln_rec_info.ship_to_org_id);
3632 END IF;
3633 l_booked_ln_excpn := TRUE;
3634 GOTO start_over;
3635 END IF;
3636 WSH_INTERFACE.PrintMsg(txt=>'To organization:'|| ln_rec_info.ship_to_org_id||' loc:'|| l_ship_to_location_id );
3637
3638 IF (ln_rec_info.deliver_to_org_id IS NOT NULL) THEN
3639 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.deliver_to_org_id,
3640 l_deliver_to_location_id, l_location_status);
3641 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3642 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3643 txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3644 ' delivery to org:'||ln_rec_info.deliver_to_org_id);
3645 IF l_debug_on THEN
3646 WSH_DEBUG_SV.log(l_module_name,'Failed to get location for line: '||ln_rec_info.line_id||' delivery to org:'||ln_rec_info.deliver_to_org_id);
3647 END IF;
3648 l_booked_ln_excpn := TRUE;
3649 GOTO start_over;
3650 END IF;
3651 END IF;
3652 WSH_INTERFACE.PrintMsg(txt=>'Deliver to org:'|| ln_rec_info.deliver_to_org_id||' loc:'|| l_deliver_to_location_id );
3653
3654 IF (ln_rec_info.intmed_ship_to_org_id IS NOT NULL) THEN
3655 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.intmed_ship_to_org_id,
3656 l_intmed_ship_to_location_id, l_location_status);
3657 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3658 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3659 txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3660 ' intermediate ship to org:'||ln_rec_info.intmed_ship_to_org_id);
3661 IF l_debug_on THEN
3662 WSH_DEBUG_SV.log(l_module_name,'Failed to get location for line: '||ln_rec_info.line_id||' intermediate ship to org:'||ln_rec_info.intmed_ship_to_org_id);
3663 END IF;
3664 l_booked_ln_excpn := TRUE;
3665 GOTO start_over;
3666 END IF;
3667 END IF;
3668 WSH_INTERFACE.PrintMsg(txt=>'Intermediate ship to org:'|| ln_rec_info.intmed_ship_to_org_id||' loc:'|| l_intmed_ship_to_location_id );
3669
3670 IF (l_deliver_to_location_id is NULL) THEN
3671 l_deliver_to_location_id := l_ship_to_location_id;
3672 END IF;
3673
3674 OPEN c_shipping_parameters(ln_rec_info.ship_from_org_id);
3675 FETCH c_shipping_parameters INTO l_ship_parameters;
3676 IF ( c_shipping_parameters%NOTFOUND ) THEN
3677 IF l_debug_on THEN
3678 WSH_DEBUG_SV.log(l_module_name,'Shipping Parameters notfound for warehuse:'||ln_rec_info.ship_from_org_id);
3679 END IF;
3680 WSH_INTERFACE.PrintMsg(txt=>'Shipping Parameters notfound for warehouse:'||ln_rec_info.ship_from_org_id);
3681 END IF;
3682 CLOSE c_shipping_parameters;
3683
3684 OPEN c_category_id(ln_rec_info.inventory_item_id,
3685 ln_rec_info.ship_from_org_id,
3686 l_ship_parameters.freight_class_cat_set_id);
3687 FETCH c_category_id INTO l_freight_cl_cat_id;
3688 IF (c_category_id%NOTFOUND) THEN
3689 WSH_INTERFACE.PrintMsg(txt=>'Freight Catergory id notfound for item:'||ln_rec_info.inventory_item_id||
3690 ' warehouse:'||ln_rec_info.ship_from_org_id ||
3691 ' freight_class:'||l_ship_parameters.freight_class_cat_set_id);
3692 END IF;
3693 CLOSE c_category_id;
3694
3695 OPEN c_category_id(ln_rec_info.inventory_item_id,
3696 ln_rec_info.ship_from_org_id,
3697 l_ship_parameters.commodity_code_cat_set_id);
3698 FETCH c_category_id INTO l_commodity_cat_id;
3699 IF (c_category_id%NOTFOUND) THEN
3700 WSH_INTERFACE.PrintMsg(txt=>'Commodity Catergory id notfound for item:'||ln_rec_info.inventory_item_id||
3701 ' warehouse:'||ln_rec_info.ship_from_org_id ||
3702 ' commodity_class:'||l_ship_parameters.commodity_code_cat_set_id);
3703 END IF;
3704 CLOSE c_category_id;
3705 WSH_INTERFACE.PrintMsg(txt=>'frght_class_catg_id:'|| l_freight_cl_cat_id ||' commodity_class_ctg_id:'|| l_commodity_cat_id);
3706
3707
3708 -- Check added for Standalone Project
3709 IF NVL(WMS_DEPLOY.Wms_Deployment_Mode,'I') <> 'D' THEN --{
3710 /* J TP Release */
3711 WSH_TP_RELEASE.calculate_tp_dates (
3712 p_request_date_type => ln_rec_info.order_date_type_code,
3713 p_latest_acceptable_date => ln_rec_info.latest_acceptable_date,
3714 p_promise_date => ln_rec_info.promise_date,
3715 p_schedule_arrival_date => ln_rec_info.schedule_arrival_date,
3716 p_schedule_ship_date => ln_rec_info.date_scheduled,
3717 p_earliest_acceptable_date => ln_rec_info.earliest_acceptable_date,
3718 p_demand_satisfaction_date => ln_rec_info.earliest_ship_date,
3719 p_source_line_id => p_source_line_id,
3720 p_source_code => p_source_code,
3721 x_return_status => l_return_status,
3722 x_earliest_pickup_date => l_delivery_details_info.earliest_pickup_date,
3723 x_latest_pickup_date => l_delivery_details_info.latest_pickup_date,
3724 x_earliest_dropoff_date => l_delivery_details_info.earliest_dropoff_date,
3725 x_latest_dropoff_date => l_delivery_details_info.latest_dropoff_date
3726 );
3727 IF (l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3728 WSH_INTERFACE.PrintMsg(name=>'WSH_CALC_TP_DATES',
3729 txt=>'Error in calculating TP dates: '||ln_rec_info.order_date_type_code||ln_rec_info.latest_acceptable_date||ln_rec_info.promise_date||ln_rec_info.schedule_arrival_date||ln_rec_info.earliest_acceptable_date||ln_rec_info.earliest_ship_date);
3730 IF l_debug_on THEN
3731 WSH_DEBUG_SV.log(l_module_name,'Error in calculating TP dates: ' ||
3732 ln_rec_info.order_date_type_code ||
3733 ln_rec_info.latest_acceptable_date ||
3734 ln_rec_info.promise_date ||
3735 ln_rec_info.schedule_arrival_date ||
3736 ln_rec_info.earliest_acceptable_date||
3737 ln_rec_info.earliest_ship_date);
3738 END IF;
3739 END IF;
3740 IF l_delivery_details_info.earliest_pickup_date > l_delivery_details_info.latest_pickup_date THEN
3741 latest_pickup_tpdate_excep := l_delivery_details_info.latest_pickup_date;
3742 l_delivery_details_info.latest_pickup_date:= l_delivery_details_info.earliest_pickup_date;
3743 END IF;
3744
3745 IF l_delivery_details_info.earliest_dropoff_date > l_delivery_details_info.latest_dropoff_date THEN
3746 latest_dropoff_tpdate_excep := l_delivery_details_info.latest_dropoff_date;
3747 l_delivery_details_info.latest_dropoff_date := l_delivery_details_info.earliest_dropoff_date;
3748 END IF;
3749 END IF; --}
3750
3751 l_delivery_details_info.source_document_type_id := ln_rec_info.source_document_type_id;
3752 /*J TP Release*/
3753
3754 --bug 3272115 - for internal orders line dir shud be set to 'IO'
3755 --internal orders have source_document_type_id=10
3756 IF ln_rec_info.source_document_type_id=10 THEN
3757 l_delivery_details_info.line_direction:='IO';
3758 END IF;
3759
3760 l_delivery_details_info.source_code := 'OE';
3761 l_delivery_details_info.source_header_id := ln_rec_info.header_id;
3762 l_delivery_details_info.source_line_id := ln_rec_info.line_id;
3763 l_delivery_details_info.customer_id := ln_rec_info.sold_to_org_id;
3764 l_delivery_details_info.sold_to_contact_id := ln_rec_info.sold_to_contact_id;
3765 l_delivery_details_info.inventory_item_id := ln_rec_info.inventory_item_id;
3766 l_delivery_details_info.item_description := ln_rec_info.item_description;
3767 l_delivery_details_info.hazard_class_id := haz_class_id;
3768 l_delivery_details_info.ship_from_location_id := l_ship_from_location_id;
3769 l_delivery_details_info.ship_to_location_id := l_ship_to_location_id;
3770 l_delivery_details_info.ship_to_site_use_id := ln_rec_info.ship_to_org_id ;
3771 l_delivery_details_info.deliver_to_site_use_id := ln_rec_info.deliver_to_org_id ;
3772 l_delivery_details_info.deliver_to_location_id := l_deliver_to_location_id;
3773 l_delivery_details_info.ship_to_contact_id := ln_rec_info.ship_to_contact_id;
3774 l_delivery_details_info.deliver_to_contact_id := ln_rec_info.deliver_to_contact_id;
3775 l_delivery_details_info.intmed_ship_to_location_id := l_intmed_ship_to_location_id;
3776 l_delivery_details_info.intmed_ship_to_contact_id := ln_rec_info.intmed_ship_to_contact_id;
3777 l_delivery_details_info.ship_tolerance_above := ln_rec_info.ship_tolerance_above;
3778 l_delivery_details_info.ship_tolerance_below := ln_rec_info.ship_tolerance_below;
3779 WSH_INTERFACE.PrintMsg(txt=>'calling Convert uom=> order uom:'||ln_rec_info.order_quantity_uom||
3780 ' order_qty:'||ln_rec_info.ordered_quantity || ' item_id:'|| ln_rec_info.inventory_item_id ||
3781 'to uom:'||prim_uom_code);
3782
3783 -- HW OPMCONV.
3784 l_delivery_details_info.requested_quantity := wsh_wv_utils.convert_uom(ln_rec_info.order_quantity_uom,
3785 prim_uom_code,ln_rec_info.ordered_quantity,
3786 ln_rec_info.inventory_item_id);
3787 WSH_INTERFACE.PrintMsg(txt=>'Convert uom=> prim_uom:'||prim_uom_code);
3788 l_delivery_details_info.requested_quantity_uom := prim_uom_code;
3789
3790 -- odaboval : Begin of OPM Changes
3791 -- requested_quantities need to converted from apps primary uom code to
3792 -- opm primary uom code
3793 -- Only convert if this is a process org
3794 -- HW OPMCONV. Removed forking and print msgs
3795
3796 l_delivery_details_info.requested_quantity2 := ln_rec_info.ordered_quantity2;
3797
3798 l_delivery_details_info.requested_quantity_uom2 :=ln_rec_info.ordered_quantity_uom2;
3799 l_delivery_details_info.preferred_grade := ln_rec_info.preferred_grade;
3800 l_delivery_details_info.src_requested_quantity2 := ln_rec_info.ordered_quantity2;
3801 l_delivery_details_info.src_requested_quantity_uom2 := ln_rec_info.ordered_quantity_uom2;
3802 --
3803 -- HW OPMCONV. Removed reference to OPM in the debugging statements
3804 IF l_debug_on THEN
3805 WSH_DEBUG_SV.logmsg(l_module_name, 'LN_REC_INFO.PREFERRED_GRADE IS ' || LN_REC_INFO.PREFERRED_GRADE );
3806 WSH_DEBUG_SV.logmsg(l_module_name, 'LN_REC_INFO.ORDERED_QUANTITY2 IS ' || LN_REC_INFO.ORDERED_QUANTITY2 );
3807 END IF;
3808 --
3809 l_delivery_details_info.cancelled_quantity2:= NULL;
3810 -- odaboval : End of OPM Changes
3811 WSH_INTERFACE.PrintMsg(txt=>'Continuing to populate delivery_details_info record.');
3812
3813 l_delivery_details_info.customer_requested_lot_flag := NULL;
3814 l_delivery_details_info.date_requested := ln_rec_info.date_requested;
3815 l_delivery_details_info.date_scheduled := ln_rec_info.date_scheduled;
3816 l_delivery_details_info.load_seq_number := NULL;
3817 IF ln_rec_info.shipping_method_code IS NOT NULL THEN
3818 IF l_debug_on THEN
3819 WSH_DEBUG_SV.logmsg(l_module_name, ' shipping_method_code: ' || ln_rec_info.shipping_method_code );
3820 END IF;
3821
3822 l_carrier_rec.ship_method_code := ln_rec_info.shipping_method_code;
3823 WSH_CARRIERS_GRP.get_carrier_service_mode(
3824 p_carrier_service_inout_rec => l_carrier_rec,
3825 x_return_status => x_return_status);
3826
3827 IF l_debug_on THEN
3828 WSH_DEBUG_SV.logmsg(l_module_name, ' shipping_method_code: ' || l_carrier_rec.ship_method_code );
3829 WSH_DEBUG_SV.logmsg(l_module_name, ' generic: ' || l_carrier_rec.generic_flag );
3830 WSH_DEBUG_SV.logmsg(l_module_name, ' service_level: ' || l_carrier_rec.service_level );
3831 WSH_DEBUG_SV.logmsg(l_module_name, ' mode_of_transport: ' || l_carrier_rec.mode_of_transport );
3832 END IF;
3833 END IF;
3834 IF l_carrier_rec.generic_flag = 'Y' THEN
3835 l_delivery_details_info.ship_method_code := NULL;
3836 l_delivery_details_info.carrier_id := NULL;
3837 ELSE
3838 l_delivery_details_info.ship_method_code := ln_rec_info.shipping_method_code;
3839 l_delivery_details_info.carrier_id := l_carrier_rec.carrier_id;
3840 END IF;
3841 l_delivery_details_info.service_level := l_carrier_rec.service_level;
3842 l_delivery_details_info.mode_of_transport := l_carrier_rec.mode_of_transport;
3843 l_delivery_details_info.freight_terms_code := ln_rec_info.freight_terms_code;
3844 l_delivery_details_info.shipment_priority_code := ln_rec_info.shipment_priority_code;
3845 l_delivery_details_info.fob_code := ln_rec_info.fob_code;
3846 IF (ln_rec_info.item_identifier_type = 'CUST') THEN
3847 l_delivery_details_info.customer_item_id := ln_rec_info.ordered_item_id;
3848 END IF;
3849 l_delivery_details_info.dep_plan_required_flag := ln_rec_info.dep_plan_required_flag;
3850 l_delivery_details_info.customer_prod_seq := ln_rec_info.customer_prod_seq_number;
3851 l_delivery_details_info.customer_dock_code := ln_rec_info.customer_dock_code;
3852 l_delivery_details_info.cust_model_serial_number := ln_rec_info.cust_model_serial_number;
3853 l_delivery_details_info.customer_job := ln_rec_info.customer_job;
3854 l_delivery_details_info.customer_production_line := ln_rec_info.customer_production_line;
3855 -- bug 1701560: use requested_quantity, instead of ordered_quantity,
3856 -- to calculate correct net_weight
3857 l_delivery_details_info.net_weight := unit_weight * l_delivery_details_info.requested_quantity;
3858 l_delivery_details_info.weight_uom_code := weight_uom;
3859 -- bug 1701560: use requested_quantity, instead of ordered_quantity,
3860 -- to calculate correct volume
3861 l_delivery_details_info.volume := unit_volume * l_delivery_details_info.requested_quantity;
3862 l_delivery_details_info.volume_uom_code := volume_uom;
3863 l_delivery_details_info.released_flag := 'N';
3864 l_delivery_details_info.pickable_flag := transactable_flag;
3865 l_delivery_details_info.organization_id := ln_rec_info.ship_from_org_id;
3866 l_delivery_details_info.ship_set_id := ln_rec_info.ship_set_id;
3867 l_delivery_details_info.arrival_set_id := ln_rec_info.arrival_set_id;
3868 l_delivery_details_info.ship_model_complete_flag := ln_rec_info.ship_model_complete_flag;
3869 l_delivery_details_info.top_model_line_id := ln_rec_info.top_model_line_id;
3870 l_delivery_details_info.source_header_number := ln_rec_info.header_number;
3871 l_delivery_details_info.source_header_type_id := ln_rec_info.header_type_id;
3872 l_delivery_details_info.source_header_type_name := ln_rec_info.header_type_name;
3873 l_delivery_details_info.cust_po_number := ln_rec_info.cust_po_number;
3874 l_delivery_details_info.ato_line_id := ln_rec_info.ato_line_id;
3875 l_delivery_details_info.src_requested_quantity := ln_rec_info.ordered_quantity;
3876 l_delivery_details_info.src_requested_quantity_uom := ln_rec_info.order_quantity_uom;
3877 l_delivery_details_info.cancelled_quantity := NULL;
3878 l_delivery_details_info.shipping_instructions := ln_rec_info.shipping_instructions;
3879 l_delivery_details_info.packing_instructions := ln_rec_info.packing_instructions;
3880 l_delivery_details_info.project_id := ln_rec_info.project_id;
3881 l_delivery_details_info.task_id := ln_rec_info.task_id;
3882 l_delivery_details_info.org_id := ln_rec_info.org_id;
3883 l_delivery_details_info.source_line_number := ln_rec_info.line_number;
3884 -- l_delivery_details_info.master_container_item_id := ln_rec_info.master_container_item_id; vms updated in 115.69
3885 l_delivery_details_info.master_container_item_id := NULL;
3886 -- l_delivery_details_info.detail_container_item_id := ln_rec_info.detail_container_item_id;
3887 l_delivery_details_info.detail_container_item_id := NULL;
3888 l_delivery_details_info.released_status := 'N';
3889 l_delivery_details_info.container_flag := 'N';
3890 l_delivery_details_info.container_type_code := NULL;
3891 l_delivery_details_info.container_name := NULL;
3892 l_delivery_details_info.fill_percent := NULL;
3893 l_delivery_details_info.gross_weight := l_delivery_details_info.net_weight;
3894 l_delivery_details_info.master_serial_number := NULL;
3895 l_delivery_details_info.maximum_load_weight := NULL;
3896 l_delivery_details_info.maximum_volume := NULL;
3897 l_delivery_details_info.minimum_fill_percent := NULL;
3898 l_delivery_details_info.seal_code := NULL;
3899 l_delivery_details_info.mvt_stat_status := 'NEW';
3900 l_delivery_details_info.unit_price := ln_rec_info.unit_list_price;
3901 l_delivery_details_info.currency_code := ln_rec_info.transactional_curr_code;
3902 l_delivery_details_info.unit_number := ln_rec_info.end_item_unit_number;
3903 l_delivery_details_info.freight_class_cat_id := l_freight_cl_cat_id;
3904 l_delivery_details_info.commodity_code_cat_id := l_commodity_cat_id;
3905
3906 l_delivery_details_info.subinventory := ln_rec_info.subinventory;
3907 -- l_delivery_details_info.attribute15 := ln_rec_info.subinventory; -- 1561078
3908 l_delivery_details_info.original_subinventory := ln_rec_info.subinventory; -- 1561078
3909
3910 l_delivery_details_info.tp_attribute_category := ln_rec_info.tp_context;
3911 l_delivery_details_info.tp_attribute1 := ln_rec_info.tp_attribute1;
3912 l_delivery_details_info.tp_attribute2 := ln_rec_info.tp_attribute2;
3913 l_delivery_details_info.tp_attribute3 := ln_rec_info.tp_attribute3;
3914 l_delivery_details_info.tp_attribute4 := ln_rec_info.tp_attribute4;
3915 l_delivery_details_info.tp_attribute5 := ln_rec_info.tp_attribute5;
3916 l_delivery_details_info.tp_attribute6 := ln_rec_info.tp_attribute6;
3917 l_delivery_details_info.tp_attribute7 := ln_rec_info.tp_attribute7;
3918 l_delivery_details_info.tp_attribute8 := ln_rec_info.tp_attribute8;
3919 l_delivery_details_info.tp_attribute9 := ln_rec_info.tp_attribute9;
3920 l_delivery_details_info.tp_attribute10 := ln_rec_info.tp_attribute10;
3921 l_delivery_details_info.tp_attribute11 := ln_rec_info.tp_attribute11;
3922 l_delivery_details_info.tp_attribute12 := ln_rec_info.tp_attribute12;
3923 l_delivery_details_info.tp_attribute13 := ln_rec_info.tp_attribute13;
3924 l_delivery_details_info.tp_attribute14 := ln_rec_info.tp_attribute14;
3925 l_delivery_details_info.tp_attribute15 := ln_rec_info.tp_attribute15;
3926 -- J: W/V Changes
3927 l_delivery_details_info.unit_weight := unit_weight;
3928 l_delivery_details_info.unit_volume := unit_volume;
3929 l_delivery_details_info.wv_frozen_flag := 'N';
3930 l_delivery_details_info.request_date_type_code := ln_rec_info.order_date_type_code;
3931
3932 IF (l_delivery_details_info.requested_quantity = 0) THEN
3933 WSH_INTERFACE.PrintMsg(name=>'WSH_QTY_OR_UOM_NOT_VALID',
3934 txt=>'Failed to import order line ' || ln_rec_info.line_id || ' because quantity uom or quantity is invalid');
3935 l_booked_ln_excpn := TRUE;
3936 GOTO start_over;
3937 END IF;
3938
3939 -- anxsharm Bug 2181132
3940 l_delivery_details_info.source_line_set_id := ln_rec_info.line_set_id;
3941
3942 IF (l_delivery_details_info.source_line_set_id IS NOT NULL) THEN
3943 WSH_INTERFACE.PrintMsg(txt=> 'Source Line Set id -'||l_delivery_details_info.source_line_set_id);
3944 END IF;
3945 IF l_debug_on THEN
3946 WSH_DEBUG_SV.log(l_module_name,'Source Line Set id -'||l_delivery_details_info.source_line_set_id);
3947 END IF;
3948
3949 WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Details( l_delivery_details_info, dummy_rowid,
3950 DUMMY_DELIVERY_DETAIL_ID,
3951 l_cr_dt_status );
3952 IF (l_cr_dt_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3953 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_DET_FAILED',
3954 txt=>'Create_delivery_details failed for line: ' || ln_rec_info.line_id );
3955 WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3956 WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery details failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3957 l_booked_ln_excpn := TRUE;
3958 GOTO start_over;
3959 END IF;
3960 WSH_INTERFACE.PrintMsg(txt=>'Created dd:' || dummy_delivery_detail_id|| ' for line:'|| ln_rec_info.line_id );
3961 IF DUMMY_DELIVERY_DETAIL_ID IS NOT NULL THEN
3962 IF latest_pickup_tpdate_excep IS NOT NULL THEN
3963 WSH_TP_RELEASE.log_tpdate_exception('LINE',dummy_delivery_detail_id,TRUE,l_delivery_details_info.earliest_pickup_date,latest_pickup_tpdate_excep);
3964 END IF;
3965 IF latest_dropoff_tpdate_excep IS NOT NULL THEN
3966 WSH_TP_RELEASE.log_tpdate_exception('LINE',dummy_delivery_detail_id,FALSE,l_delivery_details_info.earliest_dropoff_date,latest_dropoff_tpdate_excep);
3967 END IF;
3968 END IF;
3969 /* LG OPM for OM changes */
3970 /* OPM assign the new delivery_detail_id to the trans that associated with this order line */
3971 -- HW OPMCONV. Removed code forking
3972
3973 -- Call default container after create a new delivery detail to populate
3974 -- default container
3975 WSH_INTERFACE.Default_Container(dummy_delivery_detail_id, l_return_status);
3976 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3977 WSH_INTERFACE.PrintMsg(txt=>'Default container failed for dd:'||dummy_delivery_detail_id);
3978 END IF;
3979
3980 l_delivery_assignments_info.delivery_id := NULL;
3981 l_delivery_assignments_info.parent_delivery_id := NULL;
3982 l_delivery_assignments_info.delivery_detail_id := dummy_delivery_detail_id;
3983 l_delivery_assignments_info.parent_delivery_detail_id := NULL;
3984
3985 WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Assignments( l_delivery_assignments_info,
3986 dummy_assgn_rowid,
3987 DUMMY_DELIVERY_ASSIGNMENT_ID,
3988 l_cr_as_status);
3989 IF (l_cr_as_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3990 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_AS_FAILED',
3991 txt=>'Create Delivery assignments failed for order line:' || ln_rec_info.line_id);
3992 WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3993 WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery assignments failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3994 l_booked_ln_excpn := TRUE;
3995 GOTO start_over;
3996 END IF;
3997 WSH_INTERFACE.PrintMsg(txt=>'Created da:' || DUMMY_DELIVERY_ASSIGNMENT_ID|| ' for dd:'|| l_delivery_assignments_info.delivery_detail_id);
3998
3999 --OTM R12, calling delivery detail splitter
4000 IF (l_gc3_is_installed = 'Y') THEN
4001
4002 WSH_INTERFACE.PrintMsg(txt=>'Delivery Detail Splitter Data:');
4003 WSH_INTERFACE.PrintMsg(txt=>'delivery detail id: '||dummy_delivery_detail_id);
4004 WSH_INTERFACE.PrintMsg(txt=>'inventory item id: '||l_delivery_details_info.inventory_item_id);
4005 WSH_INTERFACE.PrintMsg(txt=>'net weight: '|| l_delivery_details_info.net_weight);
4006 WSH_INTERFACE.PrintMsg(txt=>'organization id: '||l_delivery_details_info.organization_id);
4007 WSH_INTERFACE.PrintMsg(txt=>'weight uom code: '||l_delivery_details_info.weight_uom_code);
4008 WSH_INTERFACE.PrintMsg(txt=>'requested quantity: '||l_delivery_details_info.requested_quantity);
4009 WSH_INTERFACE.PrintMsg(txt=>'ship from location id: '||l_delivery_details_info.ship_from_location_id);
4010 WSH_INTERFACE.PrintMsg(txt=>'requested_quantity_uom: '||l_delivery_details_info.requested_quantity_uom);
4011
4012 --prepare table of delivery detail information to call splitter
4013 l_delivery_detail_tab(1) := WSH_ENTITY_INFO_REC(
4014 dummy_delivery_detail_id,
4015 NULL,
4016 l_delivery_details_info.inventory_item_id,
4017 l_delivery_details_info.net_weight,
4018 0,
4019 l_delivery_details_info.organization_id,
4020 l_delivery_details_info.weight_uom_code,
4021 l_delivery_details_info.requested_quantity,
4022 l_delivery_details_info.ship_from_location_id,
4023 NULL);
4024 l_item_quantity_uom_tab(1) := l_delivery_details_info.requested_quantity_uom;
4025
4026 IF l_debug_on THEN
4027 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split',WSH_DEBUG_SV.C_PROC_LEVEL);
4028 END IF;
4029
4030 WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split(
4031 p_detail_tab => l_delivery_detail_tab,
4032 p_item_quantity_uom_tab => l_item_quantity_uom_tab,
4033 x_return_status => l_return_status);
4034
4035 IF l_debug_on THEN
4036 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split: ' || l_return_status);
4037 END IF;
4038
4039 -- we will not fail based on l_return_status here because
4040 -- we do not want to stop the flow
4041 -- if the detail doesn't split, it will be caught later in
4042 -- delivery splitting and will have exception on the detail
4043 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4044 WSH_INTERFACE.PrintMsg(txt=>'Delivery detail split failed for dd:'||dummy_delivery_detail_id);
4045 END IF;
4046
4047 END IF;
4048 --END OTM R12
4049
4050
4051 MO_GLOBAL.set_policy_context('S', ln_rec_info.org_id);
4052
4053 oe_globals.set_context;
4054 /* Replace process order with update_shipping_interface API for better
4055 performance
4056 */
4057 IF l_debug_on THEN
4058 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE',WSH_DEBUG_SV.C_PROC_LEVEL);
4059 END IF;
4060 --
4061 OE_Shipping_Integration_PUB.Update_Shipping_Interface(
4062 p_api_version_number => 1.0,
4063 p_line_id => ln_rec_info.line_id,
4064 p_shipping_interfaced_flag => 'Y',
4065 x_return_status => l_return_status,
4066 x_msg_count => l_msg_count,
4067 x_msg_data => l_msg_data);
4068
4069
4070 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) or (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
4071 THEN
4072 --
4073 IF l_debug_on THEN
4074 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE RETURNED ' || L_RETURN_STATUS );
4075 END IF;
4076 --
4077 WSH_INTERFACE.PrintMsg(name=>'WSH_DET_PROCESS_ORDER_FAILED',
4078 txt=>'Update_Shipping_Interface failed for line:' || ln_rec_info.line_id );
4079 WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
4080 WSH_INTERFACE.PrintMsg(txt=>'no. of OE messages :'||l_msg_count);
4081 FOR k IN 1 .. l_msg_count LOOP
4082 l_msg_data := OE_MSG_PUB.GET( p_msg_index => k, p_encoded => 'F');
4083 --
4084 IF l_debug_on THEN
4085 WSH_DEBUG_SV.log(l_module_name,'l_msg_data',SUBSTR(l_msg_data,1,200));
4086 END IF;
4087 --
4088 WSH_INTERFACE.PrintMsg(txt=>substr(l_msg_data,1,255));
4089 END LOOP;
4090 -- Added for Bug-2876707
4091 l_booked_ln_excpn := TRUE;
4092 GOTO Start_Over;
4093 ELSE
4094 --
4095 IF l_debug_on THEN
4096 WSH_DEBUG_SV.logmsg(l_module_name, 'OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE RETURNED ' || L_RETURN_STATUS );
4097 END IF;
4098 --
4099 l_wf_source_header_id := l_delivery_details_info.source_header_id;
4100 l_wf_source_code := l_delivery_details_info.source_code;
4101 l_wf_order_number := l_delivery_details_info.source_header_number;
4102 IF (l_delivery_details_info.ship_to_contact_id is not null) THEN
4103 l_wf_contact_type := 'SHIP_TO';
4104 l_wf_contact_id := l_delivery_details_info.ship_to_contact_id;
4105 ELSIF (l_delivery_details_info.sold_to_contact_id is not null) then
4106 l_wf_contact_type := 'SOLD_TO';
4107 l_wf_contact_id := l_delivery_details_info.sold_to_contact_id;
4108 ELSE
4109 l_wf_contact_type := 'CUSTOMER';
4110 l_wf_contact_id := l_delivery_details_info.customer_id;
4111 END IF;
4112
4113 --
4114 IF l_debug_on THEN
4115 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_SOURCE_HEADER_ID : ' || L_WF_SOURCE_HEADER_ID );
4116 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_SOURCE_CODE : ' || L_WF_SOURCE_CODE );
4117 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_ORDER_NUMBER : ' || L_WF_ORDER_NUMBER );
4118 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_CONTACT_TYPE : ' || L_WF_CONTACT_TYPE );
4119 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_CONTACT_ID : ' || L_WF_CONTACT_ID );
4120 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF.START_WORKFLOW',WSH_DEBUG_SV.C_PROC_LEVEL);
4121 END IF;
4122 --
4123 WSH_WF.Start_Workflow( l_wf_source_header_id, l_wf_source_code,
4124 l_wf_order_number, l_wf_contact_type,
4125 l_wf_contact_id, l_result);
4126
4127 IF (l_result = TRUE) THEN
4128 --
4129 IF l_debug_on THEN
4130 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_WF.START_WORKFLOW : RETURNED TRUE' );
4131 END IF;
4132 --
4133 WSH_INTERFACE.PrintMsg(txt=>'Started workflow for '||l_wf_source_header_id||'-'||l_wf_source_code||'-'||
4134 l_wf_contact_type||'-'||l_wf_contact_id);
4135 ELSE
4136 --
4137 IF l_debug_on THEN
4138 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_WF.START_WORKFLOW : DID NOT RETURNED TRUE' );
4139 END IF;
4140 --
4141 WSH_INTERFACE.PrintMsg(txt=>'Did not Start workflow for '||l_wf_source_header_id||'-'||l_wf_source_code||'-'||
4142 l_wf_contact_type||'-'||l_wf_contact_id);
4143 END IF;
4144 --
4145 IF l_debug_on THEN
4146 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING WSH_WF.START_WORKFLOW ' );
4147 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_SOURCE_HEADER_ID : ' || L_WF_SOURCE_HEADER_ID );
4148 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_SOURCE_CODE : ' || L_WF_SOURCE_CODE );
4149 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_ORDER_NUMBER : ' || L_WF_ORDER_NUMBER );
4150 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_CONTACT_TYPE : ' || L_WF_CONTACT_TYPE );
4151 WSH_DEBUG_SV.logmsg(l_module_name, 'L_WF_CONTACT_ID : ' || L_WF_CONTACT_ID );
4152 END IF;
4153 --
4154 END IF;
4155
4156 -- Bug: 1902176
4157 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4158 CLOSE C_OE_LINES_REC_LOCK;
4159 END IF;
4160 EXCEPTION
4161 -- 1902176: Handling the Locked Condition
4162 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
4163 -- some one else is currently working on this line id
4164 --
4165 IF l_debug_on THEN
4166 WSH_DEBUG_SV.logmsg(l_module_name, 'IN LOCK LINE ID EXCEPTION: WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS' );
4167 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: ' || SQLERRM );
4168 END IF;
4169 --
4170 wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4171 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4172 CLOSE C_OE_LINES_REC_LOCK;
4173 END IF;
4174 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4175 when invalid_org THEN
4176 --
4177 IF l_debug_on THEN
4178 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_ORG' );
4179 END IF;
4180 --
4181 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_ORG');
4182 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4183 CLOSE C_OE_LINES_REC_LOCK;
4184 END IF;
4185 WSH_UTIL_CORE.add_message ('E',l_module_name);
4186 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4187 when invalid_cust_site THEN
4188 --
4189 IF l_debug_on THEN
4190 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_CUST_SITE' );
4191 END IF;
4192 --
4193 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_SITE');
4194 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4195 CLOSE C_OE_LINES_REC_LOCK;
4196 END IF;
4197 WSH_UTIL_CORE.add_message ('E',l_module_name);
4198 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4199 when invalid_qty_or_uom THEN
4200 --
4201 IF l_debug_on THEN
4202 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_QTY_OR_UOM' );
4203 END IF;
4204 --
4205 fnd_message.set_name('WSH','WSH_QTY_OR_UOM_NOT_VALID');
4206 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4207 CLOSE C_OE_LINES_REC_LOCK;
4208 END IF;
4209 WSH_UTIL_CORE.add_message ('E',l_module_name);
4210 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4211 when process_order_failed THEN
4212 --
4213 IF l_debug_on THEN
4214 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: PROCESS_ORDER_FAILED' );
4215 END IF;
4216 --
4217 fnd_message.set_name('WSH', 'WSH_DET_PROCESS_ORDER_FAILED');
4218 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4219 CLOSE C_OE_LINES_REC_LOCK;
4220 END IF;
4221 WSH_UTIL_CORE.add_message ('E',l_module_name);
4222 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4223 when create_details_failed THEN
4224 --
4225 IF l_debug_on THEN
4226 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION:CREATE_DETAILS_FAILED' );
4227 END IF;
4228 --
4229 fnd_message.set_name('WSH', 'WSH_DET_CREATE_DET_FAILED');
4230 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4231 CLOSE C_OE_LINES_REC_LOCK;
4232 END IF;
4233 WSH_UTIL_CORE.add_message ('E',l_module_name);
4234 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4235 when create_assignments_failed THEN
4236 --
4237 IF l_debug_on THEN
4238 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: CREATE_ASSIGNMENTS_FAILED' );
4239 END IF;
4240 --
4241 fnd_message.set_name('WSH', 'WSH_DET_CREATE_AS_FAILED');
4242 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4243 CLOSE C_OE_LINES_REC_LOCK;
4244 END IF;
4245 WSH_UTIL_CORE.add_message ('E',l_module_name);
4246 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4247 WHEN invalid_ship_set THEN -- bug 2373131
4248 --
4249 IF l_debug_on THEN
4250 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID SHIP SET' );
4251 END IF;
4252 --
4253 fnd_message.set_name('WSH', 'WSH_INVALID_SET');
4254 fnd_message.set_token('SHIP_SET',l_ship_set_name);
4255 fnd_message.set_token('LINE_NUMBER',ln_rec_info.line_number);
4256 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4257 CLOSE C_OE_LINES_REC_LOCK;
4258 END IF;
4259 WSH_UTIL_CORE.add_message ('E',l_module_name);
4260 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4261
4262 -- HW OPM BUG#:2677054
4263 -- HW OPMCONV. Removed OPM exceptions
4264
4265 WHEN OTHERS THEN
4266 --
4267 IF l_debug_on THEN
4268 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: OTHERS ' || SQLERRM );
4269 END IF;
4270 --
4271 wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4272 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4273 CLOSE C_OE_LINES_REC_LOCK;
4274 END IF;
4275 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4276 END; -- Lock Check Block (1902176)
4277 END IF; --} --Bugfix 6740363
4278
4279 END LOOP;
4280 IF (C_PULL_DELIVERY_DETAILS%ISOPEN) THEN
4281 CLOSE C_PULL_DELIVERY_DETAILS;
4282 END IF;
4283 IF (C_PULL_ONE_LINE%ISOPEN) THEN
4284 CLOSE C_PULL_ONE_LINE;
4285 END IF;
4286 --
4287 IF l_debug_on THEN
4288 WSH_DEBUG_SV.log(l_module_name, 'l_pull_lines_count',l_pull_lines_count);
4289 END IF;
4290
4291 IF (l_pull_lines_count = 0 and p_source_line_id IS NOT NULL) THEN
4292 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4293
4294 fnd_message.set_name('WSH', 'WSH_DET_CREATE_LINE_FAILED');
4295 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
4296
4297
4298 IF l_debug_on THEN
4299 WSH_DEBUG_SV.logmsg(l_module_name, 'Cursor C_PULL_DELIVERY_DETAIL DID NOT FOUND ANY LINES');
4300 END IF;
4301 END IF;
4302
4303
4304 IF l_debug_on THEN
4305 WSH_DEBUG_SV.pop(l_module_name);
4306 END IF;
4307 --
4308 EXCEPTION
4309 -- 2680026
4310 when create_details_failed THEN
4311 --
4312 IF l_debug_on THEN
4313 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION:CREATE_DETAILS_FAILED' );
4314 END IF;
4315 --
4316 fnd_message.set_name('WSH', 'WSH_DET_CREATE_DET_FAILED');
4317 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4318 CLOSE C_OE_LINES_REC_LOCK;
4319 END IF;
4320 IF (C_PULL_DELIVERY_DETAILS%ISOPEN) THEN
4321 CLOSE C_PULL_DELIVERY_DETAILS;
4322 END IF;
4323 IF (C_PULL_ONE_LINE%ISOPEN) THEN
4324 CLOSE C_PULL_ONE_LINE;
4325 END IF;
4326 WSH_UTIL_CORE.add_message ('E',l_module_name);
4327 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4328 when others THEN
4329 wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4330 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4331 --
4332 IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4333 CLOSE C_OE_LINES_REC_LOCK;
4334 END IF;
4335 IF (C_PULL_DELIVERY_DETAILS%ISOPEN) THEN
4336 CLOSE C_PULL_DELIVERY_DETAILS;
4337 END IF;
4338 IF (C_PULL_ONE_LINE%ISOPEN) THEN
4339 CLOSE C_PULL_ONE_LINE;
4340 END IF;
4341 --
4342 IF l_debug_on THEN
4343 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4345 END IF;
4346 --
4347 END Import_Delivery_Details;
4348
4349
4350 END WSH_USA_ACTIONS_PVT;