WITH RECURSIVE tmp_pagamento_titulos(
    id_titulo_raiz, --ID PARA FAZER O RELACIONAMENTO MASTER DETAIL
    id_titulo_financeiro, 
    id_titulo_filho, 
    id_titulo_pai, 
	id_natureza_titulo,
	natureza_titulo,
	data_emissao,
	data_vencimento,
	numero_titulo,
    valor_titulo, 
    situacao, 
    data_liquidacao, 
    id_conta_liquidacao,
    juros_cobranca, 
    multa_cobranca, 
    desconto_cobranca, 
    retencao_tributaria, 
    valor_liquidado, 
    valor_ajuste,
    valor_recebido,
    valor_amortizado,
    valor_residual,
    valor_titulo_recalculado,
    valor_amortizado_recalculado,
    valor_residual_recalculado) AS 
    (
        SELECT  
            tf.id_titulo_financeiro AS id_titulo_raiz,
            tf.id_titulo_financeiro,
            tf.id_titulo_filho,
            tf.id_titulo_pai,  
			tf.natureza_titulo as id_natureza_titulo,
			ovl1.titulo AS natureza_titulo,
			tf.data_emissao,
			tf.data_vencimento,
			tf.numero_titulo,
            tf.valor_titulo,
            tf.situacao,
            tf.data_liquidacao,
            tf.id_conta_liquidacao,
            tf.juros_cobranca,
            tf.multa_cobranca,
            tf.desconto_cobranca,
            tf.retencao_tributaria,
            tf.valor_liquidado,
            (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria) AS valor_ajuste,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_liquidado ELSE 0 END) AS DECIMAL(12,2)) AS valor_recebido,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria) ELSE 0 END) AS DECIMAL(12,2)) AS valor_amortizado,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_titulo - (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) ELSE tf.valor_titulo END) AS DECIMAL(12,2)) AS valor_residual,
            tf.valor_titulo AS valor_titulo_recalculado,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria) ELSE 0.00 END) AS DECIMAL(12,2)) AS valor_amortizado_recalculado,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_titulo - (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) ELSE tf.valor_titulo END) AS DECIMAL(12,2)) AS valor_residual_recalculado
        FROM  titulo_financeiro tf
			INNER JOIN sis_opcao_lista_valor AS ovl1 ON (ovl1.id = tf.natureza_titulo AND ovl1.id_lista_valor = 'C043EFC0EDD5414995003170978F8878')
        WHERE tf.tipo_titulo = 3 AND tf.id_empresa = :IdEmpresaObrigatorio AND tf.data_emissao BETWEEN :DataInicialEmissao AND :DataFinalEmissao
		AND	(:IdCredorDevedorNaoObrigatorio = -1 OR tf.id_credor_devedor = :IdCredorDevedorNaoObrigatorio) -- INFORMAR NESTE CAMPO A CONDIÇÃO DO SQL

        UNION ALL

        SELECT  
            t.id_titulo_raiz, --ID PARA FAZER O RELACIONAMENTO MASTER DETAIL
            tf.id_titulo_financeiro,
            tf.id_titulo_filho,
            tf.id_titulo_pai,
			tf.natureza_titulo as id_natureza_titulo,
			ovl1.titulo AS natureza_titulo,	
			tf.data_emissao,
			tf.data_vencimento,
			tf.numero_titulo,
            tf.valor_titulo,
            tf.situacao,
            tf.data_liquidacao,
            tf.id_conta_liquidacao,
            tf.juros_cobranca,
            tf.multa_cobranca,
            tf.desconto_cobranca,
            tf.retencao_tributaria,
            tf.valor_liquidado, --tf.situacao = 2, 3 - Liquidado Parcialmente, 8 - Desmembrado 
            (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria) AS valor_ajuste,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_liquidado ELSE 0.00 END) AS DECIMAL(12,2)) AS valor_recebido,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria) ELSE 0.00 END) AS DECIMAL(12,2)) AS valor_amortizado,
            CAST((CASE WHEN tf.situacao IN (2, 3) THEN tf.valor_titulo - (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) ELSE tf.valor_titulo END) AS DECIMAL(12,2)) AS valor_residual,
            CAST(
                (CASE WHEN t.situacao = 8 
                THEN COALESCE((tf.valor_titulo * t.valor_residual_recalculado) / NULLIF(t.valor_residual, 0), (tf.valor_titulo * t.valor_residual_recalculado)) 
                ELSE t.valor_residual_recalculado END) AS DECIMAL(12,2)
            ) AS valor_titulo_recalculado,
            CAST(
                (CASE WHEN tf.situacao IN (2, 3) 
                THEN (CASE WHEN t.situacao = 8 
                    THEN COALESCE(((tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) * (t.valor_residual_recalculado / NULLIF(t.valor_residual, 0))), (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria))) 
                    ELSE COALESCE((((tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) / NULLIF(tf.valor_titulo, 0)) * t.valor_residual_recalculado), (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria))) END) 
                ELSE 0.00 END) AS DECIMAL(12,2)
            ) AS valor_amortizado_recalculado,
            CAST(
                (CASE WHEN tf.situacao IN (2, 3) 
                THEN (CASE WHEN t.situacao = 8 
                    THEN COALESCE(((tf.valor_titulo - (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria))) * (t.valor_residual_recalculado / NULLIF(t.valor_residual, 0))), (tf.valor_titulo - (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria))))
                    ELSE (t.valor_residual_recalculado - COALESCE((((tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)) / NULLIF(tf.valor_titulo, 0)) * t.valor_residual_recalculado), (tf.valor_liquidado - (tf.juros_cobranca + tf.multa_cobranca - tf.desconto_cobranca - tf.retencao_tributaria)))) END)
                ELSE (CASE WHEN t.situacao = 8 
                    THEN COALESCE((tf.valor_titulo * t.valor_residual_recalculado) / NULLIF(t.valor_residual, 0), (tf.valor_titulo * t.valor_residual_recalculado)) 
                    ELSE t.valor_residual_recalculado END) END) AS DECIMAL(12,2)
            ) AS valor_residual_recalculado

        FROM  titulo_financeiro as tf
		INNER JOIN sis_opcao_lista_valor AS ovl1 ON (ovl1.id = tf.natureza_titulo AND ovl1.id_lista_valor = 'C043EFC0EDD5414995003170978F8878'),
        tmp_pagamento_titulos as t
		
        WHERE (tf.id_titulo_financeiro = t.id_titulo_filho OR tf.id_titulo_pai = t.id_titulo_financeiro)
 ), 

