Skip to main content

Install Source System-Specific Objects

For Transaction, AVL, Drilldown, and SyncBack, you must install the source system-specific objects, such as extraction views, change data capture (CDC) objects, and Reconciliation-related objects in the database on which your source system resides.

Install Source System-Specific Objects for Transaction and AVL

Before you begin, make sure that the setup files are available at the following locations:

  • <SetupFolder>\<SourceSystem>\<SourceDatabase>\Scripts
  • <SetupFolder>\Common_Mappings

In the subsequent sections, the path for the folder, such as Scripts, is referred to as \\<SourceDatabase>\Scripts, instead of the full path of the folder.

Oracle E-Business Suite, IFS Applications, PeopleSoft, Infor Baan

To install source system-specific objects for Oracle E-Business Suite, IFS Applications, Oracle PeopleSoft, or Infor Baan, perform the following steps:

  1. Create a schema on the database in which the schema pertaining to your source system resides. This schema is referred to as source schema and is used to store extraction views. For information on how to create the schema, see the sample SQL statements in DB Script.sql located at \\<SourceDatabase>\Scripts. For Oracle E-Business Suite, refer to DB Script Manual.sql and readme.txt in the SourceConnect/SCT/EBS folder. DB_Script_Auto.sql is for automation script.
  2. (For Transaction) Create the CDC schema and tables in the database containing extraction views based on the following tables in your source system. For information on how to configure CDC, see the sample statements in DB Script.sql.
    • Oracle E-Business Suite:GL_JE_HEADERS table
    • IFS Applications:GEN_LED_VOUCHER_TAB table
    • PeopleSoft:PS_JRNL_HEADER table
    • Infor Baan:TTFGLD106<CompanyCode> table
  3. (For AVL) Create the CDC schema and tables in the database containing accounting views based on the tables in your source system. For information on how to configure CDC, see the sample statements in DB Script.sql.
  4. At the SQL prompt, run install.sql located at \\<SourceDatabase>\Scripts, and provide values for the prompts specific to the following that are applicable to source system:
    • Name and password for the schema in which your source application tables reside
    • The database that contains the extraction views and CDC objects
    • CDC schema name and password

    The extraction views are created.

JD Edwards EnterpriseOne on Oracle Database

To install source-specific objects for JD Edwards EnterpriseOne on an Oracle database, perform the following steps:

  1. Create a schema on the database in which your source system resides. This schema is used to store extraction views.
  2. Configure CDC for the F0911 table in the database that contains extraction views in your source system. For information on how to configure CDC, see the sample statements in DB Script Manual.sql at \\<SourceDatabase>\Scripts.
  3. At the SQL prompt, run install.sql located at \\<SourceDatabase>\Scripts. The extraction views and the related database objects are created. Also, the tables and views related to Level 1 Reconciliation report are created. For information about any errors, see log.out at \\<SourceDatabase>\Scripts.

JD Edwards EnterpriseOne on SQL Server

To install source-specific objects for JD Edwards EnterpriseOne on a SQL Server database, perform the following steps:

  1. Create a database on the server computer in which your source system resides. This database is used to store extraction views.
  2. Configure CDC for the F0911 table in the database that contains extraction views in your source system. For information on how to configure CDC for SQL Server 2014 and later, see "Configure CDC on SQL Server" in Appendix A: Configure CDC on SQL Server.
  3. Run sct_install.bat located at \\<SourceSystem>\<SourceDatabase>. The extraction views and the related database objects are created. For information about any errors, see log.out at \\<SourceSystem>\<SourceDatabase>.

Dynamics AX, Dynamics NAV, Dynamics SL, Dynamics GP, Epicor iScala, SyteLine

To install source-specific objects for Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics SL, Microsoft Dynamics GP, Epicor iScala, or Infor CloudSuite Industrial (SyteLine), perform the following steps:

  1. Create a database on the server computer in which your source system resides. This database is used to store extraction views.
  2. Run sct_install.bat located at \\<SourceDatabase>\Scripts. The extraction views and the related database objects are created. For information about any errors, see log.out at \\<SourceDatabase>\Scripts.

Dynamics 365 for Finance and Operations and Dynamics 365 Business Central (On-premises and Cloud)

Refer to the Create Source System chapter in the SAP Central Finance Transaction Replication by insightsoftware for SAP S/4HANA Installer and Management Console Guide for information about installing the source systems for Transaction.

Workday

To install source-specific objects for Workday, perform the following:

  • Create reports in your Workday instance based on the out-of-the-box report definitions specified in the .docx files at \\<SourceSystem>\SOURCE_SCRIPTS. The output is downloaded as .csv files. The data in the .csv files is considered as the source data.

QAD

To install Transaction for QAD, perform the following:

  1. Run the .p files available at \\<SourceSystem>\<SourceDatabase>\Progress DB. The output is downloaded as .txt files.
  2. Manually import the data in the .txt files into the .csv files according to the format specified in the insightsoftware Universal Format.xlsx file in the setup folder. The data in the .csv files is considered as the source data.

NetSuite

To install source-specific objects for NetSuite, perform the following steps:

  1. Create a schema on an Oracle database. This schema is used to store extraction views. For information on how to create the schema, see DB Script.sql located at \\<SourceDatabase>\Scripts.
  2. At the SQL prompt, run cr_tables.sql located at \\<SourceDatabase>\Scripts. The tables are created to store the source data.
  3. Create the CDC schema and tables in the database containing extraction views based on the NS_TRANSACTIONS table in your source system. For information on how to configure CDC, see DB Script.sql located at \\<SourceDatabase>\Scripts.
  4. At the SQL prompt, run install.sql located at \\<SourceDatabase>\Scripts. The extraction views are created.

Deltek Costpoint

