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