-- Migrar gestiones de clientes (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS
(Fusion_CLI_ID, Fusion_OBJ_ID, INC_FC, Fusion_USR_Id_FC, INC_FM, Fusion_USR_Id_FM, INC_Descripcion, INC_Situacion, INC_Cierre,
Fusion_INC_UsuarioCierre, GesNum, INC_TipoObj, EmpID,
 GesTipID, GesATipID,GesRes, GesStatus, GesLim, GesTie, GesAviFH, DesFin, GesBRIden, Aviso_Todos, Aviso_Gestor_Comercial,
 Aviso_Gestor_Administrativo, Aviso_Tramitador_Siniestro, INC_FechaEntrada )
SELECT  A.CliID, A.CliID, A.CliGesAltFec,
ISNULL(ISNULL(B.UseID,E.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) AS UseIDAlt,
A.CliGesUltFec,
CASE WHEN A.CliGesUltFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(C.UseID,F.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDUlt,
CASE WHEN ISNULL(B.UseID,E.UseID) IS NULL THEN CONCAT('[', TRIM(A.CliGesAltUsu),'] ', CAST(A.CliGesDes AS VARCHAR(MAX))) ELSE CAST(A.CliGesDes AS VARCHAR(MAX)) END AS CliGesDes,
CASE A.CliGesStatus WHEN 'P' THEN 1 WHEN 'R' THEN 1 ELSE 2 END AS CliGesStatus,
A.CliGesFinFec,
CASE WHEN A.CliGesFinFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(D.UseID,G.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDFin,
A.CliGesNum, 1, A.EmpID,
CASE WHEN A.CliGesTipId  = 0 THEN NULL ELSE A.CliGesTipId END AS CliGesTipId,
CASE WHEN A.CliGesATipID  = 0 THEN NULL ELSE A.CliGesATipID END AS CliGesATipID,
A.CliGesRes, A.CliGesStatus,
CASE WHEN A.CliGesLim  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.CliGesLim END AS CliGesLim,
A.CliGesTie,
CASE WHEN A.CliGesAviFH  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.CliGesAviFH END AS CliGesAviFH,
A.CliGesDesFin, A.CliGesBRIden,
CASE WHEN datalength(A.CliGesPriv) = 0 AND A.CliGesAviFH != '1753-01-01 00:00:00.000' AND A.CliGesAviFH != '' THEN 1 ELSE 0 END AS Aviso_Todos,
CASE WHEN A.CliGesPriv LIKE '%-111%' THEN 1 ELSE 0 END AS Aviso_Gestor_Comercial,
CASE WHEN A.CliGesPriv LIKE '%-222%' THEN 1 ELSE 0 END AS Aviso_Gestor_Administrativo,
CASE WHEN A.CliGesPriv LIKE '%-333%' THEN 1 ELSE 0 END AS Aviso_Tramitador_Siniestro,
A.CliGesAltFec
FROM TESISSEG.dbo.CliGes A
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) B ON A.CliGesAltUsu = B.UseRazSoc AND A.EmpID = B.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) C ON A.CliGesUltUsu = C.UseRazSoc AND A.EmpID = C.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) D ON A.CliGesFinUsu = D.UseRazSoc AND A.EmpID = D.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) E ON A.CliGesAltUsu = E.UseAlias AND A.EmpID = E.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) F ON A.CliGesUltUsu = F.UseAlias AND A.EmpID = F.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) G ON A.CliGesFinUsu = G.UseAlias AND A.EmpID = G.EmpID
WHERE A.INC_Id = 0 AND A.EmpID = X;

