Data Applications → BlackLine Account Reconciliations for Oracle EBS

Using the Incorta BlackLine data application and data destination, you will have the edge to quickly ingest and integrate data using Oracle E-Business Suite (EBS). Integrating Oracle EBS with BlackLine with packaged data templates for BlackLine Account Reconciliations will replace any manual processes and custom code, it will also refresh the data incrementally from the data source more frequently.

Through the Incorta BlackLine data application, you will have a consistent, single version of detailed data pulled up from multiple data sources. You can also drill down from summarized to detailed data.

Configure BlackLine as a data destination

Here are the steps to configure BlackLine as a data destination in Incorta:

  • Sign in to your Incorta cluster
  • In the Navigation bar, select Data+ NewAdd New Data Destination
  • Create a new BlackLine data destination. Refer to Concepts → Data Destination for more information.

Install the Incorta BlackLine data application

Here are the steps to install the Incorta BlackLine data application:

  • In the Navigation bar, select Marketplace
  • Select the BlackLine Reconciliation for Oracle EBS data application
  • Select Install

BL_EBS_GL schema Schema Diagram

Available physical schemas and tables

The following describes the different schemas available in the data application and the usage for each table, along with its description and refresh mechanism:

Table NameDescriptionRefresh MechanismUsage
GL_LEDGERSGL ledgers table from EBSIncrementalUsed to populate the Blackline Account template
GL_PERIODSGL periods table from EBSIncrementalUsed to populate the Blackline Account template
GL_JE_HEADERSJournal headers table from EBSIncrementalUsed to populate the Journal Line Drill dashboard
GL_JE_LINESJournal Lines table from EBSIncrementalUsed to populate the Journal Line Drill dashboard
GL_JE_SOURCESJournal Sources table from EBSIncrementalUsed to populate the Journal Line Drill dashboard
GL_JE_CATEGORIESJournal categories table from EBSIncrementalUsed to populate the Journal Line Drill dashboard
GL_JE_BATCHESJournal batches tables from EBSIncrementalUsed to populate the Journal Line Drill dashboard
GL_ACCOUNT_SEGGL Account segment tableIncrementalUsed to populate the Blackline Account template
GL_BALANCING_SEGGL Balancing segment tableIncrementalUsed to populate the Blackline Account template
GL_COST_CENTER_SEGGL cost center segment tableIncrementalUsed to populate the Blackline Account template
GL_CODE_COMBINATIONSGL code combinations table from EBSIncrementalUsed to populate the Blackline Account template
XX_GL_CODE_COMBINATIONSCustom code combinations table created for Blackline Data AppIncrementalUsed to populate the Blackline Account template
Blackline_Account_GroupsTable to store Account groups details in Incorta from BlacklineFullUsed to enable drill from Blackine Accounts and Account Groups
Mv_Active_AccountMaterialized view that keeps the status of accountsIncrementalUsed to set the values of the flags in the Blackline Accounts template
Mv_Account_Activity_in_PeriodMaterialized view that keeps track of activity in a period for a given combinationFullUsed to set the values of the flags in the Blackline Accounts template
Mv_GL_BalancesMaterialized view with GL balances used to enable drill down from account groupsIncrementalUsed to enable drill from Blackine Accounts and Account Groups

Available views in the BlackLineDM business schema

The following describes the different view available in the BlackLine DM business schema and the usage for each view along with its description:

Business View NameDescriptionUsage
AccountsA business view that has all the attributes needed in the Blackline reconciliation templateUsed in the dashboard to push Blackline accounts template data into Blackline SFTP location
AccountGroupsA business view that has all the account group related attributes to be used in the drill-thru reports from BlacklineUsed in the dashboard that drills down to journal details from Blackline Accounts or Account Groups
JournalLinesA business view that has all the attributes used in the Journal details drill reportUsed in the dashboard that drills down to journal details from Blackline Accounts or Account Groups
FixedAssetsA business view that has all the attributes used in the Fixed Assets subledger balances Blackline templateUsed in the dashboard to push Blackline Fixed Assets subledger balances template data into Blackline SFTP location

