Tuesday, January 12, 2021

Query to get active Customer and their Profile Details (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS)

 

 

 

 

 

 

 

In this post we have given a query which helps you to gather the basic customer details and profile  information. In other words, it helps to understand the link between below tables.

Tables Used:

  1. HZ_PARTIES
  2. HZ_CUST_ACCOUNTS
  3. HZ_CUST_ACCT_SITES_ALL
  4. HZ_CUSTOMER_PROFILES
  5. HZ_CUST_PROFILE_AMTS
  6. HZ_CUST_SITE_USES_ALL
  7. RA_GROUPING_RULES
  8. AR_STATEMENT_CYCLES
  9. RA_TERMS
  10. AR_COLLECTORS
  11. HZ_CUST_PROFILE_CLASSES

Instance: R12

Script: 

SELECT DISTINCT hzp.party_name, hzp.orig_system_reference party_osr, hzp.party_number, hzp.party_type, hzp.duns_number, hzp.tax_reference, hzp.category_code, hzc.orig_system_reference cust_osr, hzc.account_number, hzc.account_name, hou.NAME ou_name, (SELECT a.NAME FROM ar.hz_cust_profile_classes a WHERE a.profile_class_id = hzcp.profile_class_id ) profile_class_name, (SELECT a.NAME FROM ar.ar_collectors a WHERE a.collector_id = hzcp.collector_id ) collector_name, (SELECT a.NAME FROM ar.ar_statement_cycles a WHERE statement_cycle_id = hzcp.statement_cycle_id ) statement_cycle_name, (SELECT a.NAME FROM apps.ra_terms a WHERE a.term_id = hzcp.standard_terms) payment_term_name, (SELECT a.NAME FROM ar.ra_grouping_rules a WHERE a.grouping_rule_id = hzcp.grouping_rule_id) grouping_rules_name, (SELECT a.orig_system_reference FROM hz_cust_site_uses_all a WHERE a.site_use_id = hzcp.site_use_id) profile_site_use_osr, hzcp.credit_checking, hzcp.tolerance, hzcp.credit_hold, hzcp.credit_balance_statements, hzcp.send_statements, hzcp.jgzz_attribute1 enable_late_charges,      hzcp.interest_charges,        hzcp.jgzz_attribute2 late_charge_calculation_trx,      hzcp.jgzz_attribute5 hold_charged_invoices_flag, hzcp.dunning_letters, hzcp.discount_terms, hzcp.interest_period_days, hzcp.payment_grace_days, hzcp.discount_grace_days, hzcp.auto_rec_incl_disputed_flag, hzcp.attribute_category cust_prof_dff_category, hzcp.attribute2 cust_prof_dff_attr2, hzcpm.currency_code, hzcpm.trx_credit_limit, hzcpm.overall_credit_limit, hzcpm.min_dunning_amount, hzcpm.min_dunning_invoice_amount, hzcpm.max_interest_charge, hzcpm.min_statement_amount, hzcpm.auto_rec_min_receipt_amount, hzcpm.interest_rate, hzcpm.min_fc_balance_amount, hzcpm.min_fc_invoice_amount, (SELECT a.orig_system_reference FROM hz_cust_site_uses_all a WHERE a.site_use_id = hzcpm.site_use_id ) profile_amt_site_use_osr FROM ar.hz_parties hzp, ar.hz_cust_accounts hzc,        ar.hz_cust_acct_sites_All has, ar.hz_customer_profiles hzcp, ar.hz_cust_profile_amts hzcpm, apps.hr_operating_units hou WHERE hzp.party_id = hzc.party_id AND hzc.cust_account_id = hzcp.cust_account_id and hzcp.cust_account_profile_id = hzcpm.cust_account_profile_id(+) AND has.cust_account_id = hzc.cust_Account_id AND HAS.ORG_ID = HOU.ORGANIZATION_ID AND hou.organization_id = 105 -- Operating unit id -- Customer is active and created after cut off date AND ((trunc(hzc.creation_date) >'01-MAY-2012' and hzc.status = 'A') -- AR invoices created after cut off date or exists (SELECT 1 FROM ar.ra_customer_trx_all t WHERE trunc(t.trx_date)>'01-MAY-2012' and t.bill_to_customer_id = hzc.cust_account_id and t.org_id = hzc.org_id and hzc.status = 'A') -- AR invoice Payments are in open status or exists (SELECT 1 FROM ar.ar_payment_schedules_all apsa WHERE apsa.CLASS IN ('INV', 'CM', 'DM','PMT') AND apsa.status = 'OP' AND apsa.org_id = hzc.org_id AND apsa.customer_id = hzc.cust_account_id AND to_number(apsa.acctd_amount_due_remaining) <> to_number('0') GROUP BY apsa.customer_id having sum(nvl(apsa.acctd_amount_due_remaining,0))<>0))

0 Responses to “Query to get active Customer and their Profile Details (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS)”

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.