Integrity Reports
The Integrity reports system template holds a list of SQL checks that compare NomaUBL with a source system and list what is missing or wrong: print jobs that were never integrated, accounting records whose amounts differ from the e-invoices… Each check runs read-only SELECT statements — a single query, or a source query and a target query on two different databases compared in memory — and every row it returns is one anomaly. The results are shown on the Integrity tab of the Reports page, where a check can also carry an action on the selected rows.
Open it under Configuration → System → Integrity reports. On an installation that does not have the template yet, an Add integrity reports button creates it without restarting the service. The template ships empty: nothing appears on the Reports page until a check is defined.
At a glance
Global settings
| Field | Default | Description |
|---|---|---|
| Max rows | 5000 | Row cap per run. A larger result is cut and flagged result truncated at the row cap on the Reports page. |
| Timeout (s) | 120 | Statement timeout, in seconds. |
Modes
The Mode field of a check decides how its rows are produced.
| Mode | Result |
|---|---|
| Direct (default) | One query; its rows are the anomalies. For two systems in the same database. |
| Missing | Source rows whose key is absent from the target. |
| Extra | Target rows whose key is absent from the source. |
| Mismatch | Keys present on both sides whose compared columns differ. |
The three comparison modes run a source query and a target query, each on its own connector — JD Edwards on SQL Server and NomaUBL on PostgreSQL, for instance — and match their rows in memory on the key columns. Keys are normalised before matching: trailing spaces, leading zeros, decimal scale and date formats no longer get in the way.
Checks
Each check is a collapsible group — click its title to open it, × removes it, + Add check appends one.
| Field | Description |
|---|---|
| Name | Identifier shown on the Reports page (e.g. missing-pdf). Keep it stable. |
| Description | One line explaining what the check finds (e.g. PDF print jobs not integrated). |
| Start from | Optional. Pre-fills the check from a known JD Edwards comparison — see Query templates. |
| Mode | Direct, Missing, Extra or Mismatch — see Modes. |
| Connector | The database connector the SELECT runs against — the JD Edwards database, the NomaUBL database or any other connector declared on the platform. |
| Key columns | Optional. Comma-separated result columns that identify the record an action works on (e.g. JCPID, JCVERS, JCJOBNBR, or document number + type + company). |
| SQL (SELECT only) | The query. A single SELECT (or WITH) statement; anything else is refused before it reaches the database. The connection is opened read-only. |
| Action | What the Reports page can do with the selected rows: none, or Integrate BIP jobs. |
In the comparison modes, Connector, Key columns and SQL become Source connector, Source key columns and Source SQL, and further fields appear:
| Field | Description |
|---|---|
| Target connector | Database connector of the target query — it may differ from the source. |
| Target key columns | Columns of the target result matching the source key columns, in the same order. Empty = same names. |
| Target SQL | The target query (e.g. SELECT TRIM(FEWDS1) AS JOBKEY FROM F564230 WHERE FEUPMJ >= {fromJde}). The date placeholders apply to both queries. |
| Compare columns (Mismatch) | Comma-separated SOURCE=TARGET column pairs (e.g. AMOUNT_HT=UHATXA, DOC_DATE=UHDIVJ). A row is an anomaly when any pair differs. |
| Tolerance (Mismatch) | Numeric values differing by at most this amount are equal. Default 0.01. |
In Mismatch mode, the result carries the source columns, then one TARGET_<column> column per compared pair with the target value, and a DIFFERENCE column naming the columns that differ.
Date placeholders
The SQL can use the period picked on the Reports page. Placeholders are replaced before the run; without a period, the range is 1900-01-01 → today.
| Placeholder | Format | Example |
|---|---|---|
{from} / {to} | yyyy-MM-dd | 2026-09-01 |
{fromJde} / {toJde} | JDE Julian date (CYYDDD) | 126244 |
{fromDMY} / {toDMY} | dd-MM-yyyy | 01-09-2026 |
The SQL field suggests the usual shape of a check — the source records of the period that never reached the NomaUBL log (F564230):
SELECT … FROM … WHERE JCSTDTIM > TO_DATE('{fromDMY}','dd-MM-yyyy')
AND … NOT IN (SELECT TRIM(FEWDS1) FROM PRODDTA.F564230 WHERE FEUPMJ > {fromJde})
The Insert placeholder picker, under the label of each SQL field, lists every placeholder with its format and inserts it at the cursor.
BIP placeholders
These placeholders read the report list of the JDE print queue (BIP) settings.
Three more placeholders follow the BIP setup, so a check never needs a hand-kept list of reports and versions:
| Placeholder | Replaced by |
|---|---|
{bipReports} | The configured report names, quoted, for an IN list: 'R42565','R03B505'. |
{bipFilter:REPORTCOL,VERSIONCOL} | The predicate the BIP scan uses, on the named columns: ((TRIM(REPORTCOL)='R42565' AND TRIM(VERSIONCOL)='XJDE0001') OR TRIM(REPORTCOL)='R03B505'). A row set to all versions matches on the report alone. |
{bipTemplate:REPORTCOL,VERSIONCOL} | A CASE expression giving the document of each row, from the document set on each report row. |
With no report configured, the filter becomes 1=0 and the list NULL: the check returns nothing rather than every job.
Query templates
The Start from list pre-fills a check — mode, connectors, key columns, SQL and action — from a known comparison:
| Template | Check |
|---|---|
| BIP jobs not integrated (F986114 vs F564230) | The BIP print jobs of the configured reports that never reached NomaUBL, with the Integrate BIP jobs action. Uses {bipFilter} and {bipTemplate}. |
| Accounting invoices not in NomaUBL (F03B11 vs F564231) | Customer ledger invoices of the period absent from NomaUBL. |
| Sales-order invoices not in NomaUBL (F4211/F42119 vs F564231) | Invoices written by sales order update, open and history lines, absent from NomaUBL. |
| Amounts JDE vs NomaUBL (F03B11 vs F564231, mismatch) | Invoices present on both sides whose amounts differ. |
On a check that already has SQL, the editor asks for confirmation before replacing it (Replace this check?). The schema prefixes (PRODDTA, SVM920) and the status codes are the usual ones: adjust them to the JD Edwards environment, then save.
Action Integrate BIP jobs
This action extracts JD Edwards BI Publisher jobs. Checks without an action work on any source system.
When Action is set to Integrate BIP jobs, the selected rows are integrated exactly like the command fetch-single <template> bip <job> (see Command line), through the Fetch Invoices batch. Five fields map the result columns:
| Field | Description |
|---|---|
| Job column | Result column holding the JDE job number (RJJOBNBR). Required. |
| Template column | Result column holding the document name. Leave empty to use the fixed template. |
| Fixed template | Document used when the template column is empty or missing. |
| Host column | Result column holding the JDE execution host — only on JD Edwards installations with several hosts. Optional. |
| Processing type | AUTO (default), SINGLE, BURST, UBL or BOTH. |
On the Reports page
- Pick a check and a period (last 30 days by default), then Run. Show SQL displays the query of the check.
- The header line recalls the connector, the key columns and the configured action. On a comparison check, a second line adds the mode, the target connector and, for Mismatch, the compared columns with the tolerance; Show SQL then displays both queries, and the count reads N source / N target. The result reads No anomaly or N anomaly(ies), every row being one anomaly in the standard grid — search, filters, column chooser and export.
- When the check carries an action, tick the rows and click Integrate selection (N). A confirmation recalls that the jobs are extracted and processed like a normal run — validation, storage and sending follow the template settings.
- After the action, the check runs again: integrated rows drop out of the result, and a summary banner shows N integrated, N failed out of N, with the name of every failed job.
API
| Endpoint | Purpose |
|---|---|
GET /api/reports/integrity | Lists the configured checks (name, description, connector, key columns, SQL). |
POST /api/reports/integrity/run | Runs one check — body { "name", "dateFrom", "dateTo" } — and returns the result columns and rows. |
POST /api/reports/integrity/action | Runs the check's action on the selected rows — body { "name", "jobs": [...], "templates": [...], "hosts": [...] } — and returns the per-job result and the summary. |
See the API reference for the full schemas.
Tips & best practices
- One question per check. A check that answers a single question (jobs not integrated, amounts that differ) gives a result that can be acted on row by row.
- Return the key columns. Even without an action, key columns make each anomaly easy to trace back to its source record.
- Bound the query with the period. The date placeholders keep the query on the window the user picked and well under the timeout.
- Point the action at the job number, not a label. The job column must contain the JDE job number; rows without one are ignored by Integrate selection.