Data Applications → Analyze Oracle EBS Accounts Payable

About the Oracle EBS Accounts Payable Data Application

The Oracle E-Business Suite (EBS) Accounts Payable (AP) module administers payment for products or services. The Incorta Direct Data Platform™ data application for the Accounts Payable module provides prebuilt schemas, dashboards, and insights to help you manage cash, optimize payment timing for maximum benefit, and even help to identify problematic vendors.

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

Are payments to key suppliers or supplier groups overdue?
What is the discount lost?
What are the AP invoice holds by buyer, and how does that relate to the purchase orders (POs)?
What are the top invoices by discount amount offered that are on hold?
Do we pay suppliers too early, too late, or on time?
How does payment timing impact our working capital?
How efficiently do we process payables?
What percentage of the invoices we receive are manual?
How long does it take us to process an invoice?
Which cost centers generate the top expenses? Which organization and/or person is the top spender?

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

Note

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

Accounts Payable Data Application Components

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

Physical Schemas

EBS_AP
EBS common schemas:
- EBS_ITEM_COMMON
- EBS_CAL_COMMON
- EBS_FND_COMMON
- EBS_HR_COMMON
- EBS_FIN_COMMON
- EBS_PARTY_COMMON

Business Schemas

PayablesHoldsAndDiscounts: Provides visibility into invoices and payments that are on hold so that payments to key suppliers or supplier groups do not become overdue and cause supply chain disruptions
PayablesTransactions: Provides a detailed level business view to analyze payables transaction activities during a period of time by supplier, organization, item and other related dimensions
PurchaseOrder: Provides an operational view of the purchase orders to help manage the overall procurement process and make sure purchase orders are processed in timely manner
SupplierPaymentsOverview: Provides a high level overview of delivery and quality performance related metrics for suppliers, including payments and hold information
SupplierList: Provides details on suppliers

Dashboard Folder

Payables

Dashboards and Insights

Payables Overview Dashboard
Holds and Discount Dashboard
Payables Effectiveness Dashboard
Procurement and Payables Details Dashboard
Supplier Payments Dashboard
Employee Expenses Dashboard
Supplier Performance Dashboard
AP Holds with PO Details Dashboard
AP PO Details Dashboard

Payables Overview Dashboard

The Payables Overview Dashboard provides insight into invoices, holds, and payments, primarily as they pertain to suppliers. This dashboard consists of the following tabs: Overview, Holds, and Payments.

Overview Tab

Insight	Description
Invoices on Hold	This KPI insight shows the number of invoices on hold
Open Holds	This KPI insight shows the number of open holds
# of Buyers	This KPI insight shows the number of buyers
Top 10 Suppliers by Billed Amount	This stacked column insight shows the top ten suppliers by year and amount billed
Top 10 Suppliers by Outstanding Payment	This sunburst insight shows the top ten suppliers by amount of outstanding payment
Top 20 Suppliers by Discount Lost	This aggregated table insight ranks the top twenty suppliers by the amount of discount lost
Holds by Type	This donut insight shows the holds by type as a percent of total holds
Holds by Supplier	This aggregated table insight shows the number invoices on hold and open holds by supplier
Details	This aggregated table insight shows the invoice details, including: Year, Invoice Number, Invoice Date, Supplier Name, Supplier Site Code, Source, Invoice Received Date, Invoice Currency Code, and Invoice Description

Example: Overview Tab (Payables Overview Dashboard)

Holds Tab

Insight	Description
Holds	This KPI insight shows the number of open holds
Invoice Amount on Hold	This KPI insight shows the dollar amount of invoices on hold
Avg Holds per Invoice	This KPI insight shows the average number of holds per invoice
Discount Lost %	This KPI insight shows the discount percent lost
Hold Creation Trend	This line insight shows the trend of holds created by month end date
Hold Details	This listing table insight shows the open hold details, including: Hold ID, Hold Invoice ID, Invoice Number, Supplier, Year, Purchase Order Number, Buyer, Item, Invoice Date, and Term

Example: Holds Tab (Payables Overview Dashboard)

Payments Tab

Insight	Description
Outstanding Payments	This KPI insight shows the dollar amount of outstanding payments
Early Payment Amount	This KPI insight shows the dollar amount of early payments
% Overdue	This KPI insight shows the percent of supplier payments that are overdue
% Due	This KPI insight shows the percent of supplier payments that are due at a future date
Top 10 Suppliers by Payment	This column insight shows the top ten suppliers by payment amount in dollars
Payment Details	This aggregated table insight shows the payment details, including: year, supplier, supplier site, invoice number, payment status flag, payment number, payment priority, due date, aging bucket due, and amount remaining

