JobLink-SQL / Custom ERP Integration
A standard part of creating a 'Custom Import', is to match the fields for Work Orders (shop orders, factory orders) in the source database system with the equivalent fields in the JobTime system. Besides the field-name matching, we also will verify the data types, field widths, and occasionally convert data where required, in order that your data is imported to function as expected inside JobTime. We strongly suggest that the client work with us, and use the SQL Server Integration Services (SSIS), a feature provided in SQL Server to link SQL Server with any other SQL Server or other ODBC compliant database connection. From another SQL database the integration can be accomplished within a few days, and for other systems it may take a few weeks. Once data starts to flow, follow-up adjustments will be made which will vary depending on the client's needs for creating a tailored import.
Our JobLink-SQL process includes a standard battery of validation tests, to help you see where your host database may be following conventions other than those required in the JobTime database. Our validation tests check and isolate records into 'quarantine tables', which can be displayed in a validation errors report to let you take corrective action in your host data system, or to adjust specifications on how the import should handle your data.
Typically, the entire process of our JobLink-SQL project result in a thorough (and often long postponed) database cleanup issues, which will result in greatly improved data consistency and increased usability of your host data system. For example, we find cases where work orders might have been entered into your ERP system with missing or even illogical due-dates, such at 20 years in the past or future. These are often due to inadvertent data entry, which the host ERP system was not programmed to validate.
Other consistency issues occur if your ERP sends JobTime a work-order with missing work order number, zero quantity, any operation-steps which are not assigned to a valid work-center, or any operations that require secondary resources which are not yet defined. JobTime supports a high level of consistent referential integrity in its database, in order to be able to schedule accurately. As you might expect, JobTime5's database internal consistency requirements need to be stronger on the data which impact scheduling needs than most ERP systems typically require.
The time required to implement the Custom Import is caused mostly by data cleanup issues in the client's Host ERP system. But in additional cases, the client may finds that some desired JobTime scheduling features require data which the ERP might not contain. In these cases, we tailor a 'default populating rule' to generate data consistent with your current business rules. Each client import generally has one or more unique requirements, which make your JobTime-ERP import unique, customized to your own company.
In some cases, if we discover that an specific data table is needed in the import, in order to correctly populate the JobTime Scheduling fields, then you might also request that we create a utility tool to help you maintain that new data table in your Custom Import Database.
- Once you deposit your data in JobTime's Import Database, and it is sufficiently well validated to your satisfaction, then we add your required specifications for custom-data populating logic, or even new data tables that are unique to your local manufacturing technologies. These steps assure us that your data will flow automatically and be compatible with the JobTime SQL database, and will create logical schedules that make sense to the shop.
- To avoid a prolonged development effort, it is essential to follow standard software development procedures, and therefore, we must initially do a preliminary analysis of all your known data requirements, and we mutually write specifications and sign off before detailed development of your Custom Import is started. The development steps to be followed are known by both our JobTime staff and our own scheduling and IT staff. The specification helps all parties know how much custom work will be included in your Custom Import project, and to track its progress.
- During the nearly 28 years JobTime has been creating custom ERP imports, we have found that it is typical for each client may need about a half-dozen specific customized import tasks. This is due to the uniqueness of every manufacturing company, local policies and your local ERP's database.
Some of our clients like to use JobTime to make changes in the schedule, by means of our visual Schedule Board. Users can also make changes in JobTime like changing work-order due-dates or priorities. When a new snapshot of your data is downloaded from your host system, you will want these user-defined changes to be preserved, so they don't have to be re-entered after the next import. Therefore, the client needs to specify exactly which data changes they make in JobTime need to be preserved from one download to the next. This can be an additional implementation requirement which further customizes your local JobTime import.
All of the steps mentioned above, will indicate why the 'JobLink-SQL Custom Import' requires more time than simply 1 or 2 days to line up data fields and create a data-flow. The initial data connection step is the easiest part of the Custom Import project. In some cases it can even take just a few hours to see the first data flowing from your ERP system into the JobTime Import database. But it clearly takes additional time, to deal with customer-specific requirements. We have seen extremely simply import projects, when the client has exceptionally strong SQL programmers in-house, and only take a single week of effort. Other projects, where the client does not have an in-house IT department, may require about 4 man-weeks of effort, but due to coordination with an external client-IT consultant may extend the required time for implementation.
- JobLink-SQL lets you maintain routings in either your ERP or in JobTime.
- Your custom import will normally NOT be impacted by upgrading to the latest version of JobTime5. The architecture of the JobLink-SQL design, specifically allows you to stay up-to-date with the latest version of JobTime5, which we strongly advise you to follow. We strive to restrict all company-unique modifications to your custom Import Database. The Standard JobTime5 Database is not modified for the sake of the import. For this reason, you can have unlimited customizations of your import system, but at the same time you can safely and confidently install new builds of JobTime5 as they are released. This allows our clients to 'have their cake and eat it too', in the sense that they enjoy the stability benefits of a standard out-of-the-box scheduling solution, but also gain unique tailoring via their 'Custom Import'.
- We designed our JobLink-SQL Custom Import technique, to give you both import flexibility and safe upgrade stability. This kind of import flexibility is typically found only in major ERP systems with a 7-digit price tag. But you will pay far less than this for JobTime5 APS system, even including your customized JobTime-SQL Import system.
- Advanced Levels of JobTime5 let you progressively add and expand your Custom Import, to assure you that your finite capacity shop schedule will continue to be consistent with your ERP Master Schedule.
- For example, in Level5, your JobLink-SQL custom import can be extended to import your material pegging allocations of the sources and uses of materials, to assure you that these links will be fully recognized in your JobTime5 APS scheduling, so that you have both detailed capacity and detailed material availability considered in your scheduling system.
- Level5 helps improve the Master Scheduling of your material consumption, because we recognize materials inputs used in each operation step, on the import of your current ERP pegging file. All material allocations, the sources and uses, are recognized. This will synchronize your material constraints to your capacity constraints, giving you full 'Advanced Planning and Scheduling' capabilities.
- As another example, in Level5, our powerful APS capability supports better long-term capacity and material planning, recognizing forecasts, and long-term blanket purchase orders. It also gives you the ability to revise short-range material delivery dates which will be synchronized to realistic shop order start-dates. Material usage can even be tied to specific operations within orders. When you see our profiles of materials consumption over time, for any given material inventory buy-item, then you will get even better control over your material master Scheduling, that the capabilities provided by your ERP system. This is one of the key benefits of JobTime5-Level4.