<!DOCTYPE openRPTDef>
<report>
 <title>Pick List WO Show Locations</title>
 <name>PickListWOShowLocations</name>
 <description>This WO Pick List Shows All Locations and Lots for Each Requirement</description>
 <parameter default="565" active="true" listtype="static" type="integer" name="wo_id" >
  <description></description>
 </parameter>
 <size>Letter</size>
 <portrait/>
 <topmargin>50</topmargin>
 <bottommargin>28</bottommargin>
 <rightmargin>33</rightmargin>
 <leftmargin>33</leftmargin>
 <querysource>
  <name>Head</name>
  <sql>SELECT formatWoNumber(wo_id) AS wonumber, wo_prodnotes,
                item_number, uom_name, item_descrip1, item_descrip2,
                warehous_code, formatQty(wo_qtyord) AS qtyord,
                formatQty(wo_qtyrcv) AS qtyrcv,
                formatDate(wo_startdate) AS startdate,
                formatDate(wo_duedate) AS duedate
         FROM wo, itemsite, item, whsinfo, uom
         WHERE ((wo_itemsite_id=itemsite_id)
          AND (itemsite_item_id=item_id)
          AND (item_inv_uom_id=uom_id)
          AND (itemsite_warehous_id=warehous_id)
          AND (wo_id=%1))
    </sql>
 </querysource>
 <querysource>
  <name>Detail</name>
  <sql>SELECT
     warehous_code, 
     wo_number,
     womatl_id,
     item_number,
     item_descrip1,
     item_descrip2, 
     uom_name,
--   itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * womatl_qtyreq AS required_qty,
     womatl_qtyreq AS required_qty,
--   formatqty(itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * womatl_qtyreq) AS required_fmt,
     formatqty(womatl_qtyreq) AS required_fmt,
--   itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * womatl_qtyiss AS issued_qty,
     womatl_qtyiss AS issued_qty,
     formatqty(womatl_qtyiss) AS issued_fmt,
--   (itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * womatl_qtyreq) - 
               (itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id)  * womatl_qtyiss) AS pick_qty,
     womatl_qtyreq - womatl_qtyiss AS pick_qty,   
    formatqty(womatl_qtyreq - womatl_qtyiss) AS pick_fmt,
     CASE WHEN (womatl_issuemethod='S') THEN text('Push')
            WHEN (womatl_issuemethod='L') THEN text('Pull')
            WHEN (womatl_issuemethod='M') THEN text('Mixed')
            ELSE text(womatl_issuemethod)
     END AS f_issuemethod,
     location_aisle || '-' || location_rack || '-' || location_bin || '-' || location_name  AS location_name,
     formatlotserialnumber(itemloc_ls_id),
--     itemloc_lotserial,
     itemsite_location_comments AS location_comment,
     itemloc_qty AS location_qty_qty,
     itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * itemloc_qty AS loc_issue_uom_qty,
     formatqty(itemuomtouomratio(item_id,item_inv_uom_id, womatl_uom_id) * itemloc_qty) AS loc_issue_uom_fmt,
     CASE WHEN itemloc_expiration = '1970-01-01' THEN text ('N/A')
          WHEN itemloc_expiration = '2100-01-01' THEN text ('N/A')
          ELSE CAST(formatdate(itemloc_expiration) AS text)
     END AS expiration 

FROM

     item, uom, whsinfo, womatl, wo, itemsite
     LEFT OUTER JOIN itemloc  ON (itemloc_itemsite_id = itemsite_id)
     LEFT OUTER JOIN location ON (itemloc_location_id = location_id)


WHERE
     itemsite_item_id = item_id
     and 
     itemsite_warehous_id = warehous_id
     and
     womatl_uom_id = uom_id
     and
     itemsite_id = womatl_itemsite_id
     and
     womatl_wo_id = wo_id
     and
     wo_id = &lt;? value("wo_id") ?>
