Add Items Group Name in this report

69 Views

SELECT DISTINCT T0.DocEntry,t0.docnum as 'Invoice No', --T0.[U_CustomersGRNNo] ,T0.[U_GRDate],

(SELECT Top 1 X.[County] FROM CRD1 X Where T0.CardCode = X.CardCode)as'County',

T0.[DocDate],T0.[CANCELED], T0.[TaxDate], T0.[CardName],

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

(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.[TargetType], T1.[Dscription],T1.[Quantity],T1.[Price], T1.[DiscPrcnt],T0.[DocTotal],-- A.[U_ProductGroup], A.[U_SubGroup],

t2.chapterid,

--(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.linenum=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.linenum>=1 then 0 end) as 'Freight Amt',

(case when t1.linenum=0 then

(select sum( t.taxsum) from inv4 t where t.expnscode in (1,2,3,4) and t.docentry=t0.docentry)

when t1.linenum>=1 then 0 end) as 'Freight Tax Amt',

(case when t1.linenum=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.linenum>=1 then '' end) as 'Freight Tax Rate',

T1.[LineTotal]

FROM dbo.OINV T0

left JOIN dbo.INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

left JOIN dbo.OITM a ON T1.[ItemCode] = a.[ItemCode]

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]

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] and T0.Canceled = 'N' AND T0.GSTTranTyp NOT IN('GD') And T1.[TargetType] Not In ('14')