InventoryQtyAdjustments
Create, update, and query QuickBooks POS Inventory Quantity Adjustments.
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 InventoryCostAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert
To create a new InventoryQtyAdjustment record, the Reason 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 InventoryQtyAdjustmentItems table as # columns. Note that ItemListId and ItemNewQuantity are required when adding an item.
The following example will insert a new InventoryQtyAdjustment with two items:
INSERT INTO InventoryQtyAdjustments (Reason, ItemsAggregate)
VALUES ('New Shipment', '<InventoryQtyAdjustmentItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemNewQuantity>10</ItemNewQuantity></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemNewQuantity>20</ItemNewQuantity></Row>
</InventoryQtyAdjustmentItems>')
Update
Any field that is not read-only can be updated.
When updating an InventoryQtyAdjustment record, item quantities can be adjusted via the ItemsAggregate column, just as in an insert. In such cases, the ItemListId column is required to identify the item being adjusted and ItemNewQuantity is required to specify the new quantity.
Note items cannot be removed from an InventoryQtyAdjustment record, but rather the cost can only be adjusted, so a record of adjustments for an item is always kept.
UPDATE InventoryQtyAdjustments SET ItemsAggregate='<InventoryQtyAdjustmentItems> <Row><ItemListId>-1000000000000000003</ItemListId><ItemNewQuantity>30</ItemNewQuantity></Row> </InventoryQtyAdjustmentItems>' WHERE TxnId='-1234567890123456789'
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 adjustment. |
| Comments | String | False | Range |
Contains a description of the transaction. |
| CostDifference | Decimal | True |
Difference in cost for the items. The value displayed is equal to the new cost minus the old cost. | |
| 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 memo), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected). |
| InventoryAdjustmentNumber | Double | True | Range |
The adjustment number, generated by QuickBooks POS. |
| InventoryAdjustmentSource | String | False | Single |
The action or functionality that created the adjustment. Possible values are Manual (manually created memo), Physical (physical inventory), and Assembly (assembly item). |
| ItemsCount | String | True | Range |
The number of line items in the request that added the transaction. |
| ItemsAggregate | String | False |
An aggregate of the line item data, which can be used to add a transaction and its line items. | |
| NewQuantity | Double | True | Range |
The new quantity of the items being adjusted. |
| OldQuantity | Double | True | Range |
The quantity of the items prior to the adjustment. |
| QtyDifference | Double | True |
Difference in quantity for the items. The value displayed is equal to NewQuantity minus OldQuantity. | |
| 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. |
| Reason | String | False | Range |
Enter the reason for the adjustment or select from the following predefined reasons: Cycl Cnt (cycle count), Vend Chg (vendor change), Prc Line (price line), Seas End (end of season), Annual, Shrink, Stolen, Damaged, and New Item. |
| 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 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. |
| 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) or 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. |