Skip to content
fzambroniPublic

About

No description, website, or topics provided.

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

Ortems Toolbox

Ortems Toolbox is a Windows application built with AutoIt to create, review, validate, and import demonstration data into an Ortems SQL Server database.

The tool replaces the older macro-based spreadsheet workflow with a tabbed interface for maintaining calendars, machines, operations, routings, items, BOMs, work orders, secondary resources, capacity data, and inventory movements. Once the data is prepared, the application generates a structured SQL script and can execute it directly against the connected database.

⚠️ Recommended use: demo, training, sandbox, or cloned environments. Do not run this tool against a production database without a backup, a full review of the generated SQL, and validation from the environment owner.


Overview

The Toolbox was designed to speed up the preparation of Ortems databases, especially when a planning scenario needs to be created quickly for a demo, training session, proof of concept, or functional validation.

Main goals:

  • reduce dependency on Excel macro workbooks;
  • make Ortems demo data easier to create and maintain;
  • validate references before importing data;
  • generate traceable SQL scripts;
  • support Excel workbook import/export for round-trip editing;
  • keep Work Orders in a launchable state, without pre-creating WIP/launch records.

Features

SQL Server connection

  • Connects to an Ortems SQL Server database.
  • Supports:
    • Windows Authentication;
    • SQL Server Authentication.
  • Displays the active connection string.
  • Tests the connection using ADODB.Connection.
  • Includes an Inspect Table utility to view Ortems table columns and troubleshoot schema differences.

Ortems module selection

The Modules tab defines the functional scope of the scenario:

  • PS - Production Scheduling or MP - Master Planning;
  • SRP;
  • WO Links;
  • Complex Routings;
  • Secondary Resources;
  • Batch Machines;
  • Inventory;
  • Markers;
  • Limited Resources;
  • Parameters.

Tabs are enabled or locked automatically based on the selected modules.

Data editing interface

Each data tab includes actions to:

  • add a row;
  • edit the selected row;
  • delete the selected row;
  • delete all rows from the tab;
  • import CSV;
  • export CSV;
  • load sample data.

Main tabs:

Tab Purpose Main Ortems tables involved
Calendars Calendars and working periods B_CAL, B_PERI
Machines Sites, work centers, sections, and machines B_ZONE, B_ILOT, B_SECT, B_MACH
Operations Operations and machine rates B_OPE, B_CADE
Routings Routings and phases B_GAMM, B_PHAS
Items Items, item versions, and production routings B_ART, B_VER_ART, E_GAMME_NOME when applicable
BOM Product structure B_NOME
Work Orders Planned/launchable production orders B_OF
WO Links Precedence relationships between WOs/operations B_PROF, when applicable after launching
Secondary Resources Secondary resources by operation Resource-related auxiliary tables, depending on the schema
Capacity Capacity calendars Capacity-related tables, depending on the schema
Inventory Movements Inventory transactions Inventory-related tables, depending on the schema

Excel import and export

The application can export a structured workbook with all data tabs and import that same workbook back into the tool.

Important rules:

  • do not rename the generated sheets;
  • do not change the headers;
  • data is treated as text to prevent Excel from automatically converting dates and times;
  • the Line column is regenerated by the application during import.

The exported workbook includes a README sheet with the integrity status at the time of export.

Integrity validation

Before generating or executing SQL, the Toolbox performs cross-tab validation. Examples include:

  • operations pointing to existing work centers and machines;
  • routings pointing to existing operations;
  • manufactured items pointing to valid routings;
  • BOM rows pointing to valid items, versions, and routings;
  • Work Orders pointing to valid items and routings;
  • inventory movements pointing to valid items;
  • calendar, capacity, and date format checks.

When possible, the app offers simple automatic fixes, such as reference normalization when the only issue is case, extra spaces, or a single obvious match.

SQL generation and execution

The Generate & Run SQL tab allows you to:

  • generate the full SQL script;
  • review the script before execution;
  • execute it on the connected database;
  • save the script as a .sql file;
  • review the execution log.

By default, SQL execution runs inside a transaction. If an error occurs, the app attempts to roll back the transaction to avoid leaving the database partially loaded.


Current Work Order behavior

The current version imports Work Orders as launchable orders by creating records in B_OF.

It does not pre-create records in E_OF or B_BT.

This is intentional.