Available dashboards

The following describes the different dashboards available in the BlackLine data application and the usage for each dashboard along with its description:

Dashboard NameDescriptionUsage
01 AccountsA dashboard that has the data for the Blackline Account templateUsed to push data into Blackline SFTP location
GL Journal DetailsA dashboard that has Journal DetailsUsed to drill to journal details from Blackline Accounts or Account Groups
Subledger drillA ashboard that has the subledger drills for AP, AR, Inventory, and Fixed AssetsUsed to drill from journal details to individual subledgers

Mandatory Configurations

Incorta requires you to configure few tables before using the BlackLine Reconciliation for Oracle Oracle E-Business Suite data application. These configurations aim to adapt the default logic implemented in the tables to your business logic.

Note

You do not need to apply the mandatory configurations if the default ones comply with your business.

In the BL_EBS_GL schema, edit the following tables using SQL.

XX_GL_CODE_COMBINATIONS

Edit the table to update the segments for each of the charts of accounts you want to bring to BlackLine using Incorta. Currently, the following is the default behavior:

  • Chart of accounts ID is 101, and account is mapped to segment 3
  • Inter company affiliate is mapped to segment 5
  • Line of business (LOB) is mapped to segment 2
Note

The segments and visualization of accounts IDs depend on the customer's Oracle implementation.

Refer to additional configurations for a sample SQL and more information.

Mv_Active_Account

Edit the table to reflect whether an account is active or not. Currently, the default behavior states that if an account is enabled, then the account is active.

If the account is disabled and its status changes in the current time frame, then the active account remains inactive.

Mv_Account_Activity_in_Period

Edit this table to determine if an account is active for a given period of time. Currently, the default behavior states that if there is a period_net_balance of 0, then there is no activity in the current period. Any other value indicates activity in this period.

BlackLine additional configurations

If you need to enable additional navigation from BlackLine to Incorta to examine GL Journal details and sub-ledger drills.

Note

You must download the Enterprise version of the data application. Alternatively, you can use the Oracle EBS Base data application.

To achieve that, apply the following steps:

  • Use these schemas to create the joins in the following step:

    • EBS_AP
    • EBS_AR
    • EBS_INV
    • EBS_FA
    • EBS_ITEM_COMMON
    • EBS_PARTY_COMMON
    • EBS_HR_COMMON
    • EBS_PARTY_COMMON
    • EBA_XLA
    • EBS_XLA_MV
  • Create the following joins from the XLA_MV schema:

