<!DOCTYPE openRPTDef>
<report>
 <title>Invoice Register</title>
 <name>InvoiceRegister</name>
 <description></description>
 <size>Letter</size>
 <portrait/>
 <topmargin>50</topmargin>
 <bottommargin>50</bottommargin>
 <rightmargin>50</rightmargin>
 <leftmargin>50</leftmargin>
 <querysource>
  <name>head</name>
  <sql>SELECT formatDate(&lt;? value("startDate") ?>, 'Earliest') AS startdate,
       formatDate(&lt;? value("endDate") ?>, 'Latest') AS enddate,
       &lt;? if exists("accnt_id") ?>
         ( SELECT (accnt_number || '-' || accnt_descrip)
             FROM accnt
            WHERE (accnt_id=&lt;? value("accnt_id") ?>) )
       &lt;? else ?>
         text('All Accounts')
       &lt;? endif ?>
       AS accnt</sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT gltrans_id, gltrans_date AS transdate,
       formatDate(gltrans_date) AS f_transdate,
       gltrans_source AS source,
       CASE WHEN(gltrans_doctype='IN') THEN text('Invoice')
            WHEN(gltrans_doctype='CM') THEN text('Credit Memo')
            WHEN(gltrans_doctype='DM') THEN text('Debit Memo')
            WHEN(gltrans_doctype='CD') THEN text('Customer Deposit')
            ELSE gltrans_doctype
       END AS doctype,
       gltrans_docnumber AS docnumber,
       CASE WHEN(gltrans_doctype='IN') THEN
                (SELECT invchead_shipto_name
                   FROM aropen LEFT OUTER JOIN
                        invchead
                          ON (invchead_id=aropen_cobmisc_id
                          AND invchead_cust_id=aropen_cust_id)
                  WHERE ((aropen_docnumber=gltrans_docnumber)
                    AND  (aropen_doctype='I')))
            ELSE firstLine(gltrans_notes)
       END AS transnotes,
       (formatGLAccount(accnt_id) || ' - ' || accnt_descrip) AS account,
       CASE WHEN (gltrans_amount &lt; 0) THEN formatMoney(ABS(gltrans_amount))
            ELSE ''
       END AS f_debit,
       CASE WHEN (gltrans_amount &lt; 0) THEN ABS(gltrans_amount)
            ELSE 0
       END AS debit,
       CASE WHEN (gltrans_amount > 0) THEN formatMoney(gltrans_amount)
            ELSE ''
       END AS f_credit,
       CASE WHEN (gltrans_amount > 0) THEN gltrans_amount
            ELSE 0
       END AS credit
  FROM gltrans, accnt
 WHERE ((gltrans_accnt_id=accnt_id)
   AND (gltrans_doctype IN ('IN', 'CM', 'DM', 'CD'))
   AND (gltrans_source = 'A/R')
   AND (gltrans_date BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
&lt;? if exists("accnt_id") ?>
   AND (gltrans_accnt_id=&lt;? value("accnt_id") ?>)
&lt;? endif ?>
       )
UNION
SELECT sltrans_id AS id, sltrans_date AS transdate,
       formatDate(sltrans_date) AS f_transdate,
       sltrans_source AS source,
       CASE WHEN(sltrans_doctype='IN') THEN text('Invoice')
            WHEN(sltrans_doctype='CM') THEN text('Credit Memo')
            WHEN(sltrans_doctype='DM') THEN text('Debit Memo')
            WHEN(sltrans_doctype='CD') THEN text('Customer Deposit')
            ELSE sltrans_doctype
       END AS doctype,
       sltrans_docnumber AS docnumber,
       CASE WHEN(sltrans_doctype='IN') THEN
                (SELECT invchead_shipto_name
                   FROM aropen LEFT OUTER JOIN
                        invchead
                          ON (invchead_id=aropen_cobmisc_id
                          AND invchead_cust_id=aropen_cust_id)
                  WHERE ((aropen_docnumber=sltrans_docnumber)
                    AND  (aropen_doctype='I')))
            ELSE firstLine(sltrans_notes)
       END AS transnotes,
       (formatGLAccount(accnt_id) || ' - ' || accnt_descrip) AS account,
       CASE WHEN (sltrans_amount &lt; 0) THEN formatMoney(ABS(sltrans_amount))
            ELSE ''
       END AS f_debit,
       CASE WHEN (sltrans_amount &lt; 0) THEN ABS(sltrans_amount)
            ELSE 0
       END AS debit,
       CASE WHEN (sltrans_amount > 0) THEN formatMoney(sltrans_amount)
            ELSE ''
       END AS f_credit,
       CASE WHEN (sltrans_amount > 0) THEN sltrans_amount
            ELSE 0
       END AS credit
  FROM sltrans, accnt
 WHERE ((sltrans_accnt_id=accnt_id)
   AND (sltrans_doctype IN ('IN', 'CM', 'DM', 'CD'))
   AND (sltrans_source = 'A/R')
   AND (sltrans_date BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
&lt;? if exists("accnt_id") ?>
   AND (sltrans_accnt_id=&lt;? value("accnt_id") ?>)
&lt;? endif ?>
       )
ORDER BY transdate, docnumber;</sql>
 </querysource>
 <rpthead>
  <height>205</height>
  <label>
   <rect>
    <x>408</x>
    <y>90</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>End Date:</string>
  </label>
  <field>
   <rect>
    <x>545</x>
    <y>70</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>startdate</column>
   </data>
  </field>
  <label>
   <rect>
    <x>160</x>
    <y>170</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Doc. Type</string>
  </label>
  <label>
   <rect>
    <x>95</x>
    <y>170</y>
    <width>60</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Source</string>
  </label>
  <label>
   <rect>
    <x>260</x>
    <y>10</y>
    <width>465</width>
    <height>38</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Invoice Register</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>170</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Date</string>
  </label>
  <label>
   <rect>
    <x>245</x>
    <y>185</y>
    <width>104</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Account</string>
  </label>
  <label>
   <rect>
    <x>160</x>
    <y>185</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Doc. #</string>
  </label>
  <label>
   <rect>
    <x>5</x>
    <y>70</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Account:</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>70</y>
    <width>290</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>accnt</column>
   </data>
  </field>
  <label>
   <rect>
    <x>245</x>
    <y>170</y>
    <width>104</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Reference</string>
  </label>
  <field>
   <rect>
    <x>545</x>
    <y>90</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>enddate</column>
   </data>
  </field>
  <label>
   <rect>
    <x>408</x>
    <y>70</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Start Date:</string>
  </label>
  <label>
   <rect>
    <x>595</x>
    <y>185</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Credit</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>200</ystart>
   <xend>745</xend>
   <yend>200</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>508</x>
    <y>185</y>
    <width>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Debit</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>6</height>
 </pghead>
 <pghead>
  <height>41</height>
  <label>
   <rect>
    <x>245</x>
    <y>5</y>
    <width>104</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Reference</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>5</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Date</string>
  </label>
  <label>
   <rect>
    <x>95</x>
    <y>5</y>
    <width>60</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Source</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>35</ystart>
   <xend>745</xend>
   <yend>35</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>508</x>
    <y>20</y>
    <width>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Debit</string>
  </label>
  <label>
   <rect>
    <x>595</x>
    <y>20</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Credit</string>
  </label>
  <label>
   <rect>
    <x>160</x>
    <y>5</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Doc. Type</string>
  </label>
  <label>
   <rect>
    <x>160</x>
    <y>20</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Doc. #</string>
  </label>
  <label>
   <rect>
    <x>245</x>
    <y>20</y>
    <width>104</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Account</string>
  </label>
 </pghead>
 <section>
  <name>detail</name>
  <group>
   <name>bydate</name>
   <column>f_transdate</column>
   <head>
    <height>21</height>
    <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>detail</query>
      <column>f_transdate</column>
     </data>
    </field>
   </head>
   <foot>
    <height>21</height>
    <label>
     <rect>
      <x>160</x>
      <y>0</y>
      <width>80</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>509</x>
      <y>0</y>
      <width>80</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>debit</column>
     </data>
     <tracktotal subtotal="true" builtin="true" >money</tracktotal>
    </field>
    <field>
     <rect>
      <x>600</x>
      <y>0</y>
      <width>80</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>credit</column>
     </data>
     <tracktotal subtotal="true" builtin="true" >money</tracktotal>
    </field>
    <line>
     <xstart>5</xstart>
     <ystart>15</ystart>
     <xend>745</xend>
     <yend>15</yend>
     <weight>1</weight>
    </line>
   </foot>
  </group>
  <detail>
   <key>
    <query>detail</query>
   </key>
   <height>31</height>
   <field>
    <rect>
     <x>95</x>
     <y>0</y>
     <width>60</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>source</column>
    </data>
   </field>
   <field>
    <rect>
     <x>160</x>
     <y>15</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>docnumber</column>
    </data>
   </field>
   <field>
    <rect>
     <x>160</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>detail</query>
     <column>doctype</column>
    </data>
   </field>
   <field>
    <rect>
     <x>509</x>
     <y>15</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_debit</column>
    </data>
   </field>
   <field>
    <rect>
     <x>245</x>
     <y>15</y>
     <width>260</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>account</column>
    </data>
   </field>
   <field>
    <rect>
     <x>600</x>
     <y>15</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_credit</column>
    </data>
   </field>
   <field>
    <rect>
     <x>245</x>
     <y>0</y>
     <width>260</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>transnotes</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>18</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>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Report Date:</string>
  </label>
  <field>
   <rect>
    <x>90</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>
  <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>
 </pgfoot>
 <rptfoot>
  <height>21</height>
  <field>
   <rect>
    <x>600</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>detail</query>
    <column>credit</column>
   </data>
   <tracktotal builtin="true" >money</tracktotal>
  </field>
  <field>
   <rect>
    <x>509</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>detail</query>
    <column>debit</column>
   </data>
   <tracktotal builtin="true" >money</tracktotal>
  </field>
  <label>
   <rect>
    <x>160</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Total:</string>
  </label>
 </rptfoot>
</report>
