Dear All
I posted here some Accounting Tax Queries it will helpful for new comers in SAP Business One.
A/P Invoice Tax Wise Breakup
declare @todate as datetime declare @enddate as datetime SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]' SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]' select DISTINCT a.Docnum as "SAP Invoice No" ,a.docdate AS " SAP Invoice Date" ,a.comments, a.Numatcard AS "Vendor Bill No" ,a.U_Date AS " Vendor Bill Date" ,a.cardcode as "Party Code" ,a.cardname as "Name of the Supplier" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin No], (SELECT SUM(Quantity) FROM PCH1 where docentry =a.docentry)as "Quantity" , (select sum(LineTotal) from PCH1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM PCH4 where docentry = a.docentry and statype=-90) as "BED" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=1) as "VAT" , (Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=4) as "CST" , A.VatSum as "Total Tax" ,(select sum(linetotal) from PCH3 where docentry = a.docentry) as 'Freight' , A.DocTotal as "Net Value" FROM OPCH A left OUTER JOIN PCH3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN PCH4 C ON A.DOCENTRY = C.DOCENTRY left outer join PCH3 h on A.DocEntry = h.DocEntry left outer join PCH1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and g.itemcode=d.itemcode ORDER BY A.DOCNUM
A/R Invoice Tax Wise Breakup
declare @todate as datetime declare @enddate as datetime SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]' SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]' select DISTINCT a.Docnum as "Invoice No" ,a.Numatcard AS "Customer Reference" ,a.comments, a.cardcode as "Cust.Code", a.cardname as "Customer Name" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin#], a.docdate AS "Invoice Date" ,(SELECT SUM(Quantity) FROM INV1 where docentry =a.docentry)as "Quantity" , (select sum(LineTotal) from INV1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM INV4 where docentry = a.docentry and statype=-90) as "BED" , (Select SUM(taxsum) FROM INV4 where docentry = a.docentry and statype=1) as "VAT" , (Select SUM(taxsum) FROM INV4 where docentry = a.docentry and statype=4) as "CST" , A.VatSum as "Total Tax" ,(select sum(linetotal) from inv3 where docentry = a.docentry) as 'Freight' , A.DocTotal as "Net Value" FROM OINV A left OUTER JOIN INV3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN INV4 C ON A.DOCENTRY = C.DOCENTRY left outer join INV3 h on A.DocEntry = h.DocEntry left outer join INV1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and g.itemcode=d.itemcode ORDER BY A.DOCNUM
TDS Report Section Wise
SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate],T0.[BaseAmnt], T0.[WTSum] as TDSAmount, T0.[DocTotal] FROM [dbo].[OPCH] T0 INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry WHERE T0.[DocDate] > =[%0] AND T0.[DocDate] < =[%1] AND T0.[WTSum] > = '1' AND T1.[WTCode] =[%2]
Regards
Balaji Sampath
