SELECT   o.Ord_No,
         o.Host_Ord_Type,
         o.Host_prio,
         o.dest_Id,
         (SELECT COUNT(Ord_Line_No)
          FROM   Load l
          WHERE  l.Ord_cntrl_No = o.Ord_cntrl_No) AS Tot_Lines,
         (SELECT SUM(Tot_wght)
          FROM   Shipbol s
          WHERE  s.Ord_cntrl_No = o.Ord_cntrl_No) AS Tot_Weight,
         (SELECT SUM(qty)
          FROM   Load l
          WHERE  l.Ord_cntrl_No = o.Ord_cntrl_No) AS Tot_Units,
         (SELECT SUM(Act_Ship_Charge)
          FROM   Shipbol s
          WHERE  s.ConSol_cntrl_No = o.ConSol_cntrl_No) AS Tot_Cost,
         o.Box_Total,
         o.cUst_Ord_Date,
         o.Assgn_Ship_Date
FROM     OrdHead o,
         clsd_Task_Detail d,
         Shipbol s
WHERE    d.Ord_cntrl_No = o.Ord_cntrl_No
         AND o.Assgn_Ship_Date >= To_date('7/1/2007','mm/dd/yyyy')
         AND o.Assgn_Ship_Date <= To_date('3/17/2008','mm/dd/yyyy')
GROUP BY o.Ord_No,
         o.Host_Ord_Type,
         o.Host_prio,
         o.dest_Id,
         o.Box_Total,
         o.cUst_Ord_Date,
         o.Assgn_Ship_Date,
         o.Ord_cntrl_No,
         o.ConSol_cntrl_No;