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_POOL_ACTIVITY_DECONS_V

Source

View Text - Preformatted


SELECT distinct mt_in.plan_id , wlo.owner_type , flp.facility_id , mt_in.carrier_id , wc.freight_code , wcs.service_level , mst_wb_util.get_contact_info(mt_in.carrier_id, 'PHONE', 3) , mt_in.trip_number , substr(mst_wb_util.get_local_chardt(mts_in.stop_location_id, mts_in.planned_arrival_date),1,20) , mts_in.planned_arrival_date , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md WHERE md.plan_id = mt_in.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = in_leg.plan_id and mdl.trip_id = in_leg.trip_id and mdl.drop_off_Stop_id = in_leg.drop_off_stop_id)) , (select nvl(sum(md.gross_weight),0) from mst_deliveries md where md.plan_id = mt_in.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = in_leg.plan_id and mdl.trip_id = in_leg.trip_id and mdl.drop_off_Stop_id = in_leg.drop_off_stop_id)) , (select sum(nvl(md.volume,0)) from mst_deliveries md where md.plan_id = mt_in.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = in_leg.plan_id and mdl.trip_id = in_leg.trip_id and mdl.drop_off_Stop_id = in_leg.drop_off_stop_id)) , wc1.freight_code , wcs1.service_level , mst_wb_util.get_contact_info(mt_out.carrier_id, 'PHONE', 3) , mt_out.trip_number , mst_wb_util.r_get_company_name(wlo_dest.wsh_location_id, wlo_dest.owner_type) , wl.location_code , mst_wb_util.get_contact_info(wlo_dest.owner_party_id, 'PHONE', wlo_dest.owner_type) , wl.address1 , wl.city , wl.state , wl.postal_code , substr(mst_wb_util.get_local_chardt(mt_out.destination_location_id, mt_out.trip_end_date),1,20) , mt_out.trip_end_date , substr(mst_wb_util.get_local_chardt(mt_out.destination_location_id, (select min(md.latest_acceptable_date) from mst_deliveries md where md.plan_id = mt_out.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = out_leg.plan_id and mdl.trip_id = out_leg.trip_id and mdl.pick_up_stop_id = out_leg.pick_up_stop_id))),1,20) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md where md.plan_id = mt_out.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = out_leg.plan_id and mdl.trip_id = out_leg.trip_id and mdl.pick_up_stop_id = out_leg.pick_up_stop_id)) , (select nvl(sum(md.gross_weight),0) from mst_deliveries md where md.plan_id = mt_out.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = out_leg.plan_id and mdl.trip_id = out_leg.trip_id and mdl.pick_up_stop_id = out_leg.pick_up_stop_id)) , (select sum(nvl(md.volume,0)) from mst_deliveries md where md.plan_id = mt_out.plan_id and md.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = out_leg.plan_id and mdl.trip_id = out_leg.trip_id and mdl.pick_up_stop_id = out_leg.pick_up_stop_id)) , mdd.tp_refer_header_number , mdd.source_header_number from mst_trips mt_in , mst_trips mt_out , mst_trip_stops mts_in , mst_trip_stops mts_out , (select distinct mdl.plan_id plan_id, mdl.trip_id trip_id, mdl.drop_off_stop_id drop_off_stop_id from mst_delivery_legs mdl) in_leg , (select distinct mdl.plan_id plan_id, mdl.trip_id trip_id, mdl.pick_up_stop_id pick_up_stop_id from mst_delivery_legs mdl) out_leg , fte_location_parameters flp , wsh_location_owners wlo , wsh_carriers wc , wsh_carriers wc1 , wsh_carrier_services wcs , wsh_carrier_services wcs1 , wsh_locations wl , wsh_location_owners wlo_dest , mst_delivery_assignments mda , mst_delivery_details mdd where mt_in.plan_id = mts_in.plan_id and mt_in.trip_id = mts_in.trip_id and mt_out.plan_id = mts_out.plan_id and mt_out.trip_id = mts_out.trip_id and mt_in.plan_id = mt_out.plan_id and mts_in.stop_location_id = mts_out.stop_location_id and mts_in.plan_id = in_leg.plan_id and mts_in.trip_id = in_leg.trip_id and mts_in.stop_id = in_leg.drop_off_stop_id and mts_out.plan_id = out_leg.plan_id and mts_out.trip_id = out_leg.trip_id and mts_out.stop_id = out_leg.pick_up_stop_id and flp.location_id = mts_in.stop_location_id and flp.location_id = mt_in.destination_location_id and wlo.wsh_location_id = flp.location_id and wc.carrier_id = mt_in.carrier_id and wc1.carrier_id = mt_out.carrier_id and wcs.carrier_service_id = mt_in.carrier_service_id and wcs1.carrier_service_id = mt_out.carrier_service_id and wl.wsh_location_id = mt_out.destination_location_id and wlo_dest.wsh_location_id = wl.wsh_location_id and mda.delivery_id in (select distinct mdl.delivery_id from mst_delivery_legs mdl where mdl.plan_id = out_leg.plan_id and mdl.trip_id = out_leg.trip_id and mdl.pick_up_stop_id = out_leg.pick_up_stop_id) and mda.plan_id = mdd.plan_id and mda.delivery_detail_id = mdd.delivery_detail_id and mdd.container_flag = 2

