Blueprints → Analyze Oracle EBS Accounts Receivable
About the Oracle EBS Accounts Receivable Blueprint
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™ Blueprint 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 Blueprint:
- 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 Blueprint to support your specific business needs.
Note
The Oracle EBS Accounts Receivable blueprint is available in the On-Premises and Cloud Versions of Incorta.
Accounts Receivable Blueprint Components
Here are the Incorta prebuilt Accounts Receivable Blueprint physical schemas, business schemas, dashboard folders, dashboards, and insights:
Physical Schemas
EBS_AREBS_AR_SNPEBS_AR_MV-
EBS common schemas:
EBS_ITEM_COMMONEBS_CAL_COMMONEBS_FND_COMMONEBS_HR_COMMONEBS_FIN_COMMONEBS_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
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 Blueprint 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_DUEAGE_BUCKET_DUE_NUMAGE_BUCKET_OVERDUEAGE_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.