VbzCart/queries/qry PkgItem qtys byOrder

Details

 * Returns: data for items packaged for each order -- totals actually put into non-voided packages ("QtySent") and totals "handled" ("QtyDone" - sent or marked as n/a or cancelled)
 * Requires: ,
 * Used by:
 * History:
 * 2008-11-16 created for new restocking process
 * Fields:
 * QtyExtra quantities can't be included in the total for QtyDone or else freebies will cause problems (open order lines dues to "overfulfillment", plus they shouldn't count against ordered items)

SQL
CREATE OR REPLACE VIEW qry_PkgItem_qtys_byOrder AS SELECT COUNT(opl.ID) AS PkgCount, opl.ID_Item, opl.ID_OrdLine, op.ID_Order, SUM(IFNULL(QtyShipped,0)+IFNULL(QtyExtra,0)) AS QtySent, SUM(IFNULL(QtyShipped,0)+IFNULL(QtyKilled,0)+IFNULL(QtyNotAvail,0)) AS QtyDone FROM ord_pkg_lines AS opl LEFT JOIN ord_pkgs AS op ON opl.ID_Pkg=op.ID WHERE (op.ID IS NOT NULL) AND (op.WhenVoided IS NULL) GROUP BY ID_Item, opl.ID_OrdLine, op.ID_Order ORDER BY ID_Order, ID_Item;