Oracle ERP Customer- Tables and Relations , Queries and API

Oracle ERP Customer- Basic Tables and Relations , Queries and API

HZ_PARTIES :- Parties are organizations, people, relationship  and groups.

HZ_LOCATIONS:- Address Information is stored in this table.

HZ_PARTY_SITES:- Party Site information and connection between Party and  Address(Location)

It has 1:N relationship with Party. There can be N party sites for one Party connected by Party_id field. It has 1:1 relationship with Location. For one entry in Location table there can be only one entry in Party site connected by Location_id field.

HZ_CUST_ACCOUNTS:- Customer account Number information. Is has 1:N relationship with Party. There can be Many Accounts for a Party connected by Party_id.

HZ_CUST_ACCT_SITES_ALL:- Account Site Information. It is Connection between Party Site, Account and OU.  There can be only 1 Customer Account site for the combination of OU,Party site and cust account connected by Cust_Account_id ,Party_site_id and Org_id.

HZ_CUST_SITE_USES_ALL:- Usage information of the site like Bill to, Ship to. It has 1:N relationship with Customer Account sites. There can be many Site uses for an Account site connected by Cust_Acct_Site_id.

Basic Queries for tables

select * from apps.hz_parties
where party_id =
and party_name =

select * from apps.hz_cust_accounts
where account_number =
and party_id =
and cust_account_id =

select * from apps.hz_party_sites
where party_id =
and party_site_number =

select * from apps.hz_locations where location_id =

select cust_acct_site_id,cust_account_id,party_site_id,org_id from apps.hz_cust_acct_sites_all
where cust_acct_site_id =
and cust_account_id =
and party_site_id =

select * from apps.hz_cust_site_uses_all where cust_acct_site_id =
and site_use_id =

Query to get the Sites details based on account number and OU

select *
  from hz_cust_accounts hca,
       hz_cust_acct_sites_all hcasa,
       hz_party_sites hps,
       hz_cust_site_uses_all hcsua     
 where     hca.cust_account_id = hcasa.cust_account_id
       and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
       and hps.party_site_id = hcasa.party_site_id
       and hca.status = 'A'
       and hps.status = 'A'
       and hcasa.status = 'A'
       and hcsua.site_use_code = 'BILL_TO'
       and hcsua.status = 'A'
       and hca.account_number =
       and hcasa.org_id =


API to create Party and Account

HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
         p_init_msg_list          => FND_API.G_TRUE,
         p_cust_account_rec       => acct_rec,
         p_organization_rec       => org_rec,
         p_customer_profile_rec   => prof_rec,
         p_create_profile_amt     => FND_API.G_FALSE,
         x_cust_account_id        => p_out_num_cust_account_id,
         x_account_number         => p_out_chr_cust_account_number,
         x_party_id               => p_out_num_party_id,
         x_party_number           => p_out_chr_party_number,
         x_profile_id             => p_organization_profile_id,
         x_return_status          => p_return_status,
         x_msg_count              => p_msg_count,

         x_msg_data               => p_msg_data);

acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
party_rec   HZ_PARTY_V2PUB.PARTY_REC_TYPE;
org_rec     HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
prof_rec    HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;

API to update Party

   HZ_PARTY_V2PUB.update_organization (
         p_organization_rec              => org_rec,
         p_party_object_version_number   => l_party_object_version,
         x_profile_id                    => p_organization_profile_id,
         x_return_status                 => p_return_status,
         x_msg_count                     => p_msg_count,

         x_msg_data                      => p_msg_data);

party_rec   HZ_PARTY_V2PUB.PARTY_REC_TYPE;
org_rec     HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;

API to update Account

 HZ_CUST_ACCOUNT_V2PUB.update_cust_account (

         p_cust_account_rec        => acct_rec,

         p_object_version_number   => l_cust_acct_object_version,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;

API to create Location

HZ_LOCATION_V2PUB.create_location (

            p_location_rec    => location_rec,

            x_location_id     => p_location_id,

            x_return_status   => p_return_status,

            x_msg_count       => p_msg_count,

            x_msg_data        => p_msg_data);

location_rec        hz_location_v2pub.location_rec_type;

API to update Location

HZ_LOCATION_V2PUB.update_location (

         p_location_rec            => location_rec,

         p_object_version_number   => l_object_version_location,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

location_rec        hz_location_v2pub.location_rec_type;

API to create Party Site

HZ_PARTY_SITE_V2PUB.create_party_site (

         p_party_site_rec      => psite_rec,

         x_party_site_id       => p_party_site_id,

         x_party_site_number   => p_party_site_number,

         x_return_status       => p_return_status,

         x_msg_count           => p_msg_count,

         x_msg_data            => p_msg_data);

 

psite_rec   hz_party_site_v2pub.party_site_rec_type;

API to update Party site

HZ_PARTY_SITE_V2PUB.update_party_site (

         p_party_site_rec          => psite_rec,

         p_object_version_number   => l_object_version_party_site,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

psite_rec                     hz_party_site_v2pub.party_site_rec_type;

API to create Cust Site

  HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (

         p_cust_acct_site_rec   => asite_rec,

         x_cust_acct_site_id    => p_cust_acct_site_id,

         x_return_status        => p_return_status,

         x_msg_count            => p_msg_count,

         x_msg_data             => p_msg_data);

 asite_rec           hz_cust_account_site_v2pub.cust_acct_site_rec_type;

API to update Cust Site

 HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site (

         p_cust_acct_site_rec      => asite_rec,

         p_object_version_number   => l_object_version_number,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

asite_rec                       hz_cust_account_site_v2pub.cust_acct_site_rec_type;

API to create Cust Site Uses

      HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (

         p_cust_site_use_rec      => suse_rec,

         p_customer_profile_rec   => profile_rec,

         p_create_profile         => fnd_api.g_false,

         p_create_profile_amt     => fnd_api.g_false,

         x_site_use_id            => p_site_use_id,

         x_return_status          => p_return_status,

         x_msg_count              => p_msg_count,

         x_msg_data               => p_msg_data);

suse_rec                hz_cust_account_site_v2pub.cust_site_use_rec_type;

profile_rec             hz_customer_profile_v2pub.customer_profile_rec_type;

API to update Cust Site Uses

HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (

            p_cust_site_use_rec       => suse_rec,

            p_object_version_number   => l_site_object_version_number,

            x_return_status           => p_return_status,

            x_msg_count               => p_msg_count,

            x_msg_data                => p_msg_data);

suse_rec                        hz_cust_account_site_v2pub.cust_site_use_rec_type;