VIEW: APPS.MST_PQ_LOAD_DETAILS_V

Source

View Text - Preformatted

SELECT P.PLAN_ID, P.COMPILE_DESIGNATOR PLAN_NAME, T.TRIP_ID, T.TRIP_NUMBER, T.MODE_OF_TRANSPORT, T.CARRIER_ID, MDC.CUSTOMER_ID, MDS.SUPPLIER_ID, WC.FREIGHT_CODE CARRIER_NAME, substr( MST_WB_UTIL.Get_Partner_Name(MDC.CUSTOMER_ID, 1), 1,80) CUSTOMER_NAME, substr( MST_WB_UTIL.Get_Partner_Name(MDS.SUPPLIER_ID, 2), 1,80) SUPPLIER_NAME, LP3.FACILITY_ID FACILITY_ID, LP3.FACILITY_CODE FACILITY, T.ORIGIN_LOCATION_ID, T.DESTINATION_LOCATION_ID, LP1.FACILITY_ID ORIGIN_FACILITY_ID, LP1.FACILITY_CODE ORIGIN_FACILITY, WL1.CITY ORIGIN_CITY, WL1.STATE ORIGIN_STATE, WL1.POSTAL_CODE ORIGIN_ZIP, WL1.COUNTRY ORIGIN_COUNTRY, LP2.FACILITY_ID DESTINATION_FACILITY_ID, LP2.FACILITY_CODE DESTINATION_FACILITY, WL2.CITY DESTINATION_CITY, WL2.STATE DESTINATION_STATE, WL2.POSTAL_CODE DESTINATION_ZIP, WL2.COUNTRY DESTINATION_COUNTRY, round(((NVL(T.TRIP_START_DATE,SYSDATE)-SYSDATE)),2) DAYS_LEFT, round(((NVL(T.TRIP_START_DATE,SYSDATE)-SYSDATE) * 24),2) HOURS_LEFT, (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, MST_WB_UTIL.Get_Trip_Det(P.PLAN_ID,T.TRIP_ID,'W') TOTAL_WEIGHT, MST_WB_UTIL.Get_Trip_Det(P.PLAN_ID,T.TRIP_ID,'V') TOTAL_CUBE, round(greatest(PEAK_WEIGHT_UTILIZATION, PEAK_VOLUME_UTILIZATION, PEAK_PALLET_UTILIZATION ) * 100) TOTAL_UTILIZATION, MST_WB_UTIL.GET_TRIP_CIRCUITY (P.PLAN_ID, T.TRIP_ID) CIRCUITY, T.CONTINUOUS_MOVE_ID, T.CHANGED_BY_USER , P.CURRENCY_UOM CURRENCY_UOM FROM MST_PLANS P, MST_TRIPS T, MST_TRIP_STOPS TS, (SELECT DISTINCT MD1.PLAN_ID,MDL1.TRIP_ID, MD1.CUSTOMER_ID FROM MST_DELIVERIES MD1, MST_DELIVERY_LEGS MDL1 WHERE MD1.PLAN_ID = MDL1.PLAN_ID AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID )MDC, (SELECT DISTINCT MD2.PLAN_ID,MDL2.TRIP_ID, MD2.SUPPLIER_ID FROM MST_DELIVERIES MD2, MST_DELIVERY_LEGS MDL2 WHERE MD2.PLAN_ID = MDL2.PLAN_ID AND MD2.DELIVERY_ID = MDL2.DELIVERY_ID )MDS, WSH_CARRIERS WC, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2, FTE_LOCATION_PARAMETERS LP3 WHERE P.PLAN_ID = T.PLAN_ID AND T.PLAN_ID = MDC.PLAN_ID (+) AND T.TRIP_ID = MDC.TRIP_ID (+) AND T.PLAN_ID = MDS.PLAN_ID (+) AND T.TRIP_ID = MDS.TRIP_ID (+) AND T.PLAN_ID = TS.PLAN_ID AND T.TRIP_ID = TS.TRIP_ID AND TS.STOP_LOCATION_ID = LP3.LOCATION_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 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
, T.TRIP_ID
, T.TRIP_NUMBER
, T.MODE_OF_TRANSPORT
, T.CARRIER_ID
, MDC.CUSTOMER_ID
, MDS.SUPPLIER_ID
, WC.FREIGHT_CODE CARRIER_NAME
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(MDC.CUSTOMER_ID
, 1)
, 1
, 80) CUSTOMER_NAME
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(MDS.SUPPLIER_ID
, 2)
, 1
, 80) SUPPLIER_NAME
, LP3.FACILITY_ID FACILITY_ID
, LP3.FACILITY_CODE FACILITY
, T.ORIGIN_LOCATION_ID
, T.DESTINATION_LOCATION_ID
, LP1.FACILITY_ID ORIGIN_FACILITY_ID
, LP1.FACILITY_CODE ORIGIN_FACILITY
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, LP2.FACILITY_ID DESTINATION_FACILITY_ID
, LP2.FACILITY_CODE DESTINATION_FACILITY
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, ROUND(((NVL(T.TRIP_START_DATE
, SYSDATE)-SYSDATE))
, 2) DAYS_LEFT
, ROUND(((NVL(T.TRIP_START_DATE
, SYSDATE)-SYSDATE) * 24)
, 2) HOURS_LEFT
, (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
, MST_WB_UTIL.GET_TRIP_DET(P.PLAN_ID
, T.TRIP_ID
, 'W') TOTAL_WEIGHT
, MST_WB_UTIL.GET_TRIP_DET(P.PLAN_ID
, T.TRIP_ID
, 'V') TOTAL_CUBE
, ROUND(GREATEST(PEAK_WEIGHT_UTILIZATION
, PEAK_VOLUME_UTILIZATION
, PEAK_PALLET_UTILIZATION ) * 100) TOTAL_UTILIZATION
, MST_WB_UTIL.GET_TRIP_CIRCUITY (P.PLAN_ID
, T.TRIP_ID) CIRCUITY
, T.CONTINUOUS_MOVE_ID
, T.CHANGED_BY_USER
, P.CURRENCY_UOM CURRENCY_UOM
FROM MST_PLANS P
, MST_TRIPS T
, MST_TRIP_STOPS TS
, (SELECT DISTINCT MD1.PLAN_ID
, MDL1.TRIP_ID
, MD1.CUSTOMER_ID
FROM MST_DELIVERIES MD1
, MST_DELIVERY_LEGS MDL1
WHERE MD1.PLAN_ID = MDL1.PLAN_ID
AND MD1.DELIVERY_ID = MDL1.DELIVERY_ID )MDC
, (SELECT DISTINCT MD2.PLAN_ID
, MDL2.TRIP_ID
, MD2.SUPPLIER_ID
FROM MST_DELIVERIES MD2
, MST_DELIVERY_LEGS MDL2
WHERE MD2.PLAN_ID = MDL2.PLAN_ID
AND MD2.DELIVERY_ID = MDL2.DELIVERY_ID )MDS
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
, FTE_LOCATION_PARAMETERS LP3
WHERE P.PLAN_ID = T.PLAN_ID
AND T.PLAN_ID = MDC.PLAN_ID (+)
AND T.TRIP_ID = MDC.TRIP_ID (+)
AND T.PLAN_ID = MDS.PLAN_ID (+)
AND T.TRIP_ID = MDS.TRIP_ID (+)
AND T.PLAN_ID = TS.PLAN_ID
AND T.TRIP_ID = TS.TRIP_ID
AND TS.STOP_LOCATION_ID = LP3.LOCATION_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 WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID