The header information in the api.quote view has a few more fields than are probably necessary, so we'll pick a subset of all of the fields. We might also want to send a little bit of summary information, like the number of line items in the quote and the expected total cost. Keep in mind that the only information we have on which to base the query comes from either the basic EDI tokens or information returned by a single query that we can define using those basic EDI tokens (see Section 8, “Emailing CRM Incidents” for an example). Fortunately the standard </docnumber> token is sufficient for our needs. Now we need to choose the columns we want. The only tricky part is estimating the total, including tax, for the quote:

-- This is not the most elegant query to solve the problem but it is easier to read than the alternatives
SELECT 'Quote', quote_number, quote_date, expire_date, sales_rep, tax_zone, tax_type, terms,
       billto_name, billto_address1, billto_address2,
       billto_city, billto_state, billto_country, billto_postal_code,
       shipto_name, shipto_address1, shipto_address2,
       shipto_city, shipto_state, shipto_country, shipto_postal_code,
       ship_via, currency, freight, misc_charge,
       (SELECT SUM(quitem_qtyord * quitem_price +
                   calculateTax(quhead_taxzone_id, quitem_taxtype_id, quhead_quotedate,
                                quhead_curr_id, quitem_qtyord * quitem_price))
        FROM quitem JOIN quhead ON (quitem_quhead_id=quhead_id)
        WHERE (quhead_number=quote_number)
       ) + misc_charge + freight AS total,
       (SELECT COUNT(*)
        FROM quitem JOIN quhead ON (quitem_quhead_id=quhead_id)
        WHERE (quhead_number=quote_number)) AS lines
FROM api.quote
WHERE (quote_number=<? value("docnumber") ?>);

The next step is to write a query to get the individual line items for the quote:

SELECT line_number, item_number, qty_ordered, qty_uom, net_unit_price, price_uom, scheduled_date, tax_type
FROM api.quoteline
WHERE (quote_number=<? value("docnumber") ?>)
ORDER BY line_number;

The same two queries used to get the Quote information can be modified slightly to get the Sales Order. We have to translate from the quote-related tables and views to sales order tables and views, change a couple of columns (e.g. Sales Orders don't have expiration dates), and account for the fact that Sales Order line items might have been cancelled:

-- This is not the most elegant query to solve the problem but it is easier to read than the alternatives
SELECT 'Sales Order', order_number, order_date, sales_rep, tax_zone, terms,
       billto_contact_name, billto_address1, billto_address2,
       billto_city, billto_state, billto_country, billto_postal_code,
       shipto_contact_first || ' ' || shipto_contact_last, shipto_address1, shipto_address2,
       shipto_city, shipto_state, shipto_country, shipto_postal_code,
       ship_via, currency, freight, misc_charge,
       (SELECT SUM(coitem_qtyord * coitem_price +
                   calculateTax(cohead_taxzone_id, coitem_taxtype_id, cohead_orderdate,
                                cohead_curr_id, coitem_qtyord * coitem_price))
        FROM coitem JOIN cohead ON (coitem_cohead_id=cohead_id)
        WHERE (cohead_number=order_number AND coitem_status <> 'X')
       ) + misc_charge + freight AS total,
       (SELECT COUNT(*)
        FROM coitem JOIN cohead ON (coitem_cohead_id=cohead_id)
        WHERE (cohead_number=order_number AND coitem_status <> 'X')) AS lines
FROM api.salesorder
WHERE (order_number=<? value("docnumber") ?>);

SELECT line_number, item_number, qty_ordered, qty_uom, net_unit_price, price_uom, scheduled_date, tax_type
FROM api.salesline
WHERE (order_number=<? value("docnumber") ?> AND status <> 'X')
ORDER BY line_number;

If your Customers want more or less data in their Sales Order EDI, you can easily add or subtract columns from these queries. Alternatively, you can rewrite them to use the cohead and coitem tables directly.

You could also build Invoices by picking and choosing particular columns like we did for Quotes and Sales Orders. We'll save some time by just sending all of the fields in the Invoice header:

-- This is very easy to read but is prone to change between releases of xTuple ERP
-- as columns are added and subtracted
SELECT 'Invoice', *,
       (SELECT SUM(invcitem_billed * invcitem_price +
                   calculateTax(invchead_taxzone_id, invcitem_taxtype_id, invchead_invcdate,
                                invchead_curr_id, invcitem_billed * invcitem_price))
        FROM invcitem JOIN invchead ON (invcitem_invchead_id=invchead_id)
        WHERE (invchead_invcnumber=invoice_number)
       ) + misc_charge + freight AS total,
       (SELECT COUNT(*)
        FROM invcitem JOIN invchead ON (invcitem_invchead_id=invchead_id)
        WHERE (invchead_invcnumber=invoice_number)) AS lines
FROM api.invoice
WHERE (invoice_number=<? value("docnumber") ?>);

SELECT line_number, item_number, qty_billed, qty_uom, net_unit_price, price_uom, tax_type
FROM api.invoiceline
WHERE (invoice_number=<? value("docnumber") ?>)
ORDER BY line_number;

As with the Sales Order queries, you can rewrite these queries to suit your own needs.