[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 QODBCExample of Creating One Inventory Adjustment with Three LinesThis 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 QuantityValue 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",
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 HandNote: 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",
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 ValueUsing 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",
Result in QuickBooks Which in turn has changed the average cost to $1200.00 / 10 = $120.00:
Display in Inventory valuation Detail Report
| |
|
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]".
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