ZUGFeRD and XRechnung on IBM i (AS/400): E-Invoicing from RPG and SQL with One API Call

This guide adds ZUGFeRD and XRechnung to IBM i, also known as AS/400 or iSeries. The code is Db2 for i SQL and free-format SQLRPGLE. It reads your own invoice files, sends one JSON request to a REST API and gets back the finished e-invoice. Everything runs on the IBM i. No Java, no PC in between, no extra product. The email section shows how the API sends the invoice to your customer. The import section shows how to check supplier invoices and stage their data for your accounts payable. The SAP ABAP guide shows the same pattern inside SAP.

Why IBM i needs ZUGFeRD now

German companies that invoice from IBM i must send and receive structured e-invoices. Every German business must accept e-invoices since January 2025. Businesses with more than EUR 800,000 turnover in the previous year must send them from January 2027. Every other business must follow in January 2028. Dates and formats for each group are on the Germany mandate page.

A PDF from a spooled file is not enough. Many IBM i shops turn the invoice spooled file into a PDF. IBM Transform Services and Infoprint Server are common tools for this. That PDF is only an image of the invoice. The law asks for structured data that software can read. A ZUGFeRD invoice is a PDF/A-3 file that carries this data as embedded XML. Your customer can open it like any PDF, and their software can read it.

The RPG code does not change when the formats change. New versions of ZUGFeRD, XRechnung and the EN 16931 rules appear from time to time. The API switches to each one on the day it takes effect. Your program sends the same JSON before and after. No recompile, no new release, no retest of the formats.

What you need on IBM i

• IBM i 7.4 or 7.5 with a current Db2 PTF group. The guide uses the BLOB versions of the QSYS2 HTTP functions. They need SF99704 Level 25 on 7.4 or SF99950 Level 4 on 7.5. Later releases include them. See releases and PTFs.
• The SQL precompiler for CRTSQLRPGI. It is part of the licensed program 5770-ST1 (Db2 Query Manager and SQL Development Kit).
• A tool to run SQL scripts: Run SQL Scripts in IBM i Access Client Solutions (ACS), or RUNSQLSTM.
• HTTPS to api.invoicexml.com on port 443, with its certificate authority in the *SYSTEM certificate store.
• Three IFS folders, for example /invxml/out, /invxml/inbox and /invxml/reports.
• An InvoiceXML account. Create one. You get an API key and 100 free credits. No credit card is needed.

How the integration works

An invoice is final in your system. An RPG program calls an SQL function that reads INVHDR, INVLIN and CUSTMAST and returns the invoice as JSON. The program posts the JSON to one endpoint. The API adds up the lines and the VAT, writes the CII XML and puts it into a PDF/A-3 file. It checks the result against the rules and sends the PDF back. The program saves the PDF in the IFS. Or the API emails it to the customer instead. Every invoice is one HTTP request.

All endpoints take the same JSON body. You create these objects in a new schema called INVXML:

• Table INVXMLCFG: the API key, the base URL and your company data as the seller.
• SQL functions API_URL, HTTP_OPTIONS, UTF8 and MULTIPART: the HTTP helpers.
• SQL function INVOICE_JSON: one invoice as JSON, built from your files.
• Program INVZUGF (SQLRPGLE): creates the e-invoice and writes it to the IFS.
• Program INVINBND (SQLRPGLE) and procedure STAGE_INVOICE: check, report and stage incoming invoices.

Everything else is standard IBM i: the QSYS2 HTTP functions, JSON_OBJECT, JSON_TABLE, GET_BLOB_FROM_FILE, QSYS2.IFS_WRITE_BINARY, data queues, the job scheduler and SNDSMTPEMM. The file and field names INVHDR, INVLIN and CUSTMAST in library APPLIB are examples. Replace them with your own.

Step 1: Store the API key and the HTTP helpers

Store the key in a table that only the integration user can read. Run the script once with Run SQL Scripts. CREATE SCHEMA also creates a journal. The tables need it, because the import program runs under commitment control.

create schema invxml;

create table invxml.invxmlcfg (
  api_key        varchar(200) not null,
  base_url       varchar(100) not null default 'https://api.invoicexml.com',
  from_email     varchar(254) not null default '',
  seller_name    varchar(100) not null,
  seller_vat     varchar(20)  not null,
  seller_street  varchar(100) not null,
  seller_city    varchar(50)  not null,
  seller_zip     varchar(10)  not null,
  seller_country char(2)      not null default 'DE',
  seller_iban    varchar(34)  not null,
  contact_name   varchar(100) not null default '',
  contact_phone  varchar(30)  not null default '',
  contact_email  varchar(254) not null default '');

-- One row per invoice sent to the API
create table invxml.invxmllog (
  invno       decimal(9, 0) not null,
  http_status integer       not null,
  file_path   varchar(300)  not null,
  created     timestamp     not null default current timestamp);

-- Only the integration user profile may read the key
revoke all on invxml.invxmlcfg from public;
grant select on invxml.invxmlcfg to invxmlusr;

-- The full URL of an endpoint
create or replace function invxml.api_url(endpoint varchar(100))
  returns varchar(200)
  language sql reads sql data
  return (select trim(base_url) concat endpoint from invxml.invxmlcfg);

-- The HTTP options: API key, content type, read timeout in seconds
create or replace function invxml.http_options(content_type varchar(100))
  returns varchar(1000)
  language sql reads sql data
  return (select json_object(
                   'headers' value json_object(
                       'Authorization' value 'Bearer ' concat trim(api_key),
                       'Content-Type'  value content_type),
                   'ioTimeout' value '120')
            from invxml.invxmlcfg);

-- Text as UTF-8 bytes
create or replace function invxml.utf8(str varchar(1000))
  returns blob(4000)
  language sql deterministic
  return blob(cast(str as varchar(4000) ccsid 1208));

-- A multipart/form-data body: the field "file" and, if given, "validationLanguage"
create or replace function invxml.multipart(
    file_data blob(50M), file_name varchar(255), lang varchar(2))
  returns blob(51M)
  language sql deterministic no external action
begin
  declare crlf blob(2);
  declare body blob(51M);
  set crlf = blob(x'0D0A');
  set body = invxml.utf8('--InvXmlFormBoundary') concat crlf
    concat invxml.utf8('Content-Disposition: form-data; name="file"; filename="'
                       concat file_name concat '"') concat crlf
    concat invxml.utf8('Content-Type: application/octet-stream') concat crlf
    concat crlf concat file_data concat crlf;
  if lang <> '' then
    set body = body concat invxml.utf8('--InvXmlFormBoundary') concat crlf
      concat invxml.utf8('Content-Disposition: form-data; name="validationLanguage"')
      concat crlf concat crlf concat invxml.utf8(lang) concat crlf;
  end if;
  return body concat invxml.utf8('--InvXmlFormBoundary--') concat crlf;
end;

Run the programs under the user profile INVXMLUSR, or compile them with USRPRF(*OWNER). Every upload endpoint expects multipart/form-data with a single file field called file. Db2 has no built-in multipart support, so MULTIPART builds the body as a BLOB. The text parts must be UTF-8 bytes, not EBCDIC. That is the job of UTF8. The boundary string must also appear in the Content-Type header, as you will see in the import program.

Step 2: Map Db2 fields to JSON

The API takes an EN 16931 invoice as JSON. The data is in your invoice header, invoice lines and customer master. Your seller data comes from INVXMLCFG. This is the mapping with the example field names:

The JSON has no totals and no VAT amounts. The API computes them from the quantity, the net price and the VAT rate of each line. When the numbers do not fit together, you get an error list instead of a PDF. A wrong price or a rounding problem in your files shows up here, not at your customer.

One SQL statement builds the whole body. JSON_OBJECT builds each object, and JSON_ARRAYAGG collects the invoice lines into an array. The result is UTF-8 (CCSID 1208), which is what the API expects.

