View: AR_SHIP_ADDRESS_V

Product:	AR - Receivables
Description:	Active Ship to Addresses
Implementation/DBA Data:	APPS.AR_SHIP_ADDRESS_V

View Text

SELECT A_LOC.ADDRESS1 ADDRESS1
, A_LOC.ADDRESS2 ADDRESS2
, A_LOC.CITY ||FND_GLOBAL.LOCAL_CHR(44)|| FND_GLOBAL.LOCAL_CHR(32)|| NVL(A_LOC.STATE
, A_LOC.PROVINCE) ||' '|| A_LOC.POSTAL_CODE|| FND_GLOBAL.LOCAL_CHR(32) || T.TERRITORY_SHORT_NAME ADDRESS3
, A_LOC.ADDRESS3 ADDRESS3_DB
, SUBSTR(A_LOC.ADDRESS1
, 1
, 25) ||' '|| SUBSTR(A_LOC.ADDRESS2
, 1
, 25) ||' '|| SUBSTR(A_LOC.ADDRESS3
, 1
, 25) ||' '|| SUBSTR(A_LOC.ADDRESS4
, 1
, 25) ||' '|| A_LOC.CITY|| FND_GLOBAL.LOCAL_CHR(44) || FND_GLOBAL.LOCAL_CHR(32) || NVL(A_LOC.STATE
, A_LOC.PROVINCE) ||' '|| T.TERRITORY_SHORT_NAME ADDRESS
, SU.LOCATION LOCATION
, SU.PRIMARY_FLAG PRIMARY_FLAG
, A_LOC.CITY CITY
, A_LOC.COUNTY COUNTY
, A_LOC.STATE STATE
, A_LOC.PROVINCE PROVINCE
, A_LOC.COUNTRY COUNTRY
, A_LOC.POSTAL_CODE POSTAL_CODE
, SU.TERRITORY_ID TERRITORY_ID
, SU.STATUS SU_STATUS
, A.STATUS A_STATUS
, NVL(SU_BILL.LOCATION
, SU_BILL2.LOCATION) BILL_LOCATION
, NVL( A_BILL_LOC.ADDRESS1
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.ADDRESS1) ) BILL_ADDRESS1
, NVL( A_BILL_LOC.ADDRESS2
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.ADDRESS2) ) BILL_ADDRESS2
, DECODE( A_BILL.CUST_ACCT_SITE_ID
, NULL
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.CITY ||FND_GLOBAL.LOCAL_CHR(44)|| FND_GLOBAL.LOCAL_CHR(32)|| NVL(A_LOC.STATE
, A_LOC.PROVINCE) ||' '||A_LOC.POSTAL_CODE|| FND_GLOBAL.LOCAL_CHR(32) || T.TERRITORY_SHORT_NAME)
, A_BILL_LOC.CITY ||FND_GLOBAL.LOCAL_CHR(44)|| FND_GLOBAL.LOCAL_CHR(32)|| NVL(A_BILL_LOC.STATE
, A_BILL_LOC.PROVINCE) ||' '|| A_BILL_LOC.POSTAL_CODE|| FND_GLOBAL.LOCAL_CHR(32) || T_BILL.TERRITORY_SHORT_NAME ) BILL_ADDRESS3
, NVL( A_BILL_LOC.ADDRESS3
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.ADDRESS3) ) BILL_ADDRESS3_DB
, SUBSTR(A_BILL_LOC.ADDRESS1
, 1
, 25) ||' '|| SUBSTR(A_BILL_LOC.ADDRESS2
, 1
, 25) ||' '|| SUBSTR(A_BILL_LOC.ADDRESS3
, 1
, 25) ||' '|| SUBSTR(A_BILL_LOC.ADDRESS4
, 1
, 25) ||' '|| A_BILL_LOC.CITY|| FND_GLOBAL.LOCAL_CHR(44) || FND_GLOBAL.LOCAL_CHR(32) || NVL(A_BILL_LOC.STATE
, A_BILL_LOC.PROVINCE) ||' '|| T_BILL.TERRITORY_SHORT_NAME BILL_ADDRESS
, NVL( A_BILL_LOC.CITY
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.CITY) ) BILL_CITY
, NVL( A_BILL_LOC.STATE
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.STATE) ) BILL_STATE
, NVL( A_BILL_LOC.PROVINCE
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.PROVINCE) ) BILL_PROVINCE
, NVL( A_BILL_LOC.COUNTY
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.COUNTY) ) BILL_COUNTY
, NVL( A_BILL_LOC.COUNTRY
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.COUNTRY) ) BILL_COUNTRY
, NVL( A_BILL_LOC.POSTAL_CODE
, DECODE(SU_BILL2.SITE_USE_ID
, NULL
, NULL
, A_LOC.POSTAL_CODE) ) BILL_POSTAL_CODE
, NVL(SU_BILL.TERRITORY_ID
, SU_BILL2.TERRITORY_ID) BILL_TERRITORY_ID
, NVL(SU_BILL.SITE_USE_ID
, SU_BILL2.SITE_USE_ID) BILL_SITE_USE_ID
, NVL(SU_BILL.CUST_ACCT_SITE_ID
, SU_BILL2.CUST_ACCT_SITE_ID) BILL_ADDRESS_ID
, NULL BILL_LOCATION_ID /* IN R12
, LOC_ID IS ALWAYS NULL */
, SU.SITE_USE_ID SITE_USE_ID
, A.CUST_ACCT_SITE_ID ADDRESS_ID
, SU.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, A.CUST_ACCOUNT_ID CUSTOMER_ID
, NULL LOCATION_ID
, SU.PRIMARY_SALESREP_ID
FROM HZ_CUST_ACCT_SITES A_BILL
, HZ_PARTY_SITES A_BILL_PS
, HZ_LOCATIONS A_BILL_LOC
, HZ_CUST_SITE_USES SU_BILL
, HZ_CUST_SITE_USES SU_BILL2
, FND_TERRITORIES_VL T
, FND_TERRITORIES_VL T_BILL
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES A
, HZ_PARTY_SITES A_PS
, HZ_LOCATIONS A_LOC
WHERE A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND A.PARTY_SITE_ID = A_PS.PARTY_SITE_ID
AND A_LOC.LOCATION_ID = A_PS.LOCATION_ID
AND A_LOC.COUNTRY = T.TERRITORY_CODE
AND SU.SITE_USE_CODE = 'SHIP_TO'
AND SU.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID(+)
AND SU_BILL.CUST_ACCT_SITE_ID = A_BILL.CUST_ACCT_SITE_ID(+)
AND A_BILL.PARTY_SITE_ID = A_BILL_PS.PARTY_SITE_ID(+)
AND A_BILL_LOC.LOCATION_ID(+) = A_BILL_PS.LOCATION_ID
AND 'BILL_TO' = SU_BILL.SITE_USE_CODE(+)
AND A_BILL_LOC.COUNTRY = T_BILL.TERRITORY_CODE(+)
AND 'A' = SU_BILL.STATUS(+)
AND 'A' = A_BILL.STATUS(+)
AND A.CUST_ACCT_SITE_ID = SU_BILL2.CUST_ACCT_SITE_ID(+)
AND 'BILL_TO' = SU_BILL2.SITE_USE_CODE(+)
AND 'A' = SU_BILL2.STATUS(+)

Columns