Summary:
Can Available Credit Limit For A Customer be Performed in OTBI?
Content (required):
How can we check Open Credit Limit for a customer account in OTBI Report
Do we any way to get open credit limit value for a Customer Account in OTBI report?
Version (include the version you are using, if applicable):
21D
Code Snippet (add any code snippets that support your topic, if applicable):
I was given the queries below to get the data, but would rather get this through OTBI.
Below queries can be run to obtain this information:
Credit Limit of the Customer (set at account profile level)
===========================================================
SELECT Credit_Limit,Exchange_Rate_Type,Credit_Currency_Code,Credit_Hold
FROM hz_customer_profiles_f
WHERE cust_account_id = (select Cust_Account_Id from FUSION.Hz_Cust_Accounts where Account_Number='<account_number>')
AND (Trunc(SYSDATE) BETWEEN effective_start_date AND effective_end_date)
AND site_use_id is NULL
AND status = 'A';
Total Balance
==============
SELECT TOTAL_BALANCE
FROM ar_total_balance_view
WHERE cust_account_id = (SELECT Cust_Account_Id
FROM FUSION.Hz_Cust_Accounts
WHERE Account_Number='<account_number>')
Sum of the open credit authorizations
======================================
SELECT sum(CREDIT_AUTH_AMOUNT)
FROM FUSION.AR_CUST_CREDIT_AUTHORIZATION
WHERE AUTHORIZATION_STATUS = 'OPEN'
AND CUST_ACCOUNT_ID = (SELECT Cust_Account_Id
FROM FUSION.Hz_Cust_Accounts
WHERE Account_Number='<account_number>');
