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.

VIEW: APPS.MST_CM_IMPROPER_TIME_LOAD_V

Source

View Text - Preformatted


SELECT mt.plan_id , mt.trip_id , mt.mode_of_transport , mt.trip_number , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , nvl(sum(mdd.gross_weight),0) , mp.weight_uom , nvl(sum(mdd.volume),0) , mp.volume_uom , nvl(sum(mdd.number_of_pallets),0) , nvl(sum(mdd.requested_quantity),0) , nvl(count(distinct mdd.source_code||mdd.source_header_number),0) , mt.total_trip_distance , mp.distance_uom , substr(mst_wb_util.get_local_chardtzone(mts1.stop_location_id, mts1.planned_departure_date),1,40) , mts1.planned_departure_date , substr(mst_wb_util.get_local_chardtzone(mts2.stop_location_id, mts2.planned_arrival_date),1,40) , mts2.planned_arrival_date from mst_plans mp , mst_trips mt , mst_trip_stops mts1 , mst_trip_stops mts2 , mst_delivery_legs mdl , mst_deliveries md , mst_delivery_assignments mda , mst_delivery_details mdd , 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.plan_id = mda.plan_id (+) and md.delivery_id = mda.delivery_id (+) and mda.plan_id = mdd.plan_id (+) and mda.delivery_detail_id = mdd.delivery_detail_id (+) and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mts1.plan_id = mt.plan_id and mts1.trip_id = mt.trip_id and mts1.stop_sequence_number = 1 and mts2.plan_id = mt.plan_id and mts2.trip_id = mt.trip_id and mts2.stop_sequence_number = (select max(stop_sequence_number) from mst_trip_stops where plan_id = mt.plan_id and trip_id = mt.trip_id) group by mt.plan_id , mt.trip_id , mt.mode_of_transport , mt.trip_number , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , mp.weight_uom , mp.volume_uom , mt.total_trip_distance , mp.distance_uom , substr(mst_wb_util.get_local_chardtzone(mts1.stop_location_id, mts1.planned_departure_date),1,40) , mts1.planned_departure_date , substr(mst_wb_util.get_local_chardtzone(mts2.stop_location_id, mts2.planned_arrival_date),1,40) , mts2.planned_arrival_date

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, NVL(SUM(MDD.GROSS_WEIGHT)
, 0)
, MP.WEIGHT_UOM
, NVL(SUM(MDD.VOLUME)
, 0)
, MP.VOLUME_UOM
, NVL(SUM(MDD.NUMBER_OF_PALLETS)
, 0)
, NVL(SUM(MDD.REQUESTED_QUANTITY)
, 0)
, NVL(COUNT(DISTINCT MDD.SOURCE_CODE||MDD.SOURCE_HEADER_NUMBER)
, 0)
, MT.TOTAL_TRIP_DISTANCE
, MP.DISTANCE_UOM
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, MTS1.PLANNED_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS2.PLANNED_ARRIVAL_DATE
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
, 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.PLAN_ID = MDA.PLAN_ID (+)
AND MD.DELIVERY_ID = MDA.DELIVERY_ID (+)
AND MDA.PLAN_ID = MDD.PLAN_ID (+)
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID (+)
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MTS1.PLAN_ID = MT.PLAN_ID
AND MTS1.TRIP_ID = MT.TRIP_ID
AND MTS1.STOP_SEQUENCE_NUMBER = 1
AND MTS2.PLAN_ID = MT.PLAN_ID
AND MTS2.TRIP_ID = MT.TRIP_ID
AND MTS2.STOP_SEQUENCE_NUMBER = (SELECT MAX(STOP_SEQUENCE_NUMBER)
FROM MST_TRIP_STOPS
WHERE PLAN_ID = MT.PLAN_ID
AND TRIP_ID = MT.TRIP_ID) GROUP BY MT.PLAN_ID
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MT.TOTAL_TRIP_DISTANCE
, MP.DISTANCE_UOM
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, MTS1.PLANNED_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS2.PLANNED_ARRIVAL_DATE