Report Showing duplicate invoice in single item invoice

[anil_goel]

SELECT T0.DocEntry,t0.docnum as 'Invoice No', --T0.[U_CustomersGRNNo],T0.[U_GRDate], M.[BeginStr] as 'Prerflix',T0.[NumAtCard], (SELECT Top 1 X.[County] FROM CRD1 X Where T0.CardCode = X.CardCode)as'County', T0.[DocDate],T0.[CANCELED], T0.[TaxDate], T0.[CardName], a.[ItmsGrpCod], a.[U_ItemSubGroup], a.[SalUnitMsr], (case when t0.gsttrantyp='GA' then 'GST Tax Invoice' when t0.gsttrantyp='GD' then 'GST Debit Memo' else 'Bill Of Supply' end ) as 'Document Type', --(Select N.[U_BPSubGroup] From OCRD N Where T0.CardCode = N.CardCode)as'BP Sub Group', t3.bpgstn, t0.paytocode as 'Bill To', t0.shiptocode as 'Ship To', T1.[OcrCode], (Select top 1 T.[Location] from OLCT T where T.Code=t1.LocCode ) as 'Location', T3.[BpGSTN] as 'BP GSTIN', (SELECT Top 1 S.[Name] FROM CRD1 R INNER JOIN OCST S ON R.[State] = S.[Code] Where T0.CardCode = R.CardCode )as'State', T1.[ItemCode], T1.[Dscription],A.[FrgnName],T1.[Quantity],T1.[U_NoOfPkgs], T1.[U_DNo], T1.[U_QtyInKG], l.ItmsGrpNam, T1.[Weight1],T1.[LineTotal]-(T1.LineTotal*T0.[DiscPrcnt]/100) 'Basic Value', T1.[PriceBefDi],T1.[Price], T0.[RoundDif],T1.[DiscPrcnt],T0.[DocTotal],-- A.[U_ProductGroup], A.[U_SubGroup], t2.chapterid,---T8.[DistNumber],T8.[MnfDate], T8.[ExpDate], --(SELECT X.[U_GST] FROM [@UOM] X where X.[Name] = T1.[unitMsr])as'Unit1', T1.[PackQty], --(SELECT X.[U_GST] FROM [@UOM] X where X.[Name] = a.[SalPackMsr])as'Unit2',T1.[Quantity], t1.price, T1.[LineTotal]-(T1.LineTotal*T0.[DiscPrcnt]/100) 'Basic Value', (SELECT top 1 ISNULL(T7.stccode,0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-120 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum)'IGSTRate%', (SELECT ISNULL(sum(T7.TaxSUm),0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-120 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum and t7.expnscode=-1 )IGSTAmt, (SELECT top 1 ISNULL(T7.stccode,0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType= 8 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum)'TCSRate%', (SELECT ISNULL(sum(T7.TaxSUm),0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType= 8 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum and t7.expnscode=-1 )TCSAmt, (SELECT top 1 ISNULL(T7.stccode,0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-100 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum)'CGSTRate%', (SELECT ISNULL(sum(T7.TaxSUm),0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-100 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum and t7.expnscode=-1 )CGSTAmt, (SELECT top 1 ISNULL(T7.stccode,0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-110 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum)'SGSTRate%', (SELECT ISNULL(sum(T7.TaxSUm),0) FROM OINV T5 INNER JOIN INV1 T6 ON T5.DocEntry=T6.DocEntry INNER JOIN INV4 T7 ON T5.DocEntry=T7.DocEntry Where T7.StaType=-110 AND T6.LineNum=T1.LineNum AND T5.DocEntry=T0.DocEntry AND T6.ItemCode=T1.ItemCode AND T6.LineNum=T7.LineNum and t7.expnscode=-1 )SGSTAmt, (case when t1.VisOrder=0 then (select sum(distinct t.basesum) from inv4 t where t.expnscode in (1,2,3,4) and t.docentry=t0.docentry) when t1.VisOrder>=1 then 0 end) as 'Freight Amt', (case when t1.VisOrder=0 then (select sum( t.taxsum) from inv4 t where t.expnscode in (1,2,3,4) and t.docentry=t0.docentry) when t1.VisOrder>=1 then 0 end) as 'Freight Tax Amt', (case when t1.VisOrder=0 then (select top 1 t.stccode from inv4 t where t.expnscode in (1,2,3,4) and t.docentry=t0.docentry) when t1.VisOrder>=1 then '' end) as 'Freight Tax Rate', T1.[LineTotal],H.[SlpName],T0.[U_VehicleNo], T0.[U_Destination],T0.[U_Transporter],T0.[U_LRNo], T0.[U_LRDate], T0.[U_Ewaybill], T0.[U_TransporterGST] FROM dbo.OINV T0 left JOIN dbo.INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN NNM1 M ON T0.[Series] = M.[Series] left JOIN dbo.OITM a ON T1.[ItemCode] = a.[ItemCode] left Join dbo.OITB l on a.ItmsGrpCod = l.ItmsGrpCod left JOIN dbo.OCHP T2 ON a.[ChapterID] = T2.[AbsEntry] left join dbo.inv12 t3 on t0.docentry=t3.docentry INNER JOIN dbo.OLCT T4 ON T1.[LocCode] = T4.[Code] LEFT JOIN DLN1 T5 ON T1.BaseEntry = T5.DocEntry And T5.BaseLine = T1.LineNum LEFT JOIN ODLN T6 ON T5.DocEntry = T6.DocEntry INNER JOIN OSLP H ON T0.[SlpCode] = H.[SlpCode] left outer join IBT1 I1 on T1.ItemCode=I1.ItemCode and (T5.DocEntry=I1.BaseEntry and T5.ObjType=I1.BaseType) left outer join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber LEFT Join WTR1 T16 On T16.DocEntry=T1.BaseEntry and T16.LineNum=T1.BaseLine left outer join IBT1 I11 on T1.ItemCode=I11.ItemCode and (T16.DocEntry=I11.BaseEntry and T16.ObjType=I11.BaseType) left outer join OBTN T18 on T18.ItemCode=I11.ItemCode and I11.BatchNum=T18.DistNumber WHERE T4.[GSTRegnNo] =[%2] and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] and T0.Canceled = 'N' AND T0.GSTTranTyp NOT IN('GD') And T1.[TargetType] Not In ('14')