VIEW: APPS.MST_R_POOL_ACTIVITY_CONSOL_V

Source

View Text - Preformatted

SELECT distinct mt_out.plan_id , wlo_dest.owner_type , flp.facility_id , mt_out.carrier_id , wc_out.freight_code , wcs_out.service_level , mst_wb_util.get_contact_info(mt_out.carrier_id, 'PHONE', 3) , mt_out.trip_number , substr(mst_wb_util.get_local_chardt(mts_out.stop_location_id, mts_out.planned_departure_date),1,20) , mts_out.planned_departure_date , mst_wb_util.r_get_company_name(wl_out.wsh_location_id, wlo_dest.owner_type) , wl_out.location_code , mst_wb_util.get_contact_info(wlo_dest.owner_party_id, 'PHONE', wlo_dest.owner_type) , wl_out.address1 , wl_out.city , wl_out.state , wl_out.postal_code , (select sum(nvl(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 sum(nvl(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)) , wc_in.freight_code , wcs_in.service_level , mst_wb_util.get_contact_info(mt_in.carrier_id, 'PHONE', 3) , mt_in.trip_number , mst_wb_util.r_get_company_name(wl_in.wsh_location_id, wlo_orig.owner_type) , wl_in.location_code , mst_wb_util.get_contact_info(wlo_orig.owner_party_id, 'PHONE', wlo_orig.owner_type) , wl_in.address1 , wl_in.city , wl_in.state , wl_in.postal_code , substr(mst_wb_util.get_local_chardt(mt_in.origin_location_id, mt_in.trip_start_date),1,20) , mt_in.trip_start_date , (select sum(nvl(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 sum(nvl(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)) , mdd.tp_refer_header_number , mdd.source_header_number from mst_trips mt_out , mst_trips mt_in , mst_trip_stops mts_out , mst_trip_stops mts_in , (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 , (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 , fte_location_parameters flp , wsh_carriers wc_out , wsh_carriers wc_in , wsh_carrier_services wcs_out , wsh_carrier_services wcs_in , wsh_locations wl_out , wsh_locations wl_in , wsh_location_owners wlo_dest , wsh_location_owners wlo_orig , mst_delivery_assignments mda , mst_delivery_details mdd where mt_out.plan_id = mts_out.plan_id and mt_out.trip_id = mts_out.trip_id and mt_in.plan_id = mts_in.plan_id and mt_in.trip_id = mts_in.trip_id and mt_in.plan_id = mt_out.plan_id and mts_in.stop_location_id = mts_out.stop_location_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 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 flp.location_id = mts_out.stop_location_id and flp.location_id = mt_out.origin_location_id and wc_out.carrier_id = mt_out.carrier_id and wc_in.carrier_id = mt_in.carrier_id and wcs_out.carrier_service_id = mt_out.carrier_service_id and wcs_in.carrier_service_id = mt_in.carrier_service_id and wl_out.wsh_location_id = mt_out.destination_location_id and wlo_dest.wsh_location_id = wl_out.wsh_location_id and wl_in.wsh_location_id = mt_in.origin_location_id and wlo_orig.wsh_location_id = wl_in.wsh_location_id and mda.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) 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_OUT.PLAN_ID
, WLO_DEST.OWNER_TYPE
, FLP.FACILITY_ID
, MT_OUT.CARRIER_ID
, WC_OUT.FREIGHT_CODE
, WCS_OUT.SERVICE_LEVEL
, MST_WB_UTIL.GET_CONTACT_INFO(MT_OUT.CARRIER_ID
, 'PHONE'
, 3)
, MT_OUT.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS_OUT.STOP_LOCATION_ID
, MTS_OUT.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, MTS_OUT.PLANNED_DEPARTURE_DATE
, MST_WB_UTIL.R_GET_COMPANY_NAME(WL_OUT.WSH_LOCATION_ID
, WLO_DEST.OWNER_TYPE)
, WL_OUT.LOCATION_CODE
, MST_WB_UTIL.GET_CONTACT_INFO(WLO_DEST.OWNER_PARTY_ID
, 'PHONE'
, WLO_DEST.OWNER_TYPE)
, WL_OUT.ADDRESS1
, WL_OUT.CITY
, WL_OUT.STATE
, WL_OUT.POSTAL_CODE
, (SELECT SUM(NVL(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 SUM(NVL(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))
, WC_IN.FREIGHT_CODE
, WCS_IN.SERVICE_LEVEL
, MST_WB_UTIL.GET_CONTACT_INFO(MT_IN.CARRIER_ID
, 'PHONE'
, 3)
, MT_IN.TRIP_NUMBER
, MST_WB_UTIL.R_GET_COMPANY_NAME(WL_IN.WSH_LOCATION_ID
, WLO_ORIG.OWNER_TYPE)
, WL_IN.LOCATION_CODE
, MST_WB_UTIL.GET_CONTACT_INFO(WLO_ORIG.OWNER_PARTY_ID
, 'PHONE'
, WLO_ORIG.OWNER_TYPE)
, WL_IN.ADDRESS1
, WL_IN.CITY
, WL_IN.STATE
, WL_IN.POSTAL_CODE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MT_IN.ORIGIN_LOCATION_ID
, MT_IN.TRIP_START_DATE)
, 1
, 20)
, MT_IN.TRIP_START_DATE
, (SELECT SUM(NVL(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 SUM(NVL(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))
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
FROM MST_TRIPS MT_OUT
, MST_TRIPS MT_IN
, MST_TRIP_STOPS MTS_OUT
, MST_TRIP_STOPS MTS_IN
, (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
, (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
, FTE_LOCATION_PARAMETERS FLP
, WSH_CARRIERS WC_OUT
, WSH_CARRIERS WC_IN
, WSH_CARRIER_SERVICES WCS_OUT
, WSH_CARRIER_SERVICES WCS_IN
, WSH_LOCATIONS WL_OUT
, WSH_LOCATIONS WL_IN
, WSH_LOCATION_OWNERS WLO_DEST
, WSH_LOCATION_OWNERS WLO_ORIG
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
WHERE MT_OUT.PLAN_ID = MTS_OUT.PLAN_ID
AND MT_OUT.TRIP_ID = MTS_OUT.TRIP_ID
AND MT_IN.PLAN_ID = MTS_IN.PLAN_ID
AND MT_IN.TRIP_ID = MTS_IN.TRIP_ID
AND MT_IN.PLAN_ID = MT_OUT.PLAN_ID
AND MTS_IN.STOP_LOCATION_ID = MTS_OUT.STOP_LOCATION_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 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 FLP.LOCATION_ID = MTS_OUT.STOP_LOCATION_ID
AND FLP.LOCATION_ID = MT_OUT.ORIGIN_LOCATION_ID
AND WC_OUT.CARRIER_ID = MT_OUT.CARRIER_ID
AND WC_IN.CARRIER_ID = MT_IN.CARRIER_ID
AND WCS_OUT.CARRIER_SERVICE_ID = MT_OUT.CARRIER_SERVICE_ID
AND WCS_IN.CARRIER_SERVICE_ID = MT_IN.CARRIER_SERVICE_ID
AND WL_OUT.WSH_LOCATION_ID = MT_OUT.DESTINATION_LOCATION_ID
AND WLO_DEST.WSH_LOCATION_ID = WL_OUT.WSH_LOCATION_ID
AND WL_IN.WSH_LOCATION_ID = MT_IN.ORIGIN_LOCATION_ID
AND WLO_ORIG.WSH_LOCATION_ID = WL_IN.WSH_LOCATION_ID
AND MDA.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)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD.CONTAINER_FLAG = 2