VIEW: APPS.MST_LOW_COST_CAR_AVAILABLE_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , mt.trip_id , mt.trip_number , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , mt.mode_of_transport , wlk.meaning , wc1.carrier_id , wc1.freight_code , mt.service_level , wlk1.meaning , msikfv.concatenated_segments , (nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) + nvl(mt.total_layover_cost,0) + nvl(mt.total_load_unload_cost,0) + nvl(total_stop_cost,0)) , mp.currency_uom , wc2.carrier_id , wc2.freight_code , wcs.service_level , wlk2.meaning , wcs.mode_of_transport , msikfv1.concatenated_segments , nvl(number1,0) , (nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) + nvl(mt.total_layover_cost,0) + nvl(mt.total_load_unload_cost,0) + nvl(total_stop_cost,0) - nvl(number1,0)) , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_plans mp , mst_trips mt , mfg_lookups ml , wsh_locations wl1 , wsh_locations wl2 , wsh_carriers wc1 , wsh_carriers wc2 , wsh_carrier_services wcs , wsh_lookups wlk , wsh_lookups wlk1 , wsh_lookups wlk2 , fte_vehicle_types fvt , mtl_system_items_kfv msikfv , fte_vehicle_types fvt1 , mtl_system_items_kfv msikfv1 WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and mt.carrier_id = wc1.carrier_id and med.carrier_id = wc2.carrier_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and med.plan_id = mp.plan_id and med.carrier_service_id = wcs.carrier_service_id and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wlk.lookup_code = mt.mode_of_transport and wlk1.lookup_type = 'WSH_SERVICE_LEVELS' and wlk1.lookup_code = mt.service_level and wlk2.lookup_type = 'WSH_SERVICE_LEVELS' and wlk2.lookup_code = wcs.service_level and mt.vehicle_type_id = fvt.vehicle_type_id (+) and fvt.inventory_item_id = msikfv.inventory_item_id (+) and fvt.organization_id = msikfv.organization_id (+) and med.vehicle_type_id = fvt1.vehicle_type_id (+) and fvt1.inventory_item_id = msikfv1.inventory_item_id (+) and fvt1.organization_id = msikfv1.organization_id (+)

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, WC1.CARRIER_ID
, WC1.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK1.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(TOTAL_STOP_COST
, 0))
, MP.CURRENCY_UOM
, WC2.CARRIER_ID
, WC2.FREIGHT_CODE
, WCS.SERVICE_LEVEL
, WLK2.MEANING
, WCS.MODE_OF_TRANSPORT
, MSIKFV1.CONCATENATED_SEGMENTS
, NVL(NUMBER1
, 0)
, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(TOTAL_STOP_COST
, 0) - NVL(NUMBER1
, 0))
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_PLANS MP
, MST_TRIPS MT
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_CARRIERS WC1
, WSH_CARRIERS WC2
, WSH_CARRIER_SERVICES WCS
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, WSH_LOOKUPS WLK2
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, FTE_VEHICLE_TYPES FVT1
, MTL_SYSTEM_ITEMS_KFV MSIKFV1
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MT.CARRIER_ID = WC1.CARRIER_ID
AND MED.CARRIER_ID = WC2.CARRIER_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.CARRIER_SERVICE_ID = WCS.CARRIER_SERVICE_ID
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WLK.LOOKUP_CODE = MT.MODE_OF_TRANSPORT
AND WLK1.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND WLK1.LOOKUP_CODE = MT.SERVICE_LEVEL
AND WLK2.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND WLK2.LOOKUP_CODE = WCS.SERVICE_LEVEL
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID (+)
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID (+)
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID (+)
AND MED.VEHICLE_TYPE_ID = FVT1.VEHICLE_TYPE_ID (+)
AND FVT1.INVENTORY_ITEM_ID = MSIKFV1.INVENTORY_ITEM_ID (+)
AND FVT1.ORGANIZATION_ID = MSIKFV1.ORGANIZATION_ID (+)