VIEW: APPS.MST_PARCELS_V

Source

View Text - Preformatted

SELECT P.PLAN_ID, P.COMPILE_DESIGNATOR PLAN_NAME, P.DESCRIPTION DESCRIPTION, T.ROWID ROW_ID, T.TRIP_NUMBER, T.TRIP_ID, 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, 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, SUBSTR( MST_WB_UTIL.get_local_chardt (WL1.WSH_LOCATION_ID, T.TRIP_START_DATE),1,80) DEPARTURE_DATE_TIME, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, T.TRIP_START_DATE) DEP_DATETIME_HIDDEN, 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, SUBSTR( MST_WB_UTIL.get_local_chardt (WL2.WSH_LOCATION_ID, T.TRIP_END_DATE),1,80) ARRIVAL_DATE_TIME, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, T.TRIP_END_DATE) ARR_DATETIME_HIDDEN, SUBSTR( MST_GEOCODING.Get_timezone_code (WL2.WSH_location_id, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, T.TRIP_END_DATE) ),1,10) TIME_ZONE2, (nvl(T.TOTAL_BASIC_TRANSPORT_COST,0) + nvl(t.TOTAL_ACCESSORIAL_COST,0) ) COST, P.CURRENCY_UOM, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'W') WEIGHT, P.WEIGHT_UOM, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID, T.TRIP_ID,'V') CUBE, P.VOLUME_UOM, 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(P.PLAN_ID, T.TRIP_ID) ORDERS, T.PLANNED_FLAG FIRM_STATUS, T.RELEASE_STATUS RELEASE_STATUS_CODE, substr(MST_WB_UTIL.GET_MEANING ('MST_RELEASE_STATUS', T.RELEASE_STATUS,'MFG'),1,80) RELEASE_STATUS, T.RELEASE_DATE RELEASED, fnd_date.date_to_chardt(T.RELEASE_DATE), T.SELECTED_FOR_RELEASE RELEASE, T.CHANGED_BY_USER, T.LOCKED_FOR_IMPACT FROM MST_PLANS P, MST_TRIPS T, WSH_CARRIERS WC, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2 WHERE P.PLAN_ID = T.PLAN_ID AND T.MODE_OF_TRANSPORT = 'PARCEL' AND WC.CARRIER_ID = T.CARRIER_ID AND WL1.WSH_LOCATION_ID = T.ORIGIN_LOCATION_ID AND WL2.WSH_LOCATION_ID = T.DESTINATION_LOCATION_ID 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_NUMBER
, T.TRIP_ID
, 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
, 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
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDT (WL1.WSH_LOCATION_ID
, T.TRIP_START_DATE)
, 1
, 80) DEPARTURE_DATE_TIME
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, T.TRIP_START_DATE) DEP_DATETIME_HIDDEN
, 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
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDT (WL2.WSH_LOCATION_ID
, T.TRIP_END_DATE)
, 1
, 80) ARRIVAL_DATE_TIME
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, T.TRIP_END_DATE) ARR_DATETIME_HIDDEN
, SUBSTR( MST_GEOCODING.GET_TIMEZONE_CODE (WL2.WSH_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, T.TRIP_END_DATE) )
, 1
, 10) TIME_ZONE2
, (NVL(T.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(T.TOTAL_ACCESSORIAL_COST
, 0) ) COST
, P.CURRENCY_UOM
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'W') WEIGHT
, P.WEIGHT_UOM
, MST_WB_UTIL.GET_TRIP_DET (T.PLAN_ID
, T.TRIP_ID
, 'V') CUBE
, P.VOLUME_UOM
, 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(P.PLAN_ID
, T.TRIP_ID) ORDERS
, T.PLANNED_FLAG FIRM_STATUS
, T.RELEASE_STATUS RELEASE_STATUS_CODE
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_RELEASE_STATUS'
, T.RELEASE_STATUS
, 'MFG')
, 1
, 80) RELEASE_STATUS
, T.RELEASE_DATE RELEASED
, FND_DATE.DATE_TO_CHARDT(T.RELEASE_DATE)
, T.SELECTED_FOR_RELEASE RELEASE
, T.CHANGED_BY_USER
, T.LOCKED_FOR_IMPACT
FROM MST_PLANS P
, MST_TRIPS T
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
WHERE P.PLAN_ID = T.PLAN_ID
AND T.MODE_OF_TRANSPORT = 'PARCEL'
AND WC.CARRIER_ID = T.CARRIER_ID
AND WL1.WSH_LOCATION_ID = T.ORIGIN_LOCATION_ID
AND WL2.WSH_LOCATION_ID = T.DESTINATION_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID