Implement EBS Analytics for Financials

Fixed Assets (FA) Analytics provides information to effectively analyze organizations fixed assets details, e.g. additions, adjustments, depreciations & retirements.

Fixed Assets Data Model

FA Configuration

In order to successfully configure the FA module, ensure the following schemas, business schemas, and dashboard folder exist:

Required schemas:

• EBS_FA
• COMMON schemas like EBS_ITEM_COMMON,
  EBS_CAL_COMMON, EBS_FND_COMMON,
  EBS_HR_COMMON, EBS_FIN_COMMON,
  EBS_PARTY_COMMON.

Business Schema

• FixedAssetsDetail

Dashboard Folder

• Fixed Assets

Accounts Payable (AP) Analytics

This module provides information to effectively and efficiently meet organizations’ payment obligations.

AP Data Model

AP Configuration

In order to successfully configure the AP module, ensure the following schemas, business schemas, and dashboard folder exist:

Required schemas:

• EBS_AP
• COMMON schemas like EBS_ITEM_COMMON,
  EBS_CAL_COMMON, EBS_FND_COMMON,
  EBS_HR_COMMON, EBS_FIN_COMMON,
  EBS_PARTY_COMMON.

Business Schema

• PayablesHoldsAndDiscounts
• PayablesTransactions
• SupplierPaymentsOverview
• SupplierList

Dashboard Folder

• Payables.

AP Configuration Steps

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 formula is a sample formula with 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')))))

AP Data Security (Optional)

By default, row-level data security is turned off. To turn on the data security based on the Oracle EBS ORG_ID security profile, 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)

Accounts Receivables (AR) Analytics

This module provides information on revenue, billing, and profitability to effectively and efficiently collect payment from sales.

AR Data Model

AR Configuration

In order to successfully configure the AR module, ensure the following schemas, business schemas, and dashboard folder exist:

Required schemas:

• EBS_AR
• EBS_AR_SNP
• EBS_AR_MV
• COMMON schemas like EBS_ITEM_COMMON, EBS_CAL_COMMON, EBS_FND_COMMON, EBS_HR_COMMON, EBS_FIN_COMMON, EBS_PARTY_COMMON.

Business Schema

• CollectionsManager
• CashReceipts
• BillingAndTax

Dashboard Folder

• Receivables.
• Receivables/Revenue.

AR Configuration Steps

Currency Types - In the AR module, the Incorta Unified Data Analytics Platform supports the aging metrics in four currencies. The currency types are add as a presentation variable, using the table EBS_AR_SNP.CurrencyTypes. The available currencies are “Entered”, “Functional”, “AR Global”, and “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, you would need to 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 using the presentation variable $CurrType on the Aging 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, this rate is calculated using this logic, which can be updated 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, the following formula columns for the aging buckets need to be modified 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 formula is a sample formula with 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+')))))))

(Optional) AR Configuration Steps

Here are some additional configuration details that are needed during EBS implementations:

1. Limiting Initial Extracts into Incorta: To limit the data loaded in full loads for some huge tables add a sql WHERE clause on <CREATION_DATE> <DATE>.
2. FND Lookups - We support fnd_lookups by using a lookup() function in a formula field so that we do not need to join to that table.
3. Chart of Accounts - The out-of-the-box EBS_FIN_COMMON schema comes with three charts of account segment tables - GL_ACCOUNT_SEG, GL_COST_CENTER_SEG, GL_BALANCING_SEG. Just open those and customize the SQL statements as per your implementation needs. Create any additional segment tables as required.

4. EBS Hierarchies like Account or Cost Center:

   1. Use the EBS_GL.GL_SEG_VAL_NORM_HIERARCHY. Check with your customer if multiple parents are defined for a given segment. For the segments without multiple parent, two approaches can be used.
   2. Incorta Self-Join Hierarchy.
   3. Use the flattened data.
   4. For those segments that do have multiple parents, analyze the top nodes from the given segment. We can select a limited number of top that can be created as a separate attribute / hierarchy within Incorta.

5. Calendars

   1. Incorta supports Gregorian calendar by joining to the EBS_CAL_COMMON.GL_DATE table, which is loaded from the file Date_US.csv.
   2. We support fiscal calendar by join to the EBS_CAL_COMMON.GL_DATE_PERIOD_MAP table.
