VIEW: APPS.MST_ACT_UNASSIGNED_DELIVERY_V

Source

View Text - Preformatted

SELECT md.plan_id , md.delivery_id , md.delivery_number , md.pickup_location_id , md.dropoff_location_id , flp1.facility_id , flp2.facility_id , substr(MST_WB_UTIL.GET_NAME(md.pickup_location_id),1,80) , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(MST_WB_UTIL.GET_NAME(md.dropoff_location_id),1,80) , wl2.city , wl2.state , wl2.postal_code , wl2.country , md.earliest_pickup_date , md.latest_pickup_date , md.earliest_acceptable_date , md.latest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.latest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.earliest_acceptable_date),1,40) , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.latest_acceptable_date),1,40) , md.gross_weight , mp.weight_uom , md.volume , mp.volume_uom , md.number_of_pallets , md.number_of_pieces , mst_wb_util.get_delivery_orders(md.plan_id, md.delivery_id, null) from mst_plans mp, mst_deliveries md, wsh_locations wl1, wsh_locations wl2, fte_location_parameters flp1, fte_location_parameters flp2 WHERE mp.plan_id = md.plan_id and md.pickup_location_id = wl1.wsh_location_id and wl1.wsh_location_id = flp1.location_id and md.dropoff_location_id = wl2.wsh_location_id and wl2.wsh_location_id = flp2.location_id and (md.plan_id, md.delivery_id) not in (select plan_id, delivery_id from mst_delivery_legs)

View Text - HTML Formatted

SELECT MD.PLAN_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, MD.PICKUP_LOCATION_ID
, MD.DROPOFF_LOCATION_ID
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80)
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80)
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MD.EARLIEST_PICKUP_DATE
, MD.LATEST_PICKUP_DATE
, MD.EARLIEST_ACCEPTABLE_DATE
, MD.LATEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.LATEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.GROSS_WEIGHT
, MP.WEIGHT_UOM
, MD.VOLUME
, MP.VOLUME_UOM
, MD.NUMBER_OF_PALLETS
, MD.NUMBER_OF_PIECES
, MST_WB_UTIL.GET_DELIVERY_ORDERS(MD.PLAN_ID
, MD.DELIVERY_ID
, NULL)
FROM MST_PLANS MP
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
WHERE MP.PLAN_ID = MD.PLAN_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = FLP1.LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.WSH_LOCATION_ID = FLP2.LOCATION_ID
AND (MD.PLAN_ID
, MD.DELIVERY_ID) NOT IN (SELECT PLAN_ID
, DELIVERY_ID
FROM MST_DELIVERY_LEGS)