Sales Tax Report

DECLARE
@StartDate DATETIME,
 @EndDate DATETIME,
 @Dummy INTEGER
SELECT TOP 1 @Dummy = DocNum
 FROM OINV T0

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'INVOICE'
when T0.DocType = 'S' then 'INVOICE'
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as [Customer Code],
T0.CardName AS [Customer Name] ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '420' then 'Karnataka-BLR'
When t0.series = '423' then 'Tamilnadu-CHN'
When t0.series = '424' then 'Maharasthra-PUN'
When t0.series = '425' then 'Maharasthra-MUM'
When t0.series = '574' then 'Karnataka-BLR'
When t0.series = '575' then 'Tamilnadu-CHN'
When t0.series = '576' then 'Maharasthra-PUN'
When t0.series = '577'  then 'Maharasthra-MUM'  end 'StateName',

T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and t4.U_sertype = 'ITSS' and INV4.StcCode in (  'S14V5M' , 'C5+ST14M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and INV4.StcCode in (  'S14V5M' , 'C5+ST14M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and t4.U_sertype = 'ITSS' and INV4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and INV4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE INV4.StaType = '1' and t4.U_sertype = 'ITSS' and INV4.StcCode in  ( 'S12V5+SM' ,  'S12V5M' ,   'S14V5M'  )
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5')  and INV4.StcCode = 'S14BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and  INV4.StcCode = 'S14BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5')  and INV4.StcCode = 'S12BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and  INV4.StcCode = 'S12BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and t4.U_sertype = 'MRS' and INV4.StcCode IN ('S14MRSM', 'V75SM')
AND INV4.DocEntry = T0.DocEntry) AS 'MRS@Ser14BasAmt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and t4.U_sertype = 'MRS' and INV4.StcCode IN ('S14MRSM' , 'V75SM')
AND INV4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and t4.U_sertype = 'MRS' and INV4.StcCode IN ('S12MRSM', 'V75S100M')
AND INV4.DocEntry = T0.DocEntry) AS 'MRS@Ser12BasAmt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and t4.U_sertype = 'MRS' and INV4.StcCode IN ('S12MRSM' , 'V75S100M')
AND INV4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5', '4' ) and INV4.StcCode IN ('STEXEMPM' ,  'ST14EXMM' )
AND INV4.DocEntry = T0.DocEntry) AS 'SEZ',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')  
and INV4.StcCode = 'CST2MUM'
AND INV4.DocEntry = T0.DocEntry) AS 'CST2Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
 and INV4.StcCode = 'CST2MUM'
AND INV4.DocEntry = T0.DocEntry) AS 'CST2Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('1') and  INV4.StcCode IN ('VAT5MUM'  )
AND INV4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('1') and  INV4.StcCode IN ('VAT5MUM', 'V75S100M' , 'V75SM'  )
AND INV4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')  
and INV4.StcCode = 'CST5MUM'
AND INV4.DocEntry = T0.DocEntry) AS 'CST5Basamt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
 and INV4.StcCode in ( 'CST5MUM' , 'C5+ST12M' , 'C5+ST14M'  )
AND INV4.DocEntry = T0.DocEntry) AS 'CST5Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')  
and INV4.StcCode = 'CST12.5M'
AND INV4.DocEntry = T0.DocEntry) AS 'CST12.5Basamt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
 and INV4.StcCode = 'CST12.5M'
AND INV4.DocEntry = T0.DocEntry) AS 'CST12.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE  INV4.StcCode = 'VAT12.5M' and INV4.RelateType in (1,3)
AND INV4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE INV4.StaType = '1' and INV4.StcCode = 'VAT12.5M'
AND INV4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM INV1
WHERE INV1.TAXCODE in ('NOTAXMUM') and INV1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',
(case when t0.taxonexp = 0 then t0.TotalExpns end)  'FreightAmt',
t0.RoundDifSy as 'Roundoff',
(t0.DocTotal)+(t0.WTSum) as 'Doctotal',
-t0.WTSum as WTax ,
t0.DocTotal as 'AfterWHDoctotal',
Case when t0.series = 425 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series = 577 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in  ('425' , '577' ) then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN INV12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode =  T3.U_TaxCode 
where
T0.DocDate >= [%3]
 AND T0.DocDate <= [%4] AND
 t3.U_State = 'MUM'

union all

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'CREDITMEMO'
when T0.DocType = 'S' then ' CREDITMEMO '
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as [Customer Code],
T0.CardName AS [Customer Name] ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '426' then 'Karnataka-BLR'
When t0.series = '427' then 'Tamilnadu-CHN'
When t0.series = '428' then 'Maharasthra-PUN'
When t0.series = '429' then 'Maharasthra-MUM'
 When t0.series = '559' then 'Karnataka-BLR'
