TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
internalid	integer	IN
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
	curdate date;
begin
	curdate = current_date;

-- VALIDACAO	
		-- EXTERNALID E INTERNALID NAO INFORMADOS
		if coalesce(PutNotaFiscal.externalid,'')='' and PutNotaFiscal.internalid is null then 
		RAISE EXCEPTION 'externalid = % and internalid = %', PutNotaFiscal.externalid, PutNotaFiscal.internalid
			USING HINT = 'Fields internalid and externalid must be not null';
		end if;

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

-- LOOKUP
		-- NOTA FISCAL
		IF PutNotaFiscal.internalid IS NULL 
		THEN PutNotaFiscal.internalid = (SELECT IDNOTAFISCAL FROM NOTAFISCAL WHERE CODIGOERP = PutNotaFiscal.externalid);
		END IF;
		
		-- CLIENTE
		IF PutNotaFiscal.idcliente IS NULL AND PutNotaFiscal.idcliente_externalid IS NOT NULL
			THEN PutNotaFiscal.idcliente = (SELECT C2.idparceiro FROM parceiro C2 WHERE C2.CODIGOERP = PutNotaFiscal.idcliente_externalid);
		END IF;

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

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

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

-- USUARIO
		IF PutNotaFiscal.idusuario IS NULL AND PutNotaFiscal.idusuario_externalid IS NOT NULL
			THEN PutNotaFiscal.idusuario = (SELECT C2.idusuario FROM usuario C2 WHERE C2.CODIGOERP = PutNotaFiscal.idusuario_externalid);
		END IF;	
		
		-- TIPONOTAFISCAL
		IF PutNotaFiscal.idtiponotafiscal IS NULL AND PutNotaFiscal.idtiponotafiscal_externalid IS NOT NULL
			THEN PutNotaFiscal.idtiponotafiscal = (SELECT C2.idtiponotafiscal FROM tiponotafiscal C2 WHERE C2.CODIGOERP = PutNotaFiscal.idtiponotafiscal_externalid);
		END IF;
		
		-- FILIALFATURAMENTO
		IF PutNotaFiscal.idfiliallocalfaturamento IS NULL AND PutNotaFiscal.idfiliallocalfaturamento_externalid IS NOT NULL
			THEN PutNotaFiscal.idfiliallocalfaturamento = (SELECT C2.idlocal FROM LOCAL C2 WHERE C2.CODIGOERP = PutNotaFiscal.idfiliallocalfaturamento_externalid);
		END IF;

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

-- UPDATE
	update notafiscal set 
		idncancelada = case when PutNotaFiscal.idncancelada = -99999 then notafiscal.idncancelada else PutNotaFiscal.idncancelada end, 
		numeronotafiscal = case when PutNotaFiscal.numeronotafiscal = 'NOVALUEWS' then notafiscal.numeronotafiscal else PutNotaFiscal.numeronotafiscal end, 
		serie = case when PutNotaFiscal.serie = 'NOVALUEWS' then notafiscal.serie else PutNotaFiscal.serie end, 
		horaatualizacao = case when PutNotaFiscal.horaatualizacao = 'NOVALUEWS' then notafiscal.horaatualizacao else PutNotaFiscal.horaatualizacao end, 
		dataemissao = case when PutNotaFiscal.dataemissao = 'NOVALUEWS' then notafiscal.dataemissao else TO_DATE(PutNotaFiscal.dataemissao,'YYYY-MM-DD') end, 
		valorfaturado = case when PutNotaFiscal.valorfaturado = -99999 then notafiscal.valorfaturado else PutNotaFiscal.valorfaturado end, 
		idlocal = case when PutNotaFiscal.idclientelocal = -99999 then notafiscal.idlocal else PutNotaFiscal.idclientelocal end, 
		idparceiro = case when PutNotaFiscal.idcliente = -99999 then notafiscal.idparceiro else PutNotaFiscal.idcliente end, 
		idusuario = case when PutNotaFiscal.idusuario = -99999 then notafiscal.idusuario else PutNotaFiscal.idusuario end, 
		idtiponotafiscal = case when PutNotaFiscal.idtiponotafiscal = -99999 then notafiscal.idtiponotafiscal else PutNotaFiscal.idtiponotafiscal end, 
		idlocalfilialfaturamento = case when PutNotaFiscal.idfiliallocalfaturamento = -99999 then notafiscal.idlocalfilialfaturamento else PutNotaFiscal.idfiliallocalfaturamento end, 
		idlocalfilialretirada = case when PutNotaFiscal.idfiliallocalretirada = -99999 then notafiscal.idlocalfilialretirada else PutNotaFiscal.idfiliallocalretirada end, 
		idlocalfilialvenda = case when PutNotaFiscal.idfiliallocalvenda = -99999 then notafiscal.idlocalfilialvenda else PutNotaFiscal.idfiliallocalvenda end, 
		observacao = case when PutNotaFiscal.observacao = 'NOVALUEWS' then notafiscal.observacao else PutNotaFiscal.observacao end, 
		idcondicaopagamento = case when PutNotaFiscal.idcondicaopagamento = -99999 then notafiscal.idcondicaopagamento else PutNotaFiscal.idcondicaopagamento end, 
		valorfrete = case when PutNotaFiscal.valorfrete = -99999 then notafiscal.valorfrete else PutNotaFiscal.valorfrete end, 
		valorseguro = case when PutNotaFiscal.valorseguro = -99999 then notafiscal.valorseguro else PutNotaFiscal.valorseguro end, 
		dataatualizacao = curdate,
		codigoerp = case when PutNotaFiscal.externalid = 'NOVALUEWS' then codigoerp else PutNotaFiscal.externalid end
	where idnotafiscal = internalid ;

return query select internalid;
END;