TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
idncancelada	integer	IN
numeronotafiscal	character varying	IN
serie	character varying	IN
horaatualizacao	character varying	IN
dataemissao	character varying	IN
valorfaturado	numeric	IN
idclientelocal	integer	IN
idclientelocal_externalid	character varying	IN
idcliente	integer	IN
idcliente_externalid	character varying	IN
idusuario	integer	IN
idusuario_externalid	character varying	IN
idtiponotafiscal	integer	IN
idtiponotafiscal_externalid	character varying	IN
idfiliallocalfaturamento	integer	IN
idfiliallocalfaturamento_externalid	character varying	IN
idfiliallocalretirada	integer	IN
idfiliallocalretirada_externalid	character varying	IN
idfiliallocalvenda	integer	IN
idfiliallocalvenda_externalid	character varying	IN
observacao	character varying	IN
idcondicaopagamento	integer	IN
idcondicaopagamento_externalid	character varying	IN
valorfrete	numeric	IN
valorseguro	numeric	IN
externalid	character varying	IN

Definition

declare	
	internalid integer;
	curdate date;
begin

--IDNCANCELADA NULO OU FORA DO PADRAO 
	if idncancelada is null or idncancelada not in (0,1) then 
		RAISE EXCEPTION 'idncancelada = %', idncancelada
      		USING HINT = 'Field must not be null or different from 1 or 0.';
     end if;

--LOOKUP CLIENTE
	IF PostNotaFiscal.idcliente IS NULL AND PostNotaFiscal.idcliente_externalid IS NOT NULL
		THEN PostNotaFiscal.idcliente = (SELECT C2.idparceiro FROM parceiro C2 WHERE C2.CODIGOERP = PostNotaFiscal.idcliente_externalid);
	END IF;

--CLIENTE NULO
	 if PostNotaFiscal.idcliente is null then 
		RAISE EXCEPTION 'idcliente = %', PostNotaFiscal.idcliente
      		USING HINT = 'Field idcliente must be not null';
     end if;

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

--CLIENTELOCAL NULO
	 if PostNotaFiscal.idclientelocal is null then 
		RAISE EXCEPTION 'idclientelocal = %', PostNotaFiscal.idclientelocal
      		USING HINT = 'Field idclientelocal 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; 
	 
	--LOOKUPS NÃO OBRIGATÓRIOS 
	--LOOKUP USUARIO
	IF PostNotaFiscal.idusuario IS NULL AND PostNotaFiscal.idusuario_externalid IS NOT NULL
		THEN PostNotaFiscal.idusuario = (SELECT C2.idusuario FROM usuario C2 WHERE C2.CODIGOERP = PostNotaFiscal.idusuario_externalid);
	END IF;	
	
	--LOOKUP TIPONOTAFISCAL
	IF PostNotaFiscal.idtiponotafiscal IS NULL AND PostNotaFiscal.idtiponotafiscal_externalid IS NOT NULL
		THEN PostNotaFiscal.idtiponotafiscal = (SELECT C2.idtiponotafiscal FROM tiponotafiscal C2 WHERE C2.CODIGOERP = PostNotaFiscal.idtiponotafiscal_externalid);
	END IF;
	
	--LOOKUP FILIALFATURAMENTO
	IF PostNotaFiscal.idfiliallocalfaturamento IS NULL AND PostNotaFiscal.idfiliallocalfaturamento_externalid IS NOT NULL
		THEN PostNotaFiscal.idfiliallocalfaturamento = (SELECT C2.idlocal FROM LOCAL C2 WHERE C2.CODIGOERP = PostNotaFiscal.idfiliallocalfaturamento_externalid);
	END IF;

--LOOKUP FILIALRETIRADA
	IF PostNotaFiscal.idfiliallocalretirada IS NULL AND PostNotaFiscal.idfiliallocalretirada_externalid IS NOT NULL
		THEN PostNotaFiscal.idfiliallocalretirada = (SELECT C2.idlocal FROM LOCAL C2 WHERE C2.CODIGOERP = PostNotaFiscal.idfiliallocalretirada_externalid);
	END IF;		
	
	--LOOKUP FILIALVENDA
	IF PostNotaFiscal.idfiliallocalvenda IS NULL AND PostNotaFiscal.idfiliallocalvenda_externalid IS NOT NULL
		THEN PostNotaFiscal.idfiliallocalvenda = (SELECT C2.idlocal FROM LOCAL C2 WHERE C2.CODIGOERP = PostNotaFiscal.idfiliallocalvenda_externalid);
	END IF;		
	
	--LOOKUP CONDICAOPAGAMENTO
	IF PostNotaFiscal.idcondicaopagamento IS NULL AND PostNotaFiscal.idcondicaopagamento_externalid IS NOT NULL
		THEN PostNotaFiscal.idcondicaopagamento = (SELECT C2.idcondicaopagamento FROM condicaopagamento C2 WHERE C2.CODIGOERP = PostNotaFiscal.idcondicaopagamento_externalid);
	END IF;		
	
	curdate = current_date;

--VALIDAÇÃO DE CODIGOERP JÁ EXISTENTE
	IF (select distinct 1 FROM NOTAFISCAL WHERE CODIGOERP = PostNotaFiscal.externalid) = 1
		THEN RAISE EXCEPTION 'externalid = %', PostNotaFiscal.externalid 
				USING HINT = 'The record (externalid = '|| PostNotaFiscal.externalid ||') already exists, it was not possible to insert it.';
		ELSE insert into notafiscal (idnotafiscal, idncancelada, numeronotafiscal, serie, horaatualizacao, dataemissao, valorfaturado, idlocal, idparceiro, idusuario, idtiponotafiscal, idlocalfilialfaturamento, idlocalfilialretirada, idlocalfilialvenda, observacao, idcondicaopagamento, valorfrete, valorseguro, dataatualizacao, codigoerp) values (nextval('seqpknotafiscal'), idncancelada, numeronotafiscal, serie, horaatualizacao, TO_DATE(dataemissao,'YYYY-MM-DD'), valorfaturado, idclientelocal, idcliente, idusuario, idtiponotafiscal, idfiliallocalfaturamento, idfiliallocalretirada, idfiliallocalvenda, observacao, idcondicaopagamento, valorfrete, valorseguro, curdate, externalid) returning idnotafiscal into internalid;
	end if;
	
	return query select internalid;

END;