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