VIEW: APPS.MST_TL_STOPS_V

Source

View Text - Preformatted

SELECT mts.plan_id , mts.stop_id , mts.stop_location_id , substr(mst_wb_util.get_name(mts.stop_location_id),1,80) , wl.city , wl.state , wl.postal_code , wl.country , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_arrival_date),1,40) , mts.planned_arrival_date , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_departure_date),1,40) , mts.planned_departure_date , (select nvl(sum(md.gross_weight),0) from mst_delivery_legs mdl , mst_deliveries md WHERE md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , (select nvl(sum(md.volume),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pallets),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pieces),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , mp.weight_uom , mp.volume_uom , (select nvl(sum(md.gross_weight),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , (select nvl(sum(md.volume),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pallets),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pieces),0) from mst_delivery_legs mdl , mst_deliveries md where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , mp.weight_uom , mp.volume_uom from mst_plans mp , mst_trip_stops mts , wsh_locations wl where mp.plan_id = mts.plan_id and mts.stop_location_id = wl.wsh_location_id

View Text - HTML Formatted

SELECT MTS.PLAN_ID
, MTS.STOP_ID
, MTS.STOP_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS.STOP_LOCATION_ID)
, 1
, 80)
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS.PLANNED_ARRIVAL_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, MTS.PLANNED_DEPARTURE_DATE
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_TRIP_STOPS MTS
, WSH_LOCATIONS WL
WHERE MP.PLAN_ID = MTS.PLAN_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID