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;

select a.c_order_id from c_order a where a.documentno='24307';

select a.ad_client_id, dt.name, count(*)
from 
	c_order a
join c_orderline b on a.c_order_id = b.c_order_id
join c_doctype dt on a.c_doctypetarget_id = dt.c_doctype_id
group by a.ad_client_id, dt.name
order by dt.name
;

With more detail

select 
  a.ad_client_id, client.name as client_name, org.value as org_value, org.name as org_name, usr.name as user_createdby,
  bp.value as bp_value, bp.name as bp_name, 
  dt.name as doctype_name, a.documentno, a.dateordered, a.docstatus, a.totallines, a.grandtotal,
  b.line, b.m_product_id, prod.name as productName, b.qtyentered, b.pricelimit, b.priceentered, b.pricelist, b.priceactual, 
	b.discount, b.pricelist - b.priceactual as discountAmt, b.linenetamt,
from 
	c_order a
left join c_orderline b on a.c_order_id = b.c_order_id
left join ad_client client on client.ad_client_id = a.ad_client_id
left join ad_org org on org.ad_org_id = a.ad_org_id
left join c_doctype dt on a.c_doctype_id = dt.c_doctype_id
left join m_product prod on prod.m_product_id = b.m_product_id
left join c_bpartner bp on bp.c_bpartner_id = a.c_bpartner_id
left join ad_user usr on usr.ad_user_id = a.createdby
where
  a.ad_client_id > 111 and
  a.grandtotal = 0 and
  a.docstatus in  ('CO', 'VO') and
order by a.dateordered desc;

Invoice and tax

select 
  b.c_invoice_id, b.c_invoiceline_id, a.documentno, a.totallines, a.grandtotal, a.istaxincluded, a.dateinvoiced, a.dateacct,
  a.docstatus, coalesce(a.c_doctype_id, a.c_doctypetarget_id) as c_doctype_id, 
	b.qtyinvoiced,b.qtyentered, b.pricelist, b.priceactual, b.pricelimit, b.priceentered, b.linenetamt, 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

Link from POS Order to the invoice to the payment

select
	posorder.documentno, pmt.documentno
from
	c_order posorder
join 
	c_invoice inv on inv.c_order_id = posorder.c_order_id
join
	c_payment pmt on pmt.c_payment_id = inv.c_payment_id
where posorder.documentno = '80014';