2 -- Name: selectedpayments
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.
8 CASE WHEN (apopen_doctype='V') THEN <? value("voucher") ?>
9 WHEN (apopen_doctype='D') THEN <? value("debitmemo") ?>
10 WHEN (apopen_doctype='C') THEN <? value("creditmemo") ?>
12 CASE WHEN (apopen_doctype='C') THEN 'emphasis' END AS doctype_qtforegroundrole,
13 'curr' AS apselect_amount_xtnumericrole,
14 'curr' AS apselect_amount_base_xtnumericrole,
15 'curr' AS apselect_running_base_xtnumericrole,
16 0 AS apselect_running_base_xtrunninginit,
17 1 AS apselect_running_base_xtrunningrole
19 -- Vouchers and Debits
20 SELECT apopen_id, apselect_id,
21 (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bank,
22 (vend_number || '-' || vend_name) AS f_vendor,
23 apopen_docnumber, apopen_ponumber, apselect_amount,
24 apopen_invcnumber, apopen_doctype,
25 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) AS apselect_amount_base,
26 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) AS apselect_running_base,
27 currConcat(apselect_curr_id) AS currAbbr
28 FROM apopen, apselect, vendinfo, bankaccnt
29 WHERE ( (apopen_vend_id=vend_id)
30 AND (apselect_apopen_id=apopen_id)
31 AND (apselect_bankaccnt_id=bankaccnt_id)
32 AND (apopen_doctype IN ('V', 'D'))
33 <? if exists("bankaccntid") ?>
34 AND (bankaccnt_id=<? value("bankaccntid") ?>)
36 <? if exists("vend_id") ?>
37 AND (vend_id=<? value("vend_id") ?>)
38 <? elseif exists("vendtype_id") ?>
39 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
40 <? elseif exists("vendtype_pattern") ?>
41 AND (vend_vendtype_id IN (SELECT vendtype_id
43 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
48 SELECT apopen_id, apselect_id,
49 (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bank,
50 (vend_number || '-' || vend_name) AS f_vendor,
51 apopen_docnumber, apopen_ponumber, -apselect_amount,
52 apopen_invcnumber, apopen_doctype,
53 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) * -1.0 AS apselect_amount_base,
54 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) * -1.0 AS apselect_running_base,
55 currConcat(apselect_curr_id) AS currAbbr
56 FROM apopen, apselect, vendinfo, bankaccnt
57 WHERE ( (apopen_vend_id=vend_id)
58 AND (apselect_apopen_id=apopen_id)
59 AND (apselect_bankaccnt_id=bankaccnt_id)
60 AND (apopen_doctype='C')
61 <? if exists("bankaccntid") ?>
62 AND (bankaccnt_id=<? value("bankaccntid") ?>)
64 <? if exists("vend_id") ?>
65 AND (vend_id=<? value("vend_id") ?>)
66 <? elseif exists("vendtype_id") ?>
67 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
68 <? elseif exists("vendtype_pattern") ?>
69 AND (vend_vendtype_id IN (SELECT vendtype_id
71 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
74 ORDER BY f_bank, f_vendor, apopen_docnumber