(SQL Server) Amazon MWS Upload Invoice

Demonstrates how to upload an invoice using _UPLOAD_VAT_INVOICE_FeedType to submit an invoice for an order.

For more information, see https://m.media-amazon.com/images/G/03/B2B/invoice-uploader-developer-documentation.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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

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

    -- Connect to the Amazon MWS REST server.
    -- 
    -- Make sure to connect to the correct Amazon MWS Endpoint, otherwise
    -- you'll get an HTTP 401 response code.
    -- 
    -- See Amazon MWS endpoints and MarketplaceId values

    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'mws.amazonservices.com', @port, @bTls, @bAutoReconnect

    EXEC sp_OASetProperty @rest, 'Host', 'mws.amazonservices.com'

    -- MarketplaceList.Id parameter �This should be the marketplace in which the order was placed. Only one marketplace must be used per order.T
    -- Here are the marketplace ID's
    -- Spain: A1RKKUPIHCS9HS
    -- UK: A1F83G8C2ARO7P
    -- France: A13V1IB3VIYZZH
    -- Germany: A1PA6795UKMFR9
    -- Italy: APJ6JRA9NG5V4
    -- ...
    -- (See https://docs.developer.amazonservices.com/en_US/dev_guide/DG_Endpoints.html)

    -- FeedOptions parameter � Seller can input key value pairs to give important metadata along with the PDF invoice.
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'FeedOptions', 'metadata:orderid=206-2341234-3455465;metadata:invoicenumber=INT-3431-XJE3;metadata:documenttype=Invoice'

    -- Load the PDF invoice file that is to be the body of the HTTP POST request.
    DECLARE @pdfData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @pdfData OUT

    EXEC sp_OAMethod @pdfData, 'LoadFile', @success OUT, 'qa_data/pdf/sample.pdf'

    -- Get the MD5 hash of the PDF data.
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'md5'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @md5Hash nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashBdENC', @md5Hash OUT, @pdfData

    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'AWSAccessKeyId', '0PB842ExampleN4ZTR2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Action', 'SubmitFeed'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'FeedType', '_UPLOAD_VAT_INVOICE_'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MWSAuthToken', 'EXAMPLE-amzn.mws.4ea38b7b-f563-7709-4bae-87aea-EXAMPLE'

    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MarketplaceIdList.Id.1', 'ATVExampleDER'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SellerId', 'A1XExample5E6'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'ContentMD5Value', @md5Hash
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureMethod', 'HmacSHA256'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureVersion', '2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Version', '2009-01-01'

    -- Add the MWS Signature param.  (Also adds the Timestamp parameter using the curent system date/time.)
    EXEC sp_OAMethod @rest, 'AddMwsSignature', @success OUT, 'POST', '/Feeds/2009-01-01', 'mws.amazonservices.com', 'YOUR_MWS_SECRET_ACCESS_KEY_ID'

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/octet-stream'
    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestBd', @success OUT, 'POST', '/Feeds/2009-01-01', @pdfData, @sbResponseBody
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @pdfData
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT 'response body: ' + @sTmp0

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
      END

    -- Examine the XML returned in the response body.
    EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @pdfData
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbResponseBody


END
GO