AR Customer Credit Limits

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
Master data report for customer setup audit of credit amount limits and GL accounts for customer credit management and dunning notices.
select
x.operating_unit,
x.party_name,
x.currency_code,
&column_party
&column_account
&column_site_use
x.party_number
from
(
select
haouv.name operating_unit,
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_vl haouv,
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=haouv.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.operating_unit,
x.currency_code
Parameter Name SQL text Validation
Account Number
hca.account_number=:account_number
LOV
Operating Unit
hp.party_id in (
select
hca1.party_id
from
hr_all_organization_units_vl haouv1,
hz_cust_acct_sites_all hcasa1,
hz_cust_accounts hca1
where
haouv1.name=:operating_unit and
haouv1.organization_id=hcasa1.org_id and
hcasa1.cust_account_id=hca1.cust_account_id and
hcasa1.status='A' and
hca1.status='A'
) and
:operating_unit=case when :display_level in ('All','Site Use') then haouv.name else :operating_unit end
LOV
Customer Name
upper(hp.party_name) like upper(:customer_name)
LOV
Display Level
:display_level=:display_level
LOV
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
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.operating_unit,--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.operating_unit,--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,