Blueprints → Analyze Oracle EBS General Ledger

About the Oracle EBS General Ledger Blueprint

The Oracle E-Business Suite (EBS) General Ledger (GL) module is a comprehensive financial management solution that provides highly automated financial processing, effective management control, and real-time visibility to financial results. The Incorta Direct Data Platform™ Blueprint for the General Ledger module provides prebuilt schemas, dashboards, and insights to help you analyze core financial statements and the underlying transactions. The GL Journals and Subledger Accounting (SLA) analytics support:

  • Trial Balance and Journal reporting with drill down to Accounts Payable (AP), Accounts Receivable (AR), Inventory (INV), and Fixed Asset (FA) subledgers.
  • Sample financial statements, including the Balance Sheet, Cash Flow, and Income Statement using the EBS Financial Statement Generator (FSG) hierarchy.
  • GL to SLA reconciliation for the AP, AP, INV, and FA subledgers.

Here are some of the questions answered by the Oracle EBS General Ledger Blueprint:

  • What are the driving factors behind the operating cycle, and how did they trend over the last year?
  • How does the operating cycle compare with my competitors and the industry average?
  • How does cash flow compare to the same period last year?
  • Which factors impact cash flow?
  • Have the yearly revenue projections been met?
  • Which customer or product categories came in below expectations and by how much?

You can further customize and add on to the insights in the Incorta General Ledger blueprint to support your specific business needs.

Note

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

General Ledger Blueprint Components

Here are the Incorta prebuilt General Ledger Blueprint physical schemas, business schemas, dashboard folders, dashboards, and insights:

Physical Schemas

Required Schemas

  • EBS_GL
  • EBS common schemas:

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON

Optional Schemas

  • EBS_XLA
  • EBS_XLA_MV
  • EBS_FSG
Note

Use EBS_GL and the EBS common schemas for GL reporting. Use the EBS_XLA and EBS_XLA_MV schemas to drill into Subledger details. Use the EBS_FSG schema to view the Financial Statement dashboards.

Business Schemas

  • GeneralLedger: Provides a complete picture of the key GL and SLA related indicators
  • ReconciliationMetrics: Provides reconciliation details for Accounts Receivable, Accounts Payable, Inventory, and Fixed Assets

Dashboard Folders

  • GL

    • Trial Balance and Journals

      • Drill Reports
    • Reconciliation with Sub-Ledgers

      • Drill Reports
    • Financial Statements

Dashboards and Insights

GL Summary Dashboard

Insight Description
Legal Entities This KPI insight shows the number of legal entities in the period you select
Accounts This KPI insight shows the number of accounts in the period you select
Total Journals This KPI insight shows the total number of journals in the period you select
UnPosted% This KPI insight shows the percent of unposted journal entries
Journal Status by Batch This aggregated table insight shows the total number of journal entries and the percent unposted by batch
Journal Status by Source This aggregated table insight shows the total number of journal entries, the number unposted, and the percent unposted by journal source
Top 10 LE/Accounts by Unposted Journals This column insight shows the top ten legal entities (LEs) or accounts with the highest number of unposted journal entries
Reconciliation Summary This aggregated table insight shows the net variance between the GL net amount and the experience level agreement (XLA) net amount for each legal entity and account
Payment Details This aggregated table insight shows the header details, including: journal header ID, journal line number, journal name, GL period, fiscal year, batch name, journal category, journal source, natural account code, natural account, entered currency, functional currency, and net amount

Example: GL Summary Dashboard

GL Journals to AR Dashboard

Insight Description
Journal Amount by Entity This column insight shows the receivables functional amount (debit) for each legal entity
Journal Amount by Batch This treemap insight shows the receivables functional amount (debit) for each batch
Journal Details This aggregated table insight shows the journal details for receivables, including: journal header ID, journal line number, journal name, GL period, fiscal year, batch name, journal category, journal source, natural account code, natural account, entered currency, functional currency, and net amount

Select the Journal Header ID from the Journal Details insight to drill down to open the Go To menu. From this menu, you are able to select the AR Drill dashboard. The AR Drill dashboard opens and inherits the runtime filters from the Journal Details insight.

AR Drill Dashboard

