
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
date(cr.created_on) as _date
,'by api - ' as description
,'' as cheque_no
,c.customer_name as payee
,round(((sip.net_total/sip.qty)*rp.qty),2) as amount
,'DR' as `type`
,0 as tag_id
, ( case 
		when ifnull(pc.fn_acount_id,0) > 0 then ifnull(pc.fn_acount_id,0)
        else 110 end 
	) as account_id  
,cr.id as transaction_id
,'sales_return' as `event`
,0 as is_send 
,( case 
		when ifnull(pc.fn_acount_id,0) > 0 then pc.fn_account_name
        else 'Sales Revenue' end 
	) as account_code
,bil.fn_companyCode as location_id
,now() as cheque_date
,now() as sent_on
,0 as log_id
,now() as created_on
,si.code as transaction_code
    
from tbl_customer_returns cr
inner join tbl_return_products rp on rp.return_id = cr.id
inner join tbl_sale_invoice_products sip on sip.id = rp.sale_invoice_product_id
inner join tbl_sale_invoice si on si.id = cr.sale_invoice_id
inner join tbl_branch_inventory_locations bil on bil.id = si.location_id
inner join tbl_customers c on c.customer_id = si.customer_id
left join tbl_products p on p.product_id = rp.product_id
left join tbl_productcategory pc on pc.productCategory_id = p.category_id 
where cr.id = ?


