The principle behind this integration is that on a push of a button in the ROB-EX client, SQL calls are executed on the ROB-EX client towards the database of the ERP system. The SQL itself can be modified from application to application, but ready made templates are available to get up and running with a minimal effort.
The direct SQL integration supports import of
- Resources and resource calendars
- Master materials including opening stock, customer requirements and planned raw material purchases
- Projects
- Production orders
- Operations
- BOM’s
The direct SQL integration out of the box support export of
- Updated operation values: planned start/end times, selected resource, setup time, workload, queue times and more.
Standard connectors
Ready made Direct SQL connectors are available for the following ERP systems. Please contact us for more information.
- Microsoft Dynamics 365FO
- Microsoft Dynamics 365 Business Central
- Microsoft Dynamics AX
- Microsoft Dynamics NAV
- Microsoft Dynamics XAL
- Microsoft Dynamics C5
- Jeeves ERP
Step by step getting started guide
This is a step by step instruction on how to get started using the Direct SQL interface, in case you want to build your own Direct SQL connector.
| 1. | License | Make sure the Direct SQL integration option is enabled in your ROB-EX license. From the client select menu “Help->Register…” and make sure the option “Direct SQL” (in older versions “SQL XAL Project”) is licensed |
| 2. | Choose and copy template configuration file | Settings and SQL statements being executed when the Direct SQL integration runs must be placed in a text file located and named “\custom\xalprjsql.properties”. Several ready to use configuration files are available for different standard ERP systems like Dynamics NAV, Dynamics AX, Baan etc. To get to these files copy \plugins\erp\erp.jar into erp.zip and open erp.zip with any zip-program (e.g. 7-zip). In the zip-file navigate to “erp\xalprj\config” directory and copy the file “nav.sql.xalprjsql.properties” (or if applicable any of the other versions) into “\custom\xalprjsql.properties”. It can be recommended to initially copy all template xalprjsql files to the custom directory so they are readily available as reference material, however make sure to name the file you choose to use “xalprjsql.properties”. For new ERP systems use nav.sql.xalprjsql.properties as the starting base, since it has the most generic configuration. |
| 3. | Configure query reading available data area id’s (companies) | Most ERP systems will support multiple companies (data area id’s) in the same database. Open xalprjsql.properties in a text editor and search for query “getDataAreas”. Edit the query to correctly return the id and name of available companies, as it will be used in the next step. If the ERP system does not support multiple companies create a query that will not return any rows (i.e. select null as id, null as name from someTable where xx=‘neverFound’). Always validate the query in e.g. SQL Management Studio or similar, to make sure it will execute as expected. |
| 4. | Start ROB-EX client | Notice that the ROB-EX client must be restarted whenever a change has been made to xalprjsql.properties. |
| 5. | Create and configure database import connection | In this step a database connection to the database of the ERP system is configured (can be read-only if required). From menu “Functions->Settings->Direct SQL” select tab “Connection”. On the “Import data source profile” setting select the “DEFAULT_DirectSQL”. Click the “Show profiles” button and edit the “DEFAULT_DirectSQL” row. Select the appropriate JDBC driver (for MS SQL select the jTDS native JDBC driver) and fill in missing data for e.g. , database name (defaults to “GanttERP”) and optional instance name (is default blank). Also enter correct login and password. Click the “Test connection” to validate that a connection can be successfully established. Finally click “Ok” and “Close”. |
| 6. | Disable export | To be able to Initially focus on the import it is recommended to disable export from ROB-EX by selecting the “Macro” tab and for the “Manual” macro double click the “ProjectDBSync” macro item. For parameters “ExportAfterImport” and “ExportBeforeImport” enter “false” and click the “Ok” button to close the Macro line parameters dialog. |
| 7. | Select data area | On the “Import” tab now select from the drop down the correct “Data area”. The drop down will contain the list of companies returned by the query edited in previous step 3. The data area selected here will be the last parameter used in all sub-sequent SQL queries the Direct SQL module will call. Notice you may have to restart your ROB-EX client at this point to make sure the companies drop down has been refreshed. If data areas are not supported by the ERP system keep the default selection to “none” |
| 8. | Edit custom/xalprjsql.properties and test | Now start to edit the custom/xalprjsql.properties in a text editor, making the modifications necessary to translate SQL queries into the database format of the ERP database. When the Direct SQL runs it will on import execute queries in the sequence shown in the NAV example below. Read the section below regarding syntax of property files for general rules about how to edit a property file. As an example start out looking at query for key “getTouchedProjects2”. If projects are not imported leave this query blank. Do not remove the line entirely, simply just write “getTouchedProjects2=”. Note initially import of shift calendars can be disabled with the xalprjsql.properties setting settings.importCalendars=false This way queries getComplexCalendar, getSimpleCalendar, getSimpleCalendar2,getWeekPlan, getDayPeriod, getDayPeriod1, getDayPeriod2,getDayCalendar and getDayCalendar2 can initially be ignored and focus can be put on import of process order related data. |
An existing example
The following shows for the default NAV SQL implementation in what sequence the SQL call are made and the default NAV query used. It may look over whelming, but notice that a lot of the calls simply returns empty rows.
When creating a new Direct SQL connector focus on the queries in this sequence. I.e. make sure that each of the following queries will run and return the expected columns, for the ERP system being developed.
In order to keep the overview, descriptions for each property key has not been included in this table. A description is availabe in the “nav.sql.xalprjsql.properties” file above each of the keys.
| Property Key | Comments | Default NAV implementation of SQL query |
|---|---|---|
| getTimeLastImport1 | select CURRENT_TIMESTAMP | |
| selectNowTimestamp | select CURRENT_TIMESTAMP | |
| getTouchedProjects | ||
| getTouchedProjects2 | select distinct sh.No_ as projId, sh.No_ as projNumber, sh.[Your Reference] as projName, sh.[Sell-to Customer Name] as customer, sh.[Posting Date] as projStartCal, sh.[Shipment Date] as projEndCal, 40 as projStatus, null as color, sh.[Salesperson Code] as projectMgr, sh.[Sell-to Contact] as resp1, null as resp2, sh.[Sell-to Customer No_] as customerId, 1 as fixedPrice from [Production Order] po, [Sales Header] sh where (po.Status=2 OR po.Status=3) and po.[Source No_]=sh.No_ and ? IS NOT NULL | |
| getComplexCalendar | select DISTINCT a.No_ as id, a.No_ as name from [Work Center] a UNION select distinct b.No_ as id, b.No_ as name from [Machine Center] b WHERE b.Blocked=0 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getSimpleCalendar | select DISTINCT a.No_ + ‘-W’ as id, ‘1’ as type, a.No_ as compCalId from [Work Center] a UNION select DISTINCT b.No_ + ‘-W’ as id, ‘1’ as type, b.No_ as compCalId from [Machine Center] b WHERE b.Blocked=0 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getSimpleCalendar2 | ||
| getWeekPlan | SELECT 0 FROM [Capacity Unit of Measure] WHERE 1=2 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getDayPeriod | SELECT 0 FROM [Capacity Unit of Measure] WHERE 1=2 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getDayPeriod1 | ||
| getDayPeriod2 | ||
| getDayCalendar | SELECT a.No_ + ‘-W’ as simpCalId, a.Date as startDate, CONVERT,a.[Starting Time],8) as startTime, a.[Capacity (Total)] / a.Capacity as hourCapacity, 1 as available, cpUnit.Type as unitOfMeasure FROM [Calendar Entry] a, [Work Center] m, [Capacity Unit of Measure] cpUnit WHERE a.[Work Center No_]=m.No_ AND m.[Unit of Measure Code]=cpUnit.Code AND a.Date >= ? AND a.Date <= ? AND ? IS NOT NULL | |
| getDayCalendar2 | ||
| getTouchedResourceIds | ||
| getResourceGroups | SELECT LTRIM+’-W’ as id, a.Name as name, ‘group’ as type, a.Efficiency as effPct, 0 as infiniteCapacity, null as resGroupId, LTRIM as genCalId, null as genCalName, 1 as capacity, null as shopFloorLoginName, LTRIM as description, null as resTeamid, LTRIM as resourceTypeId, a.Capacity as avFactor FROM [Work Center] a | |
| getResourceGroups2 | ||
| getTouchedResources | SELECT LTRIM as id, a.Name as name, ‘normal’ as type, a.Efficiency as effPct, 0 as infiniteCapacity, LTRIM+’-W’ as resGroupId, LTRIM as genCalId, NULL as genCalName, 1 as capacity, null as shopFloorLoginName, LTRIM as description, null as resTeamid, LTRIM as resourceTypeId, a.Capacity as avFactor FROM [Machine Center] a, [Work Center] b WHERE a.[Work Center No_]=b.No_ AND a.Blocked=0 AND ? is not null | |
| getTouchedResources2 | ||
| getResourceSimpleCalendar | SELECT 0 FROM [Capacity Unit of Measure] WHERE 1=2 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getResourceSpecificCalendar | SELECT 0 FROM [Capacity Unit of Measure] WHERE 1=2 AND ? IS NOT NULL AND ? IS NOT NULL AND ? IS NOT NULL | |
| getTouchedProjects | ||
| isOperationForProjectAvailable | SELECT distinct 1 FROM [Capacity Unit of Measure] WHERE ? IS NOT NULL | |
| getOperationsByProject | SELECT LTRIM as oprId, rl.[Routing Reference No_] as oprId2, LTRIM as oprId3, 0 as useCapFromRes, LTRIM+’-’+cast(pl.[Line No_] as VARCHAR) as prodorderid, rl.Type as resGrpOrNormal, LTRIM as resId, LTRIM as name, rl.[Run Time] as capa, cpUnit.Type as capaUnitOfMeasure, 0.0 as targetcapa, 1.0 as quantityFactor, 1 as quantityFactorDisplayedReciproc, LTRIM as label, rl.[Routing Status] as state, -2 as type, 1 as opr_plan, rl.[Starting Date] as startCalDate, CONVERT,rl.[Starting Time],8) as startCalTime, null as endCalDate, null as endCalTime, 0 as prevQueueTime, 3 as prevQTUnitOfM, rl.[Setup Time] as setUp, setupUnit.Type as setupTUnitOfM, 1 as wlType, 0 as wl, 0 as switchOverTime, 2 as switchOTUnitOfM, rl.[Wait Time] as postQueueTime, waitUnit.Type as waitUnitOfM, rl.[Move Time] as transportTime, moveUnit.Type as moveTUnitOfM, 0 as quantity, rl.[Send-Ahead Quantity] as overlapCount, rl.[Concurrent Capacities] * 100 as effPct, 0 as quantFin, null as descr, null as templateOprId, null as parentOprId, null as materialCalendar, null as deliveryCalendar, null as realStartCal, null as lastStartCal, null as realEndCal2, 0 as accSetupHours, ‘quantity’ as accValueType, pl.[Finished Quantity] as accValue, null as accProcPct, null as altResList, null as groupId, 0 as linktype, null as anchor, 0 as locked, null as custText1, null as custText2, null as custText3, null as custText4, 0 as wasteFactor, null as rawMatId, null as rawMatName, null as rawMatNumber, 0 as rawMatType, null as rawMatUnitClass, null as rawMatUnitFactor, null as rawMatColor, null as rawMatProdInt, null as rawMatState, null as rawMatDesc, null as rawMatCustomText1, null as accId, null as accMatId, 1 as accTransType, 202 as accMethod, 301 as accStrategy, 0 as accQty, null as accUnitFactor, ‘’ accWhId ,rl.[Next Operation No_] as nextOprNo ,rl.[Previous Operation No_] as prevOprNo FROM [Prod_ Order Routing Line] rl, [Prod_ Order Line] pl , [Capacity Unit of Measure] cpUnit, [Capacity Unit of Measure] setupUnit , [Capacity Unit of Measure] waitUnit , [Capacity Unit of Measure] moveUnit where rl.[Prod_ Order No_]=pl.[Prod_ Order No_] AND (pl.Status = 2 OR pl.Status = 3) AND rl.[Routing Reference No_]=pl.[Line No_] AND rl.[Run Time Unit of Meas_ Code]=cpUnit.Code AND rl.[Setup Time Unit of Meas_ Code]=setupUnit.Code AND rl.[Wait Time Unit of Meas_ Code]=waitUnit.Code AND rl.[Move Time Unit of Meas_ Code]=moveUnit.Code AND ? is not null | |
| getOperationsByProject2 | ||
| getProgressByProject | SELECT DISTINCT ‘’ as id, ‘’ as name FROM [Capacity Unit of Measure] where Type=-100 AND ? IS NOT NULL | |
| getProgressByProject2 | ||
| getProductionOrderListByProjectId | SELECT LTRIM+‘ |
|
| getProductionOrderListByProjectId2 | ||
| getProducingAndConsumingFromOrder | Repeatedly called once for each order imported | select distinct pl.[Item No_] as fgItem, pl.Description as fgItemName, null as fgItemDescr, pl.[Unit of Measure Code] as fgUnitOfMeasure, 1 as fgUnitFactor, pl.[Production BOM Version Code] as fgVersion, it.[Inventory Posting Group] as fgType, null as fgColor, null as fgProductFamilyId, 0 as fgProcurementType, 206 as fgAccessMethod, 301 as fgQuantityStrategy, case when len(ltrim(isnull(pl.[Location Code], ‘’)))=0 then null else pl.[Location Code] end as fgWarehouse, pbl.[Item No_] as compItemNo, pbl.[Line No_] as compItemLineNo, pbl.Description as compName, null as compDescr, pbl.Quantity as compQuantity, pbl.[Unit of Measure Code] as compUnitOfMeasure, 1 as compUnitFactor, compIt.[Inventory Posting Group] as compType, null as compColor, null as compProductFamilyId, 0 as compProcurementType, 202 as compAccessMethod, 301 as compQuantityStrategy, pbl.[Scrap %] / 100 as scrapFactor, case when len(ltrim(isnull(pbl.[Location Code], ‘’)))=0 then null else pbl.[Location Code] end as compWarehouse from [Prod_ Order Component] pbl, [Prod_ Order Line] pl, [Item] it, [Item] compIt where pbl.[Prod_ Order No_]=? AND pbl.[Prod_ Order Line No_]=? AND pbl.[Item No_] is not null AND (pbl.[Routing Link Code] is null OR pbl.[Routing Link Code]=’‘) AND pbl.[Prod_ Order No_]=pl.[Prod_ Order No_] AND pbl.[Prod_ Order Line No_]=pl.[Line No_] AND pl.[Item No_]=it.No_ AND pbl.[Item No_]=compIt.No_ order by pl.[Item No_], pbl.[Line No_], pbl.[Item No_] |
| getProgressFromOrder | Repeatedly called once for each order imported | select LTRIM+‘ |
| getProdRefByOrderId | Repeatedly called once for each order imported | SELECT 0 FROM [Capacity Unit of Measure] WHERE 1=2 AND ? IS NOT NULL |
General syntax rules for editing property files
Use a text editor with syntax highlighting for property files, i.e. Notepad++ or the like.
Comments can be inserted in the configuration file by starting the line with a hash (‘#’) sign, see below.
# This is a commentA setting consists of a key, the ‘=’ character followed by the value.
key=valueThe key must be unique within all keys of the file. Only keys that are “known” to ROB-EX are significant – unknowns keys will never be read by ROB-EX.
The value can, to provide better overview, be divided across several lines by ending the line with ‘ \’ (space + backslash). So as an example:
myKey=select id, name from machine_centercan also be written as
myKey=select \
id, \
name \
from machine_center
Post your comment on this topic.