0
Former Member
Jan 07, 2011 at 07:02 AM

# CR query

34 Views

Hi Experts,

To create a purchase order, I have used this formula

SELECT op.docentry,

op.docnum,

op.docdate,

op.cardcode,

op.cardname,

p1.itemcode,

p1.dscription as 'Item Name',

p1.Quantity,

p1.price,

p1.linetotal,

isnull(vat.taxsum,0) as 'VAT Amount',

isnull(cst.taxsum,0) as 'CST Amount',

Excise.taxsum as 'Cenvat Amount',

Cess.TaxSum as 'Cess Amount',

Hcess.Taxsum as ' Hcess Amount',

op.discsum as 'Discount',

op.TotalExpns as 'Freight Charges',

op.doctotal

from OPCH op

inner join PCH1 p1 on

op.docentry=p1.docentry

left outer join (select * from pch4 where statype=1) vat on p1.linenum=vat.linenum and p1.docentry=vat.docentry

left outer join (select * from pch4 where statype=4) cst on p1.linenum=cst.linenum and p1.docentry=cst.docentry

left outer join (select * from pch4 where statype=-90) excise on p1.linenum=excise.linenum and p1.docentry=excise.docentry

left outer join (select * from pch4 where statype=-60) cess on p1.linenum=cess.linenum and p1.docentry=cess.docentry

left outer join (select * from pch4 where statype=-55) Hcess on p1.linenum=Hcess.linenum and p1.docentry=Hcess.docentry

And in CR2008 generating the report.

Am I going right?

Is there any option so that VAT comes when there is VAT amount and CST comes when there is CST amount? How can we do that?