-- drop function if exists par.get_ciencias_termo(int, int);
create or replace function par.get_ciencias_termo(
		i_prpid int,
		i_dopid int
	)
	
	returns table (
		_cpf varchar,
		_id integer,
		_tipo varchar,
		_data_notificado date,
		_data_ciencia date
	)
	
	language plpgsql
	as $$
	declare 
		
		ciencias record;
		
	BEGIN		    				

		for ciencias in( 
	
		with cpf_ciencias as(	
									select opc.opcid as id, usu.usucpf, 'omissaopcente' as tipo, opc.opcdataomissao as data_notificado, opc.opcdataciencia as data_ciencia 
									FROM par.omissaopcente opc
									inner JOIN seguranca.usuario usu on usu.usucpf = opc.opccpfciencia 
									WHERE opc.prpid = i_prpid and opc.opcstatus = 'A' -- and usu.usustatus = 'A' NÃO COLOCAR PQ REMOVE CIENCIA/RESPOSNAVEL
							
									union all		
									
									select neg.negid as id, usu.usucpf, 'notificacaoexgestor' as tipo, neg.negdtenvionotificacao as data_notificado, neg.negdtaceiterealizado as data_ciencia 
									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 dopid = i_dopid and neg.negstatus = 'A' and neg.negdtaceiterealizado is not null and eg.egstatus = 'A' -- and usu.usustatus = 'A' NÃO COLOCAR PQ REMOVE CIENCIA/RESPONSAVEL
																
									union all 
									
									select np.npid as id, usu.usucpf, 'notificacao_postal' as tipo, np.npdtgeracao as data_notificado, np.npcienc_dt_receb as data_ciencia
									from par.notificacao_postal np
									inner join seguranca.usuario usu on usu.usucpf = np.npresptermocpf  -- and usu.usustatus = 'A' NÃO COLOCAR PQ REMOVE CIENCIA/RESPONSAVEL
									where np.dopid = i_dopid and np.npcienc_dt_receb is not null and np.npstatus = 'A'   

									) select * from cpf_ciencias order by usucpf desc
								)
		loop
			_id := ciencias.id;
			_cpf := ciencias.usucpf;
			_tipo := ciencias.tipo;
			_data_notificado := ciencias.data_notificado;
			_data_ciencia := ciencias.data_ciencia;
			
			return next;	
		end loop;
	
	end; $$