To prepare VAT Register -India Localization , how / from where to pull data

[Former Member]

A VAT Register report is required as featured below with the selection criteria/parameters and column names.

1. Output VAT Register Report Detailed with From Date and To Date Selection Range

Document Number Document Date Customer Name Customer VAT Reg No Base Document Total Vat Amount Vat Rate Gross Total

Which table (s) has / have the VAT information stored under India localization - 2007B running on MSSQL 2005.

Thank you

[Former Member · Accepted solution]

HI Joy,

We are using this SP as Purchase Register to get the details of not only vat, but BED, Ecess, Cess and Cst. I have called this SP in SAP through Biz-reporter add on.

Create PROC [dbo].[PurchaseRegister1]

(

@DATEFROM datetime,

@DATETO datetime

)

AS

SELECT DISTINCT T0.DOCENTRY, T0.[DOCNUM], T1.[DSCRIPTION],T1.[ITEMCODE],

CONVERT(VARCHAR(10), T0.[DOCDATE], 3) AS DOCDATE , T1.[QUANTITY], T1.[PRICE], T1.[TAXCODE], T1.[VATSUM],

( T1.LINETOTAL +T1.[VATSUM]) AS 'ROW TOTAL', ( T1.LINETOTAL) AS 'BASIC' , T0.[CARDCODE] AS 'VENDOR CODE',

(SELECT DISTINCT ISNULL(SUM(PCH4.TAXSUM),0) FROM PCH4 WHERE PCH4.STATYPE=-90 AND PCH4.DOCENTRY=T0.DOCENTRY AND PCH4.LINENUM = T1.LINENUM )BED,

(SELECT DISTINCT ISNULL(SUM(PCH4.TAXSUM),0) FROM PCH4 WHERE PCH4.STATYPE=-60 AND PCH4.DOCENTRY=T0.DOCENTRY AND PCH4.LINENUM = T1.LINENUM )CESS,

(SELECT DISTINCT ISNULL(SUM(PCH4.TAXSUM),0) FROM PCH4 WHERE PCH4.STATYPE=8 AND PCH4.DOCENTRY=T0.DOCENTRY AND PCH4.LINENUM = T1.LINENUM)HCESS,

(SELECT DISTINCT ISNULL(SUM(PCH4.TAXSUM),0) FROM PCH4 WHERE PCH4.STATYPE=4 AND PCH4.DOCENTRY=T0.DOCENTRY AND PCH4.LINENUM = T1.LINENUM)CST,

(SELECT DISTINCT ISNULL(SUM(PCH4.TAXSUM),0) FROM PCH4 WHERE PCH4.STATYPE=1 AND PCH4.DOCENTRY=T0.DOCENTRY AND PCH4.LINENUM = T1.LINENUM)VAT,(SELECT DISTINCT ISNULL(TAXID11,0) FROM CRD7 WHERE CRD7.CARDCODE =T0.CARDCODE AND TAXID11 IS NOT NULL) AS TINNO,DISCSUM,

T0.[CARDNAME] AS 'VENDOR NAME' , T0.[COMMENTS] FROM OPCH T0

INNER JOIN PCH1 T1 ON T0.DOCENTRY = T1.DOCENTRY

--INNER JOIN OITM T2 ON T1.ITEMCODE = T2.ITEMCODE

INNER JOIN PCH4 T3 ON T1.DOCENTRY =T3.DOCENTRY AND T1.LINENUM=T3.LINENUM

INNER JOIN CRD7 T4 ON T0.CARDCODE = T4.CARDCODE

WHERE (T0.Docdate>=@DATEFROM AND T0.Docdate <=@DATETO)

You can check the statype of the tax types in the OSTT table.

Hope this helps.

Thanks,

Joseph

[Former Member]

You may check this thread first:

To get some idea about which tables to use, check this:

Thanks,

Gordon