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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
2 Responses to “Query to fetch customer Profile Details (HZ_CUSTOMER_PROFILES)”
May 15, 2014 at 4:46 PM
Thanks!
April 2, 2018 at 12:20 PM
It helped, but for credit limit I used cust profile amounts which failed.
Post a Comment