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
NEXT, 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.
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 instead 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".
|