SELECT T1.OcrCode5,T1.OcrCode3,T1.OcrCode2,T1.OcrCode4,T1.ocrcode, NNM1.SeriesName,T0.CardCode + '-' + T0.CardName AS Customer,
T0.DocNum, convert(nvarchar,T0.DocDate,103) AS [Invoice Date], convert(nvarchar,T0.DocDueDate,103) AS [Invoice Due Date],
(SELECT CASE T0.[DocStatus] WHEN 'O' THEN 'Open' WHEN 'C' THEN 'Closed' END AS Expr1) AS [Document Status], T0.NumAtCard, 
T0.U_CourierNo, T0.U_CourierName, T0.DocTotal, T0.VatSum, T0.DocTotal - T0.VatSum AS Net, OSLP.SlpName,T1.LineNum, T1.Dscription, 
T1.Quantity, T0.U_Warranty, T1.Price, (select itmsgrpnam from oitb where oitm.itmsgrpcod = itmsgrpcod) as GroupCode, T0.comments, T0.address2, T1.u_amcperiod,
(select pymntgroup from octg where groupnum = T0.Groupnum) as PymntTerms,
(select case 
when u_spcust = 'Z' then 'Hotel'
when u_spcust = 'I' then 'IT'
when u_spcust = 'E' then 'Central Govt'
when u_spcust = 'G' then 'State Govt'
when u_spcust = 'O' then 'Other'
when u_spcust = 'L' then 'Logistics'
when u_spcust = 'M' then 'MFG'
when u_spcust = 'R' then 'Retail'
when u_spcust = 'T' then 'Telcos'
when u_spcust = 'H' then 'Hospital'
when u_spcust = 'C' then 'Channel'
when u_spcust = 'S' then 'SI'
when u_spcust = 'B' then 'BFSI'
when u_spcust = 'Y' then 'Yes'
when u_spcust = 'N' then 'No' end from ocrd where cardcode = T0.cardcode) as CustType, T1.linetotal, T1.vatsum, (T1.linetotal + T1.vatsum) as LinTotWithTax
FROM OINV AS T0 INNER JOIN
INV1 AS T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN
OSLP ON T0.SlpCode = OSLP.SlpCode INNER JOIN
NNM1 ON T0.Series = NNM1.Series INNER JOIN
OITM ON T1.ItemCode = OITM.ItemCode
WHERE NNM1.SeriesName <> 'OB' 
AND (T0.DocDate >= @FromDate) AND (T0.DocDate <= @ToDate) 
AND (T0.DocStatus IN (@Doc_Status)) 
AND (T1.ocrcode in (@Principle) or T1.ocrcode is null or T1.ocrcode = '' )  
AND (T1.OcrCode5 IN (@Branch) or T1.OcrCode5 is null or T1.OcrCode5 = '') 
AND (T1.OcrCode3 IN (@SBU_Head) or T1.OcrCode3 is null or T1.OcrCode3 = '') 
AND (T1.OcrCode2 IN (@Division) or T1.OcrCode2 is null or T1.OcrCode2 = '') 
AND (T1.OcrCode4 IN (@Channel) or T1.OcrCode4 is null or T1.OcrCode4 = '') 
AND (T0.CardName IN (@Custmer)) 
AND (OSLP.SlpName IN (@Sales_Employee)) 
AND nnm1.SeriesName IN (@Series_Name) 
AND OITM.Itmsgrpcod in (@itemgroup) 
AND T1.itemcode in (@Item)

GROUP BY T1.OcrCode5, T1.OcrCode3, T1.OcrCode2, T1.OcrCode4, T0.CardCode,T0.CardName, T0.DocNum, 
T0.DocDate, T0.DocDueDate, T0.DocStatus, T0.NumAtCard, T0.U_CourierNo, T0.U_CourierName, T0.DocTotal, T0.VatSum, 
T0.DocTotal - T0.VatSum, nnm1.Series,NNM1.SeriesName, OSLP.SlpName, T1.Dscription, T1.Quantity, T0.U_Warranty, T1.LineNum, T1.ItemCode, 
T0.DocEntry, T1.Price, oitm.itmsgrpcod, T0.comments, T1.ocrcode, T0.cardcode, T0.address2, T1.u_amcperiod, T0.groupnum, T1.linetotal, T1.vatsum

union all

