Skip to Content
0
Former Member
Aug 18, 2012 at 10:53 AM

AP INVOICE QUERY

424 Views

Hi,

I have a query that display all the document which is in the AP invoice and its taxes.

I had added one AP that entry have the item twice in one document.

but in report it will come only once, Please find the query posted below

SELECT DISTINCT T0.[DocNum], T0.[DocDate], T0.[TaxDate], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[LineTotal], T1.[TaxCode],

(SELECT sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',

(SELECT sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS',

(SELECT sum(TaxSum) FROM PCH4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS',

(SELECT sum(TaxSum) FROM PCH4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',

(SELECT sum(TaxSum) FROM PCH4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',

(SELECT sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'SERVICETAX',

(SELECT sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',

(SELECT sum(TaxSum) FROM PCH4 where statype=-10 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%'

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

WHERE T1.[TargetType] ='-1' and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T0.[CardName] ='[%2]' OR '[%2]'=' ') AND (T1.[Dscription] ='[%3]' OR '[%3]'=' ')

ORDER BY T0.[DocDate], T0.[DocNum]

Can any one give me the solution?

Regards,

Pushpa Damodaran