VIEW: APPS.MST_PQ_UA_DEL_DETAILS_V

Source

View Text - Preformatted

SELECT D.PLAN_ID, D.DELIVERY_ID, D.CUSTOMER_ID, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.CUSTOMER_ID, 1), 1,80) CUSTOMER_NAME, D.SUPPLIER_ID, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.SUPPLIER_ID, 2), 1,80) SUPPLIER_NAME, DD.DELIVERY_DETAIL_ID, D.DELIVERY_NUMBER, DD.SOURCE_CODE, DD.SOURCE_HEADER_NUMBER, DD.REFERENCE_SOURCE_TYPE, LP1.FACILITY_ID ORIGIN_FACILITY_ID, LP1.FACILITY_CODE ORIGIN_FACILITY, WL1.CITY ORIGIN_CITY, WL1.STATE ORIGIN_STATE, WL1.POSTAL_CODE ORIGIN_ZIP, WL1.COUNTRY ORIGIN_COUNTRY, LP2.FACILITY_ID DESTINATION_FACILITY_ID, LP2.FACILITY_CODE DESTINATION_FACILITY, 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, D.GROSS_WEIGHT TOTAL_WEIGHT, D.VOLUME TOTAL_CUBE, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, MST_DELIVERIES D, 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 WHERE MP.PLAN_ID = D.PLAN_ID AND DD.PLAN_ID = D.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 0 = (SELECT COUNT(dl.delivery_id) FROM mst_delivery_legs dl WHERE dl.plan_id = d.plan_id AND dl.delivery_id = d.delivery_id)

View Text - HTML Formatted

SELECT D.PLAN_ID
, D.DELIVERY_ID
, D.CUSTOMER_ID
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.CUSTOMER_ID
, 1)
, 1
, 80) CUSTOMER_NAME
, D.SUPPLIER_ID
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.SUPPLIER_ID
, 2)
, 1
, 80) SUPPLIER_NAME
, DD.DELIVERY_DETAIL_ID
, D.DELIVERY_NUMBER
, DD.SOURCE_CODE
, DD.SOURCE_HEADER_NUMBER
, DD.REFERENCE_SOURCE_TYPE
, LP1.FACILITY_ID ORIGIN_FACILITY_ID
, LP1.FACILITY_CODE ORIGIN_FACILITY
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, LP2.FACILITY_ID DESTINATION_FACILITY_ID
, LP2.FACILITY_CODE DESTINATION_FACILITY
, 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
, D.GROSS_WEIGHT TOTAL_WEIGHT
, D.VOLUME TOTAL_CUBE
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_DELIVERIES D
, 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
WHERE MP.PLAN_ID = D.PLAN_ID
AND DD.PLAN_ID = D.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 0 = (SELECT COUNT(DL.DELIVERY_ID)
FROM MST_DELIVERY_LEGS DL
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID)