VoucherItems
Create and query QuickBooks POS Voucher Items.
Table Specific Information
Select
QuickBooks POS 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 VoucherItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert
Insert can be used to create a new Voucher record or to add an item to an existing Voucher record.
To create a new Voucher record, the VendorListId field is required.
INSERT INTO VoucherItems (VoucherType, VendorListId, InvoiceNumber, Fee, ItemListId, ItemCost, ItemQtyReceived)
VALUES ('Receiving', '-9876543210987654321', 'INV12345', 2.15, '-1000000000000000001', 20.00, 10)
To add an item to an existing record, the TxnId column of the Voucher that the item should be added to and the ItemListId are required.
INSERT INTO VoucherItems (TxnId, ItemListId, ItemPrice, ItemQtyReceived)
VALUES ('-1234567890123456789', '-1000000000000000001', 40.00, 5)
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. |
| 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 | Boolean | 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 | Double | 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. |
| ItemListID# | String | False |
The unique identifier, generated by QuickBooks POS, for the item. | |
| ItemALU# | String | False |
Alternate Lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups. | |
| ItemAttribute# | String | False |
A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters. | |
| ItemCost# | Decimal | False |
The average cost of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos. | |
| ItemDesc1# | String | False |
The principal item description field. This description is printed on receipts. | |
| ItemDesc2# | String | False |
Secondary description field usually used for any additional information to describe this particular item, such as a vendor's catalog number. Also can be used for additional information at the point of sale. | |
| ItemExtendedCost# | Decimal | False |
The quantity ordered multiplied by the item cost. | |
| ItemItemNumber# | Double | True |
Unique number assigned to the item when it is added to QBPOS. | |
| ItemNumberOfBaseUnits# | Double | True |
The base unit, used to track and report the inventory quantity. | |
| ItemOriginalOrderQty# | Double | False |
The quantity of the line item being ordered or transferred. | |
| ItemQtyReceived# | Double | True |
The item quantity received to date. | |
| ItemSerialNumber# | String | False |
Item serial numbers can be recorded in QBPOS for purposes of warranty tracking or meeting legal requirements. Once recorded, former documents can be quickly located by searching by serial number. | |
| ItemSize# | String | False |
The size of an item. This field can also be used to describe a second item attribute other than size if desired. This value must be 1-8 characters. | |
| ItemUnitOfMeasure# | String | False |
If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item. | |
| ItemUPC# | String | False |
The item's UPC/EAN/ISBN identifier. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field. |