Mar 17, 2015 at 05:15 AM

Sales Invoice Report - Date Wise (Tax Report)

296 Views

Hi Experts,

Am using following query to generate Sales Invoice Report - Date Wise (Tax Report).

SELECT DISTINCT T1.DOCNUM AS 'SAP NO.',T6.SERIESNAME , T1.NUMATCARD AS 'BILL NO.', T4.U_VSPCITY, T1.[TAXDATE],T1.[CARDCODE],T1.[CARDNAME],

(SELECT ((B.[DOCTOTAL]))-(B.VATSUM) FROM [DBO].[OINV] B INNER JOIN [DBO].[INV4] C ON B.DOCENTRY = C.DOCENTRY WHERE B.DOCENTRY=T1.DOCENTRY

AND C.STACODE = 'VAT5'GROUP BY B.DOCTOTAL,B.VATSUM) AS 'SALES 5%',

(SELECT SUM(C.TAXSUM) FROM [DBO].[INV4] C WHERE C.DOCENTRY =T1.DOCENTRY AND (C.STACODE='VAT5') ) AS 'VAT@5',

(SELECT ((B.[DOCTOTAL]))-(B.VATSUM) FROM[DBO].[OINV] B INNER JOIN[DBO].[INV4] C ON B.DOCENTRY = C.DOCENTRY WHERE B.DOCENTRY=T1.DOCENTRY

AND C.STACODE = 'VAT14.5'GROUP BY B.DOCTOTAL, B.VATSUM) AS 'SALES 14.5%',

(SELECT SUM(C.TAXSUM) FROM[DBO].[INV4] C WHERE C.DOCENTRY =T1.DOCENTRY AND (C.STACODE='VAT14.5') ) AS 'VAT@14.5',

(SELECT ((B.[DOCTOTAL]))-(B.VATSUM) FROM[DBO].[OINV] B INNER JOIN[DBO].[INV4] C ON B.DOCENTRY = C.DOCENTRY WHERE B.DOCENTRY=T1.DOCENTRY

AND C.STACODE = 'CST 2'GROUP BY B.DOCTOTAL, B.VATSUM) AS 'SALES CST 2%',

(SELECT SUM(C.TAXSUM) FROM[DBO].[INV4] C WHERE C.DOCENTRY =T1.DOCENTRY AND (C.STACODE='CST 2') ) AS 'CST@2', T1.DOCTOTAL

--(SELECT (SUM(B.[DOCTOTAL])) FROM[DBO].[OINV] B INNER JOIN[DBO].[INV4] C ON B.DOCENTRY = C.DOCENTRY WHERE B.DOCENTRY=T1.DOCENTRY

--GROUP BY B.DOCTOTAL, B.VATSUM) AS 'TOTAL'

FROM [DBO].[INV1] T0 INNER JOIN [DBO].[OINV] T1 ON T0.DOCENTRY = T1.DOCENTRY INNER JOIN [DBO].[OITM] T2 ON T0.ITEMCODE = T2.ITEMCODE

INNER JOIN [DBO].[OCRD] T4 ON T1.CARDCODE =

T4.CARDCODE INNER JOIN [DBO].[CRD7] T5 ON T1.CARDCODE = T5.CARDCODE INNER JOIN [DBO]. [NNM1] T6 ON T6.SERIES = T1 .SERIES

WHERE T1.[CANCELED]='N' AND T1.[TAXDATE] >=[%0] AND T1.[TAXDATE] <=[%1] AND T6.OBJECTCODE = '13'

By using the above query am not able to retrieve TIN Number. Please anyone can help me to get TIN Number in this report.