[Home] [Help]
PACKAGE BODY: APPS.INV_SHIPPING_TRANSACTION_PUB
Source
1 PACKAGE BODY INV_SHIPPING_TRANSACTION_PUB AS
2 /* $Header: INVPWSHB.pls 120.16.12020000.9 2013/05/02 05:53:05 raminoch ship $ */
3
4 G_Debug BOOLEAN := TRUE;
5
6 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.g_ret_sts_success;
7 G_RET_STS_ERROR VARCHAR2(1) := FND_API.g_ret_sts_error;
8 G_RET_STS_UNEXP_ERROR VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
9 G_FALSE VARCHAR2(1) := FND_API.G_FALSE;
10 G_TRUE VARCHAR2(1) := FND_API.G_TRUE;
11
12 --Inline branching
13 g_wms_current_release_level NUMBER := wms_control.g_current_release_level;
14 g_inv_current_release_level NUMBER := inv_control.g_current_release_level;
15 g_j_release_level NUMBER := inv_release.g_j_release_level;
16
17
18 PROCEDURE DEBUG(p_message IN VARCHAR2,
19 p_module IN VARCHAR2) IS
20 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21 BEGIN
22 if( G_Debug = TRUE ) then
23 -- inv_debug.message('wshtxn', p_message);
24 IF (l_debug = 1) THEN
25 inv_trx_util_pub.trace(p_message, 'SHPTRX.'||p_module, 1);
26 END IF;
27 -- inv_pick_wave_pick_confirm_pub.tracelog(p_message, 'SHPTRX.'||p_module);
28 -- dbms_output.put_line(p_message);
29 -- null;
30 end if;
31 END;
32
33
34 --transportation enhancement for patchset I only
35 --customer will need to be on shipping's I code
36 --check whether ship method can be used to ship the delivery
37 PROCEDURE validate_ship_method(p_shipmethod_code IN VARCHAR2,
38 p_delivery_id IN NUMBER,
39 x_return_status OUT nocopy VARCHAR2,
40 x_msg_count OUT nocopy NUMBER,
41 x_msg_data OUT nocopy varchar2) IS
42
43 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44
45 l_entity_table WSH_FTE_COMP_CONSTRAINT_GRP.wshfte_ccin_tab_type;
46 l_return_status VARCHAR2(1);
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(2000);
49 l_fte_install_status VARCHAR2(30);
50 l_industry VARCHAR2(30);
51 l_install_return_val BOOLEAN;
52
53 l_details VARCHAR2(4000);
54
55 BEGIN
56 x_return_status := FND_API.g_ret_sts_success;
57
58 IF l_debug = 1 THEN
59 debug('Start of validate_ship_method','VALIDATE_SHIP_METHOD');
60 END IF;
61
62 --check if FTE installed; only need to validate if FTE installed
63 inv_check_product_install.check_fte_installed(x_fte_installed => l_fte_install_status
64 , x_industry => l_industry
65 , x_return_status => l_return_status
66 , x_msg_count => l_msg_count
67 , x_msg_data =>
68 l_msg_data);
69 IF (l_fte_install_status = 'I') THEN
70 IF l_debug = 1 THEN
71 debug('delivery_id : ' || p_delivery_id, 'VALIDATE_SHIP_METHOD');
72 debug('shipMethod code: ' || p_shipmethod_code, 'VALIDATE_SHIP_METHOD');
73 END IF;
74
75 --query the fields needed to pass into
76 --wsh_fte_comp_constraint_grp.validate_constraint
77 SELECT organization_id,
78 customer_id,
79 initial_pickup_location_id,
80 ultimate_dropoff_location_id,
81 intmed_ship_to_location_id,
82 planned_flag,
83 status_code
84 INTO
85 l_entity_table(1).p_organization_id,
86 l_entity_table(1).p_customer_id,
87 l_entity_table(1).p_ship_from_location_id,
88 l_entity_table(1).p_ship_to_location_id,
89 l_entity_table(1).p_intmed_location_id,
90 l_entity_table(1).p_planned_flag,
91 l_entity_table(1).p_status_code
92 FROM wsh_new_deliveries_ob_grp_v wnd
93 WHERE wnd.delivery_id = p_delivery_id;
94
95 --for validation, 'UPDATE' is the action code
96 l_entity_table(1).p_action_code := wsh_fte_comp_constraint_grp.g_action_update;
97 l_entity_table(1).p_entity_type := wsh_fte_comp_constraint_grp.g_delivery;
98 l_entity_table(1).p_entity_id := p_delivery_id;
99 l_entity_table(1).p_shipmethod_code := p_shipmethod_code;
100
101 wsh_fte_comp_constraint_grp.validate_constraint(p_api_version_number => 1.0,
102 p_init_msg_list => FND_API.G_TRUE,
103 p_entity_tab => l_entity_table,
104 x_msg_count => l_msg_count,
105 x_msg_data => l_msg_data,
106 x_return_status => l_return_status);
107
108 IF l_debug = 1 THEN
109 debug('Validate constraint returned with status: ' || l_return_status,'VALIDATE_SHIP_METHOD');
110 debug('Message count is : ' || l_msg_count,'VALIDATE_SHIP_METHOD');
111 END IF;
112
113 --treating warnings as errors also
114 IF l_return_status <> WSH_UTIL_CORE.g_ret_sts_success THEN
115 l_return_status := FND_API.g_ret_sts_error;
116 wsh_util_core.get_messages(
117 p_init_msg_list => 'Y',
118 x_summary => l_msg_data,
119 x_details => l_details,
120 x_count => l_msg_count);
121
122 IF l_debug = 1 THEN
123 debug('message from wsh_util_core.get_messages: ' || l_msg_data, 'VALIDATE_SHIP_METHOD');
124 debug('l_msg_count : ' || l_msg_count,
125 'VALIDATE_SHIP_METHOD');
126 END IF;
127 END IF;
128
129 x_msg_data := l_msg_data;
130 x_msg_count := l_msg_count;
131 x_return_status := l_return_status;
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF l_debug=1 THEN
136 debug('Unexpected error!', 'VALIDATE_SHIP_METHOD');
137 END IF;
138 x_return_status := FND_API.g_ret_sts_unexp_error;
139 END VALIDATE_SHIP_METHOD;
140 --
141
142 FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2)
143 RETURN VARCHAR2 IS
144 l_ship_method_meaning VARCHAR2(80);
145 BEGIN
146 if p_ship_method_code is null then
147 return '';
148 else
149 select meaning
150 into l_ship_method_meaning
151 from fnd_lookup_values_vl
152 where lookup_type = 'SHIP_METHOD'
153 and view_application_id = 3
154 and lookup_code = p_ship_method_code;
155 end if;
156 return l_ship_method_meaning;
157 EXCEPTION
158 WHEN OTHERS THEN
159 return '';
160 END GET_SHIPMETHOD_MEANING;
161
162 PROCEDURE GET_VALID_DELIVERY(x_deliveryLOV OUT NOCOPY t_genref,
163 p_delivery_name IN VARCHAR2,
164 p_organization_id IN NUMBER) IS
165 BEGIN
166 --inv_debug.message('ssia', 'get_valid_delivery');
167 OPEN x_deliveryLOV for
168 SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
169 wnd.waybill,
170 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
171 FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd
172 WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
173 AND wda.delivery_id = wnd.delivery_id
174 and ( wdd.released_status = 'Y'
175 OR (wdd.released_status='X' and wdd.source_code='RTV') --RTV Change 16197273
176 OR ( wdd.released_status = 'X' and
177 exists (select 1
178 from mtl_system_items_b msi
179 where msi.organization_id = wdd.organization_id
180 and msi.inventory_item_id = wdd.inventory_item_id
181 and msi.mtl_transactions_enabled_flag = 'N')) -- for nontransactable items
182 )
183 and wdd.organization_id = p_organization_id
184 and wnd.name like (p_delivery_name)
185 AND status_code not in ('CO', 'CL', 'IT');
186 END GET_VALID_DELIVERY;
187
188 PROCEDURE GET_VALID_DELIVERY_VIA_LPN(x_deliveryLOV OUT NOCOPY t_genref,
189 p_delivery_name IN VARCHAR2,
190 p_organization_id IN NUMBER,
191 p_lpn_id IN NUMBER) IS
192 BEGIN
193
194 IF (p_lpn_id = 0) THEN
195 OPEN x_deliveryLOV for
196 SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
197 wnd.waybill,
198 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
199 FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
200 WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
201 AND wda.delivery_id = wnd.delivery_id
202 and wdd.organization_id = p_organization_id
203 and wnd.name like (p_delivery_name);
204
205
206 ELSE
207 OPEN x_deliveryLOV for
208 /* Commented for the Bug#4331183
209 * This query was a three level nested query, replaced the lowest level of
210 * nesting with a join clause.
211 select wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
212 wnd.waybill,
213 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
214 from wsh_new_deliveries_ob_grp_v wnd
215 where wnd.delivery_id IN -- bug 2326192
216 ( select wda.delivery_id
217 from wsh_delivery_assignments_v wda
218 where parent_delivery_detail_id =
219 ( select wdd.delivery_detail_id
220 from wsh_delivery_details_ob_grp_v wdd
221 where wdd.lpn_id = p_lpn_id
222 and wdd.organization_id = p_organization_id
223 )
224 )
225 and wnd.name like (p_delivery_name);
226 */
227 -- Added for Bug#4331183
228 -- Replaced nested sub query by a join condition
229 SELECT wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
230 wnd.waybill,
231 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
232 FROM wsh_new_deliveries wnd
233 WHERE wnd.delivery_id IN
234 ( SELECT wda.delivery_id
235 FROM wsh_delivery_details wdd,
236 wsh_delivery_assignments wda
237 WHERE wdd.lpn_id = p_lpn_id
238 AND wdd.organization_id = p_organization_id
239 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
240 -- AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650 -- Commented to display the closed deliveries for bug#13990462
241 )
242 AND wnd.name like (p_delivery_name);
243 END IF;
244 END GET_VALID_DELIVERY_VIA_LPN;
245
246
247
248 PROCEDURE GET_VALID_DELIVERY_LINE(x_deliveryLineLOV OUT NOCOPY t_genref,
249 p_delivery_id IN NUMBER,
250 p_inventory_item_id IN NUMBER) IS
251 BEGIN
252 OPEN x_deliveryLineLOV for
253 SELECT wdd.delivery_detail_id
254 FROM wsh_delivery_details_ob_grp_v wdd,
255 wsh_delivery_assignments_v wda,
256 wsh_new_deliveries_ob_grp_v wnd
257 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
258 AND wda.delivery_id = wnd.delivery_id
259 AND wnd.delivery_id = p_delivery_id
260 AND wdd.inventory_item_id = nvl(p_inventory_item_id, wdd.inventory_item_id)
261 AND wdd.released_status = 'Y';
262 END GET_VALID_DELIVERY_LINE;
263
264 PROCEDURE GET_VALID_CARRIER(x_carrierLOV OUT NOCOPY t_genref,
265 p_carrier_name IN VARCHAR2) IS
266 BEGIN
267 /*
268 OPEN x_carrierLOV for
269 SELECT distinct PV.vendor_name, WCSM.carrier_id,
270 WCSM.ship_method_code
271 FROM WSH_CARRIER_SHIP_METHODS_V WCSM,
272 PO_VENDORS PV
273 WHERE PV.vendor_name like (p_carrier_name)
274 AND WCSM.carrier_id is not null
275 AND PV.vendor_id = WCSM.carrier_id;
276 */
277 null;
278 END GET_VALID_CARRIER;
279
280 PROCEDURE GET_SHIP_METHOD_LOV(x_shipMethodLOV OUT NOCOPY t_genref,
281 p_organization_id IN NUMBER,
282 p_ship_method_name IN VARCHAR2) IS
283 BEGIN
284 OPEN x_shipMethodLOV for
285 select
286 meaning,
287 description,
288 lookup_code ship_method_code
289 from fnd_lookup_values_vl flv
290 where lookup_type = 'SHIP_METHOD'
291 and view_application_id = 3
292 and nvl(start_date_active,sysdate)<=sysdate
293 AND nvl(end_date_active,sysdate)>=sysdate
294 AND enabled_flag = 'Y'
295 AND meaning like ( p_ship_method_name)
296 AND lookup_code in (select ship_method_code
297 from wsh_carrier_services wcs, wsh_org_carrier_services wocs,
298 wsh_carriers wc
299 where wocs.organization_id = p_organization_id
300 AND wcs.ship_method_code = flv.lookup_code
301 AND wcs.enabled_flag = 'Y'
302 AND wocs.enabled_flag = 'Y'
303 AND wcs.carrier_service_id = wocs.carrier_service_id
304 and wcs.carrier_id = wc.carrier_id
305 AND NVL(wc.generic_flag, 'N') = 'N')
306 order by meaning;
307 END GET_SHIP_METHOD_LOV;
308
309 PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
310 p_delivery_id IN NUMBER) IS
311 BEGIN
312 open x_delivery_info for
313 SELECT wnd.name, wnd.delivery_id, nvl(wnd.gross_weight, 0), wnd.weight_uom_code,
314 wnd.waybill,' ',
315 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
316 FROM wsh_new_deliveries_ob_grp_v wnd
317 WHERE wnd.delivery_id = p_delivery_id;
318 END GET_DELIVERY_INFO;
319
320
321 PROCEDURE INV_DELIVERY_LINE_INFO(x_deliveryLineInfo OUT NOCOPY t_genref,
322 p_delivery_id IN NUMBER,
323 p_inventory_item_id IN NUMBER,
324 p_serial_flag IN VARCHAR2,
325 x_return_Status OUT NOCOPY VARCHAR2) IS
326 BEGIN
327 /** ssia 10/17/2002 Add nvl(transaction_temp_id, 0) in the select statement
328 For serial - shipping enhancement project
329 **/
330 /*Bug#5612236. In the below queries, replaced 'MTL_SYSTEM_ITEMS_KFV' with
331 'MTL_SYSTEM_ITEMS_VL'.*/
332 x_return_Status := 'C';
333 if( p_serial_flag = 'N' ) then
334 OPEN x_deliveryLineInfo FOR
335 SELECT ' ',del.name delivery_name, dd.delivery_detail_id,
336 dd.inventory_item_id,msiv.concatenated_segments, msiv.description,
337 dd.requested_quantity, dd.requested_quantity_uom,
338 dd.serial_number, del.waybill, Nvl(msiv.serial_number_control_code, 1),
339 dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
340 nvl(dd.transaction_temp_id,0),
341 --3348813
342 --Adding picked_quantity as part of the return cursor.
343 dd.picked_quantity,
344 dd.shipped_quantity,
345 --Bug 3952081
346 --add DUOM values
347 REQUESTED_QUANTITY_UOM2,
348 REQUESTED_QUANTITY2,
349 PICKED_QUANTITY2,
350 SHIPPED_QUANTITY2
351 FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
352 wsh_delivery_assignments_v da, mtl_system_items_vl msiv
353 WHERE da.delivery_id = del.delivery_id
354 AND da.delivery_detail_id = dd.delivery_detail_id
355 AND ( dd.inventory_item_id = p_inventory_item_id or p_inventory_item_id = -1 )
356 AND NVL( dd.inv_interfaced_flag, 'N') = 'N'
357 AND dd.released_status = 'Y'
358 AND del.delivery_id = p_delivery_id
359 AND msiv.inventory_item_id(+) = dd.inventory_item_id
360 AND msiv.organization_id(+) = dd.organization_id
361 ORDER BY dd.subinventory,dd.locator_id, msiv.concatenated_segments;
362
363 else
364 OPEN x_deliveryLineInfo FOR
365 SELECT ' ',del.name delivery_name, dd.delivery_detail_id, dd.inventory_item_id,
366 msiv.concatenated_segments, msiv.description,
367 dd.requested_quantity, dd.requested_quantity_uom,
368 dd.serial_number, del.waybill, Nvl(msiv.serial_number_control_code, 1),
369 dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
370 nvl(dd.transaction_temp_id,0),
371 --3348813
372 --Adding picked_quantity as part of the return cursor.
373 dd.picked_quantity,
374 dd.shipped_quantity
375 FROM wsh_new_deliveries_ob_grp_v del,
376 wsh_delivery_details_ob_grp_v dd,
377 wsh_delivery_assignments_v da,
378 mtl_system_items_vl msiv
379 WHERE da.delivery_id = del.delivery_id
380 AND da.delivery_detail_id = dd.delivery_detail_id
381 AND ( dd.inventory_item_id = p_inventory_item_id
382 or p_inventory_item_id = -1 )
383 AND NVL( dd.inv_interfaced_flag, 'N') = 'N'
384 AND dd.released_status = 'Y'
385 AND del.delivery_id = p_delivery_id
386 AND msiv.inventory_item_id(+) = dd.inventory_item_id
387 AND msiv.organization_id(+) = dd.organization_id
388 AND msiv.serial_number_control_code = 6
389 ORDER BY dd.subinventory, dd.locator_id,msiv.concatenated_segments;
390
391 end if;
392 EXCEPTION
393 when others then
394 x_return_Status := 'E';
395 END INV_DELIVERY_LINE_INFO;
396
397 PROCEDURE SERIAL_AT_SALES_CHECK(x_result OUT NOCOPY NUMBER,
398 x_item_name OUT NOCOPY VARCHAR2,
399 p_delivery_id IN NUMBER)
400 IS
401 l_item_name VARCHAR2(40);
402 all_items VARCHAR2(20000) := NULL;
403 cursor item_name is
404 select msik.concatenated_segments
405 from wsh_new_deliveries_ob_grp_v del,
406 wsh_delivery_details_ob_grp_v dd,
407 wsh_delivery_assignments_v da,
408 mtl_system_items_kfv msik
409 where da.delivery_id = del.delivery_id
410 AND da.delivery_detail_id = dd.delivery_detail_id
411 AND del.delivery_id = p_delivery_id
412 AND msik.inventory_item_id(+) = dd.inventory_item_id
413 AND msik.organization_id(+) = dd.organization_id
414 AND msik.serial_number_control_code = 6;
415 BEGIN
416 OPEN item_name;
417 loop
418 FETCH item_name into l_item_name;
419 EXIT WHEN item_name%NOTFOUND;
420 if all_items is null then
421 all_items := l_item_name;
422 else
423 all_items := all_items||', '||l_item_name;
424 end if;
425 end loop;
426 CLOSE item_name;
427 if all_items is null then
428 x_result := 0;
429 else
430 x_result := 1;
431 x_item_name := all_items;
432 end if;
433 EXCEPTION
434 WHEN OTHERS THEN
435 x_result := 9999;
436
437 END SERIAL_AT_SALES_CHECK;
438
439 /** add out parameter x_num_serial_record for serial shipping enhancement project **/
440 /** Dependencies: in DeliveryLineFListener.java **/
441 PROCEDURE GET_DELIVERY_LINE_SERIAL_INFO(
442 p_delivery_detail_id IN NUMBER,
443 x_return_Status OUT NOCOPY VARCHAR2,
444 x_inventory_item_id OUT NOCOPY NUMBER,
445 x_transaction_Temp_id OUT NOCOPY NUMBER,
446 x_subinventory_code OUT NOCOPY VARCHAR2,
447 x_revision OUT NOCOPY VARCHAR2,
448 x_locator_id OUT NOCOPY NUMBER,
449 x_lot_number OUT NOCOPY VARCHAR2,
450 x_num_serial_record OUT NOCOPY NUMBER
451 ) IS
452
453 l_transaction_temp_id NUMBER;
454 l_inventory_item_id NUMBER := 0;
455 l_subinventory_code VARCHAR2(30);
456 l_revision VARCHAR2(10);
457 l_locator_id NUMBER;
458 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
459 l_lot_number VARCHAR2(80);
460 l_serial_number VARCHAR2(30);
461 l_num_serial_record NUMBER := 0;
462
463 l_detail_attributes wsh_interface.ChangedAttributeTabType;
464 l_InvPCInRecType wsh_integration.InvPCInRecType;
465 l_return_status VARCHAR2(1);
466 l_msg_count NUMBER;
467 l_msg_data VARCHAR2(2000);
468
469 l_picked_quantity NUMBER := 0;
470 BEGIN
471 --initalizing l_InvPCInRecType to use for updating wdd with transaction_temp_id
472 l_InvPCInRecType.transaction_id := NULL;
473 l_InvPCInRecType.transaction_temp_id := NULL;
474 l_InvPCInRecType.source_code :='INV';
475 l_InvPCInRecType.api_version_number :=1.0;
476
477 x_return_Status := 'C';
478 select inventory_item_id, subinventory, revision, locator_id,lot_number, transaction_temp_id, serial_number,picked_quantity
479 into l_inventory_item_id, l_subinventory_code, l_revision, l_locator_id, l_lot_number,
480 l_transaction_temp_id, l_serial_number,l_picked_quantity
481 from wsh_delivery_details_ob_grp_v
482 where delivery_detail_id = p_delivery_detail_id;
483
484 IF ( l_serial_number IS NULL ) THEN
485 IF ( l_transaction_temp_id IS NULL ) THEN
486 select mtl_material_Transactions_s.nextval
487 into l_InvPCInRecType.transaction_temp_id
488 from dual;
489
490 l_transaction_temp_id := l_InvPCInRecType.transaction_temp_id;
491
492 debug('About to call wsh_integration.Set_Inv_PC_Attributes tempid='||l_transaction_temp_id, 'GET_DELIVERY_LINE_SERIAL_INFO');
493
494 wsh_integration.Set_Inv_PC_Attributes
495 (p_in_attributes => l_InvPCInRecType,
496 x_return_status => l_return_status,
497 x_msg_count => l_msg_count,
498 x_msg_data => l_msg_data);
499
500 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
501 debug('wsh_integration.set_inv_pc_attributes failed'
502 || ' with status: ' || l_return_status,'GET_DELIVERY_LINE_SERIAL_INFO');
503 --check where to handle this error
504 RAISE fnd_api.g_exc_unexpected_error;
505 END IF;
506
507 l_detail_attributes(1).action_flag := 'U';
508 l_detail_attributes(1).delivery_detail_id :=
509 p_delivery_detail_id;
510 --Passing picked_quantity also because wsh_interface.update_shipping_attributes
511 --will null it out if we do not
512 l_detail_attributes(1).picked_quantity := l_picked_quantity;
513
514 debug('About to call wsh_interface.update_shipping_attributes',
515 'GET_DELIVERY_LINE_SERIAL_INFO');
516 debug('picked_quantity: ' || l_picked_quantity,'GET_DELIVERY_LINE_SERIAL_INFO');
517
518 wsh_interface.update_shipping_attributes
519 (x_return_status => l_return_status,
520 p_changed_attributes => l_detail_attributes,
521 p_source_code => 'INV');
522
523 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
524 debug('wsh_interface.update_shipping_attributes failed'
525 || ' with status: ' || l_return_status,'GET_DELIVERY_LINE_SERIAL_INFO');
526 --check where to handle this error
527 RAISE fnd_api.g_exc_unexpected_error;
528 END IF;
529 ELSE
530 select count(*)
531 into l_num_serial_record
532 From mtl_serial_numbers_temp
533 where transaction_temp_id = l_transaction_temp_id;
534
535 debug('Found '||l_num_serial_record||' lines for tempid '||l_transaction_temp_id,'GET_DELIVERY_LINE_SERIAL_INFO');
536 END IF;
537 END IF;
538
539 x_inventory_item_id := l_inventory_item_id;
540 x_transaction_temp_id := l_transaction_Temp_id;
541 x_subinventory_code := l_subinventory_code;
542 x_locator_id := l_locator_id;
543 x_revision := l_revision;
544 x_lot_number := l_lot_number;
545 x_num_serial_record := l_num_serial_record;
546 EXCEPTION
547 when NO_DATA_FOUND then
548 x_return_Status := 'E';
549
550 END GET_DELIVERY_LINE_SERIAL_INFO;
551
552 PROCEDURE GET_TRIP_NAME(p_delivery_id IN NUMBER,
553 x_trip_name OUT NOCOPY VARCHAR2,
554 x_trip_id OUT NOCOPY NUMBER) IS
555 l_trip_name VARCHAR2(80);
556 l_trip_id NUMBER;
557 BEGIN
558 select trip.name, trip.trip_id
559 into l_trip_name, l_trip_id
560 from wsh_trips_ob_grp_v trip,
561 wsh_trip_stops_ob_grp_v pickup_stop,
562 wsh_trip_stops_ob_grp_v dropoff_stop,
563 wsh_delivery_legs_ob_grp_v wdl,
564 wsh_new_deliveries_ob_grp_v wnd
565 where wdl.delivery_id = wnd.delivery_id(+)
566 and wdl.delivery_id = p_delivery_id
567 and pickup_stop.stop_id = wdl.pick_up_stop_id
568 and dropoff_stop.stop_id = wdl.drop_off_stop_id
569 and pickup_stop.trip_id = trip.trip_id(+)
570 and wnd.delivery_id = p_delivery_id;
571 EXCEPTION
572 when no_data_found THEN
573 l_trip_name := 'NONE';
574 l_trip_id := -99999;
575 END GET_TRIP_NAME;
576
577 PROCEDURE GET_TRIP_LOV(x_trip_lov OUT NOCOPY t_genref,
578 p_trip_name IN VARCHAR2) IS
579 BEGIN
580 open x_trip_lov for
581 select name, trip_id, ship_method_code, carrier_id
582 from wsh_trips_ob_grp_v
583 where name like p_trip_name
584 and status_code = 'OP';
585 end GET_TRIP_LOV;
586
587 procedure get_dock_door(x_dock_door OUT NOCOPY t_genref,
588 p_trip_id IN NUMBER) IS
589 BEGIN
590 open x_dock_door for
591 select hrl.location_code
592 from wsh_trip_stops_ob_grp_v wts, hr_locations hrl
593 where wts.stop_location_id = hrl.location_id
594 and wts.trip_id = p_trip_id;
595 END get_dock_door;
596
597 procedure get_items_in_lpn(x_items OUT NOCOPY t_genref,
598 p_lpn_id IN NUMBER) IS
599 BEGIN
600 open x_items for
601 select wlpn.inventory_item_id, msik.concatenated_segments
602 from wms_license_plate_numbers wlpn, mtl_system_items_kfv msik
603 where wlpn.lpn_id = p_lpn_id
604 and wlpn.inventory_item_id = msik.inventory_item_id(+);
605 END get_items_in_lpn;
606
607
608 --Returns an entire delivery to stock. No partial shipment
609 PROCEDURE INV_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
610 x_return_status OUT NOCOPY VARCHAR2,
611 x_msg_data OUT NOCOPY VARCHAR2,
612 x_msg_count OUT NOCOPY NUMBER) IS
613
614 cursor delivery_details_ids(p_delivery_id NUMBER) is
615 select dd.delivery_detail_id
616 from wsh_delivery_assignments_v da,wsh_delivery_details_ob_grp_v dd
617 where da.delivery_id = p_delivery_id
618 and da.delivery_detail_id = dd.delivery_detail_id
619 and dd.container_flag <>'Y';
620
621 cursor lpn_csr(p_delivery_detail_id in NUMBER) is
622 select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
623 from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
624 , wsh_delivery_assignments_v wda2
625 where wdd.delivery_detail_id = wda.parent_delivery_detail_id
626 and wda.delivery_detail_id = wdd2.delivery_detail_id
627 and wdd2.delivery_detail_id = p_delivery_detail_id
628 and wda2.delivery_detail_id = wdd.delivery_detail_id;
629
630 CURSOR nested_parent_lpn_cursor(l_inner_lpn_id NUMBER) is
631 SELECT lpn_id
632 FROM WMS_LICENSE_PLATE_NUMBERS
633 START WITH lpn_id = l_inner_lpn_id
634 CONNECT BY lpn_id = PRIOR parent_lpn_id;
635
636 l_return_status VARCHAR2(1);
637 l_delivery_id NUMBER := p_delivery_id;
638 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
639
640 l_requested_quantity NUMBER;
641 l_requested_quantity2 NUMBER;
642 l_picked_quantity NUMBER;
643
644 l_out_rows WSH_UTIL_CORE.ID_TAB_TYPE;
645 l_delivery_details_id_table WSH_UTIL_CORE.ID_TAB_TYPE;
646 l_backorder_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
647 l_requested_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
648 l_overpicked_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
649 l_dummy_table wsh_util_core.id_tab_type;
650 l_table_index NUMBER := 1;
651
652 l_parent_delivery_detail_id NUMBER;
653 l_delivery_assignment_id NUMBER;
654 l_par_delivery_assignment_id NUMBER;
655 l_lpn_id NUMBER;
656
657 l_lpn_tbl WMS_Data_Type_Definitions_PUB.LPNTableType;
658 l_lpn_rec WMS_Data_Type_Definitions_PUB.LPNRecordType;
659
660 CURSOR lpn_cur(p_delivery_id NUMBER) IS
661 SELECT wdd.lpn_id, wdd.organization_id
662 FROM (SELECT delivery_detail_id
663 FROM wsh_delivery_assignments_v wda
664 WHERE wda.delivery_id = p_delivery_id ) wda
665 , wsh_delivery_details_ob_grp_v wdd
666 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
667 AND wdd.lpn_id IS NOT NULL;
668
669 BEGIN
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671 IF (l_debug = 1) THEN
672 debug('Entering the new return_to_stock ','wshtxn');
673 END IF;
674
675 OPEN delivery_details_ids(l_delivery_id);
676 LOOP
677 FETCH delivery_details_ids INTO
678 l_delivery_details_id_table(l_table_index);
679 EXIT WHEN delivery_details_ids%NOTFOUND;
680 IF (l_debug = 1) THEN
681 debug('Return to stock for delivery line '||to_char(l_delivery_details_id_table(l_table_index)),'wshtxn');
682 END IF;
683
684 select dd.requested_quantity, dd.picked_quantity,dd.requested_quantity2
685 INTO l_requested_quantity, l_picked_quantity, l_requested_quantity2
686 from wsh_delivery_details_ob_grp_v dd
687 where
688 dd.delivery_detail_id = l_delivery_details_id_table(l_table_index);
689
690 l_backorder_quantities_table(l_table_index) :=
691 l_requested_quantity;
692 l_requested_quantities_table(l_table_index) :=
693 l_requested_quantity;
694 l_dummy_table(l_table_index) := l_requested_quantity2;
695
696 IF l_picked_quantity > l_requested_quantity THEN
697 l_overpicked_quantities_table(l_table_index) :=
698 l_picked_quantity - l_requested_quantity;
699 ELSE
700 l_overpicked_quantities_table(l_table_index) := 0;
701 END IF;
702
703 -- Release 12: LPN SyncUP
704 -- In addition to the LPN context update
705 -- WDD records also need to be removed
706 -- This is done by calling wms_container_pvt.modify_lpn API
707 -- Remove the direct update here
708 -- Call modify_lpn API after backorder
709 /*open lpn_csr(l_delivery_details_id_table(l_table_index));
710 LOOP
711 fetch lpn_csr into l_parent_delivery_detail_id,l_delivery_assignment_id,
712 l_par_delivery_assignment_id;
713 exit when lpn_csr%NOTFOUND;
714
715 -- change the LPN context first since we changed the LPN context to
716 -- picked after pick confirm
717 select lpn_id
718 into l_lpn_id
719 from wsh_delivery_details_ob_grp_v
720 where delivery_detail_id = l_parent_delivery_detail_id;
721
722 IF (l_debug = 1) THEN
723 debug('Change the context of LPNs to 1:'||l_lpn_id,'wshtxn');
724 END IF;
725 -- change the LPN and parent LPN context
726 FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
727 IF (l_debug = 1) THEN
728 debug('LPN ID'||l_par_lpn_id.lpn_id,'wshtxn');
729 END IF;
730 UPDATE WMS_LICENSE_PLATE_NUMBERS
731 SET lpn_context = 1,
732 last_update_date = SYSDATE,
733 last_updated_by = FND_GLOBAL.USER_ID
734 where lpn_id = l_par_lpn_id.lpn_id;
735 END LOOP;
736 END LOOP;
737 close lpn_csr;*/
738
739 l_table_index := l_table_index + 1;
740 END LOOP;
741 CLOSE delivery_details_ids;
742
743 -- Release 12: LPN SyncUP
744 -- Populate the lpn_tbl to call modify_lpn API
745 -- Call modify_lpn API after backorder
746 l_lpn_tbl.delete;
747 FOR l_lpn IN lpn_cur(p_delivery_id) LOOP
748 l_lpn_rec.organization_id := l_lpn.organization_id;
749 l_lpn_rec.lpn_id := l_lpn.lpn_id;
750 l_lpn_rec.lpn_context := 1;
751 l_lpn_tbl(nvl(l_lpn_tbl.last, 0)+1) := l_lpn_rec;
752 IF (l_debug = 1) THEN
753 debug('Add to l_lpn_tbl with lpn_rec of org_id'||l_lpn_rec.organization_id
754 ||', lpn_id '||l_lpn_rec.lpn_id||', lpn_context '||l_lpn_rec.lpn_context, 'INV_RETURN_TO_STOCK');
755 END IF;
756 END LOOP;
757
758
759
760 IF (l_debug = 1) THEN
761 debug('calling wsh_ship_confirm_actions2.backorder','INV_RETURN_TO_STOCK');
762 END IF;
763 WSH_SHIP_CONFIRM_ACTIONS2.Backorder(p_detail_ids => l_delivery_details_id_table,
764 p_bo_qtys => l_backorder_quantities_table,
765 p_req_qtys => l_requested_quantities_table,
766 p_bo_qtys2 => l_dummy_table,
767 p_overpick_qtys => l_overpicked_quantities_table,
768 p_overpick_qtys2 => l_dummy_table,
769 p_bo_mode => 'UNRESERVE',
770 x_out_rows => l_out_rows,
771 x_return_status => l_return_status
772 );
773
774 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
775 IF (l_debug = 1) THEN
776 DEBUG('return error from shipping Backorder', 'INV_RETURN_TO_STOCK');
777 END IF;
778 RAISE fnd_api.g_exc_error;
779 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
780 IF (l_debug = 1) THEN
781 DEBUG('return error from shipping Backorder', 'INV_RETURN_TO_STOCK');
782 END IF;
783 RAISE fnd_api.g_exc_unexpected_error;
784 ELSIF l_return_status = fnd_api.g_ret_sts_success THEN
785 COMMIT;
786 END IF;
787
788
789
790 -- Release 12: LPN SyncUP
791 -- Call modify_lpn API to update lpn context to 1
792 -- and remove associated WDD lines
793 IF(l_debug = 1) THEN
794 DEBUG('Calling WMS_CONTAINER_PVT.Modify_LPNs with caller WMS_SHIPPING','INV_RETURN_TO_STOCK');
795 END IF;
796
797 WMS_CONTAINER_PVT.Modify_LPNs(
798 p_api_version => 1.0
799 , p_init_msg_list => fnd_api.g_true
800 , p_commit => fnd_api.g_false
801 , x_return_status => l_return_status
802 , x_msg_count => x_msg_count
803 , x_msg_data => x_msg_data
804 , p_caller => 'WMS_SHIPPING'
805 , p_lpn_table => l_lpn_tbl
806 );
807 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
808 IF (l_debug = 1) THEN
809 DEBUG('return error from WMS_CONTAINER_PVT.Modify_LPNs', 'INV_RETURN_TO_STOCK');
810 END IF;
811 RAISE fnd_api.g_exc_error;
812 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
813 IF (l_debug = 1) THEN
814 DEBUG('return error from WMS_CONTAINER_PVT.Modify_LPNs', 'INV_RETURN_TO_STOCK');
815 END IF;
816 RAISE fnd_api.g_exc_unexpected_error;
817 ELSIF l_return_status = fnd_api.g_ret_sts_success THEN
818 null;
819 END IF;
820 EXCEPTION
821 WHEN FND_API.G_EXC_ERROR THEN
822 x_return_status := FND_API.G_RET_STS_ERROR;
823 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
824 , p_data => x_msg_data);
825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
828 , p_data => x_msg_data);
829 WHEN OTHERS THEN
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
832 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INV_RETURN_TO_STOCK');
833 END IF;
834 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
835 , p_data => x_msg_data);
836 END INV_RETURN_TO_STOCK;
837
838
839 PROCEDURE INV_DELAY_SHIPMENT(p_delivery_id IN NUMBER,
840 p_delivery_line_id IN NUMBER,
841 p_shipped_quantity IN NUMBER,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_data OUT NOCOPY VARCHAR2,
844 x_msg_count OUT NOCOPY NUMBER) IS
845 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
846 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
847 l_details VARCHAR2(2000);
848 BEGIN
849
850 --null;
851 -- need to split the delivery line
852 -- the delivery line is split during the om interface, so
853 -- all we need to do here is to update the shipped_quantity of the delivery line
854 -- so later when shipping interface to OM, the delivery line will be split
855 l_detail_attributes(1).cycle_count_quantity := 0; /* Bug 5466481 */
856 l_detail_attributes(1).shipped_quantity := p_shipped_quantity;
857 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
858
859 IF l_debug = 1 THEN
860 debug('About to call Shipping ' ||
861 'wsh_delivery_details_pub.update_shipping_attributes', 'INV_DELAY_SHIPMENT');
862 END IF;
863
864 wsh_delivery_details_pub.update_shipping_attributes
865 (p_api_version_number => 1.0,
866 p_init_msg_list => G_TRUE,
867 p_commit => G_FALSE,
868 x_return_status => x_return_status,
869 x_msg_count => x_msg_count,
870 x_msg_data => x_msg_data,
871 p_changed_attributes => l_detail_attributes,
872 p_source_code => 'OE');
873
874 IF x_return_status <> G_RET_STS_SUCCESS THEN
875 IF l_debug = 1 THEN
876 debug('wsh_delivery_details_pub.update_shipping_attributes failed'
877 || ' with status: ' || x_return_status, 'INV_DELAY_SHIPMENT');
878 END IF;
879 RAISE fnd_api.g_exc_unexpected_error;
880 END IF;
881
882 EXCEPTION
883 WHEN fnd_api.g_exc_unexpected_error THEN
884 wsh_util_core.get_messages
885 (p_init_msg_list => 'Y',
886 x_summary => x_msg_data,
887 x_details => l_details,
888 x_count => x_msg_count);
889 when no_data_found then
890 -- put error message on the stack
891 null;
892
893 END INV_DELAY_SHIPMENT;
894
895 PROCEDURE INV_LINE_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
896 p_delivery_line_id IN NUMBER,
897 p_shipped_quantity IN NUMBER,
898 x_return_status OUT NOCOPY VARCHAR2,
899 x_msg_data OUT NOCOPY VARCHAR2,
900 x_msg_count OUT NOCOPY NUMBER,
901 p_commit_flag IN VARCHAR2 DEFAULT FND_API.g_true,
902 p_relieve_rsv IN VARCHAR2 DEFAULT 'Y')
903 IS
904 cursor delivery_line(p_delivery_detail_id NUMBER) is
905 select dd.delivery_detail_id, dd.requested_quantity, dd.picked_quantity
906 from wsh_delivery_details_ob_grp_v dd
907 WHERE dd.delivery_detail_id = p_delivery_detail_id;
908
909 cursor lpn_csr(p_delivery_detail_id in NUMBER) is
910 select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
911 from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
912 , wsh_delivery_assignments_v wda2
913 where wdd.delivery_detail_id = wda.parent_delivery_detail_id
914 and wda.delivery_detail_id = wdd2.delivery_detail_id
915 and wdd2.delivery_detail_id = p_delivery_detail_id
916 and wda2.delivery_detail_id = wdd.delivery_detail_id;
917
918 CURSOR nested_parent_lpn_cursor(l_inner_lpn_id NUMBER) is
919 SELECT lpn_id
920 FROM WMS_LICENSE_PLATE_NUMBERS
921 START WITH lpn_id = l_inner_lpn_id
922 CONNECT BY lpn_id = PRIOR parent_lpn_id;
923
924 l_delivery_details_id_table WSH_UTIL_CORE.ID_TAB_TYPE;
925 l_backorder_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
926 l_requested_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
927 l_overpicked_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
928 l_dummy_table wsh_util_core.id_tab_type;
929 l_out_rows wsh_util_core.id_tab_type;
930 l_detail_attributes wsh_delivery_details_pub.ChangedAttributeTabType;
931 l_dummy_num_var NUMBER := NULL;
932 l_table_index NUMBER := 1;
933
934 l_picked_quantity NUMBER;
935 l_parent_delivery_detail_id NUMBER;
936 l_bo_delivery_detail_id NUMBER;
937 l_delivery_assignment_id NUMBER;
938 l_par_delivery_assignment_id NUMBER;
939 l_lpn_id NUMBER;
940
941 l_more_detail NUMBER;
942
943 l_return_status VARCHAR2(1);
944 l_msg_count NUMBER;
945 l_msg_data VARCHAR2(2000);
946
947 l_bo_mode VARCHAR2(10);
948
949 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
950 l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ; --6607967 --FP6922321
951 l_return_shipping_status VARCHAR2(30); --6607967--FP6922321
952 BEGIN
953 x_return_status := fnd_api.g_ret_sts_success;
954
955 --this cursor only returns 1 record because delivery_line_id is an
956 --unique key
957
958 IF l_debug = 1 THEN
959 debug('Entered INV_LINE_RETURN_TO_STOCK ', 'INV_LINE_RETURN_TO_STOCK');
960 debug('p_delivery_line_id: ' || p_delivery_line_id, 'INV_LINE_RETURN_TO_STOCK');
961 debug('p_delivery_id: ' || p_delivery_id , 'INV_LINE_RETURN_TO_STOCK');
962 debug('p_shipped_quantity: ' ||p_shipped_quantity , 'INV_LINE_RETURN_TO_STOCK');
963 debug('p_commit_flag: ' || p_commit_flag , 'INV_LINE_RETURN_TO_STOCK');
964 debug('p_relieve_rsv: ' || p_relieve_rsv, 'INV_LINE_RETURN_TO_STOCK');
965 END IF;
966
967 OPEN delivery_line(p_delivery_line_id);
968
969 FETCH delivery_line INTO
970 l_delivery_details_id_table(1),
971 l_requested_quantities_table(1),
972 l_picked_quantity;
973
974 IF l_debug = 1 THEN
975 debug('fetched : l_delivery_details_id_table(1): '|| l_delivery_details_id_table(1), 'INV_LINE_RETURN_TO_STOCK');
976 debug('fetched : l_requested_quantities_table(1): '|| l_requested_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
977 debug('fetched : l_picked_quantity: '|| l_picked_quantity, 'INV_LINE_RETURN_TO_STOCK');
978 END IF;
979 --6607967 start --FP 6922321
980 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_shipping_status);
981 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
982 debug('ERROR WHILE FETCHING SHIPPING GLOBAL PARAMETERS l_return_shipping_status'||l_return_shipping_status, 'INV_LINE_RETURN_TO_STOCK');
983 END IF;
984
985 debug('fetched : l_global_param_rec.consolidate_bo_lines: '|| l_global_param_rec.consolidate_bo_lines, 'INV_LINE_RETURN_TO_STOCK');
986
987 IF ( l_global_param_rec.consolidate_bo_lines = 'Y' ) THEN
988 l_backorder_quantities_table(1) :=l_requested_quantities_table(1) - p_shipped_quantity;
989
990 IF l_picked_quantity > l_requested_quantities_table(1) THEN
991 l_overpicked_quantities_table(1) :=l_picked_quantity - l_requested_quantities_table(1);
992 ELSE
993 l_overpicked_quantities_table(1) := 0;
994 END IF;
995 ELSE
996 IF (l_picked_quantity > l_requested_quantities_table(1)) THEN
997 l_backorder_quantities_table(1) :=l_picked_quantity - p_shipped_quantity;
998 l_overpicked_quantities_table(1) :=l_picked_quantity - l_requested_quantities_table(1);
999 ELSE
1000 l_backorder_quantities_table(1) := l_requested_quantities_table(1) - p_shipped_quantity;
1001 l_overpicked_quantities_table(1) := 0;
1002 END IF;
1003 END IF;
1004
1005 debug('fetched : new l_requested_quantities_table(1): '|| l_requested_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
1006 debug('fetched : new l_picked_quantity: '|| l_picked_quantity, 'INV_LINE_RETURN_TO_STOCK');
1007 debug('fetched : new l_backorder_quantities_table(1): '|| l_backorder_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
1008 --6607967 end --FP 6922321 END
1009
1010 l_dummy_table(1) := NULL;
1011
1012 CLOSE delivery_line;
1013 IF l_debug = 1 THEN
1014 debug('l_overpicked_quantities_table(1): '|| l_overpicked_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
1015 debug('l_backorder_quantities_table(1): '|| l_backorder_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
1016 debug('l_requested_quantities_table(1): '|| l_requested_quantities_table(1), 'INV_LINE_RETURN_TO_STOCK');
1017 END IF;
1018
1019 IF p_shipped_quantity = 0 THEN
1020 IF l_debug = 1 THEN
1021 debug('Before lpn_csr(l_delivery_details_id_table(1)): '|| l_delivery_details_id_table(1), 'INV_LINE_RETURN_TO_STOCK');
1022 END IF;
1023
1024 -- Release 12: LPN SyncUP
1025 -- In addition to the LPN context update
1026 -- WDD records also need to be removed
1027 -- This is done by calling wms_container_pvt.modify_lpn API
1028 -- in WMS_DIRECT_SHIP_PVT.UNLOAD_TRUCK
1029 -- Remove the direct update here
1030 /*OPEN lpn_csr(l_delivery_details_id_table(1));
1031 LOOP
1032 FETCH lpn_csr INTO
1033 l_parent_delivery_detail_id, l_delivery_assignment_id,
1034 l_par_delivery_assignment_id;
1035
1036 EXIT WHEN lpn_csr%NOTFOUND;
1037
1038 IF l_debug = 1 THEN
1039 debug('l_parent_delivery_detail_id: '|| l_parent_delivery_detail_id, 'INV_LINE_RETURN_TO_STOCK');
1040 debug('l_delivery_assignment_id: '|| l_delivery_assignment_id, 'INV_LINE_RETURN_TO_STOCK');
1041 debug('l_par_delivery_assignment_id: '|| l_par_delivery_assignment_id, 'INV_LINE_RETURN_TO_STOCK');
1042 END IF;
1043
1044 SELECT lpn_id
1045 INTO l_lpn_id
1046 FROM wsh_delivery_details_ob_grp_v wdd
1047 WHERE delivery_detail_id = l_parent_delivery_detail_id;
1048
1049 IF l_debug = 1 THEN
1050 debug('l_lpn_id: '|| l_lpn_id, 'INV_LINE_RETURN_TO_STOCK');
1051 END IF;
1052
1053 --update LPN(s) context to Resides in Inventory
1054
1055 FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
1056 IF l_debug = 1 THEN
1057 debug('l_par_lpn_id.lpn_id: '|| l_par_lpn_id.lpn_id, 'INV_LINE_RETURN_TO_STOCK');
1058 END IF;
1059 UPDATE wms_license_plate_numbers
1060 SET lpn_context = 1,
1061 last_update_date = SYSDATE,
1062 last_updated_by = fnd_global.user_id
1063 WHERE lpn_id = l_par_lpn_id.lpn_id;
1064 IF l_debug = 1 THEN
1065 debug('Updated wms_license_plate_numbers context 1: ', 'INV_LINE_RETURN_TO_STOCK');
1066 END IF;
1067 END LOOP;
1068
1069 --**Check whether Shipping's backorder API does
1070 --1. Unassign the delivery line from container
1071 --2. if container becomes empty, unassign the container from
1072 -- delivery
1073 END LOOP;
1074
1075 CLOSE lpn_csr;*/
1076 -- End of release 12 change
1077
1078 ELSE --corresponding if: p_shipped_quantity = 0
1079
1080 IF l_debug = 1 THEN
1081 debug('Backordering part of delivery line: '|| l_delivery_details_id_table(1),
1082 'INV_LINE_RETURN_TO_STOCK');
1083 debug('Splitting the delivery line into ship: '
1084 || p_shipped_quantity || ' backorder : '
1085 || l_backorder_quantities_table(1)
1086 || ' requested : ' || l_requested_quantities_table(1)
1087 , 'INV_LINE_RETURN_TO_STOCK');
1088 END IF;
1089
1090 WSH_DELIVERY_DETAILS_PUB.split_line
1091 (p_api_version => 1.0,
1092 p_init_msg_list => fnd_api.g_false,
1093 p_commit => p_commit_flag,
1094 x_return_status => l_return_status,
1095 x_msg_count => l_msg_count,
1096 x_msg_data => l_msg_data,
1097 p_from_detail_id => l_delivery_details_id_table(1),
1098 x_new_detail_id => l_bo_delivery_detail_id,
1099 x_split_quantity => l_backorder_quantities_table(1),
1100 x_split_quantity2 => l_dummy_num_var);
1101
1102 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1103 IF l_debug = 1 THEN
1104 debug('WSH_DELIVERY_DETAILS_PUB.split_line failed',
1105 'INV_LINE_RETURN_TO_STOCK');
1106 END IF;
1107
1108 RAISE fnd_api.g_exc_unexpected_error;
1109 END IF;
1110
1111 l_detail_attributes(1).delivery_detail_id :=
1112 l_delivery_details_id_table(1);
1113 l_detail_attributes(1).shipped_quantity := p_shipped_quantity;
1114
1115 wsh_delivery_details_pub.update_shipping_attributes
1116 (p_api_version_number => 1.0,
1117 p_init_msg_list => fnd_api.g_false,
1118 p_commit => p_commit_flag,
1119 x_return_status => l_return_status,
1120 x_msg_count => l_msg_count,
1121 x_msg_data => l_msg_data,
1122 p_changed_attributes => l_detail_attributes,
1123 p_source_code => 'OE');
1124
1125 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1126 IF l_debug = 1 THEN
1127 debug('wsh_delivery_details_pub.update_shipping_attributesfailed',
1128 'INV_LINE_RETURN_TO_STOCK');
1129 END IF;
1130
1131 RAISE fnd_api.g_exc_unexpected_error;
1132 END IF;
1133
1134 l_delivery_details_id_table(1) := l_bo_delivery_detail_id;
1135 END IF;
1136
1137 IF l_debug = 1 THEN
1138 debug('Calling wsh_ship_confirm_actions2.backorder' ,'INV_LINE_RETURN_TO_STOCK');
1139 debug('delivery line being backorder : '|| l_delivery_details_id_table(1), 'INV_LINE_RETURN_TO_STOCK');
1140 debug(' backorder quantity : ' || l_backorder_quantities_table(1),'INV_LINE_RETURN_TO_STOCK');
1141 debug(' requested quantity : ' || l_requested_quantities_table(1),'INV_LINE_RETURN_TO_STOCK');
1142 debug(' overpick quantity : ' || l_overpicked_quantities_table(1) ,'INV_LINE_RETURN_TO_STOCK');
1143 END IF;
1144
1145 --bug3564157: Shipping's API require the dummy_table to be initialized
1146 l_dummy_table(1) := 0;
1147
1148 /* -- MRANA : bug:4594831-- Added the following setup, if p_relieve_rsv = 'Y',
1149 then we want reservations to be deleted after backorder, if it is N, then we
1150 want to retain reservations. Note that the overpicked reservations will not be
1151 retained.
1152 p_relieve_rsv is set by the unload page (UnloadTruckPage.java) using the value
1153 passed in the form function parameter- RELIEVE_RSV */
1154 IF nvl(p_relieve_rsv,'Y') = 'Y' THEN
1155 l_bo_mode := 'UNRESERVE';
1156 ELSE
1157 l_bo_mode := 'RETAIN_RSV'; -- suggested by shipping
1158 END IF;
1159
1160 wsh_ship_confirm_actions2.backorder
1161 (p_detail_ids => l_delivery_details_id_table,
1162 p_bo_qtys => l_backorder_quantities_table,
1163 p_req_qtys => l_requested_quantities_table, --BUG15854656
1164 p_bo_qtys2 => l_dummy_table,
1165 p_overpick_qtys => l_overpicked_quantities_table,
1166 p_overpick_qtys2 => l_dummy_table,
1167 p_bo_mode => l_bo_mode, -- MRANA : bug:4594831-- 'UNRESERVE',
1168 x_out_rows => l_out_rows,
1169 x_return_status => l_return_status);
1170
1171
1172 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1173 IF l_debug = 1 THEN
1174 debug('wsh_ship_confirm_actions2.backorder failed',
1175 'INV_LINE_RETURN_TO_STOCK');
1176 END IF;
1177
1178 RAISE fnd_api.g_exc_unexpected_error;
1179 END IF;
1180
1181 IF p_commit_flag = fnd_api.g_true THEN
1182 IF l_debug = 1 THEN
1183 debug('Successful, so commit everything','INV_LINE_RETURN_TO_STOCK');
1184 END IF;
1185 commit;
1186 END IF;
1187 EXCEPTION
1188 WHEN FND_API.G_EXC_ERROR THEN
1189 x_return_status := FND_API.G_RET_STS_ERROR;
1190 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1191 , p_data => x_msg_data);
1192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1194 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1195 , p_data => x_msg_data);
1196 WHEN OTHERS THEN
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1199 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INV_LINE_RETURN_TO_STOCK');
1200 END IF;
1201 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1202 , p_data => x_msg_data);
1203
1204 END INV_LINE_RETURN_TO_STOCK;
1205
1206 /**
1207 Bug No 3952081
1208 Override INV_LINE_RETURN_TO_STOCK to include DUOM attributes
1209 **/
1210 PROCEDURE INV_LINE_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
1211 p_delivery_line_id IN NUMBER,
1212 p_shipped_quantity IN NUMBER,
1213 p_sec_shipped_quantity IN NUMBER,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 x_msg_data OUT NOCOPY VARCHAR2,
1216 x_msg_count OUT NOCOPY NUMBER,
1217 p_commit_flag IN VARCHAR2 DEFAULT FND_API.g_true,
1218 p_relieve_rsv IN VARCHAR2 DEFAULT 'Y')
1219 IS
1220
1221 /* Change the cursor to pick up secondary picked and requested quantities */
1222 cursor delivery_line(p_delivery_detail_id NUMBER) is
1223 select dd.delivery_detail_id, dd.requested_quantity, dd.picked_quantity,
1224 PICKED_QUANTITY2, REQUESTED_QUANTITY2
1225 from wsh_delivery_details_ob_grp_v dd
1226 WHERE dd.delivery_detail_id = p_delivery_detail_id;
1227
1228 cursor lpn_csr(p_delivery_detail_id in NUMBER) is
1229 select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
1230 from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1231 , wsh_delivery_assignments_v wda2
1232 where wdd.delivery_detail_id = wda.parent_delivery_detail_id
1233 and wda.delivery_detail_id = wdd2.delivery_detail_id
1234 and wdd2.delivery_detail_id = p_delivery_detail_id
1235 and wda2.delivery_detail_id = wdd.delivery_detail_id;
1236
1237 CURSOR nested_parent_lpn_cursor(l_inner_lpn_id NUMBER) is
1238 SELECT lpn_id
1239 FROM WMS_LICENSE_PLATE_NUMBERS
1240 START WITH lpn_id = l_inner_lpn_id
1241 CONNECT BY lpn_id = PRIOR parent_lpn_id;
1242
1243 l_delivery_details_id_table WSH_UTIL_CORE.ID_TAB_TYPE;
1244 l_backorder_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
1245 l_requested_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
1246 l_overpicked_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
1247 l_sec_bck_qtys_table WSH_UTIL_CORE.ID_TAB_TYPE;
1248 l_sec_req_qtys_table WSH_UTIL_CORE.ID_TAB_TYPE;
1249 l_sec_ovpk_qtys_table WSH_UTIL_CORE.ID_TAB_TYPE;
1250 l_dummy_table wsh_util_core.id_tab_type;
1251 l_out_rows wsh_util_core.id_tab_type;
1252 l_detail_attributes wsh_delivery_details_pub.ChangedAttributeTabType;
1253 l_dummy_num_var NUMBER := NULL;
1254 l_table_index NUMBER := 1;
1255
1256 l_picked_quantity NUMBER;
1257 l_sec_picked_quantity NUMBER;
1258 l_parent_delivery_detail_id NUMBER;
1259 l_bo_delivery_detail_id NUMBER;
1260 l_delivery_assignment_id NUMBER;
1261 l_par_delivery_assignment_id NUMBER;
1262 l_lpn_id NUMBER;
1263
1264 l_more_detail NUMBER;
1265
1266 l_return_status VARCHAR2(1);
1267 l_msg_count NUMBER;
1268 l_msg_data VARCHAR2(2000);
1269
1270 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1271 BEGIN
1272 x_return_status := fnd_api.g_ret_sts_success;
1273
1274 --this cursor only returns 1 record because delivery_line_id is an
1275 --unique key
1276 OPEN delivery_line(p_delivery_line_id);
1277
1278 FETCH delivery_line INTO
1279 l_delivery_details_id_table(1),
1280 l_requested_quantities_table(1),
1281 l_picked_quantity,
1282 l_sec_picked_quantity,
1283 l_sec_req_qtys_table(1);
1284
1285
1286 IF l_picked_quantity > l_requested_quantities_table(1) THEN
1287 l_backorder_quantities_table(1) :=
1288 l_picked_quantity - p_shipped_quantity;
1289
1290 l_overpicked_quantities_table(1) :=
1291 l_picked_quantity - l_requested_quantities_table(1);
1292
1293 l_sec_bck_qtys_table(1) := l_sec_picked_quantity - p_sec_shipped_quantity;
1294
1295 l_sec_ovpk_qtys_table(1) := l_sec_picked_quantity - l_sec_req_qtys_table(1);
1296
1297 ELSE
1298 l_backorder_quantities_table(1) := l_requested_quantities_table(1) - p_shipped_quantity;
1299
1300 l_overpicked_quantities_table(1) := 0;
1301
1302 l_sec_bck_qtys_table(1) := l_sec_req_qtys_table(1) - p_sec_shipped_quantity;
1303
1304 l_sec_ovpk_qtys_table(1) := 0;
1305 END IF;
1306
1307 l_dummy_table(1) := NULL;
1308
1309 CLOSE delivery_line;
1310
1311 IF p_shipped_quantity = 0 THEN
1312 IF l_debug = 1 THEN
1313 debug('Backordering the entire delivery line: '
1314 || l_delivery_details_id_table(1), 'INV_LINE_RETURN_TO_STOCK');
1315 END IF;
1316
1317 -- Release 12: LPN SyncUP
1318 -- In addition to the LPN context update
1319 -- WDD records also need to be removed
1320 -- This is done by calling wms_container_pvt.modify_lpn API
1321 -- in WMS_DIRECT_SHIP_PVT.UNLOAD_TRUCK
1322 -- Remove the direct update here
1323 /*OPEN lpn_csr(l_delivery_details_id_table(1));
1324 LOOP
1325 FETCH lpn_csr INTO
1326 l_parent_delivery_detail_id, l_delivery_assignment_id,
1327 l_par_delivery_assignment_id;
1328
1329 EXIT WHEN lpn_csr%NOTFOUND;
1330
1331 SELECT lpn_id
1332 INTO l_lpn_id
1333 FROM wsh_delivery_details_ob_grp_v wdd
1334 WHERE delivery_detail_id = l_parent_delivery_detail_id;
1335
1336 --update LPN(s) context to Resides in Inventory
1337 FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
1338 UPDATE wms_license_plate_numbers
1339 SET lpn_context = 1,
1340 last_update_date = SYSDATE,
1341 last_updated_by = fnd_global.user_id
1342 WHERE lpn_id = l_par_lpn_id.lpn_id;
1343 END LOOP;
1344
1345 --**Check whether Shipping's backorder API does
1346 --1. Unassign the delivery line from container
1347 --2. if container becomes empty, unassign the container from
1348 -- delivery
1349 END LOOP;
1350
1351 CLOSE lpn_csr;*/
1352
1353 ELSE --corresponding if: p_shipped_quantity = 0
1354
1355 IF l_debug = 1 THEN
1356 debug('Backordering part of delivery line: '|| l_delivery_details_id_table(1),
1357 'INV_LINE_RETURN_TO_STOCK');
1358 debug('Splitting the delivery line into ship: '
1359 || p_shipped_quantity || ' backorder : '
1360 || l_backorder_quantities_table(1)
1361 || ' requested : ' || l_requested_quantities_table(1)
1362 , 'INV_LINE_RETURN_TO_STOCK');
1363 END IF;
1364
1365 WSH_DELIVERY_DETAILS_PUB.split_line
1366 (p_api_version => 1.0,
1367 p_init_msg_list => fnd_api.g_false,
1368 p_commit => p_commit_flag,
1369 x_return_status => l_return_status,
1370 x_msg_count => l_msg_count,
1371 x_msg_data => l_msg_data,
1372 p_from_detail_id => l_delivery_details_id_table(1),
1373 x_new_detail_id => l_bo_delivery_detail_id,
1374 x_split_quantity => l_backorder_quantities_table(1),
1375 x_split_quantity2 => l_sec_bck_qtys_table(1));
1376
1377 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1378 IF l_debug = 1 THEN
1379 debug('WSH_DELIVERY_DETAILS_PUB.split_line failed',
1380 'INV_LINE_RETURN_TO_STOCK');
1381 END IF;
1382
1383 RAISE fnd_api.g_exc_unexpected_error;
1384 END IF;
1385
1386 l_detail_attributes(1).delivery_detail_id :=
1387 l_delivery_details_id_table(1);
1388 l_detail_attributes(1).shipped_quantity := p_shipped_quantity;
1389
1390 /* set secondary shipped quantity into the delivery details attribute set */
1391 l_detail_attributes(1).shipped_quantity2 := p_sec_shipped_quantity;
1392
1393 wsh_delivery_details_pub.update_shipping_attributes
1394 (p_api_version_number => 1.0,
1395 p_init_msg_list => fnd_api.g_false,
1396 p_commit => p_commit_flag,
1397 x_return_status => l_return_status,
1398 x_msg_count => l_msg_count,
1399 x_msg_data => l_msg_data,
1400 p_changed_attributes => l_detail_attributes,
1401 p_source_code => 'OE');
1402
1403 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1404 IF l_debug = 1 THEN
1405 debug('wsh_delivery_details_pub.update_shipping_attributesfailed',
1406 'INV_LINE_RETURN_TO_STOCK');
1407 END IF;
1408
1409 RAISE fnd_api.g_exc_unexpected_error;
1410 END IF;
1411
1412 l_delivery_details_id_table(1) := l_bo_delivery_detail_id;
1413 END IF;
1414
1415 IF l_debug = 1 THEN
1416 debug('Calling wsh_ship_confirm_actions2.backorder'
1417 ,'INV_LINE_RETURN_TO_STOCK');
1418 debug('delivery line being backorder : '|| l_delivery_details_id_table(1)
1419 || ' backorder quantity : ' || l_backorder_quantities_table(1)
1420 || ' requested quantity : ' || l_requested_quantities_table(1)
1421 || ' overpick quantity : ' || l_overpicked_quantities_table(1)
1422 ,'INV_LINE_RETURN_TO_STOCK');
1423 END IF;
1424
1425 --bug3564157: Shipping's API require the dummy_table to be initialized
1426 l_dummy_table(1) := 0;
1427 wsh_ship_confirm_actions2.backorder
1428 (p_detail_ids => l_delivery_details_id_table,
1429 p_bo_qtys => l_backorder_quantities_table,
1430 p_req_qtys => l_requested_quantities_table, --BUG15854656
1431 p_bo_qtys2 =>l_sec_bck_qtys_table,
1432 p_overpick_qtys => l_overpicked_quantities_table,
1433 p_overpick_qtys2 => l_sec_ovpk_qtys_table,
1434 p_bo_mode => 'UNRESERVE',
1435 x_out_rows => l_out_rows,
1436 x_return_status => l_return_status);
1437
1438
1439 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1440 IF l_debug = 1 THEN
1441 debug('wsh_ship_confirm_actions2.backorder failed',
1442 'INV_LINE_RETURN_TO_STOCK');
1443 END IF;
1444
1445 RAISE fnd_api.g_exc_unexpected_error;
1446 END IF;
1447
1448 IF p_commit_flag = fnd_api.g_true THEN
1449 IF l_debug = 1 THEN
1450 debug('Successful, so commit everything','INV_LINE_RETURN_TO_STOCK');
1451 END IF;
1452 commit;
1453 END IF;
1454 EXCEPTION
1455 WHEN FND_API.G_EXC_ERROR THEN
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1458 , p_data => x_msg_data);
1459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1462 , p_data => x_msg_data);
1463 WHEN OTHERS THEN
1464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1465 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1466 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INV_LINE_RETURN_TO_STOCK');
1467 END IF;
1468 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1469 , p_data => x_msg_data);
1470
1471 END INV_LINE_RETURN_TO_STOCK;
1472
1473 PROCEDURE INV_REPORT_MISSING_QTY(
1474 p_delivery_line_id IN NUMBER,
1475 p_missing_quantity IN NUMBER,
1476 x_return_status OUT NOCOPY VARCHAR2,
1477 x_msg_data OUT NOCOPY VARCHAR2,
1478 x_msg_count OUT NOCOPY NUMBER) IS
1479 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1480 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
1481 l_details VARCHAR2(2000);
1482 BEGIN
1483 l_detail_attributes(1).cycle_count_quantity := p_missing_quantity;
1484 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
1485
1486 IF l_debug = 1 THEN
1487 debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
1488 'INV_REPORT_MISSING_QTY');
1489 END IF;
1490 wsh_delivery_details_pub.update_shipping_attributes
1491 (p_api_version_number => 1.0,
1492 p_init_msg_list => G_TRUE,
1493 p_commit => G_FALSE,
1494 x_return_status => x_return_status,
1495 x_msg_count => x_msg_count,
1496 x_msg_data => x_msg_data,
1497 p_changed_attributes => l_detail_attributes,
1498 p_source_code => 'OE');
1499
1500 IF x_return_status <> G_RET_STS_SUCCESS THEN
1501 IF l_debug = 1 THEN
1502 debug('wsh_delivery_details_pub.update_shipping_attributes failed'
1503 || ' with status: ' || x_return_status, 'INV_REPORT_MISSING_QTY');
1504 END IF;
1505 RAISE fnd_api.g_exc_unexpected_error;
1506 END IF;
1507
1508 EXCEPTION
1509 WHEN fnd_api.g_exc_unexpected_error THEN
1510 wsh_util_core.get_messages
1511 (p_init_msg_list => 'Y',
1512 x_summary => x_msg_data,
1513 x_details => l_details,
1514 x_count => x_msg_count);
1515 when no_data_found then
1516 -- do nothing for now
1517 null;
1518
1519 END INV_REPORT_MISSING_QTY;
1520
1521 /**
1522 Bug No 3952081
1523 Overiding method INV_REPORT_MISSINg_QTY to include DUOM
1524 attributes as part of input arguments
1525 **/
1526 PROCEDURE INV_REPORT_MISSING_QTY(
1527 p_delivery_line_id IN NUMBER,
1528 p_missing_quantity IN NUMBER,
1529 p_sec_missing_quantity IN NUMBER,
1530 x_return_status OUT NOCOPY VARCHAR2,
1531 x_msg_data OUT NOCOPY VARCHAR2,
1532 x_msg_count OUT NOCOPY NUMBER) IS
1533 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1534 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
1535 l_details VARCHAR2(2000);
1536 BEGIN
1537 l_detail_attributes(1).cycle_count_quantity := p_missing_quantity;
1538
1539 /* Set cycle_count_quantity2 using argument p_sec_missing_quantity */
1540 l_detail_attributes(1).cycle_count_quantity2 := p_sec_missing_quantity;
1541
1542 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
1543
1544 IF l_debug = 1 THEN
1545 debug('After setting l_detail_attributes(1).cycle_count_quantity2 is '|| l_detail_attributes(1).cycle_count_quantity2,
1546 'INV_REPORT_MISSING_QTY');
1547 END IF;
1548
1549 IF l_debug = 1 THEN
1550 debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
1551 'INV_REPORT_MISSING_QTY');
1552 END IF;
1553 wsh_delivery_details_pub.update_shipping_attributes
1554 (p_api_version_number => 1.0,
1555 p_init_msg_list => G_TRUE,
1556 p_commit => G_FALSE,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data,
1560 p_changed_attributes => l_detail_attributes,
1561 p_source_code => 'OE');
1562
1563 IF x_return_status <> G_RET_STS_SUCCESS THEN
1564 IF l_debug = 1 THEN
1565 debug('wsh_delivery_details_pub.update_shipping_attributes failed'
1566 || ' with status: ' || x_return_status, 'INV_REPORT_MISSING_QTY');
1567 END IF;
1568 RAISE fnd_api.g_exc_unexpected_error;
1569 END IF;
1570
1571 EXCEPTION
1572 WHEN fnd_api.g_exc_unexpected_error THEN
1573 wsh_util_core.get_messages
1574 (p_init_msg_list => 'Y',
1575 x_summary => x_msg_data,
1576 x_details => l_details,
1577 x_count => x_msg_count);
1578 when no_data_found then
1579 -- do nothing for now
1580 null;
1581
1582 END INV_REPORT_MISSING_QTY;
1583
1584 PROCEDURE SUBMIT_DELIVERY_LINE(p_delivery_line_id IN NUMBER,
1585 p_quantity IN NUMBER,
1586 p_trackingNumber IN VARCHAR2,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_data OUT NOCOPY VARCHAR2,
1589 x_msg_count OUT NOCOPY NUMBER ) IS
1590 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1591 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
1592
1593
1594 l_details VARCHAR2(2000);
1595
1596 CURSOR c_weight_vol_info IS
1597 SELECT unit_weight,
1598 unit_volume,
1599 nvl(wv_frozen_flag ,'N') wv_frozen_flag
1600 FROM WSH_DELIVERY_DETAILS_OB_GRP_V
1601 WHERE delivery_detail_id = p_delivery_line_id;
1602
1603 l_weight_vol_info c_weight_vol_info%ROWTYPE;
1604
1605 l_gross_weight NUMBER;
1606
1607 l_net_weight NUMBER;
1608
1609 l_total_volume NUMBER;
1610
1611 BEGIN
1612 IF l_debug = 1 THEN
1613 debug('p_quantity passed in: ' || p_quantity,'SUBMIT_DELIVERY_LINE');
1614 debug('p_trackingNumber: ' ||p_trackingNumber,'SUMBIT_DELIVERY_LINE');
1615 debug('p_deliery_line_id: ' || p_delivery_line_id,'SUBMIT_DELIVERY_LINE');
1616 END IF;
1617
1618 IF p_quantity IS NOT NULL then
1619 if p_quantity > 0 then /* Bug 5466481 */
1620 l_detail_attributes(1).shipped_quantity := p_quantity;
1621 end if;
1622
1623 OPEN c_weight_vol_info;
1624
1625 FETCH c_weight_vol_info INTO l_weight_vol_info;
1626
1627 CLOSE c_weight_vol_info;
1628
1629 IF (l_debug =1) THEN
1630 debug('Unit Weight :'||l_weight_vol_info.unit_weight||l_weight_vol_info.unit_volume,'SUBMIT_DELIVERY_LINE');
1631 debug('Unit Volume are :'||l_weight_vol_info.unit_weight||l_weight_vol_info.unit_volume,'SUBMIT_DELIVERY_LINE');
1632 END IF;
1633
1634 IF (l_weight_vol_info.wv_frozen_flag='N' AND (l_weight_vol_info.unit_weight IS NOT NULL OR l_weight_vol_info.unit_volume IS NOT NULL)) THEN
1635
1636 IF l_weight_vol_info.unit_weight IS NOT NULL THEN
1637 l_detail_attributes(1).gross_weight := p_quantity*l_weight_vol_info.unit_weight;
1638 l_detail_attributes(1).net_weight := p_quantity*l_weight_vol_info.unit_weight;
1639 END IF;
1640
1641 IF l_weight_vol_info.unit_volume IS NOT NULL THEN
1642 l_detail_attributes(1).volume := p_quantity*l_weight_vol_info.unit_volume;
1643 END IF;
1644
1645 IF (l_debug=1) THEN
1646 debug('The Gross weight calcuated is '||l_detail_attributes(1).gross_weight,'SUBMIT_DELIVERY_LINE');
1647 debug('The Net weight calcuated is '||l_detail_attributes(1).net_weight,'SUBMIT_DELIVERY_LINE');
1648 debug('The Volume calcuated is '||l_detail_attributes(1).volume,'SUBMIT_DELIVERY_LINE');
1649 END IF;
1650 END IF;
1651 END IF;
1652
1653 IF p_trackingNumber IS NOT NULL THEN
1654 IF l_debug = 1 THEN
1655 debug('updating tracking number to: ' || p_trackingNumber,'SUBMIT_DELIVERY_LINE');
1656 END IF;
1657 l_detail_attributes(1).tracking_number := p_trackingNumber;
1658 END IF;
1659
1660 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
1661
1662 IF l_debug = 1 THEN
1663 debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
1664 'SUBMIT_DELIVERY_LINE');
1665 END IF;
1666
1667 wsh_delivery_details_pub.update_shipping_attributes
1668 (p_api_version_number => 1.0,
1669 p_init_msg_list => G_TRUE,
1670 p_commit => G_TRUE,
1671 x_return_status => x_return_status,
1672 x_msg_count => x_msg_count,
1673 x_msg_data => x_msg_data,
1674 p_changed_attributes => l_detail_attributes,
1675 p_source_code => 'OE');
1676
1677 IF l_debug = 1 THEN
1678 debug('return stat: ' || x_return_status,'SUBMIT_DELIVERY_LINE');
1679 END IF;
1680
1681 IF x_return_status <> G_RET_STS_SUCCESS THEN
1682 IF l_debug = 1 THEN
1683 debug('wsh_delivery_details_pub.update_shipping_attributes failed'
1684 || ' with status: ' || x_return_status, 'SUBMIT_DELIVERY_LINE');
1685 END IF;
1686 RAISE fnd_api.g_exc_unexpected_error;
1687 END IF;
1688 EXCEPTION
1689 WHEN fnd_api.g_exc_unexpected_error THEN
1690 wsh_util_core.get_messages
1691 (p_init_msg_list => 'Y',
1692 x_summary => x_msg_data,
1693 x_details => l_details,
1694 x_count => x_msg_count);
1695
1696 IF l_debug = 1 THEN
1697 debug('x_summary: ' || x_msg_data,'SUBMIT_DELIVERY_LINE');
1698 debug('x_details: ' || l_details, 'SUBMIT_DELIVERY_LINE');
1699 END IF;
1700 when no_data_found then
1701 -- do nothing for now
1702 null;
1703 END SUBMIT_DELIVERY_LINE;
1704
1705 --Bug 3952081
1706 --Override SUBMIT_DELIVERY_LINE to include secondary qty as
1707 -- parameter
1708 PROCEDURE SUBMIT_DELIVERY_LINE(p_delivery_line_id IN NUMBER,
1709 p_quantity IN NUMBER,
1710 p_sec_quantity IN NUMBER,
1711 p_trackingNumber IN VARCHAR2,
1712 x_return_status OUT NOCOPY VARCHAR2,
1713 x_msg_data OUT NOCOPY VARCHAR2,
1714 x_msg_count OUT NOCOPY NUMBER ) IS
1715 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1716 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
1717
1718
1719 l_details VARCHAR2(2000);
1720
1721 CURSOR c_weight_vol_info IS
1722 SELECT unit_weight,
1723 unit_volume,
1724 nvl(wv_frozen_flag ,'N') wv_frozen_flag
1725 FROM WSH_DELIVERY_DETAILS_OB_GRP_V
1726 WHERE delivery_detail_id = p_delivery_line_id;
1727
1728 l_weight_vol_info c_weight_vol_info%ROWTYPE;
1729
1730 l_gross_weight NUMBER;
1731
1732 l_net_weight NUMBER;
1733
1734 l_total_volume NUMBER;
1735
1736 BEGIN
1737 IF l_debug = 1 THEN
1738 debug('p_quantity passed in: ' || p_quantity,'SUBMIT_DELIVERY_LINE');
1739 debug('p_trackingNumber: ' ||p_trackingNumber,'SUMBIT_DELIVERY_LINE');
1740 debug('p_deliery_line_id: ' || p_delivery_line_id,'SUBMIT_DELIVERY_LINE');
1741 END IF;
1742
1743 IF p_quantity IS NOT NULL then
1744 if p_quantity > 0 then /* Bug 5466481 */
1745 l_detail_attributes(1).shipped_quantity := p_quantity;
1746 end if;
1747
1748 OPEN c_weight_vol_info;
1749
1750 FETCH c_weight_vol_info INTO l_weight_vol_info;
1751
1752 CLOSE c_weight_vol_info;
1753
1754 IF (l_debug =1) THEN
1755 debug('Unit Weight :'||l_weight_vol_info.unit_weight||l_weight_vol_info.unit_volume,'SUBMIT_DELIVERY_LINE');
1756 debug('Unit Volume are :'||l_weight_vol_info.unit_weight||l_weight_vol_info.unit_volume,'SUBMIT_DELIVERY_LINE');
1757 END IF;
1758
1759 IF (l_weight_vol_info.wv_frozen_flag='N' AND (l_weight_vol_info.unit_weight IS NOT NULL OR l_weight_vol_info.unit_volume IS NOT NULL)) THEN
1760
1761 IF l_weight_vol_info.unit_weight IS NOT NULL THEN
1762 l_detail_attributes(1).gross_weight := p_quantity*l_weight_vol_info.unit_weight;
1763 l_detail_attributes(1).net_weight := p_quantity*l_weight_vol_info.unit_weight;
1764 END IF;
1765
1766 IF l_weight_vol_info.unit_volume IS NOT NULL THEN
1767 l_detail_attributes(1).volume := p_quantity*l_weight_vol_info.unit_volume;
1768 END IF;
1769
1770 IF (l_debug=1) THEN
1771 debug('The Gross weight calcuated is '||l_detail_attributes(1).gross_weight,'SUBMIT_DELIVERY_LINE');
1772 debug('The Net weight calcuated is '||l_detail_attributes(1).net_weight,'SUBMIT_DELIVERY_LINE');
1773 debug('The Volume calcuated is '||l_detail_attributes(1).volume,'SUBMIT_DELIVERY_LINE');
1774 END IF;
1775 END IF;
1776 END IF;
1777
1778 IF p_sec_quantity IS NOT NULL THEN
1779 IF l_debug = 1 THEN
1780 debug('updating Secondary Quantity: ' || p_sec_quantity,'SUBMIT_DELIVERY_LINE');
1781 END IF;
1782 if p_sec_quantity > 0 then /* Bug 5466481 */
1783 l_detail_attributes(1).shipped_quantity2 := p_sec_quantity;
1784 end if;
1785 END IF;
1786
1787 IF p_trackingNumber IS NOT NULL THEN
1788 IF l_debug = 1 THEN
1789 debug('updating tracking number to: ' || p_trackingNumber,'SUBMIT_DELIVERY_LINE');
1790 END IF;
1791 l_detail_attributes(1).tracking_number := p_trackingNumber;
1792 END IF;
1793
1794 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
1795
1796 IF l_debug = 1 THEN
1797 debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
1798 'SUBMIT_DELIVERY_LINE');
1799 END IF;
1800
1801 wsh_delivery_details_pub.update_shipping_attributes
1802 (p_api_version_number => 1.0,
1803 p_init_msg_list => G_TRUE,
1804 p_commit => G_TRUE,
1805 x_return_status => x_return_status,
1806 x_msg_count => x_msg_count,
1807 x_msg_data => x_msg_data,
1808 p_changed_attributes => l_detail_attributes,
1809 p_source_code => 'OE');
1810
1811 IF l_debug = 1 THEN
1812 debug('return stat: ' || x_return_status,'SUBMIT_DELIVERY_LINE');
1813 END IF;
1814
1815 IF x_return_status <> G_RET_STS_SUCCESS THEN
1816 IF l_debug = 1 THEN
1817 debug('wsh_delivery_details_pub.update_shipping_attributes failed'
1818 || ' with status: ' || x_return_status, 'SUBMIT_DELIVERY_LINE');
1819 END IF;
1820 RAISE fnd_api.g_exc_unexpected_error;
1821 END IF;
1822 EXCEPTION
1823 WHEN fnd_api.g_exc_unexpected_error THEN
1824 wsh_util_core.get_messages
1825 (p_init_msg_list => 'Y',
1826 x_summary => x_msg_data,
1827 x_details => l_details,
1828 x_count => x_msg_count);
1829
1830 IF l_debug = 1 THEN
1831 debug('x_summary: ' || x_msg_data,'SUBMIT_DELIVERY_LINE');
1832 debug('x_details: ' || l_details, 'SUBMIT_DELIVERY_LINE');
1833 END IF;
1834 when no_data_found then
1835 -- do nothing for now
1836 null;
1837 END SUBMIT_DELIVERY_LINE;
1838
1839 FUNCTION GET_LINE_TRANSACTION_TYPE(
1840 p_order_line_id IN NUMBER,
1841 x_trx_source_type_id OUT NOCOPY NUMBER,
1842 x_trx_Action_id OUT NOCOPY NUMBER,
1843 x_return_status OUT NOCOPY VARCHAR2 )
1844 return NUMBER IS
1845
1846 CURSOR c_order_line_info(c_order_line_id number) is
1847 SELECT source_document_type_id, source_document_id, source_document_line_id
1848 from oe_order_lines_all
1849 where line_id = c_order_line_id;
1850
1851 l_order_line_info c_order_line_info%ROWTYPE;
1852
1853 CURSOR c_po_info(c_po_line_id NUMBER, c_order_line_id NUMBER) is
1854 SELECT destination_type_code,
1855 destination_subinventory,
1856 source_organization_id,
1857 destination_organization_id,
1858 deliver_to_location_id,
1859 pl.requisition_line_id
1860 from po_requisition_lines_all pl,
1861 oe_order_lines_all ol
1862 where ol.source_document_type_id = 10
1863 AND ol.line_id = c_order_line_id
1864 and pl.requisition_line_id = c_po_line_id
1865 and pl.requisition_line_id = ol.source_document_line_id
1866 and pl.requisition_header_id = ol.source_document_id;
1867
1868 l_po_info c_po_info%ROWTYPE;
1869
1870 l_source_line_id NUMBER;
1871 l_trx_type_code NUMBER;
1872 l_trx_src_type NUMBER;
1873 l_trx_act_id NUMBER;
1874 l_intransit_type NUMBER;
1875
1876 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1877 BEGIN
1878 x_return_status := 'C';
1879 l_trx_type_code := -1;
1880
1881 OPEN c_order_line_info(p_order_line_id);
1882 FETCH c_order_line_info into l_order_line_info;
1883 if (c_order_line_info%NOTFOUND) THEN
1884 CLOSE c_order_line_info;
1885 x_return_status := 'E';
1886 return -1;
1887 END if;
1888 CLOSE c_order_line_info;
1889
1890 if (l_order_line_info.source_document_type_id = 10) THEN /* internal order */
1891 /* only for internal purchase orders, we need to fetch the po info */
1892 OPEN c_po_info(l_order_line_info.source_document_line_id,p_order_line_id);
1893 FETCH c_po_info into l_po_info;
1894 if c_po_info%NOTFOUND then
1895 CLOSE c_po_info;
1896 x_return_status := 'E';
1897 return -1;
1898 end if;
1899 CLOSE c_po_info;
1900
1901 if (l_po_info.destination_type_code = 'EXPENSE') THEN
1902 l_trx_type_code := 34; /* Store Issue */
1903 elsif (l_po_info.destination_type_code = 'INVENTORY') THEN
1904 if (l_po_info.source_organization_id = l_po_info.destination_organization_id) then
1905 l_trx_type_code := 50 /* Subinv_xfer */;
1906 else
1907 BEGIN
1908 SELECT intransit_type
1909 INTO l_intransit_type
1910 FROM mtl_interorg_parameters
1911 WHERE from_organization_id = l_po_info.source_organization_id
1912 and to_organization_id = l_po_info.destination_organization_id;
1913
1914 if (l_intransit_type =1) then
1915 l_trx_type_code := 54 /* Direct shipment */;
1916 else
1917 l_trx_type_code := 62 /* intransit_shpmnt */;
1918 end if;
1919 EXCEPTION WHEN NO_DATA_FOUND THEN
1920 l_trx_type_code := 62;
1921 END;
1922 end if;
1923 end if;
1924 else /* not internal order */
1925 l_trx_type_code := 33;
1926 END if;
1927
1928 if l_trx_type_code = -1 then
1929 x_trx_source_type_id := -1;
1930 x_trx_action_id := -1;
1931 return -1;
1932 end if; -- if there is no any type matching, don't need to
1933 -- check status
1934
1935 select TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID
1936 into l_trx_act_id, l_trx_src_type
1937 from mtl_transaction_Types
1938 where transaction_type_id = l_trx_type_code;
1939
1940 x_trx_source_type_id := l_trx_src_type;
1941 x_trx_action_id := l_trx_act_id;
1942 return l_trx_type_code;
1943
1944 EXCEPTION
1945 when no_data_found then
1946 x_return_status := 'E';
1947 x_trx_source_type_id := -1;
1948 x_trx_action_id := -1;
1949 return -1;
1950 END GET_LINE_TRANSACTION_TYPE;
1951
1952 FUNCTION GET_DELIVERY_TRANSACTION_TYPE(
1953 p_delivery_detail_id IN NUMBER,
1954 x_trx_source_type_id OUT NOCOPY NUMBER,
1955 x_trx_Action_id OUT NOCOPY NUMBER,
1956 x_return_status OUT NOCOPY VARCHAR2 )
1957 return NUMBER IS
1958
1959 l_source_line_id number;
1960
1961 BEGIN
1962
1963 SELECT source_line_id
1964 INTO l_source_line_id
1965 FROM wsh_delivery_details_ob_grp_v
1966 WHERE delivery_detail_id = p_delivery_detail_id;
1967
1968 RETURN GET_LINE_TRANSACTION_TYPE(l_source_line_id,x_trx_source_type_id,
1969 x_trx_action_id,x_return_status);
1970
1971 EXCEPTION
1972 when no_data_found then
1973 x_return_status := 'E';
1974 x_trx_source_type_id := -1;
1975 x_trx_action_id := -1;
1976 return -1;
1977
1978 END GET_DELIVERY_TRANSACTION_TYPE;
1979
1980 PROCEDURE CHECK_DELIVERY_STATUS(
1981 p_delivery_id IN NUMBER,
1982 x_return_Status OUT NOCOPY VARCHAR2,
1983 x_error_msg OUT NOCOPY VARCHAR2)
1984 IS
1985
1986 CURSOR c_delivery_details is
1987 SELECT dd.*
1988 from wsh_delivery_details_ob_grp_v dd,
1989 wsh_delivery_assignments_v da
1990 where
1991 da.delivery_id = p_delivery_id
1992 and da.delivery_detail_id = dd.delivery_detail_id
1993 and dd.lpn_id is null;
1994
1995 CURSOR get_lpn_id_wdd(l_delivery_detail_id NUMBER) IS --16482236
1996 SELECT wdd1.lpn_id
1997 from wsh_delivery_details wdd1,
1998 wsh_delivery_details wdd2,
1999 wsh_delivery_assignments wda
2000 WHERE wdd2.delivery_detail_id = l_delivery_detail_id
2001 AND wdd2.delivery_detail_id = wda.delivery_detail_id
2002 AND wda.parent_delivery_detail_id IS NOT NULL
2003 AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
2004 AND wdd1.container_flag = 'Y'
2005 AND wdd1.lpn_id is not null; --16482236
2006
2007 l_detail_rec c_delivery_details%ROWTYPE;
2008
2009 l_org_id NUMBER;
2010 l_trx_type_code NUMBER;
2011 l_status_enabled NUMBER;
2012 l_trx_src_type_id NUMBER;
2013 l_trx_act_id NUMBER;
2014 l_status_applicable VARCHAR2(1);
2015 l_lpn_id NUMBER; --16482236
2016
2017 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2018 BEGIN
2019 FOR l_detail_rec IN c_delivery_details LOOP
2020 l_trx_type_code := GET_DELIVERY_TRANSACTION_TYPE(l_detail_rec.delivery_detail_id,
2021 l_trx_src_type_id,
2022 l_trx_act_id,
2023 x_return_Status);
2024 --inv_debug.message('jali','Transaction Type'||to_char(l_trx_type_code));
2025 if (l_trx_type_code = -1) then
2026 x_error_msg := 'Cannot find the transaction type for delivery line:'
2027 ||to_char(l_detail_rec.delivery_detail_id);
2028 x_return_status := 'C';
2029 return;
2030 end if;
2031
2032 select status_control_flag
2033 into l_status_enabled
2034 from mtl_transaction_types
2035 where transaction_type_id = l_trx_type_code;
2036
2037 l_org_id := l_detail_rec.organization_id;
2038
2039 if (l_status_enabled = 1) then
2040
2041 l_lpn_id := NULL; --16482236
2042 OPEN get_lpn_id_wdd (l_detail_rec.delivery_detail_id);
2043 FETCH get_lpn_id_wdd INTO l_lpn_id;
2044 CLOSE get_lpn_id_wdd; --16482236
2045 -- check subinventory
2046 if (l_detail_rec.subinventory is not NULL) then
2047 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2048 p_wms_installed => 'TRUE'
2049 , p_trx_status_enabled => l_status_enabled
2050 , p_trx_type_id => l_trx_type_code
2051 , p_organization_id => l_org_id
2052 , p_sub_code => l_detail_rec.subinventory
2053 , p_object_type => 'Z'
2054 , p_lpn_id => l_lpn_id); --16482236
2055 if (l_status_applicable = 'N') then
2056 x_error_msg := 'Subinventory '||l_detail_rec.subinventory||
2057 ' does not allow Ship Confirm';
2058 x_return_status := 'E';
2059 return;
2060 end if;
2061 end if;
2062 -- check locator
2063 if (l_detail_rec.locator_id is not NULL) then
2064 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2065 p_wms_installed => 'TRUE'
2066 , p_trx_status_enabled => l_status_enabled
2067 , p_trx_type_id => l_trx_type_code
2068 , p_organization_id => l_org_id
2069 , p_locator_id => l_detail_rec.locator_id
2070 , p_object_type => 'L'
2071 , p_lpn_id => l_lpn_id); --16482236
2072 if (l_status_applicable = 'N') then
2073 x_error_msg := 'Staging Lane '||' does not allow Ship Confirm';
2074 x_return_status := 'E';
2075 return ;
2076 end if;
2077 end if;
2078 -- check lot
2079 if (l_detail_rec.lot_number is not NULL) then
2080 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2081 p_wms_installed => 'TRUE'
2082 , p_trx_status_enabled => l_status_enabled
2083 , p_trx_type_id => l_trx_type_code
2084 , p_organization_id => l_org_id
2085 , p_inventory_item_id => l_detail_rec.inventory_item_id
2086 , p_lot_number => l_detail_rec.lot_number
2087 , p_object_type => 'O'
2088 , p_lpn_id => l_lpn_id --16482236
2089 , p_locator_id => l_detail_rec.locator_id --16482236
2090 , p_sub_code => l_detail_rec.subinventory); --16482236
2091 if (l_status_applicable = 'N') then
2092 x_error_msg := 'Lot '||l_detail_rec.lot_number||
2093 ' does not allow Ship Confirm';
2094 x_return_status := 'E';
2095 return;
2096 end if;
2097 end if;
2098 -- check serial
2099 if (l_detail_rec.serial_number is not NULL) then
2100 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2101 p_wms_installed => 'TRUE'
2102 , p_trx_status_enabled => l_status_enabled
2103 , p_trx_type_id => l_trx_type_code
2104 , p_organization_id => l_org_id
2105 , p_inventory_item_id => l_detail_rec.inventory_item_id
2106 , p_serial_number => l_detail_rec.serial_number
2107 , p_object_type => 'S'
2108 , p_lpn_id => l_lpn_id); --16482236
2109 if (l_status_applicable = 'N') then
2110 x_error_msg := 'Serial '||l_detail_rec.serial_number||
2111 ' does not allow Ship Confirm';
2112 x_return_status := 'E';
2113 return;
2114 end if;
2115 end if;
2116 end if;
2117 end LOOP;
2118 x_return_status := 'C';
2119 return;
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 x_return_status := 'C';
2123 return;
2124 END CHECK_DELIVERY_STATUS;
2125
2126 PROCEDURE CHECK_SHIP_SET(
2127 p_delivery_id IN NUMBER,
2128 x_ship_set OUT NOCOPY VARCHAR2,
2129 x_return_Status OUT NOCOPY VARCHAR2,
2130 x_error_msg OUT NOCOPY VARCHAR2)
2131 IS
2132 l_ship_set VARCHAR2(2000) := NULL;
2133 l_ship_set_id NUMBER;
2134 l_ship_set_name VARCHAR2(30);
2135 unshipped_count NUMBER;
2136
2137 CURSOR specified_ship_set IS
2138 SELECT wdd.ship_set_id
2139 FROM wsh_delivery_details_ob_grp_v wdd,
2140 wsh_delivery_assignments_v wda
2141 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2142 AND EXISTS (SELECT 'x'
2143 FROM wsh_delivery_details_ob_grp_v wdd2
2144 WHERE wdd2.delivery_detail_id = wdd.delivery_detail_id
2145 AND wdd2.ship_set_id is not null
2146 AND wdd2.shipped_quantity is not null)
2147 AND wda.delivery_id = p_delivery_id;
2148
2149 BEGIN
2150 x_return_status := 'C';
2151 OPEN specified_ship_set;
2152 loop
2153 FETCH specified_ship_set INTO l_ship_set_id;
2154 EXIT WHEN specified_ship_set%NOTFOUND;
2155 SELECT count(*)
2156 INTO unshipped_count
2157 FROM wsh_delivery_details_ob_grp_v wdd,
2158 wsh_delivery_assignments_v wda,
2159 wsh_new_deliveries_ob_grp_v wnd
2160 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2161 AND wda.delivery_id = wnd.delivery_id
2162 AND wnd.delivery_id = p_delivery_id
2163 AND wdd.ship_set_id = l_ship_set_id
2164 AND wdd.shipped_quantity is null;
2165 if (unshipped_count >0 ) then
2166 select set_name
2167 into l_ship_set_name
2168 from oe_sets
2169 where set_id = l_ship_set_id;
2170 if (l_ship_set is null) then
2171 l_ship_set := l_ship_set_name;
2172 else l_ship_set := l_ship_set ||', '||l_ship_set_name;
2173 end if;
2174 end if;
2175 end loop;
2176 close specified_ship_set;
2177 if l_ship_set is null then
2178 x_return_status := 'C';
2179 else
2180 x_return_status := 'E';
2181 x_ship_set := l_ship_set;
2182 end if;
2183 EXCEPTION
2184 WHEN OTHERS THEN
2185 x_return_status := 'U';
2186 END CHECK_SHIP_SET;
2187
2188 PROCEDURE CHECK_COMPLETE_DELVIERY(
2189 p_delivery_id IN NUMBER,
2190 x_return_Status OUT NOCOPY VARCHAR2,
2191 x_error_msg OUT NOCOPY VARCHAR2) IS
2192 exist_unspecified NUMBER;
2193 BEGIN
2194 x_return_Status := 'C';
2195 select 1
2196 into exist_unspecified
2197 from dual
2198 where exists (select 1
2199 from wsh_delivery_details_ob_grp_v wdd,
2200 wsh_delivery_assignments_v wda
2201 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2202 AND wdd.shipped_quantity is null
2203 AND wdd.container_flag = 'N'
2204 AND wda.delivery_id = p_delivery_id
2205 );
2206 if exist_unspecified = 1 then x_return_Status := 'E'; end if;
2207 EXCEPTION
2208 WHEN NO_DATA_FOUND THEN
2209 x_return_Status := 'C';
2210 WHEN OTHERS THEN
2211 x_return_Status := 'U';
2212 END CHECK_COMPLETE_DELVIERY;
2213
2214 PROCEDURE UNASSIGN_DELIVERY_LINES(
2215 p_delivery_id IN NUMBER,
2216 x_return_Status OUT NOCOPY VARCHAR2,
2217 x_error_msg OUT NOCOPY VARCHAR2) IS
2218 l_return_status VARCHAR2(1);
2219
2220 CURSOR delivery_details IS
2221 select wdd.delivery_detail_id
2222 from wsh_delivery_details_ob_grp_v wdd,
2223 wsh_delivery_assignments_v wda,
2224 wsh_new_deliveries_ob_grp_v wnd
2225 where wdd.delivery_detail_id = wda.delivery_detail_id
2226 AND wda.delivery_id = wnd.delivery_id
2227 AND wnd.delivery_id = p_delivery_id
2228 AND wdd.shipped_quantity is null
2229 AND wdd.container_flag = 'N'; --Bug 5971499
2230
2231 l_delivery_detail_id delivery_details%ROWTYPE;
2232 BEGIN
2233 for l_delivery_detail_id in delivery_details
2234 loop
2235 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Delivery(
2236 l_delivery_detail_id.delivery_detail_id,
2237 l_return_status);
2238 exit when l_return_status<>FND_API.G_RET_STS_SUCCESS;
2239 end loop;
2240 x_return_Status := l_return_status;
2241 END UNASSIGN_DELIVERY_LINES;
2242
2243 PROCEDURE CHECK_ENTIRE_EZ_DELIVERY(
2244 p_delivery_id IN NUMBER,
2245 x_return_Status OUT NOCOPY VARCHAR2,
2246 x_error_msg OUT NOCOPY VARCHAR2) IS
2247 exist_unqualified NUMBER := 0;
2248 BEGIN
2249 x_return_Status := 'Y';
2250 SELECT 1
2251 INTO exist_unqualified
2252 FROM dual
2253 WHERE EXISTS (SELECT 1
2254 FROM wsh_delivery_details_ob_grp_v wdd,
2255 wsh_delivery_assignments_v wda
2256 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2257 AND wda.delivery_id = p_delivery_id
2258 AND wdd.container_flag = 'N'
2259 AND (wdd.released_status NOT IN ( 'X', 'Y' )
2260 OR (Nvl(oe_dual_uom_util.GET_FULFILLMENT_BASE(wdd.source_line_id), 'P') = 'P'
2261 AND (wdd.cycle_count_quantity > 0
2262 OR wdd.shipped_quantity < wdd.requested_quantity
2263 )
2264 )
2265 OR (Nvl(oe_dual_uom_util.GET_FULFILLMENT_BASE(wdd.source_line_id), 'P') = 'S'
2266 AND (wdd.cycle_count_quantity2 > 0
2267 OR wdd.shipped_quantity2 < wdd.requested_quantity2
2268 )
2269 )
2270 )
2271 );--16739882
2272 if exist_unqualified = 1 then x_return_Status := 'N'; end if;
2273 EXCEPTION
2274 WHEN NO_DATA_FOUND THEN
2275 x_return_Status := 'Y';
2276 WHEN OTHERS THEN
2277 x_return_Status := 'N';
2278 END CHECK_ENTIRE_EZ_DELIVERY;
2279
2280 PROCEDURE CHECK_DELIVERY_LOADED(
2281 p_delivery_id IN NUMBER,
2282 x_return_Status OUT NOCOPY VARCHAR2) IS
2283 l_loaded VARCHAR2(1) := 'N';
2284 BEGIN
2285 select 'Y'
2286 into l_loaded
2287 from dual
2288 where exists (select 1
2289 from wms_shipping_transaction_temp
2290 where delivery_id = p_delivery_id);
2291 x_return_Status := l_loaded;
2292 EXCEPTION
2293 WHEN NO_DATA_FOUND then
2294 x_return_Status := 'N';
2295 END CHECK_DELIVERY_LOADED;
2296
2297 /*bug13581767
2298 Check whether the Delivery (with the p_delivery_id) is the last delivery with status=Open on corresponding Trip.
2299 If any other delivery exists with status = OPEN in the trip ,then x_return_Status will be 'N'
2300 If no other open deliveries exist or the delivery is not assigned to any Trip, then x_return_Status will be 'Y'
2301 This procudure must be accordant with WSH_NEW_DELIVERY_ACTIONS.check_last_del_trip.
2302 */
2303 PROCEDURE CHECK_LAST_DEL_TRIP(
2304 p_delivery_id IN NUMBER,
2305 x_return_Status OUT NOCOPY VARCHAR2) IS
2306 l_is_last VARCHAR2(1) := 'N';
2307 l_trip_id NUMBER;
2308 CURSOR Check_Last_Trip (l_delivery_id NUMBER) IS
2309 SELECT s1.trip_id
2310 FROM wsh_trip_stops s1,
2311 wsh_delivery_legs dl1,
2312 wsh_new_deliveries d1,
2313 wsh_trip_stops s2,
2314 wsh_delivery_legs dl2
2315 WHERE d1.delivery_id <> l_delivery_id
2316 AND s1.stop_id = dl1.pick_up_stop_id
2317 AND d1.delivery_id = dl1.delivery_id
2318 AND d1.status_code = 'OP'
2319 AND d1.delivery_type = 'STANDARD'
2320 AND s2.trip_id = s1.trip_id
2321 AND s2.stop_id = dl2.pick_up_stop_id
2322 AND dl2.delivery_id = l_delivery_id
2323 AND rownum = 1;
2324 BEGIN
2325
2326 OPEN check_last_trip(p_delivery_id);
2327
2328 FETCH check_last_trip
2329 INTO l_trip_id;
2330 CLOSE check_last_trip;
2331
2332 IF l_trip_id IS NOT NULL THEN
2333 debug('The delivery-'||p_delivery_id||' is not the last delivery on Trip-'||l_trip_id, 'CHECK_LAST_DEL_TRIP');
2334 l_is_last := 'N';
2335 ELSE
2336 debug('The delivery-'||p_delivery_id||' is the last Open delivery', 'CHECK_LAST_DEL_TRIP');
2337 l_is_last := 'Y';
2338 END IF;
2339 x_return_Status := l_is_last;
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 debug('EXCEPTION:OTHERS.', 'CHECK_LAST_DEL_TRIP');
2343 x_return_Status := 'N';
2344
2345 END CHECK_LAST_DEL_TRIP;
2346 --end bug13581767
2347 PROCEDURE CHECK_EZ_SHIP_DELIVERY(p_delivery_id IN NUMBER,
2348 x_item_name OUT NOCOPY VARCHAR2,
2349 x_return_Status OUT NOCOPY VARCHAR2,
2350 x_error_code OUT NOCOPY NUMBER,
2351 x_error_msg OUT NOCOPY VARCHAR2) IS
2352
2353 l_return_status VARCHAR2(1);
2354 l_result NUMBER;
2355 l_item_name VARCHAR2(2000);
2356 l_organization_id NUMBER;
2357 l_allow_shipping VARCHAR2(1);
2358
2359 l_msg_count NUMBER;
2360
2361 l_wms_org_flag BOOLEAN;
2362 l_action_prms wsh_interface_ext_grp.del_action_parameters_rectype;
2363 l_delivery_id_tab wsh_util_core.id_tab_type;
2364 l_delivery_out_rec wsh_interface_ext_grp.del_action_out_rec_type;
2365
2366 --Added for Bug 14696492
2367 CURSOR get_dlvy_trip(p_delivery_id NUMBER) IS
2368 SELECT wts.trip_id, wt.name
2369 FROM wsh_trip_stops wts, wsh_trips wt, wsh_delivery_legs wdl
2370 WHERE wdl.delivery_id = p_delivery_id
2371 AND wdl.pick_up_stop_id = wts.stop_id
2372 AND wts.trip_id = wt.trip_id;
2373 l_mul_del_trip VARCHAR2(1) := 'N';
2374 l_trip_id NUMBER;
2375 l_trip_name VARCHAR2(30);
2376 l_del_leg_count NUMBER := 0;
2377 -- End for Bug 14696492
2378
2379 BEGIN
2380 x_return_Status := 'Y';
2381 x_error_code := 0; -- everything is fine
2382 -- Locked the record first, so that others will not able to ship the same delivery
2383 BEGIN
2384 select organization_id
2385 into l_organization_id
2386 from wsh_new_deliveries_ob_grp_v
2387 where delivery_id = p_delivery_id
2388 for update NOWAIT;
2389 EXCEPTION WHEN others THEN
2390 x_return_Status := 'N';
2391 x_error_code := 5;
2392 return;
2393 END;
2394
2395 -- Fix for Bug 14696492
2396 -- Check if delivery is assigned to trip which has additional deliveries
2397 BEGIN
2398 SELECT count(*) INTO l_del_leg_count
2399 FROM wsh_delivery_legs
2400 WHERE delivery_id = p_delivery_id;
2401 IF l_del_leg_count > 1 THEN
2402 x_return_Status := 'N';
2403 x_error_code := 8;
2404 return;
2405 ELSIF l_del_leg_count = 1 THEN
2406 OPEN get_dlvy_trip(p_delivery_id);
2407 FETCH get_dlvy_trip INTO l_trip_id, l_trip_name;
2408 CLOSE get_dlvy_trip;
2409
2410 BEGIN
2411 SELECT 'Y'
2412 INTO l_mul_del_trip
2413 FROM wsh_trip_stops wts, wsh_delivery_legs wdl
2414 WHERE wts.trip_id = l_trip_id
2415 AND wdl.pick_up_stop_id = wts.stop_id
2416 AND wdl.delivery_id <> p_delivery_id;
2417 EXCEPTION
2418 WHEN NO_DATA_FOUND THEN
2419 l_mul_del_trip := 'N';
2420 WHEN TOO_MANY_ROWS THEN
2421 l_mul_del_trip := 'Y';
2422 END;
2423 IF l_mul_del_trip = 'Y' THEN
2424 x_return_Status := 'N';
2425 x_error_code := 7;
2426 return;
2427 END IF;
2428 END IF;
2429 END;
2430 -- End of fix for Bug 14696492
2431
2432 -- First check if the entire delivery is ready to be ship confirmed
2433 CHECK_ENTIRE_EZ_DELIVERY(
2434 p_delivery_id,
2435 x_return_Status,
2436 x_error_msg);
2437 if x_return_Status = 'N' then
2438 x_return_Status := 'N';
2439 x_error_code := 1; -- not entire delivery is ready
2440 return;
2441 end if;
2442 -- check if this delivery is loaded to any dock and delivery status
2443 if (inv_install.adv_inv_installed(p_organization_id=>null) = TRUE ) then
2444 CHECK_DELIVERY_LOADED(p_delivery_id => p_delivery_id,
2445 x_return_Status => l_return_status);
2446 if l_return_status = 'Y' then
2447 x_return_Status := 'N';
2448 x_error_code := 4;
2449 end if;
2450
2451 CHECK_DELIVERY_STATUS(p_delivery_id => p_delivery_id,
2452 x_return_Status => l_return_status,
2453 x_error_msg => x_error_msg );
2454 if l_return_status = 'E' then
2455 x_return_Status := 'N';
2456 x_error_code := 2; -- status doesn't allow ship confirm
2457 return;
2458 end if;
2459 end if;
2460
2461 -- check serial control at sales issue
2462 SERIAL_AT_SALES_CHECK(x_result => l_result,
2463 x_item_name => l_item_name,
2464 p_delivery_id => p_delivery_id );
2465 if (l_result = 1) then
2466 x_return_Status := 'N';
2467 x_error_code := 3; -- serial control at issue
2468 x_item_name := l_item_name;
2469 return;
2470 end if;
2471
2472 -- Check if the LPN which this delivery is contained in has material for
2473 -- other deliveries
2474 wms_mdc_pvt.can_ship_delivery(p_delivery_id => p_delivery_id,
2475 x_allow_shipping => l_allow_shipping,
2476 x_return_status => x_return_status,
2477 x_msg_count => l_msg_count,
2478 x_msg_data => x_error_msg);
2479
2480 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
2481 RAISE fnd_api.g_exc_error;
2482 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2483 RAISE fnd_api.g_exc_unexpected_error;
2484 END IF;
2485
2486 IF l_allow_shipping <> 'Y' THEN
2487 x_error_code := 6; -- Delivery is a part of consol delivery
2488 x_return_Status := 'N';
2489 RETURN;
2490 END IF;
2491
2492 --- <Changes for Delivery Merge>
2493 l_wms_org_flag := wms_install.check_install(x_return_status => x_return_status,
2494 x_msg_count => l_msg_count,
2495 x_msg_data => x_error_msg,
2496 p_organization_id => l_organization_id);
2497
2498 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2499 RAISE FND_API.G_EXC_ERROR;
2500 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2502 END IF;
2503
2504 IF (NOT l_wms_org_flag AND g_inv_current_release_level >= g_j_release_level)
2505 OR (l_wms_org_flag AND g_wms_current_release_level >= g_j_release_level) THEN
2506 l_action_prms.caller := 'WMS_DLMG';
2507 l_action_prms.event := wsh_interface_ext_grp.g_start_of_shipping;
2508 l_action_prms.action_code := 'ADJUST-PLANNED-FLAG';
2509
2510 l_delivery_id_tab(1) := p_delivery_id;
2511
2512 wsh_interface_ext_grp.Delivery_Action
2513 (p_api_version_number => 1.0,
2514 p_init_msg_list => fnd_api.g_false,
2515 p_commit => fnd_api.g_false,
2516 p_action_prms => l_action_prms,
2517 p_delivery_id_tab => l_delivery_id_tab,
2518 x_delivery_out_rec => l_delivery_out_rec,
2519 x_return_status => l_return_status,
2520 x_msg_count => l_msg_count,
2521 x_msg_data => x_error_msg);
2522 -- We do not error out even if the API returns failure
2523 END IF;
2524 -- </Changes for delivery merge>
2525
2526 EXCEPTION
2527 WHEN NO_DATA_FOUND THEN
2528 x_return_Status := 'Y';
2529 WHEN OTHERS THEN
2530 x_return_Status := 'N';
2531 END CHECK_EZ_SHIP_DELIVERY;
2532
2533
2534
2535 PROCEDURE CONFIRM_DELIVERY (
2536 p_ship_delivery IN VARCHAR2 DEFAULT NULL,
2537 p_delivery_id IN NUMBER,
2538 p_organization_id IN NUMBER,
2539 p_delivery_name IN VARCHAR2,
2540 p_carrier_id IN NUMBER,
2541 p_ship_method_code IN VARCHAR2,
2542 p_gross_weight IN NUMBER,
2543 p_gross_weight_uom IN VARCHAR2,
2544 p_bol IN VARCHAR2,
2545 p_waybill IN VARCHAR2,
2546 p_action_flag IN VARCHAR2,
2547 x_return_status OUT NOCOPY VARCHAR2,
2548 x_ret_code OUT NOCOPY NUMBER,
2549 x_msg_data OUT NOCOPY VARCHAR2,
2550 x_msg_count OUT NOCOPY NUMBER) IS
2551
2552 l_ship_set VARCHAR2(2000) := NULL;
2553 l_error_msg VARCHAR2(2000) := NULL;
2554
2555 unspec_ship_set_exists EXCEPTION;
2556 incomplete_delivery EXCEPTION;
2557
2558 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2559 BEGIN
2560 x_return_status := FND_API.G_RET_STS_SUCCESS;
2561 x_ret_code := 0;
2562 fnd_msg_pub.initialize;
2563
2564 IF l_debug=1 THEN
2565 debug('INV_SHIPPING_TRANSACTION_PUB.CONFIRM_DELIVERY..delivery_id: ' || p_delivery_id, 'confirm_delivery');
2566 END IF;
2567
2568 IF p_ship_delivery = 'YES' THEN
2569 INV_SHIPPING_TRANSACTION_PUB.CHECK_SHIP_SET(
2570 p_delivery_id => p_delivery_id,
2571 x_ship_set => l_ship_set,
2572 x_return_Status => x_return_status,
2573 x_error_msg => l_error_msg);
2574 IF x_return_status = 'E' THEN
2575 FND_MESSAGE.SET_NAME('INV', 'WMS_WSH_SHIPSET_FORCED');
2576 FND_MESSAGE.SET_TOKEN('SHIP_SET_NAME', l_ship_set);
2577 FND_MSG_PUB.ADD;
2578 RAISE unspec_ship_set_exists;
2579 ELSIF x_return_status = 'U' THEN
2580 RAISE fnd_api.g_exc_unexpected_error;
2581 END IF;
2582
2583 INV_SHIPPING_TRANSACTION_PUB.CHECK_COMPLETE_DELVIERY(
2584 p_delivery_id => p_delivery_id,
2585 x_return_Status => x_return_status,
2586 x_error_msg => l_error_msg);
2587 IF x_return_status = 'E' THEN
2588 FND_MESSAGE.SET_NAME('INV', 'WMS_INCOMPLETE_DELI');
2589 FND_MSG_PUB.ADD;
2590 IF l_debug = 1 THEN
2591 debug('check_complete_delivery failed with status E','CONFIRM_DELIVERY');
2592 END IF;
2593 RAISE incomplete_delivery;
2594 ELSIF x_return_status = 'U' THEN
2595 IF l_debug = 1 THEN
2596 debug('check_complete_deliery failed with status U','CONFIRM_DELIVERY');
2597 END IF;
2598 RAISE fnd_api.g_exc_unexpected_error;
2599 END IF;
2600
2601 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM_ALL(
2602 p_delivery_id => p_delivery_id,
2603 p_organization_id => p_organization_id,
2604 p_delivery_name => p_delivery_name,
2605 p_carrier_id => p_carrier_id,
2606 p_ship_method_code => p_ship_method_code,
2607 p_gross_weight => p_gross_weight,
2608 p_gross_weight_uom => p_gross_weight_uom,
2609 p_bol => p_bol,
2610 p_waybill => p_waybill,
2611 p_action_flag => p_action_flag,
2612 x_return_status => x_return_status,
2613 x_msg_data => x_msg_data,
2614 x_msg_count => x_msg_count);
2615
2616 ELSE
2617 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM(
2618 p_delivery_id => p_delivery_id,
2619 p_organization_id => p_organization_id,
2620 p_delivery_name => p_delivery_name,
2621 p_carrier_id => p_carrier_id,
2622 p_ship_method_code => p_ship_method_code,
2623 p_gross_weight => p_gross_weight,
2624 p_gross_weight_uom => p_gross_weight_uom,
2625 p_bol => p_bol,
2626 p_waybill => p_waybill,
2627 p_action_flag => p_action_flag,
2628 x_return_status => x_return_status,
2629 x_msg_data => x_msg_data,
2630 x_msg_count => x_msg_count);
2631 END IF;
2632
2633 IF x_return_status not in ('S','W') THEN
2634 RAISE fnd_api.g_exc_unexpected_error;
2635 END IF;
2636
2637 EXCEPTION
2638 WHEN unspec_ship_set_exists THEN
2639 x_return_status := fnd_api.g_ret_sts_error;
2640 x_ret_code := 1;
2641
2642 -- Get message count and data
2643 fnd_msg_pub.count_and_get
2644 ( p_count => x_msg_count
2645 , p_data => x_msg_data
2646 );
2647
2648 WHEN incomplete_delivery THEN
2649 x_return_status := fnd_api.g_ret_sts_error;
2650 x_ret_code := 2;
2651
2652 -- Get message count and data
2653 fnd_msg_pub.count_and_get
2654 ( p_count => x_msg_count
2655 , p_data => x_msg_data
2656 );
2657
2658 WHEN OTHERS THEN
2659 x_return_status := fnd_api.g_ret_sts_unexp_error;
2660
2661 END CONFIRM_DELIVERY;
2662
2663
2664 PROCEDURE UNASSIGN_LINES_AND_CONFIRM (
2665 p_delivery_id IN NUMBER,
2666 p_organization_id IN NUMBER,
2667 p_delivery_name IN VARCHAR2,
2668 p_carrier_id IN NUMBER,
2669 p_ship_method_code IN VARCHAR2,
2670 p_gross_weight IN NUMBER,
2671 p_gross_weight_uom IN VARCHAR2,
2672 p_bol IN VARCHAR2,
2673 p_waybill IN VARCHAR2,
2674 p_action_flag IN VARCHAR2,
2675 x_return_status OUT NOCOPY VARCHAR2,
2676 x_msg_data OUT NOCOPY VARCHAR2,
2677 x_msg_count OUT NOCOPY NUMBER) IS
2678 l_error_msg VARCHAR2(2000) := NULL;
2679 unassign_lines_exc EXCEPTION;
2680 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2681 BEGIN
2682 fnd_msg_pub.initialize;
2683
2684 INV_SHIPPING_TRANSACTION_PUB.UNASSIGN_DELIVERY_LINES(
2685 p_delivery_id => p_delivery_id,
2686 x_return_Status => x_return_status,
2687 x_error_msg => l_error_msg);
2688 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2689 RAISE unassign_lines_exc;
2690 END IF;
2691
2692 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM_ALL(
2693 p_delivery_id => p_delivery_id,
2694 p_organization_id => p_organization_id,
2695 p_delivery_name => p_delivery_name,
2696 p_carrier_id => p_carrier_id,
2697 p_ship_method_code => p_ship_method_code,
2698 p_gross_weight => p_gross_weight,
2699 p_gross_weight_uom => p_gross_weight_uom,
2700 p_bol => p_bol,
2701 p_waybill => p_waybill,
2702 p_action_flag => p_action_flag,
2703 x_return_status => x_return_status,
2704 x_msg_data => x_msg_data,
2705 x_msg_count => x_msg_count);
2706
2707 IF l_debug = 1 THEN
2708 debug('Return status after SHIP_CONFIRM_ALL :' || x_return_status ,'UNASSIGN_LINES_AND_CONFIRM ');
2709 END IF;
2710
2711 IF x_return_status not in ('S','W') THEN
2712 RAISE fnd_api.g_exc_unexpected_error;
2713 END IF;
2714
2715 EXCEPTION
2716 WHEN unassign_lines_exc THEN
2717 x_return_status := fnd_api.g_ret_sts_error;
2718
2719 WHEN OTHERS THEN
2720 x_return_status := fnd_api.g_ret_sts_unexp_error;
2721
2722 -- Get message count and data
2723 fnd_msg_pub.count_and_get
2724 ( p_count => x_msg_count
2725 , p_data => x_msg_data
2726 );
2727
2728 END UNASSIGN_LINES_AND_CONFIRM;
2729
2730 PROCEDURE INV_SPLIT_DELIVERY_LINE(
2731 p_delivery_detail_id IN NUMBER,
2732 p_ship_quantity IN NUMBER,
2733 p_requested_quantity IN NUMBER,
2734 x_return_status OUT NOCOPY VARCHAR2,
2735 x_msg_count OUT NOCOPY NUMBER,
2736 x_msg_data OUT NOCOPY VARCHAR2,
2737 x_new_delivery_detail_id OUT NOCOPY NUMBER,
2738 x_new_transaction_temp_id OUT NOCOPY NUMBER)
2739 IS
2740 l_return_status VARCHAR2(1);
2741 l_msg_count NUMBER;
2742 l_msg_data VARCHAR2(2000);
2743
2744 l_new_transaction_temp_id NUMBER;
2745 l_delivery_detail_id NUMBER := p_delivery_detail_id;
2746 l_shipped_quantity NUMBER := p_ship_quantity;
2747 l_requested_quantity NUMBER := p_requested_quantity;
2748 l_new_delivery_line_id NUMBER;
2749 l_transaction_temp_id NUMBER;
2750 l_delivery_id NUMBER;
2751 l_delay_quantity NUMBER;
2752
2753
2754 l_detail_attributes wsh_interface.ChangedAttributeTabType;
2755 l_InvPCInRecType wsh_integration.InvPCInRecType;
2756 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2757 BEGIN
2758 IF l_debug = 1 THEN
2759 debug('Entered procedure','INV_SPLIT_DELIVERY_LINE');
2760 debug('p_delivery_detail_id: ' || p_delivery_detail_id,'INV_SPLIT_DELIVERY_LINE');
2761 debug('p_ship_quantity: ' || p_ship_quantity,'INV_SPLIT_DELIVERY_LINE');
2762 debug('p_requested_quantity: ' || p_requested_quantity,'INV_SPLIT_DELIVERY_LINE');
2763 END IF;
2764
2765 --initalizing l_InvPCInRecType to use for updating wdd with transaction_temp_id
2766 l_InvPCInRecType.transaction_id := NULL;
2767 l_InvPCInRecType.transaction_temp_id := NULL;
2768 l_InvPCInRecType.source_code :='INV';
2769 l_InvPCInRecType.api_version_number :=1.0;
2770
2771 l_return_status := 'S';
2772
2773 select transaction_temp_id
2774 into l_transaction_temp_id
2775 From wsh_delivery_details_ob_grp_v
2776 where delivery_detail_id = l_delivery_detail_id;
2777
2778 l_delay_quantity := l_requested_quantity - l_shipped_quantity;
2779
2780 WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details
2781 (p_from_detail_id => l_delivery_detail_id,
2782 p_req_quantity => l_delay_quantity,
2783 x_new_detail_id => l_new_delivery_line_id,
2784 x_return_status => l_return_status
2785 );
2786 if l_return_status <> fnd_api.g_ret_sts_success THEN
2787 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2788 end if;
2789
2790 select transaction_temp_id
2791 into l_new_transaction_temp_id
2792 from wsh_delivery_details_ob_grp_v
2793 where delivery_detail_id = l_new_delivery_line_id;
2794
2795 IF (l_debug = 1) THEN
2796 debug('new transaction_temp_id is ' || l_new_transaction_temp_id,
2797 'INV_SPLIT_DELIVERY_LINE');
2798 debug('old transaction_temp_id is ' ||
2799 l_transaction_temp_id,'INV_SPLIT_DELIVERY_LINE');
2800 END IF;
2801
2802 if( l_transaction_temp_id = l_new_transaction_temp_id) then
2803 select mtl_material_transactions_s.nextval
2804 into l_new_transaction_temp_id
2805 from dual;
2806 if( l_debug = 1 ) then
2807 debug('new transaction_temp_id is ' || l_new_transaction_temp_id, 'split_Delivery');
2808 end if;
2809 end if;
2810
2811 IF l_debug =1 THEN
2812 debug('Setting WDD attributes of new line with following:','INV_SPLIT_DELIVERY_LINE');
2813 debug('delivery_detail_id ' || l_new_delivery_line_id,'INV_SPLIT_DELIVERY_LINE');
2814 debug('transaction_temp_id ' || l_new_transaction_temp_id,'INV_SPLIT_DELIVERY_LINE');
2815 debug('shipped_quantity 0','INV_SPLIT_DELIVERY_LINE');
2816 END IF;
2817
2818 l_InvPCInRecType.transaction_temp_id := l_new_transaction_temp_id;
2819
2820 wsh_integration.Set_Inv_PC_Attributes
2821 (p_in_attributes => l_InvPCInRecType,
2822 x_return_status => l_return_status,
2823 x_msg_count => l_msg_count,
2824 x_msg_data => l_msg_data);
2825
2826 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
2827 IF l_debug = 1 THEN
2828 debug('wsh_integration.set_inv_pc_attributes failed'
2829 || ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
2830 END IF;
2831 --check where to handle this error
2832 RAISE fnd_api.g_exc_unexpected_error;
2833 END IF;
2834
2835 l_detail_attributes(1).shipped_quantity := 0;
2836 l_detail_attributes(1).delivery_detail_id := l_new_delivery_line_id;
2837 l_detail_attributes(1).action_flag := 'U';
2838
2839 wsh_interface.update_shipping_attributes
2840 (x_return_status => l_return_status,
2841 p_changed_attributes => l_detail_attributes,
2842 p_source_code => 'INV');
2843
2844 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
2845 IF l_debug = 1 THEN
2846 debug('wsh_interface.update_shipping_attributes failed'
2847 || ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
2848 END IF;
2849 --check where to handle this error
2850 RAISE fnd_api.g_exc_unexpected_error;
2851 END IF;
2852
2853 x_return_status := FND_API.G_RET_STS_SUCCESS;
2854 x_new_delivery_detail_id := l_new_delivery_line_id;
2855 x_new_transaction_temp_id := l_new_transaction_temp_id;
2856 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2857 EXCEPTION
2858 WHEN FND_API.G_EXC_ERROR THEN
2859 x_return_status := FND_API.G_RET_STS_ERROR;
2860 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2861 END INV_SPLIT_DELIVERY_LINE;
2862
2863
2864 /**
2865 Bug no 3952081
2866 Overriding the procedure INV_SPLIT_DELIVERY_LINE to include
2867 DUOM attributes as input arguments
2868 **/
2869 PROCEDURE INV_SPLIT_DELIVERY_LINE(
2870 p_delivery_detail_id IN NUMBER,
2871 p_ship_quantity IN NUMBER,
2872 p_requested_quantity IN NUMBER,
2873 p_sec_ship_quantity IN NUMBER,
2874 p_sec_requested_quantity IN NUMBER,
2875 x_return_status OUT NOCOPY VARCHAR2,
2876 x_msg_count OUT NOCOPY NUMBER,
2877 x_msg_data OUT NOCOPY VARCHAR2,
2878 x_new_delivery_detail_id OUT NOCOPY NUMBER,
2879 x_new_transaction_temp_id OUT NOCOPY NUMBER)
2880 IS
2881 l_return_status VARCHAR2(1);
2882 l_msg_count NUMBER;
2883 l_msg_data VARCHAR2(2000);
2884
2885 l_new_transaction_temp_id NUMBER;
2886 l_delivery_detail_id NUMBER := p_delivery_detail_id;
2887 l_shipped_quantity NUMBER := p_ship_quantity;
2888 l_requested_quantity NUMBER := p_requested_quantity;
2889 l_new_delivery_line_id NUMBER;
2890 l_transaction_temp_id NUMBER;
2891 l_delivery_id NUMBER;
2892 l_delay_quantity NUMBER;
2893
2894
2895 l_detail_attributes wsh_interface.ChangedAttributeTabType;
2896 l_InvPCInRecType wsh_integration.InvPCInRecType;
2897 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2898
2899 --Bug No 3952081
2900 --New field to hol sec delay qty
2901 l_sec_delay_quantity NUMBER;
2902 BEGIN
2903 IF l_debug = 1 THEN
2904 debug('Entered procedure','INV_SPLIT_DELIVERY_LINE');
2905 debug('p_delivery_detail_id: ' || p_delivery_detail_id,'INV_SPLIT_DELIVERY_LINE');
2906 debug('p_ship_quantity: ' || p_ship_quantity,'INV_SPLIT_DELIVERY_LINE');
2907 debug('p_requested_quantity: ' || p_requested_quantity,'INV_SPLIT_DELIVERY_LINE');
2908 END IF;
2909
2910 --initalizing l_InvPCInRecType to use for updating wdd with transaction_temp_id
2911 l_InvPCInRecType.transaction_id := NULL;
2912 l_InvPCInRecType.transaction_temp_id := NULL;
2913 l_InvPCInRecType.source_code :='INV';
2914 l_InvPCInRecType.api_version_number :=1.0;
2915
2916 l_return_status := 'S';
2917
2918 select transaction_temp_id
2919 into l_transaction_temp_id
2920 From wsh_delivery_details_ob_grp_v
2921 where delivery_detail_id = l_delivery_detail_id;
2922
2923 l_delay_quantity := l_requested_quantity - l_shipped_quantity;
2924
2925 /* Calculate the value for l_sec_delay_quantity */
2926 l_sec_delay_quantity := p_sec_requested_quantity - p_sec_ship_quantity;
2927
2928 /* Changes in call to WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details */
2929 IF l_sec_delay_quantity IS NOT NULL THEN
2930 IF l_debug = 1 THEN
2931 debug('Setting Secondary Delay Quantity: ' || l_sec_delay_quantity,'INV_SPLIT_DELIVERY_LINE');
2932 END IF;
2933
2934 /* pass on the secondary delay quantity to WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details */
2935
2936 WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details
2937 (p_from_detail_id => l_delivery_detail_id,
2938 p_req_quantity => l_delay_quantity,
2939 p_req_quantity2 => l_sec_delay_quantity,
2940 x_new_detail_id => l_new_delivery_line_id,
2941 x_return_status => l_return_status
2942 );
2943 ELSE
2944 /* call WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details without secondary delay quantity */
2945 WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details
2946 (p_from_detail_id => l_delivery_detail_id,
2947 p_req_quantity => l_delay_quantity,
2948 x_new_detail_id => l_new_delivery_line_id,
2949 x_return_status => l_return_status
2950 );
2951 END IF;
2952
2953
2954 if l_return_status <> fnd_api.g_ret_sts_success THEN
2955 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2956 end if;
2957
2958 select transaction_temp_id
2959 into l_new_transaction_temp_id
2960 from wsh_delivery_details_ob_grp_v
2961 where delivery_detail_id = l_new_delivery_line_id;
2962
2963 IF (l_debug = 1) THEN
2964 debug('new transaction_temp_id is ' || l_new_transaction_temp_id,
2965 'INV_SPLIT_DELIVERY_LINE');
2966 debug('old transaction_temp_id is ' ||
2967 l_transaction_temp_id,'INV_SPLIT_DELIVERY_LINE');
2968 END IF;
2969
2970 if( l_transaction_temp_id = l_new_transaction_temp_id) then
2971 select mtl_material_transactions_s.nextval
2972 into l_new_transaction_temp_id
2973 from dual;
2974 if( l_debug = 1 ) then
2975 debug('new transaction_temp_id is ' || l_new_transaction_temp_id, 'split_Delivery');
2976 end if;
2977 end if;
2978
2979 IF l_debug =1 THEN
2980 debug('Setting WDD attributes of new line with following:','INV_SPLIT_DELIVERY_LINE');
2981 debug('delivery_detail_id ' || l_new_delivery_line_id,'INV_SPLIT_DELIVERY_LINE');
2982 debug('transaction_temp_id ' || l_new_transaction_temp_id,'INV_SPLIT_DELIVERY_LINE');
2983 debug('shipped_quantity 0','INV_SPLIT_DELIVERY_LINE');
2984 END IF;
2985
2986 l_InvPCInRecType.transaction_temp_id := l_new_transaction_temp_id;
2987
2988 wsh_integration.Set_Inv_PC_Attributes
2989 (p_in_attributes => l_InvPCInRecType,
2990 x_return_status => l_return_status,
2991 x_msg_count => l_msg_count,
2992 x_msg_data => l_msg_data);
2993
2994 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
2995 IF l_debug = 1 THEN
2996 debug('wsh_integration.set_inv_pc_attributes failed'
2997 || ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
2998 END IF;
2999 --check where to handle this error
3000 RAISE fnd_api.g_exc_unexpected_error;
3001 END IF;
3002
3003 l_detail_attributes(1).shipped_quantity := 0;
3004 l_detail_attributes(1).delivery_detail_id := l_new_delivery_line_id;
3005 l_detail_attributes(1).action_flag := 'U';
3006
3007 wsh_interface.update_shipping_attributes
3008 (x_return_status => l_return_status,
3009 p_changed_attributes => l_detail_attributes,
3010 p_source_code => 'INV');
3011
3012 IF l_return_status IN (G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR) THEN
3013 IF l_debug = 1 THEN
3014 debug('wsh_interface.update_shipping_attributes failed'
3015 || ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
3016 END IF;
3017 --check where to handle this error
3018 RAISE fnd_api.g_exc_unexpected_error;
3019 END IF;
3020
3021 x_return_status := FND_API.G_RET_STS_SUCCESS;
3022 x_new_delivery_detail_id := l_new_delivery_line_id;
3023 x_new_transaction_temp_id := l_new_transaction_temp_id;
3024 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3025 EXCEPTION
3026 WHEN FND_API.G_EXC_ERROR THEN
3027 x_return_status := FND_API.G_RET_STS_ERROR;
3028 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3029 END INV_SPLIT_DELIVERY_LINE;
3030
3031 PROCEDURE INV_PROCESS_SERIALS(
3032 p_transaction_temp_id IN NUMBER,
3033 p_delivery_detail_id IN NUMBER,
3034 x_return_status OUT NOCOPY VARCHAR2,
3035 x_msg_count OUT NOCOPY NUMBER,
3036 x_msg_data OUT NOCOPY VARCHAR2)
3037 IS
3038 l_transaction_temp_id NUMBER := p_transaction_temp_id;
3039 l_delivery_detail_id NUMBER := p_delivery_detail_id;
3040 l_count NUMBER;
3041 BEGIN
3042 select count(*)
3043 into l_count
3044 from mtl_serial_numbers_temp msnt, wsh_delivery_details_ob_grp_v wdd
3045 where msnt.transaction_temp_id = wdd.transaction_temp_id
3046 and wdd.delivery_detail_id = l_delivery_detail_id
3047 and wdd.transaction_temp_id = l_transaction_temp_id;
3048
3049 if( l_count > 0 ) then
3050 delete from mtl_serial_numbers_temp
3051 where transaction_temp_id in (select transaction_temp_id
3052 From wsh_delivery_details_ob_grp_v
3053 where transaction_temp_id = l_transaction_temp_id
3054 And delivery_detail_id = l_delivery_detail_id);
3055 end if;
3056 x_return_status := FND_API.G_RET_STS_SUCCESS;
3057 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3058
3059 EXCEPTION
3060 when FND_API.G_EXC_ERROR THEN
3061 x_return_status := FND_API.G_RET_STS_ERROR;
3062 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3063 when others then
3064 x_return_status := FND_API.G_RET_STS_ERROR;
3065 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3066 END INV_PROCESS_SERIALS;
3067
3068
3069 PROCEDURE get_enforce_ship(p_org_id IN NUMBER,
3070 x_enforce_ship OUT NOCOPY VARCHAR2,
3071 x_return_status OUT nocopy VARCHAR2,
3072 x_msg_data OUT nocopy VARCHAR,
3073 x_msg_count OUT nocopy NUMBER) IS
3074 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3075 l_shipping_params WSH_SHIPPING_PARAMS_GRP.Global_Params_Rec;
3076 BEGIN
3077 x_return_status := FND_API.G_RET_STS_SUCCESS;
3078
3079 --Changed to call Shipping's API because they
3080 --moved a few columns from wsh_shipping_parameters
3081 --to a new table called wsh_global_parameters in patchset J.
3082 WSH_SHIPPING_PARAMS_GRP.get_global_parameters
3083 (x_global_param_info=>l_shipping_params,
3084 x_return_status => x_return_status);
3085
3086 x_enforce_ship := l_shipping_params.ENFORCE_SHIP_METHOD;
3087
3088 IF x_enforce_ship IS NULL THEN
3089 x_enforce_ship := 'N';
3090 END IF;
3091
3092 IF (l_debug = 1) THEN
3093 debug('Shipping API returned status: ' || x_return_status,'get_enforce_ship');
3094 debug('Enforce ship Y/N : ' || x_enforce_ship, 'get_enforce_ship');
3095 END IF;
3096 EXCEPTION
3097 WHEN others THEN
3098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3099 x_enforce_ship := 'N';
3100 END get_enforce_ship;
3101
3102 /** This procedure gets the enforce_ship_method parameter from shipping
3103 * and Ship Method at trip level, if trip exists for this Delivery**/
3104 PROCEDURE get_shipmethod_details
3105 (p_org_id IN NUMBER,
3106 p_delivery_id IN NUMBER,
3107 p_enforce_shipmethod IN OUT NOCOPY VARCHAR2,
3108 p_trip_id IN OUT NOCOPY NUMBER,
3109 x_trip_shipmethod_code OUT NOCOPY VARCHAR2,
3110 x_trip_shipmethod_meaning OUT NOCOPY VARCHAR2,
3111 x_return_status OUT NOCOPY VARCHAR2,
3112 x_msg_data OUT NOCOPY VARCHAR,
3113 x_msg_count OUT NOCOPY NUMBER) IS
3114 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3115 BEGIN
3116 IF (l_debug = 1) THEN
3117 debug('inside get_shipmethod_details ' , 'get_shipmethod_details');
3118 debug('p_org_id : ' || p_org_id, 'get_shipmethod_details');
3119 debug('p_delivery_id : ' || p_delivery_id, 'get_shipmethod_details');
3120 debug('p_enforce_shipmethod : ' || p_enforce_shipmethod, 'get_shipmethod_details');
3121 debug('p_trip_id : ' || p_trip_id, 'get_shipmethod_details');
3122 END IF;
3123
3124 x_return_status := FND_API.G_RET_STS_SUCCESS;
3125
3126 IF p_enforce_shipmethod is NULL THEN
3127 SELECT enforce_ship_method
3128 INTO p_enforce_shipmethod
3129 FROM wsh_global_parameters ; -- changed from wsh_shipping_parameters
3130 END IF;
3131
3132 IF p_enforce_shipmethod IS NULL THEN p_enforce_shipmethod := 'N' ; END IF;
3133
3134 IF (l_debug = 1) THEN
3135 debug('Enforce ship Y/N : ' || p_enforce_shipmethod, 'get_shipmethod_details');
3136 END IF;
3137
3138 BEGIN
3139 SELECT wt.ship_method_code,
3140 wt.trip_id
3141 INTO x_trip_shipmethod_code,
3142 p_trip_id
3143 FROM wsh_new_deliveries del,
3144 wsh_delivery_legs dlg,
3145 wsh_trip_stops st,
3146 wsh_trips wt
3147 WHERE del.delivery_id = dlg.delivery_id
3148 AND dlg.pick_up_stop_id = st.stop_id
3149 AND del.initial_pickup_location_id = st.stop_location_id
3150 AND st.trip_id = wt.trip_id
3151 AND del.delivery_id = p_delivery_id
3152 AND rownum = 1;
3153
3154 x_trip_shipmethod_meaning := GET_SHIPMETHOD_MEANING(x_trip_shipmethod_code);
3155 IF (l_debug = 1) THEN
3156 debug('x_tripshipmethod_code : ' || x_trip_shipmethod_code, 'get_shipmethod_details');
3157 debug('x_tripshipmethod_meaning : ' || x_trip_shipmethod_meaning, 'get_shipmethod_details');
3158 debug('p_trip_id : ' || p_trip_id, 'get_shipmethod_details');
3159 END IF;
3160 EXCEPTION
3161 WHEN NO_DATA_FOUND THEN
3162 x_trip_shipmethod_code := NULL;
3163 p_trip_id := NULL;
3164 IF (l_debug = 1) THEN
3165 debug('Trip Not found for this Delivery ' , 'get_shipmethod_details');
3166 debug('x_trip_shipmethod_code : ' || x_trip_shipmethod_code, 'get_shipmethod_details');
3167 debug('p_trip_id : ' || p_trip_id, 'get_shipmethod_details');
3168 END IF;
3169 END ;
3170
3171 IF (l_debug = 1) THEN
3172 debug('Going ou ' , 'get_shipmethod_details');
3173 END IF;
3174 END get_shipmethod_details;
3175
3176 -- Start of fix for 4629955
3177 FUNCTION GET_FREIGHT_CODE(p_carrier_id IN NUMBER)
3178 RETURN VARCHAR2 IS
3179 l_freight_code wsh_carriers.freight_code%TYPE;
3180 BEGIN
3181 if p_carrier_id is null then
3182 return null;
3183 else
3184 select freight_code
3185 into l_freight_code
3186 from wsh_carriers
3187 where carrier_id=p_carrier_id;
3188 end if;
3189 return l_freight_code;
3190 EXCEPTION
3191 WHEN OTHERS THEN
3192 return null;
3193 END GET_FREIGHT_CODE;
3194 -- End of fix for 4629955
3195
3196 /* The following API will get the secondary shipped qty
3197 by taking the lot specific conversion defined, if any, into account
3198 Return values and meanings :
3199 -1 - No conversion defined
3200 Any 0 or +ve value - The secondary qty
3201 */
3202
3203 FUNCTION is_lotspec_conv(p_delivery_detail_id IN NUMBER, x_lot_number OUT NOCOPY VARCHAR2) RETURN NUMBER IS
3204 l_lot_number VARCHAR2(80) := null;
3205 BEGIN
3206
3207 SELECT lot_number INTO l_lot_number
3208 FROM wsh_delivery_details
3209 WHERE delivery_detail_id = p_delivery_detail_id
3210 AND lot_number IS NOT NULL;
3211
3212 BEGIN
3213 /* If this returns values, that means no lot_specific conversion defined, so return 0*/
3214 SELECT wdd.lot_number INTO l_lot_number
3215 FROM mtl_lot_uom_class_conversions lsc
3216 , mtl_uom_class_conversions uc
3217 , wsh_delivery_details wdd
3218 WHERE lsc.inventory_item_id = uc.inventory_item_id
3219 AND uc.from_uom_code = lsc.from_uom_code
3220 AND uc.to_uom_code = lsc.to_uom_code
3221 AND uc.conversion_rate = lsc.conversion_rate
3222 AND wdd.organization_id = lsc.organization_id
3223 AND wdd.inventory_item_id = lsc.inventory_item_id
3224 AND wdd.lot_number = lsc.lot_number
3225 AND wdd.delivery_detail_id = p_delivery_detail_id
3226 AND wdd.lot_number IS NOT NULL;
3227
3228 x_lot_number := l_lot_number;
3229 return 0;
3230 EXCEPTION
3231 WHEN no_data_found THEN
3232 x_lot_number := l_lot_number;
3233 return 1;
3234 END;
3235 EXCEPTION
3236
3237 WHEN no_data_found THEN /* This means No lot item, so return 0*/
3238 return 0;
3239 WHEN others THEN
3240 return 0;
3241 END is_lotspec_conv;
3242
3243 END INV_SHIPPING_TRANSACTION_PUB;