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