valores as
(		SELECT id_titulo_raiz,		
		sum(juros_cobranca + multa_cobranca - desconto_cobranca) as acresc_desc,
		sum(valor_liquidado) as valor_liquidado	
			

	FROM tmp_pagamento_titulos where 
	id_titulo_raiz IN (SELECT id_titulo_financeiro
		FROM	titulo_financeiro AS tf
		WHERE	id_empresa = :IdEmpresaObrigatorio
		AND	data_emissao BETWEEN :DataInicialEmissao AND :DataFinalEmissao
		AND	(:IdCredorDevedorNaoObrigatorio = -1 OR tf.id_credor_devedor = :IdCredorDevedorNaoObrigatorio)		
		AND	tf.natureza_titulo IN (201, 209, 213, 220) -- Venda a Prazo, Receita, Vale Frete e Vale Motorista
		AND	situacao IN (1, 2, 3, 10) -- Aberto, Liquidado, Liquidado Parcialmente, Liquidado por Troca de Título
		AND	tipo_titulo = 3) -- Membramento
		
	group by 1
)

-- Emissão de Fatura por Período
-- Fatura
SELECT	
	tf.id_titulo_financeiro,	
	se.nome AS nome_empresa,
	tf.numero_titulo,	
	
	(CASE WHEN (select tpt.situacao from tmp_pagamento_titulos as tpt where tpt.situacao  = 2 and tpt.id_titulo_raiz = tf.id_titulo_financeiro limit 1) = 2
		THEN 2
		ELSE tf.situacao
		END
	)  AS situacao,
	
	(CASE WHEN (select tpt.situacao from tmp_pagamento_titulos as tpt where tpt.situacao  = 2 and tpt.id_titulo_raiz = tf.id_titulo_financeiro limit 1) = 2
		THEN 'Liquidado'
		ELSE solv.titulo
		END
	)  AS situacao_titulo,
	
	v.acresc_desc,
	v.valor_liquidado,
	
	--tf.situacao as situacao,
	--solv.titulo AS situacao_titulo,
	p.id_pessoa,
	p.nome AS nome_pessoa,
	p.codigo,
	p.natureza,
	ep.logradouro,
	ep.complemento,
	ep.numero,
	ep.cep,
	ep.bairro,
	ep.telefone,
	m.nome AS nome_municipio,
	CASE WHEN (p.natureza = 1) THEN
		SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-', ''), 11, '0'), 1, 3) || '.' || SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-', ''), 11, '0'), 4, 3) || '.' ||
		SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-', ''), 11, '0'), 7, 3) || '-' || SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-', ''), 11, '0'), 10)
	WHEN (p.natureza = 2) THEN
		SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-/', ''), 14, '0'), 1, 2) || '.' || SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-/', ''), 14, '0'), 3, 3) || '.' || SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-/', ''), 14, '0'), 6, 3) || '/' ||
		SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-/', ''), 14, '0'), 9, 4) || '-' || SUBSTRING(LPAD(TRANSLATE(p.cnpj_cpf, '.-/', ''), 14, '0'), 13) END AS cnpj_cpf,
	p.inscricao_estadual,
	tf.data_emissao,
	
	(CASE WHEN tf.situacao = 3
		THEN (select tpt.data_vencimento from tmp_pagamento_titulos as tpt where tpt.situacao = 1 and tpt.id_titulo_raiz = tf.id_titulo_financeiro order by tpt.data_vencimento limit 1)
		ELSE tf.data_vencimento
		END
	) as data_vencimento,
	
	--tf.data_vencimento,
	tf.valor_titulo AS valor_titulo,
	mu.nome AS municipio_empresa,
	uf.sigla AS uf_empresa,
	pes.natureza AS natureza_empresa,
	CASE WHEN (pes.natureza = 1) THEN
		SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-', ''), 11, '0'), 1, 3) || '.' || SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-', ''), 11, '0'), 4, 3) || '.' ||
		SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-', ''), 11, '0'), 7, 3) || '-' || SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-', ''), 11, '0'), 10)
	WHEN (pes.natureza = 2) THEN
		SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-/', ''), 14, '0'), 1, 2) || '.' || SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-/', ''), 14, '0'), 3, 3) || '.' || SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-/', ''), 14, '0'), 6, 3) || '/' ||
		SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-/', ''), 14, '0'), 9, 4) || '-' || SUBSTRING(LPAD(TRANSLATE(pes.cnpj_cpf, '.-/', ''), 14, '0'), 13) END AS cnpj_empresa,
	pes.inscricao_estadual AS ie_empresa,
	epe.logradouro AS logradouro_empresa,
	epe.complemento AS complemento_empresa,
	epe.numero AS numero_empresa,
	epe.cep AS cep_empresa,
	epe.bairro AS bairro_empresa,
	epe.telefone AS telefone_empresa,
	su.nome AS usuario_lancamento,
	pes.opcional_1 AS rodape_1,
	pes.opcional_2 AS rodape_2
