An Example of Receiving Items against a Purchase Order

Note: Here, we will do a simple one-line purchase order example to understand the process.

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: Receive