exclude cancelled incoming payments in Journal Entries Query

[kedalenechong]

Hi all

How to exclude cancelled incoming payments in the following Journal Entries Query?

SELECT
T0.[TransId], T0.[Account], T3.[AcctName], T0.[ContraAct], T4.[AcctName], T2.[BaseRef] as 'Receipt No.',
T2.[RefDate], T0.[ShortName], T1.[CardName], T0.[Credit], T0.[Debit]
FROM [dbo].[JDT1] T0
LEFT OUTER JOIN OCRD T1 ON T0.[ShortName]=T1.[CardCode]
INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId]
LEFT OUTER JOIN OACT T3 ON T0.[Account]=T3.[AcctCode]
LEFT OUTER JOIN OACT T4 ON T0.[ContraAct]=T4.[AcctCode]
WHERE T0.[TransType] =24 AND T2.[RefDate] >='[%0]' AND T2.[RefDate] <='[%1]'

[kedalenechong]

Hi Nagarajan

Your Query seems correct, will monitor result.

[kothandaraman_nagarajan]

Hi,

Try this,

SELECT T0.[TransId], T5.[Canceled], T0.[Account], T3.[AcctName], T0.[ContraAct], T4.[AcctName], T2.[BaseRef] as 'Receipt No.', T2.[RefDate], T0.[ShortName], T1.[CardName], T0.[Credit], T0.[Debit] FROM [dbo].[JDT1] T0 INNER JOIN ORCT T5 ON T5.[TransId] = T0.[TransID] LEFT OUTER JOIN OCRD T1 ON T0.[ShortName]=T1.[CardCode] INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId] LEFT OUTER JOIN OACT T3 ON T0.[Account]=T3.[AcctCode] LEFT OUTER JOIN OACT T4 ON T0.[ContraAct]=T4.[AcctCode] WHERE T0.[TransType] =24 AND T2.[RefDate] >='[%0]' AND T2.[RefDate] <='[%1]' and T5.Canceled = 'N'

[jitin_chawla]

Hi,

Check this:

SELECT T0.[TransId], T5.[Canceled], T0.[Account], T3.[AcctName], T0.[ContraAct], T4.[AcctName], T2.[BaseRef] as 'Receipt No.', T2.[RefDate], T0.[ShortName], T1.[CardName], T0.[Credit], T0.[Debit] FROM [dbo].[JDT1] T0 INNER JOIN ORCT T5 ON T5.[TransId] = T0.[TransID] LEFT OUTER JOIN OCRD T1 ON T0.[ShortName]=T1.[CardCode] INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId] LEFT OUTER JOIN OACT T3 ON T0.[Account]=T3.[AcctCode] LEFT OUTER JOIN OACT T4 ON T0.[ContraAct]=T4.[AcctCode] WHERE T0.[TransType] =24 AND T2.[RefDate] >='[%0]' AND T2.[RefDate] <='[%1]' and T5.Canceled = 'Y'

Regards,

Jitin

[Former Member]

Hi,

try this

SELECT 
T0.[TransId], T0.[Account], T3.[AcctName], T0.[ContraAct], T4.[AcctName], T2.[BaseRef] as 'Receipt No.', 
T2.[RefDate], T0.[ShortName], T1.[CardName], T0.[Credit], T0.[Debit]
FROM [dbo].[JDT1] T0
LEFT OUTER JOIN OCRD T1 ON T0.[ShortName]=T1.[CardCode]
INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId]
LEFT OUTER JOIN OACT T3 ON T0.[Account]=T3.[AcctCode]
LEFT OUTER JOIN OACT T4 ON T0.[ContraAct]=T4.[AcctCode]
WHERE T0.[TransType] =24 AND T2.[RefDate] >='[%0]' AND T2.[RefDate] <='[%1]'
AND t0.BaseRef NOT IN 
(SELECT t5.DocEntry FROM dbo.ORCT T5 WHERE t5.CancelDate IS NOT NULL)

Thanks

Engr. Taseeb Saeed