<!DOCTYPE openRPTDef>
<report>
 <title>Bank Reconciliation History</title>
 <name>BankrecHistory</name>
 <description></description>
 <grid>
  <snap/>
  <show/>
  <x>0.05</x>
  <y>0.05</y>
 </grid>
 <size>Letter</size>
 <portrait/>
 <topmargin>50</topmargin>
 <bottommargin>50</bottommargin>
 <rightmargin>50</rightmargin>
 <leftmargin>50</leftmargin>
 <querysource loadFromDb="true">
  <name>reconciled</name>
  <mqlgroup>bankrecHistory</mqlgroup>
  <mqlname>reconciled</mqlname>
 </querysource>
 <querysource>
  <name>head</name>
  <sql>SELECT (bankaccnt_name || '-' || bankaccnt_descrip) AS f_bankaccnt,
       (formatDate(bankrec_opendate) || '-' || formatDate(bankrec_enddate)) AS f_bankrec,
       bankrec_username AS f_username, formatDate(bankrec_postdate) AS f_postdate,
       formatDate(bankrec_opendate) AS f_opendate,
       formatDate(bankrec_enddate) AS f_enddate,
       formatMoney(bankrec_openbal) AS f_openbal,
       formatMoney(bankrec_endbal) AS f_endbal,
       text('Opening Balance') AS f_opendescrip,
       text('Ending Balance') AS f_enddescrip
  FROM bankrec, bankaccnt
 WHERE ((bankrec_bankaccnt_id=bankaccnt_id)
   AND  (bankrec_id=&lt;? value("bankrec_id") ?>));</sql>
 </querysource>
 <querysource>
  <name>Subtotal</name>
  <sql>SELECT 
   formatMoney(COALESCE(MAX(selrec.bankrec_endbal) +
   SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date) * -1),0)) AS amount
--   SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date)),0)) AS amount
 FROM gltrans
   JOIN bankaccnt ON (bankaccnt_accnt_id = gltrans_accnt_id)
   JOIN bankrec selrec ON ((selrec.bankrec_bankaccnt_id = bankaccnt_id)
                       AND (selrec.bankrec_id=&lt;? value("bankrec_id") ?>))
   LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
                              AND (bankrecitem_source_id=gltrans_id))
   LEFT OUTER JOIN bankrec actrec ON (actrec.bankrec_id = bankrecitem_bankrec_id)
 WHERE ((COALESCE(actrec.bankrec_postdate,endoftime()) > COALESCE(selrec.bankrec_postdate,now()))
 AND (gltrans_doctype='CK'))</sql>
 </querysource>
 <querysource>
  <name>ActualAmount</name>
  <sql>SELECT 
   formatMoney(COALESCE(MAX(selrec.bankrec_endbal) +
   SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date) * -1),0)) AS amount
