VIEW: APPS.MST_LOAD_LEGS_V

Source

View Text - Preformatted

SELECT T.Plan_id, MST_WB_UTIL.GET_LEG_NUMBER (TS1.PLAN_ID, TS1.TRIP_ID,TS1.STOP_ID) Leg, T.Trip_id, TS1.ROWID ROW_ID, TS1.STOP_ID STOP_ID1, TS2.STOP_ID STOP_ID2, TS1.STOP_LOCATION_ID ORIGIN_LOCATION_ID, TS2.STOP_LOCATION_ID DESTINATION_LOCATION_ID, LP1.FACILITY_ID ORIGIN_FACILITY_ID, SUBSTR(MST_WB_UTIL.Get_name (WL1.WSH_LOCATION_ID), 1,80) Org_Company, WL1.City ORG_City, WL1.State Org_State, WL1.POSTAL_CODE ORG_ZIP, WL1.COUNTRY ORG_COUNTRY, LP2.FACILITY_ID DESTINATION_FACILITY_ID, SUBSTR(MST_WB_UTIL.Get_name (WL2.WSH_LOCATION_ID), 1,80) Dest_COMPANY, WL2.City Dest_City, WL2.State Dest_State, WL2.POSTAL_CODE DEST_ZIP, WL2.COUNTRY DEST_COUNTRY, SUBSTR(MST_WB_UTIL.get_local_chardtzone (WL2.WSH_LOCATION_ID, TS2.PLANNED_ARRIVAL_DATE),1,80) ARRIVAL_DATE_TIME, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, TS2.PLANNED_ARRIVAL_DATE) ARR_DATETIME_HIDDEN, SUBSTR( MST_GEOCODING.Get_timezone_code (WL2.WSH_LOCATION_ID, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, TS2.PLANNED_ARRIVAL_DATE)),1,10) ARRIVAL_TIME_ZONE, TS1.DISTANCE_TO_NEXT_STOP DISTANCE, SUBSTR(MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, TS1.PLANNED_DEPARTURE_DATE),1,80) DEPARTURE_DATE_TIME, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, TS1.PLANNED_DEPARTURE_DATE) DEP_DATETIME_HIDDEN, SUBSTR( MST_GEOCODING.Get_timezone_code (WL1.WSH_LOCATION_ID, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, TS1.PLANNED_DEPARTURE_DATE)),1,10) DEPARTURE_TIME_ZONE, TS1.DISTANCE_COST MILEAGE_CHARGE, SUBSTR(MST_WB_UTIL.get_hr_min(TS1.DRV_TIME_TO_NEXT_STOP),1,12) DRIVE_TIME, SUBSTR(MST_WB_UTIL.get_hr_min (decode(sign((TS2.PLANNED_ARRIVAL_DATE - TS1.PLANNED_DEPARTURE_DATE)*24 - TS1.DRV_TIME_TO_NEXT_STOP - TS1.TOTAL_LAYOVER_DURATION ) ,-1,0,((TS2.PLANNED_ARRIVAL_DATE - TS1.PLANNED_DEPARTURE_DATE)*24 - TS1.DRV_TIME_TO_NEXT_STOP - TS1.TOTAL_LAYOVER_DURATION ))), 1,12) WAIT_TIME, SUBSTR(MST_WB_UTIL.get_hr_min(TS1.TOTAL_LAYOVER_DURATION),1,12) Layover_Time, TS1.DEPARTURE_GROSS_WEIGHT Weight, TS1.DEPARTURE_VOLUME Cube, TS1.DEPARTURE_PALLETS Pallets, TS1.DEPARTURE_PIECES Pieces, MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID, T.VEHICLE_TYPE_ID, 'W') Max_Weight, T.EFFECTIVE_VOL_CAPACITY Max_Cube, MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID, T.VEHICLE_TYPE_ID, 'P') Max_Pallets, ROUND((TS1.DEPARTURE_GROSS_WEIGHT/ MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID, T.VEHICLE_TYPE_ID, 'W')) * 100 ) Weight_Utilization, ROUND((TS1.DEPARTURE_VOLUME/ T.EFFECTIVE_VOL_CAPACITY) * 100 ) Cube_Utilization, ROUND((TS1.DEPARTURE_PALLETS/ MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID, T.VEHICLE_TYPE_ID, 'P')) * 100 ) Pallet_Utilization, MST_WB_UTIL.Get_stop_Orders (TS1.Plan_Id , TS1.TRIP_ID ,TS1.STOP_ID) ORDERS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, MST_TRIPS T, MST_TRIP_STOPS TS1, MST_TRIP_STOPS TS2, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2 WHERE MP.PLAN_ID = T.PLAN_ID AND T.TRIP_ID = TS1.TRIP_ID AND T.PLAN_ID = TS1.PLAN_ID AND T.TRIP_ID = TS2.TRIP_ID AND T.PLAN_ID = TS2.PLAN_ID AND TS1.PLAN_ID = TS2.PLAN_ID AND TS1.TRIP_ID = TS2.TRIP_ID AND TS2.STOP_SEQUENCE_NUMBER = (SELECT MIN(TS3.STOP_SEQUENCE_NUMBER) FROM MST_TRIP_STOPS TS3 WHERE TS3.PLAN_ID = TS1.PLAN_ID AND TS3.TRIP_ID = TS1.TRIP_ID AND TS3.STOP_SEQUENCE_NUMBER > TS1.STOP_SEQUENCE_NUMBER) AND TS1.STOP_LOCATION_ID = WL1.WSH_LOCATION_ID AND TS2.STOP_LOCATION_ID = WL2.WSH_LOCATION_ID AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID AND T.MODE_OF_TRANSPORT = 'TRUCK'

View Text - HTML Formatted

