[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) into l_count
from wsh_delivery_details
where ship_set_id is not null
and source_line_id = p_source_line_id
and NVL(line_direction, 'O') IN ('O', 'IO') -- J Inbound Logistics Changes jckwok
and NVL(container_flag,'N') = 'N';
select
RES.RESERVATION_ID RESERV_ID,
decode(RES.SHIP_READY_FLAG,1,'1=Released',2,'2=Submitted',to_char(RES.SHIP_READY_FLAG))
SHIP_READY,
RES.DEMAND_SOURCE_HEADER_ID DS_HEADER_ID,
RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID,
RES.DEMAND_SOURCE_DELIVERY DS_DELIVERY,
to_char(LIN.line_number)||
'.'||to_char(LIN.shipment_number) ||
decode(LIN.option_number,NULL,NULL,'.'||to_char(LIN.option_number)) LINE,
RES.INVENTORY_ITEM_ID ITEM_ID,
RES.PRIMARY_RESERVATION_QUANTITY RES_QTY,
RES.DETAILED_QUANTITY DET_QTY,
RES.REQUIREMENT_DATE REQUIRD_D,
RES.DEMAND_SOURCE_TYPE_ID DS_TYPE,
RES.ORGANIZATION_ID ORG_ID,
RES.SUBINVENTORY_CODE SUBINV,
RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID,
RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET,
RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE,
RES.AUTODETAIL_GROUP_ID AUTODET_GRP_ID,
RES.AUTO_DETAILED AUTODET
from
MTL_RESERVATIONS RES,
OE_ORDER_LINES_ALL LIN --R12:MOAC use base table
where
RES.DEMAND_SOURCE_HEADER_ID = c_sales_order_id
and RES.DEMAND_SOURCE_TYPE_ID in (2,8,9,21,22)
and RES.DEMAND_SOURCE_LINE_ID = LIN.LINE_ID(+)
order by
NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SORT_ORDER, '0000'),
NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
LIN.LINE_ID,
RES.RESERVATION_ID;
SELECT shipped_quantity
,shipped_quantity2 -- OPM KYH 12/SEP/00
, dd.source_line_id
, dl.status_code
FROM wsh_delivery_Details dd,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id AND
st.stop_id = p_stop_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id
and nvl ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
and nvl ( dd.inv_interfaced_flag, 'N' ) IN ( 'Y','X')
and dd.source_line_id = p_source_line_id
and dd.source_code = 'OE'
and dd.container_flag = 'N' ;
SELECT source_line_id,
source_code,
picked_quantity,
delivery_detail_id,
released_status,
pickable_flag,
organization_id,
inventory_item_id,
requested_quantity,
-- muom
picked_quantity2,
requested_quantity2,
serial_number,
transaction_temp_id,
subinventory,
client_id -- LSP PROJECT : Required to check whether order is for LSP
FROM wsh_delivery_details
WHERE delivery_detail_id = x_detail_id AND
NVL(container_flag, 'N') = 'N';
SELECT decode ( delivery_id , null , 'N' , 'Y' ),
decode( delivery_id,null,-9999999,delivery_id)
FROM wsh_delivery_assignments_v
WHERE delivery_Detail_id = c_delivery_detail_id ; */
SELECT wnd.delivery_id,
wnd.planned_flag,
wdd.source_code,
wdd.source_line_id
FROM wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = c_delivery_detail_id
AND wda.delivery_id = wnd.delivery_id (+)
AND wda.delivery_detail_id = wdd.delivery_detail_id ;
l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete overpicked delivery lines
SELECT organization_id from wsh_delivery_details
WHERE delivery_detail_id = l_delivery_detail_id
AND NVL(container_flag, 'N') = 'N';
SELECT oelines.preferred_grade
FROM oe_order_lines_all oelines, wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wdd_id
AND wdd.source_code = 'OE'
AND wdd.source_line_id = oelines.line_id;
l_line_ids.DELETE;
l_detail_ids.DELETE;
l_req_qtys.DELETE;
l_bo_qtys.DELETE;
l_req_qty2s.DELETE;
l_bo_qty2s.DELETE;
UPDATE wsh_delivery_details
SET picked_quantity = picked_quantity - l_overpick_qtys(i),
picked_quantity2 = picked_quantity2 - l_overpick_qty2s(i) -- muom
WHERE l_detail_ids(i) = delivery_detail_id
-- muom
RETURNING picked_quantity, picked_quantity2 INTO l_new_picked_quantity, l_new_picked_quantity2;
--bug 7166138 new picked quantity should be updated in l_detail_rec
l_detail_rec.picked_quantity := l_new_picked_quantity;
l_delete_dds( l_delete_dds.count+1 ) := l_bo_detail_id;
l_delete_dds( l_delete_dds.count+1 ) := l_detail_ids(i);
UPDATE wsh_delivery_details
SET transaction_temp_id = NULL,
serial_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
UPDATE wsh_delivery_details
SET move_order_line_id = NULL ,
-- 2807093: For ATO items, it is possible that during Shp.Confirm CTO would have updated the rel.Status to N
-- so checking to see if it is 'N' then it has to remain 'N' otherwise 'B'
released_status = decode(pickable_flag,'Y', decode(released_status, 'N', released_status,'B'),'X'),
cycle_count_quantity = NULL,
cycle_count_quantity2 = NULL,
shipped_quantity = NULL,
shipped_quantity2 = NULL,
picked_quantity = NULL,
picked_quantity2 = NULL,
-- ship_set_id = NULL , code removed per bug 2008156
-- Bug 2444564 : Backordered/Cycle Count lines should be reset to Original Subinventory
subinventory = original_subinventory,
inv_interfaced_flag = decode(pickable_flag, 'Y', nvl(inv_interfaced_flag,'N'), 'X'),
--Standalone project Changes
locator_id = decode(l_standalone_mode, 'D', original_locator_id, NULL),
-- OPM Bug 3561937 replaced NULL with l_oeline_pref_grade
preferred_grade = l_oeline_pref_grade,
-- HW OPMCONV. No need for sublot anymore
-- sublot_number=NULL,
--Standalone project Changes Starts
lot_number = decode(l_standalone_mode, 'D', original_lot_number, NULL) , -- Bug 1705057
revision = decode(l_standalone_mode, 'D', original_revision, NULL) ,
--Standalone project Changes Ends
batch_id = null , -- Bug 2711490
-- tracking_number = NULL, Bug# 3632485
transaction_id = NULL, --- 2803570
replenishment_status = NULL -- bug# 6908504 (replenishment project), update replenishment status to NULL.
WHERE delivery_detail_id = l_detail_rec.delivery_detail_id AND
NVL(container_flag, 'N') = 'N' ;
UPDATE wsh_delivery_details
SET move_order_line_id = NULL ,
released_status ='X',
cycle_count_quantity = NULL,
cycle_count_quantity2 = NULL,
shipped_quantity = NULL,
shipped_quantity2 = NULL,
picked_quantity = NULL,
picked_quantity2 = NULL,
subinventory = original_subinventory,
inv_interfaced_flag = decode(source_code,'RTV','N','X'),
locator_id = original_locator_id,
preferred_grade = l_oeline_pref_grade,
lot_number = original_lot_number,
revision = original_revision,
batch_id = null ,
transaction_id = NULL,
replenishment_status = NULL
WHERE delivery_detail_id = l_detail_rec.delivery_detail_id AND
NVL(container_flag, 'N') = 'N' ;
UPDATE WSH_DELIVERY_DETAILS
SET requested_quantity = requested_quantity + l_remain_bo_qtys(i),
requested_quantity2 = requested_quantity2 + l_remain_bo_qty2s(i)
WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'Y',
x_net_weight => l_split_weight,
x_volume => l_split_volume,
x_return_status => l_return_status);
l_unassign_dds.DELETE(l_unassign_dds.COUNT);
l_cons_source_line_rec_tab.delete;
l_cons_dd_ids.delete;
l_unassign_dds.delete(l_unassign_dds.count);
IF l_delete_dds.count > 0 THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.DELETE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_INTERFACE.Delete_Details(
p_details_id => l_delete_dds,
x_return_status => l_return_status
);
END IF; -- l_delete_dds.count > 0
SELECT decode(severity,'HIGH','H','MEDIUM','M','L') severity
FROM wsh_exceptions
WHERE delivery_detail_id = p_delivery_detail_id
AND status not in (l_not_handled , l_no_action_reqd , l_closed)
ORDER BY decode(severity,'H',1,'M',2,3);