TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
internalid	integer	IN
descricao	character varying	IN
datainiciovigencia	character varying	IN
datafimvigencia	character varying	IN
idnativo	integer	IN
externalid	character varying	IN

Definition

begin	 
	-- VALIDACAO	
		-- EXTERNALID E INTERNALID NAO INFORMADOS
		if coalesce(PutTabelaPreco.externalid,'')='' and PutTabelaPreco.internalid is null then 
		RAISE EXCEPTION 'externalid = % and internalid = %', PutTabelaPreco.externalid, PutTabelaPreco.internalid
			USING HINT = 'Fields internalid and externalid must be not null';
		end if;

--IDNATIVO FORA DO PADRAO 
		if idnativo not in (0,1,-99999) then 
			RAISE EXCEPTION 'idnativo = %', idnativo
				USING HINT = 'Field must not be null or different from 1 or 0.';
		end if;  
		
		--DESCRICAO NULA
		if descricao is null then 
			RAISE EXCEPTION 'descricao = %', descricao
				USING HINT = 'Field descricao must be not null';
		end if;

-- LOOKUP
		-- TABELA PRECO
		IF PutTabelaPreco.internalid IS NULL 
		THEN PutTabelaPreco.internalid = (SELECT IDTABELAPRECO FROM TABELAPRECO WHERE CODIGOERP = PutTabelaPreco.externalid);
		END IF;

-- UPDATE
	update tabelapreco set 
		descricao = case when PutTabelaPreco.descricao = 'NOVALUEWS' then tabelapreco.descricao else PutTabelaPreco.descricao end, 
		datainiciovigencia = case when PutTabelaPreco.datainiciovigencia = 'NOVALUEWS' then tabelapreco.datainiciovigencia else TO_DATE(PutTabelaPreco.datainiciovigencia,'YYYY-MM-DD') end, 
		datafimvigencia = case when PutTabelaPreco.datafimvigencia = 'NOVALUEWS' then tabelapreco.datafimvigencia else TO_DATE(PutTabelaPreco.datafimvigencia,'YYYY-MM-DD') end, 
		idnativo = case when PutTabelaPreco.idnativo = -99999 then tabelapreco.idnativo else PutTabelaPreco.idnativo end,
		codigoerp = case when PutTabelaPreco.externalid = 'NOVALUEWS' then codigoerp else PutTabelaPreco.externalid end
	where idtabelapreco = internalid ;

return query select internalid;
END;

puttabelapreco(integer, character varying, character varying, character varying, integer, character varying)

Parameters

Definition