Data Applications → Analyze Oracle EBS Accounts Receivable

About the Oracle EBS Accounts Receivable Data Application

The Oracle E-Business Suite (EBS) Accounts Receivable (AR) module streamlines invoicing, receipt, and customer deduction processing while improving cash flow, optimizing customer relationships, and providing strategic information. The Incorta Direct Data Platform™ data application for the Accounts Receivable module provides prebuilt schemas, dashboards, and insights to help you positively, materially impact your company’s cash flow and, ultimately, the bottom line.

Here are some of the questions answered by the Oracle EBS Accounts Receivable data application:

How is my performance across core AR KPIs?
What does my aging trend look like across segment, region, and collection agent?
What is the exposure related to my top 20 customers based on sales dollars, sales volume, or receivables balance?
Which customers have overdue balances and pending orders?

You can further customize and add on to the insights in the Incorta Accounts Receivable data application to support your specific business needs.

Note

The Oracle EBS Accounts Receivable data application is available in the On-Premises and Cloud Versions of Incorta.

Accounts Receivable Data Application Components

Here are the Incorta prebuilt Accounts Receivable data application physical schemas, business schemas, dashboard folders, dashboards, and insights:

Physical Schemas

EBS_AR
EBS_AR_SNP
EBS_AR_MV
EBS common schemas:
- EBS_ITEM_COMMON
- EBS_CAL_COMMON
- EBS_FND_COMMON
- EBS_HR_COMMON
- EBS_FIN_COMMON
- EBS_PARTY_COMMON

Business Schemas

ARCashReceipts: Provides customer and AR cash receipt details
BillingAndTax: Provides AR billing and tax information, including customer, invoice, payment, and tax details
CollectionsManager: Provides a complete picture of the key collections related indicators, including Total Outstanding Balance, % Overdue, and % Due to ensure that overdue balances are on target and customers are paying in line with their terms

Dashboard Folders

Receivables
- Drills
Revenue & Billing

Dashboards and Insights

Receivables Folder
Revenue & Billing Folder
- Revenue Summary Dashboard
- Revenue Billing and Tax Details Dashboard

AR Summary Dashboard

Insight	Description
Total Revenue Amount	This KPI insight shows the company total revenue amount for the year
Outstanding Receivable Amount	This KPI insight shows the company outstanding receivable amount for the year
% Overdue	This KPI insight shows the percent of supplier payments that are overdue for the year
% Due but Current	This KPI insight shows the percent of supplier payments that are due at a future date
# of Invoices	This KPI insight shows the number of invoices
Current Day Sales Outstanding (DSO)	This KPI insight shows the average number of days it takes to receive payment for a sale
Revenue, Receivables & DSO	This dual axis insight shows receivables, billings, and DSO by month as lines, and the receivables to billings ratio by month as a bar
Invoices by Customer Category	This donut insight shows the number of invoices by customer customer category (industry)
Cumulative Revenue Growth %	This dual axis insight shows the cumulative revenue and growth percent by month as lines, and revenue amount by month as a bar
Top 10 Customers By Revenue	This dual axis insight shows the invoice count by customer as a line, and the revenue amount by customer as a bar for the top ten customers
Customers contributing to 80% of Outstanding Receivables	This aggregated table insight shows a list of customers that contribute to eighty percent of outstanding receivables, and the percent of payments remaining for each customer
Overdue Invoices by Customer and Aging Bucket (With Drill)	This pivot table insight shows a list of customers with overdue invoices, including the original invoice amount and the remaining amount. Select a customer name to drill down to the AR overdue invoices dashboard for the customer.
Invoice Details	This listing table shows the invoice details, including: Invoice number, country, customer name, transaction type, transaction type name, invoice date, goods issue (GI) date, complete flag, account, functional currency, transactional currency, unit price, quantity, transactional amount, transactional tax, functional amount, functional tax, global amount, global tax, and global total with tax

Select a Customer Name from the Overdue Invoices by Customer and Aging Bucket (With Drill) insight to open the Go To menu. From this menu, you are able to select the AR Overdue Invoices dashboard. The AR Overdue Invoices dashboard opens and inherits the runtime filters from the Overdue Invoices by Customer and Aging Bucket (With Drill) insight.

AR Overdue Invoices Dashboard

Insight	Description
Total Revenue Amount	This KPI insight shows the company total revenue amount for the year
Outstanding Receivable Amount	This KPI insight shows the company outstanding receivable amount for the year
% Overdue	This KPI insight shows the percent of supplier payments that are overdue for the year
% Due but Current	This KPI insight shows the percent of supplier payments that are due at a future date
# of Invoices	This KPI insight shows the number of invoices
Outstanding Receivables by Business Unit	This donut insight shows the outstanding receivables amount by business unit, as a percent of total outstanding receivables
Overdue Invoices by Aging Bucket	This column insight shows the overdue invoice amount by aging bucket
Overdue Invoice Details	This aggregated table insight shows the overdue invoice details, including: invoice number, account name, invoice date, due date, age bucket overdue, amount due original, amount due remaining, amount applied, and amount credited

Example: AR Summary Dashboard

