Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- i.id,
- i.tenant_id,
- c.id AS customer_id,
- a.id AS account_id,
- a.subsidiary_id AS subsidiary_id
- FROM invoices AS i
- LEFT JOIN subsidiaries AS s ON s.id = COALESCE(i.subsidiary_id, 0)
- INNER JOIN (
- SELECT
- a.id, COALESCE(s.id, 0) AS subsidiary_id,
- c.id AS customer_id
- FROM (SELECT * FROM invoices WHERE void_flag = FALSE AND invoice_status_id = 6 AND tenant_id = 65 AND (invoice_outstanding > 0 OR invoice_outstanding_with_interest > 0)) AS i
- INNER JOIN accounts AS a ON i.account_id = a.id
- INNER JOIN customers AS c ON c.id = a.customer_id
- LEFT JOIN (
- SELECT
- account_id,
- subsidiary_id,
- SUM(invoice_amount + gst) AS total_amount,
- SUM(CASE WHEN invoice_due_date::DATE < CURRENT_DATE AND invoice_status_id = 6 THEN invoice_outstanding ELSE 0 END) AS total_due,
- SUM(COALESCE(invoice_outstanding,0)) AS total_outstanding,
- COUNT(id) AS num_of_invoices
- FROM invoices
- GROUP BY account_id, subsidiary_id
- ) AS inv ON inv.account_id = a.id AND inv.subsidiary_id = COALESCE(i.subsidiary_id, a.subsidiary_id, 0)
- LEFT JOIN subsidiaries AS s ON s.id = COALESCE(i.subsidiary_id, a.subsidiary_id, 0)
- LEFT JOIN (
- SELECT DISTINCT ON (group_name) group_code, group_name FROM customer_grouping
- --GROUP BY group_code, group_name
- ) AS cg ON cg.group_code = c.customer_group
- LEFT JOIN customers_addresses AS ca ON a.customer_id = ca.customer_id
- LEFT JOIN(
- SELECT id, VALUE AS state_name
- FROM global_references
- WHERE table_name = 'states'
- ) AS st ON ca.state_id = st.id
- LEFT JOIN (
- SELECT
- COALESCE(string_agg(t.code, ', '), '') AS code,
- cit.invoice_id AS invoice_id
- FROM customer_invoice_tags AS cit
- LEFT JOIN tags AS t ON cit.tag_id = t.id
- WHERE cit.tenant_id = 65
- GROUP BY cit.invoice_id
- ) AS t ON i.id = t.invoice_id
- LEFT JOIN (
- SELECT
- COALESCE(string_agg(t.code, ', '), '') AS code,
- cit.customer_id AS customer_id
- FROM customer_invoice_tags AS cit
- LEFT JOIN tags AS t ON cit.tag_id = t.id
- WHERE cit.tenant_id = 65
- GROUP BY cit.customer_id
- ) AS ct ON c.id = ct.customer_id
- LEFT JOIN (
- SELECT customer_id, key, VALUE
- FROM customers_additional_info
- WHERE tenant_id = 65 AND key = 'collector_id'
- ) AS customers_additional_info ON c.id = customers_additional_info.customer_id
- WHERE 1=1
- AND ( inv.total_due > 200000 )
- GROUP BY a.id, s.id, c.id
- ) AS a ON i.account_id = a.id AND COALESCE(s.id, 0) = a.subsidiary_id
- INNER JOIN customers AS c ON c.id = a.customer_id
- WHERE i.void_flag = FALSE AND i.invoice_status_id = 6 AND i.tenant_id = 65 AND (i.invoice_outstanding > 0 OR i.invoice_outstanding_with_interest > 0)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement