master_data_reconciliation_gt.sql

SELECT DISTINCT
	ON (T . ID) T . ID,
	ip. ID
FROM
	bras_ip_pool T
INNER JOIN ip_vrf v1 ON (v1. ID = T .vrf_id)
INNER JOIN ip_vrfgroup vg1 ON (vg1. ID = v1.vrf_group_id)
INNER JOIN ip_prefix ip
LEFT OUTER JOIN ipannouncement ia on (ia.prefix_id=ip.id)
INNER JOIN ip_vrf v2 ON (v2. ID = ip.vrf_id)
INNER JOIN ip_vrfgroup vg2 ON (vg2. ID = v2.vrf_group_id)
ON (
	(
		(
			vg1.address_constraint = 'V'
			AND v1. ID = v2. ID
		)
		OR (
			vg1.address_constraint = 'G'
			AND vg1. ID = vg2. ID
		)
	)
	AND
	  ip.prefix >> T.ip
	AND ip.prefix<>'0.0.0.0/0'
  AND ip.prefix<>'::/0'
  AND ip.status = '1'
)
LEFT OUTER JOIN notification n ON (
	(
		n.object_id_1 = T . ID
		AND n.content_type_1_id = (
			SELECT
				dct. ID
			FROM
				django_content_type dct
			WHERE
				dct.model = '%(module)s'
		)
		AND n.object_id_2 = ip. ID
		AND n.content_type_2_id = (
			SELECT
				dct. ID
			FROM
				django_content_type dct
			WHERE
				dct.model = 'prefix'
		)
	)
	OR (
		n.object_id_1 = ip. ID
		AND n.content_type_1_id = (
			SELECT
				dct. ID
			FROM
				django_content_type dct
			WHERE
				dct.model = 'prefix'
		)
		AND n.object_id_2 = T . ID
		AND n.content_type_2_id = (
			SELECT
				dct. ID
			FROM
				django_content_type dct
			WHERE
				dct.model = '%(module)s'
		)
	)
)
WHERE
	ia is null and n. ID IS NULL and T.is_redundant=FALSE  and T.id in (%(ids)s)
ORDER BY
	T . ID,
	ip. PREFIX DESC