Can Available Credit Limit For A Customer be Performed in OTBI?

Stephen Johnson-Oracle

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>');

Nathan CCC

Hi, If you have the correct application database physical sql on the tables and views then you are half way there. OTBI is part of your SaaS. OTBI is software and platform, the software bit (the "T" in OTBI if you like) is the package of pre-build content including the subject areas metadata repository database (RPD) and the analytics and reports, the platform (the OBI in OTBI) is the suite of products (dashboards, answers (blue), delivers (green), publisher (orange) etc) in Oracle Business Intelligence OBI 11.1.1.9 to build your custom content which includes publisher to build "data models" and "reports". So you can use OTBI to build a "report" with a "data model" using this physical SQL. If you want to see if you can get similar information also using OTBI to build an "analysis" from a subject area then go to the "Review Data Lineage Mapping" sheet in your help page to see if any of these physical columns are mapped to any presentation columns in any of the subject areas https://docs.oracle.com/en/cloud/saas/financials/21d/analyze-and-report.html. Maybe you can get the answers using an analysis in stead of a report and data model. An analysis comes out of the box with the data security built in so that is a bonus. In my opinion it gives a better user experience so I will always try to find a solution using an analysis before resort to data model/report.