Skip to Content

exclude cancelled incoming payments in Journal Entries Query

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]'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Feb 01, 2017 at 10:37 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2017 at 11:25 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 08:16 AM

    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'

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 03, 2017 at 09:46 AM

    Hi Nagarajan

    Your Query seems correct, will monitor result.

    Add comment
    10|10000 characters needed characters exceeded