AR - Oracle Fusion AR Customer Tables and Scripts

Base Tables Descriptions

HZ_PARTIES Foundational part of Oracle’s Trading Community

Architecture (TCA). Trading Partner can be on 3 types

•  Person (e.g., Jane Doe)

•  Organization (e.g., Oracle Corporation)

•  Group (e.g., World Wide Web Consortium)

HZ_PARTY_SITES Location details like the Address related to a party

HZ_CUST_ACCOUNTS contains details about customer accounts or business relationships that the deploying company has formed with parties classified as either Organizations or Individuals

HZ_LOCATIONS Postal Address, physical location of party site.

HZ_ORIG_SYSTEMS_B Source systems are systems that load data into the

HZ_ORIG_SYSTEMS_TL TCA Registry

HZ_ORIG_SYS_REFERENCES source system references details stored.

HZ_RELATIONSHIPS Relationship between the party and the contact person details.

HZ_CUST_ACCT_SITES_ALL Store Customer Site against the Oracle Business Unit.

HZ_CUST_SITE_USES_ALL Purpose of the Site either Bill-To , Ship-To , Dunning Letter.

HZ_CUST_ACCOUNT_ROLES A role or function that a party performs for a customer account.

HZ_CONTACT_POINTS Communication to the contact person either by Phone, Website URL, or Email.

HZ_GEOGRAPHIES Stores Geography detail and its Address format.

1. Customer Account and Addresses - Query to fetch Customer details at Account, Address, Sites, and Site Uses.

SELECT hca.cust_account_id,

       hca.party_id                    cust_acct_party_id,

       hca.account_number,

       hca.orig_system_reference       acct_orig_sys_ref,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.orig_system_reference        party_orig_sys_ref,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hcas.orig_system_reference      acct_site_orig_sys_ref,

       hcas.status                     acct_site_status,

       hcas.set_id                     acct_site_set_id,

       hps.location_id,

       hps.party_id,

       hps.party_site_number,

       hl.orig_system_reference        loc_orig_sys_ref,

       hl.country,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.address4,

       hl.city,

       hl.state,

       hl.postal_code,

       hcsu.site_use_id,

       hcsu.site_use_code,

       hcsu.status                     site_use_status,

       hcsu.location,

       hcsu.set_id                     site_user_set_id,

       acct_osr.orig_system            acct_osr_system,

       acct_osr.orig_system_reference  acct_osr_value,

       party_osr.orig_system           party_osr_system,

       party_osr.orig_system_reference party_osr_value,

       site_osr.orig_system            site_osr_system,

       site_osr.orig_system_reference  site_osr_value,

       loc_osr.orig_system             loc_osr_system,

       loc_osr.orig_system_reference   loc_osr_value

  FROM hz_cust_accounts       hca,

       hz_parties             hp,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_locations           hl,

       hz_cust_site_uses_all  hcsu,

       hz_orig_sys_references acct_osr,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references site_osr,

       hz_orig_sys_references loc_osr

 WHERE     hca.party_id = hp.party_id

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hps.location_id = hl.location_id

       AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

       AND acct_osr.owner_table_name = 'HZ_CUST_ACCOUNTS'

       AND acct_osr.owner_table_id = hca.cust_account_id

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND site_osr.owner_table_name = 'HZ_CUST_ACCT_SITES_ALL'

       AND site_osr.owner_table_id = hcas.cust_acct_site_id

       AND loc_osr.owner_table_name = 'HZ_LOCATIONS'

       AND loc_osr.owner_table_id = hps.location_id

       AND hps.party_site_number = :p_site_number -- Query for Particular Customer Site

       AND hca.account_number = :p_acct_number -- Query for particular Customer Account Number

2.Customer Account and Contact Details - Query to fetch customer details in the account and the contact person details.

