Invoice With Serial Number , Part No , ItemGroup

Select
 P2.docentry , P2.docnum as [InvNo.],
 P1.DocDate,
 P2.cardcode ,P2.cardname as 'CustomerName' ,
 P1.U_part_no as 'PartNo',
 P1.Itemcode, P1.Dscription,
 P1.price as 'UnitPrice' ,
 P1.Quantity ,
 P1.linetotal as 'TotalSP' ,
 case when P2.Doctype = 'I' then 'Invoice' End 'Doctype' ,
 (select T6.FirmName  from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
 T5.itmsgrpnam as 'ItemGroup',
 Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15  and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=13  and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
  from  OINV P2
INNER JOIN INV1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')

union all

 Select
 P2.docentry , P2.Docnum as [InvNo.],
   P1.DocDate,
 P2.cardcode ,P2.cardname as 'CustomerName' ,
 --Cast(P1.DocDate as Varchar(25)) [Docdate],
 P1.U_part_no as 'PartNo',
 P1.Itemcode, P1.Dscription ,
 -P1.price as 'UnitPrice' ,
 -P1.Quantity ,
 -P1.linetotal as 'TotalSP' ,
 case when P2.Doctype = 'I' then 'Creditmemo' End 'Doctype' ,
 (select T6.FirmName  from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
 t5.itmsgrpnam as 'ItemGroup',
 Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15  and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=14  and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
 from  ORIN P2
INNER JOIN RIN1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')
order by P2.docnum