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', '201', '440001-1071511796',
'standard interior door frame', 1.0, 12.00, 12.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
RECEIVE INVENTORY WITH BILL: TO CREATE A BILL FROM A PURCHASE ORDER
When we're ready to receive the item (with the bill) we can read the Purchase Order table and insert it into the BillItemLine table like this:
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber",
"ItemLineItemRefListID", "ItemLineDesc",
"ItemLineQuantity", "ItemLineCost",
"ItemLineAmount", "ItemLineCustomerRefListID",
"FQSaveToCache")
Select "VendorRefListID", {fn CONCAT('B', "RefNumber")},
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
0 as "FQSaveToCache" from PurchaseOrderLine
where "VendorRefFullName" ='Perry Windows & Doors' and "RefNumber"='201'
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 Bill in QuickBooks but is unlinked to the Purchase Order line at this stage. (See further down below for more on how to create a linked Bill).
RECEIVE INVENTORY WITHOUT BILL: 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. (See: How do I receive Items against a Purchase Order? Receive Inventory without Bill? ItemReceipts for more details on how to link the Purchase Order instead).
RECEIVE INVENTORY WITH BILL: TO CREATE A LINKED BILL FROM A PURCHASE ORDER
When we're ready to receive the item (with the bill) we can read the Purchase Order table and insert it into the BillItemLine table as a linked Bill like this:
INSERT INTO "BillItemLine" ("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"='601'
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.
which results in the following linked Bill in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:
and because there was only one line in the example Purchase Order, the Purchase Order has also been marked, "RECEIVED IN FULL".
| 
