Skip to Content
0
Former Member
Oct 06, 2009 at 11:46 AM

Document Series Filtering

17 Views

Hi All,

Here we have a query which will display the open AR Invoices.

SELECT t0.DOCNUM as 'Invoice No', t0.DOCDATE as 'Invoice Date', 
t0.DOCDUEDATE as 'Due Date', t0.CARDCODE as 'Customer Code', 
t0.CARDNAME as 'Customer Name',t0.NUMATCARD as 'Cust Ref No',
t0.DOCTOTAL as 'Amount', T0.pAIDSUM aS 'Paid Amount', (t0.DOCTOTAL- T0.pAIDSUM) aS 'Pending Amount',datediff(dd,getdate() ,t0.DOCdueDATE)as 'Due Days' 
FROM OINV T0 INNER JOIN NNM1 T1  ON T0.Series = T1.Series 
WHERE 
T0.[CardName] >=[%0] AND  T0.[CardName] <=[%1] AND  T0.[DocDate] >=[%2] AND  T0.[DocDate] <=[%3] AND  T1.[SeriesName] >=[%4] AND  T1.[SeriesName] <=[%5] AND T0.DOCSTATUS='O'

In the above query we have included NNM1 table to filter the document series as we have four different series for different brances like for Bangalore- BAN09-10, for Delhi -DEL09-10 etc..

Now the problem is the document series is displaying all series which are not assigned for Invoice( document like 'BAN91-10' which is for Journal Entry)

We want to filter the series which are assigned for AR Invoice.