Credit Limit

This forum is to discuss different features/issues of Oracle Financials modules ( GL - General Ledger, AP - Accounts Payable, AR - Accounts Receivable, FA - Fixed Assets & CM - Cash Management ).
Post Reply
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Credit Limit

Post by ahmadbilal »

I want to get the credit limit ,order limit and tolerance level of my OM(RA) customers but using following query Customer names repeated
any one <u><b>have any idea how to get credit limit</b></u>

select
(select unique (ra.customer_name)
from ra_customers ra
where ra.customer_id = os.customer_id) NAME,
cpa.currency_code currency_code,
os.organization_id ,
cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100) CRED_LIMIT,
cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100) ORD_LIMIT,
cp.credit_checking CRD_FLG,
cp.tolerance TOLR
from hz_customer_profiles cp,
hz_cust_profile_amts cpa,
oe_sold_to_orgs_v os
where cp.cust_account_id = os.customer_id
-- and os.organization_id = '204'
and cp.site_use_id IS NULL
and cp.cust_account_profile_id = cpa.cust_account_profile_id
order by os.customer_id
utsavwalia
Posts: 8
Joined: Mon Dec 11, 2006 7:38 am
Location: India

Post by utsavwalia »

HI,

I have tied the query you have written and it didn't give me any records with repeat customer names.

Wanna explain the problem in detail????

Regards.
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

NAME CURRENCY_CODE ORGANIZATION_ID CRED_LIMIT ORD_LIMIT CRD_FLG TOLR
World of Business ANY 1,000 5,100,000 1,020,000 Y 2
World of Business GBP 1,000 918,000,000 1,020,000 Y 2
World of Business USD 1,000 5,100,000 1,020,000 Y 2
World of Business FRF 1,000 1,020,000 510,000 Y 2
American Telephone and Telegraph EUR 1,001 9,050,518 4,525,259 N 10
American Telephone and Telegraph USD 1,001 11,000,000 5,500,000 N 10
Imaging Innovations, Inc. EUR 1,002 9,050,518 4,525,259 N 10
Imaging Innovations, Inc. GBP 1,002 5,500,000 2,750,000 N 10
Imaging Innovations, Inc. USD 1,002 11,000,000 5,500,000 N 10
United Parcel Service EUR 1,003 9,050,518 4,525,259 N 10
United Parcel Service USD 1,003 11,000,000 5,500,000 N 10
Hilman and Associates EUR 1,004 9,050,518 4,525,259 N 10
Hilman and Associates GBP 1,004 5,500,000 2,750,000 N 10
Hilman and Associates ITL 1,004 N 10
Hilman and Associates USD 1,004 11,000,000 5,500,000 N 10
AT&T Universal Card EUR 1,005 9,050,518 4,525,259 N 10
AT&T Universal Card SEK 1,005 81,114,000 40,557,000 N 10
AT&T Universal Card GBP 1,005 5,500,000 2,750,000 N 10
AT&T Universal Card USD 1,005 11,000,000 5,500,000 N 10
Computer Service and Rentals ANY 1,006 3,000,000 1,000,000 Y 0
Computer Service and Rentals EUR 1,006 300,000 10,000 Y 0
Computer Service and Rentals SEK 1,006 22,122,000 7,374,000 Y 0
Computer Service and Rentals CAD 1,006 3,913,200 1,304,400 Y 0
Computer Service and Rentals USD 1,006 3,000,000 1,000,000 Y 0
Alexander
Posts: 1
Joined: Sun May 14, 2023 10:52 am

Re: Credit Limit

Post by Alexander »

ahmadbilal wrote: Mon Dec 11, 2006 2:45 am I want to get the credit limit ,order limit and tolerance level of my OM(RA) customers but using following query Customer names repeated
any one <u><b>have any idea how to get credit limit</b></u>

select
(select unique (ra.customer_name)
from ra_customers ra
where ra.customer_id = os.customer_id) NAME,
cpa.currency_code currency_code,
os.organization_id ,
cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100) CRED_LIMIT,
cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100) ORD_LIMIT,
cp.credit_checking CRD_FLG,
cp.tolerance TOLR
from hz_customer_profiles cp,
hz_cust_profile_amts cpa,
oe_sold_to_orgs_v os
where cp.cust_account_id = os.customer_id
-- and os.organization_id = '204'
and cp.site_use_id IS NULL
and cp.cust_account_profile_id = cpa.cust_account_profile_id
order by os.customer_id
If you're looking to retrieve the credit limit, order limit, and tolerance level of your OM(RA) customers using the provided query, I can help you out. It seems like the query you shared retrieves the necessary information, but you mentioned that customer names are repeated. To avoid that, you can modify the query to include a DISTINCT keyword.
Post Reply

Who is online

Users browsing this forum: Google Adsense [Bot], Semrush [Bot] and 3 guests