Child tableChild Table ColumnParent TableParent Table Column
XLA_MV.XLAAPBridgeCODE_COMBINATION_IDBL_EBS_GL.ACCOUNT_SEGCODE_COMBINATION_ID
XLA_MV.XLAAPBridgeCODE_COMBINATION_IDBL_EBS_GL.XX_GL_CODE_COMBINATIONSCODE_COMBINATION_ID
XLA_MV.XLAAPBridgeJE_HEADER_ID, JE_LINE_NUMBL_EBS_GL.GL_JE_LINESJE_HEADER_ID, JE_LINE_NUM
XLA_MV.XLAAPBridgeLEDGER_IDBL_EBS_GL.GL_LEDGERSLEDGER_ID
XLA_MV.XLAARBridgeCODE_COMBINATION_IDBL_EBS_GL.ACCOUNT_SEGCODE_COMBINATION_ID
XLA_MV.XLAARBridgeCODE_COMBINATION_IDBL_EBS_GL.XX_GL_CODE_COMBINATIONSCODE_COMBINATION_ID
XLA_MV.XLAARBridgeJE_HEADER_ID, JE_LINE_NUMBL_EBS_GL.GL_JE_LINESJE_HEADER_ID, JE_LINE_NUM
XLA_MV.XLAARBridgeLEDGER_IDBL_EBS_GL.GL_LEDGERSLEDGER_ID
XLA_MV.XLAINVBridgeCODE_COMBINATION_IDBL_EBS_GL.ACCOUNT_SEGCODE_COMBINATION_ID
XLA_MV.XLAINVBridgeCODE_COMBINATION_IDBL_EBS_GL.XX_GL_CODE_COMBINATIONSCODE_COMBINATION_ID
XLA_MV.XLAINVBridgeJE_HEADER_ID, JE_LINE_NUMBL_EBS_GL.GL_JE_LINESJE_HEADER_ID, JE_LINE_NUM
XLA_MV.XLAINVBridgeLEDGER_IDBL_EBS_GL.GL_LEDGERSLEDGER_ID
XLA_MV.XLAFABridgeCODE_COMBINATION_IDBL_EBS_GL.ACCOUNT_SEGCODE_COMBINATION_ID
XLA_MV.XLAFABridgeCODE_COMBINATION_IDBL_EBS_GL.XX_GL_CODE_COMBINATIONSCODE_COMBINATION_ID
XLA_MV.XLAFABridgeJE_HEADER_ID, JE_LINE_NUMBL_EBS_GL.GL_JE_LINESJE_HEADER_ID, JE_LINE_NUM
XLA_MV.XLAFABridgeLEDGER_IDBL_EBS_GL.GL_LEDGERSLEDGER_ID
  • In your BlackLine environment, do the following:

    • Download Group Accounts template from BlackLine (for all periods)
    • Go to reporting
    • Run the Account Template report
    • Select All Periods, then GL and Group Accounts
    • When the report complete, download it in a CSV format
  • Upload the file you downloaded in Incorta. [[note | Note]] | For more information on how to upload the file, refer to Connectors → Data Files.

  • Load your data using full load mode

To maintain your data and keep it up-to-date, you must apply the following steps regularly:

  • In your BlackLine environment:
    • Download the Group Accounts template from BlackLine (for all current and previous periods only)
    • Go to reporting
    • Run the Account Template report
    • Select All Periods, then GL and Group Accounts
    • When the report complete, download it in a CSV format
  • Upload the file you downloaded in Incorta replacing the existing one
Recommendation

Apply these steps one or twice per week or whenever you have refreshed data.

Drill-down configurations in BlackLine

In your BlackLine environment, go to ERP Drill Down settings and apply the following step:

  • Add a drill down “Oracle GL Detail BL Accounts” using the following URL:
http://<your_cluster_name>.incorta.com/incorta/#/dashboard/<your_GL_Journal_Details_dashboard_path>/params?filters=[{ "field": "BlackLineDM.AccountGroups.Entity_with_plus", "operator": "IN", "values": ["[BL-Seg1]”] },{ "field": "BlackLineDM.AccountGroups.ACCOUNT", "operator": "IN", "values": ["[BL-Seg2]”] },{ "field": "BlackLineDM.Accounts.SEGMENT3", "operator": "IN", "values": ["[BL-Seg3]"]},{ "field": "BL_EBS_GL.Mv_GL_Balances.LEDGER_ID”, "operator": "IN", "values": ["[BL-Seg4]"] },{ "field": "BlackLineDM.AccountGroups.LOB", "operator": "IN", "values": [“[BL-Seg5]"] }]&merge-filters=true
  • Add a drill down “Oracle GL Detail BL Group” using the following URL:
https://<your_cluster_name>.incorta.com/incorta/#/dashboard/<your_GL_Journal_Details_dashboard_path>/params?filters=[{ "field": "BlackLineDM.AccountGroups.Entity_with_plus", "operator": "IN", "values": ["[BL-Seg1]”] },{ "field": "BlackLineDM.AccountGroups.Group_Account_with_plus", "operator": "IN", "values": ["[BL-Seg2]”] },{ "field": "BlackLineDM.Accounts.END_DATE”, "operator": "IN", "values": [“[BL-PeriodEnd]”] }]&merge-filters=true
Important

You must replace <your_cluster_name> with your cloud cluster name, and <your_GL_Journal_Details_dashboard_path> with your URL dashboard path.

Additional configurations

