Customer Tables Understanding

Hello everyone,

This is a just how customer informations are stored in apps tables. This post gives you clear explanation of how customers information are getting stored.

Customer Created Name: TESTCUST_1

Customer Accounts Created under the customer TESTCUST_1:

1.TESTCUST_1_1

2.TESTCUST_1_2

For Customer:

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTCUST_1'

Party Id: 307957

For Customer Accounts:

SELECT * FROM HZ_CUST_ACCOUNTS hca

WHERE hca.ACCOUNT_NAME IN ('TESTCUST_1_1','TESTCUST_1_2')

Account Number: 5391,5392

Account Id: 96675,96677

Customer Accounts Sites:

Under the Customer Account:

1.TESTCUST_1_1

Sites Name:

1.TESTCUSTS1_1

2.TESTCUSTS1_2

/*2.TESTCUST_1_2

Site Name:

1.TESTCUSTS2_1

2.TESTCUSTS2_1*/

SELECT * FROM HZ_CUST_ACCT_SITES_ALL hcsa

where hcsa.CUST_ACCOUNT_ID IN (96675) -- Customer Account Id of TESTCUST_1_1 ---Sites - TESTCUSTS1_1,TESTCUSTS1_2

Customer Account Site Id: 10554 - TESTCUSTS_1_2

10558 - TESTCUSTS_1_1

SELECT * FROM HZ_PARTY_SITES

WHERE PARTY_ID = 307957 -- Check Party Site Name : TESTCUSTS1_1,TESTCUSTS1_2

Party site Id: 179402 - TESTCUSTS_1_2

179404 - TESTCUSTS_1_1

For the Site TESTCUSTS1_1:

Contact Creation:

1.Contact Name: TESTS_1_1

2.Telephone - created

3.Email

There are two Id need to get, one is subject id and other is object id

1.Subject Id is related to Organization that is customer Party id here the party id is

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTCUST_1'

Party Id: 307957 - subject Id

2.Object Id is related to the contact name created that is Party Id of the person(Contact)

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTS_1_1'

Party Id : 307959 - Object id

Now Check in the Relationship table using subject id and object id

SELECT * FROM HZ_RELATIONSHIPS hr

WHERE hr.SUBJECT_ID = 307957

and hr.OBJECT_ID = 307959

and the reverse entry will also be there

SELECT * FROM HZ_RELATIONSHIPS hr

WHERE hr.SUBJECT_ID = 307959

and hr.OBJECT_ID = 307957

Relatonship Id:137025

Party Id:307960

SELECT * FROM HZ_PARTIES hp

WHERE hp.PARTY_ID = 307960 -- Party_type will be PARTY_RELATIONSHIP

SELECT * FROM HZ_ORG_CONTACTS hoc

where hoc.PARTY_RELATIONSHIP_ID = 137025 -- Relatioship Id

Org Contact Id: 118071

SELECT * FROM HZ_CONTACT_POINTS hp --HZ_CUST_CONTACT_POINTS hcp

WHERE hp.OWNER_TABLE_ID = 179404 --Party Site Id of the Contact - TESTCUSTS_1_1

SELECT * FROM HZ_CUST_ACCOUNT_ROLES hcr

WHERE hcr.CUST_ACCT_SITE_ID = 10558 --Customer Account Role created when creating contacts --This is the Customer Site Id of TESTCUSTS_1_1

References:
http://oracletechnofunc.blogspot.com/2010/06/customer-tables-understanding.html