6. Multi-Currency - Incorta supports amounts in Entered and Accounted currency. In order to support other global currencies (for example, USD) for multi-ledger usecase, use the EBS_FIN_COMMON.GL_DAILY_RATES table (customize as required) for doing a lookup to get the exchange rate, then multiply that by the base amount. For example, check the global amounts field in the CollectionsManager.Payments business schema.
7. Multiple child tables with a common parent (M:N) usecase:

AP_INVOICE_PAYMENTS_ALL is a child of AP_INVOICES_ALL and there is no direct path to navigate from AP_INVOICE_DISTRIBUTIONS_ALL to that. If we want to report payment amount with Invoice number and PO number, it is not possible to do that in one list report in Incorta. In this case, we woul create a small Materialized View (shown in yellow) that has the invoice_id (key) and the aggregated payment amount from the payments table. Then make that as the parent table of AP_INVOICES_ALL

AR Data Security (Optional)

By default, the row-level data security is turned off.

To turn on the data security based on the Oracle EBS ORG_ID security profile, 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:

1. Create an EBS user in Incorta.
2. Edit a table in the schema you want to secure.
3. 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.

Project Analytics (PA)

PA functional module overview

This module provides information to effectively monitor the projects financial aspects (e.g. Budgeting, Funding, Costing, Revenue, and Profitability analysis).

PA Data Model

PA Configuration

In order to successfully configure the PO module, ensure that the following schemas, business schemas, and dashboard folder exist:

Required schemas:

• EBS_PA
• COMMON schemas like EBS_ITEM_COMMON,
  EBS_CAL_COMMON, EBS_FND_COMMON,
  EBS_HR_COMMON, EBS_FIN_COMMON,
  EBS_PARTY_COMMON.

Business Schema

• ProjectFinancials

Dashboard Folder

• 5-Projects

General Ledger (GL) and Subledger Accounting (SLA) Analytics

This module allows you to report on GL journals and SLA tables:

• Incorta supports Trial Balance and Journal reporting with drill to AP/AR/INV/FA sub ledgers.
• Sample financial statements like Balance Sheet, Cash FLow and Income Statements are available using the standard FSG hierarchy info defined in EBS.
• Incorta also support GL to SLA reconciliation dashboards for AP/AR/INV/FA sub ledgers.

GL/SLA Data Model

GL/SLA Configuration

In order to successfully configure the GL/SLA module, ensure that the following schemas exists:

Required schemas:

• EBS_GL
• EBS_XLA
• COMMON schemas like EBS_ITEM_COMMON,
  EBS_CAL_COMMON, EBS_FND_COMMON,
  EBS_HR_COMMON, EBS_FIN_COMMON,
  EBS_PARTY_COMMON.

Business schemas:

• GeneralLedger
• ReconcilliationMetrics

Optional schemas:

• EBS_XLA
• EBS_XLA_MV
• EBS_FSG

Note

For GL reporting, only EBS_GL and the common schemas are needed. If you want to drill to Sub Ledger info using the XLA tables, you would need to load EBS_XLA, EBS_XLA_MV schemas and EBS_GL.GL_IMPORT_REFERENCES table. EBS_FSG schema load is needed if you want to see the Financial Statements dashboards.

Dashboard Folder:

• GL\Financial Statements
• GL\Reconciliation
• GL\Trial Balance and Journals

GL/SLA Configuration steps

1. In the EBS_GL schema, the table GL_IMPORT_REFERENCES can become very large and thus, loading to memory is turned off by default. This table is only required for drill to Subledger.
2. In the ”EBS_XLA.XLA_DISTRIBUTION_LINKS“, EBS_XLA.XLA_AE_HEADERS, EBS_XLA.XLA_AE_LINES tables SQL, configure the application_id filter, as this table has a large number of rows in the EBS source database. For example, the application id for AP is 200, for AR is 222, for FA is 140, and for INV is 707.
3. For the Reconciliation Dashboards under the GL folder, the account segments mapping to AP/AR/INV/FA is in the XLA_Accounts.csv file in the data folder. Configure the “From” and “To” Account as per the customer’s EBS configuration.
4. Use this reference, to get information about getting application ids.