Gist criado pelo Formatador-Planos para compartilhamento de Query SQL.

atualizado.sql

SELECT
	a.inf_nfe,
	a.n_nf,
	a.id_empresa,
	a.d_emi,
	a.cnpj,
	a.ie,
	b.id_nfe,
	CASE WHEN b.id_nfe IS NULL THEN
		FALSE
	ELSE
		TRUE
	END AS importada
FROM
(
	SELECT 
		a.inf_nfe,
		d.n_nf,
		e.id_empresa,
		d.d_emi,
		b.cpf_cnpj AS cnpj,
		TRIM(LEADING '0' FROM b.ie) AS ie
	FROM
		nfe.raiz_nfe as a
	JOIN
		nfe.c01_emit as b USING(id_nfe)
	JOIN
		nfe.e01_dest as c USING(id_nfe)
	JOIN
		nfe.b01_ide as d USING(id_nfe)
	JOIN
		ncm_helper.cad_empresas as e ON c.cpf_cnpj = e.cpf_cnpj
	WHERE
		e.id_empresa = 12 AND d.d_emi BETWEEN '2016-01-01' AND '2016-12-31'
		
	UNION
	
	SELECT 
		a.ch_nfe,
		SUBSTR(a.ch_nfe, 28, 7)::BIGINT AS n_nf,
		d.id_empresa,
		b.d_emi,
		b.cnpj,
		TRIM(LEADING '0' FROM b.ie) AS ie
	FROM
		manifesto.res as a
	JOIN
		manifesto.res_nfe as b ON a.id = b.id_res
	JOIN
		manifesto.cons_nfe_dest_ret as c ON a.id_cons_nfe_dest_ret = c.id
	JOIN
		manifesto.cons_nfe_dest as d ON c.id_cons_nfe_dest = d.id
	LEFT JOIN -- chaves canceladas
	(
		SELECT
			b.ch_nfe
		FROM
			manifesto.res_canc as a
		JOIN
			manifesto.res as b ON a.id_res = b.id
	) h USING(ch_nfe)
	WHERE
		h.ch_nfe IS NULL AND d.id_empresa = 12 AND b.d_emi BETWEEN '2016-01-01' AND '2016-12-31'
) a
LEFT JOIN
	nfe.raiz_nfe as b USING(inf_nfe)
ORDER BY
	a.d_emi;