View: WSH_POD_SUMMARY_V

View Text

SELECT WDI.SEQUENCE_NUMBER BILL_OF_LADING_NUMBER
, WDI.STATUS BILL_OF_LADING_STATUS
, WDI.DOCUMENT_INSTANCE_ID DOCUMENT_INSTANCE_ID
, WDI.BOOKING_NUMBER BOOKING_NUMBER
, 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
, HOU.NAME||'
'|| HR1.ADDRESS_LINE_1||'
'|| HR1.ADDRESS_LINE_2||'
'|| HR1.ADDRESS_LINE_3||'
'|| HR1.TOWN_OR_CITY||'
'|| HR1.REGION_3||'
'|| HR1.COUNTRY||'
'|| HR1.POSTAL_CODE LOCATION_FROM
, 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
, RC.CUSTOMER_NAME||'
'|| HR2.ADDRESS_LINE_1||'
'|| HR2.ADDRESS_LINE_2||'
'|| HR2.ADDRESS_LINE_3||'
'|| HR2.TOWN_OR_CITY||'
'|| HR2.REGION_3||'
'|| HR2.COUNTRY||'
'|| HR2.POSTAL_CODE LOCATION_TO
, HR3.LOCATION_ID INTERMED_PICK_UP_LOCATION_ID
, HR3.ADDRESS_LINE_1 INTERMED_PICK_UP_ADRSS_LINE_1
, HR3.ADDRESS_LINE_2 INTERMED_PICK_UP_ADRSS_LINE_2
, HR3.ADDRESS_LINE_3 INTERMED_PICK_UP_ADRSS_LINE_3
, HR3.TOWN_OR_CITY INTERMED_PICK_UP_TOWN
, HR3.REGION_3 INTERMED_PICK_UP_STATE
, HR3.COUNTRY INTERMED_PICK_UP_COUNTRY
, HR3.POSTAL_CODE INTERMED_PICK_UP_ZIP
, HR3.ADDRESS_LINE_1||'
'|| HR3.ADDRESS_LINE_2||'
'|| HR3.ADDRESS_LINE_3||'
'|| HR3.TOWN_OR_CITY||'
'|| HR3.REGION_3||'
'|| HR3.COUNTRY||'
'|| HR3.POSTAL_CODE INTERMED_LOCATION_FROM
, HR4.LOCATION_ID INTERMED_DROP_OFF_LOCATION_ID
, HR4.ADDRESS_LINE_1 INTERMED_DROP_OFF_ADRSS_LINE_1
, HR4.ADDRESS_LINE_2 INTERMED_DROP_OFF_ADRSS_LINE_2
, HR4.ADDRESS_LINE_3 INTERMED_DROP_OFF_ADRSS_LINE_3
, HR4.TOWN_OR_CITY INTERMED_DROP_OFF_TOWN
, HR4.REGION_3 INTERMED_DROP_OFF_STATE
, HR4.COUNTRY INTERMED_DROP_OFF_COUNTRY
, HR4.POSTAL_CODE INTERMED_DROP_OFF_ZIP
, HR4.ADDRESS_LINE_1||'
'|| HR4.ADDRESS_LINE_2||'
'|| HR4.ADDRESS_LINE_3||'
'|| HR4.TOWN_OR_CITY||'
'|| HR4.REGION_3||'
'|| HR4.COUNTRY||'
'|| HR4.POSTAL_CODE INTERMED_LOCATION_TO
, TO_NUMBER(NULL) NUMBER_OF_PIECES
, TO_CHAR(WDL.GROSS_WEIGHT)||' '||WDL.WEIGHT_UOM_CODE TOTAL_GROSS_WEIGHT
, TO_CHAR(WDL.VOLUME)||' '||WDL.VOLUME_UOM_CODE TOTAL_VOLUME
, WDI.POD_BY POD_BY
, TO_CHAR(WDI.POD_DATE
, 'DD-MON-YYYY HH12:MI AM') POD_DATE
, WND.STATUS_CODE STATUS_CODE
, WDL.DELIVERY_LEG_ID DELIVERY_LEG_ID
, WDL.SEQUENCE_NUMBER SEQUENCE_NUMBER
FROM WSH_DOCUMENT_INSTANCES WDI
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, HR_LOCATIONS HR1
, HR_LOCATIONS HR2
, HR_LOCATIONS HR3
, HR_LOCATIONS HR4
, PO_LOCATION_ASSOCIATIONS PLA
, HR_ORGANIZATION_UNITS HOU
, RA_CUSTOMERS RC
, WSH_TRIP_STOPS WTS1
, WSH_TRIP_STOPS WTS2
, WSH_TRIPS WT
, WSH_CARRIER_SHIP_METHODS WCSM
, PO_VENDORS PV
WHERE WDI.ENTITY_NAME = 'WSH_DELIVERY_LEGS'
AND WDI.ENTITY_ID = WDL.DELIVERY_LEG_ID
AND WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WND.INITIAL_PICKUP_LOCATION_ID = PLA.LOCATION_ID(+)
AND PLA.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
AND WND.CUSTOMER_ID = RC.CUSTOMER_ID
AND WND.INITIAL_PICKUP_LOCATION_ID = HR1.LOCATION_ID(+)
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = HR2.LOCATION_ID(+)
AND WDL.PICK_UP_STOP_ID = WTS1.STOP_ID
AND WDL.DROP_OFF_STOP_ID = WTS2.STOP_ID
AND WTS1.STOP_LOCATION_ID = HR3.LOCATION_ID(+)
AND WTS2.STOP_LOCATION_ID = HR4.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