eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MST_STOP_DELIVERIES_V

Source

View Text - Preformatted


SELECT D.PLAN_ID, D.DELIVERY_ID, D.PICKUP_LOCATION_ID, D.DROPOFF_LOCATION_ID, DL.PICK_UP_STOP_ID, DL.DROP_OFF_STOP_ID, TS.TRIP_ID, TS.STOP_ID, D.DELIVERY_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_DESCRIPTION, 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_DESCRIPTION, WL2.CITY DESTINATION_CITY, WL2.STATE DESTINATION_STATE, WL2.POSTAL_CODE DESTINATION_ZIP, WL2.COUNTRY DESTINATION_COUNTRY, DECODE(TS.STOP_ID,DL.PICK_UP_STOP_ID, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_STRING', 31,'MFG'),1,10), DL.DROP_OFF_STOP_ID, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_STRING', 32,'MFG'),1,10), NULL) LOAD_UNLOAD_FLAG, DECODE(TS.STOP_LOCATION_ID, D.PICKUP_LOCATION_ID, SUBSTR( MST_WB_UTIL.get_local_chardtzone (D.PICKUP_LOCATION_ID, D.EARLIEST_PICKUP_DATE),1,80), D.DROPOFF_LOCATION_ID, SUBSTR( MST_WB_UTIL.get_local_chardtzone (D.DROPOFF_LOCATION_ID, D.EARLIEST_ACCEPTABLE_DATE),1,80), NULL) EARLIEST_ALLOWED_DATE_TIME, DECODE(TS.STOP_LOCATION_ID, D.PICKUP_LOCATION_ID, SUBSTR( MST_WB_UTIL.get_local_chardtzone (D.PICKUP_LOCATION_ID, D.LATEST_PICKUP_DATE),1,80), D.DROPOFF_LOCATION_ID, SUBSTR( MST_WB_UTIL.get_local_chardtzone (D.DROPOFF_LOCATION_ID, D.LATEST_ACCEPTABLE_DATE),1,80), NULL) LATEST_ALLOWED_DATE_TIME, DECODE(TS.STOP_LOCATION_ID, D.PICKUP_LOCATION_ID, MST_GEOCODING.Get_local_time (D.PICKUP_LOCATION_ID, D.EARLIEST_PICKUP_DATE), D.DROPOFF_LOCATION_ID, MST_GEOCODING.Get_local_time (D.DROPOFF_LOCATION_ID, D.EARLIEST_ACCEPTABLE_DATE), NULL ) EARLY_ALLOWED_DATE_HIDDEN, DECODE(TS.STOP_LOCATION_ID, D.PICKUP_LOCATION_ID, MST_GEOCODING.Get_local_time (D.PICKUP_LOCATION_ID, D.LATEST_PICKUP_DATE), D.DROPOFF_LOCATION_ID, MST_GEOCODING.Get_local_time (D.DROPOFF_LOCATION_ID, D.LATEST_ACCEPTABLE_DATE), NULL ) LATEST_ALLOWED_DATE_HIDDEN, D.GROSS_WEIGHT WEIGHT, D.VOLUME CUBE, D.NUMBER_OF_PALLETS PALLETS, D.NUMBER_OF_PIECES PIECES, MST_WB_UTIL.Get_DELIVERY_Orders (TS.Plan_Id , D.DELIVERY_ID,'1') ORDERS, MST_AGG_PKG.Get_alloc_cost_for_delivery ( D.Plan_Id , D.DELIVERY_ID) ALLOCATED_COST, D.DIRECT_COST, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_DELIVERY_PLANNED_FLAG', DECODE(D.PLANNED_FLAG, 1, 1, 2, DECODE(D.PRESERVE_GROUPING_FLAG, 1, 2, 2, 3, 3), 3), 'MFG'),1,80) FIRM_STATUS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM From MST_PLANS MP, MST_DELIVERIES D, MST_DELIVERY_LEGS DL, MST_TRIP_STOPS TS, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2 WHERE MP.PLAN_ID = DL.PLAN_ID AND (DL.PICK_UP_STOP_ID = TS.STOP_ID or DL.DROP_OFF_STOP_ID = TS.STOP_ID) AND TS.PLAN_ID = DL.PLAN_ID AND DL.PLAN_ID = D.PLAN_ID AND DL.DELIVERY_ID = D.DELIVERY_ID AND WL1.wsh_LOCATION_ID = D.PICKUP_LOCATION_ID AND WL2.wsh_LOCATION_ID = D.DROPOFF_LOCATION_ID AND WL1.wsh_LOCATION_ID = LP1.LOCATION_ID AND WL2.wsh_LOCATION_ID = LP2.LOCATION_ID