SELECT T1.OcrCode5,T1.OcrCode3,T1.OcrCode2, T1.OcrCode4,T1.ocrcode, NNM1.SeriesName,
T0.CardCode + '  - ' + T0.CardName AS Customer, T0.DocNum, convert(nvarchar,T0.DocDate,103) AS [Invoice Date], 
convert(nvarchar,T0.DocDueDate,103) AS [Invoice Due Date],
(SELECT     CASE T0.[DocStatus] WHEN 'O' THEN 'Open' WHEN 'C' THEN 'Closed' END AS Expr1) AS [Document Status], 
T0.NumAtCard, T0.U_CourierNo, T0.U_CourierName, - T0.DocTotal AS DocTotal, - T0.VatSum AS VatSum, 
- (T0.DocTotal - T0.VatSum) AS Net, oslp.slpname, T1.LineNum,T1.dscription, T1.quantity, 
'NA' as U_Warranty, 0 as Price, 
(select itmsgrpnam from oitb where oitm.itmsgrpcod = itmsgrpcod) as GroupCode, 
T0.comments, T0.address2, 
T1.u_amcperiod,
(select pymntgroup from octg where groupnum = T0.Groupnum) as PymntTerms,
(select case 
when u_spcust = 'Z' then 'Hotel'
when u_spcust = 'I' then 'IT'
when u_spcust = 'E' then 'Central Govt'
when u_spcust = 'G' then 'State Govt'
when u_spcust = 'O' then 'Other'
when u_spcust = 'L' then 'Logistics'
when u_spcust = 'M' then 'MFG'
when u_spcust = 'R' then 'Retail'
when u_spcust = 'T' then 'Telcos'
when u_spcust = 'H' then 'Hospital'
when u_spcust = 'C' then 'Channel'
when u_spcust = 'S' then 'SI'
when u_spcust = 'B' then 'BFSI'
when u_spcust = 'Y' then 'Yes'
when u_spcust = 'N' then 'No' end from ocrd where cardcode = T0.cardcode) as CustType,
-T1.linetotal as linetotal, -T1.vatsum, - (T1.linetotal + T1.vatsum) as LinTotWithTax
FROM ORIN AS T0 
INNER JOIN RIN1 AS T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP ON T0.SlpCode = OSLP.SlpCode 
INNER JOIN NNM1 ON T0.Series = NNM1.Series 
LEFT JOIN OINV ON T0.U_SaleInvNo = OINV.DocNum and T0.U_SalesInvDt=OINV.DocDate 

LEFT JOIN OITM ON T1.ItemCode = OITM.ItemCode
WHERE NNM1.SeriesName <> 'OB' AND ( t1.acctcode>=410000 and t1.acctcode<=413999)
AND (T0.DocDate >= @FromDate) AND (T0.DocDate <= @ToDate)
AND (T0.DocStatus IN (@Doc_Status)) 
AND (T1.ocrcode in (@Principle) or T1.ocrcode is null or T1.ocrcode = '' )  
and (T1.OcrCode5 IN (@Branch) or T1.OcrCode5 is null or T1.OcrCode5 = '') 
AND (T1.OcrCode3 IN (@SBU_Head) or T1.OcrCode3 is null or T1.OcrCode3 = '') 
AND (T1.OcrCode2 IN (@Division) or T1.OcrCode2 is null or T1.OcrCode2 = '') 
AND (T1.OcrCode4 IN (@Channel) or T1.OcrCode4 is null or T1.OcrCode4 = '') 
AND (T0.CardName IN (@Custmer)) 
AND  (OSLP.SlpName IN (@Sales_Employee) or OSLP.SlpName is null) 
AND (OITM.Itmsgrpcod in (@itemgroup) or OITM.Itmsgrpcod is null)
AND (T1.itemcode in (@Item) or t1.itemcode is null)

AND T0.U_SaleInvNo IN 
(SELECT T2.DOCNUM FROM OINV T2 INNER JOIN NNM1 T3 ON T2.Series = T3.Series 
INNER JOIN ORIN T4 ON T4.U_SaleInvNo =T2.DOCNUM AND T4.U_SalesInvDt=T2.DocDate 
WHERE T3.SERIESNAME IN (@Series_Name))

GROUP BY T1.OcrCode5, T1.OcrCode3, T1.OcrCode2, T1.OcrCode4, T0.CardCode,T0.CardName, T0.DocNum, 
T0.DocDate,T0.DocDueDate, T0.DocStatus, T0.NumAtCard, 
T0.U_CourierNo, T0.U_CourierName, T0.DocTotal, T0.VatSum, T0.DocTotal - T0.VatSum, nnm1.Series,
NNM1.SeriesName, oslp.slpname, T1.dscription, T1.quantity, t0.u_warranty, oitm.itmsgrpcod, T0.comments, 
T1.ocrcode, T0.cardcode, T0.address2, T1.u_amcperiod, T0.groupnum, T1.baseentry, T1.linetotal, T1.vatsum,
T1.LineNum


order by 1,2,3,6,7,8