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_LEG_DELIVERIES_V

Source

View Text - Preformatted


SELECT D.PLAN_ID, D.DELIVERY_ID, D.PICKUP_LOCATION_ID, D.DROPOFF_LOCATION_ID, TS1.STOP_ID STOP_ID1, TS2.STOP_ID STOP_ID2, DL.PICK_UP_STOP_ID, DL.DROP_OFF_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, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, D.PICKUP_DATE),1,80) SHIP_DATE, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL2.WSH_LOCATION_ID, D.DROPOFF_DATE),1,80) DELIVER_DATE, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, D.PICKUP_DATE) SHIP_DATE_HIDDEN, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, D.DROPOFF_DATE) DELIVER_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 (D.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, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, 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 FROM MST_PLANS MP, MST_DELIVERIES D, MST_DELIVERY_LEGS DL, 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 = DL.PLAN_ID AND (( DL.PICK_UP_STOP_ID = TS1.STOP_ID OR DL.DROP_OFF_STOP_ID = TS2.STOP_ID) OR ( dl.pick_up_stop_id IN ( SELECT ts11.stop_id FROM mst_trip_stops ts11 WHERE ts11.plan_id = ts1.plan_id AND ts11.trip_id = ts1.trip_id AND ts11.stop_sequence_number < ts1.stop_sequence_number ) AND dl.drop_off_stop_id IN ( SELECT ts12.stop_id FROM mst_trip_stops ts12 WHERE ts12.plan_id = ts2.plan_id AND ts12.trip_id = ts2.trip_id AND ts12.stop_sequence_number > ts2.stop_sequence_number ))) AND TS1.PLAN_ID = DL.PLAN_ID AND TS2.PLAN_ID = DL.PLAN_ID AND ts1.trip_id = ts2.trip_id AND ts1.stop_id <> ts2.stop_id AND DL.DELIVERY_ID = D.DELIVERY_ID AND DL.PLAN_ID = D.PLAN_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
, TS1.STOP_ID STOP_ID1
, TS2.STOP_ID STOP_ID2
, DL.PICK_UP_STOP_ID
, DL.DROP_OFF_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
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, D.PICKUP_DATE)
, 1
, 80) SHIP_DATE
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL2.WSH_LOCATION_ID
, D.DROPOFF_DATE)
, 1
, 80) DELIVER_DATE
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, D.PICKUP_DATE) SHIP_DATE_HIDDEN
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, D.DROPOFF_DATE) DELIVER_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 (D.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
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, 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
FROM MST_PLANS MP
, MST_DELIVERIES D
, MST_DELIVERY_LEGS DL
, 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 = DL.PLAN_ID
AND (( DL.PICK_UP_STOP_ID = TS1.STOP_ID OR DL.DROP_OFF_STOP_ID = TS2.STOP_ID) OR ( DL.PICK_UP_STOP_ID IN ( SELECT TS11.STOP_ID
FROM MST_TRIP_STOPS TS11
WHERE TS11.PLAN_ID = TS1.PLAN_ID
AND TS11.TRIP_ID = TS1.TRIP_ID
AND TS11.STOP_SEQUENCE_NUMBER < TS1.STOP_SEQUENCE_NUMBER )
AND DL.DROP_OFF_STOP_ID IN ( SELECT TS12.STOP_ID
FROM MST_TRIP_STOPS TS12
WHERE TS12.PLAN_ID = TS2.PLAN_ID
AND TS12.TRIP_ID = TS2.TRIP_ID
AND TS12.STOP_SEQUENCE_NUMBER > TS2.STOP_SEQUENCE_NUMBER )))
AND TS1.PLAN_ID = DL.PLAN_ID
AND TS2.PLAN_ID = DL.PLAN_ID
AND TS1.TRIP_ID = TS2.TRIP_ID
AND TS1.STOP_ID <> TS2.STOP_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = D.PLAN_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