OAT » TA Admin

Correr en prod

ALTER PROCEDURE dbo.getNewControlNumber
    @typeCode INT,
    @recourseId INT = NULL
AS
BEGIN
    DECLARE @recourseCount INT
	DECLARE @recourseInitials VARCHAR(2) = CASE
	    WHEN @typeCode = 13 THEN 'AP' -- Apelación
	    WHEN @typeCode = 14 THEN 'CE' -- Certiorari
	    WHEN @typeCode = 22 THEN 'RA' -- Revisión Administrativa
	    WHEN @typeCode IN (23, 24, 778, 779, 780) THEN 'RE' -- Habeas Corpus, Mandamus, Injunction, Auto-Inhibitorio, Quo Warranto 
	    WHEN @typeCode = 645 THEN 'EM' -- Escrito Misceláneo
	    ELSE NULL
	END
	
	IF @recourseInitials IS NULL
	BEGIN
	    RAISERROR('Not a valid recourse type code', 16, 1)
	    RETURN;
	END
	
	SELECT @recourseCount = COUNT(r.recourseId)
	    FROM recourses r WITH (NOLOCK) 
	    INNER JOIN recourseDetails rd WITH (NOLOCK)
            ON rd.recourseId = r.recourseId
            AND rd.isDeleted = 0
            AND rd.controlNumber LIKE @recourseInitials + '-' + CAST(YEAR(GETDATE()) AS VARCHAR) + '%'
        WHERE (@recourseId IS NULL OR r.recourseId != @recourseId)
	
	WHILE EXISTS(
		SELECT * 
		FROM recourseDetails rd WITH(NOLOCK)
		INNER JOIN recourses r WITH(NOLOCK) ON r.recourseId = rd.recourseId AND (@recourseId IS NULL OR r.recourseId != @recourseId)
		WHERE controlNumber = @recourseInitials + '-' + CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('00000' + CAST(@recourseCount AS VARCHAR), 5) 
	) OR @recourseCount = 0
	BEGIN 
		SET @recourseCount = @recourseCount + 1
	END
	
	SELECT @recourseInitials + '-' + CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('00000' + CAST(@recourseCount AS VARCHAR), 5) AS number
END;