In this post we will discuss on customer. A customer is an entity with whom we do the business and having selling relationship. A customer can be party, organization, partner etc. A customer has a relationship identifier like account_number, cust_account_id also having customer contact like person name, email, contact number, address. Customer also having location details where the product or service will deliver which can be includes customer site, bill_to and ship_to location.
Customer Tables
HZ_PARTIES
HZ_PARTY_SITES
HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
ZX_PARTY_TAX_PROFILE
HZ_PARTY_SITES
HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
ZX_PARTY_TAX_PROFILE
Detailed Query
SELECT
hp.party_name org_name
,hp.party_number org_number
, hca.account_number
,hca.account_name acc_description
,hca.customer_class_code cust_class
,(SELECT NVL(hf.Credit_Limit,0)
FROM hz_customer_profiles_f hf
WHERE hf.cust_account_id = hca.Cust_Account_Id
AND (TRUNC (SYSDATE) BETWEEN hf.
effective_start_date
AND hf.
effective_end_date)
AND hf.status = 'A'
) Credit_Limit
,hps.party_site_number
,hps.party_site_name
,addr_set.set_code address_set
,fnd.territory_short_name country
,site_reg.registration_number site_regi_number
,INITCAP(site_reg.registration_status_code) site_status
,hou.name cust_business_unit
,hl.CITY
,hop.DUNS_NUMBER_C DUNS_Number
FROM hz_parties hp
,hz_party_sites hps
,hz_cust_accounts hca
,hz_cust_acct_sites_all hcas
,hz_locations hl
,HZ_ORGANIZATION_PROFILES hop
,fnd_territories_vl fnd
,zx_party_tax_profile site_pro
,zx_registrations site_reg
,fnd_setid_sets addr_set
,hr_operating_units hou
,fnd_setid_assignments fsa
WHERE 1=1
AND hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hps.party_site_id = hcas.party_site_id(+)
AND hca.cust_account_id = hcas.cust_account_id(+)
AND hps.LOCATION_ID=hl.LOCATION_ID
AND hp.party_id=hop.party_id
AND hp.country = fnd.territory_code(+)
AND site_pro.party_id(+) = hps.party_site_id --Party_id in site_pro table is nothing but party_site_id
AND site_pro.party_tax_profile_id = site_reg.party_tax_profile_id(+)
AND TRUNC (SYSDATE) BETWEEN TRUNC (nvl(site_reg.effective_from,SYSDATE -1)) AND TRUNC ( NVL (site_reg.effective_to, SYSDATE ))
AND addr_set.set_id(+) = hcas.set_id
AND addr_set.LANGUAGE = 'US' --To get only one one record
AND hcas.SET_ID = FSA.SET_ID(+)
AND TO_NUMBER (FSA.DETERMINANT_VALUE) = HOU.ORGANIZATION_ID(+)
AND fsa.determinant_type(+) = 'BU' -- To get only business unit data
AND reference_group_name(+) = 'HZ_CUSTOMER_ACCOUNT_SITE'-- Businees unit defined at Cust account level
AND hp.party_id = NVL(:p_party_name,hp.party_id)
AND NVL(hca.cust_account_id,-999) = NVL(:p_acc_desc,NVL(hca.cust_account_id,-999))
AND NVL(addr_set.set_id,-999) = NVL(:p_set_id,NVL(addr_set.set_id,-999))
AND NVL(site_reg.registration_number,-999) = NVL(:p_registration_num,NVL(site_reg.registration_number,-999))
AND NVL(hou.organization_id,-999) = NVL(:p_business_unit,NVL(hou.organization_id,-999))
AND HP.PARTY_ID IN
(SELECT HPS.PARTY_ID
FROM FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
HR_OPERATING_UNITS HOU,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
FND_SETID_ASSIGNMENTS FSA
WHERE USER_GUID = FND_GLOBAL.USER_GUID
AND ACTIVE_FLAG != 'N'
AND HOU.ORGANIZATION_ID = FURDA.ORG_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.SET_ID = FSA.SET_ID
AND TO_NUMBER (FSA.DETERMINANT_VALUE) =
HOU.ORGANIZATION_ID
UNION
SELECT HPS.PARTY_ID
FROM PER_USERS PU,
PER_ALL_PEOPLE_F PPF,
PER_ALL_ASSIGNMENTS_F PAPF,
HR_OPERATING_UNITS HOU,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
FND_SETID_ASSIGNMENTS FSA
WHERE PPF.PERSON_ID = PU.PERSON_ID
AND PAPF.PERSON_ID = PPF.PERSON_ID
AND HOU.ORGANIZATION_ID = PAPF.BUSINESS_UNIT_ID
AND PU.USERNAME = FND_GLOBAL.USER_NAME
AND PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.SET_ID = FSA.SET_ID
AND TO_NUMBER (FSA.DETERMINANT_VALUE) =
HOU.ORGANIZATION_ID)
ORDER BY hp.party_name,hca.account_name;
hp.party_name org_name
,hp.party_number org_number
, hca.account_number
,hca.account_name acc_description
,hca.customer_class_code cust_class
,(SELECT NVL(hf.Credit_Limit,0)
FROM hz_customer_profiles_f hf
WHERE hf.cust_account_id = hca.Cust_Account_Id
AND (TRUNC (SYSDATE) BETWEEN hf.
effective_start_date
AND hf.
effective_end_date)
AND hf.status = 'A'
) Credit_Limit
,hps.party_site_number
,hps.party_site_name
,addr_set.set_code address_set
,fnd.territory_short_name country
,site_reg.registration_number site_regi_number
,INITCAP(site_reg.registration_status_code) site_status
,hou.name cust_business_unit
,hl.CITY
,hop.DUNS_NUMBER_C DUNS_Number
FROM hz_parties hp
,hz_party_sites hps
,hz_cust_accounts hca
,hz_cust_acct_sites_all hcas
,hz_locations hl
,HZ_ORGANIZATION_PROFILES hop
,fnd_territories_vl fnd
,zx_party_tax_profile site_pro
,zx_registrations site_reg
,fnd_setid_sets addr_set
,hr_operating_units hou
,fnd_setid_assignments fsa
WHERE 1=1
AND hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hps.party_site_id = hcas.party_site_id(+)
AND hca.cust_account_id = hcas.cust_account_id(+)
AND hps.LOCATION_ID=hl.LOCATION_ID
AND hp.party_id=hop.party_id
AND hp.country = fnd.territory_code(+)
AND site_pro.party_id(+) = hps.party_site_id --Party_id in site_pro table is nothing but party_site_id
AND site_pro.party_tax_profile_id = site_reg.party_tax_profile_id(+)
AND TRUNC (SYSDATE) BETWEEN TRUNC (nvl(site_reg.effective_from,SYSDATE -1)) AND TRUNC ( NVL (site_reg.effective_to, SYSDATE ))
AND addr_set.set_id(+) = hcas.set_id
AND addr_set.LANGUAGE = 'US' --To get only one one record
AND hcas.SET_ID = FSA.SET_ID(+)
AND TO_NUMBER (FSA.DETERMINANT_VALUE) = HOU.ORGANIZATION_ID(+)
AND fsa.determinant_type(+) = 'BU' -- To get only business unit data
AND reference_group_name(+) = 'HZ_CUSTOMER_ACCOUNT_SITE'-- Businees unit defined at Cust account level
AND hp.party_id = NVL(:p_party_name,hp.party_id)
AND NVL(hca.cust_account_id,-999) = NVL(:p_acc_desc,NVL(hca.cust_account_id,-999))
AND NVL(addr_set.set_id,-999) = NVL(:p_set_id,NVL(addr_set.set_id,-999))
AND NVL(site_reg.registration_number,-999) = NVL(:p_registration_num,NVL(site_reg.registration_number,-999))
AND NVL(hou.organization_id,-999) = NVL(:p_business_unit,NVL(hou.organization_id,-999))
AND HP.PARTY_ID IN
(SELECT HPS.PARTY_ID
FROM FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
HR_OPERATING_UNITS HOU,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
FND_SETID_ASSIGNMENTS FSA
WHERE USER_GUID = FND_GLOBAL.USER_GUID
AND ACTIVE_FLAG != 'N'
AND HOU.ORGANIZATION_ID = FURDA.ORG_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.SET_ID = FSA.SET_ID
AND TO_NUMBER (FSA.DETERMINANT_VALUE) =
HOU.ORGANIZATION_ID
UNION
SELECT HPS.PARTY_ID
FROM PER_USERS PU,
PER_ALL_PEOPLE_F PPF,
PER_ALL_ASSIGNMENTS_F PAPF,
HR_OPERATING_UNITS HOU,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
FND_SETID_ASSIGNMENTS FSA
WHERE PPF.PERSON_ID = PU.PERSON_ID
AND PAPF.PERSON_ID = PPF.PERSON_ID
AND HOU.ORGANIZATION_ID = PAPF.BUSINESS_UNIT_ID
AND PU.USERNAME = FND_GLOBAL.USER_NAME
AND PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.SET_ID = FSA.SET_ID
AND TO_NUMBER (FSA.DETERMINANT_VALUE) =
HOU.ORGANIZATION_ID)
ORDER BY hp.party_name,hca.account_name;