Query to fetch customer Profile Details (HZ_CUSTOMER_PROFILES)

The profile information available in the HZ_CUSTOMER_PROFILES can be created in three levels namely Party, Customer Account  and Customer Account Site. The values available in the three columns of the table HZ_CUSTOMER_PROFILES PARTY_ID , CUST_ACCOUNT_ID,SITE_USE_ID dictates the level of profile information.

Party Level Profile:

Party_id                = vl_party_id

Cust_account_id = -1

Site_use_id           = NULL

Customer Account Level Profile:

Party_id                = vl_party_id

Cust_account_id = vl_cust_account_id

Site_use_id           = NULL

Customer Account Site Level Profile:

Party_id                = vl_party_id

Cust_account_id = vl_cust_account_id

Site_use_id           = vl_cust_site_use_id

A Query with joins to the other master table is given below,

SELECT cp.cust_account_profile_id,

       cp.cust_account_id,

       cp.collector_id,

       col.NAME collector_name,

       cp.profile_class_id,

       cpc.NAME profile_class_name,

       cp.site_use_id,

       term.NAME standard_terms,

       cp.statement_cycle_id,

       cyc.NAME statement_cycle_name,

       cp.autocash_hierarchy_id,

       hier.hierarchy_name autocash_hierarchy_name,

       cp.grouping_rule_id,

       grp.NAME grouping_rule_name,

       cp.autocash_hierarchy_id_for_adr,

       hier_adr.hierarchy_name autocash_hierarchy_name_adr,

       cp.*

  FROM hz_customer_profiles cp,

       ar_collectors col,

       hz_cust_profile_classes cpc,

       ar_dunning_letter_sets dun_set,

       ar_statement_cycles cyc,

       ar_autocash_hierarchies hier,

       ra_grouping_rules grp,

       ra_terms term,

       ar_autocash_hierarchies hier_adr

 WHERE cp.collector_id          = col.collector_id

   AND cp.profile_class_id      = cpc.profile_class_id(+)

   AND cp.dunning_letter_set_id = dun_set.dunning_letter_set_id(+)

   AND cp.statement_cycle_id    = cyc.statement_cycle_id(+)

   AND cp.autocash_hierarchy_id = hier.autocash_hierarchy_id(+)

   AND cp.grouping_rule_id      = grp.grouping_rule_id(+)

   AND cp.standard_terms        = term.term_id(+)

   AND cp.autocash_hierarchy_id_for_adr = hier_adr.autocash_hierarchy_id(+)

   AND cp.party_id              = vl_party_id

   AND cp.cust_account_id       = vl_cust_account_id

   AND cp.site_use_id           = vl_site_use_id

R12: SQL scripts with Customers and Profile Classes

Script 1:
Provide Customer Number to the following SQL query. It gives you the Profile Class attached to the Customer.

select hcpc.name PROFILE_CLASS

  from apps.hz_cust_profile_classes hcpc,

       apps.hz_customer_profiles hcp,

       apps.hz_cust_accounts hca

 where hcpc.profile_class_id = hcp.profile_class_id

   and hcp.party_id = hca.party_id

   and hca.account_number = '&Cust_Number';

Script 2:
Provide Profile Class to the following SQL query. It gives list of all customers attached to that Profile Class.

select hca.cust_account_id,

       hca.account_number,

       hca.status

  from apps.hz_cust_accounts hca,

       apps.hz_customer_profiles hcp,

       apps.hz_cust_profile_classes hcpc

 where trim(hcpc.name) = trim('&Profile_Class_Name')

   and hcp.profile_class_id = hcpc.profile_class_id

   and hca.cust_account_id = hcp.cust_account_id;