create or replace function invxml.invoice_json(p_invno decimal(9, 0))
  returns clob(1M) ccsid 1208
  language sql reads sql data
  return (
    select json_object(
      'invoiceNumber' value trim(leading '0' from digits(h.invno)),
      'issueDate'     value varchar_format(to_date(digits(h.invdat), 'YYYYMMDD'), 'YYYY-MM-DD'),
      'dueDate'       value varchar_format(to_date(digits(h.duedat), 'YYYYMMDD'), 'YYYY-MM-DD'),
      'currency'      value h.curcde,
      'purchaseOrderReference' value nullif(trim(h.ponum), ''),
      'buyerReference'         value nullif(trim(c.leitwegid), ''),   -- XRechnung only
      'seller' value json_object(
          'name'          value trim(s.seller_name),
          'vatIdentifier' value trim(s.seller_vat),
          'postalAddress' value json_object(
              'line1' value trim(s.seller_street), 'city'    value trim(s.seller_city),
              'postCode' value trim(s.seller_zip), 'country' value s.seller_country),
          'contact' value json_object(
              'name'  value trim(s.contact_name), 'phone' value trim(s.contact_phone),
              'email' value trim(s.contact_email)),
          'electronicAddress' value json_object(
              'identifier' value trim(s.seller_vat), 'schemeId' value '9930')),
      'buyer' value json_object(
          'name'          value trim(c.cusnam),
          'vatIdentifier' value nullif(trim(c.cusvat), ''),
          'postalAddress' value json_object(
              'line1' value trim(c.cusstr), 'city'    value trim(c.cuscty),
              'postCode' value trim(c.cuszip), 'country' value c.cusctr),
          'electronicAddress' value
              case when trim(c.leitwegid) <> ''
                   then json_object('identifier' value trim(c.cusvat), 'schemeId' value '9930')
              end format json
          absent on null),
      'paymentDetails' value json_object('paymentAccountIdentifier' value trim(s.seller_iban)),
      'lines' value (
          select json_arrayagg(json_object(
                   'quantity' value l.qty,
                   'unitCode' value case trim(l.uom) when 'ST'  then 'H87'
                                                     when 'STD' then 'HUR'
                                                     when 'KG'  then 'KGM'
                                                     else 'C62' end,
                   'item'           value json_object('name' value trim(l.itmdsc)),
                   'priceDetails'   value json_object('netPrice' value l.price),
                   'vatInformation' value json_object('rate' value l.vatpct))
                 order by l.linno)
            from applib.invlin l
           where l.invno = h.invno) format json
      absent on null)
    from applib.invhdr h
    join applib.custmast c on c.custno = h.custno
    cross join invxml.invxmlcfg s
    where h.invno = p_invno);

Test it in Run SQL Scripts with values invxml.invoice_json(4711);. ABSENT ON NULL drops empty fields, so the purchase order and the XRechnung fields only appear when they have a value. FORMAT JSON tells Db2 that the subquery and the CASE already return JSON. Without it, Db2 would send them as quoted text. If your prices are per 100 pieces, divide by the price unit in the netPrice line.

Step 3: Create the ZUGFeRD invoice

The program INVZUGF takes an invoice number and calls the API. QSYS2.HTTP_POST_BLOB_VERBOSE returns two columns: the response body as a BLOB and the response headers as JSON. The HTTP status is in the header key HTTP_STATUS_CODE. The BLOB version matters here, because the PDF is binary data.

**free
ctl-opt dftactgrp(*no) actgrp(*caller) option(*srcstmt);

// INVZUGF: create the e-invoice for one invoice and write it to the IFS
dcl-pi *n;
  invNo packed(9:0);
end-pi;

dcl-s response   sqltype(BLOB:10000000);
dcl-s httpStatus int(10);
dcl-s leitwegId  varchar(46);
dcl-s endpoint   varchar(50);
dcl-s target     varchar(300);

exec sql set option commit = *none, naming = *sql, closqlcsr = *endmod;

// Customers with a Leitweg-ID get XRechnung, all others get ZUGFeRD
exec sql
  select trim(c.leitwegid) into :leitwegId
    from applib.invhdr h
    join applib.custmast c on c.custno = h.custno
   where h.invno = :invNo;

