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')
36 CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
37 THEN (calcInvoiceAmt(invchead_id,'M') / calcInvoiceAmt(invchead_id,'S'))
40 'percent' AS marginpercent_xtnumericrole,
41 'curr' AS margin_xtnumericrole,
42 'curr' AS extprice_xtnumericrole,
43 'curr' AS balance_xtnumericrole,
44 CASE WHEN (aropen_id IS NULL) THEN 'Unposted' END AS balance_qtdisplayrole
45 FROM invchead JOIN custinfo ON (cust_id=invchead_cust_id)
46 LEFT OUTER JOIN aropen ON ( (aropen_doctype='I') AND (aropen_docnumber=invchead_invcnumber) )
47 LEFT OUTER JOIN recur ON (invchead_recurring_invchead_id=recur_parent_id AND recur_parent_type='I')
49 <? if exists("unpostedOnly") ?>
50 AND (NOT invchead_posted)
52 <? if exists("postedOnly") ?>
55 <? if exists("recurringOnly") ?>
56 AND (invchead_recurring_invchead_id IS NOT NULL)
58 <? if exists("cust_id") ?>
59 AND (invchead_cust_id=<? value("cust_id") ?>)
61 <? if exists("startDate") ?>
62 AND (invchead_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
64 <? if exists("invc_pattern") ?>
65 AND (invchead_invcnumber ~ <? value("invc_pattern") ?>)
67 <? if exists("balanceOnly") ?>
68 AND (aropen_open AND (aropen_amount - aropen_paid > 0))
70 AND (checkInvoiceSitePrivs(invchead_id)) )
71 ORDER BY invchead_invcnumber