For many years TimeXtender has supported Infor SunSystems in v20 through the business unit data source adapter - Sun Adapter.

The Sun Adapter has now been implemented into an Ingest data source - TimeXtender Infor SunSystems Data Source.

In this implementation,, we have excluded SunSystems version 4 and are building support for the database structure introduced with version 5.

SunSystems database structure

The system uses multiple databases. It has one configuration database (SunSystems domain database) which contains information about business units.

The business unit data is stored in one or more databases (SunSystems data database)

Table types

The adapter only reads the table/view structure from the SunSystem data database. It will read the structure from the database where the template business unit data is stored.

There are three types of tables/views

•  Ledger (also business unit dependent)
•  Business unit depended
•  None business units depended

How the table/view is identified

• "Ledger" is defined by a letter between A-K. The table name will start with a three-letter code for a business unit followed by _ then the ledger character then _ then the table name
  • Example: ACM_A_BAL, ACM_B_BAL, FND_A_BAL, FND_B_BAL 
    The example above will give one table in the "Ingest" instance called 'BAL'
• "Business unit depended" is defined by table name starting with the three-letter code for a business unit followed by _ then the table name
  • Example: ACM_CUST, FND_CUST, INS_CUST, PK1_CUST, PKP_CUST
    The example above will give one table in the "Ingest" instance called 'CUST'
• "None business unit depended" is defined by not starting with a three-letter code for a business unit and followed by _
  • Example: BAL_CONFIG
    The example above will give one table in the "Ingest" instance called 'BAL_CONFIG'

"Ingest" table structure

An additional column in all tables from the TimeXtender Infor SunSystems Data Source:

• 'DW_PARTITION'
  • Composite identifier containing:
    • Business unit group name
    • Business unit name
    • Ledger (if applicable)
    • Critical for incremental loading:
      • Must be selected when enabling incremental load for a table
• None business units depended
• Contains no additional columns
• Business unit depended:
• Contains column 'SUN_DB' which contains the three-letter code for the business unit
• Ledger:
• Contains column 'SUN_DB' which contains the three-letter code for the business unit and  'DW_LEDGER_SOURCE' which contains the letter for the ledger

Prerequisites before TimeXtender Infor SunSystems Data Source setup

Infor SunSystems SQL Databases

• SunSystems domain database LINK restores this as SunSystemsDomain
• SynSystems data database LINK restore this as SunSystemsData
• When both databases are up and running connect to the SunSystemsDomain database in SSMS and run the following query:

  USE [SunSystemsDomain]
  GO
  UPDATE [dbo].[DOMN_DSRCE_CONFIG] SET [DBASE_INSTANCE] = @@SERVERNAME

  To get all views to work you will need to add a Linked Server this can be done with the following script:

  USE [master]
  GO
  EXEC master.dbo.sp_addlinkedserver @server = N'SALESVM\SALESVMSUN', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc= @@SERVERNAME, @catalog=N'SunSystemsDomain'
  
  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SALESVM\SALESVMSUN',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'collation compatible', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'data access', @optvalue=N'true'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'dist', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'pub', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'rpc', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'rpc out', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'sub', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'connect timeout', @optvalue=N'0'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'collation name', @optvalue=null
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'lazy schema validation', @optvalue=N'false'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'query timeout', @optvalue=N'0'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'use remote collation', @optvalue=N'true'
  GO
  EXEC master.dbo.sp_serveroption @server=N'SALESVM\SALESVMSUN', @optname=N'remote proc transaction promotion', @optvalue=N'true'
  GO

Setting up TimeXtender Infor SunSystems Data Source in the portal

General setup

Type: Infor SunSystems

Provider: TimeXtender Infor SunSystems Data Source

Name and Description of your own choice

Connection setting

Server: the SQL Server instance name where the Sun databases are located
Database: The Sun domain database name (following the guide above this should be SunSystemsDomain)
The rest of the properties are as any other SQL Server database.

Business unit setup

Business units to include: The business units to get data from.

Example of getting data from all business units in the database from the guide above

ACM
FND
INS
PK1
PKP

Template business unit: The business unit that will be used as a template. The template business unit does not need to be in the 'Business units to include' setting.

Example: 

ACM