cancel
Showing results for 
Search instead for 
Did you mean: 

Excise Register Query

Former Member
0 Kudos

Dear Experts,

I need a single query for Output & Input Excise base on Outgoing & Incoming document.

The result should like this

Output Excise

Doc No Doc Date Customer Name Ass Value BED Cess HSCess

Total of Output Tax

Input Excise

Doc No Doc Date Customer Name Ass Value BED Cess HSCess

Total of Input Tax

Net Excise Payable

Regards

Datta Kharat

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Dear Datta Kharat,

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

Former Member
0 Kudos

Hi

This is for sales

SELECT DISTINCT 
T0.[DocNum] as 'Bill No.', 
CONVERT(VARCHAR(10), T0.[DOCDATE], 3) AS DOCDATE, T0.[CardName] as 'Customer Name' , T1.[Quantity], T1.[TaxCode],
( T1.LineTotal ) as 'Line Total' ,
T0.[TotalExpns] as 'P&F',
 (T1.LineTotal + T0.[TotalExpns]) AS 'Basic',
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=7 or INV4.statype=-90) and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum )BED,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=8 or INV4.statype=-90)and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum)Cess,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=9 or INV4.statype=-90) and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)HeCess,

( T1.LineTotal + T0.[TotalExpns]  +  
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where(INV4.statype=7 or INV4.statype=-90) and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum ) +
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where(INV4.statype=8 or INV4.statype=-90) and INV4.docentry=T0.Docentry and INV4.linenum = T1.Linenum) +
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=9 or INV4.statype=-90) and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum))"VAT Basic Value",




(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=12 or INV4.statype=1) and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)VAT,
(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where (INV4.statype=13 or INV4.statype=4) and INV4.docentry=T0.Docentry and INV4.linenum = T1.linenum)CST,
 T0.DocTotal as 'Doc Total'  FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
 WHERE T0.[DocType]!='I'

change statype as per you taxcode. For purchase change the table as OPCH,PCH1,PCH4 and make it as union

Regards,

Sudhir B.