(SQL Server) Hungary NAV Manage Annulment Request

Demonstrates the manageAnnulment request for the Hungarian NAV Online Invoicing System REST API v2.0.

Note: This example requires Chilkat v9.5.0.83 or greater.

For more information, see https://onlineszamla.nav.gov.hu/api/files/container/download/Online%20Szamla_Interfesz%20specifik%C3%A1ci%C3%B3_EN_v2.0.pdf

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 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 @success int

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

    DECLARE @dtNow int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    DECLARE @sbRequestId int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OASetProperty @http, 'Accept', 'application/xml'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/xml'
    DECLARE @endpoint nvarchar(4000)
    SELECT @endpoint = 'https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/manageAnnulment'
    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @endpoint, @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 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
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code = ' + @iTmp0

    DECLARE @respXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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

    EXEC @hr = sp_OADestroy @resp

    -- 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 @respXml


END
GO