[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; --}
626: CURSOR get_pref_grade(wdd_id NUMBER) IS
627: SELECT oelines.preferred_grade,
628: wdd.organization_id, -- LPN CONV. rv
629: nvl(wdd.line_direction,'O') -- LPN CONV. rv
630: FROM oe_order_lines_all oelines, wsh_delivery_details wdd
631: WHERE wdd.delivery_detail_id = wdd_id
632: AND wdd.source_code = 'OE'
633: AND wdd.source_line_id = oelines.line_id;
634:
681: FETCH get_pref_grade INTO l_oeline_pref_grade,
682: l_organization_id, l_line_direction; -- LPN CONV. rv
683: CLOSE get_pref_grade;
684:
685: UPDATE wsh_delivery_details
686: SET released_status = DECODE(requested_quantity, 0, 'D', decode(pickable_flag,'Y','B','X')) ,
687: requested_quantity = DECODE(p_requested_quantity, NULL, requested_quantity, p_requested_quantity),
688: requested_quantity2 = DECODE(p_requested_quantity2, NULL, requested_quantity2, p_requested_quantity2),
689: subinventory = original_subinventory,
710: --Added organization_id for raise event: Pick to POD workflow
711:
712: --
713: -- DBI Project
714: -- Update of wsh_delivery_details where released_status
715: -- are changed, call DBI API after the update.
716: -- This API will also check for DBI Installed or not
717: --DBI
718: WSH_DD_TXNS_PVT. create_dd_txn_from_dd (
784: -- replenishment requested lines.
785: IF ( p_replenishment_status IS NULL ) THEN
786: --{
787: IF l_debug_on THEN
788: WSH_DEBUG_SV.log(l_module_name,'Updated wsh_delivery_details for backordered_delivery_detail ' ,p_delivery_detail_id);
789: END IF;
790: -- If Pick Release is run in Parallel Mode, then Unassigning is Deferred
791: -- Unassignment API is later called from WSH_PICK_LIST.Release_Batch API
792: -- For backordering in other cases or Non-Parallel Pick Release, unassign api is called immediately
1634: l_organization_id NUMBER;
1635:
1636: Cursor get_dd_info (p_delivery_detail_id IN NUMBER) is
1637: Select organization_id
1638: From wsh_delivery_details
1639: Where delivery_detail_id = p_delivery_detail_id;
1640: BEGIN
1641: --
1642: -- Debug Statements
1789: l_delivery_detail_id NUMBER;
1790:
1791: cursor pickable_flag (p_delivery_detail IN number) is
1792: select pickable_flag
1793: from wsh_delivery_details
1794: where delivery_detail_id = p_delivery_detail;
1795:
1796: cancel_failed EXCEPTION;
1797:
1890: --
1891: -- Debug Statements
1892: --
1893: IF l_debug_on THEN
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_RESERVABLE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
1895: END IF;
1896: --
1897: l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1898: x_item_id => p_delivery_detail_split_rec.inventory_item_id,
1893: IF l_debug_on THEN
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_RESERVABLE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
1895: END IF;
1896: --
1897: l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1898: x_item_id => p_delivery_detail_split_rec.inventory_item_id,
1899: x_organization_id => p_delivery_detail_split_rec.organization_id,
1900: x_pickable_flag => l_pickable_flag);
1901: IF l_debug_on THEN
2078: -- Bug 2595657
2079: CURSOR c_nonstaged_qty is
2080: SELECT nvl(sum(requested_quantity),0),
2081: nvl(sum(requested_quantity2),0)
2082: FROM wsh_delivery_details
2083: WHERE source_line_id = p_source_line_id
2084: and source_header_id = p_source_header_id
2085: and source_code = p_source_code
2086: and organization_id = p_organization_id
2929:
2930: l_delivery_detail_id NUMBER;
2931: split_failed EXCEPTION;
2932:
2933: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
2934:
2935: --
2936: l_debug_on BOOLEAN;
2937: --
3053: IF p_delivery_detail_split_rec.move_order_line_id IS NOT NULL THEN
3054: IF l_debug_on THEN
3055: WSH_DEBUG_SV.log(l_module_name,'Updating released_status for detail');
3056: END IF;
3057: UPDATE wsh_delivery_details
3058: SET released_status = 'R',
3059: move_order_line_id = NULL
3060: WHERE delivery_detail_id = p_delivery_detail_split_rec.delivery_detail_id
3061: RETURNING organization_id -- done for Workflow Project
3077: --Done Raise Event: Pick To Pod Workflow
3078:
3079: --
3080: -- DBI Project
3081: -- Update of wsh_delivery_details where released_status
3082: -- are changed, call DBI API after the update.
3083: -- This API will also check for DBI Installed or not
3084: IF l_debug_on THEN
3085: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_detail_split_rec.delivery_detail_id);
3320: IS
3321: CURSOR Get_Sl_Num_Ctrl_Cd IS
3322: SELECT msi.serial_number_control_code
3323: FROM mtl_system_items msi,
3324: wsh_delivery_details wdd
3325: WHERE msi.inventory_item_id = wdd.inventory_item_id
3326: AND msi.organization_id = wdd.organization_id
3327: AND wdd.delivery_detail_id = p_delivery_detail_id;
3328:
3386: RETURN;
3387: END IF;
3388: CLOSE Get_Sl_Num_Ctrl_Cd;
3389:
3390: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
3391: p_delivery_detail_id => p_delivery_detail_id,
3392: p_serial_number_code => l_serial_number_control_code,
3393: p_serial_number => p_serial_number,
3394: p_transaction_temp_id => p_transaction_temp_id,
3407: END IF;
3408: --
3409: RETURN;
3410: END IF;
3411: UPDATE wsh_delivery_details SET
3412: serial_number = NULL,
3413: to_serial_number = NULL,
3414: transaction_temp_id = NULL,
3415: shipped_quantity = 0
3495: mode_of_transport,
3496: carrier_id,
3497: service_level, -- End of Bug 4093619(FP-4145867)
3498: requested_quantity_uom2 --Bug# 5436033
3499: FROM wsh_delivery_details
3500: WHERE delivery_detail_id = p_Changed_Attributes(p_Counter).delivery_detail_id ;
3501:
3502: old_delivery_detail_rec C_Old_Detail%ROWTYPE;
3503:
3505:
3506: -- Added the record to call the WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters
3507: l_global_param_rec_type WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3508:
3509: -- Added tables to call the wsh_delivery_details_actions.Consolidate_Source_Lines
3510: l_Cons_Source_Line_Rec_Tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3511: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type;
3512:
3513: -- To store the shipping global parameter "Consolidate BO lines" value
3506: -- Added the record to call the WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters
3507: l_global_param_rec_type WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3508:
3509: -- Added tables to call the wsh_delivery_details_actions.Consolidate_Source_Lines
3510: l_Cons_Source_Line_Rec_Tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3511: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type;
3512:
3513: -- To store the shipping global parameter "Consolidate BO lines" value
3514: l_auto_consolidate VARCHAR2(1) := 'N';
3574: l_details_to_delete WSH_UTIL_CORE.Id_Tab_Type;
3575: l_delete_count NUMBER := 0;
3576:
3577: l_overpick_rec WSH_INTERFACE.ChangedAttributeRecType;
3578: l_source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE;
3579: l_new_req_quantity NUMBER;
3580: -- HW OPM added qty2
3581: l_new_req_quantity2 NUMBER;
3582: l_delivery_id NUMBER;
3594: -- bug 2805603 : added following variables for call to get_inv_pc_attributes and set_inv_pc_attributes
3595: l_InvPCOutRecType WSH_INTEGRATION.InvPCOutRecType;
3596: l_InvPCInRecType WSH_INTEGRATION.InvPCInRecType;
3597: l_transaction_id NUMBER;
3598: l_transaction_temp_id wsh_delivery_Details.transaction_temp_id%type;
3599: l_msg_count NUMBER;
3600: l_msg_data VARCHAR2(2000);
3601:
3602: l_user_id NUMBER;
3607: l_customer_id NUMBER;
3608: l_ship_to_location_id NUMBER;
3609: l_intmed_ship_to_location_id NUMBER;
3610: l_deliver_to_location_id NUMBER;
3611: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3608: l_ship_to_location_id NUMBER;
3609: l_intmed_ship_to_location_id NUMBER;
3610: l_deliver_to_location_id NUMBER;
3611: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3609: l_intmed_ship_to_location_id NUMBER;
3610: l_deliver_to_location_id NUMBER;
3611: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3610: l_deliver_to_location_id NUMBER;
3611: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3618: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
3611: l_fob_code WSH_DELIVERY_DETAILS.Fob_Code%TYPE;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3618: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
3619: l_carrier_id NUMBER;
3612: l_freight_terms_code WSH_DELIVERY_DETAILS.Freight_Terms_Code%TYPE;
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3618: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
3619: l_carrier_id NUMBER;
3620:
3613: l_ship_method_code WSH_DELIVERY_DETAILS.Ship_Method_Code%TYPE;
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3618: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
3619: l_carrier_id NUMBER;
3620:
3621: -- K LPN CONV. rvishnuv
3614: l_line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE;
3615: l_ignore_for_planning WSH_DELIVERY_DETAILS.ignore_for_planning%TYPE;
3616: l_shipping_control WSH_DELIVERY_DETAILS.shipping_control%TYPE;
3617: l_mode_of_transport WSH_DELIVERY_DETAILS.mode_of_transport%TYPE;
3618: l_service_level WSH_DELIVERY_DETAILS.service_level%TYPE;
3619: l_carrier_id NUMBER;
3620:
3621: -- K LPN CONV. rvishnuv
3622: l_cont_tab wsh_util_core.id_tab_type;
3671: Cursor c_get_picked_lines_count (c_delivery_detail_id NUMBER)
3672: IS
3673: SELECT count (wdd.delivery_detail_id), delivery_id
3674: FROM
3675: wsh_delivery_details wdd,
3676: wsh_delivery_assignments_v wda
3677: WHERE
3678: wdd.delivery_detail_id = wda.delivery_detail_id
3679: AND wda.delivery_id = ( SELECT delivery_id
3866: l_delivery_id,
3867: l_planned_flag,
3868: l_del_batch_id
3869: FROM wsh_new_deliveries wnd,
3870: wsh_delivery_details wdd,
3871: wsh_delivery_assignments_v wda
3872: WHERE wdd.delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id
3873: AND wda.delivery_id = wnd.delivery_id
3874: AND wda.delivery_detail_id = wdd.delivery_detail_id ;
4012: --
4013: l_changed_attributes(l_counter).action_flag := 'U';
4014: ELSE
4015: -- odaboval : Begin of OPM Changes (Pick_Confirm)
4016: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4017: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4018: p_req_quantity => l_split_quantity,
4019: x_new_detail_id => l_dummy_detail_id,
4020: x_return_status => l_return_status,
4068: /*
4069: old_delivery_detail_rec.picked_quantity2 := l_split_quantity2
4070: + p_changed_attributes(l_counter).pending_quantity2; */
4071:
4072: UPDATE WSH_DELIVERY_DETAILS
4073: SET picked_quantity = old_delivery_detail_rec.picked_quantity,
4074: picked_quantity2 = old_delivery_detail_rec.picked_quantity2,
4075: requested_quantity_uom2 = DECODE(l_wms_enabled_flag,
4076: 'Y',
4081: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4082:
4083: IF l_split_quantity < old_delivery_detail_rec.picked_quantity THEN
4084:
4085: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4086: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4087: p_req_quantity => l_split_quantity,
4088: x_new_detail_id => l_dummy_detail_id,
4089: x_return_status => l_return_status,
4104: END IF;
4105:
4106: -- If this delivery line has requested_quantity > 0, it is not pending overpick,
4107: -- and its picked quantity columns should be NULL.
4108: UPDATE wsh_delivery_details
4109: SET picked_quantity = decode(requested_quantity, 0, picked_quantity, NULL),
4110: picked_quantity2 = decode(requested_quantity2, 0, picked_quantity2, NULL)
4111: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4112:
4159: wsh_debug_sv.log(l_module_name, 'old_delivery_detail_rec.picked_quantity', old_delivery_detail_rec.picked_quantity);
4160: wsh_debug_sv.log(l_module_name, 'old_delivery_detail_rec.picked_quantity2', old_delivery_detail_rec.picked_quantity2);
4161: END IF;
4162:
4163: UPDATE WSH_DELIVERY_DETAILS
4164: SET picked_quantity = old_delivery_detail_rec.picked_quantity,
4165: picked_quantity2 = old_delivery_detail_rec.picked_quantity2
4166: WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
4167:
4189: --
4190: -- Debug Statements
4191: --
4192: IF l_debug_on THEN
4193: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
4194: END IF;
4195: --
4196: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4197: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4192: IF l_debug_on THEN
4193: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
4194: END IF;
4195: --
4196: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4197: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4198: p_req_quantity => l_split_quantity,
4199: x_new_detail_id => l_dummy_detail_id,
4200: x_return_status => l_return_status,
4219: IF p_changed_attributes(l_counter).picked_quantity <> FND_API.G_MISS_NUM THEN
4220: -- correct the requested quantities of the split lines.
4221: -- be sure to clear the picked quantities if line has requested_quantity > 0.
4222:
4223: UPDATE wsh_delivery_details
4224: SET requested_quantity = LEAST(old_delivery_detail_rec.requested_quantity,
4225: picked_quantity),
4226: requested_quantity2 = LEAST(old_delivery_detail_rec.requested_quantity2,
4227: picked_quantity2)
4229: RETURNING requested_quantity, requested_quantity2 INTO l_split_quantity, l_split_quantity2;
4230:
4231: --
4232: -- DBI Project
4233: -- Update of wsh_delivery_details where requested_quantity
4234: -- are changed, call DBI API after the update.
4235: -- DBI API checks for DBI Installed also
4236: IF l_debug_on THEN
4237: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',old_delivery_detail_rec.delivery_detail_id);
4261: --
4262:
4263: l_split_quantity := GREATEST(old_delivery_detail_rec.requested_quantity - l_split_quantity, 0);
4264: l_split_quantity2 := GREATEST(old_delivery_detail_rec.requested_quantity2 - l_split_quantity2, 0);
4265: UPDATE wsh_delivery_details SET
4266: requested_quantity = l_split_quantity,
4267: requested_quantity2 = l_split_quantity2,
4268: picked_quantity = decode(l_split_quantity, 0, picked_quantity, NULL),
4269: picked_quantity2 = decode(l_split_quantity2, 0, picked_quantity2, NULL)
4293:
4294: ELSE
4295:
4296: -- make sure the newly create detail will be released_status of 'S'
4297: UPDATE wsh_delivery_details
4298: SET released_status = 'S'
4299: WHERE delivery_detail_id = l_dummy_detail_id
4300: RETURNING organization_id
4301: INTO l_organization_id;
4346:
4347: END IF;
4348: --
4349: -- DBI Project
4350: -- Update of wsh_delivery_details where requested_quantity
4351: -- are changed, call DBI API after the update.
4352: -- DBI API checks for DBI Installed also
4353: IF l_debug_on THEN
4354: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_dummy_detail_id);
4399: IF l_debug_on THEN
4400: WSH_DEBUG_SV.logmsg(l_module_name, 'SPLIT_DELIVERY_DETAILS BEING CALLED WITH ' || TO_CHAR ( L_SPLIT_QUANTITY ) );
4401: END IF;
4402: --
4403: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
4404: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
4405: p_req_quantity => l_split_quantity,
4406: x_new_detail_id => l_dummy_detail_id,
4407: x_return_status => l_return_status,
4541: --
4542: IF g_delivery_detail_id IS NOT NULL THEN
4543: SELECT inventory_item_id
4544: INTO l_backordered_item_id
4545: FROM wsh_delivery_details
4546: WHERE delivery_detail_id = g_delivery_detail_id;
4547: ELSIF g_move_order_line_id IS NOT NULL THEN
4548: SELECT inventory_item_id
4549: INTO l_backordered_item_id
4546: WHERE delivery_detail_id = g_delivery_detail_id;
4547: ELSIF g_move_order_line_id IS NOT NULL THEN
4548: SELECT inventory_item_id
4549: INTO l_backordered_item_id
4550: FROM wsh_delivery_details
4551: WHERE move_order_line_id = g_move_order_line_id;
4552: END IF;
4553: IF g_ship_set_id IS NOT NULL THEN
4554: SELECT set_name
4644:
4645: IF g_delivery_detail_id IS NOT NULL THEN
4646: SELECT inventory_item_id
4647: INTO l_backordered_item_id
4648: FROM wsh_delivery_details
4649: WHERE delivery_detail_id = g_delivery_detail_id;
4650: ELSIF g_move_order_line_id IS NOT NULL THEN
4651: SELECT inventory_item_id
4652: INTO l_backordered_item_id
4649: WHERE delivery_detail_id = g_delivery_detail_id;
4650: ELSIF g_move_order_line_id IS NOT NULL THEN
4651: SELECT inventory_item_id
4652: INTO l_backordered_item_id
4653: FROM wsh_delivery_details
4654: WHERE move_order_line_id = g_move_order_line_id;
4655: END IF;
4656: IF g_ship_model_id IS NOT NULL THEN
4657: SELECT inventory_item_id
4950: --
4951: -- Debug Statements
4952: --
4953: IF l_debug_on THEN
4954: 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);
4955: END IF;
4956: --
4957: l_Cons_Source_Line_Rec_Tab(1).delivery_detail_id:= old_Delivery_detail_rec.delivery_detail_id ;
4958: l_Cons_Source_Line_Rec_Tab(1).delivery_id:= null;
4963: l_Cons_Source_Line_Rec_Tab(1).req_qty2:= old_Delivery_detail_rec.requested_quantity2 ;
4964: l_Cons_Source_Line_Rec_Tab(1).bo_qty2:= l_split_quantity2;
4965: -- end of 3121616
4966:
4967: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4968: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
4969: x_consolidate_ids => l_cons_dd_ids,
4970: x_return_status => l_return_status);
4971:
4997: IF l_debug_on THEN
4998: WSH_DEBUG_SV.logmsg(l_module_name, 'SPLIT_DELIVERY_DETAILS BEING CALLED WITH ' || TO_CHAR ( L_SPLIT_QUANTITY ) );
4999: END IF;
5000: --
5001: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
5002: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
5003: p_req_quantity => l_split_quantity,
5004: x_new_detail_id => l_dummy_detail_id,
5005: x_return_status => l_return_status,
5029: -- If it is complete back order case then try to consolidate it
5030: IF ( l_auto_consolidate = 'Y' AND l_dummy_detail_id = p_Changed_attributes(l_counter).delivery_detail_id) THEN
5031: --{
5032: IF l_debug_on THEN
5033: 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);
5034: END IF;
5035: l_Cons_Source_Line_Rec_Tab(1).delivery_detail_id:= old_Delivery_detail_rec.delivery_detail_id ;
5036: l_Cons_Source_Line_Rec_Tab(1).delivery_id:= null;
5037: l_Cons_Source_Line_Rec_Tab(1).source_line_id:= old_Delivery_detail_rec.source_line_id;
5041: l_Cons_Source_Line_Rec_Tab(1).req_qty2:= old_Delivery_detail_rec.requested_quantity2 ;
5042: l_Cons_Source_Line_Rec_Tab(1).bo_qty2:= old_Delivery_detail_rec.requested_quantity2;
5043: -- end of 3121616
5044:
5045: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
5046: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
5047: x_consolidate_ids => l_cons_dd_ids,
5048: x_return_status => l_return_status);
5049:
5135: AND parent_delivery_detail_id IS NOT NULL;
5136:
5137: SELECT container_name
5138: INTO l_container_name
5139: FROM wsh_delivery_details
5140: WHERE delivery_detail_id = l_parent_detail_id;
5141: EXCEPTION
5142: WHEN no_data_found THEN
5143: l_parent_detail_id := NULL;
5236: -- exists
5237:
5238: SELECT count(*)
5239: INTO l_num_containers
5240: FROM wsh_delivery_Details
5241: WHERE lpn_id = p_Changed_Attributes(l_Counter).transfer_lpn_id
5242: AND container_flag = 'Y'
5243: AND delivery_detail_id <> p_changed_attributes(l_Counter).delivery_detail_id
5244: AND nvl(line_direction , 'O') IN ('O', 'IO') -- J-IB-JCKWOK
5340: l_freight_terms_code, l_ship_method_code,
5341: l_line_direction, l_ignore_for_planning,
5342: l_shipping_control, l_carrier_id,
5343: l_service_level, l_mode_of_transport
5344: FROM wsh_Delivery_Details
5345: WHERE lpn_id = p_changed_attributes(l_Counter).transfer_lpn_id
5346: AND container_flag = 'Y'
5347: --LPN reuse project
5348: AND released_status = 'X'
5373: WSH_WMS_LPN_GRP.g_caller := 'WMS';
5374: WSH_WMS_LPN_GRP.g_callback_required := 'N';
5375: -- LPN CONV rv
5376: --
5377: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
5378: p_changed_attributes(l_Counter).delivery_detail_id,
5379: l_cont_instance_id,
5380: l_return_status);
5381: IF l_debug_on THEN
5391: IF l_debug_on THEN
5392: WSH_DEBUG_SV.logmsg(l_module_name,'Updating the master serial number of the container onto the line');
5393: END IF;
5394:
5395: UPDATE WSH_DELIVERY_DETAILS
5396: SET master_serial_number = (select master_serial_number
5397: from wsh_delivery_details
5398: where delivery_detail_id = l_cont_instance_id
5399: and container_flag = 'Y')
5393: END IF;
5394:
5395: UPDATE WSH_DELIVERY_DETAILS
5396: SET master_serial_number = (select master_serial_number
5397: from wsh_delivery_details
5398: where delivery_detail_id = l_cont_instance_id
5399: and container_flag = 'Y')
5400: WHERE delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id;
5401:
5712: source_code,
5713: organization_id, -- bug 7131800
5714: requested_quantity,
5715: requested_quantity2
5716: FROM wsh_delivery_details
5717: WHERE delivery_detail_id = x_delivery_detail_id;
5718:
5719: --HW OPM Rretrieve qty2 attributes
5720: CURSOR c_source_info(x_source_line_id IN NUMBER, x_source_code IN VARCHAR2) IS
5931: --
5932: -- Debug Statements
5933: --
5934: IF l_debug_on THEN
5935: WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WSH_DELIVERY_DETAILS' );
5936: END IF;
5937: --
5938:
5939: -- bug 2120494
6024: WSH_DEBUG_SV.log(l_module_name,'Back Order Qty2:',l_Cons_Source_Line_Rec_Tab(1).bo_qty2);
6025:
6026: END IF;
6027: --
6028: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
6029: p_Cons_Source_Line_Rec_Tab => l_Cons_Source_Line_Rec_Tab,
6030: x_consolidate_ids => l_cons_dd_ids,
6031: x_return_status => l_return_status);
6032:
6053:
6054: -- IF no consolidation then Split the line
6055: IF ( l_cons_flag = 'N' ) THEN
6056:
6057: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details(
6058: p_from_detail_id => p_Changed_attributes(l_counter).delivery_detail_id,
6059: p_req_quantity => l_split_quantity,
6060: x_new_detail_id => l_dummy_detail_id,
6061: x_return_status => l_return_status,
6130:
6131: -- bug 2120494
6132: -- HW OPM added OPM attributes qty2
6133: -- HW OPM BUG#:3121616 added shipped_qty2 and cycle_count_quantity2
6134: UPDATE wsh_delivery_details
6135: SET subinventory = decode(l_update_sub,'Y',p_Changed_Attributes(l_Counter).subinventory,subinventory),
6136: locator_id = decode(l_update_loc, 'Y',p_Changed_Attributes(l_Counter).locator_id,locator_id),
6137: revision = decode(l_update_rev ,'Y',p_Changed_Attributes(l_Counter).revision,revision),
6138: -- HW OPMCONV - No need for sublot_number
6196:
6197: --
6198: --
6199: -- DBI Project
6200: -- Update of wsh_delivery_details where requested_quantity
6201: -- are changed, call DBI API after the update.
6202: -- DBI API checks for DBI Installed also
6203: IF l_debug_on THEN
6204: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_dummy_detail_id);
6228: --
6229: -- bug 3632485 When transacted quantity is less than allocated quantity, null out tracking number for backordered delivery detail.
6230: if (old_Delivery_detail_rec.tracking_number is not null
6231: and p_Changed_attributes(l_counter).delivery_detail_id <>l_dummy_detail_id) then
6232: update wsh_delivery_details
6233: set tracking_number = NULL
6234: where delivery_detail_id = p_Changed_attributes(l_counter).delivery_detail_id;
6235: end if;
6236:
6603: -- Procedure : get_putaway_detail_id
6604: -- Description : Procedure to return delivery_detail_id based on move_order_type
6605: -- associated with Move Order Line id.For scenarios where released_status
6606: -- and move_order_line_id are not passed to this API, first
6607: -- derive those from wsh_delivery_details and then proceed.
6608: --
6609: PROCEDURE get_putaway_detail_id(
6610: p_detail_id IN NUMBER,
6611: p_released_status IN VARCHAR2,
6615:
6616: CURSOR c_get_line_details IS
6617: SELECT released_status,
6618: move_order_line_id
6619: FROM wsh_delivery_details
6620: WHERE delivery_detail_id = p_detail_id;
6621:
6622: l_line_details c_get_line_details%ROWTYPE;
6623: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
6619: FROM wsh_delivery_details
6620: WHERE delivery_detail_id = p_detail_id;
6621:
6622: l_line_details c_get_line_details%ROWTYPE;
6623: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
6624: l_move_order_line_id WSH_DELIVERY_DETAILS.MOVE_ORDER_LINE_ID%TYPE;
6625:
6626: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'get_putaway_detail_id';
6627: l_debug_on BOOLEAN;
6620: WHERE delivery_detail_id = p_detail_id;
6621:
6622: l_line_details c_get_line_details%ROWTYPE;
6623: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
6624: l_move_order_line_id WSH_DELIVERY_DETAILS.MOVE_ORDER_LINE_ID%TYPE;
6625:
6626: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'get_putaway_detail_id';
6627: l_debug_on BOOLEAN;
6628:
6664: x_detail_id := NULL;
6665:
6666: --Check only for line with released_status = 'S' and MOL is not null and MO type = PUTAWAY
6667: IF p_detail_id IS NOT NULL AND
6668: l_released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE AND
6669: l_move_order_line_id IS NOT NULL AND
6670: wsh_usa_inv_pvt.is_mo_type_putaway (l_move_order_line_id) = 'Y' THEN
6671: x_detail_id := p_detail_id;
6672: END IF;