Create, update, and query QuickBooks POS Price Adjustments.
Table Specific Information
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 PriceAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
To create a new PriceAdjustment record, the PriceAdjustmentName 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 PriceAdjustmentItems table as # columns. Note that ItemListId is required when adding an item.
The following example will insert a new PriceAdjustment with two items:
INSERT INTO PriceAdjustments (PriceAdjustmentName, ItemsAggregate) VALUES ('New Adjustment', '<PriceAdjustmentItems> <Row><ItemListId>-1000000000000000001</ItemListId><ItemNewPrice>10.00</ItemNewPrice></Row> <Row><ItemListId>-1000000000000000002</ItemListId><ItemNewPrice>20.00</ItemNewPrice></Row> </PriceAdjustmentItems>')
Any field that is not read-only can be updated.
When updating a PriceAdjustment record, item prices can be added or modified via the ItemsAggregate column.
To modify an existing item in a PriceAdjustment record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.
To add a new item, the ItemListId that corresponds to the item to be added must be specified (just as in an insert).
Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of the existing items.
Modify Items Example: Total three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.
UPDATE PriceAdjustments SET ItemsAggregate='<PriceAdjustmentItems> <Row><ItemTxnLineId>1</ItemTxnLineId><ItemNewPrice>30.00</ItemNewPrice></Row> <Row><ItemTxnLineId>2</ItemTxnLineId></Row> </PriceAdjustmentItems>' WHERE TxnId='-1234567890123456789'
Add New Item Example: Existing items remain intact when adding new items.
UPDATE PriceAdjustments SET ItemsAggregate='<PriceAdjustmentItems> <Row><ItemListId>-1000000000000000003</ItemListId><ItemNewPrice>30.00</ItemNewPrice></Row> </PriceAdjustmentItems>' WHERE TxnId='-1234567890123456789'
The unique identifier, generated by QuickBooks POS.
The employee making the adjustment.
The employee who applied the price adjustment.
Contains a description of the transaction.
Date the price adjustment was applied.
Date the price adjustment was restored.
The number of line items in the insert that added the transaction.
An aggregate of the line item data, which can be used to add a transaction and its line items.
The name of the price adjustment.
The status of the price adjustment.
The suggested price level discount for the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
The employee who restored the price adjustment.
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 customer information were successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
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).
Custom fields returned from QuickBooks POS and formatted into XML.
When the transaction was created.
When the transaction was last modified.