SELECT hca.CUST_ACCOUNT_ID,

       hca.PARTY_ID                    cust_acct_party_id,

       hca.ACCOUNT_NUMBER,

       hcas.CUST_ACCT_SITE_ID,

       hcas.PARTY_SITE_ID,

       hps.PARTY_SITE_NUMBER,

       hp.PARTY_NUMBER,

       hp.PARTY_NAME,

       hp.PARTY_TYPE,

       hp.party_id,

       hp.PERSON_FIRST_NAME,

       hp.PERSON_MIDDLE_NAME,

       hp.PERSON_LAST_NAME,

       party_osr.ORIG_SYSTEM           party_osr_system,

       party_osr.ORIG_SYSTEM_REFERENCE party_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       HZ_CUST_ACCOUNT_ROLES  HCAR,

       HZ_RELATIONSHIPS       HR,

       HZ_PARTIES             HP,

       HZ_ORIG_SYS_REFERENCES party_osr

 WHERE     1 = 1

       AND hca.CUST_ACCOUNT_ID = hcas.CUST_ACCOUNT_ID

       AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND HCAR.RELATIONSHIP_ID = HR.RELATIONSHIP_ID

       AND HP.party_id = HR.Object_id

       AND HR.object_type = 'PERSON'

       AND party_osr.OWNER_TABLE_NAME = 'HZ_PARTIES'

       AND party_osr.OWNER_TABLE_ID = hp.PARTY_ID

       AND hps.PARTY_SITE_NUMBER = :P_SITE_NUMBER

       AND hca.account_number = :P_ACCT_NUMBER -- Query for Customer Account detail

       AND hp.PARTY_NAME = :P_PARTY_NAME    -- Query for Contact Person detail

3. Customer Account and Contact Point Details - Query to fetch customer details in the account, contact person and contact points details

SELECT hca.cust_account_id,

       hca.party_id           cust_acct_party_id,

       hca.account_number,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hps.party_site_number,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.party_id,

       hp.person_first_name,

       hp.person_middle_name,

       hp.person_last_name,

       party_osr.orig_system  party_osr_system,

       party_osr.orig_system_reference                     party_osr_value,

       hcp_phone.contact_point_type                        phone_cp_type,

       hcp_phone.contact_point_id                          phone_cp_id,

       hcp_phone.status       phone_status,

       hcp_phone.primary_flag                              phone_primary_flag,

       hcp_phone.phone_area_code || hcp_phone.phone_number phone_no,

       hcp_email.contact_point_type                        email_cp_type,

       hcp_email.contact_point_id                          email_cp_id,

       hcp_email.status       email_status,

       hcp_email.primary_flag                              email_primary_type,

       hcp_email.email_address,

       phone_osr.orig_system  phone_osr_system,

       phone_osr.orig_system_reference                     phone_osr_value,

       email_osr.orig_system  email_osr_system,

       email_osr.orig_system_reference                     email_osr_value,

       cust_osr.orig_system   cust_osr_system,

       cust_osr.orig_system_reference                      cust_osr_value,

       site_osr.orig_system   site_osr_system,

       site_osr.orig_system_reference                      site_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_cust_account_roles  hcar,

       hz_relationships       hr,

       hz_parties             hp,

       hz_contact_points      hcp_email,

       hz_contact_points      hcp_phone,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references phone_osr,

       hz_orig_sys_references email_osr,

       hz_orig_sys_references cust_osr,

       hz_orig_sys_references site_osr

 WHERE     1 = 1

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND hcar.relationship_id = hr.relationship_id

       AND hp.party_id = hr.object_id

       AND hr.object_type = 'PERSON'

       AND hcp_email.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_email.owner_table_id(+) = hp.party_id

       AND hcp_email.contact_point_type(+) = 'EMAIL'

       AND hcp_phone.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_phone.owner_table_id(+) = hp.party_id

       AND hcp_phone.contact_point_type(+) = 'PHONE'

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND phone_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND phone_osr.owner_table_id(+) = hcp_phone.contact_point_id

       AND email_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND email_osr.owner_table_id(+) = hcp_email.contact_point_id

       AND cust_osr.owner_table_name = 'HZ_PARTIES'

       AND cust_osr.owner_table_id = hca.party_id

       AND site_osr.owner_table_name = 'HZ_PARTY_SITES'

       AND site_osr.owner_table_id = hps.party_site_id

       AND hps.party_site_number = :p_site_number

       AND hca.account_number = :p_acct_number

       AND hp.party_name = :p_party_name