SELECT
	bp_localtrab.pes_fantasia,
    rf.fun_matricula,
    bp_funcionario.pes_nome,
    CASE
    	WHEN plj.lanjus_ate_dia IS NULL
        THEN
        	CAST(plj.lanjus_do_dia AS TEXT)
        ELSE
        	plj.lanjus_do_dia || ' - ' || plj.lanjus_ate_dia
    END AS data_homologacao,
	pma.motaus_descricao,
    lpad(CAST(get_mes(cp.comp_data) AS VARCHAR), 2, '0') || '/' || get_ano(cp.comp_data) AS competencia

FROM pto_lan_justificativa plj

INNER JOIN pto_lan_justificativa_status pljs
	ON plj.lanjus_cod = pljs.lanjusstat_lanjuscod
    	AND pljs.lanjusstat_ativo = 1
        AND pljs.lanjusstat_status = 2

INNER JOIN rh_funcionario rf
	ON plj.lanjus_funcod = rf.fun_cod

INNER JOIN pto_competencia cp
	ON
		(
		  plj.lanjus_ate_dia IS NULL
		  AND plj.lanjus_do_dia BETWEEN cp.comp_data_inicio AND cp.comp_data_fim
		)
		OR
		(
		  plj.lanjus_ate_dia IS NOT NULL
		  AND plj.lanjus_do_dia >= cp.comp_data_inicio
		  AND plj.lanjus_do_dia <= cp.comp_data_fim
		)

INNER JOIN bas_pessoa bp_funcionario
	ON rf.fun_pescod = bp_funcionario.pes_cod

INNER JOIN bas_local_trabalho blt
	ON plj.lanjus_lctrabcod = blt.lctrab_cod

INNER JOIN bas_pessoa bp_localtrab
	ON blt.lctrab_pescod = bp_localtrab.pes_cod

INNER JOIN pto_motivo_ausencia pma
	ON plj.lanjus_motauscod = pma.motaus_cod

ORDER BY
	bp_localtrab.pes_fantasia,
	bp_funcionario.pes_nome