Skip to Content
author's profile photo Former Member
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

Assigned Tags

Related questions

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

  • author's profile photo Former Member
    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

  • author's profile photo Former Member
    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.