QR Code in Base64 encoding for KSA E-Invoicing

Generate Base64 encoded QR in Oracle Apex 22.2 and or Oracle Reports 11g

# For more details about the documentation please follow (S M Firoz Ashraf) the author's article

As per Zakat, Tax and Customs Authority (ZATCA) of Saudi Arabia, one of the main requirements is the implementation of QR codes on tax invoices in the e-invoicing project (Fatoora), which will be mandatory starting December 4, 2021

As per the ZATCA instructions(Page No. 23), the minimum requirements that must be shown after scanning a QR code are the following fields, which should be represented in form of based64 encoding:

1. Seller’s name.
2. VAT registration number of the seller.
3. Time stamp of the invoice (date and time).
4. Invoice total (with VAT).
5. VAT total.

In this blog, I will show how to encode the QR data in base64 format using Oracle Apex 22.2 and Oracle Reports 11g with Oracle Database 19c to print QR code on Invoice layouts.

1st Step is to prepare each of the five values in TLV (Tag-Length-Value) structure

Tag is fixed (1 for Seller’s name, 2 for VAT No……5 for VAT Total)

Length is the size of the value field in bytes (it’s not the count of characters but how many bytes the value represents)

Value is the data against each of the five fields.

Let’s take an example to clarify TLV

• 1. Seller name; for example, “Firoz Ashraf”
     • Tag = 1 (1 as a type represents the seller name)
     • Length = 12 (The number of the bytes in “Firoz Ashraf” word)
     • Value = Firoz Ashraf
  2. VAT Number; for example, 1234567891
     • Tag = 2 (2 as a type represents the VAT number)
     • Length = 10
     • Value = 1234567891
  3. Time Stamp; for example, 2021-11-17 08:30:00
     • Tag = 3 (3 as a type represents invoice time stamp)
     • Length = 19
     • Value = 2021-11-17 08:30:00
  4. Invoice Total; for example, 100.00
     • Tag = 4 (4 as a type represents the invoice amount)
     • Length = 6
     • Value = 100.00
  5. VAT Total; for example, 15.00
     • Tag = 5 (5 as a type represents the tax amount)
     • Length = 5
     • Value = 15.00

 

2nd Step

For each tag first convert it to hexa, then concatenate the hexa value into one string. Use this single string to convert to base64.

For example,

Tag1: convert 1, 12 (length of 'Firoz Ashraf') & 'Firoz Ashraf' itself to hexa. Store this in variable tlv1

(01 for 1, 0C for 12, 4669726F7A20417368726166 for Firoz Ashraf)

So the hexa value in tlv1 will be 010C4669726F7A20417368726166

 

Tag2: convert 2, 10 (length of '1234567891') & '1234567891' itself to hexa. Store this in variable tlv2

(02 for 2, 0A for 10, 31323334353637383931 for 1234567891)

So the hexa value in tlv2 will be 020A31323334353637383931

 

Do this for remaining tags. Then concatenate tlv1 tlv2 tlv3 tlv4 tlv5 into a single variable say qrcode_xstring

3rd Step

As per above example the value in variable qrcode_xstring will be

010C4669726F7A20417368726166020A313233343536373839310313323032312D31312D31372030383A33303A303004063131352E3030050531352E3030

Now convert this to base64, which will become(for the value above)

AQxGaXJveiBBc2hyYWYCCjEyMzQ1Njc4OTEDEzIwMjEtMTEtMTcgMDg6MzA6MDAEBjExNS4wMAUFMTUuMDA=

I tested it here on this site and it works fine https://tomeko.net/online_tools/hex_to_base64.php

Now let’s see the QR.SQL that used do this in Oracle PL/SQL