Cancelled Invoices

[former_member645084]

Hi

I have below query & i dont want to include Cancelled Documents

SELECT T0.CardCode, T0.CardName,
(SUM(T1.Debit) - sum(T1.Credit)) 
FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND T1.TransType in ('13','14')
WHERE T0.CardType = 'C' 
Group By T0.CardCode, T0.CardName
having (SUM(T1.Debit) - sum(T1.Credit)) <> 0

Thanks

[marco_ramirez]

For this you must refer to the OINV table and exclude the documents with value 'Y' from the Canceled field.

Regards

[jitin_chawla]

Hi,

With limited data in DEMO, I am not sure that this is perfect but try and see:

SELECT T0.CardCode, T0.CardName,
(SUM(T1.Debit) - sum(T1.Credit)) 
FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND T1.TransType in ('13','14')
WHERE T0.CardType = 'C' AND T1.Transid not in (Select Transid from OINV WHERE Canceled = 'Y')
AND T1.Transid not in (Select Transid from ORIN WHERE Canceled = 'Y')
Group By T0.CardCode, T0.CardName
having (SUM(T1.Debit) - sum(T1.Credit)) <> 0
ORDER BY T0.CardCode

KR,

Jitin

[kothandaraman_nagarajan]

Hi,

Are you trying to get customer balance in above query?

Regards,

Nagarajan