Creation of View for G/L Account Selected in BP Master Data

CREATE View [dbo].[OCRDFN2]
as
(
Select
CardName,
CardCode,
CardFName ,
 CASE    

      when DebPayAcct = '_SYS00000000227' then '12700001-001'
      when DebPayAcct = '_SYS00000000617' then '21180001-001'
      when DebPayAcct = '_SYS00000000230' then '12700001-006'
      when DebPayAcct = '_SYS00000000620' then '21180001-006'  
      when DebPayAcct = '_SYS00000000618' then '21180001-004'
      when DebPayAcct = '_SYS00000000228' then '12700001-004'
      when DebPayAcct = '_SYS00000000229' then '12700001-005'
      when DebPayAcct = '_SYS00000000619' then '21180001-005'
 End 'DebPaySeg',
 CASE 

      when DebPayAcct = '_SYS00000000227' then 'Bangalore'
      when DebPayAcct = '_SYS00000000617' then 'Bangalore'
      when DebPayAcct = '_SYS00000000230' then 'Mumbai'
      when DebPayAcct = '_SYS00000000620' then 'Mumbai'  
      when DebPayAcct = '_SYS00000000618' then 'Chennai'
      when DebPayAcct = '_SYS00000000228' then 'Chennai'
      when DebPayAcct = '_SYS00000000229' then 'Pune'
      when DebPayAcct = '_SYS00000000619' then 'Pune'
 End 'DebPay',
 CASE
      when Dpmclear =   '_SYS00000000149' then '47000003-001'
      when Dpmclear =   '_SYS00000000621' then '21180002-001'
      when Dpmclear =   '_SYS00000000232' then '12700003-001'
      when Dpmclear =   '_SYS00000000624' then '21180002-006'
      when Dpmclear =   '_SYS00000000235' then '12700003-006'
      when Dpmclear =   '_SYS00000000233' then '12700003-004'
      when Dpmclear =   '_SYS00000000622' then '21180002-004'
      when Dpmclear =   '_SYS00000000623' then '21180002-005'
      when Dpmclear =   '_SYS00000000234' then '12700003-005'
   End   'DebClearSeg',
CASE
      when Dpmclear =   '_SYS00000000149' then 'Bangalore'
      when Dpmclear =   '_SYS00000000621' then 'Bangalore'
      when Dpmclear =   '_SYS00000000232' then 'Bangalore'
      when Dpmclear =   '_SYS00000000624' then 'Mumbai'
      when Dpmclear =   '_SYS00000000235' then 'Mumbai'
      when Dpmclear =   '_SYS00000000233' then 'Chennai'
      when Dpmclear =   '_SYS00000000622' then 'Chennai'
      when Dpmclear =   '_SYS00000000623' then 'Pune'
      when Dpmclear =   '_SYS00000000234' then 'Pune'
   End 'DebClear',
  DebPayAcct , DpmClear from OCRD )
GO

Cumulative Balance By Project Wise Query

SELECT (SUM_ABC) , ProjectName From(SELECT Sum(T1.[Debit])-SUM(T1.[Credit]) SUM_ABC ,(Select max(T1.Project)) ProjectNameFROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId =  T1.TransId  inner join OCRD T2 on T2.[CardCode] = T1.[ShortName]  INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode  WHERE T1. [ShortName] like 'S%' and T0.RefDate <= '20200104'  and T3.[GroupName]='Suppliers'  and T1.[Project]='APSHHN'  GROUP BY T1.[ShortName],T2.[CardName],T1.[Project]  having Sum(T1.[Debit])-Sum(T1.[Credit])!= 0 and Sum(T1.[Debit])-Sum(T1.[Credit])<0) SUM_PQ

Inventory Report Combining of Two Databases

