<!DOCTYPE openRPTDef>
<report>
 <title>Material Usage Variance By BOM Item</title>
 <name>MaterialUsageVarianceByBOMItem</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 parent.item_number AS parent_number,
       parent.item_descrip1 AS parent_descrip1,
       parent.item_descrip2 AS parent_descrip2,
       puom.uom_name AS parent_invuom,
       child.item_number AS comp_number,
       child.item_descrip1 AS comp_descrip1,
       child.item_descrip2 AS comp_descrip2,
       cuom.uom_name AS comp_invuom,
       bomitem_seqnumber as comp_seqnumber,
       &lt;? if exists("warehous_id") ?>
         ( SELECT warehous_code
             FROM whsinfo
            WHERE (warehous_id=&lt;? value("warehous_id") ?>) )
       &lt;? else ?>
         text('All Sites')
       &lt;? endif ?>
       AS warehouse
  FROM item as parent, item as child, uom AS puom, uom AS cuom, bomitem
 WHERE ((parent.item_id=&lt;? value("item_id") ?>)
   AND (parent.item_inv_uom_id=puom.uom_id)
   AND (bomitem_item_id=child.item_id)
   AND (child.item_inv_uom_id=cuom.uom_id)
   AND (bomitem_id=&lt;? value("bomitem_id") ?>) );
</sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT formatDate(posted) AS f_posted,
       formatQty(ordered) AS f_ordered,
       formatQty(received) AS f_produced,
       formatQty(projreq) AS f_projreq,
       formatQtyPer(projqtyper) AS f_projqtyper,
       formatQty(actiss) AS f_actiss, 
       formatQtyPer(actqtyper) AS f_actqtyper,
       formatQtyPer(actqtyper - projqtyper) AS f_qtypervar,
       CASE WHEN (actqtyper=projqtyper) THEN formatPrcnt(0)
            WHEN (projqtyper=0) THEN formatPrcnt(actqtyper)
            ELSE formatPrcnt((1 - (actqtyper / projqtyper)) * -1)
       END AS f_percent
  FROM ( SELECT womatlvar_posted AS posted,
                womatlvar_qtyord AS ordered,
                womatlvar_qtyrcv AS received,
                (womatlvar_qtyrcv * (womatlvar_qtyper * (1 + womatlvar_scrap))) AS projreq,
                womatlvar_qtyper AS projqtyper,
                (womatlvar_qtyiss) AS actiss, (womatlvar_qtyiss / (womatlvar_qtyrcv * (1 + womatlvar_scrap))) AS actqtyper 
           FROM womatlvar, itemsite AS component,
                itemsite AS parent
          WHERE ((womatlvar_parent_itemsite_id=parent.itemsite_id)
            AND (womatlvar_component_itemsite_id=component.itemsite_id)
            AND (womatlvar_bomitem_id=&lt;? value("bomitem_id") ?>)
            AND (womatlvar_posted BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
&lt;? if exists("warehous_id") ?>
            AND (component.itemsite_warehous_id=&lt;? value("warehous_id") ?>)
&lt;? endif ?>
                )
       ) AS data
ORDER BY posted;</sql>
 </querysource>
 <rpthead>
  <height>237</height>
  <field>
   <rect>
    <x>80</x>
    <y>140</y>
    <width>50</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>comp_seqnumber</column>
   </data>
  </field>
  <line>
   <xstart>5</xstart>
   <ystart>227</ystart>
   <xend>745</xend>
   <yend>227</yend>
   <weight>2</weight>
  </line>
  <field>
   <rect>
    <x>140</x>
    <y>180</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>warehouse</column>
   </data>
  </field>
  <label>
   <rect>
    <x>250</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Proj. Req.</string>
  </label>
  <field>
   <rect>
    <x>360</x>
    <y>60</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>parent_invuom</column>
   </data>
  </field>
  <label>
   <rect>
    <x>330</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Proj. Qty. per</string>
  </label>
  <label>
   <rect>
    <x>30</x>
    <y>180</y>
    <width>103</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Site:</string>
  </label>
  <label>
   <rect>
    <x>415</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Act. Issue</string>
  </label>
  <label>
   <rect>
    <x>30</x>
    <y>60</y>
    <width>103</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Item Number:</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>60</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>parent_number</column>
   </data>
  </field>
  <field>
   <rect>
    <x>360</x>
    <y>120</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>comp_invuom</column>
   </data>
  </field>
  <label>
   <rect>
    <x>665</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>%</string>
  </label>
  <label>
   <rect>
    <x>165</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Produced</string>
  </label>
  <label>
   <rect>
    <x>5</x>
    <y>215</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Post Date</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>80</y>
    <width>350</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>parent_descrip1</column>
   </data>
  </field>
  <label>
   <rect>
    <x>85</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Ordered</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>120</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>comp_number</column>
   </data>
  </field>
  <field>
   <rect>
    <x>140</x>
    <y>160</y>
    <width>350</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>comp_descrip2</column>
   </data>
  </field>
  <label>
   <rect>
    <x>305</x>
    <y>60</y>
    <width>50</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>UOM:</string>
  </label>
  <label>
   <rect>
    <x>580</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Qty. per Var.</string>
  </label>
  <label>
   <rect>
    <x>305</x>
    <y>120</y>
    <width>50</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>UOM:</string>
  </label>
  <label>
   <rect>
    <x>25</x>
    <y>140</y>
    <width>50</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Seq. #:</string>
  </label>
  <label>
   <rect>
    <x>493</x>
    <y>215</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Act. Qty. per</string>
  </label>
  <label>
   <rect>
    <x>200</x>
    <y>10</y>
    <width>545</width>
    <height>37</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Material Usage Variance By BOM Item</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>140</y>
    <width>350</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>comp_descrip1</column>
   </data>
  </field>
  <field>
   <rect>
    <x>140</x>
    <y>100</y>
    <width>350</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>parent_descrip2</column>
   </data>
  </field>
  <label>
   <rect>
    <x>15</x>
    <y>120</y>
    <width>120</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Component Item:</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>7</height>
 </pghead>
 <pghead>
  <height>22</height>
  <label>
   <rect>
    <x>5</x>
    <y>0</y>
    <width>75</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Post Date</string>
  </label>
  <label>
   <rect>
    <x>330</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Proj. Qty. per</string>
  </label>
  <label>
   <rect>
    <x>415</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Act. Issue</string>
  </label>
  <label>
   <rect>
    <x>250</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Proj. Req.</string>
  </label>
  <label>
   <rect>
    <x>165</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Produced</string>
  </label>
  <label>
   <rect>
    <x>665</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>%</string>
  </label>
  <label>
   <rect>
    <x>580</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Qty. per Var.</string>
  </label>
  <label>
   <rect>
    <x>493</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Act. Qty. per</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>15</ystart>
   <xend>745</xend>
   <yend>15</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>85</x>
    <y>0</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Ordered</string>
  </label>
 </pghead>
 <section>
  <name>detail</name>
  <detail>
   <key>
    <query>detail</query>
   </key>
   <height>17</height>
   <field>
    <rect>
     <x>250</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>f_projreq</column>
    </data>
   </field>
   <field>
    <rect>
     <x>665</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>f_percent</column>
    </data>
   </field>
   <field>
    <rect>
     <x>165</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>f_produced</column>
    </data>
   </field>
   <field>
    <rect>
     <x>85</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>f_ordered</column>
    </data>
   </field>
   <field>
    <rect>
     <x>415</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>f_actiss</column>
    </data>
   </field>
   <field>
    <rect>
     <x>5</x>
     <y>0</y>
     <width>75</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_posted</column>
    </data>
   </field>
   <field>
    <rect>
     <x>330</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>f_projqtyper</column>
    </data>
   </field>
   <field>
    <rect>
     <x>493</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>f_actqtyper</column>
    </data>
   </field>
   <field>
    <rect>
     <x>580</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>f_qtypervar</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>17</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>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>
  <label>
   <rect>
    <x>5</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>95</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>
</report>
