VIEW: APPS.MST_ACT_MODE_CSV_V

Source

View Text - Preformatted

SELECT mor.plan_id PLAN_ID , mir.trip_id TRIP_ID , mor.in_request_id IN_REQUEST_ID , mor.out_request_id OUT_REQUEST_ID , wc.carrier_id CARRIER_ID , mor.carrier_service_id CARRIER_SERVICE_ID , mor.vehicle_type_id VEHICLE_TYPE_ID , wc.freight_code CARRIER_NAME , mor.service_level SERVICE_LEVEL , wlk.meaning SERVICE_LEVEL_TL , mt.mode_of_transport MODE_OF_TRANSPORT , wlk1.meaning MODE_OF_TRANSPORT_TL , msikfv.concatenated_segments VEHICLE_TYPE , nvl(mor.number2, 0) AVAILABLE_CAPACITY , nvl(mor.number1, 0) COST , (nvl(mt.total_basic_transport_cost, 0) + nvl(mt.total_accessorial_cost, 0) + nvl(mt.total_handling_cost, 0) + nvl(mt.total_layover_cost, 0) + nvl( mt.total_load_unload_cost, 0) + nvl(mt.total_stop_cost, 0) - nvl(mor.number1, 0)) COST_DIFFERENCE , mp.currency_uom COST_UOM from mst_out_requests mor, wsh_lookups wlk, wsh_lookups wlk1, wsh_carriers wc, mtl_system_items_kfv msikfv, fte_vehicle_types fvt, mst_in_requests mir, mst_trips mt, mst_plans mp WHERE mor.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and mt.trip_id = mir.trip_id and mt.plan_id = mir.plan_id and mt.mode_of_transport = wlk1.lookup_code and wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wc.carrier_id = mor.carrier_id and fvt.vehicle_type_id = mor.vehicle_type_id and fvt.organization_id = msikfv.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and mir.in_request_id = mor.in_request_id and mor.plan_id = mp.plan_id union all select mor.plan_id , mir.trip_id , mor.in_request_id , mor.out_request_id , wc.carrier_id , mor.carrier_service_id , to_number(null) , wc.freight_code , mor.service_level , wlk.meaning , mt.mode_of_transport , wlk1.meaning , to_char(null) , nvl(mor.number2, 0) , nvl(mor.number1, 0) , (nvl(mt.total_basic_transport_cost, 0) + nvl(mt.total_accessorial_cost, 0) + nvl(mt.total_handling_cost, 0) + nvl(mt.total_layover_cost, 0) + nvl( mt.total_load_unload_cost, 0) + nvl(mt.total_stop_cost, 0) - nvl(mor.number1, 0)) , mp.currency_uom from mst_out_requests mor, wsh_lookups wlk, wsh_lookups wlk1, wsh_carriers wc, mst_in_requests mir, mst_trips mt, mst_plans mp where mor.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and mt.trip_id = mir.trip_id and mt.plan_id = mir.plan_id and mt.mode_of_transport in ('LTL', 'PARCEL') and mt.mode_of_transport = wlk1.lookup_code and wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wc.carrier_id = mor.carrier_id and mir.in_request_id = mor.in_request_id and mor.plan_id = mp.plan_id union all SELECT mor.plan_id , mir.trip_id , mor.in_request_id , mor.out_request_id , wc.carrier_id , mor.carrier_service_id , mor.vehicle_type_id , wc.freight_code , mor.service_level , wlk.meaning , 'TRUCK' , wlk1.meaning , msikfv.concatenated_segments , nvl(mor.number2, 0) , nvl(mor.number1, 0) , (nvl(mt.total_transportation_cost, 0) - nvl(mor.number1, 0)) , mp.currency_uom from mst_out_requests mor, wsh_lookups wlk, wsh_lookups wlk1, wsh_carriers wc, mtl_system_items_kfv msikfv, fte_vehicle_types fvt, mst_in_requests mir, mst_cm_trips mt, mst_plans mp WHERE mor.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and mt.continuous_move_id = mir.continuous_move_id and mt.plan_id = mir.plan_id and wlk1.lookup_code = 'TRUCK' and wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wc.carrier_id = mor.carrier_id and fvt.vehicle_type_id = mor.vehicle_type_id and fvt.organization_id = msikfv.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and mir.in_request_id = mor.in_request_id and mor.plan_id = mp.plan_id

View Text - HTML Formatted

