Purchase - Update
Update a QuickBooks Online purchase 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_CompanyPurchase_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','purchase' 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 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Body varchar(MAX) SET @Body = '{ "AccountRef": { "value": "35", "name": "Checking" }, "PaymentType": "Check", "TotalAmt": 10.0, "PurchaseEx": { "any": [ { "name": "{http://schema.intuit.com/finance/v3}NameValue", "declaredType": "com.intuit.schema.finance.v3.NameValue", "scope": "javax.xml.bind.JAXBElement$GlobalScope", "value": { "Name": "TxnType", "Value": "54" }, "nil": false, "globalScope": true, "typeSubstituted": false } ] }, "domain": "QBO", "sparse": false, "Id": "165", "SyncToken": "0", "MetaData": { "CreateTime": "2017-06-04T09:11:33-07:00", "LastUpdatedTime": "2017-06-04T09:11:33-07:00" }, "CustomField": [], "TxnDate": "2017-06-04", "PrivateNote": "Added an updated private note via update.", "Line": [ { "Id": "1", "Amount": 10.0, "DetailType": "AccountBasedExpenseLineDetail", "AccountBasedExpenseLineDetail": { "AccountRef": { "value": "13", "name": "Meals and Entertainment" }, "BillableStatus": "NotBillable", "TaxCodeRef": { "value": "NON" } } } ] }' EXEC usp_QuickBooks_v3_CompanyPurchase_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/Purchase' SELECT T.C.value(N'@Id', N'nvarchar(MAX)') AS [Id] ,T.C.value(N'AccountRef[1]/@value', N'nvarchar(MAX)') AS [AccountRef_value] ,T.C.value(N'AccountRef[1]/@name', N'nvarchar(MAX)') AS [AccountRef_name] ,T.C.value(N'@PaymentType', N'nvarchar(MAX)') AS [PaymentType] ,T.C.value(N'@TotalAmt', N'nvarchar(MAX)') AS [TotalAmt] ,T.C.value(N'@TxnDate', N'nvarchar(MAX)') AS [TxnDate] FROM @X.nodes(N'/JsonObject/Purchase') T(C) END |
|
1 2 3 4 5 6 |
Id AccountRef_value AccountRef_name PaymentType TotalAmt TxnDate ------- ----------------- ---------------- -------------- ------------ ----------- 165 35 Checking Check 10.00 2017-06-04 |
- 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.