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_R_FREIGHT_TL_V

Source

View Text - Preformatted


SELECT mp.plan_id , md.customer_id , md.supplier_id , flp.facility_id , wlo.owner_type , wc.freight_code , mt.service_level , mst_wb_util.get_phone_number(wlo.wsh_location_id) , mt.trip_number , msi.concatenated_segments vehicle , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id ,( select max ( md1.earliest_acceptable_date ) from mst_deliveries md1 , mst_delivery_legs mdl1 WHERE md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and md1.plan_id = mts.plan_id and mdl1.plan_id = mts.plan_id and mdl1.trip_id = mts.trip_id and mdl1.drop_off_stop_id = mts.stop_id and md1.dropoff_location_id = mts.stop_location_id )),1,20) , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id,mts.planned_arrival_date),1,20) , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id ,( select min ( md1.latest_acceptable_date ) from mst_deliveries md1 , mst_delivery_legs mdl1 where md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and md1.plan_id = mts.plan_id and mdl1.plan_id = mts.plan_id and mdl1.trip_id = mts.trip_id and mdl1.drop_off_stop_id = mts.stop_id and md1.dropoff_location_id = mts.stop_location_id )),1,20) , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id ,( select max ( md1.earliest_pickup_date ) from mst_deliveries md1 , mst_delivery_legs mdl1 where md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and md1.plan_id = mts.plan_id and mdl1.plan_id = mts.plan_id and mdl1.trip_id = mts.trip_id and mdl1.pick_up_stop_id = mts.stop_id and md1.pickup_location_id = mts.stop_location_id )),1,20) , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id,mts.planned_departure_date),1,20) , substr(mst_wb_util.get_local_chardt(wlo.wsh_location_id ,( select min ( md1.latest_pickup_date ) from mst_deliveries md1 , mst_delivery_legs mdl1 where md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and md1.plan_id = mts.plan_id and mdl1.plan_id = mts.plan_id and mdl1.trip_id = mts.trip_id and mdl1.pick_up_stop_id = mts.stop_id and md1.pickup_location_id = mts.stop_location_id )),1,20) , (select nvl(sum(md1.number_of_pieces),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.drop_off_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , (select nvl(sum(md1.gross_weight),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.drop_off_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , (select nvl(sum(md1.volume),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.drop_off_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , (select nvl(sum(md1.number_of_pieces),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , (select nvl(sum(md1.gross_weight),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , (select nvl(sum(md1.volume),0) from mst_trip_stops mts1 , mst_delivery_legs mdl1 , mst_deliveries md1 , fte_location_parameters flp1 where mts1.plan_id = mts.plan_id and mts1.trip_id = mts.trip_id and mts1.stop_location_id = flp1.location_id and mdl1.plan_id = mts1.plan_id and mdl1.trip_id = mts1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id and md1.plan_id = mdl1.plan_id and md1.delivery_id = mdl1.delivery_id and flp1.facility_id = flp.facility_id) , DECODE(mts.stop_id,mdl.pick_up_stop_id,'L','U') , mdd.tp_refer_header_number , mdd.source_header_number from mst_plans mp , mst_trips mt , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , mst_delivery_assignments mda , mst_delivery_details mdd , wsh_carriers wc , mtl_system_items_kfv msi , fte_vehicle_types fvt , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_location_owners wlo where mp.plan_id = mt.plan_id and mts.plan_id = mp.plan_id and mts.trip_id = mt.trip_id and mts.stop_location_id = flp.location_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id) and md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mda.delivery_detail_id = mdd.delivery_detail_id and mdd.plan_id = mda.plan_id and wc.carrier_id = mt.carrier_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mt.vehicle_type_id = fvt.vehicle_type_id and msi.inventory_item_id = fvt.inventory_item_id and msi.organization_id = fvt.organization_id and mt.mode_of_transport = 'TRUCK' and wl1.wsh_location_id = flp1.location_id and wl2.wsh_location_id = flp2.location_id and wlo.wsh_location_id = flp.location_id

View Text - HTML Formatted

SELECT MP.PLAN_ID
, MD.CUSTOMER_ID
, MD.SUPPLIER_ID
, FLP.FACILITY_ID
, WLO.OWNER_TYPE
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, MST_WB_UTIL.GET_PHONE_NUMBER(WLO.WSH_LOCATION_ID)
, MT.TRIP_NUMBER
, MSI.CONCATENATED_SEGMENTS VEHICLE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, ( SELECT MAX ( MD1.EARLIEST_ACCEPTABLE_DATE )
FROM MST_DELIVERIES MD1
, MST_DELIVERY_LEGS MDL1
WHERE MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND MD1.PLAN_ID = MTS.PLAN_ID
AND MDL1.PLAN_ID = MTS.PLAN_ID
AND MDL1.TRIP_ID = MTS.TRIP_ID
AND MDL1.DROP_OFF_STOP_ID = MTS.STOP_ID
AND MD1.DROPOFF_LOCATION_ID = MTS.STOP_LOCATION_ID ))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, ( SELECT MIN ( MD1.LATEST_ACCEPTABLE_DATE )
FROM MST_DELIVERIES MD1
, MST_DELIVERY_LEGS MDL1
WHERE MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND MD1.PLAN_ID = MTS.PLAN_ID
AND MDL1.PLAN_ID = MTS.PLAN_ID
AND MDL1.TRIP_ID = MTS.TRIP_ID
AND MDL1.DROP_OFF_STOP_ID = MTS.STOP_ID
AND MD1.DROPOFF_LOCATION_ID = MTS.STOP_LOCATION_ID ))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, ( SELECT MAX ( MD1.EARLIEST_PICKUP_DATE )
FROM MST_DELIVERIES MD1
, MST_DELIVERY_LEGS MDL1
WHERE MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND MD1.PLAN_ID = MTS.PLAN_ID
AND MDL1.PLAN_ID = MTS.PLAN_ID
AND MDL1.TRIP_ID = MTS.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS.STOP_ID
AND MD1.PICKUP_LOCATION_ID = MTS.STOP_LOCATION_ID ))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WLO.WSH_LOCATION_ID
, ( SELECT MIN ( MD1.LATEST_PICKUP_DATE )
FROM MST_DELIVERIES MD1
, MST_DELIVERY_LEGS MDL1
WHERE MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND MD1.PLAN_ID = MTS.PLAN_ID
AND MDL1.PLAN_ID = MTS.PLAN_ID
AND MDL1.TRIP_ID = MTS.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS.STOP_ID
AND MD1.PICKUP_LOCATION_ID = MTS.STOP_LOCATION_ID ))
, 1
, 20)
, (SELECT NVL(SUM(MD1.NUMBER_OF_PIECES)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.DROP_OFF_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, (SELECT NVL(SUM(MD1.GROSS_WEIGHT)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.DROP_OFF_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, (SELECT NVL(SUM(MD1.VOLUME)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.DROP_OFF_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, (SELECT NVL(SUM(MD1.NUMBER_OF_PIECES)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, (SELECT NVL(SUM(MD1.GROSS_WEIGHT)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, (SELECT NVL(SUM(MD1.VOLUME)
, 0)
FROM MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
, MST_DELIVERIES MD1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MTS.TRIP_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MDL1.PLAN_ID = MTS1.PLAN_ID
AND MDL1.TRIP_ID = MTS1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID
AND MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID
AND FLP1.FACILITY_ID = FLP.FACILITY_ID)
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, 'L'
, 'U')
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_KFV MSI
, FTE_VEHICLE_TYPES FVT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOCATION_OWNERS WLO
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MTS.PLAN_ID = MP.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MTS.STOP_LOCATION_ID = FLP.LOCATION_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
AND MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND WC.CARRIER_ID = MT.CARRIER_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID
AND MT.MODE_OF_TRANSPORT = 'TRUCK'
AND WL1.WSH_LOCATION_ID = FLP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = FLP2.LOCATION_ID
AND WLO.WSH_LOCATION_ID = FLP.LOCATION_ID