SELECT T.PLAN_ID
, MST_WB_UTIL.GET_LEG_NUMBER (TS1.PLAN_ID
, TS1.TRIP_ID
, TS1.STOP_ID) LEG
, T.TRIP_ID
, TS1.ROWID ROW_ID
, TS1.STOP_ID STOP_ID1
, TS2.STOP_ID STOP_ID2
, TS1.STOP_LOCATION_ID ORIGIN_LOCATION_ID
, TS2.STOP_LOCATION_ID DESTINATION_LOCATION_ID
, LP1.FACILITY_ID ORIGIN_FACILITY_ID
, SUBSTR(MST_WB_UTIL.GET_NAME (WL1.WSH_LOCATION_ID)
, 1
, 80) ORG_COMPANY
, WL1.CITY ORG_CITY
, WL1.STATE ORG_STATE
, WL1.POSTAL_CODE ORG_ZIP
, WL1.COUNTRY ORG_COUNTRY
, LP2.FACILITY_ID DESTINATION_FACILITY_ID
, SUBSTR(MST_WB_UTIL.GET_NAME (WL2.WSH_LOCATION_ID)
, 1
, 80) DEST_COMPANY
, WL2.CITY DEST_CITY
, WL2.STATE DEST_STATE
, WL2.POSTAL_CODE DEST_ZIP
, WL2.COUNTRY DEST_COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL2.WSH_LOCATION_ID
, TS2.PLANNED_ARRIVAL_DATE)
, 1
, 80) ARRIVAL_DATE_TIME
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, TS2.PLANNED_ARRIVAL_DATE) ARR_DATETIME_HIDDEN
, SUBSTR( MST_GEOCODING.GET_TIMEZONE_CODE (WL2.WSH_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, TS2.PLANNED_ARRIVAL_DATE))
, 1
, 10) ARRIVAL_TIME_ZONE
, TS1.DISTANCE_TO_NEXT_STOP DISTANCE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, TS1.PLANNED_DEPARTURE_DATE)
, 1
, 80) DEPARTURE_DATE_TIME
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, TS1.PLANNED_DEPARTURE_DATE) DEP_DATETIME_HIDDEN
, SUBSTR( MST_GEOCODING.GET_TIMEZONE_CODE (WL1.WSH_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, TS1.PLANNED_DEPARTURE_DATE))
, 1
, 10) DEPARTURE_TIME_ZONE
, TS1.DISTANCE_COST MILEAGE_CHARGE
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(TS1.DRV_TIME_TO_NEXT_STOP)
, 1
, 12) DRIVE_TIME
, SUBSTR(MST_WB_UTIL.GET_HR_MIN (DECODE(SIGN((TS2.PLANNED_ARRIVAL_DATE - TS1.PLANNED_DEPARTURE_DATE)*24 - TS1.DRV_TIME_TO_NEXT_STOP - TS1.TOTAL_LAYOVER_DURATION )
, -1
, 0
, ((TS2.PLANNED_ARRIVAL_DATE - TS1.PLANNED_DEPARTURE_DATE)*24 - TS1.DRV_TIME_TO_NEXT_STOP - TS1.TOTAL_LAYOVER_DURATION )))
, 1
, 12) WAIT_TIME
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(TS1.TOTAL_LAYOVER_DURATION)
, 1
, 12) LAYOVER_TIME
, TS1.DEPARTURE_GROSS_WEIGHT WEIGHT
, TS1.DEPARTURE_VOLUME CUBE
, TS1.DEPARTURE_PALLETS PALLETS
, TS1.DEPARTURE_PIECES PIECES
, MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID
, T.VEHICLE_TYPE_ID
, 'W') MAX_WEIGHT
, T.EFFECTIVE_VOL_CAPACITY MAX_CUBE
, MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID
, T.VEHICLE_TYPE_ID
, 'P') MAX_PALLETS
, ROUND((TS1.DEPARTURE_GROSS_WEIGHT/ MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID
, T.VEHICLE_TYPE_ID
, 'W')) * 100 ) WEIGHT_UTILIZATION
, ROUND((TS1.DEPARTURE_VOLUME/ T.EFFECTIVE_VOL_CAPACITY) * 100 ) CUBE_UTILIZATION
, ROUND((TS1.DEPARTURE_PALLETS/ MST_WB_UTIL.GET_VEHICLE_CAPACITY (TS1.PLAN_ID
, T.VEHICLE_TYPE_ID
, 'P')) * 100 ) PALLET_UTILIZATION
, MST_WB_UTIL.GET_STOP_ORDERS (TS1.PLAN_ID
, TS1.TRIP_ID
, TS1.STOP_ID) ORDERS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_TRIPS T
, MST_TRIP_STOPS TS1
, MST_TRIP_STOPS TS2
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
WHERE MP.PLAN_ID = T.PLAN_ID
AND T.TRIP_ID = TS1.TRIP_ID
AND T.PLAN_ID = TS1.PLAN_ID
AND T.TRIP_ID = TS2.TRIP_ID
AND T.PLAN_ID = TS2.PLAN_ID
AND TS1.PLAN_ID = TS2.PLAN_ID
AND TS1.TRIP_ID = TS2.TRIP_ID
AND TS2.STOP_SEQUENCE_NUMBER = (SELECT MIN(TS3.STOP_SEQUENCE_NUMBER)
FROM MST_TRIP_STOPS TS3
WHERE TS3.PLAN_ID = TS1.PLAN_ID
AND TS3.TRIP_ID = TS1.TRIP_ID
AND TS3.STOP_SEQUENCE_NUMBER > TS1.STOP_SEQUENCE_NUMBER)
AND TS1.STOP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND TS2.STOP_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID
AND T.MODE_OF_TRANSPORT = 'TRUCK'