Customer Query in oracle fusion

Customer Query in oracle fusion

In this post , we will be discuss about the Customer Query in oracle fusion.This query will help to extract the customer details from header and site level in oracle fusion. This is one of the important customer query in oracle fusion. We are only extracting the customer information’s from customer header and Customer Site. I will share some of the important tables for customer query. Please find the complete code for customer query in oracle fusion.

Most Important Tables used by customer query in oracle fusion.

1.HZ_CUST_ACCOUNTS

2.HZ_CUST_ACCT_SITES_ALL

3.HZ_CUST_SITE_USES_ALL

4.HZ_PARTY_SITES

5.HZ_LOCATIONS

6.HZ_PARTIES

Detail Customer Query in oracle fusion

select hp.party_name customer_name,

a1.account_number customer_number,

a3.STATUS site_status,

 SITE_USE_CODE,LOCATION,PRIMARY_FLAG

,PARTY_SITE_NUMBER,A5.ADDRESS1,A5.ADDRESS2,A5.ADDRESS3,A5.ADDRESS4,A5.CITY,A5.POSTAL_CODE,A5.STATE,A5.COUNTRY

 from hz_cust_accounts a1,hz_cust_acct_sites_all a2,

 hz_cust_site_uses_all a3,HZ_PARTY_SITES A4,HZ_LOCATIONS A5,

 hz_parties hp

 where a1.CUST_ACCOUNT_ID=a2.CUST_ACCOUNT_ID

 and a1.party_id=hp.party_id

 and a2.CUST_ACCT_SITE_ID=a3.CUST_ACCT_SITE_ID

 and nvl(A1.STATUS,'A')='A'

 and nvl(A3.STATUS,'A')='A'

 AND A2.PARTY_SITE_ID=A4.PARTY_SITE_ID

 AND A4.LOCATION_ID=A5.LOCATION_ID