Skip to Content
0

exclude cancelled incoming payments in Journal Entries Query

Feb 01, 2017 at 07:43 AM

93

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

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

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

Share
10 |10000 characters needed characters left characters exceeded
Jitin Chawla
Feb 01, 2017 at 11:25 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 02, 2017 at 08:16 AM
0

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'

Share
10 |10000 characters needed characters left characters exceeded
Kedalene Chong Feb 03, 2017 at 09:46 AM
0

Hi Nagarajan

Your Query seems correct, will monitor result.

Share
10 |10000 characters needed characters left characters exceeded