Oracle Apps Knowledge Sharing

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:

HZ_PARTIES

HZ_CUST_ACCOUNTS

HZ_CUST_ACCT_SITES_ALL

HZ_CUSTOMER_PROFILES

HZ_CUST_PROFILE_AMTS

HZ_CUST_SITE_USES_ALL

RA_GROUPING_RULES

AR_STATEMENT_CYCLES

RA_TERMS

AR_COLLECTORS

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))

This summary is not available. Please click here to view the post.