VbzCart/queries/qryPkgs status
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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>