View: WSH_DSNO_DELIVERIES_V

Product:	WSH - Shipping Execution
Description:
Implementation/DBA Data:	APPS.WSH_DSNO_DELIVERIES_V

View Text

SELECT /*+ ORDERED USE_NL(HCAS) USE_NL(HCSU) */ DISTINCT ETD.DOCUMENT_ID TRANSACTION_TYPE
, 'EDI' COMMUNICATION_METHOD
, SYSDATE TRANSACTION_DATE
, ETH.TP_HEADER_ID TP_HEADER_ID
, ETD.TP_DETAIL_ID TP_DETAIL_ID
, WND.NAME DOCUMENT_CODE
, ETD.TEST_FLAG TP_TEST_FLAG
, ETD.DOCUMENT_TYPE TP_DOCUMENT_TYPE
, ETH.TP_CODE TP_CODE
, HCAS.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, ETH.TP_DESCRIPTION TP_DESCRIPTION
, ETH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, ETD.TRANSLATOR_CODE TP_TRANSLATOR_CODE
, ETD.EDI_FLAG TP_EDI_FLAG
, ETD.PRINT_FLAG TP_PRINT_FLAG
, ETH.TP_CONTACT_NAME TP_CONTACT_NAME
, ETH.TP_CONTACT_JOB_TITLE TP_CONTACT_JOB_TITLE
, ETH.ADDRESS1 TP_ADDRESS1
, ETH.ADDRESS2 TP_ADDRESS2
, ETH.ADDRESS3 TP_ADDRESS3
, ETH.ADDRESS4 TP_ADDRESS4
, ETH.CITY TP_CITY
, ETH.STATE TP_STATE
, ETH.PROVINCE TP_PROVINCE
, ETH.COUNTRY TP_COUNTRY
, ETH.POSTAL_CODE TP_POSTAL_CODE
, ETH.PHONE TP_PHONE
, ETH.FAX TP_FAX
, ETH.EMAIL TP_EMAIL
, ETH.ATTRIBUTE_CATEGORY TP_HEADER_CATEGORY
, ETH.ATTRIBUTE1 TP_HEADER_ATTRIBUTE1
, ETH.ATTRIBUTE2 TP_HEADER_ATTRIBUTE2
, ETH.ATTRIBUTE3 TP_HEADER_ATTRIBUTE3
, ETH.ATTRIBUTE4 TP_HEADER_ATTRIBUTE4
, ETH.ATTRIBUTE5 TP_HEADER_ATTRIBUTE5
, ETH.ATTRIBUTE6 TP_HEADER_ATTRIBUTE6
, ETH.ATTRIBUTE7 TP_HEADER_ATTRIBUTE7
, ETH.ATTRIBUTE8 TP_HEADER_ATTRIBUTE8
, ETH.ATTRIBUTE9 TP_HEADER_ATTRIBUTE9
, ETH.ATTRIBUTE10 TP_HEADER_ATTRIBUTE10
, ETH.ATTRIBUTE11 TP_HEADER_ATTRIBUTE11
, ETH.ATTRIBUTE12 TP_HEADER_ATTRIBUTE12
, ETH.ATTRIBUTE13 TP_HEADER_ATTRIBUTE13
, ETH.ATTRIBUTE14 TP_HEADER_ATTRIBUTE14
, ETH.ATTRIBUTE15 TP_HEADER_ATTRIBUTE15
, ETD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, ETD.ATTRIBUTE_CATEGORY TP_DETAIL_CATEGORY
, ETD.ATTRIBUTE1 TP_DETAIL_ATTRIBUTE1
, ETD.ATTRIBUTE2 TP_DETAIL_ATTRIBUTE2
, ETD.ATTRIBUTE3 TP_DETAIL_ATTRIBUTE3
, ETD.ATTRIBUTE4 TP_DETAIL_ATTRIBUTE4
, ETD.ATTRIBUTE5 TP_DETAIL_ATTRIBUTE5
, WND.ORGANIZATION_ID ORGANIZATION_ID
, WTP.TRIP_ID TRIP_ID
, WTP.NAME TRIP_NAME
, WTS.ACTUAL_DEPARTURE_DATE DEPARTURE_DATE
, WND.DELIVERY_ID DELIVERY_ID
, WND.NAME DELIVERY_NAME
, WND.ASN_SEQ_NUMBER TIME_STAMP_SEQUENCE_NUMBER
, WND.ASN_DATE_SENT TIME_STAMP_DATE
, WTP.VEHICLE_NUM_PREFIX EQUIPMENT_PREFIX
, WTP.VEHICLE_NUMBER EQUIPMENT_NUMBER
, WTS.DEPARTURE_SEAL_CODE EQUIPMENT_SEAL
, WTP.CARRIER_ID CARRIER_NAME_INT
, WTS.STOP_LOCATION_ID PICK_UP_LOCAION_ID
, WTS.STOP_ID PICK_UP_STOP_ID
, WTS2.STOP_LOCATION_ID DROP_OFF_LOCATION_ID
, WTS2.STOP_ID DROP_OFF_STOP_ID
, WTS.DEPARTURE_GROSS_WEIGHT DEPARTURE_GROSS_WEIGHT
, WTS.WEIGHT_UOM_CODE DEPARTURE_GROSS_WEIGHT_UOM_INT
, WTS.DEPARTURE_NET_WEIGHT DEPARTURE_NET_WEIGHT
, WTS.WEIGHT_UOM_CODE DEPARTURE_NET_WEIGHT_UOM_INT
, (WTS.DEPARTURE_GROSS_WEIGHT - WTS.DEPARTURE_NET_WEIGHT) DEPARTURE_TARE_WEIGHT
, WTS.WEIGHT_UOM_CODE DEPARTURE_TARE_WEIGHT_UOM_INT
, WTS.DEPARTURE_VOLUME DEPARTURE_VOLUME
, WTS.VOLUME_UOM_CODE DEPARTURE_VOLUME_UOM_INT
, SUBSTR(WTP.ROUTING_INSTRUCTIONS
, 0
, 400) ROUTING_INSTRUCTIONS1
, SUBSTR(WTP.ROUTING_INSTRUCTIONS
, 401
, 400) ROUTING_INSTRUCTIONS2
, SUBSTR(WTP.ROUTING_INSTRUCTIONS
, 801
, 400) ROUTING_INSTRUCTIONS3
, SUBSTR(WTP.ROUTING_INSTRUCTIONS
, 1201
, 400) ROUTING_INSTRUCTIONS4
, SUBSTR(WTP.ROUTING_INSTRUCTIONS
, 1601
, 400) ROUTING_INSTRUCTIONS5
, WND.INITIAL_PICKUP_LOCATION_ID WAREHOUSE_LOCATION_ID
, MTP.ORGANIZATION_CODE WAREHOUSE_CODE_INT
, HRL.ECE_TP_LOCATION_CODE WAREHOUSE_EDI_LOC_CODE
, ETH1.TP_REFERENCE_EXT1 WAREHOUSE_TP_REF_1
, ETH1.TP_REFERENCE_EXT2 WAREHOUSE_TP_REF_2
, HOU.NAME WAREHOUSE_NAME
, WSHL.ADDRESS1 WAREHOUSE_ADDRESS1
, WSHL.ADDRESS2 WAREHOUSE_ADDRESS2
, WSHL.ADDRESS3 WAREHOUSE_ADDRESS3
, WSHL.CITY WAREHOUSE_CITY
, WSHL.POSTAL_CODE WAREHOUSE_POSTAL_CODE
, WSHL.COUNTRY WAREHOUSE_COUNTRY_INT
, WSHL.COUNTY WAREHOUSE_REGION1_INT
, WSHL.STATE WAREHOUSE_REGION2_INT
, HRL.REGION_3 WAREHOUSE_REGION3_INT
, HRL.TELEPHONE_NUMBER_1 WAREHOUSE_TELEPHONE_1
, HRL.TELEPHONE_NUMBER_2 WAREHOUSE_TELEPHONE_2
, HRL.TELEPHONE_NUMBER_3 WAREHOUSE_TELEPHONE_3
, HCAS.CUST_ACCT_SITE_ID DELIVERY_ADDRESS_ID
, HCSU.LOCATION DELIVERY_CODE_INT
, HCAS.ECE_TP_LOCATION_CODE DELIVERY_EDI_LOC_CODE
, ETH.TP_REFERENCE_EXT1 DELIVERY_TP_REF_1
, ETH.TP_REFERENCE_EXT2 DELIVERY_TP_REF_2
, SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) DELIVERY_CUST_NAME
, WSHL2.ADDRESS1 DELIVERY_ADDRESS1
, WSHL2.ADDRESS2 DELIVERY_ADDRESS2
, WSHL2.ADDRESS3 DELIVERY_ADDRESS3
, WSHL2.ADDRESS4 DELIVERY_ADDRESS4
, WSHL2.CITY DELIVERY_CITY
, WSHL2.POSTAL_CODE DELIVERY_POSTAL_CODE
, WSHL2.COUNTRY DELIVERY_COUNTRY_INT
, WSHL2.STATE DELIVERY_STATE_INT
, WSHL2.PROVINCE DELIVERY_PROVINCE_INT
, WSHL2.COUNTY DELIVERY_COUNTY
, WSH_ECE_VIEWS_DEF.GET_CUST_AREA_CODE(CUST_ACCT1.CUST_ACCOUNT_ID) DELIVERY_AREA_CODE
, WSH_ECE_VIEWS_DEF.GET_CUST_PHONE_NUMBER DELIVERY_TELEPHONE
, HCAS.CUST_ACCT_SITE_ID DESTINATION_ADDRESS_ID
, WND.CUSTOMER_ID DESTINATION_CUSTOMER_ID
, HCSU.SITE_USE_ID DESTINATION_SITE_USE_ID
, HCSU.LOCATION DESTINATION_CODE_INT
, HCAS.ECE_TP_LOCATION_CODE DESTINATION_EDI_LOC_CODE
, ETH.TP_REFERENCE_EXT1 DESTINATION_TP_REF_1
, ETH.TP_REFERENCE_EXT2 DESTINATION_TP_REF_2
, SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) DESTINATION_NAME
, WSHL2.ADDRESS1 DESTINATION_ADDRESS1
, WSHL2.ADDRESS2 DESTINATION_ADDRESS2
, WSHL2.ADDRESS3 DESTINATION_ADDRESS3
, WSHL2.ADDRESS4 DESTINATION_ADDRESS4
, WSHL2.CITY DESTINATION_CITY
, WSHL2.POSTAL_CODE DESTINATION_POSTAL_CODE
, WSHL2.COUNTRY DESTINATION_COUNTRY_INT
, WSHL2.STATE DESTINATION_STATE_INT
, WSHL2.PROVINCE DESTINATION_PROVINCE_INT
, WSHL2.COUNTY DESTINATION_COUNTY
, WSH_ECE_VIEWS_DEF.GET_CONT_AREA_CODE(HCSU.CONTACT_ID) DESTINATION_AREA_CODE
, WSH_ECE_VIEWS_DEF.GET_CONT_PHONE_NUMBER DESTINATION_TELEPHONE
, WND.DELIVERY_ID SHIPPER_NUMBER
, WND.WAYBILL WAYBILL_NUMBER
, WDI.SEQUENCE_NUMBER BILL_OF_LADING
, WDOC.SEQUENCE_NUMBER PACKING_SLIP_NUMBER
, WTS.ACTUAL_DEPARTURE_DATE ACTUAL_SHIP_DATE
, WND.GROSS_WEIGHT DELIVERY_GROSS_WEIGHT
, WND.WEIGHT_UOM_CODE DELIVERY_GROSS_WEIGHT_UOM_INT
, WND.VOLUME DELIVERY_VOLUME
, WND.VOLUME_UOM_CODE DELIVERY_VOLUME_UOM_INT
, WND.NET_WEIGHT DELIVERY_NET_WEIGHT
, WND.WEIGHT_UOM_CODE DELIVERY_NET_WEIGHT_UOM_INT
, (WND.GROSS_WEIGHT - WND.NET_WEIGHT) DELIVERY_TARE_WEIGHT
, WND.WEIGHT_UOM_CODE DELIVERY_TARE_WEIGHT_UOM_INT
, WTS2.PLANNED_ARRIVAL_DATE EXPECTED_ARRIVAL_DATE
, WND.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
, WND.NUMBER_OF_LPN NUMBER_OF_LPN
, WND.SHIP_METHOD_CODE SHIP_METHOD_CODE_INT
, WND.FOB_CODE FOB_POINT_CODE_INT
, WND.FOB_LOCATION_ID FOB_LOCATION_ID
, WND.POOLED_SHIP_TO_LOCATION_ID POOLED_SHIP_TO_LOCATION_ID
, NVL(WND.INTMED_SHIP_TO_LOCATION_ID
, WND.ULTIMATE_DROPOFF_LOCATION_ID) INTMED_SHIP_TO_LOCATION_ID
, WTP.VEHICLE_ORGANIZATION_ID VEHICLE_ORGANIZATION_ID
, WTP.VEHICLE_ITEM_ID VEHICLE_ITEM_ID
, HCSU.CONTACT_ID CONTACT_ID
, WCS.SHIP_METHOD_MEANING SHIP_METHOD_NAME_INT
FROM WSH_TRIP_STOPS WTS
, WSH_TRIPS WTP
, WSH_TRIP_STOPS WTS2
, WSH_DELIVERY_LEGS WDL
, WSH_NEW_DELIVERIES WND
, WSH_LOCATIONS WSHL
, WSH_LOCATIONS WSHL2
, HR_LOCATIONS_ALL HRL
, HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MTP
, ECE_TP_HEADERS ETH1
, WSH_DOCUMENT_INSTANCES WDOC
, WSH_DOCUMENT_INSTANCES WDI
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCOUNTS CUST_ACCT1
, ECE_TP_HEADERS ETH
, ECE_TP_DETAILS ETD
, HZ_PARTIES PARTY1
, WSH_CARRIER_SERVICES WCS
WHERE WND.INITIAL_PICKUP_LOCATION_ID = WSHL.WSH_LOCATION_ID
AND WSHL.SOURCE_LOCATION_ID = HRL.LOCATION_ID
AND WSHL.LOCATION_SOURCE_CODE = 'HR'
AND HOU.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MTP.ORGANIZATION_ID
AND HRL.TP_HEADER_ID = ETH1.TP_HEADER_ID(+)
AND WTP.TRIP_ID = WTS.TRIP_ID
AND WTP.TRIP_ID = WTS2.TRIP_ID
AND WTS.TRIP_ID = WTS2.TRIP_ID
AND WND.ULTIMATE_DROPOFF_LOCATION_ID= WTS2.STOP_LOCATION_ID
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WSHL2.WSH_LOCATION_ID
AND WSHL2.LOCATION_SOURCE_CODE = 'HZ'
AND WSHL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND CUST_ACCT1.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND ( HCAS.ORG_ID IS NULL OR HCAS.ORG_ID IN ( SELECT FIRST_VALUE(WDD.ORG_ID) OVER(ORDER BY COUNT(WDD.ORG_ID) DESC ) AS ORG_ID
FROM WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDD.CONTAINER_FLAG = 'N' GROUP BY ORG_ID ) )
AND NVL(HCAS.ORG_ID
, -999) = NVL(HCSU.ORG_ID
, -999)
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND CUST_ACCT1.STATUS = 'A'
AND HCAS.TP_HEADER_ID = ETH.TP_HEADER_ID
AND WCS.SHIP_METHOD_CODE (+) = WND.SHIP_METHOD_CODE
AND WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WND.DELIVERY_ID = WDOC.ENTITY_ID (+)
AND WDOC.ENTITY_NAME(+) = 'WSH_NEW_DELIVERIES'
AND WDOC.DOCUMENT_TYPE(+) = 'PACK_TYPE'
AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
AND WDL.DELIVERY_LEG_ID = WDI.ENTITY_ID (+)
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND WDI.STATUS(+) <> 'CANCELLED'
AND HPS.PARTY_ID = CUST_ACCT1.PARTY_ID
AND CUST_ACCT1.PARTY_ID = PARTY1.PARTY_ID
AND ETD.TP_HEADER_ID = ETH.TP_HEADER_ID
AND ETD.EDI_FLAG = 'Y'
AND ETD.DOCUMENT_ID = 'DSNO'
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
AND WTS2.STOP_LOCATION_ID = HPS.LOCATION_ID
AND WND.DELIVERY_TYPE = 'STANDARD'

Columns