Insight Description
Journal Line Count This KPI insight shows the number of AR journal lines associated with the journal you select
Entered Dr This KPI insight shows the entered debit amount for the AR journal you select
Entered Cr This KPI insight shows the entered credit amount for the AR journal you select
Accounted Dr This KPI insight shows the accounted debit amount for the AR journal you select
Accounted Cr This KPI insight shows the accounted credit amount for the AR journal you select
Invoice Details This aggregated table insight shows the invoice details for the AR journal you select, including: journal header ID, journal description, journal line number, journal category, customer number, customer name, AR EBS transaction subtype, invoice number, invoice transaction date, GL period, accounted amount, GL distribution amount, invoice amount, extended amount, and extended accounted amount
Receipts This aggregated table insight shows the receipt details for the AR journal you select, including: journal header ID, journal description, journal line number, journal category, journal source, cash receipt status, receipt number, and cash receipt amount

Example: GL Journals to AR Dashboard

GL Journals to AP Dashboard

Insight Description
Journal Amount by Cost Center (Top 20) This bar insight shows the top twenty cost centers with the highest net payables amount
Journal Amount by Account This donut insight shows net payables amount by account as a percent of total net payables amount
Journal Details This aggregated table insight shows the journal details for payables, including: journal header ID, journal line number, GL period, GL account segment, natural account code, journal name, batch name, journal category, journal source, natural account, entered currency, functional currency, net amount, accounted debit amount, accounted credit amount, entered debit amount, and entered credit amount

Select the Journal Header ID from the Journal Details insight to drill down to open the Go To menu. From this menu, you are able to select the AP Drill dashboard. The AP Drill dashboard opens and inherits the runtime filters from the Journal Details insight.

AP Drill Dashboard

Insight Description
Invoice Details (Drill to PO) This aggregated table insight shows the invoice details for the AP journal you select, including: invoice number, invoice date, line number, supplier, GL period, journal header ID, journal description, journal line number, journal category, automatic entry (AE) header ID, and distribution amount
Payment Details This listing table insight shows the payment details for the AP journal you select, including: invoice number, journal header ID, journal line number, journal description, journal category, GL period, account description, account, accounting date, check number, check date, checkrun name, check amount, and net amount

Select the Invoice Number from the Invoice Details insight to drill down to open the Go To menu. From this menu, you are able to select the AP PO Details dashboard. The AR Drill dashboard opens and inherits the runtime filters from the Invoice Details insight.

AP PO Details Dashboard

Insight Description
Supplier Summary This aggregated table insight shows the supplier summary for the AP invoice you select, including: supplier name, invoice amount, and payment amount
AP/PO Details This listing table insight shows the AP/Purchase Order (PO) details for the AP invoice you select, including: invoice number, period name, PO number, supplier, supplier site, requisition number, requisition status, and requisition line amount

Example: GL Journals to AP Dashboard

GL Journals with Hierarchy Dashboard

Insight Description
Journal Amount by Account This donut insight shows net amount by account as a percent of total amount
Balances by Cost Center Hierarchy This aggregated table insight shows the accounted debit amount and accounted credit amount by cost center hierarchy and cost center code
Balances by Account Hierarchy This aggregated table insight shows the accounted debit amount and accounted credit amount by account hierarchy and account description
Balances by Flattened Account Hierarchy This aggregated table insight shows the accounted debit amount and accounted credit amount for each account level code and description
Journal Details This listing table insight shows the journal details, including: journal header ID, journal line number, ledger name, GL period, account, account description, journal name, batch name, journal category, journal source, natural account, entered currency, functional currency, net amount, accounted debit amount, accounted credit amount, entered debit amount, and entered credit amount

Example: GL Journals with Hierarchy Dashboard

GL Journals to Inventory Dashboard

Insight Description
Journal Amount by Cost Center (Top 20) This bar insight shows the top twenty cost centers with the highest net inventory amount
Journal Amount by Account This donut insight shows net inventory amount by account as a percent of total net inventory amount
Journal Details This aggregated table insight shows the journal details for payables, including: journal header ID, journal line number, GL period, GL account segment, account segment value, journal name, batch name, journal category, journal source, natural account, entered currency, functional currency, net amount, accounted debit amount, accounted credit amount, entered debit amount, and entered credit amount

Select the Journal Header ID from the Journal Details insight to drill down to open the Go To menu. From this menu, you are able to select the INV Drill dashboard. The INV Drill dashboard opens and inherits the runtime filters from the Journal Details insight.

INV Drill Dashboard

