AR Customer Credit Limits

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Operating Unit, Party Name, Currency Code, Min Receipt Amt Party, Credit Limit Party, Order Credit Limit Party, Min Statement Amt Party, Min Dunning Amt Party, Min Dunning Inv Amt Party, Account Number ...
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'
)
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,
Operating Unit
:operating_unit=case when :display_level in ('All','Site Use') then haouv.name else :operating_unit end