(SQL Server) Send ZATCA Invoice for Clearance

See more ZATCA Examples

Demonstrates how to send a ZATCA invoice for clearance, which is to send the HTTP POST with invoice hash and the signed invoice in base64 format.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example will send a POST with HTTP Basic Authentication to 
    -- https://gw-fatoora.zatca.sa/e-invoicing/simulation/invoices/clearance/single

    -- NOTE: At the time of this writing, I don't know for sure if the above URL is correct.
    -- 
    -- The body of the POST will contain JSON formatted as such:

    -- {
    --   "summary": "Standard Invoice",
    --   "value": {
    --     "invoiceHash": "PEx8bNFcEMEpHzUVvQntQI6ot8eFqTT/l59b+H1HqX4=",
    --     "uuid": "16e78469-64af-406d-9cfd-895e724198f0",
    --     "invoice": "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPEl....."
    --   }
    -- }

    -- The above JSON is created from the signed XML invoice, such as the following:

    -- *** Look at the above base64 data for the invoiceHash and uuid.  These values are found in the invoice as shown below.
    -- *** We'll write code to load the signed invoice into a Chilkat XML object, grab the invoice hash and the uuid,
    -- *** and then construct the above JSON, which will then be sent in a POST.  (See below...)

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"><ext:UBLExtensions>
    --     <ext:UBLExtension>
    --         <ext:ExtensionURI>urn:oasis:names:specification:ubl:dsig:enveloped:xades</ext:ExtensionURI>
    --         <ext:ExtensionContent>
    --             <!-- Please note that the signature values are sample values only -->
    --             <sig:UBLDocumentSignatures xmlns:sig="urn:oasis:names:specification:ubl:schema:xsd:CommonSignatureComponents-2" xmlns:sac="urn:oasis:names:specification:ubl:schema:xsd:SignatureAggregateComponents-2" xmlns:sbc="urn:oasis:names:specification:ubl:schema:xsd:SignatureBasicComponents-2">
    --                 <sac:SignatureInformation>
    --                     <cbc:ID>urn:oasis:names:specification:ubl:signature:1</cbc:ID>
    --                     <sbc:ReferencedSignatureID>urn:oasis:names:specification:ubl:signature:Invoice</sbc:ReferencedSignatureID>
    --                     <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#" Id="signature">
    --                         <ds:SignedInfo>
    --                             <ds:CanonicalizationMethod Algorithm="http://www.w3.org/2006/12/xml-c14n11"/>
    --                             <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#ecdsa-sha256"/>
    --                             <ds:Reference Id="invoiceSignedData" URI="">
    --                                 <ds:Transforms>
    --                                     <ds:Transform Algorithm="http://www.w3.org/TR/1999/REC-xpath-19991116">
    --                                         <ds:XPath>not(//ancestor-or-self::ext:UBLExtensions)</ds:XPath>
    --                                     </ds:Transform>
    --                                     <ds:Transform Algorithm="http://www.w3.org/TR/1999/REC-xpath-19991116">
    --                                         <ds:XPath>not(//ancestor-or-self::cac:Signature)</ds:XPath>
    --                                     </ds:Transform>
    --                                     <ds:Transform Algorithm="http://www.w3.org/TR/1999/REC-xpath-19991116">
    --                                         <ds:XPath>not(//ancestor-or-self::cac:AdditionalDocumentReference[cbc:ID='QR'])</ds:XPath>
    --                                     </ds:Transform>
    --                                     <ds:Transform Algorithm="http://www.w3.org/2006/12/xml-c14n11"/>
    --                                 </ds:Transforms>
    --                                 <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --                                 <ds:DigestValue>PEx8bNFcEMEpHzUVvQntQI6ot8eFqTT/l59b+H1HqX4=</ds:DigestValue>
    --                             </ds:Reference>
    --                             <ds:Reference Type="http://www.w3.org/2000/09/xmldsig#SignatureProperties" URI="#xadesSignedProperties">
    --                                 <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --                                 <ds:DigestValue>ZDEyMDUyODJjYzk4MGViNTJhNmYzMGIyZTgxODhkY2JlOWEzNmRiMTFlZTVhMDAxNjk5OTRkYTg3ODhlY2ZiMw==</ds:DigestValue>
    --                             </ds:Reference>
    --                         </ds:SignedInfo>
    -- ...
    -- ...
    -- ...
    --         </ext:ExtensionContent>
    --     </ext:UBLExtension>
    -- </ext:UBLExtensions>
    --     
    --     <cbc:ProfileID>reporting:1.0</cbc:ProfileID>
    --     <cbc:ID>SME00062</cbc:ID>
    --     <cbc:UUID>16e78469-64af-406d-9cfd-895e724198f0</cbc:UUID>
    --     <cbc:IssueDate>2022-03-13</cbc:IssueDate>
    --     <cbc:IssueTime>14:40:40</cbc:IssueTime>
    --     <cbc:InvoiceTypeCode name="0111010">388</cbc:InvoiceTypeCode>
    -- ...
    -- ...
    -- ...
    --     </cac:InvoiceLine>
    -- </Invoice>

    -- -------------------------------------------------------------------
    -- First, let's load our signed XML into a Chilkat XML object so we can get the uuid and invoice hash.
    DECLARE @signedXmlFilePath nvarchar(4000)
    SELECT @signedXmlFilePath = 'qa_data/zatca/http_post_clearance/sample_signed_invoice.xml'

    DECLARE @signedXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @signedXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @signedXml, 'LoadXmlFile', @success OUT, @signedXmlFilePath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @signedXml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @signedXml
        RETURN
      END

    DECLARE @invoiceHash nvarchar(4000)
    EXEC sp_OAMethod @signedXml, 'GetChildContent', @invoiceHash OUT, 'ext:UBLExtensions|ext:UBLExtension|ext:ExtensionContent|sig:UBLDocumentSignatures|sac:SignatureInformation|ds:Signature|ds:SignedInfo|ds:Reference[0]|ds:DigestValue'

    PRINT 'invoiceHash: ' + @invoiceHash

    DECLARE @cbc_UUID nvarchar(4000)
    EXEC sp_OAMethod @signedXml, 'GetChildContent', @cbc_UUID OUT, 'cbc:UUID'

    PRINT 'UUID: ' + @cbc_UUID

    -- The Chilkat XML object was used purely for getting the above 2 values.
    -- We must send the EXACT signed XML in base64 format.  Therefore, we should load the signed XML into a Chilkat BinData and get it in base64 single-line format.

    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, @signedXmlFilePath
    DECLARE @base64Invoice nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetEncoded', @base64Invoice OUT, 'base64'

    -- OK, let's build the JSON that will be in the HTTP POST body.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'summary', 'Standard Invoice'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'value.invoiceHash', @invoiceHash
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'value.uuid', @cbc_UUID
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'value.invoice', @base64Invoice

    -- Now create the HTTP object and send the POST.
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- We'll want HTTP basic authentication.
    EXEC sp_OASetProperty @http, 'BasicAuth', 1
    EXEC sp_OASetProperty @http, 'Login', 'your login'
    EXEC sp_OASetProperty @http, 'Password', 'your password'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://gw-fatoora.zatca.sa/e-invoicing/simulation/invoices/clearance/single', 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @signedXml
        EXEC @hr = sp_OADestroy @bd
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Check the response status code.  A 200 or 202 indicates success..
    DECLARE @statusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT

    PRINT 'Response status code = ' + @statusCode

    -- Examine the response, which is JSON.
    DECLARE @jsonResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT

    EXEC sp_OAMethod @resp, 'GetBodyJson', @success OUT, @jsonResp
    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0

    PRINT 'JSON Response:'
    EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    EXEC @hr = sp_OADestroy @signedXml
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonResp


END
GO