Query to display the customer name and customer address details

Introduction:

This query is used to fetch the data of customer name and also the details like the account number, addresses, city, country, postal code and customer account id. In this CUST_NAME is used as a parameter so that fetching of data will be done based on the parameter value we pass to the query.

SQL Query:

SELECT p.PARTY_NAME,

ca.ACCOUNT_NUMBER,

loc.address1,

loc.address2,

loc.address3,

loc.city,

loc.postal_code,

loc.country,

ca.CUST_ACCOUNT_ID

FROM apps.ra_customer_trx_all I,

apps.hz_cust_accounts CA,

apps.hz_parties P,

apps.hz_locations Loc,

apps.hz_cust_site_uses_all CSU,

apps.hz_cust_acct_sites_all CAS,

apps.hz_party_sites PS

WHERE I.COMPLETE_FLAG    =’Y’

AND I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID

AND ca.PARTY_ID          =p.PARTY_ID

AND I.bill_to_site_use_id=csu.site_use_id

AND csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID

AND cas.PARTY_SITE_ID    =ps.party_site_id

AND ps.location_id       =loc.LOCATION_ID

AND p.PARTY_NAME         = <CUST_NAME> ;

 

 

Parameter: Customer_Name