VIEW: APPS.CLN_XML_DELIVERIES2_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')