VbzCart/queries/qryPkgs status

About

• Requires: ord_pkg_lines, ord_pkgs, ord_shipmt, ord_lines, core_orders
• Grouped by: Package
• Used by: VbzAdmin shipment detail display
• History:
  • 2009-07-09 Adapted from MS Access version

SQL

<mysql>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;</mysql>