--Busca los ids de los documentos en el GESDOC de tesis y los guarda en sus columnas
UPDATE AA
SET AA.CMD_CifDocId = A.DocApiID
FROM
TESISSEG.dbo.AS_CAMBIOMEDIACION AA
LEFT JOIN
(SELECT A.CMD_Id, AA.DocApiID,A.EmpID
FROM TSSGESDOC.dbo.GesDoc AA
INNER JOIN
(SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID
FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100039N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
AND B.ApaId = 'CAMBIOMEDIACION'
AND B.DocApiID != 0
GROUP BY A.CMD_Id,A.EmpID) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION') A ON AA.EmpID = A.EmpID AND AA.CMD_Id = A.CMD_Id

UPDATE AA
SET AA.CMD_AutorizacionDocId  = B.DocApiID
FROM
TESISSEG.dbo.AS_CAMBIOMEDIACION AA
LEFT JOIN
(SELECT A.CMD_Id, AA.DocApiID,A.EmpID
FROM TSSGESDOC.dbo.GesDoc AA
INNER JOIN
(SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID
FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100028N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
AND B.ApaId = 'CAMBIOMEDIACION'
AND B.DocApiID != 0
GROUP BY A.CMD_Id,A.EmpID) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION') B ON AA.EmpID = B.EmpID AND AA.CMD_Id = B.CMD_Id

UPDATE AA
SET AA.CMD_DniDocId = C.DocApiID
FROM
TESISSEG.dbo.AS_CAMBIOMEDIACION AA
LEFT JOIN
(SELECT A.CMD_Id, AA.DocApiID,A.EmpID
FROM TSSGESDOC.dbo.GesDoc AA
INNER JOIN
(SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID
FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100045N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
AND B.ApaId = 'CAMBIOMEDIACION'
AND B.DocApiID != 0
GROUP BY A.CMD_Id,A.EmpID) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION') C ON AA.EmpID = C.EmpID AND AA.CMD_Id = C.CMD_Id

UPDATE AA
SET AA.CMD_CartaDocId  = D.DocApiID
    FROM
TESISSEG.dbo.AS_CAMBIOMEDIACION AA
LEFT JOIN
(SELECT A.CMD_Id, AA.DocApiID,A.EmpID
FROM TSSGESDOC.dbo.GesDoc AA
INNER JOIN
(SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID
FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100109N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
AND B.ApaId = 'CAMBIOMEDIACION'
AND B.DocApiID != 0
GROUP BY A.CMD_Id,A.EmpID) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION') D ON AA.EmpID = D.EmpId AND AA.CMD_Id = D.CMD_Id
