[Home] [Help]
43:
44: -- K LPN CONV. rv
45: cursor l_cnt_orgn_csr (p_cnt_inst_id IN NUMBER) IS
46: select organization_id
47: from wsh_delivery_details
48: where delivery_detail_id = p_cnt_inst_id
49: and container_flag = 'Y'
50: and source_code = 'WSH';
51:
52: -- OTM R12 : unassign delivery detail
53:
54: CURSOR c_detail_cont_planning_cur (p_detail_id IN NUMBER) IS
55: SELECT ignore_for_planning
56: FROM wsh_delivery_details
57: WHERE delivery_detail_id = p_detail_id;
58:
59: -- End of OTM R12 : unassign delivery detail
60:
83: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UNASSIGN_BACKORDERED_DETAILS';
84:
85:
86: -- OTM R12 : unassign delivery detail
87: l_ignore_for_planning WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
88: l_is_delivery_empty VARCHAR2(1);
89: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
90: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
91: l_gc3_is_installed VARCHAR2(1);
290:
291: l_mdc_detail_tab(1) := p_backorder_rec_tbl(i).delivery_detail_id;
292: IF p_backorder_rec_tbl(i).planned_flag = 'N' THEN --{
293: l_mdc_detail_tab(1) := p_backorder_rec_tbl(i).delivery_detail_id;
294: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
295: p_detail_id_tab => l_mdc_detail_tab,
296: x_return_status => l_return_status);
297:
298: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
296: x_return_status => l_return_status);
297:
298: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
299: IF l_debug_on THEN
300: WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record');
301: END IF;
302: ROLLBACK TO unassign_backorder_details;
303: l_num_errors := l_num_errors + 1;
304: GOTO next_record;
303: l_num_errors := l_num_errors + 1;
304: GOTO next_record;
305: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
306: IF l_debug_on THEN
307: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error in WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record ');
308: END IF;
309: RAISE e_return;
310: END IF;
311: --}
310: END IF;
311: --}
312: ELSIF p_org_info_tbl(p_backorder_rec_tbl(i).organization_id).wms_org = 'Y' THEN --{
313: l_mdc_detail_tab(1) := p_backorder_rec_tbl(i).delivery_detail_id;
314: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
315: p_detail_id_tab => l_mdc_detail_tab,
316: x_return_status => l_return_status);
317:
318: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
316: x_return_status => l_return_status);
317:
318: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
319: IF l_debug_on THEN
320: WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record');
321: END IF;
322: ROLLBACK TO unassign_backorder_details;
323: l_num_errors := l_num_errors + 1;
324: GOTO next_record;
323: l_num_errors := l_num_errors + 1;
324: GOTO next_record;
325: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
326: IF l_debug_on THEN
327: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error in WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record');
328: END IF;
329: RAISE e_return;
330: END IF;
331: END IF; --}
633: CURSOR get_pref_grade(wdd_id NUMBER) IS
634: SELECT oelines.preferred_grade,
635: wdd.organization_id, -- LPN CONV. rv
636: nvl(wdd.line_direction,'O') -- LPN CONV. rv
637: FROM oe_order_lines_all oelines, wsh_delivery_details wdd
638: WHERE wdd.delivery_detail_id = wdd_id
639: AND wdd.source_code = 'OE'
640: AND wdd.source_line_id = oelines.line_id;
641:
691: FETCH get_pref_grade INTO l_oeline_pref_grade,
692: l_organization_id, l_line_direction; -- LPN CONV. rv
693: CLOSE get_pref_grade;
694:
695: UPDATE wsh_delivery_details
696: SET released_status = DECODE(requested_quantity, 0, 'D', decode(pickable_flag,'Y','B','X')) ,
697: requested_quantity = DECODE(p_requested_quantity, NULL, requested_quantity, p_requested_quantity),
698: requested_quantity2 = DECODE(p_requested_quantity2, NULL, requested_quantity2, p_requested_quantity2),
699: subinventory = original_subinventory,
723: --Added organization_id for raise event: Pick to POD workflow
724:
725: --
726: -- DBI Project
727: -- Update of wsh_delivery_details where released_status
728: -- are changed, call DBI API after the update.
729: -- This API will also check for DBI Installed or not
730: --DBI
731: WSH_DD_TXNS_PVT. create_dd_txn_from_dd (
797: -- replenishment requested lines.
798: IF ( p_replenishment_status IS NULL ) THEN
799: --{
800: IF l_debug_on THEN
801: WSH_DEBUG_SV.log(l_module_name,'Updated wsh_delivery_details for backordered_delivery_detail ' ,p_delivery_detail_id);
802: END IF;
803: -- If Pick Release is run in Parallel Mode, then Unassigning is Deferred
804: -- Unassignment API is later called from WSH_PICK_LIST.Release_Batch API
805: -- For backordering in other cases or Non-Parallel Pick Release, unassign api is called immediately
1647: l_organization_id NUMBER;
1648:
1649: Cursor get_dd_info (p_delivery_detail_id IN NUMBER) is
1650: Select organization_id
1651: From wsh_delivery_details
1652: Where delivery_detail_id = p_delivery_detail_id;
1653: BEGIN
1654: --
1655: -- Debug Statements
1802: l_delivery_detail_id NUMBER;
1803:
1804: cursor pickable_flag (p_delivery_detail IN number) is
1805: select pickable_flag
1806: from wsh_delivery_details
1807: where delivery_detail_id = p_delivery_detail;
1808:
1809: cancel_failed EXCEPTION;
1810:
1903: --
1904: -- Debug Statements
1905: --
1906: IF l_debug_on THEN
1907: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_RESERVABLE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
1908: END IF;
1909: --
1910: l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1911: x_item_id => p_delivery_detail_split_rec.inventory_item_id,
1906: IF l_debug_on THEN
1907: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_RESERVABLE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
1908: END IF;
1909: --
1910: l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1911: x_item_id => p_delivery_detail_split_rec.inventory_item_id,
1912: x_organization_id => p_delivery_detail_split_rec.organization_id,
1913: x_pickable_flag => l_pickable_flag);
1914: IF l_debug_on THEN
2091: -- Bug 2595657
2092: CURSOR c_nonstaged_qty is
2093: SELECT nvl(sum(requested_quantity),0),
2094: nvl(sum(requested_quantity2),0)
2095: FROM wsh_delivery_details
2096: WHERE source_line_id = p_source_line_id
2097: and source_header_id = p_source_header_id
2098: and source_code = p_source_code
2099: and organization_id = p_organization_id
3211:
3212: l_delivery_detail_id NUMBER;
3213: split_failed EXCEPTION;
3214:
3215: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
3216: -- muom
3217: l_fulfillment_base varchar2(1);
3218:
3219: --
3343: IF p_delivery_detail_split_rec.move_order_line_id IS NOT NULL THEN
3344: IF l_debug_on THEN
3345: WSH_DEBUG_SV.log(l_module_name,'Updating released_status for detail');
3346: END IF;
3347: UPDATE wsh_delivery_details
3348: SET released_status = 'R',
3349: move_order_line_id = NULL
3350: WHERE delivery_detail_id = p_delivery_detail_split_rec.delivery_detail_id
3351: RETURNING organization_id -- done for Workflow Project
3367: --Done Raise Event: Pick To Pod Workflow
3368:
3369: --
3370: -- DBI Project
3371: -- Update of wsh_delivery_details where released_status
3372: -- are changed, call DBI API after the update.
3373: -- This API will also check for DBI Installed or not
3374: IF l_debug_on THEN
3375: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_detail_split_rec.delivery_detail_id);
3710: IS
3711: CURSOR Get_Sl_Num_Ctrl_Cd IS
3712: SELECT msi.serial_number_control_code
3713: FROM mtl_system_items msi,
3714: wsh_delivery_details wdd
3715: WHERE msi.inventory_item_id = wdd.inventory_item_id
3716: AND msi.organization_id = wdd.organization_id
3717: AND wdd.delivery_detail_id = p_delivery_detail_id;
3718:
3776: RETURN;
3777: END IF;
3778: CLOSE Get_Sl_Num_Ctrl_Cd;
3779:
3780: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
3781: p_delivery_detail_id => p_delivery_detail_id,
3782: p_serial_number_code => l_serial_number_control_code,
3783: p_serial_number => p_serial_number,
3784: p_transaction_temp_id => p_transaction_temp_id,
3797: END IF;
3798: --
3799: RETURN;
3800: END IF;
3801: UPDATE wsh_delivery_details SET
3802: serial_number = NULL,
3803: to_serial_number = NULL,
3804: transaction_temp_id = NULL,
3805: shipped_quantity = 0
3887: service_level, -- End of Bug 4093619(FP-4145867)
3888: requested_quantity_uom2, --Bug# 5436033
3889: client_id, -- LSP PROJECT
3890: source_code
3891: FROM wsh_delivery_details
3892: WHERE delivery_detail_id = p_Changed_Attributes(p_Counter).delivery_detail_id ;
3893:
3894: old_delivery_detail_rec C_Old_Detail%ROWTYPE;
3895:
3897:
3898: -- Added the record to call the WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters
3899: l_global_param_rec_type WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3900:
3901: -- Added tables to call the wsh_delivery_details_actions.Consolidate_Source_Lines
3902: l_Cons_Source_Line_Rec_Tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3903: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type;
3904:
3905: -- To store the shipping global parameter "Consolidate BO lines" value
3898: -- Added the record to call the WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters
3899: l_global_param_rec_type WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3900:
3901: -- Added tables to call the wsh_delivery_details_actions.Consolidate_Source_Lines
3902: l_Cons_Source_Line_Rec_Tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3903: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type;
3904:
3905: -- To store the shipping global parameter "Consolidate BO lines" value
3906: l_auto_consolidate VARCHAR2(1) := 'N';
3966: l_details_to_delete WSH_UTIL_CORE.Id_Tab_Type;
3967: l_delete_count NUMBER := 0;
3968:
3969: l_overpick_rec WSH_INTERFACE.ChangedAttributeRecType;
3970: l_source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE;
3971: l_new_req_quantity NUMBER;
3972: -- HW OPM added qty2
3973: l_new_req_quantity2 NUMBER;
3974: l_delivery_id NUMBER;
3988: -- bug 2805603 : added following variables for call to get_inv_pc_attributes and set_inv_pc_attributes
3989: l_InvPCOutRecType WSH_INTEGRATION.InvPCOutRecType;
3990: l_InvPCInRecType WSH_INTEGRATION.InvPCInRecType;
3991: l_transaction_id NUMBER;
3992: l_transaction_temp_id wsh_delivery_Details.transaction_temp_id%type;
3993: l_msg_count NUMBER;
3994: l_msg_data VARCHAR2(2000);
3995:
3996: l_user_id NUMBER;
4001: l_customer_id NUMBER;
4002: l_ship_to_location_id NUMBER;
4003: l_intmed_ship_to_location_id NUMBER;
4004: l_deliver_to_location_id NUMBER;
4005: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4002: l_ship_to_location_id NUMBER;
4003: l_intmed_ship_to_location_id NUMBER;
4004: l_deliver_to_location_id NUMBER;
4005: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4003: l_intmed_ship_to_location_id NUMBER;
4004: l_deliver_to_location_id NUMBER;
4005: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4004: l_deliver_to_location_id NUMBER;
4005: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4012: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
4005: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4012: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
4013: l_carrier_id NUMBER;
4006: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4012: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
4013: l_carrier_id NUMBER;
4014: l_client_id NUMBER; -- LSP PROJECT
4007: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4012: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
4013: l_carrier_id NUMBER;
4014: l_client_id NUMBER; -- LSP PROJECT
4015:
4008: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
4009: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
4010: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
4011: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
4012: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
4013: l_carrier_id NUMBER;
4014: l_client_id NUMBER; -- LSP PROJECT
4015:
4016: -- K LPN CONV. rvishnuv
4066: Cursor c_get_picked_lines_count (c_delivery_detail_id NUMBER)
4067: IS
4068: SELECT count (wdd.delivery_detail_id), delivery_id
4069: FROM
4070: wsh_delivery_details wdd,
4071: wsh_delivery_assignments_v wda
4072: WHERE
4073: wdd.delivery_detail_id = wda.delivery_detail_id
4074: AND wda.delivery_id = ( SELECT delivery_id
4271: l_delivery_id,
4272: l_planned_flag,
4273: l_del_batch_id
4274: FROM wsh_new_deliveries wnd,
4275: wsh_delivery_details wdd,
4276: wsh_delivery_assignments_v wda
4277: WHERE wdd.delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id
4278: AND wda.delivery_id = wnd.delivery_id
4279: AND wda.delivery_detail_id = wdd.delivery_detail_id ;
4423: END IF;
4424: --
4425: l_changed_attributes(l_counter).action_flag := 'U';
4426: ELSE
4427: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4428: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4429: p_req_quantity => l_split_quantity,
4430: x_new_detail_id => l_dummy_detail_id,
4431: x_return_status => l_return_status,
4477: /*
4478: old_delivery_detail_rec.picked_quantity2 := l_split_quantity2
4479: + p_changed_attributes(l_counter).pending_quantity2; */
4480:
4481: UPDATE WSH_DELIVERY_DETAILS
4482: SET picked_quantity = old_delivery_detail_rec.picked_quantity,
4483: picked_quantity2 = old_delivery_detail_rec.picked_quantity2,
4484: requested_quantity_uom2 = DECODE(l_wms_enabled_flag,
4485: 'Y',
4490: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4491:
4492: IF l_split_quantity < old_delivery_detail_rec.picked_quantity THEN
4493:
4494: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4495: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4496: p_req_quantity => l_split_quantity,
4497: x_new_detail_id => l_dummy_detail_id,
4498: x_return_status => l_return_status,
4513: END IF;
4514:
4515: -- If this delivery line has requested_quantity > 0, it is not pending overpick,
4516: -- and its picked quantity columns should be NULL.
4517: UPDATE wsh_delivery_details
4518: SET picked_quantity = decode(requested_quantity, 0, picked_quantity, NULL),
4519: picked_quantity2 = decode(requested_quantity2, 0, picked_quantity2, NULL)
4520: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4521:
4554: --
4555: l_changed_attributes(l_counter).action_flag := 'U';
4556: ELSE
4557:
4558: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4559: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4560: p_req_quantity => l_split_quantity,
4561: x_new_detail_id => l_dummy_detail_id,
4562: x_return_status => l_return_status,
4604: --Use l_pending_quantity instead of p_changed_attributes(l_counter).pending_quantity
4605: --because p_changed_attributes(l_counter).pending_quantity could be fnd_api.g_miss_num
4606: old_delivery_detail_rec.picked_quantity := l_split_quantity + l_pending_quantity;
4607:
4608: UPDATE WSH_DELIVERY_DETAILS
4609: SET picked_quantity = old_delivery_detail_rec.picked_quantity,
4610: picked_quantity2 = old_delivery_detail_rec.picked_quantity2,
4611: requested_quantity_uom2 = DECODE(l_wms_enabled_flag,
4612: 'Y',
4618:
4619:
4620: IF l_split_quantity2 < old_delivery_detail_rec.picked_quantity2 THEN
4621:
4622: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4623: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4624: p_req_quantity => l_split_quantity,
4625: x_new_detail_id => l_dummy_detail_id,
4626: x_return_status => l_return_status,
4640: END IF;
4641:
4642: -- If this delivery line has requested_quantity > 0, it is not pending overpick,
4643: -- and its picked quantity columns should be NULL.
4644: UPDATE wsh_delivery_details
4645: SET picked_quantity = decode(requested_quantity, 0, picked_quantity, NULL),
4646: picked_quantity2 = decode(requested_quantity2, 0, picked_quantity2, NULL)
4647: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4648:
4694: wsh_debug_sv.log(l_module_name, 'old_delivery_detail_rec.picked_quantity', old_delivery_detail_rec.picked_quantity);
4695: wsh_debug_sv.log(l_module_name, 'old_delivery_detail_rec.picked_quantity2', old_delivery_detail_rec.picked_quantity2);
4696: END IF;
4697:
4698: UPDATE WSH_DELIVERY_DETAILS
4699: SET picked_quantity = old_delivery_detail_rec.picked_quantity,
4700: picked_quantity2 = old_delivery_detail_rec.picked_quantity2
4701: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4702:
4724: --
4725: -- Debug Statements
4726: --
4727: IF l_debug_on THEN
4728: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
4729: END IF;
4730: --
4731: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4732: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4727: IF l_debug_on THEN
4728: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
4729: END IF;
4730: --
4731: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4732: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4733: p_req_quantity => l_split_quantity,
4734: x_new_detail_id => l_dummy_detail_id,
4735: x_return_status => l_return_status,
4753: IF p_changed_attributes(l_counter).picked_quantity <> FND_API.G_MISS_NUM THEN
4754: -- correct the requested quantities of the split lines.
4755: -- be sure to clear the picked quantities if line has requested_quantity > 0.
4756:
4757: UPDATE wsh_delivery_details
4758: SET requested_quantity = LEAST(old_delivery_detail_rec.requested_quantity,
4759: picked_quantity),
4760: requested_quantity2 = LEAST(old_delivery_detail_rec.requested_quantity2,
4761: picked_quantity2)
4763: RETURNING requested_quantity, requested_quantity2 INTO l_split_quantity, l_split_quantity2;
4764:
4765: --
4766: -- DBI Project
4767: -- Update of wsh_delivery_details where requested_quantity
4768: -- are changed, call DBI API after the update.
4769: -- DBI API checks for DBI Installed also
4770: IF l_debug_on THEN
4771: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',old_delivery_detail_rec.delivery_detail_id);
4795: --
4796:
4797: l_split_quantity := GREATEST(old_delivery_detail_rec.requested_quantity - l_split_quantity, 0);
4798: l_split_quantity2 := GREATEST(old_delivery_detail_rec.requested_quantity2 - l_split_quantity2, 0);
4799: UPDATE wsh_delivery_details SET
4800: requested_quantity = l_split_quantity,
4801: requested_quantity2 = l_split_quantity2,
4802: picked_quantity = decode(l_split_quantity, 0, picked_quantity, NULL),
4803: picked_quantity2 = decode(l_split_quantity2, 0, picked_quantity2, NULL)
4827:
4828: ELSE
4829:
4830: -- make sure the newly create detail will be released_status of 'S'
4831: UPDATE wsh_delivery_details
4832: SET released_status = 'S'
4833: WHERE delivery_detail_id = l_dummy_detail_id
4834: RETURNING organization_id
4835: INTO l_organization_id;
4880:
4881: END IF;
4882: --
4883: -- DBI Project
4884: -- Update of wsh_delivery_details where requested_quantity
4885: -- are changed, call DBI API after the update.
4886: -- DBI API checks for DBI Installed also
4887: IF l_debug_on THEN
4888: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_dummy_detail_id);
4933: IF l_debug_on THEN
4934: WSH_DEBUG_SV.logmsg(l_module_name, 'SPLIT_DELIVERY_DETAILS BEING CALLED WITH ' || TO_CHAR ( L_SPLIT_QUANTITY ) );
4935: END IF;
4936: --
4937: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4938: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4939: p_req_quantity => l_split_quantity,
4940: x_new_detail_id => l_dummy_detail_id,
4941: x_return_status => l_return_status,
5077: --
5078: IF g_delivery_detail_id IS NOT NULL THEN
5079: SELECT inventory_item_id
5080: INTO l_backordered_item_id
5081: FROM wsh_delivery_details
5082: WHERE delivery_detail_id = g_delivery_detail_id;
5083: ELSIF g_move_order_line_id IS NOT NULL THEN
5084: SELECT inventory_item_id
5085: INTO l_backordered_item_id
5082: WHERE delivery_detail_id = g_delivery_detail_id;
5083: ELSIF g_move_order_line_id IS NOT NULL THEN
5084: SELECT inventory_item_id
5085: INTO l_backordered_item_id
5086: FROM wsh_delivery_details
5087: WHERE move_order_line_id = g_move_order_line_id;
5088: END IF;
5089: IF g_ship_set_id IS NOT NULL THEN
5090: SELECT set_name
5180:
5181: IF g_delivery_detail_id IS NOT NULL THEN
5182: SELECT inventory_item_id
5183: INTO l_backordered_item_id
5184: FROM wsh_delivery_details
5185: WHERE delivery_detail_id = g_delivery_detail_id;
5186: ELSIF g_move_order_line_id IS NOT NULL THEN
5187: SELECT inventory_item_id
5188: INTO l_backordered_item_id
5185: WHERE delivery_detail_id = g_delivery_detail_id;
5186: ELSIF g_move_order_line_id IS NOT NULL THEN
5187: SELECT inventory_item_id
5188: INTO l_backordered_item_id
5189: FROM wsh_delivery_details
5190: WHERE move_order_line_id = g_move_order_line_id;
5191: END IF;
5192: IF g_ship_model_id IS NOT NULL THEN
5193: SELECT inventory_item_id
5508: --
5509: -- Debug Statements
5510: --
5511: IF l_debug_on THEN
5512: WSH_DEBUG_SV.log(l_module_name, 'WSH_DELIVERY_DETAILS_ACTIONS.CONSOLIDATE_SOURCE_LINE being called with Del Det Id: ',p_Changed_attributes(l_counter).delivery_detail_id);
5513: END IF;
5514: --
5515: l_Cons_Source_Line_Rec_Tab(1).delivery_detail_id:= old_Delivery_detail_rec.delivery_detail_id ;
5516: l_Cons_Source_Line_Rec_Tab(1).delivery_id:= null;
5522: -- muom
5523: l_Cons_Source_Line_Rec_Tab(1).fulfillment_base := l_fulfillment_base;
5524: -- end of 3121616
5525:
5526: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
5527: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
5528: x_consolidate_ids => l_cons_dd_ids,
5529: x_return_status => l_return_status);
5530:
5557: -- muom
5558: WSH_DEBUG_SV.logmsg(l_module_name, 'SPLIT_DELIVERY_DETAILS BEING CALLED WITH ' || TO_CHAR ( L_SPLIT_QUANTITY )||' and '||TO_CHAR ( L_SPLIT_QUANTITY2 ) );
5559: END IF;
5560: --
5561: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
5562: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
5563: p_req_quantity => l_split_quantity,
5564: x_new_detail_id => l_dummy_detail_id,
5565: x_return_status => l_return_status,
5590: -- If it is complete back order case then try to consolidate it
5591: IF ( l_auto_consolidate = 'Y' AND l_dummy_detail_id = p_Changed_attributes(l_counter).delivery_detail_id) THEN
5592: --{
5593: IF l_debug_on THEN
5594: WSH_DEBUG_SV.log(l_module_name, 'WSH_DELIVERY_DETAILS_ACTIONS.CONSOLIDATE_SOURCE_LINE being called with Del Det Id: ',old_Delivery_detail_rec.delivery_detail_id);
5595: END IF;
5596: l_Cons_Source_Line_Rec_Tab(1).delivery_detail_id:= old_Delivery_detail_rec.delivery_detail_id ;
5597: l_Cons_Source_Line_Rec_Tab(1).delivery_id:= null;
5598: l_Cons_Source_Line_Rec_Tab(1).source_line_id:= old_Delivery_detail_rec.source_line_id;
5602: l_Cons_Source_Line_Rec_Tab(1).bo_qty2:= old_Delivery_detail_rec.requested_quantity2;
5603: -- muom
5604: l_Cons_Source_Line_Rec_Tab(1).fulfillment_base := l_fulfillment_base;
5605:
5606: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
5607: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
5608: x_consolidate_ids => l_cons_dd_ids,
5609: x_return_status => l_return_status);
5610:
5699: AND parent_delivery_detail_id IS NOT NULL;
5700:
5701: SELECT container_name
5702: INTO l_container_name
5703: FROM wsh_delivery_details
5704: WHERE delivery_detail_id = l_parent_detail_id;
5705: EXCEPTION
5706: WHEN no_data_found THEN
5707: l_parent_detail_id := NULL;
5807: -- exists
5808:
5809: SELECT count(*)
5810: INTO l_num_containers
5811: FROM wsh_delivery_Details
5812: WHERE lpn_id = p_Changed_Attributes(l_Counter).transfer_lpn_id
5813: AND container_flag = 'Y'
5814: AND delivery_detail_id <> p_changed_attributes(l_Counter).delivery_detail_id
5815: AND nvl(line_direction , 'O') IN ('O', 'IO') -- J-IB-JCKWOK
5912: l_freight_terms_code, l_ship_method_code,
5913: l_line_direction, l_ignore_for_planning,
5914: l_shipping_control, l_carrier_id,
5915: l_service_level, l_mode_of_transport,l_client_id -- LSP PROJECT
5916: FROM wsh_Delivery_Details
5917: WHERE lpn_id = p_changed_attributes(l_Counter).transfer_lpn_id
5918: AND container_flag = 'Y'
5919: --LPN reuse project
5920: AND released_status = 'X'
5946: WSH_WMS_LPN_GRP.g_caller := 'WMS';
5947: WSH_WMS_LPN_GRP.g_callback_required := 'N';
5948: -- LPN CONV rv
5949: --
5950: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
5951: p_changed_attributes(l_Counter).delivery_detail_id,
5952: l_cont_instance_id,
5953: l_return_status);
5954: IF l_debug_on THEN
5964: IF l_debug_on THEN
5965: WSH_DEBUG_SV.logmsg(l_module_name,'Updating the master serial number of the container onto the line');
5966: END IF;
5967:
5968: UPDATE WSH_DELIVERY_DETAILS
5969: SET master_serial_number = (select master_serial_number
5970: from wsh_delivery_details
5971: where delivery_detail_id = l_cont_instance_id
5972: and container_flag = 'Y')
5966: END IF;
5967:
5968: UPDATE WSH_DELIVERY_DETAILS
5969: SET master_serial_number = (select master_serial_number
5970: from wsh_delivery_details
5971: where delivery_detail_id = l_cont_instance_id
5972: and container_flag = 'Y')
5973: WHERE delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id;
5974:
6283: source_code,
6284: organization_id, -- bug 7131800
6285: requested_quantity,
6286: requested_quantity2
6287: FROM wsh_delivery_details
6288: WHERE delivery_detail_id = x_delivery_detail_id;
6289:
6290: CURSOR c_source_info(x_source_line_id IN NUMBER, x_source_code IN VARCHAR2) IS
6291: SELECT ordered_quantity,
6502: --
6503: -- Debug Statements
6504: --
6505: IF l_debug_on THEN
6506: WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WSH_DELIVERY_DETAILS' );
6507: END IF;
6508: --
6509:
6510: -- muom
6649: WSH_DEBUG_SV.log(l_module_name,'Back Order Qty2:',l_Cons_Source_Line_Rec_Tab(1).bo_qty2);
6650:
6651: END IF;
6652: --
6653: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
6654: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
6655: x_consolidate_ids => l_cons_dd_ids,
6656: x_return_status => l_return_status);
6657:
6684: WSH_DEBUG_SV.log(l_module_name, 'Req. Qty.', l_split_quantity);
6685: WSH_DEBUG_SV.log(l_module_name, 'Req. Qty2.', l_split_quantity2);
6686: END IF;
6687:
6688: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
6689: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
6690: p_req_quantity => l_split_quantity,
6691: x_new_detail_id => l_dummy_detail_id,
6692: x_return_status => l_return_status,
6799: WSH_DEBUG_SV.log(l_module_name, 'Right before update');
6800: WSH_DEBUG_SV.log(l_module_name, 'l_new_req_quantity2', l_new_req_quantity2);
6801: END IF;
6802: -- bug 2120494
6803: UPDATE wsh_delivery_details
6804: SET subinventory = decode(l_update_sub,'Y',p_Changed_Attributes(l_Counter).subinventory,subinventory),
6805: locator_id = decode(l_update_loc, 'Y',p_Changed_Attributes(l_Counter).locator_id,locator_id),
6806: revision = decode(l_update_rev ,'Y',p_Changed_Attributes(l_Counter).revision,revision),
6807: lot_number = decode(l_update_lot,'Y',p_Changed_Attributes(l_Counter).lot_number,lot_number),
6863:
6864: --
6865: --
6866: -- DBI Project
6867: -- Update of wsh_delivery_details where requested_quantity
6868: -- are changed, call DBI API after the update.
6869: -- DBI API checks for DBI Installed also
6870: IF l_debug_on THEN
6871: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_dummy_detail_id);
6895: --
6896: -- bug 3632485 When transacted quantity is less than allocated quantity, null out tracking number for backordered delivery detail.
6897: if (old_Delivery_detail_rec.tracking_number is not null
6898: and p_Changed_attributes(l_counter).delivery_detail_id <>l_dummy_detail_id) then
6899: update wsh_delivery_details
6900: set tracking_number = NULL
6901: where delivery_detail_id = p_Changed_attributes(l_counter).delivery_detail_id;
6902: end if;
6903:
7309: -- Procedure : get_putaway_detail_id
7310: -- Description : Procedure to return delivery_detail_id based on move_order_type
7311: -- associated with Move Order Line id.For scenarios where released_status
7312: -- and move_order_line_id are not passed to this API, first
7313: -- derive those from wsh_delivery_details and then proceed.
7314: --
7315: PROCEDURE get_putaway_detail_id(
7316: p_detail_id IN NUMBER,
7317: p_released_status IN VARCHAR2,
7321:
7322: CURSOR c_get_line_details IS
7323: SELECT released_status,
7324: move_order_line_id
7325: FROM wsh_delivery_details
7326: WHERE delivery_detail_id = p_detail_id;
7327:
7328: l_line_details c_get_line_details%ROWTYPE;
7329: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
7325: FROM wsh_delivery_details
7326: WHERE delivery_detail_id = p_detail_id;
7327:
7328: l_line_details c_get_line_details%ROWTYPE;
7329: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
7330: l_move_order_line_id WSH_DELIVERY_DETAILS.MOVE_ORDER_LINE_ID%TYPE;
7331:
7332: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'get_putaway_detail_id';
7333: l_debug_on BOOLEAN;
7326: WHERE delivery_detail_id = p_detail_id;
7327:
7328: l_line_details c_get_line_details%ROWTYPE;
7329: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
7330: l_move_order_line_id WSH_DELIVERY_DETAILS.MOVE_ORDER_LINE_ID%TYPE;
7331:
7332: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'get_putaway_detail_id';
7333: l_debug_on BOOLEAN;
7334:
7370: x_detail_id := NULL;
7371:
7372: --Check only for line with released_status = 'S' and MOL is not null and MO type = PUTAWAY
7373: IF p_detail_id IS NOT NULL AND
7374: l_released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE AND
7375: l_move_order_line_id IS NOT NULL AND
7376: wsh_usa_inv_pvt.is_mo_type_putaway (l_move_order_line_id) = 'Y' THEN
7377: x_detail_id := p_detail_id;
7378: END IF;