Thursday, May 6, 2010

Query relating Delivery legs and its Trips ( R12 )

Below query gives you the info about the DELIVERY LEGS and its associated TRIPS based on delivery id.

I have tested this query in R12.1.1.


SELECT wt.trip_id
      ,wt.name
      ,wt.status_code
      ,wt.vehicle_item_id
      ,wt.vehicle_number
      ,wt.carrier_id
      ,wt.ship_method_code
      ,wtp.stop_id                 pick_stop_id
      ,wtp.stop_location_id        pick_stop_location_id
      ,wtp.status_code             pick_status_code
      ,wtp.stop_sequence_number    pick_stop_sequence_number
      ,wtp.planned_arrival_date    pick_planned_arrival_date
      ,wtp.planned_departure_date  pick_planned_departure_date
      ,wtp.actual_arrival_date     pick_actual_arrival_date
      ,wtp.actual_departure_date   pick_actual_departure_date
      ,wtp.departure_net_weight    pick_departure_net_weight
      ,wtp.weight_uom_code         pick_weight_uom_code
      ,wtd.stop_id                 drop_stop_id
      ,wtd.stop_location_id        drop_stop_location_id
      ,wtd.status_code             drop_status_code
      ,wtd.stop_sequence_number    drop_stop_sequence_number
      ,wtd.planned_arrival_date    drop_planned_arrival_date
      ,wtd.planned_departure_date  drop_planned_departure_date
      ,wtd.actual_arrival_date     drop_actual_arrival_date
      ,wtd.actual_departure_date   drop_actual_departure_date
      ,wtd.departure_net_weight    drop_departure_net_weight
      ,wtd.weight_uom_code         drop_weight_uom_code
      ,wdl.delivery_leg_id
      ,wdl.delivery_id
      ,wdl.sequence_number
      ,wdl.pick_up_stop_id
      ,wdl.drop_off_stop_id
      ,wdl.shipper_title
      ,wdl.shipper_phone
      ,wdl.delivered_quantity
      ,wdl.loaded_quantity
      ,wdl.received_quantity
FROM   wsh_trips wt
      ,wsh_trip_stops wtp
      ,wsh_trip_stops wtd
      ,wsh_delivery_legs wdl
WHERE wt.trip_id   =wtp.trip_id
AND   wt.trip_id     =wtd.trip_id
AND   wtp.stop_id    =wdl.pick_up_stop_id
AND   wtd.stop_id    =wdl.drop_off_stop_id
AND   wdl.delivery_id='&delivery_id'; 

1 Responses to “Query relating Delivery legs and its Trips ( R12 )”

Anonymous said...
August 4, 2015 at 8:10 PM

Thanks much!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.