How to create Inventory Adjustments using QODBC

Example of Creating One Inventory Adjustment with Three Lines

This example creates one Inventory Adjustment with three lines. Note the FQSaveToCache field, set to True except on the last one.

Note: You must be in single-user mode for this to work.

First Line

INSERT INTO "InventoryAdjustmentLine" ("AccountRefListID", "TxnDate", "RefNumber", "Memo", "InventoryAdjustmentLineItemRefListID", "InventoryAdjustmentLineQuantityAdjustmentNewQuantity", "FQSaveToCache") VALUES ('320000-933270542', {d'2003-12-15'}, '1', 'Memo 1', '160000-933272656', 200.0, 1)

Second Line

INSERT INTO "InventoryAdjustmentLine" ("AccountRefListID", "TxnDate", "RefNumber", "Memo", "InventoryAdjustmentLineItemRefListID", "InventoryAdjustmentLineValueAdjustmentNewValue", "FQSaveToCache") VALUES ('10000-933270541', {d'2003-12-15'}, '1', 'Memo 2', '450000-1071511428', 100.0, 1)

Third Line

INSERT INTO "InventoryAdjustmentLine" ("AccountRefListID", "TxnDate", "RefNumber", "Memo", "InventoryAdjustmentLineItemRefListID", "InventoryAdjustmentLineQuantityAdjustmentNewQuantity", "FQSaveToCache") VALUES ('10000-933270541', {d'2003-12-15'}, '1', 'Memo 3', '440001-1071511796', 30.0, 0)

Example of Adjusting the Value of a Stock Item without Adjusting the Stock Quantity

Value in QuickBooks

This item has a COGS Average Cost of $69.73429:

INSERT query in QODBC

Using QODBC, I adjusted the opening balance equity cost of the stock item by $250.00 using the following SQL insert statement:

INSERT INTO "InventoryAdjustmentLine" ("AccountRefFullName", "TxnDate", "RefNumber", "Memo",
"InventoryAdjustmentLineItemRefFullName", "InventoryAdjustmentLineValueAdjustmentNewValue", "FQSaveToCache")
VALUES ('Opening Bal Equity', {d'2007-05-23'}, '110', 'Value Adj 250', 'Wood Door:Exterior', 250.0, 0)

Result in QuickBooks

This item now has a COGS Average Cost of $106.69857:

Display in Inventory valuation Detail Report

The value adjustment details can be seen in the Inventory Valuation Detail:

Example of Adjusting the Quantity On Hand

Note: InventoryAdjustmentLine transactions CAN NOT be edited, but to continue my example, you can adjust the quantity on hand at any point by using the following adjustment example, and QuickBooks will automatically make all the value adjustments for you:

INSRET Query in QODBC

INSERT INTO "InventoryAdjustmentLine" ("AccountRefFullName", "TxnDate", "RefNumber", "Memo",
"InventoryAdjustmentLineItemRefFullName", "InventoryAdjustmentLineQuantityAdjustmentNewQuantity", "FQSaveToCache")
VALUES ('Opening Bal Equity', {d'2007-05-23'}, '110', 'Qty Adj 4', 'Wood Door:Exterior', 4, 0)

Result in QuickBooks

The on-hand quantity and average cost are adjusted regardless of the previous value in the date sequence:

Display in Inventory valuation Detail Report

Example of Setting Quantity On-hand and Asset Value

Using QODBC, you can override everything and set the quantity on hand and the value of the stock to whatever you want. In this example, I've reset the quantity on hand and the asset value by using this insert statement:

Query in QODBC

INSERT INTO "InventoryAdjustmentLine" ("AccountRefFullName", "TxnDate", "RefNumber", "Memo",
"InventoryAdjustmentLineItemRefFullName", "InventoryAdjustmentLineValueAdjustmentNewQuantity",
"InventoryAdjustmentLineValueAdjustmentNewValue", "FQSaveToCache")
VALUES ('Opening Bal Equity', {d'2007-12-16'}, '110', 'Qty Adj 10 Value 1200',
'Wood Door:Exterior', 10, 1200.0, 0)

Result in QuickBooks

Which in turn has changed the average cost to $1200.00 / 10 = $120.00:

Display in Inventory valuation Detail Report

QuickBooks SDK does not allow you to specify Site Location Name when you are using value adjustment.
Unfortunately, QuickBooks does not allow us to perform Quantity + Value + Site Location in a single command.

You can either use the value adjustment or the quantity adjustment.

Please try the following SQL statements

INSERT INTO inventoryadjustmentline
(AccountRefFullName, InventorySiteRefFullName, Memo, InventoryAdjustmentLineItemRefFullName,
InventoryAdjustmentLineValueAdjustmentNewQuantity, InventoryAdjustmentLineValueAdjustmentNewValue, FQsaveToCache)
VALUES ('INVENTORY SPECIAL ADJUSTMENT', 'PARTS AND MATERIALS', 'CNVRT', 'AF0311860531',
3, 174, 0)

Or

INSERT INTO inventoryadjustmentline
(AccountRefFullName, InventorySiteRefFullName, Memo, InventoryAdjustmentLineItemRefFullName,
InventoryAdjustmentLineValueAdjustmentNewQuantity, InventoryAdjustmentLineValueAdjustmentNewValue,
InventoryAdjustmentLineQuantityAdjustmentSiteLocRefFullName, FQsaveToCache)
VALUES ('INVENTORY SPECIAL ADJUSTMENT', 'PARTS AND MATERIALS', 'CNVRT', 'AF0311860531',
3, 174, 'PARTS AND MATERIALS:P5', 0)

Or

INSERT INTO inventoryadjustmentline
(AccountRefFullName, InventorySiteRefFullName, Memo, InventoryAdjustmentLineItemRefFullName,
InventoryAdjustmentLineQuantityAdjustmentNewQuantity, InventoryAdjustmentLineQuantityAdjustmentSiteLocRefFullName, FQsaveToCache)
VALUES ('INVENTORY SPECIAL ADJUSTMENT', 'PARTS AND MATERIALS', 'CNVRT', 'AF0311860531',
3, 'PARTS AND MATERIALS:P5' , 0)

Or

Insert into inventoryadjustmentline (AccountRefListID, InventorySiteRefLIstID, InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentNewQuantity, InventoryAdjustmentLineQuantityAdjustmentLotNumber,InventoryAdjustmentLineQuantityAdjustmentSiteLocRefFullName) VALUES ('1D0001-896819777', '80000005-1418667219', '4D0002-1044564193', 856,'3 3','San Jose Warehouse:S12')


To perform a quantity and value adjustment to a bin location:


INSERT INTO inventoryadjustmentline
(AccountRefFullName, InventorySiteRefFullName, Memo, InventoryAdjustmentLineItemRefFullName,
InventoryAdjustmentLineQuantityAdjustmentNewQuantity, InventoryAdjustmentLineQuantityAdjustmentSiteLocRefFullName, FQsaveToCache)
VALUES ('INVENTORY SPECIAL ADJUSTMENT', 'PARTS AND MATERIALS', 'CNVRT', 'AF0311860531',
3, 'PARTS AND MATERIALS:P5' , 0)