UBW Integration

Integration middleware connecting Kofax KTM document capture with Unit4 UBW ERP system, built with Apache Camel.

Technology Stack: Apache Camel 2.22.1 | Groovy | Gradle

---

Overview

UBW Integration provides two core capabilities:

1. SQL Bridge Emulation - REST API that emulates SQL Server's SQLBridge component, enabling vendor lookups and invoice validation queries against UBW ERP
2. Document Conversion - Monitors folders for Kofax-exported invoices and converts them to OIOUBL format (Danish e-invoice standard)

The application supports multi-company deployments with separate preprod and production environments.

---

Features

• REST endpoint for vendor/invoice queries (SQLBridge compatible)
• File system monitoring with scheduled polling
• XML to OIOUBL transformation via XSLT
• PDF embedding as Base64 in output documents
• Multi-tenant configuration (environment + company)
• Automatic cleanup of processed files
• Metrics collection for monitoring

---

Quick Start

Prerequisites

• Java 8 or higher
• Gradle 4.6+
• Access to UBW ERP endpoints

Installation

# Clone the repository
git clone git@bitbucket.org:bpa-solutions-a-s/ubwintegration.git
cd ubwintegration

# Build the distribution
./gradlew distZip

Configuration

1. Copy the environment template:

   cp .env.example .env

2. Edit .env with your UBW credentials:

   UBW_PREPROD_USERNAME=your_preprod_username
   UBW_PREPROD_PASSWORD=your_preprod_password
   UBW_PROD_USERNAME=your_prod_username
   UBW_PROD_PASSWORD=your_prod_password

3. Configure camel.properties for your environment (endpoints, folders, schedules)

Running

# Development
./gradlew run

# Production (from distribution)
./bin/ubwintegration

---

REST API

The SQL Bridge endpoint emulates database queries for Kofax integration.

Base URL: http://localhost:8088/sqlbridge/dataconnect/selectAction

Query Parameters

Parameter	Description	Example
config	Environment and company	preprod.A1, prod.A1
key	Query type	cvrpicklist, vendorinvoiceno
searchfor	Search criteria	Oden, 12345678
debug	Enable debug output	true

Available Queries

Key	Description	Template ID
cvrpicklist	Search vendors by VAT ID/name	1118
cvrvalidate	Validate vendor VAT ID	1119
picklistvendorno	Validate vendor number	1119
vendorinvoiceno	Get last invoice for vendor	1121
vendorinvoicenoexist	Check if invoice exists	1120
picklistorderno	Search work orders	1122
workorderpicklist	Get work order details	1122

Example Request

curl "http://localhost:8088/sqlbridge/dataconnect/selectAction?key=cvrpicklist&config=preprod.A1&searchfor=Oden"

Response Format

RESULTSET|apar_id|vat_reg_no|ou1|apar_name||place|currency|apar_gr_id|FIK|bank_account|
NULL

---

Apache Camel Routes

SqlBridgeRoute

HTTP endpoint that processes vendor/invoice queries:

HTTP Request → Validate → Load Config → Render SOAP Template → POST to UBW → Parse Response → Format Output

• Endpoint: jetty:http://0.0.0.0:8088/sqlbridge/dataconnect/selectAction
• Route ID: SqlBridge
• Templates: Velocity templates in vm/ directory

KofaxFileToOioUblRoute

File watcher that converts Kofax exports to OIOUBL:

Poll fromFolder → Read XML → Extract PDF → Base64 Encode → XSLT Transform → Write to toFolder → Backup

• Route ID: FromFolder
• Schedule: Every 5 seconds, 6 AM - 6 PM, Monday-Friday
• XSLT: config/XML2OIOUBL.xsl

Cleanup Route

Scheduled cleanup of old processed files:

• Route ID: cleanupFolder
• Schedule: 6 PM daily on weekdays
• Retention: 30 days (configurable)

---

Configuration

camel.properties

# Application
application.name=UBW Integration Multicompany

# Scheduling (Quartz cron format)
schedule=0/5+*+06-18+?+*+MON-FRI
cleanupschedule=0+0+18+?+*+MON-FRI
cleanupdays=30

# Folders
fromFolder=fromFolder
toFolder=toFolder
toBackupFolder=toBackupFolder
toOioStylesheet=config/XML2OIOUBL.xsl