-- Migrar avisos de incidencias de clientes (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS_USUARIOS_AVISO (
    INC_Id,
    UseID)
SELECT
    B.INC_Id,
    value
FROM
    TESISSEG.dbo.CliGes A
        INNER JOIN TESISSEG.dbo.AS_INCIDENCIAS B ON
                A.CliID = B.Fusion_OBJ_Id AND B.GesNum = A.CliGesNum
            AND B.EmpID = A.EmpID
            AND B.INC_TipoObj = 1
    CROSS APPLY STRING_SPLIT(replace(replace(convert(varchar(8000), A.CliGesPriv), '[', ''), ']', ''),
	',')
WHERE
    A.CliGesPriv NOT LIKE ''
  AND EXISTS
    (
    SELECT A.UseID
    FROM TESISSEG.dbo.Usuarios A
    WHERE A.UseID = value
    )
    AND A.INC_Id = 0
  AND A.EmpID = X;

-- Actualizar referencia de la gestión de clientes (sustituir X por el id de la correduría)
UPDATE A SET
    A.INC_Id = B.INC_Id
    FROM TESISSEG.dbo.CliGes A
INNER JOIN (
SELECT A.INC_Id, B.CliID, B.CliGesNum, A.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS A
INNER JOIN TESISSEG.dbo.CliGes B ON B.CliID = A.Fusion_OBJ_Id AND A.GesNum = B.CliGesNum AND A.INC_TipoObj = 1 AND A.EmpID = B.EmpID
WHERE B.INC_Id = 0 AND A.EmpID = X
)
B ON A.CliID = B.CliID AND A.CliGesNum = B.CliGesNum AND A.EmpID = B.EmpID

-- Migrar gestiones de pólizas (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS
(Fusion_CLI_ID, Fusion_OBJ_ID, INC_FC, Fusion_USR_Id_FC, INC_FM, Fusion_USR_Id_FM, INC_Descripcion, INC_Situacion, INC_Cierre,
Fusion_INC_UsuarioCierre, GesNum, INC_TipoObj, EmpID,
 GesTipID, GesATipID,GesRes, GesStatus, GesLim, GesTie, GesAviFH, DesFin, GesBRIden, Aviso_Todos, Aviso_Gestor_Comercial,
 Aviso_Gestor_Administrativo, Aviso_Tramitador_Siniestro, INC_FechaEntrada )
SELECT p.CliID, A.PolNumID, A.PolGesAltFec,
ISNULL(ISNULL(B.UseID,E.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) AS UseIDAlt,
A.PolGesUltFec,
CASE WHEN A.PolGesUltFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(C.UseID,F.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDUlt,
CASE WHEN ISNULL(B.UseID,E.UseID) IS NULL THEN CONCAT('[', TRIM(A.PolGesAltUsu),'] ', CAST(A.PolGesDes AS VARCHAR(MAX))) ELSE CAST(A.PolGesDes AS VARCHAR(MAX)) END AS PolGesDes,
CASE A.PolGesStatus WHEN 'P' THEN 1 WHEN 'R' THEN 1 ELSE 2 END AS PolGesStatus,
A.PolGesFinFec,
CASE WHEN A.PolGesFinFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(D.UseID,G.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDFin,
A.PolGesNum, 7, A.EmpID,
CASE WHEN A.PolGesTipId  = 0 THEN NULL ELSE A.PolGesTipId END AS PolGesTipId,
CASE WHEN A.PolGesATipID  = 0 THEN NULL ELSE A.PolGesATipID END AS PolGesATipID,
A.PolGesRes, A.PolGesStatus,
CASE WHEN A.PolGesLim  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.PolGesLim END AS PolGesLim,
A.PolGesTie,
CASE WHEN A.PolGesAviFH  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.PolGesAviFH END AS PolGesAviFH,
A.PolGesDesFin, A.PolGesBRIden,
CASE WHEN datalength(A.PolGesPriv) = 0 AND A.PolGesAviFH != '1753-01-01 00:00:00.000' AND A.PolGesAviFH != '' THEN 1 ELSE 0 END AS Aviso_Todos,
CASE WHEN A.PolGesPriv LIKE '%-111%' THEN 1 ELSE 0 END AS Aviso_Gestor_Comercial,
CASE WHEN A.PolGesPriv LIKE '%-222%' THEN 1 ELSE 0 END AS Aviso_Gestor_Administrativo,
CASE WHEN A.PolGesPriv LIKE '%-333%' THEN 1 ELSE 0 END AS Aviso_Tramitador_Siniestro,
A.PolGesAltFec
FROM TESISSEG.dbo.PolGes A
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) B ON A.PolGesAltUsu = B.UseRazSoc AND A.EmpID = B.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) C ON A.PolGesUltUsu = C.UseRazSoc AND A.EmpID = C.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) D ON A.PolGesFinUsu = D.UseRazSoc AND A.EmpID = D.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) E ON A.PolGesAltUsu = E.UseAlias AND A.EmpID = E.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) F ON A.PolGesUltUsu = F.UseAlias AND A.EmpID = F.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) G ON A.PolGesFinUsu = G.UseAlias AND A.EmpID = G.EmpID
LEFT JOIN TESISSEG.dbo.Polizas p ON p.PolNumID = A.PolNumID AND p.EmpID = A.EmpID
WHERE A.INC_Id = 0 AND A.EmpID = X;