View Text - HTML Formatted

SELECT DISTINCT MT_IN.PLAN_ID
, WLO.OWNER_TYPE
, FLP.FACILITY_ID
, MT_IN.CARRIER_ID
, WC.FREIGHT_CODE
, WCS.SERVICE_LEVEL
, MST_WB_UTIL.GET_CONTACT_INFO(MT_IN.CARRIER_ID
, 'PHONE'
, 3)
, MT_IN.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS_IN.STOP_LOCATION_ID
, MTS_IN.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, MTS_IN.PLANNED_ARRIVAL_DATE
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_IN.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = IN_LEG.PLAN_ID
AND MDL.TRIP_ID = IN_LEG.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = IN_LEG.DROP_OFF_STOP_ID))
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_IN.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = IN_LEG.PLAN_ID
AND MDL.TRIP_ID = IN_LEG.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = IN_LEG.DROP_OFF_STOP_ID))
, (SELECT SUM(NVL(MD.VOLUME
, 0))
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_IN.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = IN_LEG.PLAN_ID
AND MDL.TRIP_ID = IN_LEG.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = IN_LEG.DROP_OFF_STOP_ID))
, WC1.FREIGHT_CODE
, WCS1.SERVICE_LEVEL
, MST_WB_UTIL.GET_CONTACT_INFO(MT_OUT.CARRIER_ID
, 'PHONE'
, 3)
, MT_OUT.TRIP_NUMBER
, MST_WB_UTIL.R_GET_COMPANY_NAME(WLO_DEST.WSH_LOCATION_ID
, WLO_DEST.OWNER_TYPE)
, WL.LOCATION_CODE
, MST_WB_UTIL.GET_CONTACT_INFO(WLO_DEST.OWNER_PARTY_ID
, 'PHONE'
, WLO_DEST.OWNER_TYPE)
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MT_OUT.DESTINATION_LOCATION_ID
, MT_OUT.TRIP_END_DATE)
, 1
, 20)
, MT_OUT.TRIP_END_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MT_OUT.DESTINATION_LOCATION_ID
, (SELECT MIN(MD.LATEST_ACCEPTABLE_DATE)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_OUT.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = OUT_LEG.PLAN_ID
AND MDL.TRIP_ID = OUT_LEG.TRIP_ID
AND MDL.PICK_UP_STOP_ID = OUT_LEG.PICK_UP_STOP_ID)))
, 1
, 20)
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_OUT.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = OUT_LEG.PLAN_ID
AND MDL.TRIP_ID = OUT_LEG.TRIP_ID
AND MDL.PICK_UP_STOP_ID = OUT_LEG.PICK_UP_STOP_ID))
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_OUT.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = OUT_LEG.PLAN_ID
AND MDL.TRIP_ID = OUT_LEG.TRIP_ID
AND MDL.PICK_UP_STOP_ID = OUT_LEG.PICK_UP_STOP_ID))
, (SELECT SUM(NVL(MD.VOLUME
, 0))
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MT_OUT.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = OUT_LEG.PLAN_ID
AND MDL.TRIP_ID = OUT_LEG.TRIP_ID
AND MDL.PICK_UP_STOP_ID = OUT_LEG.PICK_UP_STOP_ID))
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
FROM MST_TRIPS MT_IN
, MST_TRIPS MT_OUT
, MST_TRIP_STOPS MTS_IN
, MST_TRIP_STOPS MTS_OUT
, (SELECT DISTINCT MDL.PLAN_ID PLAN_ID
, MDL.TRIP_ID TRIP_ID
, MDL.DROP_OFF_STOP_ID DROP_OFF_STOP_ID
FROM MST_DELIVERY_LEGS MDL) IN_LEG
, (SELECT DISTINCT MDL.PLAN_ID PLAN_ID
, MDL.TRIP_ID TRIP_ID
, MDL.PICK_UP_STOP_ID PICK_UP_STOP_ID
FROM MST_DELIVERY_LEGS MDL) OUT_LEG
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
, WSH_CARRIERS WC
, WSH_CARRIERS WC1
, WSH_CARRIER_SERVICES WCS
, WSH_CARRIER_SERVICES WCS1
, WSH_LOCATIONS WL
, WSH_LOCATION_OWNERS WLO_DEST
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
WHERE MT_IN.PLAN_ID = MTS_IN.PLAN_ID
AND MT_IN.TRIP_ID = MTS_IN.TRIP_ID
AND MT_OUT.PLAN_ID = MTS_OUT.PLAN_ID
AND MT_OUT.TRIP_ID = MTS_OUT.TRIP_ID
AND MT_IN.PLAN_ID = MT_OUT.PLAN_ID
AND MTS_IN.STOP_LOCATION_ID = MTS_OUT.STOP_LOCATION_ID
AND MTS_IN.PLAN_ID = IN_LEG.PLAN_ID
AND MTS_IN.TRIP_ID = IN_LEG.TRIP_ID
AND MTS_IN.STOP_ID = IN_LEG.DROP_OFF_STOP_ID
AND MTS_OUT.PLAN_ID = OUT_LEG.PLAN_ID
AND MTS_OUT.TRIP_ID = OUT_LEG.TRIP_ID
AND MTS_OUT.STOP_ID = OUT_LEG.PICK_UP_STOP_ID
AND FLP.LOCATION_ID = MTS_IN.STOP_LOCATION_ID
AND FLP.LOCATION_ID = MT_IN.DESTINATION_LOCATION_ID
AND WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND WC.CARRIER_ID = MT_IN.CARRIER_ID
AND WC1.CARRIER_ID = MT_OUT.CARRIER_ID
AND WCS.CARRIER_SERVICE_ID = MT_IN.CARRIER_SERVICE_ID
AND WCS1.CARRIER_SERVICE_ID = MT_OUT.CARRIER_SERVICE_ID
AND WL.WSH_LOCATION_ID = MT_OUT.DESTINATION_LOCATION_ID
AND WLO_DEST.WSH_LOCATION_ID = WL.WSH_LOCATION_ID
AND MDA.DELIVERY_ID IN (SELECT DISTINCT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = OUT_LEG.PLAN_ID
AND MDL.TRIP_ID = OUT_LEG.TRIP_ID
AND MDL.PICK_UP_STOP_ID = OUT_LEG.PICK_UP_STOP_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD.CONTAINER_FLAG = 2