eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.MST_OVER_UTIL_VEH_CAP_DEL_V

Source

View Text - Preformatted


SELECT md.plan_id , mt.trip_id , md.delivery_id , md.delivery_number , substr(mst_wb_util.get_name(md.pickup_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(md.dropoff_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.earliest_pickup_date),1,40) , md.earliest_pickup_date , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.latest_pickup_date),1,40) , md.latest_pickup_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.earliest_acceptable_date),1,40) , md.earliest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.latest_acceptable_date),1,40) , md.latest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.pickup_date),1,40) , md.pickup_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.dropoff_date),1,40) , md.dropoff_date , nvl(md.gross_weight, 0) , mp.weight_uom , nvl(md.volume, 0) , mp.volume_uom , nvl(md.number_of_pallets, 0) , nvl(md.number_of_pieces, 0) , (select count(mdd.delivery_detail_id) from mst_delivery_details mdd , mst_delivery_assignments mda WHERE mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id ) , (select nvl(sum(mdl1.allocated_transport_cost),0) + nvl(sum(mdl1.allocated_fac_shp_hand_cost),0) + nvl(sum(mdl1.allocated_fac_rec_hand_cost),0) + nvl(sum(mdl1.allocated_fac_loading_cost),0) + nvl(sum(mdl1.allocated_fac_unloading_cost),0) from mst_delivery_legs mdl1 where mdl1.plan_id = md.plan_id and mdl1.delivery_id = md.delivery_id ) , md.direct_cost , mp.currency_uom from mst_plans mp , mst_trips mt , mst_delivery_legs mdl , mst_deliveries md , wsh_locations wl1 , wsh_locations wl2 where mp.plan_id = mt.plan_id and mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and md.pickup_location_id = wl1.wsh_location_id and md.dropoff_location_id = wl2.wsh_location_id

View Text - HTML Formatted

SELECT MD.PLAN_ID
, MT.TRIP_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, MD.EARLIEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.LATEST_PICKUP_DATE)
, 1
, 40)
, MD.LATEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.EARLIEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.LATEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.PICKUP_DATE)
, 1
, 40)
, MD.PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.DROPOFF_DATE)
, 1
, 40)
, MD.DROPOFF_DATE
, NVL(MD.GROSS_WEIGHT
, 0)
, MP.WEIGHT_UOM
, NVL(MD.VOLUME
, 0)
, MP.VOLUME_UOM
, NVL(MD.NUMBER_OF_PALLETS
, 0)
, NVL(MD.NUMBER_OF_PIECES
, 0)
, (SELECT COUNT(MDD.DELIVERY_DETAIL_ID)
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID )
, (SELECT NVL(SUM(MDL1.ALLOCATED_TRANSPORT_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_SHP_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_REC_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_LOADING_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_UNLOADING_COST)
, 0)
FROM MST_DELIVERY_LEGS MDL1
WHERE MDL1.PLAN_ID = MD.PLAN_ID
AND MDL1.DELIVERY_ID = MD.DELIVERY_ID )
, MD.DIRECT_COST
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID