Knowledgebase: Syntax
[QODBC-Desktop] How to create Inventory Adjustments using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

How to create Inventory Adjustments using QODBC

Example of Creating One Inventroy Adjustment with Three Lines

This example creates one inventoryadjustment 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 Adjustting 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 CANNOT be edited, but to contiune my example, you can adjust the quantity on hand at any point by using the following adjustment example and QuickBooks will automatically do 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 is adjusted regardless of the prevous value in the date sequence:

Display in Inventory valuation Detail Report

 

Example of Setting Quantity On-hand and Asset Value

Using QODBC you can overhide 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 as $1200.00 / 10 = $120.00:

Display in Inventory valuation Detail Report

(166 vote(s))
Helpful
Not helpful

Comments (2)
Jermaine Gray
08 September 2014 12:50 PM
Hello,
Am trying to insert inventory balance into the InventoryAdjustmentLine but am getting the following error.

OLE DB provider "MSDASQL" for linked server "QODBC" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "QODBC" could not INSERT INTO table "[QODBC]...[InventoryAdjustmentLine]".
Jack
08 September 2014 02:46 PM
Hi Jemaine,

I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).