VIEW: APPS.MST_TRUCKLOADS_V

Source

View Text - Preformatted

SELECT P.PLAN_ID, P.COMPILE_DESIGNATOR PLAN_NAME, P.DESCRIPTION DESCRIPTION, T.ROWID ROW_ID, T.TRIP_ID, T.TRIP_NUMBER, T.CARRIER_ID, WC.FREIGHT_CODE CARRIER, T.SERVICE_LEVEL SERVICE, SUBSTR(MST_WB_UTIL.GET_MEANING ('WSH_SERVICE_LEVELS', T.SERVICE_LEVEL, 'WSH'),1,80) SERVICE_DESC, T.VEHICLE_TYPE_ID, MSI.CONCATENATED_SEGMENTS VEHICLE, T.ORIGIN_LOCATION_ID, T.DESTINATION_LOCATION_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, T.TRIP_START_DATE TRIP_START_DATE, T.TRIP_END_DATE TRIP_END_DATE, T.TOTAL_TRIP_DISTANCE, P.DISTANCE_UOM, MST_WB_UTIL.GET_TRIP_CIRCUITY (P.PLAN_ID, T.TRIP_ID) CIRCUITY, MST_WB_UTIL.GET_TRIP_STOPS (P.PLAN_ID, T.TRIP_ID) STOPS, NVL(T.TOTAL_BASIC_TRANSPORT_COST,0) MILEAGE_CHARGE, NVL(T.TOTAL_STOP_COST,0) STOP_CHARGE, (NVL(T.TOTAL_ACCESSORIAL_COST,0) + NVL(T.TOTAL_LOAD_UNLOAD_COST,0) + NVL(T.TOTAL_LAYOVER_COST,0) + NVL(T.TOTAL_HANDLING_COST,0) ) OTHER_ACCESSORIAL_CHARGE, (NVL(T.TOTAL_BASIC_TRANSPORT_COST,0)+ NVL(T.TOTAL_LOAD_UNLOAD_COST,0) + NVL(T.TOTAL_HANDLING_COST,0) + NVL(T.TOTAL_STOP_COST,0) + NVL(T.TOTAL_LAYOVER_COST,0) + NVL(T.TOTAL_ACCESSORIAL_COST,0) ) TOTAL_COST, P.CURRENCY_UOM CURRENCY_UOM1, P.CURRENCY_UOM CURRENCY_UOM2, P.CURRENCY_UOM CURRENCY_UOM3, P.CURRENCY_UOM CURRENCY_UOM4, MST_WB_UTIL.GET_Trip_ORDERS (P.PLAN_ID, T.TRIP_ID) ORDERS, T.PLANNED_FLAG FIRM_STATUS, 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, T.CHANGED_BY_USER, T.LOCKED_FOR_IMPACT FROM MST_PLANS P, MST_TRIPS T, WSH_CARRIERS WC, MTL_SYSTEM_ITEMS_KFV MSI, FTE_VEHICLE_TYPES FVT, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2 WHERE P.PLAN_ID = T.PLAN_ID AND WC.CARRIER_ID = T.CARRIER_ID AND T.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID AND T.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID AND T.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID AND T.MODE_OF_TRANSPORT = 'TRUCK' AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID

View Text - HTML Formatted

SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR PLAN_NAME
, P.DESCRIPTION DESCRIPTION
, T.ROWID ROW_ID
, T.TRIP_ID
, T.TRIP_NUMBER
, T.CARRIER_ID
, WC.FREIGHT_CODE CARRIER
, T.SERVICE_LEVEL SERVICE
, SUBSTR(MST_WB_UTIL.GET_MEANING ('WSH_SERVICE_LEVELS'
, T.SERVICE_LEVEL
, 'WSH')
, 1
, 80) SERVICE_DESC
, T.VEHICLE_TYPE_ID
, MSI.CONCATENATED_SEGMENTS VEHICLE
, T.ORIGIN_LOCATION_ID
, T.DESTINATION_LOCATION_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
, T.TRIP_START_DATE TRIP_START_DATE
, T.TRIP_END_DATE TRIP_END_DATE
, T.TOTAL_TRIP_DISTANCE
, P.DISTANCE_UOM
, MST_WB_UTIL.GET_TRIP_CIRCUITY (P.PLAN_ID
, T.TRIP_ID) CIRCUITY
, MST_WB_UTIL.GET_TRIP_STOPS (P.PLAN_ID
, T.TRIP_ID) STOPS
, NVL(T.TOTAL_BASIC_TRANSPORT_COST
, 0) MILEAGE_CHARGE
, NVL(T.TOTAL_STOP_COST
, 0) STOP_CHARGE
, (NVL(T.TOTAL_ACCESSORIAL_COST
, 0) + NVL(T.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(T.TOTAL_LAYOVER_COST
, 0) + NVL(T.TOTAL_HANDLING_COST
, 0) ) OTHER_ACCESSORIAL_CHARGE
, (NVL(T.TOTAL_BASIC_TRANSPORT_COST
, 0)+ NVL(T.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(T.TOTAL_HANDLING_COST
, 0) + NVL(T.TOTAL_STOP_COST
, 0) + NVL(T.TOTAL_LAYOVER_COST
, 0) + NVL(T.TOTAL_ACCESSORIAL_COST
, 0) ) TOTAL_COST
, P.CURRENCY_UOM CURRENCY_UOM1
, P.CURRENCY_UOM CURRENCY_UOM2
, P.CURRENCY_UOM CURRENCY_UOM3
, P.CURRENCY_UOM CURRENCY_UOM4
, MST_WB_UTIL.GET_TRIP_ORDERS (P.PLAN_ID
, T.TRIP_ID) ORDERS
, T.PLANNED_FLAG FIRM_STATUS
, 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
, T.CHANGED_BY_USER
, T.LOCKED_FOR_IMPACT
FROM MST_PLANS P
, MST_TRIPS T
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_KFV MSI
, FTE_VEHICLE_TYPES FVT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
WHERE P.PLAN_ID = T.PLAN_ID
AND WC.CARRIER_ID = T.CARRIER_ID
AND T.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND T.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND T.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID
AND T.MODE_OF_TRANSPORT = 'TRUCK'
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID