(SQL Server) Quickbooks Create an Invoice

Demonstrates how to create an invoice via the Quickbooks REST API.

For more information, see https://www.developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/invoice#create-an-invoice

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.

    -- First get our previously obtained OAuth2 access token.
    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/qb-access-token.json'

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- Connect to the REST server.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', @port, @bTls, @bAutoReconnect

    DECLARE @sbAuth int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT

    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
    EXEC sp_OASetProperty @rest, 'Authorization', @sTmp0

    -- --------------------------------------------------------------------------
    -- Note: The above code to setup the initial REST connection
    -- can be done once.  After connecting, any number of REST calls can be made.
    -- If the connection is lost, the next REST method call will automatically
    -- reconnect if needed.
    -- --------------------------------------------------------------------------

    -- Create the following JSON:

    -- {
    --   "Line": [
    --     {
    --       "DetailType": "SalesItemLineDetail",
    --       "Amount": 100.0,
    --       "SalesItemLineDetail": {
    --         "ItemRef": {
    --           "name": "Services",
    --           "value": "1"
    --         }
    --       }
    --     }
    --   ],
    --   "CustomerRef": {
    --     "value": "1"
    --   }
    -- }
    -- 
    -- Use the this online tool to generate the code from sample JSON: 
    -- Generate Code to Create JSON

    DECLARE @jsonReq int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonReq OUT

    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Line[0].DetailType', 'SalesItemLineDetail'
    EXEC sp_OAMethod @jsonReq, 'UpdateNumber', @success OUT, 'Line[0].Amount', '100.0'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Line[0].SalesItemLineDetail.ItemRef.name', 'Services'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Line[0].SalesItemLineDetail.ItemRef.value', '1'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'CustomerRef.value', '1'

    DECLARE @sbRequestBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @jsonReq, 'EmitSb', @success OUT, @sbRequestBody

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/json'
    EXEC sp_OASetProperty @rest, 'AllowHeaderFolding', 0

    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, 'FullRequestSb', @success OUT, 'POST', '/v3/company/<realmID>/invoice', @sbRequestBody, @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @jsonReq
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT

    -- Success is indicated by a 200 response status code.

    PRINT 'response status code = ' + @respStatusCode

    DECLARE @jsonResponse int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT

    EXEC sp_OAMethod @jsonResponse, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @jsonReq
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jsonResponse
        RETURN
      END

    -- Sample output...
    -- (See the parsing code below..)
    -- 
    -- Use the this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "Invoice": {
    --     "DocNumber": "1069",
    --     "SyncToken": "0",
    --     "domain": "QBO",
    --     "Balance": 100.0,
    --     "BillAddr": {
    --       "City": "Bayshore",
    --       "Line1": "4581 Finch St.",
    --       "PostalCode": "94326",
    --       "Lat": "INVALID",
    --       "Long": "INVALID",
    --       "CountrySubDivisionCode": "CA",
    --       "Id": "2"
    --     },
    --     "TxnDate": "2015-07-24",
    --     "TotalAmt": 100.0,
    --     "CustomerRef": {
    --       "name": "Amy's Bird Sanctuary",
    --       "value": "1"
    --     },
    --     "ShipAddr": {
    --       "City": "Bayshore",
    --       "Line1": "4581 Finch St.",
    --       "PostalCode": "94326",
    --       "Lat": "INVALID",
    --       "Long": "INVALID",
    --       "CountrySubDivisionCode": "CA",
    --       "Id": "109"
    --     },
    --     "LinkedTxn": [
    --     ],
    --     "DueDate": "2015-08-23",
    --     "PrintStatus": "NeedToPrint",
    --     "Deposit": 0,
    --     "sparse": false,
    --     "EmailStatus": "NotSet",
    --     "Line": [
    --       {
    --         "LineNum": 1,
    --         "Amount": 100.0,
    --         "SalesItemLineDetail": {
    --           "TaxCodeRef": {
    --             "value": "NON"
    --           },
    --           "ItemRef": {
    --             "name": "Services",
    --             "value": "1"
    --           }
    --         },
    --         "Id": "1",
    --         "DetailType": "SalesItemLineDetail"
    --       },
    --       {
    --         "DetailType": "SubTotalLineDetail",
    --         "Amount": 100.0,
    --         "SubTotalLineDetail": {}
    --       }
    --     ],
    --     "ApplyTaxAfterDiscount": false,
    --     "CustomField": [
    --       {
    --         "DefinitionId": "1",
    --         "Type": "StringType",
    --         "Name": "Crew #"
    --       }
    --     ],
    --     "Id": "238",
    --     "TxnTaxDetail": {
    --       "TotalTax": 0
    --     },
    --     "MetaData": {
    --       "CreateTime": "2015-07-24T10:33:39-07:00",
    --       "LastUpdatedTime": "2015-07-24T10:33:39-07:00"
    --     }
    --   },
    --   "time": "2015-07-24T10:33:39.11-07:00"
    -- }
    -- 

    DECLARE @LineNum int

    DECLARE @Amount nvarchar(4000)

    DECLARE @SalesItemLineDetailTaxCodeRefValue nvarchar(4000)

    DECLARE @SalesItemLineDetailItemRefName nvarchar(4000)

    DECLARE @SalesItemLineDetailItemRefValue nvarchar(4000)

    DECLARE @Id nvarchar(4000)

    DECLARE @DetailType nvarchar(4000)

    DECLARE @DefinitionId nvarchar(4000)

    DECLARE @invType nvarchar(4000)

    DECLARE @Name nvarchar(4000)

    DECLARE @InvoiceDocNumber nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceDocNumber OUT, 'Invoice.DocNumber'
    DECLARE @InvoiceSyncToken nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceSyncToken OUT, 'Invoice.SyncToken'
    DECLARE @InvoiceDomain nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceDomain OUT, 'Invoice.domain'
    DECLARE @InvoiceBalance nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBalance OUT, 'Invoice.Balance'
    DECLARE @InvoiceBillAddrCity nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrCity OUT, 'Invoice.BillAddr.City'
    DECLARE @InvoiceBillAddrLine1 nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrLine1 OUT, 'Invoice.BillAddr.Line1'
    DECLARE @InvoiceBillAddrPostalCode nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrPostalCode OUT, 'Invoice.BillAddr.PostalCode'
    DECLARE @InvoiceBillAddrLat nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrLat OUT, 'Invoice.BillAddr.Lat'
    DECLARE @InvoiceBillAddrLong nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrLong OUT, 'Invoice.BillAddr.Long'
    DECLARE @InvoiceBillAddrCountrySubDivisionCode nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrCountrySubDivisionCode OUT, 'Invoice.BillAddr.CountrySubDivisionCode'
    DECLARE @InvoiceBillAddrId nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceBillAddrId OUT, 'Invoice.BillAddr.Id'
    DECLARE @InvoiceTxnDate nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceTxnDate OUT, 'Invoice.TxnDate'
    DECLARE @InvoiceTotalAmt nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceTotalAmt OUT, 'Invoice.TotalAmt'
    DECLARE @InvoiceCustomerRefName nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceCustomerRefName OUT, 'Invoice.CustomerRef.name'
    DECLARE @InvoiceCustomerRefValue nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceCustomerRefValue OUT, 'Invoice.CustomerRef.value'
    DECLARE @InvoiceShipAddrCity nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrCity OUT, 'Invoice.ShipAddr.City'
    DECLARE @InvoiceShipAddrLine1 nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrLine1 OUT, 'Invoice.ShipAddr.Line1'
    DECLARE @InvoiceShipAddrPostalCode nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrPostalCode OUT, 'Invoice.ShipAddr.PostalCode'
    DECLARE @InvoiceShipAddrLat nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrLat OUT, 'Invoice.ShipAddr.Lat'
    DECLARE @InvoiceShipAddrLong nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrLong OUT, 'Invoice.ShipAddr.Long'
    DECLARE @InvoiceShipAddrCountrySubDivisionCode nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrCountrySubDivisionCode OUT, 'Invoice.ShipAddr.CountrySubDivisionCode'
    DECLARE @InvoiceShipAddrId nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceShipAddrId OUT, 'Invoice.ShipAddr.Id'
    DECLARE @InvoiceDueDate nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceDueDate OUT, 'Invoice.DueDate'
    DECLARE @InvoicePrintStatus nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoicePrintStatus OUT, 'Invoice.PrintStatus'
    DECLARE @InvoiceDeposit int
    EXEC sp_OAMethod @jsonResponse, 'IntOf', @InvoiceDeposit OUT, 'Invoice.Deposit'
    DECLARE @InvoiceSparse int
    EXEC sp_OAMethod @jsonResponse, 'BoolOf', @InvoiceSparse OUT, 'Invoice.sparse'
    DECLARE @InvoiceEmailStatus nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceEmailStatus OUT, 'Invoice.EmailStatus'
    DECLARE @InvoiceApplyTaxAfterDiscount int
    EXEC sp_OAMethod @jsonResponse, 'BoolOf', @InvoiceApplyTaxAfterDiscount OUT, 'Invoice.ApplyTaxAfterDiscount'
    DECLARE @InvoiceId nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceId OUT, 'Invoice.Id'
    DECLARE @InvoiceTxnTaxDetailTotalTax int
    EXEC sp_OAMethod @jsonResponse, 'IntOf', @InvoiceTxnTaxDetailTotalTax OUT, 'Invoice.TxnTaxDetail.TotalTax'
    DECLARE @InvoiceMetaDataCreateTime nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceMetaDataCreateTime OUT, 'Invoice.MetaData.CreateTime'
    DECLARE @InvoiceMetaDataLastUpdatedTime nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @InvoiceMetaDataLastUpdatedTime OUT, 'Invoice.MetaData.LastUpdatedTime'
    DECLARE @time nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @time OUT, 'time'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'Invoice.LinkedTxn'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonResponse, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'Invoice.Line'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonResponse, 'I', @i
        EXEC sp_OAMethod @jsonResponse, 'IntOf', @LineNum OUT, 'Invoice.Line[i].LineNum'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @Amount OUT, 'Invoice.Line[i].Amount'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @SalesItemLineDetailTaxCodeRefValue OUT, 'Invoice.Line[i].SalesItemLineDetail.TaxCodeRef.value'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @SalesItemLineDetailItemRefName OUT, 'Invoice.Line[i].SalesItemLineDetail.ItemRef.name'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @SalesItemLineDetailItemRefValue OUT, 'Invoice.Line[i].SalesItemLineDetail.ItemRef.value'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @Id OUT, 'Invoice.Line[i].Id'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @DetailType OUT, 'Invoice.Line[i].DetailType'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'Invoice.CustomField'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonResponse, 'I', @i
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @DefinitionId OUT, 'Invoice.CustomField[i].DefinitionId'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @invType OUT, 'Invoice.CustomField[i].Type'
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @Name OUT, 'Invoice.CustomField[i].Name'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbAuth
    EXEC @hr = sp_OADestroy @jsonReq
    EXEC @hr = sp_OADestroy @sbRequestBody
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jsonResponse


END
GO