AR Aging Details Dashboard

Insight	Description
Amount Due Remaining	This KPI insight shows the remaining amount of revenue due
% Overdue	This KPI insight shows the percent of payments that are overdue
% Due	This KPI insight shows the percent of payments that are due at a future date
# of Collectors	This KPI insight shows the number of collectors
Balance by OU	This donut insight shows the balance due remaining by operating unit, as a percent of the total balance due remaining
Balance by Region	This column insight shows the balance due remaining by region
AR Overdue Aging	This stacked column insight shows the ten customers with the highest accounts receivable amounts overdue by aging bucket
AR Aging by Bucket	This treemap insight shows the accounts receivable amounts overdue by aging bucket
AR Aging Trend	This line insight shows the trend of the average number of days for an invoice to be paid by fiscal period
AR Overdue Invoice Count	This pie insight shows the overdue invoice count by operating unit, as a percent of total overdue invoices
Who are the Top 10 Customers by Receivables?	This packed bubble insight shows the ten customers with the highest accounts receivable amounts
What are the Top 10 Receivables by Collectors?	This packed bubble insight shows the top ten collectors responsible for the highest receivable amounts
AR Aging Details	This listing insight shows the accounts receivable aging details, including: operating unit, sales invoice number, AR EBS transaction subtype, payment terms, as of date, transaction date, GI date, due date, days payment overdue, account number - bill to account, account number - ship to account, company name - bill to account, company name - ship to account, company name - sold to account, entered currency, functional currency, amount due original, amount due remaining, amount current, AR overdue 1-30, AR overdue 31-60, AR overdue 61-90, AR overdue 91-150, AR overdue 151-210, AR overdue 31+, AR overdue 61+, AR overdue 91+, AR overdue 151+, and AR overdue 210+

Example: AR Aging Details Dashboard

Invoice by Customer Dashboard

Insight	Description
Invoice Amount By Bill To Region	This donut insight shows the invoice amount by bill to region, as a percent of total invoice amounts
Invoice Count By Bill To Region	This column insight shows the invoice count by bill to region
Operating Unit Amounts	This aggregated table insight shows Amount (Entered), Amount (Functional), Amount (AR USD), and Amount (Revenue USD) by operating unit
GL Periods	This aggregated table insight shows Amount (AR USD), Balance (AR USD), and Transaction Count by GL period
Details	This listing table insight shows customer invoice details, including: operating unit, sales invoice number, region - bill to location, region - ship to location, company name - bill to account, account number - bill to account, hq country name - bill to SF account, country name - bill to location, due date, company name - sold to account, country name - ship to location, HQ country name - ship to account, payment terms, transaction type, invoice transaction date, GI date, GL period, aging bucket overdue, entered currency, functional currency, amount due original (entered), amount due remaining (entered), amount due original (functional), and amount due remaining (functional)

Example: Invoice by Customer Dashboard

Adjustment Register Dashboard

Insight	Description
Adjustment Debit USD by OU	This bar insight shows the adjustment debit in US dollars by operating unit
Adjustment Debit USD by Trx Type	This bar insight shows the adjustment debit in US dollars by transaction type
Adjustment Debit Details	This listing table insight shows the adjustment debit details, including: operating unit, payment schedule ID, invoice number, invoice currency code, currency - functional, AR EBS transaction subtype, company name - bill to account, country name - bill to location, region - bill to location, adjustment GI date, adjustment apply date, and adjustment amount (entered)

Example: Adjustment Register Dashboard

Cash Receipt Register Dashboard

Insight	Description
Receipts by Customers	This column insight shows the cash receipt amount by customer
Top Invoices	This aggregated table insight shows the top invoices by cash receipt amount applied
Receipts	This listing table insights shows the cash receipt details, including: transaction number, receipt number, ledger name, cash receipt status, batch name, deposit date, customer number, customer name, receipt date, GI date, currency code, cash receipt entered amount, cash receipt functional amount, and amount applied

Example: Cash Receipt Register Dashboard

Revenue Summary Dashboard

Insight	Description
# of Parts	This KPI insight shows the number of product items the company sells
# of Customers	This KPI insight shows the number of customers the company sells to
Global Revenue without Tax (Mil)	This KPI insight shows the global revenue amount without tax
Revenue Performance by OU	This pie donut insight shows the revenue amount by operating unit as a percent of total revenue
Revenue by Country	This donut insight shows the revenue amount by country as a percentage of total revenue
Top Parts (Pareto)	This dual axis insight shows the cumulative revenue percent of total revenue for the top 30 product items as a line, and the total revenue amount by product item as a bar
Customers contributing to top 80%	This aggregated table insight shows a list of the top customers that contribute to eighty percent of total revenue, including the revenue amount for each customer and the rolling revenue percent of total
Revenue Performance by Item Category	This line insight shows the revenue amount trend by quarter for each product item category

Example: Revenue Summary Dashboard

Revenue Billing and Tax Details Dashboard