Example: Payments Tab (Payables Overview Dashboard)

Holds and Discount Dashboard

Insight	Description
Holds	This KPI insight shows the number of open holds
Invoice Amount on Hold	This KPI insight shows the dollar amount of invoices on hold
Avg Holds per Invoice	This KPI insight shows the average number of holds per invoice
Discount Lost %	This KPI insight shows the percent of discount lost
Top Holds Reason	This tag cloud insight shows the top hold reasons
Holds by Type	This heatmap insight shows holds by type
Holds Aging	This column insight shows the number of holds in each aging bucket
Supplier Holds	This bubble insight shows the number of holds and average days on hold by supplier
Hold Creation Trend	This line insight shows the trend of hold creation by month end date
Holds by Supplier	This aggregated table insight shows the number of holds by supplier
Holds by Buyer	This aggregated table insight shows the number of holds by buyer
Holds and Discount Lost by Invoices	This aggregated table shows the number of holds and discount lost amount by invoice
Hold Details	This listing table insight shows the open hold details, including: invoice number, purchase order number, buyer, supplier, item, invoice date, term, hold status, hold date, hold type, and invoice hold amount
Discount Lost Details	This listing table insight shows the discount lost details, including: discount lost, supplier name, discount date, invoice number, invoice date, term, payment creation date

Example: Holds & Discount Dashboard

Payables Effectiveness Dashboard

Insight	Description
Invoices	This KPI insight shows the number of invoices
% Manual	This KPI insight shows the percent of manually entered invoices
Avg Days to Pay an Invoice	This KPI insight shows the average number of days to pay an invoice
Discount Lost %	This KPI insight shows the percent of discount lost
Trend of Invoice Received Days Aging	This stacked column insight shows the trend of number of invoices received and days aging by quarter
Invoice Payment Cycle Days	This stacked column insight shows the trend of number of invoices by payment cycle by quarter
Manual Invoices Trend	This column insight shows the trend of manually entered invoices by quarter
Invoices Processed by Source	This donut insight shows the invoices processed by source as a percent of total invoices
Holds Aging	This column insight shows the number of holds in each aging bucket
Top 10 Suppliers by Discount Lost	This aggregated table insight shows the top ten suppliers by discount lost
Holds by User and Age Bucket	This pivot table shows the number of holds by user and aging bucket

Example: Payables Effectiveness Dashboard

Procurement and Payables Details Dashboard

Insight	Description
Supplier Invoices	This pie insight shows invoices by supplier as a percent of total invoices
Hold Type Counts	This funnel insight shows the number of holds by hold type
Procurement Hold Details	This listing table insight shows the procurement hold details, including: invoice number, invoice date, supplier name, purchase order number, release number, hold type, buyer full name, release buyer, hold invoice number, term, hold status, need by date, promised date, hold date, ship to organization, shipment number, shipment location code, item, UOM, quantity ordered, quantity billed, quantity cancelled, quantity received, and invoice hold amount

Example: Procurement & Payables Dashboard

Supplier Payments Dashboard

Insight	Description
Outstanding Payments	This KPI insight shows the dollar amount of outstanding payments
Early Payment Amount	This KPI insight shows the dollar amount of early payments
% Overdue	This KPI insight shows the percent of supplier payments that are overdue
% Due	This KPI insight shows the percent of supplier payments that are due at a future date
Payment Due Aging	This column insight shows the amount of payment remaining in each aging bucket
Payment Overdue Aging	This pie chart shows the amount of payment remaining in each overdue aging bucket
Top 10 Suppliers by Payment	This column insight shows the top ten suppliers by payment amount
Top 10 Suppliers by Avg Payment Early Days	This aggregated table shows the top ten suppliers by average days the payment was early
Payment Details	This aggregated table insight shows the payment details, including: invoice number, supplier site, supplier, payment number, payment priority, payment status flag, invoice date, due date, payment type, payment creation date, check date, check number, aging bucket due, and payment amount

Example: Supplier Payments Dashboard

Employee Expenses Dashboard

