SELECT 
 a.flight_no, 
 date_format(a.flight_date,"%d-%b-%y") as flight_date, 
 g.owner_name AS Airlines, 
 a.awb AS hawb, 
 a.mawb, 
 a.tot_package AS Pcs, 
 a.tot_weight AS Wgt, 
 a.tot_dimensi AS Cw, 
 a.consignee_name AS Cust_Name, 
 a.consignee_company AS Cust_Consignee, 
 f.uredi AS clearance, 
 d.number_of_days AS days_no, 
 b.dd_no, c.cdo_no, 
 date_format(c.cdo_date,"%d-%b-%y") as cdo_date, 
 d.invoice_no, 
 e.total_charge, 
 CASE WHEN e.payment_type ="D" THEN "DEPOSIT" WHEN e.payment_type ="C" THEN "CASH" ELSE "PENDING" END AS payment_type, 
 date_format(c.cdo_date,"%d-%b-%y") as Rls_date, 
 a.warehouse AS location 

FROM t_shipment_hawb a 
 LEFT JOIN t_master_clearance_type f ON a.clearance_type_id=f.kdedi 
 LEFT JOIN t_dd b ON a.awb = b.awb AND a.flight_no=b.flight_no AND a.flight_date=b.flight_date 
 LEFT JOIN t_invoice_detail d ON b.dd_no=d.reference_no 
 LEFT JOIN t_cdo_detail c ON b.dd_no=c.dd_no AND b.awb=c.awb AND b.mawb=c.mawb AND a.flight_date=c.flight_date 
 LEFT JOIN t_invoice e ON d.invoice_no=e.invoice_no AND d.date_out=e.invoice_date AND d.exim=e.exim 

# gue remark karena muter2x. a.flight_no nya di cari lagi di where. jadinya lsg aja di define di sini
 #LEFT JOIN t_owner g ON g.flight_code = SUBSTRING(a.flight_no, 1, 2) 
 LEFT JOIN t_owner g ON g.flight_code like "OZ%"

where 
 c.cdo_date <> 0 
 and c.cdo_date between "2018-10-10" and "2018-11-10" 
# and c.cdo_date = "2018-10-10"
# and a.flight_no like "%OZ%" 
 and d.is_btb="N" 
 AND d.exim="I" 
 and e.void="N" 

ORDER BY c.cdo_date,a.flight_no #ASC