<!DOCTYPE openRPTDef>
<report>
 <title>Drop Ship Bill of Lading</title>
 <name>CustomerBOL</name>
 <description></description>
 <size>
  <width>850</width>
  <height>1100</height>
 </size>
 <portrait/>
 <topmargin>0</topmargin>
 <bottommargin>50</bottommargin>
 <rightmargin>0</rightmargin>
 <leftmargin>0</leftmargin>
 <querysource>
  <name>head</name>
  <sql>SELECT shiphead_shipvia, formatDate(shiphead_shipdate) AS shipdate,
                cust_name, ca.addr_line1 AS cust_address1, ca.addr_line2 AS cust_address2,
                (ca.addr_city || '  ' || ca.addr_state || '  ' || ca.addr_postalcode) AS custcitystatezip,
                cust_number, cohead_number, cohead_fob, cohead_custponumber,
                warehous_descrip, wa.addr_line1 AS warehous_addr1, wa.addr_line2 AS warehous_addr2, wa.addr_line3 AS warehous_addr3, wa.addr_city AS warehous_addr4, warehous_fob,
                cohead_shiptoname, cohead_shiptoaddress1, cohead_shiptoaddress2, cohead_shiptoaddress3,
                (cohead_shiptocity || ' ' || cohead_shiptostate || ' ' || cohead_shiptozipcode) AS shiptocitystatezip,
                cohead_shiptophone
         FROM shiphead, cohead, whsinfo, custinfo
         LEFT OUTER JOIN addr ca ON (cntct_addr_id=ca.addr_id)
         LEFT OUTER JOIN addr wa ON (warehous_addr_id=wa.addr_id);
         WHERE ((shiphead_order_id=cohead_id)
          AND (cohead_cust_id=cust_id)
          AND (cohead_warehous_id=warehous_id)
          AND (shiphead_id=%1));</sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT coitem_linenumber, formatQty(SUM(shipitem_qty)) AS invqty, uom_name, roundUp(SUM(shipitem_qty) / itemuomratiobytype(item_id, 'Selling'))::integer AS shipqty,
                itemsellinguom(item_id) AS shipuom, item_number, item_descrip1, item_descrip2,
                formatQty(SUM(shipitem_qty) * item_prodweight) AS netweight,
                formatQty(SUM(shipitem_qty) * (item_prodweight + item_packweight)) AS grossweight
         FROM shipitem, coitem, itemsite, item, uom
         WHERE ((shipitem_orderitem_id=coitem_id)
          AND (coitem_itemsite_id=itemsite_id)
          AND (itemsite_item_id=item_id)
          AND (item_inv_uom_id=uom_id)
          AND (shipitem_shiphead_id=%1))
         GROUP BY coitem_linenumber, item_number, uom_name, shipuom
                  item_descrip1, item_descrip2, item_prodweight, item_packweight
         ORDER BY coitem_linenumber;
    </sql>
 </querysource>
 <querysource>
  <name>foot</name>
  <sql>SELECT formatQty(SUM(shipitem_qty * item_prodweight)) AS netweight,
                formatQty(SUM(shipitem_qty * (item_prodweight + item_packweight))) AS grossweight,
                CASE
                 WHEN ('%3' = 'C') THEN 'X'
                 ELSE ' '
                END AS collectflag,
                CASE
                 WHEN ('%3' = 'C') THEN 'Therm-O-Rock East, Inc.'
                 ELSE ' '
                END AS section7,
                CASE
                 WHEN ('%3' = 'C') THEN cust_name
                 ELSE ' '
                END AS collect_name,
                CASE
                 WHEN ('%3' = 'C') THEN addr_line1
                 ELSE ' '
                END AS collect_address1,
                CASE
                 WHEN ('%3' = 'C') THEN addr_line2
                 ELSE ' '
                END AS collect_address2,
                CASE
                 WHEN ('%3' = 'C') THEN (addr_city || '  ' || addr_state || '  ' || addr_postalcode)
                 ELSE ' '
                END AS collect_address3
         FROM shipitem, cohead, custinfo, coitem, itemsite, item
         LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
         WHERE ((shipitem_orderitem_id=coitem_id)
          AND (coitem_itemsite_id=itemsite_id)
          AND (itemsite_item_id=item_id)
          AND (coitem_cohead_id=cohead_id)
          AND (cohead_cust_id=cust_id)
          AND (shipitem_shiphead_id=%1))
         GROUP BY cust_name, addr_line1, addr_line2, addr_city, addr_state, addr_postalcode;</sql>
 </querysource>
 <querysource>
  <name>notes</name>
  <sql>SELECT shiphead_notes
         FROM shiphead
         WHERE (shiphead_id=%1);</sql>
 </querysource>
 <pghead>
  <firstpage/>
  <height>318</height>
  <field>
   <rect>
    <x>532</x>
    <y>224</y>
    <width>300</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>shiptocitystatezip</column>
   </data>
  </field>
  <field>
   <rect>
    <x>532</x>
    <y>205</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_shiptoaddress3</column>
   </data>
  </field>
  <field>
   <rect>
    <x>93</x>
    <y>168</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>warehous_addr2</column>
   </data>
  </field>
  <field>
   <rect>
    <x>93</x>
    <y>205</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>warehous_addr4</column>
   </data>
  </field>
  <field>
   <rect>
    <x>532</x>
    <y>168</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_shiptoaddress1</column>
   </data>
  </field>
  <field>
   <rect>
    <x>93</x>
    <y>149</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cust_name</column>
   </data>
  </field>
  <field>
   <rect>
    <x>532</x>
    <y>149</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_shiptoname</column>
   </data>
  </field>
  <field>
   <rect>
    <x>532</x>
    <y>242</y>
    <width>300</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_shiptophone</column>
   </data>
  </field>
  <field>
   <rect>
    <x>578</x>
    <y>65</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>shipdate</column>
   </data>
  </field>
  <field>
   <rect>
    <x>93</x>
    <y>186</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>warehous_addr3</column>
   </data>
  </field>
  <label>
   <rect>
    <x>93</x>
    <y>224</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>724-258-3670</string>
  </label>
  <field>
   <rect>
    <x>121</x>
    <y>65</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>shiphead_shipvia</column>
   </data>
  </field>
  <field>
   <rect>
    <x>532</x>
    <y>186</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_shiptoaddress2</column>
   </data>
  </field>
 </pghead>
 <section>
  <name>detail</name>
  <detail>
   <key>
    <query>detail</query>
    <column>coitem_linenumber</column>
   </key>
   <height>54</height>
   <field>
    <rect>
     <x>200</x>
     <y>40</y>
     <width>300</width>
     <height>14</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>item_descrip2</column>
    </data>
   </field>
   <field>
    <rect>
     <x>94</x>
     <y>5</y>
     <width>80</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>shipuom</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>30</y>
     <width>80</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>invqty</column>
    </data>
   </field>
   <field>
    <rect>
     <x>200</x>
     <y>25</y>
     <width>300</width>
     <height>14</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>item_descrip1</column>
    </data>
   </field>
   <field>
    <rect>
     <x>94</x>
     <y>30</y>
     <width>80</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>uom_name</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>5</y>
     <width>80</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>shipqty</column>
    </data>
   </field>
   <field>
    <rect>
     <x>520</x>
     <y>5</y>
     <width>100</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>netweight</column>
    </data>
   </field>
   <field>
    <rect>
     <x>200</x>
     <y>5</y>
     <width>300</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>item_number</column>
    </data>
   </field>
   <field>
    <rect>
     <x>520</x>
     <y>25</y>
     <width>100</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>grossweight</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <lastpage/>
  <height>346</height>
  <field>
   <rect>
    <x>614</x>
    <y>80</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cust_number</column>
   </data>
  </field>
  <field>
   <rect>
    <x>230</x>
    <y>200</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>collect_address3</column>
   </data>
  </field>
  <label>
   <rect>
    <x>500</x>
    <y>100</y>
    <width>110</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Order #:</string>
  </label>
  <field>
   <rect>
    <x>20</x>
    <y>180</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>collect_name</column>
   </data>
  </field>
  <field>
   <rect>
    <x>520</x>
    <y>5</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>netweight</column>
   </data>
  </field>
  <field>
   <rect>
    <x>50</x>
    <y>10</y>
    <width>400</width>
    <height>100</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <top/>
   <data>
    <query>notes</query>
    <column>shiphead_notes</column>
   </data>
  </field>
  <field>
   <rect>
    <x>614</x>
    <y>120</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_custponumber</column>
   </data>
  </field>
  <field>
   <rect>
    <x>20</x>
    <y>200</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>collect_address1</column>
   </data>
  </field>
  <field>
   <rect>
    <x>438</x>
    <y>270</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>section7</column>
   </data>
  </field>
  <field>
   <rect>
    <x>520</x>
    <y>25</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>grossweight</column>
   </data>
  </field>
  <label>
   <rect>
    <x>500</x>
    <y>80</y>
    <width>110</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Customer #:</string>
  </label>
  <field>
   <rect>
    <x>614</x>
    <y>60</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_fob</column>
   </data>
  </field>
  <field>
   <rect>
    <x>230</x>
    <y>180</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>collect_address2</column>
   </data>
  </field>
  <field>
   <rect>
    <x>728</x>
    <y>270</y>
    <width>46</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>foot</query>
    <column>collectflag</column>
   </data>
  </field>
  <field>
   <rect>
    <x>614</x>
    <y>100</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>cohead_number</column>
   </data>
  </field>
  <label>
   <rect>
    <x>480</x>
    <y>120</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Customer P/O #:</string>
  </label>
  <label>
   <rect>
    <x>500</x>
    <y>60</y>
    <width>110</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Shipped From:</string>
  </label>
 </pgfoot>
</report>
