Queries: Invoices, Orders and Quotes

Orders

select * from 
	c_order a
join c_orderline b on a.c_order_id = b.c_order_id
where
	a.documentno='21686';

select a.documentno, b.line, b.qtyentered, b.pricelimit, b.priceentered, b.pricelist, b.priceactual, 
	b.discount, b.pricelist - b.priceactual as discountAmt, b.linenetamt from 
	c_order a
join c_orderline b on a.c_order_id = b.c_order_id
where
	a.documentno='24307'
order by b.line;

Invoice and tax

select 
	a.documentno, a.totallines, a.grandtotal, a.istaxincluded, a.dateinvoiced, a.dateacct,
	b.qtyinvoiced,b.qtyentered, b.pricelist, b.priceactual, b.pricelimit, b.priceentered, b.linetotalamt, b.taxamt 
	from 
c_invoice a
join c_invoiceline b on a.c_invoice_id = b.c_invoice_id
where
	a.documentno='INW82785';

select
	* 
from
	c_invoicetax tax
join c_invoice a on a.c_invoice_id = tax.c_invoice_id
where
	a.documentno='INW82785';
	
select * from 
	c_invoice a
join c_invoiceline b on a.c_invoice_id = b.c_invoice_id
where
	a.documentno='21686';
	
select a.ad_client_id, client.name, a.c_bpartner_id, b.value, b.name, a.issotrx, max(a.dateinvoiced), min(a.dateinvoiced), count(*) from 
	c_invoice a
join c_bpartner b on a.c_bpartner_id = b.c_bpartner_id
join ad_client client on a.ad_client_id = client.ad_client_id
group by
	a.ad_client_id, client.name, a.c_bpartner_id, b.value, b.name, a.issotrx
order by a.ad_client_id, a.issotrx, count(*) desc;

Set invoice as paid (system error)

select ispaid, * from c_invoice where c_invoice_id = 1047724;

select * into bk_20150630_c_invoice from c_invoice where c_invoice_id = 1047724;

update c_invoice set ispaid = 'Y' where c_invoice_id = 1047724;

Payments

Last payment related to an invoice

SELECT p.DateTrx,p.PayAmt,alp.Amount
FROM C_AllocationLine ali
INNER JOIN C_AllocationHdr ah ON (ah.C_AllocationHdr_ID = ali.C_AllocationHdr_ID AND ah.DocStatus IN ('CO', 'CL'))
INNER JOIN C_AllocationLine alp ON (alp.C_AllocationHdr_ID = ah.C_AllocationHdr_ID)
INNER JOIN C_Payment p ON (p.C_Payment_ID= alp.C_Payment_ID AND p.DocStatus IN ('CO', 'CL'))
WHERE ali.C_Invoice_ID = ?
ORDER BY p.DateTrx DESC
LIMIT 1

Discussion here

Credit:Anozi Mada