Examples
- Creating a purchase order
- Creating an invoice
- Creating a bill
- Creating a blank Invoice
- Refunding an existing credit memo
- Creating multiple invoices with multiple line items
- Creating a vendor credit
- Creating an estimate
- Creating a credit memo
- Writing a deposit
- Converting a sales order into an invoice
- Creating a sales receipt
- Creating a sales order
- Applying payment to an invoice
-
Inserting an opening balance in the
Customer
table - Apply discounts to existing invoices
- Getting a list of voided invoices
Creating a purchase order
These SQL INSERT
statements create a purchase order with 3 lines.
-- Create a purchase order with 1 line:
INSERT INTO
"PurchaseOrderLine" (
"VendorRef_ListID",
"RefNumber",
"PurchaseOrderLine_ItemRef_ListID",
"PurchaseOrderLine_Desc",
"PurchaseOrderLine_Quantity",
"PurchaseOrderLine_Rate",
"PurchaseOrderLine_Amount",
"PurchaseOrderLine_CustomerRef_ListID"
)
VALUES
(
'80000077-1356973501',
'1',
'80000086-1546265999',
'See Attached 1',
1.0,
1.0,
1.11,
'80000005-1356973498'
)
-- Add second line using cached ID
INSERT INTO
"PurchaseOrderLine" (
"PurchaseOrderLine_ItemRef_ListID",
"PurchaseOrderLine_Desc",
"PurchaseOrderLine_Quantity",
"PurchaseOrderLine_Rate",
"PurchaseOrderLine_Amount",
"PurchaseOrderLine_CustomerRef_ListID",
"RQUseCachedID"
)
VALUES
(
'80000086-1546265999',
'See Attached 2',
2.0,
2.0,
2.22,
'80000005-1356973498',
1
)
-- Add third line using cached ID
INSERT INTO
"PurchaseOrderLine" (
"PurchaseOrderLine_ItemRef_ListID",
"PurchaseOrderLine_Desc",
"PurchaseOrderLine_Quantity",
"PurchaseOrderLine_Rate",
"PurchaseOrderLine_Amount",
"PurchaseOrderLine_CustomerRef_ListID",
"RQUseCachedID"
)
VALUES
(
'80000086-1546265999',
'See Attached 3',
3.0,
3.0,
3.33,
'80000005-1356973498',
1
)
Creating an invoice
In this example, customer Ernesto Natiello is invoiced for a storage shed:
-- Find out the necessary information for the invoice:
SELECT
ListID
FROM
Customer
WHERE
Name = 'Natiello, Ernesto' "ListID" "240000-933272658"
SELECT
ListID
FROM
Account
WHERE
Name = 'Accounts Receivable' "ListID" "40000-933270541"
SELECT DISTINCT
InvoiceLineGroup_ItemGroupRef_ListID
FROM
InvoiceLine
WHERE
InvoiceLineGroup_ItemGroupRef_FullName = 'A2 Custom Storage Shed' "InvoiceLineGroup_ItemGroupRef_ListID" "410000-1071530396"
-- Invoice the customer:
INSERT INTO
InvoiceLine (
"CustomerRef_ListID",
"ARAccountRef_ListID",
"InvoiceLineGroup_ItemGroupRef_ListID",
"InvoiceLineGroup_Quantity"
)
VALUES
(
'240000-933272658',
'40000-933270541',
'410000-1071530396',
1
)
UPDATE InvoiceLine
SET
InvoiceLine_Rate = 1200
WHERE
ID = '21D09-1608033094|21D0C-1608033094|21D0B-1608033094'
UPDATE InvoiceLine
SET
InvoiceLine_Rate = 799
WHERE
ID = '21D09-1608033094|21D0D-1608033094|21D0B-1608033094'
Creating a bill
-- Create a bill with a group:
INSERT INTO
BillItemLine (
"VendorRef_FullName",
"RefNumber",
"ItemGroupLine_ItemGroupRef_FullName"
)
VALUES
('C.U. Electric', 10000, 'Room Addition/Remodel')
-- Add the line items.
INSERT INTO
BillItemLine (
"VendorRef_FullName",
"RefNumber",
"ItemGroupLine_ItemGroupRef_FullName"
)
VALUES
('Bank of Anycity', 666, 'A1 Custom Storage Shed')
INSERT INTO
BillItemLine (
"ItemGroupLine_ItemGroupRef_FullName",
"RQUSeCachedID"
)
VALUES
('A2 Custom Storage Shed', 1)
INSERT INTO
BillItemLine ("ItemLine_ItemRef_FullName", "RQUseCachedID")
values
('Hardware', 1)
Creating a blank invoice
-- Create an invoice with no line items:
INSERT INTO
InvoiceLine (
CustomerRef_FullName,
InvoiceLine_ItemRef_FullName,
RefNumber
)
VALUES
(
'Melton, Johnny:Dental office',
'Installation',
'1079'
)
Refunding an existing credit memo
-- The payment to be refunded:
SELECT
TotalAmount,
PaymentMethodRef_FullName
FROM
ReceivePayment
where
customerRef_FullName = 'Adwin Ko' "TotalAmount",
"PaymentMethodRef_FullName" 373.65,
"MasterCard"
-- Create a credit memo for a partial refund:
INSERT INTO
CreditMemoLine (
"CustomerRef_FullName",
"ARAccountRef_FullName",
"TemplateRef_FullName",
"CreditMemoLine_ItemRef_FullName",
"CreditMemoLine_Amount"
)
VALUES
(
'Adwin Ko',
'Accounts Receivable',
'Custom Credit Note',
'Service',
50.00
)
-- Get the transaction ID for the credit memo:
SELECT
TxnID
FROM
CreditMemoLine
WHERE
CustomerRef_FullName = 'Adwin Ko' "TxnID" "6C8F-1546250338"
-- Apply the refund:
INSERT INTO
ARRefundCreditCardRefundAppliedTo (
"CustomerRef_FullName",
"RefundFromAccountRef_FullName",
"PaymentMethodRef_FullName",
"RefundAppliedToTxn_TxnID",
"RefundAppliedToTxn_RefundAmount"
)
VALUES
(
'Adwin Ko',
'Undeposited Funds',
'MasterCard',
'6C8F-1546250338',
50.00
)
SELECT
TotalAmount
FROM
ARRefundCreditCardRefundAppliedTo
WHERE
CustomerRef_FullName = 'Adwin Ko' "TotalAmount" 50
-- Note RQUseCacheID is only supported if AUTOCOMMIT is OFF. RefundAppliedToTxn_TxnID values must be unique within
-- the batch in this case. If AUTOCOMMIT is ON, RQUseCacheID is not supported. Each INSERT is independent.
Creating multiple invoices with multiple line items
-- Add an inventory item:
INSERT INTO
ItemInventory (
"Name",
"IncomeAccountRef_FullName",
"COGSAccountRef_FullName",
"AssetAccountRef_FullName"
)
Values
(
'Boiler',
'Services Income',
'Parts and Materials',
'Fixed Assets'
)
SELECT
ListID
FROM
xCache
WHERE
TableName = 'ItemInventory'
8000008C-1546266630
-- Add an inventory sub item:
INSERT INTO
ItemInventory (
"Name",
"IncomeAccountRef_FullName",
"COGSAccountRef_FullName",
"AssetAccountRef_FullName",
"ParentRef_ListID"
)
Values
(
'Stay',
'Services Income',
'Parts and Materials',
'Fixed Assets',
'8000008C-1546266630'
)
Creating a vendor credit
INSERT INTO
VendorCreditItemLine (
"VendorRef_Fullname",
"ItemLine_ItemRef_FullName",
"ItemLine_Cost",
"ItemLine_Amount"
)
VALUES
(
'Brakes by Hickey',
'Special Order Part',
100.00,
45.00
)
Creating an estimate
-- Get the ListID for the item which the estimate is being provided for:
SELECT
ListID
FROM
ItemService
WHERE
Name = 'Vintage Restoration' "ListID" "8000007E-1264403363"
SELECT
ListID
FROM
Customer
WHERE
FullName = 'Adwin Ko' "ListID" "80000004-1356973498"
-- Create a single line estimate for 'Vintage Restoration':
INSERT INTO
EstimateLine (
"CustomerRef_ListID",
"EstimateLine_ItemRef_ListID",
"EstimateLine_Quantity",
"EstimateLine_Amount"
)
VALUES
(
'80000004-1356973498',
'8000007E-1264403363',
1,
600
)
SELECT
ID,
CustomerRef_FullName,
SubTotal,
TotalAmount,
EstimateLineType,
EstimateLine_TxnLineID,
Estimateline_ItemRef_FullName,
Estimateline_Desc,
Estimateline_Quantity
FROM
EstimateLine
WHERE
TxnID IN (
SELECT
ID
FROM
xCache
WHERE
TableName = 'Estimate'
)
"ID", "CustomerRef_FullName", "Subtotal", "TotalAmount", "EstimateLineType", "EstimateLine_TxnLineID", "EstimateLine_ItemRef_FullName", "EstimateLine_Desc", "EstimateLine_Quantity" "6C9A-1546269526|6C9C-1546269526|", "Adwin Ko", 600, 703.95, "Line", "6C9C-1546269526", "Vintage Restoration", "Complete", 1
-- Add another line to this estimate:
INSERT INTO
EstimateLine (
"EstimateLine_ItemRef_FullName",
"EstimateLine_Quantity",
"EstimateLine_Amount",
"RQUseCachedID"
)
VALUES
('Parts Sourcing', 3, 300, 1)
"ID", "CustomerRef_FullName", "Subtotal", "TotalAmount", "EstimateLineType", "EstimateLine_TxnLineID", "EstimateLine_ItemRef_FullName", "EstimateLine_Desc", "EstimateLine_Quantity" "6C9A-1546269526|6C9E-1546269526|", "Adwin Ko", 899.99, 1055.91, "Line", "6C9E-1546269526", "Parts Sourcing", "Complete Care", 3 "6C9A-1546269526|6C9C-1546269526|", "Adwin Ko", 899.99, 1055.91, "Line", "6C9C-1546269526", "Vintage Restoration", "Complete", 1
-- This multi-line estimate was created with two seperate INSERT statements (independent transactions: Autocommit OFF).
-- Behind the scenes, the QuickBooks ODBC driver does an UPDATE when processing the second INSERT, and so the second item
-- appears before the first item in the EstimateLine table.
-- Create estimate with three lines with one INSERT:
-- AutoCommit is OFF
INSERT INTO
EstimateLine (
"CustomerRef_FullName",
"EstimateLine_ItemRef_ListID",
"EstimateLine_Quantity",
"EstimateLine_Amount"
)
VALUES
('Adwin Ko', '80000019-1356973474', 1, 100)
INSERT INTO
EstimateLine (
"EstimateLine_ItemRef_FullName",
"EstimateLine_Quantity",
"EstimateLine_Amount",
"RQUseCachedID"
)
VALUES
('Motorcar Detailing', 3, 300, 1)
INSERT INTO
EstimateLine (
"EstimateLine_ItemRef_FullName",
"EstimateLine_Quantity",
"EstimateLine_Amount",
"RQUseCachedID"
)
VALUES
('Oil, Filter, Lubrication', 1, 120, 1)
-- EndTransaction
SELECT
ID,
CustomerRef_FullName,
SubTotal,
TotalAmount,
EstimateLineType,
EstimateLine_TxnLineID,
Estimateline_ItemRef_FullName,
Estimateline_Desc,
Estimateline_Quantity
FROM
EstimateLine
WHERE
TxnID IN (
SELECT
ID
FROM
xCache
WHERE
TableName = 'Estimate'
)
Row ID CustomerRef_FullName Subtotal TotalAmount EstimateLineType EstimateLine_TxnLineID EstimateLine_ItemRef_FullName EstimateLine_Desc EstimateLine_Quantity 1 69B1-1546272190|69B3-1546272190| Adwin Ko 520 611 Line 69B3-1546272190 Service Repair Service 1 2 69B1-1546272190|69B4-1546272190| Adwin Ko 520 611 Line 69B4-1546272190 Motorcar Detailing Complete Care 3 3 69B1-1546272190|69B5-1546272190| Adwin Ko 520 611 Line 69B5-1546272190 Oil, Filter, Lubrication 1
Creating a credit memo
-- Create a credit memo for a partial refund:
INSERT INTO
CreditMemoLine (
"CustomerRef_FullName",
"ARAccountRef_FullName",
"TemplateRef_FullName",
"CreditMemoLine_ItemRef_FullName",
"CreditMemoLine_Amount"
)
VALUES
(
'Adwin Ko',
'Accounts Receivable',
'Custom Credit Note',
'Service',
50.00
)
-- See also: Refunding an Existing CreditMemo example.
Writing a deposit
SELECT
TxnID,
CustomerRef_FullName,
RefNumber,
Amount
FROM
ReceivePaymentToDeposit "59A8-1264411556",
"Adwin Ko",
<null>,
373.65
SELECT
ListID
FROM
Account
WHERE
Name = 'Customer Deposits Received' "ListID" "80000019-1356973473"
INSERT INTO
DepositLine (
DepositLine_PaymentTxnLineID,
DepositToAccountRef_FullName,
TxnDate
)
VALUES
('6C8F-1546250338', 'Undeposited Funds', {d'2017-01-15'})
Converting a sales order into an invoice
This example will not work with QuickBooks Pro, which returns: Error 3270 : 'Missing posting account'
.
SELECT
"CustomerRef_ListID",
"RefNumber",
{fn CONCAT('Estimate ', "RefNumber")} as "Memo",
"EstimateLine_ItemRef_ListID",
"EstimateLine_Desc",
"EstimateLine_Rate",
"EstimateLine_Amount",
"EstimateLine_SalesTaxCodeRef_ListID"
FROM
EstimateLine
WHERE
RefNumber = 267
AND EstimateLine_Desc = 'Granite Counter and Tile Installation Labor (by hour)' "CustomerRef_ListID",
"RefNumber",
"Memo",
"EstimateLine_ItemRef_ListID",
"EstimateLine_Desc",
"EstimateLine_Rate",
"EstimateLine_Amount",
"EstimateLine_SalesTaxCodeRef_ListID" "800000B3-1197778093",
"267",
"Estimate267",
"E0000-933272656",
"Granite Counter and Tile Installation Labor (by hour)",
25,
450,
"20000-999022286"
INSERT INTO
"SalesOrderLine" (
"CustomerRef_ListID",
"RefNumber",
"Memo",
"SalesOrderLine_ItemRef_ListID",
"SalesOrderLine_Desc",
"SalesOrderLine_Rate",
"SalesOrderLine_Amount",
"SalesOrderLine_SalesTaxCodeRef_ListID"
)
SELECT
"CustomerRef_ListID",
"RefNumber",
{fn CONCAT('Estimate ', "RefNumber")} AS "Memo",
"EstimateLine_ItemRef_ListID",
"EstimateLine_Desc",
"EstimateLine_Rate",
"EstimateLine_Amount",
"EstimateLine_SalesTaxCodeRef_ListID"
FROM
EstimateLine
WHERE
RefNumber = 267
AND EstimateLine_Desc = 'Granite Counter and Tile Installation Labor (by hour)'
Creating a sales receipt
-- Create a sales receipt for items in this group:
INSERT INTO
"SalesReceiptLine" (
"CustomerRef_FullName",
"RefNumber",
"SalesReceiptLineGroup_ItemGroupRef_FullName",
"SalesReceiptLineGroup_Quantity"
)
VALUES
('Retail Homeowners', '1072', 'Service Kit', 1)
INSERT INTO
"SalesReceiptLine" (
"SalesReceiptLine_ItemRef_FullName",
"SalesReceiptLine_Desc",
"SalesReceiptLine_Rate",
"SalesReceiptLine_Amount",
"SalesReceiptLine_SalesTaxCodeRef_FullName",
"RQUseCachedID"
)
VALUES
(
'DIY Brake Disk',
'DIY Brake Disk',
109.96,
2.00,
'Non',
1
)
INSERT INTO
"SalesReceiptLine" (
"SalesReceiptLine_ItemRef_FullName",
"SalesReceiptLine_Desc",
"SalesReceiptLine_Rate",
"SalesReceiptLine_Amount",
"SalesReceiptLine_SalesTaxCodeRef_FullName",
"RQUseCachedID"
)
VALUES
(
'DIY Brake Pad',
'DIY Brake Pad',
49.95,
2.00,
'Non',
1
)
SELECT
ID,
SalesReceiptLineType,
SalesReceiptLine_Desc,
SalesReceiptLine_Quantity
FROM
SalesReceiptLine
WHERE
TxnID = '9E9B-1608030646'
Row ID SalesReceiptLineType SalesReceiptLine_Desc SalesReceiptLine_Quantity 1 9E9B-1608030646|9EA3-1608030646| Line DIY Brake Pad 2 9E9B-1608030646|9EA2-1608030646| Line DIY Brake Disk 3 9E9B-1608030646|9E9E-1608030646|9E9D-1608030646 GroupLine DIY Kit for Brake Disk Change 0 4 9E9B-1608030646|9E9F-1608030646|9E9D-1608030646 GroupLine DIY Kit for Brake Fluid Change 1 5 9E9B-1608030646|9EA0-1608030646|9E9D-1608030646 GroupLine DIY Kit for Brake Pad Change 1
Create a sales order
-- Create SalesOrder with two item lines:
INSERT INTO "SalesOrderLine" (
"CustomerRef_FullName", "TemplateRef_FullName",
"RefNumber", "SalesOrderLine_ItemRef_FullName",
"SalesOrderLine_Quantity", "SalesOrderLine_Rate",
"SalesOrderLine_Amount", "SalesOrderLine_SalesTaxCodeRef_FullName"
)
VALUES
(
'Baker, Chris', 'Sales Order with Rep',
'1111', 'Cabinets:Light Pine', 1,
1, 150.00, 'Non'
)
SELECT
*
FROM
xCache
WHERE
TableName = 'SalesOrder'
Row TableName Type ID EditSequence 1 SalesOrder TxnID 21CF5-1608040644 1608040644
SELECT
TxnID,
TxnNumber,
CustomerRef_FullName,
TemplateRef_FullName,
RefNumber,
TermsRef_FullName,
SubTotal,
TotalAmount,
"Memo"
FROM
SalesOrderLine
WHERE
TXnID IN (
SELECT
ID
FROM
xCache
WHERE
TableName = 'SalesOrder'
)
Row TxnID TxnNumber CustomerRef_FullName TemplateRef_FullName RefNumber TermsRef_FullName SubTotal TotalAmount, "Memo" 1 21CF5-1608040644 1780 Baker, Chris Sales Order with Rep 1111 Net 30 150 150
INSERT INTO SalesOrderLine (
TxnID, EditSequence, Refnumber, SalesOrderLine_ItemRef_FullName,
SalesOrderLine_Quantity, SalesOrderLine_Rate,
SalesOrderLine_Amount, SalesOrderLine_SalesTaxCodeRef_FullName
)
VALUES
(
'21CF5-1608040644', '1608043435',
'222', 'Subs:Painting', 2, 100, 200,
'Non'
)
Applying payment to an invoice
SELECT
TxnID,
TxnNumber,
InvoiceLine_Amount,
InvoiceLine_ItemRef_FullName,
RefNumber
FROM
InvoiceLine
WHERE
CustomerRef_Fullname = 'Ecker Designs:Office Repairs' "TxnID",
"TxnNumber",
"InvoiceLine_Amount",
"InvoiceLine_ItemRef_FullName",
"RefNumber" "CEC-933782257",
243,
225,
"Permit",
"1086" "CEC-933782257",
243,
252,
"Removal",
"1086" "CEC-933782257",
243,
0,
"Framing",
"1086" "CEC-933782257",
243,
0,
"Subs:Drywall",
"1086" "CEC-933782257",
243,
0,
"Subs:Plumbing",
"1086" "CEC-933782257",
243,
0,
"Subs:Painting",
"1086" "CEC-933782257",
243,
0,
"Subs:Insulating",
"1086" "CEC-933782257",
243,
745,
"Lumber:Rough",
"1086" "CEC-933782257",
243,
175,
"Lumber:Trim",
"1086"
INSERT INTO
ReceivePaymentLine (
CustomerRef_FullName,
DepositToAccountRef_FullName,
TotalAmount,
AppliedToTxn_TxnID,
AppliedToTxn_PaymentAmount,
TxnDate,
"Memo"
)
VALUES
(
'Ecker Designs:Office Repairs',
'Checking',
225.00,
'CEC-933782257',
225.00,
{d'2017-01-25'},
'Permit'
)
SELECT
*
FROM
xCache
WHERE
TableName = 'ReceivePayment' "TableName",
"IDType",
"ID",
"EditSequence" "ReceivePayment",
"TxnID",
"21CF0-1608041008",
"1608041008"
SELECT
CustomerRef_FullName,
TotalAmount
FROM
ReceivePayment
WHERE
TxnID = '21CF0-1608041008' "CustomerRef_FullName",
"TotalAmount" "Ecker Designs:Office Repairs",
225
Inserting an opening balance in the Customer
table
INSERT INTO
customer (
"Name",
"FirstName",
"LastName",
"CompanyName",
"Contact",
"AccountNumber",
"BillAddress_Addr1",
"BillAddress_Addr2",
"BillAddress_Addr3",
"BillAddress_City",
"BillAddress_State",
"BillAddress_PostalCode",
"Phone",
"Fax",
"Email",
"TermsRef_ListID",
"SalesTaxCodeRef_ListID",
"PreferredPaymentMethodRef_FullName",
"CreditCardInfo_CreditCardNumber",
"CreditCardInfo_ExpirationMonth",
"CreditCardInfo_ExpirationYear",
"CreditCardInfo_NameOnCard",
"CreditCardInfo_CreditCardAddress",
"CreditCardInfo_CreditCardPostalCode",
"OpenBalance",
"JobStatus",
"JobStartDate",
"JobEndDate",
"JobDesc"
)
VALUES
(
'Amy''s Bird Sanctuary',
'Amy',
'Lauterbach',
'Amy''s Bird Sanctuary',
'Amy Lauterbach',
'123456',
'Amy''s Bird Sanctuary',
'4581 Finch St.',
'Suite 456',
'Bayshore',
'CA',
'94326',
'(650) 555-3311',
'(650) 555-3312',
'Birds@Intuit.com',
'10000-933272658',
'10000-999022286',
'Visa',
'4111111111111111',
06,
2020,
'Amy Lauterbach',
'4581 Finch St. Suite 456',
)
-- After inserting a value in the OpenBalance field, the value is moved to Balance field.
"TableName", "IDType", "ID", "EditSequence" "Customer", "ListID", "800000DC-1608027680", "1608027680"
SELECT
Name,
OpenBalance,
Balance
FROM
Customer
WHERE
ListID = '800000DC-1608027680' "Name",
"OpenBalance",
"Balance" "Amy's Bird Sanctuary",
<Null>,
0
Apply discounts to existing invoices
SELECT
ListID,
Name
From
Item
WHERE
Name LIKE '%iscount%' "2A0000-933272656",
"Discount"
INSERT INTO
InvoiceLine (
TxnID,
InvoiceLine_ItemRef_ListID,
InvoiceLine_Desc
)
VALUES
('3F45-1071505743', '2A0000-933272656', 'Discount')
SELECT
TOP 1 InvoiceLine_RatePercent
FROM
InvoiceLine
WHERE
TxnID = '3F45-1071505743' "InvoiceLine_RatePercent" -10
Getting a list of voided invoices
-- Get a list of voided invoices:
SELECT
RefNumber,
CustomerRef_FullName,
"Memo"
FROM
Invoice
WHERE
"Memo" like 'VOID:%'