Thursday, September 13, 2012

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

2 Responses to “Query to fetch customer Profile Details (HZ_CUSTOMER_PROFILES)”

Anonymous said...
May 15, 2014 at 4:46 PM

Thanks!


Anonymous said...
April 2, 2018 at 12:20 PM

It helped, but for credit limit I used cust profile amounts which failed.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.