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