VbzCart/docs/queries/qryOrderLines notPkgd

Details

• Returns: Active order lines which have yet to be (completely) put into a package
• Requires: Template:Vbzcart/table, Template:Vbzcart/table
• Used by:

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS SELECT

 oi.ID,
 oi.ID_Order,
 oi.ID_Item,
 oi.CatNum,
 oi.Descr,
 oi.QtyOrd,
 oi.Price,
 oi.QtyOrd
   -SUM(
     IFNULL(pi.QtyShipped,0)
     +IFNULL(pi.QtyNotAvail,0)
     +IFNULL(pi.QtyKilled,0)
   ) AS QtyOpen,
 SUM(IFNULL(pi.QtyShipped,0)) AS QtyShipped,
 SUM(IFNULL(pi.QtyShipped,0)
   +IFNULL(pi.QtyNotAvail,0)
   +IFNULL(pi.QtyKilled,0)) AS QtyHandled

FROM

 ord_lines AS oi
   LEFT JOIN ord_pkg_lines pi ON pi.ID_OrdLine=oi.ID

GROUP BY

 oi.ID,
 oi.ID_Order,
 oi.ID_Item,
 oi.CatNum,
 oi.Descr,
 oi.QtyOrd,
 oi.Price

HAVING oi.QtyOrd-QtyHandled >0;</mysql> <section end=sql />