TCA QUERIES

The SQL queries involving following TCA tables are explained in the next section.

1. HZ_PARTIES

2. HZ_ORGANIZATION_PROFILES

3. HZ_PERSON_PROFILES

4. HZ_CUSTOMER_PROFILES

5. HZ_LOCATIONS

6. HZ_PARTY_SITES

7. HZ_PARTY_SITE_USES

8. HZ_CUST_ACCOUNTS

9. HZ_CUST_ACCT_SITES

10. HZ_CUST_SITE_USES

11. HZ_CONTACT_POINTS

12. HZ_ORG_CONTACTS

13. HZ_ORG_CONTACT_ROLES

14. HZ_RELATIONSHIPS

15. HZ_CLASS_CATEGORIES

16. HZ_CLASS_CODE_DENORM

17. HZ_CODE_ASSIGNMENTS

18. HZ_CLASS_CATEGORY_USES

19. HZ_RELATIONSHIP_TYPES

Query to fetch Organization party details.

• When a new Organization is created, a record is created in the HZ_PARTIES table with PARTY_TYPE = ORGANIZATION. The HZ_PARTIES table holds the basic information about the party like party name, party number, party type etc.
• Query based on party id. Party id is primary key in HZ_PARTIES.

           SELECT * 

              FROM hz_parties 

            WHERE party_id = <enter party id here> 

                  AND party_type = 'ORGANIZATION'

• Query based on party number. Party number is stored in a VARCHAR type column. So, it should always be included in single quotes. Party number is unique for all the parties and will return a single row for one party number.

            SELECT * 

                FROM hz_parties 

             WHERE party_number = '<enter party number here>' 

                   AND party_type = 'ORGANIZATION'

• Query based on party name. Multiple parties can have same name. Below query may return multiple rows depending on the party name.

            SELECT * 

               FROM hz_parties 

            WHERE party_name = '<enter party name here>' 

                   AND party_type = 'ORGANIZATION'

• To find active parties, add join

             AND status = 'A'

• To find inactive parties, add join

             AND status = 'I'

• When a new Organization is created, a record is also created in HZ_ORGANIZATION_PROFILES table. The table holds more detailed and specific information about the organization like organization‟s finance history, bank, employees, etc. The primary key is ORGANIZATION_PROFILE_ID 

             SELECT * 

                FROM hz_organization_profiles 

             WHERE party_id = <enter party id here>

• When a new Person is created, a record is created in the HZ_PARTIES table with PARTY_TYPE = PERSON. The HZ_PARTIES table holds the basic information about the party like party name, party number, party type etc.

           SELECT * 

              FROM hz_parties 

            WHERE party_name = '<enter party name here>' 

                  AND party_type = 'PERSON'

• HZ_PARTY_SITES table relates an existing party from the HZ_PARTIES table with an address location from the HZ_LOCATIONS table. The table stores location-specific party information such as MAILSTOP and ADDRESSEE.

            SELECT hps.* 

                FROM hz_parties hp ,

                             hz_party_sites hps 

             WHERE hp.party_id = hps.party_id 

                   AND hp.party_id = <enter party id here>

• Query to find addresses for a party.

            SELECT hl.* 

               FROM hz_parties hp ,

                            hz_party_sites hps ,

                            hz_locations hl 

            WHERE hp.party_id = hps.party_id 

                  AND hp.party_id = <enter party id here> 

                   AND hl.location_id = hps.location_id

• Query to find accounts for a party.

           SELECT * FROM hz_cust_accounts WHERE party_id = <enter party id here>

          

             CUST_ACCOUNT_ID is primary key of the table.

• Query to find customer account sites for a party.

           CUST_ACCT_SITE_ID is the primary key of the table.

            SELECT hcas.* 

               FROM hz_cust_accounts hca ,

                            hz_cust_acct_sites_all hcas ,

                            hz_parties hp 

           WHERE hp.party_id = <enter party id here> 

                 AND hca.cust_account_id = hcas.cust_account_id 

                 AND hp.party_id = hca.party_id

              OR

          SELECT hcas.* 

              FROM hz_cust_acct_sites_all hcas ,

                           hz_parties hp ,

                           hz_party_sites hps 

           WHERE hp.party_id = hps.party_id 

                  AND hp.party_id = <enter party id here> 

                  AND hcas.party_site_id = hps.party_site_id

• Query to find business purposes (ship to, bill to etc) of account sites.

            SELECT hcsu.* 

               FROM hz_cust_accounts hca ,

                            hz_cust_acct_sites_all hcas ,

                            hz_cust_site_uses_all hcsu ,

                            hz_parties hp 

            WHERE hp.party_id = <enter party id here> 

                  AND hp.party_id = hca.party_id 

                  AND hca.cust_account_id = hcas.cust_account_id 

                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id 

                  AND hcsu.site_use_code = '<enter site use here>'

           Site use code can be SHIP_TO, BILL_TO, SOLD_TO etc.

            

           SITE_USE_ID is primary key of the table.

• Query to find contact points for a party/party site.

           SELECT * 

              FROM hz_contact_points 

             WHERE owner_table_name = 'HZ_PARTIES' 

                  AND owner_table_id = <enter party id here>

• Query to find relationship between two parties.

            SELECT * 

               FROM hz_relationships 

            WHERE subject_table_name = 'HZ_PARTIES' 

                 AND object_table_name = 'HZ_PARTIES' 

                 AND relationship_type = <enter relationship type here>

• Query to find Organization contacts.

             SELECT hoc.* 

                 FROM hz_org_contacts hoc ,

                              hz_relationships hr 

              WHERE hoc.party_relationship_id = hr.relationship_id 

                    AND hr.subject_id = <enter party id of party A> 

                    AND hr.object_id = <enter party id of party B>