Query to get customer details in oracle apps

Query to get customer details in oracle apps

In this post , I will share the Oracle SQL query to get customer detail in oracle apps. This sql query helps to extract all the relevant information of the customer in Oracle Application. Oracle information's stores in the oracle application in different different. database tables and this below sql query is extracting information from these different different tables to show all the data of the customers in the single extraction.

 

Query to get customer details in oracle apps

 

These are the above tables we are using in the sql query to get customer details in oracle apps.

 

 

select CUSTOMER_NAME,CUSTOMER_Number,a1.STATUS CUSTOMER_STATUS,a3.STATUS site_status,
SITE_USE_CODE,LOCATION,PRIMARY_FLAG,(SELECT NAME FROM JTF_RS_SALESREPS WHERE SALESREP_ID=a3.PRIMARY_SALESREP_ID AND ROWNUM=1) SALES_PERSON
,(SELECT NAME FROM RA_TERMS WHERE TERM_ID=PAYMENT_TERM_ID AND ROWNUM=1) PAYMENT_TERM,
(SELECT NAME FROM QP_LIST_HEADERS_TL
WHERE LIST_HEADER_ID=a3.PRICE_LIST_ID
AND LANGUAGE='US') PRICE_LIST,(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv
where code_combination_id=a3.GL_ID_REC) recv_account,(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv
where code_combination_id=a3.GL_ID_REV) REVNUE_ACCOUNT,
PARTY_SITE_NUMBER,A5.ADDRESS1,A5.ADDRESS2,A5.ADDRESS3,A5.ADDRESS4,A5.CITY,A5.POSTAL_CODE,A5.STATE,A5.COUNTRY,
from ar_customers a1,hz_cust_acct_sites_all a2,
hz_cust_site_uses_all a3,HZ_PARTY_SITES A4,HZ_LOCATIONS A5
where a1.CUSTomer_ID=a2.CUST_ACCOUNT_ID
and a2.org_id= :P_ORG_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

 

 

SELECT hcasa.org_id,
  role_acct.account_number,
  AR.CUSTOMER_NAME,
  hcasa.orig_system_reference,
  rel.subject_id,rel.object_id
  ,party.party_id                                           party_id,
  rel_party.party_id                                      rel_party_id,
  acct_role.cust_account_id                         ,
  acct_role.cust_acct_site_id                        ,
  party.person_pre_name_adjunct                 contact_prefix,
  substr(party.person_first_name, 1, 40)       contact_first_name,
  substr(party.person_middle_name, 1, 40)  contact_middle_name,
  substr(party.person_last_name, 1, 50)        contact_last_name,
  party.person_name_suffix                           contact_suffix,
  acct_role.status, org_cont.job_title             contact_job_title,
  org_cont.job_title_code                              contact_job_title_code,
  rel_party.address1                                       contact_address1,
  rel_party.address2                                       contact_address2,
  rel_party.address3                                       contact_address3,
  rel_party.address4                                       contact_address4,
  rel_party.country                                         contact_country,
  rel_party.state                                              contact_state,
  rel_party.city                                               contact_city,
  rel_party.county                                          contact_county,
  rel_party.postal_code                                  contact_postal_code,
 cont_point.email_address,
 cont_point.phone_area_code || ' ' || cont_point.phone_number phone
FROM    hz_contact_points                         cont_point,
        hz_cust_account_roles                         acct_role,
        hz_parties                                             party,
        hz_parties                                             rel_party,
        hz_relationships                                   rel,
        hz_org_contacts                                   org_cont,
        hz_cust_accounts                                 role_acct,
        hz_contact_restrictions                        cont_res,
        hz_person_language                            per_lang,
        hz_cust_acct_sites_all                        hcasa,
        AR_CUSTOMERS AR
WHERE   acct_role.party_id                   = rel.party_id
  and   acct_role.role_type                        = 'CONTACT'
  and   org_cont.party_relationship_id      = rel.relationship_id
  and   rel.subject_id                                  = party.party_id
  and   rel_party.party_id                           = rel.party_id
  and   cont_point.owner_table_id(+)        = rel_party.party_id
 and   cont_point.contact_point_type(+)   IN ( 'EMAIL','PHONE')
  and   cont_point.primary_flag(+)            = 'Y'
  and   acct_role.cust_account_id              = role_acct.cust_account_id
   and   acct_role.cust_account_id =AR.CUSTOMER_ID
  and   role_acct.party_id                          =  rel.object_id
  and   party.party_id                                 = per_lang.party_id(+)
  and   per_lang.native_language(+)         = 'Y'
  and   party.party_id                                 = cont_res.subject_id(+)
  and   cont_res.subject_table(+)               = 'HZ_PARTIES'
  and   role_acct.cust_account_id              =  hcasa.cust_account_id
  and   hcasa.cust_acct_site_id                  =  acct_role.cust_acct_site_id
 and   hcasa.org_id                                =  :P_ORGID