To understand the process, I will do a simple one line purchase order example.
FIRST TO CREATE THE PURCHASE ORDER
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)
which results in the following purchase order in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:
In QuickBooks you have two options when Receiving Inventory:-
- Receive Inventory with Bill
- Receive Inventory without Bill
NEXT, TO CREATE A ITEM RECEIPT FROM A PURCHASE ORDER
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.
The Purchase Order now appears as a Item Receipt in QuickBooks but is unlinked to the Purchase Order line at this stage.
TO CREATE A LINKED ITEM RECEIPT FROM A PURCHASE ORDER
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.
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".
| 
