JDBC Driver for QuickBooks POS

Build 22.0.8462

SalesReceiptItems

Create, update, and query QuickBooks POS Sale Order 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 SalesReceiptItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

Insert can be used to create a new Sales Receipt record or to add an item to an existing Sales Receipt record.

To create a new Sales Receipt record, the CustomerListId is required along with an Item. 

INSERT INTO SalesReceiptItems (SalesReceiptType, CustomerListId, SalesReceiptNumber, CashTenderAmount, ItemListId, ItemPrice, ItemQty) 
VALUES ('Sales', '-9876543210987654321', 'SR12345', 2.15, '-1000000000000000001', 2.00, 1)

To add an item to an existing record, the TxnId column of the Sales Receipt record that the item is to be added to and the ItemListId are required. 

INSERT INTO SalesReceiptItems (TxnId, CashTenderAmount, ItemListId, ItemPrice, ItemQty) 
VALUES ('-1234567890123456789', 2.15, '-1000000000000000001', 2.00, 1)

Columns

Name Type ReadOnly Filter Type Description
ID String True

Index line identifier, auto generated by CData.

TxnID String True Single

The unique identifier, generated by QBPOS.

CustomerListID String False Single

A reference to the customer.

Associate String False Range

The employee making the adjustment.

Cashier String False Range

Name of the employee making the transaction. Notice that this value is auto-filled with the logged-in employee name if logins are required.

Comments String False Range

A description of the transaction.

Discount Decimal False Range

The amount of a 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

The percentage discount applicable to the transaction. Discounts are applied to the order subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.

HistoryDocStatus String False Single

Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this is the corrected version of the original memo), Reversing (this memo caused an older memo to be reversed), Regular (the document has been corrected).

ItemsCount String True Range

The number of line items in the request that added the transaction to QBPOS.

PriceLevelNumber String False Single

The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.

PromoCode String False Range

A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.

QuickBooksFlag String False Single

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.

SalesOrderTxnID String False Single

A reference to the associated sales order, which will be automatically updated with changes to the sales receipt.

SalesReceiptNumber String False Range

A unique number assigned to the receipt by QBPOS at creation.

SalesReceiptType String False Single

The receipt type. Possible values are Sales, Return, Deposit, Refund, Payout, and Payin.

ShipDate Datetime False Range

The date the merchandise shipped.

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 the customer information was successfully updated. 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 the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.

TaxAmount String True Range

The tax amount, returned in the response to inserting a non-held transaction.

TaxCategory String False Range

The tax category (location).

TaxPercentage String False Range

The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.

TenderType String False Single

Identifies which payment method was used for customer payment in the transaction. Possible values are None, Cash, Check, CreditCard, DebitCard, GiftCard, Account, Gift, Deposit, and Split.

TipReceiver String False Range

The employee to whom the tip is to be paid.

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.

TrackingNumber String True Range

The number provided to customers by the shipping company to help them track merchandise location and progress during shipment.

TxnDate Date 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

Indicates the current state of the transaction. Possible values are Normal (indicates the transaction is complete) and Held (the transaction has not yet been finalized).

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 transaction was created.

TimeModified Datetime True Range

When the transaction was last modified.

BillingInformation_Salutation String True

A salutation, such as Mr., Mrs., etc.

BillingInformation_FirstName String True

The first name of the customer as stated in the address info.

BillingInformation_LastName String True

The last name of the customer as stated in the address info.

BillingInformation_CompanyName String True

The name of the company of the customer.

BillingInformation_Phone String True

The primary telephone number for the customer.

BillingInformation_Phone2 String True

An alternate telephone or fax number for the customer.

BillingInformation_Phone3 String True

An alternate telephone or fax number for the customer.

BillingInformation_Street String True

Street address of the billing address of the customer.

BillingInformation_Street2 String True

The second line of the street address in the billing address for the customer.

BillingInformation_City String True

City name for the billing address of the customer.

BillingInformation_State String True

State name for the billing address of the customer.