-- Migrar avisos de incidencias de polizas (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS_USUARIOS_AVISO (
    INC_Id,
    UseID)
SELECT
    B.INC_Id,
    value
FROM
    TESISSEG.dbo.PolGes A
        INNER JOIN TESISSEG.dbo.AS_INCIDENCIAS B ON
                A.PolNumID = B.Fusion_OBJ_Id AND B.GesNum = A.PolGesNum
            AND B.EmpID = A.EmpID
            AND B.INC_TipoObj = 7
    CROSS APPLY STRING_SPLIT(replace(replace(convert(varchar(8000), A.PolGesPriv), '[', ''), ']', ''),
	',')
WHERE
    A.PolGesPriv NOT LIKE ''
  AND EXISTS
    (
    SELECT A.UseID
    FROM TESISSEG.dbo.Usuarios A
    WHERE A.UseID = value
    )
    AND A.INC_Id = 0
  AND A.EmpID = X;


-- Actualizar referencia de la gestión de pólizas (sustituir X por el id de la correduría)
UPDATE A SET
    A.INC_Id = B.INC_Id
    FROM TESISSEG.dbo.PolGes A
INNER JOIN (
SELECT A.INC_Id, B.PolNumID, B.PolGesNum, A.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS A
INNER JOIN TESISSEG.dbo.PolGes B ON CAST(B.PolNumID AS varchar) = A.Fusion_OBJ_Id AND A.GesNum = B.PolGesNum AND A.INC_TipoObj = 7 AND A.EmpID = B.EmpID
WHERE B.INC_Id = 0 AND A.EmpID = X
)
B ON A.PolNumID = B.PolNumID AND A.PolGesNum = B.PolGesNum AND A.EmpID = B.EmpID



-- Migrar gestiones de recibos (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS
(Fusion_CLI_ID, Fusion_OBJ_ID, INC_FC, Fusion_USR_Id_FC, INC_FM, Fusion_USR_Id_FM, INC_Descripcion, INC_Situacion, INC_Cierre,
Fusion_INC_UsuarioCierre, GesNum, INC_TipoObj, EmpID,
 GesTipID, GesATipID,GesRes, GesStatus, GesLim, GesTie, GesAviFH, DesFin, GesBRIden, Aviso_Todos, Aviso_Gestor_Comercial,
 Aviso_Gestor_Administrativo, Aviso_Tramitador_Siniestro, INC_FechaEntrada )
SELECT r.CliID, CONCAT(A.RecSer, '/', A.RecNumID) AS RecId, A.RecGesAltFec,
ISNULL(ISNULL(B.UseID,E.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) AS UseIDAlt,
A.RecGesUltFec,
CASE WHEN A.RecGesUltFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(C.UseID,F.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDUlt,
CASE WHEN ISNULL(B.UseID,E.UseID) IS NULL THEN CONCAT('[', TRIM(A.RecGesAltUsu),'] ', CAST(A.RecGesDes AS VARCHAR(MAX))) ELSE CAST(A.RecGesDes AS VARCHAR(MAX)) END AS RecGesDes,
CASE A.RecGesStatus WHEN 'P' THEN 1 WHEN 'R' THEN 1 ELSE 2 END AS RecGesStatus,
A.RecGesFinFec,
CASE WHEN A.RecGesFinFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(D.UseID,G.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDFin,
A.RecGesNum, 10, A.EmpID,
CASE WHEN A.RecGesTipId  = 0 THEN NULL ELSE A.RecGesTipId END AS RecGesTipId,
CASE WHEN A.RecGesATipID  = 0 THEN NULL ELSE A.RecGesATipID END AS RecGesATipID,
A.RecGesRes, A.RecGesStatus,
CASE WHEN A.RecGesLim  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.RecGesLim END AS RecGesLim,
A.RecGesTie,
CASE WHEN A.RecGesAviFH  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.RecGesAviFH END AS RecGesAviFH,
A.RecGesDesFin, A.RecGesBRIden,
CASE WHEN datalength(A.RecGesPriv) = 0 AND A.RecGesAviFH != '1753-01-01 00:00:00.000' AND A.RecGesAviFH != '' THEN 1 ELSE 0 END AS Aviso_Todos,
CASE WHEN A.RecGesPriv LIKE '%-111%' THEN 1 ELSE 0 END AS Aviso_Gestor_Comercial,
CASE WHEN A.RecGesPriv LIKE '%-222%' THEN 1 ELSE 0 END AS Aviso_Gestor_Administrativo,
CASE WHEN A.RecGesPriv LIKE '%-333%' THEN 1 ELSE 0 END AS Aviso_Tramitador_Siniestro,
A.RecGesAltFec
FROM TESISSEG.dbo.RecGes A
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) B ON A.RecGesAltUsu = B.UseRazSoc AND A.EmpID = B.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) C ON A.RecGesUltUsu = C.UseRazSoc AND A.EmpID = C.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) D ON A.RecGesFinUsu = D.UseRazSoc AND A.EmpID = D.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) E ON A.RecGesAltUsu = E.UseAlias AND A.EmpID = E.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) F ON A.RecGesUltUsu = F.UseAlias AND A.EmpID = F.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) G ON A.RecGesFinUsu = G.UseAlias AND A.EmpID = G.EmpID
LEFT JOIN TESISSEG.dbo.Recibos r
ON r.RecNumID = A.RecNumID AND r.RecSer = A.RecSer AND r.EmpID = A.EmpID 
WHERE A.INC_Id = 0 AND A.EmpID = X;

