2 -- Name: clearedbalance
3 -- Notes: used by reconcileBankaccount
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 (COALESCE(SUM(amount),0.0) + <? value("begBal") ?>) AS cleared_amount,
8 <? value("endBal") ?> AS end_amount,
9 (<? value("endBal") ?> - (<? value("begBal") ?> + (COALESCE(SUM(amount),0.0)))) AS diff_amount,
10 round(<? value("endBal") ?> - (<? value("begBal") ?> + COALESCE(SUM(amount),0.0)), 2) AS diff_value
11 FROM ( SELECT CASE WHEN (gltrans_amount > 0) THEN bankrecitem_amount * -1.0
12 ELSE bankrecitem_amount END AS amount
13 FROM bankaccnt, gltrans, bankrecitem
14 WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)
15 AND (bankrecitem_source='GL')
16 AND (bankrecitem_source_id=gltrans_id)
17 AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
18 AND (bankrecitem_cleared)
19 AND (NOT gltrans_deleted)
21 AND (bankaccnt_id=<? value("bankaccntid") ?>) )
23 SELECT CASE WHEN (sltrans_amount > 0) THEN bankrecitem_amount * -1.0
24 ELSE bankrecitem_amount END AS amount
25 FROM bankaccnt, sltrans, bankrecitem
26 WHERE ((sltrans_accnt_id=bankaccnt_accnt_id)
27 AND (bankrecitem_source='SL')
28 AND (bankrecitem_source_id=sltrans_id)
29 AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
30 AND (bankrecitem_cleared)
32 AND (bankaccnt_id=<? value("bankaccntid") ?>) )
34 SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount
35 FROM bankadj, bankadjtype, bankrecitem
36 WHERE ( (bankrecitem_source='AD')
37 AND (bankrecitem_source_id=bankadj_id)
38 AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
39 AND (bankrecitem_cleared)
40 AND (bankadj_bankadjtype_id=bankadjtype_id)
41 AND (NOT bankadj_posted)
42 AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>) ) ) AS data;