SELECT A.ID_DDOCGEST, A.MODULCOD, A.FORMATO, A.SIZEBYTE, A.FECHAUDI,
       A.USUARCOD, A.DOCUORIG, DECODE(C.DESCRIPCION, NULL, A.DESCRIPCION, C.DESCRIPCION) AS DESCRIPCION , A.SWONLINE, A.FECHBACK, A.IDBACKUP,
       A.ID_DSINPOLI_FK, A.ID_DPOLIZAS_FK, A.ID_DPERSONA_FK, A.FNAMEORI,

       CASE WHEN FNAMEORI LIKE '%Carátula Fianza Chile%'
            THEN 'CH000005'
            WHEN FNAMEORI LIKE 'POL%.pdf'
            THEN 'CH000005'
            WHEN FNAMEORI LIKE '%Temporario Fianza Colombia%' OR FNAMEORI LIKE '%Caratula Fianza Colombia%'
            THEN 'DFIAC02'
            WHEN FNAMEORI LIKE 'CCG%.pdf' AND B.PRODUCTO = 'CA03'
            THEN 'DFIAC02'
            WHEN (FNAMEORI LIKE '%Carátula Fianza Perú%' AND DECODE(C.DESCRIPCION, NULL, A.DESCRIPCION, C.DESCRIPCION) NOT LIKE '%Temporario%' AND B.PRODUCTO = 'CA04')
              OR FNAMEORI = 'Liquidación'
            THEN ( SELECT MAX(C.CODIDOCU)
                    FROM DDOCGEST A, DPOLIZAS B, DPOLGDOC C
                   WHERE A.ID_DPOLIZAS_FK = B.ID_DPOLIZAS
                         AND A.ID_DDOCGEST = C.ID_DDOCGEST_FK(+)
                   AND C.CODIDOCU LIKE 'RG%'
                   AND B.PRODUCTO = 'CA01' AND (C.CODIDOCU NOT LIKE 'DEXPE%' OR C.CODIDOCU IS NULL)  AND B.POLIZANN = 2018 AND B.POLIZSEC = 97582)
            WHEN B.PRODUCTO = 'CA01' AND C.CODIDOCU IS NULL AND FNAMEORI NOT LIKE '%tracking%' THEN
                (SELECT C.VALOR
                  FROM DPOLIZAS A,
                       DPOLSCON B,
                       DPODATOS C
                  WHERE A.ID_DPOLSCON_FK = B.ID_DPOLSCON
                    AND B.DPODATOS_SCON = C.ID_DPOLSCON_FK
                    AND C.NOMBDATO='CODIGODOCDEFESP'
                   -- AND A.PRODUCTO = 'CA01' AND A.POLIZANN = 2018 AND A.POLIZSEC = 97582)
                    AND A.PRODUCTO ='CA01' AND A.POLIZANN = 2018 AND A.POLIZSEC = 97582)
            ELSE C.CODIDOCU
        END CODIDOCU

  FROM DDOCGEST A, DPOLIZAS B, DPOLGDOC C
 WHERE A.ID_DPOLIZAS_FK = B.ID_DPOLIZAS
       AND A.ID_DDOCGEST = C.ID_DDOCGEST_FK(+)
AND B.PRODUCTO = 'CA01' AND (C.CODIDOCU NOT LIKE 'DEXPE%' OR C.CODIDOCU IS NULL)  AND B.POLIZANN = 2018 AND B.POLIZSEC = 97582;