cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Invoice Report - Date Wise (Tax Report)

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member209066
Active Contributor
0 Kudos

Hi,

Please Check this

SELECT DISTINCT T1.DOCNUM AS 'SAP NO.',T6.SERIESNAME , T1.NUMATCARD AS 'BILL NO.',

(Select Max(X.TaxId11) from CRD7 X Where X.CardCode=T4.CardCode Order by X.TaxId11 desc) as 'TIN 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'

Thanks,

Nithi

Former Member
0 Kudos

Dear Mr.Nithi,

I was checked this query, But no result. am getting the following error.

[Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Thanks & Regards,

Abdul Rasheed P

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT DISTINCT T1.DOCNUM AS 'SAP NO.',T6.SERIESNAME , T1.NUMATCARD AS 'BILL NO.',

(Select Max(X.TaxId11) from CRD7 X Where X.CardCode=T4.CardCode ) as 'TIN NO'

,  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'

Thanks & Regards,

Nagarajan

former_member209066
Active Contributor
0 Kudos

Hi,

Check this

SELECT DISTINCT T1.DOCNUM AS 'SAP NO.',T6.SERIESNAME , T1.NUMATCARD AS 'BILL NO.',

(Select Top 1 X.TaxId11 from CRD7 X Where X.CardCode=T4.CardCode and isnull(X.TaxId11,'0')<>'0' ) as 'TIN 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'

Thanks,

Nithi

Answers (0)