cancel
Showing results for 
Search instead for 
Did you mean: 

Tax Break Up in Sales Report

Former Member
0 Kudos

Hi Experts,

Below is the query i have used to run the Sales Register.

But i need the Tax wise breakup in this query..PLs update my query

(need to display the invoices by tax wise--

TaxCode 1

Inv no 1

inv no 2

.....

Inv no Z.

TaxCode 2

Inv no 1

inv no 2

.....

Inv no Z.

..

..

TaxCode N

Inv no 1

inv no 2

.....

Inv no Z.

)

SELECT M.DocNum AS ' Inv No. #', M.DocDate as 'Posting Date', M.CardName as 'Customer Name', 
(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as 'BaseAmt.(Rs.)', 
 
M.VatSum as 'Tax (Rs.)',
M.DocTotal as 'Total (Rs.)',
(M.DocTotal-M.[PaidToDate]) as 'BalanceDue (Rs.)'
FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry 
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry

WHERE M.DocDate BETWEEN '[%0]' AND '[%1]' AND TargetType ! = 14 
GROUP BY 
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,
M.WTSum,M.DocTotal,M.PaidToDate,M.VatSum
ORDER BY
M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,
M.WTSum,M.DocTotal,M.PaidToDate,M.VatSum

Thanks in advance,

Regards,

Vamsi.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks guys...

Its been solved with ur help

Former Member
0 Kudos

Hi Vamsi,

Try to Design the Query Print Layout(QPLD) because your requirement is possible on QPLD.

Regards,

Madhan.

former_member206437
Active Contributor
0 Kudos

Hi

Try this:

SELECT M.DocNum AS 'AR Inv. #', M.DocDate as 'Date', M.CardName as 'Customer Name',M.NumAtCard as 'Customer Ref',

(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',

(SELECT Avg(TaxRate) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) as 'ED %',

(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) as 'ED (Rs.)',

(SELECT Avg(TaxRate) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) as 'EDCS %',

(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) as 'EDCS (Rs.)',

(SELECT Avg(TaxRate) FROM INV4 where statype=-55 and DocEntry=M.DocEntry) as 'HECS %',

(SELECT Sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=M.DocEntry) as 'HECS (Rs.)',

(SELECT Avg(TaxRate) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as ' VAT % ',

(SELECT Sum(TaxSum) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as ' VAT (Rs.) ',

(SELECT Avg(TaxRate) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as ' CST % ',

(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as ' CST (Rs.) ',

(SELECT Sum(TaxSum) FROM INV4 where statype=-40 and DocEntry=M.DocEntry) as ' CVD (Rs.) ',

(SELECT Avg(TaxRate) FROM INV4 where statype=5 and DocEntry=M.DocEntry) as ' Ser.Tax % ',

(SELECT Sum(TaxSum) FROM INV4 where statype=5 and DocEntry=M.DocEntry) as ' Ser.Tax (Rs.) ',

(SELECT Avg(TaxRate) FROM INV4 where statype=6 and DocEntry=M.DocEntry) as 'CS on Ser.Tax %',

(SELECT Sum(TaxSum) FROM INV4 where statype=6 and DocEntry=M.DocEntry) as 'CS on Ser.Tax (Rs.)',

(SELECT Avg(TaxRate) FROM INV4 where statype=-10 and DocEntry=M.DocEntry) as 'HECS_ST %',

(SELECT Sum(TaxSum) FROM INV4 where statype=-10 and DocEntry=M.DocEntry) as 'HECS_ST (Rs.)',

(Select Sum(LineTotal) From INV3 Q Where Q.DocEntry=M.DocEntry) AS 'Freight (Rs.)',

M.WTSum AS 'TDS (Rs.)',

M.DocTotal as 'Total (Rs.)'

FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry

WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]')

GROUP BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.DocTotal

ORDER BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.DocTotal

Giri