-- Migrar avisos de incidencias de recibos (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS_USUARIOS_AVISO (
    INC_Id,
    UseID)
SELECT
    B.INC_Id,
    value
FROM
    TESISSEG.dbo.RecGes A
        INNER JOIN TESISSEG.dbo.AS_INCIDENCIAS B ON
                CONCAT(A.RecSer, '/', A.RecNumID) = B.Fusion_OBJ_Id AND B.GesNum = A.RecGesNum
            AND B.EmpID = A.EmpID
            AND B.INC_TipoObj = 10
    CROSS APPLY STRING_SPLIT(replace(replace(convert(varchar(8000), A.RecGesPriv), '[', ''), ']', ''),
	',')
WHERE
    A.RecGesPriv NOT LIKE ''
  AND EXISTS
    (
    SELECT A.UseID
    FROM TESISSEG.dbo.Usuarios A
    WHERE A.UseID = value
    )
    AND A.INC_Id = 0
  AND A.EmpID = X;

-- Actualizar referencia de la gestión de recibos (sustituir X por el id de la correduría)
UPDATE A SET
    A.INC_Id = B.INC_Id
    FROM TESISSEG.dbo.RecGes A
INNER JOIN (
SELECT A.INC_Id, B.RecSer, B.RecNumID, B.RecGesNum, A.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS A
INNER JOIN TESISSEG.dbo.RecGes B ON CONCAT(B.RecSer, '/', B.RecNumID) = A.Fusion_OBJ_Id AND A.GesNum = B.RecGesNum AND A.INC_TipoObj = 10 AND A.EmpID = B.EmpID
WHERE B.INC_Id = 0 AND A.EmpID = X
)
B ON A.RecSer = B.RecSer AND A.RecNumID = B.RecNumID AND A.RecGesNum = B.RecGesNum AND A.EmpID = B.EmpID

-- Migrar gestiones de siniestros (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS
(Fusion_CLI_ID, Fusion_OBJ_ID, INC_FC, Fusion_USR_Id_FC, INC_FM, Fusion_USR_Id_FM, INC_Descripcion, INC_Situacion, INC_Cierre,
Fusion_INC_UsuarioCierre, GesNum, INC_TipoObj, EmpID,
 GesTipID, GesATipID,GesRes, GesStatus, GesLim, GesTie, GesAviFH, DesFin, GesBRIden, Aviso_Todos, Aviso_Gestor_Comercial,
 Aviso_Gestor_Administrativo, Aviso_Tramitador_Siniestro, INC_FechaEntrada )
SELECT s.CliID, CONCAT(A.SinSer, '/', A.SinNumID) AS SinId, A.SinGesAltFec,
ISNULL(ISNULL(B.UseID,E.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) AS UseIDAlt,
A.SinGesUltFec,
CASE WHEN A.SinGesUltFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(C.UseID,F.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDUlt,
CASE WHEN ISNULL(B.UseID,E.UseID) IS NULL THEN CONCAT('[', TRIM(A.SinGesAltUsu),'] ',CAST(A.SinGesDes AS VARCHAR(MAX))) ELSE CAST(A.SinGesDes AS VARCHAR(MAX)) END AS SinGesDes,
CASE A.SinGesStatus WHEN 'P' THEN 1 WHEN 'R' THEN 1 ELSE 2 END AS SinGesStatus,
A.SinGesFinFec,
CASE WHEN A.SinGesFinFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(D.UseID,G.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDFin,
A.SinGesNum, 13, A.EmpID,
CASE WHEN A.SinGesTipId  = 0 THEN NULL ELSE A.SinGesTipId END AS SinGesTipId,
CASE WHEN A.SinGesATipID  = 0 THEN NULL ELSE A.SinGesATipID END AS SinGesATipID,
A.SinGesRes, A.SinGesStatus,
CASE WHEN A.SinGesLim  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.SinGesLim END AS SinGesLim,
A.SinGesTie,
CASE WHEN A.SinGesAviFH  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.SinGesAviFH END AS SinGesAviFH,
A.SinGesDesFin, A.SinGesBRIden,
CASE WHEN datalength(A.SinGesPriv) = 0 AND A.SinGesAviFH != '1753-01-01 00:00:00.000' AND A.SinGesAviFH != '' THEN 1 ELSE 0 END AS Aviso_Todos,
CASE WHEN A.SinGesPriv LIKE '%-111%' THEN 1 ELSE 0 END AS Aviso_Gestor_Comercial,
CASE WHEN A.SinGesPriv LIKE '%-222%' THEN 1 ELSE 0 END AS Aviso_Gestor_Administrativo,
CASE WHEN A.SinGesPriv LIKE '%-333%' THEN 1 ELSE 0 END AS Aviso_Tramitador_Siniestro,
A.SinGesAltFec
FROM TESISSEG.dbo.SinGes A
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) B ON A.SinGesAltUsu = B.UseRazSoc AND A.EmpID = B.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) C ON A.SinGesUltUsu = C.UseRazSoc AND A.EmpID = C.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) D ON A.SinGesFinUsu = D.UseRazSoc AND A.EmpID = D.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) E ON A.SinGesAltUsu = E.UseAlias AND A.EmpID = E.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) F ON A.SinGesUltUsu = F.UseAlias AND A.EmpID = F.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) G ON A.SinGesFinUsu = G.UseAlias AND A.EmpID = G.EmpID
LEFT JOIN TESISSEG.dbo.Siniestros s
ON s.SinNumID = A.SinNumID AND s.SinSer = A.SinSer AND s.EmpID = A.EmpID 
WHERE A.INC_Id = 0 AND A.EmpID = X;

-- Migrar avisos de incidencias de siniestros (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS_USUARIOS_AVISO (
    INC_Id,
    UseID)
SELECT
    B.INC_Id,
    value
FROM
    TESISSEG.dbo.SinGes A
        INNER JOIN TESISSEG.dbo.AS_INCIDENCIAS B ON
                CONCAT(A.SinSer, '/', A.SinNumID) = B.Fusion_OBJ_Id AND B.GesNum = A.SinGesNum
            AND B.EmpID = A.EmpID
            AND B.INC_TipoObj = 13
    CROSS APPLY STRING_SPLIT(replace(replace(convert(varchar(8000), A.SinGesPriv), '[', ''), ']', ''),
	',')
WHERE
    A.SinGesPriv NOT LIKE ''
  AND EXISTS
    (
    SELECT A.UseID
    FROM TESISSEG.dbo.Usuarios A
    WHERE A.UseID = value
    )
    AND A.INC_Id = 0
  AND A.EmpID = X;


-- Actualizar referencia de la gestión de siniestros (sustituir X por el id de la correduría)
UPDATE A SET
    A.INC_Id = B.INC_Id
    FROM TESISSEG.dbo.SinGes A
INNER JOIN (
SELECT A.INC_Id, B.SinSer, B.SinNumID, B.SinGesNum, A.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS A
INNER JOIN TESISSEG.dbo.SinGes B ON CONCAT(B.SinSer, '/', B.SinNumID) = A.Fusion_OBJ_Id AND A.GesNum = B.SinGesNum AND A.INC_TipoObj = 13 AND A.EmpID = B.EmpID
WHERE B.INC_Id = 0 AND A.EmpID = X
)
B ON A.SinSer = B.SinSer AND A.SinNumID = B.SinNumID AND A.SinGesNum = B.SinGesNum AND A.EmpID = B.EmpID

-- Migrar gestiones de multirecibos (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS
(Fusion_CLI_ID, Fusion_OBJ_ID, INC_FC, Fusion_USR_Id_FC, INC_FM, Fusion_USR_Id_FM, INC_Descripcion, INC_Situacion, INC_Cierre,
 Fusion_INC_UsuarioCierre, GesNum, INC_TipoObj, EmpID,
 GesTipID, GesATipID,GesRes, GesStatus, GesLim, GesTie, GesAviFH, DesFin, GesBRIden, Aviso_Todos, Aviso_Gestor_Comercial,
 Aviso_Gestor_Administrativo, Aviso_Tramitador_Siniestro, INC_FechaEntrada )
SELECT r.CliID, CONCAT(A.MReSer, '/', A.MReNumID) AS RecId, A.MReGesAltFec,
       ISNULL(ISNULL(B.UseID,E.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
                                        WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) AS UseIDAlt,
       A.MReGesUltFec,
       CASE WHEN A.MReGesUltFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(C.UseID,F.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
                                                                                                            WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDUlt,
       CASE WHEN ISNULL(B.UseID,E.UseID) IS NULL THEN CONCAT('[', TRIM(A.MReGesAltUsu),'] ', CAST(A.MReGesDes AS VARCHAR(MAX))) ELSE CAST(A.MReGesDes AS VARCHAR(MAX)) END AS RecGesDes,
       CASE A.MReGesStatus WHEN 'P' THEN 1 WHEN 'R' THEN 1 ELSE 2 END AS RecGesStatus,
       A.MReGesFinFec,
       CASE WHEN A.MReGesFinFec = '1753-01-01 00:00:00.000' THEN NULL ELSE ISNULL(ISNULL(D.UseID,G.UseID), (SELECT A.UseID FROM TESISSEG.dbo.Usuarios A INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
                                                                                                            WHERE A.UPerID != 'MST' AND B.EmpID = X AND A.UseLog = 'SUPERVISOR')) END AS UseIDFin,
       A.MReGesNum, 10, A.EmpID,
       CASE WHEN A.MReGesTipId  = 0 THEN NULL ELSE A.MReGesTipId END AS RecGesTipId,
       CASE WHEN A.MReGesATipID  = 0 THEN NULL ELSE A.MReGesATipID END AS RecGesATipID,
       A.MReGesRes, A.MReGesStatus,
       CASE WHEN A.MReGesLim  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.MReGesLim END AS RecGesLim,
       A.MReGesTie,
       CASE WHEN A.MReGesAviFH  = '1753-01-01 00:00:00.000' THEN NULL ELSE A.MReGesAviFH END AS RecGesAviFH,
       A.MReGesDesFin, A.MReGesBRIden,
       CASE WHEN datalength(A.MReGesPriv) = 0 AND A.MReGesAviFH != '1753-01-01 00:00:00.000' AND A.MReGesAviFH != '' THEN 1 ELSE 0 END AS Aviso_Todos,
CASE WHEN A.MReGesPriv LIKE '%-111%' THEN 1 ELSE 0 END AS Aviso_Gestor_Comercial,
CASE WHEN A.MReGesPriv LIKE '%-222%' THEN 1 ELSE 0 END AS Aviso_Gestor_Administrativo,
CASE WHEN A.MReGesPriv LIKE '%-333%' THEN 1 ELSE 0 END AS Aviso_Tramitador_Siniestro,
A.MReGesAltFec
FROM TESISSEG.dbo.MultiRecibosGes A
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) B ON A.MReGesAltUsu = B.UseRazSoc AND A.EmpID = B.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) C ON A.MReGesUltUsu = C.UseRazSoc AND A.EmpID = C.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseRazSoc, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST'
) D ON A.MReGesFinUsu = D.UseRazSoc AND A.EmpID = D.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) E ON A.MReGesAltUsu = E.UseAlias AND A.EmpID = E.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) F ON A.MReGesUltUsu = F.UseAlias AND A.EmpID = F.EmpID
LEFT JOIN
(
SELECT A.UseID, A.UseAlias, B.EmpID
FROM TESISSEG.dbo.Usuarios A
INNER JOIN TESISSEG.dbo.UsuParCor B ON A.UseID = B.UseID
WHERE A.UPerID != 'MST' AND A.UseAlias != ''
) G ON A.MReGesFinUsu = G.UseAlias AND A.EmpID = G.EmpID
LEFT JOIN TESISSEG.dbo.Recibos r
ON r.RecNumID = A.MReNumID AND r.RecSer = A.MReSer AND r.EmpID = A.EmpID 
WHERE A.INC_Id = 0 AND A.EmpID = X;

