SELECT
b.billing_ID as System_Billing_ID,
concat(region.region_code,project.project_code,lpad(b.contract_ID,4,'0'),lpad(b.billing_ID,6,'0')) as Quotation_ID,
concat(contract.contract_name,' ','[',region.region_code,project.project_code,lpad(b.contract_ID,4,'0'),']',' - ',b.billing_month) as Quotation,
b.billing_name as Custom_Name ,b.billing_PO as PO, b.billing_status as Status , b.created_date as Registered_Date ,
media.media_name as Service_Fee ,bd.billing_name as Custom_Name,
bd.billing_billing as Billing , bd.billing_revenue as Revenue , bd.billing_cost as Cost , bd.billing_revenue - bd.billing_cost as Margin , bd.billing_invoice as Invoice_Number,
bd.billing_fee_included as Fee_Included , bd.billing_hide as Billing_Hide , bd.billing_note as Note
FROM mkt_billing_detail bd
right join mkt_billing b on bd.billing_ID = b.billing_ID
inner join mkt_media media on bd.media_ID = media.media_ID
inner join mkt_contract contract on b.contract_ID = contract.contract_ID
inner join mkt_project project on contract.project_ID = project.project_ID
inner join mkt_client cl on project.client_ID = cl.client_ID
inner join mkt_region region on cl.region_ID = region.region_ID
where (b.del != 'Y' and bd.del != 'Y')
order by b.billing_ID desc