VbzCart/queries/qryPkgLines qtys done

About

• Requires: ord_pkgs
• Returns: sum of "done" quantities for all packages which have been checked in each order
  • In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled).
  • There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl.
• History:
  • 2011-05-31 created for packing slips

SQL

<mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS SELECT

 p.ID_Order,
 pl.ID_OrdLine,
 pl.ID_Item,
 SUM(pl.QtyShipped + pl.QtyKilled + pl.QtyNotAvail) AS QtyDone

FROM

 ord_pkgs AS p LEFT JOIN
 ord_pkg_lines as pl ON pl.ID_Pkg=p.ID

WHERE (WhenChecked IS NOT NULL) GROUP BY ID_Order, ID_OrdLine, ID_Item;</mysql>