Customer Credit limit query in oracle apps

Customer Credit limit query in oracle apps

In this post , we will discuss about sql query to fetch the Customer Credit limit information's in oracle apps. Customer credit limit in oracle apps  is the maximum amount of credit that we can give to that customer after that system we not allow to create the AR invoices against that Customer. With this below sql query you will get the Customer Credit Limit with the Amount Remaining so far in the Oracle apps. Please find below the Customer Credit limit query in oracle apps.

 

 

 

Complete Customer Credit limit query in oracle apps

SELECT a.overall_credit_limit,

d.site_use_id,

haou.name ou_name,

c.customer_number,

c.customer_name,

g.party_site_number,

d.location,

SUM (e.amount_due_remaining) credit_balance

FROM HZ_CUST_PROFILE_AMTS a,

HZ_CUST_ACCOUNTS b,

ar_customers c,

hz_cust_site_uses_all d,

ar_payment_schedules_all e,

hz_cust_acct_sites_all f,

hz_party_sites g,



hr_all_organization_units haou
  WHERE overall_credit_limit IS NOT NULL

AND a.cust_account_id = b.cust_account_id

AND b.account_number = c.customer_number

AND a.site_use_id = d.site_use_id

AND c.customer_id = e.customer_id

AND d.site_use_id = e.customer_site_use_id

and c.customer_number = :p_customer_number

AND d.cust_acct_site_id = f.cust_acct_site_id

AND g.party_site_id = f.party_site_id

AND a.overall_credit_limit is not null

and a.overall_credit_limit not in (0,1)

AND e.org_id =:p_org_id

AND haou.organization_id=e.org_id

GROUP BY c.customer_name,

c.customer_number,

d.site_use_id,

d.location,

a.overall_credit_limit,

g.party_site_number,

haou.name

ORDER BY haou.name, CUSTOMER_NAME