endpoint = '/v1/create/zugferd';
if leitwegId <> '';
  endpoint = '/v1/create/xrechnung';
endif;

exec sql
  select r.response_message,
         json_value(r.response_http_header, '$.HTTP_STATUS_CODE' returning integer)
    into :response, :httpStatus
    from table(qsys2.http_post_blob_verbose(
           invxml.api_url(:endpoint),
           blob(json_object('invoice' value invxml.invoice_json(:invNo) format json)),
           invxml.http_options('application/json'))) r;

if sqlcode < 0;
  // No connection, no trusted certificate or a timeout (for example SQL0443)
  httpStatus = sqlcode;
  target = '';
elseif httpStatus = 200;
  target = '/invxml/out/' + %char(invNo);
  if leitwegId = '';
    target += '.pdf';
  else;
    target += '.xml';
  endif;
else;
  // 400: a JSON list of the rules the invoice breaks, with the JSON path of each field
  target = '/invxml/out/' + %char(invNo) + '.error.json';
endif;

if target <> '';
  exec sql call qsys2.ifs_write_binary(path_name  => :target,
                                       line       => :response,
                                       file_ccsid => 1208,
                                       overwrite  => 'REPLACE');
endif;

exec sql insert into invxml.invxmllog (invno, http_status, file_path)
         values (:invNo, :httpStatus, :target);

*inlr = *on;

Compile it with CRTSQLRPGI OBJ(INVXML/INVZUGF) SRCFILE(INVXML/QRPGLESRC) COMMIT(*NONE). The BLOB host variable takes up to 10 MB, which is far more than an invoice PDF needs. On an error, the program writes the API answer to an .error.json file next to the PDFs. The log table INVXMLLOG shows the status of every call. Customers in France get Factur-X: change the endpoint to /v1/create/facturx and keep the JSON. More on that format is in the Factur-X API toolkit.

Step 4: Store the PDF and start the program

The PDF is now in /invxml/out. Your archive or document system can pick it up from there. You can also share the folder with IBM i NetServer. Keep the file. It is the invoice your customer received, and you must store it for the legal retention period.

There are three common ways to start INVZUGF. Pick the one that fits your invoice run:

1. From the invoice print program. Call INVZUGF right after the invoice is final. Simple, but the print job waits for the API.
2. Through a data queue. The print program puts the invoice number on a data queue and continues. A batch job reads the queue and calls INVZUGF. The print run does not wait.
3. From the job scheduler. ADDJOBSCDE runs a program every evening. It selects the invoices of the day that have no row in INVXMLLOG and calls INVZUGF for each.

// In the invoice print program (option 1 or 2)
dcl-pr createEInvoice extpgm('INVZUGF');
  invNo packed(9:0);
end-pr;
dcl-pr sendDtaq extpgm('QSNDDTAQ');
  name char(10) const;
  lib  char(10) const;
  len  packed(5:0) const;
  data char(9) const;
end-pr;

createEInvoice(invNo);                                    // option 1: call directly
sendDtaq('INVXMLQ': 'INVXML': 9: %editc(invNo: 'X'));      // option 2: queue it

// INVDTAQ: the batch job that reads the queue (option 2)
dcl-pr receiveDtaq extpgm('QRCVDTAQ');
  name char(10) const;
  lib  char(10) const;
  len  packed(5:0);
  data char(9);
  wait packed(5:0) const;
end-pr;
dcl-s len  packed(5:0);
dcl-s data char(9);

dow *on;
  receiveDtaq('INVXMLQ': 'INVXML': len: data: -1);        // -1 = wait for the next entry
  createEInvoice(%dec(data: 9: 0));
enddo;

// CL commands to set it up
CRTDTAQ DTAQ(INVXML/INVXMLQ) MAXLEN(9)
SBMJOB CMD(CALL PGM(INVXML/INVDTAQ)) JOB(INVDTAQ) JOBQ(QSYSNOMAX) USER(INVXMLUSR)
ADDJOBSCDE JOB(INVBATCH) CMD(CALL PGM(INVXML/INVBATCH)) FRQ(*WEEKLY) +
           SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME('220000') USER(INVXMLUSR)