(SELECT  T0.Itemcode ,
CASE When T0.ItemCode like 'DBA%' then 'Database_A' End 'Database',
T1.FrgnName as 'Part No', T1.ItemName, T2.ItmsGrpNam, T0.WhsCode, T0.ONHAND as 'Total Qty',  T0.ONHAND*T0.AVGPRICE as 'StockValue',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30 THEN T0.ONHAND END '>30 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30THEN T0.ONHAND*T0.AVGPRICE END '>30 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60 THEN T0.ONHAND END '>60 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60THEN T0.ONHAND*T0.AVGPRICE END '>60 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND END '>180 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND*T0.AVGPRICE END '>180 Days(Value)'
FROM 
[DATABASE_A].[dbo].[OITW] as T0 INNER JOIN [DATABASE_A].[dbo].[OITM] as T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN [DATABASE_A].[dbo].[OITB] as T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join
[DATABASE_A].[dbo].[IBT1] as t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode
WHERE
T0.ONHAND>0  and (T0.WhsCode like 'BLR%') and T2.ItmsGrpNam in ('Toner' , 'Cartridge')

Union All


SELECT T0.Itemcode,CASE When T0.ItemCode like 'DBB%' then 'Database_B' End 'Database',
 T1.FrgnName as 'Part No', T1.ItemName, T2.ItmsGrpNam, T0.WhsCode, T0.ONHAND as 'Total Qty',  T0.ONHAND*T0.AVGPRICE as 'StockValue',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30 THEN T0.ONHAND END '>30 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30THEN T0.ONHAND*T0.AVGPRICE END '>30 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60 THEN T0.ONHAND END '>60 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60THEN T0.ONHAND*T0.AVGPRICE END '>60 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND END '>180 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND*T0.AVGPRICE END '>180 Days(Value)'
FROM 
[DATABASE_B].[dbo].[OITW] as T0 INNER JOIN [DATABASE_B].[dbo].[OITM] as T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN [DATABASE_B].[dbo].[OITB] as T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join
[DATABASE_B].[dbo].[IBT1] as t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode
WHERE
T0.ONHAND>0 and (T0.WhsCode like 'BLR%') and T2.ItmsGrpNam in ('Toner' , 'Cartridge'))
Order By
T1.FrgnName

Price Difference Between Last Purchase Price & Unit Price Fms Query

SELECT $[POR1.U_Tax_name.numbeR] - $[$38.14.numbeR]

Hour/Minutes Calculation Between Two Specific Date/Time & Converting Integer into varchar for Time Format

Select (DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm)) as 'Minutes' ,

((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100)) as 'Hrs' ,

CASE when Durtype = 'M' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.1*1000))

when Durtype = 'H' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100))

when Durtype = 'D' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100))

End 'TotalHrs.Mins',

OCLG1.DurType , OCLG1.ClgCode, OCLG1.parentId , OCLG1.CardCode , OCLG1.Recontact as 'BeginDate' , OCLG1.BeginTime,

CONVERT(datetime, CONVERT(varchar(10), OCLG1.Recontact, 120) + ' '

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.BeginTime)) + CONVERT(varchar, OCLG1.BeginTime, 10), 1, 2) + ':'

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.BeginTime)) + CONVERT(varchar, OCLG1.BeginTime, 10), 3, 4) + ':' + '00.000') as 'StartDtTm',

OCLG1.endDate , OCLG1.ENDTime ,

CONVERT(datetime, CONVERT(varchar(10), OCLG1.endDate, 120) + ' '

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.ENDTime)) + CONVERT(varchar, OCLG1. ENDTime, 10), 1, 2) + ':'

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.ENDTime)) + CONVERT(varchar, OCLG1. ENDTime, 10), 3, 4) + ':' + '00.000') as 'EndDtTm'

from OCLG1

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'

Invoice Prepared before 30 days only by Item description

SELECT 
a.CardCode as 'Code',
a.Cardname as 'Customer Name' ,
a.Docentry ,
a.Docnum as 'Inv.No' ,
a.Docdate as 'Inv.dt' ,
b.U_part_no as 'PartNo.', 
b.Dscription,
b.U_inv_f_text as 'Inv.Txt',
b.Price as 'Unit SP',
b.Quantity,
b.linetotal as 'Total SP'  ,
 Case
when a.DocType = 'I' then 'INVOICE'
when a.DocType = 'S' then 'INVOICE'
End 'Doctype'
from OINV a inner join INV1 b on a.docentry = b.docentry
where b.Dscription like '%Rental Charges%'
and  (a.docdate =  CONVERT(VARCHAR(10),DATEADD(MONTH,-1,GETDATE()+1),120) )

