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_ACT_REASSIGN_DELIVERY_V

Source

View Text - Preformatted


SELECT mt.plan_id , mt.trip_id , mir.in_request_id , mt.trip_number , mt.mode_of_transport , wlk.meaning , MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt.plan_id, mt.trip_id) , mp.weight_uom , MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt.plan_id, mt.trip_id) , mp.volume_uom , MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt.plan_id, mt.trip_id) , MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(mt.plan_id, mt.trip_id) , decode(mt.mode_of_transport,'TRUCK',mt.total_trip_distance,to_number(null)) , decode(mt.mode_of_transport,'TRUCK',mp.distance_uom,null) , decode(mt.mode_of_transport,'TRUCK',MST_AGG_PKG.GET_NUM_OF_STOPS_FOR_TL(mt.plan_id, mt.trip_id),to_number(null)) , decode(mt.mode_of_transport,'TRUCK',MST_WB_UTIL.GET_TRIP_CIRCUITY(mt.plan_id, mt.trip_id),to_number(null)) , nvl ( ( nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_stop_cost, 0) + nvl(mt.total_load_unload_cost,0) + nvl(mt.total_layover_cost, 0) + nvl(mt.total_accessorial_cost, 0) + nvl(mt.total_handling_cost, 0) ), 0 ) , mp.currency_uom , 1 , substr(MST_WB_UTIL.get_meaning('MST_STRING', 38, 'MFG'), 1, 80) , mt.origin_location_id , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.city , wl1.state , wl1.postal_code , wl1.country , mt.destination_location_id , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.city , wl2.state , wl2.postal_code , wl2.country from mst_trips mt , mst_plans mp , mst_in_requests mir , wsh_lookups wlk , wsh_locations wl1 , wsh_locations wl2 WHERE mt.plan_id = mir.plan_id and mt.trip_id = mir.trip_id and mir.type_id = 38 and mp.plan_id = mt.plan_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id union all select mt.plan_id , mt.trip_id , mir.in_request_id , mt.trip_number , mt.mode_of_transport , wlk.meaning , MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt.plan_id, mt.trip_id) , mp.weight_uom , MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt.plan_id, mt.trip_id) , mp.volume_uom , MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt.plan_id, mt.trip_id) , MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(mt.plan_id, mt.trip_id) , decode(mt.mode_of_transport,'TRUCK',mt.total_trip_distance,to_number(null)) , decode(mt.mode_of_transport,'TRUCK',mp.distance_uom,null) , decode(mt.mode_of_transport,'TRUCK',MST_AGG_PKG.GET_NUM_OF_STOPS_FOR_TL(mt.plan_id, mt.trip_id),to_number(null)) , decode(mt.mode_of_transport,'TRUCK',MST_WB_UTIL.GET_TRIP_CIRCUITY(mt.plan_id, mt.trip_id),to_number(null)) , nvl ( ( nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_stop_cost, 0) + nvl(mt.total_load_unload_cost,0) + nvl(mt.total_layover_cost, 0) + nvl(mt.total_accessorial_cost, 0) + nvl(mt.total_handling_cost, 0) ), 0 ) , mp.currency_uom , 1 , substr(MST_WB_UTIL.get_meaning('MST_STRING', 38, 'MFG'), 1, 80) , mt.origin_location_id , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.city , wl1.state , wl1.postal_code , wl1.country , mt.destination_location_id , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.city , wl2.state , wl2.postal_code , wl2.country from mst_trips mt , mst_plans mp , mst_in_requests mir , mst_delivery_legs mdl , wsh_lookups wlk , wsh_locations wl1 , wsh_locations wl2 where mt.plan_id = mir.plan_id and mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.delivery_id = mir.delivery_id and mir.type_id = 38 and mp.plan_id = mt.plan_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id union all select mir.plan_id , to_number(null) , mir.in_request_id , to_number(null) , to_char(null) , to_char(null) , to_number(null) , to_char(null) , to_number(null) , to_char(null) , to_number(null) , to_number(null) , to_number(null) , to_char(null) , to_number(null) , to_number(null) , to_number(null) , to_char(null) , 2 , to_char(null) , to_number(null) , to_char(null) , to_char(null) , to_char(null) , to_char(null) , to_char(null) , to_number(null) , to_char(null) , to_char(null) , to_char(null) , to_char(null) , to_char(null) from mst_in_requests mir where mir.type_id = 38 union all select mt.plan_id , mt.trip_id , mir.in_request_id , mt.trip_number , mt.mode_of_transport , wlk.meaning , mord.number4 , mp.weight_uom , mord.number5 , mp.volume_uom , mord.number6 , mord.number7 , mord.number8 , mp.distance_uom , mord.number9 , mord.number10 , mord.number2 , mp.currency_uom , 3 , substr(MST_WB_UTIL.get_meaning('MST_STRING', 37, 'MFG'), 1, 80) , mt.origin_location_id , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.city , wl1.state , wl1.postal_code , wl1.country , mt.destination_location_id , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.city , wl2.state , wl2.postal_code , wl2.country from mst_trips mt , mst_plans mp , mst_in_requests mir , mst_out_requests mor , mst_out_request_details mord , wsh_lookups wlk , wsh_locations wl1 , wsh_locations wl2 where mir.plan_id = mor.plan_id and mir.in_request_id = mor.in_request_id and mir.type_id = 38 and mt.plan_id = mor.plan_id and mt.trip_id = mord.number12 and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mord.plan_id = mor.plan_id and mord.out_request_id = mor.out_request_id and mp.plan_id = mt.plan_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT.PLAN_ID
, MT.TRIP_ID)
, MP.WEIGHT_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID)
, MP.VOLUME_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT.PLAN_ID
, MT.TRIP_ID)
, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(MT.PLAN_ID
, MT.TRIP_ID)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MT.TOTAL_TRIP_DISTANCE
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MP.DISTANCE_UOM
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MST_AGG_PKG.GET_NUM_OF_STOPS_FOR_TL(MT.PLAN_ID
, MT.TRIP_ID)
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MST_WB_UTIL.GET_TRIP_CIRCUITY(MT.PLAN_ID
, MT.TRIP_ID)
, TO_NUMBER(NULL))
, NVL ( ( NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) )
, 0 )
, MP.CURRENCY_UOM
, 1
, SUBSTR(MST_WB_UTIL.GET_MEANING('MST_STRING'
, 38
, 'MFG')
, 1
, 80)
, MT.ORIGIN_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, MT.DESTINATION_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
FROM MST_TRIPS MT
, MST_PLANS MP
, MST_IN_REQUESTS MIR
, WSH_LOOKUPS WLK
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MT.PLAN_ID = MIR.PLAN_ID
AND MT.TRIP_ID = MIR.TRIP_ID
AND MIR.TYPE_ID = 38
AND MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID UNION ALL SELECT MT.PLAN_ID
, MT.TRIP_ID
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT.PLAN_ID
, MT.TRIP_ID)
, MP.WEIGHT_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID)
, MP.VOLUME_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT.PLAN_ID
, MT.TRIP_ID)
, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(MT.PLAN_ID
, MT.TRIP_ID)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MT.TOTAL_TRIP_DISTANCE
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MP.DISTANCE_UOM
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MST_AGG_PKG.GET_NUM_OF_STOPS_FOR_TL(MT.PLAN_ID
, MT.TRIP_ID)
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MST_WB_UTIL.GET_TRIP_CIRCUITY(MT.PLAN_ID
, MT.TRIP_ID)
, TO_NUMBER(NULL))
, NVL ( ( NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) )
, 0 )
, MP.CURRENCY_UOM
, 1
, SUBSTR(MST_WB_UTIL.GET_MEANING('MST_STRING'
, 38
, 'MFG')
, 1
, 80)
, MT.ORIGIN_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, MT.DESTINATION_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
FROM MST_TRIPS MT
, MST_PLANS MP
, MST_IN_REQUESTS MIR
, MST_DELIVERY_LEGS MDL
, WSH_LOOKUPS WLK
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MT.PLAN_ID = MIR.PLAN_ID
AND MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MDL.DELIVERY_ID = MIR.DELIVERY_ID
AND MIR.TYPE_ID = 38
AND MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID UNION ALL SELECT MIR.PLAN_ID
, TO_NUMBER(NULL)
, MIR.IN_REQUEST_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 2
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
FROM MST_IN_REQUESTS MIR
WHERE MIR.TYPE_ID = 38 UNION ALL SELECT MT.PLAN_ID
, MT.TRIP_ID
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, MORD.NUMBER4
, MP.WEIGHT_UOM
, MORD.NUMBER5
, MP.VOLUME_UOM
, MORD.NUMBER6
, MORD.NUMBER7
, MORD.NUMBER8
, MP.DISTANCE_UOM
, MORD.NUMBER9
, MORD.NUMBER10
, MORD.NUMBER2
, MP.CURRENCY_UOM
, 3
, SUBSTR(MST_WB_UTIL.GET_MEANING('MST_STRING'
, 37
, 'MFG')
, 1
, 80)
, MT.ORIGIN_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, MT.DESTINATION_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
FROM MST_TRIPS MT
, MST_PLANS MP
, MST_IN_REQUESTS MIR
, MST_OUT_REQUESTS MOR
, MST_OUT_REQUEST_DETAILS MORD
, WSH_LOOKUPS WLK
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MIR.PLAN_ID = MOR.PLAN_ID
AND MIR.IN_REQUEST_ID = MOR.IN_REQUEST_ID
AND MIR.TYPE_ID = 38
AND MT.PLAN_ID = MOR.PLAN_ID
AND MT.TRIP_ID = MORD.NUMBER12
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MORD.PLAN_ID = MOR.PLAN_ID
AND MORD.OUT_REQUEST_ID = MOR.OUT_REQUEST_ID
AND MP.PLAN_ID = MT.PLAN_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID