[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsh_location_id
,source_location_id
,location_source_code
,location_code
,address1
,city
,state
,country
,postal_code
,ui_location_code
, NULL -- hr_location_code
FROM wsh_locations
where wsh_location_id = v_location_id;
SELECT b.location_code
FROM hr_locations_all a,
hr_locations_all_tl b
WHERE a.location_id = v_location_id
AND a.location_id = b.location_id
AND b.language=USERENV('LANG');
select SITE_USE_ID from HZ_CUST_SITE_USES_ALL
where SITE_USE_CODE = c_site_code;
SELECT location_id
FROM wsh_ship_from_orgs_v
WHERE organization_id = v_org_id;
SELECT ps.location_id
FROM hz_party_sites ps,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
WHERE su.site_use_id = v_site_id
AND su.cust_acct_site_id = ca.cust_acct_site_id
AND ca.party_site_id = ps.party_site_id;
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = c_org_id;
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;
l_insert_text VARCHAR2(80);
l_insert_text := SUBSTRB(l_remainder_text,1,80);
fnd_file.put(FND_FILE.LOG, l_insert_text);
l_insert_text VARCHAR2(80);
l_insert_text := SUBSTRB(l_remainder_text,1,80);
fnd_file.put_line(FND_FILE.LOG, l_insert_text);
l_insert_text VARCHAR2(80);
l_insert_text := SUBSTRB(l_remainder_text,1,80);
fnd_file.put_line(FND_FILE.LOG, l_insert_text);
SELECT SYSDATE INTO l_date_time FROM dual;
query_string := 'SELECT '||p_pkey1||' FROM '||p_table_name||' WHERE ';
query_text := 'SELECT '|| P_COLUMN_NAME || ' FROM ' ||
P_TABLE_NAME || ' WHERE ROWID = :x_row_id';
SELECT HOU.NAME organization_name
FROM HR_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = p_organization_id;
PROCEDURE Delete(
p_type wsh_saved_queries_vl.entity_type%type,
p_rows IN OUT NOCOPY wsh_util_core.id_tab_type,
p_caller IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
is
cur_return_status varchar2(1);
l_delete_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_DELETE;
SELECT lpn_id
FROM wsh_delivery_details
WHERE delivery_detail_id = v_delivery_detail_id;
select lpn_id, delivery_detail_id FROM
wsh_lpn_purge_tmp
WHERE delivery_detail_id = v_delivery_detail_id
AND eligible_flag = 'Y';
select lpn_id FROM
wsh_lpn_purge_tmp
WHERE lpn_id IS NOT NULL
AND eligible_flag = 'Y';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE';
SAVEPOINT s_delete_savepoint;
SELECT 1
INTO l_count
FROM wsh_lpn_purge_tmp;
DELETE FROM wsh_lpn_purge_tmp;
INSERT INTO wsh_lpn_purge_tmp(
lpn_id,
delivery_detail_id,
eligible_flag
) VALUES (
l_rows_lpns(i),
p_rows(i),
decode(l_rows_lpns(i), NULL,'Y','N')
);
, p_caller => 'WSH_DELETE'
, p_action => l_validate_mode
, p_lpn_purge_rec => l_lpn_ids
);
UPDATE wsh_lpn_purge_tmp
SET eligible_flag = 'Y'
WHERE lpn_id = l_lpn_ids.lpn_ids(i);
p_action => 'DELETE',
x_return_status => l_return_status);
p_action => 'DELETE',
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.DELETE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_trips_pvt.delete_trip(p_rowid => null,
p_trip_id => p_rows(i),
x_return_status => cur_return_status,
p_validate_flag => 'Y',
p_caller => p_caller);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.DELETE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_trip_stops_pvt.delete_trip_stop(p_rowid => null,
p_stop_id => p_rows(i),
x_return_status => cur_return_status,
p_validate_flag => 'Y',
p_caller => p_caller);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.DELETE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_new_deliveries_pvt.delete_delivery(null, p_rows(i), cur_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.DELETE_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_ACTIONS.delete_containers(p_rows(i), cur_return_status);
update wsh_lpn_purge_tmp
SET eligible_flag = 'N'
WHERE delivery_detail_id = p_rows(i);
, p_caller => 'WSH_DELETE'
, p_action => l_delete_mode
, p_lpn_purge_rec => l_purged_lpns
);
ROLLBACK TO s_delete_savepoint;
l_message := 'WSH_TRIP_DELETE_SUMMARY';
l_message := 'WSH_STOP_DELETE_SUMMARY';
l_message := 'WSH_DLVY_DELETE_SUMMARY';
l_message := 'WSH_DLEG_DELETE_SUMMARY';
l_message := 'WSH_DLVB_DELETE_SUMMARY';
ROLLBACK TO s_delete_savepoint;
ROLLBACK TO s_delete_savepoint;
wsh_util_core.default_handler('WSH_UTIL_CORE.DELETE');
end delete;
SELECT wdd.shipment_priority_code ship_priority_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
AND wda.delivery_id = p_delivery_id
AND wda.delivery_id is not null
AND wdd.container_flag = 'N'
AND rownum = 1;
SELECT wdd.shipment_priority_code ship_priority_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
AND wda.delivery_id = p_delivery_id
AND wda.delivery_id is not null
AND wdd.container_flag = 'N'
AND (wdd.shipment_priority_code <> p_ship_code
OR wdd.shipment_priority_code IS NULL)
AND rownum = 1;
SELECT wdd.shipment_priority_code ship_priority_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
AND wda.delivery_id = p_delivery_id
AND wdd.container_flag = 'N'
AND wda.delivery_id is not null
AND wdd.shipment_priority_code IS NOT NULL
AND rownum = 1;
SELECT ar.org_id, ar.set_of_books_id, glpv.currency_code
FROM ar_system_parameters_all ar, gl_ledgers_public_v glpv
WHERE ar.ORG_ID = p_org_id
AND glpv.ledger_id = ar.set_of_books_id;
SELECT mp.wms_enabled_flag,
wnd.delivery_id
FROM wsh_new_deliveries wnd,
mtl_parameters mp
WHERE wnd.delivery_id = x_delivery_id AND
mp.organization_id = wnd.organization_id AND
mp.wms_enabled_flag = 'Y';
SELECT dg.delivery_id,
st.trip_id,
dl.organization_id,
mp.wms_enabled_flag
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
mtl_parameters mp
WHERE dg.delivery_id = dl.delivery_id AND
nvl(dl.SHIPMENT_DIRECTION, 'O') IN ('O', 'IO') AND -- J Inbound Logistics jckwok
st.stop_location_id = dl.initial_pickup_location_id AND
st.stop_id = dg.pick_up_stop_id AND
st.stop_id = l_stop_id AND
dl.organization_id = mp.organization_id AND
mp.wms_enabled_flag = 'Y';
l_del_tab.delete;
SELECT TP.Name, DLG.reprice_required
FROM WSH_DELIVERY_LEGS DLG,
WSH_TRIP_STOPS TS,
WSH_TRIPS TP
WHERE DLG.delivery_leg_id = p_delivery_leg_id AND
DLG.pick_up_stop_id = TS.stop_id AND
TS.trip_id = TP.trip_id;
SELECT wdd.org_id org_id , count(*) cnt
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = v_delivery_id
AND wdd.container_flag = 'N'
GROUP BY org_id
ORDER BY cnt DESC;
WSH_INTEGRATION.G_MSG_TABLE.delete ;
SELECT meaning
FROM WSH_LOOKUPS
WHERE LOOKUP_TYPE = p_lookup_type
AND LOOKUP_CODE = P_lookup_code;
ELSIF (p_action_code = 'DELETE') THEN
l_lookup_code:= 'DLVB_DELETE';
ELSIF (p_action_code = 'UPDATE') THEN
l_lookup_code:= 'DLVB_UPDATE';
ELSIF (p_action_code = 'SELECT-CARRIER') THEN
l_lookup_code:= 'DLVY_SELECT_CARRIER';
ELSIF (p_action_code = 'DELETE') THEN
l_lookup_code:= 'DLVY_DELETE';
ELSIF (p_action_code = 'UPDATE') THEN
l_lookup_code:= 'DLVY_UPDATE';
IF (p_action_code = 'UPDATE-STATUS') THEN
l_lookup_code:= 'STOP_UPDATE_STATUS';
ELSIF (p_action_code = 'DELETE') THEN
l_lookup_code:= 'STOP_DELETE';
ELSIF (p_action_code = 'UPDATE') THEN
l_lookup_code:= 'STOP_UPDATE';
ELSIF (p_action_code = 'DELETE') THEN
l_lookup_code:= 'TRIP_DELETE';
ELSIF (p_action_code = 'UPDATE') THEN
l_lookup_code:= 'TRIP_UPDATE';
select stop_id,
departure_gross_weight,
departure_net_weight,
departure_volume,
departure_fill_percent
from wsh_trip_stops
where stop_id = c_stop_id;
validated in Fte_Load_Tender to prevent error message that 'stop has been deleted'. */
IF (l_db_stop_id is not NULL) THEN
--{
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Stop ID',l_stop_id);
validated in Fte_Load_Tender to prevent error message that 'stop has been deleted'. */
IF (l_db_stop_id is not NULL) THEN
--{
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Stop ID',l_stop_id);
G_STOP_IDS_STOP_IDS_CACHE.delete;
G_STOP_IDS_STOP_IDS_EXT_CACHE.delete;
SELECT ac_intransit_flag
INTO v_ACIntransitFlag
FROM WSH_SHIP_CONFIRM_RULES
WHERE ship_confirm_rule_id = p_ship_confirm_rule_id;
SELECT ship_confirm_rule_id
INTO v_SCRuleID
FROM wsh_picking_rules
WHERE picking_rule_id = p_picking_rule_id;
SELECT hcas.cust_account_id
FROM wsh_locations wl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas
WHERE wl.wsh_location_id = c_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id;
SELECT owner_party_id
FROM wsh_location_owners
WHERE owner_type = 1
AND wsh_location_id = c_location_id
AND owner_party_id <> -1 ;
SELECT organization_id
FROM wsh_ship_from_orgs_v
WHERE location_id = c_location_id;
select status_code
from wsh_new_deliveries
where delivery_id = c_delivery_id;
select status_code
from wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda
where wnd.delivery_id (+) = wda.delivery_id
and wda.delivery_detail_id = c_delivery_detail_id;
select distinct wnd.delivery_id, wnd.status_code
from wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
where wnd.delivery_id (+) = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
--LPN Reuse project
and wdd.released_status = 'X'
and wdd.lpn_id in
( select wlpn.lpn_id
from wms_license_plate_numbers wlpn
where wlpn.outermost_lpn_id = c_lpn_id );
SELECT shipments_type_flag INTO l_typeflag
FROM WSH_TRIPS
WHERE TRIP_ID = p_trip_id;
SELECT mp. organization_id
INTO l_organization_id
FROM hr_organization_units hou,mtl_parameters mp
WHERE hou.organization_id = mp.organization_id
AND hou.location_id = l_first_stop_loc_id
AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
SELECT mp. organization_id
INTO l_organization_id
FROM hr_organization_units hou,mtl_parameters mp
WHERE hou.organization_id = mp.organization_id
AND hou.location_id = l_last_stop_loc_id
AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
SELECT dlvy.ORGANIZATION_ID
INTO l_organization_id
FROM WSH_TRIP_STOPS stops,
WSH_DELIVERY_LEGS leg,
WSH_NEW_DELIVERIES dlvy
WHERE stops.stop_id = leg.pick_up_stop_id
AND leg.delivery_id = dlvy.delivery_id
AND stops.stop_id = l_first_stop_id
AND ROWNUM = 1;
SELECT dlvy.ORGANIZATION_ID
INTO l_organization_id
FROM WSH_TRIP_STOPS stops, WSH_DELIVERY_LEGS leg,
WSH_NEW_DELIVERIES dlvy
WHERE stops.stop_id = leg.pick_up_stop_id
AND leg.delivery_id = dlvy.delivery_id
AND stops.stop_id = l_first_stop_id
AND ROWNUM = 1;
SELECT stop_location_id, planned_arrival_date, planned_departure_date ,
stops.stop_id
FROM wsh_trip_stops stops, wsh_trips trips
WHERE trips.trip_id = p_trip_id
and trips.trip_id = stops.trip_id
ORDER BY PLANNED_ARRIVAL_DATE,
STOP_SEQUENCE_NUMBER;
SELECT stop_location_id, planned_arrival_date, planned_departure_date,
stops.stop_id,stops.tms_interface_flag
FROM wsh_trip_stops stops,
wsh_trips trips,
wsh_delivery_legs wdg
WHERE trips.trip_id = p_trip_id
and trips.trip_id = stops.trip_id
and wdg.pick_up_stop_id = stops.stop_id
ORDER BY STOP_SEQUENCE_NUMBER;
SELECT stop_location_id, planned_arrival_date, planned_departure_date,
stops.stop_id,stops.tms_interface_flag
FROM wsh_trip_stops stops,
wsh_trips trips,
wsh_delivery_legs wdg
WHERE trips.trip_id = p_trip_id
and trips.trip_id = stops.trip_id
and wdg.drop_off_stop_id = stops.stop_id
ORDER BY STOP_SEQUENCE_NUMBER DESC;