You are viewing content for 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


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_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

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_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.


© Incorta, Inc. All Rights Reserved.