# Environment configuration (preprod/prod)
preprod.A1.username=${UBW_PREPROD_USERNAME}
preprod.A1.password=${UBW_PREPROD_PASSWORD}
preprod.A1.client=A1
preprod.A1.ubwendpoint=https://mth-preprod.unit4cloud.com/dk_mth_preprod_webservices/service.svc

prod.A1.username=${UBW_PROD_USERNAME}
prod.A1.password=${UBW_PROD_PASSWORD}
prod.A1.client=A1
prod.A1.ubwendpoint=https://mth.unit4cloud.com/dk_mth_live_webservices/service.svc

Directory Structure

The application monitors a hierarchical folder structure:

fromFolder/
├── preprod/
│   └── A1/
│       ├── invoice1.xml
│       └── invoice1.pdf
└── prod/
    └── A1/
        ├── invoice2.xml
        └── invoice2.pdf

---

Build & Development

Gradle Commands

Command	Description
./gradlew build	Compile and package
./gradlew run	Run the application
./gradlew distZip	Create distribution ZIP
./gradlew distTar	Create distribution TAR
./gradlew clean	Clean build artifacts

Project Structure

ubwintegration/
├── src/
│   ├── main/groovy/
│   │   ├── Start.groovy                 # Entry point
│   │   └── dk/bpas/mth/ubwintegration/
│   │       ├── routes/
│   │       │   ├── SqlBridgeRoute.groovy
│   │       │   └── KofaxFileToOioUblRoute.groovy
│   │       ├── dataformat/
│   │       │   └── AgressoXmlDataFormat.groovy
│   │       └── utilities/
│   │           ├── GetProperties.groovy
│   │           ├── HttpClientProcessor.groovy
│   │           ├── PdfUtilities.groovy
│   │           └── CleanFolders.groovy
│   ├── main/resources/
│   │   └── logback.xml
│   └── dist/
│       ├── camel.properties
│       ├── config/XML2OIOUBL.xsl
│       └── vm/*.vm                      # Velocity templates
├── build.gradle
├── .env.example
└── README.md

Key Components

Component	Purpose
Start.groovy	Bootstraps Camel context and routes
SqlBridgeRoute	REST endpoint for UBW queries
KofaxFileToOioUblRoute	File monitoring and OIOUBL conversion
AgressoXmlDataFormat	Parses UBW SOAP responses
HttpClientProcessor	Sends SOAP requests to UBW
GetProperties	Loads multi-tenant configuration

---

Deployment

Distribution Package

./gradlew distZip
# Output: build/distributions/UbwIntegration-1.0.20.zip

Windows Service

The distribution includes NSSM for Windows service installation:

nssm install UbwIntegration "C:\path\to\bin\ubwintegration.bat"
nssm start UbwIntegration

Production Checklist

• Configure environment variables for credentials
• Update camel.properties with production endpoints
• Verify folder permissions for fromFolder/toFolder
• Configure log rotation if needed
• Set up monitoring for the REST endpoint

---

Logging

Logs are written to the logs/ directory:

File	Content
info.log	All DEBUG and above messages
warning.log	WARN and ERROR messages only

Logs rotate daily with 30-day retention. Configuration: src/main/resources/logback.xml

---

Architecture

┌─────────────┐     ┌──────────────────┐     ┌─────────────┐
│  Kofax KTM  │────▶│  UBW Integration │────▶│  UBW ERP    │
│  (XML+PDF)  │     │  (Apache Camel)  │     │  (Unit4)    │
└─────────────┘     └──────────────────┘     └─────────────┘
                            │
                            ▼
                    ┌──────────────────┐
                    │   OIOUBL Files   │
                    │   (toFolder)     │
                    └──────────────────┘

┌─────────────┐     ┌──────────────────┐     ┌─────────────┐
│  WWWSearch  │────▶│  SQL Bridge API  │────▶│  UBW ERP    │
│  (HTTP)     │     │  (REST/Jetty)    │     │  (SOAP)     │
└─────────────┘     └──────────────────┘     └─────────────┘

---

License

This software is proprietary and confidential. Unauthorized copying, distribution, or use is strictly prohibited.

Copyright (c) BPA Solutions A/S. All rights reserved.