UPDATE A
SET A.UseParTraSin = 'S'
    FROM TESISSEG.dbo.UsuParCor A
INNER JOIN TESISSEG.dbo.AS_SINIESTROS as2 ON A.UseID = as2.USR_TramitadorTesis
WHERE as2.USR_TramitadorTesis IS NOT NULL
  AND A.UseParTraSin = 'N'

DECLARE @EmpID INT
DECLARE @RowCount INT
DECLARE @Index INT

SET @Index = 1
SELECT @RowCount = COUNT(*) FROM TESISSEG.dbo.Corredurias

WHILE @Index <= @RowCount
BEGIN

	-- Obtener el ID de correduría
WITH CONS AS (SELECT EmpID, ROW_NUMBER() OVER (ORDER BY EmpID) AS RN FROM TESISSEG.dbo.Corredurias)
SELECT @EmpID = EmpID FROM CONS WHERE RN = @Index

                                      -- Mostrar un mensaje informando la correduría que se está procesando
                                      --PRINT 'Procesando Correduría: ' + CAST(@EmpID AS VARCHAR)
    RAISERROR ('Procesando Correduría %d', 0, 1, @EmpID) WITH NOWAIT

-- INSTRUCCIONES A EJECUTAR PARA CADA CORREDURÍA
UPDATE A
SET A.UseTSSPars = SUBSTRING(A.UseTSSPars,1,5) + 'S' + SUBSTRING(A.UseTSSPars,7,LEN(A.UseTSSPars))
    FROM TESISSEG.dbo.UsuParCor A
WHERE SUBSTRING(A.UseTSSPars,6,1) = 'N'
  AND A.EmpID = @EmpID
  AND EXISTS (
    SELECT UseGesCom FROM TESISSEG.dbo.Clientes WHERE UseGesCom = A.UseID AND EmpID = @EmpID
    UNION
    SELECT UseGesCom FROM TESISSEG.dbo.Polizas WHERE UseGesCom = A.UseID AND EmpID = @EmpID
    UNION
    SELECT USR_GestorComercial FROM TESISSEG.dbo.AS_OPORTUNIDADES WHERE USR_GestorComercial = A.UseID AND EmpID = @EmpID
    )

UPDATE A
SET A.UseTSSPars = SUBSTRING(A.UseTSSPars,1,7) + 'S' + SUBSTRING(A.UseTSSPars,9,LEN(A.UseTSSPars))
    FROM TESISSEG.dbo.UsuParCor A
WHERE SUBSTRING(A.UseTSSPars,8,1) = 'N'
  AND A.EmpID = @EmpID
  AND EXISTS (
    SELECT UseGesAdm FROM TESISSEG.dbo.Clientes WHERE UseGesAdm = A.UseID AND EmpID = @EmpID
    UNION
    SELECT UseGesAdm FROM TESISSEG.dbo.Polizas WHERE UseGesAdm = A.UseID AND EmpID = @EmpID
    UNION
    SELECT USR_GestorAdministrativo FROM TESISSEG.dbo.AS_OPORTUNIDADES WHERE USR_GestorAdministrativo = A.UseID AND EmpID = @EmpID
    )

-- Incrementar el índice
SET @Index = @Index + 1

END