-- Migrar avisos de incidencias de multirecibos (sustituir X por el id de la correduría)
INSERT INTO TESISSEG.dbo.AS_INCIDENCIAS_USUARIOS_AVISO (
    INC_Id,
    UseID)
SELECT
    B.INC_Id,
    value
FROM
    TESISSEG.dbo.MultiRecibosGes A
        INNER JOIN TESISSEG.dbo.AS_INCIDENCIAS B ON
        CONCAT(A.MReSer, '/', A.MReNumID) = B.Fusion_OBJ_Id AND B.GesNum = A.MReGesNum
            AND B.EmpID = A.EmpID
            AND B.INC_TipoObj = 10
    CROSS APPLY STRING_SPLIT(replace(replace(convert(varchar(8000), A.MReGesPriv), '[', ''), ']', ''),
	',')
WHERE
    A.MReGesPriv NOT LIKE ''
  AND EXISTS
    (
    SELECT A.UseID
    FROM TESISSEG.dbo.Usuarios A
    WHERE A.UseID = value
    )
  AND A.INC_Id = 0
  AND A.EmpID = X;

-- Actualizar referencia de la gestión de multirecibos (sustituir X por el id de la correduría)
UPDATE A SET
    A.INC_Id = B.INC_Id
    FROM TESISSEG.dbo.MultiRecibosGes A
