SQL Server
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
-- 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 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