Insight	Description
Avg Expense Filing Days	This KPI insight shows the average number of days to file the expense report
Avg Expense Reimbursement Days	This KPI insight shows the average number of days to reimburse the expenses
Credit Card Trxns %	This KPI insight shows the credit card transaction percent of total expense report transactions
Top 100 Expense Reasons	This tag cloud insight shows the top hundred expense reasons
Expense Filing Cycle Time	This stacked column insight shows the number of expense reports by filing cycle time bucket by quarter
Expense Reimbursement Cycle Time	This stacked column insight shows the number of expense reports by reimbursement cycle time bucket by quarter
Credit Card Trxns %	This percent column insight shows the credit card transaction percent of total expense report transactions by quarter
Top 10 Cost Centers by Expenses	This treemap insight shows the top ten expense amounts by cost centers and line category
Expenses by Type	This treemap shows expense amounts by line category
Top Violations by Type	This packed bubble insight shows the top violations by audit reason
Top Violators	This aggregated table insight shows the people with the top number of violating expense reports, and the percent of total violating expense reports
Expense Report Details	This aggregated table insight shows the expense report details, including: report number, report description, cost center description, natural account description, and total amount

Example: Employee Expenses Dashboard

Supplier Performance Dashboard

Insight	Description
Early Payments	This KPI insight shows the dollar amount of early payments
Total Outstanding	This KPI insight shows the dollar amount of outstanding payments
Overdue	This KPI insight shows the dollar amount of overdue payments
% Overdue	This KPI insight shows the percent of supplier payments that are overdue
Discount Offered	This KPI insight shows the dollar amount of payment discounts offered
Discount Lost	This KPI insight shows the dollar amount of payment discounts lost
Spend by Top Suppliers	This aggregated table insight shows the spend by top suppliers, including total payment amount, payment percent of total payments, and payment amount with invoice
Invoice Leakage	This dual axis insight shows the payment amounts with a purchase order and invoice leakage by supplier
Outstanding Amount by Supplier	This stacked bar insight shows the payment amount remaining by supplier
Supplier Payment Trend	This dual x-axis insight shows a trend of the total payment amounts made by suppliers by month
Payment Details	This aggregated table insight shows the payment details, including: supplier name, period name, operating unit, invoice date, invoice number, invoice amount, payment amount, discount offered, discount taken, and amount remaining

Example: Supplier Performance Dashboard

AP Holds with PO Details Dashboard

Insight	Description
Org Hold Amount	This donut invoice shows the total hold amount of an organization as a percent of total holds
Supplier Hold Amount	This treemap insight shows the total hold amount by supplier
Invoice Details	This aggregated table insight shows the expense report details, including: invoice organization name, invoice organization ID, supplier, supplier site name, invoice number, invoice line number, invoice date, approval status, invoice type, PO currency, invoice currency, invoice line type, invoice UOM, unit price, requestor, PO number, PO line number, line type description, PO unit price, PO line UOM, created by, quantity ordered, quantity billed, quantity received, invoice amount, invoice amount (USD), hold type, hold date, hold reason, hold status, and hold ID

Example: AP Holds with PO Details Dashboard

AP PO Details Dashboard

Insight	Description
OU Summary	This pie insight shows the invoice amount by operating unit as a percent of total invoice amount
Supplier Summary	This treemap insight shows the invoice amount by supplier
Details	This aggregated table insight shows the PO details, including: supplier name, operating unit, invoice number, line number, PO number, month year, and amount

Example: AP PO Details Dashboard

View the Accounts Payable Data Application Schema Diagram with the Schema Diagram Viewer

Here are the steps to view the Accounts Payable data application schema diagram using the Schema Diagram Viewer:

Sign in to the Incorta Direct Data Platform.
In the Navigation bar, select Schema.
In the list of schemas, select the EBS_AP schema.
In the Schema Designer, in the Action bar, select Diagram.

`EBS_AP` Schema Diagram

Here is the EBS_AP schema diagram. This subset of the Accounts Payable data application schema diagram represents the relationships between the tables in the EBS_AP schema and is provided for brevity.

Additional Information

Aging Buckets

By default, the aging buckets are set up as 0-10, 11-20, 21-30, and >30 days. To change the aging bucket duration or add more buckets, the following formula columns for the aging buckets need to be modified in the AP_PAYMENT_SCHEDULES_ALL table in the EBS_AP 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_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <0,'OVERDUE', IF ( daysBetween (EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE , $currentDate ) <=10, '0-10', IF(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=20, '10-20', IF(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=30,'20-30',' >30'))))

The following is an example formula for an additional 31 to 60-day bucket:

if(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <0,'OVERDUE', IF ( daysBetween (EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE , $currentDate ) <=10, '0-10', IF(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=20, '10-20', IF(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=30,'20-30',IF(daysBetween(EBS_AP.AP_PAYMENT_SCHEDULES_ALL.DUE_DATE, $currentDate) <=600,'30-60',' >60')))))

Accounts Payable 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)