View: CLN_WSH_SS_SHIPMENT_V

Product:	CLN - Supply Chain Trading Connector for RosettaNet
Description:	View of delivery header details
Implementation/DBA Data:	APPS.CLN_WSH_SS_SHIPMENT_V

View Text

SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID
, WTH.ACTION_TYPE ACTION_CODE
, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER
, WND.CREATION_DATE DOCUMENT_CREATION_DATE
, HRL.LOCATION_CODE SHIP_FROM_LOCATION
, WND.SHIP_METHOD_CODE SHIP_METHOD
, WL1.COUNTRY COUNTRY
, WND.ULTIMATE_DROPOFF_DATE ULTIMATE_DROPOFF_DATE
, WND.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
, WND.DESCRIPTION DESCRIPTION
, WTH.DOCUMENT_TYPE DOCUMENT_TYPE
, WND.FREIGHT_TERMS_CODE FREIGHT_TERMS
, WND.GROSS_WEIGHT GROSS_WEIGHT
, WND.NET_WEIGHT NET_WEIGHT
, WND.WEIGHT_UOM_CODE WEIGHT_UOM_CODE
, WND.NUMBER_OF_LPN NUMBER_OF_LPN
, WND.VOLUME VOLUME
, WND.VOLUME_UOM_CODE VOLUME_UOM_CODE
, WND.SHIPPING_MARKS SHIPPING_MARKS
, HCSU.LOCATION SHIP_TO_LOCATION
, WND.FOB_CODE FOB_CODE
, OOG.ORGANIZATION_CODE ORGANIZATION_CODE
, OOG.ORGANIZATION_NAME ORGANIZATION_NAME
, WND.LOADING_SEQUENCE LOADING_SEQUENCE
, WTH.ORIG_DOCUMENT_NUMBER ORIG_DOCUMENT_NUMBER
, DECODE(WTH.DOCUMENT_TYPE
, 'SA'
, WND.WAYBILL
, NULL) WAYBILL
, WCAR.CARRIER_NAME CARRIER
, WND.SERVICE_LEVEL SERVICE_LEVEL
, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD
, HRLA.ECE_TP_LOCATION_CODE SHIP_FROM_EDI_LOCATION_CODE1
, HRLA.ECE_TP_LOCATION_CODE SHIP_FROM_EDI_LOCATION_CODE2
, HRLA.ADDRESS_LINE_1 SHIP_FROM_ADDRESS1
, HRLA.ADDRESS_LINE_2 SHIP_FROM_ADDRESS2
, HRLA.ADDRESS_LINE_3 SHIP_FROM_ADDRESS3
, HRLA.TOWN_OR_CITY SHIP_FROM_CITY
, HRLA.COUNTRY SHIP_FROM_COUNTRY_CODE
, HRLA.POSTAL_CODE SHIP_FROM_POSTAL_CODE
, HRLA.REGION_1 SHIP_FROM_REGION
, HCAS.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE1
, HCAS.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE2
, WL2.ADDRESS1 SHIP_TO_ADDRESS1
, WL2.ADDRESS2 SHIP_TO_ADDRESS2
, WL2.ADDRESS3 SHIP_TO_ADDRESS3
, WL2.CITY SHIP_TO_CITY
, WL2.COUNTRY SHIP_TO_COUNTRY_CODE
, WL2.POSTAL_CODE SHIP_TO_POSTAL_CODE
, NULL SHIP_TO_REGION
FROM WSH_NEW_DELIVERIES WND
, WSH_TRANSACTIONS_HISTORY WTH
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL HRLA
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS
, ORG_ORGANIZATION_DEFINITIONS OOG
, WSH_CARRIERS_V WCAR
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE WND.INITIAL_PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND WL1.LOCATION_SOURCE_CODE = 'HR'
AND WL1.SOURCE_LOCATION_ID = HRL.LOCATION_ID
AND HRL.LANGUAGE = USERENV('LANG')
AND HRL.LOCATION_ID = HRLA.LOCATION_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND HCAS.STATUS = 'A'
AND HCA.STATUS = 'A'
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.LOCATION_SOURCE_CODE = 'HZ'
AND WL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID
AND WTH.ENTITY_NUMBER = WND.NAME
AND WTH.ENTITY_TYPE ='DLVY'
AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_ID))
AND NVL(HCAS.ORG_ID
, -999) = NVL(HCSU.ORG_ID
, -999)
AND WTH.DOCUMENT_DIRECTION = 'O'
AND WND.CARRIER_ID = WCAR.CARRIER_ID (+)

Columns