If IBM i sends the invoice email itself today, attach the hybrid PDF instead of the spooled file PDF. The hybrid PDF already shows the readable invoice. Your customer gets one file that people can read and software can process.

Send the invoice by email from the API

On IBM i, this is often the easiest route. One request creates the invoice, checks it and sends it to the customer by email. IBM i needs no SMTP setup and never handles the file. This is a complete delivery in Germany: there is no central platform for B2B e-invoices, and an email with a ZUGFeRD PDF fulfils the rules.

First, verify your sending domain. In the dashboard, enter the domain and add the three CNAME records you see there to your DNS. Then every address on that domain can appear in from. The request has the invoice JSON and one more object, email. The customer address comes from CUSTMAST.CUSEML:

select json_value(r.response_http_header, '$.HTTP_STATUS_CODE' returning integer)
         as http_status
  from applib.invhdr h
  join applib.custmast c on c.custno = h.custno,
  table(qsys2.http_post_blob_verbose(
    invxml.api_url('/v1/create/zugferd/email'),
    blob(json_object(
      'invoice' value invxml.invoice_json(h.invno) format json,
      'email' value json_object(
        'from'    value (select trim(from_email) from invxml.invxmlcfg),
        'to'      value trim(c.cuseml),
        'subject' value 'Rechnung ' concat trim(leading '0' from digits(h.invno)),
        'message' value 'Guten Tag, anbei erhalten Sie unsere Rechnung als ZUGFeRD-PDF.'))),
    invxml.http_options('application/json'))) r
 where h.invno = 4711;

A 200 status says three things: the invoice exists, it passed the checks, and the email is on its way. The file is not part of the answer. In INVZUGF, use this statement with INTO :httpStatus and :invNo instead of the fixed number. Use this route when the API should send. Use the plain endpoint from step 3 when IBM i should store the file and send it itself. XRechnung and Factur-X have the same /email route, for example /v1/create/xrechnung/email.

Import incoming supplier invoices

Since January 2025, your suppliers can send you e-invoices, and you must accept them. Most of them come as ZUGFeRD PDFs, and some as XRechnung XML. Most mail gateways can save attachments to a folder. Let yours save them in /invxml/inbox and write one row per file into the table INVXML.INBOX, with the path and the sender address. The program INVINBND then runs three steps for each file:

1. Validate. Send the file to POST /v1/validate/zugferd/report (PDF) or /v1/validate/xrechnung/report (XML), with validationLanguage=de. The API runs the EN 16931 rules and the German rules on it. You get a PDF report as the body. The result is in the header X-Invoice-Valid: true or false. If you only need the findings as JSON, use /v1/validate/zugferd without /report.
2. Reply with the report. For an invalid invoice, the program mails the PDF report back to the sender. Processing ends there. The report names every failed rule, the field and the reason, in German. With it, the supplier knows what to correct and sends a new invoice.
3. Extract. For a valid invoice, the program posts the file to POST /v1/extract/json. The API returns the invoice as JSON in the same shape as the create request, with the totals in invoice.totals.

**free
ctl-opt dftactgrp(*no) actgrp(*new) option(*srcstmt);

// INVINBND: validate incoming invoices, send back the report or stage the data
dcl-s path       varchar(256);
dcl-s sender     varchar(254);
dcl-s fileName   varchar(256);
dcl-s endpoint   varchar(50);
dcl-s httpStatus int(10);
dcl-s valid      varchar(5);
dcl-s newStatus  varchar(10);
dcl-s reportPath varchar(300);
dcl-s cmd        varchar(2000);
dcl-s response   sqltype(BLOB:10000000);

// GET_BLOB_FROM_FILE only runs under commitment control
exec sql set option commit = *chg, naming = *sql, closqlcsr = *endmod;

