Skip to content

Instantly share code, notes, and snippets.

@josemoralesp
Created February 12, 2019 01:04
Show Gist options
  • Save josemoralesp/beeeee5914abae2da445f2b97b9ad86e to your computer and use it in GitHub Desktop.
Save josemoralesp/beeeee5914abae2da445f2b97b9ad86e to your computer and use it in GitHub Desktop.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment