TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
idclientelocal	integer	IN
idclientelocal_externalid	character varying	IN
idtabelapreco	integer	IN
idtabelapreco_externalid	character varying	IN
idnpadrao	integer	IN
externalid	character varying	IN
idfiliallocal	integer	IN
idfiliallocal_externalid	character varying	IN
idtipopedido	integer	IN
idtipopedido_externalid	character varying	IN
idusuario	integer	IN
idusuario_externalid	character varying	IN

Definition

declare	
	internalid integer;
	idparceiro integer;
begin
	--IDNPADRAO NULO OU FORA DO PADRAO 
	if idnpadrao is null or idnpadrao not in (0,1) then 
		RAISE EXCEPTION 'idnpadrao = %', idnpadrao
      		USING HINT = 'Field must not be null or different from 1 or 0.';
     end if;

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

--CLIENTELOCAL NULO
	 --if PostLocalTabelaPreco.idclientelocal is null then 
		--RAISE EXCEPTION 'idclientelocal = %', PostLocalTabelaPreco.idclientelocal
      		--USING HINT = 'Field idclientelocal must be not null';
     --end if;

--LOOKUP TABELAPRECO
	IF PostLocalTabelaPreco.idtabelapreco IS NULL AND PostLocalTabelaPreco.idtabelapreco_externalid IS NOT NULL
		THEN PostLocalTabelaPreco.idtabelapreco = (SELECT C2.IDTABELAPRECO FROM TABELAPRECO C2 WHERE C2.CODIGOERP = PostLocalTabelaPreco.idtabelapreco_externalid);
	END IF;

--TABELAPRECO NULA
	 if PostLocalTabelaPreco.idtabelapreco is null then 
		RAISE EXCEPTION 'idtabelapreco = %', PostLocalTabelaPreco.idtabelapreco
      		USING HINT = 'Field idtabelapreco must be not null';
     end if;
	 
	 --LOOKUP CLIENTE
	 idparceiro = (SELECT distinct C2.idparceiro FROM parceirolocal C2 WHERE C2.idlocal = PostLocalTabelaPreco.idclientelocal);	 
	 
	--LOOKUP FILIAL
	IF PostLocalTabelaPreco.idfiliallocal IS NULL AND PostLocalTabelaPreco.idfiliallocal_externalid IS NOT NULL
		THEN PostLocalTabelaPreco.idfiliallocal = (SELECT C2.IDLOCAL FROM LOCAL C2 WHERE C2.CODIGOERP = PostLocalTabelaPreco.idfiliallocal_externalid);
	END IF;

--LOOKUP TIPOPEDIDO
	IF PostLocalTabelaPreco.idtipopedido IS NULL AND PostLocalTabelaPreco.idtipopedido_externalid IS NOT NULL
		THEN PostLocalTabelaPreco.idtipopedido = (SELECT C2.IDTIPOPEDIDO FROM TIPOPEDIDO C2 WHERE C2.CODIGOERP = PostLocalTabelaPreco.idtipopedido_externalid);
	END IF;	
	
	--LOOKUP USUARIO
	IF PostLocalTabelaPreco.idusuario IS NULL AND PostLocalTabelaPreco.idusuario_externalid IS NOT NULL
		THEN PostLocalTabelaPreco.idusuario = (SELECT C2.idusuario FROM usuario C2 WHERE C2.CODIGOERP = PostLocalTabelaPreco.idusuario_externalid);
	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 LOCALTABELAPRECO WHERE CODIGOERP = PostLocalTabelaPreco.externalid) = 1
		THEN RAISE EXCEPTION 'externalid = %', PostLocalTabelaPreco.externalid 
				USING HINT = 'The record (externalid = '|| PostLocalTabelaPreco.externalid ||') already exists, it was not possible to insert it.';
		ELSE insert into localtabelapreco (idlocaltabelapreco, idparceiro, idtabelapreco, idnpadrao, codigoerp, idlocal, idlocalfilial, idtipopedido, idusuario) values (nextval('seqpklocaltabelapreco'),idparceiro, idtabelapreco, idnpadrao, externalid, idclientelocal, idfiliallocal, idtipopedido, idusuario) returning idlocaltabelapreco into internalid;
	end if;
	
	return query select internalid;
END;

postlocaltabelapreco(integer, character varying, integer, character varying, integer, character varying, integer, character varying, integer, character varying, integer, character varying)

Parameters

Definition