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
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