VbzCart/queries/qryPkgs status

About

 * Requires:, , , ,
 * Grouped by: Package
 * Used by: VbzAdmin shipment detail display
 * History:
 * 2009-07-09 Adapted from MS Access version

SQL
CREATE OR REPLACE VIEW qryPkgs_status AS SELECT p.ID_Order, p.ID AS ID_Package, CONCAT("(",s.ID,") ",IF(s.Abbr="",s.WhenShipped,s.Abbr)) AS Shipment, p.ID_Shipment, CONCAT(o.Number,"-",p.Seq) AS PackageName, p.WhenStarted, p.WhenFinished, p.ShipCost, p.PkgCost, s.WhenShipped, Sum(oi.QtyOrd) AS Ordered, Sum(pl.QtyShipped) AS Shipped, Sum(pl.QtyNotAvail) AS Deleted, Sum(pl.QtyKilled) AS Cancelled, Sum(oi.QtyOrd-IFNULL(pl.QtyShipped,0)-IFNULL(pl.QtyNotAvail,0)-IFNULL(pl.QtyKilled,0)) AS Remaining, WhenChecked FROM (   (      (ord_pkg_lines AS pl RIGHT JOIN ord_pkgs AS p ON pl.ID_Pkg = p.ID) LEFT JOIN ord_shipmt AS s ON p.ID_Shipment = s.ID)   LEFT JOIN ord_lines AS oi ON pl.ID_OrdLine=oi.ID) LEFT JOIN core_orders AS o ON p.ID_Order=o.ID GROUP BY p.ID, p.ID_Order, p.ID_Shipment, p.ShipCost, p.PkgCost, s.Abbr, o.Number, p.Seq, p.WhenStarted, p.WhenFinished, s.WhenShipped, s.ID, WhenChecked ORDER BY o.Number, p.Seq;