INSERT INTO `tbl_acc_details`(`_date`, `description`, `cheque_no`, `payee`, `amount`, `type`, `tag_id`, `account_id`, `transaction_id`, `event`, `is_send`, `account_code`, `location_id`, `cheque_date`, `sent_on`, `log_id`, `created_on`,transaction_code)
select 
gr.return_date as _date
,'by api - ' as description
,'' as cheque_no
,s.name as payee
,(gr.total-(round((case when ifnull(g.tax_type,'') = 'Exclusive' then (gr.total/100)*SUBSTRING_INDEX(g.tax, '~', -1)
	when ifnull(g.tax_type,'') = 'Inclusive' then (gr.total/(100+SUBSTRING_INDEX(g.tax, '~', -1)))*SUBSTRING_INDEX(g.tax, '~', -1)
    else 0 end),2))) as amount
,'CR' as `type`
,0 as tag_id
,34 as account_id 
,gr.grn_return_id as transaction_id
,'GRNReturn' as `event`
,0 as is_send
,'Inventory Stock:COLOMBO-07 STOCK' as account_code
,bil.fn_companyCode as location_id
,now() as cheque_date
,now() as sent_on
,'' as log_id
,now() as created_on
,gr.code as transaction_code
from tbl_grn_return gr 
inner join tbl_supplier s on s.supplier_id = gr.supplier_id
inner join tbl_branch_inventory_locations bil on bil.id = gr.location_id
left join tbl_grns g on g.grn_id = gr.grn_id
where gr.grn_return_id = ?