TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
internalid	integer	IN
idclientelocal	integer	IN
idclientelocal_externalid	character varying	IN
idtabelapreco	integer	IN
idtabelapreco_externalid	character varying	IN
idnpadrao	integer	IN
externalid	character varying	IN

Definition

declare	
	idparceiro_ integer;
begin	 
	-- VALIDACAO	
		-- EXTERNALID E INTERNALID NAO INFORMADOS
		if coalesce(PutLocalTabelaPreco.externalid,'')='' and PutLocalTabelaPreco.internalid is null then 
		RAISE EXCEPTION 'externalid = % and internalid = %', PutLocalTabelaPreco.externalid, PutLocalTabelaPreco.internalid
			USING HINT = 'Fields internalid and externalid must be not null';
		end if; 	 

		--IDNPADRAO NULO OU FORA DO PADRAO 
		if idnpadrao not in (0,1,-99999) then 
			RAISE EXCEPTION 'idnpadrao = %', idnpadrao
				USING HINT = 'Field must not be null or different from 1 or 0.';
		end if; 

	-- LOOKUP
		-- LOCALTABELAPRECO
		IF PutLocalTabelaPreco.internalid IS NULL 
		THEN PutLocalTabelaPreco.internalid = (SELECT LOCALTABELAPRECO.IDLOCALTABELAPRECO FROM LOCALTABELAPRECO WHERE LOCALTABELAPRECO.CODIGOERP = PutLocalTabelaPreco.externalid);
		END IF;

		-- CLIENTELOCAL
		IF PutLocalTabelaPreco.idclientelocal IS NULL AND PutLocalTabelaPreco.idclientelocal_externalid IS NOT NULL
			THEN PutLocalTabelaPreco.idclientelocal = (SELECT C2.idlocal FROM local C2 WHERE C2.CODIGOERP = PutLocalTabelaPreco.idclientelocal_externalid);
		END IF;	

		if PutLocalTabelaPreco.idclientelocal is null then 
			RAISE EXCEPTION 'idclientelocal = %', PutLocalTabelaPreco.idclientelocal
				USING HINT = 'Field idclientelocal must be not null';
		end if;
			-- CLIENTE
			idparceiro_ = (SELECT distinct C2.idparceiro FROM parceirolocal C2 WHERE C2.idlocal = PutLocalTabelaPreco.idclientelocal);	 
			
		-- TABELAPRECO
		IF PutLocalTabelaPreco.idtabelapreco IS NULL AND PutLocalTabelaPreco.idtabelapreco_externalid IS NOT NULL
			THEN PutLocalTabelaPreco.idtabelapreco = (SELECT C2.IDTABELAPRECO FROM TABELAPRECO C2 WHERE C2.CODIGOERP = PutLocalTabelaPreco.idtabelapreco_externalid);
		END IF;	

		if PutLocalTabelaPreco.idtabelapreco is null then 
			RAISE EXCEPTION 'idtabelapreco = %', PutLocalTabelaPreco.idtabelapreco
				USING HINT = 'Field idtabelapreco must be not null';
		end if;
		
	-- UPDATE	
	update localtabelapreco set 
			idparceiro = case when PutLocalTabelaPreco.idclientelocal = -99999 then localtabelapreco.idparceiro else idparceiro_ end, 
			idlocal = case when PutLocalTabelaPreco.idclientelocal = -99999 then localtabelapreco.idlocal else PutLocalTabelaPreco.idclientelocal end, 
			idtabelapreco = case when PutLocalTabelaPreco.idtabelapreco = -99999 then localtabelapreco.idtabelapreco else PutLocalTabelaPreco.idtabelapreco end, 
			idnpadrao = case when PutLocalTabelaPreco.idnpadrao = -99999 then localtabelapreco.idnpadrao else PutLocalTabelaPreco.idnpadrao end,
		codigoerp = case when PutLocalTabelaPreco.externalid = 'NOVALUEWS' then codigoerp else PutLocalTabelaPreco.externalid end
		where idlocaltabelapreco = internalid ;

	return query select internalid;
END;

putlocaltabelapreco(integer, integer, character varying, integer, character varying, integer, character varying)

Parameters

Definition