Blueprints → Analyze Oracle EBS Accounts Payable

About the Oracle EBS Accounts Payable Blueprint

The Oracle E-Business Suite (EBS) Accounts Payable (AP) module administers payment for products or services. The Incorta Direct Data Platform™ Blueprint 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 Blueprint:

  • 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 blueprint to support your specific business needs.

Note

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

Accounts Payable Blueprint Components

Here are the Incorta prebuilt Accounts Payable Blueprint 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

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 Blueprint Schema Diagram with the Schema Diagram Viewer

Here are the steps to view the Accounts Payable Blueprint 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 Blueprint 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)

© Incorta, Inc. All Rights Reserved.