Below is the sample query to retrieve the customer invoice and ship to customer
SELECT rct.trx_number,
hp.party_name ship_to_customer,
site.party_site_number
FROM RA_CUSTOMER_TRX_ALL rct,
HZ_PARTY_SITE_USES hps,
hz_party_sites site,
hz_parties hp
WHERE RCT.TRX_NUMBER = '1017986'
AND hps.PARTY_SITE_USE_ID = rct.SHIP_TO_PARTY_SITE_USE_ID
AND hps.SITE_USE_TYPE = 'SHIP_TO'
AND hps.PARTY_SITE_ID = site.PARTY_SITE_ID
AND site.party_id = hp.party_id
***************************************************************************
Query to get the bill to details for a customer
SELECT hl.ADDRESS1 || ',' || hl.CITY || ',' || hl.STATE || ',' || hl.POSTAL_CODE || ',' || COUNTRY remit_address
FROM ra_customer_trx_all rct,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
HZ_CUST_SITE_USES_ALL hcsu,
HZ_PARTY_SITES RAAD_BILL_PS,
hz_locations hl
WHERE 1 = 1
AND rct.bill_to_customer_id = hca.cust_account_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.site_use_id = rct.BILL_TO_SITE_USE_ID
AND hcas.PARTY_SITE_ID = RAAD_BILL_PS.PARTY_SITE_ID
AND SITE_USE_CODE = 'BILL_TO'
AND hcas.CUST_ACCT_SITE_ID(+) = hcsu.CUST_ACCT_SITE_ID
AND RAAD_BILL_PS.location_id = hl.location_id
AND trx_number = '1014102'