In Ortems, E_OF and B_BT belong to the launch/WIP side of the model and should be created by Ortems through the native Launching process. When those records are created manually during import, Ortems may interpret the WO as already existing in WIP and return errors such as:

E_OF_EXISTS

The expected flow is:

  1. import data through the Toolbox;
  2. open Ortems;
  3. review the Work Orders;
  4. launch the Work Orders using the native Ortems launching process.

WO Links

Links between Work Orders depend on launched operational lines, usually associated with B_BT/B_PROF, depending on the Ortems version and schema.

Because the Toolbox keeps WOs in a launchable state, WO Links are skipped during the import of non-launched WOs. If the scenario requires operation-level links, create or update those links after Ortems has launched the WOs and generated the corresponding operational lines.


Clean import mode

The current version forces a global clean import mode to avoid old residual data in demo databases.

During SQL generation, the application:

  1. disables constraints on all user tables;
  2. checks whether any foreign key remains enabled;
  3. disables triggers;
  4. deletes data from all non-system user tables;
  5. validates that the tables are empty;
  6. recreates the scenario data;
  7. recreates reference data required by Work Orders, such as the default series and WIP/en-cours reference;
  8. re-enables triggers;
  9. revalidates constraints.

⚠️ This process is destructive. It was designed for demo/sandbox databases. Always create a backup before running it.


Requirements

To run

  • Windows 10 or later.
  • Access to an Ortems SQL Server database.
  • SQL Server ODBC/OLE DB provider available on the machine.
  • Sufficient database permissions to:
    • connect;
    • insert/update/delete data;
    • disable/re-enable constraints and triggers when clean import is used.
  • Microsoft Excel installed, only if workbook import/export is required.

To compile

  • AutoIt 3.
  • SciTE4AutoIt3 or AutoIt3Wrapper.
  • x64 compilation enabled.
  • Project support files stored in the same folder as the source code.

Suggested repository structure

.\Toolbox
├── Toolbox.au3
├── Updater_lib2.au3
├── Toolbox.ico
├── splash.jpg
├── Help.html
├── Updater.exe
├── FileUpdate.exe
├── README.md
└── .gitignore

Runtime-generated files, such as settings.ini, logs, exported workbooks, and generated SQL files, should not be committed.


How to compile

  1. Clone or copy the project to a local folder:

    .\Toolbox
    
  2. Open Toolbox.au3 in SciTE/AutoIt.

  3. Make sure all support files are in the same folder:

    .\Updater_lib2.au3
    .\Toolbox.ico
    .\splash.jpg
    .\Help.html
    .\Updater.exe
    .\FileUpdate.exe
    
  4. Before publishing or compiling for distribution, use relative paths in the AutoIt3Wrapper directives. Example:

    #AutoIt3Wrapper_Res_File_Add=.\Updater.exe
    #AutoIt3Wrapper_Res_File_Add=.\Help.html
    #AutoIt3Wrapper_Run_After=.\FileUpdate.exe
  5. Compile as x64.

  6. Create a release containing the final executable and, if required by the project update model, the updater support files.


How to use

1. Connect to the database

  1. Open the Toolbox.
  2. On the Database tab, enter:
    • Server / Instance;
    • Database name;
    • authentication type.
  3. Click DB Connect.
  4. Confirm that the status changed to connected.

2. Select modules

  1. Go to the Modules tab.
  2. Select PS or MP.
  3. Enable the modules required for the scenario.
  4. Click Apply / Show Summary if you want to review the selected scope.

3. Enter data

Fill the tabs in the logical order:

  1. Calendars;
  2. Machines;
  3. Operations;
  4. Routings;
  5. Items;
  6. BOM;
  7. Work Orders;
  8. optional modules, when applicable.

You can also use Load Example to create a starter dataset and then adjust it to your scenario.

4. Validate integrity

Click Integrity Check.

Fix the reported issues before generating SQL. When the app offers an auto-fix, review the result before continuing.

5. Generate SQL

Click Generate SQL.

Review the script in the Generate & Run SQL tab. This step matters because the script may delete and recreate data in the connected database.

6. Execute on the database

Click Run on DB.

Confirm execution only after verifying that the connected database is the correct one and that a backup or rebuild option is available.

7. Open Ortems

After successful execution:

  1. open Ortems;
  2. verify the imported data;
  3. launch the Work Orders using the native Ortems process.

settings.ini