FROM	titulo_financeiro AS tf
	INNER JOIN valores AS v ON (v.id_titulo_raiz = tf.id_titulo_financeiro)
	INNER JOIN pessoa AS p ON (p.id_pessoa = tf.id_credor_devedor)
	INNER JOIN sis_empresa AS se ON (se.id_empresa = tf.id_empresa)
	INNER JOIN pessoa AS pes ON (pes.id_pessoa = (SELECT F_OBTEM_PARAMETRO_INTEIRO('sistema.id_pessoa_empresa', tf.id_empresa, 0, 'S')))
	INNER JOIN municipio AS mu ON (mu.id_municipio = pes.id_municipio)
	INNER JOIN uf AS uf ON (uf.id_uf = mu.id_uf)
	LEFT OUTER JOIN endereco_pessoa AS ep ON (ep.id_pessoa = p.id_pessoa AND ep.id_tipo_endereco = 1 AND ep.registro_ativo = 'S')
	LEFT OUTER JOIN municipio AS m ON (m.id_municipio = ep.id_municipio)
	LEFT OUTER JOIN sis_usuario AS su ON (su.id_usuario = tf.id_usuario_lancamento)
	LEFT OUTER JOIN endereco_pessoa AS epe ON (epe.id_pessoa = pes.id_pessoa AND epe.id_tipo_endereco = 1 AND epe.registro_ativo = 'S')
	LEFT OUTER JOIN sis_opcao_lista_valor solv ON (solv.id = tf.situacao AND solv.id_lista_valor = 'CEDBEA3BCD98445F98C29C5DAC4B809D')
WHERE	tf.id_empresa = :IdEmpresaObrigatorio
AND	tf.data_emissao BETWEEN :DataInicialEmissao AND :DataFinalEmissao
AND	(:IdCredorDevedorNaoObrigatorio = -1 OR tf.id_credor_devedor = :IdCredorDevedorNaoObrigatorio)
AND	tf.natureza_titulo IN (201, 209, 213, 220)
AND	tf.situacao IN (1, 2, 3, 10)
AND	tf.tipo_titulo = 3


		
