View: WSH_SS_PARTNER_V

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

View Text

SELECT DISTINCT HP.PARTY_NAME PARTY_NAME
, HCSU.LOCATION PARTNER_LOCATION
, 'SHIPTO' PARTNER_TYPE
, NULL CURRENCY
, HP.DUNS_NUMBER DUNS_NUMBER
, INTMED_SHIP_TO_V.LOCATION INTMED_SHIP_TO_LOCATION
, POOLED_SHIP_TO_V.LOCATION POOLED_SHIP_TO_LOCATION
, HL.ADDRESS1 ADDRESS1
, HL.ADDRESS2 ADDRESS2
, HL.ADDRESS3 ADDRESS3
, HL.ADDRESS4 ADDRESS4
, HL.CITY CITY
, HL.COUNTRY COUNTRY
, HL.COUNTY COUNTY
, HL.POSTAL_CODE POSTAL_CODE
, HL.PROVINCE REGION
, HL.STATE STATE
, POOLED_SHIP_TO_V.RAW_PHONE_NUMBER FAX_NUMBER
, INTMED_SHIP_TO_V.RAW_PHONE_NUMBER TELEPHONE
, INTMED_SHIP_TO_V.URL URL
, WND.DELIVERY_ID DELIVERY_ID
FROM WSH_NEW_DELIVERIES WND
, HZ_PARTIES HP
, HZ_PARTY_SITES HPS
, HZ_CUST_SITE_USES HCSU
, HZ_CUST_ACCT_SITES HCAS
, HZ_LOCATIONS HL
, ( SELECT HCSU1.LOCATION
, HCP1.RAW_PHONE_NUMBER
, HCP1.URL
, HCP1.CONTACT_POINT_ID
, HPS1.LOCATION_ID FROM HZ_CUST_SITE_USES HCSU1
, HZ_CUST_ACCT_SITES HCAS1
, HZ_PARTY_SITES HPS1
, HZ_CONTACT_POINTS HCP1
WHERE HCSU1.CUST_ACCT_SITE_ID = HCAS1.CUST_ACCT_SITE_ID AND HCAS1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID AND HCSU1.SITE_USE_CODE = 'SHIP_TO' AND HCSU1.STATUS = 'A' AND HCP1.CONTACT_POINT_TYPE = 'GEN' AND HCP1.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND HPS1.PARTY_SITE_ID = HCP1.CONTACT_POINT_ID ) INTMED_SHIP_TO_V
, ( SELECT HCSU2.LOCATION
, HCP2.RAW_PHONE_NUMBER
, HCP2.URL
, HCP2.CONTACT_POINT_ID
, HPS2.LOCATION_ID FROM HZ_CUST_SITE_USES HCSU2
, HZ_CUST_ACCT_SITES HCAS2
, HZ_PARTY_SITES HPS2
, HZ_CONTACT_POINTS HCP2 WHERE HCSU2.CUST_ACCT_SITE_ID = HCAS2.CUST_ACCT_SITE_ID AND HCAS2.PARTY_SITE_ID = HPS2.PARTY_SITE_ID AND HCSU2.SITE_USE_CODE = 'SHIP_TO' AND HCSU2.STATUS = 'A' AND HCP2.CONTACT_POINT_TYPE = 'FAX' AND HCP2.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND HPS2.PARTY_SITE_ID = HCP2.CONTACT_POINT_ID ) POOLED_SHIP_TO_V
WHERE HP.PARTY_ID = HPS.PARTY_ID AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND HCSU.SITE_USE_CODE = 'SHIP_TO' AND HCSU.STATUS = 'A'
AND HPS.LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID AND HL.LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID
AND WND.INTMED_SHIP_TO_LOCATION_ID = INTMED_SHIP_TO_V.LOCATION_ID (+)
AND WND.POOLED_SHIP_TO_LOCATION_ID = POOLED_SHIP_TO_V.LOCATION_ID(+) UNION ALL SELECT OOD.ORGANIZATION_NAME PARTY_NAME
, HL.LOCATION_CODE PARTNER_LOCATION
, 'SHIPFROM' PARTNER_TYPE
, WND.CURRENCY_CODE CURRENCY
, 0 DUNS_NUMBER
, NULL INTMED_SHIP_TO_LOCATION
, NULL POOLED_SHIP_TO_LOCATION_ID
, HL.ADDRESS_LINE_1 ADDRESS1
, HL.ADDRESS_LINE_2 ADDRESS2
, HL.ADDRESS_LINE_3 ADDRESS3
, NULL ADDRESS4
, HL.TOWN_OR_CITY CITY
, HL.COUNTRY COUNTRY
, NULL COUNTY
, HL.POSTAL_CODE POSTAL_CODE
, HL.REGION_1 REGION
, HL.REGION_2 STATE
, HL.TELEPHONE_NUMBER_2 FAX_NUMBER
, HL.TELEPHONE_NUMBER_1 TELEPHONE
, NULL URL
, WND.DELIVERY_ID DELIVERY_ID
FROM WSH_NEW_DELIVERIES WND
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HL
WHERE WND.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND WND.INITIAL_PICKUP_LOCATION_ID = HL.LOCATION_ID
AND 'THIS VIEW IS OBSOLETE
FROM PATCHSET I ONWARDS' = 'TRUE'

Columns