View: WSH_LOAD_TENDER_SUMMARY_V

View Text

SELECT WND.DELIVERY_ID DELIVERY_ID
, WND.NAME DELIVERY_NAME
, WND.WAYBILL CARRIER_TRACKING_NUMBER
, WND.SHIP_METHOD_CODE CARRIER_AND_SERVICE_CODE
, WCSM.CARRIER_ID CARRIER_ID
, WCSM.SERVICE_LEVEL SERVICE_LEVEL
, PV.VENDOR_NAME||' '||WCSM.SERVICE_LEVEL CARRIER_SERVICE
, PV.VENDOR_NAME CARRIER_NAME
, PLA.ORGANIZATION_ID ORG_ID
, HOU.NAME FROM_NAME
, HR1.LOCATION_ID INITIAL_PICK_UP_LOCATION_ID
, HR1.ADDRESS_LINE_1 INITIAL_PICK_UP_ADDRESS_LINE_1
, HR1.ADDRESS_LINE_2 INITIAL_PICK_UP_ADDRESS_LINE_2
, HR1.ADDRESS_LINE_3 INITIAL_PICK_UP_ADDRESS_LINE_3
, HR1.TOWN_OR_CITY INITIAL_PICK_UP_TOWN
, HR1.REGION_3 INITIAL_PICK_UP_STATE
, HR1.COUNTRY INITIAL_PICK_UP_COUNTRY
, HR1.POSTAL_CODE INITIAL_PICK_UP_ZIP
, HR1.ADDRESS_LINE_1||'
'||HR1.ADDRESS_LINE_2||'
'||HR1.ADDRESS_LINE_3||'
'||HR1.TOWN_OR_CITY||'
'||HR1.REGION_3||'
'||HR1.COUNTRY||'
'||HR1.POSTAL_CODE SHIP_FROM
, TO_CHAR(WND.INITIAL_PICKUP_DATE
, 'DD-MON-YYYY HH12:MI AM') SHIP_DATE
, RC.CUSTOMER_NAME CUSTOMER_NAME
, HR2.LOCATION_ID ULTIMATE_DROP_OFF_LOCATION_ID
, HR2.ADDRESS_LINE_1 ULTIMATE_DROP_OFF_ADRSS_LINE_1
, HR2.ADDRESS_LINE_2 ULTIMATE_DROP_OFF_ADRSS_LINE_2
, HR2.ADDRESS_LINE_3 ULTIMATE_DROP_OFF_ADRSS_LINE_3
, HR2.TOWN_OR_CITY ULTIMATE_DROP_OFF_TOWN
, HR2.REGION_3 ULTIMATE_DROP_OFF_STATE
, HR2.COUNTRY ULTIMATE_DROP_OFF_COUNTRY
, HR2.POSTAL_CODE ULTIMATE_DROP_OFF_ZIP
, HR2.ADDRESS_LINE_1||'
'||HR2.ADDRESS_LINE_2||'
'||HR2.ADDRESS_LINE_3||'
'||HR2.TOWN_OR_CITY||'
'||HR2.REGION_3||'
'||HR2.COUNTRY||'
'||HR2.POSTAL_CODE SHIP_TO
, TO_CHAR(WND.ULTIMATE_DROPOFF_DATE
, 'DD-MON-YYYY HH12:MI AM') ARRIVE_DATE
, TO_NUMBER(NULL) NUMBER_OF_PIECES
, WDL.LOAD_TENDER_STATUS LOAD_TENDER_FLAG
, WDL.DELIVERY_LEG_ID DELIVERY_LEG_ID
, TO_CHAR(WDL.GROSS_WEIGHT)||' '||WDL.WEIGHT_UOM_CODE TOTAL_GROSS_WEIGHT
, TO_CHAR(WDL.NET_WEIGHT)||' '||WDL.WEIGHT_UOM_CODE TOTAL_NET_WEIGHT
, TO_CHAR(WDL.VOLUME)||' '||WDL.VOLUME_UOM_CODE TOTAL_VOLUME
, WDL.SEQUENCE_NUMBER SEQUENCE_NUMBER
FROM WSH_NEW_DELIVERIES WND
, PO_LOCATION_ASSOCIATIONS PLA
, HR_ORGANIZATION_UNITS HOU
, RA_CUSTOMERS RC
, WSH_DELIVERY_LEGS WDL
, WSH_TRIP_STOPS WTS1
, WSH_TRIP_STOPS WTS2
, WSH_TRIPS WT
, HR_LOCATIONS HR1
, HR_LOCATIONS HR2
, WSH_CARRIER_SHIP_METHODS WCSM
, PO_VENDORS PV
WHERE WND.INITIAL_PICKUP_LOCATION_ID = PLA.LOCATION_ID(+)
AND PLA.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
AND WND.CUSTOMER_ID = RC.CUSTOMER_ID
AND WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WDL.PICK_UP_STOP_ID = WTS1.STOP_ID
AND WDL.DROP_OFF_STOP_ID = WTS2.STOP_ID
AND WTS1.STOP_LOCATION_ID = HR1.LOCATION_ID(+)
AND WTS2.STOP_LOCATION_ID = HR2.LOCATION_ID(+)
AND WTS1.TRIP_ID = WT.TRIP_ID
AND WT.SHIP_METHOD_CODE = WCSM.SHIP_METHOD_CODE
AND WCSM.CARRIER_ID = PV.VENDOR_ID ORDER BY WND.DELIVERY_ID
, WDL.SEQUENCE_NUMBER
, WDL.DELIVERY_LEG_ID

Columns