Knowledgebase
[QODBC-Desktop] How to receive Items against a Purchase Order
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Receiving Items against a Purchase Order

Note: To understand the process, here we will do a simple one line purchase order example.

CREATE THE PURCHASE ORDER

Query in QODBC

     The following SQL statement will create a new purchase order:

     INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
     "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
     "PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
     "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
     "FQSaveToCache") VALUES ('C0000-933272656', '401', '440001-1071511796',
     'standard interior door frame', 2.0, 24.00, 24.00, 'AB0000-1197756245', 0)

Result in QuickBooks

     Which results in the following purchase order in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:

Receive Inventory with/without Bill Instruction

Note: In QuickBooks, you have two options when Receiving Inventory:

          Receive Inventory with Bill Receive Inventory without Bill as follows:

CREATE AN ITEM RECEIPT FROM A PURCHASE ORDER

Query in QODBC

     When we're ready to receive the item (without a bill) we can read the Purchase Order table and insert it into the ItemReceiptItemLine table like this:

     INSERT INTO "ItemReceiptItemLine" ("VendorRefListID", "RefNumber",
     "Memo","ItemLineItemRefListID", "ItemLineDesc",
     "ItemLineQuantity", "ItemLineCost", "ItemLineAmount",
     "ItemLineCustomerRefListID","ItemLineBillableStatus","FQSaveToCache")
     Select "VendorRefListID", {fn CONCAT('ItemReceipt ', "RefNumber")},
     'Received items (bill to follow)', "PurchaseOrderLineItemRefListID",
     "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
     "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",'Billable',
     0 as "FQSaveToCache" from PurchaseOrderLine
     where "VendorRefFullName" ='Perry Windows & Doors' and "RefNumber"='401'
     and "PurchaseOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple purchase order lines you would set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the PurchaseOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

     The Purchase Order now appears as an Item Receipt in QuickBooks but is unlinked to the Purchase Order line at this stage.


CREATE A LINKED ITEM RECEIPT FROM A PURCHASE ORDER

Query in QODBC

     We can also receive the item (without a bill) by reading the Purchase Order table and inserting it into the ItemReceiptItemLine table as a linked Item Receipt like this instead:

    INSERT INTO "ItemReceiptItemLine" ("VendorRefListID", "RefNumber",
    "ItemLineLinkToTxnTxnID", "ItemLineLinkToTxnTxnLineID",
    "FQSaveToCache")
    Select "VendorRefListID", {fn CONCAT('POLink', "RefNumber")},

    "TxnID", "PurchaseOrderLineTxnLineID",
    0 as "FQSaveToCache" from PurchaseOrderLine
    where "VendorRefFullName" ='Perry Windows & Doors' and "RefNumber"='401'
    and "PurchaseOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple purchase order lines you would set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the PurchaseOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

     The Purchase Order now appears as a linked item Receipt in QuickBooks:

      and because there was only one line in the example Purchase Order, the Purchase Order has also been marked, "RECEIVED IN FULL".


Question and Answer

Question

     How to receive over the PO expected quantity?

Answer

     If you need to receive more than the quantity ordered, you should update the PurchaseOrderLineQuantity first as below:

     UPDATE PurchaseOrderLine SET PurchaseOrderLineQuantity = 3.0 where "VendorRefFullName" ='Perry Windows & Doors' and "RefNumber"='401' and "PurchaseOrderLineSeqNo"=1

 

Keywords : Recive

(137 vote(s))
Helpful
Not helpful

Comments (1)
Nina
20 September 2012 02:52 PM
Edit Sales Tax Rate in QBJuly 5th, 2010Many states and ltialioces have recently increased their sales tax rates. How can you be sure to collect and remit the correct amount in QuickBooks?Here are the instructions:While you could simply overwrite the rate on your existing sales tax item with the new percentage on the date it becomes effective, please consider the following warning from the QuickBooks Help Index:“Note: If the sales tax rate for an existing item changes, do not change the rate. Instead, create a new sales tax item with the new rate, and make the old sales tax item inactive. If you change an existing rate, it will impact reports and transactions that occurred prior to the rate change.”To do this task in QuickBooks Pro/Premier or Enterprise:1.Go to the Lists menu and click Item List.2.In the Item list, double-click the sales tax item you need to change.3.In the Edit Item window, if necessary, change the Sales Tax Name to reflect the existing percentage.4.Click the Item is inactive checkbox.5.Click OK.6.Next, Create a New Sales Tax Item. Go to Lists, Item List, New Item.A.Loading, please wait . .7.From the Type drop-down list, click Sales Tax Item.8.Enter a sales tax name, Specifying the Locality and the new rate.9.Enter a description for the way this sales tax item will appear as a line item on your sales forms.10. Enter the sales tax rate as a percentage.11. From the Tax Agency drop-down list, choose the vendor you’ve set up for paying the tax. Click OK.12. Finally, go to Edit, Preferences, Company Preferences, Sales Tax. Change “Your most common sales tax item” drop-down to the new Sales Tax Item that you just set up.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).