One Year Sales Analysis Report

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) – sum(T1.Credit)) AS ‘January’, (SUM(T2.Debit) – sum(T1.Credit)) AS ‘February’, (SUM(T3.Debit) – sum(T1.Credit)) AS ‘March ‘, (SUM(T4.Debit) – sum(T1.Credit)) AS ‘April’, (SUM(T5.Debit) – sum(T1.Credit)) AS ‘May ‘, (SUM(T6.Debit) – sum(T1.Credit)) AS ‘June’, (SUM(T7.Debit) – sum(T1.Credit)) AS ‘July ‘, (SUM(T8.Debit) – sum(T1.Credit)) AS ‘August’, (SUM(T9.Debit) – sum(T1.Credit)) AS ‘September ‘, (SUM(T10.Debit) – sum(T1.Credit)) AS ‘October ‘, (SUM(T11.Debit) – sum(T1.Credit)) AS ‘November ‘, (SUM(T12.Debit) – sum(T1.Credit)) AS ‘December ‘ FROM dbo.OCRD T0 LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1. Duedate) = 1 AND Year(T1.Duedate) = Year(GetDate()) AND T1.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2. Duedate) = 2 AND Year(T2.Duedate) = Year(GetDate()) AND T2.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T3 ON T3.ShortName = T0.CardCode AND Month(T3. Duedate) = 3 AND Year(T3.Duedate) = Year(GetDate()) AND T3.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T4 ON T4.ShortName = T0.CardCode AND Month(T4. Duedate) = 4 AND Year(T4.Duedate) = Year(GetDate()) AND T4.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T5 ON T5.ShortName = T0.CardCode AND Month(T5. Duedate) = 5 AND Year(T5.Duedate) = Year(GetDate()) AND T5.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T6 ON T6.ShortName = T0.CardCode AND Month(T6. Duedate) = 6 AND Year(T6.Duedate) = Year(GetDate()) AND T6.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T7 ON T7.ShortName = T0.CardCode AND Month(T7. Duedate) = 7 AND Year(T7.Duedate) = Year(GetDate()) AND T7.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T8 ON T8.ShortName = T0.CardCode AND Month(T8. Duedate) = 8 AND Year(T8.Duedate) = Year(GetDate()) AND T8.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T9 ON T9.ShortName = T0.CardCode AND Month(T9. Duedate) = 9 AND Year(T9.Duedate) = Year(GetDate()) AND T9.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T10 ON T10.ShortName = T0.CardCode AND Month(T10. Duedate) = 10 AND Year(T10.Duedate) = Year(GetDate()) AND T10.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T11 ON T11.ShortName = T0.CardCode AND Month(T11. Duedate) = 11 AND Year(T11.Duedate) = Year(GetDate()) AND T11.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T12 ON T12.ShortName = T0.CardCode AND Month(T12. Duedate) = 12 AND Year(T12.Duedate) = Year(GetDate()) AND T12.TransType in (’13’,’14’) WHERE T0.CardType = ‘C’ Group By T0.[CardCode], T0.[CardName]

Deliveries done on Previous Day

 SELECT 
T0.[DocNum] as ‘Delivery Note No.’, 
T0.[DocDate],T0.[DocDueDate] as ‘Delivery Date’, 
T0.[CardName] as ‘Customer Name’, 
T1.[ItemCode], 
T1.[Dscription], 
T1.[Quantity]
 FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE T0.[DocDate] =DATEADD(DAY, -1, CONVERT(char,GETDATE(),101)) 
order by T0.[CardName] , T0.[DocDate] 

Stock By Warehouse

SELECT 
T1.WhsName,
T0.ItemCode,
SUM(T0.InQty – T0.OutQty) as ‘On Hand Changed’
FROM dbo.OINM T0
INNER JOIN dbo.OWHS T1 ON T0.Warehouse = T1.WhsCode
WHERE T0.DocDate >= ‘[%0]’ and
T1.WhsName like ‘[%1]%’ and
T0.ItemCode like ‘[%2]%’
GROUP BY T1.WhsName, T0.ItemCode
Having SUM(T0.InQty – T0.OutQty) != 0

Active item list Query

SELECT T0.ItemCode

FROM dbo.OITM T0

WHERE T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.ITT1) OR

T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.OITT) OR T0.ITEMCODE

IN (SELECT DISTINCT ITEMCODE FROM dbo.OINM) OR T0.ITEMCODE IN (SELECT

DISTINCT ITEMCODE FROM dbo.ADO1)