View: WSH_DSNO_DELIVERY_ATTRIBS_V

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

View Text

SELECT /*+ ORDERED */ ETH.TP_HEADER_ID TP_HEADER_ID
, ETD.TP_DETAIL_ID TP_DETAIL_ID
, WND.DELIVERY_ID DELIVERY_ID
, WND.INITIAL_PICKUP_LOCATION_ID WAREHOUSE_LOCATION_ID
, HRL.ECE_TP_LOCATION_CODE WAREHOUSE_CODE_EXT
, RSU3.SITE_USE_ID DESTINATION_SITE_USE_ID
, CUST_ACCT1.CUST_ACCOUNT_ID DESTINATION_CUSTOMER_ID
, 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.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
, HRL.ATTRIBUTE_CATEGORY WAREHOUSE_CATEGORY
, HRL.ATTRIBUTE1 WAREHOUSE_ATTRIBUTE1
, HRL.ATTRIBUTE2 WAREHOUSE_ATTRIBUTE2
, HRL.ATTRIBUTE3 WAREHOUSE_ATTRIBUTE3
, HRL.ATTRIBUTE4 WAREHOUSE_ATTRIBUTE4
, HRL.ATTRIBUTE5 WAREHOUSE_ATTRIBUTE5
, HRL.ATTRIBUTE6 WAREHOUSE_ATTRIBUTE6
, HRL.ATTRIBUTE7 WAREHOUSE_ATTRIBUTE7
, HRL.ATTRIBUTE8 WAREHOUSE_ATTRIBUTE8
, HRL.ATTRIBUTE9 WAREHOUSE_ATTRIBUTE9
, HRL.ATTRIBUTE10 WAREHOUSE_ATTRIBUTE10
, HRL.ATTRIBUTE11 WAREHOUSE_ATTRIBUTE11
, HRL.ATTRIBUTE12 WAREHOUSE_ATTRIBUTE12
, HRL.ATTRIBUTE13 WAREHOUSE_ATTRIBUTE13
, HRL.ATTRIBUTE14 WAREHOUSE_ATTRIBUTE14
, HRL.ATTRIBUTE15 WAREHOUSE_ATTRIBUTE15
, HRL.ATTRIBUTE16 WAREHOUSE_ATTRIBUTE16
, HRL.ATTRIBUTE17 WAREHOUSE_ATTRIBUTE17
, HRL.ATTRIBUTE18 WAREHOUSE_ATTRIBUTE18
, HRL.ATTRIBUTE19 WAREHOUSE_ATTRIBUTE19
, HRL.ATTRIBUTE20 WAREHOUSE_ATTRIBUTE20
, RSU3.ATTRIBUTE_CATEGORY DESTINATION_CATEGORY
, RSU3.ATTRIBUTE1 DESTINATION_ATTRIBUTE1
, RSU3.ATTRIBUTE2 DESTINATION_ATTRIBUTE2
, RSU3.ATTRIBUTE3 DESTINATION_ATTRIBUTE3
, RSU3.ATTRIBUTE4 DESTINATION_ATTRIBUTE4
, RSU3.ATTRIBUTE5 DESTINATION_ATTRIBUTE5
, RSU3.ATTRIBUTE6 DESTINATION_ATTRIBUTE6
, RSU3.ATTRIBUTE7 DESTINATION_ATTRIBUTE7
, RSU3.ATTRIBUTE8 DESTINATION_ATTRIBUTE8
, RSU3.ATTRIBUTE9 DESTINATION_ATTRIBUTE9
, RSU3.ATTRIBUTE10 DESTINATION_ATTRIBUTE10
, RSU3.ATTRIBUTE11 DESTINATION_ATTRIBUTE11
, RSU3.ATTRIBUTE12 DESTINATION_ATTRIBUTE12
, RSU3.ATTRIBUTE13 DESTINATION_ATTRIBUTE13
, RSU3.ATTRIBUTE14 DESTINATION_ATTRIBUTE14
, RSU3.ATTRIBUTE15 DESTINATION_ATTRIBUTE15
, RSU3.ATTRIBUTE16 DESTINATION_ATTRIBUTE16
, RSU3.ATTRIBUTE17 DESTINATION_ATTRIBUTE17
, RSU3.ATTRIBUTE18 DESTINATION_ATTRIBUTE18
, RSU3.ATTRIBUTE19 DESTINATION_ATTRIBUTE19
, RSU3.ATTRIBUTE20 DESTINATION_ATTRIBUTE20
, RSU3.ATTRIBUTE21 DESTINATION_ATTRIBUTE21
, RSU3.ATTRIBUTE22 DESTINATION_ATTRIBUTE22
, RSU3.ATTRIBUTE23 DESTINATION_ATTRIBUTE23
, RSU3.ATTRIBUTE24 DESTINATION_ATTRIBUTE24
, RSU3.ATTRIBUTE25 DESTINATION_ATTRIBUTE25
, CUST_ACCT1.ATTRIBUTE_CATEGORY CUSTOMER_CATEGORY
, CUST_ACCT1.ATTRIBUTE1 CUSTOMER_ATTRIBUTE1
, CUST_ACCT1.ATTRIBUTE2 CUSTOMER_ATTRIBUTE2
, CUST_ACCT1.ATTRIBUTE3 CUSTOMER_ATTRIBUTE3
, CUST_ACCT1.ATTRIBUTE4 CUSTOMER_ATTRIBUTE4
, CUST_ACCT1.ATTRIBUTE5 CUSTOMER_ATTRIBUTE5
, CUST_ACCT1.ATTRIBUTE6 CUSTOMER_ATTRIBUTE6
, CUST_ACCT1.ATTRIBUTE7 CUSTOMER_ATTRIBUTE7
, CUST_ACCT1.ATTRIBUTE8 CUSTOMER_ATTRIBUTE8
, CUST_ACCT1.ATTRIBUTE9 CUSTOMER_ATTRIBUTE9
, CUST_ACCT1.ATTRIBUTE10 CUSTOMER_ATTRIBUTE10
, CUST_ACCT1.ATTRIBUTE11 CUSTOMER_ATTRIBUTE11
, CUST_ACCT1.ATTRIBUTE12 CUSTOMER_ATTRIBUTE12
, CUST_ACCT1.ATTRIBUTE13 CUSTOMER_ATTRIBUTE13
, CUST_ACCT1.ATTRIBUTE14 CUSTOMER_ATTRIBUTE14
, CUST_ACCT1.ATTRIBUTE15 CUSTOMER_ATTRIBUTE15
, CUST_ACCT1.GLOBAL_ATTRIBUTE_CATEGORY CUSTOMER_GLOBAL_CATEGORY
, CUST_ACCT1.GLOBAL_ATTRIBUTE1 CUSTOMER_GLOBAL_ATTRIBUTE1
, CUST_ACCT1.GLOBAL_ATTRIBUTE2 CUSTOMER_GLOBAL_ATTRIBUTE2
, CUST_ACCT1.GLOBAL_ATTRIBUTE3 CUSTOMER_GLOBAL_ATTRIBUTE3
, CUST_ACCT1.GLOBAL_ATTRIBUTE4 CUSTOMER_GLOBAL_ATTRIBUTE4
, CUST_ACCT1.GLOBAL_ATTRIBUTE5 CUSTOMER_GLOBAL_ATTRIBUTE5
, CUST_ACCT1.GLOBAL_ATTRIBUTE6 CUSTOMER_GLOBAL_ATTRIBUTE6
, CUST_ACCT1.GLOBAL_ATTRIBUTE7 CUSTOMER_GLOBAL_ATTRIBUTE7
, CUST_ACCT1.GLOBAL_ATTRIBUTE8 CUSTOMER_GLOBAL_ATTRIBUTE8
, CUST_ACCT1.GLOBAL_ATTRIBUTE9 CUSTOMER_GLOBAL_ATTRIBUTE9
, CUST_ACCT1.GLOBAL_ATTRIBUTE10 CUSTOMER_GLOBAL_ATTRIBUTE10
, CUST_ACCT1.GLOBAL_ATTRIBUTE11 CUSTOMER_GLOBAL_ATTRIBUTE11
, CUST_ACCT1.GLOBAL_ATTRIBUTE12 CUSTOMER_GLOBAL_ATTRIBUTE12
, CUST_ACCT1.GLOBAL_ATTRIBUTE13 CUSTOMER_GLOBAL_ATTRIBUTE13
, CUST_ACCT1.GLOBAL_ATTRIBUTE14 CUSTOMER_GLOBAL_ATTRIBUTE14
, CUST_ACCT1.GLOBAL_ATTRIBUTE15 CUSTOMER_GLOBAL_ATTRIBUTE15
, CUST_ACCT1.GLOBAL_ATTRIBUTE16 CUSTOMER_GLOBAL_ATTRIBUTE16
, CUST_ACCT1.GLOBAL_ATTRIBUTE17 CUSTOMER_GLOBAL_ATTRIBUTE17
, CUST_ACCT1.GLOBAL_ATTRIBUTE18 CUSTOMER_GLOBAL_ATTRIBUTE18
, CUST_ACCT1.GLOBAL_ATTRIBUTE19 CUSTOMER_GLOBAL_ATTRIBUTE19
, CUST_ACCT1.GLOBAL_ATTRIBUTE20 CUSTOMER_GLOBAL_ATTRIBUTE20
, WND.TP_ATTRIBUTE_CATEGORY DELIVERY_TP_CATEGORY
, WND.TP_ATTRIBUTE1 DELIVERY_TP_ATTRIBUTE1
, WND.TP_ATTRIBUTE2 DELIVERY_TP_ATTRIBUTE2
, WND.TP_ATTRIBUTE3 DELIVERY_TP_ATTRIBUTE3
, WND.TP_ATTRIBUTE4 DELIVERY_TP_ATTRIBUTE4
, WND.TP_ATTRIBUTE5 DELIVERY_TP_ATTRIBUTE5
, WND.TP_ATTRIBUTE6 DELIVERY_TP_ATTRIBUTE6
, WND.TP_ATTRIBUTE7 DELIVERY_TP_ATTRIBUTE7
, WND.TP_ATTRIBUTE8 DELIVERY_TP_ATTRIBUTE8
, WND.TP_ATTRIBUTE9 DELIVERY_TP_ATTRIBUTE9
, WND.TP_ATTRIBUTE10 DELIVERY_TP_ATTRIBUTE10
, WND.TP_ATTRIBUTE11 DELIVERY_TP_ATTRIBUTE11
, WND.TP_ATTRIBUTE12 DELIVERY_TP_ATTRIBUTE12
, WND.TP_ATTRIBUTE13 DELIVERY_TP_ATTRIBUTE13
, WND.TP_ATTRIBUTE14 DELIVERY_TP_ATTRIBUTE14
, WND.TP_ATTRIBUTE15 DELIVERY_TP_ATTRIBUTE15
, WND.GLOBAL_ATTRIBUTE_CATEGORY DELIVERY_GLOBAL_CATEGORY
, WND.GLOBAL_ATTRIBUTE1 DELIVERY_GLOBAL_ATTRIBUTE1
, WND.GLOBAL_ATTRIBUTE2 DELIVERY_GLOBAL_ATTRIBUTE2
, WND.GLOBAL_ATTRIBUTE3 DELIVERY_GLOBAL_ATTRIBUTE3
, WND.GLOBAL_ATTRIBUTE4 DELIVERY_GLOBAL_ATTRIBUTE4
, WND.GLOBAL_ATTRIBUTE5 DELIVERY_GLOBAL_ATTRIBUTE5
, WND.GLOBAL_ATTRIBUTE6 DELIVERY_GLOBAL_ATTRIBUTE6
, WND.GLOBAL_ATTRIBUTE7 DELIVERY_GLOBAL_ATTRIBUTE7
, WND.GLOBAL_ATTRIBUTE8 DELIVERY_GLOBAL_ATTRIBUTE8
, WND.GLOBAL_ATTRIBUTE9 DELIVERY_GLOBAL_ATTRIBUTE9
, WND.GLOBAL_ATTRIBUTE10 DELIVERY_GLOBAL_ATTRIBUTE10
, WND.GLOBAL_ATTRIBUTE11 DELIVERY_GLOBAL_ATTRIBUTE11
, WND.GLOBAL_ATTRIBUTE12 DELIVERY_GLOBAL_ATTRIBUTE12
, WND.GLOBAL_ATTRIBUTE13 DELIVERY_GLOBAL_ATTRIBUTE13
, WND.GLOBAL_ATTRIBUTE14 DELIVERY_GLOBAL_ATTRIBUTE14
, WND.GLOBAL_ATTRIBUTE15 DELIVERY_GLOBAL_ATTRIBUTE15
, WND.GLOBAL_ATTRIBUTE16 DELIVERY_GLOBAL_ATTRIBUTE16
, WND.GLOBAL_ATTRIBUTE17 DELIVERY_GLOBAL_ATTRIBUTE17
, WND.GLOBAL_ATTRIBUTE18 DELIVERY_GLOBAL_ATTRIBUTE18
, WND.GLOBAL_ATTRIBUTE19 DELIVERY_GLOBAL_ATTRIBUTE19
, WND.GLOBAL_ATTRIBUTE20 DELIVERY_GLOBAL_ATTRIBUTE20
, WND.ATTRIBUTE_CATEGORY DELIVERY_CATEGORY
, WND.ATTRIBUTE1 DELIVERY_ATTRIBUTE1
, WND.ATTRIBUTE2 DELIVERY_ATTRIBUTE2
, WND.ATTRIBUTE3 DELIVERY_ATTRIBUTE3
, WND.ATTRIBUTE4 DELIVERY_ATTRIBUTE4
, WND.ATTRIBUTE5 DELIVERY_ATTRIBUTE5
, WND.ATTRIBUTE6 DELIVERY_ATTRIBUTE6
, WND.ATTRIBUTE7 DELIVERY_ATTRIBUTE7
, WND.ATTRIBUTE8 DELIVERY_ATTRIBUTE8
, WND.ATTRIBUTE9 DELIVERY_ATTRIBUTE9
, WND.ATTRIBUTE10 DELIVERY_ATTRIBUTE10
, WND.ATTRIBUTE11 DELIVERY_ATTRIBUTE11
, WND.ATTRIBUTE12 DELIVERY_ATTRIBUTE12
, WND.ATTRIBUTE13 DELIVERY_ATTRIBUTE13
, WND.ATTRIBUTE14 DELIVERY_ATTRIBUTE14
, WND.ATTRIBUTE15 DELIVERY_ATTRIBUTE15
FROM WSH_NEW_DELIVERIES WND
, WSH_LOCATIONS WSHL
, WSH_LOCATIONS WSHL2
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_CUST_SITE_USES_ALL RSU3
, HZ_CUST_ACCOUNTS CUST_ACCT1
, HR_LOCATIONS_ALL HRL
, ECE_TP_HEADERS ETH
, ECE_TP_DETAILS ETD
WHERE WND.INITIAL_PICKUP_LOCATION_ID = WSHL.WSH_LOCATION_ID
AND WSHL.LOCATION_SOURCE_CODE = 'HR'
AND WSHL.SOURCE_LOCATION_ID = HRL.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 = ACCT_SITE.PARTY_SITE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = RSU3.CUST_ACCT_SITE_ID
AND HPS.PARTY_ID = CUST_ACCT1.PARTY_ID
AND RSU3.SITE_USE_CODE = 'SHIP_TO'
AND ( ACCT_SITE.ORG_ID IS NULL OR ACCT_SITE.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(ACCT_SITE.ORG_ID
, -9999) = NVL(RSU3.ORG_ID
, -9999)
AND RSU3.STATUS = 'A'
AND ETD.EDI_FLAG = 'Y'
AND ETD.DOCUMENT_ID = 'DSNO'
AND ACCT_SITE.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ACCT_SITE.TP_HEADER_ID = ETH.TP_HEADER_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT1.CUST_ACCOUNT_ID
AND WND.DELIVERY_TYPE = 'STANDARD'

Columns