SQL Server
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 SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.Xml', @signedXml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
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
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://gw-fatoora.zatca.sa/e-invoicing/simulation/invoices/clearance/single', @json, 'application/json', @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
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
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 @resp
EXEC @hr = sp_OADestroy @jsonResp
END
GO