-- DROP function if exists par.get_ciencias_termo_obra(int, int);

create or replace function par.get_ciencias_termo_obra(
		i_proid_par 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.proid_par = i_proid_par 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 distinct usucpf, id, tipo, data_notificado, data_ciencia 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; $$ 