SQL example for XX_GL_CODE_COMBINATIONS table in BL_EBS_GL schema

In the following SQL example, assume having three chart of accounts pre-seeded and have the segments for each key hardcoded. You can add additional chart of accounts and map the keys to the right segments. In this example, you also have the following:

  • the logic for inclusions and exclusions
  • all the keys for Blackline are harmonized across multiple chart of accounts needed for Blackline.
  • the logic for key 5 (LOB) - conditional display of the 5th key (Optional configuration)

Steps to edit the configuration

  • Select the needed BlackLine keys
  • Harmonize across all chart of accounts
  • Apply any inclusion, exclusion logic, and any special conditional logic needed
SELECT
gcc.code_combination_id,
gcc.chart_of_accounts_id,
Gledger.LEDGER_ID,
gcc.segment1 company,
-- it is SEGMENT1 for all chart of accounts
CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment3 || '.' || gcc.segment4
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6
ELSE ''
END account,
--Reuse EBS_FIN_COMMON.GL_ACCOUNT_SEG Description
CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment5
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment6
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment7
ELSE ''
END ic_affiliate,
--I/C Affiliate
'' ledger,
-- Ledger will be sourced from GL_BALANCES.LEDGER_ID
lob_eligible.segment2 lob,
-- lob
CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6
ELSE ''
END account_reference,
gcc.enabled_flag,
Nvl(gcc.end_date_active, gcc.last_update_date) status_change_date,
To_number(
To_char(
Nvl(gcc.end_date_active, gcc.last_update_date),
'YYYYMMDD'
)
) status_change_dt_wid,
To_number(
To_char(
Nvl(gcc.end_date_active, gcc.last_update_date),
'YYYYMM'
)
) status_change_month_wid
FROM
apps.gl_code_combinations gcc
left outer join (
SELECT
DISTINCT glcc.code_combination_id,
glcc.segment1,
glcc.segment6,
glcc.segment2
FROM
apps.gl_code_combinations glcc,
apps.fnd_lookup_values flv
WHERE
1 = 1
AND glcc.chart_of_accounts_id = 50688
AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(flv.start_date_active, SYSDATE))
AND Trunc(
Nvl(flv.end_date_active, SYSDATE)
)
AND flv.enabled_flag = 'Y'
AND flv.LANGUAGE = 'US' --Userenv('lang')
AND flv.lookup_type = 'XXSG_BLACKLINE_LOB_LKP'
AND (
(
flv.attribute2 = glcc.segment6
AND flv.attribute1 = glcc.segment1
)
OR (
flv.attribute2 IS NULL
AND flv.attribute1 = glcc.segment1
)
OR (
flv.attribute2 = glcc.segment6
AND flv.attribute1 IS NULL
)
)
) lob_eligible ON gcc.code_combination_id = lob_eligible.code_combination_id
left outer join
(SELECT ledger_id,CHART_OF_ACCOUNTS_ID from apps.GL_LEDGERS) Gledger on gcc.chart_of_accounts_id=Gledger.chart_of_accounts_id
WHERE
1 = 1
AND gcc.chart_of_accounts_id IN (101, 50408, 50688) -- Remove Entity Unique IDs as '000'
AND gcc.segment1 <> '000' -- Static Filters on Account Numbers
AND (
CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6
ELSE ''
END LIKE '1%'
OR CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6
ELSE ''
END LIKE '2%'
OR CASE
WHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3
WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4
WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6
ELSE ''
END LIKE '3%'
) -- Exclude Specific Code Combinations
AND gcc.code_combination_id NOT IN (
SELECT
flv.meaning
FROM
apps.fnd_lookup_values flv
WHERE
flv.lookup_type = 'XXWG_GL_BLACKLINE_EXCLUSIONS'
AND flv.LANGUAGE = 'US'
AND flv.enabled_flag = 'Y'
AND Trunc (SYSDATE) BETWEEN Trunc (flv.start_date_active)
AND Nvl (flv.end_date_active, SYSDATE + 1)
);