VIEW: APPS.WSH_SS_PARTNER_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'