cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Queries for open sales orders with no invoice.

Former Member
0 Kudos

Hello all!

Is there a way to make a query to find out if there are any uninvoiced sales orders older than 5 days? I am having difficulty figuring out how the invoice is linked to the sales order.

I am more familiar with excel than SQL but I'm guessing it will be similar to the following:

SELECT * FROM ORDR WHERE today's date - posting date>5 AND (not sure what to check here for the invoice having been created)

**Note: my syntax is of course not exactly correct, this is just to give you an idea of what I'm looking for

Has anyone had to do this before or know how it would be done? It would be very useful for me.

Thanks!

Mike

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Mike

The document link is built on row level so the RDR1 should be used instead of ORDR to server your propose.

You could use this query to get all the lines do not link to a invoice.

*targettype 13 refers to target document invoice

select t0.docentry,t0.docdate,t0.linenum,t0.itemcode,t0.quantity,t0.price,t0.linetotal 
from rdr1 t0 join ordr t1 on t0.docentry= t1.docentry
where t0.trgetentry is null and t0.DocDate < dateadd("d",-5, getdate()) and t1.docstatus = 'O' 

or just use this query instead to find those lines do not link to any document

select t0.docentry,t0.docdate,t0.linenum,t0.itemcode,t0.quantity,t0.price,t0.linetotal 
from rdr1 t0 join ordr t1 on t0.docentry= t1.docentry
where t0.targettype !='13' and t0.DocDate < dateadd("d",-5, getdate()) and t1.docstatus = 'O'

Please modify the code according to your needs.

Regards,

Syn Qin

SAP Business One Forums Team

Edited by: Syn Qin on Aug 11, 2008 5:20 AM

Former Member
0 Kudos

Excellent, this is just what I'm looking for. Is there any way for me to GROUP by t0.docentry?

I have tried adding GROUP BY t0.docentry and entering it into the query generator but I get the following error:

1). [Microsoft][SQL Native Client][SQL Server]Column 'rdr1.DocDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contrac

Is there any way to do this?

Thanks!

Mike

Former Member
0 Kudos

Hi Mike

Of cause you can group by t0.docentry.But some non-aggregatable column like itemcode has to be given up.

select t0.docentry,count(linenum) as LineNumber,min(t0.docdate) as EarilestDocDate, sum(t0.linetotal) as DocTotal 
from rdr1 t0 join ordr t1 on t0.docentry= t1.docentry
where t0.trgetentry is null and t0.DocDate < dateadd("d",-5, getdate()) and t1.docstatus = 'O' 
group by t0.docentry

Regards,

Syn Qin

SAP Business One Forums Team

Answers (0)