VIEW: APPS.MST_CNT_MOVE_LOADS_V

Source

View Text - Preformatted

SELECT T.PLAN_ID, T.ROWID ROW_ID, T.TRIP_ID, CMT.CONTINUOUS_MOVE_ID, T.TRIP_NUMBER, 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, T.TRIP_START_DATE) TRIP_START_DATE, SUBSTR( MST_GEOCODING.Get_timezone_code (WL1.WSH_location_id, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, T.TRIP_START_DATE) ),1,10) TIME_ZONE1, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, T.TRIP_END_DATE) TRIP_END_DATE, T.TOTAL_TRIP_DISTANCE, MST_WB_UTIL.GET_TRIP_STOPS(T.PLAN_ID, T.TRIP_ID) STOPS, (NVL(T.TOTAL_BASIC_TRANSPORT_COST,0) + NVL(T.TOTAL_STOP_COST,0) + NVL(T.TOTAL_LAYOVER_COST,0) + NVL(t.TOTAL_LOAD_UNLOAD_COST,0) + NVL(t.TOTAL_ACCESSORIAL_COST,0) + NVL(t.TOTAL_HANDLING_COST,0) ) TOTAL_COST, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (T.PLAN_ID, T.TRIP_ID)),1,80) FIRST_DEPARTURE_DATE, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (T.PLAN_ID, T.TRIP_ID)),1,80) LAST_ARRIVAL_DATE, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (T.PLAN_ID, T.TRIP_ID)) FIRST_DEPARTURE_DATE_HIDDEN, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (T.PLAN_ID, T.TRIP_ID)) LAST_ARRIVAL_DATE_HIDDEN, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'W') WEIGHT, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'V') CUBE, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'P') PALLETS, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'PC') PIECES, MST_WB_UTIL.GET_TRIP_ORDERS(T.PLAN_ID, T.TRIP_ID) ORDERS, T.PLANNED_FLAG FIRM_STATUS, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_TRIP_PLANNED_FLAG', T.PLANNED_FLAG, 'MFG'),1,80) FIRM_STATUS_DESC, T.RELEASE_DATE RELEASED, fnd_date.date_to_chardt(T.RELEASE_DATE), T.RELEASE_STATUS RELEASE_STATUS, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_RELEASE_STATUS', T.RELEASE_STATUS, 'MFG'),1,80) RELEASE_STATUS_DESC, T.SELECTED_FOR_RELEASE RELEASE, P.CURRENCY_UOM, P.WEIGHT_UOM, P.VOLUME_UOM FROM MST_PLANS P, MST_TRIPS T, MST_CM_TRIPS CMT, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2 WHERE P.PLAN_ID = CMT.PLAN_ID AND CMT.PLAN_ID = T.PLAN_ID AND CMT.CONTINUOUS_MOVE_ID = T.CONTINUOUS_MOVE_ID AND T.MODE_OF_TRANSPORT = 'TRUCK' AND T.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID AND T.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID

View Text - HTML Formatted

SELECT T.PLAN_ID
, T.ROWID ROW_ID
, T.TRIP_ID
, CMT.CONTINUOUS_MOVE_ID
, T.TRIP_NUMBER
, 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
, T.TRIP_START_DATE) TRIP_START_DATE
, SUBSTR( MST_GEOCODING.GET_TIMEZONE_CODE (WL1.WSH_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, T.TRIP_START_DATE) )
, 1
, 10) TIME_ZONE1
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, T.TRIP_END_DATE) TRIP_END_DATE
, T.TOTAL_TRIP_DISTANCE
, MST_WB_UTIL.GET_TRIP_STOPS(T.PLAN_ID
, T.TRIP_ID) STOPS
, (NVL(T.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(T.TOTAL_STOP_COST
, 0) + NVL(T.TOTAL_LAYOVER_COST
, 0) + NVL(T.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(T.TOTAL_ACCESSORIAL_COST
, 0) + NVL(T.TOTAL_HANDLING_COST
, 0) ) TOTAL_COST
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (T.PLAN_ID
, T.TRIP_ID))
, 1
, 80) FIRST_DEPARTURE_DATE
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (T.PLAN_ID
, T.TRIP_ID))
, 1
, 80) LAST_ARRIVAL_DATE
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, MST_WB_UTIL.GET_FIRST_DEPARTURE_DATE (T.PLAN_ID
, T.TRIP_ID)) FIRST_DEPARTURE_DATE_HIDDEN
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, MST_WB_UTIL.GET_LAST_ARRIVAL_DATE (T.PLAN_ID
, T.TRIP_ID)) LAST_ARRIVAL_DATE_HIDDEN
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'W') WEIGHT
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'V') CUBE
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'P') PALLETS
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'PC') PIECES
, MST_WB_UTIL.GET_TRIP_ORDERS(T.PLAN_ID
, T.TRIP_ID) ORDERS
, T.PLANNED_FLAG FIRM_STATUS
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_TRIP_PLANNED_FLAG'
, T.PLANNED_FLAG
, 'MFG')
, 1
, 80) FIRM_STATUS_DESC
, T.RELEASE_DATE RELEASED
, FND_DATE.DATE_TO_CHARDT(T.RELEASE_DATE)
, T.RELEASE_STATUS RELEASE_STATUS
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_RELEASE_STATUS'
, T.RELEASE_STATUS
, 'MFG')
, 1
, 80) RELEASE_STATUS_DESC
, T.SELECTED_FOR_RELEASE RELEASE
, P.CURRENCY_UOM
, P.WEIGHT_UOM
, P.VOLUME_UOM
FROM MST_PLANS P
, MST_TRIPS T
, MST_CM_TRIPS CMT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
WHERE P.PLAN_ID = CMT.PLAN_ID
AND CMT.PLAN_ID = T.PLAN_ID
AND CMT.CONTINUOUS_MOVE_ID = T.CONTINUOUS_MOVE_ID
AND T.MODE_OF_TRANSPORT = 'TRUCK'
AND T.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND T.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID