cancel
Showing results for 
Search instead for 
Did you mean: 

Tables for Sales Register

former_member296295
Participant
0 Kudos

Hi,

I want to create Sales register for all the taxes like BED, VAT etc... What are the tables from which the data need to picked?

Kalli

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Kalli,

I have this which uses OINV (invoice as a base for sale register).

DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
/* SELECT T0.DocDate FROM OINV T0 where T0.DocDate >=[%0] */
SET @FromDate = [%0]
/* SELECT T0.DocDate FROM OINV T0 where T0.DocDate <=[%0] */
SET @ToDate = [%1]

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.LineTotal ) as 'Basic' , T1.[VatSum]as 'Total Tax',
( T1.LineTotal +T1.[VatSum]) as 'INCLUDING TAX',  T0.[CardCode] as 'Customer Code',T0.[CardName] as 'Customer Name' , 
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=-90 and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum )BED,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=-60 and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum)Cess,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=8 and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum)HCess,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=10 and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum )SAD,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=4 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)CST,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=1 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)VAT,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=8 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)SURCHARGE,
Discsum, T0.[Comments] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry --INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode inner join INV4 T3 on T1.Docentry =T3.docentry and T1.LineNum=T3.LineNum inner join CRD7 T4 on T0.cardcode = T4.Cardcode

Thanks,

Joseph

Answers (3)

Answers (3)

former_member296295
Participant
0 Kudos

Helpful

former_member1269712
Active Contributor
0 Kudos

Hi Kalli,

Use OINV - Header details, INV1- Line details, INV2 - Row wise Freight details,INV3 Document Wise Freight details, INV4 - Tax Amount per Document and INV5 - Withholding Tax

Thanks

Sachin

Former Member
0 Kudos

Hi.......

You can pick up your data from OINV (Header), INV1(Rows), INV3 (Freight/Exp), INV4 (Taxes).....

Hope this is sufficient to gather information.....

Regards,

Rahul