Find Customer Bill To and Ship to Address Script in Oracle Apps EBS R12
SELECT HP.PARTY_NAME CUSTOMER_NAME,
DECODE (HCSUA.SITE_USE_CODE,'SHIP_TO', HCSUA.LOCATION) CUSTOMER_BILL_TO_CODE,
DECODE (HCSUA.SITE_USE_CODE,'SHIP_TO', HL.ADDRESS1||' '||HL.ADDRESS2 ||' '|| HL.CITY||' '|| HL.STATE||' '|| HL.COUNTRY||' '|| HL.POSTAL_CODE) CUSTOMER_BILL_TO_ADDRESS,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.STATE
FROM HZ_CUST_ACCT_SITES_ALL HCSA,
HZ_PARTY_SITES HPS,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_LOCATIONS HL
WHERE HPS.PARTY_SITE_ID = HCSA.PARTY_SITE_ID
AND HCSA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HCSUA.SITE_USE_CODE = 'SHIP_TO'
AND HP.PARTY_NAME =:P_CUST_NAME
UNION
SELECT HP.PARTY_NAME CUSTOMER_NAME,
DECODE (HCSUA.SITE_USE_CODE,'BILL_TO', HCSUA.LOCATION) CUSTOMER_BILL_TO_CODE,
DECODE (HCSUA.SITE_USE_CODE,'BILL_TO', HL.ADDRESS1||' '||HL.ADDRESS2 ||' '|| HL.CITY||' '|| HL.STATE||' '|| HL.COUNTRY||' '|| HL.POSTAL_CODE) CUSTOMER_BILL_TO_ADDRESS,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.STATE
FROM HZ_CUST_ACCT_SITES_ALL HCSA,
HZ_PARTY_SITES HPS,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_LOCATIONS HL
WHERE HPS.PARTY_SITE_ID = HCSA.PARTY_SITE_ID
AND HCSA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HCSUA.SITE_USE_CODE = 'BILL_TO'
AND HP.PARTY_NAME =:P_CUST_NAME
--AND HCSUA.LOCATION = 1059
