Skip to Content
Former Member

# Total Purchases

Hi Friends,

How we know the total purchase amount...?

Is the vat amount getting included in the purchase analyses purchase amount as per SAP B1 report.......?

Thanks

Komanduri.KS

##### Add a comment
10|10000 characters needed characters exceeded

### 3 Answers

• Best Answer
Posted on Dec 13, 2011 at 10:02 AM

HI Komanduri

Tax amount not included in Purchase Analysis report only accessable value is showned. If you want report included with Tax Amount Means make a query or Genarate the report in XL or Crystal Reporter.

Try This Query Also

```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=-60) as "Cess" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=7) as "HeCess" ,(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```

With Regards

Balaji Sampath

##### Add a comment
10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 13, 2011 at 11:37 AM

Hi Komanduri.KS,

Try this

```/*SELECT FROM [dbo].[OPCH] T2*/
Declare @startDate as DATETIME
/* WHERE */
Set @startDate = /* T2.DocDate */  '[%1]'

/*SELECT FROM [dbo].[OPCH] T3*/
Declare @endDate as  DATETIME
/* WHERE */
Set @endDate = /* T3.DocDate */  '[%2]'

SELECT DISTINCT  T0.[DocNum] as 'Invoice No.', T0.[DOCDATE] as 'Invoice Date', T0.[CardName] as 'Customer Name' ,

((T0.DocTotal-T0.[VatSum]) ) as 'Gross Amount',T2.SeriesName,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-90 and PCH4.docentry=T0.Docentry  )BED@10,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-60 and PCH4.docentry=T0.Docentry )ECESS@2,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-55 and PCH4.docentry=T0.Docentry )SHE@1,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  1 and PCH4.docentry=T0.Docentry )VAT@4,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  7 and PCH4.docentry=T0.Docentry )ADDVAT@1,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  7 and PCH4.docentry=T0.Docentry )CST@2,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-80 and PCH4.docentry=T0.Docentry )AED@4,

( T0.Doctotal ) as 'Net Amount' , T3.City

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN NNM1 T2 ON T0.Series = T2.Series
left outer join ocrd T3 on T0.CardCode = T3.CardCode

Where T0.DocDate >=@startDate
and T0.DocDate <= @endDate order by T0.DocDate, T0.Docnum```

Just change the Statype as per your DB. For Statype Run Query

`SELECT distinct T0.[staType], T0.[StcCode], T0.[StaCode] FROM PCH4 T0`

Here Name call as Authority Type.

Change the Table INV in place of PCH for Sales Query.

Thanks,

Srujal Patel

##### Add a comment
10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 14, 2011 at 05:41 AM

Hi Komanduri......

Your Total purchase is Nothing but Basic Purchase which should not include VAT Amount or any tax amount whose credit you get........

It should always be basic.......

Regards,

Rahul

##### Add a comment
10|10000 characters needed characters exceeded

### Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.