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_CM_LOADS_V

Source

View Text - Preformatted


SELECT MT.PLAN_ID, MT.ROWID ROW_ID, MT.TRIP_ID, CMT.CONTINUOUS_MOVE_ID, MT.continuous_move_sequence, MT.TRIP_NUMBER, CAR.carrier_id, CAR.freight_code, MT.service_level, WSH.meaning, item_kfv.concatenated_segments, mst_cm_details.get_trip_loading_status(MT.plan_id, MT.trip_id), SUBSTR(MST_WB_UTIL.GET_NAME (WL1.WSH_LOCATION_ID), 1,80) ORIGIN_COMPANY, LP1.FACILITY_ID ORIGIN_FACILITY_ID, LP1.FACILITY_CODE ORIGIN_FACILITY, LP1.DESCRIPTION ORIGIN_FACILITY_DESC, WL1.CITY ORIGIN_CITY, WL1.STATE ORIGIN_STATE, WL1.POSTAL_CODE ORIGIN_ZIP, WL1.COUNTRY ORIGIN_COUNTRY, SUBSTR(MST_WB_UTIL.GET_NAME (WL2.WSH_LOCATION_ID), 1,80) DESTINATION_COMPANY, LP2.FACILITY_ID DESTINATION_FACILITY_ID, LP2.FACILITY_CODE DESTINATION_FACILITY, LP2.DESCRIPTION DESTINATION_FACILITY_DESC, WL2.CITY DESTINATION_CITY, WL2.STATE DESTINATION_STATE, WL2.POSTAL_CODE DESTINATION_ZIP, WL2.COUNTRY DESTINATION_COUNTRY, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, MT.TRIP_START_DATE) TRIP_START_DATE, MST_GEOCODING.Get_timezone_code (WL1.WSH_location_id, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, MT.TRIP_START_DATE) ) TIME_ZONE1, MT.TRIP_END_DATE, MT.TOTAL_TRIP_DISTANCE, MST_WB_UTIL.GET_TRIP_STOPS(MT.PLAN_ID, MT.TRIP_ID) STOPS, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST,0) + NVL(MT.TOTAL_STOP_COST,0) + NVL(MT.TOTAL_LAYOVER_COST,0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST,0) + NVL(MT.TOTAL_ACCESSORIAL_COST,0) + NVL(MT.TOTAL_HANDLING_COST,0) ) TOTAL_TRIP_COST, SUBSTR(MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (MT.PLAN_ID, MT.TRIP_ID)),1,80) FIRST_DEPARTURE_DATE, SUBSTR(MST_WB_UTIL.get_local_chardtzone (WL2.WSH_LOCATION_ID, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (MT.PLAN_ID, MT.TRIP_ID)),1,80) LAST_ARRIVAL_DATE, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID, MT.TRIP_ID,'W') WEIGHT, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID, MT.TRIP_ID,'V') CUBE, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID, MT.TRIP_ID,'P') PALLETS, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID, MT.TRIP_ID,'PC') PIECES, MST_WB_UTIL.GET_TRIP_ORDERS(MT.PLAN_ID, MT.TRIP_ID) ORDERS, MT.planned_flag, mfg1.meaning, nvl(MT.selected_for_release,2), mfg2.meaning, MT.release_date, fnd_date.date_to_chardt(MT.release_date), MT.release_status, mfg3.meaning, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, MST_TRIPS MT, MST_CM_TRIPS CMT, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2, wsh_carriers CAR, FTE_VEHICLE_TYPES FVT, MTL_SYSTEM_ITEMS_KFV item_kfv, mfg_lookups mfg1, mfg_lookups mfg2, mfg_lookups mfg3, wsh_lookups wsh WHERE MP.PLAN_ID = CMT.PLAN_ID and CMT.PLAN_ID = MT.PLAN_ID and CMT.CONTINUOUS_MOVE_ID = MT.CONTINUOUS_MOVE_ID and MT.MODE_OF_TRANSPORT = 'TRUCK' and MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID and MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID and WL1.WSH_LOCATION_ID = LP1.LOCATION_ID and WL2.WSH_LOCATION_ID = LP2.LOCATION_ID and MT.carrier_id = CAR.carrier_id and MT.vehicle_type_id = FVT.vehicle_type_id and FVT.inventory_item_id = item_kfv.inventory_item_id and FVT.organization_id = item_kfv.organization_id and MT.planned_flag = mfg1.lookup_code and mfg1.lookup_type = 'MST_TRIP_PLANNED_FLAG' and nvl(MT.selected_for_release,2) = mfg2.lookup_code and mfg2.lookup_type = 'MST_SELECTED_FOR_RELEASE' and nvl(MT.release_status,5) = mfg3.lookup_code and mfg3.lookup_type = 'MST_RELEASE_STATUS' and MT.service_level = WSH.lookup_code and WSH.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.ROWID ROW_ID
, MT.TRIP_ID
, CMT.CONTINUOUS_MOVE_ID
, MT.CONTINUOUS_MOVE_SEQUENCE
, MT.TRIP_NUMBER
, CAR.CARRIER_ID
, CAR.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WSH.MEANING
, ITEM_KFV.CONCATENATED_SEGMENTS
, MST_CM_DETAILS.GET_TRIP_LOADING_STATUS(MT.PLAN_ID
, MT.TRIP_ID)
, SUBSTR(MST_WB_UTIL.GET_NAME (WL1.WSH_LOCATION_ID)
, 1
, 80) ORIGIN_COMPANY
, LP1.FACILITY_ID ORIGIN_FACILITY_ID
, LP1.FACILITY_CODE ORIGIN_FACILITY
, LP1.DESCRIPTION ORIGIN_FACILITY_DESC
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME (WL2.WSH_LOCATION_ID)
, 1
, 80) DESTINATION_COMPANY
, LP2.FACILITY_ID DESTINATION_FACILITY_ID
, LP2.FACILITY_CODE DESTINATION_FACILITY
, LP2.DESCRIPTION DESTINATION_FACILITY_DESC
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, MT.TRIP_START_DATE) TRIP_START_DATE
, MST_GEOCODING.GET_TIMEZONE_CODE (WL1.WSH_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, MT.TRIP_START_DATE) ) TIME_ZONE1
, MT.TRIP_END_DATE
, MT.TOTAL_TRIP_DISTANCE
, MST_WB_UTIL.GET_TRIP_STOPS(MT.PLAN_ID
, MT.TRIP_ID) STOPS
, (NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) ) TOTAL_TRIP_COST
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (MT.PLAN_ID
, MT.TRIP_ID))
, 1
, 80) FIRST_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL2.WSH_LOCATION_ID
, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (MT.PLAN_ID
, MT.TRIP_ID))
, 1
, 80) LAST_ARRIVAL_DATE
, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID
, MT.TRIP_ID
, 'W') WEIGHT
, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID
, MT.TRIP_ID
, 'V') CUBE
, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID
, MT.TRIP_ID
, 'P') PALLETS
, MST_WB_UTIL.GET_TRIP_DET (MT.PLAN_ID
, MT.TRIP_ID
, 'PC') PIECES
, MST_WB_UTIL.GET_TRIP_ORDERS(MT.PLAN_ID
, MT.TRIP_ID) ORDERS
, MT.PLANNED_FLAG
, MFG1.MEANING
, NVL(MT.SELECTED_FOR_RELEASE
, 2)
, MFG2.MEANING
, MT.RELEASE_DATE
, FND_DATE.DATE_TO_CHARDT(MT.RELEASE_DATE)
, MT.RELEASE_STATUS
, MFG3.MEANING
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_CM_TRIPS CMT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
, WSH_CARRIERS CAR
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV ITEM_KFV
, MFG_LOOKUPS MFG1
, MFG_LOOKUPS MFG2
, MFG_LOOKUPS MFG3
, WSH_LOOKUPS WSH
WHERE MP.PLAN_ID = CMT.PLAN_ID
AND CMT.PLAN_ID = MT.PLAN_ID
AND CMT.CONTINUOUS_MOVE_ID = MT.CONTINUOUS_MOVE_ID
AND MT.MODE_OF_TRANSPORT = 'TRUCK'
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID
AND MT.CARRIER_ID = CAR.CARRIER_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = ITEM_KFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = ITEM_KFV.ORGANIZATION_ID
AND MT.PLANNED_FLAG = MFG1.LOOKUP_CODE
AND MFG1.LOOKUP_TYPE = 'MST_TRIP_PLANNED_FLAG'
AND NVL(MT.SELECTED_FOR_RELEASE
, 2) = MFG2.LOOKUP_CODE
AND MFG2.LOOKUP_TYPE = 'MST_SELECTED_FOR_RELEASE'
AND NVL(MT.RELEASE_STATUS
, 5) = MFG3.LOOKUP_CODE
AND MFG3.LOOKUP_TYPE = 'MST_RELEASE_STATUS'
AND MT.SERVICE_LEVEL = WSH.LOOKUP_CODE
AND WSH.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'