View: CLN_XML_DELIVERIES_V

Product:	CLN - Supply Chain Trading Connector for RosettaNet
Description:	View for SHIPMENT deliveries section in OAG SHOW_SHIPMENT_005 XML payload
Implementation/DBA Data:	APPS.CLN_XML_DELIVERIES_V

View Text

SELECT DISTINCT WND.NAME DOCUMENT_CODE
, WND.DELIVERY_ID DELIVERY_ID
, HCSU.LOCATION DESTINATION_CODE_INT
, HCAS.ECE_TP_LOCATION_CODE DESTINATION_EDI_LOC_CODE
, SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) DESTINATION_NAME
, HZL.ADDRESS1 DESTINATION_ADDRESS1
, HZL.ADDRESS2 DESTINATION_ADDRESS2
, HZL.ADDRESS3 DESTINATION_ADDRESS3
, HZL.CITY DESTINATION_CITY
, HZL.STATE DESTINATION_STATE
, HZL.POSTAL_CODE DESTINATION_POSTAL_CODE
, HZL.COUNTRY DESTINATION_COUNTRY_INT
, SUBSTRB( REL_PARTY.PERSON_LAST_NAME
, 1
, 50) DESTINATION_CONTACT_LAST_NAME
, SUBSTRB( REL_PARTY.PERSON_FIRST_NAME
, 1
, 40) DESTINATION_CONTACT_FIRST_NAME
, 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
, WND.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
, WND.NUMBER_OF_LPN NUMBER_OF_LPN
, WND.SHIP_METHOD_CODE SHIP_METHOD_CODE_INT
, WND.ULTIMATE_DROPOFF_DATE ULTIMATE_DROPOFF_DATE
, WND.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
, WTS.PLANNED_DEPARTURE_DATE PLANNED_DEPARTURE_DATE
, WND.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, WND.ATTRIBUTE1 ATTRIBUTE1
, WND.ATTRIBUTE2 ATTRIBUTE2
, WND.ATTRIBUTE3 ATTRIBUTE3
, WND.ATTRIBUTE4 ATTRIBUTE4
, WND.ATTRIBUTE5 ATTRIBUTE5
, WND.ATTRIBUTE6 ATTRIBUTE6
, WND.ATTRIBUTE7 ATTRIBUTE7
, WND.ATTRIBUTE8 ATTRIBUTE8
, WND.ATTRIBUTE9 ATTRIBUTE9
, WND.ATTRIBUTE10 ATTRIBUTE10
, WND.ATTRIBUTE11 ATTRIBUTE11
, WND.ATTRIBUTE12 ATTRIBUTE12
, WND.ATTRIBUTE13 ATTRIBUTE13
, WND.ATTRIBUTE14 ATTRIBUTE14
, WND.ATTRIBUTE15 ATTRIBUTE15
, WSHL.UI_LOCATION_CODE TP_DESCRIPTION
, HRL.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, WSHL.ADDRESS1 TP_ADDRESS1
, WSHL.ADDRESS2 TP_ADDRESS2
, WSHL.ADDRESS3 TP_ADDRESS3
, WSHL.CITY TP_CITY
, WSHL.STATE TP_STATE
, WSHL.COUNTRY TP_COUNTRY
, WSHL.POSTAL_CODE TP_POSTAL_CODE
, HRL.TELEPHONE_NUMBER_1 TP_PHONE
, HRL.TELEPHONE_NUMBER_2 TP_FAX
, WSHL.WSH_LOCATION_ID TP_CODE
, CRPARTY.PARTY_NAME CARRIER_NAME_INT
FROM WSH_TRIP_STOPS WTS
, WSH_DELIVERY_LEGS WDL
, WSH_NEW_DELIVERIES WND
, WSH_TRIPS WTP
, WSH_DOCUMENT_INSTANCES WDOC
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_PARTIES PARTY1
, HZ_CUST_ACCOUNTS CUST_ACCT1
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_PARTIES REL_PARTY
, HR_LOCATIONS_ALL HRL
, WSH_DOCUMENT_INSTANCES WDI
, WSH_LOCATIONS WSHL
, HZ_PARTIES CRPARTY
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HZL
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 WTP.TRIP_ID = WTS.TRIP_ID
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = HZL.LOCATION_ID
AND WND.ULTIMATE_DROPOFF_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 = WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_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 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 WDL.PICK_UP_STOP_ID = WTS.STOP_ID
AND WDL.DELIVERY_LEG_ID = WDI.ENTITY_ID (+)
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND CUST_ACCT1.PARTY_ID = PARTY1.PARTY_ID
AND HCSU.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND REL.PARTY_ID (+) = ACCT_ROLE.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND REL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG (+) = 'F'
AND REL.SUBJECT_ID = REL_PARTY.PARTY_ID (+)
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS WDA
WHERE WDA.DELIVERY_ID = WND.DELIVERY_ID)
AND WTP.CARRIER_ID = CRPARTY.PARTY_ID(+)
AND (WTS.SHIPMENTS_TYPE_FLAG IS NULL OR WTS.SHIPMENTS_TYPE_FLAG = 'O' OR WTS.SHIPMENTS_TYPE_FLAG = 'M')
AND (WTP.SHIPMENTS_TYPE_FLAG IS NULL OR WTP.SHIPMENTS_TYPE_FLAG = 'O' OR WTP.SHIPMENTS_TYPE_FLAG = 'M')
AND (WND.SHIPMENT_DIRECTION IS NULL OR WND.SHIPMENT_DIRECTION = 'O')

Columns