WITH detalhamento AS 
(

SELECT 	
	pe.nome AS nome_empresa,
	pe.inscricao_estadual AS inscricao_estadual,
	REGEXP_REPLACE(LPAD(pe.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as cnpj,
	CASE WHEN i.tipo_item = 2 THEN 'SERVIÇO'  
		WHEN (i.id_item IN (SELECT le.id_item_tanque FROM local_estoque AS le WHERE le.id_empresa = nfe.id_empresa AND le.tipo = 2)) THEN 'COMBUSTÍVEL'
		ELSE 'PRODUTO' END AS tipo_item,
	lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
	'NF'	AS esp,
	nfe.serie AS ser_sub,
	nfe.numero AS numero,
	nfe.entrada AS datadoc,
	REGEXP_REPLACE(LPAD(forn.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as codigo_emitente,
	uf.sigla AS uf_orig,
	SUM(infe.total_produto + infe.icms_substituicao + infe.rateio_seguro + infe.rateio_despesa_acessoria + infe.ipi  + infe.fcp_st_retido - (CASE WHEN infe.deduzir_icms_desoneracao = 'S' THEN infe.icms_desoneracao ELSE 0 END)) AS valor_contabil,
	cfop.cfop AS cfop,
	SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.base_icms - infe.base_icms_reduzida) ELSE 0 END) AS isenta_naotributada,
	SUM(infe.ipi) AS ipi,
	SUM(CASE WHEN infe.base_icms > 0 THEN infe.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
	(CASE WHEN infe.base_icms > 0  THEN infe.aliquota_icms ELSE 0 END)	AS aliquota_icms,
	SUM(CASE WHEN infe.base_icms > 0 THEN infe.icms ELSE 0 END) AS imposto_creditado,
	SUM(CASE WHEN infe.base_icms > 0 THEN (infe.total_produto + rateio_seguro + rateio_despesa_acessoria - infe.base_icms_reduzida) ELSE (infe.total_produto + rateio_seguro + rateio_despesa_acessoria) END) AS outras,
	SUM(infe.icms_substituicao) AS icms_substituicao,
	SUM(infe.base_icms_substituicao) AS base_icms_substituicao,
	
	SUM(0) as rateio_frete,
	SUM(infe.rateio_seguro) as rateio_seguro,
	SUM(infe.rateio_despesa_acessoria) AS rateio_despesa_acessoria
	
FROM nota_fiscal_entrada AS nfe
	JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
	JOIN pessoa AS forn ON (nfe.id_fornecedor=forn.id_pessoa)
	JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
	JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
	JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=nfe.id_modelo_documento_fiscal)
	JOIN item_nfe AS infe ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
	JOIN item AS i ON (infe.id_item = i.id_item)
	JOIN cfop AS cfop ON (cfop.id_cfop=infe.id_cfop)
WHERE nfe.id_nota_fiscal_entrada IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
	AND nfe.id_empresa = :IdEmpresaObrigatorio
	--AND	(0 = 0 OR i.tipo_item = 0)
	--AND CAST(EXTRACT('MONTH' FROM DATE(nfe.entrada)) AS INTEGER) = 3
	AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
	AND nfe.situacao=1
	
GROUP BY
	1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 17

UNION ALL


SELECT 	pe.nome AS nome_empresa,
	pe.inscricao_estadual AS inscricao_estadual,
	REGEXP_REPLACE(LPAD(pe.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as cnpj,
	CASE WHEN i.tipo_item = 2 THEN 'SERVIÇO'  
		WHEN (i.id_item IN (SELECT le.id_item_tanque FROM local_estoque AS le WHERE le.id_empresa = nfe.id_empresa AND le.tipo = 2)) THEN 'COMBUSTÍVEL'
		ELSE 'PRODUTO' END AS tipo_item,
	lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
	'NF'	AS esp,
	nfe.serie AS ser_sub,
	nfe.numero AS numero,
	nfe.entrada AS datadoc,
	REGEXP_REPLACE(LPAD(forn.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as codigo_emitente,
	uf.sigla AS uf_orig,
	SUM(infe.total_produto + infe.icms_substituicao + infe.rateio_frete + infe.rateio_seguro + infe.rateio_despesa_acessoria + infe.ipi  + infe.fcp_st_retido - (CASE WHEN infe.deduzir_icms_desoneracao = 'S' THEN infe.icms_desoneracao ELSE 0 END)) AS valor_contabil,
	cfop.cfop AS cfop,
	SUM(CASE WHEN infe.base_icms > 0 and infe.base_icms_substituicao = 0 THEN (infe.base_icms - infe.base_icms_reduzida) ELSE 0 END) AS isenta_naotributada,
	SUM(infe.ipi) AS ipi,
	SUM(CASE WHEN infe.base_icms > 0  THEN infe.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
	(CASE WHEN infe.base_icms > 0  THEN infe.aliquota_icms ELSE 0 END)	AS aliquota_icms,
	SUM(CASE WHEN infe.base_icms > 0 THEN infe.icms ELSE 0 END) AS imposto_creditado,
	SUM(CASE WHEN infe.base_icms > 0 THEN (infe.total_produto + rateio_seguro + rateio_despesa_acessoria + rateio_frete - infe.base_icms_reduzida) ELSE (infe.total_produto + rateio_seguro + rateio_despesa_acessoria + rateio_frete) END) AS outras,
	SUM(infe.icms_substituicao) AS icms_substituicao,
	SUM(infe.base_icms_substituicao) AS base_icms_substituicao,
	
	Sum(infe.rateio_frete) as rateio_frete,
	SUM(infe.rateio_seguro) as rateio_seguro,
	SUM(infe.rateio_despesa_acessoria) AS rateio_despesa_acessoria
	
	
FROM nota_fiscal_entrada AS nfe
	JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
	JOIN pessoa AS forn	ON (nfe.id_fornecedor=forn.id_pessoa)
	JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
	JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
	JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=nfe.id_modelo_documento_fiscal)
	JOIN item_nfe AS infe ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
	JOIN item AS i ON (infe.id_item = i.id_item)
	JOIN cfop AS cfop ON (cfop.id_cfop=infe.id_cfop)
WHERE nfe.id_nota_fiscal_entrada NOT IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
	AND nfe.id_empresa = :IdEmpresaObrigatorio
	--AND	(0 = 0 OR i.tipo_item = 0)
	--AND CAST(EXTRACT('MONTH' FROM DATE(nfe.entrada)) AS INTEGER) = 3
	AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
	AND nfe.situacao=1
	
GROUP BY
	1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 17
							
UNION ALL


SELECT 	pe.nome AS nome_empresa,
	pe.inscricao_estadual AS inscricao_estadual,
	REGEXP_REPLACE(LPAD(pe.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as cnpj,
	'CONHECIMENTO DE FRETE' AS tipo_item, 
	lpad(CAST(EXTRACT (DAY FROM nfe.entrada) AS VARCHAR), 2, '0')	AS dia,
	'CT'	AS esp,
	nfe.serie AS ser_sub,
	CAST(ct.numero AS INTEGER) AS numero,
	nfe.entrada AS datadoc,
	REGEXP_REPLACE(LPAD(forn.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as codigo_emitente,
	uf.sigla AS uf_orig,
	(ct.valor_frete) AS valor_contabil,
	cfop.cfop AS cfop,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN (ct.base_icms - ct.base_icms_reduzida) 
			 WHEN ct.id_cst in (4, 5) AND uf.sigla=ufe.sigla THEN (ct.valor_frete)
			 ELSE 0 END) AS isenta_naotributada,
	CAST(0 AS NUMERIC) AS ipi,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.aliquota ELSE 0 END)	AS aliquota_icms,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.icms ELSE 0 END) AS imposto_creditado,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN 0 
			 WHEN ct.id_cst in (4, 5)  AND uf.sigla=ufe.sigla THEN 0
			 ELSE (valor_frete - ct.base_icms_reduzida) END) AS outras,
	CAST(0 AS NUMERIC) AS icms_substituicao,
	CAST(0 AS NUMERIC) AS base_icms_substituicao,
	
	CAST(0 AS NUMERIC) as rateio_frete,
	CAST(0 AS NUMERIC) as rateio_seguro,
	CAST(0 AS NUMERIC) AS rateio_despesa_acessoria
	
	
FROM nota_fiscal_entrada AS nfe
	JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', nfe.id_empresa, 0, 'S')))
	JOIN municipio AS mune ON (mune.id_municipio=pe.id_municipio)
	JOIN uf AS ufe ON (ufe.id_uf=mune.id_uf)
	JOIN item_nfe AS infe ON (nfe.id_nota_fiscal_entrada=infe.id_nota_fiscal_entrada)
	JOIN sis_referencia_registro AS srr	ON (srr.id_chave_registro_b=infe.id_nota_fiscal_entrada)
	JOIN conhecimento_transporte AS ct	ON (ct.id_conhecimento_transporte=srr.id_chave_registro_a AND ct.entrada_saida = 1)
	JOIN cfop AS cfop ON (cfop.id_cfop=ct.id_cfop)
	JOIN pessoa AS forn	ON (ct.id_transportadora=forn.id_pessoa)
	JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
	JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
	JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=ct.id_modelo_documento_fiscal)
WHERE nfe.id_nota_fiscal_entrada IN (SELECT id_chave_registro_b FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
	AND nfe.id_empresa = :IdEmpresaObrigatorio
	--AND CAST(EXTRACT('MONTH' FROM DATE(nfe.entrada)) AS INTEGER) = 3
	AND CAST(EXTRACT('YEAR' FROM DATE(nfe.entrada)) AS INTEGER) = :Ano
	AND ct.situacao = 1

GROUP BY
	1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22

UNION ALL


SELECT 	pe.nome AS nome_empresa,
	pe.inscricao_estadual AS inscricao_estadual,
	REGEXP_REPLACE(LPAD(pe.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as cnpj,
	'CONHECIMENTO DE FRETE' AS tipo_item, 
	lpad(CAST(EXTRACT (DAY FROM ct.data_emissao) AS VARCHAR), 2, '0')	AS dia,
	'CT'	AS esp,
	mdf.serie AS ser_sub,
	CAST(ct.numero AS INTEGER) AS numero,
	ct.data_emissao AS datadoc,
	REGEXP_REPLACE(LPAD(forn.cnpj_cpf, 14),'([0-9]{2})([0-9]{3})([0-9]{3})([0-9]{4})','\1.\2.\3/\4-') as codigo_emitente,
	uf.sigla AS uf_orig,
	(ct.valor_frete) AS valor_contabil,
	cfop.cfop AS cfop,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN (ct.base_icms - ct.base_icms_reduzida) 
			 WHEN ct.id_cst in (4, 5) AND uf.sigla=ufe.sigla THEN (ct.valor_frete)
			 ELSE 0 END) AS isenta_naotributada,
	CAST(0 AS NUMERIC) AS ipi,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.base_icms_reduzida ELSE 0 END) AS base_calculo_icms,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.aliquota ELSE 0 END)	AS aliquota_icms,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN ct.icms ELSE 0 END) AS imposto_creditado,
	(CASE WHEN ct.base_icms_reduzida > 0 AND ct.id_cst in (1, 3) THEN 0 
		  WHEN ct.id_cst in (4, 5)  AND uf.sigla=ufe.sigla THEN 0
		  ELSE (valor_frete - ct.base_icms_reduzida) END) AS outras,
	CAST(0 AS NUMERIC) AS icms_substituicao,
	CAST(0 AS NUMERIC) AS base_icms_substituicao,
	
	CAST(0 AS NUMERIC) as rateio_frete,
	CAST(0 AS NUMERIC) as rateio_seguro,
	CAST(0 AS NUMERIC) AS rateio_despesa_acessoria
	
FROM conhecimento_transporte AS ct
	JOIN pessoa AS pe ON (pe.id_pessoa=(SELECT f_obtem_parametro_inteiro('sistema.id_pessoa_empresa', ct.id_empresa, 0, 'S')))
	JOIN municipio AS mune ON (mune.id_municipio=pe.id_municipio)
	JOIN uf AS ufe ON (ufe.id_uf=mune.id_uf)
	JOIN cfop AS cfop ON (cfop.id_cfop=ct.id_cfop)
	JOIN pessoa AS forn	ON (ct.id_transportadora=forn.id_pessoa)
	JOIN municipio AS mun ON (mun.id_municipio=forn.id_municipio)
	JOIN uf AS uf ON (uf.id_uf=mun.id_uf)
	JOIN modelo_documento_fiscal AS mdf	ON (mdf.id_modelo_documento_fiscal=ct.id_modelo_documento_fiscal)
WHERE ct.id_conhecimento_transporte NOT IN (SELECT id_chave_registro_a FROM sis_referencia_registro WHERE tipo_referencia ='ref_0425_0414_frete')
	AND ct.id_empresa = :IdEmpresaObrigatorio	
	AND CAST(EXTRACT('YEAR' FROM DATE(ct.data_emissao)) AS INTEGER) = :Ano
	AND ct.situacao = 1
	AND ct.entrada_saida = 1

GROUP BY
	1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22
	
) 

SELECT 

	nome_empresa,
	cnpj,
	inscricao_estadual,
	uf_orig as UF,
	cfop,
	'E' as ES,
	Sum(base_calculo_icms) as base_icms,
	Sum(imposto_creditado) as valor_icms,	
	Sum(base_icms_substituicao) as base_icms_st,
	Sum(icms_substituicao) as valor_icms_st,
	
	
	Sum(ipi) as valor_ipi,	
	Sum(0) as valor_iss,
	Sum(rateio_frete) as valor_frete,	
	Sum(rateio_seguro) as valor_seguro,
	Sum(rateio_despesa_acessoria) as rateio_despesa_acessoria,
	
	
	SUM(CASE WHEN tipo_item = 'SERVIÇO' THEN outras ELSE 0 END) as servico,
	SUM(CASE WHEN tipo_item = 'PRODUTO' THEN outras ELSE 0 END) as produto,
	SUM(CASE WHEN tipo_item = 'COMBUSTÍVEL' THEN outras ELSE 0 END) as combustivel,	
	
	Sum(valor_contabil) as valor_contabil,
	Sum(outras) as total
	
FROM detalhamento

Group by 1, 2, 3, 4, 5, 6
Order by 4, 5
	