When t0.series = '560' then 'Tamilnadu-CHN'
When t0.series = '561' then 'Maharasthra-PUN'
When t0.series = '562' then 'Maharasthra-MUM'
 end 'StateName',
T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in (  'S14V5M' , 'C5+ST14M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in  (  'S14V5M' , 'C5+ST14M')
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE RIN4.StaType = '1' and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' ,  'S12V5M' ,  'S14V5M'  )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5')  and RIN4.StcCode = 'S14BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and  RIN4.StcCode = 'S14BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5')  and RIN4.StcCode = 'S12BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and  RIN4.StcCode = 'S12BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and t4.U_sertype = 'MRS' and RIN4.StcCode IN ('S14MRSM', 'V75SM')
AND RIN4.DocEntry = T0.DocEntry) AS 'MRS@Ser14BasAmt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and t4.U_sertype = 'MRS' and RIN4.StcCode IN ('S14MRSM' , 'V75SM')
AND RIN4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and t4.U_sertype = 'MRS' and RIN4.StcCode IN ('S12MRSM', 'V75S100M')
AND RIN4.DocEntry = T0.DocEntry) AS 'MRS@Ser12BasAmt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and t4.U_sertype = 'MRS' and RIN4.StcCode IN ('S12MRSM' , 'V75S100M')
AND RIN4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5', '4' ) and RIN4.StcCode IN ('STEXEMPM' ,  'ST14EXMM'  )
AND RIN4.DocEntry = T0.DocEntry) AS 'SEZ',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')  
and RIN4.StcCode = 'CST2MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST2Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
 and RIN4.StcCode = 'CST2MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST2Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('1') and  RIN4.StcCode IN ('VAT5MUM'  )
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('1') and  RIN4.StcCode IN ('VAT5MUM', 'V75S100M' , 'V75SM' )
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')  
and RIN4.StcCode = 'CST5MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
 and RIN4.StcCode in ( 'CST5MUM' , 'C5+ST12M' ,  'C5+ST14M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'CST5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')  
and RIN4.StcCode = 'CST12.5M'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST12.5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
 and RIN4.StcCode = 'CST5MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST12.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE  RIN4.StcCode = 'VAT12.5M' and RIN4.RelateType in (1,3)
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE RIN4.StaType = '1' and RIN4.StcCode = 'VAT12.5M'
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RIN1
WHERE RIN1.TAXCODE in ('NOTAXMUM') and RIN1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

-(case when t0.taxonexp = 0 then t0.TotalExpns end)  'FreightAmt',
-t0.RoundDifSy as 'Roundoff',
-((t0.DocTotal)+(t0.WTSum)) as 'Doctotal',
t0.WTSum as WTax ,
-t0.DocTotal as 'AfterWHDoctotal',
Case when t0.series = 429 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series = 562 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in  ('429' , '562' ) then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN RIN12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode =  T3.U_TaxCode 
where
T0.DocDate >= [%5]
 AND T0.DocDate <= [%6] AND
 t3.U_State = 'MUM'

Purchase Tax Report

DECLARE
@StartDate DATETIME,
 @EndDate DATETIME,
 @Dummy INTEGER
SELECT TOP 1 @Dummy = DocNum
 FROM OPCH T0
 WHERE T0.DocDate >=[%1]
 AND T0.DocDate <= [%2]

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'INVOICE'
when T0.DocType = 'S' then 'INVOICE'
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as 'Vendor Code',
T0.CardName AS 'Vendor Name' ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '467' then 'Karnataka-BLR'
When t0.series = '468' then 'Tamilnadu-CHN'
When t0.series = '469' then 'Maharasthra-PUN'
When t0.series = '470' then 'Maharasthra-MUM' 
When t0.series = '625' then 'Karnataka-BLR'
When t0.series = '626' then 'Tamilnadu-CHN'
When t0.series = '627' then 'Maharasthra-PUN'
When t0.series = '628' then 'Maharasthra-MUM' 
end 'StateName',

T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5')  and PCH4.StcCode in  ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode in ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5')  and PCH4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5') and PCH4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StaType = '1'  and PCH4.StcCode in ('S12V5+SM' , 'S12V5M' , 'S14V5+SM' , 'S14V5M' )
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S14BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S14BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S12BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S12BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5')  and PCH4.StcCode IN ('S14MRSM' , 'V75SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser14BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5')  and PCH4.StcCode IN ('S14MRSM' , 'V75SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5')  and PCH4.StcCode IN ('S12MRSM', 'V75S100M')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser12BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5')  and PCH4.StcCode IN ('S12MRSM','V75S100M')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)  
and PCH4.StcCode = 'ICST2M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST2Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
 and PCH4.StcCode = 'ICST2M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST2Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE  PCH4.StcCode = 'VAT5MUM' and pch4.RelateType in (1,3)
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE  pch4.RelateType in (1,3) and PCH4.StcCode in ( 'VAT5MUM' , 'V75S100M' , 'V75SM'  )
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)  
and PCH4.StcCode = 'ICST5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
 and PCH4.StcCode = 'ICST5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5Taxamt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM PCH1
