[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_ACTIONS as
2: /* $Header: WSHDDACB.pls 120.36.12010000.3 2008/11/06 11:53:07 anvarshn 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.36.12010000.3 2008/11/06 11:53:07 anvarshn 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
334:
335: l_rsv_rec.demand_source_header_id := l_sales_order_id;
336: l_rsv_rec.demand_source_line_id := l_line_rec.line_id;
337:
338: -- Bug 1842613 : Initializing inventory controls present in wsh_delivery_details
339: IF ( l_line_rec.organization_id IS NOT NULL ) THEN
340: l_rsv_rec.organization_id := l_line_rec.organization_id;
341: END IF;
342: IF ( l_line_rec.subinventory IS NOT NULL) THEN
427: END IF;
428: --2587777
429:
430: --X-dock
431: IF l_line_rec.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE THEN
432: WSH_USA_INV_PVT.get_putaway_detail_id
433: (p_detail_id => p_delivery_detail_id,
434: p_released_status => l_line_rec.released_status,
435: p_move_order_line_id => l_line_rec.move_order_line_id,
1373: NULL
1374: )
1375: ) picked_lines_count
1376: FROM wsh_delivery_assignments_v wda,
1377: wsh_delivery_details wdd
1378: WHERE
1379: wda.delivery_detail_id= wdd.delivery_detail_id AND
1380: wdd.container_flag = 'N' AND
1381: wda.delivery_id = p_delv_id;
1458: END IF;
1459: -- Workflow Changes ==/
1460: --
1461: IF l_debug_on THEN
1462: 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);
1463: END IF;
1464: --
1465:
1466: wsh_delivery_details_actions.assign_top_detail_to_delivery(
1462: 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);
1463: END IF;
1464: --
1465:
1466: wsh_delivery_details_actions.assign_top_detail_to_delivery(
1467: P_DETAIL_ID => l_topmost_delivery_detail_id,
1468: P_DELIVERY_ID => p_delivery_id,
1469: X_RETURN_STATUS => x_return_status,
1470: x_dlvy_has_lines => x_dlvy_has_lines, -- J-IB-NPARIKH
1602: WHEN others THEN
1603: ROLLBACK TO before_assign_topmost_cont;
1604: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1605: wsh_util_core.add_message(x_return_status,l_module_name);
1606: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DELIVERY_DETAIL',l_module_name);
1607:
1608: --
1609: IF l_debug_on THEN
1610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1673: --
1674: EXCEPTION
1675: WHEN others THEN
1676: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1677: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery',l_module_name);
1678: --
1679: IF l_debug_on THEN
1680: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1681: END IF;
1728: WHERE delivery_detail_id = p_id;
1729:
1730: CURSOR c_get_gross_weight (p_id NUMBER) IS
1731: SELECT nvl(gross_weight, 0)
1732: FROM wsh_delivery_details
1733: WHERE delivery_detail_id = p_id;
1734:
1735: l_is_delivery_empty VARCHAR2(1);
1736: l_delivery1 NUMBER;
2131: l_display_error BOOLEAN;
2132:
2133: CURSOR c_get_shipto (v_container_id NUMBER) IS
2134: SELECT ship_to_location_id
2135: FROM wsh_delivery_details
2136: WHERE delivery_detail_id = v_container_id;
2137:
2138: -- Bug 3715176
2139: CURSOR c_get_plan_flag (v_delivery_id NUMBER) IS
3112: -- End of OTM R12 : assign delivery detail
3113:
3114: -- K: MDC delete consolidation record of it exists
3115: l_mdc_detail_tab(1) := p_detail_id;
3116: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
3117: p_detail_id_tab => l_mdc_detail_tab,
3118: x_return_status => x_return_status);
3119:
3120: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3189: last_update_login = FND_GLOBAL.LOGIN_ID
3190: WHERE delivery_detail_id = p_detail_id;
3191:
3192: l_mdc_detail_tab(1) := p_detail_id;
3193: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
3194: p_detail_id_tab => l_mdc_detail_tab,
3195: x_return_status => x_return_status);
3196:
3197: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3368:
3369: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
3370: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
3371: END LOOP;
3372: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
3373: p_detail_id_tab => l_mdc_detail_tab,
3374: x_return_status => x_return_status);
3375:
3376: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3520: --
3521: RETURN;
3522: WHEN others THEN
3523: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3524: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',l_module_name);
3525: --
3526: IF l_debug_on THEN
3527: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3528: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3569:
3570: cursor l_parent_cnt_csr (p_cnt_inst_id IN NUMBER) is
3571: select organization_id,
3572: nvl(line_direction,'O')
3573: from wsh_delivery_details
3574: where delivery_detail_id = p_cnt_inst_id
3575: and container_flag = 'Y'
3576: and source_code = 'WSH';
3577:
3729: last_updated_by = FND_GLOBAL.USER_ID,
3730: last_update_login = FND_GLOBAL.LOGIN_ID
3731: WHERE delivery_detail_id = p_detail_id;
3732: l_mdc_detail_tab(1) := p_detail_id;
3733: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
3734: p_detail_id_tab => l_mdc_detail_tab,
3735: x_return_status => x_return_status);
3736:
3737: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3895: END IF;
3896: --
3897: RETURN;
3898: WHEN others THEN
3899: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_CONT',l_module_name);
3900: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3901:
3902: --
3903: IF l_debug_on THEN
3957: x_dlvy_freight_terms_code IN OUT NOCOPY VARCHAR2, -- J-IB-NPARIKH
3958: p_caller IN VARCHAR2 --bug 5100229
3959: ) IS
3960:
3961: TYPE del_det_id_tab_type IS TABLE OF wsh_delivery_details.delivery_detail_id%TYPE INDEX BY BINARY_INTEGER;
3962:
3963: l_rowid varchar2(150);
3964: l_delivery_assignment_id NUMBER;
3965: -- cont_id NUMBER;
3981: del_not_updatable exception;
3982: grouping_attributes_not_match exception;
3983: arrival_SET_failed exception;
3984: ship_method_not_match exception;
3985: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
3986: l_mdc_detail_tab wsh_util_core.id_tab_type;
3987: DETAIL_DEL_FROM_to_not_SAME exception;
3988: det_confirmed exception;
3989: update_mol_carton_group_error exception;
4015: NVL(ignore_for_planning,'N') ignore_for_planning,--J TP Release ttrichy
4016: mode_of_transport, carrier_id, service_level,
4017: wda.parent_delivery_detail_id, -- K LPN CONV. rv
4018: wdd.gross_weight -- OTM R12 : assign delivery detail
4019: FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
4020: WHERE wdd.delivery_detail_id = wda.delivery_detail_id and wdd.delivery_detail_id = c_detail_id;
4021:
4022:
4023: l_detail c_detail%ROWTYPE;
4031: CONNECT BY prior delivery_detail_id = parent_delivery_detail_id;
4032:
4033: CURSOR c_getdet_ignore IS
4034: select NVL(ignore_for_planning, 'N') ignore_for_planning
4035: from wsh_delivery_details
4036: where delivery_detail_id=p_detail_id;
4037:
4038: -- J: W/V Changes
4039: l_detail_status VARCHAR2(1);
4105:
4106: -- OTM R12 : assign delivery detail
4107: l_delivery_was_empty BOOLEAN;
4108: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
4109: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4110: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4111: l_delivery_detail_ids WSH_GLBL_VAR_STRCT_GRP.NUM_TBL_TYPE;
4112: l_assign_update BOOLEAN;
4113: -- End of OTM R12 : assign delivery detail
4106: -- OTM R12 : assign delivery detail
4107: l_delivery_was_empty BOOLEAN;
4108: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
4109: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4110: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
4111: l_delivery_detail_ids WSH_GLBL_VAR_STRCT_GRP.NUM_TBL_TYPE;
4112: l_assign_update BOOLEAN;
4113: -- End of OTM R12 : assign delivery detail
4114:
4272: LOOP
4273: FETCH c_all_details_below into l_dd_id(i);
4274: EXIT WHEN c_all_details_below%notfound;
4275: SELECT l_dd_id(i) into l_dummy_del_det_id
4276: FROM wsh_delivery_details
4277: WHERE delivery_detail_id=l_dd_id(i)
4278: FOR UPDATE NOWAIT;
4279: i:=i+1;
4280: END LOOP;
4416: --}
4417: END IF;
4418: -- K LPN CONV. rv
4419:
4420: UPDATE WSH_DELIVERY_DETAILS
4421: SET ship_from_location_id = l_del.initial_pickup_location_id,
4422: ship_to_location_id = l_del.ultimate_dropoff_location_id,
4423: customer_id = l_del.customer_id,
4424: intmed_ship_to_location_id = l_del.intmed_ship_to_location_id,
4729: RAISE NO_DATA_FOUND;
4730: END IF;
4731:
4732: l_mdc_detail_tab(1) := l_detail.delivery_detail_id;
4733: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
4734: p_detail_id_tab => l_mdc_detail_tab,
4735: x_return_status => x_return_status);
4736:
4737: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4808: --
4809: -- J-IB-NPARIKH-}
4810:
4811:
4812: IF (l_detail.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE) THEN
4813: --{
4814: IF (wsh_util_validate.Check_Wms_Org(l_detail.organization_id)='Y') AND
4815: (l_detail.move_order_line_id IS NOT NULL AND
4816: (WSH_USA_INV_PVT.is_mo_type_putaway
5083: --
5084: RETURN;
5085: WHEN others THEN
5086: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5087: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',l_module_name);
5088: --
5089: IF l_debug_on THEN
5090: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5151: --
5152: EXCEPTION
5153: WHEN others THEN
5154: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5155: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_TOP_DETAIL_TO_DELIVERY',l_module_name);
5156: --
5157: IF l_debug_on THEN
5158: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5159: END IF;
5217: */
5218:
5219: CURSOR c_get_shipto (p_container_id NUMBER) IS
5220: SELECT ship_to_location_id
5221: FROM wsh_delivery_details
5222: WHERE delivery_detail_id = p_container_id;
5223:
5224: l_container_is_empty BOOLEAN;
5225: l_ship_to_loc NUMBER;
5250: -- when packing a container
5251:
5252: CURSOR Get_Min_Fill(v_cont_id NUMBER) IS
5253: SELECT nvl(minimum_fill_percent,0)
5254: FROM WSH_DELIVERY_DETAILS
5255: WHERE delivery_detail_id = v_cont_id
5256: AND container_flag = 'Y';
5257:
5258: l_min_fill NUMBER;
5268: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5269: l_del_id_for_container1 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5270: l_del_id_for_container2 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5271:
5272: detail_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5273: parent_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5274:
5275: l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
5276: /* H projects: pricing integration csun */
5269: l_del_id_for_container1 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5270: l_del_id_for_container2 C_DEL_ID_FOR_CONT_OR_DETAIL%ROWTYPE;
5271:
5272: detail_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5273: parent_cont_name WSH_DELIVERY_DETAILS.CONTAINER_NAME%TYPE;
5274:
5275: l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
5276: /* H projects: pricing integration csun */
5277: m NUMBER := 0;
5294:
5295: -- OTM R12 : assign delivery detail
5296: l_delivery_was_empty BOOLEAN;
5297: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
5298: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5299: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5300: l_gc3_is_installed VARCHAR2(1);
5301: l_assign_update BOOLEAN;
5302: -- End of OTM R12 : assign delivery detail
5295: -- OTM R12 : assign delivery detail
5296: l_delivery_was_empty BOOLEAN;
5297: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
5298: l_gross_weight1 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5299: l_gross_weight2 WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
5300: l_gc3_is_installed VARCHAR2(1);
5301: l_assign_update BOOLEAN;
5302: -- End of OTM R12 : assign delivery detail
5303:
6080:
6081: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
6082: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
6083: END LOOP;
6084: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
6085: p_detail_id_tab => l_mdc_detail_tab,
6086: x_return_status => x_return_status);
6087:
6088: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6244:
6245: FOR i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last LOOP
6246: l_mdc_detail_tab(i) := l_sync_tmp_recTbl.delivery_detail_id_tbl(i);
6247: END LOOP;
6248: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
6249: p_detail_id_tab => l_mdc_detail_tab,
6250: x_return_status => x_return_status);
6251: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6252: IF l_debug_on THEN
6325: last_updated_by = FND_GLOBAL.USER_ID,
6326: last_update_login = FND_GLOBAL.LOGIN_ID
6327: WHERE delivery_detail_id = p_detail_id1;
6328: l_mdc_detail_tab(1) := p_detail_id1;
6329: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
6330: p_detail_id_tab => l_mdc_detail_tab,
6331: x_return_status => x_return_status);
6332:
6333: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6377: i NUMBER := 0;
6378: begin
6379:
6380: select container_name into detail_cont_name
6381: from wsh_delivery_details where delivery_detail_id = p_detail_id1;
6382: if SQL%NOTFOUND then
6383: detail_cont_name := '(' || p_detail_id1 || ')';
6384: end if;
6385:
6388: detail_cont_name);
6389: END IF;
6390:
6391: select container_name into parent_cont_name
6392: from wsh_delivery_details where delivery_detail_id = p_detail_id2;
6393: if SQL%NOTFOUND then
6394: detail_cont_name := '(' || p_detail_id2 || ')';
6395: end if;
6396: IF l_debug_on THEN
6488: if c_del_id_for_cont_or_detail%ISOPEN THEN
6489: close c_del_id_for_cont_or_detail;
6490: end if;
6491: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6492: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',l_module_name);
6493: --
6494: IF l_debug_on THEN
6495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6585: wdd.weight_uom_code,
6586: wdd.volume_uom_code,
6587: wdd.inventory_item_id
6588: FROM wsh_delivery_assignments_v wda,
6589: wsh_delivery_details wdd
6590: WHERE wda.delivery_detail_id = p_detail_id
6591: and wdd.delivery_detail_id = wda.delivery_detail_id
6592: and ((wda.delivery_id IS not null) or (wda.parent_delivery_detail_id IS not null));
6593:
6602: update_mol_carton_group_error exception;
6603:
6604: cursor get_mo_line_id (p_del_det_id IN NUMBER) is
6605: select move_order_line_id, organization_id
6606: from wsh_delivery_details
6607: where delivery_detail_id = p_del_det_id
6608: and released_status = 'S';
6609:
6610: cursor c_grouping_id is
6675:
6676: cursor l_parent_cnt_csr (p_cnt_inst_id IN NUMBER) is
6677: select organization_id,
6678: nvl(line_direction,'O')
6679: from wsh_delivery_details
6680: where delivery_detail_id = p_cnt_inst_id
6681: and container_flag = 'Y'
6682: and source_code = 'WSH';
6683:
6691: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
6692: l_is_delivery_empty VARCHAR2(1);
6693: l_gc3_is_installed VARCHAR2(1);
6694: l_call_update VARCHAR2(1);
6695: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE;
6696: l_gross_weight_tbl WSH_UTIL_CORE.ID_TAB_TYPE;
6697: -- End of OTM R12 : unassign delivery detail
6698:
6699: l_debug_on BOOLEAN;
6904: gross_weight -- OTM R12 : unassign delivery detail
6905: INTO l_dummy_del_det_id, l_line_dir_tbl(i), l_cnt_flag_tbl(i),
6906: l_det_org_id_tbl(i),
6907: l_gross_weight_tbl(i) -- OTM R12 : unassign delivery detail
6908: FROM wsh_delivery_details
6909: WHERE delivery_detail_id=l_dd_id(i)
6910: FOR UPDATE NOWAIT;
6911:
6912: -- OTM R12 : unassign delivery detail
6990: WHERE delivery_detail_id = l_dd_id(j);
6991:
6992: -- bug 6700792: OTM Dock Door Appt Sched Proj
6993: --Upadating the loading sequence of the delivery detail to NULL while unassigning from delivery
6994: UPDATE wsh_delivery_details
6995: SET load_seq_number = NULL,
6996: last_update_date = SYSDATE,
6997: last_updated_by = FND_GLOBAL.USER_ID,
6998: last_update_login = FND_GLOBAL.LOGIN_ID
7003: RAISE NO_DATA_FOUND;
7004: END IF;
7005:
7006: l_mdc_detail_tab(1) := l_dd_id(j);
7007: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
7008: p_detail_id_tab => l_mdc_detail_tab,
7009: x_return_status => x_return_status);
7010:
7011: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7084: --}
7085: END IF;
7086: -- K LPN CONV. rv
7087: --
7088: UPDATE wsh_delivery_details
7089: SET customer_id = NULL,
7090: ship_to_location_id = NULL,
7091: intmed_ship_to_location_id = NULL,
7092: fob_code = NULL,
7109: --
7110:
7111: END IF;
7112: IF l_ib_upd_flag ='Y' THEN
7113: UPDATE wsh_delivery_details
7114: SET ship_from_location_id = -1, -- J-IB-NPARIKH
7115: routing_req_id = NULL, -- J-IB-NPARIKH
7116: rcv_shipment_line_id = NULL, -- J-IB-NPARIKH
7117: shipped_quantity = NULL, -- J-IB-NPARIKH
7246: -- End of OTM R12 : unassign delivery detail
7247:
7248: -- Call DBI api after the loop
7249: -- DBI Project
7250: -- Update of wsh_delivery_details, call DBI API after the action.
7251: -- This API will also check for DBI Installed or not
7252: IF l_debug_on THEN
7253: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',l_detail_tab.count);
7254: END IF;
7342: WHERE delivery_detail_id = p_detail_id;
7343:
7344: --OTM Dock Door Appt Sched Proj
7345: --Upadating the loading sequence of the delivery detail to NULL while unassigning from delivery
7346: UPDATE wsh_delivery_details
7347: SET load_seq_number = NULL,
7348: last_update_date = SYSDATE,
7349: last_updated_by = FND_GLOBAL.USER_ID,
7350: last_update_login = FND_GLOBAL.LOGIN_ID
7355: RAISE NO_DATA_FOUND;
7356: END IF;
7357:
7358: l_mdc_detail_tab(1) := p_detail_id;
7359: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
7360: p_detail_id_tab => l_mdc_detail_tab,
7361: x_return_status => x_return_status);
7362:
7363: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7553: END IF;
7554:
7555: WHEN others THEN
7556: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7557: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',l_module_name);
7558: ROLLBACK TO before_unassign;
7559:
7560:
7561: --
7605: WSH_DEBUG_SV.log(l_module_name,'P_VALIDATE_FLAG',P_VALIDATE_FLAG);
7606: END IF;
7607: --
7608: IF l_debug_on THEN
7609: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
7610: END IF;
7611: UNASSIGN_DETAIL_FROM_DELIVERY (p_detail_id,
7612: x_return_status,
7613: p_validate_flag,
7619: --
7620: EXCEPTION
7621: WHEN others THEN
7622: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7623: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',l_module_name);
7624: --
7625: IF l_debug_on THEN
7626: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7627: END IF;
8226: END IF;
8227: --
8228: WHEN others THEN
8229: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8230: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',l_module_name);
8231: --
8232: IF l_debug_on THEN
8233: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8234: END IF;
8309: --
8310: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8311: --
8312: IF l_debug_on THEN
8313: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
8314: END IF;
8315: --
8316: UNASSIGN_MULTIPLE_DETAILS
8317: (
8329:
8330: EXCEPTION
8331: WHEN others THEN
8332: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8333: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAIL',l_module_name);
8334: --
8335: IF l_debug_on THEN
8336: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8337: END IF;
8354: IS
8355: l_rowid varchar2(150);
8356: l_group_status varchar2(30);
8357: l_ship_method_match boolean;
8358: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_TYPE;
8359: l_cr_assg_status varchar2(30);
8360: l_delivery_assignment_id NUMBER;
8361: del_id number;
8362: cont_id number;
8460: --
8461: IF l_debug_on THEN
8462: WSH_DEBUG_SV.log(l_module_name,'Delivery id ',p_rec_of_detail_ids(i));
8463:
8464: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
8465: END IF;
8466: --
8467: wsh_delivery_details_actions.assign_detail_to_delivery(
8468: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8463:
8464: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
8465: END IF;
8466: --
8467: wsh_delivery_details_actions.assign_detail_to_delivery(
8468: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8469: p_delivery_id => p_delivery_id,
8470: x_return_status => x_return_status,
8471: x_dlvy_has_lines => l_has_lines, -- J-IB-NPARIKH
8490:
8491: IF (p_cont_ins_id <> -9999) THEN
8492: -- J: W/V Changes
8493: -- replaced the direct update with Assign_Detail_To_Cont API
8494: wsh_delivery_details_actions.Assign_Detail_To_Cont(
8495: p_detail_id => P_REC_OF_DETAIL_IDS(i),
8496: p_parent_detail_id => p_cont_ins_id,
8497: x_return_status => x_return_status);
8498:
8606: END IF;
8607: --
8608: WHEN others THEN
8609: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8610: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_MULTIPLE_DETAILS',l_module_name);
8611:
8612: --
8613: IF l_debug_on THEN
8614: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8697: mark reprice required flag when split_delivery_details
8698: */
8699: l_entity_ids WSH_UTIL_CORE.id_tab_type;
8700: reprice_required_err EXCEPTION;
8701: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
8702: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8703: l_split_weight number;
8704: l_split_volume number;
8705: l_split_return_status varchar2(30);
8712: l_num_of_split NUMBER;
8713:
8714: old_delivery_detail_rec SplitDetailRecType;
8715:
8716: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
8717:
8718:
8719: -- Bug 2637876
8720: l_original_shipped_qty NUMBER;
8718:
8719: -- Bug 2637876
8720: l_original_shipped_qty NUMBER;
8721: l_split_shipped_qty NUMBER;
8722: l_serial_tab WSH_DELIVERY_DETAILS_ACTIONS.serial_tab;
8723: i NUMBER;
8724: j NUMBER;
8725: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8726: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8721: l_split_shipped_qty NUMBER;
8722: l_serial_tab WSH_DELIVERY_DETAILS_ACTIONS.serial_tab;
8723: i NUMBER;
8724: j NUMBER;
8725: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8726: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8727: l_transaction_id_tab WSH_UTIL_CORE.id_tab_type;
8728: l_serial_number WSH_DELIVERY_DETAILS.serial_number%TYPE;
8729: TYPE l_sr_no_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8724: j NUMBER;
8725: l_serial_orig_rec WSH_DELIVERY_DETAILS_ACTIONS.SplitDetailRecType;
8726: l_new_serial_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
8727: l_transaction_id_tab WSH_UTIL_CORE.id_tab_type;
8728: l_serial_number WSH_DELIVERY_DETAILS.serial_number%TYPE;
8729: TYPE l_sr_no_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8730: l_serial_number_tab l_sr_no_tab;
8731: l_id1 NUMBER;
8732: l_s1 VARCHAR2(30);
8935:
8936: -- Bug 4455732 : Combining transaction_temp_id and serial_number update into single cursor
8937: -- since single quantity detail can have transaction_temp_id also.
8938: FORALL i IN 1..l_dd_id_tab.count
8939: UPDATE wsh_delivery_details
8940: SET serial_number = decode(l_serial_number_tab(i),FND_API.G_MISS_CHAR,NULL,
8941: NULL,serial_number,l_serial_number_tab(i)) ,
8942: transaction_temp_id = decode(l_transaction_id_tab(i),FND_API.G_MISS_NUM,NULL,
8943: NULL,transaction_temp_id,l_transaction_id_tab(i))
9023: END IF;
9024: --
9025: WHEN others THEN
9026: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9027: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',l_module_name);
9028: --
9029: IF l_debug_on THEN
9030: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
9031: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
9100: l_output_quantity number;
9101: l_qty_return_status varchar2(30);
9102: new_det_wt_vol_failed exception;
9103: old_det_wt_vol_failed exception;
9104: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
9105: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
9106: l_split_weight number;
9107: l_split_volume number;
9108: l_split_return_status varchar2(30);
9128: l_org_wv_qty NUMBER;
9129: l_new_wv_qty NUMBER;
9130: l_final_req_qty NUMBER; --bug# 6689448 (replenishment project)
9131:
9132: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
9133:
9134:
9135: WSH_SN_SPLIT_ERR EXCEPTION;
9136: WSH_NO_DATA_FOUND EXCEPTION;
9146: l_req_qty2_update_index NUMBER;
9147:
9148: CURSOR c_get_req_pick_qty(p_del_det IN NUMBER) IS
9149: SELECT requested_quantity, picked_quantity,requested_quantity2
9150: FROM wsh_delivery_details
9151: WHERE delivery_detail_id = p_del_det;
9152:
9153:
9154:
9152:
9153:
9154:
9155: -- Bug 2734868
9156: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9157: -- HW 12345 Added qty2
9158: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9159: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9160: wsh_split_error EXCEPTION;
9154:
9155: -- Bug 2734868
9156: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9157: -- HW 12345 Added qty2
9158: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9159: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9160: wsh_split_error EXCEPTION;
9161:
9162: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
9155: -- Bug 2734868
9156: l_requested_quantity WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9157: -- HW 12345 Added qty2
9158: l_requested_quantity2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE;
9159: l_picked_quantity WSH_DELIVERY_DETAILS.picked_quantity%TYPE;
9160: wsh_split_error EXCEPTION;
9161:
9162: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
9163: l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
9302:
9303: --
9304: /*
9305: IF l_debug_on THEN
9306: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
9307: END IF;
9308: --
9309: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
9310: p_delivery_detail_rec => l_delivery_details_info);
9305: IF l_debug_on THEN
9306: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
9307: END IF;
9308: --
9309: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
9310: p_delivery_detail_rec => l_delivery_details_info);
9311: */
9312:
9313: IF p_old_delivery_detail_rec.picked_quantity IS NULL THEN
9429: l_num_of_split := p_num_of_split;
9430:
9431: --
9432: IF l_debug_on THEN
9433: 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);
9434: END IF;
9435: --
9436: -- call to new API
9437: -- with a value for p_num_of_rec
9434: END IF;
9435: --
9436: -- call to new API
9437: -- with a value for p_num_of_rec
9438: WSH_DELIVERY_DETAILS_PKG.create_dd_from_old_bulk(
9439: p_delivery_detail_rec => l_delivery_details_info,
9440: p_delivery_detail_id => p_old_delivery_detail_rec.delivery_detail_id,
9441: p_num_of_rec => l_num_of_split,
9442: x_dd_id_tab => l_dd_id_tab,
9526: -- l_req_qty_update_index = CEIL(3/2) = 2
9527: -- Update at l_dd_id_tab(l_req_qty_update_index) = l_dd_id_tab(2) is a non-exist value which will fail.
9528: IF l_req_qty_update_index <= l_dd_id_tab.count THEN
9529: -- 12345 HW added qty2
9530: update wsh_delivery_details
9531: set requested_quantity = l_requested_quantity - ((l_req_qty_update_index - 1) * l_delivery_details_info.requested_quantity),
9532: 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))
9533: where delivery_detail_id = l_dd_id_tab(l_req_qty_update_index);
9534: END IF;
9537: -- Set the requested quantity to zero in all the newly created lines after the above index.
9538:
9539: FORALL i in (l_req_qty_update_index + 1) .. l_dd_id_tab.count
9540: -- HW added qty2
9541: update wsh_delivery_details
9542: set requested_quantity = 0,
9543: requested_quantity2 = 0
9544: where delivery_detail_id = l_dd_id_tab(i);
9545:
9546: --
9547: -- DBI Project,Above 2 updates for requested quantity need to be tracked for DBI Call
9548: -- These can be combined since both use l_dd_id_tab
9549: -- DBI Project
9550: -- Update of wsh_delivery_details where requested_quantity/released_status
9551: -- are changed, call DBI API after the update.
9552: -- DBI API will check if DBI is installed
9553: IF l_debug_on THEN
9554: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_dd_id_tab.count);
9638:
9639: l_delivery_assignments_info.delivery_detail_id := l_new_delivery_detail_id;
9640: --
9641: IF l_debug_on THEN
9642: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
9643: END IF;
9644: --
9645:
9646: -- this is a bulk call
9645:
9646: -- this is a bulk call
9647: -- with new parameter of p_num_of_rec
9648: -- and pass l_dd_id_tab which will pass the delivery detail id
9649: wsh_delivery_details_pkg.create_deliv_assignment_bulk(
9650: p_delivery_assignments_info => l_delivery_assignments_info,
9651: p_num_of_rec => l_num_of_split,
9652: p_dd_id_tab => l_dd_id_tab,
9653: x_da_id_tab => l_da_id_tab,
9661: l_delivery_assignments_info.type := 'C';
9662: l_delivery_assignments_info.parent_delivery_id := p_old_delivery_detail_rec.parent_delivery_id;
9663: l_delivery_assignments_info.parent_delivery_detail_id := p_old_delivery_detail_rec.parent_delivery_detail_id;
9664:
9665: wsh_delivery_details_pkg.create_deliv_assignment_bulk(
9666: p_delivery_assignments_info => l_delivery_assignments_info,
9667: p_num_of_rec => l_num_of_split,
9668: p_dd_id_tab => l_dd_id_tab,
9669: x_da_id_tab => l_da_id_tab,
9728: -- OPM HW Included this fix in 3011758
9729: -- Modified requested_quantity2,picked_quantity2 and shipped_quantity2
9730: -- to be the same as quantity1s
9731:
9732: UPDATE wsh_delivery_details
9733: SET requested_quantity = GREATEST(requested_quantity - (l_num_of_split*l_delivery_details_info.requested_quantity), 0),
9734: requested_quantity2 = GREATEST(requested_quantity2 -(l_num_of_split*l_qty2), 0),
9735: picked_quantity = GREATEST(picked_quantity - (l_num_of_split*p_quantity_to_split),0),
9736: -- HW OPMCONV. No need to use l_new_pick_qty2
9772: --
9773:
9774: --
9775: -- DBI Project
9776: -- Update of wsh_delivery_details where requested_quantity/released_status
9777: -- are changed, call DBI API after the update.
9778: -- This API will also check for DBI Installed or not
9779: IF l_debug_on THEN
9780: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_old_delivery_detail_rec.delivery_detail_id);
9903: CLOSE c_get_req_pick_qty;
9904: END IF;
9905: rollback to split_savepoint;
9906: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9907: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DETAIL_INT_BULK',l_module_name);
9908: --
9909: IF l_debug_on THEN
9910: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
9911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
9984: mark reprice required flag when split_delivery_details
9985: */
9986: l_entity_ids WSH_UTIL_CORE.id_tab_type;
9987: reprice_required_err EXCEPTION;
9988: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
9989: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
9990: l_split_weight number;
9991: l_split_volume number;
9992: l_split_return_status varchar2(30);
9996:
9997:
9998: old_delivery_detail_rec SplitDetailRecType;
9999:
10000: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
10001:
10002:
10003: --
10004: l_debug_on BOOLEAN;
10246: END IF;
10247: --
10248: WHEN others THEN
10249: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10250: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',l_module_name);
10251: --
10252: IF l_debug_on THEN
10253: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
10254: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10316: l_output_quantity number;
10317: l_qty_return_status varchar2(30);
10318: new_det_wt_vol_failed exception;
10319: old_det_wt_vol_failed exception;
10320: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
10321: l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
10322: l_split_weight number;
10323: l_split_volume number;
10324: l_split_return_status varchar2(30);
10360:
10361: -- HW OPMCONV. Removed OPM excpetion
10362:
10363:
10364: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
10365:
10366: WSH_CREATE_DET_ERR EXCEPTION;
10367: WSH_SN_SPLIT_ERR EXCEPTION;
10368:
10487:
10488: --
10489: /*
10490: IF l_debug_on THEN
10491: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
10492: END IF;
10493: --
10494: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
10495: p_delivery_detail_rec => l_delivery_details_info);
10490: IF l_debug_on THEN
10491: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.INITIALIZE_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
10492: END IF;
10493: --
10494: WSH_DELIVERY_DETAILS_PKG.initialize_detail(
10495: p_delivery_detail_rec => l_delivery_details_info);
10496: */
10497:
10498: IF p_old_delivery_detail_rec.picked_quantity IS NULL THEN
10778: END IF; -- are there serial numbers to split?
10779:
10780: --
10781: IF l_debug_on THEN
10782: 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);
10783: END IF;
10784: --
10785: WSH_DELIVERY_DETAILS_PKG.create_new_detail_from_old(
10786: p_delivery_detail_rec => l_delivery_details_info,
10781: IF l_debug_on THEN
10782: 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);
10783: END IF;
10784: --
10785: WSH_DELIVERY_DETAILS_PKG.create_new_detail_from_old(
10786: p_delivery_detail_rec => l_delivery_details_info,
10787: p_delivery_detail_id => p_old_delivery_detail_rec.delivery_detail_id,
10788: x_row_id => detail_rowid,
10789: x_delivery_detail_id => l_new_delivery_detail_id,
10845:
10846: l_delivery_assignments_info.delivery_detail_id := l_new_delivery_detail_id;
10847: --
10848: IF l_debug_on THEN
10849: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
10850: END IF;
10851: --
10852: wsh_delivery_details_pkg.create_delivery_assignments(
10853: l_delivery_assignments_info,
10848: IF l_debug_on THEN
10849: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
10850: END IF;
10851: --
10852: wsh_delivery_details_pkg.create_delivery_assignments(
10853: l_delivery_assignments_info,
10854: assignment_rowid,
10855: l_delivery_assignment_id,
10856: l_cr_assg_status
10873: l_delivery_assignments_info.parent_delivery_id := p_old_delivery_detail_rec.parent_delivery_id;
10874: l_delivery_assignments_info.parent_delivery_detail_id := p_old_delivery_detail_rec.parent_delivery_detail_id;
10875: l_delivery_assignments_info.type := 'C';
10876:
10877: wsh_delivery_details_pkg.create_delivery_assignments(
10878: l_delivery_assignments_info,
10879: assignment_rowid,
10880: l_delivery_assignment_id,
10881: l_cr_assg_status
10906:
10907: x_split_detail_id := l_new_delivery_detail_id;
10908:
10909: IF l_debug_on THEN
10910: WSH_DEBUG_SV.logmsg(l_module_name, 'Before Update wsh_delivery_details');
10911: WSH_DEBUG_SV.log(l_module_name, 'serial_number',l_updated_delivery_detail_rec.serial_number);
10912: WSH_DEBUG_SV.log(l_module_name, 'to_serial_number',l_updated_delivery_detail_rec.to_serial_number);
10913: WSH_DEBUG_SV.log(l_module_name, 'transaction_temp_id',l_updated_delivery_detail_rec.transaction_temp_id);
10914: END IF;
10912: WSH_DEBUG_SV.log(l_module_name, 'to_serial_number',l_updated_delivery_detail_rec.to_serial_number);
10913: WSH_DEBUG_SV.log(l_module_name, 'transaction_temp_id',l_updated_delivery_detail_rec.transaction_temp_id);
10914: END IF;
10915:
10916: UPDATE wsh_delivery_details
10917: SET requested_quantity = l_original_req_qty, -- J-IB-NPARIKH, GREATEST(requested_quantity - l_delivery_details_info.requested_quantity, 0),
10918: requested_quantity2 = l_original_req_qty2, -- J-IB-NPARIKH, GREATEST(requested_quantity2 - l_qty2, 0),
10919: picked_quantity = l_original_picked_qty, -- J-IB-NPARIKH, picked_quantity - p_quantity_to_split,
10920: picked_quantity2 = l_original_picked_qty2, -- J-IB-NPARIKH, picked_quantity2 - nvl(l_qty2,l_new_pick_qty2),
10942: last_update_login = FND_GLOBAL.LOGIN_ID
10943: WHERE delivery_detail_id = p_old_delivery_detail_rec.delivery_detail_id;
10944:
10945: -- DBI Project
10946: -- Update of wsh_delivery_details where requested_quantity/released_status
10947: -- are changed, call DBI API after the update.
10948: -- This API will also check for DBI Installed or not
10949: IF l_debug_on THEN
10950: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_old_delivery_detail_rec.delivery_detail_id);
11043: END IF;
11044:
11045: WHEN others THEN
11046: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
11047: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DETAIL_INT',l_module_name);
11048: --
11049: IF l_debug_on THEN
11050: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
11051: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
11065: x_return_status OUT NOCOPY VARCHAR2) IS
11066:
11067: l_ser_qty NUMBER;
11068:
11069: l_real_serial_prefix WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11070: l_prefix_length NUMBER;
11071: l_fm_numeric NUMBER;
11072: l_to_numeric NUMBER;
11073: l_range_count NUMBER;
11071: l_fm_numeric NUMBER;
11072: l_to_numeric NUMBER;
11073: l_range_count NUMBER;
11074: l_qty_to_split NUMBER;
11075: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11076: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11077: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11078: l_transaction_temp_id NUMBER := NULL;
11079: l_success NUMBER;
11072: l_to_numeric NUMBER;
11073: l_range_count NUMBER;
11074: l_qty_to_split NUMBER;
11075: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11076: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11077: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11078: l_transaction_temp_id NUMBER := NULL;
11079: l_success NUMBER;
11080:
11073: l_range_count NUMBER;
11074: l_qty_to_split NUMBER;
11075: l_new_sn WSH_DELIVERY_DETAILS.SERIAL_NUMBER%TYPE;
11076: l_new_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11077: l_old_to_sn WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER%TYPE;
11078: l_transaction_temp_id NUMBER := NULL;
11079: l_success NUMBER;
11080:
11081: -- Bug 3782838
11216:
11217: -- we have at least one record in MTL_SERIAL_NUMBERS_TEMP
11218: --
11219: IF l_debug_on THEN
11220: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_SERIAL_QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
11221: END IF;
11222: --
11223: l_ser_qty := WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty(
11224: p_organization_id => x_old_detail_rec.organization_id,
11219: IF l_debug_on THEN
11220: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_SERIAL_QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
11221: END IF;
11222: --
11223: l_ser_qty := WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty(
11224: p_organization_id => x_old_detail_rec.organization_id,
11225: p_delivery_detail_id => x_old_detail_rec.delivery_detail_id);
11226: l_qty_to_split := l_ser_qty - p_old_shipped_quantity;
11227: --
11632:
11633: cursor l_get_cnt_org_csr(p_detail_id IN NUMBER) is
11634: select organization_id,
11635: nvl(line_direction,'O')
11636: from wsh_delivery_details
11637: where delivery_detail_id = p_detail_id;
11638:
11639: l_orgn_id NUMBER;
11640: l_line_dir VARCHAR2(10);
11689: END IF;
11690: CLOSE get_container;
11691:
11692: IF l_debug_on THEN
11693: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
11694: END IF;
11695:
11696: -- Unassign the Line or Container
11697: -- because this
11694: END IF;
11695:
11696: -- Unassign the Line or Container
11697: -- because this
11698: wsh_delivery_details_actions.unassign_detail_from_delivery(
11699: p_detail_id => p_ids_tobe_unassigned(i),
11700: p_validate_flag => p_validate_flag,
11701: x_return_status => l_return_status
11702: );
11737: END IF;
11738:
11739: IF l_line_id IS NULL THEN
11740: IF l_debug_on THEN
11741: WSH_DEBUG_SV.log(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_UNPACK_EMPTY_CONT
11742: recursively for ',l_parent_container_id(1));
11743: END IF;
11744:
11745: -- lpn conv
12015: --
12016: END IF;
12017:
12018: select requested_quantity,requested_quantity2 into l_qty1,l_qty2
12019: from wsh_delivery_details
12020: where delivery_detail_id = p_new_delivery_detail_id;
12021:
12022: IF l_debug_on THEN
12023: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
12153: -- in p_Cons_Source_Line_Rec_Tab.
12154: --
12155: -- HW OPM BUG#:3121616 added requested_quantity2
12156: PROCEDURE Consolidate_Source_Line(
12157: p_Cons_Source_Line_Rec_Tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12158: x_consolidate_ids OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12159: x_return_status OUT NOCOPY VARCHAR2 ) IS
12160:
12161: CURSOR get_bo_dds_cur(p_delivery_detail_id IN NUMBER, p_source_line_id IN NUMBER,p_delivery_id IN NUMBER) is
12159: x_return_status OUT NOCOPY VARCHAR2 ) IS
12160:
12161: CURSOR get_bo_dds_cur(p_delivery_detail_id IN NUMBER, p_source_line_id IN NUMBER,p_delivery_id IN NUMBER) is
12162: SELECT wdd.delivery_detail_id, wdd.requested_quantity,wdd.requested_quantity2
12163: FROM wsh_delivery_details wdd,
12164: wsh_delivery_assignments_v wda
12165: WHERE wdd.source_line_id = p_source_line_id
12166: AND wdd.delivery_detail_id <> p_delivery_detail_id
12167: AND wdd.released_status = 'B'
12371: --
12372: -- Debug Statements
12373: --
12374: IF l_debug_on THEN
12375: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
12376: END IF;
12377: --
12378: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12379: p_delivery_detail_id => l_delete_dd_ids(i),
12374: IF l_debug_on THEN
12375: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
12376: END IF;
12377: --
12378: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12379: p_delivery_detail_id => l_delete_dd_ids(i),
12380: x_return_status => l_return_status );
12381:
12382: --
12397: END IF;
12398: -- Updating the selected Delivery Details for the consolidation with corresponding total Qty
12399: FOR i IN 1..l_cons_dd_ids.COUNT LOOP
12400: -- HW OPM BUG#:3121616 added qty2s
12401: UPDATE wsh_delivery_details
12402: SET requested_quantity = l_cons_qtys(i),
12403: requested_quantity2 = l_cons_qty2s(i),
12404: tracking_number = null,
12405: master_container_item_id = null,
12420: END LOOP;
12421:
12422: --
12423: -- DBI Project
12424: -- Update of wsh_delivery_details where requested_quantity/released_status
12425: -- are changed, call DBI API after the update.
12426: -- DBI API will check if DBI is installed
12427: IF l_debug_on THEN
12428: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_cons_dd_ids.count);
12492: END IF;
12493: -- Updating the partial Back order cases. Qty should be subtracted for the partial case
12494: FORALL i IN 1..l_partial_dd_ids.COUNT
12495: -- HW OPM BUG#:3121616 added qty2s
12496: UPDATE wsh_delivery_details
12497: SET requested_quantity = l_partial_req_qtys(i),
12498: requested_quantity2 = l_partial_req_qty2s(i)
12499: WHERE delivery_detail_id = l_partial_dd_ids (i);
12500:
12499: WHERE delivery_detail_id = l_partial_dd_ids (i);
12500:
12501: --
12502: -- DBI Project
12503: -- Update of wsh_delivery_details where requested_quantity/released_status
12504: -- are changed, call DBI API after the update.
12505: -- DBI API will check if DBI is installed
12506: IF l_debug_on THEN
12507: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_partial_dd_ids.count);
12538: INTO l_gross_weight,
12539: l_net_weight,
12540: l_volume,
12541: l_wv_frozen_flag
12542: FROM wsh_delivery_details
12543: WHERE delivery_detail_id = l_partial_dd_ids(l_index);
12544:
12545: IF l_wv_frozen_flag = 'Y' THEN
12546:
12555: IF l_debug_on THEN
12556: 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);
12557: END IF;
12558:
12559: UPDATE wsh_delivery_details
12560: set gross_weight = l_new_gross_wt,
12561: net_weight = l_new_net_wt,
12562: volume = l_new_vol
12563: WHERE delivery_detail_id = l_partial_dd_ids(l_index);
12619:
12620:
12621: WHEN OTHERS THEN
12622: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
12623: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Lines ' );
12624: --
12625: -- Debug Statements
12626: --
12627: IF l_debug_on THEN
12662: l_organization_id NUMBER := NULL;
12663: l_subinventory VARCHAR2(10) := NULL;
12664: l_serial_number VARCHAR2(30) := NULL;
12665: l_transaction_temp_id NUMBER := NULL;
12666: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
12667: l_return_status VARCHAR2(5) := NULL;
12668:
12669: --
12670: l_debug_on BOOLEAN;
12696: --
12697: -- Debug Statements
12698: --
12699: IF l_debug_on THEN
12700: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
12701: END IF;
12702: --
12703: l_delivery_detail_ids(1) := p_delivery_detail_id;
12704: IF (p_delete_flag = 'Y') THEN --{
12701: END IF;
12702: --
12703: l_delivery_detail_ids(1) := p_delivery_detail_id;
12704: IF (p_delete_flag = 'Y') THEN --{
12705: WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
12706: p_ids_tobe_unassigned => l_delivery_detail_ids ,
12707: p_validate_flag => 'N',
12708: x_return_status => l_return_status
12709: );
12721: IF l_debug_on THEN
12722: WSH_DEBUG_SV.logmsg(l_module_name,'Unreserving the Backordered quantity ',WSH_DEBUG_SV.C_PROC_LEVEL);
12723: WSH_DEBUG_SV.logmsg(l_module_name,'... delivery_detail_id '|| p_delivery_detail_id,WSH_DEBUG_SV.C_PROC_LEVEL);
12724: WSH_DEBUG_SV.logmsg(l_module_name,'... backordered quantity '||p_bo_qty ,WSH_DEBUG_SV.C_PROC_LEVEL);
12725: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
12726: END IF;
12727: --
12728: wsh_delivery_details_actions.unreserve_delivery_detail(
12729: p_delivery_Detail_id => p_delivery_detail_id,
12724: WSH_DEBUG_SV.logmsg(l_module_name,'... backordered quantity '||p_bo_qty ,WSH_DEBUG_SV.C_PROC_LEVEL);
12725: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
12726: END IF;
12727: --
12728: wsh_delivery_details_actions.unreserve_delivery_detail(
12729: p_delivery_Detail_id => p_delivery_detail_id,
12730: p_unreserve_mode => p_bo_mode ,
12731: p_quantity_to_unreserve => p_bo_qty,
12732: p_override_retain_ato_rsv => 'N',
12747: IF l_debug_on THEN
12748: WSH_DEBUG_SV.logmsg(l_module_name,'Unreserving the Backordered quantity ',WSH_DEBUG_SV.C_PROC_LEVEL);
12749: WSH_DEBUG_SV.logmsg(l_module_name,'... delivery_detail_id '|| p_delivery_detail_id,WSH_DEBUG_SV.C_PROC_LEVEL);
12750: WSH_DEBUG_SV.logmsg(l_module_name,'... overpicked quantity '||p_overpick_qty ,WSH_DEBUG_SV.C_PROC_LEVEL);
12751: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
12752: END IF;
12753: --
12754: --Bug 4721577 Do not retain reservation for overpicked quantities
12755: IF p_bo_mode = 'RETAIN_RSV' THEN
12752: END IF;
12753: --
12754: --Bug 4721577 Do not retain reservation for overpicked quantities
12755: IF p_bo_mode = 'RETAIN_RSV' THEN
12756: wsh_delivery_details_actions.unreserve_delivery_detail(
12757: p_delivery_Detail_id => p_delivery_detail_id,
12758: p_unreserve_mode => 'UNRESERVE' ,
12759: p_quantity_to_unreserve => p_overpick_qty,
12760: p_override_retain_ato_rsv => 'Y',
12761: p_quantity2_to_unreserve => p_bo_qty2s,
12762: x_return_status => l_return_status
12763: );
12764: ELSE
12765: wsh_delivery_details_actions.unreserve_delivery_detail(
12766: p_delivery_Detail_id => p_delivery_detail_id,
12767: p_unreserve_mode => p_bo_mode ,
12768: p_quantity_to_unreserve => p_overpick_qty,
12769: p_override_retain_ato_rsv => 'Y',
12782: --
12783: -- Debug Statements
12784: --
12785: IF l_debug_on THEN
12786: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
12787: END IF;
12788: --
12789:
12790: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12786: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
12787: END IF;
12788: --
12789:
12790: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
12791: p_delivery_detail_id => p_delivery_detail_id,
12792: p_cancel_flag => 'N',
12793: x_return_status => l_return_status);
12794: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
12804:
12805: EXCEPTION
12806: WHEN others THEN
12807: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
12808: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.Process_Delivery_Details',l_module_name);
12809: --
12810: IF l_debug_on THEN
12811: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
12812: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
12837: -- delivery detail for each source line id.
12838: --
12839: -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
12840: PROCEDURE Consolidate_Delivery_Details(
12841: p_delivery_details_tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12842: p_bo_mode IN VARCHAR2,
12843: x_cons_delivery_details_tab OUT NOCOPY WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12844: x_remain_bo_qtys OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12845: x_remain_bo_qty2s OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12839: -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
12840: PROCEDURE Consolidate_Delivery_Details(
12841: p_delivery_details_tab IN WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12842: p_bo_mode IN VARCHAR2,
12843: x_cons_delivery_details_tab OUT NOCOPY WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab,
12844: x_remain_bo_qtys OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12845: x_remain_bo_qty2s OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
12846: x_return_status OUT NOCOPY VARCHAR2
12847: ) IS
12846: x_return_status OUT NOCOPY VARCHAR2
12847: ) IS
12848: l_line_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the source line ids passed as parameter
12849: l_detail_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the delivery detail ids passed as parameter
12850: l_delivery_details_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab; -- Stores the delivery detail records with req-qty >0
12851: l_freight_detail_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the delivery details for which the freight costs need to be deleted.
12852: l_req_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the requested quantities passed as parameter
12853: l_bo_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the BO quantities passed as parameter
12854: l_overpick_qtys WSH_UTIL_CORE.Id_Tab_Type; -- Stores the Overpicked quantities passed as parameter
13078: raise FND_API.G_EXC_UNEXPECTED_ERROR;
13079: END IF;
13080:
13081: -- HW OPM BUG#:3121616 added qty2s
13082: update wsh_delivery_details
13083: set requested_quantity = requested_quantity - l_bo_qtys(l_cmp_idx),
13084: requested_quantity2 = requested_quantity2 - l_bo_qty2s(l_cmp_idx),
13085: picked_quantity = picked_quantity - l_bo_qtys(l_cmp_idx) - l_overpick_qtys(l_cmp_idx),
13086: picked_quantity2 = picked_quantity2 - l_bo_qty2s(l_cmp_idx),
13103: l_cmp_idx := l_detail_ids.NEXT(l_cmp_idx);
13104: END LOOP; --} inner Loop(l_cmp_idx)
13105:
13106: -- DBI Project
13107: -- Update of wsh_delivery_details where requested_quantity/released_status
13108: -- are changed, call DBI API after the update.
13109: -- This API will also check for DBI Installed or not
13110: IF l_debug_on THEN
13111: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',l_detail_tab.count);
13204: --
13205:
13206: WHEN OTHERS THEN
13207: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13208: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details',l_module_name);
13209: --
13210: -- Debug Statements
13211: --
13212: IF l_debug_on THEN
13258: END IF;
13259: EXCEPTION
13260: WHEN OTHERS THEN
13261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13262: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Top_Detail_from_Delivery',l_module_name);
13263: --
13264: -- Debug Statements
13265: --
13266: IF l_debug_on THEN
13390: EXCEPTION
13391:
13392: WHEN OTHERS THEN
13393: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13394: WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record',l_module_name);
13395: --
13396: -- Debug Statements
13397: --
13398: IF l_debug_on THEN
13401: END IF;
13402: --
13403: END Create_Consol_Record;
13404:
13405: END WSH_DELIVERY_DETAILS_ACTIONS;