Insight Description
Journal Line Count This KPI insight shows the number of journal lines associated with the INV journal you select
Entered Dr This KPI insight shows the entered debit amount for the INV journal you select
Entered Cr This KPI insight shows the entered credit amount for the INV journal you select
Accounted Dr This KPI insight shows the accounted debit amount for the INV journal you select
Accounted Cr This KPI insight shows the accounted credit amount for the INV journal you select
Transaction Details This aggregated table insight shows the depreciation details for the INV journal you select, including: journal header ID, journal line number, inventory organization, item, item description, accounting line type, transaction source, transaction type, transaction date, primary quantity, rate or amount, and actual cost

Example: Journals to Inventory (INV) Dashboard

GL Journals to Fixed Assets Depreciation Dashboard

Insight Description
Journal Amount by Cost Center (Top 20) This bar insight shows the top twenty cost centers with the highest net assets depreciation amount
Journal Amount by Account This donut insight shows net assets depreciation amount by account as a percent of total net assets depreciation amount
Journal Details This aggregated table insight shows the journal details for payables, including: journal header ID, journal line number, GL period, GL account segment, account segment value, journal name, batch name, journal category, journal source, natural account, entered currency, functional currency, net amount, accounted debit amount, accounted credit amount, entered debit amount, and entered credit amount

Select the Journal Header ID from the Journal Details insight to drill down to open the Go To menu. From this menu, you are able to select the FA Depreciation Drill dashboard. The FA Depreciation Drill dashboard opens and inherits the runtime filters from the Journal Details insight.

FA Depreciation Drill Dashboard

Insight Description
Journal Line Count This KPI insight shows the number of journal lines associated with the FA journal you select
Entered Dr This KPI insight shows the entered debit amount for the FA journal you select
Entered Cr This KPI insight shows the entered credit amount for the FA journal you select
Accounted Dr This KPI insight shows the accounted debit amount for the FA journal you select
Accounted Cr This KPI insight shows the accounted credit amount for the FA journal you select
Depreciation Details This aggregated table insight shows the depreciation details for the FA journal you select, including: journal header ID, journal line number, book type code, fiscal year, period counter, asset ID, asset description, model number, serial number, and depreciation amount

Example: GL Journals to Fixed Assets Depreciation Dashboard

Trial Balance to AP Dashboard

Insight Description
Trial Balance This aggregated table insight shows the trial balance payables details, including: entity, account segment, account, cost center, program, functional currency, currency, opening balance, period net debit, period net credit, net activity, and closing balance

Select the Account Segment from the Trial Balance insight to drill down to open the Go To menu. From this menu, you are able to select the GL Journals Details - AP dashboard. The GL Journals Details - AP dashboard opens and inherits the runtime filters from the Trial Balance insight.

GL Journals Details - AP

Insight Description
Journal Amount by Batch This aggregated table insight shows the journal functional amount by batch for the AP account segment you select
Journal Amount by Category This donut insight shows the accounted debit amount by category as a percent of total amount for the AP account segment you select
Journal Details This aggregated table insight shows the journal details for the AP account segment you select, including: journal ID, journal line number, journal name, period, journal source, journal category, batch, entity, account code, account, cost center, functional currency, and net amount

Example: Trial Balance to AP Dashboard

Trial Balance to AR Dashboard

Insight Description
Trial Balance This aggregated table insight shows the trial balance receivables details, including: entity, account, account description, cost center, program, functional currency, currency, opening balance, period net debit, period net credit, net activity, and closing balance

Select the Account Segment from the Trial Balance insight to drill down to open the Go To menu. From this menu, you are able to select the GL Journals Details - AR dashboard. The GL Journals Details - AR dashboard opens and inherits the runtime filters from the Trial Balance insight.

GL Journals Details - AR Dashboard

Insight Description
Journal Amount by Batch This aggregated table insight shows the journal functional amount by batch for the AR account segment you select
Journal Amount by Category This donut insight shows the accounted debit amount by category as a percent of total amount for the AR account segment you select
Journal Details This aggregated table insight shows the journal details for the AR account segment you select, including: journal ID, journal line number, journal name, period, journal source, journal category, batch, entity, account code, account, cost center, functional currency, and net amount

Example: Trial Balance to AR Dashboard

Trial Balance Dynamic Group By Dashboard

Insight Description
Trial Balance This aggregated table insight allows you to group the data by account, cost center, or entity and shows the trial balance details, including: opening balance, period net debit, period net credit, period activity, and closing balance

Example: Trial Balance - Dynamic Group By Dashboard

GL Journal Quarterly Summary Dashboard

