eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_SHIP_ADDRESS_V

Source

View Text - Preformatted


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(+)

View Text - HTML Formatted

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(+)