ERP systems contain most of the data needed to run APS including:

• Plants
• Departments
• Resources
• Resource Capabilities (sometimes called Operation Types)
• Jobs (including Manufacturing Orders, Operations, and Material requirements)

Some of this data is fairly static, such as Plants and Departments, while Jobs tend to be added, closed and changed at least daily. All of the above data can be entered manually into APS but it is generally preferred to import as much as possible so that most data maintenance is in one place. For some data elements, especially Resources, the definition in APS contains fields that do not have an ERP equivalent. In these cases the values can be left at their default values, calculated in the interface, or manually entered in APS after the object is created via the interface (or manually). If a value is omitted in the interface then manual changes to object fields will be preserved even after updating the other fields via the interface.

Note that some data is entered directly in APS and cannot be sent via interface.  This data, however, is very easy to enter.  It includes:

• Users, and
• Dispatchers (used in optimization)

Import Options

There are three options for importing data into APS:

1. Use the APS Interface Service
2. Build a custom interface using the APS API (application program interface)
3. Using the Scheduling Agent’s Custom Message Processor to import from text files.  (This is usually used for importing from bar code type transaction systems.)

The APS Interface is usually easier and faster to setup since it requires no programming. It is a batch-style, “pull” interface that is initiated from within APS. If a real-time, transactional interface is desired or if data volumes are especially high and your database’s ODBC/OLEDB/SQL interface is slow or unavailable then the APS API would be a better choice.

If you decide to build a custom interface using either the APS Interface or the APS API, the first thing to do is make an integration plan.

Connecting to a Database

APS can connect to a variety of different data sources, including SQL, Oracle, OLEDB and ODBC.

SQL: On the configuration manager for the instance, you can enter in the name of the SQL Server and the Database from which you want to draw information, and the connection string will be formatted correctly underneath.

ODBC: Leave the ‘Server Name’ and ‘Database’ Names blank and select ODBC on the left hand side. The connection string will need to be entered manually.

Oracle: APS uses the .NET Framework Data Provider for Oracle as the provider. The connection string, like ODBC, needs to be set manually. For information regarding the string format, examples can be found here.

Note: There are certain keywords that are reserved by Access, ODBC and SQL Server. When modifying database fields and columns, these keywords should be avoided to prevent syntax errors.

Configuring Database Connection

The APS.net Interface Service exchanges data with external databases using Connections– usually one Connection for each database. In most cases all of the data you need will be available in a single ERP database. In this case only one Connection is needed. Each Connection has the following properties:

• Connection Name: This is a text name for identifying the Connection. Each Connection name must be unique but can be virtually anything that you would like to call it. (Some characters are not allowed in the name and will be replaced with underscores automatically when you exit the field.)
• Connection Type:The Connection Type is an important setting. It must be set to the correct value for the Interface Service to be able to connect to your database.
  • ODBC:This stands for Open Database Connectivity. Many databases provide ODBC drivers to support interfacing. If you will be using ODBC then an ODBC System Data Source must be setup on the computer that the Interface Service is running on. The Connection will not work unless the Data Source is working. When you select the ODBC option, a list of System Data Sources will be displayed. This list is from the PC running the Interface Service, which may not be the same PC on which you are running the Interface Wizard.
  • OLE DB:A connection to any data store accessible via OLE DB. Depending on the functionality supported by the native OLE DB provider, some methods, or properties of an OLE DB Connection object may not be available. The .NET OLE DB Connection String is designed to match the standard OLE DB connection string format as closely as possible with the following exceptions. The “Provider = value ” clause is required. However, you cannot use “Provider = MSDASQL” because the .NET Framework Data Provider for OLE DB does not support the OLE DB Provider for ODBC (MSDASQL). To access ODBC data sources, use an ODBC Connection Type instead. You can use an OLED DB Connection to connect to a variety of data sources, including a Microsoft Data Link (UDL).
  • SQL Server 7.0+:A connection to a SQL Server version 7.0 or later. It is optimized for use with SQL Server 7.0 or later by (among other things) bypassing the OLE DB layer.
  • Oracle:A connection to an Oracle database.
• The database stores time spans in: This setting determines the conversion to be used when creating TimeSpans in APS.net from numeric values gotten from the external database. Since some Connection Types do not support the TimeSpan type, it is necessary to import TimeSpans as numeric values and then convert them to TimeSpans later. This field is very important because if this value is set incorrecty then all TimeSpans will be incorrect.
• Connection String:This is used by the Interface Service to establish a Connection to a data source. If it is invalid then the Connection will fail. The value required depends upon the Connection Type and the data source you are connecting to.
  • These are some ODBC example Connection Strings:
  • Driver={SQL Server};Server=MyServer;Trusted_Connection=yes;Database=Northwind;
  • Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=yes
  • DSN=dsnname
  • Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\nwind.mdb
  • Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls
  • Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin
  • These are some OLE DB example Connection Strings:
  • Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND_RW.MDB
  • Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=yes
  • Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb
  • Provider=SQLOLEDB;Data Source=MySQLServer;Integrated Security=SSPI
  • The SQL Connection String is similar to an OLE DB connection string, but is not identical. Unlike OLE DB, the Connection String that is returned is the same as the user-set ConnectionString, minus security information if the Persist Security Info value is set to false (default). The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true. This is a typical SQL Connection String:
  • Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer
  • This is a typical Oracle Connection String:
  • Data Source=Oracle8i;Integrated Security=yes