In this post we will see the difference between the below terminologies:
1. Customer Account Number
2. Location Number (located under Business Purposes tab of the customer site in the Site Details section)
3. Site Number
4. Registry ID
1. Customer Account Number
A party can have multiple customer accounts, and each of those customer accounts should have a customer number. So Customer Account Number is the customer number. Every time you enter a customer account, a row is inserted into the hz_cust_accounts table.
2. Location Number
Location Number is the number assigned to the site business usage (i.e. bill-to, ship-to, etc…). Therefore, you’ll see Location Number under Business Purposes tab of the customer site in the Site Details section. Location Number is stored in hz_cust_site_uses_all table.
3. Site Number
Site Number is the number assigned to a customer address. Therefore, you’ll see the site number in the Address section. Site Number is stored in HZ_PARTY_SITES table.
4. Registry ID
Registry ID is the party (organization) number. Every time you enter a party, a row is inserted into hz_parties table. In 11i Customers Standard form, you see Organization Number and Customer Number. Organization Number is the party number and Customer Number is the Customer Account Number.
To find customer number, site number, party number from invoice:
select rcta.trx_number, cust_acct.account_number customer_number, party_site.party_site_number site_number, hp.party_number FROM ra_customer_trx_all rcta, hz_cust_accounts cust_acct, hz_party_sites party_site, hz_cust_acct_sites_all acct_site, hz_cust_site_uses_all site_uses, hz_parties hp where rcta.bill_to_customer_id = cust_acct.cust_account_id AND rcta.bill_to_customer_id = acct_site.cust_account_id AND rcta.bill_to_site_use_id = site_uses.site_use_id AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND site_uses.site_use_code = 'BILL_TO' AND party_site.party_site_id = acct_site.party_site_id AND hp.party_id = cust_acct.party_id AND hp.party_id = party_site.party_id and rcta.trx_number='11111111'