Query to Get Customer Information in r12

Query to Get Customer Information in r12

This Query will help to extract the Customer Detail Information’s in r12. I will share two queries related to Customer Information. First Query will extract the Customer Details with complete Site details information in r12. The second query will help to get the Customer Detail contact information’s in r12. This is one of the detail level SQL query for customer information’s. Here below I will share the complete sql query to get customer information in r12.

Most Important Tables Used by query to get customer information in r12

AR_CUSTOMERS

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HA_PARTY_SITES

HZ_LOCATIONS

HZ_CUST_ACCOUNT_ROLES

HZ_RELATIONSHIPS

HZ_ORG_CONTACTS

HZ_CONTACT_RESTRICTIONS

Detail SQL query to get customer information in r12

select a2.org_id,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) CUSTOMER_PAYMENT_TERM,

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

(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv

where code_combination_id=a3.GL_ID_REC) REC_GL_ACCT,(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv

where code_combination_id=a3.GL_ID_REV) REV_GL_ACCOUNT,

(SELECT NAME FROM QP_LIST_HEADERS_TL

WHERE LIST_HEADER_ID=a3.PRICE_LIST_ID

AND LANGUAGE='US') PRICE_LIST

from apps.ar_customers a1,apps.hz_cust_acct_sites_all a2,

apps.hz_cust_site_uses_all a3,apps.HZ_PARTY_SITES A4,apps.HZ_LOCATIONS A5

where a1.CUSTomer_ID=a2.CUST_ACCOUNT_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

and a2.org_id=:P_ORG_ID

and a2.CUST_ACCT_SITE_ID=a3.CUST_ACCT_SITE_ID

SELECT role_acct.account_number,

  AR.CUSTOMER_NAME,

  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,

 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