Data Applications → Install and configure BlackLine for on-premises
BlackLine is a market-leading provider of cloud software that automates and controls financial close and accounting processes.
The Incorta BlackLine data application provides a faster, more genuine, and more practical option for analyzing and understanding your financial data.
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.
Move your data from Incorta to BlackLine
Here are the steps to move your data from Incorta to your BlackLine secure FTP location:
- Install Python verion 3.9. For more information, refer to How to install Python3.9 on CentOS.
- Move the scripts from Sandbox folder, located at
<INCORTA_INSTALLATION_PATH>/<script_folder>
, to production under<INCORTA_INSTALLATION_PATH>
- Setup virtual environment for Python using this command:
python3.9 -m venv <INCORTA_INSTALLATION_PATH>/<script_folder>
- Install the following packages:
- python3.9 -m pip install requests
- python3.9 -m pip install jproperties
- python3.9 -m pip install -U setuptools
- python3.9 -m pip install pysftp
- Verify SFTP server password less connectivity, using the
sftp
command. For example,sftp <ftp_user@ftp_host>
. - Verify the destination directory in the SFTP Server, for example,
/nonprod/Incorta
for Sandbox - Verify that the PROD server is able to send emails using this command:
echo "hello" | mail -s "Test Subject" -r <sender_email> <recipient_email>
NoteMake sure you replace
<sender_email>
and<recipient_email>
with appropriate valid emails. - Update parameter.properties file as required
- Setup
cron
jobs to run the python script once every 3 hours
Update parameter.properties file
You must add several parameters to the parameter.properties file located at <INCORTA_INSTALLATION_PATH>/<script_folder>
to configure different information.
Make sure to add the right values for all placeholders contained between <
>
.
Configure login information
- Update the login details with the following:
INCORTA_URL= <your_Incorta_production_URL>TENANT= <Production_Tenant_Name>USER= <user_name>PASSWORD= <encoded_password>
- To set the encoded password, run the following command in the command line:
echo -n <password> | base64
.
Configure Incorta content information and dashboard parameters
- Update the content information such as Dashboard GUIDs and Insight GUIDs as follows:
DASHBOARD_GUID= <Dashboard GUID> //for example: aa70fb6c-8fc6-45c6-8b98-f96e9d96264cINSIGHT_IDS= <Insight_GUID> //for example: b6d00d21-b654-444f-bc39-75338e6a5026INSIGHT_FILENAME= <GLAccounts_Incorta>DASHBOARD_QUERY= <The_same_as_in_Sandbox>
Configure FTP details
- Update the FTP details as follows:
FTP_HOST= <your_ftp_host_URL>FTP_DIR= <your_ftp_folder/directory> // for example: /incortaFTP_USER= <ftp_username>FTP_PASSWORD= <ftp_password>
Configure and verify email and notifications
Update the email notification details with the following:
EMAIL_SENDER= <sender_email> //example: jane.doe@example.comEMAIL_RECIPIENT= <recipient_email> //example: john.doe@example.com (Multiple recipients is separated by a white space)EMAIL_SUBJECT= <email-subject> //example: Sandbox Export Automation Script
Install the Incorta BlackLine data application
To install Incorta BlackLine data application, please contact Incorta Support to provide you with you the required files.
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 Name | Description | Refresh Mechanism | Usage |
---|---|---|---|
GL_LEDGERS | GL ledgers table from EBS | Incremental | Used to populate the Blackline Account template |
GL_PERIODS | GL periods table from EBS | Incremental | Used to populate the Blackline Account template |
GL_JE_HEADERS | Journal headers table from EBS | Incremental | Used to populate the Journal Line Drill dashboard |
GL_JE_LINES | Journal Lines table from EBS | Incremental | Used to populate the Journal Line Drill dashboard |
GL_JE_SOURCES | Journal Sources table from EBS | Incremental | Used to populate the Journal Line Drill dashboard |
GL_JE_CATEGORIES | Journal categories table from EBS | Incremental | Used to populate the Journal Line Drill dashboard |
GL_JE_BATCHES | Journal batches tables from EBS | Incremental | Used to populate the Journal Line Drill dashboard |
GL_ACCOUNT_SEG | GL Account segment table | Incremental | Used to populate the Blackline Account template |
GL_BALANCING_SEG | GL Balancing segment table | Incremental | Used to populate the Blackline Account template |
GL_COST_CENTER_SEG | GL cost center segment table | Incremental | Used to populate the Blackline Account template |
GL_CODE_COMBINATIONS | GL code combinations table from EBS | Incremental | Used to populate the Blackline Account template |
XX_GL_CODE_COMBINATIONS | Custom code combinations table created for Blackline Data App | Incremental | Used to populate the Blackline Account template |
Blackline_Account_Groups | Table to store Account groups details in Incorta from Blackline | Full | Used to enable drill from Blackine Accounts and Account Groups |
Mv_Active_Account | Materialized view that keeps the status of accounts | Incremental | Used to set the values of the flags in the Blackline Accounts template |
Mv_Account_Activity_in_Period | Materialized view that keeps track of activity in a period for a given combination | Full | Used to set the values of the flags in the Blackline Accounts template |
Mv_GL_Balances | Materialized view with GL balances used to enable drill down from account groups | Incremental | Used 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 Name | Description | Usage |
---|---|---|
Accounts | A business view that has all the attributes needed in the Blackline reconciliation template | Used in the dashboard to push Blackline accounts template data into Blackline SFTP location |
AccountGroups | A business view that has all the account group related attributes to be used in the drill-thru reports from Blackline | Used in the dashboard that drills down to journal details from Blackline Accounts or Account Groups |
JournalLines | A business view that has all the attributes used in the Journal details drill report | Used in the dashboard that drills down to journal details from Blackline Accounts or Account Groups |
FixedAssets | A business view that has all the attributes used in the Fixed Assets subledger balances Blackline template | Used 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 Name | Description | Usage |
---|---|---|
01 Accounts | A dashboard that has the data for the Blackline Account template | Used to push data into Blackline SFTP location |
GL Journal Details | A dashboard that has Journal Details | Used to drill to journal details from Blackline Accounts or Account Groups |
Subledger drill | A ashboard that has the subledger drills for AP, AR, Inventory, and Fixed Assets | Used to drill from journal details to individual subledgers |
Mandatory Configurations
Incorta requires you to configure several 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.
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
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 configuration
If you need to enable additional navigation from BlackLine to Incorta to examine GL Journal details and sub-ledger drills. 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 table | Child Table Column | Parent Table | Parent Table Column |
---|---|---|---|
XLA_MV.XLAAPBridge | CODE_COMBINATION_ID | BL_EBS_GL.ACCOUNT_SEG | CODE_COMBINATION_ID |
XLA_MV.XLAAPBridge | CODE_COMBINATION_ID | BL_EBS_GL.XX_GL_CODE_COMBINATIONS | CODE_COMBINATION_ID |
XLA_MV.XLAAPBridge | JE_HEADER_ID, JE_LINE_NUM | BL_EBS_GL.GL_JE_LINES | JE_HEADER_ID, JE_LINE_NUM |
XLA_MV.XLAAPBridge | LEDGER_ID | BL_EBS_GL.GL_LEDGERS | LEDGER_ID |
XLA_MV.XLAARBridge | CODE_COMBINATION_ID | BL_EBS_GL.ACCOUNT_SEG | CODE_COMBINATION_ID |
XLA_MV.XLAARBridge | CODE_COMBINATION_ID | BL_EBS_GL.XX_GL_CODE_COMBINATIONS | CODE_COMBINATION_ID |
XLA_MV.XLAARBridge | JE_HEADER_ID, JE_LINE_NUM | BL_EBS_GL.GL_JE_LINES | JE_HEADER_ID, JE_LINE_NUM |
XLA_MV.XLAARBridge | LEDGER_ID | BL_EBS_GL.GL_LEDGERS | LEDGER_ID |
XLA_MV.XLAINVBridge | CODE_COMBINATION_ID | BL_EBS_GL.ACCOUNT_SEG | CODE_COMBINATION_ID |
XLA_MV.XLAINVBridge | CODE_COMBINATION_ID | BL_EBS_GL.XX_GL_CODE_COMBINATIONS | CODE_COMBINATION_ID |
XLA_MV.XLAINVBridge | JE_HEADER_ID, JE_LINE_NUM | BL_EBS_GL.GL_JE_LINES | JE_HEADER_ID, JE_LINE_NUM |
XLA_MV.XLAINVBridge | LEDGER_ID | BL_EBS_GL.GL_LEDGERS | LEDGER_ID |
XLA_MV.XLAFABridge | CODE_COMBINATION_ID | BL_EBS_GL.ACCOUNT_SEG | CODE_COMBINATION_ID |
XLA_MV.XLAFABridge | CODE_COMBINATION_ID | BL_EBS_GL.XX_GL_CODE_COMBINATIONS | CODE_COMBINATION_ID |
XLA_MV.XLAFABridge | JE_HEADER_ID, JE_LINE_NUM | BL_EBS_GL.GL_JE_LINES | JE_HEADER_ID, JE_LINE_NUM |
XLA_MV.XLAFABridge | LEDGER_ID | BL_EBS_GL.GL_LEDGERS | LEDGER_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
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
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:
https://<your_incorta_ip_address>/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_incorta_ip_address>/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
You must replace <your_incorta_ip_address>
with your Incorta evnironment, 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
SELECTgcc.code_combination_id,gcc.chart_of_accounts_id,Gledger.LEDGER_ID,gcc.segment1 company,-- it is SEGMENT1 for all chart of accountsCASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment3 || '.' || gcc.segment4WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6ELSE ''END account,--Reuse EBS_FIN_COMMON.GL_ACCOUNT_SEG DescriptionCASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment5WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment6WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment7ELSE ''END ic_affiliate,--I/C Affiliate'' ledger,-- Ledger will be sourced from GL_BALANCES.LEDGER_IDlob_eligible.segment2 lob,-- lobCASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6ELSE ''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_widFROMapps.gl_code_combinations gccleft outer join (SELECTDISTINCT glcc.code_combination_id,glcc.segment1,glcc.segment6,glcc.segment2FROMapps.gl_code_combinations glcc,apps.fnd_lookup_values flvWHERE1 = 1AND glcc.chart_of_accounts_id = 50688AND 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.segment6AND flv.attribute1 = glcc.segment1)OR (flv.attribute2 IS NULLAND flv.attribute1 = glcc.segment1)OR (flv.attribute2 = glcc.segment6AND flv.attribute1 IS NULL))) lob_eligible ON gcc.code_combination_id = lob_eligible.code_combination_idleft outer join(SELECT ledger_id,CHART_OF_ACCOUNTS_ID from apps.GL_LEDGERS) Gledger on gcc.chart_of_accounts_id=Gledger.chart_of_accounts_idWHERE1 = 1AND gcc.chart_of_accounts_id IN (101, 50408, 50688) -- Remove Entity Unique IDs as '000'AND gcc.segment1 <> '000' -- Static Filters on Account NumbersAND (CASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6ELSE ''END LIKE '1%'OR CASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6ELSE ''END LIKE '2%'OR CASEWHEN gcc.chart_of_accounts_id = 101 THEN gcc.segment3WHEN gcc.chart_of_accounts_id = 50408 THEN gcc.segment4WHEN gcc.chart_of_accounts_id = 50688 THEN gcc.segment6ELSE ''END LIKE '3%') -- Exclude Specific Code CombinationsAND gcc.code_combination_id NOT IN (SELECTflv.meaningFROMapps.fnd_lookup_values flvWHEREflv.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));