To install source-specific objects for Deltek Costpoint, perform the following steps:

  1. Create a database on the server computer in which your source system resides. This database is used to store extraction views.
  2. Run sct_install.bat located at \\<SourceDatabase>\Scripts. The extraction views and related database objects are created in the source and staging databases. For information about any errors, see log.out at \\<SourceDatabase>\Scripts.

1C:Enterprise

To install source-specific objects for 1C:Enterprise, perform the following:

  1. Import the SourceConnectExtracts.cf configuration file available at \\<SourceSystem> into your 1C:Enterprise instance. On the home page of the application interface, a link named insightsoftware SourceConnect Configuration is displayed on the left pane and links named Extracting plan and Log Journal are displayed on the right pane.

    For information about importing and using the configuration file, contact insightsoftware Support.

  2. Select Extracting plan. The Extracting plan window is displayed.
  3. Double-click SAP. The insightsoftware SAP (Extracting plan) window is displayed.
  4. Select the required options, such as Extract transactions, and specify the folder location that must contain the extracted data.
  5. Depending on your requirements, select the extraction options, such as Extract all data or Extract data (changes). The source data is extracted to a .csv file in the specified folder.

Install Source System-Specific Objects for Drilldown

This section explains how to install source system-specific objects for Drilldown. Before you install, perform the following task:

  • Copy the setup files available at the <SetupFolder>\<SourceSystem>\<SourceDatabase>\Scripts folder to the computer on which Drilldown is installed.

To install system-specific objects for Drilldown

  • (For all source systems) Based on the tables in your source system, create CDC tables in the database containing extraction views. For the list of source tables that you must use for creating the CDC tables, see readme.txt at <SetupFolder>\<SourceSystem>.

    Note: If your source system is on SQL Server 2014 or later, create CDC tables in the database containing extraction views. For information, see "Configure CDC on SQL Server" in Appendix A: Configure CDC on SQL Server.

Install Source System-Specific Objects for SyncBack

You can install source system-specific objects for SyncBack. For source systems, such as Oracle E-Business Suite, the source system-specific objects for SyncBack are configured by default.

  • Copy the setup files available at the following location to the computer on which SyncBack is installed:
    • <SetupFolder>\<SourceSystem>\<SourceDatabase>

JD Edwards EnterpriseOne

To install source system-specific objects for SyncBack

  1. Register the SyncBack APIs in your source system. Import the .par files at \\<SourceSystem>\<SourceDatabase>\PAR files into your source system to create the required Universal Batch Engine (UBE) reports.
  2. Run the Grants.sql file at \\<SourceSystem>\<SourceDatabase>\Scripts in the schema in which the source tables exist. The grants are provided on the staging tables to the application user in the source database.
  3. Run the Synonyms.sql file at \\<SourceSystem>\<SourceDatabase>\Scripts in the schema in which the source tables exist. The synonyms are created for the staging tables for which the application user has grants.

Microsoft Dynamics AX

To install source system-specific objects for SyncBack

  • Import the .xpo files at \\<SourceSystem>\XPO into your source system to import the application objects. For information about importing the file, see Import Process in AX-2009.docx.

Microsoft Dynamics NAV and 365 Business Central (On-premises)

To install source system-specific objects for SyncBack

  1. Open the Visual Studio Code (AL) environment.
  2. Navigate to the tables folder and create two new tables:
    1. CUSTOMER_PAYMENTS_TABLE
    2. VENDOR_PAYMENTS_TABLE
  3. After creating the payment tables, add the exact same fields (case-sensitive) from the source database table.
  4. Set the field property EXTERNALNAME to match the field name in the source database table (case-sensitive).
  5. Follow the standard process to create pages for viewing data:
    1. CUSTOMER_PAYMENTS_PAGES
    2. VENDOR_PAYMENTS_PAGES
  6. Create new reports for journal creation.
  7. For the vendor report, create a journal according to the syncback functionality to generate a payment journal.
  8. For the customer report, create a journal according to the syncback functionality to generate a cash receipt:
    1. CUSTOMER_PAYMENTS_REPORTS
    2. VENDOR_PAYMENTS_REPORT
    3. CUSTOMER_PAYMENTS_REVERSAL_REPORTS
    4. VENDOR_PAYMENTS_REVERSAL_REPORTS
  9. Create a CODEUNIT to connect the two databases:
    1. Application BC database or NAV database.
    2. Source database
      3. Note: Configure the database connection credentials in the CODEUNIT as follows:

      RegisterTableConnection(TableConnectionType::ExternalSQL, DatabaseName,

      'Data Source=HY***; Initial catalog=MSFT****; User ID=****; Password=****;')

Workday

To install source system-specific objects for SyncBack

  • Create customer integration payments in your source system by using the instructions in the Customer Payment Integration.docx document at \\<SourceSystem>\SOURCE_SCRIPTS.

QAD

  1. Run the xxapmcmt.p and xxarpamt.p files located at \\<SourceSystem>\<SourceDatabase>\Progress DB. The corresponding xxapmcmt.r and xxarpamt.r files will be generated in the QAD server.
  2. Create an .xlsx file and update it with the data in the staging database according to the format specified in the arpay|appay.xlsx file in the setup folder.
  3. Import the .xlsx file that you created into the QAD server.
  4. Run the xxapmcmt.r and xxarpamt.r files in the QAD application by providing the input .xlsx file as input.

Install Source System-Specific Objects for Flat File Data

To install source-specific objects for Transaction, Drilldown, or SyncBack for the source data in the flat file format, perform the following:

  1. Save the data according to the format specified in insightsoftware Universal Format.xlsx located at <SetupFolder>\UFF.
  2. Provide the following information during the implementation:
    • Frequency of updates to the data in the file
    • Location in which you want to save the file on your computer

Was this article helpful?

We're sorry to hear that.