BillingInformation_PostalCode String True

Postal code for the billing address of the customer.

BillingInformation_Country String True

Country for the billing address of the customer.

ShippingInformation_AddressName String False

The name used to identify the shipping address of the customer.

ShippingInformation_CompanyName String False

The company name of the shipping address of the customer.

ShippingInformation_FullName String False

The full name of the recipient in the shipping address of the customer.

ShippingInformation_Phone String False

The primary telephone number for the shipping address of the customer.

ShippingInformation_Street String False

Street address of the shipping address of the customer.

ShippingInformation_Street2 String False

The second line of the street address of the shipping address of the customer.

ShippingInformation_City String False

City name for the shipping address of the customer.

ShippingInformation_State String False

State name for the shipping address of the customer.

ShippingInformation_PostalCode String False

Postal code for the shipping address of the customer.

ShippingInformation_Country String False

Country for the shipping address of the customer.

ShippingInformation_ShipBy String False

The shipping method used to send merchandise to a customer.

ShippingInformation_Shipping Decimal False

Indicates the amount to be paid for shipping.

AccountTenderAmount Decimal False

Amount paid by the customer.

AccountTipAmount Decimal False

The amount of the gratuity paid to the employee.

CashTenderAmount Decimal False

Cash amount paid by the customer.

CheckNumber String False

The number of the check used as payment.

CheckTenderAmount Decimal False

Check amount paid by the customer.

CreditCardName String False

The name of the credit card used in the transaction, for example, Visa.

CreditCardTenderAmount Decimal False

Credit card amount paid by the customer.

CreditCardTipAmount Decimal False

Credit card amount of the gratuity paid to the employee.

DebitCardCashback Decimal False

Debit card amount paid by the customer.

DebitCardTenderAmount Decimal False

Debit card amount paid by the customer.

DepositTenderAmount Decimal False

Deposit amount paid by the customer.

GiftCertificateNumber String False

Number of the gift certificate used for full or partial payment.

GiftTenderAmount Decimal False

Gift certificate amount paid by the customer.

GiftCardTenderAmount Decimal False

Gift card amount paid by the customer.

GiftCardTipAmount Decimal False

Gift card amount of the gratuity paid to the employee.

ItemListID# String False

The unique identifier for the item, generated by QuickBooks POS.

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.

ItemAssociate# String False

The employee making the adjustment.

ItemAttribute# String False

A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value can be 1-8 characters.

ItemCommission# Decimal False

The commission amount for the item, based on the item price and the Associates commission percent, if the item is eligible for commissions.

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 the catalog number of a vendor. Also can be used for additional information at the point of sale.

ItemDiscount# Decimal False

The amount of a discount applicable to the transaction. Discounts are applied to the transaction subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.

ItemDiscountPercent# Double False

A percentage discount applicable to the item. Discounts are applied to the transaction subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.

ItemDiscountType# String False

Allows you to specify the reason for an item discount. Discount types can be customized in the company preferences and used to filter reports.

ItemExtendedPrice# Decimal True

The extended price of a line item.

ItemExtendedTax# Decimal True

Total tax per line item.

ItemItemNumber# Double True

Unique number assigned to the item when it is added to QBPOS.

ItemNumberOfBaseUnits# Double True

The base unit, used to specify inventory quantity.

ItemPrice# Decimal False

The purchase or sales price of this item.

ItemPriceLevelNumber# String True

The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.

ItemQty# Double False

The quantity of the line item being ordered or transferred.

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 field can contain 1-8 characters.

ItemTaxAmount# String True

The tax amount for the item, returned in the response to inserting a non-held transaction.

ItemTaxCode# String False

The tax code of the item.

ItemTaxPercentage# String True

Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.

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 UPC/EAN/ISBN of the item. 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.

ItemWebDesc# String True

The description of the item for use online.

ItemManufacturer# String True

The manufacturer of the item.

ItemWeight# Double True

The weight of the item.

ItemWebSKU# String True

The SKU of the item for online use.

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