Created
February 12, 2019 01:04
-
-
Save josemoralesp/beeeee5914abae2da445f2b97b9ad86e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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