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_BEFORE_AFTER_V

Source

View Text - Preformatted


SELECT mt.plan_id, mir.number1, mt.trip_id, mor.carrier_id, mor.carrier_service_id, mor.vehicle_type_id, to_number(null), mir.in_request_id, mt.trip_number, 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(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), mp.currency_uom, 'TRIP', 1 from mst_plans mp, mst_trips mt, wsh_lookups wlk, mst_in_requests mir, mst_out_requests mor where mp.plan_id = mt.plan_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.plan_id = mir.plan_id and mt.trip_id = mir.trip_id and mir.type_id = 15 and mor.plan_id = mir.plan_id and mor.in_request_id = mir.in_request_id union all select md.plan_id, mir.trip_id, to_number(null), mor.carrier_id, mor.carrier_service_id, mor.vehicle_type_id, md.delivery_id, mir.in_request_id, md.delivery_number, substr(MST_WB_UTIL.get_meaning('MST_STRING', 36, 'MFG'), 1, 80), md.gross_weight, mp.weight_uom, md.volume, mp.volume_uom, md.number_of_pallets, md.number_of_pieces, to_number(null), null, to_number(null), to_number(null), to_number(null), null, 'DELIVERY', 1 from mst_plans mp, mst_deliveries md, mst_in_requests mir, mst_out_requests mor where mp.plan_id = md.plan_id and md.plan_id = mir.plan_id and md.delivery_id = mir.delivery_id and mir.type_id = 12 and mor.plan_id = mir.plan_id and mor.in_request_id = mir.in_request_id union all select mt.plan_id, mt.trip_id, to_number(null), mor.carrier_id, mor.carrier_service_id, mor.vehicle_type_id, to_number(null), mir.in_request_id, mt.trip_number, 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(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), mp.currency_uom, 'TRIP', 2 from mst_plans mp, mst_trips mt, wsh_lookups wlk, mst_in_requests mir, mst_out_requests mor where mp.plan_id = mt.plan_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.plan_id = mir.plan_id and ( (mir.type_id = 15 and mt.trip_id = mir.number1) or (mir.type_id = 12 and mt.trip_id = mir.trip_id) ) and mor.plan_id = mir.plan_id and mor.in_request_id = mir.in_request_id union all select plan_id, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), in_request_id, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), to_number(null), null, null, 3 from mst_in_requests union all select mt.plan_id, mir.number1, mt.trip_id, mor.carrier_id, mor.carrier_service_id, mor.vehicle_type_id, to_number(null), mir.in_request_id, mt.trip_number, wlk.meaning, 0, mp.weight_uom, 0, mp.volume_uom, 0, 0, decode(mt.mode_of_transport,'TRUCK',0,to_number(null)), decode(mt.mode_of_transport,'TRUCK',mp.distance_uom,null), decode(mt.mode_of_transport,'TRUCK',0,to_number(null)), decode(mt.mode_of_transport,'TRUCK',0,to_number(null)), 0, mp.currency_uom, 'TRIP', 4 from mst_plans mp, mst_trips mt, wsh_lookups wlk, mst_in_requests mir, mst_out_requests mor where mp.plan_id = mt.plan_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.plan_id = mir.plan_id and mt.trip_id = mir.trip_id and mir.type_id = 15 and mor.plan_id = mir.plan_id and mor.in_request_id = mir.in_request_id union all select mt.plan_id, mt.trip_id, to_number(null), mor.carrier_id, mor.carrier_service_id, mor.vehicle_type_id, to_number(null), mor.in_request_id, mt.trip_number, wlk.meaning, mor.number4, weight_uom, mor.number5, mp.volume_uom, mor.number6, mor.number7, decode(mt.mode_of_transport,'TRUCK',mor.number8,to_number(null)), decode(mt.mode_of_transport,'TRUCK',distance_uom,null), decode(mt.mode_of_transport,'TRUCK',mor.number9,to_number(null)), decode(mt.mode_of_transport,'TRUCK',mor.number10,to_number(null)), mor.number2, mp.currency_uom, 'TRIP', 5 from mst_plans mp, mst_trips mt, mst_out_requests mor, mst_in_requests mir, wsh_lookups wlk where mp.plan_id = mt.plan_id and mt.plan_id = mir.plan_id and ( (mir.type_id = 15 and mt.trip_id = mir.number1) or (mir.type_id = 12 and mt.trip_id = mir.trip_id) ) and mir.in_request_id = mor.in_request_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mor.plan_id = mir.plan_id and mor.in_request_id = mir.in_request_id

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MIR.NUMBER1
, MT.TRIP_ID
, MOR.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, TO_NUMBER(NULL)
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, 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(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)
, MP.CURRENCY_UOM
, 'TRIP'
, 1
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_LOOKUPS WLK
, MST_IN_REQUESTS MIR
, MST_OUT_REQUESTS MOR
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.PLAN_ID = MIR.PLAN_ID
AND MT.TRIP_ID = MIR.TRIP_ID
AND MIR.TYPE_ID = 15
AND MOR.PLAN_ID = MIR.PLAN_ID
AND MOR.IN_REQUEST_ID = MIR.IN_REQUEST_ID UNION ALL SELECT MD.PLAN_ID
, MIR.TRIP_ID
, TO_NUMBER(NULL)
, MOR.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, MD.DELIVERY_ID
, MIR.IN_REQUEST_ID
, MD.DELIVERY_NUMBER
, SUBSTR(MST_WB_UTIL.GET_MEANING('MST_STRING'
, 36
, 'MFG')
, 1
, 80)
, MD.GROSS_WEIGHT
, MP.WEIGHT_UOM
, MD.VOLUME
, MP.VOLUME_UOM
, MD.NUMBER_OF_PALLETS
, MD.NUMBER_OF_PIECES
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 'DELIVERY'
, 1
FROM MST_PLANS MP
, MST_DELIVERIES MD
, MST_IN_REQUESTS MIR
, MST_OUT_REQUESTS MOR
WHERE MP.PLAN_ID = MD.PLAN_ID
AND MD.PLAN_ID = MIR.PLAN_ID
AND MD.DELIVERY_ID = MIR.DELIVERY_ID
AND MIR.TYPE_ID = 12
AND MOR.PLAN_ID = MIR.PLAN_ID
AND MOR.IN_REQUEST_ID = MIR.IN_REQUEST_ID UNION ALL SELECT MT.PLAN_ID
, MT.TRIP_ID
, TO_NUMBER(NULL)
, MOR.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, TO_NUMBER(NULL)
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, 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(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)
, MP.CURRENCY_UOM
, 'TRIP'
, 2
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_LOOKUPS WLK
, MST_IN_REQUESTS MIR
, MST_OUT_REQUESTS MOR
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.PLAN_ID = MIR.PLAN_ID
AND ( (MIR.TYPE_ID = 15
AND MT.TRIP_ID = MIR.NUMBER1) OR (MIR.TYPE_ID = 12
AND MT.TRIP_ID = MIR.TRIP_ID) )
AND MOR.PLAN_ID = MIR.PLAN_ID
AND MOR.IN_REQUEST_ID = MIR.IN_REQUEST_ID UNION ALL SELECT PLAN_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, IN_REQUEST_ID
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, 3
FROM MST_IN_REQUESTS UNION ALL SELECT MT.PLAN_ID
, MIR.NUMBER1
, MT.TRIP_ID
, MOR.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, TO_NUMBER(NULL)
, MIR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, WLK.MEANING
, 0
, MP.WEIGHT_UOM
, 0
, MP.VOLUME_UOM
, 0
, 0
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, 0
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MP.DISTANCE_UOM
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, 0
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, 0
, TO_NUMBER(NULL))
, 0
, MP.CURRENCY_UOM
, 'TRIP'
, 4
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_LOOKUPS WLK
, MST_IN_REQUESTS MIR
, MST_OUT_REQUESTS MOR
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.PLAN_ID = MIR.PLAN_ID
AND MT.TRIP_ID = MIR.TRIP_ID
AND MIR.TYPE_ID = 15
AND MOR.PLAN_ID = MIR.PLAN_ID
AND MOR.IN_REQUEST_ID = MIR.IN_REQUEST_ID UNION ALL SELECT MT.PLAN_ID
, MT.TRIP_ID
, TO_NUMBER(NULL)
, MOR.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, TO_NUMBER(NULL)
, MOR.IN_REQUEST_ID
, MT.TRIP_NUMBER
, WLK.MEANING
, MOR.NUMBER4
, WEIGHT_UOM
, MOR.NUMBER5
, MP.VOLUME_UOM
, MOR.NUMBER6
, MOR.NUMBER7
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MOR.NUMBER8
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, DISTANCE_UOM
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MOR.NUMBER9
, TO_NUMBER(NULL))
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MOR.NUMBER10
, TO_NUMBER(NULL))
, MOR.NUMBER2
, MP.CURRENCY_UOM
, 'TRIP'
, 5
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_OUT_REQUESTS MOR
, MST_IN_REQUESTS MIR
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MIR.PLAN_ID
AND ( (MIR.TYPE_ID = 15
AND MT.TRIP_ID = MIR.NUMBER1) OR (MIR.TYPE_ID = 12
AND MT.TRIP_ID = MIR.TRIP_ID) )
AND MIR.IN_REQUEST_ID = MOR.IN_REQUEST_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MOR.PLAN_ID = MIR.PLAN_ID
AND MOR.IN_REQUEST_ID = MIR.IN_REQUEST_ID