The application automatically creates a settings.ini file in the same folder as the executable/source code.

Example:

[Connection]
Server=localhost\SQLEXPRESS
Database=ORTEMS_DEMO
Auth=Windows Authentication
User=

[Modules]
Mode=PS
SRP=0
WOL=0
SR=0
INV=0
MRK=0
LR=0
PRM=0
BATCH=0
CROUT=0

[SQL]
ClearFirst=1
Transaction=1
Verbose=0

[Logging]
VerboseMode=0

[Window]
X=-1
Y=-1
W=1100
H=720

Notes:

  • this file is local to each machine/user;
  • do not publish this file to GitHub;
  • passwords must not be committed;
  • for troubleshooting, enable VerboseMode=1 or turn on Verbose Mode in the interface.

Logs and troubleshooting

Logs are written to:

.\log

Use Verbose Mode when investigating:

  • connection errors;
  • missing columns in Ortems tables;
  • foreign key errors;
  • transaction rollbacks;
  • SQL generation issues;
  • clean import failures.

Verbose mode records additional details, including schema detection queries and executed commands.

E_OF_EXISTS when launching WOs

This error usually means that the database already contains launch/WIP records related to the same WO.

The current version avoids this issue by design:

  • it creates the WO in B_OF;
  • it does not create E_OF;
  • it does not create B_BT;
  • it cleans old residual data during clean import.

If the error still occurs, confirm that:

  1. you are using the current version of the application;
  2. the SQL was regenerated after the update;
  3. the import completed successfully;
  4. no external customization is recreating E_OF or B_BT before Ortems launching;
  5. the log does not show a clean import failure or transaction rollback.

Foreign key error after import

Check whether:

  • all tabs passed Integrity Check;
  • IDs are written exactly as expected;
  • the Ortems version/schema uses different column names;
  • the verbose log shows any column that could not be detected;
  • the database has specific triggers or customizations.

Excel does not open during import/export

Workbook import/export uses Microsoft Excel COM automation. Excel must be installed on the Windows machine running the application.


Best practices

  • Always use a cloned or demo database.
  • Create a backup before running Run on DB.
  • Generate and review the SQL before executing it.
  • Keep IDs simple, stable, and free from unnecessary special characters.
  • Enter data in the logical order: calendar → machine → operation → routing → item → BOM → WO.
  • Run Integrity Check before any database execution.
  • Enable Verbose Mode before reproducing an error.
  • Do not commit settings.ini, logs, customer workbooks, or exports containing sensitive data.

Suggested .gitignore

# Runtime settings
settings.ini

# Logs
log/
*.log

# Build output
*.exe
*.a3x
*.bak

# Local exports/imports
*.xlsx
*.xlsm
*.csv
*.sql

# Editor/OS files
.vscode/
.idea/
.DS_Store
Thumbs.db

If you want to publish binaries through GitHub Releases, keep .exe files out of the main repository and attach them to the release instead.


Security and privacy

Before publishing this project:

  • remove absolute paths from your machine;
  • use only relative paths, such as .\Toolbox;
  • do not publish settings.ini;
  • do not publish real customer logs;
  • do not publish Excel/CSV/SQL exports containing sensitive data;
  • review executable metadata before distribution;
  • validate that the updater does not contain tokens, private URLs, or credentials.

Known limitations

  • Windows-only application.
  • Requires SQL Server and an available ODBC/OLE DB provider.
  • Excel import/export requires Microsoft Excel to be installed.
  • The Ortems schema may vary across versions. The app attempts to detect columns dynamically, but heavily customized schemas may require code adjustments.
  • Operation-level WO Links are not applied during the import of launchable WOs. They should be handled after Ortems creates the launch lines.
  • Clean import is destructive and should not be used in production.

Suggested roadmap

Possible future improvements:

  • non-destructive mode for partial refreshes;
  • dedicated Ortems schema diagnostics screen;
  • HTML integrity report;
  • JSON import/export in addition to Excel/CSV;
  • fuller support for post-launch WO Links;
  • database impact preview before Run on DB;
  • automated tests for SQL generation by module.

License

Define the license before publishing the project.

Suggested action: add a LICENSE file to the repository root and update this section with the selected license name.


Disclaimer

This project directly manipulates Ortems SQL Server database tables. Use it carefully, always in a controlled environment, and review the generated SQL before execution.

About

No description, website, or topics provided.

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages