openbravo: custom query to find invoice's product account

SELECT
c_invoice.documentno,
c_invoiceline.linenetamt,
--m_product_acct.m_product_id,
--m_product_acct.ad_org_id,
m_product_acct.p_expense_acct,
c_elementvalue.name

FROM
c_invoice
LEFT JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN m_product_acct ON c_invoiceline.m_product_id = m_product_acct.m_product_id
LEFT JOIN c_elementvalue ON c_elementvalue.c_elementvalue_id = m_product_acct.p_expense_acct

WHERE
dateinvoiced > '2017-01-01 00:00:00'
AND dateinvoiced < '2017-12-31 00:00:00'
AND issotrx = 'N'
AND m_product_acct.c_acctschema_id = '092273C810444B4086ED18612E3F5833'

ORDER BY
--c_invoice.dateinvoiced ASC
p_expense_acct asc

Leave a Reply

Your email address will not be published. Required fields are marked *