Tuesday, 13 January 2015

Query to find delivery leg and pick up stop info :

SELECT   wt.trip_id,
         wt.name,
         wt.STATUS_CODE,
         wt.VEHICLE_ITEM_ID,
         wt.VEHICLE_NUMBER,
         wt.CARRIER_ID,
         wt.SHIP_METHOD_CODE,
         wts.STOP_ID,
         wts.STOP_LOCATION_ID,
         wts.STATUS_CODE,
         wts.STOP_SEQUENCE_NUMBER,
         wts.PLANNED_ARRIVAL_DATE,
         wts.PLANNED_DEPARTURE_DATE,
         wts.ACTUAL_ARRIVAL_DATE,
         wts.ACTUAL_DEPARTURE_DATE,
         wts.DEPARTURE_NET_WEIGHT,
         wts.WEIGHT_UOM_CODE,
         wdl.DELIVERY_LEG_ID,
         wdl.DELIVERY_ID,
         wdl.PICK_UP_STOP_ID,
         wdl.DROP_OFF_STOP_ID,
         wdl.SEQUENCE_NUMBER,
         wdl.LOADING_ORDER_FLAG,
         wdl.SHIPPER_TITLE,
         wdl.SHIPPER_PHONE
  FROM   wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl
 WHERE       wdl.delivery_id = 12814
         AND wts.stop_id = wdl.pick_up_stop_id
         AND wts.trip_id = wt.trip_id;

Query to find Bill of Lading info of the Delivery :

SELECT   wnd.delivery_id delivery_id,
         wdi.sequence_number bol_number,
         wdi.bol_notify_party,
         wdi.port_of_loading,
         wdi.port_of_discharge,
         wnd.WAYBILL waybill,
         wnd.GROSS_WEIGHT gross_weight,
         wnd.WEIGHT_UOM_CODE uom,
         wnd.status_code
  FROM   wsh_new_deliveries wnd,
         wsh_delivery_legs wdl,
         wsh_document_instances wdi
 WHERE       wnd.delivery_id = 12784
         AND wnd.delivery_id = wdl.delivery_id(+)
         AND wdi.entity_id(+) = wdl.delivery_leg_id
         AND wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
         AND wdi.document_type(+) = 'BOL'
         AND wdi.status(+) <> 'CANCELLED'
/

No comments:

Post a Comment