INNER JOIN (
SELECT A.INC_Id, B.MReSer, B.MReNumID, B.MReGesNum, A.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS A
INNER JOIN TESISSEG.dbo.MultiRecibosGes B ON CONCAT(B.MReSer, '/', B.MReNumID) = A.Fusion_OBJ_Id AND A.GesNum = B.MReGesNum AND A.INC_TipoObj = 10 AND A.EmpID = B.EmpID
WHERE B.INC_Id = 0 AND A.EmpID = X
)
B ON A.MReSer = B.MReSer AND A.MReNumID = B.MReNumID AND A.MReGesNum = B.MReGesNum AND A.EmpID = B.EmpID


-- Corregir INcidencias de colaborador
INSERT INTO TESISSEG.dbo.AS_ITEM_USUARIOCOLABORADOR
(IUC_TipoObjeto, Fusion_IUC_IdObjeto, USR_Id, EmpID)
SELECT
    44,
    ai.INC_Id,
    uc.USER_id ,
    ai.EmpID
FROM TESISSEG.dbo.AS_INCIDENCIAS ai
JOIN TESISSEG.dbo.UsuParCor upc ON upc.UseID = ai.fusion_usr_id_FC  and upc.EmpID = ai.EmpID AND (upc.ColID != '' and upc.ColID != '-1')
JOIN TESISSEG.dbo.AS_USUARIOS_COLABORADORES uc ON uc.USER_usuariotesis = ai.fusion_usr_id_FC AND uc.EmpID = ai.EmpID
LEFT JOIN TESISSEG.dbo.AS_ITEM_USUARIOCOLABORADOR iuc ON  iuc.IUC_TipoObjeto = 44 AND iuc.Fusion_IUC_IdObjeto = ai.INC_Id AND iuc.USR_Id = uc.USER_id AND iuc.EmpID = ai.EmpID
WHERE
    iuc.IUC_Id IS NULL
  AND ai.EmpID = X
;

UPDATE ai
SET ai.fusion_usr_id_FC = CAST(par.PAR_Valor AS int)
FROM TESISSEG.dbo.AS_INCIDENCIAS ai
JOIN TESISSEG.dbo.UsuParCor upc ON upc.UseID = ai.fusion_usr_id_FC  and upc.EmpID = ai.EmpID AND (upc.ColID != '' and upc.ColID != '-1')
JOIN TESISSEG.dbo.AS_PARAMETROS par ON par.PAR_Parametro = 'USUARIO_WEB_TESIS' and par.EmpID = ai.EmpID and par.PAR_Oficina_tesis IS NULL
WHERE
    ai.EmpID = X