Skip to Content
0
Former Member
May 24, 2016 at 08:03 AM

Tax calculations for AR Invoice

25 Views

this is my query but i want dispaly Sales Vat5%,and ,VAT sales14.5% and CST sales and output vat5 % ,output vat 14.5,CST payable

How i can display all Tax calculations , As Upto I just displayed on VAT5,VAT14.5,CSt

ALTER proc [dbo].[@SMS_RPT_SALES_SBC_23052016]

(

@FromDate Date,

@ToDate Date

)

as

begin

Select

T0.CardName [Customer Name],(isnull(T1.itemcode,''))[Item Code],(isnull(T1.Dscription,''))[Description],T1.Quantity,

T1.LineTotal[Rate],T5.Location,T0.DocNum,T5.Code,T6.SeriesName,T0.TotalExpns[Packing & Forwarding Charges Recd],T1.LocCode,

convert(varchar(max),T0.DocDate,103)as Date,T0.RoundDif[Round Off],T0.Series,T2.FrgnName[part No],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('VAT')),0) [VAT],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('CST')),0) [CST],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('EX-DT','BED')),0) [ED 12.5%] ,

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry= T1.DocEntry and OSTT.name in('CESS')),0) [CESS 2%],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.name in('VAT14.5')),0) [VAT 14.5],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('VAT')),0) [VAT Rate],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('CST')),0) [CST Rate],

isnull((select top 1 CONVERT(varchar(10),CONVERT(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('EX-DT','BED')),0) [ED 12.5%],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('CESS')),0) [CESS 2%],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('VAT14.5')),0) [VAT 14.5]

from OINV T0

inner join INV1 T1 on T1.DocEntry=T0.DocEntry

left outer join OITM T2 on T2.ItemCode=T1.ItemCode

left Join OSTC T4 on T4.Code=T1.TaxCode

inner join OLCT T5 on T5.Code=T1.LocCode

left outer join NNM1 T6 on T6.Series=T0.Series --and ObjectCode='140000009'

where T0.DocEntry=T1.DocEntry and TaxDate Between @FromDate And @ToDate

Group By T1.itemcode,T0.DocNum,T0.CardName,T1.Dscription,T1.Quantity,T1.LineTotal,T6.SeriesName,T0.TotalExpns,T0.Series,T2.FrgnName,T1.LocCode,T0.DocDate,T0.RoundDif,T5.Location,T5.Code,T1.DocEntry

end