ORDER BY wo_number, womatl_id, item_number, itemloc_expiration, location_name;</sql>
 </querysource>
 <rpthead>
  <height>188</height>
  <field>
   <rect>
    <x>196</x>
    <y>5</y>
    <width>150</width>
    <height>40</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>wonumber</column>
   </data>
  </field>
  <field>
   <rect>
    <x>196</x>
    <y>140</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>qtyord</column>
   </data>
  </field>
  <label>
   <rect>
    <x>489</x>
    <y>100</y>
    <width>154</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Start Date:</string>
  </label>
  <label>
   <rect>
    <x>37</x>
    <y>121</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Site:</string>
  </label>
  <field>
   <rect>
    <x>195</x>
    <y>120</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>warehous_code</column>
   </data>
  </field>
  <label>
   <rect>
    <x>37</x>
    <y>5</y>
    <width>154</width>
    <height>40</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Work Order #:</string>
  </label>
  <field>
   <rect>
    <x>196</x>
    <y>102</y>
    <width>300</width>
    <height>17</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>item_descrip2</column>
   </data>
  </field>
  <field>
   <rect>
    <x>649</x>
    <y>100</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>startdate</column>
   </data>
  </field>
  <field>
   <rect>
    <x>196</x>
    <y>65</y>
    <width>200</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>item_number</column>
   </data>
  </field>
  <field>
   <rect>
    <x>649</x>
    <y>120</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>duedate</column>
   </data>
  </field>
  <field>
   <rect>
    <x>649</x>
    <y>80</y>
    <width>130</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>report_date</column>
   </data>
  </field>
  <field>
   <rect>
    <x>196</x>
    <y>84</y>
    <width>300</width>
    <height>17</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>item_descrip1</column>
   </data>
  </field>
  <label>
   <rect>
    <x>489</x>
    <y>120</y>
    <width>154</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Due Date:</string>
  </label>
  <field>
   <rect>
    <x>196</x>
    <y>158</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>Head</query>
    <column>qtyrcv</column>
   </data>
  </field>
  <label>
   <rect>
    <x>489</x>
    <y>80</y>
    <width>154</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Date:</string>
  </label>
  <label>
   <rect>
    <x>37</x>
    <y>140</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Qty. Ordered:</string>
  </label>
  <label>
   <rect>
    <x>37</x>
    <y>158</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Qty. Received:</string>
  </label>
  <label>
   <rect>
    <x>575</x>
    <y>5</y>
    <width>204</width>
    <height>40</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>20</size>
    <weight>bold</weight>
   </font>
   <right/>
   <top/>
   <string>WO Pick List</string>
  </label>
  <label>
   <rect>
    <x>37</x>
    <y>65</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>14</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Item:</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>6</height>
 </pghead>
 <pghead>
  <height>6</height>
 </pghead>
 <section>
  <name>detail_group</name>
  <group>
   <name>detail_group</name>
   <column>womatl_id</column>
   <head>
    <height>133</height>
    <field>
     <rect>
      <x>208</x>
      <y>40</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>required_fmt</column>
     </data>
    </field>
    <field>
     <rect>
      <x>330</x>
      <y>55</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>f_issuemethod</column>
     </data>
    </field>
    <label>
     <rect>
      <x>330</x>
      <y>15</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Issue Method</string>
    </label>
    <label>
     <rect>
      <x>330</x>
      <y>0</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Qty. Issued</string>
    </label>
    <label>
     <rect>
      <x>465</x>
      <y>15</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Qty. To Pick</string>
    </label>
    <line>
     <xstart>10</xstart>
     <ystart>35</ystart>
     <xend>822</xend>
     <yend>35</yend>
     <weight>2</weight>
    </line>
    <label>
     <rect>
      <x>208</x>
      <y>0</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Qty. Required</string>
    </label>
    <field>
     <rect>
      <x>575</x>
      <y>40</y>
      <width>45</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>
    <label>
     <rect>
      <x>660</x>
      <y>15</y>
      <width>120</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Total Qty. Picked</string>
    </label>
    <field>
     <rect>
      <x>469</x>
      <y>75</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>location_comment</column>
     </data>
    </field>
    <line>
     <xstart>80</xstart>
     <ystart>100</ystart>
     <xend>817</xend>
     <yend>100</yend>
     <weight>1</weight>
    </line>
    <label>
     <rect>
      <x>240</x>
      <y>100</y>
      <width>125</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>Lot</string>
    </label>
    <label>
     <rect>
      <x>37</x>
      <y>0</y>
      <width>150</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>Item Number</string>
    </label>
    <field>
     <rect>
      <x>469</x>
      <y>40</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>Detail</query>
      <column>pick_fmt</column>
     </data>
    </field>
    <field>
     <rect>
      <x>37</x>
      <y>60</y>
      <width>195</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>37</x>
      <y>40</y>
      <width>160</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>
    <label>
     <rect>
      <x>305</x>
      <y>75</y>
      <width>125</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Location Coment;</string>
    </label>
    <label>
     <rect>
      <x>540</x>
      <y>104</y>
      <width>90</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Expiration</string>
    </label>
    <label>
     <rect>
      <x>37</x>
      <y>15</y>
      <width>300</width>
      <height>14</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>Item Description</string>
    </label>
    <field>
     <rect>
      <x>208</x>
      <y>55</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>uom_name</column>
     </data>
    </field>
    <field>
     <rect>
      <x>37</x>
      <y>75</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>
    <label>
     <rect>
      <x>208</x>
      <y>15</y>
      <width>100</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Issue UOM</string>
    </label>
    <field>
     <rect>
      <x>330</x>
      <y>40</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>issued_fmt</column>
     </data>
    </field>
    <line>
     <xstart>680</xstart>
     <ystart>57</ystart>
     <xend>780</xend>
     <yend>57</yend>
     <weight>1</weight>
    </line>
    <label>
     <rect>
      <x>405</x>
      <y>104</y>
      <width>125</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <hcenter/>
     <vcenter/>
     <string>Qty / UOM</string>
    </label>
    <label>
     <rect>
      <x>645</x>
      <y>104</y>
      <width>140</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Loc / Lot Qty Picked</string>
    </label>
    <label>
     <rect>
      <x>80</x>
      <y>100</y>
      <width>125</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <left/>
     <vcenter/>
     <string>Location</string>
    </label>
   </head>
   <foot>
    <height>36</height>
    <line>
     <xstart>75</xstart>
     <ystart>25</ystart>
     <xend>825</xend>
     <yend>25</yend>
     <weight>1</weight>
    </line>
    <line>
     <xstart>75</xstart>
     <ystart>5</ystart>
     <xend>822</xend>
     <yend>5</yend>
     <weight>1</weight>
    </line>
    <field>
     <rect>
      <x>465</x>
      <y>5</y>
      <width>50</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>355</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>loc_issue_uom_qty</column>
     </data>
     <tracktotal subtotal="true" builtin="true" >qty</tracktotal>
    </field>
    <label>
     <rect>
      <x>185</x>
      <y>5</y>
      <width>165</width>
      <height>20</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>10</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Total All Lot/Locations:</string>
    </label>
   </foot>
  </group>
  <detail>
   <key>
    <query>Detail</query>
   </key>
   <height>21</height>
   <field>
    <rect>
     <x>75</x>
     <y>0</y>
     <width>115</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>Detail</query>
     <column>location_name</column>
    </data>
   </field>
   <field>
    <rect>
     <x>555</x>
     <y>0</y>
     <width>75</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>Detail</query>
     <column>expiration</column>
    </data>
   </field>
   <field>
    <rect>
     <x>465</x>
     <y>0</y>
     <width>50</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>
   <line>
    <xstart>690</xstart>
    <ystart>17</ystart>
    <xend>790</xend>
    <yend>17</yend>
    <weight>1</weight>
   </line>
   <field>
    <rect>
     <x>215</x>
     <y>0</y>
     <width>115</width>
     <height>20</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>10</size>
     <weight>normal</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>Detail</query>
     <column>formatlotserialnumber</column>
    </data>
   </field>
   <field>
    <rect>
     <x>355</x>
     <y>0</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>loc_issue_uom_fmt</column>
    </data>
   </field>
  </detail>
 </section>
 <rptfoot>
  <height>57</height>
  <text>
   <rect>
    <x>110</x>
    <y>30</y>
    <width>480</width>
    <height>22</height>
   </rect>
   <bottompad>0</bottompad>
   <font>
    <face>Arial</face>
    <size>12</size>
    <weight>48</weight>
   </font>
   <left/>
   <top/>
   <data>
    <query>Head</query>
    <column>wo_prodnotes</column>
   </data>
  </text>
  <label>
   <rect>
    <x>110</x>
    <y>10</y>
    <width>194</width>
    <height>22</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>12</size>
    <weight>48</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Production Notes:</string>
  </label>
 </rptfoot>
</report>
