TOTVS CRM | SFA - 12.1.2501

Parameters

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

Definition

declare	
	idparceiro_ integer;
begin  		
	-- VALIDACAO	
		-- EXTERNALID E INTERNALID NAO INFORMADOS
		if coalesce(PutLocalEmail.externalid,'')='' and PutLocalEmail.internalid is null then 
		RAISE EXCEPTION 'externalid = % and internalid = %', PutLocalEmail.externalid, PutLocalEmail.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; 
	 
		--EMAIL NULO
		if PutLocalEmail.email is null then 
			RAISE EXCEPTION 'email = %', PutLocalEmail.email
				USING HINT = 'Field email must be not null';
		end if;

-- LOOKUP
		-- LOCALEMAIL
		IF PutLocalEmail.internalid IS NULL 
		THEN PutLocalEmail.internalid = (SELECT LOCALEMAIL.IDLOCALEMAIL FROM LOCALEMAIL WHERE LOCALEMAIL.CODIGOERP = PutLocalEmail.externalid);
		END IF;

--LOOKUP CLIENTELOCAL
		IF PutLocalEmail.idclientelocal IS NULL AND PutLocalEmail.idclientelocal_externalid IS NOT NULL
			THEN PutLocalEmail.idclientelocal = (SELECT C2.idlocal FROM local C2 WHERE C2.CODIGOERP = PutLocalEmail.idclientelocal_externalid);
		END IF;

if PutLocalEmail.idclientelocal is null then 
			RAISE EXCEPTION 'idclientelocal = %', PutLocalEmail.idclientelocal
				USING HINT = 'Field idclientelocal must be not null';
		end if;
	 
			-- CLIENTE
			idparceiro_ = (SELECT distinct C2.idparceiro FROM parceirolocal C2 WHERE C2.idlocal = PutLocalEmail.idclientelocal);

-- UPDATE
	update localemail set 
		idparceiro = case when PutLocalEmail.idclientelocal = -99999 then localemail.idparceiro else idparceiro_ end, 
		idlocal = case when PutLocalEmail.idclientelocal = -99999 then localemail.idlocal else PutLocalEmail.idclientelocal end, 
		email = case when PutLocalEmail.email = 'NOVALUEWS' then localemail.email else PutLocalEmail.email end, 
		idnpadrao = case when PutLocalEmail.idnpadrao = -99999 then localemail.idnpadrao else PutLocalEmail.idnpadrao end,
		codigoerp = case when PutLocalEmail.externalid = 'NOVALUEWS' then codigoerp else PutLocalEmail.externalid end
	where idlocalemail = internalid ;

return query select internalid;
END;

putlocalemail(integer, integer, character varying, character varying, integer, character varying)

Parameters

Definition