VIEW: APPS.MST_UNASSIGNED_DELIVERY_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_detail_id , med.exception_type , med.delivery_id , md.delivery_number , substr(mst_wb_util.get_name(md.pickup_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(md.dropoff_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.earliest_pickup_date),1,40) , md.earliest_pickup_date , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.latest_pickup_date),1,40) , md.latest_pickup_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.earliest_acceptable_date),1,40) , md.earliest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.latest_acceptable_date),1,40) , md.latest_acceptable_date , nvl(md.gross_weight, 0) , mp.weight_uom , nvl(md.volume, 0) , mp.volume_uom , nvl(md.number_of_pallets, 0) , nvl(md.number_of_pieces, 0) , ml.meaning , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_deliveries md , mst_plans mp , wsh_locations wl1 , wsh_locations wl2 , mfg_lookups ml WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mp.plan_id and med.plan_id = md.plan_id and med.delivery_id = md.delivery_id and md.pickup_location_id = wl1.wsh_location_id and md.dropoff_location_id = wl2.wsh_location_id

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MED.DELIVERY_ID
, MD.DELIVERY_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, MD.EARLIEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.LATEST_PICKUP_DATE)
, 1
, 40)
, MD.LATEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.EARLIEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.LATEST_ACCEPTABLE_DATE
, NVL(MD.GROSS_WEIGHT
, 0)
, MP.WEIGHT_UOM
, NVL(MD.VOLUME
, 0)
, MP.VOLUME_UOM
, NVL(MD.NUMBER_OF_PALLETS
, 0)
, NVL(MD.NUMBER_OF_PIECES
, 0)
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_DELIVERIES MD
, MST_PLANS MP
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MFG_LOOKUPS ML
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.PLAN_ID = MD.PLAN_ID
AND MED.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID