Skip to Content
Author's profile photo Balaji Sampath

TAX Queries

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

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo AndakondaRamudu A
      AndakondaRamudu A

      HI Sir

      Thanks for Shared Valuable Query here ...

      Author's profile photo Former Member
      Former Member

      Hi

      Thanks for shared query

      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan

      Hi Balaji,

      Thanks for sharing your experience.

      Regards

      Unnikrishnan

      Author's profile photo Balaji Sampath
      Balaji Sampath
      Blog Post Author

      Thank Unnikrishan

      You also doing a great job in scn keep it up

      Author's profile photo Former Member
      Former Member

      Thank you sir for sharing the valuable queries.

      Regards,

      Bharathiraja

      Author's profile photo Former Member
      Former Member

      Sir

      It will helpful

      Thanks

      Gopinath