-- DROP FUNCTION IF EXISTS par.get_gestor_ciencia_par(VARCHAR, VARCHAR, INT, INT)

CREATE OR REPLACE FUNCTION par.get_gestor_ciencia_par(i_responsavel_cpf VARCHAR, i_processo VARCHAR, i_dopid INT, i_prp_id INT)
RETURNS TABLE (usucpf VARCHAR) AS $$
BEGIN
    RETURN QUERY 
        SELECT np.npresptermocpf AS usucpf
        FROM par.notificacao_postal np
        WHERE np.npstatus = 'A'
          AND np.npcienc_dt_receb IS NOT NULL
          AND np.npresptermocpf = i_responsavel_cpf
          AND np.processo = i_processo
		
		union all
		select  usu.usucpf  
		from par.exgestor eg
		inner join par.notificacaoexgestor neg on neg.egid = eg.egid 
		inner join seguranca.usuario usu on usu.usucpf = eg.usucpf
		where eg.dopid = i_dopid and neg.negdtaceiterealizado is not null and eg.egstatus = 'A' and neg.negstatus = 'A' 
          
        UNION ALL
        
        SELECT opc.opccpfciencia AS usucpf
        FROM par.omissaopcente opc
        WHERE opc.opcciencia = 'true'
          AND opc.opcstatus = 'A'
          AND opc.opccpfciencia = i_responsavel_cpf
          AND opc.prpid = i_prp_id;
END;
$$ LANGUAGE plpgsql;