<!DOCTYPE openRPTDef>
<report>
 <title>Inventory Availability by Customer Type</title>
 <name>InventoryAvailabilityByCustomerType</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>
  <name>head</name>
  <sql>SELECT 
       &lt;? if exists("onlyShowShortages") ?>
         text('Only Showing Shortages')
       &lt;? else ?>
         text('')
       &lt;? endif ?>
       AS f_onlyShowShortages,
      &lt;? if exists("showWoSupply") ?>
         text('Show W/O Supply')
       &lt;? else ?>
         text('')
       &lt;? endif ?>
       AS f_showWoSupply,
&lt;? if exists("custtype_id") ?>
       (SELECT custtype_code from custtype where (custtype_id=&lt;? value("custtype_id") ?>))
&lt;? elseif exists("custtype_pattern") ?>
       &lt;? value("custtype_pattern") ?>
&lt;? else ?>
       text('All Customer Types')
&lt;? endif ?>
       AS f_custtype;</sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT itemsite_id, coitem_id,
       cohead_id, cohead_number, (cust_number||'-'||cust_name) AS custname,
       item_number, item_description, uom_name, item_picklist,
       qoh, formatQty(qoh) AS f_qoh,sobalance,
       formatQty(sobalance) AS f_sobalance,
       formatQty(allocated) AS f_allocated,
       ordered, formatQty(ordered) AS f_ordered,
       (qoh + ordered - sobalance) AS woavail,
&lt;? if exists("useReservationNetting") ?>
       formatQty(coitem_qtyreserved) AS f_soavail,
&lt;? else ?>
       formatQty(qoh + ordered - sobalance) AS f_soavail,
&lt;? endif ?>
       (qoh + ordered - allocated) AS totalavail,
       formatQty(qoh + ordered - allocated) AS f_totalavail,
       atshipping,formatQty(atshipping) AS f_atshipping,
       reorderlevel 
&lt;? if exists(showWoSupply) ?>,         
       wo_id,
       wo_status,
       wo_number,
       wo_ordered,
       CASE WHEN (wo_id = -1) THEN NULL ELSE formatQty(wo_ordered) END AS f_wo_ordered,
       formatdate(wo_startdate) AS f_wo_startdate, 
       formatdate(wo_duedate) AS f_wo_duedate,
       COALESCE(wo_latestart,false) AS wo_latestart,
       COALESCE(wo_latedue,false) AS wo_latedue 
&lt;? endif ?>
       FROM ( SELECT itemsite_id, coitem_id,
                     cohead_id, cohead_number, cust_number, cust_name,
                     item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
                     uom_name, item_picklist,
                     noNeg(qtyAvailable(itemsite_id)) AS qoh,
                     noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
                     qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
                     qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
                     qtyatshipping(coitem_id) AS atshipping,
                     coitem_qtyreserved,
                     CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel 
&lt;? if exists(showWoSupply) ?>,  
                     COALESCE(wo_id,-1) AS wo_id,
                     formatwonumber(wo_id) AS wo_number,
                     noNeg((wo_qtyord-wo_qtyrcv)) AS wo_ordered,
                     wo_status, wo_startdate, wo_duedate,
                     ((wo_startdate &lt;= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS wo_latestart,
                     (wo_duedate&lt;=CURRENT_DATE) AS wo_latedue  
&lt;? endif ?> 
                FROM cohead, itemsite, item, uom, custinfo, coitem
&lt;? if exists(showWoSupply) ?> 
                LEFT OUTER JOIN wo
                  ON ((coitem_itemsite_id=wo_itemsite_id)
                  AND (wo_status IN ('E','R','I'))
                  AND (wo_qtyord-wo_qtyrcv > 0)
                  AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) > 
                        (SELECT qtyAvailable(itemsite_id) AS availableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
&lt;? endif ?>
                WHERE((coitem_cohead_id=cohead_id)
                  AND (cohead_cust_id=cust_id)
                  AND (coitem_itemsite_id=itemsite_id)
                  AND (itemsite_item_id=item_id)
                  AND (item_inv_uom_id=uom_id)
                  AND (coitem_status NOT IN ('C', 'X'))
&lt;? if exists("custtype_id") ?>
                  AND (cust_custtype_id=&lt;? value("custtype_id") ?>)
&lt;? elseif exists("custtype_pattern") ?>
                  AND (cust_custtype_id IN (SELECT custtype_id
                                              FROM custtype
                                             WHERE(custtype_code ~ &lt;? value("custtype_pattern") ?>)))
&lt;? endif ?>
              )
            ) AS data 
&lt;? if exists(onlyShowShortages) ?>
      WHERE ( ((qoh + ordered - allocated) &lt; 0)
           OR ((qoh + ordered - sobalance) &lt; 0) ) 
&lt;? endif ?>
      ORDER BY cohead_id, cohead_number, item_number
&lt;? if exists(showWoSupply) ?> ,
               wo_duedate
&lt;? endif ?>
;</sql>
 </querysource>
 <rpthead>
  <height>251</height>
  <label>
   <rect>
    <x>180</x>
    <y>200</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>UOM</string>
  </label>
  <field>
   <rect>
    <x>360</x>
    <y>120</y>
    <width>380</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_showWoSupply</column>
   </data>
  </field>
  <label>
   <rect>
    <x>10</x>
    <y>215</y>
    <width>125</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Description</string>
  </label>
  <line>
   <xstart>10</xstart>
   <ystart>245</ystart>
   <xend>750</xend>
   <yend>245</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>330</x>
    <y>200</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>QOH</string>
  </label>
  <label>
   <rect>
    <x>30</x>
    <y>100</y>
    <width>102</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Customer Type:</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>100</y>
    <width>209</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_custtype</column>
   </data>
  </field>
  <label>
   <rect>
    <x>645</x>
    <y>215</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Total Available</string>
  </label>
  <label>
   <rect>
    <x>645</x>
    <y>230</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Due Date</string>
  </label>
  <label>
   <rect>
    <x>540</x>
    <y>230</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Start Date</string>
  </label>
  <field>
   <rect>
    <x>360</x>
    <y>100</y>
    <width>380</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>f_onlyShowShortages</column>
   </data>
  </field>
  <label>
   <rect>
    <x>540</x>
    <y>215</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>At Shipping</string>
  </label>
  <label>
   <rect>
    <x>185</x>
    <y>230</y>
    <width>65</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Status</string>
  </label>
  <label>
   <rect>
    <x>432</x>
    <y>200</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>This Allocated</string>
  </label>
  <label>
   <rect>
    <x>540</x>
    <y>200</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Orders</string>
  </label>
  <label>
   <rect>
    <x>430</x>
    <y>230</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Qty Due</string>
  </label>
  <label>
   <rect>
    <x>645</x>
    <y>200</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>This Available</string>
  </label>
  <label>
   <rect>
    <x>20</x>
    <y>230</y>
    <width>125</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>W/O Number</string>
  </label>
  <label>
   <rect>
    <x>225</x>
    <y>10</y>
    <width>500</width>
    <height>37</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Inventory Availability by Customer Type</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>200</y>
    <width>125</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Item</string>
  </label>
  <label>
   <rect>
    <x>432</x>
    <y>215</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Total Allocated</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>6</height>
 </pghead>
 <pghead>
  <height>41</height>
  <label>
   <rect>
    <x>645</x>
    <y>5</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>This Available</string>
  </label>
  <label>
   <rect>
    <x>432</x>
    <y>5</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>This Allocated</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>35</ystart>
   <xend>745</xend>
   <yend>35</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>540</x>
    <y>5</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Orders</string>
  </label>
  <label>
   <rect>
    <x>432</x>
    <y>20</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Total Allocated</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>20</y>
    <width>125</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Description</string>
  </label>
  <label>
   <rect>
    <x>330</x>
    <y>5</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>QOH</string>
  </label>
  <label>
   <rect>
    <x>180</x>
    <y>5</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>UOM</string>
  </label>
  <label>
   <rect>
    <x>645</x>
    <y>20</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Total Available</string>
  </label>
  <label>
   <rect>
    <x>540</x>
    <y>20</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>At Shipping</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>5</y>
    <width>125</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Item</string>
  </label>
 </pghead>
 <section>
  <name>detail</name>
  <group>
   <name>cohead</name>
   <column>cohead_id</column>
   <head>
    <height>26</height>
    <label>
     <rect>
      <x>5</x>
      <y>0</y>
      <width>55</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>S/O #:</string>
    </label>
    <field>
     <rect>
      <x>390</x>
      <y>0</y>
      <width>209</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>custname</column>
     </data>
    </field>
    <label>
     <rect>
      <x>300</x>
      <y>0</y>
      <width>80</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>65</x>
      <y>0</y>
      <width>209</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>cohead_number</column>
     </data>
    </field>
   </head>
   <foot>
    <height>6</height>
    <line>
     <xstart>5</xstart>
     <ystart>0</ystart>
     <xend>745</xend>
     <yend>0</yend>
     <weight>0</weight>
    </line>
   </foot>
  </group>
  <detail>
   <key>
    <query>detail</query>
   </key>
   <height>46</height>
   <field>
    <rect>
     <x>640</x>
     <y>15</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_totalavail</column>
    </data>
   </field>
   <field>
    <rect>
     <x>25</x>
     <y>30</y>
     <width>150</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>wo_number</column>
    </data>
   </field>
   <field>
    <rect>
     <x>535</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>detail</query>
     <column>f_ordered</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>15</y>
     <width>400</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>Item_description</column>
    </data>
   </field>
   <field>
    <rect>
     <x>640</x>
     <y>30</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_wo_duedate</column>
    </data>
   </field>
   <field>
    <rect>
     <x>10</x>
     <y>0</y>
     <width>150</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>Item_number</column>
    </data>
   </field>
   <field>
    <rect>
     <x>430</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>detail</query>
     <column>f_sobalance</column>
    </data>
   </field>
   <field>
    <rect>
     <x>185</x>
     <y>0</y>
     <width>75</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>uom_name</column>
    </data>
   </field>
   <field>
    <rect>
     <x>185</x>
     <y>30</y>
     <width>75</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>wo_status</column>
    </data>
   </field>
   <field>
    <rect>
     <x>640</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>detail</query>
     <column>f_soavail</column>
    </data>
   </field>
   <field>
    <rect>
     <x>430</x>
     <y>15</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_allocated</column>
    </data>
   </field>
   <field>
    <rect>
     <x>430</x>
     <y>30</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_wo_ordered</column>
    </data>
   </field>
   <field>
    <rect>
     <x>325</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>detail</query>
     <column>f_qoh</column>
    </data>
   </field>
   <field>
    <rect>
     <x>535</x>
     <y>15</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_atshipping</column>
    </data>
   </field>
   <field>
    <rect>
     <x>535</x>
     <y>30</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_wo_startdate</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>18</height>
  <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>
  <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>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>
  <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>
 </pgfoot>
</report>
