Sirs,
In the company where I work, I use an ERP that I do not know why, it is done typing sales notes, return sales and purchases returns in three different forms, and the same activity could be performed on the same form , the field of nf number is the same for all three forms and can not be repeated if in the sales form used nf number 525, the next entry will be the 526 in any of the three forms, for that the company of the ERP used a function that brings the last number used, adds + 1 and finds the next one to be used. Anyone who has a little knowledge will understand that this is a good gambiarra and that at some point one of the three forms can pick up the same number if typing occurs at the same time. Now, how to get around this situation? I already contacted the company about 3 months ago about this problem, several charges and I still have the problem here in the company.
Function used:
CREATE FUNCTION [dbo].[NfNumero]
(
@NfEspecie VARCHAR(3),
@NfSerie VARCHAR(3)
)
RETURNS NUMERIC(15)
AS
BEGIN
DECLARE @NfNumero AS NUMERIC(15)
SELECT @NfNumero = MAX(A.NfNumero)
FROM (
SELECT ISNULL(MAX(NfNumero),0) AS NfNumero
FROM Venda WITH(NOLOCK)
WHERE NfEspecie = @NfEspecie
AND NfSerie = @NfSerie
UNION ALL
SELECT ISNULL(MAX(NfNumero),0) NfNumero
FROM DevolucoesVendas WITH(NOLOCK)
WHERE NfEspecie = @NfEspecie
AND NfSerie = @NfSerie
UNION ALL
SELECT ISNULL(MAX(NfNumero),0) NfNumero
FROM DevolucoesCompras WITH(NOLOCK)
WHERE NfEspecie = @NfEspecie
AND Empresa = @Empresa
AND NfSerie = @NfSerie ) AS A
RETURN @NfNumero + 1
END