Insight	Description
# of Parts	This KPI insight shows the number of product items the company sells
# of Customers	This KPI insight shows the number of customers the company sells to
Global Revenue without Tax	This KPI insight shows the global revenue amount without tax
Revenue Performance	This line insight shows the revenue amount by quarter
Customer Performance	This stacked column insight shows the revenue amount by customer and year
Which Parts contribute to Top 80% of Revenue	This aggregated table insight shows a list of product items that contribute to eighty percent of total revenue, including the revenue amount for each product item and the rolling revenue percent of total
Which Customers contribute to Top 80% of Revenue	This aggregated table insight shows a list of the top customers that contribute to eighty percent of total revenue, including the revenue amount for each customer and the rolling revenue percent of total
Invoice Details	This listing table insight shows the invoice details, including: invoice number, line number, invoice description - summary, invoice year, country, customer number, customer name, item number, item description, transaction type, transaction type name, comments, transaction date, GI date, GL period, complete flag, account description, credit requested by, functional currency, transactional currency, global currency, unit price, quantity, transactional amount, transactional tax, functional amount, functional tax, global amount, global tax, and global total with tax

Example: Revenue - Billing and Tax Details Dashboard

Accounts Receivable Data Application Data Model

Additional Information

Currency Types

In the AR module, Incorta supports the aging metrics in four currencies. The currency types are added as a presentation variable, using the table EBS_AR_SNP.CurrencyTypes. The available currencies are:

Entered
Functional
AR Global
Revenue Global

In the CollectionsManager business schema, there are formulas that use the currency conversion lookup function to calculate the amounts in various currency types. To change these, modify the CurrencyTypes table SQL and the various metrics in the business schema and reports.

For example, Amount Due Original (Dynamic) has the following formula, which uses the presentation variable $CurrType in the AR Aging Details dashboard:

sum(case($CurrType = 'Entered',EBS_AR_SNP.AR_AGING.AMT, $CurrType = 'Functional', EBS_AR_SNP.AR_AGING.AMT_DUE_ORIG_FUNC, $CurrType = 'AR Global', CollectionsManager.Payments.AMT_DUE_ORIG_AR_GLBL ,$CurrType = 'Revenue Global', CollectionsManager.Payments.AMT_DUE_ORIG_REV_GLBL, 0.0))

Revenue Global Currency Rate

By default, the Revenue Global Currency Rate is calculated using the following logic, which you can update as required:

EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $EBS_FIN_COMMON.GL_DAILY_RATES.CONVERSION_TYPE = '1000'

Aging Buckets

By default, the aging buckets are set up as 1-30, 31-60, 61-90, 91-150, 151-210, and 210+ days. To change the aging bucket duration or add more buckets, you need to modify the following formula columns for the aging buckets in the AR_PAYMENT_SCHEDULES_ALL table in the EBS_AR schema:

AGE_BUCKET_DUE
AGE_BUCKET_DUE_NUM
AGE_BUCKET_OVERDUE
AGE_BUCKET_OVERDUE_NUM

The following formula is the default for AGE_BUCKET_DUE:

if(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate)<0,'OVERDUE', IF(daysBetween (EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate ) <=30, '1-30', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate)<=60, '31-60', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=90,'61-90', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=150,'91-150', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=210,'151-210',' 210+'))))))

The following is an example formula for an additional 211 to 270-day bucket:

if(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <0,'OVERDUE', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=30, '1-30', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=60, '31-60', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=90,'61-90', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=150,'91-150', IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=210,'151-210',IF(daysBetween(EBS_AR.AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=270,'211-270',' 270+')))))))

Accounts Receivable Data Security (Optional)

By default, row-level data security is turned off. To turn on row-level data security based on the Oracle EBS ORG_ID security profile, which controls access to operating unit data, update the ORG_IDS session variable with the following SQL:

SELECT distinct fpov.profile_option_value org_id
FROM   apps.hr_organization_units hou,
       apps.fnd_profile_options_vl fpo,
       apps.fnd_profile_option_values fpov,
       apps.fnd_responsibility_vl frv,
       apps.fnd_user_resp_groups furg,
       apps.fnd_user fu
WHERE  fpov.level_value = frv.responsibility_id
AND    fpo.profile_option_id = fpov.profile_option_id
AND    fpo.user_profile_option_name = 'MO: Operating Unit'
AND    fpov.profile_option_id = fpo.profile_option_id
AND    hou.organization_id = TO_NUMBER (fpov.profile_option_value)
AND    frv.responsibility_id = furg.responsibility_id
AND    furg.user_id = fu.user_id
AND    UPPER(fu.user_name)=UPPER($USER)

To create a table security filter to secure data based on user type:

Create an EBS user in Incorta.
Edit a table in the schema you want to secure.
Add an expression in the Security Filter section and save the table.

For example, to secure revenue data in the table, EBS_ARRA_CUST_TRX_LINES_ALL, and in the AR dashboards, edit the table and add the following expression in the Security Filter section:

or($user='admin',inlist(EBS_AR.RA_CUSTOMER_TRX_ALL.ORG_ID,$ORG_IDS))

The expression allows admin users to see all data. Non-admin users cannot see revenue data because revenue data is filtered out using the organization IDs that control access.