--   SUM(currtolocal(bankaccnt_curr_id,gltrans_amount,gltrans_date)),0)) AS amount
 FROM gltrans
   JOIN bankaccnt ON (bankaccnt_accnt_id = gltrans_accnt_id)
   JOIN bankrec selrec ON ((selrec.bankrec_bankaccnt_id = bankaccnt_id)
                       AND (selrec.bankrec_id=&lt;? value("bankrec_id") ?>))
   LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')

                              AND (bankrecitem_source_id=gltrans_id))
   LEFT OUTER JOIN bankrec actrec ON (actrec.bankrec_id = bankrecitem_bankrec_id)
 WHERE (COALESCE(actrec.bankrec_postdate,endoftime()) > COALESCE(selrec.bankrec_postdate,now()))</sql>
 </querysource>
 <querysource loadFromDb="true">
  <name>unreconciled</name>
  <mqlgroup>bankrecHistory</mqlgroup>
  <mqlname>unreconciled</mqlname>
 </querysource>
 <rpthead>
  <height>166</height>
  <label>
   <rect>
    <x>645</x>
    <y>100</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Amount</string>
  </label>
  <label>
   <rect>
    <x>370</x>
    <y>50</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Posted By:</string>
  </label>
  <field>
   <rect>
    <x>100</x>
    <y>148</y>
    <width>535</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_opendescrip</column>
   </data>
  </field>
  <label>
   <rect>
    <x>100</x>
    <y>100</y>
    <width>150</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Doc. Number/Notes</string>
  </label>
  <field>
   <rect>
    <x>145</x>
    <y>65</y>
    <width>220</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_bankrec</column>
   </data>
  </field>
  <line>
   <xstart>5</xstart>
   <ystart>115</ystart>
   <xend>745</xend>
   <yend>115</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>370</x>
    <y>65</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Date Posted:</string>
  </label>
  <field>
   <rect>
    <x>145</x>
    <y>50</y>
    <width>245</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_bankaccnt</column>
   </data>
  </field>
  <field>
   <rect>
    <x>475</x>
    <y>65</y>
    <width>220</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_postdate</column>
   </data>
  </field>
  <label>
   <rect>
    <x>10</x>
    <y>100</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Dist. Date</string>
  </label>
  <label>
   <rect>
    <x>23</x>
    <y>64</y>
    <width>117</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Bank Rec. Post:</string>
  </label>
  <field>
   <rect>
    <x>9</x>
    <y>148</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_opendate</column>
   </data>
  </field>
  <field>
   <rect>
    <x>475</x>
    <y>50</y>
    <width>220</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_username</column>
   </data>
  </field>
  <label>
   <rect>
    <x>40</x>
    <y>50</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Bark Account:</string>
  </label>
  <label>
   <rect>
    <x>280</x>
    <y>5</y>
    <width>465</width>
    <height>38</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Bank Reconciliation History</string>
  </label>
  <field>
   <rect>
    <x>644</x>
    <y>148</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_openbal</column>
   </data>
  </field>
  <label>
   <rect>
    <x>5</x>
    <y>125</y>
    <width>121</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>9</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Reconciled</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>0</height>
 </pghead>
 <pghead>
  <height>31</height>
  <line>
   <xstart>5</xstart>
   <ystart>15</ystart>
   <xend>745</xend>
   <yend>15</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>100</x>
    <y>0</y>
    <width>150</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Doc. Number/Notes</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>0</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Dist. Date</string>
  </label>
  <label>
   <rect>
    <x>645</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Amount</string>
  </label>
 </pghead>
 <section>
  <name>reconciled</name>
  <group>
   <name>reconciled-group</name>
   <column>amount_xtnumericrole</column>
   <foot>
    <height>21</height>
    <field>
     <rect>
      <x>645</x>
      <y>3.28</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>head</query>
      <column>f_endbal</column>
     </data>
    </field>
    <field>
     <rect>
      <x>10</x>
      <y>3.28</y>
      <width>80</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <hcenter/>
     <vcenter/>
     <data>
      <query>head</query>
      <column>f_enddate</column>
     </data>
    </field>
    <field>
     <rect>
      <x>100</x>
      <y>3.28</y>
      <width>200</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>head</query>
      <column>f_enddescrip</column>
     </data>
    </field>
   </foot>
  </group>
  <group>
   <name>reconciled-type</name>
   <column>sort</column>
   <head>
    <height>27</height>
    <field>
     <rect>
      <x>10</x>
      <y>6.78</y>
      <width>94</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>reconciled</query>
      <column>type</column>
     </data>
    </field>
   </head>
   <foot>
    <height>18</height>
    <field>
     <rect>
      <x>645</x>
      <y>2</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>reconciled</query>
      <column>amount</column>
     </data>
     <format builtin="true">qty</format>
     <tracktotal subtotal="true"/>
    </field>
    <field>
     <rect>
      <x>438</x>
      <y>2</y>
      <width>168</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>reconciled</query>
      <column>type</column>
     </data>
    </field>
    <label>
     <rect>
      <x>399</x>
      <y>2</y>
      <width>49</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <left/>
     <vcenter/>
     <string>TOTAL</string>
    </label>
   </foot>
  </group>
  <detail>
   <key>
    <query>reconciled</query>
   </key>
   <height>18</height>
   <field>
    <rect>
     <x>206</x>
     <y>0</y>
     <width>430</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>reconciled</query>
     <column>notes</column>
    </data>
   </field>
   <field>
    <rect>
     <x>645</x>
     <y>0</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>reconciled</query>
     <column>f_amount</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>0</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>reconciled</query>
     <column>f_date</column>
    </data>
   </field>
   <field>
    <rect>
     <x>100</x>
     <y>-0.182</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>reconciled</query>
     <column>gltrans_docnumber</column>
    </data>
   </field>
  </detail>
 </section>
 <section>
  <name>unreconciled</name>
  <group>
   <name>unreconciled-group</name>
   <column>amount_xtnumericrole</column>
   <head>
    <height>33</height>
    <label>
     <rect>
      <x>5</x>
      <y>10.3</y>
      <width>121</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>9</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>Unreconciled</string>
    </label>
   </head>
   <foot>
    <height>136.499</height>
    <label>
     <rect>
      <x>450</x>
      <y>43</y>
      <width>162</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Checks in Circulation:</string>
    </label>
    <label>
     <rect>
      <x>5</x>
      <y>12.5</y>
      <width>235</width>
      <height>15.5</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>9</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>General Legder Reconciliation</string>
    </label>
    <label>
     <rect>
      <x>450</x>
      <y>73</y>
      <width>163</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Deposits in Circulation:</string>
    </label>
    <label>
     <rect>
      <x>395</x>
      <y>103</y>
      <width>219</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Actual Account Balance:</string>
    </label>
    <label>
     <rect>
      <x>450</x>
      <y>28</y>
      <width>162</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Ending Reconciled Balance:</string>
    </label>
    <field>
     <rect>
      <x>640</x>
      <y>28</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>head</query>
      <column>f_endbal</column>
     </data>
    </field>
    <field>
     <rect>
      <x>640</x>
      <y>43</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>unreconciled</query>
      <column>rtcheck</column>
     </data>
     <format builtin="true">money</format>
     <tracktotal/>
    </field>
    <label>
     <rect>
      <x>555</x>
      <y>58</y>
      <width>56</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Subtotal:</string>
    </label>
    <field>
     <rect>
      <x>640</x>
      <y>58</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>Subtotal</query>
      <column>amount</column>
     </data>
    </field>
    <field>
     <rect>
      <x>640</x>
      <y>73</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>unreconciled</query>
      <column>rtdep</column>
     </data>
     <format builtin="true">money</format>
     <tracktotal/>
    </field>
    <field>
     <rect>
      <x>640</x>
      <y>88</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>unreconciled</query>
      <column>rtadj</column>
     </data>
     <format builtin="true">money</format>
     <tracktotal/>
    </field>
    <field>
     <rect>
      <x>640</x>
      <y>103</y>
      <width>100</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>ActualAmount</query>
      <column>amount</column>
     </data>
    </field>
    <label>
     <rect>
      <x>435</x>
      <y>88</y>
      <width>177</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Adjustments in Circulation:</string>
    </label>
   </foot>
  </group>
  <group>
   <name>unreconciled-type</name>
   <column>type</column>
   <head>
    <height>26.0233</height>
    <field>
     <rect>
      <x>10</x>
      <y>4.9</y>
      <width>94</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>unreconciled</query>
      <column>type</column>
     </data>
    </field>
   </head>
  </group>
  <detail>
   <key>
    <query>unreconciled</query>
   </key>
   <height>21.9767</height>
   <field>
    <rect>
     <x>204</x>
     <y>0.687</y>
     <width>430</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>unreconciled</query>
     <column>notes</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>0.687</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>unreconciled</query>
     <column>f_date</column>
    </data>
   </field>
   <field>
    <rect>
     <x>98.5</x>
     <y>0.687</y>
     <width>94</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>unreconciled</query>
     <column>gltrans_docnumber</column>
    </data>
   </field>
   <field>
    <rect>
     <x>643</x>
     <y>0.687</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>unreconciled</query>
     <column>f_amount</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>15</height>
  <field>
   <rect>
    <x>705</x>
    <y>0</y>
    <width>40</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>page_number</column>
   </data>
  </field>
  <label>
   <rect>
    <x>0</x>
    <y>0</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Report Date:</string>
  </label>
  <label>
   <rect>
    <x>615</x>
    <y>0</y>
    <width>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Page:</string>
  </label>
  <field>
   <rect>
    <x>80</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>report_date</column>
   </data>
  </field>
 </pgfoot>
 <rptfoot>
  <height>143</height>
 </rptfoot>
</report>
