Friday, June 18, 2010

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