on 11-21-2011 11:31 AM
Forum,
We would like to see sales for a particular month, which shows Sales Total (WITH CARRIAGE AND VAT) currently the standard SAP report only shows sales without it.
Any help would be greatly appreciated.
Regards,
Juan
Hi,
Try this.... Put Your VAT Tax code statype here and modify the query as per your requirement...
DECLARE @startDate DATETIME, @endDate DATETIME, @dummy INTEGER
SELECT TOP 1 @dummy = DocNum from OINV T0 where T0.DocDate >=[%1] and T0.DocDate <= [%2]
Select @startDate='[%1]' ,@endDate = '[%2]'
SELECT DISTINCT T0.Docentry,
T0.[DocNum] as 'Bill No.',
CONVERT(VARCHAR(10), T0.[DOCDATE], 3) AS DOCDATE, T0.[CardName] as 'Customer Name' , T1.[ItemCode] as 'Item Code', T1.[Dscription] as 'Item Description', T1.[Quantity],T1.[Price] as 'Price', T1.[TaxCode],
( T1.LineTotal ) as 'Line Total' ,
T0.[TotalExpns] as 'P&F',
(T1.LineTotal + T0.[TotalExpns]) AS 'Basic',
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=1 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)VAT,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=4 and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)CST,
T0.DocTotal as 'Doc Total' , T0.[Comments] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocDate >=@startDate and T0.DocDate <= @endDate
Regards,
Sudhir B.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Juan........
Please try this.......
SELECT SUBSTRING((CONVERT(VARCHAR(11),T0.DOCDATE,106)),4,11), T0.[DocNum], T0.[DocDate], Sum(T1.[Quantity]) 'Quantity', Sum(T1.[LineTotal]) 'Amount',(Sum(T1.[LineTotal])/Sum(T1.[Quantity])) 'Rate', T0.[TotalExpns] 'Pack/Forw.', t0.doctotal-t0.vatsum as 'Mat.Value', (isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'Cenvat 10%',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'ECESS%' then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'Edu. Cess 2%',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC%' then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'S&H Cess 1%',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'VAT4%',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'AVAT%' and t4.TaxRate=1 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) 'VAT1%'
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
Where T0.DocDate>='[%0]' and T0.DocDate<='[%1]'
Group By T0.[DocNum], T0.[DocDate], T0.[TotalExpns], t0.doctotal, t0.vatsum, T0.DocEntry
Order By T0.[DocDate]
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
6 | |
4 | |
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.