3 <title>Bank Reconciliation History</title>
4 <name>BankrecHistory</name>
5 <description></description>
14 <topmargin>50</topmargin>
15 <bottommargin>50</bottommargin>
16 <rightmargin>50</rightmargin>
17 <leftmargin>50</leftmargin>
18 <querysource loadFromDb="true">
19 <name>reconciled</name>
20 <mqlgroup>bankrecHistory</mqlgroup>
21 <mqlname>reconciled</mqlname>
25 <sql>SELECT (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bankaccnt,
26 (formatDate(bankrec_opendate) || '-' || formatDate(bankrec_enddate)) AS f_bankrec,
27 bankrec_username AS f_username, formatDate(bankrec_postdate) AS f_postdate,
28 formatDate(bankrec_opendate) AS f_opendate,
29 formatDate(bankrec_enddate) AS f_enddate,
30 formatMoney(bankrec_openbal) AS f_openbal,
31 formatMoney(bankrec_endbal) AS f_endbal,
32 text('Opening Balance') AS f_opendescrip,
33 text('Ending Balance') AS f_enddescrip
34 FROM bankrec, bankaccnt
35 WHERE ((bankrec_bankaccnt_id=bankaccnt_id)
36 AND (bankrec_id=<? value("bankrec_id") ?>));</sql>
41 formatMoney(COALESCE(MAX(selrec.bankrec_endbal) +
42 SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date) * -1),0)) AS amount
43 -- SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date)),0)) AS amount
45 JOIN bankaccnt ON (bankaccnt_accnt_id = gltrans_accnt_id)
46 JOIN bankrec selrec ON ((selrec.bankrec_bankaccnt_id = bankaccnt_id)
47 AND (selrec.bankrec_id=<? value("bankrec_id") ?>))
48 LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
49 AND (bankrecitem_source_id=gltrans_id))
50 LEFT OUTER JOIN bankrec actrec ON (actrec.bankrec_id = bankrecitem_bankrec_id)
51 WHERE ((COALESCE(actrec.bankrec_postdate,endoftime()) > COALESCE(selrec.bankrec_postdate,now()))
52 AND (gltrans_doctype='CK'))</sql>
55 <name>ActualAmount</name>
57 formatMoney(COALESCE(MAX(selrec.bankrec_endbal) +
58 SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date) * -1),0)) AS amount
59 -- SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date)),0)) AS amount
61 JOIN bankaccnt ON (bankaccnt_accnt_id = gltrans_accnt_id)
62 JOIN bankrec selrec ON ((selrec.bankrec_bankaccnt_id = bankaccnt_id)
63 AND (selrec.bankrec_id=<? value("bankrec_id") ?>))
64 LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
66 AND (bankrecitem_source_id=gltrans_id))
67 LEFT OUTER JOIN bankrec actrec ON (actrec.bankrec_id = bankrecitem_bankrec_id)
68 WHERE (COALESCE(actrec.bankrec_postdate,endoftime()) > COALESCE(selrec.bankrec_postdate,now()))</sql>
70 <querysource loadFromDb="true">
71 <name>unreconciled</name>
72 <mqlgroup>bankrecHistory</mqlgroup>
73 <mqlname>unreconciled</mqlname>
87 <weight>normal</weight>
91 <string>Amount</string>
103 <weight>normal</weight>
107 <string>Posted By:</string>
119 <weight>bold</weight>
125 <column>f_opendescrip</column>
138 <weight>normal</weight>
142 <string>Doc. Number/Notes</string>
154 <weight>bold</weight>
160 <column>f_bankrec</column>
180 <weight>normal</weight>
184 <string>Date Posted:</string>
196 <weight>bold</weight>
202 <column>f_bankaccnt</column>
215 <weight>bold</weight>
221 <column>f_postdate</column>
234 <weight>normal</weight>
238 <string>Dist. Date</string>
250 <weight>normal</weight>
254 <string>Bank Rec. Post:</string>
266 <weight>bold</weight>
272 <column>f_opendate</column>
285 <weight>bold</weight>
291 <column>f_username</column>
304 <weight>normal</weight>
308 <string>Bark Account:</string>
320 <weight>bold</weight>
324 <string>Bank Reconciliation History</string>
336 <weight>bold</weight>
342 <column>f_openbal</column>
355 <weight>bold</weight>
359 <string>Reconciled</string>
385 <weight>normal</weight>
389 <string>Doc. Number/Notes</string>
401 <weight>normal</weight>
405 <string>Dist. Date</string>
417 <weight>normal</weight>
421 <string>Amount</string>
425 <name>reconciled</name>
427 <name>reconciled-group</name>
428 <column>amount_xtnumericrole</column>
441 <weight>bold</weight>
447 <column>f_endbal</column>
460 <weight>bold</weight>
466 <column>f_enddate</column>
479 <weight>bold</weight>
485 <column>f_enddescrip</column>
491 <name>reconciled-type</name>
492 <column>sort</column>
505 <weight>bold</weight>
510 <query>reconciled</query>
511 <column>type</column>
527 <weight>bold</weight>
532 <query>reconciled</query>
533 <column>amount</column>
535 <format builtin="true">qty</format>
536 <tracktotal subtotal="true"/>
548 <weight>bold</weight>
553 <query>reconciled</query>
554 <column>type</column>
567 <weight>normal</weight>
571 <string>TOTAL</string>
577 <query>reconciled</query>
590 <weight>bold</weight>
595 <query>reconciled</query>
596 <column>notes</column>
609 <weight>bold</weight>
614 <query>reconciled</query>
615 <column>f_amount</column>
628 <weight>bold</weight>
633 <query>reconciled</query>
634 <column>f_date</column>
647 <weight>bold</weight>
652 <query>reconciled</query>
653 <column>gltrans_docnumber</column>
659 <name>unreconciled</name>
661 <name>unreconciled-group</name>
662 <column>amount_xtnumericrole</column>
675 <weight>bold</weight>
679 <string>Unreconciled</string>
683 <height>136.499</height>
694 <weight>normal</weight>
698 <string>Checks in Circulation:</string>
705 <height>15.5</height>
710 <weight>bold</weight>
714 <string>General Legder Reconciliation</string>
726 <weight>normal</weight>
730 <string>Deposits in Circulation:</string>
742 <weight>normal</weight>
746 <string>Actual Account Balance:</string>
758 <weight>normal</weight>
762 <string>Ending Reconciled Balance:</string>
774 <weight>bold</weight>
780 <column>f_endbal</column>
793 <weight>bold</weight>
798 <query>unreconciled</query>
799 <column>rtcheck</column>
801 <format builtin="true">money</format>
814 <weight>normal</weight>
818 <string>Subtotal:</string>
830 <weight>bold</weight>
835 <query>Subtotal</query>
836 <column>amount</column>
849 <weight>bold</weight>
854 <query>unreconciled</query>
855 <column>rtdep</column>
857 <format builtin="true">money</format>
870 <weight>bold</weight>
875 <query>unreconciled</query>
876 <column>rtadj</column>
878 <format builtin="true">money</format>
891 <weight>bold</weight>
896 <query>ActualAmount</query>
897 <column>amount</column>
910 <weight>normal</weight>
914 <string>Adjustments in Circulation:</string>
919 <name>unreconciled-type</name>
920 <column>type</column>
922 <height>26.0233</height>
933 <weight>bold</weight>
938 <query>unreconciled</query>
939 <column>type</column>
946 <query>unreconciled</query>
948 <height>21.9767</height>
959 <weight>bold</weight>
964 <query>unreconciled</query>
965 <column>notes</column>
978 <weight>bold</weight>
983 <query>unreconciled</query>
984 <column>f_date</column>
997 <weight>bold</weight>
1002 <query>unreconciled</query>
1003 <column>gltrans_docnumber</column>
1016 <weight>bold</weight>
1021 <query>unreconciled</query>
1022 <column>f_amount</column>
1039 <weight>bold</weight>
1044 <query>Context Query</query>
1045 <column>page_number</column>
1058 <weight>normal</weight>
1062 <string>Report Date:</string>
1074 <weight>normal</weight>
1078 <string>Page:</string>
1090 <weight>bold</weight>
1095 <query>Context Query</query>
1096 <column>report_date</column>
1101 <height>143</height>