SELECT
wt.trip_id,
to_char(wt.trip_id) trip_id_chr,
wt.name,
wt.status_code trip_status,
wpt.name Prior_Trip,
wdi.sequence_number mbol_number,
wsh_util_core.get_item_name(msi.inventory_item_id,
msi.organization_id) vehicle_type,
wt.vehicle_number,
hp.party_name carrier_name,
wt.routing_instructions,
wt.mode_of_transport,
wt.service_level,
wts.stop_id,
to_char(wts.stop_id) stop_id_chr,
wts.stop_sequence_number,
wts.stop_location_id,
wl.address1,
wl.address2,
wl.address3,
wl.address4,
wl.city,
nvl(nvl(wl.province,wl.state),wl.county) region,
wl.postal_code,
wl.country,
wts.status_code,
wts.departure_seal_code seal_code,
wts.status_code Stop_status,
wts.departure_gross_weight,
wts.departure_net_weight,
wts.weight_uom_code weight_uom,
wts.departure_volume,
wts.volume_uom_code volume_uom,
FND_DATE.DATE_TO_CANONICAL(wts.planned_arrival_date) planned_arrival_date,
FND_DATE.DATE_TO_CANONICAL(wts.actual_arrival_date) actual_arrival_date,
FND_DATE.DATE_TO_CANONICAL(wts.planned_departure_date) planned_departure_date,
FND_DATE.DATE_TO_CANONICAL(wts.actual_departure_date) actual_departure_date,
wc.scac_code scac_code,
wt.attribute1 trip_attribute1,
wt.attribute2 trip_attribute2,
wt.attribute3 trip_attribute3,
wt.attribute4 trip_attribute4,
wt.attribute5 trip_attribute5,
wt.attribute6 trip_attribute6,
wt.attribute7 trip_attribute7,
wt.attribute8 trip_attribute8,
wt.attribute9 trip_attribute9,
wt.attribute10 trip_attribute10,
wt.attribute11 trip_attribute11,
wt.attribute12 trip_attribute12,
wt.attribute13 trip_attribute13,
wt.attribute14 trip_attribute14,
wt.attribute15 trip_attribute15,
WSH_WSHRDMBL_XMLP_PKG.cf_shipper_nameformula(wts.stop_id) CF_SHIPPER_NAME,
WSH_WSHRDMBL_XMLP_PKG.cf_trip_statusformula(wt.status_code) CF_Trip_status,
WSH_WSHRDMBL_XMLP_PKG.cf_mode_meanformula(wt.mode_of_transport) CF_Mode_mean,
WSH_WSHRDMBL_XMLP_PKG.cf_service_levelformula(wt.service_level) CF_Service_level,
WSH_WSHRDMBL_XMLP_PKG.cf_address_trips_stopsformula(wl.address1, wl.address2, wl.address3, wl.address4) CF_address_trips_stops,
WSH_WSHRDMBL_XMLP_PKG.cf_stop_statusformula(wts.status_code) CF_Stop_status,
WSH_WSHRDMBL_XMLP_PKG.cf_weight_uomformula(wts.weight_uom_code) CF_Weight_Uom,
WSH_WSHRDMBL_XMLP_PKG.cf_volume_uomformula(wts.volume_uom_code) CF_Volume_uom
FROM
wsh_trips wt,
wsh_trips wpt,
wsh_document_instances wdi,
hz_parties hp,
mtl_system_items msi,
wsh_trip_stops wts,
wsh_locations wl,
wsh_carriers wc
WHERE
wt.arrive_after_trip_id = wpt.trip_id(+)
AND wt.trip_id = wts.trip_id
AND wt.carrier_id = hp.party_id
AND wt.trip_id = wdi.entity_id
AND wdi.entity_name = 'WSH_TRIPS'
AND wt.vehicle_item_id = msi.inventory_item_id(+)
AND wts.stop_location_id=wl.wsh_location_id
AND wt.vehicle_organization_id = msi.organization_id(+)
AND wt.trip_id = :P_TRIP_ID
AND wt.carrier_id = wc.carrier_id
order by wts.stop_sequence_number |