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
---- ,hzcp.*
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
,iby_pmt_mthd_attrib_appl ibyappl
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 has.org_id = NVL(:p_org_id,has.org_id)
AND hzc.account_number = nvl(:p_cust_acct, hzc.account_number)
-- Customer is active and created after cut off date
-- 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))