[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
decode(wda.delivery_id, x_plan_delivery_id,1,null, 2, 3) choice,
decode(wdd.delivery_detail_id,
x_plan_delivery_detail_id, 1,
decode(wdd.source_line_id,
x_plan_source_line_id, 2,
3)) scope,
wdd.delivery_detail_id,
nvl(wdd.shipped_quantity, nvl(wdd.picked_quantity, wdd.requested_quantity)) quantity,
wdd.requested_quantity_uom quantity_uom,
wdd.source_code,
wdd.source_header_id,
wdd.source_line_set_id,
wdd.source_line_id,
wdd.ship_from_location_id,
wdd.ship_to_location_id,
wdd.inventory_item_id,
wdd.released_status,
wdd.move_order_line_id,
wdd.line_direction,
wdd.ship_set_id,
wdd.po_shipment_line_id, -- inbound/drop
wdd.top_model_line_id,
wdd.ato_line_id,
wdd.ship_model_complete_flag,
wdd.organization_id,
wdd.customer_id,
wdd.fob_code,
wdd.freight_terms_code,
wdd.intmed_ship_to_location_id,
wdd.ship_method_code,
wdd.mode_of_transport,
wdd.service_level,
wdd.carrier_id,
wda.parent_delivery_detail_id parent_cont_id,
wda.delivery_id,
wdd.src_requested_quantity,
wdd.src_requested_quantity_uom,
wdd.shipping_control,
wdd.vendor_id,
wdd.party_id,
wdd.wv_frozen_flag -- WV changes
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
(
wdd.source_code = x_plan_source_code
AND wdd.source_header_id = x_plan_source_header_id
AND ( wdd.source_line_set_id = x_plan_source_line_set_id
OR wdd.source_line_id = x_plan_source_line_id)
AND ( x_plan_po_shipment_line_id IS NULL -- inbound/drop
OR wdd.po_shipment_line_id = x_plan_po_shipment_line_id)
)
AND wdd.ship_from_location_id = x_plan_ship_from_location_id
AND wdd.ship_to_location_id = x_plan_ship_to_location_id
AND wdd.released_status IN ('N', 'R', 'B', 'S', 'Y', 'X')
AND nvl(wdd.ignore_for_planning,'N') = 'N'
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.container_flag = 'N'
AND (
(wda.delivery_id IS NOT NULL
AND wda.delivery_id = x_plan_delivery_id
AND x_plan_delivery_id IS NOT NULL
)
OR
(wda.delivery_id IS NULL
)
OR
(exists ( select 'x'
from wsh_new_deliveries wnd
where wnd.delivery_id = wda.delivery_id
AND wnd.planned_flag = 'N'
AND wnd.status_code IN ('OP', 'SA')
AND wnd.delivery_id <> NVL(x_plan_delivery_id, 0)
)
)
)
ORDER BY choice ASC, scope ASC, quantity DESC;
PROCEDURE create_update_plan_trips(
p_phase IN NUMBER,
x_context IN OUT NOCOPY context_rec_type,
x_plan_trips IN OUT NOCOPY plan_trip_tab_type,
x_errors_tab IN OUT NOCOPY interface_errors_tab_type,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE insert_interface_errors(
p_errors_tab IN interface_errors_tab_type,
x_return_status OUT NOCOPY VARCHAR2);
insert_interface_errors(
p_errors_tab => l_errors_tab,
x_return_status => l_return_status);
l_errors_tab.DELETE;
delete from wsh_del_details_interface wddi
where wddi.delivery_detail_interface_id in (
select wdai.delivery_detail_interface_id
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli,
wsh_del_assgn_interface wdai
where wti.group_id = p_group_ids(i)
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TP_RELEASE_CODE
and wdli.delivery_interface_id = wdai.delivery_interface_id
and wdli.interface_action_code = G_TP_RELEASE_CODE
and wdai.delivery_interface_id is not null
and wdai.interface_action_code = G_TP_RELEASE_CODE)
and wddi.interface_action_code = G_TP_RELEASE_CODE;
delete from wsh_del_assgn_interface wdai
where wdai.delivery_interface_id in (
select wdli.delivery_interface_id
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wti.group_id = p_group_ids(i)
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TP_RELEASE_CODE
and wdli.interface_action_code = G_TP_RELEASE_CODE)
and wdai.interface_action_code = G_TP_RELEASE_CODE;
delete from wsh_new_del_interface wndi
where wndi.delivery_interface_id in (
select wdli.delivery_interface_id
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wti.group_id = p_group_ids(i)
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TP_RELEASE_CODE
and wdli.interface_action_code = G_TP_RELEASE_CODE)
and wndi.interface_action_code = G_TP_RELEASE_CODE;
delete from wsh_del_legs_interface wdli
where wdli.pick_up_stop_interface_id in (
select wtsi.stop_interface_id
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi
where wti.group_id = p_group_ids(i)
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.interface_action_code = G_TP_RELEASE_CODE)
and wdli.interface_action_code = G_TP_RELEASE_CODE;
delete from wsh_trip_stops_interface wtsi
where wtsi.trip_interface_id in (
select wtsi.stop_interface_id
from wsh_trips_interface wti
where wti.group_id = p_group_ids(i)
and wti.interface_action_code = G_TP_RELEASE_CODE)
and wtsi.interface_action_code = G_TP_RELEASE_CODE;
delete from wsh_trips_interface wti
where wti.group_id = p_group_ids(i)
and wti.INTERFACE_ACTION_CODE = G_TP_RELEASE_CODE;
delete from wsh_interface_errors wie
where wie.interface_error_group_id = p_group_ids(i)
and wie.interface_action_code = G_TP_RELEASE_CODE;
x_plan_details.DELETE;
x_track_conts.DELETE;
x_plan_deliveries.DELETE;
x_plan_legs.DELETE;
x_plan_stops.DELETE;
x_plan_trips.DELETE;
x_plan_trip_moves.DELETE;
x_plan_moves.DELETE;
x_used_details.DELETE;
x_delivery_unassigns.DELETE;
x_trip_unassigns.DELETE;
x_obsoleted_stops.DELETE;
x_obsoleted_trip_moves.DELETE;
x_context.wv_exception_details.delete;
x_context.wv_exception_dels.delete;
x_context.wv_exception_stops.delete;
select distinct wddi.delivery_detail_interface_id delivery_detail_interface_id,
wddi.delivery_detail_id delivery_detail_id,
wddi.split_from_delivery_detail_id split_from_delivery_detail_id,
wddi.source_code,
wddi.source_header_id,
wddi.source_line_id,
wddi.source_line_set_id,
wndi.tp_delivery_number
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli,
wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi,
wsh_new_del_interface wndi
where wti.group_id = p_group_id
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TP_RELEASE_CODE
and wdli.delivery_interface_id = wdai.delivery_interface_id
and wdli.interface_action_code = G_TP_RELEASE_CODE
and wdai.delivery_interface_id is not null
and wdai.interface_action_code = G_TP_RELEASE_CODE
and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wddi.interface_action_code = G_TP_RELEASE_CODE
and wddi.container_flag = 'N'
and wddi.requested_quantity <> 0 -- bug 4322654
and wndi.delivery_interface_id = wdli.delivery_interface_id
and wndi.interface_action_code = G_TP_RELEASE_CODE
and NVL(wddi.line_direction, 'O') IN (p_dir1, p_dir2);
select a.released_status, a.delivery_detail_id
from wsh_delivery_details a,
wsh_delivery_details b
where b.delivery_detail_id = p_delivery_detail_id
and b.released_status = 'D'
and a.source_code = b.source_code
and a.source_line_id = b.source_line_id
and a.delivery_detail_id <> b.delivery_detail_id
and a.released_status NOT IN ('C', 'D')
and rownum = 1
UNION
select released_status, delivery_detail_id
from wsh_delivery_details
where released_status NOT IN ('C', 'D')
and delivery_detail_id = p_delivery_detail_id;
select a.released_status, a.delivery_detail_id
from wsh_delivery_details a,
wsh_delivery_details b
where b.delivery_detail_id = p_delivery_detail_id
and a.source_code = b.source_code
and a.source_header_id = b.source_header_id
and a.source_line_set_id IN (
SELECT c.source_line_set_id
FROM WSH_DELIVERY_DETAILS c
WHERE c.source_code = b.source_code
AND c.source_line_id = b.source_line_id
AND c.source_line_set_id IS NOT NULL)
and a.delivery_detail_id <> b.delivery_detail_id
and a.released_status NOT IN ('C', 'D')
and rownum = 1;
select wdd.delivery_detail_id
from wsh_delivery_details wdd
where wdd.source_code = p_source_code
and wdd.source_header_id = p_source_header_id
and wdd.source_line_id = p_source_line_id
and wdd.container_flag = 'N'
and wdd.released_status NOT IN ('C', 'D')
and rownum = 1;
select wdd.source_line_set_id
from wsh_delivery_details wdd
where wdd.source_code = p_source_code
and wdd.source_header_id = p_source_header_id
and wdd.source_line_id = p_source_line_id
and wdd.container_flag = 'N'
and wdd.source_line_set_id IS NOT NULL
and rownum = 1;
select wdd.delivery_detail_id
from wsh_delivery_details wdd
where wdd.source_code = p_source_code
and wdd.source_header_id = p_source_header_id
and wdd.source_line_set_id = p_source_line_set_id
and wdd.container_flag = 'N'
and wdd.released_status NOT IN ('C', 'D')
and rownum = 1;
select a.delivery_detail_id
from wsh_delivery_details a,
wsh_delivery_details b
where b.delivery_detail_id = p_delivery_detail_id
and a.source_code = b.source_code
and a.source_line_id = b.source_line_id
and a.po_shipment_line_id = b.po_shipment_line_id
and a.delivery_detail_id <> b.delivery_detail_id
and a.released_status = 'X'
and rownum = 1
UNION
select delivery_detail_id
from wsh_delivery_details
where released_status = 'X'
and delivery_detail_id = p_delivery_detail_id;
select wddi.delivery_detail_id
from wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli,
wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi,
wsh_new_del_interface wndi
where wti.group_id = p_group_id
and wti.trip_interface_id = wtsi.trip_interface_id
and wti.interface_action_code = G_TP_RELEASE_CODE
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TP_RELEASE_CODE
and wdli.delivery_interface_id = wdai.delivery_interface_id
and wdli.interface_action_code = G_TP_RELEASE_CODE
and wdai.delivery_interface_id is not null
and wdai.interface_action_code = G_TP_RELEASE_CODE
and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wddi.interface_action_code = G_TP_RELEASE_CODE
and ((wddi.container_flag = 'Y') OR
(wddi.container_flag = 'N' and wddi.requested_quantity = 0))
and wndi.delivery_interface_id = wdli.delivery_interface_id
and wndi.interface_action_code = G_TP_RELEASE_CODE
and rownum = 1;
UPDATE wsh_del_details_interface
SET (source_line_id, source_line_set_id, source_header_id) =
(select wdd.source_line_id, wdd.source_line_set_id, wdd.source_header_id
from wsh_delivery_details wdd
where wdd.delivery_detail_id = l_dd_id(i))
WHERE delivery_detail_interface_id = l_dd_interface_id(i);
UPDATE wsh_del_details_interface
SET (source_line_id, source_line_set_id, source_header_id, po_shipment_line_id, po_shipment_line_number) =
(select wdd.source_line_id, wdd.source_line_set_id, wdd.source_header_id, po_shipment_line_id, po_shipment_line_number
from wsh_delivery_details wdd
where wdd.delivery_detail_id = l_dd_id(i))
WHERE delivery_detail_interface_id = l_dd_interface_id(i);
SELECT DISTINCT
wddi.delivery_detail_interface_id dd_interface_id,
wddi.delivery_detail_id delivery_detail_id,
wddi.split_from_delivery_detail_id split_from_delivery_detail_id,
wddi.tp_delivery_detail_id,
wddi.source_code,
wddi.source_header_id,
wddi.source_line_id,
wddi.source_line_set_id,
wddi.po_shipment_line_id,
wddi.ship_from_location_id,
wddi.ship_to_location_id,
wddi.requested_quantity quantity,
wddi.requested_quantity_uom quantity_uom,
wddi.src_requested_quantity,
wddi.src_requested_quantity_uom,
wndi.delivery_id,
wndi.delivery_interface_id
FROM wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi
WHERE wti.group_id = p_group_id
AND wti.interface_action_code = G_TP_RELEASE_CODE
AND wti.trip_interface_id = wtsi.trip_interface_id
AND wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi.interface_action_code = G_TP_RELEASE_CODE
AND wndi.delivery_interface_id = wdli.delivery_interface_id
AND wdai.delivery_interface_id = wndi.delivery_interface_id
AND wdai.delivery_interface_id IS NOT NULL
AND wdai.interface_action_code = G_TP_RELEASE_CODE
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wddi.interface_action_code = G_TP_RELEASE_CODE
AND wddi.container_flag = 'N'
AND wddi.requested_quantity <> 0 -- Bug 4322654
ORDER BY
-- outbound: group by line sets and order lines
-- inbound/drop: group by po line shipments
wddi.source_code,
wddi.source_line_set_id,
DECODE(wddi.source_line_set_id,
NULL, 0,
wddi.requested_quantity) DESC,
wddi.source_line_id,
wddi.po_shipment_line_id,
wddi.requested_quantity DESC
;
SELECT DISTINCT
wndi.delivery_id original_delivery_id,
wndi.delivery_interface_id,
wndi.tp_delivery_number,
wndi.tp_plan_name,
wndi.planned_flag,
wnd.delivery_id, -- if delivery has been deleted, we can create a new record.
wnd.name wsh_name,
wnd.status_code,
nvl(wnd.ignore_for_planning, 'N') ignore_for_planning,
wnd.planned_flag wsh_planned_flag,
wnd.initial_pickup_location_id wsh_initial_pu_loc_id,
wnd.ultimate_dropoff_location_id wsh_ultimate_do_loc_id,
wndi.initial_pickup_location_id,
wndi.initial_pickup_date,
wndi.ultimate_dropoff_location_id,
wndi.ultimate_dropoff_date,
NVL(wnd.ship_method_code, wndi.ship_method_code) ship_method_code,
NVL(wnd.mode_of_transport, wndi.mode_of_transport) mode_of_transport,
NVL(wnd.service_level, wndi.service_level) service_level,
NVL(wnd.freight_terms_code, wndi.freight_terms_code) freight_terms_code,
wndi.name,
wndi.loading_sequence,
wndi.loading_order_flag,
NVL(wnd.fob_code, wndi.fob_code) fob_code,
NVL(wnd.fob_location_id, wndi.fob_location_id) fob_location_id,
wndi.waybill,
wndi.currency_code,
NVL(wnd.party_id, wndi.party_id) party_id,
NVL(wnd.shipping_control, wndi.shipping_control) shipping_control,
NVL(wnd.vendor_id, wndi.vendor_id) vendor_id,
wndi.organization_id,
wndi.customer_id,
NVL(wnd.intmed_ship_to_location_id, wndi.intmed_ship_to_location_id)
intmed_ship_to_location_id,
NVL(wnd.carrier_id, wndi.carrier_id) carrier_id,
NVL(wnd.shipment_direction, wndi.shipment_direction) shipment_direction,
wndi.additional_shipment_info,
wndi.gross_weight,
wndi.net_weight,
wndi.weight_uom_code,
wndi.volume,
wndi.volume_uom_code,
wndi.pooled_ship_to_location_id,
wndi.dock_code,
wnd.wv_frozen_flag -- WV changes
FROM wsh_trips_interface wti,
wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_new_deliveries wnd
WHERE wti.group_id = p_group_id
AND wti.interface_action_code = G_TP_RELEASE_CODE
AND wti.trip_interface_id = wtsi.trip_interface_id
AND wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi.interface_action_code = G_TP_RELEASE_CODE
AND wndi.delivery_interface_id = wdli.delivery_interface_id
AND wndi.interface_action_code = G_TP_RELEASE_CODE
AND wnd.delivery_id(+) = wndi.delivery_id
ORDER BY wndi.delivery_interface_id
;
SELECT
wti.trip_interface_id,
wti.planned_flag,
wti.tp_trip_number,
wti.tp_plan_name,
wt.trip_id,
wt.status_code wsh_status_code,
wt.planned_flag wsh_planned_flag,
nvl(wt.ignore_for_planning, 'N') wsh_ignore_for_planning,
wt.carrier_id wsh_carrier_id,
wt.mode_of_transport wsh_mode_of_transport,
wt.service_level wsh_service_level,
wt.vehicle_organization_id wsh_vehicle_org_id,
wt.vehicle_item_id wsh_vehicle_item_id,
wt.lane_id wsh_lane_id,
wti.name,
wti.vehicle_item_id,
wti.vehicle_organization_id,
wti.vehicle_num_prefix,
wti.vehicle_number,
wti.carrier_id,
wti.ship_method_code,
wti.route_id,
wti.routing_instructions,
wti.service_level,
wti.mode_of_transport,
wti.freight_terms_code,
wti.schedule_id,
wti.consolidation_allowed,
wti.route_lane_id,
wti.lane_id,
wti.seal_code,
wti.shipments_type_flag,
wti.booking_number,
wti.vessel,
wti.voyage_number,
wti.port_of_loading,
wti.port_of_discharge,
wti.carrier_contact_id,
wti.shipper_wait_time,
wti.wait_time_uom,
wti.carrier_response,
wti.operator
FROM wsh_trips_interface wti,
wsh_trips wt
WHERE wti.group_id = p_group_id
AND wti.interface_action_code = G_TP_RELEASE_CODE
AND wt.trip_id(+) = wti.trip_id
ORDER BY wti.trip_interface_id
;
SELECT
wtsi.stop_interface_id,
wtsi.stop_id,
wtsi.tp_stop_id,
wtsi.stop_location_id,
wtsi.stop_sequence_number,
wtsi.planned_arrival_date,
wtsi.planned_departure_date,
wtsi.departure_gross_weight,
wtsi.departure_net_weight,
wtsi.weight_uom_code,
wtsi.departure_volume,
wtsi.volume_uom_code,
wtsi.departure_seal_code,
wtsi.departure_fill_percent,
wtsi.wkend_layover_stops,
wtsi.wkday_layover_stops,
wtsi.shipments_type_flag
FROM wsh_trip_stops_interface wtsi
WHERE wtsi.trip_interface_id = p_trip_interface_id
AND wtsi.interface_action_code = G_TP_RELEASE_CODE
ORDER BY wtsi.stop_sequence_number
;
SELECT wts.stop_id,
wv_frozen_flag -- WV changes
FROM wsh_trip_stops wts
WHERE wts.trip_id = p_trip_id
AND wts.stop_location_id = p_stop_location_id
AND wts.planned_arrival_date = p_planned_arrival_date
AND wts.planned_departure_date = p_planned_departure_date
AND wts.status_code = 'OP'
UNION
SELECT wts.stop_id, wv_frozen_flag
FROM wsh_trip_stops wts
WHERE wts.trip_id = p_trip_id
AND wts.physical_location_id = p_stop_location_id
AND wts.physical_stop_id IS NULL
AND wts.planned_arrival_date = p_planned_arrival_date
AND wts.planned_departure_date = p_planned_departure_date
AND wts.status_code = 'OP'
;
SELECT wdl.delivery_leg_id delivery_leg_id,
wt.trip_id trip_id,
wt.planned_flag trip_planned_flag,
wt.carrier_id carrier_id,
wt.mode_of_transport mode_of_transport,
wt.service_level service_level,
wt.vehicle_organization_id vehicle_org_id,
wt.vehicle_item_id vehicle_item_id,
wt.lane_id lane_id,
wts_pu.stop_id pu_stop_id,
wts_pu.stop_location_id pu_stop_location_id,
wts_pu.planned_arrival_date pu_planned_arrival_date,
wts_pu.planned_departure_date pu_planned_departure_date,
wts_pu.wv_frozen_flag pu_wv_flag,
NVL(wts_do.physical_stop_id, wts_do.stop_id) do_stop_id,
NVL(wts_do.physical_location_id, wts_do.stop_location_id)
do_stop_location_id,
wts_do.physical_stop_id do_physical_stop_id,
wts_do.planned_arrival_date do_planned_arrival_date,
wts_do.planned_departure_date do_planned_departure_date,
wts_do.wv_frozen_flag do_wv_flag
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts_pu,
wsh_trip_stops wts_do,
wsh_trips wt
WHERE wdl.delivery_id = p_delivery_id
AND wts_pu.stop_id = wdl.pick_up_stop_id
AND wts_do.stop_id = wdl.drop_off_stop_id
AND wt.trip_id = wts_pu.trip_id
ORDER BY wdl.delivery_leg_id;
SELECT planned_arrival_date,
planned_departure_date,
wv_frozen_flag
FROM wsh_trip_stops wts
WHERE wts.stop_id = p_stop_id;
SELECT wdli.delivery_leg_interface_id,
wdli.delivery_leg_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id,
wtsi_pu.trip_interface_id
FROM wsh_del_legs_interface wdli,
wsh_trip_stops_interface wtsi_pu
WHERE wdli.delivery_interface_id = p_delivery_interface_id
AND wdli.interface_action_code = G_TP_RELEASE_CODE
AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi_pu.interface_action_code = G_TP_RELEASE_CODE
ORDER BY wdli.sequence_number, wdli.delivery_leg_interface_id
;
SELECT wdd.delivery_detail_id
FROM WSH_DELIVERY_DETAILS wdd
WHERE wdd.source_code = p_source_code
AND wdd.source_line_id = p_source_line_id
AND wdd.released_status IN ('B', 'S', 'Y', 'C')
AND rownum = 1;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE
(
wdd.source_code = p_source_code
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
)
AND ( wdd.ship_from_location_id <> p_ship_from_location_id
OR wdd.ship_to_location_id <> p_ship_to_location_id)
AND wdd.released_status IN ('N', 'R', 'B', 'S', 'Y', 'X')
AND nvl(wdd.ignore_for_planning, 'N') = 'N'
AND rownum = 1;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE
(
wdd.source_code = p_source_code
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
)
AND wdd.released_status IN ('N', 'R', 'B', 'S', 'Y', 'X')
AND wdd.ignore_for_planning = 'Y'
AND NOT EXISTS (SELECT 'planning'
FROM wsh_delivery_details wdd2
WHERE wdd2.source_code = p_source_code
AND wdd2.source_header_id = p_source_header_id
AND wdd2.source_line_id = p_source_line_id
AND nvl(wdd2.ignore_for_planning, 'N') = 'N')
AND rownum = 1;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH wda.delivery_detail_id = p_topmost_cont_id
CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT wdd.delivery_detail_id,
wdd.container_flag,
wdd.lpn_id,
wdd.released_status,
wdd.source_code
FROM wsh_delivery_details wdd
WHERE delivery_detail_id = p_detail_id;
l_cont_contents.DELETE(l_content_index);
l_cont_contents.DELETE(l_content_index);
x_plan_stops(l_stop_index).stop_id := NULL; -- to be updated
x_plan_stops(l_stop_index).wv_frozen_flag := NULL; -- to be updated, WV changes
l_snapshot_leg_ids.DELETE;
l_snapshot_trip_ids.DELETE;
l_snapshot_trip_plan_flags.DELETE;
l_snapshot_pu_stop_ids.DELETE;
l_snapshot_pu_seq_nums.DELETE;
l_snapshot_pu_loc_ids.DELETE;
l_snapshot_pu_arrive_dates.DELETE;
l_snapshot_pu_depart_dates.DELETE;
l_snapshot_pu_wv_flag.DELETE;
l_snapshot_do_stop_ids.DELETE;
l_snapshot_do_phys_stop_ids.DELETE;
l_snapshot_do_seq_nums.DELETE;
l_snapshot_do_loc_ids.DELETE;
l_snapshot_do_arrive_dates.DELETE;
l_snapshot_do_depart_dates.DELETE;
l_snapshot_do_wv_flag.DELETE;
l_snapshot_carrier_ids.DELETE;
l_snapshot_modes.DELETE;
l_snapshot_service_levels.DELETE;
l_snapshot_veh_org_ids.DELETE;
l_snapshot_veh_item_ids.DELETE;
l_snapshot_lane_ids.DELETE;
l_snapshot_leg_ids.DELETE(l_used_index);
l_snapshot_leg_ids.DELETE(l_used_index);
l_snapshot_trip_ids.DELETE(l_used_index);
l_snapshot_trip_plan_flags.DELETE(l_used_index);
l_snapshot_pu_stop_ids.DELETE(l_used_index);
l_snapshot_pu_seq_nums.DELETE(l_used_index);
l_snapshot_pu_loc_ids.DELETE(l_used_index);
l_snapshot_pu_arrive_dates.DELETE(l_used_index);
l_snapshot_pu_depart_dates.DELETE(l_used_index);
l_snapshot_do_stop_ids.DELETE(l_used_index);
l_snapshot_do_seq_nums.DELETE(l_used_index);
l_snapshot_do_loc_ids.DELETE(l_used_index);
l_snapshot_do_arrive_dates.DELETE(l_used_index);
l_snapshot_do_depart_dates.DELETE(l_used_index);
select initial_pickup_location_id
from wsh_new_deliveries
where wv_frozen_flag = 'Y'
and delivery_id = p_delivery_id;
select s.stop_id, s.stop_location_id
from wsh_trip_stops s,
wsh_trip_stops spu,
wsh_trip_stops sdo
where s.wv_frozen_flag = 'Y'
and (s.stop_sequence_number between
spu.stop_sequence_number
and
sdo.stop_sequence_number)
and spu.stop_id = p_pickup_stop_id
and sdo.stop_id = p_dropoff_stop_id
and s.trip_id = p_trip_id;
select wts.stop_id, wts.stop_location_id
from wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trip_stops wtspu,
wsh_trip_stops wtsdo
where wdl.delivery_id = p_delivery_id
and (wts.stop_sequence_number between
wtspu.stop_sequence_number
and
wtsdo.stop_sequence_number)
and wdl.pick_up_stop_id = wtspu.stop_id
and wdl.drop_off_stop_id = wtsdo.stop_id
and wts.trip_id = wtspu.trip_id
and wts.wv_frozen_flag = 'Y';
select wts.stop_id, wts.stop_location_id
from wsh_trip_stops wts
where wts.stop_sequence_number < p_dropoff_seq_number
and wts.stop_sequence_number > p_pickup_seq_number
and wts.trip_id = p_trip_id
and wts.wv_frozen_flag = 'Y';
l_dd_attrs.DELETE;
l_del_attrs.DELETE;
WSH_DEBUG_SV.log(l_module_name,'3. Create/update deliveries: x_plan_deliveries.COUNT: ', x_plan_deliveries.COUNT);
l_del_attrs.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'create/update deliveries: l_index', l_index);
l_del_in_rec.action_code := 'UPDATE';
l_message_name := 'WSH_TP_F_UPDATE_DEL';
wsh_deliveries_grp.create_update_delivery (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE, -- bug 3593690: retain messages from copy_delivery_record
p_commit => FND_API.G_FALSE,
p_in_rec => l_del_in_rec,
p_rec_attr_tab => l_del_attrs,
x_del_out_rec_tab => l_del_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.log(l_module_name,'4.0. Create/update trips: x_plan_trips.COUNT: ', x_plan_trips.COUNT);
create_update_plan_trips(
p_phase => 1,
x_context => x_context,
x_plan_trips => x_plan_trips,
x_errors_tab => x_errors_tab,
x_return_status => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'4.1. Delete stops: x_obsoleted_stops.COUNT: ', x_obsoleted_stops.COUNT);
l_stop_attrs.DELETE;
l_stop_action_prms.action_code := 'DELETE';
WSH_DEBUG_SV.log(l_module_name,'4.2 Create/update stops: x_plan_stops.COUNT: ', x_plan_stops.COUNT);
l_stop_attrs.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'create/update stops: l_index', l_index);
l_stop_in_rec.action_code := 'UPDATE';
l_message_name := 'WSH_TP_F_UPDATE_STOP';
wsh_trip_stops_grp.Create_Update_Stop(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_in_rec => l_stop_in_rec,
p_rec_attr_tab => l_stop_attrs,
x_stop_out_tab => l_stop_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab
);
WSH_DEBUG_SV.log(l_module_name,'4.3 update lane_id on trips: x_plan_trips.COUNT: ', x_plan_trips.COUNT);
create_update_plan_trips(
p_phase => 2,
x_context => x_context,
x_plan_trips => x_plan_trips,
x_errors_tab => x_errors_tab,
x_return_status => l_return_status
);
l_dd_attrs.DELETE;
l_dd_attrs.DELETE;
l_dd_attrs.DELETE;
l_work_details.DELETE(l_work_index);
WSH_DEBUG_SV.log(l_module_name,'8. update delivery details with TP attributes: x_plan_details.COUNT: ', x_plan_details.COUNT);
UPDATE wsh_delivery_details
SET tp_delivery_detail_id = x_plan_details(l_index).tp_delivery_detail_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_detail_id = x_plan_details(l_index).delivery_detail_id;
l_del_attrs.DELETE;
select delivery_detail_id
from wsh_delivery_assignments_v
where delivery_id = p_del_id
and delivery_id is not null
and rownum = 1;
select wdl.delivery_leg_id,
wdl.pick_up_stop_id,
wdl.drop_off_stop_id,
wts.trip_id
from wsh_delivery_legs wdl,
wsh_trip_stops wts
where wdl.delivery_id = p_del_id
and wts.stop_id = wdl.pick_up_stop_id;
select s.planned_departure_date
from wsh_trip_stops s,
wsh_delivery_legs l
where s.stop_location_id = p_init_pu_loc_id
and s.stop_id = l.pick_up_stop_id
and l.delivery_id = p_delivery_id;
select s.planned_arrival_date
from wsh_trip_stops s,
wsh_delivery_legs l
where s.stop_location_id = p_ult_do_loc_id
and s.stop_id = l.drop_off_stop_id
and l.delivery_id = p_delivery_id;
select s.planned_arrival_date
from wsh_trip_stops s,
wsh_delivery_legs l
where s.stop_location_id = p_ult_do_loc_id
and s.stop_id = l.drop_off_stop_id
and l.delivery_id = p_delivery_id
and s.physical_stop_id IS NOT NULL
UNION
select phys_s.planned_arrival_date
from wsh_trip_stops s,
wsh_trip_stops phys_s,
wsh_delivery_legs l
where s.stop_location_id = p_ult_do_loc_id
and s.stop_id = l.drop_off_stop_id
and l.delivery_id = p_delivery_id
and phys_s.stop_id = s.physical_stop_id
;
select wdd.delivery_detail_id,
wdd.ship_from_location_id,
wdd.earliest_pickup_date,
wdd.latest_dropoff_date
from wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id is not null
and (wdd.earliest_pickup_date > p_initial_pu_date or
wdd.latest_dropoff_date < p_final_do_date)
and wnd.delivery_id = p_delivery_id;
SELECT wts.stop_id
FROM WSH_TRIP_STOPS wts
WHERE wts.trip_id = p_trip_id
AND rownum = 1;
SELECT wdl.delivery_leg_id
FROM WSH_DELIVERY_LEGS wdl,
WSH_TRIP_STOPS wts
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = p_trip_id
AND rownum = 1
UNION
SELECT wdl.delivery_leg_id
FROM WSH_DELIVERY_LEGS wdl,
WSH_TRIP_STOPS wts
WHERE wdl.drop_off_stop_id = wts.stop_id
AND wts.trip_id = p_trip_id
AND rownum = 1;
select stop_location_id
from wsh_trip_stops
where wv_frozen_flag = 'Y'
and stop_id = p_stop_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id;
SELECT NVL(wts.physical_stop_id, wts.stop_id)
FROM WSH_TRIP_STOPS wts
WHERE wts.stop_id = p_stop_id;
SELECT wdl.delivery_leg_id
FROM WSH_DELIVERY_LEGS wdl
WHERE wdl.pick_up_stop_id = p_stop_id
AND rownum = 1
UNION
SELECT wdl.delivery_leg_id
FROM WSH_DELIVERY_LEGS wdl
WHERE wdl.drop_off_stop_id = p_stop_id
AND rownum = 1;
x_context.wv_exception_dels.delete(l_empty_dels(i));
WSH_DEBUG_SV.log(l_module_name,'before DELETE: l_empty_dels.COUNT', l_empty_dels.COUNT);
WSH_UTIL_CORE.Delete(p_type => 'DLVY',
p_rows => l_empty_dels,
x_return_status => l_return_status);
l_empty_stops.DELETE;
l_empty_trips.DELETE;
x_context.wv_exception_stops.delete(l_empty_stops(i));
l_stop_action_prms.action_code := 'DELETE';
l_trip_action_prms.action_code := 'DELETE';
WSH_DEBUG_SV.logmsg(l_module_name, '5. update interface tables');
WSH_DEBUG_SV.log(l_module_name, 'Bulk update trips: count', l_interface_ids.COUNT);
UPDATE wsh_trips_interface
SET trip_id = l_ids(i)
WHERE trip_interface_id = l_interface_ids(i);
l_interface_ids.DELETE;
l_ids.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'Bulk update stops: count', l_interface_ids.COUNT);
UPDATE wsh_trip_stops_interface
SET stop_id = l_ids(i)
WHERE stop_interface_id = l_interface_ids(i);
l_interface_ids.DELETE;
l_ids.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'Bulk update deliveries: count', l_interface_ids.COUNT);
UPDATE wsh_new_del_interface
SET delivery_id = l_ids(i)
WHERE delivery_interface_id = l_interface_ids(i);
l_interface_ids.DELETE;
l_ids.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'Bulk update details: count', l_interface_ids.COUNT);
UPDATE wsh_del_details_interface
SET delivery_detail_id = l_ids(i),
requested_quantity = l_qtys(i)
WHERE delivery_detail_interface_id = l_interface_ids(i);
x_context.wv_exception_details.delete;
x_context.wv_exception_dels.delete;
x_context.wv_exception_stops.delete;
x_current_used_details.DELETE;
SELECT d.delivery_detail_id
FROM wsh_delivery_assignments_v a, wsh_delivery_details d
WHERE a.parent_delivery_detail_id is NULL
AND a.delivery_id = p_delivery_id
AND a.delivery_id IS NOT NULL
AND a.delivery_detail_id = d.delivery_detail_id
AND d.released_status <> 'D' -- 4322654
AND d.container_flag = 'N'
AND rownum = 1;
SELECT d.delivery_detail_id,
d.container_name,
a.parent_delivery_detail_id
FROM wsh_delivery_assignments_v a, wsh_delivery_details d
WHERE a.delivery_id = p_delivery_id
AND a.delivery_id IS NOT NULL
AND a.delivery_detail_id = d.delivery_detail_id
AND d.container_flag = 'Y'
FOR UPDATE NOWAIT;
SELECT count(wddi.delivery_detail_id)
FROM wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi
WHERE wdai.delivery_interface_id = x_delivery_interface_id
AND wdai.delivery_interface_id IS NOT NULL
AND wdai.interface_action_code = G_TP_RELEASE_CODE
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wddi.interface_action_code = G_TP_RELEASE_CODE
AND wddi.container_flag = 'Y';
SELECT wddi.delivery_detail_interface_id
FROM wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi
WHERE wdai.delivery_interface_id = x_delivery_interface_id
AND wdai.delivery_interface_id IS NOT NULL
AND wdai.interface_action_code = G_TP_RELEASE_CODE
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wddi.interface_action_code = G_TP_RELEASE_CODE
AND wddi.delivery_detail_id = x_delivery_detail_id
AND wddi.container_flag = 'Y'
AND rownum = 1;
SELECT d.delivery_detail_id,
d.organization_id,
d.container_flag,
d.source_code,
d.released_status,
d.lpn_id
FROM wsh_delivery_assignments_v a, wsh_delivery_details d
WHERE a.parent_delivery_detail_id is NULL
AND a.delivery_id = p_delivery_id
AND a.delivery_id IS NOT NULL
AND a.delivery_detail_id = d.delivery_detail_id
AND d.released_status <> 'D'; -- Bug 4322654
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.container_flag = 'N'
AND wdd.released_status <> 'D' -- 4322654
AND wdd.delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH parent_delivery_detail_id = p_container_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id);
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.container_flag = 'N'
AND wdd.released_status = 'Y'
AND wdd.delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH parent_delivery_detail_id = p_container_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id);
l_working_used_details.DELETE(l_index);
l_working_used_details.DELETE(l_index);
l_wms_table.DELETE;
l_indexes.DELETE;
l_wms_table.DELETE;
l_indexes.DELETE;
l_wms_table.DELETE;
l_indexes.DELETE;
l_wms_table.DELETE;
l_indexes.DELETE;
SELECT ts.stop_id,
ts.physical_location_id,
ts.physical_stop_id,
ts.planned_arrival_date
FROM wsh_trip_stops ts
WHERE ts.trip_id = p_trip_id
AND ts.status_code = 'OP'
ORDER BY ts.stop_sequence_number;
SELECT l.delivery_id,
d.organization_id,
l.pick_up_stop_id,
l.drop_off_stop_id
FROM wsh_delivery_legs l,
wsh_new_deliveries d
WHERE l.pick_up_stop_id = p_stop_id
AND d.delivery_id = l.delivery_id
UNION
SELECT l.delivery_id,
d.organization_id,
l.pick_up_stop_id,
l.drop_off_stop_id
FROM wsh_delivery_legs l,
wsh_new_deliveries d
WHERE l.drop_off_stop_id = p_stop_id
AND d.delivery_id = l.delivery_id;
l_temp_unassigned_dels.delete;
SELECT name,
gross_weight,
net_weight,
weight_uom_code,
volume,
volume_uom_code,
customer_id,
intmed_ship_to_location_id,
fob_code,
freight_terms_code,
ship_method_code,
carrier_id,
service_level,
mode_of_transport,
vendor_id,
party_id,
shipping_control,
shipment_direction
FROM wsh_new_deliveries
WHERE delivery_id = x_delivery_id;
'create/update del will fail because grouping attrs could not be found for org',
p_plan_delivery_rec.organization_id);
SELECT name,
operator,
load_tender_status,
load_tender_number
FROM wsh_trips
WHERE trip_id = x_trip_id;
SELECT departure_gross_weight,
departure_net_weight,
weight_uom_code,
departure_volume,
volume_uom_code,
departure_fill_percent
FROM wsh_trip_stops
WHERE stop_id = x_stop_id;
PROCEDURE create_update_plan_trips(
p_phase IN NUMBER, -- 1 to create/update trips with NULL lane_id
-- 2 to update trips with lane_id populated
x_context IN OUT NOCOPY context_rec_type,
x_plan_trips IN OUT NOCOPY plan_trip_tab_type,
x_errors_tab IN OUT NOCOPY interface_errors_tab_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_PLAN_TRIPS';
l_trip_attrs.DELETE;
WSH_DEBUG_SV.log(l_module_name, 'create/update trips: l_index', l_index);
l_trip_in_rec.action_code := 'UPDATE';
l_message_name := 'WSH_TP_F_UPDATE_TRIP';
l_message_name := 'WSH_TP_F_UPDATE_LANE';
wsh_trips_grp.Create_Update_Trip(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_trip_info_tab => l_trip_attrs,
p_In_rec => l_trip_in_rec,
x_Out_Tab => l_trip_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'WSH_TP_RELEASE.create_update_plan_trips',
l_module_name);
END create_update_plan_trips;
PROCEDURE insert_interface_errors(
p_errors_tab IN interface_errors_tab_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE text_tab_type IS TABLE OF WSH_INTERFACE_ERRORS.ERROR_MESSAGE%TYPE INDEX BY BINARY_INTEGER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_INTERFACE_ERRORS';
SAVEPOINT before_insert;
INSERT INTO WSH_INTERFACE_ERRORS (
INTERFACE_ERROR_ID,
INTERFACE_ERROR_GROUP_ID,
INTERFACE_TABLE_NAME,
INTERFACE_ID,
INTERFACE_ACTION_CODE,
MESSAGE_NAME,
ERROR_MESSAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
WSH_INTERFACE_ERRORS_S.nextval,
l_groups(i),
l_table_names(i),
l_interface_ids(i),
G_TP_RELEASE_CODE,
l_message_names(i),
l_messages(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
'WSH_TP_RELEASE.insert_interface_errors',
l_module_name);
ROLLBACK TO before_insert;
END insert_interface_errors;
SELECT wti.tp_trip_number
FROM wsh_trips_interface wti
WHERE wti.group_id = p_group_id
AND wti.interface_action_code = G_TP_RELEASE_CODE
AND rownum = 1
;