3 -- Notes: used by reprintInvoices, unpostedInvoices, dspRecurringInvoices
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- See www.xtuple.com/CPAL for the full text of the software license.
7 SELECT invchead_id, cust_id,
9 cust_number, cust_name,
10 (TEXT(cust_number) || ' - ' || cust_name) AS customer,
11 COALESCE(invchead_gldistdate, invchead_invcdate) AS gldistdate,
12 invoicetotal(invchead_id) AS extprice,
13 CASE WHEN (aropen_open) THEN (aropen_amount - aropen_paid)
16 (invchead_recurring_invchead_id IS NOT NULL) AS isRecurring,
19 CASE WHEN (recur_period='D') THEN <? value("day") ?>
20 WHEN (recur_period='W') THEN <? value("week") ?>
21 WHEN (recur_period='M') THEN <? value("month") ?>
22 WHEN (recur_period='Y') THEN <? value("year") ?>
23 WHEN (recur_period='m') THEN <? value("minute") ?>
24 WHEN (recur_period='H') THEN <? value("hour") ?>
25 ELSE <? value("none") ?>
26 END AS recur_period_qtdisplayrole,
28 <? if exists('getForm') ?>
29 invchead_invcnumber AS docnumber,
30 findCustomerForm(cust_id, 'I') AS reportname,
32 CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
33 THEN (calcInvoiceAmt(invchead_id,'M') / calcInvoiceAmt(invchead_id,'S'))
36 'percent' AS marginpercent_xtnumericrole,
37 'curr' AS extprice_xtnumericrole,
38 'curr' AS balance_xtnumericrole,
39 CASE WHEN (aropen_id IS NULL) THEN 'Unposted' END AS balance_qtdisplayrole
40 FROM invchead JOIN custinfo ON (cust_id=invchead_cust_id)
41 LEFT OUTER JOIN aropen ON ( (aropen_doctype='I') AND (aropen_docnumber=invchead_invcnumber) )
42 LEFT OUTER JOIN recur ON (invchead_recurring_invchead_id=recur_parent_id AND recur_parent_type='I')
44 <? if exists("unpostedOnly") ?>
45 AND (NOT invchead_posted)
47 <? if exists("postedOnly") ?>
50 <? if exists("recurringOnly") ?>
51 AND (invchead_recurring_invchead_id IS NOT NULL)
53 <? if exists("cust_id") ?>
54 AND (invchead_cust_id=<? value("cust_id") ?>)
56 <? if exists("startDate") ?>
57 AND (invchead_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
59 <? if exists("invc_pattern") ?>
60 AND (invchead_invcnumber ~ <? value("invc_pattern") ?>)
62 <? if exists("balanceOnly") ?>
63 AND (aropen_open AND (aropen_amount - aropen_paid > 0))
65 AND (checkInvoiceSitePrivs(invchead_id)) )
66 ORDER BY invchead_invcnumber