I want to take some fields from the sale_order and the respective invoice date(date_invoice) from the account_invoice. I can't find the exact link between these two tables. While after creating sale order, and then after the creation of customer invoice, there will be the origin field in the account_invoice which will have the name of the respective sale order prefix with delivery number or some thing.

I cant match these two tables, one way is to customize and make the sale_id to fall in account_invoice. But for the transactions which had been already recorded wont have the sale_id fallen on account_invoice.

and other way what i did is

select

so.id,

so.name,

rp.name,

so.date_order,

sol.deliver_date,

pt.name,

sol.product_uom_qty,

so.state,

so.shipped,

ai.date_invoice

from

sale_order so

left join sale_order_line sol on (sol.order_id=so.id)

left join res_partner rp on (so.partner_id=rp.id)

left join product_product pp on (sol.product_id=pp.id)

left join product_template pt on (pt.id = pp.product_tmpl_id)

left outer join account_invoice ai on (ai.origin=so.name)

where

1=1

order by

so.id

but it will fetch only when both name so.name and ai.origin is same.. But other with prefix it is not coming.

I have also taken the sale order name from origin of account_invoice using

select substring(origin, position(':' in origin)+1, length(origin)) from account_invoice

but the data is not correct. pls tell me what is the link to match both these tables and to get the date_invoice from the account_invoice table for the respective sale_order