VbzCart/docs/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: Template:Vbzcart/table, Template:Vbzcart/table
• 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

<section begin=sql /><mysql>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;</mysql> <section end=sql />