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
update script to change the shipping address in sales order
ReplyDeleteThanks!!!
ReplyDeleteI need the ar transaction and bill to contact details for the receivables.
ReplyDeleteand if customer do not have contact it shud pock trx wrt to customer .
I really impressed after read this because of some quality work and informative thoughts . I just wanna say thanks for the writer and wish you all the best for coming!. Korea personal shopper
ReplyDeletehow to get the Bill to location and Bill to location for specific ship to address
ReplyDeletehow to get the Bill to location and ship to location for specific ship to address from business purpose tab?
ReplyDelete