SQL Server Requires Chilkat v11.0.0+
SQL Server
Hungary NAV Manage Annulment Request
See more Hungary NAV Invoicing Examples
Demonstrates the manageAnnulment request for the Hungarian NAV Online Invoicing System REST API v2.0.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
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Build the following XML:
-- Use this online tool to generate code from sample XML:
-- Generate Code to Create XML
-- <?xml version="1.0" encoding="UTF-8"?>
-- <ManageAnnulmentRequest xmlns="http://schemas.nav.gov.hu/OSA/2.0/api">
-- <header>
-- <requestId>RID338592103413</requestId>
-- <timestamp>2019-09-11T13:37:09.385Z</timestamp>
-- <requestVersion>2.0</requestVersion>
-- <headerVersion>1.0</headerVersion>
-- </header>
-- <user>
-- <login>lwilsmn0uqdxe6u</login>
-- <passwordHash>2F43840A882CFDB7DB0 ... F6CA57FE8CD74DC28E</passwordHash>
-- <taxNumber>11111111</taxNumber>
-- <requestSignature>A297E3BD9 ... 1959ADFAF5</requestSignature>
-- </user>
-- <software>
-- <softwareId>123456789123456789</softwareId>
-- <softwareName>string</softwareName>
-- <softwareOperation>LOCAL_SOFTWARE</softwareOperation>
-- <softwareMainVersion>string</softwareMainVersion>
-- <softwareDevName>string</softwareDevName>
-- <softwareDevContact>string</softwareDevContact>
-- <softwareDevCountryCode>HU</softwareDevCountryCode>
-- <softwareDevTaxNumber>string</softwareDevTaxNumber>
-- </software>
-- <exchangeToken>dbd03076-3a9b-4312-bbbb-0cee3a6472572P11CS49ASIL</exchangeToken>
-- <annulmentOperations>
-- <annulmentOperation>
-- <index>1</index>
-- <annulmentOperation>ANNUL</annulmentOperation>
-- <invoiceAnnulment>PD94bWwgdm ... ubnVsbWVudD4=</invoiceAnnulment>
-- </annulmentOperation>
-- </annulmentOperations>
-- </ManageAnnulmentRequest>
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @dtNow int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT
EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
PRINT @sTmp0
-- The hash algorithm for the password is SHA512 (not SHA3-512).
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha512'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
DECLARE @myPassword nvarchar(4000)
SELECT @myPassword = 'my-password'
DECLARE @passwordHash nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashStringENC', @passwordHash OUT, @myPassword
-- Generate a random request ID like "RID215118906689"
DECLARE @prng int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @sbRequestId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestId OUT
EXEC sp_OAMethod @sbRequestId, 'Append', @success OUT, 'RID'
EXEC sp_OAMethod @prng, 'RandomString', @sTmp0 OUT, 12, 1, 0, 0
EXEC sp_OAMethod @sbRequestId, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
PRINT 'generated requestId = ' + @sTmp0
-- Calculate the requestSignature
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha3-512'
DECLARE @signatureKey nvarchar(4000)
SELECT @signatureKey = 'ce-8f5e-215119fa7dd621DLMRHRLH2S'
DECLARE @sbFinalHashBase int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFinalHashBase OUT
-- First append the timestamp because we are going to remove certain chars/parts.
EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @sTmp0
DECLARE @numReplaced int
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'Z', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '-', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, ':', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'T', ''
-- Prepend the requestId and append the signatureKey
EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbFinalHashBase, 'Prepend', @success OUT, @sTmp0
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @signatureKey
-- Calculate first (and only) index hash.
DECLARE @invoiceOperation nvarchar(4000)
SELECT @invoiceOperation = 'ANNUL'
DECLARE @invoiceData nvarchar(4000)
SELECT @invoiceData = 'PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9InllcyI/Pg0KPEludm9pY2VBbm51bG1lbnQgeG1sbnM9Imh0dHA6Ly9zY2hlbWFzLm5hdi5nb3YuaHUvT1NBLzIuMC9hbm51bCI+DQoJCTxhbm51bG1lbnRSZWZlcmVuY2U+MjIyMjIyMjI8L2FubnVsbWVudFJlZmVyZW5jZT4NCgkJPGFubnVsbWVudFRpbWVzdGFtcD4yMDE4LTA2LTE4VDA5OjEwOjQ1LjMwOVo8L2FubnVsbWVudFRpbWVzdGFtcD4NCgkJPGFubnVsbWVudENvZGU+RVJSQVRJQ19EQVRBPC9hbm51bG1lbnRDb2RlPg0KCQk8YW5udWxtZW50UmVhc29uPmNyZWF0ZSBzemFtbGEgYW5udWw8L2FubnVsbWVudFJlYXNvbj4NCjwvSW52b2ljZUFubnVsbWVudD4='
DECLARE @sbHashBase int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHashBase OUT
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceOperation
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceData
DECLARE @firstHash nvarchar(4000)
EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @firstHash OUT, @sTmp0
-- Append the index hash to the final hash base.
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @firstHash
-- Get our request signature (using sha3-512 because our HashAlgorithm was set to "sha3-512" up above...)
DECLARE @requestSignature nvarchar(4000)
EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @requestSignature OUT, @sTmp0
-- Load our recently obtained exchange token.
-- See Hungary NAV Invoicing Token Exchange Sample Code
DECLARE @xmlExchangeToken int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlExchangeToken OUT
EXEC sp_OAMethod @xmlExchangeToken, 'LoadXmlFile', @success OUT, 'qa_data/tokens/nav_exchange_token.xml'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @xmlExchangeToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @sbRequestId
EXEC @hr = sp_OADestroy @sbFinalHashBase
EXEC @hr = sp_OADestroy @sbHashBase
EXEC @hr = sp_OADestroy @xmlExchangeToken
RETURN
END
-- Get the base64 encoded/encrypted exchange token.
-- IMPORTANT: Make sure to use the exchange token before it expires.
-- If it expired, then get a new one..
DECLARE @encodedEncryptedExchangeToken nvarchar(4000)
EXEC sp_OAMethod @xmlExchangeToken, 'GetChildContent', @encodedEncryptedExchangeToken OUT, 'encodedExchangeToken'
-- Decode to binary.
DECLARE @bdExchangeToken int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdExchangeToken OUT
EXEC sp_OAMethod @bdExchangeToken, 'AppendEncoded', @success OUT, @encodedEncryptedExchangeToken, 'base64'
-- Decrypt using your 16-digit replacement key:
EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
EXEC sp_OASetProperty @crypt, 'CipherMode', 'ecb'
EXEC sp_OASetProperty @crypt, 'KeyLength', 128
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
-- Pass your 16-digit replacement key here:
EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, '99952BBAAAAA8XYZ', 'ascii'
EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bdExchangeToken
DECLARE @exchangeToken nvarchar(4000)
EXEC sp_OAMethod @bdExchangeToken, 'GetString', @exchangeToken OUT, 'utf-8'
PRINT 'exchange token = ' + @exchangeToken
-- Now build the XML..
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'ManageAnnulmentRequest'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://schemas.nav.gov.hu/OSA/2.0/api'
EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|requestId', @sTmp0
EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|timestamp', @sTmp0
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|requestVersion', '2.0'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|headerVersion', '1.0'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|login', 'lwilsmn0uqdxe6u'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|passwordHash', @passwordHash
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|taxNumber', '11111111'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|requestSignature', @requestSignature
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareId', '123456789123456789'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareName', 'string'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareOperation', 'LOCAL_SOFTWARE'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareMainVersion', 'string'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevName', 'string'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevContact', 'string'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevCountryCode', 'HU'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevTaxNumber', 'string'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'exchangeToken', @exchangeToken
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'annulmentOperations|annulmentOperation|index', '1'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'annulmentOperations|annulmentOperation|annulmentOperation', @invoiceOperation
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'annulmentOperations|annulmentOperation|invoiceAnnulment', @invoiceData
-- POST the XML to https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/manageAnnulment
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'Accept', 'application/xml'
DECLARE @endpoint nvarchar(4000)
SELECT @endpoint = 'https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/manageAnnulment'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @endpoint, @sTmp0, 'utf-8', 'application/xml', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @sbRequestId
EXEC @hr = sp_OADestroy @sbFinalHashBase
EXEC @hr = sp_OADestroy @sbHashBase
EXEC @hr = sp_OADestroy @xmlExchangeToken
EXEC @hr = sp_OADestroy @bdExchangeToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code = ' + @iTmp0
DECLARE @respXml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @respXml OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @respXml, 'LoadXml', @success OUT, @sTmp0
PRINT 'Response body:'
EXEC sp_OAMethod @respXml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- The result looks like this:
-- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
-- <ManageAnnulmentResponse xmlns="http://schemas.nav.gov.hu/OSA/2.0/api" xmlns:ns2="http://schemas.nav.gov.hu/OSA/2.0/data">
-- <header>
-- <requestId>RID465317110495</requestId>
-- <timestamp>2020-03-25T15:08:40Z</timestamp>
-- <requestVersion>2.0</requestVersion>
-- <headerVersion>1.0</headerVersion>
-- </header>
-- <result>
-- <funcCode>OK</funcCode>
-- </result>
-- <software>
-- <softwareId>123456789123456789</softwareId>
-- <softwareName>string</softwareName>
-- <softwareOperation>LOCAL_SOFTWARE</softwareOperation>
-- <softwareMainVersion>string</softwareMainVersion>
-- <softwareDevName>string</softwareDevName>
-- <softwareDevContact>string</softwareDevContact>
-- <softwareDevCountryCode>HU</softwareDevCountryCode>
-- <softwareDevTaxNumber>string</softwareDevTaxNumber>
-- </software>
-- <transactionId>2WT6XG9LQ0U9VQNA</transactionId>
-- </ManageAnnulmentResponse>
-- Use this online tool to generate parsing code from sample XML:
-- Generate Parsing Code from XML
DECLARE @ManageAnnulmentResponse_xmlns nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetAttrValue', @ManageAnnulmentResponse_xmlns OUT, 'xmlns'
DECLARE @ManageAnnulmentResponse_xmlns_ns2 nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetAttrValue', @ManageAnnulmentResponse_xmlns_ns2 OUT, 'xmlns:ns2'
DECLARE @requestId nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @requestId OUT, 'header|requestId'
DECLARE @timestamp nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @timestamp OUT, 'header|timestamp'
DECLARE @requestVersion nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @requestVersion OUT, 'header|requestVersion'
DECLARE @headerVersion nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @headerVersion OUT, 'header|headerVersion'
DECLARE @funcCode nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @funcCode OUT, 'result|funcCode'
DECLARE @softwareId nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareId OUT, 'software|softwareId'
DECLARE @softwareName nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareName OUT, 'software|softwareName'
DECLARE @softwareOperation nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareOperation OUT, 'software|softwareOperation'
DECLARE @softwareMainVersion nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareMainVersion OUT, 'software|softwareMainVersion'
DECLARE @softwareDevName nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevName OUT, 'software|softwareDevName'
DECLARE @softwareDevContact nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevContact OUT, 'software|softwareDevContact'
DECLARE @softwareDevCountryCode nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevCountryCode OUT, 'software|softwareDevCountryCode'
DECLARE @softwareDevTaxNumber nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevTaxNumber OUT, 'software|softwareDevTaxNumber'
DECLARE @transactionId nvarchar(4000)
EXEC sp_OAMethod @respXml, 'GetChildContent', @transactionId OUT, 'transactionId'
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @sbRequestId
EXEC @hr = sp_OADestroy @sbFinalHashBase
EXEC @hr = sp_OADestroy @sbHashBase
EXEC @hr = sp_OADestroy @xmlExchangeToken
EXEC @hr = sp_OADestroy @bdExchangeToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @respXml
END
GO