exec sql declare inbox cursor with hold for
  select file_path, sender,
         substr(file_path, locate_in_string(file_path, '/', -1) + 1),
         case when lower(file_path) like '%.xml' then '/v1/validate/xrechnung/report'
              else '/v1/validate/zugferd/report' end
    from invxml.inbox
   where status = 'NEW';

exec sql open inbox;
dow *on;
  exec sql fetch inbox into :path, :sender, :fileName, :endpoint;
  if sqlcode <> 0;
    leave;
  endif;

  // 1. Validate: the body is the PDF report, the header holds the result
  exec sql
    select r.response_message,
           json_value(r.response_http_header, '$.HTTP_STATUS_CODE' returning integer),
           coalesce(json_value(r.response_http_header, '$."X-Invoice-Valid"'), 'false')
      into :response, :httpStatus, :valid
      from table(qsys2.http_post_blob_verbose(
             invxml.api_url(:endpoint),
             invxml.multipart(get_blob_from_file(:path), :fileName, 'de'),
             invxml.http_options('multipart/form-data; boundary=InvXmlFormBoundary'))) r;

  if sqlcode < 0 or httpStatus <> 200;
    newStatus = 'ERROR';
  else;
    reportPath = '/invxml/reports/' + fileName + '.report.pdf';
    exec sql call qsys2.ifs_write_binary(path_name => :reportPath,
                                         line      => :response,
                                         overwrite => 'REPLACE');
    if valid <> 'true';
      // 2. Invalid: send the report to the sender and stop
      cmd = 'SNDSMTPEMM RCP((''' + %scanrpl('''': '': sender) + ''' *PRI)) '
          + 'SUBJECT(''Pruefbericht zu Ihrer Rechnung ' + fileName + ''') '
          + 'NOTE(''Ihre E-Rechnung ist nicht gueltig. Der Bericht im Anhang nennt die Fehler.'') '
          + 'ATTACH((''' + reportPath + ''' *PDF *BIN))';
      exec sql call qsys2.qcmdexc(:cmd);
      newStatus = 'REJECTED';
    else;
      // 3. Valid: read the invoice as JSON and stage it
      exec sql
        select r.response_message,
               json_value(r.response_http_header, '$.HTTP_STATUS_CODE' returning integer)
          into :response, :httpStatus
          from table(qsys2.http_post_blob_verbose(
                 invxml.api_url('/v1/extract/json'),
                 invxml.multipart(get_blob_from_file(:path), :fileName, ''),
                 invxml.http_options('multipart/form-data; boundary=InvXmlFormBoundary'))) r;
      newStatus = 'ERROR';
      if sqlcode >= 0 and httpStatus = 200;
        exec sql call invxml.stage_invoice(:response, :path);
        newStatus = 'STAGED';
      endif;
    endif;
  endif;

  exec sql update invxml.inbox set status = :newStatus where file_path = :path;
  exec sql commit;
enddo;
exec sql close inbox;

*inlr = *on;

SNDSMTPEMM sends through the IBM i SMTP server. The sender is the SMTP address of the user profile that runs the job, so that profile needs an SMTP name (WRKNAMSMTP or ADDUSRSMTP, depending on your SMTP setup). If your mail gateway can send mail, let it send the report instead. Keep rejected files in the inbox folder too, so the case is on record. Schedule INVINBND with ADDJOBSCDE, for example every 15 minutes during office hours.

PDFs with no XML inside: post them to POST /v1/parse/json, with the same multipart body. AI reads the typed, scanned or photographed invoice. The answer is the same JSON, plus a confidence object with scores between 0.0 and 1.0. Your program can stage high scores directly and mark low scores for a clerk. A file that is not an invoice gets error code 4008.

Create the supplier invoice from the JSON

Most IBM i accounts payable systems have their own files and posting programs. So the import writes the data into two staging tables. Your existing program, or a small review screen, posts from there. JSON_TABLE reads the JSON. It accepts the BLOB from the API directly with FORMAT JSON, because the JSON is UTF-8. The vendor is found by the VAT id in your vendor master (VENDMAST in this example).

create table invxml.inbox (
  file_path varchar(256) not null primary key,
  sender    varchar(254) not null,
  received  timestamp    not null default current timestamp,
  status    varchar(10)  not null default 'NEW');

create table invxml.supinvhdr (
  id             bigint generated always as identity primary key,
  file_path      varchar(256) not null,
  vendor_no      decimal(7, 0),            -- empty when no vendor has this VAT id
  seller_vat     varchar(20),
  seller_name    varchar(100),
  invoice_number varchar(50),
  issue_date     date,
  due_date       date,
  currency       char(3),
  po_reference   varchar(50),
  grand_total    decimal(13, 2),
  due_payable    decimal(13, 2),
  status         varchar(10) not null default 'NEW');

create table invxml.supinvlin (
  hdr_id    bigint not null,
  line_no   integer not null,
  item_name varchar(200),
  quantity  decimal(15, 3),
  unit_code varchar(3),
  net_price decimal(15, 4),
  vat_rate  decimal(5, 2));

create or replace procedure invxml.stage_invoice(p_json blob(10M), p_path varchar(256))
  language sql modifies sql data
begin
  declare v_id bigint;

  insert into invxml.supinvhdr (file_path, vendor_no, seller_vat, seller_name,
         invoice_number, issue_date, due_date, currency, po_reference,
         grand_total, due_payable)
    select p_path, v.vndno, j.seller_vat, j.seller_name, j.invoice_number,
           j.issue_date, j.due_date, j.currency, j.po_reference,
           j.grand_total, j.due_payable
      from json_table(p_json format json, 'lax $.invoice'
             columns (invoice_number varchar(50)   path 'lax $.invoiceNumber',
                      issue_date     date          path 'lax $.issueDate',
                      due_date       date          path 'lax $.dueDate',
                      currency       char(3)       path 'lax $.currency',
                      po_reference   varchar(50)   path 'lax $.purchaseOrderReference',
                      seller_name    varchar(100)  path 'lax $.seller.name',
                      seller_vat     varchar(20)   path 'lax $.seller.vatIdentifier',
                      grand_total    decimal(13,2) path 'lax $.totals.grandTotalAmount',
                      due_payable    decimal(13,2) path 'lax $.totals.duePayableAmount')) j
      left join applib.vendmast v on trim(v.vndvat) = j.seller_vat;

  set v_id = identity_val_local();

  insert into invxml.supinvlin
    select v_id, j.line_no, j.item_name, j.quantity, j.unit_code, j.net_price, j.vat_rate
      from json_table(p_json format json, 'lax $.invoice.lines[*]'
             columns (line_no   for ordinality,
                      item_name varchar(200)  path 'lax $.item.name',
                      quantity  decimal(15,3) path 'lax $.quantity',
                      unit_code varchar(3)    path 'lax $.unitCode',
                      net_price decimal(15,4) path 'lax $.priceDetails.netPrice',
                      vat_rate  decimal(5,2)  path 'lax $.vatInformation.rate')) j;
end;

The totals are in invoice.totals, inside the invoice object. Compare GRAND_TOTAL with the purchase order before you post. PO_REFERENCE holds the buyer order number from the supplier invoice, so your program can match it against your open orders. When VENDOR_NO is empty, the clerk assigns the vendor in the review screen.

XRechnung for German public buyers

Public buyers in Germany (federal, state and local) want XRechnung. An XRechnung is an XML file only. The invoice portals ZRE and OZG-RE run the KoSIT rules on every file they receive. The function and the program from this guide already create it. You only add the Leitweg-ID to the customer master:

-- The Leitweg-ID on the customer master
alter table applib.custmast
  add column leitwegid varchar(46) not null default '';

update applib.custmast
   set leitwegid = '04011000-1234512345-06'
 where custno = 10020;

-- Check the XRechnung JSON of one invoice before you send it
values invxml.invoice_json(4712);

The Leitweg-ID tells the portal which public office gets the invoice. The buyer gives it to you. An XRechnung without it fails rule BR-DE-15. When the column is filled, INVOICE_JSON adds it as buyerReference, together with the buyer's electronic address. The seller contact (name, phone, email) and the seller electronic address come from INVXMLCFG. XRechnung needs both. The scheme 9930 marks the identifier as a German VAT id. If you deliver over Peppol, send your Peppol participant id with its scheme instead.

A note on DDS files: if CUSTMAST is a DDS physical file with many programs over it, adding a column changes its record format. Those programs then end with a level check error until you recompile them. In that case, put the Leitweg-ID in a small side table keyed by customer number and join it in INVOICE_JSON.

INVZUGF sees the Leitweg-ID, calls /v1/create/xrechnung and writes an .xml file. The API has already checked the XML with the KoSIT rules. Each buyer names the delivery channel: the ZRE or OZG-RE portal, Peppol, or email. For your own files, /v1/render/xrechnung/to/pdf turns the XML into a PDF that people can read. Details on the format are in the XRechnung API toolkit.

IBM i releases, PTFs and the certificate store

Releases and PTF groups. The QSYS2 HTTP functions first came with the Db2 for i PTF groups SF99703 Level 26 (IBM i 7.3) and SF99704 Level 15 (IBM i 7.4). Those first versions return text (CLOB). The BLOB versions, such as HTTP_POST_BLOB and HTTP_POST_BLOB_VERBOSE, came later: SF99704 Level 25 on 7.4 and SF99950 Level 4 on 7.5. This guide needs the BLOB versions, because PDFs are binary. The older SYSTOOLS functions (for example SYSTOOLS.HTTPPOSTBLOB) run in a Java virtual machine and use the Java certificate store. The QSYS2 functions are the current ones. They run without Java and use the system certificate store.

The certificate store. By default, the QSYS2 HTTP functions use the *SYSTEM store, the file /QIBM/UserData/ICSS/Cert/Server/DEFAULT.KDB. The system must trust the certificate authority of api.invoicexml.com. Open the certificate path in a browser and export the root and the intermediate certificate. Then, in Digital Certificate Manager (DCM, https://your-system:2007/dcm):

1. Open the *SYSTEM certificate store with its password.
2. Import the root certificate as a certificate authority (CA) certificate.
3. Import the intermediate certificate the same way.
4. Give the user profile that runs the programs read authority (*R) to DEFAULT.KDB.

The HTTP option sslTolerate skips the certificate check. IBM recommends it only for tests, so keep it out of production. Use this script to check the PTF level and the connection:

-- Installed Db2 PTF group (SF99704 = 7.4, SF99950 = 7.5)
select ptf_group_name, ptf_group_level, ptf_group_status
  from qsys2.group_ptf_info
 where ptf_group_name in ('SF99704', 'SF99950')
   and ptf_group_status = 'INSTALLED';

-- Does the system trust the API certificate? Any HTTP status means yes.
-- SQL0443 with a GSKit message means the CA is missing in the *SYSTEM store.
select json_value(response_http_header, '$.HTTP_STATUS_CODE' returning integer)
  from table(qsys2.http_get_verbose('https://api.invoicexml.com/'));

Job CCSID. Run the programs in a job with a real CCSID, for example 273 or 1141 for German. With CCSID 65535, Db2 cannot convert the text between EBCDIC and UTF-8, and umlauts in names and addresses break.

Endpoint reference

Get started

The whole IBM i integration is one SQL script and two RPG programs. The script holds the key, the HTTP helpers and the JSON mapping. INVZUGF creates the ZUGFeRD or XRechnung file, or has the API email it. INVINBND checks and stages what comes in. Your invoice files stay as they are. Nothing in your code changes when the formats change. More platforms, from SAP to Shopware, are in the integrations hub.

Related guides:

• SAP ABAP ZUGFeRD: the same integration inside SAP ECC and S/4HANA on-premise.
• Excel and Access VBA ZUGFeRD: the same API calls from Office, for smaller invoice runs.
• ZUGFeRD API toolkit: what is inside a ZUGFeRD file, and all endpoints for it.
• XRechnung API toolkit: invoices to public buyers, from the Leitweg-ID to the portals.