test.sql

WITH a AS (
SELECT
    aml.payment_id,
    aml.id,
    aml.debit::numeric(12,2),
    aml.credit::numeric(12,2),
    aml.amount_currency::numeric(12,2)
FROM account_move_line aml
INNER JOIN account_account aa ON aa.id = aml.account_id
INNER JOIN account_account_type aat ON aat.id = aa.user_type_id AND aat.type = 'receivable'
INNER JOIN account_move am ON am.id = aml.move_id AND am.state = 'posted'
INNER JOIN account_partial_reconcile apr ON apr.credit_move_id = aml.id
INNER JOIN account_move am2 ON am2.tax_cash_basis_rec_id = apr.id
WHERE
aml.debit = 0
AND aml.date BETWEEN '2019-01-01' AND '2019-01-31'
AND aml.payment_id IS NOT NULL
/* AND am2.tax_cash_basis_rec_id IS NULL */
AND aml.payment_id = 4021
ORDER BY aml.date, aml.payment_id, aml.id
), B AS (
SELECT
    aml.payment_id,
    aml.id,
    aml.debit::numeric(12,2),
    aml.credit::numeric(12,2),
    aml.amount_currency::numeric(12,2)
FROM account_move_line aml
INNER JOIN account_account aa ON aa.id = aml.account_id
INNER JOIN account_account_type aat ON aat.id = aa.user_type_id AND aat.type = 'receivable'
INNER JOIN account_move am ON am.id = aml.move_id AND am.state = 'posted'
INNER JOIN account_partial_reconcile apr ON apr.credit_move_id = aml.id
WHERE
aml.debit = 0
AND aml.date BETWEEN '2019-01-01' AND '2019-01-31'
AND aml.payment_id IS NOT NULL
/* AND am2.tax_cash_basis_rec_id IS NULL */
AND aml.payment_id = 4021
ORDER BY aml.date, aml.payment_id, aml.id
) SELECT * FROM a WHERE a.id IN (SELECT b.id FROM b)