on 05-24-2016 9:03 AM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
11 | |
11 | |
6 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.