[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wti.trip_interface_id,
wti.group_id, --Bug7717569
wt.trip_id,
wti.tp_plan_name,
wt.tp_trip_number,
wti.planned_flag,
wt.planned_flag wsh_planned_flag,
wt.status_code wsh_status_code,
wt.name,
wti.vehicle_item_id,
wti.vehicle_organization_id,
wti.vehicle_num_prefix,
wti.vehicle_number,
wti.carrier_id carrier_id,
wt.ship_method_code,
wt.route_id,
wt.routing_instructions,
wti.service_level,
wti.mode_of_transport,
wti.freight_terms_code,
wt.seal_code,
wt.shipments_type_flag,
'N' wsh_ignore_for_planning,
wt.booking_number,
wt.vessel,
wt.voyage_number,
wt.port_of_loading,
wt.port_of_discharge,
wt.carrier_contact_id,
wt.shipper_wait_time,
wt.wait_time_uom,
wt.carrier_response,
wt.operator,
wti.vehicle_item_name,
wti.interface_action_code,
wt.attribute_category,
wt.attribute1,
wt.attribute2,
wt.attribute3,
wt.attribute4,
wt.attribute5,
wt.attribute6,
wt.attribute7,
wt.attribute8,
wt.attribute9,
wt.attribute10,
wt.attribute11,
wt.attribute12,
wt.attribute13,
wt.attribute14,
wt.attribute15
FROM wsh_trips_interface wti,
wsh_trips wt
WHERE wti.group_id = p_group_id --p_group_id is the input parameter
AND wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wt.tp_plan_name(+) = wti.tp_plan_name
ORDER BY wti.trip_interface_id;
SELECT wti.trip_interface_id,
wti.group_id, --Bug7717569
wt.trip_id,
wti.tp_plan_name,
wt.tp_trip_number,
wti.planned_flag,
wt.planned_flag wsh_planned_flag,
wt.status_code wsh_status_code,
wt.name,
wti.vehicle_item_id,
wti.vehicle_organization_id,
wti.vehicle_num_prefix,
wti.vehicle_number,
wti.carrier_id carrier_id,
wt.ship_method_code,
wt.route_id,
wt.routing_instructions,
wti.service_level,
wti.mode_of_transport,
wti.freight_terms_code,
wt.seal_code,
wt.shipments_type_flag,
'N' wsh_ignore_for_planning,
wt.booking_number,
wt.vessel,
wt.voyage_number,
wt.port_of_loading,
wt.port_of_discharge,
wt.carrier_contact_id,
wt.shipper_wait_time,
wt.wait_time_uom,
wt.carrier_response,
wt.operator,
wti.vehicle_item_name,
wti.interface_action_code,
wt.attribute_category,
wt.attribute1,
wt.attribute2,
wt.attribute3,
wt.attribute4,
wt.attribute5,
wt.attribute6,
wt.attribute7,
wt.attribute8,
wt.attribute9,
wt.attribute10,
wt.attribute11,
wt.attribute12,
wt.attribute13,
wt.attribute14,
wt.attribute15
FROM wsh_trips_interface wti,
wsh_trips wt
WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
AND wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wt.tp_plan_name(+) = wti.tp_plan_name
ORDER BY wti.trip_interface_id;
SELECT wti.trip_interface_id,
wti.group_id, --Bug7717569
wt.trip_id,
wti.tp_plan_name,
wt.tp_trip_number,
wti.planned_flag,
wt.planned_flag wsh_planned_flag,
wt.status_code wsh_status_code,
wt.name,
wti.vehicle_item_id,
wti.vehicle_organization_id,
wti.vehicle_num_prefix,
wti.vehicle_number,
wti.carrier_id carrier_id,
wt.ship_method_code,
wt.route_id,
wt.routing_instructions,
wti.service_level,
wti.mode_of_transport,
wti.freight_terms_code,
wt.seal_code,
wt.shipments_type_flag,
'N' wsh_ignore_for_planning,
wt.booking_number,
wt.vessel,
wt.voyage_number,
wt.port_of_loading,
wt.port_of_discharge,
wt.carrier_contact_id,
wt.shipper_wait_time,
wt.wait_time_uom,
wt.carrier_response,
wt.operator,
wti.vehicle_item_name,
wti.interface_action_code,
wt.attribute_category,
wt.attribute1,
wt.attribute2,
wt.attribute3,
wt.attribute4,
wt.attribute5,
wt.attribute6,
wt.attribute7,
wt.attribute8,
wt.attribute9,
wt.attribute10,
wt.attribute11,
wt.attribute12,
wt.attribute13,
wt.attribute14,
wt.attribute15
FROM wsh_trips_interface wti,
wsh_trips wt
WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wt.tp_plan_name(+) = wti.tp_plan_name
AND wti.group_id in
(SELECT wti.group_id FROM wsh_new_del_interface wndi,
wsh_del_legs_interface wdli,
wsh_trip_stops_interface wtsi,
wsh_trips_interface wti,
wsh_new_deliveries wnd
where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wdli.delivery_interface_id = wndi.delivery_interface_id
AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wnd.delivery_id = wndi.delivery_id
AND wndi.name >= nvl(p_del_name_low,wndi.name)
AND wndi.name <= nvl(p_del_name_high,wndi.name)
AND wnd.organization_id = NVL(l_organization_id,wnd.organization_id)
AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
ORDER BY wti.trip_interface_id;
SELECT wti.trip_interface_id,
wti.group_id, --Bug7717569
wt.trip_id,
wti.tp_plan_name,
wt.tp_trip_number,
wti.planned_flag,
wt.planned_flag wsh_planned_flag,
wt.status_code wsh_status_code,
wt.name,
wti.vehicle_item_id,
wti.vehicle_organization_id,
wti.vehicle_num_prefix,
wti.vehicle_number,
wti.carrier_id carrier_id,
wt.ship_method_code,
wt.route_id,
wt.routing_instructions,
wti.service_level,
wti.mode_of_transport,
wti.freight_terms_code,
wt.seal_code,
wt.shipments_type_flag,
'N' wsh_ignore_for_planning,
wt.booking_number,
wt.vessel,
wt.voyage_number,
wt.port_of_loading,
wt.port_of_discharge,
wt.carrier_contact_id,
wt.shipper_wait_time,
wt.wait_time_uom,
wt.carrier_response,
wt.operator,
wti.vehicle_item_name,
wti.interface_action_code,
wt.attribute_category,
wt.attribute1,
wt.attribute2,
wt.attribute3,
wt.attribute4,
wt.attribute5,
wt.attribute6,
wt.attribute7,
wt.attribute8,
wt.attribute9,
wt.attribute10,
wt.attribute11,
wt.attribute12,
wt.attribute13,
wt.attribute14,
wt.attribute15
FROM wsh_trips_interface wti,
wsh_trips wt
WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wt.tp_plan_name(+) = wti.tp_plan_name
ORDER BY wti.trip_interface_id;
SELECT wtsi.stop_interface_id,
wtsi.stop_id, -- will be populated after create or update, not from GC3
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,
wtsi.trip_interface_id,
wtsi.timezone_code,
'C' dml_action, -- indicates if stop has to be created or updated
null tp_attribute_category,
null tp_attribute1,
null tp_attribute2,
null tp_attribute3,
null tp_attribute4,
null tp_attribute5,
null tp_attribute6,
null tp_attribute7,
null tp_attribute8,
null tp_attribute9,
null tp_attribute10,
null tp_attribute11,
null tp_attribute12,
null tp_attribute13,
null tp_attribute14,
null tp_attribute15,
null attribute_category,
null attribute1,
null attribute2,
null attribute3,
null attribute4,
null attribute5,
null attribute6,
null attribute7,
null attribute8,
null attribute9,
null attribute10,
null attribute11,
null attribute12,
null attribute13,
null attribute14,
null attribute15
FROM wsh_trip_stops_interface wtsi,
wsh_trips_interface wti
WHERE wti.group_id = p_group_id --p_group_id is the input parameter
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
AND wti.interface_action_code = G_TMS_RELEASE_CODE
ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
SELECT wtsi.stop_interface_id,
wtsi.stop_id, -- will be populated after create or update, not from GC3
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,
wtsi.trip_interface_id,
wtsi.timezone_code,
'C' dml_action, -- indicates if stop has to be created or updated
null tp_attribute_category,
null tp_attribute1,
null tp_attribute2,
null tp_attribute3,
null tp_attribute4,
null tp_attribute5,
null tp_attribute6,
null tp_attribute7,
null tp_attribute8,
null tp_attribute9,
null tp_attribute10,
null tp_attribute11,
null tp_attribute12,
null tp_attribute13,
null tp_attribute14,
null tp_attribute15,
null attribute_category,
null attribute1,
null attribute2,
null attribute3,
null attribute4,
null attribute5,
null attribute6,
null attribute7,
null attribute8,
null attribute9,
null attribute10,
null attribute11,
null attribute12,
null attribute13,
null attribute14,
null attribute15
FROM wsh_trip_stops_interface wtsi,
wsh_trips_interface wti
WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
AND wti.interface_action_code = G_TMS_RELEASE_CODE
ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
SELECT wtsi.stop_interface_id,
wtsi.stop_id, -- will be populated after create or update, not from GC3
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,
wtsi.trip_interface_id,
wtsi.timezone_code,
'C' dml_action, -- indicates if stop has to be created or updated
null tp_attribute_category,
null tp_attribute1,
null tp_attribute2,
null tp_attribute3,
null tp_attribute4,
null tp_attribute5,
null tp_attribute6,
null tp_attribute7,
null tp_attribute8,
null tp_attribute9,
null tp_attribute10,
null tp_attribute11,
null tp_attribute12,
null tp_attribute13,
null tp_attribute14,
null tp_attribute15,
null attribute_category,
null attribute1,
null attribute2,
null attribute3,
null attribute4,
null attribute5,
null attribute6,
null attribute7,
null attribute8,
null attribute9,
null attribute10,
null attribute11,
null attribute12,
null attribute13,
null attribute14,
null attribute15
FROM wsh_trip_stops_interface wtsi,
wsh_trips_interface wti
WHERE wtsi.trip_interface_id = wti.trip_interface_id
AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
AND wti.interface_action_code = G_TMS_RELEASE_CODE
AND wti.group_id in
(SELECT wti.group_id FROM wsh_new_del_interface wndi,
wsh_del_legs_interface wdli,
wsh_trip_stops_interface wtsi,
wsh_trips_interface wti,
wsh_new_deliveries wnd
where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wdli.delivery_interface_id = wndi.delivery_interface_id
AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wnd.delivery_id = wndi.delivery_id
AND wndi.name >= nvl(p_del_name_low,wndi.name)
AND wndi.name <= nvl(p_del_name_high,wndi.name)
AND wnd.organization_id = NVL(p_organization_id,wnd.organization_id)
AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
SELECT wtsi.stop_interface_id,
wtsi.stop_id, -- will be populated after create or update, not from GC3
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,
wtsi.trip_interface_id,
wtsi.timezone_code,
'C' dml_action, -- indicates if stop has to be created or updated
null tp_attribute_category,
null tp_attribute1,
null tp_attribute2,
null tp_attribute3,
null tp_attribute4,
null tp_attribute5,
null tp_attribute6,
null tp_attribute7,
null tp_attribute8,
null tp_attribute9,
null tp_attribute10,
null tp_attribute11,
null tp_attribute12,
null tp_attribute13,
null tp_attribute14,
null tp_attribute15,
null attribute_category,
null attribute1,
null attribute2,
null attribute3,
null attribute4,
null attribute5,
null attribute6,
null attribute7,
null attribute8,
null attribute9,
null attribute10,
null attribute11,
null attribute12,
null attribute13,
null attribute14,
null attribute15
FROM wsh_trip_stops_interface wtsi,
wsh_trips_interface wti
WHERE wtsi.trip_interface_id = wti.trip_interface_id
AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
AND wti.interface_action_code = G_TMS_RELEASE_CODE
ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
SELECT wdli.delivery_leg_interface_id,
wdli.delivery_interface_id,
wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
wtsi_pu.trip_interface_id, -- trip_interface_id
wdli.delivery_leg_id,
wdli.delivery_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id,
wnd.weight_uom_code weight_uom,
wnd.volume_uom_code volume_uom,
wnd.organization_id, -- Organization id, used for deriving default UOM
wnd.tms_version_number, -- used before locking the delivery and rollback updates
wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
wnd.initial_pickup_location_id,-- used while logging exceptions
wnd.ultimate_dropoff_location_id,-- used for internal Locations
'N' processed_flag, -- used to indicate which deliveries have to be assigned
wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
FROM wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_trip_stops_interface wtsi_pu,
wsh_trip_stops_interface wtsi_do,
wsh_trips_interface wti,
wsh_new_deliveries wnd
WHERE wti.group_id = p_group_id --p_group_id is the input parameter
AND wti.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_pu.trip_interface_id = wti.trip_interface_id
AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_do.trip_interface_id = wti.trip_interface_id
AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
-- OTM R12 no need to have outer join
AND wnd.delivery_id = wdli.delivery_id
AND wndi.delivery_id = wdli.delivery_id
AND wdli.interface_action_code = G_TMS_RELEASE_CODE
--AND wnd.delivery_id(+) = wdli.delivery_id
--AND wnd.delivery_id = wndi.delivery_id
-- OTM R12
-- Fix Bug 5134725
--AND nvl(wnd.tms_version_number,-99) = nvl(wndi.tms_version_number,-99) -- version number check
ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
SELECT wdli.delivery_leg_interface_id,
wdli.delivery_interface_id,
wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
wtsi_pu.trip_interface_id, -- trip_interface_id
wdli.delivery_leg_id,
wdli.delivery_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id,
wnd.weight_uom_code weight_uom,
wnd.volume_uom_code volume_uom,
wnd.organization_id, -- Organization id, used for deriving default UOM
wnd.tms_version_number, -- used before locking the delivery and rollback updates
wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
wnd.initial_pickup_location_id,-- used while logging exceptions
wnd.ultimate_dropoff_location_id,-- used for internal Locations
'N' processed_flag, -- used to indicate which deliveries have to be assigned
wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
FROM wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_trip_stops_interface wtsi_pu,
wsh_trip_stops_interface wtsi_do,
wsh_trips_interface wti,
wsh_new_deliveries wnd
WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
AND wti.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_pu.trip_interface_id = wti.trip_interface_id
AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_do.trip_interface_id = wti.trip_interface_id
AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
-- OTM R12 no need to have outer join
AND wnd.delivery_id = wdli.delivery_id
AND wndi.delivery_id = wdli.delivery_id
AND wdli.interface_action_code = G_TMS_RELEASE_CODE
--AND wnd.delivery_id(+) = wdli.delivery_id
--AND wnd.delivery_id = wndi.delivery_id
-- OTM R12
-- Fix Bug 5134725
--AND nvl(wnd.tms_version_number,-99) = nvl(wndi.tms_version_number,-99) -- version number check
ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
SELECT wdli.delivery_leg_interface_id,
wdli.delivery_interface_id,
wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
wtsi_pu.trip_interface_id, -- trip_interface_id
wdli.delivery_leg_id,
wdli.delivery_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id,
wnd.weight_uom_code weight_uom,
wnd.volume_uom_code volume_uom,
wnd.organization_id, -- Organization id, used for deriving default UOM
wnd.tms_version_number, -- used before locking the delivery and rollback updates
wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
wnd.initial_pickup_location_id,-- used while logging exceptions
wnd.ultimate_dropoff_location_id,-- used for internal Locations
'N' processed_flag, -- used to indicate which deliveries have to be assigned
wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
FROM wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_trip_stops_interface wtsi_pu,
wsh_trip_stops_interface wtsi_do,
wsh_trips_interface wti,
wsh_new_deliveries wnd
WHERE wti.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_pu.trip_interface_id = wti.trip_interface_id
AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_do.trip_interface_id = wti.trip_interface_id
AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
-- OTM R12 no need to have outer join
AND wnd.delivery_id = wdli.delivery_id
AND wndi.delivery_id = wdli.delivery_id
AND wdli.interface_action_code = G_TMS_RELEASE_CODE
AND wti.group_id in
(SELECT wti.group_id FROM wsh_new_del_interface wndi,
wsh_del_legs_interface wdli,
wsh_trip_stops_interface wtsi,
wsh_trips_interface wti,
wsh_new_deliveries wnd
where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
AND wdli.delivery_interface_id = wndi.delivery_interface_id
AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wnd.delivery_id = wndi.delivery_id
AND wndi.name >= nvl(p_del_name_low,wndi.name)
AND wndi.name <= nvl(p_del_name_high,wndi.name)
AND wnd.organization_id = NVL(p_organization_id,wnd.organization_id)
AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
SELECT wdli.delivery_leg_interface_id,
wdli.delivery_interface_id,
wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
wtsi_pu.trip_interface_id, -- trip_interface_id
wdli.delivery_leg_id,
wdli.delivery_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id,
wnd.weight_uom_code weight_uom,
wnd.volume_uom_code volume_uom,
wnd.organization_id, -- Organization id, used for deriving default UOM
wnd.tms_version_number, -- used before locking the delivery and rollback updates
wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
wnd.initial_pickup_location_id,-- used while logging exceptions
wnd.ultimate_dropoff_location_id,-- used for internal Locations
'N' processed_flag, -- used to indicate which deliveries have to be assigned
wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
FROM wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_trip_stops_interface wtsi_pu,
wsh_trip_stops_interface wtsi_do,
wsh_trips_interface wti,
wsh_new_deliveries wnd
WHERE wti.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_pu.trip_interface_id = wti.trip_interface_id
AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi_do.trip_interface_id = wti.trip_interface_id
AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
-- OTM R12 no need to have outer join
AND wnd.delivery_id = wdli.delivery_id
AND wndi.delivery_id = wdli.delivery_id
AND wdli.interface_action_code = G_TMS_RELEASE_CODE
ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
SELECT stop_id,stop_location_id,stop_sequence_number,
physical_stop_id,physical_location_id,departure_gross_weight,departure_net_weight,
departure_volume,weight_uom_code,volume_uom_code,departure_seal_code,
departure_fill_percent,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM wsh_trip_stops
WHERE trip_id = p_trip_id;
SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = p_stop_id
AND wnd.delivery_id = wdl.delivery_id
UNION
SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE wdl.drop_off_stop_id = p_stop_id
AND wnd.delivery_id = wdl.delivery_id
UNION
SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts
WHERE wdl.drop_off_stop_id = wts.stop_id
AND wnd.delivery_id = wdl.delivery_id
AND wts.physical_stop_id = p_stop_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id;
SELECT freight_cost_interface_id,
freight_cost_id,
freight_cost_type_id,
freight_cost_type_code,
unit_amount,
uom,
total_amount,
currency_code,
delivery_id
FROM wsh_freight_costs_interface
WHERE delivery_id = p_delivery_id
AND interface_action_code = G_TMS_RELEASE_CODE;
SELECT wc.currency_code, wti.carrier_id
FROM wsh_trips_interface wti,
wsh_carriers wc
WHERE wti.trip_interface_id = p_trip_interface_id
AND wti.carrier_id = wc.carrier_id;
SELECT freight_cost_type_id
FROM wsh_freight_cost_types
--WHERE name = 'OTM Freight Cost'
WHERE name = c_name
AND freight_cost_type_code = c_fc_type_code;
SELECT wti.trip_interface_id,
wndi.delivery_interface_id,
wdli.delivery_leg_interface_id,
wtsi.stop_interface_id,
wfci.freight_cost_interface_id
from wsh_trips_interface wti ,
wsh_trip_stops_interface wtsi ,
wsh_del_legs_interface wdli ,
wsh_freight_costs_interface wfci,
wsh_new_del_interface wndi
where wti.trip_interface_id = c_trip_interface_id --Bug7717569
and wdli.delivery_interface_id = wndi.delivery_interface_id
and wndi.delivery_interface_id = wfci.delivery_interface_id (+)
and wndi.interface_action_code = 'TMS_RELEASE'
and wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
and wtsi.trip_interface_id = wti.trip_interface_id
FOR UPDATE NOWAIT;
SELECT fnd_date.chardt_to_date(p_del_pickup_date_low)
INTO l_del_pickup_date_low
FROM dual;
SELECT fnd_date.chardt_to_date(p_del_pickup_date_high)
INTO l_del_pickup_date_high
FROM dual;
l_unassigned_delivery_id_tab.DELETE;
l_unassigned_dlvy_version_tab.DELETE;
l_trip_info_tab.DELETE;
l_stop_info_tab.DELETE;
l_dleg_info_tab.DELETE;
l_dock_appt_tab.DELETE;
DELETE from wsh_interface_errors wie
WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
AND wie.interface_id in (
select wdli.delivery_interface_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_del_wti_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
AND wie.interface_action_code = G_TMS_RELEASE_CODE;
DELETE from WSH_EXCEPTIONS we
WHERE we.delivery_id in (
select wdli.delivery_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_del_wti_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and we.status = 'OPEN'
and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC');
l_dleg_local_tab.DELETE;
l_stop_local_tab.DELETE;
l_dlvy_id_tab.DELETE;
IF l_trip_info_tab(i).interface_action_code = G_TMS_DELETE_CODE THEN--{
DECLARE
l_delivery_tab WSH_TMS_RELEASE.delivery_tab;
END IF;--} -- interface_action_code is DELETE
l_in_rec.action_code := 'UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIPS_GRP.Create_Update_Trip:'||l_in_rec.action_code);
WSH_TRIPS_GRP.Create_Update_Trip(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => l_rs,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trip_info_tab => l_trip_input_tab,
p_In_rec => l_in_rec,
x_Out_tab => l_trip_output_tab);
WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Failed: l_return_status', l_return_status);
ELSIF l_in_rec.action_code = 'UPDATE' THEN -- trip_id is already known
l_trip_id := l_trip_info_tab(i).trip_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
l_stop_delete_tab WSH_UTIL_CORE.id_tab_type; -- stops to be deleted
l_stop_local_tab.DELETE;
l_del_attrs.DELETE;
l_stop_action_prms.action_code := 'DELETE';
WSH_DEBUG_SV.log(l_module_name, 'Delete Trip Stop Failed: l_return_status', l_return_status);
l_stop_attrs.DELETE;
l_stop_in_rec.action_code := 'UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
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_rs,
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, 'Create Update Trip Stop Failed: l_return_status', l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
SELECT wtsi.dock_name, wnd.organization_id, wtsi.start_time, wtsi.end_time
FROM wsh_del_legs_interface wdli,
wsh_new_del_interface wndi,
wsh_new_deliveries wnd,
wsh_trip_stops_interface wtsi,
wsh_trips_interface wti
WHERE wti.group_id = c_group_id
AND wti.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi.trip_interface_id = wti.trip_interface_id
AND wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
AND wnd.delivery_id = wdli.delivery_id
AND wndi.delivery_id = wdli.delivery_id
AND wdli.interface_action_code = G_TMS_RELEASE_CODE
AND wtsi.stop_interface_id = c_stop_interface_id;
select stop_id,stop_location_id,stop_sequence_number,
physical_stop_id,physical_location_id
from wsh_trip_stops
where stop_id = l_stop_local_tab(stop_rec).stop_id;
l_delivery_id_tab.DELETE;
l_del_attrs.DELETE;
l_del_attrs.DELETE;
l_del_index.DELETE;
l_unassigned_delivery_id_tab.DELETE(l_del_index(i_del));
l_unassigned_dlvy_version_tab.DELETE(l_del_index(i_del));
l_dleg_id_tab.DELETE;
DELETE FROM wsh_freight_costs
WHERE delivery_id = l_dleg_id_tab(k)
AND freight_cost_type_id = l_frcost_type_id;
DELETE FROM wsh_freight_costs
WHERE delivery_id = l_dlvy_id_tab(j)
AND freight_cost_type_id = l_frcost_type_id;
SELECT user_conversion_type
FROM gl_daily_conversion_types
WHERE conversion_type = p_curr_conv_type;
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs');
WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs
(p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_freight_info_tab => l_freight_info_tab,
p_in_rec => l_in_rec,
x_out_tab => l_out_tab,
x_return_status => l_rs,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.log(l_module_name, 'Create_Update_Freight_Costs Failed: l_return_status', l_return_status);
SELECT wts.stop_id stop_id
FROM wsh_trips wt,
wsh_trip_stops wts
WHERE wt.trip_id = c_trip_id
AND wt.trip_id = wts.trip_id
ORDER BY wts.stop_sequence_number DESC;
SELECT wnd.delivery_id, wdd.delivery_detail_id
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_delivery_assignments wda, wsh_delivery_details wdd
WHERE wdl.drop_off_stop_id = c_drop_off_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND WSH_UTIL_VALIDATE.CHECK_WMS_ORG(wnd.organization_id) = 'Y'
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
FOR UPDATE OF wnd.loading_sequence, wdd.load_seq_number NOWAIT;
l_del_tab.DELETE;
l_del_det_tab.DELETE;
UPDATE wsh_new_deliveries
SET loading_sequence = l_loading_sequence,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = l_del_tab(i);
UPDATE wsh_delivery_details
SET load_seq_number = l_loading_sequence,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_del_det_tab(i);
fnd_msg_pub.delete_msg();
WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
l_upd_dlvy_tms_tab(l_upd_dlvy_tms_tab.COUNT + 1) := l_dleg_local_tab(rec).tms_interface_flag; --update to the old status.
fnd_msg_pub.delete_msg();
l_delivery_info_tab.DELETE;
WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag
( p_delivery_id_tab => l_upd_dlvy_id_tab,
p_tms_interface_flag_tab => l_upd_dlvy_tms_tab,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name, 'update_tms_interface_flag : l_return_status : ', l_return_status);
SELECT wti.trip_interface_id
FROM wsh_trips_interface wti
WHERE wti.tp_plan_name = p_tp_plan_name
AND wti.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_freight_costs_interface wfci
where wfci.delivery_interface_id in (
select wdli.delivery_interface_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and wfci.interface_action_code = G_TMS_RELEASE_CODE;
DELETE from WSH_EXCEPTIONS we
WHERE we.delivery_id in (
select wdli.delivery_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and we.status = 'OPEN'
and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC');
UPDATE WSH_EXCEPTIONS we
SET status = 'CLOSED'
WHERE we.delivery_id in (
select wdli.delivery_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC')
and we.status = 'OPEN';
Delete from wsh_interface_errors wie
where wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
and wie.interface_id in (
select wdli.delivery_interface_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and wie.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_interface_errors wie
where wie.interface_table_name = 'WSH_TRIP_STOPS_INTERFACE'
and wie.interface_id in (
select wtsi.stop_interface_id
from wsh_trip_stops_interface wtsi
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.interface_action_code = G_TMS_RELEASE_CODE)
and wie.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_interface_errors wie
where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
and wie.interface_id = l_trip_interface_id_tab(i)
and wie.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_new_del_interface wndi
where wndi.delivery_interface_id in (
select wdli.delivery_interface_id
from wsh_trip_stops_interface wtsi,
wsh_del_legs_interface wdli
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
and wtsi.interface_action_code = G_TMS_RELEASE_CODE
and wdli.interface_action_code = G_TMS_RELEASE_CODE)
and wndi.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_del_legs_interface wdli
where wdli.pick_up_stop_interface_id in (
select wtsi.stop_interface_id
from wsh_trip_stops_interface wtsi
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.interface_action_code = G_TMS_RELEASE_CODE)
and wdli.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_trip_stops_interface wtsi
where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
and wtsi.interface_action_code = G_TMS_RELEASE_CODE;
Delete from wsh_trips_interface wti
where wti.trip_interface_id = l_trip_interface_id_tab(i)
and wti.INTERFACE_ACTION_CODE = G_TMS_RELEASE_CODE;
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,
p_group_id, --l_groups(i),
'WSH_NEW_DEL_INTERFACE', --l_table_names(i),
l_dlvy_id_tab(i),--l_interface_ids(i),
G_TMS_RELEASE_CODE,
l_message_name,
l_message,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
SELECT wdl.delivery_id,
wnd.tms_version_number,
wnd.organization_id,
wnd.name,
wnd.status_code
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = p_trip_id
AND wdl.delivery_id = wnd.delivery_id(+);
SELECT wdl.delivery_id,
wnd.tms_version_number,
wnd.organization_id,
wnd.name,
wnd.status_code
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wt.trip_id
AND wt.tp_plan_name = p_tp_plan_name
AND wdl.delivery_id = wnd.delivery_id(+);
SELECT wdl.delivery_leg_id,
wdl.pick_up_stop_id pick_up_stop_id,
wdl.drop_off_stop_id drop_off_stop_id,
wts_pu.stop_location_id pickup_stop_location_id,
wts_pu.stop_sequence_number pickup_stop_sequence,
wts_do.stop_location_id dropoff_stop_location_id,
wts_do.stop_sequence_number dropoff_stop_sequence,
wts_pu.trip_id trip_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts_pu,
wsh_trip_stops wts_do
WHERE wdl.delivery_id = p_delivery_id
AND wdl.pick_up_stop_id = wts_pu.stop_id
AND wdl.drop_off_stop_id = wts_do.stop_id ;
l_del_attrs.DELETE;
l_update_stop VARCHAR2(1);
l_update_stop := 'Y';
l_update_stop := 'N';
WSH_DEBUG_SV.logmsg(l_module_name, 'L_UPDATE_STOP FLAG:'||l_update_stop);
IF l_update_stop = 'Y' THEN--{
-- Update the Stop Location for current stop to match delivery location
-- x_stop_tab matches l_stop_tab
--l_stop_tab(i).stop_location_id := l_dropoff_location_id;
ELSE -- l_update_stop = N, when other deliveries use physical location
-- Assign current Stop record to the new stop table
l_index := l_new_stop_tab.count + 1;
FOR stop_update IN x_stop_tab.FIRST..x_stop_tab.LAST
LOOP
IF x_stop_tab(stop_update).stop_sequence_number >= l_stop_tab(i).stop_sequence_number
THEN
-- This will ensure dummy and physical stops are created together
-- Physical and other higher stops (with higher SSN are pushed out by 1)
x_stop_tab(stop_update).stop_sequence_number
:= x_stop_tab(stop_update).stop_sequence_number + 1;
END IF;--} -- l_update_stop condition
SELECT upgrade_tz_id
FROM fnd_timezones_b
WHERE timezone_code = c_timezone_code;