-- INÍCIO [douglas.japiassu] 04.01.2017

CREATE OR REPLACE TYPE o_permissoes_fluxo AS OBJECT (idtipofluxo number, criar char(1), executar char(1), delegar char(1), suspender char(1), reativar char(1), alterarsla char(1), reabrir char(1), cancelar char(1));

CREATE OR REPLACE TYPE t_permissoes_fluxo AS TABLE OF o_permissoes_fluxo;

CREATE OR REPLACE FUNCTION f_permissao_fluxo(p_idusuario number, p_idfluxo number) RETURN t_permissoes_fluxo PIPELINED IS record_obj o_permissoes_fluxo; 
	TYPE ref_cursor_permissoes IS REF CURSOR; 
	cursor_permissoes ref_cursor_permissoes; 
	TYPE t_registro is record (idtipofluxo number, criar char(1), executar char(1), delegar char(1), suspender char(1), reativar char(1),  alterarsla char(1), reabrir char(1), cancelar char(1)); 
	permissoes t_registro; 
BEGIN 
	
	open cursor_permissoes for select pf.idtipofluxo, pf.criar, pf.executar, pf.delegar, pf.suspender, pf.reativar, pf.alterarsla, pf.reabrir, pf.cancelar from bpm_fluxo f inner join permissoesfluxo pf on pf.idtipofluxo = f.idtipofluxo inner join gruposempregados ge on ge.idgrupo = pf.idgrupo inner join usuario u on u.idempregado = ge.idempregado where u.idusuario = p_idusuario and f.idfluxo = p_idfluxo and f.datafim is null; 

	record_obj := o_permissoes_fluxo(null, 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N'); 

	LOOP 
		FETCH cursor_permissoes INTO permissoes; 
		EXIT WHEN cursor_permissoes%NOTFOUND; 
	record_obj.idtipofluxo := permissoes.idtipofluxo; 
	IF upper(permissoes.criar) = upper('S') THEN record_obj.criar := 'S'; END IF; 
		if upper(permissoes.executar) = upper('S') then record_obj.executar := 'S'; end if; 
		if upper(permissoes.delegar) = upper('S') then record_obj.delegar := 'S'; end if; 
		if upper(permissoes.suspender) = upper('S') then record_obj.suspender := 'S'; end if; 
		if upper(permissoes.reativar) = upper('S') then record_obj.reativar := 'S'; end if; 
		if upper(permissoes.alterarsla) = upper('S') then record_obj.alterarsla := 'S'; end if; 
		if upper(permissoes.reabrir) = upper('S') then record_obj.reabrir := 'S'; end if; 
		if upper(permissoes.cancelar) = upper('S') then record_obj.cancelar := 'S'; end if; 
	
	END LOOP; 
	CLOSE cursor_permissoes; 
	PIPE ROW(record_obj); 
	RETURN; 
END;;

update portfolioservico set  imagemportal = REGEXP_REPLACE(imagemportal, '.+?(?\/imagens/portal)', '/imagens/portal');
update servico set  imagemportal = REGEXP_REPLACE(imagemportal, '.+?(?\/imagens/portal)', '/imagens/portal');

-- FIM [douglas.japiassu] 04.01.2017