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 'curr' AS apselect_amount_xtnumericrole,
13 'curr' AS apselect_amount_base_xtnumericrole,
14 'curr' AS apselect_running_base_xtnumericrole,
15 0 AS apselect_running_base_xtrunninginit,
16 1 AS apselect_running_base_xtrunningrole
18 -- Vouchers and Debits
19 SELECT apopen_id, apselect_id,
20 (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bank,
21 (vend_number || '-' || vend_name) AS f_vendor,
22 apopen_docnumber, apopen_ponumber, apselect_amount,
23 apopen_invcnumber, apopen_doctype,
24 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) AS apselect_amount_base,
25 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) AS apselect_running_base,
26 currConcat(apselect_curr_id) AS currAbbr
27 FROM apopen, apselect, vendinfo, bankaccnt
28 WHERE ( (apopen_vend_id=vend_id)
29 AND (apselect_apopen_id=apopen_id)
30 AND (apselect_bankaccnt_id=bankaccnt_id)
31 AND (apopen_doctype IN ('V', 'D'))
32 <? if exists("bankaccntid") ?>
33 AND (bankaccnt_id=<? value("bankaccntid") ?>)
35 <? if exists("vend_id") ?>
36 AND (vend_id=<? value("vend_id") ?>)
37 <? elseif exists("vendtype_id") ?>
38 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
39 <? elseif exists("vendtype_pattern") ?>
40 AND (vend_vendtype_id IN (SELECT vendtype_id
42 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
47 SELECT apopen_id, apselect_id,
48 (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bank,
49 (vend_number || '-' || vend_name) AS f_vendor,
50 apopen_docnumber, apopen_ponumber, apselect_amount,
51 apopen_invcnumber, apopen_doctype,
52 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) AS apselect_amount_base,
53 currToBase(apselect_curr_id, apselect_amount, CURRENT_DATE) * -1.0 AS apselect_running_base,
54 currConcat(apselect_curr_id) AS currAbbr
55 FROM apopen, apselect, vendinfo, bankaccnt
56 WHERE ( (apopen_vend_id=vend_id)
57 AND (apselect_apopen_id=apopen_id)
58 AND (apselect_bankaccnt_id=bankaccnt_id)
59 AND (apopen_doctype='C')
60 <? if exists("bankaccntid") ?>
61 AND (bankaccnt_id=<? value("bankaccntid") ?>)
63 <? if exists("vend_id") ?>
64 AND (vend_id=<? value("vend_id") ?>)
65 <? elseif exists("vendtype_id") ?>
66 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
67 <? elseif exists("vendtype_pattern") ?>
68 AND (vend_vendtype_id IN (SELECT vendtype_id
70 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
73 ORDER BY f_bank, f_vendor, apopen_docnumber