Estimate - Update
Update a QuickBooks Online estimate object using SQL Server
- See SQLHTTP easy setup for QuickBooks
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE usp_QuickBooks_v3_CompanyEstimate_Update( @Profile nvarchar(100), @Body varchar(MAX), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = 'https://sandbox-quickbooks.api.intuit.com/v3' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'company', '#RealmID','estimate' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_QuickBooks_v3_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @ContentType = 'application/json', @Accept = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Body varchar(MAX) SET @Body = '{ "domain": "QBO", "sparse": false, "Id": "156", "SyncToken": "1", "MetaData": { "CreateTime": "2017-06-01T15:42:26-07:00", "LastUpdatedTime": "2017-06-01T15:58:00-07:00" }, "CustomField": [{ "DefinitionId": "1", "Name": "Crew #", "Type": "StringType" }], "DocNumber": "1001", "TxnDate": "2017-06-01", "TxnStatus": "Closed", "LinkedTxn": [{ "TxnId": "103", "TxnType": "Invoice" }], "Line": [{ "Id": "1", "LineNum": 1, "Description": "Rock Fountain", "Amount": 275.0, "DetailType": "SalesItemLineDetail", "SalesItemLineDetail": { "ItemRef": { "value": "5", "name": "Rock Fountain" }, "UnitPrice": 275, "Qty": 1, "TaxCodeRef": { "value": "NON" } } }, { "Id": "2", "LineNum": 2, "Description": "Custom Design", "Amount": 262.5, "DetailType": "SalesItemLineDetail", "SalesItemLineDetail": { "ItemRef": { "value": "4", "name": "Design" }, "UnitPrice": 75, "Qty": 3.5, "TaxCodeRef": { "value": "NON" } } }, { "Id": "3", "LineNum": 3, "Description": "Fountain Pump", "Amount": 45.0, "DetailType": "SalesItemLineDetail", "SalesItemLineDetail": { "ItemRef": { "value": "11", "name": "Pump" }, "UnitPrice": 22.5, "Qty": 2, "TaxCodeRef": { "value": "NON" } } }, { "Amount": 582.5, "DetailType": "SubTotalLineDetail", "SubTotalLineDetail": {} }], "TxnTaxDetail": { "TotalTax": 0 }, "CustomerRef": { "value": "10", "name": "Geeta Kalapatapu" }, "CustomerMemo": { "value": "An updated memo via full update." }, "BillAddr": { "Id": "59", "Line1": "Geeta Kalapatapu", "Line2": "1987 Main St.", "Line3": "Middlefield, CA 94303" }, "ShipAddr": { "Id": "119", "Line1": "1987 Main St.", "City": "Middlefield", "CountrySubDivisionCode": "CA", "PostalCode": "94303" }, "TotalAmt": 582.5, "ApplyTaxAfterDiscount": false, "PrintStatus": "NeedToPrint", "EmailStatus": "NotSet", "BillEmail": { "Address": "Geeta@Kalapatapu.com" } }' EXEC usp_QuickBooks_v3_CompanyEstimate_Update @Profile = 'My Quickbooks', @Body = @Body, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/Estimate' SELECT T.C.value(N'@Id', N'nvarchar(MAX)') AS [Id] ,T.C.value(N'@SyncToken', N'nvarchar(MAX)') AS [SyncToken] ,T.C.value(N'@TxnStatus', N'nvarchar(MAX)') AS [TxnStatus] ,T.C.value(N'CustomerMemo[1]/@value', N'nvarchar(MAX)') AS [CustomerMemo_value] ,T.C.value(N'@TotalAmt', N'nvarchar(MAX)') AS [TotalAmt] FROM @X.nodes(N'/JsonObject/Estimate') T(C) END |
|
1 2 3 4 5 6 |
Id SyncToken TxnStatus CustomerMemo_value TotalAmt ------- ----------- ----------- --------------------------------- ----------- 156 1 Closed An updated memo via full update 582.50 |
- Account – Create
- Account – Fetch
- Account – Query
- Account – Update
- Attachable – Create
- Attachable – Delete
- Attachable – Fetch
- Attachable – Query
- Attachable – Update
- BatchRequest – Create
- Bill – Create
- Bill – Delete
- Bill – Fetch
- Bill – Query
- Bill – Update
- BillPayment – Create
- BillPayment – Delete
- BillPayment – Fetch
- BillPayment – Query
- BillPayment – Update
- Budget – Query
- Change Data Capture – Query
- Class – Create
- Class – Fetch
- Class – Query
- Class – Update
- CompanyInfo – Fetch
- CompanyInfo – Query
- CreditMemo – Create
- CreditMemo – Delete
- CreditMemo – Fetch
- CreditMemo – Query
- CreditMemo – Update
- Customer – Create
- Customer – Fetch
- Customer – Query
- Customer – Update
- Customer – Update (Sparse)
- Department – Create
- Department – Fetch
- Department – Query
- Department – Update
- Deposit – Create
- Deposit – Delete
- Deposit – Fetch
- Deposit – Query
- Deposit – Update
- Deposit – Update (Sparse)
- Employee – Create
- Employee – Fetch
- Employee – Query
- Employee – Update
- Estimate – Create
- Estimate – Delete
- Estimate – Fetch
- Estimate – Query
- Estimate – Send
- Estimate – Update
- Estimate – Update (Sparse)
- Invoice – Create
- Invoice – Delete
- Invoice – Fetch
- Invoice – Query
- Invoice – Send
- Invoice – Update
- Invoice – Update (Sparse)
- Item – Create
- Item – Fetch
- Item – Inactivate
- Item – Query
- Item – Update
- JournalEntry – Create
- JournalEntry – Delete
- JournalEntry – Fetch
- JournalEntry – Query
- JournalEntry – Update
- JournalEntry – Update (Sparse)
- Payment – Create
- Payment – Delete
- Payment – Fetch
- Payment – Query
- PaymentMethod – Create
- PaymentMethod – Fetch
- PaymentMethod – Query
- PaymentMethod – Update
- Preferences – Query
- Preferences – Read
- Preferences – Update
- Purchase – Create
- Purchase – Delete
- Purchase – Fetch
- Purchase – Query
- Purchase – Update
- Purchase Order – Update
- PurchaseOrder – Create
- PurchaseOrder – Delete
- PurchaseOrder – Fetch
- PurchaseOrder – Query
- RefundReceipt – Create
- RefundReceipt – Delete
- RefundReceipt – Fetch
- RefundReceipt – Query
- RefundReceipt – Update
- RefundReceipt – Update (Sparse)
- SalesReceipt – Create
- SalesReceipt – Delete
- SalesReceipt – Fetch
- SalesReceipt – Query
- SalesReceipt – Send
- SalesReceipt – Update
- SalesReceipt – Update (Sparse)
- TaxAgency – Create
- TaxAgency – Fetch
- TaxAgency – Query
- TaxCode – Fetch
- TaxCode – Query
- TaxRate – Fetch
- TaxRate – Query
- TaxService – Create
- Term – Create
- Term – Fetch
- Term – Query
- Term – Update
- TimeActivity – Create
- TimeActivity – Delete
- TimeActivity – Fetch
- TimeActivity – Query
- TimeActivity – Update
- Transfer – Create
- Transfer – Delete
- Transfer – Fetch
- Transfer – Query
- Transfer – Update
- Transfer – Update (Sparse)
- Vendor – Create
- Vendor – Fetch
- Vendor – Query
- Vendor – Update
- VendorCredit – Create
- VendorCredit – Delete
- VendorCredit – Fetch
- VendorCredit – Query
- VendorCredit – Update
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.