Reports2017-11-18T12:27:27+00:00

AR Customer Credit Limits

Description
Categories: Enginatics, Financials
Customers profile amounts such as credit and order credit limits, minimum statement and dunning amounts.
The report can be restricted to amount limits on party, account or site use level via parameter 'Display Level'

select
x.ou,
x.party_name,
x.currency_code,
&column_party
&column_account
&column_site_use
x.party_number
from
(
select
haou.name ou,
hp.party_name,
hp.party_number,
hca.account_number,
fc.currency_code,
hcsua.location,
hcsua.site_use_code,
hcasa.party_site_id,
(select hcp.cust_account_profile_id from hz_customer_profiles hcp where hp.party_id=hcp.party_id and hcp.cust_account_id=-1) party_level_id,
(select hcp.cust_account_profile_id from hz_customer_profiles hcp where hca.cust_account_id=hcp.cust_account_id and hcp.site_use_id is null) account_level_id,
(select hcp.cust_account_profile_id from hz_customer_profiles hcp where hcsua.site_use_id=hcp.site_use_id) site_use_level_id
from
hr_all_organization_units haou,
hz_parties hp,
(select hca.* from hz_cust_accounts hca where :display_level in ('Account','Site Use','All')) hca,
(select hcasa.* from hz_cust_acct_sites_all hcasa where :display_level in ('Site Use','All')) hcasa,
(select hcsua.* from hz_cust_site_uses_all hcsua where :display_level in ('Site Use','All')) hcsua,
fnd_currencies fc
where
1=1 and
hp.party_id=hca.party_id(+) and
hca.cust_account_id=hcasa.cust_account_id(+) and
hcasa.org_id=haou.organization_id(+) and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id(+)
) x,
hz_cust_profile_amts hcpa0,
hz_cust_profile_amts hcpa1,
hz_cust_profile_amts hcpa2,
hz_party_sites hps,
hz_locations hl,
fnd_territories_tl ftt
where
(
hcpa0.auto_rec_min_receipt_amount is not null or
hcpa0.overall_credit_limit is not null or
hcpa0.trx_credit_limit is not null or
hcpa0.min_statement_amount is not null or
hcpa0.min_dunning_amount is not null or
hcpa0.min_dunning_invoice_amount is not null or
hcpa1.auto_rec_min_receipt_amount is not null or
hcpa1.overall_credit_limit is not null or
hcpa1.trx_credit_limit is not null or
hcpa1.min_statement_amount is not null or
hcpa1.min_dunning_amount is not null or
hcpa1.min_dunning_invoice_amount is not null or
hcpa2.auto_rec_min_receipt_amount is not null or
hcpa2.overall_credit_limit is not null or
hcpa2.trx_credit_limit is not null or
hcpa2.min_statement_amount is not null or
hcpa2.min_dunning_amount is not null or
hcpa2.min_dunning_invoice_amount is not null
) and
x.party_level_id=hcpa0.cust_account_profile_id(+) and
x.account_level_id=hcpa1.cust_account_profile_id(+) and
x.site_use_level_id=hcpa2.cust_account_profile_id(+) and
x.currency_code=hcpa0.currency_code(+) and
x.currency_code=hcpa1.currency_code(+) and
x.currency_code=hcpa2.currency_code(+) and
x.party_site_id=hps.party_site_id(+) and
hps.location_id=hl.location_id(+) and
hl.country=ftt.territory_code(+) and
ftt.language(+)=userenv('lang')
order by
x.party_name,
hl.country,
hps.party_site_number,
x.ou,
site_use,
x.currency_code

Parameter Name SQL text Validation
Account Number
hca.account_number=:account_number
LOV
Customer Name
upper(hp.party_name) like upper(:customer_name)
LOV
Operating Unit
hp.party_id in (
select
hca1.party_id
from
hr_all_organization_units haou1,
hz_cust_acct_sites_all hcasa1,
hz_cust_accounts hca1
where
haou1.name=:operating_unit and
haou1.organization_id=hcasa1.org_id and
hcasa1.cust_account_id=hca1.cust_account_id and
hcasa1.status='A' and
hca1.status='A'
)
LOV
Operating Unit
:operating_unit=case when :display_level in ('All','Site Use') then haou.name else :operating_unit end
Display Level
x.ou,--site use
hps.party_site_number site_number,--site use
x.location,--site use
hz_format_pub.format_address (hps.location_id,null,null,' , ') address,--site use
ftt.territory_short_name country,--site use
xxen_util.meaning(x.site_use_code,'SITE_USE_CODE',222) site_use,--site use
hcpa2.auto_rec_min_receipt_amount min_receipt_amt_site_use,
hcpa2.overall_credit_limit credit_limit_site_use,
hcpa2.trx_credit_limit order_credit_limit_site_use,
hcpa2.min_statement_amount min_statement_amt_site_use,
hcpa2.min_dunning_amount min_dunning_amt_site_use,
hcpa2.min_dunning_invoice_amount min_dunning_inv_amt_site_use,
Display Level
x.ou,--site use
hps.party_site_number site_number,--site use
x.location,--site use
hz_format_pub.format_address (hps.location_id,null,null,' , ') address,--site use
ftt.territory_short_name country,--site use
xxen_util.meaning(x.site_use_code,'SITE_USE_CODE',222) site_use,--site use
hcpa2.auto_rec_min_receipt_amount min_receipt_amt_site_use,
hcpa2.overall_credit_limit credit_limit_site_use,
hcpa2.trx_credit_limit order_credit_limit_site_use,
hcpa2.min_statement_amount min_statement_amt_site_use,
hcpa2.min_dunning_amount min_dunning_amt_site_use,
hcpa2.min_dunning_invoice_amount min_dunning_inv_amt_site_use,
Display Level
hcpa0.auto_rec_min_receipt_amount min_receipt_amt_party,
hcpa0.overall_credit_limit credit_limit_party,
hcpa0.trx_credit_limit order_credit_limit_party,
hcpa0.min_statement_amount min_statement_amt_party,
hcpa0.min_dunning_amount min_dunning_amt_party,
hcpa0.min_dunning_invoice_amount min_dunning_inv_amt_party,
Display Level
hcpa0.auto_rec_min_receipt_amount min_receipt_amt_party,
hcpa0.overall_credit_limit credit_limit_party,
hcpa0.trx_credit_limit order_credit_limit_party,
hcpa0.min_statement_amount min_statement_amt_party,
hcpa0.min_dunning_amount min_dunning_amt_party,
hcpa0.min_dunning_invoice_amount min_dunning_inv_amt_party,
Display Level
x.account_number,--account
hcpa1.auto_rec_min_receipt_amount min_receipt_amt_account,
hcpa1.overall_credit_limit credit_limit_account,
hcpa1.trx_credit_limit order_credit_limit_account,
hcpa1.min_statement_amount min_statement_amt_account,
hcpa1.min_dunning_amount min_dunning_amt_account,
hcpa1.min_dunning_invoice_amount min_dunning_inv_amt_account,
Display Level
:display_level=:display_level
LOV