[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_ACTIONS as
2: /* $Header: WSHDDACB.pls 120.50.12020000.14 2013/05/30 13:27:00 skanduku ship $ */
3:
4: -- odaboval : Begin of OPM Changes (Pick_Confirm)
5: G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_ACTIONS';
1: PACKAGE BODY WSH_DELIVERY_DETAILS_ACTIONS as
2: /* $Header: WSHDDACB.pls 120.50.12020000.14 2013/05/30 13:27:00 skanduku ship $ */
3:
4: -- odaboval : Begin of OPM Changes (Pick_Confirm)
5: G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_ACTIONS';
6:
7: G_MAX_DECIMAL_DIGITS CONSTANT NUMBER := 9 ;
8: G_MAX_REAL_DIGITS CONSTANT NUMBER := 10 ;
9: G_RET_WARNING CONSTANT VARCHAR2(1):= 'W';
21: net_weight,
22: volume,
23: container_flag,
24: delivery_id
25: from wsh_Delivery_details wdd,
26: wsh_delivery_assignments_v wda
27: where wdd.delivery_detail_id = detail_id
28: and wdd.delivery_detail_id = wda.delivery_detail_id;
29:
63: Description : This API calls Inventory's APIs to Unreserve. It first
64: queries the reservation records, and then calls
65: delete_reservations until the p_quantity_to_unreserve
66: is satisfied.
67: Parameters : p_delivery_detail_id -> WSH_DELIVERY_DETAILS.delivery_detail_id corresponding to
68: which the reservations are to be Unreserved
69: p_quantity_to_unreserve -> Quantity to Unreserve / Cycle Count
70: p_unreserve_mode -> p_unreserve_mode -> Either 'UNRESERVE' or 'CYCLE_COUNT' or 'RETAIN_RSV'
71: Added 'RETAIN_RSV' for bug 4721577. WMS wants
164: wdd.move_order_line_id,
165: wdd.released_status, -- 2747520
166: wdd.date_scheduled, -- 2847687
167: wda.parent_delivery_detail_id --4721577
168: from wsh_delivery_details wdd ,
169: oe_order_lines_all o,
170: wsh_delivery_assignments_v wda
171: where wdd.delivery_detail_id = p_delivery_Detail_id
172: and wdd.delivery_detail_id = wda.delivery_detail_id
176: l_line_rec c_line_details%ROWTYPE ;
177: -- Bug 4721577
178: CURSOR c_lpn_id (c_delivery_detail_id NUMBER) IS
179: SELECT wdd.lpn_id
180: FROM wsh_delivery_details wdd
181: WHERE wdd.delivery_detail_id = c_delivery_detail_id;
182:
183: -- HW BUG#:2005977 for OPM
184:
190: l_cancel_rsv_type VARCHAR2(1); -- 2747520: 'S'taged or 'U'nstaged
191: -- HW OPMCONV - Added Qty2
192: CURSOR c_nonstaged_qty is
193: SELECT nvl(sum(requested_quantity),0),nvl(sum(requested_quantity2),0)
194: FROM wsh_delivery_details
195: WHERE
196: source_line_id = l_line_rec.line_id
197: and source_header_id = l_line_rec.header_id
198: and organization_id = l_line_rec.organization_id
342:
343: l_rsv_rec.demand_source_header_id := l_sales_order_id;
344: l_rsv_rec.demand_source_line_id := l_line_rec.line_id;
345:
346: -- Bug 1842613 : Initializing inventory controls present in wsh_delivery_details
347: IF ( l_line_rec.organization_id IS NOT NULL ) THEN
348: l_rsv_rec.organization_id := l_line_rec.organization_id;
349: END IF;
350: IF ( l_line_rec.subinventory IS NOT NULL) THEN
461: END IF;
462: --2587777
463:
464: --X-dock
465: IF l_line_rec.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE THEN
466: WSH_USA_INV_PVT.get_putaway_detail_id
467: (p_detail_id => p_delivery_detail_id,
468: p_released_status => l_line_rec.released_status,
469: p_move_order_line_id => l_line_rec.move_order_line_id,
1453: FROM dual
1454: WHERE EXISTS
1455: ( SELECT 'x'
1456: FROM wsh_delivery_assignments wda
1457: , wsh_delivery_details wdd
1458: WHERE wda.delivery_id = p_delv_id
1459: AND (wda.type IN ('S', 'O') OR
1460: wda.type IS NULL)
1461: AND wda.delivery_detail_id = wdd.delivery_detail_id
1467: FROM dual
1468: WHERE EXISTS
1469: ( SELECT 'x'
1470: FROM wsh_delivery_assignments wda
1471: , wsh_delivery_details wdd
1472: WHERE wda.delivery_id = p_delv_id
1473: AND (wda.type IN ('S', 'O') OR
1474: wda.type IS NULL)
1475: AND wda.delivery_detail_id = wdd.delivery_detail_id
1563: END IF;
1564: -- Workflow Changes ==/
1565: --
1566: IF l_debug_on THEN
1567: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1568: END IF;
1569: --
1570:
1571: wsh_delivery_details_actions.assign_top_detail_to_delivery(
1567: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1568: END IF;
1569: --
1570:
1571: wsh_delivery_details_actions.assign_top_detail_to_delivery(
1572: P_DETAIL_ID => l_topmost_delivery_detail_id,
1573: P_DELIVERY_ID => p_delivery_id,
1574: X_RETURN_STATUS => x_return_status,
1575: x_dlvy_has_lines => x_dlvy_has_lines, -- J-IB-NPARIKH
1713: WHEN others THEN
1714: ROLLBACK TO before_assign_topmost_cont;
1715: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1716: wsh_util_core.add_message(x_return_status,l_module_name);
1717: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DELIVERY_DETAIL',l_module_name);
1718:
1719: --
1720: IF l_debug_on THEN
1721: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1784: --
1785: EXCEPTION
1786: WHEN others THEN
1787: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1788: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery',l_module_name);
1789: --
1790: IF l_debug_on THEN
1791: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1792: END IF;
1839: WHERE delivery_detail_id = p_id;
1840:
1841: CURSOR c_get_gross_weight (p_id NUMBER) IS
1842: SELECT nvl(gross_weight, 0)
1843: FROM wsh_delivery_details
1844: WHERE delivery_detail_id = p_id;
1845:
1846: l_is_delivery_empty VARCHAR2(1);
1847: l_delivery1 NUMBER;
2237: l_display_error BOOLEAN;
2238:
2239: CURSOR c_get_shipto (v_container_id NUMBER) IS
2240: SELECT ship_to_location_id
2241: FROM wsh_delivery_details
2242: WHERE delivery_detail_id = v_container_id;
2243:
2244: -- Bug 3715176
2245: CURSOR c_get_plan_flag (v_delivery_id NUMBER) IS
3257: -- End of OTM R12 : assign delivery detail
3258:
3259: -- K: MDC delete consolidation record of it exists
3260: l_mdc_detail_tab(1) := p_detail_id;
3261: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
3262: p_detail_id_tab => l_mdc_detail_tab,
3263: x_return_status => x_return_status);
3264:
3265: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3334: last_update_login = FND_GLOBAL.LOGIN_ID
3335: WHERE delivery_detail_id = p_detail_id;
3336:
3337: l_mdc_detail_tab(1) := p_detail_id;
3338: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
3339: p_detail_id_tab => l_mdc_detail_tab,
3340: x_return_status => x_return_status);
3341:
3342: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3513:
3514: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
3515: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
3516: END LOOP;
3517: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
3518: p_detail_id_tab => l_mdc_detail_tab,
3519: x_return_status => x_return_status);
3520:
3521: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3665: --
3666: RETURN;
3667: WHEN others THEN
3668: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3669: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',l_module_name);
3670: --
3671: IF l_debug_on THEN
3672: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3673: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3714:
3715: cursor l_parent_cnt_csr (p_cnt_inst_id IN NUMBER) is
3716: select organization_id,
3717: nvl(line_direction,'O')
3718: from wsh_delivery_details
3719: where delivery_detail_id = p_cnt_inst_id
3720: and container_flag = 'Y'
3721: and source_code = 'WSH';
3722:
3874: last_updated_by = FND_GLOBAL.USER_ID,
3875: last_update_login = FND_GLOBAL.LOGIN_ID
3876: WHERE delivery_detail_id = p_detail_id;
3877: l_mdc_detail_tab(1) := p_detail_id;
3878: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
3879: p_detail_id_tab => l_mdc_detail_tab,
3880: x_return_status => x_return_status);
3881:
3882: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4040: END IF;
4041: --
4042: RETURN;
4043: WHEN others THEN
4044: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_CONT',l_module_name);
4045: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4046:
4047: --
4048: IF l_debug_on THEN
4102: x_dlvy_freight_terms_code IN OUT NOCOPY VARCHAR2, -- J-IB-NPARIKH
4103: p_caller IN VARCHAR2 --bug 5100229
4104: ) IS
4105:
4106: TYPE del_det_id_tab_type IS TABLE OF wsh_delivery_details.delivery_detail_id%TYPE INDEX BY BINARY_INTEGER;
4107:
4108: l_rowid varchar2(150);
4109: l_delivery_assignment_id NUMBER;
4110: -- cont_id NUMBER;
4126: del_not_updatable exception;
4127: grouping_attributes_not_match exception;
4128: arrival_SET_failed exception;
4129: ship_method_not_match exception;
4130: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
4131: l_mdc_detail_tab wsh_util_core.id_tab_type;
4132: DETAIL_DEL_FROM_to_not_SAME exception;
4133: det_confirmed exception;
4134: update_mol_carton_group_error exception;
4162: mode_of_transport, carrier_id, service_level,
4163: wda.parent_delivery_detail_id, -- K LPN CONV. rv
4164: wdd.gross_weight, -- OTM R12 : assign delivery detail
4165: wdd.client_id -- LSP PROJECT
4166: FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
4167: WHERE wdd.delivery_detail_id = wda.delivery_detail_id and wdd.delivery_detail_id = c_detail_id;
4168:
4169:
4170: l_detail c_detail%ROWTYPE;
4178: CONNECT BY prior delivery_detail_id = parent_delivery_detail_id;
4179:
4180: CURSOR c_getdet_ignore IS
4181: select NVL(ignore_for_planning, 'N') ignore_for_planning
4182: from wsh_delivery_details
4183: where delivery_detail_id=p_detail_id;
4184:
4185: -- J: W/V Changes
4186: l_detail_status VARCHAR2(1);
4252:
4253: -- OTM R12 : assign delivery detail
4254: l_delivery_was_empty BOOLEAN;
4255: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
4256: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4257: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4258: l_delivery_detail_ids WSH_GLBL_VAR_STRCT_GRP.NUM_TBL_TYPE;
4259: l_assign_update BOOLEAN;
4260: -- End of OTM R12 : assign delivery detail
4253: -- OTM R12 : assign delivery detail
4254: l_delivery_was_empty BOOLEAN;
4255: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
4256: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4257: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4258: l_delivery_detail_ids WSH_GLBL_VAR_STRCT_GRP.NUM_TBL_TYPE;
4259: l_assign_update BOOLEAN;
4260: -- End of OTM R12 : assign delivery detail
4261: --
4420: LOOP
4421: FETCH c_all_details_below into l_dd_id(i);
4422: EXIT WHEN c_all_details_below%notfound;
4423: SELECT l_dd_id(i) into l_dummy_del_det_id
4424: FROM wsh_delivery_details
4425: WHERE delivery_detail_id=l_dd_id(i)
4426: FOR UPDATE NOWAIT;
4427: i:=i+1;
4428: END LOOP;
4564: --}
4565: END IF;
4566: -- K LPN CONV. rv
4567:
4568: UPDATE WSH_DELIVERY_DETAILS
4569: SET ship_from_location_id = l_del.initial_pickup_location_id,
4570: ship_to_location_id = l_del.ultimate_dropoff_location_id,
4571: customer_id = l_del.customer_id,
4572: intmed_ship_to_location_id = l_del.intmed_ship_to_location_id,
4882: RAISE NO_DATA_FOUND;
4883: END IF;
4884:
4885: l_mdc_detail_tab(1) := l_detail.delivery_detail_id;
4886: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
4887: p_detail_id_tab => l_mdc_detail_tab,
4888: x_return_status => x_return_status);
4889:
4890: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4961: --
4962: -- J-IB-NPARIKH-}
4963:
4964:
4965: IF (l_detail.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE) THEN
4966: --{
4967: IF (wsh_util_validate.Check_Wms_Org(l_detail.organization_id)='Y') AND
4968: (l_detail.move_order_line_id IS NOT NULL AND
4969: (WSH_USA_INV_PVT.is_mo_type_putaway
5236: --
5237: RETURN;
5238: WHEN others THEN
5239: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5240: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',l_module_name);
5241: --
5242: IF l_debug_on THEN
5243: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5244: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5304: --
5305: EXCEPTION
5306: WHEN others THEN
5307: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5308: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',l_module_name);
5309: --
5310: IF l_debug_on THEN
5311: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5312: END IF;
5370: */
5371:
5372: CURSOR c_get_shipto (p_container_id NUMBER) IS
5373: SELECT ship_to_location_id
5374: FROM wsh_delivery_details
5375: WHERE delivery_detail_id = p_container_id;
5376:
5377: l_container_is_empty BOOLEAN;
5378: l_ship_to_loc NUMBER;
5403: -- when packing a container
5404:
5405: CURSOR Get_Min_Fill(v_cont_id NUMBER) IS
5406: SELECT nvl(minimum_fill_percent,0)
5407: FROM WSH_DELIVERY_DETAILS
5408: WHERE delivery_detail_id = v_cont_id
5409: AND container_flag = 'Y';
5410:
5411: l_min_fill NUMBER;
5421: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5422: l_del_id_for_container1 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5423: l_del_id_for_container2 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5424:
5425: detail_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5426: parent_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5427:
5428: l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
5429: /* H projects: pricing integration csun */
5422: l_del_id_for_container1 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5423: l_del_id_for_container2 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5424:
5425: detail_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5426: parent_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5427:
5428: l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
5429: /* H projects: pricing integration csun */
5430: m NUMBER := 0;
5447:
5448: -- OTM R12 : assign delivery detail
5449: l_delivery_was_empty BOOLEAN;
5450: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
5451: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5452: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5453: l_gc3_is_installed VARCHAR2(1);
5454: l_assign_update BOOLEAN;
5455: -- End of OTM R12 : assign delivery detail
5448: -- OTM R12 : assign delivery detail
5449: l_delivery_was_empty BOOLEAN;
5450: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
5451: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5452: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5453: l_gc3_is_installed VARCHAR2(1);
5454: l_assign_update BOOLEAN;
5455: -- End of OTM R12 : assign delivery detail
5456:
6234:
6235: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
6236: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
6237: END LOOP;
6238: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
6239: p_detail_id_tab => l_mdc_detail_tab,
6240: x_return_status => x_return_status);
6241:
6242: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6398:
6399: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
6400: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
6401: END LOOP;
6402: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
6403: p_detail_id_tab => l_mdc_detail_tab,
6404: x_return_status => x_return_status);
6405: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6406: IF l_debug_on THEN
6479: last_updated_by = FND_GLOBAL.USER_ID,
6480: last_update_login = FND_GLOBAL.LOGIN_ID
6481: WHERE delivery_detail_id = p_detail_id1;
6482: l_mdc_detail_tab(1) := p_detail_id1;
6483: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
6484: p_detail_id_tab => l_mdc_detail_tab,
6485: x_return_status => x_return_status);
6486:
6487: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6531: i NUMBER := 0;
6532: begin
6533:
6534: select container_name into detail_cont_name
6535: from wsh_delivery_details where delivery_detail_id = p_detail_id1;
6536: if SQL%NOTFOUND then
6537: detail_cont_name := '(' || p_detail_id1 || ')';
6538: end if;
6539:
6542: detail_cont_name);
6543: END IF;
6544:
6545: select container_name into parent_cont_name
6546: from wsh_delivery_details where delivery_detail_id = p_detail_id2;
6547: if SQL%NOTFOUND then
6548: detail_cont_name := '(' || p_detail_id2 || ')';
6549: end if;
6550: IF l_debug_on THEN
6642: if c_del_id_for_cont_or_detail%ISOPEN THEN
6643: close c_del_id_for_cont_or_detail;
6644: end if;
6645: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6646: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',l_module_name);
6647: --
6648: IF l_debug_on THEN
6649: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6739: wdd.weight_uom_code,
6740: wdd.volume_uom_code,
6741: wdd.inventory_item_id
6742: FROM wsh_delivery_assignments_v wda,
6743: wsh_delivery_details wdd
6744: WHERE wda.delivery_detail_id = p_detail_id
6745: and wdd.delivery_detail_id = wda.delivery_detail_id
6746: and ((wda.delivery_id IS not null) or (wda.parent_delivery_detail_id IS not null));
6747:
6756: update_mol_carton_group_error exception;
6757:
6758: cursor get_mo_line_id (p_del_det_id IN NUMBER) is
6759: select move_order_line_id, organization_id
6760: from wsh_delivery_details
6761: where delivery_detail_id = p_del_det_id
6762: and released_status = 'S';
6763:
6764: cursor c_grouping_id is
6829:
6830: cursor l_parent_cnt_csr (p_cnt_inst_id IN NUMBER) is
6831: select organization_id,
6832: nvl(line_direction,'O')
6833: from wsh_delivery_details
6834: where delivery_detail_id = p_cnt_inst_id
6835: and container_flag = 'Y'
6836: and source_code = 'WSH';
6837:
6845: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
6846: l_is_delivery_empty VARCHAR2(1);
6847: l_gc3_is_installed VARCHAR2(1);
6848: l_call_update VARCHAR2(1);
6849: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
6850: l_gross_weight_tbl WSH_UTIL_CORE.ID_TAB_TYPE;
6851: -- End of OTM R12 : unassign delivery detail
6852: l_chk_wms_org VARCHAR2(10) := 'N';
6853: l_debug_on BOOLEAN;
7073: gross_weight -- OTM R12 : unassign delivery detail
7074: INTO l_dummy_del_det_id, l_line_dir_tbl(i), l_cnt_flag_tbl(i),
7075: l_det_org_id_tbl(i),
7076: l_gross_weight_tbl(i) -- OTM R12 : unassign delivery detail
7077: FROM wsh_delivery_details
7078: WHERE delivery_detail_id=l_dd_id(i)
7079: FOR UPDATE NOWAIT;
7080:
7081: -- OTM R12 : unassign delivery detail
7159: WHERE delivery_detail_id = l_dd_id(j);
7160:
7161: -- bug 6700792: OTM Dock Door Appt Sched Proj
7162: --Upadating the loading sequence of the delivery detail to NULL while unassigning from delivery
7163: UPDATE wsh_delivery_details
7164: SET load_seq_number = NULL,
7165: last_update_date = SYSDATE,
7166: last_updated_by = FND_GLOBAL.USER_ID,
7167: last_update_login = FND_GLOBAL.LOGIN_ID
7172: RAISE NO_DATA_FOUND;
7173: END IF;
7174:
7175: l_mdc_detail_tab(1) := l_dd_id(j);
7176: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
7177: p_detail_id_tab => l_mdc_detail_tab,
7178: x_return_status => x_return_status);
7179:
7180: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7253: --}
7254: END IF;
7255: -- K LPN CONV. rv
7256: --
7257: UPDATE wsh_delivery_details
7258: SET customer_id = NULL,
7259: ship_to_location_id = NULL,
7260: intmed_ship_to_location_id = NULL,
7261: fob_code = NULL,
7278: --
7279:
7280: END IF;
7281: IF l_ib_upd_flag ='Y' THEN
7282: UPDATE wsh_delivery_details
7283: SET ship_from_location_id = -1, -- J-IB-NPARIKH
7284: routing_req_id = NULL, -- J-IB-NPARIKH
7285: rcv_shipment_line_id = NULL, -- J-IB-NPARIKH
7286: shipped_quantity = NULL, -- J-IB-NPARIKH
7410: -- End of OTM R12 : unassign delivery detail
7411:
7412: -- Call DBI api after the loop
7413: -- DBI Project
7414: -- Update of wsh_delivery_details, call DBI API after the action.
7415: -- This API will also check for DBI Installed or not
7416: IF l_debug_on THEN
7417: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',l_detail_tab.count);
7418: END IF;
7506: WHERE delivery_detail_id = p_detail_id;
7507:
7508: --OTM Dock Door Appt Sched Proj
7509: --Upadating the loading sequence of the delivery detail to NULL while unassigning from delivery
7510: UPDATE wsh_delivery_details
7511: SET load_seq_number = NULL,
7512: last_update_date = SYSDATE,
7513: last_updated_by = FND_GLOBAL.USER_ID,
7514: last_update_login = FND_GLOBAL.LOGIN_ID
7519: RAISE NO_DATA_FOUND;
7520: END IF;
7521:
7522: l_mdc_detail_tab(1) := p_detail_id;
7523: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
7524: p_detail_id_tab => l_mdc_detail_tab,
7525: x_return_status => x_return_status);
7526:
7527: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7717: END IF;
7718:
7719: WHEN others THEN
7720: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7721: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',l_module_name);
7722: ROLLBACK TO before_unassign;
7723:
7724:
7725: --
7769: WSH_DEBUG_SV.log(l_module_name,'P_VALIDATE_FLAG',P_VALIDATE_FLAG);
7770: END IF;
7771: --
7772: IF l_debug_on THEN
7773: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
7774: END IF;
7775: UNASSIGN_DETAIL_FROM_DELIVERY (p_detail_id,
7776: x_return_status,
7777: p_validate_flag,
7783: --
7784: EXCEPTION
7785: WHEN others THEN
7786: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7787: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',l_module_name);
7788: --
7789: IF l_debug_on THEN
7790: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7791: END IF;
8390: END IF;
8391: --
8392: WHEN others THEN
8393: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8394: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',l_module_name);
8395: --
8396: IF l_debug_on THEN
8397: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8398: END IF;
8473: --
8474: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8475: --
8476: IF l_debug_on THEN
8477: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
8478: END IF;
8479: --
8480: UNASSIGN_MULTIPLE_DETAILS
8481: (
8493:
8494: EXCEPTION
8495: WHEN others THEN
8496: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8497: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',l_module_name);
8498: --
8499: IF l_debug_on THEN
8500: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8501: END IF;
8518: IS
8519: l_rowid varchar2(150);
8520: l_group_status varchar2(30);
8521: l_ship_method_match boolean;
8522: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_TYPE;
8523: l_cr_assg_status varchar2(30);
8524: l_delivery_assignment_id NUMBER;
8525: del_id number;
8526: cont_id number;
8624: --
8625: IF l_debug_on THEN
8626: WSH_DEBUG_SV.log(l_module_name,'Delivery id ',p_rec_of_detail_ids(i));
8627:
8628: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
8629: END IF;
8630: --
8631: wsh_delivery_details_actions.assign_detail_to_delivery(
8632: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8627:
8628: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
8629: END IF;
8630: --
8631: wsh_delivery_details_actions.assign_detail_to_delivery(
8632: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8633: p_delivery_id => p_delivery_id,
8634: x_return_status => x_return_status,
8635: x_dlvy_has_lines => l_has_lines, -- J-IB-NPARIKH
8654:
8655: IF (p_cont_ins_id <> -9999) THEN
8656: -- J: W/V Changes
8657: -- replaced the direct update with Assign_Detail_To_Cont API
8658: wsh_delivery_details_actions.Assign_Detail_To_Cont(
8659: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8660: p_parent_detail_id => p_cont_ins_id,
8661: x_return_status => x_return_status);
8662:
8770: END IF;
8771: --
8772: WHEN others THEN
8773: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8774: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_MULTIPLE_DETAILS',l_module_name);
8775:
8776: --
8777: IF l_debug_on THEN
8778: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8861: mark reprice required flag when split_delivery_details
8862: */
8863: l_entity_ids WSH_UTIL_CORE.id_tab_type;
8864: reprice_required_err EXCEPTION;
8865: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
8866: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8867: l_split_weight number;
8868: l_split_volume number;
8869: l_split_return_status varchar2(30);
8876: l_num_of_split NUMBER;
8877:
8878: old_delivery_detail_rec SplitDetailRecType;
8879:
8880: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
8881:
8882:
8883: -- Bug 2637876
8884: l_original_shipped_qty NUMBER;
8882:
8883: -- Bug 2637876
8884: l_original_shipped_qty NUMBER;
8885: l_split_shipped_qty NUMBER;
8886: l_serial_tab WSH_DELIVERY_DETAILS_ACTIONS.serial_tab;
8887: i NUMBER;
8888: j NUMBER;
8889: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8890: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8885: l_split_shipped_qty NUMBER;
8886: l_serial_tab WSH_DELIVERY_DETAILS_ACTIONS.serial_tab;
8887: i NUMBER;
8888: j NUMBER;
8889: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8890: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8891: l_transaction_id_tab WSH_UTIL_CORE.id_tab_type;
8892: l_serial_number WSH_DELIVERY_DETAILS.serial_number%TYPE;
8893: TYPE l_sr_no_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8888: j NUMBER;
8889: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8890: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8891: l_transaction_id_tab WSH_UTIL_CORE.id_tab_type;
8892: l_serial_number WSH_DELIVERY_DETAILS.serial_number%TYPE;
8893: TYPE l_sr_no_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8894: l_serial_number_tab l_sr_no_tab;
8895: l_id1 NUMBER;
8896: l_s1 VARCHAR2(30);
9099:
9100: -- Bug 4455732 : Combining transaction_temp_id and serial_number update into single cursor
9101: -- since single quantity detail can have transaction_temp_id also.
9102: FORALL i IN 1..l_dd_id_tab.count
9103: UPDATE wsh_delivery_details
9104: SET serial_number = decode(l_serial_number_tab(i),FND_API.G_MISS_CHAR,NULL,
9105: NULL,serial_number,l_serial_number_tab(i)) ,
9106: transaction_temp_id = decode(l_transaction_id_tab(i),FND_API.G_MISS_NUM,NULL,
9107: NULL,transaction_temp_id,l_transaction_id_tab(i))
9187: END IF;
9188: --
9189: WHEN others THEN
9190: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9191: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',l_module_name);
9192: --
9193: IF l_debug_on THEN
9194: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
9195: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
9264: l_output_quantity number;
9265: l_qty_return_status varchar2(30);
9266: new_det_wt_vol_failed exception;
9267: old_det_wt_vol_failed exception;
9268: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
9269: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
9270: l_split_weight number;
9271: l_split_volume number;
9272: l_split_return_status varchar2(30);
9292: l_org_wv_qty NUMBER;
9293: l_new_wv_qty NUMBER;
9294: l_final_req_qty NUMBER; --bug# 6689448 (replenishment project)
9295:
9296: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
9297:
9298:
9299: WSH_SN_SPLIT_ERR EXCEPTION;
9300: WSH_NO_DATA_FOUND EXCEPTION;
9310: l_req_qty2_update_index NUMBER;
9311:
9312: CURSOR c_get_req_pick_qty(p_del_det IN NUMBER) IS
9313: SELECT requested_quantity, picked_quantity,requested_quantity2
9314: FROM wsh_delivery_details
9315: WHERE delivery_detail_id = p_del_det;
9316:
9317:
9318:
9316:
9317:
9318:
9319: -- Bug 2734868
9320: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9321: -- HW 12345 Added qty2
9322: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9323: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9324: wsh_split_error EXCEPTION;
9318:
9319: -- Bug 2734868
9320: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9321: -- HW 12345 Added qty2
9322: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9323: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9324: wsh_split_error EXCEPTION;
9325:
9326: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
9319: -- Bug 2734868
9320: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9321: -- HW 12345 Added qty2
9322: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9323: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9324: wsh_split_error EXCEPTION;
9325:
9326: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
9327: l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
9474:
9475: --
9476: /*
9477: IF l_debug_on THEN
9478: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
9479: END IF;
9480: --
9481: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
9482: p_delivery_detail_rec => l_delivery_details_info);
9477: IF l_debug_on THEN
9478: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
9479: END IF;
9480: --
9481: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
9482: p_delivery_detail_rec => l_delivery_details_info);
9483: END IF;
9484: */
9485: IF p_old_delivery_detail_rec.picked_quantity IS NULL THEN
9609: l_num_of_split := p_num_of_split;
9610:
9611: --
9612: IF l_debug_on THEN
9613: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DD_FROM_OLD_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
9614: END IF;
9615: --
9616: -- call to new API
9617: -- with a value for p_num_of_rec
9614: END IF;
9615: --
9616: -- call to new API
9617: -- with a value for p_num_of_rec
9618: WSH_DELIVERY_DETAILS_PKG.create_dd_from_old_bulk(
9619: p_delivery_detail_rec => l_delivery_details_info,
9620: p_delivery_detail_id => p_old_delivery_detail_rec.delivery_detail_id,
9621: p_num_of_rec => l_num_of_split,
9622: x_dd_id_tab => l_dd_id_tab,
9706: -- l_req_qty_update_index = CEIL(3/2) = 2
9707: -- Update at l_dd_id_tab(l_req_qty_update_index) = l_dd_id_tab(2) is a non-exist value which will fail.
9708: IF l_req_qty_update_index <= l_dd_id_tab.count THEN
9709: -- 12345 HW added qty2
9710: update wsh_delivery_details
9711: set requested_quantity = l_requested_quantity - ((l_req_qty_update_index - 1) * l_delivery_details_info.requested_quantity),
9712: requested_quantity2 = decode(l_delivery_details_info.requested_quantity2, fnd_api.g_miss_num, NULL, l_requested_quantity2 - ((l_req_qty2_update_index - 1) * l_delivery_details_info.requested_quantity2))
9713: where delivery_detail_id = l_dd_id_tab(l_req_qty_update_index);
9714: END IF;
9717: -- Set the requested quantity to zero in all the newly created lines after the above index.
9718:
9719: FORALL i in (l_req_qty_update_index + 1) .. l_dd_id_tab.count
9720: -- HW added qty2
9721: update wsh_delivery_details
9722: set requested_quantity = 0,
9723: requested_quantity2 = 0
9724: where delivery_detail_id = l_dd_id_tab(i);
9725:
9726: --
9727: -- DBI Project,Above 2 updates for requested quantity need to be tracked for DBI Call
9728: -- These can be combined since both use l_dd_id_tab
9729: -- DBI Project
9730: -- Update of wsh_delivery_details where requested_quantity/released_status
9731: -- are changed, call DBI API after the update.
9732: -- DBI API will check if DBI is installed
9733: IF l_debug_on THEN
9734: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_dd_id_tab.count);
9818:
9819: l_delivery_assignments_info.delivery_detail_id := l_new_delivery_detail_id;
9820: --
9821: IF l_debug_on THEN
9822: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
9823: END IF;
9824: --
9825:
9826: -- this is a bulk call
9825:
9826: -- this is a bulk call
9827: -- with new parameter of p_num_of_rec
9828: -- and pass l_dd_id_tab which will pass the delivery detail id
9829: wsh_delivery_details_pkg.create_deliv_assignment_bulk(
9830: p_delivery_assignments_info => l_delivery_assignments_info,
9831: p_num_of_rec => l_num_of_split,
9832: p_dd_id_tab => l_dd_id_tab,
9833: x_da_id_tab => l_da_id_tab,
9841: l_delivery_assignments_info.type := 'C';
9842: l_delivery_assignments_info.parent_delivery_id := p_old_delivery_detail_rec.parent_delivery_id;
9843: l_delivery_assignments_info.parent_delivery_detail_id := p_old_delivery_detail_rec.parent_delivery_detail_id;
9844:
9845: wsh_delivery_details_pkg.create_deliv_assignment_bulk(
9846: p_delivery_assignments_info => l_delivery_assignments_info,
9847: p_num_of_rec => l_num_of_split,
9848: p_dd_id_tab => l_dd_id_tab,
9849: x_da_id_tab => l_da_id_tab,
9908: -- OPM HW Included this fix in 3011758
9909: -- Modified requested_quantity2,picked_quantity2 and shipped_quantity2
9910: -- to be the same as quantity1s
9911:
9912: UPDATE wsh_delivery_details
9913: SET requested_quantity = GREATEST(requested_quantity - (l_num_of_split*l_delivery_details_info.requested_quantity), 0),
9914: requested_quantity2 = GREATEST(requested_quantity2 -(l_num_of_split*l_qty2), 0),
9915: picked_quantity = GREATEST(picked_quantity - (l_num_of_split*p_quantity_to_split),0),
9916: -- HW OPMCONV. No need to use l_new_pick_qty2
9952: --
9953:
9954: --
9955: -- DBI Project
9956: -- Update of wsh_delivery_details where requested_quantity/released_status
9957: -- are changed, call DBI API after the update.
9958: -- This API will also check for DBI Installed or not
9959: IF l_debug_on THEN
9960: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_old_delivery_detail_rec.delivery_detail_id);
10083: CLOSE c_get_req_pick_qty;
10084: END IF;
10085: rollback to split_savepoint;
10086: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10087: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DETAIL_INT_BULK',l_module_name);
10088: --
10089: IF l_debug_on THEN
10090: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
10091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10166: mark reprice required flag when split_delivery_details
10167: */
10168: l_entity_ids WSH_UTIL_CORE.id_tab_type;
10169: reprice_required_err EXCEPTION;
10170: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
10171: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
10172: l_split_weight number;
10173: l_split_volume number;
10174: l_split_return_status varchar2(30);
10178:
10179:
10180: old_delivery_detail_rec SplitDetailRecType;
10181:
10182: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
10183: -- muom
10184: l_fulfillment_base VARCHAR2(1);
10185: l_dummy NUMBER;
10186:
10476: END IF;
10477: --
10478: WHEN others THEN
10479: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10480: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',l_module_name);
10481: --
10482: IF l_debug_on THEN
10483: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
10484: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10589: l_output_quantity number;
10590: l_qty_return_status varchar2(30);
10591: new_det_wt_vol_failed exception;
10592: old_det_wt_vol_failed exception;
10593: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
10594: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
10595: l_split_weight number;
10596: l_split_volume number;
10597: l_split_return_status varchar2(30);
10633:
10634: -- HW OPMCONV. Removed OPM excpetion
10635:
10636:
10637: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
10638:
10639: WSH_CREATE_DET_ERR EXCEPTION;
10640: WSH_SN_SPLIT_ERR EXCEPTION;
10641:
11162: END IF; -- are there serial numbers to split?
11163:
11164: --
11165: IF l_debug_on THEN
11166: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_NEW_DETAIL_FROM_OLD',WSH_DEBUG_SV.C_PROC_LEVEL);
11167: END IF;
11168: --
11169: WSH_DELIVERY_DETAILS_PKG.create_new_detail_from_old(
11170: p_delivery_detail_rec => l_delivery_details_info,
11165: IF l_debug_on THEN
11166: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_NEW_DETAIL_FROM_OLD',WSH_DEBUG_SV.C_PROC_LEVEL);
11167: END IF;
11168: --
11169: WSH_DELIVERY_DETAILS_PKG.create_new_detail_from_old(
11170: p_delivery_detail_rec => l_delivery_details_info,
11171: p_delivery_detail_id => p_old_delivery_detail_rec.delivery_detail_id,
11172: x_row_id => detail_rowid,
11173: x_delivery_detail_id => l_new_delivery_detail_id,
11242:
11243: l_delivery_assignments_info.delivery_detail_id := l_new_delivery_detail_id;
11244: --
11245: IF l_debug_on THEN
11246: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
11247: END IF;
11248: --
11249: wsh_delivery_details_pkg.create_delivery_assignments(
11250: l_delivery_assignments_info,
11245: IF l_debug_on THEN
11246: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
11247: END IF;
11248: --
11249: wsh_delivery_details_pkg.create_delivery_assignments(
11250: l_delivery_assignments_info,
11251: assignment_rowid,
11252: l_delivery_assignment_id,
11253: l_cr_assg_status
11270: l_delivery_assignments_info.parent_delivery_id := p_old_delivery_detail_rec.parent_delivery_id;
11271: l_delivery_assignments_info.parent_delivery_detail_id := p_old_delivery_detail_rec.parent_delivery_detail_id;
11272: l_delivery_assignments_info.type := 'C';
11273:
11274: wsh_delivery_details_pkg.create_delivery_assignments(
11275: l_delivery_assignments_info,
11276: assignment_rowid,
11277: l_delivery_assignment_id,
11278: l_cr_assg_status
11303:
11304: x_split_detail_id := l_new_delivery_detail_id;
11305:
11306: IF l_debug_on THEN
11307: WSH_DEBUG_SV.logmsg(l_module_name, 'Before Update wsh_delivery_details');
11308: WSH_DEBUG_SV.log(l_module_name, 'serial_number',l_updated_delivery_detail_rec.serial_number);
11309: WSH_DEBUG_SV.log(l_module_name, 'to_serial_number',l_updated_delivery_detail_rec.to_serial_number);
11310: WSH_DEBUG_SV.log(l_module_name, 'transaction_temp_id',l_updated_delivery_detail_rec.transaction_temp_id);
11311: WSH_DEBUG_SV.log(l_module_name, ' l_original_req_qty ',l_original_req_qty);
11321: WSH_DEBUG_SV.log(l_module_name, ' l_original_cc_qty ',l_original_cc_qty);
11322: WSH_DEBUG_SV.log(l_module_name, ' l_original_cc_qty2 ',l_original_cc_qty2);
11323: END IF;
11324:
11325: UPDATE wsh_delivery_details
11326: SET requested_quantity = l_original_req_qty, -- J-IB-NPARIKH, GREATEST(requested_quantity - l_delivery_details_info.requested_quantity, 0),
11327: requested_quantity2 = l_original_req_qty2, -- J-IB-NPARIKH, GREATEST(requested_quantity2 - l_qty2, 0),
11328: picked_quantity = l_original_picked_qty, -- J-IB-NPARIKH, picked_quantity - p_quantity_to_split,
11329: picked_quantity2 = l_original_picked_qty2, -- J-IB-NPARIKH, picked_quantity2 - nvl(l_qty2,l_new_pick_qty2),
11351: last_update_login = FND_GLOBAL.LOGIN_ID
11352: WHERE delivery_detail_id = p_old_delivery_detail_rec.delivery_detail_id;
11353:
11354: -- DBI Project
11355: -- Update of wsh_delivery_details where requested_quantity/released_status
11356: -- are changed, call DBI API after the update.
11357: -- This API will also check for DBI Installed or not
11358: IF l_debug_on THEN
11359: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_old_delivery_detail_rec.delivery_detail_id);
11474: END IF;
11475:
11476: WHEN others THEN
11477: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
11478: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DETAIL_INT',l_module_name);
11479: --
11480: IF l_debug_on THEN
11481: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
11482: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
11496: x_return_status OUT NOCOPY VARCHAR2) IS
11497:
11498: l_ser_qty NUMBER;
11499:
11500: l_real_serial_prefix WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11501: l_prefix_length NUMBER;
11502: l_fm_numeric NUMBER;
11503: l_to_numeric NUMBER;
11504: l_range_count NUMBER;
11502: l_fm_numeric NUMBER;
11503: l_to_numeric NUMBER;
11504: l_range_count NUMBER;
11505: l_qty_to_split NUMBER;
11506: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11507: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11508: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11509: l_transaction_temp_id NUMBER := NULL;
11510: l_success NUMBER;
11503: l_to_numeric NUMBER;
11504: l_range_count NUMBER;
11505: l_qty_to_split NUMBER;
11506: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11507: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11508: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11509: l_transaction_temp_id NUMBER := NULL;
11510: l_success NUMBER;
11511:
11504: l_range_count NUMBER;
11505: l_qty_to_split NUMBER;
11506: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11507: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11508: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11509: l_transaction_temp_id NUMBER := NULL;
11510: l_success NUMBER;
11511:
11512: -- Bug 3782838
11647:
11648: -- we have at least one record in MTL_SERIAL_NUMBERS_TEMP
11649: --
11650: IF l_debug_on THEN
11651: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_SERIAL_QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
11652: END IF;
11653: --
11654: l_ser_qty := WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty(
11655: p_organization_id => x_old_detail_rec.organization_id,
11650: IF l_debug_on THEN
11651: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_SERIAL_QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
11652: END IF;
11653: --
11654: l_ser_qty := WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty(
11655: p_organization_id => x_old_detail_rec.organization_id,
11656: p_delivery_detail_id => x_old_detail_rec.delivery_detail_id);
11657: l_qty_to_split := l_ser_qty - p_old_shipped_quantity;
11658: --
12097:
12098: cursor l_get_cnt_org_csr(p_detail_id IN NUMBER) is
12099: select organization_id,
12100: nvl(line_direction,'O')
12101: from wsh_delivery_details
12102: where delivery_detail_id = p_detail_id;
12103:
12104: l_orgn_id NUMBER;
12105: l_line_dir VARCHAR2(10);
12154: END IF;
12155: CLOSE get_container;
12156:
12157: IF l_debug_on THEN
12158: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
12159: END IF;
12160:
12161: -- Unassign the Line or Container
12162: -- because this
12159: END IF;
12160:
12161: -- Unassign the Line or Container
12162: -- because this
12163: wsh_delivery_details_actions.unassign_detail_from_delivery(
12164: p_detail_id => p_ids_tobe_unassigned(i),
12165: p_validate_flag => p_validate_flag,
12166: x_return_status => l_return_status
12167: );
12202: END IF;
12203:
12204: IF l_line_id IS NULL THEN
12205: IF l_debug_on THEN
12206: WSH_DEBUG_SV.log(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_UNPACK_EMPTY_CONT
12207: recursively for ',l_parent_container_id(1));
12208: END IF;
12209:
12210: -- lpn conv
12480: --
12481: END IF;
12482:
12483: select requested_quantity,requested_quantity2 into l_qty1,l_qty2
12484: from wsh_delivery_details
12485: where delivery_detail_id = p_new_delivery_detail_id;
12486:
12487: IF l_debug_on THEN
12488: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
12618: -- in p_Cons_Source_Line_Rec_Tab.
12619: --
12620: -- HW OPM BUG#:3121616 added requested_quantity2
12621: PROCEDURE Consolidate_Source_Line(
12622: p_Cons_Source_Line_Rec_Tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12623: x_consolidate_ids OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12624: x_return_status OUT NOCOPY VARCHAR2 ) IS
12625:
12626: CURSOR get_bo_dds_cur(p_delivery_detail_id IN NUMBER, p_source_line_id IN NUMBER,p_delivery_id IN NUMBER) is
12624: x_return_status OUT NOCOPY VARCHAR2 ) IS
12625:
12626: CURSOR get_bo_dds_cur(p_delivery_detail_id IN NUMBER, p_source_line_id IN NUMBER,p_delivery_id IN NUMBER) is
12627: SELECT wdd.delivery_detail_id, wdd.requested_quantity,wdd.requested_quantity2
12628: FROM wsh_delivery_details wdd,
12629: wsh_delivery_assignments_v wda
12630: WHERE wdd.source_line_id = p_source_line_id
12631: AND wdd.delivery_detail_id <> p_delivery_detail_id
12632: AND wdd.released_status = 'B'
12847: --
12848: -- Debug Statements
12849: --
12850: IF l_debug_on THEN
12851: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
12852: END IF;
12853: --
12854: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12855: p_delivery_detail_id => l_delete_dd_ids(i),
12850: IF l_debug_on THEN
12851: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
12852: END IF;
12853: --
12854: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12855: p_delivery_detail_id => l_delete_dd_ids(i),
12856: x_return_status => l_return_status );
12857:
12858: --
12873: END IF;
12874: -- Updating the selected Delivery Details for the consolidation with corresponding total Qty
12875: FOR i IN 1..l_cons_dd_ids.COUNT LOOP
12876: -- HW OPM BUG#:3121616 added qty2s
12877: UPDATE wsh_delivery_details
12878: SET requested_quantity = l_cons_qtys(i),
12879: requested_quantity2 = l_cons_qty2s(i),
12880: tracking_number = null,
12881: master_container_item_id = null,
12896: END LOOP;
12897:
12898: --
12899: -- DBI Project
12900: -- Update of wsh_delivery_details where requested_quantity/released_status
12901: -- are changed, call DBI API after the update.
12902: -- DBI API will check if DBI is installed
12903: IF l_debug_on THEN
12904: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_cons_dd_ids.count);
12968: END IF;
12969: -- Updating the partial Back order cases. Qty should be subtracted for the partial case
12970: FORALL i IN 1..l_partial_dd_ids.COUNT
12971: -- HW OPM BUG#:3121616 added qty2s
12972: UPDATE wsh_delivery_details
12973: SET requested_quantity = l_partial_req_qtys(i),
12974: requested_quantity2 = l_partial_req_qty2s(i)
12975: WHERE delivery_detail_id = l_partial_dd_ids (i);
12976:
12975: WHERE delivery_detail_id = l_partial_dd_ids (i);
12976:
12977: --
12978: -- DBI Project
12979: -- Update of wsh_delivery_details where requested_quantity/released_status
12980: -- are changed, call DBI API after the update.
12981: -- DBI API will check if DBI is installed
12982: IF l_debug_on THEN
12983: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_partial_dd_ids.count);
13014: INTO l_gross_weight,
13015: l_net_weight,
13016: l_volume,
13017: l_wv_frozen_flag
13018: FROM wsh_delivery_details
13019: WHERE delivery_detail_id = l_partial_dd_ids(l_index);
13020:
13021: IF l_wv_frozen_flag = 'Y' THEN
13022:
13031: IF l_debug_on THEN
13032: WSH_DEBUG_SV.logmsg(l_module_name,'Updating DD '||l_partial_dd_ids(l_index)||' with Gross '||l_new_gross_wt||' Net '||l_new_net_wt||' Vol '||l_new_vol);
13033: END IF;
13034:
13035: UPDATE wsh_delivery_details
13036: set gross_weight = l_new_gross_wt,
13037: net_weight = l_new_net_wt,
13038: volume = l_new_vol
13039: WHERE delivery_detail_id = l_partial_dd_ids(l_index);
13095:
13096:
13097: WHEN OTHERS THEN
13098: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13099: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Lines ' );
13100: --
13101: -- Debug Statements
13102: --
13103: IF l_debug_on THEN
13140: l_organization_id NUMBER := NULL;
13141: l_subinventory VARCHAR2(10) := NULL;
13142: l_serial_number VARCHAR2(30) := NULL;
13143: l_transaction_temp_id NUMBER := NULL;
13144: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
13145: l_return_status VARCHAR2(5) := NULL;
13146: --Added for bug 10062808
13147: CURSOR c_detail(c_detail_id NUMBER) IS
13148: SELECT organization_id,
13147: CURSOR c_detail(c_detail_id NUMBER) IS
13148: SELECT organization_id,
13149: inventory_item_id,
13150: WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE',source_line_id) fulfillment_base
13151: FROM wsh_delivery_details
13152: WHERE delivery_detail_id = c_detail_id AND
13153: NVL(container_flag, 'N') = 'N';
13154: l_qty_unres NUMBER;
13155: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
13151: FROM wsh_delivery_details
13152: WHERE delivery_detail_id = c_detail_id AND
13153: NVL(container_flag, 'N') = 'N';
13154: l_qty_unres NUMBER;
13155: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
13156: l_bo_mode VARCHAR2(30);
13157: -- muom
13158: l_qty2_unres NUMBER;
13159: l_fulfillment_base VARCHAR2(1);
13191: --
13192: -- Debug Statements
13193: --
13194: IF l_debug_on THEN
13195: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
13196: END IF;
13197: --
13198: l_delivery_detail_ids(1) := p_delivery_detail_id;
13199: IF (p_delete_flag = 'Y') THEN --{
13196: END IF;
13197: --
13198: l_delivery_detail_ids(1) := p_delivery_detail_id;
13199: IF (p_delete_flag = 'Y') THEN --{
13200: WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
13201: p_ids_tobe_unassigned => l_delivery_detail_ids ,
13202: p_validate_flag => 'N',
13203: x_return_status => l_return_status
13204: );
13212: OPEN c_detail(p_delivery_detail_id );
13213: FETCH c_detail INTO l_organization_id,l_inventory_item_id,l_fulfillment_base; --muom
13214: CLOSE c_detail;
13215:
13216: WSH_DELIVERY_DETAILS_INV.Get_item_information(
13217: p_organization_id => l_organization_id,
13218: p_inventory_item_id => l_inventory_item_id,
13219: x_mtl_system_items_rec => l_item_info,
13220: x_return_status => l_return_status
13240: WSH_DEBUG_SV.logmsg(l_module_name,'Unreserving the Backordered quantity ');
13241: WSH_DEBUG_SV.logmsg(l_module_name,'... delivery_detail_id '|| p_delivery_detail_id);
13242: -- muom
13243: WSH_DEBUG_SV.logmsg(l_module_name,'... backordered quantity '||p_bo_qty||' backordered quantity2 '||p_bo_qty2s );
13244: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
13245: END IF;
13246: --
13247: IF (l_fulfillment_base='S' AND p_overpick_qty2s= 0 AND p_overpick_qty>0 ) THEN
13248: WSH_DEBUG_SV.logmsg(l_module_name,'..with . backordered quantity+overpicked quantity '||p_bo_qty||'+'||p_overpick_qty||' backordered quantity2 '||p_bo_qty2s );
13245: END IF;
13246: --
13247: IF (l_fulfillment_base='S' AND p_overpick_qty2s= 0 AND p_overpick_qty>0 ) THEN
13248: WSH_DEBUG_SV.logmsg(l_module_name,'..with . backordered quantity+overpicked quantity '||p_bo_qty||'+'||p_overpick_qty||' backordered quantity2 '||p_bo_qty2s );
13249: wsh_delivery_details_actions.unreserve_delivery_detail(
13250: p_delivery_Detail_id => p_delivery_detail_id,
13251: p_unreserve_mode => p_bo_mode ,
13252: p_quantity_to_unreserve => p_bo_qty+p_overpick_qty,
13253: p_override_retain_ato_rsv => 'N',
13255: x_return_status => l_return_status
13256: );
13257:
13258: ELSE
13259: wsh_delivery_details_actions.unreserve_delivery_detail(
13260: p_delivery_Detail_id => p_delivery_detail_id,
13261: p_unreserve_mode => p_bo_mode ,
13262: p_quantity_to_unreserve => p_bo_qty,
13263: p_override_retain_ato_rsv => 'N',
13280: IF l_debug_on THEN
13281: WSH_DEBUG_SV.logmsg(l_module_name,'Unreserving the Backordered quantity ');
13282: WSH_DEBUG_SV.logmsg(l_module_name,'... delivery_detail_id '|| p_delivery_detail_id);
13283: WSH_DEBUG_SV.logmsg(l_module_name,'... overpicked quantity '||p_overpick_qty||' overpicked quantity2 '||p_overpick_qty2s );
13284: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
13285: END IF;
13286: --
13287:
13288: --Bug 4721577 Do not retain reservation for overpicked quantities
13288: --Bug 4721577 Do not retain reservation for overpicked quantities
13289: -- muom
13290: IF l_fulfillment_base = 'P' THEN
13291: IF p_bo_mode = 'RETAIN_RSV' THEN
13292: wsh_delivery_details_actions.unreserve_delivery_detail(
13293: p_delivery_Detail_id => p_delivery_detail_id,
13294: p_unreserve_mode => 'UNRESERVE' ,
13295: p_quantity_to_unreserve => p_overpick_qty,
13296: p_override_retain_ato_rsv => 'Y',
13297: p_quantity2_to_unreserve => p_bo_qty2s,
13298: x_return_status => l_return_status
13299: );
13300: ELSE
13301: wsh_delivery_details_actions.unreserve_delivery_detail(
13302: p_delivery_Detail_id => p_delivery_detail_id,
13303: p_unreserve_mode => p_bo_mode ,
13304: p_quantity_to_unreserve => p_overpick_qty,
13305: p_override_retain_ato_rsv => 'Y',
13308: );
13309: END IF;
13310: ELSE
13311: IF p_bo_mode = 'RETAIN_RSV' THEN
13312: wsh_delivery_details_actions.unreserve_delivery_detail(
13313: p_delivery_Detail_id => p_delivery_detail_id,
13314: p_unreserve_mode => 'UNRESERVE' ,
13315: p_quantity_to_unreserve => p_overpick_qty,
13316: p_override_retain_ato_rsv => 'Y',
13317: p_quantity2_to_unreserve => p_overpick_qty2s,
13318: x_return_status => l_return_status
13319: );
13320: ELSE
13321: wsh_delivery_details_actions.unreserve_delivery_detail(
13322: p_delivery_Detail_id => p_delivery_detail_id,
13323: p_unreserve_mode => p_bo_mode ,
13324: p_quantity_to_unreserve => p_overpick_qty,
13325: p_override_retain_ato_rsv => 'Y',
13351: -- muom
13352: l_qty2_unres :=p_bo_qty2s + p_overpick_qty2s;
13353: ------
13354: --This change should not be done in WSH_SHIP_CONFIRM_ACTIONS2.Backorder before calling
13355: --WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details because
13356: ------
13357: --When backorder quantity is not equal to requested
13358: --Consolidate_Delivery_Details API calls Process_Delivery_Details with 'delete_flag' as 'N'
13359:
13367: WSH_DEBUG_SV.logmsg(l_module_name,'... delivery_detail_id '|| p_delivery_detail_id);
13368: WSH_DEBUG_SV.logmsg(l_module_name,'... backordered quantity '||l_qty_unres );
13369: -- muom
13370: WSH_DEBUG_SV.logmsg(l_module_name,'... backordered quantity2 '||l_qty2_unres );
13371: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
13372: END IF;
13373: --
13374: -- muom
13375: IF l_fulfillment_base = 'P' THEN
13372: END IF;
13373: --
13374: -- muom
13375: IF l_fulfillment_base = 'P' THEN
13376: wsh_delivery_details_actions.unreserve_delivery_detail(
13377: p_delivery_Detail_id => p_delivery_detail_id,
13378: p_unreserve_mode => l_bo_mode ,
13379: p_quantity_to_unreserve => l_qty_unres ,
13380: p_override_retain_ato_rsv => 'N',
13381: p_quantity2_to_unreserve => p_bo_qty2s,
13382: x_return_status => l_return_status
13383: );
13384: ELSE
13385: wsh_delivery_details_actions.unreserve_delivery_detail(
13386: p_delivery_Detail_id => p_delivery_detail_id,
13387: p_unreserve_mode => l_bo_mode ,
13388: p_quantity_to_unreserve => l_qty_unres ,
13389: p_override_retain_ato_rsv => 'N',
13406: --
13407: -- Debug Statements
13408: --
13409: IF l_debug_on THEN
13410: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
13411: END IF;
13412: --
13413:
13414: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
13410: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
13411: END IF;
13412: --
13413:
13414: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
13415: p_delivery_detail_id => p_delivery_detail_id,
13416: p_cancel_flag => 'N',
13417: x_return_status => l_return_status);
13418: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
13447: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
13448: IF c_detail%ISOPEN THEN
13449: CLOSE c_detail;
13450: END IF;
13451: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.Process_Delivery_Details',l_module_name);
13452: --
13453: IF l_debug_on THEN
13454: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
13455: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
13480: -- delivery detail for each source line id.
13481: --
13482: -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
13483: PROCEDURE Consolidate_Delivery_Details(
13484: p_delivery_details_tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
13485: p_bo_mode IN VARCHAR2,
13486: x_cons_delivery_details_tab OUT NOCOPY WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
13487: x_remain_bo_qtys OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
13488: x_remain_bo_qty2s OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
13482: -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
13483: PROCEDURE Consolidate_Delivery_Details(
13484: p_delivery_details_tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
13485: p_bo_mode IN VARCHAR2,
13486: x_cons_delivery_details_tab OUT NOCOPY WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
13487: x_remain_bo_qtys OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
13488: x_remain_bo_qty2s OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
13489: x_return_status OUT NOCOPY VARCHAR2
13490: ) IS
13489: x_return_status OUT NOCOPY VARCHAR2
13490: ) IS
13491: l_line_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the source line ids passed as parameter
13492: l_detail_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the delivery detail ids passed as parameter
13493: l_delivery_details_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab; -- Stores the delivery detail records with req-qty >0
13494: l_freight_detail_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the delivery details for which the freight costs need to be deleted.
13495: l_req_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the requested quantities passed as parameter
13496: l_bo_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the BO quantities passed as parameter
13497: l_overpick_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the Overpicked quantities passed as parameter
13764: raise FND_API.G_EXC_UNEXPECTED_ERROR;
13765: END IF;
13766:
13767: -- HW OPM BUG#:3121616 added qty2s
13768: update wsh_delivery_details
13769: set requested_quantity = requested_quantity - l_bo_qtys(l_cmp_idx),
13770: requested_quantity2 = requested_quantity2 - l_bo_qty2s(l_cmp_idx),
13771: picked_quantity = picked_quantity - l_bo_qtys(l_cmp_idx) - l_overpick_qtys(l_cmp_idx),
13772: picked_quantity2 = picked_quantity2 - l_bo_qty2s(l_cmp_idx),
13788: l_cmp_idx := l_detail_ids.NEXT(l_cmp_idx);
13789: END LOOP; --} inner Loop(l_cmp_idx)
13790:
13791: -- DBI Project
13792: -- Update of wsh_delivery_details where requested_quantity/released_status
13793: -- are changed, call DBI API after the update.
13794: -- This API will also check for DBI Installed or not
13795: IF l_debug_on THEN
13796: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',l_detail_tab.count);
13895: --
13896:
13897: WHEN OTHERS THEN
13898: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13899: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details',l_module_name);
13900: --
13901: -- Debug Statements
13902: --
13903: IF l_debug_on THEN
13949: END IF;
13950: EXCEPTION
13951: WHEN OTHERS THEN
13952: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13953: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Top_Detail_from_Delivery',l_module_name);
13954: --
13955: -- Debug Statements
13956: --
13957: IF l_debug_on THEN
14081: EXCEPTION
14082:
14083: WHEN OTHERS THEN
14084: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
14085: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record',l_module_name);
14086: --
14087: -- Debug Statements
14088: --
14089: IF l_debug_on THEN
14092: END IF;
14093: --
14094: END Create_Consol_Record;
14095:
14096: END WSH_DELIVERY_DETAILS_ACTIONS;