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