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_LTL_PARC_V

Source

View Text - Preformatted


SELECT mp.plan_id , md.customer_id , md.supplier_id , flp.facility_id , wlo.owner_type , wc.freight_code , wcs.service_level , mt.trip_number , mst_wb_util.get_phone_number(flp.location_id) , 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 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 ,( 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_name(wl.wsh_location_id),1,80) , wl.address1 , wl.city , wl.state , wl.postal_code , mst_wb_util.Get_Contact_Name(wl.wsh_location_id) , mst_wb_util.get_phone_number(wl.wsh_location_id) , (select sum(md.number_of_pieces) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select sum(md.gross_weight) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select sum(md.volume) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select sum(nvl(mdl.allocated_fac_loading_cost,0) + nvl(mdl.allocated_transport_cost,0) + nvl(mdl.allocated_fac_shp_hand_cost,0)) from mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , mdd.tp_refer_header_number , mdd.source_header_number , mst_reports_pkg.get_freight_classes_per_order(mp.plan_id, mdd.source_header_number) , wc.freight_code , wcs.service_level , mt.trip_number , mst_wb_util.get_phone_number(flp.location_id) , 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 ,( 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) , (select sum(md.number_of_pieces) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select sum(md.gross_weight) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select sum(md.volume) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , 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 , fte_location_parameters flp , wsh_location_owners wlo , wsh_carriers wc , wsh_carrier_services wcs , wsh_locations wl where md.plan_id = mp.plan_id and 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 or mdl.drop_off_stop_id = mts.stop_id) and mdl.trip_id = mts.trip_id and mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and (mt.mode_of_transport = 'LTL' or mt.mode_of_transport = 'PARCEL') 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 mts.stop_location_id = flp.location_id and wlo.wsh_location_id = flp.location_id and decode(wlo.owner_type,2,mt.destination_location_id,mt.origin_location_id) = mts.stop_location_id and wc.carrier_id = mt.carrier_id and wcs.carrier_service_id = mt.carrier_service_id and wl.wsh_location_id = mt.destination_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
, WCS.SERVICE_LEVEL
, MT.TRIP_NUMBER
, MST_WB_UTIL.GET_PHONE_NUMBER(FLP.LOCATION_ID)
, 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 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
, ( 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_NAME(WL.WSH_LOCATION_ID)
, 1
, 80)
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, MST_WB_UTIL.GET_CONTACT_NAME(WL.WSH_LOCATION_ID)
, MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
, (SELECT SUM(MD.NUMBER_OF_PIECES)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT SUM(MD.GROSS_WEIGHT)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT SUM(MD.VOLUME)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT SUM(NVL(MDL.ALLOCATED_FAC_LOADING_COST
, 0) + NVL(MDL.ALLOCATED_TRANSPORT_COST
, 0) + NVL(MDL.ALLOCATED_FAC_SHP_HAND_COST
, 0))
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
, MST_REPORTS_PKG.GET_FREIGHT_CLASSES_PER_ORDER(MP.PLAN_ID
, MDD.SOURCE_HEADER_NUMBER)
, WC.FREIGHT_CODE
, WCS.SERVICE_LEVEL
, MT.TRIP_NUMBER
, MST_WB_UTIL.GET_PHONE_NUMBER(FLP.LOCATION_ID)
, 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
, ( 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)
, (SELECT SUM(MD.NUMBER_OF_PIECES)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT SUM(MD.GROSS_WEIGHT)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT SUM(MD.VOLUME)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, 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
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
, WSH_CARRIERS WC
, WSH_CARRIER_SERVICES WCS
, WSH_LOCATIONS WL
WHERE MD.PLAN_ID = MP.PLAN_ID
AND 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 OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND (MT.MODE_OF_TRANSPORT = 'LTL' OR MT.MODE_OF_TRANSPORT = 'PARCEL')
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 MTS.STOP_LOCATION_ID = FLP.LOCATION_ID
AND WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND DECODE(WLO.OWNER_TYPE
, 2
, MT.DESTINATION_LOCATION_ID
, MT.ORIGIN_LOCATION_ID) = MTS.STOP_LOCATION_ID
AND WC.CARRIER_ID = MT.CARRIER_ID
AND WCS.CARRIER_SERVICE_ID = MT.CARRIER_SERVICE_ID
AND WL.WSH_LOCATION_ID = MT.DESTINATION_LOCATION_ID