SELECT MOR.PLAN_ID PLAN_ID
, MIR.TRIP_ID TRIP_ID
, MOR.IN_REQUEST_ID IN_REQUEST_ID
, MOR.OUT_REQUEST_ID OUT_REQUEST_ID
, WC.CARRIER_ID CARRIER_ID
, MOR.CARRIER_SERVICE_ID CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID VEHICLE_TYPE_ID
, WC.FREIGHT_CODE CARRIER_NAME
, MOR.SERVICE_LEVEL SERVICE_LEVEL
, WLK.MEANING SERVICE_LEVEL_TL
, MT.MODE_OF_TRANSPORT MODE_OF_TRANSPORT
, WLK1.MEANING MODE_OF_TRANSPORT_TL
, MSIKFV.CONCATENATED_SEGMENTS VEHICLE_TYPE
, NVL(MOR.NUMBER2
, 0) AVAILABLE_CAPACITY
, NVL(MOR.NUMBER1
, 0) COST
, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL( MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) - NVL(MOR.NUMBER1
, 0)) COST_DIFFERENCE
, MP.CURRENCY_UOM COST_UOM
FROM MST_OUT_REQUESTS MOR
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, FTE_VEHICLE_TYPES FVT
, MST_IN_REQUESTS MIR
, MST_TRIPS MT
, MST_PLANS MP
WHERE MOR.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT.TRIP_ID = MIR.TRIP_ID
AND MT.PLAN_ID = MIR.PLAN_ID
AND MT.MODE_OF_TRANSPORT = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WC.CARRIER_ID = MOR.CARRIER_ID
AND FVT.VEHICLE_TYPE_ID = MOR.VEHICLE_TYPE_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MIR.IN_REQUEST_ID = MOR.IN_REQUEST_ID
AND MOR.PLAN_ID = MP.PLAN_ID UNION ALL SELECT MOR.PLAN_ID
, MIR.TRIP_ID
, MOR.IN_REQUEST_ID
, MOR.OUT_REQUEST_ID
, WC.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, TO_NUMBER(NULL)
, WC.FREIGHT_CODE
, MOR.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, WLK1.MEANING
, TO_CHAR(NULL)
, NVL(MOR.NUMBER2
, 0)
, NVL(MOR.NUMBER1
, 0)
, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL( MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) - NVL(MOR.NUMBER1
, 0))
, MP.CURRENCY_UOM
FROM MST_OUT_REQUESTS MOR
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, WSH_CARRIERS WC
, MST_IN_REQUESTS MIR
, MST_TRIPS MT
, MST_PLANS MP
WHERE MOR.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT.TRIP_ID = MIR.TRIP_ID
AND MT.PLAN_ID = MIR.PLAN_ID
AND MT.MODE_OF_TRANSPORT IN ('LTL'
, 'PARCEL')
AND MT.MODE_OF_TRANSPORT = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WC.CARRIER_ID = MOR.CARRIER_ID
AND MIR.IN_REQUEST_ID = MOR.IN_REQUEST_ID
AND MOR.PLAN_ID = MP.PLAN_ID UNION ALL SELECT MOR.PLAN_ID
, MIR.TRIP_ID
, MOR.IN_REQUEST_ID
, MOR.OUT_REQUEST_ID
, WC.CARRIER_ID
, MOR.CARRIER_SERVICE_ID
, MOR.VEHICLE_TYPE_ID
, WC.FREIGHT_CODE
, MOR.SERVICE_LEVEL
, WLK.MEANING
, 'TRUCK'
, WLK1.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, NVL(MOR.NUMBER2
, 0)
, NVL(MOR.NUMBER1
, 0)
, (NVL(MT.TOTAL_TRANSPORTATION_COST
, 0) - NVL(MOR.NUMBER1
, 0))
, MP.CURRENCY_UOM
FROM MST_OUT_REQUESTS MOR
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, FTE_VEHICLE_TYPES FVT
, MST_IN_REQUESTS MIR
, MST_CM_TRIPS MT
, MST_PLANS MP
WHERE MOR.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT.CONTINUOUS_MOVE_ID = MIR.CONTINUOUS_MOVE_ID
AND MT.PLAN_ID = MIR.PLAN_ID
AND WLK1.LOOKUP_CODE = 'TRUCK'
AND WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WC.CARRIER_ID = MOR.CARRIER_ID
AND FVT.VEHICLE_TYPE_ID = MOR.VEHICLE_TYPE_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MIR.IN_REQUEST_ID = MOR.IN_REQUEST_ID
AND MOR.PLAN_ID = MP.PLAN_ID