Exclude A/P Credit Memo from this Query

[former_member541807]

Hello All,

i am trying to figure out to exclude A/P Invoice that it was already Credit Memo with my existing Query

SELECT
max(T0.DocDate) as 'Date',
max(T1.CardName) as 'Vendor Name',
max(T0.Address) as 'Vendor Address',
count(T0.DocNum) as 'No. of Invoices',
max(T1.LicTradNum) as 'TIN No.',
sum(T0.BaseAmnt) as 'Base Amount',
sum(T0.VatSum) as 'Input Tax',
max(T1.ECVatGroup ) as 'TaxCode',
max(T2.WTCode) as 'WtaxCode'
FROM OPCH T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
left join PCH5 T2 on T0.DocEntry = T2.AbsEntry
WHERE T0.DocDate BETWEEN '[%0]' and '[%1]' and T0.VatSum <> 0 
GROUP BY T1.CardCode

Please help mew revise my Query.

this is connected with my previous question   but i cannot figure out how to incorporate PCH1.TargetEntry

Please Help.

Thank You.

Fidel

[kothandaraman_nagarajan]

Hi,

Your query already written without considering Credit memo. There is no link between AP invoice and credit memo in above query.

Thanks & Regards,

Nagarajan

[former_member541807]

Hello Nagarajan,

thanks for the response.

My query retrieve A/P Invoices that is been credit memo, what i want is to exclude or NOT to include the A/P Invoices that was been Credit Memo. what i only want is the A/P invoices that was not Credit Memo.

will help me to revise my query to the my requirement?

thanks

Fidel

[former_member184146]

try the below query

SELECT 

max(T0.DocDate) as 'Date', 

max(T1.CardName) as 'Vendor Name', 

max(T0.Address) as 'Vendor Address', 

count(T0.DocNum) as 'No. of Invoices', 

COUNT(T5.DocNum) AS 'No of Credit Memos',

max(T1.LicTradNum) as 'TIN No.', 

sum(T0.BaseAmnt) as 'Base Amount', 

sum(T0.VatSum) as 'Input Tax', 

max(T1.ECVatGroup ) as 'TaxCode', 

max(T2.WTCode) as 'WtaxCode' 

FROM OPCH T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode  

INNER JOIN pch1 t3 ON t3.DocEntry=t0.DocEntry

LEFT JOIN dbo.RPC1 T4 ON T4.BaseEntry=T3.DocEntry left JOIN dbo.ORPC T5 ON T5.DocEntry=T4.DocEntry

left join PCH5 T2 on T0.DocEntry = T2.AbsEntry 

WHERE T0.DocDate BETWEEN '[%0]' and '[%1]' and T0.VatSum <> 0  

GROUP BY T1.CardCode 

--Manish

[former_member541807]

Hi Manish,

the Query you had provided does not get the exact amount. it multiply the amount with num of rows is guess.

thanks

Fidel