Skip to Content
0
Former Member
Jan 09, 2011 at 07:48 AM

Query

41 Views

Hi

SELECT M.CardCode,M.CardName as 'Vendor Name',m.DocNum,

(SELECT Sum(TaxSum) FROM INV4 where relatetype = 1 and docentry=m.docentry and stccode = t.stccode as 'AmountOfTax'

FROM OINV M Inner JOIN INV1 L on L.DocEntry=M.DocEntry

Inner JOIN INV4 T on T.DocEntry=L.DocEntry

Inner JOIN INV12 T0 on T.DocEntry=L.DocEntry

INNER JOIN OLCT T2 ON L.LocCode = T2.Code

WHERE T2.[Location]=@location and

M.[DocDate] >= @fr_date and M.[DocDate] <= @to_date

GROUP BY

M.CardCode,M.CardName,m.docnum,M.DocEntry,T.stccode

Union All

SELECT M.CardCode,M.CardName as 'Vendor Name',m.DocNum,

(SELECT Sum(TaxSum) FROM INV4 where (statype=1 or statype = 7) and (relatetype = 1 and docentry=m.docentry and stccode = t.stccode)) as 'AmountOfTax'

FROM Ordn M Inner JOIN rdn1 L on L.DocEntry=M.DocEntry

Inner JOIN INV4 T on T.DocEntry=L.DocEntry

Inner JOIN INV12 T0 on T.DocEntry=L.DocEntry

INNER JOIN OLCT T2 ON L.LocCode = T2.Code

WHERE T2.[Location]=@location and

M.[DocDate] >= @fr_date and M.[DocDate] <= @to_date

GROUP BY

M.CardCode,M.CardName,m.docnum,M.DocEntry,T.stccode

In this i don't want those invoices which have been wrongly created and their A/r Credit Note has been created . These entries will have same Doc Total and UDF field in Credit Note will have 'N'

Thanks