View Text - HTML Formatted

SELECT D.PLAN_ID
, D.DELIVERY_ID
, D.PICKUP_LOCATION_ID
, D.DROPOFF_LOCATION_ID
, DL.PICK_UP_STOP_ID
, DL.DROP_OFF_STOP_ID
, TS.TRIP_ID
, TS.STOP_ID
, D.DELIVERY_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_DESCRIPTION
, 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_DESCRIPTION
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, DECODE(TS.STOP_ID
, DL.PICK_UP_STOP_ID
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_STRING'
, 31
, 'MFG')
, 1
, 10)
, DL.DROP_OFF_STOP_ID
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_STRING'
, 32
, 'MFG')
, 1
, 10)
, NULL) LOAD_UNLOAD_FLAG
, DECODE(TS.STOP_LOCATION_ID
, D.PICKUP_LOCATION_ID
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (D.PICKUP_LOCATION_ID
, D.EARLIEST_PICKUP_DATE)
, 1
, 80)
, D.DROPOFF_LOCATION_ID
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (D.DROPOFF_LOCATION_ID
, D.EARLIEST_ACCEPTABLE_DATE)
, 1
, 80)
, NULL) EARLIEST_ALLOWED_DATE_TIME
, DECODE(TS.STOP_LOCATION_ID
, D.PICKUP_LOCATION_ID
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (D.PICKUP_LOCATION_ID
, D.LATEST_PICKUP_DATE)
, 1
, 80)
, D.DROPOFF_LOCATION_ID
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (D.DROPOFF_LOCATION_ID
, D.LATEST_ACCEPTABLE_DATE)
, 1
, 80)
, NULL) LATEST_ALLOWED_DATE_TIME
, DECODE(TS.STOP_LOCATION_ID
, D.PICKUP_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (D.PICKUP_LOCATION_ID
, D.EARLIEST_PICKUP_DATE)
, D.DROPOFF_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (D.DROPOFF_LOCATION_ID
, D.EARLIEST_ACCEPTABLE_DATE)
, NULL ) EARLY_ALLOWED_DATE_HIDDEN
, DECODE(TS.STOP_LOCATION_ID
, D.PICKUP_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (D.PICKUP_LOCATION_ID
, D.LATEST_PICKUP_DATE)
, D.DROPOFF_LOCATION_ID
, MST_GEOCODING.GET_LOCAL_TIME (D.DROPOFF_LOCATION_ID
, D.LATEST_ACCEPTABLE_DATE)
, NULL ) LATEST_ALLOWED_DATE_HIDDEN
, D.GROSS_WEIGHT WEIGHT
, D.VOLUME CUBE
, D.NUMBER_OF_PALLETS PALLETS
, D.NUMBER_OF_PIECES PIECES
, MST_WB_UTIL.GET_DELIVERY_ORDERS (TS.PLAN_ID
, D.DELIVERY_ID
, '1') ORDERS
, MST_AGG_PKG.GET_ALLOC_COST_FOR_DELIVERY ( D.PLAN_ID
, D.DELIVERY_ID) ALLOCATED_COST
, D.DIRECT_COST
, SUBSTR(MST_WB_UTIL.GET_MEANING ('MST_DELIVERY_PLANNED_FLAG'
, DECODE(D.PLANNED_FLAG
, 1
, 1
, 2
, DECODE(D.PRESERVE_GROUPING_FLAG
, 1
, 2
, 2
, 3
, 3)
, 3)
, 'MFG')
, 1
, 80) FIRM_STATUS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_DELIVERIES D
, MST_DELIVERY_LEGS DL
, MST_TRIP_STOPS TS
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
WHERE MP.PLAN_ID = DL.PLAN_ID
AND (DL.PICK_UP_STOP_ID = TS.STOP_ID OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = DL.PLAN_ID
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND WL1.WSH_LOCATION_ID = D.PICKUP_LOCATION_ID
AND WL2.WSH_LOCATION_ID = D.DROPOFF_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID