VIEW: APPS.MST_DELIVERY_LINES_V

Source

View Text - Preformatted

SELECT D.PLAN_ID, D.DELIVERY_ID, DECODE(DD.SPLIT_FROM_DELIVERY_DETAIL_ID,NULL,DD.SR_DELIVERY_DETAIL_ID,DD.DELIVERY_DETAIL_ID), D.DELIVERY_NUMBER, DD.SOURCE_LINE_NUMBER, DL.TRIP_ID, DD.SOURCE_CODE, DD.SOURCE_HEADER_NUMBER, DD.TP_REFER_HEADER_NUMBER, DD.REFERENCE_SOURCE_TYPE, DD.SHIP_FROM_LOCATION_ID, DD.SHIP_TO_LOCATION_ID, DL.PICK_UP_STOP_ID, DL.DROP_OFF_STOP_ID, 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, DD.INVENTORY_ITEM_ID, MSIKFV.CONCATENATED_SEGMENTS ITEM_NAME, MSITL.DESCRIPTION ITEM_DESCRIPTION, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, DD.EARLIEST_PICKUP_DATE),1,80) EARLIEST_PICKUP_DATE_TIME, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL1.WSH_LOCATION_ID, DD.LATEST_PICKUP_DATE),1,80) LATEST_PICKUP_DATE_TIME, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL2.WSH_LOCATION_ID, DD.EARLIEST_ACCEPTABLE_DATE),1,80) EARLIEST_ACCEPTABLE_DATE_TIME, SUBSTR( MST_WB_UTIL.get_local_chardtzone (WL2.WSH_LOCATION_ID, DD.LATEST_ACCEPTABLE_DATE),1,80) LATEST_ACCEPTABLE_DATE_TIME, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, DD.EARLIEST_PICKUP_DATE) EARLY_PICKUP_DATE_HIDDEN, MST_GEOCODING.Get_local_time (WL1.WSH_LOCATION_ID, DD.LATEST_PICKUP_DATE) LATEST_PICKUP_DATE_HIDDEN, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, DD.EARLIEST_ACCEPTABLE_DATE) EARLY_ACCEPTABLE_DATE_HIDDEN, MST_GEOCODING.Get_local_time (WL2.WSH_LOCATION_ID, DD.LATEST_ACCEPTABLE_DATE) LATEST_ACCEPTABLE_DATE_HIDDEN, DD.GROSS_WEIGHT WEIGHT, DD.VOLUME CUBE, ROUND(DD.NUMBER_OF_PALLETS) PALLETS, DD.REQUESTED_QUANTITY PIECES, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, MST_DELIVERIES D, MST_DELIVERY_LEGS DL, MST_DELIVERY_ASSIGNMENTS DA, MST_DELIVERY_DETAILS DD, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2, MTL_SYSTEM_ITEMS_KFV MSIKFV, MTL_SYSTEM_ITEMS_TL MSITL WHERE MP.PLAN_ID = DL.PLAN_ID AND DL.PLAN_ID = D.PLAN_ID AND DL.DELIVERY_ID = D.DELIVERY_ID AND DD.PLAN_ID = DL.PLAN_ID AND DA.PLAN_ID = DD.PLAN_ID AND D.DELIVERY_ID = DA.DELIVERY_ID AND DA.DELIVERY_DETAIL_ID = DD.DELIVERY_DETAIL_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 AND DD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID AND DD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID AND DD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID AND DD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID AND MSITL.LANGUAGE = USERENV('LANG')

View Text - HTML Formatted

SELECT D.PLAN_ID
, D.DELIVERY_ID
, DECODE(DD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, DD.SR_DELIVERY_DETAIL_ID
, DD.DELIVERY_DETAIL_ID)
, D.DELIVERY_NUMBER
, DD.SOURCE_LINE_NUMBER
, DL.TRIP_ID
, DD.SOURCE_CODE
, DD.SOURCE_HEADER_NUMBER
, DD.TP_REFER_HEADER_NUMBER
, DD.REFERENCE_SOURCE_TYPE
, DD.SHIP_FROM_LOCATION_ID
, DD.SHIP_TO_LOCATION_ID
, DL.PICK_UP_STOP_ID
, DL.DROP_OFF_STOP_ID
, 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
, DD.INVENTORY_ITEM_ID
, MSIKFV.CONCATENATED_SEGMENTS ITEM_NAME
, MSITL.DESCRIPTION ITEM_DESCRIPTION
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, DD.EARLIEST_PICKUP_DATE)
, 1
, 80) EARLIEST_PICKUP_DATE_TIME
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL1.WSH_LOCATION_ID
, DD.LATEST_PICKUP_DATE)
, 1
, 80) LATEST_PICKUP_DATE_TIME
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL2.WSH_LOCATION_ID
, DD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 80) EARLIEST_ACCEPTABLE_DATE_TIME
, SUBSTR( MST_WB_UTIL.GET_LOCAL_CHARDTZONE (WL2.WSH_LOCATION_ID
, DD.LATEST_ACCEPTABLE_DATE)
, 1
, 80) LATEST_ACCEPTABLE_DATE_TIME
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, DD.EARLIEST_PICKUP_DATE) EARLY_PICKUP_DATE_HIDDEN
, MST_GEOCODING.GET_LOCAL_TIME (WL1.WSH_LOCATION_ID
, DD.LATEST_PICKUP_DATE) LATEST_PICKUP_DATE_HIDDEN
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, DD.EARLIEST_ACCEPTABLE_DATE) EARLY_ACCEPTABLE_DATE_HIDDEN
, MST_GEOCODING.GET_LOCAL_TIME (WL2.WSH_LOCATION_ID
, DD.LATEST_ACCEPTABLE_DATE) LATEST_ACCEPTABLE_DATE_HIDDEN
, DD.GROSS_WEIGHT WEIGHT
, DD.VOLUME CUBE
, ROUND(DD.NUMBER_OF_PALLETS) PALLETS
, DD.REQUESTED_QUANTITY PIECES
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_DELIVERIES D
, MST_DELIVERY_LEGS DL
, MST_DELIVERY_ASSIGNMENTS DA
, MST_DELIVERY_DETAILS DD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
WHERE MP.PLAN_ID = DL.PLAN_ID
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DD.PLAN_ID = DL.PLAN_ID
AND DA.PLAN_ID = DD.PLAN_ID
AND D.DELIVERY_ID = DA.DELIVERY_ID
AND DA.DELIVERY_DETAIL_ID = DD.DELIVERY_DETAIL_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
AND DD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND DD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND DD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND DD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND MSITL.LANGUAGE = USERENV('LANG')