TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
idclientelocal	integer	IN
idclientelocal_externalid	character varying	IN
idregiao	integer	IN
idregiao_externalid	character varying	IN
externalid	character varying	IN

Definition

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

--CLIENTELOCAL NULO
	 if PostLocalRegiao.idclientelocal is null then 
		RAISE EXCEPTION 'idclientelocal = %', PostLocalRegiao.idclientelocal
      		USING HINT = 'Field idclientelocal must be not null';
     end if;
	 
	--LOOKUP REGIAO
	IF PostLocalRegiao.idregiao IS NULL AND PostLocalRegiao.idregiao_externalid IS NOT NULL
		THEN PostLocalRegiao.idregiao = (SELECT C2.idregiao FROM REGIAO C2 WHERE C2.CODIGOERP = PostLocalRegiao.idregiao_externalid);
	END IF;

--REGIAO NULO
	 if PostLocalRegiao.idregiao is null then 
		RAISE EXCEPTION 'idregiao = %', PostLocalRegiao.idregiao
      		USING HINT = 'Field idregiao must be not null';
     end if;	 
	 
	 --CODIGOERP NULO
	 if externalid is null or externalid = '' then 
		RAISE EXCEPTION 'externalid = %', externalid
      		USING HINT = 'Field externalid must be not null';
     end if;

--VALIDAÇÃO DE CODIGOERP JÁ EXISTENTE
	IF (select distinct 1 FROM LOCALREGIAO WHERE CODIGOERP = PostLocalRegiao.externalid) = 1
		THEN RAISE EXCEPTION 'externalid = %', PostLocalRegiao.externalid 
				USING HINT = 'The record (externalid = '|| PostLocalRegiao.externalid ||') already exists, it was not possible to insert it.';
		ELSE insert into localregiao (idlocalregiao,idlocal, idregiao, codigoerp) values (nextval('seqpklocalregiao'), PostLocalRegiao.idclientelocal, idregiao, externalid) returning idlocalregiao into internalid;	
	end if;
	
	return query select internalid;
END;

postlocalregiao(integer, character varying, integer, character varying, character varying)

Parameters

Definition