WHERE PCH1.TAXCODE in ('ICST5.5M') and PCH1.DocEntry = t0.DocEntry ) as 'ICST5.5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
 and PCH4.StcCode in ('IC5SV12' , 'ICST5.5M' , 'IC5SV14'  )
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE  PCH4.StcCode = 'VAT12.5M' and pch4.RelateType in (1,3)
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StaType = '1' and PCH4.StcCode = 'VAT12.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)  
and PCH4.StcCode = 'ICS14.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST14.5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
 and PCH4.StcCode = 'ICS14.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST14.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM PCH1
WHERE PCH1.TAXCODE in ('NOTAXMUM') and PCH1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

(case when t0.taxonexp = 0 then t0.TotalExpns end)  'FreightAmt',
t0.RoundDifSy as 'Roundoff',
(t0.DocTotal)+(t0.WTSum) as 'Doctotal',
-t0.WTSum as WTax ,
t0.DocTotal as 'AfterWHDoctotal',
Case when  t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in  ('470', '628') then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN PCH12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode =  T3.U_TaxCode 
where
T0.DocDate >= [%3]
 AND T0.DocDate <= [%4] AND
 t3.U_state = 'MUM'

union all

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'CREDITMEMO'
when T0.DocType = 'S' then ' CREDITMEMO '
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as 'Vendor Code',
T0.CardName AS 'Vendor Name' ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '478' then 'Karnataka-BLR'
When t0.series = '479' then 'Tamilnadu-CHN'
When t0.series = '480' then 'Maharasthra-PUN'
When t0.series = '481' then 'Maharasthra-MUM'
When t0.series = '629' then 'Karnataka-BLR'
When t0.series = '630' then 'Tamilnadu-CHN'
When t0.series = '631' then 'Maharasthra-PUN'
When t0.series = '632' then 'Maharasthra-MUM'
 end 'StateName',T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5')  and RPC4.StcCode in  ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and RPC4.StcCode in ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5')  and RPC4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5') and RPC4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StaType = '1'  and RPC4.StcCode in ('S12V5+SM' , 'S12V5M' , 'S14V5+SM' , 'S14V5M' )
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S14BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S14BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S12BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S12BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5')  and RPC4.StcCode IN ('S14MRSM' , 'V75SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser14BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5')  and RPC4.StcCode IN ('S14MRSM' , 'V75SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5')  and RPC4.StcCode IN ('S12MRSM', 'V75S100M')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser12BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5')  and RPC4.StcCode IN ('S12MRSM','V75S100M')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)  
and RPC4.StcCode = 'ICST2M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST2Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
 and RPC4.StcCode = 'ICST2M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST2Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE  RPC4.StcCode = 'VAT5MUM' and RPC4.RelateType in (1,3)
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE  RPC4.RelateType in (1,3) and RPC4.StcCode in ( 'VAT5MUM' , 'V75S100M' , 'V75SM'  )
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)  
and RPC4.StcCode = 'ICST5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
 and RPC4.StcCode = 'ICST5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RPC1
WHERE RPC1.TAXCODE in ('ICST5.5M') and RPC1.DocEntry = t0.DocEntry ) as 'ICST5.5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
 and RPC4.StcCode in ('IC5SV12' , 'ICST5.5M' , 'IC5SV14'  )
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE  RPC4.StcCode = 'VAT12.5M' and RPC4.RelateType in (1,3)
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StaType = '1' and RPC4.StcCode = 'VAT12.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)  
and RPC4.StcCode = 'ICS14.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST14.5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
 and RPC4.StcCode = 'ICS14.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST14.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RPC1
WHERE RPC1.TAXCODE in ('NOTAXMUM') and RPC1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

-(case when t0.taxonexp = 0 then t0.TotalExpns end)  'FreightAmt',
-t0.RoundDifSy as 'Roundoff',
-((t0.DocTotal)+(t0.WTSum)) as 'Doctotal',
t0.WTSum as WTax ,
-t0.DocTotal as 'AfterWHDoctotal',
Case when  t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in  ('481', '632') then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
ORPC T0 INNER JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN RPC12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode =  T3.U_TaxCode 
where
T0.DocDate >= [%3]
 AND T0.DocDate <= [%4] AND
 t3.U_state = 'MUM'