Help with SQL Query in SAP business One

[Former Member]

Hello Experts,

I need help with one of my SQL query, I am generating a sales register report in sap business one. I have got the query, but i am stuck at one thing in the output. I am getting duplicate values of my subquery. the output is attached with the message.

Here, what I want is to have only single value for the fields.

the query is as given below:

Select Distinct 'Sales Invoice' as 'Invoice Type',T0.DocEntry,T0.DOCNUM  AS 'INVOICE NO',T4.SeriesName AS 'SERIES', 
T0.DocType as 'Invoice Type', CONVERT(VARCHAR,T0.DOCDATE,103) AS 'INVOICE DATE',
T0.NumAtCard AS 'VENDOR REF NO',T0.CARDNAME AS 'VENDOR NAME', T11.Name AS 'STATE BILL TO',
T9.Location as 'Location',T6.TAXID1 AS 'CST NO',T6.TAXID2 AS 'VAT NO', T6.BpGSTN as 'GST No',
(Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry) AS 'BASIC VALUE',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =4)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =4)as 'Excise-Baroda',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =5)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =5)as 'Education Cess-Baroda',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =6)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =6)as 'Higher Edu. Cess-Baroda',
(Select Case when ((Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))=0 then (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)
else (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)+((Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))end) as 'Assessable Value',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('BVT4','DVT4','VAT4')And DOCENTRY=T0.DOCENTRY )AS 'VAT 4% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('MVT5')  And DOCENTRY=T0.DOCENTRY)AS 'VAT 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('BVT12.5','DVT12.5','MVT12.5') And DOCENTRY=T0.DOCENTRY  )AS 'VAT 12.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='8' And STACODE in ('BAVAT1')  And DOCENTRY=T0.DOCENTRY  )AS 'ADD. VAT 1% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='8' And STACODE in ('BAVAT2.5')  And DOCENTRY=T0.DOCENTRY  )AS 'ADD. VAT 2.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT2', 'DCT2','MCT2')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 2% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT5', 'DCT5','MCT5')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT15', 'DCT15','MCT15')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 15% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='7' And STACODE in ('BExempt', 'DExempt','MExempt')And DOCENTRY=T0.DOCENTRY  ) AS 'EXEMPT VALUE',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Freight-Baroda',
(Select ISNULL(Sum(LineVat),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(LineVat),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Freight-Baroda Tax',
(Select ISNULL(Sum(VatPrcnt),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(VatPrcnt),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Rate',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =2)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =2)as 'Freight-Mumbai',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =3)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =3)as 'Freight-Delhi',
T0.DOCTOTAL AS 'TOTAL AMOUNT' ,

(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGSTRate,

(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGSTRate,

(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGSTRate
From OINV T0 INNER JOIN INV1 T1 ON T0.DOCENTRY=T1.DOCENTRY
LEFT OUTER JOIN NNM1 T4 ON T4.SERIES=T0.SERIES
LEFT OUTER JOIN INV12 T6 ON T6.DOCENTRY=T0.DOCENTRY
LEFT OUTER JOIN CRD1 t10 ON t0.CardCode=t10.CardCode And T10.AdresType='B' And T10.Address=T0.Address
LEFT OUTER JOIN OCST  T8 ON T8.Code  = T10.State  And T8.Country =T10.Country  
LEFT OUTER JOIN OLCT  T9 ON T9.Code  = (Select TOP 1 LocCode From INV1 Where DocEntry=T0.DocEntry)  
Left Outer Join OCST T11 on T6.StateB=T11.Code and T6.CountryB=T11.Country
Where T0.[DOCDATE] >= [%0] And T0.[DOCDATE] <=[%1]  And 
T0.DOCTYPE = 'I' 
And T9.Location ='[%2]' 
AND T0.Canceled = 'N'
Group by T0.DOCNUM,T0.DocType,T0.DOCDATE,T0.NUMATCARD,T0.CARDNAME,T6.STATEB,T6.TAXID1,T6 .TaxId2, T6.BpGSTN, T0.DocEntry,T0.DocTotal,T11.Name,T4.SeriesName,T9.location,T1.LineNum

Union All

Select Distinct'Sales Return' as 'Invoice Type',T0.DocEntry,T0.DOCNUM  AS 'INVOICE NO',
T4.SeriesName  AS 'SERIES', T0.DocType as 'Invoice Type', CONVERT(VARCHAR,T0.DOCDATE,103) AS 'INVOICE DATE',
T0.NumAtCard AS 'VENDOR REF NO',T0.CARDNAME AS 'VENDOR NAME',T11.Name AS 'STATE BILL TO',
T9.Location as 'Location',T6.TAXID1 AS 'CST NO',T6.TAXID2 AS 'VAT NO',T6.BpGSTN as 'GST No',
(Select SUM(LINETOTAL)*(-1) From RIN1 Where DocEntry=T0.DocEntry) AS 'BASIC VALUE',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =4)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =4)as 'Excise-Baroda',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =5)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =5)as 'Education Cess-Baroda',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =6)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =6)as 'Higher Edu. Cess-Baroda',
(Select Case when ((Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0)From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))=0 
then (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)
else (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)+((Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))end)*(-1) as 'Assessable Value',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('BVT4','DVT4','VAT4') And DOCENTRY=T0.DOCENTRY)AS 'VAT 4% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('MVT5') And DOCENTRY=T0.DOCENTRY)AS 'VAT 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('BVT12.5','DVT12.5','MVT12.5')  And DOCENTRY=T0.DOCENTRY)AS 'VAT 12.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='8' And STACODE in ('BAVAT1')  And DOCENTRY=T0.DOCENTRY)AS 'ADD. VAT 1% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='8' And STACODE in ('BAVAT2.5') And DOCENTRY=T0.DOCENTRY)AS 'ADD. VAT 2.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT2', 'DCT2','MCT2')  And DOCENTRY=T0.DOCENTRY)AS 'CST 2% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT5', 'DCT5','MCT5')  And DOCENTRY=T0.DOCENTRY)AS 'CST 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT15', 'DCT15','MCT15')And DOCENTRY=T0.DOCENTRY)AS 'CST 15% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='7' And STACODE in ('BExempt', 'DExempt','MExempt')And DOCENTRY=T0.DOCENTRY) AS 'EXEMPT VALUE',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =1)*(-1)as 'Freight-Baroda',
(Select ISNULL(Sum(LineVat),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(LineVat),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)*(-1)as 'Freight-Baroda Tax',
(Select ISNULL(Sum(VatPrcnt),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(VatPrcnt),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Rate',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =2)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =2)*(-1)as 'Freight-Mumbai',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =3)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =3)*(-1)as 'Freight-Delhi',
T0.DOCTOTAL *(-1) AS 'TOTAL AMOUNT' ,

(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 and S4.LineNum=T1.LineNum) as IGSTRate,

(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 and S4.LineNum=T1.LineNum) as CGSTRate,

(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 and S4.LineNum=T1.LineNum) as SGSTRate
From ORIN T0 INNER JOIN RIN1 T1 ON T0.DOCENTRY=T1.DOCENTRY
LEFT OUTER JOIN NNM1 T4 ON T4.SERIES=T0.SERIES
LEFT OUTER JOIN RIN12 T6 ON T6.DOCENTRY=T0.DOCENTRY
LEFT OUTER JOIN CRD1 t10 ON t0.CardCode=t10.CardCode And T10.AdresType='B' And T10.Address=T0.Address 
LEFT OUTER JOIN OCST  T8 ON T8.Code  = T10.State  And T8.Country =T10.Country  
LEFT OUTER JOIN OLCT  T9 ON T9.Code  = (Select TOP 1 LocCode From INV1 Where DocEntry=T0.DocEntry)   
Left Outer Join OCST T11 on T6.StateB=T11.Code and T6.CountryB=T11.Country
Where  T0.[DOCDATE] >= [%0] And T0.[DOCDATE] <=[%1]  And 
T0.DOCTYPE = 'I' 
And T9.Location ='[%2]'
AND T0.Canceled = 'N'
Group by  T0.DOCNUM  ,T0.DocType, T0.DOCDATE,T0.NUMATCARD,T0.CARDNAME,T6.STATEB,T6.TAXID1,T6 .TaxId2, T6.BpGSTN, T0.DocEntry,T0.DocTotal ,T11.Name, 
T4.SeriesName,T9.location,T1.LineNum

[anand_singh]

Hi Ravi,

Becoz you have join INV1 in "sale Invoice" & RIN1 in "Sale Return". That's why its reflect as much as times numbers of items are in AR invoice & Ar Credit Memo. So Please remove that join, then you will get values in single row.

Query is as per below:-

Select Distinct 'Sales Invoice' as 'Invoice Type',T0.DocEntry,T0.DOCNUM  AS 'INVOICE NO',T4.SeriesName AS 'SERIES', 
T0.DocType as 'Invoice Type', CONVERT(VARCHAR,T0.DOCDATE,103) AS 'INVOICE DATE',
T0.NumAtCard AS 'VENDOR REF NO',T0.CARDNAME AS 'VENDOR NAME', T11.Name AS 'STATE BILL TO',
T9.Location as 'Location',T6.TAXID1 AS 'CST NO',T6.TAXID2 AS 'VAT NO', T6.BpGSTN as 'GST No',
(Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry) AS 'BASIC VALUE',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =4)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =4)as 'Excise-Baroda',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =5)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =5)as 'Education Cess-Baroda',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =6)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =6)as 'Higher Edu. Cess-Baroda',
(Select Case when ((Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))=0 then (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)
else (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)+((Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))end) as 'Assessable Value',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('BVT4','DVT4','VAT4')And DOCENTRY=T0.DOCENTRY )AS 'VAT 4% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('MVT5')  And DOCENTRY=T0.DOCENTRY)AS 'VAT 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='1' And STACODE in ('BVT12.5','DVT12.5','MVT12.5') And DOCENTRY=T0.DOCENTRY  )AS 'VAT 12.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='8' And STACODE in ('BAVAT1')  And DOCENTRY=T0.DOCENTRY  )AS 'ADD. VAT 1% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='8' And STACODE in ('BAVAT2.5')  And DOCENTRY=T0.DOCENTRY  )AS 'ADD. VAT 2.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT2', 'DCT2','MCT2')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 2% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT5', 'DCT5','MCT5')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='4' And STACODE in ('BCT15', 'DCT15','MCT15')  And DOCENTRY=T0.DOCENTRY  )AS 'CST 15% VALUE',
(Select ISNULL(SUM(TAXSUM),0) From INV4 Where STATYPE='7' And STACODE in ('BExempt', 'DExempt','MExempt')And DOCENTRY=T0.DOCENTRY  ) AS 'EXEMPT VALUE',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Freight-Baroda',
(Select ISNULL(Sum(LineVat),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(LineVat),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Freight-Baroda Tax',
(Select ISNULL(Sum(VatPrcnt),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(VatPrcnt),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Rate',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =2)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =2)as 'Freight-Mumbai',
(Select ISNULL(Sum(linetotal),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =3)+
(Select ISNULL(Sum(linetotal),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =3)as 'Freight-Delhi',
T0.DOCTOTAL AS 'TOTAL AMOUNT' ,
(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGSTRate,
(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGSTRate,
(select sum(isnull(S4.TaxSum,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGST,
(select sum(isnull(S4.TaxSumFrgn,0)) from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGSTFrgn,
(select top 1 S4.TaxRate from INV4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGSTRate
From OINV T0
LEFT OUTER JOIN NNM1 T4 ON T4.SERIES=T0.SERIES
LEFT OUTER JOIN INV12 T6 ON T6.DOCENTRY=T0.DOCENTRY
LEFT OUTER JOIN CRD1 t10 ON t0.CardCode=t10.CardCode And T10.AdresType='B' And T10.Address=T0.Address
LEFT OUTER JOIN OCST  T8 ON T8.Code  = T10.State  And T8.Country =T10.Country 
LEFT OUTER JOIN OLCT  T9 ON T9.Code  = (Select TOP 1 LocCode From INV1 Where DocEntry=T0.DocEntry) 
Left Outer Join OCST T11 on T6.StateB=T11.Code and T6.CountryB=T11.Country
Where T0.[DOCDATE] >= [%0] And T0.[DOCDATE] <=[%1]  And 
T0.DOCTYPE = 'I' 
And T9.Location ='[%2]' 
AND T0.Canceled = 'N'
Group by T0.DOCNUM,T0.DocType,T0.DOCDATE,T0.NUMATCARD,T0.CARDNAME,T6.STATEB,T6.TAXID1,T6 .TaxId2, T6.BpGSTN, T0.DocEntry,T0.DocTotal,T11.Name,T4.SeriesName,T9.location
Union All
Select Distinct'Sales Return' as 'Invoice Type',T0.DocEntry,T0.DOCNUM  AS 'INVOICE NO',
T4.SeriesName  AS 'SERIES', T0.DocType as 'Invoice Type', CONVERT(VARCHAR,T0.DOCDATE,103) AS 'INVOICE DATE',
T0.NumAtCard AS 'VENDOR REF NO',T0.CARDNAME AS 'VENDOR NAME',T11.Name AS 'STATE BILL TO',
T9.Location as 'Location',T6.TAXID1 AS 'CST NO',T6.TAXID2 AS 'VAT NO',T6.BpGSTN as 'GST No',
(Select SUM(LINETOTAL)*(-1) From RIN1 Where DocEntry=T0.DocEntry) AS 'BASIC VALUE',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =4)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =4)as 'Excise-Baroda',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =5)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =5)as 'Education Cess-Baroda',
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =6)+
(Select ISNULL(Sum(linetotal),0)*(-1) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =6)as 'Higher Edu. Cess-Baroda',
(Select Case when ((Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0)From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))=0 
then (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)
else (Select SUM(LINETOTAL) From INV1 Where DocEntry=T0.DocEntry)+((Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6))+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode in (4,5,6)))end)*(-1) as 'Assessable Value',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('BVT4','DVT4','VAT4') And DOCENTRY=T0.DOCENTRY)AS 'VAT 4% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('MVT5') And DOCENTRY=T0.DOCENTRY)AS 'VAT 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='1' And STACODE in ('BVT12.5','DVT12.5','MVT12.5')  And DOCENTRY=T0.DOCENTRY)AS 'VAT 12.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='8' And STACODE in ('BAVAT1')  And DOCENTRY=T0.DOCENTRY)AS 'ADD. VAT 1% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='8' And STACODE in ('BAVAT2.5') And DOCENTRY=T0.DOCENTRY)AS 'ADD. VAT 2.5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT2', 'DCT2','MCT2')  And DOCENTRY=T0.DOCENTRY)AS 'CST 2% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT5', 'DCT5','MCT5')  And DOCENTRY=T0.DOCENTRY)AS 'CST 5% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='4' And STACODE in ('BCT15', 'DCT15','MCT15')And DOCENTRY=T0.DOCENTRY)AS 'CST 15% VALUE',
(Select ISNULL(SUM(TAXSUM),0)*(-1) From RIN4 Where STATYPE='7' And STACODE in ('BExempt', 'DExempt','MExempt')And DOCENTRY=T0.DOCENTRY) AS 'EXEMPT VALUE',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =1)*(-1)as 'Freight-Baroda',
(Select ISNULL(Sum(LineVat),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(LineVat),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)*(-1)as 'Freight-Baroda Tax',
(Select ISNULL(Sum(VatPrcnt),0) From INV2 Where DocEntry=T0.DocEntry And ExpnsCode =1)+
(Select ISNULL(Sum(VatPrcnt),0) From INV3 Where DocEntry=T0.DocEntry And ExpnsCode =1)as 'Rate',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =2)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =2)*(-1)as 'Freight-Mumbai',
(Select ISNULL(Sum(linetotal),0) From RIN2 Where DocEntry=T0.DocEntry And ExpnsCode =3)+
(Select ISNULL(Sum(linetotal),0) From RIN3 Where DocEntry=T0.DocEntry And ExpnsCode =3)*(-1)as 'Freight-Delhi',
T0.DOCTOTAL *(-1) AS 'TOTAL AMOUNT' ,
(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-120 ) as IGSTRate,
(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-100 ) as CGSTRate,
(select sum(isnull(S4.TaxSum,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGST,
(select sum(isnull(S4.TaxSumFrgn,0))*(-1) from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGSTFrgn,
(select top 1 S4.TaxRate from RIN4 S4 where S4.DocEntry=T0.DocEntry and S4.RelateType=1 and S4.staType=-110 ) as SGSTRate
From ORIN T0 
LEFT OUTER JOIN NNM1 T4 ON T4.SERIES=T0.SERIES
LEFT OUTER JOIN RIN12 T6 ON T6.DOCENTRY=T0.DOCENTRY
LEFT OUTER JOIN CRD1 t10 ON t0.CardCode=t10.CardCode And T10.AdresType='B' And T10.Address=T0.Address 
LEFT OUTER JOIN OCST  T8 ON T8.Code  = T10.State  And T8.Country =T10.Country 
LEFT OUTER JOIN OLCT  T9 ON T9.Code  = (Select TOP 1 LocCode From INV1 Where DocEntry=T0.DocEntry)   
Left Outer Join OCST T11 on T6.StateB=T11.Code and T6.CountryB=T11.Country
Where  T0.[DOCDATE] >= [%0] And T0.[DOCDATE] <=[%1]  And 
T0.DOCTYPE = 'I' 
And T9.Location ='[%2]'
AND T0.Canceled = 'N'
Group by  T0.DOCNUM  ,T0.DocType, T0.DOCDATE,T0.NUMATCARD,T0.CARDNAME,T6.STATEB,T6.TAXID1,T6 .TaxId2, T6.BpGSTN, T0.DocEntry,T0.DocTotal ,T11.Name, 
T4.SeriesName,T9.location

I think above query will solve your issue.

Regards,

Anand Singh

[Former Member]

Hello Anand,

the Report here is to show the details of all the line items in the sales document, as there can also be different type of tax applied, so the it is required.

the output which i have given is proper, what i need is to remove the duplicate of values that i am getting in the assesable values, tax amount , frieght amount and all.

let me know how to eliminate that..

thank you for your help.

regards,

Ravi.

[Johan_H]

Hi Ravi,

From the screenshot you can see that the IGST subquery is returning different values for the double rows.

This subquery is the same above and below UNION ALL, and the WHERE clause is also the same above and below UNION ALL. Why is that?

Regards,

Johan

[Johan_H]

Hi Ravi,

From the screenshot you can see that the IGST subquery is returning different values for the double rows.

This subquery is the same above and below UNION ALL, and the WHERE clause is also the same above and below UNION ALL. Why is that?

Regards,

Johan