Insight Description
GL Journal Summary This aggregated table insight shows the GL journal summary information, including: entity, account, cost center, journal category, journal source, functional currency, selected quarter net amount, and previous quarter net amount

Example: GL Journal Quarterly Summary Dashboard

AR Account Summary Dashboard

Insight Description
AR Reconciliation Summary This aggregated table insight shows the AR reconciliation summary information, including: account description, account, net amount variance, GL debit amount, GL credit amount, GL net amount, XLA debit amount, XLA credit amount, XLA net amount, debit amount variance, and credit amount variance

Example: AR Account Summary Dashboard

AP Account Summary Dashboard

Insight Description
AP Reconciliation Summary This aggregated table insight shows the AP reconciliation summary information, including: account description, account, net amount variance, GL debit amount, GL credit amount, GL net amount, XLA debit amount, XLA credit amount, XLA net amount, debit amount variance, and credit amount variance

Example: AP Account Summary Dashboard

Fixed Asset Account Summary Dashboard

Insight Description
FA Reconciliation Summary This aggregated table insight shows the FA reconciliation summary information, including: account description, account, net amount variance, GL debit amount, GL credit amount, GL net amount, XLA debit amount, XLA credit amount, XLA net amount, debit amount variance, and credit amount variance

Example: Fixed Asset Account Summary Dashboard

Inventory Account Summary Dashboard

Insight Description
INV Reconciliation Summary This aggregated table insight shows the FA reconciliation summary information, including: account description, account, net amount variance, GL debit amount, GL credit amount, GL net amount, XLA debit amount, XLA credit amount, XLA net amount, debit amount variance, and credit amount variance

Example: Inventory Account Summary Dashboard

Cash Flow Statement Dashboard

Insight Description
Cash Flow Statement This aggregated table insight shows a cash flow statement, including: account category, opening balance, period net debit, period net credit, period activity, and closing balance

Example: Cash Flow Statement Dashboard

Corporate Balance Sheet Dashboard

Insight Description
Corporate Balance Sheet This aggregated table insight shows a corporate balance sheet, including: account category, opening balance, period net debit, period net credit, period activity, and closing balance

Example: Corporate Balance Sheet Dashboard

Income Statement Dashboard

Insight Description
Income Statement This aggregated table insight shows an income statement, including: account category, opening balance, period net debit, period net credit, period activity, and closing balance

Example: Income Statement Dashboard

General Ledger Blueprint Data Model

Additional Information

  • The EBS_XLA.GL_IMPORT_REFERENCEStable, which is required to drill into Subledger details, can become very large. As a result, this table is not loaded into memory by default (Performance Optimized is disabled).
  • Configure the APPLICATION_ID filter in the SQL for EBS_XLA.XLA_DISTRIBUTION_LINKS, EBS_XLA.XLA_AE_HEADERS, and EBS_XLA.XLA_AE_LINES, as these tables have a large number of rows in the Oracle EBS database. Here is an example for EBS_XLA.XLA_DISTRIBUTION_LINKS, where the application_id for AP is 200, AR is 22, FA is 140 and INV is 707:
SELECT      EVENT_CLASS_CODE,
            EVENT_TYPE_CODE,
            SOURCE_DISTRIBUTION_ID_CHAR_1,
     		SOURCE_DISTRIBUTION_ID_CHAR_2,
     		SOURCE_DISTRIBUTION_ID_CHAR_3,
     		SOURCE_DISTRIBUTION_ID_CHAR_4,
     		SOURCE_DISTRIBUTION_ID_NUM_1,
     		SOURCE_DISTRIBUTION_ID_NUM_2,
    		SOURCE_DISTRIBUTION_TYPE,
     		AE_HEADER_ID,
     		AE_LINE_NUM,
     		APPLICATION_ID,
     		REF_AE_HEADER_ID,
     		TEMP_LINE_NUM,
     		ACCOUNTING_LINE_CODE
FROM        APPS.XLA_DISTRIBUTION_LINKS
WHERE       APPLICATION_ID IN (200, 707, 222, 140)

You can use the following query to find the application IDs for your Oracle EBS instance:

SELECT      fa.application_id       	"Application ID",
   		    fat.application_name    	"Application Name",
   		    fa.application_short_name   "Application Short Name",
   		    fa.basepath             	"Basepath"
FROM        fnd_application 	        fa,
   		    fnd_application_tl          fat
WHERE       fa.application_id = fat.application_id
AND         fat.language = USERENV('LANG')
ORDER BY    fat.application_name;