[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dd.delivery_detail_id
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE ((NVL(da.delivery_id, -1) <> p_delivery_id) OR
(da.delivery_id = p_delivery_id AND dd.requested_quantity > dd.shipped_quantity
AND da.delivery_id IS NOT NULL AND dd.shipped_quantity <> 0)) AND
da.delivery_detail_id = dd.delivery_detail_id AND
NVL(dd.released_status, 'C') NOT IN ('C' ,'D') AND -- when the other lines are not shipped
dd.container_flag <> 'Y' AND
dd.source_code = 'OE' AND
(dd.ship_set_id, dd.source_header_id) IN
(SELECT DISTINCT dd.ship_set_id, dd.source_header_id
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id AND
dd.container_flag <> 'Y' AND
dd.source_code = 'OE' AND
dd.ship_set_id IS NOT NULL );
SELECT DISTINCT dd.source_code, dd.ship_set_id, dd.source_header_id
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id AND
dd.container_flag <> 'Y' AND
dd.source_code = 'OE' AND
dd.ship_set_id IS NOT NULL ;
SELECT MAX(OEL.ORDERED_QUANTITY) child_ord_qty, SUM(NVL(WDD.SHIPPED_QUANTITY,0)) child_shp_qty,
OEL.LINE_ID
FROM WSH_DELIVERY_DETAILS WDD, OE_ORDER_LINES_ALL OEL, wsh_delivery_assignments_v WDA
WHERE OEL.LINE_ID = WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.SHIP_MODEL_COMPLETE_FLAG = 'Y'
AND WDD.SOURCE_CODE = 'OE'
AND WDD.CONTAINER_FLAG <> 'Y'
AND WDA.DELIVERY_ID IS NOT NULL
AND WDA.DELIVERY_ID = p_delivery_id
AND WDD.TOP_MODEL_LINE_ID = top_model_line
GROUP BY OEL.LINE_ID;
SELECT DISTINCT dd.top_model_line_id, dd.source_header_id , oe.ordered_quantity
FROM wsh_delivery_details dd, wsh_delivery_assignments_v da, oe_order_lines_all oe
WHERE da.delivery_id = p_delivery_id
AND da.delivery_id IS NOT NULL
AND dd.ship_model_complete_flag = 'Y'
AND da.delivery_detail_id = dd.delivery_detail_id
AND dd.source_code = 'OE'
AND dd.container_flag <> 'Y'
AND dd.top_model_line_id IS NOT NULL
AND dd.top_model_line_id = oe.line_id ;
SELECT line_id
FROM oe_order_lines_all
WHERE top_model_line_id = top_model_line
AND NVL(shippable_flag,'N') = 'Y'
AND NVL(cancelled_flag,'N') = 'N' -- bug 4997888
AND ( ato_line_id IS NULL OR
( ato_line_id IS NOT NULL AND item_type_code = 'CONFIG' )
)
MINUS
SELECT source_line_id
FROM wsh_delivery_details dd, wsh_delivery_assignments_v da
WHERE dd.source_header_id = header_id
AND dd.source_code = 'OE'
AND dd.container_flag <> 'Y'
AND da.delivery_id IS NOT NULL
AND dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = p_delivery_id;
SELECT inventory_item_id
FROM oe_order_lines_all
WHERE ato_line_id = ato_id
AND NVL(shippable_flag,'N') = 'Y'
AND item_type_code = 'CONFIG';
SELECT ato_line_id
FROM oe_order_lines_all
WHERE top_model_line_id = top_model_line
AND ato_line_id = line_id
AND item_type_code NOT IN ('STANDARD','OPTION','INCLUDED');--Bug10166876
SELECT 1 from dual
WHERE exists (
SELECT 1
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE ((NVL(da.delivery_id, -1) <> p_delivery_id) OR
(da.delivery_id = p_delivery_id AND dd.requested_quantity > dd.shipped_quantity
AND da.delivery_id IS NOT NULL AND dd.shipped_quantity <> 0))
AND da.delivery_detail_id = dd.delivery_detail_id
AND dd.container_flag <> 'Y'
AND dd.source_code = 'OE'
AND (dd.arrival_set_id, dd.source_header_id) IN
(SELECT DISTINCT dd.arrival_set_id, dd.source_header_id
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE da.delivery_id = p_delivery_id
AND da.delivery_id IS NOT NULL
AND da.delivery_detail_id = dd.delivery_detail_id
AND dd.container_flag <> 'Y'
AND dd.source_code = 'OE'
AND dd.arrival_set_id IS NOT NULL ) );
p_update_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_msg_count NUMBER;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
IF (p_update_flag <> 'Y') THEN
FND_MESSAGE.SET_NAME('WSH','WSH_CAL_SHIP_DATE_ALT');
FND_MESSAGE.SET_NAME('WSH','WSH_CAL_SHIP_DATE_ALT_UPDATE');
IF (p_update_flag <> 'Y') THEN
FND_MESSAGE.SET_NAME('WSH','WSH_REC_DATE_ALT');
FND_MESSAGE.SET_NAME('WSH','WSH_REC_DATE_ALT_UPDATE');
IF (p_update_flag <> 'Y') THEN
FND_MESSAGE.SET_NAME('WSH','WSH_CAL_REC_DATE_ALT');
FND_MESSAGE.SET_NAME('WSH','WSH_CAL_REC_DATE_ALT_UPDATE');
IF (p_update_flag = 'Y') THEN
IF (l_suggest_ship_date <> p_ship_date) THEN
UPDATE wsh_new_deliveries
SET initial_pickup_date = l_suggest_ship_date
WHERE delivery_id = p_entity_id;
UPDATE wsh_new_deliveries
SET ultimate_dropoff_date = l_suggest_receive_date
WHERE delivery_id = p_entity_id;
SELECT status_code,
planned_flag,
organization_id,
ship_method_code,
initial_pickup_date,
ultimate_dropoff_date,
initial_pickup_location_id,
ultimate_dropoff_location_id,
nvl(shipment_direction,'O') shipment_direction,
delivery_type
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments_v da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id AND
da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
nvl(dd.container_flag,'N') <> 'Y';
SELECT freight_code
FROM wsh_carriers wc,wsh_carrier_services wcs,wsh_org_carrier_services wocs
WHERE wc.carrier_id=wcs.carrier_id AND
wcs.carrier_service_id=wocs.carrier_service_id AND
wcs.ship_method_code = p_ship_method_code AND
wocs.organization_id = p_organization_id;
SELECT freight_code
FROM wsh_carrier_ship_methods_v
WHERE ship_method_code = p_ship_method_code AND
organization_id = p_organization_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.UPDATE_LEG_SEQUENCE',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_new_delivery_actions.update_leg_sequence(
p_delivery_id => p_delivery_id,
x_return_status => l_return_status);
p_update_flag => 'Y',
x_return_status => l_return_status);
SELECT status_code,
planned_flag,
nvl(shipment_direction,'O') shipment_direction -- J-IB-NPARIKH
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT dl.delivery_id,
dg.gross_weight,
dg.net_weight,
dg.weight_uom_code,
dg.volume,
dg.volume_uom_code
FROM wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE dl.delivery_id = dg.delivery_id AND
dg.delivery_leg_id = p_delivery_leg_id;
SELECT status_code,
organization_id,
ship_method_code,
nvl(shipment_direction,'O') shipment_direction, -- J-IB-NPARIKH
nvl(ignore_for_planning, 'N') ignore_for_planning
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT status_code,ship_method_code, planned_flag,
nvl(ignore_for_planning, 'N') ignore_for_planning
FROM wsh_trips
WHERE trip_id = p_trip_id;
SELECT ship_method_code
FROM wsh_carrier_ship_methods
WHERE organization_id=l_organization_id
AND ship_method_code=l_ship_method_code;
SELECT ship_method_code
FROM wsh_carrier_services wcs, wsh_org_carrier_services wocs
WHERE wocs.organization_id=l_organization_id
and wocs.carrier_service_id=wcs.carrier_service_id
AND wcs.ship_method_code=l_ship_method_code;
SELECT status_code,
planned_arrival_date,
planned_departure_date
FROM wsh_trip_Stops
WHERE stop_id = l_stop_id AND
status_code <> 'OP';
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT status_code
FROM wsh_trips
WHERE trip_id = p_trip_id;
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT da.parent_delivery_detail_id
FROM wsh_delivery_assignments_v da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id AND
da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
dd.container_flag = 'N';
SELECT delivery_detail_id,
inventory_item_id,
shipped_quantity,
requested_quantity,
NULL,
revision,
subinventory,
lot_number,
locator_id,
NULL,
serial_number,
NULL,
transaction_temp_id,
organization_id,
source_line_id,
source_header_id,
source_code,
picked_quantity,
picked_quantity2,
requested_quantity_uom,
requested_quantity_uom2,
line_direction
FROM wsh_delivery_details
WHERE delivery_detail_id = p_detail_id;
SELECT fm_serial_number,nvl(to_serial_number,fm_serial_number)
from MTL_SERIAL_NUMBERS_TEMP
where transaction_temp_id = l_transaction_temp_id;
SELECT wdd1.lpn_id from wsh_delivery_details wdd1,
wsh_delivery_details wdd2, wsh_delivery_assignments wda
WHERE wdd2.delivery_detail_id = p_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
AND wda.parent_delivery_detail_id IS NOT NULL
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
AND wdd1.container_flag = 'Y'
AND wdd1.lpn_id is not null;
select DECODE(wsh_util_validate.Check_Wms_Org(l_line_inv_rec.organization_id),'Y','TRUE','FALSE')
into l_wms_enabled
from dual;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.UPDATE_LEG_SEQUENCE',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_new_delivery_actions.update_leg_sequence(
p_delivery_id => p_delivery_id,
x_return_status => l_return_status);
p_update_flag => 'N',
x_return_status => l_return_status);
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT st.status_code
FROM wsh_trip_stops st,
wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE dl.delivery_id = p_delivery_id AND
dg.delivery_id = dl.delivery_id AND
st.stop_id = dg.pick_up_stop_id AND
dl.initial_pickup_location_id = st.stop_location_id;
SELECT dg.drop_off_stop_id,
st.stop_location_id
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st
WHERE dl.delivery_id = l_delivery_id AND
dl.delivery_id = dg.delivery_id AND
dg.drop_off_stop_id = st.stop_id AND
dl.ultimate_dropoff_location_id = st.stop_location_id;
SELECT dg.pod_flag
FROM wsh_delivery_legs dg,
wsh_document_instances di
WHERE dg.drop_off_stop_id = l_stop_id AND
di.entity_id = dg.delivery_leg_id AND
di.entity_name = 'WSH_DELIVERY_LEGS' AND
di.document_type = 'BOL' AND
dg.pod_flag = 'Y' AND
dg.pod_date IS NULL AND
di.status IN ('OPEN','PLANNED');
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = l_delivery_id
AND rownum = 1;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = l_delivery_id;
SELECT dg.pick_up_stop_id,
pu_stop.status_code pu_status,
dg.drop_off_stop_id,
do_stop.status_code do_status
FROM wsh_delivery_legs dg,
wsh_trip_stops pu_stop,
wsh_trip_stops do_stop
WHERE dg.delivery_id = l_delivery_id
AND pu_stop.stop_id = dg.pick_up_stop_id
AND do_stop.stop_id = dg.drop_off_stop_id;
SELECT dg.delivery_leg_id
FROM wsh_delivery_legs dg, wsh_new_deliveries nd
WHERE dg.pick_up_stop_id = l_stop_id
AND dg.delivery_id <> l_delivery_id
AND dg.delivery_id = nd.delivery_id
AND nd.delivery_type = 'STANDARD'
UNION
SELECT dg.delivery_leg_id
FROM wsh_delivery_legs dg, wsh_new_deliveries nd
WHERE dg.drop_off_stop_id = l_stop_id
AND dg.delivery_id <> l_delivery_id
AND dg.delivery_id = nd.delivery_id
AND nd.delivery_type = 'STANDARD';
SELECT ts.stop_id
FROM wsh_trip_stops ts,
wsh_delivery_legs dg
WHERE dg.delivery_id = l_delivery_id
AND ts.stop_id = dg.drop_off_stop_id
AND ts.status_code in ('OP', 'AR')
AND rownum = 1;
SELECT 1
FROM wsh_trip_stops ts,
wsh_delivery_legs dg
WHERE dg.delivery_id = p_delivery_id
AND ts.stop_id = dg.drop_off_stop_id
AND ts.stop_id <> p_stop_id
AND ts.status_code in ('OP', 'AR')
AND rownum = 1;
SELECT 1
FROM wsh_delivery_legs dg,
wsh_document_instances di
WHERE dg.drop_off_stop_id = p_stop_id
AND di.entity_id = dg.delivery_leg_id
AND di.entity_name = 'WSH_DELIVERY_LEGS'
AND di.document_type = 'BOL'
AND dg.pod_flag = 'Y'
AND dg.pod_date IS NULL
AND di.status IN ('OPEN','PLANNED');
SELECT 1
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_delivery_id
AND rownum = 1;
SELECT 1
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id
AND rownum = 1;
SELECT 1
FROM wsh_delivery_legs wdl,
wsh_Trip_stops wts
WHERE wdl.delivery_id = p_delivery_id
AND (
wdl.pick_up_stop_id = wts.stop_id
OR wdl.drop_off_stop_id = wts.stop_id
)
AND wts.status_code <> 'CL'
AND EXISTS
(
SELECT 1
FROM wsh_delivery_legs wdl1, wsh_new_deliveries wnd
WHERE wdl1.delivery_id <> p_delivery_id
AND wdl1.delivery_id = wnd.delivery_id
AND wnd.delivery_type = 'STANDARD'
AND (
wdl1.pick_up_stop_id = wts.stop_id
OR wdl1.drop_off_stop_id = wts.stop_id
)
)
AND rownum = 1;
l_exceptions_tab.delete;
PROCEDURE Check_Delete_Delivery ( p_delivery_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR delivery_status IS
SELECT status_code, routing_response_id, --J-IB-NPARIKH
delivery_type,
tms_interface_flag --OTM R12
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
SELECT freight_cost_id
FROM wsh_freight_costs
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
cannot_delete_GC3_delivery EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_DELETE_DELIVERY';
RAISE cannot_delete_gc3_delivery;
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_ERROR');
-- Routing response is sent for delivery, cannot be deleted.
--
IF l_routingResponseId IS NOT NULL
THEN
FND_MESSAGE.SET_NAME('WSH','WSH_IB_DELETE_DELIVERY');
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_WITH_LEGS');
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_WITH_FC');
WHEN cannot_delete_gc3_delivery THEN
IF (delivery_status%ISOPEN) THEN
CLOSE delivery_status;
FND_MESSAGE.SET_NAME('WSH','WSH_OTM_DEL_DELETE_ERROR');
WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_DELETE_GC3_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_DELETE_GC3_DELIVERY');
wsh_util_core.default_handler('WSH_DELIVERY_VALIDATIONS.CHECK_DELETE_DELIVERY');
END Check_Delete_Delivery;
SELECT status_code,
organization_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT count(*)
FROM wsh_delivery_assignments_v da,
wsh_delivery_details dd
WHERE da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id AND
dd.ship_method_code IS NULL AND
nvl(dd.container_flag,'N') <> 'Y';
SELECT status_code, planned_flag, organization_id,
nvl(shipment_direction,'O') shipment_direction -- J-IB-NPARIKH
, INITIAL_PICKUP_LOCATION_ID, --3509004:public api changes
delivery_type -- MDC
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_delivery_id
AND ROWNUM=1;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id
AND ROWNUM=1;
x_disabled_list.delete;
SELECT oel.line_id
FROM oe_order_lines_all oel,
mtl_system_items msi
WHERE oel.header_id = p_source_header_id
AND oel.ship_set_id = p_ship_set_id
AND oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and ((p_check_transactable = 'N') or (p_check_transactable = 'Y' AND msi.mtl_transactions_enabled_flag = 'Y'))
AND oel.shipping_interfaced_flag = 'N'
AND (oel.shippable_flag = 'Y' or
(EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel1
WHERE oel1.header_id = p_source_header_id
AND oel1.ship_set_id = p_ship_set_id
AND oel1.ato_line_id = oel1.line_id
AND oel1.item_type_code in ('MODEL','CLASS')
AND NOT EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel2
WHERE oel2.top_model_line_id = oel1.top_model_line_id
AND oel2.ato_line_id = oel1.ato_line_id
AND oel2.item_type_code = 'CONFIG')
)
)
);
select distinct
dd.source_line_id,
dd.source_code,
dd.source_line_number,
dd.source_header_id,
dd.source_header_number,
dd.source_header_type_name,
dd.organization_id,
dd.source_line_set_id, -- Bug 2181132
dd.ship_tolerance_above, -- Bug 2181132
dd.ship_tolerance_below, -- Bug 2181132
dd.requested_quantity_uom, -- Bug 2181132
dd.requested_quantity_uom2 -- Bug 2181132
from wsh_delivery_assignments_v da,
wsh_Delivery_details dd
where dd.delivery_detail_id = da.delivery_detail_id
and da.delivery_id = v_delivery_id
and dd.container_flag = 'N'
and dd.released_status <> 'D'
and da.delivery_id is not null; /* added for performance */
select sum( nvl(requested_quantity,0)) requested_quantity,
sum( nvl(requested_quantity2,0)) requested_quantity2,
ship_tolerance_above,
ship_tolerance_below
from wsh_delivery_details
where source_code = v_source_code and
source_line_id = v_source_line_id and
container_flag = 'N'
group by ship_tolerance_above,
ship_tolerance_below;
select sum( greatest ( nvl(shipped_quantity , 0) ,
nvl(picked_quantity , requested_quantity )
)
) expected_quantity ,
sum( greatest ( nvl(shipped_quantity2 , 0) ,
nvl(picked_quantity2 , requested_quantity2 )
)
) expected_quantity2 ,
src_requested_quantity_uom ,
requested_quantity_uom ,
src_requested_quantity_uom2 ,
requested_quantity_uom2 ,
inventory_item_id
from wsh_delivery_details
where source_line_id = v_source_line_id
and released_status <> 'D'
and source_code = v_source_code
and container_flag = 'N'
--Bug 3622571
and source_header_id = v_source_header_id
group by
src_requested_quantity_uom ,
requested_quantity_uom ,
src_requested_quantity_uom2 ,
requested_quantity_uom2 ,
inventory_item_id;
select sum( greatest ( nvl(wdd.shipped_quantity , 0) ,
nvl(wdd.picked_quantity , wdd.requested_quantity )
)
) expected_quantity ,
sum( greatest ( nvl(wdd.shipped_quantity2 , 0) ,
nvl(wdd.picked_quantity2 , wdd.requested_quantity2 )
)
) expected_quantity2 ,
wdd.src_requested_quantity_uom ,
wdd.requested_quantity_uom ,
wdd.src_requested_quantity_uom2 ,
wdd.requested_quantity_uom2 ,
wdd.inventory_item_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wdd.source_line_id = v_source_line_id
and wdd.released_status <> ('D')
and wdd.source_code = v_source_code
and wdd.delivery_detail_id = wda.delivery_detail_id
and (wda.delivery_id IS NULL
OR wda.delivery_id <> p_delivery_id)
and wdd.container_flag = 'N'
--Bug 3622571
and wdd.source_header_id = v_source_header_id
group by
wdd.src_requested_quantity_uom ,
wdd.requested_quantity_uom ,
wdd.src_requested_quantity_uom2 ,
wdd.requested_quantity_uom2 ,
wdd.inventory_item_id;
SELECT meaning
INTO l_source_system
FROM wsh_lookups
WHERE lookup_type = 'SOURCE_SYSTEM'
AND lookup_code = dl.source_code;
select delivery_id
FROM wsh_delivery_legs
WHERE delivery_id = c_delivery_id
AND rownum = 1;
select distinct 'X'
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wda.delivery_id = p_delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.source_code = 'WSH'
and wdd.container_flag = 'N';
select 'X'
from wsh_transactions_history
where transaction_id = (
select max(transaction_id)
from wsh_transactions_history wth,
wsh_new_deliveries wnd
where wth.entity_number = wnd.name
and wth.trading_partner_id = p_tp_id
and wnd.delivery_id = p_delivery_id
)
and document_direction='I'
and action_type = 'A';
select distinct 'X'
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wdd.source_code = 'WSH'
and wdd.container_flag = 'N'
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = p_delivery_id;
SELECT 'Y'
FROM wsh_trips wt
WHERE wt.trip_id=p_tripid
AND wt.planned_flag='F';
SELECT 'Y'
FROM wsh_trips wt
WHERE wt.trip_id=p_tripid
AND (nvl(wt.ignore_for_planning,'N') <> (select nvl(ignore_for_planning,'N') from wsh_new_deliveries where delivery_id=p_delid)
);
SELECT delivery_scpod_wf_process,
del_wf_close_attr
FROM WSH_NEW_DELIVERIES
WHERE delivery_id = p_delivery_id;
SELECT delivery_type
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
select 1
from wsh_delivery_legs pleg,
wsh_delivery_legs cleg
where pleg.delivery_leg_id = cleg.parent_delivery_leg_id
and cleg.delivery_id = p_delivery_id;
select 'X'
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wda.delivery_id = p_delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.source_code NOT IN ('OE','WSH','OKE')
and wdd.container_flag = 'N';
IF (p_action NOT IN ('PRINT-BOL', 'GENERATE-BOL', 'WT-VOL', 'UNASSIGN', 'SELECT-CARRIER'))
OR (p_action = 'UNASSIGN' AND p_caller NOT like 'WMS%') THEN --{
OPEN cur_get_delivery_type(p_dlvy_rec_tab(j).delivery_id);
'SELECT-CARRIER', 'GET-FREIGHT-COSTS', 'CANCEL-SHIP-METHOD',
'IGNORE_PLAN') THEN
OPEN cur_check_consol_delivery(p_dlvy_rec_tab(j).delivery_id);
SELECT DELIVERY_ID
,NAME
,PLANNED_FLAG
,STATUS_CODE
,DELIVERY_TYPE
,LOADING_SEQUENCE
,LOADING_ORDER_FLAG
,INITIAL_PICKUP_DATE
,INITIAL_PICKUP_LOCATION_ID
,ORGANIZATION_ID
,ULTIMATE_DROPOFF_LOCATION_ID
,ULTIMATE_DROPOFF_DATE
,CUSTOMER_ID
,INTMED_SHIP_TO_LOCATION_ID
,POOLED_SHIP_TO_LOCATION_ID
,CARRIER_ID
,SHIP_METHOD_CODE
,FREIGHT_TERMS_CODE
,FOB_CODE
,FOB_LOCATION_ID
,WAYBILL
,DOCK_CODE
,ACCEPTANCE_FLAG
,ACCEPTED_BY
,ACCEPTED_DATE
,ACKNOWLEDGED_BY
,CONFIRMED_BY
,CONFIRM_DATE
,ASN_DATE_SENT
,ASN_STATUS_CODE
,ASN_SEQ_NUMBER
,GROSS_WEIGHT
,NET_WEIGHT
,WEIGHT_UOM_CODE
,VOLUME
,VOLUME_UOM_CODE
,ADDITIONAL_SHIPMENT_INFO
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TP_ATTRIBUTE_CATEGORY
,TP_ATTRIBUTE1
,TP_ATTRIBUTE2
,TP_ATTRIBUTE3
,TP_ATTRIBUTE4
,TP_ATTRIBUTE5
,TP_ATTRIBUTE6
,TP_ATTRIBUTE7
,TP_ATTRIBUTE8
,TP_ATTRIBUTE9
,TP_ATTRIBUTE10
,TP_ATTRIBUTE11
,TP_ATTRIBUTE12
,TP_ATTRIBUTE13
,TP_ATTRIBUTE14
,TP_ATTRIBUTE15
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,CREATION_DATE
,CREATED_BY
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,BATCH_ID
,HASH_VALUE
,SOURCE_HEADER_ID
,NUMBER_OF_LPN
,COD_AMOUNT
,COD_CURRENCY_CODE
,COD_REMIT_TO
,COD_CHARGE_PAID_BY
,PROBLEM_CONTACT_REFERENCE
,PORT_OF_LOADING
,PORT_OF_DISCHARGE
,FTZ_NUMBER
,ROUTED_EXPORT_TXN
,ENTRY_NUMBER
,ROUTING_INSTRUCTIONS
,IN_BOND_CODE
,SHIPPING_MARKS
,SERVICE_LEVEL
,MODE_OF_TRANSPORT
,ASSIGNED_TO_FTE_TRIPS
--new added fields
, AUTO_SC_EXCLUDE_FLAG
, AUTO_AP_EXCLUDE_FLAG
, AP_BATCH_ID
--
,p_delivery_rec.ROWID
,p_delivery_rec.LOADING_ORDER_DESC
,p_delivery_rec.ORGANIZATION_CODE
,p_delivery_rec.ULTIMATE_DROPOFF_LOCATION_CODE
,p_delivery_rec.INITIAL_PICKUP_LOCATION_CODE
,p_delivery_rec.CUSTOMER_NUMBER
,p_delivery_rec.INTMED_SHIP_TO_LOCATION_CODE
,p_delivery_rec.POOLED_SHIP_TO_LOCATION_CODE
,p_delivery_rec.CARRIER_CODE
,p_delivery_rec.SHIP_METHOD_NAME
,p_delivery_rec.FREIGHT_TERMS_NAME
,p_delivery_rec.FOB_NAME
,p_delivery_rec.FOB_LOCATION_CODE
,p_delivery_rec.WEIGHT_UOM_DESC
,p_delivery_rec.VOLUME_UOM_DESC
,p_delivery_rec.CURRENCY_NAME
/* J Inbound Logistics: New columns jckwok */
,SHIPMENT_DIRECTION
,VENDOR_ID
,PARTY_ID
,ROUTING_RESPONSE_ID
,RCV_SHIPMENT_HEADER_ID
,ASN_SHIPMENT_HEADER_ID
,SHIPPING_CONTROL
/* J TP Release : ttrichy */
,TP_DELIVERY_NUMBER
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,IGNORE_FOR_PLANNING
,TP_PLAN_NAME
-- J: W/V Changes
,WV_FROZEN_FLAG
,HASH_STRING
,delivered_date
,p_delivery_rec.packing_slip
--bug 3667348
,REASON_OF_TRANSPORT
,DESCRIPTION
,'N' --Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
--OTM R12
,TMS_INTERFACE_FLAG
,TMS_VERSION_NUMBER
--R12.1.1 STANDALONE PROJECT
,PENDING_ADVICE_FLAG
,CLIENT_ID -- LSP PROJECT : Added just for compatibility ( not used anywhere). -- Modified R12.1.1 LSP PROJECT (rminocha)
,p_delivery_rec.client_code -- LSP PROJECT
,consignee_flag -- RTV changes
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_rec.delivery_id;
ELSIF p_in_rec.action_code = 'UPDATE' THEN
--
l_caller := p_in_rec.caller;
END IF; /* if action = 'UPDATE' */
status code, action not allowed etc.) and NOT the other way (update i after updating
status code, action not allowed).
*/
--OTM R12
IF l_gc3_is_installed = 'Y' THEN
IF p_action IN ('GET-FREIGHT-COSTS',
'FIRM',
'SELECT-CARRIER',
'RATE_WITH_UPS',
'UPS_TIME_IN_TRANSIT',
'UPS_ADDRESS_VALIDATION',
'UPS_TRACKING') THEN
i := i + 1;
'PICK-RELEASE-UI' ,'DELETE','ASSIGN-TRIP') THEN
-- J-IB-NPARIKH-{
IF p_action = 'WT-VOL'
THEN
--{
--
-- Calculate weight/volume action
-- - allowed for in-transit/closed inbound (not O/IO) deliveries
-- - not allowed for in-transit/closed outbound (O/IO) deliveries
--
i := i + 1;
IF p_action NOT IN ('ASSIGN-TRIP', 'UNASSIGN-TRIP', 'AUTOCREATE-TRIP', 'WT-VOL','DELETE') THEN
/*J add disallowed actions for shipment_direction jckwok */
i := i + 1;
'SELECT-CARRIER','GEN-LOAD-SEQ') THEN
--order of i := i + 1; changed to be before and not after addition of new record
select organization_id,
weight_uom_code,
volume_uom_code
from wsh_new_deliveries
where delivery_id = p_delivery_id;
SELECT weight_uom_class,weight_uom_code,volume_uom_class,volume_uom_code
FROM wsh_shipping_parameters
WHERE organization_id = p_organization_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code
AND uom_class = p_class
AND nvl(disable_date, sysdate) >= sysdate;
select uom_code
from mtl_units_of_measure
where uom_class = p_class
and base_uom_flag = 'Y'
AND nvl(disable_date, sysdate) >= sysdate;
select count (distinct wda.delivery_detail_id)
from wsh_delivery_assignments_v wda
where wda.parent_delivery_detail_id is null
and wda.delivery_id is NOT NULL
and level > 1
connect by prior wda.parent_delivery_detail_id = wda.delivery_detail_id
start with wda.delivery_id =p_delivery_id;
select wt.name, wnd.name
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
where wnd.delivery_id = p_del_id
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wts.stop_location_id = p_pickup_locn_id
and wts.trip_id = wt.trip_id;
select wt.name, wnd.name
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
where wnd.delivery_id = p_del_id
and wnd.delivery_id = wdl.delivery_id
and wdl.drop_off_stop_id = wts.stop_id
and wts.stop_location_id = p_dropoff_locn_id
and wts.trip_id = wt.trip_id;
l_strQuery := 'select wnd.delivery_id from wsh_new_deliveries wnd ' ||
-- ' where wnd.shipment_direction = ''O'' ';
SELECT severity, delivery_id, exception_id
FROM wsh_exceptions
WHERE delivery_id = p_delivery_id
AND status not in ('NOT_HANDLED' , 'NO_ACTION_REQUIRED' , 'CLOSED')
ORDER BY decode (severity, 'HIGH', 1,
'MEDIUM', 2,
3);
SELECT 'Y'
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND NVL(wdd.container_flag,'N') = 'N'
AND rownum = 1;
l_exceptions_tab.delete;
SELECT shipping_control, routing_response_id
FROM WSH_NEW_DELIVERIES
WHERE delivery_id = p_delivery_id;
SELECT 'Y'
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = p_delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND NVL(wdd.container_flag,'N') = 'N'
AND routing_req_id IS NOT NULL
AND rownum = 1;
IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'LAST_UPDATE_DATE, ';
IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATED_BY, ';
IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATE_LOGIN, ';
IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE, ';
select 1
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status in ('R','B','X')
and nvl(wdd.replenishment_status,'C') = 'C' --bug# 6719369 (replenishment project)
and wdd.source_code = 'OE'
and wda.delivery_id = p_delivery_id
and rownum =1;
SELECT wt.trip_id,
wt.name,
wt.status_code
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_trips wt,
wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_del_id
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wts.trip_id = wt.trip_id;
SELECT DELIVERY_ID
,NAME
,PLANNED_FLAG
,STATUS_CODE
,DELIVERY_TYPE
,INITIAL_PICKUP_LOCATION_ID
,ORGANIZATION_ID
,ULTIMATE_DROPOFF_LOCATION_ID
,CARRIER_ID
,SHIP_METHOD_CODE
,FREIGHT_TERMS_CODE
,FOB_CODE
,CURRENCY_CODE
,SERVICE_LEVEL
,MODE_OF_TRANSPORT
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,NVL(IGNORE_FOR_PLANNING, 'N')
,TP_PLAN_NAME
,WV_FROZEN_FLAG
,TMS_INTERFACE_FLAG
,TMS_VERSION_NUMBER
,client_id -- LSP PROJECT (used in wshddacb. assign_dd_to_del
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;