Vouchers

Create, update, delete, and query QuickBooks POS Vouchers.

Table Specific Information

Select

QuickBooks Point of Sale allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Single Filters

Single filters make direct comparisons by using the = comparison.

Range Filters

Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator.

To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available.

To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.

SELECT * FROM Vouchers WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

To create a new Voucher record, the VendorListId field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the VoucherItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Voucher with two items (note: 7.5% sales tax):

INSERT INTO Vouchers (VoucherType, VendorListId, InvoiceNumber, Fee, ItemsAggregate) 
VALUES ('Receiving', '-9876543210987654321', 'INV12345', 3.00, '<VoucherItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemCost>10.00</ItemPrice><ItemQtyReceived>25</ItemQtyReceived></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemCost>20.00</ItemPrice><ItemQtyReceived>25</ItemQtyReceived></Row>
</VoucherItems>')

Update

Modifying a voucher is not allowed by QuickBooks POS. Instead an insert can be performed and VoucherType set to the desired function, such as "Return".

Columns

Name	Type	ReadOnly	Filter Type	Description
TxnID [KEY]	String	True	Single	The unique identifier, generated by QuickBooks POS.
Associate	String	False	Range	The employee making the voucher transaction.
Comments	String	False	Range	A description of the voucher.
CompanyName	String	True	Range	The name of the business.
Discount	Decimal	False	Range	The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent	Double	False	Range	Enter a percentage discount applicable to the voucher. Discounts are applied to the voucher subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Fee	Decimal	False	Range	You can enter a fee using this field. Notice that fees entered on a voucher (such as a special handling fee) are not spread over the cost of individual items. When a receiving voucher references a voucher with a fee entered, the fee can be transferred to the voucher at that point.
Freight	String	False	Range	Price charged by the shipping carrier to move the merchandise between the stores involved in the transaction.
HistoryDocStatus	String	True	Single	Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of the original), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InvoiceDate	Datetime	False	Range	The date shown on the invoice of the vendor.
InvoiceDueDate	Datetime	False	Range	The date the invoice is due and payable.
InvoiceNumber	String	False	Range	The invoice number for the shipment.
ItemsCount	String	True	Range	The number of line items in request that added the voucher.
ItemsAggregate	String	False		An aggregate of the line item data, which can be used for adding a voucher and its line item data.
PayeeCode	String	True	Range	The vendor or agent to be paid for the merchandise.
PayeeListID	String	False	Single	The refrrence to the vendor who is to be paid for the merchandise being received via this receiving voucher.
PayeeName	String	True	Range	The full vendor or customer name returned from the QBPOS company that matches the PayeeCode value.
PurchaseOrderNumber	String	True	Range	The number you want to assign to the voucher. It should be unique. This number is automatically assigned to the voucher by QBPOS if left empty during the creation process.
PurchaseOrderTxnID	String	False	Single	In a receiving voucher, this field links the voucher to a purchase order so it can receive items against that purchase order.
QuickBooksFlag	String	False	Single	The value here displays the status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
StoreExchangeStatus	String	True	Single	In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the information about the customer was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber	Double	False	Range	This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal	Decimal	True	Range	The sum of extended item costs of the transaction before applying any discounts or fees.
TermsDiscount	Decimal	False	Range	The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays	Double	False	Range	The number of days after the invoice date in which payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays	Double	False	Range	Payment must be made within this number of days, counting from the invoice date.
Total	Decimal	True	Range	The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TotalQty	Double	True	Range	Total quantity of items being transferred.
TxnDate	Datetime	False	Range	The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState	String	False	Single	The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
VendorCode	String	True	Range	The code assigned to the vendor specified for the item.
VendorListID	String	False	Single	A reference to the vendor.
VoucherNumber	String	True	Range	A unique number assigned to the voucher by QBPOS at creation.
VoucherType	String	False	Single	The type of voucher you are creating. Possible values are Receiving and Return.
Workstation	Double	False	Range	Workstation from which the transaction was made.
CustomFieldsOwnerID	String	False	Multi	Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields	String	False		Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated	Datetime	True	Range	When the voucher was created.
TimeModified	Datetime	True	Range	When the voucher was last modified.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.

Build 22.0.8462