SELECT		y.dba_name,
					m.client_name, m.client_add, m.client_city, m.client_state, m.client_zip, m.client_sid, m.customer_name, 
					m.customer_add, m.customer_add1, m.customer_add2, m.customer_city, m.customer_state, m.customer_zip, m.customer_sid,
					m.customer_location, m.email, m.bol_number, m.carrier_name, m.pro_number,
					case m.pay_type when 1 then 'P' when 2 then 'C' when 3 then '3' else 'A' end as 'pay_type',
					m.prepaid_amt, m.tbl_req_date,
					m.tbl_spot_quote, m.tbl_f_charge, m.tbl_notes, m.special_instructions, m.bol_status, m.ship_date, m.ref_number, m.code,
					m.confirm_date, m.est_charges_manual, m.accrue_amt_manual, m.bolpk, m.mastid, m.fp_tmstmp, m.fp_btsinvdt, m.fp_btsinv,
					m.bol_status_id, m.fiscalpd, m.accrueamt, m.acno, m.io, m.crp_status_id, m.mode_id, m.routescac, m.routeused, m.routeamt,
					m.rate_status, m.imported, m.datasrc, m.delivery_notes, m.delivery_date, m.delivered, m.i_fbttl, m.i_lhamt, m.i_fsamt, m.i_acamt, a.ref_num as ref_num,
					y.pack_qty, y.weight, y.h_unit_qty, m.master_bol_number, m.master_bol_stop_num, isnull(cast(b.h_unit_qty as varchar(4)),'') as uct, h_unit_type_txt as uty  
			FROM (SELECT x.bol_id, MAX(x.dba_name) as dba_name, max(x.pack_qty) as pack_qty, MAX(x.weight) as weight, SUM(g.h_unit_qty) as h_unit_qty
					FROM 
						(SELECT m2.bol_id, MAX(c.dba_name) as dba_name, SUM(u.pack_qty) as pack_qty, SUM(u.weight) as weight, MAX(m2.bol_status) as bol_status
							FROM tbl_bol m2 inner join tbl_user_tbl c on m2.dba_id = c.dba_id
								INNER JOIN tbl_bol_carrier u on m2.bol_id=u.bol_id
							WHERE deleted='false' and ship_date >= @ShippedLo and ship_date <= @ShippedHi and c.rate_id=@ClientCode 
							group by m2.bol_id) x
						inner join tbl_bol_carrier_group g on x.bol_id=g.bol_id
						group by x.bol_id) y inner join tbl_bol m on y.bol_id=m.bol_id
						left join #TempRefTable2 a on m.bol_id = a.bol_id
			order by client_zip, customer_zip

SELECT 
            isnull(cast(b.h_unit_qty as varchar(4)),'') as uct, h_unit_type_txt as uty  
		FROM tbl_bol_carrier a
            left join tbl_bol_carrier_group b on b.handling_group_id = a.handling_group_id
            left join fm_handling_unit_type c on c.h_unit_type_id = b.h_unit_type_id
		WHERE a.bol_id = @bol_id